[Home] [Help]
MATERIALIZED VIEW: MSC.MSC_RESOURCE_HIERARCHY_MV
Source
SELECT maa.resource_id RESOURCE_ID,
mdr.department_id DEPARTMENT_ID,
mdr.organization_id ORGANIZATION_ID,
mdr.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_department_resources mdr
WHERE maa.assignment_type = 4 -- this is department resource level
AND maa.allocation_rule_name = martp.allocation_rule_name
AND martp.time_phase_id = ma.time_phase_id
AND maa.resource_id = mdr.resource_id
AND maa.department_id = mdr.department_id
AND maa.organization_id = mdr.organization_id
AND maa.sr_instance_id = mdr.sr_instance_id
AND maa.resource_group IS NULL
AND mdr.plan_id = -1
UNION ALL
SELECT mdr.resource_id RESOURCE_ID,
mdr.department_id DEPARTMENT_ID,
mdr.organization_id ORGANIZATION_ID,
mdr.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_department_resources mdr
WHERE maa.assignment_type = 5 -- this is resource group level
AND maa.sr_instance_id = mdr.sr_instance_id
AND maa.resource_group = mdr.resource_group_name
AND maa.allocation_rule_name = martp.allocation_rule_name
AND martp.time_phase_id = ma.time_phase_id
AND mdr.plan_id = -1
AND maa.resource_group IS NOT NULL
AND NOT EXISTS (
SELECT maa1.resource_id
FROM msc_allocation_assignments maa1
WHERE maa1.resource_id = mdr.resource_id
AND maa1.organization_id = mdr.organization_id
AND maa1.sr_instance_id = mdr.sr_instance_id
AND maa1.department_id = mdr.department_id
AND maa1.assignment_type = 4)
UNION ALL
SELECT mdr.resource_id RESOURCE_ID,
mdr.department_id DEPARTMENT_ID,
mdr.organization_id ORGANIZATION_ID,
mdr.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_department_resources mdr
WHERE maa.assignment_type = 6 -- this is organization level
AND maa.sr_instance_id = mdr.sr_instance_id
AND maa.organization_id = mdr.organization_id
AND maa.allocation_rule_name = martp.allocation_rule_name
AND martp.time_phase_id = ma.time_phase_id
AND mdr.plan_id = -1
AND maa.resource_group IS NULL
AND maa.resource_id IS NULL
AND NOT EXISTS (
SELECT maa1.resource_id
FROM msc_allocation_assignments maa1
WHERE maa1.resource_id = mdr.resource_id
AND maa1.organization_id = mdr.organization_id
AND maa1.sr_instance_id = mdr.sr_instance_id
AND maa1.department_id = mdr.department_id
AND maa1.assignment_type = 4)
AND NOT EXISTS (
SELECT maa2.resource_group
FROM msc_allocation_assignments maa2
WHERE maa2.resource_group = mdr.resource_group_name
AND maa2.sr_instance_id = mdr.sr_instance_id
AND maa2.assignment_type = 5)
UNION ALL
SELECT mdr.resource_id RESOURCE_ID,
mdr.department_id DEPARTMENT_ID,
mdr.organization_id ORGANIZATION_ID,
mdr.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_department_resources mdr,
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 mdr.plan_id = -1
AND maa.resource_id IS NULL
AND maa.resource_group IS NULL
AND NOT EXISTS (
SELECT maa1.resource_id
FROM msc_allocation_assignments maa1
WHERE maa1.resource_id = mdr.resource_id
AND maa1.department_id = mdr.department_id
AND maa1.organization_id = mdr.organization_id
AND maa1.sr_instance_id = mdr.sr_instance_id
AND maa1.assignment_type = 4)
AND NOT EXISTS (
SELECT maa1.resource_group
FROM msc_allocation_assignments maa1
WHERE maa1.resource_group = mdr.resource_group_name
AND maa1.sr_instance_id = mdr.sr_instance_id
AND maa1.assignment_type = 5)
AND NOT EXISTS (
SELECT maa3.resource_group
FROM msc_allocation_assignments maa3
WHERE maa3.organization_id = mdr.organization_id
AND maa3.sr_instance_id = mdr.sr_instance_id
AND maa3.assignment_type = 6)