[Home] [Help]
PACKAGE BODY: APPS.GMI_ERES_UTILS
Source
1 PACKAGE BODY gmi_eres_utils AS
2 /* $Header: GMIERESB.pls 115.7 2003/10/28 15:20:52 jdiiorio noship $ */
3
4
5
6 PROCEDURE GET_ITEM_NO
7 (pitem_id IN VARCHAR2,
8 pitem_no OUT NOCOPY VARCHAR2) IS
9
10 CURSOR get_item_number IS
11 SELECT item_no
12 FROM ic_item_mst
13 WHERE item_id = pitem_id;
14
15 BEGIN
16
17 OPEN get_item_number;
18 FETCH get_item_number INTO pitem_no;
19 IF (get_item_number%NOTFOUND) THEN
20 pitem_no := ' ';
21 END IF;
22 CLOSE get_item_number;
23
24 END GET_ITEM_NO;
25
26 PROCEDURE GET_ITEM_UM
27 (pitem_id IN VARCHAR2,
28 pitem_um OUT NOCOPY VARCHAR2) IS
29
30 CURSOR get_item_um IS
31 SELECT item_um
32 FROM ic_item_mst
33 WHERE item_id = pitem_id;
34
35 BEGIN
36
37 OPEN get_item_um;
38 FETCH get_item_um INTO pitem_um;
39 IF (get_item_um%NOTFOUND) THEN
40 pitem_um := ' ';
41 END IF;
42 CLOSE get_item_um;
43
44
45 END GET_ITEM_UM;
46
47 PROCEDURE GET_ITEM_DESC
48 (pitem_id IN VARCHAR2,
49 pitem_desc OUT NOCOPY VARCHAR2) IS
50
51 CURSOR get_item_desc IS
52 SELECT item_desc1
53 FROM ic_item_mst
54 WHERE item_id = pitem_id;
55
56 BEGIN
57
58 OPEN get_item_desc;
59 FETCH get_item_desc INTO pitem_desc;
60 IF (get_item_desc%NOTFOUND) THEN
61 pitem_desc := ' ';
62 END IF;
63 CLOSE get_item_desc;
64
65 END GET_ITEM_DESC;
66
67 PROCEDURE GET_UM_TYPE
68 (pum IN VARCHAR2,
69 pum_type OUT NOCOPY VARCHAR2) IS
70
71 CURSOR get_uom_type IS
72 SELECT um_type
73 FROM sy_uoms_mst
74 WHERE um_code = pum;
75
76 BEGIN
77
78 OPEN get_uom_type;
79 FETCH get_uom_type INTO pum_type;
80 IF (get_uom_type%NOTFOUND) THEN
81 pum_type := ' ';
82 END IF;
83 CLOSE get_uom_type;
84
85 END GET_UM_TYPE;
86
87
88 PROCEDURE GET_BASE_UOM
89 (pum_type IN VARCHAR2,
90 puom OUT NOCOPY VARCHAR2) IS
91
92 CURSOR get_base_uom IS
93 SELECT std_um
94 FROM sy_uoms_typ
95 WHERE um_type = pum_type;
96
97 BEGIN
98
99 OPEN get_base_uom;
100 FETCH get_base_uom INTO puom;
101 IF (get_base_uom%NOTFOUND) THEN
102 puom := ' ';
103 END IF;
104 CLOSE get_base_uom;
105
106 END GET_BASE_UOM;
107
108
109
110 PROCEDURE GET_ITEM_UOM_AND_TYPE
111 (pitem_id IN VARCHAR2,
112 puom OUT NOCOPY VARCHAR2,
113 pum_type OUT NOCOPY VARCHAR2) IS
114
115 CURSOR get_item_info IS
116 SELECT m.item_um, s.um_type
117 FROM ic_item_mst m, sy_uoms_mst s
118 WHERE m.item_id = pitem_id AND
119 m.item_um = s.um_code;
120
121 BEGIN
122
123 OPEN get_item_info;
124 FETCH get_item_info INTO puom, pum_type;
125 IF (get_item_info%NOTFOUND) THEN
126 puom := ' ';
127 pum_type := ' ';
128 END IF;
129 CLOSE get_item_info;
130
131 END GET_ITEM_UOM_AND_TYPE;
132
133 PROCEDURE GET_LOT_NO
134 (pitem_id IN VARCHAR2,
135 plot_id IN VARCHAR2,
136 plot_no OUT NOCOPY VARCHAR2) IS
137
138 CURSOR get_lot_no IS
139 SELECT lot_no
140 FROM ic_lots_mst
141 WHERE lot_id = plot_id AND
142 item_id = pitem_id;
143
144 BEGIN
145
146 OPEN get_lot_no;
147 FETCH get_lot_no INTO plot_no;
148 IF (get_lot_no%NOTFOUND) THEN
149 plot_no := ' ';
150 END IF;
151 CLOSE get_lot_no;
152
153 END GET_LOT_NO;
154
155
156 PROCEDURE GET_SUBLOT_NO
157 (pitem_id IN VARCHAR2,
158 plot_id IN VARCHAR2,
159 psublot_no OUT NOCOPY VARCHAR2) IS
160
161 CURSOR get_sublot_no IS
162 SELECT sublot_no
163 FROM ic_lots_mst
164 WHERE lot_id = plot_id AND
165 item_id = pitem_id;
166
167 BEGIN
168
169 OPEN get_sublot_no;
170 FETCH get_sublot_no INTO psublot_no;
171 IF (get_sublot_no%NOTFOUND) THEN
172 psublot_no := ' ';
173 END IF;
174 CLOSE get_sublot_no;
175
176 END GET_SUBLOT_NO;
177
178
179 PROCEDURE GET_LOT_DESC
180 (pitem_id IN VARCHAR2,
181 plot_id IN VARCHAR2,
182 plot_desc OUT NOCOPY VARCHAR2) IS
183
184 CURSOR get_lot_desc IS
185 SELECT lot_desc
186 FROM ic_lots_mst
187 WHERE lot_id = plot_id AND
188 item_id = pitem_id;
189
190 BEGIN
191
192 OPEN get_lot_desc;
193 FETCH get_lot_desc INTO plot_desc;
194 IF (get_lot_desc%NOTFOUND) THEN
195 plot_desc := ' ';
196 END IF;
197 CLOSE get_lot_desc;
198
199 END GET_LOT_DESC;
200
201
202 PROCEDURE GET_LOOKUP_VALUE (
203 plookup_type IN VARCHAR2,
204 plookup_code IN VARCHAR2,
205 pmeaning OUT NOCOPY VARCHAR2) IS
206
207 CURSOR get_lookup IS
208 SELECT meaning
209 FROM fnd_lookup_values_vl
210 WHERE lookup_type = plookup_type and
211 lookup_code = plookup_code;
212
213 BEGIN
214
215 OPEN get_lookup;
216 FETCH get_lookup into pmeaning;
217 IF (get_lookup%NOTFOUND) THEN
218 pmeaning := ' ';
219 END IF;
220 CLOSE get_lookup;
221
222 END GET_LOOKUP_VALUE;
223
224
225 PROCEDURE GET_VENDOR_NO (
226 pvendor_id IN VARCHAR2,
227 pvendor_no OUT NOCOPY VARCHAR2) IS
228
229 CURSOR get_vend_no IS
230 SELECT vendor_no
231 FROM po_vend_mst
232 WHERE vendor_id = pvendor_id;
233
234 BEGIN
235
236 OPEN get_vend_no;
237 FETCH get_vend_no INTO pvendor_no;
238 IF (get_vend_no%NOTFOUND) THEN
239 pvendor_no := ' ';
240 END IF;
241 CLOSE get_vend_no;
242
243 END GET_VENDOR_NO;
244
245 PROCEDURE GET_VENDOR_DESC (
246 pvendor_id IN VARCHAR2,
247 pvendor_desc OUT NOCOPY VARCHAR2) IS
248
249 CURSOR get_vend_name IS
250 SELECT vendor_name
251 FROM po_vend_mst
252 WHERE vendor_id = pvendor_id;
253
254 BEGIN
255
256 OPEN get_vend_name;
257 FETCH get_vend_name INTO pvendor_desc;
258 IF (get_vend_name%NOTFOUND) THEN
259 pvendor_desc := ' ';
260 END IF;
261 CLOSE get_vend_name;
262
263 END GET_VENDOR_DESC;
264
265
266 PROCEDURE PAD_LANGUAGE (
267 planguage_in IN VARCHAR2,
268 planguage_out OUT NOCOPY VARCHAR2) IS
269
270
271 BEGIN
272 planguage_out := RPAD(planguage_in,4);
273 END PAD_LANGUAGE;
274
275
276 PROCEDURE ACTIVATE_ITEM (pitem_id IN NUMBER) IS
277
278 BEGIN
279
280 IF (EDR_PSIG_PAGE_FLOW.SIGNATURE_STATUS = 'SUCCESS') THEN
281 UPDATE ic_item_mst_b
282 SET inactive_ind = 0,
283 trans_cnt = -99
284 WHERE item_id = pitem_id;
285 /*==================
286 BUG#3103125
287 ==================*/
288 COMMIT;
289 END IF;
290
291
292 END ACTIVATE_ITEM;
293
294
295 /*==================
296 BUG#3031296
297 ==================*/
298
299 PROCEDURE GET_JOURNAL_NO (
300 pjournal_id IN NUMBER,
301 pjournal_no OUT NOCOPY VARCHAR2) IS
302
303 CURSOR get_jrnl_no IS
304 SELECT journal_no
305 FROM ic_jrnl_mst
306 WHERE journal_id = pjournal_id;
307
308 BEGIN
309 OPEN get_jrnl_no;
310 FETCH get_jrnl_no INTO pjournal_no;
311 IF (get_jrnl_no%NOTFOUND) THEN
312 pjournal_no := ' ';
313 END IF;
314 CLOSE get_jrnl_no;
315
316 END GET_JOURNAL_NO;
317
318
319 PROCEDURE GET_GRADE_DESC (
320 pgrade IN VARCHAR2,
321 pgrade_desc OUT NOCOPY VARCHAR2) IS
322
323 CURSOR get_grade_desc IS
324 SELECT qc_grade_desc
325 FROM qc_grad_mst
326 WHERE qc_grade = pgrade;
327
328 BEGIN
329 OPEN get_grade_desc;
330 FETCH get_grade_desc INTO pgrade_desc;
331 IF (get_grade_desc%NOTFOUND) THEN
332 pgrade_desc := ' ';
333 END IF;
334 CLOSE get_grade_desc;
335
336 END GET_GRADE_DESC;
337
338 PROCEDURE GET_STATUS_DESC (
339 pstatus IN VARCHAR2,
340 pstatus_desc OUT NOCOPY VARCHAR2) IS
341
342
343 CURSOR get_status_desc IS
344 SELECT status_desc
345 FROM ic_lots_sts
346 WHERE lot_status = pstatus;
347
348 BEGIN
349 OPEN get_status_desc;
350 FETCH get_status_desc INTO pstatus_desc;
351 IF (get_status_desc%NOTFOUND) THEN
352 pstatus_desc := ' ';
353 END IF;
354 CLOSE get_status_desc;
355
356 END GET_STATUS_DESC;
357
358
359 PROCEDURE GET_REASON_DESC (
360 preason_code IN VARCHAR2,
361 preason_desc OUT NOCOPY VARCHAR2) IS
362
363 CURSOR get_reason_desc IS
364 SELECT reason_desc1
365 FROM sy_reas_cds
366 WHERE reason_code = preason_code;
367
368 BEGIN
369
370 OPEN get_reason_desc;
371 FETCH get_reason_desc INTO preason_desc;
372 IF (get_reason_desc%NOTFOUND) THEN
373 preason_desc := ' ';
374 END IF;
375 CLOSE get_reason_desc;
376
377
378 END GET_REASON_DESC;
379
380
381 PROCEDURE GET_WHSE_DESC (
382 pwhse_code IN VARCHAR2,
383 pwhse_desc OUT NOCOPY VARCHAR2) IS
384
385 CURSOR get_whse_desc IS
386 SELECT whse_name
387 FROM ic_whse_mst
388 WHERE whse_code = pwhse_code;
389
390 BEGIN
391
392 OPEN get_whse_desc;
393 FETCH get_whse_desc INTO pwhse_desc;
394 IF (get_whse_desc%NOTFOUND) THEN
395 pwhse_desc := ' ';
396 END IF;
397 CLOSE get_whse_desc;
398
399
400 END GET_WHSE_DESC;
401
402
403 PROCEDURE GET_JRNL_COMMENT (
404 pjournal_id IN NUMBER,
405 pjrnl_comment OUT NOCOPY VARCHAR2) IS
406
407 CURSOR get_jrnl_comment IS
408 SELECT journal_comment
409 FROM ic_jrnl_mst
410 WHERE journal_id = pjournal_id;
411
412 BEGIN
413 OPEN get_jrnl_comment;
414 FETCH get_jrnl_comment INTO pjrnl_comment;
415 IF (get_jrnl_comment%NOTFOUND) THEN
416 pjrnl_comment := ' ';
417 END IF;
418 CLOSE get_jrnl_comment;
419
420 END GET_JRNL_COMMENT;
421
422
423 PROCEDURE GET_SEG_VALUE (pcategory_id IN NUMBER,
424 pstructure_id IN NUMBER,
425 pcolname IN VARCHAR2,
426 pvalue OUT NOCOPY VARCHAR2)
427
428 IS
429
430 CURSOR get_value is
431 SELECT *
432 FROM mtl_categories
433 WHERE structure_id = pstructure_id
434 AND category_id = pcategory_id;
435
436 x_cat_rec get_value%ROWTYPE;
437
438 BEGIN
439
440 IF (pcolname IS NULL) THEN
441 pvalue := NULL;
442 RETURN;
443 END IF;
444
445 OPEN get_value;
446 FETCH get_value into x_cat_rec;
447 CLOSE get_value;
448
449 IF (pcolname = 'SEGMENT1') THEN
450 pvalue := x_cat_rec.segment1;
451 ELSIF (pcolname = 'SEGMENT2') THEN
452 pvalue := x_cat_rec.segment2;
453 ELSIF (pcolname = 'SEGMENT3') THEN
454 pvalue := x_cat_rec.segment3;
455 ELSIF (pcolname = 'SEGMENT4') THEN
456 pvalue := x_cat_rec.segment4;
457 ELSIF (pcolname = 'SEGMENT5') THEN
458 pvalue := x_cat_rec.segment5;
459 ELSIF (pcolname = 'SEGMENT6') THEN
460 pvalue := x_cat_rec.segment6;
461 ELSIF (pcolname = 'SEGMENT7') THEN
462 pvalue := x_cat_rec.segment7;
463 ELSIF (pcolname = 'SEGMENT8') THEN
464 pvalue := x_cat_rec.segment8;
465 ELSIF (pcolname = 'SEGMENT9') THEN
466 pvalue := x_cat_rec.segment9;
467 ELSIF (pcolname = 'SEGMENT10') THEN
468 pvalue := x_cat_rec.segment10;
469 ELSIF (pcolname = 'SEGMENT11') THEN
470 pvalue := x_cat_rec.segment11;
471 ELSIF (pcolname = 'SEGMENT12') THEN
472 pvalue := x_cat_rec.segment12;
473 ELSIF (pcolname = 'SEGMENT13') THEN
474 pvalue := x_cat_rec.segment13;
475 ELSIF (pcolname = 'SEGMENT14') THEN
476 pvalue := x_cat_rec.segment14;
477 ELSIF (pcolname = 'SEGMENT15') THEN
478 pvalue := x_cat_rec.segment15;
479 ELSIF (pcolname = 'SEGMENT16') THEN
480 pvalue := x_cat_rec.segment16;
481 ELSIF (pcolname = 'SEGMENT17') THEN
482 pvalue := x_cat_rec.segment17;
483 ELSIF (pcolname = 'SEGMENT18') THEN
484 pvalue := x_cat_rec.segment18;
485 ELSIF (pcolname = 'SEGMENT19') THEN
486 pvalue := x_cat_rec.segment19;
487 ELSIF (pcolname = 'SEGMENT20') THEN
488 pvalue := x_cat_rec.segment20;
489 ELSE
490 pvalue := NULL;
491 END IF;
492
493 END GET_SEG_VALUE;
494
495 PROCEDURE GET_ATTRIBUTE_VALUE (pitem_id IN NUMBER,
496 pcolname IN VARCHAR2,
497 pvalue OUT NOCOPY VARCHAR2)
498
499 IS
500
501 CURSOR get_itemattr is
502 SELECT *
503 FROM ic_item_mst_b
504 WHERE item_id = pitem_id;
505
506 x_item_rec get_itemattr%ROWTYPE;
507
508 BEGIN
509
510 IF (pcolname IS NULL) THEN
511 pvalue := NULL;
512 RETURN;
513 END IF;
514
515 OPEN get_itemattr;
516 FETCH get_itemattr into x_item_rec;
517 CLOSE get_itemattr;
518
519
520 IF (pcolname = 'ATTRIBUTE1') THEN
521 pvalue := x_item_rec.attribute1;
522 ELSIF (pcolname = 'ATTRIBUTE2') THEN
523 pvalue := x_item_rec.attribute2;
524 ELSIF (pcolname = 'ATTRIBUTE3') THEN
525 pvalue := x_item_rec.attribute3;
526 ELSIF (pcolname = 'ATTRIBUTE4') THEN
527 pvalue := x_item_rec.attribute4;
528 ELSIF (pcolname = 'ATTRIBUTE5') THEN
529 pvalue := x_item_rec.attribute5;
530 ELSIF (pcolname = 'ATTRIBUTE6') THEN
531 pvalue := x_item_rec.attribute6;
532 ELSIF (pcolname = 'ATTRIBUTE7') THEN
533 pvalue := x_item_rec.attribute7;
534 ELSIF (pcolname = 'ATTRIBUTE8') THEN
535 pvalue := x_item_rec.attribute8;
536 ELSIF (pcolname = 'ATTRIBUTE9') THEN
537 pvalue := x_item_rec.attribute9;
538 ELSIF (pcolname = 'ATTRIBUTE10') THEN
539 pvalue := x_item_rec.attribute10;
540 ELSIF (pcolname = 'ATTRIBUTE11') THEN
541 pvalue := x_item_rec.attribute11;
542 ELSIF (pcolname = 'ATTRIBUTE12') THEN
543 pvalue := x_item_rec.attribute12;
544 ELSIF (pcolname = 'ATTRIBUTE13') THEN
545 pvalue := x_item_rec.attribute13;
546 ELSIF (pcolname = 'ATTRIBUTE14') THEN
547 pvalue := x_item_rec.attribute14;
548 ELSIF (pcolname = 'ATTRIBUTE15') THEN
549 pvalue := x_item_rec.attribute15;
550 ELSIF (pcolname = 'ATTRIBUTE16') THEN
551 pvalue := x_item_rec.attribute16;
552 ELSIF (pcolname = 'ATTRIBUTE17') THEN
553 pvalue := x_item_rec.attribute17;
554 ELSIF (pcolname = 'ATTRIBUTE18') THEN
555 pvalue := x_item_rec.attribute18;
556 ELSIF (pcolname = 'ATTRIBUTE19') THEN
557 pvalue := x_item_rec.attribute19;
558 ELSIF (pcolname = 'ATTRIBUTE20') THEN
559 pvalue := x_item_rec.attribute20;
560 ELSIF (pcolname = 'ATTRIBUTE21') THEN
561 pvalue := x_item_rec.attribute21;
562 ELSIF (pcolname = 'ATTRIBUTE22') THEN
563 pvalue := x_item_rec.attribute22;
564 ELSIF (pcolname = 'ATTRIBUTE23') THEN
565 pvalue := x_item_rec.attribute23;
566 ELSIF (pcolname = 'ATTRIBUTE24') THEN
567 pvalue := x_item_rec.attribute24;
568 ELSIF (pcolname = 'ATTRIBUTE25') THEN
569 pvalue := x_item_rec.attribute25;
570 ELSIF (pcolname = 'ATTRIBUTE26') THEN
571 pvalue := x_item_rec.attribute26;
572 ELSIF (pcolname = 'ATTRIBUTE27') THEN
573 pvalue := x_item_rec.attribute27;
574 ELSIF (pcolname = 'ATTRIBUTE28') THEN
575 pvalue := x_item_rec.attribute28;
576 ELSIF (pcolname = 'ATTRIBUTE29') THEN
577 pvalue := x_item_rec.attribute29;
578 ELSIF (pcolname = 'ATTRIBUTE30') THEN
579 pvalue := x_item_rec.attribute30;
580 ELSE
581 pvalue := NULL;
582 END IF;
583
584
585
586 END GET_ATTRIBUTE_VALUE;
587
588
589 PROCEDURE GET_BATCH_NO
590 (pbatch_id IN NUMBER,
591 pbatch_no OUT NOCOPY VARCHAR2) IS
592
593 CURSOR get_batch_no IS
594 SELECT batch_no
595 FROM gme_batch_header
596 WHERE batch_id = pbatch_id;
597
598 BEGIN
599
600 OPEN get_batch_no;
601 FETCH get_batch_no INTO pbatch_no;
602 IF (get_batch_no%NOTFOUND) THEN
603 pbatch_no := ' ';
604 END IF;
605 CLOSE get_batch_no;
606
607 END GET_BATCH_NO;
608
609 PROCEDURE GET_HOLD_RELEASE_DATE
610 (pitem_id IN NUMBER,
611 plot_id IN NUMBER,
612 phold_date OUT NOCOPY DATE) IS
613
614 CURSOR get_hold_date IS
615 SELECT ic_hold_date
616 FROM ic_lots_cpg
617 WHERE item_id = pitem_id
618 AND lot_id = plot_id;
619
620 BEGIN
621 OPEN get_hold_date;
622 FETCH get_hold_date INTO phold_date;
623 IF (get_hold_date%NOTFOUND) THEN
624 phold_date := NULL;
625 END IF;
626 CLOSE get_hold_date;
627
628
629 END GET_HOLD_RELEASE_DATE;
630
631
632 END gmi_eres_utils;