risyal 2

Country Code    Business Partner Key    Size    PO Buyer    Country Group   SupplierValue   Additional

where mpfg.value in ('HUMMEL-OLD-302SHA--BLUE GRASS-L')

select
    mpFg.value,
    ppbl.kst_part,
    mpDr.value,
    ppbl.QtyBOM,
    cuFg.name as uom,
    ppbl.ispiping,
    mpDr.value as Product_Category,
    '2021-01-01' as validFrom,
    'ID' as countryCode,
    '' as bpKey,
    '' as "size",
    '' as po_buyer,
    'INDONESIA' as countryGroup,
    case
        when (mpcDr.value = 'FB' or mpcDr.value = 'WIP')  then 'SP-DM-515'
        else 'SP-DM-516'
    end as bp,
    ppbwip.*
from m_product mpFG
join c_uom cuFg on mpfg.c_uom_id = cuFg.c_uom_id
join pp_product_bom ppb on ppb.pp_product_bom_id = (select max(ppb2.pp_product_bom_id) from pp_product_bom ppb2 where ppb2.m_product_id = mpfg.m_product_id)
join pp_product_bomline ppbl on ppb.pp_product_bom_id = ppbl.pp_product_bom_id
join m_product mpDr on ppbl.m_product_id = mpDr.m_product_id
join m_product_category mpcDr on mpDr.m_product_category_id = mpcDr.m_product_category_id
union all
select
    mpFg.value,
    ppbl.kst_part,
    mpDr.value,
    ppbl.QtyBOM,
    cuFg.name as uom,
    ppbl.ispiping,
    mpDr.value as Product_Category,
    '2021-01-01' as validFrom,
    'ID' as countryCode,
    '' as bpKey,
    '' as "size",
    '' as po_buyer,
    'INDONESIA' as countryGroup,
    case
        when (mpcDr.value = 'FB' or mpcDr.value = 'WIP')  then 'SP-DM-515'
        else 'SP-DM-516'
    end as bp,
    ppbwip.*
from m_product mpFG
join c_uom cuFg on mpfg.c_uom_id = cuFg.c_uom_id
join pp_product_bom ppb on ppb.pp_product_bom_id = (select max(ppb2.pp_product_bom_id) from pp_product_bom ppb2 where ppb2.m_product_id = mpfg.m_product_id)
join pp_product_bomline ppbl on ppb.pp_product_bom_id = ppbl.pp_product_bom_id
join pp_product_bom ppbWip on ppbWip.pp_product_bom_id = (select max(ppb2.pp_product_bom_id) from pp_product_bom ppb2 where ppb2.m_product_id = mpfg.m_product_id)
join m_product mpDr on ppbl.m_product_id = mpDr.m_product_id
join m_product_category mpcDr on mpDr.m_product_category_id = mpcDr.m_product_category_id