[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE find_update_seq_in_collection(p_cur_record_indicator NUMBER,
p_seq_position NUMBER,
p_action NUMBER,
p_seq_value IN OUT NOCOPY VARCHAR2);
PROCEDURE update_record(p_total_rec_count NUMBER);
UPDATE qa_chars
SET sequence_nextval = sequence_nextval + sequence_increment
WHERE char_id = p_char_id
RETURNING sequence_nextval - sequence_increment
INTO l_curr_val;
CURSOR c IS SELECT sequence_prefix, sequence_suffix, sequence_separator,
sequence_length, sequence_zero_pad
FROM qa_chars
WHERE char_id = p_char_id;
UPDATE qa_chars
SET sequence_nextval = sequence_nextval + sequence_increment
WHERE char_id = p_char_id
RETURNING sequence_nextval - sequence_increment
INTO l_curr_val;
SELECT plan_id,
collection_id,
occurrence,
txn_header_id
FROM qa_results
WHERE txn_header_id = p_txn_header_id;
UPDATE qa_pc_results_relationship
SET child_txn_header_id = results_tab(cntr).txn_header_id
WHERE child_plan_id = results_tab(cntr).plan_id
AND child_collection_id = results_tab(cntr).collection_id
AND child_occurrence = results_tab(cntr).occurrence;
SELECT distinct qr.plan_id
FROM qa_results qr
WHERE qr.collection_id = p_collection_id AND
NOT EXISTS (SELECT 1 FROM qa_pc_results_relationship qprr
WHERE qprr.child_plan_id = qr.plan_id AND
qprr.child_collection_id = qr.collection_id AND
qprr.child_occurrence = qr.occurrence);
update_record(l_row_count);
update_record(l_row_count);
SELECT occurrence,collection_id,plan_id,txn_header_id,
sequence1,sequence2,sequence3,
sequence4,sequence5,sequence6,
sequence7,sequence8,sequence9,
sequence10,sequence11,sequence12,
sequence13,sequence14,sequence15
FROM qa_results
WHERE collection_id = p_collection_id;
SELECT occurrence,collection_id,plan_id,txn_header_id,
sequence1,sequence2,sequence3,
sequence4,sequence5,sequence6,
sequence7,sequence8,sequence9,
sequence10,sequence11,sequence12,
sequence13,sequence14,sequence15
FROM qa_results
WHERE txn_header_id = p_txn_header_id;
SELECT 0 child_plan_id,
0 child_collection_id,
0 child_occurrence,
plan_id parent_plan_id,
collection_id parent_collection_id,
occurrence parent_occurrence,
0 levels
FROM qa_results qr
WHERE qr.plan_id = p_plan_id AND
qr.collection_id = p_collection_id
UNION ALL
SELECT child_plan_id,
child_collection_id,
child_occurrence,
parent_plan_id,
parent_collection_id,
parent_occurrence,
level levels
FROM qa_pc_results_relationship r
START WITH r.parent_plan_id = p_plan_id AND
r.parent_collection_id = p_collection_id
CONNECT BY PRIOR r.child_occurrence = r.parent_occurrence
ORDER BY levels, parent_occurrence, child_occurrence;
SELECT 0 child_plan_id,
0 child_collection_id,
0 child_occurrence,
plan_id parent_plan_id,
collection_id parent_collection_id,
occurrence parent_occurrence,
0 levels
FROM qa_results qr
WHERE qr.plan_id = p_plan_id AND
qr.txn_header_id = p_txn_header_id
UNION ALL
SELECT child_plan_id,
child_collection_id,
child_occurrence,
parent_plan_id,
parent_collection_id,
parent_occurrence,
level levels
FROM qa_pc_results_relationship_v r
WHERE r.child_txn_header_id = p_txn_header_id
START WITH r.parent_plan_id = p_plan_id
CONNECT BY PRIOR r.child_occurrence = r.parent_occurrence
ORDER BY levels; */
/* SELECT 0 child_plan_id,
0 child_collection_id,
0 child_occurrence,
plan_id parent_plan_id,
collection_id parent_collection_id,
occurrence parent_occurrence,
0 levels
FROM qa_results qr
WHERE qr.plan_id = p_plan_id AND
qr.txn_header_id = p_txn_header_id
UNION ALL
SELECT child_plan_id,
child_collection_id,
child_occurrence,
parent_plan_id,
parent_collection_id,
parent_occurrence,
level levels
FROM qa_pc_results_relationship r
WHERE p_txn_header_id =
(SELECT qr.txn_header_id
FROM qa_results qr
WHERE qr.plan_id = r.child_plan_id and
qr.collection_id = r.child_collection_id and
qr.occurrence = r.child_occurrence)
START WITH r.parent_plan_id = p_plan_id
CONNECT BY PRIOR r.child_occurrence = r.parent_occurrence
ORDER BY levels, parent_occurrence; */
SELECT 0 child_plan_id,
0 child_collection_id,
0 child_occurrence,
plan_id parent_plan_id,
collection_id parent_collection_id,
occurrence parent_occurrence,
0 levels
FROM qa_results qr
WHERE qr.plan_id = p_plan_id AND
qr.txn_header_id = p_txn_header_id
UNION ALL
--
-- bug 16037863
-- With the fix done for bug 14134055, the child txn header id in
-- qa_pc_results_relationhsip table will be in sync with that in
-- qa_results. So we can use the original form of the SQL that starts
-- from a parent plan id and gets all the rows in the hierarchy and
-- then picks the one with the specific txn_header_id
--
SELECT child_plan_id,
child_collection_id,
child_occurrence,
parent_plan_id,
parent_collection_id,
parent_occurrence,
levels
FROM (
SELECT child_plan_id,
child_collection_id,
child_occurrence,
parent_plan_id,
parent_collection_id,
parent_occurrence,
level levels,
child_txn_header_id
FROM qa_pc_results_relationship r
START WITH r.parent_plan_id = p_plan_id
CONNECT BY PRIOR r.child_occurrence = r.parent_occurrence)
WHERE child_txn_header_id = p_txn_header_id
ORDER BY levels, parent_occurrence, child_occurrence;
SELECT sequence_value
FROM qa_seq_audit_history
WHERE plan_id = p_plan_id
AND collection_id = p_collection_id
AND occurrence = p_occurrence
AND char_id = p_char_id;
SELECT name FROM qa_plans
WHERE plan_id = l_plan_id;
l_childUpdate_retval VARCHAR2(10);
find_update_seq_in_collection(p_parent_rec_indicator,
g_parent_plan_seq_nos(i),
1,
l_seq_value);
find_update_seq_in_collection(p_cur_rec_indicator,
i,
2,
l_seq_value);
SELECT qc.char_id,substr(qpc.result_column_name,9,10) position
FROM qa_plan_chars qpc,
qa_chars qc
WHERE qpc.plan_id = p_plan_id AND
qpc.char_id = qc.char_id AND
qpc.enabled_flag = 1 AND
qc.datatype = 5;
SELECT parent_char_id, substr(parent_database_column,9,10) parent_seq_position,
child_char_id, substr(child_database_column,9,10) child_seq_position
FROM qa_pc_result_columns_v
WHERE parent_plan_id = p_parent_plan_id AND
child_plan_id = p_plan_id AND
element_relationship_type = 1 AND
parent_dataType = 5 AND
child_dataType = 5;
g_curr_plan_seq_char_ids.DELETE;
g_parent_plan_seq_char_ids.DELETE;
g_parent_plan_seq_nos.DELETE;
PROCEDURE find_update_seq_in_collection(p_cur_record_indicator NUMBER,
p_seq_position NUMBER,
p_action NUMBER,
p_seq_value IN OUT NOCOPY VARCHAR2) IS
/*
p_action:
Holds value 1 Meaning get the seq value from parent record in plsql table
Holds value 2 Meaning copy the seq value into child record in plsql table
p_cur_record_indicator:
Indicates the record postion in the plsql table that should be used to
get or update the seq values from parent or child record respectively
p_seq_position:
Holds values from 1 to 15 to identify the result column name in qa_results
p_seq_value inout variable:
When p_action = 1 then parent seq value return back to calling procedure
When p_action = 2 then parent seq value passed in
*/
BEGIN
IF p_seq_position = 1 THEN
IF p_action = 1 THEN
p_seq_value := QLTTRAWB.g_seq_tab1(p_cur_record_indicator);
END find_update_seq_in_collection;
g_true_seq_gen_recids.delete;
ParentChild_Tab.delete;
PROCEDURE update_record(p_total_rec_count NUMBER) IS
l_childUpdate_retval varchar2(10);
UPDATE qa_results
SET sequence1 = QLTTRAWB.g_seq_tab1(g_true_seq_gen_recids(k)),
sequence2 = QLTTRAWB.g_seq_tab2(g_true_seq_gen_recids(k)),
sequence3 = QLTTRAWB.g_seq_tab3(g_true_seq_gen_recids(k)),
sequence4 = QLTTRAWB.g_seq_tab4(g_true_seq_gen_recids(k)),
sequence5 = QLTTRAWB.g_seq_tab5(g_true_seq_gen_recids(k)),
sequence6 = QLTTRAWB.g_seq_tab6(g_true_seq_gen_recids(k)),
sequence7 = QLTTRAWB.g_seq_tab7(g_true_seq_gen_recids(k)),
sequence8 = QLTTRAWB.g_seq_tab8(g_true_seq_gen_recids(k)),
sequence9 = QLTTRAWB.g_seq_tab9(g_true_seq_gen_recids(k)),
sequence10 = QLTTRAWB.g_seq_tab10(g_true_seq_gen_recids(k)),
sequence11 = QLTTRAWB.g_seq_tab11(g_true_seq_gen_recids(k)),
sequence12 = QLTTRAWB.g_seq_tab12(g_true_seq_gen_recids(k)),
sequence13 = QLTTRAWB.g_seq_tab13(g_true_seq_gen_recids(k)),
sequence14 = QLTTRAWB.g_seq_tab14(g_true_seq_gen_recids(k)),
sequence15 = QLTTRAWB.g_seq_tab15(g_true_seq_gen_recids(k))
WHERE plan_id = QLTTRAWB.g_plan_id_tab(g_true_seq_gen_recids(k)) AND
collection_id = QLTTRAWB.g_collection_id_tab(g_true_seq_gen_recids(k)) AND
occurrence = QLTTRAWB.g_occurrence_tab(g_true_seq_gen_recids(k));
l_childUpdate_retval := QA_PARENT_CHILD_PKG.update_sequence_child
(p_ParentChild_Tab => ParentChild_Tab);
END update_record;
SELECT 1 FROM qa_seq_audit_history
WHERE txn_header_id = p_txn_header_id
and (p_plan_id IS NULL OR plan_id = p_plan_id)
and (p_occurrence IS NULL OR occurrence = p_occurrence);
SELECT 1 FROM qa_seq_audit_history
WHERE collection_id = p_collection_id
and (p_plan_id IS NULL OR plan_id = p_plan_id)
and (p_occurrence IS NULL OR occurrence = p_occurrence);
SELECT occurrence,collection_id,plan_id,txn_header_id,
sequence1,sequence2,sequence3,
sequence4,sequence5,sequence6,
sequence7,sequence8,sequence9,
sequence10,sequence11,sequence12,
sequence13,sequence14,sequence15
INTO
QLTTRAWB.g_occurrence_tab(l_count), QLTTRAWB.g_collection_id_tab(l_count),
QLTTRAWB.g_plan_id_tab(l_count), QLTTRAWB.g_txn_header_id_tab(l_count),
QLTTRAWB.g_seq_tab1(l_count), QLTTRAWB.g_seq_tab2(l_count), QLTTRAWB.g_seq_tab3(l_count),
QLTTRAWB.g_seq_tab4(l_count), QLTTRAWB.g_seq_tab5(l_count), QLTTRAWB.g_seq_tab6(l_count),
QLTTRAWB.g_seq_tab7(l_count), QLTTRAWB.g_seq_tab8(l_count), QLTTRAWB.g_seq_tab9(l_count),
QLTTRAWB.g_seq_tab10(l_count), QLTTRAWB.g_seq_tab11(l_count), QLTTRAWB.g_seq_tab12(l_count),
QLTTRAWB.g_seq_tab13(l_count), QLTTRAWB.g_seq_tab14(l_count), QLTTRAWB.g_seq_tab15(l_count)
FROM qa_results
WHERE plan_id = p_plan_ids(i) AND
collection_id = p_collection_ids(i) AND
occurrence = p_occurrences(i);
SELECT child_plan_id, child_collection_id, child_occurrence,
parent_plan_id, parent_collection_id, parent_occurrence,
level
FROM qa_pc_results_relationship r
WHERE EXISTS (
SELECT 1
FROM qa_results qr
WHERE qr.plan_id = r.child_plan_id AND
qr.collection_id = r.child_collection_id AND
qr.occurrence = r.child_occurrence AND
(qr.status IS NULL or qr.status=2) )
START WITH r.parent_plan_id = p_plan_id AND
r.parent_collection_id = p_collection_id AND
r.parent_occurrence = p_occurrence
CONNECT BY PRIOR r.child_occurrence = r.parent_occurrence
ORDER BY level;
find_update_seq_in_collection(l_cur_rec_indicator,
i,
1,
l_seq_value);
p_audit_type => 'DELETED',
p_audit_date => l_date,
p_last_update_date => l_date,
p_last_updated_by => l_user_id,
p_creation_date => l_date,
p_created_by => l_user_id,
p_last_update_login => l_login_id);
p_last_update_date => l_date,
p_last_updated_by => l_user_id,
p_creation_date => l_date,
p_created_by => l_user_id,
p_last_update_login => l_login_id);
p_last_update_date DATE,
p_last_updated_by NUMBER,
p_creation_date DATE,
p_created_by NUMBER,
p_last_update_login NUMBER) IS
l_rowid VARCHAR2(18) := NULL;
QA_SEQ_AUDIT_PKG.insert_row(
P_Rowid => l_rowid,
P_Plan_Id => p_plan_id,
P_Collection_Id => p_collection_id,
P_Occurrence => p_occurrence,
P_Char_Id => p_char_id,
P_Txn_Header_Id => p_txn_header_id,
P_Sequence_Value => p_sequence_value,
P_User_Id => p_user_id,
P_Source_Code => p_source_code,
P_Source_Id => p_source_id,
P_Audit_Type => p_audit_type,
P_Audit_Date => p_audit_date,
P_Last_Update_Date => p_last_update_date,
P_Last_Updated_By => p_last_updated_by,
P_Creation_Date => p_creation_date,
P_Created_By => p_created_by,
P_Last_Update_Login => p_last_update_login);
PROCEDURE delete_auditinfo_for_Txn(p_collection_id NUMBER) IS
-- This procedure is called by eRecords in TXN Mode.
-- For a txn if eRecords are enabled, sequence are generated before
-- eRecord information shown to the user. At sequence generation, we
-- are capturing audit info. for each sequence value generated.
-- If eRecord is accepted then we need to delete the audit information
-- that got captured at generation. If eRecords rejected by user then
-- leave the audit info. as it was.
BEGIN
DELETE FROM qa_seq_audit_history
WHERE collection_id = p_collection_id;
END delete_auditinfo_for_Txn;
PROCEDURE delete_auditinfo_for_DDE(p_txn_header_id NUMBER) IS
-- This procedure is called by eRecords in DDE scanario and is
-- similar to delete_audit_for_Txn(see this proc. for details).
BEGIN
DELETE FROM qa_seq_audit_history
WHERE txn_header_id = p_txn_header_id;
END delete_auditinfo_for_DDE;
g_message_array.DELETE;
SELECT qpc.prompt,qpc.char_id
FROM qa_plan_chars qpc,
qa_chars qc
WHERE qpc.plan_id = p_plan_id
AND qpc.char_id = qc.char_id
AND qc.datatype = 5;
g_prompt_tab.DELETE;
SELECT DISTINCT qr.plan_id
FROM qa_results qr
WHERE qr.collection_id = p_collection_id
AND NOT EXISTS
(SELECT 1
FROM qa_pc_results_relationship qprr
WHERE qprr.child_plan_id = qr.plan_id
AND qprr.child_collection_id = qr.collection_id
AND qprr.child_occurrence = qr.occurrence);
g_message_array.DELETE;
update_record(l_row_count);
'Called update_record with row count ' || l_row_count);
p_last_update_date => l_date,
p_last_updated_by => l_user_id,
p_creation_date => l_date,
p_created_by => l_user_id,
p_last_update_login => l_login_id);
g_message_array.DELETE;
seq_tab.delete;