mas charis permintaan roll.sql

select "name",m_warehouse_id  from m_warehouse mw where m_warehouse_id in (1000002,1000011, 1000001)

select qtyordered, qtyentered,* from c_orderline co where
c_orderline_id =4164919

qtyentered-> qty order
qtyordered -> qty bom

-- header

select
    cb.c_bpartner_id,
    cb."name" as supplier_name,
    ki.kst_packinglist  as no_packing_list,
    ki.kst_invoicevendor as no_invoice,
    ki.kst_resi as no_receipt,
    ki.kst_invoicevendor_id as no_delivery_order,
    ki.kst_ETDDate::date  as ex_factory_date,
    kie.eta_date::date as eta_date,
    kr.m_warehouse_id as erp_warehouse_id,
    ki.created::date as created_at,
    kr.kst_receipt_id
from kst_invoicevendor ki
join c_bpartner cb on ki.c_bpartner_id = cb.c_bpartner_id
join kst_receipt kr on ki.kst_invoicevendor_id = kr.kst_invoicevendor_id
join kst_invoicevendor_exim kie on ki.kst_invoicevendor_id = kie.kst_invoicevendor_id
where kr.kst_receipt_id =1341040

-- line
select
    krl.m_product_id as item_id,
    co.c_order_id, -- c_order_id asal bukan additional
    mpc.isfabric,
    cu.uomsymbol,
    coalesce ((select sum(kst_receiptline.qtyentered) from kst_receiptline
            join c_orderline on kst_receiptline.c_orderline_id = c_orderline.c_orderline_id
            join c_order on c_orderline.c_order_id = c_order.c_order_id
            where kst_receiptline.kst_receipt_id = krl.kst_receipt_id and kst_receiptline.m_product_id = krl.m_product_id
            and kst_receiptline.ref_orderline_id is null
            and c_order.c_order_id= co.c_order_id),0) as qty_delivered,
    coalesce ((select sum(kst_receiptline.qtyentered) from kst_receiptline
            where kst_receiptline.kst_receipt_id = krl.kst_receipt_id and kst_receiptline.m_product_id = krl.m_product_id
            and kst_receiptline.isfockst = 'Y' ),0) as foc,
    coalesce ((select sum(kst_receiptline.qtyentered) from kst_receiptline
            where kst_receiptline.kst_receipt_id = krl.kst_receipt_id and kst_receiptline.m_product_id = krl.m_product_id
            and kst_receiptline.isfockst = 'N' and kst_receiptline.ref_orderline_id > 0),0) as additional,
    krl.kst_receipt_id
from kst_receiptline krl
join kst_receipt kr on krl.kst_receipt_id = kr.kst_receipt_id
join kst_invoicevendor ki on kr.kst_invoicevendor_id = ki.kst_invoicevendor_id
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)
join c_order co on col.c_order_id = co.c_order_id
join m_product mp on krl.m_product_id = mp.m_product_id
join m_product_category mpc on mp.m_product_category_id = mpc.m_product_category_id
join c_uom cu on krl.c_uom_id = cu.c_uom_id
where krl.kst_receipt_id =1341040
group by krl.kst_receipt_id,
    krl.m_product_id,
    co.c_order_id,
    mpc.isfabric,
    cu.uomsymbol

-- detail roll
select
    krl.kst_noroll as nomor_roll,
    '-' as batch_number,
    cu.uomsymbol as uom,
    sum(qty),
    krl.m_product_id,
    col.c_order_id,
    col.c_orderline_id ,
    krl.kst_receipt_id
from kst_receiptline krl
join c_orderline col on krl.c_orderline_id = col.c_orderline_id
join c_uom cu on krl.c_uom_id = cu.c_uom_id
where krl.kst_receipt_id =1341040
group by krl.m_product_id, krl.kst_receipt_id, krl.kst_noroll, cu.uomsymbol, col.c_orderline_id