VIEW: APPS.MSC_DRP_ALLOC_LEVELS_V

Source

View Text - Preformatted

SELECT items.inventory_item_id , items.sr_instance_id , items.organization_id , msra.allocation_rule_id ,msra.assignment_set_id , 1 from msc_sr_assignments msra , MSC_SYSTEM_ITEMS ITEMS WHERE ITEMS.PLAN_ID = -1 AND ITEMS.INVENTORY_ITEM_ID = MSRA.INVENTORY_ITEM_ID AND ITEMS.ORGANIZATION_ID = MSRA.ORGANIZATION_ID AND ITEMS.SR_INSTANCE_ID = MSRA.SR_INSTANCE_ID AND MSRA.ASSIGNMENT_TYPE = 6 AND MSRA.SOURCING_RULE_TYPE = 3 AND EXISTS (SELECT 1 FROM MSC_SR_ASSIGNMENTS WHERE ASSIGNMENT_TYPE = 6) union all select items.inventory_item_id , items.sr_instance_id , items.organization_id , msra.allocation_rule_id ,msra.assignment_set_id , 3 from msc_sr_assignments msra , MSC_SYSTEM_ITEMS ITEMS WHERE ITEMS.PLAN_ID = -1 AND ITEMS.ORGANIZATION_ID = MSRA.ORGANIZATION_ID AND ITEMS.SR_INSTANCE_ID = MSRA.SR_INSTANCE_ID AND MSRA.ASSIGNMENT_TYPE = 4 AND MSRA.SOURCING_RULE_TYPE = 3 AND EXISTS (SELECT 1 FROM MSC_SR_ASSIGNMENTS WHERE ASSIGNMENT_TYPE = 4) union all select cat.inventory_item_id , cat.sr_instance_id , cat.organization_id , msra.allocation_rule_id , msra.assignment_set_id , 2 from msc_sr_assignments msra , MSC_ITEM_CATEGORIES CAT WHERE CAT.CATEGORY_SET_ID = MSRA.CATEGORY_SET_ID AND CAT.CATEGORY_NAME = MSRA.CATEGORY_NAME AND CAT.ORGANIZATION_ID = MSRA.ORGANIZATION_ID AND CAT.SR_INSTANCE_ID = MSRA.SR_INSTANCE_ID AND MSRA.ASSIGNMENT_TYPE = 5 AND MSRA.SOURCING_RULE_TYPE = 3 AND EXISTS (SELECT 1 FROM MSC_SR_ASSIGNMENTS WHERE ASSIGNMENT_TYPE = 5)

View Text - HTML Formatted

SELECT ITEMS.INVENTORY_ITEM_ID
, ITEMS.SR_INSTANCE_ID
, ITEMS.ORGANIZATION_ID
, MSRA.ALLOCATION_RULE_ID
, MSRA.ASSIGNMENT_SET_ID
, 1
FROM MSC_SR_ASSIGNMENTS MSRA
, MSC_SYSTEM_ITEMS ITEMS
WHERE ITEMS.PLAN_ID = -1
AND ITEMS.INVENTORY_ITEM_ID = MSRA.INVENTORY_ITEM_ID
AND ITEMS.ORGANIZATION_ID = MSRA.ORGANIZATION_ID
AND ITEMS.SR_INSTANCE_ID = MSRA.SR_INSTANCE_ID
AND MSRA.ASSIGNMENT_TYPE = 6
AND MSRA.SOURCING_RULE_TYPE = 3
AND EXISTS (SELECT 1
FROM MSC_SR_ASSIGNMENTS
WHERE ASSIGNMENT_TYPE = 6) UNION ALL SELECT ITEMS.INVENTORY_ITEM_ID
, ITEMS.SR_INSTANCE_ID
, ITEMS.ORGANIZATION_ID
, MSRA.ALLOCATION_RULE_ID
, MSRA.ASSIGNMENT_SET_ID
, 3
FROM MSC_SR_ASSIGNMENTS MSRA
, MSC_SYSTEM_ITEMS ITEMS
WHERE ITEMS.PLAN_ID = -1
AND ITEMS.ORGANIZATION_ID = MSRA.ORGANIZATION_ID
AND ITEMS.SR_INSTANCE_ID = MSRA.SR_INSTANCE_ID
AND MSRA.ASSIGNMENT_TYPE = 4
AND MSRA.SOURCING_RULE_TYPE = 3
AND EXISTS (SELECT 1
FROM MSC_SR_ASSIGNMENTS
WHERE ASSIGNMENT_TYPE = 4) UNION ALL SELECT CAT.INVENTORY_ITEM_ID
, CAT.SR_INSTANCE_ID
, CAT.ORGANIZATION_ID
, MSRA.ALLOCATION_RULE_ID
, MSRA.ASSIGNMENT_SET_ID
, 2
FROM MSC_SR_ASSIGNMENTS MSRA
, MSC_ITEM_CATEGORIES CAT
WHERE CAT.CATEGORY_SET_ID = MSRA.CATEGORY_SET_ID
AND CAT.CATEGORY_NAME = MSRA.CATEGORY_NAME
AND CAT.ORGANIZATION_ID = MSRA.ORGANIZATION_ID
AND CAT.SR_INSTANCE_ID = MSRA.SR_INSTANCE_ID
AND MSRA.ASSIGNMENT_TYPE = 5
AND MSRA.SOURCING_RULE_TYPE = 3
AND EXISTS (SELECT 1
FROM MSC_SR_ASSIGNMENTS
WHERE ASSIGNMENT_TYPE = 5)