1. Tables and Views for SCM
  2. MSC_SOURCING_ASSIGN_V

MSC_SOURCING_ASSIGN_V

Details

  • Schema: FUSION

  • Object owner: MSC

  • Object type: VIEW

Columns

Name

ASSIGNMENT_SET_ID

INVENTORY_ITEM_ID

ORGANIZATION_ID

SOURCING_RULE_ID

ASSIGNMENT_TYPE

DENSERANK

Query

SQL_Statement

select ASSIGNMENT_SET_ID,INVENTORY_ITEM_ID,ORGANIZATION_ID,SOURCING_RULE_ID,ASSIGNMENT_TYPE,DENSERANK from (

select assignment_set_id, inventory_item_id, organization_id, sourcing_rule_id, assignment_type,

DENSE_RANK() OVER (PARTITION BY inventory_item_id, organization_id ORDER BY rank) denserank

from (

SELECT asg.assignment_set_id,

item.inventory_item_id,

item.organization_id,

asg.sourcing_rule_id,

asg.assignment_type,

1 rank

FROM fusion.msc_bm_options mrt,

fusion.msc_sr_assignments asg,

fusion.msc_system_items item

WHERE mrt.configuration_id=-1

AND asg.assignment_set_id = mrt.default_assignment_set_id

AND asg.assignment_type = 6

AND asg.sourcing_rule_type=1

AND asg.organization_id = item.organization_id

AND asg.inventory_item_id = item.inventory_item_id

AND item.plan_id = -1

AND item.new_plan_id = -1

AND item.simulation_set_id = -1

UNION ALL

SELECT asg.assignment_set_id,

item.inventory_item_id,

item.organization_id,

asg.sourcing_rule_id,

asg.assignment_type,

2 rank

FROM fusion.msc_bm_options mrt,

fusion.msc_sr_assignments asg,

fusion.msc_sourcing_rules msr,

fusion.msc_sr_receipt_org rec,

fusion.msc_system_items item

WHERE mrt.configuration_id=-1

AND asg.assignment_set_id = mrt.default_assignment_set_id

AND asg.assignment_type = 12

AND asg.sourcing_rule_type=2

AND asg.sourcing_rule_id=msr.sourcing_rule_id

AND msr.sourcing_rule_id=rec.sourcing_rule_id

AND asg.inventory_item_id = item.inventory_item_id

AND rec.sr_receipt_org=item.organization_id

AND item.plan_id = -1

AND item.new_plan_id = -1

AND item.simulation_set_id = -1

UNION ALL

SELECT asg.assignment_set_id,

item.inventory_item_id,

item.organization_id,

asg.sourcing_rule_id,

asg.assignment_type,

3 rank

FROM fusion.msc_bm_options mrt,

fusion.msc_sr_assignments asg,

fusion.msc_item_categories cat,

fusion.msc_catalog_categories mcc,

fusion.msc_system_items item

WHERE mrt.configuration_id=-1

AND asg.assignment_set_id = mrt.default_assignment_set_id

AND asg.sourcing_rule_type=1

AND asg.assignment_type = 5

AND asg.category_id = cat.category_id

AND asg.organization_id = cat.organization_id

AND cat.category_id = mcc.category_id

AND asg.category_set_id = mcc.category_set_id

AND cat.inventory_item_id = item.inventory_item_id

AND cat.organization_id = item.organization_id

AND item.plan_id = -1

AND item.new_plan_id = -1

AND item.simulation_set_id = -1

UNION ALL

SELECT asg.assignment_set_id,

item.inventory_item_id,

item.organization_id,

asg.sourcing_rule_id,

asg.assignment_type,

4 rank

FROM fusion.msc_bm_options mrt,

fusion.msc_sr_assignments asg,

fusion.msc_item_categories cat,

fusion.msc_catalog_categories mcc,

fusion.msc_system_items item,

fusion.msc_sr_receipt_org rec,

fusion.msc_sourcing_rules msr

WHERE mrt.configuration_id=-1

AND asg.assignment_set_id = mrt.default_assignment_set_id

AND asg.sourcing_rule_type=2

AND asg.assignment_type = 11

AND asg.category_id = cat.category_id

AND cat.category_id = mcc.category_id

AND asg.category_set_id = mcc.category_set_id

AND cat.inventory_item_id = item.inventory_item_id

AND asg.sourcing_rule_id=msr.sourcing_rule_id

AND msr.sourcing_rule_id=rec.sourcing_rule_id

AND rec.sr_receipt_org = item.organization_id

AND item.plan_id = -1

AND item.new_plan_id = -1

AND item.simulation_set_id = -1

UNION ALL

SELECT asg.assignment_set_id,

item.inventory_item_id,

item.organization_id,

asg.sourcing_rule_id,

asg.assignment_type,

5 rank

FROM fusion.msc_bm_options mrt,

fusion.msc_sr_assignments asg,

fusion.msc_system_items item

WHERE mrt.configuration_id=-1

AND asg.assignment_set_id = mrt.default_assignment_set_id

AND asg.sourcing_rule_type=1

AND asg.assignment_type = 4

AND asg.organization_id = item.organization_id

AND item.plan_id = -1

AND item.new_plan_id = -1

AND item.simulation_set_id = -1

UNION ALL

SELECT asg.assignment_set_id,

item.inventory_item_id,

item.organization_id,

asg.sourcing_rule_id,

asg.assignment_type,

6 rank

FROM fusion.msc_bm_options mrt,

fusion.msc_sr_assignments asg,

fusion.msc_system_items item

WHERE mrt.configuration_id=-1

AND asg.assignment_set_id = mrt.default_assignment_set_id

AND asg.assignment_type = 10

AND item.plan_id = -1

AND item.new_plan_id = -1

AND item.simulation_set_id = -1

)) where denserank = 1