VIEW: APPS.MSC_SEARCH_ACTIONS_V

Source

View Text - Preformatted

SELECT p.plan_id, p.compile_designator, p.plan_type, p.planned_instance_id, p.planned_organization, 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, null, null, to_number(null), to_number(null), to_number(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 p.plan_id, p.compile_designator, p.plan_type, p.planned_instance_id, p.planned_organization, null, a.exception_group, ml.meaning, to_number(null), null, to_number(null), to_number(null), null, to_number(null), null, to_number(null), null, to_number(null), null, null, null, to_number(null), to_number(null), to_number(null), null, to_number(null), to_number(null), null, null from msc_plans_tree_v p, msc_actions_tree_v a, mfg_lookups ml where p.plan_id = a.plan_id and p.plan_completion_date IS NOT NULL and p.data_completion_date IS NOT NULL and ml.lookup_code = a.exception_group and ml.lookup_type='MSC_EXCEPTION_GROUP' UNION ALL SELECT p.plan_id, p.compile_designator, p.plan_type, p.planned_instance_id, p.planned_organization, null, a.exception_group, ml_grp.meaning, a.exception_type, ml_type.meaning, to_number(null), to_number(null), null, to_number(null), null, to_number(null), null, to_number(null), null, null, null, to_number(null), to_number(null), to_number(null), null, to_number(null), to_number(null), null, null from msc_plans_tree_v p, msc_actions_tree_v a, mfg_lookups ml_grp, mfg_lookups ml_type where p.plan_id = a.plan_id and p.plan_completion_date IS NOT NULL and p.data_completion_date IS NOT NULL and ml_grp.lookup_type = 'MSC_EXCEPTION_GROUP' and ml_grp.lookup_code = a.exception_group and ml_type.lookup_type = 'MRP_EXCEPTION_CODE_TYPE' and ml_type.lookup_code = a.exception_type UNION ALL SELECT p.plan_id, p.compile_designator, p.plan_type, p.planned_instance_id, p.planned_organization, null, a.exception_group, ml_grp.meaning, a.exception_type, ml_type.meaning, o.sr_instance_id, o.organization_id, o.organization_code, o.inventory_item_id, o.item_number, o.department_id, o.department_code, o.resource_id, o.resource_code, null, null, to_number(null), to_number(null), to_number(null), null, to_number(null), to_number(null), null, null from msc_plans_tree_v p, msc_action_orders_tree_v o, msc_actions_tree_v a, mfg_lookups ml_grp, mfg_lookups ml_type where p.plan_id = a.plan_id and p.plan_completion_date IS NOT NULL and p.data_completion_date IS NOT NULL and a.plan_id = o.plan_id and a.exception_type = o.exception_type and ml_grp.lookup_type = 'MSC_EXCEPTION_GROUP' and ml_grp.lookup_code = a.exception_group and ml_type.lookup_type = 'MRP_EXCEPTION_CODE_TYPE' and ml_type.lookup_code = a.exception_type

View Text - HTML Formatted

SELECT P.PLAN_ID
, P.COMPILE_DESIGNATOR
, P.PLAN_TYPE
, P.PLANNED_INSTANCE_ID
, P.PLANNED_ORGANIZATION
, 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
, NULL
, NULL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(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 P.PLAN_ID
, P.COMPILE_DESIGNATOR
, P.PLAN_TYPE
, P.PLANNED_INSTANCE_ID
, P.PLANNED_ORGANIZATION
, NULL
, A.EXCEPTION_GROUP
, ML.MEANING
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, NULL
, NULL
, NULL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, NULL
FROM MSC_PLANS_TREE_V P
, MSC_ACTIONS_TREE_V A
, MFG_LOOKUPS ML
WHERE P.PLAN_ID = A.PLAN_ID
AND P.PLAN_COMPLETION_DATE IS NOT NULL
AND P.DATA_COMPLETION_DATE IS NOT NULL
AND ML.LOOKUP_CODE = A.EXCEPTION_GROUP
AND ML.LOOKUP_TYPE='MSC_EXCEPTION_GROUP' UNION ALL SELECT P.PLAN_ID
, P.COMPILE_DESIGNATOR
, P.PLAN_TYPE
, P.PLANNED_INSTANCE_ID
, P.PLANNED_ORGANIZATION
, NULL
, A.EXCEPTION_GROUP
, ML_GRP.MEANING
, A.EXCEPTION_TYPE
, ML_TYPE.MEANING
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, NULL
, NULL
, NULL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, NULL
FROM MSC_PLANS_TREE_V P
, MSC_ACTIONS_TREE_V A
, MFG_LOOKUPS ML_GRP
, MFG_LOOKUPS ML_TYPE
WHERE P.PLAN_ID = A.PLAN_ID
AND P.PLAN_COMPLETION_DATE IS NOT NULL
AND P.DATA_COMPLETION_DATE IS NOT NULL
AND ML_GRP.LOOKUP_TYPE = 'MSC_EXCEPTION_GROUP'
AND ML_GRP.LOOKUP_CODE = A.EXCEPTION_GROUP
AND ML_TYPE.LOOKUP_TYPE = 'MRP_EXCEPTION_CODE_TYPE'
AND ML_TYPE.LOOKUP_CODE = A.EXCEPTION_TYPE UNION ALL SELECT P.PLAN_ID
, P.COMPILE_DESIGNATOR
, P.PLAN_TYPE
, P.PLANNED_INSTANCE_ID
, P.PLANNED_ORGANIZATION
, NULL
, A.EXCEPTION_GROUP
, ML_GRP.MEANING
, A.EXCEPTION_TYPE
, ML_TYPE.MEANING
, O.SR_INSTANCE_ID
, O.ORGANIZATION_ID
, O.ORGANIZATION_CODE
, O.INVENTORY_ITEM_ID
, O.ITEM_NUMBER
, O.DEPARTMENT_ID
, O.DEPARTMENT_CODE
, O.RESOURCE_ID
, O.RESOURCE_CODE
, NULL
, NULL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, NULL
FROM MSC_PLANS_TREE_V P
, MSC_ACTION_ORDERS_TREE_V O
, MSC_ACTIONS_TREE_V A
, MFG_LOOKUPS ML_GRP
, MFG_LOOKUPS ML_TYPE
WHERE P.PLAN_ID = A.PLAN_ID
AND P.PLAN_COMPLETION_DATE IS NOT NULL
AND P.DATA_COMPLETION_DATE IS NOT NULL
AND A.PLAN_ID = O.PLAN_ID
AND A.EXCEPTION_TYPE = O.EXCEPTION_TYPE
AND ML_GRP.LOOKUP_TYPE = 'MSC_EXCEPTION_GROUP'
AND ML_GRP.LOOKUP_CODE = A.EXCEPTION_GROUP
AND ML_TYPE.LOOKUP_TYPE = 'MRP_EXCEPTION_CODE_TYPE'
AND ML_TYPE.LOOKUP_CODE = A.EXCEPTION_TYPE