1 PACKAGE BODY QLTVCREB AS
2 /* $Header: qltvcreb.plb 120.6.12010000.2 2008/09/15 09:37:29 ntungare ship $ */
3
4 -- executes a sql statement dynamically
5
6 -- A constant to fool GSCC. See bug 3554899
7 -- bso Wed Apr 7 22:27:11 PDT 2004
8 g_period CONSTANT VARCHAR2(1) := '.';
9
10 --
11 -- The following set functions is an effort to extend the capability
12 -- of the global_view procedure, so it can handle view definitions
13 -- longer than 32K, the previous hard limit (because of PL/SQL varchar2
14 -- string limit). Now, the DBMS_SQL.Varchar2s type is used for storing
15 -- the view definition. This is a table and is the data type used by
16 -- AD_DDL.DO_ARRAY_DDL.
17 --
18 -- April 22, 1998. bso
19 --
20
21 --
22 -- Apps schema info
23 --
24 g_dummy BOOLEAN;
25 g_fnd CONSTANT VARCHAR2(3) := 'FND';
26 g_status VARCHAR2(1);
27 g_industry VARCHAR2(10);
28 g_schema VARCHAR2(30);
29
30 /*
31 PROCEDURE table_print(A in DBMS_SQL.varchar2s) IS
32 -- Debug procedure, not used in production.
33 -- bso
34 i binary_integer;
35 BEGIN
36 --FOR i IN 1..A.count LOOP
37 -- DBMS_OUTPUT.put_line(A(i));
38 --END LOOP;
39 FOR i IN 1..A.count LOOP
40 insert into bso(l1, n1) values(A(i), i);
41 END LOOP;
42 commit;
43 END;
44 */
45
46 --
47 -- bug 7409976
48 -- New type to hold the privileges granted on a view
49 -- ntungare
50 --
51 TYPE grants_tab_typ IS TABLE OF VARCHAR2(32767) INDEX BY binary_integer;
52
53 PROCEDURE create_clause(s in out NOCOPY DBMS_SQL.varchar2s, f in DBMS_SQL.varchar2s,
54 keyword varchar2) IS
55 --
56 -- Add to s the content of f to make f the FROM or WHERE clause of s.
57 -- This procedure replaces the QLTSTORB.Create_From/Where_Clause procedures
58 -- because that one does not allow a clause that's > 32k.
59 --
60 -- s is the sql string in dbms_sql.varchar2s
61 -- f is the list of from or where clauses
62 -- keyword is either 'FROM' or 'WHERE'
63 --
64 -- bso
65 --
66 i binary_integer;
67 sep varchar2(10);
68 BEGIN
69 IF f.count > 0 THEN
70 IF keyword = 'FROM' THEN
71 sep := ', ';
72 ELSE
73 sep := ' AND ';
74 END IF;
75 i := s.count + 1;
76 s(i) := ' ' || keyword || ' ' || f(1);
77
78 FOR j IN 2..f.count LOOP
79 i := i + 1;
80 s(i) := sep || f(j);
81 END LOOP;
82 END IF;
83 END;
84
85
86 PROCEDURE exec_ddl_table(schema in varchar2, shortname in varchar2,
87 cmd in integer, statement in DBMS_SQL.varchar2s, name in varchar2) IS
88 -- Execute a data definition statement by using the AD_DDL.DO_ARRAY_DDL
89 -- function. See Bug 574078. Release 10.7 requires this patch to work.
90 -- bso
91
92 --
93 -- Bug 3756235
94 -- This is a performance fix. A global view usually has tens of
95 -- thousands of "statements" because each statement(i) is a short
96 -- string. Thus, we have been calling ad_ddl.build_statement a
97 -- lot. The no. of calls can be reduced 10-fold if we concat the
98 -- shorter strings into a long one before invoking the API.
99 --
100 -- 255 is the current max size of build_statement's input param.
101 -- If this limit is lifted, this constant and the variable can
102 -- be changed accordingly and the code will adapt to use the
103 -- larger buffer for even better performance. See AD's bug or
104 -- enhancement 3754657.
105 --
106 -- bso Sat Jul 24 16:17:15 PDT 2004
107 --
108 max_size CONSTANT NUMBER := 255;
109 s VARCHAR2(255);
110 n INTEGER;
111
112 BEGIN
113 --table_print(statement);
114
115 n := 0;
116 FOR i IN 1..statement.count LOOP
117 --
118 -- check if we have enough room in s to take in more statements
119 -- if not, we should call build_statement to process the previous
120 -- concatenated string.
121 -- bso Sat Jul 24 16:17:15 PDT 2004
122 --
123 -- check the size of the statement using lengthb function instead of
124 -- length, to handle NLS characters.
125 -- vvs BUG 4129987 Wed Mar 2 04:48:07 PST 2005
126 --
127 IF nvl(lengthb(s), 0) + nvl(lengthb(statement(i)), 0) + 1 > max_size THEN
128 n := n + 1;
129 AD_DDL.build_statement(s, n);
130 s := '';
131 END IF;
132
133 s := s || statement(i) || ' ';
134 END LOOP;
135
136 --
137 -- Because the last command in the loop is a string concatenation,
138 -- we will always have a final "s" to process here.
139 -- bso Sat Jul 24 16:17:15 PDT 2004
140 --
141 n := n + 1;
142 AD_DDL.build_statement(s, n);
143
144 AD_DDL.do_array_ddl(schema, shortname, cmd, 1, n, name);
145 END;
146
147
148 FUNCTION trans(name VARCHAR2) RETURN VARCHAR2 IS
149 --
150 -- The translate is there for NLS fix and pseudo-trans fix.
151 -- Problem is, we allow user to enter single quote in NLS fix.
152 -- Pseudo-trans will also translate element names into names
153 -- with asterisks and braces. All these will cause error in
154 -- Discoverer Work Book (although they actually won't cause
155 -- problem in our view generation code). Therefore, translate
156 -- them into underscores to help Discoverer out.
157 --
158 BEGIN
159 RETURN upper(translate(name, ' ''"*{}', '______'));
160 END trans;
161
162
163 PROCEDURE drop_view(x VARCHAR2) IS
164 BEGIN
165 ad_ddl.do_ddl(g_schema, 'QA', ad_ddl.drop_view,
166 'DROP VIEW "' || upper(x) || '"', x);
167 EXCEPTION WHEN OTHERS THEN
168 NULL;
169 END drop_view;
170
171
172 FUNCTION contains(a dbms_sql.number_table, x NUMBER) RETURN NUMBER IS
173 BEGIN
174 FOR i IN a.first .. a.last LOOP
175 IF a(i) = x THEN
176 RETURN i;
177 END IF;
178 END LOOP;
179 RETURN -1;
180 END contains;
181
182
183 PROCEDURE global_view(x_view_name IN VARCHAR2) IS
184
185 --
186 -- Complete rewrite on Thu Dec 7 14:08:03 PST 2000
187 -- bso
188 --
189
190 --
191 -- Used to construct the final dynamic sql.
192 --
193 v_sql_table dbms_sql.varchar2s;
194 v_from_table dbms_sql.varchar2s;
195 v_where_table dbms_sql.varchar2s;
196
197
198 --
199 -- Maximum no. of columns allowed in a view.
200 --
201 max_columns CONSTANT NUMBER := 1000;
202
203 --
204 -- No. of fixed, seeded columns to be included in the beginning of the view
205 --
206 fixed_columns CONSTANT NUMBER := 14;
207 column_count NUMBER;
208
209 --
210 -- Bug 1357601. The decode statement used to "straighten" softcoded
211 -- elements into a single column has a sever limit of 255 parameters.
212 -- These variables are added to resolve the limit. When the limit is
213 -- up, we use the very last parameter of the decode statement to
214 -- start a new decode, which can have another 255 params. This is
215 -- repeated as necessary.
216 --
217 -- decode_count keeps the no. of decodes being used so far.
218 -- decode_param keeps the no. of parameters in the current decode.
219 -- decode_limit is the server limit. This should be updated if
220 -- the server is enhanced in the future.
221 --
222 -- bso Thu Sep 21 13:11:19 PDT 2000
223 --
224 decode_count NUMBER;
225 decode_param NUMBER;
226 decode_limit CONSTANT NUMBER := 255;
227
228 --
229 -- Bug 4958779
230 -- This cursor is not performing. Removed and fetch element
231 -- info on-the-fly.
232 -- bso Tue Jan 31 14:57:53 PST 2006
233 --
234 -- CURSOR elements_cursor IS
235 -- SELECT qc.char_id, qc.name, qc.hardcoded_column, qc.datatype
236 -- FROM qa_chars qc
237 -- WHERE qc.enabled_flag = 1 and
238 -- rownum < (max_columns - fixed_columns)
239 -- ORDER BY qc.hardcoded_column, qc.name;
240 --
241 -- The elements to be included in the final view.
242 --
243 -- element_ids dbms_sql.number_table;
244 -- element_names dbms_sql.varchar2s;
245 -- element_hardcoded_columns dbms_sql.varchar2s;
246 -- element_datatypes dbms_sql.number_table;
247 --
248 l_element_id NUMBER;
249 l_element_name qa_chars.name%TYPE;
250 l_element_hardcoded_column qa_chars.hardcoded_column%TYPE;
251 l_element_datatype NUMBER;
252
253 --
254 -- This cursor is used to fetch foreign key info for hardcoded,
255 -- normalized elements.
256 --
257 CURSOR fk_cursor(x NUMBER) IS
258 SELECT qc.fk_table_name,
259 qc.fk_table_short_name,
260 qc.fk_lookup_type,
261 qc.pk_id,
262 qc.fk_id,
263 qc.pk_id2,
264 qc.fk_id2,
265 qc.pk_id3,
266 qc.fk_id3,
267 qc.fk_meaning,
268 qc.fk_description,
269 qc.fk_add_where
270 FROM qa_chars qc
271 WHERE qc.char_id = x;
272
273 fk fk_cursor%ROWTYPE;
274
275 --
276 -- This cursor loops through qa_plan_chars to find all
277 -- plan_id and result_column_name for each collection element.
278 --
279 -- The decode() function and distinct are there to cleverly
280 -- eliminate all but one row of hardcoded elements. We only
281 -- need one row for each hardcoded ones.
282 --
283 CURSOR plan_cursor IS
284 SELECT
285 /*
286 This is a batch program that needs to loop through
287 all collection plans to generate a global view.
288 Full table scan is expected. Bryan So 1/31/2006
289 */
290 distinct qpc.char_id,
291 decode(qc.hardcoded_column, null, qpc.plan_id, 0) plan_id,
292 upper(qpc.result_column_name) result_column_name,
293 qc.name,
294 qc.hardcoded_column
295 FROM qa_plan_chars qpc, qa_chars qc
296 WHERE qc.char_id = qpc.char_id AND
297 qpc.enabled_flag = 1 AND
298 qc.enabled_flag = 1
299 ORDER BY qc.hardcoded_column, qc.name;
300
301 pc plan_cursor%ROWTYPE;
302
303 current_element NUMBER;
304 temp VARCHAR2(255);
305 n INTEGER;
306
307 i INTEGER; -- I used these as counters for the
308 j INTEGER; -- following lists: select list (v_sql_table),
309 k INTEGER; -- from list, where list
310 --
311 -- I have used generic function such as table_add to an element
312 -- to a PL/SQL table... it is way too slow. 3 times difference
313 -- than using simple variable counters.
314 --
315
316 BEGIN
317
318 --
319 -- This is the beginning of the global view.
320 --
321
322 v_sql_table(1) := 'CREATE OR REPLACE FORCE VIEW ' || x_view_name;
323 v_sql_table(2) := ' AS SELECT ';
324 v_sql_table(3) := ' qr.rowid row_id,'; -- 1
325 v_sql_table(4) := ' qr.plan_id,'; -- 2
326 v_sql_table(5) := ' qp'||g_period||'name plan_name,'; -- 3 see bug 3554899
327 v_sql_table(6) := ' qr.organization_id,'; -- 4
328 v_sql_table(7) := ' hou.name organization_name,'; -- 5
329 v_sql_table(8) := ' qr.collection_id,'; -- 6
330 v_sql_table(9) := ' qr.occurrence,'; -- 7
331 v_sql_table(10) := ' qr.qa_last_update_date last_update_date,'; -- 8
332 v_sql_table(11) := ' qr.qa_last_updated_by last_update_by_id,'; -- 9
333 v_sql_table(12) := ' fu2.user_name last_updated_by,'; -- 10
334 v_sql_table(13) := ' qr.qa_creation_date creation_date,'; -- 11
335 v_sql_table(14) := ' qr.qa_created_by created_by_id,'; -- 12
336 v_sql_table(15) := ' fu.user_name created_by,'; -- 13
337 v_sql_table(16) := ' qr.last_update_login'; -- 14
338 i := 17;
339 column_count := fixed_columns; -- 14
340
341 -- Then add the necessary where and froms
342 v_from_table(1) := 'qa_results qr';
343 v_from_table(2) := 'qa_plans qp';
344 v_from_table(3) := 'fnd_user_view fu';
345 v_from_table(4) := 'fnd_user_view fu2';
346 v_from_table(5) := 'hr_organization_units hou';
347 j := 6;
348
349 v_where_table(1) := 'qp'||g_period||'plan_id = qr.plan_id'; -- see bug 3554899
350 v_where_table(2) := 'qr.qa_created_by = fu.user_id';
351 v_where_table(3) := 'qr.qa_last_updated_by = fu2.user_id';
352 v_where_table(4) := 'qr.organization_id = hou.organization_id';
353 v_where_table(5) := '(qr.status IS NULL OR qr.status = 2)';
354 k := 6;
355
356 --
357 -- Bug 4958779
358 -- This cursor is not performing. Removed and fetch element
359 -- info on-the-fly.
360 -- bso Tue Jan 31 14:57:53 PST 2006
361 --
362 -- -- Collection all elements to be included in the view.
363 -- --
364 -- OPEN elements_cursor;
365 --
366 -- FETCH elements_cursor BULK COLLECT INTO element_ids, element_names,
367 -- element_hardcoded_columns, element_datatypes;
368 --
369 -- CLOSE elements_cursor;
370 --
371
372 --
373 -- One option is to go through each element, but it is slightly
374 -- more efficient to go through qa_plan_chars to avoid performing
375 -- too many database parses.
376 --
377 -- FOR element IN elements_cursor LOOP
378 --
379 OPEN plan_cursor;
380 FETCH plan_cursor INTO pc;
381
382 LOOP
383 EXIT WHEN column_count > (max_columns-2); -- reserve 2 columns
384
385 EXIT WHEN plan_cursor%NOTFOUND;
386
387 --
388 -- We will do the following only if element appears in the given
389 -- element ID list.
390 --
391 current_element := pc.char_id;
392
393 SELECT char_id, name, hardcoded_column, datatype
394 INTO l_element_id, l_element_name, l_element_hardcoded_column,
395 l_element_datatype
396 FROM qa_chars
397 WHERE char_id = current_element;
398
399 -- There are several conditions.
400
401 --
402 -- If the element has a specially-coded stored function, then
403 -- use the stored function...
404 --
405 IF current_element = qa_ss_const.sales_order THEN
406 v_sql_table(i) := ', qr.' || l_element_hardcoded_column;
407 i := i + 1;
408 v_sql_table(i) := ', qa_flex_util.sales_order(qr.so_header_id) "'
409 || trans(l_element_name) || '"';
410 i := i + 1;
411 column_count := column_count + 2;
412
413 ELSIF current_element = qa_ss_const.rma_number THEN
414 v_sql_table(i) := ', qr.' || l_element_hardcoded_column;
415 i := i + 1;
416 v_sql_table(i) := ', qa_flex_util.rma_number(qr.rma_header_id) "'
417 || trans(l_element_name) || '"';
418 i := i + 1;
419 column_count := column_count + 2;
420
421 ELSIF current_element = qa_ss_const.project_number THEN
425 || trans(l_element_name) || '"';
422 v_sql_table(i) := ', qr.' || l_element_hardcoded_column;
423 i := i + 1;
424 v_sql_table(i) := ', qa_flex_util.project_number(qr.project_id) "'
426 i := i + 1;
427 column_count := column_count + 2;
428
429 --
430 -- If the element is hardcoded, then simply use the hardcoded column.
431 --
432 ELSIF l_element_hardcoded_column IS NOT NULL THEN
433
434 --
435 -- a small complication is if the element is a foreign key,
436 -- such as ITEM_ID, then outer join to the foreign table.
437 --
438 OPEN fk_cursor(current_element);
439 FETCH fk_cursor INTO fk;
440
441 IF fk.fk_lookup_type IN (0, 1)
442 --
443 -- Safety check to make sure foreign key reference is
444 -- there. Otherwise, we will have a cross product with
445 -- no join condition!
446 --
447 AND fk.fk_id IS NOT NULL
448 AND fk.pk_id IS NOT NULL THEN
449 --
450 -- Here is where there is a foreign table. First add
451 -- the hardcoded column as is, so the ID column is present.
452 --
453 v_sql_table(i) := ', qr.' || l_element_hardcoded_column;
454 i := i + 1;
455
456 --
457 -- Then add the foreign select as a separate column.
458 --
459 v_sql_table(i) := ', ' || fk.fk_table_short_name || '.' ||
460 fk.fk_meaning || ' "' || trans(l_element_name) || '"';
461 i := i + 1;
462
463 -- Then the foreign table in the from clause
464 -- and finally the outer join in where clause.
465 --
466 v_from_table(j) :=
467 fk.fk_table_name || ' ' || fk.fk_table_short_name;
468 j := j + 1;
469
470 IF fk.pk_id IS NOT NULL AND fk.fk_id IS NOT NULL THEN
471 v_where_table(k) := 'qr.' || fk.fk_id || ' = ' ||
472 fk.fk_table_short_name || '.' || fk.pk_id || ' (+)';
473 k := k + 1;
474 END IF;
475
476 IF fk.pk_id2 IS NOT NULL AND fk.fk_id2 IS NOT NULL THEN
477 v_where_table(k) := 'qr.' || fk.fk_id2 || ' = ' ||
478 fk.fk_table_short_name || '.' || fk.pk_id2 || ' (+)';
479 k := k + 1;
480 END IF;
481
482 IF fk.pk_id3 IS NOT NULL AND fk.fk_id3 IS NOT NULL THEN
483 v_where_table(k) := 'qr.' || fk.fk_id3 || ' = ' ||
484 fk.fk_table_short_name || '.' || fk.pk_id3 || ' (+)';
485 k := k + 1;
486 END IF;
487
488 IF fk.fk_add_where IS NOT NULL THEN
489 v_where_table(k) := fk.fk_add_where;
490 k := k + 1;
491 END IF;
492
493 column_count := column_count + 2;
494
495 ELSE
496 --
497 -- Not foreign key ... great, simply add the hardcoded
498 -- column name, examples are LOT_NUMBER, QUANTITY...
499 --
500 v_sql_table(i) := ', qr.' || l_element_hardcoded_column ||
501 ' "' || trans(l_element_name) || '"';
502 i := i + 1;
503
504 column_count := column_count + 1;
505 END IF;
506
507 CLOSE fk_cursor;
508
509 ELSE -- Here, hardcoded_column IS NULL
510
511 --
512 -- Here the element must be softcoded. This is most interesting.
513 -- Need to use a bunch of decode statement and also correct the
514 -- canonical datatype to real datatype format.
515 --
516 -- For example, if defect code is assigned to CHARACTER2 in
517 -- plan 101 and assigned to CHARACTER5 in plan 103, the decode
518 -- statement will look like this:
519 --
520 -- decode(qr.plan_id, 101, character2, 103, character5) defect_code
521 --
522
523 decode_count := 0; -- see comments in variable declaration.
524 decode_param := decode_limit;
525
526 WHILE pc.char_id = current_element LOOP
527 --
528 -- If maximum no. of arguments to the "decode" function is
529 -- close to the server allowed 'decode_limit', then we want
530 -- to start a new tail-end decode statement.
531 --
532 IF decode_param >= (decode_limit - 2) THEN
533 v_sql_table(i) := ', decode(qr.plan_id';
534 i := i + 1;
535 decode_count := decode_count + 1;
536 decode_param := 1;
537 END IF;
538
539 --
540 -- CHARACTER column data are stored in canonical format.
541 -- Convert to real number/real date if appropriate.
542 --
543 IF l_element_datatype = 2 THEN
544
545 --
546 -- Need to create views with 12 decimal places
547 -- for number type elements. See Bug 2624112
551 -- ', ''9999999999999999999999999999999.999999'')';
548 -- rkunchal Wed Oct 16 05:32:33 PDT 2002
549 --
550 -- temp := 'to_number(qr.' || pc.result_column_name ||
552
553 temp := 'qltdate.any_to_number(qr.' || pc.result_column_name || ')';
554
555 ELSIF l_element_datatype = 3 THEN
556 temp := 'to_date(qr.' || pc.result_column_name ||
557 ', ''YYYY/MM/DD'')';
558 --
559 -- Bug 3179845. Added to iinclude datetime type for Timezone.
560 -- saugupta Tue Oct 14 05:08:00 PDT 2003
561 --
562 ELSIF l_element_datatype = 6 THEN
563 temp := 'to_date(qr.' || pc.result_column_name ||
564 ', ''YYYY/MM/DD HH24:MI:SS'')';
565 ELSE
566 temp := 'qr.' || pc.result_column_name;
567 END IF;
568
569 v_sql_table(i) := ', ' || pc.plan_id || ', ' || temp;
570 i := i + 1;
571 decode_param := decode_param + 2;
572
573 FETCH plan_cursor INTO pc;
574
575 EXIT WHEN plan_cursor%NOTFOUND;
576
577 END LOOP;
578
579 --
580 -- Close all decode() parenthesis
581 --
582 temp := '';
583 FOR x IN 1 .. decode_count LOOP
584 temp := temp || ')';
585 END LOOP;
586
587 v_sql_table(i) := temp || ' "' || trans(l_element_name) || '"';
588 i := i + 1;
589 column_count := column_count + 1;
590
591 END IF;
592
593 WHILE pc.char_id = current_element LOOP
594 FETCH plan_cursor INTO pc;
595 EXIT WHEN plan_cursor%NOTFOUND;
596 END LOOP;
597
598 END LOOP;
599
600 CLOSE plan_cursor;
601
602 -- create the from and where clause
603 create_clause(v_sql_table, v_from_table, 'FROM');
604 create_clause(v_sql_table, v_where_table, 'WHERE');
605
606 --
607 -- Find apps schema info. To be used in ad_ddl calls.
608 --
609 exec_ddl_table(g_schema, 'QA', AD_DDL.create_view, v_sql_table, x_view_name);
610
611 END global_view;
612
613 --
614 -- bug 7409976
615 -- New procedure to create a stack of
616 -- all the grants present on the views
617 -- ntungare
618 --
619 PROCEDURE create_grant_sql(p_view_name IN VARCHAR2,
620 x_grants_tab OUT NOCOPY grants_tab_typ) AS
621
622 TYPE privs_rec IS RECORD(grantee VARCHAR2(200),
623 obj_priv VARCHAR2(200),
624 grantable VARCHAR2(200));
625 TYPE privs_rec_tab_type IS TABLE OF privs_rec INDEX BY BINARY_INTEGER;
626 privs_rec_tab privs_rec_tab_type ;
627
628 grant_option VARCHAR2(2000):= ' WITH GRANT OPTION ';
629
630 grant_stmt VARCHAR2(4000);
631 BEGIN
632 SELECT grantee, privilege, grantable
633 BULK COLLECT INTO privs_rec_tab
634 FROM User_TAB_PRIVS
635 WHERE table_name = p_view_name
636 ORDER BY grantee;
637
638 FOR cntr in 1..privs_rec_tab.COUNT
639 LOOP
640 grant_stmt := 'GRANT '|| privs_rec_tab(cntr).obj_priv ||' ON '||p_view_name ||
641 ' TO ' || privs_rec_tab(cntr).grantee;
642
643 IF (privs_rec_tab(cntr).grantable = 'YES') THEN
644 grant_stmt := grant_stmt ||grant_option;
645 END IF;
646
647 x_grants_tab(cntr) := grant_stmt;
648 END LOOP;
649 END create_grant_sql;
650
651 -- Plan View creates a view of QA_RESULTS using the user defined names
652 -- for the Character fields found there
653
654 -- Kevin Wiggen September 22 1994
655
656
657 PROCEDURE plan_view(x_view_name IN VARCHAR2, x_old_view_name IN VARCHAR2,
658 x_plan_id IN NUMBER) IS
659
660 v_select VARCHAR2(20000);
661 v_from VARCHAR2(20000);
662 v_where VARCHAR2(20000);
663 v_final VARCHAR2(32000);
664
665 temp VARCHAR2(255);
666
667 CURSOR pcursor is
668 --
669 -- See comments in global_view
670 --
671 SELECT qc.char_id,
672 upper(translate(qc.name,' ''*{}','_____')) name,
673 qpc.result_column_name,
674 qc.hardcoded_column,
675 qc.FK_LOOKUP_TYPE,
676 qc.FK_TABLE_NAME,
677 qc.FK_TABLE_SHORT_NAME,
678 qc.PK_ID,
679 qc.FK_ID,
680 qc.PK_ID2,
681 qc.FK_ID2,
682 qc.PK_ID3,
683 qc.FK_ID3,
684 qc.FK_MEANING,
685 qc.FK_DESCRIPTION,
686 qc.FK_ADD_WHERE,
687 qc.DATATYPE
688 FROM qa_chars qc,
689 qa_plan_chars qpc
690 WHERE qc.char_id = qpc.char_id
691 AND qpc.plan_id = x_plan_id
692 ORDER BY qpc.prompt_sequence;
693
694 --
695 -- bug 6350575
696 -- 12.1 QWB Usability Improvements Project
697 --
701 v_deref_view_where VARCHAR2(20000);
698 v_plan_view_create VARCHAR2(2000);
699 v_deref_view_create VARCHAR2(2000);
700 v_deref_view_final VARCHAR2(32000);
702 v_deref_view_name VARCHAR2(30);
703
704 --
705 -- bug 7409976
706 -- ntungare
707 --
708 x_pv_grants_tab grants_tab_typ; -- Collection for grants on Plan View
709 x_dv_grants_tab grants_tab_typ; -- Collection for grants on Deref View
710 BEGIN
711 --
712 -- bug 6350575
713 -- 12. QWB Usability Improvements Project
714 -- Deriving the Deref view name
715 --
716 SELECT substr(import_view_name, 1, length(import_view_name)-2)||'DV'
717 INTO v_deref_view_name FROM qa_plans
718 WHERE plan_id = x_plan_id;
719
720 --
721 -- When QLTPLMDF deletes a plan, it calls this proc with
722 -- null x_view_name and with x_old_view_name populated.
723 -- Need to drop the old view name and return.
724 --
725 IF x_old_view_name IS NOT NULL THEN
726 --
727 -- bug 7409976
728 -- Getting a list of all the grants on the plan view
729 -- before dropping it
730 -- ntungare
731 --
732 create_grant_sql(p_view_name => x_old_view_name,
733 x_grants_tab => x_pv_grants_tab);
734
735 drop_view(x_old_view_name);
736
737 --
738 -- bug 7409976
739 -- Getting a list of all the grants on the deref view
740 -- before dropping it
741 -- ntungare
742 --
743 create_grant_sql(p_view_name => v_deref_view_name,
744 x_grants_tab => x_dv_grants_tab);
745
746 drop_view(v_deref_view_name);
747 END IF;
748
749 IF x_view_name IS NULL THEN
750 RETURN;
751 END IF;
752
753 --
754 -- bug 6350575
755 -- 12.1 QWB Usability Improvements Project
756 -- building the create clause for the plan view
757 --
758 v_plan_view_create := 'CREATE OR REPLACE FORCE VIEW "' || upper(x_view_name) || '" AS ';
759
760 --
761 -- bug 6350575
762 -- 12.1 QWB Usability Improvements Project
763 -- building the create clause for the plan view
764 --
765 v_deref_view_create := 'CREATE OR REPLACE FORCE VIEW "' || upper(v_deref_view_name) || '" AS ';
766
767 v_select := ' SELECT
768 qr.rowid row_id,
769 qr.plan_id,
770 qp'||g_period||'name plan_name,
771 qr.organization_id,
772 hou.name organization_name,
773 qr.collection_id,
774 qr.occurrence,
775 qr.qa_last_update_date last_update_date,
776 qr.qa_last_updated_by last_updated_by_id,
777 fu2.user_name last_updated_by,
778 qr.qa_creation_date creation_date,
779 qr.qa_created_by created_by_id,
780 fu.user_name created_by,
781 qr.last_update_login';
782
783 v_from := ' FROM qa_results qr,
784 qa_plans qp,
785 fnd_user_view fu,
786 fnd_user_view fu2,
787 hr_organization_units hou';
788
789 v_where := ' WHERE qp'||g_period||'plan_id = ' || x_plan_id || ' AND '||
790 --
791 -- bug 6044832
792 -- Added an additional where clause, so that
793 -- the index on QA_RESULTS is looked at, while
794 -- querying the view, even with high volume of
795 -- data
796 -- ntungare Mon Jul 16 03:02:23 PDT 2007
797 --
798 -- bug 6350575
799 -- 12.1 QWB USABILITY IMPROVEMENTS PROJECT
800 -- removed the status where caluse since it would be
801 -- added at the end
802 --
803 'qr'||g_period||'plan_id = '|| x_plan_id || ' AND
804 qp'||g_period||'plan_id = qr.plan_id AND
805 qr.qa_created_by = fu.user_id AND
806 qr.qa_last_updated_by = fu2.user_id AND
807 qr.organization_id = hou.organization_id';
808 --AND
809 --(qr.status IS NULL OR qr.status = 2)';
810
811 FOR fk in pcursor LOOP
812
813 IF fk.char_id = qa_ss_const.sales_order THEN
814 v_select := v_select || ', qr.' || fk.hardcoded_column;
815 v_select := v_select ||
816 ', qa_flex_util.sales_order(qr.so_header_id) "' || fk.name || '"';
817
818 ELSIF fk.char_id = qa_ss_const.rma_number THEN
819 v_select := v_select || ', qr.' || fk.hardcoded_column;
820 v_select := v_select ||
821 ', qa_flex_util.rma_number(qr.rma_header_id) "' || fk.name || '"';
822
823 ELSIF fk.char_id = qa_ss_const.project_number THEN
824 v_select := v_select || ', qr.' || fk.hardcoded_column;
825 v_select := v_select ||
826 ', qa_flex_util.project_number(qr.project_id) "' || fk.name || '"';
827
828 --
829 -- If the element is hardcoded, then simply use the hardcoded column.
830 --
831 ELSIF fk.hardcoded_column IS NOT NULL THEN
832
833 --
834 -- a small complication is if the element is a foreign key,
835 -- such as ITEM_ID, then outer join to the foreign table.
836 --
837 IF fk.fk_lookup_type IN (0, 1)
838 --
842 --
839 -- Safety check to make sure foreign key reference is
840 -- there. Otherwise, we will have a cross product with
841 -- no join condition!
843 AND fk.fk_id IS NOT NULL
844 AND fk.pk_id IS NOT NULL THEN
845
846 --
847 -- Here is where there is a foreign table. First add
848 -- the hardcoded column as is, so the ID column is present.
849 --
850 v_select := v_select || ', qr.' || fk.hardcoded_column;
851
852 --
853 -- Then add the foreign select as a separate column.
854 --
855 v_select := v_select || ', ' || fk.fk_table_short_name || '.' ||
856 fk.fk_meaning || ' "' || fk.name || '"';
857
858 -- Then the foreign table in the from clause
859 -- and finally the outer join in where clause.
860 --
861 v_from := v_from || ', ' || fk.fk_table_name || ' ' ||
862 fk.fk_table_short_name;
863
864 IF fk.pk_id IS NOT NULL AND fk.fk_id IS NOT NULL THEN
865 v_where := v_where || ' AND qr.' || fk.fk_id || ' = ' ||
866 fk.fk_table_short_name || '.' || fk.pk_id || ' (+)';
867 END IF;
868
869 IF fk.pk_id2 IS NOT NULL AND fk.fk_id2 IS NOT NULL THEN
870 v_where := v_where || ' AND qr.' || fk.fk_id2 || ' = ' ||
871 fk.fk_table_short_name || '.' || fk.pk_id2 || ' (+)';
872 END IF;
873
874 IF fk.pk_id3 IS NOT NULL AND fk.fk_id3 IS NOT NULL THEN
875 v_where := v_where || ' AND qr.' || fk.fk_id3 || ' = ' ||
876 fk.fk_table_short_name || '.' || fk.pk_id3 || ' (+)';
877 END IF;
878
879 IF fk.fk_add_where IS NOT NULL THEN
880 v_where := v_where || ' AND ' || fk.fk_add_where;
881 END IF;
882
883 ELSE
884 --
885 -- Not foreign key ... great, simply add the hardcoded
886 -- column name, examples are LOT_NUMBER, QUANTITY...
887 --
888 v_select := v_select || ', qr.' || fk.hardcoded_column ||
889 ' "' || fk.name || '"';
890 END IF;
891 ELSE
892 --
893 -- Element is softcoded. Use result_column name.
894 --
895 -- CHARACTER column data are stored in canonical format.
896 -- Convert to real number/real date if appropriate.
897 --
898 IF fk.datatype = 2 THEN
899
900 --
901 -- Need to create views with 12 decimal places
902 -- for number type elements. See Bug 2624112
903 -- rkunchal Wed Oct 16 05:32:33 PDT 2002
904 --
905 -- temp := 'to_number(qr.' || fk.result_column_name ||
906 -- ', ''9999999999999999999999999999999.999999'')';
907
908 temp := 'qltdate.any_to_number(qr.' || fk.result_column_name || ')';
909
910 ELSIF fk.datatype = 3 THEN
911 temp := 'to_date(qr.' || fk.result_column_name ||
912 ', ''YYYY/MM/DD'')';
913 --
914 -- Bug 3179845. Added to include datetime type in plan_view
915 -- saugupta Tue Oct 14 05:29:19 PDT 2003
916 --
917 ELSIF fk.datatype = 6 THEN
918 temp := 'to_date(qr.' || fk.result_column_name ||
919 ', ''YYYY/MM/DD HH24:MI:SS'')';
920 ELSE
921 temp := 'qr.' || fk.result_column_name;
922 END IF;
923
924 v_select := v_select || ', ' || temp || ' "' || fk.name || '"';
925 END IF;
926
927 END LOOP;
928
929 -- create a dynamic call to do_ddl for either the 10.6 or 10.7 api
930 -- bug 6350575
931 -- 12.1 QWB Usability Improvements Project
932 -- Adding the status clause to the plan view
933 -- The Deref view does not contain the status
934 -- where clause
935 --
936 v_deref_view_where := v_where;
937 v_where := v_where || ' AND (qr.status IS NULL OR qr.status = 2)';
938
939 --
940 -- bug 6350575
941 -- 12.1 QWB Usability Improvements
942 -- Appending the create clause to the Plan view
943 --
944 v_final := v_plan_view_create || v_select || v_from || v_where;
945
946 --
947 -- bug 6350575
948 -- 12.1 QWB Usability Improvements
949 -- Building the final query for the deref view
950 --
951 v_deref_view_final := v_deref_view_create || v_select || v_from || v_deref_view_where;
952
953 ad_ddl.do_ddl(g_schema, 'QA', ad_ddl.create_view, v_final,
954 x_view_name);
955
956 --
957 -- bug 6350575
958 -- 12.1 QWB Usability Improvements
959 -- Creating the deref view
960 --
961 ad_ddl.do_ddl(g_schema, 'QA', ad_ddl.create_view, v_deref_view_final,
962 v_deref_view_name);
963
964 --
965 -- bug 6350575
966 -- 12.1 QWB Usability Improvements
967 -- Updating the deref view name in the qa_plans table
968 --
969 UPDATE qa_plans set deref_view_name = v_deref_view_name
970 WHERE plan_id = x_plan_id;
971
972 --
973 -- bug 7409976
974 -- Regranting the privileges on the plan view and
975 -- deref view
976 -- ntungare
977 --
978 FOR Cntr in 1..x_pv_grants_tab.COUNT
979 LOOP
980 EXECUTE IMMEDIATE x_pv_grants_tab(cntr);
981 END LOOP; -- End of loop for plan view
982
983 FOR Cntr in 1..x_dv_grants_tab.COUNT
984 LOOP
985 EXECUTE IMMEDIATE x_dv_grants_tab(cntr);
986 END LOOP; -- End of loop for deref view
987 END plan_view;
988
989
990 PROCEDURE import_plan_view(x_view_name IN VARCHAR2, x_old_view_name IN VARCHAR2,
991 x_plan_id IN NUMBER) IS
992 --
993 -- import view creation for QA_RESULTS_INTERFACE and collection import
994 --
995
996 v_select VARCHAR2(20000);
997
998 --
999 -- See comments in global_view about translate()
1000 --
1001 CURSOR pcursor is
1002 SELECT qpc.result_column_name,
1003 upper(translate(qc.name,' ''*{}','_____')) name,
1004 qc.hardcoded_column,
1005 qc.developer_name
1006 FROM qa_plan_chars qpc, qa_chars qc
1007 WHERE qpc.char_id = qc.char_id AND qpc.plan_id = x_plan_id
1008 ORDER BY prompt_sequence;
1009
1010 --
1011 -- bug 7409976
1012 -- ntungare
1013 --
1014 x_grants_tab grants_tab_typ;
1015 BEGIN
1016
1017 --
1018 -- When QLTPLMDF deletes a plan, it calls this proc with
1019 -- null x_view_name and with x_old_view_name populated.
1020 -- Need to drop the old view name and return.
1021 --
1022 IF x_old_view_name IS NOT NULL THEN
1023 --
1024 -- bug 7409976
1025 -- Getting a list of all the grants on the view
1026 -- before dropping it
1027 -- ntungare
1028 --
1029 create_grant_sql(p_view_name => x_old_view_name,
1030 x_grants_tab => x_grants_tab);
1031
1032 drop_view(x_old_view_name);
1033 END IF;
1034
1035 IF x_view_name IS NULL THEN
1036 RETURN;
1037 END IF;
1038
1039 --
1040 -- R12 Project MOAC 4637896
1041 -- Added operating_unit and operating_unit_id as columns in an
1042 -- import view. These are now needed because according to MOAC
1043 -- PO Number is no longer unique across OUs. Thus user has to
1044 -- specify OU name in order to resolve PO Number uniquely in
1045 -- the worst case.
1046 -- bso Sun Oct 2 11:41:00 PDT 2005
1047 --
1048 v_select := 'CREATE OR REPLACE FORCE VIEW "' || upper(x_view_name) ||
1049 '" AS SELECT
1050 transaction_interface_id,
1051 qa_last_updated_by_name,
1052 qa_created_by_name,
1053 collection_id,
1054 source_code,
1055 source_line_id,
1056 process_status,
1057 organization_code,
1058 operating_unit_id,
1059 operating_unit,
1060 plan_name,
1061 insert_type,
1062 matching_elements,
1063 spec_name';
1064
1065 FOR prec in pcursor LOOP
1066
1067 -- If the column is hardcoded column access the developer name and leave
1068 -- the developer name as the actual name for it in the view
1069 -- Otherwise, use the result column name to get the correct column but
1070 -- use the characteristic name as the column name in the view.
1071
1072 --
1073 -- Added the following IF condition for ASO project
1074 -- To uniquely identify a Maintenance_Requirement it takes
1075 -- two fields, namely, Title and Version_Number.
1076 -- rkunchal Thu Jul 25 01:43:48 PDT 2002
1077 --
1078
1079 IF prec.developer_name = 'MAINTENANCE_REQUIREMENT' THEN
1080 v_select := v_select || ', VERSION_NUMBER';
1081 END IF;
1082
1083 IF prec.hardcoded_column IS NOT NULL THEN
1084 v_select := v_select || ', ' || prec.developer_name;
1085 ELSE
1086
1087 -- originally we were going to change the datatype to number or
1088 -- date here if that was the datatype of the element. when we
1089 -- did this, however, it was no longer possible to insert into
1090 -- this column of the view (it became a virtual column).
1091
1092 v_select := v_select || ', ' || prec.result_column_name ||
1093 ' "' || prec.name || '"';
1094 END IF;
1095 END LOOP;
1096
1097 v_select := v_select || ' FROM QA_RESULTS_INTERFACE';
1098
1099 ad_ddl.do_ddl(g_schema, 'QA', ad_ddl.create_view, v_select,
1100 x_view_name);
1101
1102 --
1103 -- bug 7409976
1104 -- Regranting the privileges
1105 -- ntungare
1106 --
1107 FOR Cntr in 1..x_grants_tab.COUNT
1108 LOOP
1109 EXECUTE IMMEDIATE x_grants_tab(cntr);
1110 END LOOP;
1111 END import_plan_view;
1112
1113 BEGIN
1114
1115 g_dummy := fnd_installation.get_app_info(g_fnd, g_status,
1116 g_industry, g_schema);
1117
1118 END QLTVCREB ;
[Home] [Help]