1 PACKAGE BODY qa_plan_element_api AS
2 /* $Header: qltelemb.plb 120.43.12020000.3 2013/02/11 14:12:15 ntungare ship $ */
3
4 --
5 -- Type definition. These are tables used to create internal
6 -- cache to improve performance. Any records retrieved will be
7 -- temporarily saved into these tables.
8 --
9
10 -- Bug 3769260. shkalyan 30 July 2004.
11 -- Added this record type for storing relevant QA Spec elements.
12 -- This is needed as we need to select only specific columns
13 -- of QA spec elements instead of selecting * from QA_SPEC_CHARS
14
15 TYPE qa_spec_char_rec IS RECORD
16 (
17 spec_id qa_spec_chars.spec_id%TYPE,
18 char_id qa_spec_chars.char_id%TYPE,
19 enabled_flag qa_spec_chars.enabled_flag%TYPE,
20 target_value qa_spec_chars.target_value%TYPE,
21 upper_spec_limit qa_spec_chars.upper_spec_limit%TYPE,
22 lower_spec_limit qa_spec_chars.lower_spec_limit%TYPE,
23 upper_reasonable_limit qa_spec_chars.upper_reasonable_limit%TYPE,
24 lower_reasonable_limit qa_spec_chars.lower_reasonable_limit%TYPE,
25 upper_user_defined_limit qa_spec_chars.upper_user_defined_limit%TYPE,
26 lower_user_defined_limit qa_spec_chars.lower_user_defined_limit%TYPE,
27 uom_code qa_spec_chars.uom_code%TYPE
28 );
29
30 TYPE qa_spec_chars_table IS TABLE OF qa_spec_char_rec
31 INDEX BY BINARY_INTEGER;
32
33 --TYPE qa_spec_chars_table IS TABLE OF qa_spec_chars%ROWTYPE
34 -- INDEX BY BINARY_INTEGER;
35
36 -- Bug 3769260. shkalyan 30 July 2004.
37 -- Added this record type for storing relevant QA Plan elements.
38 -- This is needed as we need to select only specific columns
39 -- of QA Plan elements instead of selecting * from QA_PLAN_CHARS
40
41 TYPE qa_plan_char_rec IS RECORD
42 (
43 plan_id qa_plan_chars.plan_id%TYPE,
44 char_id qa_plan_chars.char_id%TYPE,
45 prompt_sequence qa_plan_chars.prompt_sequence%TYPE,
46 prompt qa_plan_chars.prompt%TYPE,
47 enabled_flag qa_plan_chars.enabled_flag%TYPE,
48 mandatory_flag qa_plan_chars.mandatory_flag%TYPE,
49 default_value qa_plan_chars.default_value%TYPE,
50 default_value_id qa_plan_chars.default_value_id%TYPE,
51 result_column_name qa_plan_chars.result_column_name%TYPE,
52 values_exist_flag qa_plan_chars.values_exist_flag%TYPE,
53 displayed_flag qa_plan_chars.displayed_flag%TYPE,
54 decimal_precision qa_plan_chars.decimal_precision%TYPE,
55 uom_code qa_plan_chars.uom_code%TYPE,
56 read_only_flag qa_plan_chars.read_only_flag%TYPE,
57 ss_poplist_flag qa_plan_chars.ss_poplist_flag%TYPE,
58 information_flag qa_plan_chars.information_flag%TYPE
59 );
60
61 TYPE qa_plan_chars_table IS TABLE OF qa_plan_char_rec
62 INDEX BY BINARY_INTEGER;
63
64 --12.1 QWB Usability Improvements project
65 Type string_list is table of varchar2(200) index by binary_integer;
66
67 --TYPE qa_plan_chars_table IS TABLE OF qa_plan_chars%ROWTYPE
68 -- INDEX BY BINARY_INTEGER;
69
70 CURSOR cursor_qa_plan_chars(p_id NUMBER) IS
71 SELECT plan_id,
72 char_id,
73 prompt_sequence,
74 prompt,
75 enabled_flag,
76 mandatory_flag,
77 default_value,
78 default_value_id,
79 result_column_name,
80 values_exist_flag,
81 displayed_flag,
82 decimal_precision,
83 uom_code,
84 read_only_flag,
85 ss_poplist_flag,
86 information_flag
87 FROM qa_plan_chars
88 WHERE plan_id = p_id
89 AND enabled_flag = 1;
90
91 --
92 -- Package Variables: These will be populated at run time
93 --
94
95 x_qa_spec_chars_array qa_spec_chars_table;
96 x_qa_plan_chars_array qa_plan_chars_table;
97
98 --
99 -- All the fetch_... procedures are auxiliary caching functions
100 -- called only by inquiry APIs that return the object's attributes.
101 --
102
103 --
104 -- This plan element index is used to hash plan id and element id
105 -- into one unique integer to be used as index into the cache.
106 --
107
108 FUNCTION plan_element_index(plan_id IN NUMBER, element_id IN NUMBER)
109 RETURN NUMBER IS
110
111 i NUMBER;
112 BEGIN
113 --
114 -- Bug 2409938
115 -- This is a potential installation/upgrade error.
116 -- Error happens if there is some customization of
117 -- collection plans or elements with huge IDs.
118 -- Temporarily fixed with a modulus. It should be
119 -- properly fixed with a hash collision resolution.
120 -- But the temp workaround should only have collision
121 -- when user has more than 20,000 collection plans
122 -- *and still* with a probability of about 1/200,000.
123 -- bso Tue Jul 16 12:41:23 PDT 2002
124 --
125
126 --
127 -- Bug 2465704
128 -- The above hash collision problem is now fixed with
129 -- linear hash collision resolution.
130 -- The plan_element array is hit to see if the index
131 -- contains the right plan element. If not, we search
132 -- forward until either the matching plan element is
133 -- reached or an empty cell is reached.
134 --
135 -- Because of this, we need to introduce a new function
136 -- spec_element_index for use by the spec_element array.
137 -- bso Sun Dec 1 17:39:18 PST 2002
138 --
139
140 i := mod(plan_id * qa_ss_const.max_elements + element_id,
141 2147483647);
142
143 LOOP
144 IF NOT x_qa_plan_chars_array.EXISTS(i) THEN
145 RETURN i;
146 END IF;
147
148 IF x_qa_plan_chars_array(i).plan_id = plan_id AND
149 x_qa_plan_chars_array(i).char_id = element_id THEN
150 RETURN i;
151 END IF;
152
153 i := mod(i + 1, 2147483647);
154 END LOOP;
155
156 END plan_element_index;
157
158
159 FUNCTION spec_element_index(p_spec_id IN NUMBER, p_element_id IN NUMBER)
160 RETURN NUMBER IS
161
162 i NUMBER;
163 BEGIN
164 -- Bug 2465704
165 -- See comments in plan_element_index.
166
167 i := mod(p_spec_id * qa_ss_const.max_elements + p_element_id,
168 2147483647);
169
170 LOOP
171 IF NOT x_qa_spec_chars_array.EXISTS(i) THEN
172 RETURN i;
173 END IF;
174
175 IF x_qa_spec_chars_array(i).spec_id = p_spec_id AND
176 x_qa_spec_chars_array(i).char_id = p_element_id THEN
177 RETURN i;
178 END IF;
179
180 i := mod(i + 1, 2147483647);
181 END LOOP;
182
183 END spec_element_index;
184
185
186 FUNCTION exists_qa_spec_chars(spec_id IN NUMBER, element_id IN NUMBER)
187 RETURN BOOLEAN IS
188 BEGIN
189 RETURN x_qa_spec_chars_array.EXISTS(
190 spec_element_index(spec_id, element_id));
191 END exists_qa_spec_chars;
192
193
194 --
195 -- See Bug 2624112
196 --
197 -- Modified the logic for Global Specifications Enhancements
198 --
199 -- rkunchal
200 --
201
202 PROCEDURE fetch_qa_spec_chars (spec_id IN NUMBER, element_id IN NUMBER) IS
203
204 -- Bug 3769260. shkalyan 30 July 2004.
205 -- Modified cursor to select only specific columns
206 -- of QA spec elements instead of selecting * from QA_SPEC_CHARS
207
208 CURSOR C1 (s_id NUMBER, e_id NUMBER) IS
209 SELECT QSC.spec_id,
210 QSC.char_id,
211 QSC.enabled_flag,
212 QSC.target_value,
213 QSC.upper_spec_limit,
214 QSC.lower_spec_limit,
215 QSC.upper_reasonable_limit,
216 QSC.lower_reasonable_limit,
217 QSC.upper_user_defined_limit,
218 QSC.lower_user_defined_limit,
219 QSC.uom_code
220 FROM qa_spec_chars QSC,
221 qa_specs QS
222 WHERE QSC.char_id = e_id
223 AND QSC.spec_id = QS.common_spec_id
224 AND QS.spec_id = s_id;
225
226 BEGIN
227
228 IF NOT exists_qa_spec_chars(spec_id, element_id) THEN
229 OPEN C1(spec_id, element_id);
230
231 FETCH C1 INTO x_qa_spec_chars_array(
232 spec_element_index(spec_id, element_id));
233 CLOSE C1;
234 END IF;
235
236 END fetch_qa_spec_chars;
237
238 -- Bug 3769260. shkalyan 30 July 2004.
239 -- Added this procedure to fetch all the elements of a specifications
240 -- The reason for introducing this procedure is to reduce the number of
241 -- hits on the QA_SPEC_CHARS.
242 -- Callers will use this procedure to pre-fetch all the Spec elements
243 -- to the cache if all the elements of a Spec would be accessed.
244
245 PROCEDURE fetch_qa_spec_chars (spec_id IN NUMBER) IS
246
247 CURSOR C1 (s_id NUMBER) IS
248 SELECT QSC.spec_id,
249 QSC.char_id,
250 QSC.enabled_flag,
251 QSC.target_value,
252 QSC.upper_spec_limit,
253 QSC.lower_spec_limit,
254 QSC.upper_reasonable_limit,
255 QSC.lower_reasonable_limit,
256 QSC.upper_user_defined_limit,
257 QSC.lower_user_defined_limit,
258 QSC.uom_code
259 FROM qa_spec_chars QSC,
260 qa_specs QS
261 WHERE QSC.spec_id = QS.common_spec_id
262 AND QS.spec_id = s_id;
263
264 l_spec_char_rec qa_spec_char_rec;
265
266 BEGIN
267
268 OPEN C1(spec_id);
269 LOOP
270 FETCH C1 INTO l_spec_char_rec;
271 EXIT WHEN C1%NOTFOUND;
272
273 IF NOT exists_qa_spec_chars(spec_id, l_spec_char_rec.char_id) THEN
274 x_qa_spec_chars_array(spec_element_index(spec_id, l_spec_char_rec.char_id)) := l_spec_char_rec;
275 END IF;
276 END LOOP;
277 CLOSE C1;
278
279 END fetch_qa_spec_chars;
280
281
282 FUNCTION exists_qa_plan_chars(plan_id IN NUMBER, element_id IN NUMBER)
283 RETURN BOOLEAN IS
284 BEGIN
285
286 RETURN x_qa_plan_chars_array.EXISTS(
287 plan_element_index(plan_id, element_id));
288 END exists_qa_plan_chars;
289
290
291 PROCEDURE fetch_qa_plan_chars (plan_id IN NUMBER, element_id IN NUMBER) IS
292
293 -- Bug 3769260. shkalyan 30 July 2004.
294 -- Modified cursor to select only specific columns
295 -- of QA plan elements instead of selecting * from QA_PLAN_CHARS
296
297 CURSOR C1 (p_id NUMBER, e_id NUMBER) IS
298 SELECT plan_id,
299 char_id,
300 prompt_sequence,
301 prompt,
302 enabled_flag,
303 mandatory_flag,
304 default_value,
305 default_value_id,
306 result_column_name,
307 values_exist_flag,
308 displayed_flag,
309 decimal_precision,
310 uom_code,
311 read_only_flag,
312 ss_poplist_flag,
313 information_flag
314 FROM qa_plan_chars
315 WHERE plan_id = p_id
316 AND char_id = e_id
317 AND enabled_flag = 1;
318
319 BEGIN
320
321 IF NOT exists_qa_plan_chars(plan_id, element_id) THEN
322
323 OPEN C1(plan_id, element_id);
324 FETCH C1 INTO x_qa_plan_chars_array(
325 plan_element_index(plan_id, element_id));
326
327 CLOSE C1;
328 END IF;
329
330 EXCEPTION WHEN OTHERS THEN
331 RAISE;
332 END fetch_qa_plan_chars;
333
334 -- Bug 3769260. shkalyan 30 July 2004.
335 -- Added this procedure to fetch all the elements of a plan
336 -- The reason for introducing this procedure is to reduce the number of
337 -- hits on the QA_PLAN_CHARS.
338 -- Callers will use this procedure to pre-fetch all the Plan elements
339 -- to the cache if all the elements of a plan would be accessed.
340
341 PROCEDURE fetch_qa_plan_chars (plan_id IN NUMBER) IS
342 l_plan_char_rec qa_plan_char_rec;
343
344 -- Bug 5182097. Cursor C1 is needed in the new proc
345 -- refetch_qa_plan_chars, so extracted out to package
346 -- level and renaming it to cursor_qa_plan_chars.
347 -- bso Mon May 1 16:59:56 PDT 2006
348
349 BEGIN
350
351 OPEN cursor_qa_plan_chars(plan_id);
352 LOOP
353 FETCH cursor_qa_plan_chars INTO l_plan_char_rec;
354 EXIT WHEN cursor_qa_plan_chars%NOTFOUND;
355
356 IF NOT exists_qa_plan_chars(plan_id, l_plan_char_rec.char_id) THEN
357 x_qa_plan_chars_array(plan_element_index(plan_id, l_plan_char_rec.char_id)) := l_plan_char_rec;
358 END IF;
359 END LOOP;
360 CLOSE cursor_qa_plan_chars;
361
362 END fetch_qa_plan_chars;
363
364
365 PROCEDURE refetch_qa_plan_chars(p_plan_id IN NUMBER) IS
366 --
367 -- Bug 5182097. Need a procedure to repopulate qpc otherwise
368 -- some subtle changes in Setup Collection Plans are not immediately
369 -- reflected in QWB. Also need Map_in_demand to call this proc during
370 -- qwb execution.
371 --
372 -- The procedure is almost identical to fetch_qa_plan_chars except
373 -- it doesn't first check if the plan char exists. Fetching is
374 -- forced to avoid caching problem.
375 --
376 -- bso Mon May 1 17:01:45 PDT 2006
377 --
378 l_plan_char_rec qa_plan_char_rec;
379
380 BEGIN
381
382 OPEN cursor_qa_plan_chars(p_plan_id);
383 LOOP
384 FETCH cursor_qa_plan_chars INTO l_plan_char_rec;
385 EXIT WHEN cursor_qa_plan_chars%NOTFOUND;
386
387 x_qa_plan_chars_array(plan_element_index(p_plan_id,
388 l_plan_char_rec.char_id)) := l_plan_char_rec;
389 END LOOP;
390 CLOSE cursor_qa_plan_chars;
391
392 END refetch_qa_plan_chars;
393
394 --
395 -- This is a qa_spec_chars inquiry API.
396 --
397 FUNCTION qsc_lower_reasonable_limit(spec_id IN NUMBER,
398 element_id IN NUMBER) RETURN VARCHAR2 IS
399 BEGIN
400 fetch_qa_spec_chars(spec_id, element_id);
401 IF NOT exists_qa_spec_chars(spec_id, element_id) THEN
402 RETURN NULL;
403 END IF;
404 RETURN x_qa_spec_chars_array(spec_element_index(spec_id, element_id)).
405 lower_reasonable_limit;
406 END qsc_lower_reasonable_limit;
407
408
409 --
410 -- This is a qa_spec_chars inquiry API.
411 --
412 FUNCTION qsc_upper_reasonable_limit(spec_id IN NUMBER,
413 element_id IN NUMBER) RETURN VARCHAR2 IS
414 BEGIN
415 fetch_qa_spec_chars(spec_id, element_id);
416 IF NOT exists_qa_spec_chars(spec_id, element_id) THEN
417 RETURN NULL;
418 END IF;
419 RETURN x_qa_spec_chars_array(spec_element_index(spec_id, element_id)).
420 upper_reasonable_limit;
421 END qsc_upper_reasonable_limit;
422
423
424 --
425 -- This is a qa_plan_chars inquiry API.
426 --
427 FUNCTION qpc_enabled_flag(plan_id IN NUMBER,
428 element_id IN NUMBER) RETURN NUMBER IS
429 BEGIN
430 fetch_qa_plan_chars(plan_id, element_id);
431 IF NOT exists_qa_plan_chars(plan_id, element_id) THEN
432 RETURN NULL;
433 END IF;
434 RETURN x_qa_plan_chars_array(plan_element_index(plan_id, element_id)).
435 enabled_flag;
436 END qpc_enabled_flag;
437
438 --
439 -- See Bug 2624112
440 -- The decimal precision for a number type collection
441 -- element is to be configured at plan level.
442 -- rkunchal Wed Oct 16 05:32:33 PDT 2002
443 --
444 -- Decimal Precision is taken from qa_plan_chars
445 --
446
447 --
448 -- This is a qa_plan_chars inquiry API.
449 --
450 FUNCTION qpc_decimal_precision(p_plan_id IN NUMBER,
451 p_element_id IN NUMBER) RETURN NUMBER IS
452 BEGIN
453
454 fetch_qa_plan_chars(p_plan_id, p_element_id);
455 IF NOT exists_qa_plan_chars(p_plan_id, p_element_id) THEN
456 RETURN NULL;
457 END IF;
458
459 RETURN x_qa_plan_chars_array(plan_element_index(p_plan_id, p_element_id)).
460 decimal_precision;
461
462 END qpc_decimal_precision;
463
464 --
465 -- This is a qa_plan_chars inquiry API.
466 --
467 FUNCTION qpc_mandatory_flag(plan_id IN NUMBER,
468 element_id IN NUMBER) RETURN NUMBER IS
469 BEGIN
470 fetch_qa_plan_chars(plan_id, element_id);
471 IF NOT exists_qa_plan_chars(plan_id, element_id) THEN
472 RETURN NULL;
473 END IF;
474 RETURN x_qa_plan_chars_array(plan_element_index(plan_id, element_id)).
475 mandatory_flag;
476 END qpc_mandatory_flag;
477
478
479 -- New get functions added for the new columns in qa_plan_chars
480 -- SSQR project
481
482 FUNCTION qpc_displayed_flag(plan_id IN NUMBER,
483 element_id IN NUMBER) RETURN NUMBER IS
484 BEGIN
485 fetch_qa_plan_chars(plan_id, element_id);
486 IF NOT exists_qa_plan_chars(plan_id, element_id) THEN
487 RETURN NULL;
488 END IF;
489 RETURN x_qa_plan_chars_array(plan_element_index(plan_id, element_id)).
490 displayed_flag;
491 END qpc_displayed_flag;
492
493
494
495 FUNCTION qpc_poplist_flag(plan_id IN NUMBER,
496 element_id IN NUMBER) RETURN NUMBER IS
497 BEGIN
498 fetch_qa_plan_chars(plan_id, element_id);
499 IF NOT exists_qa_plan_chars(plan_id, element_id) THEN
500 RETURN NULL;
501 END IF;
502 RETURN x_qa_plan_chars_array(plan_element_index(plan_id, element_id)).
503 ss_poplist_flag;
504 END qpc_poplist_flag;
505
506
507
508 FUNCTION qpc_read_only_flag(plan_id IN NUMBER,
509 element_id IN NUMBER) RETURN NUMBER IS
510 BEGIN
511 fetch_qa_plan_chars(plan_id, element_id);
512 IF NOT exists_qa_plan_chars(plan_id, element_id) THEN
513 RETURN NULL;
514 END IF;
515 RETURN x_qa_plan_chars_array(plan_element_index(plan_id, element_id)).
516 read_only_flag;
517 END qpc_read_only_flag;
518
519 --
520 -- This is a qa_plan_chars inquiry API.
521 --
522 FUNCTION qpc_values_exist_flag(plan_id IN NUMBER,
523 element_id IN NUMBER) RETURN NUMBER IS
524 BEGIN
525
526 fetch_qa_plan_chars(plan_id, element_id);
527 IF NOT exists_qa_plan_chars(plan_id, element_id) THEN
528 RETURN NULL;
529 END IF;
530
531 RETURN x_qa_plan_chars_array(plan_element_index(plan_id, element_id)).
532 values_exist_flag;
533 END qpc_values_exist_flag;
534
535
536 --
537 -- This is a qa_plan_chars inquiry API.
538 --
539 FUNCTION qpc_result_column_name (plan_id IN NUMBER,
540 element_id IN NUMBER) RETURN VARCHAR2 IS
541 BEGIN
542
543 fetch_qa_plan_chars(plan_id, element_id);
544 IF NOT exists_qa_plan_chars(plan_id, element_id) THEN
545 RETURN NULL;
546 END IF;
547
548 RETURN x_qa_plan_chars_array(plan_element_index(plan_id, element_id)).
549 result_column_name;
550 END qpc_result_column_name;
551
552 --
553 -- This is a qa_plan_chars inquiry API.
554 --
555 FUNCTION get_prompt (plan_id IN NUMBER,
556 element_id IN NUMBER) RETURN VARCHAR2 IS
557 BEGIN
558
559 fetch_qa_plan_chars(plan_id, element_id);
560 IF NOT exists_qa_plan_chars(plan_id, element_id) THEN
561 RETURN NULL;
562 END IF;
563
564 RETURN x_qa_plan_chars_array(plan_element_index(plan_id, element_id)).
565 prompt;
566 END get_prompt;
567
568
569 --
570 -- This is a qa_plan_chars inquiry API.
571 --
572 FUNCTION get_uom_code (plan_id IN NUMBER,
573 element_id IN NUMBER) RETURN VARCHAR2 IS
574 BEGIN
575
576 fetch_qa_plan_chars(plan_id, element_id);
577 IF NOT exists_qa_plan_chars(plan_id, element_id) THEN
578 RETURN NULL;
579 END IF;
580
581 RETURN x_qa_plan_chars_array(plan_element_index(plan_id, element_id)).
582 uom_code;
583 END get_uom_code;
584
585
586 --
587 -- This is a qa_plan_chars inquiry API.
588 --
589 FUNCTION get_decimal_precision (plan_id IN NUMBER,
590 element_id IN NUMBER) RETURN VARCHAR2 IS
591 BEGIN
592
593 fetch_qa_plan_chars(plan_id, element_id);
594 IF NOT exists_qa_plan_chars(plan_id, element_id) THEN
595 RETURN NULL;
596 END IF;
597
598 RETURN x_qa_plan_chars_array(plan_element_index(plan_id, element_id)).
599 decimal_precision;
600 END get_decimal_precision;
601
602
603 FUNCTION keyflex (element_id IN NUMBER)
604 RETURN BOOLEAN IS
605
606 BEGIN
607 -- Added bill_reference,routing_reference,to_locator for NCM Hardode Elements.
608 -- Bug 2449067.
609
610 -- added the following to include new hardcoded element followup activity
611 -- saugupta Aug 2003
612
613 RETURN element_id IN (qa_ss_const.item, qa_ss_const.locator,
614 qa_ss_const.comp_item, qa_ss_const.comp_locator, qa_ss_const.asset_group, qa_ss_const.asset_activity, qa_ss_const.followup_activity, qa_ss_const.bill_reference, qa_ss_const.routing_reference, qa_ss_const.to_locator);
615 END keyflex;
616
617
618 FUNCTION normalized (element_id IN NUMBER)
619 RETURN BOOLEAN IS
620 BEGIN
621 RETURN (NOT keyflex(element_id))
622 AND (qa_chars_api.hardcoded_column(element_id) IS NOT NULL)
623 AND (qa_chars_api.fk_meaning(element_id) IS NOT NULL)
624 AND (qa_chars_api.fk_lookup_type(element_id) <> 2);
625 END normalized;
626
627
628 FUNCTION derived (element_id IN NUMBER)
629 RETURN VARCHAR2 IS
630 BEGIN
631 IF (keyflex(element_id) OR normalized(element_id)) THEN
632 RETURN 'Y';
633 ELSE
634 RETURN 'F';
635 END IF;
636 END derived;
637
638
639 FUNCTION primitive (element_id IN NUMBER)
640 RETURN BOOLEAN IS
641 BEGIN
642 RETURN (qa_chars_api.hardcoded_column(element_id) IS NOT NULL)
643 AND NOT (normalized(element_id));
644 END primitive;
645
646
647 FUNCTION hardcoded (element_id IN NUMBER)
648 RETURN BOOLEAN IS
649
650 BEGIN
651
652 RETURN qa_chars_api.hardcoded_column(element_id) IS NOT NULL;
653
654 END hardcoded;
655
656
657 FUNCTION get_element_datatype (element_id IN NUMBER)
658 RETURN NUMBER IS
659
660 BEGIN
661
662 RETURN qa_chars_api.datatype(element_id);
663
664 END get_element_datatype;
665
666
667 -- BUG 3303285
668 -- ksoh Mon Dec 29 13:33:02 PST 2003
669 -- this version of get_spec_limits is left for backward compatibility only.
670 -- please use the get_spec_limits that takes in plan_id
671 -- and performs uom conversion when necessary.
672 PROCEDURE get_spec_limits (spec_id IN NUMBER, element_id IN NUMBER,
673 lower_limit OUT NOCOPY VARCHAR2, upper_limit OUT NOCOPY VARCHAR2) IS
674
675 BEGIN
676
677 IF element_in_spec(spec_id, element_id) THEN
678 lower_limit := qsc_lower_reasonable_limit(spec_id, element_id);
679 upper_limit := qsc_upper_reasonable_limit(spec_id, element_id);
680 ELSE
681 -- Bug 3692326
682 -- shkalyan Tue Jun 22 2004
683 -- If spec_id is present but, element is not in spec, then,
684 -- element spec should not be used.
685 IF ( NVL(spec_id, 0) <= 0 ) THEN
686 lower_limit := qa_chars_api.lower_reasonable_limit(element_id);
687 upper_limit := qa_chars_api.upper_reasonable_limit(element_id);
688 ELSE
689 lower_limit := NULL;
690 upper_limit := NULL;
691 END IF;
692 END IF;
693
694 END get_spec_limits;
695
696
697 -- BUG 3303285
698 -- ksoh Mon Dec 29 13:33:02 PST 2003
699 -- overloaded get_spec_limits with a version that takes in plan_id
700 -- it is used for retrieving qa_plan_chars.uom_code for uom conversion
701 PROCEDURE get_spec_limits (p_plan_id IN NUMBER, p_spec_id IN NUMBER,
702 p_element_id IN NUMBER,
703 lower_limit OUT NOCOPY VARCHAR2, upper_limit OUT NOCOPY VARCHAR2) IS
704
705 CURSOR c IS
706 select uom_code
707 from qa_spec_chars
708 where spec_id = p_spec_id
709 and char_id = p_element_id;
710
711 -- 12.1 QWB Usabiltiy Improvements
712 -- Cursor to read the UOM defined on element Level
713 --
714 Cursor char_uom is
715 select uom_code
716 from qa_chars
717 where char_id = p_element_id;
718
719
720 l_plan_char_uom qa_plan_chars.uom_code%TYPE;
721 l_spec_char_uom qa_spec_chars.uom_code%TYPE;
722 l_decimal_precision NUMBER;
723 BEGIN
724 l_plan_char_uom := get_uom_code(p_plan_id, p_element_id);
725 l_decimal_precision := get_decimal_precision(p_plan_id, p_element_id);
726
727 IF element_in_spec(p_spec_id, p_element_id) THEN
728 lower_limit := qsc_lower_reasonable_limit(p_spec_id, p_element_id);
729 upper_limit := qsc_upper_reasonable_limit(p_spec_id, p_element_id);
730
731 -- find out spec element UOM and perform conversion
732 -- if they are different
733 -- NOTE that no conversion will be performed if one of them is null
734 OPEN c;
735 FETCH c INTO l_spec_char_uom;
736 CLOSE C;
737 IF l_plan_char_uom <> l_spec_char_uom THEN
738 IF (lower_limit IS NOT NULL) THEN
739 lower_limit := INV_CONVERT.INV_UM_CONVERT(null,
740 l_decimal_precision,
741 lower_limit,
742 l_spec_char_uom,
743 l_plan_char_uom,
744 null,
745 null);
746 END IF;
747 IF (upper_limit IS NOT NULL) THEN
748 upper_limit := INV_CONVERT.INV_UM_CONVERT(null,
749 l_decimal_precision,
750 upper_limit,
751 l_spec_char_uom,
752 l_plan_char_uom,
753 null,
754 null);
755 END IF;
756 IF ((lower_limit = -99999) OR (upper_limit = -99999)) THEN
757 fnd_message.set_name('QA', 'QA_INCONVERTIBLE_UOM');
758 fnd_message.set_token('ENTITY1', l_spec_char_uom);
759 fnd_message.set_token('ENTITY2', l_plan_char_uom);
760 fnd_msg_pub.add();
761 END IF;
762 END IF;
763 ELSE
764 -- Bug 3692326
765 -- shkalyan Tue Jun 22 2004
766 -- If spec_id is present but, element is not in spec, then,
767 -- element spec should not be used.
768
769 -- Spec id is NULL
770 IF ( NVL(p_spec_id, 0) <= 0 ) THEN
771
772 -- 12.1 QWB Usability Improvements
773 -- If the spec_id is NULL then we would the cursor and read the
774 -- UOM defined on the char level. Then fetch the lower and the
775 -- upper limits and perform the UOM conversion in case the UOM
776 -- defined for that element on the plan level is different from
777 -- that defined on the element level.
778 --
779 OPEN char_uom;
780 FETCH char_uom into l_spec_char_uom;
781 CLOSE char_uom;
782
783 -- Get the spec limits defined on the elemnt level
784 lower_limit := qa_chars_api.lower_reasonable_limit(p_element_id);
785 upper_limit := qa_chars_api.upper_reasonable_limit(p_element_id);
786
787 -- Check if the UOM on the plan level is different that that on the
788 -- element level in which case perform the conversion
789 IF l_plan_char_uom <> l_spec_char_uom THEN
790 -- perform UOM conversion for the lower spec limit
791 IF (lower_limit IS NOT NULL) THEN
792 lower_limit := INV_CONVERT.INV_UM_CONVERT(null,
793 l_decimal_precision,
794 lower_limit,
795 l_spec_char_uom,
796 l_plan_char_uom,
797 null,
798 null);
799 END IF;
800 -- perform UOM conversion for the upper spec limit
801 IF (upper_limit IS NOT NULL) THEN
802 upper_limit := INV_CONVERT.INV_UM_CONVERT(null,
803 l_decimal_precision,
804 upper_limit,
805 l_spec_char_uom,
806 l_plan_char_uom,
807 null,
808 null);
809 END IF;
810 IF ((lower_limit = -99999) OR (upper_limit = -99999)) THEN
811 fnd_message.set_name('QA', 'QA_INCONVERTIBLE_UOM');
812 fnd_message.set_token('ENTITY1', l_spec_char_uom);
813 fnd_message.set_token('ENTITY2', l_plan_char_uom);
814 fnd_msg_pub.add();
815 END IF;
816 END IF;
817 ELSE
818 lower_limit := NULL;
819 upper_limit := NULL;
820 END IF;
821 END IF;
822
823 END get_spec_limits;
824
825
826 -- BUG 3303285
827 -- ksoh Mon Jan 5 12:55:13 PST 2004
828 -- it is used for retrieving low/high value for evaluation of action triggers
829 -- it performs UOM conversion.
830 -- NOTE: p_spec_id can be 0 if not in use
831 PROCEDURE get_low_high_values (p_plan_id IN NUMBER, p_spec_id IN NUMBER,
832 p_element_id IN NUMBER,
833 p_low_value_lookup IN NUMBER,
834 p_high_value_lookup IN NUMBER,
835 x_low_value OUT NOCOPY VARCHAR2, x_high_value OUT NOCOPY VARCHAR2) IS
836
837 CURSOR c IS
838 SELECT
839 decode(p_spec_id,0,
840 decode(p_low_value_lookup,
841 7,qc.lower_reasonable_limit,
842 6,qc.lower_spec_limit,
843 5,qc.lower_user_defined_limit,
844 4,qc.target_value,
845 3,qc.upper_user_defined_limit,
846 2,qc.upper_spec_limit,
847 1,qc.upper_reasonable_limit,
848 NULL),
849 decode(p_low_value_lookup,
850 7,QscQs.lower_reasonable_limit,
851 6,QscQs.lower_spec_limit,
852 5,QscQs.lower_user_defined_limit,
853 4,QscQs.target_value,
854 3,QscQs.upper_user_defined_limit,
855 2,QscQs.upper_spec_limit,
856 1,QscQs.upper_reasonable_limit,
857 NULL)) LOW_VALUE,
858 decode(p_spec_id,0,
859 decode(p_high_value_lookup,
860 7,qc.lower_reasonable_limit,
861 6,qc.lower_spec_limit,
862 5,qc.lower_user_defined_limit,
863 4,qc.target_value,
864 3,qc.upper_user_defined_limit,
865 2,qc.upper_spec_limit,
866 1,qc.upper_reasonable_limit,
867 NULL),
868 decode(p_high_value_lookup,
869 7,QscQs.lower_reasonable_limit,
870 6,QscQs.lower_spec_limit,
871 5,QscQs.lower_user_defined_limit,
872 4,QscQs.target_value,
873 3,QscQs.upper_user_defined_limit,
874 2,QscQs.upper_spec_limit,
875 1,QscQs.upper_reasonable_limit,
876 NULL)) HIGH_VALUE,
877 nvl(QscQs.uom_code, qc.uom_code) SPEC_CHAR_UOM,
878 nvl(qpc.uom_code, qc.uom_code) PLAN_CHAR_UOM,
879 nvl(qpc.decimal_precision, qc.decimal_precision) DECIMAL_PRECISION
880 FROM
881 qa_chars qc,
882 qa_plan_chars qpc,
883 (select
884 qsc.CHAR_ID,
885 qsc.ENABLED_FLAG,
886 qsc.TARGET_VALUE,
887 qsc.UPPER_SPEC_LIMIT,
888 qsc.LOWER_SPEC_LIMIT,
889 qsc.UPPER_REASONABLE_LIMIT,
890 qsc.LOWER_REASONABLE_LIMIT,
891 qsc.UPPER_USER_DEFINED_LIMIT,
892 qsc.LOWER_USER_DEFINED_LIMIT,
893 qsc.UOM_CODE
894
895 from
896 qa_spec_chars qsc,
897 qa_specs qs
898
899 where
900 qsc.spec_id = qs.common_spec_id and
901 qs.spec_id = p_spec_id) QscQs
902
903 WHERE
904 qpc.plan_id = p_plan_id AND
905 qpc.enabled_flag = 1 AND
906 qc.char_id = qpc.char_id AND
907 qc.char_id = QscQs.char_id (+) AND
908 qpc.char_id = p_element_id;
909
910 l_plan_char_uom qa_plan_chars.uom_code%TYPE;
911 l_spec_char_uom qa_spec_chars.uom_code%TYPE;
912 l_decimal_precision NUMBER;
913
914 BEGIN
915 OPEN c;
916 FETCH c INTO x_low_value,
917 x_high_value,
918 l_spec_char_uom,
919 l_plan_char_uom,
920 l_decimal_precision;
921 CLOSE c;
922 IF (p_spec_id <> 0) AND
923 (l_plan_char_uom <> l_spec_char_uom) THEN
924 IF (x_low_value IS NOT NULL) THEN
925 x_low_value := INV_CONVERT.INV_UM_CONVERT(null,
926 l_decimal_precision,
927 x_low_value,
928 l_spec_char_uom,
929 l_plan_char_uom,
930 null,
931 null);
932 END IF;
933 IF (x_high_value IS NOT NULL) THEN
934 x_high_value := INV_CONVERT.INV_UM_CONVERT(null,
935 l_decimal_precision,
936 x_high_value,
937 l_spec_char_uom,
938 l_plan_char_uom,
939 null,
940 null);
941 END IF;
942 IF ((x_low_value = -99999) OR (x_high_value = -99999)) THEN
943 fnd_message.set_name('QA', 'QA_INCONVERTIBLE_UOM');
944 fnd_message.set_token('ENTITY1', l_spec_char_uom);
945 fnd_message.set_token('ENTITY2', l_plan_char_uom);
946 fnd_msg_pub.add();
947 END IF;
948 END IF;
949 END get_low_high_values;
950
951
952 FUNCTION values_exist (plan_id IN NUMBER, element_id IN NUMBER)
953 RETURN BOOLEAN IS
954
955 BEGIN
956
957 RETURN qpc_values_exist_flag(plan_id, element_id) = 1;
958
959 END values_exist;
960
961
962 FUNCTION sql_validation_exists (element_id IN NUMBER)
963 RETURN BOOLEAN IS
964
965 BEGIN
966
967 RETURN qa_chars_api.sql_validation_string(element_id) IS NOT NULL;
968
969 END sql_validation_exists;
970
971
972 FUNCTION element_in_plan (plan_id IN NUMBER, element_id IN NUMBER)
973 RETURN BOOLEAN IS
974
975 BEGIN
976
977 fetch_qa_plan_chars(plan_id, element_id);
978 RETURN exists_qa_plan_chars(plan_id, element_id);
979
980 END element_in_plan;
981
982
983 FUNCTION element_in_spec (spec_id IN NUMBER, element_id IN NUMBER)
984 RETURN BOOLEAN IS
985
986 BEGIN
987
988 fetch_qa_spec_chars(spec_id, element_id);
989 RETURN exists_qa_spec_chars(spec_id, element_id);
990
991 END element_in_spec;
992
993
994 FUNCTION get_actual_datatype (element_id IN NUMBER)
995 RETURN NUMBER IS
996
997 BEGIN
998
999 IF NOT hardcoded(element_id) THEN
1000 RETURN qa_ss_const.character_datatype;
1001
1002 ELSIF NOT primitive(element_id) THEN
1003 RETURN qa_ss_const.number_datatype;
1004
1005 ELSE
1006 RETURN qa_chars_api.datatype(element_id);
1007
1008 END IF;
1009
1010 END get_actual_datatype;
1011
1012
1013
1014 FUNCTION get_department_id (org_id IN NUMBER, value IN VARCHAR2)
1015 RETURN NUMBER IS
1016
1017 id NUMBER;
1018
1019 CURSOR c (d_code VARCHAR2, o_id NUMBER) IS
1020 SELECT department_id
1021 FROM bom_departments_val_v
1022 WHERE department_code = d_code
1023 AND organization_id = o_id;
1024
1025 BEGIN
1026
1027 IF value IS NULL THEN
1028 RETURN NULL;
1029 END IF;
1030
1031 OPEN c(value, org_id);
1032 FETCH c INTO id;
1033 CLOSE c;
1034
1035 RETURN id;
1036
1037 END get_department_id;
1038
1039
1040 FUNCTION get_job_id (org_id IN NUMBER, value IN VARCHAR2)
1041 RETURN NUMBER IS
1042
1043 id NUMBER;
1044
1045 -- #2382432
1046 -- Changed the view to WIP_DISCRETE_JOBS_ALL_V instead of
1047 -- earlier wip_open_discrete_jobs_val_v
1048 -- rkunchal Sun Jun 30 22:59:11 PDT 2002
1049
1050 CURSOR c (w_e_name VARCHAR2, o_id NUMBER) IS
1051 SELECT wip_entity_id
1052 FROM wip_discrete_jobs_all_v
1053 WHERE wip_entity_name = w_e_name
1054 AND organization_id = o_id;
1055
1056 BEGIN
1057
1058 IF value IS NULL THEN
1059 RETURN NULL;
1060 END IF;
1061
1062 OPEN c(value, org_id);
1063 FETCH c INTO id;
1064 CLOSE c;
1065
1066 RETURN id;
1067
1068 END get_job_id;
1069
1070
1071 FUNCTION get_production_line_id (org_id IN NUMBER, value IN VARCHAR2)
1072 RETURN NUMBER IS
1073
1074 id NUMBER;
1075
1076 CURSOR c (w_e_name VARCHAR2, o_id NUMBER) IS
1077 SELECT line_id
1078 FROM wip_lines_val_v
1079 WHERE line_code = w_e_name
1080 AND organization_id = o_id;
1081
1082 BEGIN
1083
1084 IF value IS NULL THEN
1085 RETURN NULL;
1086 END IF;
1087
1088 OPEN c(value, org_id);
1089 FETCH c INTO id;
1090 CLOSE c;
1091
1092 RETURN id;
1093
1094 END get_production_line_id;
1095
1096
1097 FUNCTION get_resource_code_id (org_id IN NUMBER, value IN VARCHAR2)
1098 RETURN NUMBER IS
1099
1100 id NUMBER;
1101
1102 CURSOR c (r_code VARCHAR2, o_id NUMBER) IS
1103 SELECT resource_id
1104 FROM bom_resources_val_v
1105 WHERE resource_code = r_code
1106 AND organization_id = o_id;
1107
1108 BEGIN
1109
1110 IF value IS NULL THEN
1111 RETURN NULL;
1112 END IF;
1113
1114 OPEN c(value, org_id);
1115 FETCH c INTO id;
1116 CLOSE c;
1117
1118 RETURN id;
1119
1120 END get_resource_code_id;
1121
1122
1123 FUNCTION get_supplier_id (value IN VARCHAR2)
1124 RETURN NUMBER IS
1125
1126 id NUMBER;
1127
1128 CURSOR c (v_name VARCHAR2) IS
1129 SELECT vendor_id
1130 FROM po_vendors
1131 WHERE vendor_name = v_name
1132 AND nvl(end_date_active, sysdate + 1) > sysdate;
1133
1134 BEGIN
1135
1136 IF value IS NULL THEN
1137 RETURN NULL;
1138 END IF;
1139
1140 OPEN c(value);
1141 FETCH c INTO id;
1142 CLOSE c;
1143
1144 RETURN id;
1145
1146 END get_supplier_id;
1147
1148
1149 FUNCTION get_po_number_id (value IN VARCHAR2)
1150 RETURN NUMBER IS
1151
1152 --
1153 -- R12 Project MOAC 4637896
1154 -- This function used to be invoked by mobile quality
1155 -- to derive po_header_id for use by some PO-related
1156 -- dependent elements, such as PO Line. After MOAC
1157 -- update, mobile will pass po_header_id directly to
1158 -- those dependent element LOVs, so this function is
1159 -- no longer in use.
1160 --
1161 -- The dependent elements are PO Line Number and
1162 -- PO Shipment Number
1163 --
1164 -- bso Sat Oct 8 12:20:50 PDT 2005
1165 --
1166 id NUMBER;
1167
1168 -- Bug 4958763. SQL Repository Fix SQL ID: 15008272
1169 -- Reverting back the changes for functionality
1170 CURSOR c (s VARCHAR2) IS
1171 SELECT po_header_id
1172 FROM PO_POS_VAL_TRX_V
1173 WHERE segment1 = s;
1174
1175 BEGIN
1176
1177 IF value IS NULL THEN
1178 RETURN NULL;
1179 END IF;
1180
1181 OPEN c(value);
1182 FETCH c INTO id;
1183 CLOSE c;
1184
1185 RETURN id;
1186
1187 END get_po_number_id;
1188
1189
1190 FUNCTION get_customer_id (value IN VARCHAR2)
1191 RETURN NUMBER IS
1192
1193 id NUMBER;
1194
1195 CURSOR c (c_name VARCHAR2) IS
1196 SELECT customer_id
1197 FROM qa_customers_lov_v
1198 WHERE status = 'A'
1199 AND customer_name = c_name
1200 AND nvl(customer_prospect_code, 'CUSTOMER') = 'CUSTOMER';
1201
1202 BEGIN
1203
1204 IF value IS NULL THEN
1205 RETURN NULL;
1206 END IF;
1207
1208 OPEN c(value);
1209 FETCH c INTO id;
1210 CLOSE c;
1211
1212 RETURN id;
1213
1214 END get_customer_id;
1215
1216
1217 FUNCTION get_so_number_id (value IN VARCHAR2)
1218 RETURN NUMBER IS
1219
1220 id NUMBER;
1221
1222 -- Bug 4958763. SQL Repository Fix SQL ID: 15008330
1223 -- reverting back the changes for functionality
1224 CURSOR c (v VARCHAR2) IS
1225 SELECT sales_order_id header_id
1226 FROM qa_sales_orders_lov_v
1227 WHERE order_number = v;
1228
1229 BEGIN
1230
1231 IF value IS NULL THEN
1232 RETURN NULL;
1233 END IF;
1234
1235 OPEN c(value);
1236 FETCH c INTO id;
1237 CLOSE c;
1238
1239 RETURN id;
1240
1241 END get_so_number_id;
1242
1243 --
1244 -- Bug 16214422
1245 --
1246 --FUNCTION get_so_line_number_id (value IN VARCHAR2)
1247 FUNCTION get_so_line_number_id (value IN VARCHAR2, p_sales_order_id IN NUMBER)
1248 RETURN NUMBER IS
1249
1250 id NUMBER;
1251
1252 -- bug 16214422
1253 /*
1254 CURSOR c (h_id IN VARCHAR2) IS
1255 SELECT sl.line_number
1256 FROM mtl_system_items_kfv msik, so_lines sl
1257 WHERE sl.inventory_item_id = msik.inventory_item_id
1258 AND header_id = h_id;
1259 */
1260
1261 CURSOR c (h_id IN VARCHAR2) IS
1262 SELECT oel.line_id
1263 FROM oe_order_headers_all oeh,
1264 oe_order_lines_all oel,
1265 mtl_sales_orders mso,
1266 oe_transaction_types_tl ot
1267 WHERE mso.segment1 = oeh.order_number
1268 AND oeh.order_type_id = ot.transaction_type_id
1269 AND mso.segment2 = ot.name
1270 AND mso.sales_order_id = p_sales_order_id
1271 AND oeh.header_id = oel.header_id
1272 AND TO_CHAR(oel.Line_number) = h_id;
1273
1274 BEGIN
1275
1276 IF value IS NULL THEN
1277 RETURN NULL;
1278 END IF;
1279
1280 OPEN c(value);
1281 FETCH c INTO id;
1282 CLOSE c;
1283
1284 RETURN id;
1285
1286 END get_so_line_number_id;
1287
1288
1289 FUNCTION get_po_release_number_id (value IN VARCHAR2, x_po_header_id IN NUMBER)
1290 RETURN NUMBER IS
1291
1292 id NUMBER;
1293
1294 CURSOR c (p_id NUMBER, r_num VARCHAR2) IS
1295 SELECT pr.po_release_id
1296 FROM po_releases pr
1297 WHERE pr.po_header_id = p_id
1298 AND pr.release_num = r_num;
1299
1300 BEGIN
1301
1302 IF value IS NULL THEN
1303 RETURN NULL;
1304 END IF;
1305
1306 OPEN c(x_po_header_id, value);
1307 FETCH c INTO id;
1308 CLOSE c;
1309
1310 RETURN id;
1311
1312 END get_po_release_number_id;
1313
1314
1315 FUNCTION get_project_number_id (value IN VARCHAR2)
1316 RETURN NUMBER IS
1317
1318 id NUMBER;
1319 /*
1320 mtl_project_v changed to pjm_projects_all_v (selects from both pjm enabled and
1321 non-pjm enabled orgs).
1322 rkaza, 11/10/2001.
1323 */
1324
1325 --
1326 -- the sql has to be changed as pjm_projects_all_v is operating unit sensitive.
1327 -- has to change to a sql that searching all operating units.
1328 -- reference bug 3578563
1329 -- jezheng
1330 -- Mon Apr 19 12:20:16 PDT 2004
1331 --
1332 /* CURSOR c (p_num VARCHAR2) IS
1333 SELECT project_id
1334 FROM pjm_projects_all_v
1335 WHERE project_number = p_num;
1336 */
1337
1338 cursor c (p_proj_num varchar2) is
1339 select project_id
1340 from pa_projects_all
1341 where segment1 = p_proj_num
1342 UNION ALL
1343 select project_id
1344 from pjm_seiban_numbers
1345 where project_number = p_proj_num;
1346
1347 BEGIN
1348
1349 IF value IS NULL THEN
1350 RETURN NULL;
1351 END IF;
1352
1353 OPEN c(value);
1354 FETCH c INTO id;
1355 CLOSE c;
1356
1357 RETURN id;
1358
1359 END get_project_number_id;
1360
1361
1362 --
1363 -- Bug 2672396. Added p_project_id because task is a dependent element.
1364 -- Fix is required in qltvalb.plb also.
1365 -- bso Mon Nov 25 17:29:56 PST 2002
1366 --
1367 FUNCTION get_task_number_id(value IN VARCHAR2, p_project_id IN NUMBER)
1368 RETURN NUMBER IS
1369
1370 id NUMBER;
1371
1372 --
1373 -- The sql is operating unit sensitive. Has to be changed to a sql
1374 -- that searches all operating units.
1375 -- reference bug 3578563
1376 -- jezheng
1377 -- Mon Apr 19 12:25:23 PDT 2004
1378 --
1379
1380 /* CURSOR c (p_task_number VARCHAR2, p_project_id NUMBER) IS
1381 SELECT task_id
1382 FROM mtl_task_v
1383 WHERE task_number = p_task_number AND project_id = p_project_id;
1384 */
1385
1386 cursor c (p_task_num varchar2, p_proj_id number) is
1387 select TASK_ID
1388 from pa_tasks
1389 where PROJECT_ID = p_proj_id and
1390 task_number = p_task_num;
1391
1392 BEGIN
1393
1394 IF value IS NULL THEN
1395 RETURN NULL;
1396 END IF;
1397
1398 OPEN c(value, p_project_id);
1399 FETCH c INTO id;
1400 CLOSE c;
1401
1402 RETURN id;
1403
1404 END get_task_number_id;
1405
1406
1407 FUNCTION get_rma_number_id (value IN VARCHAR2)
1408 RETURN NUMBER IS
1409
1410 id NUMBER;
1411
1412 CURSOR C(v VARCHAR2) IS
1413 SELECT sh.header_id
1414 FROM so_order_types sot,
1415 oe_order_headers sh,
1416 qa_customers_lov_v rc
1417 WHERE sh.order_type_id = sot.order_type_id and
1418 sh.sold_to_org_id = rc.customer_id and
1419 sh.order_category_code in ('RETURN', 'MIXED') and
1420 sh.order_number = v;
1421
1422 BEGIN
1423
1424 IF value IS NULL THEN
1425 RETURN NULL;
1426 END IF;
1427
1428 OPEN c(value);
1429 FETCH c INTO id;
1430 CLOSE c;
1431
1432 RETURN id;
1433
1434 END get_rma_number_id;
1435
1436 FUNCTION get_LPN_id (value IN VARCHAR2)
1437 RETURN NUMBER IS
1438
1439 id NUMBER;
1440
1441 CURSOR c (t_id VARCHAR2) IS
1442 SELECT LPN_ID
1443 FROM WMS_LICENSE_PLATE_NUMBERS
1444 WHERE LICENSE_PLATE_NUMBER = t_id;
1445
1446 BEGIN
1447
1448 IF value IS NULL THEN
1449 RETURN NULL;
1450 END IF;
1451
1452 OPEN c(value);
1453 FETCH c INTO id;
1454 CLOSE c;
1455
1456 RETURN id;
1457
1458 END get_LPN_id;
1459
1460 -- added the following to include new hardcoded element Transfer license plate number
1461 -- saugupta Aug 2003
1462
1463 FUNCTION get_XFR_LPN_id (value IN VARCHAR2)
1464 RETURN NUMBER IS
1465
1466 id NUMBER;
1467
1468 CURSOR c (t_id VARCHAR2) IS
1469 SELECT LPN_ID
1470 FROM WMS_LICENSE_PLATE_NUMBERS
1471 WHERE LICENSE_PLATE_NUMBER = t_id;
1472
1473 BEGIN
1474
1475 IF value IS NULL THEN
1476 RETURN NULL;
1477 END IF;
1478
1479 OPEN c(value);
1480 FETCH c INTO id;
1481 CLOSE c;
1482
1483 RETURN id;
1484
1485 END get_XFR_LPN_id;
1486
1487 FUNCTION get_contract_id (value IN VARCHAR2)
1488 RETURN NUMBER IS
1489
1490 id NUMBER := NULL;
1491
1492 CURSOR c (val VARCHAR2) IS
1493 SELECT k_header_id
1494 FROM oke_k_headers_lov_v
1495 WHERE k_number = val;
1496
1497 BEGIN
1498
1499 IF value is NOT NULL THEN
1500 OPEN c(value);
1501 FETCH c INTO id;
1502 CLOSE c;
1503 END IF;
1504
1505 RETURN id;
1506
1507 END get_contract_id;
1508
1509 FUNCTION get_contract_line_id (value IN VARCHAR2)
1510 RETURN NUMBER IS
1511
1512 id NUMBER := NULL;
1513
1514 CURSOR c (val VARCHAR2) IS
1515 SELECT k_line_id
1516 FROM oke_k_lines_full_v
1517 WHERE line_number = val;
1518
1519 BEGIN
1520
1521 IF value is NOT NULL THEN
1522 OPEN c(value);
1523 FETCH c INTO id;
1524 CLOSE c;
1525 END IF;
1526
1527 RETURN id;
1528
1529 END get_contract_line_id;
1530
1531 FUNCTION get_deliverable_id (value IN VARCHAR2)
1532 RETURN NUMBER IS
1533
1534 id NUMBER := NULL;
1535
1536 CURSOR c (val VARCHAR2) IS
1537 SELECT deliverable_id
1538 FROM oke_k_deliverables_vl
1539 WHERE deliverable_num = val;
1540
1541 BEGIN
1542
1543 IF value is NOT NULL THEN
1544 OPEN c(value);
1545 FETCH c INTO id;
1546 CLOSE c;
1547 END IF;
1548
1549 RETURN id;
1550
1551 END get_deliverable_id;
1552
1553
1554
1555 FUNCTION get_work_order_id (org_id IN NUMBER, value IN VARCHAR2)
1556 RETURN NUMBER IS
1557
1558 id NUMBER;
1559
1560 /* rkaza 10/21/2002. Bug 2635736 */
1561 CURSOR c (w_e_name VARCHAR2, o_id NUMBER) IS
1562 SELECT WDJ.wip_entity_id
1563 FROM wip_entities WE, wip_discrete_jobs WDJ
1564 WHERE WDJ.status_type in (3,4) and
1565 WDJ.wip_entity_id = WE.wip_entity_id and
1566 WE.entity_type IN (6, 7) and
1567 WE.wip_entity_name = w_e_name
1568 AND WDJ.organization_id = o_id;
1569
1570 BEGIN
1571
1572 IF value IS NULL THEN
1573 RETURN NULL;
1574 END IF;
1575
1576 OPEN c(value, org_id);
1577 FETCH c INTO id;
1578 CLOSE c;
1579
1580 RETURN id;
1581
1582 END get_work_order_id;
1583
1584
1585
1586 FUNCTION get_party_id (value IN VARCHAR2)
1587 RETURN NUMBER IS
1588
1589 id NUMBER;
1590
1591 CURSOR c (p_name VARCHAR2) IS
1592 SELECT party_id
1593 FROM hz_parties
1594 WHERE status = 'A'
1595 AND party_name = p_name
1596 AND party_type IN ('ORGANIZATION','PERSON')
1597 ORDER BY party_name;
1598
1599 BEGIN
1600
1601 IF value IS NULL THEN
1602 RETURN NULL;
1603 END IF;
1604
1605 OPEN c(value);
1606 FETCH c INTO id;
1607 CLOSE c;
1608
1609 RETURN id;
1610
1611 END get_party_id;
1612
1613 --
1614 -- Implemented the following get_id functions for
1615 -- Service_Item, Counter, Maintenance_Requirement,
1616 -- Service_Request, Rework_Job
1617 -- For ASO project
1618 -- rkunchal Thu Jul 25 01:43:48 PDT 2002
1619 --
1620
1621 FUNCTION get_item_instance_id (value IN VARCHAR2)
1622 RETURN NUMBER IS
1623
1624 id NUMBER;
1625
1626 CURSOR c (i_num VARCHAR2) IS
1627 SELECT cii.instance_id
1628 FROM qa_csi_item_instances cii, mtl_system_items_kfv msik
1629 WHERE cii.instance_number = i_num
1630 AND cii.last_vld_organization_id = msik.organization_id;
1631
1632 BEGIN
1633
1634 IF value IS NULL THEN
1635 RETURN NULL;
1636 END IF;
1637
1638 OPEN c(value);
1639 FETCH c INTO id;
1640 CLOSE c;
1641
1642 RETURN id;
1643
1644 END get_item_instance_id;
1645
1646
1647 FUNCTION get_counter_name_id (value IN VARCHAR2)
1648 RETURN NUMBER IS
1649
1650 id NUMBER;
1651 -- Bug 4958763. SQL Repository Fix SQL ID: 15008597
1652 -- to maintain consistency using IB view for counters
1653 -- replacing cs_counters with csi_counters_vl
1654 CURSOR c (c_name VARCHAR2) IS
1655 SELECT cc.counter_id
1656 FROM csi_counters_vl cc
1657 WHERE cc.name = c_name;
1658 /*
1659 SELECT cc.counter_id
1660 FROM cs_counters cc, cs_counter_groups ccg
1661 WHERE cc.counter_group_id = ccg.counter_group_id
1662 AND ccg.template_flag = 'N'
1663 AND cc.name = c_name;
1664 */
1665
1666 BEGIN
1667
1668 IF value IS NULL THEN
1669 RETURN NULL;
1670 END IF;
1671
1672 OPEN c(value);
1673 FETCH c INTO id;
1674 CLOSE c;
1675
1676 RETURN id;
1677
1678 END get_counter_name_id;
1679
1680
1681 FUNCTION get_maintenance_req_id (value IN VARCHAR2)
1682 RETURN NUMBER IS
1683
1684 id NUMBER;
1685
1686 CURSOR c (mr_title VARCHAR2) IS
1687 SELECT mr_header_id
1688 FROM qa_ahl_mr
1689 WHERE title = mr_title;
1690
1691 BEGIN
1692
1693 IF value IS NULL THEN
1694 RETURN NULL;
1695 END IF;
1696
1697 OPEN c(value);
1698 FETCH c INTO id;
1699 CLOSE c;
1700
1701 RETURN id;
1702
1703 END get_maintenance_req_id;
1704
1705
1706 FUNCTION get_service_request_id (value IN VARCHAR2)
1707 RETURN NUMBER IS
1708
1709 id NUMBER;
1710
1711 CURSOR c (s_request VARCHAR2) IS
1712 SELECT incident_id
1713 FROM cs_incidents
1714 WHERE incident_number = s_request;
1715
1716 BEGIN
1717
1718 IF value IS NULL THEN
1719 RETURN NULL;
1720 END IF;
1721
1722 OPEN c(value);
1723 FETCH c INTO id;
1724 CLOSE c;
1725
1726 RETURN id;
1727
1728 END get_service_request_id;
1729
1730
1731 FUNCTION get_rework_job_id (org_id IN NUMBER, value IN VARCHAR2)
1732 RETURN NUMBER IS
1733
1734 id NUMBER;
1735
1736 CURSOR c (w_e_name VARCHAR2, o_id NUMBER) IS
1737 SELECT wip_entity_id
1738 FROM wip_discrete_jobs_all_v
1739 WHERE wip_entity_name = w_e_name
1740 AND organization_id = o_id;
1741
1742 BEGIN
1743
1744 IF value IS NULL THEN
1745 RETURN NULL;
1746 END IF;
1747
1748 OPEN c(value, org_id);
1749 FETCH c INTO id;
1750 CLOSE c;
1751
1752 RETURN id;
1753
1754 END get_rework_job_id;
1755
1756 --
1757 -- End of inclusions for ASO project
1758 -- rkunchal Thu Jul 25 01:43:48 PDT 2002
1759 --
1760 -- R12 OPM Deviations. Bug 4345503 Start
1761
1762 FUNCTION get_process_batch_id (value IN VARCHAR2,p_org_id IN NUMBER)
1763 RETURN NUMBER IS
1764 id NUMBER;
1765 CURSOR c (batch_num VARCHAR2,o_id NUMBER) IS
1766 SELECT BATCH_ID
1767 FROM GME_BATCH_HEADER
1768 WHERE BATCH_NO = batch_num
1769 AND ( ORGANIZATION_ID IS NULL OR
1770 ORGANIZATION_ID = o_id );
1771 BEGIN
1772 IF value IS NULL THEN
1773 RETURN NULL;
1774 END IF;
1775
1776 OPEN c(value,p_org_id);
1777 FETCH c INTO id;
1778 CLOSE c;
1779
1780 RETURN id;
1781 END get_process_batch_id;
1782
1783 FUNCTION get_process_batchstep_id (value IN VARCHAR2,
1784 p_process_batch_id IN NUMBER)
1785 RETURN NUMBER IS
1786 id NUMBER;
1787 CURSOR c (l_batchstep_num VARCHAR2, l_batch_id NUMBER) is
1788 SELECT BATCHSTEP_ID
1789 FROM GME_BATCH_STEPS
1790 WHERE BATCHSTEP_NO = L_BATCHSTEP_NUM
1791 AND BATCH_ID = L_BATCH_ID;
1792 BEGIN
1793
1794 IF value IS NULL THEN
1795 RETURN NULL;
1796 END IF;
1797
1798 OPEN c(value,p_process_batch_id);
1799 FETCH c INTO id;
1800 CLOSE c;
1801
1802 RETURN id;
1803
1804 END get_process_batchstep_id;
1805
1806 FUNCTION get_process_operation_id (value IN VARCHAR2,
1807 p_process_batch_id IN NUMBER,
1808 p_process_batchstep_id IN NUMBER)
1809 RETURN NUMBER IS
1810 id NUMBER;
1811
1812 CURSOR c (l_operation VARCHAR2, l_batch_id NUMBER, l_batchstep_id VARCHAR2) is
1813 SELECT OPRN_ID
1814 FROM GMO_BATCH_STEPS_V
1815 WHERE OPERATION = L_OPERATION
1816 AND BATCH_ID = L_BATCH_ID
1817 AND BATCHSTEP_ID = L_BATCHSTEP_ID;
1818 BEGIN
1819 IF value IS NULL THEN
1820 RETURN NULL;
1821 END IF;
1822
1823 OPEN c(value,p_process_batch_id, p_process_batchstep_id);
1824 FETCH c INTO id;
1825 CLOSE c;
1826
1827 RETURN id;
1828
1829 END get_process_operation_id;
1830
1831 FUNCTION get_process_activity_id (value IN VARCHAR2,
1832 p_process_batch_id IN NUMBER,
1833 p_process_batchstep_id IN NUMBER)
1834 RETURN NUMBER IS
1835 id NUMBER;
1836 CURSOR c (l_activity VARCHAR2, l_batch_id NUMBER, l_batchstep_id NUMBER) is
1837 SELECT BATCHSTEP_ACTIVITY_ID
1838 FROM GME_BATCH_STEP_ACTIVITIES
1839 WHERE ACTIVITY = L_ACTIVITY
1840 AND BATCH_ID = L_BATCH_ID
1841 AND BATCHSTEP_ID = L_BATCHSTEP_ID ;
1842 BEGIN
1843 IF value IS NULL THEN
1844 RETURN NULL;
1845 END IF;
1846
1847 OPEN c(value,p_process_batch_id, p_process_batchstep_id);
1848 FETCH c INTO id;
1849 CLOSE c;
1850
1851 RETURN id;
1852
1853 END get_process_activity_id;
1854
1855 FUNCTION get_process_resource_id (value IN VARCHAR2,
1856 p_process_batch_id IN NUMBER,
1857 p_process_batchstep_id IN NUMBER,
1858 p_process_activity_id IN NUMBER)
1859 RETURN NUMBER IS
1860 id NUMBER;
1861 CURSOR c (l_resources VARCHAR2, l_batch_id NUMBER,
1862 l_batchstep_id NUMBER, l_activity_id NUMBER) is
1863 SELECT BATCHSTEP_RESOURCE_ID
1864 FROM GME_BATCH_STEP_RESOURCES
1865 WHERE RESOURCES = L_RESOURCES
1866 AND BATCH_ID = L_BATCH_ID
1867 AND BATCHSTEP_ID = L_BATCHSTEP_ID
1868 AND BATCHSTEP_ACTIVITY_ID = L_ACTIVITY_ID;
1869 BEGIN
1870 IF value IS NULL THEN
1871 RETURN NULL;
1872 END IF;
1873
1874 OPEN c(value,p_process_batch_id, p_process_batchstep_id,
1875 p_process_activity_id);
1876 FETCH c INTO id;
1877 CLOSE c;
1878
1879 RETURN id;
1880
1881 END get_process_resource_id;
1882
1883 FUNCTION get_process_parameter_id (value IN VARCHAR2,
1884 p_process_resource_id IN NUMBER)
1885 RETURN NUMBER IS
1886 id NUMBER;
1887 CURSOR c (l_parameter VARCHAR2, l_resource_id NUMBER) is
1888 SELECT GP.PARAMETER_ID
1889 FROM GMP_PROCESS_PARAMETERS GP, GME_PROCESS_PARAMETERS GE
1890 WHERE GP.PARAMETER_NAME = L_PARAMETER
1891 AND GP.PARAMETER_ID = GE.PARAMETER_ID
1892 AND GE.BATCHSTEP_RESOURCE_ID= L_RESOURCE_ID;
1893 BEGIN
1894 IF value IS NULL THEN
1895 RETURN NULL;
1896 END IF;
1897
1898 OPEN c(value,p_process_resource_id);
1899 FETCH c INTO id;
1900 CLOSE c;
1901
1902 RETURN id;
1903
1904 END get_process_parameter_id;
1905
1906 -- R12 OPM Deviations. Bug 4345503 End
1907
1908 /* R12 DR Integration. Bug 4345489 Start */
1909
1910 FUNCTION get_repair_line_id (value IN VARCHAR2)
1911 RETURN NUMBER
1912 IS
1913
1914 id NUMBER;
1915
1916 cursor c (p_ro_num varchar2) is
1917 select repair_line_id
1918 from csd_repairs
1919 where repair_number = p_ro_num;
1920
1921
1922 BEGIN
1923
1924 IF value IS NULL THEN
1925 RETURN NULL;
1926 END IF;
1927
1928 OPEN c(value);
1929 FETCH c INTO id;
1930 CLOSE c;
1931
1932 RETURN id;
1933
1934 END get_repair_line_id;
1935
1936
1937 FUNCTION get_jtf_task_id (value IN VARCHAR2)
1938 RETURN NUMBER
1939 IS
1940
1941 id NUMBER;
1942
1943 cursor c (p_task_num varchar2) is
1944 select task_id
1945 from jtf_tasks_b
1946 where task_number = p_task_num;
1947
1948
1949 BEGIN
1950
1951 IF value IS NULL THEN
1952 RETURN NULL;
1953 END IF;
1954
1955 OPEN c(value);
1956 FETCH c INTO id;
1957 CLOSE c;
1958
1959 RETURN id;
1960
1961 END get_jtf_task_id;
1962
1963 /* R12 DR Integration. Bug 4345489 End */
1964
1965 -- Start of inclusions for NCM Hardcode Elements.
1966 -- suramasw Thu Oct 31 10:48:59 PST 2002.
1967 -- Bug 2449067.
1968
1969
1970 FUNCTION validate_to_subinventory (x_org_id IN NUMBER, x_to_subinventory IN VARCHAR2)
1971 RETURN BOOLEAN IS
1972
1973 CURSOR c IS
1974 SELECT 1
1975 FROM mtl_secondary_inventories
1976 WHERE organization_id = x_org_id
1977 AND nvl(disable_date, sysdate+1) > sysdate
1978 AND secondary_inventory_name = x_to_subinventory;
1979
1980 result BOOLEAN;
1981 dummy NUMBER;
1982
1983 BEGIN
1984
1985 OPEN c;
1986 FETCH c INTO dummy;
1987 result := c%FOUND;
1988 CLOSE c;
1989
1990 RETURN result;
1991
1992 END validate_to_subinventory;
1993
1994 -- End of inclusions for NCM Hardcode Elements.
1995
1996 FUNCTION retrieve_id (sql_statement IN VARCHAR2)
1997 RETURN NUMBER IS
1998
1999 retrieved_id NUMBER;
2000
2001 BEGIN
2002
2003 EXECUTE IMMEDIATE sql_statement
2004 INTO retrieved_id;
2005
2006 RETURN retrieved_id;
2007
2008 EXCEPTION WHEN OTHERS THEN
2009 RAISE;
2010
2011 END retrieve_id;
2012
2013
2014 FUNCTION value_in_sql (sql_statement IN VARCHAR2, value IN VARCHAR2)
2015 RETURN BOOLEAN IS
2016
2017 indicator NUMBER;
2018 new_sql_statement VARCHAR2(10000);
2019
2020 BEGIN
2021
2022
2023 new_sql_statement := 'SELECT 1 FROM DUAL WHERE ' || '''' ||
2024 qa_core_pkg.dequote(value) || '''';
2025
2026 new_sql_statement := new_sql_statement || ' IN ' || '(' ||
2027 sql_statement || ')';
2028
2029
2030 EXECUTE IMMEDIATE new_sql_statement
2031 INTO indicator;
2032
2033 RETURN indicator = 1;
2034
2035 EXCEPTION WHEN OTHERS THEN
2036 RETURN FALSE;
2037
2038 END value_in_sql;
2039
2040
2041 FUNCTION validate_transaction_date(transaction_number IN NUMBER)
2042 RETURN BOOLEAN IS
2043
2044 result BOOLEAN DEFAULT TRUE;
2045 dummy NUMBER;
2046
2047 BEGIN
2048
2049
2050 IF (transaction_number = qa_ss_const.po_inspection_txn) THEN
2051 -- NEED FURTHER WORK: need to validate transaction date
2052 -- in this specific case
2053 result := FALSE;
2054 END IF;
2055
2056 RETURN result;
2057
2058 END validate_transaction_date;
2059
2060
2061 FUNCTION validate_uom(x_org_id IN NUMBER, x_item_id IN NUMBER,
2062 x_uom_code IN VARCHAR2) RETURN BOOLEAN IS
2063
2064 CURSOR c IS
2065 SELECT 1
2066 FROM mtl_item_uoms_view
2067 WHERE inventory_item_id = x_item_id AND
2068 organization_id = x_org_id AND
2069 uom_code = x_uom_code;
2070
2071 result BOOLEAN;
2072 dummy NUMBER;
2073
2074 BEGIN
2075
2076 OPEN c;
2077 FETCH c INTO dummy;
2078 result := c%FOUND;
2079 CLOSE c;
2080
2081 RETURN result;
2082
2083 END validate_uom;
2084
2085
2086 FUNCTION validate_revision (x_org_id IN NUMBER, x_item_id IN NUMBER,
2087 x_revision IN VARCHAR2)
2088 RETURN BOOLEAN IS
2089
2090 CURSOR c IS
2091 SELECT 1
2092 FROM mtl_item_revisions
2093 WHERE inventory_item_id = x_item_id AND
2094 organization_id = x_org_id AND
2095 revision = x_revision;
2096
2097 result BOOLEAN;
2098 dummy NUMBER;
2099
2100 BEGIN
2101
2102 OPEN c;
2103 FETCH c INTO dummy;
2104 result := c%FOUND;
2105 CLOSE c;
2106
2107 RETURN result;
2108
2109 END validate_revision;
2110
2111 FUNCTION validate_lot_num(x_org_id IN NUMBER, x_item_id IN NUMBER,
2112 x_lot_num IN VARCHAR2)
2113 RETURN BOOLEAN IS
2114
2115 CURSOR c IS
2116 SELECT 1
2117 FROM mtl_lot_numbers
2118 WHERE inventory_item_id = x_item_id AND
2119 organization_id = x_org_id AND
2120 lot_number = x_lot_num;
2121
2122 result BOOLEAN;
2123 dummy NUMBER;
2124
2125 BEGIN
2126
2127 OPEN c;
2128 FETCH c INTO dummy;
2129 result := c%FOUND;
2130 CLOSE c;
2131
2132 RETURN result;
2133
2134 END validate_lot_num;
2135
2136
2137 FUNCTION validate_serial_num(x_org_id IN NUMBER, x_item_id IN NUMBER,
2138 x_lot_num IN VARCHAR2, x_revision IN VARCHAR2, x_serial_num IN VARCHAR2)
2139 RETURN BOOLEAN IS
2140
2141
2142 -- Bug 3364660. Changed the cursor sql to use the nvl() for revision
2143 -- and lot number columns. kabalakr.
2144
2145 --
2146 -- Bug 3773298. Relaxing the where conditions such that if
2147 -- input lot number is null, we allow for all serial numbers
2148 -- for that item to pass validation. Same for revision.
2149 -- bso Tue Jul 20 15:20:37 PDT 2004
2150 --
2151 CURSOR c IS
2152 SELECT 1
2153 FROM mtl_serial_numbers
2154 WHERE inventory_item_id = x_item_id AND
2155 current_organization_id = x_org_id AND
2156 (x_lot_num IS NULL OR lot_number = x_lot_num) AND
2157 (x_revision IS NULL OR revision = x_revision) AND
2158 serial_number = x_serial_num;
2159
2160 result BOOLEAN;
2161 dummy NUMBER;
2162
2163 BEGIN
2164
2165 OPEN c;
2166 FETCH c INTO dummy;
2167 result := c%FOUND;
2168 CLOSE c;
2169
2170 RETURN result;
2171
2172 END validate_serial_num;
2173
2174
2175
2176 FUNCTION validate_subinventory (x_org_id IN NUMBER, x_subinventory IN VARCHAR2)
2177 RETURN BOOLEAN IS
2178
2179
2180 -- Bug 3381173. The Subinventory specified for Mobile LPN inspection could
2181 -- either be a storage or receiving sub. Hence changed the sql to accomodate
2182 -- both types of sub.
2183 -- kabalakr Tue Jan 27 02:18:59 PST 2004.
2184
2185 CURSOR c IS
2186 SELECT 1
2187 FROM mtl_secondary_inventories
2188 WHERE organization_id = x_org_id
2189 AND ((((SUBINVENTORY_TYPE <> 2) OR (SUBINVENTORY_TYPE IS NULL))
2190 AND nvl(disable_date, sysdate+1) > sysdate)
2191 OR (SUBINVENTORY_TYPE = 2))
2192 AND secondary_inventory_name = x_subinventory;
2193
2194 result BOOLEAN;
2195 dummy NUMBER;
2196
2197 BEGIN
2198
2199 OPEN c;
2200 FETCH c INTO dummy;
2201 result := c%FOUND;
2202 CLOSE c;
2203
2204 RETURN result;
2205
2206 END validate_subinventory;
2207
2208
2209 FUNCTION validate_lot_number (x_transaction_number IN NUMBER, x_transaction_id
2210 IN NUMBER, x_lot_number IN VARCHAR2)
2211 RETURN BOOLEAN IS
2212
2213 CURSOR c IS
2214 SELECT 1
2215 FROM mtl_transaction_lots_temp
2216 WHERE transaction_temp_id = x_transaction_id
2217 AND lot_number = x_lot_number;
2218
2219 result BOOLEAN;
2220 dummy NUMBER;
2221
2222 BEGIN
2223
2224 -- No validation done for direct data entry in which case
2225 -- the transaction number is going to be null
2226
2227 IF (x_transaction_number is NULL) THEN
2228 RETURN TRUE;
2229 END IF;
2230
2231 -- Only done for WIP Completion and Work Order Less Completions
2232
2233 IF (x_transaction_number NOT IN( qa_ss_const.wip_completion_txn,
2234 qa_ss_const.flow_work_order_less_txn)) THEN
2235 RETURN TRUE;
2236 END IF;
2237
2238 OPEN c;
2239 FETCH c INTO dummy;
2240 result := c%FOUND;
2241 CLOSE c;
2242
2243 RETURN result;
2244
2245 END validate_lot_number;
2246
2247
2248 FUNCTION validate_serial_number (x_transaction_number IN NUMBER,
2249 x_transaction_id IN NUMBER, x_lot_number IN VARCHAR2, x_serial_number IN
2250 VARCHAR2)
2251 RETURN BOOLEAN IS
2252
2253 --
2254 -- Bug 3758145. The original SQL is incorrect in transaction scenario.
2255 -- the WHERE conditions msn.line_mark_id should be rewritten as
2256 -- msn.lot_line_mark_id and vice versa.
2257 -- bso Tue Jul 20 15:52:21 PDT 2004
2258 --
2259 CURSOR c IS
2260 SELECT 1
2261 FROM mtl_serial_numbers msn,
2262 mtl_transaction_lots_temp mtlt
2263 WHERE msn.lot_line_mark_id = x_transaction_id
2264 AND mtlt.transaction_temp_id = msn.lot_line_mark_id
2265 AND mtlt.serial_transaction_temp_id = msn.line_mark_id
2266 AND mtlt.lot_number = x_lot_number
2267 AND x_lot_number IS NOT NULL
2268 AND msn.serial_number = x_serial_number
2269 UNION ALL
2270 SELECT 1
2271 FROM mtl_serial_numbers msn
2272 WHERE msn.line_mark_id = x_transaction_id
2273 AND x_lot_number IS NULL
2274 AND msn.serial_number = x_serial_number;
2275
2276 result BOOLEAN DEFAULT FALSE;
2277 dummy NUMBER;
2278
2279 BEGIN
2280
2281 -- Only done for WIP Completion and Work Order Less Completions
2282
2283 -- Bug 3364660. Return TRUE even if the x_transaction_number is NULL.
2284 -- kabalakr.
2285
2286 IF (x_transaction_number NOT IN( qa_ss_const.wip_completion_txn,
2287 qa_ss_const.flow_work_order_less_txn))
2288 OR (x_transaction_number IS NULL) THEN
2289
2290 RETURN TRUE;
2291 END IF;
2292
2293 OPEN c;
2294 FETCH c INTO dummy;
2295 result := c%FOUND;
2296 CLOSE c;
2297
2298 RETURN result;
2299
2300 END validate_serial_number;
2301
2302
2303 FUNCTION validate_op_seq_number (x_org_id IN NUMBER, x_line_id IN NUMBER,
2304 x_wip_entity_id IN NUMBER, x_op_seq_number IN VARCHAR2)
2305 RETURN BOOLEAN IS
2306
2307 CURSOR c1 IS
2308 SELECT 1
2309 FROM wip_operations_all_v
2310 WHERE organization_id = x_org_id
2311 AND wip_entity_id = x_wip_entity_id
2312 AND operation_seq_num = x_op_seq_number;
2313
2314 CURSOR c2 IS
2315 SELECT 1
2316 FROM wip_operations_all_v
2317 WHERE organization_id = x_org_id
2318 AND wip_entity_id = x_wip_entity_id
2319 AND operation_seq_num = x_op_seq_number
2320 AND repetitive_schedule_id =
2321 ( SELECT repetitive_schedule_id
2322 FROM wip_first_open_schedule_v
2323 WHERE organization_id = x_org_id
2324 AND wip_entity_id = x_wip_entity_id
2325 AND line_id = x_line_id );
2326
2327 result BOOLEAN DEFAULT FALSE;
2328 dummy NUMBER;
2329
2330 BEGIN
2331
2332 IF (x_line_id IS NULL) THEN
2333
2334 OPEN c1;
2335 FETCH c1 INTO dummy;
2336 result := c1%FOUND;
2337 CLOSE c1;
2338
2339 ELSE
2340
2341 OPEN c2;
2342 FETCH c2 INTO dummy;
2343 result := c2%FOUND;
2344 CLOSE c2;
2345
2346 END IF;
2347 RETURN result;
2348
2349 END validate_op_seq_number;
2350
2351 --
2352 -- See Bug 2588213
2353 -- To support the element Maintenance Op Seq Number
2354 -- to be used along with Maintenance Workorder
2355 -- rkunchal Mon Sep 23 23:46:28 PDT 2002
2356 --
2357 FUNCTION validate_maintenance_op_seq (x_org_id IN NUMBER,
2358 x_maintenance_work_order_id IN NUMBER,
2359 x_maintenance_op_seq IN VARCHAR2)
2360 RETURN BOOLEAN IS
2361
2362 CURSOR c IS
2363 SELECT 1
2364 FROM wip_operations_all_v
2365 WHERE organization_id = x_org_id
2366 AND wip_entity_id = x_maintenance_work_order_id
2367 AND operation_seq_num = x_maintenance_op_seq;
2368
2369 result BOOLEAN DEFAULT FALSE;
2370 dummy NUMBER;
2371
2372 BEGIN
2373
2374 OPEN c;
2375 FETCH c INTO dummy;
2376 result := c%FOUND;
2377 CLOSE c;
2378
2379 RETURN result;
2380
2381 END validate_maintenance_op_seq;
2382
2383 --
2384 -- End of inclusions for Bug 2588213
2385 --
2386
2387 FUNCTION validate_po_line_number (x_po_header_id IN NUMBER, x_po_line_number
2388 IN VARCHAR2)
2389 RETURN BOOLEAN IS
2390
2391 CURSOR c IS
2392 SELECT 1
2393 FROM PO_LINES_VAL_TRX_V
2394 WHERE po_header_id = x_po_header_id
2395 AND line_num = x_po_line_number;
2396
2397 result BOOLEAN;
2398 dummy NUMBER;
2399
2400 BEGIN
2401
2402 OPEN c;
2403 FETCH c INTO dummy;
2404 result := c%FOUND;
2405 CLOSE c;
2406
2407 RETURN result;
2408
2409 END validate_po_line_number;
2410
2411 --
2412 -- bug 9652549 CLM changes
2413 --
2414 FUNCTION validate_po_shipments (x_po_line_num IN VARCHAR2, x_po_header_id IN
2415 NUMBER, x_po_shipments IN VARCHAR2)
2416 RETURN BOOLEAN IS
2417
2418 --
2419 -- bug 9652549 CLM changes
2420 --
2421 -- Bug 4958763. SQL Repository Fix SQL ID: 15008958
2422 CURSOR c IS
2423 SELECT 1
2424 FROM po_line_locations
2425 WHERE po_line_id =
2426 ( SELECT po_line_id
2427 FROM PO_LINES_TRX_V
2428 WHERE line_num = x_po_line_num
2429 AND po_header_id = x_po_header_id)
2430 AND shipment_num = x_po_shipments;
2431 /*
2432 SELECT 1
2433 FROM po_shipments_all_v
2434 WHERE po_line_id =
2435 (SELECT po_line_id
2436 FROM po_lines_val_v
2437 WHERE line_num = x_po_line_num
2438 AND po_header_id = x_po_header_id)
2439 AND shipment_num = x_po_shipments;
2440 */
2441
2442 result BOOLEAN;
2443 dummy NUMBER;
2444
2445 BEGIN
2446
2447 OPEN c;
2448 FETCH c INTO dummy;
2449 result := c%FOUND;
2450 CLOSE c;
2451
2452 RETURN result;
2453
2454 END validate_po_shipments;
2455
2456
2457 FUNCTION validate_receipt_number (x_receipt_number IN VARCHAR2)
2458 RETURN BOOLEAN IS
2459
2460 /* -- Bug 4958763. SQL Repository Fix SQL ID: 15008972
2461 CURSOR c IS
2462 SELECT 1
2463 FROM RCV_SHIPMENT_HEADERS
2464 WHERE receipt_num = x_receipt_number
2465 AND RECEIPT_SOURCE_CODE = 'VENDOR';
2466 */
2467 --
2468 -- Bug 7491455.FP For bug 6800960.
2469 -- changed the query for validationg receipt number to include RMA receipts
2470 -- pdube Fri Oct 17 00:14:28 PDT 2008
2471 CURSOR c IS
2472 SELECT 1
2473 FROM RCV_SHIPMENT_HEADERS RCVSH,
2474 PO_VENDORS POV,
2475 RCV_TRANSACTIONS RT
2476 WHERE RCVSH.RECEIPT_SOURCE_CODE in ('VENDOR','CUSTOMER') AND
2477 RCVSH.VENDOR_ID = POV.VENDOR_ID(+) AND
2478 RT.SHIPMENT_HEADER_ID = RCVSH.SHIPMENT_HEADER_ID AND
2479 receipt_num = x_receipt_number;
2480 /*
2481 SELECT 1
2482 FROM rcv_receipts_all_v
2483 WHERE receipt_num = x_receipt_number;
2484 */
2485
2486 result BOOLEAN;
2487 dummy NUMBER;
2488
2489 BEGIN
2490
2491 OPEN c;
2492 FETCH c INTO dummy;
2493 result := c%FOUND;
2494 CLOSE c;
2495
2496 RETURN result;
2497
2498 END validate_receipt_number;
2499
2500
2501 FUNCTION get_target_element (plan_char_action_id IN NUMBER)
2502 RETURN NUMBER IS
2503
2504 target_element NUMBER;
2505
2506 CURSOR c1 (pca_id NUMBER) IS
2507 SELECT assigned_char_id
2508 FROM qa_plan_char_actions
2509 WHERE plan_char_action_id = pca_id;
2510
2511 BEGIN
2512
2513 OPEN c1(plan_char_action_id);
2514 FETCH c1 INTO target_element;
2515 CLOSE c1;
2516
2517 RETURN target_element;
2518
2519 EXCEPTION WHEN OTHERS THEN
2520 RAISE;
2521
2522 END get_target_element;
2523
2524
2525 FUNCTION get_enabled_flag (plan_id IN NUMBER, element_id IN NUMBER)
2526 RETURN NUMBER IS
2527 BEGIN
2528 RETURN qpc_enabled_flag(plan_id, element_id);
2529 END get_enabled_flag;
2530
2531 --
2532 -- See Bug 2624112
2533 -- The decimal precision for a number type collection
2534 -- element is to be configured at plan level.
2535 -- rkunchal Wed Oct 16 05:32:33 PDT 2002
2536 --
2537 -- New function to get the decimal precision for the element
2538 -- from the QA_PLAN_CHARS table.
2539 --
2540
2541 FUNCTION decimal_precision (p_plan_id IN NUMBER, p_element_id IN NUMBER)
2542 RETURN NUMBER IS
2543 BEGIN
2544 RETURN qpc_decimal_precision(p_plan_id, p_element_id);
2545 END decimal_precision;
2546
2547 FUNCTION get_mandatory_flag (plan_id IN NUMBER, element_id IN NUMBER)
2548 RETURN NUMBER IS
2549 BEGIN
2550 RETURN qpc_mandatory_flag(plan_id, element_id);
2551 END get_mandatory_flag;
2552
2553
2554 FUNCTION get_sql_validation_string (element_id IN NUMBER)
2555 RETURN VARCHAR2 IS
2556 BEGIN
2557 RETURN qa_chars_api.sql_validation_string(element_id);
2558 END get_sql_validation_string;
2559
2560
2561 FUNCTION get_result_column_name (plan_id IN NUMBER, element_id IN NUMBER)
2562 RETURN VARCHAR2 IS
2563
2564 --
2565 -- This is a function that returns the unique column name in the table
2566 -- qa_results given an element_id, plan_id combination.
2567 --
2568
2569 name VARCHAR2(30);
2570
2571 BEGIN
2572
2573 name := qa_chars_api.hardcoded_column(element_id);
2574
2575 IF (name IS NULL) THEN
2576 name := qpc_result_column_name(plan_id, element_id);
2577 END IF;
2578
2579 RETURN name;
2580
2581 END get_result_column_name;
2582
2583
2584 FUNCTION sql_string_exists(x_plan_id IN NUMBER, x_char_id IN NUMBER,
2585 org_id IN NUMBER, user_id IN NUMBER, value IN VARCHAR2,
2586 x_ref OUT NOCOPY LovRefCursor)
2587 RETURN BOOLEAN IS
2588
2589 sql_string VARCHAR2(3000);
2590 wild VARCHAR2(250);
2591
2592 BEGIN
2593
2594 -- Before Single Scan LOV
2595 -- wild := value || '%';
2596
2597 -- After Single Scan LOV
2598 wild := value;
2599
2600 IF values_exist(x_plan_id, x_char_id) THEN
2601 sql_string := 'SELECT short_code, description
2602 FROM qa_plan_char_value_lookups
2603 WHERE plan_id = :1
2604 AND char_id = :2
2605 AND short_code LIKE :3
2606 ORDER BY short_code';
2607 OPEN x_ref FOR sql_string USING x_plan_id, x_char_id, wild;
2608 RETURN TRUE;
2609
2610 ELSIF sql_validation_exists(x_char_id) THEN
2611
2612 sql_string := get_sql_validation_string(x_char_id);
2613 sql_string := qa_chars_api.format_sql_for_lov(sql_string,
2614 org_id, user_id);
2615
2616 --
2617 -- Bug 1474995. Adding filter to the user-defined SQL.
2618 --
2619 sql_string :=
2620 'select *
2621 from
2622 (select ''x'' code, ''x'' description
2623 from dual
2624 where 1 = 2
2625 union
2626 select * from
2627 ( '|| sql_string ||
2628 ' )) where code like :1';
2629
2630 OPEN x_ref FOR sql_string USING wild;
2631 RETURN TRUE;
2632
2633 ELSE
2634 RETURN FALSE;
2635 END IF;
2636
2637 END sql_string_exists;
2638
2639
2640 PROCEDURE get_department_lov(org_id IN NUMBER, value IN VARCHAR2,
2641 x_ref OUT NOCOPY LovRefCursor) IS
2642
2643 wild VARCHAR2(160);
2644 sql_string VARCHAR2(1500);
2645
2646 BEGIN
2647
2648 /* Before Single Scan LOV
2649 IF value IS NULL THEN
2650 wild := '%';
2651 ELSE
2652 wild := value || '%';
2653 END IF; */
2654
2655 -- After Single Scan LOV
2656 wild := value;
2657
2658 sql_string := 'SELECT department_code, description
2659 FROM bom_departments_val_v
2660 WHERE department_code like :1 AND
2661 organization_id = :2
2662 ORDER BY department_code';
2663 OPEN x_ref FOR sql_string USING wild, org_id;
2664
2665 END get_department_lov;
2666
2667
2668 PROCEDURE get_job_lov(org_id IN NUMBER, value IN VARCHAR2,
2669 x_ref OUT NOCOPY LovRefCursor) IS
2670
2671 wild VARCHAR2(160);
2672 sql_string VARCHAR2(1500);
2673
2674 BEGIN
2675
2676 /* Before Single Scan LOV
2677 IF value IS NULL THEN
2678 wild := '%';
2679 ELSE
2680 wild := value || '%';
2681 END IF; */
2682
2683 -- After Single Scan LOV
2684 wild := value;
2685
2686
2687 -- #2382432
2688 -- Changed the view to WIP_DISCRETE_JOBS_ALL_V instead of
2689 -- earlier wip_open_discrete_jobs_val_v
2690 -- rkunchal Sun Jun 30 22:59:11 PDT 2002
2691
2692 sql_string := 'SELECT wip_entity_name, description
2693 FROM wip_discrete_jobs_all_v
2694 WHERE wip_entity_name like :1 AND
2695 organization_id = :2
2696 ORDER BY wip_entity_name';
2697 OPEN x_ref FOR sql_string USING wild, org_id;
2698 END get_job_lov;
2699
2700
2701 PROCEDURE get_work_order_lov(org_id IN NUMBER, value IN VARCHAR2,
2702 x_ref OUT NOCOPY LovRefCursor) IS
2703
2704 wild VARCHAR2(160);
2705 sql_string VARCHAR2(1500);
2706
2707 BEGIN
2708
2709 /* Before Single Scan LOV
2710 IF value IS NULL THEN
2711 wild := '%';
2712 ELSE
2713 wild := value || '%';
2714 END IF; */
2715
2716 -- After Single Scan LOV
2717 wild := value;
2718
2719 /* rkaza 10/21/2002. Bug 2635736 */
2720 sql_string := 'select WE.wip_entity_name, WDJ.description
2721 from wip_entities WE, wip_discrete_jobs WDJ
2722 where WDJ.organization_id = :1 and
2723 WDJ.status_type in (3,4) and
2724 WDJ.wip_entity_id = WE.wip_entity_id and
2725 WE.entity_type IN (6, 7) and
2726 WE.wip_entity_name like :2
2727 order by WE.wip_entity_name';
2728
2729 OPEN x_ref FOR sql_string USING org_id, wild;
2730
2731 END get_work_order_lov;
2732
2733
2734
2735 PROCEDURE get_production_lov (org_id IN NUMBER, value IN VARCHAR2,
2736 x_ref OUT NOCOPY LovRefCursor) IS
2737
2738 wild VARCHAR2(160);
2739 sql_string VARCHAR2(1500);
2740
2741 BEGIN
2742
2743 /* Before Single Scan LOV
2744 IF value IS NULL THEN
2745 wild := '%';
2746 ELSE
2747 wild := value || '%';
2748 END IF; */
2749
2750 -- After Single Scan LOV
2751 wild := value;
2752
2753 sql_string := 'SELECT line_code, description
2754 FROM wip_lines_val_v
2755 WHERE line_code like :1 AND
2756 organization_id = :2
2757 ORDER BY line_code';
2758 OPEN x_ref FOR sql_string USING wild, org_id;
2759
2760 END get_production_lov;
2761
2762
2763 PROCEDURE get_resource_code_lov (org_id IN NUMBER, value IN VARCHAR2,
2764 x_ref OUT NOCOPY LovRefCursor) IS
2765
2766 wild VARCHAR2(160);
2767 sql_string VARCHAR2(1500);
2768
2769 BEGIN
2770
2771 /* Before Single Scan LOV
2772 IF value IS NULL THEN
2773 wild := '%';
2774 ELSE
2775 wild := value || '%';
2776 END IF; */
2777
2778 -- After Single Scan LOV
2779 wild := value;
2780
2781
2782 sql_string := 'SELECT resource_code, description
2783 FROM bom_resources_val_v
2784 WHERE resource_code like :1
2785 AND organization_id = :2
2786 ORDER BY resource_code';
2787
2788 OPEN x_ref FOR sql_string USING wild, org_id;
2789
2790 END get_resource_code_lov;
2791
2792
2793 PROCEDURE get_supplier_lov (value IN VARCHAR2, x_ref OUT NOCOPY LovRefCursor) IS
2794
2795 wild VARCHAR2(240);
2796 sql_string VARCHAR2(1500);
2797
2798 BEGIN
2799
2800 /* Before Single Scan LOV
2801 IF value IS NULL THEN
2802 wild := '%';
2803 ELSE
2804 wild := value || '%';
2805 END IF; */
2806
2807 -- After Single Scan LOV
2808 wild := value;
2809
2810 sql_string := 'SELECT vendor_name, segment1
2811 FROM po_vendors
2812 WHERE vendor_name like :1
2813 AND nvl(end_date_active, sysdate + 1) > sysdate
2814 ORDER BY vendor_name';
2815
2816 OPEN x_ref FOR sql_string USING wild;
2817
2818 END get_supplier_lov;
2819
2820
2821 PROCEDURE get_po_number_lov (value IN VARCHAR2, x_ref OUT NOCOPY LovRefCursor) IS
2822
2823 wild VARCHAR2(160);
2824 sql_string VARCHAR2(1500);
2825
2826 BEGIN
2827
2828 /* Before Single Scan LOV
2829 IF value IS NULL THEN
2830 wild := '%';
2831 ELSE
2832 wild := value || '%';
2833 END IF; */
2834
2835 -- After Single Scan LOV
2836 wild := value;
2837
2838 -- R12 Project MOAC 4637896
2839 -- Now select operating unit as an additional column.
2840 -- bso Sat Oct 8 12:21:06 PDT 2005
2841
2842 --
2843 -- bug 14038553
2844 -- Using the more optimal view
2845 --
2846 /*
2847 sql_string := 'SELECT po_header_id, segment1, vendor_name ||
2848 '' ('' || operating_unit || '')''
2849 FROM qa_po_numbers_lov_v
2850 WHERE segment1 like :1
2851 ORDER BY segment1';
2852 */
2853 sql_string := 'SELECT po_header_id, segment1, vendor_name ||
2854 '' ('' || OWNING_ORG || '')''
2855 FROM PO_POS_ALL_TRX_V
2856 WHERE segment1 like :1
2857 ORDER BY segment1';
2858
2859 OPEN x_ref FOR sql_string USING wild;
2860
2861 END get_po_number_lov;
2862
2863
2864 --
2865 -- Reference bug 2286796
2866 -- The lov query should be the same with the element Customer's
2867 -- sql_validation_string. customer_name is the column that should show
2868 -- first and populate to collection element Customer.
2869 -- Reversed the column order and order by customer_name instead of number
2870 -- jezheng
2871 -- Wed Apr 17 14:57:27 PDT 2002
2872 --
2873 PROCEDURE get_customer_lov (value IN VARCHAR2, x_ref OUT NOCOPY LovRefCursor) IS
2874
2875 wild VARCHAR2(160);
2876 sql_string VARCHAR2(1500);
2877
2878 BEGIN
2879
2880 /* Before Single Scan LOV
2881 IF value IS NULL THEN
2882 wild := '%';
2883 ELSE
2884 wild := value || '%';
2885 END IF; */
2886
2887 -- After Single Scan LOV
2888 wild := value;
2889
2890 sql_string := 'SELECT customer_name, customer_number
2891 FROM qa_customers_lov_v
2892 WHERE customer_name like :1
2893 AND status = ''A''
2894 AND nvl(customer_prospect_code, ''CUSTOMER'') =
2895 ''CUSTOMER''
2896 ORDER BY customer_name';
2897
2898 OPEN x_ref FOR sql_string USING wild;
2899
2900 END get_customer_lov;
2901
2902
2903 PROCEDURE get_so_number_lov (value IN VARCHAR2, x_ref OUT NOCOPY LovRefCursor) IS
2904
2905 wild VARCHAR2(160);
2906 sql_string VARCHAR2(1500);
2907
2908 BEGIN
2909
2910 /* Before Single Scan LOV
2911 IF value IS NULL THEN
2912 wild := '%';
2913 ELSE
2914 wild := value || '%';
2915 END IF; */
2916
2917 -- After Single Scan LOV
2918 wild := value;
2919
2920 -- Bug 4958763. Fixing it along with SQL repository fixes
2921 -- reverting back the changes for functionality
2922 sql_string := 'SELECT order_number, order_type name
2923 FROM qa_sales_orders_lov_v
2924 WHERE order_number like :1
2925 ORDER BY order_number';
2926
2927 OPEN x_ref FOR sql_string USING wild;
2928
2929 END get_so_number_lov;
2930
2931
2932 -- Bug 4958763. SQL Repository Fix SQL ID: 15009074
2933 -- "so_lines" is obsoleted in 11i. Replacing it with oe_order_lines
2934 -- also SO Line Number is not having LOV in Forms and QWB
2935 -- and so removing the lov in Mobile as well
2936 -- even after commenting out it does not require any change in Mobile code
2937 -- saugupta Wed, 08 Feb 2006 03:00:30 -0800 PDT
2938
2939 -- Bug 7716875.Setting LOV for SO Line Num based on SO Num
2940 -- Included so num in parameters.pdube Mon Apr 13 03:25:19 PDT 2009
2941 PROCEDURE get_so_line_number_lov (x_so_number IN VARCHAR2,
2942 value IN VARCHAR2,
2943 x_ref OUT NOCOPY LovRefCursor) IS
2944
2945 wild VARCHAR2(160);
2946 sql_string VARCHAR2(1500);
2947
2948 BEGIN
2949
2950 -- Bug 7716875.Added this query to get so_line_nums based on so.
2951 -- x_ref := NULL;
2952 wild := value;
2953 sql_string := 'select distinct to_char(oel.line_number) ,''Sales Order: ''|| '||
2954 'oeha.order_number || '';'' || ''Item: '' || oel.ordered_item description '||
2955 'from oe_order_lines_all oel, oe_order_headers_all oeha '||
2956 'where oel.header_id = oeha.header_id '||
2957 'and oeha.order_number = :1 ' ||
2958 'and to_char(oel.line_number) like :2 ' ||
2959 'order by description, line_number ';
2960 OPEN x_ref FOR sql_string USING x_so_number,wild;
2961
2962 /* Before Single Scan LOV
2963 IF value IS NULL THEN
2964 wild := '%';
2965 ELSE
2966 wild := value || '%';
2967 END IF;
2968
2969 -- After Single Scan LOV
2970 wild := value;
2971
2972 sql_string := 'SELECT to_char(sl.line_number), msik.concatenated_segments
2973 FROM mtl_system_items_kfv msik, so_lines sl
2974 WHERE sl.inventory_item_id = msik.inventory_item_id
2975 AND header_id like :1';
2976
2977 OPEN x_ref FOR sql_string USING wild;
2978 */
2979 END get_so_line_number_lov;
2980
2981
2982 PROCEDURE get_po_release_number_lov (p_po_header_id IN NUMBER, value IN VARCHAR2, x_ref OUT
2983 NOCOPY LovRefCursor) IS
2984
2985 wild VARCHAR2(160);
2986 sql_string VARCHAR2(1500);
2987
2988 BEGIN
2989
2990 /* Before Single Scan LOV
2991 IF value IS NULL THEN
2992 wild := '%';
2993 ELSE
2994 wild := value || '%';
2995 END IF; */
2996
2997 -- After Single Scan LOV
2998 wild := value;
2999 -- Bug 4958763. SQL Repository Fix SQL ID: 15009081
3000 /*
3001 sql_string := 'SELECT release_num, release_date
3002 FROM po_releases pr
3003 WHERE pr.release_num like :1
3004 ORDER BY pr.release_num';
3005 */
3006
3007 sql_string := 'SELECT release_num, release_date
3008 FROM po_releases
3009 WHERE po_header_id = :1
3010 AND release_num like :2
3011 ORDER BY release_num';
3012
3013 OPEN x_ref FOR sql_string USING p_po_header_id, wild;
3014
3015 END get_po_release_number_lov;
3016
3017 --
3018 -- Bug 9817478.
3019 -- Overloaded procedure which uses PO Number and PO Line Num to derive PO Release Num's LOV
3020 -- skolluku
3021 --
3022 PROCEDURE get_po_release_number_lov (p_po_header_id IN NUMBER, po_line_num IN VARCHAR2, value IN
3023 VARCHAR2, x_ref OUT NOCOPY LovRefCursor) IS
3024
3025 wild VARCHAR2(160);
3026 sql_string VARCHAR2(1500);
3027 l_po_line_id NUMBER;
3028
3029 BEGIN
3030
3031 -- After Single Scan LOV
3032 wild := value;
3033
3034 IF po_line_num IS NULL THEN
3035 l_po_line_id := NULL;
3036 ELSE
3037 SELECT po_line_id
3038 INTO l_po_line_id
3039 FROM po_lines_val_trx_v
3040 WHERE line_num = po_line_num
3041 AND po_header_id = p_po_header_id;
3042 END IF;
3043
3044 sql_string := 'SELECT DISTINCT to_char(pr.release_num) release_number,
3045 QLTDATE.date_to_user(pr.release_date)
3046 FROM po_releases pr,
3047 po_line_locations_trx_v pll
3048 WHERE pr.po_header_id = :1
3049 AND pr.po_header_id = pll.po_header_id
3050 AND pr.po_release_id = pll.po_release_id
3051 AND pll.po_line_id = NVL(:2, pll.po_line_id)
3052 AND pr.release_num LIKE :3
3053 ORDER BY pr.release_num';
3054
3055 OPEN x_ref FOR sql_string USING p_po_header_id, l_po_line_id, wild;
3056
3057 END get_po_release_number_lov;
3058
3059 PROCEDURE get_project_number_lov (value IN VARCHAR2, x_ref OUT
3060 NOCOPY LovRefCursor) IS
3061
3062 wild VARCHAR2(160);
3063 sql_string VARCHAR2(1500);
3064
3065 BEGIN
3066 /*
3067 mtl_project_v changed to pjm_projects_all_v (selects from both pjm enabled and
3068 non-pjm enabled orgs).
3069 rkaza, 11/10/2001.
3070 */
3071 /* Before Single Scan LOV
3072 IF value IS NULL THEN
3073 wild := '%';
3074 ELSE
3075 wild := value || '%';
3076 END IF; */
3077
3078 -- After Single Scan LOV
3079 wild := value;
3080
3081 --
3082 -- Bug 5249078. Changed pjm_projects_all_v to
3083 -- pjm_projects_v for MOAC compliance.
3084 -- bso Thu Jun 1 10:46:50 PDT 2006
3085 --
3086 sql_string := 'SELECT project_number, project_name
3087 FROM pjm_projects_v
3088 WHERE project_number like :1
3089 ORDER BY project_number';
3090
3091 OPEN x_ref FOR sql_string USING wild;
3092
3093 END get_project_number_lov;
3094
3095
3096 PROCEDURE get_task_number_lov (value IN VARCHAR2, x_ref OUT
3097 NOCOPY LovRefCursor) IS
3098
3099 wild VARCHAR2(160);
3100 sql_string VARCHAR2(1500);
3101
3102 BEGIN
3103
3104 /* Before Single Scan LOV
3105 IF value IS NULL THEN
3106 wild := '%';
3107 ELSE
3108 wild := value || '%';
3109 END IF; */
3110
3111 -- After Single Scan LOV
3112 wild := value;
3113
3114 sql_string := 'SELECT task_number, task_name
3115 FROM mtl_task_v
3116 WHERE task_number like :1
3117 ORDER BY task_number';
3118
3119 OPEN x_ref FOR sql_string USING wild;
3120
3121 END get_task_number_lov;
3122
3123
3124 PROCEDURE get_rma_number_lov (value IN VARCHAR2, x_ref OUT
3125 NOCOPY LovRefCursor) IS
3126
3127 wild VARCHAR2(160);
3128 sql_string VARCHAR2(1500);
3129
3130 BEGIN
3131
3132 /* Before Single Scan LOV
3133 IF value IS NULL THEN
3134 wild := '%';
3135 ELSE
3136 wild := value || '%';
3137 END IF; */
3138
3139 -- After Single Scan LOV
3140 wild := value;
3141
3142 sql_string := 'SELECT sh.order_number, sot.name
3143 FROM so_order_types sot,
3144 oe_order_headers sh,
3145 qa_customers_lov_v rc
3146 WHERE sh.order_type_id = sot.order_type_id and
3147 sh.sold_to_org_id = rc.customer_id and
3148 sh.order_category_code in (''RETURN'', ''MIXED'') and
3149 sh.order_number like :1
3150 ORDER BY sh.order_number';
3151
3152 OPEN x_ref FOR sql_string USING wild;
3153
3154 END get_rma_number_lov;
3155
3156 --
3157 -- Bug 6161802
3158 -- Added procedure to return lov sql for rma line number
3159 -- with rma number as a bind variable
3160 -- skolluku Tue Jul 17 23:47:13 PDT 2007
3161 --
3162 PROCEDURE get_rma_line_num_lov (x_rma_number IN VARCHAR2,
3163 value IN VARCHAR2, x_ref OUT NOCOPY LovRefCursor) IS
3164
3165 wild VARCHAR2(160);
3166 sql_string VARCHAR2(1500);
3167
3168 BEGIN
3169
3170 -- After Single Scan LOV
3171 wild := value;
3172
3173 sql_string := 'select distinct to_char(oel.line_number),
3174 ''RMA Number: '' || sh.order_number || '';'' || ''Item: '' || oel.ordered_item description
3175 from oe_order_lines oel, so_order_types sot, oe_order_headers sh
3176 where sh.order_type_id = sot.order_type_id and oel.header_id = sh.header_id and
3177 oel.line_category_code in (''RETURN'', ''MIXED'') and
3178 sh.order_number = :1 and
3179 to_char(oel.line_number) like :2
3180 order by description, line_number';
3181
3182 OPEN x_ref FOR sql_string USING x_rma_number, wild;
3183
3184 END get_rma_line_num_lov;
3185
3186 PROCEDURE get_LPN_lov (value IN VARCHAR2, x_ref OUT
3187 NOCOPY LovRefCursor) IS
3188
3189 wild VARCHAR2(160);
3190 sql_string VARCHAR2(1500);
3191
3192 BEGIN
3193
3194 /* Before Single Scan LOV
3195 IF value IS NULL THEN
3196 wild := '%';
3197 ELSE
3198 wild := value || '%';
3199 END IF; */
3200
3201 -- After Single Scan LOV
3202 wild := value;
3203
3204 sql_string := 'SELECT license_plate_number, attribute1
3205 FROM wms_license_plate_numbers
3206 WHERE license_plate_number like :1
3207 ORDER BY license_plate_number';
3208
3209 OPEN x_ref FOR sql_string USING wild;
3210
3211 END get_LPN_lov;
3212
3213 -- added the following to include new hardcoded element Transfer license plate number
3214 -- saugupta Aug 2003
3215
3216 PROCEDURE get_XFR_LPN_lov (value IN VARCHAR2, x_ref OUT
3217 NOCOPY LovRefCursor) IS
3218
3219 wild VARCHAR2(160);
3220 sql_string VARCHAR2(1500);
3221
3222 BEGIN
3223
3224 /* Before Single Scan LOV
3225 IF value IS NULL THEN
3226 wild := '%';
3227 ELSE
3228 wild := value || '%';
3229 END IF; */
3230
3231 -- After Single Scan LOV
3232 wild := value;
3233
3234 sql_string := 'SELECT license_plate_number, attribute1
3235 FROM wms_license_plate_numbers
3236 WHERE license_plate_number like :1
3237 ORDER BY license_plate_number';
3238
3239 OPEN x_ref FOR sql_string USING wild;
3240
3241 END get_XFR_LPN_lov;
3242
3243 PROCEDURE get_contract_lov (value IN VARCHAR2, x_ref OUT
3244 NOCOPY LovRefCursor) IS
3245
3246 filter VARCHAR2(160);
3247 sql_string VARCHAR2(1500);
3248
3249 BEGIN
3250
3251 /* Before Single Scan LOV
3252 filter := value || '%'; */
3253
3254 -- After Single Scan LOV
3255 filter := value;
3256
3257 sql_string := 'SELECT k_number, short_description
3258 FROM oke_k_headers_lov_v
3259 WHERE k_number like :1
3260 ORDER BY k_number';
3261
3262 OPEN x_ref FOR sql_string USING filter;
3263
3264 END get_contract_lov;
3265
3266 PROCEDURE get_contract_line_lov (value IN VARCHAR2, contract_number IN VARCHAR2,
3267 x_ref OUT NOCOPY LovRefCursor) IS
3268
3269 filter VARCHAR2(160);
3270 sql_string VARCHAR2(1500);
3271 contract_id NUMBER ;
3272
3273 BEGIN
3274
3275 /* Before Single Scan LOV
3276 filter := value || '%'; */
3277
3278 -- After Single Scan LOV
3279 filter := value;
3280
3281 contract_id := get_contract_id (contract_number);
3282 sql_string := 'SELECT line_number, line_description
3283 FROM oke_k_lines_full_v
3284 WHERE header_id = :1 AND
3285 line_number like :2
3286 ORDER BY line_number';
3287
3288 OPEN x_ref FOR sql_string USING contract_id, filter;
3289
3290 END get_contract_line_lov;
3291
3292 PROCEDURE get_deliverable_lov (value IN VARCHAR2, contract_number IN VARCHAR2,
3293 line_number IN VARCHAR2, x_ref OUT NOCOPY LovRefCursor) IS
3294
3295 filter VARCHAR2(160);
3296 sql_string VARCHAR2(1500);
3297 contract_id NUMBER;
3298 line_id NUMBER;
3299
3300 BEGIN
3301
3302 /* Before Single Scan LOV
3303 filter := value || '%'; */
3304
3305 -- After Single Scan LOV
3306 filter := value;
3307
3308 contract_id := get_contract_id (contract_number);
3309 line_id := get_contract_line_id (line_number);
3310
3311 sql_string := 'SELECT deliverable_num, description
3312 FROM oke_k_deliverables_vl
3313 WHERE k_header_id = :1 AND
3314 k_line_id = :2 AND
3315 deliverable_num like :3
3316 ORDER BY deliverable_num';
3317
3318 OPEN x_ref FOR sql_string USING contract_id, line_id, filter;
3319
3320 END get_deliverable_lov;
3321
3322
3323
3324 PROCEDURE get_uom_lov (x_org_id IN NUMBER, x_item_name IN VARCHAR2,
3325 value IN VARCHAR2, x_ref OUT NOCOPY LovRefCursor) IS
3326
3327 wild VARCHAR2(160);
3328 sql_string VARCHAR2(1500);
3329 x_item_id NUMBER DEFAULT NULL;
3330
3331 BEGIN
3332
3333 -- This procedure is used for both uom and component uom
3334
3335 /* Before Single Scan LOV
3336 IF value IS NULL THEN
3337 wild := '%';
3338 ELSE
3339 wild := value || '%';
3340 END IF; */
3341
3342 -- After Single Scan LOV
3343 wild := value;
3344
3345 x_item_id := qa_flex_util.get_item_id(x_org_id, x_item_name);
3346
3347 sql_string := 'SELECT uom_code, description
3348 FROM mtl_item_uoms_view
3349 WHERE inventory_item_id = :1
3350 AND organization_id = :2
3351 AND uom_code like :3
3352 ORDER BY uom_code';
3353
3354 OPEN x_ref FOR sql_string USING x_item_id, x_org_id, wild;
3355
3356 END get_uom_lov;
3357
3358
3359 PROCEDURE get_revision_lov (x_org_id IN NUMBER, x_item_name IN VARCHAR2,
3360 value IN VARCHAR2, x_ref OUT NOCOPY LovRefCursor) IS
3361
3362 wild VARCHAR2(160);
3363 sql_string VARCHAR2(1500);
3364 x_item_id NUMBER DEFAULT NULL;
3365
3366 BEGIN
3367
3368 -- This procedure is used for both revision and component revision
3369
3370 /* Before Single Scan LOV
3371 IF value IS NULL THEN
3372 wild := '%';
3373 ELSE
3374 wild := value || '%';
3375 END IF; */
3376
3377 -- After Single Scan LOV
3378 wild := value;
3379
3380 x_item_id := qa_flex_util.get_item_id(x_org_id, x_item_name);
3381
3382 -- Bug 3595553. Modified the below query to join with msi to fetch
3383 -- revisions only if the item is revision controlled. All the items
3384 -- will have a base revision by default. But we want this sql to
3385 -- fetch revision only if the item is revision controlled. kabalakr.
3386
3387 sql_string := 'SELECT mir.revision, mir.effectivity_date
3388 FROM mtl_item_revisions mir, mtl_system_items msi
3389 WHERE mir.inventory_item_id = :1
3390 AND mir.organization_id = :2
3391 AND mir.revision like :3
3392 AND mir.inventory_item_id = msi.inventory_item_id
3393 AND mir.organization_id = msi.organization_id
3394 AND msi.revision_qty_control_code = 2
3395 ORDER BY revision';
3396
3397 OPEN x_ref FOR sql_string USING x_item_id, x_org_id, wild;
3398
3399 END get_revision_lov;
3400
3401 PROCEDURE get_lot_num_lov (x_org_id IN NUMBER, x_item_name IN VARCHAR2,
3402 value IN VARCHAR2, x_ref OUT NOCOPY LovRefCursor) IS
3403
3404 wild VARCHAR2(160);
3405 sql_string VARCHAR2(1500);
3406 x_item_id NUMBER DEFAULT NULL;
3407
3408 BEGIN
3409
3410 /* Before Single Scan LOV
3411 IF value IS NULL THEN
3412 wild := '%';
3413 ELSE
3414 wild := value || '%';
3415 END IF; */
3416
3417 -- After Single Scan LOV
3418 wild := value;
3419
3420 x_item_id := qa_flex_util.get_item_id(x_org_id, x_item_name);
3421
3422 sql_string := 'select lot_number, description
3423 from mtl_lot_numbers
3424 where inventory_item_id = :1
3425 and organization_id = :2
3426 and lot_number like :3
3427 and (disable_flag = 2 or disable_flag is null)
3428 ORDER BY lot_number';
3429
3430 OPEN x_ref FOR sql_string USING x_item_id, x_org_id, wild;
3431
3432 END get_lot_num_lov;
3433
3434 PROCEDURE get_serial_num_lov (x_org_id IN NUMBER, x_item_name IN VARCHAR2,
3435 x_lot_number IN VARCHAR2,
3436 x_revision IN VARCHAR2,
3437 value IN VARCHAR2, x_ref OUT NOCOPY LovRefCursor) IS
3438
3439 wild VARCHAR2(160);
3440 sql_string VARCHAR2(1500);
3441 x_item_id NUMBER DEFAULT NULL;
3442
3443 BEGIN
3444
3445 wild := value;
3446
3447 x_item_id := qa_flex_util.get_item_id(x_org_id, x_item_name);
3448
3449 -- Undoing Bug 3364660. Not needed after sync up with Bug 3773298
3450 -- l_trans_string := fnd_message.get_string('QA','QA_MOBILE_SERIAL_LOV_TXT');
3451 --
3452 -- Bug 3773298
3453 -- Sync up the SQL to be the same with forms.
3454 -- bso Tue Jul 20 16:12:06 PDT 2004
3455 --
3456 sql_string := 'select serial_number, current_status_name
3457 from mtl_serial_numbers_all_v
3458 where current_organization_id = :1
3459 and inventory_item_id = :2
3460 and (:3 is null OR lot_number = :4)
3461 and (:5 is null OR revision = :6)
3462 and serial_number like :7
3463 order by 1';
3464
3465 OPEN x_ref FOR sql_string USING x_org_id , x_item_id, x_lot_number, x_lot_number, x_revision, x_revision, wild;
3466
3467 END get_serial_num_lov;
3468
3469 --dgupta: Start R12 EAM Integration. Bug 4345492
3470 PROCEDURE get_asset_instance_number_lov (p_org_id IN NUMBER, p_asset_group IN VARCHAR2,
3471 p_asset_number IN VARCHAR2, value IN VARCHAR2, x_ref OUT NOCOPY LovRefCursor) IS
3472
3473 wild VARCHAR2(160);
3474 sql_string VARCHAR2(1500);
3475 l_asset_group_id NUMBER DEFAULT NULL;
3476
3477 BEGIN
3478 -- After Single Scan LOV
3479 wild := value;
3480
3481 l_asset_group_id := get_asset_group_id(p_org_id, p_asset_group);
3482
3483 sql_string := 'SELECT
3484 cii.instance_number, cii.instance_description
3485 FROM
3486 csi_item_instances cii, mtl_system_items_b msib, mtl_parameters mp
3487 WHERE
3488 msib.organization_id = mp.organization_id and
3489 msib.organization_id = cii.last_vld_organization_id and
3490 msib.inventory_item_id = cii.inventory_item_id and
3491 msib.eam_item_type in (1,3) and
3492 msib.serial_number_control_code <> 1 and
3493 sysdate between nvl(cii.active_start_date, sysdate-1)
3494 and nvl(cii.active_end_date, sysdate+1) and
3495 mp.maint_organization_id = :1 and
3496 cii.inventory_item_id = nvl(:2, cii.inventory_item_id) and
3497 cii.instance_number like :3 and
3498 cii.serial_number = nvl(:4, cii. serial_number)
3499 order by cii.instance_number';
3500
3501 OPEN x_ref FOR sql_string USING p_org_id , l_asset_group_id, wild, p_asset_number;
3502
3503 END get_asset_instance_number_lov;
3504 --dgupta: End R12 EAM Integration. Bug 4345492
3505
3506
3507 PROCEDURE get_asset_number_lov (x_org_id IN NUMBER, x_asset_group IN VARCHAR2,
3508 x_asset_instance_number IN VARCHAR2, --R12 EAM Integration. Bug 4345492
3509 value IN VARCHAR2, x_ref OUT NOCOPY LovRefCursor) IS
3510
3511 wild VARCHAR2(160);
3512 sql_string VARCHAR2(1500);
3513 x_asset_group_id NUMBER DEFAULT NULL;
3514 x_asset_instance_id NUMBER DEFAULT NULL; --R12 EAM Integration. Bug 4345492
3515
3516 BEGIN
3517
3518 /* Before Single Scan LOV
3519 IF value IS NULL THEN
3520 wild := '%';
3521 ELSE
3522 wild := value || '%';
3523 END IF; */
3524
3525 -- After Single Scan LOV
3526 wild := value;
3527
3528 --dgupta: Start R12 EAM Integration. Bug 4345492
3529 x_asset_group_id := get_asset_group_id(x_org_id, x_asset_group);
3530 x_asset_instance_id := get_asset_instance_id(x_asset_instance_number);
3531
3532 sql_string := 'SELECT
3533 distinct msn.serial_number, msn.descriptive_text
3534 FROM
3535 mtl_serial_numbers msn, csi_item_instances cii, mtl_system_items_b msib, mtl_parameters mp
3536 WHERE
3537 msib.organization_id = mp.organization_id and
3538 msib.organization_id = cii.last_vld_organization_id and
3539 msib.inventory_item_id = cii.inventory_item_id and
3540 msib.eam_item_type in (1,3) and
3541 sysdate between nvl(cii.active_start_date(+), sysdate-1)
3542 and nvl(cii.active_end_date(+), sysdate+1) and
3543 msib.organization_id = msn.current_organization_id and
3544 msib.inventory_item_id = msn.inventory_item_id and
3545 mp.maint_organization_id = :1 and
3546 msn.inventory_item_id = :2 and --removed nvl: serial number requires asset group as well
3547 msn.serial_number like :3 and
3548 cii.instance_id= nvl(:4, cii.instance_id)
3549 order by msn.serial_number';
3550
3551 OPEN x_ref FOR sql_string USING x_org_id , x_asset_group_id, wild, x_asset_instance_id;
3552 --dgupta: End R12 EAM Integration. Bug 4345492
3553
3554 END get_asset_number_lov;
3555
3556
3557 PROCEDURE get_subinventory_lov (x_org_id IN NUMBER, value IN VARCHAR2,
3558 x_ref OUT NOCOPY LovRefCursor) IS
3559
3560 wild VARCHAR2(160);
3561 sql_string VARCHAR2(1500);
3562
3563 BEGIN
3564
3565 -- This procedure is used for both subinventory and component subinventory
3566
3567 /* Before Single Scan LOV
3568 IF value IS NULL THEN
3569 wild := '%';
3570 ELSE
3571 wild := value || '%';
3572 END IF; */
3573
3574 -- After Single Scan LOV
3575 wild := value;
3576
3577 sql_string := 'SELECT secondary_inventory_name, description
3578 FROM mtl_secondary_inventories
3579 WHERE organization_id = :1
3580 AND nvl(disable_date, sysdate+1) > sysdate
3581 AND secondary_inventory_name like :2
3582 ORDER BY secondary_inventory_name';
3583
3584 OPEN x_ref FOR sql_string USING x_org_id, wild;
3585
3586 END get_subinventory_lov;
3587
3588
3589 PROCEDURE get_lot_number_lov (x_transaction_id IN NUMBER, value IN VARCHAR2,
3590 x_ref OUT NOCOPY LovRefCursor) IS
3591
3592 wild VARCHAR2(160);
3593 sql_string VARCHAR2(1500);
3594
3595 BEGIN
3596
3597 /* Before Single Scan LOV
3598 IF value IS NULL THEN
3599 wild := '%';
3600 ELSE
3601 wild := value || '%';
3602 END IF; */
3603
3604 -- After Single Scan LOV
3605 wild := value;
3606
3607 sql_string := 'SELECT lot_number, lot_expiration_date
3608 FROM mtl_transaction_lots_temp
3609 WHERE transaction_temp_id = :1
3610 AND lot_number like :2
3611 ORDER BY lot_number';
3612
3613 OPEN x_ref FOR sql_string USING x_transaction_id, wild;
3614
3615 END get_lot_number_lov;
3616
3617
3618 PROCEDURE get_serial_number_lov (x_transaction_id IN NUMBER, x_lot_number
3619 IN VARCHAR2, value IN VARCHAR2, x_ref OUT NOCOPY LovRefCursor) IS
3620
3621 wild VARCHAR2(160);
3622 sql_string VARCHAR2(1500);
3623
3624 BEGIN
3625
3626 wild := value;
3627
3628 --
3629 -- Bug 3758145. The original SQL is incorrect in transaction scenario.
3630 -- the WHERE conditions msn.line_mark_id should be rewritten as
3631 -- msn.lot_line_mark_id and vice versa.
3632 -- bso Tue Jul 20 15:52:21 PDT 2004
3633 --
3634 sql_string := 'SELECT msn.serial_number, msn.current_status
3635 FROM mtl_serial_numbers msn,
3636 mtl_transaction_lots_temp mtlt
3637 WHERE msn.lot_line_mark_id = :1
3638 AND mtlt.transaction_temp_id = msn.lot_line_mark_id
3639 AND mtlt.serial_transaction_temp_id = msn.line_mark_id
3640 AND mtlt.lot_number = :2
3641 AND :3 IS NOT NULL
3642 AND msn.serial_number like :4
3643 UNION ALL
3644 SELECT msn.serial_number, msn.current_status
3645 FROM mtl_serial_numbers msn
3646 WHERE msn.line_mark_id = :5
3647 AND :6 IS NULL
3648 AND msn.serial_number like :7
3649 ORDER BY 1';
3650
3651 OPEN x_ref FOR sql_string USING x_transaction_id, x_lot_number,
3652 x_lot_number, wild, x_transaction_id, x_lot_number, wild;
3653
3654 END get_serial_number_lov;
3655
3656 --
3657 -- Removed the DEFAULT clause to make the code GSCC compliant
3658 -- List of changed arguments.
3659 -- Old
3660 -- production_line IN VARCHAR2 DEFAULT NULL
3661 -- New
3662 -- production_line IN VARCHAR2
3663 --
3664
3665 PROCEDURE get_op_seq_number_lov (org_id IN NUMBER, value IN VARCHAR2,
3666 job_name IN VARCHAR2, production_line IN VARCHAR2,
3667 x_ref OUT NOCOPY LovRefCursor) IS
3668
3669 wild VARCHAR2(160);
3670 sql_string VARCHAR2(1500);
3671 x_line_id NUMBER DEFAULT NULL;
3672 x_wip_entity_id NUMBER DEFAULT NULL;
3673
3674 BEGIN
3675
3676 /* Before Single Scan LOV
3677 IF value IS NULL THEN
3678 wild := '%';
3679 ELSE
3680 wild := value || '%';
3681 END IF; */
3682
3683 -- After Single Scan LOV
3684 wild := value;
3685
3686 IF (production_line IS NOT NULL) THEN
3687 x_line_id := get_production_line_id(org_id, production_line);
3688 END IF;
3689
3690 x_wip_entity_id := get_job_id(org_id, job_name);
3691
3692
3693 IF (x_line_id IS NULL) THEN
3694
3695 sql_string := 'SELECT operation_seq_num, operation_code
3696 FROM wip_operations_all_v
3697 WHERE operation_seq_num like :1
3698 AND wip_entity_id = :2
3699 AND organization_id = :3
3700 ORDER BY operation_seq_num';
3701
3702 OPEN x_ref FOR sql_string USING wild, x_wip_entity_id, org_id;
3703
3704 ELSE
3705
3706 sql_string := 'SELECT operation_seq_num, operation_code
3707 FROM wip_operations_all_v
3708 WHERE operation_seq_num like :1
3709 AND wip_entity_id = :2
3710 AND organization_id = :3
3711 AND repetitive_schedule_id =
3712 (
3713 SELECT repetitive_schedule_id
3714 FROM wip_first_open_schedule_v
3715 WHERE line_id = :4
3716 AND wip_entity_id = :5
3717 AND organization_id = :6
3718 )
3719 ORDER BY operation_seq_num';
3720
3721 OPEN x_ref FOR sql_string USING wild, x_wip_entity_id, org_id,
3722 x_line_id, x_wip_entity_id, org_id;
3723
3724 END IF;
3725
3726 END get_op_seq_number_lov;
3727
3728 --
3729 -- See Bug 2588213
3730 -- To support the element Maintenance Op Seq Number
3731 -- to be used along with Maintenance Workorder
3732 -- rkunchal Mon Sep 23 23:46:28 PDT 2002
3733 --
3734
3735 PROCEDURE get_maintenance_op_seq_lov(org_id IN NUMBER,
3736 value IN VARCHAR2,
3737 maintenance_work_order IN VARCHAR2,
3738 x_ref OUT NOCOPY LovRefCursor) IS
3739 wild VARCHAR2(160);
3740 sql_string VARCHAR2(1500);
3741 x_wip_entity_id NUMBER DEFAULT NULL;
3742
3743 BEGIN
3744
3745 /* Before Single Scan LOV
3746 IF value IS NULL THEN
3747 wild := '%';
3748 ELSE
3749 wild := value || '%';
3750 END IF; */
3751
3752 -- After Single Scan LOV
3753 wild := value;
3754
3755 x_wip_entity_id := get_job_id(org_id, maintenance_work_order);
3756
3757 sql_string := 'SELECT operation_seq_num, operation_code
3758 FROM wip_operations_all_v
3759 WHERE operation_seq_num like :1
3760 AND wip_entity_id = :2
3761 AND organization_id = :3
3762 ORDER BY operation_seq_num';
3763
3764 OPEN x_ref FOR sql_string USING wild, x_wip_entity_id, org_id;
3765
3766 END get_maintenance_op_seq_lov;
3767
3768 --
3769 -- End of inclusions for Bug 2588213
3770 --
3771
3772
3773 --
3774 -- R12 Project MOAC 4637896
3775 -- Change po_number param to p_po_header_id and change the
3776 -- SQL to bind to this param.
3777 -- bso Sat Oct 8 12:29:58 PDT 2005
3778 --
3779 PROCEDURE get_po_line_number_lov(p_po_header_id IN NUMBER, value IN
3780 VARCHAR2, x_ref OUT NOCOPY LovRefCursor) IS
3781
3782 wild VARCHAR2(160);
3783 sql_string VARCHAR2(1500);
3784
3785 BEGIN
3786
3787 /* Before Single Scan LOV
3788 IF value IS NULL THEN
3789 wild := '%';
3790 ELSE
3791 wild := value || '%';
3792 END IF; */
3793
3794 -- After Single Scan LOV
3795 wild := value;
3796
3797 --
3798 -- bug 9652444 CLM changes
3799 --
3800 sql_string := 'SELECT line_num, concatenated_segments
3801 FROM PO_LINES_VAL_TRX_V
3802 WHERE po_header_id = :1
3803 AND line_num like :2
3804 ORDER BY line_num';
3805
3806 OPEN x_ref FOR sql_string USING p_po_header_id, wild;
3807
3808 END get_po_line_number_lov;
3809
3810
3811 --
3812 -- R12 Project MOAC 4637896
3813 -- Change po_number param to p_po_header_id and change the
3814 -- SQL to bind to this param.
3815 -- bso Sat Oct 8 12:29:58 PDT 2005
3816 --
3817 -- bug 9652444 CLM changes
3818 --
3819 PROCEDURE get_po_shipments_lov(po_line_num IN VARCHAR2, p_po_header_id IN NUMBER,
3820 value IN VARCHAR2, x_ref OUT NOCOPY LovRefCursor) IS
3821
3822 wild VARCHAR2(160);
3823 sql_string VARCHAR2(1500);
3824
3825 BEGIN
3826
3827 /* Before Single Scan LOV
3828 IF value IS NULL THEN
3829 wild := '%';
3830 ELSE
3831 wild := value || '%';
3832 END IF; */
3833
3834 -- After Single Scan LOV
3835 wild := value;
3836
3837 /*
3838 sql_string := 'SELECT shipment_num, shipment_type
3839 FROM po_shipments_all_v
3840 WHERE po_line_id =
3841 (SELECT po_line_id
3842 FROM po_lines_val_v
3843 WHERE line_num = :1
3844 AND po_header_id = :2)
3845 AND shipment_num like :3';
3846 */
3847 -- Bug 4958763. SQL Repository Fix SQL ID: 15009194
3848 --
3849 -- bug 9652549 CLM changes
3850 --
3851 sql_string := 'SELECT shipment_num, shipment_type
3852 FROM PO_LINE_LOCATIONS_TRX_V
3853 WHERE po_line_id =
3854 (SELECT po_line_id
3855 FROM PO_LINES_TRX_V
3856 WHERE line_num = :1
3857 AND po_header_id = :2)
3858 AND shipment_num like :3';
3859
3860 OPEN x_ref FOR sql_string USING po_line_num, p_po_header_id, wild;
3861
3862 END get_po_shipments_lov;
3863
3864 --
3865 -- Bug 9817478
3866 -- Overloaded procedure which uses PO Release Num in addition to PO Number and PO Line Num
3867 -- to get lov for PO Shipments.
3868 -- skolluku
3869 --
3870 PROCEDURE get_po_shipments_lov (po_line_num IN VARCHAR2, p_po_header_id IN NUMBER, po_release_num IN VARCHAR2,
3871 value IN VARCHAR2, x_ref OUT NOCOPY LovRefCursor) IS
3872
3873 wild VARCHAR2(160);
3874 sql_string VARCHAR2(1500);
3875 po_release_number_id NUMBER;
3876
3877 BEGIN
3878
3879 -- After Single Scan LOV
3880 wild := value;
3881
3882 po_release_number_id := get_po_release_number_id(po_release_num, p_po_header_id);
3883
3884 sql_string := 'SELECT shipment_num, shipment_type
3885 FROM po_shipments_all_v
3886 WHERE po_line_id = (SELECT po_line_id
3887 FROM po_lines_val_trx_v
3888 WHERE line_num = :1
3889 AND po_header_id = :2)
3890 AND nvl(po_release_id,1) = nvl(:3,1)
3891 AND shipment_num LIKE :4';
3892
3893 OPEN x_ref FOR sql_string USING po_line_num, p_po_header_id, po_release_number_id, wild;
3894
3895 END get_po_shipments_lov;
3896
3897 PROCEDURE get_receipt_num_lov (value IN VARCHAR2, x_ref OUT NOCOPY LovRefCursor) IS
3898
3899 wild VARCHAR2(160);
3900 sql_string VARCHAR2(1500);
3901
3902 BEGIN
3903
3904 /* Before Single Scan LOV
3905 IF value IS NULL THEN
3906 wild := '%';
3907 ELSE
3908 wild := value || '%';
3909 END IF; */
3910
3911 -- After Single Scan LOV
3912 wild := value;
3913
3914 -- Bug 7491455
3915 -- FP for bug 6800960.Changed the sql to include the RMA receipts.
3916 -- pdube Fri Oct 17 00:14:28 PDT 2008
3917 /*sql_string := 'SELECT RCVSH.receipt_num, POV.vendor_name
3918 FROM rcv_receipts_all_v
3919 WHERE receipt_num like :1
3920 ORDER BY receipt_num';
3921 */
3922 sql_string := 'SELECT RCVSH.receipt_num, POV.vendor_name
3923 FROM RCV_SHIPMENT_HEADERS RCVSH,
3924 PO_VENDORS POV,
3925 RCV_TRANSACTIONS RT
3926 WHERE RCVSH.RECEIPT_SOURCE_CODE in (''VENDOR'',''CUSTOMER'') AND
3927 RCVSH.VENDOR_ID = POV.VENDOR_ID(+) AND
3928 RT.SHIPMENT_HEADER_ID = RCVSH.SHIPMENT_HEADER_ID AND
3929 rcvsh.receipt_num like :1
3930 ORDER BY RCVSH.receipt_num';
3931
3932 OPEN x_ref FOR sql_string USING wild;
3933
3934 END get_receipt_num_lov;
3935
3936
3937 PROCEDURE get_party_lov (value IN VARCHAR2, x_ref OUT NOCOPY LovRefCursor) IS
3938
3939 wild VARCHAR2(160);
3940 sql_string VARCHAR2(1500);
3941 BEGIN
3942
3943 /* Before Single Scan LOV
3944 IF value IS NULL THEN
3945 wild := '%';
3946 ELSE
3947 wild := value || '%';
3948 END IF; */
3949
3950 -- After Single Scan LOV
3951 wild := value;
3952
3953 /*
3954 --Reviewing 2255344. Incorrect SQL. bso
3955
3956 sql_string := 'SELECT party_number, party_name
3957 FROM hz_parties
3958 WHERE party_number like :1
3959 AND status = ''A''
3960 AND party_type IN (''ORGANIZATION'',''PERSON'')
3961 ORDER BY party_name';
3962 */
3963 sql_string := 'SELECT party_name, party_number
3964 FROM hz_parties
3965 WHERE party_name like :1
3966 AND status = ''A''
3967 AND party_type IN (''ORGANIZATION'',''PERSON'')
3968 ORDER BY party_name';
3969
3970 OPEN x_ref FOR sql_string USING wild;
3971
3972 END get_party_lov;
3973
3974 --
3975 -- Implemented the following six get_lov procedures for
3976 -- Service_Item, Counter, Maintenance_Requirement, Service_Request, Rework_Job
3977 -- For ASO project
3978 -- rkunchal Thu Jul 25 01:43:48 PDT 2002
3979 --
3980
3981 PROCEDURE get_item_instance_lov (value IN VARCHAR2, x_ref OUT NOCOPY LovRefCursor) IS
3982
3983 wild VARCHAR2(160);
3984 sql_string VARCHAR2(1500);
3985
3986 BEGIN
3987
3988 /* Before Single Scan LOV
3989 IF value IS NULL THEN
3990 wild := '%';
3991 ELSE
3992 wild := value || '%';
3993 END IF; */
3994
3995 -- After Single Scan LOV
3996 wild := value;
3997
3998 sql_string := 'SELECT cii.instance_number, cii.serial_number
3999 FROM qa_csi_item_instances cii, mtl_system_items_kfv msik
4000 WHERE cii.inventory_item_id = msik.inventory_item_id
4001 AND cii.last_vld_organization_id = msik.organization_id
4002 AND instance_number like :1
4003 ORDER BY 1';
4004
4005 OPEN x_ref FOR sql_string USING wild;
4006
4007 END get_item_instance_lov;
4008
4009 --
4010 -- Bug 9032151
4011 -- Overloading above procedure and with the new one which takes
4012 -- care of the dependency of item instance on item.
4013 -- skolluku
4014 --
4015 PROCEDURE get_item_instance_lov (p_org_id IN NUMBER, p_item_name IN VARCHAR2,
4016 value IN VARCHAR2, x_ref OUT NOCOPY LovRefCursor) IS
4017
4018 wild VARCHAR2(160);
4019 sql_string VARCHAR2(1500);
4020 l_item_id NUMBER DEFAULT NULL;
4021
4022 BEGIN
4023
4024 -- After Single Scan LOV
4025 wild := value;
4026
4027 l_item_id := qa_flex_util.get_item_id(p_org_id, p_item_name);
4028
4029 sql_string := 'SELECT cii.instance_number, cii.serial_number
4030 FROM qa_csi_item_instances cii, mtl_system_items_kfv msik
4031 WHERE cii.inventory_item_id = msik.inventory_item_id
4032 AND cii.last_vld_organization_id = msik.organization_id
4033 AND cii.inventory_item_id = :1
4034 AND trunc(sysdate) BETWEEN trunc(nvl(cii.active_start_date, sysdate))
4035 AND trunc(nvl(cii.active_end_date, sysdate))
4036 AND instance_number like :2
4037 ORDER BY cii.instance_number';
4038
4039 OPEN x_ref FOR sql_string USING l_item_id, wild;
4040
4041 END get_item_instance_lov;
4042
4043 --
4044 -- Bug 9359442
4045 -- New procedure which returns lov for item instance serial based on item.
4046 -- skolluku
4047 --
4048 PROCEDURE get_item_instance_serial_lov (p_org_id IN NUMBER, p_item_name IN VARCHAR2,
4049 value IN VARCHAR2, x_ref OUT NOCOPY LovRefCursor) IS
4050
4051 wild VARCHAR2(160);
4052 sql_string VARCHAR2(1500);
4053 l_item_id NUMBER DEFAULT NULL;
4054
4055 BEGIN
4056
4057 -- After Single Scan LOV
4058 wild := value;
4059
4060 l_item_id := qa_flex_util.get_item_id(p_org_id, p_item_name);
4061
4062 sql_string := 'SELECT cii.serial_number, msik.concatenated_segments
4063 FROM qa_csi_item_instances cii, mtl_system_items_kfv msik
4064 WHERE cii.inventory_item_id = msik.inventory_item_id
4065 AND cii.inv_master_organization_id = msik.organization_id
4066 AND msik.inventory_item_id = :1
4067 AND cii.serial_number like :2
4068 AND trunc(sysdate) BETWEEN trunc(nvl(cii.active_start_date, sysdate))
4069 AND trunc(nvl(cii.active_end_date, sysdate))
4070 ORDER BY cii.serial_number';
4071
4072 OPEN x_ref FOR sql_string USING l_item_id, wild;
4073
4074 END get_item_instance_serial_lov;
4075
4076 PROCEDURE get_counter_name_lov (value IN VARCHAR2, x_ref OUT NOCOPY LovRefCursor) IS
4077
4078 wild VARCHAR2(160);
4079 sql_string VARCHAR2(1500);
4080
4081 BEGIN
4082
4083 /* Before Single Scan LOV
4084 IF value IS NULL THEN
4085 wild := '%';
4086 ELSE
4087 wild := value || '%';
4088 END IF; */
4089
4090 -- After Single Scan LOV
4091 wild := value;
4092
4093 -- Bug 4958763. SQL Repository Fix SQL ID: 15009209
4094 /*
4095 sql_string := 'SELECT cc.name, cc.description
4096 FROM cs_counters cc, cs_counter_groups ccg
4097 WHERE cc.counter_group_id = ccg.counter_group_id
4098 AND ccg.template_flag = ''N''
4099 AND cc.name like :1
4100 ORDER BY 1';
4101 */
4102 sql_string := 'SELECT name, description
4103 FROM csi_counters_vl
4104 WHERE name like :1
4105 AND trunc(sysdate) BETWEEN nvl(start_date_active, trunc(sysdate))
4106 AND nvl(end_date_active, trunc(sysdate))
4107 ORDER BY 1';
4108
4109 OPEN x_ref FOR sql_string USING wild;
4110
4111 END get_counter_name_lov;
4112
4113
4114 PROCEDURE get_maintenance_req_lov (value IN VARCHAR2, x_ref OUT NOCOPY LovRefCursor) IS
4115
4116 wild VARCHAR2(160);
4117 sql_string VARCHAR2(1500);
4118
4119 BEGIN
4120
4121 /* Before Single Scan LOV
4122 IF value IS NULL THEN
4123 wild := '%';
4124 ELSE
4125 wild := value || '%';
4126 END IF; */
4127
4128 -- After Single Scan LOV
4129 wild := value;
4130
4131 sql_string := 'SELECT title, version_number
4132 FROM qa_ahl_mr
4133 WHERE title like :1
4134 ORDER BY 1';
4135
4136 OPEN x_ref FOR sql_string USING wild;
4137
4138 END get_maintenance_req_lov;
4139
4140
4141 PROCEDURE get_service_request_lov (value IN VARCHAR2, x_ref OUT NOCOPY LovRefCursor) IS
4142
4143 wild VARCHAR2(160);
4144 sql_string VARCHAR2(1500);
4145
4146 BEGIN
4147
4148 /* Before Single Scan LOV
4149 IF value IS NULL THEN
4150 wild := '%';
4151 ELSE
4152 wild := value || '%';
4153 END IF; */
4154
4155 -- After Single Scan LOV
4156 wild := value;
4157
4158 sql_string := 'SELECT incident_number, summary
4159 FROM cs_incidents
4160 WHERE incident_number like :1
4161 ORDER BY 1';
4162
4163 OPEN x_ref FOR sql_string USING wild;
4164
4165 END get_service_request_lov;
4166
4167
4168 PROCEDURE get_rework_job_lov (org_id IN NUMBER, value IN VARCHAR2,
4169 x_ref OUT NOCOPY LovRefCursor) IS
4170
4171 wild VARCHAR2(160);
4172 sql_string VARCHAR2(1500);
4173
4174 BEGIN
4175
4176 /* Before Single Scan LOV
4177 IF value IS NULL THEN
4178 wild := '%';
4179 ELSE
4180 wild := value || '%';
4181 END IF; */
4182
4183 -- After Single Scan LOV
4184 wild := value;
4185
4186 sql_string := 'SELECT wip_entity_name, description
4187 FROM wip_discrete_jobs_all_v
4188 WHERE wip_entity_name like :1 AND
4189 organization_id = :2
4190 ORDER BY wip_entity_name';
4191 OPEN x_ref FOR sql_string USING wild, org_id;
4192
4193 END get_rework_job_lov;
4194
4195 --
4196 -- End of inclusions for ASO project
4197 -- rkunchal Thu Jul 25 01:43:48 PDT 2002
4198 --
4199
4200 -- Start of inclusions for NCM Hardcode Elements.
4201 -- suramasw Thu Oct 31 10:48:59 PST 2002.
4202 -- Bug 2449067.
4203
4204
4205 PROCEDURE get_bill_reference_lov (x_org_id IN NUMBER, value IN VARCHAR2,
4206 x_ref OUT NOCOPY LovRefCursor) IS
4207
4208 wild VARCHAR2(160);
4209 sql_string VARCHAR2(1500);
4210
4211 BEGIN
4212
4213 /* Before Single Scan LOV
4214 IF value IS NULL THEN
4215 wild := '%';
4216 ELSE
4217 wild := value || '%';
4218 END IF; */
4219
4220 -- After Single Scan LOV
4221 wild := value;
4222
4223 sql_string := 'SELECT concatenated_segments, description
4224 FROM mtl_system_items_kfv
4225 WHERE organization_id = :1
4226 AND concatenated_segments like :2
4227 ORDER BY concatenated_segments';
4228
4229 OPEN x_ref FOR sql_string USING x_org_id, wild;
4230
4231 END get_bill_reference_lov;
4232
4233 PROCEDURE get_routing_reference_lov (x_org_id IN NUMBER, value IN VARCHAR2,
4234 x_ref OUT NOCOPY LovRefCursor) IS
4235
4236 wild VARCHAR2(160);
4237 sql_string VARCHAR2(1500);
4238
4239 BEGIN
4240
4241 /* Before Single Scan LOV
4242 IF value IS NULL THEN
4243 wild := '%';
4244 ELSE
4245 wild := value || '%';
4246 END IF; */
4247
4248 -- After Single Scan LOV
4249 wild := value;
4250
4251 sql_string := 'SELECT concatenated_segments, description
4252 FROM mtl_system_items_kfv
4253 WHERE organization_id = :1
4254 AND concatenated_segments like :2
4255 ORDER BY concatenated_segments';
4256
4257 OPEN x_ref FOR sql_string USING x_org_id, wild;
4258
4259 END get_routing_reference_lov;
4260
4261 PROCEDURE get_to_locator_lov (x_org_id IN NUMBER, x_item_name IN VARCHAR2,
4262 value IN VARCHAR2, x_ref OUT NOCOPY LovRefCursor) IS
4263
4264 wild VARCHAR2(160);
4265 sql_string VARCHAR2(1500);
4266
4267 BEGIN
4268
4269 /* Before Single Scan LOV
4270 IF value IS NULL THEN
4271 wild := '%';
4272 ELSE
4273 wild := value || '%';
4274 END IF; */
4275
4276 -- After Single Scan LOV
4277 wild := value;
4278
4279 sql_string := 'SELECT concatenated_segments, description
4280 FROM mtl_item_locations_kfv
4281 WHERE organization_id = :1
4282 AND concatenated_segments like :2
4283 ORDER BY concatenated_segments';
4284
4285 OPEN x_ref FOR sql_string USING x_org_id, wild;
4286
4287 END get_to_locator_lov;
4288
4289 PROCEDURE get_to_subinventory_lov (x_org_id IN NUMBER, value IN VARCHAR2,
4290 x_ref OUT NOCOPY LovRefCursor) IS
4291
4292 wild VARCHAR2(160);
4293 sql_string VARCHAR2(1500);
4294
4295 BEGIN
4296
4297 /* Before Single Scan LOV
4298 IF value IS NULL THEN
4299 wild := '%';
4300 ELSE
4301 wild := value || '%';
4302 END IF; */
4303
4304 -- After Single Scan LOV
4305 wild := value;
4306
4307 sql_string := 'SELECT secondary_inventory_name, description
4308 FROM mtl_secondary_inventories
4309 WHERE organization_id = :1
4310 AND nvl(disable_date, sysdate+1) > sysdate
4311 AND secondary_inventory_name like :2
4312 ORDER BY secondary_inventory_name';
4313
4314 OPEN x_ref FOR sql_string USING x_org_id, wild;
4315
4316 END get_to_subinventory_lov;
4317
4318 PROCEDURE get_lot_status_lov (x_org_id IN NUMBER, x_lot_num IN VARCHAR2,
4319 x_item_name IN VARCHAR2, value IN VARCHAR2,
4320 x_ref OUT NOCOPY LovRefCursor) IS
4321
4322 wild VARCHAR2(160);
4323 sql_string VARCHAR2(1500);
4324
4325 x_item_id NUMBER;
4326
4327 BEGIN
4328
4329 /* Before Single Scan LOV
4330 IF value IS NULL THEN
4331 wild := '%';
4332 ELSE
4333 wild := value || '%';
4334 END IF; */
4335
4336 -- After Single Scan LOV
4337 wild := value;
4338
4339 x_item_id := qa_flex_util.get_item_id(x_org_id, x_item_name);
4340
4341 -- Added the organization_id condition in the following select statement.
4342 -- Bug 2686970. suramasw Wed Nov 27 04:45:34 PST 2002.
4343
4344 sql_string := 'SELECT mms.status_code, mms.description
4345 FROM mtl_lot_numbers mln, mtl_material_statuses mms
4346 WHERE mln.inventory_item_id = :1
4347 AND mln.organization_id = :2
4348 AND mln.lot_number like :3
4349 AND mln.status_id = mms.status_id
4350 AND mms.status_code like :4
4351 AND mms.enabled_flag = 1';
4352
4353 OPEN x_ref FOR sql_string USING x_item_id, x_org_id, x_lot_num, wild;
4354
4355 END get_lot_status_lov;
4356
4357 FUNCTION get_lot_status_id (value IN VARCHAR2)
4358 RETURN NUMBER IS
4359
4360 id NUMBER;
4361
4362 CURSOR c (code VARCHAR2) IS
4363 SELECT status_id
4364 FROM mtl_material_statuses
4365 WHERE status_code = code;
4366
4367 BEGIN
4368
4369 IF value IS NULL THEN
4370 RETURN NULL;
4371 END IF;
4372
4373 OPEN c(value);
4374 FETCH c INTO id;
4375 CLOSE c;
4376
4377 RETURN id;
4378
4379 END get_lot_status_id;
4380
4381 PROCEDURE get_serial_status_lov (x_org_id IN NUMBER, x_serial_num IN VARCHAR2,
4382 x_item_name IN VARCHAR2, value IN VARCHAR2,
4383 x_ref OUT NOCOPY LovRefCursor) IS
4384
4385 wild VARCHAR2(160);
4386 sql_string VARCHAR2(1500);
4387
4388 x_item_id NUMBER;
4389
4390 BEGIN
4391
4392 /* Before Single Scan LOV
4393 IF value IS NULL THEN
4394 wild := '%';
4395 ELSE
4396 wild := value || '%';
4397 END IF; */
4398
4399 -- After Single Scan LOV
4400 wild := value;
4401
4402 x_item_id := qa_flex_util.get_item_id(x_org_id, x_item_name);
4403
4404 sql_string := 'SELECT mms.status_code, mms.description
4405 FROM mtl_serial_numbers msn, mtl_material_statuses mms
4406 WHERE msn.inventory_item_id = :1
4407 AND msn.serial_number like :2
4408 AND msn.status_id = mms.status_id
4409 AND mms.status_code like :3
4410 AND mms.enabled_flag = 1';
4411
4412 OPEN x_ref FOR sql_string USING x_item_id, x_serial_num, wild;
4413
4414 END get_serial_status_lov;
4415
4416 -- R12 OPM Deviations. Bug 4345503 Start
4417 PROCEDURE get_process_batch_num_lov
4418 (p_org_id IN NUMBER,
4419 value IN VARCHAR2,
4420 x_ref OUT NOCOPY LovRefCursor) IS
4421
4422 wild VARCHAR2(160);
4423 sql_string VARCHAR2(1500);
4424 BEGIN
4425 wild := value||'%';
4426
4427 sql_string := 'SELECT BATCH_NO, BATCH_NO BATCH_DESC '||
4428 'FROM GME_BATCH_HEADER '||
4429 'WHERE BATCH_NO like :1 '||
4430 'AND (ORGANIZATION_ID = :2 '||
4431 ' or ORGANIZATION_ID IS NULL)';
4432
4433 OPEN x_ref FOR sql_string USING wild,p_org_id;
4434
4435 END get_process_batch_num_lov;
4436
4437 PROCEDURE get_process_batchstep_num_lov
4438 (p_org_id IN NUMBER,
4439 p_process_batch_num IN VARCHAR2,
4440 value IN VARCHAR2,
4441 x_ref OUT NOCOPY LovRefCursor) IS
4442
4443 wild VARCHAR2(160);
4444 sql_string VARCHAR2(1500);
4445 l_batch_id NUMBER;
4446 BEGIN
4447 l_batch_id := get_process_batch_id(p_process_batch_num, p_org_id);
4448 wild := value;
4449 sql_string := 'SELECT STEPS.BATCHSTEP_NO,OPS.OPRN_DESC BATCHSTEP_DESC '||
4450 'FROM GME_BATCH_STEPS STEPS, GMD_OPERATIONS OPS '||
4451 'WHERE STEPS.BATCHSTEP_NO like :1 '||
4452 'AND STEPS.BATCH_ID =:2 '||
4453 'AND STEPS.OPRN_ID = OPS.OPRN_ID';
4454
4455 OPEN x_ref FOR sql_string USING wild, l_batch_id;
4456
4457 END get_process_batchstep_num_lov;
4458
4459 PROCEDURE get_process_operation_lov
4460 (p_org_id IN NUMBER,
4461 p_process_batch_num IN VARCHAR2,
4462 p_process_batchstep_num IN NUMBER,
4463 value IN VARCHAR2,
4464 x_ref OUT NOCOPY LovRefCursor) IS
4465
4466 wild VARCHAR2(160);
4467 sql_string VARCHAR2(1500);
4468 l_batch_id NUMBER;
4469 l_batchstep_id NUMBER;
4470 BEGIN
4471 l_batch_id := get_process_batch_id(p_process_batch_num, p_org_id);
4472 l_batchstep_id := get_process_batchstep_id(p_process_batchstep_num, l_batch_id);
4473 wild := value;
4474
4475 sql_string := 'SELECT OPERATION PROCESS_OPERATION, OPRN_DESC '||
4476 'FROM GMO_BATCH_STEPS_V '||
4477 'WHERE OPERATION like :1 '||
4478 'AND BATCHSTEP_ID = :2 '||
4479 'AND BATCH_ID =:3';
4480
4481 OPEN x_ref FOR sql_string USING wild, l_batchstep_id, l_batch_id;
4482
4483 END get_process_operation_lov;
4484
4485 PROCEDURE get_process_activity_lov
4486 (p_org_id IN NUMBER,
4487 p_process_batch_num IN VARCHAR2,
4488 p_process_batchstep_num IN NUMBER,
4489 value IN VARCHAR2,
4490 x_ref OUT NOCOPY LovRefCursor) IS
4491
4492 wild VARCHAR2(160);
4493 sql_string VARCHAR2(1500);
4494 l_batch_id NUMBER;
4495 l_batchstep_id NUMBER;
4496 l_activity_id NUMBER;
4497 BEGIN
4498 l_batch_id := get_process_batch_id(p_process_batch_num, p_org_id);
4499 l_batchstep_id := get_process_batchstep_id(p_process_batchstep_num, l_batch_id);
4500 wild := value;
4501
4502 sql_string := 'SELECT STEPS.ACTIVITY,ACTIVITIES.ACTIVITY_DESC '||
4503 'FROM GME_BATCH_STEP_ACTIVITIES STEPS, GMD_ACTIVITIES ACTIVITIES '||
4504 'WHERE STEPS.ACTIVITY like :1 '||
4505 'AND STEPS.BATCHSTEP_ID =:2 '||
4506 'AND STEPS.BATCH_ID =:3 '||
4507 'AND STEPS.ACTIVITY = ACTIVITIES.ACTIVITY';
4508
4509 OPEN x_ref FOR sql_string USING wild, l_batchstep_id, l_batch_id;
4510
4511 END get_process_activity_lov;
4512
4513 PROCEDURE get_process_resource_lov
4514 (p_org_id IN NUMBER,
4515 p_process_batch_num IN VARCHAR2,
4516 p_process_batchstep_num IN NUMBER,
4517 p_process_activity IN VARCHAR2,
4518 value IN VARCHAR2,
4519 x_ref OUT NOCOPY LovRefCursor) IS
4520
4521 wild VARCHAR2(160);
4522 sql_string VARCHAR2(1500);
4523 l_batch_id NUMBER;
4524 l_batchstep_id NUMBER;
4525 l_activity_id NUMBER;
4526 BEGIN
4527 l_batch_id := get_process_batch_id(p_process_batch_num, p_org_id);
4528 l_batchstep_id := get_process_batchstep_id(p_process_batchstep_num, l_batch_id);
4529 l_activity_id := get_process_activity_id(p_process_activity, l_batch_id, l_batchstep_id);
4530 wild := value;
4531
4532 sql_string := 'SELECT GBSR.RESOURCES, CRMV.RESOURCE_DESC '||
4533 'FROM GME_BATCH_STEP_RESOURCES GBSR, CR_RSRC_MST_VL CRMV '||
4534 'WHERE GBSR.RESOURCES like :1 '||
4535 'AND GBSR.BATCHSTEP_ACTIVITY_ID =:2 '||
4536 'AND GBSR.BATCHSTEP_ID =:3 '||
4537 'AND GBSR.BATCH_ID =:4 '||
4538 'AND GBSR.RESOURCES = CRMV.RESOURCES';
4539
4540 OPEN x_ref FOR sql_string USING wild, l_activity_id, l_batchstep_id, l_batch_id;
4541
4542 END get_process_resource_lov;
4543
4544 PROCEDURE get_process_parameter_lov
4545 (p_org_id IN NUMBER,
4546 p_process_resource IN VARCHAR2,
4547 value IN VARCHAR2,
4548 x_ref OUT NOCOPY LovRefCursor) IS
4549
4550 wild VARCHAR2(160);
4551 sql_string VARCHAR2(1500);
4552 BEGIN
4553 wild := value;
4554
4555 sql_string := 'SELECT GP.PARAMETER_NAME, GP.PARAMETER_DESCRIPTION '||
4556 'FROM GMP_PROCESS_PARAMETERS GP,GME_PROCESS_PARAMETERS GE '||
4557 'WHERE GE.RESOURCES = :1 '||
4558 'AND GE.PARAMETER_ID = GP.PARAMETER_ID '||
4559 'AND GP.PARAMETER_NAME like :2';
4560
4561 OPEN x_ref FOR sql_string USING p_process_resource, wild;
4562
4563 END get_process_parameter_lov;
4564
4565 -- R12 OPM Deviations. Bug 4345503 End
4566
4567 /* R12 DR Integration. Bug 4345489 Start */
4568
4569 PROCEDURE get_repair_number_lov (value IN VARCHAR2,
4570 x_ref OUT NOCOPY LovRefCursor) IS
4571
4572 wild VARCHAR2(160);
4573 sql_string VARCHAR2(1500);
4574
4575 BEGIN
4576
4577 wild := value;
4578
4579 sql_string := 'SELECT repair_number, problem_description
4580 FROM csd_repairs
4581 WHERE repair_number like :1
4582 and status not in (''C'', ''H'')
4583 ORDER BY repair_number';
4584
4585 OPEN x_ref FOR sql_string USING wild;
4586
4587 END get_repair_number_lov;
4588
4589 PROCEDURE get_jtf_task_lov (value IN VARCHAR2, x_ref OUT
4590 NOCOPY LovRefCursor) IS
4591
4592 wild VARCHAR2(160);
4593 sql_string VARCHAR2(1500);
4594
4595 BEGIN
4596
4597 wild := value;
4598
4599 sql_string := 'SELECT task_number, task_name
4600 FROM jtf_tasks_vl
4601 WHERE task_number like :1
4602 ORDER BY task_number';
4603
4604 OPEN x_ref FOR sql_string USING wild;
4605
4606 END get_jtf_task_lov;
4607
4608 /* R12 DR Integration. Bug 4345489 End */
4609
4610 FUNCTION get_serial_status_id (value IN VARCHAR2)
4611 RETURN NUMBER IS
4612
4613 id NUMBER;
4614
4615 CURSOR c (code VARCHAR2) IS
4616 SELECT status_id
4617 FROM mtl_material_statuses
4618 WHERE status_code = code;
4619
4620 BEGIN
4621
4622 IF value IS NULL THEN
4623 RETURN NULL;
4624 END IF;
4625
4626 OPEN c(value);
4627 FETCH c INTO id;
4628 CLOSE c;
4629
4630 RETURN id;
4631
4632 END get_serial_status_id;
4633
4634 -- End of inclusions for NCM Hardcode Elements.
4635
4636
4637 PROCEDURE get_item_lov (x_org_id IN NUMBER, value IN VARCHAR2,
4638 x_ref OUT NOCOPY LovRefCursor) IS
4639
4640 wild VARCHAR2(160);
4641 sql_string VARCHAR2(1500);
4642
4643 BEGIN
4644
4645 /* Before Single Scan LOV
4646 IF value IS NULL THEN
4647 wild := '%';
4648 ELSE
4649 wild := value || '%';
4650 END IF; */
4651
4652 -- After Single Scan LOV
4653 wild := value;
4654
4655 sql_string := 'SELECT concatenated_segments, description
4656 FROM mtl_system_items_kfv
4657 WHERE organization_id = :1
4658 AND concatenated_segments like :2
4659 ORDER BY concatenated_segments';
4660
4661 OPEN x_ref FOR sql_string USING x_org_id, wild;
4662
4663 END get_item_lov;
4664
4665
4666 --
4667 -- Bug 5292020 adding comp item LOV to mqa.
4668 -- comp item LOVs have three variants depending on what is
4669 -- present in the collection plan. Hence this LOV contains
4670 -- 3 SQLs selected depending on the input param. All SQLs
4671 -- adapted from flex field definition in QLTRES.pld.
4672 -- bso Thu Jun 8 00:16:03 PDT 2006
4673 --
4674 PROCEDURE get_comp_item_lov(
4675 p_org_id IN NUMBER,
4676 p_item_name IN VARCHAR2,
4677 p_job_name IN VARCHAR2,
4678 p_prod_line IN VARCHAR2,
4679 p_value IN VARCHAR2,
4680 x_ref OUT NOCOPY LovRefCursor) IS
4681
4682 l_sql_string VARCHAR2(1500);
4683 l_job_id NUMBER;
4684 l_prod_line_id NUMBER;
4685 l_item_id NUMBER;
4686
4687 BEGIN
4688
4689 IF p_item_name IS NOT NULL THEN
4690 l_item_id := qa_flex_util.get_item_id(p_org_id, p_item_name);
4691 END IF;
4692
4693 IF p_job_name IS NOT NULL THEN
4694 l_job_id := get_job_id(p_org_id, p_job_name);
4695 END IF;
4696
4697 IF p_prod_line IS NOT NULL THEN
4698 l_prod_line_id := get_production_line_id(p_org_id, p_prod_line);
4699 END IF;
4700
4701 IF l_job_id IS NOT NULL THEN
4702 --
4703 -- Comp Item with WIP Job dependency.
4704 --
4705 l_sql_string :=
4706 'SELECT concatenated_segments, description
4707 FROM mtl_system_items_kfv
4708 WHERE organization_id = :1 AND
4709 concatenated_segments like :2 AND
4710 inventory_item_id IN (
4711 SELECT inventory_item_id
4712 FROM wip_requirement_operations
4713 WHERE wip_entity_id = :3 AND
4714 organization_id = :4)';
4715 OPEN x_ref FOR l_sql_string USING p_org_id, p_value, l_job_id,
4716 p_org_id;
4717
4718 ELSIF l_prod_line_id IS NOT NULL AND l_item_id IS NOT NULL THEN
4719 --
4720 -- Comp Item with Flow Production Line dependency.
4721 --
4722 l_sql_string :=
4723 'SELECT concatenated_segments, description
4724 FROM mtl_system_items_kfv
4725 WHERE organization_id = :1 AND
4726 concatenated_segments like :2 AND
4727 inventory_item_id IN (
4728 SELECT inventory_item_id
4729 FROM wip_requirement_operations
4730 WHERE wip_entity_id = (
4731 SELECT wip_entity_id
4732 FROM wip_repetitive_items
4733 WHERE line_id = :3 AND
4734 primary_item_id = :4 AND
4735 organization_id = :5))';
4736 OPEN x_ref FOR l_sql_string USING p_org_id, p_value, l_prod_line_id,
4737 l_item_id, p_org_id;
4738 ELSIF l_item_id IS NOT NULL THEN
4739 --
4740 -- Comp Item with BOM Component Dependency.
4741 --
4742 l_sql_string :=
4743 'SELECT concatenated_segments, description
4744 FROM mtl_system_items_kfv
4745 WHERE organization_id = :1 AND
4746 concatenated_segments like :2 AND
4747 inventory_item_id IN (
4748 SELECT bic.component_item_id
4749 FROM bom_inventory_components bic,
4750 bom_bill_of_materials bom
4751 WHERE bic.bill_sequence_id = bom.bill_sequence_id AND
4752 bic.effectivity_date <= sysdate AND
4753 nvl(bic.disable_date, sysdate+1) > sysdate AND
4754 bom.assembly_item_id = :3 AND
4755 bom.organization_id = :4)';
4756 OPEN x_ref FOR l_sql_string USING p_org_id, p_value, l_item_id,
4757 p_org_id;
4758 ELSE
4759 --
4760 -- This is a catchall SQL that returns no value.
4761 --
4762 l_sql_string := 'SELECT ''x'', ''x'' FROM dual WHERE 1 = 2';
4763 open x_ref FOR l_sql_string;
4764 END IF;
4765
4766 END get_comp_item_lov;
4767
4768
4769 PROCEDURE get_asset_group_lov (x_org_id IN NUMBER, value IN VARCHAR2,
4770 x_ref OUT NOCOPY LovRefCursor) IS
4771
4772 wild VARCHAR2(160);
4773 sql_string VARCHAR2(1500);
4774
4775 BEGIN
4776
4777 /* Before Single Scan LOV
4778 IF value IS NULL THEN
4779 wild := '%';
4780 ELSE
4781 wild := value || '%';
4782 END IF; */
4783
4784 -- After Single Scan LOV
4785 wild := value;
4786
4787 --dgupta: Start R12 EAM Integration. Bug 4345492
4788 sql_string := 'select distinct msikfv.concatenated_segments, msikfv.description
4789 from mtl_system_items_b_kfv msikfv, mtl_parameters mp
4790 where msikfv.organization_id = mp.organization_id
4791 and msikfv.eam_item_type in (1,3)
4792 and mp.maint_organization_id = :1
4793 and msikfv.concatenated_segments like :2
4794 order by msikfv.concatenated_segments';
4795 --dgupta: End R12 EAM Integration. Bug 4345492
4796
4797 OPEN x_ref FOR sql_string USING x_org_id, wild;
4798
4799 END get_asset_group_lov;
4800
4801
4802
4803 --dgupta: Start R12 EAM Integration. Bug 4345492
4804 PROCEDURE get_asset_activity_lov (x_org_id IN NUMBER, p_asset_group IN VARCHAR2,
4805 p_asset_number IN VARCHAR2, p_asset_instance_number IN VARCHAR2, value IN VARCHAR2,
4806 x_ref OUT NOCOPY LovRefCursor) IS
4807
4808 wild VARCHAR2(160);
4809 sql_string VARCHAR2(1500);
4810 l_asset_group_id NUMBER DEFAULT NULL;
4811 l_asset_instance_id NUMBER DEFAULT NULL;
4812
4813 BEGIN
4814
4815 -- After Single Scan LOV
4816 wild := value;
4817
4818 l_asset_group_id := get_asset_group_id(x_org_id, p_asset_group);
4819 l_asset_instance_id := get_asset_instance_id(p_asset_instance_number);
4820 if (l_asset_instance_id is null) then
4821 l_asset_instance_id := get_asset_instance_id(l_asset_group_id, p_asset_number);
4822 end if;
4823
4824 if (p_asset_number is null and l_asset_instance_id is null) then
4825 -- show all activities asssociated to the asset group
4826 -- If no match found or if asset group passed in is null, lov is empty
4827 /*
4828 sql_string := 'SELECT meaav.activity, meaav.activity_description
4829 FROM mtl_eam_asset_activities_v meaav, mtl_system_items_b msib
4830 WHERE msib.organization_id = :1
4831 and meaav. maintenance_object_id = :2 --pass asset group inventory_item_id
4832 and (meaav.end_date_active is null or meaav.end_date_active > sysdate)
4833 and (meaav.start_date_active is null or meaav.start_date_active < sysdate)
4834 and msib.inventory_item_id = meaav. maintenance_object_id
4835 and meaav.maintenance_object_type = 2 --non serialized item
4836 AND meaav.activity like :3
4837 ORDER BY meaav.activity';
4838 */
4839 -- Bug 4958763. SQL Repository Fix SQL ID: 15009272
4840 sql_string := 'SELECT
4841 msib.concatenated_segments activity ,
4842 msib.description activity_description
4843 FROM mtl_eam_asset_activities meaav,
4844 mtl_system_items_b_kfv msib
4845 WHERE msib.organization_id = :1
4846 AND meaav. maintenance_object_id = :2 --pass asset group inventory_item_id
4847 AND (meaav.end_date_active is null
4848 OR meaav.end_date_active > sysdate)
4849 AND (meaav.start_date_active is null
4850 OR meaav.start_date_active < sysdate)
4851 AND msib.inventory_item_id = meaav.asset_activity_id
4852 AND meaav.maintenance_object_type = 2 --non serialized item
4853 AND msib.concatenated_segments like :3
4854 ORDER BY msib.concatenated_segments';
4855
4856 OPEN x_ref FOR sql_string USING x_org_id, l_asset_group_id, wild;
4857 else
4858 -- show all activities associated to asset group and asset number
4859 -- if exact match not found, lov is empty.
4860 /*
4861 sql_string := 'SELECT meaav.activity, meaav.activity_description
4862 FROM mtl_eam_asset_activities_v meaav, mtl_system_items_b msib
4863 WHERE msib.organization_id = :1
4864 and meaav.maintenance_object_id = :2 --pass asset instance_id
4865 and meaav.maintenance_object_type = 3 --serialized item
4866 and (meaav.end_date_active is null or meaav.end_date_active > sysdate)
4867 and (meaav.start_date_active is null or meaav.start_date_active < sysdate)
4868 and msib.inventory_item_id = meaav.inventory_item_id
4869 AND meaav.activity like :3
4870 ORDER BY meaav.activity';
4871 */
4872 -- Bug 4958763. SQL Repository Fix SQL ID: 15009282
4873 sql_string := 'SELECT
4874 msi.concatenated_segments activity ,
4875 msi.description activity_description
4876 FROM mtl_eam_asset_activities meaa,
4877 mtl_system_items_b_kfv msi
4878 WHERE msi.organization_id = :1
4879 AND meaa.maintenance_object_id = :2 --pass asset instance_id
4880 AND meaa.maintenance_object_type = 3 --serialized item
4881 AND (meaa.end_date_active is null
4882 OR meaa.end_date_active > sysdate)
4883 AND (meaa.start_date_active is null
4884 OR meaa.start_date_active < sysdate)
4885 AND msi.inventory_item_id = meaa.asset_activity_id
4886 AND msi.concatenated_segments like :3
4887 ORDER BY msi.concatenated_segments';
4888
4889 OPEN x_ref FOR sql_string USING x_org_id, l_asset_instance_id, wild;
4890 end if;
4891
4892 END get_asset_activity_lov;
4893
4894 -- added the following to include new hardcoded element followup activity
4895 -- saugupta Aug 2003
4896 --dgupta: just call asset activity lov, since query is same
4897 PROCEDURE get_followup_activity_lov (x_org_id IN NUMBER, p_asset_group IN VARCHAR2,
4898 p_asset_number IN VARCHAR2, p_asset_instance_number IN VARCHAR2, value IN VARCHAR2,
4899 x_ref OUT NOCOPY LovRefCursor) IS
4900 BEGIN
4901 get_asset_activity_lov(x_org_id, p_asset_group, p_asset_number, p_asset_instance_number,
4902 value, x_ref); --no use duplicating code
4903 END get_followup_activity_lov;
4904
4905 --dgupta: End R12 EAM Integration. Bug 4345492
4906
4907 PROCEDURE get_locator_lov (x_org_id IN NUMBER, x_item_name IN VARCHAR2,
4908 value IN VARCHAR2, x_ref OUT NOCOPY LovRefCursor) IS
4909
4910 wild VARCHAR2(160);
4911 sql_string VARCHAR2(1500);
4912
4913 BEGIN
4914
4915 /* Before Single Scan LOV
4916 IF value IS NULL THEN
4917 wild := '%';
4918 ELSE
4919 wild := value || '%';
4920 END IF; */
4921
4922 -- After Single Scan LOV
4923 wild := value;
4924
4925 sql_string := 'SELECT concatenated_segments, description
4926 FROM mtl_item_locations_kfv
4927 WHERE organization_id = :1
4928 AND concatenated_segments like :2
4929 ORDER BY concatenated_segments';
4930
4931 OPEN x_ref FOR sql_string USING x_org_id, wild;
4932
4933 END get_locator_lov;
4934
4935 --
4936 -- Removed the DEFAULT clause to make the code GSCC compliant
4937 -- List of changed arguments.
4938 -- Old
4939 -- user_id IN NUMBER DEFAULT NULL
4940 -- value IN VARCHAR2 DEFAULT NULL
4941 -- New
4942 -- user_id IN NUMBER
4943 -- value IN VARCHAR2
4944 --
4945
4946 PROCEDURE get_plan_element_lov(plan_id IN NUMBER, char_id IN NUMBER,
4947 org_id IN NUMBER, user_id IN NUMBER,
4948 value IN VARCHAR2,
4949 x_ref OUT NOCOPY LovRefCursor) IS
4950
4951 BEGIN
4952
4953 -- The function sql_string_exists simple checks to see
4954 -- if the user defined element should have a LOV
4955 -- associated with it or not. If it should then it returns
4956 -- true and populates sql_string - an out parameter.
4957
4958 IF sql_string_exists(plan_id, char_id, org_id, user_id, value, x_ref) THEN
4959 RETURN;
4960 ELSE
4961 --
4962 -- To prevent client from bombing, open an empty cursor in case
4963 -- this function if called but there is no LOV!
4964 --
4965 OPEN x_ref FOR
4966 SELECT 'x', 'x' FROM dual WHERE 1 = 2;
4967 END IF;
4968
4969 END get_plan_element_lov;
4970
4971
4972 PROCEDURE get_spec_details ( x_spec_id IN NUMBER, x_char_id IN NUMBER,
4973 x_target_value OUT NOCOPY VARCHAR2,
4974 x_lower_spec_limit OUT NOCOPY VARCHAR2,
4975 x_upper_spec_limit OUT NOCOPY VARCHAR2,
4976 x_lower_user_defined_limit OUT NOCOPY VARCHAR2,
4977 x_upper_user_defined_limit OUT NOCOPY VARCHAR2,
4978 x_lower_reasonable_limit OUT NOCOPY VARCHAR2,
4979 x_upper_reasonable_limit OUT NOCOPY VARCHAR2) IS
4980
4981 CURSOR c IS
4982 SELECT
4983 target_value,
4984 lower_spec_limit,
4985 upper_spec_limit,
4986 lower_user_defined_limit,
4987 upper_user_defined_limit,
4988 lower_reasonable_limit,
4989 upper_reasonable_limit
4990 FROM qa_spec_chars
4991 WHERE spec_id = x_spec_id
4992 AND char_id = x_char_id;
4993
4994 BEGIN
4995
4996 OPEN c;
4997 FETCH c INTO x_target_value,
4998 x_lower_spec_limit,
4999 x_upper_spec_limit,
5000 x_lower_user_defined_limit,
5001 x_upper_user_defined_limit,
5002 x_lower_reasonable_limit,
5003 x_upper_reasonable_limit;
5004 CLOSE c;
5005
5006 x_target_value := nvl(x_target_value, ' ');
5007 x_lower_spec_limit := nvl(x_lower_spec_limit, ' ');
5008 x_upper_spec_limit := nvl(x_upper_spec_limit, ' ');
5009 x_lower_user_defined_limit := nvl(x_lower_user_defined_limit, ' ');
5010 x_upper_user_defined_limit := nvl(x_upper_user_defined_limit, ' ');
5011 x_lower_reasonable_limit := nvl(x_lower_reasonable_limit, ' ');
5012 x_upper_reasonable_limit := nvl(x_upper_reasonable_limit, ' ');
5013
5014 END get_spec_details;
5015
5016
5017 PROCEDURE get_spec_sub_type (x_spec_id IN NUMBER,
5018 x_element_name OUT NOCOPY VARCHAR2) IS
5019
5020 CURSOR c IS
5021 SELECT name
5022 FROM qa_chars
5023 WHERE char_id =
5024 ( SELECT char_id
5025 FROM qa_specs
5026 WHERE spec_id = x_spec_id );
5027
5028 BEGIN
5029
5030 OPEN c;
5031 FETCH c INTO x_element_name;
5032 CLOSE c;
5033
5034 x_element_name := nvl(x_element_name, ' ');
5035
5036 END get_spec_sub_type;
5037
5038
5039 PROCEDURE get_spec_type (p_spec_id IN NUMBER,
5040 x_spec_type OUT NOCOPY VARCHAR2) IS
5041
5042 CURSOR c IS
5043 SELECT meaning
5044 FROM mfg_lookups
5045 WHERE lookup_type = 'QA_SPEC_TYPE'
5046 AND lookup_code =
5047 ( SELECT assignment_type
5048 FROM qa_specs
5049 WHERE spec_id = p_spec_id );
5050
5051 BEGIN
5052
5053 OPEN c;
5054 FETCH c INTO x_spec_type;
5055 CLOSE c;
5056
5057 END get_spec_type;
5058
5059
5060 PROCEDURE get_item_name (p_spec_id IN NUMBER,
5061 x_item_name OUT NOCOPY VARCHAR2) IS
5062
5063 CURSOR c IS
5064 SELECT organization_id, item_id
5065 FROM qa_specs
5066 WHERE spec_id = p_spec_id;
5067
5068 l_org_id NUMBER;
5069 l_item_id NUMBER;
5070
5071 BEGIN
5072
5073 OPEN c;
5074 FETCH c INTO l_org_id, l_item_id;
5075 CLOSE c;
5076
5077 x_item_name := QA_FLEX_UTIL.item(l_org_id, l_item_id);
5078
5079 END get_item_name;
5080
5081
5082 PROCEDURE get_supplier_name (p_spec_id IN NUMBER,
5083 x_supplier_name OUT NOCOPY VARCHAR2) IS
5084
5085 CURSOR c IS
5086 SELECT vendor_name
5087 FROM po_vendors
5088 WHERE vendor_id =
5089 ( SELECT vendor_id
5090 FROM qa_specs
5091 WHERE spec_id = p_spec_id );
5092
5093 BEGIN
5094
5095 OPEN c;
5096 FETCH c INTO x_supplier_name;
5097 CLOSE c;
5098
5099 END get_supplier_name;
5100
5101
5102 PROCEDURE get_customer_name (p_spec_id IN NUMBER,
5103 x_customer_name OUT NOCOPY VARCHAR2) IS
5104
5105 CURSOR c IS
5106 SELECT customer_name
5107 FROM qa_customers_lov_v
5108 WHERE customer_id =
5109 ( SELECT customer_id
5110 FROM qa_specs
5111 WHERE spec_id = p_spec_id );
5112
5113 BEGIN
5114
5115 OPEN c;
5116 FETCH c INTO x_customer_name;
5117 CLOSE c;
5118
5119 END get_customer_name;
5120
5121
5122 FUNCTION context_element (element_id IN NUMBER, txn_number IN NUMBER)
5123 RETURN BOOLEAN IS
5124
5125 result BOOLEAN;
5126 dummy NUMBER;
5127
5128 CURSOR c IS
5129 SELECT 1
5130 FROM qa_txn_collection_triggers qtct
5131 WHERE qtct.transaction_number = txn_number
5132 AND qtct.collection_trigger_id = element_id;
5133
5134 BEGIN
5135
5136 -- This function determines if collection element is a context element
5137 -- given a transaction number.
5138
5139 OPEN c;
5140 FETCH c INTO dummy;
5141 result := c%FOUND;
5142 CLOSE c;
5143
5144 RETURN result;
5145
5146 END context_element;
5147
5148 --dgupta: Start R12 EAM Integration. Bug 4345492
5149 FUNCTION get_asset_instance_id (value IN VARCHAR2)
5150 RETURN NUMBER IS
5151
5152 id NUMBER;
5153 --Instance Number was already validated for eam restrictions
5154 --Following sql returns 1 row as there is unique index on instance_number
5155 CURSOR c (i_num VARCHAR2) IS
5156 SELECT cii.instance_id
5157 FROM csi_item_instances cii
5158 WHERE cii.instance_number = i_num;
5159
5160 BEGIN
5161
5162 IF value IS NULL THEN
5163 RETURN NULL;
5164 END IF;
5165
5166 OPEN c(value);
5167 FETCH c INTO id;
5168 CLOSE c;
5169
5170 RETURN id;
5171
5172 END get_asset_instance_id;
5173
5174 FUNCTION get_asset_instance_id (p_asset_group_id IN NUMBER, p_asset_number IN VARCHAR2)
5175 RETURN NUMBER IS
5176
5177 id NUMBER;
5178 --Asset Group and Asset Number were already validated for eam restrictions
5179 CURSOR c (org_id NUMBER, asset_group VARCHAR2) IS
5180 SELECT cii.instance_id
5181 FROM csi_item_instances cii
5182 WHERE cii.inventory_item_id = p_asset_group_id
5183 AND cii.serial_number = p_asset_number; --inv id and serial num combo is unique
5184
5185 BEGIN
5186
5187 IF ((p_asset_group_id IS NULL) OR (p_asset_number is NULL)) THEN
5188 RETURN NULL;
5189 END IF;
5190
5191 OPEN c(p_asset_group_id, p_asset_number);
5192 FETCH c INTO id;
5193 CLOSE c;
5194
5195 RETURN id;
5196
5197 END get_asset_instance_id;
5198
5199
5200 FUNCTION get_asset_group_id (org_id IN NUMBER, value IN VARCHAR2)
5201 RETURN NUMBER IS
5202
5203 id NUMBER;
5204 --rownum=1 =>better performance since all rows have same inventory_item_id
5205 CURSOR c (o_id NUMBER, a_group VARCHAR2) IS
5206 SELECT msikfv.inventory_item_id
5207 FROM mtl_system_items_b_kfv msikfv, mtl_parameters mp
5208 WHERE msikfv.organization_id = mp.organization_id
5209 and msikfv.eam_item_type in (1,3)
5210 and mp.maint_organization_id = o_id
5211 and msikfv.concatenated_segments = a_group
5212 and rownum=1;
5213
5214 BEGIN
5215
5216 IF value IS NULL THEN
5217 RETURN NULL;
5218 END IF;
5219
5220 OPEN c(org_id, value);
5221 FETCH c INTO id;
5222 CLOSE c;
5223
5224 RETURN id;
5225
5226 END get_asset_group_id;
5227
5228 --dgupta: End R12 EAM Integration. Bug 4345492
5229
5230 -- Bug 4519558. OA Framework integration project. UT bug fix.
5231 -- Transaction type element was erroring out for WIP transactions.
5232 -- New function to validate "Transaction Type".
5233 -- srhariha.Tue Aug 2 00:43:07 PDT 2005.
5234 FUNCTION validate_transaction_type(p_transaction_number IN NUMBER,
5235 p_org_id IN NUMBER,
5236 p_user_id IN NUMBER,
5237 p_value IN VARCHAR2)
5238 RETURN BOOLEAN IS
5239
5240 CURSOR C4 IS
5241 SELECT 1
5242 FROM MTL_TRANSACTION_TYPES
5243 WHERE transaction_source_type_id = 5
5244 AND transaction_action_id in (31,32)
5245 AND transaction_type_name = p_value;
5246
5247 CURSOR C22 IS
5248 SELECT 1
5249 FROM MTL_TRANSACTION_TYPES
5250 WHERE transaction_source_type_id = 5
5251 AND transaction_action_id in (30,31,32)
5252 AND transaction_type_name = p_value;
5253
5254 CURSOR C1 IS
5255 SELECT 1
5256 FROM MFG_LOOKUPS
5257 WHERE lookup_type ='WIP_MOVE_TRANSACTION_TYPE'
5258 AND meaning = p_value;
5259
5260 CURSOR C_MOBILE(p_lookup_type VARCHAR2) IS
5261 SELECT 1
5262 FROM QA_LOOKUPS
5263 WHERE lookup_type = p_lookup_type
5264 AND lookup_code = p_value;
5265
5266 l_temp NUMBER;
5267 result BOOLEAN;
5268 sql_string QA_CHARS.SQL_VALIDATION_STRING%TYPE;
5269
5270 BEGIN
5271 IF p_transaction_number = qa_ss_const.wip_completion_txn THEN
5272 OPEN C4;
5273 FETCH C4 INTO l_temp;
5274 result := C4%FOUND;
5275 CLOSE C4;
5276
5277 ELSIF p_transaction_number = qa_ss_const.flow_work_order_less_txn THEN
5278 OPEN C22;
5279 FETCH C22 INTO l_temp;
5280 result := C22%FOUND;
5281 CLOSE C22;
5282
5283 ELSIF p_transaction_number = qa_ss_const.wip_move_txn THEN
5284 OPEN C1;
5285 FETCH C1 INTO l_temp;
5286 result := C1%FOUND;
5287 CLOSE C1;
5288
5289 -- Bug 4519558.OA Framework Integration project. UT bug fix.
5290 -- Incorporating Bryan's code review comments. Use new
5291 -- method in qa_mqa_mwa_api package.
5292 -- srhariha. Mon Aug 22 02:50:35 PDT 2005.
5293
5294 ELSIF qa_mqa_mwa_api.is_mobile_txn(p_transaction_number) = fnd_api.g_true THEN
5295 OPEN C_MOBILE(qa_ss_const.mob_txn_lookup_prefix || to_char(p_transaction_number));
5296 FETCH C_MOBILE INTO l_temp;
5297 result := C_MOBILE%FOUND;
5298 CLOSE C_MOBILE;
5299
5300 ELSE
5301
5302 sql_string := get_sql_validation_string(qa_ss_const.transaction_type);
5303
5304
5305 sql_string := qa_chars_api.format_sql_for_validation (sql_string,
5306 p_org_id, p_user_id);
5307
5308 IF value_in_sql (sql_string, p_value) THEN
5309 result := TRUE;
5310 ELSE
5311 result := FALSE;
5312 END IF;
5313
5314 END IF;
5315
5316 RETURN result;
5317
5318 END validate_transaction_type;
5319
5320 -- bug 5186397
5321 -- new function to perform the UOM conversion
5322 -- This will call the INV api to convert
5323 -- the source value passed from the source
5324 -- UOM to the Target UOM
5325 -- SHKALYAN 01-May-2006
5326 --
5327 FUNCTION perform_uom_conversion (p_source_val IN VARCHAR2,
5328 p_precision IN NUMBER ,
5329 p_source_UOM IN VARCHAR2,
5330 p_target_UOM IN VARCHAR2)
5331 RETURN NUMBER AS
5332 converted_value NUMBER;
5333 BEGIN
5334 converted_value := INV_CONVERT.INV_UM_CONVERT
5335 (null,
5336 p_precision,
5337 to_number(p_source_val),
5338 p_source_UOM,
5339 p_target_UOM,
5340 null,
5341 null);
5342
5343 RETURN converted_value;
5344 END perform_uom_conversion;
5345
5346 --
5347 -- Bug 5383667
5348 -- New function to get the
5349 -- Id Values from QA_results table
5350 -- ntungare Thu Aug 24 02:02:38 PDT 2006
5351 --
5352 Function get_id_val(p_child_char_id IN NUMBER,
5353 p_plan_id IN NUMBER,
5354 p_collection_id IN NUMBER,
5355 p_occurrence IN NUMBER)
5356 RETURN VARCHAR2 AS
5357 id_val NUMBER;
5358 str VARCHAR2(2000);
5359 BEGIN
5360 -- bug 6129280
5361 -- Added to fetch locator_id, comp_locator_id
5362 -- and to_locator_id and process it
5363 -- bhsankar Tue Jul 17 02:35:19 PDT 2007
5364 --
5365 -- bug 6132613
5366 -- Modified to fetch Ids for RMA number
5367 -- ntungare Tue Jul 17 22:54:27 PDT 2007
5368 --
5369 If (p_child_char_id = qa_ss_const.party_name OR
5370 p_child_char_id = qa_ss_const.po_number OR
5371 p_child_char_id = qa_ss_const.locator OR
5372 p_child_char_id = qa_ss_const.comp_locator OR
5373 p_child_char_id = qa_ss_const.to_locator OR
5374 p_child_char_id = qa_ss_const.rma_number ) THEN
5375 str := 'Select '|| qa_chars_api.hardcoded_column(p_child_char_id)||
5376 ' from qa_results '||
5377 ' where plan_id = :p_plan_id '||
5378 ' and collection_id = :p_collection_id '||
5379 ' and occurrence = :p_occurrence';
5380
5381 Execute Immediate str
5382 INTO id_val
5383 USING p_plan_id, p_collection_id, p_occurrence;
5384
5385 If id_val IS NOT NULL THEN
5386 RETURN id_val;
5387 End If;
5388 End if;
5389
5390 Return NULL;
5391 END get_id_val;
5392
5393 -- bug 6263809
5394 -- New function to get the quantity received for
5395 -- a particular shipment in a receipt.
5396 -- This is needed for LPN Inspections wherein
5397 -- if there is a shipment number collection element
5398 -- then the quantity validation should happen
5399 -- based on it.
5400 -- bhsankar Fri Oct 12 03:06:24 PDT 2007
5401 --
5402 --
5403 -- bug 9652549 CLM changes
5404 --
5405 PROCEDURE get_qty_for_shipment(
5406 p_po_num IN VARCHAR2,
5407 p_line_num IN VARCHAR2,
5408 p_ship_num IN NUMBER,
5409 x_qty OUT NOCOPY NUMBER) IS
5410
5411 --
5412 -- bug 9652549 CLM changes
5413 --
5414 CURSOR C1 IS
5415 SELECT (pll.quantity_received - (pll.quantity_accepted + pll.quantity_rejected)) quantity_received
5416 FROM PO_HEADERS_TRX_V ph,
5417 PO_LINE_LOCATIONS_TRX_V pll,
5418 PO_LINES_TRX_V pl
5419 WHERE pll.po_header_id = ph.po_header_id
5420 AND pll.po_line_id = pl.po_line_id
5421 AND pll.shipment_num = p_ship_num
5422 AND pl.line_num = p_line_num
5423 AND ph.segment1 = p_po_num;
5424 BEGIN
5425 OPEN C1;
5426 FETCH C1 INTO x_qty;
5427 CLOSE C1;
5428
5429 IF x_qty is null THEN
5430 x_qty := -1;
5431 END IF;
5432 END get_qty_for_shipment;
5433
5434 -- 12.1 QWB Usability Improvements
5435 -- Method to set the flags for the depenedent elements
5436 PROCEDURE set_dep_element_flag (elements IN qa_validation_api.ElementsArray,
5437 charId IN NUMBER,
5438 condition IN BOOLEAN,
5439 dep_elements_list IN OUT NOCOPY string_list,
5440 dep_flag_list IN OUT NOCOPY string_list) AS
5441
5442 enable_flag CONSTANT VARCHAR2(1) := 'E';
5443 disable_flag CONSTANT VARCHAR2(1) := 'D';
5444 status VARCHAR2(1);
5445 BEGIN
5446 IF (condition = TRUE) THEN
5447 status := enable_flag;
5448 ELSE
5449 status := disable_flag;
5450 END If;
5451
5452 If elements.exists(charId) then
5453 dep_elements_list(NVL(dep_elements_list.LAST,0)+1) := charId;
5454 dep_flag_list(NVL(dep_flag_list.LAST,0)+1) := status;
5455 End If;
5456 END;
5457
5458 -- 12.1 QWB Usability Improvements Project
5459 -- This method has been copied from the file
5460 -- INVCORE.pld. INV team doesn't have an equivalent API
5461 -- to do the processing in PL/SQL since the currrent
5462 -- processing is restricted to Forms UI only.
5463 --
5464 FUNCTION NO_NEG_BALANCE(restrict_flag IN Number,
5465 neg_flag IN Number,
5466 action IN Number)
5467 return boolean IS
5468
5469 VALUE VARCHAR2(1);
5470 DO_NOT BOOLEAN;
5471 BEGIN
5472 if (restrict_flag = 2 or restrict_flag IS NULL) then
5473 if (neg_flag = 2) THEN
5474 if (action = 1 OR action = 2 or action = 3 or
5475 action = 21 or action = 30 or action = 32) then
5476 DO_NOT := TRUE;
5477 else
5478 DO_NOT := FALSE;
5479 end if;
5480 else
5481 DO_NOT := FALSE;
5482 end if;
5483 elsif (restrict_flag = 1) then
5484 DO_NOT := TRUE;
5485
5486 else
5487 /*
5488 VALUE := restrict_flag;
5489 app_exception.invalid_argument('LOCATOR.NO_NEG_BALANCE',
5490 'RESTRICT_FLAG',
5491 VALUE);*/
5492 NULL;
5493 end if;
5494 return DO_NOT;
5495 END NO_NEG_BALANCE;
5496
5497 -- 12.1 QWB Usability Improvements Project
5498 -- This method has been copied from the file
5499 -- INVCORE.pld. INV team doesn't have an equivalent API
5500 -- to do the processing in PL/SQL since the currrent
5501 -- processing is restricted to Forms UI only.
5502 --
5503 FUNCTION CONTROL(org_control IN number,
5504 sub_control IN number,
5505 item_control IN number default NULL,
5506 restrict_flag IN Number default NULL,
5507 Neg_flag IN Number default NULL,
5508 action IN Number default NULL)
5509 return number is
5510 VALUE VARCHAR2(1);
5511 locator_control number;
5512 begin
5513
5514 if (org_control = 1) then
5515 locator_control := 1;
5516 elsif (org_control = 2) then
5517 locator_control := 2;
5518 elsif (org_control = 3) then
5519 locator_control := 3;
5520 if (no_neg_balance(restrict_flag,
5521 neg_flag,action)) then
5522 locator_control := 2;
5523 end if;
5524 elsif (org_control = 4) then
5525 if (sub_control = 1) then
5526 locator_control := 1;
5527 elsif (sub_control = 2) then
5528 locator_control := 2;
5529 elsif (sub_control = 3) then
5530 locator_control := 3;
5531 if (no_neg_balance(restrict_flag,
5532 neg_flag,action)) then
5533 locator_control := 2;
5534 end if;
5535 elsif (sub_control = 5) then
5536 if (item_control = 1) then
5537 locator_control := 1;
5538 elsif (item_control = 2) then
5539 locator_control := 2;
5540 elsif (item_control = 3) then
5541 locator_control := 3;
5542 if (no_neg_balance(restrict_flag,
5543 neg_flag,action)) then
5544 locator_control := 2;
5545 end if;
5546 elsif (item_control IS NULL) then
5547 locator_control := sub_control;
5548 else
5549 /*
5550 VALUE := item_control;
5551 app_exception.invalid_argument('LOCATOR.CONTROL',
5552 'ITEM_LOCATOR_CONTROL',
5553 VALUE);
5554 */NULL;
5555 end if;
5556 else
5557 /*
5558 VALUE := sub_control;
5559 app_exception.invalid_argument('LOCATOR.CONTROL',
5560 'SUB_LOCATOR_CONTROL',
5561 VALUE);*/
5562 NULL;
5563 end if;
5564 else
5565 /*
5566 VALUE := org_control;
5567 app_exception.invalid_argument('LOCATOR.CONTROL',
5568 'ORG_LOCATOR_CONTROL',
5569 VALUE);*/
5570 NULL;
5571 end if;
5572 return locator_control;
5573 END CONTROL;
5574
5575 --
5576 -- 12.1 QWB Usability Improvements
5577 -- Procedure that sets the dependeny rules between different elements
5578 --
5579 PROCEDURE enable_disable_dep_elements (elements IN qa_validation_api.ElementsArray,
5580 charId IN NUMBER,
5581 plan_id IN NUMBER,
5582 org_id IN NUMBER,
5583 dependent_elements OUT NOCOPY VARCHAR2,
5584 disable_enable_flag_list OUT NOCOPY VARCHAR2)
5585 AS
5586
5587 dep_elements_list string_list ;
5588 dep_flag_list string_list ;
5589 enable_flag CONSTANT VARCHAR2(1) := 'E';
5590 disable_flag CONSTANT VARCHAR2(1) := 'D';
5591
5592 l_restrict_subinventories_code NUMBER;
5593 l_restrict_locators_code NUMBER;
5594 l_location_control_code NUMBER;
5595 l_revision_qty_control_code NUMBER;
5596 l_serial_number_control_code NUMBER;
5597 l_lot_control_code NUMBER;
5598 l_primary_uom_code VARCHAR2(20);
5599
5600 X_LOC_CNTRL NUMBER;
5601
5602 enable_contition BOOLEAN;
5603
5604 CURSOR locator is
5605 SELECT stock_locator_control_code,
5606 negative_inv_receipt_code
5607 FROM mtl_parameters
5608 WHERE organization_id = org_id;
5609
5610 x_org_loc_control NUMBER;
5611 x_neg_inv NUMBER;
5612
5613 Cursor sub_loc_cur (p_subinv_name IN VARCHAR2) IS
5614 SELECT locator_type
5615 FROM mtl_secondary_inventories
5616 WHERE organization_id = org_id
5617 AND nvl(disable_date, sysdate + 1) > sysdate
5618 AND secondary_inventory_name = p_subinv_name;
5619
5620 l_sub_loc_type NUMBER := 1;
5621
5622 CURSOR c (p_item_id IN NUMBER) IS
5623 SELECT
5624 msi.restrict_subinventories_code,
5625 msi.restrict_locators_code,
5626 msi.location_control_code,
5627 msi.revision_qty_control_code,
5628 msi.serial_number_control_code,
5629 msi.lot_control_code,
5630 msi.primary_uom_code
5631 FROM
5632 mtl_system_items msi
5633 WHERE msi.organization_id = org_id AND
5634 msi.inventory_item_id = p_item_id;
5635
5636 cascaded_dep_elements_list VARCHAR2(32000);
5637 cascaded_dep_flag_list VARCHAR2(32000);
5638 disabled_elements qa_validation_api.ElementsArray;
5639 BEGIN
5640 -- build the logic to fetch the dependent elements to be enabled
5641 -- based on the lines of the code present in QLTRES.pld
5642 --
5643
5644 --
5645 -- bug 7191632
5646 -- Removed the Hard dependency between the Production Line
5647 -- Item collection elements. This dependency would be
5648 -- established using LOVs
5649 -- ntungare
5650 --
5651 -- Process the element Production Line
5652 /*
5653 If (charId = 20) then
5654 -- enable Item
5655 set_dep_element_flag(elements,
5656 10,
5657 (elements(charId).value IS NOT NULL),
5658 dep_elements_list,
5659 dep_flag_list);
5660 */
5661
5662 -- Process the element Item
5663 IF (charId = 10) then
5664 OPEN c (elements(charId).id);
5665 FETCH c INTO
5666 l_restrict_subinventories_code ,
5667 l_restrict_locators_code ,
5668 l_location_control_code ,
5669 l_revision_qty_control_code ,
5670 l_serial_number_control_code ,
5671 l_lot_control_code ,
5672 l_primary_uom_code;
5673 CLOSE c;
5674
5675 -- enable UOM
5676 set_dep_element_flag(elements,
5677 12,
5678 (elements(charId).value IS NOT NULL),
5679 dep_elements_list,
5680 dep_flag_list);
5681
5682 --enable revision
5683 set_dep_element_flag(elements,
5684 13,
5685 ((elements(charId).value IS NOT NULL) AND
5686 (l_revision_qty_control_code = 2)),
5687 dep_elements_list,
5688 dep_flag_list);
5689
5690 --enable Lot Number
5691 set_dep_element_flag(elements,
5692 16,
5693 ((elements(charId).value IS NOT NULL) AND
5694 (l_lot_control_code <> 1)),
5695 dep_elements_list,
5696 dep_flag_list);
5697
5698 --enable Serial Number
5699 set_dep_element_flag(elements,
5700 17,
5701 ((elements(charId).value IS NOT NULL) AND
5702 (l_serial_number_control_code <> 1)),
5703 dep_elements_list,
5704 dep_flag_list);
5705
5706 --enable Subinventory
5707 set_dep_element_flag(elements,
5708 14,
5709 (elements(charId).value IS NOT NULL),
5710 dep_elements_list,
5711 dep_flag_list);
5712
5713 --enable To Subinventory
5714 set_dep_element_flag(elements,
5715 2147483628,
5716 (elements(charId).value IS NOT NULL),
5717 dep_elements_list,
5718 dep_flag_list);
5719
5720 -- Process element Lot number
5721 ELSIF (charId = 16) then
5722 --enable Lot Status
5723 set_dep_element_flag(elements,
5724 188,
5725 (elements(charId).value IS NOT NULL),
5726 dep_elements_list,
5727 dep_flag_list);
5728
5729 -- Process element Serial Number
5730 ELSIF charId = 17 then
5731 --enable Serial Status
5732 set_dep_element_flag(elements,
5733 189,
5734 (elements(charId).value IS NOT NULL),
5735 dep_elements_list,
5736 dep_flag_list);
5737
5738 -- Process element Subinventory
5739 ELSIF charId = 14 then
5740 --enable Locator
5741 OPEN locator;
5742 FETCH locator INTO x_org_loc_control,
5743 x_neg_inv;
5744 CLOSE locator;
5745
5746 OPEN sub_loc_cur(elements(charId).value);
5747 FETCH sub_loc_cur INTO l_sub_loc_type;
5748 CLOSE sub_loc_cur;
5749
5750 If (elements.exists(10)) then
5751 OPEN c (elements(10).id);
5752 FETCH c INTO
5753 l_restrict_subinventories_code ,
5754 l_restrict_locators_code ,
5755 l_location_control_code ,
5756 l_revision_qty_control_code ,
5757 l_serial_number_control_code ,
5758 l_lot_control_code ,
5759 l_primary_uom_code;
5760 CLOSE c;
5761 Else
5762 l_restrict_subinventories_code := NULL;
5763 l_restrict_locators_code := NULL;
5764 l_location_control_code := NULL;
5765 l_revision_qty_control_code := NULL;
5766 l_serial_number_control_code := NULL;
5767 l_lot_control_code := NULL;
5768 l_primary_uom_code := NULL;
5769 End If;
5770
5771 IF (elements(charId).value IS NULL) THEN
5772 x_loc_cntrl := 1;
5773 Else
5774 x_loc_cntrl := CONTROL(
5775 ORG_CONTROL => x_org_loc_control,
5776 SUB_CONTROL => l_sub_loc_type,
5777 ITEM_CONTROL => l_location_control_code,
5778 RESTRICT_FLAG => l_restrict_locators_code,
5779 NEG_FLAG => x_neg_inv);
5780 End If;
5781
5782 set_dep_element_flag(elements,
5783 15,
5784 ((elements(charId).value IS NOT NULL) AND
5785 (x_LOC_CNTRL <>1 AND X_LOC_CNTRL in (2,3))),
5786 dep_elements_list,
5787 dep_flag_list);
5788
5789 -- Process element To Subinventory
5790 ELSIF charId = 2147483628 then
5791 --enable To Locator
5792 OPEN locator;
5793 FETCH locator INTO x_org_loc_control,
5794 x_neg_inv;
5795 CLOSE locator;
5796
5797 OPEN sub_loc_cur (elements(charId).value) ;
5798 FETCH sub_loc_cur INTO l_sub_loc_type;
5799 CLOSE sub_loc_cur;
5800
5801 If (elements.exists(10)) then
5802 OPEN c (elements(10).id);
5803 FETCH c INTO
5804 l_restrict_subinventories_code ,
5805 l_restrict_locators_code ,
5806 l_location_control_code ,
5807 l_revision_qty_control_code ,
5808 l_serial_number_control_code ,
5809 l_lot_control_code ,
5810 l_primary_uom_code;
5811 CLOSE c;
5812 Else
5813 l_restrict_subinventories_code := NULL;
5814 l_restrict_locators_code := NULL;
5815 l_location_control_code := NULL;
5816 l_revision_qty_control_code := NULL;
5817 l_serial_number_control_code := NULL;
5818 l_lot_control_code := NULL;
5819 l_primary_uom_code := NULL;
5820 End If;
5821
5822 IF (elements(charId).value IS NULL) THEN
5823 x_loc_cntrl := 1;
5824 Else
5825 x_loc_cntrl := CONTROL(
5826 ORG_CONTROL => x_org_loc_control,
5827 SUB_CONTROL => l_sub_loc_type,
5828 ITEM_CONTROL => l_location_control_code,
5829 RESTRICT_FLAG => l_restrict_locators_code,
5830 NEG_FLAG => x_neg_inv);
5831 End If;
5832
5833 set_dep_element_flag(elements,
5834 2147483627,
5835 ((elements(charId).value IS NOT NULL) AND
5836 (x_LOC_CNTRL <>1 AND X_LOC_CNTRL in (2,3))),
5837 dep_elements_list,
5838 dep_flag_list);
5839
5840 -- Process element Job Name
5841 ELSIF charId = 19 then
5842 -- enable TO_OP_SEQ_NUM
5843 set_dep_element_flag(elements,
5844 21,
5845 (elements(charId).value IS NOT NULL),
5846 dep_elements_list,
5847 dep_flag_list);
5848
5849 --enable FROM_OP_SEQ_NUM
5850 set_dep_element_flag(elements,
5851 22,
5852 (elements(charId).value IS NOT NULL),
5853 dep_elements_list,
5854 dep_flag_list);
5855
5856 -- Process element TO_OP_SEQ_NUM
5857 ELSIF charId = 21 then
5858 -- enable TO_INTRAOPERATION_STEP
5859 set_dep_element_flag(elements,
5860 23,
5861 (elements(charId).value IS NOT NULL),
5862 dep_elements_list,
5863 dep_flag_list);
5864
5865 -- Process element FROM_OP_SEQ_NUM
5866 ELSIF charId = 22 then
5867 -- enable TO_INTRAOPERATION_STEP
5868 set_dep_element_flag(elements,
5869 24,
5870 (elements(charId).value IS NOT NULL),
5871 dep_elements_list,
5872 dep_flag_list);
5873
5874 -- Process element PO Number
5875 ELSIF charId = 27 then
5876 -- enable PO Release Number
5877 set_dep_element_flag(elements,
5878 110,
5879 (elements(charId).value IS NOT NULL),
5880 dep_elements_list,
5881 dep_flag_list);
5882
5883 -- enable PO Line Number
5884 set_dep_element_flag(elements,
5885 28,
5886 (elements(charId).value IS NOT NULL),
5887 dep_elements_list,
5888 dep_flag_list);
5889
5890 -- Process element PO Line Number
5891 ELSIF charId = 28 then
5892 -- enable PO Release Number
5893 set_dep_element_flag(elements,
5894 29,
5895 (elements(charId).value IS NOT NULL),
5896 dep_elements_list,
5897 dep_flag_list);
5898
5899 -- Process element Project Number
5900 ELSIF charId = 121 then
5901 -- enable PO Release Number
5902 set_dep_element_flag(elements,
5903 122,
5904 (elements(charId).value IS NOT NULL),
5905 dep_elements_list,
5906 dep_flag_list);
5907
5908 -- Process element Asset Group
5909 ELSIF charId = 162 then
5910 -- enable Asset Instance Number
5911 set_dep_element_flag(elements,
5912 2147483550,
5913 (elements(charId).value IS NOT NULL),
5914 dep_elements_list,
5915 dep_flag_list);
5916
5917 -- bug 13841504
5918 -- enable Asset Serial Number only if Asset instance number doesnt exist
5919 IF (elements.exists(2147483550)=FALSE) THEN
5920 set_dep_element_flag(elements,
5921 163,
5922 (elements(charId).value IS NOT NULL),
5923 dep_elements_list,
5924 dep_flag_list);
5925 END IF;
5926
5927 -- enable Asset Activity
5928 set_dep_element_flag(elements,
5929 164,
5930 (elements(charId).value IS NOT NULL),
5931 dep_elements_list,
5932 dep_flag_list);
5933
5934 -- Bug 13841504
5935 -- Process element Asset Instance Number (Asset Num)
5936 ELSIF charId = 2147483550 then
5937 -- enable Asset Serial Number
5938 set_dep_element_flag(elements,
5939 163,
5940 (elements(charId).value IS NOT NULL),
5941 dep_elements_list,
5942 dep_flag_list);
5943
5944 -- enable Asset Activity
5945 set_dep_element_flag(elements,
5946 164,
5947 (elements(charId).value IS NOT NULL),
5948 dep_elements_list,
5949 dep_flag_list);
5950
5951 -- Process element PROCESS_BATCHSTEP_NUM
5952 ELSIF charId = 2147483555 then
5953 -- enable PROCESS_OPERATION
5954 set_dep_element_flag(elements,
5955 2147483554,
5956 (elements(charId).value IS NOT NULL),
5957 dep_elements_list,
5958 dep_flag_list);
5959
5960 -- Process element PROCESS_OPERATION
5961 ELSIF charId = 2147483554 then
5962 -- enable PROCESS_ACTIVITY
5963 set_dep_element_flag(elements,
5964 2147483553,
5965 (elements(charId).value IS NOT NULL),
5966 dep_elements_list,
5967 dep_flag_list);
5968
5969 -- Process element PROCESS_ACTIVITY
5970 ELSIF charId = 2147483553 then
5971 -- enable PROCESS_RESOURCE
5972 set_dep_element_flag(elements,
5973 2147483552,
5974 (elements(charId).value IS NOT NULL),
5975 dep_elements_list,
5976 dep_flag_list);
5977
5978 -- Process element PROCESS_RESOURCE
5979 ELSIF charId = 2147483552 then
5980 -- enable PROCESS_PARAMETER
5981 set_dep_element_flag(elements,
5982 2147483551,
5983 (elements(charId).value IS NOT NULL),
5984 dep_elements_list,
5985 dep_flag_list);
5986
5987 -- Process the element Comp Item
5988 ELSIF charId = 60 then
5989 OPEN c (elements(charId).id);
5990 FETCH c INTO
5991 l_restrict_subinventories_code ,
5992 l_restrict_locators_code ,
5993 l_location_control_code ,
5994 l_revision_qty_control_code ,
5995 l_serial_number_control_code ,
5996 l_lot_control_code ,
5997 l_primary_uom_code;
5998 CLOSE c;
5999
6000 -- enable COMP_UOM
6001 set_dep_element_flag(elements,
6002 62,
6003 (elements(charId).value IS NOT NULL),
6004 dep_elements_list,
6005 dep_flag_list);
6006
6007 --enable COMP_revision
6008 set_dep_element_flag(elements,
6009 63,
6010 ((elements(charId).value IS NOT NULL) AND
6011 (l_revision_qty_control_code = 2)),
6012 dep_elements_list,
6013 dep_flag_list);
6014
6015 --enable Comp Lot Number
6016 set_dep_element_flag(elements,
6017 66,
6018 ((elements(charId).value IS NOT NULL) AND
6019 (l_lot_control_code <> 1)),
6020 dep_elements_list,
6021 dep_flag_list);
6022
6023 --enable Comp Serial Number
6024 set_dep_element_flag(elements,
6025 67,
6026 ((elements(charId).value IS NOT NULL) AND
6027 (l_serial_number_control_code <> 1)),
6028 dep_elements_list,
6029 dep_flag_list);
6030
6031 --enable Subinventory
6032 set_dep_element_flag(elements,
6033 64,
6034 (elements(charId).value IS NOT NULL),
6035 dep_elements_list,
6036 dep_flag_list);
6037
6038 -- Process element Comp Subinventory
6039 ELSIF charId = 64 then
6040 --enable Comp Locator
6041 OPEN locator;
6042 FETCH locator INTO x_org_loc_control,
6043 x_neg_inv;
6044 CLOSE locator;
6045
6046 OPEN sub_loc_cur (elements(charId).value) ;
6047 FETCH sub_loc_cur INTO l_sub_loc_type;
6048 CLOSE sub_loc_cur;
6049
6050 If (elements.exists(60)) then
6051 OPEN c (elements(60).id);
6052 FETCH c INTO
6053 l_restrict_subinventories_code ,
6054 l_restrict_locators_code ,
6055 l_location_control_code ,
6056 l_revision_qty_control_code ,
6057 l_serial_number_control_code ,
6058 l_lot_control_code ,
6059 l_primary_uom_code;
6060 CLOSE c;
6061 Else
6062 l_restrict_subinventories_code := NULL;
6063 l_restrict_locators_code := NULL;
6064 l_location_control_code := NULL;
6065 l_revision_qty_control_code := NULL;
6066 l_serial_number_control_code := NULL;
6067 l_lot_control_code := NULL;
6068 l_primary_uom_code := NULL;
6069 End If;
6070
6071 IF (elements(charId).value IS NULL) THEN
6072 x_loc_cntrl := 1;
6073 Else
6074 x_loc_cntrl := CONTROL(
6075 ORG_CONTROL => x_org_loc_control,
6076 SUB_CONTROL => l_sub_loc_type,
6077 ITEM_CONTROL => l_location_control_code,
6078 RESTRICT_FLAG => l_restrict_locators_code,
6079 NEG_FLAG => x_neg_inv);
6080 End If;
6081
6082 --
6083 -- bug 7194001
6084 -- The locator field was getting modified
6085 -- incorrectly instead of the comp locator
6086 -- ntungare
6087 --
6088 set_dep_element_flag(elements,
6089 65,
6090 ((elements(charId).value IS NOT NULL) AND
6091 (x_LOC_CNTRL <>1 AND X_LOC_CNTRL in (2,3))),
6092 dep_elements_list,
6093 dep_flag_list);
6094
6095 -- Bug 7716875.Added dependency between sales_order
6096 -- and so line_num.pdube Mon Apr 13 03:25:19 PDT 2009.
6097 ELSIF charId = 33 THEN
6098 -- enable SO Line Number
6099 set_dep_element_flag(elements,
6100 35,
6101 (elements(charId).value IS NOT NULL),
6102 dep_elements_list,
6103 dep_flag_list);
6104 END IF;
6105
6106 -- initializing the disabled elements array as
6107 -- equal to the elements array
6108 --
6109 disabled_elements := elements;
6110
6111 IF dep_elements_list.count <> 0 then
6112 FOR cntr in 1..dep_elements_list.count
6113 LOOP
6114 dependent_elements := dependent_elements ||','|| dep_elements_list(cntr);
6115 disable_enable_flag_list := disable_enable_flag_list ||','|| dep_flag_list(cntr);
6116
6117 -- If a collection element has been diasbled, all its
6118 -- dependent elements must also be disabled
6119 --
6120 IF (dep_flag_list(cntr) = disable_flag) THEN
6121 -- Since the element is to be disbaled. Hence setting the value as NULL.
6122 IF elements.exists(dep_elements_list(cntr)) THEN
6123 disabled_elements(dep_elements_list(cntr)).value := NULL;
6124 null;
6125 END IF;
6126
6127 enable_disable_dep_elements(disabled_elements,
6128 dep_elements_list(cntr),
6129 plan_id,
6130 org_id,
6131 cascaded_dep_elements_list,
6132 cascaded_dep_flag_list);
6133
6134 IF (cascaded_dep_elements_list IS NOT NULL) THEN
6135 dependent_elements := dependent_elements ||','|| cascaded_dep_elements_list;
6136 disable_enable_flag_list := disable_enable_flag_list || ',' || cascaded_dep_flag_list ;
6137 END IF;
6138 END IF;
6139 END LOOP;
6140 ELSE
6141 RETURN;
6142 END If;
6143
6144 dependent_elements := LTRIM(dependent_elements ,',');
6145 disable_enable_flag_list := LTRIM(disable_enable_flag_list, ',') ;
6146
6147 END enable_disable_dep_elements ;
6148
6149 -- 12.1 QWB Usability Improvemenets
6150 -- New procedure to process dependent elements
6151 PROCEDURE process_dependent_elements(result_string IN VARCHAR2,
6152 id_string IN VARCHAR2,
6153 org_id IN NUMBER,
6154 p_plan_id IN NUMBER,
6155 char_Id IN VARCHAR2,
6156 dependent_elements OUT NOCOPY VARCHAR2,
6157 disable_enable_flag_list OUT NOCOPY VARCHAR2)
6158 AS
6159 elements qa_validation_api.ElementsArray;
6160 char_cntr NUMBER;
6161 dep_elements_list VARCHAR2(4000);
6162 dep_flag_list VARCHAR2(4000);
6163
6164 l_result_string VARCHAR2(32767);
6165
6166 Cursor plan_chars_cur is
6167 select char_id from qa_plan_chars
6168 where plan_id = p_plan_id;
6169 BEGIN
6170 l_result_string := result_string;
6171
6172 -- Handling for NULL result string. In this case
6173 -- build a result string with all the elements
6174 -- set as NULL
6175 if (l_result_string IS NULL) THEN
6176 For rad in plan_chars_cur
6177 loop
6178 l_result_string := l_result_string||'@'||rad.char_id||'=';
6179 end loop;
6180 l_result_string := LTRIM(l_result_string,'@');
6181 end If;
6182
6183 -- Builid elements array
6184 elements := qa_validation_api.result_to_array(l_result_string);
6185 elements := qa_validation_api.id_to_array(id_string, elements);
6186
6187 -- If char id is NULL then the entire row is to be
6188 -- processed
6189 If char_id IS NULL THEN
6190 char_cntr := elements.first;
6191
6192 -- Process all elements
6193 while char_cntr <= elements.last
6194 loop
6195 -- Get the dependent elemnts list and the flags
6196 enable_disable_dep_elements(elements,
6197 char_cntr,
6198 p_plan_id,
6199 org_id,
6200 dep_elements_list,
6201 dep_flag_list);
6202
6203 if dep_elements_list is not null then
6204 dependent_elements := dependent_elements ||','||dep_elements_list;
6205 disable_enable_flag_list := disable_enable_flag_list||','||dep_flag_list;
6206 end if;
6207 char_cntr := elements.next(char_cntr);
6208 end loop;
6209
6210 -- If char id is NOT NULL then the specific element
6211 -- is to be processed
6212 Else
6213 -- Get the dependent elemnts list and the flags
6214 enable_disable_dep_elements(elements,
6215 char_Id,
6216 p_plan_id,
6217 org_id,
6218 dep_elements_list,
6219 dep_flag_list);
6220
6221 dependent_elements := dep_elements_list;
6222 disable_enable_flag_list := dep_flag_list;
6223 End If;
6224
6225 If dependent_elements IS NOT NULL THEN
6226 dependent_elements := LTRIM(dependent_elements ,',');
6227 disable_enable_flag_list := LTRIM(disable_enable_flag_list ,',');
6228 End If;
6229 END process_dependent_elements;
6230
6231 --
6232 -- 12.1 QWB Usabitlity Improvements
6233 -- Function to build the Info column value
6234 --
6235 FUNCTION build_info_column(p_plan_id IN NUMBER,
6236 p_collection_id IN NUMBER,
6237 p_occurrence IN NUMBER)
6238 RETURN VARCHAR2
6239 AS
6240
6241 Type hardcoded_char_tab_typ IS TABLE OF NUMBER INDEX BY binary_integer;
6242 hardcoded_char_tab hardcoded_char_tab_typ;
6243
6244 cols_str VARCHAR2(32767);
6245 char_ids_str VARCHAR2(32767);
6246
6247 plan_name VARCHAR2(100);
6248
6249 result_str VARCHAR2(32767);
6250
6251 char_name VARCHAR(100);
6252
6253 p_values_string VARCHAR2(32767) := NULL;
6254 BEGIN
6255 -- get the list of information columns
6256 --
6257 -- bug 7115965
6258 -- Added an order by clause
6259 -- ntungare
6260 --
6261 SELECT qpc.char_id bulk collect
6262 INTO hardcoded_char_tab
6263 FROM qa_plan_chars qpc
6264 WHERE qpc.plan_id = p_plan_id
6265 AND qpc.information_flag = 1
6266 ORDER BY prompt_sequence;
6267
6268 -- get the columns names corresponding to
6269 -- the information columns
6270 -- The processing is to be done only if there are any
6271 -- information columns.
6272 if hardcoded_char_tab.count <> 0 then
6273 FOR cntr IN 1 .. hardcoded_char_tab.COUNT
6274 LOOP
6275 -- get the column name to select from view
6276 Select upper(translate(name,' ''*{}','_____')) into char_name
6277 from qa_chars
6278 where char_id = hardcoded_char_tab(cntr);
6279
6280 -- build a list of columns to select from the
6281 -- plan view
6282 --
6283 -- bug 7115965
6284 -- Changed the separator from space to comma
6285 -- ntungare
6286 --
6287 cols_str := cols_str || char_name||'||'', ''||';
6288
6289 END LOOP;
6290
6291 -- build the plan view name
6292 SELECT deref_view_name INTO plan_name
6293 FROM qa_plans
6294 WHERE plan_id = p_plan_id;
6295
6296 --
6297 -- bug 7115965
6298 -- Changed the separator from space to comma
6299 -- ntungare
6300 --
6301 cols_str := RTRIM(cols_str, '||'', ''||');
6302
6303 -- Execute the dynamic query and get the information cols string
6304 EXECUTE IMMEDIATE 'Select ' || cols_str || ' from ' || plan_name ||
6305 ' where collection_id = :collection_id and
6306 occurrence = :occurrence'
6307 INTO p_values_string
6308 USING p_collection_id,
6309 p_occurrence;
6310 end if;
6311
6312 return p_values_string;
6313 END build_info_column;
6314
6315 END qa_plan_element_api;
[Home] [Help]