[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT ccf.name formula_name
, ccf.calc_formula_id formula_id
, ccf.formula_status formula_status
, ccf.org_id org_id
FROM cn_quotas cq
, cn_calc_formulas ccf
WHERE cq.quota_id = p_quota_id
AND ccf.calc_formula_id = cq.calc_formula_id;
SELECT count(*)
FROM user_objects
WHERE object_name = 'CN_FORMULA_'||abs(p_formula_id)||'_'||abs(p_org_id)||'_PKG'
AND (object_type = 'PACKAGE' OR object_type = 'PACKAGE BODY');
SELECT cspa.srp_plan_assign_id srp_plan_assign_id,
cspa.salesrep_id salesrep_id,
cspa.comp_plan_id comp_plan_id,
cspa.start_date start_date,
nvl(cspa.end_date,p_date) end_date
FROM cn_srp_plan_assigns cspa,
cn_srp_quota_assigns csqa
WHERE cspa.salesrep_id = p_salesrep_id
AND p_date BETWEEN cspa.start_date AND nvl(cspa.end_date,p_date)
AND csqa.quota_id = p_quota_id
AND csqa.srp_plan_assign_id = cspa.srp_plan_assign_id;
SELECT count(*) valid_compplan_cnt
FROM cn_comp_plans
WHERE comp_plan_id = p_comp_plan_id
AND status_code = 'INCOMPLETE';
SELECT
sqa.quota_id
, q.interval_type_id interval_type_id
, q.credit_type_id credit_type_id
, cit.name interval_type_name
, cci.name credit_type_name
, q.name quota_name
, qr.revenue_class_id
from cn_srp_plan_assigns spa,
cn_srp_quota_assigns sqa,
cn_quotas q,
cn_quota_rules qr,
cn_dim_hierarchies dh,
jtf_rs_salesreps jrs,
jtf_rs_group_members mem,
jtf_rs_role_relations rr,
cn_interval_types cit,
cn_credit_types cci,
cn_repositories cr
where spa.salesrep_id = p_salesrep_id
and spa.start_date <= p_date
and nvl(spa.end_date, p_date) >= p_date
and jrs.salesrep_id = p_salesrep_id
and nvl(jrs.org_id, -9999) = nvl(spa.org_id, -9999)
and mem.resource_id = jrs.resource_id
and nvl(mem.delete_flag, 'N') <> 'Y'
and rr.role_id = spa.role_id
and rr.role_resource_id = mem.group_member_id
and rr.role_resource_type = 'RS_GROUP_MEMBER'
and nvl(rr.delete_flag, 'N') <> 'Y'
and exists (select 1 from cn_comp_plans where status_code = 'COMPLETE' AND comp_plan_id = spa.comp_plan_id)
and rr.start_date_active <= p_date
and nvl(rr.end_date_active, p_date) >= p_date
and rr.start_date_active <= nvl(spa.end_date, p_date)
and nvl(rr.end_date_active, nvl(spa.end_date, p_date)) >= spa.start_date
and sqa.srp_plan_assign_id = spa.srp_plan_assign_id
and q.quota_id = sqa.quota_id
and q.start_date <= p_date
and nvl(q.end_date, p_date) >= p_date
and qr.quota_id = sqa.quota_id
and dh.header_dim_hierarchy_id = cr.rev_class_hierarchy_id
and dh.start_date <= least(nvl(spa.end_date, p_date), nvl(q.end_date, p_date))
and nvl(dh.end_date, p_date) >= greatest(spa.start_date, q.start_date)
and exists (select 1 from cn_dim_explosion de
where de.dim_hierarchy_id = dh.dim_hierarchy_id
and de.ancestor_external_id = qr.revenue_class_id
and de.value_external_id = p_revenueclass_id)
AND cit.interval_type_id = q.interval_type_id
AND cci.credit_type_id = q.credit_type_id
order by greatest(dh.start_date, spa.start_date, q.start_date, rr.start_date_active, p_date),
least(nvl(dh.end_date, p_date),
nvl(spa.end_date, p_date),
nvl(q.end_date, p_date),
nvl(rr.end_date_active, p_date), p_date);
SELECT nvl(cspq.input_achieved_itd,0) input_achieved_itd
, nvl(cspq.itd_TARGET,0) target_itd
, nvl(cspq.target_amount,0) target_amount
FROM cn_srp_period_quotas cspq
WHERE cspq.srp_plan_assign_id = p_srp_plan_assign_id
AND cspq.salesrep_id = p_salesrep_id
AND cspq.quota_id = p_quota_id
AND cspq.period_id = p_period_id;
UPDATE cn_proj_compensation_gtt
SET PE_NAME = l_quotadetails.quota_name,
PROJ_COMP = l_tot_proj_comm_amt,
PE_QUOTA = l_tot_target_amt,
PE_ACHIEVED = l_quota_achievement,
PE_CREDIT = l_tot_inp_ach_itd+l_tot_proj_comm_amt,
PE_INTERVAL = l_quotadetails.interval_type_id,
CALC_STATUS = l_return_status
WHERE LINE_ID = p_proj_comp_rec.line_id;
SELECT cpt.period_type period_type
, cr.set_of_books_id set_of_books_id
, cr.period_set_id period_set_id
, cr.period_type_id period_type_id
, cr.income_planner_disclaimer income_planner_disclaimer
FROM cn_period_types cpt,cn_repositories cr
WHERE cpt.period_type_id = cr.period_type_id
AND cpt.org_id = cr.org_id
AND cr.org_id = p_org_id;
SELECT *
FROM
cn_proj_compensation_gtt
WHERE salesrep_id IS NOT NULL
AND period_id IS NOT NULL
AND ruleset_id IS NOT NULL;
SELECT line_id
FROM cn_proj_compensation_gtt
WHERE salesrep_id IS NULL;
SELECT line_id
FROM cn_proj_compensation_gtt
WHERE period_id IS NULL;
SELECT line_id
FROM cn_proj_compensation_gtt
WHERE ruleset_id IS NULL;
UPDATE cn_proj_compensation_gtt cpcg
SET line_id = cn_proj_compensation_gtt_s.NEXTVAL
, cpcg.period_id =
(
SELECT cps.period_id period_id
FROM cn_period_statuses cps
WHERE cps.period_set_id = l_repositories.period_set_id
AND cps.period_type_id = l_repositories.period_type_id
AND cpcg.calc_date between cps.start_date and cps.end_Date
AND cps.period_status = 'O'
AND cps.org_id = g_cached_org_id
)
, cpcg.salesrep_id = (
SELECT salesrep_id
FROM cn_salesreps cs
WHERE cs.resource_id = cpcg.resource_id
AND cs.org_id = g_cached_org_id
)
, cpcg.ruleset_id = (
SELECT ruleset_id
FROM cn_rulesets cr
WHERE cpcg.calc_date BETWEEN cr.start_date AND nvl(cr.end_date,cpcg.calc_date)
AND cr.module_type = 'PECLS'
and cr.org_id = g_cached_org_id
)
, cpcg.pe_name = FND_API.G_MISS_CHAR
, cpcg.proj_comp = 0
, cpcg.pe_quota = 0
, cpcg.pe_achieved = 0
, cpcg.pe_credit = 0
, cpcg.pe_interval = FND_API.G_MISS_NUM
, cpcg.calc_status = FND_API.G_RET_STS_ERROR;