Project:
| View Issue Details[ Jump to Notes ] | [ Issue History ] [ Print ] | |||||||
| ID | ||||||||
| 0037515 | ||||||||
| Type | Category | Severity | Reproducibility | Date Submitted | Last Update | |||
| defect | [Openbravo ERP] 09. Financial management | major | always | 2017-12-19 16:48 | 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 | 1f43ecba24a4 | ||||
| Projection | none | ETA | none | Target Version | ||||
| OS | Any | Database | Any | Java version | ||||
| OS Version | Database version | Ant version | ||||||
| Product Version | SCM revision | |||||||
| Merge Request Status | ||||||||
| Review Assigned To | dmiguelez | |||||||
| OBNetwork customer | OBPS | |||||||
| Web browser | ||||||||
| Modules | Core | |||||||
| Support ticket | 48552 | |||||||
| Regression level | ||||||||
| Regression date | ||||||||
| Regression introduced in release | ||||||||
| Regression introduced by commit | ||||||||
| Triggers an Emergency Pack | No | |||||||
| Summary | 0037515: Performance issue in Orders Awaiting Invoice Report | |||||||
| Description | There is a performance issue in Orders Awaiting Invoice Report on environments with high amount of data. | |||||||
| Steps To Reproduce | Login on the environment provided by support: - Open Orders Awaiting Invoice Report and set: * From date: 10-08-2017 * To date: 12-08-2017 It take about 120 seconds to finish | |||||||
| Proposed Solution | https://code.openbravo.com/erp/devel/pi/file/tip/src/org/openbravo/erpCommon/ad_reports/ReportOrderNotInvoice_data.xsql [^] The c_order table could be excluded in this part of the query: SELECT 1 FROM c_order C2, c_orderline CL WHERE C2.c_order_id = C.c_order_id AND c2.c_order_id = cl.c_order_id AND cl.qtyordered <> cl.qtyinvoiced Also, an index could be added for that condition: cl.qtyordered <> cl.qtyinvoiced | |||||||
| Tags | No tags attached. | |||||||
| Attached Files |  query_plan [^] (25,737 bytes) 2017-12-19 16:49 [Show Content] [Hide Content]'Sort (cost=589.41..589.43 rows=7 width=729)'
' Sort Key: ad_org.name, c_bpartner.name, c_order.dateordered, c_order.documentno, c_orderline.line'
' -> Result (cost=20.15..589.31 rows=7 width=729)'
' -> Append (cost=20.15..585.81 rows=7 width=729)'
' -> Nested Loop (cost=20.15..550.66 rows=6 width=510)'
' -> Nested Loop (cost=20.01..535.95 rows=6 width=510)'
' Join Filter: ((c_order.ad_org_id)::text = (ad_org.ad_org_id)::text)'
' -> Seq Scan on ad_org (cost=0.00..1.08 rows=8 width=47)'
' -> Materialize (cost=20.01..534.17 rows=6 width=492)'
' -> Nested Loop Left Join (cost=20.01..534.14 rows=6 width=492)'
' Join Filter: ((c_bpartner.c_invoiceschedule_id)::text = (c_invoiceschedule.c_invoiceschedule_id)::text)'
' -> Nested Loop (cost=20.01..533.03 rows=6 width=562)'
' Join Filter: ((c_order.c_bpartner_id)::text = (c_bpartner.c_bpartner_id)::text)'
' -> Seq Scan on c_bpartner (cost=0.00..2.25 rows=25 width=137)'
' -> Materialize (cost=20.01..528.55 rows=6 width=458)'
' -> Hash Join (cost=20.01..528.52 rows=6 width=458)'
' Hash Cond: ((c_orderline.c_order_id)::text = (c_order.c_order_id)::text)'
' -> Hash Left Join (cost=2.90..490.96 rows=5437 width=178)'
' Hash Cond: ((c_orderline.m_product_id)::text = (m_product.m_product_id)::text)'
' -> Seq Scan on c_orderline (cost=0.00..413.30 rows=5437 width=166)'
' Filter: (qtyordered <> qtyinvoiced)'
' -> Hash (cost=2.40..2.40 rows=40 width=78)'
' -> Seq Scan on m_product (cost=0.00..2.40 rows=40 width=78)'
' -> Hash (cost=17.10..17.10 rows=1 width=313)'
' -> Merge Join (cost=16.89..17.10 rows=1 width=313)'
' Merge Cond: ((c_currency.c_currency_id)::text = (c_order.c_currency_id)::text)'
' -> Index Only Scan using c_currency_key on c_currency (cost=0.14..10.77 rows=175 width=4)'
' -> Sort (cost=16.75..16.75 rows=1 width=313)'
' Sort Key: c_order.c_currency_id'
' -> Merge Join (cost=16.72..16.74 rows=1 width=313)'
' Merge Cond: ((ad_ref_list.value)::text = (c_order.invoicerule)::text)'
' -> Nested Loop Left Join (cost=0.42..23.54 rows=3 width=156)'
' Join Filter: ((ad_ref_list.ad_ref_list_id)::text = (ad_ref_list_trl.ad_ref_list_id)::text)'
' -> Index Scan using ad_ref_list_value on ad_ref_list (cost=0.28..14.89 rows=3 width=36)'
' Index Cond: ((ad_reference_id)::text = '150'::text)'
' -> Materialize (cost=0.14..8.61 rows=1 width=220)'
' -> Index Scan using ad_ref_list_trl_ref_list_la_un on ad_ref_list_trl (cost=0.14..8.60 rows=1 width=220)'
' Index Cond: ((ad_language)::text = 'en_US'::text)'
' -> Sort (cost=8.59..8.60 rows=1 width=161)'
' Sort Key: c_order.invoicerule'
' -> Index Scan using c_order_dateordered_id on c_order (cost=0.28..8.58 rows=1 width=161)'
' 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: (((invoicerule)::text <> 'N'::text) AND ((ad_client_id)::text = ANY ('{0,8B72EA2C5B444DE291849E37F2F86DBA}'::text[])) AND (processed = 'Y'::bpchar) AND (issotrx = 'Y'::bpchar) AND ((docstatus)::text <> ALL ('{CJ,UE,CA}'::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[])))'
' -> Materialize (cost=0.00..1.01 rows=1 width=45)'
' -> Seq Scan on c_invoiceschedule (cost=0.00..1.01 rows=1 width=45)'
' -> Index Scan using c_uom_key on c_uom (cost=0.14..0.16 rows=1 width=28)'
' Index Cond: ((c_uom_id)::text = (c_orderline.c_uom_id)::text)'
' -> Nested Loop Semi Join (cost=18.19..35.08 rows=1 width=481)'
' Join Filter: ((c.c_order_id)::text = (c2.c_order_id)::text)'
' -> Nested Loop (cost=17.56..30.17 rows=1 width=514)'
' -> Nested Loop (cost=17.29..29.78 rows=1 width=509)'
' Join Filter: ((c.ad_org_id)::text = (ad_org_1.ad_org_id)::text)'
' -> Nested Loop Left Join (cost=17.29..28.60 rows=1 width=491)'
' -> Nested Loop (cost=17.17..27.97 rows=1 width=561)'
' Join Filter: ((c.c_bpartner_id)::text = (c_bpartner_1.c_bpartner_id)::text)'
' -> Nested Loop (cost=17.17..25.40 rows=1 width=457)'
' -> Merge Join (cost=16.89..17.10 rows=1 width=313)'
' Merge Cond: ((c_currency_1.c_currency_id)::text = (c.c_currency_id)::text)'
' -> Index Only Scan using c_currency_key on c_currency c_currency_1 (cost=0.14..10.77 rows=175 width=4)'
' -> Sort (cost=16.75..16.75 rows=1 width=313)'
' Sort Key: c.c_currency_id'
' -> Merge Join (cost=16.72..16.74 rows=1 width=313)'
' Merge Cond: ((ad_ref_list_1.value)::text = (c.invoicerule)::text)'
' -> Nested Loop Left Join (cost=0.42..23.54 rows=3 width=156)'
' Join Filter: ((ad_ref_list_1.ad_ref_list_id)::text = (ad_ref_list_trl_1.ad_ref_list_id)::text)'
' -> Index Scan using ad_ref_list_value on ad_ref_list ad_ref_list_1 (cost=0.28..14.89 rows=3 width=36)'
' Index Cond: ((ad_reference_id)::text = '150'::text)'
' -> Materialize (cost=0.14..8.61 rows=1 width=220)'
' -> Index Scan using ad_ref_list_trl_ref_list_la_un on ad_ref_list_trl ad_ref_list_trl_1 (cost=0.14..8.60 rows=1 width=220)'
' Index Cond: ((ad_language)::text = 'en_US'::text)'
' -> Sort (cost=8.59..8.60 rows=1 width=161)'
' Sort Key: c.invoicerule'
' -> Index Scan using c_order_dateordered_id on c_order c (cost=0.28..8.58 rows=1 width=161)'
' 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: (((invoicerule)::text <> 'N'::text) AND ((ad_client_id)::text = ANY ('{0,8B72EA2C5B444DE291849E37F2F86DBA}'::text[])) AND (processed = 'Y'::bpchar) AND (issotrx = 'Y'::bpchar) AND ((docstatus)::text <> ALL ('{CJ,UE,CA}'::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[])))'
' -> Index Scan using c_ordertax_orderid on c_ordertax (cost=0.28..8.29 rows=1 width=144)'
' Index Cond: ((c_order_id)::text = (c.c_order_id)::text)'
' -> Seq Scan on c_bpartner c_bpartner_1 (cost=0.00..2.25 rows=25 width=137)'
' -> Index Scan using c_invoiceschedule_key on c_invoiceschedule c_invoiceschedule_1 (cost=0.12..0.62 rows=1 width=45)'
' Index Cond: ((c_bpartner_1.c_invoiceschedule_id)::text = (c_invoiceschedule_id)::text)'
' -> Seq Scan on ad_org ad_org_1 (cost=0.00..1.08 rows=8 width=47)'
' -> Index Scan using c_tax_key on c_tax (cost=0.27..0.38 rows=1 width=71)'
' Index Cond: ((c_tax_id)::text = (c_ordertax.c_tax_id)::text)'
' -> Hash Join (cost=0.63..2.61 rows=6 width=66)'
' Hash Cond: ((cl.c_order_id)::text = (c2.c_order_id)::text)'
' -> Index Scan using c_orderline_order on c_orderline cl (cost=0.28..2.23 rows=6 width=33)'
' Index Cond: ((c_order_id)::text = (c_ordertax.c_order_id)::text)'
' Filter: (qtyordered <> qtyinvoiced)'
' -> Hash (cost=0.34..0.34 rows=1 width=33)'
' -> Index Only Scan using c_order_key on c_order c2 (cost=0.28..0.34 rows=1 width=33)'
' Index Cond: (c_order_id = (c_ordertax.c_order_id)::text)'
query.sql [^] (42,006 bytes) 2017-12-19 17:03 Final_37515_Query_QueryPlan_CTE_OBEQUALS_Index.txt [^] (37,460 bytes) 2018-01-22 18:35 [Show Content] [Hide Content]WITH pendingorders as
(
SELECT C.C_ORDER_ID, C.DOCUMENTNO, C.DATEORDERED, C.GRANDTOTAL, C.C_CURRENCY_ID,
C.AD_CLIENT_ID, C.AD_ORG_ID, C.C_BPARTNER_ID, C.INVOICERULE,
AD_REF_LIST.AD_REF_LIST_ID,
AD_REF_LIST.NAME AS INVOICERULENAME,
CL.LINE,
CL.AD_CLIENT_ID AS CL_AD_CLIENT_ID,
CL.AD_ORG_ID AS CL_AD_ORG_ID,
CL.C_CURRENCY_ID AS CL_C_CURRENCY_ID,
CL.LINENETAMT,
CL.QTYORDERED,
CL.QTYINVOICED,
CL.PRICEACTUAL, CL.M_PRODUCT_ID, CL.C_UOM_ID
FROM C_Order C JOIN C_Orderline CL on CL.C_Order_ID = C.C_Order_ID
JOIN AD_REF_LIST ON C.INVOICERULE = AD_REF_LIST.VALUE AND AD_REF_LIST.AD_REFERENCE_ID = '150'
WHERE C.invoicerule <> 'N'
AND C.processed = 'Y'
AND C.docstatus NOT IN ( 'CJ', 'UE', 'CA', 'DR', 'CL')
AND C.issotrx = 'Y'
AND C.AD_Client_ID IN ( '0',
'8B72EA2C5B444DE291849E37F2F86DBA' )
AND C.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',
'317DAAB1CF494',
'11009FD31FA045C3910556DDB72C5817',
'86B7EAE491FF4025946652B3423A4EB5',
'C83B8D6D3DBA455B8C4973F68D8E2F59',
'FD8E0F9DA2124FCB954859AB6F48755F' )
AND 2 = 2
AND C.dateordered >= To_date('10-08-2017')
AND C.dateordered < To_date('13-09-2017')
AND OBEQUALS(CL.QTYORDERED, CL.QTYINVOICED) = 'N')
SELECT AD_ORG.NAME AS ORGNAME,
C_BPARTNER.C_BPARTNER_ID,
C_BPARTNER.NAME AS BPARTNERNAME,
pendingOrders.C_ORDER_ID,
pendingOrders.DOCUMENTNO,
pendingOrders.DATEORDERED,
pendingOrders.GRANDTOTAL,
CASE WHEN pendingorders.C_CURRENCY_ID = '100' THEN pendingorders.GRANDTOTAL ELSE C_CURRENCY_CONVERT(pendingorders.GRANDTOTAL, pendingorders.C_CURRENCY_ID, '100', TO_DATE(COALESCE(pendingorders.DATEORDERED, NOW())), NULL, pendingorders.AD_CLIENT_ID, pendingorders.AD_ORG_ID) END AS CONVGRANDTOTAL,
CASE WHEN pendingOrders.INVOICERULE = 'S' THEN COALESCE(AD_REF_LIST_TRL.NAME, pendingOrders.INVOICERULENAME)||' ('||C_INVOICESCHEDULE.NAME||')'
ELSE COALESCE(AD_REF_LIST_TRL.NAME, pendingOrders.INVOICERULENAME) END AS INVOICERULE,
pendingOrders.LINE AS LINE,
COALESCE(P.NAME, P.DESCRIPTION) AS PRODUCT,
pendingOrders.PRICEACTUAL AS PRICE,
CASE WHEN COALESCE(pendingOrders.CL_C_CURRENCY_ID, pendingorders.C_CURRENCY_ID) = '100' THEN pendingOrders.PRICEACTUAL ELSE C_CURRENCY_CONVERT(pendingOrders.PRICEACTUAL, COALESCE(pendingOrders.CL_C_CURRENCY_ID, pendingorders.C_CURRENCY_ID), '100', TO_DATE(COALESCE(pendingorders.DATEORDERED, NOW())), NULL, pendingOrders.CL_AD_CLIENT_ID, pendingOrders.CL_AD_ORG_ID) END AS CONVPRICE,
pendingOrders.QTYORDERED - pendingOrders.QTYINVOICED AS QTYORDERED,
U.UOMSYMBOL,
NULL AS TAX,
NULL AS TAXBASE,
NULL AS CONVTAXBASE,
pendingOrders.LINENETAMT,
CASE WHEN COALESCE(pendingOrders.CL_C_CURRENCY_ID, pendingorders.C_CURRENCY_ID) = '100' THEN pendingOrders.LINENETAMT ELSE C_CURRENCY_CONVERT(pendingOrders.LINENETAMT, COALESCE(pendingOrders.CL_C_CURRENCY_ID, pendingorders.C_CURRENCY_ID), '100', TO_DATE(COALESCE(pendingorders.DATEORDERED, NOW())), NULL, pendingOrders.CL_AD_CLIENT_ID, pendingOrders.CL_AD_ORG_ID) END AS CONVLINENETAMT,
C_CURRENCY_SYMBOL(pendingorders.C_CURRENCY_ID, 0, 'Y') AS ORDERCURRENCYSYM,
pendingorders.C_CURRENCY_ID AS TRANSCURRENCYIDORDER,
pendingorders.DATEORDERED AS TRANSDATEORDER,
pendingorders.AD_CLIENT_ID AS TRANSCLIENTIDORDER,
pendingorders.AD_ORG_ID AS TRANSORGIDORDER,
C_CURRENCY_SYMBOL(COALESCE(pendingOrders.CL_C_CURRENCY_ID, pendingorders.C_CURRENCY_ID), 0, 'Y') AS LINECURRENCYSYM,
COALESCE(pendingOrders.CL_C_CURRENCY_ID, pendingorders.C_CURRENCY_ID) AS TRANSCURRENCYIDLINE,
TO_DATE(COALESCE(pendingorders.DATEORDERED, NOW())) AS TRANSDATELINE,
pendingOrders.CL_AD_CLIENT_ID AS TRANSCLIENTIDLINE,
pendingOrders.CL_AD_ORG_ID AS TRANSORGIDLINE
FROM pendingOrders join m_product p on p.m_product_id = pendingOrders.m_product_id
join c_uom u on u.c_uom_id = pendingOrders.c_uom_id
left join AD_REF_LIST_TRL ON pendingOrders.AD_REF_LIST_ID = AD_REF_LIST_TRL.AD_REF_LIST_ID
AND AD_REF_LIST_TRL.AD_LANGUAGE = 'en_US',
C_BPARTNER
LEFT JOIN C_INVOICESCHEDULE ON C_BPARTNER.C_INVOICESCHEDULE_ID = C_INVOICESCHEDULE.C_INVOICESCHEDULE_ID,
AD_ORG
WHERE pendingorders.C_BPARTNER_ID = C_BPARTNER.C_BPARTNER_ID
AND pendingorders.AD_ORG_ID = AD_ORG.AD_ORG_ID
UNION ALL
SELECT MAX(AD_ORG.NAME) AS ORGNAME,
MAX(C_BPARTNER.C_BPARTNER_ID) AS C_BPARTNER_ID,
MAX(C_BPARTNER.NAME) AS BPARTNERNAME,
pendingOrders.C_ORDER_ID AS C_ORDER_ID,
MAX(pendingOrders.DOCUMENTNO) AS DOCUMENTNO,
MAX(pendingOrders.DATEORDERED) AS DATEORDERED,
MAX(pendingOrders.GRANDTOTAL) AS GRANDTOTAL,
C_CURRENCY_CONVERT(MAX(pendingOrders.GRANDTOTAL), MAX(pendingOrders.C_CURRENCY_ID), '100', TO_DATE(COALESCE(MAX(pendingOrders.DATEORDERED), NOW())), NULL, MAX(pendingOrders.AD_CLIENT_ID), MAX(pendingOrders.AD_ORG_ID)) AS CONVGRANDTOTAL,
CASE WHEN MAX(pendingOrders.INVOICERULE) = 'S' THEN COALESCE(MAX(AD_REF_LIST_TRL.NAME), MAX(pendingOrders.INVOICERULENAME))||' ('||MAX(C_INVOICESCHEDULE.NAME)||')'
ELSE COALESCE(MAX(AD_REF_LIST_TRL.NAME), MAX(pendingOrders.INVOICERULENAME)) END AS INVOICERULE,
NULL AS LINE,
MAX(C_TAX.NAME) AS PRODUCT,
NULL AS PRICE,
NULL AS CONVPRICE,
NULL AS QTYORDERED,
NULL AS UOMSYMBOL,
MAX(C_TAX.RATE) AS TAX,
MAX(C_ORDERTAX.TAXBASEAMT) AS TAXBASE,
C_CURRENCY_CONVERT(MAX(C_ORDERTAX.TAXBASEAMT), MAX(pendingOrders.C_CURRENCY_ID), '100', TO_DATE(COALESCE(MAX(pendingOrders.DATEORDERED), NOW())), NULL, MAX(C_ORDERTAX.AD_CLIENT_ID), MAX(C_ORDERTAX.AD_ORG_ID)) AS CONVTAXBASE,
MAX(C_ORDERTAX.TAXAMT) AS LINENETAMT,
C_CURRENCY_CONVERT(MAX(C_ORDERTAX.TAXAMT), MAX(pendingOrders.C_CURRENCY_ID), '100', TO_DATE(COALESCE(MAX(pendingOrders.DATEORDERED), NOW())), NULL, MAX(C_ORDERTAX.AD_CLIENT_ID), MAX(C_ORDERTAX.AD_ORG_ID)) AS CONVLINENETAMT,
C_CURRENCY_SYMBOL(MAX(pendingOrders.C_CURRENCY_ID), 0, 'Y') AS ORDERCURRENCYSYM,
MAX(pendingOrders.C_CURRENCY_ID) AS TRANSCURRENCYIDORDER,
MAX(pendingOrders.DATEORDERED) AS TRANSDATEORDER,
MAX(pendingOrders.AD_CLIENT_ID) AS TRANSCLIENTIDORDER,
MAX(pendingOrders.AD_ORG_ID) AS TRANSORGIDORDER,
C_CURRENCY_SYMBOL(MAX(pendingOrders.C_CURRENCY_ID), 0, 'Y') AS LINECURRENCYSYM,
MAX(pendingOrders.C_CURRENCY_ID) AS TRANSCURRENCYIDLINE,
TO_DATE(COALESCE(MAX(pendingOrders.DATEORDERED), NOW())) AS TRANSDATELINE,
MAX(C_ORDERTAX.AD_CLIENT_ID) AS TRANSCLIENTIDLINE,
MAX(C_ORDERTAX.AD_ORG_ID) AS TRANSORGIDLINE
FROM C_BPARTNER
LEFT JOIN C_INVOICESCHEDULE ON C_BPARTNER.C_INVOICESCHEDULE_ID = C_INVOICESCHEDULE.C_INVOICESCHEDULE_ID,
C_ORDERTAX,
AD_ORG,
C_TAX, pendingOrders left join AD_REF_LIST_TRL ON pendingOrders.AD_REF_LIST_ID = AD_REF_LIST_TRL.AD_REF_LIST_ID
AND AD_REF_LIST_TRL.AD_LANGUAGE = 'en_US'
WHERE pendingOrders.C_ORDER_ID = C_ORDERTAX.C_ORDER_ID
AND pendingOrders.C_BPARTNER_ID=C_BPARTNER.C_BPARTNER_ID
AND C_ORDERTAX.C_TAX_ID = C_TAX.C_TAX_ID
AND pendingOrders.AD_ORG_ID = AD_ORG.AD_ORG_ID
AND 2=2 GROUP BY
pendingOrders.C_ORDER_ID, C_ORDERTAX.C_TAX_ID;
Append (cost=223303.95..239178.62 rows=2092 width=1104) (actual time=1573.964..1964.612 rows=1809 loops=1)
CTE pendingorders
-> Nested Loop (cost=15.52..223258.18 rows=100 width=362) (actual time=118.979..1566.962 rows=1434 loops=1)
-> Hash Join (cost=14.71..186374.63 rows=4864 width=203) (actual time=0.851..240.118 rows=102818 loops=1)
Hash Cond: ((c.invoicerule)::text = (ad_ref_list.value)::text)
-> Index Scan using c_order_client_org_date_docno on c_order c (cost=0.80..185601.04 rows=113766 width=165) (actual time=0.826..183.920 rows=102818 loops=1)
Index Cond: (((ad_client_id)::text = ANY ('{0,8B72EA2C5B444DE291849E37F2F86DBA}'::text[])) AND ((ad_org_id)::text = ANY ('{0,306D3B3010FB47C8830A3CD6544CB71D,A25CF2
46A7B94A4A92B0C0D74878D227,CBC7D0B4DEB04192ABA391687EAE0F61,3F52F6C378274F8EA1A9A1A10D15E21E,C14CFAB09B594263AB5C99A82937E427,E453C89517DD4FB3A2EBB9EF79808E3A,E3ADD08E7A344BC4AA0E5271A00E
E1AB,48C21BE2F1A449A8AD397A7D1B3F2C18,318A3152361C46959E07EC1393D1C7CE,E3353FB57805410D9515EFA23737C399,AB40EE591DBE44F093E4C5B606D354C6,A31788F217B448728321DDB7800D1808,3CB045196B7547DFA
E80DC60193B687A,362AAC83D0D94AB5B802E2FDDA49A8E0,5F7B6E84D15843E69C4ED7D97444D478,40C5CBF3762A4BDA85058E1D837E03E4,8BD961208E0244D99F31700FFAC12341,6D3322D0CEFE40FBB5ADCE995C9A5E18,D2FF04
2B470147AB81BC7B993D63214C,B31B3BFBBE154ACB93897A42C9FF351C,2DE44FC9B33847FEB69C2908E1FBC1C2,E7F4AC1093684F15B5954A5818076653,3A1097E45C4243F6BBE926EA3D947E3F,5F0B6579C951489A936A683A426A
132E,9FC6900EC6D04B55B3F831FA09121E32,D46CCA95EB5542A5973BAEEF312F6A0D,EC9580A5DE2F4AADA9ECC42D34702571,25E8B7AA299A492D9EF6B2B99B864419,316DC99A97B844CDBE9E20003A16C0F3,5F49108E9AA24990A
0EBC198DF40CE54,5305CF0416744873BB2DF46386354A31,9A3DA17921AD4C52A79C2359B0BBC981,BADC1A86EB20423FA958DE0B65CC381F,A8B01CD45FDA490AA2FBF2A857249345,63022951ABB74A86B790E95F52E40F58,FAF808
E5C3F349248230456BFC11C6B8,53D2871ECE304BB09604AF4D0FF3D7F1,A892B2A07E2643C1845C99F4E687862B,D8CF30DCA6AB43F69021C95005E6BD13,D9AD12FC3BBA499DA29BDB45ADCE6D5A,FB5802AE5E284CCE8B1AEF307089
595B,A5112471313A4F0199F10514E5DF2BBE,4B67D434A6DD44A69A1EAE1D74C83562,18B60CB5A6174D169BA790216E002B4D,7BCCA05C769C44E395EDA1AC27A56A03,E6EAD13FC35445AE9BD81901F0624172,6F67E7FC52C444CFA
0D7B7E95F5FEDB8,158F30646EE949ABB8B637F09BC79AED,327EF8CB2F564BA99F20687CD8AA35F1,53479AE940E744B5B3624BE316062075,325EDF84AA9C4395AAD33A14F59BE6A1,E43A0CFA32F044C6B381E8771ED0DD7C,2F3F3D
5938D84A67811FBF8A27162690,7EBDA22E5A664C56A56C28C3D26DC092,E70FD5E6A6B04477A2200D9C43E78A24,98C02C1EB0224902AAE5171D347AC76B,1CDFEC4B6E2140D6843D25ADB328128E,2653FE69D1DE43C8ADC3D1E20505
B430,0C6AAE6CBB6842739797E8E20048E91A,D96638F06E794FA5825D23E36EFFB9D1,8EA4BA1E749B4C388AB7A8F976788D56,6A2500E55D034D7B8EB13D06878A1D9B,A0CA35EECA0F42229D8410545ACB68E3,C34B8B66DDF244B8A
3A908C53EBB16F6,1FCF5E2FDCB2450F806A675AB07193A7,F788E977C5B64ED7A85169BA5236589E,E4B776F73C724DD7A5B323E0B2E841BA,08189B9A7D844CBCAFE14A7FB9B96913,B147B3EC7A8D42A4A608053D0BBDA16A,D47DAC
0B366F483BAA194C5343D319C0,E279CA67D8CA43F5AEC3F4CA84214406,CCBEF4528C9A42BD9DB5DD3BF8173728,55477A2045D3473B8F4206A86698E6D2,5FAE4EEA12BD4A688A15C8E37EE3EBD7,67D855B636114782AD040B50B722
8692,103BEA21E8E542D2A034A31770ADE05F,AF4BA739CCEA492B833CD43A77A33C6D,66FE8861B05B4347A044EDFF1014A288,092B255D7EC74A3FA5525FC569B3F71E,4D9E8560D45A460480F4A1149D08606E,E058BB072CFA44B2B
6C100566EAADEED,319E8635613743318EA26DBD03BC882C,19F0E53F8AA44F4A8C0B61C1A747688D,2133FE47A7324CDA80EE68FF45E61E05,8FBFE61810E4454F8E1246C7981F1551,7A31597C9469404EB6F07F8DDDC6DE3E,703610
55E36D43EAABBE36524BCEF26D,4F3851920A7449BA903F88A3136A7F17,87652CFFDAD949FABD8B9C568435E3EF,2D104264E8E74EA58549EE603BCD8AEA,A6D118067C8B403985AD754E3AFD075A,A67CC53B0AD04B07B9419AE42A73
1707,9C6267A300964551B8EB72221E0E6D33,4531AF4A7F3A422DA80ABA2B6B45E657,309E9A1B24C6496B9D79AC17C0886209,993AA85D1C7747E09823747874BCC4F3,469016B267C84C208442E2696C9D400A,1B693F0BE60F4C56A
0BA89BDE1DC55B1,3BA5DBAB24F6422580F083DA3776FD08,3794C31D03A643EE928C8A5358E12853,76A2DDBEC2224AEDBF71A320A6667987,DFE5315B13BB4F8F821774FE4A49D46C,78F45D18B38048E9901DE1F0102384DC,C98428
7036394AF6943C8F93B78E4506,F71E03783E794EA6B72CF90DF5B7DBA5,D0B0F660DF9A4F609C00BD619EBB0F51,AD55542CD1F54C208E7F7B58EC65C008,2432C0FB102D4451908EDA04A6642190,535C96CDB35F49BB801321F522FB
2B96,C853ABFC80E74DC4BB82967F029D42E2,2324EF6B39E54D17BACC2684CF4F16AA,E340A0C8A88C443BB8FAB9FCDF844FD8,6D005AF435DA48C183ACA2B0E81EDD8F,2C743FE937A9485492069A2DFD5C1503,C82D39BA61EE465E9
5EB5D9C16B5A5F8,975DDFA55BAD4CB9AF9B8435DD12D7A0,8D2AAA7E604C47DE81535132E0E6C48B,2644B64E7DC843C1A49EEED5C9AD8861,C4E159A41C614709867FD47F8455B218,CD18CEA3A5F6443E9B47DA2076F03C03,09868D
D506E044AEB71468EA45014280,FFC21A886B7B442A8939CF67950E815A,783B64B7E16E4469BDAD56E05AF5403B,806B5D2219004E21AD2EA379FE966670,0598DB73327A4B4CBE02684086122065,16ACC38D0A22432E80DD09FF717B
6224,EFB204879DDD41D396B2FB3A5E029323,C774409334DC43FDBC3FE3687D9DDE69,FF0C52506A3945EFB25591C780D8EB98,5D047A3692B840F58CBCDA2F54D8CF7D,82BBDFEC32274B72AB27A8E1D453F514,2ED7CF7C8C574B4BB
9FA8AEA512D4D63,FB83A1D3A97D4CE099FC66AB24FBFFCA,B1D84FD139D84CB3AAB1A03CDFD6B579,FA91DD8960124653AE9B01E2F8655687,A64FB0CB369043F29A1C20EC1E613F8A,8280E4BDF99642E7A5FBFB6427D236FD,BFBAC4
10E31D4DE3B2CB6CAA31341CEA,DFCC55A6294C425F9E9A2776F0E0DC65,182152C8710A463CA2D015AB770F4193,31825820459F42EEADBA2E15A3F1D1FA,A1F5B06F460F4F67BBBC6AC3FE27AE85,DF65A343ABA543CF88B7A6DCD523
80A3,D9CC311BEE7B435EA13ED5359665FA1C,A23C7BE09AA344D0BB7E8ACECCF84B9B,092895CF76EA44FABC262980D8A156D9,62CD82E1A1024DC8AB12DB0E4338674D,617419D69ADC49F0A5E84CE7807233EE,64FF896979C74E799
DDE3F383812E4F1,CE80F7CD48AD494BA9C51D29CF2A8773,D31CC43D89D242EFBAB45C84ABCE1FF4,4F376722D18F42DBB0E86866B7017F50,F0D1C82A28DE4E0995FFD646E7A5A24D,D4D7E44F737644069A7C8FFE2A577E7A,DB9066
3FCEC343B6BDF99BB21C63C02F,6A3B7B0E57DD48CEACE2981F1C7C81ED,04FFD14E863C427B9E95FDD273059339,7A3EBF715DBB43258B29BFCBA7E8F8ED,71329097F6AD4455A6193BA551993DF2,45F69D028C46421C9B2897B1EB3E
DD4E,26654E04FDD4405CBDB9334070DC2026,AB585C50B9934FEE8D6DCC141AECA3EE,2D288394D33F449F8C5BE09A23B5F97B,288B1466C1024455A5C80DE9944C275E,16303BD6F5C74355A3E6ED4DB60B8A67,469ACE6358FB4C0EA
58058FA823EAFEC,0D796DFE59D0443E93A2CA46F607886E,790BA1D65CA34B72AFA6ED87355A6337,9228CFE725604EA0BF01C676B5C5E72A,BF77D03787D84C9888FCFBD012BFDA02,043D618911174C8D85F2B97407B3B652,085379
56D91D40B69AE040254CAF341E,DDDD4C61896142B78A1EA29105A082AD,BDA0ECE46C39499491FD6149E787C9F4,96B08A8DB32C49E2A809E156DB592842,D3E00B67031D4A309FE917292CF1F719,77D3336DC2214AE6A7304A23FD35
A805,F04CEDB8E1FC4341958071416F24AD05,2484267157304B488B71329DDB3D8421,D76C8E0696EA4C62B38DBD29ACB602E7,F3CE6131A1674567ADC67FA3FD764B7E,6B6A074DD75A415C958AC44CA5755B60,38D225ED623B4E13B
78C2C917579E440,6A247D9E877C4A5689C8A90BCBD8F957,537C0C44F0C740CA917101B6413C9AB5,C851613D607F47F99AAD8516BBA438F4,1955B39FD1884AB180F1F69F45C0B1BE,43C4BA0AFB8B486B9AACBD519E403AD9,152652
1C6C704155A625CF21CE913BF4,0917477F7B8B48228D2A13D8B5E5D0C7,D1331BB2694840FD8B38D5857E4D8C05,317DAAB1CF494,11009FD31FA045C3910556DDB72C5817,86B7EAE491FF4025946652B3423A4EB5,C83B8D6D3DBA45
5B8C4973F68D8E2F59,FD8E0F9DA2124FCB954859AB6F48755F}'::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: (((invoicerule)::text <> 'N'::text) AND (processed = 'Y'::bpchar) AND (issotrx = 'Y'::bpchar) AND ((docstatus)::text <> ALL ('{CJ,UE,CA,DR,CL}'::text[])))
Rows Removed by Filter: 1806
-> Hash (cost=13.87..13.87 rows=3 width=45) (actual time=0.019..0.019 rows=5 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 1kB
-> Bitmap Heap Scan on ad_ref_list (cost=4.30..13.87 rows=3 width=45) (actual time=0.013..0.014 rows=5 loops=1)
Recheck Cond: ((ad_reference_id)::text = '150'::text)
-> Bitmap Index Scan on ad_ref_list_value (cost=0.00..4.30 rows=3 width=0) (actual time=0.009..0.009 rows=5 loops=1)
Index Cond: ((ad_reference_id)::text = '150'::text)
-> Index Scan using c_orderline_order on c_orderline cl (cost=0.81..7.57 rows=1 width=192) (actual time=0.012..0.012 rows=0 loops=102818)
Index Cond: (((c_order_id)::text = (c.c_order_id)::text) AND (obequals(qtyordered, qtyinvoiced) = 'N'::bpchar))
-> Nested Loop Left Join (cost=45.77..1405.53 rows=100 width=1406) (actual time=1573.963..1684.677 rows=1434 loops=1)
Join Filter: ((c_bpartner.c_invoiceschedule_id)::text = (c_invoiceschedule.c_invoiceschedule_id)::text)
Rows Removed by Join Filter: 5736
-> Nested Loop (cost=45.77..1169.48 rows=100 width=1428) (actual time=1573.758..1591.608 rows=1434 loops=1)
-> Nested Loop Left Join (cost=45.48..545.98 rows=100 width=1425) (actual time=1573.741..1579.587 rows=1434 loops=1)
Join Filter: ((pendingorders.ad_ref_list_id)::text = (ad_ref_list_trl.ad_ref_list_id)::text)
-> Hash Join (cost=45.34..535.88 rows=100 width=1369) (actual time=1573.732..1578.420 rows=1434 loops=1)
Hash Cond: ((p.m_product_id)::text = (pendingorders.m_product_id)::text)
-> Seq Scan on m_product p (cost=0.00..465.66 rows=6366 width=99) (actual time=0.003..1.252 rows=6366 loops=1)
-> Hash (cost=44.09..44.09 rows=100 width=1385) (actual time=1573.683..1573.683 rows=1434 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 529kB
-> Hash Join (cost=22.96..44.09 rows=100 width=1385) (actual time=1571.896..1572.583 rows=1434 loops=1)
Hash Cond: ((ad_org.ad_org_id)::text = (pendingorders.ad_org_id)::text)
-> Seq Scan on ad_org (cost=0.00..19.28 rows=228 width=55) (actual time=0.001..0.041 rows=228 loops=1)
-> Hash (cost=21.71..21.71 rows=100 width=1362) (actual time=1571.865..1571.865 rows=1434 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 494kB
-> Hash Join (cost=3.25..21.71 rows=100 width=1362) (actual time=1569.874..1570.837 rows=1434 loops=1)
Hash Cond: ((u.c_uom_id)::text = (pendingorders.c_uom_id)::text)
-> Seq Scan on c_uom u (cost=0.00..15.97 rows=397 width=36) (actual time=0.001..0.073 rows=397 loops=1)
-> Hash (cost=2.00..2.00 rows=100 width=1440) (actual time=1569.857..1569.857 rows=1434 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 533kB
-> CTE Scan on pendingorders (cost=0.00..2.00 rows=100 width=1440) (actual time=118.984..1568.794 rows=1434 loops=1)
-> Materialize (cost=0.14..8.61 rows=1 width=220) (actual time=0.000..0.000 rows=0 loops=1434)
-> Index Scan using ad_ref_list_trl_ref_list_la_un on ad_ref_list_trl (cost=0.14..8.60 rows=1 width=220) (actual time=0.003..0.003 rows=0 loops=1)
Index Cond: ((ad_language)::text = 'en_US'::text)
-> Index Scan using c_bpartner_idsalesrep on c_bpartner (cost=0.29..6.23 rows=1 width=85) (actual time=0.007..0.007 rows=1 loops=1434)
Index Cond: ((c_bpartner_id)::text = (pendingorders.c_bpartner_id)::text)
-> Materialize (cost=0.00..1.06 rows=4 width=44) (actual time=0.000..0.001 rows=4 loops=1434)
-> Seq Scan on c_invoiceschedule (cost=0.00..1.04 rows=4 width=44) (actual time=0.002..0.004 rows=4 loops=1)
-> Subquery Scan on "*SELECT* 2" (cost=9713.19..14513.91 rows=1992 width=1089) (actual time=111.817..279.282 rows=375 loops=1)
-> GroupAggregate (cost=9713.19..14493.99 rows=1992 width=1089) (actual time=111.812..278.920 rows=375 loops=1)
-> Sort (cost=9713.19..9718.17 rows=1992 width=1089) (actual time=111.578..112.788 rows=6179 loops=1)
Sort Key: pendingorders_1.c_order_id, c_ordertax.c_tax_id
Sort Method: quicksort Memory: 3365kB
-> Hash Join (cost=447.25..9604.02 rows=1992 width=1089) (actual time=6.097..48.559 rows=6179 loops=1)
Hash Cond: ((c_ordertax.c_tax_id)::text = (c_tax.c_tax_id)::text)
-> Nested Loop (cost=8.39..9125.32 rows=1992 width=1055) (actual time=1.353..40.034 rows=6179 loops=1)
-> Nested Loop Left Join (cost=3.68..665.04 rows=100 width=946) (actual time=1.313..15.997 rows=1434 loops=1)
Join Filter: ((pendingorders_1.ad_ref_list_id)::text = (ad_ref_list_trl_1.ad_ref_list_id)::text)
-> Nested Loop Left Join (cost=3.54..654.93 rows=100 width=890) (actual time=1.307..15.036 rows=1434 loops=1)
Join Filter: ((c_bpartner_1.c_invoiceschedule_id)::text = (c_invoiceschedule_1.c_invoiceschedule_id)::text)
Rows Removed by Join Filter: 5736
-> Nested Loop (cost=3.54..647.88 rows=100 width=912) (actual time=1.293..12.087 rows=1434 loops=1)
-> Hash Join (cost=3.25..24.39 rows=100 width=909) (actual time=1.278..1.942 rows=1434 loops=1)
Hash Cond: ((ad_org_1.ad_org_id)::text = (pendingorders_1.ad_org_id)::text)
-> Seq Scan on ad_org ad_org_1 (cost=0.00..19.28 rows=228 width=55) (actual time=0.002..0.043 rows=228 loops=1)
-> Hash (cost=2.00..2.00 rows=100 width=886) (actual time=1.244..1.244 rows=1434 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 307kB
-> CTE Scan on pendingorders pendingorders_1 (cost=0.00..2.00 rows=100 width=886) (actual time=0.002..0.515 rows=1434 loops=1)
-> Index Scan using c_bpartner_idsalesrep on c_bpartner c_bpartner_1 (cost=0.29..6.23 rows=1 width=85) (actual time=0.006..0.006 rows=1 loops=1434)
Index Cond: ((c_bpartner_id)::text = (pendingorders_1.c_bpartner_id)::text)
-> Materialize (cost=0.00..1.06 rows=4 width=44) (actual time=0.000..0.001 rows=4 loops=1434)
-> Seq Scan on c_invoiceschedule c_invoiceschedule_1 (cost=0.00..1.04 rows=4 width=44) (actual time=0.003..0.004 rows=4 loops=1)
-> Materialize (cost=0.14..8.61 rows=1 width=220) (actual time=0.000..0.000 rows=0 loops=1434)
-> Index Scan using ad_ref_list_trl_ref_list_la_un on ad_ref_list_trl ad_ref_list_trl_1 (cost=0.14..8.60 rows=1 width=220) (actual time=0.003..0.003 rows=0 loops=1)
Index Cond: ((ad_language)::text = 'en_US'::text)
-> Bitmap Heap Scan on c_ordertax (cost=4.71..84.40 rows=20 width=142) (actual time=0.013..0.014 rows=4 loops=1434)
Recheck Cond: ((c_order_id)::text = (pendingorders_1.c_order_id)::text)
-> Bitmap Index Scan on c_ordertax_orderid (cost=0.00..4.71 rows=20 width=0) (actual time=0.012..0.012 rows=4 loops=1434)
Index Cond: ((c_order_id)::text = (pendingorders_1.c_order_id)::text)
-> Hash (cost=358.94..358.94 rows=6394 width=67) (actual time=4.733..4.733 rows=6394 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 623kB
-> Seq Scan on c_tax (cost=0.00..358.94 rows=6394 width=67) (actual time=0.002..2.472 rows=6394 loops=1)
Total runtime: 1965.729 ms
(81 rows)
ClientEnvironment37515Fix.diff [^] (21,338 bytes) 2018-01-22 19:12 [Show Content] [Hide Content]# HG changeset patch
# User Atul Gaware <atul.gaware@openbravo.com>
# Date 1516644390 -19800
# Mon Jan 22 23:36:30 2018 +0530
# Node ID 3b26a5dbb00b21ff73b018399277b85995c35176
# Parent 7fafd2a9f24bd0d5889253005412101631cc1efe
Fixes Issue 37515:Performance issue in Orders Awaiting Invoice Report
** Split the query using CTE for pending orders to be invoiced.
** Group by Taxes sub query on Order ID and Tax ID to void duplicates.
** Modify C_OrderLine_Order index by adding function based column
OBEQUALS(QTYORDERED,QTYINVOICED)
** Rearrange parameters in xsql accordingly and pass them while calling
select method in java class.
diff -r 7fafd2a9f24b -r 3b26a5dbb00b src-db/database/model/tables/C_ORDERLINE.xml
--- a/src-db/database/model/tables/C_ORDERLINE.xml Wed Sep 13 06:39:31 2017 +0000
+++ b/src-db/database/model/tables/C_ORDERLINE.xml Mon Jan 22 23:36:30 2018 +0530
@@ -390,6 +390,7 @@
</index>
<index name="C_ORDERLINE_ORDER" unique="false">
<index-column name="C_ORDER_ID"/>
+ <index-column name="functionBasedColumn" functionExpression="OBEQUALS(QTYORDERED,QTYINVOICED)"/>
</index>
<index name="C_ORDERLINE_PRODUCT" unique="false">
<index-column name="M_PRODUCT_ID"/>
diff -r 7fafd2a9f24b -r 3b26a5dbb00b src/org/openbravo/erpCommon/ad_reports/ReportOrderNotInvoiceJR.java
--- a/src/org/openbravo/erpCommon/ad_reports/ReportOrderNotInvoiceJR.java Wed Sep 13 06:39:31 2017 +0000
+++ b/src/org/openbravo/erpCommon/ad_reports/ReportOrderNotInvoiceJR.java Mon Jan 22 23:36:30 2018 +0530
@@ -11,7 +11,7 @@
* under the License.
* The Original Code is Openbravo ERP.
* The Initial Developer of the Original Code is Openbravo SLU
- * All portions are Copyright (C) 2001-2015 Openbravo SLU
+ * All portions are Copyright (C) 2001-2018 Openbravo SLU
* All Rights Reserved.
* Contributor(s): ______________________________________.
************************************************************************
@@ -194,11 +194,11 @@
OBError myMessage = null;
myMessage = new OBError();
try {
- data = ReportOrderNotInvoiceData.select(this, strCurrencyId, vars.getLanguage(),
+ data = ReportOrderNotInvoiceData.select(this, strCurrencyId,
Utility.getContext(this, vars, "#User_Client", "ReportOrderNotInvoiceJR"),
Utility.getContext(this, vars, "#AccessibleOrgTree", "ReportOrderNotInvoiceJR"),
strcBpartnetId, strCOrgId, strInvoiceRule, strdateFrom,
- DateTimeData.nDaysAfter(this, strdateTo, "1"));
+ DateTimeData.nDaysAfter(this, strdateTo, "1"), vars.getLanguage());
} catch (ServletException ex) {
myMessage = Utility.translateError(this, vars, vars.getLanguage(), ex.getMessage());
}
diff -r 7fafd2a9f24b -r 3b26a5dbb00b src/org/openbravo/erpCommon/ad_reports/ReportOrderNotInvoice_data.xsql
--- a/src/org/openbravo/erpCommon/ad_reports/ReportOrderNotInvoice_data.xsql Wed Sep 13 06:39:31 2017 +0000
+++ b/src/org/openbravo/erpCommon/ad_reports/ReportOrderNotInvoice_data.xsql Mon Jan 22 23:36:30 2018 +0530
@@ -12,7 +12,7 @@
* under the License.
* The Original Code is Openbravo ERP.
* The Initial Developer of the Original Code is Openbravo SLU
- * All portions are Copyright (C) 2001-2010 Openbravo SLU
+ * All portions are Copyright (C) 2001-2018 Openbravo SLU
* All Rights Reserved.
* Contributor(s): ______________________________________.
************************************************************************
@@ -28,117 +28,117 @@
<SqlMethodComment></SqlMethodComment>
<Sql>
<![CDATA[
- SELECT ORGNAME, C_BPARTNER_ID, BPARTNERNAME, C_ORDER_ID, DOCUMENTNO, DATEORDERED, GRANDTOTAL, CONVGRANDTOTAL, INVOICERULE,
- LINE, PRODUCT, PRICE, CONVPRICE, QTYORDERED, UOMSYMBOL, TAX, TAXBASE, CONVTAXBASE, LINENETAMT, CONVLINENETAMT,
- ORDERCURRENCYSYM, TRANSCURRENCYIDORDER, TRANSDATEORDER, TRANSCLIENTIDORDER, TRANSORGIDORDER,
- LINECURRENCYSYM, TRANSCURRENCYIDLINE, TRANSDATELINE, TRANSCLIENTIDLINE, TRANSORGIDLINE,
- C_CURRENCY_SYMBOL(?, 0, 'Y') AS CONVSYM, C_CURRENCY_ISOSYM(?) AS CONVISOSYM
- FROM (
- SELECT AD_ORG.NAME AS ORGNAME, C_BPARTNER.C_BPARTNER_ID, C_BPARTNER.NAME AS BPARTNERNAME, C_ORDER.C_ORDER_ID, C_ORDER.DOCUMENTNO, C_ORDER.DATEORDERED, C_ORDER.GRANDTOTAL,
- C_CURRENCY_CONVERT(C_ORDER.GRANDTOTAL, C_ORDER.C_CURRENCY_ID, ?, TO_DATE(COALESCE(C_ORDER.DATEORDERED, NOW())), NULL, C_ORDER.AD_CLIENT_ID, C_ORDER.AD_ORG_ID) AS CONVGRANDTOTAL,
- (CASE C_ORDER.INVOICERULE WHEN 'S' THEN COALESCE(AD_REF_LIST_TRL.NAME, AD_REF_LIST.NAME)||' ('||C_INVOICESCHEDULE.NAME||')' ELSE COALESCE(AD_REF_LIST_TRL.NAME, AD_REF_LIST.NAME) END) AS INVOICERULE,
- C_ORDERLINE.LINE AS LINE, COALESCE(M_PRODUCT.NAME, M_PRODUCT.DESCRIPTION) AS PRODUCT, C_ORDERLINE.PRICEACTUAL AS PRICE,
- C_CURRENCY_CONVERT(C_ORDERLINE.PRICEACTUAL, COALESCE(C_ORDERLINE.C_CURRENCY_ID, C_ORDER.C_CURRENCY_ID), ?, TO_DATE(COALESCE(C_ORDER.DATEORDERED, NOW())), NULL, C_ORDERLINE.AD_CLIENT_ID, C_ORDERLINE.AD_ORG_ID) AS CONVPRICE,
- C_ORDERLINE.QTYORDERED - C_ORDERLINE.QTYINVOICED AS QTYORDERED, C_UOM.UOMSYMBOL, NULL AS TAX, NULL AS TAXBASE, NULL AS CONVTAXBASE, C_ORDERLINE.LINENETAMT,
- C_CURRENCY_CONVERT(C_ORDERLINE.LINENETAMT, COALESCE(C_ORDERLINE.C_CURRENCY_ID, C_ORDER.C_CURRENCY_ID), ?, TO_DATE(COALESCE(C_ORDER.DATEORDERED, NOW())), NULL, C_ORDERLINE.AD_CLIENT_ID, C_ORDERLINE.AD_ORG_ID) AS CONVLINENETAMT,
- C_CURRENCY_SYMBOL(C_ORDER.C_CURRENCY_ID, 0, 'Y') AS ORDERCURRENCYSYM,
- C_ORDER.C_CURRENCY_ID AS TRANSCURRENCYIDORDER,
- C_ORDER.DATEORDERED AS TRANSDATEORDER,
- C_ORDER.AD_CLIENT_ID AS TRANSCLIENTIDORDER,
- C_ORDER.AD_ORG_ID AS TRANSORGIDORDER,
- C_CURRENCY_SYMBOL(COALESCE(C_ORDERLINE.C_CURRENCY_ID, C_ORDER.C_CURRENCY_ID), 0, 'Y') AS LINECURRENCYSYM,
- COALESCE(C_ORDERLINE.C_CURRENCY_ID, C_ORDER.C_CURRENCY_ID) AS TRANSCURRENCYIDLINE,
- TO_DATE(COALESCE(C_ORDER.DATEORDERED, NOW())) AS TRANSDATELINE,
- C_ORDERLINE.AD_CLIENT_ID AS TRANSCLIENTIDLINE,
- C_ORDERLINE.AD_ORG_ID AS TRANSORGIDLINE
- FROM C_BPARTNER left join C_INVOICESCHEDULE on C_BPARTNER.C_INVOICESCHEDULE_ID = C_INVOICESCHEDULE.C_INVOICESCHEDULE_ID,
- AD_REF_LIST left join AD_REF_LIST_TRL on AD_REF_LIST.AD_REF_LIST_ID = AD_REF_LIST_TRL.AD_REF_LIST_ID
- and AD_REF_LIST_TRL.AD_LANGUAGE = ?,
- C_ORDERLINE left join M_PRODUCT on C_ORDERLINE.M_PRODUCT_ID=M_PRODUCT.M_PRODUCT_ID,
- C_ORDER, AD_ORG, C_UOM, C_CURRENCY
- WHERE C_ORDER.C_ORDER_ID=C_ORDERLINE.C_ORDER_ID
- AND C_ORDER.C_BPARTNER_ID = C_BPARTNER.C_BPARTNER_ID
- AND C_ORDER.INVOICERULE = AD_REF_LIST.VALUE
- AND C_ORDER.INVOICERULE <> 'N'
- AND C_ORDERLINE.C_UOM_ID = C_UOM.C_UOM_ID
- AND C_ORDER.AD_ORG_ID = AD_ORG.AD_ORG_ID
- AND C_ORDER.C_CURRENCY_ID = C_CURRENCY.C_CURRENCY_ID
- AND AD_REF_LIST.AD_REFERENCE_ID = '150'
- AND C_ORDER.PROCESSED = 'Y'
- AND C_ORDER.DOCSTATUS NOT IN ('CJ', 'UE', 'CA')
- AND C_ORDER.ISSOTRX = 'Y'
- AND C_ORDERLINE.QTYORDERED<>C_ORDERLINE.QTYINVOICED
- AND C_ORDER.AD_Client_ID IN ('1')
- AND C_ORDER.AD_ORG_ID IN ('1')
- AND 1=1
- UNION ALL
- SELECT AD_ORG.NAME AS ORGNAME, C_BPARTNER.C_BPARTNER_ID, C_BPARTNER.NAME AS BPARTNERNAME, C.C_ORDER_ID, C.DOCUMENTNO, C.DATEORDERED, C.GRANDTOTAL,
- C_CURRENCY_CONVERT(C.GRANDTOTAL, C.C_CURRENCY_ID, ?, TO_DATE(COALESCE(C.DATEORDERED, NOW())), NULL, C.AD_CLIENT_ID, C.AD_ORG_ID) AS CONVGRANDTOTAL,
- (CASE C.INVOICERULE WHEN 'S' THEN COALESCE(AD_REF_LIST_TRL.NAME, AD_REF_LIST.NAME)||' ('||C_INVOICESCHEDULE.NAME||')' ELSE COALESCE(AD_REF_LIST_TRL.NAME, AD_REF_LIST.NAME) END) AS INVOICERULE,
- NULL AS LINE , C_TAX.NAME AS PRODUCT, NULL AS PRICE, NULL AS CONVPRICE, NULL AS QTYORDERED, NULL AS UOMSYMBOL,
- C_TAX.RATE AS TAX, C_ORDERTAX.TAXBASEAMT AS TAXBASE,
- C_CURRENCY_CONVERT(C_ORDERTAX.TAXBASEAMT, C.C_CURRENCY_ID, ?, TO_DATE(COALESCE(C.DATEORDERED, NOW())), NULL, C_ORDERTAX.AD_CLIENT_ID, C_ORDERTAX.AD_ORG_ID) AS CONVTAXBASE,
- C_ORDERTAX.TAXAMT AS LINENETAMT,
- C_CURRENCY_CONVERT(C_ORDERTAX.TAXAMT, C.C_CURRENCY_ID, ?, TO_DATE(COALESCE(C.DATEORDERED, NOW())), NULL, C_ORDERTAX.AD_CLIENT_ID, C_ORDERTAX.AD_ORG_ID) AS CONVLINENETAMT,
- C_CURRENCY_SYMBOL(C.C_CURRENCY_ID, 0, 'Y') AS ORDERCURRENCYSYM,
- C.C_CURRENCY_ID AS TRANSCURRENCYIDORDER,
- C.DATEORDERED AS TRANSDATEORDER,
- C.AD_CLIENT_ID AS TRANSCLIENTIDORDER,
- C.AD_ORG_ID AS TRANSORGIDORDER,
- C_CURRENCY_SYMBOL(C.C_CURRENCY_ID, 0, 'Y') AS LINECURRENCYSYM,
- C.C_CURRENCY_ID AS TRANSCURRENCYIDLINE,
- TO_DATE(COALESCE(C.DATEORDERED, NOW())) AS TRANSDATELINE,
- C_ORDERTAX.AD_CLIENT_ID AS TRANSCLIENTIDLINE,
- C_ORDERTAX.AD_ORG_ID AS TRANSORGIDLINE
- FROM C_BPARTNER left join C_INVOICESCHEDULE on C_BPARTNER.C_INVOICESCHEDULE_ID = C_INVOICESCHEDULE.C_INVOICESCHEDULE_ID,
- AD_REF_LIST left join AD_REF_LIST_TRL on AD_REF_LIST.AD_REF_LIST_ID = AD_REF_LIST_TRL.AD_REF_LIST_ID
- AND AD_REF_LIST_TRL.AD_LANGUAGE = ? ,
- C_ORDER C ,C_ORDERTAX, AD_ORG, C_TAX, C_CURRENCY
- WHERE C.C_ORDER_ID=C_ORDERTAX.C_ORDER_ID
- AND C.C_BPARTNER_ID=C_BPARTNER.C_BPARTNER_ID
- AND C.C_CURRENCY_ID = C_CURRENCY.C_CURRENCY_ID
- AND C.INVOICERULE = AD_REF_LIST.VALUE
- AND C.INVOICERULE <> 'N'
- AND AD_REF_LIST.AD_REFERENCE_ID = '150'
- AND C_ORDERTAX.C_TAX_ID = C_TAX.C_TAX_ID
- AND C.AD_ORG_ID = AD_ORG.AD_ORG_ID
- AND C.PROCESSED = 'Y'
- AND C.DOCSTATUS NOT IN ('CJ', 'UE', 'CA')
- AND C.ISSOTRX = 'Y'
- AND EXISTS (SELECT 1 FROM C_ORDER C2, C_ORDERLINE CL
- WHERE C2.c_order_id = C.c_order_id
- and c2.c_order_id=cl.c_order_id
- and cl.QTYORDERED<>cl.QTYINVOICED)
- AND C.AD_Client_ID IN ('2')
- AND C.AD_ORG_ID IN ('2')
- AND 2=2
- ) AAA
- ORDER BY ORGNAME, BPARTNERNAME, DATEORDERED DESC, DOCUMENTNO, LINE
- ]]></Sql>
+ SELECT ORGNAME, C_BPARTNER_ID, BPARTNERNAME, C_ORDER_ID, DOCUMENTNO,
+ DATEORDERED, GRANDTOTAL, CONVGRANDTOTAL, INVOICERULE, LINE, PRODUCT, PRICE, CONVPRICE,
+ QTYORDERED, UOMSYMBOL, TAX, TAXBASE, CONVTAXBASE, LINENETAMT, CONVLINENETAMT,
+ ORDERCURRENCYSYM, TRANSCURRENCYIDORDER, TRANSDATEORDER, TRANSCLIENTIDORDER, TRANSORGIDORDER,
+ LINECURRENCYSYM, TRANSCURRENCYIDLINE, TRANSDATELINE, TRANSCLIENTIDLINE,
+ TRANSORGIDLINE, C_CURRENCY_SYMBOL(?, 0, 'Y') AS CONVSYM, C_CURRENCY_ISOSYM(?) AS CONVISOSYM
+ FROM
+ (
+ WITH pendingOrders AS
+ (
+ SELECT C.C_ORDER_ID, C.DOCUMENTNO, C.DATEORDERED, C.GRANDTOTAL,
+ C.C_CURRENCY_ID, C.AD_CLIENT_ID, C.AD_ORG_ID, C.C_BPARTNER_ID, C.INVOICERULE,
+ CL.LINE, CL.AD_CLIENT_ID AS CL_AD_CLIENT_ID, CL.AD_ORG_ID AS CL_AD_ORG_ID,
+ CL.C_CURRENCY_ID AS CL_C_CURRENCY_ID, CL.LINENETAMT, CL.QTYORDERED,
+ CL.QTYINVOICED, CL.PRICEACTUAL, CL.M_PRODUCT_ID, CL.C_UOM_ID,
+ AD_REF_LIST.AD_REF_LIST_ID,
+ AD_REF_LIST.NAME AS INVOICERULENAME
+ FROM c_order C JOIN C_Orderline CL on CL.C_Order_ID = C.C_Order_ID
+ JOIN AD_REF_LIST ON C.INVOICERULE = AD_REF_LIST.VALUE
+ AND AD_REF_LIST.AD_REFERENCE_ID = '150'
+ WHERE C.invoicerule <> 'N'
+ AND C.processed = 'Y'
+ AND C.docstatus NOT IN ( 'CJ', 'UE', 'CA', 'DR', 'CL')
+ AND C.issotrx = 'Y'
+ AND OBEQUALS(cl.qtyordered, cl.qtyinvoiced) = 'N'
+ AND C.AD_Client_ID IN ('1')
+ AND C.AD_ORG_ID IN ('1')
+ AND 1=1
+ )
+ SELECT AD_ORG.NAME AS ORGNAME, C_BPARTNER.C_BPARTNER_ID, C_BPARTNER.NAME AS BPARTNERNAME,
+ pendingOrders.C_ORDER_ID, pendingOrders.DOCUMENTNO, pendingOrders.DATEORDERED,
+ pendingOrders.GRANDTOTAL,
+ CASE WHEN pendingorders.C_CURRENCY_ID = ? THEN pendingorders.GRANDTOTAL ELSE C_CURRENCY_CONVERT(pendingorders.GRANDTOTAL, pendingorders.C_CURRENCY_ID, ?, TO_DATE(COALESCE(pendingorders.DATEORDERED, NOW())), NULL, pendingorders.AD_CLIENT_ID, pendingorders.AD_ORG_ID) END AS CONVGRANDTOTAL,
+ CASE WHEN pendingOrders.INVOICERULE = 'S' THEN COALESCE(AD_REF_LIST_TRL.NAME, pendingOrders.INVOICERULENAME)||' ('||C_INVOICESCHEDULE.NAME||')'
+ ELSE COALESCE(AD_REF_LIST_TRL.NAME, pendingOrders.INVOICERULENAME) END AS INVOICERULE,
+ pendingOrders.LINE AS LINE,
+ COALESCE(P.NAME, P.DESCRIPTION) AS PRODUCT, pendingOrders.PRICEACTUAL AS PRICE,
+ CASE WHEN COALESCE(pendingOrders.CL_C_CURRENCY_ID, pendingorders.C_CURRENCY_ID) = ? THEN pendingOrders.PRICEACTUAL ELSE C_CURRENCY_CONVERT(pendingOrders.PRICEACTUAL, COALESCE(pendingOrders.CL_C_CURRENCY_ID, pendingorders.C_CURRENCY_ID), ?, TO_DATE(COALESCE(pendingorders.DATEORDERED, NOW())), NULL, pendingOrders.CL_AD_CLIENT_ID, pendingOrders.CL_AD_ORG_ID) END AS CONVPRICE,
+ pendingOrders.QTYORDERED - pendingOrders.QTYINVOICED AS QTYORDERED,
+ U.UOMSYMBOL, NULL AS TAX, NULL AS TAXBASE, NULL AS CONVTAXBASE, pendingOrders.LINENETAMT,
+ CASE WHEN COALESCE(pendingOrders.CL_C_CURRENCY_ID, pendingorders.C_CURRENCY_ID) = ? THEN pendingOrders.LINENETAMT ELSE C_CURRENCY_CONVERT(pendingOrders.LINENETAMT, COALESCE(pendingOrders.CL_C_CURRENCY_ID, pendingorders.C_CURRENCY_ID), ?, TO_DATE(COALESCE(pendingorders.DATEORDERED, NOW())), NULL, pendingOrders.CL_AD_CLIENT_ID, pendingOrders.CL_AD_ORG_ID) END AS CONVLINENETAMT,
+ C_CURRENCY_SYMBOL(pendingorders.C_CURRENCY_ID, 0, 'Y') AS ORDERCURRENCYSYM,
+ pendingorders.C_CURRENCY_ID AS TRANSCURRENCYIDORDER, pendingorders.DATEORDERED AS TRANSDATEORDER,
+ pendingorders.AD_CLIENT_ID AS TRANSCLIENTIDORDER, pendingorders.AD_ORG_ID AS TRANSORGIDORDER,
+ C_CURRENCY_SYMBOL(COALESCE(pendingOrders.CL_C_CURRENCY_ID, pendingorders.C_CURRENCY_ID), 0, 'Y') AS LINECURRENCYSYM,
+ COALESCE(pendingOrders.CL_C_CURRENCY_ID, pendingorders.C_CURRENCY_ID) AS TRANSCURRENCYIDLINE,
+ TO_DATE(COALESCE(pendingorders.DATEORDERED, NOW())) AS TRANSDATELINE,
+ pendingOrders.CL_AD_CLIENT_ID AS TRANSCLIENTIDLINE, pendingOrders.CL_AD_ORG_ID AS TRANSORGIDLINE
+ FROM pendingOrders join m_product p on p.m_product_id = pendingOrders.m_product_id
+ join c_uom u on u.c_uom_id = pendingOrders.c_uom_id
+ LEFT JOIN AD_REF_LIST_TRL ON pendingOrders.AD_REF_LIST_ID = AD_REF_LIST_TRL.AD_REF_LIST_ID
+ AND AD_REF_LIST_TRL.AD_LANGUAGE = ?,
+ C_BPARTNER LEFT JOIN C_INVOICESCHEDULE ON C_BPARTNER.C_INVOICESCHEDULE_ID = C_INVOICESCHEDULE.C_INVOICESCHEDULE_ID,
+ AD_ORG
+ WHERE pendingorders.C_BPARTNER_ID = C_BPARTNER.C_BPARTNER_ID
+ AND pendingorders.AD_ORG_ID = AD_ORG.AD_ORG_ID
+
+ UNION ALL
+
+ SELECT MAX(AD_ORG.NAME) AS ORGNAME, MAX(C_BPARTNER.C_BPARTNER_ID) AS C_BPARTNER_ID, MAX(C_BPARTNER.NAME) AS BPARTNERNAME,
+ pendingOrders.C_ORDER_ID AS C_ORDER_ID, MAX(pendingOrders.DOCUMENTNO) AS DOCUMENTNO,
+ MAX(pendingOrders.DATEORDERED) AS DATEORDERED, MAX(pendingOrders.GRANDTOTAL) AS GRANDTOTAL,
+ CASE WHEN MAX(pendingOrders.C_CURRENCY_ID) = ? THEN MAX(pendingOrders.GRANDTOTAL)
+ ELSE C_CURRENCY_CONVERT(MAX(pendingOrders.GRANDTOTAL), MAX(pendingOrders.C_CURRENCY_ID), ?, TO_DATE(COALESCE(MAX(pendingOrders.DATEORDERED), NOW())), NULL, MAX(pendingOrders.AD_CLIENT_ID), MAX(pendingOrders.AD_ORG_ID)) END AS CONVGRANDTOTAL,
+ CASE WHEN MAX(pendingOrders.INVOICERULE) = 'S'
+ THEN COALESCE(MAX(AD_REF_LIST_TRL.NAME), MAX(pendingOrders.INVOICERULENAME))||' ('||MAX(C_INVOICESCHEDULE.NAME)||')'
+ ELSE COALESCE(MAX(AD_REF_LIST_TRL.NAME), MAX(pendingOrders.INVOICERULENAME)) END AS INVOICERULE,
+ NULL AS LINE, MAX(C_TAX.NAME) AS PRODUCT, NULL AS PRICE, NULL AS CONVPRICE, NULL AS QTYORDERED, NULL AS UOMSYMBOL,
+ MAX(C_TAX.RATE) AS TAX, MAX(C_ORDERTAX.TAXBASEAMT) AS TAXBASE,
+ CASE WHEN MAX(pendingOrders.C_CURRENCY_ID) = ? THEN MAX(C_ORDERTAX.TAXBASEAMT) ELSE C_CURRENCY_CONVERT(MAX(C_ORDERTAX.TAXBASEAMT), MAX(pendingOrders.C_CURRENCY_ID), ?, TO_DATE(COALESCE(MAX(pendingOrders.DATEORDERED), NOW())), NULL, MAX(C_ORDERTAX.AD_CLIENT_ID), MAX(C_ORDERTAX.AD_ORG_ID)) END AS CONVTAXBASE,
+ MAX(C_ORDERTAX.TAXAMT) AS LINENETAMT,
+ CASE WHEN MAX(pendingOrders.C_CURRENCY_ID) = ? THEN MAX(C_ORDERTAX.TAXAMT) ELSE C_CURRENCY_CONVERT(MAX(C_ORDERTAX.TAXAMT), MAX(pendingOrders.C_CURRENCY_ID), ?, TO_DATE(COALESCE(MAX(pendingOrders.DATEORDERED), NOW())), NULL, MAX(C_ORDERTAX.AD_CLIENT_ID), MAX(C_ORDERTAX.AD_ORG_ID)) END AS CONVLINENETAMT,
+ C_CURRENCY_SYMBOL(MAX(pendingOrders.C_CURRENCY_ID), 0, 'Y') AS ORDERCURRENCYSYM, MAX(pendingOrders.C_CURRENCY_ID) AS TRANSCURRENCYIDORDER,
+ MAX(pendingOrders.DATEORDERED) AS TRANSDATEORDER, MAX(pendingOrders.AD_CLIENT_ID) AS TRANSCLIENTIDORDER,
+ MAX(pendingOrders.AD_ORG_ID) AS TRANSORGIDORDER, C_CURRENCY_SYMBOL(MAX(pendingOrders.C_CURRENCY_ID), 0, 'Y') AS LINECURRENCYSYM,
+ MAX(pendingOrders.C_CURRENCY_ID) AS TRANSCURRENCYIDLINE, TO_DATE(COALESCE(MAX(pendingOrders.DATEORDERED), NOW())) AS TRANSDATELINE,
+ MAX(C_ORDERTAX.AD_CLIENT_ID) AS TRANSCLIENTIDLINE, MAX(C_ORDERTAX.AD_ORG_ID) AS TRANSORGIDLINE
+ FROM C_BPARTNER LEFT JOIN C_INVOICESCHEDULE ON C_BPARTNER.C_INVOICESCHEDULE_ID = C_INVOICESCHEDULE.C_INVOICESCHEDULE_ID,
+ C_ORDERTAX, AD_ORG, C_TAX,
+ pendingOrders left join AD_REF_LIST_TRL ON pendingOrders.AD_REF_LIST_ID = AD_REF_LIST_TRL.AD_REF_LIST_ID
+ AND AD_REF_LIST_TRL.AD_LANGUAGE = ?
+ WHERE pendingOrders.C_ORDER_ID = C_ORDERTAX.C_ORDER_ID
+ AND pendingOrders.C_BPARTNER_ID=C_BPARTNER.C_BPARTNER_ID
+ AND C_ORDERTAX.C_TAX_ID = C_TAX.C_TAX_ID
+ AND pendingOrders.AD_ORG_ID = AD_ORG.AD_ORG_ID
+ GROUP BY pendingOrders.C_ORDER_ID, C_ORDERTAX.C_TAX_ID
+ ) AAA ORDER BY ORGNAME, BPARTNERNAME, DATEORDERED DESC, DOCUMENTNO, LINE
+ ]]></Sql>
<Parameter name="cCurrencyConv"/>
<Parameter name="cCurrencyConv"/>
+ <Parameter name="adUserClient" type="replace" optional="true" after="C.AD_Client_ID IN (" text="'1'"/>
+ <Parameter name="adUserOrg" type="replace" optional="true" after="C.AD_ORG_ID IN (" text="'1'"/>
+ <Parameter name="cBpartnerId" optional="true" after="1=1">AND C.C_BPARTNER_ID = ?</Parameter>
+ <Parameter name="cOrgId" optional="true" after="1=1">AND C.AD_ORG_ID = ?</Parameter>
+ <Parameter name="invoiceRule" optional="true" after="1=1">AND C.INVOICERULE = ?</Parameter>
+ <Parameter name="dateFrom" optional="true" after="1=1"><![CDATA[ AND C.DATEORDERED >= to_date(?) ]]></Parameter>
+ <Parameter name="dateTo" optional="true" after="1=1"><![CDATA[ AND C.DATEORDERED < to_date(?) ]]></Parameter>
<Parameter name="cCurrencyConv"/>
<Parameter name="cCurrencyConv"/>
<Parameter name="cCurrencyConv"/>
+ <Parameter name="cCurrencyConv"/>
+ <Parameter name="cCurrencyConv"/>
+ <Parameter name="cCurrencyConv"/>
+ <Parameter name="adLanguage"/>
+ <Parameter name="cCurrencyConv"/>
+ <Parameter name="cCurrencyConv"/>
+ <Parameter name="cCurrencyConv"/>
+ <Parameter name="cCurrencyConv"/>
+ <Parameter name="cCurrencyConv"/>
+ <Parameter name="cCurrencyConv"/>
<Parameter name="adLanguage"/>
- <Parameter name="adUserClient" type="replace" optional="true" after="C_ORDER.AD_Client_ID IN (" text="'1'"/>
- <Parameter name="adUserOrg" type="replace" optional="true" after="C_ORDER.AD_ORG_ID IN (" text="'1'"/>
- <Parameter name="cBpartnerId" optional="true" after="1=1">AND C_ORDER.C_BPARTNER_ID = ?</Parameter>
- <Parameter name="cOrgId" optional="true" after="1=1">AND C_ORDER.AD_ORG_ID = ?</Parameter>
- <Parameter name="invoiceRule" optional="true" after="1=1">AND C_ORDER.INVOICERULE = ?</Parameter>
- <Parameter name="dateFrom" optional="true" after="1=1"><![CDATA[ AND C_ORDER.DATEORDERED >= to_date(?) ]]></Parameter>
- <Parameter name="dateTo" optional="true" after="1=1"><![CDATA[ AND C_ORDER.DATEORDERED < to_date(?) ]]></Parameter>
- <Parameter name="cCurrencyConv"/>
- <Parameter name="cCurrencyConv"/>
- <Parameter name="cCurrencyConv"/>
- <Parameter name="adLanguage"/>
- <Parameter name="adUserClient" type="replace" optional="true" after="C.AD_Client_ID IN (" text="'2'"/>
- <Parameter name="adUserOrg" type="replace" optional="true" after="C.AD_ORG_ID IN (" text="'2'"/>
- <Parameter name="cBpartnerId" optional="true" after="2=2">AND C.C_BPARTNER_ID = ?</Parameter>
- <Parameter name="cOrgId" optional="true" after="2=2">AND C.AD_ORG_ID = ?</Parameter>
- <Parameter name="invoiceRule" optional="true" after="2=2">AND C.INVOICERULE = ?</Parameter>
- <Parameter name="dateFrom" optional="true" after="2=2"><![CDATA[ AND C.DATEORDERED >= to_date(?) ]]></Parameter>
- <Parameter name="dateTo" optional="true" after="2=2"><![CDATA[ AND C.DATEORDERED < to_date(?) ]]></Parameter>
</SqlMethod>
<SqlMethod name="bPartnerDescription" type="preparedStatement" return="String" default="">
<SqlMethodComment></SqlMethodComment>
| |||||||
 Relationships [ Relation Graph ]
[ Dependency Graph ]
|
||||||||
|
||||||||
 Relationships |
|
Notes |
|
|
(0101139) JONHM (viewer) 2017-12-19 17:09 |
See file https://code.openbravo.com/erp/devel/pi/file/tip/modules/org.openbravo.reports.ordersawaitingdelivery/src/org/openbravo/reports/ordersawaitingdelivery/erpCommon/ad_reports/ReportOrderNotShipped_data.xsql [^] |
|
(0101827) hgbot (developer) 2018-01-22 11:31 |
Repository: erp/devel/pi Changeset: 1f43ecba24a4849831199964da55739b71ee35d0 Author: Atul Gaware <atul.gaware <at> openbravo.com> Date: Mon Jan 22 00:18:10 2018 +0530 URL: http://code.openbravo.com/erp/devel/pi/rev/1f43ecba24a4849831199964da55739b71ee35d0 [^] Fixes Issue 37515:Performance issue in Orders Awaiting Invoice Report ** Split the query using CTE for pending orders to be invoiced. ** Group by Taxes sub query on Order ID and Tax ID to void duplicates. ** Modify C_OrderLine_Order index by adding function based column OBEQUALS(QTYORDERED,QTYINVOICED) ** Rearrange parameters in xsql accordingly and pass them while calling select method in java class. ** Filters Orders to remove the ones in Draft and Closed Status. --- M src-db/database/model/tables/C_ORDERLINE.xml M src/org/openbravo/erpCommon/ad_reports/ReportOrderNotInvoiceJR.java M src/org/openbravo/erpCommon/ad_reports/ReportOrderNotInvoice_data.xsql --- |
|
(0101829) dmiguelez (viewer) 2018-01-22 11:37 |
Code Review + Testing Ok |
|
(0101858) AtulOpenbravo (viewer) 2018-01-22 18:34 edited on: 2018-01-22 18:36 |
- Login on the environment provided by support: - Open Orders Awaiting Invoice Report and set: * From date: 10-08-2017 * To date: 13-09-2017 - Reports takes approximtaely 5 sec to show output. Earlier it took around 18 secs. Time taken to fetch data for select method in java class is as below:- Run#1:- ReportOrderNotInvoiceData.select takes 2768 ms. Run#2:- ReportOrderNotInvoiceData.select takes 2563 ms. Run#3:- ReportOrderNotInvoiceData.select takes 2475 ms. |
|
(0102672) hudsonbot (viewer) 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 16:48 | JONHM | New Issue | |
| 2017-12-19 16:48 | JONHM | Assigned To | => Triage Finance |
| 2017-12-19 16:48 | JONHM | File Added: query.sql | |
| 2017-12-19 16:48 | JONHM | OBNetwork customer | => Yes |
| 2017-12-19 16:48 | JONHM | Modules | => Core |
| 2017-12-19 16:48 | JONHM | Support ticket | => 48552 |
| 2017-12-19 16:48 | JONHM | Resolution time | => 1515452400 |
| 2017-12-19 16:48 | JONHM | Triggers an Emergency Pack | => No |
| 2017-12-19 16:49 | JONHM | Proposed Solution updated | |
| 2017-12-19 16:49 | JONHM | File Added: query_plan | |
| 2017-12-19 16:55 | JONHM | Steps to Reproduce Updated | View Revisions |
| 2017-12-19 16:58 | JONHM | Summary | Performance issue in Orders Awaiting Delivery Report => Performance issue in Orders Awaiting Invoice Report |
| 2017-12-19 16:58 | JONHM | Description Updated | View Revisions |
| 2017-12-19 16:58 | JONHM | Steps to Reproduce Updated | View Revisions |
| 2017-12-19 17:02 | JONHM | File Deleted: query.sql | |
| 2017-12-19 17:03 | JONHM | File Added: query.sql | |
| 2017-12-19 17:09 | JONHM | Note Added: 0101139 | |
| 2017-12-19 17:11 | aferraz | Relationship added | related to 0037516 |
| 2017-12-19 17:53 | dmiguelez | Assigned To | Triage Finance => AtulOpenbravo |
| 2018-01-15 09:55 | AtulOpenbravo | Status | new => scheduled |
| 2018-01-22 11:31 | hgbot | Checkin | |
| 2018-01-22 11:31 | hgbot | Note Added: 0101827 | |
| 2018-01-22 11:31 | hgbot | Status | scheduled => resolved |
| 2018-01-22 11:31 | hgbot | Resolution | open => fixed |
| 2018-01-22 11:31 | hgbot | Fixed in SCM revision | => http://code.openbravo.com/erp/devel/pi/rev/1f43ecba24a4849831199964da55739b71ee35d0 [^] |
| 2018-01-22 11:37 | dmiguelez | Review Assigned To | => dmiguelez |
| 2018-01-22 11:37 | dmiguelez | Note Added: 0101829 | |
| 2018-01-22 11:37 | dmiguelez | Status | resolved => closed |
| 2018-01-22 11:37 | dmiguelez | Fixed in Version | => 3.0PR18Q2 |
| 2018-01-22 18:34 | AtulOpenbravo | Note Added: 0101858 | |
| 2018-01-22 18:35 | AtulOpenbravo | File Added: Final_37515_Query_QueryPlan_CTE_OBEQUALS_Index.txt | |
| 2018-01-22 18:36 | AtulOpenbravo | Note Edited: 0101858 | View Revisions |
| 2018-01-22 19:12 | AtulOpenbravo | File Added: ClientEnvironment37515Fix.diff | |
| 2018-02-22 18:18 | hudsonbot | Checkin | |
| 2018-02-22 18:18 | hudsonbot | Note Added: 0102672 | |
|
Issue History |
| Copyright © 2000 - 2009 MantisBT Group |
 |