MSC_SUPPLY_UPDATE_ASGN

Details

Schema: FUSION
Object owner: MSC
Object type: VIEW

Columns

Name
INVENTORY_ITEM_ID ASSIGNMENT_TYPE ORGANIZATION_ID

Name

INVENTORY_ITEM_ID

ASSIGNMENT_TYPE

ORGANIZATION_ID

Query

SQL_Statement
select items.inventory_item_id,assign.assignment_type, items.organization_id from MSC_SUPPLY_UPDATE_ASSIGNMENTS assign, msc_system_items items where assign.assignment_type = 5 and items.inventory_item_id = assign.inventory_item_id and items.organization_id = assign.organization_id union all select items.inventory_item_id,assign.assignment_type, items.organization_id from MSC_SUPPLY_UPDATE_ASSIGNMENTS assign, msc_system_items items, msc_instance_orgs mp where assign.assignment_type = 3 and items.inventory_item_id = assign.inventory_item_id and mp.organization_id = items.organization_id and nvl(mp.enable_atp_ws,2) <> 1 and not exists (select 1 from MSC_SUPPLY_UPDATE_ASSIGNMENTS assign1 where assign1.assignment_type = 5 and assign1.inventory_item_id = items.inventory_item_id and assign1.organization_id = items.organization_id) union all select mic.inventory_item_id,assign.assignment_type, mic.organization_id from MSC_SUPPLY_UPDATE_ASSIGNMENTS assign, msc_item_categories mic, msc_catalog_categories mcc, msc_system_items items, msc_instance_orgs mp where assign.assignment_type = 2 and assign.category_id = mic.category_id and mic.category_id = mcc.category_id and assign.category_set_id = mcc.category_set_id and items.inventory_item_id = mic.inventory_item_id and items.organization_id = mic.organization_id and mp.organization_id = items.organization_id and nvl(mp.enable_atp_ws,2) <> 1 and not exists (select 1 from MSC_SUPPLY_UPDATE_ASSIGNMENTS assign1 where assign1.assignment_type = 3 and assign1.inventory_item_id = items.inventory_item_id) and not exists (select 1 from MSC_SUPPLY_UPDATE_ASSIGNMENTS assign1 where assign1.assignment_type = 5 and assign1.inventory_item_id = items.inventory_item_id and assign1.organization_id = items.organization_id ) union all select items.inventory_item_id,assign.assignment_type, items.organization_id from MSC_SUPPLY_UPDATE_ASSIGNMENTS assign, msc_system_items items where assign.assignment_type = 4 and items.organization_id = assign.organization_id and not exists (select 1 from MSC_SUPPLY_UPDATE_ASSIGNMENTS assign1, msc_catalog_categories mcc, msc_item_categories mic where assign1.assignment_type = 2 and assign1.category_id = mic.category_id and mic.category_id = mcc.category_id and assign1.category_set_id = mcc.category_set_id and items.inventory_item_id = mic.inventory_item_id and items.organization_id = mic.organization_id ) and not exists (select 1 from MSC_SUPPLY_UPDATE_ASSIGNMENTS assign1 where assign1.assignment_type = 3 and assign1.inventory_item_id = items.inventory_item_id) and not exists (select 1 from MSC_SUPPLY_UPDATE_ASSIGNMENTS assign1 where assign1.assignment_type = 5 and assign1.inventory_item_id = items.inventory_item_id and assign1.organization_id = items.organization_id ) union all select items.inventory_item_id,assign.assignment_type, items.organization_id from MSC_SUPPLY_UPDATE_ASSIGNMENTS assign, msc_system_items items, msc_catalog_categories mcc, msc_item_categories mic where assign.assignment_type = 1 and items.organization_id = mic.organization_id and items.inventory_item_id = mic.inventory_item_id and assign.category_id = mic.category_id and mic.category_id = mcc.category_id and assign.category_set_id = mcc.category_set_id and assign.organization_id = mic.organization_id and not exists (select 1 from MSC_SUPPLY_UPDATE_ASSIGNMENTS assign1, msc_item_categories mic where assign1.assignment_type = 2 and assign1.category_id = mic.category_id and mic.category_id = mcc.category_id and assign1.category_set_id = mcc.category_set_id and items.inventory_item_id = mic.inventory_item_id and items.organization_id = mic.organization_id ) and not exists (select 1 from MSC_SUPPLY_UPDATE_ASSIGNMENTS assign1 where assign1.assignment_type = 3 and assign1.inventory_item_id = items.inventory_item_id) and not exists (select 1 from MSC_SUPPLY_UPDATE_ASSIGNMENTS assign1 where assign1.assignment_type = 5 and assign1.inventory_item_id = items.inventory_item_id and assign1.organization_id = items.organization_id ) and not exists (select 1 from MSC_SUPPLY_UPDATE_ASSIGNMENTS assign1 where assign1.assignment_type = 4 and assign1.inventory_item_id = items.inventory_item_id and assign1.organization_id = items.organization_id )

SQL_Statement

select

items.inventory_item_id,assign.assignment_type,

items.organization_id

from MSC_SUPPLY_UPDATE_ASSIGNMENTS assign,

msc_system_items items

where assign.assignment_type = 5

and items.inventory_item_id = assign.inventory_item_id

and items.organization_id = assign.organization_id

union all

select

items.inventory_item_id,assign.assignment_type,

items.organization_id

from

MSC_SUPPLY_UPDATE_ASSIGNMENTS assign,

msc_system_items items,

msc_instance_orgs mp

where assign.assignment_type = 3

and items.inventory_item_id = assign.inventory_item_id

and mp.organization_id = items.organization_id

and nvl(mp.enable_atp_ws,2) <> 1

and not exists

(select 1

from MSC_SUPPLY_UPDATE_ASSIGNMENTS assign1

where assign1.assignment_type = 5

and assign1.inventory_item_id = items.inventory_item_id

and assign1.organization_id = items.organization_id)

union all

select

mic.inventory_item_id,assign.assignment_type,

mic.organization_id

from MSC_SUPPLY_UPDATE_ASSIGNMENTS assign,

msc_item_categories mic,

msc_catalog_categories mcc,

msc_system_items items,

msc_instance_orgs mp

where assign.assignment_type = 2

and assign.category_id = mic.category_id

and mic.category_id = mcc.category_id

and assign.category_set_id = mcc.category_set_id

and items.inventory_item_id = mic.inventory_item_id

and items.organization_id = mic.organization_id

and mp.organization_id = items.organization_id

and nvl(mp.enable_atp_ws,2) <> 1

and not exists

(select 1

from MSC_SUPPLY_UPDATE_ASSIGNMENTS assign1

where assign1.assignment_type = 3

and assign1.inventory_item_id = items.inventory_item_id)

and not exists

(select 1

from MSC_SUPPLY_UPDATE_ASSIGNMENTS assign1

where assign1.assignment_type = 5

and assign1.inventory_item_id = items.inventory_item_id

and assign1.organization_id = items.organization_id

)

union all

select

items.inventory_item_id,assign.assignment_type,

items.organization_id

from

MSC_SUPPLY_UPDATE_ASSIGNMENTS assign,

msc_system_items items

where assign.assignment_type = 4

and items.organization_id = assign.organization_id

and not exists

(select 1

from MSC_SUPPLY_UPDATE_ASSIGNMENTS assign1,

msc_catalog_categories mcc,

msc_item_categories mic

where assign1.assignment_type = 2

and assign1.category_id = mic.category_id

and mic.category_id = mcc.category_id

and assign1.category_set_id = mcc.category_set_id

and items.inventory_item_id = mic.inventory_item_id

and items.organization_id = mic.organization_id

)

and not exists

(select 1

from MSC_SUPPLY_UPDATE_ASSIGNMENTS assign1

where assign1.assignment_type = 3

and assign1.inventory_item_id = items.inventory_item_id)

and not exists

(select 1

from MSC_SUPPLY_UPDATE_ASSIGNMENTS assign1

where assign1.assignment_type = 5

and assign1.inventory_item_id = items.inventory_item_id

and assign1.organization_id = items.organization_id

)

union all

select

items.inventory_item_id,assign.assignment_type,

items.organization_id

from

MSC_SUPPLY_UPDATE_ASSIGNMENTS assign,

msc_system_items items,

msc_catalog_categories mcc,

msc_item_categories mic

where assign.assignment_type = 1

and items.organization_id = mic.organization_id

and items.inventory_item_id = mic.inventory_item_id

and assign.category_id = mic.category_id

and mic.category_id = mcc.category_id

and assign.category_set_id = mcc.category_set_id

and assign.organization_id = mic.organization_id

and not exists

(select 1

from MSC_SUPPLY_UPDATE_ASSIGNMENTS assign1,

msc_item_categories mic

where assign1.assignment_type = 2

and assign1.category_id = mic.category_id

and mic.category_id = mcc.category_id

and assign1.category_set_id = mcc.category_set_id

and items.inventory_item_id = mic.inventory_item_id

and items.organization_id = mic.organization_id

)

and not exists

(select 1

from MSC_SUPPLY_UPDATE_ASSIGNMENTS assign1

where assign1.assignment_type = 3

and assign1.inventory_item_id = items.inventory_item_id)

and not exists

(select 1

from MSC_SUPPLY_UPDATE_ASSIGNMENTS assign1

where assign1.assignment_type = 5

and assign1.inventory_item_id = items.inventory_item_id

and assign1.organization_id = items.organization_id

)

and not exists

(select 1

from MSC_SUPPLY_UPDATE_ASSIGNMENTS assign1

where assign1.assignment_type = 4

and assign1.inventory_item_id = items.inventory_item_id

and assign1.organization_id = items.organization_id

)