[Home] [Help]
PACKAGE BODY: APPS.QLTTRAFB
Source
1 PACKAGE BODY QLTTRAFB as
2 /* $Header: qlttrafb.plb 115.16 2003/12/21 17:46:48 suramasw ship $ */
3 -- 1/23/96 - created
4 -- Paul Mishkin
5
6
7 PROCEDURE parse_error_columns(
8 p_cols IN VARCHAR2,
9 x_col1 OUT NOCOPY VARCHAR2,
10 x_col2 OUT NOCOPY VARCHAR2,
11 x_col3 OUT NOCOPY VARCHAR2,
12 x_col4 OUT NOCOPY VARCHAR2,
13 x_col5 OUT NOCOPY VARCHAR2) IS
14 --
15 -- SQL Bind Project for performance.
16 -- There is an IN operation in many places of the form:
17 -- IN ERR_COL_LIST. This is a literal SQL. The fact
18 -- that an err_col_list can contain at most 5 tokens can
19 -- be used to make this into a bind SQL.
20 -- This procedure parses out the various columns from the list
21 -- into separate tokens. 'NULL' will be substituted if there
22 -- is no string in that position.
23
24 -- Bug 3136107.Same as the fix done in Bug 3079312. suramasw.
25
26 s VARCHAR2(2000) := p_cols;
27 p INTEGER;
28 i INTEGER := 1;
29 val dbms_sql.varchar2s;
30
31 BEGIN
32 val(1) := '';
33 val(2) := '';
34 val(3) := '';
35 val(4) := '';
36 val(5) := '';
37
38 --
39 -- Get rid of single-quotes
40 --
41 s := translate(s, '''', ' ');
42
43 --
44 -- Loop until , is not found
45 --
46 p := instr(s, ',');
47
48 WHILE p <> 0 LOOP
49 -- found a comma, splice everything in front
50 val(i) := rtrim(ltrim(substr(s, 1, p-1)));
51 s := substr(s, p+1);
52 p := instr(s, ',');
53 i := i + 1;
54 END LOOP;
55 val(i) := rtrim(ltrim(s));
56
57 x_col1 := val(1);
58 x_col2 := val(2);
59 x_col3 := val(3);
60 x_col4 := val(4);
61 x_col5 := val(5);
62
63 END parse_error_columns;
64
65
66 -- helper function for sql parser. replaces every occurrence of
67 -- X_OLD_TOKEN, in upper, lower, or mixed case, with X_NEW_TOKEN.
68 -- Assumes that both tokens are sent in upper case.
69 FUNCTION REPLACE_TOKEN( X_STRING VARCHAR2,
70 X_OLD_TOKEN VARCHAR2,
71 X_NEW_TOKEN VARCHAR2 ) RETURN VARCHAR2 IS
72 POS NUMBER;
73 NEW_STRING VARCHAR2(2500);
74 NEW_U_STRING VARCHAR2(2500);
75 BEGIN
76 NEW_STRING := X_STRING;
77 NEW_U_STRING := UPPER(X_STRING);
78
79 LOOP
80 POS := INSTR(NEW_U_STRING, X_OLD_TOKEN);
81 EXIT WHEN POS = 0;
82 NEW_STRING := SUBSTR(NEW_STRING, 1, POS - 1) ||
83 X_NEW_TOKEN ||
84 SUBSTR(NEW_STRING, POS + LENGTH(X_OLD_TOKEN));
85 NEW_U_STRING := SUBSTR(NEW_U_STRING, 1, POS - 1) ||
86 X_NEW_TOKEN ||
87 SUBSTR(NEW_U_STRING, POS + LENGTH(X_OLD_TOKEN));
88 END LOOP;
89
90 RETURN NEW_STRING;
91 END;
92
93 -- helper function for sql parser.
94 FUNCTION IS_WHITESPACE(X_CHARACTER VARCHAR2) RETURN BOOLEAN IS
95 BEGIN
96 -- remember to account for null values
97 -- space, tab, and newline characters are also whitespace
98 IF NVL(X_CHARACTER, 'X') IN (' ', ' ', '
99 ') THEN
100 RETURN TRUE;
101 ELSE
102 RETURN FALSE;
103 END IF;
104 END;
105
106
107 -- helper function for sql parser.
108 -- Return TRUE if input character, x, is alpha numeric. Assume not null.
109 -- bso
110 FUNCTION IS_ALPHANUM(x varchar2) RETURN BOOLEAN IS
111 BEGIN
112 return (x between 'a' and 'z') or (x between 'A' and 'Z') or
113 (x between '0' and '9');
114 END;
115
116
117 -- helper function for sql parser.
118 -- Return the character at a position N. The first position is 1.
119 -- No error checking.
120 -- bso
121 FUNCTION CHAR_AT(S VARCHAR2, N NUMBER) RETURN VARCHAR2 IS
122 BEGIN
123 RETURN substr(S, N, 1);
124 END;
125
126
127 -- helper function for sql parser.
128 -- This function locates a keyword in a SQL string and
129 -- returns the position. The tricky thing is to make sure if
130 -- the word appears between quotes and between parenthesis,
131 -- then it is not considered to be a separator, and therefore
132 -- should not be returned. This method is a lot more sophisticated
133 -- then the previous technique of using INSTR to locate the first
134 -- comma. The latter will yield a critical error in case there is
135 -- a comma between quotes or inside a multi-argument function.
136 --
137 -- S is the input string
138 -- KEY is the keyword to be located.
139 -- ST is the starting position to search (default is 1 = beginning).
140 --
141 -- Returns the position of the first comma or 0 if not found.
142 --
143 -- bso
144 FUNCTION FIND_KEYWORD(S VARCHAR2, KEY VARCHAR2, ST NUMBER DEFAULT 1)
145 RETURN NUMBER IS
146 p number; -- current and final position
147 bracket number; -- state: how many levels of parenthesis
148 quote boolean; -- state: are we in single quote?
149 L number; -- length of the input string
150 K number; -- length of the input keyword
151 c varchar2(1); -- temporary variable
152 BEGIN
153 p := st;
154 bracket := 0;
155 quote := false;
156 L := length(S);
157 K := length(key);
158
159 Loop
160 if (p > L) then
161 return 0;
162 end if;
163 if (bracket = 0) and (not quote) and (instr(S, key, p) = p) then
164 if is_alphanum(char_at(key,1)) then -- do not use AND
165 -- Found keyword. (not in quote or brackets)
166 -- But let's make sure it is surrounded by spaces
167 -- unless it is at the beginning of the sentence or at the
168 -- very end.
169 if (p = 1 or is_whitespace(char_at(S, p-1))) and
170 (p+K > L or is_whitespace(char_at(S, p+K))) then
171 return p;
172 end if;
173 -- Do not return if it is not surrounded by spaces.
174 else
175 -- Return if it's not a real keyword, but a punctuation.
176 return p;
177 end if;
178 end if;
179 c := char_at(S, p);
180 if (not quote) and (c = '(') then
181 bracket := bracket + 1;
182 end if;
183 if (not quote) and (c = ')') then
184 bracket := bracket - 1;
185 end if;
186 if (c = '''') then
187 quote := not quote; -- Amazingly, this will take care of ''
188 end if;
189 p := p + 1;
190 end loop;
191
192 return 0;
193 END;
194
195
196
197 -- formats the sql validation string into a usable form
198 FUNCTION FORMAT_SQL_VALIDATION_STRING (X_STRING VARCHAR2) RETURN VARCHAR2 IS
199 ORDER_POS NUMBER;
200 NEW_STRING VARCHAR2(2500);
201 NEW_U_STRING VARCHAR2(2500);
202
203 COMMA_POS NUMBER;
204 FROM_POS NUMBER;
205 BEGIN
206 -- note: this procedure will generally return a string longer than the
207 -- input parameter X_STRING. dimension the variables to account for this.
208
209
210 -- allow trailing semi-colon and slash. Bug 956708.
211 -- bso
212 NEW_STRING := rtrim(X_STRING, ' ;/
213 ');
214
215 -- convert string to all uppercase for searching.
216 NEW_U_STRING := UPPER(X_STRING);
217
218 -- remove order by clause from string
219
220 ORDER_POS := INSTR(NEW_U_STRING, 'ORDER BY');
221 IF (ORDER_POS <> 0) THEN
222 NEW_STRING := SUBSTR(NEW_STRING, 1, ORDER_POS - 1);
223 END IF;
224
225 -- check for :parameters
226 IF INSTR(NEW_U_STRING, ':PARAMETER') <> 0 THEN
227 -- replace :parameter.ord_id and :parameter.user_id
228 NEW_STRING := REPLACE_TOKEN(NEW_STRING,
229 ':PARAMETER.ORG_ID',
230 'QRI.ORGANIZATION_ID');
231
232 NEW_STRING := REPLACE_TOKEN(NEW_STRING,
233 ':PARAMETER.USER_ID',
234 'QRI.CREATED_BY');
235
236 -- remove the second column from the query.
237 -- search for the end of the first column name or alias
238 -- and the from keyword
239 -- <-- <--
240 -- select blah code, bleh description from ... where ...
241 -- COMMA_POS := INSTR(NEW_U_STRING, ',');
242 COMMA_POS := FIND_KEYWORD(NEW_U_STRING, ',');
243
244 FROM_POS := COMMA_POS;
245 LOOP
246 -- find first occurrence of FROM
247 FROM_POS := INSTR(NEW_U_STRING, 'FROM', FROM_POS);
248 -- check for whitespace before and after
249 EXIT WHEN IS_WHITESPACE(SUBSTR(NEW_U_STRING, FROM_POS - 1, 1))
250 AND IS_WHITESPACE(SUBSTR(NEW_U_STRING, FROM_POS + 4, 1));
251 -- look for next occurrence of FROM
252 FROM_POS := FROM_POS + 4;
253 END LOOP;
254
255 NEW_STRING := SUBSTR(NEW_STRING, 1, COMMA_POS - 1)
256 || SUBSTR(NEW_STRING, FROM_POS - 1);
257
258 ELSE
259 -- encapsulate query and withdraw the first column
260 NEW_STRING := 'SELECT CODE FROM (' ||
261 'SELECT ''1'' CODE, ''1'' DESCRIPTION ' ||
262 'FROM SYS.DUAL WHERE 1=2 ' ||
263 'UNION ALL (' ||
264 NEW_STRING ||
265 ') )';
266 -- Added where code is not null because this subquery
267 -- will be compared with a value using NOT IN, see
268 -- pitfalls of NOT IN in SQL Reference, vol 1.
269 -- bso
270 --
271 -- taken 'where code is not null' away because the
272 -- enclosing SQL has been changed to use NOT EXISTS
273 -- and IN to simulate NOT IN; thus avoiding the
274 -- NOT IN pitfall. See Bug 682093.
275 -- bso
276 END IF;
277
278 RETURN NEW_STRING;
279 END FORMAT_SQL_VALIDATION_STRING;
280
281
282 FUNCTION VALIDATE_TYPE (X_VALUE VARCHAR2, X_DATATYPE NUMBER) RETURN BOOLEAN IS
283 BEGIN
284 IF (X_DATATYPE = 2) THEN -- number datatype
285 DECLARE
286 TEMPNUM NUMBER;
287 BEGIN
288 TEMPNUM := qltdate.any_to_number(X_VALUE);
289 RETURN TRUE;
290 EXCEPTION
291 WHEN OTHERS THEN
292 RETURN FALSE;
293 END;
294 ELSIF (X_DATATYPE = 3) THEN -- date datatype
295 DECLARE
296 TEMPDATE DATE;
297 BEGIN
298 TEMPDATE := qltdate.any_to_date(X_VALUE);
299 RETURN TRUE;
300 EXCEPTION
301 WHEN OTHERS THEN
302 RETURN FALSE;
303 END;
304
305 -- For Timezone Compliance bug 3179845. Validate datetime elements.
306 -- kabalakr Mon Oct 27 04:33:49 PST 2003.
307
308 ELSIF (X_DATATYPE = 6) THEN -- date datatype
309 DECLARE
310 TEMPDATE DATE;
311 BEGIN
312 TEMPDATE := qltdate.any_to_datetime(X_VALUE);
313 RETURN TRUE;
314 EXCEPTION
315 WHEN OTHERS THEN
316 RETURN FALSE;
317 END;
318 END IF;
319 END VALIDATE_TYPE;
320
321 PROCEDURE EXEC_SQL (STRING IN VARCHAR2) IS
322 CUR INTEGER;
323 RET INTEGER;
324 BEGIN
325 CUR := DBMS_SQL.OPEN_CURSOR;
326 DBMS_SQL.PARSE(CUR, STRING, DBMS_SQL.NATIVE);
327 RET := DBMS_SQL.EXECUTE(CUR);
328 DBMS_SQL.CLOSE_CURSOR(CUR);
329
330 exception when others then
331 IF dbms_sql.is_open(cur) THEN
332 dbms_sql.close_cursor(cur);
333 END IF;
334 raise;
335 END EXEC_SQL;
336
337 FUNCTION DECODE_ACTION_VALUE_LOOKUP (NUM NUMBER) RETURN VARCHAR2 IS
338 BEGIN
339 IF NUM = 1 THEN
340 RETURN 'UPPER_REASONABLE_LIMIT';
341 ELSIF NUM = 2 THEN
342 RETURN 'UPPER_SPEC_LIMIT';
343 ELSIF NUM = 3 THEN
344 RETURN 'UPPER_USER_DEFINED_LIMIT';
345 ELSIF NUM = 4 THEN
346 RETURN 'TARGET_VALUE';
347 ELSIF NUM = 5 THEN
348 RETURN 'LOWER_USER_DEFINED_LIMIT';
349 ELSIF NUM = 6 THEN
350 RETURN 'LOWER_SPEC_LIMIT';
351 ELSIF NUM = 7 THEN
352 RETURN 'LOWER_REASONABLE_LIMIT';
353 END IF;
354 END DECODE_ACTION_VALUE_LOOKUP;
355
356 FUNCTION DECODE_OPERATOR (OP NUMBER) RETURN VARCHAR2 IS
357 BEGIN
358 IF OP = 1 THEN
359 RETURN '=';
360 ELSIF OP = 2 THEN
361 RETURN '<>';
362 ELSIF OP = 3 THEN
363 RETURN '>=';
364 ELSIF OP = 4 THEN
365 RETURN '<=';
366 ELSIF OP = 5 THEN
367 RETURN '>';
368 ELSIF OP = 6 THEN
369 RETURN '<';
370 ELSIF OP = 7 THEN
371 RETURN 'IS NOT NULL';
372 ELSIF OP = 8 THEN
373 RETURN 'IS NULL';
374 ELSIF OP = 9 THEN
375 RETURN 'BETWEEN';
376 ELSIF OP = 10 THEN
377 RETURN 'NOT BETWEEN';
378 END IF;
379 END DECODE_OPERATOR;
380
381 /* validate_disabled
382 *
383 * writes an error to the errors table for each row in the interface table
384 * that has a non-null value for a particular disabled element (col_name).
385 * call this procedure only for disabled elements.
386 */
387
388 PROCEDURE VALIDATE_DISABLED(COL_NAME VARCHAR2,
389 ERROR_COL_NAME VARCHAR2,
390 ERROR_MESSAGE VARCHAR2,
391 X_GROUP_ID NUMBER,
392 X_USER_ID NUMBER,
393 X_LAST_UPDATE_LOGIN NUMBER,
394 X_REQUEST_ID NUMBER,
395 X_PROGRAM_APPLICATION_ID NUMBER,
396 X_PROGRAM_ID NUMBER) IS
397 SQL_STATEMENT VARCHAR2(2000);
398 QUOTED_COL_NAME VARCHAR2(50);
399 BEGIN
400 QUOTED_COL_NAME := '''' || COL_NAME || '''';
401
402 -- Bug 3136107.
403 -- SQL Bind project. Code modified to use bind variables instead of literals
404 -- Same as the fix done for Bug 3079312.suramasw.
405
406 SQL_STATEMENT :=
407 'INSERT INTO QA_INTERFACE_ERRORS (TRANSACTION_INTERFACE_ID, ' ||
408 'ERROR_COLUMN, ERROR_MESSAGE, LAST_UPDATE_DATE, LAST_UPDATED_BY, ' ||
409 'CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, REQUEST_ID, ' ||
410 'PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE) ' ||
411 'SELECT QRI.TRANSACTION_INTERFACE_ID, :ERROR_COL_NAME, :ERROR_MESSAGE, ' ||
412 'SYSDATE, :USER_ID, SYSDATE, :USER_ID2 , :LAST_UPDATE_LOGIN , ' ||
413 ':REQUEST_ID, :PROGRAM_APPLICATION_ID , :PROGRAM_ID, SYSDATE ' ||
414 'FROM QA_RESULTS_INTERFACE QRI ' ||
415 'WHERE QRI.GROUP_ID = :GROUP_ID ' ||
416 ' AND QRI.PROCESS_STATUS = 2 ' ||
417 'AND NOT EXISTS
418 (SELECT ''X'' ' ||
419 'FROM QA_INTERFACE_ERRORS QIE ' ||
420 'WHERE QIE.TRANSACTION_INTERFACE_ID = ' ||
421 'QRI.TRANSACTION_INTERFACE_ID ' ||
422 'AND QIE.ERROR_COLUMN IN (' ||
423 QUOTED_COL_NAME || ', NULL)) ' ||
424 'AND EXISTS ' ||
425 '(SELECT ''X'' FROM QA_RESULTS_INTERFACE ' ||
426 'WHERE QRI.' || COL_NAME || ' IS NOT NULL)';
427
428 EXECUTE IMMEDIATE SQL_STATEMENT USING ERROR_COL_NAME,
429 ERROR_MESSAGE,
430 X_USER_ID,
431 X_USER_ID,
432 X_LAST_UPDATE_LOGIN,
433 X_REQUEST_ID,
434 X_PROGRAM_APPLICATION_ID,
435 X_PROGRAM_ID,
436 X_GROUP_ID;
437
438 --QLTTRAFB.EXEC_SQL(SQL_STATEMENT);
439 END VALIDATE_DISABLED;
440
441 /* validate_mandatory
442 *
443 * writes an error to the errors table for each row in the interface table
444 * that has a null value for a particular mandatory element. call this
445 * procedure only for mandatory elements.
446 */
447
448 PROCEDURE VALIDATE_MANDATORY(COL_NAME VARCHAR2,
449 ERROR_COL_NAME VARCHAR2,
450 ERROR_MESSAGE VARCHAR2,
451 X_GROUP_ID NUMBER,
452 X_USER_ID NUMBER,
453 X_LAST_UPDATE_LOGIN NUMBER,
454 X_REQUEST_ID NUMBER,
455 X_PROGRAM_APPLICATION_ID NUMBER,
456 X_PROGRAM_ID NUMBER,
457 PARENT_COL_NAME VARCHAR2,
458 ERROR_COL_LIST VARCHAR2) IS
459 SQL_STATEMENT VARCHAR2(2000);
460
461 l_col1 VARCHAR2(100);
462 l_col2 VARCHAR2(100);
463 l_col3 VARCHAR2(100);
464 l_col4 VARCHAR2(100);
465 l_col5 VARCHAR2(100);
466
467 BEGIN
468
469 -- Bug 3136107.
470 -- SQL Bind project. Code modified to use bind variables instead of literals
471 -- Same as the fix done for Bug 3079312.suramasw.
472
473 parse_error_columns(error_col_list, l_col1, l_col2, l_col3, l_col4, l_col5);
474
475 SQL_STATEMENT :=
476 'INSERT INTO QA_INTERFACE_ERRORS (TRANSACTION_INTERFACE_ID, ' ||
477 'ERROR_COLUMN, ERROR_MESSAGE, LAST_UPDATE_DATE, LAST_UPDATED_BY, ' ||
478 'CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, REQUEST_ID, ' ||
479 'PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE) ' ||
480 'SELECT QRI.TRANSACTION_INTERFACE_ID, :ERROR_COL_NAME, :ERROR_MESSAGE, '||
481 'SYSDATE, :USER_ID, SYSDATE, :USER_ID2, :LAST_UPDATE_LOGIN, ' ||
482 ':REQUEST_ID, :PROGRAM_APPLICATION_ID, :PROGRAM_ID, SYSDATE ' ||
483 'FROM QA_RESULTS_INTERFACE QRI ' ||
484 'WHERE QRI.GROUP_ID = :GROUP_ID ' ||
485 ' AND QRI.PROCESS_STATUS = 2 ';
486
487 IF (PARENT_COL_NAME IS NOT NULL) THEN
488 SQL_STATEMENT := SQL_STATEMENT ||
489 ' AND QRI.' || PARENT_COL_NAME || ' IS NOT NULL ';
490 END IF;
491
492 SQL_STATEMENT := SQL_STATEMENT ||
493 'AND NOT EXISTS
494 (SELECT ''X'' ' ||
495 'FROM QA_INTERFACE_ERRORS QIE ' ||
496 'WHERE QIE.TRANSACTION_INTERFACE_ID = ' ||
497 'QRI.TRANSACTION_INTERFACE_ID ' ||
498 'AND QIE.ERROR_COLUMN IN (:c1,:c2,:c3,:c4,:c5)) ' ||
499 'AND EXISTS ' ||
500 '(SELECT ''X'' FROM QA_RESULTS_INTERFACE ' ||
501 'WHERE QRI.' || COL_NAME || ' IS NULL)';
502
503 EXECUTE IMMEDIATE SQL_STATEMENT USING ERROR_COL_NAME,
504 ERROR_MESSAGE,
505 X_USER_ID,
506 X_USER_ID,
507 X_LAST_UPDATE_LOGIN,
508 X_REQUEST_ID,
509 X_PROGRAM_APPLICATION_ID,
510 X_PROGRAM_ID,
511 X_GROUP_ID,
512 l_col1, l_col2, l_col3, l_col4, l_col5;
513
514 -- QLTTRAFB.EXEC_SQL(SQL_STATEMENT);
515 END VALIDATE_MANDATORY;
516
517
518 PROCEDURE VALIDATE_LOOKUPS(COL_NAME VARCHAR2,
519 ERROR_COL_NAME VARCHAR2,
520 ERROR_MESSAGE VARCHAR2,
521 X_GROUP_ID NUMBER,
522 X_USER_ID NUMBER,
523 X_LAST_UPDATE_LOGIN NUMBER,
524 X_REQUEST_ID NUMBER,
525 X_PROGRAM_APPLICATION_ID NUMBER,
526 X_PROGRAM_ID NUMBER,
527 X_CHAR_ID NUMBER,
528 X_PLAN_ID NUMBER,
529 ERROR_COL_LIST VARCHAR2) IS
530 SQL_STATEMENT VARCHAR2(2000);
531
532 l_col1 VARCHAR2(100);
533 l_col2 VARCHAR2(100);
534 l_col3 VARCHAR2(100);
535 l_col4 VARCHAR2(100);
536 l_col5 VARCHAR2(100);
537
538 BEGIN
539
540 -- Bug 3136107.
541 -- SQL Bind project. Code modified to use bind variables instead of literals
542 -- Same as the fix done for Bug 3079312.suramasw.
543
544 parse_error_columns(error_col_list, l_col1, l_col2, l_col3, l_col4, l_col5);
545
546 SQL_STATEMENT :=
547 'INSERT INTO QA_INTERFACE_ERRORS (TRANSACTION_INTERFACE_ID, ' ||
548 'ERROR_COLUMN, ERROR_MESSAGE, LAST_UPDATE_DATE, LAST_UPDATED_BY, ' ||
549 'CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, REQUEST_ID, ' ||
550 'PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE) ' ||
551 'SELECT QRI.TRANSACTION_INTERFACE_ID, :ERROR_COL_NAME, :ERROR_MESSAGE, '||
552 'SYSDATE, :USER_ID, SYSDATE, :USER_ID2, :LAST_UPDATE_LOGIN, ' ||
553 ':REQUEST_ID, :PROGRAM_APPLICATION_ID, :PROGRAM_ID, SYSDATE ' ||
554 'FROM QA_RESULTS_INTERFACE QRI ' ||
555 'WHERE QRI.GROUP_ID = :GROUP_ID ' ||
556 ' AND QRI.PROCESS_STATUS = 2 ' ||
557 'AND NOT EXISTS
558 (SELECT ''X'' ' ||
559 'FROM QA_INTERFACE_ERRORS QIE ' ||
560 'WHERE QIE.TRANSACTION_INTERFACE_ID = ' ||
561 'QRI.TRANSACTION_INTERFACE_ID ' ||
562 'AND QIE.ERROR_COLUMN IN (:c1,:c2,:c3,:c4,:c5)) ' ||
563 'AND QRI.' || COL_NAME || ' NOT IN ' ||
564 '(SELECT SHORT_CODE FROM QA_PLAN_CHAR_VALUE_LOOKUPS ' ||
565 'WHERE PLAN_ID = :PLAN_ID ' ||
566 ' AND CHAR_ID = :CHAR_ID ' || ')';
567
568 EXECUTE IMMEDIATE SQL_STATEMENT USING ERROR_COL_NAME,
569 ERROR_MESSAGE,
570 X_USER_ID,
571 X_USER_ID,
572 X_LAST_UPDATE_LOGIN,
573 X_REQUEST_ID,
574 X_PROGRAM_APPLICATION_ID,
575 X_PROGRAM_ID,
576 X_GROUP_ID,
577 l_col1, l_col2, l_col3, l_col4, l_col5,
578 X_PLAN_ID,
579 X_CHAR_ID;
580
581 -- QLTTRAFB.EXEC_SQL(SQL_STATEMENT);
582 END VALIDATE_LOOKUPS;
583
584
585 PROCEDURE VALIDATE_PARENT_ENTERED(COL_NAME VARCHAR2,
586 ERROR_COL_NAME VARCHAR2,
587 ERROR_MESSAGE VARCHAR2,
588 X_GROUP_ID NUMBER,
589 X_USER_ID NUMBER,
590 X_LAST_UPDATE_LOGIN NUMBER,
591 X_REQUEST_ID NUMBER,
592 X_PROGRAM_APPLICATION_ID NUMBER,
593 X_PROGRAM_ID NUMBER,
594 PARENT_COL_NAME VARCHAR2,
595 ERROR_COL_LIST VARCHAR2) IS
596 SQL_STATEMENT VARCHAR2(2000);
597
598 l_col1 VARCHAR2(100);
599 l_col2 VARCHAR2(100);
600 l_col3 VARCHAR2(100);
601 l_col4 VARCHAR2(100);
602 l_col5 VARCHAR2(100);
603
604 BEGIN
605
606 -- Bug 3136107.
607 -- SQL Bind project. Code modified to use bind variables instead of literals
608 -- Same as the fix done for Bug 3079312.suramasw.
609
610 parse_error_columns(error_col_list, l_col1, l_col2, l_col3, l_col4, l_col5);
611
612 SQL_STATEMENT :=
613 'INSERT INTO QA_INTERFACE_ERRORS (TRANSACTION_INTERFACE_ID, ' ||
614 'ERROR_COLUMN, ERROR_MESSAGE, LAST_UPDATE_DATE, LAST_UPDATED_BY, ' ||
615 'CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, REQUEST_ID, ' ||
616 'PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE) ' ||
617 'SELECT QRI.TRANSACTION_INTERFACE_ID,:ERROR_COL_NAME, :ERROR_MESSAGE,' ||
618 'SYSDATE, :USER_ID, SYSDATE, :USER_ID2, :LAST_UPDATE_LOGIN, ' ||
619 ':REQUEST_ID, :PROGRAM_APPLICATION_ID, :PROGRAM_ID, SYSDATE ' ||
620 'FROM QA_RESULTS_INTERFACE QRI ' ||
621 'WHERE QRI.GROUP_ID = :GROUP_ID ' ||
622 ' AND QRI.PROCESS_STATUS = 2 ' ||
623 'AND NOT EXISTS
624 (SELECT ''X'' ' ||
625 'FROM QA_INTERFACE_ERRORS QIE ' ||
626 'WHERE QIE.TRANSACTION_INTERFACE_ID = ' ||
627 'QRI.TRANSACTION_INTERFACE_ID ' ||
628 'AND QIE.ERROR_COLUMN IN (:c1,:c2,:c3,:c4,:c5)) ' ||
629 'AND EXISTS ' ||
630 '(SELECT ''X'' FROM QA_RESULTS_INTERFACE ' ||
631 'WHERE QRI.' || COL_NAME || ' IS NOT NULL ' ||
632 'AND QRI.' || PARENT_COL_NAME || ' IS NULL)';
633
634 EXECUTE IMMEDIATE SQL_STATEMENT USING ERROR_COL_NAME,
635 ERROR_MESSAGE,
636 X_USER_ID,
637 X_USER_ID,
638 X_LAST_UPDATE_LOGIN,
639 X_REQUEST_ID,
640 X_PROGRAM_APPLICATION_ID,
641 X_PROGRAM_ID,
642 X_GROUP_ID,
643 l_col1, l_col2, l_col3, l_col4, l_col5;
644
645 -- QLTTRAFB.EXEC_SQL(SQL_STATEMENT);
646 END VALIDATE_PARENT_ENTERED;
647
648 -- Tracking Bug : 3104827. Review Tracking Bug : 3148873
649 -- Added for Read Only for Flag Collection Plan Elements
650 -- saugupta Thu Aug 28 08:59:59 PDT 2003
651
652 PROCEDURE VALIDATE_READ_ONLY(P_COL_NAME VARCHAR2,
653 P_ERROR_COL_NAME VARCHAR2,
654 P_ERROR_MESSAGE VARCHAR2,
655 P_GROUP_ID NUMBER,
656 P_USER_ID NUMBER,
657 P_LAST_UPDATE_LOGIN NUMBER,
658 P_REQUEST_ID NUMBER,
659 P_PROGRAM_APPLICATION_ID NUMBER,
660 P_PROGRAM_ID NUMBER,
661 P_PARENT_COL_NAME VARCHAR2,
662 P_ERROR_COL_LIST VARCHAR2) IS
663 SQL_STATEMENT VARCHAR2(2000);
664
665 l_col1 VARCHAR2(100);
666 l_col2 VARCHAR2(100);
667 l_col3 VARCHAR2(100);
668 l_col4 VARCHAR2(100);
669 l_col5 VARCHAR2(100);
670
671 BEGIN
672
673 -- Bug 3136107.
674 -- SQL Bind project. Code modified to use bind variables instead of literals
675 -- Same as the fix done for Bug 3079312.suramasw.
676
677 parse_error_columns(p_error_col_list, l_col1, l_col2, l_col3, l_col4, l_col5);
678
679 SQL_STATEMENT :=
680 'INSERT INTO QA_INTERFACE_ERRORS (TRANSACTION_INTERFACE_ID, ' ||
681 'ERROR_COLUMN, ERROR_MESSAGE, LAST_UPDATE_DATE, LAST_UPDATED_BY, ' ||
682 'CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, REQUEST_ID, ' ||
683 'PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE) ' ||
684 'SELECT QRI.TRANSACTION_INTERFACE_ID, :ERROR_COL_NAME, :ERROR_MESSAGE,' ||
685 'SYSDATE, :USER_ID, SYSDATE, :USER_ID2, :LAST_UPDATE_LOGIN, ' ||
686 ':REQUEST_ID, :PROGRAM_APPLICATION_ID, :PROGRAM_ID, SYSDATE ' ||
687 'FROM QA_RESULTS_INTERFACE QRI ' ||
688 'WHERE QRI.GROUP_ID = :GROUP_ID ' ||
689 ' AND QRI.PROCESS_STATUS = 2 ';
690
691 IF (P_PARENT_COL_NAME IS NOT NULL) THEN
692 SQL_STATEMENT := SQL_STATEMENT ||
693 ' AND QRI.' || P_PARENT_COL_NAME || ' IS NOT NULL ';
694 END IF;
695
696 SQL_STATEMENT := SQL_STATEMENT ||
697 'AND NOT EXISTS
698 (SELECT ''X'' ' ||
699 'FROM QA_INTERFACE_ERRORS QIE ' ||
700 'WHERE QIE.TRANSACTION_INTERFACE_ID = ' ||
701 'QRI.TRANSACTION_INTERFACE_ID ' ||
702 'AND QIE.ERROR_COLUMN IN (:c1,:c2,:c3,:c4,:c5)) ' ||
703 'AND EXISTS ' ||
704 '(SELECT ''X'' FROM QA_RESULTS_INTERFACE ' ||
705 'WHERE QRI.' || P_COL_NAME || ' IS NOT NULL)';
706
707 EXECUTE IMMEDIATE SQL_STATEMENT USING P_ERROR_COL_NAME,
708 P_ERROR_MESSAGE,
709 P_USER_ID,
710 P_USER_ID,
711 P_LAST_UPDATE_LOGIN,
712 P_REQUEST_ID,
713 P_PROGRAM_APPLICATION_ID,
714 P_PROGRAM_ID,
715 P_GROUP_ID,
716 l_col1, l_col2, l_col3, l_col4, l_col5;
717
718 -- QLTTRAFB.EXEC_SQL(SQL_STATEMENT);
719 END VALIDATE_READ_ONLY;
720
721
722
723
724 END QLTTRAFB;
725