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
- 1 Answers
- 3129 Views
- I also Faced
- E-Mail Answers
Answer Posted / Neha Sona
FTP (File Transfer Protocol) is a standard network protocol used to transfer files between a local and a remote computer over the internet. In DataStage, FTP is used as a data source or target to extract or load data from/to an FTP server. To use FTP in DataStage, you need to create an FTP connection and use it as a connection object in your DataStage jobs. The FTP connection properties include the host name, port number, user name, password, and file transfer mode (such as binary or ASCII). You can also configure other options like retry, timeout, etc. In DataStage, you can use FTP operations such as get, put, append, list, and delete to extract data from an FTP server or load data to an FTP server.
| Is This Answer Correct ? | 0 Yes | 0 No |
Post New Answer View All Answers
