[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
who_last_update_login constant number := fnd_global.conc_login_id;
PROCEDURE delete_update_history IS
--
-- Delete the audit trail in the update history table. The audit
-- trail stores the primary key of the purged records (i.e. those
-- deleted from qa_results). This procedure should be called only
-- by complete rebuild or by procedure delete_purged_rows.
--
BEGIN
DELETE
FROM qa_bis_update_history
WHERE occurrence >= 0 AND
last_update_date < g_current_datetime;
END delete_update_history;
PROCEDURE delete_purged_rows IS
--
-- Delete those records that have been deleted from qa_results.
--
BEGIN
DELETE
FROM qa_bis_results qbr
WHERE qbr.occurrence IN
(SELECT h.occurrence
FROM qa_bis_update_history h
WHERE h.occurrence >= 0 AND
h.last_update_date < g_current_datetime);
delete_update_history;
END delete_purged_rows;
PROCEDURE delete_updated_rows IS
--
-- Delete those rows in BIS Summary Table whose counterpart in
-- QA_RESULTS have been modified since last_refresh.
--
-- Notes on efficiency:
-- . QA_RESULTS must have either an index on qa_creation_date
-- or an index on qa_last_update_date (preferrable)
--
-- . QA_BIS_RESULTS must have a unique index on occurrence.
--
-- Notes on coding standard:
-- . Never use WHO columns to quality rows for processing.
-- Coding Standards R10SC p. 3-4.
-- Therefore, qa_last_update_date is used instead.
--
BEGIN
DELETE
FROM qa_bis_results qbr
WHERE occurrence IN (
SELECT occurrence
FROM qa_results qr
WHERE qr.qa_last_update_date < g_current_datetime AND
g_last_refresh BETWEEN
qr.qa_creation_date AND qr.qa_last_update_date);
END delete_updated_rows;
SELECT plan_id, result_column_name
FROM qa_plan_chars
WHERE char_id = x_char_id;
s := s || 'null'; -- no such char_id, simply select null
s := 'SELECT '||
who_request_id || ',' ||
who_program_application_id || ',' ||
who_program_id || ',' ||
'sysdate,' ||
who_user_id || ',' ||
'sysdate,' ||
who_last_update_login || ',' ||
who_user_id || ',' ||
'sysdate,' ||
'qr.organization_id,' ||
'''decoded by view'' organization_name,' ||
'qr.plan_id,' ||
'qr.collection_id,' ||
'qr.occurrence,' ||
'''decoded by view'' plan_type_code,' ||
'''decoded by view'' meaning,' ||
'''decoded by view'' plan_name,' ||
'qr.item_id,' ||
'''decoded by view'' item,' ||
'-1 lot_control_code,' ||
'qr.lot_number,';
'qr.qa_last_update_date ' ||
'FROM qa_results qr ' ||
'WHERE (qr.status is null or qr.status = 2) ' ||
'and qr.qa_last_update_date < :today';
UPDATE qa_bis_update_history SET
request_id = who_request_id,
program_application_id = who_program_application_id,
program_id = who_program_id,
program_update_date = sysdate,
last_update_login = who_last_update_login,
last_updated_by = who_user_id,
last_update_date = sysdate,
last_refresh_time = x_last_refresh_time
WHERE occurrence = -1; -- special record for refresh time.
INSERT INTO qa_bis_update_history(
request_id,
program_application_id,
program_id,
program_update_date,
created_by,
creation_date,
last_update_login,
last_updated_by,
last_update_date,
occurrence,
last_refresh_time
)
VALUES (
who_request_id, -- request_id
who_program_application_id, -- program_application_id
who_program_id, -- program_id
sysdate, -- program_update_date
who_user_id, -- created_by
sysdate, -- creation_date
who_last_update_login, -- last_update_login
who_user_id, -- last_updated_by
sysdate, -- last_update_date
-1, -- special flag
sysdate); -- last_refresh_time
SELECT last_refresh_time
FROM qa_bis_update_history
WHERE occurrence = -1;
PROCEDURE insert_summary_table(select_statement varchar2,
method number) IS
--
-- Insert into the summary table by selecting rows from the
-- select_statement.
--
-- See comments for construct_summary_table to find out what
-- columns are selected.
--
-- The summary table looks like this:
--
-- request_id number
-- program_application_id number
-- program_id number
-- program_update_date date
-- created_by number
-- creation_date date
-- last_update_login number
-- last_updated_by number
-- last_update_date date
-- qa_creation_date date not null
-- qa_last_update_date date not null
-- organization_id number not null
-- organization_name varchar2(60) not null
-- plan_id number not null
-- plan_name varchar2(30) not null
-- collection_id number not null
-- occurrence number not null unique
-- plan_type_code varchar2(30)
-- plan_type_meaning varchar2(80)
-- item_id number
-- item varchar2(2000)
-- lot_control_code number
-- lot_number varchar2(30)
-- defect_code varchar2(150)
-- quantity_defective varchar2(150)
--
-- bso
--
insert_statement varchar2(32000);
insert_statement := 'INSERT /*+ parallel (qb,default) append */ ' ||
'INTO qa_bis_results qb(' ||
'request_id,' ||
'program_application_id,' ||
'program_id,' ||
'program_update_date,' ||
'created_by,' ||
'creation_date,' ||
'last_update_login,' ||
'last_updated_by,' ||
'last_update_date,' ||
'organization_id,' ||
'organization_name,' ||
'plan_id,' ||
'collection_id,' ||
'occurrence,' ||
'plan_type_code,' ||
'plan_type_meaning,' ||
'plan_name,' ||
'item_id,' ||
'item,' ||
'lot_control_code,' ||
'lot_number,' ||
'defect_code,' ||
'quantity_defective,' ||
'qa_creation_date,' ||
'qa_last_update_date) ' || select_statement;
EXECUTE IMMEDIATE insert_statement USING
g_current_datetime;
EXECUTE IMMEDIATE insert_statement USING
g_current_datetime,
g_last_refresh;
END insert_summary_table;
select_statement varchar2(30000);
delete_update_history;
construct_summary_table(select_statement);
insert_summary_table(select_statement, Complete);
select_statement varchar2(30000);
delete_purged_rows;
delete_updated_rows;
construct_summary_table(select_statement);
select_statement := select_statement ||
' and qr.qa_last_update_date >= :refresh';
insert_summary_table(select_statement, Incremental);
PROCEDURE delete_log(x_occurrence number) IS
--
-- This procedure is used when a row is deleted from qa_results.
-- Called by QLTRES.Q_RES_PRIVATE.delete_row.
--
BEGIN
--
-- Insert a row into QA_BIS_UPDATE_HISTORY to log the deletion.
--
INSERT INTO qa_bis_update_history(
created_by,
creation_date,
last_update_login,
last_updated_by,
last_update_date,
occurrence
)
VALUES (
who_user_id, -- created_by
sysdate, -- creation_date
who_last_update_login, -- last_update_login
who_user_id, -- last_updated_by
sysdate, -- last_update_date
x_occurrence);
END delete_log;