FND Design Data [Home] [Help]

View: MSC_SEARCH_ORGS_V

Product: MSC - Advanced Supply Chain Planning
Description: this view shows all the search criteria for organizations
Implementation/DBA Data: ViewAPPS.MSC_SEARCH_ORGS_V
View Text

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
, NULL
, NULL
, TO_NUMBER(NULL)
, NULL
, NULL
, TO_NUMBER(NULL)
, TO_NUMBER(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
, NULL
, NULL
, TO_NUMBER(NULL)
, NULL
, NULL
, TO_NUMBER(NULL)
, TO_NUMBER(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
, NULL
, NULL
, TO_NUMBER(NULL)
, NULL
, NULL
, TO_NUMBER(NULL)
, TO_NUMBER(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 I.PLAN_ID
, P.COMPILE_DESIGNATOR
, P.PLAN_TYPE
, P.PLANNED_INSTANCE_ID
, P.PLANNED_ORGANIZATION
, I.SR_INSTANCE_ID
, I.ORGANIZATION_ID
, I.ORGANIZATION_CODE
, NVL(I.PRODUCT_FAMILY_ID
, I.BASE_ITEM_ID)
, NULL
, C.CATEGORY_SET_ID
, C.SR_CATEGORY_ID
, C.CATEGORY_NAME
, I.INVENTORY_ITEM_ID
, I.ITEM_NAME
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, NULL
, I.PLANNER_CODE
, I.BUYER_ID
, I.BUYER_NAME
, I.ABC_CLASS
, I.ABC_CLASS_NAME
, TO_NUMBER(NULL)
, NULL
, NULL
, NULL
, NULL
, TO_NUMBER(NULL)
, NULL
, NULL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
FROM MSC_SYSTEM_ITEMS I
, MSC_ITEM_CATEGORIES C
, MSC_PLANS_TREE_V P
WHERE I.INVENTORY_ITEM_ID = C.INVENTORY_ITEM_ID
AND I.ORGANIZATION_ID = C.ORGANIZATION_ID
AND I.SR_INSTANCE_ID = C.SR_INSTANCE_ID
AND P.PLAN_ID = I.PLAN_ID
AND P.PLAN_COMPLETION_DATE IS NOT NULL
AND P.DATA_COMPLETION_DATE IS NOT NULL UNION ALL SELECT B.PLAN_ID
, P.COMPILE_DESIGNATOR
, P.PLAN_TYPE
, P.PLANNED_INSTANCE_ID
, P.PLANNED_ORGANIZATION
, I.SR_INSTANCE_ID
, I.ORGANIZATION_ID
, I.ORGANIZATION_CODE
, NVL(I.PRODUCT_FAMILY_ID
, I.BASE_ITEM_ID)
, NULL
, C.CATEGORY_SET_ID
, C.SR_CATEGORY_ID
, C.CATEGORY_NAME
, B.USING_ASSEMBLY_ID
, I.ITEM_NAME
, B.INVENTORY_ITEM_ID
, NULL
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, NULL
, I.PLANNER_CODE
, I.BUYER_ID
, I.BUYER_NAME
, I.ABC_CLASS
, I.ABC_CLASS_NAME
, TO_NUMBER(NULL)
, NULL
, NULL
, NULL
, NULL
, TO_NUMBER(NULL)
, NULL
, NULL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
FROM MSC_SYSTEM_ITEMS I
, MSC_ITEM_CATEGORIES C
, MSC_BOM_COMPONENTS B
, MSC_PLANS_TREE_V P
WHERE I.PLAN_ID = B.PLAN_ID
AND I.ORGANIZATION_ID = B.ORGANIZATION_ID
AND I.SR_INSTANCE_ID = B.SR_INSTANCE_ID
AND I.INVENTORY_ITEM_ID = B.USING_ASSEMBLY_ID
AND C.ORGANIZATION_ID = I.ORGANIZATION_ID
AND C.SR_INSTANCE_ID = I.SR_INSTANCE_ID
AND C.INVENTORY_ITEM_ID = I.INVENTORY_ITEM_ID
AND P.PLAN_ID = I.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
, NULL
, NULL
, TO_NUMBER(NULL)
, NULL
, NULL
, TO_NUMBER(NULL)
, TO_NUMBER(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 DISTINCT I.PLAN_ID
, P.COMPILE_DESIGNATOR
, P.PLAN_TYPE
, P.PLANNED_INSTANCE_ID
, P.PLANNED_ORGANIZATION
, I.SR_INSTANCE_ID
, I.ORGANIZATION_ID
, I.ORGANIZATION_CODE
, TO_NUMBER(NULL)
, NULL
, C.CATEGORY_SET_ID
, C.SR_CATEGORY_ID
, C.CATEGORY_NAME
, 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
, NULL
, NULL
, TO_NUMBER(NULL)
, NULL
, NULL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
FROM MSC_ITEM_CATEGORIES C
, MSC_SYSTEM_ITEMS I
, MSC_PLANS_TREE_V P
WHERE I.INVENTORY_ITEM_ID = C.INVENTORY_ITEM_ID
AND I.ORGANIZATION_ID = C.ORGANIZATION_ID
AND I.SR_INSTANCE_ID = C.SR_INSTANCE_ID
AND P.PLAN_ID = I.PLAN_ID
AND P.PLAN_COMPLETION_DATE IS NOT NULL
AND P.DATA_COMPLETION_DATE IS NOT NULL UNION ALL SELECT DISTINCT I.PLAN_ID
, P.COMPILE_DESIGNATOR
, P.PLAN_TYPE
, P.PLANNED_INSTANCE_ID
, P.PLANNED_ORGANIZATION
, I.SR_INSTANCE_ID
, I.ORGANIZATION_ID
, I.ORGANIZATION_CODE
, TO_NUMBER(NULL)
, NULL
, C.CATEGORY_SET_ID
, C.SR_CATEGORY_ID
, C.CATEGORY_NAME
, I.INVENTORY_ITEM_ID
, I.ITEM_NAME
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, NULL
, I.PLANNER_CODE
, I.BUYER_ID
, I.BUYER_NAME
, I.ABC_CLASS
, I.ABC_CLASS_NAME
, S.SUPPLIER_ID
, TP.PARTNER_NAME
, NULL
, NULL
, NULL
, TO_NUMBER(NULL)
, NULL
, NULL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
FROM MSC_ITEM_CATEGORIES C
, MSC_SYSTEM_ITEMS I
, MSC_PLANS_TREE_V P
, MSC_ITEM_SUPPLIERS S
, MSC_TRADING_PARTNERS TP
WHERE I.INVENTORY_ITEM_ID = C.INVENTORY_ITEM_ID
AND I.ORGANIZATION_ID = C.ORGANIZATION_ID
AND I.SR_INSTANCE_ID = C.SR_INSTANCE_ID
AND P.PLAN_ID = I.PLAN_ID
AND P.PLAN_COMPLETION_DATE IS NOT NULL
AND P.DATA_COMPLETION_DATE IS NOT NULL
AND 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.SUPPLIER_ID = TP.PARTNER_ID
AND TP.PARTNER_TYPE = 1

Columns

PLAN_ID
COMPILE_DESIGNATOR
PLAN_TYPE
PLANNED_INSTANCE_ID
PLANNED_ORGANIZATION
SR_INSTANCE_ID
ORGANIZATION_ID
ORGANIZATION_CODE
PRODUCT_FAMILY_ID
PRODUCT_FAMILY_NAME
CATEGORY_SET_ID
CATEGORY_ID
CATEGORY_NAME
INVENTORY_ITEM_ID
ITEM_NAME
COMPONENT_ID
COMPONENT_NAME
DEPARTMENT_ID
DEPARTMENT_CODE
RESOURCE_ID
RESOURCE_CODE
LINE_ID
LINE_CODE
PLANNER_CODE
BUYER_ID
BUYER_NAME
ABC_CLASS
ABC_CLASS_NAME
SUPPLIER_ID
SUPPLIER_NAME
DEPARTMENT_CLASS
RESOURCE_GROUP
PLANNING_GROUP
PROJECT_ID
TASK_NUMBER
VERSION
EXCEPTION_GROUP
EXCEPTION_TYPE
Name