VIEW: APPS.IC_SUMM_INV_PENDING_V

Source

View Text - Preformatted

SELECT t.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_lots_mst m , ic_item_mst i , ic_tran_pnd t WHERE t.completed_ind = 0 and t.delete_mark = 0 and t.trans_qty <> 0 and m.item_id = t.item_id and m.lot_id = t.lot_id and i.item_id = t.item_id and i.noninv_ind = 0 group by t.item_id , t.whse_code , t.qc_grade , t.doc_type , t.line_type

View Text - HTML Formatted

SELECT T.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_LOTS_MST M
, IC_ITEM_MST I
, IC_TRAN_PND T
WHERE T.COMPLETED_IND = 0
AND T.DELETE_MARK = 0
AND T.TRANS_QTY <> 0
AND M.ITEM_ID = T.ITEM_ID
AND M.LOT_ID = T.LOT_ID
AND I.ITEM_ID = T.ITEM_ID
AND I.NONINV_IND = 0 GROUP BY T.ITEM_ID
, T.WHSE_CODE
, T.QC_GRADE
, T.DOC_TYPE
, T.LINE_TYPE