1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!
juelson

Multiple level bom in one sql 2013-04-24

Multiple level bom in one sql

  1. juelson
    Here's the SQL scripts I counted on very often while I prepare answers on why MRP or ASCP planning suggest the planned orders or not. Just in case the scripts posted below not showing correctly I uploaded a text document as well.

    [highlight=sql]

    -- BOM multiple levels

    SELECT y.* FROM (
    SELECT LEVEL lev, x.* FROM (
    SELECT
    mp.organization_code Org
    , pi.item_number Assembly
    ,pi.inventory_item_id
    , c.item_num ItemNum
    , c.operation_seq_num OPseq
    , ci.item_number Component
    , c.effectivity_date Effective
    , c.disable_date Defectiv
    , c.component_quantity bUsage
    , 1/DECODE(c.component_quantity,0,.0001,c.component_quantity) RevsUse
    ,ci.primary_UOM_code uom
    , c.component_yield_factor Yield
    , pi.cumulative_total_lead_time "CumLT", pi.fixed_lead_time "FLT", pi.variable_lead_time "VLT"
    , pi.postprocessing_lead_time "PstLT", pi.preprocessing_lead_time "PreLT", pi.full_lead_time "PLT"
    , pi.lead_time_lot_size "LTlot"
    , ci.cumulative_total_lead_time "cCumLT", ci.fixed_lead_time "cFLT", ci.variable_lead_time "cVLT"
    , ci.postprocessing_lead_time "cPstLT", ci.preprocessing_lead_time "cPreLT", ci.full_lead_time "cPLT"
    , ci.lead_time_lot_size "cLTlot", ci.attribute9 "ItemYield", g.segment1 Catalog ,ci.description
    , b.bill_sequence_id
    , b.common_bill_sequence_id
    , b.organization_id
    , NVL(b.common_assembly_item_id,b.assembly_item_id) assembly_item_id
    , c.component_item_id
    , b.alternate_bom_designator
    ,b.creation_date
    FROM
    apps.bom_bill_of_materials b
    , apps.bom_inventory_components c
    , apps.mtl_item_flexfields pi
    , apps.mtl_item_flexfields ci
    ,apps.mtl_item_catalog_groups g
    , apps.mtl_parameters mp
    WHERE NVL(b.common_bill_sequence_id,b.bill_sequence_id) = c.bill_sequence_id
    AND b.assembly_item_id = pi.inventory_item_id
    AND b.organization_id = pi.organization_id
    AND c.component_item_id = ci.inventory_item_id
    AND b.organization_id = ci.organization_id
    AND ci.item_catalog_group_id = g.item_catalog_group_id(+)
    --AND ci.organization_id = g.organization_id (+)
    AND b.organization_id = mp.organization_id
    AND NVL(c.disable_date, SYSDATE)>= SYSDATE
    AND b.alternate_bom_designator IS NULL
    AND mp.organization_code IN ('your_org')
    ) x
    START WITH --x.assembly like 'top_item%'--= '&item'
    --x.creation_date > SYSDATE - 20
    x.component in ('you_item_list')
    /*exists (select '' from mrp_forecast_dates fd
    WHERE forecast_designator IN ('FC_list')
    AND fd.organization_id = x.organization_id
    AND fd.inventory_item_id = x.inventory_item_id)*/
    AND NVL(x.alternate_bom_designator,'~NULL~')='~NULL~'
    CONNECT BY PRIOR x.component_item_id = x.assembly_item_id AND
    NVL(x.alternate_bom_designator,'~NULL~')='~NULL~' AND
    LEVEL <= &LEVELS
    ORDER SIBLINGS BY x.opseq, x.itemnum) y;
    [/highlight]