View: IC_SUMM_INV_INT_V

Product:	GMI - Process Manufacturing Inventory
Description:	Private View
Implementation/DBA Data:	APPS.IC_SUMM_INV_INT_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
, DECODE(NVL(S.REJECTED_IND
, 0)
, 1
, 0
, 1))
, SUM(I.LOCT_ONHAND2) * 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
, I.LOT_STATUS
, S.PROD_IND
, M.EXPIRE_DATE
, S.ORDER_PROC_IND
, S.SHIPPING_IND
, S.REJECTED_IND UNION ALL SELECT I.ITEM_ID
, T.WHSE_CODE
, T.QC_GRADE
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, DECODE(T.DOC_TYPE
, 'PORD'
, SUM(T.TRANS_QTY))
, DECODE(T.DOC_TYPE
, 'PORD'
, SUM(NVL(T.TRANS_QTY2
, 0)))
, DECODE(T.DOC_TYPE
, 'PROD'
, SUM(T.TRANS_QTY)* DECODE(T.LINE_TYPE
, -1
, 0
, 1)
, 0)
, DECODE(T.DOC_TYPE
, 'PROD'
, SUM(NVL(T.TRANS_QTY2
, 0)) *DECODE(T.LINE_TYPE
, -1
, 0
, 1)
, 0)
, DECODE(T.DOC_TYPE
, 'OMSO'
, SUM(T.TRANS_QTY)
, 'OPSO'
, SUM(T.TRANS_QTY)
, 0)
, DECODE(T.DOC_TYPE
, 'OMSO'
, SUM(NVL(T.TRANS_QTY2
, 0))
, 'OPSO'
, SUM(NVL(T.TRANS_QTY2
, 0))
, 0)
, DECODE(T.DOC_TYPE
, 'PROD'
, SUM(T.TRANS_QTY)* DECODE(T.LINE_TYPE
, -1
, 1
, 0)
, 0)
, DECODE(T.DOC_TYPE
, 'PROD'
, SUM(NVL(T.TRANS_QTY2
, 0))*DECODE(T.LINE_TYPE
, -1
, 1
, 0)
, 0)
, DECODE(T.DOC_TYPE
, 'XFER'
, SUM(T.TRANS_QTY)
, 0)
, DECODE(T.DOC_TYPE
, 'XFER'
, SUM(NVL(T.TRANS_QTY2
, 0))
, 0)
FROM IC_ITEM_MST_B I
, IC_TRAN_PND T
WHERE T.COMPLETED_IND = 0
AND T.DELETE_MARK = 0
AND T.TRANS_QTY <> 0
AND I.ITEM_ID = T.ITEM_ID
AND I.NONINV_IND = 0
AND T.DOC_TYPE IN ('PORD'
, 'PROD'
, 'OMSO'
, 'XFER'
, 'OPSO') GROUP BY I.ITEM_ID
, T.WHSE_CODE
, T.QC_GRADE
, T.DOC_TYPE
, T.LINE_TYPE

Columns