1 PACKAGE BODY qa_ss_lov_api AS
2 /* $Header: qltsslob.plb 120.27 2011/03/08 12:05:35 hmakam ship $ */
3
4
5 TYPE qa_plan_chars_table IS TABLE OF qa_plan_chars%ROWTYPE
6 INDEX BY BINARY_INTEGER;
7
8 --
9 -- Package Variables: These will be populated at run time
10 --
11
12 x_qa_plan_chars_array qa_plan_chars_table;
13 g_bind_value_list_seperator CONSTANT VARCHAR2(3) := ',';
14
15
16 -- anagarwa Thu Aug 12 15:49:51 PDT 2004
17 -- bug 3830258 incorrect LOVs in QWB
18 -- utility method to check if given item is revision controlled or not.
19 FUNCTION is_item_revision_controlled(p_item_id IN NUMBER ,
20 p_org_id IN NUMBER)
21 RETURN BOOLEAN IS
22
23 l_rev_control_code NUMBER;
24
25 CURSOR c IS
26 SELECT revision_qty_control_code
27 FROM mtl_system_items
28 WHERE inventory_item_id = p_item_id
29 AND organization_id = p_org_id;
30 BEGIN
31
32 OPEN c;
33 FETCH c INTO l_rev_control_code;
34 IF (c%FOUND AND l_rev_control_code = 2) THEN
35 CLOSE c;
36 RETURN true;
37 END IF;
38 CLOSE c;
39 RETURN false;
40
41 END is_item_revision_controlled;
42
43
44 -- anagarwa Thu Aug 12 15:49:51 PDT 2004
45 -- bug 3830258 incorrect LOVs in QWB
46 -- utility method to check if given item is lot controlled or not.
47 FUNCTION is_item_lot_controlled(p_item_id IN NUMBER ,
48 p_org_id IN NUMBER)
49 RETURN BOOLEAN IS
50
51 l_lot_control_code NUMBER;
52
53 CURSOR c IS
54 SELECT lot_control_code
55 FROM mtl_system_items
56 WHERE inventory_item_id = p_item_id
57 AND organization_id = p_org_id;
58 BEGIN
59
60 OPEN c;
61 FETCH c INTO l_lot_control_code;
62 IF (c%FOUND AND l_lot_control_code = 2) THEN
63 CLOSE c;
64 RETURN true;
65 END IF;
66 CLOSE c;
67 RETURN false;
68
69 END is_item_lot_controlled;
70
71
72
73 --
74 -- All the fetch_... procedures are auxiliary caching functions
75 -- called only by inquiry APIs that return the object's attributes.
76 --
77
78 --
79 -- This plan element index is used to hash plan id and element id
80 -- into one unique integer to be used as index into the cache.
81 -- Can also be used by spec_chars.
82 --
83
84 FUNCTION plan_element_index(plan_id IN NUMBER, element_id IN NUMBER)
85 RETURN NUMBER IS
86
87 i NUMBER;
88 BEGIN
89 --
90 -- Bug 2409938
91 -- This is a potential installation/upgrade error.
92 -- Error happens if there is some customization of
93 -- collection plans or elements with huge IDs.
94 -- Temporarily fixed with a modulus. It should be
95 -- properly fixed with a hash collision resolution.
96 -- But the temp workaround should only have collision
97 -- when user has more than 20,000 collection plans
98 -- *and still* with a probability of about 1/200,000.
99 -- bso Tue Jul 16 12:41:23 PDT 2002
100 --
101
102 --
103 -- Bug 2465704
104 -- The above hash collision problem is now fixed with
105 -- linear hash collision resolution.
106 -- The plan_element array is hit to see if the index
107 -- contains the right plan element. If not, we search
108 -- forward until either the matching plan element is
109 -- reached or an empty cell is reached.
110 --
111 -- Because of this, we need to introduce a new function
112 -- spec_element_index for use by the spec_element array.
113 -- bso Sun Dec 1 17:39:18 PST 2002
114 --
115 -- COMMENTS: its seems like this caching mechanism is
116 -- a mirror copy of that in qltelemb. Can this one be
117 -- removed?
118 --
119
120 i := mod(plan_id * qa_ss_const.max_elements + element_id,
121 2147483647);
122
123 LOOP
124 IF NOT x_qa_plan_chars_array.EXISTS(i) THEN
125 RETURN i;
126 END IF;
127
128 IF x_qa_plan_chars_array(i).plan_id = plan_id AND
129 x_qa_plan_chars_array(i).char_id = element_id THEN
130 RETURN i;
131 END IF;
132
133 i := mod(i + 1, 2147483647);
134 END LOOP;
135
136 END plan_element_index;
137
138
139 FUNCTION exists_qa_plan_chars(plan_id IN NUMBER, element_id IN NUMBER)
140 RETURN BOOLEAN IS
141 BEGIN
142
143 RETURN x_qa_plan_chars_array.EXISTS(
144 plan_element_index(plan_id, element_id));
145 END exists_qa_plan_chars;
146
147
148 PROCEDURE fetch_qa_plan_chars (plan_id IN NUMBER, element_id IN NUMBER) IS
149
150 CURSOR C1 (p_id NUMBER, e_id NUMBER) IS
151 SELECT *
152 FROM qa_plan_chars
153 WHERE plan_id = p_id AND char_id = e_id;
154
155 BEGIN
156
157 IF NOT exists_qa_plan_chars(plan_id, element_id) THEN
158
159 OPEN C1(plan_id, element_id);
160 FETCH C1 INTO x_qa_plan_chars_array(
161 plan_element_index(plan_id, element_id));
162
163 CLOSE C1;
164 END IF;
165
166 EXCEPTION WHEN OTHERS THEN
167 RAISE;
168 END fetch_qa_plan_chars;
169
170 --parent-child addition
171 FUNCTION qa_chars_values_exist (x_char_id IN NUMBER)
172 RETURN BOOLEAN IS
173
174 result BOOLEAN;
175 dummy NUMBER;
176
177 CURSOR c IS
178 select 1
179 from qa_chars qc
180 where qc.char_id = x_char_id
181 AND qc.values_exist_flag = 1;
182 BEGIN
183 OPEN c;
184 FETCH c INTO dummy;
185 result := c%FOUND;
186 CLOSE c;
187
188 RETURN result;
189
190
191
192 END qa_chars_values_exist;
193 ---
194
195 FUNCTION qpc_values_exist_flag(plan_id IN NUMBER,
196 element_id IN NUMBER) RETURN NUMBER IS
197 BEGIN
198
199 fetch_qa_plan_chars(plan_id, element_id);
200 IF NOT exists_qa_plan_chars(plan_id, element_id) THEN
201 RETURN NULL;
202 END IF;
203
204 RETURN x_qa_plan_chars_array(plan_element_index(plan_id, element_id)).
205 values_exist_flag;
206 END qpc_values_exist_flag;
207
208
209 FUNCTION values_exist (plan_id IN NUMBER, element_id IN NUMBER)
210 RETURN BOOLEAN IS
211
212 BEGIN
213
214 RETURN qpc_values_exist_flag(plan_id, element_id) = 1;
215
216 END values_exist;
217
218
219 FUNCTION sql_validation_exists (element_id IN NUMBER)
220 RETURN BOOLEAN IS
221
222 BEGIN
223
224 RETURN qa_chars_api.sql_validation_string(element_id) IS NOT NULL;
225
226 END sql_validation_exists;
227
228
229 FUNCTION element_in_plan (plan_id IN NUMBER, element_id IN NUMBER)
230 RETURN BOOLEAN IS
231
232 BEGIN
233
234 fetch_qa_plan_chars(plan_id, element_id);
235 RETURN exists_qa_plan_chars(plan_id, element_id);
236
237 END element_in_plan;
238
239
240 FUNCTION get_sql_validation_string (element_id IN NUMBER)
241 RETURN VARCHAR2 IS
242 BEGIN
243 RETURN qa_chars_api.sql_validation_string(element_id);
244 END get_sql_validation_string;
245
246
247 FUNCTION sql_string_exists(x_plan_id IN NUMBER, x_char_id IN NUMBER,
248 org_id IN NUMBER, user_id NUMBER, x_lov_sql OUT NOCOPY VARCHAR2)
249 RETURN BOOLEAN IS
250
251 BEGIN
252
253 IF (x_plan_id = -1) THEN
254 IF qa_chars_values_exist(x_char_id) THEN
255 x_lov_sql := 'SELECT short_code, description
256 FROM qa_char_value_lookups
257 WHERE char_id = :1 ';
258
259 RETURN TRUE;
260 -- we will not go down further in the chain
261 END IF;
262 END IF;
263
264 -- if x_plan_id is not -1, then proceed as below
265
266 IF values_exist(x_plan_id, x_char_id) THEN
267 x_lov_sql := 'SELECT short_code, description
268 FROM qa_plan_char_value_lookups
269 WHERE plan_id = :1
270 AND char_id = :2';
271 RETURN TRUE;
272
273 ELSIF sql_validation_exists(x_char_id) THEN
274
275 x_lov_sql := get_sql_validation_string(x_char_id);
276 x_lov_sql := qa_chars_api.format_sql_for_lov(x_lov_sql,
277 org_id, user_id);
278
279 RETURN TRUE;
280
281 ELSE
282 RETURN FALSE;
283 END IF;
284
285 END sql_string_exists;
286
287 FUNCTION sql_string_bind_values(p_plan_id IN NUMBER,
288 p_char_id IN NUMBER)
289 RETURN VARCHAR2 IS
290
291 BEGIN
292
293 IF (p_plan_id = -1) THEN
294 IF qa_chars_values_exist(p_char_id) THEN
295
296 RETURN to_char(p_char_id);
297
298 END IF;
299 END IF;
300
301 -- if p_plan_id is not -1, then proceed as below
302
303 IF values_exist(p_plan_id, p_char_id) THEN
304 RETURN to_char(p_plan_id) || g_bind_value_list_seperator || to_char(p_char_id);
305
306 END IF;
307
308 RETURN NULL;
309
310 END sql_string_bind_values;
311
312 -- *** start of get_lov functions ****
313
314
315 PROCEDURE get_department_lov(org_id IN NUMBER, value IN VARCHAR2,
316 x_lov_sql OUT NOCOPY VARCHAR2) IS
317
318 BEGIN
319
320 x_lov_sql := 'SELECT department_code, description
321 FROM bom_departments_val_v
322 WHERE organization_id = :1
323 ORDER BY department_code';
324
325 END get_department_lov;
326
327
328 PROCEDURE get_job_lov(org_id IN NUMBER, value IN VARCHAR2,
329 x_lov_sql OUT NOCOPY VARCHAR2) IS
330
331 BEGIN
332
333 -- #2382432
334 -- Changed the view to WIP_DISCRETE_JOBS_ALL_V instead of
335 -- earlier wip_open_discrete_jobs_val_v
336 -- rkunchal Sun Jun 30 22:59:11 PDT 2002
337
338 x_lov_sql := 'SELECT wip_entity_name, description
339 FROM wip_discrete_jobs_all_v
340 WHERE organization_id = :1
341 ORDER BY wip_entity_name';
342
343 END get_job_lov;
344
345
346 PROCEDURE get_work_order_lov(org_id IN NUMBER, value IN VARCHAR2,
347 x_lov_sql OUT NOCOPY VARCHAR2) IS
348
349 BEGIN
350 /* rkaza 10/21/2002. Bug 2635736 */
351 x_lov_sql :=
352 'select WE.wip_entity_name, WDJ.description
353 from wip_entities WE, wip_discrete_jobs WDJ
354 where WDJ.organization_id = :1 and
355 WDJ.status_type in (3,4) and
356 WDJ.wip_entity_id = WE.wip_entity_id and
357 WE.entity_type IN (6, 7)
358 order by WE.wip_entity_name';
359
360 END get_work_order_lov;
361
362
363 PROCEDURE get_production_lov (org_id IN NUMBER, value IN VARCHAR2,
364 x_lov_sql OUT NOCOPY VARCHAR2) IS
365
366 BEGIN
367
368 x_lov_sql := 'SELECT line_code, description
369 FROM wip_lines_val_v
370 WHERE organization_id = :1
371 ORDER BY line_code';
372
373
374 END get_production_lov;
375
376
377 PROCEDURE get_resource_code_lov (org_id IN NUMBER, value IN VARCHAR2,
378 x_lov_sql OUT NOCOPY VARCHAR2) IS
379
380 BEGIN
381
382 x_lov_sql := 'SELECT resource_code, description
383 FROM bom_resources_val_v
384 WHERE organization_id = :1
385 ORDER BY resource_code';
386
387 END get_resource_code_lov;
388
389
390 PROCEDURE get_supplier_lov (value IN VARCHAR2, x_lov_sql OUT NOCOPY VARCHAR2) IS
391
392 BEGIN
393
394 x_lov_sql := 'SELECT vendor_name, segment1
395 FROM po_vendors
396 WHERE nvl(end_date_active, sysdate + 1) > sysdate
397 ORDER BY vendor_name';
398
399
400
401 END get_supplier_lov;
402
403 --
404 -- Bug 5003511. R12 Performance bug. SQLID: 15008553
405 -- After MOAC changes, PO Number uses different LOV logic.
406 -- This method is not used. So stubbing it out.
407 -- srhariha. Wed Feb 8 02:10:26 PST 2006.
408 --
409
410 PROCEDURE get_po_number_lov (value IN VARCHAR2, x_lov_sql OUT NOCOPY VARCHAR2) IS
411
412 BEGIN
413 -- Bug 3215866 ksoh Wed Oct 29 16:21:11 PST 2003
414 -- fixed sql to make it return value instead of id.
415
416 /*
417 x_lov_sql := 'SELECT segment1, vendor_name
418 FROM po_pos_val_v
419 ORDER BY po_header_id';
420 */
421 x_lov_sql := NULL;
422
423 END get_po_number_lov;
424
425
426 PROCEDURE get_customer_lov (value IN VARCHAR2, x_lov_sql OUT NOCOPY VARCHAR2) IS
427
428 BEGIN
429 -- Bug 3384771 ksoh Wed Jan 21 10:23:25 PST 2004
430 -- fixed sql to make it return value instead of id.
431 x_lov_sql := 'SELECT customer_name, customer_number
432 FROM qa_customers_lov_v
433 WHERE status = ''A''
434 AND nvl(customer_prospect_code, ''CUSTOMER'') = ''CUSTOMER''
435 ORDER BY customer_number';
436
437 END get_customer_lov;
438
439
440 PROCEDURE get_so_number_lov (value IN VARCHAR2, x_lov_sql OUT NOCOPY VARCHAR2) IS
441
442 BEGIN
443
444 x_lov_sql := 'SELECT order_number, order_type
445 FROM qa_sales_orders_lov_v mso
446 ORDER BY order_number';
447
448 END get_so_number_lov;
449 --
450 -- Bug 5003511. R12 Performance bug. SQLID: 15008608
451 -- SO Line number is not at all an LOV. So simply stubbing
452 -- out the procedure.
453 -- srhariha. Wed Feb 8 02:10:26 PST 2006.
454 --
455
456 -- Bug 7716875.pdube Mon Apr 13 03:25:19 PDT 2009
457 -- Introduced new procedure to get the LOV of SO Line Number
458 -- Based on the SO Number element value.Commenting the old one.
459 /*PROCEDURE get_so_line_number_lov (value IN VARCHAR2, x_lov_sql OUT NOCOPY VARCHAR2) IS
460
461 BEGIN
462
463
464 /*x_lov_sql := 'SELECT to_char(sl.line_number), msik.concatenated_segments
465 FROM mtl_system_items_kfv msik, so_lines sl
466 WHERE sl.inventory_item_id = msik.inventory_item_id'; *//*
467
468 x_lov_sql := NULL;
469
470 END get_so_line_number_lov;*/
471
472 PROCEDURE get_so_line_number_lov (p_plan_id IN NUMBER,
473 p_so_number IN VARCHAR2,
474 value IN VARCHAR2,
475 x_lov_sql OUT NOCOPY VARCHAR2) IS
476 BEGIN
477
478 IF (p_so_number is null) THEN
479 fnd_message.set_name('QA', 'QA_SSQR_DEPENDENT_LOV');
480 fnd_message.set_token('DEPELEM',
481 qa_plan_element_api.get_prompt(p_plan_id, qa_ss_const.sales_order));
482 fnd_msg_pub.add();
483 END IF;
484
485 x_lov_sql := 'select distinct to_char(oel.line_number) ,''Sales Order: '' ||
486 oeha.order_number || '';'' || ''Item: '' || oel.ordered_item description
487 from oe_order_lines_all oel, oe_order_headers_all oeha
488 where oel.header_id = oeha.header_id ' ||
489 ' and oeha.order_number = :1 ' ||
490 ' order by description, line_number ';
491
492 END get_so_line_number_lov;
493
494 -- Bug 7716875
495 -- Return so_number as bind value for LOV of SO Line Number
496 -- pdube Mon Apr 13 03:25:19 PDT 2009
497 FUNCTION get_so_line_num_bind_values (p_so_number IN VARCHAR2)
498 RETURN VARCHAR2 IS
499 BEGIN
500
501 IF (p_so_number IS NULL) THEN
502 RETURN NULL;
503 END IF;
504
505 RETURN to_char(p_so_number);
506
507 END get_so_line_num_bind_values;
508
509 --
510 -- Bug 5003511. R12 Performance bug. SQLID: 15008630
511 -- Release number is dependent on PO Number.
512 -- As per safe spec, creating an overloaded method for getting
513 -- the lov sql. Also created new method for getting the bind value.
514 -- srhariha. Wed Feb 8 02:10:26 PST 2006.
515 --
516 PROCEDURE get_po_release_number_lov (p_plan_id IN NUMBER,
517 po_header_id IN VARCHAR2,
518 value IN VARCHAR2,
519 x_lov_sql OUT NOCOPY VARCHAR2) IS
520
521 BEGIN
522
523 IF ((p_plan_id is not null) AND (po_header_id is null)) THEN
524 fnd_message.set_name('QA', 'QA_SSQR_DEPENDENT_LOV');
525 fnd_message.set_token('DEPELEM',
526 qa_plan_element_api.get_prompt(p_plan_id, qa_ss_const.po_number));
527 fnd_msg_pub.add();
528 END IF;
529
530 x_lov_sql := 'SELECT to_char(release_num), QLTDATE.date_to_user(release_date) ' ||
531 'FROM po_releases pr ' ||
532 'WHERE pr.po_header_id = :1 ' ||
533 'ORDER BY pr.release_num ';
534
535 END get_po_release_number_lov;
536
537 --
538 -- Bug 9817478
539 -- Overloaded the procedure get_po_release_number_lov to pass PO Number
540 -- and PO Line Number as parents for PO Release Number.
541 -- skolluku
542 --
543 PROCEDURE get_po_release_number_lov (plan_id IN NUMBER, po_number IN VARCHAR2, po_line IN VARCHAR2,
544 value IN VARCHAR2, x_lov_sql OUT NOCOPY VARCHAR2) IS
545
546 BEGIN
547 IF ((plan_id is not null) AND (po_number is null)) THEN
548 fnd_message.set_name('QA', 'QA_SSQR_DEPENDENT_LOV');
549 fnd_message.set_token('DEPELEM',
550 qa_plan_element_api.get_prompt(plan_id, qa_ss_const.po_number));
551 fnd_msg_pub.add();
552 END IF;
553
554 x_lov_sql := 'SELECT DISTINCT to_char(pr.release_num) release_number,
555 QLTDATE.date_to_user(pr.release_date)
556 FROM po_releases pr,
557 po_line_locations_trx_v pll
558 WHERE pr.po_header_id = :1
559 AND pr.po_header_id = pll.po_header_id
560 AND pr.po_release_id = pll.po_release_id
561 AND pll.po_line_id = nvl(:2, pll.po_line_id)
562 ORDER BY pr.release_num';
563
564 END get_po_release_number_lov;
565
566 -- Bug 9817478. Bind value for PO Release Num.
567 FUNCTION get_po_rel_no_bind_values (p_po_number IN VARCHAR2, p_po_line IN VARCHAR2)
568 RETURN VARCHAR2 IS
569 l_po_number_id NUMBER;
570 l_po_line_id NUMBER;
571
572 BEGIN
573
574 l_po_number_id := to_number(p_po_number);
575 -- Bug 9817478. Fetch the Line Id from Line Num.
576 IF p_po_line IS NULL THEN
577 l_po_line_id := NULL;
578 ELSE
579 SELECT po_line_id
580 INTO l_po_line_id
581 FROM po_lines_val_trx_v
582 WHERE line_num = p_po_line
583 AND po_header_id = l_po_number_id;
584 END IF;
585
586 RETURN to_char(l_po_number_id) || g_bind_value_list_seperator || to_char(l_po_line_id);
587
588 END get_po_rel_no_bind_values;
589
590 FUNCTION get_po_rel_no_bind_values (p_po_header_id IN VARCHAR2)
591 RETURN VARCHAR2 IS
592
593 BEGIN
594
595 RETURN p_po_header_id;
596
597 END get_po_rel_no_bind_values;
598
599 -- End Bug 5003511. SQLID : 15008630.
600
601 -- Bug 5003511 SQLID : 15008630
602 -- commneting out unused overridden procedure below
603 -- saugupta Tue, 14 Feb 2006 07:07:11 -0800 PDT
604 /*
605 PROCEDURE get_po_release_number_lov (value IN VARCHAR2, x_lov_sql OUT
606 NOCOPY VARCHAR2) IS
607
608 BEGIN
609 -- Bug 3215866 ksoh Wed Oct 29 16:21:11 PST 2003
610 -- added to_char and QLTDATE to lov sql to make it compatible to
611 -- char column when it is union with the kludge sql: select '1'.....
612 x_lov_sql := 'SELECT to_char(release_num), QLTDATE.date_to_user(release_date)
613 FROM po_releases pr
614 ORDER BY pr.release_num';
615
616 END get_po_release_number_lov;
617 */
618
619 PROCEDURE get_project_number_lov (value IN VARCHAR2, x_lov_sql OUT
620 NOCOPY VARCHAR2) IS
621
622 BEGIN
623 /*
624 mtl_project_v changed to pjm_projects_all_v (selects from both pjm enabled and
625 non-pjm enabled orgs).
626 rkaza, 11/10/2001.
627 */
628
629 --
630 -- Bug 5249078. Changed pjm_projects_all_v to
631 -- pjm_projects_v for MOAC compliance.
632 -- bso Thu Jun 1 10:46:50 PDT 2006
633 --
634
635 x_lov_sql := 'SELECT project_number, project_name
636 FROM pjm_projects_v
637 ORDER BY project_number';
638
639 END get_project_number_lov;
640
641
642 /*
643 anagarwa Thu Jan 29 15:04:26 PST 2004
644 Bug 3404863 : Task LOV should be dependent upon Project lov
645 We look for project number and get project id and then add this to where
646 clause of task lov sql
647
648 */
649 PROCEDURE get_task_number_lov (plan_id IN NUMBER,
650 p_project_number IN VARCHAR2,
651 value IN VARCHAR2,
652 x_lov_sql OUT
653 NOCOPY VARCHAR2) IS
654
655 l_project_id NUMBER ;
656
657 BEGIN
658
659 l_project_id := qa_plan_element_api.get_project_number_id(p_project_number);
660 IF (l_project_id is null) THEN
661 fnd_message.set_name('QA', 'QA_SSQR_DEPENDENT_LOV');
662 fnd_message.set_token('DEPELEM',
663 qa_plan_element_api.get_prompt(plan_id, qa_ss_const.project_number));
664 fnd_msg_pub.add();
665 END IF;
666
667 x_lov_sql := 'select task_number, task_name
668 from mtl_task_v
669 where project_id = :1
670 ORDER BY task_number';
671
672 END get_task_number_lov;
673
674 -- Bug 4270911. SQL bind compliance fix.
675 -- New function added to return bind values.
676 -- Please see bugdb for more details and TD link.
677 -- srhariha. Thu Apr 7 21:43:08 PDT 2005
678
679 FUNCTION get_task_number_bind_values (p_project_number IN VARCHAR2)
680 RETURN VARCHAR2 IS
681
682 l_project_id NUMBER;
683 BEGIN
684
685 l_project_id := qa_plan_element_api.get_project_number_id(p_project_number);
686
687 RETURN to_char(l_project_id);
688
689 END get_task_number_bind_values;
690
691 PROCEDURE get_task_number_lov (value IN VARCHAR2, x_lov_sql OUT
692 NOCOPY VARCHAR2) IS
693
694 BEGIN
695
696 -- anagarwa Thu Jan 29 15:04:26 PST 2004
697 -- Bug 3404863 : task lov should be dependent upon project.
698 get_task_number_lov(NULL, NULL, value, x_lov_sql);
699 /*
700 x_lov_sql := 'SELECT task_number, task_name
701 FROM mtl_task_v
702 ORDER BY task_number';
703 */
704
705 END get_task_number_lov;
706
707
708 PROCEDURE get_rma_number_lov (value IN VARCHAR2, x_lov_sql OUT
709 NOCOPY VARCHAR2) IS
710
711 BEGIN
712 -- Bug 3215866 ksoh Wed Oct 29 16:21:11 PST 2003
713 -- added to_char to lov sql to make it compatible to
714 -- char column when it is union with the kludge sql: select '1'.....
715 x_lov_sql := 'SELECT to_char(sh.order_number), sot.name
716 FROM so_order_types sot,
717 oe_order_headers sh,
718 qa_customers_lov_v rc
719 WHERE sh.order_type_id = sot.order_type_id and
720 sh.sold_to_org_id = rc.customer_id and
721 sh.order_category_code in (''RETURN'', ''MIXED'')
722 ORDER BY sh.order_number';
723
724 END get_rma_number_lov;
725
726 --
727 -- Bug 6161802
728 -- Added procedure to return lov for rma line number
729 -- with rma number as a bind variable
730 -- skolluku Mon Jul 16 22:08:16 PDT 2007
731 --
732 PROCEDURE get_rma_line_num_lov (p_plan_id IN NUMBER,
733 p_rma_number IN VARCHAR2,
734 value IN VARCHAR2,
735 x_lov_sql OUT NOCOPY VARCHAR2) IS
736 BEGIN
737 IF (p_rma_number is null) THEN
738 fnd_message.set_name('QA', 'QA_SSQR_DEPENDENT_LOV');
739 fnd_message.set_token('DEPELEM',
740 qa_plan_element_api.get_prompt(p_plan_id, qa_ss_const.rma_number));
741 fnd_msg_pub.add();
742 END IF;
743
744 x_lov_sql := 'select distinct to_char(oel.line_number),''RMA Number: '' ||
745 sh.order_number || '';'' || ''Item: '' || oel.ordered_item description
746 from oe_order_lines oel, so_order_types sot, oe_order_headers sh
747 where sh.order_type_id = sot.order_type_id ' ||
748 ' and oel.header_id = sh.header_id ' ||
749 ' and oel.line_category_code in (''RETURN'', ''MIXED'') ' ||
750 ' and sh.order_number = :1 ' ||
751 ' order by description, line_number ';
752
753 END get_rma_line_num_lov;
754
755 --
756 -- Bug 6161802
757 -- Return rma number as bind value for rma line number lov
758 -- skolluku Mon Jul 16 22:08:16 PDT 2007
759 --
760 FUNCTION get_rma_line_num_bind_values (p_rma_number IN VARCHAR2)
761 RETURN VARCHAR2 IS
762 BEGIN
763
764 IF (p_rma_number IS NULL) THEN
765 RETURN NULL;
766 END IF;
767
768 RETURN to_char(p_rma_number);
769
770 END get_rma_line_num_bind_values;
771
772
773 -- Bug 3228490 ksoh Fri Oct 31 11:38:21 PST 2003
774 -- check if any dependent element value is null
775 -- if so, put error message with element prompts
776 -- requires plan_id to be passed in to retrieve element prompts.
777 -- old signature calls new signature with plan_id = NULL to
778 -- maintain old behavior
779 PROCEDURE get_uom_lov (plan_id IN NUMBER, org_id IN NUMBER, x_item_name IN VARCHAR2,
780 value IN VARCHAR2, x_lov_sql OUT NOCOPY VARCHAR2) IS
781
782 -- x_item_id NUMBER DEFAULT NULL;
783
784 BEGIN
785 IF ((plan_id is not null) and (x_item_name is null)) THEN
786 fnd_message.set_name('QA', 'QA_SSQR_DEPENDENT_LOV');
787 fnd_message.set_token('DEPELEM',
788 qa_plan_element_api.get_prompt(plan_id, qa_ss_const.item));
789 fnd_msg_pub.add();
790 END IF;
791
792 -- This procedure is used for both uom and component uom
793
794 -- x_item_id := qa_flex_util.get_item_id(org_id, x_item_name);
795
796 x_lov_sql := 'SELECT uom_code, description
797 FROM mtl_item_uoms_view
798 WHERE organization_id = :1
799 AND inventory_item_id = :2
800 ORDER BY uom_code';
801
802 END get_uom_lov;
803
804 PROCEDURE get_uom_lov (org_id IN NUMBER, x_item_name IN VARCHAR2,
805 value IN VARCHAR2, x_lov_sql OUT NOCOPY VARCHAR2) IS
806 BEGIN
807 get_uom_lov (NULL, org_id, x_item_name, value, x_lov_sql);
808 END get_uom_lov;
809
810 -- Bug 5005707. New function to return the proper bind values
811 -- saugupta Mon, 10 Jul 2006 21:51:04 -0700 PDT
812 FUNCTION get_uom_bind_values (p_org_id IN NUMBER,
813 p_item_name IN VARCHAR2)
814 RETURN VARCHAR2 IS
815
816 l_item_id NUMBER;
817
818 BEGIN
819
820 l_item_id := qa_flex_util.get_item_id(p_org_id, p_item_name);
821
822 IF (l_item_id IS NULL) THEN
823 RETURN NULL;
824 END IF;
825
826 RETURN to_char(p_org_id) || g_bind_value_list_seperator || to_char(l_item_id) ;
827
828 END get_uom_bind_values;
829
830
831
832 -- Bug 3228490 ksoh Fri Oct 31 11:38:21 PST 2003
833 -- check if any dependent element value is null
834 -- if so, put error message with element prompts
835 -- requires plan_id to be passed in to retrieve element prompts.
836 -- old signature calls new signature with plan_id = NULL to
837 -- maintain old behavior
838 PROCEDURE get_revision_lov (plan_id IN NUMBER, org_id IN NUMBER, x_item_name IN VARCHAR2,
839 value IN VARCHAR2, x_lov_sql OUT NOCOPY VARCHAR2) IS
840
841 -- x_item_id NUMBER DEFAULT NULL;
842
843 BEGIN
844 IF ((plan_id is not null) AND (x_item_name is null)) THEN
845 fnd_message.set_name('QA', 'QA_SSQR_DEPENDENT_LOV');
846 fnd_message.set_token('DEPELEM',
847 qa_plan_element_api.get_prompt(plan_id, qa_ss_const.item));
848 fnd_msg_pub.add();
849 END IF;
850
851 -- This procedure is used for both revision and component revision
852
853 -- x_item_id := qa_flex_util.get_item_id(org_id, x_item_name);
854
855 -- anagarwa Mon Feb 24 17:08:57 PST 2003
856 -- Bug 2808693
857 -- using QLTDATE.date_to_user for effectivity date as LOV's in selfservice
858 -- expect both selected columns to be varchar2 or they give an Error.
859
860 -- Bug 5371467. Rewriting revision LOV SQL
861 -- saugupta Tue, 18 Jul 2006 01:30:18 -0700 PDT
862 /*
863 x_lov_sql := 'SELECT revision, QLTDATE.date_to_user(effectivity_date)
864 FROM mtl_item_revisions
865 WHERE inventory_item_id = :1
866 AND organization_id = :2
867 ORDER BY revision';
868 */
869 x_lov_sql := 'SELECT revision, QLTDATE.date_to_user(effectivity_date)
870 FROM mtl_item_revisions mir,
871 mtl_system_items_kfv msi
872 WHERE mir.inventory_item_id = msi.inventory_item_id
873 AND mir.organization_id = msi.organization_id
874 AND msi.revision_qty_control_code = 2
875 AND mir.inventory_item_id = :1
876 AND mir.organization_id = :2
877 ORDER BY revision';
878
879 END get_revision_lov;
880
881
882 FUNCTION get_revision_bind_values (p_org_id IN NUMBER,
883 p_item_name IN VARCHAR2)
884 RETURN VARCHAR2 IS
885
886 l_item_id NUMBER;
887
888 BEGIN
889
890 l_item_id := qa_flex_util.get_item_id(p_org_id, p_item_name);
891
892 IF (l_item_id IS NULL) THEN
893 RETURN NULL;
894 END IF;
895
896 RETURN to_char(l_item_id) || g_bind_value_list_seperator || to_char(p_org_id) ;
897
898 END get_revision_bind_values;
899
900 PROCEDURE get_revision_lov (org_id IN NUMBER, x_item_name IN VARCHAR2,
901 value IN VARCHAR2, x_lov_sql OUT NOCOPY VARCHAR2) IS
902 BEGIN
903 get_revision_lov (NULL, org_id, x_item_name, value, x_lov_sql);
904 END get_revision_lov;
905
906
907 PROCEDURE get_subinventory_lov (org_id IN NUMBER, value IN VARCHAR2,
908 x_lov_sql OUT NOCOPY VARCHAR2) IS
909
910 BEGIN
911
912 -- This procedure is used for both subinventory and component subinventory
913
914 x_lov_sql := 'SELECT secondary_inventory_name, description
915 FROM mtl_secondary_inventories
916 WHERE organization_id = :1
917 AND nvl(disable_date, sysdate+1) > sysdate ';
918 -- ORDER BY secondary_inventory_name';
919
920 END get_subinventory_lov;
921
922
923 -- anagarwa Thu Aug 12 15:49:51 PDT 2004
924 -- bug 3830258 incorrect LOVs in QWB
925 -- synced up the lot number lov with forms
926 PROCEDURE get_lot_number_lov (p_plan_id IN NUMBER,
927 p_org_id IN NUMBER,
928 p_item IN VARCHAR2,
929 value IN VARCHAR2,
930 x_lov_sql OUT NOCOPY VARCHAR2) IS
931 BEGIN
932
933 IF (p_item is null) THEN
934 fnd_message.set_name('QA', 'QA_SSQR_DEPENDENT_LOV');
935 fnd_message.set_token('DEPELEM',
936 qa_plan_element_api.get_prompt(p_plan_id, qa_ss_const.item));
937 fnd_msg_pub.add();
938 END IF;
939
940 x_lov_sql := 'select lot_number, description
941 from mtl_lot_numbers
942 where organization_id = :1' ||
943 ' and inventory_item_id = :2 ' ||
944 ' and (disable_flag = 2 or disable_flag is null)';
945
946 END get_lot_number_lov;
947
948 FUNCTION get_lot_number_bind_values(p_org_id IN NUMBER,
949 p_item_name IN VARCHAR2)
950 RETURN VARCHAR2 IS
951
952
953 l_item_id NUMBER;
954
955 BEGIN
956
957 l_item_id := qa_flex_util.get_item_id(p_org_id, p_item_name);
958
959 IF l_item_id IS NULL THEN
960 RETURN NULL;
961 END IF;
962
963 RETURN to_char(p_org_id) || g_bind_value_list_seperator || to_char(l_item_id);
964
965 END get_lot_number_bind_values;
966
967 -- anagarwa Thu Aug 12 15:49:51 PDT 2004
968 -- bug 3830258 incorrect LOVs in QWB
969 -- synced up the component lot number lov with forms
970 PROCEDURE get_comp_lot_number_lov (p_plan_id IN NUMBER,
971 p_org_id IN NUMBER,
972 p_comp_item IN VARCHAR2,
973 value IN VARCHAR2,
974 x_lov_sql OUT NOCOPY VARCHAR2) IS
975 -- l_lov_sql VARCHAR2(300);
976 BEGIN
977 IF (p_comp_item is null) THEN
978 fnd_message.set_name('QA', 'QA_SSQR_DEPENDENT_LOV');
979 fnd_message.set_token('DEPELEM',
980 qa_plan_element_api.get_prompt(p_plan_id, qa_ss_const.comp_item));
981 fnd_msg_pub.add();
982 END IF;
983
984 x_lov_sql := 'select lot_number, description
985 from mtl_lot_numbers
986 where organization_id = :1 '||
987 ' and inventory_item_id = :2 ' ||
988 ' and (disable_flag = 2 or disable_flag is null)';
989
990 END get_comp_lot_number_lov;
991
992 FUNCTION get_comp_lot_bind_values(p_org_id IN NUMBER,
993 p_item_name IN VARCHAR2)
994 RETURN VARCHAR2 IS
995
996
997 l_item_id NUMBER;
998
999 BEGIN
1000
1001 l_item_id := qa_flex_util.get_item_id(p_org_id, p_item_name);
1002
1003 IF l_item_id IS NULL THEN
1004 RETURN NULL;
1005 END IF;
1006
1007 RETURN to_char(p_org_id) || g_bind_value_list_seperator || to_char(l_item_id);
1008
1009 END get_comp_lot_bind_values;
1010
1011 --
1012 -- Bug 5003511. R12 Performance fix. Obsolete the unused method
1013 -- so that it wont appear in fututre SQL Literal reports.
1014 -- srhariha. Wed Feb 1 04:16:10 PST 2006
1015 --
1016
1017 PROCEDURE get_lot_number_lov (x_transaction_id IN NUMBER, value IN VARCHAR2,
1018 x_lov_sql OUT NOCOPY VARCHAR2) IS
1019
1020 BEGIN
1021 /*
1022 x_lov_sql := 'SELECT lot_number, lot_expiration_date
1023 FROM mtl_transaction_lots_temp
1024 WHERE transaction_temp_id = ' || x_transaction_id || '
1025 ORDER BY lot_number';
1026 */
1027 x_lov_sql := NULL;
1028
1029 END get_lot_number_lov;
1030
1031 -- anagarwa Thu Aug 12 15:49:51 PDT 2004
1032 -- bug 3830258 incorrect LOVs in QWB
1033 -- synced up the serial number lov with forms
1034 PROCEDURE get_serial_number_lov (p_plan_id IN NUMBER,
1035 p_org_id IN NUMBER,
1036 p_item IN VARCHAR2,
1037 p_revision IN VARCHAR2,
1038 p_lot_number IN VARCHAR2,
1039 p_value IN VARCHAR2,
1040 x_lov_sql OUT NOCOPY VARCHAR2) IS
1041
1042 l_item_id NUMBER;
1043 j NUMBER;
1044 BEGIN
1045
1046 IF (p_item is null) THEN
1047 fnd_message.set_name('QA', 'QA_SSQR_DEPENDENT_LOV');
1048 fnd_message.set_token('DEPELEM',
1049 qa_plan_element_api.get_prompt(p_plan_id, qa_ss_const.item));
1050 fnd_msg_pub.add();
1051 END IF;
1052
1053 l_item_id := qa_flex_util.get_item_id(p_org_id, p_item);
1054
1055 x_lov_sql := 'SELECT msn.serial_number, msn.current_status_name
1056 FROM mtl_serial_numbers_all_v msn
1057 WHERE msn.current_organization_id = :1 ' ||
1058 ' AND msn.inventory_item_id = :2 ';
1059
1060 -- anagarwa Thu Aug 12 15:49:51 PDT 2004
1061 -- discussed with Bryan. This sql is a little different from the one
1062 -- being used in forms. The reason is that if user selects a lot number
1063 -- we expect the item to be lot controlled too. So serial lov is restricted
1064 -- by lot number
1065 j := 3;
1066 IF (qa_plan_element_api.element_in_plan(p_plan_id, qa_ss_const.lot_number)
1067 AND p_lot_number is not NULL
1068 AND is_item_lot_controlled(l_item_id, p_org_id)) THEN
1069 x_lov_sql := x_lov_sql || ' AND msn.lot_number = :' || to_char(j) || ' ';
1070 j := j+1;
1071 END IF;
1072 -- anagarwa Thu Aug 12 15:49:51 PDT 2004
1073 -- if revision is entered then restrict the serial lov with revision
1074 IF (qa_plan_element_api.element_in_plan(p_plan_id, qa_ss_const.revision)
1075 AND p_revision is not NULL
1076 AND is_item_revision_controlled(l_item_id, p_org_id)) THEN
1077 x_lov_sql := x_lov_sql || ' AND msn.revision = :' || to_char(j) || ' ';
1078 END IF;
1079
1080 END get_serial_number_lov;
1081
1082
1083 FUNCTION get_serial_no_bind_values (p_plan_id IN NUMBER,
1084 p_org_id IN NUMBER,
1085 p_item IN VARCHAR2,
1086 p_revision IN VARCHAR2,
1087 p_lot_number IN VARCHAR2)
1088 RETURN VARCHAR2 IS
1089
1090 l_item_id NUMBER;
1091 l_ret_string VARCHAR2(1000);
1092 BEGIN
1093
1094
1095 l_item_id := qa_flex_util.get_item_id(p_org_id, p_item);
1096
1097 IF l_item_id IS NULL THEN
1098 RETURN NULL;
1099 END IF;
1100
1101 l_ret_string := to_char(p_org_id) || g_bind_value_list_seperator || to_char(l_item_id);
1102
1103
1104 IF (qa_plan_element_api.element_in_plan(p_plan_id, qa_ss_const.lot_number)
1105 AND p_lot_number is not NULL
1106 AND is_item_lot_controlled(l_item_id, p_org_id)) THEN
1107 l_ret_string := l_ret_string || g_bind_value_list_seperator || p_lot_number;
1108 END IF;
1109
1110 IF (qa_plan_element_api.element_in_plan(p_plan_id, qa_ss_const.revision)
1111 AND p_revision is not NULL
1112 AND is_item_revision_controlled(l_item_id, p_org_id)) THEN
1113 l_ret_string := l_ret_string || g_bind_value_list_seperator || p_revision;
1114 END IF;
1115
1116 RETURN l_ret_string;
1117
1118 END get_serial_no_bind_values;
1119
1120
1121 -- anagarwa Thu Aug 12 15:49:51 PDT 2004
1122 -- bug 3830258 incorrect LOVs in QWB
1123 -- synced up the component serial number lov with forms
1124 PROCEDURE get_comp_serial_number_lov (p_plan_id IN NUMBER,
1125 p_org_id IN NUMBER,
1126 p_comp_item IN VARCHAR2,
1127 p_comp_revision IN VARCHAR2,
1128 p_comp_lot_number IN VARCHAR2,
1129 p_value IN VARCHAR2,
1130 x_lov_sql OUT NOCOPY VARCHAR2) IS
1131 l_item_id NUMBER;
1132 j NUMBER;
1133 BEGIN
1134
1135 IF (p_comp_item is null) THEN
1136 fnd_message.set_name('QA', 'QA_SSQR_DEPENDENT_LOV');
1137 fnd_message.set_token('DEPELEM',
1138 qa_plan_element_api.get_prompt(p_plan_id, qa_ss_const.comp_item));
1139 fnd_msg_pub.add();
1140 END IF;
1141
1142 l_item_id := qa_flex_util.get_item_id(p_org_id, p_comp_item);
1143
1144 x_lov_sql := 'SELECT msn.serial_number, msn.current_status_name
1145 FROM mtl_serial_numbers_all_v msn
1146 WHERE msn.current_organization_id = :1 ' ||
1147 ' AND msn.inventory_item_id = :2 ';
1148 j := 3;
1149 IF (qa_plan_element_api.element_in_plan(p_plan_id, qa_ss_const.comp_lot_number)
1150 AND p_comp_lot_number is not NULL
1151 AND is_item_lot_controlled(l_item_id, p_org_id)) THEN
1152 x_lov_sql := x_lov_sql || ' AND msn.lot_number = :' || to_char(j) || ' ';
1153 j := j+1;
1154 END IF;
1155 IF (qa_plan_element_api.element_in_plan(p_plan_id, qa_ss_const.comp_revision)
1156 AND p_comp_revision is not NULL
1157 AND is_item_revision_controlled(l_item_id, p_org_id)) THEN
1158 x_lov_sql := x_lov_sql || ' AND msn.revision = :' || to_char(j) || ' ';
1159 END IF;
1160
1161 END get_comp_serial_number_lov;
1162
1163 FUNCTION get_comp_serial_no_bind_values (p_plan_id IN NUMBER,
1164 p_org_id IN NUMBER,
1165 p_item IN VARCHAR2,
1166 p_revision IN VARCHAR2,
1167 p_lot_number IN VARCHAR2)
1168 RETURN VARCHAR2 IS
1169
1170 l_item_id NUMBER;
1171 l_ret_string VARCHAR2(1000);
1172 BEGIN
1173
1174
1175 l_item_id := qa_flex_util.get_item_id(p_org_id, p_item);
1176
1177 IF l_item_id IS NULL THEN
1178 RETURN NULL;
1179 END IF;
1180
1181 l_ret_string := to_char(p_org_id) || g_bind_value_list_seperator || to_char(l_item_id);
1182
1183
1184 IF (qa_plan_element_api.element_in_plan(p_plan_id, qa_ss_const.lot_number)
1185 AND p_lot_number is not NULL
1186 AND is_item_lot_controlled(l_item_id, p_org_id)) THEN
1187 l_ret_string := l_ret_string || g_bind_value_list_seperator || p_lot_number;
1188 END IF;
1189
1190 IF (qa_plan_element_api.element_in_plan(p_plan_id, qa_ss_const.revision)
1191 AND p_revision is not NULL
1192 AND is_item_revision_controlled(l_item_id, p_org_id)) THEN
1193 l_ret_string := l_ret_string || g_bind_value_list_seperator || p_revision;
1194 END IF;
1195
1196 RETURN l_ret_string;
1197
1198 END get_comp_serial_no_bind_values;
1199
1200
1201
1202 -- Bug 3228490 ksoh Fri Oct 31 11:38:21 PST 2003
1203 -- check if any dependent element value is null
1204 -- if so, put error message with element prompts
1205 -- requires plan_id to be passed in to retrieve element prompts.
1206 -- old signature calls new signature with plan_id = NULL to
1207 -- maintain old behavior
1208
1209 --
1210 -- Bug 5003511. R12 Performance fix. Obsolete the unused method
1211 -- so that it wont appear in fututre SQL Literal reports.
1212 -- srhariha. Wed Feb 1 04:16:10 PST 2006
1213 --
1214
1215 PROCEDURE get_serial_number_lov (plan_id IN NUMBER, x_transaction_id IN NUMBER, x_lot_number
1216 IN VARCHAR2, value IN VARCHAR2, x_lov_sql OUT NOCOPY VARCHAR2) IS
1217
1218 BEGIN
1219 /*
1220 IF (x_lot_number is null) THEN
1221 fnd_message.set_name('QA', 'QA_SSQR_DEPENDENT_LOV');
1222 fnd_message.set_token('DEPELEM',
1223 qa_plan_element_api.get_prompt(plan_id, qa_ss_const.lot_number));
1224 fnd_msg_pub.add();
1225 END IF;
1226
1227 x_lov_sql := 'SELECT msn.serial_number, msn.current_status
1228 FROM mtl_serial_numbers msn,
1229 mtl_transaction_lots_temp mtlt
1230 WHERE msn.line_mark_id = ' || x_transaction_id || '
1231 AND mtlt.transaction_temp_id = msn.line_mark_id
1232 AND mtlt.serial_transaction_temp_id = msn.lot_line_mark_id
1233 AND mtlt.lot_number = ' || '''' || x_lot_number || '''' || '
1234 AND mtlt.lot_number IS NOT NULL
1235 UNION ALL
1236 SELECT msn.serial_number, msn.current_status
1237 FROM mtl_serial_numbers msn
1238 WHERE msn.line_mark_id = ' || x_transaction_id || '
1239 ORDER BY serial_number';
1240 */
1241 x_lov_sql := NULL;
1242 END get_serial_number_lov;
1243
1244 PROCEDURE get_serial_number_lov (x_transaction_id IN NUMBER, x_lot_number
1245 IN VARCHAR2, value IN VARCHAR2, x_lov_sql OUT NOCOPY VARCHAR2) IS
1246
1247 BEGIN
1248 get_serial_number_lov (NULL, x_transaction_id, x_lot_number, value, x_lov_sql);
1249 END get_serial_number_lov;
1250
1251 PROCEDURE get_asset_instance_number_lov (plan_id IN NUMBER, x_org_id IN NUMBER, x_asset_group IN VARCHAR2,
1252 x_asset_number IN VARCHAR2,value IN VARCHAR2, x_lov_sql OUT NOCOPY VARCHAR2) IS
1253 j NUMBER;
1254 BEGIN
1255
1256 x_lov_sql :=
1257 'SELECT cii.instance_number, cii.instance_description
1258 FROM
1259 csi_item_instances cii, mtl_system_items_b msib, mtl_parameters mp
1260 WHERE
1261 msib.organization_id = mp.organization_id and
1262 msib.organization_id = cii.last_vld_organization_id and
1263 msib.inventory_item_id = cii.inventory_item_id and
1264 msib.eam_item_type in (1,3) and
1265 msib.serial_number_control_code <> 1 and
1266 sysdate between nvl(cii.active_start_date, sysdate-1)
1267 and nvl(cii.active_end_date, sysdate+1) and
1268 mp.maint_organization_id = :1';
1269
1270
1271 j := 2;
1272 IF (x_asset_group is not NULL) THEN
1273 x_lov_sql := x_lov_sql || ' AND cii.inventory_item_id = :' || to_char(j) || ' ';
1274 j := j+1;
1275 END IF;
1276
1277 IF (x_asset_number is not NULL ) THEN
1278 x_lov_sql := x_lov_sql || ' AND cii.serial_number = :' || to_char(j) || ' ';
1279 END IF;
1280 x_lov_sql := x_lov_sql || 'order by cii.instance_number';
1281
1282 END get_asset_instance_number_lov;
1283
1284 FUNCTION get_asset_inst_num_bind_values (p_org_id IN NUMBER,
1285 p_asset_group IN VARCHAR2, p_asset_number IN VARCHAR2)
1286 RETURN VARCHAR2 IS
1287 l_asset_group_id NUMBER;
1288 l_ret_string VARCHAR2(1000);
1289
1290 BEGIN
1291 l_asset_group_id := qa_plan_element_api.get_asset_group_id(p_org_id, p_asset_group);
1292 l_ret_string := to_char(p_org_id);
1293 --RETURN to_char(p_org_id) || g_bind_value_list_seperator || to_char(l_asset_group_id) ||
1294 -- g_bind_value_list_seperator || p_asset_number;
1295
1296 IF l_asset_group_id is not NULL THEN
1297 l_ret_string := l_ret_string||g_bind_value_list_seperator||to_char(l_asset_group_id);
1298 END IF;
1299
1300 IF p_asset_number is not NULL THEN
1301 l_ret_string := l_ret_string||g_bind_value_list_seperator||to_char(p_asset_number);
1302 END IF;
1303
1304 RETURN l_ret_string;
1305 END get_asset_inst_num_bind_values;
1306 -- Bug 3228490 ksoh Fri Oct 31 11:38:21 PST 2003
1307 -- check if any dependent element value is null
1308 -- if so, put error message with element prompts
1309 -- requires plan_id to be passed in to retrieve element prompts.
1310 -- old signature calls new signature with plan_id = NULL to
1311 -- maintain old behavior
1312 PROCEDURE get_asset_number_lov (plan_id IN NUMBER, x_org_id IN NUMBER, x_asset_group IN VARCHAR2,
1313 value IN VARCHAR2, x_lov_sql OUT NOCOPY VARCHAR2) IS
1314
1315 BEGIN
1316 IF ((plan_id is not null) AND (x_asset_group is null)) THEN
1317 fnd_message.set_name('QA', 'QA_SSQR_DEPENDENT_LOV');
1318 fnd_message.set_token('DEPELEM',
1319 qa_plan_element_api.get_prompt(plan_id, qa_ss_const.asset_group));
1320 fnd_msg_pub.add();
1321 END IF;
1322
1323 --dgupta: Start R12 EAM Integration. Bug 4345492
1324 x_lov_sql := 'SELECT
1325 distinct msn.serial_number, msn.descriptive_text
1326 FROM
1327 mtl_serial_numbers msn, csi_item_instances cii, mtl_system_items_b msib, mtl_parameters mp
1328 WHERE
1329 msib.organization_id = mp.organization_id and
1330 msib.organization_id = cii.last_vld_organization_id and
1331 msib.inventory_item_id = cii.inventory_item_id and
1332 msib.eam_item_type in (1,3) and
1333 sysdate between nvl(cii.active_start_date(+), sysdate-1)
1334 and nvl(cii.active_end_date(+), sysdate+1) and
1335 msib.organization_id = msn.current_organization_id and
1336 cii.serial_number=msn.serial_number and
1337 msib.inventory_item_id = msn.inventory_item_id and
1338 mp.maint_organization_id = :1 and
1339 msn.inventory_item_id = :2 and --removed nvl: serial number requires asset group as well
1340 cii.instance_id= nvl(:3, cii.instance_id)
1341 order by msn.serial_number';
1342 --dgupta: End R12 EAM Integration. Bug 4345492
1343
1344 END get_asset_number_lov;
1345
1346
1347 --dgupta: Start R12 EAM Integration. Bug 4345492
1348 FUNCTION get_asset_number_bind_values (p_org_id IN NUMBER,
1349 p_asset_group IN VARCHAR2, p_asset_instance_number IN VARCHAR2)
1350 RETURN VARCHAR2 IS
1351 l_asset_group_id NUMBER;
1352 l_asset_instance_id NUMBER;
1353
1354 BEGIN
1355 l_asset_group_id := qa_plan_element_api.get_asset_group_id(p_org_id, p_asset_group);
1356 l_asset_instance_id := qa_plan_element_api.get_asset_instance_id(p_asset_instance_number);
1357
1358 RETURN to_char(p_org_id) || g_bind_value_list_seperator || to_char(l_asset_group_id) ||
1359 g_bind_value_list_seperator || to_char(l_asset_instance_id);
1360
1361 END get_asset_number_bind_values;
1362 --dgupta: End R12 EAM Integration. Bug 4345492
1363
1364
1365 PROCEDURE get_asset_number_lov (x_org_id IN NUMBER, x_asset_group IN VARCHAR2,
1366 value IN VARCHAR2, x_lov_sql OUT NOCOPY VARCHAR2) IS
1367 BEGIN
1368 get_asset_number_lov (NULL, x_org_id, x_asset_group, value, x_lov_sql);
1369 END get_asset_number_lov;
1370
1371 --
1372 -- Removed the DEFAULT clause to make the code GSCC compliant
1373 -- List of changed arguments.
1374 -- Old
1375 -- production_line IN VARCHAR2 DEFAULT NULL
1376 -- New
1377 -- production_line IN VARCHAR2
1378 --
1379
1380 -- Bug 3228490 ksoh Fri Oct 31 11:38:21 PST 2003
1381 -- check if any dependent element value is null
1382 -- if so, put error message with element prompts
1383 -- requires plan_id to be passed in to retrieve element prompts.
1384 -- old signature calls new signature with plan_id = NULL to
1385 -- maintain old behavior
1386 PROCEDURE get_op_seq_number_lov (plan_id IN NUMBER, org_id IN NUMBER, value IN VARCHAR2,
1387 job_name IN VARCHAR2, production_line IN VARCHAR2,
1388 x_lov_sql OUT NOCOPY VARCHAR2) IS
1389
1390 x_line_id NUMBER DEFAULT NULL;
1391 -- x_wip_entity_id NUMBER DEFAULT NULL;
1392
1393 BEGIN
1394
1395 -- anagarwa Sun May 2 11:15:38 PDT 2004
1396 -- Bug 3574820 If production line is not present then we cannot save op seq
1397 -- number. This is not consistent with forms behaviour where for
1398 -- to/from op seq number only job is the required field. Production line is
1399 -- used only if present. Hence changing the following if condition to look
1400 -- for job only.
1401 IF (plan_id is not null) AND (job_name is null) THEN
1402 fnd_message.set_name('QA', 'QA_SSQR_DEPENDENT_LOV');
1403 fnd_message.set_token('DEPELEM',
1404 qa_plan_element_api.get_prompt(plan_id, qa_ss_const.job_name));
1405 fnd_msg_pub.add();
1406 END IF;
1407
1408 IF (production_line IS NOT NULL) THEN
1409 x_line_id := qa_plan_element_api.get_production_line_id(org_id,
1410 production_line);
1411 END IF;
1412
1413 -- x_wip_entity_id := qa_plan_element_api.get_job_id(org_id, job_name);
1414
1415 IF (x_line_id IS NULL) THEN
1416
1417 -- anagarwa Thu Jan 16 19:02:01 PST 2003
1418 -- Bug 2751198
1419 -- to_char added as first col for lov is supposed to be a varchar
1420 x_lov_sql := 'SELECT to_char(operation_seq_num), operation_code
1421 FROM wip_operations_all_v
1422 WHERE wip_entity_id = :1
1423 AND organization_id = :2
1424 ORDER BY operation_seq_num';
1425
1426 ELSE
1427
1428 -- anagarwa Thu Jan 16 19:02:01 PST 2003
1429 -- Bug 2751198
1430 -- to_char added as first col for lov is supposed to be a varchar
1431 x_lov_sql := 'SELECT to_char(operation_seq_num), operation_code
1432 FROM wip_operations_all_v
1433 WHERE wip_entity_id = :1
1434 AND organization_id = :2
1435 AND repetitive_schedule_id =
1436 (
1437 SELECT repetitive_schedule_id
1438 FROM wip_first_open_schedule_v
1439 WHERE line_id = :3
1440 AND wip_entity_id = :4
1441 AND organization_id = :5
1442 )
1443 ORDER BY operation_seq_num';
1444
1445 END IF;
1446
1447 END get_op_seq_number_lov;
1448
1449 FUNCTION get_op_seq_no_bind_values (p_plan_id IN NUMBER,
1450 p_org_id IN NUMBER,
1451 p_job_name IN VARCHAR2,
1452 p_production_line IN VARCHAR2)
1453 RETURN VARCHAR2 IS
1454
1455 l_line_id NUMBER;
1456 l_wip_entity_id NUMBER;
1457
1458 BEGIN
1459
1460 l_line_id := NULL;
1461
1462 IF (p_production_line IS NOT NULL OR p_production_line <> '') THEN
1463 l_line_id := qa_plan_element_api.get_production_line_id(p_org_id,
1464 p_production_line);
1465 END IF;
1466
1467 l_wip_entity_id := qa_plan_element_api.get_job_id(p_org_id,p_job_name);
1468
1469 IF (l_line_id IS NULL) THEN
1470
1471 RETURN to_char(l_wip_entity_id) || g_bind_value_list_seperator || to_char(p_org_id);
1472
1473 ELSE
1474
1475 RETURN to_char(l_wip_entity_id) || g_bind_value_list_seperator || to_char(p_org_id) ||
1476 g_bind_value_list_seperator || to_char(l_line_id) ||
1477 g_bind_value_list_seperator || to_char(l_wip_entity_id) ||
1478 g_bind_value_list_seperator || to_char(p_org_id);
1479
1480 END IF;
1481
1482 END get_op_seq_no_bind_values;
1483
1484 PROCEDURE get_op_seq_number_lov (org_id IN NUMBER, value IN VARCHAR2,
1485 job_name IN VARCHAR2, production_line IN VARCHAR2,
1486 x_lov_sql OUT NOCOPY VARCHAR2) IS
1487 BEGIN
1488 get_op_seq_number_lov (NULL, org_id, value, job_name, production_line, x_lov_sql);
1489 END get_op_seq_number_lov;
1490 --
1491 -- MOAC Project. 4637896
1492 -- Now we are passing po header id directly.
1493 -- Corresponding change in dependent lov evaluation.
1494 -- srhariha. Tue Oct 11 04:22:16 PDT 2005.
1495 --
1496
1497 -- Bug 3228490 ksoh Fri Oct 31 11:38:21 PST 2003
1498 -- check if any dependent element value is null
1499 -- if so, put error message with element prompts
1500 -- requires plan_id to be passed in to retrieve element prompts.
1501 -- old signature calls new signature with plan_id = NULL to
1502 -- maintain old behavior
1503 PROCEDURE get_po_line_number_lov (plan_id IN NUMBER, po_header_id IN VARCHAR2, value IN
1504 VARCHAR2, x_lov_sql OUT NOCOPY VARCHAR2) IS
1505
1506 -- po_number_id NUMBER;
1507
1508 BEGIN
1509 -- MOAC. Just changed the name becuase we are not using po_header_id
1510 -- in this method except for this dependency check.
1511 IF ((plan_id is not null) AND (po_header_id is null)) THEN
1512 fnd_message.set_name('QA', 'QA_SSQR_DEPENDENT_LOV');
1513 fnd_message.set_token('DEPELEM',
1514 qa_plan_element_api.get_prompt(plan_id, qa_ss_const.po_number));
1515 fnd_msg_pub.add();
1516 END IF;
1517
1518 -- po_number_id := qa_plan_element_api.get_po_number_id(po_number);
1519
1520 -- Bug 3215866 ksoh Wed Oct 29 16:21:11 PST 2003
1521 -- fixed sql to make it return value instead of id.
1522 x_lov_sql := 'SELECT to_char(line_num), concatenated_segments
1523 FROM PO_LINES_VAL_TRX_V
1524 WHERE po_header_id = :1
1525 ORDER BY line_num';
1526
1527 END get_po_line_number_lov;
1528
1529
1530 FUNCTION get_po_line_no_bind_values (p_po_header_id IN VARCHAR2)
1531 RETURN VARCHAR2 IS
1532
1533 BEGIN
1534
1535 -- l_po_number_id := qa_plan_element_api.get_po_number_id(p_po_number);
1536 RETURN p_po_header_id;
1537
1538 END get_po_line_no_bind_values;
1539
1540
1541 PROCEDURE get_po_line_number_lov (po_number IN VARCHAR2, value IN
1542 VARCHAR2, x_lov_sql OUT NOCOPY VARCHAR2) IS
1543 BEGIN
1544 get_po_line_number_lov (NULL, po_number, value, x_lov_sql);
1545 END get_po_line_number_lov;
1546
1547
1548 -- Bug 3228490 ksoh Fri Oct 31 11:38:21 PST 2003
1549 -- check if any dependent element value is null
1550 -- if so, put error message with element prompts
1551 -- requires plan_id to be passed in to retrieve element prompts.
1552 -- old signature calls new signature with plan_id = NULL to
1553 -- maintain old behavior
1554 --
1555 -- bug 9652549 CLM changes
1556 --
1557
1558 PROCEDURE get_po_shipments_lov (plan_id IN NUMBER, po_line_num IN VARCHAR2, po_header_id IN VARCHAR2,
1559 value IN VARCHAR2, x_lov_sql OUT NOCOPY VARCHAR2) IS
1560
1561 -- po_number_id NUMBER;
1562
1563 BEGIN
1564 IF ((plan_id is not null) AND ((po_header_id is null) OR (po_line_num is null))) THEN
1565 fnd_message.set_name('QA', 'QA_SSQR_DEPENDENT_LOV');
1566 fnd_message.set_token('DEPELEM',
1567 qa_plan_element_api.get_prompt(plan_id, qa_ss_const.po_number) ||
1568 ', ' || qa_plan_element_api.get_prompt(plan_id, qa_ss_const.po_line_num));
1569 fnd_msg_pub.add();
1570 END IF;
1571
1572 -- po_number_id := qa_plan_element_api.get_po_number_id(po_number);
1573
1574 -- Bug 3215866 ksoh Wed Oct 29 16:21:11 PST 2003
1575 -- added to_char to lov sql to make it compatible to
1576 -- char column when it is union with the kludge sql: select '1'.....
1577
1578 -- Bug 5003511. SQL Repository Fix SQL ID: 15008892
1579 /*
1580 x_lov_sql := 'SELECT to_char(shipment_num), shipment_type
1581 FROM po_shipments_all_v
1582 WHERE po_line_id =
1583 (SELECT po_line_id
1584 FROM po_lines_val_v
1585 WHERE line_num = :1
1586 AND po_header_id = :2)';
1587 */
1588 --
1589 -- bug 9652549 CLM changes
1590 --
1591 x_lov_sql := 'SELECT to_char(pll.shipment_num), pll.shipment_type
1592 FROM PO_LINE_LOCATIONS_TRX_V pll
1593 WHERE pll.ship_to_location_id is not null
1594 AND pll.po_line_id =
1595 (SELECT po_line_id
1596 FROM PO_LINES_TRX_V
1597 WHERE line_num = :1
1598 AND po_header_id= :2 )';
1599
1600
1601 END get_po_shipments_lov;
1602
1603 --
1604 -- bug 9652549 CLM changes
1605 --
1606 FUNCTION get_po_shipments_bind_values (p_po_line_num IN VARCHAR2,
1607 p_po_header_id IN VARCHAR2)
1608 RETURN VARCHAR2 IS
1609
1610
1611 BEGIN
1612
1613 -- l_po_number_id := qa_plan_element_api.get_po_number_id(p_po_number);
1614
1615 RETURN to_char(p_po_line_num) || g_bind_value_list_seperator || p_po_header_id;
1616
1617 END get_po_shipments_bind_values;
1618
1619 --
1620 -- Bug 9652549 CLM changes
1621 --
1622 PROCEDURE get_po_shipments_lov (po_line_num IN VARCHAR2, po_number IN VARCHAR2,
1623 value IN VARCHAR2, x_lov_sql OUT NOCOPY VARCHAR2) IS
1624
1625 -- po_number_id NUMBER;
1626
1627 BEGIN
1628 get_po_shipments_lov (NULL, po_line_num, po_number, value, x_lov_sql);
1629 END get_po_shipments_lov;
1630
1631 --
1632 -- Bug 9817478
1633 -- Overloaded method which would be used if PO Release Number is not NULL.
1634 -- In this case, the LOV for PO Shipments should consider PO Release Num too.
1635 -- skolluku
1636 --
1637 PROCEDURE get_po_shipments_lov (plan_id IN NUMBER, po_line_num IN VARCHAR2, po_header_id IN VARCHAR2,
1638 po_release_num IN NUMBER, value IN VARCHAR2, x_lov_sql OUT NOCOPY VARCHAR2) IS
1639
1640 BEGIN
1641 IF ((plan_id is not null) AND ((po_header_id is null) OR (po_line_num is null))) THEN
1642 fnd_message.set_name('QA', 'QA_SSQR_DEPENDENT_LOV');
1643 fnd_message.set_token('DEPELEM',
1644 qa_plan_element_api.get_prompt(plan_id, qa_ss_const.po_number) ||
1645 ', ' || qa_plan_element_api.get_prompt(plan_id, qa_ss_const.po_line_num));
1646 fnd_msg_pub.add();
1647 END IF;
1648
1649 x_lov_sql := 'select to_char(shipment_num) shipment_number,shipment_type
1650 from po_shipments_all_v
1651 where po_line_id = (select po_line_id
1652 from po_lines_val_trx_v
1653 where line_num = :1
1654 and po_header_id = :2)
1655 and po_release_id = :3';
1656
1657 END get_po_shipments_lov;
1658
1659 -- Bug 9817478. Overloaded bind values method which returns PO Release Id
1660 -- also, as a parent to po shipments.
1661 FUNCTION get_po_shipments_bind_values (p_po_line_num IN VARCHAR2,
1662 po_header_id IN VARCHAR2,
1663 p_po_release_num IN NUMBER)
1664 RETURN VARCHAR2 IS
1665
1666 l_po_rel_id NUMBER;
1667
1668 BEGIN
1669
1670 l_po_rel_id := qa_plan_element_api.get_po_release_number_id(p_po_release_num, po_header_id);
1671
1672 RETURN to_char(p_po_line_num) || g_bind_value_list_seperator || po_header_id || g_bind_value_list_seperator || to_char(l_po_rel_id);
1673
1674 END get_po_shipments_bind_values;
1675
1676 PROCEDURE get_receipt_num_lov (value IN VARCHAR2, x_lov_sql OUT NOCOPY VARCHAR2) IS
1677
1678 BEGIN
1679
1680 -- Bug 7491455.FP For bug 6800960
1681 -- changing the validation query for receipt number to include RMA receipts
1682 -- pdube Fri Oct 17 00:14:28 PDT 2008
1683 -- x_lov_sql := 'SELECT receipt_num, vendor_name
1684 -- FROM rcv_receipts_all_v
1685 -- ORDER BY receipt_num';
1686 x_lov_sql := 'SELECT receipt_num, vendor_name
1687 FROM ( SELECT DISTINCT RCVSH.RECEIPT_NUM,
1688 POV.VENDOR_NAME
1689 FROM RCV_SHIPMENT_HEADERS RCVSH,
1690 PO_VENDORS POV,
1691 RCV_TRANSACTIONS RT
1692 WHERE RCVSH.RECEIPT_SOURCE_CODE in (''VENDOR'',''CUSTOMER'') AND
1693 RCVSH.VENDOR_ID = POV.VENDOR_ID(+) AND
1694 RT.SHIPMENT_HEADER_ID = RCVSH.SHIPMENT_HEADER_ID)
1695 ORDER BY receipt_num';
1696
1697 END get_receipt_num_lov;
1698
1699
1700 --
1701 -- bug 7197055
1702 -- Added new parameter, p_production_line to base item's lov on the prod line
1703 -- if a valule is entered for it.
1704 -- skolluku
1705 --
1706 PROCEDURE get_item_lov (org_id IN NUMBER, value IN VARCHAR2, p_production_line IN VARCHAR2 DEFAULT NULL,
1707 x_lov_sql OUT NOCOPY VARCHAR2) IS
1708
1709 BEGIN
1710
1711 x_lov_sql := 'SELECT concatenated_segments, description
1712 FROM mtl_system_items_kfv
1713 WHERE organization_id = :1';
1714 --
1715 -- bug 7197055
1716 -- Added the below condition only if a value for production_line is entered
1717 -- In such a case, the item becomes dependent on prod line.
1718 -- skolluku
1719 --
1720 IF (p_production_line IS NOT NULL OR p_production_line <> '') THEN
1721 x_lov_sql := x_lov_sql || ' AND inventory_item_id IN
1722 (SELECT primary_item_id
1723 FROM wip_rep_assy_val_v
1724 WHERE organization_id = :2
1725 AND line_id = :3
1726 UNION
1727 SELECT assembly_item_id
1728 FROM bom_operational_routings
1729 WHERE organization_id = :4
1730 AND line_id = :5)';
1731 END IF;
1732 x_lov_sql := x_lov_sql || ' ORDER BY concatenated_segments';
1733 END get_item_lov;
1734
1735 --
1736 -- bug 7197055
1737 -- New method to return the bind values for item.
1738 -- In addition to org id, prod line also needs to be returned in case
1739 -- production line is entered.
1740 -- skolluku
1741 --
1742 FUNCTION get_item_bind_values (p_org_id IN NUMBER,
1743 p_production_line IN VARCHAR2)
1744 RETURN VARCHAR2 IS
1745
1746 l_line_id NUMBER;
1747 BEGIN
1748 IF (p_production_line IS NOT NULL OR p_production_line <> '') THEN
1749 l_line_id := qa_plan_element_api.get_production_line_id(p_org_id,
1750 p_production_line);
1751
1752 RETURN to_char(p_org_id) || g_bind_value_list_seperator || to_char(p_org_id) ||
1753 g_bind_value_list_seperator || to_char(l_line_id) || g_bind_value_list_seperator ||
1754 to_char(p_org_id) || g_bind_value_list_seperator || to_char(l_line_id);
1755 ELSE
1756 RETURN to_char(p_org_id);
1757 END IF;
1758 END get_item_bind_values;
1759
1760 -- rkaza. 12/15/2003. bug 3280307. Added lov for comp item
1761 PROCEDURE get_comp_item_lov (plan_id IN NUMBER, x_org_id IN NUMBER,
1762 p_item IN VARCHAR2, value IN VARCHAR2,
1763 x_lov_sql OUT NOCOPY VARCHAR2) IS
1764
1765 -- l_item_id NUMBER DEFAULT NULL;
1766 BEGIN
1767
1768 IF (p_item is null) THEN
1769 fnd_message.set_name('QA', 'QA_SSQR_DEPENDENT_LOV');
1770 fnd_message.set_token('DEPELEM',
1771 qa_plan_element_api.get_prompt(plan_id, qa_ss_const.item));
1772 fnd_msg_pub.add();
1773 END IF;
1774
1775 IF (p_item is not null) THEN
1776
1777 -- l_item_id := qa_flex_util.get_item_id(x_org_id, p_item);
1778
1779 x_lov_sql := 'SELECT concatenated_segments, description
1780 FROM mtl_system_items_kfv
1781 WHERE organization_id = :1 ' ||
1782 ' and inventory_item_id in (
1783 SELECT bic.component_item_id
1784 FROM bom_inventory_components bic,
1785 bom_bill_of_materials bom
1786 WHERE bic.bill_sequence_id = bom.bill_sequence_id AND
1787 bic.effectivity_date <= sysdate AND
1788 nvl(bic.disable_date, sysdate+1) > sysdate AND
1789 bom.assembly_item_id = :2 AND
1790 bom.organization_id = :3)
1791 ORDER BY concatenated_segments';
1792 else
1793
1794 -- show an empty list if parent item is not passed
1795 x_lov_sql := 'SELECT concatenated_segments, description
1796 FROM mtl_system_items_kfv
1797 WHERE 1 = 2
1798 ORDER BY concatenated_segments';
1799
1800 end if;
1801
1802 END get_comp_item_lov;
1803
1804 FUNCTION get_comp_item_bind_values (p_org_id IN NUMBER,
1805 p_item IN VARCHAR2)
1806 RETURN VARCHAR2 IS
1807
1808 l_item_id NUMBER;
1809 BEGIN
1810
1811
1812 IF (p_item is null) THEN
1813 RETURN NULL;
1814 end if;
1815
1816 l_item_id := qa_flex_util.get_item_id(p_org_id, p_item);
1817 RETURN to_char(p_org_id) || g_bind_value_list_seperator ||
1818 to_char(l_item_id) || g_bind_value_list_seperator ||
1819 to_char(p_org_id);
1820
1821 END get_comp_item_bind_values;
1822
1823
1824 PROCEDURE get_asset_group_lov (x_org_id IN NUMBER, value IN VARCHAR2,
1825 x_lov_sql OUT NOCOPY VARCHAR2) IS
1826
1827 BEGIN
1828
1829 --dgupta: Start R12 EAM Integration. Bug 4345492
1830 x_lov_sql := 'select distinct msikfv.concatenated_segments, msikfv.description
1831 from mtl_system_items_b_kfv msikfv, mtl_parameters mp
1832 where msikfv.organization_id = mp.organization_id
1833 and msikfv.eam_item_type in (1,3)
1834 and mp.maint_organization_id = :1
1835 order by msikfv.concatenated_segments';
1836 --dgupta: End R12 EAM Integration. Bug 43454922
1837
1838
1839 END get_asset_group_lov;
1840
1841
1842 -- Bug 3228490 ksoh Fri Oct 31 11:38:21 PST 2003
1843 -- check if any dependent element value is null
1844 -- if so, put error message with element prompts
1845 -- requires plan_id to be passed in to retrieve element prompts.
1846 -- old signature calls new signature with plan_id = NULL to
1847 -- maintain old behavior
1848 --dgupta: Start R12 EAM Integration. Bug 4345492
1849 PROCEDURE get_asset_activity_lov (plan_id IN NUMBER, x_org_id IN NUMBER, p_asset_group IN VARCHAR2,
1850 p_asset_number IN VARCHAR2, p_asset_instance_number IN VARCHAR2, value IN VARCHAR2,
1851 x_lov_sql OUT NOCOPY VARCHAR2) IS
1852
1853 BEGIN
1854 -- rkaza. 12/02/2003. bug 3215372. Asset number can be null.
1855 -- Only Asset group is needed.
1856 -- Dependency on Asset Number is not a must. So removed it from the check.
1857 -- dgupta: Added that either asset group or asset instance number be present
1858 IF ((plan_id is not null) AND (p_asset_group is null)
1859 AND (p_asset_instance_number is null)) THEN
1860 fnd_message.set_name('QA', 'QA_SSQR_DEPENDENT_LOV');
1861 fnd_message.set_token('DEPELEM',
1862 qa_plan_element_api.get_prompt(plan_id, qa_ss_const.asset_group));
1863 fnd_msg_pub.add();
1864 END IF;
1865
1866 if (p_asset_number is null and p_asset_instance_number is null) then
1867 -- show all activities asssociated to the asset group
1868 -- If no match found or if asset group passed in is null, lov is empty
1869 /*
1870 x_lov_sql := 'SELECT meaav.activity, meaav.activity_description
1871 FROM mtl_eam_asset_activities_v meaav, mtl_system_items_b msib
1872 WHERE msib.organization_id = :1
1873 and meaav. maintenance_object_id = :2 --pass asset group inventory_item_id
1874 and (meaav.end_date_active is null or meaav.end_date_active > sysdate)
1875 and (meaav.start_date_active is null or meaav.start_date_active < sysdate)
1876 and msib.inventory_item_id = meaav. maintenance_object_id
1877 and meaav.maintenance_object_type = 2 --non serialized item
1878 ORDER BY meaav.activity';
1879 */
1880 -- Bug 5003511. SQL Repository Fix SQL ID: 15008957
1881 x_lov_sql := 'SELECT
1882 msib.concatenated_segments activity ,
1883 msib.description activity_description
1884 FROM mtl_eam_asset_activities meaav,
1885 mtl_system_items_b_kfv msib
1886 WHERE msib.organization_id = :1
1887 AND meaav.maintenance_object_id = :2 --pass asset group inventory_item_id
1888 AND (meaav.end_date_active is null
1889 OR meaav.end_date_active > sysdate)
1890 AND (meaav.start_date_active is null
1891 OR meaav.start_date_active < sysdate)
1892 AND msib.inventory_item_id = meaav.asset_activity_id
1893 AND meaav.maintenance_object_type = 2 --non serialized item
1894 ORDER BY msib.concatenated_segments';
1895 else
1896 -- show all activities associated to asset group and asset number
1897 -- if exact match not found, lov is empty.
1898 /*
1899 x_lov_sql := 'SELECT meaav.activity, meaav.activity_description
1900 FROM mtl_eam_asset_activities_v meaav, mtl_system_items_b msib
1901 WHERE msib.organization_id = :1
1902 and meaav.maintenance_object_id = :2 --pass asset instance_id
1903 and meaav.maintenance_object_type = 3 --serialized item
1904 and (meaav.end_date_active is null or meaav.end_date_active > sysdate)
1905 and (meaav.start_date_active is null or meaav.start_date_active < sysdate)
1906 and msib.inventory_item_id = meaav.inventory_item_id
1907 ORDER BY meaav.activity';
1908 */
1909 -- Bug 5003511. SQL Repository Fix SQL ID: 15008986
1910 x_lov_sql := 'SELECT
1911 msi.concatenated_segments activity ,
1912 msi.description activity_description
1913 FROM mtl_eam_asset_activities meaa,
1914 mtl_system_items_b_kfv msi
1915 WHERE msi.organization_id = :1
1916 AND meaa.maintenance_object_id = :2 --pass asset instance_id
1917 AND meaa.maintenance_object_type = 3 --serialized item
1918 AND (meaa.end_date_active is null
1919 OR meaa.end_date_active > sysdate)
1920 AND (meaa.start_date_active is null
1921 OR meaa.start_date_active < sysdate)
1922 AND msi.inventory_item_id = meaa.asset_activity_id
1923 ORDER BY msi.concatenated_segments';
1924
1925 end if;
1926
1927 END get_asset_activity_lov;
1928
1929
1930 FUNCTION get_asset_activity_bind_values (p_org_id IN NUMBER,
1931 p_asset_group IN VARCHAR2,
1932 p_asset_number IN VARCHAR2, p_asset_instance_number IN VARCHAR2)
1933 RETURN VARCHAR2 IS
1934
1935 l_asset_group_id NUMBER DEFAULT NULL;
1936 l_asset_instance_id NUMBER DEFAULT NULL;
1937
1938 BEGIN
1939 if (p_asset_number is null and p_asset_instance_number is null) then
1940 l_asset_group_id := qa_plan_element_api.get_asset_group_id(p_org_id, p_asset_group);
1941 RETURN to_char(p_org_id) || g_bind_value_list_seperator || to_char(l_asset_group_id);
1942 else
1943 l_asset_instance_id := qa_plan_element_api.get_asset_instance_id(p_asset_instance_number);
1944 if (l_asset_instance_id is null) then
1945 l_asset_instance_id := qa_plan_element_api.get_asset_instance_id(l_asset_group_id, p_asset_number);
1946 end if;
1947 RETURN to_char(p_org_id) || g_bind_value_list_seperator || to_char(l_asset_instance_id);
1948 end if;
1949 END get_asset_activity_bind_values;
1950
1951
1952 PROCEDURE get_asset_activity_lov (x_org_id IN NUMBER, p_asset_group IN VARCHAR2,
1953 p_asset_number IN VARCHAR2, p_asset_instance_number IN VARCHAR2, value IN VARCHAR2,
1954 x_lov_sql OUT NOCOPY VARCHAR2) IS
1955 BEGIN
1956 get_asset_activity_lov (NULL, x_org_id, p_asset_group, p_asset_number,
1957 p_asset_instance_number, value, x_lov_sql);
1958 END get_asset_activity_lov;
1959
1960 -- added the following to include new hardcoded element followup activity
1961 -- saugupta
1962 PROCEDURE get_followup_activity_lov (plan_id IN NUMBER, x_org_id IN NUMBER, p_asset_group IN VARCHAR2,
1963 p_asset_number IN VARCHAR2, p_asset_instance_number IN VARCHAR2, value IN VARCHAR2,
1964 x_lov_sql OUT NOCOPY VARCHAR2) IS
1965 BEGIN
1966 get_asset_activity_lov (plan_id, x_org_id, p_asset_group, p_asset_number,
1967 p_asset_instance_number, value, x_lov_sql); --no use duplicating code
1968
1969 END get_followup_activity_lov;
1970
1971 FUNCTION get_followup_act_bind_values (p_org_id IN NUMBER,
1972 p_asset_group IN VARCHAR2,
1973 p_asset_number IN VARCHAR2, p_asset_instance_number IN VARCHAR2)
1974 RETURN VARCHAR2 IS
1975
1976 BEGIN
1977 RETURN get_asset_activity_bind_values(p_org_id, p_asset_group,
1978 p_asset_number, p_asset_instance_number); -- same as asset activity lov, dont duplicate
1979 END get_followup_act_bind_values;
1980
1981 PROCEDURE get_followup_activity_lov (x_org_id IN NUMBER, p_asset_group IN VARCHAR2,
1982 p_asset_number IN VARCHAR2, p_asset_instance_number IN VARCHAR2, value IN VARCHAR2,
1983 x_lov_sql OUT NOCOPY VARCHAR2) IS
1984 BEGIN
1985 get_followup_activity_lov (NULL, x_org_id, p_asset_group,
1986 p_asset_number, p_asset_instance_number, value,
1987 x_lov_sql);
1988 END get_followup_activity_lov;
1989
1990 --dgupta: End R12 EAM Integration. Bug 4345492
1991
1992 PROCEDURE get_xfr_lpn_lov (value IN VARCHAR2, x_lov_sql OUT NOCOPY VARCHAR2) IS
1993 BEGIN
1994
1995 x_lov_sql := 'SELECT license_plate_number, attribute1
1996 FROM wms_license_plate_numbers
1997 ORDER BY license_plate_number';
1998
1999 END get_xfr_lpn_lov;
2000
2001
2002
2003
2004 -- Bug 3228490 ksoh Fri Oct 31 11:38:21 PST 2003
2005 -- check if any dependent element value is null
2006 -- if so, put error message with element prompts
2007 -- requires plan_id to be passed in to retrieve element prompts.
2008 -- old signature calls new signature with plan_id = NULL to
2009 -- maintain old behavior
2010
2011 -- anagarwa Thu May 13 14:56:49 PDT 2004
2012 -- Bug 3625998 Locator should be restricted by subinventory
2013 -- Earlier, we were taking in item and not using it. I have changed the
2014 -- variable name to x_subinventory and used it in the lov sql
2015 PROCEDURE get_locator_lov (plan_id IN NUMBER, org_id IN NUMBER,
2016 x_subinventory IN VARCHAR2, value IN VARCHAR2,
2017 x_lov_sql OUT NOCOPY VARCHAR2) IS
2018
2019 BEGIN
2020 IF ((plan_id is not null) AND (x_subinventory is null)) THEN
2021 fnd_message.set_name('QA', 'QA_SSQR_DEPENDENT_LOV');
2022 fnd_message.set_token('DEPELEM',
2023 qa_plan_element_api.get_prompt(plan_id, qa_ss_const.subinventory));
2024 fnd_msg_pub.add();
2025 END IF;
2026
2027 -- anagarwa Thu May 13 14:56:49 PDT 2004
2028 -- Bug 3625998: Added subinventory and disable_date to restrict the lov
2029 x_lov_sql := 'SELECT concatenated_segments, description
2030 FROM mtl_item_locations_kfv
2031 WHERE organization_id = :1
2032 AND subinventory_code = :2
2033 AND nvl(disable_date, sysdate+1) > sysdate
2034 ORDER BY concatenated_segments';
2035
2036 END get_locator_lov;
2037
2038
2039 FUNCTION get_locator_bind_values (p_org_id IN NUMBER,
2040 p_subinventory IN VARCHAR2)
2041 RETURN VARCHAR2 IS
2042
2043 BEGIN
2044
2045 RETURN to_char(p_org_id) || g_bind_value_list_seperator || p_subinventory;
2046
2047 END get_locator_bind_values;
2048
2049 PROCEDURE get_locator_lov (org_id IN NUMBER, x_item_name IN VARCHAR2,
2050 value IN VARCHAR2, x_lov_sql OUT NOCOPY VARCHAR2) IS
2051
2052 BEGIN
2053 get_locator_lov (NULL, org_id, x_item_name, value, x_lov_sql);
2054 END get_locator_lov;
2055
2056
2057 PROCEDURE get_party_lov (value IN VARCHAR2, x_lov_sql OUT NOCOPY VARCHAR2) IS
2058
2059 BEGIN
2060
2061 x_lov_sql := 'SELECT party_name, party_number
2062 FROM hz_parties
2063 WHERE status = ''A''
2064 AND party_type IN (''ORGANIZATION'',''PERSON'')
2065 ORDER BY party_name';
2066
2067 END get_party_lov;
2068
2069 --
2070 -- Implemented the following six get_lov procedures for
2071 -- Service_Item, Counter, Maintenance_Requirement, Service_Request, Rework_Job
2072 -- For ASO project
2073 -- rkunchal Thu Aug 1 12:04:56 PDT 2002
2074 --
2075
2076 PROCEDURE get_item_instance_lov (value IN VARCHAR2, x_lov_sql OUT NOCOPY VARCHAR2) IS
2077
2078 BEGIN
2079
2080 x_lov_sql := 'SELECT cii.instance_number, cii.serial_number
2081 FROM qa_csi_item_instances cii, mtl_system_items_kfv msik
2082 WHERE cii.inventory_item_id = msik.inventory_item_id
2083 AND cii.last_vld_organization_id = msik.organization_id
2084 ORDER BY 1';
2085
2086 END get_item_instance_lov;
2087
2088 --
2089 -- Bug 9032151
2090 -- Overloading above procedure and with the new one which takes
2091 -- care of the dependency of item instance on item.
2092 -- skolluku
2093 --
2094 PROCEDURE get_item_instance_lov (p_plan_id IN NUMBER, p_item IN VARCHAR2,
2095 value IN VARCHAR2, x_lov_sql OUT NOCOPY VARCHAR2) IS
2096
2097 BEGIN
2098 IF (p_item is null) THEN
2099 fnd_message.set_name('QA', 'QA_SSQR_DEPENDENT_LOV');
2100 fnd_message.set_token('DEPELEM',
2101 qa_plan_element_api.get_prompt(p_plan_id, qa_ss_const.item));
2102 fnd_msg_pub.add();
2103 END IF;
2104
2105 x_lov_sql := 'SELECT cii.instance_number, cii.serial_number
2106 FROM qa_csi_item_instances cii, mtl_system_items_kfv msik
2107 WHERE cii.inventory_item_id = msik.inventory_item_id
2108 AND cii.last_vld_organization_id = msik.organization_id
2109 AND cii.inventory_item_id = :1
2110 AND trunc(sysdate) BETWEEN trunc(nvl(cii.active_start_date, sysdate))
2111 AND trunc(nvl(cii.active_end_date, sysdate))
2112 ORDER BY cii.instance_number';
2113
2114 END get_item_instance_lov;
2115
2116 --
2117 -- Bug 8979498
2118 -- New function to fetch bind values for item instance lov
2119 -- skolluku
2120 --
2121 FUNCTION get_item_instance_bind_values(p_org_id IN NUMBER,
2122 p_item_name IN VARCHAR2)
2123 RETURN VARCHAR2 IS
2124
2125
2126 l_item_id NUMBER;
2127
2128 BEGIN
2129
2130 l_item_id := qa_flex_util.get_item_id(p_org_id, p_item_name);
2131
2132 IF l_item_id IS NULL THEN
2133 RETURN NULL;
2134 END IF;
2135
2136 RETURN to_char(l_item_id);
2137
2138 END get_item_instance_bind_values;
2139
2140 --
2141 -- Bug 9359442
2142 -- New procedure which returns lov for item instance serial based on item.
2143 -- skolluku
2144 --
2145 PROCEDURE get_item_instance_serial_lov (p_plan_id IN NUMBER, p_item IN VARCHAR2,
2146 value IN VARCHAR2, x_lov_sql OUT NOCOPY VARCHAR2) IS
2147
2148 BEGIN
2149 IF (p_item is null) THEN
2150 fnd_message.set_name('QA', 'QA_SSQR_DEPENDENT_LOV');
2151 fnd_message.set_token('DEPELEM',
2152 qa_plan_element_api.get_prompt(p_plan_id, qa_ss_const.item));
2153 fnd_msg_pub.add();
2154 END IF;
2155
2156 x_lov_sql := 'SELECT cii.serial_number, msik.concatenated_segments
2157 FROM qa_csi_item_instances cii, mtl_system_items_kfv msik
2158 WHERE cii.inventory_item_id = msik.inventory_item_id
2159 AND cii.inv_master_organization_id = msik.organization_id
2160 AND msik.inventory_item_id = :1
2161 AND trunc(sysdate) BETWEEN trunc(nvl(cii.active_start_date, sysdate))
2162 AND trunc(nvl(cii.active_end_date, sysdate))
2163 AND cii.serial_number IS NOT NULL
2164 ORDER BY cii.serial_number';
2165
2166 END get_item_instance_serial_lov;
2167
2168 --
2169 -- Bug 9359442
2170 -- New function to fetch bind values for item instance serial lov
2171 -- skolluku
2172 --
2173 FUNCTION get_item_inst_ser_bind_values(p_org_id IN NUMBER,
2174 p_item_name IN VARCHAR2)
2175 RETURN VARCHAR2 IS
2176
2177
2178 l_item_id NUMBER;
2179
2180 BEGIN
2181
2182 l_item_id := qa_flex_util.get_item_id(p_org_id, p_item_name);
2183
2184 IF l_item_id IS NULL THEN
2185 RETURN NULL;
2186 END IF;
2187
2188 RETURN to_char(l_item_id);
2189
2190 END get_item_inst_ser_bind_values;
2191
2192 PROCEDURE get_counter_name_lov (value IN VARCHAR2, x_lov_sql OUT NOCOPY VARCHAR2) IS
2193
2194 BEGIN
2195
2196 -- Bug 5003511. SQL Repository Fix SQL ID: 15009044
2197 x_lov_sql := 'SELECT name, description
2198 FROM csi_counters_vl
2199 WHERE trunc(sysdate) BETWEEN
2200 nvl(start_date_active, trunc(sysdate))
2201 AND nvl(end_date_active, trunc(sysdate))
2202 ORDER BY 1';
2203 /*
2204 x_lov_sql := 'SELECT cc.name, cc.description
2205 FROM cs_counters cc, cs_counter_groups ccg
2206 WHERE cc.counter_group_id = ccg.counter_group_id
2207 AND ccg.template_flag = ''N''
2208 ORDER BY 1';
2209 */
2210
2211 END get_counter_name_lov;
2212
2213
2214 PROCEDURE get_maintenance_req_lov (value IN VARCHAR2, x_lov_sql OUT NOCOPY VARCHAR2) IS
2215
2216 BEGIN
2217
2218 x_lov_sql := 'SELECT amr.title, amr.version_number
2219 FROM qa_ahl_mr amr
2220 WHERE trunc(sysdate) BETWEEN
2221 trunc(nvl(amr.effective_from, sysdate)) AND trunc(nvl(amr.effective_to, sysdate))
2222 ORDER BY 1';
2223
2224 END get_maintenance_req_lov;
2225
2226
2227 PROCEDURE get_service_request_lov (value IN VARCHAR2, x_lov_sql OUT NOCOPY VARCHAR2) IS
2228
2229 BEGIN
2230
2231 x_lov_sql := 'SELECT incident_number, summary
2232 FROM cs_incidents
2233 ORDER BY 1';
2234
2235 END get_service_request_lov;
2236
2237
2238 PROCEDURE get_rework_job_lov (org_id IN NUMBER, value IN VARCHAR2, x_lov_sql OUT NOCOPY VARCHAR2) IS
2239
2240 BEGIN
2241
2242 x_lov_sql:= 'SELECT wip_entity_name, description
2243 FROM wip_discrete_jobs_all_v
2244 WHERE organization_id = :1
2245 ORDER BY wip_entity_name';
2246
2247 END get_rework_job_lov;
2248
2249 PROCEDURE get_disposition_source_lov(value IN VARCHAR2, x_lov_sql OUT NOCOPY VARCHAR2) IS
2250
2251 BEGIN
2252
2253 x_lov_sql := 'SELECT v.short_code code,
2254 v.description
2255 FROM qa_char_value_lookups v
2256 WHERE v.char_id = :1
2257 ORDER BY 1';
2258
2259 END get_disposition_source_lov;
2260
2261 PROCEDURE get_disposition_action_lov(value IN VARCHAR2, x_lov_sql OUT NOCOPY VARCHAR2) IS
2262
2263 BEGIN
2264
2265 x_lov_sql := 'SELECT v.short_code code,
2266 v.description
2267 FROM qa_char_value_lookups v
2268 WHERE v.char_id = :1
2269 ORDER BY 1';
2270
2271 END get_disposition_action_lov;
2272
2273 PROCEDURE get_disposition_lov(value IN VARCHAR2, x_lov_sql OUT NOCOPY VARCHAR2) IS
2274
2275 BEGIN
2276
2277 x_lov_sql := 'SELECT v.short_code code,
2278 v.description
2279 FROM qa_char_value_lookups v
2280 WHERE v.char_id = :1
2281 ORDER BY 1';
2282
2283 END get_disposition_lov;
2284
2285 PROCEDURE get_disposition_status_lov(value IN VARCHAR2, x_lov_sql OUT NOCOPY VARCHAR2) IS
2286
2287 BEGIN
2288
2289 x_lov_sql := 'SELECT v.short_code code,
2290 v.description
2291 FROM qa_char_value_lookups v
2292 WHERE v.char_id = :1
2293 ORDER BY 1';
2294
2295 END get_disposition_status_lov;
2296
2297 /* R12 DR Integration. Bug 4345489 Strat */
2298 PROCEDURE get_repair_order_lov(value IN VARCHAR2, x_lov_sql OUT NOCOPY VARCHAR2) IS
2299
2300 BEGIN
2301
2302 x_lov_sql := 'SELECT cr.repair_number,
2303 cr.problem_description
2304 FROM csd_repairs cr
2305 WHERE status not in (''C'', ''H'')
2306 ORDER BY 1';
2307
2308 END get_repair_order_lov;
2309
2310 PROCEDURE get_jtf_task_lov(value IN VARCHAR2, x_lov_sql OUT NOCOPY VARCHAR2) IS
2311
2312 BEGIN
2313
2314 x_lov_sql := 'SELECT task_number, task_name
2315 FROM JTF_TASKS_VL
2316 ORDER BY 1';
2317
2318 END get_jtf_task_lov;
2319 /* R12 DR Integration. Bug 4345489 End */
2320
2321 -- R12 OPM Deviations. Bug 4345503 Start
2322
2323 PROCEDURE get_process_batch_num_lov
2324 (org_id IN NUMBER,
2325 value IN VARCHAR2,
2326 x_lov_sql OUT NOCOPY VARCHAR2) IS
2327 BEGIN
2328 x_lov_sql := 'SELECT BATCH_NO, BATCH_NO BATCH_DESC FROM GME_BATCH_HEADER '||
2329 'WHERE ORGANIZATION_ID is null or ORGANIZATION_ID = :1'||
2330 'ORDER BY BATCH_NO';
2331 END get_process_batch_num_lov;
2332
2333 PROCEDURE get_process_batchstep_num_lov
2334 (org_id IN NUMBER,
2335 plan_id IN NUMBER,
2336 process_batch_num IN VARCHAR2,
2337 value IN VARCHAR2,
2338 x_lov_sql OUT NOCOPY VARCHAR2) IS
2339
2340 l_process_batch_id number;
2341 BEGIN
2342 l_process_batch_id := qa_plan_element_api.get_process_batch_id (process_batch_num, org_id);
2343
2344 IF (l_process_batch_id is null) THEN
2345 fnd_message.set_name('QA', 'QA_SSQR_DEPENDENT_LOV');
2346 fnd_message.set_token('DEPELEM',
2347 qa_plan_element_api.get_prompt(plan_id, qa_ss_const.process_batch_num)
2348 );
2349 fnd_msg_pub.add();
2350 END IF;
2351
2352 x_lov_sql := 'SELECT to_char(STEPS.BATCHSTEP_NO) ,OPS.OPRN_DESC BATCHSTEP_DESC '||
2353 'FROM GME_BATCH_STEPS STEPS, GMD_OPERATIONS OPS '||
2354 'WHERE STEPS.BATCH_ID = :1 ' ||
2355 'AND STEPS.OPRN_ID = OPS.OPRN_ID '||
2356 'ORDER BY BATCHSTEP_NO';
2357
2358 END get_process_batchstep_num_lov;
2359
2360 FUNCTION GET_PROCESS_STEP_BIND_VALUE
2361 (org_id IN NUMBER,
2362 process_batch_num IN VARCHAR2)
2363 RETURN VARCHAR2 IS
2364
2365 l_process_batch_id number;
2366 BEGIN
2367 l_process_batch_id := qa_plan_element_api.get_process_batch_id (process_batch_num, org_id);
2368
2369 IF (l_process_batch_id IS NULL) THEN
2370 RETURN NULL;
2371 END IF;
2372
2373 RETURN to_char(l_process_batch_id);
2374 END GET_PROCESS_STEP_BIND_VALUE;
2375
2376 PROCEDURE get_process_operation_lov
2377 (org_id IN NUMBER,
2378 plan_id IN NUMBER,
2379 process_batch_num IN VARCHAR2,
2380 process_batchstep_num IN VARCHAR2,
2381 value IN VARCHAR2,
2382 x_lov_sql OUT NOCOPY VARCHAR2) IS
2383
2384 l_process_batch_id number;
2385 l_process_batchstep_id number;
2386 BEGIN
2387 L_PROCESS_BATCH_ID := QA_PLAN_ELEMENT_API.GET_PROCESS_BATCH_ID(PROCESS_BATCH_NUM,ORG_ID);
2388
2389 IF (l_process_batch_id is null) THEN
2390 fnd_message.set_name('QA', 'QA_SSQR_DEPENDENT_LOV');
2391 fnd_message.set_token('DEPELEM',
2392 qa_plan_element_api.get_prompt(plan_id, qa_ss_const.process_batch_num)
2393 );
2394 fnd_msg_pub.add();
2395 END IF;
2396
2397 L_PROCESS_BATCHSTEP_ID := QA_PLAN_ELEMENT_API.GET_PROCESS_BATCHSTEP_ID
2398 (process_batchstep_num,L_PROCESS_BATCH_ID);
2399
2400 IF (l_process_batchstep_id is null) THEN
2401 fnd_message.set_name('QA', 'QA_SSQR_DEPENDENT_LOV');
2402 fnd_message.set_token('DEPELEM',
2403 qa_plan_element_api.get_prompt(plan_id, qa_ss_const.process_batchstep_num)
2404 );
2405 fnd_msg_pub.add();
2406 END IF;
2407
2408 x_lov_sql := 'SELECT OPERATION PROCESS_OPERATION, OPRN_DESC '||
2409 'FROM GMO_BATCH_STEPS_V '||
2410 'WHERE BATCHSTEP_ID = :1 '||
2411 ' AND BATCH_ID = :2 '||
2412 ' ORDER BY PROCESS_OPERATION';
2413 END get_process_operation_lov;
2414
2415 FUNCTION GET_PROCESS_OPRN_BIND_VALUE
2416 (org_id IN NUMBER,
2417 process_batch_num IN VARCHAR2,
2418 process_batchstep_num IN VARCHAR2)
2419 RETURN VARCHAR2 IS
2420
2421 l_process_batch_id number;
2422 l_process_batchstep_id number;
2423 BEGIN
2424 l_process_batch_id := qa_plan_element_api.get_process_batch_id (process_batch_num, org_id);
2425 IF (l_process_batch_id IS NULL) THEN
2426 RETURN NULL;
2427 END IF;
2428
2429 l_process_batchstep_id := QA_PLAN_ELEMENT_API.GET_PROCESS_BATCHSTEP_ID
2430 (process_batchstep_num,L_PROCESS_BATCH_ID);
2431 IF (l_process_batchstep_id IS NULL) THEN
2432 RETURN NULL;
2433 END IF;
2434
2435 RETURN to_char(l_process_batchstep_id) ||
2436 g_bind_value_list_seperator ||
2437 to_char(l_process_batch_id);
2438
2439 END GET_PROCESS_OPRN_BIND_VALUE;
2440
2441 PROCEDURE get_process_activity_lov
2442 (org_id IN NUMBER,
2443 plan_id IN NUMBER,
2444 process_batch_num IN VARCHAR2,
2445 process_batchstep_num IN VARCHAR2,
2446 value IN VARCHAR2,
2447 x_lov_sql OUT NOCOPY VARCHAR2) IS
2448
2449 l_process_batch_id number;
2450 l_process_batchstep_id number;
2451 BEGIN
2452 L_PROCESS_BATCH_ID := QA_PLAN_ELEMENT_API.GET_PROCESS_BATCH_ID(PROCESS_BATCH_NUM,ORG_ID);
2453
2454 IF (l_process_batch_id is null) THEN
2455 fnd_message.set_name('QA', 'QA_SSQR_DEPENDENT_LOV');
2456 fnd_message.set_token('DEPELEM',
2457 qa_plan_element_api.get_prompt(plan_id, qa_ss_const.process_batch_num)
2458 );
2459 fnd_msg_pub.add();
2460 END IF;
2461
2462 L_PROCESS_BATCHSTEP_ID := QA_PLAN_ELEMENT_API.GET_PROCESS_BATCHSTEP_ID
2463 (PROCESS_BATCHSTEP_NUM,L_PROCESS_BATCH_ID);
2464
2465 IF (l_process_batchstep_id is null) THEN
2466 fnd_message.set_name('QA', 'QA_SSQR_DEPENDENT_LOV');
2467 fnd_message.set_token('DEPELEM',
2468 qa_plan_element_api.get_prompt(plan_id, qa_ss_const.process_batchstep_num)
2469 );
2470 fnd_msg_pub.add();
2471 END IF;
2472
2473 x_lov_sql := 'SELECT STEPS.ACTIVITY, ACTIVITIES.ACTIVITY_DESC '||
2474 'FROM GME_BATCH_STEP_ACTIVITIES STEPS, GMD_ACTIVITIES ACTIVITIES '||
2475 'WHERE STEPS.BATCHSTEP_ID = :1 '||
2476 ' AND STEPS.BATCH_ID = :2 '||
2477 ' AND STEPS.ACTIVITY = ACTIVITIES.ACTIVITY '||
2478 'ORDER BY ACTIVITY';
2479 END get_process_activity_lov;
2480
2481 FUNCTION GET_PROCESS_ACT_BIND_VALUE
2482 (org_id IN NUMBER,
2483 process_batch_num IN VARCHAR2,
2484 process_batchstep_num IN VARCHAR2)
2485 RETURN VARCHAR2 IS
2486
2487 l_process_batch_id number;
2488 l_process_batchstep_id number;
2489 BEGIN
2490 l_process_batch_id := qa_plan_element_api.get_process_batch_id (process_batch_num, org_id);
2491 IF (l_process_batch_id IS NULL) THEN
2492 RETURN NULL;
2493 END IF;
2494
2495 l_process_batchstep_id := QA_PLAN_ELEMENT_API.GET_PROCESS_BATCHSTEP_ID
2496 (process_batchstep_num,L_PROCESS_BATCH_ID);
2497 IF (l_process_batchstep_id IS NULL) THEN
2498 RETURN NULL;
2499 END IF;
2500
2501 RETURN to_char(l_process_batchstep_id) ||
2502 g_bind_value_list_seperator ||
2503 to_char(l_process_batch_id);
2504
2505 END GET_PROCESS_ACT_BIND_VALUE;
2506
2507 PROCEDURE get_process_resource_lov
2508 (org_id IN NUMBER,
2509 plan_id IN NUMBER,
2510 process_batch_num IN VARCHAR2,
2511 process_batchstep_num IN VARCHAR2,
2512 process_activity IN VARCHAR2,
2513 value IN VARCHAR2,
2514 x_lov_sql OUT NOCOPY VARCHAR2) IS
2515
2516 l_process_batch_id number;
2517 l_process_batchstep_id number;
2518 l_process_activity_id number;
2519 BEGIN
2520 L_PROCESS_BATCH_ID := QA_PLAN_ELEMENT_API.GET_PROCESS_BATCH_ID(PROCESS_BATCH_NUM,ORG_ID);
2521
2522 IF (l_process_batch_id is null) THEN
2523 fnd_message.set_name('QA', 'QA_SSQR_DEPENDENT_LOV');
2524 fnd_message.set_token('DEPELEM',
2525 qa_plan_element_api.get_prompt(plan_id, qa_ss_const.process_batch_num)
2526 );
2527 fnd_msg_pub.add();
2528 END IF;
2529
2530 L_PROCESS_BATCHSTEP_ID := QA_PLAN_ELEMENT_API.GET_PROCESS_BATCHSTEP_ID
2531 (PROCESS_BATCHSTEP_NUM,L_PROCESS_BATCH_ID);
2532
2533 IF (l_process_batchstep_id is null) THEN
2534 fnd_message.set_name('QA', 'QA_SSQR_DEPENDENT_LOV');
2535 fnd_message.set_token('DEPELEM',
2536 qa_plan_element_api.get_prompt(plan_id, qa_ss_const.process_batchstep_num)
2537 );
2538 fnd_msg_pub.add();
2539 END IF;
2540
2541 L_PROCESS_ACTIVITY_ID := QA_PLAN_ELEMENT_API.GET_PROCESS_ACTIVITY_ID
2542 (PROCESS_ACTIVITY,L_PROCESS_BATCH_ID,L_PROCESS_BATCHSTEP_ID);
2543
2544 IF (l_process_activity_id is null) THEN
2545 fnd_message.set_name('QA', 'QA_SSQR_DEPENDENT_LOV');
2546 fnd_message.set_token('DEPELEM',
2547 qa_plan_element_api.get_prompt(plan_id, qa_ss_const.process_activity)
2548 );
2549 fnd_msg_pub.add();
2550 END IF;
2551
2552 x_lov_sql := 'SELECT GBSR.RESOURCES, CRMV.RESOURCE_DESC '||
2553 'FROM GME_BATCH_STEP_RESOURCES GBSR, CR_RSRC_MST_VL CRMV '||
2554 'WHERE GBSR.BATCHSTEP_ACTIVITY_ID = :1 '||
2555 'AND GBSR.BATCHSTEP_ID = :2 '||
2556 'AND GBSR.BATCH_ID = :3 '||
2557 'AND GBSR.RESOURCES = CRMV.RESOURCES '||
2558 'ORDER BY RESOURCES';
2559
2560 END get_process_resource_lov;
2561
2562 FUNCTION GET_PROCESS_RSR_BIND_VALUE
2563 (org_id IN NUMBER,
2564 process_batch_num IN VARCHAR2,
2565 process_batchstep_num IN VARCHAR2,
2566 process_activity IN VARCHAR2)
2567 RETURN VARCHAR2 IS
2568
2569 l_process_batch_id number;
2570 l_process_batchstep_id number;
2571 l_process_activity_id number;
2572 BEGIN
2573 l_process_batch_id := qa_plan_element_api.get_process_batch_id (process_batch_num, org_id);
2574 IF (l_process_batch_id IS NULL) THEN
2575 RETURN NULL;
2576 END IF;
2577
2578 l_process_batchstep_id := QA_PLAN_ELEMENT_API.GET_PROCESS_BATCHSTEP_ID
2579 (process_batchstep_num,L_PROCESS_BATCH_ID);
2580 IF (l_process_batchstep_id IS NULL) THEN
2581 RETURN NULL;
2582 END IF;
2583
2584 L_PROCESS_ACTIVITY_ID := QA_PLAN_ELEMENT_API.GET_PROCESS_ACTIVITY_ID
2585 (PROCESS_ACTIVITY,L_PROCESS_BATCH_ID,L_PROCESS_BATCHSTEP_ID);
2586
2587 IF (l_process_activity_id IS NULL) THEN
2588 RETURN NULL;
2589 END IF;
2590
2591 RETURN to_char(l_process_activity_id) ||
2592 g_bind_value_list_seperator ||
2593 to_char(l_process_batchstep_id) ||
2594 g_bind_value_list_seperator ||
2595 to_char(l_process_batch_id);
2596
2597 END GET_PROCESS_RSR_BIND_VALUE;
2598
2599 PROCEDURE get_process_parameter_lov
2600 (org_id IN NUMBER,
2601 plan_id IN NUMBER,
2602 process_resource IN VARCHAR2,
2603 value IN VARCHAR2,
2604 x_lov_sql OUT NOCOPY VARCHAR2) IS
2605 BEGIN
2606 x_lov_sql := 'SELECT DISTINCT GP.PARAMETER_NAME, GP.PARAMETER_DESCRIPTION '||
2607 'FROM GMP_PROCESS_PARAMETERS GP, GME_PROCESS_PARAMETERS GE '||
2608 'WHERE GP.PARAMETER_ID = GE.PARAMETER_ID '||
2609 'AND GE.RESOURCES = :1'||
2610 ' ORDER BY PARAMETER_NAME';
2611
2612 END get_process_parameter_lov;
2613
2614 FUNCTION GET_PROCESS_PARAM_BIND_VALUE
2615 (org_id IN NUMBER,
2616 process_resource IN VARCHAR2)
2617 RETURN VARCHAR2 IS
2618
2619 BEGIN
2620 IF (process_resource IS NULL) THEN
2621 RETURN NULL;
2622 END IF;
2623
2624 RETURN to_char(process_resource);
2625 END GET_PROCESS_PARAM_BIND_VALUE;
2626 -- R12 OPM Deviations. Bug 4345503 End
2627
2628 --
2629 -- See Bug 2588213
2630 -- To support the element Maintenance Op Seq Number
2631 -- to be used along with Maintenance Workorder
2632 -- rkunchal Mon Sep 23 23:46:28 PDT 2002
2633 --
2634 -- Bug 3228490 ksoh Fri Oct 31 11:38:21 PST 2003
2635 -- check if any dependent element value is null
2636 -- if so, put error message with element prompts
2637 -- requires plan_id to be passed in to retrieve element prompts.
2638 -- old signature calls new signature with plan_id = NULL to
2639 -- maintain old behavior
2640 PROCEDURE get_maintenance_op_seq_lov(plan_id IN NUMBER, org_id IN NUMBER,
2641 value IN VARCHAR2,
2642 maintenance_work_order IN VARCHAR2,
2643 x_lov_sql OUT NOCOPY VARCHAR2) IS
2644
2645 x_wip_entity_id NUMBER DEFAULT NULL;
2646
2647 BEGIN
2648 IF ((plan_id is not null) AND (maintenance_work_order is null)) THEN
2649 fnd_message.set_name('QA', 'QA_SSQR_DEPENDENT_LOV');
2650 fnd_message.set_token('DEPELEM',
2651 qa_plan_element_api.get_prompt(plan_id, qa_ss_const.work_order));
2652 fnd_msg_pub.add();
2653 END IF;
2654 -- rkaza. 10/22/2003. 3209804.
2655 -- operation_seq_num should be made a varchar2 to be compatible with
2656 -- code and description in a dynamic lov.
2657 -- Also lov should not error out if wip_entity_id is null.
2658
2659 x_wip_entity_id := qa_plan_element_api.get_job_id(org_id, maintenance_work_order);
2660
2661 if x_wip_entity_id is not null then
2662 x_lov_sql := 'SELECT to_char(operation_seq_num), operation_code
2663 FROM wip_operations_all_v
2664 WHERE wip_entity_id = :1
2665 AND organization_id = :2
2666 ORDER BY operation_seq_num';
2667 else
2668 -- nothing should be selected.
2669 x_lov_sql := 'SELECT to_char(operation_seq_num), operation_code
2670 FROM wip_operations_all_v
2671 WHERE 1 = 2' || '
2672 ORDER BY operation_seq_num';
2673 end if;
2674
2675 END get_maintenance_op_seq_lov;
2676
2677
2678 FUNCTION get_maint_op_seq_bind_values (p_org_id IN NUMBER,
2679 p_maintenance_work_order IN VARCHAR2)
2680 RETURN VARCHAR2 IS
2681
2682 l_wip_entity_id NUMBER;
2683
2684 BEGIN
2685
2686 l_wip_entity_id := qa_plan_element_api.get_job_id(p_org_id, p_maintenance_work_order);
2687
2688 if l_wip_entity_id is not null then
2689 RETURN to_char(l_wip_entity_id) || g_bind_value_list_seperator || to_char(p_org_id);
2690 end if;
2691
2692 RETURN NULL;
2693
2694 END get_maint_op_seq_bind_values;
2695
2696 PROCEDURE get_maintenance_op_seq_lov(org_id IN NUMBER,
2697 value IN VARCHAR2,
2698 maintenance_work_order IN VARCHAR2,
2699 x_lov_sql OUT NOCOPY VARCHAR2) IS
2700 BEGIN
2701 get_maintenance_op_seq_lov (NULL, org_id,
2702 value, maintenance_work_order, x_lov_sql);
2703 END get_maintenance_op_seq_lov;
2704
2705 --
2706 -- End of inclusions for ASO project
2707 -- rkunchal Thu Aug 1 12:04:56 PDT 2002
2708 --
2709
2710
2711 -- Start of inclusions for NCM Hardcode Elements.
2712 -- suramasw Thu Oct 31 10:48:59 PST 2002.
2713 -- Bug 2449067.
2714
2715 PROCEDURE get_bill_reference_lov (org_id IN NUMBER, value IN VARCHAR2,
2716 x_lov_sql OUT NOCOPY VARCHAR2) IS
2717
2718 BEGIN
2719
2720 x_lov_sql := 'SELECT concatenated_segments, description
2721 FROM mtl_system_items_kfv
2722 WHERE organization_id = :1
2723 ORDER BY concatenated_segments';
2724
2725 END get_bill_reference_lov;
2726
2727 PROCEDURE get_routing_reference_lov (org_id IN NUMBER, value IN VARCHAR2,
2728 x_lov_sql OUT NOCOPY VARCHAR2) IS
2729
2730 BEGIN
2731
2732 x_lov_sql := 'SELECT concatenated_segments, description
2733 FROM mtl_system_items_kfv
2734 WHERE organization_id = :1
2735 ORDER BY concatenated_segments';
2736
2737 END get_routing_reference_lov;
2738
2739 PROCEDURE get_to_subinventory_lov (org_id IN NUMBER, value IN VARCHAR2,
2740 x_lov_sql OUT NOCOPY VARCHAR2) IS
2741
2742 BEGIN
2743
2744 x_lov_sql := 'SELECT secondary_inventory_name, description
2745 FROM mtl_secondary_inventories
2746 WHERE organization_id = :1
2747 AND nvl(disable_date, sysdate+1) > sysdate
2748 ORDER BY secondary_inventory_name';
2749
2750 END get_to_subinventory_lov;
2751
2752 -- Bug 3228490 ksoh Fri Oct 31 11:38:21 PST 2003
2753 -- check if any dependent element value is null
2754 -- if so, put error message with element prompts
2755 -- requires plan_id to be passed in to retrieve element prompts.
2756 -- old signature calls new signature with plan_id = NULL to
2757 -- maintain old behavior
2758
2759 -- anagarwa Thu May 13 14:56:49 PDT 2004
2760 -- Bug 3625998 Locator should be restricted by subinventory
2761 -- Earlier, we were taking in item and not using it. I have changed the
2762 -- variable name to x_subinventory and used it in the lov sql
2763 PROCEDURE get_to_locator_lov (plan_id IN NUMBER, org_id IN NUMBER,
2764 x_subinventory IN VARCHAR2, value IN VARCHAR2,
2765 x_lov_sql OUT NOCOPY VARCHAR2) IS
2766
2767 BEGIN
2768
2769 -- anagarwa Thu May 13 14:56:49 PDT 2004
2770 -- Bug 3625998 Locator should be restricted by subinventory
2771 -- We are doing exactly the same thing as in get_locator_lov.
2772 -- I do not see the reason to maintain 2 occurrences of same code.
2773 -- If we need to change the this lov for some reason, then we can simply
2774 -- comment out my changes and write new logic here.
2775 get_locator_lov(plan_id, org_id, x_subinventory, value, x_lov_sql);
2776 /*
2777 IF ((plan_id is not null) AND (x_item_name is null)) THEN
2778 fnd_message.set_name('QA', 'QA_SSQR_DEPENDENT_LOV');
2779 fnd_message.set_token('DEPELEM',
2780 qa_plan_element_api.get_prompt(plan_id, qa_ss_const.item));
2781 fnd_msg_pub.add();
2782 END IF;
2783
2784 x_lov_sql := 'SELECT concatenated_segments, description
2785 FROM mtl_item_locations_kfv
2786 WHERE organization_id = ' || org_id || '
2787 ORDER BY concatenated_segments';
2788 */
2789
2790 END get_to_locator_lov;
2791
2792 PROCEDURE get_to_locator_lov (org_id IN NUMBER, x_item_name IN VARCHAR2,
2793 value IN VARCHAR2, x_lov_sql OUT NOCOPY VARCHAR2) IS
2794 BEGIN
2795 get_to_locator_lov (NULL, org_id, x_item_name, value, x_lov_sql);
2796 END get_to_locator_lov;
2797
2798 PROCEDURE get_lot_status_lov (value IN VARCHAR2, x_lov_sql OUT NOCOPY VARCHAR2) IS
2799
2800 BEGIN
2801
2802 x_lov_sql := 'SELECT status_code,description
2803 FROM mtl_material_statuses
2804 ORDER BY status_code';
2805
2806 END get_lot_status_lov;
2807
2808 -- Bug 7588754.pdube Wed Apr 15 07:37:25 PDT 2009
2809 -- Added parameters item_name and srl_num
2810 -- PROCEDURE get_serial_status_lov (value IN VARCHAR2, x_lov_sql OUT NOCOPY VARCHAR2) IS
2811 PROCEDURE get_serial_status_lov (value IN VARCHAR2,
2812 item_name IN VARCHAR2,
2813 serial_num IN VARCHAR2,
2814 x_lov_sql OUT NOCOPY VARCHAR2) IS
2815 BEGIN
2816
2817 -- Bug 7588754
2818 -- Changed the Lov SQL based on srl number and item
2819 x_lov_sql := 'SELECT mms.status_code, mms.description
2820 FROM mtl_serial_numbers msn, mtl_material_statuses mms
2821 WHERE msn.inventory_item_id = :1
2822 AND msn.serial_number like :2
2823 AND msn.status_id = mms.status_id
2824 AND mms.enabled_flag = 1';
2825
2826 END get_serial_status_lov;
2827
2828 -- Bug 7588754. New function to return the proper bind values
2829 -- pdube Wed Apr 15 07:37:25 PDT 2009
2830 FUNCTION get_serial_status_bind_values (p_org_id IN NUMBER,
2831 p_item_name IN VARCHAR2,
2832 p_serial_num IN VARCHAR2)
2833 RETURN VARCHAR2 IS
2834
2835 l_item_id NUMBER;
2836
2837 BEGIN
2838
2839 l_item_id := qa_flex_util.get_item_id(p_org_id, p_item_name);
2840
2841 IF (l_item_id IS NULL) THEN
2842 RETURN NULL;
2843 END IF;
2844
2845 RETURN to_char(l_item_id) || g_bind_value_list_seperator || p_serial_num ;
2846
2847 END get_serial_status_bind_values;
2848
2849 PROCEDURE get_nonconformance_source_lov(value IN VARCHAR2, x_lov_sql OUT NOCOPY VARCHAR2)
2850 IS
2851
2852 BEGIN
2853
2854 x_lov_sql := 'SELECT v.short_code code,
2855 v.description
2856 FROM qa_char_value_lookups v
2857 WHERE v.char_id = :1
2858 ORDER BY 1';
2859
2860 END get_nonconformance_source_lov;
2861
2862 PROCEDURE get_nonconform_severity_lov(value IN VARCHAR2, x_lov_sql OUT NOCOPY VARCHAR2)
2863 IS
2864
2865 BEGIN
2866
2867 x_lov_sql := 'SELECT v.short_code code,
2868 v.description
2869 FROM qa_char_value_lookups v
2870 WHERE v.char_id = :1
2871 ORDER BY 1';
2872
2873 END get_nonconform_severity_lov;
2874
2875 PROCEDURE get_nonconform_priority_lov(value IN VARCHAR2, x_lov_sql OUT NOCOPY VARCHAR2)
2876 IS
2877
2878 BEGIN
2879
2880 x_lov_sql := 'SELECT v.short_code code,
2881 v.description
2882 FROM qa_char_value_lookups v
2883 WHERE v.char_id = :1
2884 ORDER BY 1';
2885
2886 END get_nonconform_priority_lov;
2887
2888 PROCEDURE get_nonconformance_type_lov(value IN VARCHAR2, x_lov_sql OUT NOCOPY VARCHAR2)
2889 IS
2890
2891 BEGIN
2892
2893 x_lov_sql := 'SELECT v.short_code code,
2894 v.description
2895 FROM qa_char_value_lookups v
2896 WHERE v.char_id = :1
2897 ORDER BY 1';
2898
2899 END get_nonconformance_type_lov;
2900
2901
2902 PROCEDURE get_nonconformance_status_lov(value IN VARCHAR2, x_lov_sql OUT NOCOPY VARCHAR2)
2903 IS
2904
2905 BEGIN
2906
2907 x_lov_sql := 'SELECT v.short_code code,
2908 v.description
2909 FROM qa_char_value_lookups v
2910 WHERE v.char_id = :1
2911 ORDER BY 1';
2912
2913 END get_nonconformance_status_lov;
2914
2915 --anagarwa Wed Jan 15 13:51:41 PST 2003
2916 -- Bug 2751198
2917 -- support needed for contract number, contract line number and
2918 -- deliverable number
2919
2920 PROCEDURE get_contract_lov (value IN VARCHAR2,
2921 x_lov_sql OUT NOCOPY VARCHAR2) IS
2922
2923 BEGIN
2924
2925 x_lov_sql := 'SELECT k_number, short_description
2926 FROM oke_k_headers_lov_v order by k_number';
2927
2928 END get_contract_lov;
2929
2930
2931 PROCEDURE get_contract_line_lov (value IN VARCHAR2,
2932 x_lov_sql OUT NOCOPY VARCHAR2) IS
2933
2934 BEGIN
2935
2936 x_lov_sql := 'SELECT line_number, line_description
2937 FROM oke_k_lines_full_v order by line_number';
2938
2939 END get_contract_line_lov;
2940
2941 PROCEDURE get_deliverable_lov(value IN VARCHAR2,
2942 x_lov_sql OUT NOCOPY VARCHAR2) IS
2943
2944 BEGIN
2945
2946 x_lov_sql := 'SELECT deliverable_num, description
2947 FROM oke_k_deliverables_vl order by deliverable_num' ;
2948
2949 END get_deliverable_lov;
2950
2951
2952 -- End of inclusions for NCM Hardcode Elements.
2953
2954 --anagarwa Fri Nov 15 13:03:35 PST 2002
2955 --Following added for new CAR lov's
2956
2957 PROCEDURE get_request_source_lov(value IN VARCHAR2, x_lov_sql OUT NOCOPY VARCHAR2)
2958 IS
2959
2960 BEGIN
2961
2962 x_lov_sql := 'SELECT v.short_code code,
2963 v.description
2964 FROM qa_char_value_lookups v
2965 WHERE v.char_id = :1
2966 ORDER BY 1';
2967
2968 END get_request_source_lov;
2969
2970 PROCEDURE get_request_priority_lov(value IN VARCHAR2, x_lov_sql OUT NOCOPY VARCHAR2)
2971 IS
2972
2973 BEGIN
2974
2975 x_lov_sql := 'SELECT v.short_code code,
2976 v.description
2977 FROM qa_char_value_lookups v
2978 WHERE v.char_id = :1
2979 ORDER BY 1';
2980
2981 END get_request_priority_lov;
2982
2983 PROCEDURE get_request_severity_lov(value IN VARCHAR2, x_lov_sql OUT NOCOPY VARCHAR2)
2984 IS
2985
2986 BEGIN
2987
2988 x_lov_sql := 'SELECT v.short_code code,
2989 v.description
2990 FROM qa_char_value_lookups v
2991 WHERE v.char_id = :1
2992 ORDER BY 1';
2993
2994 END get_request_severity_lov;
2995
2996
2997 PROCEDURE get_request_status_lov(value IN VARCHAR2, x_lov_sql OUT NOCOPY VARCHAR2)
2998 IS
2999
3000 BEGIN
3001
3002 x_lov_sql := 'SELECT v.short_code code,
3003 v.description
3004 FROM qa_char_value_lookups v
3005 WHERE v.char_id = :1
3006 ORDER BY 1';
3007
3008 END get_request_status_lov;
3009
3010 -- End of inclusions for CAR Hardcode Elements.
3011
3012
3013 --
3014 -- Removed the DEFAULT clause to make the code GSCC compliant
3015 -- List of changed arguments.
3016 -- Old
3017 -- user_id IN NUMBER DEFAULT NULL
3018 -- New
3019 -- user_id IN NUMBER
3020 --
3021
3022
3023 PROCEDURE get_plan_element_lov(plan_id IN NUMBER, char_id IN NUMBER,
3024 org_id IN NUMBER, user_id IN NUMBER,
3025 x_lov_sql OUT NOCOPY VARCHAR2) IS
3026
3027 BEGIN
3028
3029 -- The function sql_string_exists simple checks to see
3030 -- if the user defined element should have a LOV
3031 -- associated with it or not. If it should then it returns
3032 -- true and populates sql_string - an out parameter.
3033
3034 IF sql_string_exists(plan_id, char_id, org_id, user_id, x_lov_sql) THEN
3035 RETURN;
3036 END IF;
3037
3038 END get_plan_element_lov;
3039
3040 --
3041 -- Added to the IF-ELSIF ladder for newly added collection elements
3042 -- for ASO project. New entries are appended after Party_Name
3043 -- rkunchal Thu Aug 1 12:27:48 PDT 2002
3044 --
3045
3046 FUNCTION get_lov_sql (
3047 plan_id IN NUMBER,
3048 char_id IN NUMBER,
3049 org_id IN NUMBER,
3050 user_id IN NUMBER,
3051 depen1 IN VARCHAR2,
3052 depen2 IN VARCHAR2,
3053 depen3 IN VARCHAR2,
3054 value IN VARCHAR2) RETURN VARCHAR2 IS
3055
3056 l_lov_sql VARCHAR2(1500);
3057
3058 BEGIN
3059 -- Bug 3228490 ksoh Fri Oct 31 11:38:21 PST 2003
3060 -- Now that we utilize fnd_msg_pub to show error messages
3061 -- we should clear stack so that errors are not shown over and over again.
3062 fnd_msg_pub.Initialize();
3063 fnd_msg_pub.reset();
3064
3065 IF (char_id = qa_ss_const.department) THEN
3066 get_department_lov(org_id, value, l_lov_sql);
3067
3068 ELSIF (char_id = qa_ss_const.job_name) THEN
3069 get_job_lov(org_id, value, l_lov_sql);
3070
3071 ELSIF (char_id = qa_ss_const.work_order) THEN
3072 get_work_order_lov(org_id, value, l_lov_sql);
3073
3074 ELSIF (char_id = qa_ss_const.production_line) THEN
3075 get_production_lov(org_id, value, l_lov_sql);
3076
3077 ELSIF (char_id = qa_ss_const.resource_code) THEN
3078 get_resource_code_lov(org_id, value, l_lov_sql);
3079
3080 ELSIF (char_id = qa_ss_const.vendor_name) THEN
3081 get_supplier_lov(value, l_lov_sql);
3082
3083 ELSIF (char_id = qa_ss_const.po_number) THEN
3084 get_po_number_lov(value, l_lov_sql);
3085
3086 ELSIF (char_id = qa_ss_const.customer_name) THEN
3087 get_customer_lov(value, l_lov_sql);
3088
3089 ELSIF (char_id = qa_ss_const.sales_order) THEN
3090 get_so_number_lov(value, l_lov_sql);
3091
3092 ELSIF (char_id = qa_ss_const.order_line) THEN
3093 -- Bug 7716875.Added sales Order as dependent.The depen1 here
3094 -- will be passed as SO Number.pdube Mon Apr 13 03:25:19 PDT 2009
3095 -- get_so_line_number_lov(value, l_lov_sql);
3096 get_so_line_number_lov(plan_id, depen1, value, l_lov_sql);
3097
3098 ELSIF (char_id = qa_ss_const.po_release_num) THEN
3099 --
3100 -- Bug 5003511. R12 Performance bug. SQLID: 15008630
3101 -- Release number is dependent on PO Number.
3102 -- Call new overloaded method.
3103 -- srhariha. Wed Feb 8 02:10:26 PST 2006.
3104 --
3105 -- Bug 9817478. Using new procedure which takes
3106 -- PO Number and PO Line Number value to compute PO Release Num.
3107 -- get_po_release_number_lov(plan_id,depen1,value, l_lov_sql);
3108 -- skolluku.
3109 get_po_release_number_lov(plan_id, depen1, depen2, value, l_lov_sql);
3110
3111
3112 ELSIF (char_id = qa_ss_const.project_number) THEN
3113 get_project_number_lov(value, l_lov_sql);
3114
3115 ELSIF (char_id = qa_ss_const.task_number) THEN
3116 -- anagarwa Thu Jan 29 15:04:26 PST 2004
3117 -- Bug 3404863 : task lov should be dependent upon project so now we
3118 -- pass project number as parent element value.
3119 get_task_number_lov(plan_id, depen1, value, l_lov_sql);
3120
3121 ELSIF (char_id = qa_ss_const.rma_number) THEN
3122 get_rma_number_lov(value, l_lov_sql);
3123
3124 ELSIF (char_id = qa_ss_const.uom) THEN
3125 -- get_uom_lov(org_id, item_name, value, l_lov_sql);
3126 get_uom_lov(plan_id, org_id, depen1, value, l_lov_sql);
3127
3128 -- anagarwa Mon Feb 24 17:08:57 PST 2003
3129 -- Bug 2808693
3130 -- adding support for comp_revision
3131 ELSIF (char_id = qa_ss_const.revision OR
3132 char_id = qa_ss_const.comp_revision) THEN
3133 -- get_revision_lov(org_id, item_name, value, l_lov_sql);
3134 get_revision_lov(plan_id, org_id, depen1, value, l_lov_sql);
3135
3136 ELSIF (char_id = qa_ss_const.subinventory) THEN
3137 get_subinventory_lov(org_id, value, l_lov_sql);
3138
3139 -- anagarwa Thu Aug 12 15:49:51 PDT 2004
3140 -- bug 3830258 incorrect LOVs in QWB
3141 -- synced up the lot and serial number lov with forms
3142 ELSIF (char_id = qa_ss_const.lot_number) THEN
3143 -- get_lot_number_lov(transaction_id, value, l_lov_sql);
3144 -- get_lot_number_lov(depen3, value, l_lov_sql);
3145 get_lot_number_lov(plan_id, org_id, depen1, value, l_lov_sql);
3146
3147 ELSIF (char_id = qa_ss_const.serial_number) THEN
3148 -- get_serial_number_lov(transaction_id, lot_number, value, l_lov_sql);
3149 -- get_serial_number_lov(plan_id, depen3, depen1, value, l_lov_sql);
3150 get_serial_number_lov(plan_id, org_id, depen2, depen3, depen1, value, l_lov_sql);
3151
3152 -- dgupta: Start R12 EAM Integration. Bug 4345492
3153 ELSIF (char_id = qa_ss_const.asset_instance_number) THEN
3154 get_asset_instance_number_lov(plan_id, org_id, depen1,depen2,value, l_lov_sql);
3155 --dgupta: End R12 EAM Integration. Bug 4345492
3156
3157 ELSIF (char_id = qa_ss_const.asset_number) THEN
3158 -- get_asset_number_lov(org_id, depen1, value, l_lov_sql);
3159 get_asset_number_lov(plan_id, org_id, depen1, value, l_lov_sql);
3160
3161 ELSIF (char_id = qa_ss_const.from_op_seq_num) OR
3162 (char_id = qa_ss_const.to_op_seq_num) THEN
3163 -- get_op_seq_number_lov(org_id, value, job_name, production_line,
3164 -- l_lov_sql);
3165 get_op_seq_number_lov(plan_id, org_id, value, depen1, depen2, l_lov_sql);
3166
3167 ELSIF (char_id = qa_ss_const.po_line_num) THEN
3168 -- get_po_line_number_lov(po_number, value, l_lov_sql);
3169 get_po_line_number_lov(plan_id, depen1, value, l_lov_sql);
3170
3171 ELSIF (char_id = qa_ss_const.po_shipment_num) THEN
3172 -- Bug 9817478. If PO Release Num is present call the new proc.
3173 -- Else use the existing one.
3174 IF (depen3 IS NOT NULL) THEN
3175 get_po_shipments_lov(plan_id, depen1, depen2, depen3, value, l_lov_sql);
3176 ELSE
3177 -- get_po_shipments_lov(po_line_number, po_number, value, l_lov_sql);
3178 get_po_shipments_lov(plan_id, depen1, depen2, value, l_lov_sql);
3179 END IF;
3180 ELSIF (char_id = qa_ss_const.receipt_num) THEN
3181 get_receipt_num_lov(value, l_lov_sql);
3182
3183 ELSIF (char_id = qa_ss_const.item) THEN
3184 --
3185 -- bug 7197055
3186 -- Added depen1 (prod line) for item.
3187 -- skolluku
3188 --
3189 get_item_lov(org_id, value, depen1, l_lov_sql);
3190
3191 -- rkaza. 12/15/2003. bug 3280307. Added lov for comp item
3192 ELSIF (char_id = qa_ss_const.comp_item) THEN
3193 get_comp_item_lov(plan_id, org_id, depen1, value, l_lov_sql);
3194
3195 ELSIF (char_id = qa_ss_const.asset_group) THEN
3196 get_asset_group_lov(org_id, value, l_lov_sql);
3197
3198 ELSIF (char_id = qa_ss_const.asset_activity) THEN
3199 -- get_asset_activity_lov(org_id, depen1, depen2, value, l_lov_sql);
3200 get_asset_activity_lov(plan_id, org_id, depen1, depen2, value, l_lov_sql);
3201
3202 -- added the following to include new hardcoded element followup activity
3203 -- saugupta
3204
3205 ELSIF (char_id = qa_ss_const.followup_activity) THEN
3206 -- get_followup_activity_lov(org_id, depen1, depen2, value, l_lov_sql);
3207 get_followup_activity_lov(plan_id, org_id, depen1, depen2, value, l_lov_sql);
3208
3209 ELSIF (char_id = qa_ss_const.xfr_license_plate_number) THEN
3210 get_xfr_lpn_lov(value, l_lov_sql);
3211
3212 ELSIF (char_id = qa_ss_const.locator) THEN
3213 -- get_locator_lov(org_id, depen1, value, l_lov_sql);
3214 get_locator_lov(plan_id, org_id, depen1, value, l_lov_sql);
3215
3216 ELSIF (char_id = qa_ss_const.party_name) THEN
3217 get_party_lov(value, l_lov_sql);
3218
3219 ELSIF (char_id = qa_ss_const.item_instance) THEN
3220 --
3221 -- Bug 9032151
3222 -- Modified the below procedure to include the value
3223 -- for item on which item instance is dependent.
3224 -- skolluku
3225 --
3226 get_item_instance_lov(plan_id, depen1, value, l_lov_sql);
3227
3228 --
3229 -- Bug 9359442
3230 -- Added lov for item instance serial based on item.
3231 -- skolluku
3232 --
3233 ELSIF (char_id = qa_ss_const.item_instance_serial) THEN
3234 get_item_instance_serial_lov(plan_id, depen1, value, l_lov_sql);
3235
3236 ELSIF (char_id = qa_ss_const.service_request) THEN
3237 get_service_request_lov(value, l_lov_sql);
3238
3239 ELSIF (char_id = qa_ss_const.maintenance_requirement) THEN
3240 get_maintenance_req_lov(value, l_lov_sql);
3241
3242 ELSIF (char_id = qa_ss_const.rework_job) THEN
3243 get_rework_job_lov(org_id, value, l_lov_sql);
3244
3245 ELSIF (char_id = qa_ss_const.counter_name) THEN
3246 get_counter_name_lov(value, l_lov_sql);
3247
3248 -- anagarwa Mon Feb 2 16:27:56 PST 2004
3249 -- Bug 3415693
3250 -- Disposition, disposition source, disposition actions and
3251 -- disposition status can all be processed using
3252 -- get_plan_element_lov and hence there is no need for the following code.
3253 -- Infact, the inclusion of the following code will cause incorrect lov
3254 -- to appear on selfservice
3255 -- For maintenance sake we should remove corresponding get lov functions
3256 -- too as they serve no purpose other than making this file bulkier.
3257 /*
3258 ELSIF (char_id = qa_ss_const.disposition_source) THEN
3259 get_disposition_source_lov(value, l_lov_sql);
3260
3261 ELSIF (char_id = qa_ss_const.disposition_action) THEN
3262 get_disposition_action_lov(value, l_lov_sql);
3263
3264 ELSIF (char_id = qa_ss_const.disposition) THEN
3265 get_disposition_lov(value, l_lov_sql);
3266
3267 ELSIF (char_id = qa_ss_const.disposition_status) THEN
3268 get_disposition_status_lov(value, l_lov_sql);
3269
3270 */
3271 --
3272 -- See Bug 2588213
3273 -- To support the element Maintenance Op Seq Number
3274 -- to be used along with Maintenance Workorder
3275 -- rkunchal Mon Sep 23 23:46:28 PDT 2002
3276 --
3277 ELSIF (char_id = qa_ss_const.maintenance_op_seq) THEN
3278 -- get_maintenance_op_seq_lov(org_id, value, depen1, l_lov_sql);
3279 get_maintenance_op_seq_lov(plan_id, org_id, value, depen1, l_lov_sql);
3280 --
3281 -- End of inclusions for Bug 2588213
3282 --
3283
3284 -- Start of inclusions for NCM Hardcode Elements.
3285 -- suramasw Thu Oct 31 10:48:59 PST 2002.
3286 -- Bug 2449067.
3287
3288 ELSIF (char_id = qa_ss_const.bill_reference) THEN
3289 get_bill_reference_lov(org_id, value, l_lov_sql);
3290
3291 ELSIF (char_id = qa_ss_const.routing_reference) THEN
3292 get_routing_reference_lov(org_id, value, l_lov_sql);
3293
3294 ELSIF (char_id = qa_ss_const.to_subinventory) THEN
3295 get_to_subinventory_lov(org_id, value, l_lov_sql);
3296
3297 ELSIF (char_id = qa_ss_const.to_locator) THEN
3298 -- get_to_locator_lov(org_id, depen1, value, l_lov_sql);
3299 get_to_locator_lov(plan_id, org_id, depen1, value, l_lov_sql);
3300
3301 ELSIF (char_id = qa_ss_const.lot_status) THEN
3302 get_lot_status_lov(value, l_lov_sql);
3303
3304 ELSIF (char_id = qa_ss_const.serial_status) THEN
3305 -- Bug 7588754. Added additional bind parameters
3306 -- pdube Wed Apr 15 07:37:25 PDT 2009
3307 -- get_serial_status_lov(value, l_lov_sql);
3308 get_serial_status_lov(value,depen1,depen2, l_lov_sql);
3309
3310 ELSIF (char_id = qa_ss_const.nonconformance_source) THEN
3311 get_nonconformance_source_lov(value, l_lov_sql);
3312
3313 ELSIF (char_id = qa_ss_const.nonconform_severity) THEN
3314 get_nonconform_severity_lov(value, l_lov_sql);
3315
3316 ELSIF (char_id = qa_ss_const.nonconform_priority) THEN
3317 get_nonconform_priority_lov(value, l_lov_sql);
3318
3319 ELSIF (char_id = qa_ss_const.nonconformance_type) THEN
3320 get_nonconformance_type_lov(value, l_lov_sql);
3321
3322 ELSIF (char_id = qa_ss_const.nonconformance_status) THEN
3323 get_nonconformance_status_lov(value, l_lov_sql);
3324
3325 --anagarwa Wed Jan 15 13:51:41 PST 2003
3326 -- Bug 2751198
3327 -- support needed for contract number, contract line number and
3328 -- deliverable number
3329
3330 ELSIF (char_id = qa_ss_const.contract_number) THEN
3331 get_contract_lov(value, l_lov_sql);
3332
3333 ELSIF (char_id = qa_ss_const.contract_line_number) THEN
3334 get_contract_line_lov(value, l_lov_sql);
3335
3336 ELSIF (char_id = qa_ss_const.deliverable_number) THEN
3337 get_deliverable_lov(value, l_lov_sql);
3338
3339 -- End of inclusions for NCM Hardcode Elements.
3340
3341 --anagarwa Fri Nov 15 13:03:35 PST 2002
3342 --Following added for new CAR lov's
3343
3344 ELSIF (char_id = qa_ss_const.request_source) THEN
3345 get_request_source_lov(value, l_lov_sql);
3346
3347 ELSIF (char_id = qa_ss_const.request_priority) THEN
3348 get_request_priority_lov(value, l_lov_sql);
3349
3350 ELSIF (char_id = qa_ss_const.request_severity) THEN
3351 get_request_severity_lov(value, l_lov_sql);
3352
3353 ELSIF (char_id = qa_ss_const.request_status) THEN
3354 get_request_status_lov(value, l_lov_sql);
3355
3356 -- anagarwa Thu Aug 12 15:49:51 PDT 2004
3357 -- bug 3830258 incorrect LOVs in QWB
3358 -- synced up the component lot number and component serial number
3359 -- lov with forms
3360 ELSIF (char_id = qa_ss_const.comp_lot_number) THEN
3361 get_comp_lot_number_lov(plan_id, org_id, depen1, value, l_lov_sql);
3362
3363 ELSIF (char_id = qa_ss_const.comp_serial_number) THEN
3364 get_comp_serial_number_lov(plan_id, org_id, depen2, depen3, depen1, value, l_lov_sql);
3365
3366 -- End of inclusions for CAR Hardcode Elements.
3367 /* R12 DR Integration. Bug 4345489 Start */
3368 ELSIF (char_id = qa_ss_const.repair_order_number) THEN
3369 get_repair_order_lov(value, l_lov_sql);
3370
3371 ELSIF (char_id = qa_ss_const.jtf_task_number) THEN
3372 get_jtf_task_lov(value, l_lov_sql);
3373 /* R12 DR Integration. Bug 4345489 End */
3374
3375 -- R12 OPM Deviations. Bug 4345503 Start
3376 ELSIF (char_id = qa_ss_const.process_batch_num) THEN
3377 get_process_batch_num_lov(org_id,value,l_lov_sql);
3378
3379 ELSIF (char_id = qa_ss_const.process_batchstep_num) THEN
3380 get_process_batchstep_num_lov
3381 (org_id,plan_id,depen1,value, l_lov_sql);
3382
3383 ELSIF (char_id = qa_ss_const.process_operation) THEN
3384 get_process_operation_lov
3385 (org_id,plan_id,depen1, depen2,value, l_lov_sql);
3386
3387 ELSIF (char_id = qa_ss_const.process_activity) THEN
3388 get_process_activity_lov
3389 (org_id,plan_id,depen1, depen2,value, l_lov_sql);
3390
3391 ELSIF (char_id = qa_ss_const.process_resource) THEN
3392 get_process_resource_lov
3393 (org_id,plan_id,depen1, depen2, depen3, value, l_lov_sql);
3394
3395 ELSIF (char_id = qa_ss_const.process_parameter) THEN
3396 get_process_parameter_lov
3397 (org_id,plan_id,depen1, value, l_lov_sql);
3398
3399 -- R12 OPM Deviations. Bug 4345503 End
3400
3401 --
3402 -- Bug 6161802
3403 -- Obtain rma line number lov with rma number as a bind variable.
3404 -- skolluku Mon Jul 16 22:08:16 PDT 2007
3405 --
3406 ELSIF (char_id = qa_ss_const.rma_line_num) THEN
3407 get_rma_line_num_lov(plan_id, depen1, value, l_lov_sql);
3408
3409 ELSE
3410 get_plan_element_lov (plan_id, char_id, org_id, user_id, l_lov_sql);
3411 END IF;
3412
3413 RETURN l_lov_sql;
3414
3415 END get_lov_sql;
3416
3417
3418 -- Bug 4270911. SQL bind compliance fix.
3419 -- Please see bugdb for more details and TD link.
3420 -- srhariha. Thu Apr 7 21:43:08 PDT 2005.
3421
3422 FUNCTION get_lov_bind_values (
3423 plan_id IN NUMBER,
3424 char_id IN NUMBER,
3425 org_id IN NUMBER DEFAULT NULL,
3426 user_id IN NUMBER DEFAULT NULL,
3427 depen1 IN VARCHAR2 DEFAULT NULL,
3428 depen2 IN VARCHAR2 DEFAULT NULL,
3429 depen3 IN VARCHAR2 DEFAULT NULL,
3430 value IN VARCHAR2 DEFAULT NULL) RETURN VARCHAR2 IS
3431
3432 BEGIN
3433
3434
3435 -- Collection elements dependent only on ORG_ID.
3436 --
3437 -- Bug 7197055
3438 -- Commented out item, because a new method to fetch its bind values
3439 -- been created.
3440 -- skolluku
3441 --
3442 IF char_id IN ( qa_ss_const.department,
3443 qa_ss_const.job_name,
3444 qa_ss_const.work_order,
3445 qa_ss_const.production_line,
3446 qa_ss_const.resource_code,
3447 -- qa_ss_const.uom,
3448 qa_ss_const.subinventory,
3449 -- qa_ss_const.item,
3450 qa_ss_const.asset_group,
3451 qa_ss_const.rework_job,
3452 qa_ss_const.bill_reference,
3453 qa_ss_const.routing_reference,
3454 -- R12 OPM Deviations. Bug 4345503 Start
3455 qa_ss_const.process_batch_num,
3456 -- R12 OPM Deviations. Bug 4345503 End
3457 qa_ss_const.to_subinventory ) THEN
3458
3459 RETURN to_char(org_id);
3460
3461 END IF;
3462
3463 -- Collection elements based on QA_LOOKUPS.
3464
3465 IF char_id IN ( qa_ss_const.nonconformance_source,
3466 qa_ss_const.nonconform_severity,
3467 qa_ss_const.nonconform_priority,
3468 qa_ss_const.nonconformance_status,
3469 qa_ss_const.nonconformance_type,
3470 qa_ss_const.request_source,
3471 qa_ss_const.request_priority,
3472 qa_ss_const.request_severity,
3473 qa_ss_const.request_status ) THEN
3474
3475 RETURN to_char(char_id);
3476
3477 END IF;
3478
3479 -- Other dependent elements.
3480
3481 IF char_id = qa_ss_const.task_number THEN
3482 RETURN get_task_number_bind_values(depen1);
3483
3484 ELSIF (char_id = qa_ss_const.revision OR
3485 char_id = qa_ss_const.comp_revision) THEN
3486 RETURN get_revision_bind_values(org_id, depen1);
3487
3488 -- Bug 5005707. Adding item as a filtering criteria
3489 -- for item uom to improve performance.
3490 -- Removed the same from the above if condition
3491 -- saugupta Mon, 10 Jul 2006 21:47:17 -0700 PDT
3492 ELSIF (char_id = qa_ss_const.uom) THEN
3493 RETURN get_uom_bind_values(p_org_id => org_id, p_item_name => depen1);
3494
3495 ELSIF (char_id = qa_ss_const.lot_number) THEN
3496 RETURN get_lot_number_bind_values(org_id, depen1);
3497
3498 ELSIF (char_id = qa_ss_const.comp_lot_number) THEN
3499 RETURN get_comp_lot_bind_values(org_id, depen1);
3500
3501 ELSIF (char_id = qa_ss_const.serial_number) THEN
3502 RETURN get_serial_no_bind_values(plan_id, org_id, depen2, depen3, depen1);
3503
3504 ELSIF (char_id = qa_ss_const.comp_serial_number) THEN
3505 RETURN get_comp_serial_no_bind_values(plan_id, org_id, depen2, depen3, depen1);
3506
3507 -- dgupta: Start R12 EAM Integration. Bug 4345492
3508 ELSIF (char_id = qa_ss_const.asset_instance_number) THEN
3509 RETURN get_asset_inst_num_bind_values(org_id, depen1, depen2);
3510
3511 ELSIF (char_id = qa_ss_const.asset_number) THEN
3512 RETURN get_asset_number_bind_values(org_id, depen1, depen2);
3513 --dgupta: End R12 EAM Integration. Bug 4345492
3514
3515 ELSIF (char_id = qa_ss_const.from_op_seq_num) OR
3516 (char_id = qa_ss_const.to_op_seq_num) THEN
3517 RETURN get_op_seq_no_bind_values(plan_id, org_id, depen1, depen2);
3518
3519 ELSIF (char_id = qa_ss_const.po_line_num) THEN
3520 RETURN get_po_line_no_bind_values(depen1);
3521
3522 ELSIF (char_id = qa_ss_const.po_shipment_num) THEN
3523 -- Bug 9817478. If PO Release Num is present, use the new proc.
3524 -- Else use the existing.
3525 IF (depen3 IS NOT NULL) THEN
3526 RETURN get_po_shipments_bind_values (depen1, depen2, depen3);
3527 ELSE
3528 RETURN get_po_shipments_bind_values (depen1, depen2);
3529 END IF;
3530 ELSIF (char_id = qa_ss_const.comp_item) THEN
3531 RETURN get_comp_item_bind_values (org_id, depen1);
3532
3533 -- dgupta: Start R12 EAM Integration. Bug 4345492
3534 ELSIF (char_id = qa_ss_const.asset_activity) THEN
3535 RETURN get_asset_activity_bind_values (org_id, depen1, depen2, depen3);
3536
3537 ELSIF (char_id = qa_ss_const.followup_activity) THEN
3538 RETURN get_followup_act_bind_values(org_id, depen1, depen2, depen3);
3539 --dgupta: End R12 EAM Integration. Bug 4345492
3540
3541 ELSIF (char_id = qa_ss_const.locator) OR
3542 (char_id = qa_ss_const.to_locator) THEN
3543 RETURN get_locator_bind_values(org_id, depen1);
3544
3545 ELSIF (char_id = qa_ss_const.maintenance_op_seq) THEN
3546 RETURN get_maint_op_seq_bind_values(org_id, depen1);
3547
3548 -- R12 OPM Deviations. Bug 4345503 Start
3549 ELSIF (char_id = qa_ss_const.process_batchstep_num) THEN
3550 RETURN GET_PROCESS_STEP_BIND_VALUE(org_id, depen1);
3551
3552 ELSIF (char_id = qa_ss_const.process_operation) THEN
3553 RETURN GET_PROCESS_OPRN_BIND_VALUE(org_id, depen1, depen2);
3554
3555 ELSIF (char_id = qa_ss_const.process_activity) THEN
3556 RETURN GET_PROCESS_ACT_BIND_VALUE(org_id, depen1, depen2);
3557
3558 ELSIF (char_id = qa_ss_const.process_resource) THEN
3559 RETURN GET_PROCESS_RSR_BIND_VALUE(org_id, depen1, depen2, depen3);
3560
3561 ELSIF (char_id = qa_ss_const.process_parameter) THEN
3562 RETURN GET_PROCESS_PARAM_BIND_VALUE(org_id, depen1);
3563 -- R12 OPM Deviations. Bug 4345503 End
3564
3565 ELSIF (char_id = qa_ss_const.po_release_num) THEN
3566 --
3567 -- Bug 5003511. R12 Performance bug. SQLID: 15008630
3568 -- Release number is dependent on PO Number.
3569 -- Call new method for bind values.
3570 -- srhariha. Wed Feb 8 02:10:26 PST 2006.
3571 --
3572 -- Bug 9817478. Added PO Line as a parent.
3573 -- skolluku.
3574 RETURN get_po_rel_no_bind_values(depen1,depen2);
3575 --
3576 -- Bug 6161802
3577 -- Return rma number as a bind variable for rma line number lov
3578 -- skolluku Mon Jul 16 22:08:16 PDT 2007
3579 --
3580 ELSIF (char_id = qa_ss_const.rma_line_num) THEN
3581 RETURN get_rma_line_num_bind_values(depen1);
3582
3583 -- Bug 7716875.Return SO Number as bind variable for
3584 -- SO Line Number.pdube Mon Apr 13 03:25:19 PDT 2009
3585 ELSIF (char_id = qa_ss_const.order_line) THEN
3586 RETURN get_so_line_num_bind_values(depen1);
3587
3588 -- Bug 7588754.Added org_id,item and srl number as binds
3589 ELSIF (char_id = qa_ss_const.serial_status) THEN
3590 RETURN get_serial_status_bind_values(org_id,depen1,depen2);
3591 --
3592 -- bug 7197055
3593 -- Fetch bind values for item.
3594 -- skolluku
3595 --
3596 ELSIF (char_id = qa_ss_const.item) THEN
3597 RETURN get_item_bind_values(org_id,depen1);
3598 --
3599 -- Bug 9032151
3600 -- Return item as a bind variable for item instance lov
3601 -- skolluku
3602 --
3603 ELSIF (char_id = qa_ss_const.item_instance) THEN
3604 RETURN get_item_instance_bind_values(org_id, depen1);
3605 --
3606 -- Bug 9359442
3607 -- Return item as a bind variable for item instance serial lov
3608 -- skolluku
3609 --
3610 ELSIF (char_id = qa_ss_const.item_instance_serial) THEN
3611 RETURN get_item_inst_ser_bind_values(org_id, depen1);
3612 ELSE
3613
3614 -- Will handle qa_plan_char_value_lookups.
3615 -- For all other cases it returns NULL
3616 RETURN sql_string_bind_values (plan_id, char_id);
3617
3618 END IF;
3619
3620
3621 RETURN NULL;
3622
3623 END get_lov_bind_values;
3624
3625
3626 END qa_ss_lov_api;
[Home] [Help]