VIEW: APPS.MSC_ITEM_HIERARCHY_MV#

Source

View Text - Preformatted

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

View Text - HTML Formatted

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