VIEW: APPS.MSC_COMPONENTS_TREE_V

Source

View Text - Preformatted

SELECT comp.plan_id, comp.sr_instance_id, comp.organization_id, comp.inventory_item_id, item.item_name, item.description, comp.using_assembly_id, item1.item_name, item1.description, 2 FROM msc_items item1, msc_items item, msc_bom_components comp WHERE item.inventory_item_id = comp.inventory_item_id AND item1.inventory_item_id = comp.using_assembly_id AND sysdate BETWEEN trunc(comp.EFFECTIVITY_DATE) AND NVL(trunc(comp.DISABLE_DATE), TO_DATE(2634525, 'J')) and nvl(comp.component_type,-1) <> 10 UNION ALL select sub.plan_id, sub.sr_instance_id, sub.organization_id, sub.substitute_item_id, item.item_name, item.description, comp.using_assembly_id, item1.item_name, item1.description, 1 FROM msc_items item1, msc_items item, msc_bom_components comp, msc_component_substitutes sub WHERE item.inventory_item_id = sub.substitute_item_id and item1.inventory_item_id = comp.using_assembly_id and comp.bill_sequence_id = sub.bill_sequence_id and comp.component_sequence_id = sub.component_sequence_id and comp.plan_id = sub.plan_id and comp.sr_instance_id = sub.sr_instance_id and comp.organization_id = sub.organization_id AND sysdate BETWEEN trunc(comp.EFFECTIVITY_DATE) AND NVL(trunc(comp.DISABLE_DATE), TO_DATE(2634525, 'J')) and nvl(comp.component_type,-1) <> 10

View Text - HTML Formatted

SELECT COMP.PLAN_ID
, COMP.SR_INSTANCE_ID
, COMP.ORGANIZATION_ID
, COMP.INVENTORY_ITEM_ID
, ITEM.ITEM_NAME
, ITEM.DESCRIPTION
, COMP.USING_ASSEMBLY_ID
, ITEM1.ITEM_NAME
, ITEM1.DESCRIPTION
, 2
FROM MSC_ITEMS ITEM1
, MSC_ITEMS ITEM
, MSC_BOM_COMPONENTS COMP
WHERE ITEM.INVENTORY_ITEM_ID = COMP.INVENTORY_ITEM_ID
AND ITEM1.INVENTORY_ITEM_ID = COMP.USING_ASSEMBLY_ID
AND SYSDATE BETWEEN TRUNC(COMP.EFFECTIVITY_DATE)
AND NVL(TRUNC(COMP.DISABLE_DATE)
, TO_DATE(2634525
, 'J'))
AND NVL(COMP.COMPONENT_TYPE
, -1) <> 10 UNION ALL SELECT SUB.PLAN_ID
, SUB.SR_INSTANCE_ID
, SUB.ORGANIZATION_ID
, SUB.SUBSTITUTE_ITEM_ID
, ITEM.ITEM_NAME
, ITEM.DESCRIPTION
, COMP.USING_ASSEMBLY_ID
, ITEM1.ITEM_NAME
, ITEM1.DESCRIPTION
, 1
FROM MSC_ITEMS ITEM1
, MSC_ITEMS ITEM
, MSC_BOM_COMPONENTS COMP
, MSC_COMPONENT_SUBSTITUTES SUB
WHERE ITEM.INVENTORY_ITEM_ID = SUB.SUBSTITUTE_ITEM_ID
AND ITEM1.INVENTORY_ITEM_ID = COMP.USING_ASSEMBLY_ID
AND COMP.BILL_SEQUENCE_ID = SUB.BILL_SEQUENCE_ID
AND COMP.COMPONENT_SEQUENCE_ID = SUB.COMPONENT_SEQUENCE_ID
AND COMP.PLAN_ID = SUB.PLAN_ID
AND COMP.SR_INSTANCE_ID = SUB.SR_INSTANCE_ID
AND COMP.ORGANIZATION_ID = SUB.ORGANIZATION_ID
AND SYSDATE BETWEEN TRUNC(COMP.EFFECTIVITY_DATE)
AND NVL(TRUNC(COMP.DISABLE_DATE)
, TO_DATE(2634525
, 'J'))
AND NVL(COMP.COMPONENT_TYPE
, -1) <> 10