[Home] [Help]
PACKAGE BODY: APPS.GML_MLT_CNTR_RCPT
Source
1 PACKAGE BODY GML_MLT_CNTR_RCPT AS
2 /* $Header: GMLMTCRB.pls 115.3 2004/06/18 17:08:01 pupakare noship $*/
3
4 /*+========================================================================+
5 | Copyright (c) 1998 Oracle Corporation |
6 | Redwood Shores, CA, USA |
7 | All rights reserved. |
8 +==========================================================================+
9 | FILE NAME |
10 | GMLMTCRB.pls |
11 | |
12 | PACKAGE NAME |
13 | GML_MLT_CNTR_RCPT |
14 | |
15 | DESCRIPTION |
16 | This package procedure is use to create new lots depending on the |
17 | parameters passed and returns the new lots in the plsql table back to |
18 | RCVGMLCR.pld which then populates the LOT ENTRY screen with all the |
19 | lots.
20 | |
21 | CONTENTS |
22 | Create_Lots |
23 | |
24 | HISTORY |
25 | Created - Preetam Bamb 07/28/2003 |
26 | |
27 +==========================================================================+
28 Body end of comments*/
29
30
31 /*==========================================================================+
32 | FUNCTION NAME |
33 | check_if_lot_exists |
34 | |
35 | TYPE |
36 | Public |
37 | |
38 | USAGE |
39 | Check if the lot/sublot suggested by the auto lot routine already |
40 | exists in the database. If yes then return 1 else return 0 |
41 | |
42 | DESCRIPTION |
43 | |
44 | |
45 | RETURNS |
46 | NUMBER - 1 - Lot already exists for that item in the database. |
47 | 0 - Lot does not exist in the database. |
48 | |
49 | HISTORY |
50 | Created - Preetam Bamb 7/28/2003 Bug# 3033780 11.5.1L and ahead |
51 +==========================================================================+
52 Api end of comments*/
53
54 FUNCTION check_if_lot_exists(p_f_item_id IN NUMBER,
55 p_f_lot_no IN VARCHAR2,
56 p_f_sublot_no IN VARCHAR2)
57 RETURN NUMBER IS
58
59 CURSOR GET_LOT_MASTER IS
60 SELECT lot_id
61 FROM ic_lots_mst
62 WHERE item_id = p_f_item_id AND
63 lot_no = p_f_lot_no AND
64 sublot_no = p_f_sublot_no;
65
66 CURSOR GET_LOT_MASTER2 IS
67 SELECT lot_id
68 FROM ic_lots_mst
69 WHERE item_id = p_f_item_id AND
70 lot_no = p_f_lot_no;
71
72 w_lot_id number := 0;
73
74 BEGIN
75 IF (p_f_sublot_no IS NULL) THEN
76 OPEN GET_LOT_MASTER2;
77 FETCH GET_LOT_MASTER2 INTO w_lot_id;
78 IF GET_LOT_MASTER2%NOTFOUND THEN
79 CLOSE GET_LOT_MASTER2;
80 RETURN 0;
81 ELSE
82 CLOSE GET_LOT_MASTER2;
83 RETURN 1;
84 END IF;
85 ELSE
86 OPEN GET_LOT_MASTER;
87 FETCH GET_LOT_MASTER INTO w_lot_id;
88 IF GET_LOT_MASTER%NOTFOUND THEN
89 CLOSE GET_LOT_MASTER;
90 RETURN 0;
91 ELSE
92 CLOSE GET_LOT_MASTER;
93 RETURN 1;
94 END IF;
95 END IF;
96
97
98 END check_if_lot_exists;
99
100 /*==========================================================================+
101 | FUNCTION NAME |
102 | Create_lots |
103 | |
104 | TYPE |
105 | Public |
106 | |
107 | USAGE |
108 | Create lots and lot specific conversion |
109 | |
110 | DESCRIPTION |
111 | |
112 | |
113 | RETURNS |
114 | VARCHAR2 - 'S' All records processed successfully |
115 | 'E' 1 or more records errored |
116 | 'U' 1 or more record unexpected error |
117 | PLSQL table of Lots. |
118 | |
119 | HISTORY |
120 | Created - Preetam Bamb 7/28/2003 Bug# 3033780 11.5.1L and ahead |
121 +==========================================================================+
122 Api end of comments*/
123
124 FUNCTION Create_Lots
125 ( p_item_id IN NUMBER
126 , p_lot_no IN VARCHAR2
127 , p_no_of_lots IN NUMBER
128 , p_no_of_sublots IN NUMBER
129 , p_expire_date IN DATE
130 , p_lot_spec_conv IN VARCHAR2
131 , p_primary_uom IN VARCHAR2
132 , p_primary_qty IN VARCHAR2
133 , p_secondary_uom IN VARCHAR2
134 , p_secondary_qty IN VARCHAR2
135 , p_shipvend_id IN NUMBER
136 , p_vendor_lot_no IN VARCHAR2
137 , x_lot_table IN OUT NOCOPY lot_table
138 , x_return_status OUT NOCOPY VARCHAR2
139 , x_msg_count OUT NOCOPY NUMBER
140 , x_msg_data OUT NOCOPY VARCHAR2
141 ) RETURN VARCHAR2
142
143 IS
144
145 /*
146 Local variables
147 */
148
149 l_status VARCHAR2(1);
150 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
151 l_count NUMBER ;
152 l_loop_cnt NUMBER ;
153 l_no_of_lots NUMBER := p_no_of_lots;
154 l_no_of_sublots NUMBER := p_no_of_sublots;
155 l_dummy_cnt NUMBER :=0;
156 l_dummy_cnt1 NUMBER :=0;
157 l_record_count NUMBER :=0;
158 l_data VARCHAR2(2000);
159
160 lot_rec GMIGAPI.lot_rec_typ;
161
162 l_lot_no VARCHAR2(32) := NULL;
163 l_sublot_no VARCHAR2(32) := NULL;
164 l_item_no VARCHAR2(100);
165 l_qc_grade VARCHAR2(100);
166
167 l_ic_lots_mst_row ic_lots_mst%ROWTYPE;
168 l_ic_lots_cpg_row ic_lots_cpg%ROWTYPE;
169
170 l_api_version NUMBER := GMIGUTL.api_version;
171 l_dummy_lot VARCHAR2(32);
172
173 e_auto_lot_create EXCEPTION;
174 e_create_lot EXCEPTION;
175 e_lot_conv_err EXCEPTION;
176 l_shipvendor_no VARCHAR2(32);
177 x_found NUMBER := 1;
178
179 Cursor Cr_item Is
180 Select item_no, qc_grade
181 From ic_item_mst
182 Where item_id = p_item_id;
183
184 BEGIN
185 x_return_status := FND_API.G_RET_STS_SUCCESS;
186
187 --Get item attributes required to create lots.
188 Open Cr_item;
189 Fetch Cr_item Into l_item_no,l_qc_grade;
190 If Cr_item%NOTFOUND Then
191 Close Cr_item;
192 FND_MESSAGE.SET_NAME('GML','GML_OPM_ITEM_NOT_EXIST');
193 FND_MSG_PUB.ADD;
194 x_msg_count := 1;
195 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
196 RETURN FND_API.G_RET_STS_UNEXP_ERROR;
197 End If;
198 If Cr_item%ISOPEN Then
199 Close Cr_item;
200 End If;
201
202 IF (p_shipvend_id) IS NOT NULL THEN
203 SELECT vendor_no INTO l_shipvendor_no
204 FROM po_vend_mst
205 where vendor_id = p_shipvend_id;
206 IF (SQL%NOTFOUND) THEN
207 l_shipvendor_no := NULL;
208 END IF;
209 END IF;
210
211 --If lot number specified then create sublots for that lot depending on the p_no_of_sublots
212 --parameter.
213 IF p_lot_no IS NOT NULL THEN
214
215 l_no_of_sublots := p_no_of_sublots;
216
217
218 IF nvl(l_no_of_sublots,0) > 0 THEN
219
220 l_dummy_cnt := 0;
221
222 --Loop p_no_of_sublots times to get sublots for the specified lot.
223 WHILE l_no_of_sublots <> 0
224 LOOP
225
226 l_dummy_cnt := l_dummy_cnt + 1;
227 l_no_of_sublots := l_no_of_sublots -1;
228
229 -- Check if this combination of Lot/Sublot already exists in the database
230 --x_found := 1; -- 1 means combination in ic_lots_mst exists
231 --WHILE (x_found = 1)
232 ---LOOP
233 gmi_autolot.generate_lot_number(
234 p_item_id => p_item_id,
235 p_in_lot_no => p_lot_no,
236 p_orgn_code => NULL,
237 p_doc_id => NULL,
238 p_line_id => NULL,
239 p_doc_type => 'PORC',
240 p_out_lot_no => l_lot_no,
241 p_sublot_no => l_sublot_no,
242 p_return_status => l_return_status );
243
244 IF l_return_status < 0 THEN
245 RAISE e_auto_lot_create;
246 END IF; --l_return_status < 0
247
248 -- If not then exit else get the next combination.
249 --x_found := check_if_lot_exists(p_item_id, l_lot_no, l_sublot_no);
250 --IF (x_found = 0) THEN
251 -- EXIT;
252 --END IF;
253 --END LOOP;
254
255 --Populate the lot record.
256 lot_rec.item_no := l_item_no;
257 lot_rec.lot_no := l_lot_no;
258 lot_rec.sublot_no := l_sublot_no;
259 lot_rec.lot_desc := NULL;
260 lot_rec.qc_grade := l_qc_grade;
261 lot_rec.lot_created := SYSDATE;
262
263 IF p_expire_date IS NOT NULL
264 THEN
265 lot_rec.expire_date := p_expire_date;
266 END IF;
267
268 lot_rec.inactive_ind := 0; --0 for active.
269 lot_rec.origination_type:= 3; --3 for receiving.
270 lot_rec.shipvendor_no := l_shipvendor_no;
271 lot_rec.vendor_lot_no := p_vendor_lot_no;
272 lot_rec.ic_matr_date := NULL; -- Bug 3698036 - changed to NULL from GMA_GLOBAL_GRP.SY$MAX_DATE.
273 lot_rec.ic_hold_date := NULL; -- Bug 3698036 - changed to NULL from GMA_GLOBAL_GRP.SY$MAX_DATE.
274 lot_rec.user_name := FND_GLOBAl.USER_NAME;
275
276 IF (GMIGUTL.SETUP(lot_rec.user_name)) THEN
277 GMIPAPI.Create_Lot
278 ( p_api_version => 3
279 , p_init_msg_list => FND_API.G_TRUE
280 , p_commit => FND_API.G_FALSE
281 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
282 , p_lot_rec =>lot_rec
283 , x_ic_lots_mst_row => l_ic_lots_mst_row
284 , x_ic_lots_cpg_row => l_ic_lots_cpg_row
285 , x_return_status => l_status
286 , x_msg_count => l_count
287 , x_msg_data => l_data
288 );
289
290 IF (l_status = 'S') THEN
291 x_return_status := FND_API.G_RET_STS_SUCCESS;
292 ELSE
293 x_return_status := l_status;
294 x_msg_count := l_count;
295 x_msg_data := l_data;
296 RAISE e_create_lot;
297 END IF;
298
299 END IF;
300
301 IF p_lot_spec_conv = 'Y' THEN
302 PO_GML_DB_COMMON.CREATE_LOT_SPECIFIC_CONVERSION(
303 l_item_no,
304 l_ic_lots_mst_row.lot_no,
305 l_ic_lots_mst_row.sublot_no,
306 p_primary_uom,
307 p_secondary_uom,
308 p_secondary_qty/nvl(p_primary_qty,1),
309 l_status,l_data);
310 IF l_status IN ('E','U') THEN
311 x_return_status := l_status;
312 x_msg_count := 1;
313 --x_msg_data := l_data;
314 FND_MESSAGE.SET_NAME('GMI', 'GMI_UNEXPECTED_ERROR');
315 FND_MESSAGE.SET_TOKEN('ERROR', l_data);
316 FND_MSG_PUB.ADD;
317 raise e_lot_conv_err;
318 END IF;
319 END IF;/* IF p_lot_spec_conv = 'Y' */
320
321 x_lot_table(l_dummy_cnt).lot_id := l_ic_lots_mst_row.lot_id;
322 x_lot_table(l_dummy_cnt).lot_no := l_ic_lots_mst_row.lot_no;
323 x_lot_table(l_dummy_cnt).sublot_no := l_ic_lots_mst_row.sublot_no;
324 x_lot_table(l_dummy_cnt).expire_date := l_ic_lots_mst_row.expire_date;
325
326 END LOOP;
327 END IF; /*IF nvl(l_no_of_sublots,0) > 0 */
328
329 ELSIF p_lot_no IS NULL AND p_no_of_lots IS NOT NULL THEN
330
331 l_no_of_lots := p_no_of_lots;
332 l_dummy_cnt := 0;
333
334 WHILE l_no_of_lots <> 0
335 LOOP
336
337 l_dummy_cnt := l_dummy_cnt + 1;
338
339 l_no_of_lots :=l_no_of_lots -1;
340
341 --x_found := 1; -- 1 means ic_lots_mst exists
342 --WHILE (x_found = 1)
343 --LOOP
344 gmi_autolot.generate_lot_number(
345 p_item_id => p_item_id,
346 p_in_lot_no => NULL,
347 p_orgn_code => NULL,
348 p_doc_id => NULL,
349 p_line_id => NULL,
350 p_doc_type => 'PORC',
351 p_out_lot_no => l_lot_no,
352 p_sublot_no => l_sublot_no,
353 p_return_status => l_return_status );
354 IF l_return_status < 0 THEN
355 RAISE e_auto_lot_create;
356 END IF; --l_return_status < 0
357
358 -- x_found := check_if_lot_exists(p_item_id, l_lot_no, NULL);
359 -- IF (x_found = 0) THEN
360 -- EXIT;
361 -- END IF;
362 --END LOOP;
363
364
365 lot_rec.item_no := l_item_no;
366 lot_rec.lot_no := l_lot_no;
367 lot_rec.sublot_no := l_sublot_no;
368 lot_rec.lot_desc := NULL;
369 lot_rec.qc_grade := l_qc_grade;
370 lot_rec.lot_created := SYSDATE;
371
372 IF p_expire_date IS NOT NULL
373 THEN
374 lot_rec.expire_date := p_expire_date;
375 END IF;
376
377 lot_rec.inactive_ind := 0;
378 lot_rec.origination_type:= 3;
379 lot_rec.shipvendor_no := l_shipvendor_no;
380 lot_rec.vendor_lot_no := p_vendor_lot_no;
381 lot_rec.ic_matr_date := NULL; -- Bug 3698036 - changed to NULL from GMA_GLOBAL_GRP.SY$MAX_DATE.
382 lot_rec.ic_hold_date := NULL; -- Bug 3698036 - changed to NULL from GMA_GLOBAL_GRP.SY$MAX_DATE.
383 lot_rec.user_name := FND_GLOBAl.USER_NAME;
384
385 IF nvl(p_no_of_sublots,0) = 0 THEN
386 IF (GMIGUTL.SETUP(lot_rec.user_name)) THEN
387 GMIPAPI.Create_Lot
388 ( p_api_version => 3
389 , p_init_msg_list => FND_API.G_TRUE
390 , p_commit => FND_API.G_FALSE
391 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
392 , p_lot_rec =>lot_rec
393 , x_ic_lots_mst_row => l_ic_lots_mst_row
394 , x_ic_lots_cpg_row => l_ic_lots_cpg_row
395 , x_return_status => l_status
396 , x_msg_count => l_count
397 , x_msg_data => l_data
398 );
399 IF (l_status = 'S') THEN
400 x_return_status := FND_API.G_RET_STS_SUCCESS;
401 ELSE
402 x_return_status := l_status;
403 x_msg_count := l_count;
404 x_msg_data := l_data;
405 RAISE e_create_lot;
406 END IF;
407 END IF;
408
409 IF p_lot_spec_conv = 'Y' THEN
410
411
412
413 PO_GML_DB_COMMON.CREATE_LOT_SPECIFIC_CONVERSION(
414 l_item_no,
415 l_ic_lots_mst_row.lot_no,
416 l_ic_lots_mst_row.sublot_no,
417 p_primary_uom,
418 p_secondary_uom,
419 p_secondary_qty/nvl(p_primary_qty,1),
420 l_status,l_data);
421
422 IF l_status IN ('E','U') THEN
423
424 x_return_status := l_status;
425 x_msg_count := 1;
426 --x_msg_data := l_data;
427 FND_MESSAGE.SET_NAME('GMI', 'GMI_UNEXPECTED_ERROR');
428 FND_MESSAGE.SET_TOKEN('ERROR', l_data);
429 FND_MSG_PUB.ADD;
430 raise e_lot_conv_err;
431 END IF;
432 END IF;/* IF p_lot_spec_conv = 'Y' */
433
434 END IF;
435
436 x_lot_table(l_dummy_cnt).lot_id := l_ic_lots_mst_row.lot_id;
437 x_lot_table(l_dummy_cnt).lot_no := l_ic_lots_mst_row.lot_no;
438 x_lot_table(l_dummy_cnt).sublot_no := l_ic_lots_mst_row.sublot_no;
439 x_lot_table(l_dummy_cnt).expire_date := l_ic_lots_mst_row.expire_date;
440
441 IF nvl(p_no_of_sublots,0) <> 0 THEN
442
443 l_no_of_sublots := p_no_of_sublots;
444 l_dummy_cnt1 := l_dummy_cnt;
445
446 WHILE l_no_of_sublots <> 0
447 LOOP
448
449 l_no_of_sublots :=l_no_of_sublots - 1;
450 l_dummy_lot := l_lot_no;
451
452 --x_found := 1; -- 1 means ic_lots_mst exists
453 --WHILE (x_found = 1)
454 --LOOP
455 gmi_autolot.generate_lot_number(
456 p_item_id => p_item_id,
457 p_in_lot_no => l_dummy_lot,
458 p_orgn_code => NULL,
459 p_doc_id => NULL,
460 p_line_id => NULL,
461 p_doc_type => 'PORC',
462 p_out_lot_no => l_lot_no,
463 p_sublot_no => l_sublot_no,
464 p_return_status => l_return_status );
465 IF l_return_status < 0 THEN
466 RAISE e_auto_lot_create;
467 END IF; --l_return_status < 0
468
469 -- x_found := check_if_lot_exists(p_item_id, l_lot_no, l_sublot_no);
470 -- IF (x_found = 0) THEN
471 -- EXIT;
472 -- END IF;
473 --END LOOP;
474
475
476 lot_rec.item_no := l_item_no;
477 lot_rec.lot_no := l_lot_no;
478 lot_rec.sublot_no := l_sublot_no;
479 lot_rec.lot_desc := NULL;
480 lot_rec.qc_grade := l_qc_grade;
481 lot_rec.lot_created := SYSDATE;
482
483 IF p_expire_date IS NOT NULL
484 THEN
485 lot_rec.expire_date := p_expire_date;
486 END IF;
487
488 lot_rec.inactive_ind :=0;
489 lot_rec.origination_type:=3;
490 lot_rec.shipvendor_no := l_shipvendor_no;
491 lot_rec.vendor_lot_no := p_vendor_lot_no;
492 lot_rec.ic_matr_date := NULL; -- Bug 3698036 - changed to NULL from GMA_GLOBAL_GRP.SY$MAX_DATE.
493 lot_rec.ic_hold_date := NULL; -- Bug 3698036 - changed to NULL from GMA_GLOBAL_GRP.SY$MAX_DATE.
494 lot_rec.user_name :=FND_GLOBAl.USER_NAME;
495
496 IF (GMIGUTL.SETUP(lot_rec.user_name)) THEN
497 GMIPAPI.Create_Lot
498 ( p_api_version => 3
499 , p_init_msg_list => FND_API.G_TRUE
500 , p_commit => FND_API.G_FALSE
501 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
502 , p_lot_rec =>lot_rec
503 , x_ic_lots_mst_row => l_ic_lots_mst_row
504 , x_ic_lots_cpg_row => l_ic_lots_cpg_row
505 , x_return_status => l_status
506 , x_msg_count => l_count
507 , x_msg_data => l_data
508 );
509 IF (l_status = 'S') THEN
510 x_return_status := FND_API.G_RET_STS_SUCCESS;
511 ELSE
512 x_return_status := l_status;
513 x_msg_count := l_count;
514 x_msg_data := l_data;
515 RAISE e_create_lot;
516 END IF;
517
518 END IF;
519
520 x_lot_table(l_dummy_cnt1).lot_id := l_ic_lots_mst_row.lot_id;
521 x_lot_table(l_dummy_cnt1).lot_no := l_ic_lots_mst_row.lot_no;
522 x_lot_table(l_dummy_cnt1).sublot_no := l_ic_lots_mst_row.sublot_no;
523 x_lot_table(l_dummy_cnt1).expire_date := l_ic_lots_mst_row.expire_date;
524
525 IF p_lot_spec_conv = 'Y' THEN
526
527 PO_GML_DB_COMMON.CREATE_LOT_SPECIFIC_CONVERSION(
528 l_item_no,
529 l_ic_lots_mst_row.lot_no,
530 l_ic_lots_mst_row.sublot_no,
531 p_primary_uom,
532 p_secondary_uom,
533 p_secondary_qty/nvl(p_primary_qty,1),
534 l_status,
535 l_data);
536 IF l_status IN ('E','U') THEN
537 x_return_status := l_status;
538 x_msg_count := 1;
539 --x_msg_data := l_data;
540 FND_MESSAGE.SET_NAME('GMI', 'GMI_UNEXPECTED_ERROR');
541 FND_MESSAGE.SET_TOKEN('ERROR', l_data);
542 FND_MSG_PUB.ADD;
543 raise e_lot_conv_err;
544 END IF;
545 END IF;/* IF p_lot_spec_conv = 'Y' */
546
547 l_dummy_cnt1 := l_dummy_cnt1 + 1;
548
549 END LOOP;/*While l_no_of_sublots <> 0*/
550
551 l_dummy_cnt := l_dummy_cnt1 -1 ;
552
553 END IF;/*IF nvl(p_no_of_sublots,0) <> 0*/
554
555 END LOOP;/*While l_no_of_lots <> 0*/
556 END IF;/*p_lot_no IS NOT NULL */
557
558
559
560 RETURN l_return_status;
561
562 EXCEPTION
563 WHEN e_auto_lot_create THEN
564 x_return_status := FND_API.G_RET_STS_ERROR;
565 RETURN x_return_status;
566 WHEN e_create_lot THEN
567 x_return_status := FND_API.G_RET_STS_ERROR;
568 RETURN x_return_status;
569 WHEN e_lot_conv_err THEN
570 x_return_status := FND_API.G_RET_STS_ERROR;
571 RETURN x_return_status;
572 WHEN OTHERS THEN
573 /* dbms_output.put_line('Other Error'); */
574 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
575 x_msg_count := 1;
576 FND_MESSAGE.SET_NAME('GMI', 'GMI_UNEXPECTED_ERROR');
577 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
578 FND_MSG_PUB.ADD;
579 RETURN x_return_status;
580 END Create_Lots;
581
582
583 --Start of comments
584 --+========================================================================+
585 --| API Name : GMIGAPI_qty_format |
586 --| TYPE : Group |
587 --| Notes : This function returns the format of GMAGAPI which |
588 --| is used by the receiving library RCVGMLCR.pld to |
589 --| call the status immediate api to change status of a lot |
590 --| |
591 --| HISTORY |
592 --| P Bamb 11-AUG-2003 Created. |
593 --| |
594 --+========================================================================+
595 -- End of comments
596
597 FUNCTION Gmigapi_Qty_Format RETURN GMIGAPI.qty_rec_typ IS
598 l_temp GMIGAPI.qty_rec_typ;
599
600 BEGIN
601 return l_temp;
602 END Gmigapi_Qty_Format;
603
604 END GML_MLT_CNTR_RCPT;