1 PACKAGE BODY GML_RELEASE_SHIPMENT_PKG AS
2 /* $Header: GMLRLSHB.pls 115.4 2002/11/05 19:01:11 pkanetka noship $ */
3
4 FUNCTION Check_negative_inv(v_bol_id NUMBER) RETURN NUMBER IS
5 l_loct_inv t_loct_inv_tbl;
6 l_tran_pnd t_loct_inv_tbl;
7
8 CURSOR Cur_get_pending(pbol_id NUMBER) IS
9 SELECT Item_id as item_id,
10 Whse_code as whse_code,
11 Lot_id as lot_id,
12 Location as location,
13 SUM(-Trans_qty) as qty,
14 0 as valid_flag
15 FROM ic_tran_pnd
16 WHERE doc_type = 'OPSO'
17 AND delete_mark <> 1
18 AND completed_ind <> 1
19 AND trans_qty <> 0
20 AND line_id in (SELECT line_id
21 FROM op_ordr_dtl
22 WHERE bol_id = pbol_id)
23 GROUP BY item_id,whse_code,lot_id,location
24 ORDER BY item_id,whse_code,lot_id,location;
25
26 CURSOR Cur_get_loct_inv(pbol_id NUMBER) IS
27 SELECT Item_id as item_id,
28 Whse_code as whse_code,
29 Lot_id as lot_id,
30 Location as location,
31 loct_onhand as qty,
32 0 as valid_flag
33 FROM ic_loct_inv
34 WHERE (item_id, whse_code, lot_id, location) in
35 ( SELECT distinct item_id,
36 whse_code,
37 lot_id,
38 location
39 FROM ic_tran_pnd
40 WHERE doc_type = 'OPSO'
41 AND delete_mark <> 1
42 AND completed_ind <> 1
43 AND trans_qty <> 0
44 AND line_id in ( SELECT line_id
45 FROM op_ordr_dtl
46 WHERE bol_id = pbol_id))
47 ORDER BY item_id,whse_code,lot_id,location;
48
49
50 /* Begin bug 1994824 - lswamy */
51 CURSOR Cur_get_noninv_ind(pitem_id ic_item_mst.item_id%TYPE) IS
52 SELECT noninv_ind
53 FROM ic_item_mst
54 WHERE item_id = pitem_id;
55 l_noninv_ind ic_item_mst.noninv_ind%TYPE;
56 /* End bug 1994824 */
57
58 l_counter NUMBER DEFAULT 1;
59 l_num_trans NUMBER DEFAULT 0;
60 l_num_onhand NUMBER DEFAULT 0;
61 l_loct_exists NUMBER DEFAULT 0;
62
63
64 BEGIN
65
66 OPEN Cur_get_pending(v_bol_id);
67 LOOP
68 FETCH Cur_get_pending INTO l_tran_pnd(l_counter);
69 EXIT WHEN Cur_get_pending%NOTFOUND;
70 l_counter := l_counter + 1;
71 END LOOP;
72 CLOSE Cur_get_pending;
73 l_num_trans := l_tran_pnd.COUNT;
74
75 l_counter := 1;
76
77 OPEN Cur_get_loct_inv(v_bol_id);
78 LOOP
79 FETCH Cur_get_loct_inv INTO l_loct_inv(l_counter);
80 EXIT WHEN Cur_get_loct_inv%NOTFOUND;
81 l_counter := l_counter + 1;
82 END LOOP;
83 CLOSE Cur_get_loct_inv;
84 l_num_onhand := l_loct_inv.COUNT;
85
86 /* For Checking Negative Inventory */
87 l_counter := 1;
88
89 FOR i IN 1..l_num_trans LOOP
90 l_loct_exists := 0;
91
92 OPEN cur_get_noninv_ind(l_tran_pnd(i).item_id);
93 FETCH cur_get_noninv_ind INTO l_noninv_ind;
94 CLOSE cur_get_noninv_ind;
95
96
97 -- For a noninventory item, we set the valid flag to zero
98 IF (l_noninv_ind = 1) THEN
99 l_tran_pnd(i).valid_flag:= 0;
100 l_counter := l_counter + 1;
101 ELSE
102
103 -- Loop to see if a record exists in IC_LOCT_INV
104 FOR j IN 1..l_num_onhand LOOP
105 IF ( (l_tran_pnd(i).item_id = l_loct_inv(j).item_id)
106 AND (l_tran_pnd(i).whse_code = l_loct_inv(j).whse_code)
107 AND (l_tran_pnd(i).lot_id = l_loct_inv(j).lot_id)
108 AND (l_tran_pnd(i).location = l_loct_inv(j).location)) THEN
109 l_loct_exists := l_loct_exists + 1;
110 END IF;
111 END LOOP;
112
113 IF (l_loct_exists = 0) THEN
114 l_tran_pnd(i).valid_flag:= 1;
115 l_counter := l_counter + 1;
116 END IF;
117
118 -- This loop checks if there is sufficient quantity in ic_tran_pnd
119 FOR j IN 1..l_num_onhand LOOP
120 IF ( (l_tran_pnd(i).item_id = l_loct_inv(j).item_id)
121 AND (l_tran_pnd(i).whse_code = l_loct_inv(j).whse_code)
122 AND (l_tran_pnd(i).lot_id = l_loct_inv(j).lot_id)
123 AND (l_tran_pnd(i).location = l_loct_inv(j).location)
124 AND (l_tran_pnd(i).trans_qty > l_loct_inv(j).trans_qty)) THEN
125 l_tran_pnd(i).valid_flag:= 1;
126 l_counter := l_counter + 1;
127 END IF;
128 END LOOP;
129 END IF; --noninv_ind=0
130
131 END LOOP; -- i Loop ends
132
133 FOR i IN 1..l_tran_pnd.COUNT LOOP
134 IF (l_tran_pnd(i).valid_flag = 1) THEN
135 RETURN(-1);
136 END IF;
137 END LOOP;
138 RETURN(0);
139
140 END Check_negative_inv;
141
142 END GML_RELEASE_SHIPMENT_PKG;
[Home] [Help]