[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;
SELECT quota_id, quota_type_code, incentive_type_code, credit_type_id
INTO l_pe_rec.quota_id, l_pe_rec.quota_type_code, l_pe_rec.incentive_type_code, l_pe_rec.credit_type_id
FROM cn_quotas_v
WHERE NAME = l_pe_rec.NAME and org_id = p_revenue_class_rec.org_id;
SELECT DECODE (p_revenue_class_rec.rev_class_target, fnd_api.g_miss_num, 0, NULL, 0, p_revenue_class_rec.rev_class_target)
INTO l_pe_rec.rev_class_target
FROM SYS.DUAL;
SELECT DECODE (p_revenue_class_rec.rev_class_payment_amount, fnd_api.g_miss_num, 0, NULL, 0, p_revenue_class_rec.rev_class_payment_amount)
INTO l_pe_rec.rev_class_payment_amount
FROM SYS.DUAL;
SELECT DECODE (p_revenue_class_rec.rev_class_performance_goal, fnd_api.g_miss_num, 0, NULL, 0, p_revenue_class_rec.rev_class_performance_goal)
INTO l_pe_rec.rev_class_performance_goal
FROM SYS.DUAL;
SELECT COUNT (*)
INTO l_same_pe
FROM cn_quota_rules qr
WHERE qr.revenue_class_id = (SELECT revenue_class_id
FROM cn_revenue_classes
WHERE NAME = p_pe_rec.rev_class_name
AND org_id = p_pe_rec.org_id) AND qr.quota_id = p_pe_rec.quota_id;
PROCEDURE check_valid_update (
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_rev_class_name_old IN VARCHAR2,
x_rev_class_id_old OUT NOCOPY NUMBER,
x_quota_rule_id_old OUT NOCOPY NUMBER,
p_new_pe_rec IN cn_chk_plan_element_pkg.pe_rec_type := cn_chk_plan_element_pkg.g_miss_pe_rec,
p_loading_status IN VARCHAR2,
x_loading_status OUT NOCOPY VARCHAR2
)
IS
l_api_name CONSTANT VARCHAR2 (30) := 'Check_Valid_Update';
SELECT COUNT (*)
INTO l_same_pe
FROM cn_quota_rules qr
WHERE qr.revenue_class_id = (SELECT revenue_class_id
FROM cn_revenue_classes
WHERE revenue_class_id = x_rev_class_id_old) AND qr.quota_id = p_new_pe_rec.quota_id;
SELECT COUNT (*)
INTO l_same_pe
FROM cn_quota_rules qr
WHERE qr.revenue_class_id = (SELECT revenue_class_id
FROM cn_revenue_classes
WHERE revenue_class_id = p_new_pe_rec.rev_class_id) AND qr.quota_id = p_new_pe_rec.quota_id;
END check_valid_update;
x_loading_status := 'CN_INSERTED';
ELSIF (x_return_status = fnd_api.g_ret_sts_success) AND (x_loading_status = 'CN_INSERTED')
THEN
IF l_pe_rec.incentive_type_code IN ('COMMISSION', 'BONUS')
THEN
cn_quota_rules_pkg.begin_record (x_operation => 'INSERT',
x_object_version_number => l_pe_rec.object_version_number,
x_org_id => l_pe_rec.org_id,
x_quota_rule_id => l_pe_rec.quota_rule_id,
x_quota_id => l_pe_rec.quota_id,
x_revenue_class_id => l_pe_rec.rev_class_id,
x_revenue_class_name => l_pe_rec.rev_class_name,
x_target => l_pe_rec.rev_class_target,
x_revenue_class_id_old => l_pe_rec.rev_class_id,
x_target_old => l_pe_rec.rev_class_target,
x_payment_amount => l_pe_rec.rev_class_payment_amount,
x_performance_goal => l_pe_rec.rev_class_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
);
UPDATE cn_trx_factors
SET event_factor = p_trx_factor_rec_tbl (i).event_factor
WHERE quota_rule_id = l_pe_rec.quota_rule_id
AND quota_id = l_pe_rec.quota_id
AND trx_type = p_trx_factor_rec_tbl (i).trx_type;
IF (x_return_status <> fnd_api.g_ret_sts_success) OR x_loading_status <> 'CN_INSERTED'
THEN
RAISE fnd_api.g_exc_error;
x_loading_status := 'CN_INSERTED'; -- Calling Place will handle this
END IF; -- CN_INSERTED.
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,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
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
)
IS
l_api_name CONSTANT VARCHAR2 (30) := 'Update_Quota_Rules';
SAVEPOINT update_plan_element;
x_loading_status := 'CN_UPDATED';
check_valid_update (x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_rev_class_name_old => p_revenue_class_rec_tbl (i).rev_class_name_old,
x_rev_class_id_old => l_rev_class_id_old,
x_quota_rule_id_old => l_quota_rule_id,
p_new_pe_rec => l_pe_rec,
p_loading_status => x_loading_status,
x_loading_status => l_loading_status
);
ELSIF (x_return_status = fnd_api.g_ret_sts_success) AND (x_loading_status = 'CN_UPDATED')
THEN
IF l_pe_rec.incentive_type_code IN ('COMMISSION', 'BONUS')
THEN
cn_quota_rules_pkg.begin_record (x_operation => 'UPDATE',
x_quota_rule_id => l_quota_rule_id,
x_object_version_number => l_pe_rec.object_version_number,
x_org_id => l_pe_rec.org_id,
x_quota_id => l_pe_rec.quota_id,
x_revenue_class_id => l_pe_rec.rev_class_id,
x_revenue_class_name => l_pe_rec.rev_class_name,
x_target => l_pe_rec.rev_class_target,
x_payment_amount => l_pe_rec.rev_class_payment_amount,
x_performance_goal => l_pe_rec.rev_class_performance_goal,
x_revenue_class_id_old => l_rev_class_id_old,
x_target_old => l_pe_rec.rev_class_target,
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
);
UPDATE cn_trx_factors
SET event_factor = p_trx_factor_rec_tbl (i).event_factor
WHERE quota_rule_id = l_quota_rule_id AND trx_type = p_trx_factor_rec_tbl (i).trx_type;
IF (x_return_status <> fnd_api.g_ret_sts_success) OR x_loading_status <> 'CN_UPDATED'
THEN
RAISE fnd_api.g_exc_error;
ROLLBACK TO update_plan_element;
ROLLBACK TO update_plan_element;
ROLLBACK TO update_plan_element;
END update_quota_rules;
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,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
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
)
IS
l_api_name CONSTANT VARCHAR2 (30) := 'Delete_Plan_Element';
SAVEPOINT delete_plan_element;
x_loading_status := 'CN_DELETED';
ELSIF (x_return_status = fnd_api.g_ret_sts_success AND x_loading_status = 'CN_DELETED')
THEN
-- Delete Record;
cn_quota_rules_pkg.DELETE_RECORD (x_quota_id => l_rev_rec.quota_id,
x_quota_rule_id => l_rev_rec.quota_rule_id,
x_revenue_class_id => l_rev_rec.revenue_class_id
);
ROLLBACK TO delete_plan_element;
ROLLBACK TO delete_plan_element;
ROLLBACK TO delete_plan_element;
END delete_quota_rules;