View: CSP_PLANNING_HEADERS_V

Product:	CSP - Spares Management
Description:
Implementation/DBA Data:	APPS.CSP_PLANNING_HEADERS_V

View Text

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
, 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
, CUH.AWU
, CUH.LEAD_TIME
, CUH.STANDARD_DEVIATION
, CPP.SAFETY_STOCK_FLAG
, CPP.ASL_FLAG) FUNC_CALL
, CSP_PICK_UTILS.GET_SAFETY_FACTOR SAFETY_FACTOR
, CSP_PICK_UTILS.GET_MIN_QUANTITY RECOMMENDED_MIN_QTY
, CSP_PICK_UTILS.GET_MAX_QUANTITY RECOMMENDED_MAX_QTY
, CSP_PICK_UTILS.GET_EDQ_FACTOR EDQ_FACTOR
, CSP_PICK_UTILS.GET_SERVICE_LEVEL SERVICE_LEVEL
, CSP_PICK_UTILS.GET_ASL_FLAG ASL_FLAG
, CSP_PICK_UTILS.GET_SAFETY_STOCK_FLAG 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.ITEM_COST
, 2) RECOMMENDED_COST
, ROUND( (CSP_PICK_UTILS.GET_MAX_QUANTITY * 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 - 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 - 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 - 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 - 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 - 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 - 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

Columns