View: CSP_MINMAX_NOTIFS_V

Product:	CSP - Spares Management
Description:	Lists warehouse notifications
Implementation/DBA Data:	APPS.CSP_MINMAX_NOTIFS_V

View Text

SELECT CN.NOTIFICATION_ID NOTIFICATION_ID
, CN.ORGANIZATION_ID ORGANIZATION_ID
, CN.INVENTORY_ITEM_ID INVENTORY_ITEM_ID
, MSIV.CONCATENATED_SEGMENTS ITEM_NUMBER
, MSIV.DESCRIPTION ITEM_DESCRIPTION
, ( CIC.ITEM_COST * CN.QUANTITY) ITEM_COST
, CN.PLANNER_CODE PLANNER_CODE
, CN.NOTIFICATION_TYPE NOTIFICATION_TYPE
, FL.MEANING NOTIFICATION_TYPE_MEANING
, CN.QUANTITY NOTIFICATION_QTY
, CN.NOTIFICATION_DATE NOTIFICATION_DATE
, SUM(DECODE(CND.SOURCE_TYPE
, 'EXCESS'
, CND.AVAILABLE_QUANTITY
, NULL)) AS EXCESS_QTY
, SUM(DECODE(CND.SOURCE_TYPE
, 'REPAIR'
, CND.AVAILABLE_QUANTITY
, NULL)) AS REPAIR_QTY
, CN.NEED_DATE NEED_DATE
, MIN(CND.ORDER_BY_DATE) ORDER_BY_DATE
FROM MTL_SYSTEM_ITEMS_KFV MSIV
, CST_ITEM_COSTS_FOR_GL_VIEW CIC
, CSP_NOTIFICATION_DETAILS CND
, CSP_NOTIFICATIONS CN
, FND_LOOKUPS FL
WHERE CIC.INVENTORY_ITEM_ID(+) = MSIV.INVENTORY_ITEM_ID
AND CIC.ORGANIZATION_ID(+) = MSIV.ORGANIZATION_ID
AND MSIV.INVENTORY_ITEM_ID = CN.INVENTORY_ITEM_ID
AND MSIV.ORGANIZATION_ID = CN.ORGANIZATION_ID
AND CND.NOTIFICATION_ID(+) = CN.NOTIFICATION_ID
AND CN.STATUS = 1
AND FL.LOOKUP_CODE = CN.NOTIFICATION_TYPE
AND FL.LOOKUP_TYPE = 'CSP_NOTIF_SOURCE_TYPE' GROUP BY CN.NOTIFICATION_ID
, CN.ORGANIZATION_ID
, CN.INVENTORY_ITEM_ID
, MSIV.CONCATENATED_SEGMENTS
, MSIV.DESCRIPTION
, ITEM_COST
, CN.PLANNER_CODE
, CN.NOTIFICATION_TYPE
, FL.MEANING
, CN.QUANTITY
, CN.NOTIFICATION_DATE
, CN.NEED_DATE

Columns