1 PACKAGE BODY CN_GET_COMM_SUMM_DATA AS
2 /*$Header: cnvcommb.pls 115.5 2001/01/23 13:27:46 pkm ship $*/
3
4 -- Main query. This will be a main cursor in the api
5 -- Each returned record represents a row in the report
6
7 --{{{ this is for where a user ID is specified
8 CURSOR main_cur(p_period_id in number) IS
9 -- for sales online users
10 SELECT rep.name salesrep_name,
11 rep.employee_number salesrep_no,
12 rep.cost_center home_cc,
13 rep.charge_to_cost_center charge_cc,
14 rep.currency_code currency,
15 u.user_name analyst_name,
16 role.name role_name,
17 plan.name comp_plan_name,
18 assign.srp_plan_assign_id srp_plan_assign_id
19 FROM cn_srp_plan_assigns assign,
20 fnd_user u,
21 cn_salesreps rep,
22 cn_comp_plans plan,
23 cn_roles role,
24 cn_period_statuses ps,
25 jtf_rs_resource_extns re
26 WHERE re.user_id = fnd_global.user_id
27 AND re.resource_id = rep.resource_id
28 AND rep.assigned_to_user_id = u.user_id (+)
29 AND assign.salesrep_id = rep.salesrep_id
30 AND ps.period_id = p_period_id
31 AND ps.start_date <= nvl(assign.end_date, ps.start_date)
32 AND ps.end_date >= assign.start_date
33 AND plan.comp_plan_id = assign.comp_plan_id
34 AND assign.role_id = role.role_id
35 ORDER BY 1;
36 --}}}
37
38 --{{{ for SFP users selecting a specific analyst ID
39 CURSOR main_cur2(p_user_id in number, p_period_id in number) IS
40 SELECT rep.name salesrep_name,
41 rep.employee_number salesrep_no,
42 rep.cost_center home_cc,
43 rep.charge_to_cost_center charge_cc,
44 rep.currency_code currency,
45 u.user_name analyst_name,
46 role.name role_name,
47 plan.name comp_plan_name,
48 assign.srp_plan_assign_id srp_plan_assign_id
49 FROM cn_srp_plan_assigns assign,
50 fnd_user u,
51 cn_salesreps rep,
52 cn_comp_plans plan,
53 cn_roles role,
54 cn_period_statuses ps
55 WHERE u.user_id = p_user_id
56 AND rep.assigned_to_user_id = u.user_id
57 AND assign.salesrep_id = rep.salesrep_id
58 AND ps.period_id = p_period_id
59 AND ps.start_date <= nvl(assign.end_date, ps.start_date)
60 AND ps.end_date >= assign.start_date
61 AND plan.comp_plan_id = assign.comp_plan_id
62 AND assign.role_id = role.role_id
63 ORDER BY 1;
64 --}}}
65
66 --{{{ for SFP users not selecting a specific analyst ID
67 CURSOR main_cur3(p_period_id in number) IS
68 SELECT rep.name salesrep_name,
69 rep.employee_number salesrep_no,
70 rep.cost_center home_cc,
71 rep.charge_to_cost_center charge_cc,
72 rep.currency_code currency,
73 u.user_name analyst_name,
74 role.name role_name,
75 plan.name comp_plan_name,
76 assign.srp_plan_assign_id srp_plan_assign_id
77 FROM cn_srp_plan_assigns assign,
78 fnd_user u,
79 cn_salesreps rep,
80 cn_comp_plans plan,
81 cn_roles role,
82 cn_period_statuses ps
83 WHERE rep.assigned_to_user_id = u.user_id (+)
84 AND assign.salesrep_id = rep.salesrep_id
85 AND ps.period_id = p_period_id
86 AND ps.start_date <= nvl(assign.end_date, ps.start_date)
87 AND ps.end_date >= assign.start_date
88 AND plan.comp_plan_id = assign.comp_plan_id
89 AND assign.role_id = role.role_id
90 ORDER BY 1;
91 --}}}
92
93 --{{{ to populate the record type from the cursor and get balances
94 FUNCTION query_row(main IN OUT main_cur%rowtype,
95 p_period_id IN NUMBER,
96 p_credit_type_id IN NUMBER) RETURN comm_summ_rec_type IS
97
98 CURSOR get_balances
99 (l_srp_plan_assign_id IN NUMBER) IS
100 select nvl(sum((nvl(balance2_bbd, 0) - nvl(balance2_bbc, 0)) +
101 (nvl(balance10_bbd,0) - nvl(balance10_bbc,0)) +
102 (nvl(balance28_bbd,0) - nvl(balance28_bbc,0))),0) begin_balance,
103 nvl(sum((nvl(balance2_dtd, 0) - nvl(balance2_ctd, 0)) +
104 (nvl(balance10_dtd,0) - nvl(balance10_ctd,0))),0) earnminuspay,
105 nvl(sum((nvl(balance28_dtd,0) - nvl(balance28_ctd,0))),0) draw
106 from cn_srp_periods p, cn_srp_plan_assigns spa
107 where spa.srp_plan_assign_id = l_srp_plan_assign_id
108 and p.salesrep_id = spa.salesrep_id
109 and p.credit_type_id = p_credit_type_id
110 and p.role_id = spa.role_id
111 and p.period_id = p_period_id;
112
113 l_rec comm_summ_rec_type;
114 BEGIN
115 -- general info
116 l_rec.name := main.salesrep_name;
117 l_rec.emp_num := main.salesrep_no;
118 l_rec.cost_center := main.home_cc;
119 l_rec.charge_to_cost_center := main.charge_cc;
120 l_rec.analyst_name := main.analyst_name;
121 l_rec.role_name := main.role_name;
122 l_rec.plan_name := main.comp_plan_name;
123 l_rec.srp_plan_assign_id := main.srp_plan_assign_id;
124
125 -- balances
126 for c in get_balances(main.srp_plan_assign_id) loop
127 l_rec.begin_balance := c.begin_balance;
128 l_rec.draw := c.draw;
129 l_rec.net_due := c.begin_balance + c.earnminuspay + c.draw;
130 end loop;
131
132 RETURN l_rec;
133 END query_row;
134 --}}}
135
136 --{{{ to populate details of each record
137 PROCEDURE Get_Pe_Info
138 (p_srp_plan_assign_id IN NUMBER,
139 p_period_id IN NUMBER,
140 p_credit_type_id IN NUMBER,
141 x_ytd_pe_info OUT pe_info_tbl_type,
142 x_ptd_pe_info OUT pe_info_tbl_type,
143 x_ytd_total_earnings OUT NUMBER,
144 x_ptd_total_earnings OUT NUMBER) IS
145
146 l_count NUMBER := 0;
147
148 -- Group Code subquery
149 CURSOR group_code_cur IS
150 SELECT distinct quota_group_code
151 FROM cn_quotas where quota_group_code is not null;
152
153 -- Annual Subquery
154 -- To get
155 -- 1) Annual Quota Target and percent
156 CURSOR annual_quota_cur
157 (l_srp_plan_assign_id IN NUMBER,
158 l_quota_group_code IN VARCHAR) IS
159 SELECT nvl(sum(sqa.target),0) target
160 from cn_srp_quota_assigns sqa,
161 cn_quotas q
162 where sqa.srp_plan_assign_id = l_srp_plan_assign_id
163 and sqa.quota_id = q.quota_id
164 and q.quota_group_code = l_quota_group_code;
165
166 -- YTD Subquery for ytd quota target
167 -- To get
168 -- 1) YTD Quota Target (ITD Quota Target)
169 CURSOR ytd_quota_target_cur
170 (l_srp_plan_assign_id IN NUMBER,
171 l_quota_group_code IN VARCHAR) IS
172 select nvl(sum(itd_target),0) itd_target
173 from cn_srp_period_quotas spq, cn_quotas q
174 where srp_plan_assign_id = l_srp_plan_assign_id
175 and period_id = p_period_id
176 and q.quota_id = spq.quota_id
177 and q.quota_group_code = l_quota_group_code;
178
179 -- YTD Subquery
180 -- To get
181 -- 1) YTD Credit (ITD credit)
182 -- 2) YTD Earnings (ITD earnings)
183 CURSOR ytd_cur(l_quota_group_code IN VARCHAR,
184 l_srp_plan_assign_id IN NUMBER) IS
185 SELECT sum(nvl(cspq.perf_achieved_itd,0)) ytd_credit,
186 sum(nvl(cspq.commission_payed_itd,0)) ytd_earnings
187 FROM cn_srp_period_quotas cspq,
188 cn_quotas quota
189 WHERE cspq.period_id = p_period_id
190 AND cspq.srp_plan_assign_id = l_srp_plan_assign_id
191 AND cspq.quota_id = quota.quota_id
192 AND nvl(quota.quota_group_code, FND_API.G_MISS_CHAR) =
193 nvl(l_quota_group_code , FND_API.G_MISS_CHAR)
194 AND quota.credit_type_id = p_credit_type_id
195 GROUP BY cspq.salesrep_id,
196 cspq.srp_plan_assign_id,
197 cspq.period_id;
198
199 -- PTD Subquery
200 -- To get
201 -- 1) PTD Target
202 -- 2) PTD Credit
203 -- 3) PTD Earnings
204 CURSOR ptd_cur(l_quota_group_code IN VARCHAR,
205 l_srp_plan_assign_id IN NUMBER) IS
206 SELECT sum(nvl(cspq.target_amount,0)) ptd_target,
207 sum(nvl(cspq.perf_achieved_ptd,0)) ptd_credit,
208 sum(nvl(cspq.commission_payed_ptd,0)) ptd_earnings
209 FROM cn_quotas quota,
210 cn_srp_period_quotas cspq
211 WHERE cspq.period_id = p_period_id
212 AND cspq.srp_plan_assign_id = l_srp_plan_assign_id
213 AND cspq.quota_id = quota.quota_id
214 AND quota.quota_group_code = l_quota_group_code
215 AND quota.credit_type_id = p_credit_type_id
216 GROUP BY cspq.salesrep_id,
217 cspq.srp_plan_assign_id,
218 cspq.period_id;
219
220 BEGIN
221 -- group_code_cur loop
222 x_ytd_total_earnings := 0;
223 x_ptd_total_earnings := 0;
224 FOR group_code IN group_code_cur LOOP
225 x_ytd_pe_info(l_count).quota_group_code := group_code.quota_group_code;
226 x_ptd_pe_info(l_count).quota_group_code := group_code.quota_group_code;
227 -- ytd_cur loop
228 FOR ytd IN ytd_cur(group_code.quota_group_code,
229 p_srp_plan_assign_id) LOOP
230
231 --first the subquery to get the ytd target
232 open ytd_quota_target_cur(p_srp_plan_assign_id,
233 group_code.quota_group_code);
234 fetch ytd_quota_target_cur into x_ytd_pe_info(l_count).target;
235 close ytd_quota_target_cur;
236
237 x_ytd_pe_info(l_count).credit := ytd.ytd_credit;
238 x_ytd_pe_info(l_count).earnings := ytd.ytd_earnings;
239 x_ytd_total_earnings := x_ytd_total_earnings + ytd.ytd_earnings;
240 END LOOP; -- end of ytd_cur loop
241
242 -- annual_quota_cur loop
243 open annual_quota_cur(p_srp_plan_assign_id,
244 group_code.quota_group_code);
245 fetch annual_quota_cur into x_ytd_pe_info(l_count).annual_quota;
246 close annual_quota_cur;
247
248 IF x_ytd_pe_info(l_count).annual_quota = 0 THEN
249 x_ytd_pe_info(l_count).pct_annual_quota := 0;
250 ELSE
251 x_ytd_pe_info(l_count).pct_annual_quota :=
252 (x_ytd_pe_info(l_count).credit /
253 x_ytd_pe_info(l_count).annual_quota) * 100;
254 END IF;
255
256 -- ptd_cur loop
257 FOR ptd IN ptd_cur(group_code.quota_group_code,
258 p_srp_plan_assign_id) LOOP
259
260 x_ptd_pe_info(l_count).target := ptd.ptd_target;
261 x_ptd_pe_info(l_count).credit := ptd.ptd_credit;
262 x_ptd_pe_info(l_count).earnings := ptd.ptd_earnings;
263 x_ptd_total_earnings := x_ptd_total_earnings + ptd.ptd_earnings;
264 END LOOP; -- end of ptd_cur loop
265 l_count := l_count + 1;
266 END LOOP; -- end of quota_group_code cur loop
267 END Get_Pe_Info;
268 --}}}
269
270 --{{{ to get set of all quota group codes
271 PROCEDURE Get_Group_Codes
272 (x_result_tbl OUT pe_info_tbl_type) IS
273
274 CURSOR group_codes IS
275 select distinct quota_group_code from cn_quotas
276 where quota_group_code is not null;
277 l_count NUMBER := 0;
278 BEGIN
279 FOR c in group_codes LOOP
280 x_result_tbl(l_count).quota_group_code := c.quota_group_code;
281 l_count := l_count + 1;
282 END LOOP;
283 END Get_Group_Codes;
284 --}}}
285
286 --{{{ to populate master part of records
287 PROCEDURE Get_Quota_Summary
288 (p_first IN NUMBER,
289 p_last IN NUMBER,
290 p_period_id IN NUMBER,
291 p_user_id IN NUMBER,
292 p_credit_type_id IN NUMBER,
293 x_total_rows OUT NUMBER,
294 x_result_tbl OUT comm_summ_tbl_type) IS
295
296 l_min_period_id NUMBER;
297 l_max_period_id NUMBER;
298 l_count NUMBER := 0;
299 l_rec comm_summ_rec_type;
300
301 BEGIN
302 -- Get the min and max date of the period year which the
303 -- given P_PERIOD_ID is in.
304 SELECT min(period_id), max(period_id)
305 INTO l_min_period_id, l_max_period_id
306 FROM cn_acc_period_statuses_v
307 WHERE period_year = mod(floor(p_period_id/1000),10000);
308
309 x_total_rows := 0;
310
311 if (fnd_global.resp_appl_id <> 283) then
312 -- we are in oracle sales, use main_cur
313 FOR main IN main_cur(p_period_id) LOOP
314 x_total_rows := x_total_rows + 1;
315 if x_total_rows between p_first and p_last then
316 l_rec := query_row(main, p_period_id, p_credit_type_id);
317 x_result_tbl(l_count) := l_rec;
318 l_count := l_count + 1;
319 END IF;
320 END LOOP; -- end of main loop;
321 elsif (p_user_id <> -99) then
322 -- we specified an analyst ID
323 FOR main IN main_cur2(p_user_id, p_period_id) LOOP
324 x_total_rows := x_total_rows + 1;
325 if x_total_rows between p_first and p_last then
326 l_rec := query_row(main, p_period_id, p_credit_type_id);
327 x_result_tbl(l_count) := l_rec;
328 l_count := l_count + 1;
329 END IF;
330 END LOOP; -- end of main LOOP;
331 else
332 -- unspecified analyst ID
333 FOR main IN main_cur3(p_period_id) LOOP
334 x_total_rows := x_total_rows + 1;
335 if x_total_rows between p_first and p_last then
336 l_rec := query_row(main, p_period_id, p_credit_type_id);
337 x_result_tbl(l_count) := l_rec;
338 l_count := l_count + 1;
339 END IF;
340 END LOOP; -- end of main LOOP;
341 end if;
342
343 END Get_Quota_Summary;
344 --}}}
345
346 END CN_GET_COMM_SUMM_DATA;
[Home] [Help]