Product: | MSC - Advanced Supply Chain Planning |
---|---|
Description: | this view shows all the search criteria for resources |
Implementation/DBA Data: | APPS.MSC_SEARCH_RESOURCES_V |
SELECT P.PLAN_ID
, P.COMPILE_DESIGNATOR
, P.PLAN_TYPE
, P.PLANNED_INSTANCE_ID
, P.PLANNED_ORGANIZATION
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, NULL
, NULL
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, NULL
, NULL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, NULL
FROM MSC_PLANS_TREE_V P
WHERE P.PLAN_COMPLETION_DATE IS NOT NULL
AND P.DATA_COMPLETION_DATE IS NOT NULL UNION ALL SELECT O.PLAN_ID
, P.COMPILE_DESIGNATOR
, P.PLAN_TYPE
, P.PLANNED_INSTANCE_ID
, P.PLANNED_ORGANIZATION
, O.SR_INSTANCE_ID
, O.ORGANIZATION_ID
, O.ORGANIZATION_CODE
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, NULL
, NULL
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, NULL
, NULL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, NULL
FROM MSC_ORGS_TREE_V O
, MSC_PLANS_TREE_V P
WHERE P.PLAN_ID = O.PLAN_ID
AND P.PLAN_COMPLETION_DATE IS NOT NULL
AND P.DATA_COMPLETION_DATE IS NOT NULL UNION ALL SELECT PF.PLAN_ID
, P.COMPILE_DESIGNATOR
, P.PLAN_TYPE
, P.PLANNED_INSTANCE_ID
, P.PLANNED_ORGANIZATION
, PF.SR_INSTANCE_ID
, PF.ORGANIZATION_ID
, PF.ORGANIZATION_CODE
, PF.PRODUCT_FAMILY_ID
, PF.PF_NUMBER
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, NULL
, NULL
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, NULL
, NULL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, NULL
FROM MSC_PRODUCT_FAMILIES_TREE_V PF
, MSC_PLANS_TREE_V P
WHERE P.PLAN_ID = PF.PLAN_ID
AND P.PLAN_COMPLETION_DATE IS NOT NULL
AND P.DATA_COMPLETION_DATE IS NOT NULL UNION ALL SELECT D.PLAN_ID
, P.COMPILE_DESIGNATOR
, P.PLAN_TYPE
, P.PLANNED_INSTANCE_ID
, P.PLANNED_ORGANIZATION
, D.SR_INSTANCE_ID
, D.ORGANIZATION_ID
, TP.ORGANIZATION_CODE
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, NULL
, DECODE(D.LINE_FLAG
, 2
, D.DEPARTMENT_ID
, TO_NUMBER(NULL))
, DECODE(D.LINE_FLAG
, 2
, D.DEPARTMENT_CODE
, NULL)
, DECODE(D.LINE_FLAG
, 2
, D.RESOURCE_ID
, TO_NUMBER(NULL))
, DECODE(D.LINE_FLAG
, 2
, D.RESOURCE_CODE
, NULL)
, DECODE(D.LINE_FLAG
, 1
, D.DEPARTMENT_ID
, TO_NUMBER(NULL))
, DECODE(D.LINE_FLAG
, 1
, D.DEPARTMENT_CODE
, NULL)
, NULL
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, NULL
, NULL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, NULL
FROM MSC_RESOURCES_TREE_V D
, MSC_TRADING_PARTNERS TP
, MSC_PLANS_TREE_V P
WHERE P.PLAN_ID = D.PLAN_ID
AND TP.SR_INSTANCE_ID = D.SR_INSTANCE_ID
AND TP.SR_TP_ID = D.ORGANIZATION_ID
AND TP.PARTNER_TYPE = 3
AND P.PLAN_COMPLETION_DATE IS NOT NULL
AND P.DATA_COMPLETION_DATE IS NOT NULL UNION ALL SELECT D.PLAN_ID
, P.COMPILE_DESIGNATOR
, P.PLAN_TYPE
, P.PLANNED_INSTANCE_ID
, P.PLANNED_ORGANIZATION
, D.SR_INSTANCE_ID
, D.ORGANIZATION_ID
, TP.ORGANIZATION_CODE
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, D.INVENTORY_ITEM_ID
, D.ITEM_NUMBER
, TO_NUMBER(NULL)
, NULL
, DECODE(D.LINE_FLAG
, 2
, D.DEPARTMENT_ID
, TO_NUMBER(NULL))
, DECODE(D.LINE_FLAG
, 2
, D.DEPARTMENT_CODE
, NULL)
, DECODE(D.LINE_FLAG
, 2
, D.RESOURCE_ID
, TO_NUMBER(NULL))
, DECODE(D.LINE_FLAG
, 2
, D.RESOURCE_CODE
, NULL)
, DECODE(D.LINE_FLAG
, 1
, D.DEPARTMENT_ID
, TO_NUMBER(NULL))
, DECODE(D.LINE_FLAG
, 1
, D.DEPARTMENT_CODE
, NULL)
, NULL
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, NULL
, NULL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, NULL
FROM MSC_RESOURCE_ITEMS_TREE_V D
, MSC_TRADING_PARTNERS TP
, MSC_PLANS_TREE_V P
WHERE P.PLAN_ID = D.PLAN_ID
AND P.PLAN_COMPLETION_DATE IS NOT NULL
AND P.DATA_COMPLETION_DATE IS NOT NULL
AND TP.SR_INSTANCE_ID = D.SR_INSTANCE_ID
AND TP.SR_TP_ID = D.ORGANIZATION_ID
AND TP.PARTNER_TYPE = 3