1 PACKAGE BODY QLTVCREB AS
2 /* $Header: qltvcreb.plb 120.10.12020000.2 2012/07/03 18:10:54 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 -- bug 13616101
229 decode_cntr NUMBER;
230 --
231 -- Bug 4958779
232 -- This cursor is not performing. Removed and fetch element
233 -- info on-the-fly.
234 -- bso Tue Jan 31 14:57:53 PST 2006
235 --
236 -- CURSOR elements_cursor IS
237 -- SELECT qc.char_id, qc.name, qc.hardcoded_column, qc.datatype
238 -- FROM qa_chars qc
239 -- WHERE qc.enabled_flag = 1 and
240 -- rownum < (max_columns - fixed_columns)
241 -- ORDER BY qc.hardcoded_column, qc.name;
242 --
243 -- The elements to be included in the final view.
244 --
245 -- element_ids dbms_sql.number_table;
246 -- element_names dbms_sql.varchar2s;
247 -- element_hardcoded_columns dbms_sql.varchar2s;
248 -- element_datatypes dbms_sql.number_table;
249 --
250 l_element_id NUMBER;
251 l_element_name qa_chars.name%TYPE;
252 l_element_hardcoded_column qa_chars.hardcoded_column%TYPE;
253 l_element_datatype NUMBER;
254
255 --
256 -- This cursor is used to fetch foreign key info for hardcoded,
257 -- normalized elements.
258 --
259 CURSOR fk_cursor(x NUMBER) IS
260 SELECT qc.fk_table_name,
261 qc.fk_table_short_name,
262 qc.fk_lookup_type,
263 qc.pk_id,
264 qc.fk_id,
265 qc.pk_id2,
266 qc.fk_id2,
267 qc.pk_id3,
268 qc.fk_id3,
269 qc.fk_meaning,
270 qc.fk_description,
271 qc.fk_add_where
272 FROM qa_chars qc
273 WHERE qc.char_id = x;
274
275 fk fk_cursor%ROWTYPE;
276
277 --
278 -- This cursor loops through qa_plan_chars to find all
279 -- plan_id and result_column_name for each collection element.
280 --
281 -- The decode() function and distinct are there to cleverly
282 -- eliminate all but one row of hardcoded elements. We only
283 -- need one row for each hardcoded ones.
284 --
285 CURSOR plan_cursor IS
286 SELECT
287 /*
288 This is a batch program that needs to loop through
289 all collection plans to generate a global view.
290 Full table scan is expected. Bryan So 1/31/2006
291 */
292 distinct qpc.char_id,
293 decode(qc.hardcoded_column, null, qpc.plan_id, 0) plan_id,
294 upper(qpc.result_column_name) result_column_name,
295 qc.name,
296 qc.hardcoded_column
297 FROM qa_plan_chars qpc, qa_chars qc
298 WHERE qc.char_id = qpc.char_id AND
299 qpc.enabled_flag = 1 AND
300 qc.enabled_flag = 1
301 ORDER BY qc.hardcoded_column, qc.name;
302
303 pc plan_cursor%ROWTYPE;
304
305 current_element NUMBER;
306 temp VARCHAR2(255);
307 n INTEGER;
308
309 i INTEGER; -- I used these as counters for the
310 j INTEGER; -- following lists: select list (v_sql_table),
311 k INTEGER; -- from list, where list
312 --
313 -- I have used generic function such as table_add to an element
314 -- to a PL/SQL table... it is way too slow. 3 times difference
315 -- than using simple variable counters.
316 --
317
318 BEGIN
319
320 --
321 -- This is the beginning of the global view.
322 --
323
324 v_sql_table(1) := 'CREATE OR REPLACE FORCE VIEW ' || x_view_name;
325 --
326 -- Bug 13816444.
327 -- Added a hint to improve the performance while accessing the Global view
328 --
329 v_sql_table(2) := ' AS SELECT /*+ push_pred(PH) USE_NL(PH PR)*/ ';
330 v_sql_table(3) := ' qr.rowid row_id,'; -- 1
331 v_sql_table(4) := ' qr.plan_id,'; -- 2
332 v_sql_table(5) := ' qp'||g_period||'name plan_name,'; -- 3 see bug 3554899
333 v_sql_table(6) := ' qr.organization_id,'; -- 4
334 v_sql_table(7) := ' hou.name organization_name,'; -- 5
335 v_sql_table(8) := ' qr.collection_id,'; -- 6
336 v_sql_table(9) := ' qr.occurrence,'; -- 7
337 v_sql_table(10) := ' qr.qa_last_update_date last_update_date,'; -- 8
338 v_sql_table(11) := ' qr.qa_last_updated_by last_update_by_id,'; -- 9
339 v_sql_table(12) := ' fu2.user_name last_updated_by,'; -- 10
340 v_sql_table(13) := ' qr.qa_creation_date creation_date,'; -- 11
341 v_sql_table(14) := ' qr.qa_created_by created_by_id,'; -- 12
342 v_sql_table(15) := ' fu.user_name created_by,'; -- 13
343 v_sql_table(16) := ' qr.last_update_login'; -- 14
344 i := 17;
345 column_count := fixed_columns; -- 14
346
347 -- Then add the necessary where and froms
348 v_from_table(1) := 'qa_results qr';
349 v_from_table(2) := 'qa_plans qp';
350 v_from_table(3) := 'fnd_user_view fu';
351 v_from_table(4) := 'fnd_user_view fu2';
352 v_from_table(5) := 'hr_organization_units hou';
353 j := 6;
354
355 v_where_table(1) := 'qp'||g_period||'plan_id = qr.plan_id'; -- see bug 3554899
356 v_where_table(2) := 'qr.qa_created_by = fu.user_id';
357 v_where_table(3) := 'qr.qa_last_updated_by = fu2.user_id';
358 v_where_table(4) := 'qr.organization_id = hou.organization_id';
359 v_where_table(5) := '(qr.status IS NULL OR qr.status = 2)';
360 k := 6;
361
362 --
363 -- Bug 4958779
364 -- This cursor is not performing. Removed and fetch element
365 -- info on-the-fly.
366 -- bso Tue Jan 31 14:57:53 PST 2006
367 --
368 -- -- Collection all elements to be included in the view.
369 -- --
370 -- OPEN elements_cursor;
371 --
372 -- FETCH elements_cursor BULK COLLECT INTO element_ids, element_names,
373 -- element_hardcoded_columns, element_datatypes;
374 --
375 -- CLOSE elements_cursor;
376 --
377
378 --
379 -- One option is to go through each element, but it is slightly
380 -- more efficient to go through qa_plan_chars to avoid performing
381 -- too many database parses.
382 --
383 -- FOR element IN elements_cursor LOOP
384 --
385 OPEN plan_cursor;
386 FETCH plan_cursor INTO pc;
387
388 LOOP
389 EXIT WHEN column_count > (max_columns-2); -- reserve 2 columns
390
391 EXIT WHEN plan_cursor%NOTFOUND;
392
393 --
394 -- We will do the following only if element appears in the given
395 -- element ID list.
396 --
397 current_element := pc.char_id;
398
399 SELECT char_id, name, hardcoded_column, datatype
400 INTO l_element_id, l_element_name, l_element_hardcoded_column,
401 l_element_datatype
402 FROM qa_chars
403 WHERE char_id = current_element;
404
405 -- There are several conditions.
406
407 --
408 -- If the element has a specially-coded stored function, then
409 -- use the stored function...
410 --
411 IF current_element = qa_ss_const.sales_order THEN
412 v_sql_table(i) := ', qr.' || l_element_hardcoded_column;
413 i := i + 1;
414 v_sql_table(i) := ', qa_flex_util.sales_order(qr.so_header_id) "'
415 || trans(l_element_name) || '"';
416 i := i + 1;
417 column_count := column_count + 2;
418
419 ELSIF current_element = qa_ss_const.rma_number THEN
420 v_sql_table(i) := ', qr.' || l_element_hardcoded_column;
421 i := i + 1;
422 v_sql_table(i) := ', qa_flex_util.rma_number(qr.rma_header_id) "'
423 || trans(l_element_name) || '"';
424 i := i + 1;
425 column_count := column_count + 2;
426
427 ELSIF current_element = qa_ss_const.project_number THEN
428 v_sql_table(i) := ', qr.' || l_element_hardcoded_column;
429 i := i + 1;
430 v_sql_table(i) := ', qa_flex_util.project_number(qr.project_id) "'
431 || trans(l_element_name) || '"';
432 i := i + 1;
433 column_count := column_count + 2;
434
435 --
436 -- If the element is hardcoded, then simply use the hardcoded column.
437 --
438 ELSIF l_element_hardcoded_column IS NOT NULL THEN
439
440 --
441 -- a small complication is if the element is a foreign key,
442 -- such as ITEM_ID, then outer join to the foreign table.
446
443 --
444 OPEN fk_cursor(current_element);
445 FETCH fk_cursor INTO fk;
447 IF fk.fk_lookup_type IN (0, 1)
448 --
449 -- Safety check to make sure foreign key reference is
450 -- there. Otherwise, we will have a cross product with
451 -- no join condition!
452 --
453 AND fk.fk_id IS NOT NULL
454 AND fk.pk_id IS NOT NULL THEN
455 --
456 -- Here is where there is a foreign table. First add
457 -- the hardcoded column as is, so the ID column is present.
458 --
459 v_sql_table(i) := ', qr.' || l_element_hardcoded_column;
460 i := i + 1;
461
462 --
463 -- Then add the foreign select as a separate column.
464 --
465 v_sql_table(i) := ', ' || fk.fk_table_short_name || '.' ||
466 fk.fk_meaning || ' "' || trans(l_element_name) || '"';
467 i := i + 1;
468
469 -- Then the foreign table in the from clause
470 -- and finally the outer join in where clause.
471 --
472 v_from_table(j) :=
473 fk.fk_table_name || ' ' || fk.fk_table_short_name;
474 j := j + 1;
475
476 IF fk.pk_id IS NOT NULL AND fk.fk_id IS NOT NULL THEN
477 v_where_table(k) := 'qr.' || fk.fk_id || ' = ' ||
478 fk.fk_table_short_name || '.' || fk.pk_id || ' (+)';
479 k := k + 1;
480 END IF;
481
482 IF fk.pk_id2 IS NOT NULL AND fk.fk_id2 IS NOT NULL THEN
483 v_where_table(k) := 'qr.' || fk.fk_id2 || ' = ' ||
484 fk.fk_table_short_name || '.' || fk.pk_id2 || ' (+)';
485 k := k + 1;
486 END IF;
487
488 IF fk.pk_id3 IS NOT NULL AND fk.fk_id3 IS NOT NULL THEN
489 v_where_table(k) := 'qr.' || fk.fk_id3 || ' = ' ||
490 fk.fk_table_short_name || '.' || fk.pk_id3 || ' (+)';
491 k := k + 1;
492 END IF;
493
494 IF fk.fk_add_where IS NOT NULL THEN
495 v_where_table(k) := fk.fk_add_where;
496 k := k + 1;
497 END IF;
498
499 column_count := column_count + 2;
500
501 ELSE
502 --
503 -- Not foreign key ... great, simply add the hardcoded
504 -- column name, examples are LOT_NUMBER, QUANTITY...
505 --
506 v_sql_table(i) := ', qr.' || l_element_hardcoded_column ||
507 ' "' || trans(l_element_name) || '"';
508 i := i + 1;
509
510 column_count := column_count + 1;
511 END IF;
512
513 CLOSE fk_cursor;
514
515 ELSE -- Here, hardcoded_column IS NULL
516
517 --
518 -- Here the element must be softcoded. This is most interesting.
519 -- Need to use a bunch of decode statement and also correct the
520 -- canonical datatype to real datatype format.
521 --
522 -- For example, if defect code is assigned to CHARACTER2 in
523 -- plan 101 and assigned to CHARACTER5 in plan 103, the decode
524 -- statement will look like this:
525 --
526 -- decode(qr.plan_id, 101, character2, 103, character5) defect_code
527 --
528
529 decode_count := 0; -- see comments in variable declaration.
530 decode_param := decode_limit;
531
532 WHILE pc.char_id = current_element LOOP
533 --
534 -- If maximum no. of arguments to the "decode" function is
535 -- close to the server allowed 'decode_limit', then we want
536 -- to start a new tail-end decode statement.
537 --
538 IF decode_param >= (decode_limit - 2) THEN
539 v_sql_table(i) := ', decode(qr.plan_id';
540 i := i + 1;
541 decode_count := decode_count + 1;
542 decode_param := 1;
543 END IF;
544
545 --
546 -- CHARACTER column data are stored in canonical format.
547 -- Convert to real number/real date if appropriate.
548 --
549 IF l_element_datatype = 2 THEN
550
551 --
552 -- Need to create views with 12 decimal places
553 -- for number type elements. See Bug 2624112
554 -- rkunchal Wed Oct 16 05:32:33 PDT 2002
555 --
556 -- temp := 'to_number(qr.' || pc.result_column_name ||
557 -- ', ''9999999999999999999999999999999.999999'')';
558
559 temp := 'qltdate.any_to_number(qr.' || pc.result_column_name || ')';
560
561 ELSIF l_element_datatype = 3 THEN
562 temp := 'to_date(qr.' || pc.result_column_name ||
563 ', ''YYYY/MM/DD'')';
564 --
565 -- Bug 3179845. Added to iinclude datetime type for Timezone.
566 -- saugupta Tue Oct 14 05:08:00 PDT 2003
567 --
568 ELSIF l_element_datatype = 6 THEN
569 temp := 'to_date(qr.' || pc.result_column_name ||
570 ', ''YYYY/MM/DD HH24:MI:SS'')';
571 ELSE
572 temp := 'qr.' || pc.result_column_name;
573 END IF;
574
575 v_sql_table(i) := ', ' || pc.plan_id || ', ' || temp;
576 i := i + 1;
580
577 decode_param := decode_param + 2;
578
579 FETCH plan_cursor INTO pc;
581 EXIT WHEN plan_cursor%NOTFOUND;
582
583 END LOOP;
584
585 --
586 -- Close all decode() parenthesis
587 --
588 temp := '';
589 FOR x IN 1 .. decode_count LOOP
590 -- bug 13616101
591 -- The decode length can go beyond 255 in which this would error. Hence
592 -- pushing it to buffer periodically.
593 --
594 decode_cntr := decode_cntr + 1;
595 IF decode_cntr <= 200 THEN
596 temp := temp || ')';
597 ELSE
598 v_sql_table(i) := temp;
599 i := i + 1;
600 temp := ')';
601 decode_cntr := 0;
602 END IF;
603 END LOOP;
604
605 v_sql_table(i) := temp || ' "' || trans(l_element_name) || '"';
606 i := i + 1;
607 column_count := column_count + 1;
608
609 END IF;
610
611 WHILE pc.char_id = current_element LOOP
612 FETCH plan_cursor INTO pc;
613 EXIT WHEN plan_cursor%NOTFOUND;
614 END LOOP;
615
616 END LOOP;
617
618 CLOSE plan_cursor;
619
620 -- create the from and where clause
621 create_clause(v_sql_table, v_from_table, 'FROM');
622 create_clause(v_sql_table, v_where_table, 'WHERE');
623
624 --
625 -- Find apps schema info. To be used in ad_ddl calls.
626 --
627 exec_ddl_table(g_schema, 'QA', AD_DDL.create_view, v_sql_table, x_view_name);
628
629 END global_view;
630
631 --
632 -- bug 7409976
633 -- New procedure to create a stack of
634 -- all the grants present on the views
635 -- ntungare
636 --
637 PROCEDURE create_grant_sql(p_view_name IN VARCHAR2,
638 x_grants_tab OUT NOCOPY grants_tab_typ) AS
639
640 TYPE privs_rec IS RECORD(grantee VARCHAR2(200),
641 obj_priv VARCHAR2(200),
642 grantable VARCHAR2(200));
643 TYPE privs_rec_tab_type IS TABLE OF privs_rec INDEX BY BINARY_INTEGER;
644 privs_rec_tab privs_rec_tab_type ;
645
646 grant_option VARCHAR2(2000):= ' WITH GRANT OPTION ';
647
648 grant_stmt VARCHAR2(4000);
649 BEGIN
650 SELECT grantee, privilege, grantable
651 BULK COLLECT INTO privs_rec_tab
652 FROM User_TAB_PRIVS
653 WHERE table_name = p_view_name
654 ORDER BY grantee;
655
656 FOR cntr in 1..privs_rec_tab.COUNT
657 LOOP
658 grant_stmt := 'GRANT '|| privs_rec_tab(cntr).obj_priv ||' ON '||p_view_name ||
659 ' TO ' || privs_rec_tab(cntr).grantee;
660
661 IF (privs_rec_tab(cntr).grantable = 'YES') THEN
662 grant_stmt := grant_stmt ||grant_option;
663 END IF;
664
665 x_grants_tab(cntr) := grant_stmt;
666 END LOOP;
667 END create_grant_sql;
668
669 -- Plan View creates a view of QA_RESULTS using the user defined names
670 -- for the Character fields found there
671
672 -- Kevin Wiggen September 22 1994
673
674
675 --
676 -- bug 12596623
677 -- New function to build the inline view for constructing the VO SQL
678 -- for export functionality. This has been done so that the export
679 -- feature could make use of the function based indices if there are
680 -- any on the elements. Since the index predicate would be based on
681 -- CHARACTERXX columns, hence the plan view had to be exploded into the
682 -- actual SQL.. This code is similar as that in the plan view creation
683 -- API
684 --
685 Function get_export_view_sql(x_plan_id IN NUMBER) Return VARCHAR2 IS
686
687 v_select VARCHAR2(20000);
688 v_from VARCHAR2(20000);
689 v_where VARCHAR2(20000);
690 v_final VARCHAR2(32000);
691
692 temp VARCHAR2(4000);
693
694 CURSOR pcursor is
695 SELECT qc.char_id,
696 upper(translate(qc.name,' ''*{}','_____')) name,
697 qpc.result_column_name,
698 qc.hardcoded_column,
699 qc.FK_LOOKUP_TYPE,
700 qc.FK_TABLE_NAME,
701 qc.FK_TABLE_SHORT_NAME,
702 qc.PK_ID,
703 qc.FK_ID,
704 qc.PK_ID2,
705 qc.FK_ID2,
706 qc.PK_ID3,
707 qc.FK_ID3,
708 qc.FK_MEANING,
709 qc.FK_DESCRIPTION,
710 qc.FK_ADD_WHERE,
711 qc.DATATYPE
712 FROM qa_chars qc,
713 qa_plan_chars qpc
714 WHERE qc.char_id = qpc.char_id
715 AND qpc.plan_id = x_plan_id
716 ORDER BY qpc.prompt_sequence;
717
718
719 l_index_exists NUMBER;
720 l_index_predicate VARCHAR2(32767) := null;
721
722 g_period varchar2(10) := '.';
723
724 l_num_column VARCHAR2(32767);
725 l_ret_status INTEGER;
726 BEGIN
727 v_select := ' SELECT
728 qr.rowid row_id,
729 qr.plan_id,
730 qp'||g_period||'name plan_name,
731 qr.organization_id,
732 hou.name organization_name,
733 qr.collection_id,
734 qr.occurrence,
735 qr.qa_last_update_date last_update_date,
736 qr.qa_last_updated_by last_updated_by_id,
737 fu2.user_name last_updated_by,
738 qr.qa_creation_date creation_date,
739 qr.qa_created_by created_by_id,
743 v_from := ' FROM qa_results qr,
740 fu.user_name created_by,
741 qr.last_update_login';
742
744 qa_plans qp,
745 fnd_user_view fu,
746 fnd_user_view fu2,
747 hr_organization_units hou';
748
749 v_where := ' WHERE qp'||g_period||'plan_id = ' || x_plan_id || ' AND '||
750 'qr'||g_period||'plan_id = '|| x_plan_id || ' AND
751 qp'||g_period||'plan_id = qr.plan_id AND
752 qr.qa_created_by = fu.user_id AND
753 qr.qa_last_updated_by = fu2.user_id AND
754 qr.organization_id = hou.organization_id
755 AND (qr.status IS NULL OR qr.status = 2)';
756
757 FOR fk in pcursor LOOP
758
759 IF fk.char_id = qa_ss_const.sales_order THEN
760 v_select := v_select || ', qr.' || fk.hardcoded_column;
761 v_select := v_select ||
762 ', qa_flex_util.sales_order(qr.so_header_id) "' || fk.name || '"';
763
764 ELSIF fk.char_id = qa_ss_const.rma_number THEN
765 v_select := v_select || ', qr.' || fk.hardcoded_column;
766 v_select := v_select ||
767 ', qa_flex_util.rma_number(qr.rma_header_id) "' || fk.name || '"';
768
769 ELSIF fk.char_id = qa_ss_const.project_number THEN
770 v_select := v_select || ', qr.' || fk.hardcoded_column;
771 v_select := v_select ||
772 ', qa_flex_util.project_number(qr.project_id) "' || fk.name || '"';
773
774 --
775 -- If the element is hardcoded, then simply use the hardcoded column.
776 --
777 ELSIF fk.hardcoded_column IS NOT NULL THEN
778
779 --
780 -- a small complication is if the element is a foreign key,
781 -- such as ITEM_ID, then outer join to the foreign table.
782 --
783 IF fk.fk_lookup_type IN (0, 1)
784 --
785 -- Safety check to make sure foreign key reference is
786 -- there. Otherwise, we will have a cross product with
787 -- no join condition!
788 --
789 AND fk.fk_id IS NOT NULL
790 AND fk.pk_id IS NOT NULL THEN
791
792 --
793 -- Here is where there is a foreign table. First add
794 -- the hardcoded column as is, so the ID column is present.
795 --
796 v_select := v_select || ', qr.' || fk.hardcoded_column;
797
798 --
799 -- Then add the foreign select as a separate column.
800 --
801 v_select := v_select || ', ' || fk.fk_table_short_name || '.' ||
802 fk.fk_meaning || ' "' || fk.name || '"';
803
804 -- Then the foreign table in the from clause
805 -- and finally the outer join in where clause.
806 --
807 v_from := v_from || ', ' || fk.fk_table_name || ' ' ||
808 fk.fk_table_short_name;
809
810 IF fk.pk_id IS NOT NULL AND fk.fk_id IS NOT NULL THEN
811 v_where := v_where || ' AND qr.' || fk.fk_id || ' = ' ||
812 fk.fk_table_short_name || '.' || fk.pk_id || ' (+)';
813 END IF;
814
815 IF fk.pk_id2 IS NOT NULL AND fk.fk_id2 IS NOT NULL THEN
816 v_where := v_where || ' AND qr.' || fk.fk_id2 || ' = ' ||
817 fk.fk_table_short_name || '.' || fk.pk_id2 || ' (+)';
818 END IF;
819
820 IF fk.pk_id3 IS NOT NULL AND fk.fk_id3 IS NOT NULL THEN
821 v_where := v_where || ' AND qr.' || fk.fk_id3 || ' = ' ||
822 fk.fk_table_short_name || '.' || fk.pk_id3 || ' (+)';
823 END IF;
824
825 IF fk.fk_add_where IS NOT NULL THEN
826 v_where := v_where || ' AND ' || fk.fk_add_where;
827 END IF;
828
829 ELSE
830 --
831 -- Not foreign key ... great, simply add the hardcoded
832 -- column name, examples are LOT_NUMBER, QUANTITY...
833 --
834 v_select := v_select || ', qr.' || fk.hardcoded_column ||
835 ' "' || fk.name || '"';
836 END IF;
837 ELSE
838 --
839 -- Element is softcoded. Use result_column name.
840 --
841 -- CHARACTER column data are stored in canonical format.
842 -- Convert to real number/real date if appropriate.
843 --
844 IF fk.datatype = 2 THEN
845
846 --
847 -- Need to create views with 12 decimal places
848 -- for number type elements. See Bug 2624112
849 -- rkunchal Wed Oct 16 05:32:33 PDT 2002
850 --
851 -- temp := 'qr.' || fk.result_column_name ;
852
853 -- temp := 'qltdate.any_to_number(qr.' || fk.result_column_name || ')';
854
855 l_index_exists := qa_char_indexes_pkg.index_exists_and_enabled(fk.char_id);
856
857 if l_index_exists = 1 then
858 l_ret_status := qa_char_indexes_pkg.get_case_function(fk.char_id, 'QR', l_num_column);
859
860 If l_ret_status = 0 then
861 temp := l_num_column ;
862 Else
863 temp := 'qr.' || fk.result_column_name ;
864 END IF;
865 else
866 temp := 'qr.' || fk.result_column_name ;
867 end if;
868
869
870 ELSIF fk.datatype = 3 THEN
871 temp := 'to_date(qr.' || fk.result_column_name ||
872 ', ''YYYY/MM/DD'')';
873 --
877 ELSIF fk.datatype = 6 THEN
874 -- Bug 3179845. Added to include datetime type in plan_view
875 -- saugupta Tue Oct 14 05:29:19 PDT 2003
876 --
878 temp := 'to_date(qr.' || fk.result_column_name ||
879 ', ''YYYY/MM/DD HH24:MI:SS'')';
880 ELSE
881 --
882 -- For Softcoded character elements, we should add the index predicate
883 -- So the drive goes through the Function based index, if its created
884 --
885 l_index_exists := qa_char_indexes_pkg.index_exists_and_enabled(fk.char_id);
886
887 if l_index_exists = 1 then
888 l_index_predicate := qa_char_indexes_pkg.get_decode_function(fk.char_id, 'QR.');
889
890 temp := l_index_predicate;
891 l_index_predicate := null;
892 l_index_exists := 0;
893 else
894 temp := 'qr.' || fk.result_column_name;
895 end if;
896 END IF;
897
898 v_select := v_select || ', ' || temp || ' "' || fk.name || '"';
899 END IF;
900
901 END LOOP;
902
903 v_final := v_select || v_from || v_where;
904
905 Return v_final;
906
907 END get_export_view_sql;
908
909
910
911 PROCEDURE plan_view(x_view_name IN VARCHAR2, x_old_view_name IN VARCHAR2,
912 x_plan_id IN NUMBER) IS
913
914 v_select VARCHAR2(20000);
915 v_from VARCHAR2(20000);
916 v_where VARCHAR2(20000);
917 v_final VARCHAR2(32000);
918
919 temp VARCHAR2(255);
920
921 CURSOR pcursor is
922 --
923 -- See comments in global_view
924 --
925 SELECT qc.char_id,
926 upper(translate(qc.name,' ''*{}','_____')) name,
927 qpc.result_column_name,
928 qc.hardcoded_column,
929 qc.FK_LOOKUP_TYPE,
930 qc.FK_TABLE_NAME,
931 qc.FK_TABLE_SHORT_NAME,
932 qc.PK_ID,
933 qc.FK_ID,
934 qc.PK_ID2,
935 qc.FK_ID2,
936 qc.PK_ID3,
937 qc.FK_ID3,
938 qc.FK_MEANING,
939 qc.FK_DESCRIPTION,
940 qc.FK_ADD_WHERE,
941 qc.DATATYPE
942 FROM qa_chars qc,
943 qa_plan_chars qpc
944 WHERE qc.char_id = qpc.char_id
945 AND qpc.plan_id = x_plan_id
946 ORDER BY qpc.prompt_sequence;
947
948 --
949 -- bug 6350575
950 -- 12.1 QWB Usability Improvements Project
951 --
952 v_plan_view_create VARCHAR2(2000);
953 v_deref_view_create VARCHAR2(2000);
954 v_deref_view_final VARCHAR2(32000);
955 v_deref_view_where VARCHAR2(20000);
956 v_deref_view_name VARCHAR2(30);
957
958 --
959 -- bug 7409976
960 -- ntungare
961 --
962 x_pv_grants_tab grants_tab_typ; -- Collection for grants on Plan View
963 x_dv_grants_tab grants_tab_typ; -- Collection for grants on Deref View
964
965 --
966 -- bug 12596623
967 --
968 l_export_view_final VARCHAR2(32000);
969 BEGIN
970 --
971 -- bug 6350575
972 -- 12. QWB Usability Improvements Project
973 -- Deriving the Deref view name
974 --
975 SELECT substr(import_view_name, 1, length(import_view_name)-2)||'DV'
976 INTO v_deref_view_name FROM qa_plans
977 WHERE plan_id = x_plan_id;
978
979 --
980 -- When QLTPLMDF deletes a plan, it calls this proc with
981 -- null x_view_name and with x_old_view_name populated.
982 -- Need to drop the old view name and return.
983 --
984 IF x_old_view_name IS NOT NULL THEN
985 --
986 -- bug 7409976
987 -- Getting a list of all the grants on the plan view
988 -- before dropping it
989 -- ntungare
990 --
991 create_grant_sql(p_view_name => x_old_view_name,
992 x_grants_tab => x_pv_grants_tab);
993
994 drop_view(x_old_view_name);
995
996 --
997 -- bug 7409976
998 -- Getting a list of all the grants on the deref view
999 -- before dropping it
1000 -- ntungare
1001 --
1002 create_grant_sql(p_view_name => v_deref_view_name,
1003 x_grants_tab => x_dv_grants_tab);
1004
1005 drop_view(v_deref_view_name);
1006 END IF;
1007
1008 IF x_view_name IS NULL THEN
1009 RETURN;
1010 END IF;
1011
1012 --
1013 -- bug 6350575
1014 -- 12.1 QWB Usability Improvements Project
1015 -- building the create clause for the plan view
1016 --
1017 v_plan_view_create := 'CREATE OR REPLACE FORCE VIEW "' || upper(x_view_name) || '" AS ';
1018
1019 --
1020 -- bug 6350575
1021 -- 12.1 QWB Usability Improvements Project
1022 -- building the create clause for the plan view
1023 --
1024 v_deref_view_create := 'CREATE OR REPLACE FORCE VIEW "' || upper(v_deref_view_name) || '" AS ';
1025
1026 --
1027 -- bug 9919908 fp to 9592090
1028 -- Added the hint to ensure that the drive happens from
1029 -- qa_plans to qa_results
1030 -- hmakam
1031 --
1032 --
1033 -- bug 12765034
1034 -- Modified the hint to ensure that the drive occurs
1035 -- through qp and followed by qr, but the access on QR
1039 -- v_select := ' SELECT /*+ USE_NL(qp qr) INDEX(qr QA_RESULTS_N14)*/
1036 -- occurs through the most optimal index and not
1037 -- necessarily QA_RESULTS_N14
1038 --
1040 --
1041 -- bug 14038553
1042 -- If the po related views are in the plan view PH, PR, then the additional
1043 -- hint would improve the performance.
1044 --
1045 v_select := ' SELECT /*+ LEADING(qp) USE_NL(qp qr) push_pred(PH) USE_NL(PH PR)*/
1046 qr.rowid row_id,
1047 qr.plan_id,
1048 qp'||g_period||'name plan_name,
1049 qr.organization_id,
1050 hou.name organization_name,
1051 qr.collection_id,
1052 qr.occurrence,
1053 qr.qa_last_update_date last_update_date,
1054 qr.qa_last_updated_by last_updated_by_id,
1055 fu2.user_name last_updated_by,
1056 qr.qa_creation_date creation_date,
1057 qr.qa_created_by created_by_id,
1058 fu.user_name created_by,
1059 qr.last_update_login';
1060
1061 v_from := ' FROM qa_results qr,
1062 qa_plans qp,
1063 fnd_user_view fu,
1064 fnd_user_view fu2,
1065 hr_organization_units hou';
1066
1067 v_where := ' WHERE qp'||g_period||'plan_id = ' || x_plan_id || ' AND '||
1068 --
1069 -- bug 6044832
1070 -- Added an additional where clause, so that
1071 -- the index on QA_RESULTS is looked at, while
1072 -- querying the view, even with high volume of
1073 -- data
1074 -- ntungare Mon Jul 16 03:02:23 PDT 2007
1075 --
1076 -- bug 6350575
1077 -- 12.1 QWB USABILITY IMPROVEMENTS PROJECT
1078 -- removed the status where caluse since it would be
1079 -- added at the end
1080 --
1081 'qr'||g_period||'plan_id = '|| x_plan_id || ' AND
1082 qp'||g_period||'plan_id = qr.plan_id AND
1083 qr.qa_created_by = fu.user_id AND
1084 qr.qa_last_updated_by = fu2.user_id AND
1085 qr.organization_id = hou.organization_id';
1086 --AND
1087 --(qr.status IS NULL OR qr.status = 2)';
1088
1089 FOR fk in pcursor LOOP
1090
1091 IF fk.char_id = qa_ss_const.sales_order THEN
1092 v_select := v_select || ', qr.' || fk.hardcoded_column;
1093 v_select := v_select ||
1094 ', qa_flex_util.sales_order(qr.so_header_id) "' || fk.name || '"';
1095
1096 ELSIF fk.char_id = qa_ss_const.rma_number THEN
1097 v_select := v_select || ', qr.' || fk.hardcoded_column;
1098 v_select := v_select ||
1099 ', qa_flex_util.rma_number(qr.rma_header_id) "' || fk.name || '"';
1100
1101 ELSIF fk.char_id = qa_ss_const.project_number THEN
1102 v_select := v_select || ', qr.' || fk.hardcoded_column;
1103 v_select := v_select ||
1104 ', qa_flex_util.project_number(qr.project_id) "' || fk.name || '"';
1105
1106 --
1107 -- If the element is hardcoded, then simply use the hardcoded column.
1108 --
1109 ELSIF fk.hardcoded_column IS NOT NULL THEN
1110
1111 --
1112 -- a small complication is if the element is a foreign key,
1113 -- such as ITEM_ID, then outer join to the foreign table.
1114 --
1115 IF fk.fk_lookup_type IN (0, 1)
1116 --
1117 -- Safety check to make sure foreign key reference is
1118 -- there. Otherwise, we will have a cross product with
1119 -- no join condition!
1120 --
1121 AND fk.fk_id IS NOT NULL
1122 AND fk.pk_id IS NOT NULL THEN
1123
1124 --
1125 -- Here is where there is a foreign table. First add
1126 -- the hardcoded column as is, so the ID column is present.
1127 --
1128 v_select := v_select || ', qr.' || fk.hardcoded_column;
1129
1130 --
1131 -- Then add the foreign select as a separate column.
1132 --
1133 v_select := v_select || ', ' || fk.fk_table_short_name || '.' ||
1134 fk.fk_meaning || ' "' || fk.name || '"';
1135
1136 -- Then the foreign table in the from clause
1137 -- and finally the outer join in where clause.
1138 --
1139 v_from := v_from || ', ' || fk.fk_table_name || ' ' ||
1140 fk.fk_table_short_name;
1141
1142 IF fk.pk_id IS NOT NULL AND fk.fk_id IS NOT NULL THEN
1143 v_where := v_where || ' AND qr.' || fk.fk_id || ' = ' ||
1144 fk.fk_table_short_name || '.' || fk.pk_id || ' (+)';
1145 END IF;
1146
1147 IF fk.pk_id2 IS NOT NULL AND fk.fk_id2 IS NOT NULL THEN
1148 v_where := v_where || ' AND qr.' || fk.fk_id2 || ' = ' ||
1149 fk.fk_table_short_name || '.' || fk.pk_id2 || ' (+)';
1150 END IF;
1151
1152 IF fk.pk_id3 IS NOT NULL AND fk.fk_id3 IS NOT NULL THEN
1153 v_where := v_where || ' AND qr.' || fk.fk_id3 || ' = ' ||
1154 fk.fk_table_short_name || '.' || fk.pk_id3 || ' (+)';
1155 END IF;
1156
1157 IF fk.fk_add_where IS NOT NULL THEN
1158 v_where := v_where || ' AND ' || fk.fk_add_where;
1159 END IF;
1160
1161 ELSE
1162 --
1163 -- Not foreign key ... great, simply add the hardcoded
1164 -- column name, examples are LOT_NUMBER, QUANTITY...
1165 --
1166 v_select := v_select || ', qr.' || fk.hardcoded_column ||
1167 ' "' || fk.name || '"';
1168 END IF;
1169 ELSE
1170 --
1171 -- Element is softcoded. Use result_column name.
1175 --
1172 --
1173 -- CHARACTER column data are stored in canonical format.
1174 -- Convert to real number/real date if appropriate.
1176 IF fk.datatype = 2 THEN
1177
1178 --
1179 -- Need to create views with 12 decimal places
1180 -- for number type elements. See Bug 2624112
1181 -- rkunchal Wed Oct 16 05:32:33 PDT 2002
1182 --
1183 -- temp := 'to_number(qr.' || fk.result_column_name ||
1184 -- ', ''9999999999999999999999999999999.999999'')';
1185
1186 temp := 'qltdate.any_to_number(qr.' || fk.result_column_name || ')';
1187
1188 ELSIF fk.datatype = 3 THEN
1189 temp := 'to_date(qr.' || fk.result_column_name ||
1190 ', ''YYYY/MM/DD'')';
1191 --
1192 -- Bug 3179845. Added to include datetime type in plan_view
1193 -- saugupta Tue Oct 14 05:29:19 PDT 2003
1194 --
1195 ELSIF fk.datatype = 6 THEN
1196 temp := 'to_date(qr.' || fk.result_column_name ||
1197 ', ''YYYY/MM/DD HH24:MI:SS'')';
1198 ELSE
1199 temp := 'qr.' || fk.result_column_name;
1200 END IF;
1201
1202 v_select := v_select || ', ' || temp || ' "' || fk.name || '"';
1203 END IF;
1204
1205 END LOOP;
1206
1207 -- create a dynamic call to do_ddl for either the 10.6 or 10.7 api
1208 -- bug 6350575
1209 -- 12.1 QWB Usability Improvements Project
1210 -- Adding the status clause to the plan view
1211 -- The Deref view does not contain the status
1212 -- where clause
1213 --
1214 v_deref_view_where := v_where;
1215 v_where := v_where || ' AND (qr.status IS NULL OR qr.status = 2)';
1216
1217 --
1218 -- bug 6350575
1219 -- 12.1 QWB Usability Improvements
1220 -- Appending the create clause to the Plan view
1221 --
1222 v_final := v_plan_view_create || v_select || v_from || v_where;
1223
1224 --
1225 -- bug 6350575
1226 -- 12.1 QWB Usability Improvements
1227 -- Building the final query for the deref view
1228 --
1229 v_deref_view_final := v_deref_view_create || v_select || v_from || v_deref_view_where;
1230
1231 ad_ddl.do_ddl(g_schema, 'QA', ad_ddl.create_view, v_final,
1232 x_view_name);
1233
1234 --
1235 -- bug 6350575
1236 -- 12.1 QWB Usability Improvements
1237 -- Creating the deref view
1238 --
1239 ad_ddl.do_ddl(g_schema, 'QA', ad_ddl.create_view, v_deref_view_final,
1240 v_deref_view_name);
1241
1242 --
1243 -- bug 12596623
1244 -- Create the export view
1245 --
1246 l_export_view_final := 'CREATE OR REPLACE FORCE VIEW QA_'||x_plan_id||'_EXP_V AS '||get_export_view_sql(x_plan_id);
1247 ad_ddl.do_ddl(g_schema, 'QA', ad_ddl.create_view, l_export_view_final, 'QA_'||x_plan_id||'_EXP_V');
1248
1249 --
1250 -- bug 6350575
1251 -- 12.1 QWB Usability Improvements
1252 -- Updating the deref view name in the qa_plans table
1253 --
1254 UPDATE qa_plans set deref_view_name = v_deref_view_name
1255 WHERE plan_id = x_plan_id;
1256
1257 --
1258 -- bug 7409976
1259 -- Regranting the privileges on the plan view and
1260 -- deref view
1261 -- ntungare
1262 --
1263 FOR Cntr in 1..x_pv_grants_tab.COUNT
1264 LOOP
1265 EXECUTE IMMEDIATE x_pv_grants_tab(cntr);
1266 END LOOP; -- End of loop for plan view
1267
1268 FOR Cntr in 1..x_dv_grants_tab.COUNT
1269 LOOP
1270 EXECUTE IMMEDIATE x_dv_grants_tab(cntr);
1271 END LOOP; -- End of loop for deref view
1272 END plan_view;
1273
1274
1275 PROCEDURE import_plan_view(x_view_name IN VARCHAR2, x_old_view_name IN VARCHAR2,
1276 x_plan_id IN NUMBER) IS
1277 --
1278 -- import view creation for QA_RESULTS_INTERFACE and collection import
1279 --
1280
1281 v_select VARCHAR2(20000);
1282
1283 --
1284 -- See comments in global_view about translate()
1285 --
1286 CURSOR pcursor is
1287 SELECT qpc.result_column_name,
1288 upper(translate(qc.name,' ''*{}','_____')) name,
1289 qc.hardcoded_column,
1290 qc.developer_name
1291 FROM qa_plan_chars qpc, qa_chars qc
1292 WHERE qpc.char_id = qc.char_id AND qpc.plan_id = x_plan_id
1293 ORDER BY prompt_sequence;
1294
1295 --
1296 -- bug 7409976
1297 -- ntungare
1298 --
1299 x_grants_tab grants_tab_typ;
1300 BEGIN
1301
1302 --
1303 -- When QLTPLMDF deletes a plan, it calls this proc with
1304 -- null x_view_name and with x_old_view_name populated.
1305 -- Need to drop the old view name and return.
1306 --
1307 IF x_old_view_name IS NOT NULL THEN
1308 --
1309 -- bug 7409976
1310 -- Getting a list of all the grants on the view
1311 -- before dropping it
1312 -- ntungare
1313 --
1314 create_grant_sql(p_view_name => x_old_view_name,
1315 x_grants_tab => x_grants_tab);
1316
1317 drop_view(x_old_view_name);
1318 END IF;
1319
1320 IF x_view_name IS NULL THEN
1321 RETURN;
1322 END IF;
1323
1324 --
1325 -- R12 Project MOAC 4637896
1326 -- Added operating_unit and operating_unit_id as columns in an
1327 -- import view. These are now needed because according to MOAC
1328 -- PO Number is no longer unique across OUs. Thus user has to
1332 --
1329 -- specify OU name in order to resolve PO Number uniquely in
1330 -- the worst case.
1331 -- bso Sun Oct 2 11:41:00 PDT 2005
1333 v_select := 'CREATE OR REPLACE FORCE VIEW "' || upper(x_view_name) ||
1334 '" AS SELECT
1335 transaction_interface_id,
1336 qa_last_updated_by_name,
1337 qa_created_by_name,
1338 collection_id,
1339 source_code,
1340 source_line_id,
1341 process_status,
1342 organization_code,
1343 operating_unit_id,
1344 operating_unit,
1345 plan_name,
1346 insert_type,
1347 matching_elements,
1348 spec_name';
1349
1350 FOR prec in pcursor LOOP
1351
1352 -- If the column is hardcoded column access the developer name and leave
1353 -- the developer name as the actual name for it in the view
1354 -- Otherwise, use the result column name to get the correct column but
1355 -- use the characteristic name as the column name in the view.
1356
1357 --
1358 -- Added the following IF condition for ASO project
1359 -- To uniquely identify a Maintenance_Requirement it takes
1360 -- two fields, namely, Title and Version_Number.
1361 -- rkunchal Thu Jul 25 01:43:48 PDT 2002
1362 --
1363
1364 IF prec.developer_name = 'MAINTENANCE_REQUIREMENT' THEN
1365 v_select := v_select || ', VERSION_NUMBER';
1366 END IF;
1367
1368 IF prec.hardcoded_column IS NOT NULL THEN
1369 v_select := v_select || ', ' || prec.developer_name;
1370 ELSE
1371
1372 -- originally we were going to change the datatype to number or
1373 -- date here if that was the datatype of the element. when we
1374 -- did this, however, it was no longer possible to insert into
1375 -- this column of the view (it became a virtual column).
1376
1377 v_select := v_select || ', ' || prec.result_column_name ||
1378 ' "' || prec.name || '"';
1379 END IF;
1380 END LOOP;
1381
1382 v_select := v_select || ' FROM QA_RESULTS_INTERFACE';
1383
1384 ad_ddl.do_ddl(g_schema, 'QA', ad_ddl.create_view, v_select,
1385 x_view_name);
1386
1387 --
1388 -- bug 7409976
1389 -- Regranting the privileges
1390 -- ntungare
1391 --
1392 FOR Cntr in 1..x_grants_tab.COUNT
1393 LOOP
1394 EXECUTE IMMEDIATE x_grants_tab(cntr);
1395 END LOOP;
1396 END import_plan_view;
1397
1398 BEGIN
1399
1400 g_dummy := fnd_installation.get_app_info(g_fnd, g_status,
1401 g_industry, g_schema);
1402
1403 END QLTVCREB ;
[Home] [Help]