VIEW: APPS.MSC_SEARCH_RESOURCES_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, 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

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