[Home] [Help]
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: --
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.
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
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:
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;
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
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
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,' ||
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,' ||
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: --