Perubahan Stok / Inventori

select 0 as counter,
    lokasi as lok,jenis,produk as nama,kode,uom,
    sum(stok_awal) as stok_awal,
    sum(stok_masuk) as stok_masuk,
    sum(pemakaian)*-1 as pemakaian,
    sum(waste)*-1 as waste,
    (coalesce((select sum(movementqty)
    from m_product mpr
    join m_transaction mtr on mtr.m_product_id = mpr.m_product_id
    join ad_org morg on morg.ad_org_id = mtr.ad_org_id
    where mpr.ad_client_id = $P{CLIENT}
    and mpr.name = produk
    and morg.name = lokasi
    and movementdate < $P{DATE1}),0)+
    coalesce((select sum(movementqty)
    from m_product mpr
    join m_transaction mtr on mtr.m_product_id = mpr.m_product_id
    join ad_org morg on morg.ad_org_id = mtr.ad_org_id
    where mpr.ad_client_id = $P{CLIENT}
    and mpr.name = produk
    and morg.name = lokasi
    and date(movementdate) between $P{DATE1} and $P{DATE2}),0)) as stok_akhir
from
(select lokasi,jenis,produk,kode,uom,0 as stok_awal,
    coalesce(
        case when movementtype ~ 'V\+|I\+|C\+'
        then movementqty end,0
        ) as stok_masuk,
    coalesce(
        case when movementtype ~ 'C\-|I\-|V\-' and chargeid != 1000020
        then movementqty end,0) as pemakaian,
    (coalesce(
        case when chargeid = '1000020'
        then movementqty end,0
        )) as waste
from
(select org.name as lokasi,
    ca.name as jenis,
    pr.name as produk,
    pr.value as kode,
    coalesce(inv.c_charge_id,0) as chargeid,
    movementtype,movementqty,
    cu.uomsymbol as uom
from m_product pr
join m_transaction tr on tr.m_product_id = pr.m_product_id
join c_uom cu on cu.c_uom_id = pr.c_uom_id
join m_product_category ca on ca.m_product_category_id = pr.m_product_category_id
left join m_inventoryline inv on inv.m_inventoryline_id = tr.m_inventoryline_id
join m_inventory minv on minv.m_inventory_id = inv.m_inventory_id
join ad_org org on org.ad_org_id = tr.ad_org_id
left join c_charge ch on ch.c_charge_id = inv.c_charge_id
where pr.ad_client_id = $P{CLIENT}
and date(tr.movementdate) between $P{DATE1} and $P{DATE2}
and case when $P{LOKASI} is null then org.ad_org_id != 0 else org.ad_org_id = $P{LOKASI} end
and docstatus ~ 'CO|CL')
as sub1

union all

select (select name from ad_org where ad_org_id = lokasiid) as lokasi,
    (select name from m_product_category where m_product_category_id = idjenis) as jenis,
    (select name from m_product where m_product_id = produkid) as produk,
    (select value from m_product where m_product_id = produkid) as kode,
    uom,
    coalesce((select sum(movementqty)
    from m_transaction
    where m_product_id = produkid
    and ad_org_id = lokasiid
    and movementdate < $P{DATE1}),0) as stok_awal,
    0 as stok_masuk, 0 as pemakaian, 0 as waste
from
(select org.ad_org_id as lokasiid,
    pr.m_product_id as produkid,
    m_product_category_id as idjenis,
    (select uomsymbol from c_uom where c_uom_id = pr.c_uom_id) as uom
from m_product pr
join m_transaction tr on tr.m_product_id = pr.m_product_id
join ad_org org on org.ad_org_id = tr.ad_org_id
join m_inventoryline invl on invl.m_inventoryline_id = tr.m_inventoryline_id
join m_inventory minv on minv.m_inventory_id = invl.m_inventory_id
where pr.ad_client_id = $P{CLIENT}
and case when $P{LOKASI} is null then org.ad_org_id != 0 else org.ad_org_id = $P{LOKASI} end
and docstatus ~ 'CO|CL'
group by pr.m_product_id,lokasiid) as uni
) as sub2
group by lokasi,jenis,produk,kode,uom
order by lokasi,kode