Generate Balance Forward Billing Process Performance issue

Last updated on FEBRUARY 08, 2018

Applies to:

Symptoms

ACTUAL BEHAVIOR
---------------

The Generate Balance forward billing process takes around 40 hours to complete. There are  two problematic sql's  running more executions
 

SELECT SUM(ADR), SUM(BUCKET0), SUM(BUCKET1), SUM(BUCKET2), SUM(BUCKET3),
SUM(BUCKET4), SUM(BUCKET5),SUM(BUCKET6)
FROM
( SELECT SUM( GL_CURRENCY_API.CONVERT_AMOUNT( PS.INVOICE_CURRENCY_CODE, :B2 ,
SYSDATE, :B5 , PS.AMOUNT_DUE_REMAINING)) ADR,
SUM(ARPT_SQL_FUNC_UTIL.BUCKET_FUNCTION(:B27 , PS.AMOUNT_IN_DISPUTE,
PS.AMOUNT_ADJUSTED_PENDING,:B26 , :B25 ,PS.DUE_DATE,:B6 ,:B4 ) *
GL_CURRENCY_API.CONVERT_AMOUNT( PS.INVOICE_CURRENCY_CODE, :B2 , SYSDATE,
:B5 , PS.AMOUNT_DUE_REMAINING)) BUCKET0 ,
SUM(ARPT_SQL_FUNC_UTIL.BUCKET_FUNCTION(:B24 , PS.AMOUNT_IN_DISPUTE,
PS.AMOUNT_ADJUSTED_PENDING,:B23 , :B22 ,PS.DUE_DATE,:B6 ,:B4 ) *
GL_CURRENCY_API.CONVERT_AMOUNT( PS.INVOICE_CURRENCY_CODE, :B2 , SYSDATE,
:B5 , PS.AMOUNT_DUE_REMAINING)) BUCKET1 ,
SUM(ARPT_SQL_FUNC_UTIL.BUCKET_FUNCTION(:B21 , PS.AMOUNT_IN_DISPUTE,
PS.AMOUNT_ADJUSTED_PENDING,:B20 , :B19 ,PS.DUE_DATE,:B6 ,:B4 ) *
GL_CURRENCY_API.CONVERT_AMOUNT( PS.INVOICE_CURRENCY_CODE, :B2 , SYSDATE,
:B5 , PS.AMOUNT_DUE_REMAINING)) BUCKET2 ,
SUM(ARPT_SQL_FUNC_UTIL.BUCKET_FUNCTION(:B18 , PS.AMOUNT_IN_DISPUTE,
PS.AMOUNT_ADJUSTED_PENDING,:B17 , :B16 ,PS.DUE_DATE,:B6 ,:B4 ) *
GL_CURRENCY_API.CONVERT_AMOUNT( PS.INVOICE_CURRENCY_CODE, :B2 , SYSDATE,
:B5 , PS.AMOUNT_DUE_REMAINING)) BUCKET3 ,
SUM(ARPT_SQL_FUNC_UTIL.BUCKET_FUNCTION(:B15 , PS.AMOUNT_IN_DISPUTE,
PS.AMOUNT_ADJUSTED_PENDING,:B14 , :B13 ,PS.DUE_DATE,:B6 ,:B4 ) *
GL_CURRENCY_API.CONVERT_AMOUNT( PS.INVOICE_CURRENCY_CODE, :B2 , SYSDATE,
:B5 , PS.AMOUNT_DUE_REMAINING)) BUCKET4 ,
SUM(ARPT_SQL_FUNC_UTIL.BUCKET_FUNCTION(:B12 , PS.AMOUNT_IN_DISPUTE,
PS.AMOUNT_ADJUSTED_PENDING,:B11 , :B10 ,PS.DUE_DATE,:B6 ,:B4 ) *
GL_CURRENCY_API.CONVERT_AMOUNT( PS.INVOICE_CURRENCY_CODE, :B2 , SYSDATE,
:B5 , PS.AMOUNT_DUE_REMAINING)) BUCKET5 ,
SUM(ARPT_SQL_FUNC_UTIL.BUCKET_FUNCTION(:B9 , PS.AMOUNT_IN_DISPUTE,
PS.AMOUNT_ADJUSTED_PENDING,:B8 , :B7 ,PS.DUE_DATE,:B6 ,:B4 ) *
GL_CURRENCY_API.CONVERT_AMOUNT( PS.INVOICE_CURRENCY_CODE, :B2 , SYSDATE,
:B5 , PS.AMOUNT_DUE_REMAINING)) BUCKET6 FROM AR_PAYMENT_SCHEDULES_ALL PS
WHERE PAYMENT_SCHEDULE_ID +0 > 0 AND PS.CLASS NOT IN ('GUAR', 'PMT') AND
NVL(SIGN(PS.CONS_INV_ID),0) = DECODE(:B1 ,'CONS_BILL',1,0) AND TRX_DATE <=
:B4 AND ACTUAL_DATE_CLOSED > :B4 AND PS.CUSTOMER_ID IN (SELECT
CUST_ACCOUNT_ID FROM HZ_CUST_ACCOUNTS WHERE PARTY_ID IN ( SELECT CHILD_ID
FROM HZ_HIERARCHY_NODES WHERE PARENT_OBJECT_TYPE = 'ORGANIZATION' AND
PARENT_TABLE_NAME = 'HZ_PARTIES' AND CHILD_OBJECT_TYPE = 'ORGANIZATION' AND
PARENT_ID = :B29 AND EFFECTIVE_START_DATE <= :B4 AND EFFECTIVE_END_DATE >=
:B4 AND HIERARCHY_TYPE = FND_PROFILE.VALUE('AR_CMGT_HIERARCHY_TYPE') AND
:B28 <> 'LNS' UNION SELECT :B29 FROM DUAL UNION SELECT HZ_PARTY_ID FROM
LNS_LOAN_PARTICIPANTS_V WHERE LOAN_ID = :B30 AND PARTICIPANT_TYPE_CODE =
'COBORROWER' AND :B28 = 'LNS' AND (END_DATE_ACTIVE IS NULL OR (SYSDATE
BETWEEN START_DATE_ACTIVE AND END_DATE_ACTIVE) ) ) UNION SELECT :B31 FROM
DUAL ) AND DECODE(:B3 , NULL, PS.CUSTOMER_SITE_USE_ID, :B3 ) =
PS.CUSTOMER_SITE_USE_ID AND ((PS.INVOICE_CURRENCY_CODE = :B2 AND :B1 =
'CONS_BILL') OR (NVL(:B1 ,'x') <> 'CONS_BILL' AND PS.INVOICE_CURRENCY_CODE
IN (SELECT CURRENCY FROM AR_CMGT_CURR_USAGE_GT))) )

SELECT CT.CUSTOMER_TRX_ID TRX_ID, CT.TRX_DATE TRX_DATE, CT.TRX_NUMBER
TRX_NUMBER, PS.CLASS CLASS, PS.PAYMENT_SCHEDULE_ID SCHEDULE_ID,
PS.AMOUNT_DUE_ORIGINAL AMOUNT_DUE, PS.TAX_ORIGINAL TAX,
PS.INVOICE_CURRENCY_CODE CURRENCY, CT.TERM_ID TERM_ID, CT.BILLING_DATE
BILLING_DATE, CT.INITIAL_CUSTOMER_TRX_ID INIT_TRX_ID,
CT.PREVIOUS_CUSTOMER_TRX_ID PREV_TRX_ID, CT.INTERFACE_HEADER_ATTRIBUTE1
TRX_DESC, CT.SHIP_TO_SITE_USE_ID SHIP_ID, CT.TERM_DUE_DATE DUE_DATE
FROM
RA_CUSTOMER_TRX CT, AR_PAYMENT_SCHEDULES PS WHERE PS.CUSTOMER_SITE_USE_ID =
:B3 AND PS.CONS_INV_ID IS NULL AND PS.INVOICE_CURRENCY_CODE = NVL(:B2 ,
PS.INVOICE_CURRENCY_CODE) AND CT.CUSTOMER_TRX_ID = PS.CUSTOMER_TRX_ID AND
CT.PRINTING_OPTION = 'PRI' AND PS.CLASS IN ('INV', 'DM', 'DEP', 'CB','CM')
AND NVL(PS.EXCLUDE_FROM_CONS_BILL_FLAG, 'N') <> 'Y' AND NVL(CT.BILLING_DATE,
CT.TRX_DATE) <= :B1 ORDER BY 10, 1

BUSINESS IMPACT
-----------------------
Due to this issue, users cannot Generate Balance Forward Bills.

Changes

Cause