[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,
calc_formula_id,
start_date,
end_date,
quota_type_code
INTO x_quota_id,
l_calc_formula_id,
l_quota_start_date,
l_quota_end_date,
l_quota_type_code
FROM cn_quotas_v
WHERE NAME = p_quota_name AND org_id = l_org_id;
SELECT start_date,
end_date
INTO l_period_start_date,
l_period_end_date
FROM cn_acc_period_statuses_v
WHERE period_id = x_period_id AND org_id = l_org_id;
SELECT COUNT (*)
INTO l_same_pe
FROM cn_period_quotas pq
WHERE pq.period_id = x_period_id AND pq.quota_id = x_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_quota_name IN VARCHAR2,
p_period_quotas_rec IN cn_plan_element_pub.period_quotas_rec_type,
x_period_quota_id_old OUT NOCOPY NUMBER,
x_period_id_old OUT NOCOPY NUMBER,
x_quota_id_old OUT NOCOPY NUMBER,
p_loading_status IN VARCHAR2,
x_loading_status OUT NOCOPY VARCHAR2
)
IS
l_api_name CONSTANT VARCHAR2 (30) := 'Check_Valid_Update';
SELECT *
INTO l_period_quotas_rec
FROM cn_period_quotas
WHERE period_id = x_period_id_old AND quota_id = x_quota_id_old;
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
cn_period_quotas_pkg.begin_record (x_operation => 'INSERT',
x_period_quota_id => l_period_quota_id,
x_period_id => l_period_id,
x_quota_id => l_quota_id,
x_period_target => p_period_quotas_rec_tbl (i).period_target,
x_itd_target => NULL,
x_period_payment => p_period_quotas_rec_tbl (i).period_payment,
x_itd_payment => NULL,
x_quarter_num => NULL,
x_period_year => NULL,
x_performance_goal => p_period_quotas_rec_tbl (i).performance_goal,
x_creation_date => g_creation_date,
x_last_update_date => g_last_update_date,
x_last_update_login => g_last_update_login,
x_last_updated_by => g_last_updated_by,
x_created_by => g_created_by,
x_period_type_code => NULL
);
END IF; -- CN_INSERTED.
PROCEDURE update_period_quotas (
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_period_quotas_rec_tbl IN cn_plan_element_pub.period_quotas_rec_tbl_type := cn_plan_element_pub.g_miss_period_quotas_rec_tbl,
x_loading_status OUT NOCOPY VARCHAR2
)
IS
l_api_name CONSTANT VARCHAR2 (30) := 'Update_Period_Quotas';
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_quota_name => p_quota_name,
p_period_quotas_rec => p_period_quotas_rec_tbl (i),
x_quota_id_old => l_quota_id,
x_period_id_old => l_period_id,
x_period_quota_id_old => l_period_quota_id,
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
cn_period_quotas_pkg.begin_record (x_operation => 'UPDATE',
x_period_quota_id => l_period_quota_id,
x_period_id => l_period_id,
x_quota_id => l_quota_id,
x_period_target => p_period_quotas_rec_tbl (i).period_target,
x_itd_target => NULL,
x_period_payment => p_period_quotas_rec_tbl (i).period_payment,
x_itd_payment => NULL,
x_quarter_num => NULL,
x_period_year => NULL,
x_performance_goal => p_period_quotas_rec_tbl (i).performance_goal,
x_creation_date => g_creation_date,
x_last_update_date => g_last_update_date,
x_last_update_login => g_last_update_login,
x_last_updated_by => g_last_updated_by,
x_created_by => g_created_by,
x_period_type_code => NULL
);
END IF; -- CN_UPDATED.
ROLLBACK TO update_plan_element;
ROLLBACK TO update_plan_element;
ROLLBACK TO update_plan_element;
END update_period_quotas;