slow query issue [urgent]

[RIAZ]

dear all
I am working in Oracle 11i Apps, problem is

I am trying to JOIN two tables using the following criteria

GL_JE_LINES.GL_SL_LINK_ID = CST_AE_LINES.GL_SL_LINK_ID

but this take too much time to execute

please guide me....

[Sadik]

hi

can we see the full query?

[Sadik]

and did you see another member's response here?

GL to Inventory Mapping Issue

[RIAZ]

SELECT    gcc.segment1
       || '-'
       || gcc.segment2
       || '-'
       || gcc.segment3
       || '-'
       || gcc.segment4
       || '-'
       || gcc.segment5
       || '-'
       || gcc.segment6
       || '-'
       || gcc.segment7 AS gl_account,
       NVL (cal.accounted_dr, 0) AS ac_dr, NVL (cal.accounted_cr, 0) AS ac_cr
  FROM cst_ae_headers cah,
       cst_ae_lines cal,
       gl_code_combinations gcc,
       gl_je_lines gjl,
       gl_je_headers gjh
 WHERE gcc.segment4 BETWEEN :p_acct_from AND :p_acct_to
   AND TRUNC (gjl.effective_date) <= :p_date
   AND gcc.code_combination_id = gjl.code_combination_id
   AND gjh.je_header_id = gjl.je_header_id
   AND gjh.je_source = 'Periodic Inventory'
   AND gjl.STATUS = 'P'
   AND gjl.gl_sl_link_id = NVL (cal.gl_sl_link_id, 0)
   AND cah.ae_header_id = cal.ae_header_id
   AND cah.period_name = gjh.period_name
   AND cah.ae_category = gjh.je_category

[Sadik]

hi

It maybe an issue with the between clause. Try narrowing down on the accounts range and see.

[RIAZ]

huuuu... tried this ALSO but same problem (( please help me.

[zargon]

huuuu... tried this ALSO but same problem (( please help me.

Then provide the query plan for this 'problem'; no one can find the issue if you don't provide the proper information.

[RIAZ]

"Basically i want to perform mapping from GL to INVENTORY & PAYABLES "

I want to get transactional details (like po number, item description). I have GL account number and i want to route from GL to get the material transactional details.

[Sadik]

RIAZ did you check the view XLA_INV_AEL_GL_V. It might have everything you need. If not, pay special attention to the create view script for this view (or it's underlying view).