1 PACKAGE BODY qa_chars_api AS
2 /* $Header: qltcharb.plb 120.4 2005/06/24 02:29:21 srhariha noship $ */
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
275
276 FUNCTION lower_reasonable_limit(element_id IN NUMBER)
277 RETURN VARCHAR2 IS
278
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 FUNCTION format_sql_for_validation (x_string VARCHAR2, x_org_id IN NUMBER,
348 x_created_by IN NUMBER)
349 RETURN VARCHAR2 IS
350
351 order_pos NUMBER;
352 new_string VARCHAR2(2500);
353 comma_pos NUMBER;
354 from_pos NUMBER;
355 who_created_by NUMBER;
356
357 BEGIN
358
359 -- NOTE: This function is a re-write of format_sql_validation_string
360 -- in the qlttrafb package with a lot of modiifcation. There is
361 -- is a similar function named format_sql_for_lov inthis package
362 -- that does the formatting specific for lovs.
363 --
364 -- The reason the original function can not be used are:
365 --
366 -- 1. The original function retrieves org_id and user_id from
367 -- QRI tables. But, for direct data entry this data
368 -- should not and can not be retrieved from QRI.
369 --
373 -- 3. The original function removes Order BY clause from the query.
370 -- 2. The original function adds a sql wrapper to the sql, this
371 -- is not the correct thing to do in case mobile quality.
372 --
374 -- This is required in mobile quality.
375 --
376 --
377 -- ORASHID
378
379 -- note: this procedure will generally return a string longer than the
380 -- input parameter x_string. dimension the variables to account for this.
381
382
383 IF (x_created_by IS NULL) THEN
384 who_created_by := fnd_global.user_id;
385 ELSE
386 who_created_by := x_created_by;
387 END IF;
388
389
390 -- allow trailing semi-colon and slash. Bug 956708.
391 -- bso
392 new_string := rtrim(x_string, ' ;/');
393
394 -- convert string to all uppercase for searching.
395 new_string := upper(new_string);
396
397 -- remove order by clause from string
398
399 order_pos := instr(new_string, 'ORDER BY');
400 IF (order_pos <> 0) THEN
401 new_string := SUBSTR(new_string, 1, order_pos - 1);
402 END IF;
403
404 new_string := replace(new_string, ':PARAMETER.ORG_ID', to_char(x_org_id));
405 new_string := replace(new_string, ':PARAMETER.USER_ID', to_char(who_created_by));
406
407 -- encapsulate query and withdraw the first column
408 new_string := 'SELECT CODE FROM (' ||
409 'SELECT ''1'' CODE, ''1'' DESCRIPTION ' ||
410 'FROM SYS.DUAL WHERE 1=2 ' ||
411 'UNION ALL (' ||
412 new_string ||
413 ') )';
414
415 RETURN new_string;
416
417 END format_sql_for_validation;
418
419
420 FUNCTION format_sql_for_lov (x_string IN VARCHAR2, x_org_id IN NUMBER,
421 x_created_by IN NUMBER)
422 RETURN VARCHAR2 IS
423
424 order_pos NUMBER;
425 new_string VARCHAR2(2500);
426 comma_pos NUMBER;
427 from_pos NUMBER;
428 who_created_by NUMBER;
429
430 BEGIN
431
432 -- note: this procedure is a re-write of format_sql_validation_string
433 -- in the qlttrafb package with a lot of modiifcation.
434 -- This is needed LOVs in direct data entry for Mobile Quality.
435 --
436 -- The reasons the original function can not be used are:
437 --
438 -- 1. The original function retrieves org_id and user_id from
439 -- QRI tables. But, for direct data entry this data
440 -- should not and can not be retrieved from QRI.
441 --
442 -- 2. The original function adds a sql wrapper to the sql, this
443 -- is not the correct thing to do in case mobile quality.
444 --
445 -- 3. The original function removes second column from the query.
446 -- This is a big NO NO in mobile quality.
447 --
448 -- 4. The original function removes Order BY clause from the query.
449 -- This is required in mobile quality.
450 --
451 --
452 -- ORASHID
453
454 IF (x_created_by IS NULL) THEN
455 who_created_by := fnd_global.user_id;
456 ELSE
457 who_created_by := x_created_by;
458 END IF;
459
460 -- note: this procedure will generally return a string longer than the
461 -- input parameter x_string. dimension the variables to account for this.
462
463 -- allow trailing semi-colon and slash. Bug 956708.
464 -- bso
465 new_string := rtrim(x_string, ' ;/');
466
467 -- convert string to all uppercase for searching.
468 new_string := upper(x_string);
469
470 -- check for :parameters
471
472 new_string := replace(new_string, ':PARAMETER.ORG_ID', to_char(x_org_id));
473 new_string := replace(new_string, ':PARAMETER.USER_ID', to_char(who_created_by));
474
475
476 RETURN new_string;
477
478 END format_sql_for_lov;
479
480
481 FUNCTION get_element_id (p_element_name IN VARCHAR2)
482 RETURN NUMBER IS
483
484 CURSOR c IS
485 SELECT char_id
486 FROm qa_chars
487 WHERE name = p_element_name
488 AND enabled_flag = 1;
489
490 l_element_id NUMBER;
491
492 BEGIN
493
494 OPEN c;
495 FETCH c INTO l_element_id;
496 CLOSE c;
497
498 RETURN l_element_id;
499
500 END get_element_id;
501
502
503 FUNCTION has_hardcoded_lov (p_element_id IN NUMBER)
504 RETURN BOOLEAN IS
505
506 BEGIN
507
508 -- rkaza. 12/15/2003. bug 3280307.
509 -- Added Component item to the list of hardcoded lov's.
510
511 -- anagarwa Mon Jul 26 11:37:43 PDT 2004
512 -- bug 3773298 Added Serial number, component serial number,
513 -- component lot number and lot number lovs missing in
514 -- OA Fwk based Quality (iSP, EAM and QWB)
515
516 IF (p_element_id IN
517 (qa_ss_const.Item,
518 qa_ss_const.Locator,
519 qa_ss_const.Comp_Revision,
520 qa_ss_const.Comp_Subinventory,
521 qa_ss_const.Comp_UOM,
522 qa_ss_const.Customer_Name,
523 qa_ss_const.Department,
527 qa_ss_const.PO_Release_Num,
524 qa_ss_const.From_Op_Seq_Num,
525 qa_ss_const.Production_Line,
526 qa_ss_const.PO_Number,
528 qa_ss_const.po_line_num, -- bug 3215866
529 qa_ss_const.PO_Shipment_Num,
530 qa_ss_const.Project_Number,
531 qa_ss_const.Receipt_Num,
532 qa_ss_const.Resource_Code,
533 qa_ss_const.Revision,
534 qa_ss_const.RMA_Number,
535 qa_ss_const.Sales_Order,
536 qa_ss_const.Subinventory,
537 qa_ss_const.Task_Number,
538 qa_ss_const.To_Department,
539 qa_ss_const.To_Op_Seq_Num,
540 qa_ss_const.UOM,
541 qa_ss_const.Vendor_Name,
542 qa_ss_const.Job_Name,
543 qa_ss_const.asset_group,
544 qa_ss_const.asset_number,
545 --dgupta: R12 EAM Integration. Bug 4345492 Start
546 qa_ss_const.asset_instance_number,
547 --dgupta: R12 EAM Integration. Bug 4345492 End
548 qa_ss_const.asset_activity,
549 qa_ss_const.work_order,
550 qa_ss_const.maintenance_op_seq,
551 qa_ss_const.followup_activity,
552 qa_ss_const.comp_item,
553 qa_ss_const.serial_number,
554 qa_ss_const.lot_number,
555 qa_ss_const.comp_lot_number,
556 qa_ss_const.comp_serial_number,
557 /* R12 DR Integration. Bug 4345489 */
558 qa_ss_const.repair_order_number,
559 qa_ss_const.jtf_task_number,
560 /* R12 DR Integration. Bug 4345489 */
561 -- R12 OPM Deviations. Bug 4345503 Start
562 qa_ss_const.process_batch_num,
563 qa_ss_const.process_batchstep_num,
564 qa_ss_const.process_operation,
565 qa_ss_const.process_activity,
566 qa_ss_const.process_resource,
567 qa_ss_const.process_parameter
568 -- R12 OPM Deviations. Bug 4345503 End
569 )
570 ) THEN
571
572 RETURN TRUE;
573
574 ELSE
575
576 RETURN FALSE;
577
578 END IF;
579
580 END has_hardcoded_lov;
581
582 -- anagarwa Tue Jun 22 14:19:42 PDT 2004
583 -- bug 3692326 Support element spec in QWB
584 FUNCTION lower_spec_limit(element_id IN NUMBER)
585 RETURN VARCHAR2 IS
586
587 BEGIN
588
589 fetch_qa_chars(element_id);
590 IF NOT exists_qa_chars(element_id) THEN
591 RETURN NULL;
592 END IF;
593 RETURN x_qa_chars_array(element_id).lower_spec_limit;
594
595 END lower_spec_limit;
596
597
598 FUNCTION upper_spec_limit(element_id IN NUMBER)
599 RETURN VARCHAR2 IS
600
601 BEGIN
602
603 fetch_qa_chars(element_id);
604 IF NOT exists_qa_chars(element_id) THEN
605 RETURN NULL;
606 END IF;
607 RETURN x_qa_chars_array(element_id).upper_spec_limit;
608
609 END upper_spec_limit;
610
611 FUNCTION target_value(element_id IN NUMBER)
612 RETURN VARCHAR2 IS
613
614 BEGIN
615
616 fetch_qa_chars(element_id);
617 IF NOT exists_qa_chars(element_id) THEN
618 RETURN NULL;
619 END IF;
620 RETURN x_qa_chars_array(element_id).target_value;
621
622 END target_value;
623
624
625 -- Bug 3754667. Added the below function to fetch the developer_name
626 -- for a collection element. kabalakr.
627
628 FUNCTION developer_name(element_id IN NUMBER)
629 RETURN VARCHAR2 IS
630
631 BEGIN
632
633 fetch_qa_chars(element_id);
634 IF NOT exists_qa_chars(element_id) THEN
635 RETURN NULL;
636 END IF;
637 RETURN x_qa_chars_array(element_id).developer_name;
638
639 END developer_name;
640
641 -- Bug 4453386. R12 base line build.
642 -- Manaully merging 115.19.11510.2 changes and mainline version.
643 -- srhariha. Fri Jun 24 02:19:00 PDT 2005.
644
645 --
646 -- Bug 3926150. Added get_element_name. A useful utility for
647 -- general use also.
648 -- bso Sat Dec 4 15:01:54 PST 2004
649 --
650 FUNCTION get_element_name (p_element_id IN NUMBER)
651 RETURN VARCHAR2 IS
652 BEGIN
653 fetch_qa_chars(p_element_id);
654 IF NOT exists_qa_chars(p_element_id) THEN
655 RETURN NULL;
656 END IF;
657 RETURN x_qa_chars_array(p_element_id).name;
658 END get_element_name;
659
660
661
662 END qa_chars_api;
[Home] [Help]