My Oracle Support Banner

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

Last updated on DECEMBER 14, 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

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!


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