1 PACKAGE BODY GMD_QC_ERES_UTILS AS
2 --$Header: GMDGEREB.pls 120.7.12020000.2 2012/07/17 10:22:35 mtou ship $
3 --Bug 3222090, magupta removed call to FND_PROFILE.VALUE('AFLOG_ENABLED')
4 --forward decl.
5 function set_debug_flag return varchar2;
6 --l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
7 l_debug VARCHAR2(1) := set_debug_flag;
8
9 FUNCTION set_debug_flag RETURN VARCHAR2 IS
10 l_debug VARCHAR2(1):= 'N';
11 BEGIN
12 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
13 l_debug := 'Y';
14 END IF;
15 RETURN l_debug;
16 END set_debug_flag;
17
18
19 -- Start of comments
20 --+==========================================================================+
21 --| Copyright (c) 1998 Oracle Corporation |
22 --| Redwood Shores, CA, USA |
23 --| All rights reserved. |
24 --+==========================================================================+
25 --| File Name : GMDVEREB.pls |
26 --| Package Name : GMD_QC_ERES_UTILS |
27 --| Type : Group |
28 --| |
29 --| Notes |
30 --| This package contains group layer APIs for Results Entity |
31 --| |
32 --| HISTORY |
33 --| Chetan Nagar 08-Aug-2002 Created. |
34 --| RLNAGARA 17-Nov-2005 Created 3 Procedures |
35 --| 1. GET_YES_NO |
36 --| 2. GET_ITEM_UOM_CALC |
37 --| 3. GET_DECIMAL_VALUE |
38 --| RLNAGARA 30-Nov-2005 Added NVL to the cursor in procedure |
39 --| GET_DECIMAL_VALUE |
40 --| RLNAGARA 30-Jan-2006 Bug # 4918840 Modified the cursors in the|
41 --| Procedures chek_spec_validity_eres, |
42 --| get_orgn_name and get_orgn_code |
43 --+==========================================================================+
44 -- End of comments
45
46
47 PROCEDURE set_spec_status(p_spec_id IN NUMBER,
48 p_from_status IN VARCHAR2,
49 p_to_status IN VARCHAR2) IS
50 l_signature_status VARCHAR2(40);
51 l_pending_status VARCHAR2(40);
52 l_rework_status VARCHAR2(40);
53 BEGIN
54
55
56 l_signature_status := EDR_PSIG_PAGE_FLOW.signature_status;
57
58 IF l_signature_status = 'SUCCESS' THEN
59 UPDATE gmd_specifications_b
60 SET spec_status = p_to_status
61 WHERE spec_id = p_spec_id;
62 ELSIF l_signature_status = 'PENDING' THEN
63 l_pending_status := GMD_QC_STATUS_NEXT_PVT.get_pending_status(p_from_status => p_from_status
64 ,p_to_status => p_to_status
65 ,p_entity_type => 'S');
66 IF l_pending_status IS NOT NULL THEN
67 UPDATE gmd_specifications_b
68 SET spec_status = l_pending_status
69 WHERE spec_id = p_spec_id;
70 END IF;
71 ELSIF l_signature_status = 'REJECTED' THEN
72 l_rework_status := GMD_QC_STATUS_NEXT_PVT.get_rework_status(p_from_status => p_from_status
73 ,p_to_status => p_to_status
74 ,p_entity_type => 'S');
75 IF l_rework_status IS NOT NULL THEN
76 UPDATE gmd_specifications_b
77 SET spec_status = l_rework_status
78 WHERE spec_id = p_spec_id;
79 END IF;
80 END IF;
81 END set_spec_status;
82
83
84 PROCEDURE set_spec_vr_status(p_spec_vr_id IN NUMBER,
85 p_entity_type IN VARCHAR2,
86 p_from_status IN VARCHAR2,
87 p_to_status IN VARCHAR2) IS
88 l_signature_status VARCHAR2(40);
89 l_pending_status VARCHAR2(40);
90 l_rework_status VARCHAR2(40);
91 BEGIN
92
93 l_signature_status := EDR_PSIG_PAGE_FLOW.signature_status;
94
95 IF l_signature_status = 'SUCCESS' THEN
96 update_vr_status(p_entity_type,
97 p_spec_vr_id,
98 p_to_status);
99 ELSIF l_signature_status = 'PENDING' THEN
100 l_pending_status := GMD_QC_STATUS_NEXT_PVT.get_pending_status(p_from_status => p_from_status
101 ,p_to_status => p_to_status
102 ,p_entity_type => 'S');
103 IF l_pending_status IS NOT NULL THEN
104 update_vr_status(p_entity_type,
105 p_spec_vr_id,
106 l_pending_status);
107 END IF;
108 ELSIF l_signature_status = 'REJECTED' THEN
109 l_rework_status := GMD_QC_STATUS_NEXT_PVT.get_rework_status(p_from_status => p_from_status
110 ,p_to_status => p_to_status
111 ,p_entity_type => 'S');
112 IF l_rework_status IS NOT NULL THEN
113 update_vr_status(p_entity_type,
114 p_spec_vr_id,
115 l_rework_status);
116 END IF;
117 END IF;
118 END set_spec_vr_status;
119 PROCEDURE update_vr_status(pentity_type IN VARCHAR2,
120 pspec_vr_id IN NUMBER,
121 p_to_status IN NUMBER) IS
122 BEGIN
123
124 IF (pentity_type = 'I') THEN
125 UPDATE gmd_inventory_spec_vrs
126 SET spec_vr_status = p_to_status
127 WHERE spec_vr_id = pspec_vr_id;
128 ELSIF(pentity_type = 'W') THEN
129 UPDATE gmd_wip_spec_vrs
130 SET spec_vr_status = p_to_status
131 WHERE spec_vr_id = pspec_vr_id;
132 ELSIF(pentity_type = 'C') THEN
133 UPDATE gmd_customer_spec_vrs
134 SET spec_vr_status = p_to_status
135 WHERE spec_vr_id = pspec_vr_id;
136 ELSIF(pentity_type = 'S') THEN
137 UPDATE gmd_supplier_spec_vrs
138 SET spec_vr_status = p_to_status
139 WHERE spec_vr_id = pspec_vr_id;
140 ELSIF(pentity_type = 'M') THEN
141 UPDATE gmd_monitoring_spec_vrs
142 SET spec_vr_status = p_to_status
143 WHERE spec_vr_id = pspec_vr_id;
144 END IF;
145
146 END update_vr_status;
147
148 FUNCTION chek_spec_validity_eres (p_spec_id IN NUMBER,
149 p_to_status IN VARCHAR2,
150 p_event IN VARCHAR2)
151 RETURN BOOLEAN IS
152 --RLNAGARA Bug # 4918840
153
154 CURSOR Cur_get_validity IS
155 SELECT v.spec_vr_id,'I' spec_type
156 FROM gmd_inventory_spec_vrs v
157 WHERE v.spec_id = p_spec_id
158 AND v.spec_vr_status < p_to_status
159 UNION
160 SELECT v.spec_vr_id,'W' spec_type
161 FROM gmd_wip_spec_vrs v
162 WHERE v.spec_id = p_spec_id
163 AND v.spec_vr_status < p_to_status
164 UNION
165 SELECT v.spec_vr_id,'C' spec_type
166 FROM gmd_customer_spec_vrs v
167 WHERE v.spec_id = p_spec_id
168 AND v.spec_vr_status < p_to_status
169 UNION
170 SELECT v.spec_vr_id,'S' spec_type
171 FROM gmd_supplier_spec_vrs v
172 WHERE v.spec_id = p_spec_id
173 AND v.spec_vr_status < p_to_status
174 UNION
175 SELECT v.spec_vr_id,v.rule_type
176 FROM gmd_monitoring_spec_vrs v
177 WHERE v.spec_id = p_spec_id
178 AND v.spec_vr_status < p_to_status
179 UNION
180 SELECT v.spec_vr_id,'T' spec_type
181 FROM gmd_stability_spec_vrs v
182 WHERE v.spec_id = p_spec_id
183 AND v.spec_vr_status < p_to_status;
184
185 --RLNAGARA Bug # 4918840
186
187 l_spec_validity_rule_id NUMBER;
188 l_status BOOLEAN;
189 l_spec_type VARCHAR2(2);
190 l_event_name VARCHAR2(40);
191 PRAGMA AUTONOMOUS_TRANSACTION;
192 BEGIN
193
194 OPEN Cur_get_validity;
195 FETCH Cur_get_validity INTO l_spec_validity_rule_id,l_spec_type;
196 WHILE Cur_get_validity%FOUND LOOP
197 update_vr_status(pentity_type => l_spec_type,
198 pspec_vr_id => l_spec_validity_rule_id,
199 p_to_status => p_to_status);
200 SELECT DECODE(l_spec_type,'I','oracle.apps.gmd.qm.spec.vr.inv',
201 'W','oracle.apps.gmd.qm.spec.vr.wip',
202 'C','oracle.apps.gmd.qm.spec.vr.cus',
203 'S','oracle.apps.gmd.qm.spec.vr.sup',
204 'R','oracle.apps.gmd.qm.spec.vr.mon',
205 'L','oracle.apps.gmd.qm.spec.vr.mon')
206 INTO l_event_name
207 FROM sys.dual;
208 EDR_STANDARD.psig_required (p_event => l_event_name
209 ,p_event_key => l_spec_validity_rule_id
210 ,p_status => l_status);
211
212 IF l_status THEN
213 ROLLBACK;
214 CLOSE Cur_get_validity;
215 RETURN TRUE;
216 END IF;
217 FETCH Cur_get_validity INTO l_spec_validity_rule_id,l_spec_type;
218 END LOOP;
219 ROLLBACK;
220 CLOSE Cur_get_validity;
221 RETURN FALSE;
222 END chek_spec_validity_eres;
223
224 FUNCTION esig_required (p_event IN VARCHAR2,
225 p_event_key IN VARCHAR2,
226 p_to_status IN VARCHAR2)
227 RETURN BOOLEAN IS
228 l_status BOOLEAN;
229 -- PRAGMA AUTONOMOUS_TRANSACTION;
230 BEGIN
231
232 /* Lets first update the status of the entity to to status */
233 IF p_event = 'oracle.apps.gmd.qm.spec.vr.inv' THEN
234 UPDATE gmd_inventory_spec_vrs
235 SET spec_vr_status = p_to_status
236 WHERE spec_vr_id = p_event_key
237 AND spec_vr_status <> p_to_status;
238 ELSIF p_event = 'oracle.apps.gmd.qm.spec.vr.wip' THEN
239 UPDATE gmd_wip_spec_vrs
240 SET spec_vr_status = p_to_status
241 WHERE spec_vr_id = p_event_key
242 AND spec_vr_status <> p_to_status;
243 ELSIF p_event = 'oracle.apps.gmd.qm.spec.vr.cus' THEN
244
245 UPDATE gmd_customer_spec_vrs
246 SET spec_vr_status = p_to_status
247 WHERE spec_vr_id = p_event_key
248 AND spec_vr_status <> p_to_status;
249 ELSIF p_event = 'oracle.apps.gmd.qm.spec.vr.sup' THEN
250 UPDATE gmd_supplier_spec_vrs
251 SET spec_vr_status = p_to_status
252 WHERE spec_vr_id = p_event_key
253 AND spec_vr_status <> p_to_status;
254 ELSIF p_event = 'oracle.apps.gmd.qm.spec' THEN
255 UPDATE gmd_specifications_b
256 SET spec_status = p_to_status
257 WHERE spec_id = p_event_key
258 AND spec_status <> p_to_status;
259 END IF;
260
261 GMA_STANDARD.psig_required (p_event => p_event
262 ,p_event_key => p_event_key
263 ,p_status => l_status);
264 --ROLLBACK;
265 IF l_status THEN
266 RETURN TRUE;
267 ELSE
268 RETURN FALSE;
269 END IF;
270
271 exception
272 when others then
273 raise;
274 END esig_required;
275
276 -- INVCONV, NSRIVAST, START
277 PROCEDURE get_orgn_name(p_orgn_code VARCHAR2,
278 p_orgn_name OUT NOCOPY VARCHAR2) IS
279 --RLNAGARA Bug # 4918840
280 CURSOR Cur_get_organization IS
281 SELECT hr.NAME
282 FROM MTL_parameters mp, hr_all_organization_units hr
283 WHERE mp.ORGANIZATION_CODE =p_orgn_code
284 and hr.organization_id = mp.organization_id;
285
286
287 BEGIN
288 OPEN Cur_get_organization;
289 FETCH Cur_get_organization INTO P_orgn_name;
290 CLOSE Cur_get_organization;
291 END get_orgn_name;
292 -- INVCONV, NSRIVAST, END
293 PROCEDURE get_user_name(p_user_id VARCHAR2,
294 p_user_name OUT NOCOPY VARCHAR2) IS
295 CURSOR Cur_get_user_name IS
296 SELECT USER_NAME
297 FROM FND_USER
298 WHERE user_id = p_user_id;
299 BEGIN
300 OPEN Cur_get_user_name;
301 FETCH Cur_get_user_name INTO P_user_name;
302 CLOSE Cur_get_user_name;
303 END get_user_name;
304 PROCEDURE get_test_method_code(p_test_method_id VARCHAR2,
305 p_test_method_code OUT NOCOPY VARCHAR2) IS
306 CURSOR Cur_get_test_method IS
307 SELECT TEST_METHOD_CODE
308 FROM gmd_test_methods
309 WHERE TEST_METHOD_ID = p_test_method_id;
310 BEGIN
311 OPEN Cur_get_test_method;
312 FETCH Cur_get_test_method INTO p_test_method_code;
313 CLOSE Cur_get_test_method;
314 END get_test_method_code;
315 PROCEDURE get_test_method_desc(p_test_method_id VARCHAR2,
316 p_test_method_desc OUT NOCOPY VARCHAR2) IS
317 CURSOR Cur_get_test_method IS
318 SELECT TEST_METHOD_DESC
319 FROM gmd_test_methods
320 WHERE TEST_METHOD_ID = p_test_method_id;
321 BEGIN
322 OPEN Cur_get_test_method;
323 FETCH Cur_get_test_method INTO p_test_method_desc;
324 CLOSE Cur_get_test_method;
325 END get_test_method_desc;
326
327
328 PROCEDURE get_cust_name
329 (
330 p_cust_id IN NUMBER
331 , x_cust_name OUT NOCOPY VARCHAR2
332 ) IS
333
334 CURSOR c1 IS
335 SELECT
336 hzp.party_name
337 FROM
338 hz_parties hzp
339 , hz_cust_accounts_all hzca
340 WHERE hzp.party_id = hzca.party_id
341 AND hzca.cust_account_id = p_cust_id
342 ;
343
344 BEGIN
345 OPEN c1;
346 FETCH c1 INTO x_cust_name;
347 CLOSE c1;
348 END;
349
350
351
352 PROCEDURE get_org_name
353 (
354 p_org_id IN NUMBER
355 , x_org_name OUT NOCOPY VARCHAR2
356 ) IS
357
358 CURSOR c1 IS
359 SELECT name
360 FROM hr_operating_units
361 WHERE organization_id = p_org_id
362 ;
363
364 BEGIN
365 OPEN c1;
366 FETCH c1 INTO x_org_name;
367 CLOSE c1;
368 END;
369
370
371 PROCEDURE get_ship_to_site_name
372 (
373 p_ship_to_site_id IN NUMBER
374 , x_ship_to_site_name OUT NOCOPY VARCHAR2
375 ) IS
376
377 CURSOR c1 IS
378 SELECT location
379 FROM hz_cust_site_uses_all
380 WHERE site_use_id = p_ship_to_site_id
381 ;
382
383 BEGIN
384 OPEN c1;
385 FETCH c1 INTO x_ship_to_site_name;
386 CLOSE c1;
387 END;
388
389
390 PROCEDURE get_order_number
391 (
392 p_order_id IN NUMBER
393 , x_order_number OUT NOCOPY NUMBER
394 ) IS
395
396 CURSOR c1 IS
397 SELECT order_number
398 FROM oe_order_headers_all
399 WHERE header_id = p_order_id
400 ;
401
402 BEGIN
403 OPEN c1;
404 FETCH c1 INTO x_order_number;
405 CLOSE c1;
406 END;
407
408 PROCEDURE get_order_type
409 (
410 p_order_id IN NUMBER
411 , x_order_type OUT NOCOPY VARCHAR2
412 ) IS
413
414 CURSOR c1 IS
415 SELECT b.name
416 FROM oe_order_headers_all a, oe_transaction_types_tl b
417 WHERE a.order_type_id = b.transaction_type_id
418 AND a.header_id = p_order_id
419 ;
420
421 BEGIN
422 OPEN c1;
423 FETCH c1 INTO x_order_type;
424 CLOSE c1;
425 END;
426
427 PROCEDURE get_order_line
428 (
429 p_order_line_id IN NUMBER
430 , x_order_line_number OUT NOCOPY NUMBER
431 ) IS
432
433 CURSOR c1 IS
434 SELECT line_number
435 FROM oe_order_lines_all
436 WHERE line_id = p_order_line_id
437 ;
438
439 BEGIN
440 OPEN c1;
441 FETCH c1 INTO x_order_line_number;
442 CLOSE c1;
443 END;
444
445 PROCEDURE get_supp_code
446 (
447 p_supp_id IN NUMBER
448 , x_supp_code OUT NOCOPY VARCHAR2
449 ) IS
450
451 CURSOR c1 IS
452 SELECT segment1
453 FROM po_vendors
454 WHERE vendor_id = p_supp_id
455 ;
456
457 BEGIN
458 OPEN c1;
459 FETCH c1 INTO x_supp_code;
460 CLOSE c1;
461 END;
462
463
464 PROCEDURE get_supp_name
465 (
466 p_supp_id IN NUMBER
467 , x_supp_name OUT NOCOPY VARCHAR2
468 ) IS
469 CURSOR c1 IS
470 SELECT vendor_name
471 FROM po_vendors
472 WHERE vendor_id = p_supp_id
473 ;
474
475 BEGIN
476 OPEN c1;
477 FETCH c1 INTO x_supp_name;
478 CLOSE c1;
479 END;
480
481 PROCEDURE get_supp_site_name
482 (
483 p_supp_site_id IN NUMBER
484 , x_supp_site_name OUT NOCOPY VARCHAR2
485 ) IS
486
487 CURSOR c1 IS
488 SELECT vendor_site_code
489 FROM po_vendor_sites_all
490 WHERE vendor_site_id = p_supp_site_id
491 ;
492
493 BEGIN
494 OPEN c1;
495 FETCH c1 INTO x_supp_site_name;
496 CLOSE c1;
497 END;
498
499 PROCEDURE get_po_number
500 (
501 p_po_id IN NUMBER
502 , x_po_number OUT NOCOPY NUMBER
503 ) IS
504
505 CURSOR c1 IS
506 SELECT segment1
507 FROM po_headers_all
508 WHERE po_header_id = p_po_id
509 ;
510
511 BEGIN
512 OPEN c1;
513 FETCH c1 INTO x_po_number;
514 CLOSE c1;
515 END;
516
517 PROCEDURE get_po_line_number
518 (
519 p_po_line_id IN NUMBER
520 , x_po_line_number OUT NOCOPY NUMBER
521 ) IS
522
523 CURSOR c1 IS
524 SELECT line_num
525 FROM po_lines_all
526 WHERE po_line_id = p_po_line_id
527 ;
528
529 BEGIN
530 OPEN c1;
531 FETCH c1 INTO x_po_line_number;
532 CLOSE c1;
533 END;
534
535
536 PROCEDURE get_receipt_number
537 (
538 p_receipt_id IN NUMBER
539 , x_receipt_number OUT NOCOPY NUMBER
540 ) IS
541
542 CURSOR c1 IS
543 SELECT receipt_num
544 FROM rcv_shipment_headers
545 WHERE shipment_header_id = p_receipt_id
546 ;
547
548 BEGIN
549 OPEN c1;
550 FETCH c1 INTO x_receipt_number;
551 CLOSE c1;
552 END;
553
554 PROCEDURE get_receipt_line_number
555 (
556 p_receipt_line_id IN NUMBER
557 , x_receipt_line_number OUT NOCOPY NUMBER
558 ) IS
559
560 CURSOR c1 IS
561 SELECT line_num
562 FROM rcv_shipment_lines
563 WHERE shipment_line_id = p_receipt_line_id
564 ;
565
566 BEGIN
567 OPEN c1;
568 FETCH c1 INTO x_receipt_line_number;
569 CLOSE c1;
570 END;
571
572 PROCEDURE get_status_code_meaning
573 (
574 p_status_code IN NUMBER
575 , p_entity_type IN VARCHAR2
576 , x_status_meaning OUT NOCOPY VARCHAR2
577 ) IS
578
579 CURSOR c1 IS
580 SELECT meaning
581 FROM gmd_qc_status
582 WHERE status_code = p_status_code
583 AND entity_type = p_entity_type
584 ;
585
586 BEGIN
587 OPEN c1;
588 FETCH c1 INTO x_status_meaning;
589 CLOSE c1;
590 END;
591
592 -- added by mahesh to support stability study.
593
594 PROCEDURE set_stability_status( p_ss_id IN NUMBER,
595 p_from_status IN VARCHAR2,
596 p_to_status IN VARCHAR2) IS
597 l_signature_status VARCHAR2(40);
598 l_pending_status VARCHAR2(40);
599 l_rework_status VARCHAR2(40);
600 l_return_status VARCHAR2(1);
601
602 BEGIN
603
604 l_signature_status := EDR_PSIG_PAGE_FLOW.signature_status;
605
606 IF l_signature_status = 'SUCCESS' THEN
607 UPDATE gmd_stability_studies_b
608 SET status = p_to_status
609 WHERE ss_id = p_ss_id;
610
611 IF (p_to_status = 400) THEN
612 -- We got approved, so kick off API to create sampling events
613 GMD_SS_WFLOW_GRP.events_for_status_change(p_ss_id ,l_return_status) ;
614 ELSIF (p_to_status = 700) THEN
615 -- We need to launch; Enable the Mother workflow for testing
616 GMD_API_PUB.RAISE('oracle.apps.gmd.qm.ss.test',p_ss_id);
617 END IF;
618
619 ELSIF l_signature_status = 'PENDING' THEN
620 l_pending_status := GMD_QC_STATUS_NEXT_PVT.get_pending_status(p_from_status => p_from_status
621 ,p_to_status => p_to_status
622 ,p_entity_type => 'STABILITY');
623 IF l_pending_status IS NOT NULL THEN
624 UPDATE gmd_stability_studies_b
625 SET status = l_pending_status
626 WHERE ss_id = p_ss_id;
627 END IF;
628 ELSIF l_signature_status = 'REJECTED' THEN
629 l_rework_status := GMD_QC_STATUS_NEXT_PVT.get_rework_status(p_from_status => p_from_status
630 ,p_to_status => p_to_status
631 ,p_entity_type => 'STABILITY');
632 IF l_rework_status IS NOT NULL THEN
633 UPDATE gmd_stability_studies_b
634 SET status = l_rework_status
635 WHERE ss_id = p_ss_id;
636 END IF;
637 END IF;
638 END set_stability_status ;
639
640
641
642
643 PROCEDURE get_test_desc
644 (
645 p_test_id IN NUMBER
646 , x_test_desc OUT NOCOPY VARCHAR2
647 ) IS
648
649 CURSOR c1 IS
650 select test_desc
651 from gmd_qc_tests
652 where test_id = p_test_id;
653
654 begin
655 OPEN c1;
656 FETCH c1 INTO x_test_desc;
657 CLOSE c1;
658
659 end;
660
661
662 /* Get Stability study time unit */
663 PROCEDURE get_ss_time_unit
664 (
665 p_time IN VARCHAR2
666 , x_time_unit OUT NOCOPY VARCHAR2
667 ) IS
668
669 CURSOR c1 IS
670 SELECT meaning
671 FROM gem_lookups
672 WHERE lookup_type = 'GMD_QC_FREQUENCY_PERIOD'
673 and lookup_code = p_time ;
674
675 begin
676 OPEN c1;
677 FETCH c1 INTO x_time_unit;
678 CLOSE c1;
679
680 end;
681
682
683 -- INVCONV, NSRIVAST, START
684 PROCEDURE get_orgn_code(p_orgn_id VARCHAR2,
685 p_orgn_code OUT NOCOPY VARCHAR2) IS
686 -- RLNAGARA BUG # 4918840
687 CURSOR Cur_get_organization IS
688 SELECT ORGANIZATION_CODE
689 FROM MTL_PARAMETERS
690 WHERE ORGANIZATION_ID = p_orgn_id;
691 BEGIN
692 OPEN Cur_get_organization;
693 FETCH Cur_get_organization INTO p_orgn_code;
694 CLOSE Cur_get_organization;
695 END get_orgn_code;
696
697 PROCEDURE get_item_number(p_item_id VARCHAR2,
698 p_org_id VARCHAR2,
699 p_item_no OUT NOCOPY VARCHAR2) IS
700 CURSOR cur_get_item_no IS
701 SELECT concatenated_segments
702 FROM mtl_system_items_kfv
703 WHERE inventory_item_Id = p_item_id
704 AND ORGANIZATION_ID = p_org_id;
705 BEGIN
706 OPEN cur_get_item_no;
707 FETCH cur_get_item_no INTO p_item_no;
708 CLOSE cur_get_item_no;
709 END get_item_number;
710
711 PROCEDURE get_item_number1(p_item_id VARCHAR2,
712 p_item_no OUT NOCOPY VARCHAR2) IS
713 CURSOR cur_get_item_no IS
714 SELECT distinct concatenated_segments
715 FROM mtl_system_items_kfv
716 WHERE inventory_item_Id = p_item_id ;
717 BEGIN
718 OPEN cur_get_item_no;
719 FETCH cur_get_item_no INTO p_item_no;
720 CLOSE cur_get_item_no;
721 END get_item_number1;
722
723 PROCEDURE get_item_desc(p_item_id VARCHAR2,
724 p_org_id VARCHAR2,
725 p_item_desc OUT NOCOPY VARCHAR2) IS
726 CURSOR cur_get_item_desc IS
727 SELECT description
728 FROM mtl_system_items_kfv
729 WHERE inventory_item_Id = p_item_id
730 AND ORGANIZATION_ID = p_org_id;
731 BEGIN
732 OPEN cur_get_item_desc;
733 FETCH cur_get_item_desc INTO p_item_desc;
734 CLOSE cur_get_item_desc;
735 END get_item_desc;
736
737
738 PROCEDURE get_spec_name(p_spec_id VARCHAR2,
739 p_spec_name OUT NOCOPY VARCHAR2) IS
740 CURSOR cur_get_spec_name IS
741 SELECT SPEC_NAME
742 FROM gmd_specifications
743 WHERE SPEC_ID = p_spec_id ;
744 BEGIN
745 OPEN cur_get_spec_name;
746 FETCH cur_get_spec_name INTO p_spec_name;
747 CLOSE cur_get_spec_name;
748 END get_spec_name;
749
750 PROCEDURE get_spec_vers(p_spec_id VARCHAR2,
751 p_spec_vers OUT NOCOPY VARCHAR2) IS
752 CURSOR cur_get_spec_vers IS
753 SELECT SPEC_VERS
754 FROM gmd_specifications
755 WHERE SPEC_ID = p_spec_id ;
756 BEGIN
757 OPEN cur_get_spec_vers;
758 FETCH cur_get_spec_vers INTO p_spec_vers;
759 CLOSE cur_get_spec_vers;
760 END get_spec_vers;
761
762 PROCEDURE get_item_uom (p_item_id VARCHAR2,
763 p_org_id VARCHAR2,
764 p_item_uom OUT NOCOPY VARCHAR2) IS
765 CURSOR cur_item_uom IS
766 SELECT primary_uom_code
767 FROM mtl_system_items
768 WHERE inventory_item_Id = p_item_id
769 AND ORGANIZATION_ID = p_org_id;
770 BEGIN
771 OPEN cur_item_uom;
772 FETCH cur_item_uom INTO p_item_uom;
773 CLOSE cur_item_uom;
774 END get_item_uom;
775
776 PROCEDURE get_spec_type (p_spec_type_ind VARCHAR2,
777 p_spec_type OUT NOCOPY VARCHAR2) IS
778 BEGIN
779 IF p_spec_type_ind = 'I' THEN
780 p_spec_type := 'Item' ; --Changed from Inventory to Item --Bug # 4706847
781 END IF ;
782 IF p_spec_type_ind = 'M' THEN
783 p_spec_type := 'Monitoring' ;
784 END IF ;
785 END get_spec_type;
786
787
788 PROCEDURE get_loc_name (p_loc_id VARCHAR2,
789 p_loc_name OUT NOCOPY VARCHAR2) IS
790 CURSOR cur_loc_name IS
791 SELECT concatenated_segments
792 FROM mtl_item_locations_kfv
793 WHERE inventory_location_id = p_loc_id ;
794 BEGIN
795 OPEN cur_loc_name;
796 FETCH cur_loc_name INTO p_loc_name;
797 CLOSE cur_loc_name;
798 END get_loc_name;
799
800
801 PROCEDURE GET_LOOKUP_VALUE (plookup_type IN VARCHAR2,
802 plookup_code IN VARCHAR2,
803 pmeaning OUT NOCOPY VARCHAR2) IS
804
805 CURSOR get_lookup IS
806 SELECT meaning
807 FROM fnd_lookup_values_vl
808 WHERE lookup_type = plookup_type
809 AND lookup_code = plookup_code;
810
811 BEGIN
812
813 OPEN get_lookup;
814 FETCH get_lookup INTO pmeaning;
815 IF (get_lookup%NOTFOUND) THEN
816 pmeaning := ' ';
817 END IF;
818 CLOSE get_lookup;
819
820 END GET_LOOKUP_VALUE;
821
822 -- INVCONV, NSRIVAST, END
823
824 /*=============================================================================+
825 Procedure Name : GET_YES_NO
826
827 Purpose :
828 This procedure is used when we need to display Yes or No for a
829 particular field in the ERES document and when the fields stored
830 database value is something like Y,N,1,0 or NULL.
831 Input :
832 p_short -- the short forms that is stored in the database
833 like 'Y','N','0' etc.
834 Output :
835 x_expanded -- 'Yes' or 'No' based on input
836
837 HISTORY
838 RLNAGARA 17-Nov-2005 Created
839 ==============================================================================*/
840 PROCEDURE GET_YES_NO(p_short IN VARCHAR2 ,
841 x_expanded OUT NOCOPY VARCHAR2) IS
842 BEGIN
843 IF p_short IS NULL OR p_short = '0' OR p_short = 'N' THEN
844 x_expanded := 'No';
845 END IF; --p_short = 0
846
847 IF p_short ='1' OR p_short ='Y' THEN
848 x_expanded := 'Yes';
849 END IF; --p_short = 1
850
851 END GET_YES_NO;
852
853
854 /*=============================================================================+
855 Procedure Name : GET_ITEM_UOM_CALC
856
857 Purpose :
858 This procedure is used for the ERES in which the From_UOM field
859 is to be displayed based on the calc_uom_conv_ind.
860
861 HISTORY
862 RLNAGARA 17-Nov-2005 Created
863 ==============================================================================*/
864 PROCEDURE GET_ITEM_UOM_CALC ( p_item_id IN VARCHAR2
865 , p_organization_id IN VARCHAR2
866 , p_spec_id IN VARCHAR2
867 , p_test_id IN VARCHAR2
868 , x_item_uom OUT NOCOPY VARCHAR2) IS
869 CURSOR calc_uom_conv_ind_cur IS
870 SELECT CALC_UOM_CONV_IND
871 FROM GMD_SPEC_TESTS
872 WHERE SPEC_ID = p_spec_id
873 AND TEST_ID = p_test_id;
874
875 CURSOR cur_item_uom_cur IS
876 SELECT primary_uom_code
877 FROM mtl_system_items
878 WHERE inventory_item_Id = p_item_id
879 AND ORGANIZATION_ID = p_organization_id;
880
881 l_uom_conv VARCHAR2(10) := NULL;
882 x_uom_conv VARCHAR2(10) := NULL;
883 BEGIN
884 OPEN calc_uom_conv_ind_cur;
885 FETCH calc_uom_conv_ind_cur INTO l_uom_conv;
886 CLOSE calc_uom_conv_ind_cur;
887
888 GET_YES_NO(l_uom_conv,x_uom_conv);
889
890 IF x_uom_conv = 'Yes' THEN
891 OPEN cur_item_uom_cur;
892 FETCH cur_item_uom_cur INTO x_item_uom;
893 CLOSE cur_item_uom_cur;
894 END IF; --x_uom_lov = Yes
895
896 END GET_ITEM_UOM_CALC;
897
898 /*=============================================================================+
899 Procedure Name : GET_DECIMAL_VALUE
900
901 Purpose :
902 This procedure is used for showing decimal places for any Numeric Fields
903 in the ERES Document.
904 Input :
905 p_value -- Value stored in the database
906 p_test_qty -- 'T' for Test or 'Q' for Quantity fields
907 p_test_id -- If p_test_qty = 'T' then the test_id should be passed and
908 if test_id is not passed or passed wrong then
909 default decimal precision is taken as 9.
910 Else(p_test_qty='Q')
911 this value can be NULL as it is not used anywhere in
912 procedure as default decimal precision is taken as 5.
913 x_decimal_value -- This is the final value which is returned with proper
914 decimal precision.
915
916
917 HISTORY
918 RLNAGARA 17-Nov-2005 Created
919 RLNAGARA 30-Nov-2005 Added NVL to the cursor.
920 QZENG 13798271 22-Mar-2012 Modified cursor get_decimal_precision, change VL(REPORT_PRECISION,0) to
921 VL(REPORT_PRECISION,-1) to support blank precision, then return the real p_value
922 QZENG 13798271 09-Apr-2012 Round the p_value with its report_precision
923 ==============================================================================*/
924 PROCEDURE GET_DECIMAL_VALUE(p_value IN VARCHAR2
925 , p_test_qty IN VARCHAR2
926 , p_test_id IN VARCHAR2
927 , x_decimal_value OUT NOCOPY VARCHAR2 ) IS
928 CURSOR get_decimal_precision IS
929 --Commented out by QZENG Bug
930 --SELECT NVL(REPORT_PRECISION, 0)
931 SELECT NVL(REPORT_PRECISION,-1)
932 FROM GMD_QC_TESTS
933 WHERE TEST_ID = p_test_id;
934 l_value VARCHAR2(50) := NULL;
935 l_decimal_precision NUMBER := 9;
936 l_dot_precision NUMBER := 0;
937 l_total_length NUMBER := 0;
938 l_decimal_length NUMBER := 0;
939 l_extra_decimal NUMBER := 0;
940 l_final_length NUMBER := 0;
941 BEGIN
942
943 IF p_test_qty = 'Q' THEN
944 l_decimal_precision := 5;
945 ELSIF p_test_qty = 'T' THEN
946 OPEN get_decimal_precision;
947 FETCH get_decimal_precision INTO l_decimal_precision;
948 CLOSE get_decimal_precision;
949 END IF;
950
951 --QZENG bug 13798271 Set x_decimal_value to p_value when no precision found.
952 IF l_decimal_precision = -1 THEN
953 x_decimal_value := p_value;
954 ELSE
955 --QZENG bug 13798271 Round the value, and set value to l_value
956 l_value := to_char(round(to_number(p_value), l_decimal_precision));
957 --SELECT LENGTH(p_value) INTO l_total_length FROM DUAL;
958 SELECT LENGTH(l_value) INTO l_total_length FROM DUAL;
959 --SELECT INSTR(p_value,'.') INTO l_dot_precision FROM DUAL;
960 SELECT INSTR(l_value,'.') INTO l_dot_precision FROM DUAL;
961 IF l_dot_precision > 0 THEN
962 l_decimal_length := l_total_length - l_dot_precision;
963 ELSE
964 l_decimal_length := 0;
965 END IF;
966 --QZENG bug 13798271 If l_decimal_precision is 0, then retieve integer number to x_decimal_value.
967 IF l_decimal_precision = 0 THEN
968 IF l_decimal_length = 0 THEN
969 --SELECT SUBSTR(p_value, 0) INTO x_decimal_value FROM DUAL;
970 SELECT SUBSTR(l_value, 0) INTO x_decimal_value FROM DUAL;
971 ELSE
972 IF l_dot_precision = 1 THEN
973 x_decimal_value := '0';
974 ELSE
975 --SELECT SUBSTR(p_value, 0, l_decimal_length - 1) INTO x_decimal_value FROM DUAL;
976 SELECT SUBSTR(l_value, 0, l_dot_precision - 1) INTO x_decimal_value FROM DUAL;
977 END IF;
978 END IF;
979 ELSE
980
981 l_extra_decimal := l_decimal_precision - l_decimal_length;
982 --QZENG bug 13798271 set value already.
983 --l_value := p_value;
984 IF l_dot_precision = 0 THEN
985 l_total_length := l_total_length + 1;
986 --SELECT RPAD(p_value,l_total_length,'.') INTO l_value FROM DUAL;
987 SELECT RPAD(l_value,l_total_length,'.') INTO l_value FROM DUAL;
988 END IF;
989 l_final_length := l_total_length + l_extra_decimal;
990 SELECT RPAD(l_value,l_final_length,'0') INTO x_decimal_value FROM DUAL;
991 END IF;
992 END IF;
993 --QZENG bug 13798271 consider exception.
994 EXCEPTION
995 WHEN INVALID_NUMBER THEN
996 x_decimal_value := p_value;
997 WHEN OTHERS THEN
998 x_decimal_value := p_value;
999 END GET_DECIMAL_VALUE;
1000
1001 /* Bug 5023089. RLNAGARA 09-Mar-2006. To display the from and to Sample's disposition */
1002
1003 PROCEDURE get_disp_meaning(p_sample_id IN NUMBER,
1004 p_sampling_event_id IN NUMBER,
1005 p_organization_id IN NUMBER,
1006 p_disp_type IN VARCHAR2,
1007 psample_disposition OUT NOCOPY VARCHAR2 ) IS
1008
1009 CURSOR get_chng_id IS
1010 select max(change_disp_id)
1011 from GMD_CHANGE_DISPOSITION
1012 where sampling_event_id = p_sampling_event_id
1013 and organization_id = p_organization_id
1014 group by organization_id;
1015
1016 CURSOR get_chng_id_smpl IS
1017 select max(change_disp_id)
1018 from GMD_CHANGE_DISPOSITION
1019 where sample_id = p_sample_id
1020 and sampling_event_id = p_sampling_event_id
1021 and organization_id = p_organization_id
1022 group by organization_id;
1023
1024 CURSOR get_from_disp(p_change_disp_id IN NUMBER) IS
1025 select gl.meaning
1026 from gmd_change_disposition gcd ,gem_lookups gl
1027 where gl.lookup_type = 'GMD_QC_SAMPLE_DISP'
1028 and gl.lookup_code = gcd.disposition_from
1029 and gcd.change_disp_id = p_change_disp_id;
1030
1031 CURSOR get_to_disp(p_change_disp_id IN NUMBER) IS
1032 select gl.meaning
1033 from gmd_change_disposition gcd ,gem_lookups gl
1034 where gl.lookup_type = 'GMD_QC_SAMPLE_DISP'
1035 and gl.lookup_code = gcd.disposition_to
1036 and gcd.change_disp_id = p_change_disp_id;
1037
1038 l_chng_disp_id NUMBER;
1039
1040 BEGIN
1041 IF p_sample_id IS NULL THEN
1042 OPEN get_chng_id;
1043 FETCH get_chng_id INTO l_chng_disp_id;
1044 IF (get_chng_id%NOTFOUND) THEN
1045 l_chng_disp_id := 0;
1046 END IF;
1047 CLOSE get_chng_id;
1048 ELSE
1049 OPEN get_chng_id_smpl;
1050 FETCH get_chng_id_smpl INTO l_chng_disp_id;
1051 IF (get_chng_id_smpl%NOTFOUND) THEN
1052 l_chng_disp_id := 0;
1053 END IF;
1054 CLOSE get_chng_id_smpl;
1055 END IF;
1056
1057 IF p_disp_type = 'FROM' THEN
1058 OPEN get_from_disp(l_chng_disp_id);
1059 FETCH get_from_disp INTO psample_disposition;
1060 IF (get_from_disp%NOTFOUND) THEN
1061 psample_disposition := ' ';
1062 END IF;
1063 CLOSE get_from_disp;
1064 ELSIF p_disp_type = 'TO' THEN
1065 OPEN get_to_disp(l_chng_disp_id);
1066 FETCH get_to_disp INTO psample_disposition;
1067 IF (get_to_disp%NOTFOUND) THEN
1068 psample_disposition := ' ';
1069 END IF;
1070 CLOSE get_to_disp;
1071 END IF;
1072
1073 END get_disp_meaning;
1074
1075
1076
1077 /*=============================================================================+
1078 Procedure Name : GET_COMPOSITED
1079
1080 Purpose :
1081 This procedure is used for knowing whether the sample is composited or not.
1082 Input :
1083 p_event_spec_disp_id -- EVENT_SPEC_DISP_ID
1084 Output :
1085 x_composited -- This returns Yes or No based on whether sample is
1086 composited or not.
1087
1088
1089 HISTORY
1090 RLNAGARA 18-Apr-2006 Created
1091 ==============================================================================*/
1092 PROCEDURE get_composited(p_event_spec_disp_id IN NUMBER,
1093 x_composited OUT NOCOPY VARCHAR2) IS
1094
1095 CURSOR check_composite IS
1096 SELECT 'Y'
1097 FROM gmd_composite_spec_disp
1098 WHERE event_spec_disp_id = p_event_spec_disp_id;
1099
1100 l_composited_flag VARCHAR2(20);
1101
1102 BEGIN
1103
1104 OPEN check_composite;
1105 FETCH check_composite INTO l_composited_flag;
1106 IF check_composite%FOUND THEN
1107 x_composited := 'Yes';
1108 ELSE
1109 x_composited := 'No';
1110 END IF;
1111 CLOSE check_composite;
1112
1113 END;
1114
1115 /*=============================================================================+
1116 Procedure Name : get_lpn
1117
1118 Purpose :
1119 This procedure get the LPN using LPN_ID.
1120 Input :
1121 p_lpn_id -- LPN_ID
1122 Output :
1123 x_lpn -- LICENSE_PLATE_NUMBER
1124
1125 HISTORY
1126 RLNAGARA LPN ME 7027149 14-May-2008 Created
1127 ==============================================================================*/
1128 PROCEDURE get_lpn(p_lpn_id IN NUMBER,
1129 x_lpn OUT NOCOPY VARCHAR2) IS
1130
1131 CURSOR c_get_lpn IS
1132 SELECT license_plate_number
1133 FROM wms_license_plate_numbers
1134 WHERE lpn_id = p_lpn_id;
1135
1136 BEGIN
1137 IF p_lpn_id IS NOT NULL THEN
1138 OPEN c_get_lpn;
1139 FETCH c_get_lpn INTO x_lpn;
1140 CLOSE c_get_lpn;
1141 ELSE
1142 x_lpn := NULL;
1143 END IF;
1144
1145 END get_lpn;
1146
1147
1148 end GMD_QC_ERES_UTILS;
1149
[Home] [Help]