VIEW: APPS.MSC_COMPONENTS_SC_V

Source

View Text - Preformatted

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 , decode(mtp.organization_type ,2 ,decode(assy.scaling_type,null, null, msc_get_name.lookup_meaning('MSC_SCALING_TYPE', decode(assy.scaling_type, 0,0,2,0,1,1,3,1,4,2,5,2))), msc_get_name.lookup_meaning('MSC_RES_BASIS_TYPE', assy.scaling_type) ) , decode(mtp.organization_type ,2 , decode(assy.scaling_type, null, null, msc_get_name.lookup_meaning('SYS_YES_NO', decode (assy.scaling_type, 0,1,1,1,4,1, 2,2,3,2,5,2))), null ) ,assy.component_yield_factor 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_trading_partners mtp 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 and mtp.sr_tp_id = assy.organization_id and mtp.sr_instance_id = assy.sr_instance_id and mtp.partner_type = 3 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 , decode(mtp.organization_type ,2 ,decode(assy.scaling_type,null, null, msc_get_name.lookup_meaning('MSC_SCALING_TYPE', decode(assy.scaling_type, 0,0,2,0,1,1,3,1,4,2,5,2))), msc_get_name.lookup_meaning('MSC_RES_BASIS_TYPE', assy.scaling_type) ) , decode(mtp.organization_type ,2 , decode(assy.scaling_type, null, null, msc_get_name.lookup_meaning('SYS_YES_NO', decode (assy.scaling_type, 0,1,1,1,4,1, 2,2,3,2,5,2))), null ) ,assy.component_yield_factor 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 , msc_trading_partners mtp 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 and mtp.sr_tp_id = assy.organization_id and mtp.sr_instance_id = assy.sr_instance_id and mtp.partner_type = 3

View Text - HTML Formatted

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
, DECODE(MTP.ORGANIZATION_TYPE
, 2
, DECODE(ASSY.SCALING_TYPE
, NULL
, NULL
, MSC_GET_NAME.LOOKUP_MEANING('MSC_SCALING_TYPE'
, DECODE(ASSY.SCALING_TYPE
, 0
, 0
, 2
, 0
, 1
, 1
, 3
, 1
, 4
, 2
, 5
, 2)))
, MSC_GET_NAME.LOOKUP_MEANING('MSC_RES_BASIS_TYPE'
, ASSY.SCALING_TYPE) )
, DECODE(MTP.ORGANIZATION_TYPE
, 2
, DECODE(ASSY.SCALING_TYPE
, NULL
, NULL
, MSC_GET_NAME.LOOKUP_MEANING('SYS_YES_NO'
, DECODE (ASSY.SCALING_TYPE
, 0
, 1
, 1
, 1
, 4
, 1
, 2
, 2
, 3
, 2
, 5
, 2)))
, NULL )
, ASSY.COMPONENT_YIELD_FACTOR
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_TRADING_PARTNERS MTP
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
AND MTP.SR_TP_ID = ASSY.ORGANIZATION_ID
AND MTP.SR_INSTANCE_ID = ASSY.SR_INSTANCE_ID
AND MTP.PARTNER_TYPE = 3 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
, DECODE(MTP.ORGANIZATION_TYPE
, 2
, DECODE(ASSY.SCALING_TYPE
, NULL
, NULL
, MSC_GET_NAME.LOOKUP_MEANING('MSC_SCALING_TYPE'
, DECODE(ASSY.SCALING_TYPE
, 0
, 0
, 2
, 0
, 1
, 1
, 3
, 1
, 4
, 2
, 5
, 2)))
, MSC_GET_NAME.LOOKUP_MEANING('MSC_RES_BASIS_TYPE'
, ASSY.SCALING_TYPE) )
, DECODE(MTP.ORGANIZATION_TYPE
, 2
, DECODE(ASSY.SCALING_TYPE
, NULL
, NULL
, MSC_GET_NAME.LOOKUP_MEANING('SYS_YES_NO'
, DECODE (ASSY.SCALING_TYPE
, 0
, 1
, 1
, 1
, 4
, 1
, 2
, 2
, 3
, 2
, 5
, 2)))
, NULL )
, ASSY.COMPONENT_YIELD_FACTOR
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
, MSC_TRADING_PARTNERS MTP
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
AND MTP.SR_TP_ID = ASSY.ORGANIZATION_ID
AND MTP.SR_INSTANCE_ID = ASSY.SR_INSTANCE_ID
AND MTP.PARTNER_TYPE = 3