2 Replies Latest reply: Aug 12, 2014 6:53 AM by Pravin Takpire RSS

    My procedure is take many days to run, please help me for tuning.

    user11996389

      Hi,

      Procedure registered as concurrent program is running very slow, taking many days to run. so please help me for tuning.

      Here is the procedure code.

       

      CREATE OR REPLACE PROCEDURE APPS.xxm_ap_lib_ageing_rep (

         p_errbuff   OUT      VARCHAR2,

         p_retcode   OUT      NUMBER,

         acc_date    IN       DATE,

         cmp_code    IN       VARCHAR2,

         cost_cntr   IN       VARCHAR2,

         acc_code    IN       VARCHAR2,

         p_loc       IN       VARCHAR2

      )

      IS

         tot_inv_amt         NUMBER             := 0;

         tot_amt_90          NUMBER             := 0;

         tot_amt_180         NUMBER             := 0;

         tot_amt_365         NUMBER             := 0;

         tot_amt_730         NUMBER             := 0;

         tot_amt_1095        NUMBER             := 0;

         tot_amt_above1095   NUMBER             := 0;

         l_org_id            NUMBER             := fnd_profile.VALUE ('ORG_ID');

       

         CURSOR c1

         IS

           SELECT /*+ USE_NL(A1) */ (SELECT a.approver_comments

                                  FROM ap_inv_aprvl_hist_all a

                                 WHERE a.invoice_id = d.invoice_id

                                   AND a.response = 'REJECTED'

                                   AND a.approver_comments IS NOT NULL

                                   AND ROWID = (SELECT MAX(ROWID)

                                                  FROM ap_inv_aprvl_hist_all k

                                                 WHERE k.invoice_id = a.invoice_id

                                                   AND k.approver_comments IS NOT NULL

                                                   AND k.response = 'REJECTED')) approver_comments,

             b.posted_flag,

             NVL(f.currency_code, d.invoice_currency_code) currency_code,

             d.invoice_amount,

             d.wfapproval_status,

             d.invoice_num,

             d.invoice_id,

             i.segment1 v_code,

             i.vendor_name,

             NVL(d.invoice_amount, 0) - NVL(d.amount_paid, 0) amt,

             SUM(nvl(A1.acctd_rounded_cr, 0)) - SUM(nvl(A1.acctd_rounded_dr, 0)) amount,

             b.accounting_date,

             f.segment1 po_num,

             f.comments descr,

             h.receipt_num rct_num,

             c.segment6 loc,

             c.segment2 cost_centre,

             d.doc_sequence_value ap_voucher,

             j.employee_number,

             j.full_name,

             (SELECT DISTINCT pj.NAME

                FROM pa_projects_all pj

               WHERE pj.project_id = b.project_id) projnect_name,

             (SELECT fpay.empl_dept_code

                FROM fpay_empl@xxmmulsr1 fpay,

                     fnd_user usr

               WHERE usr.user_name = TO_CHAR(fpay.empl_id)

                 AND usr.employee_id = f.agent_id) dept,

             CASE WHEN TO_CHAR(TO_DATE(acc_date, 'DD-MON-YY') - TO_DATE(b.accounting_date, 'DD-MON-YY')) <= 90 THEN ROUND(SUM(A1.acctd_rounded_cr) - SUM(A1.acctd_rounded_dr), 2)

                  ELSE 0 END amount90_days,

             CASE WHEN TO_CHAR(TO_DATE(acc_date, 'DD-MON-YY') - TO_DATE(b.accounting_date, 'DD-MON-YY')) > 90

         AND TO_CHAR(TO_DATE(acc_date, 'DD-MON-YY') - TO_DATE(b.accounting_date, 'DD-MON-YY')) <= 180 THEN ROUND(SUM(A1.acctd_rounded_cr) - SUM(A1.acctd_rounded_dr), 2)

                  ELSE 0 END amount180_days,

             CASE WHEN TO_CHAR(TO_DATE(acc_date, 'DD-MON-YY') - TO_DATE(b.accounting_date, 'DD-MON-YY')) > 180

         AND TO_CHAR(TO_DATE(acc_date, 'DD-MON-YY') - TO_DATE(b.accounting_date, 'DD-MON-YY')) <= 365 THEN ROUND(SUM(A1.acctd_rounded_cr) - SUM(A1.acctd_rounded_dr), 2)

                  ELSE 0 END amount365_days,

             CASE WHEN TO_CHAR(TO_DATE(acc_date, 'DD-MON-YY') - TO_DATE(b.accounting_date, 'DD-MON-YY')) > 365

         AND TO_CHAR(TO_DATE(acc_date, 'DD-MON-YY') - TO_DATE(b.accounting_date, 'DD-MON-YY')) <= 730 THEN ROUND(SUM(A1.acctd_rounded_cr) - SUM(A1.acctd_rounded_dr), 2)

                  ELSE 0 END amount730_days,

             CASE WHEN TO_CHAR(TO_DATE(acc_date, 'DD-MON-YY') - TO_DATE(b.accounting_date, 'DD-MON-YY')) > 730

         AND TO_CHAR(TO_DATE(acc_date, 'DD-MON-YY') - TO_DATE(b.accounting_date, 'DD-MON-YY')) <= 1095 THEN ROUND(SUM(A1.acctd_rounded_cr) - SUM(A1.acctd_rounded_dr), 2)

                  ELSE 0 END amount1095_days,

             CASE WHEN TO_CHAR(TO_DATE(acc_date, 'DD-MON-YY') - TO_DATE(b.accounting_date, 'DD-MON-YY')) > 1095 THEN ROUND(SUM(A1.acctd_rounded_cr) - SUM(A1.acctd_rounded_dr), 2)

                  ELSE 0 END amount_above1095_days

        FROM xla.xla_transaction_entities xte,

             ap.ap_invoice_distributions_all b,

             ap.ap_invoice_lines_all k,

             gl.gl_code_combinations c,

             ap.ap_invoices_all d,

             po.po_distributions_all e,

             po.po_headers_all f,

             apps.rcv_transactions g,

             apps.rcv_shipment_headers h,

             ap_suppliers i,

             apps.per_all_people_f j,

             (SELECT tb.code_combination_id,

                     tb.LEDGER_ID,

                     tb.PARTY_ID party_id,

                     tb.GL_DATE,

                     tb.AE_HEADER_ID,

                     NVL(tb.applied_to_entity_id, tb.source_entity_id) entity_id,

                     tb.DEFINITION_CODE,

                     tb.RECORD_TYPE_CODE,

                     SUM(NVL(tb.acctd_rounded_cr, 0)) acctd_rounded_cr,

                     SUM(NVL(tb.acctd_rounded_dr, 0)) acctd_rounded_dr,

                     SUM(NVL(tb.acctd_rounded_cr, 0)) - SUM(NVL(tb.acctd_rounded_dr, 0)) diff,

                     party_id

                FROM xla.xla_trial_balances tb

               WHERE tb.definition_code = 'AP_200_1001'

                 and tb.gl_date <= acc_date

                 and exists (select 1

                               from gl_code_combinations A2

                              where A2.SEGMENT3 = acc_code

                                and A2.code_combination_id = tb.CODE_COMBINATION_ID)

               GROUP BY tb.code_combination_id, tb.LEDGER_ID, tb.PARTY_ID, tb.PARTY_ID, NVL(tb.applied_to_entity_id, tb.source_entity_id), tb.RECORD_TYPE_CODE, tb.party_id, tb.GL_DATE, tb.DEFINITION_CODE, tb.AE_HEADER_ID) A1

      WHERE A1.code_combination_id = c.code_combination_id

         and A1.ENTITY_ID = xte.ENTITY_ID

         AND xte.application_id = 200

         AND xte.ledger_id = A1.ledger_id

         and NVL(SOURCE_ID_INT_1, -99) = d.INVOICE_ID

         and xte.LEDGER_ID = 1001

         and xte.ENTITY_CODE = 'AP_INVOICES'

         AND d.invoice_id = k.invoice_id

         AND k.line_number = b.invoice_line_number

         AND c.segment3 = acc_code

         AND (cost_cntr is not null

              and c.segment2 = cost_cntr

               or cost_cntr is null)

         AND (p_loc is not null

              and c.segment6 = p_loc

               or p_loc is null)

         AND A1.gl_date <= acc_date

         AND k.invoice_id = b.invoice_id

         AND b.distribution_line_number = '1'

         AND k.line_number = '1'

         AND b.po_distribution_id = e.po_distribution_id

         AND e.po_header_id = f.po_header_id

         AND b.rcv_transaction_id = g.transaction_id

         AND g.shipment_header_id = h.shipment_header_id

         AND f.agent_id = j.person_id

         and i.vendor_id = d.vendor_id

         AND d.set_of_books_id = A1.ledger_id

        -- AND d.org_id = l_org_id  (20-MAY-2014 AS REQUIRED BY VINOD)

         AND TRUNC(SYSDATE) BETWEEN j.effective_start_date AND j.effective_end_date

      HAVING SUM(A1.acctd_rounded_cr) - SUM(A1.acctd_rounded_dr) <> 0

      GROUP BY f.currency_code, d.invoice_amount, b.project_id, d.wfapproval_status, d.invoice_num,

      d.invoice_id, b.accounting_date, f.segment1, h.receipt_num, i.segment1, i.vendor_name, c.segment6,

      d.doc_sequence_value, c.segment2, j.employee_number, j.full_name, f.agent_id, f.comments,

      d.invoice_currency_code, b.posted_flag, d.amount_paid, d.invoice_id;

       

       

       

       

         /* SELECT   (SELECT a.approver_comments

                        FROM ap_inv_aprvl_hist_all a

                       WHERE a.invoice_id = d.invoice_id

                         AND a.response = 'REJECTED'

                         AND a.approver_comments IS NOT NULL

                         AND ROWID =

                                (SELECT MAX (ROWID)

                                   FROM ap_inv_aprvl_hist_all k

                                  WHERE k.invoice_id = a.invoice_id

                                    AND k.approver_comments IS NOT NULL

                                    AND k.response = 'REJECTED'))

                                                                 approver_comments,

                     b.posted_flag,

                     NVL (f.currency_code, d.invoice_currency_code) currency_code,

                     d.invoice_amount, d.wfapproval_status, d.invoice_num,

                     d.invoice_id, i.segment1 v_code, i.vendor_name,

                     NVL (d.invoice_amount, 0) - NVL (d.amount_paid, 0) amt,

                     SUM (nvl(a.acctd_rounded_cr,0)) - SUM (nvl(a.acctd_rounded_dr,0)) amount,

                     --NVL (d.invoice_amount, 0) - NVL (d.amount_paid, 0) amount,

                     b.accounting_date, f.segment1 po_num, f.comments descr,

                     h.receipt_num rct_num, c.segment6 loc, c.segment2 cost_centre,

                     d.doc_sequence_value ap_voucher, j.employee_number,

                     j.full_name,

                     (SELECT DISTINCT pj.NAME

                                 FROM pa_projects_all pj

                                WHERE pj.project_id = b.project_id) projnect_name,

                     (SELECT fpay.empl_dept_code

                        FROM fpay_empl@xxmmulsr1 fpay, fnd_user usr

                       WHERE usr.user_name = TO_CHAR (fpay.empl_id)

                         AND usr.employee_id = f.agent_id) dept,

                     CASE

                        WHEN TO_CHAR (  TO_DATE (acc_date, 'DD-MON-YY')

                                      - TO_DATE (b.accounting_date, 'DD-MON-YY')

                                     ) <= 90

                           THEN ROUND (  SUM (a.acctd_rounded_cr)

                                       - SUM (a.acctd_rounded_dr),

                                       2

                                      )

                        ELSE 0

                     END amount90_days,

                     CASE

                        WHEN TO_CHAR (  TO_DATE (acc_date, 'DD-MON-YY')

                                      - TO_DATE (b.accounting_date, 'DD-MON-YY')

                                     ) > 90

                        AND TO_CHAR (  TO_DATE (acc_date, 'DD-MON-YY')

                                     - TO_DATE (b.accounting_date, 'DD-MON-YY')

                                    ) <= 180

                           THEN ROUND (  SUM (a.acctd_rounded_cr)

                                       - SUM (a.acctd_rounded_dr),

                                       2

                                      )

                        ELSE 0

                     END amount180_days,

                     CASE

                        WHEN TO_CHAR (  TO_DATE (acc_date, 'DD-MON-YY')

                                      - TO_DATE (b.accounting_date, 'DD-MON-YY')

                                     ) > 180

                        AND TO_CHAR (  TO_DATE (acc_date, 'DD-MON-YY')

                                     - TO_DATE (b.accounting_date, 'DD-MON-YY')

                                    ) <= 365

                           THEN ROUND (  SUM (a.acctd_rounded_cr)

                                       - SUM (a.acctd_rounded_dr),

                                       2

                                      )

                        ELSE 0

                     END amount365_days,

                     CASE

                        WHEN TO_CHAR (  TO_DATE (acc_date, 'DD-MON-YY')

                                      - TO_DATE (b.accounting_date, 'DD-MON-YY')

                                     ) > 365

                        AND TO_CHAR (  TO_DATE (acc_date, 'DD-MON-YY')

                                     - TO_DATE (b.accounting_date, 'DD-MON-YY')

                                    ) <= 730

                           THEN ROUND (  SUM (a.acctd_rounded_cr)

                                       - SUM (a.acctd_rounded_dr),

                                       2

                                      )

                        ELSE 0

                     END amount730_days,

                     CASE

                        WHEN TO_CHAR (  TO_DATE (acc_date, 'DD-MON-YY')

                                      - TO_DATE (b.accounting_date, 'DD-MON-YY')

                                     ) > 730

                        AND TO_CHAR (  TO_DATE (acc_date, 'DD-MON-YY')

                                     - TO_DATE (b.accounting_date, 'DD-MON-YY')

                                    ) <= 1095

                           THEN ROUND (  SUM (a.acctd_rounded_cr)

                                       - SUM (a.acctd_rounded_dr),

                                       2

                                      )

                        ELSE 0

                     END amount1095_days,

                     CASE

                        WHEN TO_CHAR

                                 (  TO_DATE (acc_date, 'DD-MON-YY')

                                  - TO_DATE (b.accounting_date, 'DD-MON-YY')

                                 ) > 1095

                           THEN ROUND (  SUM (a.acctd_rounded_cr)

                                       - SUM (a.acctd_rounded_dr),

                                       2

                                      )

                        ELSE 0

                     END amount_above1095_days

                FROM --apps.xla_trial_balances a,

                     xla.xla_transaction_entities            xte,

                     ap.ap_invoice_distributions_all b,

                     ap.ap_invoice_lines_all k,

                     gl.gl_code_combinations c,

                     ap.ap_invoices_all d,

                     po.po_distributions_all e,

                     po.po_headers_all f,

                     apps.rcv_transactions g,

                     apps.rcv_shipment_headers h,

                   --  apps.po_vendors i,

                     ap_suppliers i,

                     apps.per_all_people_f j,

                      (SELECT   \*+ index(tb XLA_TRIAL_BALANCES_N2) *\

                       tb.code_combination_id,tb.LEDGER_ID,tb.PARTY_ID party_id,tb.GL_DATE,tb.AE_HEADER_ID,

                       NVL (tb.applied_to_entity_id, tb.source_entity_id) entity_id,tb.DEFINITION_CODE,tb.RECORD_TYPE_CODE,

                       SUM (NVL (tb.acctd_rounded_cr, 0)) acctd_rounded_cr,

                       SUM (NVL (tb.acctd_rounded_dr, 0)) acctd_rounded_dr,

                         SUM (NVL (tb.acctd_rounded_cr, 0))

                       - SUM (NVL (tb.acctd_rounded_dr, 0)) diff,

                       party_id

                  FROM xla.xla_trial_balances tb

                 WHERE tb.definition_code ='AP_200_1001'  -- Ex :  'AP_200_1001'

                     and tb.gl_date <= acc_date

                     --and tb.CODE_COMBINATION_ID = 70119

                     --and tb.CODE_COMBINATION_ID in (select code_combination_id from gl_code_combinations a where a.SEGMENT3 =acc_code  )

                     and exists (select 1 from gl_code_combinations a where a.SEGMENT3 =acc_code and a.code_combination_id = tb.CODE_COMBINATION_ID )

                    GROUP BY tb.code_combination_id,tb.LEDGER_ID,tb.PARTY_ID,tb.PARTY_ID,

                       NVL (tb.applied_to_entity_id, tb.source_entity_id),tb.RECORD_TYPE_CODE,

                       tb.party_id,tb.GL_DATE,tb.DEFINITION_CODE,tb.AE_HEADER_ID

                       ) a

               WHERE a.code_combination_id = c.code_combination_id

               --and d.INVOICE_NUM = '0LJFE-13-3713'

                 --and    a.invoice_id                    =       d.invoice_id

                  --AND a.definition_code                   =       'AP_200_1001'

                  and a.ENTITY_ID                 =       xte.ENTITY_ID

                  AND xte.application_id                 =       200

                  AND xte.ledger_id                      =       a.ledger_id

                  and NVL(SOURCE_ID_INT_1,(-99))         =       d.INVOICE_ID

                  and xte.LEDGER_ID = 1001

                  and xte.ENTITY_CODE = 'AP_INVOICES'

                 AND d.invoice_id = k.invoice_id

                 AND k.line_number = b.invoice_line_number

                 AND c.segment3 = acc_code --'05D0709'

                 --AND c.segment2 = NVL (cost_cntr, c.segment2)

                 --AND c.segment6 = NVL (p_loc, c.segment6)

                 AND ((cost_cntr is not null and c.segment2 = cost_cntr) or cost_cntr is null)

                 AND ((p_loc is not null and c.segment6 = p_loc) or p_loc is null)

                 AND a.gl_date <= acc_date--'31-DEC-13'

                 AND k.invoice_id = b.invoice_id--

                 AND b.distribution_line_number = '1'

                 AND k.line_number = '1'

                 AND b.po_distribution_id = e.po_distribution_id(+)

                 AND e.po_header_id = f.po_header_id(+)

                 AND b.rcv_transaction_id = g.transaction_id(+)

                 AND g.shipment_header_id = h.shipment_header_id(+)

                 AND f.agent_id = j.person_id(+)

                 and i.vendor_id = d.vendor_id

                 AND d.set_of_books_id = a.ledger_id

                 AND d.org_id = l_org_id

                 AND TRUNC (SYSDATE) BETWEEN j.effective_start_date(+) AND j.effective_end_date(+)

                HAVING SUM (a.acctd_rounded_cr) - SUM (a.acctd_rounded_dr) <> 0

            GROUP BY f.currency_code,

                     d.invoice_amount,

                     b.project_id,

                     d.wfapproval_status,

                     d.invoice_num,

                     d.invoice_id,

                     b.accounting_date,

                     f.segment1,

                     h.receipt_num,

                     i.segment1,

                     i.vendor_name,

                     c.segment6,

                     d.doc_sequence_value,

                     c.segment2,

                     j.employee_number,

                     j.full_name,

                     f.agent_id,

                     f.comments,

                     d.invoice_currency_code,

                     b.posted_flag,

                     d.amount_paid,

                     d.invoice_id;*/

       

         fp                  UTL_FILE.file_type;

      BEGIN

         fp := UTL_FILE.fopen ('/usr/tmp', 'RECE.txt', 'W');

         fnd_file.put_line

            (fnd_file.output,

                '                                           MSIL LIABILITY AGEING REPORT AS ON DATE ---- '

             || acc_date

            );

         fnd_file.put_line (fnd_file.output, ' ');

         fnd_file.put_line (fnd_file.output, RPAD ('-', 435, '-'));

         fnd_file.put_line (fnd_file.output,

                               RPAD (NVL ('Invoice Num', ' '), 55, ' ')

                            || RPAD (NVL ('Invoice ID', ' '), 15, ' ')

                            || RPAD (NVL ('Vendor Code', ' '), 15, ' ')

                            || RPAD (NVL ('Vendor Name', ' '), 50, ' ')

                            || RPAD (NVL ('Amount', ' '), 20, ' ')

                            || RPAD (NVL ('Accntng Date', ' '), 14, ' ')

                            || RPAD (NVL ('PO Num', ' '), 10, ' ')

                            || RPAD (NVL ('Rcpt Num', ' '), 10, ' ')

                            || RPAD (NVL ('LOC', ' '), 7, ' ')

                            || RPAD (NVL ('Cst Cntr', ' '), 10, ' ')

                            || RPAD (NVL ('AP Vou', ' '), 9, ' ')

                            || RPAD (NVL ('Invoice Status', ' '), 20, ' ')

                            || RPAD (NVL ('DEPT', ' '), 9, ' ')

                            || RPAD (NVL ('Emp Num', ' '), 9, ' ')

                            || RPAD (NVL ('Full Name', ' '), 30, ' ')

                            || RPAD (NVL ('Currency name', ' '), 30, ' ')

                            || RPAD (NVL ('Original FC Amount', ' '), 30, ' ')

                            || RPAD (NVL ('Rem. FC Amount', ' '), 30, ' ')

                            || RPAD (NVL ('Project Name', ' '), 60, ' ')

                            || RPAD (NVL ('Posted to GL', ' '), 20, ' ')

                            || RPAD (NVL ('< 90_days', ' '), 20, ' ')

                            || RPAD (NVL ('< 180_days', ' '), 20, ' ')

                            || RPAD (NVL ('< 365_days', ' '), 20, ' ')

                            || RPAD (NVL ('< 730_days', ' '), 20, ' ')

                            || RPAD (NVL ('< 1095_days', ' '), 20, ' ')

                            || RPAD (NVL ('> 1095_days', ' '), 20, ' ')

                            || RPAD (NVL ('PO Description', ' '), 70, ' ')

                            || RPAD (NVL ('Rejected Reason', ' '), 70, ' ')

                           );

         fnd_file.put_line (fnd_file.output, RPAD ('-', 465, '-'));

       

         FOR i IN c1

       

         LOOP

       

         -- fnd_file.put_line (fnd_file.output, 'Invoice_id -'|| i.invoice_id);

            fnd_file.put_line (fnd_file.output,

                                  RPAD (NVL (TO_CHAR (i.invoice_num), ' '), 55, ' ')

                               || RPAD (NVL (TO_CHAR (i.invoice_id), ' '), 15, ' ')

                               || RPAD (NVL (TO_CHAR (i.v_code), ' '), 15, ' ')

                               || RPAD (NVL (TO_CHAR (i.vendor_name), ' '), 50, ' ')

                               || RPAD (NVL (TO_CHAR (i.amount), ' '), 20, ' ')

                               || RPAD (NVL (TO_CHAR (i.accounting_date), ' '),

                                        14,

                                        ' '

                                       )

                               || RPAD (NVL (TO_CHAR (i.po_num), ' '), 10, ' ')

                               || RPAD (NVL (TO_CHAR (i.rct_num), ' '), 10, ' ')

                               || RPAD (NVL (TO_CHAR (i.loc), ' '), 7, ' ')

                               || RPAD (NVL (TO_CHAR (i.cost_centre), ' '), 10, ' ')

                               || RPAD (NVL (TO_CHAR (i.ap_voucher), ' '), 9, ' ')

                               || RPAD (NVL (TO_CHAR (i.wfapproval_status), ' '),

                                        20,

                                        ' '

                                       )

                               || RPAD (NVL (TO_CHAR (i.dept), ' '), 9, ' ')

                               || RPAD (NVL (TO_CHAR (i.employee_number), ' '),

                                        9,

                                        ' '

                                       )

                               || RPAD (NVL (TO_CHAR (i.full_name), ' '), 30, ' ')

                               || RPAD (NVL (TO_CHAR (i.currency_code), ' '),

                                        30,

                                        ' '

                                       )

                               || RPAD (NVL (TO_CHAR (i.invoice_amount), ' '),

                                        30,

                                        ' '

                                       )

                               || RPAD (NVL (TO_CHAR (i.amt), ' '), 30, ' ')

                               || RPAD (NVL (TO_CHAR (i.projnect_name), ' '),

                                        60,

                                        ' '

                                       )

                               || RPAD (NVL (TO_CHAR (i.posted_flag), ' '), 20, ' ')

                               || RPAD (NVL (TO_CHAR (i.amount90_days), ' '),

                                        20,

                                        ' '

                                       )

                               || RPAD (NVL (TO_CHAR (i.amount180_days), ' '),

                                        20,

                                        ' '

                                       )

                               || RPAD (NVL (TO_CHAR (i.amount365_days), ' '),

                                        20,

                                        ' '

                                       )

                               || RPAD (NVL (TO_CHAR (i.amount730_days), ' '),

                                        20,

                                        ' '

                                       )

                               || RPAD (NVL (TO_CHAR (i.amount1095_days), ' '),

                                        20,

                                        ' '

                                       )

                               || RPAD (NVL (TO_CHAR (i.amount_above1095_days), ' '),

                                        20,

                                        ' '

                                       )

                               || RPAD (NVL (TO_CHAR (i.descr), ' '), 70, ' ')

                               || RPAD (NVL (TO_CHAR (i.approver_comments), ' '),

                                        70,

                                        ' '

                                       )

                              );

            tot_inv_amt := tot_inv_amt + i.amount;

            tot_amt_90 := tot_amt_90 + i.amount90_days;

            tot_amt_180 := tot_amt_180 + i.amount180_days;

            tot_amt_365 := tot_amt_365 + i.amount365_days;

            tot_amt_730 := tot_amt_730 + i.amount730_days;

            tot_amt_1095 := tot_amt_1095 + i.amount1095_days;

            tot_amt_above1095 := tot_amt_above1095 + i.amount_above1095_days;

         END LOOP;

       

         fnd_file.put_line (fnd_file.output, RPAD ('-', 465, '-'));

         fnd_file.put_line (fnd_file.output,

                               LPAD (NVL (TO_CHAR (tot_inv_amt), ' '), 142, ' ')

                            || LPAD (NVL (TO_CHAR (tot_amt_90), ' '), 123, ' ')

                            || LPAD (NVL (TO_CHAR (tot_amt_180), ' '), 19, ' ')

                            || LPAD (NVL (TO_CHAR (tot_amt_365), ' '), 22, ' ')

                            || LPAD (NVL (TO_CHAR (tot_amt_730), ' '), 17, ' ')

                            || LPAD (NVL (TO_CHAR (tot_amt_1095), ' '), 20, ' ')

                            || LPAD (NVL (TO_CHAR (tot_amt_above1095), ' '), 20,

                                     ' ')

                           );

         fnd_file.put_line (fnd_file.output, RPAD ('-', 465, '-'));

         UTL_FILE.fclose (fp);

      END;

      /