1 PACKAGE BODY QA_BIS AS
2 /* $Header: qltbisb.plb 115.19 2002/11/27 19:22:13 jezheng ship $ */
3
4
5 --
6 -- Create a mirror image of QA_RESULTS into QA_BIS_RESULTS for
7 -- BIS reporting purpose. QA_BIS_RESULTS is also called a
8 -- Summary Table. Not all columns of QA_RESULTS are selected.
9 -- Only those that BIS reports on will be included.
10 --
11 -- There are two methodologies for this mirroring process.
12 -- One is a complete rebuild where the summary table is first
13 -- deleted and truncated. Then the results are transferred.
14 -- Another is by incremental refresh, where only the recently
15 -- inserted or updated records are transferred.
16 --
17 -- A last refresh time is kept in the table QA_BIS_UPDATE_HISTORY.
18 -- It is updated in both complete rebuild and incremental refresh.
19 --
20 -- **OBSOLETE**
21 -- The incremental refresh method does not take care of deletion.
22 -- This means deleted records in QA_RESULTS will appear in the
23 -- summary table, and consequently, in the BIS Report. The current
24 -- strategy is to modify QLTRSMDF/QLTRSINF so that when a row is
25 -- deleted from direct data entry, the corresponding row from
26 -- QA_BIS_RESULTS will be deleted as well.
27 --
28 -- Change of status in QA_RESULTS, however, will be taken care of.
29 -- There is a status column in QA_RESULTS which can be set to 1
30 -- to indicate an invalid rows. Those rows with status=1 will
31 -- not be included in the summary table. When the status is
32 -- updated to other values, as long as the qa_last_update_date is
33 -- also correctly updated, this row will be included in the next
34 -- refresh (complete or incremental). Changing the status of a
35 -- valid row to invalid (status=1) will also delete the row from
36 -- the summary table in the next refresh (complete or incremental).
37 --
38 -- Incremental refresh now takes care of deletion by using
39 -- QA_BIS_UPDATE_HISTORY as delete log. When a record is deleted
40 -- from QA_RESUTLS, qa_bis.delete_log is called to record an audit
41 -- trail.
42 --
43 -- Author: Bryan So (bso)
44 -- Contribution: Revathy Narasimhan (rnarasim)
45 --
46 -- Mon Jun 21 17:49:22 PDT 1999
47 --
48
49 Incremental constant number := 1;
50 Complete constant number := 2;
51
52 --
53 -- Maximum no. of rows inserted before a commit. We have had
54 -- reports of performance problem when inserting a large no. of
55 -- records with a single commit at the end. The problem happens
56 -- because of the immensely large rollback segment that needs to
57 -- be maintained. Intermittent commit will solve the problem.
58 -- This variable will be initiated by the wrapper from a SRS param.
59 --
60 g_intermittent_commit number;
61
62 --
63 -- Date of last refresh. Could be null if never performed.
64 -- Initialized by wrapper.
65 --
66 g_last_refresh date;
67
68 --
69 -- Current system date. Initialized by the wrapper.
70 -- When performing incremental refresh of the BIS Summary Table,
71 -- all records updated *before* (but not at) this and *after or at*
72 -- last refresh datetime will be inserted.
73 --
74 g_current_datetime date;
75
76 --
77 -- Standard who columns.
78 --
79 who_user_id constant number := fnd_global.conc_login_id;
80 who_request_id constant number := fnd_global.conc_request_id;
81 who_last_update_login constant number := fnd_global.conc_login_id;
82 who_program_id constant number := fnd_global.conc_program_id;
83 who_program_application_id constant number := fnd_global.prog_appl_id;
84
85 --
86 -- schema information for DDL
87 --
88 qa_status varchar2(1);
89 qa_industry varchar2(10);
90 qa_schema varchar2(30);
91
92
93 --
94 -- Private Functions
95 --
96
97 PROCEDURE truncate_summary_table IS
98 --
99 -- Delete the entire BIS Summary Table. Done for complete rebuild.
100 --
101 BEGIN
102 --
103 -- Truncate is used for quicker performance and reuse of
104 -- space. The reuse storage clause actually means do not
105 -- purge the storage but reserve it for the QBR table.
106 -- Since this command is followed by immediate inserts, this
107 -- would be a good option.
108 --
109 qlttrafb.exec_sql('truncate table ' ||
110 qa_schema || '.QA_BIS_RESULTS reuse storage');
111
112 --
113 -- Performance notes. appsperf actually recommends an alternative
114 -- of dropping the table here. Then the new table can be created:
115 -- create table as <select_stmt>. This approach will not make
116 -- use of a rollback segment; therefore no need for intermittent
117 -- commits.
118 --
119 -- Problem is, there are many areas to pay attention to. We have
120 -- to drop and create the table using ad_ddl.do_array_ddl calls.
121 -- We have to worry about recreating indices on qa_bis_results...
122 -- Not implemented yet.
123 -- bso
124 --
125 END truncate_summary_table;
126
127
128 PROCEDURE delete_update_history IS
129 --
130 -- Delete the audit trail in the update history table. The audit
131 -- trail stores the primary key of the purged records (i.e. those
132 -- deleted from qa_results). This procedure should be called only
133 -- by complete rebuild or by procedure delete_purged_rows.
134 --
135 BEGIN
136 DELETE
137 FROM qa_bis_update_history
138 WHERE occurrence >= 0 AND
139 last_update_date < g_current_datetime;
140 commit;
141 END delete_update_history;
142
143
144 PROCEDURE delete_purged_rows IS
145 --
146 -- Delete those records that have been deleted from qa_results.
147 --
148 BEGIN
149 DELETE
150 FROM qa_bis_results qbr
151 WHERE qbr.occurrence IN
152 (SELECT h.occurrence
153 FROM qa_bis_update_history h
154 WHERE h.occurrence >= 0 AND
155 h.last_update_date < g_current_datetime);
156 commit;
157 delete_update_history;
158 END delete_purged_rows;
159
160
161 PROCEDURE delete_updated_rows IS
162 --
163 -- Delete those rows in BIS Summary Table whose counterpart in
164 -- QA_RESULTS have been modified since last_refresh.
165 --
166 -- Notes on efficiency:
167 -- . QA_RESULTS must have either an index on qa_creation_date
168 -- or an index on qa_last_update_date (preferrable)
169 --
170 -- . QA_BIS_RESULTS must have a unique index on occurrence.
171 --
172 -- Notes on coding standard:
173 -- . Never use WHO columns to quality rows for processing.
174 -- Coding Standards R10SC p. 3-4.
175 -- Therefore, qa_last_update_date is used instead.
176 --
177 BEGIN
178 DELETE
179 FROM qa_bis_results qbr
180 WHERE occurrence IN (
181 SELECT occurrence
182 FROM qa_results qr
183 WHERE qr.qa_last_update_date < g_current_datetime AND
184 g_last_refresh BETWEEN
185 qr.qa_creation_date AND qr.qa_last_update_date);
186 commit;
187
188 END delete_updated_rows;
189
190
191 PROCEDURE construct_decode(alias varchar2, x_char_id number,
192 s in out NOCOPY varchar2) IS
193 --
194 -- Dynamically construct a decode function that decodes the
195 -- value for a softcoded collection element. The decode
196 -- statement started at the end of s.
197 --
198 -- Performance notes: Will be more efficient if qa_plan_chars
199 -- has an index on char_id.
200 --
201 x_plan_id number;
202 x_result_column_name varchar2(30);
203 CURSOR c IS
204 SELECT plan_id, result_column_name
205 FROM qa_plan_chars
206 WHERE char_id = x_char_id;
207
208 --
209 -- Bug 1357601. The decode statement used to "straighten" softcoded
210 -- elements into a single column has a sever limit of 255 parameters.
211 -- These variables are added to resolve the limit. When the limit is
212 -- up, we use the very last parameter of the decode statement to
213 -- start a new decode, which can have another 255 params. This is
214 -- repeated as necessary.
215 --
216 -- decode_count keeps the no. of decodes being used so far.
217 -- decode_param keeps the no. of parameters in the current decode.
218 -- decode_limit is the server limit. This should be updated if
219 -- the server is enhanced in the future.
220 --
221 -- bso Thu Sep 21 13:11:19 PDT 2000
222 --
223 decode_count NUMBER;
224 decode_param NUMBER;
225 decode_limit CONSTANT NUMBER := 255;
226
227 BEGIN
228 OPEN c;
229 FETCH c INTO x_plan_id, x_result_column_name;
230
231 IF c%found THEN
232
233 s := s || 'decode(' || alias || '.plan_id';
234
235 decode_count := 1;
236 decode_param := 1;
237
238 --
239 -- Find <plan_id, result_column_name> pairs for all plans
240 -- with x_char_id as collection element.
241 --
242 WHILE c%found LOOP
243
244 IF decode_param >= (decode_limit - 2) THEN
245 s := s || ', decode(qr.plan_id';
246 decode_count := decode_count + 1;
247 decode_param := 1;
248 END IF;
249
250 s := s || ', ' || to_char(x_plan_id) || ', ' ||
251 alias || '.' || x_result_column_name;
252 decode_param := decode_param + 2;
253
254 FETCH c INTO x_plan_id, x_result_column_name;
255 END LOOP;
256 CLOSE c;
257
258 FOR n IN 1 .. decode_count LOOP
259 s := s || ')';
260 END LOOP;
261 ELSE
262 CLOSE c;
263 s := s || 'null'; -- no such char_id, simply select null
264 END IF;
265 END construct_decode;
266
267
268 PROCEDURE construct_summary_table(s in out NOCOPY varchar2) IS
269 --
270 -- Construct a SQL statement that selects from QA_RESULTS and
271 -- format it so that the results can be directly inserted into
272 -- the BIS Summary Table after putting in WHO information.
273 --
274 -- The final sql statement looks like this:
275 --
276 -- SELECT
277 -- :w1 to :w9 standard who columns
278 -- qr.organization_id,
279 -- 'decoded by view' organization_name,
280 -- qr.plan_id,
281 -- qr.collection_id,
282 -- qr.occurrence,
283 -- 'decoded by view' plan_type_code,
284 -- 'decoded by view' meaning,
285 -- 'decoded by view' plan_name,
286 -- qr.item_id,
287 -- 'decoded by view' item,
288 -- -1 lot_control_code,
289 -- qr.lot_number,
290 -- decode(qr.plan_id, 120, qr.CHARACTER3 ...) defect_code,
291 -- decode(qr.plan_id, 120, qr.CHARACTER2 ...) quantity_defective,
292 -- qr.qa_creation_date,
293 -- qr.qa_last_update_date
294 -- FROM
295 -- qa_results qr
296 -- WHERE
297 -- (qr.status is null or qr.status = 2)
298 -- and qr.qa_last_update_date < :today
299 --
300 -- bso
301 --
302 defect_code_char_id constant number := 100;
303 quantity_defective_char_id constant number := 101;
304 BEGIN
305 --
306 -- Construct the select clause
307 --
308 s := 'SELECT '||
309 who_request_id || ',' ||
310 who_program_application_id || ',' ||
311 who_program_id || ',' ||
312 'sysdate,' ||
313 who_user_id || ',' ||
314 'sysdate,' ||
315 who_last_update_login || ',' ||
316 who_user_id || ',' ||
317 'sysdate,' ||
318 'qr.organization_id,' ||
319 '''decoded by view'' organization_name,' ||
320 'qr.plan_id,' ||
321 'qr.collection_id,' ||
322 'qr.occurrence,' ||
323 '''decoded by view'' plan_type_code,' ||
324 '''decoded by view'' meaning,' ||
325 '''decoded by view'' plan_name,' ||
326 'qr.item_id,' ||
327 '''decoded by view'' item,' ||
331 --
328 '-1 lot_control_code,' ||
329 'qr.lot_number,';
330
332 -- The followings construct the dynamic select clauses
333 -- required for softcoded collection elements.
334 --
335 construct_decode('qr', defect_code_char_id, s);
336 s := s || ' defect_code,';
337
338 construct_decode('qr', quantity_defective_char_id, s);
339 s := s || ' quantity_defective,' ||
340 'qr.qa_creation_date,' ||
341 'qr.qa_last_update_date ' ||
342 'FROM qa_results qr ' ||
343 'WHERE (qr.status is null or qr.status = 2) ' ||
344 'and qr.qa_last_update_date < :today';
345
346 END construct_summary_table;
347
348
349 PROCEDURE write_last_refresh_datetime(x_last_refresh_time date) IS
350 --
351 -- Write a refresh time to the QA_BIS_UPDATE_HISTORY table.
352 --
353 BEGIN
354 UPDATE qa_bis_update_history SET
355 request_id = who_request_id,
356 program_application_id = who_program_application_id,
357 program_id = who_program_id,
358 program_update_date = sysdate,
359 last_update_login = who_last_update_login,
360 last_updated_by = who_user_id,
361 last_update_date = sysdate,
362 last_refresh_time = x_last_refresh_time
363 WHERE occurrence = -1; -- special record for refresh time.
364 commit;
365 END write_last_refresh_datetime;
366
367
368 PROCEDURE init_last_refresh_datetime IS
369 --
370 -- Initialize the QA_BIS_UPDATE_HISTORY table.
371 --
372 BEGIN
373 INSERT INTO qa_bis_update_history(
374 request_id,
375 program_application_id,
376 program_id,
377 program_update_date,
378 created_by,
379 creation_date,
380 last_update_login,
381 last_updated_by,
382 last_update_date,
383 occurrence,
384 last_refresh_time
385 )
386 VALUES (
387 who_request_id, -- request_id
388 who_program_application_id, -- program_application_id
389 who_program_id, -- program_id
390 sysdate, -- program_update_date
391 who_user_id, -- created_by
392 sysdate, -- creation_date
393 who_last_update_login, -- last_update_login
394 who_user_id, -- last_updated_by
395 sysdate, -- last_update_date
396 -1, -- special flag
397 sysdate); -- last_refresh_time
398 commit;
399 END init_last_refresh_datetime;
400
401
402 FUNCTION get_last_refresh_datetime RETURN date IS
403 --
404 -- Get last refresh time from the QA_BIS_UPDATE_HISTORY table
405 --
406 d date;
407 CURSOR c IS
408 SELECT last_refresh_time
409 FROM qa_bis_update_history
410 WHERE occurrence = -1;
411 -- special flag that indicates this is the record
412 -- to look for refresh time
413 BEGIN
414 OPEN c;
415 FETCH c INTO d;
416 IF c%notfound THEN
417 CLOSE c;
418 init_last_refresh_datetime;
419 RETURN null;
420 ELSE
421 CLOSE c;
422 RETURN d;
423 END IF;
424 END get_last_refresh_datetime;
425
426
427 PROCEDURE insert_summary_table(select_statement varchar2,
428 method number) IS
429 --
430 -- Insert into the summary table by selecting rows from the
431 -- select_statement.
432 --
433 -- See comments for construct_summary_table to find out what
434 -- columns are selected.
435 --
436 -- The summary table looks like this:
437 --
438 -- request_id number
439 -- program_application_id number
440 -- program_id number
441 -- program_update_date date
442 -- created_by number
443 -- creation_date date
444 -- last_update_login number
445 -- last_updated_by number
446 -- last_update_date date
447 -- qa_creation_date date not null
448 -- qa_last_update_date date not null
449 -- organization_id number not null
450 -- organization_name varchar2(60) not null
451 -- plan_id number not null
452 -- plan_name varchar2(30) not null
453 -- collection_id number not null
454 -- occurrence number not null unique
455 -- plan_type_code varchar2(30)
456 -- plan_type_meaning varchar2(80)
457 -- item_id number
458 -- item varchar2(2000)
459 -- lot_control_code number
460 -- lot_number varchar2(30)
464 -- bso
461 -- defect_code varchar2(150)
462 -- quantity_defective varchar2(150)
463 --
465 --
466 insert_statement varchar2(32000);
467
468 BEGIN
469
470 --
471 -- The following defines the cursor for inserting data into
472 -- QA_BIS_RESULTS.
473 --
474 insert_statement := 'INSERT /*+ parallel (qb,default) append */ ' ||
475 'INTO qa_bis_results qb(' ||
476 'request_id,' ||
477 'program_application_id,' ||
478 'program_id,' ||
479 'program_update_date,' ||
480 'created_by,' ||
481 'creation_date,' ||
482 'last_update_login,' ||
483 'last_updated_by,' ||
484 'last_update_date,' ||
485 'organization_id,' ||
486 'organization_name,' ||
487 'plan_id,' ||
488 'collection_id,' ||
489 'occurrence,' ||
490 'plan_type_code,' ||
491 'plan_type_meaning,' ||
492 'plan_name,' ||
493 'item_id,' ||
494 'item,' ||
495 'lot_control_code,' ||
496 'lot_number,' ||
497 'defect_code,' ||
498 'quantity_defective,' ||
499 'qa_creation_date,' ||
500 'qa_last_update_date) ' || select_statement;
501
502 IF method = Complete THEN
503 EXECUTE IMMEDIATE insert_statement USING
504 g_current_datetime;
505 ELSE
506 EXECUTE IMMEDIATE insert_statement USING
507 g_current_datetime,
508 g_last_refresh;
509 END IF;
510
511 COMMIT;
512
513 END insert_summary_table;
514
515
516
517 --
518 -- Main Entry Points
519 --
520
521
522
523 FUNCTION complete_rebuild RETURN number IS
524 --
525 -- Completely rebuild the BIS Summary Table. This involves deleting
526 -- the existing table, truncate it, and re-compute the records.
527 -- Return a SQL error code. 0 indicates no error.
528 -- bso
529 --
530 select_statement varchar2(30000);
531 BEGIN
532 truncate_summary_table;
533 delete_update_history;
534 construct_summary_table(select_statement);
535 insert_summary_table(select_statement, Complete);
536 write_last_refresh_datetime(g_current_datetime);
537
538 RETURN 0;
539 END complete_rebuild;
540
541
542 FUNCTION incremental_rebuild RETURN number IS
543 --
544 -- Rebuild the BIS Summary Table by incremental refresh method.
545 -- The records in QA_RESULTS that have been modified since the
546 -- previous refresh datetime will be inserted into the BIS
547 -- Table. Two kinds of records in QA_RESULTS will be processed,
548 -- those that are new and those that have been updated.
549 --
550 -- Notes on efficiency:
551 -- . QA_RESULTS should have an index on qa_last_update_date.
552 --
553 -- Return a SQL error code. 0 indicates no error.
554 -- bso
555 --
556 select_statement varchar2(30000);
557 BEGIN
558 IF g_last_refresh is null THEN
559 --
560 -- Exception. The first time Rebuild is run, last_refresh
561 -- will be null. Just do complete rebuild.
562 --
563 RETURN complete_rebuild;
564 ELSE
565 delete_purged_rows;
566 delete_updated_rows;
567 construct_summary_table(select_statement);
568 --
569 -- Add where clause to select only the new data.
570 --
571 select_statement := select_statement ||
572 ' and qr.qa_last_update_date >= :refresh';
573 insert_summary_table(select_statement, Incremental);
574 write_last_refresh_datetime(g_current_datetime);
575 RETURN 0;
576 END IF;
577 END incremental_rebuild;
578
579
580
581
582 --
583 -- Public Functions
584 --
585
586
587
588 procedure rebuild is
589 --
590 -- Debug routine for testing purpose only.
591 --
592 dummy boolean;
593 n number;
594 begin
595 dummy := fnd_installation.get_app_info('QA', qa_status,
596 qa_industry, qa_schema);
597 g_intermittent_commit := 1000;
598 g_current_datetime := qltdate.get_sysdate;
599 g_last_refresh := get_last_refresh_datetime;
600
601 n := complete_rebuild;
602 end;
603
604
605 procedure refresh is
606 --
607 -- Debug routine for testing purpose only.
608 --
609 dummy boolean;
610 n number;
611 begin
612 dummy := fnd_installation.get_app_info('QA', qa_status,
613 qa_industry, qa_schema);
614 g_intermittent_commit := 1000;
615 g_current_datetime := qltdate.get_sysdate;
616 g_last_refresh := get_last_refresh_datetime;
617
618 n := incremental_rebuild;
619 end;
620
621
622 PROCEDURE delete_log(x_occurrence number) IS
623 --
624 -- This procedure is used when a row is deleted from qa_results.
625 -- Called by QLTRES.Q_RES_PRIVATE.delete_row.
626 --
627 BEGIN
628 --
629 -- Insert a row into QA_BIS_UPDATE_HISTORY to log the deletion.
630 --
631
632 INSERT INTO qa_bis_update_history(
633 created_by,
634 creation_date,
635 last_update_login,
636 last_updated_by,
637 last_update_date,
638 occurrence
639 )
640 VALUES (
641 who_user_id, -- created_by
642 sysdate, -- creation_date
643 who_last_update_login, -- last_update_login
644 who_user_id, -- last_updated_by
645 sysdate, -- last_update_date
646 x_occurrence);
647
648 -- Do not commit because the user may rollback their delete.
649 -- Wait until forms does it.
650
651 END delete_log;
652
653
654 PROCEDURE WRAPPER(ERRBUF OUT NOCOPY VARCHAR2,
655 RETCODE OUT NOCOPY NUMBER,
656 ARGUMENT1 IN VARCHAR2, -- Rebuild strategy
657 ARGUMENT2 IN VARCHAR2) IS -- # of rows between commits
658 method number := nvl(to_number(argument1), 1);
659 dummy boolean;
660 BEGIN
661
662 dummy := fnd_installation.get_app_info('QA', qa_status,
663 qa_industry, qa_schema);
664
665 --
666 -- Intermittent_commit specifies the no. of rows to process
667 -- between commits. It is here for performance purpose. If
668 -- there is a large no. of rows to process, the rollback segment
669 -- becomes increasingly large and drags down performance.
670 --
671 -- OBSOLETE. The parallel and append database hints do much
672 -- better than intermittent commit.
673 -- bso Mon May 21 18:26:38 PDT 2001
674 g_intermittent_commit := nvl(to_number(argument2), 1000);
675
676 --
677 -- Get current time and last refresh time. If last refresh is
678 -- null, then a complete rebuild will be performed. This usually
679 -- means this is the first time the summary table is being used.
680 --
681 -- For incremental rebuild:
682 --
683 -- Delete all rows from QA_BIS_RESULTS that satisfy this
684 --
685 -- qr.qa_creation_date <= last_refresh <= qr.qa_last_update_date
686 -- and qr.qa_last_update_date < current_date
687 --
688 -- The last condition is needed because we will be writing
689 -- current_date to the QA_BIS_UPDATE_HISTORY table as last
690 -- refresh time. All rows updated at or after current_date
691 -- will be excluded in this rebuild. They will be mirrored
692 -- in the next rebulid.
693 --
694 -- Then mirror all rows in QA_RESULTS that satify this
695 --
696 -- last_refresh <= qr.qa_last_update_date < current_date
697 --
698 g_current_datetime := qltdate.get_sysdate;
699 g_last_refresh := get_last_refresh_datetime;
700
701 IF method = Incremental THEN
702 retcode := incremental_rebuild;
703 ELSIF method = Complete THEN
704 retcode := complete_rebuild;
705 ELSE
706 retcode := 2;
707 END IF;
708
709 errbuf := '';
710 END;
711
712
713 END QA_BIS;
714
[Home] [Help]