« Hide

CREATE OR REPLACE VIEW IC_ITEM_INV_V
(ITEM_ID, LOT_NO, SUBLOT_NO, LOT_ID, LOT_STATUS,
 LOT_CREATED, EXPIRE_DATE, QC_GRADE, WHSE_CODE, LOCATION,
 LOCT_ONHAND, LOCT_ONHAND2, COMMIT_QTY, COMMIT_QTY2)
AS
SELECT l.item_id, l.lot_no, l.sublot_no, l.lot_id, s.lot_status,
          l.lot_created, l.expire_date, l.qc_grade, b.whse_code, b.LOCATION,
          b.loct_onhand, b.loct_onhand2, 0, 0
     FROM ic_lots_mst l, ic_loct_inv b, ic_lots_sts s
    WHERE l.item_id = b.item_id
      AND l.inactive_ind = 0
      AND l.lot_id = b.lot_id
      AND b.lot_status = s.lot_status(+)
      AND NVL (s.order_proc_ind, 1) = 1
      AND NVL (s.rejected_ind, 0) = 0
      AND b.loct_onhand > 0
   UNION ALL
   SELECT /*+ INDEX(t IC_TRAN_PNDI1) */ t.item_id, l.lot_no, l.sublot_no, t.lot_id, t.lot_status,
          l.lot_created, l.expire_date, l.qc_grade, t.whse_code, t.LOCATION,
          0, 0, t.trans_qty commit_qty, t.trans_qty2 commit_qty2
     FROM ic_lots_mst l, ic_tran_pnd t, ic_item_mst i
    WHERE i.item_id = l.item_id
      AND i.item_id = t.item_id
      AND l.inactive_ind = 0
      AND t.lot_id = l.lot_id
      AND t.delete_mark = 0
      AND t.completed_ind = 0
      AND t.trans_qty < 0
/



SELECT SUM (loct_onhand), SUM (loct_onhand2), SUM (commit_qty),
         SUM (commit_qty2), SUM (loct_onhand) + SUM (commit_qty), lot_no,
         sublot_no, lot_id, lot_status, lot_created, LOCATION, expire_date,
         qc_grade
    FROM xtdba.ic_item_inv_v_sil x
   WHERE item_id = 5125
     AND whse_code = '350'
     AND loct_onhand >= 0
     AND expire_date >
                    TO_DATE ('06-SEP-2007, 11:59:59', 'DD-MON-YYYY, HH:MI:SS')
     AND lot_id > 0
     AND LOCATION <> 'NONE'
GROUP BY lot_no,
         sublot_no,
         lot_id,
         lot_status,
         lot_created,
         LOCATION,
         expire_date,
         qc_grade
  HAVING SUM (loct_onhand) + SUM (commit_qty) > 0
ORDER BY lot_created

Show »

CREATE OR REPLACE VIEW IC_ITEM_INV_V (ITEM_ID, LOT_NO, SUBLOT_NO, LOT_ID, LOT_STATUS,  LOT_CREATED, EXPIRE_DATE, QC_GRADE, WHSE_CODE, LOCATION,  LOCT_ONHAND, LOCT_ONHAND2, COMMIT_QTY, COMMIT_QTY2) AS SELECT l.item_id, l.lot_no, l.sublot_no, l.lot_id, s.lot_status,           l.lot_created, l.expire_date, l.qc_grade, b.whse_code, b.LOCATION,           b.loct_onhand, b.loct_onhand2, 0, 0      FROM ic_lots_mst l, ic_loct_inv b, ic_lots_sts s     WHERE l.item_id = b.item_id       AND l.inactive_ind = 0       AND l.lot_id = b.lot_id       AND b.lot_status = s.lot_status(+)       AND NVL (s.order_proc_ind, 1) = 1       AND NVL (s.rejected_ind, 0) = 0       AND b.loct_onhand > 0    UNION ALL    SELECT /*+ INDEX(t IC_TRAN_PNDI1) */ t.item_id, l.lot_no, l.sublot_no, t.lot_id, t.lot_status,           l.lot_created, l.expire_date, l.qc_grade, t.whse_code, t.LOCATION,           0, 0, t.trans_qty commit_qty, t.trans_qty2 commit_qty2      FROM ic_lots_mst l, ic_tran_pnd t, ic_item_mst i     WHERE i.item_id = l.item_id       AND i.item_id = t.item_id       AND l.inactive_ind = 0       AND t.lot_id = l.lot_id       AND t.delete_mark = 0       AND t.completed_ind = 0       AND t.trans_qty < 0 / SELECT SUM (loct_onhand), SUM (loct_onhand2), SUM (commit_qty),          SUM (commit_qty2), SUM (loct_onhand) + SUM (commit_qty), lot_no,          sublot_no, lot_id, lot_status, lot_created, LOCATION, expire_date,          qc_grade     FROM xtdba.ic_item_inv_v_sil x    WHERE item_id = 5125      AND whse_code = '350'      AND loct_onhand >= 0      AND expire_date >                     TO_DATE ('06-SEP-2007, 11:59:59', 'DD-MON-YYYY, HH:MI:SS')      AND lot_id > 0      AND LOCATION <> 'NONE' GROUP BY lot_no,          sublot_no,          lot_id,          lot_status,          lot_created,          LOCATION,          expire_date,          qc_grade   HAVING SUM (loct_onhand) + SUM (commit_qty) > 0 ORDER BY lot_created