1. Tables and Views for SCM
  2. MSC_BOR_GOP_SOURCING_ASSIGN_V

MSC_BOR_GOP_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

SUPPLY_CHAIN_SEARCH_CODE

SEARCH_COMPONENTS_CODE

BOM_ITEM_TYPE

PICK_COMPONENTS_FLAG

REPLENISH_TO_ORDER_FLAG

PLANNING_MAKE_BUY_CODE

RULE_ID

POSTPROCESSING_LEAD_TIME

ATP_FLAG

CRITICAL_COMPONENT_FLAG

Query

SQL_Statement

SELECT asg.assignment_set_id,

item.inventory_item_id,

item.organization_id,

asg.sourcing_rule_id,

item.full_lead_time,

supply_chain_search_code,

search_components_code,

item.bom_item_type,

item.pick_components_flag,

item.replenish_to_order_flag,

item.planning_make_buy_code,

item.rule_id,

item.postprocessing_lead_time,

item.atp_flag,

item.critical_component_flag

FROM msc_sr_assignments asg,

MSC_BOR_GOP_ATP_RULE_V item

WHERE 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,

supply_chain_search_code,

search_components_code,

item.bom_item_type,

item.pick_components_flag,

item.replenish_to_order_flag,

item.planning_make_buy_code,

item.rule_id,

item.postprocessing_lead_time,

item.atp_flag,

item.critical_component_flag

FROM msc_sr_assignments asg,

MSC_BOR_GOP_ATP_RULE_V item,

msc_sr_receipt_org org

WHERE 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,

supply_chain_search_code,

search_components_code,

item.bom_item_type,

item.pick_components_flag,

item.replenish_to_order_flag,

item.planning_make_buy_code,

item.rule_id,

item.postprocessing_lead_time,

item.atp_flag,

item.critical_component_flag

FROM msc_sr_assignments asg,

msc_item_categories cat,

msc_catalog_categories mcc,

MSC_BOR_GOP_ATP_RULE_V item

WHERE 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,

supply_chain_search_code,

search_components_code,

item.bom_item_type,

item.pick_components_flag,

item.replenish_to_order_flag,

item.planning_make_buy_code,

item.rule_id,

item.postprocessing_lead_time,

item.atp_flag,

item.critical_component_flag

FROM msc_sr_assignments asg,

msc_item_categories cat,

msc_catalog_categories mcc,

MSC_BOR_GOP_ATP_RULE_V item

WHERE 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,

supply_chain_search_code,

search_components_code,

item.bom_item_type,

item.pick_components_flag,

item.replenish_to_order_flag,

item.planning_make_buy_code,

item.rule_id,

item.postprocessing_lead_time,

item.atp_flag,

item.critical_component_flag

FROM msc_sr_assignments asg,

MSC_BOR_GOP_ATP_RULE_V item

WHERE 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,

supply_chain_search_code,

search_components_code,

item.bom_item_type,

item.pick_components_flag,

item.replenish_to_order_flag,

item.planning_make_buy_code,

item.rule_id,

item.postprocessing_lead_time,

item.atp_flag,

item.critical_component_flag

FROM msc_sr_assignments asg,

MSC_BOR_GOP_ATP_RULE_V item,

msc_sr_receipt_org org

WHERE 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 asg.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)