My Oracle Support Banner

Generate Balance Forward Billing Performance Issue (Doc ID 2076625.1)

Last updated on FEBRUARY 09, 2018

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'

Changes

 

Cause

To view full details, sign in with your My Oracle Support account.

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


In this Document
Symptoms
Changes
Cause
Solution
 1. Bug Summary
 2. Fixed Files
 3. Recommended Patches
 4. Solution Steps
 Still Have Questions?
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.