VIEW: APPS.MSC_SEARCH_SUPPLIERS_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 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, 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 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 and p.plan_completion_date IS NOT NULL and p.data_completion_date IS NOT NULL UNION ALL SELECT DISTINCT s.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, s.supplier_id, tp.partner_name, null, to_number(null), to_number(null), null, null from msc_plans_tree_v p, msc_item_suppliers s, msc_trading_partners tp where p.plan_id = s.plan_id and s.supplier_id = tp.partner_id and tp.partner_type = 1 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 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 and p.plan_completion_date IS NOT NULL and p.data_completion_date IS NOT NULL UNION ALL SELECT DISTINCT s.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, 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, s.supplier_id, tp.partner_name, null, to_number(null), to_number(null), null, null from msc_item_categories c, msc_plans_tree_v p, msc_item_suppliers s, msc_trading_partners tp where p.plan_id = s.plan_id and p.plan_completion_date IS NOT NULL and p.data_completion_date IS NOT NULL and s.inventory_item_id = s.inventory_item_id and s.organization_id = s.organization_id and s.sr_instance_id = s.sr_instance_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 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
, 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 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
AND P.PLAN_COMPLETION_DATE IS NOT NULL
AND P.DATA_COMPLETION_DATE IS NOT NULL UNION ALL SELECT DISTINCT S.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
, S.SUPPLIER_ID
, TP.PARTNER_NAME
, NULL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, NULL
FROM MSC_PLANS_TREE_V P
, MSC_ITEM_SUPPLIERS S
, MSC_TRADING_PARTNERS TP
WHERE P.PLAN_ID = S.PLAN_ID
AND S.SUPPLIER_ID = TP.PARTNER_ID
AND TP.PARTNER_TYPE = 1
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 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
AND P.PLAN_COMPLETION_DATE IS NOT NULL
AND P.DATA_COMPLETION_DATE IS NOT NULL UNION ALL SELECT DISTINCT S.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
, 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
, S.SUPPLIER_ID
, TP.PARTNER_NAME
, NULL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, NULL
FROM MSC_ITEM_CATEGORIES C
, MSC_PLANS_TREE_V P
, MSC_ITEM_SUPPLIERS S
, MSC_TRADING_PARTNERS TP
WHERE P.PLAN_ID = S.PLAN_ID
AND P.PLAN_COMPLETION_DATE IS NOT NULL
AND P.DATA_COMPLETION_DATE IS NOT NULL
AND S.INVENTORY_ITEM_ID = S.INVENTORY_ITEM_ID
AND S.ORGANIZATION_ID = S.ORGANIZATION_ID
AND S.SR_INSTANCE_ID = S.SR_INSTANCE_ID
AND S.SUPPLIER_ID = TP.PARTNER_ID
AND TP.PARTNER_TYPE = 1