View: MSC_PROJECTS_TREE_V

Product:	MSC - Advanced Supply Chain Planning
Description:	This view shows all the projects in a plan in the tree
Implementation/DBA Data:	APPS.MSC_PROJECTS_TREE_V

View Text

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
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
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'
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'
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'
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

Columns