mas charis 2.sql

select * from (
select
    krl.m_product_id as item_id,
    co.c_order_id, -- c_order_id asal bukan additional
    mpc.isfabric,
    cu.uomsymbol,
    cu.c_uom_id,
    sum(krl.qtyentered) as qty_delivered,
    0 as foc,
    0 as additional,
    krl.kst_receipt_id,
    cb.c_bpartner_id,
    cb."name" as bp_name,
    kr.m_warehouse_id,
    case when (krl.ref_orderline_id is not null)
        then krl.ref_orderline_id
    else krl.c_orderline_id end as c_orderline_id,
    col.qtyentered,
    ki.kst_ETDDate::date  as ex_factory_date,
    coalesce (kie.eta_date::date, ki.kst_ETDDate::date) as eta_date,
    mpc.value,
    mpc.m_product_category_id,
    mpc.isfabric,
    krl.kst_noroll,
    cd.c_doctype_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 kst_invoicevendor_exim kie on ki.kst_invoicevendor_id = kie.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_bpartner cb on kr.c_bpartner_id = cb.c_bpartner_id
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
join c_doctype cd on co.c_doctype_id = cd.c_doctype_id
group by krl.kst_receipt_id,
    krl.m_product_id,
    co.c_order_id,
    mpc.isfabric,
    cu.c_uom_id,
    cb.c_bpartner_id,
    kr.m_warehouse_id,
    col.c_orderline_id,
    case when (krl.ref_orderline_id is not null)
        then krl.ref_orderline_id
    else krl.c_orderline_id end,
    ki.kst_ETDDate,
    kie.eta_date,
    mpc.m_product_category_id,
    krl.kst_noroll,
    cd.c_doctype_id,
    krl.ref_orderline_id
union
    select
    krl.m_product_id as item_id,
    co.c_order_id, -- c_order_id asal bukan additional
    mpc.isfabric,
    cu.uomsymbol,
    cu.c_uom_id,
    0 as qty_delivered,
    sum(krl.qtyentered) as foc,
    0 as additional,
    krl.kst_receipt_id,
    cb.c_bpartner_id,
    cb."name" as bp_name,
    kr.m_warehouse_id,
    case when (krl.ref_orderline_id is not null)
        then krl.ref_orderline_id
    else krl.c_orderline_id end as c_orderline_id,
    col.qtyentered,
    ki.kst_ETDDate::date  as ex_factory_date,
    coalesce (kie.eta_date::date, ki.kst_ETDDate::date) as eta_date,
    mpc.value,
    mpc.m_product_category_id,
    mpc.isfabric,
    krl.kst_noroll,
    cd.c_doctype_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 kst_invoicevendor_exim kie on ki.kst_invoicevendor_id = kie.kst_invoicevendor_id
join c_orderline col on krl.c_orderline_id = col.c_orderline_id
join c_bpartner cb on kr.c_bpartner_id = cb.c_bpartner_id
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
join c_doctype cd on co.c_doctype_id = cd.c_doctype_id
where krl.isfockst = 'Y'
group by krl.kst_receipt_id,
    krl.m_product_id,
    co.c_order_id,
    mpc.isfabric,
    cu.c_uom_id,
    cb.c_bpartner_id,
    kr.m_warehouse_id,
    col.c_orderline_id,
    case when (krl.ref_orderline_id is not null)
        then krl.ref_orderline_id
    else krl.c_orderline_id end,
    ki.kst_ETDDate,
    kie.eta_date,
    mpc.m_product_category_id,
    krl.kst_noroll,
    cd.c_doctype_id,
    krl.ref_orderline_id
union
select
    krl.m_product_id as item_id,
    co.c_order_id, -- c_order_id asal bukan additional
    mpc.isfabric,
    cu.uomsymbol,
    cu.c_uom_id,
    0 as qty_delivered,
    0 as foc,
    sum(krl.qtyentered) as additional,
    krl.kst_receipt_id,
    cb.c_bpartner_id,
    cb."name" as bp_name,
    kr.m_warehouse_id,
    case when (krl.ref_orderline_id is not null)
        then krl.ref_orderline_id
    else krl.c_orderline_id end as c_orderline_id,
    col.qtyentered,
    ki.kst_ETDDate::date  as ex_factory_date,
    coalesce (kie.eta_date::date, ki.kst_ETDDate::date) as eta_date,
    mpc.value,
    mpc.m_product_category_id,
    mpc.isfabric,
    krl.kst_noroll,
    cd.c_doctype_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 kst_invoicevendor_exim kie on ki.kst_invoicevendor_id = kie.kst_invoicevendor_id
join c_orderline col on krl.c_orderline_id = col.c_orderline_id
join c_bpartner cb on kr.c_bpartner_id = cb.c_bpartner_id
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
join c_doctype cd on co.c_doctype_id = cd.c_doctype_id
where krl.isfockst = 'N' and krl.ref_orderline_id > 0 and krl.m_product_id =2009113
group by krl.kst_receipt_id,
    krl.m_product_id,
    co.c_order_id,
    mpc.isfabric,
    cu.c_uom_id,
    cb.c_bpartner_id,
    kr.m_warehouse_id,
    col.c_orderline_id,
    case when (krl.ref_orderline_id is not null)
        then krl.ref_orderline_id
    else krl.c_orderline_id end,
    ki.kst_ETDDate,
    kie.eta_date,
    mpc.m_product_category_id,
    krl.kst_noroll,
    cd.c_doctype_id,
    krl.ref_orderline_id
) as data where  data.kst_receipt_id =1341040