VIEW: APPS.MSC_SEARCH_PROJECTS_V

Source

View Text - Preformatted

SELECT p.plan_id, p.compile_designator, p.plan_type, p.planned_instance_id, p.planned_organization, to_number(null), to_number(null), null, null, to_number(null), null, to_number(null), null, to_number(null), null, to_number(null), to_number(null), to_number(null), to_number(null), to_number(null), to_number(null), to_number(null), 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 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, null, to_number(null), null, to_number(null), null, to_number(null), null, to_number(null), to_number(null), to_number(null), to_number(null), to_number(null), to_number(null), to_number(null), null, to_number(null), to_number(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 DISTINCT p.plan_id, p2.compile_designator, p2.plan_type, p2.planned_instance_id, p2.planned_organization, p.sr_instance_id, p.organization_id, null, p.planning_group, to_number(null), null, to_number(null), null, to_number(null), null, to_number(null), to_number(null), to_number(null), to_number(null), to_number(null), to_number(null), to_number(null), null, to_number(null), to_number(null), null, to_number(null), to_number(null), null, null from msc_projects p, msc_plans_tree_v p2 where p2.plan_id = p.plan_id and p2.plan_completion_date IS NOT NULL and p2.data_completion_date IS NOT NULL UNION ALL SELECT DISTINCT p.plan_id, p2.compile_designator, p2.plan_type, p2.planned_instance_id, p2.planned_organization, p.sr_instance_id, p.organization_id, null, p.planning_group, p.project_id, p.project_number, to_number(null), null, to_number(null), null, to_number(null), to_number(null), to_number(null), to_number(null), to_number(null), to_number(null), to_number(null), null, to_number(null), to_number(null), null, to_number(null), to_number(null), null, null from msc_projects p, msc_plans_tree_v p2 where p2.plan_id = p.plan_id and p2.plan_completion_date IS NOT NULL and p2.data_completion_date IS NOT NULL UNION ALL SELECT DISTINCT p.plan_id, p2.compile_designator, p2.plan_type, p2.planned_instance_id, p2.planned_organization, p.sr_instance_id, p.organization_id, null, p.planning_group, p.project_id, p.project_number, t.task_id, t.task_number, to_number(null), null, to_number(null), to_number(null), to_number(null), to_number(null), to_number(null), to_number(null), to_number(null), null, to_number(null), to_number(null), null, to_number(null), to_number(null), null, null FROM msc_project_tasks t, msc_projects p, msc_plans_tree_v p2 WHERE p.plan_id = t.plan_id AND p.sr_instance_id = t.sr_instance_id AND p.organization_id = t.organization_id AND p.project_id = t.project_id AND p.plan_id = p2.plan_id and p2.plan_completion_date IS NOT NULL and p2.data_completion_date IS NOT NULL UNION ALL SELECT DISTINCT p.plan_id, p2.compile_designator, p2.plan_type, p2.planned_instance_id, p2.planned_organization, p.sr_instance_id, p.organization_id, null, p.planning_group, p.project_id, null, p.task_id, null, p.inventory_item_id, p.item_number, to_number(null), to_number(null), to_number(null), to_number(null), to_number(null), to_number(null), to_number(null), null, to_number(null), to_number(null), null, to_number(null), to_number(null), null, null FROM msc_project_task_items_tree_v p, msc_plans_tree_v p2 where p2.plan_id = p.plan_id and p2.plan_completion_date IS NOT NULL and p2.data_completion_date IS NOT NULL

View Text - HTML Formatted

SELECT P.PLAN_ID
, P.COMPILE_DESIGNATOR
, P.PLAN_TYPE
, P.PLANNED_INSTANCE_ID
, P.PLANNED_ORGANIZATION
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, NULL
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, 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 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
, NULL
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, TO_NUMBER(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 DISTINCT P.PLAN_ID
, P2.COMPILE_DESIGNATOR
, P2.PLAN_TYPE
, P2.PLANNED_INSTANCE_ID
, P2.PLANNED_ORGANIZATION
, P.SR_INSTANCE_ID
, P.ORGANIZATION_ID
, NULL
, P.PLANNING_GROUP
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, NULL
FROM MSC_PROJECTS P
, MSC_PLANS_TREE_V P2
WHERE P2.PLAN_ID = P.PLAN_ID
AND P2.PLAN_COMPLETION_DATE IS NOT NULL
AND P2.DATA_COMPLETION_DATE IS NOT NULL UNION ALL SELECT DISTINCT P.PLAN_ID
, P2.COMPILE_DESIGNATOR
, P2.PLAN_TYPE
, P2.PLANNED_INSTANCE_ID
, P2.PLANNED_ORGANIZATION
, P.SR_INSTANCE_ID
, P.ORGANIZATION_ID
, NULL
, P.PLANNING_GROUP
, P.PROJECT_ID
, P.PROJECT_NUMBER
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, NULL
FROM MSC_PROJECTS P
, MSC_PLANS_TREE_V P2
WHERE P2.PLAN_ID = P.PLAN_ID
AND P2.PLAN_COMPLETION_DATE IS NOT NULL
AND P2.DATA_COMPLETION_DATE IS NOT NULL UNION ALL SELECT DISTINCT P.PLAN_ID
, P2.COMPILE_DESIGNATOR
, P2.PLAN_TYPE
, P2.PLANNED_INSTANCE_ID
, P2.PLANNED_ORGANIZATION
, P.SR_INSTANCE_ID
, P.ORGANIZATION_ID
, NULL
, P.PLANNING_GROUP
, P.PROJECT_ID
, P.PROJECT_NUMBER
, T.TASK_ID
, T.TASK_NUMBER
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, NULL
FROM MSC_PROJECT_TASKS T
, MSC_PROJECTS P
, MSC_PLANS_TREE_V P2
WHERE P.PLAN_ID = T.PLAN_ID
AND P.SR_INSTANCE_ID = T.SR_INSTANCE_ID
AND P.ORGANIZATION_ID = T.ORGANIZATION_ID
AND P.PROJECT_ID = T.PROJECT_ID
AND P.PLAN_ID = P2.PLAN_ID
AND P2.PLAN_COMPLETION_DATE IS NOT NULL
AND P2.DATA_COMPLETION_DATE IS NOT NULL UNION ALL SELECT DISTINCT P.PLAN_ID
, P2.COMPILE_DESIGNATOR
, P2.PLAN_TYPE
, P2.PLANNED_INSTANCE_ID
, P2.PLANNED_ORGANIZATION
, P.SR_INSTANCE_ID
, P.ORGANIZATION_ID
, NULL
, P.PLANNING_GROUP
, P.PROJECT_ID
, NULL
, P.TASK_ID
, NULL
, P.INVENTORY_ITEM_ID
, P.ITEM_NUMBER
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, NULL
FROM MSC_PROJECT_TASK_ITEMS_TREE_V P
, MSC_PLANS_TREE_V P2
WHERE P2.PLAN_ID = P.PLAN_ID
AND P2.PLAN_COMPLETION_DATE IS NOT NULL
AND P2.DATA_COMPLETION_DATE IS NOT NULL