VIEW: APPS.IC_SUMM_INV_ONHAND_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, 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

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
, 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