f_material_requirement.sql

-- adempiere.f_material_requirement source

CREATE OR REPLACE VIEW adempiere.f_material_requirement
AS SELECT ppb.pp_product_bom_id,
    ppb.documentno,
    mp.m_product_id,
    mp.value,
    mp.upc AS style,
    ppbl.pp_product_bomline_id,
    ppbl.line,
    mpc.m_product_id AS componentid,
    mpc.value AS componentvalue,
    ppbl.isconfirmedkst,
    ppbl.kst_sourcesize_id,
    ks.value AS sourcesize,
    ppbl.c_country_id,
    cc.countrycode,
    cbc.c_bpartner_id,
    cbc.value AS bpartnervalue,
    ppbl.qtybom,
    mpc.c_uom_id,
    cu.name,
    ppbl.kst_part,
    co.documentno AS salesorderno,
    co.poreference,
    co.kst_joborder,
    co.kst_lcdate,
    co.c_order_id,
    col.c_orderline_id,
    col.qtyordered,
    col.qtyordered * ppbl.qtybom AS qtyrequired,
    co.ad_client_id,
    co.ad_org_id,
    co.issotrx,
    ctg.m_product_category_id,
    ppbl.dateconfirm,
    cbco.value AS cust_no,
    mp.kst_articleno,
    ksc.value AS gmt_size,
    ccc.countrycode AS ctycode,
    mpc.name AS desc_raw,
    cbc2.value AS supp_code,
    cbc2.name AS supp_name,
    ppbl.ispiping,
    ppbl.poreference AS poreference_bom,
    ccc.name AS ctyname,
    cbbs.value AS supp_code_bs,
    cbbs.name AS supp_name_bs,
    wh.name AS warehouse,
    date(co.datepromised) AS datepromised,
    date(co.kst_statisticaldate2) AS statisticaldate,
    date(co.kst_statisticaldate) AS kst_statisticaldate,
    mp.kst_season,
    ctgg.m_product_category_id AS m_product_category_id_fp,
    ctgg.value AS m_product_category_value_fp,
    ctgg.name AS m_product_category_name_fp,
    getcountryname(cg.c_countrygroup_id, co.c_country_id, co.c_bpartner_id, ppbl.poreference, ppbl.c_countrygroup_id, ppbl.c_country_id, ppbl.c_bpartner_id)::text AS countrycodegroup,
    ctggg.name AS m_product_category_name_fg,
    mp.kst_isartwork,
    mpc.kst_isila,
    ceiling(ppbl.qtybom / 0.001) * 0.001 AS qtybomnew,
    ceiling(ppbl.qtybom / 0.001) * 0.001 * col.qtyordered AS qtyrequirednew,
    co.kst_mr_date,
    ppbl.ref_bpartner_id,
    co.kst_ordertype
   FROM c_order co
     JOIN c_orderline col ON co.c_order_id = col.c_order_id
     JOIN m_warehouse wh ON wh.m_warehouse_id = co.m_warehouse_id
     JOIN c_bpartner cbp ON co.c_bpartner_id = cbp.c_bpartner_id
     JOIN c_bpartner_location cbpl ON cbpl.c_bpartner_location_id = co.c_bpartner_location_id
     JOIN c_location cl ON cl.c_location_id = cbpl.c_location_id
     JOIN m_product mp ON col.m_product_id = mp.m_product_id
     JOIN pp_product_bom ppb ON ppb.m_product_id = mp.m_product_id AND ppb.pp_product_bom_id = kst_validbomlcdate(ppb.m_product_id, co.kst_lcdate)
     JOIN pp_product_bomline ppbl ON ppb.pp_product_bom_id = ppbl.pp_product_bom_id
     JOIN m_product mpc ON ppbl.m_product_id = mpc.m_product_id
     LEFT JOIN m_product_category ctg ON ppbl.m_product_category_id = ctg.m_product_category_id
     LEFT JOIN kst_sourcesize ks ON ppbl.kst_sourcesize_id = ks.kst_sourcesize_id
     LEFT JOIN c_country cc ON ppbl.c_country_id = cc.c_country_id
     LEFT JOIN c_bpartner cbc ON ppbl.c_bpartner_id = cbc.c_bpartner_id
     JOIN c_uom cu ON mpc.c_uom_id = cu.c_uom_id
     LEFT JOIN c_bpartner cbco ON cbco.c_bpartner_id = co.c_bpartner_id
     LEFT JOIN kst_sourcesize ksc ON ksc.kst_sourcesize_id = mp.kst_sourcesize_id
     LEFT JOIN c_country ccc ON ccc.c_country_id = co.c_country_id
     LEFT JOIN c_bpartner cbc2 ON ppbl.ref_bpartner_id = cbc2.c_bpartner_id
     LEFT JOIN kst_bom_supplier bs ON "right"(co.poreference::text, 9) = "right"(bs.poreference::text, 9) AND ppbl.m_product_id = bs.m_product_id AND ppbl.ispiping = bs.ispiping AND bs.isactive = 'Y'::bpchar AND co.kst_joborder::text = bs.kst_joborder::text
     LEFT JOIN c_bpartner cbbs ON bs.c_bpartner_id = cbbs.c_bpartner_id
     LEFT JOIN m_product_category ctgg ON mpc.m_product_category_id = ctgg.m_product_category_id
     LEFT JOIN c_countrygroupcountry cgc ON co.c_country_id = cgc.c_country_id AND co.c_bpartner_id = cgc.c_bpartner_id
     LEFT JOIN c_countrygroup cg ON cgc.c_countrygroup_id = cg.c_countrygroup_id
     LEFT JOIN m_product_category ctggg ON ctggg.m_product_category_id = mp.m_product_category_id
  WHERE co.issotrx = 'Y'::bpchar AND (co.docstatus = ANY (ARRAY['CO'::bpchar, 'CL'::bpchar])) AND (ppbl.c_bpartner_id IS NULL OR ppbl.c_bpartner_id = co.c_bpartner_id) AND (ppbl.c_country_id IS NULL OR ppbl.c_country_id = co.c_country_id) AND (ppbl.poreference IS NULL OR "right"(ppbl.poreference::text, 9) = "right"(co.poreference::text, 9)) AND ppbl.isactive = 'Y'::bpchar AND ppbl.pp_product_bomline_id = getproductbomlinebycountry(ppbl.pp_product_bomline_id, ppbl.pp_product_bom_id, mpc.m_product_id, co.c_country_id, co.c_bpartner_id, co.poreference, cgc.c_countrygroup_id, mp.m_product_id, ppbl.kst_part, co.kst_statisticaldate) AND ctgg.m_product_category_id <> 1000032::numeric
UNION
 SELECT ppb2.pp_product_bom_id,
    ppb2.documentno,
    mp2.m_product_id,
    mp2.value,
    mp2.upc AS style,
    ppbl2.pp_product_bomline_id,
    ppbl2.line,
    mpc.m_product_id AS componentid,
    mpc.value AS componentvalue,
    ppbl2.isconfirmedkst,
    ppbl2.kst_sourcesize_id,
    ks.value AS sourcesize,
    ppbl2.c_country_id,
    cc.countrycode,
    cbc.c_bpartner_id,
    cbc.value AS bpartnervalue,
    ppbl2.qtybom,
    mpc.c_uom_id,
    cu.name,
    ppbl2.kst_part,
    co.documentno AS salesorderno,
    co.poreference,
    co.kst_joborder,
    co.kst_lcdate,
    co.c_order_id,
    col.c_orderline_id,
    col.qtyordered,
    col.qtyordered * ppbl2.qtybom AS qtyrequired,
    co.ad_client_id,
    co.ad_org_id,
    co.issotrx,
    ctg.m_product_category_id,
    ppbl2.dateconfirm,
    cbco.value AS cust_no,
    mp2.kst_articleno,
    ksc.value AS gmt_size,
    ccc.countrycode AS ctycode,
    mpc.name AS desc_raw,
    cbc2.value AS supp_code,
    cbc2.name AS supp_name,
    ppbl2.ispiping,
    ppbl2.poreference AS poreference_bom,
    ccc.name AS ctyname,
    cbbs.value AS supp_code_bs,
    cbbs.name AS supp_name_bs,
    wh.name AS warehouse,
    date(co.datepromised) AS datepromised,
    date(co.kst_statisticaldate2) AS statisticaldate,
    date(co.kst_statisticaldate) AS kst_statisticaldate,
    mp2.kst_season,
    ctgg.m_product_category_id AS m_product_category_id_fp,
    ctgg.value AS m_product_category_value_fp,
    ctgg.name AS m_product_category_name_fp,
    getcountryname(cg.c_countrygroup_id, co.c_country_id, co.c_bpartner_id, ppbl2.poreference, ppbl2.c_countrygroup_id, ppbl2.c_country_id, ppbl2.c_bpartner_id)::text AS countrycodegroup,
    ctggg.name AS m_product_category_name_fg,
    mp2.kst_isartwork,
    mpc.kst_isila,
    ceiling(ppbl2.qtybom / 0.001) * 0.001 AS qtybomnew,
    ceiling(ppbl2.qtybom / 0.001) * 0.001 * col.qtyordered AS qtyrequirednew,
    co.kst_mr_date,
    ppbl2.ref_bpartner_id,
    co.kst_ordertype
   FROM c_order co
     JOIN c_orderline col ON co.c_order_id = col.c_order_id
     JOIN m_warehouse wh ON wh.m_warehouse_id = co.m_warehouse_id
     JOIN c_bpartner cbp ON co.c_bpartner_id = cbp.c_bpartner_id
     JOIN c_bpartner_location cbpl ON cbpl.c_bpartner_location_id = co.c_bpartner_location_id
     JOIN c_location cl ON cl.c_location_id = cbpl.c_location_id
     JOIN m_product mp ON col.m_product_id = mp.m_product_id
     JOIN pp_product_bom ppb ON ppb.m_product_id = mp.m_product_id AND ppb.pp_product_bom_id = kst_validbomlcdate(ppb.m_product_id, co.kst_lcdate)
     JOIN pp_product_bomline ppbl ON ppb.pp_product_bom_id = ppbl.pp_product_bom_id
     JOIN m_product mp2 ON ppbl.m_product_id = mp2.m_product_id
     JOIN pp_product_bom ppb2 ON ppb2.m_product_id = mp2.m_product_id AND ppb2.pp_product_bom_id = kst_validbomlcdate(ppb2.m_product_id, co.kst_lcdate)
     JOIN pp_product_bomline ppbl2 ON ppb2.pp_product_bom_id = ppbl2.pp_product_bom_id
     JOIN m_product mpc ON ppbl2.m_product_id = mpc.m_product_id
     LEFT JOIN m_product_category ctg ON ppbl2.m_product_category_id = ctg.m_product_category_id
     LEFT JOIN kst_sourcesize ks ON ppbl2.kst_sourcesize_id = ks.kst_sourcesize_id
     LEFT JOIN c_country cc ON ppbl2.c_country_id = cc.c_country_id
     LEFT JOIN c_bpartner cbc ON ppbl2.c_bpartner_id = cbc.c_bpartner_id
     JOIN c_uom cu ON mpc.c_uom_id = cu.c_uom_id
     LEFT JOIN c_bpartner cbco ON cbco.c_bpartner_id = co.c_bpartner_id
     LEFT JOIN kst_sourcesize ksc ON ksc.kst_sourcesize_id = mp2.kst_sourcesize_id
     LEFT JOIN c_country ccc ON ccc.c_country_id = co.c_country_id
     LEFT JOIN c_bpartner cbc2 ON ppbl2.ref_bpartner_id = cbc2.c_bpartner_id
     LEFT JOIN kst_bom_supplier bs ON "right"(co.poreference::text, 9) = "right"(bs.poreference::text, 9) AND ppbl2.m_product_id = bs.m_product_id AND ppbl2.ispiping = bs.ispiping AND bs.isactive = 'Y'::bpchar AND co.kst_joborder::text = bs.kst_joborder::text
     LEFT JOIN c_bpartner cbbs ON bs.c_bpartner_id = cbbs.c_bpartner_id
     LEFT JOIN m_product_category ctgg ON mpc.m_product_category_id = ctgg.m_product_category_id
     LEFT JOIN c_countrygroupcountry cgc ON co.c_country_id = cgc.c_country_id AND co.c_bpartner_id = cgc.c_bpartner_id
     LEFT JOIN c_countrygroup cg ON cgc.c_countrygroup_id = cg.c_countrygroup_id
     LEFT JOIN m_product_category ctggg ON ctggg.m_product_category_id = mp2.m_product_category_id
  WHERE co.issotrx = 'Y'::bpchar AND (co.docstatus = ANY (ARRAY['CO'::bpchar, 'CL'::bpchar])) AND (ppbl2.c_bpartner_id IS NULL OR ppbl2.c_bpartner_id = co.c_bpartner_id) AND (ppbl2.c_country_id IS NULL OR ppbl2.c_country_id = co.c_country_id) AND (ppbl2.poreference IS NULL OR "right"(ppbl2.poreference::text, 9) = "right"(co.poreference::text, 9)) AND ppbl2.isactive = 'Y'::bpchar AND ppbl2.pp_product_bomline_id = getproductbomlinebycountry(ppbl2.pp_product_bomline_id, ppbl2.pp_product_bom_id, mpc.m_product_id, co.c_country_id, co.c_bpartner_id, co.poreference, cgc.c_countrygroup_id, mp.m_product_id, ppbl2.kst_part, co.kst_statisticaldate) AND ctgg.m_product_category_id <> 1000032::numeric
UNION
 SELECT ppb3.pp_product_bom_id,
    ppb3.documentno,
    mp3.m_product_id,
    mp3.value,
    mp3.upc AS style,
    ppbl3.pp_product_bomline_id,
    ppbl3.line,
    mpc.m_product_id AS componentid,
    mpc.value AS componentvalue,
    ppbl3.isconfirmedkst,
    ppbl3.kst_sourcesize_id,
    ks.value AS sourcesize,
    ppbl3.c_country_id,
    cc.countrycode,
    cbc.c_bpartner_id,
    cbc.value AS bpartnervalue,
    ppbl3.qtybom,
    mpc.c_uom_id,
    cu.name,
    ppbl3.kst_part,
    co.documentno AS salesorderno,
    co.poreference,
    co.kst_joborder,
    co.kst_lcdate,
    co.c_order_id,
    col.c_orderline_id,
    col.qtyordered,
    col.qtyordered * ppbl3.qtybom AS qtyrequired,
    co.ad_client_id,
    co.ad_org_id,
    co.issotrx,
    ctg.m_product_category_id,
    ppbl3.dateconfirm,
    cbco.value AS cust_no,
    mp3.kst_articleno,
    ksc.value AS gmt_size,
    ccc.countrycode AS ctycode,
    mpc.name AS desc_raw,
    cbc2.value AS supp_code,
    cbc2.name AS supp_name,
    ppbl3.ispiping,
    ppbl3.poreference AS poreference_bom,
    ccc.name AS ctyname,
    cbbs.value AS supp_code_bs,
    cbbs.name AS supp_name_bs,
    wh.name AS warehouse,
    date(co.datepromised) AS datepromised,
    date(co.kst_statisticaldate2) AS statisticaldate,
    date(co.kst_statisticaldate) AS kst_statisticaldate,
    mp3.kst_season,
    ctgg.m_product_category_id AS m_product_category_id_fp,
    ctgg.value AS m_product_category_value_fp,
    ctgg.name AS m_product_category_name_fp,
    getcountryname(cg.c_countrygroup_id, co.c_country_id, co.c_bpartner_id, ppbl3.poreference, ppbl3.c_countrygroup_id, ppbl3.c_country_id, ppbl3.c_bpartner_id)::text AS countrycodegroup,
    ctggg.name AS m_product_category_name_fg,
    mp3.kst_isartwork,
    mpc.kst_isila,
    ceiling(ppbl3.qtybom / 0.001) * 0.001 AS qtybomnew,
    ceiling(ppbl3.qtybom / 0.001) * 0.001 * col.qtyordered AS qtyrequirednew,
    co.kst_mr_date,
    ppbl3.ref_bpartner_id,
    co.kst_ordertype
   FROM c_order co
     JOIN c_orderline col ON co.c_order_id = col.c_order_id
     JOIN m_warehouse wh ON wh.m_warehouse_id = co.m_warehouse_id
     JOIN c_bpartner cbp ON co.c_bpartner_id = cbp.c_bpartner_id
     JOIN c_bpartner_location cbpl ON cbpl.c_bpartner_location_id = co.c_bpartner_location_id
     JOIN c_location cl ON cl.c_location_id = cbpl.c_location_id
     JOIN m_product mp ON col.m_product_id = mp.m_product_id
     JOIN pp_product_bom ppb ON ppb.m_product_id = mp.m_product_id AND ppb.pp_product_bom_id = kst_validbomlcdate(ppb.m_product_id, co.kst_lcdate)
     JOIN pp_product_bomline ppbl ON ppb.pp_product_bom_id = ppbl.pp_product_bom_id
     JOIN m_product mp2 ON ppbl.m_product_id = mp2.m_product_id
     JOIN pp_product_bom ppb2 ON ppb2.m_product_id = mp2.m_product_id AND ppb2.pp_product_bom_id = kst_validbomlcdate(ppb2.m_product_id, co.kst_lcdate)
     JOIN pp_product_bomline ppbl2 ON ppb2.pp_product_bom_id = ppbl2.pp_product_bom_id
     JOIN m_product mp3 ON ppbl2.m_product_id = mp3.m_product_id
     JOIN pp_product_bom ppb3 ON ppb3.m_product_id = mp3.m_product_id AND ppb3.pp_product_bom_id = kst_validbomlcdate(ppb3.m_product_id, co.kst_lcdate)
     JOIN pp_product_bomline ppbl3 ON ppb3.pp_product_bom_id = ppbl3.pp_product_bom_id
     JOIN m_product mpc ON ppbl3.m_product_id = mpc.m_product_id
     LEFT JOIN m_product_category ctg ON ppbl3.m_product_category_id = ctg.m_product_category_id
     LEFT JOIN kst_sourcesize ks ON ppbl3.kst_sourcesize_id = ks.kst_sourcesize_id
     LEFT JOIN c_country cc ON ppbl3.c_country_id = cc.c_country_id
     LEFT JOIN c_bpartner cbc ON ppbl3.c_bpartner_id = cbc.c_bpartner_id
     JOIN c_uom cu ON mpc.c_uom_id = cu.c_uom_id
     LEFT JOIN c_bpartner cbco ON cbco.c_bpartner_id = co.c_bpartner_id
     LEFT JOIN kst_sourcesize ksc ON ksc.kst_sourcesize_id = mp3.kst_sourcesize_id
     LEFT JOIN c_country ccc ON ccc.c_country_id = co.c_country_id
     LEFT JOIN c_bpartner cbc2 ON ppbl3.ref_bpartner_id = cbc2.c_bpartner_id
     LEFT JOIN kst_bom_supplier bs ON "right"(co.poreference::text, 9) = "right"(bs.poreference::text, 9) AND ppbl3.m_product_id = bs.m_product_id AND ppbl3.ispiping = bs.ispiping AND bs.isactive = 'Y'::bpchar AND co.kst_joborder::text = bs.kst_joborder::text
     LEFT JOIN c_bpartner cbbs ON bs.c_bpartner_id = cbbs.c_bpartner_id
     LEFT JOIN m_product_category ctgg ON mpc.m_product_category_id = ctgg.m_product_category_id
     LEFT JOIN c_countrygroupcountry cgc ON co.c_country_id = cgc.c_country_id AND co.c_bpartner_id = cgc.c_bpartner_id
     LEFT JOIN c_countrygroup cg ON cgc.c_countrygroup_id = cg.c_countrygroup_id
     LEFT JOIN m_product_category ctggg ON ctggg.m_product_category_id = mp3.m_product_category_id
  WHERE co.issotrx = 'Y'::bpchar AND (co.docstatus = ANY (ARRAY['CO'::bpchar, 'CL'::bpchar])) AND (ppbl3.c_bpartner_id IS NULL OR ppbl3.c_bpartner_id = co.c_bpartner_id) AND (ppbl3.c_country_id IS NULL OR ppbl3.c_country_id = co.c_country_id) AND (ppbl3.poreference IS NULL OR "right"(ppbl3.poreference::text, 9) = "right"(co.poreference::text, 9)) AND ppbl3.isactive = 'Y'::bpchar AND ppbl3.pp_product_bomline_id = getproductbomlinebycountry(ppbl3.pp_product_bomline_id, ppbl3.pp_product_bom_id, mpc.m_product_id, co.c_country_id, co.c_bpartner_id, co.poreference, cgc.c_countrygroup_id, mp.m_product_id, ppbl3.kst_part, co.kst_statisticaldate) AND ctgg.m_product_category_id <> 1000032::numeric;