[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
UPDATE qa_results
SET status=2
WHERE collection_id = p_collection_id;
FUNCTION insert_row( p_plan_id IN NUMBER,
p_spec_id IN NUMBER DEFAULT NULL,
p_org_id IN NUMBER,
p_transaction_number IN NUMBER DEFAULT NULL,
p_transaction_id IN NUMBER DEFAULT 0,
p_collection_id IN OUT NOCOPY NUMBER,
p_who_last_updated_by IN NUMBER := fnd_global.user_id,
p_who_created_by IN NUMBER := fnd_global.user_id,
p_who_last_update_login IN NUMBER := fnd_global.user_id,
p_enabled_flag IN NUMBER,
p_commit_flag IN BOOLEAN DEFAULT FALSE,
p_error_found OUT NOCOPY BOOLEAN,
p_occurrence IN OUT NOCOPY NUMBER,
p_do_action_return OUT NOCOPY BOOLEAN,
p_message_array OUT NOCOPY qa_validation_api.MessageArray,
p_row_elements IN OUT NOCOPY qa_validation_api.ElementsArray,
p_txn_header_id IN NUMBER DEFAULT NULL,
p_ssqr_operation IN NUMBER DEFAULT NULL,
p_last_update_date IN DATE DEFAULT SYSDATE)
RETURN qa_validation_api.ErrorArray IS
return_results_array qa_validation_api.ResultRecordArray;
insert_string varchar2(6000) := null;
insert_dbms_sql_feedback INTEGER;
SELECT QA_COLLECTION_ID_S.NEXTVAL INTO x_collection_id FROM DUAL;
SELECT QA_OCCURRENCE_S.NEXTVAL INTO x_occurrence FROM DUAL;
SELECT sysdate,
MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL
INTO x_sysdate,
x_txn_header_id
FROM DUAL;
SELECT sysdate INTO x_sysdate FROM DUAL;
insert_string := 'INSERT INTO qa_results (
collection_id,
occurrence,
last_update_date,
qa_last_update_date,
last_updated_by,
qa_last_updated_by,
creation_date,
qa_creation_date,
created_by,
last_update_login,
qa_created_by,
status,
transaction_number,
organization_id,
plan_id,
spec_id,
transaction_id,
txn_header_id ';
''''||p_who_last_updated_by||''''|| ', '||
''''||p_who_last_updated_by||''''||', '||
':current_date'||', '||
':current_date'||', '||
''''||p_who_created_by||''''||', '||
''''|| to_char(p_who_last_update_login)||''''||', '||
''''||p_who_created_by||''''||', '||
p_enabled_flag || ', '||
nvl(to_char(p_transaction_number),'NULL')||', '||
to_char(p_org_id)||', '||
to_char(p_plan_id)||', '||
nvl(to_char(p_spec_id),'NULL')||', '||
nvl(to_char(p_transaction_id),'NULL')||', '||
x_txn_header_id;*/
||':current_date, :p_who_last_updated_by, '
||':p_who_last_updated_by, :current_date, :current_date, '
||':p_who_created_by, :p_who_last_update_login, '
||':p_who_created_by, :p_enabled_flag, :p_transaction_number, '
||':p_org_id, :p_plan_id, :p_spec_id, '
||':p_transaction_id, :x_txn_header_id';
insert_string := insert_string||','|| column_name;
sql_string := insert_string||') '||value_string||') ';
DBMS_SQL.BIND_VARIABLE(c, ':p_who_last_updated_by', p_who_last_updated_by);
DBMS_SQL.BIND_VARIABLE(c, ':p_who_last_update_login', p_who_last_update_login);
DBMS_SQL.BIND_VARIABLE(c, ':current_date', p_last_update_date);
insert_dbms_sql_feedback := DBMS_SQL.EXECUTE(c);
END insert_row;
FUNCTION update_row( p_plan_id IN NUMBER,
p_spec_id IN NUMBER,
p_org_id IN NUMBER,
p_transaction_number IN NUMBER DEFAULT NULL,
p_transaction_id IN NUMBER DEFAULT NULL,
p_collection_id IN NUMBER,
p_who_last_updated_by IN NUMBER := fnd_global.user_id,
p_who_created_by IN NUMBER := fnd_global.user_id,
p_who_last_update_login IN NUMBER := fnd_global.user_id,
p_enabled_flag IN NUMBER,
p_commit_flag IN BOOLEAN DEFAULT FALSE,
p_error_found OUT NOCOPY BOOLEAN,
p_occurrence IN NUMBER,
p_do_action_return OUT NOCOPY BOOLEAN,
p_message_array OUT NOCOPY qa_validation_api.MessageArray,
p_row_elements IN OUT NOCOPY qa_validation_api.ElementsArray,
p_txn_header_id IN NUMBER DEFAULT NULL,
p_ssqr_operation IN NUMBER DEFAULT NULL,
p_last_update_date IN DATE DEFAULT SYSDATE)
RETURN qa_validation_api.ErrorArray IS
return_results_array qa_validation_api.ResultRecordArray;
update_string varchar2(25) := null;
insert_qruh varchar2(1000) := null;
update_column_value varchar2(2000);
x_update_id NUMBER;
update_dbms_sql_feedback INTEGER;
x_sysdate := p_last_update_date;
SELECT MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL
INTO x_txn_header_id
FROM DUAL;
update_string := 'UPDATE QA_RESULTS ';
value_string := 'SET qa_last_update_date = '|| ':current_date'||', '||
' last_update_date = '|| ':current_date'||', '||
' last_updated_by = '|| ':updated_by'||', '||
' qa_last_updated_by = '|| ':updated_by'||', '||
' last_update_login = '|| ':update_login'||', '||
' txn_header_id = '|| ':txn_hdr_id';
insert_qruh := ' INSERT INTO qa_results_update_history (creation_date,
last_update_date,
created_by,
last_update_login,
last_updated_by,
occurrence,
update_id,
old_value,
char_id) ';
value_qruh := '(SELECT '||':creation_date'||','||
':last_upd_date'||','||
':created_by'|| ' ,'||
':update_login'|| ' ,'||
':updated_by'|| ' ,'||
':p_occurrence'||' ,';
SELECT QA_RESULTS_UPDATE_HISTORY_S.NEXTVAL INTO x_update_id FROM DUAL;
sql_qruh := insert_qruh|| value_qruh||':x_update_id'||', '||column_name||','||
':element_id'||' FROM QA_RESULTS '||where_clause ||' )';
p_who_last_updated_by,
p_who_last_updated_by,
p_who_last_updated_by,
p_occurrence,
x_update_id,
return_results_array(i).element_id,
x_collection_id,
p_occurrence,
p_plan_id;
sql_string := update_string||' '||value_string||' '||where_clause;
update_column_value := to_char(return_results_array(j).id);
update_column_value := return_results_array(j).canonical_value;
DBMS_SQL.BIND_VARIABLE(c1, ':X'||to_char(k), update_column_value);
DBMS_SQL.BIND_VARIABLE(c1, ':updated_by', p_who_last_updated_by);
DBMS_SQL.BIND_VARIABLE(c1, ':update_login', p_who_last_updated_by);
update_dbms_sql_feedback := DBMS_SQL.EXECUTE(c1);
END update_row;
UPDATE qa_results
SET status=2
WHERE collection_id = p_collection_id;