The following lines contain the word 'select', 'insert', 'update' or 'delete':
FUNCTION Update_User_Name
(
p_subscription_guid IN RAW,
p_event IN OUT NOCOPY WF_EVENT_T
) RETURN VARCHAR2
IS
l_api_name CONSTANT VARCHAR2(30) := 'Update_User_Name';
'Before Invoking Update_Element_Value with p_old_value => ' || l_old_user_name || ' and p_new_value => ' || l_new_user_name
);
Update_Element_Value
(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_TRUE,
p_commit => FND_API.G_TRUE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
p_char_id => QA_SS_CONST.send_notification_to,
p_old_value => l_old_user_name,
p_new_value => l_new_user_name,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
'After Invoking Update_Element_Value. Return Status is: ' || l_return_status
);
'User Name Update Failed with Error: ' || l_error_string
);
'User Name Update Failed with Error: ' || l_error_string
);
'User Name Update Failed with Error: ' || SQLERRM
);
'User Name Update Failed with Error: ' || SQLERRM
);
END Update_User_Name;
PROCEDURE update_results
(
p_char_id IN NUMBER,
p_old_value IN VARCHAR2,
p_new_value IN VARCHAR2,
p_commit IN BOOLEAN
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'update_results';
SELECT qpc.plan_id,
qpc.result_column_name
BULK COLLECT INTO
l_plan_ids,
l_result_columns
FROM qa_chars qc,
qa_plan_chars qpc,
qa_plans qp
WHERE qc.hardcoded_column IS NULL
AND qc.char_id = qpc.char_id
AND qpc.char_id = p_char_id
AND qpc.plan_id = qp.plan_id
AND qp.organization_id <> 0
AND NOT EXISTS
(
SELECT 1
FROM qa_pc_plan_relationship
WHERE data_entry_mode = 4
AND child_plan_id = qp.plan_id
);
l_dml_string := 'UPDATE qa_results qr SET ' || l_result_columns(i) || ' = :1 WHERE plan_id = :2 AND ' || NVL( l_index_predicate, l_result_columns(i) ) || ' = :3 ';
'Updated ' || SQL%ROWCOUNT || ' Rows of QA Results with plan_id: ' || l_plan_ids(i) || ' for char_id: ' || p_char_id
);
SAVEPOINT Update_Element_Value_PKG;
END update_results;
PROCEDURE update_plan_chars
(
p_char_id IN NUMBER,
p_old_value IN VARCHAR2,
p_new_value IN VARCHAR2
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'update_plan_chars';
UPDATE QA_PLAN_CHARS
SET default_value = p_new_value
WHERE char_id = p_char_id
AND default_value = p_old_value;
'Updated ' || SQL%ROWCOUNT || ' Rows for QA_PLAN_CHARS.default_value for char_id: ' || p_char_id
);
END update_plan_chars;
PROCEDURE update_chars
(
p_char_id IN NUMBER,
p_old_value IN VARCHAR2,
p_new_value IN VARCHAR2
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'update_chars';
UPDATE QA_CHARS
SET default_value = p_new_value
WHERE char_id = p_char_id
AND default_value = p_old_value;
'Updated ' || SQL%ROWCOUNT || ' Rows for QA_CHARS.default_value for char_id: ' || p_char_id
);
END update_chars;
PROCEDURE update_char_action_trig
(
p_char_id IN NUMBER,
p_old_value IN VARCHAR2,
p_new_value IN VARCHAR2
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'update_char_action_trig';
UPDATE QA_CHAR_ACTION_TRIGGERS
SET low_value_other = p_new_value
WHERE char_id = p_char_id
AND low_value_other = p_old_value;
'Updated ' || SQL%ROWCOUNT || ' Rows for QA_CHAR_ACTION_TRIGGERS.low_value_other for char_id: ' || p_char_id
);
UPDATE QA_CHAR_ACTION_TRIGGERS
SET high_value_other = p_new_value
WHERE char_id = p_char_id
AND high_value_other = p_old_value;
'Updated ' || SQL%ROWCOUNT || ' Rows for QA_CHAR_ACTION_TRIGGERS.high_value_other for char_id: ' || p_char_id
);
END update_char_action_trig;
PROCEDURE update_plan_char_action_trig
(
p_char_id IN NUMBER,
p_old_value IN VARCHAR2,
p_new_value IN VARCHAR2
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'update_plan_char_action_trig';
UPDATE QA_PLAN_CHAR_ACTION_TRIGGERS
SET low_value_other = p_new_value
WHERE char_id = p_char_id
AND low_value_other = p_old_value
AND plan_id IN
(
SELECT DISTINCT plan_id
FROM QA_PLAN_CHARS
WHERE char_id = p_char_id
);
'Updated ' || SQL%ROWCOUNT || ' Rows for QA_PLAN_CHAR_ACTION_TRIGGERS.low_value_other for char_id: ' || p_char_id
);
UPDATE QA_PLAN_CHAR_ACTION_TRIGGERS
SET high_value_other = p_new_value
WHERE char_id = p_char_id
AND high_value_other = p_old_value
AND plan_id IN
(
SELECT DISTINCT plan_id
FROM QA_PLAN_CHARS
WHERE char_id = p_char_id
);
'Updated ' || SQL%ROWCOUNT || ' Rows for QA_PLAN_CHAR_ACTION_TRIGGERS.high_value_other for char_id: ' || p_char_id
);
END update_plan_char_action_trig;
PROCEDURE update_plan_coll_trig
(
p_char_id IN NUMBER,
p_old_value IN VARCHAR2,
p_new_value IN VARCHAR2
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'update_plan_coll_trig';
UPDATE QA_PLAN_COLLECTION_TRIGGERS
SET low_value = p_new_value
WHERE collection_trigger_id = p_char_id
AND low_value = p_old_value
AND plan_transaction_id IN
(
SELECT qpt.plan_transaction_id
FROM QA_PLAN_TRANSACTIONS qpt,
QA_PLAN_CHARS qpc
WHERE qpt.plan_id = qpc.plan_id
AND qpc.char_id = p_char_id
);
'Updated ' || SQL%ROWCOUNT || ' Rows for QA_PLAN_COLLECTION_TRIGGERS.low_value for char_id: ' || p_char_id
);
UPDATE QA_PLAN_COLLECTION_TRIGGERS
SET high_value = p_new_value
WHERE collection_trigger_id = p_char_id
AND high_value = p_old_value
AND plan_transaction_id IN
(
SELECT qpt.plan_transaction_id
FROM QA_PLAN_TRANSACTIONS qpt,
QA_PLAN_CHARS qpc
WHERE qpt.plan_id = qpc.plan_id
AND qpc.char_id = p_char_id
);
'Updated ' || SQL%ROWCOUNT || ' Rows for QA_PLAN_COLLECTION_TRIGGERS.high_value for char_id: ' || p_char_id
);
END update_plan_coll_trig;
PROCEDURE update_criteria
(
p_char_id IN NUMBER,
p_old_value IN VARCHAR2,
p_new_value IN VARCHAR2
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'update_criteria';
UPDATE QA_CRITERIA
SET low_value = p_new_value
WHERE char_id = p_char_id
AND low_value = p_old_value
AND criteria_id IN
(
SELECT qch.criteria_id
FROM QA_CRITERIA_HEADERS qch,
QA_PLANS qp,
QA_PLAN_CHARS qpc
WHERE qch.organization_id = qp.organization_id
AND qp.plan_id = qpc.plan_id
AND qpc.char_id = p_char_id
);
'Updated ' || SQL%ROWCOUNT || ' Rows for QA_CRITERIA.low_value for char_id: ' || p_char_id
);
UPDATE QA_CRITERIA
SET high_value = p_new_value
WHERE char_id = p_char_id
AND high_value = p_old_value
AND criteria_id IN
(
SELECT qch.criteria_id
FROM QA_CRITERIA_HEADERS qch,
QA_PLANS qp,
QA_PLAN_CHARS qpc
WHERE qch.organization_id = qp.organization_id
AND qp.plan_id = qpc.plan_id
AND qpc.char_id = p_char_id
);
'Updated ' || SQL%ROWCOUNT || ' Rows for QA_CRITERIA.high_value for char_id: ' || p_char_id
);
END update_criteria;
PROCEDURE update_pc_criteria
(
p_char_id IN NUMBER,
p_old_value IN VARCHAR2,
p_new_value IN VARCHAR2
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'update_pc_criteria';
UPDATE QA_PC_CRITERIA
SET low_value = p_new_value
WHERE char_id = p_char_id
AND low_value = p_old_value
AND plan_relationship_id IN
(
SELECT qppr.plan_relationship_id
FROM QA_PC_PLAN_RELATIONSHIP qppr,
QA_PLAN_CHARS qpc
WHERE qppr.parent_plan_id = qpc.plan_id
AND qpc.char_id = p_char_id
);
'Updated ' || SQL%ROWCOUNT || ' Rows for QA_PC_CRITERIA.low_value for char_id: ' || p_char_id
);
UPDATE QA_PC_CRITERIA
SET high_value = p_new_value
WHERE char_id = p_char_id
AND high_value = p_old_value
AND plan_relationship_id IN
(
SELECT qppr.plan_relationship_id
FROM QA_PC_PLAN_RELATIONSHIP qppr,
QA_PLAN_CHARS qpc
WHERE qppr.parent_plan_id = qpc.plan_id
AND qpc.char_id = p_char_id
);
'Updated ' || SQL%ROWCOUNT || ' Rows for QA_PC_CRITERIA.high_value for char_id: ' || p_char_id
);
END update_pc_criteria;
PROCEDURE update_specs
(
p_char_id IN NUMBER,
p_old_value IN VARCHAR2,
p_new_value IN VARCHAR2
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'update_specs';
UPDATE QA_SPECS
SET spec_element_value = p_new_value
WHERE char_id = p_char_id
AND spec_element_value = p_old_value;
'Updated ' || SQL%ROWCOUNT || ' Rows for QA_SPECS.spec_element_value for char_id: ' || p_char_id
);
END update_specs;
PROCEDURE Update_Element_Value
(
p_api_version IN NUMBER := NULL,
p_init_msg_list IN VARCHAR2 := NULL,
p_commit IN VARCHAR2 := NULL,
p_validation_level IN NUMBER := NULL,
p_char_id IN NUMBER,
p_old_value IN VARCHAR2,
p_new_value IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'Update_Element_Value';
SELECT 'Y'
FROM QA_PLAN_CHARS qpc,
QA_PLANS qp
WHERE qpc.char_id = p_char_id
AND qpc.plan_id = qp.plan_id
AND qp.organization_id <> 0;
SAVEPOINT Update_Element_Value_PKG;
'Before Calling update_chars'
);
update_chars
(
p_char_id => p_char_id,
p_old_value => p_old_value,
p_new_value => p_new_value
);
'Before Calling update_char_action_trig'
);
update_char_action_trig
(
p_char_id => p_char_id,
p_old_value => p_old_value,
p_new_value => p_new_value
);
'Before Calling update_plan_chars'
);
update_plan_chars
(
p_char_id => p_char_id,
p_old_value => p_old_value,
p_new_value => p_new_value
);
'Before Calling update_plan_char_action_trig'
);
update_plan_char_action_trig
(
p_char_id => p_char_id,
p_old_value => p_old_value,
p_new_value => p_new_value
);
'Before Calling update_plan_coll_trig'
);
update_plan_coll_trig
(
p_char_id => p_char_id,
p_old_value => p_old_value,
p_new_value => p_new_value
);
'Before Calling update_criteria'
);
'Before Calling update_pc_criteria'
);
update_pc_criteria
(
p_char_id => p_char_id,
p_old_value => p_old_value,
p_new_value => p_new_value
);
'Before Calling update_specs'
);
update_specs
(
p_char_id => p_char_id,
p_old_value => p_old_value,
p_new_value => p_new_value
);
update_criteria
(
p_char_id => p_char_id,
p_old_value => p_old_value,
p_new_value => p_new_value
);
SAVEPOINT Update_Element_Value_PKG;
'Before Calling update_results'
);
update_results
(
p_char_id => p_char_id,
p_old_value => p_old_value,
p_new_value => p_new_value,
p_commit => l_commit
);
ROLLBACK TO Update_Element_Value_PKG;
ROLLBACK TO Update_Element_Value_PKG;
ROLLBACK TO Update_Element_Value_PKG;
END Update_Element_Value;