AP invoice interface tables in oracle apps r12
In this post , we will be discuss about AP invoice interface tables in oracle apps r12. In oracle apps r12 , we have the AP invoice interface tables which helps to upload the AP invoices from External source or system in to the Oracle application system. AP Invoice Interface tables helps to integrate the Payables system with the external application , which helps to import the AP invoices from external application into the oracle payables system. We can mass upload the AP invoices using the AP invoice interface tables in oracle apps r12. AP invoice interface tables works as an Bridge between the Oracle apps system and the external system. We will share the all in important Tables of AP invoice interfaces. Here below is the complete detail about AP invoice interface tables in oracle apps r12.
 |
| AP invoice interface tables in oracle apps r12 |
2 Important AP invoice interface tables in oracle apps r12
1.AP_INVOICES_INTERFACE.
2.AP_INVOICES_LINES_INTERFACE.
Detail PLSQL Script using AP invoice interface tables in oracle apps r12
DECLARE
v_inv_type ap_invoices_interface.INVOICE_TYPE_lookup_code%TYPE;
v_invoice_date ap_invoices_interface.INVOICE_DATE%TYPE;
V_vendor_id ap_invoices_interface.vendor_id%TYPE;
v_vendor_site_id ap_invoices_interface.vendor_site_id%TYPE;
v_invoice_num ap_invoices_interface.INVOICE_NUM %TYPE;
v_invoice_curr ap_invoices_interface.INVOICE_CURRENCY_CODE%TYPE;
v_invoice_amount ap_invoices_interface.INVOICE_AMOUNT%TYPE;
v_inv_type ap_invoices_interface.INVOICE_TYPE_lookup_code%TYPE;
v_invoice_date ap_invoices_interface.INVOICE_DATE%TYPE;
V_vendor_id ap_invoices_interface.vendor_id%TYPE;
v_vendor_site_id ap_invoices_interface.vendor_site_id%TYPE;
v_invoice_num ap_invoices_interface.INVOICE_NUM %TYPE;
v_invoice_curr ap_invoices_interface.INVOICE_CURRENCY_CODE%TYPE;
v_invoice_amount ap_invoices_interface.INVOICE_AMOUNT%TYPE;
v_payment_cross_rate
ap_invoices_interface.PAYMENT_CROSS_RATE%TYPE;
v_cross_payment_curr ap_invoices_interface.PAYMENT_CURRENCY_CODE%TYPE;
v_pay_rate_date ap_invoices_interface.PAYMENT_CROSS_RATE_DATE%TYPE;
v_payment_rate_type ap_invoices_interface.PAYMENT_CROSS_RATE_TYPE%TYPE;
v_description ap_invoices_interface.DESCRIPTION%TYPE;
v_rate_type ap_invoices_interface.EXCHANGE_RATE_TYPE%TYPE;
v_exchange_date ap_invoices_interface.EXCHANGE_DATE%TYPE;
v_exchange_rate ap_invoices_interface.EXCHANGE_RATE%TYPE;
v_terms_date ap_invoices_interface.TERMS_DATE%TYPE;
v_terms ap_invoices_interface.TERMS_ID%TYPE;
v_payment_method ap_invoices_interface.PAYMENT_METHOD_LOOKUP_CODE%TYPE;
v_discountable_amt ap_invoices_interface.AMOUNT_APPLICABLE_TO_DISCOUNT%TYPE;
v_cross_payment_curr ap_invoices_interface.PAYMENT_CURRENCY_CODE%TYPE;
v_pay_rate_date ap_invoices_interface.PAYMENT_CROSS_RATE_DATE%TYPE;
v_payment_rate_type ap_invoices_interface.PAYMENT_CROSS_RATE_TYPE%TYPE;
v_description ap_invoices_interface.DESCRIPTION%TYPE;
v_rate_type ap_invoices_interface.EXCHANGE_RATE_TYPE%TYPE;
v_exchange_date ap_invoices_interface.EXCHANGE_DATE%TYPE;
v_exchange_rate ap_invoices_interface.EXCHANGE_RATE%TYPE;
v_terms_date ap_invoices_interface.TERMS_DATE%TYPE;
v_terms ap_invoices_interface.TERMS_ID%TYPE;
v_payment_method ap_invoices_interface.PAYMENT_METHOD_LOOKUP_CODE%TYPE;
v_discountable_amt ap_invoices_interface.AMOUNT_APPLICABLE_TO_DISCOUNT%TYPE;
v_invoice_recieved_date
ap_invoices_interface.INVOICE_RECEIVED_DATE%TYPE;
v_recieved_goods_date ap_invoices_interface.GOODS_RECEIVED_DATE%TYPE;
v_pay_code_combination_id ap_invoices_interface.ACCTS_PAY_CODE_COMBINATION_ID%TYPE;
v_inv_count number;
V_CODE_COMBINATION_ID NUMBER;
v_invoice_id NUMBER;
v_invoice_line_id NUMBER;
v_line_no NUMBER;
v_amount ap_invoices_interface.INVOICE_AMOUNT%TYPE;
v_accounting_date DATE;
v_line_type VARCHAR2(400);
V_LOOKUP_CODE VARCHAR2(400);
V_CURRENCY_CODE VARCHAR2(400);
V_INVOICE_NUMBER VARCHAR2(400);
V_concatenated_segment VARCHAR2(4000);
V_ERROR_MESSAGE VARCHAR2(4000);
V_ERROR_FLAG VARCHAR2(40);
cursor c_invoice IS
SELECT INVOICE_NUMBER,INVOICE_DATE INVOICE_DATE,INVOICE_TYPE,DESCRIPTION,INVOICE_AMOUNT,SUPPLIER_NUMBER,SUPPLIER_NAME
,SUPPLIER_SITE,INVOICE_CURRENCY
FROM XX_INVOICES_STG
WHERE NVL(STATUS,'NEW') NOT IN ('PROCESSED')
GROUP BY INVOICE_NUMBER,INVOICE_DATE,INVOICE_TYPE,DESCRIPTION,INVOICE_AMOUNT,SUPPLIER_NUMBER,SUPPLIER_NAME
,SUPPLIER_SITE,INVOICE_CURRENCY;
cursor c_inv_detls(c_inv_number varchar2,c_supplier_number varchar2) IS
SELECT INVOICE_NUMBER,INVOICE_DATE,INVOICE_TYPE,DESCRIPTION,INVOICE_AMOUNT,SUPPLIER_NUMBER,SUPPLIER_NAME
v_recieved_goods_date ap_invoices_interface.GOODS_RECEIVED_DATE%TYPE;
v_pay_code_combination_id ap_invoices_interface.ACCTS_PAY_CODE_COMBINATION_ID%TYPE;
v_inv_count number;
V_CODE_COMBINATION_ID NUMBER;
v_invoice_id NUMBER;
v_invoice_line_id NUMBER;
v_line_no NUMBER;
v_amount ap_invoices_interface.INVOICE_AMOUNT%TYPE;
v_accounting_date DATE;
v_line_type VARCHAR2(400);
V_LOOKUP_CODE VARCHAR2(400);
V_CURRENCY_CODE VARCHAR2(400);
V_INVOICE_NUMBER VARCHAR2(400);
V_concatenated_segment VARCHAR2(4000);
V_ERROR_MESSAGE VARCHAR2(4000);
V_ERROR_FLAG VARCHAR2(40);
cursor c_invoice IS
SELECT INVOICE_NUMBER,INVOICE_DATE INVOICE_DATE,INVOICE_TYPE,DESCRIPTION,INVOICE_AMOUNT,SUPPLIER_NUMBER,SUPPLIER_NAME
,SUPPLIER_SITE,INVOICE_CURRENCY
FROM XX_INVOICES_STG
WHERE NVL(STATUS,'NEW') NOT IN ('PROCESSED')
GROUP BY INVOICE_NUMBER,INVOICE_DATE,INVOICE_TYPE,DESCRIPTION,INVOICE_AMOUNT,SUPPLIER_NUMBER,SUPPLIER_NAME
,SUPPLIER_SITE,INVOICE_CURRENCY;
cursor c_inv_detls(c_inv_number varchar2,c_supplier_number varchar2) IS
SELECT INVOICE_NUMBER,INVOICE_DATE,INVOICE_TYPE,DESCRIPTION,INVOICE_AMOUNT,SUPPLIER_NUMBER,SUPPLIER_NAME
,SUPPLIER_SITE,INVOICE_CURRENCY,LINE_NUMBER,LINE_AMOUNT,GL_ACCOUNT,ATTRIBUTE1,ATTRIBUTE2
FROM XX_INVOICES_STG
where INVOICE_NUMBER=c_inv_number
and SUPPLIER_NUMBER=c_supplier_number;
begin
for f_invoice in c_invoice loop
V_ERROR_MESSAGE:=NULL;
V_ERROR_FLAG:=NULL;
v_inv_type :=NULL;
v_invoice_date:=NULL;
v_invoice_num:=NULL;
v_invoice_curr:=NULL;
v_invoice_amount:=NULL;
v_description:=NULL;
BEGIN
SELECT INVOICE_NUMBER into V_INVOICE_NUMBER
FROM XX_INVOICES_STG
WHERE INVOICE_NUMBER=f_invoice.invoice_number
GROUP BY INVOICE_NUMBER,INVOICE_DATE,INVOICE_TYPE,DESCRIPTION,INVOICE_AMOUNT,SUPPLIER_NUMBER,SUPPLIER_NAME
,SUPPLIER_SITE,INVOICE_CURRENCY;
EXCEPTION
WHEN OTHERS THEN
V_ERROR_MESSAGE:=V_ERROR_MESSAGE||'---'||'Error: Invoice Header Information is not Identical';
V_Error_flag:='Y';
END;
BEGIN
select LOOKUP_CODE INTO V_LOOKUP_CODE from fnd_lookup_values
where LOOKUP_CODE='STANDARD'
AND LOOKUP_TYPE='INVOICE TYPE';
EXCEPTION WHEN NO_DATA_FOUND THEN
V_ERROR_MESSAGE:=V_ERROR_MESSAGE||'---'||'Error: Invoice Type is Not Valid';
V_Error_flag:='Y';
WHEN OTHERS THEN
V_ERROR_MESSAGE:=V_ERROR_MESSAGE||'---'||'Error: Invoice Type';
V_Error_flag:='Y';
END;
begin
select VENDOR_ID into v_vendor_id from ap_suppliers
where segment1=f_invoice.SUPPLIER_NUMBER
and VENDOR_NAME=f_invoice.SUPPLIER_NAME;
EXCEPTION WHEN NO_DATA_FOUND THEN
V_ERROR_MESSAGE:=V_ERROR_MESSAGE||'---'||'Error: Vendor is Not Valid';
V_Error_flag:='Y';
WHEN OTHERS THEN
V_ERROR_MESSAGE:=V_ERROR_MESSAGE||'---'||'Error: Vendor Selection';
V_Error_flag:='Y';
END;
begin
select VENDOR_ID into v_vendor_site_id from ap_supplier_SITES_ALL
where VENDOR_ID=v_vendor_id
and VENDOR_SITE_CODE=f_invoice.SUPPLIER_SITE;
EXCEPTION WHEN NO_DATA_FOUND THEN
V_ERROR_MESSAGE:= V_ERROR_MESSAGE||'---'||'Error: Vendor Site is Not Valid';
V_Error_flag:='Y';
WHEN OTHERS THEN
V_ERROR_MESSAGE:=V_ERROR_MESSAGE||'---'||'Error: Vendor Site Selection';
V_Error_flag:='Y';
END;
begin
select CURRENCY_CODE into V_CURRENCY_CODE from fnd_currencies
where CURRENCY_CODE=f_invoice.INVOICE_CURRENCY;
EXCEPTION WHEN NO_DATA_FOUND THEN
V_ERROR_MESSAGE:= V_ERROR_MESSAGE||'---'||'Error: Invoice Currency is Not Valid';
V_Error_flag:='Y';
WHEN OTHERS THEN
V_ERROR_MESSAGE:=V_ERROR_MESSAGE||'---'||'Error: Currency Selection';
V_Error_flag:='Y';
END;
for f_inv_detls in c_inv_detls(f_invoice.invoice_number,f_invoice.supplier_number) loop
begin
select COUNT(*) into V_concatenated_segment from gl_code_combinations_kfv
where trim(concatenated_segments)=TRIM(REPLACE(f_inv_detls.GL_ACCOUNT,CHR(13),''));
EXCEPTION WHEN NO_DATA_FOUND THEN
V_ERROR_MESSAGE:= V_ERROR_MESSAGE||'-'||f_inv_detls.GL_ACCOUNT||'-'||'Error: GL Code is Not Valid';
V_Error_flag:='Y';
WHEN OTHERS THEN
V_ERROR_MESSAGE:=V_ERROR_MESSAGE||'---'||'Error: GL Code Selection';
V_Error_flag:='Y';
END;
end loop;
--v_status:= cur_invoice.status;
-- SELECT COUNT(*)
-- INTO v_inv_count
-- FROM AP_INVOICES_ALL
-- WHERE INVOICE_NUM = V_INVOICE_NUM
-- AND VENDOR_ID = v_vendor_id;
--
--IF v_count > 0 THEN
-- FND_FILE.PUT_LINE(FND_FILE.LOG,' Invocie already Exists - '||v_invoice_num);
--end if;
--inserting validated values to interface table(headers)='N'
if NVL(V_Error_flag,'N')='N' THEN
SELECT ap_invoices_interface_s.nextval INTO v_invoice_id FROM dual;
v_inv_type := f_invoice.INVOICE_TYPE;
v_invoice_date:=to_DATE(f_invoice.INVOICE_DATE,'MM/DD/YYYY');
v_invoice_num:= f_invoice.invoice_number;
v_invoice_curr:= f_invoice.INVOICE_CURRENCY;
v_invoice_amount:= f_invoice.invoice_amount;
v_description:= f_invoice.description;
INSERT INTO ap_invoices_interface (
invoice_id,
invoice_num,
invoice_type_lookup_code,
invoice_date,
vendor_id,
vendor_site_id,
invoice_amount,
invoice_currency_code,
description,
status,
source,
creation_date,
created_by,
last_update_date,
last_updated_by
)
VALUES
(
v_invoice_id,
v_invoice_num,
v_inv_type,
v_invoice_date,
v_vendor_id,
v_vendor_site_id,
nvl(v_invoice_amount,0),
v_invoice_curr,
v_description,
'NEW',
'TEST SOURCE',
sysdate,
FND_GLOBAL.user_id,
sysdate,
FND_GLOBAL.user_id
);
for f_inv_detls in c_inv_detls(f_invoice.invoice_number,f_invoice.supplier_number) loop
select ap_invoice_lines_interface_s.nextval into v_invoice_line_id from dual;
v_line_no:=f_inv_detls.line_NUMBER;
v_amount:=f_inv_detls.line_amount;
v_accounting_date:=sysdate;
v_line_type:='Item';--(Item, Fright,Tax,Miscellaneous)
select CODE_COMBINATION_ID into V_CODE_COMBINATION_ID from gl_code_combinations_kfv
where concatenated_segments=TRIM(REPLACE(f_inv_detls.GL_ACCOUNT,CHR(13),''));
-- BEGIN
-- SELECT 'Y'
-- INTO v_found
-- FROM gl_period_statuses
-- WHERE application_id = 200--ASSUMTION
-- AND set_of_books_id = 3--ASSUMTION
-- AND SYSDATE between start_date AND end_date
-- AND closing_status IN ('O', 'F')
-- AND NVL(adjustment_period_flag, 'N') = 'N'
-- AND ROWNUM = 1;
-- EXCEPTION
-- WHEN NO_DATA_FOUND THEN
-- FND_FILE.PUT_LINE(FND_FILE.log,'Accounting Date Is Not In Open');
-- END;
/*------start to Validate Line Level for po_number matching/validating----------*/
-- get the info of header (if invoice is related to po_number)
/*------end to Validate Line Level for po_number matching/validating----------*/
/*start to inserting invoice line*/
INSERT INTO ap_invoice_lines_interface (
invoice_id,
invoice_line_id,
line_number,
line_type_lookup_code,
amount,
accounting_date,
dist_code_combination_id,
creation_date,
created_by,
last_update_date,
last_updated_by
)
VALUES(v_invoice_id,
v_invoice_line_id,
v_line_no,
v_line_type,
v_amount,
v_accounting_date,
V_CODE_COMBINATION_ID,
sysdate,
FND_GLOBAL.user_id,
sysdate,
FND_GLOBAL.user_id
);
end loop;
UPDATE APPS.XX_INVOICES_STG
SET STATUS='PROCESSED',
ERROR_MESSAGE=V_ERROR_MESSAGE
WHERE invoice_number=f_invoice.invoice_number
AND SUPPLIER_NUMBER=f_invoice.SUPPLIER_NUMBER;
ELSE
UPDATE APPS.XX_INVOICES_STG
SET STATUS='ERROR',
ERROR_MESSAGE=V_ERROR_MESSAGE
WHERE invoice_number=f_invoice.invoice_number
AND SUPPLIER_NUMBER=f_invoice.SUPPLIER_NUMBER;
END IF;
COMMIT;
END LOOP;
END;
/ DECLARE
v_inv_type ap_invoices_interface.INVOICE_TYPE_lookup_code%TYPE;
v_invoice_date ap_invoices_interface.INVOICE_DATE%TYPE;
V_vendor_id ap_invoices_interface.vendor_id%TYPE;
v_vendor_site_id ap_invoices_interface.vendor_site_id%TYPE;
v_invoice_num ap_invoices_interface.INVOICE_NUM %TYPE;
v_invoice_curr ap_invoices_interface.INVOICE_CURRENCY_CODE%TYPE;
v_invoice_amount ap_invoices_interface.INVOICE_AMOUNT%TYPE;
v_payment_cross_rate ap_invoices_interface.PAYMENT_CROSS_RATE%TYPE;
v_cross_payment_curr ap_invoices_interface.PAYMENT_CURRENCY_CODE%TYPE;
v_pay_rate_date ap_invoices_interface.PAYMENT_CROSS_RATE_DATE%TYPE;
v_payment_rate_type ap_invoices_interface.PAYMENT_CROSS_RATE_TYPE%TYPE;
v_description ap_invoices_interface.DESCRIPTION%TYPE;
v_rate_type ap_invoices_interface.EXCHANGE_RATE_TYPE%TYPE;
v_exchange_date ap_invoices_interface.EXCHANGE_DATE%TYPE;
v_exchange_rate ap_invoices_interface.EXCHANGE_RATE%TYPE;
v_terms_date ap_invoices_interface.TERMS_DATE%TYPE;
v_terms ap_invoices_interface.TERMS_ID%TYPE;
v_payment_method ap_invoices_interface.PAYMENT_METHOD_LOOKUP_CODE%TYPE;
v_discountable_amt ap_invoices_interface.AMOUNT_APPLICABLE_TO_DISCOUNT%TYPE;
v_invoice_recieved_date ap_invoices_interface.INVOICE_RECEIVED_DATE%TYPE;
v_recieved_goods_date ap_invoices_interface.GOODS_RECEIVED_DATE%TYPE;
v_pay_code_combination_id ap_invoices_interface.ACCTS_PAY_CODE_COMBINATION_ID%TYPE;
v_inv_count number;
V_CODE_COMBINATION_ID NUMBER;
v_invoice_id NUMBER;
v_invoice_line_id NUMBER;
v_line_no NUMBER;
v_amount ap_invoices_interface.INVOICE_AMOUNT%TYPE;
v_accounting_date DATE;
v_line_type VARCHAR2(400);
V_LOOKUP_CODE VARCHAR2(400);
V_CURRENCY_CODE VARCHAR2(400);
V_INVOICE_NUMBER VARCHAR2(400);
V_concatenated_segment VARCHAR2(4000);
V_ERROR_MESSAGE VARCHAR2(4000);
V_ERROR_FLAG VARCHAR2(40);
cursor c_invoice IS
SELECT INVOICE_NUMBER,INVOICE_DATE INVOICE_DATE,INVOICE_TYPE,DESCRIPTION,INVOICE_AMOUNT,SUPPLIER_NUMBER,SUPPLIER_NAME
,SUPPLIER_SITE,INVOICE_CURRENCY
FROM XX_INVOICES_STG
WHERE NVL(STATUS,'NEW') NOT IN ('PROCESSED')
GROUP BY INVOICE_NUMBER,INVOICE_DATE,INVOICE_TYPE,DESCRIPTION,INVOICE_AMOUNT,SUPPLIER_NUMBER,SUPPLIER_NAME
,SUPPLIER_SITE,INVOICE_CURRENCY;
cursor c_inv_detls(c_inv_number varchar2,c_supplier_number varchar2) IS
SELECT INVOICE_NUMBER,INVOICE_DATE,INVOICE_TYPE,DESCRIPTION,INVOICE_AMOUNT,SUPPLIER_NUMBER,SUPPLIER_NAME
,SUPPLIER_SITE,INVOICE_CURRENCY,LINE_NUMBER,LINE_AMOUNT,GL_ACCOUNT,ATTRIBUTE1,ATTRIBUTE2
FROM XX_INVOICES_STG
where INVOICE_NUMBER=c_inv_number
and SUPPLIER_NUMBER=c_supplier_number;
begin
for f_invoice in c_invoice loop
V_ERROR_MESSAGE:=NULL;
V_ERROR_FLAG:=NULL;
v_inv_type :=NULL;
v_invoice_date:=NULL;
v_invoice_num:=NULL;
v_invoice_curr:=NULL;
v_invoice_amount:=NULL;
v_description:=NULL;
BEGIN
SELECT INVOICE_NUMBER into V_INVOICE_NUMBER
FROM XX_INVOICES_STG
WHERE INVOICE_NUMBER=f_invoice.invoice_number
GROUP BY INVOICE_NUMBER,INVOICE_DATE,INVOICE_TYPE,DESCRIPTION,INVOICE_AMOUNT,SUPPLIER_NUMBER,SUPPLIER_NAME
,SUPPLIER_SITE,INVOICE_CURRENCY;
EXCEPTION
WHEN OTHERS THEN
V_ERROR_MESSAGE:=V_ERROR_MESSAGE||'---'||'Error: Invoice Header Information is not Identical';
V_Error_flag:='Y';
END;
BEGIN
select LOOKUP_CODE INTO V_LOOKUP_CODE from fnd_lookup_values
where LOOKUP_CODE='STANDARD'
AND LOOKUP_TYPE='INVOICE TYPE';
EXCEPTION WHEN NO_DATA_FOUND THEN
V_ERROR_MESSAGE:=V_ERROR_MESSAGE||'---'||'Error: Invoice Type is Not Valid';
V_Error_flag:='Y';
WHEN OTHERS THEN
V_ERROR_MESSAGE:=V_ERROR_MESSAGE||'---'||'Error: Invoice Type';
V_Error_flag:='Y';
END;
begin
select VENDOR_ID into v_vendor_id from ap_suppliers
where segment1=f_invoice.SUPPLIER_NUMBER
and VENDOR_NAME=f_invoice.SUPPLIER_NAME;
EXCEPTION WHEN NO_DATA_FOUND THEN
V_ERROR_MESSAGE:=V_ERROR_MESSAGE||'---'||'Error: Vendor is Not Valid';
V_Error_flag:='Y';
WHEN OTHERS THEN
V_ERROR_MESSAGE:=V_ERROR_MESSAGE||'---'||'Error: Vendor Selection';
V_Error_flag:='Y';
END;
begin
select VENDOR_ID into v_vendor_site_id from ap_supplier_SITES_ALL
where VENDOR_ID=v_vendor_id
and VENDOR_SITE_CODE=f_invoice.SUPPLIER_SITE;
EXCEPTION WHEN NO_DATA_FOUND THEN
V_ERROR_MESSAGE:= V_ERROR_MESSAGE||'---'||'Error: Vendor Site is Not Valid';
V_Error_flag:='Y';
WHEN OTHERS THEN
V_ERROR_MESSAGE:=V_ERROR_MESSAGE||'---'||'Error: Vendor Site Selection';
V_Error_flag:='Y';
END;
begin
select CURRENCY_CODE into V_CURRENCY_CODE from fnd_currencies
where CURRENCY_CODE=f_invoice.INVOICE_CURRENCY;
EXCEPTION WHEN NO_DATA_FOUND THEN
V_ERROR_MESSAGE:= V_ERROR_MESSAGE||'---'||'Error: Invoice Currency is Not Valid';
V_Error_flag:='Y';
WHEN OTHERS THEN
V_ERROR_MESSAGE:=V_ERROR_MESSAGE||'---'||'Error: Currency Selection';
V_Error_flag:='Y';
END;
|
| AP invoice interface tables in oracle apps r12 |
for f_inv_detls in c_inv_detls(f_invoice.invoice_number,f_invoice.supplier_number) loop
begin
select COUNT(*) into V_concatenated_segment from gl_code_combinations_kfv
where trim(concatenated_segments)=TRIM(REPLACE(f_inv_detls.GL_ACCOUNT,CHR(13),''));
EXCEPTION WHEN NO_DATA_FOUND THEN
V_ERROR_MESSAGE:= V_ERROR_MESSAGE||'-'||f_inv_detls.GL_ACCOUNT||'-'||'Error: GL Code is Not Valid';
V_Error_flag:='Y';
WHEN OTHERS THEN
V_ERROR_MESSAGE:=V_ERROR_MESSAGE||'---'||'Error: GL Code Selection';
V_Error_flag:='Y';
END;
end loop;
--v_status:= cur_invoice.status;
-- SELECT COUNT(*)
-- INTO v_inv_count
-- FROM AP_INVOICES_ALL
-- WHERE INVOICE_NUM = V_INVOICE_NUM
-- AND VENDOR_ID = v_vendor_id;
--
--IF v_count > 0 THEN
-- FND_FILE.PUT_LINE(FND_FILE.LOG,' Invocie already Exists - '||v_invoice_num);
--end if;
--inserting validated values to interface table(headers)='N'
if NVL(V_Error_flag,'N')='N' THEN
SELECT ap_invoices_interface_s.nextval INTO v_invoice_id FROM dual;
v_inv_type := f_invoice.INVOICE_TYPE;
v_invoice_date:=to_DATE(f_invoice.INVOICE_DATE,'MM/DD/YYYY');
v_invoice_num:= f_invoice.invoice_number;
v_invoice_curr:= f_invoice.INVOICE_CURRENCY;
v_invoice_amount:= f_invoice.invoice_amount;
v_description:= f_invoice.description;
INSERT INTO ap_invoices_interface (
invoice_id,
invoice_num,
invoice_type_lookup_code,
invoice_date,
vendor_id,
vendor_site_id,
invoice_amount,
invoice_currency_code,
description,
status,
source,
creation_date,
created_by,
last_update_date,
last_updated_by
)
VALUES
(
v_invoice_id,
v_invoice_num,
v_inv_type,
v_invoice_date,
v_vendor_id,
v_vendor_site_id,
nvl(v_invoice_amount,0),
v_invoice_curr,
v_description,
'NEW',
'TEST SOURCE',
sysdate,
FND_GLOBAL.user_id,
sysdate,
FND_GLOBAL.user_id
);
for f_inv_detls in c_inv_detls(f_invoice.invoice_number,f_invoice.supplier_number) loop
select ap_invoice_lines_interface_s.nextval into v_invoice_line_id from dual;
v_line_no:=f_inv_detls.line_NUMBER;
v_amount:=f_inv_detls.line_amount;
v_accounting_date:=sysdate;
v_line_type:='Item';--(Item, Fright,Tax,Miscellaneous)
select CODE_COMBINATION_ID into V_CODE_COMBINATION_ID from gl_code_combinations_kfv
where concatenated_segments=TRIM(REPLACE(f_inv_detls.GL_ACCOUNT,CHR(13),''));
-- BEGIN
-- SELECT 'Y'
-- INTO v_found
-- FROM gl_period_statuses
-- WHERE application_id = 200--ASSUMTION
-- AND set_of_books_id = 3--ASSUMTION
-- AND SYSDATE between start_date AND end_date
-- AND closing_status IN ('O', 'F')
-- AND NVL(adjustment_period_flag, 'N') = 'N'
-- AND ROWNUM = 1;
-- EXCEPTION
-- WHEN NO_DATA_FOUND THEN
-- FND_FILE.PUT_LINE(FND_FILE.log,'Accounting Date Is Not In Open');
-- END;
/*------start to Validate Line Level for po_number matching/validating----------*/
-- get the info of header (if invoice is related to po_number)
/*------end to Validate Line Level for po_number matching/validating----------*/
/*start to inserting invoice line*/
INSERT INTO ap_invoice_lines_interface (
invoice_id,
invoice_line_id,
line_number,
line_type_lookup_code,
amount,
accounting_date,
dist_code_combination_id,
creation_date,
created_by,
last_update_date,
last_updated_by
)
VALUES(v_invoice_id,
v_invoice_line_id,
v_line_no,
v_line_type,
v_amount,
v_accounting_date,
V_CODE_COMBINATION_ID,
sysdate,
FND_GLOBAL.user_id,
sysdate,
FND_GLOBAL.user_id
);
end loop;
UPDATE APPS.XX_INVOICES_STG
SET STATUS='PROCESSED',
ERROR_MESSAGE=V_ERROR_MESSAGE
WHERE invoice_number=f_invoice.invoice_number
AND SUPPLIER_NUMBER=f_invoice.SUPPLIER_NUMBER;
ELSE
UPDATE APPS.XX_INVOICES_STG
SET STATUS='ERROR',
ERROR_MESSAGE=V_ERROR_MESSAGE
WHERE invoice_number=f_invoice.invoice_number
AND SUPPLIER_NUMBER=f_invoice.SUPPLIER_NUMBER;
END IF;
COMMIT;
END LOOP;
END;
0 comments:
Post a Comment