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
- 3128 Views
- I also Faced
- E-Mail Answers
Answer / 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 |
What are the different tools available for hot backups in Oracle? Is it preferable to take it manually all the time or it depends on the size of the database?
What is chargeback invoice? How do we create it? : oracle accounts receivable
How would you force a log switch?
can we drop dual table
What is the difference between a TEMPORARY tablespace and a PERMANENT tablespace?
the user should know to which database he is connected currently in oracle
How to find locks exists in a table or not?
why r u using etl tool.
Explain the difference between a hot backup and a cold backup and the benefits associated with each
as a technical developer in oracle applications which type errors feced in your experience sofar
You have just compiled a PL/SQL package but got errors, how would you view the errors?
What is the technical flow associated with P2P or Procure to Pay cycle like the tables, APIs, concurrent programs involved in this cycle. Only technical please.
Oracle General 
