The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE update_validation_flg(elements IN OUT NOCOPY qa_validation_api.ElementsArray,
p_plan_id IN NUMBER,
p_collection_id IN NUMBER,
p_occurrence IN NUMBER) AS
char_id INTEGER;
elements.delete(char_id);
l_sql_string := 'SELECT ' || l_sql_string || ' FROM QA_RESULTS_FULL_V WHERE plan_id = :2 and collection_id = :3 and occurrence = :4';
END update_validation_flg;
SELECT message
FROM qa_plan_char_actions
WHERE plan_char_action_trigger_id IN
(SELECT plan_char_action_trigger_id
FROM qa_plan_char_action_triggers
WHERE plan_id = p_plan_id
AND char_id = p_char_id)
AND action_id = 2;
qa_results_pub.insert_row(
p_api_version => 1.0,
p_init_msg_list => fnd_api.g_true,
p_org_id => x_org_id,
p_plan_id => x_plan_id,
p_spec_id => y_spec_id,
p_transaction_number => null,
p_transaction_id => null,
p_enabled_flag => x_enabled,
p_commit => y_committed,
x_collection_id => y_collection_id,
x_occurrence => x_occurrence,
x_row_elements => elements,
x_msg_count => msg_count,
x_msg_data => msg_data,
x_error_array => error_array,
x_message_array => message_array,
x_return_status => return_status,
x_action_result => action_result);
SELECT qc.char_id
BULK COLLECT INTO seq_charid_tab
FROM qa_chars qc, qa_plan_chars qpc
WHERE qpc.plan_id = x_plan_id
AND qc.char_id = qpc.char_id
AND qc.datatype = 5;
qa_results_pub.insert_row(
p_api_version => 1.0,
p_init_msg_list => fnd_api.g_true,
p_org_id => x_org_id,
p_plan_id => x_plan_id,
p_spec_id => y_spec_id,
p_transaction_number => x_transaction_number,
p_transaction_id => null,
p_enabled_flag => x_enabled,
p_commit => y_committed,
x_collection_id => y_collection_id,
x_occurrence => x_occurrence,
x_row_elements => elements,
x_msg_count => msg_count,
x_msg_data => msg_data,
x_error_array => error_array,
x_message_array => message_array,
x_return_status => return_status,
x_action_result => action_result);
p_last_update_date IN DATE DEFAULT SYSDATE)
RETURN INTEGER IS
elements qa_validation_api.ElementsArray;
l_esig_status := validate_esig_for_insert(p_plan_id => x_par_plan_id,
p_plan_collection_id => x_par_col_id,
p_plan_occurrence => x_par_occ );
qa_results_pub.insert_row(
p_api_version => 1.0,
p_init_msg_list => fnd_api.g_true,
p_org_id => x_org_id,
p_plan_id => x_plan_id,
p_spec_id => y_spec_id,
p_transaction_number => x_transaction_number,
p_transaction_id => null,
p_txn_header_id => x_txn_header_id,
p_enabled_flag => x_enabled,
p_commit => y_committed,
x_collection_id => y_collection_id,
x_occurrence => x_occurrence,
x_row_elements => elements,
x_msg_count => msg_count,
x_msg_data => msg_data,
x_error_array => error_array,
x_message_array => message_array,
x_return_status => return_status,
x_action_result => action_result,
p_ssqr_operation => l_ssqr_operation,
p_last_update_date => p_last_update_date);
l_dummy := QA_PARENT_CHILD_PKG.update_hist_children(
p_parent_plan_id => x_par_plan_id,
p_parent_collection_id => x_par_col_id,
p_parent_occurrence => x_par_occ);
qa_parent_child_pkg.insert_history_auto_rec_QWB(x_plan_id,
x_txn_header_id,
1,
2);
qa_parent_child_pkg.insert_history_auto_rec_QWB(x_plan_id,
x_txn_header_id,
1,
4);
qa_parent_child_pkg.insert_history_auto_rec_QWB(p_plan_id => x_plan_id,
p_collection_id => y_collection_id,
p_occurrence => x_occurrence,
p_organization_id => x_org_id,
p_txn_header_id => x_txn_header_id,
p_relationship_type => 1,
p_data_entry_mode => 2,
x_status => return_status);
qa_parent_child_pkg.insert_history_auto_rec_QWB(p_plan_id => x_plan_id,
p_collection_id => y_collection_id,
p_occurrence => x_occurrence,
p_organization_id => x_org_id,
p_txn_header_id => x_txn_header_id,
p_relationship_type => 1,
p_data_entry_mode => 4,
x_status => return_status);
l_ret_value:= QA_PARENT_CHILD_PKG.update_child(x_par_plan_id,
x_par_col_id,
x_par_occ, x_plan_id,
x_collection_id, x_occurrence);
FUNCTION update_result(
x_occurrence IN NUMBER,
x_org_id IN NUMBER,
x_plan_id IN NUMBER,
x_spec_id IN NUMBER,
x_collection_id IN NUMBER,
x_result IN VARCHAR2,
x_result1 IN VARCHAR2, -- R12 Project MOAC 4637896, ID passing
x_result2 IN VARCHAR2, -- not used yet, for future expansion
x_enabled IN INTEGER,
x_committed IN INTEGER,
x_transaction_number IN NUMBER,
x_messages OUT NOCOPY VARCHAR2)
RETURN INTEGER IS
elements qa_validation_api.ElementsArray;
qa_results_pub.update_row(
p_api_version => 1.0,
p_init_msg_list => fnd_api.g_true,
p_commit => y_committed,
p_plan_id => x_plan_id,
p_spec_id => y_spec_id,
p_org_id => x_org_id,
p_transaction_number => x_transaction_number,
p_transaction_id => null,
p_enabled_flag => x_enabled,
p_collection_id => y_collection_id,
p_occurrence => x_occurrence,
x_row_elements => elements,
x_msg_count => msg_count,
x_msg_data => msg_data,
x_error_array => error_array,
x_message_array => message_array,
x_return_status => return_status,
x_action_result => action_result);
END update_result;
SELECT message
FROM qa_plan_char_actions
WHERE plan_char_action_trigger_id IN
(SELECT plan_char_action_trigger_id
FROM qa_plan_char_action_triggers
WHERE plan_id = p_plan_id
AND char_id = p_char_id)
AND action_id = 2;
FUNCTION ssqr_update_result(
x_occurrence IN NUMBER,
x_org_id IN NUMBER,
x_plan_id IN NUMBER,
x_spec_id IN NUMBER,
x_collection_id IN NUMBER,
x_txn_header_id IN NUMBER,
x_par_plan_id IN NUMBER,
x_par_col_id IN NUMBER,
x_par_occ IN NUMBER,
x_result IN VARCHAR2,
x_result1 IN VARCHAR2, -- R12 Project MOAC 4637896, ID passing
x_result2 IN VARCHAR2, -- not used yet, for future expansion
x_enabled IN INTEGER,
x_committed IN INTEGER,
x_transaction_number IN NUMBER,
x_messages OUT NOCOPY VARCHAR2,
x_agg_elements OUT NOCOPY VARCHAR2,
x_agg_val OUT NOCOPY VARCHAR2,
p_last_update_date IN DATE DEFAULT SYSDATE)
RETURN INTEGER IS
elements qa_validation_api.ElementsArray;
validate_esig_for_update(p_plan_id => x_plan_id,
p_plan_collection_id => x_collection_id,
p_plan_occurrence => x_occurrence);
qa_results_pub.update_row(
p_api_version => 1.0,
p_init_msg_list => fnd_api.g_true,
p_commit => y_committed,
p_plan_id => x_plan_id,
p_spec_id => y_spec_id,
p_org_id => x_org_id,
p_transaction_number => x_transaction_number,
p_transaction_id => null,
p_txn_header_id => x_txn_header_id,
p_enabled_flag => x_enabled,
p_collection_id => y_collection_id,
p_occurrence => x_occurrence,
x_row_elements => elements,
x_msg_count => msg_count,
x_msg_data => msg_data,
x_error_array => error_array,
x_message_array => message_array,
x_return_status => return_status,
x_action_result => action_result,
p_ssqr_operation => 1,
p_last_update_date => p_last_update_date);
IF(QA_PARENT_CHILD_PKG.update_parent(
x_par_plan_id,
x_par_col_id,
x_par_occ,
x_plan_id,
x_collection_id,
x_occurrence,
x_txn_header_id,
x_agg_elements,
x_agg_val
) = 'T') THEN
NULL;
qa_parent_child_pkg.insert_history_auto_rec_QWB(
p_plan_id => x_par_plan_id,
p_collection_id => x_par_col_id,
p_occurrence => x_par_occ,
p_organization_id => x_org_id,
p_txn_header_id => x_txn_header_id,
p_relationship_type => 1,
p_data_entry_mode => 4,
x_status => return_status);
qa_parent_child_pkg.insert_history_auto_rec_QWB(x_plan_id,
x_txn_header_id,
1,
4);
SELECT b.child_plan_id,
b.child_collection_id,
b.child_occurrence
BULK COLLECT INTO hist_plan_tab
FROM qa_pc_results_relationship b, qa_pc_plan_relationship a
WHERE a.parent_plan_id = x_plan_id
AND a.parent_plan_id = b.parent_plan_id
AND a.child_plan_id = b.child_plan_id
AND a.data_entry_mode = 4
AND b.parent_collection_id = y_collection_id
AND b.parent_occurrence = x_occurrence
AND b.child_txn_header_id = x_txn_header_id;
qa_parent_child_pkg.insert_history_auto_rec_QWB(p_plan_id => x_plan_id,
p_collection_id => y_collection_id,
p_occurrence => x_occurrence,
p_organization_id => x_org_id,
p_txn_header_id => x_txn_header_id,
p_relationship_type => 1,
p_data_entry_mode => 4,
x_status => return_status);
l_ret_value:= QA_PARENT_CHILD_PKG.update_child(x_plan_id,
y_collection_id,
x_occurrence,
hist_plan_tab(hist_plan_cntr).plan_id,
hist_plan_tab(hist_plan_cntr).collection_id,
hist_plan_tab(hist_plan_cntr).occurrence);
l_ret_value:= QA_PARENT_CHILD_PKG.update_child(x_par_plan_id,
x_par_col_id,
x_par_occ,
x_plan_id,
x_collection_id,
x_occurrence);
l_ret_value := QA_PARENT_CHILD_PKG.update_all_children(x_plan_id,
x_collection_id, x_occurrence);
END ssqr_update_result;
p_last_update_date IN DATE,
x_status OUT NOCOPY VARCHAR2)
RETURN INTEGER IS
l_occurrence NUMBER;
SELECT occurrence, last_update_date
FROM qa_results
WHERE occurrence = p_occurrence
AND plan_id = p_plan_id
FOR UPDATE NOWAIT;
l_last_update_date DATE;
FETCH upd_cur INTO l_occurrence, l_last_update_date;
-- there in the database (l_last_update_date) then it
-- means the record has been updated by some other user.
-- So we return -1, to QualityResultsEOImpl.java which
-- would display the seeded QA_SSQR_LOCK_FAILED error
-- message.
-- ntungare Mon Apr 10 07:06:39 PDT 2006
--
IF (p_last_update_date <> l_last_update_date) THEN
RETURN -1;
Select 1 from QA_RESULTS
WHERE plan_id = p_plan_id
and collection_id = p_collection_id
and occurrence = p_occurrence;
update_txn PLS_INTEGER := 0;
Fetch C1 into update_txn;
If update_txn = 1 then
update_validation_flg(elements,
p_plan_id,
p_collection_id,
p_occurrence);
update_txn := 0;
PROCEDURE delete_result(
x_plan_id IN NUMBER,
x_collection_id IN NUMBER,
x_occurrence IN NUMBER) IS
BEGIN
DELETE FROM qa_results
WHERE plan_id = x_plan_id AND
collection_id = x_collection_id AND
occurrence = x_occurrence;
END delete_result;
PROCEDURE delete_results(
x_plan_id IN NUMBER,
x_collection_id IN NUMBER,
x_occurrences IN VARCHAR2) IS
c_key CONSTANT VARCHAR2(30) := 'QA_SS_RESULTS.DELETE_RESULTS';
DELETE FROM qa_results
WHERE plan_id = x_plan_id AND
collection_id = x_collection_id AND
occurrence IN
(SELECT id
FROM qa_performance_temp
WHERE key = c_key);
END delete_results;
SELECT QA_COLLECTION_ID_S.NEXTVAL FROM DUAL;
FUNCTION validate_esig_for_update(
p_plan_id IN NUMBER,
p_plan_collection_id IN NUMBER,
p_plan_occurrence IN NUMBER)
RETURN BOOLEAN
IS
l_eres_profile VARCHAR2(3);
fnd_message.set_name('QA', 'QA_ERES_CANNOT_UPDATE_RESULT');
fnd_message.set_name('QA', 'QA_ERES_CANNOT_UPDATE_RESULT');
END validate_esig_for_update;
FUNCTION validate_esig_for_insert(p_plan_id IN NUMBER,
p_plan_collection_id IN NUMBER,
p_plan_occurrence IN NUMBER)
RETURN BOOLEAN
IS
l_eres_profile VARCHAR2(3);
END validate_esig_for_insert;
FUNCTION delete_row(p_plan_id IN NUMBER,
p_collection_id IN NUMBER,
p_occurrence IN NUMBER,
p_org_id IN NUMBER,
p_txn_header_id IN NUMBER,
p_par_plan_id IN NUMBER DEFAULT -1,
p_par_col_id IN NUMBER DEFAULT -1,
p_par_occ IN NUMBER DEFAULT -1)
RETURN VARCHAR2 AS
delete_api_ret_val VARCHAR2(1);
insert_api_ret_val VARCHAR2(1);
SELECT 1
FROM qa_pc_result_columns_v
WHERE parent_plan_id = p_par_plan_id
AND child_plan_id = p_plan_id
AND element_relationship_type in (2,3,4,5,6,7,8)
AND parent_enabled_flag = 1
AND child_enabled_flag = 1;
delete_api_ret_val := QA_PARENT_CHILD_PKG.delete_row (
p_plan_id => p_plan_id,
p_collection_id => p_collection_id,
p_occurrence => p_occurrence);
IF (delete_api_ret_val = 'T') THEN
-- Check if a parent record exists for the current record
-- and if an aggregate relationship exists between then in which
-- case History record needs to be created for the parent
--
IF (p_par_plan_id <> -1 AND
p_par_col_id <> -1 AND
p_par_occ <> -1) THEN
OPEN check_agg_rel_cur;
qa_parent_child_pkg.insert_history_auto_rec_QWB(
p_plan_id => p_par_plan_id,
p_collection_id => p_par_col_id,
p_occurrence => p_par_occ,
p_organization_id => p_org_id,
p_txn_header_id => p_txn_header_id,
p_relationship_type => 1,
p_data_entry_mode => 4,
x_status => insert_api_ret_val);