Here the sql query returns more than 2Lakh records. So used bulk collect.
create or replace PACKAGE XX_GL_ACL_AUD_DATA_PKG AS
-- --------------------------------------------------------------------------
-- Date Modified By Description
-- =========== ======== =================================================
-- --------------------------------------------------------------------------
PROCEDURE GENERATE (
p_errbuf OUT NOCOPY VARCHAR2,
p_errcode OUT NOCOPY NUMBER,
p_ledger_id IN NUMBER,
p_eff_dt_fr IN VARCHAR2,
p_eff_dt_to IN VARCHAR2,
p_period_fr IN VARCHAR2,
p_period_to IN VARCHAR2,
p_je_source IN VARCHAR2,
p_je_category IN VARCHAR2,
p_company_fr IN VARCHAR2,
p_company_to IN VARCHAR2,
p_nat_acct_fr IN VARCHAR2,
p_nat_acct_to IN VARCHAR2,
p_dept_fr IN VARCHAR2,
p_dept_to IN VARCHAR2,
p_prg_evnt_fr IN VARCHAR2,
p_prg_evnt_to IN VARCHAR2,
p_resp_fr IN VARCHAR2,
p_resp_to IN VARCHAR2,
p_interco_fr IN VARCHAR2,
p_interco_to IN VARCHAR2,
p_future_fr IN VARCHAR2,
p_future_to IN VARCHAR2,
p_dir IN VARCHAR2,
p_file IN VARCHAR2);
END XX_GL_ACL_AUD_DATA_PKG;
/
create or replace PACKAGE BODY XX_GL_ACL_AUD_DATA_PKG AS
-- --------------------------------------------------------------------------
-- Date Modified By Description
-- =========== ======== =================================================
-- 20-OCT-2017 Jagadeeswar Initial Creation
-- --------------------------------------------------------------------------
PROCEDURE GENERATE (
p_errbuf OUT NOCOPY VARCHAR2,
p_errcode OUT NOCOPY NUMBER,
p_ledger_id IN NUMBER,
p_eff_dt_fr IN VARCHAR2,
p_eff_dt_to IN VARCHAR2,
p_period_fr IN VARCHAR2,
p_period_to IN VARCHAR2,
p_je_source IN VARCHAR2,
p_je_category IN VARCHAR2,
p_company_fr IN VARCHAR2,
p_company_to IN VARCHAR2,
p_nat_acct_fr IN VARCHAR2,
p_nat_acct_to IN VARCHAR2,
p_dept_fr IN VARCHAR2,
p_dept_to IN VARCHAR2,
p_prg_evnt_fr IN VARCHAR2,
p_prg_evnt_to IN VARCHAR2,
p_resp_fr IN VARCHAR2,
p_resp_to IN VARCHAR2,
p_interco_fr IN VARCHAR2,
p_interco_to IN VARCHAR2,
p_future_fr IN VARCHAR2,
p_future_to IN VARCHAR2,
p_dir IN VARCHAR2,
p_file IN VARCHAR2
) AS
l_file UTL_FILE.FILE_TYPE;
ln_rowcount NUMBER := 0 ;
ln_loadcount NUMBER := 0 ;
ln_loadedcount NUMBER := 0 ;
lc_record VARCHAR2(2000):= NULL;
lc_time VARCHAR2(30) := NULL;
CURSOR gl_je_det_cur IS
Select
Jedt.Batch_Name,
Jedt.Header_Name,
Jedt.Je_Company,
Jedt.Je_Doc_Number,
Jedt.Je_Category,
Jedt.User_Je_Source_Name,
Jedt.sob_id,
Jedt.Sob_Description,
Jedt.Segment1,
Jedt.Segment2,
Jedt.Gl_Account,
Jedt.Seg2_Description,
Jedt.Line_Creation_Date,
Jedt.Line_Effective_Date,
Jedt.Line_Last_Update_Date,
Jedt.Period_Name,
Jedt.Line_Status,
Jedt.U2_User_Name,
Jedt.U_User_Name,
jedt.currency_code,
Jedt.Orig_Entered_Dr,
Jedt.Orig_Entered_Cr,
Jedt.Orig_Accounted_Dr,
Jedt.Orig_Accounted_Cr,
Case
When Jedt.Sob_Id In(1002,3047)
THEN
Case
WHEN jedt.currency_code IN('MXN','COP')
THEN
Case
When Jedt.Segment2 Between '100000' And '399999'
/*Then To_Char ( Nvl (Jedt.Orig_Accounted_Dr, 0) * Nvl (Glt.Eop_Rate, 0), '99999999999999999999D99999')
ELSE TO_CHAR ( NVL (jedt.orig_accounted_dr, 0) * NVL (glt.avg_rate, 0), '99999999999999999999D99999')*/
Then Nvl (Jedt.Orig_Accounted_Dr, 0) * Nvl (Glt.Eop_Rate, 0)
ELSE NVL (jedt.orig_accounted_dr, 0) * NVL (glt.avg_rate, 0)
End
--ELSE TO_CHAR (NVL (jedt.orig_entered_dr, 0), '99999999999999999999D99999')
ELSE NVL (jedt.orig_entered_dr, 0)
End
--Else To_Char (Nvl (jedt.Orig_Accounted_Dr, 0), '99999999999999999999D99999')
Else Nvl (jedt.Orig_Accounted_Dr, 0)
End Us_Equiv_Dr,
Case
When Jedt.Sob_Id In(1002,3047)
THEN
CASE
WHEN jedt.currency_code IN('MXN','COP')
THEN
CASE
When Jedt.Segment2 Between '100000' And '399999'
/*
Then To_Char ( Nvl (Jedt.Orig_Accounted_Cr, 0) * Nvl (Glt.Eop_Rate, 0), '99999999999999999999D99999')
ELSE TO_CHAR ( NVL (jedt.orig_accounted_cr, 0) * NVL (glt.avg_rate, 0), '99999999999999999999D99999')*/
THEN Nvl(Jedt.Orig_Accounted_Cr, 0) * Nvl (Glt.Eop_Rate, 0)
ELSE NVL(jedt.orig_accounted_cr, 0) * NVL (glt.avg_rate, 0)
End
--ELSE TO_CHAR (NVL (jedt.orig_entered_cr, 0), '99999999999999999999D99999')
ELSE NVL (jedt.orig_entered_cr, 0)
End
--Else To_Char (Nvl (Jedt.Orig_Accounted_Cr, 0), '99999999999999999999D99999')
Else Nvl (Jedt.Orig_Accounted_Cr, 0)
END us_equiv_cr,
Jedt.Line_Description,
Jedt.Line_Invoice_Date,
Jedt.Line_Tax_Code,
Jedt.Line_Inv_Identifier,
Jedt.Invoice_Amount,
Jedt.Line_Ref1,
Jedt.Line_Ref2,
Jedt.Line_Ref3,
Jedt.Line_Ref4,
Jedt.Line_Ref5,
Jedt.Line_Ref6,
Jedt.Line_Ref7,
Jedt.Line_Ref10,
Jedt.Level_0_Account_Description,
Jedt.Level_2_Account_Code,
--To_Char (Nvl (Glt.Eop_Rate, -99), '9999D9999999999') Eop_Rate,
Nvl(Glt.Eop_Rate, -99) Eop_Rate,
--TO_CHAR (NVL (glt.avg_rate, -99), '9999D9999999999') avg_rate,
NVL (glt.avg_rate, -99) avg_rate,
Jedt.Rate_Used,
jedt.description
from
(SELECT
gjb.name batch_name,
TRIM(TRANSLATE(REGEXP_REPLACE(gjh.name,'[^[:alnum:]'' '']', NULL),'áéíñóúüÁÉÍÑÓÊÚÜ','aeinouuAEINOEUU')) header_name,
--gjh.name header_name,
(SELECT TRIM(TRANSLATE(REGEXP_REPLACE(seg1_description,'[^[:alnum:]'' '']', NULL),'áéíñóúüÁÉÍÑÓÊÚÜ','aeinouuAEINOEUU'))
FROM gl_seg1_values
WHERE seg1_value = gcc.segment1 ) je_company,
--TO_CHAR (gjh.doc_sequence_value, '999999999999999') je_doc_number,
gjh.doc_sequence_value je_doc_number,
gjh.je_category je_category,
gjs.user_je_source_name user_je_source_name,
gsob.ledger_id sob_id,
TRIM(TRANSLATE(REGEXP_REPLACE(gsob.description,'[^[:alnum:]'' '']', NULL),'áéíñóúüÁÉÍÑÓÊÚÜ','aeinouuAEINOEUU')) sob_description,
gcc.segment1 segment1,
Gcc.Segment2 Segment2,
Gcc.Segment3 Segment3,
Gcc.Segment4 Segment4,
Gcc.Segment5 Segment5,
Gcc.Segment6 Segment6,
gcc.segment7 segment7,
gcc.segment1
|| '-'
|| gcc.segment2
|| '-'
|| gcc.segment3
|| '-'
|| gcc.segment4
|| '-'
|| gcc.segment5
|| '-'
|| gcc.segment6
|| '-'
|| gcc.segment7 gl_account,
(SELECT seg2_description
FROM gl_seg2_values
WHERE seg2_value = gcc.segment2 ) seg2_description,
/*TO_CHAR (gjl.creation_date, 'MM/DD/YYYY') line_creation_date,
TO_CHAR (gjl.effective_date, 'MM/DD/YYYY') line_effective_date,
TO_CHAR (gjl.last_update_date, 'MM/DD/YYYY') line_last_update_date,*/
gjl.creation_date line_creation_date,
gjl.effective_date line_effective_date,
gjl.last_update_date line_last_update_date,
gjl.period_name period_name,
gjl.status line_status,
(SELECT user_name FROM fnd_user
WHERE user_id = gjl.created_by) u2_user_name,
(SELECT user_name FROM fnd_user
WHERE user_id = gjl.last_updated_by) u_user_name,
gjh.currency_code currency_code,
/*TO_CHAR (NVL (gjl.entered_dr, 0), '99999999999999999999D99999') orig_entered_dr,
TO_CHAR (NVL (gjl.entered_cr, 0), '99999999999999999999D99999') orig_entered_cr,
TO_CHAR (NVL (gjl.accounted_dr, 0), '99999999999999999999D99999') orig_accounted_dr,
To_Char (Nvl (Gjl.Accounted_Cr, 0), '99999999999999999999D99999') Orig_Accounted_Cr, */
NVL (gjl.entered_dr, 0) orig_entered_dr,
NVL (gjl.entered_cr, 0) orig_entered_cr,
NVL (gjl.accounted_dr, 0) orig_accounted_dr,
Nvl (Gjl.Accounted_Cr, 0) Orig_Accounted_Cr,
TRIM(TRANSLATE(REGEXP_REPLACE(gjl.description,'[^[:alnum:]'' '']', NULL),'áéíñóúüÁÉÍÑÓÊÚÜ','aeinouuAEINOEUU')) line_description,
gjl.invoice_date line_invoice_date,
gjl.tax_code line_tax_code,
gjl.invoice_identifier line_inv_identifier,
--TO_CHAR (NVL (gjl.invoice_amount, 0), '99999999999999999999D99999') invoice_amount,
NVL (gjl.invoice_amount, 0) invoice_amount,
gjl.reference_1 line_ref1,
gjl.reference_2 line_ref2,
gjl.reference_3 line_ref3,
gjl.reference_4 line_ref4,
gjl.reference_5 line_ref5,
gjl.reference_6 line_ref6,
gjl.reference_7 line_ref7,
gjl.reference_10 line_ref10,
'UNKNOWN' Level_0_Account_Description,
'UNKNOWN' level_2_account_code,
CASE
WHEN gjh.currency_code IN('MXN','COP')
THEN
CASE
WHEN gcc.segment2 BETWEEN '100000' AND '399999'
THEN 'EOP'
ELSE 'AVG'
END
ELSE 'NONE'
End Rate_Used,
TRIM(TRANSLATE(REGEXP_REPLACE(gjh.description,'[^[:alnum:]'' '']', NULL),'áéíñóúüÁÉÍÑÓÊÚÜ','aeinouuAEINOEUU')) Description,
gjh.actual_flag
From Gl_Ledgers Gsob
Inner Join Gl_Period_Statuses Gps
On(Gps.Ledger_Id = Gsob.Ledger_Id
And Gps.Application_Id = 101)
Inner Join Gl_Je_Headers Gjh
On(Gjh.Ledger_Id = Gps.Ledger_Id
And Gjh.Period_Name = Gps.Period_Name
AND Gjh.Actual_Flag = 'A')
Inner Join Gl_Je_Lines Gjl
On(Gjl.Ledger_Id = Gjh.Ledger_Id
And Gjl.Je_Header_Id = Gjh.Je_Header_Id
And Gjl.Status = Gjh.Status
And gjl.Status = 'P')
Inner Join Gl_Code_Combinations Gcc
On(Gcc.Code_Combination_Id = Gjl.Code_Combination_Id
And Gcc.Chart_Of_Accounts_Id = Gsob.Chart_Of_Accounts_Id
And Gcc.Segment2 Not In('S00200','299999','S00201') )
Inner Join Gl_Je_Categories Gjc
On(Gjh.Je_Category = Gjc.Je_Category_Name)
Inner Join Gl_Je_Sources Gjs
On(Gjh.Je_Source = Gjs.Je_Source_Name)
Inner Join Gl_Je_Batches Gjb
On(Gjb.Je_Batch_Id = Gjh.Je_Batch_Id)
Where 1=1
--And gjl.Status = 'P'
--And Gjh.Actual_Flag = 'A'
--And Gcc.Segment2 Not In('S00200','299999','S00201')
And Gsob.Ledger_Id = Nvl(P_Ledger_Id,Gsob.Ledger_Id)
And Gjs.Je_Source_Name = Nvl(P_Je_Source,Gjs.Je_Source_Name)
And Gjc.Je_Category_Name = Nvl(P_Je_Category,Gjc.Je_Category_Name)
And To_Date(Gjh.Period_Name,'MON-YY') Between Nvl(To_Date(P_Period_Fr,'MON-YY'),To_Date(Gjh.Period_Name,'MON-YY'))
And Nvl(To_Date(P_Period_To,'MON-YY'),To_Date(Gjh.Period_Name,'MON-YY'))
And Gjl.Effective_Date >= Nvl(To_Date(P_Eff_Dt_Fr,'YYYY/MM/DD HH24:MI:SS'),Gjl.Effective_Date)
And Gjl.Effective_Date <= Nvl(To_Date(P_Eff_Dt_To,'YYYY/MM/DD HH24:MI:SS'),Gjl.Effective_Date)
And gcc.segment1 = ALL (Select distinct glcc.segment1
From Gl_Code_Combinations glcc
Where Glcc.Segment1 >= P_Company_Fr
And Glcc.Segment1 <= P_Company_To)
And gcc.segment2 = ALL (Select distinct glcc.segment2
From Gl_Code_Combinations Glcc
Where Glcc.Segment2 >= P_Nat_Acct_Fr
And Glcc.Segment2 <= P_Nat_Acct_To)
And Gcc.Segment3 = All (Select Distinct Glcc.Segment3
From Gl_Code_Combinations glcc
Where Glcc.Segment3 >= P_Dept_Fr
And Glcc.Segment3 <= P_Dept_To)
And gcc.segment4 = ALL (Select distinct glcc.segment4
From Gl_Code_Combinations Glcc
Where Glcc.Segment4 >= P_Prg_Evnt_Fr
And Glcc.Segment4 <= P_Prg_Evnt_To)
And Gcc.Segment5 = All (Select Distinct Glcc.Segment5
From Gl_Code_Combinations glcc
Where Glcc.Segment5 >= P_Resp_Fr
And Glcc.Segment5 <= P_Resp_To)
And Gcc.Segment6 = All (Select Distinct Glcc.Segment6
From Gl_Code_Combinations Glcc
Where Glcc.Segment6 >= P_Interco_Fr
And Glcc.Segment6 <= P_Interco_To)
And gcc.segment7 = ALL (Select distinct glcc.segment7
From Gl_Code_Combinations glcc
Where Glcc.Segment7 >= P_Future_Fr
And Glcc.Segment7 <= P_Future_Fr)
)Jedt
Left Join
(SELECT DISTINCT
CASE
WHEN set_of_books_id = 1002
THEN 'MXN'
WHEN set_of_books_id = 3047
THEN 'COP'
WHEN Set_Of_Books_Id = 1001
THEN 'USD'
END currency_code,
set_of_books_id,
Period_Name,
DECODE(Set_Of_Books_Id, '1001',1,Eop_Rate) eop_rate,
Decode(Set_Of_Books_Id, '1001',1,Avg_Rate) Avg_Rate
From Gl_Translation_Rates) Glt
ON (Glt.Period_Name = Jedt.Period_Name
And Glt.Currency_Code = Jedt.Currency_Code
And Glt.Set_Of_Books_Id = Jedt.Sob_Id);
CURSOR gl_je_acc_cur
IS
SELECT '"'||
replace(batch_name,'"','')||'","'||
replace(header_name,'"','')||'","'||
je_company||'","'||
TO_CHAR (je_doc_number, '999999999999999') ||'","'||
je_category||'","'||
user_je_source_name||'","'||
sob_id||'","'||
sob_description||'","'||
segment1||'","'||
segment2||'","'||
gl_account||'","'||
seg2_description||'","'||
line_creation_date||'","'||
line_effective_date||'","'||
line_last_update_date||'","'||
TO_CHAR(period_name)||'","'||
line_status||'","'||
u2_user_name||'","'||
u_user_name||'","'||
currency_code||'","'||
TO_CHAR(orig_entered_dr,'99999999999999999999D99999') ||'","'||
TO_CHAR(orig_entered_cr,'99999999999999999999D99999') ||'","'||
TO_CHAR(orig_accounted_dr,'99999999999999999999D99999')||'","'||
TO_CHAR(orig_accounted_cr,'99999999999999999999D99999')||'","'||
TO_CHAR(us_equiv_dr,'99999999999999999999D99999') ||'","'||
TO_CHAR(us_equiv_cr,'99999999999999999999D99999') ||'","'||
line_description||'","'||
line_invoice_date||'","'||
line_tax_code||'","'||
line_inv_identifier||'","'||
TO_CHAR(invoice_amount,'99999999999999999999D99999')||'","'||
line_ref1||'","'||
line_ref2||'","'||
line_ref3||'","'||
line_ref4||'","'||
line_ref5||'","'||
line_ref6||'","'||
line_ref7||'","'||
line_ref10||'","'||
Level_0_Account_Description||'","'||
level_2_account_code||'","'||
TO_CHAR(eop_rate,'9999D9999999999')||'","'||
TO_CHAR(avg_rate,'9999D9999999999')||'","'||
rate_used||'","'||
replace(description,'"','')||'"' rec
FROM XXU.XX_AUD_GL_JE_LINES_GLT;
TYPE xx_gl_je_lines_tab IS TABLE OF xxu.xx_aud_gl_je_lines_glt%ROWTYPE;
xx_gl_acc_load xx_gl_je_lines_tab;
TYPE xx_gl_acc_tab IS TABLE OF gl_je_acc_cur%ROWTYPE;
xx_gl_acc_inrt xx_gl_acc_tab := xx_gl_acc_tab();
BEGIN
lc_record := NULL;
lc_time := NULL;
ln_rowcount := 0 ;
ln_loadcount := 0 ;
ln_loadedcount := 0 ;
FND_FILE.PUT_LINE(FND_FILE.LOG, 'Parameters......');
FND_FILE.PUT_LINE(FND_FILE.LOG, 'Ledger ID : '||p_ledger_id);
FND_FILE.PUT_LINE(FND_FILE.LOG, 'JE Source : '||p_je_source);
FND_FILE.PUT_LINE(FND_FILE.LOG, 'JE Category : '||p_je_category);
FND_FILE.PUT_LINE(FND_FILE.LOG, 'JE Effective From Date : '||p_eff_dt_fr);
FND_FILE.PUT_LINE(FND_FILE.LOG, 'JE Effective To Date : '||p_eff_dt_to);
FND_FILE.PUT_LINE(FND_FILE.LOG, 'GL Period From : '||p_period_fr);
FND_FILE.PUT_LINE(FND_FILE.LOG, 'GL Period To : '||p_period_to);
FND_FILE.PUT_LINE(FND_FILE.LOG, 'Directory : '||p_dir);
FND_FILE.PUT_LINE(FND_FILE.LOG, 'File Name : '||p_file);
FND_FILE.PUT_LINE(FND_FILE.LOG, '');
SELECT TO_CHAR(SYSDATE,'DD-MON-YY HH24:MI:SS') INTO lc_time FROM DUAL;
EXECUTE IMMEDIATE 'TRUNCATE TABLE XXU.XX_AUD_GL_JE_LINES_GLT';
FND_FILE.PUT_LINE(FND_FILE.LOG, 'Data Loading Strated at '||lc_time);
OPEN gl_je_det_cur;
LOOP
FETCH gl_je_det_cur BULK COLLECT INTO xx_gl_acc_load LIMIT 10000;
FORALL i IN 1..xx_gl_acc_load.COUNT
INSERT INTO XXU.XX_AUD_GL_JE_LINES_GLT VALUES xx_gl_acc_load(i);
ln_loadcount := ln_loadcount + xx_gl_acc_load.COUNT;
EXIT WHEN gl_je_det_cur%notfound;
END LOOP;
CLOSE gl_je_det_cur;
FND_FILE.PUT_LINE(FND_FILE.LOG, 'Total Record count eligible for load - '||ln_loadcount);
SELECT COUNT(*) INTO ln_loadedcount FROM XXU.XX_AUD_GL_JE_LINES_GLT;
SELECT TO_CHAR(SYSDATE,'DD-MON-YY HH24:MI:SS') INTO lc_time FROM DUAL;
FND_FILE.PUT_LINE(FND_FILE.LOG, 'Total Record count in GT Table - '||ln_loadedcount);
FND_FILE.PUT_LINE(FND_FILE.LOG, 'Data Loading completed at '||lc_time);
FND_FILE.PUT_LINE(FND_FILE.LOG, ' ');
SELECT TO_CHAR(SYSDATE,'DD-MON-YY HH24:MI:SS') INTO lc_time FROM DUAL;
FND_FILE.PUT_LINE(FND_FILE.LOG, 'Extract Generation Started at '||lc_time);
lc_record := '"'||'JE_BATCH_NAME'||'","'|| 'JE_HEADER_NAME'||'","'|| 'JE_COMPANY'||'","'|| 'JE_DOC_NUMBER'||'","'|| 'JE_CATEGORY'||'","'||
'JE_SOURCE'||'","'||'SOD_ID'||'","'||'SOB_DESCRIPTION'||'","'|| 'COMPANY_CODE'||'","'|| 'NATURAL_ACCOUNT_CODE'||'","'|| 'GL_ACCOUNT'||'","'||
'NATURAL_ACCOUNT'||'","'|| 'CREATION_DATE'||'","'|| 'EFFECTIVE_DATE'||'","'|| 'TRANSACTION_DATE'||'","'|| 'PERIOD_NAME'||'","'||
'STATUS'||'","'|| 'CREATED_BY'||'","'|| 'UPDATED_BY'||'","'|| 'CURRENCY_CODE'||'","'|| 'ORIG_ENTERED_DR'||'","'||
'ORIG_ENTERED_CR'||'","'|| 'ORIG_ACCOUNTED_DR'||'","'|| 'ORIG_ACCOUNTED_CR'||'","'|| 'US_EQUIV_DR'||'","'||'US_EQUIV_CR'||'","'||
'JE_DESCRIPTION'||'","'|| 'INVOICE_DATE'||'","'|| 'TAX_CODE'||'","'|| 'INVOICE_IDENTIFIER'||'","'||'INVOICE_AMOUNT'||'","'||
'REFERENCE_1'||'","'||'REFERENCE_2'||'","'|| 'REFERENCE_3'||'","'|| 'REFERENCE_4'||'","'|| 'REFERENCE_5'||'","'||
'REFERENCE_6'||'","'|| 'REFERENCE_7'||'","'|| 'REFERENCE_10'||'","'|| 'LEVEL_0_ACCOUNT_DESCRIPTION'||'","'||'LEVEL_2_ACCOUNT_CODE'||'","'||
'EOP_RATE'||'","'|| 'AVG_RATE'||'","'|| 'RATE_USED'||'","'|| 'DESCRIPTION'||'"';
OPEN gl_je_acc_cur;
l_file := UTL_FILE.fopen(p_dir, p_file, 'W', 32767);
-- writing File Header record
UTL_FILE.put_line(l_file, lc_Record);
LOOP
FETCH Gl_Je_Acc_Cur BULK COLLECT INTO XX_Gl_Acc_Inrt LIMIT 10000;
FOR i IN 1..XX_Gl_Acc_Inrt.COUNT
LOOP
UTL_FILE.PUT_LINE(l_file, XX_Gl_Acc_Inrt(i).rec);
END LOOP;
Ln_Rowcount := Ln_Rowcount + XX_Gl_Acc_Inrt.COUNT;
EXIT WHEN gl_je_acc_cur%notfound;
END LOOP;
CLOSE gl_je_acc_cur;
UTL_FILE.fclose(l_file);
FND_FILE.PUT_LINE(FND_FILE.LOG, 'Extract File record count....'||Ln_Rowcount);
FND_FILE.PUT_LINE(FND_FILE.LOG, 'Extract File....'||p_file);
SELECT TO_CHAR(SYSDATE,'DD-MON-YY HH24:MI:SS') INTO lc_time FROM DUAL;
FND_FILE.PUT_LINE(FND_FILE.LOG, 'Extract Generation Completed at '||lc_time);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG, 'Exception : '||SQLERRM);
END GENERATE;
END XX_GL_ACL_AUD_DATA_PKG;
/
