MSC_BOR_GOP_ATP_RULE

Details

Schema: FUSION
Object owner: MSC
Object type: VIEW

Columns

Name
RULE_ID IS_INDIVISIBLE INVENTORY_ITEM_ID ORGANIZATION_ID SUPPLY_CHAIN_SEARCH_CODE SEARCH_COMPONENTS_CODE BASE_ITEM_ID PREPROCESSING_LEAD_TIME POSTPROCESSING_LEAD_TIME CUM_MANUFACTURING_LEAD_TIME CUMULATIVE_TOTAL_LEAD_TIME FULL_LEAD_TIME UOM_CODE FIXED_LEAD_TIME VARIABLE_LEAD_TIME ATP_FLAG BOM_ITEM_TYPE PLANNING_MAKE_BUY_CODE CRITICAL_COMPONENT_FLAG PICK_COMPONENTS_FLAG REPLENISH_TO_ORDER_FLAG

Name

RULE_ID

IS_INDIVISIBLE

INVENTORY_ITEM_ID

ORGANIZATION_ID

SUPPLY_CHAIN_SEARCH_CODE

SEARCH_COMPONENTS_CODE

BASE_ITEM_ID

PREPROCESSING_LEAD_TIME

POSTPROCESSING_LEAD_TIME

CUM_MANUFACTURING_LEAD_TIME

CUMULATIVE_TOTAL_LEAD_TIME

FULL_LEAD_TIME

UOM_CODE

FIXED_LEAD_TIME

VARIABLE_LEAD_TIME

ATP_FLAG

BOM_ITEM_TYPE

PLANNING_MAKE_BUY_CODE

CRITICAL_COMPONENT_FLAG

PICK_COMPONENTS_FLAG

REPLENISH_TO_ORDER_FLAG

Query

SQL_Statement
select rule.rule_id, rule.is_indivisible, items.inventory_item_id, items.organization_id, supply_chain_search_code, search_components_code, items.base_item_id, items.preprocessing_lead_time, items.postprocessing_lead_time, items.cum_manufacturing_lead_time, items.cumulative_total_lead_time, items.full_lead_time, items.uom_code, items.fixed_lead_time, items.variable_lead_time, items.atp_flag, items.bom_item_type, items.planning_make_buy_code, items.critical_component_flag, items.pick_components_flag, items.replenish_to_order_flag from msc_atp_assignments assign, msc_atp_rules rule, msc_system_items items, msc_instance_orgs mp where assign.assignment_type = 5 and rule.rule_id = assign.atp_rule_id and items.plan_id = -1 and items.inventory_item_id = assign.inventory_item_id and items.organization_id = assign.organization_id and mp.organization_id = items.organization_id and (mp.fulfillment_instance_flag = 1 or mp.drop_ship_val_org_flag = 1) and (( items.bom_item_type = 4 and items.shippable_item_flag = 1) or items.bom_item_type in (1,2) or items.atp_flag = 1) union all select rule.rule_id, rule.is_indivisible, items.inventory_item_id, items.organization_id, supply_chain_search_code, search_components_code, items.base_item_id, items.preprocessing_lead_time, items.postprocessing_lead_time, items.cum_manufacturing_lead_time, items.cumulative_total_lead_time, items.full_lead_time, items.uom_code, items.fixed_lead_time, items.variable_lead_time, items.atp_flag, items.bom_item_type, items.planning_make_buy_code, items.critical_component_flag, items.pick_components_flag, items.replenish_to_order_flag from msc_atp_rules rule, msc_atp_assignments assign, msc_system_items items, msc_instance_orgs mp where assign.assignment_type = 3 and rule.rule_id = assign.atp_rule_id and items.plan_id = -1 and items.inventory_item_id = assign.inventory_item_id and mp.organization_id = items.organization_id and (mp.fulfillment_instance_flag = 1 or mp.drop_ship_val_org_flag = 1) and (( items.bom_item_type = 4 and items.shippable_item_flag = 1) or items.bom_item_type in (1,2) or items.atp_flag = 1) and not exists (select 1 from msc_atp_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 rule.rule_id, rule.is_indivisible, mic.inventory_item_id, mic.organization_id, supply_chain_search_code, search_components_code, items.base_item_id, items.preprocessing_lead_time, items.postprocessing_lead_time, items.cum_manufacturing_lead_time, items.cumulative_total_lead_time, items.full_lead_time, items.uom_code, items.fixed_lead_time, items.variable_lead_time, items.atp_flag, items.bom_item_type, items.planning_make_buy_code, items.critical_component_flag, items.pick_components_flag, items.replenish_to_order_flag from msc_atp_assignments assign, msc_atp_rules rule, msc_item_categories mic, msc_catalog_categories mcc, msc_system_items items, msc_instance_orgs mp where assign.assignment_type = 2 and rule.rule_id = assign.atp_rule_id and mic.category_id = mcc.category_id and assign.category_id = mic.category_id and assign.category_set_id = mcc.category_set_id and items.plan_id = -1 and items.inventory_item_id = mic.inventory_item_id and items.organization_id = mic.organization_id and (( items.bom_item_type = 4 and items.shippable_item_flag = 1) or items.bom_item_type in (1,2) or items.atp_flag = 1) and mp.organization_id = items.organization_id and ( mp.fulfillment_instance_flag = 1 or mp.drop_ship_val_org_flag = 1) and not exists (select 1 from msc_atp_assignments assign1 where assign1.assignment_type = 3 and assign1.inventory_item_id = items.inventory_item_id) and not exists (select 1 from msc_atp_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 rule.rule_id, rule.is_indivisible, items.inventory_item_id, items.organization_id, supply_chain_search_code, search_components_code, items.base_item_id, items.preprocessing_lead_time, items.postprocessing_lead_time, items.cum_manufacturing_lead_time, items.cumulative_total_lead_time, items.full_lead_time, items.uom_code, items.fixed_lead_time, items.variable_lead_time, items.atp_flag, items.bom_item_type, items.planning_make_buy_code, items.critical_component_flag, items.pick_components_flag, items.replenish_to_order_flag from msc_atp_rules rule, msc_atp_assignments assign, msc_system_items items where assign.assignment_type = 4 and rule.rule_id = assign.atp_rule_id and items.plan_id = -1 and items.organization_id = assign.organization_id and (( items.bom_item_type = 4 and items.shippable_item_flag = 1) or items.bom_item_type in (1,2) or items.atp_flag = 1) and not exists (select 1 from msc_atp_assignments assign1, msc_item_categories mic1, msc_catalog_categories mcc1 where assign1.assignment_type = 2 and assign1.category_id = mic1.category_id and mic1.category_id = mcc1.category_id and assign1.category_set_id = mcc1.category_set_id and items.inventory_item_id = mic1.inventory_item_id and items.organization_id = mic1.organization_id ) and not exists (select 1 from msc_atp_assignments assign1 where assign1.assignment_type = 3 and assign1.inventory_item_id = items.inventory_item_id) and not exists (select 1 from msc_atp_assignments assign1 where assign1.assignment_type = 5 and assign1.inventory_item_id = items.inventory_item_id and assign1.organization_id = items.organization_id )

SQL_Statement

select

rule.rule_id,

rule.is_indivisible,

items.inventory_item_id,

items.organization_id,

supply_chain_search_code,

search_components_code,

items.base_item_id,

items.preprocessing_lead_time,

items.postprocessing_lead_time,

items.cum_manufacturing_lead_time,

items.cumulative_total_lead_time,

items.full_lead_time,

items.uom_code,

items.fixed_lead_time,

items.variable_lead_time,

items.atp_flag,

items.bom_item_type,

items.planning_make_buy_code,

items.critical_component_flag,

items.pick_components_flag,

items.replenish_to_order_flag

from msc_atp_assignments assign,

msc_atp_rules rule,

msc_system_items items,

msc_instance_orgs mp

where assign.assignment_type = 5

and rule.rule_id = assign.atp_rule_id

and items.plan_id = -1

and items.inventory_item_id = assign.inventory_item_id

and items.organization_id = assign.organization_id

and mp.organization_id = items.organization_id

and (mp.fulfillment_instance_flag = 1 or mp.drop_ship_val_org_flag = 1)

and (( items.bom_item_type = 4 and items.shippable_item_flag = 1)

or items.bom_item_type in (1,2)

or items.atp_flag = 1)

union all

select

rule.rule_id,

rule.is_indivisible,

items.inventory_item_id,

items.organization_id,

supply_chain_search_code,

search_components_code,

items.base_item_id,

items.preprocessing_lead_time,

items.postprocessing_lead_time,

items.cum_manufacturing_lead_time,

items.cumulative_total_lead_time,

items.full_lead_time,

items.uom_code,

items.fixed_lead_time,

items.variable_lead_time,

items.atp_flag,

items.bom_item_type,

items.planning_make_buy_code,

items.critical_component_flag,

items.pick_components_flag,

items.replenish_to_order_flag

from msc_atp_rules rule,

msc_atp_assignments assign,

msc_system_items items,

msc_instance_orgs mp

where assign.assignment_type = 3

and rule.rule_id = assign.atp_rule_id

and items.plan_id = -1

and items.inventory_item_id = assign.inventory_item_id

and mp.organization_id = items.organization_id

and (mp.fulfillment_instance_flag = 1 or mp.drop_ship_val_org_flag = 1)

and (( items.bom_item_type = 4 and items.shippable_item_flag = 1)

or items.bom_item_type in (1,2)

or items.atp_flag = 1)

and not exists

(select 1

from msc_atp_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

rule.rule_id,

rule.is_indivisible,

mic.inventory_item_id,

mic.organization_id,

supply_chain_search_code,

search_components_code,

items.base_item_id,

items.preprocessing_lead_time,

items.postprocessing_lead_time,

items.cum_manufacturing_lead_time,

items.cumulative_total_lead_time,

items.full_lead_time,

items.uom_code,

items.fixed_lead_time,

items.variable_lead_time,

items.atp_flag,

items.bom_item_type,

items.planning_make_buy_code,

items.critical_component_flag,

items.pick_components_flag,

items.replenish_to_order_flag

from msc_atp_assignments assign,

msc_atp_rules rule,

msc_item_categories mic,

msc_catalog_categories mcc,

msc_system_items items,

msc_instance_orgs mp

where assign.assignment_type = 2

and rule.rule_id = assign.atp_rule_id

and mic.category_id = mcc.category_id

and assign.category_id = mic.category_id

and assign.category_set_id = mcc.category_set_id

and items.plan_id = -1

and items.inventory_item_id = mic.inventory_item_id

and items.organization_id = mic.organization_id

and (( items.bom_item_type = 4 and items.shippable_item_flag = 1)

or items.bom_item_type in (1,2)

or items.atp_flag = 1)

and mp.organization_id = items.organization_id

and ( mp.fulfillment_instance_flag = 1 or mp.drop_ship_val_org_flag = 1)

and not exists

(select 1

from msc_atp_assignments assign1

where assign1.assignment_type = 3

and assign1.inventory_item_id = items.inventory_item_id)

and not exists

(select 1

from msc_atp_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

rule.rule_id,

rule.is_indivisible,

items.inventory_item_id,

items.organization_id,

supply_chain_search_code,

search_components_code,

items.base_item_id,

items.preprocessing_lead_time,

items.postprocessing_lead_time,

items.cum_manufacturing_lead_time,

items.cumulative_total_lead_time,

items.full_lead_time,

items.uom_code,

items.fixed_lead_time,

items.variable_lead_time,

items.atp_flag,

items.bom_item_type,

items.planning_make_buy_code,

items.critical_component_flag,

items.pick_components_flag,

items.replenish_to_order_flag

from msc_atp_rules rule,

msc_atp_assignments assign,

msc_system_items items

where assign.assignment_type = 4

and rule.rule_id = assign.atp_rule_id

and items.plan_id = -1

and items.organization_id = assign.organization_id

and (( items.bom_item_type = 4 and items.shippable_item_flag = 1)

or items.bom_item_type in (1,2)

or items.atp_flag = 1)

and not exists

(select 1

from msc_atp_assignments assign1,

msc_item_categories mic1,

msc_catalog_categories mcc1

where assign1.assignment_type = 2

and assign1.category_id = mic1.category_id

and mic1.category_id = mcc1.category_id

and assign1.category_set_id = mcc1.category_set_id

and items.inventory_item_id = mic1.inventory_item_id

and items.organization_id = mic1.organization_id

)

and not exists

(select 1

from msc_atp_assignments assign1

where assign1.assignment_type = 3

and assign1.inventory_item_id = items.inventory_item_id)

and not exists

(select 1

from msc_atp_assignments assign1

where assign1.assignment_type = 5

and assign1.inventory_item_id = items.inventory_item_id

and assign1.organization_id = items.organization_id

)