1 PACKAGE BODY GMIUTILS AS
2 /* $Header: gmiutilb.pls 120.0 2005/05/25 16:00:37 appldev noship $
3 /* +==========================================================================+
4 | PROCEDURE NAME |
5 | get_doc_no |
6 | |
7 | USAGE |
8 | This will get the doc no from sy_docs_mst and commit the no so that |
9 | there is no lock on the table. |
10 | It is a AUTONOMOUS_TRANSACTION. will commit before the main |
11 | transaction completes. |
12 | |
13 | RETURNS |
14 | Via x_ OUT parameters |
15 | |
16 | HISTORY |
17 | Created Jalaj Srivastava/Joe DiIorio |
18 | Sastry 08/27/2003 BUG#3071034 |
19 | Added a new function get_inventory_item_id |
20 | Sastry 10/29/2003 BUG#3197801 |
21 | Modified the function get_inventory_item_id so that inventory_item_id |
22 | is fetched only once if this function is called for the same item. |
23 | Teresa Wong 6/7/2004 B3415691 - Enhancement for Serono |
24 | Added procedures to set and restore global var for profile |
25 | GMI: Allow Negative Inventory. Added functions to recheck |
26 | negative inventory and lot status. |
27 | Teresa Wong 9/10/04 B3862819 - Modified lot_status_check for OMSO txns.|
28 +==========================================================================+
29 */
30 FUNCTION get_doc_no
31 ( x_return_status OUT NOCOPY VARCHAR2
32 , x_msg_count OUT NOCOPY NUMBER
33 , x_msg_data OUT NOCOPY VARCHAR2
34 , p_doc_type IN sy_docs_seq.doc_type%TYPE
35 , p_orgn_code IN sy_docs_seq.orgn_code%TYPE
36 ) RETURN VARCHAR2 IS
37 PRAGMA AUTONOMOUS_TRANSACTION;
38 l_doc_no VARCHAR2(10);
39
40 BEGIN
41
42 SAVEPOINT get_doc_no;
43
44 x_return_status :=FND_API.G_RET_STS_SUCCESS;
45
46 l_doc_no := GMA_GLOBAL_GRP.Get_doc_no (p_doc_type,p_orgn_code);
47
48 COMMIT;
49
50 return l_doc_no;
51
52 FND_MSG_PUB.Count_AND_GET
53 (p_count => x_msg_count, p_data => x_msg_data);
54
55 EXCEPTION
56
57 WHEN FND_API.G_EXC_ERROR THEN
58 ROLLBACK to get_doc_no;
59 x_return_status := FND_API.G_RET_STS_ERROR;
60 FND_MSG_PUB.Count_AND_GET
61 (p_count => x_msg_count, p_data => x_msg_data);
62
63 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
64 ROLLBACK to get_doc_no;
65 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
66 FND_MSG_PUB.Count_AND_GET
67 (p_count => x_msg_count, p_data => x_msg_data);
68
69 WHEN OTHERS THEN
70 ROLLBACK to get_doc_no;
71 IF (SQLCODE IS NOT NULL) THEN
72 FND_MESSAGE.SET_NAME('GMI','GMI_DXFR_SQL_ERROR');
73 FND_MESSAGE.SET_TOKEN('ERRCODE',SQLCODE);
74 FND_MESSAGE.SET_TOKEN('ERRM',SQLERRM);
75 FND_MSG_PUB.Add;
76 END IF;
77 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
78 FND_MSG_PUB.Count_AND_GET
79 (p_count => x_msg_count, p_data => x_msg_data);
80
81 END get_doc_no;
82
83 -- BEGIN BUG#3071034 Sastry
84 FUNCTION get_inventory_item_id
85 ( p_item_no IN varchar2
86 , p_reset_flag IN BOOLEAN DEFAULT FALSE
87 ) RETURN NUMBER IS
88 CURSOR get_inventory_item_id IS
89 SELECT inventory_item_id
90 FROM mtl_system_items_b
91 WHERE segment1 = P_item_no and
92 ROWNUM = 1;
93 l_inventory_item_id NUMBER;
94
95 BEGIN
96 IF P_reset_flag = TRUE THEN
97 GMIUTILS.x_inventory_item_id := NULL;
98 GMIUTILS.x_item_no := NULL;
99 END IF;
100 -- BUG#3197801 Sastry
101 -- Added the OR condition
102 IF GMIUTILS.x_inventory_item_id IS NULL OR GMIUTILS.x_item_no<> p_item_no THEN
103 OPEN get_inventory_item_id;
104 FETCH get_inventory_item_id INTO l_inventory_item_id ;
105 CLOSE get_inventory_item_id;
106 GMIUTILS.x_inventory_item_id := l_inventory_item_id;
107 GMIUTILS.x_item_no := p_item_no; -- BUG#3197801 Sastry
108 END IF;
109 RETURN GMIUTILS.x_inventory_item_id;
110 END get_inventory_item_id;
111 -- END BUG#3071034
112
113 /* +=========================================================================+
114 | PROCEDURE NAME |
115 | Set_allow_neg_inv |
116 | |
117 | USAGE |
118 | Used to set G_allow_neg_inv to 1 (allow) |
119 | Can be called from ICCNVED before batch update gme api. |
120 | |
121 | PARAMETERS |
122 | None |
123 | |
124 | RETURNS |
125 | global var updated |
126 | |
127 | HISTORY |
128 | Teresa Wong 6/2/2004 B3415691 |
129 | Enhancement for Serono. |
130 +=========================================================================+
131 */
132 PROCEDURE set_allow_neg_inv
133 IS
134 BEGIN
135
136 G_ALLOW_NEG_INV := 1;
137
138 END set_allow_neg_inv;
139
140 /* +=========================================================================+
141 | PROCEDURE NAME |
142 | restore_allow_neg_inv |
143 | |
144 | USAGE |
145 | Restore global variable G_allow_neg_inv to the profile |
146 | IC$ALLOWNEGINV. Can be called from ICCNVED after batch update. |
147 | |
148 | DESCRIPTION |
149 | Used to restore global variable G_allow_neg_inv to the profile |
150 | IC$ALLOWNEGINV. |
151 | |
152 | PARAMETERS |
153 | None |
154 | |
155 | RETURNS |
156 | global var updated |
157 | |
158 | HISTORY |
159 | Teresa Wong 6/2/2004 B3415691 |
160 | Enhancement for Serono. |
161 +=========================================================================+
162 */
163 PROCEDURE restore_allow_neg_inv
164 IS
165 BEGIN
166
167 G_ALLOW_NEG_INV := NVL(fnd_profile.value('IC$ALLOWNEGINV'), 0);
168
169 END restore_allow_neg_inv;
170
171 /* +=========================================================================+
172 | PROCEDURE NAME |
173 | neg_inv_check |
174 | |
175 | USAGE |
176 | Recheck negative inventory balance at the time of save. |
177 | If transaction causes inventory to go negative in primary or |
178 | secondary quantity, then report error. |
179 | |
180 | PARAMETERS |
181 | item id, whse code, lot id, location, transaction primary quantity, |
182 | secondary quantity |
183 | |
184 | RETURNS |
185 | True if check passes |
186 | False if check fails |
187 | error on message stack if check fails |
188 | |
189 | HISTORY |
190 | Teresa Wong 6/7/2004 B3415691 |
191 | Enhancement for Serono. |
192 +=========================================================================+
193 */
194 FUNCTION neg_inv_check
195 (
196 p_item_id IN NUMBER,
197 p_whse_code IN VARCHAR2,
198 p_lot_id IN NUMBER,
199 p_location IN VARCHAR2,
200 p_qty IN NUMBER,
201 p_qty2 IN NUMBER
202 )
203 RETURN BOOLEAN
204 IS
205 l_onhand NUMBER := 0;
206 l_onhand2 NUMBER := 0;
207
208 Cursor get_balance (
209 v_item_id IN NUMBER,
210 v_whse IN VARCHAR2,
211 v_lot_id IN NUMBER,
212 v_location IN VARCHAR2) IS
213 SELECT loct_onhand, loct_onhand2
214 FROM ic_loct_inv
215 WHERE item_id = v_item_id
216 AND whse_code = v_whse
217 AND lot_id = v_lot_id
218 AND location = v_location;
219
220 BEGIN
221 IF G_allow_neg_inv = 0 THEN
222 OPEN get_balance(
223 p_item_id,
224 p_whse_code,
225 p_lot_id,
226 p_location);
227 FETCH get_balance into l_onhand, l_onhand2;
228 CLOSE get_balance;
229
230 /* If transaction causes inventory to go negative in
231 either primary or secondary qty, then report error */
232 IF ( (l_onhand + p_qty) < 0 OR (l_onhand2 + p_qty2) < 0 )THEN
233 FND_MESSAGE.SET_NAME('GMI','IC_INVQTYNEG');
234 FND_MSG_PUB.Add;
235 RETURN FALSE;
236 END IF;
237 END IF;
238
239 RETURN TRUE;
240
241 EXCEPTION
242
243 WHEN OTHERS THEN
244
245 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME
246 , 'neg_inv_check');
247 RETURN FALSE;
248
249 END neg_inv_check;
250
251 /* +=========================================================================+
252 | PROCEDURE NAME |
253 | lot_status_check |
254 | |
255 | USAGE |
256 | Recheck lot status of a transaction at the time of save. |
257 | 1) If the default status and lot status of the transaction are not |
258 | allowed according to GMI: Move Different Status profile, then report |
259 | error. |
260 | 2) If the lot status is not valid for the document type of the |
261 | transaction, then report error. |
262 | |
263 | PARAMETERS |
264 | item id, whse code, lot_id, location, |
265 | document type, qty, and lot status of the transaction |
266 | |
267 | RETURNS |
268 | True if check passes |
269 | False if check fails |
270 | error on message stack if check fails |
271 | |
272 | HISTORY |
273 | Teresa Wong 6/1/2004 B3415691 |
274 | Enhancement for Serono. |
275 | Teresa Wong 9/3/2004 B3862819 |
276 | Modified the check for OMSO doc. |
277 +=========================================================================+
278 */
279 FUNCTION lot_status_check
280 (
281 p_item_id IN NUMBER,
282 p_whse_code IN VARCHAR2,
283 p_lot_id IN NUMBER,
284 p_location IN VARCHAR2,
285 p_doc_type IN VARCHAR2,
286 p_line_type IN NUMBER,
287 p_trans_qty IN NUMBER,
288 p_lot_status IN VARCHAR2
289 )
290 RETURN BOOLEAN
291 IS
292
293 l_item_mst_rec ic_item_mst%ROWTYPE;
294 l_loct_inv_rec ic_loct_inv%ROWTYPE;
295 l_lot_no ic_lots_mst.lot_no%TYPE;
296 l_sublot_no ic_lots_mst.sublot_no%TYPE;
297 l_sts_ctl NUMBER;
298 l_net NUMBER;
299 l_ord NUMBER;
300 l_ship NUMBER;
301 l_prod NUMBER;
302 l_rej NUMBER;
303 l_move_diff_sts NUMBER(5);
304 l_valid_move_diff_sts BOOLEAN := FALSE;
305 l_valid_sts_doc BOOLEAN := FALSE;
306
307 Cursor get_lots_ind (V_lot_sts IN VARCHAR2) IS
308 SELECT
309 nettable_ind, order_proc_ind, shipping_ind, prod_ind, rejected_ind
310 FROM
311 ic_lots_sts
312 WHERE
313 lot_status = V_lot_sts;
314
315 Cursor get_lot_no(V_lot_id IN NUMBER) IS
316 SELECT
317 lot_no, sublot_no
318 FROM
319 ic_lots_mst
320 WHERE
321 lot_id = V_lot_id;
322
323 BEGIN
324 /* fetch item details */
325 l_item_mst_rec.item_id := p_item_id;
326 IF NOT gmivdbl.ic_item_mst_select (
327 p_ic_item_mst_row => l_item_mst_rec,
328 x_ic_item_mst_row => l_item_mst_rec
329 ) THEN
330 RETURN FALSE;
331 END IF;
332
333 /* item is status controlled, do the checks */
334 IF (l_item_mst_rec.status_ctl = 1) THEN
335 /* fetch location inventory details */
336 gmigutl.get_loct_inv (
337 p_item_id => p_item_id,
338 p_whse_code => p_whse_code,
339 p_lot_id => p_lot_id,
340 p_location => p_location,
341 x_ic_loct_inv_row => l_loct_inv_rec
342 );
343
344 IF (p_doc_type = 'PROD') AND (p_trans_qty > 0) THEN
345 /* begin move diff status check for production */
346 l_move_diff_sts := FND_PROFILE.VALUE('IC$MOVEDIFFSTAT');
347
348 /* check default status and lot status to see if they
349 are allowed per GMI: Move Different Status profile */
350 IF l_move_diff_sts = 0 THEN
351 IF (l_loct_inv_rec.loct_onhand IS NULL) OR
352 (NVL (p_lot_status, ' ') = NVL (l_loct_inv_rec.lot_status, ' ')) THEN
353 l_valid_move_diff_sts := TRUE;
354 END IF;
355 ELSIF l_move_diff_sts = 1 THEN
356 l_valid_move_diff_sts := TRUE;
357 ELSIF l_move_diff_sts = 2 THEN
358 IF (NVL (l_loct_inv_rec.loct_onhand, 0) = 0) OR
359 (NVL (p_lot_status, ' ') = NVL (l_loct_inv_rec.lot_status, ' ')) THEN
360 l_valid_move_diff_sts := TRUE;
361 END IF;
362 END IF;
363
364 /* If the lot status is not valid per profile, then
365 report error */
366 IF NOT l_valid_move_diff_sts THEN
367 OPEN get_lot_no(l_loct_inv_rec.lot_id);
368 FETCH get_lot_no INTO l_lot_no,l_sublot_no;
369 CLOSE get_lot_no;
370
371 FND_MESSAGE.SET_NAME('GMI','IC_API_MOVE_STATUS_ERR');
372 FND_MESSAGE.SET_TOKEN('ITEM_NO',l_item_mst_rec.item_no);
373 FND_MESSAGE.SET_TOKEN('LOT_NO',l_lot_no);
374 FND_MESSAGE.SET_TOKEN('SUBLOT_NO',l_sublot_no);
375 FND_MSG_PUB.Add;
376 RETURN FALSE;
377 END IF;
378 END IF; /* end of move diff sts check */
379
380 /* check if status is valid for the doc type */
381 IF (l_loct_inv_rec.lot_status IS NULL) THEN
382 IF (p_doc_type = 'CREI') OR (p_doc_type = 'CRER') THEN
383 l_valid_sts_doc := TRUE;
384 END IF;
385 END IF;
386
387 /* examine item's default lot status if onhand not available */
388 IF (l_loct_inv_rec.lot_status IS NULL) THEN
389 OPEN get_lots_ind(p_lot_status);
390 ELSE
391 OPEN get_lots_ind(l_loct_inv_rec.lot_status);
392 END IF;
393 FETCH get_lots_ind into l_net, l_ord, l_ship, l_prod, l_rej;
394 IF (get_lots_ind%NOTFOUND) THEN
395 CLOSE get_lots_ind;
396 ELSE
397 CLOSE get_lots_ind;
398
399 IF (p_doc_type = 'PROD') THEN
400 IF ( p_line_type IN (1, 2) AND l_rej = 0 ) THEN
401 l_valid_sts_doc := TRUE;
402 ELSIF ( p_line_type = -1 AND l_prod = 1 AND l_rej = 0 ) THEN
403 l_valid_sts_doc := TRUE;
404 END IF;
405 ELSIF (p_doc_type = 'OMSO') THEN
406 -- TKW B3862819 Lot status is valid for line type of
407 -- 1 - internal order and 2 - drop ship
408 -- irrespective of actual lot status for OMSO txn.
409 -- Order flag is not required to be 1 for
410 -- line type of 0.
411
412 IF ( p_line_type = 0 AND l_ship = 1 AND l_rej = 0 )
413 OR ( p_line_type > 0) THEN
414 l_valid_sts_doc := TRUE;
415 END IF;
416 ELSE
417 l_valid_sts_doc := TRUE;
418 END IF;
419 END IF;
420
421 /* If the lot status is not valid for the document type
422 of the transaction, then report error */
423 IF NOT l_valid_sts_doc THEN
424 FND_MESSAGE.SET_NAME('GMI','IC_INVLOTSTST');
425 FND_MSG_PUB.Add;
426 RETURN FALSE;
427 END IF;
428 END IF;
429
430 RETURN TRUE;
431
432 EXCEPTION
433
434 WHEN OTHERS THEN
435 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME
436 , 'Lot_status_check');
437 RETURN FALSE;
438
439 END lot_status_check;
440
441 END GMIUTILS;
[Home] [Help]