Home » Applications » Oracle Fusion Apps & E-Business Suite » Tuning select statement that link between sub ledgers and GL
Tuning select statement that link between sub ledgers and GL [message #521707] Thu, 01 September 2011 07:47 Go to next message
raul33
Messages: 26
Registered: October 2008
Location: jordan
Junior Member
Dears,

I am working on oracle apps r12.

I need to tune this select statement because it's need alot of minutes to execute how i can it?

select glcc.concatenated_segments Account_number,
invoice.gl_date gl_date,
je_lines.ENTERED_DR ENTERED_DR,
je_lines.ENTERED_CR ENTERED_CR,
je_lines.ACCOUNTED_DR ACCOUNTED_DR,
je_lines.ACCOUNTED_CR ACCOUNTED_CR,
from gl_je_lines je_lines,gl_import_references gl_ref,xla_ae_lines xla_lines,
xla_ae_headers xla_headers,xla_events xla_events,xla_transaction_entities xla_trans,
ap_invoices_all invoice,gl_je_headers h, gl_code_combinations_kfv glcc
where je_lines.je_header_id = gl_ref.je_header_id
and je_lines.je_line_num = gl_ref.je_line_num
and gl_ref.gl_sl_link_table = xla_lines.gl_sl_link_table
and gl_ref.gl_sl_link_id = xla_lines.gl_sl_link_id
and xla_lines.APPLICATION_ID = xla_headers.APPLICATION_ID
and xla_lines.ae_header_id = xla_headers.ae_header_id
and xla_headers.application_id = xla_events.application_id
and xla_headers.event_id = xla_events.event_id
and h.actual_flag = 'A'
and xla_trans.APPLICATION_ID = xla_events.APPLICATION_ID
and xla_trans.entity_id = xla_events.entity_id
--and invoice.invoice_num = xla_trans.transaction_number
and invoice.invoice_id = xla_trans.SOURCE_ID_INT_1
and invoice.invoice_id = 10020
and h.je_source = 'Payables'
and xla_lines.code_combination_id = glcc.code_combination_id
and je_lines.je_header_id = h.je_header_id;


Thanks in advance

Report message to a moderator

Re: Tuning select statement that link between sub ledgers and GL [message #521754 is a reply to message #521707] Thu, 01 September 2011 14:42 Go to previous messageGo to next message
vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH
Senior Member
You had my answer already long back.
Lets see what you have missed out:
1. You didn't use xla.xla_transaction_entities or xla_transaction_entities_upg.
You will hit the issue mentioned here.
2. Have ledger_id join in between xla.xla_transaction_entities and ap_invoices_all.
3. Use entity_code filter and nvl over source_id_int_1 (as mentioned in the link) for xla.xla_transaction_entities.
Also,
4. You may use application_id filter on all mentioned XLA tables as 200, as it is for invoice.
5. If you have transferred journal entries to GL in Summary mode, XLA will have the specific accounting entry of invoice than GL.

Finally, as you have hard coded the invoice_id, the explain plan should start from that.
If not, have a hint.

By
Vamsi

Report message to a moderator

Re: Tuning select statement that link between sub ledgers and GL [message #521869 is a reply to message #521754] Fri, 02 September 2011 11:56 Go to previous message
raul33
Messages: 26
Registered: October 2008
Location: jordan
Junior Member
its Very helpful, thanks a lot Mr.vamsi Smile.

Report message to a moderator

Previous Topic: BOM_BO_PUB.process_bom processes first bom and errors out every run
Next Topic: pay alone in r12
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Apr 18 21:21:09 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.