1. Tables and Views for SCM
  2. MSC_ATP_SOURCING_ASSIGN_V

MSC_ATP_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

FULL_LEAD_TIME

Query

SQL_Statement

SELECT asg.assignment_set_id,

item.inventory_item_id,

item.organization_id,

asg.sourcing_rule_id,

item.full_lead_time

FROM msc_op_regions mrt,

msc_sr_assignments asg,

msc_atp_rule_v item

WHERE mrt.party_id = -100

AND mrt.party_site_id = -100

AND asg.assignment_set_id = mrt.region_id

AND asg.assignment_type = 6

AND asg.organization_id = item.organization_id

AND asg.inventory_item_id = item.inventory_item_id

AND (nvl(item.supply_chain_search_code, 2) = 1 OR nvl(item.search_components_code, 2) = 1)

UNION ALL

SELECT asg.assignment_set_id,

item.inventory_item_id,

item.organization_id,

asg.sourcing_rule_id,

item.full_lead_time

FROM msc_op_regions mrt,

msc_sr_assignments asg,

msc_atp_rule_v item,

msc_sr_receipt_org org

WHERE mrt.party_id = -100

AND mrt.party_site_id = -100

AND asg.assignment_set_id = mrt.region_id

AND asg.assignment_type = 12

AND asg.sourcing_rule_id = org.sourcing_rule_id

AND org.sr_receipt_org = item.organization_id

AND asg.sourcing_rule_type = 2

AND asg.inventory_item_id = item.inventory_item_id

AND (nvl(item.supply_chain_search_code, 2) = 1 OR nvl(item.search_components_code, 2) = 1)

and not EXISTS

(

SELECT 1

FROM msc_sr_assignments asg2

WHERE asg2.assignment_type = 6

AND asg2.organization_id = item.organization_id

AND asg2.inventory_item_id = item.inventory_item_id

)

UNION ALL

SELECT asg.assignment_set_id,

item.inventory_item_id,

item.organization_id,

asg.sourcing_rule_id,

item.full_lead_time

FROM msc_op_regions mrt,

msc_sr_assignments asg,

msc_item_categories cat,

msc_catalog_categories mcc,

msc_atp_rule_v item

WHERE mrt.party_id = -100

AND mrt.party_site_id = -100

AND asg.assignment_set_id = mrt.region_id

AND asg.assignment_type = 5

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 decode(item.bom_item_type, 4 , nvl(item.base_item_id, item.inventory_item_id), item.inventory_item_id) = item.inventory_item_id

AND cat.organization_id = item.organization_id

AND cat.organization_id = asg.organization_id

AND (nvl(item.supply_chain_search_code, 2) = 1 OR nvl(item.search_components_code, 2) = 1)

AND NOT EXISTS

(SELECT 1

FROM msc_sr_assignments asg2, msc_sr_receipt_org org

WHERE ((asg2.assignment_type = 6

AND asg2.sourcing_rule_type = 1

AND asg2.organization_id = item.organization_id)

OR (asg2.assignment_type = 12

AND asg2.sourcing_rule_type = 2

AND org.sr_receipt_org = item.organization_id))

AND asg2.sourcing_rule_id = org.sourcing_rule_id

AND asg2.assignment_set_id = asg.assignment_set_id

AND asg2.inventory_item_id = item.inventory_item_id

)

UNION ALL

SELECT asg.assignment_set_id,

item.inventory_item_id,

item.organization_id,

asg.sourcing_rule_id,

item.full_lead_time

FROM msc_op_regions mrt,

msc_sr_assignments asg,

msc_item_categories cat,

msc_catalog_categories mcc,

msc_atp_rule_v item

WHERE mrt.party_id = -100

AND mrt.party_site_id = -100

AND asg.assignment_set_id = mrt.region_id

AND asg.assignment_type = 11

AND asg.sourcing_rule_type = 2

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 decode(item.bom_item_type, 4 , nvl(item.base_item_id, item.inventory_item_id), item.inventory_item_id) = item.inventory_item_id

AND cat.organization_id = item.organization_id

AND (nvl(item.supply_chain_search_code, 2) = 1 OR nvl(item.search_components_code, 2) = 1)

and not exists

(SELECT 1

FROM msc_sr_assignments asg2,

msc_item_categories cat2,

msc_catalog_categories mcc2

WHERE asg2.assignment_type = 5

AND asg2.assignment_set_id = asg.assignment_set_id

AND asg2.category_id = cat2.category_id

AND cat2.category_id = mcc2.category_id

AND cat2.organization_id = asg2.organization_id

AND asg2.category_set_id = mcc2.category_set_id

AND cat2.inventory_item_id = item.inventory_item_id

AND cat2.organization_id = item.organization_id)

and not EXISTS

(SELECT 1

FROM msc_sr_assignments asg2, msc_sr_receipt_org org

WHERE ((asg2.assignment_type = 6

AND asg2.sourcing_rule_type = 1

AND asg2.organization_id = item.organization_id)

OR (asg2.assignment_type = 12

AND asg2.sourcing_rule_type = 2

AND org.sr_receipt_org = item.organization_id))

AND asg2.sourcing_rule_id = org.sourcing_rule_id

AND asg2.assignment_set_id = asg.assignment_set_id

AND asg2.inventory_item_id = item.inventory_item_id

)

UNION ALL

SELECT asg.assignment_set_id,

item.inventory_item_id,

item.organization_id,

asg.sourcing_rule_id,

item.full_lead_time

FROM msc_op_regions mrt,

msc_sr_assignments asg,

msc_atp_rule_v item

WHERE mrt.party_id = -100

AND mrt.party_site_id = -100

AND asg.assignment_set_id = mrt.region_id

AND asg.assignment_type = 4

AND asg.organization_id = item.organization_id

AND decode(item.bom_item_type, 4 , nvl(item.base_item_id, item.inventory_item_id), item.inventory_item_id) = item.inventory_item_id

AND (nvl(item.supply_chain_search_code, 2) = 1 OR nvl(item.search_components_code, 2) = 1)

AND NOT EXISTS

(SELECT 1

FROM msc_sr_assignments asg2, msc_sr_receipt_org org

WHERE ((asg2.assignment_type = 6

AND asg2.sourcing_rule_type = 1

AND asg2.organization_id = item.organization_id)

OR (asg2.assignment_type = 12

AND asg2.sourcing_rule_type = 2

AND org.sr_receipt_org = item.organization_id))

AND asg2.sourcing_rule_id = org.sourcing_rule_id

AND asg2.assignment_set_id = asg.assignment_set_id

AND asg2.inventory_item_id = item.inventory_item_id

)

AND NOT EXISTS

(SELECT 1

FROM msc_sr_assignments asg2,

msc_item_categories cat2,

msc_catalog_categories mcc2

WHERE ((asg2.assignment_type = 5

AND asg2.sourcing_rule_type = 1

AND cat2.organization_id = asg2.organization_id)

OR (asg2.assignment_type = 11

AND asg2.sourcing_rule_type = 2))

AND asg2.assignment_set_id = asg.assignment_set_id

AND asg2.category_id = cat2.category_id

AND cat2.category_id = mcc2.category_id

AND asg2.category_set_id = mcc2.category_set_id

AND cat2.inventory_item_id = item.inventory_item_id

AND cat2.organization_id = item.organization_id)UNION ALL

SELECT asg.assignment_set_id,

item.inventory_item_id,

item.organization_id,

asg.sourcing_rule_id,

item.full_lead_time

FROM msc_op_regions mrt,

msc_sr_assignments asg,

msc_atp_rule_v item,

msc_sr_receipt_org org

WHERE mrt.party_id = -100

AND mrt.party_site_id = -100

AND asg.assignment_set_id = mrt.region_id

AND asg.assignment_type = 10

AND asg.sourcing_rule_type = 2

AND asg.sourcing_rule_id = org.sourcing_rule_id

AND org.sr_receipt_org = item.organization_id

AND decode(item.bom_item_type, 4 , nvl(item.base_item_id, item.inventory_item_id), item.inventory_item_id) = item.inventory_item_id

AND (nvl(item.supply_chain_search_code, 2) = 1 OR nvl(item.search_components_code, 2) = 1)

AND not exists

(SELECT 1

FROM msc_sr_assignments asg2

WHERE asg2.assignment_type = 4

AND asg2.assignment_set_id = asg.assignment_set_id

AND asg2.organization_id = item.organization_id

)

and not EXISTS

(

SELECT 1

FROM msc_sr_assignments asg3

WHERE ((asg3.assignment_type = 6

AND asg3.sourcing_rule_type = 1

AND asg3.organization_id = item.organization_id)

OR

(asg3.assignment_type = 12

AND asg3.sourcing_rule_type = 2

AND asg3.sourcing_rule_id = org.sourcing_rule_id

AND org.sr_receipt_org = item.organization_id))

AND asg3.inventory_item_id = item.inventory_item_id

)

and not exists

(SELECT 1

FROM msc_sr_assignments asg4,

msc_item_categories cat2,

msc_catalog_categories mcc2

WHERE ((asg4.assignment_type = 5

AND asg4.sourcing_rule_type = 1

AND cat2.organization_id = asg4.organization_id)

OR (asg4.assignment_type = 11

AND asg4.sourcing_rule_type = 2))

AND asg4.assignment_set_id = asg.assignment_set_id

AND asg4.category_id = cat2.category_id

AND cat2.category_id = mcc2.category_id

AND asg4.category_set_id = mcc2.category_set_id

AND cat2.inventory_item_id = item.inventory_item_id

AND cat2.organization_id = item.organization_id)