Product: | MSC - Advanced Supply Chain Planning |
---|---|
Description: | |
Implementation/DBA Data: | APPS.MSC_PLAN_PARAMETERS_V |
SELECT DISTINCT M.ROWID
, M.PLAN_ID
, D.ORGANIZATION_ID
, D.SR_INSTANCE_ID
, D.DESIGNATOR
, NVL(M.LAST_UPDATE_DATE
, SYSDATE)
, NVL(M.LAST_UPDATED_BY
, D.LAST_UPDATED_BY)
, NVL(M.CREATION_DATE
, SYSDATE)
, NVL(M.CREATED_BY
, D.CREATED_BY)
, M.LAST_UPDATE_LOGIN
, DECODE(D.DESIGNATOR_TYPE
, 5
, NVL(M.CURR_OPERATION_SCHEDULE_TYPE
, 2)
, NVL(NVL(M.CURR_OPERATION_SCHEDULE_TYPE
, P.OPERATION_SCHEDULE_TYPE)
, 1))
, DECODE(D.DESIGNATOR_TYPE
, 2
, 2
, 3
, 1
, 5
, 4
, 4
, 3
, 1)
, DECODE(D.DESIGNATOR_TYPE
, -99
, ' '
, L1.MEANING)
, NVL(M.CURR_OVERWRITE_OPTION
, 1)
, NVL(M.CURR_APPEND_PLANNED_ORDERS
, DECODE(M.CURR_OVERWRITE_OPTION
, 3
, 2
, 1))
, NVL(M.CURR_CUTOFF_DATE
, SYSDATE+NVL(M.DAILY_CUTOFF_BUCKET
, 30) +NVL(M.WEEKLY_CUTOFF_BUCKET
, 4)*7 +NVL(M.PERIOD_CUTOFF_BUCKET
, 5)*30)
, DECODE(D.DESIGNATOR_TYPE
, 5
, NVL(M.CURR_PART_INCLUDE_TYPE
, MSC_GET_NAME.ABC_CLASS_ID(D.ORGANIZATION_ID
, D.SR_INSTANCE_ID))
, NVL(NVL(M.CURR_PART_INCLUDE_TYPE
, P.PART_INCLUDE_TYPE)
, 2))
, NVL(M.CURR_PLANNING_TIME_FENCE_FLAG
, P.PLANNING_TIME_FENCE_FLAG)
, NVL(M.CURR_DEMAND_TIME_FENCE_FLAG
, P.DEMAND_TIME_FENCE_FLAG)
, NVL(M.CURR_CONSIDER_RESERVATIONS
, P.CONSIDER_RESERVATIONS)
, NVL(M.CURR_PLAN_SAFETY_STOCK
, P.PLAN_SAFETY_STOCK)
, NVL(M.CURR_CONSIDER_WIP
, P.CONSIDER_WIP)
, NVL(M.CURR_CONSIDER_PO
, P.CONSIDER_PO)
, NVL(M.CURR_SNAPSHOT_LOCK
, P.SNAPSHOT_LOCK)
, D.DESCRIPTION
, D.DISABLE_DATE
, M.CURR_ASSIGNMENT_SET_ID
, MAS.ASSIGNMENT_SET_NAME
, MAS.DESCRIPTION
, NVL(D.ORGANIZATION_SELECTION
, 3)
, L3.MEANING
, M.OPERATION_SCHEDULE_TYPE
, M.PLAN_TYPE
, MSC_GET_NAME.LOOKUP_MEANING('MRP_PLAN_TYPE'
, M.PLAN_TYPE)
, M.OVERWRITE_OPTION
, M.APPEND_PLANNED_ORDERS
, M.CUTOFF_DATE
, M.PART_INCLUDE_TYPE
, M.PLANNING_TIME_FENCE_FLAG
, M.DEMAND_TIME_FENCE_FLAG
, M.CONSIDER_RESERVATIONS
, M.PLAN_SAFETY_STOCK
, M.CONSIDER_WIP
, M.CONSIDER_PO
, M.SNAPSHOT_LOCK
, M.ASSIGNMENT_SET_ID
, MAS1.ASSIGNMENT_SET_NAME
, MAS1.DESCRIPTION
, M.DATA_START_DATE
, M.DATA_COMPLETION_DATE
, M.PLAN_START_DATE
, M.PLAN_COMPLETION_DATE
, NVL(M.CURR_RESERVATION_LEVEL
, 4)
, NVL(M.CURR_HARD_PEGGING_LEVEL
, 3)
, NVL(M.RESERVATION_LEVEL
, 3)
, NVL(M.HARD_PEGGING_LEVEL
, 3)
, NVL(M.CURR_PLAN_CAPACITY_FLAG
, 1)
, M.CURR_SIMULATION_SET
, M.CURR_BILL_OF_RESOURCES
, M.PLAN_CAPACITY_FLAG
, M.SIMULATION_SET
, M.BILL_OF_RESOURCES
, NVL(M.CURR_ENFORCE_DEM_DUE_DATES
, DECODE(D.DESIGNATOR_TYPE
, 5
, 2
, 1))
, M.ENFORCE_DEM_DUE_DATES
, NVL(M.CURR_PLANNED_RESOURCES
, 1)
, M.PLANNED_RESOURCES
, M.CURR_DEM_PRIORITY_RULE_ID
, M.DEM_PRIORITY_RULE_ID
, M.ENFORCE_CAP_CONSTRAINTS
, NVL(M.CURR_ENFORCE_CAP_CONSTRAINTS
, 2)
, M.BOTTLENECK_RES_GROUP
, M.CURR_BOTTLENECK_RES_GROUP
, M.OBJECTIVE_WEIGHT_1
, M.OBJECTIVE_WEIGHT_2
, M.OBJECTIVE_WEIGHT_3
, M.OBJECTIVE_WEIGHT_4
, M.OBJECTIVE_WEIGHT_5
, NVL(M.OPTIMIZE_FLAG
, DECODE(D.DESIGNATOR_TYPE
, 5
, 1
, 2))
, M.DMD_LATENESS_PENALTY_COST
, M.SUPPLIER_CAP_OVER_UTIL_COST
, M.TRANSPORT_CAP_OVER_UTIL_COST
, M.RESOURCE_OVER_UTIL_COST
, NVL(M.CURR_START_DATE
, SYSDATE)
, M.START_DATE
, NVL(M.DAILY_ITEM_AGGREGATION_LEVEL
, 1)
, NVL(M.DAILY_MATERIAL_CONSTRAINTS
, 2)
, NVL(M.DAILY_RESOURCE_CONSTRAINTS
, 2)
, NVL(M.DAILY_RES_AGGREGATION_LEVEL
, 1)
, M.DAILY_TRANS_CONSTRAINTS
, NVL(M.WEEKLY_ITEM_AGGREGATION_LEVEL
, 1)
, NVL(M.WEEKLY_MATERIAL_CONSTRAINTS
, 2)
, NVL(M.WEEKLY_RESOURCE_CONSTRAINTS
, 2)
, NVL(M.WEEKLY_RES_AGGREGATION_LEVEL
, 1)
, M.WEEKLY_TRANS_CONSTRAINTS
, NVL(M.PERIOD_ITEM_AGGREGATION_LEVEL
, 1)
, NVL(M.PERIOD_MATERIAL_CONSTRAINTS
, 2)
, NVL(M.PERIOD_RESOURCE_CONSTRAINTS
, 2)
, NVL(M.PERIOD_RES_AGGREGATION_LEVEL
, 1)
, M.PERIOD_TRANS_CONSTRAINTS
, NVL(M.DAILY_CUTOFF_BUCKET
, 30)
, NVL(M.HOUR_CUTOFF_BUCKET
, 0)
, NVL(M.MIN_CUTOFF_BUCKET
, 0)
, NVL(M.PERIOD_CUTOFF_BUCKET
, 5)
, M.QTRLY_CUTOFF_BUCKET
, NVL(M.WEEKLY_CUTOFF_BUCKET
, 4)
, NVL(M.DAILY_RTG_AGGREGATION_LEVEL
, DECODE(D.DESIGNATOR_TYPE
, 2
, 2
, 1))
, NVL(M.WEEKLY_RTG_AGGREGATION_LEVEL
, DECODE(D.DESIGNATOR_TYPE
, 2
, 2
, 1))
, NVL(M.PERIOD_RTG_AGGREGATION_LEVEL
, DECODE(D.DESIGNATOR_TYPE
, 2
, 2
, 1))
, M.SCHEDULE_FLAG
, M.LOT_FOR_LOT_FLAG
, M.FULL_PEGGING
, M.CURR_FULL_PEGGING
, MSR.MEANING
, M.SERVICE_LEVEL
, NVL(M.CURR_ENFORCE_SL_CONSTRAINTS
, DECODE(D.DESIGNATOR_TYPE
, 5
, 1
, 2))
, M.ENFORCE_SL_CONSTRAINTS
, D.DESIGNATOR_TYPE
, D.DEMAND_CLASS
, M.PLAN_INVENTORY_POINT
, M.ENABLE_CLOSEST_QTY_PEGGING
, M.CURR_CLOSEST_QTY_PEG
, M.ENABLE_PRIORITY_PEGGING
, M.CURR_PRIORITY_PEGGING
, M.BACKWARD_DAYS
, M.CURR_BACKWARD_DAYS
, M.FORWARD_DAYS
, M.CURR_FORWARD_DAYS
, NVL(M.CURR_ENFORCE_SRC_CONSTRAINTS
, DECODE(D.DESIGNATOR_TYPE
, 5
, 2
, 1))
, M.ENFORCE_SRC_CONSTRAINTS
, NVL(M.USE_END_ITEM_SUBSTITUTIONS
, 2)
, NVL(M.USE_ALTERNATE_RESOURCES
, 1)
, NVL(M.USE_ALTERNATE_BOM_ROUTING
, 1)
, NVL(M.USE_SUBSTITUTE_COMPONENTS
, 1)
, NVL(M.USE_ALTERNATE_SOURCES
, 1)
, M.SUBSTITUTION_DESIGNATOR
, M.DISPLAY_KPI
, DECODE(D.DESIGNATOR_TYPE
, 5
, NVL(M.FORECAST_ALLOCATION_METHOD
, 2)
, M.FORECAST_ALLOCATION_METHOD)
, M.FORECAST_CONSUMPTION_METHOD
FROM MFG_LOOKUPS L3
, MFG_LOOKUPS L1
, MSC_SCHEDULING_RULES MSR
, MSC_ASSIGNMENT_SETS MAS
, MSC_ASSIGNMENT_SETS MAS1
, MSC_PARAMETERS P
, MSC_PLANS M
, MSC_DESIGNATORS D
WHERE L1.LOOKUP_TYPE = 'MSC_PLAN_TYPE_LONG'
AND L1.LOOKUP_CODE = DECODE(D.DESIGNATOR_TYPE
, -99
, 1
, 2
, 2
, 3
, 1
, 5
, 4
, 3)
AND L3.LOOKUP_TYPE = 'MRP_ORGANIZATION_SELECTION'
AND L3.LOOKUP_CODE = NVL(D.ORGANIZATION_SELECTION
, 1)
AND MAS1.ASSIGNMENT_SET_ID (+) = M.ASSIGNMENT_SET_ID
AND MAS1.SR_INSTANCE_ID (+) = M.SR_INSTANCE_ID
AND MAS.ASSIGNMENT_SET_ID (+) = M.CURR_ASSIGNMENT_SET_ID
AND MAS.SR_INSTANCE_ID (+) = M.SR_INSTANCE_ID
AND D.ORGANIZATION_ID = P.ORGANIZATION_ID (+)
AND D.SR_INSTANCE_ID = P.SR_INSTANCE_ID (+)
AND NVL(D.DESIGNATOR_TYPE
, 3) IN (2
, 3
, 4
, 5
, -99)
AND M.SR_INSTANCE_ID(+) = D.SR_INSTANCE_ID
AND M.ORGANIZATION_ID (+)= D.ORGANIZATION_ID
AND M.COMPILE_DESIGNATOR(+) = D.DESIGNATOR
AND MSR.RULE_ID(+)=M.CURR_DEM_PRIORITY_RULE_ID
AND NVL(M.COPY_PLAN_ID
, -1) < 0
AND NVL(D.COPY_DESIGNATOR_ID
, -1)<0