VIEW: APPS.CSP_MINMAX_NOTIFS_V

Source

View Text - Preformatted

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, cn.status, fl2.meaning status_desc from mtl_system_items_kfv msiv, cst_item_costs_for_gl_view cic, csp_notification_Details cnd, csp_notifications cn, fnd_lookups fl, fnd_lookups fl2 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' and fl2.lookup_code = cn.status and fl2.lookup_type = 'CSP_NOTIFICATION_STATUS' 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, cn.status, fl2.meaning

View Text - HTML Formatted

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
, CN.STATUS
, FL2.MEANING STATUS_DESC
FROM MTL_SYSTEM_ITEMS_KFV MSIV
, CST_ITEM_COSTS_FOR_GL_VIEW CIC
, CSP_NOTIFICATION_DETAILS CND
, CSP_NOTIFICATIONS CN
, FND_LOOKUPS FL
, FND_LOOKUPS FL2
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'
AND FL2.LOOKUP_CODE = CN.STATUS
AND FL2.LOOKUP_TYPE = 'CSP_NOTIFICATION_STATUS' 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
, CN.STATUS
, FL2.MEANING