Performance Issue In Credit Exposure API Call

(Doc ID 2355466.1)

Last updated on JANUARY 31, 2018

Applies to:

Oracle Order Management - Version 12.1.3 and later
Information in this document applies to any platform.

Symptoms

There is a performance issue with Credit exposure API OE_CREDIT_EXPOSURE_PUB.GET_CUSTOMER_EXPOSURE & OE_CREDIT_CHECK_UTIL.GET_ORDER_EXPOSURE
and due to this the custom report is taking an expensive amount of time.

tkprof output shows the most expensive query:

SELECT SUM(nvl(amount_due_remaining,0) )
FROM (
  SELECT nvl(ps.amount_due_remaining,0) amount_due_remaining,
         ps.payment_schedule_id
  FROM ar_payment_schedules_all ps,
       hz_cust_site_uses_all su,
       hz_cust_acct_sites_all cas
  WHERE ps.customer_site_use_id = su.site_use_id
        AND   ps.status = 'OP'
        AND   cas.cust_acct_site_id = su.cust_acct_site_id
        AND   cas.cust_account_id =:b4
        AND   su.site_use_code = DECODE(:b3,0,site_use_code,'BILL_TO')
        AND   ps.invoice_currency_code =:b2
        AND   (ps.receipt_confirmed_flag IS NULL
    OR    ps.receipt_confirmed_flag = 'Y')
        AND   ps.gl_date_closed = TO_DATE('31-12-4712', 'DD-MM-YYYY')
        AND   SYSDATE - ps.trx_date > nvl(:b1,SYSDATE - ps.trx_date - 1)
  UNION
  SELECT nvl(ps.amount_due_remaining,0) amount_due_remaining, ps.payment_schedule_id
  FROM ar_payment_schedules_all ps
  WHERE ps.status = 'OP'
        AND   ps.customer_id =:b4
        AND   ps.customer_site_use_id IS NULL
        AND   ps.invoice_currency_code =:b2
        AND   (ps.receipt_confirmed_flag IS NULL
        OR    ps.receipt_confirmed_flag = 'Y')
        AND   ps.gl_date_closed = TO_DATE('31-12-4712', 'DD-MM-YYYY')
        AND   SYSDATE - ps.trx_date > nvl(:b1,SYSDATE - ps.trx_date - 1));


Elapsed time: 4308.62

Changes

 

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms