Outbound Interface of Reversed Journals

/*XX_REV_JE_CATEGORY*/




SELECT GJH.JE_HEADER_ID,
GJL.JE_LINE_NUM,
GJH.REVERSED_JE_HEADER_ID,
GJH.JE_BATCH_ID,
GJH.SET_OF_BOOKS_ID,
GJH.JE_CATEGORY,
GJH.JE_SOURCE,
GJH.NAME,
GJH.CURRENCY_CODE,
GJH.CREATION_DATE,
GJH.DESCRIPTION,
GJH.POSTED_DATE,
GJH.EARLIEST_POSTABLE_DATE,
GJH.STATUS,
GJL.CODE_COMBINATION_ID,
GJL.INVOICE_AMOUNT,
GJL.JE_LINE_NUM,
GJL.PERIOD_NAME,
GJL.STATUS
FROM GL_JE_HEADERS GJH,
GL_JE_LINES GJL
WHERE GJH.JE_HEADER_ID=GJL.JE_HEADER_ID
AND GJH.REVERSED_JE_HEADER_ID IS NOT NULL
AND GJH.JE_CATEGORY=:P_CATEGORY;

CREATE OR REPLACE PROCEDURE XX_REV_JOURNAL
(ERRBUF OUT VARCHAR2,
RETCODE OUT VARCHAR2,
P_CATEGORY IN VARCHAR2
)
AS
CURSOR C1 IS SELECT GJH.JE_HEADER_ID,
GJL.JE_LINE_NUM,
GJH.REVERSED_JE_HEADER_ID,
GJH.JE_BATCH_ID,
GJH.SET_OF_BOOKS_ID,
GJH.JE_CATEGORY,
GJH.JE_SOURCE,
GJH.NAME,
GJH.CURRENCY_CODE,
GJH.CREATION_DATE,
GJH.DESCRIPTION,
GJH.POSTED_DATE,
GJH.EARLIEST_POSTABLE_DATE,
GJH.STATUS,
GJL.CODE_COMBINATION_ID,
GJL.INVOICE_AMOUNT,
GJL.PERIOD_NAME,
GJL.STATUS
FROM GL_JE_HEADERS GJH,
GL_JE_LINES GJL
WHERE GJH.JE_HEADER_ID=GJL.JE_HEADER_ID
AND GJH.REVERSED_JE_HEADER_ID IS NOT NULL
AND GJH.JE_CATEGORY=:P_CATEGORY;
REV_ID UTL_FILE.FILE_TYPE;
--L_ID NUMBER(8);
BEGIN
--L_ID :=FND_PROFILE.VALUE('USER_ID');
--FND_FILE.PUT_LINE(FND_FILE.OUTPUT,L_ID);
--DBMS_OUTPUT.PUT_LINE(L_ID);

/*SELECT SUBSTR(VALUE,1,INSTR(VALUE,',')-1)
FROM V$PARAMETER
WHERE UPPER(NAME) LIKE UPPER('UTL_FILE_DIR') */


REV_ID:=UTL_FILE.FOPEN('C:\temp','REV_JE_CATEGORY.TXT','W');
FOR C2 IN C1 LOOP
UTL_FILE.PUT_LINE(REV_ID, C2.JE_HEADER_ID || ',' ||
C2.JE_LINE_NUM || ',' ||
C2.REVERSED_JE_HEADER_ID || ',' ||
C2.JE_BATCH_ID || ',' ||
C2.SET_OF_BOOKS_ID || ',' ||
C2.JE_CATEGORY || ',' ||
C2.JE_SOURCE || ',' ||
C2.CURRENCY_CODE || ',' ||
C2.CREATION_DATE || ',' ||
C2.DESCRIPTION || ',' ||
C2.POSTED_DATE || ',' ||
C2.EARLIEST_POSTABLE_DATE || ',' ||
C2.STATUS || ',' ||
C2.CODE_COMBINATION_ID || ',' ||
C2.INVOICE_AMOUNT || ',' ||
C2.PERIOD_NAME || ',' ||
C2.STATUS);
END LOOP;
UTL_FILE.FCLOSE(REV_ID);
Exception
WHEN utl_file.invalid_operation THEN
fnd_file.put_line(fnd_File.log,'invalid operation');
utl_file.fclose_all;
WHEN utl_file.invalid_path THEN
fnd_file.put_line(fnd_File.log,'invalid path');
utl_file.fclose_all;
WHEN utl_file.invalid_mode THEN
fnd_file.put_line(fnd_File.log,'invalid mode');
utl_file.fclose_all;
WHEN utl_file.invalid_filehandle THEN
fnd_file.put_line(fnd_File.log,'invalid filehandle');
utl_file.fclose_all;
WHEN utl_file.read_error THEN
fnd_file.put_line(fnd_File.log,'read error');
utl_file.fclose_all;
WHEN utl_file.internal_error THEN
fnd_file.put_line(fnd_File.log,'internal error');
utl_file.fclose_all;
WHEN OTHERS THEN
fnd_file.put_line(fnd_File.log,'other error');
utl_file.fclose_all;
END XX_REV_JOURNAL;