Friday, September 14, 2012

Poor Performance of Unaccounted Transactions Report (XML)


At my site Oracle Accounts Payable Report "Unaccounted Transactions Report (XML)" was not performing well. Upon investigation we found that when users run this report, they neither provide period name parameter nor From and To Accounting Dates parameters. This report uses a package "AP_PERIOD_CLOSE_PKG" to set global variable for XML publisher report. This package checks if Period name is passed then it sets From and To account dates based on Payable period. If period name is not passed and either from accounting date or To accounting date is not passed then it sets From and To accounting dates to start date of first payable period and end of last payable period that has status of C or O or F (i.e. CLOSED, OPEN or FUTURE).

So every time users ran this report, report queries were looking for all the data. In production this report was taking multiple hours (more than 3 hours when i checked). Since there will not be any unaccounted transaction in CLOSED Payable period, so this pkg "AP_PERIOD_CLOSE_PKG" should not use CLOSED period. Since it was a simple fix and we had already customized this report, so before calling Oracle pkg "AP_PERIOD_CLOSE_PKG.BEFORE_REPORT_APXUATR", we set From and To Accounting Dates based on OPEN and FUTURE periods only. This brought down execution time from more than 3 hours to 15 seconds. i guess it will never take more than 5 minutes. we hope there would not be too many unaccounted transactions in production.

2 comments:

  1. Hi Sanjai,

    Thanks for the post.
    We are facing the same issue. could you please send the sample code to set accounting dates which you have used.

    Thanks,

    ReplyDelete
    Replies
    1. In order to fix performance issue at my site, I modified following procedure

      AP_PERIOD_CLOSE_PKG.VALIDATE_PARAMETERS

      Search for following SQL statement in the above procedure

      SELECT min(start_date), max(end_date)
      INTO l_min_date,l_max_date
      FROM gl_period_statuses
      WHERE application_id = G_AP_APPLICATION_ID
      AND set_of_books_id = g_ledger_id
      AND closing_status in ('C','O','F');

      and remove 'C' from last line so that query gets min and max dates based on open and future periods only.

      Delete