[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_last_update_date DATE := SYSDATE;
g_last_updated_by NUMBER := fnd_global.user_id;
g_last_update_login NUMBER := fnd_global.login_id;
x_loading_status := 'CN_UPDATED';
select name into l_temp_old from cn_quotas_v where quota_id = p_quota_rule_new.quota_id;
fnd_message.set_name('CN','CNR12_NOTE_PE_PROD_UPDATE');
IF (p_operation = 'delete') THEN
fnd_message.set_name ('CN', 'CNR12_NOTE_PE_ELIGPROD_DELETE');
IF (p_operation = 'update') THEN
select NAME into l_temp_rc_old from cn_revenue_classes where
revenue_class_id = p_quota_rule_old.revenue_class_id
and org_id = p_quota_rule_old.org_id;
SELECT quota_id,
NAME,
incentive_type_code,
quota_type_code,
org_id
FROM cn_quotas
WHERE quota_id = c_quota_id;
SELECT *
FROM cn_quota_rule_uplifts
WHERE quota_rule_id = p_quota_rule.quota_rule_id;
SELECT NAME
INTO p_quota_rule.revenue_class_name
FROM cn_revenue_classes_all
WHERE revenue_class_id = p_quota_rule.revenue_class_id;
IF p_action = 'DELETE'
THEN
BEGIN
SELECT quota_id,
revenue_class_id
INTO p_quota_rule.quota_id,
p_quota_rule.revenue_class_id
FROM cn_quota_rules
WHERE quota_rule_id = p_quota_rule.quota_rule_id;
SELECT COUNT (1)
INTO l_temp_count
FROM cn_quota_rules
WHERE quota_id = p_quota_rule.quota_id AND revenue_class_id = p_quota_rule.revenue_class_id AND ROWNUM = 1;
ELSIF p_action = 'UPDATE'
THEN
-- check the object version number
IF NVL (p_quota_rule.object_version_number, -1) <> p_old_quota_rule.object_version_number
THEN
fnd_message.set_name ('CN', 'CN_RECORD_CHANGED');
SELECT COUNT (*)
INTO l_same_pe
FROM cn_quota_rules qr
WHERE qr.revenue_class_id = p_old_quota_rule.revenue_class_id
AND qr.quota_id = p_quota_rule.quota_id
AND qr.quota_rule_id = p_quota_rule.quota_rule_id;
SELECT COUNT (1)
INTO l_temp_count
FROM cn_quota_rules
WHERE quota_id = p_quota_rule.quota_id
AND revenue_class_id = p_quota_rule.revenue_class_id
AND quota_rule_id <> p_quota_rule.quota_rule_id
AND ROWNUM = 1;
END IF; -- if update end
SELECT cn_quota_rules_s.NEXTVAL
INTO p_quota_rule.quota_rule_id
FROM DUAL;
cn_quota_rules_pkg.begin_record (x_operation => 'INSERT',
x_object_version_number => p_quota_rule.object_version_number,
x_quota_rule_id => p_quota_rule.quota_rule_id,
x_quota_id => p_quota_rule.quota_id,
x_org_id => p_quota_rule.org_id,
x_revenue_class_id => p_quota_rule.revenue_class_id,
x_revenue_class_name => p_quota_rule.revenue_class_name,
x_target => p_quota_rule.target,
x_revenue_class_id_old => NULL,
x_target_old => NULL,
x_payment_amount => p_quota_rule.payment_amount,
x_performance_goal => p_quota_rule.performance_goal,
x_last_update_date => g_last_update_date,
x_last_updated_by => g_last_updated_by,
x_creation_date => g_creation_date,
x_created_by => g_created_by,
x_last_update_login => g_last_update_login,
x_program_type => g_program_type,
x_status_code => NULL,
x_payment_amount_old => NULL,
x_performance_goal_old => NULL
);
x_loading_status := 'CN_INSERTED';
x_loading_status := 'CN_UPDATE_FAILED';
cn_trx_factor_pvt.update_trx_factors (p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit,
p_validation_level => p_validation_level,
p_trx_factor_rec_tbl => p_trx_factor_rec_tbl,
p_org_id => l_revclass_rec.org_id,
p_quota_name => l_revclass_rec.plan_element_name,
p_revenue_class_name => l_revclass_rec.revenue_class_name,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_loading_status => l_loading_status
);
x_loading_status := 'CN_INSERTED'; -- Calling Place will handle this
END IF; -- CN_INSERTED.
PROCEDURE update_quota_rule (
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_quota_rule IN OUT NOCOPY quota_rule_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_Quota_Rule';
SELECT *
FROM cn_quota_rules
WHERE quota_rule_id = p_quota_rule.quota_rule_id;
SAVEPOINT update_quota_rule;
p_action => 'UPDATE',
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
cn_quota_rules_pkg.begin_record (x_operation => 'UPDATE',
x_object_version_number => p_quota_rule.object_version_number,
x_quota_rule_id => p_quota_rule.quota_rule_id,
x_quota_id => p_quota_rule.quota_id,
x_org_id => p_quota_rule.org_id,
x_revenue_class_id => p_quota_rule.revenue_class_id,
x_revenue_class_name => p_quota_rule.revenue_class_name,
x_target => p_quota_rule.target,
x_payment_amount => p_quota_rule.payment_amount,
x_performance_goal => p_quota_rule.performance_goal,
x_revenue_class_id_old => l_old_rec.revenue_class_id,
x_target_old => l_old_rec.target,
x_last_update_date => g_last_update_date,
x_last_updated_by => g_last_updated_by,
x_creation_date => l_old_quota_rule.creation_date,
x_created_by => l_old_quota_rule.created_by,
x_last_update_login => g_last_update_login,
x_program_type => g_program_type,
x_status_code => NULL,
x_payment_amount_old => NULL,
x_performance_goal_old => NULL
);
'update',
x_return_status,
x_msg_count,
x_msg_data
);
ROLLBACK TO update_quota_rule;
ROLLBACK TO update_quota_rule;
ROLLBACK TO update_quota_rule;
END update_quota_rule;
PROCEDURE update_quota_rules (
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_quota_name IN VARCHAR2,
p_revenue_class_rec_tbl IN cn_plan_element_pub.revenue_class_rec_tbl_type := cn_plan_element_pub.g_miss_revenue_class_rec_tbl,
p_trx_factor_rec_tbl IN cn_plan_element_pub.trx_factor_rec_tbl_type := cn_plan_element_pub.g_miss_trx_factor_rec_tbl,
x_loading_status OUT NOCOPY 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_Quota_Rules';
SAVEPOINT update_quota_rules;
x_loading_status := 'CN_UPDATED';
cn_quota_rule_pvt.update_quota_rule (p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit,
p_validation_level => p_validation_level,
p_quota_rule => l_revclass_rec,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
x_loading_status := 'CN_UPDATED';
x_loading_status := 'CN_UPDATE_FAILED';
cn_trx_factor_pvt.update_trx_factors (p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit,
p_validation_level => p_validation_level,
p_trx_factor_rec_tbl => p_trx_factor_rec_tbl,
p_org_id => l_revclass_rec.org_id,
p_quota_name => l_revclass_rec.plan_element_name,
p_revenue_class_name => l_revclass_rec.revenue_class_name,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_loading_status => l_loading_status
);
ROLLBACK TO update_quota_rules;
ROLLBACK TO update_quota_rules;
ROLLBACK TO update_quota_rules;
END update_quota_rules;
PROCEDURE delete_quota_rule (
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_quota_rule IN OUT NOCOPY quota_rule_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_Quota_Rule';
SAVEPOINT delete_quota_rule;
p_action => 'DELETE',
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
cn_quota_rules_pkg.DELETE_RECORD (x_quota_id => p_quota_rule.quota_id,
x_quota_rule_id => p_quota_rule.quota_rule_id,
x_revenue_class_id => p_quota_rule.revenue_class_id
);
'delete',
x_return_status,
x_msg_count,
x_msg_data
);
ROLLBACK TO delete_quota_rule;
ROLLBACK TO delete_quota_rule;
ROLLBACK TO delete_quota_rule;
END delete_quota_rule;
PROCEDURE delete_quota_rules (
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_quota_name IN VARCHAR2,
p_revenue_class_rec_tbl IN cn_plan_element_pub.revenue_class_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) := 'Delete_Plan_Element';
SAVEPOINT delete_plan_element;
x_loading_status := 'CN_DELETED';
cn_quota_rule_pvt.delete_quota_rule (p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit,
p_validation_level => p_validation_level,
p_quota_rule => l_rec,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
x_loading_status := 'QUOTA_RULE_DELETE_FAILED';
ROLLBACK TO delete_plan_element;
ROLLBACK TO delete_plan_element;
ROLLBACK TO delete_plan_element;
END delete_quota_rules;