[Home] [Help]
PACKAGE BODY: APPS.CN_CALC_POPULATE_PVT
Source
1 PACKAGE BODY cn_calc_populate_pvt AS
2 -- $Header: cnvcpopb.pls 120.9 2006/08/18 16:38:27 ymao ship $
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CN_CALC_POPULATE_PVT';
5 G_FILE_NAME CONSTANT VARCHAR2(12) := 'cnvcpopb.pls';
6 G_LAST_UPDATE_DATE DATE := sysdate;
7 G_LAST_UPDATED_BY NUMBER := fnd_global.user_id;
8 G_CREATION_DATE DATE := sysdate;
9 G_CREATED_BY NUMBER := fnd_global.user_id;
10 G_LAST_UPDATE_LOGIN NUMBER := fnd_global.login_id;
11
12 g_calc_type VARCHAR2(30);
13 g_org_id NUMBER;
14
15 -- beginning of private procedures
16
17 PROCEDURE populate_lines
18 (x_role_count IN OUT NOCOPY NUMBER,
19 p_quota_count NUMBER,
20 p_salesrep_id NUMBER,
21 p_revenue_class_id NUMBER,
22 p_processed_date DATE,
23 p_comp_group_id NUMBER,
24 p_role_id NUMBER,
25 p_srp_plan_assign_id NUMBER,
26 p_quota_id NUMBER,
27 p_processed_period_id NUMBER,
28 p_quota_rule_id NUMBER) IS
29 BEGIN
30 IF (p_quota_count = 1) AND (x_role_count = 1) THEN
31 x_role_count := 2;
32
33 UPDATE cn_commission_lines_all cl
34 SET cl.srp_plan_assign_id = p_srp_plan_assign_id,
35 cl.quota_id = p_quota_id,
36 cl.quota_rule_id = p_quota_rule_id,
37 cl.status = 'POP',
38 cl.role_id = p_role_id,
39 cl.pay_period_id = cl.processed_period_id
40 WHERE cl.credited_salesrep_id = p_salesrep_id
41 AND cl.credited_comp_group_id = p_comp_group_id
42 AND cl.processed_period_id = p_processed_period_id
43 AND cl.processed_date = p_processed_date
44 AND cl.created_during in ('ROLL', 'TROLL')
45 AND cl.status IN ('ROLL')
46 AND cl.quota_id IS NULL
47 AND cl.org_id = g_org_id
48 AND ((g_calc_type = 'COMMISSION' AND cl.trx_type NOT IN ('BONUS', 'GRP', 'FORECAST')) OR
49 (g_calc_type = 'FORECAST' AND cl.trx_type = 'FORECAST'))
50 AND cl.revenue_class_id = p_revenue_class_id
51 AND ( substr(cl.pre_processed_code, 3,1) = 'P' OR
52 (substr(cl.pre_processed_code, 3,1) = 'N' AND
53 cl.direct_salesrep_id <> cl.credited_salesrep_id ) );
54 ELSE
55
56 -- create new transaction lines for plan_quota
57 INSERT INTO cn_commission_lines_all
58 ( commission_line_id,
59 commission_header_id,
60 CREDITED_SALESREP_ID,
61 credited_comp_group_id,
62 role_id,
63 processed_period_id,
64 pay_period_id,
65 PENDING_STATUS,
66 SRP_PLAN_ASSIGN_ID,
67 quota_id,
68 QUOTA_RULE_ID,
69 STATUS,
70 CREATED_DURING,
71 PAYEE_LINE_ID,
72 trx_type,
73 processed_date,
74 LAST_UPDATE_DATE,
75 LAST_UPDATED_BY,
76 LAST_UPDATE_LOGIN,
77 CREATION_DATE,
78 created_by,
79 org_id,
80 rollup_level)
81 SELECT
82 cn_commission_lines_s.NEXTVAL,
83 cl.commission_header_id,
84 cl.credited_salesrep_id,
85 cl.credited_comp_group_id,
86 p_role_id,
87 cl.processed_period_id,
88 cl.pay_period_id,
89 cl.pending_status,
90 p_srp_plan_assign_id,
91 p_quota_id,
92 p_quota_rule_id,
93 'POP',
94 'POP',
95 cl.commission_line_id,
96 cl.trx_type,
97 cl.processed_date,
98 g_last_update_date,
99 g_last_updated_by,
100 g_last_update_login,
101 g_creation_date,
102 g_created_by,
103 g_org_id,
104 rollup_level
105 FROM cn_commission_lines_all cl
106 WHERE cl.credited_salesrep_id = p_salesrep_id
107 AND cl.processed_date = p_processed_date
108 AND cl.processed_period_id = p_processed_period_id
109 AND cl.credited_comp_group_id = p_comp_group_id
110 AND cl.created_during in ('ROLL', 'TROLL')
111 AND cl.status = 'POP'
112 AND cl.org_id = g_org_id
113 AND cl.revenue_class_id = p_revenue_class_id
114 -- only source trxs can skip 'POP' phase, others need to be handled here
115 AND ( substr(cl.pre_processed_code, 3,1) = 'P'
116 OR (substr(cl.pre_processed_code, 3,1) = 'N'
117 AND cl.direct_salesrep_id <> cl.credited_salesrep_id ) )
118 AND ((g_calc_type = 'COMMISSION'
119 AND cl.trx_type NOT IN ('BONUS', 'GRP', 'FORECAST')) OR
120 (g_calc_type = 'FORECAST'
121 AND cl.trx_type = 'FORECAST'));
122 END IF;
123 EXCEPTION WHEN OTHERS THEN
124 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
125 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
126 'cn.plsql.cn_calc_populate_pvt.populate_lines.exception',
127 sqlerrm);
128 end if;
129
130 fnd_file.put_line(fnd_file.Log, 'In cn_calc_populate_pvt.populate_lines: '||sqlerrm);
131 cn_message_pkg.debug('Exception occurs in creating commission lines in the population phase:');
132 cn_message_pkg.debug('sqlerrm');
133 RAISE;
134 END populate_lines;
135
136 -- API name : populate_batch
137 -- Type : Private.
138 -- Pre-reqs :
139 -- Usage :
140 --
141 -- Desc :
142 --
143 --
144 --
145 -- Parameters :
146 -- IN : p_api_version NUMBER Require
147 -- p_init_msg_list VARCHAR2 Optional (FND_API.G_FALSE)
148 -- p_commit VARCHAR2 Optional (FND_API.G_FALSE)
149 -- p_validation_level NUMBER Optional (FND_API.G_VALID_LEVEL_FULL)
150 -- OUT : x_return_status VARCHAR2(1)
151 -- x_msg_count NUMBER
152 -- x_msg_data VARCHAR2(2000)
153 -- IN : p_physical_batch_id NUMBER(15) Require
154 --
155 --
156 --
157 --
158 -- Version : Current version 1.0
159 -- Initial version 1.0
160 --
161 -- Notes :
162 --
163 -- End of comments
164
165 PROCEDURE populate_batch
166 ( p_api_version IN NUMBER,
167 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
168 p_commit IN VARCHAR2 := FND_API.G_FALSE,
169 p_validation_level IN VARCHAR2 := FND_API.G_VALID_LEVEL_FULL,
170
171 x_return_status OUT NOCOPY VARCHAR2,
172 x_msg_count OUT NOCOPY NUMBER,
173 x_msg_data OUT NOCOPY VARCHAR2,
174
175 p_physical_batch_id IN NUMBER
176
177 ) IS
178
179 l_api_name CONSTANT VARCHAR2(30) := 'Populate_batch';
180 l_api_version CONSTANT NUMBER :=1.0;
181
182 l_processed_date_prev DATE;
183 l_salesrep_id_prev NUMBER(15);
184 l_role_id_prev NUMBER(15);
185 l_quota_id_prev NUMBER(15);
186 l_revenue_class_id_prev NUMBER(15);
187
188 l_dim_hierarchy_id NUMBER(15);
189 l_quota_rule_id NUMBER(15);
190 l_srp_plan_assign_id NUMBER(15);
191
192 l_srp_group_rec cn_rollup_pvt.srp_group_rec_type;
193 l_role_flag_tbl cn_rollup_pvt.role_tbl_type;
194 l_rev_class_hierarchy_id NUMBER;
195 l_start_date date;
196 l_end_date date;
197 g_end_date date;
198 i pls_integer;
199 l_indirect_credit VARCHAR2(10);
200 l_manager_flag VARCHAR2(1);
201
202 l_msg_count NUMBER;
203 l_msg_data VARCHAR2(2000);
204 l_return_status VARCHAR2(30);
205
206 CURSOR team_mbrs IS
207 SELECT pb.salesrep_id,
208 pb.period_id,
209 sct.comp_team_id,
210 scg.comp_group_id,
211 greatest(pb.start_date, sct.start_date_active, scg.start_date_active) start_date,
212 least(pb.end_date, nvl(sct.end_date_active, pb.end_date), nvl(scg.end_date_active, pb.end_date)) end_date
213 FROM cn_srp_comp_teams_v sct,
214 cn_process_batches_all pb,
215 cn_srp_comp_groups_v scg
216 WHERE pb.physical_batch_id = p_physical_batch_id
217 AND pb.salesrep_id = sct.salesrep_id
218 AND pb.start_date <= nvl(sct.end_date_active, pb.end_date)
219 AND sct.org_id = g_org_id
220 AND pb.end_date >= sct.start_date_active
221 AND scg.salesrep_id = pb.salesrep_id
222 AND scg.org_id = g_org_id
223 AND pb.start_date <= nvl(scg.end_date_active, pb.end_date)
224 AND pb.end_date >= scg.start_date_active;
225
226 CURSOR other_mbrs(p_comp_team_id NUMBER, p_salesrep_id NUMBER, p_start_date DATE, p_end_date DATE) IS
227 SELECT sct.salesrep_id,
228 greatest(p_start_date, sct.start_date_active) start_date,
229 least(p_end_date, nvl(sct.end_date_active, p_end_date)) end_date
230 FROM cn_srp_comp_teams_v sct
231 WHERE sct.comp_team_id = p_comp_team_id
232 AND sct.salesrep_id <> p_salesrep_id
233 AND sct.org_id = g_org_id
234 AND p_start_date <= nvl(sct.end_date_active, p_end_date)
235 AND p_end_date >= sct.start_date_active;
236
237 cursor rev_class_hierarchy_id is
238 select rev_class_hierarchy_id
239 from cn_repositories_all
240 where org_id = g_org_id;
241
242 cursor salesreps is
243 select distinct
244 cl.credited_salesrep_id,
245 cl.credited_comp_group_id,
246 pb.period_id,
247 pb.end_period_id,
248 pb.start_date,
249 pb.end_date,
250 cl.revenue_class_id
251 from cn_commission_lines_all cl,
252 cn_process_batches_all pb
253 where pb.physical_batch_id = p_physical_batch_id
254 and cl.credited_salesrep_id = pb.salesrep_id
255 and cl.processed_period_id between pb.period_id AND pb.end_period_id
256 and cl.processed_date BETWEEN pb.start_date AND pb.end_date
257 and cl.org_id = g_org_id
258 and cl.status IN ('ROLL')
259 and cl.quota_id IS NULL
260 and (substr(cl.pre_processed_code, 3,1) = 'P' or
261 (substr(cl.pre_processed_code,3,1) = 'N' and cl.direct_salesrep_id <> cl.credited_salesrep_id ));
262
263 cursor plan_info(p_salesrep_id number,
264 p_comp_group_id number,
265 p_revenue_class_id number,
266 p_start_date date,
267 p_end_date date)
268 is
269 select /*+ ordered use_nl(SPA, JRS)*/
270 spa.role_id,
271 spa.srp_plan_assign_id,
272 sqa.quota_id,
273 qr.quota_rule_id,
274 greatest(dh.start_date, spa.start_date, q.start_date, rr.start_date_active, p_start_date) start_date,
275 least(nvl(dh.end_date, p_end_date),
276 nvl(spa.end_date, p_end_date),
277 nvl(q.end_date, p_end_date),
278 nvl(rr.end_date_active, p_end_date), p_end_date) end_date
279 from cn_srp_plan_assigns_all spa,
280 cn_srp_quota_assigns_all sqa,
281 cn_quotas_all q,
282 cn_quota_rules_all qr,
283 cn_dim_hierarchies_all dh,
284 jtf_rs_salesreps jrs,
285 jtf_rs_group_members mem,
286 jtf_rs_role_relations rr
287 where spa.salesrep_id = p_salesrep_id
288 and spa.org_id = g_org_id
289 and spa.start_date <= p_end_date
290 and nvl(spa.end_date, p_end_date) >= p_start_date
291 and jrs.salesrep_id = p_salesrep_id
292 and jrs.org_id = spa.org_id
293 and mem.group_id = p_comp_group_id
294 and mem.resource_id = jrs.resource_id
295 and nvl(mem.delete_flag, 'N') <> 'Y'
296 and rr.role_id = spa.role_id
297 and rr.role_resource_id = mem.group_member_id
298 and rr.role_resource_type = 'RS_GROUP_MEMBER'
299 and nvl(rr.delete_flag, 'N') <> 'Y'
300 and exists (select /*+ no_unnest */ 1 from cn_comp_plans_all where status_code = 'COMPLETE' AND comp_plan_id = spa.comp_plan_id)
301 and rr.start_date_active <= p_end_date
302 and nvl(rr.end_date_active, p_end_date) >= p_start_date
303 and rr.start_date_active <= nvl(spa.end_date, p_end_date)
304 and nvl(rr.end_date_active, nvl(spa.end_date, p_end_date)) >= spa.start_date
305 and sqa.srp_plan_assign_id = spa.srp_plan_assign_id
306 and q.quota_id = sqa.quota_id
307 and q.start_date <= p_end_date
308 and nvl(q.end_date, p_end_date) >= p_start_date
309 and qr.quota_id = sqa.quota_id
310 and dh.header_dim_hierarchy_id = l_rev_class_hierarchy_id
311 and dh.org_id = g_org_id
312 and dh.start_date <= least(nvl(spa.end_date, p_end_date), nvl(q.end_date, p_end_date))
313 and nvl(dh.end_date, p_end_date) >= greatest(spa.start_date, q.start_date)
314 and exists (select /*+ no_unnest */ 1 from cn_dim_explosion_all de
315 where de.dim_hierarchy_id = dh.dim_hierarchy_id
316 and de.ancestor_external_id = qr.revenue_class_id
317 and de.value_external_id = p_revenue_class_id)
318 order by greatest(dh.start_date, spa.start_date, q.start_date, rr.start_date_active, p_start_date),
319 least(nvl(dh.end_date, p_end_date),
320 nvl(spa.end_date, p_end_date),
321 nvl(q.end_date, p_end_date),
322 nvl(rr.end_date_active, p_end_date), p_end_date);
323
324
325 cursor plan_info2(p_salesrep_id number,
326 p_comp_group_id number,
327 p_revenue_class_id number,
328 p_start_date date,
329 p_end_date date)
330 is
331 select spa.role_id,
332 spa.srp_plan_assign_id,
333 sqa.quota_id,
334 qr.quota_rule_id,
335 greatest(spa.start_date, q.start_date, rr.start_date_active, p_start_date) start_date,
336 least(nvl(spa.end_date, p_end_date),
337 nvl(q.end_date, p_end_date),
338 nvl(rr.end_date_active, p_end_date), p_end_date) end_date
339 from cn_srp_plan_assigns_all spa,
340 cn_srp_quota_assigns_all sqa,
341 cn_quotas_all q,
342 cn_quota_rules_all qr,
343 jtf_rs_salesreps jrs,
344 jtf_rs_group_members mem,
345 jtf_rs_role_relations rr
346 where spa.salesrep_id = p_salesrep_id
347 and spa.org_id = g_org_id
348 and spa.start_date <= p_end_date
349 and nvl(spa.end_date, p_end_date) >= p_start_date
350 and jrs.salesrep_id = p_salesrep_id
351 and jrs.org_id = spa.org_id
352 and mem.group_id = p_comp_group_id
353 and mem.resource_id = jrs.resource_id
354 and nvl(mem.delete_flag, 'N') <> 'Y'
355 and rr.role_id = spa.role_id
356 and rr.role_resource_id = mem.group_member_id
357 and rr.role_resource_type = 'RS_GROUP_MEMBER'
358 and nvl(rr.delete_flag, 'N') <> 'Y'
359 and rr.start_date_active <= p_end_date
360 and nvl(rr.end_date_active, p_end_date) >= p_start_date
361 and rr.start_date_active <= nvl(spa.end_date, p_end_date)
362 and nvl(rr.end_date_active, nvl(spa.end_date, p_end_date)) >= spa.start_date
363 and exists (select 1 from cn_comp_plans_all where status_code = 'COMPLETE' AND comp_plan_id = spa.comp_plan_id)
364 and sqa.srp_plan_assign_id = spa.srp_plan_assign_id
365 and q.quota_id = sqa.quota_id
366 and q.start_date <= p_end_date
367 and nvl(q.end_date, p_end_date) >= p_start_date
368 and qr.quota_id = sqa.quota_id
369 and qr.revenue_class_id = p_revenue_class_id
370 order by greatest(spa.start_date, q.start_date, rr.start_date_active, p_start_date),
371 least(nvl(spa.end_date, p_end_date), nvl(q.end_date, p_end_date), nvl(rr.end_date_active, p_end_date), p_end_date);
372
373 -- get salesreps who has source trxs skipping 'POP' phase
374 CURSOR l_skip_salesreps_csr IS
375 SELECT DISTINCT cl.credited_salesrep_id salesrep_id,
376 cl.processed_period_id,
377 cl.processed_date,
378 ch.role_id,
379 ch.quota_id,
380 ch.revenue_class_id
381 FROM cn_commission_lines_all cl,
382 cn_commission_headers_all ch,
383 cn_process_batches_all pb
384 WHERE pb.physical_batch_id = p_physical_batch_id
385 AND cl.credited_salesrep_id = pb.salesrep_id
389 AND cl.status IN ('ROLL')
386 AND cl.processed_period_id between pb.period_id AND pb.end_period_id
387 AND cl.processed_date BETWEEN pb.start_date AND pb.end_date
388 AND cl.org_id = g_org_id
390 AND ((g_calc_type = 'COMMISSION'
391 AND cl.trx_type NOT IN ('BONUS', 'GRP', 'FORECAST')) OR
392 (g_calc_type = 'FORECAST' AND cl.trx_type = 'FORECAST'))
393 AND ch.commission_header_id = cl.commission_header_id
394 AND ch.role_id IS NOT NULL
395 AND ch.quota_id IS NOT NULL
396 -- only source trxs are allowed to skip the normal 'POPULATION' phase
397 AND Substr(ch.pre_processed_code,3,1) = 'N'
398 AND ch.direct_salesrep_id = cl.credited_salesrep_id
399 order by cl.processed_date, cl.credited_salesrep_id, ch.role_id;
400
401 CURSOR l_dim_hierarchy_csr (l_processed_date DATE) IS
402 SELECT dim_hierarchy_id
403 FROM cn_dim_hierarchies_all dh,
404 cn_repositories_all r
405 WHERE r.org_id = g_org_id
406 AND r.rev_class_hierarchy_id = dh.header_dim_hierarchy_id
407 AND dh.org_id = g_org_id
408 AND l_processed_date BETWEEN dh.start_date AND dh.end_date;
409
410 CURSOR l_spa_csr (l_processed_date DATE,
411 l_salesrep_id NUMBER,
412 l_role_id NUMBER ) IS
413 SELECT spa.srp_plan_assign_id
414 FROM cn_srp_plan_assigns_all spa
415 WHERE spa.role_id = l_role_id
416 AND spa.salesrep_id = l_salesrep_id
417 AND spa.org_id = g_org_id
418 and exists (select comp_plan_id from cn_comp_plans_all where status_code = 'COMPLETE' AND comp_plan_id = spa.comp_plan_id)
419 AND l_processed_date >= spa.start_date
420 AND ( spa.end_date IS NULL OR spa.end_date >= l_processed_date );
421
422 CURSOR l_quota_rule_no_hier_csr ( l_quota_id NUMBER, l_revenue_class_id NUMBER ) IS
423 SELECT qr.quota_rule_id
424 FROM cn_quota_rules_all qr
425 WHERE qr.quota_id = l_quota_id
426 AND qr.revenue_class_id = l_revenue_class_id;
427
428 CURSOR l_quota_rule_hier_csr (l_quota_id NUMBER, l_revenue_class_id NUMBER ) IS
429 SELECT qr.quota_rule_id
430 FROM cn_quota_rules_all qr,
431 cn_dim_explosion_all de
432 WHERE qr.quota_id = l_quota_id
433 AND de.dim_hierarchy_id = l_dim_hierarchy_id
434 AND de.value_external_id = l_revenue_class_id
435 AND de.ancestor_external_id = qr.revenue_class_id;
436
437 l_role_count NUMBER := 0;
438 BEGIN
439 -- Standard call to check for call compatibility.
440 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
441 p_api_version ,
442 l_api_name ,
443 G_PKG_NAME )
444 THEN
445 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
446 END IF;
447
448 -- Initialize message list if p_init_msg_list is set to TRUE.
449 IF FND_API.to_Boolean( p_init_msg_list ) THEN
450 FND_MSG_PUB.initialize;
451 END IF;
452
453 -- Initialize API return status to success
454 x_return_status := FND_API.G_RET_STS_SUCCESS;
455
456 select org_id into g_org_id
457 from cn_process_batches_all
458 where physical_batch_id = p_physical_batch_id
459 and rownum = 1;
460
461 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
462 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
463 'cn.plsql.cn_calc_populate_pvt.populate_batch.progress',
464 'Performing team rollup.');
465 end if;
466
467 -- get credits from team members
468 FOR srp IN team_mbrs LOOP
469 FOR mbr IN other_mbrs(srp.comp_team_id, srp.salesrep_id, srp.start_date, srp.end_date) LOOP
470 INSERT INTO cn_commission_lines_all
471 (commission_line_id,
472 commission_header_id,
473 direct_salesrep_id,
474 pre_processed_code,
475 revenue_class_id,
476 credited_salesrep_id,
477 credited_comp_group_id,
478 pending_status,
479 pending_date,
480 created_during,
481 status,
482 processed_date,
483 processed_period_id,
484 trx_type,
485 created_by,
486 creation_date,
487 org_id,
488 rollup_level)
489 (select
490 cn_commission_lines_s.nextval,
491 cl.commission_header_id,
492 cl.direct_salesrep_id,
493 cl.pre_processed_code,
494 cl.revenue_class_id,
495 srp.salesrep_id,
496 srp.comp_group_id,
497 'N',
498 null,
499 'TROLL',
500 'ROLL',
501 cl.processed_date,
502 cl.processed_period_id,
503 cl.trx_type,
504 fnd_global.user_id,
505 sysdate,
506 g_org_id,
507 0
508 from cn_commission_lines_all cl
509 where cl.credited_salesrep_id = mbr.salesrep_id
510 and cl.processed_date between mbr.start_date and mbr.end_date
511 and cl.created_during = 'ROLL'
512 AND cl.status <> 'OBSOLETE'
513 and cl.org_id = g_org_id
514 and not exists (select 1
515 from cn_commission_lines_all
516 where commission_header_id = cl.commission_header_id
517 and credited_salesrep_id = srp.salesrep_id));
518 IF (SQL%found) THEN
519 cn_mark_events_pkg.mark_notify
520 ( p_salesrep_id => srp.salesrep_id,
524 p_quota_id => NULL,
521 p_period_id => srp.period_id,
522 p_start_date => srp.start_date,
523 p_end_date => srp.start_date,
525 p_revert_to_state => 'CALC',
526 p_event_log_id => null,
527 p_org_id => g_org_id);
528 END IF;
529 END LOOP;
530 END LOOP;
531
532 commit;
533
534 g_calc_type := cn_calc_sub_batches_pkg.get_calc_type(p_physical_batch_id);
535
536 OPEN rev_class_hierarchy_id;
537 FETCH rev_class_hierarchy_id INTO l_rev_class_hierarchy_id;
538 CLOSE rev_class_hierarchy_id;
539
540 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
541 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
542 'cn.plsql.cn_calc_populate_pvt.populate_batch.progress',
543 'Populating transactions.');
544 end if;
545
546 for rep in salesreps loop
547 i := 1;
548
549 if (l_rev_class_hierarchy_id is not null) then
550 for plan in plan_info(rep.credited_salesrep_id,
551 rep.credited_comp_group_id,
552 rep.revenue_class_id,
553 rep.start_date,
554 rep.end_date)
555 loop
556 select nvl(indirect_credit, 'ALL') into l_indirect_credit
557 from cn_quotas_all
558 where quota_id = plan.quota_id;
559
560 select manager_flag into l_manager_flag
561 from cn_roles
562 where role_id = plan.role_id;
563
564 if (i = 1) then
565 l_start_date := plan.start_date;
566 l_end_date := plan.end_date;
567 g_end_date := l_end_date;
568
569 update cn_commission_lines_all cl
570 set cl.srp_plan_assign_id = plan.srp_plan_assign_id,
571 cl.quota_id = plan.quota_id,
572 cl.quota_rule_id = plan.quota_rule_id,
573 cl.status = 'POP',
574 cl.role_id = plan.role_id,
575 cl.pay_period_id = cl.processed_period_id
576 where cl.credited_salesrep_id = rep.credited_salesrep_id
577 and cl.credited_comp_group_id = rep.credited_comp_group_id
578 and cl.processed_period_id between rep.period_id and rep.end_period_id
579 and cl.processed_date between l_start_date and l_end_date
580 and cl.created_during in ('ROLL', 'TROLL')
581 and cl.status IN ('ROLL')
582 and cl.org_id = g_org_id
583 and cl.quota_id IS NULL
584 and cl.revenue_class_id = rep.revenue_class_id
585 and (substr(cl.pre_processed_code, 3,1) = 'P' or
586 (substr(cl.pre_processed_code, 3,1) = 'N' and
587 cl.direct_salesrep_id <> cl.credited_salesrep_id))
588 and ((l_indirect_credit = 'ALL') or
589 (l_indirect_credit = 'MGR' and l_manager_flag = 'Y') or
590 (l_indirect_credit = 'MGR' and l_manager_flag = 'N' and cl.direct_salesrep_id = cl.credited_salesrep_id) or
591 (l_indirect_credit = 'NONE' and cl.direct_salesrep_id = cl.credited_salesrep_id));
592 else
593 if (g_end_date >= plan.start_date) then
594 l_start_date := plan.start_date;
595 if (g_end_date < plan.end_date) then
596 l_end_date := g_end_date;
597 else
598 l_end_date := plan.end_date;
599 end if;
600
601 merge into cn_commission_lines_all cl
602 using (select commission_line_id,
603 commission_header_id,
604 direct_salesrep_id,
605 pre_processed_code,
606 revenue_class_id,
607 credited_salesrep_id,
608 credited_comp_group_id,
609 role_id,
610 processed_period_id,
611 pay_period_id,
612 pending_status,
613 srp_plan_assign_id,
614 quota_id,
615 quota_rule_id,
616 status,
617 created_during,
618 payee_line_id,
619 trx_type,
620 processed_date,
621 creation_date,
622 created_by,
623 org_id,
624 rollup_level
625 from cn_commission_lines_all cl2
626 where credited_salesrep_id = rep.credited_salesrep_id
627 and processed_date between l_start_date and l_end_date
628 and processed_period_id between rep.period_id and rep.end_period_id
629 and credited_comp_group_id = rep.credited_comp_group_id
630 and created_during in ('ROLL', 'TROLL')
631 and status <> 'OBSOLETE'
632 and org_id = g_org_id
633 and revenue_class_id = rep.revenue_class_id
634 and not exists (select 1 from cn_commission_lines_all
635 where credited_salesrep_id = cl2.credited_salesrep_id
636 and commission_header_id = cl2.commission_header_id
637 and srp_plan_assign_id = plan.srp_plan_assign_id
638 and quota_id = plan.quota_id)
639 and ((substr(pre_processed_code, 3,1) = 'P') or
643 (l_indirect_credit = 'MGR' and l_manager_flag = 'Y') or
640 (substr(pre_processed_code, 3,1) = 'N' and
641 direct_salesrep_id <> credited_salesrep_id))
642 and ((l_indirect_credit = 'ALL') or
644 (l_indirect_credit = 'MGR' and l_manager_flag = 'N' and
645 direct_salesrep_id = credited_salesrep_id) or
646 (l_indirect_credit = 'NONE' and direct_salesrep_id = credited_salesrep_id))) s
647 on (cl.commission_line_id = s.commission_line_id and s.status = 'ROLL')
648 when matched then
649 update set cl.srp_plan_assign_id = plan.srp_plan_assign_id,
650 cl.quota_id = plan.quota_id,
651 cl.quota_rule_id = plan.quota_rule_id,
652 cl.status = 'POP',
653 cl.role_id = plan.role_id,
654 cl.pay_period_id = cl.processed_period_id
655 when not matched then
656 insert(commission_line_id,
657 commission_header_id,
658 direct_salesrep_id,
659 pre_processed_code,
660 revenue_class_id,
661 credited_salesrep_id,
662 credited_comp_group_id,
663 role_id,
664 processed_period_id,
665 pay_period_id,
666 pending_status,
667 srp_plan_assign_id,
668 quota_id,
669 quota_rule_id,
670 status,
671 created_during,
672 payee_line_id,
673 trx_type,
674 processed_date,
675 creation_date,
676 created_by,
677 org_id,
678 rollup_level)
679 values(
680 cn_commission_lines_s.nextval,
681 s.commission_header_id,
682 s.direct_salesrep_id,
683 s.pre_processed_code,
684 s.revenue_class_id,
685 s.credited_salesrep_id,
686 s.credited_comp_group_id,
687 plan.role_id,
688 s.processed_period_id,
689 s.pay_period_id,
690 s.pending_status,
691 plan.srp_plan_assign_id,
692 plan.quota_id,
693 plan.quota_rule_id,
694 'POP',
695 'POP',
696 s.commission_line_id,
697 s.trx_type,
698 s.processed_date,
699 g_creation_date,
700 g_created_by,
701 g_org_id,
702 s.rollup_level);
703 end if;
704 if (g_end_date < plan.end_date) then
705 if (plan.start_date > g_end_date) then
706 l_start_date := plan.start_date;
707 else
708 l_start_date := g_end_date + 1;
709 end if;
710 l_end_date := plan.end_date;
711 g_end_date := plan.end_date;
712
713 update cn_commission_lines_all cl
714 set cl.srp_plan_assign_id = plan.srp_plan_assign_id,
715 cl.quota_id = plan.quota_id,
716 cl.quota_rule_id = plan.quota_rule_id,
717 cl.status = 'POP',
718 cl.role_id = plan.role_id,
719 cl.pay_period_id = cl.processed_period_id
720 where cl.credited_salesrep_id = rep.credited_salesrep_id
721 and cl.credited_comp_group_id = rep.credited_comp_group_id
722 and cl.processed_period_id between rep.period_id and rep.end_period_id
723 and cl.processed_date between l_start_date and l_end_date
724 and cl.created_during in ('ROLL', 'TROLL')
725 and cl.org_id = g_org_id
726 and cl.status IN ('ROLL')
727 and cl.quota_id IS NULL
728 and cl.revenue_class_id = rep.revenue_class_id
729 and (substr(cl.pre_processed_code, 3,1) = 'P' or
730 (substr(cl.pre_processed_code, 3,1) = 'N' and
731 cl.direct_salesrep_id <> cl.credited_salesrep_id))
732 and ((l_indirect_credit = 'ALL') or
733 (l_indirect_credit = 'MGR' and l_manager_flag = 'Y') or
734 (l_indirect_credit = 'MGR' and l_manager_flag = 'N' and cl.direct_salesrep_id = cl.credited_salesrep_id) or
735 (l_indirect_credit = 'NONE' and cl.direct_salesrep_id = cl.credited_salesrep_id));
736 end if;
737 end if;
738 i := i + 1;
739 end loop;
740 else -- no revenue hierarchy
741 for plan in plan_info2(rep.credited_salesrep_id,
742 rep.credited_comp_group_id,
743 rep.revenue_class_id,
744 rep.start_date,
745 rep.end_date)
746 loop
747 if (i = 1) then
748 l_start_date := plan.start_date;
749 l_end_date := plan.end_date;
750 g_end_date := l_end_date;
751 update cn_commission_lines_all cl
752 set cl.srp_plan_assign_id = plan.srp_plan_assign_id,
753 cl.quota_id = plan.quota_id,
754 cl.quota_rule_id = plan.quota_rule_id,
755 cl.status = 'POP',
756 cl.role_id = plan.role_id,
757 cl.pay_period_id = cl.processed_period_id
758 where cl.credited_salesrep_id = rep.credited_salesrep_id
759 and cl.credited_comp_group_id = rep.credited_comp_group_id
760 and cl.processed_period_id between rep.period_id and rep.end_period_id
761 and cl.processed_date between l_start_date and l_end_date
762 and cl.created_during in ('ROLL', 'TROLL')
763 and cl.status IN ('ROLL')
764 and cl.org_id = g_org_id
768 (substr(cl.pre_processed_code, 3,1) = 'N' and
765 and cl.quota_id IS NULL
766 and cl.revenue_class_id = rep.revenue_class_id
767 and (substr(cl.pre_processed_code, 3,1) = 'P' or
769 cl.direct_salesrep_id <> cl.credited_salesrep_id));
770 else
771 if (g_end_date >= plan.start_date) then
772 l_start_date := plan.start_date;
773 if (g_end_date < plan.end_date) then
774 l_end_date := g_end_date;
775 else
776 l_end_date := plan.end_date;
777 end if;
778 merge into cn_commission_lines_all cl
779 using (select commission_line_id,
780 commission_header_id,
781 direct_salesrep_id,
782 pre_processed_code,
783 revenue_class_id,
784 credited_salesrep_id,
785 credited_comp_group_id,
786 role_id,
787 processed_period_id,
788 pay_period_id,
789 pending_status,
790 srp_plan_assign_id,
791 quota_id,
792 quota_rule_id,
793 status,
794 created_during,
795 payee_line_id,
796 trx_type,
797 processed_date,
798 creation_date,
799 created_by,
800 org_id,
801 rollup_level
802 from cn_commission_lines_all cl2
803 where credited_salesrep_id = rep.credited_salesrep_id
804 and processed_date between l_start_date and l_end_date
805 and processed_period_id between rep.period_id and rep.end_period_id
806 and credited_comp_group_id = rep.credited_comp_group_id
807 and created_during in ('ROLL', 'TROLL')
808 and status <> 'OBSOLETE'
809 and org_id = g_org_id
810 and revenue_class_id = rep.revenue_class_id
811 and not exists (select 1 from cn_commission_lines_all
812 where credited_salesrep_id = cl2.credited_salesrep_id
813 and commission_header_id = cl2.commission_header_id
814 and srp_plan_assign_id = plan.srp_plan_assign_id
815 and quota_id = plan.quota_id)
816 and ((substr(pre_processed_code, 3,1) = 'P') or
817 (substr(pre_processed_code, 3,1) = 'N' and
818 direct_salesrep_id <> credited_salesrep_id))
819 and ((l_indirect_credit = 'ALL') or
820 (l_indirect_credit = 'MGR' and l_manager_flag = 'Y') or
821 (l_indirect_credit = 'MGR' and l_manager_flag = 'N' and
822 direct_salesrep_id = credited_salesrep_id) or
823 (l_indirect_credit = 'NONE' and direct_salesrep_id = credited_salesrep_id))) s
824 on (cl.commission_line_id = s.commission_line_id and s.status = 'ROLL')
825 when matched then
826 update set cl.srp_plan_assign_id = plan.srp_plan_assign_id,
827 cl.quota_id = plan.quota_id,
828 cl.quota_rule_id = plan.quota_rule_id,
829 cl.status = 'POP',
830 cl.role_id = plan.role_id,
831 cl.pay_period_id = cl.processed_period_id
832 when not matched then
833 insert(commission_line_id,
834 commission_header_id,
835 direct_salesrep_id,
836 pre_processed_code,
837 revenue_class_id,
838 credited_salesrep_id,
839 credited_comp_group_id,
840 role_id,
841 processed_period_id,
842 pay_period_id,
843 pending_status,
844 srp_plan_assign_id,
845 quota_id,
846 quota_rule_id,
847 status,
848 created_during,
849 payee_line_id,
850 trx_type,
851 processed_date,
852 creation_date,
853 created_by,
854 org_id,
855 rollup_level)
856 values(
857 cn_commission_lines_s.nextval,
858 s.commission_header_id,
859 s.direct_salesrep_id,
860 s.pre_processed_code,
861 s.revenue_class_id,
862 s.credited_salesrep_id,
863 s.credited_comp_group_id,
864 plan.role_id,
865 s.processed_period_id,
866 s.pay_period_id,
867 s.pending_status,
868 plan.srp_plan_assign_id,
869 plan.quota_id,
870 plan.quota_rule_id,
871 'POP',
872 'POP',
873 s.commission_line_id,
874 s.trx_type,
875 s.processed_date,
876 g_creation_date,
877 g_created_by,
878 g_org_id,
879 s.rollup_level);
880 end if;
881 if (g_end_date < plan.end_date) then
882 if (plan.start_date > g_end_date) then
883 l_start_date := plan.start_date;
884 else
885 l_start_date := g_end_date + 1;
886 end if;
887 l_end_date := plan.end_date;
891 cl.quota_id = plan.quota_id,
888 g_end_date := plan.end_date;
889 update cn_commission_lines_all cl
890 set cl.srp_plan_assign_id = plan.srp_plan_assign_id,
892 cl.quota_rule_id = plan.quota_rule_id,
893 cl.status = 'POP',
894 cl.role_id = plan.role_id,
895 cl.pay_period_id = cl.processed_period_id
896 where cl.credited_salesrep_id = rep.credited_salesrep_id
897 and cl.credited_comp_group_id = rep.credited_comp_group_id
898 and cl.processed_period_id between rep.period_id and rep.end_period_id
899 and cl.processed_date between l_start_date and l_end_date
900 and cl.created_during in ('ROLL', 'TROLL')
901 and cl.org_id = g_org_id
902 and cl.status IN ('ROLL')
903 and cl.quota_id IS NULL
904 and cl.revenue_class_id = rep.revenue_class_id
905 and (substr(cl.pre_processed_code, 3,1) = 'P' or
906 (substr(cl.pre_processed_code, 3,1) = 'N' and
907 cl.direct_salesrep_id <> cl.credited_salesrep_id));
908
909 end if;
910 end if;
911 i := i + 1;
912 end loop;
913 end if;
914
915 end loop;
916
917 commit;
918
919 --handle those trxs skipping POP phase. BUT only SOURCE trxs can skip POP phase.
920 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
921 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
922 'cn.plsql.cn_calc_populate_pvt.populate_batch.progress',
923 'Processing transactions that skip POP phase.');
924 end if;
925
926 l_processed_date_prev := To_date('01/01/1900', 'DD/MM/YYYY');
927 l_salesrep_id_prev := -999999;
928 l_role_id_prev := -999999;
929 l_quota_id_prev := -999999;
930 l_revenue_class_id_prev := -999999;
931
932 FOR srp IN l_skip_salesreps_csr LOOP
933 IF l_processed_date_prev <> srp.processed_date THEN
934 l_dim_hierarchy_id := null;
935
936 OPEN l_dim_hierarchy_csr(srp.processed_date);
937 FETCH l_dim_hierarchy_csr INTO l_dim_hierarchy_id;
938 CLOSE l_dim_hierarchy_csr;
939 END IF;
940
941 -- must be able to figure out srp_plan_assign_id
942 IF l_processed_date_prev <> srp.processed_date OR l_salesrep_id_prev <> srp.salesrep_id
943 OR l_role_id_prev <> srp.role_id THEN
944 l_srp_plan_assign_id := NULL;
945
946 OPEN l_spa_csr ( srp.processed_date, srp.salesrep_id, srp.role_id );
947 FETCH l_spa_csr INTO l_srp_plan_assign_id;
948 CLOSE l_spa_csr;
949
950 IF l_srp_plan_assign_id IS NULL THEN
951 GOTO end_of_skip_loop;
952 END IF;
953 ELSE
954 IF l_srp_plan_assign_id IS NULL THEN
955 GOTO end_of_skip_loop;
956 END IF;
957 END IF;
958
959 -- must be able to figure out quota_rule_id
960 IF l_quota_id_prev <> srp.quota_id OR l_revenue_class_id_prev <> srp.revenue_class_id THEN
961 l_quota_rule_id := NULL;
962
963 IF l_dim_hierarchy_id IS NULL THEN
964 OPEN l_quota_rule_no_hier_csr ( srp.quota_id, srp.revenue_class_id );
965 FETCH l_quota_rule_no_hier_csr INTO l_quota_rule_id;
966 CLOSE l_quota_rule_no_hier_csr;
967 ELSE
968 OPEN l_quota_rule_hier_csr ( srp.quota_id, srp.revenue_class_id );
969 FETCH l_quota_rule_hier_csr INTO l_quota_rule_id;
970 CLOSE l_quota_rule_hier_csr;
971 END IF;
972
973 IF l_quota_rule_id IS NULL THEN
974 GOTO end_of_skip_loop;
975 END IF;
976 ELSE
977 IF l_quota_rule_id IS NULL THEN
978 GOTO end_of_skip_loop;
979 END IF;
980 END IF;
981
982 IF l_srp_plan_assign_id IS NOT NULL AND l_quota_rule_id IS NOT NULL THEN
983 UPDATE cn_commission_lines_all cl
984 SET cl.status = 'POP',
985 cl.role_id = srp.role_id,
986 cl.srp_plan_assign_id = l_srp_plan_assign_id,
987 cl.quota_id = srp.quota_id,
988 cl.quota_rule_id = l_quota_rule_id,
989 cl.pay_period_id = cl.processed_period_id
990 WHERE cl.credited_salesrep_id = srp.salesrep_id
991 AND cl.processed_period_id = srp.processed_period_id
992 AND cl.processed_date = srp.processed_date
993 AND cl.org_id = g_org_id
994 AND cl.status IN ('ROLL')
995 AND ((g_calc_type = 'COMMISSION' AND cl.trx_type NOT IN ('BONUS', 'GRP', 'FORECAST')) OR
996 (g_calc_type = 'FORECAST' AND cl.trx_type = 'FORECAST'))
997 AND exists
998 (SELECT 1
999 FROM cn_commission_headers_all ch
1000 WHERE ch.commission_header_id = cl.commission_header_id
1001 AND ch.role_id = srp.role_id
1002 AND ch.quota_id = srp.quota_id
1003 AND substr(ch.pre_processed_code,3,1) = 'N'
1004 AND ch.direct_salesrep_id = srp.salesrep_id );
1005 END IF;
1006
1007 <<end_of_skip_loop>>
1008
1009 l_processed_date_prev := srp.processed_date;
1010 l_salesrep_id_prev := srp.salesrep_id;
1011 l_role_id_prev := srp.role_id;
1012 l_quota_id_prev := srp.quota_id;
1013 l_revenue_class_id_prev := srp.revenue_class_id;
1014
1015 END LOOP;
1016
1017 commit;
1018
1019 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1020 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1026 SET cl.status = 'XPOP',
1021 'cn.plsql.cn_calc_populate_pvt.populate_batch.progress',
1022 'Updating unpopulated transactions to XPOP.');
1023 end if;
1024
1025 UPDATE cn_commission_lines_all cl
1027 quota_id = NULL,
1028 quota_rule_id = NULL,
1029 role_id =NULL,
1030 srp_plan_assign_id = NULL
1031 WHERE cl.commission_line_id IN
1032 (SELECT line.commission_line_id
1033 FROM cn_commission_lines_all line,
1034 cn_process_batches_all pb
1035 WHERE pb.physical_batch_id = p_physical_batch_id
1036 AND line.credited_salesrep_id = pb.salesrep_id
1037 AND line.processed_period_id BETWEEN pb.period_id AND pb.end_period_id
1038 AND line.processed_date BETWEEN pb.start_date AND pb.end_date
1039 AND line.status = 'ROLL'
1040 AND line.org_id = g_org_id
1041 AND ((g_calc_type = 'COMMISSION' AND line.trx_type NOT IN ('BONUS', 'GRP', 'FORECAST')) OR
1042 (g_calc_type = 'FORECAST' AND line.trx_type = 'FORECAST')));
1043
1044 commit;
1045
1046 -- Standard call to get message count and if count is 1, get message info.
1047 FND_MSG_PUB.Count_And_Get
1048 ( p_count => x_msg_count ,
1049 p_data => x_msg_data ,
1050 p_encoded => FND_API.G_FALSE
1051 );
1052
1053 EXCEPTION
1054 WHEN FND_API.G_EXC_ERROR THEN
1055 x_return_status := FND_API.G_RET_STS_ERROR ;
1056 FND_MSG_PUB.Count_And_Get
1057 (p_count => x_msg_count ,
1058 p_data => x_msg_data ,
1059 p_encoded => FND_API.G_FALSE
1060 );
1061
1062 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1063 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1064 FND_MSG_PUB.Count_And_Get
1065 (p_count => x_msg_count ,
1066 p_data => x_msg_data ,
1067 p_encoded => FND_API.G_FALSE
1068 );
1069
1070 WHEN OTHERS THEN
1071 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1072 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1073 THEN
1074 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
1075 END IF;
1076 FND_MSG_PUB.Count_And_Get
1077 (p_count => x_msg_count ,
1078 p_data => x_msg_data ,
1079 p_encoded => FND_API.G_FALSE
1080 );
1081
1082 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1083 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
1084 'cn.plsql.cn_calc_populate_pvt.populate_batch.exception',
1085 sqlerrm);
1086 end if;
1087 fnd_file.put_line(fnd_file.log, 'In populate_batch: '||sqlerrm);
1088 cn_message_pkg.debug('Exception occurs in cn_calc_populate_pvt.populate_batch:');
1089 cn_message_pkg.debug(sqlerrm);
1090 END populate_batch;
1091
1092 END cn_calc_populate_pvt;