MSC_CTO_BOM_DATA

Details

Schema: FUSION
Object owner: MSC
Object type: VIEW

Columns

Name
DEPTH ITEM_NAME CTO_PARENT CTO_CHILD INVENTORY_ITEM_ID COMP_INVENTORY_ITEM_ID ASSEMBLY_ITEM_ID EFFECTIVITY_DATE DISABLE_DATE PLANNING_PERCENTAGE USING_ASSEMBLY_ID BILL_SEQUENCE_ID ORGANIZATION_ID BOM_ITEM_TYPE REFRESH_NUMBER ALTERNATE_BOM_DESIGNATOR

Name

DEPTH

ITEM_NAME

CTO_PARENT

CTO_CHILD

INVENTORY_ITEM_ID

COMP_INVENTORY_ITEM_ID

ASSEMBLY_ITEM_ID

EFFECTIVITY_DATE

DISABLE_DATE

PLANNING_PERCENTAGE

USING_ASSEMBLY_ID

BILL_SEQUENCE_ID

ORGANIZATION_ID

BOM_ITEM_TYPE

REFRESH_NUMBER

ALTERNATE_BOM_DESIGNATOR

Query

SQL_Statement
SELECT level depth, bom.ITEM_NAME, bom.CTO_PARENT, bom.CTO_CHILD, bom.INVENTORY_ITEM_ID, bom.COMP_INVENTORY_ITEM_ID, bom.ASSEMBLY_ITEM_ID, bom.EFFECTIVITY_DATE, bom.DISABLE_DATE, bom.PLANNING_PERCENTAGE, bom.USING_ASSEMBLY_ID, bom.BILL_SEQUENCE_ID, bom.ORGANIZATION_ID, bom.BOM_ITEM_TYPE, bom.REFRESH_NUMBER, bom.ALTERNATE_BOM_DESIGNATOR FROM (SELECT DISTINCT it.item_name, comp.bill_sequence_id cto_parent, st.bill_sequence_id cto_child, it.inventory_item_id , comp.inventory_item_id comp_inventory_item_id, st.assembly_item_id, comp.effectivity_date, comp.disable_date, ((comp.planning_factor/100) * comp.usage_quantity) planning_percentage, comp.using_assembly_id using_assembly_id, st.bill_sequence_id, it.organization_id, it.bom_item_type, nvl(nvl(comp.refresh_number , st.refresh_number), -1 ) refresh_number, st.alternate_bom_designator FROM fusion.msc_bom_components comp, fusion.msc_system_items it, fusion.msc_boms st WHERE it.plan_id = -1 AND st.plan_id(+) = it.plan_id AND comp.plan_id(+) = it.plan_id AND comp.inventory_item_id(+)= it.inventory_item_id AND comp.organization_id(+) = it.organization_id AND (comp.bill_sequence_id is not null or st.bill_sequence_id is not null) AND (comp.bill_sequence_id is not null or it.bom_item_type = 1) AND it.item_name NOT LIKE ('%*%') AND st.assembly_item_id(+) = it.inventory_item_id AND st.organization_id(+) = it.organization_id ) bom START WITH cto_parent IS NULL CONNECT BY prior cto_child=cto_parent

SQL_Statement

SELECT

level depth,

bom.ITEM_NAME,

bom.CTO_PARENT,

bom.CTO_CHILD,

bom.INVENTORY_ITEM_ID,

bom.COMP_INVENTORY_ITEM_ID,

bom.ASSEMBLY_ITEM_ID,

bom.EFFECTIVITY_DATE,

bom.DISABLE_DATE,

bom.PLANNING_PERCENTAGE,

bom.USING_ASSEMBLY_ID,

bom.BILL_SEQUENCE_ID,

bom.ORGANIZATION_ID,

bom.BOM_ITEM_TYPE,

bom.REFRESH_NUMBER,

bom.ALTERNATE_BOM_DESIGNATOR

FROM

(SELECT

DISTINCT it.item_name,

comp.bill_sequence_id cto_parent,

st.bill_sequence_id cto_child,

it.inventory_item_id ,

comp.inventory_item_id comp_inventory_item_id,

st.assembly_item_id,

comp.effectivity_date,

comp.disable_date,

((comp.planning_factor/100) * comp.usage_quantity) planning_percentage,

comp.using_assembly_id using_assembly_id,

st.bill_sequence_id,

it.organization_id,

it.bom_item_type,

nvl(nvl(comp.refresh_number , st.refresh_number), -1 ) refresh_number,

st.alternate_bom_designator

FROM fusion.msc_bom_components comp,

fusion.msc_system_items it,

fusion.msc_boms st

WHERE it.plan_id = -1

AND st.plan_id(+) = it.plan_id

AND comp.plan_id(+) = it.plan_id

AND comp.inventory_item_id(+)= it.inventory_item_id

AND comp.organization_id(+) = it.organization_id

AND (comp.bill_sequence_id is not null or st.bill_sequence_id is not null)

AND (comp.bill_sequence_id is not null or it.bom_item_type = 1)

AND it.item_name NOT LIKE ('%*%')

AND st.assembly_item_id(+) = it.inventory_item_id

AND st.organization_id(+) = it.organization_id

) bom

START WITH cto_parent IS NULL

CONNECT BY prior cto_child=cto_parent