VIEW: APPS.MSC_RESOURCE_HIERARCHY_MV#

Source

View Text - Preformatted

SELECT /*AUTOREFRESH*/	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 	/*AUTOREFRESH*/	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  /*AUTOREFRESH*/        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 	/*AUTOREFRESH*/	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)



  
 
   
   

View Text - HTML Formatted

SELECT /*AUTOREFRESH*/ 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 /*AUTOREFRESH*/ 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 /*AUTOREFRESH*/ 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 /*AUTOREFRESH*/ 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)