[Home] [Help]
PACKAGE BODY: APPS.GMI_AUTOLOT
Source
1 PACKAGE BODY gmi_autolot AS
2 /* $Header: gmialotb.pls 115.3 2003/08/14 13:00:57 jdiiorio noship $ */
3 /*============================
4 BUG#3097442 New function
5 ===========================*/
6 FUNCTION insert_sublot_gen(p_i_item_id IN NUMBER,
7 p_i_lot_no IN VARCHAR2,
8 p_i_sublot_suffix IN NUMBER)
9 RETURN NUMBER IS
10 PRAGMA AUTONOMOUS_TRANSACTION;
11
12 l_userid FND_USER.USER_ID%TYPE;
13
14
15 BEGIN
16 l_userid :=FND_GLOBAL.USER_ID;
17 INSERT INTO GMI_SUBLOT_GENERATE
18 (item_id, lot_no, next_sublot_suffix,
19 creation_date, created_by,
20 last_update_date, last_updated_by)
21 VALUES (p_i_item_id, p_i_lot_no, p_i_sublot_suffix,
22 SYSDATE, l_userid, SYSDATE, l_userid);
23 COMMIT;
24 RETURN SQLCODE;
25
26 END insert_sublot_gen;
27
28 /*============================
29 BUG#3097442 New function
30 ===========================*/
31 FUNCTION update_sublot_gen(p_next_sublot IN NUMBER,
32 p_item_id IN NUMBER,
33 p_i_lot_no IN VARCHAR2)
34 RETURN NUMBER IS
35 PRAGMA AUTONOMOUS_TRANSACTION;
36
37
38
39 BEGIN
40 UPDATE GMI_SUBLOT_GENERATE
41 set next_sublot_suffix = p_next_sublot
42 where item_id = p_item_id AND
43 lot_no = p_i_lot_no;
44 COMMIT;
45 RETURN 0;
46
47
48 END update_sublot_gen;
49
50
51 /*============================
52 BUG#3097442 New function
53 ===========================*/
54 FUNCTION update_item_suffix(p_item_id IN NUMBER,
55 p_suffix IN NUMBER)
56 RETURN NUMBER IS
57 PRAGMA AUTONOMOUS_TRANSACTION;
58
59 BEGIN
60 UPDATE IC_ITEM_MST_B
61 set lot_suffix = p_suffix
62 where item_id = p_item_id;
63 COMMIT;
64 RETURN 0;
65
66
67 END update_item_suffix;
68
69 FUNCTION check_if_lot_exists(p_f_item_id IN NUMBER,
70 p_f_lot_no IN VARCHAR2,
71 p_f_sublot_no IN VARCHAR2)
72 RETURN NUMBER IS
73
74 CURSOR GET_LOT_MASTER IS
75 SELECT lot_id
76 FROM ic_lots_mst
77 WHERE item_id = p_f_item_id AND
78 lot_no = p_f_lot_no AND
79 sublot_no = p_f_sublot_no;
80
81 CURSOR GET_LOT_MASTER2 IS
82 SELECT lot_id
83 FROM ic_lots_mst
84 WHERE item_id = p_f_item_id AND
85 lot_no = p_f_lot_no;
86
87 w_lot_id number := 0;
88
89 BEGIN
90 IF (p_f_sublot_no IS NULL) THEN
91 OPEN GET_LOT_MASTER2;
92 FETCH GET_LOT_MASTER2 INTO w_lot_id;
93 IF GET_LOT_MASTER2%NOTFOUND THEN
94 CLOSE GET_LOT_MASTER2;
95 RETURN 0;
96 ELSE
97 CLOSE GET_LOT_MASTER2;
98 RETURN 1;
99 END IF;
100 ELSE
101 OPEN GET_LOT_MASTER;
102 FETCH GET_LOT_MASTER INTO w_lot_id;
103 IF GET_LOT_MASTER%NOTFOUND THEN
104 CLOSE GET_LOT_MASTER;
105 RETURN 0;
106 ELSE
107 CLOSE GET_LOT_MASTER;
108 RETURN 1;
109 END IF;
110 END IF;
111
112
113 END check_if_lot_exists;
114
115 /* ***************************************************************************
116 FUNCTION NAME
117 generate_lot_number
118
119 INPUT PARAMETERS
120 p_item_id - Item id for which a lot is to be generated.
121 p_in_lot_no - Lot no for which a sublot is to be generated.
122 p_orgn_code - Optional for use in user routine.
123 p_doc_id - Optional for use in user routine.
124 p_line_id - Optional for use in user routine.
125 p_doc_type - Optional for use in user routine.
126
127 RETURNS
128 p_out_lot_no - generated lot number.
129 p_sublot_no - generated sublot number.
130 p_return_status - return code.
131
132 DESCRIPTION
133 This procedure attempts to generate a lot or sublot for an item.
134 If a user routine is requested, it will be invoked instead of
135 the OPM lot generation logic.
136 The procedure will validate the input and pad the generated lot
137 if required. It will also verify that the generated lot does
138 not exist.
139
140 AUTHOR
141 Joe DiIorio - 03/01/2003
142
143 HISTORY
144 Joe DiIorio - 08/14/2003 - BUG#3097442 - 11.5.10L
145 Removed autonomous pragma.
146 **********************************************************************/
147
148 PROCEDURE generate_lot_number(p_item_id IN NUMBER,
149 p_in_lot_no IN VARCHAR2,
150 p_orgn_code IN VARCHAR2,
151 p_doc_id IN NUMBER,
152 p_line_id IN NUMBER,
153 p_doc_type IN VARCHAR2,
154 p_out_lot_no OUT NOCOPY VARCHAR2,
155 p_sublot_no OUT NOCOPY VARCHAR2,
156 p_return_status OUT NOCOPY NUMBER)
157
158
159
160 IS
161
162 w_lot_ctl ic_item_mst_b.lot_ctl%TYPE;
163 w_sublot_ctl ic_item_mst_b.sublot_ctl%TYPE;
164 w_lot_cnt NUMBER;
165 w_next_lot NUMBER;
166 w_next_sublot NUMBER;
167 x_found NUMBER;
168 w_lotpref_length NUMBER;
169 w_lotsuff_length NUMBER;
170 w_sub_pref_length NUMBER;
171 w_sub_suff_length NUMBER;
172 db_autolot_active ic_item_mst_b.autolot_active_indicator%TYPE;
173 db_lot_prefix ic_item_mst_b.lot_prefix%TYPE;
174 db_lot_suffix ic_item_mst_b.lot_suffix%TYPE;
175 w_pad_lot_suffix VARCHAR2(32);
176 db_sublot_prefix ic_item_mst_b.sublot_prefix%TYPE;
177 db_sublot_suffix ic_item_mst_b.sublot_suffix%TYPE;
178 w_pad_sublot_suffix VARCHAR2(32);
179 db_next_sublot_suffix gmi_sublot_generate.next_sublot_suffix%TYPE;
180
181 profile_user_routine NUMBER := 0;
182 prof_lot_pad NUMBER;
183 prof_lot_max NUMBER;
184 prof_sublot_pad NUMBER;
185 prof_sublot_max NUMBER;
186 X_msg VARCHAR2(100);
187 w_userid FND_USER.USER_ID%TYPE;
188 w_err_code NUMBER;
189 /*========================================
190 BUG#3097442 - new return code holders
191 ======================================*/
192
193 l_retcode NUMBER;
194 l_updcode NUMBER;
195 l_itmcode NUMBER;
196
197 /*=============================
198 User Routine Variables
199 =============================*/
200 p_u_out_lot_no ic_lots_mst.lot_no%TYPE;
201 p_u_sublot_no ic_lots_mst.sublot_no%TYPE;
202 p_u_return_status NUMBER;
203
204 /*=============================
205 Exceptions
206 =============================*/
207 e_noitem_id EXCEPTION;
208 e_item_not_found EXCEPTION;
209 e_nolot_ctl EXCEPTION;
210 e_invalid_sublot EXCEPTION;
211 e_lot_length_error EXCEPTION;
212 e_sublot_length_error EXCEPTION;
213 e_user_prof_error EXCEPTION;
214 e_prof_lot_max EXCEPTION;
215 e_prof_sublot_max EXCEPTION;
216 e_sublot_insert EXCEPTION;
217
218 CURSOR GET_ITEM_INFO IS
219 SELECT lot_ctl, sublot_ctl,
220 autolot_active_indicator, lot_prefix,
221 lot_suffix, sublot_prefix, sublot_suffix
222 FROM ic_item_mst_b
223 WHERE item_id = p_item_id;
224
225 CURSOR GET_SUBLOT_INFO IS
226 SELECT next_sublot_suffix
227 FROM gmi_sublot_generate
228 WHERE item_id = p_item_id AND
229 lot_no = p_in_lot_no;
230
231
232
233 BEGIN
234 p_return_status := 0;
235 p_out_lot_no := NULL;
236 p_sublot_no := NULL;
237
238 /*=========================================
239 If item not passed it is an error.
240 =========================================*/
241
242 IF (p_item_id IS NULL) THEN
243 RAISE e_noitem_id;
244 END IF;
245
246 /*=========================================
247 Retrieve Item Information.
248 =========================================*/
249
250 OPEN GET_ITEM_INFO;
251 FETCH GET_ITEM_INFO INTO w_lot_ctl, w_sublot_ctl,
252 db_autolot_active, db_lot_prefix,
253 db_lot_suffix, db_sublot_prefix, db_sublot_suffix;
254 IF GET_ITEM_INFO%NOTFOUND THEN
255 CLOSE GET_ITEM_INFO;
256 RAISE e_item_not_found;
257 END IF;
258 CLOSE GET_ITEM_INFO;
259
260 /*=========================================
261 Check if Autolot Rules Exist.
262 =========================================*/
263
264 IF (db_autolot_active = 0 OR db_autolot_active IS NULL) THEN
265 /*===================================================
266 Commented out message so it stays off stack.
267 FND_MESSAGE.SET_NAME ('GMI','GMI_AUTOLOT_NO_SETUP');
268 ===================================================*/
269 p_return_status := 5;
270 RETURN;
271 END IF;
272
273
274 /*=========================================
275 Check for Lot Controlled Item.
276 =========================================*/
277
278 IF (w_lot_ctl < 1) THEN
279 RAISE e_nolot_ctl;
280 END IF;
281
282 /*=========================================
283 Check for Invalid Sublot Request.
284 =========================================*/
285
286 IF (p_in_lot_no IS NOT NULL and w_sublot_ctl = 0) THEN
287 RAISE e_invalid_sublot;
288 END IF;
289
290 /*=========================================
291 Check for User Routine
292 =========================================*/
293
294
295 IF FND_PROFILE.DEFINED('GMI_USER_LOT_AUTO_ROUTINE') THEN
296 profile_user_routine := NVL(FND_PROFILE.VALUE('GMI_USER_LOT_AUTO_ROUTINE'),0);
297 ELSE
298 profile_user_routine := 0;
299 END IF;
300
301 IF (profile_user_routine = 1) THEN
302 gmi_user_autolot.user_lot_number(p_item_id,
303 p_in_lot_no,
304 p_orgn_code,
305 p_doc_id,
306 p_line_id,
307 p_doc_type,
308 p_u_out_lot_no,
309 p_u_sublot_no,
310 p_u_return_status);
311 IF (p_u_return_status = 0) THEN
312 p_out_lot_no := p_u_out_lot_no;
313 p_sublot_no := p_u_sublot_no;
314 p_return_status := 5;
315 RETURN;
316 ELSE
317 IF (p_u_return_status < 0) THEN -- fatal
318 p_return_status := -99;
319 ROLLBACK;
320 RETURN;
321 ELSE -- nonfatal
322 p_out_lot_no := p_u_out_lot_no;
323 p_sublot_no := p_u_sublot_no;
324 p_return_status := 12;
325 RETURN;
326 END IF;
327 END IF;
328 END IF;
329
330 /*=========================================
331 Retrieve Profile Padding Rules
332 =========================================*/
333
334 IF FND_PROFILE.DEFINED('GMI_LOT_PAD_INDICATOR') THEN
335 prof_lot_pad := NVL(FND_PROFILE.VALUE('GMI_LOT_PAD_INDICATOR'),0);
336 IF (prof_lot_pad = '1') THEN
337 IF FND_PROFILE.DEFINED('GMI_MAX_LOT_LENGTH') THEN
338 prof_lot_max := FND_PROFILE.VALUE('GMI_MAX_LOT_LENGTH');
339 ELSE
340 RAISE e_prof_lot_max;
341 END IF;
342 ELSE
343 prof_lot_max := 32;
344 END IF;
345 ELSE
346 prof_lot_pad := 0;
347 prof_lot_max := 32;
348 END IF;
349
350
351 IF FND_PROFILE.DEFINED('GMI_SUBLOT_PAD_INDICATOR') THEN
352 prof_sublot_pad := NVL(FND_PROFILE.VALUE('GMI_SUBLOT_PAD_INDICATOR'),0);
353 IF (prof_sublot_pad = 1) THEN
357 RAISE e_prof_sublot_max;
354 IF FND_PROFILE.DEFINED('GMI_MAX_SUBLOT_LENGTH') THEN
355 prof_sublot_max := FND_PROFILE.VALUE('GMI_MAX_SUBLOT_LENGTH');
356 ELSE
358 END IF;
359 ELSE
360 prof_sublot_max := 32;
361 END IF;
362 ELSE
363 prof_sublot_pad := 0;
364 prof_sublot_max := 32;
365 END IF;
366
367
368 /*=========================================
369 Generate Lot Numbers
370 =======================================*/
371
372 IF (p_in_lot_no IS NOT NULL) THEN -- generate the sublot
373 OPEN GET_SUBLOT_INFO;
374 FETCH GET_SUBLOT_INFO INTO w_next_sublot;
375 IF GET_SUBLOT_INFO%NOTFOUND THEN
376 CLOSE GET_SUBLOT_INFO;
377 /*========================================
378 BUG#3097442 - Replace inline code
379 with code to autonomous function.
380 ======================================*/
381 l_retcode := insert_sublot_gen(p_item_id,
382 p_in_lot_no,
383 db_sublot_suffix);
384
385 IF (l_retcode <> 0) THEN
386 RAISE e_sublot_insert;
387 END IF;
388 w_next_sublot := db_sublot_suffix;
389 ELSE
390 CLOSE GET_SUBLOT_INFO;
391 END IF;
392 p_out_lot_no := p_in_lot_no;
393 x_found := 1; -- 1 means ic_lots_mst exists
394 WHILE (x_found = 1)
395 LOOP
396 w_sub_pref_length := nvl(LENGTHB(db_sublot_prefix),0);
397 w_sub_suff_length := LENGTHB(w_next_sublot);
398 IF ((w_sub_pref_length + w_sub_suff_length) <= prof_sublot_max) THEN
399 IF (prof_sublot_pad = 1) THEN
400 w_pad_sublot_suffix := LPAD(w_next_sublot,(prof_sublot_max - w_sub_pref_length),'0');
401 p_sublot_no := db_sublot_prefix||w_pad_sublot_suffix;
402 ELSE
403 p_sublot_no := db_sublot_prefix||w_next_sublot;
404 END IF;
405 x_found := check_if_lot_exists(p_item_id, p_out_lot_no, p_sublot_no);
406 w_next_sublot := w_next_sublot + 1;
407 IF (x_found = 0) THEN
408 EXIT;
409 END IF;
410 ELSE -- field will be truncated
411 RAISE e_sublot_length_error;
412 END IF;
413 END LOOP;
414 /*========================================
415 BUG#3097442 - Replace inline code
416 with code to autonomous function.
417 ======================================*/
418 l_updcode := update_sublot_gen(w_next_sublot, p_item_id, p_in_lot_no);
419 ELSE -- input lot number is null
420 w_next_lot := db_lot_suffix;
421 x_found := 1;
422 WHILE (x_found = 1)
423 LOOP
424 w_lotpref_length := nvl(LENGTHB(db_lot_prefix),0);
425 w_lotsuff_length := LENGTHB(w_next_lot);
426 IF ((w_lotpref_length + w_lotsuff_length) <= prof_lot_max) THEN
427 IF (prof_lot_pad = 1) THEN
428 w_pad_lot_suffix := LPAD(w_next_lot,(prof_lot_max - w_lotpref_length),'0');
429 p_out_lot_no := db_lot_prefix||w_pad_lot_suffix;
430 ELSE
431 p_out_lot_no := db_lot_prefix||w_next_lot;
432 END IF;
433 x_found := check_if_lot_exists(p_item_id, p_out_lot_no, NULL);
434 w_next_lot := w_next_lot + 1;
435 IF (x_found = 0) THEN
436 EXIT;
437 END IF;
438 ELSE -- field will be truncated
439 RAISE e_lot_length_error;
440 END IF;
441 END LOOP;
442 /*========================================
443 BUG#3097442 - Replace inline code
444 with code to autonomous function.
445 ======================================*/
446 l_itmcode := update_item_suffix(p_item_id, w_next_lot);
447
448 END IF;
449
450 /*========================================
451 BUG#3097442 - Removed commit from here.
452 ======================================*/
453
454 EXCEPTION
455 WHEN e_noitem_id THEN
456 FND_MESSAGE.SET_NAME ('GMI','GMI_AUTOLOT_NO_ITEM_ID');
457 p_return_status := -80;
458 RETURN;
459 WHEN e_item_not_found THEN
460 FND_MESSAGE.SET_NAME ('GMI','GMI_AUTOLOT_ITEM_NOTFOUND');
461 FND_MESSAGE.SET_TOKEN ('BADITEM',to_char(p_item_id));
462 p_return_status := -82;
463 RETURN;
464 WHEN e_nolot_ctl THEN
465 FND_MESSAGE.SET_NAME ('GMI','GMI_AUTOLOT_NOLOT_CTL_ITEM');
466 p_return_status := -84;
467 RETURN;
468 WHEN e_invalid_sublot THEN
469 FND_MESSAGE.SET_NAME ('GMI','GMI_AUTOLOT_INVALID_SUBLOT_REQ');
470 p_return_status := -86;
471 RETURN;
472 WHEN e_prof_lot_max THEN
473 FND_MESSAGE.SET_NAME('GMI','GMI_AUTOLOT_LOT_MAX_ERROR');
474 p_return_status := -88;
475 RETURN;
476 WHEN e_prof_sublot_max THEN
477 FND_MESSAGE.SET_NAME('GMI','GMI_AUTOLOT_SUBLOT_MAX_ERROR');
478 p_return_status := -90;
479 RETURN;
480 WHEN e_lot_length_error THEN
481 FND_MESSAGE.SET_NAME ('GMI','GMI_AUTOLOT_LOT_LENGTH_ERROR');
482 p_return_status := -120;
483 ROLLBACK;
484 RETURN;
485 WHEN e_sublot_length_error THEN
486 FND_MESSAGE.SET_NAME ('GMI','GMI_AUTOLOT_SUBLOT_LENGTH_ERR');
487 p_return_status := -122;
488 ROLLBACK;
489 RETURN;
490 WHEN e_sublot_insert THEN
491 FND_MESSAGE.SET_NAME ('GMI','GMI_AUTOLOT_INSERT_SUBLOT_ERR');
492 p_return_status := -126;
493 ROLLBACK;
494 RETURN;
495 WHEN OTHERS THEN
496 FND_MESSAGE.SET_NAME ('GMI','GMI_AUTOLOT_UNHANDLED');
497 w_err_code := SQLCODE;
498 FND_MESSAGE.SET_TOKEN ('BADCODE',to_char(w_err_code));
499 p_return_status := -136;
500 ROLLBACK;
501 RAISE;
502
503 END generate_lot_number;
504
505 /****************************************************************************
506 FUNCTION NAME
507 check_for_autolot
508
509 INPUT PARAMETERS
510 p_item_id - Item id for which a lot is to be generated.
511
512 RETURNS
513 Number indicating if the item is autolot controlled or not.
514 1 = Item has autolot activated, 0 = not activated.
515 Returns a negative number if an error was detected.
516
517 DESCRIPTION
518 This function determines whether automatic lot numbering is
519 active for a given item.
520
521 AUTHOR
522 Joe DiIorio - 03/01/2003
523
524 HISTORY
525 **********************************************************************/
526
527 FUNCTION check_for_autolot(p_item_id IN NUMBER)
528 RETURN NUMBER IS
529
530 CURSOR GET_AUTOLOT_INFO IS
531 SELECT autolot_active_indicator
532 FROM ic_item_mst_b
533 WHERE item_id = p_item_id;
534
535 w_autolot NUMBER;
536
537 e_noitem_passed EXCEPTION;
538 e_item_not_found EXCEPTION;
539
540 BEGIN
541
542 /*=========================================
543 If item not passed it is an error.
544 =========================================*/
545
546 IF (p_item_id IS NULL) THEN
547 RAISE e_noitem_passed;
548 END IF;
549
550 /*=========================================
551 Retrieve Autolot Information.
552 =========================================*/
553
554 OPEN GET_AUTOLOT_INFO;
555 FETCH GET_AUTOLOT_INFO INTO w_autolot;
556 IF GET_AUTOLOT_INFO%NOTFOUND THEN
557 CLOSE GET_AUTOLOT_INFO;
558 RAISE e_item_not_found;
559 ELSE
560 CLOSE GET_AUTOLOT_INFO;
561 RETURN w_autolot;
562 END IF;
563
564 EXCEPTION
565 WHEN e_noitem_passed THEN
566 FND_MESSAGE.SET_NAME ('GMI','GMI_AUTOLOT_NO_ITEM_ID');
567 RETURN -80;
568 WHEN e_item_not_found THEN
569 FND_MESSAGE.SET_NAME ('GMI','GMI_AUTOLOT_ITEM_NOTFOUND');
570 RETURN -82;
571
572
573 END check_for_autolot;
574 END;
575
576