[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT period_id , start_date, end_date
FROM cn_period_statuses_all
WHERE start_date <= c_end_date
AND end_date >= c_start_date
AND period_type_id = p_period_type_id
AND period_set_id = p_period_set_id
AND org_id = p_org_id;
SELECT MIN(start_date), MAX(end_date)
INTO l_start_date, l_end_date
FROM cn_acc_period_statuses_v
WHERE period_status IN ('O','F')
AND org_id = p_org_id
AND ( ( p_end_date IS NOT NULL AND start_date <= p_end_date
AND end_date >= p_start_date )
OR
( p_end_date IS NULL AND end_date >= p_start_date )
);
SELECT /*+ index (sp, CN_SRP_PERIODS_U2)*/
srp_period_id, sp.period_id, quota_id,
p.period_year, srp_plan_assign_id,
balance1_ctd, balance1_dtd,
balance1_bbc, balance1_bbd,
balance2_ctd, balance2_dtd,
balance2_bbc, balance2_bbd,
balance3_ctd, balance3_dtd,
balance3_bbc, balance3_bbd,
balance4_ctd, balance4_dtd,
balance4_bbc, balance4_bbd,
balance5_ctd, balance5_dtd,
balance5_bbc, balance5_bbd
FROM cn_srp_periods_all sp, cn_period_statuses_all p
WHERE role_id = p_role_id
AND salesrep_id = p_salesrep_id
AND sp.org_id = p_org_id
AND credit_type_id = p_credit_type_id
AND quota_id is not null
AND sp.period_id = p.period_id
AND sp.org_id = p.org_id
ORDER BY quota_id, sp.period_id;
select /*+ index (p2, CN_SRP_PERIODS_U2)*/
p1.srp_period_id, p1.period_id,
nvl(sum(p2.balance1_ctd),0) balance1_ctd,
nvl(sum(p2.balance1_dtd),0) balance1_dtd,
nvl(sum(p2.balance2_ctd),0) balance2_ctd,
nvl(sum(p2.balance2_dtd),0) balance2_dtd,
nvl(sum(p2.balance3_ctd),0) balance3_ctd,
nvl(sum(p2.balance3_dtd),0) balance3_dtd,
nvl(sum(p2.balance4_ctd),0) balance4_ctd,
nvl(sum(p2.balance4_dtd),0) balance4_dtd,
nvl(sum(p2.balance5_ctd),0) balance5_ctd,
nvl(sum(p2.balance5_dtd),0) balance5_dtd,
nvl(sum(p2.balance1_bbc),0) balance1_bbc,
nvl(sum(p2.balance1_bbd),0) balance1_bbd,
nvl(sum(p2.balance2_bbc),0) balance2_bbc,
nvl(sum(p2.balance2_bbd),0) balance2_bbd,
nvl(sum(p2.balance3_bbc),0) balance3_bbc,
nvl(sum(p2.balance3_bbd),0) balance3_bbd,
nvl(sum(p2.balance4_bbc),0) balance4_bbc,
nvl(sum(p2.balance4_bbd),0) balance4_bbd,
nvl(sum(p2.balance5_bbc),0) balance5_bbc,
nvl(sum(p2.balance5_bbd),0) balance5_bbd,
p.period_year
from cn_srp_periods_all p1, cn_srp_periods_all p2,
cn_period_statuses_all p
where p1.salesrep_id = p_salesrep_id
and p1.credit_type_id = p_credit_type_id
AND p1.org_id = p_org_id
and p1.quota_id is null and p1.role_id is null
and p1.salesrep_id = p2.salesrep_id (+)
and p1.period_id = p2.period_id (+)
AND p1.org_id = p2.org_id (+)
and p1.period_id = p.period_id
and p1.credit_type_id = p2.credit_type_id (+)
and p2.role_id (+) is not null and p2.quota_id (+) is not null
AND p.org_id = p_org_id
group by p1.period_id, p1.srp_period_id, p.period_year
order by p1.period_id, p1.srp_period_id;
SELECT srp_period_id,
balance1_bbd, balance1_bbc,
balance2_bbd, balance2_bbc,
balance3_bbd, balance3_bbc,
balance4_bbd, balance4_bbc,
balance5_bbd, balance5_bbc
FROM cn_srp_periods_all sp
WHERE role_id = -1
AND salesrep_id = p_salesrep_id
AND org_id = p_org_id
AND credit_type_id = p_credit_type_id
AND quota_id = -1000
AND period_id = l_period_id;
update cn_srp_periods_all
set balance1_bbc = l_cache_bal1_bbc,
balance1_bbd = l_cache_bal1_bbd,
balance2_bbc = l_cache_bal2_bbc,
balance2_bbd = l_cache_bal2_bbd,
balance3_bbc = l_cache_bal3_bbc,
balance3_bbd = l_cache_bal3_bbd,
balance4_bbc = l_cache_bal4_bbc,
balance4_bbd = l_cache_bal4_bbd,
balance5_bbc = l_cache_bal5_bbc,
balance5_bbd = l_cache_bal5_bbd
where srp_period_id = b.srp_period_id;
update cn_srp_periods_all
set balance1_ctd = p.balance1_ctd,
balance1_dtd = p.balance1_dtd,
balance2_ctd = p.balance2_ctd,
balance2_dtd = p.balance2_dtd,
balance3_ctd = p.balance3_ctd,
balance3_dtd = p.balance3_dtd,
balance4_ctd = p.balance4_ctd,
balance4_dtd = p.balance4_dtd,
balance5_ctd = p.balance5_ctd,
balance5_dtd = p.balance5_dtd
WHERE srp_period_id = p.srp_period_id;
update cn_srp_periods_all
SET balance1_bbc = l_cache_bal1_bbc,
balance1_bbd = l_cache_bal1_bbd,
balance2_bbc = l_cache_bal2_bbc,
balance2_bbd = l_cache_bal2_bbd,
balance3_bbc = l_cache_bal3_bbc,
balance3_bbd = l_cache_bal3_bbd,
balance4_bbc = l_cache_bal4_bbc,
balance4_bbd = l_cache_bal4_bbd,
balance5_bbc = l_cache_bal5_bbc,
balance5_bbd = l_cache_bal5_bbd
where srp_period_id = p.srp_period_id;
update cn_srp_periods_all
SET balance1_bbc=l_cache_bal1_bbc - p.balance1_bbc + cb.balance1_bbc,
balance1_bbd=l_cache_bal1_bbd - p.balance1_bbd + cb.balance1_bbd,
balance2_bbc=l_cache_bal2_bbc - p.balance2_bbc + cb.balance2_bbc,
balance2_bbd=l_cache_bal2_bbd - p.balance2_bbd + cb.balance2_bbd,
balance3_bbc=l_cache_bal3_bbc - p.balance3_bbc + cb.balance3_bbc,
balance3_bbd=l_cache_bal3_bbd - p.balance3_bbd + cb.balance3_bbd,
balance4_bbc=l_cache_bal4_bbc - p.balance4_bbc + cb.balance4_bbc,
balance4_bbd=l_cache_bal4_bbd - p.balance4_bbd + cb.balance4_bbd,
balance5_bbc=l_cache_bal5_bbc - p.balance5_bbc + cb.balance5_bbc,
balance5_bbd=l_cache_bal5_bbd - p.balance5_bbd + cb.balance5_bbd
where srp_period_id = cb.srp_period_id;
(SELECT spg.pay_group_id, spg.start_date, spg.end_date,
pg.period_set_id, pg.period_type_id
FROM cn_srp_pay_groups_all spg,cn_pay_groups_all pg
WHERE spg.salesrep_id = p_salesrep_id
AND spg.org_id = l_org_id
AND spg.pay_group_id = pg.pay_group_id
AND ( ( (p_end_date IS NOT NULL) AND (spg.end_date IS NOT NULL)
AND (spg.start_date <= p_end_date)
AND (spg.end_date >= p_start_date))
OR ((p_end_date IS NOT NULL) AND (spg.end_date IS NULL)
AND (spg.start_date <= p_end_date))
OR ((p_end_date IS NULL) AND (spg.end_date IS NOT NULL)
AND (spg.end_date >= p_start_date))
OR ((p_end_date IS NULL) AND (spg.end_date IS NULL))
)
) ;
(SELECT credit_type_id, quota_id
FROM cn_quotas_all
WHERE quota_id IN
(SELECT quota_id FROM cn_quota_assigns
WHERE comp_plan_id = p_comp_plan_id)
AND quota_id = nvl(p_quota_id, quota_id));
select p.period_id, p.start_date, p.end_date
from cn_period_statuses_all p, cn_repositories_all r
where p.period_id >= l_min_period_id
and r.period_type_id = p.period_type_id
and r.period_set_id = p.period_set_id
AND p.org_id = l_org_id
AND r.org_id = l_org_id
and not exists (select 1 from cn_srp_periods_all
where salesrep_id = p_salesrep_id and period_id = p.period_id
and role_id is null and quota_id is NULL
AND org_id = l_org_id
AND credit_type_id = c_credit_type_id)
order by 1;
select p.period_id, p.start_date, p.end_date
from cn_period_statuses_all p, cn_repositories_all r
where p.period_id >= l_min_period_id
and r.period_type_id = p.period_type_id
and r.period_set_id = p.period_set_id
AND p.org_id = l_org_id
AND r.org_id = l_org_id
and not exists (select 1 from cn_srp_periods_all
where salesrep_id = p_salesrep_id and period_id = p.period_id
and role_id = -1 and quota_id = -1000
AND org_id = l_org_id
AND credit_type_id = c_credit_type_id)
order by 1;
select distinct credit_type_id
from cn_srp_periods_all
where salesrep_id = p_salesrep_id
AND org_id = l_org_id
and quota_id is not null
and credit_type_id is not null;
select srp_plan_assign_id
from cn_srp_plan_assigns_all
where salesrep_id = p_salesrep_id
AND org_id = l_org_id
and role_id = p_role_id
and comp_plan_id = p_comp_plan_id
and l_start_date between start_date and nvl(end_date, l_start_date);
x_loading_status := 'CN_INSERTED';
SELECT org_id
INTO l_org_id
FROM cn_comp_plans_all
WHERE comp_plan_id = p_comp_plan_id;
SELECT count(1) INTO l_dummy
FROM cn_srp_periods_all
WHERE salesrep_id = p_salesrep_id
AND org_id = l_org_id
AND period_id = l_pay_period_rec_tbl(i).period_id
AND role_id = p_role_id
AND quota_id = l_quota_csr.quota_id
AND credit_type_id = l_quota_csr.credit_type_id;
cn_srp_periods_pkg.insert_row
(x_srp_period_id => l_srp_period_id
,x_salesrep_id => p_salesrep_id
,x_org_id => l_org_id
,x_period_id => l_pay_period_rec_tbl(i).period_id
,x_start_date => l_pay_period_rec_tbl(i).start_date
,x_end_date => l_pay_period_rec_tbl(i).end_date
,x_credit_type_id => l_quota_csr.credit_type_id
,x_srp_plan_assign_id => l_srp_plan_assign_id
,x_role_id => p_role_id
,x_quota_id => l_quota_csr.quota_id
,x_pay_group_id => l_srp_pay_grp_csr.pay_group_id
,x_created_by => FND_GLOBAL.USER_ID
,x_creation_date => SYSDATE
,x_last_update_date => SYSDATE
,x_last_updated_by => FND_GLOBAL.USER_ID
,x_last_update_login => FND_GLOBAL.LOGIN_ID
);
-- records exist - update plan assign ID
update cn_srp_periods_all
set srp_plan_assign_id = l_srp_plan_assign_id,
start_date = l_pay_period_rec_tbl(i).start_date,
end_date = l_pay_period_rec_tbl(i).end_date
where salesrep_id = p_salesrep_id
AND org_id = l_org_id
AND period_id = l_pay_period_rec_tbl(i).period_id
AND role_id = p_role_id
AND quota_id = l_quota_csr.quota_id
AND credit_type_id = l_quota_csr.credit_type_id;
select min(period_id) into l_min_period_id
from cn_srp_periods_all
where salesrep_id = p_salesrep_id
AND org_id = l_org_id
and quota_id is not null
and credit_type_id = ct.credit_type_id;
cn_srp_periods_pkg.insert_row
(x_srp_period_id => l_srp_period_id
,x_salesrep_id => p_salesrep_id
,x_org_id => l_org_id
,x_period_id => p.period_id
,x_start_date => p.start_date
,x_end_date => p.end_date
,x_credit_type_id => ct.credit_type_id
,x_srp_plan_assign_id => null
,x_role_id => null
,x_quota_id => null
,x_pay_group_id => null
,x_created_by => FND_GLOBAL.USER_ID
,x_creation_date => SYSDATE
,x_last_update_date => SYSDATE
,x_last_updated_by => FND_GLOBAL.USER_ID
,x_last_update_login => FND_GLOBAL.LOGIN_ID
);
cn_srp_periods_pkg.insert_row
(x_srp_period_id => l_srp_period_id
,x_salesrep_id => p_salesrep_id
,x_org_id => l_org_id
,x_period_id => p.period_id
,x_start_date => p.start_date
,x_end_date => p.end_date
,x_credit_type_id => ct.credit_type_id
,x_srp_plan_assign_id => -1
,x_role_id => -1
,x_quota_id => -1000
,x_pay_group_id => -1
,x_created_by => FND_GLOBAL.USER_ID
,x_creation_date => SYSDATE
,x_last_update_date => SYSDATE
,x_last_updated_by => FND_GLOBAL.USER_ID
,x_last_update_login => FND_GLOBAL.LOGIN_ID
);
PROCEDURE Update_Delta_Srp_Pds_No_Sync
(p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_commit IN VARCHAR2,
p_validation_level IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_del_srp_prd_rec IN delta_srp_period_rec_type,
x_loading_status OUT NOCOPY VARCHAR2
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Update_Delta_Srp_Pds_No_Sync';
SAVEPOINT Update_Delta_Srp_Pds_No_Sync;
x_loading_status := 'CN_UPDATED';
UPDATE cn_srp_periods_all
SET
balance1_ctd = (Nvl(balance1_ctd,0) +
Nvl(p_del_srp_prd_rec.del_balance1_ctd,0)),
balance1_dtd = (Nvl(balance1_dtd,0) +
Nvl(p_del_srp_prd_rec.del_balance1_dtd,0)),
balance2_ctd = (Nvl(balance2_ctd,0) +
Nvl(p_del_srp_prd_rec.del_balance2_ctd,0)),
balance2_dtd = (Nvl(balance2_dtd,0) +
Nvl(p_del_srp_prd_rec.del_balance2_dtd,0)),
balance3_ctd = (Nvl(balance3_ctd,0) +
Nvl(p_del_srp_prd_rec.del_balance3_ctd,0)),
balance3_dtd = (Nvl(balance3_dtd,0) +
Nvl(p_del_srp_prd_rec.del_balance3_dtd,0)),
balance4_ctd = (Nvl(balance4_ctd,0) +
Nvl(p_del_srp_prd_rec.del_balance4_ctd,0)),
balance4_dtd = (Nvl(balance4_dtd,0) +
Nvl(p_del_srp_prd_rec.del_balance4_dtd,0)),
balance5_ctd = (Nvl(balance5_ctd,0) +
Nvl(p_del_srp_prd_rec.del_balance5_ctd,0)),
balance5_dtd = (Nvl(balance5_dtd,0) +
Nvl(p_del_srp_prd_rec.del_balance5_dtd,0))
WHERE srp_period_id = p_del_srp_prd_rec.srp_period_id;
ROLLBACK TO Update_Delta_Srp_Pds_No_Sync;
ROLLBACK TO Update_Delta_Srp_Pds_No_Sync;
ROLLBACK TO Update_Delta_Srp_Pds_No_Sync;
END Update_Delta_Srp_Pds_No_Sync;
PROCEDURE Update_Delta_Srp_Periods
(p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_commit IN VARCHAR2,
p_validation_level IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_del_srp_prd_rec IN delta_srp_period_rec_type,
x_loading_status OUT NOCOPY VARCHAR2
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Update_Delta_Srp_Periods';
SAVEPOINT Update_Delta_Srp_Periods;
x_loading_status := 'CN_UPDATED';
SELECT salesrep_id, credit_type_id, role_id, org_id
INTO l_salesrep_id, l_credit_type_id, l_role_id, l_org_id
FROM cn_srp_periods_all
WHERE srp_period_id = p_del_srp_prd_rec.srp_period_id;
Update_Delta_Srp_Pds_No_Sync
(p_api_version => 1.0,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_del_srp_prd_rec => p_del_srp_prd_rec,
x_loading_status => x_loading_status);
ROLLBACK TO Update_Delta_Srp_Periods;
ROLLBACK TO Update_Delta_Srp_Periods;
ROLLBACK TO Update_Delta_Srp_Periods;
END Update_Delta_Srp_Periods;
PROCEDURE Update_Pmt_Delta_Srp_Periods
(p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_commit IN VARCHAR2,
p_validation_level IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_del_srp_prd_rec IN delta_srp_period_rec_type,
x_loading_status OUT NOCOPY VARCHAR2
) IS
BEGIN
null;
END Update_Pmt_Delta_Srp_Periods ;