VIEW: APPS.MSC_PROJECTS_TREE_V

Source

View Text - Preformatted

SELECT s.project_id, s.plan_id, s.organization_id, s.sr_instance_id, mtp.organization_code, mtp.partner_name, p.project_number, p.project_name, p.project_number_sort_order, p.project_description, s.task_id, t.task_number, t.task_name, t.description, p.planning_group, mtp.modeled_supplier_id, mtp.modeled_supplier_site_id FROM msc_project_tasks t, msc_projects p, msc_supplies s, msc_trading_partners mtp WHERE s.sr_instance_id = p.sr_instance_id AND s.organization_id = p.organization_id AND s.project_id = p.project_id AND p.plan_id = -1 AND mtp.sr_tp_id = p.organization_id AND mtp.sr_instance_id = p.sr_instance_id AND mtp.partner_type = 3 AND s.sr_instance_id = t.sr_instance_id(+) AND s.organization_id = t.organization_id(+) AND s.project_id = t.project_id(+) AND s.task_id = t.task_id(+) AND t.plan_id(+) = -1 UNION SELECT d.project_id, d.plan_id, d.organization_id, d.sr_instance_id, mtp.organization_code, mtp.partner_name, p.project_number, p.project_name, p.project_number_sort_order, p.project_description, d.task_id, t.task_number, t.task_name, t.description, p.planning_group, mtp.modeled_supplier_id, mtp.modeled_supplier_site_id FROM msc_project_tasks t, msc_projects p, msc_demands d, msc_trading_partners mtp WHERE d.sr_instance_id = p.sr_instance_id AND d.organization_id = p.organization_id AND d.project_id = p.project_id AND p.plan_id = -1 AND mtp.sr_tp_id = p.organization_id AND mtp.sr_instance_id = p.sr_instance_id AND mtp.partner_type = 3 AND d.sr_instance_id = t.sr_instance_id(+) AND d.organization_id = t.organization_id(+) AND d.project_id = t.project_id(+) AND d.task_id = t.task_id(+) AND t.plan_id(+) = -1 UNION SELECT to_number(null), s.plan_id, s.organization_id, s.sr_instance_id, mtp.organization_code, mtp.partner_name, NULL, NULL, NULL, NULL, to_number(null), NULL, NULL, NULL, '_COMMON', mtp.modeled_supplier_id, mtp.modeled_supplier_site_id FROM msc_supplies s, msc_trading_partners mtp WHERE s.project_id IS NULL AND mtp.sr_tp_id = s.organization_id AND mtp.sr_instance_id = s.sr_instance_id AND mtp.partner_type = 3 UNION SELECT to_number(null), d.plan_id, d.organization_id, d.sr_instance_id, mtp.organization_code, mtp.partner_name, NULL, NULL, NULL, NULL, to_number(null), NULL, NULL, NULL, '_COMMON', mtp.modeled_supplier_id, mtp.modeled_supplier_site_id FROM msc_demands d, msc_trading_partners mtp WHERE d.project_id IS NULL AND mtp.sr_tp_id = d.organization_id AND mtp.sr_instance_id = d.sr_instance_id AND mtp.partner_type = 3 UNION SELECT to_number(null), o.plan_id, o.organization_id, o.sr_instance_id, o.organization_code, mtp.partner_name, NULL, NULL, NULL, NULL, to_number(null), NULL, NULL, NULL, '_COMMON', mtp.modeled_supplier_id, mtp.modeled_supplier_site_id FROM msc_plan_organizations o, msc_trading_partners mtp WHERE mtp.sr_tp_id = o.organization_id AND mtp.sr_instance_id = o.sr_instance_id AND mtp.partner_type = 3

View Text - HTML Formatted

SELECT S.PROJECT_ID
, S.PLAN_ID
, S.ORGANIZATION_ID
, S.SR_INSTANCE_ID
, MTP.ORGANIZATION_CODE
, MTP.PARTNER_NAME
, P.PROJECT_NUMBER
, P.PROJECT_NAME
, P.PROJECT_NUMBER_SORT_ORDER
, P.PROJECT_DESCRIPTION
, S.TASK_ID
, T.TASK_NUMBER
, T.TASK_NAME
, T.DESCRIPTION
, P.PLANNING_GROUP
, MTP.MODELED_SUPPLIER_ID
, MTP.MODELED_SUPPLIER_SITE_ID
FROM MSC_PROJECT_TASKS T
, MSC_PROJECTS P
, MSC_SUPPLIES S
, MSC_TRADING_PARTNERS MTP
WHERE S.SR_INSTANCE_ID = P.SR_INSTANCE_ID
AND S.ORGANIZATION_ID = P.ORGANIZATION_ID
AND S.PROJECT_ID = P.PROJECT_ID
AND P.PLAN_ID = -1
AND MTP.SR_TP_ID = P.ORGANIZATION_ID
AND MTP.SR_INSTANCE_ID = P.SR_INSTANCE_ID
AND MTP.PARTNER_TYPE = 3
AND S.SR_INSTANCE_ID = T.SR_INSTANCE_ID(+)
AND S.ORGANIZATION_ID = T.ORGANIZATION_ID(+)
AND S.PROJECT_ID = T.PROJECT_ID(+)
AND S.TASK_ID = T.TASK_ID(+)
AND T.PLAN_ID(+) = -1 UNION SELECT D.PROJECT_ID
, D.PLAN_ID
, D.ORGANIZATION_ID
, D.SR_INSTANCE_ID
, MTP.ORGANIZATION_CODE
, MTP.PARTNER_NAME
, P.PROJECT_NUMBER
, P.PROJECT_NAME
, P.PROJECT_NUMBER_SORT_ORDER
, P.PROJECT_DESCRIPTION
, D.TASK_ID
, T.TASK_NUMBER
, T.TASK_NAME
, T.DESCRIPTION
, P.PLANNING_GROUP
, MTP.MODELED_SUPPLIER_ID
, MTP.MODELED_SUPPLIER_SITE_ID
FROM MSC_PROJECT_TASKS T
, MSC_PROJECTS P
, MSC_DEMANDS D
, MSC_TRADING_PARTNERS MTP
WHERE D.SR_INSTANCE_ID = P.SR_INSTANCE_ID
AND D.ORGANIZATION_ID = P.ORGANIZATION_ID
AND D.PROJECT_ID = P.PROJECT_ID
AND P.PLAN_ID = -1
AND MTP.SR_TP_ID = P.ORGANIZATION_ID
AND MTP.SR_INSTANCE_ID = P.SR_INSTANCE_ID
AND MTP.PARTNER_TYPE = 3
AND D.SR_INSTANCE_ID = T.SR_INSTANCE_ID(+)
AND D.ORGANIZATION_ID = T.ORGANIZATION_ID(+)
AND D.PROJECT_ID = T.PROJECT_ID(+)
AND D.TASK_ID = T.TASK_ID(+)
AND T.PLAN_ID(+) = -1 UNION SELECT TO_NUMBER(NULL)
, S.PLAN_ID
, S.ORGANIZATION_ID
, S.SR_INSTANCE_ID
, MTP.ORGANIZATION_CODE
, MTP.PARTNER_NAME
, NULL
, NULL
, NULL
, NULL
, TO_NUMBER(NULL)
, NULL
, NULL
, NULL
, '_COMMON'
, MTP.MODELED_SUPPLIER_ID
, MTP.MODELED_SUPPLIER_SITE_ID
FROM MSC_SUPPLIES S
, MSC_TRADING_PARTNERS MTP
WHERE S.PROJECT_ID IS NULL
AND MTP.SR_TP_ID = S.ORGANIZATION_ID
AND MTP.SR_INSTANCE_ID = S.SR_INSTANCE_ID
AND MTP.PARTNER_TYPE = 3 UNION SELECT TO_NUMBER(NULL)
, D.PLAN_ID
, D.ORGANIZATION_ID
, D.SR_INSTANCE_ID
, MTP.ORGANIZATION_CODE
, MTP.PARTNER_NAME
, NULL
, NULL
, NULL
, NULL
, TO_NUMBER(NULL)
, NULL
, NULL
, NULL
, '_COMMON'
, MTP.MODELED_SUPPLIER_ID
, MTP.MODELED_SUPPLIER_SITE_ID
FROM MSC_DEMANDS D
, MSC_TRADING_PARTNERS MTP
WHERE D.PROJECT_ID IS NULL
AND MTP.SR_TP_ID = D.ORGANIZATION_ID
AND MTP.SR_INSTANCE_ID = D.SR_INSTANCE_ID
AND MTP.PARTNER_TYPE = 3 UNION SELECT TO_NUMBER(NULL)
, O.PLAN_ID
, O.ORGANIZATION_ID
, O.SR_INSTANCE_ID
, O.ORGANIZATION_CODE
, MTP.PARTNER_NAME
, NULL
, NULL
, NULL
, NULL
, TO_NUMBER(NULL)
, NULL
, NULL
, NULL
, '_COMMON'
, MTP.MODELED_SUPPLIER_ID
, MTP.MODELED_SUPPLIER_SITE_ID
FROM MSC_PLAN_ORGANIZATIONS O
, MSC_TRADING_PARTNERS MTP
WHERE MTP.SR_TP_ID = O.ORGANIZATION_ID
AND MTP.SR_INSTANCE_ID = O.SR_INSTANCE_ID
AND MTP.PARTNER_TYPE = 3