{var%20f='http://v.t.sina.com.cn/share/share.php?appkey=1515056452',u=z||d.location,p=['&url=',e(u),'&title=',e(t||d.title),'&source=',e(r),'&sourceUrl=',e(l),'&content=',c||'gb2312','&pic=',e(p||'')].join('');function%20a(){if(!window.open([f,p].join(''),'mb',['toolbar=0,status=0,resizable=1,width=440,height=430,left=',(s.width-440)/2,',top=',(s.height-430)/2].join('')))u.href=[f,p].join('');};if(/Firefox/.test(navigator.userAgent))setTimeout(a,0);else%20a();})(screen,document,encodeURIComponent,'','','https://www.manongdao.com/data/attach/logo/logo.png', '推荐 该账号已被封号 的问题《Query taking too much time in oracle: Read by othe》','https://www.manongdao.com/q-910037.html','页面编码gb2312|utf-8默认gb2312'));){kind=link}
I am working in EBS system. for a particular functionality one query is taking 99% of the time. proper index are getting used. I checked in explain plan. when I used autotrace the below statistics I got.
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 1 0.00 0.00
Disk file operations I/O 1047 0.00 0.15
db file sequential read 167048 0.64 1584.33
db file parallel read 216 0.05 1.37
read by other session 520944 0.71 5113.10
latch: cache buffers chains 31768 0.00 1.32
db file scattered read 50 0.01 0.14
wait list latch free 1 0.00 0.00
I can see read by other session and latch: cache buffers chains taking too much time. is that normal? I read about latch and i found it is OK. how to correct the read by other session problem. this problem is occuring in Users machine. what advice can i give me to rectify the issue in his system?
Below is the execution plan.
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.11 0.11 0 0 0 0
Execute 1 1.40 1.40 0 0 0 0
Fetch 1 171.37 6824.71 167607 3545077 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 172.90 6826.24 167607 3545077 0 0
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 173
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
0 0 0 SORT ORDER BY (cr=0 pr=0 pw=0 time=44 us cost=318575 size=901 card=3)
0 0 0 UNION-ALL (cr=0 pr=0 pw=0 time=34 us)
0 0 0 NESTED LOOPS (cr=0 pr=0 pw=0 time=29 us cost=181784 size=357 card=1)
0 0 0 NESTED LOOPS (cr=0 pr=0 pw=0 time=27 us cost=181784 size=357 card=1)
0 0 0 NESTED LOOPS OUTER (cr=0 pr=0 pw=0 time=26 us cost=181782 size=308 card=1)
0 0 0 NESTED LOOPS OUTER (cr=0 pr=0 pw=0 time=25 us cost=181780 size=269 card=1)
0 0 0 NESTED LOOPS OUTER (cr=0 pr=0 pw=0 time=23 us cost=181778 size=229 card=1)
0 0 0 HASH JOIN (cr=0 pr=0 pw=0 time=22 us cost=181776 size=203 card=1)
399 399 399 TABLE ACCESS BY INDEX ROWID BATCHED GL_CODE_COMBINATIONS (cr=65 pr=17 pw=0 time=7099 us cost=7 size=40 card=1)
399 399 399 INDEX RANGE SCAN XXGL_WW_CODE_COMB_N99 (cr=7 pr=5 pw=0 time=5082 us cost=6 size=0 card=1)(object id 25142911)
0 0 0 NESTED LOOPS (cr=0 pr=0 pw=0 time=25 us cost=181769 size=22820 card=140)
0 0 0 NESTED LOOPS (cr=0 pr=0 pw=0 time=21 us cost=181769 size=22820 card=140)
0 0 0 NESTED LOOPS (cr=0 pr=0 pw=0 time=20 us cost=181724 size=1980 card=15)
0 0 0 HASH JOIN (cr=0 pr=0 pw=0 time=19 us cost=181693 size=1530 card=15)
1146877 1146877 1146877 NESTED LOOPS (cr=3545011 pr=167590 pw=0 time=3850324669 us cost=181689 size=1005 card=15)
1146878 1146878 1146878 NESTED LOOPS (cr=1888277 pr=14528 pw=0 time=1674644783 us cost=181689 size=1005 card=1255314)
769549 769549 769549 TABLE ACCESS BY INDEX ROWID BATCHED MTL_ITEM_CATEGORIES (cr=51305 pr=7253 pw=0 time=56155426 us cost=6029 size=607410 card=40494)
769549 769549 769549 INDEX RANGE SCAN MTL_ITEM_CATEGORIES_N4 (cr=2441 pr=809 pw=0 time=8372404 us cost=190 size=0 card=40494)(object id 24699978)
1146878 1146878 1146878 INDEX RANGE SCAN MTL_MATERIAL_TRANSACTIONS_N1 (cr=1836972 pr=7275 pw=0 time=323445690 us cost=3 size=0 card=31)(object id 1215228)
1146877 1146877 1146877 TABLE ACCESS BY INDEX ROWID MTL_MATERIAL_TRANSACTIONS (cr=1656734 pr=153062 pw=0 time=2130656126 us cost=30 size=52 card=1)
0 0 0 TABLE ACCESS FULL MTL_TRANSACTION_TYPES (cr=0 pr=0 pw=0 time=0 us cost=4 size=4095 card=117)
0 0 0 TABLE ACCESS BY INDEX ROWID BATCHED CST_AE_HEADERS (cr=0 pr=0 pw=0 time=0 us cost=3 size=30 card=1)
0 0 0 INDEX RANGE SCAN CST_AE_HEADERS_N4 (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=1)(object id 10369819)
0 0 0 INDEX RANGE SCAN CST_AE_LINES_N1 (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=9)(object id 30823)
0 0 0 TABLE ACCESS BY INDEX ROWID CST_AE_LINES (cr=0 pr=0 pw=0 time=0 us cost=3 size=279 card=9)
0 0 0 TABLE ACCESS BY INDEX ROWID BATCHED MTL_GENERIC_DISPOSITIONS (cr=0 pr=0 pw=0 time=0 us cost=2 size=26 card=1)
0 0 0 INDEX RANGE SCAN MTL_GENERIC_DISPOSITIONS_U1 (cr=0 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 37720)
0 0 0 TABLE ACCESS BY INDEX ROWID GL_CODE_COMBINATIONS (cr=0 pr=0 pw=0 time=0 us cost=2 size=40 card=1)
0 0 0 INDEX UNIQUE SCAN GL_CODE_COMBINATIONS_U1 (cr=0 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 33901)
0 0 0 TABLE ACCESS BY INDEX ROWID MTL_SALES_ORDERS (cr=0 pr=0 pw=0 time=0 us cost=2 size=39 card=1)
0 0 0 INDEX UNIQUE SCAN MTL_SALES_ORDERS_U1 (cr=0 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 37889)
0 0 0 INDEX UNIQUE SCAN MTL_SYSTEM_ITEMS_B_U1 (cr=0 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 38017)
0 0 0 TABLE ACCESS BY INDEX ROWID MTL_SYSTEM_ITEMS_B (cr=0 pr=0 pw=0 time=0 us cost=2 size=49 card=1)
0 0 0 NESTED LOOPS (cr=0 pr=0 pw=0 time=0 us cost=93756 size=275 card=1)
0 0 0 NESTED LOOPS (cr=0 pr=0 pw=0 time=0 us cost=93754 size=260 card=1)
0 0 0 NESTED LOOPS (cr=0 pr=0 pw=0 time=0 us cost=93752 size=220 card=1)
0 0 0 NESTED LOOPS (cr=0 pr=0 pw=0 time=0 us cost=93751 size=204 card=1)
0 0 0 NESTED LOOPS (cr=0 pr=0 pw=0 time=0 us cost=93748 size=160 card=1)
0 0 0 NESTED LOOPS (cr=0 pr=0 pw=0 time=0 us cost=93746 size=126 card=1)
0 0 0 NESTED LOOPS (cr=0 pr=0 pw=0 time=0 us cost=93744 size=98 card=1)
0 0 0 NESTED LOOPS (cr=0 pr=0 pw=0 time=0 us cost=93742 size=87 card=1)
0 0 0 HASH JOIN (cr=0 pr=0 pw=0 time=0 us cost=93740 size=77 card=1)
0 0 0 TABLE ACCESS BY INDEX ROWID BATCHED CST_AE_HEADERS (cr=0 pr=0 pw=0 time=0 us cost=3694 size=3532124 card=103886)
0 0 0 INDEX RANGE SCAN CST_AE_HEADERS_N3 (cr=0 pr=0 pw=0 time=0 us cost=769 size=0 card=103886)(object id 7349852)
0 0 0 TABLE ACCESS FULL RCV_TRANSACTIONS (cr=0 pr=0 pw=0 time=0 us cost=79654 size=630118345 card=14653915)
0 0 0 TABLE ACCESS BY INDEX ROWID PO_LINE_LOCATIONS_ALL (cr=0 pr=0 pw=0 time=0 us cost=2 size=10 card=1)
0 0 0 INDEX UNIQUE SCAN PO_LINE_LOCATIONS_U1 (cr=0 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 45143)
0 0 0 TABLE ACCESS BY INDEX ROWID RCV_SHIPMENT_LINES (cr=0 pr=0 pw=0 time=0 us cost=2 size=11 card=1)
0 0 0 INDEX UNIQUE SCAN RCV_SHIPMENT_LINES_U1 (cr=0 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 45222)
0 0 0 TABLE ACCESS BY INDEX ROWID MTL_SYSTEM_ITEMS_B (cr=0 pr=0 pw=0 time=0 us cost=2 size=28 card=1)
0 0 0 INDEX UNIQUE SCAN MTL_SYSTEM_ITEMS_B_U1 (cr=0 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 38017)
0 0 0 TABLE ACCESS BY INDEX ROWID MTL_SYSTEM_ITEMS_TL (cr=0 pr=0 pw=0 time=0 us cost=2 size=34 card=1)
0 0 0 INDEX UNIQUE SCAN MTL_SYSTEM_ITEMS_TL_U1 (cr=0 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 38056)
0 0 0 TABLE ACCESS BY INDEX ROWID BATCHED CST_AE_LINES (cr=0 pr=0 pw=0 time=0 us cost=3 size=44 card=1)
0 0 0 INDEX RANGE SCAN CST_AE_LINES_N1 (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=9)(object id 30823)
0 0 0 TABLE ACCESS BY INDEX ROWID PO_DISTRIBUTIONS_ALL (cr=0 pr=0 pw=0 time=0 us cost=1 size=16 card=1)
0 0 0 INDEX UNIQUE SCAN PO_DISTRIBUTIONS_U1 (cr=0 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 45005)
0 0 0 TABLE ACCESS BY INDEX ROWID GL_CODE_COMBINATIONS (cr=0 pr=0 pw=0 time=0 us cost=2 size=40 card=1)
0 0 0 INDEX UNIQUE SCAN GL_CODE_COMBINATIONS_U1 (cr=0 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 33901)
0 0 0 INDEX RANGE SCAN MTL_ITEM_CATEGORIES_U1 (cr=0 pr=0 pw=0 time=0 us cost=2 size=15 card=1)(object id 1229612)
0 0 0 NESTED LOOPS (cr=0 pr=0 pw=0 time=0 us cost=43034 size=269 card=1)
0 0 0 NESTED LOOPS (cr=0 pr=0 pw=0 time=0 us cost=43034 size=269 card=1)
0 0 0 NESTED LOOPS (cr=0 pr=0 pw=0 time=0 us cost=43032 size=229 card=1)
0 0 0 NESTED LOOPS (cr=0 pr=0 pw=0 time=0 us cost=43029 size=198 card=1)
0 0 0 NESTED LOOPS (cr=0 pr=0 pw=0 time=0 us cost=43027 size=149 card=1)
0 0 0 NESTED LOOPS (cr=0 pr=0 pw=0 time=0 us cost=43025 size=134 card=1)
0 0 0 NESTED LOOPS (cr=0 pr=0 pw=0 time=0 us cost=43023 size=123 card=1)
0 0 0 NESTED LOOPS (cr=0 pr=0 pw=0 time=0 us cost=43021 size=105 card=1)
0 0 0 HASH JOIN (cr=0 pr=0 pw=0 time=0 us cost=43020 size=61 card=1)
0 0 0 TABLE ACCESS BY INDEX ROWID BATCHED CST_AE_HEADERS (cr=0 pr=0 pw=0 time=0 us cost=3694 size=3532124 card=103886)
0 0 0 INDEX RANGE SCAN CST_AE_HEADERS_N3 (cr=0 pr=0 pw=0 time=0 us cost=769 size=0 card=103886)(object id 7349852)
0 0 0 TABLE ACCESS FULL RCV_ACCOUNTING_EVENTS (cr=0 pr=0 pw=0 time=0 us cost=35347 size=210197052 card=7785076)
0 0 0 TABLE ACCESS BY INDEX ROWID RCV_ACCOUNTING_EVENT_TYPES (cr=0 pr=0 pw=0 time=0 us cost=1 size=44 card=1)
0 0 0 INDEX UNIQUE SCAN RCV_ACCOUNTING_EVENT_TYPES_U1 (cr=0 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 24751323)
0 0 0 TABLE ACCESS BY INDEX ROWID RCV_TRANSACTIONS (cr=0 pr=0 pw=0 time=0 us cost=2 size=18 card=1)
0 0 0 INDEX UNIQUE SCAN RCV_TRANSACTIONS_U1 (cr=0 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 45286)
0 0 0 TABLE ACCESS BY INDEX ROWID RCV_SHIPMENT_LINES (cr=0 pr=0 pw=0 time=0 us cost=2 size=11 card=1)
0 0 0 INDEX UNIQUE SCAN RCV_SHIPMENT_LINES_U1 (cr=0 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 45222)
0 0 0 INDEX RANGE SCAN MTL_ITEM_CATEGORIES_U1 (cr=0 pr=0 pw=0 time=0 us cost=2 size=15 card=1)(object id 1229612)
0 0 0 TABLE ACCESS BY INDEX ROWID MTL_SYSTEM_ITEMS_B (cr=0 pr=0 pw=0 time=0 us cost=2 size=49 card=1)
0 0 0 INDEX UNIQUE SCAN MTL_SYSTEM_ITEMS_B_U1 (cr=0 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 38017)
0 0 0 TABLE ACCESS BY INDEX ROWID BATCHED CST_AE_LINES (cr=0 pr=0 pw=0 time=0 us cost=3 size=279 card=9)
0 0 0 INDEX RANGE SCAN CST_AE_LINES_N1 (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=9)(object id 30823)
0 0 0 INDEX UNIQUE SCAN GL_CODE_COMBINATIONS_U1 (cr=0 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 33901)
0 0 0 TABLE ACCESS BY INDEX ROWID GL_CODE_COMBINATIONS (cr=0 pr=0 pw=0 time=0 us cost=2 size=40 card=1)
Query is
SELECT
mmt . transaction_date Txn_date ,
GCC.SEGMENT1||'
'||GCC.SEGMENT2||'
'||GCC.SEGMENT3||'
'||GCC.SEGMENT4||'
'||GCC.SEGMENT5||'
'||GCC.SEGMENT6 C_acct_flex1 ,
MSI.SEGMENT1 C_item_flex1 ,
msi . description Description ,
mtt.transaction_type_name Type ,
mmt . transaction_source_type_id Type_id ,
mmt . organization_id Org_id ,
DECODE ( mmt . transaction_source_type_id ,
1 , to_char ( mmt . transaction_source_id ) ,
2 , MKTS.SEGMENT1||''||MKTS.SEGMENT2||''||MKTS.SEGMENT3 ,
3 , GL.SEGMENT1||''||GL.SEGMENT2||''||GL.SEGMENT3||''||GL.SEGMENT4||''||GL.SEGMENT5||''||GL.SEGMENT6 ,
5 , to_char ( mmt . transaction_source_id ) ,
6 , MDSP.SEGMENT1 ,
7 , to_char ( mmt . transaction_source_id ) ,
8 , MKTS.SEGMENT1||''||MKTS.SEGMENT2||''||MKTS.SEGMENT3 ,
9 , to_char ( mmt . transaction_source_id ) ,
10 , to_char ( mmt . transaction_source_id ) ,
11 , to_char ( mmt . transaction_source_id ) ,
12 , MKTS.SEGMENT1||''||MKTS.SEGMENT2||''||MKTS.SEGMENT3 , mmt . transaction_source_name )
Source ,
decode ( cal . accounted_dr ,
NULL , - 1 * ABS ( mmt . primary_quantity ) , ABS ( mmt . primary_quantity ) ) Quantity ,
msi . primary_uom_code Primary_uom ,
round ( cal . rate_or_amount * : P_EXCHANGE_RATE , : cp_ext_precision ) Cost ,
nvl ( nvl ( cal . accounted_dr , - 1 * cal . accounted_cr ) , 0 ) * : P_EXCHANGE_RATE Value ,
cal . ae_line_id
FROM
cst_ae_headers cah ,
mtl_material_transactions mmt ,
mtl_item_categories mic ,
mtl_system_items msi ,
MTL_SALES_ORDERS mkts ,
MTL_GENERIC_DISPOSITIONS mdsp ,
gl_code_combinations gl ,
mtl_transaction_types mtt ,
cst_ae_lines cal ,
gl_code_combinations gcc
WHERE
cah . ae_header_id = cal . ae_header_id AND
cah . accounting_event_id = mmt . transaction_id AND
cah . acct_event_source_table = 'MMT' AND
cal . code_combination_id = gcc . code_combination_id AND
mmt . inventory_item_id = msi . inventory_item_id AND
mmt . organization_id = msi . organization_id AND
mmt . organization_id = nvl ( mmt . owning_organization_id , mmt . organization_id ) AND
nvl ( mmt . owning_tp_type , 2 ) = 2 AND
cah . cost_group_id = : P_cost_group_id AND
cah . cost_type_id = : P_cost_type_id AND
cah . period_id = : P_period_id AND
GCC.SEGMENT1 = '0731' AND
GCC.SEGMENT2 BETWEEN '75000' AND '95999' AND
GCC.SEGMENT3 BETWEEN '00000' AND '99999' AND
GCC.SEGMENT4 BETWEEN '00000' AND '99999' AND
GCC.SEGMENT5 = '0000' AND
GCC.SEGMENT6 = '00000' AND 1 = 1 AND 1 = 1 AND
mmt.transaction_type_id = mtt.transaction_type_id AND
mmt.inventory_item_id = mic.inventory_item_id AND
mmt.organization_id = mic.organization_id AND
mic.category_set_id = 1 AND
( mmt . transaction_source_type_id = : P_source_type_id OR : P_source_type_id IS NULL ) AND
( mmt . transaction_type_id = : P_txn_type_id OR : P_txn_type_id IS NULL ) AND
mmt . transaction_source_id = mkts . sales_order_id (+) AND
mmt . transaction_source_id = mdsp . disposition_id (+) AND
mmt . transaction_source_id = gl . code_combination_id (+)
UNION ALL
SELECT
rt . transaction_date Txn_date ,
GCC.SEGMENT1||'
'||GCC.SEGMENT2||'
'||GCC.SEGMENT3||'
'||GCC.SEGMENT4||'
'||GCC.SEGMENT5||'
'||GCC.SEGMENT6 C_acct_flex1 ,
MSI.SEGMENT1 C_item_flex1 ,
msi . description Description ,
rt.transaction_type Type ,
0 Type_id ,
0 Org_id ,
null Source ,
decode ( rt . po_distribution_id ,
NULL , decode ( cal . accounted_dr , NULL , - 1 * ABS ( rt . primary_quantity ) ,
ABS ( rt . primary_quantity ) ) * POD . QUANTITY_ORDERED / POLL . QUANTITY , decode ( cal . accounted_dr , NULL , - 1 * ABS ( rt . primary_quantity ) , ABS ( rt . primary_quantity ) ) ) Quantity , msi . primary_uom_code Primary_uom , round ( cal . rate_or_amount * : P_EXCHANGE_RATE , : cp_ext_precision ) Cost , nvl ( nvl ( cal . accounted_dr , - 1 * cal . accounted_cr ) , 0 ) * : P_EXCHANGE_RATE Value , cal . ae_line_id
FROM
cst_ae_headers cah ,
cst_ae_lines cal ,
mtl_system_items_vl msi ,
rcv_transactions rt ,
rcv_shipment_lines rsl ,
gl_code_combinations gcc ,
po_distributions_all pod ,
po_line_locations_all poll ,
mtl_item_categories mic
WHERE
pod . line_location_id = poll . line_location_id and
poll . line_location_id = rt . po_line_location_id and
pod . po_distribution_id = nvl ( rt . po_distribution_id , pod . po_distribution_id ) and
pod . po_distribution_id = cal . po_distribution_id and
cah . ae_header_id = cal . ae_header_id AND
cah . accounting_event_id = rt . transaction_id AND
cah . acct_event_source_table = 'RT' AND
cal . code_combination_id = gcc . code_combination_id AND
rt . shipment_line_id = rsl . shipment_line_id AND
NVL ( rt . consigned_flag , 'N' ) = 'N' AND
rsl . item_id = msi . inventory_item_id (+) AND
cah . organization_id = msi . organization_id AND
cah . cost_group_id = : P_cost_group_id AND
cah . cost_type_id = : P_cost_type_id AND
cah . period_id = : P_period_id AND
GCC.SEGMENT1 = '0731' AND
GCC.SEGMENT2 BETWEEN '75000' AND '95999' AND
GCC.SEGMENT3 BETWEEN '00000' AND '99999' AND
GCC.SEGMENT4 BETWEEN '00000' AND '99999' AND
GCC.SEGMENT5 = '0000' AND
GCC.SEGMENT6 = '00000' AND 1 = 1 AND 1 = 1 AND
rsl.item_id = mic.inventory_item_id
AND cah.organization_id = mic.organization_id
AND mic.category_set_id = 1 and 1 = 1
UNION ALL
SELECT rae . transaction_date Txn_date , GCC.SEGMENT1||'
'||GCC.SEGMENT2||'
'||GCC.SEGMENT3||'
'||GCC.SEGMENT4||'
'||GCC.SEGMENT5||'
'||GCC.SEGMENT6 C_acct_flex1 ,
MSI.SEGMENT1 C_item_flex1 ,
msi . description Description ,
decode ( : P_type_option ,
1 , rt . source_document_code , decode ( rae . event_type_id , 18 , raet . description , 19 , raet . description , 20 , raet . description , raet . event_type_name ) ) Type ,
0 Type_id , 0 Org_id , null Source , decode ( cal . accounted_dr , NULL , - 1 * ABS ( rae . primary_quantity ) , ABS ( rae . primary_quantity ) ) Quantity ,
msi . primary_uom_code Primary_uom ,
round ( cal . rate_or_amount * : P_EXCHANGE_RATE , : cp_ext_precision ) Cost ,
nvl ( nvl ( cal . accounted_dr , - 1 * cal . accounted_cr ) , 0 ) * : P_EXCHANGE_RATE Value ,
cal . ae_line_id
FROM
cst_ae_headers cah ,
cst_ae_lines cal ,
mtl_system_items msi ,
rcv_transactions rt ,
rcv_accounting_events rae ,
rcv_shipment_lines rsl ,
gl_code_combinations gcc ,
rcv_Accounting_event_types raet ,
mtl_item_categories mic
WHERE
cah . ae_header_id = cal . ae_header_id AND
cah . accounting_event_id = rae . accounting_event_id and
rae . rcv_transaction_id = rt . transaction_id AND
cah . acct_event_source_table = 'RAE' AND
cal . code_combination_id = gcc . code_combination_id AND
rt . shipment_line_id = rsl . shipment_line_id AND
NVL ( rt . consigned_flag , 'N' ) = 'N' AND
rsl . item_id = msi . inventory_item_id (+) AND
cah . organization_id = msi . organization_id AND
cah . cost_group_id = : P_cost_group_id AND
cah . cost_type_id = : P_cost_type_id AND
cah . period_id = : P_period_id AND
raet . event_type_id = rae . event_type_id AND
GCC.SEGMENT1 = '0731' AND
GCC.SEGMENT2 BETWEEN '75000' AND '95999'
AND GCC.SEGMENT3 BETWEEN '00000' AND '99999'
AND GCC.SEGMENT4 BETWEEN '00000' AND '99999'
AND GCC.SEGMENT5 = '0000'
AND GCC.SEGMENT6 = '00000' AND 1 = 1 AND 1 = 1
AND rsl.item_id = mic.inventory_item_id
AND cah.organization_id = mic.organization_id
AND mic.category_set_id = 1
ORDER BY 2 asc , 1 asc , 3 asc , 5 asc , 7 asc , 8 asc , 9 asc , 11 asc