My Oracle Support Banner

Generate Balance Forward Billing Process Performance issue (Doc ID 2284850.1)

Last updated on FEBRUARY 08, 2018

Applies to:

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

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

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.