[Home] [Help]
PACKAGE BODY: APPS.CN_GET_COMM_SUMM_DATA_PVT
Source
1 PACKAGE BODY CN_GET_COMM_SUMM_DATA_PVT AS
2 /*$Header: cnvcommb.pls 120.9 2007/12/05 11:09:20 kmnagara ship $*/
3
4 -- gets all salesreps under given analyst
5 PROCEDURE Get_Salesrep_List
6 (p_first IN NUMBER,
7 p_last IN NUMBER,
8 p_period_id IN NUMBER,
9 p_analyst_id IN NUMBER,
10 p_org_id IN NUMBER,
11 x_total_rows OUT NOCOPY NUMBER,
12 x_salesrep_tbl OUT NOCOPY salesrep_tbl_type) IS
13
14 -- Main query. This will be a main cursor in the api
15 -- Each returned record represents a row in the report
16 TYPE rc IS ref cursor;
17 main_cursor rc;
18 l_count NUMBER := 0;
19 l_user_id NUMBER;
20 l_salesrep_id NUMBER;
21 l_curr_salesrep_id NUMBER;
22 l_name VARCHAR2(240);
23 l_year NUMBER;
24 l_year_start_date DATE;
25 l_period_end_date DATE;
26 l_resp_key VARCHAR2(30);
27 l_resource_id NUMBER;
28 l_groupquery_result number;
29 l_rolequery_result number;
30
31 l_query varchar2(4000) :=
32 'SELECT distinct s.name, s.salesrep_id FROM jtf_rs_resource_extns re,
33 cn_salesreps s,cn_srp_plan_assigns assign WHERE re.resource_id = s.resource_id
34 and s.org_id = assign.org_id and re.category <> ''TBH''
35 and s.salesrep_id > 0 and s.salesrep_id = assign.salesrep_id
36 and :b1 <= nvl(assign.end_date, :b2)
37 and :b3 >= assign.start_date and s.org_id = :b4';
38
39 cursor get_year is
40 select period_year from cn_period_statuses
41 where period_id = p_period_id and org_id=p_org_id;
42
43 cursor get_salesrep (a_user_id number,a_org_id number) is
44 select s.salesrep_id
45 from cn_rs_salesreps s, jtf_rs_resource_extns re
46 where re.user_id = a_user_id and s.resource_id = re.resource_id
47 and s.org_id = a_org_id;
48
49 BEGIN
50 x_total_rows := 0;
51
52 -- 3 scenarios:
53 -- 1) from OSO - only get info for logged in rep
54 -- 2) from OSC, analyst specified - get reps under that analyst only
55 -- *** OPEN ISSUE - do we recurse hierarchically or do we just get
56 -- the reps assigned directly to the analyst? ***
57 -- 3) from OSC, unspecified analyst - get all reps
58 -- 4) from OSC, and salesrep - only get info for logged in rep
59
60 l_user_id := FND_GLOBAL.USER_ID;
61 l_curr_salesrep_id := NULL;
62 OPEN get_salesrep(l_user_id,p_org_id);
63 FETCH get_salesrep INTO l_curr_salesrep_id;
64 CLOSE get_salesrep;
65
66 select je.resource_id into l_resource_id from jtf_rs_resource_extns je where je.user_id =l_user_id;
67
68 SELECT count(1) into l_groupquery_result FROM JTF_RS_GROUP_MBR_ROLE_VL GPM, JTF_RS_GROUP_USAGES GPU
69 WHERE GPM.GROUP_ID=GPU.GROUP_ID and GPU.usage='COMP_PAYMENT'
70 AND NVL(GPM.END_DATE_ACTIVE,TO_DATE('01/01/9999','DD/MM/RRRR')) >= SYSDATE
71 AND GPM.RESOURCE_ID = l_resource_id;
72
73 select count(1) into l_rolequery_result from JTF_RS_ROLES_B ROLEB , JTF_RS_ROLE_RELATIONS ROLER
74 WHERE ROLER.DELETE_FLAG ='N' AND nvl(ROLER.END_DATE_ACTIVE,TO_DATE('01/01/9999','DD/MM/RRRR')) >= SYSDATE
75 AND ROLER.ROLE_ID= ROLEB.ROLE_ID AND ROLEB.ROLE_TYPE_CODE='SALES_COMP_PAYMENT_ANALIST'
76 AND ROLER.ROLE_RESOURCE_ID = l_resource_id;
77
78 if(p_analyst_id <> -99)
79 then
80 l_query := l_query || ' AND s.assigned_to_user_id = :b5 ' ;
81 l_user_id := p_analyst_id;
82 elsif
83 ( (l_curr_salesrep_id IS NULL) OR(l_groupquery_result >0) OR (l_rolequery_result >0))
84 then
85 l_query := l_query || ' AND :b5 = 1';
86 l_user_id :=1;
87 else
88 l_query := l_query || ' AND re.user_id = :b5 ' ;
89 l_user_id := fnd_global.user_id;
90 end if;
91
92
93 /*
94 -- Added new or condition for enh#2648479
95 if (fnd_global.resp_appl_id <> 283) or (l_curr_salesrep_id IS NOT NULL) then
96 -- for OSO, only select user's salesrep
97 l_query := l_query || ' AND re.user_id = :b4 ' ;
98 l_user_id := fnd_global.user_id;
99 elsif (p_analyst_id <> -99) then
100 -- we specified an analyst ID
101 l_query := l_query || ' AND s.assigned_to_user_id = :b4 ' ;
102 l_user_id := p_analyst_id;
103 else
104 -- unspecified analyst ID, give default condition that is always true
105 l_query := l_query || ' AND :b4 = 1 ' ;
106 l_user_id := 1;
107 end if;
108 */
109
110 -- get fiscal year
111 open get_year;
112 fetch get_year into l_year;
113 close get_year;
114
115 -- get start date of year and end date of period
116 /**
117 * Fix made for bug 4368747
118 */
119
120 /*
121 select start_date into l_year_start_date from cn_period_statuses
122 where period_id = p_period_id - mod(p_period_id, 1000) + 1;
123 */
124
125 select min(start_date) into l_year_start_date from cn_period_statuses cps,cn_repositories cr
126 where cps.period_year=l_year and cr.period_set_id=cps.period_set_id
127 and cr.period_type_id=cps.period_type_id and cr.org_id=cps.org_id
128 and cr.org_id=p_org_id;
129 /**
130 * End of Fix made for bug 4368747
131 */
132 select end_date into l_period_end_date from cn_period_statuses
133 where period_id = p_period_id and org_id=p_org_id;
134
135 open main_cursor for l_query using l_year_start_date, l_year_start_date,
136 l_period_end_date, p_org_id, l_user_id;
137 loop
138 fetch main_cursor into l_name, l_salesrep_id;
139 exit when main_cursor%notfound;
140
141 x_total_rows := x_total_rows + 1;
142 if x_total_rows between p_first and p_last then
143 x_salesrep_tbl(l_count) := l_salesrep_id;
144 l_count := l_count + 1;
145 end if;
146 end loop;
147 close main_cursor;
148 END Get_Salesrep_List;
149
150 -- gets salesrep info
151 PROCEDURE Get_Salesrep_Info
152 (p_salesrep_id IN NUMBER,
153 p_org_id IN NUMBER,
154 x_name OUT NOCOPY VARCHAR2,
155 x_emp_num OUT NOCOPY VARCHAR2,
156 x_cost_center OUT NOCOPY VARCHAR2,
157 x_charge_to_cost_center OUT NOCOPY VARCHAR2,
158 x_analyst_name OUT NOCOPY VARCHAR2) IS
159
160 cursor get_info is
161 select name, employee_number, cost_center,
162 charge_to_cost_center, assigned_to_user_name
163 from cn_salesreps
164 where salesrep_id = p_salesrep_id and org_id=p_org_id;
165
166 BEGIN
167 open get_info;
168 fetch get_info into x_name, x_emp_num, x_cost_center,
169 x_charge_to_cost_center, x_analyst_name;
170 close get_info;
171 END;
172
173 PROCEDURE Get_Salesrep_Details
174 (p_salesrep_id in number,
175 p_org_id in number,
176 x_result_tbl out NOCOPY salesrep_info_tbl_type) IS
177 x_name VARCHAR2(360);
178 x_emp_num VARCHAR2(30);
179 x_cost_center VARCHAR2(30);
180 x_charge_to_cost_center VARCHAR2(30);
181 x_analyst_name VARCHAR2(100);
182 begin
183 get_salesrep_info(p_salesrep_id,p_org_id,x_name,x_emp_num,
184 x_cost_center,x_charge_to_cost_center,x_analyst_name);
185 x_result_tbl(1).x_name := x_name;
186 x_result_tbl(1).x_emp_num := x_emp_num;
187 x_result_tbl(1).x_cost_center := x_cost_center;
188 x_result_tbl(1).x_charge_to_cost_center := x_charge_to_cost_center;
189 x_result_tbl(1).x_analyst_name := x_analyst_name;
190 x_result_tbl(1).x_salesrep_id := p_salesrep_id;
191 end Get_Salesrep_Details;
192
193
194 PROCEDURE Get_Manager_Details
195 (p_org_id in NUMBER,
196 x_result_tbl out NOCOPY salesrep_info_tbl_type) IS
197 x_name VARCHAR2(360);
198 x_emp_num VARCHAR2(30);
199 x_cost_center VARCHAR2(30);
200 x_charge_to_cost_center VARCHAR2(30);
201 x_analyst_name VARCHAR2(100);
202 l_count NUMBER :=0;
203 cursor cur_salesreps
204 is
205 SELECT DISTINCT S.SALESREP_ID,S.ORG_ID
206 FROM JTF_RS_REP_MANAGERS RM,JTF_RS_GROUP_USAGES U, CN_SALESREPS S WHERE
207 RM.PARENT_RESOURCE_ID=(SELECT R.RESOURCE_ID
208 FROM JTF_RS_RESOURCE_EXTNS R WHERE R.USER_ID = FND_GLOBAL.USER_ID)
209 AND RM.RESOURCE_ID=S.RESOURCE_ID
210 AND RM.HIERARCHY_TYPE IN ('MGR_TO_REP', 'REP_TO_REP', 'MGR_TO_MGR')
211 AND U.USAGE=CN_SYSTEM_PARAMETERS.VALUE('CN_REPORTING_HIERARCHY',p_org_id)
212 AND RM.GROUP_ID = U.GROUP_ID AND SYSDATE >= RM.START_DATE_ACTIVE
213 AND (RM.END_DATE_ACTIVE IS NULL OR (RM.END_DATE_ACTIVE >= SYSDATE))
214 AND DENORM_LEVEL IS NOT NULL AND S.org_id =p_org_id;
215
216 begin
217 for s in cur_salesreps
218 loop
219 get_salesrep_info(s.salesrep_id,s.org_id,x_name,x_emp_num,
220 x_cost_center,x_charge_to_cost_center,x_analyst_name);
221 l_count := l_count + 1;
222 x_result_tbl(l_count).x_name := x_name;
223 x_result_tbl(l_count).x_emp_num := x_emp_num;
224 x_result_tbl(l_count).x_cost_center := x_cost_center;
225 x_result_tbl(l_count).x_charge_to_cost_center := x_charge_to_cost_center;
226 x_result_tbl(l_count).x_analyst_name := x_analyst_name;
227 x_result_tbl(l_count).x_salesrep_id := s.salesrep_id;
228 end loop;
229 end Get_Manager_Details;
230
231 PROCEDURE Get_Analyst_Details
232 (
233 p_org_id in number,
234 p_analyst_id in number,
235 x_result_tbl out nocopy salesrep_info_tbl_type) IS
236
237 l_count NUMBER :=0;
238
239 cursor cur_salesreps1(c_org_id in number,
240 c_analyst_id in number) is
241 select salesrep_id,
242 org_id,
243 name,
244 employee_number,
245 cost_center,
246 charge_to_cost_center,
247 assigned_to_user_name
248 from cn_salesreps
249 where org_id = c_org_id
250 AND assigned_to_user_id = c_analyst_id;
251
252 cursor cur_salesreps2(c_org_id in number) is
253 select salesrep_id,
254 org_id,
255 name,
256 employee_number,
257 cost_center,
258 charge_to_cost_center,
259 assigned_to_user_name
260 from cn_salesreps
261 where org_id = c_org_id;
262
263 TYPE cur_salesreps1_type IS TABLE OF cur_salesreps1%ROWTYPE;
264 cur_salesreps1_tbl cur_salesreps1_type;
265 TYPE cur_salesreps2_type IS TABLE OF cur_salesreps2%ROWTYPE;
266 cur_salesreps2_tbl cur_salesreps2_type;
267
268 begin
269 if(p_analyst_id <> -99) then
270 open cur_salesreps1(p_org_id, p_analyst_id);
271 fetch cur_salesreps1 bulk collect into cur_salesreps1_tbl;
272 close cur_salesreps1;
273
274 if (cur_salesreps1_tbl.COUNT > 0) then
275 for i in cur_salesreps1_tbl.FIRST .. cur_salesreps1_tbl.LAST loop
276 l_count := l_count + 1;
277 x_result_tbl(l_count).x_name := cur_salesreps1_tbl(i).name;
278 x_result_tbl(l_count).x_emp_num := cur_salesreps1_tbl(i).employee_number;
279 x_result_tbl(l_count).x_cost_center := cur_salesreps1_tbl(i).cost_center;
280 x_result_tbl(l_count).x_charge_to_cost_center := cur_salesreps1_tbl(i).charge_to_cost_center;
281 x_result_tbl(l_count).x_analyst_name := cur_salesreps1_tbl(i).assigned_to_user_name;
282 x_result_tbl(l_count).x_salesrep_id := cur_salesreps1_tbl(i).salesrep_id;
283 end loop;
284 end if; /* end if (cur_salesreps1_tbl.COUNT > 0) */
285
286 else
287 open cur_salesreps2(p_org_id);
288 fetch cur_salesreps2 bulk collect into cur_salesreps2_tbl;
289 close cur_salesreps2;
290
291 if (cur_salesreps2_tbl.COUNT > 0) then
292 for i in cur_salesreps2_tbl.FIRST .. cur_salesreps2_tbl.LAST loop
293 l_count := l_count + 1;
294 x_result_tbl(l_count).x_name := cur_salesreps2_tbl(i).name;
295 x_result_tbl(l_count).x_emp_num := cur_salesreps2_tbl(i).employee_number;
296 x_result_tbl(l_count).x_cost_center := cur_salesreps2_tbl(i).cost_center;
297 x_result_tbl(l_count).x_charge_to_cost_center := cur_salesreps2_tbl(i).charge_to_cost_center;
301 end if; /* end if (cur_salesreps2_tbl.COUNT > 0) */
298 x_result_tbl(l_count).x_analyst_name := cur_salesreps2_tbl(i).assigned_to_user_name;
299 x_result_tbl(l_count).x_salesrep_id := cur_salesreps2_tbl(i).salesrep_id;
300 end loop;
302
303 end if; /* end if(p_analyst_id <> -99) */
304
305 end Get_Analyst_Details;
306
307 -- gets comm summ report for given rep - one rec for each plan assigned
308 PROCEDURE Get_Quota_Summary
309 (p_salesrep_id IN NUMBER,
310 p_period_id IN NUMBER,
311 p_credit_type_id IN NUMBER,
312 p_org_id IN NUMBER,
313 x_result_tbl OUT NOCOPY comm_summ_tbl_type) IS
314
315 l_year_start_date DATE;
316 l_year_start_period NUMBER;
317 l_period_end_date DATE;
318 l_count NUMBER := 0;
319
320 cursor get_plans is
321 SELECT assign.srp_plan_assign_id srp_plan_assign_id,
322 r.name role_name,
323 cp.name plan_name,
324 assign.start_date start_date,
325 assign.end_date end_date
326 FROM cn_srp_plan_assigns assign,
327 cn_roles r,
328 cn_comp_plans cp
329 WHERE l_year_start_date <= nvl(assign.end_date, l_year_start_date)
330 AND l_period_end_date >= assign.start_date
331 AND assign.role_id = r.role_id
332 AND assign.comp_plan_id = cp.comp_plan_id
333 AND assign.org_id = cp.org_id
334 AND assign.salesrep_id = p_salesrep_id
335 AND assign.org_id = p_org_id
336 ORDER BY assign.start_date;
337
338 CURSOR get_int_earn(l_srp_plan_assign_id NUMBER,
339 l_start_pd NUMBER,
340 l_end_pd NUMBER,
341 l_org_id NUMBER) IS
342 select nvl(sum(balance2_dtd),0)
343 from cn_srp_periods sp, cn_srp_plan_assigns spa, cn_quotas_all q
344 where spa.srp_plan_assign_id = l_srp_plan_assign_id
345 and sp.srp_plan_assign_id = spa.srp_plan_assign_id
346 and sp.salesrep_id = spa.salesrep_id
347 and sp.quota_id = q.quota_id
348 and q.quota_group_code is not null
349 and sp.credit_type_id = p_credit_type_id
350 and sp.org_id = spa.org_id
351 and sp.org_id = q.org_id
352 and sp.org_id = l_org_id
353 and period_id between l_start_pd and l_end_pd;
354
355 BEGIN
356 -- get start date of year and end date of period
357 select min(p2.start_date), min(p2.period_id)
358 into l_year_start_date, l_year_start_period
359 from cn_period_statuses p1, cn_period_statuses p2
360 where p1.period_id = p_period_id
361 and p1.period_year = p2.period_year
362 and p1.period_set_id = p2.period_set_id
363 and p1.period_type_id = p2.period_type_id
364 and p1.org_id = p2.org_id and p1.org_id = p_org_id;
365
366 select end_date into l_period_end_date from cn_period_statuses
367 where period_id = p_period_id and org_id=p_org_id;
368
369 for p in get_plans loop
370 l_count := l_count + 1;
371 x_result_tbl(l_count).srp_plan_assign_id := p.srp_plan_assign_id;
372 x_result_tbl(l_count).role_name := p.role_name;
373 x_result_tbl(l_count).plan_name := p.plan_name;
374 x_result_tbl(l_count).start_date := p.start_date;
375 x_result_tbl(l_count).end_date := p.end_date;
376 x_result_tbl(l_count).salesrep_id := p_salesrep_id;
377 -- get ytd_total_earnings and ptd_total_earnings
378 open get_int_earn(p.srp_plan_assign_id, l_year_start_period, p_period_id,p_org_Id);
379 fetch get_int_earn into x_result_tbl(l_count).ytd_total_earnings;
380 close get_int_earn;
381 open get_int_earn(p.srp_plan_assign_id, p_period_id, p_period_id,p_org_id);
382 fetch get_int_earn into x_result_tbl(l_count).ptd_total_earnings;
383 close get_int_earn;
384 end loop;
385 END Get_Quota_Summary;
386
387 -- gets info for each plan assign and quota group
388 PROCEDURE Get_Pe_Info
389 (p_srp_plan_assign_id IN NUMBER,
390 p_period_id IN NUMBER,
391 p_credit_type_id IN NUMBER,
392 p_quota_group_code IN VARCHAR2,
393 p_quota_id IN NUMBER := NULL ,
394 p_org_id IN NUMBER,
395 x_annual_quota OUT NOCOPY NUMBER,
396 x_pct_annual_quota OUT NOCOPY NUMBER,
397 x_ytd_target OUT NOCOPY NUMBER,
398 x_ytd_credit OUT NOCOPY NUMBER,
399 x_ytd_earnings OUT NOCOPY NUMBER,
400 x_ptd_target OUT NOCOPY NUMBER,
401 x_ptd_credit OUT NOCOPY NUMBER,
402 x_ptd_earnings OUT NOCOPY NUMBER,
403 x_itd_unachieved_quota OUT NOCOPY NUMBER,
404 x_itd_tot_target OUT NOCOPY NUMBER) IS
405
406 l_count NUMBER := 0;
407 l_rollover NUMBER := 0;
408 l_total_rollover NUMBER := 0;
409 l_itd_target NUMBER := 0;
410 l_period_year NUMBER := 0;
411
412 -- YTD periods subquery
413 CURSOR ytd_periods IS
414 select ps.period_id period_id,
415 nvl(inv.credit,0) credit,
419 from cn_period_statuses ps, cn_repositories r, cn_period_statuses ps2,
416 nvl(inv.earnings,0) earnings,
417 nvl(inv.target,0) target,
418 nvl(inv.itd_target,0) itd_target
420 (SELECT ps.period_id period_id,
421 nvl(sum(cspq.perf_achieved_ptd),0) credit,
422 nvl(sum(cspq.commission_payed_ptd),0) earnings,
423 nvl(sum(cspq.target_amount),0) target,
424 nvl(sum(cspq.itd_target),0) itd_target
425 FROM cn_srp_period_quotas cspq,
426 cn_quotas_all quota,
427 cn_period_statuses ps,
428 cn_repositories r,
429 cn_period_statuses ps2
430 WHERE cspq.srp_plan_assign_id = p_srp_plan_assign_id
431 AND cspq.quota_id = quota.quota_id
432 AND quota.credit_type_id = p_credit_type_id
433 AND quota.quota_group_code = p_quota_group_code
434 and quota.org_id = cspq.org_id
435 and quota.org_id = p_org_id
436 AND quota.quota_id > 0
437 and ((p_quota_id is not null and quota.quota_id = p_quota_id)
438 OR (QUOTA.QUOTA_ID = QUOTA.QUOTA_ID AND p_quota_id is null))
439 and ps.period_year = ps2.period_year
440 and ps.period_id <= p_period_id
441 and ps2.period_id = p_period_id
442 and ps.period_set_id = r.period_set_id
443 and ps.period_type_id = r.period_type_id
444 and ps2.org_id = p_org_id
445 and ps.org_id = p_org_id
446 and r.org_id = p_org_id
447 and ps.period_id = cspq.period_id
448 GROUP BY ps.period_id) inv
449 where ps.period_id = inv.period_id(+)
450 and ps.period_year = ps2.period_year
451 and ps.period_id <= p_period_id
452 and ps2.period_id = p_period_id
453 and ps.period_set_id = r.period_set_id
454 and ps.period_type_id = r.period_type_id
455 and ps.org_id = ps2.org_id
456 and ps.org_id = r.org_id
457 and r.org_id = p_org_id;
458
459
460
461 -- Annual Subquery
462 -- To get Annual Quota Target and percent
463 CURSOR annual_quota_cur(a_period_year number) IS
464 SELECT nvl(sum(nvl(sqa.target * it.ct,0)),0) target
465 from cn_srp_quota_assigns sqa,
466 cn_quotas q,
467 (select count(distinct interval_number) ct, it.interval_type_id
468 from cn_cal_per_int_types it, cn_period_statuses ps
469 where it.cal_period_id = ps.period_id
470 and it.org_id = ps.org_id
471 and ps.period_year = a_period_year
472 and ps.org_id = p_org_id
473 group by it.interval_type_id) it
474 where sqa.srp_plan_assign_id = p_srp_plan_assign_id
475 and sqa.quota_id = q.quota_id
476 and q.quota_group_code = p_quota_group_code
477 and q.credit_type_id = p_credit_type_id
478 and q.interval_type_id = it.interval_type_id
479 and q.org_id = p_org_id --and sqa.org_id = q.org_id
480 and sqa.org_id = p_org_id
481 and ((p_quota_id is not null and q.quota_id = p_quota_id)
482 OR (q.QUOTA_ID = q.QUOTA_ID AND p_quota_id is null));
483 --and nvl(p_quota_id, q.quota_id) = q.quota_id;
484
485 CURSOR rolling_quota_cur IS
486 SELECT nvl(cspq.rollover,0),
487 nvl(cspq.total_rollover,0)
488 FROM cn_srp_period_quotas cspq,
489 cn_quotas quota
490 WHERE cspq.period_id = p_period_id
491 AND cspq.srp_plan_assign_id = p_srp_plan_assign_id
492 AND cspq.quota_id = quota.quota_id
493 AND cspq.org_id = quota.org_id
494 AND quota.credit_type_id = p_credit_type_id
495 AND quota.quota_group_code = p_quota_group_code
496 AND cspq.org_id = p_org_id
497 and ((p_quota_id is not null and quota.quota_id = p_quota_id)
498 OR (QUOTA.QUOTA_ID = QUOTA.QUOTA_ID AND p_quota_id is null));
499 --AND nvl(p_quota_id, quota.quota_id) = quota.quota_id;
500
501 BEGIN
502 -- initialize ytd fields
503 x_ytd_target := 0;
504 x_ytd_credit := 0;
505 x_ytd_earnings := 0;
506 x_ptd_target := 0;
507 x_ptd_credit := 0;
508 x_ptd_earnings := 0;
509 x_itd_unachieved_quota := 0;
510 x_itd_tot_target := 0;
511 l_itd_target := 0;
512
513 -- ytd_ptd_cur loop
514 FOR period IN ytd_periods LOOP
515 x_ytd_target := x_ytd_target + period.target;
516 x_ytd_credit := x_ytd_credit + period.credit;
517 x_ytd_earnings := x_ytd_earnings + period.earnings;
518
519 -- get ptd info when we are on the right period
520 if period.period_id = p_period_id then
521 x_ptd_target := period.target;
522 x_ptd_credit := period.credit;
523 x_ptd_earnings := period.earnings;
524 l_itd_target := period.itd_target;
525 end if;
526
527 END LOOP; -- end of period loop
528
529 -- clku, get itd_unachieved_quota and itd_tot_target
530 l_rollover := 0;
531 l_total_rollover := 0;
532
533 open rolling_quota_cur;
534 loop
535 exit when rolling_quota_cur%notfound;
536 fetch rolling_quota_cur into l_rollover, l_total_rollover;
537 END LOOP;
538 close rolling_quota_cur;
539
540 x_itd_unachieved_quota := l_rollover;
541 x_itd_tot_target := l_itd_target + l_total_rollover;
542
543 SELECT period_year into l_period_year FROM cn_period_statuses p
544 WHERE p.period_id=p_period_id and p.org_id = p_org_id;
545 -- annual_quota
546 open annual_quota_cur(l_period_year);
547 fetch annual_quota_cur into x_annual_quota;
548 close annual_quota_cur;
549
550 IF x_annual_quota = 0 THEN
551 x_pct_annual_quota := 0;
552 ELSE
553 x_pct_annual_quota :=
554 (x_ytd_credit / x_annual_quota) * 100;
555 END IF;
556 END Get_Pe_Info;
557
558 -- get list of all quota groups
559 PROCEDURE Get_Group_Codes
560 (p_org_id IN NUMBER,
561 x_result_tbl OUT NOCOPY group_code_tbl_type) IS
562
563 CURSOR group_codes IS
564 select distinct quota_group_code from cn_quotas_all
565 where quota_group_code is not null
566 and quota_id > 0 and org_id=p_org_id;
567 l_count NUMBER := 1;
568
569 BEGIN
570 FOR c in group_codes LOOP
571 x_result_tbl(l_count) := c.quota_group_code;
572 l_count := l_count + 1;
573 END LOOP;
574 END Get_Group_Codes;
575
576
577 PROCEDURE Get_Quota_Manager_Summary
578 (
579 p_period_id IN NUMBER,
580 p_credit_type_id IN NUMBER,
581 p_org_id In NUMBER,
582 x_result_tbl OUT NOCOPY comm_summ_tbl_type) IS
583
584 l_year_start_date DATE;
585 l_year_start_period NUMBER;
586 l_period_end_date DATE;
587 l_count NUMBER := 0;
588
589 CURSOR get_all_plans (c_year_start_date DATE,
590 c_period_end_date DATE,
591 c_org_id NUMBER,
592 c_period_start_pd NUMBER,
593 c_end_pd NUMBER,
594 c_year_start_pd NUMBER,
595 c_credit_type_id NUMBER) IS
596 SELECT assign.srp_plan_assign_id srp_plan_assign_id,
597 r.name role_name,
598 cp.name plan_name,
599 assign.start_date start_date,
600 assign.end_date end_date,
601 assign.salesrep_id salesrep_id,
602 nvl(inv_ptd.earnings,0) ptd_earnings,
603 nvl(inv_ytd.earnings,0) ytd_earnings
604 FROM cn_srp_plan_assigns assign,
605 cn_roles r,
606 cn_comp_plans cp,
607 (select spa.srp_plan_assign_id srp_plan_assign_id,
608 nvl(sum(balance2_dtd),0) earnings
609 from cn_srp_periods sp, cn_srp_plan_assigns spa, cn_quotas_all q
610 where sp.srp_plan_assign_id = spa.srp_plan_assign_id
611 and sp.salesrep_id = spa.salesrep_id
612 and sp.org_id = spa.org_id
613 and sp.org_id = q.org_id
614 and sp.quota_id = q.quota_id
615 and q.quota_group_code is not null
616 and sp.credit_type_id = c_credit_type_id
617 and sp.org_id = c_org_id
618 and period_id between c_period_start_pd and c_end_pd
619 group by spa.srp_plan_assign_id) inv_ptd ,
620 (select spa.srp_plan_assign_id srp_plan_assign_id,
621 nvl(sum(balance2_dtd),0) earnings
622 from cn_srp_periods sp, cn_srp_plan_assigns spa, cn_quotas_all q
623 where sp.srp_plan_assign_id = spa.srp_plan_assign_id
624 and sp.salesrep_id = spa.salesrep_id
625 and sp.org_id = spa.org_id
626 and sp.org_id = q.org_id
630 and sp.org_id = c_org_id
627 and sp.quota_id = q.quota_id
628 and q.quota_group_code is not null
629 and sp.credit_type_id = c_credit_type_id
631 and period_id between c_year_start_pd and c_end_pd
632 group by spa.srp_plan_assign_id) inv_ytd
633 WHERE --:b1 <= nvl(assign.end_date, :b2)
634 ((assign.end_date IS not null AND assign.end_date >= c_year_start_date) OR assign.end_date IS null )
635 AND c_period_end_date >= assign.start_date
636 AND assign.srp_plan_assign_id = inv_ytd.srp_plan_assign_id(+)
637 AND assign.srp_plan_assign_id = inv_ptd.srp_plan_assign_id (+)
638 AND assign.role_id = r.role_id
639 AND assign.comp_plan_id = cp.comp_plan_id
640 AND assign.org_id = cp.org_id
641 and assign.org_id = c_org_id
642 AND assign.salesrep_id in
643 (SELECT DISTINCT S.SALESREP_ID
644 FROM JTF_RS_REP_MANAGERS RM,JTF_RS_GROUP_USAGES U, CN_SALESREPS S WHERE
645 RM.PARENT_RESOURCE_ID=(SELECT R.RESOURCE_ID
646 FROM JTF_RS_RESOURCE_EXTNS R WHERE R.USER_ID = FND_GLOBAL.USER_ID)
647 AND RM.RESOURCE_ID=S.RESOURCE_ID
648 AND RM.HIERARCHY_TYPE IN ('MGR_TO_REP', 'REP_TO_REP', 'MGR_TO_MGR')
649 AND U.USAGE=CN_SYSTEM_PARAMETERS.VALUE('CN_REPORTING_HIERARCHY',c_org_id)
650 AND RM.GROUP_ID = U.GROUP_ID AND SYSDATE >= RM.START_DATE_ACTIVE
651 AND (RM.END_DATE_ACTIVE IS NULL OR (RM.END_DATE_ACTIVE >= SYSDATE))
652 AND DENORM_LEVEL IS NOT NULL AND S.ORG_ID =c_org_id)
653 ORDER BY assign.start_date ;
654
655 TYPE get_all_plans_type IS TABLE OF get_all_plans%ROWTYPE;
656 get_all_plans_tbl get_all_plans_type;
657
658 BEGIN
659 -- get start date of year and end date of period
660 select min(p2.start_date), min(p2.period_id)
661 into l_year_start_date, l_year_start_period
662 from cn_period_statuses p1, cn_period_statuses p2
663 where p1.period_id = p_period_id
664 and p1.period_year = p2.period_year
665 and p1.period_set_id = p2.period_set_id
666 and p1.period_type_id = p2.period_type_id
667 and p1.org_id = p2.org_id and p1.org_id =p_org_id;
668
669 select end_date into l_period_end_date from cn_period_statuses
670 where period_id = p_period_id and org_id =p_org_id;
671
672 open get_all_plans(l_year_start_date, l_period_end_date,p_org_id, p_period_id, p_period_id,l_year_start_period, p_credit_type_id);
673 fetch get_all_plans bulk collect into get_all_plans_tbl;
674 close get_all_plans;
675
676 if (get_all_plans_tbl.COUNT > 0) then
677 for i in get_all_plans_tbl.FIRST .. get_all_plans_tbl.LAST loop
678 l_count := l_count + 1;
679 x_result_tbl(l_count).srp_plan_assign_id := get_all_plans_tbl(i).srp_plan_assign_id;
680 x_result_tbl(l_count).role_name := get_all_plans_tbl(i).role_name;
681 x_result_tbl(l_count).plan_name := get_all_plans_tbl(i).plan_name;
682 x_result_tbl(l_count).start_date := get_all_plans_tbl(i).start_date;
683 x_result_tbl(l_count).end_date := get_all_plans_tbl(i).end_date;
684 x_result_tbl(l_count).salesrep_id := get_all_plans_tbl(i).salesrep_id;
685 x_result_tbl(l_count).ytd_total_earnings := get_all_plans_tbl(i).ytd_earnings;
686 x_result_tbl(l_count).ptd_total_earnings := get_all_plans_tbl(i).ptd_earnings;
687 end loop;
688 end if;
689
690 end Get_Quota_Manager_Summary;
691
692 PROCEDURE Get_Quota_Analyst_Summary
693 (
694 p_period_id IN NUMBER,
695 p_credit_type_id IN NUMBER,
696 p_org_id IN NUMBER,
697 p_analyst_id IN NUMBER,
698 x_result_tbl OUT NOCOPY comm_summ_tbl_type) IS
699
700 l_year_start_date DATE;
701 l_year_start_period NUMBER;
702 l_period_end_date DATE;
703 l_count NUMBER := 0;
704
705 CURSOR get_all_plans (c_year_start_date DATE,
706 c_period_end_date DATE,
707 c_org_id NUMBER,
708 c_start_pd NUMBER,
709 c_end_pd NUMBER,
710 c_credit_type_id NUMBER,
711 c_analyst_id NUMBER) IS
712 SELECT assign.srp_plan_assign_id srp_plan_assign_id,
713 r.name role_name,
714 cp.name plan_name,
715 assign.start_date start_date,
716 assign.end_date end_date,
717 assign.salesrep_id salesrep_id,
718 nvl(inv.earnings,0) earnings
719 FROM cn_srp_plan_assigns assign,
720 cn_roles r,
721 cn_comp_plans cp,
722 (select spa.srp_plan_assign_id srp_plan_assign_id,
723 nvl(sum(balance2_dtd),0) earnings
724 from cn_srp_periods sp, cn_srp_plan_assigns spa, cn_quotas_all q
725 where sp.srp_plan_assign_id = spa.srp_plan_assign_id
726 and sp.salesrep_id = spa.salesrep_id
727 and spa.org_id = p_org_id--and sp.org_id = spa.org_id
728 and q.org_id = p_org_id --and sp.org_id = q.org_id
729 and sp.quota_id = q.quota_id
730 and q.quota_group_code is not null
734 group by spa.srp_plan_assign_id) inv
731 and sp.credit_type_id = c_credit_type_id
732 and sp.org_id = c_org_id
733 and period_id between c_start_pd and c_end_pd
735 WHERE --:b1 <= nvl(assign.end_date, :b2)
736 ((assign.end_date IS not null AND assign.end_date >= c_year_start_date) OR assign.end_date IS null )
737 AND c_period_end_date >= assign.start_date
738 AND assign.srp_plan_assign_id = inv.srp_plan_assign_id(+)
739 AND assign.role_id = r.role_id
740 AND assign.comp_plan_id = cp.comp_plan_id
741 AND assign.org_id = cp.org_id
742 and assign.org_id = c_org_id
743 AND assign.salesrep_id in
744 (SELECT SALESREP_ID FROM CN_SALESREPS where org_id = c_org_id
745 AND ((c_analyst_id <> -99 AND assigned_to_user_id = c_analyst_id) OR c_analyst_id = -99))
746 ORDER BY assign.start_date ;
747
748 TYPE get_all_plans_type IS TABLE OF get_all_plans%ROWTYPE;
749 get_all_plans_tbl get_all_plans_type;
750
751 BEGIN
752 -- get start date of year and end date of period
753 select min(p2.start_date), min(p2.period_id)
754 into l_year_start_date, l_year_start_period
755 from cn_period_statuses p1, cn_period_statuses p2
756 where p1.period_id = p_period_id
757 and p1.period_year = p2.period_year
758 and p1.period_set_id = p2.period_set_id
759 and p1.period_type_id = p2.period_type_id
760 and p1.org_id = p2.org_id and p1.org_id=p_org_id;
761
762 select end_date into l_period_end_date from cn_period_statuses
763 where period_id = p_period_id and org_id =p_org_id;
764
765 open get_all_plans(l_year_start_date, l_period_end_date,p_org_id, p_period_id, p_period_id, p_credit_type_id, p_analyst_id);
766 fetch get_all_plans bulk collect into get_all_plans_tbl;
767 close get_all_plans;
768
769 if (get_all_plans_tbl.COUNT > 0) then
770 for i in get_all_plans_tbl.FIRST .. get_all_plans_tbl.LAST loop
771 l_count := l_count + 1;
772 x_result_tbl(l_count).srp_plan_assign_id := get_all_plans_tbl(i).srp_plan_assign_id;
773 x_result_tbl(l_count).role_name := get_all_plans_tbl(i).role_name;
774 x_result_tbl(l_count).plan_name := get_all_plans_tbl(i).plan_name;
775 x_result_tbl(l_count).start_date := get_all_plans_tbl(i).start_date;
776 x_result_tbl(l_count).end_date := get_all_plans_tbl(i).end_date;
777 x_result_tbl(l_count).salesrep_id := get_all_plans_tbl(i).salesrep_id;
778 x_result_tbl(l_count).ytd_total_earnings := get_all_plans_tbl(i).earnings;
779 x_result_tbl(l_count).ptd_total_earnings := get_all_plans_tbl(i).earnings;
780 end loop;
781 end if;
782
783 end Get_Quota_Analyst_Summary;
784
785 PROCEDURE Get_Salesrep_Pe_Info
786 (
787 p_salesrep_id in number,
788 p_period_id IN NUMBER,
789 p_credit_type_id IN NUMBER,
790 p_org_id IN NUMBER,
791 x_result_tbl OUT NOCOPY pe_info_tbl_type) IS
792 l_year_start_date DATE;
793 l_year_start_period NUMBER;
794 l_period_end_date DATE;
795
796 x_annual_quota NUMBER := 0;
797 x_pct_annual_quota NUMBER := 0;
798 x_ytd_target NUMBER := 0;
799 x_ytd_credit NUMBER := 0;
800 x_ytd_earnings NUMBER := 0;
801 x_ptd_target NUMBER := 0;
802 x_ptd_credit NUMBER := 0;
803 x_ptd_earnings NUMBER := 0;
804 x_itd_unachieved_quota NUMBER := 0;
805 x_itd_tot_target NUMBER := 0;
806 l_count NUMBER := 0;
807 x_quota_id number := null;
808 cursor cur_srp_plan_assign_id
809 is
810 SELECT assign.srp_plan_assign_id
811 FROM cn_srp_plan_assigns assign,
812 cn_roles r,
813 cn_comp_plans cp
814 WHERE l_year_start_date <= nvl(assign.end_date, l_year_start_date)
815 AND l_period_end_date >= assign.start_date
816 AND assign.role_id = r.role_id
817 AND assign.comp_plan_id = cp.comp_plan_id
818 AND assign.org_id = cp.org_id
819 AND assign.salesrep_id = p_salesrep_id
820 and assign.org_id = p_org_id
821 ORDER BY assign.start_date;
822
823 cursor cur_quota_groups
824 is
825 select distinct quota_group_code from cn_quotas_all where quota_group_code is not null
826 and quota_id > 0 and org_id=p_org_id;
827 begin
828 select min(p2.start_date), min(p2.period_id)
829 into l_year_start_date, l_year_start_period
830 from cn_period_statuses p1, cn_period_statuses p2
831 where p1.period_id = p_period_id
832 and p1.period_year = p2.period_year
833 and p1.period_set_id = p2.period_set_id
834 and p1.period_type_id = p2.period_type_id
835 and p1.org_id = p2.org_id
836 and p1.org_id = p_org_id;
837
838 select end_date into l_period_end_date from cn_period_statuses
839 where period_id = p_period_id and org_id = p_org_id;
840
841 for s in cur_srp_plan_assign_id
842 loop
843 for q in cur_quota_groups
844 loop
845
846 get_pe_info(
847 s.srp_plan_assign_id,
848 p_period_id,
849 p_credit_type_id,
850 q.quota_group_code,
851 x_quota_id,
855 x_ytd_target,
852 p_org_id,
853 x_annual_quota ,
854 x_pct_annual_quota,
856 x_ytd_credit,
857 x_ytd_earnings,
858 x_ptd_target,
859 x_ptd_credit,
860 x_ptd_earnings,
861 x_itd_unachieved_quota,
862 x_itd_tot_target);
863 l_count := l_count + 1;
864 x_result_tbl(l_count).srp_plan_assign_id := s.srp_plan_assign_id;
865 x_result_tbl(l_count).quota_group_code := q.quota_group_code;
866 x_result_tbl(l_count).x_annual_quota := x_annual_quota;
867 x_result_tbl(l_count).x_pct_annual_quota := x_pct_annual_quota;
868 x_result_tbl(l_count).x_ytd_target := x_ytd_target;
869 x_result_tbl(l_count).x_ytd_credit := x_ytd_credit;
870 x_result_tbl(l_count).x_ytd_earnings := x_ytd_earnings;
871 x_result_tbl(l_count).x_ptd_target := x_ptd_target;
872 x_result_tbl(l_count).x_ptd_credit := x_ptd_credit;
873 x_result_tbl(l_count).x_ptd_earnings := x_ptd_earnings;
874 x_result_tbl(l_count).x_itd_unachieved_quota := x_itd_unachieved_quota;
875 x_result_tbl(l_count).x_itd_tot_target := x_itd_tot_target;
876 end loop;
877 end loop;
878 end Get_Salesrep_Pe_Info;
879
880 PROCEDURE Get_Manager_Pe_Info
881 (
882 p_period_id IN NUMBER,
883 p_credit_type_id IN NUMBER,
884 p_org_id IN NUMBER,
885 x_result_tbl OUT NOCOPY pe_info_tbl_type) IS
886 l_year_start_date DATE;
887 l_year_start_period NUMBER;
888 l_period_end_date DATE;
889
890 x_annual_quota NUMBER := 0;
891 x_pct_annual_quota NUMBER := 0;
892 x_ytd_target NUMBER := 0;
893 x_ytd_credit NUMBER := 0;
894 x_ytd_earnings NUMBER := 0;
895 x_ptd_target NUMBER := 0;
896 x_ptd_credit NUMBER := 0;
897 x_ptd_earnings NUMBER := 0;
898 x_itd_unachieved_quota NUMBER := 0;
899 x_itd_tot_target NUMBER := 0;
900 l_count NUMBER := 0;
901 x_quota_id number := null;
902 cursor srp_plan_assigns(c_year_start_date DATE,
903 c_period_end_date DATE,
904 c_org_id NUMBER)
905 is
906 SELECT assign.srp_plan_assign_id
907 FROM cn_srp_plan_assigns assign,
908 cn_roles r,
909 cn_comp_plans cp
910 WHERE c_year_start_date <= nvl(assign.end_date, c_year_start_date)
911 AND c_period_end_date >= assign.start_date
912 AND assign.role_id = r.role_id
913 AND assign.comp_plan_id = cp.comp_plan_id
914 AND assign.org_id = cp.org_id
915 and assign.org_id = c_org_id
916 AND assign.salesrep_id in (SELECT DISTINCT S.SALESREP_ID
917 FROM JTF_RS_REP_MANAGERS RM,JTF_RS_GROUP_USAGES U, CN_SALESREPS S WHERE
918 RM.PARENT_RESOURCE_ID=(SELECT R.RESOURCE_ID
919 FROM JTF_RS_RESOURCE_EXTNS R WHERE R.USER_ID = FND_GLOBAL.USER_ID)
920 AND RM.RESOURCE_ID=S.RESOURCE_ID
921 AND RM.HIERARCHY_TYPE IN ('MGR_TO_REP', 'REP_TO_REP', 'MGR_TO_MGR')
922 AND U.USAGE=CN_SYSTEM_PARAMETERS.VALUE('CN_REPORTING_HIERARCHY',c_org_id)
923 AND RM.GROUP_ID = U.GROUP_ID AND SYSDATE >= RM.START_DATE_ACTIVE
924 AND (RM.END_DATE_ACTIVE IS NULL OR (RM.END_DATE_ACTIVE >= SYSDATE))
925 AND DENORM_LEVEL IS NOT NULL AND S.ORG_ID = c_org_id
926 )
927 ORDER BY assign.start_date;
928
929 cursor quota_groups(c_org_id NUMBER)
930 is
931 select
932 distinct quota_group_code
933 from cn_quotas_all
934 where quota_group_code is not null and quota_id > 0 and org_id=p_org_id;
935
936 TYPE srp_plan_assigns_type IS TABLE OF srp_plan_assigns%ROWTYPE;
937 srp_plan_assigns_tbl srp_plan_assigns_type;
938
939 TYPE quota_groups_type IS TABLE OF quota_groups%ROWTYPE;
940 quota_groups_tbl quota_groups_type;
941
942 begin
943 select min(p2.start_date), min(p2.period_id)
944 into l_year_start_date, l_year_start_period
945 from cn_period_statuses p1, cn_period_statuses p2
946 where p1.period_id = p_period_id
947 and p1.period_year = p2.period_year
948 and p1.period_set_id = p2.period_set_id
949 and p1.period_type_id = p2.period_type_id
950 and p1.org_id = p2.org_id and p1.org_id=p_org_id;
951
952 select end_date into l_period_end_date from cn_period_statuses
953 where period_id = p_period_id and org_id = p_org_id;
954
955 open quota_groups(p_org_id);
956 fetch quota_groups bulk collect into quota_groups_tbl;
957 close quota_groups;
958
959 open srp_plan_assigns(l_year_start_date,l_period_end_date,p_org_id);
960 fetch srp_plan_assigns bulk collect into srp_plan_assigns_tbl;
961 close srp_plan_assigns;
962
963 if ((srp_plan_assigns_tbl.COUNT > 0) AND (quota_groups_tbl.COUNT > 0)) then
964 for i in srp_plan_assigns_tbl.FIRST .. srp_plan_assigns_tbl.LAST loop
965 for j in quota_groups_tbl.FIRST .. quota_groups_tbl.LAST loop
966 get_pe_info(
967 srp_plan_assigns_tbl(i).srp_plan_assign_id,
968 p_period_id,
969 p_credit_type_id,
970 quota_groups_tbl(j).quota_group_code,
974 x_pct_annual_quota,
971 x_quota_id,
972 p_org_id,
973 x_annual_quota ,
975 x_ytd_target,
976 x_ytd_credit,
977 x_ytd_earnings,
978 x_ptd_target,
979 x_ptd_credit,
980 x_ptd_earnings,
981 x_itd_unachieved_quota,
982 x_itd_tot_target);
983 l_count := l_count + 1;
984 x_result_tbl(l_count).srp_plan_assign_id := srp_plan_assigns_tbl(i).srp_plan_assign_id;
985 x_result_tbl(l_count).quota_group_code := quota_groups_tbl(j).quota_group_code;
986 x_result_tbl(l_count).x_annual_quota := x_annual_quota;
987 x_result_tbl(l_count).x_pct_annual_quota := x_pct_annual_quota;
988 x_result_tbl(l_count).x_ytd_target := x_ytd_target;
989 x_result_tbl(l_count).x_ytd_credit := x_ytd_credit;
990 x_result_tbl(l_count).x_ytd_earnings := x_ytd_earnings;
991 x_result_tbl(l_count).x_ptd_target := x_ptd_target;
992 x_result_tbl(l_count).x_ptd_credit := x_ptd_credit;
993 x_result_tbl(l_count).x_ptd_earnings := x_ptd_earnings;
994 x_result_tbl(l_count).x_itd_unachieved_quota := x_itd_unachieved_quota;
995 x_result_tbl(l_count).x_itd_tot_target := x_itd_tot_target;
996 end loop;
997 end loop;
998 end if;
999
1000
1001 end Get_Manager_Pe_Info;
1002
1003 PROCEDURE Get_Analyst_Pe_Info
1004 (
1005 p_period_id IN NUMBER,
1006 p_credit_type_id IN NUMBER,
1007 p_org_id IN NUMBER,
1008 p_analyst_id IN NUMBER,
1009 x_result_tbl OUT NOCOPY pe_info_tbl_type) IS
1010
1011 l_year_start_date DATE;
1012 l_year_start_period NUMBER;
1013 l_period_end_date DATE;
1014
1015 x_annual_quota NUMBER := 0;
1016 x_pct_annual_quota NUMBER := 0;
1017 x_ytd_target NUMBER := 0;
1018 x_ytd_credit NUMBER := 0;
1019 x_ytd_earnings NUMBER := 0;
1020 x_ptd_target NUMBER := 0;
1021 x_ptd_credit NUMBER := 0;
1022 x_ptd_earnings NUMBER := 0;
1023 x_itd_unachieved_quota NUMBER := 0;
1024 x_itd_tot_target NUMBER := 0;
1025 l_count NUMBER := 0;
1026 x_quota_id number := null;
1027 l_srp_plan_assign_id number;
1028
1029 cursor srp_plan_assigns(c_year_start_date DATE,
1030 c_period_end_date DATE,
1031 c_org_id NUMBER,
1032 c_analyst_id NUMBER)
1033 is
1034 SELECT assign.srp_plan_assign_id
1035 FROM cn_srp_plan_assigns assign,
1036 cn_roles r,
1037 cn_comp_plans cp
1038 WHERE c_year_start_date <= nvl(assign.end_date, c_year_start_date)
1039 AND c_period_end_date >= assign.start_date
1040 AND assign.role_id = r.role_id
1041 AND assign.comp_plan_id = cp.comp_plan_id
1042 AND assign.org_id = cp.org_id
1043 and assign.org_id = c_org_id
1044 AND assign.salesrep_id in
1045 (SELECT SALESREP_ID FROM CN_SALESREPS where org_id = c_org_id
1046 AND ((c_analyst_id <> -99 AND assigned_to_user_id = c_analyst_id) OR c_analyst_id = -99))
1047 ORDER BY assign.start_date;
1048
1049 cursor quota_groups(c_org_id NUMBER)
1050 is
1051 select distinct quota_group_code
1052 from cn_quotas_all
1053 where quota_group_code is not null
1054 and quota_id > 0
1055 and org_id = c_org_id;
1056
1057 TYPE srp_plan_assigns_type IS TABLE OF srp_plan_assigns%ROWTYPE;
1058 srp_plan_assigns_tbl srp_plan_assigns_type;
1059
1060 TYPE quota_groups_type IS TABLE OF quota_groups%ROWTYPE;
1061 quota_groups_tbl quota_groups_type;
1062
1063 begin
1064 select min(p2.start_date), min(p2.period_id)
1065 into l_year_start_date, l_year_start_period
1066 from cn_period_statuses p1, cn_period_statuses p2
1067 where p1.period_id = p_period_id
1068 and p1.period_year = p2.period_year
1069 and p1.period_set_id = p2.period_set_id
1070 and p1.period_type_id = p2.period_type_id
1071 and p1.org_id = p2.org_id and p1.org_id=p_org_id;
1072
1073 select end_date into l_period_end_date from cn_period_statuses
1074 where period_id = p_period_id and org_id = p_org_id;
1075
1076 open quota_groups(p_org_id);
1077 fetch quota_groups bulk collect into quota_groups_tbl;
1078 close quota_groups;
1079
1080 open srp_plan_assigns(l_year_start_date,l_period_end_date,p_org_id,p_analyst_id);
1081 fetch srp_plan_assigns bulk collect into srp_plan_assigns_tbl;
1082 close srp_plan_assigns;
1083
1084 if ((srp_plan_assigns_tbl.COUNT > 0) AND (quota_groups_tbl.COUNT > 0)) then
1085 for i in srp_plan_assigns_tbl.FIRST .. srp_plan_assigns_tbl.LAST loop
1086 for j in quota_groups_tbl.FIRST .. quota_groups_tbl.LAST loop
1087 get_pe_info(
1088 srp_plan_assigns_tbl(i).srp_plan_assign_id,
1089 p_period_id,
1090 p_credit_type_id,
1091 quota_groups_tbl(j).quota_group_code,
1092 x_quota_id,
1093 p_org_id,
1094 x_annual_quota ,
1098 x_ytd_earnings,
1095 x_pct_annual_quota,
1096 x_ytd_target,
1097 x_ytd_credit,
1099 x_ptd_target,
1100 x_ptd_credit,
1101 x_ptd_earnings,
1102 x_itd_unachieved_quota,
1103 x_itd_tot_target);
1104 l_count := l_count + 1;
1105 x_result_tbl(l_count).srp_plan_assign_id := srp_plan_assigns_tbl(i).srp_plan_assign_id;
1106 x_result_tbl(l_count).quota_group_code := quota_groups_tbl(j).quota_group_code;
1107 x_result_tbl(l_count).x_annual_quota := x_annual_quota;
1108 x_result_tbl(l_count).x_pct_annual_quota := x_pct_annual_quota;
1109 x_result_tbl(l_count).x_ytd_target := x_ytd_target;
1110 x_result_tbl(l_count).x_ytd_credit := x_ytd_credit;
1111 x_result_tbl(l_count).x_ytd_earnings := x_ytd_earnings;
1112 x_result_tbl(l_count).x_ptd_target := x_ptd_target;
1113 x_result_tbl(l_count).x_ptd_credit := x_ptd_credit;
1114 x_result_tbl(l_count).x_ptd_earnings := x_ptd_earnings;
1115 x_result_tbl(l_count).x_itd_unachieved_quota := x_itd_unachieved_quota;
1116 x_result_tbl(l_count).x_itd_tot_target := x_itd_tot_target;
1117 end loop;
1118 end loop;
1119 end if;
1120
1121 end Get_Analyst_Pe_Info;
1122
1123 PROCEDURE Get_Ptd_Credit
1124 (p_salesrep_id IN NUMBER,
1125 p_payrun_id IN NUMBER,
1126 p_org_id in NUMBER,
1127 x_result_tbl IN OUT NOCOPY pe_ptd_credit_tbl_type
1128 ) IS
1129 x_period_id NUMBER:= 0;
1130 l_annual_quota NUMBER:= 0;
1131 l_pct_annual_quota NUMBER:= 0;
1132 l_ytd_target NUMBER:= 0;
1133 l_ytd_credit NUMBER:= 0;
1134 l_ytd_earnings NUMBER:= 0;
1135 l_ptd_target NUMBER:= 0;
1136 l_ptd_credit NUMBER:= 0;
1137 l_ptd_earnings NUMBER:= 0;
1138 l_ytd_attain NUMBER:= 0;
1139 l_ptd_attain NUMBER:= 0;
1140 l_itd_unachieved_quota NUMBER:= 0;
1141 l_itd_tot_target NUMBER:= 0;
1142 l_count NUMBER:= 0;
1143 CURSOR get_plans ( l_period_id NUMBER,
1144 l_payrun_id NUMBER,
1145 l_salesrep_id IN NUMBER,
1146 l_org_id IN NUMBER) IS
1147 SELECT DISTINCT
1148 srp.srp_plan_assign_id srp_plan_assign_id,
1149 q.quota_id quota_id,
1150 r.role_id role_id,
1151 cp.comp_plan_id comp_plan_id,
1152 assign.start_date start_date,
1153 assign.end_date end_date,
1154 q.quota_group_code quota_group_code
1155 FROM cn_srp_periods srp,
1156 cn_srp_plan_assigns assign,
1157 cn_roles r,
1158 cn_comp_plans cp,
1159 cn_payment_worksheets w,
1160 cn_quotas_all q
1161 WHERE assign.srp_plan_assign_id(+) = srp.srp_plan_assign_id
1162 AND srp.period_id = l_period_id
1163 AND assign.role_id = r.role_id(+)
1164 AND assign.comp_plan_id = cp.comp_plan_id(+)
1165 AND assign.org_id = cp.org_id(+)
1166 AND q.quota_id = w.quota_id
1167 AND q.org_id = w.org_id
1168 AND w.payrun_Id = l_payrun_id
1169 AND w.salesrep_id = l_salesrep_id
1170 AND q.quota_id = srp.quota_id
1171 AND q.org_id = srp.org_id
1172 AND srp.salesrep_id = l_salesrep_id
1173 AND srp.org_id = p_org_id
1174 AND srp.credit_type_id = -1000
1175 AND w.quota_id <> -1000
1176 ORDER BY assign.start_date;
1177 begin
1178 select pay_period_id into x_period_id from cn_payruns where payrun_id=p_payrun_id;
1179 for p in get_plans(x_period_id,
1180 p_payrun_id,
1181 p_salesrep_id,
1182 p_org_id) loop
1183
1184 cn_get_comm_summ_data_pvt.Get_Pe_Info
1185 (p_srp_plan_assign_id => p.srp_plan_assign_id,
1186 p_period_id => x_period_id,
1187 p_credit_type_id => -1000,
1188 p_quota_group_code => p.quota_group_code,
1189 p_quota_id => p.quota_id,
1190 p_org_id => p_org_id,
1191 x_annual_quota => l_annual_quota,
1192 x_pct_annual_quota => l_pct_annual_quota,
1193 x_ytd_target => l_ytd_target,
1194 x_ytd_credit => l_ytd_credit,
1195 x_ytd_earnings => l_ytd_earnings,
1196 x_ptd_target => l_ptd_target,
1197 x_ptd_credit => l_ptd_credit,
1198 x_ptd_earnings => l_ptd_earnings,
1199 x_itd_unachieved_quota => l_itd_unachieved_quota,
1200 x_itd_tot_target => l_itd_tot_target);
1201
1202 l_count := l_count + 1;
1203 x_result_tbl(l_count).quota_id := p.quota_id;
1204 x_result_tbl(l_count).x_ptd_credit := l_ptd_credit;
1205 end loop;
1206 end Get_Ptd_Credit;
1207
1208 FUNCTION get_conversion_type(p_org_id IN NUMBER)
1209 RETURN VARCHAR2 IS
1210 l_profile_value VARCHAR2(100);
1211 cursor prof_cursor(a_org_id NUMBER) is
1212 SELECT CN_CONVERSION_TYPE FROM CN_REPOSITORIES WHERE ORG_ID=a_org_id;
1213 d prof_cursor%rowtype;
1214 BEGIN
1215 open prof_cursor(p_org_id);
1216 fetch prof_cursor into d;
1217 l_profile_value := d.cn_conversion_type;
1218 close prof_cursor;
1219 return l_profile_value;
1220 EXCEPTION
1221 WHEN OTHERS THEN
1222 FND_MESSAGE.SET_NAME('CN','CN_INVALID_PROFILE_CODE');
1223 APP_EXCEPTION.RAISE_EXCEPTION;
1224 END GET_CONVERSION_TYPE;
1225
1226 END CN_GET_COMM_SUMM_DATA_PVT;