[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT DECODE (p_rev_uplift_rec.payment_factor, fnd_api.g_miss_num, 100, NULL, 100, p_rev_uplift_rec.payment_factor),
DECODE (p_rev_uplift_rec.quota_factor, fnd_api.g_miss_num, 100, NULL, 100, p_rev_uplift_rec.quota_factor)
INTO l_pe_rec.rev_class_payment_uplift,
l_pe_rec.rev_class_quota_uplift
FROM SYS.DUAL;
SELECT end_date
FROM cn_quota_rule_uplifts
WHERE quota_rule_id = p_quota_rule_id
ORDER BY start_date DESC;
SELECT COUNT (*)
INTO l_same_pe
FROM cn_quota_rule_uplifts qru
WHERE qru.quota_rule_id = p_pe_rec.quota_rule_id
AND TRUNC (qru.start_date) = TRUNC (p_pe_rec.rev_uplift_start_date)
AND qru.quota_rule_uplift_id <> NVL (p_quota_rule_uplift_id, 0);
PROCEDURE check_valid_update (
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_quota_name IN VARCHAR2,
p_rev_class_name_old IN VARCHAR2,
p_start_date_old IN DATE,
p_end_date_old IN DATE,
p_new_pe_rec IN cn_chk_plan_element_pkg.pe_rec_type := cn_chk_plan_element_pkg.g_miss_pe_rec,
x_quota_rule_uplift_id OUT NOCOPY NUMBER,
p_loading_status IN VARCHAR2,
x_loading_status OUT NOCOPY VARCHAR2
)
IS
l_api_name CONSTANT VARCHAR2 (30) := 'Check_Valid_Update';
END check_valid_update;
x_loading_status := 'CN_INSERTED';
cn_quota_rule_uplifts_pkg.begin_record (x_operation => 'INSERT',
x_org_id => l_pe_rec.org_id,
x_quota_rule_uplift_id => p_rev_uplift_rec_tbl (i).quota_rule_uplift_id,
x_quota_rule_id => l_pe_rec.quota_rule_id,
x_quota_rule_id_old => l_pe_rec.quota_rule_id,
x_start_date => l_pe_rec.rev_uplift_start_date,
x_start_date_old => l_pe_rec.rev_uplift_start_date,
x_end_date => l_pe_rec.rev_uplift_end_date,
x_end_date_old => l_pe_rec.rev_uplift_end_date,
x_payment_factor => l_pe_rec.rev_class_payment_uplift,
x_payment_factor_old => l_pe_rec.rev_class_payment_uplift,
x_quota_factor => l_pe_rec.rev_class_quota_uplift,
x_quota_factor_old => l_pe_rec.rev_class_quota_uplift,
x_last_updated_by => fnd_global.user_id,
x_creation_date => SYSDATE,
x_created_by => fnd_global.user_id,
x_last_update_login => fnd_global.login_id,
x_last_update_date => SYSDATE,
x_program_type => g_program_type,
x_status_code => NULL,
x_object_version_number => p_rev_uplift_rec_tbl (i).object_version_number
);
x_loading_status := 'CN_UPLIFT_UPDATE_NOT_ALLOWED';
PROCEDURE update_quota_rule_uplift (
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_rev_uplift_rec_tbl IN OUT NOCOPY cn_quota_rule_uplift_pvt.quota_rule_uplift_tbl_type,
x_loading_status OUT NOCOPY VARCHAR2
)
IS
l_api_name CONSTANT VARCHAR2 (30) := 'Update_Quota_Rule_uplift';
SAVEPOINT update_quota_rule_uplift;
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_quota_name => p_quota_name,
p_rev_class_name_old => p_rev_uplift_rec_tbl (i).rev_class_name_old,
p_start_date_old => p_rev_uplift_rec_tbl (i).start_date_old,
p_end_date_old => p_rev_uplift_rec_tbl (i).end_date_old,
p_new_pe_rec => l_pe_rec,
x_quota_rule_uplift_id => p_rev_uplift_rec_tbl (i).quota_rule_uplift_id,
p_loading_status => x_loading_status,
x_loading_status => l_loading_status
);
ELSIF x_loading_status = 'CN_UPDATED'
THEN
cn_quota_rule_uplifts_pkg.begin_record (x_operation => 'UPDATE',
x_org_id => l_pe_rec.org_id,
x_quota_rule_uplift_id => p_rev_uplift_rec_tbl (i).quota_rule_uplift_id,
x_quota_rule_id => l_pe_rec.quota_rule_id,
x_quota_rule_id_old => l_pe_rec.quota_rule_id,
x_start_date => l_pe_rec.rev_uplift_start_date,
x_start_date_old => l_pe_rec.rev_uplift_start_date,
x_end_date => l_pe_rec.rev_uplift_end_date,
x_end_date_old => l_pe_rec.rev_uplift_end_date,
x_payment_factor => l_pe_rec.rev_class_payment_uplift,
x_payment_factor_old => l_pe_rec.rev_class_payment_uplift,
x_quota_factor => l_pe_rec.rev_class_quota_uplift,
x_quota_factor_old => l_pe_rec.rev_class_quota_uplift,
x_last_updated_by => fnd_global.user_id,
x_creation_date => SYSDATE,
x_created_by => fnd_global.user_id,
x_last_update_login => fnd_global.login_id,
x_last_update_date => SYSDATE,
x_program_type => g_program_type,
x_status_code => NULL,
x_object_version_number => p_rev_uplift_rec_tbl (i).object_version_number
);
ROLLBACK TO update_quota_rule_uplift;
ROLLBACK TO update_quota_rule_uplift;
ROLLBACK TO update_quota_rule_uplift;
END update_quota_rule_uplift;
PROCEDURE delete_quota_rule_uplift (
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_rev_uplift_rec_tbl IN OUT NOCOPY cn_quota_rule_uplift_pvt.quota_rule_uplift_tbl_type,
x_loading_status OUT NOCOPY VARCHAR2
)
IS
l_api_name CONSTANT VARCHAR2 (30) := 'Delete_Quota_rule_uplift';
SAVEPOINT delete_quota_rule_uplift;
x_loading_status := 'CN_DELETED';
p_iud_flag => 'D', --D Stands for delete
p_quota_rule_id => l_pe_rec.quota_rule_id,
p_quota_rule_uplift_id => l_quota_rule_uplift_id,
p_loading_status => x_loading_status,
x_loading_status => l_loading_status
);
fnd_message.set_name ('CN', 'CN_UPLIFT_DELETE_NOT_ALLOWED');
x_loading_status := 'CN_UPLIFT_DELETE_NOT_ALLOWED';
ELSIF x_loading_status = 'CN_DELETED'
THEN
cn_quota_rule_uplifts_pkg.begin_record (x_operation => 'DELETE',
x_org_id => l_pe_rec.org_id,
x_quota_rule_uplift_id => l_quota_rule_uplift_id,
x_quota_rule_id => NULL,
x_quota_rule_id_old => NULL,
x_start_date => NULL,
x_start_date_old => NULL,
x_end_date => NULL,
x_end_date_old => NULL,
x_payment_factor => NULL,
x_payment_factor_old => NULL,
x_quota_factor => NULL,
x_quota_factor_old => NULL,
x_last_updated_by => NULL,
x_creation_date => NULL,
x_created_by => NULL,
x_last_update_login => NULL,
x_last_update_date => NULL,
x_program_type => g_program_type,
x_status_code => NULL,
x_object_version_number => p_rev_uplift_rec_tbl (i).object_version_number
);
ROLLBACK TO delete_quota_rule_uplift;
ROLLBACK TO delete_quota_rule_uplift;
ROLLBACK TO delete_quota_rule_uplift;
END delete_quota_rule_uplift;