View: MSC_COMPONENTS_SC_V

Product:	MSC - Advanced Supply Chain Planning
Description:	This view shows all the components in the tree
Implementation/DBA Data:	APPS.MSC_COMPONENTS_SC_V

View Text

SELECT ASSY.INVENTORY_ITEM_ID
, COMP_NAME.ITEM_NAME
, ASSY_NAME.ITEM_NAME
, ASSY.ORGANIZATION_ID
, MSC_GET_NAME.ORG_CODE(ASSY.ORGANIZATION_ID
, ASSY.SR_INSTANCE_ID)
, ASSY.PLAN_ID
, ASSY.CHANGE_NOTICE
, ASSY.USING_ASSEMBLY_ID
, MSC_GET_NAME.OP_SEQ_NUM (ASSY.PLAN_ID
, ASSY.SR_INSTANCE_ID
, ASSY.ORGANIZATION_ID
, ASSY.COMPONENT_SEQUENCE_ID
, ASSY.BILL_SEQUENCE_ID
, NULL)
, ASSY.LAST_UPDATE_DATE
, ASSY.LAST_UPDATED_BY
, ASSY.CREATION_DATE
, ASSY.CREATED_BY
, ASSY.REVISION
, DECODE(ASSY.FROM_UNIT_NUMBER
, NULL
, ASSY.EFFECTIVITY_DATE
, TO_DATE(NULL))
, DECODE(ASSY.DRIVING_ITEM_ID
, NULL
, TO_DATE(NULL)
, DRV.INVENTORY_USE_UP_DATE)
, ASSY.USE_UP_CODE
, ASSY.USAGE_QUANTITY
, ASSY.OPERATION_OFFSET_PERCENT
, ASSY.PLANNING_FACTOR
, ASSY.DRIVING_ITEM_ID
, ASSY.OPTIONAL_COMPONENT
, ASSY.DISABLE_DATE
, MSC_GET_NAME.LOOKUP_MEANING('WIP_SUPPLY'
, ASSY.WIP_SUPPLY_TYPE)
, MB.ALTERNATE_BOM_DESIGNATOR
, ASSY.FROM_UNIT_NUMBER
, ASSY.TO_UNIT_NUMBER
, ASSY.SR_INSTANCE_ID
, MSC_GET_NAME.ITEM_DESC(ASSY.INVENTORY_ITEM_ID
, ASSY.ORGANIZATION_ID
, ASSY.PLAN_ID
, ASSY.SR_INSTANCE_ID)
, ASSY_NAME.DESCRIPTION
, DRV.UOM_CODE
, DECODE(ASSY.DRIVING_ITEM_ID
, NULL
, NULL
, DRV.ITEM_NAME)
, ASSY.BILL_SEQUENCE_ID
, ASSY.COMPONENT_SEQUENCE_ID
, ASSY.SCALE_MULTIPLE
, ASSY.SCALE_ROUNDING_VARIANCE
, ASSY.ROUNDING_DIRECTION
, CAT.SR_CATEGORY_ID
, CAT.CATEGORY_SET_ID
, LOOK.MEANING
, 2
FROM MSC_SYSTEM_ITEMS DRV
, MSC_SYSTEM_ITEMS ASSY_NAME
, MSC_ITEMS COMP_NAME
, MSC_BOMS MB
, MSC_BOM_COMPONENTS ASSY
, MSC_ITEM_CATEGORIES CAT
, MFG_LOOKUPS LOOK
WHERE NVL(ASSY.DRIVING_ITEM_ID
, ASSY.INVENTORY_ITEM_ID) = DRV.INVENTORY_ITEM_ID
AND CAT.INVENTORY_ITEM_ID = DRV.INVENTORY_ITEM_ID
AND CAT.SR_INSTANCE_ID = DRV.SR_INSTANCE_ID
AND CAT.ORGANIZATION_ID = DRV.ORGANIZATION_ID
AND ASSY.PLAN_ID = DRV.PLAN_ID
AND ASSY.ORGANIZATION_ID = DRV.ORGANIZATION_ID
AND ASSY.SR_INSTANCE_ID = DRV.SR_INSTANCE_ID
AND NVL(ASSY.COMPONENT_TYPE
, -1) <> 10
AND ASSY.USING_ASSEMBLY_ID = ASSY_NAME.INVENTORY_ITEM_ID
AND ASSY.ORGANIZATION_ID = ASSY_NAME.ORGANIZATION_ID
AND ASSY.SR_INSTANCE_ID = ASSY_NAME.SR_INSTANCE_ID
AND ASSY_NAME.PLAN_ID = -1
AND ASSY.INVENTORY_ITEM_ID = COMP_NAME.INVENTORY_ITEM_ID
AND NVL(ASSY.ROUNDING_DIRECTION
, 3) = LOOK.LOOKUP_CODE
AND LOOK.LOOKUP_TYPE = 'MSC_ROUND_DIRECTION'
AND MB.PLAN_ID(+) = ASSY.PLAN_ID
AND MB.BILL_SEQUENCE_ID(+) = ASSY.BILL_SEQUENCE_ID
AND MB.ASSEMBLY_ITEM_ID(+)= ASSY.USING_ASSEMBLY_ID
AND MB.SR_INSTANCE_ID(+)= ASSY.SR_INSTANCE_ID
AND MB.ORGANIZATION_ID(+)=ASSY.ORGANIZATION_ID UNION ALL SELECT SUB.SUBSTITUTE_ITEM_ID
, COMP_NAME.ITEM_NAME
, ASSY_NAME.ITEM_NAME
, SUB.ORGANIZATION_ID
, ASSY_NAME.ORGANIZATION_CODE
, SUB.PLAN_ID
, ASSY.CHANGE_NOTICE
, ASSY.USING_ASSEMBLY_ID
, MSC_GET_NAME.OP_SEQ_NUM (ASSY.PLAN_ID
, ASSY.SR_INSTANCE_ID
, ASSY.ORGANIZATION_ID
, ASSY.COMPONENT_SEQUENCE_ID
, ASSY.BILL_SEQUENCE_ID
, NULL)
, SUB.LAST_UPDATE_DATE
, SUB.LAST_UPDATED_BY
, SUB.CREATION_DATE
, SUB.CREATED_BY
, ASSY.REVISION
, DECODE(ASSY.FROM_UNIT_NUMBER
, NULL
, ASSY.EFFECTIVITY_DATE
, TO_DATE(NULL))
, DECODE(ASSY.DRIVING_ITEM_ID
, NULL
, TO_DATE(NULL)
, DRV.INVENTORY_USE_UP_DATE)
, ASSY.USE_UP_CODE
, SUB.USAGE_QUANTITY
, ASSY.OPERATION_OFFSET_PERCENT
, ASSY.PLANNING_FACTOR
, ASSY.DRIVING_ITEM_ID
, ASSY.OPTIONAL_COMPONENT
, ASSY.DISABLE_DATE
, MSC_GET_NAME.LOOKUP_MEANING('WIP_SUPPLY'
, ASSY.WIP_SUPPLY_TYPE)
, MB.ALTERNATE_BOM_DESIGNATOR
, ASSY.FROM_UNIT_NUMBER
, ASSY.TO_UNIT_NUMBER
, ASSY.SR_INSTANCE_ID
, MSC_GET_NAME.ITEM_DESC(ASSY.INVENTORY_ITEM_ID
, ASSY.ORGANIZATION_ID
, ASSY.PLAN_ID
, ASSY.SR_INSTANCE_ID)
, ASSY_NAME.DESCRIPTION
, DRV.UOM_CODE
, DECODE(ASSY.DRIVING_ITEM_ID
, NULL
, NULL
, DRV.ITEM_NAME)
, SUB.BILL_SEQUENCE_ID
, SUB.COMPONENT_SEQUENCE_ID
, ASSY.SCALE_MULTIPLE
, ASSY.SCALE_ROUNDING_VARIANCE
, ASSY.ROUNDING_DIRECTION
, CAT.SR_CATEGORY_ID
, CAT.CATEGORY_SET_ID
, LOOK.MEANING
, 1
FROM MSC_SYSTEM_ITEMS DRV
, MSC_SYSTEM_ITEMS ASSY_NAME
, MSC_ITEMS COMP_NAME
, MSC_BOMS MB
, MSC_BOM_COMPONENTS ASSY
, MSC_ITEM_CATEGORIES CAT
, MFG_LOOKUPS LOOK
, MSC_COMPONENT_SUBSTITUTES SUB
WHERE NVL(ASSY.DRIVING_ITEM_ID
, ASSY.INVENTORY_ITEM_ID) = DRV.INVENTORY_ITEM_ID
AND CAT.INVENTORY_ITEM_ID = DRV.INVENTORY_ITEM_ID
AND CAT.SR_INSTANCE_ID = DRV.SR_INSTANCE_ID
AND CAT.ORGANIZATION_ID = DRV.ORGANIZATION_ID
AND ASSY.PLAN_ID = DRV.PLAN_ID
AND ASSY.ORGANIZATION_ID = DRV.ORGANIZATION_ID
AND ASSY.SR_INSTANCE_ID = DRV.SR_INSTANCE_ID
AND NVL(ASSY.COMPONENT_TYPE
, -1) <> 10
AND ASSY.USING_ASSEMBLY_ID = ASSY_NAME.INVENTORY_ITEM_ID
AND ASSY.ORGANIZATION_ID = ASSY_NAME.ORGANIZATION_ID
AND ASSY.SR_INSTANCE_ID = ASSY_NAME.SR_INSTANCE_ID
AND ASSY_NAME.PLAN_ID = -1
AND NVL(ASSY.ROUNDING_DIRECTION
, 3) = LOOK.LOOKUP_CODE
AND LOOK.LOOKUP_TYPE = 'MSC_ROUND_DIRECTION'
AND MB.PLAN_ID(+) = ASSY.PLAN_ID
AND MB.BILL_SEQUENCE_ID(+) = ASSY.BILL_SEQUENCE_ID
AND MB.ASSEMBLY_ITEM_ID(+)= ASSY.USING_ASSEMBLY_ID
AND MB.SR_INSTANCE_ID(+)= ASSY.SR_INSTANCE_ID
AND MB.ORGANIZATION_ID(+)=ASSY.ORGANIZATION_ID
AND ASSY.COMPONENT_SEQUENCE_ID = SUB.COMPONENT_SEQUENCE_ID
AND ASSY.BILL_SEQUENCE_ID = SUB.BILL_SEQUENCE_ID
AND ASSY.ORGANIZATION_ID = SUB.ORGANIZATION_ID
AND ASSY.PLAN_ID = SUB.PLAN_ID
AND ASSY.SR_INSTANCE_ID = SUB.SR_INSTANCE_ID
AND SUB.SUBSTITUTE_ITEM_ID = COMP_NAME.INVENTORY_ITEM_ID
AND SUB.ORGANIZATION_ID = DRV.ORGANIZATION_ID
AND SUB.PLAN_ID = DRV.PLAN_ID
AND SUB.SR_INSTANCE_ID = DRV.SR_INSTANCE_ID
AND SUB.ORGANIZATION_ID = ASSY_NAME.ORGANIZATION_ID
AND SUB.SR_INSTANCE_ID = ASSY_NAME.SR_INSTANCE_ID

Columns