9 Replies Latest reply: Dec 13, 2012 4:02 AM by AlbertoFaenza RSS

    UTL_FILE reqeust taking too much time for completion

    977941
      Hi,

      We are running utl_file reqeust.
      the select statement fetching 6000 records, for this it taking 22 mins for completion.

      the code is
      -----------------


      CREATE OR REPLACE PACKAGE BODY "XXC"."XXC_MOD_IN_068_AP_TO_FIS_PKG"
      AS
      /*******************************************************************************
      * Module Type : PL/SQL
      * Module Name : XXC_MOD_IN_068_AP_FIS_PKG
      * Description : This package is used for AP to Fiscal Interface.
      * Run Env. : SQL*Plus
      * Procedure Name Description
      * XXC_MOD_068_AP_PR XXC_MOD_068_AP_PR Procedure is used to insert transactions
      * into CSV OutPut File from Oracle Account Payables.
      * Calling Module: None
      * Modules Called: XXC_COMMON_INT_PK.INSERT_AUDIT
      * Module Number : MOD_IN_068

      * Known Bugs and Restrictions: none
      * History
      * =======
      *
      * Version Name Date Description of Change
      * ------- ------------- ----------- -------------------------------------------
      * 0.1 Sanjeev Khurana 25-JULY-2011 Initial Creation.
      * 0.2 Rohit 09-DEC-2011 Updated header details for the file
      * 0.3 Amit Kulwal 28-AUG-2012 Updated the cursor query for incident 671520
      * 0.4 Swaraj Goud 20-Nov-2012 Updated as per the CR 671520
      ***************************************************************************************
      */

      /************************
      | PACKAGE BODY
      ************************/
      --------------------------------------------------------------------------------
      -- Actual Code Start Here
      --------------------------------------------------------------------------------
      --------------------------------------------------------------------------------
      -- Procedure : XXC_MOD_068_AP_PR
      -- Description : XXC_MOD_068_AP_PR Procedure is used to insert transactions
      -- into CSV OUTPUT File from Oracle Account Payables.
      -- Parameters:
      -- Parm Name I/O Description
      -- ----------------- ---- ----------------------------------------------------
      -- p_errbuf OUT Error message.
      -- p_retcode OUT Error code. Returns 0 if no errors otherwise returns 1.
      -- p_start_date IN Start Date
      -- p_end_date IN End Date
      --------------------------------------------------------------------------------
      PROCEDURE xxc_mod_068_ap_pr (
      p_errbuf OUT VARCHAR2,
      p_retcode OUT NUMBER,
      p_start_date IN VARCHAR2,
      p_end_date IN VARCHAR2
      )
      IS
      -- Define variables and assign default values
      l_sucess_count NUMBER := 0;
      l_error_count NUMBER := 0;
      -- Standard declaration
      l_source VARCHAR2 (10);
      l_target VARCHAR2 (10);
      lc_module_description VARCHAR2 (50)
      := 'MOD_IN_068 - AP to Fiscal';
      l_status CONSTANT VARCHAR2 (50) := 'NEW';
      p_status NUMBER;
      l_batch_id NUMBER;
      l_batch_id_next NUMBER
      := apps_common_out_batch_id_s1.NEXTVAL;
      l_proc_name VARCHAR2 (100) := 'XXC_MOD_IN_068';
      l_request_id NUMBER
      := fnd_global.conc_request_id;
      l_audit_master_id NUMBER := NULL;
      l_mod_code VARCHAR2 (100);
      l_log_type NUMBER := 1; --INFORMATION
      l_det_status_success NUMBER := 0; --SUCCESS
      l_det_status_inprocess NUMBER := 3; --INPROCESS
      l_det_status_rejected NUMBER := 4; --REJECTED
      l_det_status_err NUMBER := 3; --Error
      l_det_status_complete NUMBER := 9; --COMPLETE
      -- Standard who Columns
      l_created_by NUMBER := fnd_global.user_id;
      l_creation_date DATE := SYSDATE;
      l_last_update_date DATE := SYSDATE;
      l_last_update_login NUMBER := fnd_global.user_id;
      v_file UTL_FILE.file_type;
      l_location VARCHAR2 (150);
      l_archive_location VARCHAR2 (150);
      l_date VARCHAR2 (50);
      l_filename VARCHAR2 (50);
      l_open_mode VARCHAR2 (1) := 'W';
      --- l_max_linesize NUMBER := 32767;
      l_max_linesize VARCHAR2 (150); -- Updated 09-Nov-2012
           


      --Cursor is used to fetch valid records for the interface
      CURSOR get_ap_data_inv
      IS
      SELECT asp.segment1 supplier_ref,
      -- asp.vendor_name supplier_name,
      replace(asp.vendor_name, ',', ' ') supplier_name,
      --aia.invoice_num invoice_number,
      replace(aia.invoice_num, ',','') invoice_number,
      aia.invoice_date,
      aia.invoice_amount amount,
      aia.doc_sequence_value unique_id,
      aia.creation_date date_invoice_entered,
      apsa.due_date date_invoice_paid,
      aia.SOURCE user_id,
      aia.payment_status_flag,
      aia.invoice_type_lookup_code doc_type,
      --aia.description,
      replace(aia.description, ',' , ' ') description,
      apsa.gross_amount
      FROM ap_invoices_all aia,
      ap_suppliers asp,
      apps.ap_payment_schedules_all apsa
      -- apps.iby_payments_all iba,
      -- apps.iby_docs_payable_all dp
      WHERE aia.invoice_id = apsa.invoice_id
      AND aia.vendor_id = asp.vendor_id
      AND aia.org_id = apsa.org_id
      -- AND apsa.payment_status_flag != 'Y' -- commented for CR
      -- AND dp.payment_id = iba.payment_id(+)
      -- AND aia.invoice_id = dp.calling_app_doc_unique_ref2(+)
      -- AND apsa.due_date <= (SYSDATE + 1)
      AND TRUNC (aia.creation_date)
      BETWEEN NVL (fnd_date.canonical_to_date (p_start_date),
      TRUNC (aia.creation_date))
      AND NVL (fnd_date.canonical_to_date (p_end_date),
      TRUNC (aia.creation_date));
           TYPE xxc_tbl IS TABLE OF get_ap_data_inv%ROWTYPE;
      xxc_tbl1 xxc_tbl;

      BEGIN
      l_batch_id := apps_common_out_batch_id_s1.CURRVAL;
      xxc_common_int_pk.insert_audit (p_batch_id => l_batch_id,
      p_request_id => l_request_id,
      p_source_system => l_source,
      p_proc_name => l_proc_name,
      p_log_type => l_log_type,
      p_det_status => l_det_status_inprocess,
      p_msg_code => NULL,
      p_entity => NULL,
      p_msg_desc => 'Process Starts',
      p_mast_request_id => l_request_id,
      p_record_id => NULL,
      p_source => l_source,
      p_target => l_target,
      p_email => NULL,
      p_mod_code => l_mod_code,
      p_audit_master_id => l_audit_master_id
      );

      -- Get Module Code
      BEGIN
      SELECT TRIM (fval.flex_value),
      TRIM (SUBSTR (fval.description,
      1,
      INSTR (fval.description, ' -')
      ))
      INTO l_source,
      l_mod_code
      FROM fnd_flex_values_vl fval, fnd_flex_value_sets vset
      WHERE vset.flex_value_set_id = fval.flex_value_set_id
      AND vset.flex_value_set_name IN ('XXC_COMM_INT_CONFIG')
      AND fval.enabled_flag = 'Y'
      AND fval.description = lc_module_description;
      EXCEPTION
      WHEN OTHERS
      THEN
      xxc_common_int_pk.insert_audit (p_batch_id => l_batch_id,
      p_request_id => l_request_id,
      p_source_system => l_source,
      p_proc_name => l_proc_name,
      p_log_type => l_log_type,
      p_det_status => l_det_status_err,
      p_msg_code => NULL,
      p_entity => NULL,
      p_msg_desc => 'Error Mode Code',
      p_mast_request_id => l_request_id,
      p_record_id => NULL,
      p_source => l_source,
      p_target => l_target,
      p_email => NULL,
      p_mod_code => l_mod_code,
      p_audit_master_id => l_audit_master_id
      );
      raise_application_error (-20045, SQLERRM);
      END;

      --File Location Path for OutPut File
      BEGIN
      SELECT fnd_profile.VALUE ('XXC_MOD_IN_068_AP_OUTBOUND'),
      fnd_profile.VALUE ('XXC_MOD_IN_068_AP_ARCHIVE')
      INTO l_location,
      l_archive_location
      FROM DUAL;
      EXCEPTION
      WHEN OTHERS
      THEN
      xxc_common_int_pk.insert_audit
      (p_batch_id => l_batch_id,
      p_request_id => l_request_id,
      p_source_system => l_source,
      p_proc_name => l_proc_name,
      p_log_type => l_log_type,
      p_det_status => l_det_status_rejected,
      p_msg_code => NULL,
      p_entity => NULL,
      p_msg_desc => 'Profile Value not found',
      p_mast_request_id => l_request_id,
      p_record_id => NULL,
      p_source => l_source,
      p_target => l_target,
      p_email => NULL,
      p_mod_code => l_mod_code,
      p_audit_master_id => l_audit_master_id
      );
      END;

      BEGIN
      SELECT TO_CHAR (SYSDATE, 'YYYYMMDDhh24miss')
      INTO l_date
      FROM DUAL;
      EXCEPTION
      WHEN OTHERS
      THEN
      xxc_common_int_pk.insert_audit (p_batch_id => l_batch_id,
      p_request_id => l_request_id,
      p_source_system => l_source,
      p_proc_name => l_proc_name,
      p_log_type => l_log_type,
      p_det_status => l_det_status_rejected,
      p_msg_code => NULL,
      p_entity => NULL,
      p_msg_desc => 'status not found',
      p_mast_request_id => l_request_id,
      p_record_id => NULL,
      p_source => l_source,
      p_target => l_target,
      p_email => NULL,
      p_mod_code => l_mod_code,
      p_audit_master_id => l_audit_master_id
      );
      END;

      l_filename := 'AP_Fiscal_' || l_date || '.csv';
      v_file :=
      UTL_FILE.fopen (LOCATION => l_location,
      filename => l_filename,
      open_mode => l_open_mode,
      max_linesize => l_max_linesize
      );
      -- Changed as per Sarah's email on 9th Decemeber
      /* UTL_FILE.put_line (v_file,
      'SUPPLIER_REF'
      || ','
      || 'SUPPLIER_NAME'
      || ','
      || 'INVOICE_NUMBER'
      || ','
      || 'INVOICE_DATE'
      || ','
      || 'AMOUNT'
      || ','
      || 'UNIQUE_ID'
      || ','
      || 'DATE_INVOICE_ENTERED'
      || ','
      || 'DATE_INVOICE_PAID'
      || ','
      || 'USER_ID'
      || ','
      || 'PAYMENT_STATUS_FLAG'
      || ','
      || 'DOC_TYPE'
      || ','
      || 'DESCRIPTION'
      || ','
      || 'PAYMENT_AMOUNT'
      );*/
      UTL_FILE.put_line (v_file,
      'SUPPLIERREF'
      || ','
      || 'SUPPLIERNAME'
      || ','
      || 'INVOICENUMBER'
      || ','
      || 'DATE'
      || ','
      || 'AMOUNT'
      || ','
      || 'UNIQUEID'
      || ','
      || 'DATEINVOICEENTERED'
      || ','
      || 'DATEINVOICEPAID'
      || ','
      || 'USERID'
      || ','
      || 'PAYMENTSTATUS'
      || ','
      || 'DOCTYPE'
      || ','
      || 'DESCRIPTION'
      || ','
      || 'PAYMENTAMOUNT');
      UTL_FILE.put_line (v_file,
      'XX'
      || ','
      || 'XX'
      || ','
      || 'XX'
      || ','
      || 'XX'
      || ','
      || 'XX'
      || ','
      || 'XX'
      || ','
      || 'XX'
      || ','
      || 'XX'
      || ','
      || 'XX'
      || ','
      || 'XX'
      || ','
      || 'XX'
      || ','
      || 'XX'
      || ','
      || 'XX');
                               
                               open get_ap_data_inv;
                               loop
                               fetch get_ap_data_inv bulk collect into xxc_tbl1 limit 6000;
      fnd_file.put_line(fnd_file.log, 'Cursor Count is : '||xxc_tbl1.count);
                               for i in xxc_tbl1.first .. xxc_tbl1.count

      --FOR cur_rec IN get_ap_data_inv
      LOOP
      BEGIN

      --Common package used for proper sequence for Record_id and Bath_id
      l_sucess_count := l_sucess_count + 1;
      --Insert into CSV file
      fnd_file.put_line (fnd_file.LOG, 'Before Utl file');
      UTL_FILE.put_line (v_file,
      xxc_tbl1(i).supplier_ref
      || ','
      || xxc_tbl1(i).supplier_name
      || ','
      || xxc_tbl1(i).invoice_number
      || ','
      || xxc_tbl1(i).invoice_date
      || ','
      || xxc_tbl1(i).amount
      || ','
      || xxc_tbl1(i).unique_id
      || ','
      || xxc_tbl1(i).date_invoice_entered
      || ','
      || xxc_tbl1(i).date_invoice_paid
      || ','
      || xxc_tbl1(i).user_id
      || ','
      || xxc_tbl1(i).payment_status_flag
      || ','
      || xxc_tbl1(i).doc_type
      || ','
      || xxc_tbl1(i).description
      || ','
      || xxc_tbl1(i).gross_amount);
      fnd_file.put_line (fnd_file.LOG,
      'Supplier Reference : ' || xxc_tbl1(i).supplier_ref);
      xxc_common_int_pk.insert_audit
      (p_batch_id => l_batch_id,
      p_request_id => l_request_id,
      p_source_system => l_source,
      p_proc_name => l_proc_name,
      p_log_type => l_log_type,
      p_det_status => l_det_status_complete,
      p_msg_code => NULL,
      p_entity => NULL,
      p_msg_desc => 'Inserting records from AP to Fiscal Successfully',
      p_mast_request_id => l_request_id,
      p_record_id => NULL,
      p_source => l_source,
      p_target => l_target,
      p_email => NULL,
      p_mod_code => l_mod_code,
      p_audit_master_id => l_audit_master_id
      );
      EXCEPTION
      WHEN OTHERS
      THEN
      l_error_count := l_error_count + 1;
      fnd_file.put_line (fnd_file.LOG,
      'Error While Inserting from AP to Fiscal '
      || SQLERRM);
      -- Create audit log for AP Inv records insert Exception
      --Insert into the Audit table XXC_COMM_AUDIT_DETAIL_LOG and XXC_COMM_AUDIT_MASTER_LOG
      xxc_common_int_pk.insert_audit
      (p_batch_id => l_batch_id,
      p_request_id => l_request_id,
      p_source_system => l_source,
      p_proc_name => l_proc_name,
      p_log_type => l_log_type,
      p_det_status => l_det_status_rejected,
      p_msg_code => NULL,
      p_entity => NULL,
      p_msg_desc => 'Error While Inserting from AP to Fiscal',
      p_mast_request_id => l_request_id,
      p_record_id => NULL,
      p_source => l_source,
      p_target => l_target,
      p_email => NULL,
      p_mod_code => l_mod_code,
      p_audit_master_id => l_audit_master_id
      );

      END;
           
      END LOOP;
      exit when get_ap_data_inv%NOTFOUND;
      end loop;
           close get_ap_data_inv;

      UTL_FILE.fclose (v_file);
      UTL_FILE.fcopy (l_location,
      l_filename,
      l_archive_location,
      l_filename
      );
      -- Create audit log for Successfully processed records
      -- Procedure call to insert in Audit tables
      xxc_common_int_pk.insert_audit
      (p_batch_id => l_batch_id,
      p_request_id => l_request_id,
      p_source_system => l_source,
      p_proc_name => l_proc_name,
      p_log_type => l_log_type,
      p_det_status => l_det_status_complete,
      p_msg_code => NULL,
      p_entity => NULL,
      p_msg_desc => 'Compeleted Sucessfully AP to Fiscal',
      p_mast_request_id => l_request_id,
      p_record_id => NULL,
      p_source => l_source,
      p_target => l_target,
      p_email => NULL,
      p_mod_code => l_mod_code,
      p_audit_master_id => l_audit_master_id
      );

      --Insert into the Audit table XXC_COMM_AUDIT_DETAIL_LOG and XXC_COMM_AUDIT_MASTER_LOG for populating email drop table
      BEGIN
      SELECT transaction_status
      INTO p_status
      FROM xxc_comm_audit_master_log
      WHERE audit_master_id = l_audit_master_id;
      EXCEPTION
      WHEN OTHERS
      THEN
      xxc_common_int_pk.insert_audit (p_batch_id => l_batch_id,
      p_request_id => l_request_id,
      p_source_system => l_source,
      p_proc_name => l_proc_name,
      p_log_type => l_log_type,
      p_det_status => l_det_status_err,
      p_msg_code => NULL,
      p_entity => NULL,
      p_msg_desc => 'Status Error',
      p_mast_request_id => l_request_id,
      p_record_id => NULL,
      p_source => l_source,
      p_target => l_target,
      p_email => NULL,
      p_mod_code => l_mod_code,
      p_audit_master_id => l_audit_master_id
      );
      END;

      IF p_status <> 0
      THEN
      xxc_comm_audit_log_pk.populate_email_drop_table (l_audit_master_id,
      l_batch_id);
      END IF;
      EXCEPTION
      WHEN UTL_FILE.invalid_path
      THEN
      UTL_FILE.fclose (v_file);
      raise_application_error (-20000, 'File location is invalid.');
      WHEN UTL_FILE.invalid_mode
      THEN
      UTL_FILE.fclose (v_file);
      raise_application_error (-20001,
      'The open_mode parameter in FOPEN is invalid.');
      WHEN UTL_FILE.invalid_filehandle
      THEN
      UTL_FILE.fclose (v_file);
      raise_application_error (-20002, 'File handle is invalid.');
      WHEN UTL_FILE.invalid_operation
      THEN
      UTL_FILE.fclose (v_file);
      raise_application_error
      (-20003,
      'File could not be opened or operated on as requested.');
      WHEN UTL_FILE.write_error
      THEN
      UTL_FILE.fclose (v_file);
      raise_application_error
      (-20005,
      'Operating system error occurred during the write operation.');
      WHEN UTL_FILE.file_open
      THEN
      UTL_FILE.fclose (v_file);
      raise_application_error
      (-20008,
      'The requested operation failed because the file is open.');
      WHEN UTL_FILE.invalid_maxlinesize
      THEN
      UTL_FILE.fclose (v_file);
      raise_application_error
      (-20009,
      'The MAX_LINESIZE value for FOPEN() is invalid; it should '
      || 'be within the range 1 to 32767.');
      COMMIT;
      ROLLBACK TO data_extract;
      WHEN OTHERS
      THEN
      raise_application_error (-20045, SQLERRM);
      UTL_FILE.fclose (v_file);
      END xxc_mod_068_ap_pr;
      END xxc_mod_in_068_ap_to_fis_pkg;

      /
      Show Errors

      Iam implemented BULK collect concept in programe,can anyone please suggest how can I imporve performance....

      Thanks,
      Rakesh
        • 1. Re: UTL_FILE reqeust taking too much time for completion
          JustinCave
          1) Please use the \
           tag before and after code you post to preserve the white space.  That makes the code much easier to read.
          
          2) Why do you believe that the problem is with the UTL_FILE calls?  Have you traced/ profiled/ debugged the code?  How do you know that the problem isn't that the query you're running takes 22 minutes to return 6000 rows because of a query plan that is less than optimal?
          
          Justin                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
          • 2. Re: UTL_FILE reqeust taking too much time for completion
            977941
            Hi,

            Thanks for reply...

            the select statment fetching the data in Toad as 3 sec.

            But when submitting the reqquest it will taking 22 min.

            Can you please suggest where I can wrong....please

            Thanks,
            Rakesh
            • 3. Re: UTL_FILE reqeust taking too much time for completion
              AlbertoFaenza
              974938 wrote:
              Hi,

              Thanks for reply...

              the select statment fetching the data in Toad as 3 sec.

              But when submitting the reqquest it will taking 22 min.

              Can you please suggest where I can wrong....please

              Thanks,
              Rakesh
              Edit your original message and put your code between two lines starting with {noformat}
              {noformat}
              
              i.e.:
              {noformat}
              {noformat}
              SELECT ...
              {noformat}
              {noformat}
              
              
              Also please read <a href="https://forums.oracle.com/forums/thread.jspa?threadID=2174552#9360002">How do I ask a question on the forums?</a>
              
              You need to post some sample data to be able reproduce your problem.
              
              Regards.
              Al                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
              • 4. Re: UTL_FILE reqeust taking too much time for completion
                977941
                  CREATE OR REPLACE PACKAGE BODY "XXC"."XXC_MOD_IN_068_AP_TO_FIS_PKG" 
                AS
                  PROCEDURE xxc_mod_068_ap_pr (
                    p_errbuf                         OUT      VARCHAR2,
                    p_retcode                        OUT      NUMBER,
                    p_start_date                     IN       VARCHAR2,
                    p_end_date                       IN       VARCHAR2
                  )
                  IS
                    -- Define variables and assign default values
                    l_sucess_count                          NUMBER := 0;
                    l_error_count                           NUMBER := 0;
                    -- Standard declaration
                    l_source                                VARCHAR2 (10);
                    l_target                                VARCHAR2 (10);
                    lc_module_description                   VARCHAR2 (50)
                                                                 := 'MOD_IN_068 - AP to Fiscal';
                    l_status                       CONSTANT VARCHAR2 (50) := 'NEW';
                    p_status                                NUMBER;
                    l_batch_id                              NUMBER;
                    l_batch_id_next                         NUMBER
                                                         := apps_common_out_batch_id_s1.NEXTVAL;
                    l_proc_name                             VARCHAR2 (100) := 'XXC_MOD_IN_068';
                    l_request_id                            NUMBER
                                                                  := fnd_global.conc_request_id;
                    l_audit_master_id                       NUMBER := NULL;
                    l_mod_code                              VARCHAR2 (100);
                    l_log_type                              NUMBER := 1;   --INFORMATION
                    l_det_status_success                    NUMBER := 0;   --SUCCESS
                    l_det_status_inprocess                  NUMBER := 3;   --INPROCESS
                    l_det_status_rejected                   NUMBER := 4;   --REJECTED
                    l_det_status_err                        NUMBER := 3;   --Error
                    l_det_status_complete                   NUMBER := 9;   --COMPLETE
                    -- Standard who Columns
                    l_created_by                            NUMBER := fnd_global.user_id;
                    l_creation_date                         DATE := SYSDATE;
                    l_last_update_date                      DATE := SYSDATE;
                    l_last_update_login                     NUMBER := fnd_global.user_id;
                    v_file                                  UTL_FILE.file_type;
                    l_location                              VARCHAR2 (150);
                    l_archive_location                      VARCHAR2 (150);
                    l_date                                  VARCHAR2 (50);
                    l_filename                              VARCHAR2 (50);
                    l_open_mode                             VARCHAR2 (1) := 'W';
                --- l_max_linesize                          NUMBER := 32767;
                    l_max_linesize                          VARCHAR2 (150); -- Updated 09-Nov-2012
                     
                
                
                    --Cursor is used to fetch valid records for the interface
                    CURSOR get_ap_data_inv
                    IS
                      SELECT       asp.segment1 supplier_ref,
                             -- asp.vendor_name supplier_name,
                             replace(asp.vendor_name, ',', ' ') supplier_name,
                             --aia.invoice_num invoice_number,
                             replace(aia.invoice_num, ',','') invoice_number,
                             aia.invoice_date,
                             aia.invoice_amount amount,
                             aia.doc_sequence_value unique_id,
                             aia.creation_date date_invoice_entered,
                             apsa.due_date date_invoice_paid,
                             aia.SOURCE user_id,
                             aia.payment_status_flag,
                             aia.invoice_type_lookup_code doc_type,
                             --aia.description,
                             replace(aia.description, ',' , ' ') description,
                             apsa.gross_amount
                      FROM   ap_invoices_all aia,
                             ap_suppliers asp,
                             apps.ap_payment_schedules_all apsa
                            -- apps.iby_payments_all iba,
                        --     apps.iby_docs_payable_all dp
                      WHERE  aia.invoice_id = apsa.invoice_id
                      AND    aia.vendor_id  = asp.vendor_id
                      AND    aia.org_id     = apsa.org_id
                  --  AND    apsa.payment_status_flag != 'Y'   -- commented for CR
                  --  AND    dp.payment_id = iba.payment_id(+)
                  --  AND    aia.invoice_id = dp.calling_app_doc_unique_ref2(+)
                  --  AND    apsa.due_date <= (SYSDATE + 1)
                      AND    TRUNC (aia.creation_date)
                               BETWEEN NVL (fnd_date.canonical_to_date (p_start_date),
                                            TRUNC (aia.creation_date))
                                   AND NVL (fnd_date.canonical_to_date (p_end_date),
                                            TRUNC (aia.creation_date));
                                                 TYPE xxc_tbl IS TABLE OF get_ap_data_inv%ROWTYPE;
                                              xxc_tbl1 xxc_tbl;
                                             
                  BEGIN
                    l_batch_id                      := apps_common_out_batch_id_s1.CURRVAL;
                    xxc_common_int_pk.insert_audit (p_batch_id                        => l_batch_id,
                                                    p_request_id                      => l_request_id,
                                                    p_source_system                   => l_source,
                                                    p_proc_name                       => l_proc_name,
                                                    p_log_type                        => l_log_type,
                                                    p_det_status                      => l_det_status_inprocess,
                                                    p_msg_code                        => NULL,
                                                    p_entity                          => NULL,
                                                    p_msg_desc                        => 'Process Starts',
                                                    p_mast_request_id                 => l_request_id,
                                                    p_record_id                       => NULL,
                                                    p_source                          => l_source,
                                                    p_target                          => l_target,
                                                    p_email                           => NULL,
                                                    p_mod_code                        => l_mod_code,
                                                    p_audit_master_id                 => l_audit_master_id
                                                   );
                
                -- Get Module Code
                    BEGIN
                      SELECT TRIM (fval.flex_value),
                             TRIM (SUBSTR (fval.description,
                                           1,
                                           INSTR (fval.description, ' -')
                                          ))
                      INTO   l_source,
                             l_mod_code
                      FROM   fnd_flex_values_vl fval, fnd_flex_value_sets vset
                      WHERE  vset.flex_value_set_id = fval.flex_value_set_id
                      AND    vset.flex_value_set_name IN ('XXC_COMM_INT_CONFIG')
                      AND    fval.enabled_flag = 'Y'
                      AND    fval.description = lc_module_description;
                    EXCEPTION
                      WHEN OTHERS
                      THEN
                        xxc_common_int_pk.insert_audit (p_batch_id                        => l_batch_id,
                                                        p_request_id                      => l_request_id,
                                                        p_source_system                   => l_source,
                                                        p_proc_name                       => l_proc_name,
                                                        p_log_type                        => l_log_type,
                                                        p_det_status                      => l_det_status_err,
                                                        p_msg_code                        => NULL,
                                                        p_entity                          => NULL,
                                                        p_msg_desc                        => 'Error Mode Code',
                                                        p_mast_request_id                 => l_request_id,
                                                        p_record_id                       => NULL,
                                                        p_source                          => l_source,
                                                        p_target                          => l_target,
                                                        p_email                           => NULL,
                                                        p_mod_code                        => l_mod_code,
                                                        p_audit_master_id                 => l_audit_master_id
                                                       );
                        raise_application_error (-20045, SQLERRM);
                    END;
                
                --File Location Path for OutPut File
                    BEGIN
                      SELECT fnd_profile.VALUE ('XXC_MOD_IN_068_AP_OUTBOUND'),
                             fnd_profile.VALUE ('XXC_MOD_IN_068_AP_ARCHIVE')
                      INTO   l_location,
                             l_archive_location
                      FROM   DUAL;
                    EXCEPTION
                      WHEN OTHERS
                      THEN
                        xxc_common_int_pk.insert_audit
                                                      (p_batch_id                        => l_batch_id,
                                                       p_request_id                      => l_request_id,
                                                       p_source_system                   => l_source,
                                                       p_proc_name                       => l_proc_name,
                                                       p_log_type                        => l_log_type,
                                                       p_det_status                      => l_det_status_rejected,
                                                       p_msg_code                        => NULL,
                                                       p_entity                          => NULL,
                                                       p_msg_desc                        => 'Profile Value not found',
                                                       p_mast_request_id                 => l_request_id,
                                                       p_record_id                       => NULL,
                                                       p_source                          => l_source,
                                                       p_target                          => l_target,
                                                       p_email                           => NULL,
                                                       p_mod_code                        => l_mod_code,
                                                       p_audit_master_id                 => l_audit_master_id
                                                      );
                    END;
                
                    BEGIN
                      SELECT TO_CHAR (SYSDATE, 'YYYYMMDDhh24miss')
                      INTO   l_date
                      FROM   DUAL;
                    EXCEPTION
                      WHEN OTHERS
                      THEN
                        xxc_common_int_pk.insert_audit (p_batch_id                        => l_batch_id,
                                                        p_request_id                      => l_request_id,
                                                        p_source_system                   => l_source,
                                                        p_proc_name                       => l_proc_name,
                                                        p_log_type                        => l_log_type,
                                                        p_det_status                      => l_det_status_rejected,
                                                        p_msg_code                        => NULL,
                                                        p_entity                          => NULL,
                                                        p_msg_desc                        => 'status not found',
                                                        p_mast_request_id                 => l_request_id,
                                                        p_record_id                       => NULL,
                                                        p_source                          => l_source,
                                                        p_target                          => l_target,
                                                        p_email                           => NULL,
                                                        p_mod_code                        => l_mod_code,
                                                        p_audit_master_id                 => l_audit_master_id
                                                       );
                    END;
                
                    l_filename                      := 'AP_Fiscal_' || l_date || '.csv';
                    v_file                          :=
                      UTL_FILE.fopen (LOCATION                          => l_location,
                                      filename                          => l_filename,
                                      open_mode                         => l_open_mode,
                                      max_linesize                      => l_max_linesize
                                     );
                                       -- Changed as per Sarah's email on 9th Decemeber
                    /* UTL_FILE.put_line (v_file,
                                           'SUPPLIER_REF'
                                        || ','
                                        || 'SUPPLIER_NAME'
                                        || ','
                                        || 'INVOICE_NUMBER'
                                        || ','
                                        || 'INVOICE_DATE'
                                        || ','
                                        || 'AMOUNT'
                                        || ','
                                        || 'UNIQUE_ID'
                                        || ','
                                        || 'DATE_INVOICE_ENTERED'
                                        || ','
                                        || 'DATE_INVOICE_PAID'
                                        || ','
                                        || 'USER_ID'
                                        || ','
                                        || 'PAYMENT_STATUS_FLAG'
                                        || ','
                                        || 'DOC_TYPE'
                                        || ','
                                        || 'DESCRIPTION'
                                        || ','
                                        || 'PAYMENT_AMOUNT'
                                       );*/
                    UTL_FILE.put_line (v_file,
                                          'SUPPLIERREF'
                                       || ','
                                       || 'SUPPLIERNAME'
                                       || ','
                                       || 'INVOICENUMBER'
                                       || ','
                                       || 'DATE'
                                       || ','
                                       || 'AMOUNT'
                                       || ','
                                       || 'UNIQUEID'
                                       || ','
                                       || 'DATEINVOICEENTERED'
                                       || ','
                                       || 'DATEINVOICEPAID'
                                       || ','
                                       || 'USERID'
                                       || ','
                                       || 'PAYMENTSTATUS'
                                       || ','
                                       || 'DOCTYPE'
                                       || ','
                                       || 'DESCRIPTION'
                                       || ','
                                       || 'PAYMENTAMOUNT');
                    UTL_FILE.put_line (v_file,
                                          'XX'
                                       || ','
                                       || 'XX'
                                       || ','
                                       || 'XX'
                                       || ','
                                       || 'XX'
                                       || ','
                                       || 'XX'
                                       || ','
                                       || 'XX'
                                       || ','
                                       || 'XX'
                                       || ','
                                       || 'XX'
                                       || ','
                                       || 'XX'
                                       || ','
                                       || 'XX'
                                       || ','
                                       || 'XX'
                                       || ','
                                       || 'XX'
                                       || ','
                                       || 'XX');
                                            
                                            open get_ap_data_inv;
                                            loop
                                            fetch get_ap_data_inv bulk collect into xxc_tbl1 limit 6000;
                             fnd_file.put_line(fnd_file.log, 'Cursor Count is : '||xxc_tbl1.count);
                                            for i in xxc_tbl1.first .. xxc_tbl1.count
                
                    --FOR cur_rec IN get_ap_data_inv
                    LOOP
                      BEGIN
                      
                        --Common package used for proper sequence for Record_id and Bath_id
                        l_sucess_count                  := l_sucess_count + 1;
                        --Insert into CSV file
                        fnd_file.put_line (fnd_file.LOG, 'Before Utl file');
                        UTL_FILE.put_line (v_file,
                                              xxc_tbl1(i).supplier_ref
                                           || ','
                                           || xxc_tbl1(i).supplier_name
                                           || ','
                                           || xxc_tbl1(i).invoice_number
                                           || ','
                                           || xxc_tbl1(i).invoice_date
                                           || ','
                                           || xxc_tbl1(i).amount
                                           || ','
                                           || xxc_tbl1(i).unique_id
                                           || ','
                                           ||  xxc_tbl1(i).date_invoice_entered
                                           || ','
                                           ||  xxc_tbl1(i).date_invoice_paid
                                           || ','
                                           ||  xxc_tbl1(i).user_id
                                           || ','
                                           ||  xxc_tbl1(i).payment_status_flag
                                           || ','
                                           ||  xxc_tbl1(i).doc_type
                                           || ','
                                           ||  xxc_tbl1(i).description
                                           || ','
                                           ||  xxc_tbl1(i).gross_amount);
                        fnd_file.put_line (fnd_file.LOG,
                                           'Supplier Reference : ' ||  xxc_tbl1(i).supplier_ref);
                        xxc_common_int_pk.insert_audit
                              (p_batch_id                        => l_batch_id,
                               p_request_id                      => l_request_id,
                               p_source_system                   => l_source,
                               p_proc_name                       => l_proc_name,
                               p_log_type                        => l_log_type,
                               p_det_status                      => l_det_status_complete,
                               p_msg_code                        => NULL,
                               p_entity                          => NULL,
                               p_msg_desc                        => 'Inserting records from AP to Fiscal Successfully',
                               p_mast_request_id                 => l_request_id,
                               p_record_id                       => NULL,
                               p_source                          => l_source,
                               p_target                          => l_target,
                               p_email                           => NULL,
                               p_mod_code                        => l_mod_code,
                               p_audit_master_id                 => l_audit_master_id
                              );
                      EXCEPTION
                        WHEN OTHERS
                        THEN
                          l_error_count                   := l_error_count + 1;
                          fnd_file.put_line (fnd_file.LOG,
                                                'Error While Inserting from AP to Fiscal '
                                             || SQLERRM);
                             -- Create audit log for AP Inv records insert Exception
                          --Insert into the Audit table XXC_COMM_AUDIT_DETAIL_LOG and XXC_COMM_AUDIT_MASTER_LOG
                          xxc_common_int_pk.insert_audit
                                       (p_batch_id                        => l_batch_id,
                                        p_request_id                      => l_request_id,
                                        p_source_system                   => l_source,
                                        p_proc_name                       => l_proc_name,
                                        p_log_type                        => l_log_type,
                                        p_det_status                      => l_det_status_rejected,
                                        p_msg_code                        => NULL,
                                        p_entity                          => NULL,
                                        p_msg_desc                        => 'Error While Inserting from AP to Fiscal',
                                        p_mast_request_id                 => l_request_id,
                                        p_record_id                       => NULL,
                                        p_source                          => l_source,
                                        p_target                          => l_target,
                                        p_email                           => NULL,
                                        p_mod_code                        => l_mod_code,
                                        p_audit_master_id                 => l_audit_master_id
                                       );
                                    
                      END;
                       
                    END LOOP;
                     exit when get_ap_data_inv%NOTFOUND;
                    end loop;
                     close get_ap_data_inv;
                
                    UTL_FILE.fclose (v_file);
                    UTL_FILE.fcopy (l_location,
                                    l_filename,
                                    l_archive_location,
                                    l_filename
                                   );
                      -- Create audit log for Successfully processed records
                    -- Procedure call to insert in Audit tables
                    xxc_common_int_pk.insert_audit
                                           (p_batch_id                        => l_batch_id,
                                            p_request_id                      => l_request_id,
                                            p_source_system                   => l_source,
                                            p_proc_name                       => l_proc_name,
                                            p_log_type                        => l_log_type,
                                            p_det_status                      => l_det_status_complete,
                                            p_msg_code                        => NULL,
                                            p_entity                          => NULL,
                                            p_msg_desc                        => 'Compeleted Sucessfully AP to Fiscal',
                                            p_mast_request_id                 => l_request_id,
                                            p_record_id                       => NULL,
                                            p_source                          => l_source,
                                            p_target                          => l_target,
                                            p_email                           => NULL,
                                            p_mod_code                        => l_mod_code,
                                            p_audit_master_id                 => l_audit_master_id
                                           );
                
                    --Insert into the Audit table XXC_COMM_AUDIT_DETAIL_LOG and XXC_COMM_AUDIT_MASTER_LOG for populating email drop table
                    BEGIN
                      SELECT transaction_status
                      INTO   p_status
                      FROM   xxc_comm_audit_master_log
                      WHERE  audit_master_id = l_audit_master_id;
                    EXCEPTION
                      WHEN OTHERS
                      THEN
                        xxc_common_int_pk.insert_audit (p_batch_id                        => l_batch_id,
                                                        p_request_id                      => l_request_id,
                                                        p_source_system                   => l_source,
                                                        p_proc_name                       => l_proc_name,
                                                        p_log_type                        => l_log_type,
                                                        p_det_status                      => l_det_status_err,
                                                        p_msg_code                        => NULL,
                                                        p_entity                          => NULL,
                                                        p_msg_desc                        => 'Status Error',
                                                        p_mast_request_id                 => l_request_id,
                                                        p_record_id                       => NULL,
                                                        p_source                          => l_source,
                                                        p_target                          => l_target,
                                                        p_email                           => NULL,
                                                        p_mod_code                        => l_mod_code,
                                                        p_audit_master_id                 => l_audit_master_id
                                                       );
                    END;
                
                    IF p_status <> 0
                    THEN
                      xxc_comm_audit_log_pk.populate_email_drop_table (l_audit_master_id,
                                                                       l_batch_id);
                    END IF;
                  EXCEPTION
                    WHEN UTL_FILE.invalid_path
                    THEN
                      UTL_FILE.fclose (v_file);
                      raise_application_error (-20000, 'File location is invalid.');
                    WHEN UTL_FILE.invalid_mode
                    THEN
                      UTL_FILE.fclose (v_file);
                      raise_application_error (-20001,
                                               'The open_mode parameter in FOPEN is invalid.');
                    WHEN UTL_FILE.invalid_filehandle
                    THEN
                      UTL_FILE.fclose (v_file);
                      raise_application_error (-20002, 'File handle is invalid.');
                    WHEN UTL_FILE.invalid_operation
                    THEN
                      UTL_FILE.fclose (v_file);
                      raise_application_error
                                       (-20003,
                                        'File could not be opened or operated on as requested.');
                    WHEN UTL_FILE.write_error
                    THEN
                      UTL_FILE.fclose (v_file);
                      raise_application_error
                                 (-20005,
                                  'Operating system error occurred during the write operation.');
                    WHEN UTL_FILE.file_open
                    THEN
                      UTL_FILE.fclose (v_file);
                      raise_application_error
                                    (-20008,
                                     'The requested operation failed because the file is open.');
                    WHEN UTL_FILE.invalid_maxlinesize
                    THEN
                      UTL_FILE.fclose (v_file);
                      raise_application_error
                                (-20009,
                                    'The MAX_LINESIZE value for FOPEN() is invalid; it should '
                                 || 'be within the range 1 to 32767.');
                      COMMIT;
                      ROLLBACK TO data_extract;
                    WHEN OTHERS
                    THEN
                      raise_application_error (-20045, SQLERRM);
                      UTL_FILE.fclose (v_file);
                  END xxc_mod_068_ap_pr;
                END xxc_mod_in_068_ap_to_fis_pkg;
                Please verify...
                • 5. Re: UTL_FILE reqeust taking too much time for completion
                  BluShadow
                  974938 wrote:
                  Hi,

                  Thanks for reply...

                  the select statment fetching the data in Toad as 3 sec.
                  Are you sure that was all 6000 rows?
                  TOAD only fetches the first 500 rows and displays those, and then fetches the next 500 when you scroll down the data grid etc. and it does that so you can get data back quicker than if it were to fetch all the data back.
                  How long does it take in TOAD if you scroll down to the end of all 6000 rows of data?
                  But when submitting the reqquest it will taking 22 min.

                  Can you please suggest where I can wrong....please
                  The UTL_FILE package is a low level file access which can read and write files on the operating system. It's about as fast as fast can be, so your idea that it is UTL_FILE that is causing the issue is undoubtably wrong. The delay is almost certainly elsewhere in your code, and most likely in the query that is fetching the data.

                  Try running your query along through SQL*Plus and see how long it takes for it to fetch back and show all the 6000 rows.
                  • 6. Re: UTL_FILE reqeust taking too much time for completion
                    977941
                    Hardly sqlstatement takking 10 sec to fetch data for 6000 rows and the explain plan as below:


                    SQL> SELECT PLAN_TABLE_OUTPUT
                    FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, 'ex_plan2','BASIC'));
                    2
                    PLAN_TABLE_OUTPUT
                    --------------------------------------------------------------------------------
                    --------------------------------------------------------
                    | Id | Operation | Name |
                    --------------------------------------------------------
                    | 0 | SELECT STATEMENT | |
                    | 1 | HASH JOIN | |
                    | 2 | HASH JOIN | |
                    | 3 | TABLE ACCESS FULL| AP_INVOICES_ALL |
                    | 4 | TABLE ACCESS FULL| AP_SUPPLIERS |
                    | 5 | TABLE ACCESS FULL | AP_PAYMENT_SCHEDULES_ALL |
                    --------------------------------------------------------
                    11 rows selected.

                    please suggest..
                    • 7. Re: UTL_FILE reqeust taking too much time for completion
                      AlbertoFaenza
                      Hi,

                      your code is too long and we don't have your input data.

                      To be sure that is UTL_FILE the cause you should be able to trace your code and see exactly that UTL_FILE is causing the problem.

                      In your code I don't see only UTL_FILE calls but other statements as well.

                      Consider that the following code:
                      SQL> set timing ON
                      SQL> DECLARE
                        2     v_fl      UTL_FILE.FILE_TYPE;
                        3  BEGIN
                        4     v_fl := UTL_FILE.FOPEN('DATA_TMP','test.txt','w');
                        5     FOR c1 IN (SELECT * FROM all_objects)
                        6     LOOP
                        7        UTL_FILE.PUT_LINE(v_fl,
                        8                          c1.object_name
                        9                          ||c1.subobject_name
                       10                          ||c1.object_type
                       11                          ||c1.timestamp
                       12                          ||c1.status
                       13                          ||c1.temporary
                       14                          ||c1.generated
                       15                          ||c1.secondary
                       16                          ||c1.edition_name);
                       17     END LOOP;
                       18
                       19     UTL_FILE.FCLOSE(v_fl);
                       20  END;
                       21  /
                      
                      PL/SQL procedure successfully completed.
                      
                      Elapsed: 00:00:07.81
                      SQL> set timing off
                      SQL>
                      on my simple laptop writes 71000 rows in about 7 secs.

                      Regards.
                      Al
                      • 8. Re: UTL_FILE reqeust taking too much time for completion
                        977941
                        SQL> SELECT PLAN_TABLE_OUTPUT
                        FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, 'ex_plan2','BASIC'));
                        2
                        PLAN_TABLE_OUTPUT
                        --------------------------------------------------------------------------------
                        --------------------------------------------------------
                        | Id | Operation | Name |
                        --------------------------------------------------------
                        | 0 | SELECT STATEMENT | |
                        | 1 | HASH JOIN | |
                        | 2 | HASH JOIN | |
                        | 3 | TABLE ACCESS FULL| AP_INVOICES_ALL |
                        | 4 | TABLE ACCESS FULL| AP_SUPPLIERS |
                        | 5 | TABLE ACCESS FULL | AP_PAYMENT_SCHEDULES_ALL |
                        --------------------------------------------------------
                        11 rows selected.
                        • 9. Re: UTL_FILE reqeust taking too much time for completion
                          AlbertoFaenza
                          Hi,

                          If you believe that is UTL_FILE and you don't want to trace your proble, make a test commenting out the UTL_FILE.put line in this way:
                          /*
                                  UTL_FILE.put_line (v_file,
                                                        xxc_tbl1(i).supplier_ref
                                                     || ','
                                                     || xxc_tbl1(i).supplier_name
                                                     || ','
                                                     || xxc_tbl1(i).invoice_number
                                                     || ','
                                                     || xxc_tbl1(i).invoice_date
                                                     || ','
                                                     || xxc_tbl1(i).amount
                                                     || ','
                                                     || xxc_tbl1(i).unique_id
                                                     || ','
                                                     ||  xxc_tbl1(i).date_invoice_entered
                                                     || ','
                                                     ||  xxc_tbl1(i).date_invoice_paid
                                                     || ','
                                                     ||  xxc_tbl1(i).user_id
                                                     || ','
                                                     ||  xxc_tbl1(i).payment_status_flag
                                                     || ','
                                                     ||  xxc_tbl1(i).doc_type
                                                     || ','
                                                     ||  xxc_tbl1(i).description
                                                     || ','
                                                     ||  xxc_tbl1(i).gross_amount);
                          */
                          This will definitely show to you that your procedure is slow even without using UTL_FILE.

                          Regards.
                          Al