Wednesday, 12 October 2022

Example of Outbound Interface in Oracle Apps with UTL_File and Bulk Collect

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;

/

Friday, 30 September 2022

Send email in Oracle EBS by using UTL_SMTP

 create or replace PACKAGE  xx_send_mail IS


  ----------------------- Customizable Section -----------------------


  -- Customize the SMTP host, port and your domain name below.

  smtp_host   VARCHAR2(256) := 'mercury';

  smtp_port   PLS_INTEGER   := 25;

  smtp_domain VARCHAR2(256) := 'xx.ss.net';


  -- Customize the signature that will appear in the email's MIME header.

  -- Useful for versioning.

  MAILER_ID   CONSTANT VARCHAR2(256) := 'Mailer by Oracle UTL_SMTP';


  --------------------- End Customizable Section ---------------------


  -- A unique string that demarcates boundaries of parts in a multi-part email

  -- The string should not appear inside the body of any part of the email.

  -- Customize this if needed or generate this randomly dynamically.

  BOUNDARY        CONSTANT VARCHAR2(256) := '-----7D81B75CCC90D2974F7A1CBD';


  FIRST_BOUNDARY  CONSTANT VARCHAR2(256) := '--' || BOUNDARY || utl_tcp.CRLF;

  LAST_BOUNDARY   CONSTANT VARCHAR2(256) := '--' || BOUNDARY || '--' ||

                                              utl_tcp.CRLF;


  -- A MIME type that denotes multi-part email (MIME) messages.

  MULTIPART_MIME_TYPE CONSTANT VARCHAR2(256) := 'multipart/mixed; boundary="'||

                                                  BOUNDARY || '"';

  MAX_BASE64_LINE_WIDTH CONSTANT PLS_INTEGER   := 76 / 4 * 3;


  -- A simple email API for sending email in plain text in a single call.

  -- The format of an email address is one of these:

  --   someone@some-domain

  --   "Someone at some domain" <someone@some-domain>

  --   Someone at some domain <someone@some-domain>

  -- The recipients is a list of email addresses  separated by

  -- either a "," or a ";"

  PROCEDURE mail(sender     IN VARCHAR2

               , recipients IN VARCHAR2

               , subject    IN VARCHAR2

               , message    IN VARCHAR2);


  PROCEDURE mail(srce IN VARCHAR2

               , sender     IN VARCHAR2

               , recipients IN VARCHAR2

               , subject    IN VARCHAR2

               , message    IN VARCHAR2);


  PROCEDURE mail_attach(sender     IN VARCHAR2

               , recipients IN VARCHAR2

               , subject    IN VARCHAR2

               , message    IN VARCHAR2

               , p_attch    IN VARCHAR2);


  -- Extended email API to send email in HTML or plain text with no size limit.

  -- First, begin the email by begin_mail(). Then, call write_text() repeatedly

  -- to send email in ASCII piece-by-piece. Or, call write_mb_text() to send

  -- email in non-ASCII or multi-byte character set. End the email with

  -- end_mail().

  FUNCTION begin_mail(sender     IN VARCHAR2,

              recipients IN VARCHAR2,

              subject    IN VARCHAR2,

              mime_type  IN VARCHAR2    DEFAULT 'text/plain',

              priority   IN PLS_INTEGER DEFAULT NULL)

              RETURN utl_smtp.connection;


  -- Write email body in ASCII

  PROCEDURE write_text(conn    IN OUT NOCOPY utl_smtp.connection,

               message IN VARCHAR2);


  -- Write email body in non-ASCII (including multi-byte). The email body

  -- will be sent in the database character set.

  PROCEDURE write_mb_text(conn    IN OUT NOCOPY utl_smtp.connection,

              message IN            VARCHAR2);


  -- Write email body in binary

  PROCEDURE write_raw(conn    IN OUT NOCOPY utl_smtp.connection,

              message IN RAW);


  -- APIs to send email with attachments. Attachments are sent by sending

  -- emails in "multipart/mixed" MIME format. Specify that MIME format when

  -- beginning an email with begin_mail().


  -- Send a single text attachment.

  PROCEDURE attach_text(conn         IN OUT NOCOPY utl_smtp.connection,

            data         IN VARCHAR2,

            mime_type    IN VARCHAR2 DEFAULT 'text/plain',

            inline       IN BOOLEAN  DEFAULT TRUE,

            filename     IN VARCHAR2 DEFAULT NULL,

                last         IN BOOLEAN  DEFAULT FALSE);


  -- Send a binary attachment. The attachment will be encoded in Base-64

  -- encoding format.

  PROCEDURE attach_base64(conn         IN OUT NOCOPY utl_smtp.connection,

              data         IN RAW,

              mime_type    IN VARCHAR2 DEFAULT 'application/octet',

              inline       IN BOOLEAN  DEFAULT TRUE,

              filename     IN VARCHAR2 DEFAULT NULL,

              last         IN BOOLEAN  DEFAULT FALSE);


  -- Send an attachment with no size limit. First, begin the attachment

  -- with begin_attachment(). Then, call write_text repeatedly to send

  -- the attachment piece-by-piece. If the attachment is text-based but

  -- in non-ASCII or multi-byte character set, use write_mb_text() instead.

  -- To send binary attachment, the binary content should first be

  -- encoded in Base-64 encoding format using the demo package for 8i,

  -- or the native one in 9i. End the attachment with end_attachment.

  PROCEDURE begin_attachment(conn         IN OUT NOCOPY utl_smtp.connection,

                 mime_type    IN VARCHAR2 DEFAULT 'text/plain',

                 inline       IN BOOLEAN  DEFAULT TRUE,

                 filename     IN VARCHAR2 DEFAULT NULL,

                 transfer_enc IN VARCHAR2 DEFAULT NULL);


  -- End the attachment.

  PROCEDURE end_attachment(conn IN OUT NOCOPY utl_smtp.connection,

               last IN BOOLEAN DEFAULT FALSE);


  -- End the email.

  PROCEDURE end_mail(conn IN OUT NOCOPY utl_smtp.connection);


  -- Extended email API to send multiple emails in a session for better

  -- performance. First, begin an email session with begin_session.

  -- Then, begin each email with a session by calling begin_mail_in_session

  -- instead of begin_mail. End the email with end_mail_in_session instead

  -- of end_mail. End the email session by end_session.

  FUNCTION begin_session RETURN utl_smtp.connection;


  -- Begin an email in a session.

  PROCEDURE begin_mail_in_session(conn       IN OUT NOCOPY utl_smtp.connection,

                  sender     IN VARCHAR2,

                  recipients IN VARCHAR2,

                  subject    IN VARCHAR2,

                  mime_type  IN VARCHAR2  DEFAULT 'text/plain',

                  priority   IN PLS_INTEGER DEFAULT NULL);


  -- End an email in a session.

  PROCEDURE end_mail_in_session(conn IN OUT NOCOPY utl_smtp.connection);


  -- End an email session.

  PROCEDURE end_session(conn IN OUT NOCOPY utl_smtp.connection);


END;

/



create or replace PACKAGE BODY      xx_send_mail IS


  -- Return the next email address in the list of email addresses, separated

  -- by either a "," or a ";".  The format of mailbox may be in one of these:

  --   someone@some-domain

  --   "Someone at some domain" <someone@some-domain>

  --   Someone at some domain <someone@some-domain>

  FUNCTION get_address(addr_list IN OUT VARCHAR2) RETURN VARCHAR2 IS


    addr VARCHAR2(256);

    i    pls_integer;


    FUNCTION lookup_unquoted_char(str  IN VARCHAR2,

                  chrs IN VARCHAR2) RETURN pls_integer AS

      c            VARCHAR2(5);

      i            pls_integer;

      len          pls_integer;

      inside_quote BOOLEAN;

    BEGIN

       inside_quote := false;

       i := 1;

       len := length(str);

       WHILE (i <= len) LOOP


     c := substr(str, i, 1);


     IF (inside_quote) THEN

       IF (c = '"') THEN

         inside_quote := false;

       ELSIF (c = '\') THEN

         i := i + 1; -- Skip the quote character

       END IF;

       GOTO next_char;

     END IF;


     IF (c = '"') THEN

       inside_quote := true;

       GOTO next_char;

     END IF;


     IF (instr(chrs, c) >= 1) THEN

        RETURN i;

     END IF;


     <<next_char>>

     i := i + 1;


       END LOOP;


       RETURN 0;


    END;


  BEGIN


    addr_list := ltrim(addr_list);

    i := lookup_unquoted_char(addr_list, ',;');

    IF (i >= 1) THEN

      addr      := substr(addr_list, 1, i - 1);

      addr_list := substr(addr_list, i + 1);

    ELSE

      addr := addr_list;

      addr_list := '';

    END IF;


    i := lookup_unquoted_char(addr, '<');

    IF (i >= 1) THEN

      addr := substr(addr, i + 1);

      i := instr(addr, '>');

      IF (i >= 1) THEN

    addr := substr(addr, 1, i - 1);

      END IF;

    END IF;


    RETURN addr;

  END;


  -- Write a MIME header

  PROCEDURE write_mime_header(conn  IN OUT NOCOPY utl_smtp.connection,

                  name  IN VARCHAR2,

                  value IN VARCHAR2) IS

  BEGIN

    utl_smtp.write_data(conn, name || ': ' || value || utl_tcp.CRLF);

  END;


  -- Mark a message-part boundary.  Set <last> to TRUE for the last boundary.

  PROCEDURE write_boundary(conn  IN OUT NOCOPY utl_smtp.connection,

               last  IN            BOOLEAN DEFAULT FALSE) AS

  BEGIN

    IF (last) THEN

      utl_smtp.write_data(conn, LAST_BOUNDARY);

    ELSE

      utl_smtp.write_data(conn, FIRST_BOUNDARY);

    END IF;

  END;


  ------------------------------------------------------------------------

  PROCEDURE mail(sender     IN VARCHAR2

               , recipients IN VARCHAR2

               , subject    IN VARCHAR2

               , message    IN VARCHAR2) IS

    conn utl_smtp.connection;

  BEGIN

    IF fnd_global.conc_program_id = -1 THEN

      dbms_output.put_line('The E-Mail functionality can only be used when being called by a Concurrent Program.');

    ELSE

      conn := begin_mail(sender, recipients, subject);

      write_text(conn, message);

      end_mail(conn);

    END IF;

  END;


   PROCEDURE mail(srce     IN VARCHAR2

   , sender     IN VARCHAR2

               , recipients IN VARCHAR2

               , subject    IN VARCHAR2

               , message    IN VARCHAR2) IS

    conn utl_smtp.connection;

  BEGIN

    --IF fnd_global.conc_program_id = -1 THEN

    --  dbms_output.put_line('The E-Mail functionality can only be used when being called by a Concurrent Program.');

    --ELSE

      conn := begin_mail(sender, recipients, subject);

      write_text(conn, message);

      end_mail(conn);

    --END IF;

  END;


  PROCEDURE mail_attach(sender     IN VARCHAR2

               , recipients IN VARCHAR2

               , subject    IN VARCHAR2

               , message    IN VARCHAR2

               , p_attch    IN VARCHAR2)

IS

    conn utl_smtp.connection;

  BEGIN

    --IF fnd_global.conc_program_id = -1 THEN

    --  dbms_output.put_line('The E-Mail functionality can only be used when being called by a Concurrent Program.');

    --ELSE

      conn := begin_mail(sender, recipients, subject);

      --

      IF p_attch is not null then

        attach_text( conn => conn, data => message, inline => False, filename  => p_attch, last => true);

      else

        write_text(conn, message);

      end if;

      end_mail(conn);

    --END IF;

  END;


  ------------------------------------------------------------------------

  FUNCTION begin_mail(sender     IN VARCHAR2,

              recipients IN VARCHAR2,

              subject    IN VARCHAR2,

              mime_type  IN VARCHAR2    DEFAULT 'text/plain',

              priority   IN PLS_INTEGER DEFAULT NULL)

              RETURN utl_smtp.connection IS

    conn utl_smtp.connection;

  BEGIN

    conn := begin_session;

    begin_mail_in_session(conn, sender, recipients, subject, mime_type,

      priority);

    RETURN conn;

  END;


  ------------------------------------------------------------------------

  PROCEDURE write_text(conn    IN OUT NOCOPY utl_smtp.connection,

               message IN VARCHAR2) IS

  BEGIN

    utl_smtp.write_data(conn, message);

  END;


  ------------------------------------------------------------------------

  PROCEDURE write_mb_text(conn    IN OUT NOCOPY utl_smtp.connection,

              message IN            VARCHAR2) IS

  BEGIN

    utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw(message));

  END;


  ------------------------------------------------------------------------

  PROCEDURE write_raw(conn    IN OUT NOCOPY utl_smtp.connection,

              message IN RAW) IS

  BEGIN

    utl_smtp.write_raw_data(conn, message);

  END;


  ------------------------------------------------------------------------

  PROCEDURE attach_text(conn         IN OUT NOCOPY utl_smtp.connection,

            data         IN VARCHAR2,

            mime_type    IN VARCHAR2 DEFAULT 'text/plain',

            inline       IN BOOLEAN  DEFAULT TRUE,

            filename     IN VARCHAR2 DEFAULT NULL,

                last         IN BOOLEAN  DEFAULT FALSE) IS

  BEGIN

    begin_attachment(conn, mime_type, inline, filename);

    write_text(conn, data);

    end_attachment(conn, last);

  END;


  ------------------------------------------------------------------------

  PROCEDURE attach_base64(conn         IN OUT NOCOPY utl_smtp.connection,

              data         IN RAW,

              mime_type    IN VARCHAR2 DEFAULT 'application/octet',

              inline       IN BOOLEAN  DEFAULT TRUE,

              filename     IN VARCHAR2 DEFAULT NULL,

              last         IN BOOLEAN  DEFAULT FALSE) IS

    i   PLS_INTEGER;

    len PLS_INTEGER;

  BEGIN


    begin_attachment(conn, mime_type, inline, filename, 'base64');


    -- Split the Base64-encoded attachment into multiple lines

    i   := 1;

    len := utl_raw.length(data);

    WHILE (i < len) LOOP

       IF (i + MAX_BASE64_LINE_WIDTH < len) THEN

     utl_smtp.write_raw_data(conn,

        utl_encode.base64_encode(utl_raw.substr(data, i,

        MAX_BASE64_LINE_WIDTH)));

       ELSE

     utl_smtp.write_raw_data(conn,

       utl_encode.base64_encode(utl_raw.substr(data, i)));

       END IF;

       utl_smtp.write_data(conn, utl_tcp.CRLF);

       i := i + MAX_BASE64_LINE_WIDTH;

    END LOOP;


    end_attachment(conn, last);


  END;


  ------------------------------------------------------------------------

  PROCEDURE begin_attachment(conn         IN OUT NOCOPY utl_smtp.connection,

                 mime_type    IN VARCHAR2 DEFAULT 'text/plain',

                 inline       IN BOOLEAN  DEFAULT TRUE,

                 filename     IN VARCHAR2 DEFAULT NULL,

                 transfer_enc IN VARCHAR2 DEFAULT NULL) IS

  BEGIN

    write_boundary(conn);

    write_mime_header(conn, 'Content-Type', mime_type);


    IF (filename IS NOT NULL) THEN

       IF (inline) THEN

      write_mime_header(conn, 'Content-Disposition',

        'inline; filename="'||filename||'"');

       ELSE

      write_mime_header(conn, 'Content-Disposition',

        'attachment; filename="'||filename||'"');

       END IF;

    END IF;


    IF (transfer_enc IS NOT NULL) THEN

      write_mime_header(conn, 'Content-Transfer-Encoding', transfer_enc);

    END IF;


    utl_smtp.write_data(conn, utl_tcp.CRLF);

  END;


  ------------------------------------------------------------------------

  PROCEDURE end_attachment(conn IN OUT NOCOPY utl_smtp.connection,

               last IN BOOLEAN DEFAULT FALSE) IS

  BEGIN

    utl_smtp.write_data(conn, utl_tcp.CRLF);

    IF (last) THEN

      write_boundary(conn, last);

    END IF;

  END;


  ------------------------------------------------------------------------

  PROCEDURE end_mail(conn IN OUT NOCOPY utl_smtp.connection) IS

  BEGIN

    end_mail_in_session(conn);

    end_session(conn);

  END;


  ------------------------------------------------------------------------

  FUNCTION begin_session RETURN utl_smtp.connection IS

    conn utl_smtp.connection;

  BEGIN

    -- open SMTP connection

    conn := utl_smtp.open_connection(smtp_host, smtp_port);

    utl_smtp.helo(conn, smtp_domain);

    RETURN conn;

  END;


  ------------------------------------------------------------------------

  PROCEDURE begin_mail_in_session(conn       IN OUT NOCOPY utl_smtp.connection,

                  sender     IN VARCHAR2,

                  recipients IN VARCHAR2,

                  subject    IN VARCHAR2,

                  mime_type  IN VARCHAR2  DEFAULT 'text/plain',

                  priority   IN PLS_INTEGER DEFAULT NULL) IS

    my_recipients VARCHAR2(32767) := recipients;

    my_sender     VARCHAR2(32767) := sender;

  BEGIN


    -- Specify sender's address (our server allows bogus address

    -- as long as it is a full email address (xxx@yyy.com).

    utl_smtp.mail(conn, get_address(my_sender));


    -- Specify recipient(s) of the email.

    WHILE (my_recipients IS NOT NULL) LOOP

      utl_smtp.rcpt(conn, get_address(my_recipients));

    END LOOP;


    -- Start body of email

    utl_smtp.open_data(conn);


    -- Set "From" MIME header

    write_mime_header(conn, 'From', sender);


    -- Set "To" MIME header

    write_mime_header(conn, 'To', recipients);


    -- Set "Subject" MIME header

    write_mime_header(conn, 'Subject', subject);


    -- Set "Content-Type" MIME header

    write_mime_header(conn, 'Content-Type', mime_type);


    -- Set "X-Mailer" MIME header

    write_mime_header(conn, 'X-Mailer', MAILER_ID);


    -- Set priority:

    --   High      Normal       Low

    --   1     2     3     4     5

    IF (priority IS NOT NULL) THEN

      write_mime_header(conn, 'X-Priority', priority);

    END IF;


    -- Send an empty line to denotes end of MIME headers and

    -- beginning of message body.

    utl_smtp.write_data(conn, utl_tcp.CRLF);


    IF (mime_type LIKE 'multipart/mixed%') THEN

      write_text(conn, 'This is a multi-part message in MIME format.' ||

    utl_tcp.crlf);

    END IF;


  END;


  ------------------------------------------------------------------------

  PROCEDURE end_mail_in_session(conn IN OUT NOCOPY utl_smtp.connection) IS

  BEGIN

    utl_smtp.close_data(conn);

  END;


  ------------------------------------------------------------------------

  PROCEDURE end_session(conn IN OUT NOCOPY utl_smtp.connection) IS

  BEGIN

    utl_smtp.quit(conn);

  END;


END;

/

Friday, 29 April 2022

Exporting data from OAF page to Excel programmatically

 Exporting data from OAF page to Excel programmatically


Now we are going to learn about how to export the data of VO from OAF page to excel sheet.

Consider there is a table region which retrieves a data from a VO. Now we have to export that data to excel sheet.

Create a submit button in the table region.

Write the following code in the Controller ProcessFormRequest


/*
****************************************
* Call the export button
****************************************
*/
      if(pageContext.getParameter("Export") != null)
      {
            /* Specify the hidden attribute column names*/
String ss[] = { null };
/* Call the method to export data */
       downloadCsvFile(pageContext, "AOPErrorMsgVO1", null, "MAX", ss);
      }
/* End of calling the export button */

/*
*************************************
* Create method to export the data
*************************************
*/

    public void downloadCsvFile(OAPageContext pageContext, String view_inst_name, String file_name_without_ext, String max_size, String hidden_attrib_list[])
    {
/*
***********************************
* Specify all Column names
**********************************
*/

 String voFieldNames[] = {
        "Error Message","Item/Level1", "BillToCust/Level2", "ODM/Level3", "OEM/Level4", "MarketSegment/Level5", "Program Name/Level6", "Organization/Level7","Time Period","Fiscal Year", "Total Units", "Total Sales"
    };
/* End of column names */
 
        OAViewObject v = (OAViewObject)pageContext.getRootApplicationModule().findViewObject(view_inst_name);
        if(v == null)
        {
            throw new OAException("Could not find View object instance "+view_inst_name+" in root AM.");
        }
        if(v.getFetchedRowCount() == 0)
        {
            throw new OAException("There is no data to export.");
        }
        String file_name = "AOP_Error_Report";
        if(file_name_without_ext != null && !"".equals(file_name_without_ext))
        {
            file_name = file_name_without_ext;
        }
        HttpServletResponse response = (HttpServletResponse)pageContext.getRenderingContext().getServletResponse();
        response.setContentType("application/text");
        response.setHeader("Content-Disposition", "attachment; filename="+file_name+".csv");
        ServletOutputStream pw = null;
        try
        {
            pw = response.getOutputStream();
            int j = 0;
            int k = 0;
            boolean bb = true;
            System.out.println("inside try block");
            if(max_size == null || "".equals(max_size))
            {
                k = Integer.parseInt(pageContext.getProfile("VO_MAX_FETCH_SIZE"));
                bb = false;
            } else
            if("MAX".equals(max_size))
            {
                bb = true;
            } else
            {
                k = Integer.parseInt(max_size);
                bb = false;
            }
            AttributeDef a[] = v.getAttributeDefs();
            StringBuffer cc = new StringBuffer();
            ArrayList exist_list = new ArrayList();
            for(int l = 0; l < a.length; l++)
            {
                boolean zx = true;
                if(hidden_attrib_list != null)
                {
                    for(int z = 0; z < hidden_attrib_list.length; z++)
                    {
                        if(a[l].getName().equals(hidden_attrib_list[z]))
                        {
                            zx = false;
                            exist_list.add(String.valueOf(a[l].getIndex()));
                        }
                    }

                }
            }

            for(int l = 0; l < voFieldNames.length; l++)
            {
                boolean zx = true;
                if(zx)
                {
                    cc.append("\""+voFieldNames[l])+"\"");
                    cc.append(",");
                }
            }

            String header_row = cc.toString();
            pw.println(header_row);
            OAViewRowImpl row = (OAViewRowImpl)v.first();
            do
            {
                if(row == null)
                {
                    break;
                }
                j++;
                StringBuffer b = new StringBuffer();
                for(int i = 0; i < v.getAttributeCount(); i++)
                {
                    boolean cv = true;
                    for(int u = 0; u < exist_list.size(); u++)
                    {
                        if(String.valueOf(i).equals(exist_list.get(u).toString()))
                        {
                            cv = false;
                        }
                    }

                    if(cv)
                    {
                        Object o = row.getAttribute(i);
                        if(o != null)
                        {
                            if(o.getClass().equals(Class.forName("oracle.jbo.domain.Date")))
                            {
                                Date dt = (Date)o;
                                java.sql.Date ts = dt.dateValue();
                                SimpleDateFormat displayDateFormat = new SimpleDateFormat("dd-MMM-yyyy");
                                String convertedDateString = displayDateFormat.format(ts);
                                b.append("\"" + convertedDateString + "\"");
                            } else
                            {
                                b.append("\"" + o.toString() + "\"");
                            }
                        } else
                        {
                            b.append("\"\"");
                        }
                        b.append(",");
                    }
                }

                String final_row = b.toString();
                pw.println(final_row);
                if(!bb && j == k)
                {
                    break;
                }
                row = (OAViewRowImpl)v.next();
            } while(true);
        }
        catch(Exception e)
        {
            e.printStackTrace();
            throw new OAException("Unexpected Exception occurred.Exception Details :"+e.toString());
        }
        pageContext.setDocumentRendered(false);
        try
        {
            pw.flush();
            pw.close();
        }
        catch(IOException ioexception)
        {
            ioexception.printStackTrace();
            throw new OAException("Unexpected Exception occurred.Exception Details :" ioexception.toString());
        }
        try
        {
            pw.flush();
            pw.close();
        }
        catch(IOException e)
        {
            e.printStackTrace();
            throw new OAException("Unexpected Exception occurred.Exception Details :"e.toString());
        }
    }/*End of  Export  method*/

Uploading PO Attachments from EBS to FTP Server

 create or replace PROCEDURE xx_upload_po_attachment(errbuff out varchar2, retcode out number)  IS CURSOR cur_new_attmt IS    select ponumbe...