VIEW: APPS.MSC_PROJECT_ITEMS_TREE_V

Source

View Text - Preformatted

SELECT s.plan_id, s.organization_id, s.sr_instance_id, s.planning_group, s.project_id, p.project_number, p.project_name, s.inventory_item_id, i.item_name, i.description FROM msc_system_items i, msc_projects p, msc_supplies s WHERE s.inventory_item_id = i.inventory_item_id AND s.organization_id = i.organization_id AND s.sr_instance_id = i.sr_instance_id AND s.plan_id = i.plan_id AND p.project_id = s.project_id AND p.sr_instance_id = s.sr_instance_id AND p.organization_id = s.organization_id AND p.plan_id = -1 AND s.project_id IS NOT NULL UNION ALL SELECT d.plan_id, d.organization_id, d.sr_instance_id, d.planning_group, d.project_id, p.project_number, p.project_name, d.inventory_item_id, i.item_name, i.description FROM msc_system_items i, msc_projects p, msc_demands d WHERE d.inventory_item_id = i.inventory_item_id AND d.organization_id = i.organization_id AND d.sr_instance_id = i.sr_instance_id AND d.plan_id = i.plan_id AND p.project_id = d.project_id AND p.sr_instance_id = d.sr_instance_id AND p.organization_id = d.organization_id AND p.plan_id = -1 AND d.project_id IS NOT NULL UNION ALL SELECT s.plan_id, s.organization_id, s.sr_instance_id, '_COMMON', to_number(NULL), NULL, NULL, s.inventory_item_id, i.item_name, i.description FROM msc_system_items i, msc_supplies s WHERE s.inventory_item_id = i.inventory_item_id AND s.organization_id = i.organization_id AND s.sr_instance_id = i.sr_instance_id AND s.plan_id = i.plan_id AND s.project_id IS NULL UNION ALL SELECT d.plan_id, d.organization_id, d.sr_instance_id, '_COMMON', to_number(NULL), NULL, NULL, d.inventory_item_id, i.item_name, i.description FROM msc_system_items i, msc_demands d WHERE d.inventory_item_id = i.inventory_item_id AND d.organization_id = i.organization_id AND d.sr_instance_id = i.sr_instance_id AND d.plan_id = i.plan_id AND d.project_id IS NULL

View Text - HTML Formatted

SELECT S.PLAN_ID
, S.ORGANIZATION_ID
, S.SR_INSTANCE_ID
, S.PLANNING_GROUP
, S.PROJECT_ID
, P.PROJECT_NUMBER
, P.PROJECT_NAME
, S.INVENTORY_ITEM_ID
, I.ITEM_NAME
, I.DESCRIPTION
FROM MSC_SYSTEM_ITEMS I
, MSC_PROJECTS P
, MSC_SUPPLIES S
WHERE S.INVENTORY_ITEM_ID = I.INVENTORY_ITEM_ID
AND S.ORGANIZATION_ID = I.ORGANIZATION_ID
AND S.SR_INSTANCE_ID = I.SR_INSTANCE_ID
AND S.PLAN_ID = I.PLAN_ID
AND P.PROJECT_ID = S.PROJECT_ID
AND P.SR_INSTANCE_ID = S.SR_INSTANCE_ID
AND P.ORGANIZATION_ID = S.ORGANIZATION_ID
AND P.PLAN_ID = -1
AND S.PROJECT_ID IS NOT NULL UNION ALL SELECT D.PLAN_ID
, D.ORGANIZATION_ID
, D.SR_INSTANCE_ID
, D.PLANNING_GROUP
, D.PROJECT_ID
, P.PROJECT_NUMBER
, P.PROJECT_NAME
, D.INVENTORY_ITEM_ID
, I.ITEM_NAME
, I.DESCRIPTION
FROM MSC_SYSTEM_ITEMS I
, MSC_PROJECTS P
, MSC_DEMANDS D
WHERE D.INVENTORY_ITEM_ID = I.INVENTORY_ITEM_ID
AND D.ORGANIZATION_ID = I.ORGANIZATION_ID
AND D.SR_INSTANCE_ID = I.SR_INSTANCE_ID
AND D.PLAN_ID = I.PLAN_ID
AND P.PROJECT_ID = D.PROJECT_ID
AND P.SR_INSTANCE_ID = D.SR_INSTANCE_ID
AND P.ORGANIZATION_ID = D.ORGANIZATION_ID
AND P.PLAN_ID = -1
AND D.PROJECT_ID IS NOT NULL UNION ALL SELECT S.PLAN_ID
, S.ORGANIZATION_ID
, S.SR_INSTANCE_ID
, '_COMMON'
, TO_NUMBER(NULL)
, NULL
, NULL
, S.INVENTORY_ITEM_ID
, I.ITEM_NAME
, I.DESCRIPTION
FROM MSC_SYSTEM_ITEMS I
, MSC_SUPPLIES S
WHERE S.INVENTORY_ITEM_ID = I.INVENTORY_ITEM_ID
AND S.ORGANIZATION_ID = I.ORGANIZATION_ID
AND S.SR_INSTANCE_ID = I.SR_INSTANCE_ID
AND S.PLAN_ID = I.PLAN_ID
AND S.PROJECT_ID IS NULL UNION ALL SELECT D.PLAN_ID
, D.ORGANIZATION_ID
, D.SR_INSTANCE_ID
, '_COMMON'
, TO_NUMBER(NULL)
, NULL
, NULL
, D.INVENTORY_ITEM_ID
, I.ITEM_NAME
, I.DESCRIPTION
FROM MSC_SYSTEM_ITEMS I
, MSC_DEMANDS D
WHERE D.INVENTORY_ITEM_ID = I.INVENTORY_ITEM_ID
AND D.ORGANIZATION_ID = I.ORGANIZATION_ID
AND D.SR_INSTANCE_ID = I.SR_INSTANCE_ID
AND D.PLAN_ID = I.PLAN_ID
AND D.PROJECT_ID IS NULL