AP invoice interface sample code in oracle apps r12
AP invoice Interface uses to import External AP invoices in Oracle Payables. To Create AP invoices from External System we first need to Insert the External Invoice Data in the AP invoice Interface Tables. We need to prepare the External Invoice information's in the format of AP invoice Interface and then insert these records in the AP interface Tables. In this post , I am sharing the AP invoice Interface sample code in oracle r12 to insert the invoice information from external system to the AP invoice Interface tables. You can refer this AP invoice interface sample code to insert invoices from External System.
Steps of AP invoice Interface.
1. Prepare the External System data in the form of AP invoice Interface.
2.Insert record in the AP invoice Interface Tables.
3.Run the Payables Open interface Import program to import invoices from AP interface to AP base tables.
These are the two tables of AP Invoice Interface. These tables uses to import AP invoices from External System to the Oracle application.
For example , This is the Invoice Test Data which we got from the External System and how i will insert this Data in these Two Interface tables As Below.
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;
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;
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;
/
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;
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;
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