[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT rep.name salesrep_name,
rep.employee_number salesrep_no,
rep.cost_center home_cc,
rep.charge_to_cost_center charge_cc,
rep.currency_code currency,
u.user_name analyst_name,
role.name role_name,
plan.name comp_plan_name,
assign.srp_plan_assign_id srp_plan_assign_id
FROM cn_srp_plan_assigns assign,
fnd_user u,
cn_salesreps rep,
cn_comp_plans plan,
cn_roles role,
cn_period_statuses ps,
jtf_rs_resource_extns re
WHERE re.user_id = fnd_global.user_id
AND re.resource_id = rep.resource_id
AND rep.assigned_to_user_id = u.user_id (+)
AND assign.salesrep_id = rep.salesrep_id
AND ps.period_id = p_period_id
AND ps.start_date <= nvl(assign.end_date, ps.start_date)
AND ps.end_date >= assign.start_date
AND plan.comp_plan_id = assign.comp_plan_id
AND assign.role_id = role.role_id
ORDER BY 1;
SELECT rep.name salesrep_name,
rep.employee_number salesrep_no,
rep.cost_center home_cc,
rep.charge_to_cost_center charge_cc,
rep.currency_code currency,
u.user_name analyst_name,
role.name role_name,
plan.name comp_plan_name,
assign.srp_plan_assign_id srp_plan_assign_id
FROM cn_srp_plan_assigns assign,
fnd_user u,
cn_salesreps rep,
cn_comp_plans plan,
cn_roles role,
cn_period_statuses ps
WHERE u.user_id = p_user_id
AND rep.assigned_to_user_id = u.user_id
AND assign.salesrep_id = rep.salesrep_id
AND ps.period_id = p_period_id
AND ps.start_date <= nvl(assign.end_date, ps.start_date)
AND ps.end_date >= assign.start_date
AND plan.comp_plan_id = assign.comp_plan_id
AND assign.role_id = role.role_id
ORDER BY 1;
SELECT rep.name salesrep_name,
rep.employee_number salesrep_no,
rep.cost_center home_cc,
rep.charge_to_cost_center charge_cc,
rep.currency_code currency,
u.user_name analyst_name,
role.name role_name,
plan.name comp_plan_name,
assign.srp_plan_assign_id srp_plan_assign_id
FROM cn_srp_plan_assigns assign,
fnd_user u,
cn_salesreps rep,
cn_comp_plans plan,
cn_roles role,
cn_period_statuses ps
WHERE rep.assigned_to_user_id = u.user_id (+)
AND assign.salesrep_id = rep.salesrep_id
AND ps.period_id = p_period_id
AND ps.start_date <= nvl(assign.end_date, ps.start_date)
AND ps.end_date >= assign.start_date
AND plan.comp_plan_id = assign.comp_plan_id
AND assign.role_id = role.role_id
ORDER BY 1;
select nvl(sum((nvl(balance2_bbd, 0) - nvl(balance2_bbc, 0)) +
(nvl(balance10_bbd,0) - nvl(balance10_bbc,0)) +
(nvl(balance28_bbd,0) - nvl(balance28_bbc,0))),0) begin_balance,
nvl(sum((nvl(balance2_dtd, 0) - nvl(balance2_ctd, 0)) +
(nvl(balance10_dtd,0) - nvl(balance10_ctd,0))),0) earnminuspay,
nvl(sum((nvl(balance28_dtd,0) - nvl(balance28_ctd,0))),0) draw
from cn_srp_periods p, cn_srp_plan_assigns spa
where spa.srp_plan_assign_id = l_srp_plan_assign_id
and p.salesrep_id = spa.salesrep_id
and p.credit_type_id = p_credit_type_id
and p.role_id = spa.role_id
and p.period_id = p_period_id;
SELECT distinct quota_group_code
FROM cn_quotas where quota_group_code is not null;
SELECT nvl(sum(sqa.target),0) target
from cn_srp_quota_assigns sqa,
cn_quotas q
where sqa.srp_plan_assign_id = l_srp_plan_assign_id
and sqa.quota_id = q.quota_id
and q.quota_group_code = l_quota_group_code;
select nvl(sum(itd_target),0) itd_target
from cn_srp_period_quotas spq, cn_quotas q
where srp_plan_assign_id = l_srp_plan_assign_id
and period_id = p_period_id
and q.quota_id = spq.quota_id
and q.quota_group_code = l_quota_group_code;
SELECT sum(nvl(cspq.perf_achieved_itd,0)) ytd_credit,
sum(nvl(cspq.commission_payed_itd,0)) ytd_earnings
FROM cn_srp_period_quotas cspq,
cn_quotas quota
WHERE cspq.period_id = p_period_id
AND cspq.srp_plan_assign_id = l_srp_plan_assign_id
AND cspq.quota_id = quota.quota_id
AND nvl(quota.quota_group_code, FND_API.G_MISS_CHAR) =
nvl(l_quota_group_code , FND_API.G_MISS_CHAR)
AND quota.credit_type_id = p_credit_type_id
GROUP BY cspq.salesrep_id,
cspq.srp_plan_assign_id,
cspq.period_id;
SELECT sum(nvl(cspq.target_amount,0)) ptd_target,
sum(nvl(cspq.perf_achieved_ptd,0)) ptd_credit,
sum(nvl(cspq.commission_payed_ptd,0)) ptd_earnings
FROM cn_quotas quota,
cn_srp_period_quotas cspq
WHERE cspq.period_id = p_period_id
AND cspq.srp_plan_assign_id = l_srp_plan_assign_id
AND cspq.quota_id = quota.quota_id
AND quota.quota_group_code = l_quota_group_code
AND quota.credit_type_id = p_credit_type_id
GROUP BY cspq.salesrep_id,
cspq.srp_plan_assign_id,
cspq.period_id;
select distinct quota_group_code from cn_quotas
where quota_group_code is not null;
SELECT min(period_id), max(period_id)
INTO l_min_period_id, l_max_period_id
FROM cn_acc_period_statuses_v
WHERE period_year = mod(floor(p_period_id/1000),10000);