[Home] [Help]
PACKAGE BODY: APPS.GMI_PURGE_EMPTY_BAL_PKG
Source
1 PACKAGE BODY GMI_PURGE_EMPTY_BAL_PKG AS
2 /* $Header: GMIPEBLB.pls 120.0 2005/05/25 15:49:20 appldev noship $ */
3 PROCEDURE Purge_empty_balance( err_buf OUT NOCOPY VARCHAR2,
4 ret_code OUT NOCOPY VARCHAR2,
5 p_item_from IN VARCHAR2, /* Bug 3377672 Start */
6 p_item_to IN VARCHAR2,
7 p_whse_from IN VARCHAR2,
8 p_whse_to IN VARCHAR2,
9 p_inv_class IN VARCHAR2,
10 p_lot_ind IN NUMBER DEFAULT 0, -- 0-No; 1-Yes
11 p_purge_precision IN NUMBER DEFAULT 9,
12 p_criteria_id IN NUMBER DEFAULT 0 /* Bug 3377672 End */
13 ) IS
14
15 CURSOR Cur_get_purg_info(l_criteria_id NUMBER) IS
16 SELECT *
17 FROM ic_purg_prm
18 WHERE criteria_id = l_criteria_id
19 AND process_ind = 1
20 AND delete_mark = 0;
21
22
23
24 l_del_count NUMBER := 0;
25 l_zero_count NUMBER := 0;
26 l_sdate DATE;
27 l_where_clause VARCHAR2 (500);
28 l_where1 VARCHAR2 (500);
29 --l_where2 VARCHAR2 (500); --Created the variable. BUG#2552369
30 l_loct_onhand NUMBER;
31 l_cursor_id INTEGER;
32 l_dummy INTEGER;
33 l_select_stmt LONG;
34 l_item_no ic_item_mst.item_no%TYPE;
35 l_whse_code ic_whse_mst.whse_code%type;
36 l_location ic_loct_mst.location%type;
37 l_row_id VARCHAR2(100);
38 l_lot_no ic_lots_mst.lot_no%type;
39 l_from_item ic_item_mst.item_no%type;
40 l_to_item ic_item_mst.item_no%type;
41 l_from_whse ic_whse_mst.whse_code%type;
42 l_to_whse ic_whse_mst.whse_code%type;
43 l_inv_class VARCHAR2(10);
44 l_lot_ind NUMBER;
45 l_purg_rec Cur_get_purg_info%rowtype;
46 l_loct_onhand2 NUMBER;
47 l_qchold_res_code VARCHAR2(10);
48 l_lot_status VARCHAR2(10);
49 l_default_lot VARCHAR2(32);
50 invalid_arguments EXCEPTION;
51 l_purge_precision NUMBER; /* 3377672 Purge Empty Balances Enh -- Added the variable*/
52
53 BEGIN
54
55
56 /* 3377672 Purge Empty Balances Enh -- Added IF Condition */
57 /* Query the ic_purg_prm only if this package is called from the Purge Empty Balances FORM. */
58
59 IF (p_criteria_id > 0) THEN
60 OPEN Cur_get_purg_info(p_criteria_id);
61 FETCH Cur_get_purg_info INTO l_purg_rec;
62 IF (Cur_get_purg_info%NOTFOUND) THEN
63 CLOSE Cur_get_purg_info;
64 RAISE invalid_arguments;
65 RETURN;
66 END IF;
67 CLOSE Cur_get_purg_info;
68
69 END IF; /* 3377672 Purge Empty Balances Enh. Added End If */
70
71
72
73 l_default_lot := FND_PROFILE.VALUE('IC$DEFAULT_LOT');
74
75 l_from_item := NVL(l_purg_rec.itemno_from,p_item_from); /* Bug 3377672 Purge Empty Balances Start */
76 l_to_item := NVL(l_purg_rec.itemno_thru,p_item_to);
77 l_from_whse := NVL(l_purg_rec.whse_from,p_whse_from);
78 l_to_whse := NVL(l_purg_rec.whse_thru,p_whse_to);
79 l_inv_class := NVL(l_purg_rec.inv_class,p_inv_class);
80 l_lot_ind := NVL(l_purg_rec.lot_ind,NVL(p_lot_ind,0));
81 l_purge_precision := NVL(l_purg_rec.purge_precision,p_purge_precision); /* Bug 3377672 Purge Empty Balances End */
82
83
84 l_where_clause := ' b.item_id ';
85
86 /*===================================================
87 BUG#2935108 - added the following where clauses
88 ==================================================*/
89 l_where_clause := l_where_clause || ' AND item_no between nvl(:fitm,item_no) and nvl(:titm,item_no)';
90
91 l_where_clause := l_where_clause || ' AND a.whse_code between nvl(:fwh,a.whse_code) and nvl(:twh,a.whse_code)';
92
93 l_where_clause := l_where_clause || ' AND nvl(b.inv_class,'' '') = nvl(:inclass, nvl(b.inv_class,'' ''))';
94
95 l_where_clause := l_where_clause || ' AND b.lot_ctl = decode(:lotind,1,1,b.lot_ctl)';
96
97 --BEGIN BUG#2552369 V. Ajay Kumar
98 --Commented the l_where1 clause below and modified it accordingly.
99 --l_where1 := l_where1 || ' AND a.loct_onhand >= 0 AND a.loct_onhand <= .000000001 ';
100
101
102
103 /* Dinesh 3377672 - Purge Empty Balances Enh. -- Start
104 Depending on the Purge_precision select the rows eligible for purge, instead of hardcoding to 9 decimal precisions
105 If loct_onhand quantity is zero or less than the 0.1^purge_precision then it is eligible for purge*/
106
107 -- l_where1 := l_where1 || ' AND (a.loct_onhand = 0 OR abs(a.loct_onhand) <= .000000001)';
108 l_where1 := l_where1 || ' AND (a.loct_onhand = 0 OR abs(a.loct_onhand) <= POWER(0.1,:purge_precision))';
109
110 /* Dinesh 3377672 - Purge Empty Balances Enh. -- End */
111
112
113
114 --Do not purge the empty balances if the item is used in Inventory Transfers.
115 --Bug#3315228 Ramakrishna Commented the l_where2 to purge the empty balances even the item
116 --is used in Inventory Transfers.
117 /* l_where2 := l_where2 || ' AND NOT EXISTS (SELECT item_id FROM IC_TRAN_PND p '||
118 ' WHERE doc_type = '||'''XFER''' ||
119 ' AND a.whse_code = p.whse_code '||
120 ' AND a.lot_id = p.lot_id '||
121 ' AND a.location = p.location '||
122 ' AND a.item_id = p.item_id '||
123 ' AND p.delete_mark = 0) '; */
124
125 --Add l_where2 to the l_select_stmt.
126 --Bug#3315228 Ramakrishna the l_where2 is taken out from the select statement
127 l_select_stmt := 'SELECT b.item_no,a.whse_code,a.location, ' ||
128 ' a.loct_onhand,a.rowid, a.loct_onhand2,a.qchold_res_code, ' ||
129 ' a.lot_status,decode(c.lot_no,'||''''||l_default_lot||''''||',NULL,c.lot_no) lot_no ' ||
130 ' FROM ic_loct_inv a,ic_item_mst b,ic_lots_mst c ' ||
131 ' WHERE a.item_id = '|| l_where_clause || l_where1
132 ||' and a.lot_id = c.lot_id and b.item_id = c.item_id ';
133 --END BUG#2552369
134
135 l_cursor_id := DBMS_SQL.OPEN_CURSOR;
136
137 DBMS_SQL.PARSE(l_cursor_id,l_select_stmt,DBMS_SQL.V7);
138
139 DBMS_SQL.BIND_VARIABLE(l_cursor_id,':fitm',l_from_item);
140 DBMS_SQL.BIND_VARIABLE(l_cursor_id,':titm',l_to_item);
141 DBMS_SQL.BIND_VARIABLE(l_cursor_id,':fwh',l_from_whse);
142 DBMS_SQL.BIND_VARIABLE(l_cursor_id,':twh',l_to_whse);
143 DBMS_SQL.BIND_VARIABLE(l_cursor_id,':inclass',l_inv_class);
144 DBMS_SQL.BIND_VARIABLE(l_cursor_id,':lotind',l_lot_ind);
145 DBMS_SQL.BIND_VARIABLE(l_cursor_id,':purge_precision',l_purge_precision); /* Dinesh 3377672 - Purge Empty Balances Enh. */
146
147 DBMS_SQL.DEFINE_COLUMN(l_cursor_id,1,l_item_no,32);
148 DBMS_SQL.DEFINE_COLUMN(l_cursor_id,2,l_whse_code,4);
149 DBMS_SQL.DEFINE_COLUMN(l_cursor_id,3,l_location,32);
150 DBMS_SQL.DEFINE_COLUMN(l_cursor_id,4,l_loct_onhand);
151 DBMS_SQL.DEFINE_COLUMN(l_cursor_id,5,l_row_id,100);
152 DBMS_SQL.DEFINE_COLUMN(l_cursor_id,6,l_loct_onhand2);
153 DBMS_SQL.DEFINE_COLUMN(l_cursor_id,7,l_qchold_res_code,10);
154 DBMS_SQL.DEFINE_COLUMN(l_cursor_id,8,l_lot_status,10);
155 DBMS_SQL.DEFINE_COLUMN(l_cursor_id,9,l_lot_no,32);
156
157
158 l_dummy := DBMS_SQL.EXECUTE(l_cursor_id);
159
160
161 WHILE (DBMS_SQL.FETCH_ROWS(l_cursor_id) <> 0) LOOP
162 BEGIN
163 DBMS_SQL.COLUMN_VALUE(l_cursor_id,1,l_item_no);
164 DBMS_SQL.COLUMN_VALUE(l_cursor_id,2,l_whse_code);
165 DBMS_SQL.COLUMN_VALUE(l_cursor_id,3,l_location);
166 DBMS_SQL.COLUMN_VALUE(l_cursor_id,4,l_loct_onhand);
167 DBMS_SQL.COLUMN_VALUE(l_cursor_id,5,l_row_id);
168 DBMS_SQL.COLUMN_VALUE(l_cursor_id,6,l_loct_onhand2);
169 DBMS_SQL.COLUMN_VALUE(l_cursor_id,7,l_qchold_res_code);
170 DBMS_SQL.COLUMN_VALUE(l_cursor_id,8,l_lot_status);
171 DBMS_SQL.COLUMN_VALUE(l_cursor_id,9,l_lot_no);
172
173 --BEGIN BUG#2552369 V. Ajay Kumar
174 --Commented the additional WHERE clause from the DELETE statement.
175
176 DELETE
177 FROM ic_loct_inv
178 WHERE rowid = l_row_id;
179 --AND loct_onhand >= 0
180 --AND loct_onhand <= .000000001;
181
182 --END BUG#2552369
183
184 IF (SQL%FOUND) THEN
185 commit;
186 l_del_count := l_del_count + 1;
187
188 IF (l_loct_onhand = 0) THEN
189 l_zero_count := l_zero_count + 1;
190 END IF;
191 FND_MESSAGE.SET_NAME('GMI','GMI_CONC_REQUEST_PURGE_LOG');
192 FND_MESSAGE.SET_TOKEN('ITEM_NO',l_item_no);
193 FND_MESSAGE.SET_TOKEN('LOT_NO',l_lot_no);
194 FND_MESSAGE.SET_TOKEN('WHSE',l_whse_code);
195 FND_MESSAGE.SET_TOKEN('LOC',l_location);
196 FND_MESSAGE.SET_TOKEN('QTY',to_char(l_loct_onhand));
197 FND_FILE.PUT(FND_FILE.LOG,FND_MESSAGE.GET);
198 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
199 END IF;
200 END;
201 END LOOP;
202 DBMS_SQL.CLOSE_CURSOR(l_cursor_id);
203
204 /******** 3377672 Purge Empty Balances Enhancement - Start *********/
205 /* Added IF Condition -- Update the ic_purg_prm only if it is called from Purge Empty Balances Form*/
206
207 IF (p_criteria_id > 0) THEN
208
209 UPDATE ic_purg_prm
210 SET run_date = sysdate,
211 process_ind = 2,
212 deleted_rowcount = l_del_count,
213 zero_rowcount = l_zero_count
214 WHERE criteria_id = p_criteria_id;
215
216 COMMIT;
217
218 END IF ; -- Added END IF
219 /******** 3377672 Purge Empty Balances Enhancment - End********/
220
221 EXCEPTION
222 WHEN invalid_arguments THEN
223 FND_MESSAGE.SET_NAME('GMI','GMI_CONC_REQUEST_INVALID_ARG');
224 FND_MESSAGE.SET_TOKEN('C',to_char(p_criteria_id));
225 FND_FILE.PUT(FND_FILE.LOG,FND_MESSAGE.GET);
226 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
227 WHEN OTHERS THEN
228 FND_FILE.PUT(FND_FILE.LOG,SQLERRM);
229 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
230 raise;
231 END Purge_empty_balance;
232 END GMI_PURGE_EMPTY_BAL_PKG;