CREATE OR REPLACE procedure XXPROC_OPENINT(errbuf OUT
VARCHAR2,
retcode OUT
VARCHAR2) AS
--Cursor Declaration
cursor inv_cur IS
SELECT
INVI.INVOICE_ID
,INVI.INVOICE_TYPE_LOOKUP_CODE
,INVI.INVOICE_DATE
,INVI.VENDOR_ID
,INVI.VENDOR_SITE_ID
,INVI.INVOICE_AMOUNT
,INVI.INVOICE_CURRENCY_CODE
,INVI.STATUS
,INVI.GROUP_ID
,INVI.SOURCE
,INVI.ORG_ID
,INVI.DESCRIPTION
,INVI.CREATION_DATE
,INVI.CREATED_BY
,INVL.INVOICE_ID
,INVL.LINE_NUMBER
,INVL.LINE_TYPE_LOOKUP_CODE
,INVL.DESCRIPTION
,INVL.ORG_ID
,INVL.INVENTORY_ITEM_ID
,INVL.ACCOUNTING_DATE
,INVL.AMOUNT
,INVL.CREATION_DATE
,INVL.CREATED_BY
,INVL.PO_HEADER_ID
,INVL.PO_LINE_LOCATION_ID
,INVL.ITEM_DESCRIPTION
FROM XXAP_INVOICES_INTERFACE_STG INVI
,AP_INVOICE_LINES_INTERFACE_STG INVL
WHERE INVI.INVOICE_ID=INVL.INVOICE_ID
AND INVI.ORG_ID=INVL.ORG_ID;
l_currency_code varchar2(25);
l_flag varchar2(2);
l_error_msg varchar2(100);
l_err_msg varchar2(100);
l_err_flag varchar2(10);
l_count number(9) default 0;
BEGIN
for rec_cur in inv_cur loop
l_count:=l_count+1;
--Currency Code Validation
BEGIN
SELECT currency_code
INTO l_currency_code
FROM FND_CURRENCIES
WHERE currency_code ='USD';
EXCEPTION
WHEN others THEN
l_flag:='E';
l_error_msg:='Currency Code Does not Exists';
fnd_file.put_line(fnd_file.log,'Inserting Data Into The
Interface Table'||'-'||l_count||' '||l_error_msg);
END;
IF l_flag!='E' THEN
fnd_file.put_line(fnd_file.log,'Inserting Data Into The
Interface Table');
INSERT INTO AP_INVOICES_INTERFACE(
INVOICE_ID
,INVOICE_TYPE_LOOKUP_CODE
,INVOICE_DATE
,VENDOR_ID
,VENDOR_SITE_ID
,INVOICE_AMOUNT
,INVOICE_CURRENCY_CODE
,STATUS
,GROUP_ID
,SOURCE
,ORG_ID
,DESCRIPTION
,CREATION_DATE
,CREATED_BY)
VALUES
(rec_cur.INVOICE_ID
,rec_cur.INVOICE_TYPE_LOOKUP_CODE
,rec_cur.INVOICE_DATE
,rec_cur.VENDOR_ID
,rec_cur.VENDOR_SITE_ID
,rec_cur.INVOICE_AMOUNT
,rec_cur.INVOICE_CURRENCY_CODE
,rec_cur.STATUS
,rec_cur.GROUP_ID
,rec_cur.SOURCE
,rec_cur.ORG_ID
,rec_cur.DESCRIPTION
,rec_cur.CREATION_DATE
,rec_cur.CREATED_BY);
INSERT INTO ap_invoice_lines_interface (
INVOICE_ID
,LINE_NUMBER
,LINE_TYPE_LOOKUP_CODE
,DESCRIPTION
,ORG_ID
,INVENTORY_ITEM_ID
,ACCOUNTING_DATE
,AMOUNT
,CREATION_DATE
,CREATED_BY
,PO_HEADER_ID

,PO_LINE_LOCATION_ID,ITEM_DESCRIPTION)
VALUES (rec_cur.INVOICE_ID
,rec_cur.LINE_NUMBER
,rec_cur.LINE_TYPE_LOOKUP_CODE
,rec_cur.DESCRIPTION
,rec_cur.ORG_ID
,rec_cur.INVENTORY_ITEM_ID
,rec_cur.ACCOUNTING_DATE
,rec_cur.AMOUNT
,rec_cur.CREATION_DATE
,rec_cur.CREATED_BY
,rec_cur.PO_HEADER_ID
,rec_cur.PO_LINE_LOCATION_ID
,rec_cur.ITEM_DESCRIPTION);
END IF;
l_flag:=NULL;
l_err_MSG:=NULL;
END LOOP;
COMMIT;
END XXPROC_OPENINT;
/


PLS-00402: alias required in SELECT list of cursor to
avoid
duplicate column names