VIEW: APPS.CSP_PLANNING_HEADERS_V

Source

View Text - Preformatted

SELECT cuh.USAGE_HEADER_ID, cuh.HEADER_DATA_TYPE, cuh.PLANNING_PARAMETERS_ID, cuh.INVENTORY_ITEM_ID, cuh.ORGANIZATION_ID, ood.ORGANIZATION_NAME, cuh.SECONDARY_INVENTORY, cuh.PROCESS_STATUS, cuh.COMMENTS, fl.MEANING PROCESS_STATUS_MEANING, msi.concatenated_segments ITEM_NUMBER, msi.DESCRIPTION ITEM_DESCRIPTION, ROUND(RAW_AWU,4) RAW_AWU, DECODE(SIGN(AWU),-1,0,ROUND(AWU,4)) AWU, csp_pick_utils.calculate_min_max( cuh.secondary_inventory, cuh.organization_id, cpp.edq_factor, cpp.service_level, cuh.item_cost, DECODE(SIGN(cuh.AWU),-1,0,cuh.AWU) , cuh.lead_time, cuh.standard_deviation, cpp.safety_stock_flag, cpp.Asl_Flag) func_call, csp_pick_utils.Get_SAFETY_FACTOR( cuh.secondary_inventory, cuh.organization_id, cpp.edq_factor, cpp.service_level, cuh.item_cost, DECODE(SIGN(cuh.AWU),-1,0,cuh.AWU) , cuh.lead_time, cuh.standard_deviation, cpp.safety_stock_flag, cpp.Asl_Flag) SAFETY_FACTOR, csp_pick_utils.Get_MIN_QUANTITY( cuh.secondary_inventory, cuh.organization_id, cpp.edq_factor, cpp.service_level, cuh.item_cost, DECODE(SIGN(cuh.AWU),-1,0,cuh.AWU) , cuh.lead_time, cuh.standard_deviation, cpp.safety_stock_flag, cpp.Asl_Flag) RECOMMENDED_MIN_QTY, csp_pick_utils.GET_MAX_QUANTITY( cuh.secondary_inventory, cuh.organization_id, cpp.edq_factor, cpp.service_level, cuh.item_cost, DECODE(SIGN(cuh.AWU),-1,0,cuh.AWU) , cuh.lead_time, cuh.standard_deviation, cpp.safety_stock_flag, cpp.Asl_Flag) RECOMMENDED_MAX_QTY, cpp.edq_factor, cpp.service_level, cpp.ASL_FLAG, cpp.SAFETY_STOCK_FLAG, DECODE( cpp.NODE_TYPE, 'SUBINVENTORY', NVL(misi.MIN_MINMAX_QUANTITY,0), 'ORGANIZATION_WH', NVL(msi.MIN_MINMAX_QUANTITY,0),0) MIN_QUANTITY, DECODE( cpp.NODE_TYPE, 'SUBINVENTORY', NVL(misi.MAX_MINMAX_QUANTITY,0),'ORGANIZATION_WH', NVL(msi.MAX_MINMAX_QUANTITY,0),0) MAX_QUANTITY, ROUND(cuh.ITEM_COST * DECODE( cpp.NODE_TYPE, 'SUBINVENTORY', NVL(misi.MAX_MINMAX_QUANTITY,0),'ORGANIZATION_WH', NVL(msi.MAX_MINMAX_QUANTITY,0),0),2) ACTUAL_COST, ROUND( csp_pick_utils.GET_MAX_QUANTITY( cuh.secondary_inventory, cuh.organization_id, cpp.edq_factor, cpp.service_level, cuh.item_cost, DECODE(SIGN(cuh.AWU),-1,0,cuh.AWU) , cuh.lead_time, cuh.standard_deviation, cpp.safety_stock_flag, cpp.Asl_Flag) * cuh.Item_Cost,2) RECOMMENDED_COST, ROUND( (csp_pick_utils.GET_MAX_QUANTITY( cuh.secondary_inventory, cuh.organization_id, cpp.edq_factor, cpp.service_level, cuh.item_cost, DECODE(SIGN(cuh.AWU),-1,0,cuh.AWU) , cuh.lead_time, cuh.standard_deviation, cpp.safety_stock_flag, cpp.Asl_Flag) * cuh.Item_Cost),2) - ROUND(cuh.ITEM_COST * DECODE( cpp.NODE_TYPE, 'SUBINVENTORY', NVL(misi.MAX_MINMAX_QUANTITY,0),'ORGANIZATION_WH', NVL(msi.MAX_MINMAX_QUANTITY,0),0),2) DELTA_COST, csp_pick_utils.GET_MIN_QUANTITY( cuh.secondary_inventory, cuh.organization_id, cpp.edq_factor, cpp.service_level, cuh.item_cost, DECODE(SIGN(cuh.AWU),-1,0,cuh.AWU) , cuh.lead_time, cuh.standard_deviation, cpp.safety_stock_flag, cpp.Asl_Flag) - DECODE( cpp.NODE_TYPE, 'SUBINVENTORY', NVL(misi.MIN_MINMAX_QUANTITY,0), 'ORGANIZATION_WH', NVL(msi.MIN_MINMAX_QUANTITY,0),0) DELTA_MIN, csp_pick_utils.GET_MAX_QUANTITY( cuh.secondary_inventory, cuh.organization_id, cpp.edq_factor, cpp.service_level, cuh.item_cost, DECODE(SIGN(cuh.AWU),-1,0,cuh.AWU) , cuh.lead_time, cuh.standard_deviation, cpp.safety_stock_flag, cpp.Asl_Flag) - DECODE( cpp.NODE_TYPE, 'SUBINVENTORY', NVL(misi.MAX_MINMAX_QUANTITY,0),'ORGANIZATION_WH', NVL(msi.MAX_MINMAX_QUANTITY,0),0) DELTA_MAX, DECODE( DECODE( cpp.NODE_TYPE, 'SUBINVENTORY', NVL(misi.MIN_MINMAX_QUANTITY,0), 'ORGANIZATION_WH', NVL(msi.MIN_MINMAX_QUANTITY,0),0),0, ROUND((csp_pick_utils.GET_MIN_QUANTITY( cuh.secondary_inventory, cuh.organization_id, cpp.edq_factor, cpp.service_level, cuh.item_cost, DECODE(SIGN(cuh.AWU),-1,0,cuh.AWU) , cuh.lead_time, cuh.standard_deviation, cpp.safety_stock_flag, cpp.Asl_Flag) - DECODE( cpp.NODE_TYPE, 'SUBINVENTORY', NVL(misi.MIN_MINMAX_QUANTITY,0), 'ORGANIZATION_WH', NVL(msi.MIN_MINMAX_QUANTITY,0),0)) * 100,2), ROUND(((csp_pick_utils.GET_MIN_QUANTITY( cuh.secondary_inventory, cuh.organization_id, cpp.edq_factor, cpp.service_level, cuh.item_cost, DECODE(SIGN(cuh.AWU),-1,0,cuh.AWU) , cuh.lead_time, cuh.standard_deviation, cpp.safety_stock_flag, cpp.Asl_Flag) - DECODE( cpp.NODE_TYPE, 'SUBINVENTORY', NVL(misi.MIN_MINMAX_QUANTITY,0), 'ORGANIZATION_WH', NVL(msi.MIN_MINMAX_QUANTITY,0),0))/ DECODE( cpp.NODE_TYPE, 'SUBINVENTORY', NVL(misi.MIN_MINMAX_QUANTITY,0), 'ORGANIZATION_WH', NVL(msi.MIN_MINMAX_QUANTITY,0),0)) * 100,2)) DELTA_MIN_PERC , DECODE( DECODE( cpp.NODE_TYPE, 'SUBINVENTORY', NVL(misi.MAX_MINMAX_QUANTITY,0),'ORGANIZATION_WH', NVL(msi.MAX_MINMAX_QUANTITY,0),0),0, ROUND((csp_pick_utils.GET_MAX_QUANTITY( cuh.secondary_inventory, cuh.organization_id, cpp.edq_factor, cpp.service_level, cuh.item_cost, DECODE(SIGN(cuh.AWU),-1,0,cuh.AWU) , cuh.lead_time, cuh.standard_deviation, cpp.safety_stock_flag, cpp.Asl_Flag) - DECODE( cpp.NODE_TYPE, 'SUBINVENTORY', NVL(misi.MAX_MINMAX_QUANTITY,0), 'ORGANIZATION_WH', NVL(msi.MAX_MINMAX_QUANTITY,0),0)) * 100,2), ROUND(((csp_pick_utils.GET_MAX_QUANTITY( cuh.secondary_inventory, cuh.organization_id, cpp.edq_factor, cpp.service_level, cuh.item_cost, DECODE(SIGN(cuh.AWU),-1,0,cuh.AWU) , cuh.lead_time, cuh.standard_deviation, cpp.safety_stock_flag, cpp.Asl_Flag) - DECODE( cpp.NODE_TYPE, 'SUBINVENTORY', NVL(misi.MAX_MINMAX_QUANTITY,0), 'ORGANIZATION_WH', NVL(msi.MAX_MINMAX_QUANTITY,0),0)) / DECODE( cpp.NODE_TYPE, 'SUBINVENTORY', NVL(misi.MAX_MINMAX_QUANTITY,0),'ORGANIZATION_WH', NVL(msi.MAX_MINMAX_QUANTITY,0),0)) * 100 ,2 )) DELTA_MAX_PERC, cuh.CREATED_BY, cuh.CREATION_DATE, cuh.LAST_UPDATED_BY, cuh.LAST_UPDATE_DATE, cuh.LAST_UPDATE_LOGIN from CSP_USAGE_HEADERS cuh, MTL_SYSTEM_ITEMS_B_KFV msi, CSP_PLANNING_PARAMETERS cpp, MTL_ITEM_SUB_INVENTORIES misi, ORG_ORGANIZATION_DEFINITIONS ood, FND_LOOKUPS fl Where cuh.header_data_type in (1,4) And cpp.organization_id = cuh.organization_id And nvl(cpp.secondary_inventory,'-') = cuh.secondary_inventory And msi.INVENTORY_ITEM_ID(+) = cuh.INVENTORY_ITEM_ID And msi.ORGANIZATION_ID(+) = cuh.ORGANIZATION_ID And misi.INVENTORY_ITEM_ID(+) = cuh.INVENTORY_ITEM_ID And misi.ORGANIZATION_ID(+) = cuh.ORGANIZATION_ID And misi.SECONDARY_INVENTORY(+) = cuh.SECONDARY_INVENTORY And fl.LOOKUP_TYPE = 'CSP_PLANNING_PROCESS_STATUS' And fl.LOOKUP_CODE = cuh.PROCESS_STATUS And ood.ORGANIZATION_ID = cuh.ORGANIZATION_ID order by func_call

View Text - HTML Formatted

SELECT CUH.USAGE_HEADER_ID
, CUH.HEADER_DATA_TYPE
, CUH.PLANNING_PARAMETERS_ID
, CUH.INVENTORY_ITEM_ID
, CUH.ORGANIZATION_ID
, OOD.ORGANIZATION_NAME
, CUH.SECONDARY_INVENTORY
, CUH.PROCESS_STATUS
, CUH.COMMENTS
, FL.MEANING PROCESS_STATUS_MEANING
, MSI.CONCATENATED_SEGMENTS ITEM_NUMBER
, MSI.DESCRIPTION ITEM_DESCRIPTION
, ROUND(RAW_AWU
, 4) RAW_AWU
, DECODE(SIGN(AWU)
, -1
, 0
, ROUND(AWU
, 4)) AWU
, CSP_PICK_UTILS.CALCULATE_MIN_MAX( CUH.SECONDARY_INVENTORY
, CUH.ORGANIZATION_ID
, CPP.EDQ_FACTOR
, CPP.SERVICE_LEVEL
, CUH.ITEM_COST
, DECODE(SIGN(CUH.AWU)
, -1
, 0
, CUH.AWU)
, CUH.LEAD_TIME
, CUH.STANDARD_DEVIATION
, CPP.SAFETY_STOCK_FLAG
, CPP.ASL_FLAG) FUNC_CALL
, CSP_PICK_UTILS.GET_SAFETY_FACTOR( CUH.SECONDARY_INVENTORY
, CUH.ORGANIZATION_ID
, CPP.EDQ_FACTOR
, CPP.SERVICE_LEVEL
, CUH.ITEM_COST
, DECODE(SIGN(CUH.AWU)
, -1
, 0
, CUH.AWU)
, CUH.LEAD_TIME
, CUH.STANDARD_DEVIATION
, CPP.SAFETY_STOCK_FLAG
, CPP.ASL_FLAG) SAFETY_FACTOR
, CSP_PICK_UTILS.GET_MIN_QUANTITY( CUH.SECONDARY_INVENTORY
, CUH.ORGANIZATION_ID
, CPP.EDQ_FACTOR
, CPP.SERVICE_LEVEL
, CUH.ITEM_COST
, DECODE(SIGN(CUH.AWU)
, -1
, 0
, CUH.AWU)
, CUH.LEAD_TIME
, CUH.STANDARD_DEVIATION
, CPP.SAFETY_STOCK_FLAG
, CPP.ASL_FLAG) RECOMMENDED_MIN_QTY
, CSP_PICK_UTILS.GET_MAX_QUANTITY( CUH.SECONDARY_INVENTORY
, CUH.ORGANIZATION_ID
, CPP.EDQ_FACTOR
, CPP.SERVICE_LEVEL
, CUH.ITEM_COST
, DECODE(SIGN(CUH.AWU)
, -1
, 0
, CUH.AWU)
, CUH.LEAD_TIME
, CUH.STANDARD_DEVIATION
, CPP.SAFETY_STOCK_FLAG
, CPP.ASL_FLAG) RECOMMENDED_MAX_QTY
, CPP.EDQ_FACTOR
, CPP.SERVICE_LEVEL
, CPP.ASL_FLAG
, CPP.SAFETY_STOCK_FLAG
, DECODE( CPP.NODE_TYPE
, 'SUBINVENTORY'
, NVL(MISI.MIN_MINMAX_QUANTITY
, 0)
, 'ORGANIZATION_WH'
, NVL(MSI.MIN_MINMAX_QUANTITY
, 0)
, 0) MIN_QUANTITY
, DECODE( CPP.NODE_TYPE
, 'SUBINVENTORY'
, NVL(MISI.MAX_MINMAX_QUANTITY
, 0)
, 'ORGANIZATION_WH'
, NVL(MSI.MAX_MINMAX_QUANTITY
, 0)
, 0) MAX_QUANTITY
, ROUND(CUH.ITEM_COST * DECODE( CPP.NODE_TYPE
, 'SUBINVENTORY'
, NVL(MISI.MAX_MINMAX_QUANTITY
, 0)
, 'ORGANIZATION_WH'
, NVL(MSI.MAX_MINMAX_QUANTITY
, 0)
, 0)
, 2) ACTUAL_COST
, ROUND( CSP_PICK_UTILS.GET_MAX_QUANTITY( CUH.SECONDARY_INVENTORY
, CUH.ORGANIZATION_ID
, CPP.EDQ_FACTOR
, CPP.SERVICE_LEVEL
, CUH.ITEM_COST
, DECODE(SIGN(CUH.AWU)
, -1
, 0
, CUH.AWU)
, CUH.LEAD_TIME
, CUH.STANDARD_DEVIATION
, CPP.SAFETY_STOCK_FLAG
, CPP.ASL_FLAG) * CUH.ITEM_COST
, 2) RECOMMENDED_COST
, ROUND( (CSP_PICK_UTILS.GET_MAX_QUANTITY( CUH.SECONDARY_INVENTORY
, CUH.ORGANIZATION_ID
, CPP.EDQ_FACTOR
, CPP.SERVICE_LEVEL
, CUH.ITEM_COST
, DECODE(SIGN(CUH.AWU)
, -1
, 0
, CUH.AWU)
, CUH.LEAD_TIME
, CUH.STANDARD_DEVIATION
, CPP.SAFETY_STOCK_FLAG
, CPP.ASL_FLAG) * CUH.ITEM_COST)
, 2) - ROUND(CUH.ITEM_COST * DECODE( CPP.NODE_TYPE
, 'SUBINVENTORY'
, NVL(MISI.MAX_MINMAX_QUANTITY
, 0)
, 'ORGANIZATION_WH'
, NVL(MSI.MAX_MINMAX_QUANTITY
, 0)
, 0)
, 2) DELTA_COST
, CSP_PICK_UTILS.GET_MIN_QUANTITY( CUH.SECONDARY_INVENTORY
, CUH.ORGANIZATION_ID
, CPP.EDQ_FACTOR
, CPP.SERVICE_LEVEL
, CUH.ITEM_COST
, DECODE(SIGN(CUH.AWU)
, -1
, 0
, CUH.AWU)
, CUH.LEAD_TIME
, CUH.STANDARD_DEVIATION
, CPP.SAFETY_STOCK_FLAG
, CPP.ASL_FLAG) - DECODE( CPP.NODE_TYPE
, 'SUBINVENTORY'
, NVL(MISI.MIN_MINMAX_QUANTITY
, 0)
, 'ORGANIZATION_WH'
, NVL(MSI.MIN_MINMAX_QUANTITY
, 0)
, 0) DELTA_MIN
, CSP_PICK_UTILS.GET_MAX_QUANTITY( CUH.SECONDARY_INVENTORY
, CUH.ORGANIZATION_ID
, CPP.EDQ_FACTOR
, CPP.SERVICE_LEVEL
, CUH.ITEM_COST
, DECODE(SIGN(CUH.AWU)
, -1
, 0
, CUH.AWU)
, CUH.LEAD_TIME
, CUH.STANDARD_DEVIATION
, CPP.SAFETY_STOCK_FLAG
, CPP.ASL_FLAG) - DECODE( CPP.NODE_TYPE
, 'SUBINVENTORY'
, NVL(MISI.MAX_MINMAX_QUANTITY
, 0)
, 'ORGANIZATION_WH'
, NVL(MSI.MAX_MINMAX_QUANTITY
, 0)
, 0) DELTA_MAX
, DECODE( DECODE( CPP.NODE_TYPE
, 'SUBINVENTORY'
, NVL(MISI.MIN_MINMAX_QUANTITY
, 0)
, 'ORGANIZATION_WH'
, NVL(MSI.MIN_MINMAX_QUANTITY
, 0)
, 0)
, 0
, ROUND((CSP_PICK_UTILS.GET_MIN_QUANTITY( CUH.SECONDARY_INVENTORY
, CUH.ORGANIZATION_ID
, CPP.EDQ_FACTOR
, CPP.SERVICE_LEVEL
, CUH.ITEM_COST
, DECODE(SIGN(CUH.AWU)
, -1
, 0
, CUH.AWU)
, CUH.LEAD_TIME
, CUH.STANDARD_DEVIATION
, CPP.SAFETY_STOCK_FLAG
, CPP.ASL_FLAG) - DECODE( CPP.NODE_TYPE
, 'SUBINVENTORY'
, NVL(MISI.MIN_MINMAX_QUANTITY
, 0)
, 'ORGANIZATION_WH'
, NVL(MSI.MIN_MINMAX_QUANTITY
, 0)
, 0)) * 100
, 2)
, ROUND(((CSP_PICK_UTILS.GET_MIN_QUANTITY( CUH.SECONDARY_INVENTORY
, CUH.ORGANIZATION_ID
, CPP.EDQ_FACTOR
, CPP.SERVICE_LEVEL
, CUH.ITEM_COST
, DECODE(SIGN(CUH.AWU)
, -1
, 0
, CUH.AWU)
, CUH.LEAD_TIME
, CUH.STANDARD_DEVIATION
, CPP.SAFETY_STOCK_FLAG
, CPP.ASL_FLAG) - DECODE( CPP.NODE_TYPE
, 'SUBINVENTORY'
, NVL(MISI.MIN_MINMAX_QUANTITY
, 0)
, 'ORGANIZATION_WH'
, NVL(MSI.MIN_MINMAX_QUANTITY
, 0)
, 0))/ DECODE( CPP.NODE_TYPE
, 'SUBINVENTORY'
, NVL(MISI.MIN_MINMAX_QUANTITY
, 0)
, 'ORGANIZATION_WH'
, NVL(MSI.MIN_MINMAX_QUANTITY
, 0)
, 0)) * 100
, 2)) DELTA_MIN_PERC
, DECODE( DECODE( CPP.NODE_TYPE
, 'SUBINVENTORY'
, NVL(MISI.MAX_MINMAX_QUANTITY
, 0)
, 'ORGANIZATION_WH'
, NVL(MSI.MAX_MINMAX_QUANTITY
, 0)
, 0)
, 0
, ROUND((CSP_PICK_UTILS.GET_MAX_QUANTITY( CUH.SECONDARY_INVENTORY
, CUH.ORGANIZATION_ID
, CPP.EDQ_FACTOR
, CPP.SERVICE_LEVEL
, CUH.ITEM_COST
, DECODE(SIGN(CUH.AWU)
, -1
, 0
, CUH.AWU)
, CUH.LEAD_TIME
, CUH.STANDARD_DEVIATION
, CPP.SAFETY_STOCK_FLAG
, CPP.ASL_FLAG) - DECODE( CPP.NODE_TYPE
, 'SUBINVENTORY'
, NVL(MISI.MAX_MINMAX_QUANTITY
, 0)
, 'ORGANIZATION_WH'
, NVL(MSI.MAX_MINMAX_QUANTITY
, 0)
, 0)) * 100
, 2)
, ROUND(((CSP_PICK_UTILS.GET_MAX_QUANTITY( CUH.SECONDARY_INVENTORY
, CUH.ORGANIZATION_ID
, CPP.EDQ_FACTOR
, CPP.SERVICE_LEVEL
, CUH.ITEM_COST
, DECODE(SIGN(CUH.AWU)
, -1
, 0
, CUH.AWU)
, CUH.LEAD_TIME
, CUH.STANDARD_DEVIATION
, CPP.SAFETY_STOCK_FLAG
, CPP.ASL_FLAG) - DECODE( CPP.NODE_TYPE
, 'SUBINVENTORY'
, NVL(MISI.MAX_MINMAX_QUANTITY
, 0)
, 'ORGANIZATION_WH'
, NVL(MSI.MAX_MINMAX_QUANTITY
, 0)
, 0)) / DECODE( CPP.NODE_TYPE
, 'SUBINVENTORY'
, NVL(MISI.MAX_MINMAX_QUANTITY
, 0)
, 'ORGANIZATION_WH'
, NVL(MSI.MAX_MINMAX_QUANTITY
, 0)
, 0)) * 100
, 2 )) DELTA_MAX_PERC
, CUH.CREATED_BY
, CUH.CREATION_DATE
, CUH.LAST_UPDATED_BY
, CUH.LAST_UPDATE_DATE
, CUH.LAST_UPDATE_LOGIN
FROM CSP_USAGE_HEADERS CUH
, MTL_SYSTEM_ITEMS_B_KFV MSI
, CSP_PLANNING_PARAMETERS CPP
, MTL_ITEM_SUB_INVENTORIES MISI
, ORG_ORGANIZATION_DEFINITIONS OOD
, FND_LOOKUPS FL
WHERE CUH.HEADER_DATA_TYPE IN (1
, 4)
AND CPP.ORGANIZATION_ID = CUH.ORGANIZATION_ID
AND NVL(CPP.SECONDARY_INVENTORY
, '-') = CUH.SECONDARY_INVENTORY
AND MSI.INVENTORY_ITEM_ID(+) = CUH.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID(+) = CUH.ORGANIZATION_ID
AND MISI.INVENTORY_ITEM_ID(+) = CUH.INVENTORY_ITEM_ID
AND MISI.ORGANIZATION_ID(+) = CUH.ORGANIZATION_ID
AND MISI.SECONDARY_INVENTORY(+) = CUH.SECONDARY_INVENTORY
AND FL.LOOKUP_TYPE = 'CSP_PLANNING_PROCESS_STATUS'
AND FL.LOOKUP_CODE = CUH.PROCESS_STATUS
AND OOD.ORGANIZATION_ID = CUH.ORGANIZATION_ID ORDER BY FUNC_CALL