OutBound Interface and example
create or replace PACKAGE BODY GL_RPT_01_PKG
AS
PROCEDURE JOURNAL_AUDIT_PRC(
O_ERRBUF OUT VARCHAR2,
O_RETCODE OUT VARCHAR2,
I_LEDGER IN NUMBER,
I_COMPANY IN VARCHAR2,
I_PERIOD_FROM IN VARCHAR2,
I_PERIOD_TO IN VARCHAR2
)
AS
x_count NUMBER:=0;
X_FILE_NAME varchar2(100);
x_directory_name v$parameter.value%type;--VARCHAR2(1000);
file_handle UTL_FILE.FILE_TYPE;
x_ledger NUMBER;
x_company VARCHAR2(15);
x_period_from VARCHAR2(15);
x_period_to VARCHAR2(15);
x_period_year NUMBER;
x_ledger_name VARCHAR2(100);
CURSOR cur_journal_entry(i_ledger IN NUMBER, i_company IN VARCHAR2, i_period_from IN VARCHAR2, i_period_to IN VARCHAR2 )
IS
SELECT gjh.NAME
||gjh.CURRENCY_CODE
||gjh.PERIOD_NAME JOURNAL_ENTRY , --Journal Entry Unique Identifier
gjl.code_combination_id,
gcc.segment3 ACCOUNT_NUMBER,
gjh.POSTED_DATE POSTING_DATE,
gjh.DEFAULT_EFFECTIVE_DATE EFFECTIVE_DATE,
gjl.ACCOUNTED_DR,
gjl.ACCOUNTED_CR,
DECODE(gjl.ACCOUNTED_DR,NULL,'-'
||gjl.ACCOUNTED_CR,gjl.ACCOUNTED_DR) GL_AMOUNT,
gjh.CURRENCY_CODE,
TO_CHAR(gjh.posted_date,'HH24:MI:SS') posting_time,
gjh.period_name EFFECTIVE_PERIOD,
gjh.JE_SOURCE JOURNAL_ENTRY_MA,
gjh.DESCRIPTION HEADER_DESCR,
gjl.DESCRIPTION LINE_DESCR,
(SELECT user_name FROM fnd_user WHERE user_id = gjl.CREATED_BY
) CREATED_BY,
gjh.JE_SOURCE,
gjb.NAME BATCH_NUMBER,
gjb.CREATION_DATE BATCH_DATE,
gjb.DESCRIPTION BATCH_DESCR,
gjl.JE_LINE_NUM JE_LINE_NUM,
gcc.segment2 COST_CENTER,
gjh.JE_CATEGORY DOCUMENT_TYPE,
gp.period_year FISCAL_YEAR,
gjh.JE_SOURCE ENTRY_DOCU_TYPE,
gjh.CURRENCY_CONVERSION_RATE ,
gjh.ACCRUAL_REV_FLAG REVERSE_IND,
gjh.ACCRUAL_REV_EFFECTIVE_DATE REVERSE_DATE,
gjh.CREATION_DATE CREATION_DATE ,
TO_CHAR(gjh.creation_date,'HH24:MI:SS') CREATION_TIME ,
(SELECT user_name FROM fnd_user WHERE user_id = gjh.LAST_UPDATED_BY
) CHANGED_BY,
gjh.LAST_UPDATE_DATE CHANGED_BY_DATE,
TO_CHAR(gjh.last_update_date,'HH24:MI:SS') CHANGED_BY_time,
(SELECT user_name FROM fnd_user WHERE user_id = gjb.APPROVER_EMPLOYEE_ID
) APPROVER_EMPLOYEE_ID,
gjb.je_batch_id,
SB_GL_RPT_01_PKG.SB_ApproveDate(gjb.je_batch_id) APPROVED_DATE,
to_char(SB_GL_RPT_01_PKG.SB_ApproveDate(gjb.je_batch_id),'HH24:MI:SS') APPROVED_TIME
FROM GL_JE_HEADERS gjh,
GL_JE_LINES gjl,
GL_JE_BATCHES gjb,
GL_CODE_COMBINATIONS gcc,
gl_periods gp
WHERE gjh.je_header_id = gjl.je_header_id
AND gjh.je_batch_id = gjb.je_batch_id
AND gcc.code_combination_id = gjl.code_combination_id
AND gp.period_name = gjh.period_name
AND gjh.ledger_id = i_ledger
AND gcc.segment1 = NVL(i_company,gcc.segment1)
AND gp.start_date >=
(SELECT start_date FROM gl_periods WHERE period_name = i_period_from
)
AND gp.end_date <=
(SELECT end_date FROM gl_periods WHERE period_name = i_period_to
);
BEGIN
-- initializing the variables with null
x_directory_name:='/usr/tmp';
x_file_name :=NULL;
-- initializing the parameter values into defined variables
x_ledger := i_ledger;
x_company := i_company;
x_period_from := i_period_from;
x_period_to := i_period_to;
fnd_file.put_line(fnd_file.log,'starts printing');
fnd_file.put_line(fnd_file.log,'Ledger value :'||i_ledger);
fnd_file.put_line(fnd_file.log,'Ledger value :'||x_ledger);
fnd_file.put_line(fnd_file.output,'Ledger :'||i_ledger);
fnd_file.put_line(fnd_file.log,'Company :'||x_company);
fnd_file.put_line(fnd_file.output,'Company :'||i_company);
fnd_file.put_line(fnd_file.log,'Period From :'||x_period_from);
fnd_file.put_line(fnd_file.output,'Period From :'||x_period_from);
fnd_file.put_line(fnd_file.log,'Period to :'||x_period_to);
fnd_file.put_line(fnd_file.output,'Period to :'||x_period_to);
--Directory Location
/*SELECT value
INTO x_directory_name
FROM v$parameter
WHERE name='utl_file_dir' ; */
fnd_file.put_line(fnd_file.log,'Directory Name :'||x_directory_name);
fnd_file.put_line(fnd_file.output,'Directory Name :'||x_directory_name);
-- Retrieving the period year by passing period from to concatenate with file name
SELECT period_year
INTO x_period_year
FROM gl_periods
WHERE period_name = x_period_from;
fnd_file.put_line(fnd_file.log,'Period From :'||x_period_from);
fnd_file.put_line(fnd_file.output,'Period From :'||x_period_from);
fnd_file.put_line(fnd_file.log,'Period Year :'||x_period_year);
fnd_file.put_line(fnd_file.output,'Period Year :'||x_period_year);
-- Retrieving the ledger name US or CA from the gl_sets_of_books table to concatenate with file name
SELECT SUBSTR(name,1,2)
INTO x_ledger_name
FROM gl_sets_of_books
WHERE set_of_books_id = x_ledger;
--File name
x_file_name := x_ledger_name||x_period_from||'-'||x_period_to||'-'||x_period_year||'.txt';
-- Open file in Write mode
file_handle := UTL_FILE.FOPEN(x_directory_name,x_file_name,'W',32000);
fnd_file.put_line(fnd_file.output,'File_name :'||x_file_name);
fnd_file.put_line(fnd_file.log,'File_name :'||x_file_name);
BEGIN
-- open and fetching cursor
FOR rec_journal_entry IN cur_journal_entry(i_ledger ,i_company , i_period_from , i_period_to )
LOOP
x_count := x_count+1;
IF x_count>0 THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'File Open Successful');
END IF;
utl_file.put_line(file_handle,RPAD(rec_journal_entry.JOURNAL_ENTRY,70)|| --1
RPAD(rec_journal_entry.ACCOUNT_NUMBER,10)|| --2
RPAD(rec_journal_entry.POSTING_DATE,12)|| --3
RPAD(rec_journal_entry.EFFECTIVE_DATE,12)|| --4
RPAD(rec_journal_entry.GL_AMOUNT,9)|| --5
RPAD(rec_journal_entry.CURRENCY_CODE,15)|| --6
RPAD(rec_journal_entry.posting_time,10)|| --7
RPAD(rec_journal_entry.EFFECTIVE_PERIOD,15)|| --8
RPAD(rec_journal_entry.JOURNAL_ENTRY_MA,25)|| --9
RPAD(rec_journal_entry.HEADER_DESCR,100)|| --10
RPAD(rec_journal_entry.LINE_DESCR,100)|| --11
RPAD(rec_journal_entry.CREATED_BY,50)|| --12
RPAD(rec_journal_entry.JE_SOURCE,25)|| --13
RPAD(rec_journal_entry.BATCH_NUMBER,100)|| --14
RPAD(rec_journal_entry.BATCH_DATE,12)|| --15
RPAD(rec_journal_entry.BATCH_DESCR,100)|| --16
RPAD(rec_journal_entry.JE_LINE_NUM,15)|| --17
RPAD(rec_journal_entry.COST_CENTER,10)|| --18
RPAD(rec_journal_entry.DOCUMENT_TYPE,25)|| --19
RPAD(rec_journal_entry.FISCAL_YEAR,15)|| --20
RPAD(rec_journal_entry.ENTRY_DOCU_TYPE,25)|| --21
RPAD(rec_journal_entry.CURRENCY_CONVERSION_RATE,9)|| --22
RPAD(rec_journal_entry.REVERSE_IND,2)|| --23
RPAD(rec_journal_entry.REVERSE_DATE,12)|| --24
RPAD(rec_journal_entry.CREATION_DATE,12)|| --25
RPAD(rec_journal_entry.CREATION_TIME,10)|| --26
RPAD(rec_journal_entry.CHANGED_BY,50)|| --27
RPAD(rec_journal_entry.CHANGED_BY_DATE,12)|| --28
RPAD(rec_journal_entry.CHANGED_BY_time,10)|| --29
RPAD(rec_journal_entry.APPROVER_EMPLOYEE_ID,50)|| --30
RPAD(rec_journal_entry.APPROVED_DATE,12)); --31
fnd_file.put_line(fnd_file.output,RPAD(rec_journal_entry.JOURNAL_ENTRY,130)||CHR(9)||
RPAD(rec_journal_entry.ACCOUNT_NUMBER,150)||CHR(9)||
RPAD(rec_journal_entry.POSTING_DATE,12)||CHR(9)||
RPAD(rec_journal_entry.EFFECTIVE_DATE,12)||CHR(9)||
RPAD(rec_journal_entry.GL_AMOUNT,9)||CHR(9)||
RPAD(rec_journal_entry.CURRENCY_CODE,15)||CHR(9)||
RPAD(rec_journal_entry.posting_time,10)||CHR(9)||
RPAD(rec_journal_entry.EFFECTIVE_PERIOD,15)||CHR(9)||
RPAD(rec_journal_entry.JOURNAL_ENTRY_MA,25)||CHR(9)||
RPAD(rec_journal_entry.HEADER_DESCR,240)||CHR(9)||
RPAD(rec_journal_entry.LINE_DESCR,240)||CHR(9)||
RPAD(rec_journal_entry.CREATED_BY,100)||CHR(9)||
RPAD(rec_journal_entry.JE_SOURCE,25)||CHR(9)||
RPAD(rec_journal_entry.BATCH_NUMBER,100)||CHR(9)||
RPAD(rec_journal_entry.BATCH_DATE,12)||CHR(9)||
RPAD(rec_journal_entry.BATCH_DESCR,240)||CHR(9)||
RPAD(rec_journal_entry.JE_LINE_NUM,15)||CHR(9)||
RPAD(rec_journal_entry.COST_CENTER,150)||CHR(9)||
RPAD(rec_journal_entry.DOCUMENT_TYPE,25)||CHR(9)||
RPAD(rec_journal_entry.FISCAL_YEAR,15)||CHR(9)||
RPAD(rec_journal_entry.ENTRY_DOCU_TYPE,25)||CHR(9)||
RPAD(rec_journal_entry.CURRENCY_CONVERSION_RATE,9)||CHR(9)||
RPAD(rec_journal_entry.REVERSE_IND,2)||CHR(9)||
RPAD(rec_journal_entry.REVERSE_DATE,12)||CHR(9)||
RPAD(rec_journal_entry.CREATION_DATE,12)||CHR(9)||
RPAD(rec_journal_entry.CREATION_TIME,10)||CHR(9)||
RPAD(rec_journal_entry.CHANGED_BY,100)||CHR(9)||
RPAD(rec_journal_entry.CHANGED_BY_DATE,12)||CHR(9)||
RPAD(rec_journal_entry.CHANGED_BY_time,10)||CHR(9)||
RPAD(rec_journal_entry.APPROVER_EMPLOYEE_ID,100)||CHR(9)||
RPAD(rec_journal_entry.APPROVED_DATE,12)||CHR(10));
END LOOP;
EXCEPTION
WHEN no_data_found THEN
fnd_file.put_line(fnd_file.output,'No Data Found'||sqlerrm);
fnd_file.put_line(fnd_file.log,'No Data Found'||sqlerrm);
END;
-- Closing file
utl_file.fclose(file_handle);
fnd_file.put_line(fnd_file.output,'No of Records transferred to the data file :'||x_count);
fnd_file.put_line(fnd_file.output,' ');
fnd_file.put_line(fnd_file.output,'Submitted User name :'||fnd_profile.value('username'));
fnd_file.put_line(fnd_file.output,' ');
fnd_file.put_line(fnd_file.output,'Submitted Responsibility name :'||fnd_profile.value('RESP_NAME'));
fnd_file.put_line(fnd_file.output,' ');
fnd_file.put_line(fnd_file.output,'Submission Date :'||SYSDATE);
EXCEPTION
WHEN NO_DATA_FOUND THEN
fnd_file.put_line(fnd_file.log , 'NO_DATA_FOUND');
UTL_FILE.FCLOSE_all;
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 file 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');
--Closing all file if open
utl_file.fclose_all;
end JOURNAL_AUDIT_PRC;
function ApproveDate(i_batch_id number) return date
as
x_date date;
x_batch varchar2(100);
begin
SELECT DISTINCT gjb.NAME batch,
wn.end_date approval_end_date
into x_batch,x_date
FROM wf_notifications wn, wf_items wi, gl_je_batches gjb
WHERE wn.CONTEXT LIKE
'GLBATCH%'
|| (SELECT item_key
FROM wf_items a
WHERE a.user_key = wi.user_key
AND a.item_type = 'GLBATCH'
AND ROWNUM = 1)
|| '%'
AND wi.item_type = wn.MESSAGE_TYPE
AND wi.user_key = gjb.NAME
AND gjb.je_batch_id = i_batch_id; --54626862
return (x_date);
exception
WHEN NO_DATA_FOUND THEN
return null;
fnd_file.put_line(fnd_file.log ,'NO_DATA_FOUND'||SQLERRM);
WHEN OTHERS THEN
return null;
fnd_file.put_line(fnd_file.log,'OTHER ERROR'||SQLERRM);
end ApproveDate;
end GL_RPT_01_PKG;
create or replace PACKAGE BODY GL_RPT_01_PKG
AS
PROCEDURE JOURNAL_AUDIT_PRC(
O_ERRBUF OUT VARCHAR2,
O_RETCODE OUT VARCHAR2,
I_LEDGER IN NUMBER,
I_COMPANY IN VARCHAR2,
I_PERIOD_FROM IN VARCHAR2,
I_PERIOD_TO IN VARCHAR2
)
AS
x_count NUMBER:=0;
X_FILE_NAME varchar2(100);
x_directory_name v$parameter.value%type;--VARCHAR2(1000);
file_handle UTL_FILE.FILE_TYPE;
x_ledger NUMBER;
x_company VARCHAR2(15);
x_period_from VARCHAR2(15);
x_period_to VARCHAR2(15);
x_period_year NUMBER;
x_ledger_name VARCHAR2(100);
CURSOR cur_journal_entry(i_ledger IN NUMBER, i_company IN VARCHAR2, i_period_from IN VARCHAR2, i_period_to IN VARCHAR2 )
IS
SELECT gjh.NAME
||gjh.CURRENCY_CODE
||gjh.PERIOD_NAME JOURNAL_ENTRY , --Journal Entry Unique Identifier
gjl.code_combination_id,
gcc.segment3 ACCOUNT_NUMBER,
gjh.POSTED_DATE POSTING_DATE,
gjh.DEFAULT_EFFECTIVE_DATE EFFECTIVE_DATE,
gjl.ACCOUNTED_DR,
gjl.ACCOUNTED_CR,
DECODE(gjl.ACCOUNTED_DR,NULL,'-'
||gjl.ACCOUNTED_CR,gjl.ACCOUNTED_DR) GL_AMOUNT,
gjh.CURRENCY_CODE,
TO_CHAR(gjh.posted_date,'HH24:MI:SS') posting_time,
gjh.period_name EFFECTIVE_PERIOD,
gjh.JE_SOURCE JOURNAL_ENTRY_MA,
gjh.DESCRIPTION HEADER_DESCR,
gjl.DESCRIPTION LINE_DESCR,
(SELECT user_name FROM fnd_user WHERE user_id = gjl.CREATED_BY
) CREATED_BY,
gjh.JE_SOURCE,
gjb.NAME BATCH_NUMBER,
gjb.CREATION_DATE BATCH_DATE,
gjb.DESCRIPTION BATCH_DESCR,
gjl.JE_LINE_NUM JE_LINE_NUM,
gcc.segment2 COST_CENTER,
gjh.JE_CATEGORY DOCUMENT_TYPE,
gp.period_year FISCAL_YEAR,
gjh.JE_SOURCE ENTRY_DOCU_TYPE,
gjh.CURRENCY_CONVERSION_RATE ,
gjh.ACCRUAL_REV_FLAG REVERSE_IND,
gjh.ACCRUAL_REV_EFFECTIVE_DATE REVERSE_DATE,
gjh.CREATION_DATE CREATION_DATE ,
TO_CHAR(gjh.creation_date,'HH24:MI:SS') CREATION_TIME ,
(SELECT user_name FROM fnd_user WHERE user_id = gjh.LAST_UPDATED_BY
) CHANGED_BY,
gjh.LAST_UPDATE_DATE CHANGED_BY_DATE,
TO_CHAR(gjh.last_update_date,'HH24:MI:SS') CHANGED_BY_time,
(SELECT user_name FROM fnd_user WHERE user_id = gjb.APPROVER_EMPLOYEE_ID
) APPROVER_EMPLOYEE_ID,
gjb.je_batch_id,
SB_GL_RPT_01_PKG.SB_ApproveDate(gjb.je_batch_id) APPROVED_DATE,
to_char(SB_GL_RPT_01_PKG.SB_ApproveDate(gjb.je_batch_id),'HH24:MI:SS') APPROVED_TIME
FROM GL_JE_HEADERS gjh,
GL_JE_LINES gjl,
GL_JE_BATCHES gjb,
GL_CODE_COMBINATIONS gcc,
gl_periods gp
WHERE gjh.je_header_id = gjl.je_header_id
AND gjh.je_batch_id = gjb.je_batch_id
AND gcc.code_combination_id = gjl.code_combination_id
AND gp.period_name = gjh.period_name
AND gjh.ledger_id = i_ledger
AND gcc.segment1 = NVL(i_company,gcc.segment1)
AND gp.start_date >=
(SELECT start_date FROM gl_periods WHERE period_name = i_period_from
)
AND gp.end_date <=
(SELECT end_date FROM gl_periods WHERE period_name = i_period_to
);
BEGIN
-- initializing the variables with null
x_directory_name:='/usr/tmp';
x_file_name :=NULL;
-- initializing the parameter values into defined variables
x_ledger := i_ledger;
x_company := i_company;
x_period_from := i_period_from;
x_period_to := i_period_to;
fnd_file.put_line(fnd_file.log,'starts printing');
fnd_file.put_line(fnd_file.log,'Ledger value :'||i_ledger);
fnd_file.put_line(fnd_file.log,'Ledger value :'||x_ledger);
fnd_file.put_line(fnd_file.output,'Ledger :'||i_ledger);
fnd_file.put_line(fnd_file.log,'Company :'||x_company);
fnd_file.put_line(fnd_file.output,'Company :'||i_company);
fnd_file.put_line(fnd_file.log,'Period From :'||x_period_from);
fnd_file.put_line(fnd_file.output,'Period From :'||x_period_from);
fnd_file.put_line(fnd_file.log,'Period to :'||x_period_to);
fnd_file.put_line(fnd_file.output,'Period to :'||x_period_to);
--Directory Location
/*SELECT value
INTO x_directory_name
FROM v$parameter
WHERE name='utl_file_dir' ; */
fnd_file.put_line(fnd_file.log,'Directory Name :'||x_directory_name);
fnd_file.put_line(fnd_file.output,'Directory Name :'||x_directory_name);
-- Retrieving the period year by passing period from to concatenate with file name
SELECT period_year
INTO x_period_year
FROM gl_periods
WHERE period_name = x_period_from;
fnd_file.put_line(fnd_file.log,'Period From :'||x_period_from);
fnd_file.put_line(fnd_file.output,'Period From :'||x_period_from);
fnd_file.put_line(fnd_file.log,'Period Year :'||x_period_year);
fnd_file.put_line(fnd_file.output,'Period Year :'||x_period_year);
-- Retrieving the ledger name US or CA from the gl_sets_of_books table to concatenate with file name
SELECT SUBSTR(name,1,2)
INTO x_ledger_name
FROM gl_sets_of_books
WHERE set_of_books_id = x_ledger;
--File name
x_file_name := x_ledger_name||x_period_from||'-'||x_period_to||'-'||x_period_year||'.txt';
-- Open file in Write mode
file_handle := UTL_FILE.FOPEN(x_directory_name,x_file_name,'W',32000);
fnd_file.put_line(fnd_file.output,'File_name :'||x_file_name);
fnd_file.put_line(fnd_file.log,'File_name :'||x_file_name);
BEGIN
-- open and fetching cursor
FOR rec_journal_entry IN cur_journal_entry(i_ledger ,i_company , i_period_from , i_period_to )
LOOP
x_count := x_count+1;
IF x_count>0 THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'File Open Successful');
END IF;
utl_file.put_line(file_handle,RPAD(rec_journal_entry.JOURNAL_ENTRY,70)|| --1
RPAD(rec_journal_entry.ACCOUNT_NUMBER,10)|| --2
RPAD(rec_journal_entry.POSTING_DATE,12)|| --3
RPAD(rec_journal_entry.EFFECTIVE_DATE,12)|| --4
RPAD(rec_journal_entry.GL_AMOUNT,9)|| --5
RPAD(rec_journal_entry.CURRENCY_CODE,15)|| --6
RPAD(rec_journal_entry.posting_time,10)|| --7
RPAD(rec_journal_entry.EFFECTIVE_PERIOD,15)|| --8
RPAD(rec_journal_entry.JOURNAL_ENTRY_MA,25)|| --9
RPAD(rec_journal_entry.HEADER_DESCR,100)|| --10
RPAD(rec_journal_entry.LINE_DESCR,100)|| --11
RPAD(rec_journal_entry.CREATED_BY,50)|| --12
RPAD(rec_journal_entry.JE_SOURCE,25)|| --13
RPAD(rec_journal_entry.BATCH_NUMBER,100)|| --14
RPAD(rec_journal_entry.BATCH_DATE,12)|| --15
RPAD(rec_journal_entry.BATCH_DESCR,100)|| --16
RPAD(rec_journal_entry.JE_LINE_NUM,15)|| --17
RPAD(rec_journal_entry.COST_CENTER,10)|| --18
RPAD(rec_journal_entry.DOCUMENT_TYPE,25)|| --19
RPAD(rec_journal_entry.FISCAL_YEAR,15)|| --20
RPAD(rec_journal_entry.ENTRY_DOCU_TYPE,25)|| --21
RPAD(rec_journal_entry.CURRENCY_CONVERSION_RATE,9)|| --22
RPAD(rec_journal_entry.REVERSE_IND,2)|| --23
RPAD(rec_journal_entry.REVERSE_DATE,12)|| --24
RPAD(rec_journal_entry.CREATION_DATE,12)|| --25
RPAD(rec_journal_entry.CREATION_TIME,10)|| --26
RPAD(rec_journal_entry.CHANGED_BY,50)|| --27
RPAD(rec_journal_entry.CHANGED_BY_DATE,12)|| --28
RPAD(rec_journal_entry.CHANGED_BY_time,10)|| --29
RPAD(rec_journal_entry.APPROVER_EMPLOYEE_ID,50)|| --30
RPAD(rec_journal_entry.APPROVED_DATE,12)); --31
fnd_file.put_line(fnd_file.output,RPAD(rec_journal_entry.JOURNAL_ENTRY,130)||CHR(9)||
RPAD(rec_journal_entry.ACCOUNT_NUMBER,150)||CHR(9)||
RPAD(rec_journal_entry.POSTING_DATE,12)||CHR(9)||
RPAD(rec_journal_entry.EFFECTIVE_DATE,12)||CHR(9)||
RPAD(rec_journal_entry.GL_AMOUNT,9)||CHR(9)||
RPAD(rec_journal_entry.CURRENCY_CODE,15)||CHR(9)||
RPAD(rec_journal_entry.posting_time,10)||CHR(9)||
RPAD(rec_journal_entry.EFFECTIVE_PERIOD,15)||CHR(9)||
RPAD(rec_journal_entry.JOURNAL_ENTRY_MA,25)||CHR(9)||
RPAD(rec_journal_entry.HEADER_DESCR,240)||CHR(9)||
RPAD(rec_journal_entry.LINE_DESCR,240)||CHR(9)||
RPAD(rec_journal_entry.CREATED_BY,100)||CHR(9)||
RPAD(rec_journal_entry.JE_SOURCE,25)||CHR(9)||
RPAD(rec_journal_entry.BATCH_NUMBER,100)||CHR(9)||
RPAD(rec_journal_entry.BATCH_DATE,12)||CHR(9)||
RPAD(rec_journal_entry.BATCH_DESCR,240)||CHR(9)||
RPAD(rec_journal_entry.JE_LINE_NUM,15)||CHR(9)||
RPAD(rec_journal_entry.COST_CENTER,150)||CHR(9)||
RPAD(rec_journal_entry.DOCUMENT_TYPE,25)||CHR(9)||
RPAD(rec_journal_entry.FISCAL_YEAR,15)||CHR(9)||
RPAD(rec_journal_entry.ENTRY_DOCU_TYPE,25)||CHR(9)||
RPAD(rec_journal_entry.CURRENCY_CONVERSION_RATE,9)||CHR(9)||
RPAD(rec_journal_entry.REVERSE_IND,2)||CHR(9)||
RPAD(rec_journal_entry.REVERSE_DATE,12)||CHR(9)||
RPAD(rec_journal_entry.CREATION_DATE,12)||CHR(9)||
RPAD(rec_journal_entry.CREATION_TIME,10)||CHR(9)||
RPAD(rec_journal_entry.CHANGED_BY,100)||CHR(9)||
RPAD(rec_journal_entry.CHANGED_BY_DATE,12)||CHR(9)||
RPAD(rec_journal_entry.CHANGED_BY_time,10)||CHR(9)||
RPAD(rec_journal_entry.APPROVER_EMPLOYEE_ID,100)||CHR(9)||
RPAD(rec_journal_entry.APPROVED_DATE,12)||CHR(10));
END LOOP;
EXCEPTION
WHEN no_data_found THEN
fnd_file.put_line(fnd_file.output,'No Data Found'||sqlerrm);
fnd_file.put_line(fnd_file.log,'No Data Found'||sqlerrm);
END;
-- Closing file
utl_file.fclose(file_handle);
fnd_file.put_line(fnd_file.output,'No of Records transferred to the data file :'||x_count);
fnd_file.put_line(fnd_file.output,' ');
fnd_file.put_line(fnd_file.output,'Submitted User name :'||fnd_profile.value('username'));
fnd_file.put_line(fnd_file.output,' ');
fnd_file.put_line(fnd_file.output,'Submitted Responsibility name :'||fnd_profile.value('RESP_NAME'));
fnd_file.put_line(fnd_file.output,' ');
fnd_file.put_line(fnd_file.output,'Submission Date :'||SYSDATE);
EXCEPTION
WHEN NO_DATA_FOUND THEN
fnd_file.put_line(fnd_file.log , 'NO_DATA_FOUND');
UTL_FILE.FCLOSE_all;
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 file 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');
--Closing all file if open
utl_file.fclose_all;
end JOURNAL_AUDIT_PRC;
function ApproveDate(i_batch_id number) return date
as
x_date date;
x_batch varchar2(100);
begin
SELECT DISTINCT gjb.NAME batch,
wn.end_date approval_end_date
into x_batch,x_date
FROM wf_notifications wn, wf_items wi, gl_je_batches gjb
WHERE wn.CONTEXT LIKE
'GLBATCH%'
|| (SELECT item_key
FROM wf_items a
WHERE a.user_key = wi.user_key
AND a.item_type = 'GLBATCH'
AND ROWNUM = 1)
|| '%'
AND wi.item_type = wn.MESSAGE_TYPE
AND wi.user_key = gjb.NAME
AND gjb.je_batch_id = i_batch_id; --54626862
return (x_date);
exception
WHEN NO_DATA_FOUND THEN
return null;
fnd_file.put_line(fnd_file.log ,'NO_DATA_FOUND'||SQLERRM);
WHEN OTHERS THEN
return null;
fnd_file.put_line(fnd_file.log,'OTHER ERROR'||SQLERRM);
end ApproveDate;
end GL_RPT_01_PKG;
No comments:
Post a Comment