1 PACKAGE BODY qa_plan_element_api AS
2 /* $Header: qltelemb.plb 120.35.12010000.2 2008/10/17 08:03:50 pdube 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_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 FUNCTION get_so_line_number_id (value IN VARCHAR2)
1245 RETURN NUMBER IS
1246
1247 id NUMBER;
1248
1249 CURSOR c (h_id IN VARCHAR2) IS
1250 SELECT sl.line_number
1251 FROM mtl_system_items_kfv msik, so_lines sl
1252 WHERE sl.inventory_item_id = msik.inventory_item_id
1253 AND header_id = h_id;
1254
1255 BEGIN
1256
1257 IF value IS NULL THEN
1258 RETURN NULL;
1259 END IF;
1260
1261 OPEN c(value);
1262 FETCH c INTO id;
1263 CLOSE c;
1264
1265 RETURN id;
1266
1267 END get_so_line_number_id;
1268
1269
1270 FUNCTION get_po_release_number_id (value IN VARCHAR2, x_po_header_id IN NUMBER)
1271 RETURN NUMBER IS
1272
1273 id NUMBER;
1274
1275 CURSOR c (p_id NUMBER, r_num VARCHAR2) IS
1276 SELECT pr.po_release_id
1277 FROM po_releases pr
1278 WHERE pr.po_header_id = p_id
1279 AND pr.release_num = r_num;
1280
1281 BEGIN
1282
1283 IF value IS NULL THEN
1284 RETURN NULL;
1285 END IF;
1286
1287 OPEN c(x_po_header_id, value);
1288 FETCH c INTO id;
1289 CLOSE c;
1290
1291 RETURN id;
1292
1293 END get_po_release_number_id;
1294
1295
1296 FUNCTION get_project_number_id (value IN VARCHAR2)
1297 RETURN NUMBER IS
1298
1299 id NUMBER;
1300 /*
1301 mtl_project_v changed to pjm_projects_all_v (selects from both pjm enabled and
1302 non-pjm enabled orgs).
1303 rkaza, 11/10/2001.
1304 */
1305
1306 --
1307 -- the sql has to be changed as pjm_projects_all_v is operating unit sensitive.
1308 -- has to change to a sql that searching all operating units.
1309 -- reference bug 3578563
1310 -- jezheng
1311 -- Mon Apr 19 12:20:16 PDT 2004
1312 --
1313 /* CURSOR c (p_num VARCHAR2) IS
1314 SELECT project_id
1315 FROM pjm_projects_all_v
1316 WHERE project_number = p_num;
1317 */
1318
1319 cursor c (p_proj_num varchar2) is
1320 select project_id
1321 from pa_projects_all
1322 where segment1 = p_proj_num
1323 UNION ALL
1324 select project_id
1325 from pjm_seiban_numbers
1326 where project_number = p_proj_num;
1327
1328 BEGIN
1329
1330 IF value IS NULL THEN
1331 RETURN NULL;
1332 END IF;
1333
1334 OPEN c(value);
1335 FETCH c INTO id;
1336 CLOSE c;
1337
1338 RETURN id;
1339
1340 END get_project_number_id;
1341
1342
1343 --
1344 -- Bug 2672396. Added p_project_id because task is a dependent element.
1345 -- Fix is required in qltvalb.plb also.
1346 -- bso Mon Nov 25 17:29:56 PST 2002
1347 --
1348 FUNCTION get_task_number_id(value IN VARCHAR2, p_project_id IN NUMBER)
1349 RETURN NUMBER IS
1350
1351 id NUMBER;
1352
1353 --
1354 -- The sql is operating unit sensitive. Has to be changed to a sql
1355 -- that searches all operating units.
1356 -- reference bug 3578563
1357 -- jezheng
1358 -- Mon Apr 19 12:25:23 PDT 2004
1359 --
1360
1361 /* CURSOR c (p_task_number VARCHAR2, p_project_id NUMBER) IS
1362 SELECT task_id
1363 FROM mtl_task_v
1364 WHERE task_number = p_task_number AND project_id = p_project_id;
1365 */
1366
1367 cursor c (p_task_num varchar2, p_proj_id number) is
1368 select TASK_ID
1369 from pa_tasks
1370 where PROJECT_ID = p_proj_id and
1371 task_number = p_task_num;
1372
1373 BEGIN
1374
1375 IF value IS NULL THEN
1376 RETURN NULL;
1377 END IF;
1378
1379 OPEN c(value, p_project_id);
1380 FETCH c INTO id;
1381 CLOSE c;
1382
1383 RETURN id;
1384
1385 END get_task_number_id;
1386
1387
1388 FUNCTION get_rma_number_id (value IN VARCHAR2)
1389 RETURN NUMBER IS
1390
1391 id NUMBER;
1392
1393 CURSOR C(v VARCHAR2) IS
1394 SELECT sh.header_id
1395 FROM so_order_types sot,
1396 oe_order_headers sh,
1397 qa_customers_lov_v rc
1398 WHERE sh.order_type_id = sot.order_type_id and
1399 sh.sold_to_org_id = rc.customer_id and
1400 sh.order_category_code in ('RETURN', 'MIXED') and
1401 sh.order_number = v;
1402
1403 BEGIN
1404
1405 IF value IS NULL THEN
1406 RETURN NULL;
1407 END IF;
1408
1409 OPEN c(value);
1410 FETCH c INTO id;
1411 CLOSE c;
1412
1413 RETURN id;
1414
1415 END get_rma_number_id;
1416
1417 FUNCTION get_LPN_id (value IN VARCHAR2)
1418 RETURN NUMBER IS
1419
1420 id NUMBER;
1421
1422 CURSOR c (t_id VARCHAR2) IS
1423 SELECT LPN_ID
1424 FROM WMS_LICENSE_PLATE_NUMBERS
1425 WHERE LICENSE_PLATE_NUMBER = t_id;
1426
1427 BEGIN
1428
1429 IF value IS NULL THEN
1430 RETURN NULL;
1431 END IF;
1432
1433 OPEN c(value);
1434 FETCH c INTO id;
1435 CLOSE c;
1436
1437 RETURN id;
1438
1439 END get_LPN_id;
1440
1441 -- added the following to include new hardcoded element Transfer license plate number
1442 -- saugupta Aug 2003
1443
1444 FUNCTION get_XFR_LPN_id (value IN VARCHAR2)
1445 RETURN NUMBER IS
1446
1447 id NUMBER;
1448
1449 CURSOR c (t_id VARCHAR2) IS
1450 SELECT LPN_ID
1451 FROM WMS_LICENSE_PLATE_NUMBERS
1452 WHERE LICENSE_PLATE_NUMBER = t_id;
1453
1454 BEGIN
1455
1456 IF value IS NULL THEN
1457 RETURN NULL;
1458 END IF;
1459
1460 OPEN c(value);
1461 FETCH c INTO id;
1462 CLOSE c;
1463
1464 RETURN id;
1465
1466 END get_XFR_LPN_id;
1467
1468 FUNCTION get_contract_id (value IN VARCHAR2)
1469 RETURN NUMBER IS
1470
1471 id NUMBER := NULL;
1472
1473 CURSOR c (val VARCHAR2) IS
1474 SELECT k_header_id
1475 FROM oke_k_headers_lov_v
1476 WHERE k_number = val;
1477
1478 BEGIN
1479
1480 IF value is NOT NULL THEN
1481 OPEN c(value);
1482 FETCH c INTO id;
1483 CLOSE c;
1484 END IF;
1485
1486 RETURN id;
1487
1488 END get_contract_id;
1489
1490 FUNCTION get_contract_line_id (value IN VARCHAR2)
1491 RETURN NUMBER IS
1492
1493 id NUMBER := NULL;
1494
1495 CURSOR c (val VARCHAR2) IS
1496 SELECT k_line_id
1497 FROM oke_k_lines_full_v
1498 WHERE line_number = val;
1499
1500 BEGIN
1501
1502 IF value is NOT NULL THEN
1503 OPEN c(value);
1504 FETCH c INTO id;
1505 CLOSE c;
1506 END IF;
1507
1508 RETURN id;
1509
1510 END get_contract_line_id;
1511
1512 FUNCTION get_deliverable_id (value IN VARCHAR2)
1513 RETURN NUMBER IS
1514
1515 id NUMBER := NULL;
1516
1517 CURSOR c (val VARCHAR2) IS
1518 SELECT deliverable_id
1519 FROM oke_k_deliverables_vl
1520 WHERE deliverable_num = val;
1521
1522 BEGIN
1523
1524 IF value is NOT NULL THEN
1525 OPEN c(value);
1526 FETCH c INTO id;
1527 CLOSE c;
1528 END IF;
1529
1530 RETURN id;
1531
1532 END get_deliverable_id;
1533
1534
1535
1536 FUNCTION get_work_order_id (org_id IN NUMBER, value IN VARCHAR2)
1537 RETURN NUMBER IS
1538
1539 id NUMBER;
1540
1541 /* rkaza 10/21/2002. Bug 2635736 */
1542 CURSOR c (w_e_name VARCHAR2, o_id NUMBER) IS
1543 SELECT WDJ.wip_entity_id
1544 FROM wip_entities WE, wip_discrete_jobs WDJ
1545 WHERE WDJ.status_type in (3,4) and
1546 WDJ.wip_entity_id = WE.wip_entity_id and
1547 WE.entity_type IN (6, 7) and
1548 WE.wip_entity_name = w_e_name
1549 AND WDJ.organization_id = o_id;
1550
1551 BEGIN
1552
1553 IF value IS NULL THEN
1554 RETURN NULL;
1555 END IF;
1556
1557 OPEN c(value, org_id);
1558 FETCH c INTO id;
1559 CLOSE c;
1560
1561 RETURN id;
1562
1563 END get_work_order_id;
1564
1565
1566
1567 FUNCTION get_party_id (value IN VARCHAR2)
1568 RETURN NUMBER IS
1569
1570 id NUMBER;
1571
1572 CURSOR c (p_name VARCHAR2) IS
1573 SELECT party_id
1574 FROM hz_parties
1575 WHERE status = 'A'
1576 AND party_name = p_name
1577 AND party_type IN ('ORGANIZATION','PERSON')
1578 ORDER BY party_name;
1579
1580 BEGIN
1581
1582 IF value IS NULL THEN
1583 RETURN NULL;
1584 END IF;
1585
1586 OPEN c(value);
1587 FETCH c INTO id;
1588 CLOSE c;
1589
1590 RETURN id;
1591
1592 END get_party_id;
1593
1594 --
1595 -- Implemented the following get_id functions for
1596 -- Service_Item, Counter, Maintenance_Requirement,
1597 -- Service_Request, Rework_Job
1598 -- For ASO project
1599 -- rkunchal Thu Jul 25 01:43:48 PDT 2002
1600 --
1601
1602 FUNCTION get_item_instance_id (value IN VARCHAR2)
1603 RETURN NUMBER IS
1604
1605 id NUMBER;
1606
1607 CURSOR c (i_num VARCHAR2) IS
1608 SELECT cii.instance_id
1609 FROM qa_csi_item_instances cii, mtl_system_items_kfv msik
1610 WHERE cii.instance_number = i_num
1611 AND cii.last_vld_organization_id = msik.organization_id;
1612
1613 BEGIN
1614
1615 IF value IS NULL THEN
1616 RETURN NULL;
1617 END IF;
1618
1619 OPEN c(value);
1620 FETCH c INTO id;
1621 CLOSE c;
1622
1623 RETURN id;
1624
1625 END get_item_instance_id;
1626
1627
1628 FUNCTION get_counter_name_id (value IN VARCHAR2)
1629 RETURN NUMBER IS
1630
1631 id NUMBER;
1632 -- Bug 4958763. SQL Repository Fix SQL ID: 15008597
1633 -- to maintain consistency using IB view for counters
1634 -- replacing cs_counters with csi_counters_vl
1635 CURSOR c (c_name VARCHAR2) IS
1636 SELECT cc.counter_id
1637 FROM csi_counters_vl cc
1638 WHERE cc.name = c_name;
1639 /*
1640 SELECT cc.counter_id
1641 FROM cs_counters cc, cs_counter_groups ccg
1642 WHERE cc.counter_group_id = ccg.counter_group_id
1643 AND ccg.template_flag = 'N'
1644 AND cc.name = c_name;
1645 */
1646
1647 BEGIN
1648
1649 IF value IS NULL THEN
1650 RETURN NULL;
1651 END IF;
1652
1653 OPEN c(value);
1654 FETCH c INTO id;
1655 CLOSE c;
1656
1657 RETURN id;
1658
1659 END get_counter_name_id;
1660
1661
1662 FUNCTION get_maintenance_req_id (value IN VARCHAR2)
1663 RETURN NUMBER IS
1664
1665 id NUMBER;
1666
1667 CURSOR c (mr_title VARCHAR2) IS
1668 SELECT mr_header_id
1669 FROM qa_ahl_mr
1670 WHERE title = mr_title;
1671
1672 BEGIN
1673
1674 IF value IS NULL THEN
1675 RETURN NULL;
1676 END IF;
1677
1678 OPEN c(value);
1679 FETCH c INTO id;
1680 CLOSE c;
1681
1682 RETURN id;
1683
1684 END get_maintenance_req_id;
1685
1686
1687 FUNCTION get_service_request_id (value IN VARCHAR2)
1688 RETURN NUMBER IS
1689
1690 id NUMBER;
1691
1692 CURSOR c (s_request VARCHAR2) IS
1693 SELECT incident_id
1694 FROM cs_incidents
1695 WHERE incident_number = s_request;
1696
1697 BEGIN
1698
1699 IF value IS NULL THEN
1700 RETURN NULL;
1701 END IF;
1702
1703 OPEN c(value);
1704 FETCH c INTO id;
1705 CLOSE c;
1706
1707 RETURN id;
1708
1709 END get_service_request_id;
1710
1711
1712 FUNCTION get_rework_job_id (org_id IN NUMBER, value IN VARCHAR2)
1713 RETURN NUMBER IS
1714
1715 id NUMBER;
1716
1717 CURSOR c (w_e_name VARCHAR2, o_id NUMBER) IS
1718 SELECT wip_entity_id
1719 FROM wip_discrete_jobs_all_v
1720 WHERE wip_entity_name = w_e_name
1721 AND organization_id = o_id;
1722
1723 BEGIN
1724
1725 IF value IS NULL THEN
1726 RETURN NULL;
1727 END IF;
1728
1729 OPEN c(value, org_id);
1730 FETCH c INTO id;
1731 CLOSE c;
1732
1733 RETURN id;
1734
1735 END get_rework_job_id;
1736
1737 --
1738 -- End of inclusions for ASO project
1739 -- rkunchal Thu Jul 25 01:43:48 PDT 2002
1740 --
1741 -- R12 OPM Deviations. Bug 4345503 Start
1742
1743 FUNCTION get_process_batch_id (value IN VARCHAR2,p_org_id IN NUMBER)
1744 RETURN NUMBER IS
1745 id NUMBER;
1746 CURSOR c (batch_num VARCHAR2,o_id NUMBER) IS
1747 SELECT BATCH_ID
1748 FROM GME_BATCH_HEADER
1749 WHERE BATCH_NO = batch_num
1750 AND ( ORGANIZATION_ID IS NULL OR
1751 ORGANIZATION_ID = o_id );
1752 BEGIN
1753 IF value IS NULL THEN
1754 RETURN NULL;
1755 END IF;
1756
1757 OPEN c(value,p_org_id);
1758 FETCH c INTO id;
1759 CLOSE c;
1760
1761 RETURN id;
1762 END get_process_batch_id;
1763
1764 FUNCTION get_process_batchstep_id (value IN VARCHAR2,
1765 p_process_batch_id IN NUMBER)
1766 RETURN NUMBER IS
1767 id NUMBER;
1768 CURSOR c (l_batchstep_num VARCHAR2, l_batch_id NUMBER) is
1769 SELECT BATCHSTEP_ID
1770 FROM GME_BATCH_STEPS
1771 WHERE BATCHSTEP_NO = L_BATCHSTEP_NUM
1772 AND BATCH_ID = L_BATCH_ID;
1773 BEGIN
1774
1775 IF value IS NULL THEN
1776 RETURN NULL;
1777 END IF;
1778
1779 OPEN c(value,p_process_batch_id);
1780 FETCH c INTO id;
1781 CLOSE c;
1782
1783 RETURN id;
1784
1785 END get_process_batchstep_id;
1786
1787 FUNCTION get_process_operation_id (value IN VARCHAR2,
1788 p_process_batch_id IN NUMBER,
1789 p_process_batchstep_id IN NUMBER)
1790 RETURN NUMBER IS
1791 id NUMBER;
1792
1793 CURSOR c (l_operation VARCHAR2, l_batch_id NUMBER, l_batchstep_id VARCHAR2) is
1794 SELECT OPRN_ID
1795 FROM GMO_BATCH_STEPS_V
1796 WHERE OPERATION = L_OPERATION
1797 AND BATCH_ID = L_BATCH_ID
1798 AND BATCHSTEP_ID = L_BATCHSTEP_ID;
1799 BEGIN
1800 IF value IS NULL THEN
1801 RETURN NULL;
1802 END IF;
1803
1804 OPEN c(value,p_process_batch_id, p_process_batchstep_id);
1805 FETCH c INTO id;
1806 CLOSE c;
1807
1808 RETURN id;
1809
1810 END get_process_operation_id;
1811
1812 FUNCTION get_process_activity_id (value IN VARCHAR2,
1813 p_process_batch_id IN NUMBER,
1814 p_process_batchstep_id IN NUMBER)
1815 RETURN NUMBER IS
1816 id NUMBER;
1817 CURSOR c (l_activity VARCHAR2, l_batch_id NUMBER, l_batchstep_id NUMBER) is
1818 SELECT BATCHSTEP_ACTIVITY_ID
1819 FROM GME_BATCH_STEP_ACTIVITIES
1820 WHERE ACTIVITY = L_ACTIVITY
1821 AND BATCH_ID = L_BATCH_ID
1822 AND BATCHSTEP_ID = L_BATCHSTEP_ID ;
1823 BEGIN
1824 IF value IS NULL THEN
1825 RETURN NULL;
1826 END IF;
1827
1828 OPEN c(value,p_process_batch_id, p_process_batchstep_id);
1829 FETCH c INTO id;
1830 CLOSE c;
1831
1832 RETURN id;
1833
1834 END get_process_activity_id;
1835
1836 FUNCTION get_process_resource_id (value IN VARCHAR2,
1837 p_process_batch_id IN NUMBER,
1838 p_process_batchstep_id IN NUMBER,
1839 p_process_activity_id IN NUMBER)
1840 RETURN NUMBER IS
1841 id NUMBER;
1842 CURSOR c (l_resources VARCHAR2, l_batch_id NUMBER,
1843 l_batchstep_id NUMBER, l_activity_id NUMBER) is
1844 SELECT BATCHSTEP_RESOURCE_ID
1845 FROM GME_BATCH_STEP_RESOURCES
1846 WHERE RESOURCES = L_RESOURCES
1847 AND BATCH_ID = L_BATCH_ID
1848 AND BATCHSTEP_ID = L_BATCHSTEP_ID
1849 AND BATCHSTEP_ACTIVITY_ID = L_ACTIVITY_ID;
1850 BEGIN
1851 IF value IS NULL THEN
1852 RETURN NULL;
1853 END IF;
1854
1855 OPEN c(value,p_process_batch_id, p_process_batchstep_id,
1856 p_process_activity_id);
1857 FETCH c INTO id;
1858 CLOSE c;
1859
1860 RETURN id;
1861
1862 END get_process_resource_id;
1863
1864 FUNCTION get_process_parameter_id (value IN VARCHAR2,
1865 p_process_resource_id IN NUMBER)
1866 RETURN NUMBER IS
1867 id NUMBER;
1868 CURSOR c (l_parameter VARCHAR2, l_resource_id NUMBER) is
1869 SELECT GP.PARAMETER_ID
1870 FROM GMP_PROCESS_PARAMETERS GP, GME_PROCESS_PARAMETERS GE
1871 WHERE GP.PARAMETER_NAME = L_PARAMETER
1872 AND GP.PARAMETER_ID = GE.PARAMETER_ID
1873 AND GE.BATCHSTEP_RESOURCE_ID= L_RESOURCE_ID;
1874 BEGIN
1875 IF value IS NULL THEN
1876 RETURN NULL;
1877 END IF;
1878
1879 OPEN c(value,p_process_resource_id);
1880 FETCH c INTO id;
1881 CLOSE c;
1882
1883 RETURN id;
1884
1885 END get_process_parameter_id;
1886
1887 -- R12 OPM Deviations. Bug 4345503 End
1888
1889 /* R12 DR Integration. Bug 4345489 Start */
1890
1891 FUNCTION get_repair_line_id (value IN VARCHAR2)
1892 RETURN NUMBER
1893 IS
1894
1895 id NUMBER;
1896
1897 cursor c (p_ro_num varchar2) is
1898 select repair_line_id
1899 from csd_repairs
1900 where repair_number = p_ro_num;
1901
1902
1903 BEGIN
1904
1905 IF value IS NULL THEN
1906 RETURN NULL;
1907 END IF;
1908
1909 OPEN c(value);
1910 FETCH c INTO id;
1911 CLOSE c;
1912
1913 RETURN id;
1914
1915 END get_repair_line_id;
1916
1917
1918 FUNCTION get_jtf_task_id (value IN VARCHAR2)
1919 RETURN NUMBER
1920 IS
1921
1922 id NUMBER;
1923
1924 cursor c (p_task_num varchar2) is
1925 select task_id
1926 from jtf_tasks_b
1927 where task_number = p_task_num;
1928
1929
1930 BEGIN
1931
1932 IF value IS NULL THEN
1933 RETURN NULL;
1934 END IF;
1935
1936 OPEN c(value);
1937 FETCH c INTO id;
1938 CLOSE c;
1939
1940 RETURN id;
1941
1942 END get_jtf_task_id;
1943
1944 /* R12 DR Integration. Bug 4345489 End */
1945
1946 -- Start of inclusions for NCM Hardcode Elements.
1947 -- suramasw Thu Oct 31 10:48:59 PST 2002.
1948 -- Bug 2449067.
1949
1950
1951 FUNCTION validate_to_subinventory (x_org_id IN NUMBER, x_to_subinventory IN VARCHAR2)
1952 RETURN BOOLEAN IS
1953
1954 CURSOR c IS
1955 SELECT 1
1956 FROM mtl_secondary_inventories
1957 WHERE organization_id = x_org_id
1958 AND nvl(disable_date, sysdate+1) > sysdate
1959 AND secondary_inventory_name = x_to_subinventory;
1960
1961 result BOOLEAN;
1962 dummy NUMBER;
1963
1964 BEGIN
1965
1966 OPEN c;
1967 FETCH c INTO dummy;
1968 result := c%FOUND;
1969 CLOSE c;
1970
1971 RETURN result;
1972
1973 END validate_to_subinventory;
1974
1975 -- End of inclusions for NCM Hardcode Elements.
1976
1977 FUNCTION retrieve_id (sql_statement IN VARCHAR2)
1978 RETURN NUMBER IS
1979
1980 retrieved_id NUMBER;
1981
1982 BEGIN
1983
1984 EXECUTE IMMEDIATE sql_statement
1985 INTO retrieved_id;
1986
1987 RETURN retrieved_id;
1988
1989 EXCEPTION WHEN OTHERS THEN
1990 RAISE;
1991
1992 END retrieve_id;
1993
1994
1995 FUNCTION value_in_sql (sql_statement IN VARCHAR2, value IN VARCHAR2)
1996 RETURN BOOLEAN IS
1997
1998 indicator NUMBER;
1999 new_sql_statement VARCHAR2(10000);
2000
2001 BEGIN
2002
2003
2004 new_sql_statement := 'SELECT 1 FROM DUAL WHERE ' || '''' ||
2005 qa_core_pkg.dequote(value) || '''';
2006
2007 new_sql_statement := new_sql_statement || ' IN ' || '(' ||
2008 sql_statement || ')';
2009
2010
2011 EXECUTE IMMEDIATE new_sql_statement
2012 INTO indicator;
2013
2014 RETURN indicator = 1;
2015
2016 EXCEPTION WHEN OTHERS THEN
2017 RETURN FALSE;
2018
2019 END value_in_sql;
2020
2021
2022 FUNCTION validate_transaction_date(transaction_number IN NUMBER)
2023 RETURN BOOLEAN IS
2024
2025 result BOOLEAN DEFAULT TRUE;
2026 dummy NUMBER;
2027
2028 BEGIN
2029
2030
2031 IF (transaction_number = qa_ss_const.po_inspection_txn) THEN
2032 -- NEED FURTHER WORK: need to validate transaction date
2033 -- in this specific case
2034 result := FALSE;
2035 END IF;
2036
2037 RETURN result;
2038
2039 END validate_transaction_date;
2040
2041
2042 FUNCTION validate_uom(x_org_id IN NUMBER, x_item_id IN NUMBER,
2043 x_uom_code IN VARCHAR2) RETURN BOOLEAN IS
2044
2045 CURSOR c IS
2046 SELECT 1
2047 FROM mtl_item_uoms_view
2048 WHERE inventory_item_id = x_item_id AND
2049 organization_id = x_org_id AND
2050 uom_code = x_uom_code;
2051
2052 result BOOLEAN;
2053 dummy NUMBER;
2054
2055 BEGIN
2056
2057 OPEN c;
2058 FETCH c INTO dummy;
2059 result := c%FOUND;
2060 CLOSE c;
2061
2062 RETURN result;
2063
2064 END validate_uom;
2065
2066
2067 FUNCTION validate_revision (x_org_id IN NUMBER, x_item_id IN NUMBER,
2068 x_revision IN VARCHAR2)
2069 RETURN BOOLEAN IS
2070
2071 CURSOR c IS
2072 SELECT 1
2073 FROM mtl_item_revisions
2074 WHERE inventory_item_id = x_item_id AND
2075 organization_id = x_org_id AND
2076 revision = x_revision;
2077
2078 result BOOLEAN;
2079 dummy NUMBER;
2080
2081 BEGIN
2082
2083 OPEN c;
2084 FETCH c INTO dummy;
2085 result := c%FOUND;
2086 CLOSE c;
2087
2088 RETURN result;
2089
2090 END validate_revision;
2091
2092 FUNCTION validate_lot_num(x_org_id IN NUMBER, x_item_id IN NUMBER,
2093 x_lot_num IN VARCHAR2)
2094 RETURN BOOLEAN IS
2095
2096 CURSOR c IS
2097 SELECT 1
2098 FROM mtl_lot_numbers
2099 WHERE inventory_item_id = x_item_id AND
2100 organization_id = x_org_id AND
2101 lot_number = x_lot_num;
2102
2103 result BOOLEAN;
2104 dummy NUMBER;
2105
2106 BEGIN
2107
2108 OPEN c;
2109 FETCH c INTO dummy;
2110 result := c%FOUND;
2111 CLOSE c;
2112
2113 RETURN result;
2114
2115 END validate_lot_num;
2116
2117
2118 FUNCTION validate_serial_num(x_org_id IN NUMBER, x_item_id IN NUMBER,
2119 x_lot_num IN VARCHAR2, x_revision IN VARCHAR2, x_serial_num IN VARCHAR2)
2120 RETURN BOOLEAN IS
2121
2122
2123 -- Bug 3364660. Changed the cursor sql to use the nvl() for revision
2124 -- and lot number columns. kabalakr.
2125
2126 --
2127 -- Bug 3773298. Relaxing the where conditions such that if
2128 -- input lot number is null, we allow for all serial numbers
2129 -- for that item to pass validation. Same for revision.
2130 -- bso Tue Jul 20 15:20:37 PDT 2004
2131 --
2132 CURSOR c IS
2133 SELECT 1
2134 FROM mtl_serial_numbers
2135 WHERE inventory_item_id = x_item_id AND
2136 current_organization_id = x_org_id AND
2137 (x_lot_num IS NULL OR lot_number = x_lot_num) AND
2138 (x_revision IS NULL OR revision = x_revision) AND
2139 serial_number = x_serial_num;
2140
2141 result BOOLEAN;
2142 dummy NUMBER;
2143
2144 BEGIN
2145
2146 OPEN c;
2147 FETCH c INTO dummy;
2148 result := c%FOUND;
2149 CLOSE c;
2150
2151 RETURN result;
2152
2153 END validate_serial_num;
2154
2155
2156
2157 FUNCTION validate_subinventory (x_org_id IN NUMBER, x_subinventory IN VARCHAR2)
2158 RETURN BOOLEAN IS
2159
2160
2161 -- Bug 3381173. The Subinventory specified for Mobile LPN inspection could
2162 -- either be a storage or receiving sub. Hence changed the sql to accomodate
2163 -- both types of sub.
2164 -- kabalakr Tue Jan 27 02:18:59 PST 2004.
2165
2166 CURSOR c IS
2167 SELECT 1
2168 FROM mtl_secondary_inventories
2169 WHERE organization_id = x_org_id
2170 AND ((((SUBINVENTORY_TYPE <> 2) OR (SUBINVENTORY_TYPE IS NULL))
2171 AND nvl(disable_date, sysdate+1) > sysdate)
2172 OR (SUBINVENTORY_TYPE = 2))
2173 AND secondary_inventory_name = x_subinventory;
2174
2175 result BOOLEAN;
2176 dummy NUMBER;
2177
2178 BEGIN
2179
2180 OPEN c;
2181 FETCH c INTO dummy;
2182 result := c%FOUND;
2183 CLOSE c;
2184
2185 RETURN result;
2186
2187 END validate_subinventory;
2188
2189
2190 FUNCTION validate_lot_number (x_transaction_number IN NUMBER, x_transaction_id
2191 IN NUMBER, x_lot_number IN VARCHAR2)
2192 RETURN BOOLEAN IS
2193
2194 CURSOR c IS
2195 SELECT 1
2196 FROM mtl_transaction_lots_temp
2197 WHERE transaction_temp_id = x_transaction_id
2198 AND lot_number = x_lot_number;
2199
2200 result BOOLEAN;
2201 dummy NUMBER;
2202
2203 BEGIN
2204
2205 -- No validation done for direct data entry in which case
2206 -- the transaction number is going to be null
2207
2208 IF (x_transaction_number is NULL) THEN
2209 RETURN TRUE;
2210 END IF;
2211
2212 -- Only done for WIP Completion and Work Order Less Completions
2213
2214 IF (x_transaction_number NOT IN( qa_ss_const.wip_completion_txn,
2215 qa_ss_const.flow_work_order_less_txn)) THEN
2216 RETURN TRUE;
2217 END IF;
2218
2219 OPEN c;
2220 FETCH c INTO dummy;
2221 result := c%FOUND;
2222 CLOSE c;
2223
2224 RETURN result;
2225
2226 END validate_lot_number;
2227
2228
2229 FUNCTION validate_serial_number (x_transaction_number IN NUMBER,
2230 x_transaction_id IN NUMBER, x_lot_number IN VARCHAR2, x_serial_number IN
2231 VARCHAR2)
2232 RETURN BOOLEAN IS
2233
2234 --
2235 -- Bug 3758145. The original SQL is incorrect in transaction scenario.
2236 -- the WHERE conditions msn.line_mark_id should be rewritten as
2237 -- msn.lot_line_mark_id and vice versa.
2238 -- bso Tue Jul 20 15:52:21 PDT 2004
2239 --
2240 CURSOR c IS
2241 SELECT 1
2242 FROM mtl_serial_numbers msn,
2243 mtl_transaction_lots_temp mtlt
2244 WHERE msn.lot_line_mark_id = x_transaction_id
2245 AND mtlt.transaction_temp_id = msn.lot_line_mark_id
2246 AND mtlt.serial_transaction_temp_id = msn.line_mark_id
2247 AND mtlt.lot_number = x_lot_number
2248 AND x_lot_number IS NOT NULL
2249 AND msn.serial_number = x_serial_number
2250 UNION ALL
2251 SELECT 1
2252 FROM mtl_serial_numbers msn
2253 WHERE msn.line_mark_id = x_transaction_id
2254 AND x_lot_number IS NULL
2255 AND msn.serial_number = x_serial_number;
2256
2257 result BOOLEAN DEFAULT FALSE;
2258 dummy NUMBER;
2259
2260 BEGIN
2261
2262 -- Only done for WIP Completion and Work Order Less Completions
2263
2264 -- Bug 3364660. Return TRUE even if the x_transaction_number is NULL.
2265 -- kabalakr.
2266
2267 IF (x_transaction_number NOT IN( qa_ss_const.wip_completion_txn,
2268 qa_ss_const.flow_work_order_less_txn))
2269 OR (x_transaction_number IS NULL) THEN
2270
2271 RETURN TRUE;
2272 END IF;
2273
2274 OPEN c;
2275 FETCH c INTO dummy;
2276 result := c%FOUND;
2277 CLOSE c;
2278
2279 RETURN result;
2280
2281 END validate_serial_number;
2282
2283
2284 FUNCTION validate_op_seq_number (x_org_id IN NUMBER, x_line_id IN NUMBER,
2285 x_wip_entity_id IN NUMBER, x_op_seq_number IN VARCHAR2)
2286 RETURN BOOLEAN IS
2287
2288 CURSOR c1 IS
2289 SELECT 1
2290 FROM wip_operations_all_v
2291 WHERE organization_id = x_org_id
2292 AND wip_entity_id = x_wip_entity_id
2293 AND operation_seq_num = x_op_seq_number;
2294
2295 CURSOR c2 IS
2296 SELECT 1
2297 FROM wip_operations_all_v
2298 WHERE organization_id = x_org_id
2299 AND wip_entity_id = x_wip_entity_id
2300 AND operation_seq_num = x_op_seq_number
2301 AND repetitive_schedule_id =
2302 ( SELECT repetitive_schedule_id
2303 FROM wip_first_open_schedule_v
2304 WHERE organization_id = x_org_id
2305 AND wip_entity_id = x_wip_entity_id
2306 AND line_id = x_line_id );
2307
2308 result BOOLEAN DEFAULT FALSE;
2309 dummy NUMBER;
2310
2311 BEGIN
2312
2313 IF (x_line_id IS NULL) THEN
2314
2315 OPEN c1;
2316 FETCH c1 INTO dummy;
2317 result := c1%FOUND;
2318 CLOSE c1;
2319
2320 ELSE
2321
2322 OPEN c2;
2323 FETCH c2 INTO dummy;
2324 result := c2%FOUND;
2325 CLOSE c2;
2326
2327 END IF;
2328 RETURN result;
2329
2330 END validate_op_seq_number;
2331
2332 --
2333 -- See Bug 2588213
2334 -- To support the element Maintenance Op Seq Number
2335 -- to be used along with Maintenance Workorder
2336 -- rkunchal Mon Sep 23 23:46:28 PDT 2002
2337 --
2338 FUNCTION validate_maintenance_op_seq (x_org_id IN NUMBER,
2339 x_maintenance_work_order_id IN NUMBER,
2340 x_maintenance_op_seq IN VARCHAR2)
2341 RETURN BOOLEAN IS
2342
2343 CURSOR c IS
2344 SELECT 1
2345 FROM wip_operations_all_v
2346 WHERE organization_id = x_org_id
2347 AND wip_entity_id = x_maintenance_work_order_id
2348 AND operation_seq_num = x_maintenance_op_seq;
2349
2350 result BOOLEAN DEFAULT FALSE;
2351 dummy NUMBER;
2352
2353 BEGIN
2354
2355 OPEN c;
2356 FETCH c INTO dummy;
2357 result := c%FOUND;
2358 CLOSE c;
2359
2360 RETURN result;
2361
2362 END validate_maintenance_op_seq;
2363
2364 --
2365 -- End of inclusions for Bug 2588213
2366 --
2367
2368 FUNCTION validate_po_line_number (x_po_header_id IN NUMBER, x_po_line_number
2369 IN VARCHAR2)
2370 RETURN BOOLEAN IS
2371
2372 CURSOR c IS
2373 SELECT 1
2374 FROM po_lines_val_v
2375 WHERE po_header_id = x_po_header_id
2376 AND line_num = x_po_line_number;
2377
2378 result BOOLEAN;
2379 dummy NUMBER;
2380
2381 BEGIN
2382
2383 OPEN c;
2384 FETCH c INTO dummy;
2385 result := c%FOUND;
2386 CLOSE c;
2387
2388 RETURN result;
2389
2390 END validate_po_line_number;
2391
2392
2393 FUNCTION validate_po_shipments (x_po_line_num IN NUMBER, x_po_header_id IN
2394 NUMBER, x_po_shipments IN VARCHAR2)
2395 RETURN BOOLEAN IS
2396
2397 -- Bug 4958763. SQL Repository Fix SQL ID: 15008958
2398 CURSOR c IS
2399 SELECT 1
2400 FROM po_line_locations
2401 WHERE po_line_id =
2402 ( SELECT po_line_id
2403 FROM po_lines
2404 WHERE line_num = x_po_line_num
2405 AND po_header_id = x_po_header_id)
2406 AND shipment_num = x_po_shipments;
2407 /*
2408 SELECT 1
2409 FROM po_shipments_all_v
2410 WHERE po_line_id =
2411 (SELECT po_line_id
2412 FROM po_lines_val_v
2413 WHERE line_num = x_po_line_num
2414 AND po_header_id = x_po_header_id)
2415 AND shipment_num = x_po_shipments;
2416 */
2417
2418 result BOOLEAN;
2419 dummy NUMBER;
2420
2421 BEGIN
2422
2423 OPEN c;
2424 FETCH c INTO dummy;
2425 result := c%FOUND;
2426 CLOSE c;
2427
2428 RETURN result;
2429
2430 END validate_po_shipments;
2431
2432
2433 FUNCTION validate_receipt_number (x_receipt_number IN VARCHAR2)
2434 RETURN BOOLEAN IS
2435
2436 /* -- Bug 4958763. SQL Repository Fix SQL ID: 15008972
2437 CURSOR c IS
2438 SELECT 1
2439 FROM RCV_SHIPMENT_HEADERS
2440 WHERE receipt_num = x_receipt_number
2441 AND RECEIPT_SOURCE_CODE = 'VENDOR';
2442 */
2443 --
2444 -- Bug 7491455.FP For bug 6800960.
2445 -- changed the query for validationg receipt number to include RMA receipts
2446 -- pdube Fri Oct 17 00:14:28 PDT 2008
2447 CURSOR c IS
2448 SELECT 1
2449 FROM RCV_SHIPMENT_HEADERS RCVSH,
2450 PO_VENDORS POV,
2451 RCV_TRANSACTIONS RT
2452 WHERE RCVSH.RECEIPT_SOURCE_CODE in ('VENDOR','CUSTOMER') AND
2453 RCVSH.VENDOR_ID = POV.VENDOR_ID(+) AND
2454 RT.SHIPMENT_HEADER_ID = RCVSH.SHIPMENT_HEADER_ID AND
2455 receipt_num = x_receipt_number;
2456 /*
2457 SELECT 1
2458 FROM rcv_receipts_all_v
2459 WHERE receipt_num = x_receipt_number;
2460 */
2461
2462 result BOOLEAN;
2463 dummy NUMBER;
2464
2465 BEGIN
2466
2467 OPEN c;
2468 FETCH c INTO dummy;
2469 result := c%FOUND;
2470 CLOSE c;
2471
2472 RETURN result;
2473
2474 END validate_receipt_number;
2475
2476
2477 FUNCTION get_target_element (plan_char_action_id IN NUMBER)
2478 RETURN NUMBER IS
2479
2480 target_element NUMBER;
2481
2482 CURSOR c1 (pca_id NUMBER) IS
2483 SELECT assigned_char_id
2484 FROM qa_plan_char_actions
2485 WHERE plan_char_action_id = pca_id;
2486
2487 BEGIN
2488
2489 OPEN c1(plan_char_action_id);
2490 FETCH c1 INTO target_element;
2491 CLOSE c1;
2492
2493 RETURN target_element;
2494
2495 EXCEPTION WHEN OTHERS THEN
2496 RAISE;
2497
2498 END get_target_element;
2499
2500
2501 FUNCTION get_enabled_flag (plan_id IN NUMBER, element_id IN NUMBER)
2502 RETURN NUMBER IS
2503 BEGIN
2504 RETURN qpc_enabled_flag(plan_id, element_id);
2505 END get_enabled_flag;
2506
2507 --
2508 -- See Bug 2624112
2509 -- The decimal precision for a number type collection
2510 -- element is to be configured at plan level.
2511 -- rkunchal Wed Oct 16 05:32:33 PDT 2002
2512 --
2513 -- New function to get the decimal precision for the element
2514 -- from the QA_PLAN_CHARS table.
2515 --
2516
2517 FUNCTION decimal_precision (p_plan_id IN NUMBER, p_element_id IN NUMBER)
2518 RETURN NUMBER IS
2519 BEGIN
2520 RETURN qpc_decimal_precision(p_plan_id, p_element_id);
2521 END decimal_precision;
2522
2523 FUNCTION get_mandatory_flag (plan_id IN NUMBER, element_id IN NUMBER)
2524 RETURN NUMBER IS
2525 BEGIN
2526 RETURN qpc_mandatory_flag(plan_id, element_id);
2527 END get_mandatory_flag;
2528
2529
2530 FUNCTION get_sql_validation_string (element_id IN NUMBER)
2531 RETURN VARCHAR2 IS
2532 BEGIN
2533 RETURN qa_chars_api.sql_validation_string(element_id);
2534 END get_sql_validation_string;
2535
2536
2537 FUNCTION get_result_column_name (plan_id IN NUMBER, element_id IN NUMBER)
2538 RETURN VARCHAR2 IS
2539
2540 --
2541 -- This is a function that returns the unique column name in the table
2542 -- qa_results given an element_id, plan_id combination.
2543 --
2544
2545 name VARCHAR2(30);
2546
2547 BEGIN
2548
2549 name := qa_chars_api.hardcoded_column(element_id);
2550
2551 IF (name IS NULL) THEN
2552 name := qpc_result_column_name(plan_id, element_id);
2553 END IF;
2554
2555 RETURN name;
2556
2557 END get_result_column_name;
2558
2559
2560 FUNCTION sql_string_exists(x_plan_id IN NUMBER, x_char_id IN NUMBER,
2561 org_id IN NUMBER, user_id IN NUMBER, value IN VARCHAR2,
2562 x_ref OUT NOCOPY LovRefCursor)
2563 RETURN BOOLEAN IS
2564
2565 sql_string VARCHAR2(3000);
2566 wild VARCHAR2(250);
2567
2568 BEGIN
2569
2570 -- Before Single Scan LOV
2571 -- wild := value || '%';
2572
2573 -- After Single Scan LOV
2574 wild := value;
2575
2576 IF values_exist(x_plan_id, x_char_id) THEN
2577 sql_string := 'SELECT short_code, description
2578 FROM qa_plan_char_value_lookups
2579 WHERE plan_id = :1
2580 AND char_id = :2
2581 AND short_code LIKE :3
2582 ORDER BY short_code';
2583 OPEN x_ref FOR sql_string USING x_plan_id, x_char_id, wild;
2584 RETURN TRUE;
2585
2586 ELSIF sql_validation_exists(x_char_id) THEN
2587
2588 sql_string := get_sql_validation_string(x_char_id);
2589 sql_string := qa_chars_api.format_sql_for_lov(sql_string,
2590 org_id, user_id);
2591
2592 --
2593 -- Bug 1474995. Adding filter to the user-defined SQL.
2594 --
2595 sql_string :=
2596 'select *
2597 from
2598 (select ''x'' code, ''x'' description
2599 from dual
2600 where 1 = 2
2601 union
2602 select * from
2603 ( '|| sql_string ||
2604 ' )) where code like :1';
2605
2606 OPEN x_ref FOR sql_string USING wild;
2607 RETURN TRUE;
2608
2609 ELSE
2610 RETURN FALSE;
2611 END IF;
2612
2613 END sql_string_exists;
2614
2615
2616 PROCEDURE get_department_lov(org_id IN NUMBER, value IN VARCHAR2,
2617 x_ref OUT NOCOPY LovRefCursor) IS
2618
2619 wild VARCHAR2(160);
2620 sql_string VARCHAR2(1500);
2621
2622 BEGIN
2623
2624 /* Before Single Scan LOV
2625 IF value IS NULL THEN
2626 wild := '%';
2627 ELSE
2628 wild := value || '%';
2629 END IF; */
2630
2631 -- After Single Scan LOV
2632 wild := value;
2633
2634 sql_string := 'SELECT department_code, description
2635 FROM bom_departments_val_v
2636 WHERE department_code like :1 AND
2637 organization_id = :2
2638 ORDER BY department_code';
2639 OPEN x_ref FOR sql_string USING wild, org_id;
2640
2641 END get_department_lov;
2642
2643
2644 PROCEDURE get_job_lov(org_id IN NUMBER, value IN VARCHAR2,
2645 x_ref OUT NOCOPY LovRefCursor) IS
2646
2647 wild VARCHAR2(160);
2648 sql_string VARCHAR2(1500);
2649
2650 BEGIN
2651
2652 /* Before Single Scan LOV
2653 IF value IS NULL THEN
2654 wild := '%';
2655 ELSE
2656 wild := value || '%';
2657 END IF; */
2658
2659 -- After Single Scan LOV
2660 wild := value;
2661
2662
2663 -- #2382432
2664 -- Changed the view to WIP_DISCRETE_JOBS_ALL_V instead of
2665 -- earlier wip_open_discrete_jobs_val_v
2666 -- rkunchal Sun Jun 30 22:59:11 PDT 2002
2667
2668 sql_string := 'SELECT wip_entity_name, description
2669 FROM wip_discrete_jobs_all_v
2670 WHERE wip_entity_name like :1 AND
2671 organization_id = :2
2672 ORDER BY wip_entity_name';
2673 OPEN x_ref FOR sql_string USING wild, org_id;
2674 END get_job_lov;
2675
2676
2677 PROCEDURE get_work_order_lov(org_id IN NUMBER, value IN VARCHAR2,
2678 x_ref OUT NOCOPY LovRefCursor) IS
2679
2680 wild VARCHAR2(160);
2681 sql_string VARCHAR2(1500);
2682
2683 BEGIN
2684
2685 /* Before Single Scan LOV
2686 IF value IS NULL THEN
2687 wild := '%';
2688 ELSE
2689 wild := value || '%';
2690 END IF; */
2691
2692 -- After Single Scan LOV
2693 wild := value;
2694
2695 /* rkaza 10/21/2002. Bug 2635736 */
2696 sql_string := 'select WE.wip_entity_name, WDJ.description
2697 from wip_entities WE, wip_discrete_jobs WDJ
2698 where WDJ.organization_id = :1 and
2699 WDJ.status_type in (3,4) and
2700 WDJ.wip_entity_id = WE.wip_entity_id and
2701 WE.entity_type IN (6, 7) and
2702 WE.wip_entity_name like :2
2703 order by WE.wip_entity_name';
2704
2705 OPEN x_ref FOR sql_string USING org_id, wild;
2706
2707 END get_work_order_lov;
2708
2709
2710
2711 PROCEDURE get_production_lov (org_id IN NUMBER, value IN VARCHAR2,
2712 x_ref OUT NOCOPY LovRefCursor) IS
2713
2714 wild VARCHAR2(160);
2715 sql_string VARCHAR2(1500);
2716
2717 BEGIN
2718
2719 /* Before Single Scan LOV
2720 IF value IS NULL THEN
2721 wild := '%';
2722 ELSE
2723 wild := value || '%';
2724 END IF; */
2725
2726 -- After Single Scan LOV
2727 wild := value;
2728
2729 sql_string := 'SELECT line_code, description
2730 FROM wip_lines_val_v
2731 WHERE line_code like :1 AND
2732 organization_id = :2
2733 ORDER BY line_code';
2734 OPEN x_ref FOR sql_string USING wild, org_id;
2735
2736 END get_production_lov;
2737
2738
2739 PROCEDURE get_resource_code_lov (org_id IN NUMBER, value IN VARCHAR2,
2740 x_ref OUT NOCOPY LovRefCursor) IS
2741
2742 wild VARCHAR2(160);
2743 sql_string VARCHAR2(1500);
2744
2745 BEGIN
2746
2747 /* Before Single Scan LOV
2748 IF value IS NULL THEN
2749 wild := '%';
2750 ELSE
2751 wild := value || '%';
2752 END IF; */
2753
2754 -- After Single Scan LOV
2755 wild := value;
2756
2757
2758 sql_string := 'SELECT resource_code, description
2759 FROM bom_resources_val_v
2760 WHERE resource_code like :1
2761 AND organization_id = :2
2762 ORDER BY resource_code';
2763
2764 OPEN x_ref FOR sql_string USING wild, org_id;
2765
2766 END get_resource_code_lov;
2767
2768
2769 PROCEDURE get_supplier_lov (value IN VARCHAR2, x_ref OUT NOCOPY LovRefCursor) IS
2770
2771 wild VARCHAR2(240);
2772 sql_string VARCHAR2(1500);
2773
2774 BEGIN
2775
2776 /* Before Single Scan LOV
2777 IF value IS NULL THEN
2778 wild := '%';
2779 ELSE
2780 wild := value || '%';
2781 END IF; */
2782
2783 -- After Single Scan LOV
2784 wild := value;
2785
2786 sql_string := 'SELECT vendor_name, segment1
2787 FROM po_vendors
2788 WHERE vendor_name like :1
2789 AND nvl(end_date_active, sysdate + 1) > sysdate
2790 ORDER BY vendor_name';
2791
2792 OPEN x_ref FOR sql_string USING wild;
2793
2794 END get_supplier_lov;
2795
2796
2797 PROCEDURE get_po_number_lov (value IN VARCHAR2, x_ref OUT NOCOPY LovRefCursor) IS
2798
2799 wild VARCHAR2(160);
2800 sql_string VARCHAR2(1500);
2801
2802 BEGIN
2803
2804 /* Before Single Scan LOV
2805 IF value IS NULL THEN
2806 wild := '%';
2807 ELSE
2808 wild := value || '%';
2809 END IF; */
2810
2811 -- After Single Scan LOV
2812 wild := value;
2813
2814 -- R12 Project MOAC 4637896
2815 -- Now select operating unit as an additional column.
2816 -- bso Sat Oct 8 12:21:06 PDT 2005
2817
2818 sql_string := 'SELECT po_header_id, segment1, vendor_name ||
2819 '' ('' || operating_unit || '')''
2820 FROM qa_po_numbers_lov_v
2821 WHERE segment1 like :1
2822 ORDER BY segment1';
2823
2824 OPEN x_ref FOR sql_string USING wild;
2825
2826 END get_po_number_lov;
2827
2828
2829 --
2830 -- Reference bug 2286796
2831 -- The lov query should be the same with the element Customer's
2832 -- sql_validation_string. customer_name is the column that should show
2833 -- first and populate to collection element Customer.
2834 -- Reversed the column order and order by customer_name instead of number
2835 -- jezheng
2836 -- Wed Apr 17 14:57:27 PDT 2002
2837 --
2838 PROCEDURE get_customer_lov (value IN VARCHAR2, x_ref OUT NOCOPY LovRefCursor) IS
2839
2840 wild VARCHAR2(160);
2841 sql_string VARCHAR2(1500);
2842
2843 BEGIN
2844
2845 /* Before Single Scan LOV
2846 IF value IS NULL THEN
2847 wild := '%';
2848 ELSE
2849 wild := value || '%';
2850 END IF; */
2851
2852 -- After Single Scan LOV
2853 wild := value;
2854
2855 sql_string := 'SELECT customer_name, customer_number
2856 FROM qa_customers_lov_v
2857 WHERE customer_name like :1
2858 AND status = ''A''
2859 AND nvl(customer_prospect_code, ''CUSTOMER'') =
2860 ''CUSTOMER''
2861 ORDER BY customer_name';
2862
2863 OPEN x_ref FOR sql_string USING wild;
2864
2865 END get_customer_lov;
2866
2867
2868 PROCEDURE get_so_number_lov (value IN VARCHAR2, x_ref OUT NOCOPY LovRefCursor) IS
2869
2870 wild VARCHAR2(160);
2871 sql_string VARCHAR2(1500);
2872
2873 BEGIN
2874
2875 /* Before Single Scan LOV
2876 IF value IS NULL THEN
2877 wild := '%';
2878 ELSE
2879 wild := value || '%';
2880 END IF; */
2881
2882 -- After Single Scan LOV
2883 wild := value;
2884
2885 -- Bug 4958763. Fixing it along with SQL repository fixes
2886 -- reverting back the changes for functionality
2887 sql_string := 'SELECT order_number, order_type name
2888 FROM qa_sales_orders_lov_v
2889 WHERE order_number like :1
2890 ORDER BY order_number';
2891
2892 OPEN x_ref FOR sql_string USING wild;
2893
2894 END get_so_number_lov;
2895
2896
2897 -- Bug 4958763. SQL Repository Fix SQL ID: 15009074
2898 -- "so_lines" is obsoleted in 11i. Replacing it with oe_order_lines
2899 -- also SO Line Number is not having LOV in Forms and QWB
2900 -- and so removing the lov in Mobile as well
2901 -- even after commenting out it does not require any change in Mobile code
2902 -- saugupta Wed, 08 Feb 2006 03:00:30 -0800 PDT
2903
2904 PROCEDURE get_so_line_number_lov (value IN VARCHAR2, x_ref OUT NOCOPY LovRefCursor) IS
2905
2906 wild VARCHAR2(160);
2907 sql_string VARCHAR2(1500);
2908
2909 BEGIN
2910 x_ref := NULL;
2911 /* Before Single Scan LOV
2912 IF value IS NULL THEN
2913 wild := '%';
2914 ELSE
2915 wild := value || '%';
2916 END IF;
2917
2918 -- After Single Scan LOV
2919 wild := value;
2920
2921 sql_string := 'SELECT to_char(sl.line_number), msik.concatenated_segments
2922 FROM mtl_system_items_kfv msik, so_lines sl
2923 WHERE sl.inventory_item_id = msik.inventory_item_id
2924 AND header_id like :1';
2925
2926 OPEN x_ref FOR sql_string USING wild;
2927 */
2928 END get_so_line_number_lov;
2929
2930
2931 PROCEDURE get_po_release_number_lov (p_po_header_id IN NUMBER, value IN VARCHAR2, x_ref OUT
2932 NOCOPY LovRefCursor) IS
2933
2934 wild VARCHAR2(160);
2935 sql_string VARCHAR2(1500);
2936
2937 BEGIN
2938
2939 /* Before Single Scan LOV
2940 IF value IS NULL THEN
2941 wild := '%';
2942 ELSE
2943 wild := value || '%';
2944 END IF; */
2945
2946 -- After Single Scan LOV
2947 wild := value;
2948 -- Bug 4958763. SQL Repository Fix SQL ID: 15009081
2949 /*
2950 sql_string := 'SELECT release_num, release_date
2951 FROM po_releases pr
2952 WHERE pr.release_num like :1
2953 ORDER BY pr.release_num';
2954 */
2955
2956 sql_string := 'SELECT release_num, release_date
2957 FROM po_releases
2958 WHERE po_header_id = :1
2959 AND release_num like :2
2960 ORDER BY release_num';
2961
2962 OPEN x_ref FOR sql_string USING p_po_header_id, wild;
2963
2964 END get_po_release_number_lov;
2965
2966
2967 PROCEDURE get_project_number_lov (value IN VARCHAR2, x_ref OUT
2968 NOCOPY LovRefCursor) IS
2969
2970 wild VARCHAR2(160);
2971 sql_string VARCHAR2(1500);
2972
2973 BEGIN
2974 /*
2975 mtl_project_v changed to pjm_projects_all_v (selects from both pjm enabled and
2976 non-pjm enabled orgs).
2977 rkaza, 11/10/2001.
2978 */
2979 /* Before Single Scan LOV
2980 IF value IS NULL THEN
2981 wild := '%';
2982 ELSE
2983 wild := value || '%';
2984 END IF; */
2985
2986 -- After Single Scan LOV
2987 wild := value;
2988
2989 --
2990 -- Bug 5249078. Changed pjm_projects_all_v to
2991 -- pjm_projects_v for MOAC compliance.
2992 -- bso Thu Jun 1 10:46:50 PDT 2006
2993 --
2994 sql_string := 'SELECT project_number, project_name
2995 FROM pjm_projects_v
2996 WHERE project_number like :1
2997 ORDER BY project_number';
2998
2999 OPEN x_ref FOR sql_string USING wild;
3000
3001 END get_project_number_lov;
3002
3003
3004 PROCEDURE get_task_number_lov (value IN VARCHAR2, x_ref OUT
3005 NOCOPY LovRefCursor) IS
3006
3007 wild VARCHAR2(160);
3008 sql_string VARCHAR2(1500);
3009
3010 BEGIN
3011
3012 /* Before Single Scan LOV
3013 IF value IS NULL THEN
3014 wild := '%';
3015 ELSE
3016 wild := value || '%';
3017 END IF; */
3018
3019 -- After Single Scan LOV
3020 wild := value;
3021
3022 sql_string := 'SELECT task_number, task_name
3023 FROM mtl_task_v
3024 WHERE task_number like :1
3025 ORDER BY task_number';
3026
3027 OPEN x_ref FOR sql_string USING wild;
3028
3029 END get_task_number_lov;
3030
3031
3032 PROCEDURE get_rma_number_lov (value IN VARCHAR2, x_ref OUT
3033 NOCOPY LovRefCursor) IS
3034
3035 wild VARCHAR2(160);
3036 sql_string VARCHAR2(1500);
3037
3038 BEGIN
3039
3040 /* Before Single Scan LOV
3041 IF value IS NULL THEN
3042 wild := '%';
3043 ELSE
3044 wild := value || '%';
3045 END IF; */
3046
3047 -- After Single Scan LOV
3048 wild := value;
3049
3050 sql_string := 'SELECT sh.order_number, sot.name
3051 FROM so_order_types sot,
3052 oe_order_headers sh,
3053 qa_customers_lov_v rc
3054 WHERE sh.order_type_id = sot.order_type_id and
3055 sh.sold_to_org_id = rc.customer_id and
3056 sh.order_category_code in (''RETURN'', ''MIXED'') and
3057 sh.order_number like :1
3058 ORDER BY sh.order_number';
3059
3060 OPEN x_ref FOR sql_string USING wild;
3061
3062 END get_rma_number_lov;
3063
3064 --
3065 -- Bug 6161802
3066 -- Added procedure to return lov sql for rma line number
3067 -- with rma number as a bind variable
3068 -- skolluku Tue Jul 17 23:47:13 PDT 2007
3069 --
3070 PROCEDURE get_rma_line_num_lov (x_rma_number IN VARCHAR2,
3071 value IN VARCHAR2, x_ref OUT NOCOPY LovRefCursor) IS
3072
3073 wild VARCHAR2(160);
3074 sql_string VARCHAR2(1500);
3075
3076 BEGIN
3077
3078 -- After Single Scan LOV
3079 wild := value;
3080
3081 sql_string := 'select distinct to_char(oel.line_number),
3082 ''RMA Number: '' || sh.order_number || '';'' || ''Item: '' || oel.ordered_item description
3083 from oe_order_lines oel, so_order_types sot, oe_order_headers sh
3084 where sh.order_type_id = sot.order_type_id and oel.header_id = sh.header_id and
3085 oel.line_category_code in (''RETURN'', ''MIXED'') and
3086 sh.order_number = :1 and
3087 to_char(oel.line_number) like :2
3088 order by description, line_number';
3089
3090 OPEN x_ref FOR sql_string USING x_rma_number, wild;
3091
3092 END get_rma_line_num_lov;
3093
3094 PROCEDURE get_LPN_lov (value IN VARCHAR2, x_ref OUT
3095 NOCOPY LovRefCursor) IS
3096
3097 wild VARCHAR2(160);
3098 sql_string VARCHAR2(1500);
3099
3100 BEGIN
3101
3102 /* Before Single Scan LOV
3103 IF value IS NULL THEN
3104 wild := '%';
3105 ELSE
3106 wild := value || '%';
3107 END IF; */
3108
3109 -- After Single Scan LOV
3110 wild := value;
3111
3112 sql_string := 'SELECT license_plate_number, attribute1
3113 FROM wms_license_plate_numbers
3114 WHERE license_plate_number like :1
3115 ORDER BY license_plate_number';
3116
3117 OPEN x_ref FOR sql_string USING wild;
3118
3119 END get_LPN_lov;
3120
3121 -- added the following to include new hardcoded element Transfer license plate number
3122 -- saugupta Aug 2003
3123
3124 PROCEDURE get_XFR_LPN_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 license_plate_number, attribute1
3143 FROM wms_license_plate_numbers
3144 WHERE license_plate_number like :1
3145 ORDER BY license_plate_number';
3146
3147 OPEN x_ref FOR sql_string USING wild;
3148
3149 END get_XFR_LPN_lov;
3150
3151 PROCEDURE get_contract_lov (value IN VARCHAR2, x_ref OUT
3152 NOCOPY LovRefCursor) IS
3153
3154 filter VARCHAR2(160);
3155 sql_string VARCHAR2(1500);
3156
3157 BEGIN
3158
3159 /* Before Single Scan LOV
3160 filter := value || '%'; */
3161
3162 -- After Single Scan LOV
3163 filter := value;
3164
3165 sql_string := 'SELECT k_number, short_description
3166 FROM oke_k_headers_lov_v
3167 WHERE k_number like :1
3168 ORDER BY k_number';
3169
3170 OPEN x_ref FOR sql_string USING filter;
3171
3172 END get_contract_lov;
3173
3174 PROCEDURE get_contract_line_lov (value IN VARCHAR2, contract_number IN VARCHAR2,
3175 x_ref OUT NOCOPY LovRefCursor) IS
3176
3177 filter VARCHAR2(160);
3178 sql_string VARCHAR2(1500);
3179 contract_id NUMBER ;
3180
3181 BEGIN
3182
3183 /* Before Single Scan LOV
3184 filter := value || '%'; */
3185
3186 -- After Single Scan LOV
3187 filter := value;
3188
3189 contract_id := get_contract_id (contract_number);
3190 sql_string := 'SELECT line_number, line_description
3191 FROM oke_k_lines_full_v
3192 WHERE header_id = :1 AND
3193 line_number like :2
3194 ORDER BY line_number';
3195
3196 OPEN x_ref FOR sql_string USING contract_id, filter;
3197
3198 END get_contract_line_lov;
3199
3200 PROCEDURE get_deliverable_lov (value IN VARCHAR2, contract_number IN VARCHAR2,
3201 line_number IN VARCHAR2, x_ref OUT NOCOPY LovRefCursor) IS
3202
3203 filter VARCHAR2(160);
3204 sql_string VARCHAR2(1500);
3205 contract_id NUMBER;
3206 line_id NUMBER;
3207
3208 BEGIN
3209
3210 /* Before Single Scan LOV
3211 filter := value || '%'; */
3212
3213 -- After Single Scan LOV
3214 filter := value;
3215
3216 contract_id := get_contract_id (contract_number);
3217 line_id := get_contract_line_id (line_number);
3218
3219 sql_string := 'SELECT deliverable_num, description
3220 FROM oke_k_deliverables_vl
3221 WHERE k_header_id = :1 AND
3222 k_line_id = :2 AND
3223 deliverable_num like :3
3224 ORDER BY deliverable_num';
3225
3226 OPEN x_ref FOR sql_string USING contract_id, line_id, filter;
3227
3228 END get_deliverable_lov;
3229
3230
3231
3232 PROCEDURE get_uom_lov (x_org_id IN NUMBER, x_item_name IN VARCHAR2,
3233 value IN VARCHAR2, x_ref OUT NOCOPY LovRefCursor) IS
3234
3235 wild VARCHAR2(160);
3236 sql_string VARCHAR2(1500);
3237 x_item_id NUMBER DEFAULT NULL;
3238
3239 BEGIN
3240
3241 -- This procedure is used for both uom and component uom
3242
3243 /* Before Single Scan LOV
3244 IF value IS NULL THEN
3245 wild := '%';
3246 ELSE
3247 wild := value || '%';
3248 END IF; */
3249
3250 -- After Single Scan LOV
3251 wild := value;
3252
3253 x_item_id := qa_flex_util.get_item_id(x_org_id, x_item_name);
3254
3255 sql_string := 'SELECT uom_code, description
3256 FROM mtl_item_uoms_view
3257 WHERE inventory_item_id = :1
3258 AND organization_id = :2
3259 AND uom_code like :3
3260 ORDER BY uom_code';
3261
3262 OPEN x_ref FOR sql_string USING x_item_id, x_org_id, wild;
3263
3264 END get_uom_lov;
3265
3266
3267 PROCEDURE get_revision_lov (x_org_id IN NUMBER, x_item_name IN VARCHAR2,
3268 value IN VARCHAR2, x_ref OUT NOCOPY LovRefCursor) IS
3269
3270 wild VARCHAR2(160);
3271 sql_string VARCHAR2(1500);
3272 x_item_id NUMBER DEFAULT NULL;
3273
3274 BEGIN
3275
3276 -- This procedure is used for both revision and component revision
3277
3278 /* Before Single Scan LOV
3279 IF value IS NULL THEN
3280 wild := '%';
3281 ELSE
3282 wild := value || '%';
3283 END IF; */
3284
3285 -- After Single Scan LOV
3286 wild := value;
3287
3288 x_item_id := qa_flex_util.get_item_id(x_org_id, x_item_name);
3289
3290 -- Bug 3595553. Modified the below query to join with msi to fetch
3291 -- revisions only if the item is revision controlled. All the items
3292 -- will have a base revision by default. But we want this sql to
3293 -- fetch revision only if the item is revision controlled. kabalakr.
3294
3295 sql_string := 'SELECT mir.revision, mir.effectivity_date
3296 FROM mtl_item_revisions mir, mtl_system_items msi
3297 WHERE mir.inventory_item_id = :1
3298 AND mir.organization_id = :2
3299 AND mir.revision like :3
3300 AND mir.inventory_item_id = msi.inventory_item_id
3301 AND mir.organization_id = msi.organization_id
3302 AND msi.revision_qty_control_code = 2
3303 ORDER BY revision';
3304
3305 OPEN x_ref FOR sql_string USING x_item_id, x_org_id, wild;
3306
3307 END get_revision_lov;
3308
3309 PROCEDURE get_lot_num_lov (x_org_id IN NUMBER, x_item_name IN VARCHAR2,
3310 value IN VARCHAR2, x_ref OUT NOCOPY LovRefCursor) IS
3311
3312 wild VARCHAR2(160);
3313 sql_string VARCHAR2(1500);
3314 x_item_id NUMBER DEFAULT NULL;
3315
3316 BEGIN
3317
3318 /* Before Single Scan LOV
3319 IF value IS NULL THEN
3320 wild := '%';
3321 ELSE
3322 wild := value || '%';
3323 END IF; */
3324
3325 -- After Single Scan LOV
3326 wild := value;
3327
3328 x_item_id := qa_flex_util.get_item_id(x_org_id, x_item_name);
3329
3330 sql_string := 'select lot_number, description
3331 from mtl_lot_numbers
3332 where inventory_item_id = :1
3333 and organization_id = :2
3334 and lot_number like :3
3335 and (disable_flag = 2 or disable_flag is null)
3336 ORDER BY lot_number';
3337
3338 OPEN x_ref FOR sql_string USING x_item_id, x_org_id, wild;
3339
3340 END get_lot_num_lov;
3341
3342 PROCEDURE get_serial_num_lov (x_org_id IN NUMBER, x_item_name IN VARCHAR2,
3343 x_lot_number IN VARCHAR2,
3344 x_revision IN VARCHAR2,
3345 value IN VARCHAR2, x_ref OUT NOCOPY LovRefCursor) IS
3346
3347 wild VARCHAR2(160);
3348 sql_string VARCHAR2(1500);
3349 x_item_id NUMBER DEFAULT NULL;
3350
3351 BEGIN
3352
3353 wild := value;
3354
3355 x_item_id := qa_flex_util.get_item_id(x_org_id, x_item_name);
3356
3357 -- Undoing Bug 3364660. Not needed after sync up with Bug 3773298
3358 -- l_trans_string := fnd_message.get_string('QA','QA_MOBILE_SERIAL_LOV_TXT');
3359 --
3360 -- Bug 3773298
3361 -- Sync up the SQL to be the same with forms.
3362 -- bso Tue Jul 20 16:12:06 PDT 2004
3363 --
3364 sql_string := 'select serial_number, current_status_name
3365 from mtl_serial_numbers_all_v
3366 where current_organization_id = :1
3367 and inventory_item_id = :2
3368 and (:3 is null OR lot_number = :4)
3369 and (:5 is null OR revision = :6)
3370 and serial_number like :7
3371 order by 1';
3372
3373 OPEN x_ref FOR sql_string USING x_org_id , x_item_id, x_lot_number, x_lot_number, x_revision, x_revision, wild;
3374
3375 END get_serial_num_lov;
3376
3377 --dgupta: Start R12 EAM Integration. Bug 4345492
3378 PROCEDURE get_asset_instance_number_lov (p_org_id IN NUMBER, p_asset_group IN VARCHAR2,
3379 p_asset_number IN VARCHAR2, value IN VARCHAR2, x_ref OUT NOCOPY LovRefCursor) IS
3380
3381 wild VARCHAR2(160);
3382 sql_string VARCHAR2(1500);
3383 l_asset_group_id NUMBER DEFAULT NULL;
3384
3385 BEGIN
3386 -- After Single Scan LOV
3387 wild := value;
3388
3389 l_asset_group_id := get_asset_group_id(p_org_id, p_asset_group);
3390
3391 sql_string := 'SELECT
3392 cii.instance_number, cii.instance_description
3393 FROM
3394 csi_item_instances cii, mtl_system_items_b msib, mtl_parameters mp
3395 WHERE
3396 msib.organization_id = mp.organization_id and
3397 msib.organization_id = cii.last_vld_organization_id and
3398 msib.inventory_item_id = cii.inventory_item_id and
3399 msib.eam_item_type in (1,3) and
3400 msib.serial_number_control_code <> 1 and
3401 sysdate between nvl(cii.active_start_date, sysdate-1)
3402 and nvl(cii.active_end_date, sysdate+1) and
3403 mp.maint_organization_id = :1 and
3404 cii.inventory_item_id = nvl(:2, cii.inventory_item_id) and
3405 cii.instance_number like :3 and
3406 cii.serial_number = nvl(:4, cii. serial_number)
3407 order by cii.instance_number';
3408
3409 OPEN x_ref FOR sql_string USING p_org_id , l_asset_group_id, wild, p_asset_number;
3410
3411 END get_asset_instance_number_lov;
3412 --dgupta: End R12 EAM Integration. Bug 4345492
3413
3414
3415 PROCEDURE get_asset_number_lov (x_org_id IN NUMBER, x_asset_group IN VARCHAR2,
3416 x_asset_instance_number IN VARCHAR2, --R12 EAM Integration. Bug 4345492
3417 value IN VARCHAR2, x_ref OUT NOCOPY LovRefCursor) IS
3418
3419 wild VARCHAR2(160);
3420 sql_string VARCHAR2(1500);
3421 x_asset_group_id NUMBER DEFAULT NULL;
3422 x_asset_instance_id NUMBER DEFAULT NULL; --R12 EAM Integration. Bug 4345492
3423
3424 BEGIN
3425
3426 /* Before Single Scan LOV
3427 IF value IS NULL THEN
3428 wild := '%';
3429 ELSE
3430 wild := value || '%';
3431 END IF; */
3432
3433 -- After Single Scan LOV
3434 wild := value;
3435
3436 --dgupta: Start R12 EAM Integration. Bug 4345492
3437 x_asset_group_id := get_asset_group_id(x_org_id, x_asset_group);
3438 x_asset_instance_id := get_asset_instance_id(x_asset_instance_number);
3439
3440 sql_string := 'SELECT
3441 distinct msn.serial_number, msn.descriptive_text
3442 FROM
3443 mtl_serial_numbers msn, csi_item_instances cii, mtl_system_items_b msib, mtl_parameters mp
3444 WHERE
3445 msib.organization_id = mp.organization_id and
3446 msib.organization_id = cii.last_vld_organization_id and
3447 msib.inventory_item_id = cii.inventory_item_id and
3448 msib.eam_item_type in (1,3) and
3449 sysdate between nvl(cii.active_start_date(+), sysdate-1)
3450 and nvl(cii.active_end_date(+), sysdate+1) and
3451 msib.organization_id = msn.current_organization_id and
3452 msib.inventory_item_id = msn.inventory_item_id and
3453 mp.maint_organization_id = :1 and
3454 msn.inventory_item_id = :2 and --removed nvl: serial number requires asset group as well
3455 msn.serial_number like :3 and
3456 cii.instance_id= nvl(:4, cii.instance_id)
3457 order by msn.serial_number';
3458
3459 OPEN x_ref FOR sql_string USING x_org_id , x_asset_group_id, wild, x_asset_instance_id;
3460 --dgupta: End R12 EAM Integration. Bug 4345492
3461
3462 END get_asset_number_lov;
3463
3464
3465 PROCEDURE get_subinventory_lov (x_org_id IN NUMBER, value IN VARCHAR2,
3466 x_ref OUT NOCOPY LovRefCursor) IS
3467
3468 wild VARCHAR2(160);
3469 sql_string VARCHAR2(1500);
3470
3471 BEGIN
3472
3473 -- This procedure is used for both subinventory and component subinventory
3474
3475 /* Before Single Scan LOV
3476 IF value IS NULL THEN
3477 wild := '%';
3478 ELSE
3479 wild := value || '%';
3480 END IF; */
3481
3482 -- After Single Scan LOV
3483 wild := value;
3484
3485 sql_string := 'SELECT secondary_inventory_name, description
3486 FROM mtl_secondary_inventories
3487 WHERE organization_id = :1
3488 AND nvl(disable_date, sysdate+1) > sysdate
3489 AND secondary_inventory_name like :2
3490 ORDER BY secondary_inventory_name';
3491
3492 OPEN x_ref FOR sql_string USING x_org_id, wild;
3493
3494 END get_subinventory_lov;
3495
3496
3497 PROCEDURE get_lot_number_lov (x_transaction_id IN NUMBER, value IN VARCHAR2,
3498 x_ref OUT NOCOPY LovRefCursor) IS
3499
3500 wild VARCHAR2(160);
3501 sql_string VARCHAR2(1500);
3502
3503 BEGIN
3504
3505 /* Before Single Scan LOV
3506 IF value IS NULL THEN
3507 wild := '%';
3508 ELSE
3509 wild := value || '%';
3510 END IF; */
3511
3512 -- After Single Scan LOV
3513 wild := value;
3514
3515 sql_string := 'SELECT lot_number, lot_expiration_date
3516 FROM mtl_transaction_lots_temp
3517 WHERE transaction_temp_id = :1
3518 AND lot_number like :2
3519 ORDER BY lot_number';
3520
3521 OPEN x_ref FOR sql_string USING x_transaction_id, wild;
3522
3523 END get_lot_number_lov;
3524
3525
3526 PROCEDURE get_serial_number_lov (x_transaction_id IN NUMBER, x_lot_number
3527 IN VARCHAR2, value IN VARCHAR2, x_ref OUT NOCOPY LovRefCursor) IS
3528
3529 wild VARCHAR2(160);
3530 sql_string VARCHAR2(1500);
3531
3532 BEGIN
3533
3534 wild := value;
3535
3536 --
3537 -- Bug 3758145. The original SQL is incorrect in transaction scenario.
3538 -- the WHERE conditions msn.line_mark_id should be rewritten as
3539 -- msn.lot_line_mark_id and vice versa.
3540 -- bso Tue Jul 20 15:52:21 PDT 2004
3541 --
3542 sql_string := 'SELECT msn.serial_number, msn.current_status
3543 FROM mtl_serial_numbers msn,
3544 mtl_transaction_lots_temp mtlt
3545 WHERE msn.lot_line_mark_id = :1
3546 AND mtlt.transaction_temp_id = msn.lot_line_mark_id
3547 AND mtlt.serial_transaction_temp_id = msn.line_mark_id
3548 AND mtlt.lot_number = :2
3549 AND :3 IS NOT NULL
3550 AND msn.serial_number like :4
3551 UNION ALL
3552 SELECT msn.serial_number, msn.current_status
3553 FROM mtl_serial_numbers msn
3554 WHERE msn.line_mark_id = :5
3555 AND :6 IS NULL
3556 AND msn.serial_number like :7
3557 ORDER BY 1';
3558
3559 OPEN x_ref FOR sql_string USING x_transaction_id, x_lot_number,
3560 x_lot_number, wild, x_transaction_id, x_lot_number, wild;
3561
3562 END get_serial_number_lov;
3563
3564 --
3565 -- Removed the DEFAULT clause to make the code GSCC compliant
3566 -- List of changed arguments.
3567 -- Old
3568 -- production_line IN VARCHAR2 DEFAULT NULL
3569 -- New
3570 -- production_line IN VARCHAR2
3571 --
3572
3573 PROCEDURE get_op_seq_number_lov (org_id IN NUMBER, value IN VARCHAR2,
3574 job_name IN VARCHAR2, production_line IN VARCHAR2,
3575 x_ref OUT NOCOPY LovRefCursor) IS
3576
3577 wild VARCHAR2(160);
3578 sql_string VARCHAR2(1500);
3579 x_line_id NUMBER DEFAULT NULL;
3580 x_wip_entity_id NUMBER DEFAULT NULL;
3581
3582 BEGIN
3583
3584 /* Before Single Scan LOV
3585 IF value IS NULL THEN
3586 wild := '%';
3587 ELSE
3588 wild := value || '%';
3589 END IF; */
3590
3591 -- After Single Scan LOV
3592 wild := value;
3593
3594 IF (production_line IS NOT NULL) THEN
3595 x_line_id := get_production_line_id(org_id, production_line);
3596 END IF;
3597
3598 x_wip_entity_id := get_job_id(org_id, job_name);
3599
3600
3601 IF (x_line_id IS NULL) THEN
3602
3603 sql_string := 'SELECT operation_seq_num, operation_code
3604 FROM wip_operations_all_v
3605 WHERE operation_seq_num like :1
3606 AND wip_entity_id = :2
3607 AND organization_id = :3
3608 ORDER BY operation_seq_num';
3609
3610 OPEN x_ref FOR sql_string USING wild, x_wip_entity_id, org_id;
3611
3612 ELSE
3613
3614 sql_string := 'SELECT operation_seq_num, operation_code
3615 FROM wip_operations_all_v
3616 WHERE operation_seq_num like :1
3617 AND wip_entity_id = :2
3618 AND organization_id = :3
3619 AND repetitive_schedule_id =
3620 (
3621 SELECT repetitive_schedule_id
3622 FROM wip_first_open_schedule_v
3623 WHERE line_id = :4
3624 AND wip_entity_id = :5
3625 AND organization_id = :6
3626 )
3627 ORDER BY operation_seq_num';
3628
3629 OPEN x_ref FOR sql_string USING wild, x_wip_entity_id, org_id,
3630 x_line_id, x_wip_entity_id, org_id;
3631
3632 END IF;
3633
3634 END get_op_seq_number_lov;
3635
3636 --
3637 -- See Bug 2588213
3638 -- To support the element Maintenance Op Seq Number
3639 -- to be used along with Maintenance Workorder
3640 -- rkunchal Mon Sep 23 23:46:28 PDT 2002
3641 --
3642
3643 PROCEDURE get_maintenance_op_seq_lov(org_id IN NUMBER,
3644 value IN VARCHAR2,
3645 maintenance_work_order IN VARCHAR2,
3646 x_ref OUT NOCOPY LovRefCursor) IS
3647 wild VARCHAR2(160);
3648 sql_string VARCHAR2(1500);
3649 x_wip_entity_id NUMBER DEFAULT NULL;
3650
3651 BEGIN
3652
3653 /* Before Single Scan LOV
3654 IF value IS NULL THEN
3655 wild := '%';
3656 ELSE
3657 wild := value || '%';
3658 END IF; */
3659
3660 -- After Single Scan LOV
3661 wild := value;
3662
3663 x_wip_entity_id := get_job_id(org_id, maintenance_work_order);
3664
3665 sql_string := 'SELECT operation_seq_num, operation_code
3666 FROM wip_operations_all_v
3667 WHERE operation_seq_num like :1
3668 AND wip_entity_id = :2
3669 AND organization_id = :3
3670 ORDER BY operation_seq_num';
3671
3672 OPEN x_ref FOR sql_string USING wild, x_wip_entity_id, org_id;
3673
3674 END get_maintenance_op_seq_lov;
3675
3676 --
3677 -- End of inclusions for Bug 2588213
3678 --
3679
3680
3681 --
3682 -- R12 Project MOAC 4637896
3683 -- Change po_number param to p_po_header_id and change the
3684 -- SQL to bind to this param.
3685 -- bso Sat Oct 8 12:29:58 PDT 2005
3686 --
3687 PROCEDURE get_po_line_number_lov(p_po_header_id IN NUMBER, value IN
3688 VARCHAR2, x_ref OUT NOCOPY LovRefCursor) IS
3689
3690 wild VARCHAR2(160);
3691 sql_string VARCHAR2(1500);
3692
3693 BEGIN
3694
3695 /* Before Single Scan LOV
3696 IF value IS NULL THEN
3697 wild := '%';
3698 ELSE
3699 wild := value || '%';
3700 END IF; */
3701
3702 -- After Single Scan LOV
3703 wild := value;
3704
3705 sql_string := 'SELECT line_num, concatenated_segments
3706 FROM po_lines_val_v
3707 WHERE po_header_id = :1
3708 AND line_num like :2
3709 ORDER BY line_num';
3710
3711 OPEN x_ref FOR sql_string USING p_po_header_id, wild;
3712
3713 END get_po_line_number_lov;
3714
3715
3716 --
3717 -- R12 Project MOAC 4637896
3718 -- Change po_number param to p_po_header_id and change the
3719 -- SQL to bind to this param.
3720 -- bso Sat Oct 8 12:29:58 PDT 2005
3721 --
3722 PROCEDURE get_po_shipments_lov(po_line_num IN NUMBER, p_po_header_id IN NUMBER,
3723 value IN VARCHAR2, x_ref OUT NOCOPY LovRefCursor) IS
3724
3725 wild VARCHAR2(160);
3726 sql_string VARCHAR2(1500);
3727
3728 BEGIN
3729
3730 /* Before Single Scan LOV
3731 IF value IS NULL THEN
3732 wild := '%';
3733 ELSE
3734 wild := value || '%';
3735 END IF; */
3736
3737 -- After Single Scan LOV
3738 wild := value;
3739
3740 /*
3741 sql_string := 'SELECT shipment_num, shipment_type
3742 FROM po_shipments_all_v
3743 WHERE po_line_id =
3744 (SELECT po_line_id
3745 FROM po_lines_val_v
3746 WHERE line_num = :1
3747 AND po_header_id = :2)
3748 AND shipment_num like :3';
3749 */
3750 -- Bug 4958763. SQL Repository Fix SQL ID: 15009194
3751 sql_string := 'SELECT shipment_num, shipment_type
3752 FROM po_line_locations
3753 WHERE po_line_id =
3754 (SELECT po_line_id
3755 FROM po_lines
3756 WHERE line_num = :1
3757 AND po_header_id = :2)
3758 AND shipment_num like :3';
3759
3760 OPEN x_ref FOR sql_string USING po_line_num, p_po_header_id, wild;
3761
3762 END get_po_shipments_lov;
3763
3764
3765 PROCEDURE get_receipt_num_lov (value IN VARCHAR2, x_ref OUT NOCOPY LovRefCursor) IS
3766
3767 wild VARCHAR2(160);
3768 sql_string VARCHAR2(1500);
3769
3770 BEGIN
3771
3772 /* Before Single Scan LOV
3773 IF value IS NULL THEN
3774 wild := '%';
3775 ELSE
3776 wild := value || '%';
3777 END IF; */
3778
3779 -- After Single Scan LOV
3780 wild := value;
3781
3782 -- Bug 7491455
3783 -- FP for bug 6800960.Changed the sql to include the RMA receipts.
3784 -- pdube Fri Oct 17 00:14:28 PDT 2008
3785 /*sql_string := 'SELECT RCVSH.receipt_num, POV.vendor_name
3786 FROM rcv_receipts_all_v
3787 WHERE receipt_num like :1
3788 ORDER BY receipt_num';
3789 */
3790 sql_string := 'SELECT RCVSH.receipt_num, POV.vendor_name
3791 FROM RCV_SHIPMENT_HEADERS RCVSH,
3792 PO_VENDORS POV,
3793 RCV_TRANSACTIONS RT
3794 WHERE RCVSH.RECEIPT_SOURCE_CODE in (''VENDOR'',''CUSTOMER'') AND
3795 RCVSH.VENDOR_ID = POV.VENDOR_ID(+) AND
3796 RT.SHIPMENT_HEADER_ID = RCVSH.SHIPMENT_HEADER_ID AND
3797 rcvsh.receipt_num like :1
3798 ORDER BY RCVSH.receipt_num';
3799
3800 OPEN x_ref FOR sql_string USING wild;
3801
3802 END get_receipt_num_lov;
3803
3804
3805 PROCEDURE get_party_lov (value IN VARCHAR2, x_ref OUT NOCOPY LovRefCursor) IS
3806
3807 wild VARCHAR2(160);
3808 sql_string VARCHAR2(1500);
3809 BEGIN
3810
3811 /* Before Single Scan LOV
3812 IF value IS NULL THEN
3813 wild := '%';
3814 ELSE
3815 wild := value || '%';
3816 END IF; */
3817
3818 -- After Single Scan LOV
3819 wild := value;
3820
3821 /*
3822 --Reviewing 2255344. Incorrect SQL. bso
3823
3824 sql_string := 'SELECT party_number, party_name
3825 FROM hz_parties
3826 WHERE party_number like :1
3827 AND status = ''A''
3828 AND party_type IN (''ORGANIZATION'',''PERSON'')
3829 ORDER BY party_name';
3830 */
3831 sql_string := 'SELECT party_name, party_number
3832 FROM hz_parties
3833 WHERE party_name like :1
3834 AND status = ''A''
3835 AND party_type IN (''ORGANIZATION'',''PERSON'')
3836 ORDER BY party_name';
3837
3838 OPEN x_ref FOR sql_string USING wild;
3839
3840 END get_party_lov;
3841
3842 --
3843 -- Implemented the following six get_lov procedures for
3844 -- Service_Item, Counter, Maintenance_Requirement, Service_Request, Rework_Job
3845 -- For ASO project
3846 -- rkunchal Thu Jul 25 01:43:48 PDT 2002
3847 --
3848
3849 PROCEDURE get_item_instance_lov (value IN VARCHAR2, x_ref OUT NOCOPY LovRefCursor) IS
3850
3851 wild VARCHAR2(160);
3852 sql_string VARCHAR2(1500);
3853
3854 BEGIN
3855
3856 /* Before Single Scan LOV
3857 IF value IS NULL THEN
3858 wild := '%';
3859 ELSE
3860 wild := value || '%';
3861 END IF; */
3862
3863 -- After Single Scan LOV
3864 wild := value;
3865
3866 sql_string := 'SELECT cii.instance_number, cii.serial_number
3867 FROM qa_csi_item_instances cii, mtl_system_items_kfv msik
3868 WHERE cii.inventory_item_id = msik.inventory_item_id
3869 AND cii.last_vld_organization_id = msik.organization_id
3870 AND instance_number like :1
3871 ORDER BY 1';
3872
3873 OPEN x_ref FOR sql_string USING wild;
3874
3875 END get_item_instance_lov;
3876
3877
3878 PROCEDURE get_counter_name_lov (value IN VARCHAR2, x_ref OUT NOCOPY LovRefCursor) IS
3879
3880 wild VARCHAR2(160);
3881 sql_string VARCHAR2(1500);
3882
3883 BEGIN
3884
3885 /* Before Single Scan LOV
3886 IF value IS NULL THEN
3887 wild := '%';
3888 ELSE
3889 wild := value || '%';
3890 END IF; */
3891
3892 -- After Single Scan LOV
3893 wild := value;
3894
3895 -- Bug 4958763. SQL Repository Fix SQL ID: 15009209
3896 /*
3897 sql_string := 'SELECT cc.name, cc.description
3898 FROM cs_counters cc, cs_counter_groups ccg
3899 WHERE cc.counter_group_id = ccg.counter_group_id
3900 AND ccg.template_flag = ''N''
3901 AND cc.name like :1
3902 ORDER BY 1';
3903 */
3904 sql_string := 'SELECT name, description
3905 FROM csi_counters_vl
3906 WHERE name like :1
3907 AND trunc(sysdate) BETWEEN nvl(start_date_active, trunc(sysdate))
3908 AND nvl(end_date_active, trunc(sysdate))
3909 ORDER BY 1';
3910
3911 OPEN x_ref FOR sql_string USING wild;
3912
3913 END get_counter_name_lov;
3914
3915
3916 PROCEDURE get_maintenance_req_lov (value IN VARCHAR2, x_ref OUT NOCOPY LovRefCursor) IS
3917
3918 wild VARCHAR2(160);
3919 sql_string VARCHAR2(1500);
3920
3921 BEGIN
3922
3923 /* Before Single Scan LOV
3924 IF value IS NULL THEN
3925 wild := '%';
3926 ELSE
3927 wild := value || '%';
3928 END IF; */
3929
3930 -- After Single Scan LOV
3931 wild := value;
3932
3933 sql_string := 'SELECT title, version_number
3934 FROM qa_ahl_mr
3935 WHERE title like :1
3936 ORDER BY 1';
3937
3938 OPEN x_ref FOR sql_string USING wild;
3939
3940 END get_maintenance_req_lov;
3941
3942
3943 PROCEDURE get_service_request_lov (value IN VARCHAR2, x_ref OUT NOCOPY LovRefCursor) IS
3944
3945 wild VARCHAR2(160);
3946 sql_string VARCHAR2(1500);
3947
3948 BEGIN
3949
3950 /* Before Single Scan LOV
3951 IF value IS NULL THEN
3952 wild := '%';
3953 ELSE
3954 wild := value || '%';
3955 END IF; */
3956
3957 -- After Single Scan LOV
3958 wild := value;
3959
3960 sql_string := 'SELECT incident_number, summary
3961 FROM cs_incidents
3962 WHERE incident_number like :1
3963 ORDER BY 1';
3964
3965 OPEN x_ref FOR sql_string USING wild;
3966
3967 END get_service_request_lov;
3968
3969
3970 PROCEDURE get_rework_job_lov (org_id IN NUMBER, value IN VARCHAR2,
3971 x_ref OUT NOCOPY LovRefCursor) IS
3972
3973 wild VARCHAR2(160);
3974 sql_string VARCHAR2(1500);
3975
3976 BEGIN
3977
3978 /* Before Single Scan LOV
3979 IF value IS NULL THEN
3980 wild := '%';
3981 ELSE
3982 wild := value || '%';
3983 END IF; */
3984
3985 -- After Single Scan LOV
3986 wild := value;
3987
3988 sql_string := 'SELECT wip_entity_name, description
3989 FROM wip_discrete_jobs_all_v
3990 WHERE wip_entity_name like :1 AND
3991 organization_id = :2
3992 ORDER BY wip_entity_name';
3993 OPEN x_ref FOR sql_string USING wild, org_id;
3994
3995 END get_rework_job_lov;
3996
3997 --
3998 -- End of inclusions for ASO project
3999 -- rkunchal Thu Jul 25 01:43:48 PDT 2002
4000 --
4001
4002 -- Start of inclusions for NCM Hardcode Elements.
4003 -- suramasw Thu Oct 31 10:48:59 PST 2002.
4004 -- Bug 2449067.
4005
4006
4007 PROCEDURE get_bill_reference_lov (x_org_id IN NUMBER, value IN VARCHAR2,
4008 x_ref OUT NOCOPY LovRefCursor) IS
4009
4010 wild VARCHAR2(160);
4011 sql_string VARCHAR2(1500);
4012
4013 BEGIN
4014
4015 /* Before Single Scan LOV
4016 IF value IS NULL THEN
4017 wild := '%';
4018 ELSE
4019 wild := value || '%';
4020 END IF; */
4021
4022 -- After Single Scan LOV
4023 wild := value;
4024
4025 sql_string := 'SELECT concatenated_segments, description
4026 FROM mtl_system_items_kfv
4027 WHERE organization_id = :1
4028 AND concatenated_segments like :2
4029 ORDER BY concatenated_segments';
4030
4031 OPEN x_ref FOR sql_string USING x_org_id, wild;
4032
4033 END get_bill_reference_lov;
4034
4035 PROCEDURE get_routing_reference_lov (x_org_id IN NUMBER, value IN VARCHAR2,
4036 x_ref OUT NOCOPY LovRefCursor) IS
4037
4038 wild VARCHAR2(160);
4039 sql_string VARCHAR2(1500);
4040
4041 BEGIN
4042
4043 /* Before Single Scan LOV
4044 IF value IS NULL THEN
4045 wild := '%';
4046 ELSE
4047 wild := value || '%';
4048 END IF; */
4049
4050 -- After Single Scan LOV
4051 wild := value;
4052
4053 sql_string := 'SELECT concatenated_segments, description
4054 FROM mtl_system_items_kfv
4055 WHERE organization_id = :1
4056 AND concatenated_segments like :2
4057 ORDER BY concatenated_segments';
4058
4059 OPEN x_ref FOR sql_string USING x_org_id, wild;
4060
4061 END get_routing_reference_lov;
4062
4063 PROCEDURE get_to_locator_lov (x_org_id IN NUMBER, x_item_name IN VARCHAR2,
4064 value IN VARCHAR2, x_ref OUT NOCOPY LovRefCursor) IS
4065
4066 wild VARCHAR2(160);
4067 sql_string VARCHAR2(1500);
4068
4069 BEGIN
4070
4071 /* Before Single Scan LOV
4072 IF value IS NULL THEN
4073 wild := '%';
4074 ELSE
4075 wild := value || '%';
4076 END IF; */
4077
4078 -- After Single Scan LOV
4079 wild := value;
4080
4081 sql_string := 'SELECT concatenated_segments, description
4082 FROM mtl_item_locations_kfv
4083 WHERE organization_id = :1
4084 AND concatenated_segments like :2
4085 ORDER BY concatenated_segments';
4086
4087 OPEN x_ref FOR sql_string USING x_org_id, wild;
4088
4089 END get_to_locator_lov;
4090
4091 PROCEDURE get_to_subinventory_lov (x_org_id IN NUMBER, value IN VARCHAR2,
4092 x_ref OUT NOCOPY LovRefCursor) IS
4093
4094 wild VARCHAR2(160);
4095 sql_string VARCHAR2(1500);
4096
4097 BEGIN
4098
4099 /* Before Single Scan LOV
4100 IF value IS NULL THEN
4101 wild := '%';
4102 ELSE
4103 wild := value || '%';
4104 END IF; */
4105
4106 -- After Single Scan LOV
4107 wild := value;
4108
4109 sql_string := 'SELECT secondary_inventory_name, description
4110 FROM mtl_secondary_inventories
4111 WHERE organization_id = :1
4112 AND nvl(disable_date, sysdate+1) > sysdate
4113 AND secondary_inventory_name like :2
4114 ORDER BY secondary_inventory_name';
4115
4116 OPEN x_ref FOR sql_string USING x_org_id, wild;
4117
4118 END get_to_subinventory_lov;
4119
4120 PROCEDURE get_lot_status_lov (x_org_id IN NUMBER, x_lot_num IN VARCHAR2,
4121 x_item_name IN VARCHAR2, value IN VARCHAR2,
4122 x_ref OUT NOCOPY LovRefCursor) IS
4123
4124 wild VARCHAR2(160);
4125 sql_string VARCHAR2(1500);
4126
4127 x_item_id NUMBER;
4128
4129 BEGIN
4130
4131 /* Before Single Scan LOV
4132 IF value IS NULL THEN
4133 wild := '%';
4134 ELSE
4135 wild := value || '%';
4136 END IF; */
4137
4138 -- After Single Scan LOV
4139 wild := value;
4140
4141 x_item_id := qa_flex_util.get_item_id(x_org_id, x_item_name);
4142
4143 -- Added the organization_id condition in the following select statement.
4144 -- Bug 2686970. suramasw Wed Nov 27 04:45:34 PST 2002.
4145
4146 sql_string := 'SELECT mms.status_code, mms.description
4147 FROM mtl_lot_numbers mln, mtl_material_statuses mms
4148 WHERE mln.inventory_item_id = :1
4149 AND mln.organization_id = :2
4150 AND mln.lot_number like :3
4151 AND mln.status_id = mms.status_id
4152 AND mms.status_code like :4
4153 AND mms.enabled_flag = 1';
4154
4155 OPEN x_ref FOR sql_string USING x_item_id, x_org_id, x_lot_num, wild;
4156
4157 END get_lot_status_lov;
4158
4159 FUNCTION get_lot_status_id (value IN VARCHAR2)
4160 RETURN NUMBER IS
4161
4162 id NUMBER;
4163
4164 CURSOR c (code VARCHAR2) IS
4165 SELECT status_id
4166 FROM mtl_material_statuses
4167 WHERE status_code = code;
4168
4169 BEGIN
4170
4171 IF value IS NULL THEN
4172 RETURN NULL;
4173 END IF;
4174
4175 OPEN c(value);
4176 FETCH c INTO id;
4177 CLOSE c;
4178
4179 RETURN id;
4180
4181 END get_lot_status_id;
4182
4183 PROCEDURE get_serial_status_lov (x_org_id IN NUMBER, x_serial_num IN VARCHAR2,
4184 x_item_name IN VARCHAR2, value IN VARCHAR2,
4185 x_ref OUT NOCOPY LovRefCursor) IS
4186
4187 wild VARCHAR2(160);
4188 sql_string VARCHAR2(1500);
4189
4190 x_item_id NUMBER;
4191
4192 BEGIN
4193
4194 /* Before Single Scan LOV
4195 IF value IS NULL THEN
4196 wild := '%';
4197 ELSE
4198 wild := value || '%';
4199 END IF; */
4200
4201 -- After Single Scan LOV
4202 wild := value;
4203
4204 x_item_id := qa_flex_util.get_item_id(x_org_id, x_item_name);
4205
4206 sql_string := 'SELECT mms.status_code, mms.description
4207 FROM mtl_serial_numbers msn, mtl_material_statuses mms
4208 WHERE msn.inventory_item_id = :1
4209 AND msn.serial_number like :2
4210 AND msn.status_id = mms.status_id
4211 AND mms.status_code like :3
4212 AND mms.enabled_flag = 1';
4213
4214 OPEN x_ref FOR sql_string USING x_item_id, x_serial_num, wild;
4215
4216 END get_serial_status_lov;
4217
4218 -- R12 OPM Deviations. Bug 4345503 Start
4219 PROCEDURE get_process_batch_num_lov
4220 (p_org_id IN NUMBER,
4221 value IN VARCHAR2,
4222 x_ref OUT NOCOPY LovRefCursor) IS
4223
4224 wild VARCHAR2(160);
4225 sql_string VARCHAR2(1500);
4226 BEGIN
4227 wild := value||'%';
4228
4229 sql_string := 'SELECT BATCH_NO, BATCH_NO BATCH_DESC '||
4230 'FROM GME_BATCH_HEADER '||
4231 'WHERE BATCH_NO like :1 '||
4232 'AND (ORGANIZATION_ID = :2 '||
4233 ' or ORGANIZATION_ID IS NULL)';
4234
4235 OPEN x_ref FOR sql_string USING wild,p_org_id;
4236
4237 END get_process_batch_num_lov;
4238
4239 PROCEDURE get_process_batchstep_num_lov
4240 (p_org_id IN NUMBER,
4241 p_process_batch_num IN VARCHAR2,
4242 value IN VARCHAR2,
4243 x_ref OUT NOCOPY LovRefCursor) IS
4244
4245 wild VARCHAR2(160);
4246 sql_string VARCHAR2(1500);
4247 l_batch_id NUMBER;
4248 BEGIN
4249 l_batch_id := get_process_batch_id(p_process_batch_num, p_org_id);
4250 wild := value;
4251 sql_string := 'SELECT STEPS.BATCHSTEP_NO,OPS.OPRN_DESC BATCHSTEP_DESC '||
4252 'FROM GME_BATCH_STEPS STEPS, GMD_OPERATIONS OPS '||
4253 'WHERE STEPS.BATCHSTEP_NO like :1 '||
4254 'AND STEPS.BATCH_ID =:2 '||
4255 'AND STEPS.OPRN_ID = OPS.OPRN_ID';
4256
4257 OPEN x_ref FOR sql_string USING wild, l_batch_id;
4258
4259 END get_process_batchstep_num_lov;
4260
4261 PROCEDURE get_process_operation_lov
4262 (p_org_id IN NUMBER,
4263 p_process_batch_num IN VARCHAR2,
4264 p_process_batchstep_num IN NUMBER,
4265 value IN VARCHAR2,
4266 x_ref OUT NOCOPY LovRefCursor) IS
4267
4268 wild VARCHAR2(160);
4269 sql_string VARCHAR2(1500);
4270 l_batch_id NUMBER;
4271 l_batchstep_id NUMBER;
4272 BEGIN
4273 l_batch_id := get_process_batch_id(p_process_batch_num, p_org_id);
4274 l_batchstep_id := get_process_batchstep_id(p_process_batchstep_num, l_batch_id);
4275 wild := value;
4276
4277 sql_string := 'SELECT OPERATION PROCESS_OPERATION, OPRN_DESC '||
4278 'FROM GMO_BATCH_STEPS_V '||
4279 'WHERE OPERATION like :1 '||
4280 'AND BATCHSTEP_ID = :2 '||
4281 'AND BATCH_ID =:3';
4282
4283 OPEN x_ref FOR sql_string USING wild, l_batchstep_id, l_batch_id;
4284
4285 END get_process_operation_lov;
4286
4287 PROCEDURE get_process_activity_lov
4288 (p_org_id IN NUMBER,
4289 p_process_batch_num IN VARCHAR2,
4290 p_process_batchstep_num IN NUMBER,
4291 value IN VARCHAR2,
4292 x_ref OUT NOCOPY LovRefCursor) IS
4293
4294 wild VARCHAR2(160);
4295 sql_string VARCHAR2(1500);
4296 l_batch_id NUMBER;
4297 l_batchstep_id NUMBER;
4298 l_activity_id NUMBER;
4299 BEGIN
4300 l_batch_id := get_process_batch_id(p_process_batch_num, p_org_id);
4301 l_batchstep_id := get_process_batchstep_id(p_process_batchstep_num, l_batch_id);
4302 wild := value;
4303
4304 sql_string := 'SELECT STEPS.ACTIVITY,ACTIVITIES.ACTIVITY_DESC '||
4305 'FROM GME_BATCH_STEP_ACTIVITIES STEPS, GMD_ACTIVITIES ACTIVITIES '||
4306 'WHERE STEPS.ACTIVITY like :1 '||
4307 'AND STEPS.BATCHSTEP_ID =:2 '||
4308 'AND STEPS.BATCH_ID =:3 '||
4309 'AND STEPS.ACTIVITY = ACTIVITIES.ACTIVITY';
4310
4311 OPEN x_ref FOR sql_string USING wild, l_batchstep_id, l_batch_id;
4312
4313 END get_process_activity_lov;
4314
4315 PROCEDURE get_process_resource_lov
4316 (p_org_id IN NUMBER,
4317 p_process_batch_num IN VARCHAR2,
4318 p_process_batchstep_num IN NUMBER,
4319 p_process_activity IN VARCHAR2,
4320 value IN VARCHAR2,
4321 x_ref OUT NOCOPY LovRefCursor) IS
4322
4323 wild VARCHAR2(160);
4324 sql_string VARCHAR2(1500);
4325 l_batch_id NUMBER;
4326 l_batchstep_id NUMBER;
4327 l_activity_id NUMBER;
4328 BEGIN
4329 l_batch_id := get_process_batch_id(p_process_batch_num, p_org_id);
4330 l_batchstep_id := get_process_batchstep_id(p_process_batchstep_num, l_batch_id);
4331 l_activity_id := get_process_activity_id(p_process_activity, l_batch_id, l_batchstep_id);
4332 wild := value;
4333
4334 sql_string := 'SELECT GBSR.RESOURCES, CRMV.RESOURCE_DESC '||
4335 'FROM GME_BATCH_STEP_RESOURCES GBSR, CR_RSRC_MST_VL CRMV '||
4336 'WHERE GBSR.RESOURCES like :1 '||
4337 'AND GBSR.BATCHSTEP_ACTIVITY_ID =:2 '||
4338 'AND GBSR.BATCHSTEP_ID =:3 '||
4339 'AND GBSR.BATCH_ID =:4 '||
4340 'AND GBSR.RESOURCES = CRMV.RESOURCES';
4341
4342 OPEN x_ref FOR sql_string USING wild, l_activity_id, l_batchstep_id, l_batch_id;
4343
4344 END get_process_resource_lov;
4345
4346 PROCEDURE get_process_parameter_lov
4347 (p_org_id IN NUMBER,
4348 p_process_resource IN VARCHAR2,
4349 value IN VARCHAR2,
4350 x_ref OUT NOCOPY LovRefCursor) IS
4351
4352 wild VARCHAR2(160);
4353 sql_string VARCHAR2(1500);
4354 BEGIN
4355 wild := value;
4356
4357 sql_string := 'SELECT GP.PARAMETER_NAME, GP.PARAMETER_DESCRIPTION '||
4358 'FROM GMP_PROCESS_PARAMETERS GP,GME_PROCESS_PARAMETERS GE '||
4359 'WHERE GE.RESOURCES = :1 '||
4360 'AND GE.PARAMETER_ID = GP.PARAMETER_ID '||
4361 'AND GP.PARAMETER_NAME like :2';
4362
4363 OPEN x_ref FOR sql_string USING p_process_resource, wild;
4364
4365 END get_process_parameter_lov;
4366
4367 -- R12 OPM Deviations. Bug 4345503 End
4368
4369 /* R12 DR Integration. Bug 4345489 Start */
4370
4371 PROCEDURE get_repair_number_lov (value IN VARCHAR2,
4372 x_ref OUT NOCOPY LovRefCursor) IS
4373
4374 wild VARCHAR2(160);
4375 sql_string VARCHAR2(1500);
4376
4377 BEGIN
4378
4379 wild := value;
4380
4381 sql_string := 'SELECT repair_number, problem_description
4382 FROM csd_repairs
4383 WHERE repair_number like :1
4384 and status not in (''C'', ''H'')
4385 ORDER BY repair_number';
4386
4387 OPEN x_ref FOR sql_string USING wild;
4388
4389 END get_repair_number_lov;
4390
4391 PROCEDURE get_jtf_task_lov (value IN VARCHAR2, x_ref OUT
4392 NOCOPY LovRefCursor) IS
4393
4394 wild VARCHAR2(160);
4395 sql_string VARCHAR2(1500);
4396
4397 BEGIN
4398
4399 wild := value;
4400
4401 sql_string := 'SELECT task_number, task_name
4402 FROM jtf_tasks_vl
4403 WHERE task_number like :1
4404 ORDER BY task_number';
4405
4406 OPEN x_ref FOR sql_string USING wild;
4407
4408 END get_jtf_task_lov;
4409
4410 /* R12 DR Integration. Bug 4345489 End */
4411
4412 FUNCTION get_serial_status_id (value IN VARCHAR2)
4413 RETURN NUMBER IS
4414
4415 id NUMBER;
4416
4417 CURSOR c (code VARCHAR2) IS
4418 SELECT status_id
4419 FROM mtl_material_statuses
4420 WHERE status_code = code;
4421
4422 BEGIN
4423
4424 IF value IS NULL THEN
4425 RETURN NULL;
4426 END IF;
4427
4428 OPEN c(value);
4429 FETCH c INTO id;
4430 CLOSE c;
4431
4432 RETURN id;
4433
4434 END get_serial_status_id;
4435
4436 -- End of inclusions for NCM Hardcode Elements.
4437
4438
4439 PROCEDURE get_item_lov (x_org_id IN NUMBER, value IN VARCHAR2,
4440 x_ref OUT NOCOPY LovRefCursor) IS
4441
4442 wild VARCHAR2(160);
4443 sql_string VARCHAR2(1500);
4444
4445 BEGIN
4446
4447 /* Before Single Scan LOV
4448 IF value IS NULL THEN
4449 wild := '%';
4450 ELSE
4451 wild := value || '%';
4452 END IF; */
4453
4454 -- After Single Scan LOV
4455 wild := value;
4456
4457 sql_string := 'SELECT concatenated_segments, description
4458 FROM mtl_system_items_kfv
4459 WHERE organization_id = :1
4460 AND concatenated_segments like :2
4461 ORDER BY concatenated_segments';
4462
4463 OPEN x_ref FOR sql_string USING x_org_id, wild;
4464
4465 END get_item_lov;
4466
4467
4468 --
4469 -- Bug 5292020 adding comp item LOV to mqa.
4470 -- comp item LOVs have three variants depending on what is
4471 -- present in the collection plan. Hence this LOV contains
4472 -- 3 SQLs selected depending on the input param. All SQLs
4473 -- adapted from flex field definition in QLTRES.pld.
4474 -- bso Thu Jun 8 00:16:03 PDT 2006
4475 --
4476 PROCEDURE get_comp_item_lov(
4477 p_org_id IN NUMBER,
4478 p_item_name IN VARCHAR2,
4479 p_job_name IN VARCHAR2,
4480 p_prod_line IN VARCHAR2,
4481 p_value IN VARCHAR2,
4482 x_ref OUT NOCOPY LovRefCursor) IS
4483
4484 l_sql_string VARCHAR2(1500);
4485 l_job_id NUMBER;
4486 l_prod_line_id NUMBER;
4487 l_item_id NUMBER;
4488
4489 BEGIN
4490
4491 IF p_item_name IS NOT NULL THEN
4492 l_item_id := qa_flex_util.get_item_id(p_org_id, p_item_name);
4493 END IF;
4494
4495 IF p_job_name IS NOT NULL THEN
4496 l_job_id := get_job_id(p_org_id, p_job_name);
4497 END IF;
4498
4499 IF p_prod_line IS NOT NULL THEN
4500 l_prod_line_id := get_production_line_id(p_org_id, p_prod_line);
4501 END IF;
4502
4503 IF l_job_id IS NOT NULL THEN
4504 --
4505 -- Comp Item with WIP Job dependency.
4506 --
4507 l_sql_string :=
4508 'SELECT concatenated_segments, description
4509 FROM mtl_system_items_kfv
4510 WHERE organization_id = :1 AND
4511 concatenated_segments like :2 AND
4512 inventory_item_id IN (
4513 SELECT inventory_item_id
4514 FROM wip_requirement_operations
4515 WHERE wip_entity_id = :3 AND
4516 organization_id = :4)';
4517 OPEN x_ref FOR l_sql_string USING p_org_id, p_value, l_job_id,
4518 p_org_id;
4519
4520 ELSIF l_prod_line_id IS NOT NULL AND l_item_id IS NOT NULL THEN
4521 --
4522 -- Comp Item with Flow Production Line dependency.
4523 --
4524 l_sql_string :=
4525 'SELECT concatenated_segments, description
4526 FROM mtl_system_items_kfv
4527 WHERE organization_id = :1 AND
4528 concatenated_segments like :2 AND
4529 inventory_item_id IN (
4530 SELECT inventory_item_id
4531 FROM wip_requirement_operations
4532 WHERE wip_entity_id = (
4533 SELECT wip_entity_id
4534 FROM wip_repetitive_items
4535 WHERE line_id = :3 AND
4536 primary_item_id = :4 AND
4537 organization_id = :5))';
4538 OPEN x_ref FOR l_sql_string USING p_org_id, p_value, l_prod_line_id,
4539 l_item_id, p_org_id;
4540 ELSIF l_item_id IS NOT NULL THEN
4541 --
4542 -- Comp Item with BOM Component Dependency.
4543 --
4544 l_sql_string :=
4545 'SELECT concatenated_segments, description
4546 FROM mtl_system_items_kfv
4547 WHERE organization_id = :1 AND
4548 concatenated_segments like :2 AND
4549 inventory_item_id IN (
4550 SELECT bic.component_item_id
4551 FROM bom_inventory_components bic,
4552 bom_bill_of_materials bom
4553 WHERE bic.bill_sequence_id = bom.bill_sequence_id AND
4554 bic.effectivity_date <= sysdate AND
4555 nvl(bic.disable_date, sysdate+1) > sysdate AND
4556 bom.assembly_item_id = :3 AND
4557 bom.organization_id = :4)';
4558 OPEN x_ref FOR l_sql_string USING p_org_id, p_value, l_item_id,
4559 p_org_id;
4560 ELSE
4561 --
4562 -- This is a catchall SQL that returns no value.
4563 --
4564 l_sql_string := 'SELECT ''x'', ''x'' FROM dual WHERE 1 = 2';
4565 open x_ref FOR l_sql_string;
4566 END IF;
4567
4568 END get_comp_item_lov;
4569
4570
4571 PROCEDURE get_asset_group_lov (x_org_id IN NUMBER, value IN VARCHAR2,
4572 x_ref OUT NOCOPY LovRefCursor) IS
4573
4574 wild VARCHAR2(160);
4575 sql_string VARCHAR2(1500);
4576
4577 BEGIN
4578
4579 /* Before Single Scan LOV
4580 IF value IS NULL THEN
4581 wild := '%';
4582 ELSE
4583 wild := value || '%';
4584 END IF; */
4585
4586 -- After Single Scan LOV
4587 wild := value;
4588
4589 --dgupta: Start R12 EAM Integration. Bug 4345492
4590 sql_string := 'select distinct msikfv.concatenated_segments, msikfv.description
4591 from mtl_system_items_b_kfv msikfv, mtl_parameters mp
4592 where msikfv.organization_id = mp.organization_id
4593 and msikfv.eam_item_type in (1,3)
4594 and mp.maint_organization_id = :1
4595 and msikfv.concatenated_segments like :2
4596 order by msikfv.concatenated_segments';
4597 --dgupta: End R12 EAM Integration. Bug 4345492
4598
4599 OPEN x_ref FOR sql_string USING x_org_id, wild;
4600
4601 END get_asset_group_lov;
4602
4603
4604
4605 --dgupta: Start R12 EAM Integration. Bug 4345492
4606 PROCEDURE get_asset_activity_lov (x_org_id IN NUMBER, p_asset_group IN VARCHAR2,
4607 p_asset_number IN VARCHAR2, p_asset_instance_number IN VARCHAR2, value IN VARCHAR2,
4608 x_ref OUT NOCOPY LovRefCursor) IS
4609
4610 wild VARCHAR2(160);
4611 sql_string VARCHAR2(1500);
4612 l_asset_group_id NUMBER DEFAULT NULL;
4613 l_asset_instance_id NUMBER DEFAULT NULL;
4614
4615 BEGIN
4616
4617 -- After Single Scan LOV
4618 wild := value;
4619
4620 l_asset_group_id := get_asset_group_id(x_org_id, p_asset_group);
4621 l_asset_instance_id := get_asset_instance_id(p_asset_instance_number);
4622 if (l_asset_instance_id is null) then
4623 l_asset_instance_id := get_asset_instance_id(l_asset_group_id, p_asset_number);
4624 end if;
4625
4626 if (p_asset_number is null and l_asset_instance_id is null) then
4627 -- show all activities asssociated to the asset group
4628 -- If no match found or if asset group passed in is null, lov is empty
4629 /*
4630 sql_string := 'SELECT meaav.activity, meaav.activity_description
4631 FROM mtl_eam_asset_activities_v meaav, mtl_system_items_b msib
4632 WHERE msib.organization_id = :1
4633 and meaav. maintenance_object_id = :2 --pass asset group inventory_item_id
4634 and (meaav.end_date_active is null or meaav.end_date_active > sysdate)
4635 and (meaav.start_date_active is null or meaav.start_date_active < sysdate)
4636 and msib.inventory_item_id = meaav. maintenance_object_id
4637 and meaav.maintenance_object_type = 2 --non serialized item
4638 AND meaav.activity like :3
4639 ORDER BY meaav.activity';
4640 */
4641 -- Bug 4958763. SQL Repository Fix SQL ID: 15009272
4642 sql_string := 'SELECT
4643 msib.concatenated_segments activity ,
4644 msib.description activity_description
4645 FROM mtl_eam_asset_activities meaav,
4646 mtl_system_items_b_kfv msib
4647 WHERE msib.organization_id = :1
4648 AND meaav. maintenance_object_id = :2 --pass asset group inventory_item_id
4649 AND (meaav.end_date_active is null
4650 OR meaav.end_date_active > sysdate)
4651 AND (meaav.start_date_active is null
4652 OR meaav.start_date_active < sysdate)
4653 AND msib.inventory_item_id = meaav.asset_activity_id
4654 AND meaav.maintenance_object_type = 2 --non serialized item
4655 AND msib.concatenated_segments like :3
4656 ORDER BY msib.concatenated_segments';
4657
4658 OPEN x_ref FOR sql_string USING x_org_id, l_asset_group_id, wild;
4659 else
4660 -- show all activities associated to asset group and asset number
4661 -- if exact match not found, lov is empty.
4662 /*
4663 sql_string := 'SELECT meaav.activity, meaav.activity_description
4664 FROM mtl_eam_asset_activities_v meaav, mtl_system_items_b msib
4665 WHERE msib.organization_id = :1
4666 and meaav.maintenance_object_id = :2 --pass asset instance_id
4667 and meaav.maintenance_object_type = 3 --serialized item
4668 and (meaav.end_date_active is null or meaav.end_date_active > sysdate)
4669 and (meaav.start_date_active is null or meaav.start_date_active < sysdate)
4670 and msib.inventory_item_id = meaav.inventory_item_id
4671 AND meaav.activity like :3
4672 ORDER BY meaav.activity';
4673 */
4674 -- Bug 4958763. SQL Repository Fix SQL ID: 15009282
4675 sql_string := 'SELECT
4676 msi.concatenated_segments activity ,
4677 msi.description activity_description
4678 FROM mtl_eam_asset_activities meaa,
4679 mtl_system_items_b_kfv msi
4680 WHERE msi.organization_id = :1
4681 AND meaa.maintenance_object_id = :2 --pass asset instance_id
4682 AND meaa.maintenance_object_type = 3 --serialized item
4683 AND (meaa.end_date_active is null
4684 OR meaa.end_date_active > sysdate)
4685 AND (meaa.start_date_active is null
4686 OR meaa.start_date_active < sysdate)
4687 AND msi.inventory_item_id = meaa.asset_activity_id
4688 AND msi.concatenated_segments like :3
4689 ORDER BY msi.concatenated_segments';
4690
4691 OPEN x_ref FOR sql_string USING x_org_id, l_asset_instance_id, wild;
4692 end if;
4693
4694 END get_asset_activity_lov;
4695
4696 -- added the following to include new hardcoded element followup activity
4697 -- saugupta Aug 2003
4698 --dgupta: just call asset activity lov, since query is same
4699 PROCEDURE get_followup_activity_lov (x_org_id IN NUMBER, p_asset_group IN VARCHAR2,
4700 p_asset_number IN VARCHAR2, p_asset_instance_number IN VARCHAR2, value IN VARCHAR2,
4701 x_ref OUT NOCOPY LovRefCursor) IS
4702 BEGIN
4703 get_asset_activity_lov(x_org_id, p_asset_group, p_asset_number, p_asset_instance_number,
4704 value, x_ref); --no use duplicating code
4705 END get_followup_activity_lov;
4706
4707 --dgupta: End R12 EAM Integration. Bug 4345492
4708
4709 PROCEDURE get_locator_lov (x_org_id IN NUMBER, x_item_name IN VARCHAR2,
4710 value IN VARCHAR2, x_ref OUT NOCOPY LovRefCursor) IS
4711
4712 wild VARCHAR2(160);
4713 sql_string VARCHAR2(1500);
4714
4715 BEGIN
4716
4717 /* Before Single Scan LOV
4718 IF value IS NULL THEN
4719 wild := '%';
4720 ELSE
4721 wild := value || '%';
4722 END IF; */
4723
4724 -- After Single Scan LOV
4725 wild := value;
4726
4727 sql_string := 'SELECT concatenated_segments, description
4728 FROM mtl_item_locations_kfv
4729 WHERE organization_id = :1
4730 AND concatenated_segments like :2
4731 ORDER BY concatenated_segments';
4732
4733 OPEN x_ref FOR sql_string USING x_org_id, wild;
4734
4735 END get_locator_lov;
4736
4737 --
4738 -- Removed the DEFAULT clause to make the code GSCC compliant
4739 -- List of changed arguments.
4740 -- Old
4741 -- user_id IN NUMBER DEFAULT NULL
4742 -- value IN VARCHAR2 DEFAULT NULL
4743 -- New
4744 -- user_id IN NUMBER
4745 -- value IN VARCHAR2
4746 --
4747
4748 PROCEDURE get_plan_element_lov(plan_id IN NUMBER, char_id IN NUMBER,
4749 org_id IN NUMBER, user_id IN NUMBER,
4750 value IN VARCHAR2,
4751 x_ref OUT NOCOPY LovRefCursor) IS
4752
4753 BEGIN
4754
4755 -- The function sql_string_exists simple checks to see
4756 -- if the user defined element should have a LOV
4757 -- associated with it or not. If it should then it returns
4758 -- true and populates sql_string - an out parameter.
4759
4760 IF sql_string_exists(plan_id, char_id, org_id, user_id, value, x_ref) THEN
4761 RETURN;
4762 ELSE
4763 --
4764 -- To prevent client from bombing, open an empty cursor in case
4765 -- this function if called but there is no LOV!
4766 --
4767 OPEN x_ref FOR
4768 SELECT 'x', 'x' FROM dual WHERE 1 = 2;
4769 END IF;
4770
4771 END get_plan_element_lov;
4772
4773
4774 PROCEDURE get_spec_details ( x_spec_id IN NUMBER, x_char_id IN NUMBER,
4775 x_target_value OUT NOCOPY VARCHAR2,
4776 x_lower_spec_limit OUT NOCOPY VARCHAR2,
4777 x_upper_spec_limit OUT NOCOPY VARCHAR2,
4778 x_lower_user_defined_limit OUT NOCOPY VARCHAR2,
4779 x_upper_user_defined_limit OUT NOCOPY VARCHAR2,
4780 x_lower_reasonable_limit OUT NOCOPY VARCHAR2,
4781 x_upper_reasonable_limit OUT NOCOPY VARCHAR2) IS
4782
4783 CURSOR c IS
4784 SELECT
4785 target_value,
4786 lower_spec_limit,
4787 upper_spec_limit,
4788 lower_user_defined_limit,
4789 upper_user_defined_limit,
4790 lower_reasonable_limit,
4791 upper_reasonable_limit
4792 FROM qa_spec_chars
4793 WHERE spec_id = x_spec_id
4794 AND char_id = x_char_id;
4795
4796 BEGIN
4797
4798 OPEN c;
4799 FETCH c INTO x_target_value,
4800 x_lower_spec_limit,
4801 x_upper_spec_limit,
4802 x_lower_user_defined_limit,
4803 x_upper_user_defined_limit,
4804 x_lower_reasonable_limit,
4805 x_upper_reasonable_limit;
4806 CLOSE c;
4807
4808 x_target_value := nvl(x_target_value, ' ');
4809 x_lower_spec_limit := nvl(x_lower_spec_limit, ' ');
4810 x_upper_spec_limit := nvl(x_upper_spec_limit, ' ');
4811 x_lower_user_defined_limit := nvl(x_lower_user_defined_limit, ' ');
4812 x_upper_user_defined_limit := nvl(x_upper_user_defined_limit, ' ');
4813 x_lower_reasonable_limit := nvl(x_lower_reasonable_limit, ' ');
4814 x_upper_reasonable_limit := nvl(x_upper_reasonable_limit, ' ');
4815
4816 END get_spec_details;
4817
4818
4819 PROCEDURE get_spec_sub_type (x_spec_id IN NUMBER,
4820 x_element_name OUT NOCOPY VARCHAR2) IS
4821
4822 CURSOR c IS
4823 SELECT name
4824 FROM qa_chars
4825 WHERE char_id =
4826 ( SELECT char_id
4827 FROM qa_specs
4828 WHERE spec_id = x_spec_id );
4829
4830 BEGIN
4831
4832 OPEN c;
4833 FETCH c INTO x_element_name;
4834 CLOSE c;
4835
4836 x_element_name := nvl(x_element_name, ' ');
4837
4838 END get_spec_sub_type;
4839
4840
4841 PROCEDURE get_spec_type (p_spec_id IN NUMBER,
4842 x_spec_type OUT NOCOPY VARCHAR2) IS
4843
4844 CURSOR c IS
4845 SELECT meaning
4846 FROM mfg_lookups
4847 WHERE lookup_type = 'QA_SPEC_TYPE'
4848 AND lookup_code =
4849 ( SELECT assignment_type
4850 FROM qa_specs
4851 WHERE spec_id = p_spec_id );
4852
4853 BEGIN
4854
4855 OPEN c;
4856 FETCH c INTO x_spec_type;
4857 CLOSE c;
4858
4859 END get_spec_type;
4860
4861
4862 PROCEDURE get_item_name (p_spec_id IN NUMBER,
4863 x_item_name OUT NOCOPY VARCHAR2) IS
4864
4865 CURSOR c IS
4866 SELECT organization_id, item_id
4867 FROM qa_specs
4868 WHERE spec_id = p_spec_id;
4869
4870 l_org_id NUMBER;
4871 l_item_id NUMBER;
4872
4873 BEGIN
4874
4875 OPEN c;
4876 FETCH c INTO l_org_id, l_item_id;
4877 CLOSE c;
4878
4879 x_item_name := QA_FLEX_UTIL.item(l_org_id, l_item_id);
4880
4881 END get_item_name;
4882
4883
4884 PROCEDURE get_supplier_name (p_spec_id IN NUMBER,
4885 x_supplier_name OUT NOCOPY VARCHAR2) IS
4886
4887 CURSOR c IS
4888 SELECT vendor_name
4889 FROM po_vendors
4890 WHERE vendor_id =
4891 ( SELECT vendor_id
4892 FROM qa_specs
4893 WHERE spec_id = p_spec_id );
4894
4895 BEGIN
4896
4897 OPEN c;
4898 FETCH c INTO x_supplier_name;
4899 CLOSE c;
4900
4901 END get_supplier_name;
4902
4903
4904 PROCEDURE get_customer_name (p_spec_id IN NUMBER,
4905 x_customer_name OUT NOCOPY VARCHAR2) IS
4906
4907 CURSOR c IS
4908 SELECT customer_name
4909 FROM qa_customers_lov_v
4910 WHERE customer_id =
4911 ( SELECT customer_id
4912 FROM qa_specs
4913 WHERE spec_id = p_spec_id );
4914
4915 BEGIN
4916
4917 OPEN c;
4918 FETCH c INTO x_customer_name;
4919 CLOSE c;
4920
4921 END get_customer_name;
4922
4923
4924 FUNCTION context_element (element_id IN NUMBER, txn_number IN NUMBER)
4925 RETURN BOOLEAN IS
4926
4927 result BOOLEAN;
4928 dummy NUMBER;
4929
4930 CURSOR c IS
4931 SELECT 1
4932 FROM qa_txn_collection_triggers qtct
4933 WHERE qtct.transaction_number = txn_number
4934 AND qtct.collection_trigger_id = element_id;
4935
4936 BEGIN
4937
4938 -- This function determines if collection element is a context element
4939 -- given a transaction number.
4940
4941 OPEN c;
4942 FETCH c INTO dummy;
4943 result := c%FOUND;
4944 CLOSE c;
4945
4946 RETURN result;
4947
4948 END context_element;
4949
4950 --dgupta: Start R12 EAM Integration. Bug 4345492
4951 FUNCTION get_asset_instance_id (value IN VARCHAR2)
4952 RETURN NUMBER IS
4953
4954 id NUMBER;
4955 --Instance Number was already validated for eam restrictions
4956 --Following sql returns 1 row as there is unique index on instance_number
4957 CURSOR c (i_num VARCHAR2) IS
4958 SELECT cii.instance_id
4959 FROM csi_item_instances cii
4960 WHERE cii.instance_number = i_num;
4961
4962 BEGIN
4963
4964 IF value IS NULL THEN
4965 RETURN NULL;
4966 END IF;
4967
4968 OPEN c(value);
4969 FETCH c INTO id;
4970 CLOSE c;
4971
4972 RETURN id;
4973
4974 END get_asset_instance_id;
4975
4976 FUNCTION get_asset_instance_id (p_asset_group_id IN NUMBER, p_asset_number IN VARCHAR2)
4977 RETURN NUMBER IS
4978
4979 id NUMBER;
4980 --Asset Group and Asset Number were already validated for eam restrictions
4981 CURSOR c (org_id NUMBER, asset_group VARCHAR2) IS
4982 SELECT cii.instance_id
4983 FROM csi_item_instances cii
4984 WHERE cii.inventory_item_id = p_asset_group_id
4985 AND cii.serial_number = p_asset_number; --inv id and serial num combo is unique
4986
4987 BEGIN
4988
4989 IF ((p_asset_group_id IS NULL) OR (p_asset_number is NULL)) THEN
4990 RETURN NULL;
4991 END IF;
4992
4993 OPEN c(p_asset_group_id, p_asset_number);
4994 FETCH c INTO id;
4995 CLOSE c;
4996
4997 RETURN id;
4998
4999 END get_asset_instance_id;
5000
5001
5002 FUNCTION get_asset_group_id (org_id IN NUMBER, value IN VARCHAR2)
5003 RETURN NUMBER IS
5004
5005 id NUMBER;
5006 --rownum=1 =>better performance since all rows have same inventory_item_id
5007 CURSOR c (o_id NUMBER, a_group VARCHAR2) IS
5008 SELECT msikfv.inventory_item_id
5009 FROM mtl_system_items_b_kfv msikfv, mtl_parameters mp
5010 WHERE msikfv.organization_id = mp.organization_id
5011 and msikfv.eam_item_type in (1,3)
5012 and mp.maint_organization_id = o_id
5013 and msikfv.concatenated_segments = a_group
5014 and rownum=1;
5015
5016 BEGIN
5017
5018 IF value IS NULL THEN
5019 RETURN NULL;
5020 END IF;
5021
5022 OPEN c(org_id, value);
5023 FETCH c INTO id;
5024 CLOSE c;
5025
5026 RETURN id;
5027
5028 END get_asset_group_id;
5029
5030 --dgupta: End R12 EAM Integration. Bug 4345492
5031
5032 -- Bug 4519558. OA Framework integration project. UT bug fix.
5033 -- Transaction type element was erroring out for WIP transactions.
5034 -- New function to validate "Transaction Type".
5035 -- srhariha.Tue Aug 2 00:43:07 PDT 2005.
5036 FUNCTION validate_transaction_type(p_transaction_number IN NUMBER,
5037 p_org_id IN NUMBER,
5038 p_user_id IN NUMBER,
5039 p_value IN VARCHAR2)
5040 RETURN BOOLEAN IS
5041
5042 CURSOR C4 IS
5043 SELECT 1
5044 FROM MTL_TRANSACTION_TYPES
5045 WHERE transaction_source_type_id = 5
5046 AND transaction_action_id in (31,32)
5047 AND transaction_type_name = p_value;
5048
5049 CURSOR C22 IS
5050 SELECT 1
5051 FROM MTL_TRANSACTION_TYPES
5052 WHERE transaction_source_type_id = 5
5053 AND transaction_action_id in (30,31,32)
5054 AND transaction_type_name = p_value;
5055
5056 CURSOR C1 IS
5057 SELECT 1
5058 FROM MFG_LOOKUPS
5059 WHERE lookup_type ='WIP_MOVE_TRANSACTION_TYPE'
5060 AND meaning = p_value;
5061
5062 CURSOR C_MOBILE(p_lookup_type VARCHAR2) IS
5063 SELECT 1
5064 FROM QA_LOOKUPS
5065 WHERE lookup_type = p_lookup_type
5066 AND lookup_code = p_value;
5067
5068 l_temp NUMBER;
5069 result BOOLEAN;
5070 sql_string QA_CHARS.SQL_VALIDATION_STRING%TYPE;
5071
5072 BEGIN
5073 IF p_transaction_number = qa_ss_const.wip_completion_txn THEN
5074 OPEN C4;
5075 FETCH C4 INTO l_temp;
5076 result := C4%FOUND;
5077 CLOSE C4;
5078
5079 ELSIF p_transaction_number = qa_ss_const.flow_work_order_less_txn THEN
5080 OPEN C22;
5081 FETCH C22 INTO l_temp;
5082 result := C22%FOUND;
5083 CLOSE C22;
5084
5085 ELSIF p_transaction_number = qa_ss_const.wip_move_txn THEN
5086 OPEN C1;
5087 FETCH C1 INTO l_temp;
5088 result := C1%FOUND;
5089 CLOSE C1;
5090
5091 -- Bug 4519558.OA Framework Integration project. UT bug fix.
5092 -- Incorporating Bryan's code review comments. Use new
5093 -- method in qa_mqa_mwa_api package.
5094 -- srhariha. Mon Aug 22 02:50:35 PDT 2005.
5095
5096 ELSIF qa_mqa_mwa_api.is_mobile_txn(p_transaction_number) = fnd_api.g_true THEN
5097 OPEN C_MOBILE(qa_ss_const.mob_txn_lookup_prefix || to_char(p_transaction_number));
5098 FETCH C_MOBILE INTO l_temp;
5099 result := C_MOBILE%FOUND;
5100 CLOSE C_MOBILE;
5101
5102 ELSE
5103
5104 sql_string := get_sql_validation_string(qa_ss_const.transaction_type);
5105
5106
5107 sql_string := qa_chars_api.format_sql_for_validation (sql_string,
5108 p_org_id, p_user_id);
5109
5110 IF value_in_sql (sql_string, p_value) THEN
5111 result := TRUE;
5112 ELSE
5113 result := FALSE;
5114 END IF;
5115
5116 END IF;
5117
5118 RETURN result;
5119
5120 END validate_transaction_type;
5121
5122 -- bug 5186397
5123 -- new function to perform the UOM conversion
5124 -- This will call the INV api to convert
5125 -- the source value passed from the source
5126 -- UOM to the Target UOM
5127 -- SHKALYAN 01-May-2006
5128 --
5129 FUNCTION perform_uom_conversion (p_source_val IN VARCHAR2,
5130 p_precision IN NUMBER ,
5131 p_source_UOM IN VARCHAR2,
5132 p_target_UOM IN VARCHAR2)
5133 RETURN NUMBER AS
5134 converted_value NUMBER;
5135 BEGIN
5136 converted_value := INV_CONVERT.INV_UM_CONVERT
5137 (null,
5138 p_precision,
5139 to_number(p_source_val),
5140 p_source_UOM,
5141 p_target_UOM,
5142 null,
5143 null);
5144
5145 RETURN converted_value;
5146 END perform_uom_conversion;
5147
5148 --
5149 -- Bug 5383667
5150 -- New function to get the
5151 -- Id Values from QA_results table
5152 -- ntungare Thu Aug 24 02:02:38 PDT 2006
5153 --
5154 Function get_id_val(p_child_char_id IN NUMBER,
5155 p_plan_id IN NUMBER,
5156 p_collection_id IN NUMBER,
5157 p_occurrence IN NUMBER)
5158 RETURN VARCHAR2 AS
5159 id_val NUMBER;
5160 str VARCHAR2(2000);
5161 BEGIN
5162 -- bug 6129280
5163 -- Added to fetch locator_id, comp_locator_id
5164 -- and to_locator_id and process it
5165 -- bhsankar Tue Jul 17 02:35:19 PDT 2007
5166 --
5167 -- bug 6132613
5168 -- Modified to fetch Ids for RMA number
5169 -- ntungare Tue Jul 17 22:54:27 PDT 2007
5170 --
5171 If (p_child_char_id = qa_ss_const.party_name OR
5172 p_child_char_id = qa_ss_const.po_number OR
5173 p_child_char_id = qa_ss_const.locator OR
5174 p_child_char_id = qa_ss_const.comp_locator OR
5175 p_child_char_id = qa_ss_const.to_locator OR
5176 p_child_char_id = qa_ss_const.rma_number ) THEN
5177 str := 'Select '|| qa_chars_api.hardcoded_column(p_child_char_id)||
5178 ' from qa_results '||
5179 ' where plan_id = :p_plan_id '||
5180 ' and collection_id = :p_collection_id '||
5181 ' and occurrence = :p_occurrence';
5182
5183 Execute Immediate str
5184 INTO id_val
5185 USING p_plan_id, p_collection_id, p_occurrence;
5186
5187 If id_val IS NOT NULL THEN
5188 RETURN id_val;
5189 End If;
5190 End if;
5191
5192 Return NULL;
5193 END get_id_val;
5194
5195 -- bug 6263809
5196 -- New function to get the quantity received for
5197 -- a particular shipment in a receipt.
5198 -- This is needed for LPN Inspections wherein
5199 -- if there is a shipment number collection element
5200 -- then the quantity validation should happen
5201 -- based on it.
5202 -- bhsankar Fri Oct 12 03:06:24 PDT 2007
5203 --
5204 PROCEDURE get_qty_for_shipment(
5205 p_po_num IN VARCHAR2,
5206 p_line_num IN NUMBER,
5207 p_ship_num IN NUMBER,
5208 x_qty OUT NOCOPY NUMBER) IS
5209
5210 CURSOR C1 IS
5211 SELECT (pll.quantity_received - (pll.quantity_accepted + pll.quantity_rejected)) quantity_received
5212 FROM po_headers_all ph,
5213 po_line_locations_all pll,
5214 po_lines_all pl
5215 WHERE pll.po_header_id = ph.po_header_id
5216 AND pll.po_line_id = pl.po_line_id
5217 AND pll.shipment_num = p_ship_num
5218 AND pl.line_num = p_line_num
5219 AND ph.segment1 = p_po_num;
5220 BEGIN
5221 OPEN C1;
5222 FETCH C1 INTO x_qty;
5223 CLOSE C1;
5224
5225 IF x_qty is null THEN
5226 x_qty := -1;
5227 END IF;
5228 END get_qty_for_shipment;
5229
5230 -- 12.1 QWB Usability Improvements
5231 -- Method to set the flags for the depenedent elements
5232 PROCEDURE set_dep_element_flag (elements IN qa_validation_api.ElementsArray,
5233 charId IN NUMBER,
5234 condition IN BOOLEAN,
5235 dep_elements_list IN OUT NOCOPY string_list,
5236 dep_flag_list IN OUT NOCOPY string_list) AS
5237
5238 enable_flag CONSTANT VARCHAR2(1) := 'E';
5239 disable_flag CONSTANT VARCHAR2(1) := 'D';
5240 status VARCHAR2(1);
5241 BEGIN
5242 IF (condition = TRUE) THEN
5243 status := enable_flag;
5244 ELSE
5245 status := disable_flag;
5246 END If;
5247
5248 If elements.exists(charId) then
5249 dep_elements_list(NVL(dep_elements_list.LAST,0)+1) := charId;
5250 dep_flag_list(NVL(dep_flag_list.LAST,0)+1) := status;
5251 End If;
5252 END;
5253
5254 -- 12.1 QWB Usability Improvements Project
5255 -- This method has been copied from the file
5256 -- INVCORE.pld. INV team doesn't have an equivalent API
5257 -- to do the processing in PL/SQL since the currrent
5258 -- processing is restricted to Forms UI only.
5259 --
5260 FUNCTION NO_NEG_BALANCE(restrict_flag IN Number,
5261 neg_flag IN Number,
5262 action IN Number)
5263 return boolean IS
5264
5265 VALUE VARCHAR2(1);
5266 DO_NOT BOOLEAN;
5267 BEGIN
5268 if (restrict_flag = 2 or restrict_flag IS NULL) then
5269 if (neg_flag = 2) THEN
5270 if (action = 1 OR action = 2 or action = 3 or
5271 action = 21 or action = 30 or action = 32) then
5272 DO_NOT := TRUE;
5273 else
5274 DO_NOT := FALSE;
5275 end if;
5276 else
5277 DO_NOT := FALSE;
5278 end if;
5279 elsif (restrict_flag = 1) then
5280 DO_NOT := TRUE;
5281
5282 else
5283 /*
5284 VALUE := restrict_flag;
5285 app_exception.invalid_argument('LOCATOR.NO_NEG_BALANCE',
5286 'RESTRICT_FLAG',
5287 VALUE);*/
5288 NULL;
5289 end if;
5290 return DO_NOT;
5291 END NO_NEG_BALANCE;
5292
5293 -- 12.1 QWB Usability Improvements Project
5294 -- This method has been copied from the file
5295 -- INVCORE.pld. INV team doesn't have an equivalent API
5296 -- to do the processing in PL/SQL since the currrent
5297 -- processing is restricted to Forms UI only.
5298 --
5299 FUNCTION CONTROL(org_control IN number,
5300 sub_control IN number,
5301 item_control IN number default NULL,
5302 restrict_flag IN Number default NULL,
5303 Neg_flag IN Number default NULL,
5304 action IN Number default NULL)
5305 return number is
5306 VALUE VARCHAR2(1);
5307 locator_control number;
5308 begin
5309
5310 if (org_control = 1) then
5311 locator_control := 1;
5312 elsif (org_control = 2) then
5313 locator_control := 2;
5314 elsif (org_control = 3) then
5315 locator_control := 3;
5316 if (no_neg_balance(restrict_flag,
5317 neg_flag,action)) then
5318 locator_control := 2;
5319 end if;
5320 elsif (org_control = 4) then
5321 if (sub_control = 1) then
5322 locator_control := 1;
5323 elsif (sub_control = 2) then
5324 locator_control := 2;
5325 elsif (sub_control = 3) then
5326 locator_control := 3;
5327 if (no_neg_balance(restrict_flag,
5328 neg_flag,action)) then
5329 locator_control := 2;
5330 end if;
5331 elsif (sub_control = 5) then
5332 if (item_control = 1) then
5333 locator_control := 1;
5334 elsif (item_control = 2) then
5335 locator_control := 2;
5336 elsif (item_control = 3) then
5337 locator_control := 3;
5338 if (no_neg_balance(restrict_flag,
5339 neg_flag,action)) then
5340 locator_control := 2;
5341 end if;
5342 elsif (item_control IS NULL) then
5343 locator_control := sub_control;
5344 else
5345 /*
5346 VALUE := item_control;
5347 app_exception.invalid_argument('LOCATOR.CONTROL',
5348 'ITEM_LOCATOR_CONTROL',
5349 VALUE);
5350 */NULL;
5351 end if;
5352 else
5353 /*
5354 VALUE := sub_control;
5355 app_exception.invalid_argument('LOCATOR.CONTROL',
5356 'SUB_LOCATOR_CONTROL',
5357 VALUE);*/
5358 NULL;
5359 end if;
5360 else
5361 /*
5362 VALUE := org_control;
5363 app_exception.invalid_argument('LOCATOR.CONTROL',
5364 'ORG_LOCATOR_CONTROL',
5365 VALUE);*/
5366 NULL;
5367 end if;
5368 return locator_control;
5369 END CONTROL;
5370
5371 --
5372 -- 12.1 QWB Usability Improvements
5373 -- Procedure that sets the dependeny rules between different elements
5374 --
5375 PROCEDURE enable_disable_dep_elements (elements IN qa_validation_api.ElementsArray,
5376 charId IN NUMBER,
5377 plan_id IN NUMBER,
5378 org_id IN NUMBER,
5379 dependent_elements OUT NOCOPY VARCHAR2,
5380 disable_enable_flag_list OUT NOCOPY VARCHAR2)
5381 AS
5382
5383 dep_elements_list string_list ;
5384 dep_flag_list string_list ;
5385 enable_flag CONSTANT VARCHAR2(1) := 'E';
5386 disable_flag CONSTANT VARCHAR2(1) := 'D';
5387
5388 l_restrict_subinventories_code NUMBER;
5389 l_restrict_locators_code NUMBER;
5390 l_location_control_code NUMBER;
5391 l_revision_qty_control_code NUMBER;
5392 l_serial_number_control_code NUMBER;
5393 l_lot_control_code NUMBER;
5394 l_primary_uom_code VARCHAR2(20);
5395
5396 X_LOC_CNTRL NUMBER;
5397
5398 enable_contition BOOLEAN;
5399
5400 CURSOR locator is
5401 SELECT stock_locator_control_code,
5402 negative_inv_receipt_code
5403 FROM mtl_parameters
5404 WHERE organization_id = org_id;
5405
5406 x_org_loc_control NUMBER;
5407 x_neg_inv NUMBER;
5408
5409 Cursor sub_loc_cur (p_subinv_name IN VARCHAR2) IS
5410 SELECT locator_type
5411 FROM mtl_secondary_inventories
5412 WHERE organization_id = org_id
5413 AND nvl(disable_date, sysdate + 1) > sysdate
5414 AND secondary_inventory_name = p_subinv_name;
5415
5416 l_sub_loc_type NUMBER := 1;
5417
5418 CURSOR c (p_item_id IN NUMBER) IS
5419 SELECT
5420 msi.restrict_subinventories_code,
5421 msi.restrict_locators_code,
5422 msi.location_control_code,
5423 msi.revision_qty_control_code,
5424 msi.serial_number_control_code,
5425 msi.lot_control_code,
5426 msi.primary_uom_code
5427 FROM
5428 mtl_system_items msi
5429 WHERE msi.organization_id = org_id AND
5430 msi.inventory_item_id = p_item_id;
5431
5432 cascaded_dep_elements_list VARCHAR2(32000);
5433 cascaded_dep_flag_list VARCHAR2(32000);
5434 disabled_elements qa_validation_api.ElementsArray;
5435 BEGIN
5436 -- build the logic to fetch the dependent elements to be enabled
5437 -- based on the lines of the code present in QLTRES.pld
5438 --
5439
5440 --
5441 -- bug 7191632
5442 -- Removed the Hard dependency between the Production Line
5443 -- Item collection elements. This dependency would be
5444 -- established using LOVs
5445 -- ntungare
5446 --
5447 -- Process the element Production Line
5448 /*
5449 If (charId = 20) then
5450 -- enable Item
5451 set_dep_element_flag(elements,
5452 10,
5453 (elements(charId).value IS NOT NULL),
5454 dep_elements_list,
5455 dep_flag_list);
5456 */
5457
5458 -- Process the element Item
5459 IF (charId = 10) then
5460 OPEN c (elements(charId).id);
5461 FETCH c INTO
5462 l_restrict_subinventories_code ,
5463 l_restrict_locators_code ,
5464 l_location_control_code ,
5465 l_revision_qty_control_code ,
5466 l_serial_number_control_code ,
5467 l_lot_control_code ,
5468 l_primary_uom_code;
5469 CLOSE c;
5470
5471 -- enable UOM
5472 set_dep_element_flag(elements,
5473 12,
5474 (elements(charId).value IS NOT NULL),
5475 dep_elements_list,
5476 dep_flag_list);
5477
5478 --enable revision
5479 set_dep_element_flag(elements,
5480 13,
5481 ((elements(charId).value IS NOT NULL) AND
5482 (l_revision_qty_control_code = 2)),
5483 dep_elements_list,
5484 dep_flag_list);
5485
5486 --enable Lot Number
5487 set_dep_element_flag(elements,
5488 16,
5489 ((elements(charId).value IS NOT NULL) AND
5490 (l_lot_control_code <> 1)),
5491 dep_elements_list,
5492 dep_flag_list);
5493
5494 --enable Serial Number
5495 set_dep_element_flag(elements,
5496 17,
5497 ((elements(charId).value IS NOT NULL) AND
5498 (l_serial_number_control_code <> 1)),
5499 dep_elements_list,
5500 dep_flag_list);
5501
5502 --enable Subinventory
5503 set_dep_element_flag(elements,
5504 14,
5505 (elements(charId).value IS NOT NULL),
5506 dep_elements_list,
5507 dep_flag_list);
5508
5509 --enable To Subinventory
5510 set_dep_element_flag(elements,
5511 2147483628,
5512 (elements(charId).value IS NOT NULL),
5513 dep_elements_list,
5514 dep_flag_list);
5515
5516 -- Process element Lot number
5517 ELSIF (charId = 16) then
5518 --enable Lot Status
5519 set_dep_element_flag(elements,
5520 188,
5521 (elements(charId).value IS NOT NULL),
5522 dep_elements_list,
5523 dep_flag_list);
5524
5525 -- Process element Serial Number
5526 ELSIF charId = 17 then
5527 --enable Serial Status
5528 set_dep_element_flag(elements,
5529 189,
5530 (elements(charId).value IS NOT NULL),
5531 dep_elements_list,
5532 dep_flag_list);
5533
5534 -- Process element Subinventory
5535 ELSIF charId = 14 then
5536 --enable Locator
5537 OPEN locator;
5538 FETCH locator INTO x_org_loc_control,
5539 x_neg_inv;
5540 CLOSE locator;
5541
5542 OPEN sub_loc_cur(elements(charId).value);
5543 FETCH sub_loc_cur INTO l_sub_loc_type;
5544 CLOSE sub_loc_cur;
5545
5546 If (elements.exists(10)) then
5547 OPEN c (elements(10).id);
5548 FETCH c INTO
5549 l_restrict_subinventories_code ,
5550 l_restrict_locators_code ,
5551 l_location_control_code ,
5552 l_revision_qty_control_code ,
5553 l_serial_number_control_code ,
5554 l_lot_control_code ,
5555 l_primary_uom_code;
5556 CLOSE c;
5557 Else
5558 l_restrict_subinventories_code := NULL;
5559 l_restrict_locators_code := NULL;
5560 l_location_control_code := NULL;
5561 l_revision_qty_control_code := NULL;
5562 l_serial_number_control_code := NULL;
5563 l_lot_control_code := NULL;
5564 l_primary_uom_code := NULL;
5565 End If;
5566
5567 IF (elements(charId).value IS NULL) THEN
5568 x_loc_cntrl := 1;
5569 Else
5570 x_loc_cntrl := CONTROL(
5571 ORG_CONTROL => x_org_loc_control,
5572 SUB_CONTROL => l_sub_loc_type,
5573 ITEM_CONTROL => l_location_control_code,
5574 RESTRICT_FLAG => l_restrict_locators_code,
5575 NEG_FLAG => x_neg_inv);
5576 End If;
5577
5578 set_dep_element_flag(elements,
5579 15,
5580 ((elements(charId).value IS NOT NULL) AND
5581 (x_LOC_CNTRL <>1 AND X_LOC_CNTRL in (2,3))),
5582 dep_elements_list,
5583 dep_flag_list);
5584
5585 -- Process element To Subinventory
5586 ELSIF charId = 2147483628 then
5587 --enable To Locator
5588 OPEN locator;
5589 FETCH locator INTO x_org_loc_control,
5590 x_neg_inv;
5591 CLOSE locator;
5592
5593 OPEN sub_loc_cur (elements(charId).value) ;
5594 FETCH sub_loc_cur INTO l_sub_loc_type;
5595 CLOSE sub_loc_cur;
5596
5597 If (elements.exists(10)) then
5598 OPEN c (elements(10).id);
5599 FETCH c INTO
5600 l_restrict_subinventories_code ,
5601 l_restrict_locators_code ,
5602 l_location_control_code ,
5603 l_revision_qty_control_code ,
5604 l_serial_number_control_code ,
5605 l_lot_control_code ,
5606 l_primary_uom_code;
5607 CLOSE c;
5608 Else
5609 l_restrict_subinventories_code := NULL;
5610 l_restrict_locators_code := NULL;
5611 l_location_control_code := NULL;
5612 l_revision_qty_control_code := NULL;
5613 l_serial_number_control_code := NULL;
5614 l_lot_control_code := NULL;
5615 l_primary_uom_code := NULL;
5616 End If;
5617
5618 IF (elements(charId).value IS NULL) THEN
5619 x_loc_cntrl := 1;
5620 Else
5621 x_loc_cntrl := CONTROL(
5622 ORG_CONTROL => x_org_loc_control,
5623 SUB_CONTROL => l_sub_loc_type,
5624 ITEM_CONTROL => l_location_control_code,
5625 RESTRICT_FLAG => l_restrict_locators_code,
5626 NEG_FLAG => x_neg_inv);
5627 End If;
5628
5629 set_dep_element_flag(elements,
5630 2147483627,
5631 ((elements(charId).value IS NOT NULL) AND
5632 (x_LOC_CNTRL <>1 AND X_LOC_CNTRL in (2,3))),
5633 dep_elements_list,
5634 dep_flag_list);
5635
5636 -- Process element Job Name
5637 ELSIF charId = 19 then
5638 -- enable TO_OP_SEQ_NUM
5639 set_dep_element_flag(elements,
5640 21,
5641 (elements(charId).value IS NOT NULL),
5642 dep_elements_list,
5643 dep_flag_list);
5644
5645 --enable FROM_OP_SEQ_NUM
5646 set_dep_element_flag(elements,
5647 22,
5648 (elements(charId).value IS NOT NULL),
5649 dep_elements_list,
5650 dep_flag_list);
5651
5652 -- Process element TO_OP_SEQ_NUM
5653 ELSIF charId = 21 then
5654 -- enable TO_INTRAOPERATION_STEP
5655 set_dep_element_flag(elements,
5656 23,
5657 (elements(charId).value IS NOT NULL),
5658 dep_elements_list,
5659 dep_flag_list);
5660
5661 -- Process element FROM_OP_SEQ_NUM
5662 ELSIF charId = 22 then
5663 -- enable TO_INTRAOPERATION_STEP
5664 set_dep_element_flag(elements,
5665 24,
5666 (elements(charId).value IS NOT NULL),
5667 dep_elements_list,
5668 dep_flag_list);
5669
5670 -- Process element PO Number
5671 ELSIF charId = 27 then
5672 -- enable PO Release Number
5673 set_dep_element_flag(elements,
5674 110,
5675 (elements(charId).value IS NOT NULL),
5676 dep_elements_list,
5677 dep_flag_list);
5678
5679 -- enable PO Line Number
5680 set_dep_element_flag(elements,
5681 28,
5682 (elements(charId).value IS NOT NULL),
5683 dep_elements_list,
5684 dep_flag_list);
5685
5686 -- Process element PO Line Number
5687 ELSIF charId = 28 then
5688 -- enable PO Release Number
5689 set_dep_element_flag(elements,
5690 29,
5691 (elements(charId).value IS NOT NULL),
5692 dep_elements_list,
5693 dep_flag_list);
5694
5695 -- Process element Project Number
5696 ELSIF charId = 121 then
5697 -- enable PO Release Number
5698 set_dep_element_flag(elements,
5699 122,
5700 (elements(charId).value IS NOT NULL),
5701 dep_elements_list,
5702 dep_flag_list);
5703
5704 -- Process element Asset Group
5705 ELSIF charId = 162 then
5706 -- enable Asset Number
5707 set_dep_element_flag(elements,
5708 163,
5709 (elements(charId).value IS NOT NULL),
5710 dep_elements_list,
5711 dep_flag_list);
5712
5713 -- enable Asset Instance Number
5714 set_dep_element_flag(elements,
5715 2147483550,
5716 (elements(charId).value IS NOT NULL),
5717 dep_elements_list,
5718 dep_flag_list);
5719
5720 -- enable Asset Activity
5721 set_dep_element_flag(elements,
5722 164,
5723 (elements(charId).value IS NOT NULL),
5724 dep_elements_list,
5725 dep_flag_list);
5726
5727 -- Process element Asset Number
5728 ELSIF charId = 163 then
5729 -- enable Asset Activity
5730 set_dep_element_flag(elements,
5731 164,
5732 (elements(charId).value IS NOT NULL),
5733 dep_elements_list,
5734 dep_flag_list);
5735
5736 -- Process element PROCESS_BATCHSTEP_NUM
5737 ELSIF charId = 2147483555 then
5738 -- enable PROCESS_OPERATION
5739 set_dep_element_flag(elements,
5740 2147483554,
5741 (elements(charId).value IS NOT NULL),
5742 dep_elements_list,
5743 dep_flag_list);
5744
5745 -- Process element PROCESS_OPERATION
5746 ELSIF charId = 2147483554 then
5747 -- enable PROCESS_ACTIVITY
5748 set_dep_element_flag(elements,
5749 2147483553,
5750 (elements(charId).value IS NOT NULL),
5751 dep_elements_list,
5752 dep_flag_list);
5753
5754 -- Process element PROCESS_ACTIVITY
5755 ELSIF charId = 2147483553 then
5756 -- enable PROCESS_RESOURCE
5757 set_dep_element_flag(elements,
5758 2147483552,
5759 (elements(charId).value IS NOT NULL),
5760 dep_elements_list,
5761 dep_flag_list);
5762
5763 -- Process element PROCESS_RESOURCE
5764 ELSIF charId = 2147483552 then
5765 -- enable PROCESS_PARAMETER
5766 set_dep_element_flag(elements,
5767 2147483551,
5768 (elements(charId).value IS NOT NULL),
5769 dep_elements_list,
5770 dep_flag_list);
5771
5772 -- Process the element Comp Item
5773 ELSIF charId = 60 then
5774 OPEN c (elements(charId).id);
5775 FETCH c INTO
5776 l_restrict_subinventories_code ,
5777 l_restrict_locators_code ,
5778 l_location_control_code ,
5779 l_revision_qty_control_code ,
5780 l_serial_number_control_code ,
5781 l_lot_control_code ,
5782 l_primary_uom_code;
5783 CLOSE c;
5784
5785 -- enable COMP_UOM
5786 set_dep_element_flag(elements,
5787 62,
5788 (elements(charId).value IS NOT NULL),
5789 dep_elements_list,
5790 dep_flag_list);
5791
5792 --enable COMP_revision
5793 set_dep_element_flag(elements,
5794 63,
5795 ((elements(charId).value IS NOT NULL) AND
5796 (l_revision_qty_control_code = 2)),
5797 dep_elements_list,
5798 dep_flag_list);
5799
5800 --enable Comp Lot Number
5801 set_dep_element_flag(elements,
5802 66,
5803 ((elements(charId).value IS NOT NULL) AND
5804 (l_lot_control_code <> 1)),
5805 dep_elements_list,
5806 dep_flag_list);
5807
5808 --enable Comp Serial Number
5809 set_dep_element_flag(elements,
5810 67,
5811 ((elements(charId).value IS NOT NULL) AND
5812 (l_serial_number_control_code <> 1)),
5813 dep_elements_list,
5814 dep_flag_list);
5815
5816 --enable Subinventory
5817 set_dep_element_flag(elements,
5818 64,
5819 (elements(charId).value IS NOT NULL),
5820 dep_elements_list,
5821 dep_flag_list);
5822
5823 -- Process element Comp Subinventory
5824 ELSIF charId = 64 then
5825 --enable Comp Locator
5826 OPEN locator;
5827 FETCH locator INTO x_org_loc_control,
5828 x_neg_inv;
5829 CLOSE locator;
5830
5831 OPEN sub_loc_cur (elements(charId).value) ;
5832 FETCH sub_loc_cur INTO l_sub_loc_type;
5833 CLOSE sub_loc_cur;
5834
5835 If (elements.exists(60)) then
5836 OPEN c (elements(60).id);
5837 FETCH c INTO
5838 l_restrict_subinventories_code ,
5839 l_restrict_locators_code ,
5840 l_location_control_code ,
5841 l_revision_qty_control_code ,
5842 l_serial_number_control_code ,
5843 l_lot_control_code ,
5844 l_primary_uom_code;
5845 CLOSE c;
5846 Else
5847 l_restrict_subinventories_code := NULL;
5848 l_restrict_locators_code := NULL;
5849 l_location_control_code := NULL;
5850 l_revision_qty_control_code := NULL;
5851 l_serial_number_control_code := NULL;
5852 l_lot_control_code := NULL;
5853 l_primary_uom_code := NULL;
5854 End If;
5855
5856 IF (elements(charId).value IS NULL) THEN
5857 x_loc_cntrl := 1;
5858 Else
5859 x_loc_cntrl := CONTROL(
5860 ORG_CONTROL => x_org_loc_control,
5861 SUB_CONTROL => l_sub_loc_type,
5862 ITEM_CONTROL => l_location_control_code,
5863 RESTRICT_FLAG => l_restrict_locators_code,
5864 NEG_FLAG => x_neg_inv);
5865 End If;
5866
5867 set_dep_element_flag(elements,
5868 15,
5869 ((elements(charId).value IS NOT NULL) AND
5870 (x_LOC_CNTRL <>1 AND X_LOC_CNTRL in (2,3))),
5871 dep_elements_list,
5872 dep_flag_list);
5873
5874 END IF;
5875
5876 -- initializing the disabled elements array as
5877 -- equal to the elements array
5878 --
5879 disabled_elements := elements;
5880
5881 IF dep_elements_list.count <> 0 then
5882 FOR cntr in 1..dep_elements_list.count
5883 LOOP
5884 dependent_elements := dependent_elements ||','|| dep_elements_list(cntr);
5885 disable_enable_flag_list := disable_enable_flag_list ||','|| dep_flag_list(cntr);
5886
5887 -- If a collection element has been diasbled, all its
5888 -- dependent elements must also be disabled
5889 --
5890 IF (dep_flag_list(cntr) = disable_flag) THEN
5891 -- Since the element is to be disbaled. Hence setting the value as NULL.
5892 IF elements.exists(dep_elements_list(cntr)) THEN
5893 disabled_elements(dep_elements_list(cntr)).value := NULL;
5894 null;
5895 END IF;
5896
5897 enable_disable_dep_elements(disabled_elements,
5898 dep_elements_list(cntr),
5899 plan_id,
5900 org_id,
5901 cascaded_dep_elements_list,
5902 cascaded_dep_flag_list);
5903
5904 IF (cascaded_dep_elements_list IS NOT NULL) THEN
5905 dependent_elements := dependent_elements ||','|| cascaded_dep_elements_list;
5906 disable_enable_flag_list := disable_enable_flag_list || ',' || cascaded_dep_flag_list ;
5907 END IF;
5908 END IF;
5909 END LOOP;
5910 ELSE
5911 RETURN;
5912 END If;
5913
5914 dependent_elements := LTRIM(dependent_elements ,',');
5915 disable_enable_flag_list := LTRIM(disable_enable_flag_list, ',') ;
5916
5917 END enable_disable_dep_elements ;
5918
5919 -- 12.1 QWB Usability Improvemenets
5920 -- New procedure to process dependent elements
5921 PROCEDURE process_dependent_elements(result_string IN VARCHAR2,
5922 id_string IN VARCHAR2,
5923 org_id IN NUMBER,
5924 p_plan_id IN NUMBER,
5925 char_Id IN VARCHAR2,
5926 dependent_elements OUT NOCOPY VARCHAR2,
5927 disable_enable_flag_list OUT NOCOPY VARCHAR2)
5928 AS
5929 elements qa_validation_api.ElementsArray;
5930 char_cntr NUMBER;
5931 dep_elements_list VARCHAR2(4000);
5932 dep_flag_list VARCHAR2(4000);
5933
5934 l_result_string VARCHAR2(32767);
5935
5936 Cursor plan_chars_cur is
5937 select char_id from qa_plan_chars
5938 where plan_id = p_plan_id;
5939 BEGIN
5940 l_result_string := result_string;
5941
5942 -- Handling for NULL result string. In this case
5943 -- build a result string with all the elements
5944 -- set as NULL
5945 if (l_result_string IS NULL) THEN
5946 For rad in plan_chars_cur
5947 loop
5948 l_result_string := l_result_string||'@'||rad.char_id||'=';
5949 end loop;
5950 l_result_string := LTRIM(l_result_string,'@');
5951 end If;
5952
5953 -- Builid elements array
5954 elements := qa_validation_api.result_to_array(l_result_string);
5955 elements := qa_validation_api.id_to_array(id_string, elements);
5956
5957 -- If char id is NULL then the entire row is to be
5958 -- processed
5959 If char_id IS NULL THEN
5960 char_cntr := elements.first;
5961
5962 -- Process all elements
5963 while char_cntr <= elements.last
5964 loop
5965 -- Get the dependent elemnts list and the flags
5966 enable_disable_dep_elements(elements,
5967 char_cntr,
5968 p_plan_id,
5969 org_id,
5970 dep_elements_list,
5971 dep_flag_list);
5972
5973 if dep_elements_list is not null then
5974 dependent_elements := dependent_elements ||','||dep_elements_list;
5975 disable_enable_flag_list := disable_enable_flag_list||','||dep_flag_list;
5976 end if;
5977 char_cntr := elements.next(char_cntr);
5978 end loop;
5979
5980 -- If char id is NOT NULL then the specific element
5981 -- is to be processed
5982 Else
5983 -- Get the dependent elemnts list and the flags
5984 enable_disable_dep_elements(elements,
5985 char_Id,
5986 p_plan_id,
5987 org_id,
5988 dep_elements_list,
5989 dep_flag_list);
5990
5991 dependent_elements := dep_elements_list;
5992 disable_enable_flag_list := dep_flag_list;
5993 End If;
5994
5995 If dependent_elements IS NOT NULL THEN
5996 dependent_elements := LTRIM(dependent_elements ,',');
5997 disable_enable_flag_list := LTRIM(disable_enable_flag_list ,',');
5998 End If;
5999 END process_dependent_elements;
6000
6001 --
6002 -- 12.1 QWB Usabitlity Improvements
6003 -- Function to build the Info column value
6004 --
6005 FUNCTION build_info_column(p_plan_id IN NUMBER,
6006 p_collection_id IN NUMBER,
6007 p_occurrence IN NUMBER)
6008 RETURN VARCHAR2
6009 AS
6010
6011 Type hardcoded_char_tab_typ IS TABLE OF NUMBER INDEX BY binary_integer;
6012 hardcoded_char_tab hardcoded_char_tab_typ;
6013
6014 cols_str VARCHAR2(32767);
6015 char_ids_str VARCHAR2(32767);
6016
6017 plan_name VARCHAR2(100);
6018
6019 result_str VARCHAR2(32767);
6020
6021 char_name VARCHAR(100);
6022
6023 p_values_string VARCHAR2(32767) := NULL;
6024 BEGIN
6025 -- get the list of information columns
6026 --
6027 -- bug 7115965
6028 -- Added an order by clause
6029 -- ntungare
6030 --
6031 SELECT qpc.char_id bulk collect
6032 INTO hardcoded_char_tab
6033 FROM qa_plan_chars qpc
6034 WHERE qpc.plan_id = p_plan_id
6035 AND qpc.information_flag = 1
6036 ORDER BY prompt_sequence;
6037
6038 -- get the columns names corresponding to
6039 -- the information columns
6040 -- The processing is to be done only if there are any
6041 -- information columns.
6042 if hardcoded_char_tab.count <> 0 then
6043 FOR cntr IN 1 .. hardcoded_char_tab.COUNT
6044 LOOP
6045 -- get the column name to select from view
6046 Select upper(translate(name,' ''*{}','_____')) into char_name
6047 from qa_chars
6048 where char_id = hardcoded_char_tab(cntr);
6049
6050 -- build a list of columns to select from the
6051 -- plan view
6052 --
6053 -- bug 7115965
6054 -- Changed the separator from space to comma
6055 -- ntungare
6056 --
6057 cols_str := cols_str || char_name||'||'', ''||';
6058
6059 END LOOP;
6060
6061 -- build the plan view name
6062 SELECT deref_view_name INTO plan_name
6063 FROM qa_plans
6064 WHERE plan_id = p_plan_id;
6065
6066 --
6067 -- bug 7115965
6068 -- Changed the separator from space to comma
6069 -- ntungare
6070 --
6071 cols_str := RTRIM(cols_str, '||'', ''||');
6072
6073 -- Execute the dynamic query and get the information cols string
6074 EXECUTE IMMEDIATE 'Select ' || cols_str || ' from ' || plan_name ||
6075 ' where collection_id = :collection_id and
6076 occurrence = :occurrence'
6077 INTO p_values_string
6078 USING p_collection_id,
6079 p_occurrence;
6080 end if;
6081
6082 return p_values_string;
6083 END build_info_column;
6084
6085 END qa_plan_element_api;
[Home] [Help]