1 PACKAGE BODY qa_chars_api AS
2 /* $Header: qltcharb.plb 120.7.12020000.2 2012/07/03 14:41:06 ntungare ship $ */
3
4 --
5 -- Type definition. These are tables used to create internal
6 -- cache to improve performance. Any records retrieved will be
7 -- temporarily saved into these tables.
8 --
9
10 -- Bug 3769260. shkalyan 30 July 2004.
11 -- Added this record type for storing relevant QA elements.
12 -- This is needed as we need to select only specific columns
13 -- of QAelements instead of selecting * from QA_CHARS
14
15 TYPE qa_char_rec IS RECORD
16 (
17 char_id qa_chars.char_id%TYPE,
18 name qa_chars.name%TYPE,
19 prompt qa_chars.prompt%TYPE,
20 data_entry_hint qa_chars.data_entry_hint%TYPE,
21 datatype qa_chars.datatype%TYPE,
22 display_length qa_chars.display_length%TYPE,
23 decimal_precision qa_chars.decimal_precision%TYPE,
24 default_value qa_chars.default_value%TYPE,
25 mandatory_flag qa_chars.mandatory_flag%TYPE,
26 uom_code qa_chars.uom_code%TYPE,
27 target_value qa_chars.target_value%TYPE,
28 upper_spec_limit qa_chars.upper_spec_limit%TYPE,
29 lower_spec_limit qa_chars.lower_spec_limit%TYPE,
30 upper_reasonable_limit qa_chars.upper_reasonable_limit%TYPE,
31 lower_reasonable_limit qa_chars.lower_reasonable_limit%TYPE,
32 upper_user_defined_limit qa_chars.upper_user_defined_limit%TYPE,
33 lower_user_defined_limit qa_chars.lower_user_defined_limit%TYPE,
34 hardcoded_column qa_chars.hardcoded_column%TYPE,
35 developer_name qa_chars.developer_name%TYPE,
36 sql_validation_string qa_chars.sql_validation_string%TYPE,
37 enabled_flag qa_chars.enabled_flag%TYPE,
38 values_exist_flag qa_chars.values_exist_flag%TYPE,
39 fk_lookup_type qa_chars.fk_lookup_type%TYPE,
40 fk_meaning qa_chars.fk_meaning%TYPE
41 );
42
43 TYPE qa_chars_table IS TABLE OF qa_char_rec INDEX BY BINARY_INTEGER;
44 --TYPE qa_chars_table IS TABLE OF qa_chars%ROWTYPE INDEX BY BINARY_INTEGER;
45
46 x_qa_chars_array qa_chars_table;
47
48 --
49 -- All the fetch_... procedures are auxiliary caching functions
50 -- called only by inquiry APIs that return the object's attributes.
51 --
52
53 FUNCTION exists_qa_chars(element_id IN NUMBER) RETURN BOOLEAN IS
54
55 BEGIN
56
57 RETURN x_qa_chars_array.EXISTS(element_id);
58
59 END exists_qa_chars;
60
61
62 PROCEDURE fetch_qa_chars (element_id IN NUMBER) IS
63
64 -- Bug 3769260. shkalyan 30 July 2004.
65 -- Modified cursor to select only specific columns
66 -- of QA elements instead of selecting * from QA_CHARS
67
68 CURSOR C1 (e_id NUMBER) IS
69 SELECT char_id,
70 name,
71 prompt,
72 data_entry_hint,
73 datatype,
74 display_length,
75 decimal_precision,
76 default_value,
77 mandatory_flag,
78 uom_code,
79 target_value,
80 upper_spec_limit,
81 lower_spec_limit,
82 upper_reasonable_limit,
83 lower_reasonable_limit,
84 upper_user_defined_limit,
85 lower_user_defined_limit,
86 hardcoded_column,
87 developer_name,
88 sql_validation_string,
89 enabled_flag,
90 values_exist_flag,
91 fk_lookup_type,
92 fk_meaning
93 FROM qa_chars
94 WHERE char_id = e_id;
95
96 BEGIN
97
98 IF NOT exists_qa_chars(element_id) THEN
99 OPEN C1(element_id);
100 FETCH C1 INTO x_qa_chars_array(element_id);
101 CLOSE C1;
102 END IF;
103
104 END fetch_qa_chars;
105
106
107 -- Bug 3769260. shkalyan 30 July 2004.
108 -- Added this procedure to fetch all the elements of a plan
109 -- The reason for introducing this procedure is to reduce the number of
110 -- hits on the QA_CHARS.
111 -- Callers will use this procedure to pre-fetch all the Plan elements
112 -- to the cache if all the elements of a plan would be accessed.
113
114 PROCEDURE fetch_plan_chars (plan_id IN NUMBER) IS
115
116 l_char_rec qa_char_rec;
117
118 CURSOR C1 (p_id NUMBER) IS
119 SELECT QC.char_id,
120 QC.name,
121 QC.prompt,
122 QC.data_entry_hint,
123 QC.datatype,
124 QC.display_length,
125 QC.decimal_precision,
126 QC.default_value,
127 QC.mandatory_flag,
128 QC.uom_code,
129 QC.target_value,
130 QC.upper_spec_limit,
131 QC.lower_spec_limit,
132 QC.upper_reasonable_limit,
133 QC.lower_reasonable_limit,
134 QC.upper_user_defined_limit,
135 QC.lower_user_defined_limit,
136 QC.hardcoded_column,
137 QC.developer_name,
138 QC.sql_validation_string,
139 QC.enabled_flag,
140 QC.values_exist_flag,
141 QC.fk_lookup_type,
142 QC.fk_meaning
143 FROM qa_chars QC,
144 qa_plan_chars QPC
145 WHERE QC.char_id = QPC.char_id
146 AND QPC.plan_id = p_id
147 AND QPC.enabled_flag = 1;
148
149 BEGIN
150
151 OPEN C1(plan_id);
152 LOOP
153 FETCH C1 INTO l_char_rec;
154 EXIT WHEN C1%NOTFOUND;
155
156 IF NOT exists_qa_chars(l_char_rec.char_id) THEN
157 x_qa_chars_array(l_char_rec.char_id) := l_char_rec;
158 END IF;
159 END LOOP;
160 CLOSE C1;
161
162 END fetch_plan_chars;
163
164 FUNCTION hardcoded_column(element_id IN NUMBER)
165 RETURN VARCHAR2 IS
166
167 BEGIN
168
169 fetch_qa_chars(element_id);
170 IF NOT exists_qa_chars(element_id) THEN
171 RETURN NULL;
172 END IF;
173 RETURN x_qa_chars_array(element_id).hardcoded_column;
174
175 END hardcoded_column;
176
177
178 FUNCTION fk_meaning(element_id IN NUMBER)
179 RETURN VARCHAR2 IS
180
181 BEGIN
182
183 fetch_qa_chars(element_id);
184 IF NOT exists_qa_chars(element_id) THEN
185 RETURN NULL;
186 END IF;
187 RETURN x_qa_chars_array(element_id).fk_meaning;
188
189 END fk_meaning;
190
191
192 FUNCTION fk_lookup_type(element_id IN NUMBER)
193 RETURN NUMBER IS
194
195 BEGIN
196
197 fetch_qa_chars(element_id);
198 IF NOT exists_qa_chars(element_id) THEN
199 RETURN NULL;
200 END IF;
201 RETURN x_qa_chars_array(element_id).fk_lookup_type;
202
203 END fk_lookup_type;
204
205
206 FUNCTION sql_validation_string(element_id IN NUMBER)
207 RETURN VARCHAR2 IS
208
209 BEGIN
210
211 fetch_qa_chars(element_id);
212 IF NOT exists_qa_chars(element_id) THEN
213 RETURN NULL;
214 END IF;
215 RETURN x_qa_chars_array(element_id).sql_validation_string;
216
217 END sql_validation_string;
218
219
220 FUNCTION datatype(element_id IN NUMBER)
221 RETURN NUMBER IS
222
223 BEGIN
224
225 fetch_qa_chars(element_id);
226 IF NOT exists_qa_chars(element_id) THEN
227 RETURN NULL;
228 END IF;
229 RETURN x_qa_chars_array(element_id).datatype;
230
231 END datatype;
232
233
234 FUNCTION display_length(element_id IN NUMBER)
235 RETURN NUMBER IS
236
237 BEGIN
238
239 fetch_qa_chars(element_id);
240 IF NOT exists_qa_chars(element_id) THEN
241 RETURN NULL;
242 END IF;
243 RETURN x_qa_chars_array(element_id).display_length;
244
245 END display_length;
246
247
248 FUNCTION decimal_precision (element_id IN NUMBER)
249 RETURN NUMBER IS
250
251 BEGIN
252
253 fetch_qa_chars(element_id);
254 IF NOT exists_qa_chars(element_id) THEN
255 RETURN NULL;
256 END IF;
257 RETURN x_qa_chars_array(element_id).decimal_precision;
258
259 END decimal_precision;
260
261
262 FUNCTION default_value (element_id IN NUMBER)
263 RETURN NUMBER IS
264
265 BEGIN
266
267 fetch_qa_chars(element_id);
268 IF NOT exists_qa_chars(element_id) THEN
269 RETURN NULL;
270 END IF;
271 RETURN x_qa_chars_array(element_id).default_value;
272
273 END default_value;
274
278
275
276 FUNCTION lower_reasonable_limit(element_id IN NUMBER)
277 RETURN VARCHAR2 IS
279 BEGIN
280
281 fetch_qa_chars(element_id);
282 IF NOT exists_qa_chars(element_id) THEN
283 RETURN NULL;
284 END IF;
285 RETURN x_qa_chars_array(element_id).lower_reasonable_limit;
286
287 END lower_reasonable_limit;
288
289
290 FUNCTION upper_reasonable_limit(element_id IN NUMBER)
291 RETURN VARCHAR2 IS
292
293 BEGIN
294
295 fetch_qa_chars(element_id);
296 IF NOT exists_qa_chars(element_id) THEN
297 RETURN NULL;
298 END IF;
299 RETURN x_qa_chars_array(element_id).upper_reasonable_limit;
300
301 END upper_reasonable_limit;
302
303
304 FUNCTION prompt(element_id IN NUMBER)
305 RETURN VARCHAR2 IS
306
307 BEGIN
308
309 fetch_qa_chars(element_id);
310 IF NOT exists_qa_chars(element_id) THEN
311 RETURN NULL;
312 END IF;
313 RETURN x_qa_chars_array(element_id).prompt;
314
315 END prompt;
316
317
318 -- SSQR project. 07/29/2003.
319 FUNCTION data_entry_hint(element_id IN NUMBER)
320 RETURN VARCHAR2 IS
321
322 BEGIN
323
324 fetch_qa_chars(element_id);
325 IF NOT exists_qa_chars(element_id) THEN
326 RETURN NULL;
327 END IF;
328 RETURN x_qa_chars_array(element_id).data_entry_hint;
329
330 END data_entry_hint;
331
332
333 FUNCTION mandatory_flag(element_id IN NUMBER)
334 RETURN NUMBER IS
335
336 BEGIN
337
338 fetch_qa_chars(element_id);
339 IF NOT exists_qa_chars(element_id) THEN
340 RETURN NULL;
341 END IF;
342 RETURN x_qa_chars_array(element_id).mandatory_flag;
343
344 END mandatory_flag;
345
346 --
347 -- Bug 9773026
348 -- New function to remove the order by clause from the sql validation string.
349 -- This function would be called from format_sql_for_validation and also
350 -- qa_validation_api.validate_sql
351 -- skolluku
352 --
353 FUNCTION remove_order_by_from_sql (x_string VARCHAR2)
354 RETURN VARCHAR2 IS
355
356 new_string VARCHAR2(2500);
357 order_pos NUMBER;
358
359 BEGIN
360 -- allow trailing semi-colon and slash. Bug 956708.
361 -- bso
362 new_string := rtrim(x_string, ' ;/');
363
364 -- convert string to all uppercase for searching.
365 new_string := upper(new_string);
366
367 -- remove order by clause from string
368
369 order_pos := instr(new_string, 'ORDER BY');
370 IF (order_pos <> 0) THEN
371 new_string := SUBSTR(new_string, 1, order_pos - 1);
372 END IF;
373
374 RETURN new_string;
375 END remove_order_by_from_sql;
376
377 FUNCTION format_sql_for_validation (x_string VARCHAR2, x_org_id IN NUMBER,
378 x_created_by IN NUMBER)
379 RETURN VARCHAR2 IS
380
381 order_pos NUMBER;
382 new_string VARCHAR2(2500);
383 comma_pos NUMBER;
384 from_pos NUMBER;
385 who_created_by NUMBER;
386
387 BEGIN
388
389 -- NOTE: This function is a re-write of format_sql_validation_string
390 -- in the qlttrafb package with a lot of modiifcation. There is
391 -- is a similar function named format_sql_for_lov inthis package
392 -- that does the formatting specific for lovs.
393 --
394 -- The reason the original function can not be used are:
395 --
396 -- 1. The original function retrieves org_id and user_id from
397 -- QRI tables. But, for direct data entry this data
398 -- should not and can not be retrieved from QRI.
399 --
400 -- 2. The original function adds a sql wrapper to the sql, this
401 -- is not the correct thing to do in case mobile quality.
402 --
403 -- 3. The original function removes Order BY clause from the query.
404 -- This is required in mobile quality.
405 --
406 --
407 -- ORASHID
408
409 -- note: this procedure will generally return a string longer than the
410 -- input parameter x_string. dimension the variables to account for this.
411
412
413 IF (x_created_by IS NULL) THEN
414 who_created_by := fnd_global.user_id;
415 ELSE
416 who_created_by := x_created_by;
417 END IF;
418
419
420 -- Bug 9773026
421 -- Moved following code to remove_order_by_from_sql and replaced
422 -- with a call to that function.
423 -- skolluku
424 /*
425 -- allow trailing semi-colon and slash. Bug 956708.
426 -- bso
427 new_string := rtrim(x_string, ' ;/');
428
429 -- convert string to all uppercase for searching.
430 new_string := upper(new_string);
431
432 -- remove order by clause from string
433
434 order_pos := instr(new_string, 'ORDER BY');
435 IF (order_pos <> 0) THEN
436 new_string := SUBSTR(new_string, 1, order_pos - 1);
437 END IF;
438 */
439 new_string := remove_order_by_from_sql(x_string);
440 new_string := replace(new_string, ':PARAMETER.ORG_ID', to_char(x_org_id));
441 new_string := replace(new_string, ':PARAMETER.USER_ID', to_char(who_created_by));
442
443 -- encapsulate query and withdraw the first column
444 new_string := 'SELECT CODE FROM (' ||
445 'SELECT ''1'' CODE, ''1'' DESCRIPTION ' ||
446 'FROM SYS.DUAL WHERE 1=2 ' ||
447 'UNION ALL (' ||
448 new_string ||
449 ') )';
453 END format_sql_for_validation;
450
451 RETURN new_string;
452
454
455
456 FUNCTION format_sql_for_lov (x_string IN VARCHAR2, x_org_id IN NUMBER,
457 x_created_by IN NUMBER)
458 RETURN VARCHAR2 IS
459
460 order_pos NUMBER;
461 new_string VARCHAR2(2500);
462 comma_pos NUMBER;
463 from_pos NUMBER;
464 who_created_by NUMBER;
465
466 BEGIN
467
468 -- note: this procedure is a re-write of format_sql_validation_string
469 -- in the qlttrafb package with a lot of modiifcation.
470 -- This is needed LOVs in direct data entry for Mobile Quality.
471 --
472 -- The reasons the original function can not be used are:
473 --
474 -- 1. The original function retrieves org_id and user_id from
475 -- QRI tables. But, for direct data entry this data
476 -- should not and can not be retrieved from QRI.
477 --
478 -- 2. The original function adds a sql wrapper to the sql, this
479 -- is not the correct thing to do in case mobile quality.
480 --
481 -- 3. The original function removes second column from the query.
482 -- This is a big NO NO in mobile quality.
483 --
484 -- 4. The original function removes Order BY clause from the query.
485 -- This is required in mobile quality.
486 --
487 --
488 -- ORASHID
489
490 IF (x_created_by IS NULL) THEN
491 who_created_by := fnd_global.user_id;
492 ELSE
493 who_created_by := x_created_by;
494 END IF;
495
496 -- note: this procedure will generally return a string longer than the
497 -- input parameter x_string. dimension the variables to account for this.
498
499 -- allow trailing semi-colon and slash. Bug 956708.
500 -- bso
501 new_string := rtrim(x_string, ' ;/');
502
503 -- convert string to all uppercase for searching.
504 new_string := upper(x_string);
505
506 -- check for :parameters
507
508 new_string := replace(new_string, ':PARAMETER.ORG_ID', to_char(x_org_id));
509 new_string := replace(new_string, ':PARAMETER.USER_ID', to_char(who_created_by));
510
511
512 RETURN new_string;
513
514 END format_sql_for_lov;
515
516
517 FUNCTION get_element_id (p_element_name IN VARCHAR2)
518 RETURN NUMBER IS
519
520 CURSOR c IS
521 SELECT char_id
522 FROm qa_chars
523 WHERE name = p_element_name
524 AND enabled_flag = 1;
525
526 l_element_id NUMBER;
527
528 BEGIN
529
530 OPEN c;
531 FETCH c INTO l_element_id;
532 CLOSE c;
533
534 RETURN l_element_id;
535
536 END get_element_id;
537
538
539 FUNCTION has_hardcoded_lov (p_element_id IN NUMBER)
540 RETURN BOOLEAN IS
541
542 BEGIN
543
544 -- rkaza. 12/15/2003. bug 3280307.
545 -- Added Component item to the list of hardcoded lov's.
546
547 -- anagarwa Mon Jul 26 11:37:43 PDT 2004
548 -- bug 3773298 Added Serial number, component serial number,
549 -- component lot number and lot number lovs missing in
550 -- OA Fwk based Quality (iSP, EAM and QWB)
551
552 IF (p_element_id IN
553 (qa_ss_const.Item,
554 qa_ss_const.Locator,
555 qa_ss_const.Comp_Revision,
556 qa_ss_const.Comp_Subinventory,
557 qa_ss_const.Comp_UOM,
558 qa_ss_const.Customer_Name,
559 qa_ss_const.Department,
560 qa_ss_const.From_Op_Seq_Num,
561 qa_ss_const.Production_Line,
562 qa_ss_const.PO_Number,
563 qa_ss_const.PO_Release_Num,
564 qa_ss_const.po_line_num, -- bug 3215866
565 qa_ss_const.PO_Shipment_Num,
566 qa_ss_const.Project_Number,
567 qa_ss_const.Receipt_Num,
568 qa_ss_const.Resource_Code,
569 qa_ss_const.Revision,
570 qa_ss_const.RMA_Number,
571 qa_ss_const.Sales_Order,
572 qa_ss_const.Subinventory,
573 qa_ss_const.Task_Number,
574 qa_ss_const.To_Department,
575 qa_ss_const.To_Op_Seq_Num,
576 qa_ss_const.UOM,
577 qa_ss_const.Vendor_Name,
578 qa_ss_const.Job_Name,
579 qa_ss_const.asset_group,
580 qa_ss_const.asset_number,
581 --dgupta: R12 EAM Integration. Bug 4345492 Start
582 qa_ss_const.asset_instance_number,
583 --dgupta: R12 EAM Integration. Bug 4345492 End
584 qa_ss_const.asset_activity,
585 qa_ss_const.work_order,
586 qa_ss_const.maintenance_op_seq,
587 qa_ss_const.followup_activity,
588 qa_ss_const.comp_item,
589 qa_ss_const.serial_number,
590 qa_ss_const.lot_number,
591 qa_ss_const.comp_lot_number,
592 qa_ss_const.comp_serial_number,
593 /* R12 DR Integration. Bug 4345489 */
594 qa_ss_const.repair_order_number,
595 qa_ss_const.jtf_task_number,
596 /* R12 DR Integration. Bug 4345489 */
597 -- R12 OPM Deviations. Bug 4345503 Start
598 qa_ss_const.process_batch_num,
599 qa_ss_const.process_batchstep_num,
600 qa_ss_const.process_operation,
601 qa_ss_const.process_activity,
602 qa_ss_const.process_resource,
603 qa_ss_const.process_parameter,
607 )
604 -- R12 OPM Deviations. Bug 4345503 End
605 -- added to_locator. Bug 13638890. hmakam
606 qa_ss_const.to_locator
608 ) THEN
609
610 RETURN TRUE;
611
612 ELSE
613
614 RETURN FALSE;
615
616 END IF;
617
618 END has_hardcoded_lov;
619
620 -- anagarwa Tue Jun 22 14:19:42 PDT 2004
621 -- bug 3692326 Support element spec in QWB
622 FUNCTION lower_spec_limit(element_id IN NUMBER)
623 RETURN VARCHAR2 IS
624
625 BEGIN
626
627 fetch_qa_chars(element_id);
628 IF NOT exists_qa_chars(element_id) THEN
629 RETURN NULL;
630 END IF;
631 RETURN x_qa_chars_array(element_id).lower_spec_limit;
632
633 END lower_spec_limit;
634
635
636 FUNCTION upper_spec_limit(element_id IN NUMBER)
637 RETURN VARCHAR2 IS
638
639 BEGIN
640
641 fetch_qa_chars(element_id);
642 IF NOT exists_qa_chars(element_id) THEN
643 RETURN NULL;
644 END IF;
645 RETURN x_qa_chars_array(element_id).upper_spec_limit;
646
647 END upper_spec_limit;
648
649 FUNCTION target_value(element_id IN NUMBER)
650 RETURN VARCHAR2 IS
651
652 BEGIN
653
654 fetch_qa_chars(element_id);
655 IF NOT exists_qa_chars(element_id) THEN
656 RETURN NULL;
657 END IF;
658 RETURN x_qa_chars_array(element_id).target_value;
659
660 END target_value;
661
662
663 -- Bug 3754667. Added the below function to fetch the developer_name
664 -- for a collection element. kabalakr.
665
666 FUNCTION developer_name(element_id IN NUMBER)
667 RETURN VARCHAR2 IS
668
669 BEGIN
670
671 fetch_qa_chars(element_id);
672 IF NOT exists_qa_chars(element_id) THEN
673 RETURN NULL;
674 END IF;
675 RETURN x_qa_chars_array(element_id).developer_name;
676
677 END developer_name;
678
679 -- Bug 4453386. R12 base line build.
680 -- Manaully merging 115.19.11510.2 changes and mainline version.
681 -- srhariha. Fri Jun 24 02:19:00 PDT 2005.
682
683 --
684 -- Bug 3926150. Added get_element_name. A useful utility for
685 -- general use also.
686 -- bso Sat Dec 4 15:01:54 PST 2004
687 --
688 FUNCTION get_element_name (p_element_id IN NUMBER)
689 RETURN VARCHAR2 IS
690 BEGIN
691 fetch_qa_chars(p_element_id);
692 IF NOT exists_qa_chars(p_element_id) THEN
693 RETURN NULL;
694 END IF;
695 RETURN x_qa_chars_array(p_element_id).name;
696 END get_element_name;
697
698
699
700 END qa_chars_api;
[Home] [Help]