| Attached Files |  query [^] (6,120 bytes) 2018-10-29 18:27 [Show Content] [Hide Content]SELECT ( CASE
WHEN B.m_inoutline_id IS NULL THEN A.qty
ELSE B.qty
END ) AS ID,
A.c_uom_id,
uom.uomsymbol,
A.m_product_id,
Ad_column_identifier(To_char('M_Product'), To_char(A.m_product_id),
To_char(
'en_US')) AS NAME,
A.c_orderline_id,
A.line,
uom.stdprecision AS stdprecision,
cur.priceprecision,
B.m_inoutline_id,
A.description,
( CASE
WHEN B.m_inoutline_id IS NULL THEN A.quantityorder
ELSE B.quantityorder
END ) AS quantityOrder,
( CASE
WHEN B.m_inoutline_id IS NULL THEN A.m_product_uom_id
ELSE B.m_product_uom_id
END ) AS M_Product_UOM_ID,
A.m_attributesetinstance_id,
A.ad_org_id,
A.taxbaseamt,
A.a_asset_id,
A.c_project_id,
A.c_costcenter_id,
A.user1_id,
A.user2_id,
A.explode,
'Y' AS isOrder
FROM c_currency cur,
c_uom uom,
m_product p,
(SELECT COALESCE(l.a_asset_id, o.a_asset_id) AS A_Asset_ID,
COALESCE(l.c_project_id, o.c_project_id) AS C_Project_ID,
COALESCE(l.c_costcenter_id, o.c_costcenter_id) AS C_Costcenter_ID
,
COALESCE(l.user1_id, o.user1_id) AS
User1_ID,
COALESCE(l.user2_id, o.user2_id) AS User2_ID,
l.explode,
l.ad_org_id,
l.c_currency_id,
l.c_uom_id,
l.m_product_id,
l.line,
l.description,
l.c_orderline_id,
( l.qtyordered - Sum(COALESCE(mp.qty, 0)) - COALESCE((SELECT Sum(
COALESCE(CI.qtyinvoiced, 0)
)
FROM c_orderline CO
LEFT JOIN c_invoiceline CI
ON CI.c_orderline_id =
CO.c_orderline_id
WHERE
CI.c_invoice_id = '1660DF7FB4B94F818C45C3FC6CEE81B5'
AND
CO.c_orderline_id = l.c_orderline_id
GROUP BY CI.c_orderline_id,
CO.qtyordered), 0) ) AS QTY,
l.quantityorder * C_divide((
l.qtyordered - Sum(COALESCE(mp.qty, 0))
- COALESCE(
(SELECT Sum(
COALESCE(CI.qtyinvoiced,
0))
FROM c_orderline CO
LEFT JOIN c_invoiceline CI
ON CI.c_orderline_id =
CO.c_orderline_id
WHERE
CI.c_invoice_id = '1660DF7FB4B94F818C45C3FC6CEE81B5'
AND CO.c_orderline_id = l.c_orderline_id
GROUP BY CI.c_orderline_id,
CO.qtyordered), 0) ), l.qtyordered) AS quantityOrder,
l.m_product_uom_id,
l.m_attributesetinstance_id,
l.taxbaseamt
FROM c_orderline l
LEFT JOIN m_matchpo mp
ON l.c_orderline_id = mp.c_orderline_id
AND mp.c_invoiceline_id IS NOT NULL
LEFT JOIN c_order o
ON l.c_order_id = o.c_order_id
GROUP BY l.ad_org_id,
l.c_currency_id,
l.c_uom_id,
l.m_product_id,
l.line,
l.description,
l.c_orderline_id,
l.qtyordered,
l.quantityorder,
l.m_product_uom_id,
l.m_attributesetinstance_id,
l.taxbaseamt,
COALESCE(l.a_asset_id, o.a_asset_id),
COALESCE(l.c_project_id, o.c_project_id),
COALESCE(l.c_costcenter_id, o.c_costcenter_id),
COALESCE(l.user1_id, o.user1_id),
COALESCE(l.user2_id, o.user2_id),
l.explode) A
LEFT JOIN (SELECT il.m_inoutline_id,
il.c_orderline_id,
( il.movementqty - Sum(COALESCE(mi.qty, 0)) )
AS QTY,
il.quantityorder *
C_divide(( il.movementqty - Sum(COALESCE(mi.qty, 0)) ),
( il.movementqty )) AS
quantityOrder,
il.m_product_uom_id
FROM m_inoutline il
LEFT JOIN m_matchinv mi
ON il.m_inoutline_id = mi.m_inoutline_id,
m_inout io
WHERE il.c_orderline_id IS NOT NULL
AND io.m_inout_id = il.m_inout_id
AND io.processed = 'Y'
GROUP BY il.m_inoutline_id,
il.c_orderline_id,
il.movementqty,
il.quantityorder,
il.m_product_uom_id
HAVING ( il.movementqty - Sum(COALESCE(mi.qty, 0)) ) <> 0) B
ON A.c_orderline_id = B.c_orderline_id
WHERE A.c_uom_id = uom.c_uom_id
AND cur.c_currency_id = A.c_currency_id
AND A.m_product_id = p.m_product_id
AND A.c_orderline_id IN ( '632B1284EB114531A8FEAEACE488C0F3',
'C63CC19F80564EFFAE6901A556906DB5',
'E48F5E01236B4C52892E05E8EDE7B01B'
)
ORDER BY A.line
queryPlan [^] (10,956 bytes) 2018-10-29 18:27 [Show Content] [Hide Content]
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------
Sort (cost=3979787.07..3980005.96 rows=87555 width=891) (actual time=96252.680..96252.681 rows=3 loops=1)
Sort Key: l.line
Sort Method: quicksort Memory: 25kB
-> Hash Right Join (cost=2061843.15..3952442.08 rows=87555 width=891) (actual time=96251.416..96252.652 rows=3 loops=1)
Hash Cond: ((il.c_orderline_id)::text = (l.c_orderline_id)::text)
-> GroupAggregate (cost=2061754.19..3827441.55 rows=5836983 width=190) (actual time=46446.330..95298.559 rows=5492026 loops=1)
Group Key: il.m_inoutline_id, il.c_orderline_id, il.movementqty, il.quantityorder, il.m_product_uom_id
Filter: ((il.movementqty - sum(COALESCE(mi.qty, '0'::numeric))) <> '0'::numeric)
Rows Removed by Filter: 341827
-> Sort (cost=2061754.19..2076346.65 rows=5836983 width=190) (actual time=46446.216..61119.425 rows=5835586 loops=1)
Sort Key: il.m_inoutline_id, il.c_orderline_id, il.movementqty, il.quantityorder, il.m_product_uom_id
Sort Method: external merge Disk: 465160kB
-> Hash Left Join (cost=24233.12..1090265.69 rows=5836983 width=190) (actual time=303.079..15344.169 rows=5835586 loops=1)
Hash Cond: ((il.m_inoutline_id)::text = (mi.m_inoutline_id)::text)
-> Merge Join (cost=8.39..799947.39 rows=5836983 width=185) (actual time=0.016..12328.700 rows=5833853 loops=1)
Merge Cond: ((io.m_inout_id)::text = (il.m_inout_id)::text)
-> Index Scan using m_inout_key on m_inout io (cost=0.43..162901.99 rows=1462950 width=33) (actual time=0.008..2434.400 rows=1413578 loops=1)
Filter: (processed = 'Y'::bpchar)
Rows Removed by Filter: 1207
-> Index Scan using m_inoutline_inout on m_inoutline il (cost=0.56..560416.13 rows=5841851 width=218) (actual time=0.006..6924.197 rows=5837430 loops=1)
Filter: (c_orderline_id IS NOT NULL)
Rows Removed by Filter: 582
-> Hash (cost=19933.77..19933.77 rows=343277 width=38) (actual time=301.142..301.142 rows=345976 loops=1)
Buckets: 524288 Batches: 1 Memory Usage: 27814kB
-> Seq Scan on m_matchinv mi (cost=0.00..19933.77 rows=343277 width=38) (actual time=0.013..194.470 rows=345976 loops=1)
-> Hash (cost=88.92..88.92 rows=3 width=712) (actual time=0.896..0.896 rows=3 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Nested Loop (cost=30.66..88.92 rows=3 width=712) (actual time=0.654..0.891 rows=3 loops=1)
-> Hash Join (cost=30.24..83.19 rows=3 width=712) (actual time=0.605..0.792 rows=3 loops=1)
Hash Cond: ((l.c_uom_id)::text = (uom.c_uom_id)::text)
-> Nested Loop (cost=28.28..81.18 rows=3 width=704) (actual time=0.547..0.733 rows=3 loops=1)
-> HashAggregate (cost=28.13..76.23 rows=3 width=1091) (actual time=0.531..0.709 rows=3 loops=1)
Group Key: l.ad_org_id, l.c_currency_id, l.c_uom_id, l.m_product_id, l.line, l.description, l.c_orderline_id, l.qtyordered, l.quantityorder, l.m_product_uom_id, l.m_attributesetinstance_id, l.taxbaseamt, COALESCE(l.a_asset_id, o.a_asset_id), COALESCE(l.c_project_id, o.c_project_id), COALESCE(l.c_costcenter_id, o.c_costcenter_id), COALESCE(l.user1_id, o.user1_id), COALESCE(l.user2_id, o.user2_id), l.explode
-> Nested Loop Left Join (cost=1.54..27.98 rows=3 width=1091) (actual time=0.119..0.238 rows=3 loops=1)
-> Nested Loop Left Join (cost=0.99..19.64 rows=3 width=714) (actual time=0.077..0.182 rows=3 loops=1)
-> Index Scan using c_orderline_key on c_orderline l (cost=0.56..8.33 rows=3 width=709) (actual time=0.039..0.091 rows=3 loops=1)
Index Cond: ((c_orderline_id)::text = ANY ('{632B1284EB114531A8FEAEACE488C0F3,C63CC19F80564EFFAE6901A556906DB5,E48F5E01236B4C52892E05E8EDE7B01B}'::text[]))
-> Index Scan using em_cdms_matchpo_orderline on m_matchpo mp (cost=0.42..3.76 rows=1 width=38) (actual time=0.027..0.027 rows=0 loops=3)
Index Cond: ((l.c_orderline_id)::text = (c_orderline_id)::text)
Filter: (c_invoiceline_id IS NOT NULL)
-> Index Scan using c_order_key on c_order o (cost=0.55..2.77 rows=1 width=443) (actual time=0.016..0.016 rows=1 loops=3)
Index Cond: ((l.c_order_id)::text = (c_order_id)::text)
SubPlan 1
-> HashAggregate (cost=7.87..7.88 rows=1 width=42) (actual time=0.067..0.067 rows=1 loops=3)
Group Key: ci.c_orderline_id, co.qtyordered
-> Nested Loop (cost=4.52..7.86 rows=1 width=42) (actual time=0.063..0.064 rows=1 loops=3)
-> Index Scan using c_orderline_key on c_orderline co (cost=0.56..2.78 rows=1 width=37) (actual time=0.008..0.008 rows=1 loops=3)
Index Cond: ((c_orderline_id)::text = (l.c_orderline_id)::text)
-> Bitmap Heap Scan on c_invoiceline ci (cost=3.96..5.07 rows=1 width=38) (actual time=0.052..0.053 rows=1 loops=3)
Recheck Cond: (((c_orderline_id)::text = (l.c_orderline_id)::text) AND ((c_invoice_id)::text = '1660DF7FB4B94F818C45C3FC6CEE81B5'::text))
Heap Blocks: exact=3
-> BitmapAnd (cost=3.96..3.96 rows=1 width=0) (actual time=0.047..0.047 rows=0 loops=3)
-> Bitmap Index Scan on c_invoiceline_orderline (cost=0.00..1.71 rows=7 width=0) (actual time=0.028..0.028 rows=1 loops=3)
Index Cond: ((c_orderline_id)::text = (l.c_orderline_id)::text)
-> Bitmap Index Scan on c_invoiceline_invoice (cost=0.00..2.00 rows=45 width=0) (actual time=0.018..0.018 rows=3 loops=3)
Index Cond: ((c_invoice_id)::text = '1660DF7FB4B94F818C45C3FC6CEE81B5'::text)
SubPlan 2
-> HashAggregate (cost=7.87..7.88 rows=1 width=42) (actual time=0.027..0.027 rows=1 loops=3)
Group Key: ci_1.c_orderline_id, co_1.qtyordered
-> Nested Loop (cost=4.52..7.86 rows=1 width=42) (actual time=0.024..0.025 rows=1 loops=3)
-> Index Scan using c_orderline_key on c_orderline co_1 (cost=0.56..2.78 rows=1 width=37) (actual time=0.006..0.006 rows=1 loops=3)
Index Cond: ((c_orderline_id)::text = (l.c_orderline_id)::text)
-> Bitmap Heap Scan on c_invoiceline ci_1 (cost=3.96..5.07 rows=1 width=38) (actual time=0.015..0.015 rows=1 loops=3)
Recheck Cond: (((c_orderline_id)::text = (l.c_orderline_id)::text) AND ((c_invoice_id)::text = '1660DF7FB4B94F818C45C3FC6CEE81B5'::text))
Heap Blocks: exact=3
-> BitmapAnd (cost=3.96..3.96 rows=1 width=0) (actual time=0.015..0.015 rows=0 loops=3)
-> Bitmap Index Scan on c_invoiceline_orderline (cost=0.00..1.71 rows=7 width=0) (actual time=0.006..0.006 rows=1 loops=3)
Index Cond: ((c_orderline_id)::text = (l.c_orderline_id)::text)
-> Bitmap Index Scan on c_invoiceline_invoice (cost=0.00..2.00 rows=45 width=0) (actual time=0.007..0.007 rows=3 loops=3)
Index Cond: ((c_invoice_id)::text = '1660DF7FB4B94F818C45C3FC6CEE81B5'::text)
-> Index Scan using c_currency_key on c_currency cur (cost=0.14..1.63 rows=1 width=8) (actual time=0.006..0.007 rows=1 loops=3)
Index Cond: ((c_currency_id)::text = (l.c_currency_id)::text)
-> Hash (cost=1.43..1.43 rows=43 width=34) (actual time=0.039..0.039 rows=43 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 11kB
-> Seq Scan on c_uom uom (cost=0.00..1.43 rows=43 width=34) (actual time=0.009..0.020 rows=43 loops=1)
-> Index Only Scan using m_product_key on m_product p (cost=0.42..1.90 rows=1 width=33) (actual time=0.030..0.031 rows=1 loops=3)
Index Cond: (m_product_id = (l.m_product_id)::text)
Heap Fetches: 0
Planning time: 12.625 ms
Execution time: 96308.536 ms
(80 rows)
(END)
|
Relationships [ Relation Graph ]
[ Dependency Graph ]
Relationships 