[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
'SELECT distinct s.name, s.salesrep_id FROM jtf_rs_resource_extns re,
cn_salesreps s,cn_srp_plan_assigns assign WHERE re.resource_id = s.resource_id
and s.org_id = assign.org_id and re.category <> ''TBH''
and s.salesrep_id > 0 and s.salesrep_id = assign.salesrep_id
and :b1 <= nvl(assign.end_date, :b2)
and :b3 >= assign.start_date and s.org_id = :b4';
select period_year from cn_period_statuses
where period_id = p_period_id and org_id=p_org_id;
select s.salesrep_id
from cn_rs_salesreps s, jtf_rs_resource_extns re
where re.user_id = a_user_id and s.resource_id = re.resource_id
and s.org_id = a_org_id;
select je.resource_id into l_resource_id from jtf_rs_resource_extns je where je.user_id =l_user_id;
SELECT count(1) into l_groupquery_result FROM JTF_RS_GROUP_MBR_ROLE_VL GPM, JTF_RS_GROUP_USAGES GPU
WHERE GPM.GROUP_ID=GPU.GROUP_ID and GPU.usage='COMP_PAYMENT'
AND NVL(GPM.END_DATE_ACTIVE,TO_DATE('01/01/9999','DD/MM/RRRR')) >= SYSDATE
AND GPM.RESOURCE_ID = l_resource_id;
select count(1) into l_rolequery_result from JTF_RS_ROLES_B ROLEB , JTF_RS_ROLE_RELATIONS ROLER
WHERE ROLER.DELETE_FLAG ='N' AND nvl(ROLER.END_DATE_ACTIVE,TO_DATE('01/01/9999','DD/MM/RRRR')) >= SYSDATE
AND ROLER.ROLE_ID= ROLEB.ROLE_ID AND ROLEB.ROLE_TYPE_CODE='SALES_COMP_PAYMENT_ANALIST'
AND ROLER.ROLE_RESOURCE_ID = l_resource_id;
select start_date into l_year_start_date from cn_period_statuses
where period_id = p_period_id - mod(p_period_id, 1000) + 1;
select min(start_date) into l_year_start_date from cn_period_statuses cps,cn_repositories cr
where cps.period_year=l_year and cr.period_set_id=cps.period_set_id
and cr.period_type_id=cps.period_type_id and cr.org_id=cps.org_id
and cr.org_id=p_org_id;
select end_date into l_period_end_date from cn_period_statuses
where period_id = p_period_id and org_id=p_org_id;
select name, employee_number, cost_center,
charge_to_cost_center, assigned_to_user_name
from cn_salesreps
where salesrep_id = p_salesrep_id and org_id=p_org_id;
SELECT DISTINCT S.SALESREP_ID,S.ORG_ID
FROM JTF_RS_REP_MANAGERS RM,JTF_RS_GROUP_USAGES U, CN_SALESREPS S WHERE
RM.PARENT_RESOURCE_ID=(SELECT R.RESOURCE_ID
FROM JTF_RS_RESOURCE_EXTNS R WHERE R.USER_ID = FND_GLOBAL.USER_ID)
AND RM.RESOURCE_ID=S.RESOURCE_ID
AND RM.HIERARCHY_TYPE IN ('MGR_TO_REP', 'REP_TO_REP', 'MGR_TO_MGR')
AND U.USAGE=CN_SYSTEM_PARAMETERS.VALUE('CN_REPORTING_HIERARCHY',p_org_id)
AND RM.GROUP_ID = U.GROUP_ID AND SYSDATE >= RM.START_DATE_ACTIVE
AND (RM.END_DATE_ACTIVE IS NULL OR (RM.END_DATE_ACTIVE >= SYSDATE))
AND DENORM_LEVEL IS NOT NULL AND S.org_id =p_org_id;
select salesrep_id,
org_id,
name,
employee_number,
cost_center,
charge_to_cost_center,
assigned_to_user_name
from cn_salesreps
where org_id = c_org_id
AND assigned_to_user_id = c_analyst_id;
select salesrep_id,
org_id,
name,
employee_number,
cost_center,
charge_to_cost_center,
assigned_to_user_name
from cn_salesreps
where org_id = c_org_id;
SELECT assign.srp_plan_assign_id srp_plan_assign_id,
r.name role_name,
cp.name plan_name,
assign.start_date start_date,
assign.end_date end_date
FROM cn_srp_plan_assigns assign,
cn_roles r,
cn_comp_plans cp
WHERE l_year_start_date <= nvl(assign.end_date, l_year_start_date)
AND l_period_end_date >= assign.start_date
AND assign.role_id = r.role_id
AND assign.comp_plan_id = cp.comp_plan_id
AND assign.org_id = cp.org_id
AND assign.salesrep_id = p_salesrep_id
AND assign.org_id = p_org_id
ORDER BY assign.start_date;
select nvl(sum(balance2_dtd),0)
from cn_srp_periods sp, cn_srp_plan_assigns spa, cn_quotas_all q
where spa.srp_plan_assign_id = l_srp_plan_assign_id
and sp.srp_plan_assign_id = spa.srp_plan_assign_id
and sp.salesrep_id = spa.salesrep_id
and sp.quota_id = q.quota_id
and q.quota_group_code is not null
and sp.credit_type_id = p_credit_type_id
and sp.org_id = spa.org_id
and sp.org_id = q.org_id
and sp.org_id = l_org_id
and period_id between l_start_pd and l_end_pd;
select min(p2.start_date), min(p2.period_id)
into l_year_start_date, l_year_start_period
from cn_period_statuses p1, cn_period_statuses p2
where p1.period_id = p_period_id
and p1.period_year = p2.period_year
and p1.period_set_id = p2.period_set_id
and p1.period_type_id = p2.period_type_id
and p1.org_id = p2.org_id and p1.org_id = p_org_id;
select end_date into l_period_end_date from cn_period_statuses
where period_id = p_period_id and org_id=p_org_id;
select ps.period_id period_id,
nvl(inv.credit,0) credit,
nvl(inv.earnings,0) earnings,
nvl(inv.target,0) target,
nvl(inv.itd_target,0) itd_target
from cn_period_statuses ps, cn_repositories r, cn_period_statuses ps2,
(SELECT ps.period_id period_id,
nvl(sum(cspq.perf_achieved_ptd),0) credit,
nvl(sum(cspq.commission_payed_ptd),0) earnings,
nvl(sum(cspq.target_amount),0) target,
nvl(sum(cspq.itd_target),0) itd_target
FROM cn_srp_period_quotas cspq,
cn_quotas_all quota,
cn_period_statuses ps,
cn_repositories r,
cn_period_statuses ps2
WHERE cspq.srp_plan_assign_id = p_srp_plan_assign_id
AND cspq.quota_id = quota.quota_id
AND quota.credit_type_id = p_credit_type_id
AND quota.quota_group_code = p_quota_group_code
and quota.org_id = cspq.org_id
and quota.org_id = p_org_id
AND quota.quota_id > 0
and ((p_quota_id is not null and quota.quota_id = p_quota_id)
OR (QUOTA.QUOTA_ID = QUOTA.QUOTA_ID AND p_quota_id is null))
and ps.period_year = ps2.period_year
and ps.period_id <= p_period_id
and ps2.period_id = p_period_id
and ps.period_set_id = r.period_set_id
and ps.period_type_id = r.period_type_id
and ps2.org_id = p_org_id
and ps.org_id = p_org_id
and r.org_id = p_org_id
and ps.period_id = cspq.period_id
GROUP BY ps.period_id) inv
where ps.period_id = inv.period_id(+)
and ps.period_year = ps2.period_year
and ps.period_id <= p_period_id
and ps2.period_id = p_period_id
and ps.period_set_id = r.period_set_id
and ps.period_type_id = r.period_type_id
and ps.org_id = ps2.org_id
and ps.org_id = r.org_id
and r.org_id = p_org_id;
SELECT nvl(sum(nvl(sqa.target * it.ct,0)),0) target
from cn_srp_quota_assigns sqa,
cn_quotas q,
(select count(distinct interval_number) ct, it.interval_type_id
from cn_cal_per_int_types it, cn_period_statuses ps
where it.cal_period_id = ps.period_id
and it.org_id = ps.org_id
and ps.period_year = a_period_year
and ps.org_id = p_org_id
group by it.interval_type_id) it
where sqa.srp_plan_assign_id = p_srp_plan_assign_id
and sqa.quota_id = q.quota_id
and q.quota_group_code = p_quota_group_code
and q.credit_type_id = p_credit_type_id
and q.interval_type_id = it.interval_type_id
and q.org_id = p_org_id --and sqa.org_id = q.org_id
and sqa.org_id = p_org_id
and ((p_quota_id is not null and q.quota_id = p_quota_id)
OR (q.QUOTA_ID = q.QUOTA_ID AND p_quota_id is null));
SELECT nvl(cspq.rollover,0),
nvl(cspq.total_rollover,0)
FROM cn_srp_period_quotas cspq,
cn_quotas quota
WHERE cspq.period_id = p_period_id
AND cspq.srp_plan_assign_id = p_srp_plan_assign_id
AND cspq.quota_id = quota.quota_id
AND cspq.org_id = quota.org_id
AND quota.credit_type_id = p_credit_type_id
AND quota.quota_group_code = p_quota_group_code
AND cspq.org_id = p_org_id
and ((p_quota_id is not null and quota.quota_id = p_quota_id)
OR (QUOTA.QUOTA_ID = QUOTA.QUOTA_ID AND p_quota_id is null));
SELECT period_year into l_period_year FROM cn_period_statuses p
WHERE p.period_id=p_period_id and p.org_id = p_org_id;
select distinct quota_group_code from cn_quotas_all
where quota_group_code is not null
and quota_id > 0 and org_id=p_org_id;
SELECT assign.srp_plan_assign_id srp_plan_assign_id,
r.name role_name,
cp.name plan_name,
assign.start_date start_date,
assign.end_date end_date,
assign.salesrep_id salesrep_id,
nvl(inv_ptd.earnings,0) ptd_earnings,
nvl(inv_ytd.earnings,0) ytd_earnings
FROM cn_srp_plan_assigns assign,
cn_roles r,
cn_comp_plans cp,
(select spa.srp_plan_assign_id srp_plan_assign_id,
nvl(sum(balance2_dtd),0) earnings
from cn_srp_periods sp, cn_srp_plan_assigns spa, cn_quotas_all q
where sp.srp_plan_assign_id = spa.srp_plan_assign_id
and sp.salesrep_id = spa.salesrep_id
and sp.org_id = spa.org_id
and sp.org_id = q.org_id
and sp.quota_id = q.quota_id
and q.quota_group_code is not null
and sp.credit_type_id = c_credit_type_id
and sp.org_id = c_org_id
and period_id between c_period_start_pd and c_end_pd
group by spa.srp_plan_assign_id) inv_ptd ,
(select spa.srp_plan_assign_id srp_plan_assign_id,
nvl(sum(balance2_dtd),0) earnings
from cn_srp_periods sp, cn_srp_plan_assigns spa, cn_quotas_all q
where sp.srp_plan_assign_id = spa.srp_plan_assign_id
and sp.salesrep_id = spa.salesrep_id
and sp.org_id = spa.org_id
and sp.org_id = q.org_id
and sp.quota_id = q.quota_id
and q.quota_group_code is not null
and sp.credit_type_id = c_credit_type_id
and sp.org_id = c_org_id
and period_id between c_year_start_pd and c_end_pd
group by spa.srp_plan_assign_id) inv_ytd
WHERE --:b1 <= nvl(assign.end_date, :b2)
((assign.end_date IS not null AND assign.end_date >= c_year_start_date) OR assign.end_date IS null )
AND c_period_end_date >= assign.start_date
AND assign.srp_plan_assign_id = inv_ytd.srp_plan_assign_id(+)
AND assign.srp_plan_assign_id = inv_ptd.srp_plan_assign_id (+)
AND assign.role_id = r.role_id
AND assign.comp_plan_id = cp.comp_plan_id
AND assign.org_id = cp.org_id
and assign.org_id = c_org_id
AND assign.salesrep_id in
(SELECT DISTINCT S.SALESREP_ID
FROM JTF_RS_REP_MANAGERS RM,JTF_RS_GROUP_USAGES U, CN_SALESREPS S WHERE
RM.PARENT_RESOURCE_ID=(SELECT R.RESOURCE_ID
FROM JTF_RS_RESOURCE_EXTNS R WHERE R.USER_ID = FND_GLOBAL.USER_ID)
AND RM.RESOURCE_ID=S.RESOURCE_ID
AND RM.HIERARCHY_TYPE IN ('MGR_TO_REP', 'REP_TO_REP', 'MGR_TO_MGR')
AND U.USAGE=CN_SYSTEM_PARAMETERS.VALUE('CN_REPORTING_HIERARCHY',c_org_id)
AND RM.GROUP_ID = U.GROUP_ID AND SYSDATE >= RM.START_DATE_ACTIVE
AND (RM.END_DATE_ACTIVE IS NULL OR (RM.END_DATE_ACTIVE >= SYSDATE))
AND DENORM_LEVEL IS NOT NULL AND S.ORG_ID =c_org_id)
ORDER BY assign.start_date ;
select min(p2.start_date), min(p2.period_id)
into l_year_start_date, l_year_start_period
from cn_period_statuses p1, cn_period_statuses p2
where p1.period_id = p_period_id
and p1.period_year = p2.period_year
and p1.period_set_id = p2.period_set_id
and p1.period_type_id = p2.period_type_id
and p1.org_id = p2.org_id and p1.org_id =p_org_id;
select end_date into l_period_end_date from cn_period_statuses
where period_id = p_period_id and org_id =p_org_id;
SELECT assign.srp_plan_assign_id srp_plan_assign_id,
r.name role_name,
cp.name plan_name,
assign.start_date start_date,
assign.end_date end_date,
assign.salesrep_id salesrep_id,
nvl(inv.earnings,0) earnings
FROM cn_srp_plan_assigns assign,
cn_roles r,
cn_comp_plans cp,
(select spa.srp_plan_assign_id srp_plan_assign_id,
nvl(sum(balance2_dtd),0) earnings
from cn_srp_periods sp, cn_srp_plan_assigns spa, cn_quotas_all q
where sp.srp_plan_assign_id = spa.srp_plan_assign_id
and sp.salesrep_id = spa.salesrep_id
and spa.org_id = p_org_id--and sp.org_id = spa.org_id
and q.org_id = p_org_id --and sp.org_id = q.org_id
and sp.quota_id = q.quota_id
and q.quota_group_code is not null
and sp.credit_type_id = c_credit_type_id
and sp.org_id = c_org_id
and period_id between c_start_pd and c_end_pd
group by spa.srp_plan_assign_id) inv
WHERE --:b1 <= nvl(assign.end_date, :b2)
((assign.end_date IS not null AND assign.end_date >= c_year_start_date) OR assign.end_date IS null )
AND c_period_end_date >= assign.start_date
AND assign.srp_plan_assign_id = inv.srp_plan_assign_id(+)
AND assign.role_id = r.role_id
AND assign.comp_plan_id = cp.comp_plan_id
AND assign.org_id = cp.org_id
and assign.org_id = c_org_id
AND assign.salesrep_id in
(SELECT SALESREP_ID FROM CN_SALESREPS where org_id = c_org_id
AND ((c_analyst_id <> -99 AND assigned_to_user_id = c_analyst_id) OR c_analyst_id = -99))
ORDER BY assign.start_date ;
select min(p2.start_date), min(p2.period_id)
into l_year_start_date, l_year_start_period
from cn_period_statuses p1, cn_period_statuses p2
where p1.period_id = p_period_id
and p1.period_year = p2.period_year
and p1.period_set_id = p2.period_set_id
and p1.period_type_id = p2.period_type_id
and p1.org_id = p2.org_id and p1.org_id=p_org_id;
select end_date into l_period_end_date from cn_period_statuses
where period_id = p_period_id and org_id =p_org_id;
SELECT assign.srp_plan_assign_id
FROM cn_srp_plan_assigns assign,
cn_roles r,
cn_comp_plans cp
WHERE l_year_start_date <= nvl(assign.end_date, l_year_start_date)
AND l_period_end_date >= assign.start_date
AND assign.role_id = r.role_id
AND assign.comp_plan_id = cp.comp_plan_id
AND assign.org_id = cp.org_id
AND assign.salesrep_id = p_salesrep_id
and assign.org_id = p_org_id
ORDER BY assign.start_date;
select distinct quota_group_code from cn_quotas_all where quota_group_code is not null
and quota_id > 0 and org_id=p_org_id;
select min(p2.start_date), min(p2.period_id)
into l_year_start_date, l_year_start_period
from cn_period_statuses p1, cn_period_statuses p2
where p1.period_id = p_period_id
and p1.period_year = p2.period_year
and p1.period_set_id = p2.period_set_id
and p1.period_type_id = p2.period_type_id
and p1.org_id = p2.org_id
and p1.org_id = p_org_id;
select end_date into l_period_end_date from cn_period_statuses
where period_id = p_period_id and org_id = p_org_id;
SELECT assign.srp_plan_assign_id
FROM cn_srp_plan_assigns assign,
cn_roles r,
cn_comp_plans cp
WHERE c_year_start_date <= nvl(assign.end_date, c_year_start_date)
AND c_period_end_date >= assign.start_date
AND assign.role_id = r.role_id
AND assign.comp_plan_id = cp.comp_plan_id
AND assign.org_id = cp.org_id
and assign.org_id = c_org_id
AND assign.salesrep_id in (SELECT DISTINCT S.SALESREP_ID
FROM JTF_RS_REP_MANAGERS RM,JTF_RS_GROUP_USAGES U, CN_SALESREPS S WHERE
RM.PARENT_RESOURCE_ID=(SELECT R.RESOURCE_ID
FROM JTF_RS_RESOURCE_EXTNS R WHERE R.USER_ID = FND_GLOBAL.USER_ID)
AND RM.RESOURCE_ID=S.RESOURCE_ID
AND RM.HIERARCHY_TYPE IN ('MGR_TO_REP', 'REP_TO_REP', 'MGR_TO_MGR')
AND U.USAGE=CN_SYSTEM_PARAMETERS.VALUE('CN_REPORTING_HIERARCHY',c_org_id)
AND RM.GROUP_ID = U.GROUP_ID AND SYSDATE >= RM.START_DATE_ACTIVE
AND (RM.END_DATE_ACTIVE IS NULL OR (RM.END_DATE_ACTIVE >= SYSDATE))
AND DENORM_LEVEL IS NOT NULL AND S.ORG_ID = c_org_id
)
ORDER BY assign.start_date;
select
distinct quota_group_code
from cn_quotas_all
where quota_group_code is not null and quota_id > 0 and org_id=p_org_id;
select min(p2.start_date), min(p2.period_id)
into l_year_start_date, l_year_start_period
from cn_period_statuses p1, cn_period_statuses p2
where p1.period_id = p_period_id
and p1.period_year = p2.period_year
and p1.period_set_id = p2.period_set_id
and p1.period_type_id = p2.period_type_id
and p1.org_id = p2.org_id and p1.org_id=p_org_id;
select end_date into l_period_end_date from cn_period_statuses
where period_id = p_period_id and org_id = p_org_id;
SELECT assign.srp_plan_assign_id
FROM cn_srp_plan_assigns assign,
cn_roles r,
cn_comp_plans cp
WHERE c_year_start_date <= nvl(assign.end_date, c_year_start_date)
AND c_period_end_date >= assign.start_date
AND assign.role_id = r.role_id
AND assign.comp_plan_id = cp.comp_plan_id
AND assign.org_id = cp.org_id
and assign.org_id = c_org_id
AND assign.salesrep_id in
(SELECT SALESREP_ID FROM CN_SALESREPS where org_id = c_org_id
AND ((c_analyst_id <> -99 AND assigned_to_user_id = c_analyst_id) OR c_analyst_id = -99))
ORDER BY assign.start_date;
select distinct quota_group_code
from cn_quotas_all
where quota_group_code is not null
and quota_id > 0
and org_id = c_org_id;
select min(p2.start_date), min(p2.period_id)
into l_year_start_date, l_year_start_period
from cn_period_statuses p1, cn_period_statuses p2
where p1.period_id = p_period_id
and p1.period_year = p2.period_year
and p1.period_set_id = p2.period_set_id
and p1.period_type_id = p2.period_type_id
and p1.org_id = p2.org_id and p1.org_id=p_org_id;
select end_date into l_period_end_date from cn_period_statuses
where period_id = p_period_id and org_id = p_org_id;
SELECT DISTINCT
srp.srp_plan_assign_id srp_plan_assign_id,
q.quota_id quota_id,
r.role_id role_id,
cp.comp_plan_id comp_plan_id,
assign.start_date start_date,
assign.end_date end_date,
q.quota_group_code quota_group_code
FROM cn_srp_periods srp,
cn_srp_plan_assigns assign,
cn_roles r,
cn_comp_plans cp,
cn_payment_worksheets w,
cn_quotas_all q
WHERE assign.srp_plan_assign_id(+) = srp.srp_plan_assign_id
AND srp.period_id = l_period_id
AND assign.role_id = r.role_id(+)
AND assign.comp_plan_id = cp.comp_plan_id(+)
AND assign.org_id = cp.org_id(+)
AND q.quota_id = w.quota_id
AND q.org_id = w.org_id
AND w.payrun_Id = l_payrun_id
AND w.salesrep_id = l_salesrep_id
AND q.quota_id = srp.quota_id
AND q.org_id = srp.org_id
AND srp.salesrep_id = l_salesrep_id
AND srp.org_id = p_org_id
AND srp.credit_type_id = -1000
AND w.quota_id <> -1000
ORDER BY assign.start_date;
select pay_period_id into x_period_id from cn_payruns where payrun_id=p_payrun_id;
SELECT CN_CONVERSION_TYPE FROM CN_REPOSITORIES WHERE ORG_ID=a_org_id;