View: IC_SUMM_INV_ONHAND_V

Product:	GMI - Process Manufacturing Inventory
Description:	Private view
Implementation/DBA Data:	APPS.IC_SUMM_INV_ONHAND_V

View Text

SELECT I.ITEM_ID
, I.WHSE_CODE
, DECODE(I.LOT_ID
, 0
, NULL
, M.QC_GRADE)
, SUM(I.LOCT_ONHAND) * DECODE(I.LOT_STATUS
, NULL
, 1
, NVL(S.NETTABLE_IND
, 0)) * DECODE(I.LOT_STATUS
, NULL
, 1
, DECODE(NVL(S.REJECTED_IND
, 0)
, 1
, 0
, 1))
, SUM(I.LOCT_ONHAND2) * DECODE(I.LOT_STATUS
, NULL
, 1
, NVL(S.NETTABLE_IND
, 0)) * DECODE(I.LOT_STATUS
, NULL
, 1
, DECODE(NVL(S.REJECTED_IND
, 0)
, 1
, 0
, 1))
, SUM(I.LOCT_ONHAND) * DECODE(I.LOT_STATUS
, NULL
, 1
, NVL(S.PROD_IND
, 0)) * DECODE(I.LOT_STATUS
, NULL
, 1
, DECODE(NVL(S.REJECTED_IND
, 0)
, 1
, 0
, 1))
, SUM(I.LOCT_ONHAND2) * DECODE(I.LOT_STATUS
, NULL
, 1
, NVL(S.PROD_IND
, 0)) * DECODE(I.LOT_STATUS
, NULL
, 1
, DECODE(NVL(S.REJECTED_IND
, 0)
, 1
, 0
, 1))
, SUM(I.LOCT_ONHAND) * DECODE(SIGN(M.EXPIRE_DATE - SYSDATE)
, -1
, 0
, 1) * DECODE(I.LOT_STATUS
, NULL
, 1
, NVL(S.ORDER_PROC_IND
, 0)) * DECODE(I.LOT_STATUS
, NULL
, 1
, DECODE(NVL(S.REJECTED_IND
, 0)
, 1
, 0
, 1))
, SUM(I.LOCT_ONHAND2) * DECODE(SIGN(M.EXPIRE_DATE - SYSDATE)
, -1
, 0
, 1) * DECODE(I.LOT_STATUS
, NULL
, 1
, NVL(S.ORDER_PROC_IND
, 0)) * DECODE(I.LOT_STATUS
, NULL
, 1
, DECODE(NVL(S.REJECTED_IND
, 0)
, 1
, 0
, 1))
, SUM(I.LOCT_ONHAND) * DECODE(SIGN(M.EXPIRE_DATE - SYSDATE)
, -1
, 0
, 1) * DECODE(I.LOT_STATUS
, NULL
, 1
, NVL(S.SHIPPING_IND
, 0)) * DECODE(I.LOT_STATUS
, NULL
, 1
, DECODE(NVL(S.REJECTED_IND
, 0)
, 1
, 0
, 1))
, SUM(I.LOCT_ONHAND2) * DECODE(SIGN(M.EXPIRE_DATE - SYSDATE)
, -1
, 0
, 1) * DECODE(I.LOT_STATUS
, NULL
, 1
, NVL(S.SHIPPING_IND
, 0)) * DECODE(I.LOT_STATUS
, NULL
, 1
, DECODE(NVL(S.REJECTED_IND
, 0)
, 1
, 0
, 1))
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
FROM IC_LOTS_MST M
, IC_LOTS_STS S
, IC_LOCT_INV I
WHERE I.LOCT_ONHAND <> 0
AND M.ITEM_ID = I.ITEM_ID
AND M.LOT_ID = I.LOT_ID
AND S.LOT_STATUS (+) = I.LOT_STATUS GROUP BY I.ITEM_ID
, I.WHSE_CODE
, I.LOT_ID
, M.QC_GRADE
, S.NETTABLE_IND
, I.LOT_STATUS
, S.PROD_IND
, S.ORDER_PROC_IND
, S.SHIPPING_IND
, S.REJECTED_IND
, M.EXPIRE_DATE

Columns