MSC_ATP_RULE_V

Details

• Schema: FUSION

• Object owner: MSC

• Object type: VIEW

Columns

Name

RULE_ID INVENTORY_ITEM_ID ORGANIZATION_ID CATEGORY_ID SHRINKAGE_RATE SUPPLY_CHAIN_SEARCH_CODE SEARCH_COMPONENTS_CODE PROFITABLE_TO_PROMISE_CODE ALLOCATED_ATP_CODE LEAD_TIME_CODE LEAD_TIME_TYPE LEAD_TIME_DAYS_UD INFINITE_AVAILABLE_CODE ATP_TIME_FENCE ATP_TIME_FENCE_UD PAST_DUE_DEMAND_CUTOFF_FENCE PAST_DUE_SUPPLY_CUTOFF_FENCE INFINITE_SUPPLY_FENCE_CODE INFINITE_SUPPLY_TIME_FENCE_UD AGGREGATE_TIME_FENCE_CODE AGGREGATE_TIME_FENCE INCLUDE_SALES_ORDERS INCLUDE_USER_DEFINED_DEMAND INCLUDE_DEPENDENT_WIP_DEMAND INCLUDE_INTERNAL_ORDERS INCLUDE_ONHAND_AVAILABLE INCLUDE_PURCHASE_ORDERS INCLUDE_REQUISITION INCLUDE_INTERORG_TRANSFERS INCLUDE_INTERNAL_REQS INCLUDE_DISCRETE_WIP_RECEIPTS INCLUDE_REP_WIP_RECEIPTS INCLUDE_NONSTD_WIP_RECEIPTS INCLUDE_FLOW_SCHEDULE_RECEIPTS INCLUDE_PLANNED_SUPPLY INCLUDE_USER_DEFINED_SUPPLY PREPROCESSING_LEAD_TIME POSTPROCESSING_LEAD_TIME CUM_MANUFACTURING_LEAD_TIME CUMULATIVE_TOTAL_LEAD_TIME FULL_LEAD_TIME UOM_CODE ITEM_COST FIXED_LEAD_TIME VARIABLE_LEAD_TIME ATP_FLAG BACK_TO_BACK_FLAG BOM_ITEM_TYPE PLANNING_MAKE_BUY_CODE SHIP_MODEL_COMPLETE_FLAG PICK_COMPONENTS_FLAG REPLENISH_TO_ORDER_FLAG ROUNDING_CONTROL_TYPE BASE_ITEM_ID AUTO_CREATED_CONFIG_FLAG

Query

SQL_Statement

select rule.rule_id, items.inventory_item_id, items.organization_id, cat.category_id, items.shrinkage_rate, supply_chain_search_code, search_components_code, profitable_to_promise_code, allocated_atp_code, lead_time_code, lead_time_type, ceil(lead_time_days_ud) as lead_time_days_ud, infinite_available_code, atp_time_fence, ceil(atp_time_fence_ud) as atp_time_fence_ud, ceil(past_due_demand_cutoff_fence) as past_due_demand_cutoff_fence, ceil(past_due_supply_cutoff_fence) as past_due_supply_cutoff_fence, infinite_supply_fence_code, ceil(infinite_supply_time_fence_ud) as infinite_supply_time_fence_ud, aggregate_time_fence_code, aggregate_time_fence, include_sales_orders, include_user_defined_demand, include_dependent_wip_demand, include_internal_orders, include_onhand_available, include_purchase_orders, include_requisition, include_interorg_transfers, include_internal_reqs, include_discrete_wip_receipts, include_rep_wip_receipts, include_nonstd_wip_receipts, include_flow_schedule_receipts, include_planned_supply, include_user_defined_supply , 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, attr.item_cost as item_cost, items.fixed_lead_time, items.variable_lead_time, items.atp_flag, items.back_to_back_flag, items.bom_item_type, items.planning_make_buy_code, items.ship_model_complete_flag, items.pick_components_flag, items.replenish_to_order_flag, items.rounding_control_type, items.base_item_id, items.auto_created_config_flag from msc_atp_assignments assign, msc_atp_rules rule, msc_system_items items, msc_instance_orgs mp, msc_items_addtl_attrib attr, ( SELECT DISTINCT mic.inventory_item_id, mic.category_id FROM msc_item_categories mic WHERE EXISTS (SELECT 1 FROM msc_sr_assignments srAssign, msc_catalog_categories mcc WHERE srAssign.assignment_set_id = NVL(FND_PROFILE.VALUE('MSP_DEFAULT_ASSIGNMENT_SET'),-1) AND srAssign.assignment_type IN (19,20,21,11,14) AND mic.category_id = mcc.category_id AND srAssign.category_set_id = mcc.category_set_id AND srAssign.category_id = mic.category_id ) ) cat 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 assign.inventory_item_id = attr.inventory_item_id(+) and assign.organization_id = attr.organization_id(+) and assign.inventory_item_id = cat.inventory_item_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 in (1,2)) union all select rule.rule_id, items.inventory_item_id, items.organization_id, cat.category_id, items.shrinkage_rate, supply_chain_search_code, search_components_code, profitable_to_promise_code, allocated_atp_code, lead_time_code, lead_time_type, ceil(lead_time_days_ud) as lead_time_days_ud, infinite_available_code, atp_time_fence, ceil(atp_time_fence_ud) as atp_time_fence_ud, ceil(past_due_demand_cutoff_fence) as past_due_demand_cutoff_fence, ceil(past_due_supply_cutoff_fence) as past_due_supply_cutoff_fence, infinite_supply_fence_code, ceil(infinite_supply_time_fence_ud) as infinite_supply_time_fence_ud, aggregate_time_fence_code, aggregate_time_fence, include_sales_orders, include_user_defined_demand, include_dependent_wip_demand, include_internal_orders, include_onhand_available, include_purchase_orders, include_requisition, include_interorg_transfers, include_internal_reqs, include_discrete_wip_receipts, include_rep_wip_receipts, include_nonstd_wip_receipts, include_flow_schedule_receipts, include_planned_supply, include_user_defined_supply , 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, attr.item_cost as item_cost, items.fixed_lead_time, items.variable_lead_time, items.atp_flag, items.back_to_back_flag, items.bom_item_type, items.planning_make_buy_code, items.ship_model_complete_flag, items.pick_components_flag, items.replenish_to_order_flag, items.rounding_control_type, items.base_item_id, items.auto_created_config_flag from msc_atp_rules rule, msc_atp_assignments assign, msc_system_items items, msc_instance_orgs mp, msc_items_addtl_attrib attr, ( SELECT DISTINCT mic.inventory_item_id, mic.category_id FROM msc_item_categories mic WHERE EXISTS (SELECT 1 FROM msc_sr_assignments srAssign, msc_catalog_categories mcc WHERE srAssign.assignment_set_id = NVL(FND_PROFILE.VALUE('MSP_DEFAULT_ASSIGNMENT_SET'),-1) AND srAssign.assignment_type IN (19,20,21,11,14) AND mic.category_id = mcc.category_id AND srAssign.category_set_id = mcc.category_set_id AND srAssign.category_id = mic.category_id ) ) cat 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.inventory_item_id = attr.inventory_item_id (+) and items.organization_id = attr.organization_id (+) and items.inventory_item_id = cat.inventory_item_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 in (1,2)) 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, mic.inventory_item_id, mic.organization_id, mic.category_id, items.shrinkage_rate, supply_chain_search_code, search_components_code, profitable_to_promise_code, allocated_atp_code, lead_time_code, lead_time_type, ceil(lead_time_days_ud) as lead_time_days_ud, infinite_available_code, atp_time_fence, ceil(atp_time_fence_ud) as atp_time_fence_ud, ceil(past_due_demand_cutoff_fence) as past_due_demand_cutoff_fence, ceil(past_due_supply_cutoff_fence) as past_due_supply_cutoff_fence, infinite_supply_fence_code, ceil(infinite_supply_time_fence_ud) as infinite_supply_time_fence_ud, aggregate_time_fence_code, aggregate_time_fence, include_sales_orders, include_user_defined_demand, include_dependent_wip_demand, include_internal_orders, include_onhand_available, include_purchase_orders, include_requisition, include_interorg_transfers, include_internal_reqs, include_discrete_wip_receipts, include_rep_wip_receipts, include_nonstd_wip_receipts, include_flow_schedule_receipts, include_planned_supply, include_user_defined_supply , 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, attr.item_cost as item_cost, items.fixed_lead_time, items.variable_lead_time, items.atp_flag, items.back_to_back_flag, items.bom_item_type, items.planning_make_buy_code, items.ship_model_complete_flag, items.pick_components_flag, items.replenish_to_order_flag, items.rounding_control_type, items.base_item_id, items.auto_created_config_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, msc_items_addtl_attrib attr 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 in (1,2)) and mp.organization_id = items.organization_id and ( mp.fulfillment_instance_flag = 1 or mp.drop_ship_val_org_flag = 1) and items.inventory_item_id = attr.inventory_item_id (+) and items.organization_id = attr.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 ) union all select rule.rule_id, items.inventory_item_id, items.organization_id, cat.category_id, items.shrinkage_rate, supply_chain_search_code, search_components_code, profitable_to_promise_code, allocated_atp_code, lead_time_code, lead_time_type, ceil(lead_time_days_ud) as lead_time_days_ud, infinite_available_code, atp_time_fence, ceil(atp_time_fence_ud) as atp_time_fence_ud, ceil(past_due_demand_cutoff_fence) as past_due_demand_cutoff_fence, ceil(past_due_supply_cutoff_fence) as past_due_supply_cutoff_fence, infinite_supply_fence_code, ceil(infinite_supply_time_fence_ud) as infinite_supply_time_fence_ud, aggregate_time_fence_code, aggregate_time_fence, include_sales_orders, include_user_defined_demand, include_dependent_wip_demand, include_internal_orders, include_onhand_available, include_purchase_orders, include_requisition, include_interorg_transfers, include_internal_reqs, include_discrete_wip_receipts, include_rep_wip_receipts, include_nonstd_wip_receipts, include_flow_schedule_receipts, include_planned_supply, include_user_defined_supply , 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, attr.item_cost as item_cost, items.fixed_lead_time, items.variable_lead_time, items.atp_flag, items.back_to_back_flag, items.bom_item_type, items.planning_make_buy_code, items.ship_model_complete_flag, items.pick_components_flag, items.replenish_to_order_flag, items.rounding_control_type, items.base_item_id, items.auto_created_config_flag from msc_atp_rules rule, msc_atp_assignments assign, msc_system_items items, msc_items_addtl_attrib attr, ( SELECT DISTINCT mic.inventory_item_id, mic.category_id FROM msc_item_categories mic WHERE EXISTS (SELECT 1 FROM msc_sr_assignments srAssign, msc_catalog_categories mcc WHERE srAssign.assignment_set_id = NVL(FND_PROFILE.VALUE('MSP_DEFAULT_ASSIGNMENT_SET'),-1) AND srAssign.assignment_type IN (19,20,21,11,14) AND mic.category_id = mcc.category_id AND srAssign.category_set_id = mcc.category_set_id AND srAssign.category_id = mic.category_id ) ) cat 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.inventory_item_id = attr.inventory_item_id (+) and items.organization_id = attr.organization_id (+) and items.inventory_item_id = cat.inventory_item_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 in (1,2)) 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 )