Project:
| View Issue Details[ Jump to Notes ] | [ Issue History ] [ Print ] | |||||||
| ID | ||||||||
| 0037516 | ||||||||
| Type | Category | Severity | Reproducibility | Date Submitted | Last Update | |||
| defect | [Openbravo ERP] 02. Master data management | major | always | 2017-12-19 17:08 | 2018-02-22 18:18 | |||
| Reporter | JONHM | View Status | public | |||||
| Assigned To | AtulOpenbravo | |||||||
| Priority | high | Resolution | fixed | Fixed in Version | 3.0PR18Q2 | |||
| Status | closed | Fix in branch | Fixed in SCM revision | 385dc5c17396 | ||||
| Projection | none | ETA | none | Target Version | ||||
| OS | Any | Database | Any | Java version | ||||
| OS Version | Database version | Ant version | ||||||
| Product Version | SCM revision | |||||||
| Review Assigned To | dmiguelez | |||||||
| Web browser | ||||||||
| Modules | Core | |||||||
| Regression level | ||||||||
| Regression date | ||||||||
| Regression introduced in release | ||||||||
| Regression introduced by commit | ||||||||
| Triggers an Emergency Pack | No | |||||||
| Summary | 0037516: Performance issue in Orders Awaiting Delivery Report | |||||||
| Description | There is a performance issue in Orders Awaiting Delivery Report on environments with high amount of data. | |||||||
| Steps To Reproduce | Login on the environment provided by support: - Open Orders Awaiting Delivery Report and set: * From date: 10-08-2017 * To date: 13-09-2017 * Org: BAPS Shayona - Robbinsville It take about 90 seconds to finish | |||||||
| Tags | No tags attached. | |||||||
| Attached Files |  query.sql [^] (18,898 bytes) 2017-12-19 17:19 query_plan [^] (16,463 bytes) 2017-12-19 17:19 [Show Content] [Hide Content]'Sort (cost=310.53..310.55 rows=6 width=582)'
' Sort Key: org.name, bp.name, o.documentno'
' -> Nested Loop Semi Join (cost=49.90..310.46 rows=6 width=582)'
' Join Filter: ((o.c_order_id)::text = (ordl.c_order_id)::text)'
' -> Nested Loop Left Join (cost=49.61..87.75 rows=6 width=648)'
' -> Nested Loop (cost=49.47..86.71 rows=6 width=644)'
' -> Nested Loop (cost=49.33..85.69 rows=6 width=640)'
' -> Hash Left Join (cost=49.19..84.66 rows=6 width=625)'
' Hash Cond: ((o.delivery_location_id)::text = (dloc.c_bpartner_location_id)::text)'
' -> Nested Loop (cost=47.63..83.06 rows=6 width=675)'
' -> Nested Loop (cost=43.30..56.97 rows=1 width=334)'
' Join Filter: ((o.c_bpartner_location_id)::text = (bpadd.c_bpartner_location_id)::text)'
' -> Nested Loop (cost=43.30..55.41 rows=1 width=335)'
' Join Filter: ((o.c_bpartner_id)::text = (bp.c_bpartner_id)::text)'
' -> Nested Loop (cost=43.30..52.85 rows=1 width=346)'
' Join Filter: ((o.deliveryrule)::text = (ad_ref_list.value)::text)'
' -> Nested Loop (cost=0.28..9.69 rows=1 width=316)'
' -> Index Scan using c_order_dateordered_id on c_order o (cost=0.28..8.58 rows=1 width=331)'
' Index Cond: ((dateordered >= '2017-08-10 00:00:00'::timestamp without time zone) AND (dateordered < '2017-09-13 00:00:00'::timestamp without time zone))'
' Filter: (((ad_client_id)::text = ANY ('{0,8B72EA2C5B444DE291849E37F2F86DBA}'::text[])) AND ((ad_org_id)::text = 'D9CC311BEE7B435EA13ED5359665FA1C'::text) AND ((docstatus)::text = 'CO'::text) AND (issotrx = 'Y'::bpchar) AND ((ad_org_id)::text = ANY ('{0,306D3B3010FB47C8830A3CD6544CB71D,A25CF246A7B94A4A92B0C0D74878D227,CBC7D0B4DEB04192ABA391687EAE0F61,3F52F6C378274F8EA1A9A1A10D15E21E,C14CFAB09B594263AB5C99A82937E427,E453C89517DD4FB3A2EBB9EF79808E3A,E3ADD08E7A344BC4AA0E5271A00EE1AB,48C21BE2F1A449A8AD397A7D1B3F2C18,318A3152361C46959E07EC1393D1C7CE,E3353FB57805410D9515EFA23737C399,AB40EE591DBE44F093E4C5B606D354C6,A31788F217B448728321DDB7800D1808,3CB045196B7547DFAE80DC60193B687A,362AAC83D0D94AB5B802E2FDDA49A8E0,5F7B6E84D15843E69C4ED7D97444D478,40C5CBF3762A4BDA85058E1D837E03E4,8BD961208E0244D99F31700FFAC12341,6D3322D0CEFE40FBB5ADCE995C9A5E18,D2FF042B470147AB81BC7B993D63214C,B31B3BFBBE154ACB93897A42C9FF351C,2DE44FC9B33847FEB69C2908E1FBC1C2,E7F4AC1093684F15B5954A5818076653,3A1097E45C4243F6BBE926EA3D947E3F,5F0B6579C951489A936A683A426A132E,9FC6900EC6D04B55B3F831FA09121E32,D46CCA95EB5542A5973BAEEF312F6A0D,EC9580A5DE2F4AADA9ECC42D34702571,25E8B7AA299A492D9EF6B2B99B864419,316DC99A97B844CDBE9E20003A16C0F3,5F49108E9AA24990A0EBC198DF40CE54,5305CF0416744873BB2DF46386354A31,9A3DA17921AD4C52A79C2359B0BBC981,BADC1A86EB20423FA958DE0B65CC381F,A8B01CD45FDA490AA2FBF2A857249345,63022951ABB74A86B790E95F52E40F58,FAF808E5C3F349248230456BFC11C6B8,53D2871ECE304BB09604AF4D0FF3D7F1,A892B2A07E2643C1845C99F4E687862B,D8CF30DCA6AB43F69021C95005E6BD13,D9AD12FC3BBA499DA29BDB45ADCE6D5A,FB5802AE5E284CCE8B1AEF307089595B,A5112471313A4F0199F10514E5DF2BBE,4B67D434A6DD44A69A1EAE1D74C83562,18B60CB5A6174D169BA790216E002B4D,7BCCA05C769C44E395EDA1AC27A56A03,E6EAD13FC35445AE9BD81901F0624172,6F67E7FC52C444CFA0D7B7E95F5FEDB8,158F30646EE949ABB8B637F09BC79AED,327EF8CB2F564BA99F20687CD8AA35F1,53479AE940E744B5B3624BE316062075,325EDF84AA9C4395AAD33A14F59BE6A1,E43A0CFA32F044C6B381E8771ED0DD7C,2F3F3D5938D84A67811FBF8A27162690,7EBDA22E5A664C56A56C28C3D26DC092,E70FD5E6A6B04477A2200D9C43E78A24,98C02C1EB0224902AAE5171D347AC76B,1CDFEC4B6E2140D6843D25ADB328128E,2653FE69D1DE43C8ADC3D1E20505B430,0C6AAE6CBB6842739797E8E20048E91A,D96638F06E794FA5825D23E36EFFB9D1,8EA4BA1E749B4C388AB7A8F976788D56,6A2500E55D034D7B8EB13D06878A1D9B,A0CA35EECA0F42229D8410545ACB68E3,C34B8B66DDF244B8A3A908C53EBB16F6,1FCF5E2FDCB2450F806A675AB07193A7,F788E977C5B64ED7A85169BA5236589E,E4B776F73C724DD7A5B323E0B2E841BA,08189B9A7D844CBCAFE14A7FB9B96913,B147B3EC7A8D42A4A608053D0BBDA16A,D47DAC0B366F483BAA194C5343D319C0,E279CA67D8CA43F5AEC3F4CA84214406,CCBEF4528C9A42BD9DB5DD3BF8173728,55477A2045D3473B8F4206A86698E6D2,5FAE4EEA12BD4A688A15C8E37EE3EBD7,67D855B636114782AD040B50B7228692,103BEA21E8E542D2A034A31770ADE05F,AF4BA739CCEA492B833CD43A77A33C6D,66FE8861B05B4347A044EDFF1014A288,092B255D7EC74A3FA5525FC569B3F71E,4D9E8560D45A460480F4A1149D08606E,E058BB072CFA44B2B6C100566EAADEED,319E8635613743318EA26DBD03BC882C,19F0E53F8AA44F4A8C0B61C1A747688D,2133FE47A7324CDA80EE68FF45E61E05,8FBFE61810E4454F8E1246C7981F1551,7A31597C9469404EB6F07F8DDDC6DE3E,70361055E36D43EAABBE36524BCEF26D,4F3851920A7449BA903F88A3136A7F17,87652CFFDAD949FABD8B9C568435E3EF,2D104264E8E74EA58549EE603BCD8AEA,A6D118067C8B403985AD754E3AFD075A,A67CC53B0AD04B07B9419AE42A731707,9C6267A300964551B8EB72221E0E6D33,4531AF4A7F3A422DA80ABA2B6B45E657,309E9A1B24C6496B9D79AC17C0886209,993AA85D1C7747E09823747874BCC4F3,469016B267C84C208442E2696C9D400A,1B693F0BE60F4C56A0BA89BDE1DC55B1,3BA5DBAB24F6422580F083DA3776FD08,3794C31D03A643EE928C8A5358E12853,76A2DDBEC2224AEDBF71A320A6667987,DFE5315B13BB4F8F821774FE4A49D46C,78F45D18B38048E9901DE1F0102384DC,C984287036394AF6943C8F93B78E4506,F71E03783E794EA6B72CF90DF5B7DBA5,D0B0F660DF9A4F609C00BD619EBB0F51,AD55542CD1F54C208E7F7B58EC65C008,2432C0FB102D4451908EDA04A6642190,535C96CDB35F49BB801321F522FB2B96,C853ABFC80E74DC4BB82967F029D42E2,2324EF6B39E54D17BACC2684CF4F16AA,E340A0C8A88C443BB8FAB9FCDF844FD8,6D005AF435DA48C183ACA2B0E81EDD8F,2C743FE937A9485492069A2DFD5C1503,C82D39BA61EE465E95EB5D9C16B5A5F8,975DDFA55BAD4CB9AF9B8435DD12D7A0,8D2AAA7E604C47DE81535132E0E6C48B,2644B64E7DC843C1A49EEED5C9AD8861,C4E159A41C614709867FD47F8455B218,CD18CEA3A5F6443E9B47DA2076F03C03,09868DD506E044AEB71468EA45014280,FFC21A886B7B442A8939CF67950E815A,783B64B7E16E4469BDAD56E05AF5403B,806B5D2219004E21AD2EA379FE966670,0598DB73327A4B4CBE02684086122065,16ACC38D0A22432E80DD09FF717B6224,EFB204879DDD41D396B2FB3A5E029323,C774409334DC43FDBC3FE3687D9DDE69,FF0C52506A3945EFB25591C780D8EB98,5D047A3692B840F58CBCDA2F54D8CF7D,82BBDFEC32274B72AB27A8E1D453F514,2ED7CF7C8C574B4BB9FA8AEA512D4D63,FB83A1D3A97D4CE099FC66AB24FBFFCA,B1D84FD139D84CB3AAB1A03CDFD6B579,FA91DD8960124653AE9B01E2F8655687,A64FB0CB369043F29A1C20EC1E613F8A,8280E4BDF99642E7A5FBFB6427D236FD,BFBAC410E31D4DE3B2CB6CAA31341CEA,DFCC55A6294C425F9E9A2776F0E0DC65,182152C8710A463CA2D015AB770F4193,31825820459F42EEADBA2E15A3F1D1FA,A1F5B06F460F4F67BBBC6AC3FE27AE85,DF65A343ABA543CF88B7A6DCD52380A3,D9CC311BEE7B435EA13ED5359665FA1C,A23C7BE09AA344D0BB7E8ACECCF84B9B,092895CF76EA44FABC262980D8A156D9,62CD82E1A1024DC8AB12DB0E4338674D,617419D69ADC49F0A5E84CE7807233EE,64FF896979C74E799DDE3F383812E4F1,CE80F7CD48AD494BA9C51D29CF2A8773,D31CC43D89D242EFBAB45C84ABCE1FF4,4F376722D18F42DBB0E86866B7017F50,F0D1C82A28DE4E0995FFD646E7A5A24D,D4D7E44F737644069A7C8FFE2A577E7A,DB90663FCEC343B6BDF99BB21C63C02F,6A3B7B0E57DD48CEACE2981F1C7C81ED,04FFD14E863C427B9E95FDD273059339,7A3EBF715DBB43258B29BFCBA7E8F8ED,71329097F6AD4455A6193BA551993DF2,45F69D028C46421C9B2897B1EB3EDD4E,26654E04FDD4405CBDB9334070DC2026,AB585C50B9934FEE8D6DCC141AECA3EE,2D288394D33F449F8C5BE09A23B5F97B,288B1466C1024455A5C80DE9944C275E,16303BD6F5C74355A3E6ED4DB60B8A67,469ACE6358FB4C0EA58058FA823EAFEC,0D796DFE59D0443E93A2CA46F607886E,790BA1D65CA34B72AFA6ED87355A6337,9228CFE725604EA0BF01C676B5C5E72A,BF77D03787D84C9888FCFBD012BFDA02,043D618911174C8D85F2B97407B3B652,08537956D91D40B69AE040254CAF341E,DDDD4C61896142B78A1EA29105A082AD,BDA0ECE46C39499491FD6149E787C9F4,96B08A8DB32C49E2A809E156DB592842,D3E00B67031D4A309FE917292CF1F719,77D3336DC2214AE6A7304A23FD35A805,F04CEDB8E1FC4341958071416F24AD05,2484267157304B488B71329DDB3D8421,D76C8E0696EA4C62B38DBD29ACB602E7,F3CE6131A1674567ADC67FA3FD764B7E,6B6A074DD75A415C958AC44CA5755B60,38D225ED623B4E13B78C2C917579E440,6A247D9E877C4A5689C8A90BCBD8F957,537C0C44F0C740CA917101B6413C9AB5,C851613D607F47F99AAD8516BBA438F4,1955B39FD1884AB180F1F69F45C0B1BE,43C4BA0AFB8B486B9AACBD519E403AD9,1526521C6C704155A625CF21CE913BF4,0917477F7B8B48228D2A13D8B5E5D0C7,D1331BB2694840FD8B38D5857E4D8C05,317DAAB1CF49470CA3FD565D773666DD,943596C335724B139171B9EDA473DA53,9178C2F840EB4CD9B1098A333EBFA992,B8184BF4838A4B4FA7809FDCBB25504E,AEDAF1F20FAF44FC93AB35D2DFD3E5C5,B6E2D1B581464191AD48AFF6AED9B813,C2BAA87926B14FCCB7B6C481EFAA395C,972455AB23EC47A1A2C31B53F5C06E68,8969F75D79B6402BA45B7DF1EA54435D,531F3B4C89EC4BF180B6616F94656FA1,E853EC3DE4244365BE97935652115E35,043739981A1D4C5BBACAC37926B2104C,9D1090AFC1D043B2A8086A4FFF8D31E4,8DF1BD24A1EB4F4F918E75236873A402,00FC5F98BDE047F99C062F6ABD2DCB9B,F5CA78C4CDAC45758F3D3518EB2938C5,6027B0644E5E4A238FBD6CAC1F44DDE3,15F7BFB3DA804535BDD03E216EB851B4,2CD3172D63644694A19E7BE676B63362,696A3525F40E4E01B538CF3E729CB410,68269B60854A41C48A55EC4FB363E5F3,B54CFF4B5FED44A9853F2F6FA3FFC931,11009FD31FA045C3910556DDB72C5817,86B7EAE491FF4025946652B3423A4EB5,C83B8D6D3DBA455B8C4973F68D8E2F59,FD8E0F9DA2124FCB954859AB6F48755F}'::text[])))'
' -> Seq Scan on ad_org org (cost=0.00..1.10 rows=1 width=47)'
' Filter: ((ad_org_id)::text = 'D9CC311BEE7B435EA13ED5359665FA1C'::text)'
' -> HashAggregate (cost=43.03..43.07 rows=4 width=94)'
' -> Append (cost=4.30..42.98 rows=4 width=94)'
' -> Bitmap Heap Scan on ad_ref_list (cost=4.30..13.97 rows=3 width=53)'
' Recheck Cond: ((ad_reference_id)::text = '151'::text)'
' -> Bitmap Index Scan on ad_ref_list_value (cost=0.00..4.30 rows=3 width=0)'
' Index Cond: ((ad_reference_id)::text = '151'::text)'
' -> Nested Loop (cost=0.70..28.97 rows=1 width=219)'
' -> Nested Loop (cost=0.70..25.22 rows=1 width=219)'
' -> Nested Loop (cost=0.42..16.91 rows=1 width=287)'
' -> Index Scan using ad_ref_list_trl_ref_list_la_un on ad_ref_list_trl (cost=0.14..8.60 rows=1 width=248)'
' Index Cond: ((ad_language)::text = 'en_US'::text)'
' -> Index Scan using ad_ref_list_key on ad_ref_list ad_ref_list_1 (cost=0.28..8.30 rows=1 width=39)'
' Index Cond: ((ad_ref_list_id)::text = (ad_ref_list_trl.ad_ref_list_id)::text)'
' Filter: ((ad_reference_id)::text = '151'::text)'
' -> Index Scan using ad_ref_list_key on ad_ref_list ad_ref_list_2 (cost=0.28..8.30 rows=1 width=49)'
' Index Cond: ((ad_ref_list_id)::text = (ad_ref_list_1.ad_ref_list_id)::text)'
' Filter: ((ad_reference_id)::text = '151'::text)'
' -> Seq Scan on ad_language (cost=0.00..3.24 rows=1 width=0)'
' Filter: (issystemlanguage = 'Y'::bpchar)'
' -> Seq Scan on c_bpartner bp (cost=0.00..2.25 rows=25 width=55)'
' -> Seq Scan on c_bpartner_location bpadd (cost=0.00..1.25 rows=25 width=65)'
' -> Bitmap Heap Scan on c_orderline ol (cost=4.33..26.03 rows=6 width=341)'
' Recheck Cond: ((c_order_id)::text = (o.c_order_id)::text)'
' -> Bitmap Index Scan on c_orderline_order (cost=0.00..4.33 rows=6 width=0)'
' Index Cond: ((c_order_id)::text = (o.c_order_id)::text)'
' -> Hash (cost=1.25..1.25 rows=25 width=65)'
' -> Seq Scan on c_bpartner_location dloc (cost=0.00..1.25 rows=25 width=65)'
' -> Index Scan using m_product_key on m_product pr (cost=0.14..0.16 rows=1 width=48)'
' Index Cond: ((m_product_id)::text = (ol.m_product_id)::text)'
' -> Index Scan using c_uom_key on c_uom uo (cost=0.14..0.16 rows=1 width=28)'
' Index Cond: ((c_uom_id)::text = (ol.c_uom_id)::text)'
' -> Index Scan using m_attributesetinstance_key on m_attributesetinstance asi (cost=0.14..0.16 rows=1 width=36)'
' Index Cond: ((ol.m_attributesetinstance_id)::text = (m_attributesetinstance_id)::text)'
' -> Index Scan using c_orderline_order on c_orderline ordl (cost=0.28..0.70 rows=6 width=33)'
' Index Cond: ((c_order_id)::text = (ol.c_order_id)::text)'
' Filter: (qtyordered <> qtydelivered)'
' SubPlan 1'
' -> Aggregate (cost=12.01..12.02 rows=1 width=3)'
' -> Nested Loop (cost=0.00..12.01 rows=1 width=3)'
' Join Filter: ((sd.m_locator_id)::text = (loc.m_locator_id)::text)'
' -> Seq Scan on m_storage_detail sd (cost=0.00..10.76 rows=1 width=36)'
' Filter: (((m_product_id)::text = (ol.m_product_id)::text) AND ((c_uom_id)::text = (ol.c_uom_id)::text) AND ((COALESCE(m_attributesetinstance_id, '0'::character varying))::text = (COALESCE(ol.m_attributesetinstance_id, m_attributesetinstance_id, '0'::character varying))::text) AND ((COALESCE(m_product_uom_id, '-1'::character varying))::text = (COALESCE(ol.m_product_uom_id, '-1'::character varying))::text))'
' -> Seq Scan on m_locator loc (cost=0.00..1.20 rows=4 width=33)'
' Filter: ((m_warehouse_id)::text = (ol.m_warehouse_id)::text)'
' SubPlan 2'
' -> Seq Scan on c_uom (cost=0.00..9.40 rows=1 width=4)'
' Filter: ((c_uom_id)::text = (m_get_default_aum_for_document(ol.m_product_id, o.c_doctype_id))::text)'
' SubPlan 3'
' -> Seq Scan on c_uom c_uom_1 (cost=0.00..1.40 rows=1 width=4)'
' Filter: ((c_uom_id)::text = (ol.c_aum)::text)'
' SubPlan 4'
' -> Aggregate (cost=12.01..12.02 rows=1 width=3)'
' -> Nested Loop (cost=0.00..12.01 rows=1 width=3)'
' Join Filter: ((sd_1.m_locator_id)::text = (loc_1.m_locator_id)::text)'
' -> Seq Scan on m_storage_detail sd_1 (cost=0.00..10.76 rows=1 width=36)'
' Filter: (((m_product_id)::text = (ol.m_product_id)::text) AND ((c_uom_id)::text = (ol.c_uom_id)::text) AND ((COALESCE(m_attributesetinstance_id, '0'::character varying))::text = (COALESCE(ol.m_attributesetinstance_id, m_attributesetinstance_id, '0'::character varying))::text) AND ((COALESCE(m_product_uom_id, '-1'::character varying))::text = (COALESCE(ol.m_product_uom_id, '-1'::character varying))::text))'
' -> Seq Scan on m_locator loc_1 (cost=0.00..1.20 rows=4 width=33)'
' Filter: ((m_warehouse_id)::text = (ol.m_warehouse_id)::text)'
 37516Final_QueryPlan.txt [^] (37,303 bytes) 2018-01-15 09:52 [Show Content] [Hide Content]******************************************************************************************************************************************************
Query
******************************************************************************************************************************************************
SELECT ORGNAME,
BPNAME,
documentno,
poreference,
DATEORDERED,
DATEPROMISED,
DELIVERYRULE,
DELIVERYLOCATION,
PRODNAME,
m_attributesetinstance_id,
ORDEREDQTY,
PENDINGQTY,
STOCKVALUE|| ' ' || UOMSYMBOL AS QTYINSTOCK,
aumqty,
AUM,
(SELECT uomsymbol
FROM c_uom
WHERE c_uom_id = AUM
)
AS AUMSYMBOL,
m_product_id,
ORDEREDVALUE,
PENDINGVALUE,
STOCKVALUE
FROM
( SELECT ORG.NAME
AS
ORGNAME,
BP.NAME
AS BPNAME,
O.documentno,
O.poreference,
To_date(O.dateordered)
AS DATEORDERED,
To_date(O.datepromised)
AS DATEPROMISED,
REFLISTV.NAME
AS DELIVERYRULE,
COALESCE(DLOC.NAME, BPADD.NAME)
AS DELIVERYLOCATION,
PR.NAME
|| ' '
|| COALESCE(To_char(ASI.description), '')
AS PRODNAME,
OL.m_attributesetinstance_id,
OL.qtyordered
|| ' '
|| COALESCE(To_char(UO.uomsymbol), '')
AS ORDEREDQTY,
( OL.qtyordered - OL.qtydelivered )
|| ' '
|| COALESCE(To_char(UO.uomsymbol), '')
AS PENDINGQTY,
COALESCE(To_char(UO.uomsymbol), '')
AS UOMSYMBOL,
OL.aumqty,
( COALESCE (OL.c_aum, M_get_default_aum_for_document(OL.m_product_id,
O.c_doctype_id)) )
AS AUM,
OL.m_product_id,
OL.qtyordered
AS ORDEREDVALUE,
( OL.qtyordered - OL.qtydelivered )
AS PENDINGVALUE,
(SELECT Sum(SD.qtyonhand)
FROM m_storage_detail SD
WHERE SD.m_product_id = OL.m_product_id
AND SD.c_uom_id = OL.c_uom_id
AND COALESCE(SD.m_attributesetinstance_id, '0') =
COALESCE(OL.m_attributesetinstance_id,
SD.m_attributesetinstance_id,
'0')
AND COALESCE(SD.m_product_uom_id, '-1') =
COALESCE(OL.m_product_uom_id, '-1')
AND EXISTS (SELECT 1
FROM m_locator LOC
WHERE LOC.m_locator_id = SD.m_locator_id
AND LOC.m_warehouse_id = OL.m_warehouse_id))
AS
STOCKVALUE
FROM c_order O
JOIN ad_org ORG
ON O.ad_org_id = ORG.ad_org_id
JOIN c_bpartner BP
ON O.c_bpartner_id = BP.c_bpartner_id
JOIN c_bpartner_location BPADD
ON O.c_bpartner_location_id = BPADD.c_bpartner_location_id
JOIN ad_ref_list_v REFLISTV
ON O.deliveryrule = REFLISTV.value
LEFT JOIN c_bpartner_location DLOC
ON O.delivery_location_id = DLOC.c_bpartner_location_id
JOIN c_orderline OL ON O.c_order_id = OL.c_order_id
JOIN m_product PR
ON OL.m_product_id = PR.m_product_id
JOIN c_uom UO
ON OL.c_uom_id = UO.c_uom_id
LEFT JOIN m_attributesetinstance ASI
ON OL.m_attributesetinstance_id = ASI.m_attributesetinstance_id
WHERE O.docstatus = 'CO'
AND O.issotrx = 'Y'
AND REFLISTV.ad_reference_id = '151'
AND REFLISTV.ad_language = 'en_US'
AND O.ad_client_id IN ( '0', '8B72EA2C5B444DE291849E37F2F86DBA' )
AND O.ad_org_id IN ( '0', '306D3B3010FB47C8830A3CD6544CB71D',
'A25CF246A7B94A4A92B0C0D74878D227',
'CBC7D0B4DEB04192ABA391687EAE0F61',
'3F52F6C378274F8EA1A9A1A10D15E21E',
'C14CFAB09B594263AB5C99A82937E427',
'E453C89517DD4FB3A2EBB9EF79808E3A',
'E3ADD08E7A344BC4AA0E5271A00EE1AB',
'48C21BE2F1A449A8AD397A7D1B3F2C18',
'318A3152361C46959E07EC1393D1C7CE',
'E3353FB57805410D9515EFA23737C399',
'AB40EE591DBE44F093E4C5B606D354C6',
'A31788F217B448728321DDB7800D1808',
'3CB045196B7547DFAE80DC60193B687A',
'362AAC83D0D94AB5B802E2FDDA49A8E0',
'5F7B6E84D15843E69C4ED7D97444D478',
'40C5CBF3762A4BDA85058E1D837E03E4',
'8BD961208E0244D99F31700FFAC12341',
'6D3322D0CEFE40FBB5ADCE995C9A5E18',
'D2FF042B470147AB81BC7B993D63214C',
'B31B3BFBBE154ACB93897A42C9FF351C',
'2DE44FC9B33847FEB69C2908E1FBC1C2',
'E7F4AC1093684F15B5954A5818076653',
'3A1097E45C4243F6BBE926EA3D947E3F',
'5F0B6579C951489A936A683A426A132E',
'9FC6900EC6D04B55B3F831FA09121E32',
'D46CCA95EB5542A5973BAEEF312F6A0D',
'EC9580A5DE2F4AADA9ECC42D34702571',
'25E8B7AA299A492D9EF6B2B99B864419',
'316DC99A97B844CDBE9E20003A16C0F3',
'5F49108E9AA24990A0EBC198DF40CE54',
'5305CF0416744873BB2DF46386354A31',
'9A3DA17921AD4C52A79C2359B0BBC981',
'BADC1A86EB20423FA958DE0B65CC381F',
'A8B01CD45FDA490AA2FBF2A857249345',
'63022951ABB74A86B790E95F52E40F58',
'FAF808E5C3F349248230456BFC11C6B8',
'53D2871ECE304BB09604AF4D0FF3D7F1',
'A892B2A07E2643C1845C99F4E687862B',
'D8CF30DCA6AB43F69021C95005E6BD13',
'D9AD12FC3BBA499DA29BDB45ADCE6D5A',
'FB5802AE5E284CCE8B1AEF307089595B',
'A5112471313A4F0199F10514E5DF2BBE',
'4B67D434A6DD44A69A1EAE1D74C83562',
'18B60CB5A6174D169BA790216E002B4D',
'7BCCA05C769C44E395EDA1AC27A56A03',
'E6EAD13FC35445AE9BD81901F0624172',
'6F67E7FC52C444CFA0D7B7E95F5FEDB8',
'158F30646EE949ABB8B637F09BC79AED',
'327EF8CB2F564BA99F20687CD8AA35F1',
'53479AE940E744B5B3624BE316062075',
'325EDF84AA9C4395AAD33A14F59BE6A1',
'E43A0CFA32F044C6B381E8771ED0DD7C',
'2F3F3D5938D84A67811FBF8A27162690',
'7EBDA22E5A664C56A56C28C3D26DC092',
'E70FD5E6A6B04477A2200D9C43E78A24',
'98C02C1EB0224902AAE5171D347AC76B',
'1CDFEC4B6E2140D6843D25ADB328128E',
'2653FE69D1DE43C8ADC3D1E20505B430',
'0C6AAE6CBB6842739797E8E20048E91A',
'D96638F06E794FA5825D23E36EFFB9D1',
'8EA4BA1E749B4C388AB7A8F976788D56',
'6A2500E55D034D7B8EB13D06878A1D9B',
'A0CA35EECA0F42229D8410545ACB68E3',
'C34B8B66DDF244B8A3A908C53EBB16F6',
'1FCF5E2FDCB2450F806A675AB07193A7',
'F788E977C5B64ED7A85169BA5236589E',
'E4B776F73C724DD7A5B323E0B2E841BA',
'08189B9A7D844CBCAFE14A7FB9B96913',
'B147B3EC7A8D42A4A608053D0BBDA16A',
'D47DAC0B366F483BAA194C5343D319C0',
'E279CA67D8CA43F5AEC3F4CA84214406',
'CCBEF4528C9A42BD9DB5DD3BF8173728',
'55477A2045D3473B8F4206A86698E6D2',
'5FAE4EEA12BD4A688A15C8E37EE3EBD7',
'67D855B636114782AD040B50B7228692',
'103BEA21E8E542D2A034A31770ADE05F',
'AF4BA739CCEA492B833CD43A77A33C6D',
'66FE8861B05B4347A044EDFF1014A288',
'092B255D7EC74A3FA5525FC569B3F71E',
'4D9E8560D45A460480F4A1149D08606E',
'E058BB072CFA44B2B6C100566EAADEED',
'319E8635613743318EA26DBD03BC882C',
'19F0E53F8AA44F4A8C0B61C1A747688D',
'2133FE47A7324CDA80EE68FF45E61E05',
'8FBFE61810E4454F8E1246C7981F1551',
'7A31597C9469404EB6F07F8DDDC6DE3E',
'70361055E36D43EAABBE36524BCEF26D',
'4F3851920A7449BA903F88A3136A7F17',
'87652CFFDAD949FABD8B9C568435E3EF',
'2D104264E8E74EA58549EE603BCD8AEA',
'A6D118067C8B403985AD754E3AFD075A',
'A67CC53B0AD04B07B9419AE42A731707',
'9C6267A300964551B8EB72221E0E6D33',
'4531AF4A7F3A422DA80ABA2B6B45E657',
'309E9A1B24C6496B9D79AC17C0886209',
'993AA85D1C7747E09823747874BCC4F3',
'469016B267C84C208442E2696C9D400A',
'1B693F0BE60F4C56A0BA89BDE1DC55B1',
'3BA5DBAB24F6422580F083DA3776FD08',
'3794C31D03A643EE928C8A5358E12853',
'76A2DDBEC2224AEDBF71A320A6667987',
'DFE5315B13BB4F8F821774FE4A49D46C',
'78F45D18B38048E9901DE1F0102384DC',
'C984287036394AF6943C8F93B78E4506',
'F71E03783E794EA6B72CF90DF5B7DBA5',
'D0B0F660DF9A4F609C00BD619EBB0F51',
'AD55542CD1F54C208E7F7B58EC65C008',
'2432C0FB102D4451908EDA04A6642190',
'535C96CDB35F49BB801321F522FB2B96',
'C853ABFC80E74DC4BB82967F029D42E2',
'2324EF6B39E54D17BACC2684CF4F16AA',
'E340A0C8A88C443BB8FAB9FCDF844FD8',
'6D005AF435DA48C183ACA2B0E81EDD8F',
'2C743FE937A9485492069A2DFD5C1503',
'C82D39BA61EE465E95EB5D9C16B5A5F8',
'975DDFA55BAD4CB9AF9B8435DD12D7A0',
'8D2AAA7E604C47DE81535132E0E6C48B',
'2644B64E7DC843C1A49EEED5C9AD8861',
'C4E159A41C614709867FD47F8455B218',
'CD18CEA3A5F6443E9B47DA2076F03C03',
'09868DD506E044AEB71468EA45014280',
'FFC21A886B7B442A8939CF67950E815A',
'783B64B7E16E4469BDAD56E05AF5403B',
'806B5D2219004E21AD2EA379FE966670',
'0598DB73327A4B4CBE02684086122065',
'16ACC38D0A22432E80DD09FF717B6224',
'EFB204879DDD41D396B2FB3A5E029323',
'C774409334DC43FDBC3FE3687D9DDE69',
'FF0C52506A3945EFB25591C780D8EB98',
'5D047A3692B840F58CBCDA2F54D8CF7D',
'82BBDFEC32274B72AB27A8E1D453F514',
'2ED7CF7C8C574B4BB9FA8AEA512D4D63',
'FB83A1D3A97D4CE099FC66AB24FBFFCA',
'B1D84FD139D84CB3AAB1A03CDFD6B579',
'FA91DD8960124653AE9B01E2F8655687',
'A64FB0CB369043F29A1C20EC1E613F8A',
'8280E4BDF99642E7A5FBFB6427D236FD',
'BFBAC410E31D4DE3B2CB6CAA31341CEA',
'DFCC55A6294C425F9E9A2776F0E0DC65',
'182152C8710A463CA2D015AB770F4193',
'31825820459F42EEADBA2E15A3F1D1FA',
'A1F5B06F460F4F67BBBC6AC3FE27AE85',
'DF65A343ABA543CF88B7A6DCD52380A3',
'D9CC311BEE7B435EA13ED5359665FA1C',
'A23C7BE09AA344D0BB7E8ACECCF84B9B',
'092895CF76EA44FABC262980D8A156D9',
'62CD82E1A1024DC8AB12DB0E4338674D',
'617419D69ADC49F0A5E84CE7807233EE',
'64FF896979C74E799DDE3F383812E4F1',
'CE80F7CD48AD494BA9C51D29CF2A8773',
'D31CC43D89D242EFBAB45C84ABCE1FF4',
'4F376722D18F42DBB0E86866B7017F50',
'F0D1C82A28DE4E0995FFD646E7A5A24D',
'D4D7E44F737644069A7C8FFE2A577E7A',
'DB90663FCEC343B6BDF99BB21C63C02F',
'6A3B7B0E57DD48CEACE2981F1C7C81ED',
'04FFD14E863C427B9E95FDD273059339',
'7A3EBF715DBB43258B29BFCBA7E8F8ED',
'71329097F6AD4455A6193BA551993DF2',
'45F69D028C46421C9B2897B1EB3EDD4E',
'26654E04FDD4405CBDB9334070DC2026',
'AB585C50B9934FEE8D6DCC141AECA3EE',
'2D288394D33F449F8C5BE09A23B5F97B',
'288B1466C1024455A5C80DE9944C275E',
'16303BD6F5C74355A3E6ED4DB60B8A67',
'469ACE6358FB4C0EA58058FA823EAFEC',
'0D796DFE59D0443E93A2CA46F607886E',
'790BA1D65CA34B72AFA6ED87355A6337',
'9228CFE725604EA0BF01C676B5C5E72A',
'BF77D03787D84C9888FCFBD012BFDA02',
'043D618911174C8D85F2B97407B3B652',
'08537956D91D40B69AE040254CAF341E',
'DDDD4C61896142B78A1EA29105A082AD',
'BDA0ECE46C39499491FD6149E787C9F4',
'96B08A8DB32C49E2A809E156DB592842',
'D3E00B67031D4A309FE917292CF1F719',
'77D3336DC2214AE6A7304A23FD35A805',
'F04CEDB8E1FC4341958071416F24AD05',
'2484267157304B488B71329DDB3D8421',
'D76C8E0696EA4C62B38DBD29ACB602E7',
'F3CE6131A1674567ADC67FA3FD764B7E',
'6B6A074DD75A415C958AC44CA5755B60',
'38D225ED623B4E13B78C2C917579E440',
'6A247D9E877C4A5689C8A90BCBD8F957',
'537C0C44F0C740CA917101B6413C9AB5',
'C851613D607F47F99AAD8516BBA438F4',
'1955B39FD1884AB180F1F69F45C0B1BE',
'43C4BA0AFB8B486B9AACBD519E403AD9',
'1526521C6C704155A625CF21CE913BF4',
'0917477F7B8B48228D2A13D8B5E5D0C7',
'D1331BB2694840FD8B38D5857E4D8C05',
'317DAAB1CF49470CA3FD565D773666DD',
'943596C335724B139171B9EDA473DA53',
'9178C2F840EB4CD9B1098A333EBFA992',
'B8184BF4838A4B4FA7809FDCBB25504E',
'AEDAF1F20FAF44FC93AB35D2DFD3E5C5',
'B6E2D1B581464191AD48AFF6AED9B813',
'C2BAA87926B14FCCB7B6C481EFAA395C',
'972455AB23EC47A1A2C31B53F5C06E68',
'8969F75D79B6402BA45B7DF1EA54435D',
'531F3B4C89EC4BF180B6616F94656FA1',
'E853EC3DE4244365BE97935652115E35',
'043739981A1D4C5BBACAC37926B2104C',
'9D1090AFC1D043B2A8086A4FFF8D31E4',
'8DF1BD24A1EB4F4F918E75236873A402',
'00FC5F98BDE047F99C062F6ABD2DCB9B',
'F5CA78C4CDAC45758F3D3518EB2938C5',
'6027B0644E5E4A238FBD6CAC1F44DDE3',
'15F7BFB3DA804535BDD03E216EB851B4',
'2CD3172D63644694A19E7BE676B63362',
'696A3525F40E4E01B538CF3E729CB410',
'68269B60854A41C48A55EC4FB363E5F3',
'B54CFF4B5FED44A9853F2F6FA3FFC931',
'11009FD31FA045C3910556DDB72C5817',
'86B7EAE491FF4025946652B3423A4EB5',
'C83B8D6D3DBA455B8C4973F68D8E2F59',
'FD8E0F9DA2124FCB954859AB6F48755F' )
AND o.isdelivered = 'N'
AND 1 = 1
AND O.dateordered >= To_date('10-08-2017')
AND O.dateordered < To_date('13-09-2017')
AND O.ad_org_id = 'D9CC311BEE7B435EA13ED5359665FA1C'
) A
ORDER BY orgname,
bpname,
documentno
**************
Query Plan
**************
Sort (cost=226474.81..226478.67 rows=1542 width=548) (actual time=398.889..399.114 rows=1468 loops=1)
Sort Key: a.orgname, a.bpname, a.documentno
Sort Method: quicksort Memory: 799kB
-> Subquery Scan on a (cost=2278.98..226393.16 rows=1542 width=548) (actual time=21.408..363.936 rows=1468 loops=1)
-> Hash Left Join (cost=2278.98..213579.14 rows=1542 width=559) (actual time=21.392..353.059 rows=1468 loops=1)
Hash Cond: ((ol.m_attributesetinstance_id)::text = (asi.m_attributesetinstance_id)::text)
-> Hash Join (cost=2237.55..167624.00 rows=1542 width=547) (actual time=20.527..116.713 rows=1468 loops=1)
Hash Cond: ((ol.c_uom_id)::text = (uo.c_uom_id)::text)
-> Hash Join (cost=2216.61..167581.86 rows=1542 width=543) (actual time=20.287..115.294 rows=1468 loops=1)
Hash Cond: ((ol.m_product_id)::text = (pr.m_product_id)::text)
-> Nested Loop (cost=1733.14..167067.55 rows=1542 width=509) (actual time=16.832..109.583 rows=1468 loops=1)
-> Hash Left Join (cost=1727.89..31962.74 rows=375 width=236) (actual time=16.801..105.752 rows=39 loops=1)
Hash Cond: ((o.delivery_location_id)::text = (dloc.c_bpartner_location_id)::text)
-> Hash Join (cost=1165.48..31395.56 rows=375 width=240) (actual time=11.177..100.074 rows=39 loops=1)
Hash Cond: ((o.deliveryrule)::text = (reflistv.value)::text)
-> Hash Join (cost=1121.68..31346.61 rows=375 width=210) (actual time=11.111..99.964 rows=39 loops=1)
Hash Cond: ((o.c_bpartner_location_id)::text = (bpadd.c_bpartner_location_id)::text)
-> Hash Join (cost=559.27..30775.76 rows=375 width=214) (actual time=5.353..94.120 rows=39 loops=1)
Hash Cond: ((o.c_bpartner_id)::text = (bp.c_bpartner_id)::text)
-> Nested Loop (cost=1.09..30209.15 rows=375 width=228) (actual time=0.437..89.113 rows=39 loops=1)
-> Index Scan using ad_org_key on ad_org org (cost=0.27..8.29 rows=1 width=55) (actual time=0.011..0.014 rows=1 loops=1)
Index Cond: ((ad_org_id)::text = 'D9CC311BEE7B435EA13ED5359665FA1C'::text)
-> Index Scan using c_order_client_org_date_docno on c_order o (cost=0.82..30197.11 rows=375 width=238) (actual time=0.163..88.784 rows=39 loops=1)
Index Cond: (((ad_client_id)::text = ANY ('{0,8B72EA2C5B444DE291849E37F2F86DBA}'::text[])) AND ((ad_org_id)::text = ANY ('{0,306D3B3010FB47C8830A3CD6544CB71D,A25CF246A7B94A4A92B0C0D74878D227,CBC7D0B4DEB04192ABA391687EAE0F61,3F52F6C378274F8EA1A9A1A10D15E21E,C14CFAB09B594263AB5C99A82937E427,E453C89517DD4FB3A2EBB9EF79808E3A,E3ADD08E7A344BC4AA0E5271A00EE1AB,48C21BE2F1A449A8AD397A7D1B3F2C18,318A3152361C46959E07EC1393D1C7CE,E3353FB57805410D9515EFA23737C399,AB40EE591DBE44F093E4C5B606D354C6,A31788F217B448728321DDB7800D1808,3CB045196B7547DFAE80DC60193B687A,362AAC83D0D94AB5B802E2FDDA49A8E0,5F7B6E84D15843E69C4ED7D97444D478,40C5CBF3762A4BDA85058E1D837E03E4,8BD961208E0244D99F31700FFAC12341,6D3322D0CEFE40FBB5ADCE995C9A5E18,D2FF042B470147AB81BC7B993D63214C,B31B3BFBBE154ACB93897A42C9FF351C,2DE44FC9B33847FEB69C2908E1FBC1C2,E7F4AC1093684F15B5954A5818076653,3A1097E45C4243F6BBE926EA3D947E3F,5F0B6579C951489A936A683A426A132E,9FC6900EC6D04B55B3F831FA09121E32,D46CCA95EB5542A5973BAEEF312F6A0D,EC9580A5DE2F4AADA9ECC42D34702571,25E8B7AA299A492D9EF6B2B99B864419,316DC99A97B844CDBE9E20003A16C0F3,5F49108E9AA24990A0EBC198DF40CE54,5305CF0416744873BB2DF46386354A31,9A3DA17921AD4C52A79C2359B0BBC981,BADC1A86EB20423FA958DE0B65CC381F,A8B01CD45FDA490AA2FBF2A857249345,63022951ABB74A86B790E95F52E40F58,FAF808E5C3F349248230456BFC11C6B8,53D2871ECE304BB09604AF4D0FF3D7F1,A892B2A07E2643C1845C99F4E687862B,D8CF30DCA6AB43F69021C95005E6BD13,D9AD12FC3BBA499DA29BDB45ADCE6D5A,FB5802AE5E284CCE8B1AEF307089595B,A5112471313A4F0199F10514E5DF2BBE,4B67D434A6DD44A69A1EAE1D74C83562,18B60CB5A6174D169BA790216E002B4D,7BCCA05C769C44E395EDA1AC27A56A03,E6EAD13FC35445AE9BD81901F0624172,6F67E7FC52C444CFA0D7B7E95F5FEDB8,158F30646EE949ABB8B637F09BC79AED,327EF8CB2F564BA99F20687CD8AA35F1,53479AE940E744B5B3624BE316062075,325EDF84AA9C4395AAD33A14F59BE6A1,E43A0CFA32F044C6B381E8771ED0DD7C,2F3F3D5938D84A67811FBF8A27162690,7EBDA22E5A664C56A56C28C3D26DC092,E70FD5E6A6B04477A2200D9C43E78A24,98C02C1EB0224902AAE5171D347AC76B,1CDFEC4B6E2140D6843D25ADB328128E,2653FE69D1DE43C8ADC3D1E20505B430,0C6AAE6CBB6842739797E8E20048E91A,D96638F06E794FA5825D23E36EFFB9D1,8EA4BA1E749B4C388AB7A8F976788D56,6A2500E55D034D7B8EB13D06878A1D9B,A0CA35EECA0F42229D8410545ACB68E3,C34B8B66DDF244B8A3A908C53EBB16F6,1FCF5E2FDCB2450F806A675AB07193A7,F788E977C5B64ED7A85169BA5236589E,E4B776F73C724DD7A5B323E0B2E841BA,08189B9A7D844CBCAFE14A7FB9B96913,B147B3EC7A8D42A4A608053D0BBDA16A,D47DAC0B366F483BAA194C5343D319C0,E279CA67D8CA43F5AEC3F4CA84214406,CCBEF4528C9A42BD9DB5DD3BF8173728,55477A2045D3473B8F4206A86698E6D2,5FAE4EEA12BD4A688A15C8E37EE3EBD7,67D855B636114782AD040B50B7228692,103BEA21E8E542D2A034A31770ADE05F,AF4BA739CCEA492B833CD43A77A33C6D,66FE8861B05B4347A044EDFF1014A288,092B255D7EC74A3FA5525FC569B3F71E,4D9E8560D45A460480F4A1149D08606E,E058BB072CFA44B2B6C100566EAADEED,319E8635613743318EA26DBD03BC882C,19F0E53F8AA44F4A8C0B61C1A747688D,2133FE47A7324CDA80EE68FF45E61E05,8FBFE61810E4454F8E1246C7981F1551,7A31597C9469404EB6F07F8DDDC6DE3E,70361055E36D43EAABBE36524BCEF26D,4F3851920A7449BA903F88A3136A7F17,87652CFFDAD949FABD8B9C568435E3EF,2D104264E8E74EA58549EE603BCD8AEA,A6D118067C8B403985AD754E3AFD075A,A67CC53B0AD04B07B9419AE42A731707,9C6267A300964551B8EB72221E0E6D33,4531AF4A7F3A422DA80ABA2B6B45E657,309E9A1B24C6496B9D79AC17C0886209,993AA85D1C7747E09823747874BCC4F3,469016B267C84C208442E2696C9D400A,1B693F0BE60F4C56A0BA89BDE1DC55B1,3BA5DBAB24F6422580F083DA3776FD08,3794C31D03A643EE928C8A5358E12853,76A2DDBEC2224AEDBF71A320A6667987,DFE5315B13BB4F8F821774FE4A49D46C,78F45D18B38048E9901DE1F0102384DC,C984287036394AF6943C8F93B78E4506,F71E03783E794EA6B72CF90DF5B7DBA5,D0B0F660DF9A4F609C00BD619EBB0F51,AD55542CD1F54C208E7F7B58EC65C008,2432C0FB102D4451908EDA04A6642190,535C96CDB35F49BB801321F522FB2B96,C853ABFC80E74DC4BB82967F029D42E2,2324EF6B39E54D17BACC2684CF4F16AA,E340A0C8A88C443BB8FAB9FCDF844FD8,6D005AF435DA48C183ACA2B0E81EDD8F,2C743FE937A9485492069A2DFD5C1503,C82D39BA61EE465E95EB5D9C16B5A5F8,975DDFA55BAD4CB9AF9B8435DD12D7A0,8D2AAA7E604C47DE81535132E0E6C48B,2644B64E7DC843C1A49EEED5C9AD8861,C4E159A41C614709867FD47F8455B218,CD18CEA3A5F6443E9B47DA2076F03C03,09868DD506E044AEB71468EA45014280,FFC21A886B7B442A8939CF67950E815A,783B64B7E16E4469BDAD56E05AF5403B,806B5D2219004E21AD2EA379FE966670,0598DB73327A4B4CBE02684086122065,16ACC38D0A22432E80DD09FF717B6224,EFB204879DDD41D396B2FB3A5E029323,C774409334DC43FDBC3FE3687D9DDE69,FF0C52506A3945EFB25591C780D8EB98,5D047A3692B840F58CBCDA2F54D8CF7D,82BBDFEC32274B72AB27A8E1D453F514,2ED7CF7C8C574B4BB9FA8AEA512D4D63,FB83A1D3A97D4CE099FC66AB24FBFFCA,B1D84FD139D84CB3AAB1A03CDFD6B579,FA91DD8960124653AE9B01E2F8655687,A64FB0CB369043F29A1C20EC1E613F8A,8280E4BDF99642E7A5FBFB6427D236FD,BFBAC410E31D4DE3B2CB6CAA31341CEA,DFCC55A6294C425F9E9A2776F0E0DC65,182152C8710A463CA2D015AB770F4193,31825820459F42EEADBA2E15A3F1D1FA,A1F5B06F460F4F67BBBC6AC3FE27AE85,DF65A343ABA543CF88B7A6DCD52380A3,D9CC311BEE7B435EA13ED5359665FA1C,A23C7BE09AA344D0BB7E8ACECCF84B9B,092895CF76EA44FABC262980D8A156D9,62CD82E1A1024DC8AB12DB0E4338674D,617419D69ADC49F0A5E84CE7807233EE,64FF896979C74E799DDE3F383812E4F1,CE80F7CD48AD494BA9C51D29CF2A8773,D31CC43D89D242EFBAB45C84ABCE1FF4,4F376722D18F42DBB0E86866B7017F50,F0D1C82A28DE4E0995FFD646E7A5A24D,D4D7E44F737644069A7C8FFE2A577E7A,DB90663FCEC343B6BDF99BB21C63C02F,6A3B7B0E57DD48CEACE2981F1C7C81ED,04FFD14E863C427B9E95FDD273059339,7A3EBF715DBB43258B29BFCBA7E8F8ED,71329097F6AD4455A6193BA551993DF2,45F69D028C46421C9B2897B1EB3EDD4E,26654E04FDD4405CBDB9334070DC2026,AB585C50B9934FEE8D6DCC141AECA3EE,2D288394D33F449F8C5BE09A23B5F97B,288B1466C1024455A5C80DE9944C275E,16303BD6F5C74355A3E6ED4DB60B8A67,469ACE6358FB4C0EA58058FA823EAFEC,0D796DFE59D0443E93A2CA46F607886E,790BA1D65CA34B72AFA6ED87355A6337,9228CFE725604EA0BF01C676B5C5E72A,BF77D03787D84C9888FCFBD012BFDA02,043D618911174C8D85F2B97407B3B652,08537956D91D40B69AE040254CAF341E,DDDD4C61896142B78A1EA29105A082AD,BDA0ECE46C39499491FD6149E787C9F4,96B08A8DB32C49E2A809E156DB592842,D3E00B67031D4A309FE917292CF1F719,77D3336DC2214AE6A7304A23FD35A805,F04CEDB8E1FC4341958071416F24AD05,2484267157304B488B71329DDB3D8421,D76C8E0696EA4C62B38DBD29ACB602E7,F3CE6131A1674567ADC67FA3FD764B7E,6B6A074DD75A415C958AC44CA5755B60,38D225ED623B4E13B78C2C917579E440,6A247D9E877C4A5689C8A90BCBD8F957,537C0C44F0C740CA917101B6413C9AB5,C851613D607F47F99AAD8516BBA438F4,1955B39FD1884AB180F1F69F45C0B1BE,43C4BA0AFB8B486B9AACBD519E403AD9,1526521C6C704155A625CF21CE913BF4,0917477F7B8B48228D2A13D8B5E5D0C7,D1331BB2694840FD8B38D5857E4D8C05,317DAAB1CF49470CA3FD565D773666DD,943596C335724B139171B9EDA473DA53,9178C2F840EB4CD9B1098A333EBFA992,B8184BF4838A4B4FA7809FDCBB25504E,AEDAF1F20FAF44FC93AB35D2DFD3E5C5,B6E2D1B581464191AD48AFF6AED9B813,C2BAA87926B14FCCB7B6C481EFAA395C,972455AB23EC47A1A2C31B53F5C06E68,8969F75D79B6402BA45B7DF1EA54435D,531F3B4C89EC4BF180B6616F94656FA1,E853EC3DE4244365BE97935652115E35,043739981A1D4C5BBACAC37926B2104C,9D1090AFC1D043B2A8086A4FFF8D31E4,8DF1BD24A1EB4F4F918E75236873A402,00FC5F98BDE047F99C062F6ABD2DCB9B,F5CA78C4CDAC45758F3D3518EB2938C5,6027B0644E5E4A238FBD6CAC1F44DDE3,15F7BFB3DA804535BDD03E216EB851B4,2CD3172D63644694A19E7BE676B63362,696A3525F40E4E01B538CF3E729CB410,68269B60854A41C48A55EC4FB363E5F3,B54CFF4B5FED44A9853F2F6FA3FFC931,11009FD31FA045C3910556DDB72C5817,86B7EAE491FF4025946652B3423A4EB5,C83B8D6D3DBA455B8C4973F68D8E2F59,FD8E0F9DA2124FCB954859AB6F48755F}'::text[])) AND ((ad_org_id)::text = 'D9CC311BEE7B435EA13ED5359665FA1C'::text) AND (dateordered >= '2017-08-10 00:00:00'::timestamp without time zone) AND (dateordered < '2017-09-13 00:00:00'::timestamp without time zone))
Filter: (((docstatus)::text = 'CO'::text) AND (issotrx = 'Y'::bpchar) AND (isdelivered = 'N'::bpchar))
Rows Removed by Filter: 44169
-> Hash (cost=451.86..451.86 rows=8505 width=52) (actual time=4.904..4.904 rows=8505 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 698kB
-> Index Only Scan using c_bpartner_name_id on c_bpartner bp (cost=0.29..451.86 rows=8505 width=52) (actual time=0.011..2.025 rows=8505 loops=1)
Heap Fetches: 0
-> Hash (cost=454.96..454.96 rows=8596 width=62) (actual time=5.751..5.751 rows=8596 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 796kB
-> Seq Scan on c_bpartner_location bpadd (cost=0.00..454.96 rows=8596 width=62) (actual time=0.001..2.971 rows=8596 loops=1)
-> Hash (cost=43.75..43.75 rows=4 width=170) (actual time=0.056..0.056 rows=4 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 1kB
-> Subquery Scan on reflistv (cost=43.67..43.75 rows=4 width=170) (actual time=0.053..0.056 rows=4 loops=1)
-> HashAggregate (cost=43.67..43.71 rows=4 width=108) (actual time=0.051..0.053 rows=4 loops=1)
-> Append (cost=4.30..43.62 rows=4 width=108) (actual time=0.035..0.045 rows=4 loops=1)
-> Bitmap Heap Scan on ad_ref_list (cost=4.30..14.62 rows=3 width=67) (actual time=0.035..0.042 rows=4 loops=1)
Recheck Cond: ((ad_reference_id)::text = '151'::text)
-> Bitmap Index Scan on ad_ref_list_value (cost=0.00..4.30 rows=3 width=0) (actual time=0.011..0.011 rows=4 loops=1)
Index Cond: ((ad_reference_id)::text = '151'::text)
-> Nested Loop (cost=0.70..28.96 rows=1 width=233) (actual time=0.003..0.003 rows=0 loops=1)
-> Nested Loop (cost=0.70..25.22 rows=1 width=233) (actual time=0.002..0.002 rows=0 loops=1)
-> Nested Loop (cost=0.42..16.91 rows=1 width=300) (actual time=0.002..0.002 rows=0 loops=1)
-> Index Scan using ad_ref_list_trl_ref_list_la_un on ad_ref_list_trl (cost=0.14..8.60 rows=1 width=248) (actual time=0.002..0.002 rows=0 loops=1)
Index Cond: ((ad_language)::text = 'en_US'::text)
-> Index Scan using ad_ref_list_key on ad_ref_list ad_ref_list_1 (cost=0.28..8.30 rows=1 width=52) (never executed)
Index Cond: ((ad_ref_list_id)::text = (ad_ref_list_trl.ad_ref_list_id)::text)
Filter: ((ad_reference_id)::text = '151'::text)
-> Index Scan using ad_ref_list_key on ad_ref_list ad_ref_list_2 (cost=0.28..8.30 rows=1 width=60) (never executed)
Index Cond: ((ad_ref_list_id)::text = (ad_ref_list_1.ad_ref_list_id)::text)
Filter: ((ad_reference_id)::text = '151'::text)
-> Seq Scan on ad_language (cost=0.00..3.23 rows=1 width=0) (never executed)
Filter: (issystemlanguage = 'Y'::bpchar)
-> Hash (cost=454.96..454.96 rows=8596 width=62) (actual time=5.615..5.615 rows=8596 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 796kB
-> Seq Scan on c_bpartner_location dloc (cost=0.00..454.96 rows=8596 width=62) (actual time=0.002..2.686 rows=8596 loops=1)
-> Bitmap Heap Scan on c_orderline ol (cost=5.25..359.37 rows=91 width=339) (actual time=0.030..0.050 rows=38 loops=39)
Recheck Cond: ((c_order_id)::text = (o.c_order_id)::text)
-> Bitmap Index Scan on c_orderline_order (cost=0.00..5.23 rows=91 width=0) (actual time=0.027..0.027 rows=38 loops=39)
Index Cond: ((c_order_id)::text = (o.c_order_id)::text)
-> Hash (cost=403.90..403.90 rows=6366 width=67) (actual time=3.448..3.448 rows=6366 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 620kB
-> Index Only Scan using m_product_name_id on m_product pr (cost=0.41..403.90 rows=6366 width=67) (actual time=0.011..1.575 rows=6366 loops=1)
Heap Fetches: 0
-> Hash (cost=15.97..15.97 rows=397 width=36) (actual time=0.234..0.234 rows=397 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 26kB
-> Seq Scan on c_uom uo (cost=0.00..15.97 rows=397 width=36) (actual time=0.004..0.112 rows=397 loops=1)
-> Hash (cost=31.75..31.75 rows=775 width=44) (actual time=0.500..0.500 rows=775 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 59kB
-> Seq Scan on m_attributesetinstance asi (cost=0.00..31.75 rows=775 width=44) (actual time=0.003..0.250 rows=775 loops=1)
SubPlan 2
-> Aggregate (cost=27.97..27.98 rows=1 width=3) (actual time=0.097..0.097 rows=1 loops=1468)
-> Nested Loop (cost=4.84..27.97 rows=1 width=3) (actual time=0.070..0.095 rows=1 loops=1468)
-> Bitmap Heap Scan on m_locator loc (cost=4.29..10.79 rows=2 width=33) (actual time=0.008..0.009 rows=4 loops=1468)
Recheck Cond: ((m_warehouse_id)::text = (ol.m_warehouse_id)::text)
-> Bitmap Index Scan on m_location_where (cost=0.00..4.29 rows=2 width=0) (actual time=0.006..0.006 rows=4 loops=1468)
Index Cond: ((m_warehouse_id)::text = (ol.m_warehouse_id)::text)
-> Index Scan using m_storage_detail_un on m_storage_detail sd (cost=0.55..8.58 rows=1 width=36) (actual time=0.021..0.021 rows=0 loops=5872)
Index Cond: (((m_product_id)::text = (ol.m_product_id)::text) AND ((m_locator_id)::text = (loc.m_locator_id)::text) AND ((c_uom_id)::text = (ol.c_uom_id)::text))
Filter: (((COALESCE(m_attributesetinstance_id, '0'::character varying))::text = (COALESCE(ol.m_attributesetinstance_id, m_attributesetinstance_id, '0'::character varying))::text) AND ((COALESCE(m_product_uom_id, '-1'::character varying))::text = (COALESCE(ol.m_product_uom_id, '-1'::character varying))::text))
Rows Removed by Filter: 0
SubPlan 1
-> Index Scan using c_uom_key on c_uom (cost=0.27..8.29 rows=1 width=4) (actual time=0.006..0.006 rows=1 loops=1468)
Index Cond: ((c_uom_id)::text = (a.aum)::text)
Total runtime: 399.898 ms
(87 rows)
| |||||||
 Relationships [ Relation Graph ]
[ Dependency Graph ]
|
||||||||
|
||||||||
 Relationships |
|
Notes |
|
|
(0101688) AtulOpenbravo (developer) 2018-01-15 09:48 |
Login on the environment provided by support: - Open Orders Awaiting Delivery Report and set: * From date: 10-08-2017 * To date: 13-09-2017 * Org: BAPS Shayona - Robbinsville It take about ~ 2.5 seconds to finish |
|
(0101691) hgbot (developer) 2018-01-15 12:30 |
Repository: erp/devel/pi Changeset: 385dc5c1739659c2561e5eed7293aecb1a3a9ce3 Author: Atul Gaware <atul.gaware <at> openbravo.com> Date: Fri Jan 12 02:12:24 2018 +0530 URL: http://code.openbravo.com/erp/devel/pi/rev/385dc5c1739659c2561e5eed7293aecb1a3a9ce3 [^] Fixes Issue 0037516:Performance issue in Orders Awaiting Delivery Report ** Use a subquery to fetch qtyinstock, aum by respective queries only once, add uomsymbol in inner select, aumsymbol in outer select ** Use isdelivered flag to avoid c_orderline exists clause which checks undelivered orders line by line qtyordered against qtydelivered --- M modules/org.openbravo.reports.ordersawaitingdelivery/src/org/openbravo/reports/ordersawaitingdelivery/erpCommon/ad_reports/ReportOrderNotShipped_data.xsql --- |
|
(0101692) dmiguelez (developer) 2018-01-15 12:32 |
Code Review + Testing Ok |
|
(0102656) hudsonbot (developer) 2018-02-22 18:18 |
A changeset related to this issue has been promoted main and to the Central Repository, after passing a series of tests. Promotion changeset: https://code.openbravo.com/erp/devel/main/rev/980a6ad5bbf5 [^] Maturity status: Test |
|
Notes |
|
Issue History |
|||
| Date Modified | Username | Field | Change |
| 2017-12-19 17:08 | JONHM | New Issue | |
| 2017-12-19 17:08 | JONHM | Assigned To | => Triage Finance |
| 2017-12-19 17:08 | JONHM | File Added: query.sql | |
| 2017-12-19 17:08 | JONHM | Modules | => Core |
| 2017-12-19 17:08 | JONHM | Resolution time | => 1515452400 |
| 2017-12-19 17:08 | JONHM | Triggers an Emergency Pack | => No |
| 2017-12-19 17:08 | JONHM | File Added: query_plan | |
| 2017-12-19 17:11 | aferraz | Relationship added | related to 0037515 |
| 2017-12-19 17:18 | JONHM | Steps to Reproduce Updated | View Revisions |
| 2017-12-19 17:19 | JONHM | Steps to Reproduce Updated | View Revisions |
| 2017-12-19 17:19 | JONHM | File Deleted: query.sql | |
| 2017-12-19 17:19 | JONHM | File Deleted: query_plan | |
| 2017-12-19 17:19 | JONHM | File Added: query.sql | |
| 2017-12-19 17:19 | JONHM | File Added: query_plan | |
| 2017-12-19 17:52 | dmiguelez | Assigned To | Triage Finance => AtulOpenbravo |
| 2017-12-19 18:23 | AtulOpenbravo | Status | new => scheduled |
| 2017-12-20 09:51 | AtulOpenbravo | Steps to Reproduce Updated | View Revisions |
| 2018-01-15 09:48 | AtulOpenbravo | Note Added: 0101688 | |
| 2018-01-15 09:52 | AtulOpenbravo | File Added: 37516Final_QueryPlan.txt | |
| 2018-01-15 12:30 | hgbot | Checkin | |
| 2018-01-15 12:30 | hgbot | Note Added: 0101691 | |
| 2018-01-15 12:30 | hgbot | Status | scheduled => resolved |
| 2018-01-15 12:31 | hgbot | Resolution | open => fixed |
| 2018-01-15 12:31 | hgbot | Fixed in SCM revision | => http://code.openbravo.com/erp/devel/pi/rev/385dc5c1739659c2561e5eed7293aecb1a3a9ce3 [^] |
| 2018-01-15 12:32 | dmiguelez | Review Assigned To | => dmiguelez |
| 2018-01-15 12:32 | dmiguelez | Note Added: 0101692 | |
| 2018-01-15 12:32 | dmiguelez | Status | resolved => closed |
| 2018-01-15 12:32 | dmiguelez | Fixed in Version | => 3.0PR18Q2 |
| 2018-02-22 18:18 | hudsonbot | Checkin | |
| 2018-02-22 18:18 | hudsonbot | Note Added: 0102656 | |
|
Issue History |
| Copyright © 2000 - 2009 MantisBT Group |
 |