eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.QA_BIS dependencies on QA_BIS

Line 1: PACKAGE BODY QA_BIS AS

1: PACKAGE BODY QA_BIS AS
2: /* $Header: qltbisb.plb 115.19 2002/11/27 19:22:13 jezheng ship $ */
3:
4:
5: --

Line 6: -- Create a mirror image of QA_RESULTS into QA_BIS_RESULTS for

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: --

Line 7: -- BIS reporting purpose. QA_BIS_RESULTS is also called a

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.

Line 17: -- A last refresh time is kept in the table QA_BIS_UPDATE_HISTORY.

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.

Line 26: -- QA_BIS_RESULTS will be deleted as well.

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

Line 39: -- QA_BIS_UPDATE_HISTORY as delete log. When a record is deleted

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)

Line 40: -- from QA_RESUTLS, qa_bis.delete_log is called to record an audit

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)

Line 110: qa_schema || '.QA_BIS_RESULTS reuse storage');

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:

Line 121: -- We have to worry about recreating indices on qa_bis_results...

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;

Line 137: FROM qa_bis_update_history

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;

Line 150: FROM qa_bis_results qbr

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

Line 153: FROM qa_bis_update_history h

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;

Line 170: -- . QA_BIS_RESULTS must have a unique index on occurrence.

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.

Line 179: FROM qa_bis_results qbr

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

Line 351: -- Write a refresh time to the QA_BIS_UPDATE_HISTORY 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,

Line 354: UPDATE qa_bis_update_history SET

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,

Line 370: -- Initialize the QA_BIS_UPDATE_HISTORY table.

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,

Line 373: INSERT INTO qa_bis_update_history(

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,

Line 404: -- Get last refresh time from the QA_BIS_UPDATE_HISTORY table

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

Line 409: FROM qa_bis_update_history

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

Line 472: -- QA_BIS_RESULTS.

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,' ||

Line 475: 'INTO qa_bis_results qb(' ||

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,' ||

Line 629: -- Insert a row into QA_BIS_UPDATE_HISTORY to log the deletion.

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,

Line 632: INSERT INTO qa_bis_update_history(

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,

Line 683: -- Delete all rows from QA_BIS_RESULTS that satisfy this

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: --

Line 689: -- current_date to the QA_BIS_UPDATE_HISTORY table as last

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: --

Line 713: END QA_BIS;

709: errbuf := '';
710: END;
711:
712:
713: END QA_BIS;
714: