Home » Applications » Oracle Fusion Apps & E-Business Suite » SLA issue (Oracle Apps : R12 Version: 12.1.3)
| SLA issue [message #513829] |
Wed, 29 June 2011 10:40  |
 |
Arthi Siva
Messages: 7
Registered: June 2011
Location: US
|
Junior Member |
|
|
Hi,
Need help on the below query.
Initially SLA tables were not present in this, later it was incorporated. But now we face the performance issue. Even for hard coded values also query is taking very long time.
Please correct this:
SELECT /*+ORDERED */
/* $Header: araeginv.sql 115.9 2000/12/19 18:22:40 djancis noship $ */
/* R.je_batch_id JE_BATCH_ID,*/
JEH.je_header_id JE_HEADER_ID,
R.je_line_num JE_LINE_NUM,
222 APPLICATION_ID,
CT.set_of_books_id SET_OF_BOOKS_ID,
CT.org_id ORG_ID,
CTT.TYPE TRX_CLASS,
L1.meaning TRX_CLASS_NAME,
CT.cust_trx_type_id TRX_TYPE_N,
CTT.name TRX_TYPE_NAME,
CT.trx_number TRX_NUMBER_DISPLAYED,
CT.trx_number TRX_NUMBER_C,
CT.trx_date TRX_DATE,
CTLGD.comments COMMENTS,
CT.doc_sequence_id DOC_SEQUENCE_ID,
FD.name DOC_SEQUENCE_NAME,
CT.doc_sequence_value DOC_SEQUENCE_VALUE,
'CT' TRX_HDR_TABLE,
CT.customer_trx_id TRX_HDR_ID,
CTLGD.account_class ACCT_LINE_TYPE,
L3.meaning ACCT_LINE_TYPE_NAME,
ctlgd.code_combination_id CODE_COMBINATION_ID,
ct.invoice_currency_code CURRENCY_CODE,
TO_NUMBER(DECODE (
ctlgd.account_class,
'REC',
DECODE (SIGN (NVL (ctlgd.amount, 0)),
-1, NULL,
NVL (ctlgd.amount, 0)),
DECODE (SIGN (NVL (ctlgd.amount, 0)),
-1, -NVL (ctlgd.amount, 0),
NULL)
))
ENTERED_DR,
TO_NUMBER(DECODE (
ctlgd.account_class,
'REC',
DECODE (SIGN (NVL (ctlgd.amount, 0)),
-1, -NVL (ctlgd.amount, 0),
NULL),
DECODE (SIGN (NVL (ctlgd.amount, 0)),
-1, NULL,
NVL (ctlgd.amount, 0))
))
ENTERED_CR,
TO_NUMBER(DECODE (
ctlgd.account_class,
'REC',
DECODE (SIGN (NVL (ctlgd.amount, 0)),
-1, NULL,
NVL (ctlgd.acctd_amount, 0)),
DECODE (SIGN (NVL (ctlgd.amount, 0)),
-1, -NVL (ctlgd.acctd_amount, 0),
NULL)
))
ACCOUNTED_DR,
TO_NUMBER(DECODE (
ctlgd.account_class,
'REC',
DECODE (SIGN (NVL (ctlgd.amount, 0)),
-1, -NVL (ctlgd.acctd_amount, 0),
NULL),
DECODE (SIGN (NVL (ctlgd.amount, 0)),
-1, NULL,
NVL (ctlgd.acctd_amount, 0))
))
ACCOUNTED_CR,
ct.exchange_date CURRENCY_CONVERSION_DATE,
ct.exchange_rate_type CURRENCY_CONVERSION_TYPE,
glct.user_conversion_type CURRENCY_USER_CONVERSION_TYPE,
ct.exchange_rate CURRENCY_CONVERSION_RATE,
'C' THIRD_PARTY_TYPE,
ct.bill_to_customer_id THIRD_PARTY_ID,
cust_acct.account_number THIRD_PARTY_NUMBER,
SUBSTRB (party.party_name, 1, 50) THIRD_PARTY_NAME,
ct.bill_to_site_use_id THIRD_PARTY_SUB_ID,
SU.location THIRD_PARTY_SUB_NAME,
ctlgd.gl_date ACCOUNTING_DATE,
L4.meaning GL_TRANSFER_STATUS_NAME,
DECODE (ctlgd.posting_control_id, -3, 'N', 'Y')
GL_TRANSFER_STATUS,
DECODE (ctlgd.account_class, 'REC', 'CT', 'CTLGD') SOURCE_TABLE,
DECODE (ctlgd.account_class,
'REC', ct.customer_trx_id,
ctlgd.cust_trx_line_gl_dist_id)
SOURCE_ID,
RR.name ACCOUNTING_RULE_NAME,
ctlgd.cust_trx_line_gl_dist_id AEL_ID,
DECODE (
CTL.line_number,
NULL,
L1.meaning || ' ' || CT.trx_number,
DECODE (
CTL2.line_number,
NULL,
DECODE (CTL.line_number,
NULL, NULL,
L5.meaning || ' ' || TO_NUMBER (CTL.line_number)),
L5.meaning
|| ' '
|| TO_NUMBER (CTL2.line_number)
|| ', '
|| L6.meaning
|| ' '
|| CTL.line_number
)
)
AE_LINE_REFERENCE,
DECODE (
CTL.line_number,
NULL,
RPAD (L1.meaning, 80) || ' ' || RPAD (CT.trx_number, 20),
DECODE (
CTL2.line_number,
NULL,
DECODE (
CTL.line_number,
NULL,
NULL,
L5.meaning
|| ' '
|| LPAD (TO_NUMBER (CTL.line_number), 15, '0')
),
L5.meaning
|| ' '
|| LPAD (TO_NUMBER (CTL2.line_number), 15, '0')
|| ', '
|| L6.meaning
|| ' '
|| LPAD (TO_NUMBER (CTL.line_number), 15, '0')
)
)
AE_LINE_REFERENCE_INTERNAL,
'CTLGD' AEL_TABLE,
CTLGD.last_update_date LAST_UPDATE_DATE,
CTLGD.last_updated_by LAST_UPDATED_BY,
CTLGD.creation_date CREATION_DATE,
CTLGD.created_by CREATED_BY,
CTLGD.last_update_login LAST_UPDATE_LOGIN,
CTLGD.request_id REQUEST_ID,
CTLGD.program_application_id PROGRAM_APPLICATION_ID,
CTLGD.program_id PROGRAM_ID,
CTLGD.program_update_date PROGRAM_UPDATE_DATE, /* The following columns are specific to AR Transaction */
BS.name TRX_SOURCE_NAME,
CT.batch_source_id TRX_SOURCE_ID,
CTL.tax_exempt_number TAX_EXEMPT_NUMBER,
CTL.inventory_item_id INVENTORY_ITEM_ID,
DECODE (CTL2.line_number,
NULL, TO_NUMBER (NULL),
CTL.line_number)
TRX_DETAIL_LINE_NUMBER,
DECODE (CTL2.line_number,
NULL, CTL.line_number,
CTL2.line_number)
TRX_LINE_NUMBER,
CTL.line_type TRX_LINE_TYPE,
L2.meaning TRX_LINE_TYPE_NAME,
CTL.quantity_invoiced TRX_QUANTITY,
CTL.sales_order SALES_ORDER_NUMBER,
DECODE (CTLSR.salesrep_id, NULL, NULL, S.name) SALESREP_NAME,
AVT.tax_code TAX_CODE,
CTL.vat_tax_id TAX_CODE_ID,
CTL.tax_rate TAX_RATE,
CTL.unit_selling_price UNIT_SELLING_PRICE,
MUOM.unit_of_measure TRX_UOM,
TO_DATE (NULL) APPLICATION_DATE,
NULL APPLIED_TO_TRX_HDR_TABLE,
TO_NUMBER (NULL) APPLIED_TO_TRX_HDR_ID,
NULL APPLIED_TO_TRX_HDR_NUMBER_C,
NULL APPLIED_TO_TRX_HDR_NUMBER_DISP,
NULL APPLIED_TO_TRX_HDR_CURRENCY,
TO_DATE (NULL) APPLIED_TO_TRX_HDR_DATE,
NULL APPLIED_TO_TRX_LINE_TYPE_NAME,
NULL APPLIED_TO_TRX_LINE_TYPE,
TO_NUMBER (NULL) APPLIED_TO_TRX_LINE_NUMBER--,r.gl_sl_link_id,r.gl_sl_link_table
FROM gl_je_headers JEH,
gl_import_references R,
ra_cust_trx_line_gl_dist_all CTLGD,
ra_cust_trx_line_salesreps_all CTLSR,
ra_customer_trx_all CT,
ra_batch_sources_all BS,
ra_cust_trx_types_all CTT,
hz_cust_site_uses_all SU,
hz_cust_accounts CUST_ACCT,
hz_parties PARTY,
ra_customer_trx_lines_all CTL2,
ra_customer_trx_lines_all CTL,
ar_vat_tax_all AVT,
mtl_units_of_measure MUOM,
ra_rules RR,
ra_salesreps_all S,
fnd_document_sequences FD,
gl_daily_conversion_types GLCT,
ar_lookups L1,
ar_lookups L3,
ar_lookups L4,
ar_lookups L2,
ar_lookups L5,
ar_lookups L6,
xla_distribution_links lk,
xla_ae_headers hd,
xla_ae_lines ae
-- XLA_TRANSACTION_ENTITIES XTE
WHERE L6.lookup_code = 'DETAIL_LINE'
AND L6.lookup_type = 'VIEW_ACCOUNTING'
AND L5.lookup_code = 'LINE'
AND L5.lookup_type = 'VIEW_ACCOUNTING'
AND L3.lookup_type =
DECODE (ctlgd.collected_tax_ccid,
NULL, 'AUTOGL_TYPE',
'DISTRIBUTION_SOURCE_TYPE')
AND L3.lookup_code =
DECODE (ctlgd.collected_tax_ccid,
NULL, NVL (CTLGD.account_class, 'REV'),
'DEFERRED_TAX')
AND /* Bug 2087048 */
CTLSR.cust_trx_line_salesrep_id(+) =
CTLGD.cust_trx_line_salesrep_id
AND NVL (CTLSR.salesrep_id, -3) = S.salesrep_id
AND NVL (CT.org_id, -99) = NVL (S.org_id, -99)
AND /* end 2087048 */
AVT.vat_tax_id(+) = CTL.vat_tax_id
AND NVL (AVT.org_id(+), -99) = NVL (CTL.org_id, -99)
AND MUOM.uom_code(+) = CTL.uom_code
AND RR.rule_id(+) = CTL.accounting_rule_id
AND L4.lookup_code =
DECODE (ctlgd.posting_control_id, -3, 'N', 'Y')
AND L4.lookup_type = 'YES/NO'
AND L2.lookup_code(+) = CTL.line_type
AND L2.lookup_type(+) = 'STD_LINE_TYPE'
AND CT.doc_sequence_id = FD.doc_sequence_id(+)
AND L1.lookup_code = CTT.TYPE
AND L1.lookup_type = 'INV/CM'
AND NVL (CT.org_id, -99) = NVL (CTT.org_id, -99)
AND CT.cust_trx_type_id = CTT.cust_trx_type_id
AND CT.exchange_rate_type = GLCT.conversion_type(+)
AND CT.bill_to_site_use_id = SU.site_use_id
AND CT.bill_to_customer_id = CUST_ACCT.cust_account_id
AND CUST_ACCT.party_id = PARTY.party_id
AND NVL (CT.org_id, -99) = NVL (BS.org_id, -99)
AND CT.batch_source_id = BS.batch_source_id
AND CTL.link_to_cust_trx_line_id = CTL2.customer_trx_line_id(+)
AND NVL (CTL.org_id, -99) = NVL (CTL2.org_id(+), -99)
AND CTLGD.customer_trx_line_id = CTL.customer_trx_line_id(+)
AND NVL (CTLGD.org_id, -99) = NVL (CTL.org_id(+), -99)
AND CTLGD.account_set_flag = 'N'
AND CT.customer_trx_id = CTLGD.customer_trx_id
AND NVL (CT.org_id, -99) = NVL (CTLGD.org_id, -99)
--AND CTLGD.cust_trx_line_gl_dist_id = TO_NUMBER (R.reference_3)
--AND R.reference_10 = 'RA_CUST_TRX_LINE_GL_DIST'
AND R.je_header_id = JEH.je_header_id
AND JEH.je_category IN
('Sales Invoices',
'Credit Memos',
'Debit Memos',
'Chargebacks')
and ctlgd.cust_trx_line_gl_dist_id = lk.source_distribution_id_num_1
AND ae.application_id = 222
AND lk.source_distribution_type = 'RA_CUST_TRX_LINE_GL_DIST_ALL'
AND lk.application_id = 222
AND lk.ae_header_id = ae.ae_header_id
AND lk.ae_line_num = ae.ae_line_num
AND lk.ae_header_id = hd.ae_header_id
AND hd.ledger_id = ctlgd.set_of_books_id
and r.gl_sl_link_id=ae.gl_sl_link_id
and r.gl_sl_link_table=ae.gl_sl_link_table
and ae.ledger_id=1001
and ctlgd.gl_date between '01-JAN-2011' and '31-JAN-2011'
and jeh.je_header_id=628912
and r.je_line_num=6
Do i need to include few table links.
Thanks In Advance
Arthi
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Sun Nov 24 04:54:37 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
