[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT cn_period_quotas_s.NEXTVAL
INTO x_period_quota_id
FROM DUAL;
SELECT MAX (start_date)
INTO l_previous_start_date
FROM cn_acc_period_statuses_v
WHERE TRUNC (start_date) <= TRUNC (p_start_date)
AND period_status IN ('F', 'O')
AND org_id = p_org_id;
SELECT MIN (start_date)
INTO l_previous_start_date
FROM cn_acc_period_statuses_v
WHERE period_status IN ('F', 'O')
AND org_id = p_org_id;
x_last_update_date DATE,
x_last_update_login NUMBER,
x_last_updated_by NUMBER,
x_created_by NUMBER,
x_period_type_code VARCHAR2,
x_performance_goal NUMBER
)
IS
l_org_id NUMBER;
SELECT org_id
INTO l_org_id
FROM cn_quotas
WHERE quota_id = x_quota_id;
IF x_operation = 'INSERT'
THEN
INSERT_RECORD (x_period_quota_id => x_period_quota_id,
p_period_id => x_period_id,
p_quota_id => x_quota_id,
p_period_target => x_period_target,
p_itd_target => x_itd_target,
p_period_payment => x_period_payment,
p_itd_payment => x_itd_payment,
p_quarter_num => x_quarter_num,
p_period_year => x_period_year,
p_creation_date => x_creation_date,
p_last_update_date => x_last_update_date,
p_last_update_login => x_last_update_login,
p_last_updated_by => x_last_updated_by,
p_created_by => x_created_by,
p_period_type_code => x_period_type_code,
p_performance_goal => x_performance_goal
);
ELSIF x_operation = 'UPDATE'
THEN
UPDATE_RECORD (x_period_quota_id,
x_quota_id,
x_period_id,
x_period_target,
x_period_payment,
x_performance_goal,
x_last_update_date,
x_last_update_login,
x_last_updated_by,
l_itd_tgt,
l_itd_pmt_amount,
l_itd_perf_goal,
l_object_version_number
);
ELSIF x_operation = 'DELETE'
THEN
DELETE_RECORD (x_quota_id);
PROCEDURE INSERT_RECORD (
x_period_quota_id IN OUT NOCOPY NUMBER,
p_period_id NUMBER,
p_quota_id NUMBER,
p_period_target NUMBER,
p_itd_target NUMBER,
p_period_payment NUMBER,
p_itd_payment NUMBER,
p_quarter_num NUMBER,
p_period_year NUMBER,
p_creation_date DATE,
p_last_update_date DATE,
p_last_update_login NUMBER,
p_last_updated_by NUMBER,
p_created_by NUMBER,
p_period_type_code VARCHAR2,
p_performance_goal NUMBER
)
IS
CURSOR l_period_quotas_cr
IS
SELECT --cn_period_quotas_s.nextval,
p_period_id c1,
p_quota_id c2,
p_period_target c3,
0 c4,
p_period_payment c5,
p_performance_goal c6,
0 c7,
p.quarter_num c8,
p.period_year c9,
p_creation_date c10,
p_last_update_date c11,
p_last_update_login c12,
p_last_updated_by c13,
p_created_by c14,
q.org_id org_id
FROM cn_acc_period_statuses_v p, cn_quotas q
WHERE q.org_id = p.org_id
AND q.quota_id = p_quota_id
AND p.period_id = p_period_id
AND NOT EXISTS (SELECT 'this period_quota already exists'
FROM cn_period_quotas pq
WHERE pq.period_id = p.period_id
AND pq.quota_id = q.quota_id
AND pq.org_id = q.org_id);
SELECT cn_period_quotas_s.NEXTVAL
INTO l_pqs
FROM DUAL;
INSERT INTO cn_period_quotas
(period_quota_id,
period_id,
quota_id,
period_target,
itd_target,
period_payment,
performance_goal,
itd_payment,
quarter_num,
period_year,
creation_date,
last_update_date,
last_update_login,
last_updated_by,
created_by,
org_id
)
SELECT l_pqs,
l_period_quota.c1,
l_period_quota.c2,
l_period_quota.c3,
l_period_quota.c4,
l_period_quota.c5,
l_period_quota.c6,
l_period_quota.c7,
l_period_quota.c8,
l_period_quota.c9,
l_period_quota.c10,
l_period_quota.c11,
l_period_quota.c12,
l_period_quota.c13,
l_period_quota.c14,
l_period_quota.org_id
FROM DUAL;
END INSERT_RECORD;
PROCEDURE INSERT_RECORD (
x_quota_id NUMBER
)
IS
l_user_id NUMBER (15);
SELECT p.period_id c1,
q.quota_id c2,
0 c3,
0 c4,
0 c5,
0 c6,
0 c7,
p.quarter_num c8,
p.period_year c9,
SYSDATE c10,
SYSDATE c11,
l_login_id c12,
l_user_id c13,
l_user_id c14,
q.org_id
FROM cn_quotas q,
cn_acc_period_statuses_v p
WHERE q.quota_id = x_quota_id
AND p.start_date >= previous_period (q.start_date, q.org_id)
AND p.end_date <= cn_api.next_period (NVL (q.end_date, p.end_date), q.org_id)
AND q.org_id = p.org_id
AND NOT EXISTS (SELECT 'this period_quota already exists'
FROM cn_period_quotas pq
WHERE pq.period_id = p.period_id
AND pq.quota_id = q.quota_id
AND pq.org_id = q.org_id)
-- bug 2460926, check if all the open period ends before the specified start_date
AND EXISTS (SELECT r1.end_date
FROM cn_acc_period_statuses_v r1
WHERE r1.end_date > q.start_date
AND r1.org_id = q.org_id);
l_insert_flag VARCHAR2(1);
l_insert_flag := 'Y';
select end_date, org_id into l_end_date, l_org_id from
cn_quotas_v where quota_id = x_quota_id;
select min(start_date) into l_min_date from cn_acc_period_statuses_v
where period_status IN ('F', 'O') and org_id = l_org_id;
l_insert_flag := 'N';
IF (x_quota_id IS NOT NULL AND l_insert_flag = 'Y') THEN
FOR l_period_quota IN l_period_quotas_cr
LOOP
SELECT cn_period_quotas_s.NEXTVAL
INTO l_pqs
FROM DUAL;
INSERT INTO cn_period_quotas
(period_quota_id,
period_id,
quota_id,
period_target,
itd_target,
period_payment,
performance_goal,
itd_payment,
quarter_num,
period_year,
creation_date,
last_update_date,
last_update_login,
last_updated_by,
created_by,
org_id)
SELECT l_pqs,
l_period_quota.c1,
l_period_quota.c2,
l_period_quota.c3,
l_period_quota.c4,
l_period_quota.c5,
l_period_quota.c6,
l_period_quota.c7,
l_period_quota.c8,
l_period_quota.c9,
l_period_quota.c10,
l_period_quota.c11,
l_period_quota.c12,
l_period_quota.c13,
l_period_quota.c14,
l_period_quota.org_id
FROM DUAL;
END INSERT_RECORD;
PROCEDURE UPDATE_RECORD (
p_period_quota_id NUMBER,
p_quota_id NUMBER,
p_period_id NUMBER,
p_period_target NUMBER,
p_period_payment NUMBER,
p_performance_goal NUMBER,
p_last_update_date DATE,
p_last_update_login NUMBER,
p_last_updated_by NUMBER,
x_itd_target OUT NOCOPY NUMBER,
x_itd_payment_amount OUT NOCOPY NUMBER,
x_itd_performance_amount OUT NOCOPY NUMBER,
x_object_version_number OUT NOCOPY NUMBER
)
IS
-- Get the srp_quota_assign info based on this quota
CURSOR srp_quota_assigns
IS
SELECT srp_plan_assign_id
FROM cn_srp_quota_assigns
WHERE quota_id = p_quota_id AND customized_flag = 'N';
SELECT p.period_quota_id,
p.period_target,
p.period_payment,
p.performance_goal
FROM cn_period_quotas p,
cn_acc_period_statuses_v cp,
cn_cal_per_int_types cpit,
cn_quotas cq
WHERE p.quota_id = p_quota_id
AND p.quota_id = cq.quota_id
AND cq.org_id = p.org_id
AND p.period_id = cp.period_id
AND cp.period_id = cpit.cal_period_id
AND cpit.interval_type_id = cq.interval_type_id
AND cpit.interval_number = l_interval_number
AND p.period_year = l_period_year
AND cq.org_id = p.org_id
AND cq.org_id = cp.org_id
AND cq.org_id = cpit.org_id
ORDER BY p.period_id;
IS SELECT COUNT (p.period_quota_id) interval_count,
cpit.interval_number interval_number,
p.period_year period_year
FROM cn_period_quotas p,
cn_acc_period_statuses_v cp,
cn_cal_per_int_types cpit,
cn_quotas cq
WHERE p.quota_id = p_quota_id
AND p.quota_id = cq.quota_id
AND p.period_id = cp.period_id
AND cp.period_id = cpit.cal_period_id
AND cpit.interval_type_id = cq.interval_type_id
AND cq.org_id = p.org_id
AND cq.org_id = cp.org_id
AND cq.org_id = cpit.org_id
GROUP BY cpit.interval_number, p.period_year;
SELECT object_version_number
INTO x_object_version_number
FROM cn_period_quotas
WHERE period_quota_id = p_period_quota_id
AND period_id = p_period_id AND quota_id = p_quota_id;
UPDATE cn_period_quotas
SET period_target = p_period_target,
quota_id = p_quota_id,
period_id = p_period_id,
period_payment = p_period_payment,
performance_goal = p_performance_goal,
last_update_date = p_last_update_date,
last_update_login = p_last_update_login,
last_updated_by = p_last_updated_by,
object_version_number = x_object_version_number
WHERE period_quota_id = p_period_quota_id;
UPDATE cn_period_quotas
SET itd_target = l_target_total,
itd_payment = l_payment_total,
performance_goal_itd = l_performance_goal_total
WHERE period_quota_id = pq_rec.period_quota_id;
cn_srp_period_quotas_pkg.DELETE_RECORD (x_srp_plan_assign_id => sqa_rec.srp_plan_assign_id,
x_quota_id => p_quota_id,
x_start_period_id => p_period_id,
x_end_period_id => NULL
);
cn_srp_period_quotas_pkg.INSERT_RECORD (x_srp_plan_assign_id => sqa_rec.srp_plan_assign_id,
x_quota_id => p_quota_id,
x_start_period_id => p_period_id,
x_end_period_id => NULL,
x_start_date => NULL,
x_end_date => NULL
);
END UPDATE_RECORD;
PROCEDURE DELETE_RECORD (x_quota_id NUMBER)
IS
period_quotas_count NUMBER;
SELECT COUNT (*)
INTO period_quotas_count
FROM cn_period_quotas pq
WHERE pq.quota_id = x_quota_id;
DELETE FROM cn_period_quotas
WHERE quota_id = x_quota_id;
END DELETE_RECORD;
l_delete_all_flag CHAR(1);
l_delete_all_flag := 'N';
select start_date, end_date, org_id
into l_start_date, l_end_date, l_org_id from
cn_quotas_v where quota_id = x_quota_id;
select max(end_date) into l_max_date from cn_acc_period_statuses_v
where period_status IN ('F', 'O') and org_id = l_org_id;
select min(start_date) into l_min_date from cn_acc_period_statuses_v
where period_status IN ('F', 'O') and org_id = l_org_id;
select min(period_id) into l_start_period_id
from cn_acc_period_statuses_v
where period_status IN ('F', 'O') and org_id = l_org_id;
l_delete_all_flag := 'Y';
SELECT period_id
INTO l_start_period_id
FROM cn_acc_period_statuses_v
WHERE l_start_date BETWEEN start_date and end_date
AND period_status IN ('F', 'O') and org_id = l_org_id;
l_delete_all_flag := 'Y';
select max(period_id) into l_end_period_id
from cn_acc_period_statuses_v
where period_status IN ('F', 'O') and org_id = l_org_id;
SELECT period_id
INTO l_end_period_id
FROM cn_acc_period_statuses_v
WHERE l_end_date BETWEEN start_date and end_date
AND period_status IN ('F', 'O') and org_id = l_org_id;
IF (l_delete_all_flag = 'Y') THEN
DELETE_RECORD(x_quota_id);
delete from cn_period_quotas where quota_id = x_quota_id
and (period_id < l_start_period_id OR period_id > l_end_period_id);
delete from cn_period_quotas where quota_id = x_quota_id
and period_id < l_start_period_id;
INSERT_RECORD (x_quota_id);
SELECT MAX (cal_period_id) max_cal_period_id
FROM cn_cal_per_int_types
WHERE interval_type_id = p_interval_type_id
AND cal_period_id < p_start_period_id
AND org_id = p_org_id
AND interval_number = (SELECT interval_number
FROM cn_cal_per_int_types q
WHERE q.cal_period_id = p_start_period_id
AND q.interval_type_id = p_interval_type_id
AND q.org_id = p_org_id);
SELECT cal_period_id
FROM cn_cal_per_int_types
WHERE interval_type_id = p_interval_type_id
AND cal_period_id >= p_start_period_id
AND org_id = p_org_id
AND interval_number = (SELECT interval_number
FROM cn_cal_per_int_types
WHERE cal_period_id = p_start_period_id
AND interval_type_id = p_interval_type_id
AND org_id = p_org_id);
SELECT period_id
INTO l_start_period_id
FROM cn_period_quotas
WHERE quota_id = x_quota_id
AND period_quota_id = x_start_period_quota_id;
SELECT interval_type_id, org_id
INTO l_interval_type_id, l_org_id
FROM cn_quotas
WHERE quota_id = x_quota_id;
SELECT NVL (pq.itd_target, 0),
NVL (pq.itd_payment, 0),
NVL (pq.performance_goal_itd, 0)
INTO l_itd_target,
l_itd_payment,
l_performance_goal_itd
FROM cn_period_quotas pq
WHERE quota_id = x_quota_id AND period_id = l_previous_period_id;
UPDATE cn_period_quotas
SET itd_target = l_itd_target,
itd_payment = l_itd_payment,
performance_goal_itd = l_performance_goal_itd
WHERE quota_id = x_quota_id AND period_id = i_period_id.cal_period_id;
SELECT p.period_quota_id,
p.period_target,
p.period_payment,
p.performance_goal
FROM cn_period_quotas p,
cn_acc_period_statuses_v cp,
cn_cal_per_int_types cpit,
cn_quotas cq
WHERE p.quota_id = x_quota_id
AND p.quota_id = cq.quota_id
AND p.period_id = cp.period_id
AND cp.period_id = cpit.cal_period_id
AND cpit.interval_type_id = cq.interval_type_id
AND cpit.interval_number = l_interval_number
AND p.period_year = l_period_year
AND cq.org_id = p.org_id
AND cq.org_id = cp.org_id
AND cq.org_id = cpit.org_id
ORDER BY p.period_id;
SELECT COUNT (p.period_quota_id) interval_count,
cpit.interval_number interval_number,
p.period_year period_year
FROM cn_period_quotas p,
cn_acc_period_statuses_v cp,
cn_cal_per_int_types cpit,
cn_quotas cq
WHERE p.quota_id = x_quota_id
AND p.quota_id = cq.quota_id
AND p.period_id = cp.period_id
AND cp.period_id = cpit.cal_period_id
AND cpit.interval_type_id = cq.interval_type_id
AND cq.org_id = p.org_id
AND cq.org_id = cp.org_id
AND cq.org_id = cpit.org_id
GROUP BY cpit.interval_number, p.period_year;
UPDATE cn_period_quotas
SET itd_target = NVL (l_target_total, 0),
itd_payment = NVL (l_payment_total, 0),
performance_goal_itd = NVL (l_performance_goal_total, 0)
WHERE period_quota_id = pq_rec.period_quota_id;