1 PACKAGE BODY QA_FLEX_UTIL AS
2 /* $Header: qltutlfb.plb 120.7.12000000.2 2007/07/17 10:18:23 bhsankar ship $ */
3
4
5 FUNCTION item2(p_org_id NUMBER, p_item_id NUMBER) RETURN VARCHAR2 IS
6 -- ERES project, tracking bug 3071511.
7 -- This function differs from item() because it does not raise
8 -- exception if ID is not found.
9 --
10 -- Return item name from mtl_system_items.
11 -- Return null if not found.
12 --
13 l_value varchar2(2000);
14 CURSOR c IS
15 SELECT concatenated_segments
16 FROM mtl_system_items_kfv
17 WHERE inventory_item_id = p_item_id AND
18 organization_id = p_org_id;
19
20 BEGIN
21 IF p_org_id IS NULL OR p_item_id IS NULL THEN
22 RETURN NULL;
23 END IF;
24
25 OPEN c;
26 FETCH c INTO l_value;
27 CLOSE c;
28
29 RETURN l_value;
30 END item2;
31
32
33 FUNCTION locator2(p_org_id NUMBER, p_locator_id NUMBER) RETURN VARCHAR2 IS
34 -- ERES project, tracking bug 3071511.
35 -- This function differs from locator() because it does not raise
36 -- exception if ID is not found.
37 --
38 -- Return locator name from mtl_item_locations.
39 -- Return null if not found.
40 --
41 l_value varchar2(2000);
42 CURSOR c IS
43 SELECT concatenated_segments
44 FROM mtl_item_locations_kfv
45 WHERE inventory_location_id = p_locator_id and
46 organization_id = p_org_id;
47
48 BEGIN
49 IF p_org_id IS NULL OR p_locator_id IS NULL THEN
50 RETURN NULL;
51 END IF;
52
53 OPEN c;
54 FETCH c INTO l_value;
55 CLOSE c;
56
57 RETURN l_value;
58 END locator2;
59
60
61 FUNCTION item(x_org_id number, x_item_id number) return varchar2 is
62
63 item_name varchar2(2000);
64
65 -- Bug 5245271 and 5245135. Modified query to consider EAM maint org.
66 cursor c(c_org_id NUMBER,c_item_id NUMBER) IS
67 select concatenated_segments
68 from mtl_system_items_kfv
69 where inventory_item_id = c_item_id
70 and organization_id = c_org_id
71 and rownum=1
72 UNION ALL
73 select concatenated_segments
74 from mtl_system_items_kfv msikfv,mtl_parameters mp
75 where msikfv.inventory_item_id = c_item_id
76 and msikfv.organization_id = mp.organization_id
77 and mp.maint_organization_id = c_org_id
78 and rownum=1;
79
80 BEGIN
81 IF ((x_org_id IS NULL) or (x_item_id is null)) THEN
82 RETURN NULL;
83 END IF;
84
85 OPEN c(x_org_id,x_item_id);
86 FETCH c into item_name;
87 CLOSE c;
88 return item_name;
89
90 EXCEPTION
91 when OTHERS then
92 raise;
93
94 END;
95
96
97 FUNCTION get_item_id (x_org_id number, x_item VARCHAR2) return NUMBER is
98
99 id NUMBER;
100
101 BEGIN
102
103 IF ((x_org_id IS NULL) or (x_item is null)) THEN
104 RETURN NULL;
105 END IF;
106
107 --
108 -- Bug 2672398. The SQL used to have upper() on
109 -- both sides of the x_item where condition. This
110 -- is too expensive because it hides the index.
111 -- bso Mon Nov 25 19:11:12 PST 2002
112 --
113
114 /*
115 select inventory_item_id into id
116 from mtl_system_items_kfv
117 where concatenated_segments = x_item
118 and organization_id = x_org_id
119 and rownum = 1;
120
121 return id;
122 */
123
124 -- Bug 3111310. For SQL Performance Project.
125 -- Used to have a SQL that select from _kfv which is inefficient.
126 -- Commented the above code and used FND_FLEX routine for attaining
127 -- the same.
128 -- ksoh/kabalakr Fri Sep 26 06:31:05 PDT 2003
129 --
130
131 IF (FND_FLEX_KEYVAL.validate_segs(
132 operation => 'CHECK_COMBINATION',
133 key_flex_code => 'MSTK',
134 appl_short_name => 'INV',
135 structure_number => '101',
136 concat_segments => x_item,
137 data_set => x_org_id)) THEN
138
139 return FND_FLEX_KEYVAL.combination_id;
140
141 ELSE
142 return NULL;
143
144 END IF;
145
146 EXCEPTION
147
148 when OTHERS then
149 raise;
150
151 END;
152
153
154 -- The following function is added to make item_category a collection trigger
155 -- It can be called from mobile or self service.
156 -- In mobile, the user will have item_value and not the id but in self service
157 -- item_id is available. To ensure the scalability, this function takes in
158 -- both, item_value as well as item_id. It calls get_item_id to get item_id
159 -- based upon item_value and then uses this to get item_category.
160 -- anagarwa Tue Sep 18 16:19:08 PDT 2001
161
162 PROCEDURE get_item_category_val (p_org_id NUMBER,
163 p_item_val VARCHAR2,
164 p_item_id NUMBER,
165 x_category_val OUT NOCOPY VARCHAR2,
166 x_category_id OUT NOCOPY NUMBER) IS
167
168 l_item_id NUMBER;
169 l_category_set_id NUMBER;
170 l_category_val VARCHAR2(1000) := NULL;
171 l_category_id NUMBER := NULL;
172
173
174 CURSOR category_cur(p_org_id NUMBER, p_item_id NUMBER, p_category_set_id NUMBER) IS
175 select mck.concatenated_segments,
176 mck.category_id
177 from mtl_item_categories mic, mtl_categories_kfv mck
178 where mic.organization_id = p_org_id
179 and mic.category_id = mck.category_id
180 and mic.inventory_item_id =p_item_id
181 and mic.category_set_id = p_category_set_id;
182
183 BEGIN
184 -- org_id should never be null
185 IF (p_org_id IS NULL) THEN
186 RETURN ;
187 END IF;
188
189 -- if calling from mobile, then get_item_id
190 IF ((p_item_id <0 OR p_item_id IS NULL) AND (p_item_val IS NOT NULL))
191 THEN
192 l_item_id := get_item_id(p_org_id, p_item_val);
193 ELSE
194 l_item_id := p_item_id;
195 END IF;
196
197 l_category_set_id := FND_PROFILE.VALUE('QA_CATEGORY_SET');
198
199 OPEN category_cur( p_org_id, l_item_id, l_category_set_id);
200
201 FETCH category_cur INTO l_category_val, l_category_id;
202
203 CLOSE category_cur;
204
205 --if cursor did not fetch any rows
206 --then l_category_val and l_category_id will have
207 --initialized NULL values
208 --Do Not Raise Exception Here
209 --
210 x_category_val := l_category_val;
211 x_category_id := l_category_id;
212
213 RETURN;
214
215 END;
216
217 --orashid Wed Sep 19 16:55:39 PDT 2001
218 FUNCTION work_order (x_org_id NUMBER, x_work_order_id number)
219 RETURN VARCHAR2 IS
220
221 x_work_order varchar2(150) := NULL;
222 --
223 -- Bug 4958777. R12 Performance fix.
224 -- Use base table to reduce the shared memory.
225 -- srhariha. Mon Jan 30 01:25:38 PST 2006
226 --
227 CURSOR c IS
228 SELECT wip_entity_name
229 FROM wip_entities
230 WHERE wip_entity_id = x_work_order_id
231 AND organization_id = x_org_id;
232
233 /*
234 SELECT wip_entity_name
235 FROM wip_eam_work_order_dtls_v
236 WHERE wip_entity_id = x_work_order_id
237 AND organization_id = x_org_id;
238 */
239
240 BEGIN
241
242 IF ((x_org_id IS NULL) or (x_work_order_id is null)) THEN
243 RETURN NULL;
244 END IF;
245
246 OPEN c;
247 FETCH c INTO x_work_order;
248 CLOSE c;
249
250 RETURN x_work_order;
251
252 END work_order;
253
254
255
256 FUNCTION locator(x_org_id number, x_locator_id number) return varchar2 is
257 locator_name varchar2(2000);
258
259 BEGIN
260
261 IF ((x_org_id IS NULL) or (x_locator_id is null)) THEN
262 RETURN NULL;
263 END IF;
264
265 select concatenated_segments into locator_name
266 from mtl_item_locations_kfv
267 where INVENTORY_LOCATION_ID = x_locator_id
268 and organization_id = x_org_id
269 and rownum=1;
270
271 return locator_name;
272
273 EXCEPTION when OTHERS then
274 raise;
275 END;
276
277
278 FUNCTION get_locator_id (x_org_id number, x_locator VARCHAR2) return NUMBER is
279
280 id NUMBER;
281
282 BEGIN
283
284 IF ((x_org_id IS NULL) or (x_locator is null)) THEN
285 RETURN NULL;
286 END IF;
287
288 --
289 -- Bug 2672398. The SQL used to have upper() on
290 -- both sides of the x_locator where condition. This
291 -- is too expensive because it hides the index.
292 -- bso Mon Nov 25 19:11:12 PST 2002
293 --
294
295 /*
296 select inventory_location_id into id
297 from mtl_item_locations_kfv
298 where concatenated_segments = x_locator
299 and organization_id = x_org_id
300 and rownum = 1;
301
302 return id;
303 */
304
305 -- Bug 3111310. For SQL Performance Project.
306 -- Used to have a SQL that select from _kfv which is inefficient.
307 -- Commented the above code and used FND_FLEX routine for attaining
308 -- the same.
309 -- ksoh/kabalakr Fri Sep 26 06:31:05 PDT 2003
310 --
311
312 IF (FND_FLEX_KEYVAL.validate_segs(
313 operation => 'CHECK_COMBINATION',
314 key_flex_code => 'MTLL',
315 appl_short_name => 'INV',
316 structure_number => '101',
317 concat_segments => x_locator,
318 -- bug 6129280
319 -- The default is values, but since the x_locator
320 -- has the id values the processing needs to be done
321 -- using IDs, hence, added the following parameter.
322 -- bhsankar Tue Jul 17 02:35:19 PDT 2007
323 values_or_ids => 'I',
324 data_set => x_org_id)) THEN
325
326 return FND_FLEX_KEYVAL.combination_id;
327
328 ELSE
329 return NULL;
330
331 END IF;
332
333 EXCEPTION when OTHERS then
334 raise;
335
336 END;
337
338
339 FUNCTION project_number(x_id number) RETURN varchar2 IS
340 --
341 -- Return project_number from mtl_project_v given a project ID.
342 --
343 -- This function is created mostly for performance reason. We do
344 -- outer joins to mtl_project_v from qa_results_v and global views.
345 -- The outer join is causing full table scans in various PJM tables.
346 -- A stored procedure will be able to decode the project number with
347 -- index hits.
348 --
349 -- mtl_project_v may be obsoleted in R12, use pjm_org_projects_v
350 --
351 -- bso Thu Jul 8 15:53:37 PDT 1999
352 -- pjm_org_projects_v changed to pjm_projects_all_v - rkaza, 11/10/2001.
353
354 --
355 -- Bug 5249078. There is no need to change the following
356 -- pjm_projects_all_v because this is a de-reference logic,
357 -- not a validation logic.
358 -- bso Thu Jun 1 10:56:46 PDT 2006
359 --
360 x_project_number varchar2(100) := NULL;
361 CURSOR c IS
362 SELECT project_number
363 FROM pjm_projects_all_v
364 WHERE project_id = x_id;
365
366 BEGIN
367 IF x_id IS NULL THEN
368 RETURN NULL;
369 END IF;
370
371 OPEN c;
372 FETCH c INTO x_project_number;
373 CLOSE c;
374
375 --
376 -- x_project_number will be null if not found.
377 --
378 RETURN x_project_number;
379 END project_number;
380
381
382 FUNCTION sales_order(x_id number) RETURN varchar2 IS
383 --
384 -- Return sales_order given a sales order ID.
385 --
386 -- *OBSOLETE*
387 -- This function is created mostly for performance reason. OE
388 -- is revamping their product. Eventually, the so_headers table
389 -- will be obsolete and a view so_headers_interop_v will be in
390 -- its place. Then, our outer join in qa_results_v will cause
391 -- full table scans to various underlying tables. When that
392 -- happens, we will use this function to decode the sales order
393 -- in the view.
394 --
395 -- bso Sun Jul 25 12:48:25 PDT 1999
396 --
397 -- *OBSOLETE*
398 -- changed to query from oe_order_headers view in case interop_v
399 -- is not supported.
400 -- bso Fri Mar 3 15:56:03 PST 2000
401 --
402 -- Completely eliminated oe table(s) from the query because
403 -- mtl_sales_orders is a superset of all sales orders. Further,
404 -- 3rd party sales orders can be alphanumeric, changed from
405 -- a numeric function to a varchar2 function.
406 -- See Bug 1982788.
407 -- bso Sun Sep 16 15:58:51 PDT 2001
408
409 x_sales_order mtl_sales_orders.segment1%TYPE := NULL;
410 CURSOR c IS
411 SELECT segment1 order_number
412 FROM mtl_sales_orders
413 WHERE sales_order_id = x_id;
414
415 BEGIN
416 IF x_id IS NULL THEN
417 RETURN NULL;
418 END IF;
419
420 OPEN c;
421 FETCH c INTO x_sales_order;
422 CLOSE c;
423
427 RETURN x_sales_order;
424 --
425 -- x_sales_order will be null if not found.
426 --
428 END sales_order;
429
430
431 FUNCTION rma_number(x_id number) RETURN number IS
432 --
433 -- Return rma_number given a sales order ID.
434 -- Same comments as above.
435 -- bso Sun Jul 25 12:48:25 PDT 1999
436 --
437 -- changed to query from oe_order_headers view in case interop_v
438 -- is not supported.
439 -- bso Fri Mar 3 15:56:03 PST 2000
440
441 -- Changed the from clause in the below cursor from the view
442 -- oe_order_headers to the base table oe_order_headers_all to
443 -- enable RMA number collection element to honour all Operating
444 -- Units. Refer bug for more details.
445 -- Bug 3430888. suramasw
446
447 x_sales_order number := NULL;
448 CURSOR c IS
449 SELECT order_number
450 FROM oe_order_headers_all
451 WHERE header_id = x_id;
452
453 BEGIN
454 IF x_id IS NULL THEN
455 RETURN NULL;
456 END IF;
457
458 OPEN c;
459 FETCH c INTO x_sales_order;
460 CLOSE c;
461
462 --
463 -- x_sales_order will be null if not found.
464 --
465 RETURN x_sales_order;
466 END rma_number;
467
468
469 -- Added the following three functions for performance reason.
470 -- This functions are called from qa_results_v definition to
471 -- get the number value from the IDs. Using stored procedure will
472 -- avoid outjoin to complex views as will cause full table scan.
473 -- jezheng
474 -- Wed Sep 13 11:00:28 PDT 2000
475
476 -- Return contract_number from oke_k_headers_lov_v
477 -- given a contract ID.
478
479 FUNCTION contract_number(x_id number) RETURN varchar2 IS
480
481 x_contract_number varchar2(120) := NULL;
482 CURSOR c IS
483 SELECT k_number
484 FROM oke_k_headers_lov_v
485 WHERE k_header_id = x_id;
486
487 BEGIN
488 IF x_id IS NOT NULL THEN
489 OPEN c;
490 FETCH c INTO x_contract_number;
491 CLOSE c;
492 END IF;
493
494 -- x_contract_number will be null if not found.
495 RETURN x_contract_number;
496
497 END contract_number;
498
499 -- Return contract_line_number from oke_k_lines_full_v
500 -- given contract_line_id.
501
502 FUNCTION contract_line_number(x_id number) RETURN varchar2 IS
503
504 x_contract_line_number varchar2(150) := NULL;
505 CURSOR c IS
506 SELECT line_number
507 FROM oke_k_lines_full_v
508 WHERE k_line_id = x_id;
509
510 BEGIN
511 IF x_id IS NOT NULL THEN
512 OPEN c;
513 FETCH c INTO x_contract_line_number;
514 CLOSE c;
515 END IF;
516
517 -- x_contract_line_number will be null if not found.
518 RETURN x_contract_line_number;
519
520 END contract_line_number;
521
522 -- Return deliverable_number from oke_k_deliverables_vl
523 -- given a deliverable ID.
524
525 FUNCTION deliverable_number(x_id number) RETURN varchar2 IS
526
527 x_deliverable_number varchar2(150) := NULL;
528 CURSOR c IS
529 SELECT deliverable_num
530 FROM oke_k_deliverables_vl
531 WHERE deliverable_id = x_id;
532
533 BEGIN
534 IF x_id IS NOT NULL THEN
535 OPEN c;
536 FETCH c INTO x_deliverable_number;
537 CLOSE c;
538 END IF;
539
540 -- x_deliverable_number will be null if not found.
541 RETURN x_deliverable_number;
542
543 END deliverable_number;
544
545
546 --
547 -- The following qpc functions are implemented but not used.
548 -- A better way to fix the problem was found. But I'll leave
549 -- these here as a model of an interesting cache programming
550 -- technique which will be useful for any api programming.
551 -- bso Thu Oct 28 17:05:21 PDT 1999
552 --
553
554 PROCEDURE qpc_fetch(x_plan_id number, x_char_id number) IS
555 --
556 -- To maintain a cache for the qa_plan_chars table.
557 --
558 CURSOR c IS
559 SELECT result_column_name, values_exist_flag
560 FROM qa_plan_chars
561 WHERE plan_id = x_plan_id AND
562 char_id = x_char_id;
563 BEGIN
564 --
565 -- Fetch only if primary keys different from cached.
566 --
567 IF x_plan_id <> cached_qpc_plan_id OR
568 x_char_id <> cached_qpc_char_id THEN
569 cached_qpc_plan_id := x_plan_id;
570 cached_qpc_char_id := x_char_id;
571 OPEN c;
572 FETCH c INTO
573 cached_qpc_result_column_name,
574 cached_qpc_values_exist_flag;
578 --
575 IF c%notfound THEN
576 --
577 -- Cached values should be null if not found.
579 cached_qpc_result_column_name := null;
580 cached_qpc_values_exist_flag := null;
581 END IF;
582 CLOSE c;
583 END IF;
584 END qpc_fetch;
585
586
587
588 FUNCTION qpc_result_column_name(x_plan_id number, x_char_id number)
589 RETURN varchar2 IS
590 --
591 -- Return the result_column_name from qa_plan_chars given a plan_id
592 -- and a char_id. Return null if not found.
593 -- bso
594 --
595 BEGIN
596 qpc_fetch(x_plan_id, x_char_id);
597 RETURN cached_qpc_result_column_name;
598 END qpc_result_column_name;
599
600
601 FUNCTION qpc_values_exist_flag(x_plan_id number, x_char_id number)
602 RETURN number IS
603 --
604 -- Return qpc_values_exist_flag (an integer) from qa_plan_chars given a
605 -- plan_id and a char_id. Return null if not found.
606 -- bso
607 --
608 BEGIN
609 qpc_fetch(x_plan_id, x_char_id);
610 RETURN cached_qpc_values_exist_flag;
611 END qpc_values_exist_flag;
612
613
614 FUNCTION qch_plan_id(x_criteria_id number) RETURN number IS
615 --
616 -- Return plan_id from qa_criteria_headers given a criteria_id.
617 -- Return null if not found.
618 --
619 x_plan_id number := null;
620 CURSOR c IS
621 SELECT plan_id
622 FROM qa_criteria_headers
623 WHERE criteria_id = x_criteria_id;
624 BEGIN
625 IF x_criteria_id IS NULL THEN
626 RETURN NULL;
627 END IF;
628
629 OPEN c;
630 FETCH c INTO x_plan_id;
631 CLOSE c;
632
633 RETURN x_plan_id;
634 END qch_plan_id;
635
636
637 FUNCTION mtl_categories_description(x_category_id number) RETURN varchar2 IS
638 --
639 -- Return description from mtl_categories_kfv given category_id.
640 -- Return null if not found.
641 --
642 x_description varchar2(240) := null;
643 CURSOR c IS
644 SELECT description
645 FROM mtl_categories_kfv
646 WHERE category_id = x_category_id;
647 BEGIN
648 IF x_category_id IS NULL THEN
649 RETURN NULL;
650 END IF;
651
652 OPEN c;
653 FETCH c INTO x_description;
654 CLOSE c;
655
656 RETURN x_description;
657 END mtl_categories_description;
658
659 --
660 -- Derive project id given an LPN. Used internally.
661 -- bso Wed Mar 13 16:53:35 PST 2002
662 --
663 FUNCTION get_project_id_from_lpn(
664 p_org_id NUMBER,
665 p_lpn_id NUMBER) RETURN NUMBER IS
666
667 l_project_id NUMBER;
668 l_task_id NUMBER;
669 l_return_status VARCHAR2(10);
670 l_msg_count NUMBER;
671 l_msg_data VARCHAR2(2000);
672
673 BEGIN
674 inv_project.get_proj_task_from_lpn(
675 p_organization_id => p_org_id,
676 p_lpn_id => p_lpn_id,
677 x_project_id => l_project_id,
678 x_task_id => l_task_id,
679 x_return_status => l_return_status,
680 x_msg_count => l_msg_count,
681 x_msg_data => l_msg_data);
682
683 RETURN l_project_id;
684 END get_project_id_from_lpn;
685
686 --
687 -- Derive task id given an LPN. Used internally.
688 -- bso Wed Mar 13 16:53:35 PST 2002
689 --
690 FUNCTION get_task_id_from_lpn(
691 p_org_id NUMBER,
692 p_lpn_id NUMBER) RETURN NUMBER IS
693
694 l_project_id NUMBER;
695 l_task_id NUMBER;
696 l_return_status VARCHAR2(10);
697 l_msg_count NUMBER;
698 l_msg_data VARCHAR2(2000);
699
700 BEGIN
701 inv_project.get_proj_task_from_lpn(
702 p_organization_id => p_org_id,
703 p_lpn_id => p_lpn_id,
704 x_project_id => l_project_id,
705 x_task_id => l_task_id,
706 x_return_status => l_return_status,
707 x_msg_count => l_msg_count,
708 x_msg_data => l_msg_data);
709
710 RETURN l_task_id;
711 END get_task_id_from_lpn;
712
713
714 --
715 -- Derive project number given an LPN. Used by WMS/QA mobile integration.
716 -- See /qadev/qa/51.0/11.5.8/wms_pjm_dld.txt
717 -- bso Wed Mar 13 16:53:35 PST 2002
718 --
719 PROCEDURE get_project_number_from_lpn(
720 p_org_id NUMBER,
721 p_lpn_id NUMBER,
722 x_project_number OUT NOCOPY VARCHAR2) IS
723
724 BEGIN
725
726 x_project_number := project_number(
727 get_project_id_from_lpn(p_org_id, p_lpn_id));
728
729 END get_project_number_from_lpn;
730
731
735 -- bso Wed Mar 13 16:53:35 PST 2002
732 --
733 -- Derive task number given an LPN. Used by WMS/QA mobile integration.
734 -- See /qadev/qa/51.0/11.5.8/wms_pjm_dld.txt
736 --
737 PROCEDURE get_task_number_from_lpn(
738 p_org_id NUMBER,
739 p_lpn_id NUMBER,
740 x_task_number OUT NOCOPY VARCHAR2) IS
741 BEGIN
742
743 x_task_number := inv_project.get_task_number(
744 get_task_id_from_lpn(p_org_id, p_lpn_id));
745
746 END get_task_number_from_lpn;
747
748 FUNCTION get_vendor_site_id(p_vendor_site VARCHAR2) RETURN NUMBER IS
749
750 x_vendor_site_id NUMBER;
751 CURSOR c IS
752 SELECT vendor_site_id
753 FROM po_vendor_sites_all
754 WHERE vendor_site_code = p_vendor_site;
755 BEGIN
756 IF p_vendor_site IS NULL THEN
757 RETURN -1;
758 END IF;
759
760 OPEN c;
761 FETCH c INTO x_vendor_site_id;
762 CLOSE c;
763
764 RETURN x_vendor_site_id;
765
766 END get_vendor_site_id;
767
768 FUNCTION get_project_id(p_project_number VARCHAR2) RETURN NUMBER IS
769 --
770 -- Return project_id given a project number.
771 --
772 -- rponnusa Mon Oct 7 05:59:17 PDT 2002
773
774 --
775 -- Bug 5249078. Changed pjm_projects_all_v to
776 -- pjm_projects_v for MOAC compliance.
777 -- bso Thu Jun 1 10:46:50 PDT 2006
778 --
779
780 l_project_id NUMBER;
781 CURSOR c IS
782 SELECT project_id
783 FROM pjm_projects_v
784 WHERE project_number = p_project_number;
785
786 BEGIN
787 IF p_project_number IS NULL THEN
788 RETURN NULL;
789 END IF;
790
791 OPEN c;
792 FETCH c INTO l_project_id;
793 CLOSE c;
794 RETURN l_project_id;
795
796 END get_project_id;
797
798 FUNCTION get_task_id(p_project_id NUMBER,p_task_number VARCHAR2) RETURN NUMBER IS
799 --
800 -- Return task_id given a project_id and task number.
801 --
802 -- rponnusa Mon Oct 7 05:59:17 PDT 2002
803
804 l_task_id NUMBER;
805 CURSOR c IS
806 SELECT task_id
807 FROM mtl_task_v
808 WHERE project_id = p_project_id
809 AND task_number = p_task_number;
810
811 BEGIN
812 IF p_project_id IS NULL OR p_task_number IS NULL THEN
813 RETURN NULL;
814 END IF;
815
816 OPEN c;
817 FETCH c INTO l_task_id;
818 CLOSE c;
819
820 RETURN l_task_id;
821
822 END get_task_id;
823
824
825 -- Bug 3096256.
826 -- This procedures returns the Subinventory of an LPN given the LPN_ID
827 -- from wms_license_plate_numbers. This procedure is called from
828 -- getSubinventoryFromLPN() method in ContextElementTable.java.
829 -- For RCV/WMS Enhancements. kabalakr Mon Aug 25 04:12:48 PDT 2003.
830
831 PROCEDURE get_subinventory_from_lpn(
832 p_lpn_id NUMBER,
833 x_subinventory OUT NOCOPY VARCHAR2) IS
834
835 CURSOR C1 IS
836 SELECT subinventory_code
837 FROM wms_license_plate_numbers
838 WHERE lpn_id = p_lpn_id;
839
840 BEGIN
841
842 OPEN C1;
843 FETCH C1 INTO x_subinventory;
844 CLOSE C1;
845
846 END get_subinventory_from_lpn;
847
848
849 -- Bug 3096256.
850 -- This procedures returns the Locator of an LPN given the LPN_ID
851 -- from wms_license_plate_numbers. This procedure is called from
852 -- getLocatorFromLPN() method in ContextElementTable.java.
853 -- For RCV/WMS Enhancements. kabalakr Mon Aug 25 04:12:48 PDT 2003.
854
855 PROCEDURE get_locator_from_lpn(
856 p_org_id NUMBER,
857 p_lpn_id NUMBER,
858 x_locator OUT NOCOPY VARCHAR2) IS
859
860 l_locator_id NUMBER;
861
862 CURSOR C1 IS
863 SELECT locator_id
864 FROM wms_license_plate_numbers
865 WHERE lpn_id = p_lpn_id;
866
867 BEGIN
868
869 OPEN C1;
870 FETCH C1 INTO l_locator_id;
871 CLOSE C1;
872
873 x_locator := locator(p_org_id, l_locator_id);
874
875 END get_locator_from_lpn;
876
877
878 --dgupta: Start R12 EAM Integration. Bug 4345492
879 FUNCTION get_asset_group_name (org_id IN NUMBER, value IN NUMBER)
880 RETURN VARCHAR2 IS
881
882 name VARCHAR2(2000);
883 --rownum=1 =>better performance since all rows have same inventory_item_id
884 CURSOR c (o_id NUMBER, asset_group_id NUMBER) IS
885 SELECT msikfv.concatenated_segments
886 FROM mtl_system_items_b_kfv msikfv, mtl_parameters mp
887 WHERE msikfv.organization_id = mp.organization_id
888 and msikfv.eam_item_type in (1,3)
892
889 and mp.maint_organization_id = o_id
890 and msikfv.inventory_item_id = asset_group_id
891 and rownum=1;
893 BEGIN
894
895 IF value IS NULL THEN
896 RETURN NULL;
897 END IF;
898
899 OPEN c(org_id, value);
900 FETCH c INTO name;
901 CLOSE c;
902
903 RETURN name;
904
905 END get_asset_group_name;
906 --dgupta: End R12 EAM Integration. Bug 4345492
907
908 --
909 -- Bug 4958739. R12 Performance fixes.
910 -- New utility function for getting qa_lookup meaning.
911 -- srhariha. Mon Jan 30 01:25:38 PST 2006
912 --
913 FUNCTION get_qa_lookups_meaning (p_lookup_type IN VARCHAR2,
914 p_lookup_code IN VARCHAR2)
915 RETURN VARCHAR2 IS
916
917 l_meaning VARCHAR2(80);
918
919 CURSOR C1 IS
920 SELECT meaning
921 FROM qa_lookups
922 WHERE lookup_type = p_lookup_type
923 AND lookup_code = p_lookup_code;
924
925 BEGIN
926
927 OPEN C1;
928 FETCH C1 INTO l_meaning;
929 CLOSE C1;
930
931 RETURN l_meaning;
932
933 END get_qa_lookups_meaning;
934
935 --
936 -- Bug 5279941.
937 -- New utility function for getting the asset instance
938 -- name from the asset instance Number
939 -- ntungare Wed Jun 21 01:45:43 PDT 2006
940 --
941 FUNCTION get_asset_instance_name (p_asset_instance_number IN VARCHAR2)
942 RETURN VARCHAR2 IS
943
944 l_asset_instance_name VARCHAR2(2000) :=NULL;
945
946 CURSOR C1 IS
947 SELECT instance_number
948 FROM CSI_ITEM_INSTANCES
949 WHERE INSTANCE_id = p_asset_instance_number;
950
951 BEGIN
952
953 OPEN C1;
954 FETCH C1 INTO l_asset_instance_name;
955 CLOSE C1;
956
957 RETURN l_asset_instance_name;
958
959 END get_asset_instance_name;
960
961 END QA_FLEX_UTIL;
[Home] [Help]