VIEW: APPS.IC_SUMM_INV_INT_V

Source

View Text - Preformatted

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

View Text - HTML Formatted

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