Autoinvoice: RAXTRX - Performance Problem For OKS Credit Memo Inserting Into RA_CUST_TRXLINE_GL_DIST (Doc ID 1083493.1)

Last updated on APRIL 06, 2017

Applies to:

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

Symptoms

Autoinvoice is having performance issues for large OKS Credit Memo.  The following SQL Statement shows as the top sql in the tkprof file:

INSERT INTO ra_cust_trx_line_gl_dist
(
cust_trx_line_gl_dist_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
program_application_id,
program_id,
program_update_date,
request_id,
posting_control_id,
customer_trx_id,
customer_trx_line_id,
cust_trx_line_salesrep_id,
gl_date,
original_gl_date,
set_of_books_id,
code_combination_id,
concatenated_segments,
account_class,
account_set_flag,
amount,
acctd_amount,
percent,
ussgl_transaction_code,
ussgl_transaction_code_context,
comments,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
collected_tax_ccid,
collected_tax_concat_seg,
revenue_adjustment_id /* Bug 2543675 */
)
(SELECT /*+ ORDERED
INDEX (ara.ragt ar_revenue_assignments_gt_n1) */
ra_cust_trx_line_gl_dist_s.nextval, /* cust_trx_line_dist_id */
ctl.created_by, /* created_by */
sysdate, /* creation_date */
ctl.last_updated_by, /* last_updated_by */
sysdate, /* last_update_date */
ctl.last_update_login, /* last_update_login */
ctl.program_application_id, /* program_application_id */
ctl.program_id, /* program_id */
sysdate, /* program_update_date */
ctl.request_id, /* request _id */
-3,
ctl.customer_trx_id, /* customer_trx_id */
:customer_trx_line_id, /* customer_trx_line_id */
ctls.cust_trx_line_salesrep_id, /* cust_trx_line_srep_id */
/* Bug 2142941 - use lgd.gl_date and lgd.original_gl_date instead of bind
variables :gl_date and :original_gl_date */
/* Bug 2194742 - Used bind variable for gl_date */
:gl_date, /* gl_date */
lgd.original_gl_date, /* original_gl_date */
lgd.set_of_books_id, /* set_of_books_id */
lgd.code_combination_id, /* code_combination_id */
lgd.concatenated_segments, /* concatenated_segments */
lgd.account_class, /* account class */
'N', /* account_set_flag */
decode( fc.minimum_accountable_unit,
NULL, round( (:amount * (decode(ara.amount, 0, 1, lgd.amount) /
decode(ara.amount,
0, decode(lgd.amount, 0, 1,lgd.amount),
ara.amount) ) ),
fc.precision),
round( (:amount * (decode(ara.amount, 0, 1, lgd.amount) /
decode(ara.amount,
0, decode(lgd.amount, 0, 1,lgd.amount),
ara.amount) ) ) /
fc.minimum_accountable_unit ) *
fc.minimum_accountable_unit
) * decode(lgd.account_class,
'REV', 1,
-1), /* amount */
round( decode( fc.minimum_accountable_unit,
null, round((:amount * (decode(ara.amount, 0, 1, lgd.amount) /
decode(ara.amount,
0, decode(lgd.amount, 0, 1,lgd.amount),
ara.amount) ) ),
fc.precision),
round((:amount * (decode(ara.amount, 0, 1, lgd.amount) /
decode(ara.amount,
0, decode(lgd.amount, 0, 1,lgd.amount),
ara.amount) ) ) /
fc.minimum_accountable_unit)
* fc.minimum_accountable_unit )
* nvl(ct.exchange_rate, 1) , 2 )
* decode(lgd.account_class,
'REV', 1,
-1), /* acctd_amount */
decode(lgd.account_class,
'UNBILL', -1 * round( ( (:amount * (decode(ara.amount, 0, 1, lgd.amount) /
decode(ara.amount,
0, decode(lgd.amount, 0, 1,lgd.amount),
ara.amount) ) )
/ decode(ctl.revenue_amount,
0, 1, ctl.revenue_amount)
) * 100, 4),
'UNEARN', -1 * round( ( (:amount * (decode(ara.amount, 0, 1, lgd.amount) /
decode(ara.amount,
0, decode(lgd.amount, 0, 1,lgd.amount),
ara.amount) ) )
/ decode(ctl.revenue_amount,
0, 1, ctl.revenue_amount)
) * 100, 4),
round( ( (:amount * (decode(ara.amount, 0, 1, lgd.amount) /
decode(ara.amount,
0, decode(lgd.amount, 0, 1,lgd.amount),
ara.amount) ) )
/ decode(ctl.revenue_amount,
0, 1, ctl.revenue_amount)
) * 100, 4)
), /* percent */
ct.default_ussgl_transaction_code, /* ussgl_trx_code */
ct.default_ussgl_trx_code_context, /* ussgl_trx_code_context */
NULL, /* comments */
NULL, /* attribute_category */
NULL, /* attribute1 */
NULL, /* attribute2 */
NULL, /* attribute3 */
NULL, /* attribute4 */
NULL, /* attribute5 */
NULL, /* attribute6 */
NULL, /* attribute7 */
NULL, /* attribute8 */
NULL, /* attribute9 */
NULL, /* attribute10 */
NULL, /* attribute11 */
NULL, /* attribute12 */
NULL, /* attribute13 */
NULL, /* attribute14 */
NULL, /* attribute15 */
lgd.collected_tax_ccid, /* collected tax */
lgd.collected_tax_concat_seg, /* collected tax seg */
lgd.revenue_adjustment_id /* revenue_adjustment_id */ /*Bug 2543675*/
FROM
ra_customer_trx_lines ctl,
ra_customer_trx ct,
fnd_currencies fc,
ar_revenue_assignments_v ara /* Bug 2347001 */,
ra_cust_trx_line_gl_dist inv_rec,
ra_cust_trx_line_gl_dist lgd,
ra_cust_trx_line_salesreps ctls
WHERE lgd.customer_trx_line_id = ctl.previous_customer_trx_line_id
and ctl.previous_customer_trx_line_id = ara.customer_trx_line_id
and ara.session_id IN (:session_id,-99) /* Bug 2347001 */ --PPRR Backport Bug : 5333489
and ara.gl_date = nvl(lgd.original_gl_date, lgd.gl_date)
and ara.account_class = lgd.account_class
and ara.period_set_name = :period_set_name /* 4254587 */
and ct.customer_trx_id = ctl.customer_trx_id
and inv_rec.customer_trx_id = ctl.previous_customer_trx_id
and inv_rec.account_class = 'REC'
and inv_rec.latest_rec_flag = 'Y'
/* Bug 2899714 */
and lgd.cust_trx_line_salesrep_id = ctls.prev_cust_trx_line_salesrep_id(+)
and ctls.customer_trx_line_id(+) = :customer_trx_line_id
and ctl.customer_trx_line_id = :customer_trx_line_id
and fc.currency_code = ct.invoice_currency_code
and lgd.account_set_flag = 'N'
and ( (lgd.account_class in ('REV', 'UNEARN', 'UNBILL') and
:insert_offset = 'Y' ) or
(lgd.account_class = 'REV' and :insert_offset = 'N' ) )
/* inv_dist_exists is set to "F" when crediting a release 9
immediate invoice. In this case, the cm gl_date may not correspond
to any inv gl_date, so the date check cannot be done. */
/* Bug 2142941 - include join onto lgd.original_gl_date */
and (
( trunc(ara.gl_date) = lgd.original_gl_date AND
lgd.original_gl_date = :original_gl_date)
OR
:check_gl_date = 'N'
)
/* Bug 2535023 - Revamped fixes from bugs 1936152 and 2354805
so that the insert now relies upon rec_offset_flag instead
of that and-not stuff. Forced routine to only
copy conventional distributions. */
/* Bug 2543675 - include RAM distributions */
and lgd.rec_offset_flag is null
and (ar_revenue_management_pvt.cash_based(ctl.previous_customer_trx_id)
= 1 or
lgd.revenue_adjustment_id is null)
)

AR Patchset = 11i.AR.O and <Patch 6027573> from <Note:470537.1> RAXTRX - Autoinvoice Performance Issue Inserting Into RA_CUST_TRX_LINE_GL_DIST is already applied

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