The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT meaning
INTO l_trx_name
FROM cn_lookups
WHERE lookup_type = 'TRX TYPES' AND lookup_code = p_trx_factor.trx_type;
IF p_action = 'UPDATE'
THEN
IF p_trx_factor.trx_type <> p_old_trx_factor.trx_type
THEN
IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
THEN
fnd_message.set_name ('CN', 'CN_FIELD_NOT_UPDATABLE');
SELECT COUNT (*)
INTO l_temp_count
FROM cn_trx_factors
WHERE revenue_class_id = p_trx_factor.revenue_class_id
AND quota_id = p_trx_factor.quota_id
AND quota_rule_id = p_trx_factor.quota_rule_id
AND trx_type = p_trx_factor.trx_type
AND trx_factor_id = p_trx_factor.trx_factor_id;
SELECT COUNT (1)
INTO l_temp_count
FROM cn_trx_factors
WHERE quota_rule_id = p_trx_factor.quota_rule_id
AND trx_type = p_trx_factor.trx_type
AND trx_factor_id <> p_trx_factor.trx_factor_id
AND ROWNUM = 1;
PROCEDURE update_trx_factor (
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
p_commit IN VARCHAR2 := fnd_api.g_false,
p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
p_trx_factor IN OUT NOCOPY trx_factor_rec_type,
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_Trx_Factor';
SELECT *
FROM cn_trx_factors
WHERE trx_factor_id = factor_id;
g_last_update_date DATE := SYSDATE;
g_last_updated_by NUMBER := fnd_global.user_id;
g_last_update_login NUMBER := fnd_global.login_id;
SAVEPOINT update_trx_factor;
p_action => 'UPDATE',
p_trx_factor => p_trx_factor,
p_old_trx_factor => l_old_rec,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
cn_trx_factors_pkg.begin_record (x_operation => 'UPDATE',
x_rowid => l_row_id,
x_trx_factor_id => p_trx_factor.trx_factor_id,
x_object_version_number => p_trx_factor.object_version_number,
x_event_factor => p_trx_factor.event_factor,
x_event_factor_old => l_old_trx_factor.event_factor,
x_revenue_class_id => p_trx_factor.revenue_class_id,
x_last_update_date => g_last_update_date,
x_last_updated_by => g_last_updated_by,
x_creation_date => l_old_trx_factor.creation_date,
x_created_by => l_old_trx_factor.created_by,
x_last_update_login => g_last_update_login,
x_quota_id => p_trx_factor.quota_id,
x_quota_rule_id => p_trx_factor.quota_rule_id,
x_trx_type => p_trx_factor.trx_type,
x_trx_type_name => NULL,
x_program_type => NULL,
x_status_code => NULL,
x_org_id => NULL
);
select NAME into l_rev_class_name from cn_revenue_classes
where REVENUE_CLASS_ID = p_trx_factor.revenue_class_id
and org_id = p_trx_factor.org_id;
ROLLBACK TO update_trx_factor;
ROLLBACK TO update_trx_factor;
ROLLBACK TO update_trx_factor;
END update_trx_factor;
PROCEDURE update_trx_factors (
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
p_commit IN VARCHAR2 := fnd_api.g_false,
p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
p_org_id IN NUMBER,
p_quota_name IN VARCHAR2,
p_revenue_class_name IN VARCHAR2 := NULL,
p_trx_factor_rec_tbl IN cn_plan_element_pub.trx_factor_rec_tbl_type,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_loading_status OUT NOCOPY VARCHAR2
)
IS
l_api_name CONSTANT VARCHAR2 (30) := 'Update_Trx_Factors';
SAVEPOINT update_trx_factors;
SELECT rc.NAME AS revenue_class_name
BULK COLLECT INTO l_rev_class_names_array
FROM cn_quota_rules qr,
cn_revenue_classes rc
WHERE qr.quota_id = l_quota_id AND qr.revenue_class_id = rc.revenue_class_id;
SELECT trx_factor_id,
object_version_number
INTO l_trx_factor_rec.trx_factor_id,
l_trx_factor_rec.object_version_number
FROM cn_trx_factors
WHERE quota_rule_id = l_trx_factor_rec.quota_rule_id
AND trx_type = l_trx_factor_rec.trx_type
AND quota_id = l_trx_factor_rec.quota_id
AND revenue_class_id = l_trx_factor_rec.revenue_class_id;
update_trx_factor (p_api_version => l_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit,
p_validation_level => p_validation_level,
p_trx_factor => l_trx_factor_rec,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
ROLLBACK TO update_trx_factors;
ROLLBACK TO update_trx_factors;
ROLLBACK TO update_trx_factors;
END update_trx_factors;
PROCEDURE delete_trx_factor (
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
p_commit IN VARCHAR2 := fnd_api.g_false,
p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
p_trx_factor IN OUT NOCOPY trx_factor_rec_type,
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) := 'Delete_Trx_Factor';
SAVEPOINT delete_trx_factor;
ROLLBACK TO delete_trx_factor;
ROLLBACK TO delete_trx_factor;
ROLLBACK TO delete_trx_factor;
END delete_trx_factor;
SELECT *
FROM cn_trx_factors
WHERE quota_rule_id = p_quota_rule_id;