Generate Balance Forward Billing Performance Issue (Doc ID 2076625.1)

Last updated on FEBRUARY 14, 2017

Applies to:

Oracle Receivables - Version 12.1.3 and later
Information in this document applies to any platform.
ARBFB_GEN: Generate Balance Forward Bills

Symptoms

Performance issue when running Generate Balance Forward Bills ,its very slow
It takes more than a day when run for all customers.
It finishes fast when run for a single customer.

Following sql was giving performance issue (as seen from trace/ AWR)
 
 INSERT INTO AR_CONS_INV_TRX (CONS_INV_ID, TRANSACTION_TYPE, TRX_NUMBER, 
  TRANSACTION_DATE, AMOUNT_ORIGINAL, TAX_ORIGINAL, ADJ_PS_ID, 
  CONS_INV_LINE_NUMBER, ORG_ID) SELECT /*+ LEADING(C,CTRX,PS_CASH) */ :B4 , 
  'RECEIPT ADJUST', PS_CASH.TRX_NUMBER, RA.APPLY_DATE, NVL 
  (RA.AMOUNT_APPLIED_FROM, RA.AMOUNT_APPLIED), NULL, 
  RA.RECEIVABLE_APPLICATION_ID, NULL, PS_CASH.ORG_ID FROM AR_CONS_INV C, 
  AR_CONS_INV_TRX CTRX, AR_PAYMENT_SCHEDULES PS_CASH, 
  AR_RECEIVABLE_APPLICATIONS RA, RA_CUSTOMER_TRX INV_TRX, 
  AR_PAYMENT_SCHEDULES PS_APP WHERE C.SITE_USE_ID = :B3 AND C.STATUS <> 
  'REJECTED' AND C.CURRENCY_CODE = :B2 AND C.CONS_INV_ID = CTRX.CONS_INV_ID 
  AND CTRX.TRANSACTION_TYPE = 'RECEIPT' AND C.SITE_USE_ID = 
  PS_CASH.CUSTOMER_SITE_USE_ID AND PS_CASH.INVOICE_CURRENCY_CODE = 
  C.CURRENCY_CODE AND CTRX.ADJ_PS_ID = PS_CASH.PAYMENT_SCHEDULE_ID AND 
  NVL(PS_CASH.EXCLUDE_FROM_CONS_BILL_FLAG, 'N') <> 'Y' AND RA.CONS_INV_ID IS 
  NULL AND RA.STATUS = 'APP' AND RA.APPLICATION_TYPE = 'CASH' AND 
  RA.APPLY_DATE <= :B1 AND PS_CASH.PAYMENT_SCHEDULE_ID = 
  RA.PAYMENT_SCHEDULE_ID AND INV_TRX.CUSTOMER_TRX_ID = 
  RA.APPLIED_CUSTOMER_TRX_ID AND RA.APPLIED_PAYMENT_SCHEDULE_ID = 
  PS_APP.PAYMENT_SCHEDULE_ID AND NOT EXISTS (SELECT '*' FROM AR_CONS_INV C, 
  AR_CONS_INV_TRX CTRX WHERE INV_TRX.CUSTOMER_TRX_ID = CTRX.CUSTOMER_TRX_ID 
  AND C.CONS_INV_ID = CTRX.CONS_INV_ID AND C.STATUS <> 'REJECTED' UNION ALL 
  SELECT '*' FROM AR_CONS_INV C, AR_CONS_INV_TRX CTRX WHERE C.CONS_INV_ID = 
  PS_APP.CONS_INV_ID AND C.STATUS <> 'REJECTED' AND CTRX.CONS_INV_ID = 
  C.CONS_INV_ID AND CTRX.CUSTOMER_TRX_ID IS NULL AND CTRX.ADJ_PS_ID = 
  PS_APP.PAYMENT_SCHEDULE_ID ) UNION SELECT /*+ LEADING(C,CTRX,PS_CASH) */ 
  :B4 , 'RECEIPT ADJUST', PS_CASH.TRX_NUMBER, RA.APPLY_DATE, NVL 
  (RA.AMOUNT_APPLIED_FROM, RA.AMOUNT_APPLIED), NULL, 
  RA.RECEIVABLE_APPLICATION_ID, NULL, PS_CASH.ORG_ID FROM AR_CONS_INV C, 
  AR_CONS_INV_TRX CTRX, AR_PAYMENT_SCHEDULES PS_CASH, 
  AR_RECEIVABLE_APPLICATIONS RA WHERE C.SITE_USE_ID = :B3 AND C.CURRENCY_CODE 
  = :B2 AND C.CONS_INV_ID = CTRX.CONS_INV_ID AND CTRX.TRANSACTION_TYPE = 
  'RECEIPT' AND C.STATUS <> 'REJECTED' AND CTRX.ADJ_PS_ID = 
  PS_CASH.PAYMENT_SCHEDULE_ID AND C.SITE_USE_ID = 
  PS_CASH.CUSTOMER_SITE_USE_ID AND PS_CASH.INVOICE_CURRENCY_CODE = 
  C.CURRENCY_CODE AND RA.CONS_INV_ID IS NULL AND 
  RA.APPLIED_PAYMENT_SCHEDULE_ID = -3 AND RA.APPLICATION_TYPE = 'CASH' AND 
  RA.APPLY_DATE <= :B1 AND PS_CASH.PAYMENT_SCHEDULE_ID = 
  RA.PAYMENT_SCHEDULE_ID AND NVL(PS_CASH.EXCLUDE_FROM_CONS_BILL_FLAG, 'N') <> 'Y'

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