|
(0074050)
|
|
egoitz
|
|
2015-02-03 18:10
|
|
The following is the query generated on execution time form the hsql of the pick&execute for the grid for orders/invoices. That is doing different sequencial scans
SELECT array_to_string(array_agg(fin_paymen0_.FIN_Payment_Scheduledetail_ID), ',') AS col_0_0_,
array_to_string(array_agg(order2_.DocumentNo), ',') AS col_1_0_,
invoice7_.DocumentNo AS col_2_0_,
coalesce(fin_paymen6_.Fin_Paymentmethod_ID, fin_paymen1_.Fin_Paymentmethod_ID) AS col_3_0_,
coalesce(fin_paymen10_.Name, fin_paymen5_.Name) AS col_4_0_,
coalesce(businesspa8_.C_BPartner_ID, businesspa3_.C_BPartner_ID) AS col_5_0_,
coalesce(businesspa8_.Name, businesspa3_.Name) AS col_6_0_,
coalesce(fin_paymen6_.ExpectedDate, fin_paymen1_.ExpectedDate) AS col_7_0_,
max(coalesce(fin_paymen6_.Amount, fin_paymen1_.Amount)) AS col_8_0_,
max(coalesce(invoice7_.GrandTotal, 0)) AS col_9_0_,
sum(fin_paymen0_.Amount+fin_paymen0_.Writeoffamt) AS col_10_0_,
coalesce(sum(fin_paymen13_.Amount), 0) AS col_11_0_,
CASE
WHEN sum(fin_paymen0_.Writeoffamt)<>0 THEN TRUE
ELSE FALSE
END AS col_12_0_,
CASE
WHEN max(fin_paymen14_.Fin_Payment_ID) IS NOT NULL THEN TRUE
ELSE FALSE
END AS col_13_0_
FROM FIN_Payment_ScheduleDetail fin_paymen0_
LEFT OUTER JOIN FIN_Payment_Schedule fin_paymen1_ ON fin_paymen0_.FIN_Payment_Schedule_Order=fin_paymen1_.Fin_Payment_Schedule_ID
LEFT OUTER JOIN C_Order order2_ ON fin_paymen1_.C_Order_ID=order2_.C_Order_ID
LEFT OUTER JOIN C_BPartner businesspa3_ ON order2_.C_BPartner_ID=businesspa3_.C_BPartner_ID
LEFT OUTER JOIN FIN_Payment_Priority fin_paymen4_ ON fin_paymen1_.FIN_Payment_Priority_ID=fin_paymen4_.FIN_Payment_Priority_ID
LEFT OUTER JOIN FIN_PaymentMethod fin_paymen5_ ON fin_paymen1_.Fin_Paymentmethod_ID=fin_paymen5_.Fin_Paymentmethod_ID
LEFT OUTER JOIN FIN_Payment_Schedule fin_paymen6_ ON fin_paymen0_.FIN_Payment_Schedule_Invoice=fin_paymen6_.Fin_Payment_Schedule_ID
LEFT OUTER JOIN C_Invoice invoice7_ ON fin_paymen6_.C_Invoice_ID=invoice7_.C_Invoice_ID
LEFT OUTER JOIN C_BPartner businesspa8_ ON invoice7_.C_BPartner_ID=businesspa8_.C_BPartner_ID
LEFT OUTER JOIN FIN_Payment_Priority fin_paymen9_ ON fin_paymen6_.FIN_Payment_Priority_ID=fin_paymen9_.FIN_Payment_Priority_ID
LEFT OUTER JOIN FIN_PaymentMethod fin_paymen10_ ON fin_paymen6_.Fin_Paymentmethod_ID=fin_paymen10_.Fin_Paymentmethod_ID
LEFT OUTER JOIN AD_Org organizati11_ ON fin_paymen0_.AD_Org_ID=organizati11_.AD_Org_ID
LEFT OUTER JOIN AD_OrgInfo organizati12_ ON organizati11_.AD_Org_ID=organizati12_.AD_Org_ID
LEFT OUTER JOIN FIN_Payment_Detail fin_paymen13_ ON fin_paymen0_.FIN_Payment_Detail_ID=fin_paymen13_.Fin_Payment_Detail_ID
LEFT OUTER JOIN FIN_Payment fin_paymen14_ ON fin_paymen13_.Fin_Payment_ID=fin_paymen14_.Fin_Payment_ID
WHERE (fin_paymen0_.AD_Client_ID IN ('0',
'7273232E589944E3B2C314CCFA24CE51'))
AND (fin_paymen0_.AD_Org_ID IN ('93026108D6314843BDAF1E2C9582029E',
'52096B938D1B4A0BBED92CC51175D8B5',
'BB2D2052246D434196A576FF79FA93B7',
'C640F59345554138AA2278DA89B3D525',
'4F5E7419E38A437B9381C738F4891E3F',
'816D6E5805944BAEB234679DF99B5859',
'951205728A984C9585E3C2179B4F2418',
'C9040AD8D77642D98F89A5201E1EE1F5',
'190B2FF54907429591CE631C2A03BF24',
'CFC24A4468734E5D9A05A7ACD63C78F5',
'725320F3DF8A4FD7A35F43B38CF7427C',
'B9886DDD96124C5B8BE0F7BFADE5E3C3',
'DEDB6F837E5F401DA3A5568D5E15A8C7',
'A79072D955404CB3BC5F79C455560A62',
'00CC3456888E4B7889C7104D6384D307',
'D448757A4607489C8915CDC5469CC1DD',
'1860BD190DCC4A3BA8441B7D55178E61',
'16510591DECE46039236AD9E96D7FB55',
'994E4D2FA96A4C66B063E0A58E009481',
'0',
'A598837D7B0446649105844584E6565A',
'9DE20F09DABA46BFA99DAFA52D9A6624',
'9C753DB213FA4F8B9FADA2136DFEF83E',
'926E25B8E6C8455AB36BC04738EEF394',
'77A6A6E722384AF884E80AC54D0E9A14',
'19BB9330D92F4D12AA94DF1D26C1C643'))
AND (fin_paymen0_.FIN_Payment_Detail_ID IS NULL
OR fin_paymen14_.Fin_Payment_ID='075F0B8695E247748210C927D0EAB037'
AND (fin_paymen6_.Fin_Payment_Schedule_ID IS NOT NULL
OR fin_paymen1_.Fin_Payment_Schedule_ID IS NOT NULL))
AND (organizati12_.AD_Org_ID IS NULL
OR organizati12_.IsActive='Y')
AND (fin_paymen14_.Fin_Payment_ID='075F0B8695E247748210C927D0EAB037'
OR (invoice7_.C_Invoice_ID IS NOT NULL)
AND businesspa8_.C_BPartner_ID='05DBE41BC3294E5EA0A96DB004E255A8'
AND invoice7_.IsSOTrx='N'
AND invoice7_.C_Currency_ID='303')
GROUP BY invoice7_.DocumentNo,
coalesce(fin_paymen6_.Fin_Paymentmethod_ID, fin_paymen1_.Fin_Paymentmethod_ID),
coalesce(fin_paymen10_.Name, fin_paymen5_.Name),
coalesce(fin_paymen6_.ExpectedDate, fin_paymen1_.ExpectedDate),
coalesce(fin_paymen9_.Priority, fin_paymen4_.Priority),
coalesce(businesspa8_.C_BPartner_ID, businesspa3_.C_BPartner_ID),
coalesce(businesspa8_.Name, businesspa3_.Name)
HAVING upper(coalesce(fin_paymen10_.Name, fin_paymen5_.Name)) LIKE upper('%Bank%Transfer%') ESCAPE '|'
ORDER BY CASE
WHEN max(fin_paymen14_.Fin_Payment_ID) IS NOT NULL THEN 0
ELSE 1
END,
coalesce(fin_paymen9_.Priority, fin_paymen4_.Priority),
coalesce(fin_paymen6_.ExpectedDate, fin_paymen1_.ExpectedDate),
invoice7_.DocumentNo LIMIT '76'; |
|