[Home] [Help]
MATERIALIZED VIEW: MSC.MSC_ITEM_HIERARCHY_MV
Source
SELECT mi.inventory_item_id INVENTORY_ITEM_ID,
mi.organization_id ORGANIZATION_ID,
mi.sr_instance_id SR_INSTANCE_ID,
ma.demand_class DEMAND_CLASS,
maa.allocation_rule_name ALLOCATION_RULE_NAME,
maa.assignment_type ASSIGNMENT_TYPE,
ma.allocation_percent ALLOCATION_PERCENT,
martp.effective_date EFFECTIVE_DATE,
martp.disable_date DISABLE_DATE,
ma.priority PRIORITY,
ma.service_level SERVICE_LEVEL,
martp.time_phase_id TIME_PHASE_ID,
ma.class CLASS,
ma.partner_id PARTNER_ID,
ma.partner_site_id PARTNER_SITE_ID,
ma.level_id LEVEL_ID,
ma.level_alloc_percent LEVEL_ALLOC_PERCENT,
ma.level_priority LEVEL_PRIORITY,
ma.min_level_alloc_percent MIN_LEVEL_ALLOC_PERCENT,
ma.min_allocation_percent MIN_ALLOCATION_PERCENT
FROM msc_allocations ma,
msc_alloc_rule_time_phases martp,
msc_system_items mi,
msc_allocation_assignments maa
WHERE maa.assignment_type = 7 -- this is item/org level
AND maa.inventory_item_id = mi.inventory_item_id
AND maa.organization_id = mi.organization_id
AND maa.sr_instance_id = mi.sr_instance_id
AND maa.allocation_rule_name = martp.allocation_rule_name
AND martp.time_phase_id = ma.time_phase_id
AND mi.plan_id = -1
UNION ALL
SELECT mi.inventory_item_id INVENTORY_ITEM_ID,
mi.organization_id ORGANIZATION_ID,
mi.sr_instance_id SR_INSTANCE_ID,
ma.demand_class DEMAND_CLASS,
maa.allocation_rule_name ALLOCATION_RULE_NAME,
maa.assignment_type ASSIGNMENT_TYPE,
ma.allocation_percent ALLOCATION_PERCENT,
martp.effective_date EFFECTIVE_DATE,
martp.disable_date DISABLE_DATE,
ma.priority PRIORITY,
ma.service_level SERVICE_LEVEL,
martp.time_phase_id TIME_PHASE_ID,
ma.class CLASS,
ma.partner_id PARTNER_ID,
ma.partner_site_id PARTNER_SITE_ID,
ma.level_id LEVEL_ID,
ma.level_alloc_percent LEVEL_ALLOC_PERCENT,
ma.level_priority LEVEL_PRIORITY,
ma.min_level_alloc_percent MIN_LEVEL_ALLOC_PERCENT,
ma.min_allocation_percent MIN_ALLOCATION_PERCENT
FROM msc_allocations ma,
msc_alloc_rule_time_phases martp,
msc_system_items mi,
msc_allocation_assignments maa
WHERE maa.assignment_type = 3 -- this is item level
AND maa.inventory_item_id = mi.inventory_item_id
AND maa.allocation_rule_name = martp.allocation_rule_name
AND martp.time_phase_id = ma.time_phase_id
AND mi.plan_id = -1
AND NOT EXISTS (
SELECT maa7.inventory_item_id
FROM msc_allocation_assignments maa7
WHERE maa7.inventory_item_id = mi.inventory_item_id
AND maa7.organization_id = mi.organization_id
AND maa7.sr_instance_id = mi.sr_instance_id
AND maa7.assignment_type = 7 )
UNION ALL
SELECT mi.inventory_item_id INVENTORY_ITEM_ID,
mi.organization_id ORGANIZATION_ID,
mi.sr_instance_id SR_INSTANCE_ID,
ma.demand_class DEMAND_CLASS,
maa.allocation_rule_name ALLOCATION_RULE_NAME,
maa.assignment_type ASSIGNMENT_TYPE,
ma.allocation_percent ALLOCATION_PERCENT,
martp.effective_date EFFECTIVE_DATE,
martp.disable_date DISABLE_DATE,
ma.priority PRIORITY,
ma.service_level SERVICE_LEVEL,
martp.time_phase_id TIME_PHASE_ID,
ma.class CLASS,
ma.partner_id PARTNER_ID,
ma.partner_site_id PARTNER_SITE_ID,
ma.level_id LEVEL_ID,
ma.level_alloc_percent LEVEL_ALLOC_PERCENT,
ma.level_priority LEVEL_PRIORITY,
ma.min_level_alloc_percent MIN_LEVEL_ALLOC_PERCENT,
ma.min_allocation_percent MIN_ALLOCATION_PERCENT
FROM msc_allocations ma,
msc_alloc_rule_time_phases martp,
msc_allocation_assignments maa,
msc_system_items mi,
msc_item_categories mic
WHERE maa.assignment_type = 2 -- this is item cat level
AND maa.category_set_id = mic.category_set_id
AND maa.category_name = mic.category_name
AND maa.allocation_rule_name = martp.allocation_rule_name
AND martp.time_phase_id = ma.time_phase_id
AND mic.inventory_item_id = mi.inventory_item_id
AND mic.organization_id = mi.organization_id
AND mic.sr_instance_id = mi.sr_instance_id
AND mi.plan_id = -1
AND NOT EXISTS (
SELECT maa1.category_set_id
FROM msc_allocation_assignments maa1
WHERE maa1.inventory_item_id = mi.inventory_item_id
AND maa1.assignment_type = 3)
AND NOT EXISTS (
SELECT maa7.inventory_item_id
FROM msc_allocation_assignments maa7
WHERE maa7.inventory_item_id = mi.inventory_item_id
AND maa7.organization_id = mi.organization_id
AND maa7.sr_instance_id = mi.sr_instance_id
AND maa7.assignment_type = 7 )
UNION ALL
SELECT mi.inventory_item_id INVENTORY_ITEM_ID,
mi.organization_id ORGANIZATION_ID,
mi.sr_instance_id SR_INSTANCE_ID,
ma.demand_class DEMAND_CLASS,
maa.allocation_rule_name ALLOCATION_RULE_NAME,
maa.assignment_type ASSIGNMENT_TYPE,
ma.allocation_percent ALLOCATION_PERCENT,
martp.effective_date EFFECTIVE_DATE,
martp.disable_date DISABLE_DATE,
ma.priority PRIORITY,
ma.service_level SERVICE_LEVEL,
martp.time_phase_id TIME_PHASE_ID,
ma.class CLASS,
ma.partner_id PARTNER_ID,
ma.partner_site_id PARTNER_SITE_ID,
ma.level_id LEVEL_ID,
ma.level_alloc_percent LEVEL_ALLOC_PERCENT,
ma.level_priority LEVEL_PRIORITY,
ma.min_level_alloc_percent MIN_LEVEL_ALLOC_PERCENT,
ma.min_allocation_percent MIN_ALLOCATION_PERCENT
FROM msc_allocations ma,
msc_alloc_rule_time_phases martp,
msc_allocation_assignments maa,
msc_system_items mi
WHERE maa.assignment_type = 6 -- this is org level
AND maa.allocation_rule_name = martp.allocation_rule_name
AND martp.time_phase_id = ma.time_phase_id
AND mi.organization_id = maa.organization_id
AND mi.sr_instance_id = maa.sr_instance_id
AND mi.plan_id = -1
AND NOT EXISTS (
SELECT /*+ leading(maa1) */ maa1.inventory_item_id
FROM msc_allocation_assignments maa1,
msc_item_categories mic
WHERE maa1.category_set_id = mic.category_set_id
AND maa1.category_name = mic.category_name
AND mi.inventory_item_id = mic.inventory_item_id
AND mi.organization_id = mic.organization_id
AND mi.sr_instance_id = mic.sr_instance_id
AND maa1.assignment_type = 2)
AND NOT EXISTS (
SELECT maa2.inventory_item_id
FROM msc_allocation_assignments maa2
WHERE maa2.inventory_item_id = mi.inventory_item_id
AND maa2.assignment_type = 3)
AND NOT EXISTS (
SELECT maa7.inventory_item_id
FROM msc_allocation_assignments maa7
WHERE maa7.inventory_item_id = mi.inventory_item_id
AND maa7.organization_id = mi.organization_id
AND maa7.sr_instance_id = mi.sr_instance_id
AND maa7.assignment_type = 7 )
UNION ALL
SELECT mi.inventory_item_id INVENTORY_ITEM_ID,
mi.organization_id ORGANIZATION_ID,
mi.sr_instance_id SR_INSTANCE_ID,
ma.demand_class DEMAND_CLASS,
maa.allocation_rule_name ALLOCATION_RULE_NAME,
maa.assignment_type ASSIGNMENT_TYPE,
ma.allocation_percent ALLOCATION_PERCENT,
martp.effective_date EFFECTIVE_DATE,
martp.disable_date DISABLE_DATE,
ma.priority PRIORITY,
ma.service_level SERVICE_LEVEL,
martp.time_phase_id TIME_PHASE_ID,
ma.class CLASS,
ma.partner_id PARTNER_ID,
ma.partner_site_id PARTNER_SITE_ID,
ma.level_id LEVEL_ID,
ma.level_alloc_percent LEVEL_ALLOC_PERCENT,
ma.level_priority LEVEL_PRIORITY,
ma.min_level_alloc_percent MIN_LEVEL_ALLOC_PERCENT,
ma.min_allocation_percent MIN_ALLOCATION_PERCENT
FROM msc_allocations ma,
msc_system_items mi,
msc_alloc_rule_time_phases martp,
msc_allocation_assignments maa
WHERE maa.assignment_type = 1 -- this is global rule
AND maa.allocation_rule_name = martp.allocation_rule_name
AND martp.time_phase_id = ma.time_phase_id
AND mi.plan_id = -1
AND NOT EXISTS (
SELECT /*+ leading(maa1) */ maa1.inventory_item_id
FROM msc_allocation_assignments maa1,
msc_item_categories mic
WHERE maa1.category_set_id = mic.category_set_id
AND maa1.category_name = mic.category_name
AND mi.inventory_item_id = mic.inventory_item_id
AND mi.organization_id = mic.organization_id
AND mi.sr_instance_id = mic.sr_instance_id
AND maa1.assignment_type = 2)
AND NOT EXISTS (
SELECT maa2.inventory_item_id
FROM msc_allocation_assignments maa2
WHERE maa2.inventory_item_id = mi.inventory_item_id
AND maa2.assignment_type = 3)
AND NOT EXISTS (
SELECT maa3.inventory_item_id
FROM msc_allocation_assignments maa3
WHERE maa3.organization_id = mi.organization_id
AND maa3.sr_instance_id = mi.sr_instance_id
AND maa3.assignment_type = 6)
AND NOT EXISTS (
SELECT maa7.inventory_item_id
FROM msc_allocation_assignments maa7
WHERE maa7.inventory_item_id = mi.inventory_item_id
AND maa7.organization_id = mi.organization_id
AND maa7.sr_instance_id = mi.sr_instance_id
AND maa7.assignment_type = 7 )