View: MRP_AP_SYSTEM_ITEMS_V

View Text

SELECT /*+ ORDERED */ MASIVA.ORGANIZATION_ID
, MASIVA.INVENTORY_ITEM_ID
, MASIVA.ITEM_NAME
, MASIVA.LOTS_EXPIRATION
, MASIVA.LOT_CONTROL_CODE
, MASIVA.SHRINKAGE_RATE
, MASIVA.FIXED_DAYS_SUPPLY
, MASIVA.FIXED_ORDER_QUANTITY
, MASIVA.FIXED_LOT_MULTIPLIER
, MASIVA.MINIMUM_ORDER_QUANTITY
, MASIVA.MAXIMUM_ORDER_QUANTITY
, NVL(MASIVA.ROUNDING_CONTROL_TYPE
, 2) ROUNDING_CONTROL_TYPE
, MASIVA.PLANNING_TIME_FENCE_DAYS
, MASIVA.DEMAND_TIME_FENCE_DAYS
, MASIVA.DESCRIPTION
, MASIVA.CATEGORY_ID
, MASIVA.CATEGORY_NAME
, MASIVA.MRP_PLANNING_CODE
, MASIVA.FIXED_LEAD_TIME
, MASIVA.VARIABLE_LEAD_TIME
, MASIVA.PREPROCESSING_LEAD_TIME
, MASIVA.POSTPROCESSING_LEAD_TIME
, MASIVA.FULL_LEAD_TIME
, MASIVA.CUMULATIVE_TOTAL_LEAD_TIME
, MASIVA.CUM_MANUFACTURING_LEAD_TIME
, MASIVA.UOM_CODE
, MASIVA.BUILT_IN_WIP_FLAG
, MASIVA.PURCHASING_ENABLED_FLAG
, MASIVA.PLANNING_MAKE_BUY_CODE
, MASIVA.CARRYING_COST
, MASIVA.MRP_CALCULATE_ATP_FLAG
, MASIVA.END_ASSEMBLY_PEGGING_FLAG
, MASIVA.ENG_ITEM_FLAG
, MASIVA.WIP_SUPPLY_TYPE
, MASIVA.MRP_SAFETY_STOCK_CODE
, MASIVA.MRP_SAFETY_STOCK_PERCENT
, MASIVA.SAFETY_STOCK_BUCKET_DAYS
, MASIVA.ACCEPTABLE_EARLY_DELIVERY
, MASIVA.BUYER_ID
, MASIVA.PLANNER_CODE
, MASIVA.PLANNING_EXCEPTION_SET
, MASIVA.BASE_ITEM_ID
, MASIVA.BOM_ITEM_TYPE
, MASIVA.ATO_FORECAST_CONTROL
, MASIVA.EFFECTIVITY_CONTROL
, MASIVA.INVENTORY_PLANNING_CODE
, MASIVA.UNIT_WEIGHT
, MASIVA.UNIT_VOLUME
, MASIVA.WEIGHT_UOM
, MASIVA.VOLUME_UOM
, MASIVA.PRODUCT_FAMILY_ID
, MASIVA.RELEASE_TIME_FENCE_CODE
, MASIVA.RELEASE_TIME_FENCE_DAYS
, MASIVA.ATP_RULE_ID
, MASIVA.ORDER_COST
, MASIVA.ATP_COMPONENTS_FLAG
, MASIVA.REPETITIVE_TYPE
, MASIVA.LIST_PRICE
, MASIVA.ORGANIZATION_CODE
, MASIVA.INVENTORY_TYPE
, MAC.ABC_CLASS_NAME
, MAA.ABC_CLASS_ID
, NVL( CIC.ITEM_COST
, 0) ITEM_COST
, DECODE( PPX.EMPLOYEE_NUMBER
, NULL
, NULL
, PPX.FULL_NAME) BUYER_NAME
, MPES.EXCESS_QUANTITY EXCESS_QUANTITY
, CEIL( DECODE( MPES.SHORTAGE_TYPE
, 1
, DECODE( MASIVA.PLANNING_TIME_FENCE_CODE
, 4
, CEIL(MASIVA.PLANNING_TIME_FENCE_DAYS)
, 1
, CEIL(MASIVA.CUMULATIVE_TOTAL_LEAD_TIME)
, 2
, CEIL(MASIVA.CUM_MANUFACTURING_LEAD_TIME)
, 3
, CEIL(MASIVA.FULL_LEAD_TIME))
, 2
, MPES.USER_TIME_FENCE
, 3
, MASIVA.FULL_LEAD_TIME
, 4
, MASIVA.CUMULATIVE_TOTAL_LEAD_TIME
, 5
, MASIVA.CUM_MANUFACTURING_LEAD_TIME
, NULL)) EXCEPTION_SHORTAGE_DAYS
, CEIL( DECODE( MPES.EXCESS_TYPE
, 1
, DECODE( MASIVA.PLANNING_TIME_FENCE_CODE
, 4
, CEIL(MASIVA.PLANNING_TIME_FENCE_DAYS)
, 1
, CEIL(MASIVA.CUMULATIVE_TOTAL_LEAD_TIME)
, 2
, CEIL(MASIVA.CUM_MANUFACTURING_LEAD_TIME)
, 3
, CEIL(MASIVA.FULL_LEAD_TIME))
, 2
, MPES.USER_TIME_FENCE
, 3
, MASIVA.FULL_LEAD_TIME
, 4
, MASIVA.CUMULATIVE_TOTAL_LEAD_TIME
, 5
, MASIVA.CUM_MANUFACTURING_LEAD_TIME
, NULL)) EXCEPTION_EXCESS_DAYS
, CEIL( DECODE( MPES.OVERPROMISED_TYPE
, 1
, DECODE( MASIVA.PLANNING_TIME_FENCE_CODE
, 4
, CEIL(MASIVA.PLANNING_TIME_FENCE_DAYS)
, 1
, CEIL(MASIVA.CUMULATIVE_TOTAL_LEAD_TIME)
, 2
, CEIL(MASIVA.CUM_MANUFACTURING_LEAD_TIME)
, 3
, CEIL(MASIVA.FULL_LEAD_TIME))
, 2
, MPES.USER_TIME_FENCE
, 3
, MASIVA.FULL_LEAD_TIME
, 4
, MASIVA.CUMULATIVE_TOTAL_LEAD_TIME
, 5
, MASIVA.CUM_MANUFACTURING_LEAD_TIME
, NULL)) EXCEPTION_OVERPROMISED_DAYS
, CEIL( DECODE( MPES.REP_VARIANCE_TYPE
, 1
, DECODE( MASIVA.PLANNING_TIME_FENCE_CODE
, 4
, CEIL(MASIVA.PLANNING_TIME_FENCE_DAYS)
, 1
, CEIL(MASIVA.CUMULATIVE_TOTAL_LEAD_TIME)
, 2
, CEIL(MASIVA.CUM_MANUFACTURING_LEAD_TIME)
, 3
, CEIL(MASIVA.FULL_LEAD_TIME))
, 2
, MPES.USER_TIME_FENCE
, 3
, MASIVA.FULL_LEAD_TIME
, 4
, MASIVA.CUMULATIVE_TOTAL_LEAD_TIME
, 5
, MASIVA.CUM_MANUFACTURING_LEAD_TIME
, NULL)) REPETITIVE_VARIANCE_DAYS
, NVL(MAA.RN
, 0) RN7
, NVL(CIC.RN
, 0) RN6
, NVL(MPES.RN
, 0) RN5
, MASIVA.RN4
, MASIVA.RN3
, MASIVA.RN2
, MASIVA.RN1
FROM MRP_AP_SYS_ITEMS_V MASIVA
, MTL_ABC_CLASSES MAC
, MRP_AP_ABC_ASGN_SN MAA
, MRP_AP_ITEM_COSTS_SN CIC
, PER_PEOPLE_X PPX
, MRP_AP_PL_EX_SETS_SN MPES
WHERE MAC.ABC_CLASS_ID(+)= MAA.ABC_CLASS_ID
AND MPES.EXCEPTION_SET_NAME (+) = MASIVA.PLANNING_EXCEPTION_SET
AND MPES.ORGANIZATION_ID (+) = MASIVA.ORGANIZATION_ID
AND MAA.ASSIGNMENT_GROUP_ID(+)= MASIVA.DEFAULT_ABC_ASSIGNMENT_GROUP
AND MAA.INVENTORY_ITEM_ID(+)= MASIVA.INVENTORY_ITEM_ID
AND CIC.ORGANIZATION_ID(+)= MASIVA.COST_ORGANIZATION_ID
AND CIC.COST_TYPE_ID(+)= MASIVA.PRIMARY_COST_METHOD
AND CIC.INVENTORY_ITEM_ID(+)= MASIVA.INVENTORY_ITEM_ID
AND PPX.PERSON_ID(+)= MASIVA.BUYER_ID
AND 0+PPX.BUSINESS_GROUP_ID (+)= MASIVA.BUSINESS_GROUP_ID

Columns