1. Tables and Views for SCM
  2. MSC_ATP_ALLOC_V

MSC_ATP_ALLOC_V

Details

  • Schema: FUSION

  • Object owner: MSC

  • Object type: VIEW

Columns

Name

INVENTORY_ITEM_ID

ORGANIZATION_ID

ALLOCATION_RULE_ID

Query

SQL_Statement

SELECT item.inventory_item_id inventory_item_id,

item.organization_id organization_id,

maa.allocation_rule_id allocation_rule_id

FROM MSC_ATP_RULE_V item,

msc_bm_allocation_assignments maa

WHERE maa.assignment_type = 5

AND maa.inventory_item_id = item.inventory_item_id

AND maa.organization_id = item.organization_id

AND nvl(item.allocated_atp_code,2) = 1

UNION ALL

SELECT item.inventory_item_id inventory_item_id,

item.organization_id organization_id,

maa.allocation_rule_id allocation_rule_id

FROM MSC_ATP_RULE_V item,

msc_bm_allocation_assignments maa

WHERE maa.assignment_type = 3

AND maa.inventory_item_id = item.inventory_item_id

AND nvl(item.allocated_atp_code,2) = 1

AND NOT EXISTS

(SELECT maa7.inventory_item_id

FROM msc_bm_allocation_assignments maa7

WHERE maa7.inventory_item_id = item.inventory_item_id

AND maa7.organization_id = item.organization_id

AND maa7.assignment_type = 5

AND nvl(item.allocated_atp_code,2) = 1

)

UNION ALL

SELECT item.inventory_item_id inventory_item_id,

item.organization_id organization_id,

maa.allocation_rule_id allocation_rule_id

FROM msc_bm_allocation_assignments maa,

MSC_ATP_RULE_V item,

msc_item_categories mic,

msc_catalog_categories mcc

WHERE maa.assignment_type = 6

AND maa.category_id = mic.category_id

AND maa.category_set_id = mcc.category_set_id

AND maa.organization_id = mic.organization_id

AND mic.category_id = mcc.category_id

AND item.organization_id = mic.organization_id

AND item.inventory_item_id = mic.inventory_item_id

AND nvl(item.allocated_atp_code,2) = 1

AND NOT EXISTS

(SELECT maa1.inventory_item_id

FROM msc_bm_allocation_assignments maa1

WHERE maa1.inventory_item_id = item.inventory_item_id

AND maa1.assignment_type = 3

AND nvl(item.allocated_atp_code,2) = 1)

AND NOT EXISTS

(SELECT maa7.inventory_item_id

FROM msc_bm_allocation_assignments maa7

WHERE maa7.inventory_item_id = item.inventory_item_id

AND maa7.organization_id = item.organization_id

AND maa7.assignment_type = 5

AND nvl(item.allocated_atp_code,2) = 1)

UNION ALL

SELECT item.inventory_item_id inventory_item_id,

item.organization_id organization_id,

maa.allocation_rule_id allocation_rule_id

FROM msc_bm_allocation_assignments maa,

MSC_ATP_RULE_V item,

msc_item_categories mic,

msc_catalog_categories mcc

WHERE maa.assignment_type = 2

AND maa.category_id = mic.category_id

AND mic.category_id = mcc.category_id

AND maa.category_set_id = mcc.category_set_id

AND mic.inventory_item_id = item.inventory_item_id

AND mic.organization_id = item.organization_id

AND nvl(item.allocated_atp_code,2) = 1

AND NOT EXISTS

(SELECT maa1.inventory_item_id

FROM msc_bm_allocation_assignments maa1

WHERE maa1.inventory_item_id = item.inventory_item_id

AND maa1.assignment_type = 3

AND nvl(item.allocated_atp_code,2) = 1)

AND NOT EXISTS

(SELECT maa7.inventory_item_id

FROM msc_bm_allocation_assignments maa7

WHERE maa7.inventory_item_id = item.inventory_item_id

AND maa7.organization_id = item.organization_id

AND maa7.assignment_type = 5

AND nvl(item.allocated_atp_code,2) = 1)

AND NOT EXISTS

(

SELECT maa6.inventory_item_id

FROM msc_bm_allocation_assignments maa6,

msc_item_categories mic,

msc_catalog_categories mcc

WHERE maa6.assignment_type = 6

AND maa6.category_id = mic.category_id

AND maa6.category_set_id = mcc.category_set_id

AND maa6.organization_id = mic.organization_id

AND mic.category_id = mcc.category_id

AND item.organization_id = mic.organization_id

AND item.inventory_item_id = mic.inventory_item_id

AND nvl(item.allocated_atp_code,2) = 1

)

UNION ALL

SELECT item.inventory_item_id inventory_item_id,

item.organization_id organization_id,

maa.allocation_rule_id allocation_rule_id

FROM msc_bm_allocation_assignments maa,

MSC_ATP_RULE_V item

WHERE maa.assignment_type = 4

AND item.organization_id = maa.organization_id

AND nvl(item.allocated_atp_code,2) = 1

AND NOT EXISTS

(SELECT maa1.inventory_item_id

FROM msc_bm_allocation_assignments maa1,

msc_item_categories mic,

msc_catalog_categories mcc

WHERE maa1.category_id = mic.category_id

AND mic.category_id = mcc.category_id

AND maa1.category_set_id = mcc.category_set_id

AND item.inventory_item_id = mic.inventory_item_id

AND item.organization_id = mic.organization_id

AND maa1.assignment_type = 2

AND nvl(item.allocated_atp_code,2) = 1)

AND NOT EXISTS

(SELECT maa2.inventory_item_id

FROM msc_bm_allocation_assignments maa2

WHERE maa2.inventory_item_id = item.inventory_item_id

AND maa2.assignment_type = 3

AND nvl(item.allocated_atp_code,2) = 1)

AND NOT EXISTS

(SELECT maa7.inventory_item_id

FROM msc_bm_allocation_assignments maa7

WHERE maa7.inventory_item_id = item.inventory_item_id

AND maa7.organization_id = item.organization_id

AND maa7.assignment_type = 5

AND nvl(item.allocated_atp_code,2) = 1)

AND NOT EXISTS

(

SELECT maa6.inventory_item_id

FROM msc_bm_allocation_assignments maa6,

msc_item_categories mic,

msc_catalog_categories mcc

WHERE maa6.assignment_type = 6

AND maa6.category_id = mic.category_id

AND maa6.category_set_id = mcc.category_set_id

AND maa6.organization_id = mic.organization_id

AND mic.category_id = mcc.category_id

AND item.organization_id = mic.organization_id

AND item.inventory_item_id = mic.inventory_item_id

AND nvl(item.allocated_atp_code,2) = 1)