Advertisement
tko_pb

mas charis 2.sql

Mar 10th, 2022
1,016
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. select * from (
  2. select
  3.     krl.m_product_id as item_id,
  4.     co.c_order_id, -- c_order_id asal bukan additional
  5.     mpc.isfabric,
  6.     cu.uomsymbol,
  7.     cu.c_uom_id,
  8.     sum(krl.qtyentered) as qty_delivered,
  9.     0 as foc,      
  10.     0 as additional,   
  11.     krl.kst_receipt_id,
  12.     cb.c_bpartner_id,
  13.     cb."name" as bp_name,
  14.     kr.m_warehouse_id,
  15.     case when (krl.ref_orderline_id is not null)
  16.         then krl.ref_orderline_id
  17.     else krl.c_orderline_id end as c_orderline_id,
  18.     col.qtyentered,
  19.     ki.kst_ETDDate::date  as ex_factory_date,
  20.     coalesce (kie.eta_date::date, ki.kst_ETDDate::date) as eta_date,
  21.     mpc.value,
  22.     mpc.m_product_category_id,
  23.     mpc.isfabric,
  24.     krl.kst_noroll,
  25.     cd.c_doctype_id
  26. from kst_receiptline krl
  27. join kst_receipt kr on krl.kst_receipt_id = kr.kst_receipt_id
  28. join kst_invoicevendor ki on kr.kst_invoicevendor_id = ki.kst_invoicevendor_id
  29. join kst_invoicevendor_exim kie on ki.kst_invoicevendor_id = kie.kst_invoicevendor_id
  30. join c_orderline col on krl.c_orderline_id = col.c_orderline_id and (krl.ref_orderline_id is null or krl.ref_orderline_id <1)
  31. join c_bpartner cb on kr.c_bpartner_id = cb.c_bpartner_id
  32. join c_order co on col.c_order_id = co.c_order_id
  33. join m_product mp on krl.m_product_id = mp.m_product_id
  34. join m_product_category mpc on mp.m_product_category_id = mpc.m_product_category_id
  35. join c_uom cu on krl.c_uom_id = cu.c_uom_id
  36. join c_doctype cd on co.c_doctype_id = cd.c_doctype_id
  37. group by krl.kst_receipt_id,
  38.     krl.m_product_id,
  39.     co.c_order_id,
  40.     mpc.isfabric,
  41.     cu.c_uom_id,
  42.     cb.c_bpartner_id,
  43.     kr.m_warehouse_id,
  44.     col.c_orderline_id,
  45.     case when (krl.ref_orderline_id is not null)
  46.         then krl.ref_orderline_id
  47.     else krl.c_orderline_id end,
  48.     ki.kst_ETDDate,
  49.     kie.eta_date,
  50.     mpc.m_product_category_id,
  51.     krl.kst_noroll,
  52.     cd.c_doctype_id,
  53.     krl.ref_orderline_id
  54. union
  55.     select
  56.     krl.m_product_id as item_id,
  57.     co.c_order_id, -- c_order_id asal bukan additional
  58.     mpc.isfabric,
  59.     cu.uomsymbol,
  60.     cu.c_uom_id,
  61.     0 as qty_delivered,
  62.     sum(krl.qtyentered) as foc,    
  63.     0 as additional,   
  64.     krl.kst_receipt_id,
  65.     cb.c_bpartner_id,
  66.     cb."name" as bp_name,
  67.     kr.m_warehouse_id,
  68.     case when (krl.ref_orderline_id is not null)
  69.         then krl.ref_orderline_id
  70.     else krl.c_orderline_id end as c_orderline_id,
  71.     col.qtyentered,
  72.     ki.kst_ETDDate::date  as ex_factory_date,
  73.     coalesce (kie.eta_date::date, ki.kst_ETDDate::date) as eta_date,
  74.     mpc.value,
  75.     mpc.m_product_category_id,
  76.     mpc.isfabric,
  77.     krl.kst_noroll,
  78.     cd.c_doctype_id
  79. from kst_receiptline krl
  80. join kst_receipt kr on krl.kst_receipt_id = kr.kst_receipt_id
  81. join kst_invoicevendor ki on kr.kst_invoicevendor_id = ki.kst_invoicevendor_id
  82. join kst_invoicevendor_exim kie on ki.kst_invoicevendor_id = kie.kst_invoicevendor_id
  83. join c_orderline col on krl.c_orderline_id = col.c_orderline_id
  84. join c_bpartner cb on kr.c_bpartner_id = cb.c_bpartner_id
  85. join c_order co on col.c_order_id = co.c_order_id
  86. join m_product mp on krl.m_product_id = mp.m_product_id
  87. join m_product_category mpc on mp.m_product_category_id = mpc.m_product_category_id
  88. join c_uom cu on krl.c_uom_id = cu.c_uom_id
  89. join c_doctype cd on co.c_doctype_id = cd.c_doctype_id
  90. where krl.isfockst = 'Y'
  91. group by krl.kst_receipt_id,
  92.     krl.m_product_id,
  93.     co.c_order_id,
  94.     mpc.isfabric,
  95.     cu.c_uom_id,
  96.     cb.c_bpartner_id,
  97.     kr.m_warehouse_id,
  98.     col.c_orderline_id,
  99.     case when (krl.ref_orderline_id is not null)
  100.         then krl.ref_orderline_id
  101.     else krl.c_orderline_id end,
  102.     ki.kst_ETDDate,
  103.     kie.eta_date,
  104.     mpc.m_product_category_id,
  105.     krl.kst_noroll,
  106.     cd.c_doctype_id,
  107.     krl.ref_orderline_id
  108. union  
  109. select
  110.     krl.m_product_id as item_id,
  111.     co.c_order_id, -- c_order_id asal bukan additional
  112.     mpc.isfabric,
  113.     cu.uomsymbol,
  114.     cu.c_uom_id,
  115.     0 as qty_delivered,
  116.     0 as foc,      
  117.     sum(krl.qtyentered) as additional, 
  118.     krl.kst_receipt_id,
  119.     cb.c_bpartner_id,
  120.     cb."name" as bp_name,
  121.     kr.m_warehouse_id,
  122.     case when (krl.ref_orderline_id is not null)
  123.         then krl.ref_orderline_id
  124.     else krl.c_orderline_id end as c_orderline_id,
  125.     col.qtyentered,
  126.     ki.kst_ETDDate::date  as ex_factory_date,
  127.     coalesce (kie.eta_date::date, ki.kst_ETDDate::date) as eta_date,
  128.     mpc.value,
  129.     mpc.m_product_category_id,
  130.     mpc.isfabric,
  131.     krl.kst_noroll,
  132.     cd.c_doctype_id
  133. from kst_receiptline krl
  134. join kst_receipt kr on krl.kst_receipt_id = kr.kst_receipt_id
  135. join kst_invoicevendor ki on kr.kst_invoicevendor_id = ki.kst_invoicevendor_id
  136. join kst_invoicevendor_exim kie on ki.kst_invoicevendor_id = kie.kst_invoicevendor_id
  137. join c_orderline col on krl.c_orderline_id = col.c_orderline_id
  138. join c_bpartner cb on kr.c_bpartner_id = cb.c_bpartner_id
  139. join c_order co on col.c_order_id = co.c_order_id
  140. join m_product mp on krl.m_product_id = mp.m_product_id
  141. join m_product_category mpc on mp.m_product_category_id = mpc.m_product_category_id
  142. join c_uom cu on krl.c_uom_id = cu.c_uom_id
  143. join c_doctype cd on co.c_doctype_id = cd.c_doctype_id
  144. where krl.isfockst = 'N' and krl.ref_orderline_id > 0 and krl.m_product_id =2009113
  145. group by krl.kst_receipt_id,
  146.     krl.m_product_id,
  147.     co.c_order_id,
  148.     mpc.isfabric,
  149.     cu.c_uom_id,
  150.     cb.c_bpartner_id,
  151.     kr.m_warehouse_id,
  152.     col.c_orderline_id,
  153.     case when (krl.ref_orderline_id is not null)
  154.         then krl.ref_orderline_id
  155.     else krl.c_orderline_id end,
  156.     ki.kst_ETDDate,
  157.     kie.eta_date,
  158.     mpc.m_product_category_id,
  159.     krl.kst_noroll,
  160.     cd.c_doctype_id,
  161.     krl.ref_orderline_id
  162. ) as data where  data.kst_receipt_id =1341040  
  163.  
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement