[Home] [Help]
PACKAGE BODY: APPS.QLTTRAFB
Source
1 PACKAGE BODY QLTTRAFB as
2 /* $Header: qlttrafb.plb 120.1 2010/11/11 16:49:29 ntungare 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 -- bug 10212823
284 null_num number := fnd_api.G_NULL_NUM;
285 null_char varchar2(1) := fnd_api.G_NULL_char;
286 null_date date := fnd_api.G_NULL_date;
287 BEGIN
288 --bug 10212823
289 IF (X_VALUE IN (to_char(null_num), null_char, to_char(null_date))) THEN
290 RETURN TRUE;
291 END IF;
292
293 IF (X_DATATYPE = 2) THEN -- number datatype
294 DECLARE
295 TEMPNUM NUMBER;
296 BEGIN
297 TEMPNUM := qltdate.any_to_number(X_VALUE);
298 RETURN TRUE;
299 EXCEPTION
300 WHEN OTHERS THEN
301 RETURN FALSE;
302 END;
303 ELSIF (X_DATATYPE = 3) THEN -- date datatype
304 DECLARE
305 TEMPDATE DATE;
306 BEGIN
307 TEMPDATE := qltdate.any_to_date(X_VALUE);
308 RETURN TRUE;
309 EXCEPTION
310 WHEN OTHERS THEN
311 RETURN FALSE;
312 END;
313
314 -- For Timezone Compliance bug 3179845. Validate datetime elements.
315 -- kabalakr Mon Oct 27 04:33:49 PST 2003.
316
317 ELSIF (X_DATATYPE = 6) THEN -- date datatype
318 DECLARE
319 TEMPDATE DATE;
320 BEGIN
321 TEMPDATE := qltdate.any_to_datetime(X_VALUE);
322 RETURN TRUE;
323 EXCEPTION
324 WHEN OTHERS THEN
325 RETURN FALSE;
326 END;
327 END IF;
328 END VALIDATE_TYPE;
329
330 PROCEDURE EXEC_SQL (STRING IN VARCHAR2) IS
331 CUR INTEGER;
332 RET INTEGER;
333 BEGIN
334 CUR := DBMS_SQL.OPEN_CURSOR;
335 DBMS_SQL.PARSE(CUR, STRING, DBMS_SQL.NATIVE);
336 RET := DBMS_SQL.EXECUTE(CUR);
337 DBMS_SQL.CLOSE_CURSOR(CUR);
338
339 exception when others then
340 IF dbms_sql.is_open(cur) THEN
341 dbms_sql.close_cursor(cur);
342 END IF;
343 raise;
344 END EXEC_SQL;
345
346 FUNCTION DECODE_ACTION_VALUE_LOOKUP (NUM NUMBER) RETURN VARCHAR2 IS
347 BEGIN
348 IF NUM = 1 THEN
349 RETURN 'UPPER_REASONABLE_LIMIT';
350 ELSIF NUM = 2 THEN
351 RETURN 'UPPER_SPEC_LIMIT';
352 ELSIF NUM = 3 THEN
353 RETURN 'UPPER_USER_DEFINED_LIMIT';
354 ELSIF NUM = 4 THEN
355 RETURN 'TARGET_VALUE';
356 ELSIF NUM = 5 THEN
357 RETURN 'LOWER_USER_DEFINED_LIMIT';
358 ELSIF NUM = 6 THEN
359 RETURN 'LOWER_SPEC_LIMIT';
360 ELSIF NUM = 7 THEN
361 RETURN 'LOWER_REASONABLE_LIMIT';
362 END IF;
363 END DECODE_ACTION_VALUE_LOOKUP;
364
365 FUNCTION DECODE_OPERATOR (OP NUMBER) RETURN VARCHAR2 IS
366 BEGIN
367 IF OP = 1 THEN
368 RETURN '=';
369 ELSIF OP = 2 THEN
370 RETURN '<>';
371 ELSIF OP = 3 THEN
372 RETURN '>=';
373 ELSIF OP = 4 THEN
374 RETURN '<=';
375 ELSIF OP = 5 THEN
376 RETURN '>';
377 ELSIF OP = 6 THEN
378 RETURN '<';
379 ELSIF OP = 7 THEN
380 RETURN 'IS NOT NULL';
381 ELSIF OP = 8 THEN
382 RETURN 'IS NULL';
383 ELSIF OP = 9 THEN
384 RETURN 'BETWEEN';
385 ELSIF OP = 10 THEN
386 RETURN 'NOT BETWEEN';
387 END IF;
388 END DECODE_OPERATOR;
389
390 /* validate_disabled
391 *
392 * writes an error to the errors table for each row in the interface table
393 * that has a non-null value for a particular disabled element (col_name).
394 * call this procedure only for disabled elements.
395 */
396
397 PROCEDURE VALIDATE_DISABLED(COL_NAME VARCHAR2,
398 ERROR_COL_NAME VARCHAR2,
399 ERROR_MESSAGE VARCHAR2,
400 X_GROUP_ID NUMBER,
401 X_USER_ID NUMBER,
402 X_LAST_UPDATE_LOGIN NUMBER,
403 X_REQUEST_ID NUMBER,
404 X_PROGRAM_APPLICATION_ID NUMBER,
405 X_PROGRAM_ID NUMBER) IS
406 SQL_STATEMENT VARCHAR2(2000);
407 QUOTED_COL_NAME VARCHAR2(50);
408 BEGIN
409 QUOTED_COL_NAME := '''' || COL_NAME || '''';
410
411 -- Bug 3136107.
412 -- SQL Bind project. Code modified to use bind variables instead of literals
413 -- Same as the fix done for Bug 3079312.suramasw.
414
415 SQL_STATEMENT :=
416 'INSERT INTO QA_INTERFACE_ERRORS (TRANSACTION_INTERFACE_ID, ' ||
417 'ERROR_COLUMN, ERROR_MESSAGE, LAST_UPDATE_DATE, LAST_UPDATED_BY, ' ||
418 'CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, REQUEST_ID, ' ||
419 'PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE) ' ||
420 'SELECT QRI.TRANSACTION_INTERFACE_ID, :ERROR_COL_NAME, :ERROR_MESSAGE, ' ||
421 'SYSDATE, :USER_ID, SYSDATE, :USER_ID2 , :LAST_UPDATE_LOGIN , ' ||
422 ':REQUEST_ID, :PROGRAM_APPLICATION_ID , :PROGRAM_ID, SYSDATE ' ||
423 'FROM QA_RESULTS_INTERFACE QRI ' ||
424 'WHERE QRI.GROUP_ID = :GROUP_ID ' ||
425 ' AND QRI.PROCESS_STATUS = 2 ' ||
426 'AND NOT EXISTS
427 (SELECT ''X'' ' ||
428 'FROM QA_INTERFACE_ERRORS QIE ' ||
429 'WHERE QIE.TRANSACTION_INTERFACE_ID = ' ||
430 'QRI.TRANSACTION_INTERFACE_ID ' ||
431 'AND QIE.ERROR_COLUMN IN (' ||
432 QUOTED_COL_NAME || ', NULL)) ' ||
433 'AND EXISTS ' ||
434 '(SELECT ''X'' FROM QA_RESULTS_INTERFACE ' ||
435 'WHERE QRI.' || COL_NAME || ' IS NOT NULL)';
436
437 EXECUTE IMMEDIATE SQL_STATEMENT USING ERROR_COL_NAME,
438 ERROR_MESSAGE,
439 X_USER_ID,
440 X_USER_ID,
441 X_LAST_UPDATE_LOGIN,
442 X_REQUEST_ID,
443 X_PROGRAM_APPLICATION_ID,
444 X_PROGRAM_ID,
445 X_GROUP_ID;
446
447 --QLTTRAFB.EXEC_SQL(SQL_STATEMENT);
448 END VALIDATE_DISABLED;
449
450 /* validate_mandatory
451 *
452 * writes an error to the errors table for each row in the interface table
453 * that has a null value for a particular mandatory element. call this
454 * procedure only for mandatory elements.
455 */
456
457 PROCEDURE VALIDATE_MANDATORY(COL_NAME VARCHAR2,
458 ERROR_COL_NAME VARCHAR2,
459 ERROR_MESSAGE VARCHAR2,
460 X_GROUP_ID NUMBER,
461 X_USER_ID NUMBER,
462 X_LAST_UPDATE_LOGIN NUMBER,
463 X_REQUEST_ID NUMBER,
467 ERROR_COL_LIST VARCHAR2) IS
464 X_PROGRAM_APPLICATION_ID NUMBER,
465 X_PROGRAM_ID NUMBER,
466 PARENT_COL_NAME VARCHAR2,
468 SQL_STATEMENT VARCHAR2(2000);
469
470 l_col1 VARCHAR2(100);
471 l_col2 VARCHAR2(100);
472 l_col3 VARCHAR2(100);
473 l_col4 VARCHAR2(100);
474 l_col5 VARCHAR2(100);
475
476 null_num number := fnd_api.G_NULL_NUM;
477 null_char varchar2(1) := fnd_api.G_NULL_char;
478 null_date date := fnd_api.G_NULL_date;
479
480 BEGIN
481
482 -- Bug 3136107.
483 -- SQL Bind project. Code modified to use bind variables instead of literals
484 -- Same as the fix done for Bug 3079312.suramasw.
485
486 parse_error_columns(error_col_list, l_col1, l_col2, l_col3, l_col4, l_col5);
487
488 SQL_STATEMENT :=
489 'INSERT INTO QA_INTERFACE_ERRORS (TRANSACTION_INTERFACE_ID, ' ||
490 'ERROR_COLUMN, ERROR_MESSAGE, LAST_UPDATE_DATE, LAST_UPDATED_BY, ' ||
491 'CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, REQUEST_ID, ' ||
492 'PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE) ' ||
493 'SELECT QRI.TRANSACTION_INTERFACE_ID, :ERROR_COL_NAME, :ERROR_MESSAGE, '||
494 'SYSDATE, :USER_ID, SYSDATE, :USER_ID2, :LAST_UPDATE_LOGIN, ' ||
495 ':REQUEST_ID, :PROGRAM_APPLICATION_ID, :PROGRAM_ID, SYSDATE ' ||
496 'FROM QA_RESULTS_INTERFACE QRI ' ||
497 'WHERE QRI.GROUP_ID = :GROUP_ID ' ||
498 ' AND QRI.PROCESS_STATUS = 2 ';
499
500 IF (PARENT_COL_NAME IS NOT NULL) THEN
501 SQL_STATEMENT := SQL_STATEMENT ||
502 ' AND QRI.' || PARENT_COL_NAME || ' IS NOT NULL ';
503 END IF;
504
505 -- bug 10212823
506 SQL_STATEMENT := SQL_STATEMENT ||
507 'AND NOT EXISTS
508 (SELECT ''X'' ' ||
509 'FROM QA_INTERFACE_ERRORS QIE ' ||
510 'WHERE QIE.TRANSACTION_INTERFACE_ID = ' ||
511 'QRI.TRANSACTION_INTERFACE_ID ' ||
512 'AND QIE.ERROR_COLUMN IN (:c1,:c2,:c3,:c4,:c5)) ' ||
513 'AND EXISTS ' ||
514 '(SELECT ''X'' FROM QA_RESULTS_INTERFACE ' ||
515 'WHERE QRI.' || COL_NAME || ' IS NULL '||
516 ' OR QRI.' || COL_NAME || ' IN (to_char(:null_num), :null_char, to_char(:null_date)))';
517
518
519 EXECUTE IMMEDIATE SQL_STATEMENT USING ERROR_COL_NAME,
520 ERROR_MESSAGE,
521 X_USER_ID,
522 X_USER_ID,
523 X_LAST_UPDATE_LOGIN,
524 X_REQUEST_ID,
525 X_PROGRAM_APPLICATION_ID,
526 X_PROGRAM_ID,
527 X_GROUP_ID,
528 l_col1, l_col2, l_col3, l_col4, l_col5,
529 NULL_NUM,
530 NULL_CHAR,
531 NULL_DATE;
532
533 -- QLTTRAFB.EXEC_SQL(SQL_STATEMENT);
534 END VALIDATE_MANDATORY;
535
536
537 PROCEDURE VALIDATE_LOOKUPS(COL_NAME VARCHAR2,
538 ERROR_COL_NAME VARCHAR2,
539 ERROR_MESSAGE VARCHAR2,
540 X_GROUP_ID NUMBER,
541 X_USER_ID NUMBER,
542 X_LAST_UPDATE_LOGIN NUMBER,
543 X_REQUEST_ID NUMBER,
544 X_PROGRAM_APPLICATION_ID NUMBER,
545 X_PROGRAM_ID NUMBER,
546 X_CHAR_ID NUMBER,
547 X_PLAN_ID NUMBER,
548 ERROR_COL_LIST VARCHAR2) IS
549 SQL_STATEMENT VARCHAR2(2000);
550
551 l_col1 VARCHAR2(100);
552 l_col2 VARCHAR2(100);
553 l_col3 VARCHAR2(100);
554 l_col4 VARCHAR2(100);
555 l_col5 VARCHAR2(100);
556
557 null_num number := fnd_api.G_NULL_NUM;
558 null_char varchar2(1) := fnd_api.G_NULL_char;
559 null_date date := fnd_api.G_NULL_date;
560
561 BEGIN
562
563 -- Bug 3136107.
564 -- SQL Bind project. Code modified to use bind variables instead of literals
565 -- Same as the fix done for Bug 3079312.suramasw.
566
567 parse_error_columns(error_col_list, l_col1, l_col2, l_col3, l_col4, l_col5);
568
569 -- bug 10212823
570 SQL_STATEMENT :=
571 'INSERT INTO QA_INTERFACE_ERRORS (TRANSACTION_INTERFACE_ID, ' ||
572 'ERROR_COLUMN, ERROR_MESSAGE, LAST_UPDATE_DATE, LAST_UPDATED_BY, ' ||
573 'CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, REQUEST_ID, ' ||
574 'PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE) ' ||
575 'SELECT QRI.TRANSACTION_INTERFACE_ID, :ERROR_COL_NAME, :ERROR_MESSAGE, '||
576 'SYSDATE, :USER_ID, SYSDATE, :USER_ID2, :LAST_UPDATE_LOGIN, ' ||
577 ':REQUEST_ID, :PROGRAM_APPLICATION_ID, :PROGRAM_ID, SYSDATE ' ||
578 'FROM QA_RESULTS_INTERFACE QRI ' ||
579 'WHERE QRI.GROUP_ID = :GROUP_ID ' ||
580 ' AND QRI.PROCESS_STATUS = 2 ' ||
581 'AND NOT EXISTS
582 (SELECT ''X'' ' ||
583 'FROM QA_INTERFACE_ERRORS QIE ' ||
584 'WHERE QIE.TRANSACTION_INTERFACE_ID = ' ||
585 'QRI.TRANSACTION_INTERFACE_ID ' ||
586 'AND QIE.ERROR_COLUMN IN (:c1,:c2,:c3,:c4,:c5)) ' ||
587 'AND QRI.' || COL_NAME || ' NOT IN ' ||
588 '(SELECT SHORT_CODE FROM QA_PLAN_CHAR_VALUE_LOOKUPS ' ||
592
589 'WHERE PLAN_ID = :PLAN_ID ' ||
590 ' AND CHAR_ID = :CHAR_ID ' || ')' ||
591 ' AND QRI.' || COL_NAME || ' NOT IN (to_char(:null_num), :null_char, to_char(:null_date))';
593 EXECUTE IMMEDIATE SQL_STATEMENT USING ERROR_COL_NAME,
594 ERROR_MESSAGE,
595 X_USER_ID,
596 X_USER_ID,
597 X_LAST_UPDATE_LOGIN,
598 X_REQUEST_ID,
599 X_PROGRAM_APPLICATION_ID,
600 X_PROGRAM_ID,
601 X_GROUP_ID,
602 l_col1, l_col2, l_col3, l_col4, l_col5,
603 X_PLAN_ID,
604 X_CHAR_ID,
605 null_num,
606 null_char,
607 null_date;
608
609 -- QLTTRAFB.EXEC_SQL(SQL_STATEMENT);
610 END VALIDATE_LOOKUPS;
611
612
613 PROCEDURE VALIDATE_PARENT_ENTERED(COL_NAME VARCHAR2,
614 ERROR_COL_NAME VARCHAR2,
615 ERROR_MESSAGE VARCHAR2,
616 X_GROUP_ID NUMBER,
617 X_USER_ID NUMBER,
618 X_LAST_UPDATE_LOGIN NUMBER,
619 X_REQUEST_ID NUMBER,
620 X_PROGRAM_APPLICATION_ID NUMBER,
621 X_PROGRAM_ID NUMBER,
622 PARENT_COL_NAME VARCHAR2,
623 ERROR_COL_LIST VARCHAR2) IS
624 SQL_STATEMENT VARCHAR2(2000);
625
626 l_col1 VARCHAR2(100);
627 l_col2 VARCHAR2(100);
628 l_col3 VARCHAR2(100);
629 l_col4 VARCHAR2(100);
630 l_col5 VARCHAR2(100);
631
632 BEGIN
633
634 -- Bug 3136107.
635 -- SQL Bind project. Code modified to use bind variables instead of literals
636 -- Same as the fix done for Bug 3079312.suramasw.
637
638 parse_error_columns(error_col_list, l_col1, l_col2, l_col3, l_col4, l_col5);
639
640 SQL_STATEMENT :=
641 'INSERT INTO QA_INTERFACE_ERRORS (TRANSACTION_INTERFACE_ID, ' ||
642 'ERROR_COLUMN, ERROR_MESSAGE, LAST_UPDATE_DATE, LAST_UPDATED_BY, ' ||
643 'CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, REQUEST_ID, ' ||
644 'PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE) ' ||
645 'SELECT QRI.TRANSACTION_INTERFACE_ID,:ERROR_COL_NAME, :ERROR_MESSAGE,' ||
646 'SYSDATE, :USER_ID, SYSDATE, :USER_ID2, :LAST_UPDATE_LOGIN, ' ||
647 ':REQUEST_ID, :PROGRAM_APPLICATION_ID, :PROGRAM_ID, SYSDATE ' ||
648 'FROM QA_RESULTS_INTERFACE QRI ' ||
649 'WHERE QRI.GROUP_ID = :GROUP_ID ' ||
650 ' AND QRI.PROCESS_STATUS = 2 ' ||
651 'AND NOT EXISTS
652 (SELECT ''X'' ' ||
653 'FROM QA_INTERFACE_ERRORS QIE ' ||
654 'WHERE QIE.TRANSACTION_INTERFACE_ID = ' ||
655 'QRI.TRANSACTION_INTERFACE_ID ' ||
656 'AND QIE.ERROR_COLUMN IN (:c1,:c2,:c3,:c4,:c5)) ' ||
657 'AND EXISTS ' ||
658 '(SELECT ''X'' FROM QA_RESULTS_INTERFACE ' ||
659 'WHERE QRI.' || COL_NAME || ' IS NOT NULL ' ||
660 'AND QRI.' || PARENT_COL_NAME || ' IS NULL)';
661
662 EXECUTE IMMEDIATE SQL_STATEMENT USING ERROR_COL_NAME,
663 ERROR_MESSAGE,
664 X_USER_ID,
665 X_USER_ID,
666 X_LAST_UPDATE_LOGIN,
667 X_REQUEST_ID,
668 X_PROGRAM_APPLICATION_ID,
669 X_PROGRAM_ID,
670 X_GROUP_ID,
671 l_col1, l_col2, l_col3, l_col4, l_col5;
672
673 -- QLTTRAFB.EXEC_SQL(SQL_STATEMENT);
674 END VALIDATE_PARENT_ENTERED;
675
676 -- Tracking Bug : 3104827. Review Tracking Bug : 3148873
677 -- Added for Read Only for Flag Collection Plan Elements
678 -- saugupta Thu Aug 28 08:59:59 PDT 2003
679
680 PROCEDURE VALIDATE_READ_ONLY(P_COL_NAME VARCHAR2,
681 P_ERROR_COL_NAME VARCHAR2,
682 P_ERROR_MESSAGE VARCHAR2,
683 P_GROUP_ID NUMBER,
684 P_USER_ID NUMBER,
685 P_LAST_UPDATE_LOGIN NUMBER,
686 P_REQUEST_ID NUMBER,
687 P_PROGRAM_APPLICATION_ID NUMBER,
688 P_PROGRAM_ID NUMBER,
689 P_PARENT_COL_NAME VARCHAR2,
690 P_ERROR_COL_LIST VARCHAR2) IS
691 SQL_STATEMENT VARCHAR2(2000);
692
693 l_col1 VARCHAR2(100);
694 l_col2 VARCHAR2(100);
695 l_col3 VARCHAR2(100);
696 l_col4 VARCHAR2(100);
697 l_col5 VARCHAR2(100);
698
699 BEGIN
700
701 -- Bug 3136107.
702 -- SQL Bind project. Code modified to use bind variables instead of literals
703 -- Same as the fix done for Bug 3079312.suramasw.
704
705 parse_error_columns(p_error_col_list, l_col1, l_col2, l_col3, l_col4, l_col5);
706
707 SQL_STATEMENT :=
708 'INSERT INTO QA_INTERFACE_ERRORS (TRANSACTION_INTERFACE_ID, ' ||
709 'ERROR_COLUMN, ERROR_MESSAGE, LAST_UPDATE_DATE, LAST_UPDATED_BY, ' ||
710 'CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, REQUEST_ID, ' ||
711 'PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE) ' ||
715 'FROM QA_RESULTS_INTERFACE QRI ' ||
712 'SELECT QRI.TRANSACTION_INTERFACE_ID, :ERROR_COL_NAME, :ERROR_MESSAGE,' ||
713 'SYSDATE, :USER_ID, SYSDATE, :USER_ID2, :LAST_UPDATE_LOGIN, ' ||
714 ':REQUEST_ID, :PROGRAM_APPLICATION_ID, :PROGRAM_ID, SYSDATE ' ||
716 'WHERE QRI.GROUP_ID = :GROUP_ID ' ||
717 ' AND QRI.PROCESS_STATUS = 2 ';
718
719 IF (P_PARENT_COL_NAME IS NOT NULL) THEN
720 SQL_STATEMENT := SQL_STATEMENT ||
721 ' AND QRI.' || P_PARENT_COL_NAME || ' IS NOT NULL ';
722 END IF;
723
724 SQL_STATEMENT := SQL_STATEMENT ||
725 'AND NOT EXISTS
726 (SELECT ''X'' ' ||
727 'FROM QA_INTERFACE_ERRORS QIE ' ||
728 'WHERE QIE.TRANSACTION_INTERFACE_ID = ' ||
729 'QRI.TRANSACTION_INTERFACE_ID ' ||
730 'AND QIE.ERROR_COLUMN IN (:c1,:c2,:c3,:c4,:c5)) ' ||
731 'AND EXISTS ' ||
732 '(SELECT ''X'' FROM QA_RESULTS_INTERFACE ' ||
733 'WHERE QRI.' || P_COL_NAME || ' IS NOT NULL)';
734
735 EXECUTE IMMEDIATE SQL_STATEMENT USING P_ERROR_COL_NAME,
736 P_ERROR_MESSAGE,
737 P_USER_ID,
738 P_USER_ID,
739 P_LAST_UPDATE_LOGIN,
740 P_REQUEST_ID,
741 P_PROGRAM_APPLICATION_ID,
742 P_PROGRAM_ID,
743 P_GROUP_ID,
744 l_col1, l_col2, l_col3, l_col4, l_col5;
745
746 -- QLTTRAFB.EXEC_SQL(SQL_STATEMENT);
747 END VALIDATE_READ_ONLY;
748
749
750
751
752 END QLTTRAFB;
753