1 PACKAGE BODY qa_ss_lov_api AS
2 /* $Header: qltsslob.plb 120.16.12010000.4 2008/10/17 08:05:57 pdube 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 PROCEDURE get_so_line_number_lov (value IN VARCHAR2, x_lov_sql OUT NOCOPY VARCHAR2) IS
457
458 BEGIN
459
460
461 /*x_lov_sql := 'SELECT to_char(sl.line_number), msik.concatenated_segments
462 FROM mtl_system_items_kfv msik, so_lines sl
463 WHERE sl.inventory_item_id = msik.inventory_item_id'; */
464
465 x_lov_sql := NULL;
466
467 END get_so_line_number_lov;
468
469
470 --
471 -- Bug 5003511. R12 Performance bug. SQLID: 15008630
472 -- Release number is dependent on PO Number.
473 -- As per safe spec, creating an overloaded method for getting
474 -- the lov sql. Also created new method for getting the bind value.
475 -- srhariha. Wed Feb 8 02:10:26 PST 2006.
476 --
477 PROCEDURE get_po_release_number_lov (p_plan_id IN NUMBER,
478 po_header_id IN VARCHAR2,
479 value IN VARCHAR2,
480 x_lov_sql OUT NOCOPY VARCHAR2) IS
481
482 BEGIN
483
484 IF ((p_plan_id is not null) AND (po_header_id is null)) THEN
485 fnd_message.set_name('QA', 'QA_SSQR_DEPENDENT_LOV');
486 fnd_message.set_token('DEPELEM',
487 qa_plan_element_api.get_prompt(p_plan_id, qa_ss_const.po_number));
488 fnd_msg_pub.add();
489 END IF;
490
491 x_lov_sql := 'SELECT to_char(release_num), QLTDATE.date_to_user(release_date) ' ||
492 'FROM po_releases pr ' ||
493 'WHERE pr.po_header_id = :1 ' ||
494 'ORDER BY pr.release_num ';
495
496 END get_po_release_number_lov;
497
498 FUNCTION get_po_rel_no_bind_values (p_po_header_id IN VARCHAR2)
499 RETURN VARCHAR2 IS
500
501 BEGIN
502
503 RETURN p_po_header_id;
504
505 END get_po_rel_no_bind_values;
506
507 -- End Bug 5003511. SQLID : 15008630.
508
509 -- Bug 5003511 SQLID : 15008630
510 -- commneting out unused overridden procedure below
511 -- saugupta Tue, 14 Feb 2006 07:07:11 -0800 PDT
512 /*
513 PROCEDURE get_po_release_number_lov (value IN VARCHAR2, x_lov_sql OUT
514 NOCOPY VARCHAR2) IS
515
516 BEGIN
517 -- Bug 3215866 ksoh Wed Oct 29 16:21:11 PST 2003
518 -- added to_char and QLTDATE to lov sql to make it compatible to
519 -- char column when it is union with the kludge sql: select '1'.....
520 x_lov_sql := 'SELECT to_char(release_num), QLTDATE.date_to_user(release_date)
521 FROM po_releases pr
522 ORDER BY pr.release_num';
523
524 END get_po_release_number_lov;
525 */
526
527 PROCEDURE get_project_number_lov (value IN VARCHAR2, x_lov_sql OUT
528 NOCOPY VARCHAR2) IS
529
530 BEGIN
531 /*
532 mtl_project_v changed to pjm_projects_all_v (selects from both pjm enabled and
533 non-pjm enabled orgs).
534 rkaza, 11/10/2001.
535 */
536
537 --
538 -- Bug 5249078. Changed pjm_projects_all_v to
539 -- pjm_projects_v for MOAC compliance.
540 -- bso Thu Jun 1 10:46:50 PDT 2006
541 --
542
543 x_lov_sql := 'SELECT project_number, project_name
544 FROM pjm_projects_v
545 ORDER BY project_number';
546
547 END get_project_number_lov;
548
549
550 /*
551 anagarwa Thu Jan 29 15:04:26 PST 2004
552 Bug 3404863 : Task LOV should be dependent upon Project lov
553 We look for project number and get project id and then add this to where
554 clause of task lov sql
555
556 */
557 PROCEDURE get_task_number_lov (plan_id IN NUMBER,
558 p_project_number IN VARCHAR2,
559 value IN VARCHAR2,
560 x_lov_sql OUT
561 NOCOPY VARCHAR2) IS
562
563 l_project_id NUMBER ;
564
565 BEGIN
566
567 l_project_id := qa_plan_element_api.get_project_number_id(p_project_number);
568 IF (l_project_id is null) THEN
569 fnd_message.set_name('QA', 'QA_SSQR_DEPENDENT_LOV');
570 fnd_message.set_token('DEPELEM',
571 qa_plan_element_api.get_prompt(plan_id, qa_ss_const.project_number));
572 fnd_msg_pub.add();
573 END IF;
574
575 x_lov_sql := 'select task_number, task_name
576 from mtl_task_v
577 where project_id = :1
578 ORDER BY task_number';
579
580 END get_task_number_lov;
581
582 -- Bug 4270911. SQL bind compliance fix.
583 -- New function added to return bind values.
584 -- Please see bugdb for more details and TD link.
585 -- srhariha. Thu Apr 7 21:43:08 PDT 2005
586
587 FUNCTION get_task_number_bind_values (p_project_number IN VARCHAR2)
588 RETURN VARCHAR2 IS
589
590 l_project_id NUMBER;
591 BEGIN
592
593 l_project_id := qa_plan_element_api.get_project_number_id(p_project_number);
594
595 RETURN to_char(l_project_id);
596
597 END get_task_number_bind_values;
598
599 PROCEDURE get_task_number_lov (value IN VARCHAR2, x_lov_sql OUT
600 NOCOPY VARCHAR2) IS
601
602 BEGIN
603
604 -- anagarwa Thu Jan 29 15:04:26 PST 2004
605 -- Bug 3404863 : task lov should be dependent upon project.
606 get_task_number_lov(NULL, NULL, value, x_lov_sql);
607 /*
608 x_lov_sql := 'SELECT task_number, task_name
609 FROM mtl_task_v
610 ORDER BY task_number';
611 */
612
613 END get_task_number_lov;
614
615
616 PROCEDURE get_rma_number_lov (value IN VARCHAR2, x_lov_sql OUT
617 NOCOPY VARCHAR2) IS
618
619 BEGIN
620 -- Bug 3215866 ksoh Wed Oct 29 16:21:11 PST 2003
621 -- added to_char to lov sql to make it compatible to
622 -- char column when it is union with the kludge sql: select '1'.....
623 x_lov_sql := 'SELECT to_char(sh.order_number), sot.name
624 FROM so_order_types sot,
625 oe_order_headers sh,
626 qa_customers_lov_v rc
627 WHERE sh.order_type_id = sot.order_type_id and
628 sh.sold_to_org_id = rc.customer_id and
629 sh.order_category_code in (''RETURN'', ''MIXED'')
630 ORDER BY sh.order_number';
631
632 END get_rma_number_lov;
633
634 --
635 -- Bug 6161802
636 -- Added procedure to return lov for rma line number
637 -- with rma number as a bind variable
638 -- skolluku Mon Jul 16 22:08:16 PDT 2007
639 --
640 PROCEDURE get_rma_line_num_lov (p_plan_id IN NUMBER,
641 p_rma_number IN VARCHAR2,
642 value IN VARCHAR2,
643 x_lov_sql OUT NOCOPY VARCHAR2) IS
644 BEGIN
645 IF (p_rma_number is null) THEN
646 fnd_message.set_name('QA', 'QA_SSQR_DEPENDENT_LOV');
647 fnd_message.set_token('DEPELEM',
648 qa_plan_element_api.get_prompt(p_plan_id, qa_ss_const.rma_number));
649 fnd_msg_pub.add();
650 END IF;
651
652 x_lov_sql := 'select distinct to_char(oel.line_number),''RMA Number: '' ||
653 sh.order_number || '';'' || ''Item: '' || oel.ordered_item description
654 from oe_order_lines oel, so_order_types sot, oe_order_headers sh
655 where sh.order_type_id = sot.order_type_id ' ||
656 ' and oel.header_id = sh.header_id ' ||
657 ' and oel.line_category_code in (''RETURN'', ''MIXED'') ' ||
658 ' and sh.order_number = :1 ' ||
659 ' order by description, line_number ';
660
661 END get_rma_line_num_lov;
662
663 --
664 -- Bug 6161802
665 -- Return rma number as bind value for rma line number lov
666 -- skolluku Mon Jul 16 22:08:16 PDT 2007
667 --
668 FUNCTION get_rma_line_num_bind_values (p_rma_number IN VARCHAR2)
669 RETURN VARCHAR2 IS
670 BEGIN
671
672 IF (p_rma_number IS NULL) THEN
673 RETURN NULL;
674 END IF;
675
676 RETURN to_char(p_rma_number);
677
678 END get_rma_line_num_bind_values;
679
680
681 -- Bug 3228490 ksoh Fri Oct 31 11:38:21 PST 2003
682 -- check if any dependent element value is null
683 -- if so, put error message with element prompts
684 -- requires plan_id to be passed in to retrieve element prompts.
685 -- old signature calls new signature with plan_id = NULL to
686 -- maintain old behavior
687 PROCEDURE get_uom_lov (plan_id IN NUMBER, org_id IN NUMBER, x_item_name IN VARCHAR2,
688 value IN VARCHAR2, x_lov_sql OUT NOCOPY VARCHAR2) IS
689
690 -- x_item_id NUMBER DEFAULT NULL;
691
692 BEGIN
693 IF ((plan_id is not null) and (x_item_name is null)) THEN
694 fnd_message.set_name('QA', 'QA_SSQR_DEPENDENT_LOV');
695 fnd_message.set_token('DEPELEM',
696 qa_plan_element_api.get_prompt(plan_id, qa_ss_const.item));
697 fnd_msg_pub.add();
698 END IF;
699
700 -- This procedure is used for both uom and component uom
701
702 -- x_item_id := qa_flex_util.get_item_id(org_id, x_item_name);
703
704 x_lov_sql := 'SELECT uom_code, description
705 FROM mtl_item_uoms_view
706 WHERE organization_id = :1
707 AND inventory_item_id = :2
708 ORDER BY uom_code';
709
710 END get_uom_lov;
711
712 PROCEDURE get_uom_lov (org_id IN NUMBER, x_item_name IN VARCHAR2,
713 value IN VARCHAR2, x_lov_sql OUT NOCOPY VARCHAR2) IS
714 BEGIN
715 get_uom_lov (NULL, org_id, x_item_name, value, x_lov_sql);
716 END get_uom_lov;
717
718 -- Bug 5005707. New function to return the proper bind values
719 -- saugupta Mon, 10 Jul 2006 21:51:04 -0700 PDT
720 FUNCTION get_uom_bind_values (p_org_id IN NUMBER,
721 p_item_name IN VARCHAR2)
722 RETURN VARCHAR2 IS
723
724 l_item_id NUMBER;
725
726 BEGIN
727
728 l_item_id := qa_flex_util.get_item_id(p_org_id, p_item_name);
729
730 IF (l_item_id IS NULL) THEN
731 RETURN NULL;
732 END IF;
733
734 RETURN to_char(p_org_id) || g_bind_value_list_seperator || to_char(l_item_id) ;
735
736 END get_uom_bind_values;
737
738
739
740 -- Bug 3228490 ksoh Fri Oct 31 11:38:21 PST 2003
741 -- check if any dependent element value is null
742 -- if so, put error message with element prompts
743 -- requires plan_id to be passed in to retrieve element prompts.
744 -- old signature calls new signature with plan_id = NULL to
745 -- maintain old behavior
746 PROCEDURE get_revision_lov (plan_id IN NUMBER, org_id IN NUMBER, x_item_name IN VARCHAR2,
747 value IN VARCHAR2, x_lov_sql OUT NOCOPY VARCHAR2) IS
748
749 -- x_item_id NUMBER DEFAULT NULL;
750
751 BEGIN
752 IF ((plan_id is not null) AND (x_item_name is null)) THEN
753 fnd_message.set_name('QA', 'QA_SSQR_DEPENDENT_LOV');
754 fnd_message.set_token('DEPELEM',
755 qa_plan_element_api.get_prompt(plan_id, qa_ss_const.item));
756 fnd_msg_pub.add();
757 END IF;
758
759 -- This procedure is used for both revision and component revision
760
761 -- x_item_id := qa_flex_util.get_item_id(org_id, x_item_name);
762
763 -- anagarwa Mon Feb 24 17:08:57 PST 2003
764 -- Bug 2808693
765 -- using QLTDATE.date_to_user for effectivity date as LOV's in selfservice
766 -- expect both selected columns to be varchar2 or they give an Error.
767
768 -- Bug 5371467. Rewriting revision LOV SQL
769 -- saugupta Tue, 18 Jul 2006 01:30:18 -0700 PDT
770 /*
771 x_lov_sql := 'SELECT revision, QLTDATE.date_to_user(effectivity_date)
772 FROM mtl_item_revisions
773 WHERE inventory_item_id = :1
774 AND organization_id = :2
775 ORDER BY revision';
776 */
777 x_lov_sql := 'SELECT revision, QLTDATE.date_to_user(effectivity_date)
778 FROM mtl_item_revisions mir,
779 mtl_system_items_kfv msi
780 WHERE mir.inventory_item_id = msi.inventory_item_id
781 AND mir.organization_id = msi.organization_id
782 AND msi.revision_qty_control_code = 2
783 AND mir.inventory_item_id = :1
784 AND mir.organization_id = :2
785 ORDER BY revision';
786
787 END get_revision_lov;
788
789
790 FUNCTION get_revision_bind_values (p_org_id IN NUMBER,
791 p_item_name IN VARCHAR2)
792 RETURN VARCHAR2 IS
793
794 l_item_id NUMBER;
795
796 BEGIN
797
798 l_item_id := qa_flex_util.get_item_id(p_org_id, p_item_name);
799
800 IF (l_item_id IS NULL) THEN
801 RETURN NULL;
802 END IF;
803
804 RETURN to_char(l_item_id) || g_bind_value_list_seperator || to_char(p_org_id) ;
805
806 END get_revision_bind_values;
807
808 PROCEDURE get_revision_lov (org_id IN NUMBER, x_item_name IN VARCHAR2,
809 value IN VARCHAR2, x_lov_sql OUT NOCOPY VARCHAR2) IS
810 BEGIN
811 get_revision_lov (NULL, org_id, x_item_name, value, x_lov_sql);
812 END get_revision_lov;
813
814
815 PROCEDURE get_subinventory_lov (org_id IN NUMBER, value IN VARCHAR2,
816 x_lov_sql OUT NOCOPY VARCHAR2) IS
817
818 BEGIN
819
820 -- This procedure is used for both subinventory and component subinventory
821
822 x_lov_sql := 'SELECT secondary_inventory_name, description
823 FROM mtl_secondary_inventories
824 WHERE organization_id = :1
825 AND nvl(disable_date, sysdate+1) > sysdate ';
826 -- ORDER BY secondary_inventory_name';
827
828 END get_subinventory_lov;
829
830
831 -- anagarwa Thu Aug 12 15:49:51 PDT 2004
832 -- bug 3830258 incorrect LOVs in QWB
833 -- synced up the lot number lov with forms
834 PROCEDURE get_lot_number_lov (p_plan_id IN NUMBER,
835 p_org_id IN NUMBER,
836 p_item IN VARCHAR2,
837 value IN VARCHAR2,
838 x_lov_sql OUT NOCOPY VARCHAR2) IS
839 BEGIN
840
841 IF (p_item is null) THEN
842 fnd_message.set_name('QA', 'QA_SSQR_DEPENDENT_LOV');
843 fnd_message.set_token('DEPELEM',
844 qa_plan_element_api.get_prompt(p_plan_id, qa_ss_const.item));
845 fnd_msg_pub.add();
846 END IF;
847
848 x_lov_sql := 'select lot_number, description
849 from mtl_lot_numbers
850 where organization_id = :1' ||
851 ' and inventory_item_id = :2 ' ||
852 ' and (disable_flag = 2 or disable_flag is null)';
853
854 END get_lot_number_lov;
855
856 FUNCTION get_lot_number_bind_values(p_org_id IN NUMBER,
857 p_item_name IN VARCHAR2)
858 RETURN VARCHAR2 IS
859
860
861 l_item_id NUMBER;
862
863 BEGIN
864
865 l_item_id := qa_flex_util.get_item_id(p_org_id, p_item_name);
866
867 IF l_item_id IS NULL THEN
868 RETURN NULL;
869 END IF;
870
871 RETURN to_char(p_org_id) || g_bind_value_list_seperator || to_char(l_item_id);
872
873 END get_lot_number_bind_values;
874
875 -- anagarwa Thu Aug 12 15:49:51 PDT 2004
876 -- bug 3830258 incorrect LOVs in QWB
877 -- synced up the component lot number lov with forms
878 PROCEDURE get_comp_lot_number_lov (p_plan_id IN NUMBER,
879 p_org_id IN NUMBER,
880 p_comp_item IN VARCHAR2,
881 value IN VARCHAR2,
882 x_lov_sql OUT NOCOPY VARCHAR2) IS
883 -- l_lov_sql VARCHAR2(300);
884 BEGIN
885 IF (p_comp_item is null) THEN
886 fnd_message.set_name('QA', 'QA_SSQR_DEPENDENT_LOV');
887 fnd_message.set_token('DEPELEM',
888 qa_plan_element_api.get_prompt(p_plan_id, qa_ss_const.comp_item));
889 fnd_msg_pub.add();
890 END IF;
891
892 x_lov_sql := 'select lot_number, description
893 from mtl_lot_numbers
894 where organization_id = :1 '||
895 ' and inventory_item_id = :2 ' ||
896 ' and (disable_flag = 2 or disable_flag is null)';
897
898 END get_comp_lot_number_lov;
899
900 FUNCTION get_comp_lot_bind_values(p_org_id IN NUMBER,
901 p_item_name IN VARCHAR2)
902 RETURN VARCHAR2 IS
903
904
905 l_item_id NUMBER;
906
907 BEGIN
908
909 l_item_id := qa_flex_util.get_item_id(p_org_id, p_item_name);
910
911 IF l_item_id IS NULL THEN
912 RETURN NULL;
913 END IF;
914
915 RETURN to_char(p_org_id) || g_bind_value_list_seperator || to_char(l_item_id);
916
917 END get_comp_lot_bind_values;
918
919 --
920 -- Bug 5003511. R12 Performance fix. Obsolete the unused method
921 -- so that it wont appear in fututre SQL Literal reports.
922 -- srhariha. Wed Feb 1 04:16:10 PST 2006
923 --
924
925 PROCEDURE get_lot_number_lov (x_transaction_id IN NUMBER, value IN VARCHAR2,
926 x_lov_sql OUT NOCOPY VARCHAR2) IS
927
928 BEGIN
929 /*
930 x_lov_sql := 'SELECT lot_number, lot_expiration_date
931 FROM mtl_transaction_lots_temp
932 WHERE transaction_temp_id = ' || x_transaction_id || '
933 ORDER BY lot_number';
934 */
935 x_lov_sql := NULL;
936
937 END get_lot_number_lov;
938
939 -- anagarwa Thu Aug 12 15:49:51 PDT 2004
940 -- bug 3830258 incorrect LOVs in QWB
941 -- synced up the serial number lov with forms
942 PROCEDURE get_serial_number_lov (p_plan_id IN NUMBER,
943 p_org_id IN NUMBER,
944 p_item IN VARCHAR2,
945 p_revision IN VARCHAR2,
946 p_lot_number IN VARCHAR2,
947 p_value IN VARCHAR2,
948 x_lov_sql OUT NOCOPY VARCHAR2) IS
949
950 l_item_id NUMBER;
951 j NUMBER;
952 BEGIN
953
954 IF (p_item is null) THEN
955 fnd_message.set_name('QA', 'QA_SSQR_DEPENDENT_LOV');
956 fnd_message.set_token('DEPELEM',
957 qa_plan_element_api.get_prompt(p_plan_id, qa_ss_const.item));
958 fnd_msg_pub.add();
959 END IF;
960
961 l_item_id := qa_flex_util.get_item_id(p_org_id, p_item);
962
963 x_lov_sql := 'SELECT msn.serial_number, msn.current_status_name
964 FROM mtl_serial_numbers_all_v msn
965 WHERE msn.current_organization_id = :1 ' ||
966 ' AND msn.inventory_item_id = :2 ';
967
968 -- anagarwa Thu Aug 12 15:49:51 PDT 2004
969 -- discussed with Bryan. This sql is a little different from the one
970 -- being used in forms. The reason is that if user selects a lot number
971 -- we expect the item to be lot controlled too. So serial lov is restricted
972 -- by lot number
973 j := 3;
974 IF (qa_plan_element_api.element_in_plan(p_plan_id, qa_ss_const.lot_number)
975 AND p_lot_number is not NULL
976 AND is_item_lot_controlled(l_item_id, p_org_id)) THEN
977 x_lov_sql := x_lov_sql || ' AND msn.lot_number = :' || to_char(j) || ' ';
978 j := j+1;
979 END IF;
980 -- anagarwa Thu Aug 12 15:49:51 PDT 2004
981 -- if revision is entered then restrict the serial lov with revision
982 IF (qa_plan_element_api.element_in_plan(p_plan_id, qa_ss_const.revision)
983 AND p_revision is not NULL
984 AND is_item_revision_controlled(l_item_id, p_org_id)) THEN
985 x_lov_sql := x_lov_sql || ' AND msn.revision = :' || to_char(j) || ' ';
986 END IF;
987
988 END get_serial_number_lov;
989
990
991 FUNCTION get_serial_no_bind_values (p_plan_id IN NUMBER,
992 p_org_id IN NUMBER,
993 p_item IN VARCHAR2,
994 p_revision IN VARCHAR2,
995 p_lot_number IN VARCHAR2)
996 RETURN VARCHAR2 IS
997
998 l_item_id NUMBER;
999 l_ret_string VARCHAR2(1000);
1000 BEGIN
1001
1002
1003 l_item_id := qa_flex_util.get_item_id(p_org_id, p_item);
1004
1005 IF l_item_id IS NULL THEN
1006 RETURN NULL;
1007 END IF;
1008
1009 l_ret_string := to_char(p_org_id) || g_bind_value_list_seperator || to_char(l_item_id);
1010
1011
1012 IF (qa_plan_element_api.element_in_plan(p_plan_id, qa_ss_const.lot_number)
1013 AND p_lot_number is not NULL
1014 AND is_item_lot_controlled(l_item_id, p_org_id)) THEN
1015 l_ret_string := l_ret_string || g_bind_value_list_seperator || p_lot_number;
1016 END IF;
1017
1018 IF (qa_plan_element_api.element_in_plan(p_plan_id, qa_ss_const.revision)
1019 AND p_revision is not NULL
1020 AND is_item_revision_controlled(l_item_id, p_org_id)) THEN
1021 l_ret_string := l_ret_string || g_bind_value_list_seperator || p_revision;
1022 END IF;
1023
1024 RETURN l_ret_string;
1025
1026 END get_serial_no_bind_values;
1027
1028
1029 -- anagarwa Thu Aug 12 15:49:51 PDT 2004
1030 -- bug 3830258 incorrect LOVs in QWB
1031 -- synced up the component serial number lov with forms
1032 PROCEDURE get_comp_serial_number_lov (p_plan_id IN NUMBER,
1033 p_org_id IN NUMBER,
1034 p_comp_item IN VARCHAR2,
1035 p_comp_revision IN VARCHAR2,
1036 p_comp_lot_number IN VARCHAR2,
1037 p_value IN VARCHAR2,
1038 x_lov_sql OUT NOCOPY VARCHAR2) IS
1039 l_item_id NUMBER;
1040 j NUMBER;
1041 BEGIN
1042
1043 IF (p_comp_item is null) THEN
1044 fnd_message.set_name('QA', 'QA_SSQR_DEPENDENT_LOV');
1045 fnd_message.set_token('DEPELEM',
1046 qa_plan_element_api.get_prompt(p_plan_id, qa_ss_const.comp_item));
1047 fnd_msg_pub.add();
1048 END IF;
1049
1050 l_item_id := qa_flex_util.get_item_id(p_org_id, p_comp_item);
1051
1052 x_lov_sql := 'SELECT msn.serial_number, msn.current_status_name
1053 FROM mtl_serial_numbers_all_v msn
1054 WHERE msn.current_organization_id = :1 ' ||
1055 ' AND msn.inventory_item_id = :2 ';
1056 j := 3;
1057 IF (qa_plan_element_api.element_in_plan(p_plan_id, qa_ss_const.comp_lot_number)
1058 AND p_comp_lot_number is not NULL
1059 AND is_item_lot_controlled(l_item_id, p_org_id)) THEN
1060 x_lov_sql := x_lov_sql || ' AND msn.lot_number = :' || to_char(j) || ' ';
1061 j := j+1;
1062 END IF;
1063 IF (qa_plan_element_api.element_in_plan(p_plan_id, qa_ss_const.comp_revision)
1064 AND p_comp_revision is not NULL
1065 AND is_item_revision_controlled(l_item_id, p_org_id)) THEN
1066 x_lov_sql := x_lov_sql || ' AND msn.revision = :' || to_char(j) || ' ';
1067 END IF;
1068
1069 END get_comp_serial_number_lov;
1070
1071 FUNCTION get_comp_serial_no_bind_values (p_plan_id IN NUMBER,
1072 p_org_id IN NUMBER,
1073 p_item IN VARCHAR2,
1074 p_revision IN VARCHAR2,
1075 p_lot_number IN VARCHAR2)
1076 RETURN VARCHAR2 IS
1077
1078 l_item_id NUMBER;
1079 l_ret_string VARCHAR2(1000);
1080 BEGIN
1081
1082
1083 l_item_id := qa_flex_util.get_item_id(p_org_id, p_item);
1084
1085 IF l_item_id IS NULL THEN
1086 RETURN NULL;
1087 END IF;
1088
1089 l_ret_string := to_char(p_org_id) || g_bind_value_list_seperator || to_char(l_item_id);
1090
1091
1092 IF (qa_plan_element_api.element_in_plan(p_plan_id, qa_ss_const.lot_number)
1093 AND p_lot_number is not NULL
1094 AND is_item_lot_controlled(l_item_id, p_org_id)) THEN
1095 l_ret_string := l_ret_string || g_bind_value_list_seperator || p_lot_number;
1096 END IF;
1097
1098 IF (qa_plan_element_api.element_in_plan(p_plan_id, qa_ss_const.revision)
1099 AND p_revision is not NULL
1100 AND is_item_revision_controlled(l_item_id, p_org_id)) THEN
1101 l_ret_string := l_ret_string || g_bind_value_list_seperator || p_revision;
1102 END IF;
1103
1104 RETURN l_ret_string;
1105
1106 END get_comp_serial_no_bind_values;
1107
1108
1109
1110 -- Bug 3228490 ksoh Fri Oct 31 11:38:21 PST 2003
1111 -- check if any dependent element value is null
1112 -- if so, put error message with element prompts
1113 -- requires plan_id to be passed in to retrieve element prompts.
1114 -- old signature calls new signature with plan_id = NULL to
1115 -- maintain old behavior
1116
1117 --
1118 -- Bug 5003511. R12 Performance fix. Obsolete the unused method
1119 -- so that it wont appear in fututre SQL Literal reports.
1120 -- srhariha. Wed Feb 1 04:16:10 PST 2006
1121 --
1122
1123 PROCEDURE get_serial_number_lov (plan_id IN NUMBER, x_transaction_id IN NUMBER, x_lot_number
1124 IN VARCHAR2, value IN VARCHAR2, x_lov_sql OUT NOCOPY VARCHAR2) IS
1125
1126 BEGIN
1127 /*
1128 IF (x_lot_number is null) THEN
1129 fnd_message.set_name('QA', 'QA_SSQR_DEPENDENT_LOV');
1130 fnd_message.set_token('DEPELEM',
1131 qa_plan_element_api.get_prompt(plan_id, qa_ss_const.lot_number));
1132 fnd_msg_pub.add();
1133 END IF;
1134
1135 x_lov_sql := 'SELECT msn.serial_number, msn.current_status
1136 FROM mtl_serial_numbers msn,
1137 mtl_transaction_lots_temp mtlt
1138 WHERE msn.line_mark_id = ' || x_transaction_id || '
1139 AND mtlt.transaction_temp_id = msn.line_mark_id
1140 AND mtlt.serial_transaction_temp_id = msn.lot_line_mark_id
1141 AND mtlt.lot_number = ' || '''' || x_lot_number || '''' || '
1142 AND mtlt.lot_number IS NOT NULL
1143 UNION ALL
1144 SELECT msn.serial_number, msn.current_status
1145 FROM mtl_serial_numbers msn
1146 WHERE msn.line_mark_id = ' || x_transaction_id || '
1147 ORDER BY serial_number';
1148 */
1149 x_lov_sql := NULL;
1150 END get_serial_number_lov;
1151
1152 PROCEDURE get_serial_number_lov (x_transaction_id IN NUMBER, x_lot_number
1153 IN VARCHAR2, value IN VARCHAR2, x_lov_sql OUT NOCOPY VARCHAR2) IS
1154
1155 BEGIN
1156 get_serial_number_lov (NULL, x_transaction_id, x_lot_number, value, x_lov_sql);
1157 END get_serial_number_lov;
1158
1159 PROCEDURE get_asset_instance_number_lov (plan_id IN NUMBER, x_org_id IN NUMBER, x_asset_group IN VARCHAR2,
1160 x_asset_number IN VARCHAR2,value IN VARCHAR2, x_lov_sql OUT NOCOPY VARCHAR2) IS
1161 j NUMBER;
1162 BEGIN
1163
1164 x_lov_sql :=
1165 'SELECT cii.instance_number, cii.instance_description
1166 FROM
1167 csi_item_instances cii, mtl_system_items_b msib, mtl_parameters mp
1168 WHERE
1169 msib.organization_id = mp.organization_id and
1170 msib.organization_id = cii.last_vld_organization_id and
1171 msib.inventory_item_id = cii.inventory_item_id and
1172 msib.eam_item_type in (1,3) and
1173 msib.serial_number_control_code <> 1 and
1174 sysdate between nvl(cii.active_start_date, sysdate-1)
1175 and nvl(cii.active_end_date, sysdate+1) and
1176 mp.maint_organization_id = :1';
1177
1178
1179 j := 2;
1180 IF (x_asset_group is not NULL) THEN
1181 x_lov_sql := x_lov_sql || ' AND cii.inventory_item_id = :' || to_char(j) || ' ';
1182 j := j+1;
1183 END IF;
1184
1185 IF (x_asset_number is not NULL ) THEN
1186 x_lov_sql := x_lov_sql || ' AND cii.serial_number = :' || to_char(j) || ' ';
1187 END IF;
1188 x_lov_sql := x_lov_sql || 'order by cii.instance_number';
1189
1190 END get_asset_instance_number_lov;
1191
1192 FUNCTION get_asset_inst_num_bind_values (p_org_id IN NUMBER,
1193 p_asset_group IN VARCHAR2, p_asset_number IN VARCHAR2)
1194 RETURN VARCHAR2 IS
1195 l_asset_group_id NUMBER;
1196 l_ret_string VARCHAR2(1000);
1197
1198 BEGIN
1199 l_asset_group_id := qa_plan_element_api.get_asset_group_id(p_org_id, p_asset_group);
1200 l_ret_string := to_char(p_org_id);
1201 --RETURN to_char(p_org_id) || g_bind_value_list_seperator || to_char(l_asset_group_id) ||
1202 -- g_bind_value_list_seperator || p_asset_number;
1203
1204 IF l_asset_group_id is not NULL THEN
1205 l_ret_string := l_ret_string||g_bind_value_list_seperator||to_char(l_asset_group_id);
1206 END IF;
1207
1208 IF p_asset_number is not NULL THEN
1209 l_ret_string := l_ret_string||g_bind_value_list_seperator||to_char(p_asset_number);
1210 END IF;
1211
1212 RETURN l_ret_string;
1213 END get_asset_inst_num_bind_values;
1214 -- Bug 3228490 ksoh Fri Oct 31 11:38:21 PST 2003
1215 -- check if any dependent element value is null
1216 -- if so, put error message with element prompts
1217 -- requires plan_id to be passed in to retrieve element prompts.
1218 -- old signature calls new signature with plan_id = NULL to
1219 -- maintain old behavior
1220 PROCEDURE get_asset_number_lov (plan_id IN NUMBER, x_org_id IN NUMBER, x_asset_group IN VARCHAR2,
1221 value IN VARCHAR2, x_lov_sql OUT NOCOPY VARCHAR2) IS
1222
1223 BEGIN
1224 IF ((plan_id is not null) AND (x_asset_group is null)) THEN
1225 fnd_message.set_name('QA', 'QA_SSQR_DEPENDENT_LOV');
1226 fnd_message.set_token('DEPELEM',
1227 qa_plan_element_api.get_prompt(plan_id, qa_ss_const.asset_group));
1228 fnd_msg_pub.add();
1229 END IF;
1230
1231 --dgupta: Start R12 EAM Integration. Bug 4345492
1232 x_lov_sql := 'SELECT
1233 distinct msn.serial_number, msn.descriptive_text
1234 FROM
1235 mtl_serial_numbers msn, csi_item_instances cii, mtl_system_items_b msib, mtl_parameters mp
1236 WHERE
1237 msib.organization_id = mp.organization_id and
1238 msib.organization_id = cii.last_vld_organization_id and
1239 msib.inventory_item_id = cii.inventory_item_id and
1240 msib.eam_item_type in (1,3) and
1241 sysdate between nvl(cii.active_start_date(+), sysdate-1)
1242 and nvl(cii.active_end_date(+), sysdate+1) and
1243 msib.organization_id = msn.current_organization_id and
1244 cii.serial_number=msn.serial_number and
1245 msib.inventory_item_id = msn.inventory_item_id and
1246 mp.maint_organization_id = :1 and
1247 msn.inventory_item_id = :2 and --removed nvl: serial number requires asset group as well
1248 cii.instance_id= nvl(:3, cii.instance_id)
1249 order by msn.serial_number';
1250 --dgupta: End R12 EAM Integration. Bug 4345492
1251
1252 END get_asset_number_lov;
1253
1254
1255 --dgupta: Start R12 EAM Integration. Bug 4345492
1256 FUNCTION get_asset_number_bind_values (p_org_id IN NUMBER,
1257 p_asset_group IN VARCHAR2, p_asset_instance_number IN VARCHAR2)
1258 RETURN VARCHAR2 IS
1259 l_asset_group_id NUMBER;
1260 l_asset_instance_id NUMBER;
1261
1262 BEGIN
1263 l_asset_group_id := qa_plan_element_api.get_asset_group_id(p_org_id, p_asset_group);
1264 l_asset_instance_id := qa_plan_element_api.get_asset_instance_id(p_asset_instance_number);
1265
1266 RETURN to_char(p_org_id) || g_bind_value_list_seperator || to_char(l_asset_group_id) ||
1267 g_bind_value_list_seperator || to_char(l_asset_instance_id);
1268
1269 END get_asset_number_bind_values;
1270 --dgupta: End R12 EAM Integration. Bug 4345492
1271
1272
1273 PROCEDURE get_asset_number_lov (x_org_id IN NUMBER, x_asset_group IN VARCHAR2,
1274 value IN VARCHAR2, x_lov_sql OUT NOCOPY VARCHAR2) IS
1275 BEGIN
1276 get_asset_number_lov (NULL, x_org_id, x_asset_group, value, x_lov_sql);
1277 END get_asset_number_lov;
1278
1279 --
1280 -- Removed the DEFAULT clause to make the code GSCC compliant
1281 -- List of changed arguments.
1282 -- Old
1283 -- production_line IN VARCHAR2 DEFAULT NULL
1284 -- New
1285 -- production_line IN VARCHAR2
1286 --
1287
1288 -- Bug 3228490 ksoh Fri Oct 31 11:38:21 PST 2003
1289 -- check if any dependent element value is null
1290 -- if so, put error message with element prompts
1291 -- requires plan_id to be passed in to retrieve element prompts.
1292 -- old signature calls new signature with plan_id = NULL to
1293 -- maintain old behavior
1294 PROCEDURE get_op_seq_number_lov (plan_id IN NUMBER, org_id IN NUMBER, value IN VARCHAR2,
1295 job_name IN VARCHAR2, production_line IN VARCHAR2,
1296 x_lov_sql OUT NOCOPY VARCHAR2) IS
1297
1298 x_line_id NUMBER DEFAULT NULL;
1299 -- x_wip_entity_id NUMBER DEFAULT NULL;
1300
1301 BEGIN
1302
1303 -- anagarwa Sun May 2 11:15:38 PDT 2004
1304 -- Bug 3574820 If production line is not present then we cannot save op seq
1305 -- number. This is not consistent with forms behaviour where for
1306 -- to/from op seq number only job is the required field. Production line is
1307 -- used only if present. Hence changing the following if condition to look
1308 -- for job only.
1309 IF (plan_id is not null) AND (job_name is null) THEN
1310 fnd_message.set_name('QA', 'QA_SSQR_DEPENDENT_LOV');
1311 fnd_message.set_token('DEPELEM',
1312 qa_plan_element_api.get_prompt(plan_id, qa_ss_const.job_name));
1313 fnd_msg_pub.add();
1314 END IF;
1315
1316 IF (production_line IS NOT NULL) THEN
1317 x_line_id := qa_plan_element_api.get_production_line_id(org_id,
1318 production_line);
1319 END IF;
1320
1321 -- x_wip_entity_id := qa_plan_element_api.get_job_id(org_id, job_name);
1322
1323 IF (x_line_id IS NULL) THEN
1324
1325 -- anagarwa Thu Jan 16 19:02:01 PST 2003
1326 -- Bug 2751198
1327 -- to_char added as first col for lov is supposed to be a varchar
1328 x_lov_sql := 'SELECT to_char(operation_seq_num), operation_code
1329 FROM wip_operations_all_v
1330 WHERE wip_entity_id = :1
1331 AND organization_id = :2
1332 ORDER BY operation_seq_num';
1333
1334 ELSE
1335
1336 -- anagarwa Thu Jan 16 19:02:01 PST 2003
1337 -- Bug 2751198
1338 -- to_char added as first col for lov is supposed to be a varchar
1339 x_lov_sql := 'SELECT to_char(operation_seq_num), operation_code
1340 FROM wip_operations_all_v
1341 WHERE wip_entity_id = :1
1342 AND organization_id = :2
1343 AND repetitive_schedule_id =
1344 (
1345 SELECT repetitive_schedule_id
1346 FROM wip_first_open_schedule_v
1347 WHERE line_id = :3
1348 AND wip_entity_id = :4
1349 AND organization_id = :5
1350 )
1351 ORDER BY operation_seq_num';
1352
1353 END IF;
1354
1355 END get_op_seq_number_lov;
1356
1357 FUNCTION get_op_seq_no_bind_values (p_plan_id IN NUMBER,
1358 p_org_id IN NUMBER,
1359 p_job_name IN VARCHAR2,
1360 p_production_line IN VARCHAR2)
1361 RETURN VARCHAR2 IS
1362
1363 l_line_id NUMBER;
1364 l_wip_entity_id NUMBER;
1365
1366 BEGIN
1367
1368 l_line_id := NULL;
1369
1370 IF (p_production_line IS NOT NULL OR p_production_line <> '') THEN
1371 l_line_id := qa_plan_element_api.get_production_line_id(p_org_id,
1372 p_production_line);
1373 END IF;
1374
1375 l_wip_entity_id := qa_plan_element_api.get_job_id(p_org_id,p_job_name);
1376
1377 IF (l_line_id IS NULL) THEN
1378
1379 RETURN to_char(l_wip_entity_id) || g_bind_value_list_seperator || to_char(p_org_id);
1380
1381 ELSE
1382
1383 RETURN to_char(l_wip_entity_id) || g_bind_value_list_seperator || to_char(p_org_id) ||
1384 g_bind_value_list_seperator || to_char(l_line_id) ||
1385 g_bind_value_list_seperator || to_char(l_wip_entity_id) ||
1386 g_bind_value_list_seperator || to_char(p_org_id);
1387
1388 END IF;
1389
1390 END get_op_seq_no_bind_values;
1391
1392 PROCEDURE get_op_seq_number_lov (org_id IN NUMBER, value IN VARCHAR2,
1393 job_name IN VARCHAR2, production_line IN VARCHAR2,
1394 x_lov_sql OUT NOCOPY VARCHAR2) IS
1395 BEGIN
1396 get_op_seq_number_lov (NULL, org_id, value, job_name, production_line, x_lov_sql);
1397 END get_op_seq_number_lov;
1398 --
1399 -- MOAC Project. 4637896
1400 -- Now we are passing po header id directly.
1401 -- Corresponding change in dependent lov evaluation.
1402 -- srhariha. Tue Oct 11 04:22:16 PDT 2005.
1403 --
1404
1405 -- Bug 3228490 ksoh Fri Oct 31 11:38:21 PST 2003
1406 -- check if any dependent element value is null
1407 -- if so, put error message with element prompts
1408 -- requires plan_id to be passed in to retrieve element prompts.
1409 -- old signature calls new signature with plan_id = NULL to
1410 -- maintain old behavior
1411 PROCEDURE get_po_line_number_lov (plan_id IN NUMBER, po_header_id IN VARCHAR2, value IN
1412 VARCHAR2, x_lov_sql OUT NOCOPY VARCHAR2) IS
1413
1414 -- po_number_id NUMBER;
1415
1416 BEGIN
1417 -- MOAC. Just changed the name becuase we are not using po_header_id
1418 -- in this method except for this dependency check.
1419 IF ((plan_id is not null) AND (po_header_id is null)) THEN
1420 fnd_message.set_name('QA', 'QA_SSQR_DEPENDENT_LOV');
1421 fnd_message.set_token('DEPELEM',
1422 qa_plan_element_api.get_prompt(plan_id, qa_ss_const.po_number));
1423 fnd_msg_pub.add();
1424 END IF;
1425
1426 -- po_number_id := qa_plan_element_api.get_po_number_id(po_number);
1427
1428 -- Bug 3215866 ksoh Wed Oct 29 16:21:11 PST 2003
1429 -- fixed sql to make it return value instead of id.
1430 x_lov_sql := 'SELECT to_char(line_num), concatenated_segments
1431 FROM po_lines_val_v
1432 WHERE po_header_id = :1
1433 ORDER BY line_num';
1434
1435 END get_po_line_number_lov;
1436
1437
1438 FUNCTION get_po_line_no_bind_values (p_po_header_id IN VARCHAR2)
1439 RETURN VARCHAR2 IS
1440
1441 BEGIN
1442
1443 -- l_po_number_id := qa_plan_element_api.get_po_number_id(p_po_number);
1444 RETURN p_po_header_id;
1445
1446 END get_po_line_no_bind_values;
1447
1448
1449 PROCEDURE get_po_line_number_lov (po_number IN VARCHAR2, value IN
1450 VARCHAR2, x_lov_sql OUT NOCOPY VARCHAR2) IS
1451 BEGIN
1452 get_po_line_number_lov (NULL, po_number, value, x_lov_sql);
1453 END get_po_line_number_lov;
1454
1455
1456 -- Bug 3228490 ksoh Fri Oct 31 11:38:21 PST 2003
1457 -- check if any dependent element value is null
1458 -- if so, put error message with element prompts
1459 -- requires plan_id to be passed in to retrieve element prompts.
1460 -- old signature calls new signature with plan_id = NULL to
1461 -- maintain old behavior
1462 PROCEDURE get_po_shipments_lov (plan_id IN NUMBER, po_line_num IN NUMBER, po_header_id IN VARCHAR2,
1463 value IN VARCHAR2, x_lov_sql OUT NOCOPY VARCHAR2) IS
1464
1465 -- po_number_id NUMBER;
1466
1467 BEGIN
1468 IF ((plan_id is not null) AND ((po_header_id is null) OR (po_line_num is null))) THEN
1469 fnd_message.set_name('QA', 'QA_SSQR_DEPENDENT_LOV');
1470 fnd_message.set_token('DEPELEM',
1471 qa_plan_element_api.get_prompt(plan_id, qa_ss_const.po_number) ||
1472 ', ' || qa_plan_element_api.get_prompt(plan_id, qa_ss_const.po_line_num));
1473 fnd_msg_pub.add();
1474 END IF;
1475
1476 -- po_number_id := qa_plan_element_api.get_po_number_id(po_number);
1477
1478 -- Bug 3215866 ksoh Wed Oct 29 16:21:11 PST 2003
1479 -- added to_char to lov sql to make it compatible to
1480 -- char column when it is union with the kludge sql: select '1'.....
1481
1482 -- Bug 5003511. SQL Repository Fix SQL ID: 15008892
1483 /*
1484 x_lov_sql := 'SELECT to_char(shipment_num), shipment_type
1485 FROM po_shipments_all_v
1486 WHERE po_line_id =
1487 (SELECT po_line_id
1488 FROM po_lines_val_v
1489 WHERE line_num = :1
1490 AND po_header_id = :2)';
1491 */
1492
1493 x_lov_sql := 'SELECT to_char(pll.shipment_num), pll.shipment_type
1494 FROM po_line_locations pll
1495 WHERE pll.ship_to_location_id is not null
1496 AND pll.po_line_id =
1497 (SELECT po_line_id
1498 FROM po_lines
1499 WHERE line_num = :1
1500 AND po_header_id= :2 )';
1501
1502
1503 END get_po_shipments_lov;
1504
1505 FUNCTION get_po_shipments_bind_values (p_po_line_num IN NUMBER,
1506 p_po_header_id IN VARCHAR2)
1507 RETURN VARCHAR2 IS
1508
1509
1510 BEGIN
1511
1512 -- l_po_number_id := qa_plan_element_api.get_po_number_id(p_po_number);
1513
1514 RETURN to_char(p_po_line_num) || g_bind_value_list_seperator || p_po_header_id;
1515
1516 END get_po_shipments_bind_values;
1517
1518 PROCEDURE get_po_shipments_lov (po_line_num IN NUMBER, po_number IN VARCHAR2,
1519 value IN VARCHAR2, x_lov_sql OUT NOCOPY VARCHAR2) IS
1520
1521 -- po_number_id NUMBER;
1522
1523 BEGIN
1524 get_po_shipments_lov (NULL, po_line_num, po_number, value, x_lov_sql);
1525 END get_po_shipments_lov;
1526
1527
1528 PROCEDURE get_receipt_num_lov (value IN VARCHAR2, x_lov_sql OUT NOCOPY VARCHAR2) IS
1529
1530 BEGIN
1531
1532 -- Bug 7491455.FP For bug 6800960
1533 -- changing the validation query for receipt number to include RMA receipts
1534 -- pdube Fri Oct 17 00:14:28 PDT 2008
1535 -- x_lov_sql := 'SELECT receipt_num, vendor_name
1536 -- FROM rcv_receipts_all_v
1537 -- ORDER BY receipt_num';
1538 x_lov_sql := 'SELECT receipt_num, vendor_name
1539 FROM ( SELECT DISTINCT RCVSH.RECEIPT_NUM,
1540 POV.VENDOR_NAME
1541 FROM RCV_SHIPMENT_HEADERS RCVSH,
1542 PO_VENDORS POV,
1543 RCV_TRANSACTIONS RT
1544 WHERE RCVSH.RECEIPT_SOURCE_CODE in (''VENDOR'',''CUSTOMER'') AND
1545 RCVSH.VENDOR_ID = POV.VENDOR_ID(+) AND
1546 RT.SHIPMENT_HEADER_ID = RCVSH.SHIPMENT_HEADER_ID)
1547 ORDER BY receipt_num';
1548
1549 END get_receipt_num_lov;
1550
1551
1552 PROCEDURE get_item_lov (org_id IN NUMBER, value IN VARCHAR2,
1553 x_lov_sql OUT NOCOPY VARCHAR2) IS
1554
1555 BEGIN
1556
1557 x_lov_sql := 'SELECT concatenated_segments, description
1558 FROM mtl_system_items_kfv
1559 WHERE organization_id = :1
1560 ORDER BY concatenated_segments';
1561
1562 END get_item_lov;
1563
1564
1565
1566 -- rkaza. 12/15/2003. bug 3280307. Added lov for comp item
1567 PROCEDURE get_comp_item_lov (plan_id IN NUMBER, x_org_id IN NUMBER,
1568 p_item IN VARCHAR2, value IN VARCHAR2,
1569 x_lov_sql OUT NOCOPY VARCHAR2) IS
1570
1571 -- l_item_id NUMBER DEFAULT NULL;
1572 BEGIN
1573
1574 IF (p_item is null) THEN
1575 fnd_message.set_name('QA', 'QA_SSQR_DEPENDENT_LOV');
1576 fnd_message.set_token('DEPELEM',
1577 qa_plan_element_api.get_prompt(plan_id, qa_ss_const.item));
1578 fnd_msg_pub.add();
1579 END IF;
1580
1581 IF (p_item is not null) THEN
1582
1583 -- l_item_id := qa_flex_util.get_item_id(x_org_id, p_item);
1584
1585 x_lov_sql := 'SELECT concatenated_segments, description
1586 FROM mtl_system_items_kfv
1587 WHERE organization_id = :1 ' ||
1588 ' and inventory_item_id in (
1589 SELECT bic.component_item_id
1590 FROM bom_inventory_components bic,
1591 bom_bill_of_materials bom
1592 WHERE bic.bill_sequence_id = bom.bill_sequence_id AND
1593 bic.effectivity_date <= sysdate AND
1594 nvl(bic.disable_date, sysdate+1) > sysdate AND
1595 bom.assembly_item_id = :2 AND
1596 bom.organization_id = :3)
1597 ORDER BY concatenated_segments';
1598 else
1599
1600 -- show an empty list if parent item is not passed
1601 x_lov_sql := 'SELECT concatenated_segments, description
1602 FROM mtl_system_items_kfv
1603 WHERE 1 = 2
1604 ORDER BY concatenated_segments';
1605
1606 end if;
1607
1608 END get_comp_item_lov;
1609
1610 FUNCTION get_comp_item_bind_values (p_org_id IN NUMBER,
1611 p_item IN VARCHAR2)
1612 RETURN VARCHAR2 IS
1613
1614 l_item_id NUMBER;
1615 BEGIN
1616
1617
1618 IF (p_item is null) THEN
1619 RETURN NULL;
1620 end if;
1621
1622 l_item_id := qa_flex_util.get_item_id(p_org_id, p_item);
1623 RETURN to_char(p_org_id) || g_bind_value_list_seperator ||
1624 to_char(l_item_id) || g_bind_value_list_seperator ||
1625 to_char(p_org_id);
1626
1627 END get_comp_item_bind_values;
1628
1629
1630 PROCEDURE get_asset_group_lov (x_org_id IN NUMBER, value IN VARCHAR2,
1631 x_lov_sql OUT NOCOPY VARCHAR2) IS
1632
1633 BEGIN
1634
1635 --dgupta: Start R12 EAM Integration. Bug 4345492
1636 x_lov_sql := 'select distinct msikfv.concatenated_segments, msikfv.description
1637 from mtl_system_items_b_kfv msikfv, mtl_parameters mp
1638 where msikfv.organization_id = mp.organization_id
1639 and msikfv.eam_item_type in (1,3)
1640 and mp.maint_organization_id = :1
1641 order by msikfv.concatenated_segments';
1642 --dgupta: End R12 EAM Integration. Bug 43454922
1643
1644
1645 END get_asset_group_lov;
1646
1647
1648 -- Bug 3228490 ksoh Fri Oct 31 11:38:21 PST 2003
1649 -- check if any dependent element value is null
1650 -- if so, put error message with element prompts
1651 -- requires plan_id to be passed in to retrieve element prompts.
1652 -- old signature calls new signature with plan_id = NULL to
1653 -- maintain old behavior
1654 --dgupta: Start R12 EAM Integration. Bug 4345492
1655 PROCEDURE get_asset_activity_lov (plan_id IN NUMBER, x_org_id IN NUMBER, p_asset_group IN VARCHAR2,
1656 p_asset_number IN VARCHAR2, p_asset_instance_number IN VARCHAR2, value IN VARCHAR2,
1657 x_lov_sql OUT NOCOPY VARCHAR2) IS
1658
1659 BEGIN
1660 -- rkaza. 12/02/2003. bug 3215372. Asset number can be null.
1661 -- Only Asset group is needed.
1662 -- Dependency on Asset Number is not a must. So removed it from the check.
1663 -- dgupta: Added that either asset group or asset instance number be present
1664 IF ((plan_id is not null) AND (p_asset_group is null)
1665 AND (p_asset_instance_number is null)) THEN
1666 fnd_message.set_name('QA', 'QA_SSQR_DEPENDENT_LOV');
1667 fnd_message.set_token('DEPELEM',
1668 qa_plan_element_api.get_prompt(plan_id, qa_ss_const.asset_group));
1669 fnd_msg_pub.add();
1670 END IF;
1671
1672 if (p_asset_number is null and p_asset_instance_number is null) then
1673 -- show all activities asssociated to the asset group
1674 -- If no match found or if asset group passed in is null, lov is empty
1675 /*
1676 x_lov_sql := 'SELECT meaav.activity, meaav.activity_description
1677 FROM mtl_eam_asset_activities_v meaav, mtl_system_items_b msib
1678 WHERE msib.organization_id = :1
1679 and meaav. maintenance_object_id = :2 --pass asset group inventory_item_id
1680 and (meaav.end_date_active is null or meaav.end_date_active > sysdate)
1681 and (meaav.start_date_active is null or meaav.start_date_active < sysdate)
1682 and msib.inventory_item_id = meaav. maintenance_object_id
1683 and meaav.maintenance_object_type = 2 --non serialized item
1684 ORDER BY meaav.activity';
1685 */
1686 -- Bug 5003511. SQL Repository Fix SQL ID: 15008957
1687 x_lov_sql := 'SELECT
1688 msib.concatenated_segments activity ,
1689 msib.description activity_description
1690 FROM mtl_eam_asset_activities meaav,
1691 mtl_system_items_b_kfv msib
1692 WHERE msib.organization_id = :1
1693 AND meaav.maintenance_object_id = :2 --pass asset group inventory_item_id
1694 AND (meaav.end_date_active is null
1695 OR meaav.end_date_active > sysdate)
1696 AND (meaav.start_date_active is null
1697 OR meaav.start_date_active < sysdate)
1698 AND msib.inventory_item_id = meaav.asset_activity_id
1699 AND meaav.maintenance_object_type = 2 --non serialized item
1700 ORDER BY msib.concatenated_segments';
1701 else
1702 -- show all activities associated to asset group and asset number
1703 -- if exact match not found, lov is empty.
1704 /*
1705 x_lov_sql := 'SELECT meaav.activity, meaav.activity_description
1706 FROM mtl_eam_asset_activities_v meaav, mtl_system_items_b msib
1707 WHERE msib.organization_id = :1
1708 and meaav.maintenance_object_id = :2 --pass asset instance_id
1709 and meaav.maintenance_object_type = 3 --serialized item
1710 and (meaav.end_date_active is null or meaav.end_date_active > sysdate)
1711 and (meaav.start_date_active is null or meaav.start_date_active < sysdate)
1712 and msib.inventory_item_id = meaav.inventory_item_id
1713 ORDER BY meaav.activity';
1714 */
1715 -- Bug 5003511. SQL Repository Fix SQL ID: 15008986
1716 x_lov_sql := 'SELECT
1717 msi.concatenated_segments activity ,
1718 msi.description activity_description
1719 FROM mtl_eam_asset_activities meaa,
1720 mtl_system_items_b_kfv msi
1721 WHERE msi.organization_id = :1
1722 AND meaa.maintenance_object_id = :2 --pass asset instance_id
1723 AND meaa.maintenance_object_type = 3 --serialized item
1724 AND (meaa.end_date_active is null
1725 OR meaa.end_date_active > sysdate)
1726 AND (meaa.start_date_active is null
1727 OR meaa.start_date_active < sysdate)
1728 AND msi.inventory_item_id = meaa.asset_activity_id
1729 ORDER BY msi.concatenated_segments';
1730
1731 end if;
1732
1733 END get_asset_activity_lov;
1734
1735
1736 FUNCTION get_asset_activity_bind_values (p_org_id IN NUMBER,
1737 p_asset_group IN VARCHAR2,
1738 p_asset_number IN VARCHAR2, p_asset_instance_number IN VARCHAR2)
1739 RETURN VARCHAR2 IS
1740
1741 l_asset_group_id NUMBER DEFAULT NULL;
1742 l_asset_instance_id NUMBER DEFAULT NULL;
1743
1744 BEGIN
1745 if (p_asset_number is null and p_asset_instance_number is null) then
1746 l_asset_group_id := qa_plan_element_api.get_asset_group_id(p_org_id, p_asset_group);
1747 RETURN to_char(p_org_id) || g_bind_value_list_seperator || to_char(l_asset_group_id);
1748 else
1749 l_asset_instance_id := qa_plan_element_api.get_asset_instance_id(p_asset_instance_number);
1750 if (l_asset_instance_id is null) then
1751 l_asset_instance_id := qa_plan_element_api.get_asset_instance_id(l_asset_group_id, p_asset_number);
1752 end if;
1753 RETURN to_char(p_org_id) || g_bind_value_list_seperator || to_char(l_asset_instance_id);
1754 end if;
1755 END get_asset_activity_bind_values;
1756
1757
1758 PROCEDURE get_asset_activity_lov (x_org_id IN NUMBER, p_asset_group IN VARCHAR2,
1759 p_asset_number IN VARCHAR2, p_asset_instance_number IN VARCHAR2, value IN VARCHAR2,
1760 x_lov_sql OUT NOCOPY VARCHAR2) IS
1761 BEGIN
1762 get_asset_activity_lov (NULL, x_org_id, p_asset_group, p_asset_number,
1763 p_asset_instance_number, value, x_lov_sql);
1764 END get_asset_activity_lov;
1765
1766 -- added the following to include new hardcoded element followup activity
1767 -- saugupta
1768 PROCEDURE get_followup_activity_lov (plan_id IN NUMBER, x_org_id IN NUMBER, p_asset_group IN VARCHAR2,
1769 p_asset_number IN VARCHAR2, p_asset_instance_number IN VARCHAR2, value IN VARCHAR2,
1770 x_lov_sql OUT NOCOPY VARCHAR2) IS
1771 BEGIN
1772 get_asset_activity_lov (plan_id, x_org_id, p_asset_group, p_asset_number,
1773 p_asset_instance_number, value, x_lov_sql); --no use duplicating code
1774
1775 END get_followup_activity_lov;
1776
1777 FUNCTION get_followup_act_bind_values (p_org_id IN NUMBER,
1778 p_asset_group IN VARCHAR2,
1779 p_asset_number IN VARCHAR2, p_asset_instance_number IN VARCHAR2)
1780 RETURN VARCHAR2 IS
1781
1782 BEGIN
1783 RETURN get_asset_activity_bind_values(p_org_id, p_asset_group,
1784 p_asset_number, p_asset_instance_number); -- same as asset activity lov, dont duplicate
1785 END get_followup_act_bind_values;
1786
1787 PROCEDURE get_followup_activity_lov (x_org_id IN NUMBER, p_asset_group IN VARCHAR2,
1788 p_asset_number IN VARCHAR2, p_asset_instance_number IN VARCHAR2, value IN VARCHAR2,
1789 x_lov_sql OUT NOCOPY VARCHAR2) IS
1790 BEGIN
1791 get_followup_activity_lov (NULL, x_org_id, p_asset_group,
1792 p_asset_number, p_asset_instance_number, value,
1793 x_lov_sql);
1794 END get_followup_activity_lov;
1795
1796 --dgupta: End R12 EAM Integration. Bug 4345492
1797
1798 PROCEDURE get_xfr_lpn_lov (value IN VARCHAR2, x_lov_sql OUT NOCOPY VARCHAR2) IS
1799 BEGIN
1800
1801 x_lov_sql := 'SELECT license_plate_number, attribute1
1802 FROM wms_license_plate_numbers
1803 ORDER BY license_plate_number';
1804
1805 END get_xfr_lpn_lov;
1806
1807
1808
1809
1810 -- Bug 3228490 ksoh Fri Oct 31 11:38:21 PST 2003
1811 -- check if any dependent element value is null
1812 -- if so, put error message with element prompts
1813 -- requires plan_id to be passed in to retrieve element prompts.
1814 -- old signature calls new signature with plan_id = NULL to
1815 -- maintain old behavior
1816
1817 -- anagarwa Thu May 13 14:56:49 PDT 2004
1818 -- Bug 3625998 Locator should be restricted by subinventory
1819 -- Earlier, we were taking in item and not using it. I have changed the
1820 -- variable name to x_subinventory and used it in the lov sql
1821 PROCEDURE get_locator_lov (plan_id IN NUMBER, org_id IN NUMBER,
1822 x_subinventory IN VARCHAR2, value IN VARCHAR2,
1823 x_lov_sql OUT NOCOPY VARCHAR2) IS
1824
1825 BEGIN
1826 IF ((plan_id is not null) AND (x_subinventory is null)) THEN
1827 fnd_message.set_name('QA', 'QA_SSQR_DEPENDENT_LOV');
1828 fnd_message.set_token('DEPELEM',
1829 qa_plan_element_api.get_prompt(plan_id, qa_ss_const.subinventory));
1830 fnd_msg_pub.add();
1831 END IF;
1832
1833 -- anagarwa Thu May 13 14:56:49 PDT 2004
1834 -- Bug 3625998: Added subinventory and disable_date to restrict the lov
1835 x_lov_sql := 'SELECT concatenated_segments, description
1836 FROM mtl_item_locations_kfv
1837 WHERE organization_id = :1
1838 AND subinventory_code = :2
1839 AND nvl(disable_date, sysdate+1) > sysdate
1840 ORDER BY concatenated_segments';
1841
1842 END get_locator_lov;
1843
1844
1845 FUNCTION get_locator_bind_values (p_org_id IN NUMBER,
1846 p_subinventory IN VARCHAR2)
1847 RETURN VARCHAR2 IS
1848
1849 BEGIN
1850
1851 RETURN to_char(p_org_id) || g_bind_value_list_seperator || p_subinventory;
1852
1853 END get_locator_bind_values;
1854
1855 PROCEDURE get_locator_lov (org_id IN NUMBER, x_item_name IN VARCHAR2,
1856 value IN VARCHAR2, x_lov_sql OUT NOCOPY VARCHAR2) IS
1857
1858 BEGIN
1859 get_locator_lov (NULL, org_id, x_item_name, value, x_lov_sql);
1860 END get_locator_lov;
1861
1862
1863 PROCEDURE get_party_lov (value IN VARCHAR2, x_lov_sql OUT NOCOPY VARCHAR2) IS
1864
1865 BEGIN
1866
1867 x_lov_sql := 'SELECT party_name, party_number
1868 FROM hz_parties
1869 WHERE status = ''A''
1870 AND party_type IN (''ORGANIZATION'',''PERSON'')
1871 ORDER BY party_name';
1872
1873 END get_party_lov;
1874
1875 --
1876 -- Implemented the following six get_lov procedures for
1877 -- Service_Item, Counter, Maintenance_Requirement, Service_Request, Rework_Job
1878 -- For ASO project
1879 -- rkunchal Thu Aug 1 12:04:56 PDT 2002
1880 --
1881
1882 PROCEDURE get_item_instance_lov (value IN VARCHAR2, x_lov_sql OUT NOCOPY VARCHAR2) IS
1883
1884 BEGIN
1885
1886 x_lov_sql := 'SELECT cii.instance_number, cii.serial_number
1887 FROM qa_csi_item_instances cii, mtl_system_items_kfv msik
1888 WHERE cii.inventory_item_id = msik.inventory_item_id
1889 AND cii.last_vld_organization_id = msik.organization_id
1890 ORDER BY 1';
1891
1892 END get_item_instance_lov;
1893
1894
1895 PROCEDURE get_counter_name_lov (value IN VARCHAR2, x_lov_sql OUT NOCOPY VARCHAR2) IS
1896
1897 BEGIN
1898
1899 -- Bug 5003511. SQL Repository Fix SQL ID: 15009044
1900 x_lov_sql := 'SELECT name, description
1901 FROM csi_counters_vl
1902 WHERE trunc(sysdate) BETWEEN
1903 nvl(start_date_active, trunc(sysdate))
1904 AND nvl(end_date_active, trunc(sysdate))
1905 ORDER BY 1';
1906 /*
1907 x_lov_sql := 'SELECT cc.name, cc.description
1908 FROM cs_counters cc, cs_counter_groups ccg
1909 WHERE cc.counter_group_id = ccg.counter_group_id
1910 AND ccg.template_flag = ''N''
1911 ORDER BY 1';
1912 */
1913
1914 END get_counter_name_lov;
1915
1916
1917 PROCEDURE get_maintenance_req_lov (value IN VARCHAR2, x_lov_sql OUT NOCOPY VARCHAR2) IS
1918
1919 BEGIN
1920
1921 x_lov_sql := 'SELECT amr.title, amr.version_number
1922 FROM qa_ahl_mr amr
1923 WHERE trunc(sysdate) BETWEEN
1924 trunc(nvl(amr.effective_from, sysdate)) AND trunc(nvl(amr.effective_to, sysdate))
1925 ORDER BY 1';
1926
1927 END get_maintenance_req_lov;
1928
1929
1930 PROCEDURE get_service_request_lov (value IN VARCHAR2, x_lov_sql OUT NOCOPY VARCHAR2) IS
1931
1932 BEGIN
1933
1934 x_lov_sql := 'SELECT incident_number, summary
1935 FROM cs_incidents
1936 ORDER BY 1';
1937
1938 END get_service_request_lov;
1939
1940
1941 PROCEDURE get_rework_job_lov (org_id IN NUMBER, value IN VARCHAR2, x_lov_sql OUT NOCOPY VARCHAR2) IS
1942
1943 BEGIN
1944
1945 x_lov_sql:= 'SELECT wip_entity_name, description
1946 FROM wip_discrete_jobs_all_v
1947 WHERE organization_id = :1
1948 ORDER BY wip_entity_name';
1949
1950 END get_rework_job_lov;
1951
1952 PROCEDURE get_disposition_source_lov(value IN VARCHAR2, x_lov_sql OUT NOCOPY VARCHAR2) IS
1953
1954 BEGIN
1955
1956 x_lov_sql := 'SELECT v.short_code code,
1957 v.description
1958 FROM qa_char_value_lookups v
1959 WHERE v.char_id = :1
1960 ORDER BY 1';
1961
1962 END get_disposition_source_lov;
1963
1964 PROCEDURE get_disposition_action_lov(value IN VARCHAR2, x_lov_sql OUT NOCOPY VARCHAR2) IS
1965
1966 BEGIN
1967
1968 x_lov_sql := 'SELECT v.short_code code,
1969 v.description
1970 FROM qa_char_value_lookups v
1971 WHERE v.char_id = :1
1972 ORDER BY 1';
1973
1974 END get_disposition_action_lov;
1975
1976 PROCEDURE get_disposition_lov(value IN VARCHAR2, x_lov_sql OUT NOCOPY VARCHAR2) IS
1977
1978 BEGIN
1979
1980 x_lov_sql := 'SELECT v.short_code code,
1981 v.description
1982 FROM qa_char_value_lookups v
1983 WHERE v.char_id = :1
1984 ORDER BY 1';
1985
1986 END get_disposition_lov;
1987
1988 PROCEDURE get_disposition_status_lov(value IN VARCHAR2, x_lov_sql OUT NOCOPY VARCHAR2) IS
1989
1990 BEGIN
1991
1992 x_lov_sql := 'SELECT v.short_code code,
1993 v.description
1994 FROM qa_char_value_lookups v
1995 WHERE v.char_id = :1
1996 ORDER BY 1';
1997
1998 END get_disposition_status_lov;
1999
2000 /* R12 DR Integration. Bug 4345489 Strat */
2001 PROCEDURE get_repair_order_lov(value IN VARCHAR2, x_lov_sql OUT NOCOPY VARCHAR2) IS
2002
2003 BEGIN
2004
2005 x_lov_sql := 'SELECT cr.repair_number,
2006 cr.problem_description
2007 FROM csd_repairs cr
2008 WHERE status not in (''C'', ''H'')
2009 ORDER BY 1';
2010
2011 END get_repair_order_lov;
2012
2013 PROCEDURE get_jtf_task_lov(value IN VARCHAR2, x_lov_sql OUT NOCOPY VARCHAR2) IS
2014
2015 BEGIN
2016
2017 x_lov_sql := 'SELECT task_number, task_name
2018 FROM JTF_TASKS_VL
2019 ORDER BY 1';
2020
2021 END get_jtf_task_lov;
2022 /* R12 DR Integration. Bug 4345489 End */
2023
2024 -- R12 OPM Deviations. Bug 4345503 Start
2025
2026 PROCEDURE get_process_batch_num_lov
2027 (org_id IN NUMBER,
2028 value IN VARCHAR2,
2029 x_lov_sql OUT NOCOPY VARCHAR2) IS
2030 BEGIN
2031 x_lov_sql := 'SELECT BATCH_NO, BATCH_NO BATCH_DESC FROM GME_BATCH_HEADER '||
2032 'WHERE ORGANIZATION_ID is null or ORGANIZATION_ID = :1'||
2033 'ORDER BY BATCH_NO';
2034 END get_process_batch_num_lov;
2035
2036 PROCEDURE get_process_batchstep_num_lov
2037 (org_id IN NUMBER,
2038 plan_id IN NUMBER,
2039 process_batch_num IN VARCHAR2,
2040 value IN VARCHAR2,
2041 x_lov_sql OUT NOCOPY VARCHAR2) IS
2042
2043 l_process_batch_id number;
2044 BEGIN
2045 l_process_batch_id := qa_plan_element_api.get_process_batch_id (process_batch_num, org_id);
2046
2047 IF (l_process_batch_id is null) THEN
2048 fnd_message.set_name('QA', 'QA_SSQR_DEPENDENT_LOV');
2049 fnd_message.set_token('DEPELEM',
2050 qa_plan_element_api.get_prompt(plan_id, qa_ss_const.process_batch_num)
2051 );
2052 fnd_msg_pub.add();
2053 END IF;
2054
2055 x_lov_sql := 'SELECT to_char(STEPS.BATCHSTEP_NO) ,OPS.OPRN_DESC BATCHSTEP_DESC '||
2056 'FROM GME_BATCH_STEPS STEPS, GMD_OPERATIONS OPS '||
2057 'WHERE STEPS.BATCH_ID = :1 ' ||
2058 'AND STEPS.OPRN_ID = OPS.OPRN_ID '||
2059 'ORDER BY BATCHSTEP_NO';
2060
2061 END get_process_batchstep_num_lov;
2062
2063 FUNCTION GET_PROCESS_STEP_BIND_VALUE
2064 (org_id IN NUMBER,
2065 process_batch_num IN VARCHAR2)
2066 RETURN VARCHAR2 IS
2067
2068 l_process_batch_id number;
2069 BEGIN
2070 l_process_batch_id := qa_plan_element_api.get_process_batch_id (process_batch_num, org_id);
2071
2072 IF (l_process_batch_id IS NULL) THEN
2073 RETURN NULL;
2074 END IF;
2075
2076 RETURN to_char(l_process_batch_id);
2077 END GET_PROCESS_STEP_BIND_VALUE;
2078
2079 PROCEDURE get_process_operation_lov
2080 (org_id IN NUMBER,
2081 plan_id IN NUMBER,
2082 process_batch_num IN VARCHAR2,
2083 process_batchstep_num IN VARCHAR2,
2084 value IN VARCHAR2,
2085 x_lov_sql OUT NOCOPY VARCHAR2) IS
2086
2087 l_process_batch_id number;
2088 l_process_batchstep_id number;
2089 BEGIN
2090 L_PROCESS_BATCH_ID := QA_PLAN_ELEMENT_API.GET_PROCESS_BATCH_ID(PROCESS_BATCH_NUM,ORG_ID);
2091
2092 IF (l_process_batch_id is null) THEN
2093 fnd_message.set_name('QA', 'QA_SSQR_DEPENDENT_LOV');
2094 fnd_message.set_token('DEPELEM',
2095 qa_plan_element_api.get_prompt(plan_id, qa_ss_const.process_batch_num)
2096 );
2097 fnd_msg_pub.add();
2098 END IF;
2099
2100 L_PROCESS_BATCHSTEP_ID := QA_PLAN_ELEMENT_API.GET_PROCESS_BATCHSTEP_ID
2101 (process_batchstep_num,L_PROCESS_BATCH_ID);
2102
2103 IF (l_process_batchstep_id is null) THEN
2104 fnd_message.set_name('QA', 'QA_SSQR_DEPENDENT_LOV');
2105 fnd_message.set_token('DEPELEM',
2106 qa_plan_element_api.get_prompt(plan_id, qa_ss_const.process_batchstep_num)
2107 );
2108 fnd_msg_pub.add();
2109 END IF;
2110
2111 x_lov_sql := 'SELECT OPERATION PROCESS_OPERATION, OPRN_DESC '||
2112 'FROM GMO_BATCH_STEPS_V '||
2113 'WHERE BATCHSTEP_ID = :1 '||
2114 ' AND BATCH_ID = :2 '||
2115 ' ORDER BY PROCESS_OPERATION';
2116 END get_process_operation_lov;
2117
2118 FUNCTION GET_PROCESS_OPRN_BIND_VALUE
2119 (org_id IN NUMBER,
2120 process_batch_num IN VARCHAR2,
2121 process_batchstep_num IN VARCHAR2)
2122 RETURN VARCHAR2 IS
2123
2124 l_process_batch_id number;
2125 l_process_batchstep_id number;
2126 BEGIN
2127 l_process_batch_id := qa_plan_element_api.get_process_batch_id (process_batch_num, org_id);
2128 IF (l_process_batch_id IS NULL) THEN
2129 RETURN NULL;
2130 END IF;
2131
2132 l_process_batchstep_id := QA_PLAN_ELEMENT_API.GET_PROCESS_BATCHSTEP_ID
2133 (process_batchstep_num,L_PROCESS_BATCH_ID);
2134 IF (l_process_batchstep_id IS NULL) THEN
2135 RETURN NULL;
2136 END IF;
2137
2138 RETURN to_char(l_process_batchstep_id) ||
2139 g_bind_value_list_seperator ||
2140 to_char(l_process_batch_id);
2141
2142 END GET_PROCESS_OPRN_BIND_VALUE;
2143
2144 PROCEDURE get_process_activity_lov
2145 (org_id IN NUMBER,
2146 plan_id IN NUMBER,
2147 process_batch_num IN VARCHAR2,
2148 process_batchstep_num IN VARCHAR2,
2149 value IN VARCHAR2,
2150 x_lov_sql OUT NOCOPY VARCHAR2) IS
2151
2152 l_process_batch_id number;
2153 l_process_batchstep_id number;
2154 BEGIN
2155 L_PROCESS_BATCH_ID := QA_PLAN_ELEMENT_API.GET_PROCESS_BATCH_ID(PROCESS_BATCH_NUM,ORG_ID);
2156
2157 IF (l_process_batch_id is null) THEN
2158 fnd_message.set_name('QA', 'QA_SSQR_DEPENDENT_LOV');
2159 fnd_message.set_token('DEPELEM',
2160 qa_plan_element_api.get_prompt(plan_id, qa_ss_const.process_batch_num)
2161 );
2162 fnd_msg_pub.add();
2163 END IF;
2164
2165 L_PROCESS_BATCHSTEP_ID := QA_PLAN_ELEMENT_API.GET_PROCESS_BATCHSTEP_ID
2166 (PROCESS_BATCHSTEP_NUM,L_PROCESS_BATCH_ID);
2167
2168 IF (l_process_batchstep_id is null) THEN
2169 fnd_message.set_name('QA', 'QA_SSQR_DEPENDENT_LOV');
2170 fnd_message.set_token('DEPELEM',
2171 qa_plan_element_api.get_prompt(plan_id, qa_ss_const.process_batchstep_num)
2172 );
2173 fnd_msg_pub.add();
2174 END IF;
2175
2176 x_lov_sql := 'SELECT STEPS.ACTIVITY, ACTIVITIES.ACTIVITY_DESC '||
2177 'FROM GME_BATCH_STEP_ACTIVITIES STEPS, GMD_ACTIVITIES ACTIVITIES '||
2178 'WHERE STEPS.BATCHSTEP_ID = :1 '||
2179 ' AND STEPS.BATCH_ID = :2 '||
2180 ' AND STEPS.ACTIVITY = ACTIVITIES.ACTIVITY '||
2181 'ORDER BY ACTIVITY';
2182 END get_process_activity_lov;
2183
2184 FUNCTION GET_PROCESS_ACT_BIND_VALUE
2185 (org_id IN NUMBER,
2186 process_batch_num IN VARCHAR2,
2187 process_batchstep_num IN VARCHAR2)
2188 RETURN VARCHAR2 IS
2189
2190 l_process_batch_id number;
2191 l_process_batchstep_id number;
2192 BEGIN
2193 l_process_batch_id := qa_plan_element_api.get_process_batch_id (process_batch_num, org_id);
2194 IF (l_process_batch_id IS NULL) THEN
2195 RETURN NULL;
2196 END IF;
2197
2198 l_process_batchstep_id := QA_PLAN_ELEMENT_API.GET_PROCESS_BATCHSTEP_ID
2199 (process_batchstep_num,L_PROCESS_BATCH_ID);
2200 IF (l_process_batchstep_id IS NULL) THEN
2201 RETURN NULL;
2202 END IF;
2203
2204 RETURN to_char(l_process_batchstep_id) ||
2205 g_bind_value_list_seperator ||
2206 to_char(l_process_batch_id);
2207
2208 END GET_PROCESS_ACT_BIND_VALUE;
2209
2210 PROCEDURE get_process_resource_lov
2211 (org_id IN NUMBER,
2212 plan_id IN NUMBER,
2213 process_batch_num IN VARCHAR2,
2214 process_batchstep_num IN VARCHAR2,
2215 process_activity IN VARCHAR2,
2216 value IN VARCHAR2,
2217 x_lov_sql OUT NOCOPY VARCHAR2) IS
2218
2219 l_process_batch_id number;
2220 l_process_batchstep_id number;
2221 l_process_activity_id number;
2222 BEGIN
2223 L_PROCESS_BATCH_ID := QA_PLAN_ELEMENT_API.GET_PROCESS_BATCH_ID(PROCESS_BATCH_NUM,ORG_ID);
2224
2225 IF (l_process_batch_id is null) THEN
2226 fnd_message.set_name('QA', 'QA_SSQR_DEPENDENT_LOV');
2227 fnd_message.set_token('DEPELEM',
2228 qa_plan_element_api.get_prompt(plan_id, qa_ss_const.process_batch_num)
2229 );
2230 fnd_msg_pub.add();
2231 END IF;
2232
2233 L_PROCESS_BATCHSTEP_ID := QA_PLAN_ELEMENT_API.GET_PROCESS_BATCHSTEP_ID
2234 (PROCESS_BATCHSTEP_NUM,L_PROCESS_BATCH_ID);
2235
2236 IF (l_process_batchstep_id is null) THEN
2237 fnd_message.set_name('QA', 'QA_SSQR_DEPENDENT_LOV');
2238 fnd_message.set_token('DEPELEM',
2239 qa_plan_element_api.get_prompt(plan_id, qa_ss_const.process_batchstep_num)
2240 );
2241 fnd_msg_pub.add();
2242 END IF;
2243
2244 L_PROCESS_ACTIVITY_ID := QA_PLAN_ELEMENT_API.GET_PROCESS_ACTIVITY_ID
2245 (PROCESS_ACTIVITY,L_PROCESS_BATCH_ID,L_PROCESS_BATCHSTEP_ID);
2246
2247 IF (l_process_activity_id is null) THEN
2248 fnd_message.set_name('QA', 'QA_SSQR_DEPENDENT_LOV');
2249 fnd_message.set_token('DEPELEM',
2250 qa_plan_element_api.get_prompt(plan_id, qa_ss_const.process_activity)
2251 );
2252 fnd_msg_pub.add();
2253 END IF;
2254
2255 x_lov_sql := 'SELECT GBSR.RESOURCES, CRMV.RESOURCE_DESC '||
2256 'FROM GME_BATCH_STEP_RESOURCES GBSR, CR_RSRC_MST_VL CRMV '||
2257 'WHERE GBSR.BATCHSTEP_ACTIVITY_ID = :1 '||
2258 'AND GBSR.BATCHSTEP_ID = :2 '||
2259 'AND GBSR.BATCH_ID = :3 '||
2260 'AND GBSR.RESOURCES = CRMV.RESOURCES '||
2261 'ORDER BY RESOURCES';
2262
2263 END get_process_resource_lov;
2264
2265 FUNCTION GET_PROCESS_RSR_BIND_VALUE
2266 (org_id IN NUMBER,
2267 process_batch_num IN VARCHAR2,
2268 process_batchstep_num IN VARCHAR2,
2269 process_activity IN VARCHAR2)
2270 RETURN VARCHAR2 IS
2271
2272 l_process_batch_id number;
2273 l_process_batchstep_id number;
2274 l_process_activity_id number;
2275 BEGIN
2276 l_process_batch_id := qa_plan_element_api.get_process_batch_id (process_batch_num, org_id);
2277 IF (l_process_batch_id IS NULL) THEN
2278 RETURN NULL;
2279 END IF;
2280
2281 l_process_batchstep_id := QA_PLAN_ELEMENT_API.GET_PROCESS_BATCHSTEP_ID
2282 (process_batchstep_num,L_PROCESS_BATCH_ID);
2283 IF (l_process_batchstep_id IS NULL) THEN
2284 RETURN NULL;
2285 END IF;
2286
2287 L_PROCESS_ACTIVITY_ID := QA_PLAN_ELEMENT_API.GET_PROCESS_ACTIVITY_ID
2288 (PROCESS_ACTIVITY,L_PROCESS_BATCH_ID,L_PROCESS_BATCHSTEP_ID);
2289
2290 IF (l_process_activity_id IS NULL) THEN
2291 RETURN NULL;
2292 END IF;
2293
2294 RETURN to_char(l_process_activity_id) ||
2295 g_bind_value_list_seperator ||
2296 to_char(l_process_batchstep_id) ||
2297 g_bind_value_list_seperator ||
2298 to_char(l_process_batch_id);
2299
2300 END GET_PROCESS_RSR_BIND_VALUE;
2301
2302 PROCEDURE get_process_parameter_lov
2303 (org_id IN NUMBER,
2304 plan_id IN NUMBER,
2305 process_resource IN VARCHAR2,
2306 value IN VARCHAR2,
2307 x_lov_sql OUT NOCOPY VARCHAR2) IS
2308 BEGIN
2309 x_lov_sql := 'SELECT DISTINCT GP.PARAMETER_NAME, GP.PARAMETER_DESCRIPTION '||
2310 'FROM GMP_PROCESS_PARAMETERS GP, GME_PROCESS_PARAMETERS GE '||
2311 'WHERE GP.PARAMETER_ID = GE.PARAMETER_ID '||
2312 'AND GE.RESOURCES = :1'||
2313 ' ORDER BY PARAMETER_NAME';
2314
2315 END get_process_parameter_lov;
2316
2317 FUNCTION GET_PROCESS_PARAM_BIND_VALUE
2318 (org_id IN NUMBER,
2319 process_resource IN VARCHAR2)
2320 RETURN VARCHAR2 IS
2321
2322 BEGIN
2323 IF (process_resource IS NULL) THEN
2324 RETURN NULL;
2325 END IF;
2326
2327 RETURN to_char(process_resource);
2328 END GET_PROCESS_PARAM_BIND_VALUE;
2329 -- R12 OPM Deviations. Bug 4345503 End
2330
2331 --
2332 -- See Bug 2588213
2333 -- To support the element Maintenance Op Seq Number
2334 -- to be used along with Maintenance Workorder
2335 -- rkunchal Mon Sep 23 23:46:28 PDT 2002
2336 --
2337 -- Bug 3228490 ksoh Fri Oct 31 11:38:21 PST 2003
2338 -- check if any dependent element value is null
2339 -- if so, put error message with element prompts
2340 -- requires plan_id to be passed in to retrieve element prompts.
2341 -- old signature calls new signature with plan_id = NULL to
2342 -- maintain old behavior
2343 PROCEDURE get_maintenance_op_seq_lov(plan_id IN NUMBER, org_id IN NUMBER,
2344 value IN VARCHAR2,
2345 maintenance_work_order IN VARCHAR2,
2346 x_lov_sql OUT NOCOPY VARCHAR2) IS
2347
2348 x_wip_entity_id NUMBER DEFAULT NULL;
2349
2350 BEGIN
2351 IF ((plan_id is not null) AND (maintenance_work_order is null)) THEN
2352 fnd_message.set_name('QA', 'QA_SSQR_DEPENDENT_LOV');
2353 fnd_message.set_token('DEPELEM',
2354 qa_plan_element_api.get_prompt(plan_id, qa_ss_const.work_order));
2355 fnd_msg_pub.add();
2356 END IF;
2357 -- rkaza. 10/22/2003. 3209804.
2358 -- operation_seq_num should be made a varchar2 to be compatible with
2359 -- code and description in a dynamic lov.
2360 -- Also lov should not error out if wip_entity_id is null.
2361
2362 x_wip_entity_id := qa_plan_element_api.get_job_id(org_id, maintenance_work_order);
2363
2364 if x_wip_entity_id is not null then
2365 x_lov_sql := 'SELECT to_char(operation_seq_num), operation_code
2366 FROM wip_operations_all_v
2367 WHERE wip_entity_id = :1
2368 AND organization_id = :2
2369 ORDER BY operation_seq_num';
2370 else
2371 -- nothing should be selected.
2372 x_lov_sql := 'SELECT to_char(operation_seq_num), operation_code
2373 FROM wip_operations_all_v
2374 WHERE 1 = 2' || '
2375 ORDER BY operation_seq_num';
2376 end if;
2377
2378 END get_maintenance_op_seq_lov;
2379
2380
2381 FUNCTION get_maint_op_seq_bind_values (p_org_id IN NUMBER,
2382 p_maintenance_work_order IN VARCHAR2)
2383 RETURN VARCHAR2 IS
2384
2385 l_wip_entity_id NUMBER;
2386
2387 BEGIN
2388
2389 l_wip_entity_id := qa_plan_element_api.get_job_id(p_org_id, p_maintenance_work_order);
2390
2391 if l_wip_entity_id is not null then
2392 RETURN to_char(l_wip_entity_id) || g_bind_value_list_seperator || to_char(p_org_id);
2393 end if;
2394
2395 RETURN NULL;
2396
2397 END get_maint_op_seq_bind_values;
2398
2399 PROCEDURE get_maintenance_op_seq_lov(org_id IN NUMBER,
2400 value IN VARCHAR2,
2401 maintenance_work_order IN VARCHAR2,
2402 x_lov_sql OUT NOCOPY VARCHAR2) IS
2403 BEGIN
2404 get_maintenance_op_seq_lov (NULL, org_id,
2405 value, maintenance_work_order, x_lov_sql);
2406 END get_maintenance_op_seq_lov;
2407
2408 --
2409 -- End of inclusions for ASO project
2410 -- rkunchal Thu Aug 1 12:04:56 PDT 2002
2411 --
2412
2413
2414 -- Start of inclusions for NCM Hardcode Elements.
2415 -- suramasw Thu Oct 31 10:48:59 PST 2002.
2416 -- Bug 2449067.
2417
2418 PROCEDURE get_bill_reference_lov (org_id IN NUMBER, value IN VARCHAR2,
2419 x_lov_sql OUT NOCOPY VARCHAR2) IS
2420
2421 BEGIN
2422
2423 x_lov_sql := 'SELECT concatenated_segments, description
2424 FROM mtl_system_items_kfv
2425 WHERE organization_id = :1
2426 ORDER BY concatenated_segments';
2427
2428 END get_bill_reference_lov;
2429
2430 PROCEDURE get_routing_reference_lov (org_id IN NUMBER, value IN VARCHAR2,
2431 x_lov_sql OUT NOCOPY VARCHAR2) IS
2432
2433 BEGIN
2434
2435 x_lov_sql := 'SELECT concatenated_segments, description
2436 FROM mtl_system_items_kfv
2437 WHERE organization_id = :1
2438 ORDER BY concatenated_segments';
2439
2440 END get_routing_reference_lov;
2441
2442 PROCEDURE get_to_subinventory_lov (org_id IN NUMBER, value IN VARCHAR2,
2443 x_lov_sql OUT NOCOPY VARCHAR2) IS
2444
2445 BEGIN
2446
2447 x_lov_sql := 'SELECT secondary_inventory_name, description
2448 FROM mtl_secondary_inventories
2449 WHERE organization_id = :1
2450 AND nvl(disable_date, sysdate+1) > sysdate
2451 ORDER BY secondary_inventory_name';
2452
2453 END get_to_subinventory_lov;
2454
2455 -- Bug 3228490 ksoh Fri Oct 31 11:38:21 PST 2003
2456 -- check if any dependent element value is null
2457 -- if so, put error message with element prompts
2458 -- requires plan_id to be passed in to retrieve element prompts.
2459 -- old signature calls new signature with plan_id = NULL to
2460 -- maintain old behavior
2461
2462 -- anagarwa Thu May 13 14:56:49 PDT 2004
2463 -- Bug 3625998 Locator should be restricted by subinventory
2464 -- Earlier, we were taking in item and not using it. I have changed the
2465 -- variable name to x_subinventory and used it in the lov sql
2466 PROCEDURE get_to_locator_lov (plan_id IN NUMBER, org_id IN NUMBER,
2467 x_subinventory IN VARCHAR2, value IN VARCHAR2,
2468 x_lov_sql OUT NOCOPY VARCHAR2) IS
2469
2470 BEGIN
2471
2472 -- anagarwa Thu May 13 14:56:49 PDT 2004
2473 -- Bug 3625998 Locator should be restricted by subinventory
2474 -- We are doing exactly the same thing as in get_locator_lov.
2475 -- I do not see the reason to maintain 2 occurrences of same code.
2476 -- If we need to change the this lov for some reason, then we can simply
2477 -- comment out my changes and write new logic here.
2478 get_locator_lov(plan_id, org_id, x_subinventory, value, x_lov_sql);
2479 /*
2480 IF ((plan_id is not null) AND (x_item_name is null)) THEN
2481 fnd_message.set_name('QA', 'QA_SSQR_DEPENDENT_LOV');
2482 fnd_message.set_token('DEPELEM',
2483 qa_plan_element_api.get_prompt(plan_id, qa_ss_const.item));
2484 fnd_msg_pub.add();
2485 END IF;
2486
2487 x_lov_sql := 'SELECT concatenated_segments, description
2488 FROM mtl_item_locations_kfv
2489 WHERE organization_id = ' || org_id || '
2490 ORDER BY concatenated_segments';
2491 */
2492
2493 END get_to_locator_lov;
2494
2495 PROCEDURE get_to_locator_lov (org_id IN NUMBER, x_item_name IN VARCHAR2,
2496 value IN VARCHAR2, x_lov_sql OUT NOCOPY VARCHAR2) IS
2497 BEGIN
2498 get_to_locator_lov (NULL, org_id, x_item_name, value, x_lov_sql);
2499 END get_to_locator_lov;
2500
2501 PROCEDURE get_lot_status_lov (value IN VARCHAR2, x_lov_sql OUT NOCOPY VARCHAR2) IS
2502
2503 BEGIN
2504
2505 x_lov_sql := 'SELECT status_code,description
2506 FROM mtl_material_statuses
2507 ORDER BY status_code';
2508
2509 END get_lot_status_lov;
2510
2511 PROCEDURE get_serial_status_lov (value IN VARCHAR2, x_lov_sql OUT NOCOPY VARCHAR2) IS
2512
2513 BEGIN
2514
2515 x_lov_sql := 'SELECT status_code,description
2516 FROM mtl_material_statuses
2517 ORDER BY status_code';
2518
2519 END get_serial_status_lov;
2520
2521 PROCEDURE get_nonconformance_source_lov(value IN VARCHAR2, x_lov_sql OUT NOCOPY VARCHAR2)
2522 IS
2523
2524 BEGIN
2525
2526 x_lov_sql := 'SELECT v.short_code code,
2527 v.description
2528 FROM qa_char_value_lookups v
2529 WHERE v.char_id = :1
2530 ORDER BY 1';
2531
2532 END get_nonconformance_source_lov;
2533
2534 PROCEDURE get_nonconform_severity_lov(value IN VARCHAR2, x_lov_sql OUT NOCOPY VARCHAR2)
2535 IS
2536
2537 BEGIN
2538
2539 x_lov_sql := 'SELECT v.short_code code,
2540 v.description
2541 FROM qa_char_value_lookups v
2542 WHERE v.char_id = :1
2543 ORDER BY 1';
2544
2545 END get_nonconform_severity_lov;
2546
2547 PROCEDURE get_nonconform_priority_lov(value IN VARCHAR2, x_lov_sql OUT NOCOPY VARCHAR2)
2548 IS
2549
2550 BEGIN
2551
2552 x_lov_sql := 'SELECT v.short_code code,
2553 v.description
2554 FROM qa_char_value_lookups v
2555 WHERE v.char_id = :1
2556 ORDER BY 1';
2557
2558 END get_nonconform_priority_lov;
2559
2560 PROCEDURE get_nonconformance_type_lov(value IN VARCHAR2, x_lov_sql OUT NOCOPY VARCHAR2)
2561 IS
2562
2563 BEGIN
2564
2565 x_lov_sql := 'SELECT v.short_code code,
2566 v.description
2567 FROM qa_char_value_lookups v
2568 WHERE v.char_id = :1
2569 ORDER BY 1';
2570
2571 END get_nonconformance_type_lov;
2572
2573
2574 PROCEDURE get_nonconformance_status_lov(value IN VARCHAR2, x_lov_sql OUT NOCOPY VARCHAR2)
2575 IS
2576
2577 BEGIN
2578
2579 x_lov_sql := 'SELECT v.short_code code,
2580 v.description
2581 FROM qa_char_value_lookups v
2582 WHERE v.char_id = :1
2583 ORDER BY 1';
2584
2585 END get_nonconformance_status_lov;
2586
2587 --anagarwa Wed Jan 15 13:51:41 PST 2003
2588 -- Bug 2751198
2589 -- support needed for contract number, contract line number and
2590 -- deliverable number
2591
2592 PROCEDURE get_contract_lov (value IN VARCHAR2,
2593 x_lov_sql OUT NOCOPY VARCHAR2) IS
2594
2595 BEGIN
2596
2597 x_lov_sql := 'SELECT k_number, short_description
2598 FROM oke_k_headers_lov_v order by k_number';
2599
2600 END get_contract_lov;
2601
2602
2603 PROCEDURE get_contract_line_lov (value IN VARCHAR2,
2604 x_lov_sql OUT NOCOPY VARCHAR2) IS
2605
2606 BEGIN
2607
2608 x_lov_sql := 'SELECT line_number, line_description
2609 FROM oke_k_lines_full_v order by line_number';
2610
2611 END get_contract_line_lov;
2612
2613 PROCEDURE get_deliverable_lov(value IN VARCHAR2,
2614 x_lov_sql OUT NOCOPY VARCHAR2) IS
2615
2616 BEGIN
2617
2618 x_lov_sql := 'SELECT deliverable_num, description
2619 FROM oke_k_deliverables_vl order by deliverable_num' ;
2620
2621 END get_deliverable_lov;
2622
2623
2624 -- End of inclusions for NCM Hardcode Elements.
2625
2626 --anagarwa Fri Nov 15 13:03:35 PST 2002
2627 --Following added for new CAR lov's
2628
2629 PROCEDURE get_request_source_lov(value IN VARCHAR2, x_lov_sql OUT NOCOPY VARCHAR2)
2630 IS
2631
2632 BEGIN
2633
2634 x_lov_sql := 'SELECT v.short_code code,
2635 v.description
2636 FROM qa_char_value_lookups v
2637 WHERE v.char_id = :1
2638 ORDER BY 1';
2639
2640 END get_request_source_lov;
2641
2642 PROCEDURE get_request_priority_lov(value IN VARCHAR2, x_lov_sql OUT NOCOPY VARCHAR2)
2643 IS
2644
2645 BEGIN
2646
2647 x_lov_sql := 'SELECT v.short_code code,
2648 v.description
2649 FROM qa_char_value_lookups v
2650 WHERE v.char_id = :1
2651 ORDER BY 1';
2652
2653 END get_request_priority_lov;
2654
2655 PROCEDURE get_request_severity_lov(value IN VARCHAR2, x_lov_sql OUT NOCOPY VARCHAR2)
2656 IS
2657
2658 BEGIN
2659
2660 x_lov_sql := 'SELECT v.short_code code,
2661 v.description
2662 FROM qa_char_value_lookups v
2663 WHERE v.char_id = :1
2664 ORDER BY 1';
2665
2666 END get_request_severity_lov;
2667
2668
2669 PROCEDURE get_request_status_lov(value IN VARCHAR2, x_lov_sql OUT NOCOPY VARCHAR2)
2670 IS
2671
2672 BEGIN
2673
2674 x_lov_sql := 'SELECT v.short_code code,
2675 v.description
2676 FROM qa_char_value_lookups v
2677 WHERE v.char_id = :1
2678 ORDER BY 1';
2679
2680 END get_request_status_lov;
2681
2682 -- End of inclusions for CAR Hardcode Elements.
2683
2684
2685 --
2686 -- Removed the DEFAULT clause to make the code GSCC compliant
2687 -- List of changed arguments.
2688 -- Old
2689 -- user_id IN NUMBER DEFAULT NULL
2690 -- New
2691 -- user_id IN NUMBER
2692 --
2693
2694
2695 PROCEDURE get_plan_element_lov(plan_id IN NUMBER, char_id IN NUMBER,
2696 org_id IN NUMBER, user_id IN NUMBER,
2697 x_lov_sql OUT NOCOPY VARCHAR2) IS
2698
2699 BEGIN
2700
2701 -- The function sql_string_exists simple checks to see
2702 -- if the user defined element should have a LOV
2703 -- associated with it or not. If it should then it returns
2704 -- true and populates sql_string - an out parameter.
2705
2706 IF sql_string_exists(plan_id, char_id, org_id, user_id, x_lov_sql) THEN
2707 RETURN;
2708 END IF;
2709
2710 END get_plan_element_lov;
2711
2712 --
2713 -- Added to the IF-ELSIF ladder for newly added collection elements
2714 -- for ASO project. New entries are appended after Party_Name
2715 -- rkunchal Thu Aug 1 12:27:48 PDT 2002
2716 --
2717
2718 FUNCTION get_lov_sql (
2719 plan_id IN NUMBER,
2720 char_id IN NUMBER,
2721 org_id IN NUMBER,
2722 user_id IN NUMBER,
2723 depen1 IN VARCHAR2,
2724 depen2 IN VARCHAR2,
2725 depen3 IN VARCHAR2,
2726 value IN VARCHAR2) RETURN VARCHAR2 IS
2727
2728 l_lov_sql VARCHAR2(1500);
2729
2730 BEGIN
2731 -- Bug 3228490 ksoh Fri Oct 31 11:38:21 PST 2003
2732 -- Now that we utilize fnd_msg_pub to show error messages
2733 -- we should clear stack so that errors are not shown over and over again.
2734 fnd_msg_pub.Initialize();
2735 fnd_msg_pub.reset();
2736
2737 IF (char_id = qa_ss_const.department) THEN
2738 get_department_lov(org_id, value, l_lov_sql);
2739
2740 ELSIF (char_id = qa_ss_const.job_name) THEN
2741 get_job_lov(org_id, value, l_lov_sql);
2742
2743 ELSIF (char_id = qa_ss_const.work_order) THEN
2744 get_work_order_lov(org_id, value, l_lov_sql);
2745
2746 ELSIF (char_id = qa_ss_const.production_line) THEN
2747 get_production_lov(org_id, value, l_lov_sql);
2748
2749 ELSIF (char_id = qa_ss_const.resource_code) THEN
2750 get_resource_code_lov(org_id, value, l_lov_sql);
2751
2752 ELSIF (char_id = qa_ss_const.vendor_name) THEN
2753 get_supplier_lov(value, l_lov_sql);
2754
2755 ELSIF (char_id = qa_ss_const.po_number) THEN
2756 get_po_number_lov(value, l_lov_sql);
2757
2758 ELSIF (char_id = qa_ss_const.customer_name) THEN
2759 get_customer_lov(value, l_lov_sql);
2760
2761 ELSIF (char_id = qa_ss_const.sales_order) THEN
2762 get_so_number_lov(value, l_lov_sql);
2763
2764 ELSIF (char_id = qa_ss_const.order_line) THEN
2765 get_so_line_number_lov(value, l_lov_sql);
2766
2767 ELSIF (char_id = qa_ss_const.po_release_num) THEN
2768 --
2769 -- Bug 5003511. R12 Performance bug. SQLID: 15008630
2770 -- Release number is dependent on PO Number.
2771 -- Call new overloaded method.
2772 -- srhariha. Wed Feb 8 02:10:26 PST 2006.
2773 --
2774 get_po_release_number_lov(plan_id,depen1,value, l_lov_sql);
2775
2776 ELSIF (char_id = qa_ss_const.project_number) THEN
2777 get_project_number_lov(value, l_lov_sql);
2778
2779 ELSIF (char_id = qa_ss_const.task_number) THEN
2780 -- anagarwa Thu Jan 29 15:04:26 PST 2004
2781 -- Bug 3404863 : task lov should be dependent upon project so now we
2782 -- pass project number as parent element value.
2783 get_task_number_lov(plan_id, depen1, value, l_lov_sql);
2784
2785 ELSIF (char_id = qa_ss_const.rma_number) THEN
2786 get_rma_number_lov(value, l_lov_sql);
2787
2788 ELSIF (char_id = qa_ss_const.uom) THEN
2789 -- get_uom_lov(org_id, item_name, value, l_lov_sql);
2790 get_uom_lov(plan_id, org_id, depen1, value, l_lov_sql);
2791
2792 -- anagarwa Mon Feb 24 17:08:57 PST 2003
2793 -- Bug 2808693
2794 -- adding support for comp_revision
2795 ELSIF (char_id = qa_ss_const.revision OR
2796 char_id = qa_ss_const.comp_revision) THEN
2797 -- get_revision_lov(org_id, item_name, value, l_lov_sql);
2798 get_revision_lov(plan_id, org_id, depen1, value, l_lov_sql);
2799
2800 ELSIF (char_id = qa_ss_const.subinventory) THEN
2801 get_subinventory_lov(org_id, value, l_lov_sql);
2802
2803 -- anagarwa Thu Aug 12 15:49:51 PDT 2004
2804 -- bug 3830258 incorrect LOVs in QWB
2805 -- synced up the lot and serial number lov with forms
2806 ELSIF (char_id = qa_ss_const.lot_number) THEN
2807 -- get_lot_number_lov(transaction_id, value, l_lov_sql);
2808 -- get_lot_number_lov(depen3, value, l_lov_sql);
2809 get_lot_number_lov(plan_id, org_id, depen1, value, l_lov_sql);
2810
2811 ELSIF (char_id = qa_ss_const.serial_number) THEN
2812 -- get_serial_number_lov(transaction_id, lot_number, value, l_lov_sql);
2813 -- get_serial_number_lov(plan_id, depen3, depen1, value, l_lov_sql);
2814 get_serial_number_lov(plan_id, org_id, depen2, depen3, depen1, value, l_lov_sql);
2815
2816 -- dgupta: Start R12 EAM Integration. Bug 4345492
2817 ELSIF (char_id = qa_ss_const.asset_instance_number) THEN
2818 get_asset_instance_number_lov(plan_id, org_id, depen1,depen2,value, l_lov_sql);
2819 --dgupta: End R12 EAM Integration. Bug 4345492
2820
2821 ELSIF (char_id = qa_ss_const.asset_number) THEN
2822 -- get_asset_number_lov(org_id, depen1, value, l_lov_sql);
2823 get_asset_number_lov(plan_id, org_id, depen1, value, l_lov_sql);
2824
2825 ELSIF (char_id = qa_ss_const.from_op_seq_num) OR
2826 (char_id = qa_ss_const.to_op_seq_num) THEN
2827 -- get_op_seq_number_lov(org_id, value, job_name, production_line,
2828 -- l_lov_sql);
2829 get_op_seq_number_lov(plan_id, org_id, value, depen1, depen2, l_lov_sql);
2830
2831 ELSIF (char_id = qa_ss_const.po_line_num) THEN
2832 -- get_po_line_number_lov(po_number, value, l_lov_sql);
2833 get_po_line_number_lov(plan_id, depen1, value, l_lov_sql);
2834
2835 ELSIF (char_id = qa_ss_const.po_shipment_num) THEN
2836 -- get_po_shipments_lov(po_line_number, po_number, value, l_lov_sql);
2837 get_po_shipments_lov(plan_id, depen1, depen2, value, l_lov_sql);
2838
2839 ELSIF (char_id = qa_ss_const.receipt_num) THEN
2840 get_receipt_num_lov(value, l_lov_sql);
2841
2842 ELSIF (char_id = qa_ss_const.item) THEN
2843 get_item_lov(org_id, value, l_lov_sql);
2844
2845 -- rkaza. 12/15/2003. bug 3280307. Added lov for comp item
2846 ELSIF (char_id = qa_ss_const.comp_item) THEN
2847 get_comp_item_lov(plan_id, org_id, depen1, value, l_lov_sql);
2848
2849 ELSIF (char_id = qa_ss_const.asset_group) THEN
2850 get_asset_group_lov(org_id, value, l_lov_sql);
2851
2852 ELSIF (char_id = qa_ss_const.asset_activity) THEN
2853 -- get_asset_activity_lov(org_id, depen1, depen2, value, l_lov_sql);
2854 get_asset_activity_lov(plan_id, org_id, depen1, depen2, value, l_lov_sql);
2855
2856 -- added the following to include new hardcoded element followup activity
2857 -- saugupta
2858
2859 ELSIF (char_id = qa_ss_const.followup_activity) THEN
2860 -- get_followup_activity_lov(org_id, depen1, depen2, value, l_lov_sql);
2861 get_followup_activity_lov(plan_id, org_id, depen1, depen2, value, l_lov_sql);
2862
2863 ELSIF (char_id = qa_ss_const.xfr_license_plate_number) THEN
2864 get_xfr_lpn_lov(value, l_lov_sql);
2865
2866 ELSIF (char_id = qa_ss_const.locator) THEN
2867 -- get_locator_lov(org_id, depen1, value, l_lov_sql);
2868 get_locator_lov(plan_id, org_id, depen1, value, l_lov_sql);
2869
2870 ELSIF (char_id = qa_ss_const.party_name) THEN
2871 get_party_lov(value, l_lov_sql);
2872
2873 ELSIF (char_id = qa_ss_const.item_instance) THEN
2874 get_item_instance_lov(value, l_lov_sql);
2875
2876 ELSIF (char_id = qa_ss_const.service_request) THEN
2877 get_service_request_lov(value, l_lov_sql);
2878
2879 ELSIF (char_id = qa_ss_const.maintenance_requirement) THEN
2880 get_maintenance_req_lov(value, l_lov_sql);
2881
2882 ELSIF (char_id = qa_ss_const.rework_job) THEN
2883 get_rework_job_lov(org_id, value, l_lov_sql);
2884
2885 ELSIF (char_id = qa_ss_const.counter_name) THEN
2886 get_counter_name_lov(value, l_lov_sql);
2887
2888 -- anagarwa Mon Feb 2 16:27:56 PST 2004
2889 -- Bug 3415693
2890 -- Disposition, disposition source, disposition actions and
2891 -- disposition status can all be processed using
2892 -- get_plan_element_lov and hence there is no need for the following code.
2893 -- Infact, the inclusion of the following code will cause incorrect lov
2894 -- to appear on selfservice
2895 -- For maintenance sake we should remove corresponding get lov functions
2896 -- too as they serve no purpose other than making this file bulkier.
2897 /*
2898 ELSIF (char_id = qa_ss_const.disposition_source) THEN
2899 get_disposition_source_lov(value, l_lov_sql);
2900
2901 ELSIF (char_id = qa_ss_const.disposition_action) THEN
2902 get_disposition_action_lov(value, l_lov_sql);
2903
2904 ELSIF (char_id = qa_ss_const.disposition) THEN
2905 get_disposition_lov(value, l_lov_sql);
2906
2907 ELSIF (char_id = qa_ss_const.disposition_status) THEN
2908 get_disposition_status_lov(value, l_lov_sql);
2909
2910 */
2911 --
2912 -- See Bug 2588213
2913 -- To support the element Maintenance Op Seq Number
2914 -- to be used along with Maintenance Workorder
2915 -- rkunchal Mon Sep 23 23:46:28 PDT 2002
2916 --
2917 ELSIF (char_id = qa_ss_const.maintenance_op_seq) THEN
2918 -- get_maintenance_op_seq_lov(org_id, value, depen1, l_lov_sql);
2919 get_maintenance_op_seq_lov(plan_id, org_id, value, depen1, l_lov_sql);
2920 --
2921 -- End of inclusions for Bug 2588213
2922 --
2923
2924 -- Start of inclusions for NCM Hardcode Elements.
2925 -- suramasw Thu Oct 31 10:48:59 PST 2002.
2926 -- Bug 2449067.
2927
2928 ELSIF (char_id = qa_ss_const.bill_reference) THEN
2929 get_bill_reference_lov(org_id, value, l_lov_sql);
2930
2931 ELSIF (char_id = qa_ss_const.routing_reference) THEN
2932 get_routing_reference_lov(org_id, value, l_lov_sql);
2933
2934 ELSIF (char_id = qa_ss_const.to_subinventory) THEN
2935 get_to_subinventory_lov(org_id, value, l_lov_sql);
2936
2937 ELSIF (char_id = qa_ss_const.to_locator) THEN
2938 -- get_to_locator_lov(org_id, depen1, value, l_lov_sql);
2939 get_to_locator_lov(plan_id, org_id, depen1, value, l_lov_sql);
2940
2941 ELSIF (char_id = qa_ss_const.lot_status) THEN
2942 get_lot_status_lov(value, l_lov_sql);
2943
2944 ELSIF (char_id = qa_ss_const.serial_status) THEN
2945 get_serial_status_lov(value, l_lov_sql);
2946
2947 ELSIF (char_id = qa_ss_const.nonconformance_source) THEN
2948 get_nonconformance_source_lov(value, l_lov_sql);
2949
2950 ELSIF (char_id = qa_ss_const.nonconform_severity) THEN
2951 get_nonconform_severity_lov(value, l_lov_sql);
2952
2953 ELSIF (char_id = qa_ss_const.nonconform_priority) THEN
2954 get_nonconform_priority_lov(value, l_lov_sql);
2955
2956 ELSIF (char_id = qa_ss_const.nonconformance_type) THEN
2957 get_nonconformance_type_lov(value, l_lov_sql);
2958
2959 ELSIF (char_id = qa_ss_const.nonconformance_status) THEN
2960 get_nonconformance_status_lov(value, l_lov_sql);
2961
2962 --anagarwa Wed Jan 15 13:51:41 PST 2003
2963 -- Bug 2751198
2964 -- support needed for contract number, contract line number and
2965 -- deliverable number
2966
2967 ELSIF (char_id = qa_ss_const.contract_number) THEN
2968 get_contract_lov(value, l_lov_sql);
2969
2970 ELSIF (char_id = qa_ss_const.contract_line_number) THEN
2971 get_contract_line_lov(value, l_lov_sql);
2972
2973 ELSIF (char_id = qa_ss_const.deliverable_number) THEN
2974 get_deliverable_lov(value, l_lov_sql);
2975
2976 -- End of inclusions for NCM Hardcode Elements.
2977
2978 --anagarwa Fri Nov 15 13:03:35 PST 2002
2979 --Following added for new CAR lov's
2980
2981 ELSIF (char_id = qa_ss_const.request_source) THEN
2982 get_request_source_lov(value, l_lov_sql);
2983
2984 ELSIF (char_id = qa_ss_const.request_priority) THEN
2985 get_request_priority_lov(value, l_lov_sql);
2986
2987 ELSIF (char_id = qa_ss_const.request_severity) THEN
2988 get_request_severity_lov(value, l_lov_sql);
2989
2990 ELSIF (char_id = qa_ss_const.request_status) THEN
2991 get_request_status_lov(value, l_lov_sql);
2992
2993 -- anagarwa Thu Aug 12 15:49:51 PDT 2004
2994 -- bug 3830258 incorrect LOVs in QWB
2995 -- synced up the component lot number and component serial number
2996 -- lov with forms
2997 ELSIF (char_id = qa_ss_const.comp_lot_number) THEN
2998 get_comp_lot_number_lov(plan_id, org_id, depen1, value, l_lov_sql);
2999
3000 ELSIF (char_id = qa_ss_const.comp_serial_number) THEN
3001 get_comp_serial_number_lov(plan_id, org_id, depen2, depen3, depen1, value, l_lov_sql);
3002
3003 -- End of inclusions for CAR Hardcode Elements.
3004 /* R12 DR Integration. Bug 4345489 Start */
3005 ELSIF (char_id = qa_ss_const.repair_order_number) THEN
3006 get_repair_order_lov(value, l_lov_sql);
3007
3008 ELSIF (char_id = qa_ss_const.jtf_task_number) THEN
3009 get_jtf_task_lov(value, l_lov_sql);
3010 /* R12 DR Integration. Bug 4345489 End */
3011
3012 -- R12 OPM Deviations. Bug 4345503 Start
3013 ELSIF (char_id = qa_ss_const.process_batch_num) THEN
3014 get_process_batch_num_lov(org_id,value,l_lov_sql);
3015
3016 ELSIF (char_id = qa_ss_const.process_batchstep_num) THEN
3017 get_process_batchstep_num_lov
3018 (org_id,plan_id,depen1,value, l_lov_sql);
3019
3020 ELSIF (char_id = qa_ss_const.process_operation) THEN
3021 get_process_operation_lov
3022 (org_id,plan_id,depen1, depen2,value, l_lov_sql);
3023
3024 ELSIF (char_id = qa_ss_const.process_activity) THEN
3025 get_process_activity_lov
3026 (org_id,plan_id,depen1, depen2,value, l_lov_sql);
3027
3028 ELSIF (char_id = qa_ss_const.process_resource) THEN
3029 get_process_resource_lov
3030 (org_id,plan_id,depen1, depen2, depen3, value, l_lov_sql);
3031
3032 ELSIF (char_id = qa_ss_const.process_parameter) THEN
3033 get_process_parameter_lov
3034 (org_id,plan_id,depen1, value, l_lov_sql);
3035
3036 -- R12 OPM Deviations. Bug 4345503 End
3037
3038 --
3039 -- Bug 6161802
3040 -- Obtain rma line number lov with rma number as a bind variable.
3041 -- skolluku Mon Jul 16 22:08:16 PDT 2007
3042 --
3043 ELSIF (char_id = qa_ss_const.rma_line_num) THEN
3044 get_rma_line_num_lov(plan_id, depen1, value, l_lov_sql);
3045
3046 ELSE
3047 get_plan_element_lov (plan_id, char_id, org_id, user_id, l_lov_sql);
3048 END IF;
3049
3050 RETURN l_lov_sql;
3051
3052 END get_lov_sql;
3053
3054
3055 -- Bug 4270911. SQL bind compliance fix.
3056 -- Please see bugdb for more details and TD link.
3057 -- srhariha. Thu Apr 7 21:43:08 PDT 2005.
3058
3059 FUNCTION get_lov_bind_values (
3060 plan_id IN NUMBER,
3061 char_id IN NUMBER,
3062 org_id IN NUMBER DEFAULT NULL,
3063 user_id IN NUMBER DEFAULT NULL,
3064 depen1 IN VARCHAR2 DEFAULT NULL,
3065 depen2 IN VARCHAR2 DEFAULT NULL,
3066 depen3 IN VARCHAR2 DEFAULT NULL,
3067 value IN VARCHAR2 DEFAULT NULL) RETURN VARCHAR2 IS
3068
3069 BEGIN
3070
3071
3072 -- Collection elements dependent only on ORG_ID.
3073
3074 IF char_id IN ( qa_ss_const.department,
3075 qa_ss_const.job_name,
3076 qa_ss_const.work_order,
3077 qa_ss_const.production_line,
3078 qa_ss_const.resource_code,
3079 -- qa_ss_const.uom,
3080 qa_ss_const.subinventory,
3081 qa_ss_const.item,
3082 qa_ss_const.asset_group,
3083 qa_ss_const.rework_job,
3084 qa_ss_const.bill_reference,
3085 qa_ss_const.routing_reference,
3086 -- R12 OPM Deviations. Bug 4345503 Start
3087 qa_ss_const.process_batch_num,
3088 -- R12 OPM Deviations. Bug 4345503 End
3089 qa_ss_const.to_subinventory ) THEN
3090
3091 RETURN to_char(org_id);
3092
3093 END IF;
3094
3095 -- Collection elements based on QA_LOOKUPS.
3096
3097 IF char_id IN ( qa_ss_const.nonconformance_source,
3098 qa_ss_const.nonconform_severity,
3099 qa_ss_const.nonconform_priority,
3100 qa_ss_const.nonconformance_status,
3101 qa_ss_const.nonconformance_type,
3102 qa_ss_const.request_source,
3103 qa_ss_const.request_priority,
3104 qa_ss_const.request_severity,
3105 qa_ss_const.request_status ) THEN
3106
3107 RETURN to_char(char_id);
3108
3109 END IF;
3110
3111 -- Other dependent elements.
3112
3113 IF char_id = qa_ss_const.task_number THEN
3114 RETURN get_task_number_bind_values(depen1);
3115
3116 ELSIF (char_id = qa_ss_const.revision OR
3117 char_id = qa_ss_const.comp_revision) THEN
3118 RETURN get_revision_bind_values(org_id, depen1);
3119
3120 -- Bug 5005707. Adding item as a filtering criteria
3121 -- for item uom to improve performance.
3122 -- Removed the same from the above if condition
3123 -- saugupta Mon, 10 Jul 2006 21:47:17 -0700 PDT
3124 ELSIF (char_id = qa_ss_const.uom) THEN
3125 RETURN get_uom_bind_values(p_org_id => org_id, p_item_name => depen1);
3126
3127 ELSIF (char_id = qa_ss_const.lot_number) THEN
3128 RETURN get_lot_number_bind_values(org_id, depen1);
3129
3130 ELSIF (char_id = qa_ss_const.comp_lot_number) THEN
3131 RETURN get_comp_lot_bind_values(org_id, depen1);
3132
3133 ELSIF (char_id = qa_ss_const.serial_number) THEN
3134 RETURN get_serial_no_bind_values(plan_id, org_id, depen2, depen3, depen1);
3135
3136 ELSIF (char_id = qa_ss_const.comp_serial_number) THEN
3137 RETURN get_comp_serial_no_bind_values(plan_id, org_id, depen2, depen3, depen1);
3138
3139 -- dgupta: Start R12 EAM Integration. Bug 4345492
3140 ELSIF (char_id = qa_ss_const.asset_instance_number) THEN
3141 RETURN get_asset_inst_num_bind_values(org_id, depen1, depen2);
3142
3143 ELSIF (char_id = qa_ss_const.asset_number) THEN
3144 RETURN get_asset_number_bind_values(org_id, depen1, depen2);
3145 --dgupta: End R12 EAM Integration. Bug 4345492
3146
3147 ELSIF (char_id = qa_ss_const.from_op_seq_num) OR
3148 (char_id = qa_ss_const.to_op_seq_num) THEN
3149 RETURN get_op_seq_no_bind_values(plan_id, org_id, depen1, depen2);
3150
3151 ELSIF (char_id = qa_ss_const.po_line_num) THEN
3152 RETURN get_po_line_no_bind_values(depen1);
3153
3154 ELSIF (char_id = qa_ss_const.po_shipment_num) THEN
3155 RETURN get_po_shipments_bind_values (depen1, depen2);
3156
3157 ELSIF (char_id = qa_ss_const.comp_item) THEN
3158 RETURN get_comp_item_bind_values (org_id, depen1);
3159
3160 -- dgupta: Start R12 EAM Integration. Bug 4345492
3161 ELSIF (char_id = qa_ss_const.asset_activity) THEN
3162 RETURN get_asset_activity_bind_values (org_id, depen1, depen2, depen3);
3163
3164 ELSIF (char_id = qa_ss_const.followup_activity) THEN
3165 RETURN get_followup_act_bind_values(org_id, depen1, depen2, depen3);
3166 --dgupta: End R12 EAM Integration. Bug 4345492
3167
3168 ELSIF (char_id = qa_ss_const.locator) OR
3169 (char_id = qa_ss_const.to_locator) THEN
3170 RETURN get_locator_bind_values(org_id, depen1);
3171
3172 ELSIF (char_id = qa_ss_const.maintenance_op_seq) THEN
3173 RETURN get_maint_op_seq_bind_values(org_id, depen1);
3174
3175 -- R12 OPM Deviations. Bug 4345503 Start
3176 ELSIF (char_id = qa_ss_const.process_batchstep_num) THEN
3177 RETURN GET_PROCESS_STEP_BIND_VALUE(org_id, depen1);
3178
3179 ELSIF (char_id = qa_ss_const.process_operation) THEN
3180 RETURN GET_PROCESS_OPRN_BIND_VALUE(org_id, depen1, depen2);
3181
3182 ELSIF (char_id = qa_ss_const.process_activity) THEN
3183 RETURN GET_PROCESS_ACT_BIND_VALUE(org_id, depen1, depen2);
3184
3185 ELSIF (char_id = qa_ss_const.process_resource) THEN
3186 RETURN GET_PROCESS_RSR_BIND_VALUE(org_id, depen1, depen2, depen3);
3187
3188 ELSIF (char_id = qa_ss_const.process_parameter) THEN
3189 RETURN GET_PROCESS_PARAM_BIND_VALUE(org_id, depen1);
3190 -- R12 OPM Deviations. Bug 4345503 End
3191
3192 ELSIF (char_id = qa_ss_const.po_release_num) THEN
3193 --
3194 -- Bug 5003511. R12 Performance bug. SQLID: 15008630
3195 -- Release number is dependent on PO Number.
3196 -- Call new method for bind values.
3197 -- srhariha. Wed Feb 8 02:10:26 PST 2006.
3198 --
3199 RETURN get_po_rel_no_bind_values(depen1);
3200 --
3201 -- Bug 6161802
3202 -- Return rma number as a bind variable for rma line number lov
3203 -- skolluku Mon Jul 16 22:08:16 PDT 2007
3204 --
3205 ELSIF (char_id = qa_ss_const.rma_line_num) THEN
3206 RETURN get_rma_line_num_bind_values(depen1);
3207
3208 ELSE
3209
3210 -- Will handle qa_plan_char_value_lookups.
3211 -- For all other cases it returns NULL
3212 RETURN sql_string_bind_values (plan_id, char_id);
3213
3214 END IF;
3215
3216
3217 RETURN NULL;
3218
3219 END get_lov_bind_values;
3220
3221
3222 END qa_ss_lov_api;
[Home] [Help]