VIEW: APPS.MSC_SEARCH_ITEMS_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 pf.plan_id, p.compile_designator, p.plan_type, p.planned_instance_id, p.planned_organization, to_number(null), to_number(null), null, 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 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, to_number(null), to_number(null), null, 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, to_number(null), to_number(null), null, 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, c.category_set_id, c.sr_category_id, c.category_name, 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_item_categories c, 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 c.inventory_item_id = d.inventory_item_id and c.organization_id = d.organization_id and c.sr_instance_id = d.sr_instance_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, to_number(null), to_number(null), null, 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, to_number(null), to_number(null), null, 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

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 PF.PLAN_ID
, P.COMPILE_DESIGNATOR
, P.PLAN_TYPE
, P.PLANNED_INSTANCE_ID
, P.PLANNED_ORGANIZATION
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, 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 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
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, 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
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, 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
, C.CATEGORY_SET_ID
, C.SR_CATEGORY_ID
, C.CATEGORY_NAME
, 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_ITEM_CATEGORIES C
, 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 C.INVENTORY_ITEM_ID = D.INVENTORY_ITEM_ID
AND C.ORGANIZATION_ID = D.ORGANIZATION_ID
AND C.SR_INSTANCE_ID = D.SR_INSTANCE_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
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, 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
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, 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