[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT item_no, lot_ctl, loct_ctl, grade_ctl, alloc_class, item_um, item_um2, dualum_ind,
lot_indivisible
FROM ic_item_mst
WHERE item_id = V_item_id;
SELECT loct_ctl
FROM ic_whse_mst
WHERE whse_code = V_whse_code;
SELECT 1
FROM sys.dual
WHERE EXISTS (SELECT 1
FROM ic_tran_pnd
WHERE line_id = V_line_id
AND (lot_id > 0 OR location <> P_default_loct)
AND delete_mark = 0
AND doc_type = 'OPSO'
AND trans_qty <> 0);
SELECT alloc_method, shelf_days, alloc_horizon,
alloc_type, lot_qty, partial_ind, prefqc_grade
FROM op_alot_prm
WHERE NVL(cust_id,0) = V_cust_id -- This enables us to open
-- the cursor when V_cust_id is null
AND alloc_class = V_alloc_class
AND delete_mark = 0;
SELECT lot_no, sublot_no, lot_id, lot_created ,
expire_date, qc_grade, location,
sum(loct_onhand) onhand_qty, sum(loct_onhand2) onhand_qty2,
sum(commit_qty) commit_qty, sum(commit_qty2) commit_qty2,
sum(loct_onhand) + sum(commit_qty) avail_qty,
sum(loct_onhand2) + sum(commit_qty2) avail_qty2,
sum(alloc_qty) as alloc_qty, sum(alloc_qty2) as alloc_qty2,
trans_id,count(*) numb_trans_line
FROM op_tran_tmp
WHERE session_id = P_session_id
AND item_id = V_item_id
AND whse_code = V_whse_code
AND expire_date > SYSDATE
GROUP BY lot_no, sublot_no, lot_id, lot_created,
trans_id,expire_date, qc_grade, location
ORDER BY DECODE(V_alloc_method, 0, lot_created, expire_date), qc_grade, DECODE(V_alloc_method, 0, expire_date, lot_created);
insert_temp_rows (V_item_id, V_whse_code, V_qc_grade,
V_trans_date);
INSERT INTO op_tran_tmp (session_id, doc_id, line_id, item_id, lot_no, sublot_no,
lot_id, lot_created, expire_date, qc_grade, whse_code, location,
alloc_qty, alloc_qty2)
VALUES (P_session_id, 1, 1, V_item_id, LotDetailsRec.lot_no, LotDetailsRec.sublot_no,
LotDetailsRec.lot_id, LotDetailsRec.lot_created, LotDetailsRec.expire_date,
LotDetailsRec.qc_grade, V_whse_code, LotDetailsRec.location,
X_alloc_qty, X_alloc_qty2);
PROCEDURE insert_temp_rows (V_item_id NUMBER, V_whse_code VARCHAR2, V_qc_grade VARCHAR2,
V_trans_date DATE) IS
X_Return_val number := 0;
INSERT INTO op_tran_tmp
(session_id,item_id, line_id, doc_id, lot_no, sublot_no,
lot_id, lot_created, expire_date, qc_grade, whse_code, location,
loct_onhand, loct_onhand2, commit_qty, commit_qty2, trans_id,
id_count, alloc_qty, alloc_qty2 )
SELECT
P_session_id,l.item_id, -1, -1, l.lot_no, l.sublot_no,
l.lot_id, l.lot_created, l.expire_date,
l.qc_grade, b.whse_code, b.location,
b.loct_onhand, b.loct_onhand2, 0,
0, 0, 0, 0, 0
FROM ic_lots_mst l, ic_loct_inv b, ic_lots_sts s
WHERE l.item_id = V_item_id
AND whse_code = V_whse_code
AND expire_date > V_trans_date
AND l.inactive_ind = 0
AND b.item_id = l.item_id
AND b.lot_id = l.lot_id
AND (V_qc_grade IS NULL OR l.qc_grade = V_qc_grade)
AND s.lot_status (+) = b.lot_status
AND nvl(s.order_proc_ind,1) = 1
AND nvl(s.rejected_ind,0) = 0
AND b.loct_onhand > 0 ;
INSERT INTO op_tran_tmp
(session_id,item_id, line_id, doc_id, lot_no, sublot_no,
lot_id, lot_created, expire_date, qc_grade, whse_code, location,
loct_onhand, loct_onhand2, commit_qty, commit_qty2, trans_id,
id_count, alloc_qty, alloc_qty2 )
SELECT
P_session_id,t.item_id, -1, -1, l.lot_no, l.sublot_no, t.lot_id, l.lot_created,
l.expire_date, l.qc_grade, t.whse_code, t.location, 0, 0, t.trans_qty,
t.trans_qty2, 0, 0, 0, 0
FROM ic_item_mst i, ic_lots_mst l, ic_tran_pnd t
WHERE t.item_id = V_item_id
AND whse_code = V_whse_code
AND expire_date > V_trans_date
AND l.item_id = t.item_id
AND l.inactive_ind = 0
AND t.lot_id = l.lot_id
AND (V_qc_grade IS NULL OR t.qc_grade = V_qc_grade)
AND t.delete_mark = 0
AND t.completed_ind = 0
AND t.trans_qty < 0
AND (t.lot_id <> 0 OR (i.lot_ctl = 0 AND i.loct_ctl > 0) )
AND l.item_id = i.item_id;
END insert_temp_rows;
DELETE FROM op_tran_tmp
WHERE session_id = p_session_id;