AutoInvoice Performance Issue: Poor Performance of AutoInvoice, Issue Inserting Into RA_CUST_TRX_LINE_GL_DIST (Doc ID 470537.1)

Last updated on JANUARY 13, 2017

Applies to:

Oracle Receivables - Version 11.5.10.2 to 12.2 [Release 11.5.10 to 12.2]
Information in this document applies to any platform.
Executable:RAXTRX - Autoinvoice Import Program



Symptoms

Autoinvoice (RAXTRX) is having performance issues due to the following insert statement:

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)
)

A similar insert statement is also causing performance issues when entering credit memos in the application

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