CREATE OR REPLACE PACKAGE APPS.journals_mon_ext
IS
/*************************************************************************
*
* Package: journals_mon_ext
*
*
* Purpose: To implement data extraction process for journals monthly Audit requirements
*
* REF : MD.070 -APPLICATION EXTENSION TECHNICAL DESIGN
*
* $Author: xibmo61
* $Date:
* $Modified: $APR-18-2011
* $Revision: 1.0
*
* Modification History:
*
* Date Author Version Description
* -----------------------------------------------------------------------
*NOV-19-2008 Chandra Sekhar kadali 1.0 Initial Development
************************************************************************/
PROCEDURE extract_lt_gl_je_lines(
errbuf OUT VARCHAR2
,retcode OUT NUMBER,
p_period_name IN VARCHAR2
);
PROCEDURE extract_lt_gl_je_headers(
errbuf OUT VARCHAR2
,retcode OUT NUMBER,
p_period_name IN VARCHAR2
);
PROCEDURE extract_lt_gl_je_batches( errbuf OUT VARCHAR2
,retcode OUT NUMBER,
p_period_name IN VARCHAR2
);
END;
/
CREATE OR REPLACE PACKAGE BODY APPS.lt_journals_mon_ext
AS
PROCEDURE extract_lt_gl_je_lines(
errbuf OUT VARCHAR2
,retcode OUT NUMBER
,p_period_name IN VARCHAR2
)
IS
v_file_handler UTL_FILE.file_type;
CURSOR lt_gl_je_l_cur
IS
SELECT gl.*
FROM gl_je_lines gl
,gl_code_combinations gc
WHERE gl.period_name = p_period_name AND gl.set_of_books_id = 2 AND gl.code_combination_id = gc.code_combination_id AND gc.segment1 = '21';
-- To Include LT ALSO
l_errbuf VARCHAR2(3000);
l_retcode NUMBER;
v_file_name VARCHAR2(50);
v_profile_path VARCHAR2(100);
v_rec_txt VARCHAR2(32000);
v_rec_header VARCHAR2(32000);
v_record_count NUMBER;
l_line_dr NUMBER;
l_line_cr NUMBER;
l_journal_debit NUMBER;
l_journal_credit NUMBER;
l_total NUMBER;
l_journal_debit_y NUMBER;
l_journal_credit_y NUMBER;
l_total_y NUMBER;
l_par VARCHAR2(100);
l_full VARCHAR2(100);
BEGIN
v_file_name := 'xxlt_dt_gl_je_lines_' || REPLACE(p_period_name, '-', '') || '.dat';
SELECT 'P01-' || SUBSTR(p_period_name, 5, 2)
INTO l_par
FROM DUAL;
SELECT SUM(accounted_dr)
,SUM(accounted_cr)
INTO l_line_dr
,l_line_cr
FROM gl_je_lines gl
,gl_code_combinations gc
WHERE gl.period_name = p_period_name AND gl.set_of_books_id = 2 AND gl.code_combination_id = gc.code_combination_id AND gc.segment1 = '21';
SELECT SUM(a.accounted_dr) journal_debit
,SUM(a.accounted_cr) journal_credit
,(SUM(a.accounted_dr) - SUM(a.accounted_cr)) total
INTO l_journal_debit
,l_journal_credit
,l_total
FROM gl.gl_je_lines a
,gl.gl_code_combinations b
,gl.gl_je_headers d
WHERE a.period_name = p_period_name --IN('P09-09', 'P10-09', 'P11-09', 'P12-09')
AND a.set_of_books_id = 2
AND a.code_combination_id = b.code_combination_id
AND b.segment1 = '21'
AND a.status = 'P'
AND a.je_header_id = d.je_header_id
AND NOT(d.NAME LIKE 'MV%')
AND NOT(d.NAME LIKE 'Move/Merge%')
GROUP BY a.period_name;
SELECT SUM(a.accounted_dr) journal_debit
,SUM(a.accounted_cr) journal_credit
,(SUM(a.accounted_dr) - SUM(a.accounted_cr)) total
INTO l_journal_debit_y
,l_journal_credit_y
,l_total_y
FROM gl.gl_je_lines a
,gl.gl_code_combinations b
,gl.gl_je_headers d
WHERE a.period_name BETWEEN l_par AND p_period_name --IN('P09-09', 'P10-09', 'P11-09', 'P12-09')
AND a.set_of_books_id = 2
AND a.code_combination_id = b.code_combination_id
AND b.segment1 = '21'
AND a.status = 'P'
AND a.je_header_id = d.je_header_id
AND NOT(d.NAME LIKE 'MV%')
AND NOT(d.NAME LIKE 'Move/Merge%');
fnd_profile.get('XXHBCDATA_TOP', v_profile_path);
v_profile_path := v_profile_path || '/outbound';
v_file_handler := UTL_FILE.fopen(v_profile_path, v_file_name, 'W', 32000);
v_rec_header :=
'JE_HEADER_ID|JE_LINE_NUM|LAST_UPDATE_DATE|LAST_UPDATED_BY|SET_OF_BOOKS_ID|CODE_COMBINATION_ID|PERIOD_NAME|EFFECTIVE_DATE|STATUS|CREATION_DATE|CREATED_BY|LAST_UPDATE_LOGIN|ACCOUNTED_DR|ACCOUNTED_CR|DESCRIPTION|';
UTL_FILE.put_line(v_file_handler, v_rec_header);
v_record_count := 0;
fnd_file.put_line(fnd_file.LOG, 'START OF EXTRACT Current System Time: ' || TO_CHAR(SYSDATE, 'HH24:MI:SS'));
fnd_file.put_line(fnd_file.LOG, 'Extracting Into: ' || v_file_name);
fnd_file.put_line(fnd_file.LOG, ' ');
-- Opens the cursor and fetches the rows
FOR vcur_lt_gl_je_l IN lt_gl_je_l_cur
LOOP
v_rec_txt :=
REPLACE(REPLACE( vcur_lt_gl_je_l.je_header_id
|| '|'
|| vcur_lt_gl_je_l.je_line_num
|| '|'
|| TO_CHAR(vcur_lt_gl_je_l.last_update_date, 'DD-MON-YY HH:MI:SS PM')
|| '|'
|| vcur_lt_gl_je_l.last_updated_by
|| '|'
|| vcur_lt_gl_je_l.set_of_books_id
|| '|'
|| vcur_lt_gl_je_l.code_combination_id
|| '|'
|| vcur_lt_gl_je_l.period_name
|| '|'
|| vcur_lt_gl_je_l.effective_date
|| '|'
|| vcur_lt_gl_je_l.status
|| '|'
|| TO_CHAR(vcur_lt_gl_je_l.creation_date, 'DD-MON-YY HH:MI:SS PM')
|| '|'
|| vcur_lt_gl_je_l.created_by
|| '|'
|| vcur_lt_gl_je_l.last_update_login
|| '|'
|| vcur_lt_gl_je_l.accounted_dr
|| '|'
|| vcur_lt_gl_je_l.accounted_cr
|| '|'
|| vcur_lt_gl_je_l.description
|| '|'
,CHR(10)
,' '
)
,CHR(13)
,' '
);
UTL_FILE.put_line(v_file_handler, v_rec_txt);
--vcur_lt_gl_je_l.je_header_id||'|'||vcur_lt_gl_je_l.je_line_num||'|'||TO_CHAR(vcur_lt_gl_je_l.last_update_date,'DD-MON-YY HH:MI:SS PM')||'|'||vcur_lt_gl_je_l.last_updated_by||'|'||vcur_lt_gl_je_l.set_of_books_id||'|'||vcur_lt_gl_je_l.code_combination_id||'|'||vcur_lt_gl_je_l.period_name||'|'||vcur_lt_gl_je_l.effective_date||'|'||vcur_lt_gl_je_l.status||'|'||TO_CHAR(vcur_lt_gl_je_l.creation_date, 'DD-MON-YY HH:MI:SS PM')||'|'||vcur_lt_gl_je_l.created_by||'|'||vcur_lt_gl_je_l.last_update_login||'|'||vcur_lt_gl_je_l.accounted_dr||'|'||vcur_lt_gl_je_l.accounted_cr||'|'||vcur_lt_gl_je_l.description||'|');
v_record_count := v_record_count + 1;
END LOOP;
fnd_file.put_line(fnd_file.output, '------------------------------***********************************----------------------------------------');
fnd_file.put_line(fnd_file.output, '---------------------------------------------------------------------------------------------------------');
fnd_file.put_line(fnd_file.output, ' Lord and Taylor ');
fnd_file.put_line(fnd_file.output
, 'Program: LT Journal Extract Monthly IRS Extract - GL Journals Rundate :' || TRUNC(SYSDATE));
fnd_file.put_line(fnd_file.output, ' For Fiscal Month:' || p_period_name);
fnd_file.put_line(fnd_file.output
,' ');
fnd_file.put_line(fnd_file.output
,' ');
fnd_file.put_line(fnd_file.output, ' TOTAL DEBITS TOTAL CREDITS ');
fnd_file.put_line(fnd_file.output
, 'Current Period $'
|| TRIM(TO_CHAR(l_journal_debit, '999,999,999,999,999,999.99'))
|| ' $'
|| TRIM(TO_CHAR(l_journal_credit, '999,999,999,999,999,999.99'))
);
fnd_file.put_line(fnd_file.output
,' ');
fnd_file.put_line(fnd_file.output
, 'Year To Date $'
|| TRIM(TO_CHAR(l_journal_debit_y, '999,999,999,999,999,999.99'))
|| ' $'
|| TRIM(TO_CHAR(l_journal_credit_y, '999,999,999,999,999,999.99'))
);
fnd_file.put_line(fnd_file.output
,' ');
fnd_file.put_line(fnd_file.output
,' ');
fnd_file.put_line(fnd_file.output
,'Approved By:_________________________________________________ Date:________________ ');
fnd_file.put_line(fnd_file.output
,' ');
fnd_file.put_line(fnd_file.output
,' ');
fnd_file.put_line(fnd_file.output
,' ');
fnd_file.put_line(fnd_file.output, '---------------------------------------------------------------------------------------------------------');
fnd_file.put_line(fnd_file.output, '------------------------------***********************************----------------------------------------');
/* fnd_file.put_line(fnd_file.output, '----------------*************************----------------');
fnd_file.put_line(fnd_file.output, '---------------------------------------------------------');
fnd_file.put_line(fnd_file.output, ' Summary Report For GL_JE_LINES');
fnd_file.put_line(fnd_file.output, '---------------------------------------------------------');
fnd_file.put_line(fnd_file.output, 'File Name : ' || v_file_name);
fnd_file.put_line(fnd_file.output, 'Total Credits : ' || l_line_cr);
fnd_file.put_line(fnd_file.output, 'Total Debits : ' || l_line_dr);
fnd_file.put_line(fnd_file.output, 'Total Records Extracted: ' || v_record_count);
fnd_file.put_line(fnd_file.output, 'Current System Time: ' || TO_CHAR(SYSDATE, 'HH24:MI:SS'));
fnd_file.put_line(fnd_file.output, '---------------------------------------------------------');
fnd_file.put_line(fnd_file.output, '----------------*************************----------------'); */
UTL_FILE.put_line(v_file_handler, 'Record_Count:' || v_record_count);
fnd_file.put_line(fnd_file.LOG, 'Total Records Extracted: ' || v_record_count);
fnd_file.put_line(fnd_file.LOG, 'END OF EXTRACT Current System Time: ' || TO_CHAR(SYSDATE, 'HH24:MI:SS'));
UTL_FILE.fclose(v_file_handler);
extract_lt_gl_je_headers(l_errbuf, l_retcode, p_period_name);
EXCEPTION
WHEN UTL_FILE.invalid_mode
THEN
fnd_file.put_line(fnd_file.LOG, ' Error, 29281 Invalid Mode Parameter, The open_mode parameter in FOPEN is invalid');
WHEN UTL_FILE.invalid_path
THEN
fnd_file.put_line(fnd_file.LOG, 'Error, 29280 Invalid File Location, Specified path does not exist or is not visible to Oracle');
WHEN UTL_FILE.invalid_filehandle
THEN
fnd_file.put_line(fnd_file.LOG, 'Error, 29282 Invalid Filehandle, File handle does not exist');
WHEN UTL_FILE.invalid_operation
THEN
fnd_file.put_line(fnd_file.LOG, 'Error, 29283 Invalid Operation, File could not be opened or operated on as requested');
WHEN UTL_FILE.internal_error
THEN
fnd_file.put_line(fnd_file.LOG, 'Internal Error, 29286 Unhandled internal error in the UTL_FILE package');
WHEN UTL_FILE.charsetmismatch
THEN
fnd_file.put_line
(fnd_file.LOG
,'ERROR, 29298 A file is opened using FOPEN_NCHAR, but later I/O operations use nonchar functions such as PUTF or GET_LINE'
);
WHEN UTL_FILE.file_open
THEN
fnd_file.put_line(fnd_file.LOG, 'ERROR, File Already Opened');
WHEN UTL_FILE.invalid_filename
THEN
fnd_file.put_line(fnd_file.LOG, 'Error, 29288 Invalid File Name, A file with the specified name does not exist in the path');
WHEN UTL_FILE.access_denied
THEN
fnd_file.put_line(fnd_file.LOG, 'Error, 29289 Access to the file has been denied by the operating system');
--- Default exception if all the above failed to handel
WHEN OTHERS
THEN
errbuf := SUBSTR(SQLERRM, 1, 150);
retcode := 1;
fnd_file.put_line(fnd_file.LOG, ' LAST EXCEPTION HIT (OTHER EXCEPTION) IN MAIN PROCEDURE, Unknown UTL_FILE Error');
fnd_file.put_line(fnd_file.LOG, ' UNHANDLED EXCEPTION/p_errbuf = ' || errbuf);
fnd_file.put_line(fnd_file.LOG, ' UNHANDLED EXCEPTION/p_retcode = ' || retcode);
END extract_lt_gl_je_lines;
PROCEDURE extract_lt_gl_je_headers(
errbuf OUT VARCHAR2
,retcode OUT NUMBER
,p_period_name IN VARCHAR2
)
IS
v_file_handler UTL_FILE.file_type;
CURSOR gl_je_h_cur
IS
SELECT DISTINCT js.user_je_source_name
,js.description source_description
,jh.*
FROM gl_je_headers jh
,gl_je_sources js
,gl_je_lines jl
,gl_code_combinations jb
WHERE jh.period_name = p_period_name
AND jh.je_source = js.je_source_name
AND jh.set_of_books_id = 2
AND jl.code_combination_id = jb.code_combination_id
AND jl.je_header_id = jh.je_header_id
AND jl.period_name = jh.period_name
AND jb.segment1 = '21';
-- Modified by Chandra - for LT Changes
v_file_name VARCHAR2(50);
v_profile_path VARCHAR2(100);
v_record_count NUMBER;
v_rec_txt VARCHAR2(32000);
v_rec_header VARCHAR2(32000);
l_jh_dr NUMBER;
l_jh_cr NUMBER;
l_errbuf VARCHAR2(3000);
l_retcode NUMBER;
BEGIN
v_file_name := 'xxlt_dt_gl_je_headers_' || REPLACE(p_period_name, '-', '') || '.dat';
SELECT SUM(jh.running_total_dr)
,SUM(jh.running_total_cr)
INTO l_jh_dr
,l_jh_cr
FROM gl_je_headers jh
,gl_je_sources js
,gl_je_lines jl
,gl_code_combinations jb
WHERE jh.period_name = p_period_name
AND jh.je_source = js.je_source_name
AND jh.set_of_books_id = 2
AND jl.code_combination_id = jb.code_combination_id
AND jl.je_header_id = jh.je_header_id
AND jl.period_name = jh.period_name
AND jb.segment1 = '21';
fnd_profile.get('XXHBCDATA_TOP', v_profile_path);
v_profile_path := v_profile_path || '/outbound';
v_file_handler := UTL_FILE.fopen(v_profile_path, v_file_name, 'W', 32000);
v_rec_header :=
'JE_HEADER_ID|LAST_UPDATE_DATE|LAST_UPDATED_BY|SET_OF_BOOKS_ID|JE_CATEGORY|JE_SOURCE|PERIOD_NAME|NAME|CURRENCY_CODE|STATUS|DATE_CREATED|CREATION_DATE|CREATED_BY|LAST_UPDATE_LOGIN|BALANCED_JE_FLAG|BALANCING_SEGMENT_VALUE|JE_BATCH_ID|FROM_RECURRING_HEADER_ID|EARLIEST_POSTABLE_DATE|POSTED_DATE|DESCRIPTION|CURRENCY_CONVERSION_RATE|CURRENCY_CONVERSION_TYPE|CURRENCY_CONVERSION_DATE|PARENT_JE_HEADER_ID|REVERSED_JE_HEADER_ID|INTERCOMPANY_MODE|USER_JE_SOURCE_NAME|SOURCE_DESCRIPTION|';
UTL_FILE.put_line(v_file_handler, v_rec_header);
v_record_count := 0;
fnd_file.put_line(fnd_file.LOG, 'START OF EXTRACT Current System Time: ' || TO_CHAR(SYSDATE, 'HH24:MI:SS'));
fnd_file.put_line(fnd_file.LOG, 'Extracting Into: ' || v_file_name);
fnd_file.put_line(fnd_file.LOG, ' ');
FOR vcur_gl_je_h IN gl_je_h_cur
LOOP
v_rec_txt :=
REPLACE(REPLACE( vcur_gl_je_h.je_header_id
|| '|'
|| TO_CHAR(vcur_gl_je_h.last_update_date, 'DD-MON-YY HH:MI:SS PM')
|| '|'
|| vcur_gl_je_h.last_updated_by
|| '|'
|| vcur_gl_je_h.set_of_books_id
|| '|'
|| vcur_gl_je_h.je_category
|| '|'
|| vcur_gl_je_h.je_source
|| '|'
|| vcur_gl_je_h.period_name
|| '|'
|| vcur_gl_je_h.NAME
|| '|'
|| vcur_gl_je_h.currency_code
|| '|'
|| vcur_gl_je_h.status
|| '|'
|| TO_CHAR(vcur_gl_je_h.date_created, 'DD-MON-YY HH:MI:SS PM')
|| '|'
|| TO_CHAR(vcur_gl_je_h.creation_date, 'DD-MON-YY HH:MI:SS PM')
|| '|'
|| vcur_gl_je_h.created_by
|| '|'
|| vcur_gl_je_h.last_update_login
|| '|'
|| vcur_gl_je_h.balanced_je_flag
|| '|'
|| vcur_gl_je_h.balancing_segment_value
|| '|'
|| vcur_gl_je_h.je_batch_id
|| '|'
|| vcur_gl_je_h.from_recurring_header_id
|| '|'
|| TO_CHAR(vcur_gl_je_h.earliest_postable_date, 'DD-MON-YY HH:MI:SS PM')
|| '|'
|| TO_CHAR(vcur_gl_je_h.posted_date, 'DD-MON-YY HH:MI:SS PM')
|| '|'
|| vcur_gl_je_h.description
|| '|'
|| vcur_gl_je_h.currency_conversion_rate
|| '|'
|| vcur_gl_je_h.currency_conversion_type
|| '|'
|| vcur_gl_je_h.currency_conversion_date
|| '|'
|| vcur_gl_je_h.parent_je_header_id
|| '|'
|| vcur_gl_je_h.reversed_je_header_id
|| '|'
|| vcur_gl_je_h.intercompany_mode
|| '|'
|| vcur_gl_je_h.user_je_source_name
|| '|'
|| vcur_gl_je_h.source_description
|| '|'
,CHR(10)
,' '
)
,CHR(13)
,' '
);
UTL_FILE.put_line(v_file_handler, v_rec_txt);
v_record_count := v_record_count + 1;
END LOOP;
/* fnd_file.put_line(fnd_file.output, '----------------*************************----------------');
fnd_file.put_line(fnd_file.output, '---------------------------------------------------------');
fnd_file.put_line(fnd_file.output, ' Summary Report For GL_JE_HEADERS');
fnd_file.put_line(fnd_file.output, '---------------------------------------------------------');
fnd_file.put_line(fnd_file.output, 'File Name : ' || v_file_name);
fnd_file.put_line(fnd_file.output, 'Total Credits : ' || l_jh_dr);
fnd_file.put_line(fnd_file.output, 'Total Debits : ' || l_jh_cr);
fnd_file.put_line(fnd_file.output, 'Total Records Extracted: ' || v_record_count);
fnd_file.put_line(fnd_file.output, 'Current System Time: ' || TO_CHAR(SYSDATE, 'HH24:MI:SS'));
fnd_file.put_line(fnd_file.output, '---------------------------------------------------------');
fnd_file.put_line(fnd_file.output, '----------------*************************----------------');*/
UTL_FILE.put_line(v_file_handler, 'Record_Count:' || v_record_count);
fnd_file.put_line(fnd_file.LOG, 'Total Records Extracted: ' || v_record_count);
fnd_file.put_line(fnd_file.LOG, 'END OF EXTRACT Current System Time: ' || TO_CHAR(SYSDATE, 'HH24:MI:SS'));
UTL_FILE.fclose(v_file_handler);
extract_lt_gl_je_batches(l_errbuf, l_retcode, p_period_name);
EXCEPTION
WHEN UTL_FILE.invalid_mode
THEN
fnd_file.put_line(fnd_file.LOG, ' Error, 29281 Invalid Mode Parameter, The open_mode parameter in FOPEN is invalid');
WHEN UTL_FILE.invalid_path
THEN
fnd_file.put_line(fnd_file.LOG, 'Error, 29280 Invalid File Location, Specified path does not exist or is not visible to Oracle');
WHEN UTL_FILE.invalid_filehandle
THEN
fnd_file.put_line(fnd_file.LOG, 'Error, 29282 Invalid Filehandle, File handle does not exist');
WHEN UTL_FILE.invalid_operation
THEN
fnd_file.put_line(fnd_file.LOG, 'Error, 29283 Invalid Operation, File could not be opened or operated on as requested');
WHEN UTL_FILE.internal_error
THEN
fnd_file.put_line(fnd_file.LOG, 'Internal Error, 29286 Unhandled internal error in the UTL_FILE package');
WHEN UTL_FILE.charsetmismatch
THEN
fnd_file.put_line
(fnd_file.LOG
,'ERROR, 29298 A file is opened using FOPEN_NCHAR, but later I/O operations use nonchar functions such as PUTF or GET_LINE'
);
WHEN UTL_FILE.file_open
THEN
fnd_file.put_line(fnd_file.LOG, 'ERROR, File Already Opened');
WHEN UTL_FILE.invalid_filename
THEN
fnd_file.put_line(fnd_file.LOG, 'Error, 29288 Invalid File Name, A file with the specified name does not exist in the path');
WHEN UTL_FILE.access_denied
THEN
fnd_file.put_line(fnd_file.LOG, 'Error, 29289 Access to the file has been denied by the operating system');
--- Default exception if all the above failed to handel
WHEN OTHERS
THEN
errbuf := SUBSTR(SQLERRM, 1, 150);
retcode := 1;
fnd_file.put_line(fnd_file.LOG, ' LAST EXCEPTION HIT (OTHER EXCEPTION) IN MAIN PROCEDURE, Unknown UTL_FILE Error');
fnd_file.put_line(fnd_file.LOG, ' UNHANDLED EXCEPTION/p_errbuf = ' || errbuf);
fnd_file.put_line(fnd_file.LOG, ' UNHANDLED EXCEPTION/p_retcode = ' || retcode);
END extract_lt_gl_je_headers;
PROCEDURE extract_lt_gl_je_batches(
errbuf OUT VARCHAR2
,retcode OUT NUMBER
,p_period_name IN VARCHAR2
)
IS
v_file_handler UTL_FILE.file_type;
CURSOR gl_je_b_cur
IS
SELECT jb.je_batch_id
,jb.NAME
,jb.status
,jb.approval_status_code
,jb.created_by
,fu1.user_name created_by_user
,fu1.description created_by_user_name
,jb.last_updated_by
,fu2.user_name last_updated_by_user
,fu2.description last_updated_by_user_name
FROM gl_je_batches jb
,fnd_user fu1
,fnd_user fu2
,gl_je_lines jl
,gl_je_headers jh
,gl_code_combinations jc
WHERE jb.default_period_name = p_period_name
AND jb.created_by = fu1.user_id
AND jb.last_updated_by = fu2.user_id
AND jb.set_of_books_id = 2
AND jl.je_header_id = jh.je_header_id
AND jh.je_batch_id = jb.je_batch_id
AND jl.set_of_books_id = jh.set_of_books_id
AND jc.segment1 = '21'
AND jl.period_name = jh.period_name
AND jc.code_combination_id = jl.code_combination_id;
-- Modified by Narasimha G Devalaraju - for LT Changes
v_file_name VARCHAR2(50);
v_profile_path VARCHAR2(100);
v_record_count NUMBER;
v_rec_txt VARCHAR2(32000);
v_rec_header VARCHAR2(32000);
l_bat_dr NUMBER;
l_bat_cr NUMBER;
BEGIN
v_file_name := 'xxlt_dt_gl_je_batches_' || REPLACE(p_period_name, '-', '') || '.dat';
SELECT SUM(jb.running_total_dr)
,SUM(jb.running_total_cr)
INTO l_bat_dr
,l_bat_cr
FROM gl_je_batches jb
,fnd_user fu1
,fnd_user fu2
,gl_je_lines jl
,gl_je_headers jh
,gl_code_combinations jc
WHERE jb.default_period_name = p_period_name
AND jb.created_by = fu1.user_id
AND jb.last_updated_by = fu2.user_id
AND jb.set_of_books_id = 2
AND jl.je_header_id = jh.je_header_id
AND jh.je_batch_id = jb.je_batch_id
AND jl.set_of_books_id = jh.set_of_books_id
AND jc.segment1 = '21'
AND jl.period_name = jh.period_name
AND jc.code_combination_id = jl.code_combination_id;
fnd_profile.get('XXHBCDATA_TOP', v_profile_path);
v_profile_path := v_profile_path || '/outbound';
v_file_handler := UTL_FILE.fopen(v_profile_path, v_file_name, 'W', 32000);
v_rec_header :=
'JE_BATCH_ID|NAME|STATUS|APPROVAL_STATUS_CODE|CREATED_BY|CREATED_BY_USER|CREATED_BY_USER_NAME|LAST_UPDATED_BY|LAST_UPDATED_BY_USER|LAST_UPDATED_BY_USER_NAME|';
UTL_FILE.put_line(v_file_handler, v_rec_header);
v_record_count := 0;
fnd_file.put_line(fnd_file.LOG, 'START OF EXTRACT Current System Time: ' || TO_CHAR(SYSDATE, 'HH24:MI:SS'));
fnd_file.put_line(fnd_file.LOG, 'Extracting Into: ' || v_file_name);
fnd_file.put_line(fnd_file.LOG, ' ');
FOR vcur_gl_je_b IN gl_je_b_cur
LOOP
v_rec_txt :=
REPLACE(REPLACE( vcur_gl_je_b.je_batch_id
|| '|'
|| vcur_gl_je_b.NAME
|| '|'
|| vcur_gl_je_b.status
|| '|'
|| vcur_gl_je_b.approval_status_code
|| '|'
|| vcur_gl_je_b.created_by
|| '|'
|| vcur_gl_je_b.created_by_user
|| '|'
|| vcur_gl_je_b.created_by_user_name
|| '|'
|| vcur_gl_je_b.last_updated_by
|| '|'
|| vcur_gl_je_b.last_updated_by_user
|| '|'
|| vcur_gl_je_b.last_updated_by_user_name
|| '|'
,CHR(10)
,' '
)
,CHR(13)
,' '
);
UTL_FILE.put_line(v_file_handler, v_rec_txt);
v_record_count := v_record_count + 1;
END LOOP;
/*fnd_file.put_line(fnd_file.output, '----------------*************************----------------');
fnd_file.put_line(fnd_file.output, '---------------------------------------------------------');
fnd_file.put_line(fnd_file.output, ' Summary Report For GL_JE_BATCHES');
fnd_file.put_line(fnd_file.output, '---------------------------------------------------------');
fnd_file.put_line(fnd_file.output, 'File Name : ' || v_file_name);
fnd_file.put_line(fnd_file.output, 'Total Credits : ' || l_bat_cr);
fnd_file.put_line(fnd_file.output, 'Total Debits : ' || l_bat_dr);
fnd_file.put_line(fnd_file.output, 'Total Records Extracted: ' || v_record_count);
fnd_file.put_line(fnd_file.output, 'Current System Time: ' || TO_CHAR(SYSDATE, 'HH24:MI:SS'));
fnd_file.put_line(fnd_file.output, '---------------------------------------------------------');
fnd_file.put_line(fnd_file.output, '----------------*************************----------------');*/
UTL_FILE.put_line(v_file_handler, 'Record_Count:' || v_record_count);
fnd_file.put_line(fnd_file.LOG, 'Total Records Extracted: ' || v_record_count);
fnd_file.put_line(fnd_file.LOG, 'END OF EXTRACT Current System Time: ' || TO_CHAR(SYSDATE, 'HH24:MI:SS'));
UTL_FILE.fclose(v_file_handler);
EXCEPTION
WHEN UTL_FILE.invalid_mode
THEN
fnd_file.put_line(fnd_file.LOG, ' Error, 29281 Invalid Mode Parameter, The open_mode parameter in FOPEN is invalid');
WHEN UTL_FILE.invalid_path
THEN
fnd_file.put_line(fnd_file.LOG, 'Error, 29280 Invalid File Location, Specified path does not exist or is not visible to Oracle');
WHEN UTL_FILE.invalid_filehandle
THEN
fnd_file.put_line(fnd_file.LOG, 'Error, 29282 Invalid Filehandle, File handle does not exist');
WHEN UTL_FILE.invalid_operation
THEN
fnd_file.put_line(fnd_file.LOG, 'Error, 29283 Invalid Operation, File could not be opened or operated on as requested');
WHEN UTL_FILE.internal_error
THEN
fnd_file.put_line(fnd_file.LOG, 'Internal Error, 29286 Unhandled internal error in the UTL_FILE package');
WHEN UTL_FILE.charsetmismatch
THEN
fnd_file.put_line
(fnd_file.LOG
,'ERROR, 29298 A file is opened using FOPEN_NCHAR, but later I/O operations use nonchar functions such as PUTF or GET_LINE'
);
WHEN UTL_FILE.file_open
THEN
fnd_file.put_line(fnd_file.LOG, 'ERROR, File Already Opened');
WHEN UTL_FILE.invalid_filename
THEN
fnd_file.put_line(fnd_file.LOG, 'Error, 29288 Invalid File Name, A file with the specified name does not exist in the path');
WHEN UTL_FILE.access_denied
THEN
fnd_file.put_line(fnd_file.LOG, 'Error, 29289 Access to the file has been denied by the operating system');
--- Default exception if all the above failed to handel
WHEN OTHERS
THEN
errbuf := SUBSTR(SQLERRM, 1, 150);
retcode := 1;
fnd_file.put_line(fnd_file.LOG, ' LAST EXCEPTION HIT (OTHER EXCEPTION) IN MAIN PROCEDURE, Unknown UTL_FILE Error');
fnd_file.put_line(fnd_file.LOG, ' UNHANDLED EXCEPTION/p_errbuf = ' || errbuf);
fnd_file.put_line(fnd_file.LOG, ' UNHANDLED EXCEPTION/p_retcode = ' || retcode);
END extract_lt_gl_je_batches;
END journals_mon_ext;
--- End of the package body
/
exit
/
IS
/*************************************************************************
*
* Package: journals_mon_ext
*
*
* Purpose: To implement data extraction process for journals monthly Audit requirements
*
* REF : MD.070 -APPLICATION EXTENSION TECHNICAL DESIGN
*
* $Author: xibmo61
* $Date:
* $Modified: $APR-18-2011
* $Revision: 1.0
*
* Modification History:
*
* Date Author Version Description
* -----------------------------------------------------------------------
*NOV-19-2008 Chandra Sekhar kadali 1.0 Initial Development
************************************************************************/
PROCEDURE extract_lt_gl_je_lines(
errbuf OUT VARCHAR2
,retcode OUT NUMBER,
p_period_name IN VARCHAR2
);
PROCEDURE extract_lt_gl_je_headers(
errbuf OUT VARCHAR2
,retcode OUT NUMBER,
p_period_name IN VARCHAR2
);
PROCEDURE extract_lt_gl_je_batches( errbuf OUT VARCHAR2
,retcode OUT NUMBER,
p_period_name IN VARCHAR2
);
END;
/
CREATE OR REPLACE PACKAGE BODY APPS.lt_journals_mon_ext
AS
PROCEDURE extract_lt_gl_je_lines(
errbuf OUT VARCHAR2
,retcode OUT NUMBER
,p_period_name IN VARCHAR2
)
IS
v_file_handler UTL_FILE.file_type;
CURSOR lt_gl_je_l_cur
IS
SELECT gl.*
FROM gl_je_lines gl
,gl_code_combinations gc
WHERE gl.period_name = p_period_name AND gl.set_of_books_id = 2 AND gl.code_combination_id = gc.code_combination_id AND gc.segment1 = '21';
-- To Include LT ALSO
l_errbuf VARCHAR2(3000);
l_retcode NUMBER;
v_file_name VARCHAR2(50);
v_profile_path VARCHAR2(100);
v_rec_txt VARCHAR2(32000);
v_rec_header VARCHAR2(32000);
v_record_count NUMBER;
l_line_dr NUMBER;
l_line_cr NUMBER;
l_journal_debit NUMBER;
l_journal_credit NUMBER;
l_total NUMBER;
l_journal_debit_y NUMBER;
l_journal_credit_y NUMBER;
l_total_y NUMBER;
l_par VARCHAR2(100);
l_full VARCHAR2(100);
BEGIN
v_file_name := 'xxlt_dt_gl_je_lines_' || REPLACE(p_period_name, '-', '') || '.dat';
SELECT 'P01-' || SUBSTR(p_period_name, 5, 2)
INTO l_par
FROM DUAL;
SELECT SUM(accounted_dr)
,SUM(accounted_cr)
INTO l_line_dr
,l_line_cr
FROM gl_je_lines gl
,gl_code_combinations gc
WHERE gl.period_name = p_period_name AND gl.set_of_books_id = 2 AND gl.code_combination_id = gc.code_combination_id AND gc.segment1 = '21';
SELECT SUM(a.accounted_dr) journal_debit
,SUM(a.accounted_cr) journal_credit
,(SUM(a.accounted_dr) - SUM(a.accounted_cr)) total
INTO l_journal_debit
,l_journal_credit
,l_total
FROM gl.gl_je_lines a
,gl.gl_code_combinations b
,gl.gl_je_headers d
WHERE a.period_name = p_period_name --IN('P09-09', 'P10-09', 'P11-09', 'P12-09')
AND a.set_of_books_id = 2
AND a.code_combination_id = b.code_combination_id
AND b.segment1 = '21'
AND a.status = 'P'
AND a.je_header_id = d.je_header_id
AND NOT(d.NAME LIKE 'MV%')
AND NOT(d.NAME LIKE 'Move/Merge%')
GROUP BY a.period_name;
SELECT SUM(a.accounted_dr) journal_debit
,SUM(a.accounted_cr) journal_credit
,(SUM(a.accounted_dr) - SUM(a.accounted_cr)) total
INTO l_journal_debit_y
,l_journal_credit_y
,l_total_y
FROM gl.gl_je_lines a
,gl.gl_code_combinations b
,gl.gl_je_headers d
WHERE a.period_name BETWEEN l_par AND p_period_name --IN('P09-09', 'P10-09', 'P11-09', 'P12-09')
AND a.set_of_books_id = 2
AND a.code_combination_id = b.code_combination_id
AND b.segment1 = '21'
AND a.status = 'P'
AND a.je_header_id = d.je_header_id
AND NOT(d.NAME LIKE 'MV%')
AND NOT(d.NAME LIKE 'Move/Merge%');
fnd_profile.get('XXHBCDATA_TOP', v_profile_path);
v_profile_path := v_profile_path || '/outbound';
v_file_handler := UTL_FILE.fopen(v_profile_path, v_file_name, 'W', 32000);
v_rec_header :=
'JE_HEADER_ID|JE_LINE_NUM|LAST_UPDATE_DATE|LAST_UPDATED_BY|SET_OF_BOOKS_ID|CODE_COMBINATION_ID|PERIOD_NAME|EFFECTIVE_DATE|STATUS|CREATION_DATE|CREATED_BY|LAST_UPDATE_LOGIN|ACCOUNTED_DR|ACCOUNTED_CR|DESCRIPTION|';
UTL_FILE.put_line(v_file_handler, v_rec_header);
v_record_count := 0;
fnd_file.put_line(fnd_file.LOG, 'START OF EXTRACT Current System Time: ' || TO_CHAR(SYSDATE, 'HH24:MI:SS'));
fnd_file.put_line(fnd_file.LOG, 'Extracting Into: ' || v_file_name);
fnd_file.put_line(fnd_file.LOG, ' ');
-- Opens the cursor and fetches the rows
FOR vcur_lt_gl_je_l IN lt_gl_je_l_cur
LOOP
v_rec_txt :=
REPLACE(REPLACE( vcur_lt_gl_je_l.je_header_id
|| '|'
|| vcur_lt_gl_je_l.je_line_num
|| '|'
|| TO_CHAR(vcur_lt_gl_je_l.last_update_date, 'DD-MON-YY HH:MI:SS PM')
|| '|'
|| vcur_lt_gl_je_l.last_updated_by
|| '|'
|| vcur_lt_gl_je_l.set_of_books_id
|| '|'
|| vcur_lt_gl_je_l.code_combination_id
|| '|'
|| vcur_lt_gl_je_l.period_name
|| '|'
|| vcur_lt_gl_je_l.effective_date
|| '|'
|| vcur_lt_gl_je_l.status
|| '|'
|| TO_CHAR(vcur_lt_gl_je_l.creation_date, 'DD-MON-YY HH:MI:SS PM')
|| '|'
|| vcur_lt_gl_je_l.created_by
|| '|'
|| vcur_lt_gl_je_l.last_update_login
|| '|'
|| vcur_lt_gl_je_l.accounted_dr
|| '|'
|| vcur_lt_gl_je_l.accounted_cr
|| '|'
|| vcur_lt_gl_je_l.description
|| '|'
,CHR(10)
,' '
)
,CHR(13)
,' '
);
UTL_FILE.put_line(v_file_handler, v_rec_txt);
--vcur_lt_gl_je_l.je_header_id||'|'||vcur_lt_gl_je_l.je_line_num||'|'||TO_CHAR(vcur_lt_gl_je_l.last_update_date,'DD-MON-YY HH:MI:SS PM')||'|'||vcur_lt_gl_je_l.last_updated_by||'|'||vcur_lt_gl_je_l.set_of_books_id||'|'||vcur_lt_gl_je_l.code_combination_id||'|'||vcur_lt_gl_je_l.period_name||'|'||vcur_lt_gl_je_l.effective_date||'|'||vcur_lt_gl_je_l.status||'|'||TO_CHAR(vcur_lt_gl_je_l.creation_date, 'DD-MON-YY HH:MI:SS PM')||'|'||vcur_lt_gl_je_l.created_by||'|'||vcur_lt_gl_je_l.last_update_login||'|'||vcur_lt_gl_je_l.accounted_dr||'|'||vcur_lt_gl_je_l.accounted_cr||'|'||vcur_lt_gl_je_l.description||'|');
v_record_count := v_record_count + 1;
END LOOP;
fnd_file.put_line(fnd_file.output, '------------------------------***********************************----------------------------------------');
fnd_file.put_line(fnd_file.output, '---------------------------------------------------------------------------------------------------------');
fnd_file.put_line(fnd_file.output, ' Lord and Taylor ');
fnd_file.put_line(fnd_file.output
, 'Program: LT Journal Extract Monthly IRS Extract - GL Journals Rundate :' || TRUNC(SYSDATE));
fnd_file.put_line(fnd_file.output, ' For Fiscal Month:' || p_period_name);
fnd_file.put_line(fnd_file.output
,' ');
fnd_file.put_line(fnd_file.output
,' ');
fnd_file.put_line(fnd_file.output, ' TOTAL DEBITS TOTAL CREDITS ');
fnd_file.put_line(fnd_file.output
, 'Current Period $'
|| TRIM(TO_CHAR(l_journal_debit, '999,999,999,999,999,999.99'))
|| ' $'
|| TRIM(TO_CHAR(l_journal_credit, '999,999,999,999,999,999.99'))
);
fnd_file.put_line(fnd_file.output
,' ');
fnd_file.put_line(fnd_file.output
, 'Year To Date $'
|| TRIM(TO_CHAR(l_journal_debit_y, '999,999,999,999,999,999.99'))
|| ' $'
|| TRIM(TO_CHAR(l_journal_credit_y, '999,999,999,999,999,999.99'))
);
fnd_file.put_line(fnd_file.output
,' ');
fnd_file.put_line(fnd_file.output
,' ');
fnd_file.put_line(fnd_file.output
,'Approved By:_________________________________________________ Date:________________ ');
fnd_file.put_line(fnd_file.output
,' ');
fnd_file.put_line(fnd_file.output
,' ');
fnd_file.put_line(fnd_file.output
,' ');
fnd_file.put_line(fnd_file.output, '---------------------------------------------------------------------------------------------------------');
fnd_file.put_line(fnd_file.output, '------------------------------***********************************----------------------------------------');
/* fnd_file.put_line(fnd_file.output, '----------------*************************----------------');
fnd_file.put_line(fnd_file.output, '---------------------------------------------------------');
fnd_file.put_line(fnd_file.output, ' Summary Report For GL_JE_LINES');
fnd_file.put_line(fnd_file.output, '---------------------------------------------------------');
fnd_file.put_line(fnd_file.output, 'File Name : ' || v_file_name);
fnd_file.put_line(fnd_file.output, 'Total Credits : ' || l_line_cr);
fnd_file.put_line(fnd_file.output, 'Total Debits : ' || l_line_dr);
fnd_file.put_line(fnd_file.output, 'Total Records Extracted: ' || v_record_count);
fnd_file.put_line(fnd_file.output, 'Current System Time: ' || TO_CHAR(SYSDATE, 'HH24:MI:SS'));
fnd_file.put_line(fnd_file.output, '---------------------------------------------------------');
fnd_file.put_line(fnd_file.output, '----------------*************************----------------'); */
UTL_FILE.put_line(v_file_handler, 'Record_Count:' || v_record_count);
fnd_file.put_line(fnd_file.LOG, 'Total Records Extracted: ' || v_record_count);
fnd_file.put_line(fnd_file.LOG, 'END OF EXTRACT Current System Time: ' || TO_CHAR(SYSDATE, 'HH24:MI:SS'));
UTL_FILE.fclose(v_file_handler);
extract_lt_gl_je_headers(l_errbuf, l_retcode, p_period_name);
EXCEPTION
WHEN UTL_FILE.invalid_mode
THEN
fnd_file.put_line(fnd_file.LOG, ' Error, 29281 Invalid Mode Parameter, The open_mode parameter in FOPEN is invalid');
WHEN UTL_FILE.invalid_path
THEN
fnd_file.put_line(fnd_file.LOG, 'Error, 29280 Invalid File Location, Specified path does not exist or is not visible to Oracle');
WHEN UTL_FILE.invalid_filehandle
THEN
fnd_file.put_line(fnd_file.LOG, 'Error, 29282 Invalid Filehandle, File handle does not exist');
WHEN UTL_FILE.invalid_operation
THEN
fnd_file.put_line(fnd_file.LOG, 'Error, 29283 Invalid Operation, File could not be opened or operated on as requested');
WHEN UTL_FILE.internal_error
THEN
fnd_file.put_line(fnd_file.LOG, 'Internal Error, 29286 Unhandled internal error in the UTL_FILE package');
WHEN UTL_FILE.charsetmismatch
THEN
fnd_file.put_line
(fnd_file.LOG
,'ERROR, 29298 A file is opened using FOPEN_NCHAR, but later I/O operations use nonchar functions such as PUTF or GET_LINE'
);
WHEN UTL_FILE.file_open
THEN
fnd_file.put_line(fnd_file.LOG, 'ERROR, File Already Opened');
WHEN UTL_FILE.invalid_filename
THEN
fnd_file.put_line(fnd_file.LOG, 'Error, 29288 Invalid File Name, A file with the specified name does not exist in the path');
WHEN UTL_FILE.access_denied
THEN
fnd_file.put_line(fnd_file.LOG, 'Error, 29289 Access to the file has been denied by the operating system');
--- Default exception if all the above failed to handel
WHEN OTHERS
THEN
errbuf := SUBSTR(SQLERRM, 1, 150);
retcode := 1;
fnd_file.put_line(fnd_file.LOG, ' LAST EXCEPTION HIT (OTHER EXCEPTION) IN MAIN PROCEDURE, Unknown UTL_FILE Error');
fnd_file.put_line(fnd_file.LOG, ' UNHANDLED EXCEPTION/p_errbuf = ' || errbuf);
fnd_file.put_line(fnd_file.LOG, ' UNHANDLED EXCEPTION/p_retcode = ' || retcode);
END extract_lt_gl_je_lines;
PROCEDURE extract_lt_gl_je_headers(
errbuf OUT VARCHAR2
,retcode OUT NUMBER
,p_period_name IN VARCHAR2
)
IS
v_file_handler UTL_FILE.file_type;
CURSOR gl_je_h_cur
IS
SELECT DISTINCT js.user_je_source_name
,js.description source_description
,jh.*
FROM gl_je_headers jh
,gl_je_sources js
,gl_je_lines jl
,gl_code_combinations jb
WHERE jh.period_name = p_period_name
AND jh.je_source = js.je_source_name
AND jh.set_of_books_id = 2
AND jl.code_combination_id = jb.code_combination_id
AND jl.je_header_id = jh.je_header_id
AND jl.period_name = jh.period_name
AND jb.segment1 = '21';
-- Modified by Chandra - for LT Changes
v_file_name VARCHAR2(50);
v_profile_path VARCHAR2(100);
v_record_count NUMBER;
v_rec_txt VARCHAR2(32000);
v_rec_header VARCHAR2(32000);
l_jh_dr NUMBER;
l_jh_cr NUMBER;
l_errbuf VARCHAR2(3000);
l_retcode NUMBER;
BEGIN
v_file_name := 'xxlt_dt_gl_je_headers_' || REPLACE(p_period_name, '-', '') || '.dat';
SELECT SUM(jh.running_total_dr)
,SUM(jh.running_total_cr)
INTO l_jh_dr
,l_jh_cr
FROM gl_je_headers jh
,gl_je_sources js
,gl_je_lines jl
,gl_code_combinations jb
WHERE jh.period_name = p_period_name
AND jh.je_source = js.je_source_name
AND jh.set_of_books_id = 2
AND jl.code_combination_id = jb.code_combination_id
AND jl.je_header_id = jh.je_header_id
AND jl.period_name = jh.period_name
AND jb.segment1 = '21';
fnd_profile.get('XXHBCDATA_TOP', v_profile_path);
v_profile_path := v_profile_path || '/outbound';
v_file_handler := UTL_FILE.fopen(v_profile_path, v_file_name, 'W', 32000);
v_rec_header :=
'JE_HEADER_ID|LAST_UPDATE_DATE|LAST_UPDATED_BY|SET_OF_BOOKS_ID|JE_CATEGORY|JE_SOURCE|PERIOD_NAME|NAME|CURRENCY_CODE|STATUS|DATE_CREATED|CREATION_DATE|CREATED_BY|LAST_UPDATE_LOGIN|BALANCED_JE_FLAG|BALANCING_SEGMENT_VALUE|JE_BATCH_ID|FROM_RECURRING_HEADER_ID|EARLIEST_POSTABLE_DATE|POSTED_DATE|DESCRIPTION|CURRENCY_CONVERSION_RATE|CURRENCY_CONVERSION_TYPE|CURRENCY_CONVERSION_DATE|PARENT_JE_HEADER_ID|REVERSED_JE_HEADER_ID|INTERCOMPANY_MODE|USER_JE_SOURCE_NAME|SOURCE_DESCRIPTION|';
UTL_FILE.put_line(v_file_handler, v_rec_header);
v_record_count := 0;
fnd_file.put_line(fnd_file.LOG, 'START OF EXTRACT Current System Time: ' || TO_CHAR(SYSDATE, 'HH24:MI:SS'));
fnd_file.put_line(fnd_file.LOG, 'Extracting Into: ' || v_file_name);
fnd_file.put_line(fnd_file.LOG, ' ');
FOR vcur_gl_je_h IN gl_je_h_cur
LOOP
v_rec_txt :=
REPLACE(REPLACE( vcur_gl_je_h.je_header_id
|| '|'
|| TO_CHAR(vcur_gl_je_h.last_update_date, 'DD-MON-YY HH:MI:SS PM')
|| '|'
|| vcur_gl_je_h.last_updated_by
|| '|'
|| vcur_gl_je_h.set_of_books_id
|| '|'
|| vcur_gl_je_h.je_category
|| '|'
|| vcur_gl_je_h.je_source
|| '|'
|| vcur_gl_je_h.period_name
|| '|'
|| vcur_gl_je_h.NAME
|| '|'
|| vcur_gl_je_h.currency_code
|| '|'
|| vcur_gl_je_h.status
|| '|'
|| TO_CHAR(vcur_gl_je_h.date_created, 'DD-MON-YY HH:MI:SS PM')
|| '|'
|| TO_CHAR(vcur_gl_je_h.creation_date, 'DD-MON-YY HH:MI:SS PM')
|| '|'
|| vcur_gl_je_h.created_by
|| '|'
|| vcur_gl_je_h.last_update_login
|| '|'
|| vcur_gl_je_h.balanced_je_flag
|| '|'
|| vcur_gl_je_h.balancing_segment_value
|| '|'
|| vcur_gl_je_h.je_batch_id
|| '|'
|| vcur_gl_je_h.from_recurring_header_id
|| '|'
|| TO_CHAR(vcur_gl_je_h.earliest_postable_date, 'DD-MON-YY HH:MI:SS PM')
|| '|'
|| TO_CHAR(vcur_gl_je_h.posted_date, 'DD-MON-YY HH:MI:SS PM')
|| '|'
|| vcur_gl_je_h.description
|| '|'
|| vcur_gl_je_h.currency_conversion_rate
|| '|'
|| vcur_gl_je_h.currency_conversion_type
|| '|'
|| vcur_gl_je_h.currency_conversion_date
|| '|'
|| vcur_gl_je_h.parent_je_header_id
|| '|'
|| vcur_gl_je_h.reversed_je_header_id
|| '|'
|| vcur_gl_je_h.intercompany_mode
|| '|'
|| vcur_gl_je_h.user_je_source_name
|| '|'
|| vcur_gl_je_h.source_description
|| '|'
,CHR(10)
,' '
)
,CHR(13)
,' '
);
UTL_FILE.put_line(v_file_handler, v_rec_txt);
v_record_count := v_record_count + 1;
END LOOP;
/* fnd_file.put_line(fnd_file.output, '----------------*************************----------------');
fnd_file.put_line(fnd_file.output, '---------------------------------------------------------');
fnd_file.put_line(fnd_file.output, ' Summary Report For GL_JE_HEADERS');
fnd_file.put_line(fnd_file.output, '---------------------------------------------------------');
fnd_file.put_line(fnd_file.output, 'File Name : ' || v_file_name);
fnd_file.put_line(fnd_file.output, 'Total Credits : ' || l_jh_dr);
fnd_file.put_line(fnd_file.output, 'Total Debits : ' || l_jh_cr);
fnd_file.put_line(fnd_file.output, 'Total Records Extracted: ' || v_record_count);
fnd_file.put_line(fnd_file.output, 'Current System Time: ' || TO_CHAR(SYSDATE, 'HH24:MI:SS'));
fnd_file.put_line(fnd_file.output, '---------------------------------------------------------');
fnd_file.put_line(fnd_file.output, '----------------*************************----------------');*/
UTL_FILE.put_line(v_file_handler, 'Record_Count:' || v_record_count);
fnd_file.put_line(fnd_file.LOG, 'Total Records Extracted: ' || v_record_count);
fnd_file.put_line(fnd_file.LOG, 'END OF EXTRACT Current System Time: ' || TO_CHAR(SYSDATE, 'HH24:MI:SS'));
UTL_FILE.fclose(v_file_handler);
extract_lt_gl_je_batches(l_errbuf, l_retcode, p_period_name);
EXCEPTION
WHEN UTL_FILE.invalid_mode
THEN
fnd_file.put_line(fnd_file.LOG, ' Error, 29281 Invalid Mode Parameter, The open_mode parameter in FOPEN is invalid');
WHEN UTL_FILE.invalid_path
THEN
fnd_file.put_line(fnd_file.LOG, 'Error, 29280 Invalid File Location, Specified path does not exist or is not visible to Oracle');
WHEN UTL_FILE.invalid_filehandle
THEN
fnd_file.put_line(fnd_file.LOG, 'Error, 29282 Invalid Filehandle, File handle does not exist');
WHEN UTL_FILE.invalid_operation
THEN
fnd_file.put_line(fnd_file.LOG, 'Error, 29283 Invalid Operation, File could not be opened or operated on as requested');
WHEN UTL_FILE.internal_error
THEN
fnd_file.put_line(fnd_file.LOG, 'Internal Error, 29286 Unhandled internal error in the UTL_FILE package');
WHEN UTL_FILE.charsetmismatch
THEN
fnd_file.put_line
(fnd_file.LOG
,'ERROR, 29298 A file is opened using FOPEN_NCHAR, but later I/O operations use nonchar functions such as PUTF or GET_LINE'
);
WHEN UTL_FILE.file_open
THEN
fnd_file.put_line(fnd_file.LOG, 'ERROR, File Already Opened');
WHEN UTL_FILE.invalid_filename
THEN
fnd_file.put_line(fnd_file.LOG, 'Error, 29288 Invalid File Name, A file with the specified name does not exist in the path');
WHEN UTL_FILE.access_denied
THEN
fnd_file.put_line(fnd_file.LOG, 'Error, 29289 Access to the file has been denied by the operating system');
--- Default exception if all the above failed to handel
WHEN OTHERS
THEN
errbuf := SUBSTR(SQLERRM, 1, 150);
retcode := 1;
fnd_file.put_line(fnd_file.LOG, ' LAST EXCEPTION HIT (OTHER EXCEPTION) IN MAIN PROCEDURE, Unknown UTL_FILE Error');
fnd_file.put_line(fnd_file.LOG, ' UNHANDLED EXCEPTION/p_errbuf = ' || errbuf);
fnd_file.put_line(fnd_file.LOG, ' UNHANDLED EXCEPTION/p_retcode = ' || retcode);
END extract_lt_gl_je_headers;
PROCEDURE extract_lt_gl_je_batches(
errbuf OUT VARCHAR2
,retcode OUT NUMBER
,p_period_name IN VARCHAR2
)
IS
v_file_handler UTL_FILE.file_type;
CURSOR gl_je_b_cur
IS
SELECT jb.je_batch_id
,jb.NAME
,jb.status
,jb.approval_status_code
,jb.created_by
,fu1.user_name created_by_user
,fu1.description created_by_user_name
,jb.last_updated_by
,fu2.user_name last_updated_by_user
,fu2.description last_updated_by_user_name
FROM gl_je_batches jb
,fnd_user fu1
,fnd_user fu2
,gl_je_lines jl
,gl_je_headers jh
,gl_code_combinations jc
WHERE jb.default_period_name = p_period_name
AND jb.created_by = fu1.user_id
AND jb.last_updated_by = fu2.user_id
AND jb.set_of_books_id = 2
AND jl.je_header_id = jh.je_header_id
AND jh.je_batch_id = jb.je_batch_id
AND jl.set_of_books_id = jh.set_of_books_id
AND jc.segment1 = '21'
AND jl.period_name = jh.period_name
AND jc.code_combination_id = jl.code_combination_id;
-- Modified by Narasimha G Devalaraju - for LT Changes
v_file_name VARCHAR2(50);
v_profile_path VARCHAR2(100);
v_record_count NUMBER;
v_rec_txt VARCHAR2(32000);
v_rec_header VARCHAR2(32000);
l_bat_dr NUMBER;
l_bat_cr NUMBER;
BEGIN
v_file_name := 'xxlt_dt_gl_je_batches_' || REPLACE(p_period_name, '-', '') || '.dat';
SELECT SUM(jb.running_total_dr)
,SUM(jb.running_total_cr)
INTO l_bat_dr
,l_bat_cr
FROM gl_je_batches jb
,fnd_user fu1
,fnd_user fu2
,gl_je_lines jl
,gl_je_headers jh
,gl_code_combinations jc
WHERE jb.default_period_name = p_period_name
AND jb.created_by = fu1.user_id
AND jb.last_updated_by = fu2.user_id
AND jb.set_of_books_id = 2
AND jl.je_header_id = jh.je_header_id
AND jh.je_batch_id = jb.je_batch_id
AND jl.set_of_books_id = jh.set_of_books_id
AND jc.segment1 = '21'
AND jl.period_name = jh.period_name
AND jc.code_combination_id = jl.code_combination_id;
fnd_profile.get('XXHBCDATA_TOP', v_profile_path);
v_profile_path := v_profile_path || '/outbound';
v_file_handler := UTL_FILE.fopen(v_profile_path, v_file_name, 'W', 32000);
v_rec_header :=
'JE_BATCH_ID|NAME|STATUS|APPROVAL_STATUS_CODE|CREATED_BY|CREATED_BY_USER|CREATED_BY_USER_NAME|LAST_UPDATED_BY|LAST_UPDATED_BY_USER|LAST_UPDATED_BY_USER_NAME|';
UTL_FILE.put_line(v_file_handler, v_rec_header);
v_record_count := 0;
fnd_file.put_line(fnd_file.LOG, 'START OF EXTRACT Current System Time: ' || TO_CHAR(SYSDATE, 'HH24:MI:SS'));
fnd_file.put_line(fnd_file.LOG, 'Extracting Into: ' || v_file_name);
fnd_file.put_line(fnd_file.LOG, ' ');
FOR vcur_gl_je_b IN gl_je_b_cur
LOOP
v_rec_txt :=
REPLACE(REPLACE( vcur_gl_je_b.je_batch_id
|| '|'
|| vcur_gl_je_b.NAME
|| '|'
|| vcur_gl_je_b.status
|| '|'
|| vcur_gl_je_b.approval_status_code
|| '|'
|| vcur_gl_je_b.created_by
|| '|'
|| vcur_gl_je_b.created_by_user
|| '|'
|| vcur_gl_je_b.created_by_user_name
|| '|'
|| vcur_gl_je_b.last_updated_by
|| '|'
|| vcur_gl_je_b.last_updated_by_user
|| '|'
|| vcur_gl_je_b.last_updated_by_user_name
|| '|'
,CHR(10)
,' '
)
,CHR(13)
,' '
);
UTL_FILE.put_line(v_file_handler, v_rec_txt);
v_record_count := v_record_count + 1;
END LOOP;
/*fnd_file.put_line(fnd_file.output, '----------------*************************----------------');
fnd_file.put_line(fnd_file.output, '---------------------------------------------------------');
fnd_file.put_line(fnd_file.output, ' Summary Report For GL_JE_BATCHES');
fnd_file.put_line(fnd_file.output, '---------------------------------------------------------');
fnd_file.put_line(fnd_file.output, 'File Name : ' || v_file_name);
fnd_file.put_line(fnd_file.output, 'Total Credits : ' || l_bat_cr);
fnd_file.put_line(fnd_file.output, 'Total Debits : ' || l_bat_dr);
fnd_file.put_line(fnd_file.output, 'Total Records Extracted: ' || v_record_count);
fnd_file.put_line(fnd_file.output, 'Current System Time: ' || TO_CHAR(SYSDATE, 'HH24:MI:SS'));
fnd_file.put_line(fnd_file.output, '---------------------------------------------------------');
fnd_file.put_line(fnd_file.output, '----------------*************************----------------');*/
UTL_FILE.put_line(v_file_handler, 'Record_Count:' || v_record_count);
fnd_file.put_line(fnd_file.LOG, 'Total Records Extracted: ' || v_record_count);
fnd_file.put_line(fnd_file.LOG, 'END OF EXTRACT Current System Time: ' || TO_CHAR(SYSDATE, 'HH24:MI:SS'));
UTL_FILE.fclose(v_file_handler);
EXCEPTION
WHEN UTL_FILE.invalid_mode
THEN
fnd_file.put_line(fnd_file.LOG, ' Error, 29281 Invalid Mode Parameter, The open_mode parameter in FOPEN is invalid');
WHEN UTL_FILE.invalid_path
THEN
fnd_file.put_line(fnd_file.LOG, 'Error, 29280 Invalid File Location, Specified path does not exist or is not visible to Oracle');
WHEN UTL_FILE.invalid_filehandle
THEN
fnd_file.put_line(fnd_file.LOG, 'Error, 29282 Invalid Filehandle, File handle does not exist');
WHEN UTL_FILE.invalid_operation
THEN
fnd_file.put_line(fnd_file.LOG, 'Error, 29283 Invalid Operation, File could not be opened or operated on as requested');
WHEN UTL_FILE.internal_error
THEN
fnd_file.put_line(fnd_file.LOG, 'Internal Error, 29286 Unhandled internal error in the UTL_FILE package');
WHEN UTL_FILE.charsetmismatch
THEN
fnd_file.put_line
(fnd_file.LOG
,'ERROR, 29298 A file is opened using FOPEN_NCHAR, but later I/O operations use nonchar functions such as PUTF or GET_LINE'
);
WHEN UTL_FILE.file_open
THEN
fnd_file.put_line(fnd_file.LOG, 'ERROR, File Already Opened');
WHEN UTL_FILE.invalid_filename
THEN
fnd_file.put_line(fnd_file.LOG, 'Error, 29288 Invalid File Name, A file with the specified name does not exist in the path');
WHEN UTL_FILE.access_denied
THEN
fnd_file.put_line(fnd_file.LOG, 'Error, 29289 Access to the file has been denied by the operating system');
--- Default exception if all the above failed to handel
WHEN OTHERS
THEN
errbuf := SUBSTR(SQLERRM, 1, 150);
retcode := 1;
fnd_file.put_line(fnd_file.LOG, ' LAST EXCEPTION HIT (OTHER EXCEPTION) IN MAIN PROCEDURE, Unknown UTL_FILE Error');
fnd_file.put_line(fnd_file.LOG, ' UNHANDLED EXCEPTION/p_errbuf = ' || errbuf);
fnd_file.put_line(fnd_file.LOG, ' UNHANDLED EXCEPTION/p_retcode = ' || retcode);
END extract_lt_gl_je_batches;
END journals_mon_ext;
--- End of the package body
/
exit
/
No comments:
Post a Comment