[Home] [Help]
PACKAGE BODY: APPS.CN_CALC_POPULATE_PVT
Source
1 PACKAGE BODY cn_calc_populate_pvt AS
2 -- $Header: cnvcpopb.pls 120.13 2011/03/10 06:03:31 rnagired 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;
132 cn_message_pkg.debug('sqlerrm');
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:');
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,
277 nvl(q.end_date, p_end_date),
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),
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
386 AND cl.processed_period_id between pb.period_id AND pb.end_period_id
390 AND ((g_calc_type = 'COMMISSION'
387 AND cl.processed_date BETWEEN pb.start_date AND pb.end_date
388 AND cl.org_id = g_org_id
389 AND cl.status IN ('ROLL')
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,
521 p_period_id => srp.period_id,
522 p_start_date => srp.start_date,
523 p_end_date => srp.start_date,
524 p_quota_id => NULL,
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
539
536 OPEN rev_class_hierarchy_id;
537 FETCH rev_class_hierarchy_id INTO l_rev_class_hierarchy_id;
538 CLOSE rev_class_hierarchy_id;
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 /*+ index(cl cn_commission_lines_n7) */ 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
640 (substr(pre_processed_code, 3,1) = 'N' and
641 direct_salesrep_id <> credited_salesrep_id))
642 and ((l_indirect_credit = 'ALL') or
643 (l_indirect_credit = 'MGR' and l_manager_flag = 'Y') 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 not matched then
649 insert(commission_line_id,
650 commission_header_id,
651 direct_salesrep_id,
652 pre_processed_code,
653 revenue_class_id,
654 credited_salesrep_id,
655 credited_comp_group_id,
656 role_id,
660 srp_plan_assign_id,
657 processed_period_id,
658 pay_period_id,
659 pending_status,
661 quota_id,
662 quota_rule_id,
663 status,
664 created_during,
665 payee_line_id,
666 trx_type,
667 processed_date,
668 creation_date,
669 created_by,
670 org_id,
671 rollup_level)
672 values(
673 cn_commission_lines_s.nextval,
674 s.commission_header_id,
675 s.direct_salesrep_id,
676 s.pre_processed_code,
677 s.revenue_class_id,
678 s.credited_salesrep_id,
679 s.credited_comp_group_id,
680 plan.role_id,
681 s.processed_period_id,
682 s.pay_period_id,
683 s.pending_status,
684 plan.srp_plan_assign_id,
685 plan.quota_id,
686 plan.quota_rule_id,
687 'POP',
688 'POP',
689 s.commission_line_id,
690 s.trx_type,
691 s.processed_date,
692 g_creation_date,
693 g_created_by,
694 g_org_id,
695 s.rollup_level);
696
697
698 UPDATE cn_commission_lines_all cl2
699 SET cl2.srp_plan_assign_id = plan.srp_plan_assign_id,
700 cl2.quota_id = plan.quota_id,
701 cl2.quota_rule_id = plan.quota_rule_id,
702 cl2.status = 'POP',
703 cl2.role_id = plan.role_id,
704 cl2.pay_period_id = cl2.processed_period_id
705 WHERE credited_salesrep_id = rep.credited_salesrep_id
706 AND processed_date BETWEEN l_start_date AND l_end_date
707 AND processed_period_id BETWEEN rep.period_id AND rep.end_period_id
708 AND credited_comp_group_id = rep.credited_comp_group_id
709 AND created_during IN ('ROLL', 'TROLL')
710 AND status = 'ROLL'
711 AND org_id = g_org_id
712 AND revenue_class_id = rep.revenue_class_id
713 AND NOT EXISTS
714 (SELECT 1
715 FROM cn_commission_lines_all
716 WHERE credited_salesrep_id = cl2.credited_salesrep_id
717 AND commission_header_id = cl2.commission_header_id
718 AND srp_plan_assign_id = plan.srp_plan_assign_id
719 AND quota_id = plan.quota_id
720 )
721 AND ((SUBSTR(pre_processed_code, 3,1) = 'P')
722 OR (SUBSTR(pre_processed_code, 3,1) = 'N'
723 AND direct_salesrep_id <> credited_salesrep_id))
724 AND ((l_indirect_credit = 'ALL')
725 OR (l_indirect_credit = 'MGR'
726 AND l_manager_flag = 'Y')
727 OR (l_indirect_credit = 'MGR'
728 AND l_manager_flag = 'N'
729 AND direct_salesrep_id = credited_salesrep_id)
730 OR (l_indirect_credit = 'NONE'
731 AND direct_salesrep_id = credited_salesrep_id));
732
733 end if;
734 if (g_end_date < plan.end_date) then
735 if (plan.start_date > g_end_date) then
736 l_start_date := plan.start_date;
737 else
738 l_start_date := g_end_date + 1;
739 end if;
740 l_end_date := plan.end_date;
741 g_end_date := plan.end_date;
742
743 update /*+ index(cl cn_commission_lines_n7) */ cn_commission_lines_all cl
744 set cl.srp_plan_assign_id = plan.srp_plan_assign_id,
745 cl.quota_id = plan.quota_id,
746 cl.quota_rule_id = plan.quota_rule_id,
747 cl.status = 'POP',
748 cl.role_id = plan.role_id,
749 cl.pay_period_id = cl.processed_period_id
750 where cl.credited_salesrep_id = rep.credited_salesrep_id
751 and cl.credited_comp_group_id = rep.credited_comp_group_id
752 and cl.processed_period_id between rep.period_id and rep.end_period_id
753 and cl.processed_date between l_start_date and l_end_date
754 and cl.created_during in ('ROLL', 'TROLL')
755 and cl.org_id = g_org_id
756 and cl.status IN ('ROLL')
757 and cl.quota_id IS NULL
758 and cl.revenue_class_id = rep.revenue_class_id
759 and (substr(cl.pre_processed_code, 3,1) = 'P' or
760 (substr(cl.pre_processed_code, 3,1) = 'N' and
761 cl.direct_salesrep_id <> cl.credited_salesrep_id))
762 and ((l_indirect_credit = 'ALL') or
763 (l_indirect_credit = 'MGR' and l_manager_flag = 'Y') or
764 (l_indirect_credit = 'MGR' and l_manager_flag = 'N' and cl.direct_salesrep_id = cl.credited_salesrep_id) or
765 (l_indirect_credit = 'NONE' and cl.direct_salesrep_id = cl.credited_salesrep_id));
766 end if;
767 end if;
768 i := i + 1;
769 end loop;
770 else -- no revenue hierarchy
771 for plan in plan_info2(rep.credited_salesrep_id,
772 rep.credited_comp_group_id,
773 rep.revenue_class_id,
774 rep.start_date,
775 rep.end_date)
776 loop
777 if (i = 1) then
778 l_start_date := plan.start_date;
779 l_end_date := plan.end_date;
780 g_end_date := l_end_date;
781 update cn_commission_lines_all cl
782 set cl.srp_plan_assign_id = plan.srp_plan_assign_id,
783 cl.quota_id = plan.quota_id,
784 cl.quota_rule_id = plan.quota_rule_id,
785 cl.status = 'POP',
789 and cl.credited_comp_group_id = rep.credited_comp_group_id
786 cl.role_id = plan.role_id,
787 cl.pay_period_id = cl.processed_period_id
788 where cl.credited_salesrep_id = rep.credited_salesrep_id
790 and cl.processed_period_id between rep.period_id and rep.end_period_id
791 and cl.processed_date between l_start_date and l_end_date
792 and cl.created_during in ('ROLL', 'TROLL')
793 and cl.status IN ('ROLL')
794 and cl.org_id = g_org_id
795 and cl.quota_id IS NULL
796 and cl.revenue_class_id = rep.revenue_class_id
797 and (substr(cl.pre_processed_code, 3,1) = 'P' or
798 (substr(cl.pre_processed_code, 3,1) = 'N' and
799 cl.direct_salesrep_id <> cl.credited_salesrep_id));
800 else
801 if (g_end_date >= plan.start_date) then
802 l_start_date := plan.start_date;
803 if (g_end_date < plan.end_date) then
804 l_end_date := g_end_date;
805 else
806 l_end_date := plan.end_date;
807 end if;
808 merge into cn_commission_lines_all cl
809 using (select commission_line_id,
810 commission_header_id,
811 direct_salesrep_id,
812 pre_processed_code,
813 revenue_class_id,
814 credited_salesrep_id,
815 credited_comp_group_id,
816 role_id,
817 processed_period_id,
818 pay_period_id,
819 pending_status,
820 srp_plan_assign_id,
821 quota_id,
822 quota_rule_id,
823 status,
824 created_during,
825 payee_line_id,
826 trx_type,
827 processed_date,
828 creation_date,
829 created_by,
830 org_id,
831 rollup_level
832 from cn_commission_lines_all cl2
833 where credited_salesrep_id = rep.credited_salesrep_id
834 and processed_date between l_start_date and l_end_date
835 and processed_period_id between rep.period_id and rep.end_period_id
836 and credited_comp_group_id = rep.credited_comp_group_id
837 and created_during in ('ROLL', 'TROLL')
838 and status <> 'OBSOLETE'
839 and org_id = g_org_id
840 and revenue_class_id = rep.revenue_class_id
841 and not exists (select 1 from cn_commission_lines_all
842 where credited_salesrep_id = cl2.credited_salesrep_id
843 and commission_header_id = cl2.commission_header_id
844 and srp_plan_assign_id = plan.srp_plan_assign_id
845 and quota_id = plan.quota_id)
846 and ((substr(pre_processed_code, 3,1) = 'P') or
847 (substr(pre_processed_code, 3,1) = 'N' and
848 direct_salesrep_id <> credited_salesrep_id))
849 and ((l_indirect_credit = 'ALL') or
850 (l_indirect_credit = 'MGR' and l_manager_flag = 'Y') or
851 (l_indirect_credit = 'MGR' and l_manager_flag = 'N' and
852 direct_salesrep_id = credited_salesrep_id) or
853 (l_indirect_credit = 'NONE' and direct_salesrep_id = credited_salesrep_id))) s
854 on (cl.commission_line_id = s.commission_line_id and s.status = 'ROLL')
855 when not matched then
856 insert(commission_line_id,
857 commission_header_id,
858 direct_salesrep_id,
859 pre_processed_code,
860 revenue_class_id,
861 credited_salesrep_id,
862 credited_comp_group_id,
863 role_id,
864 processed_period_id,
865 pay_period_id,
866 pending_status,
867 srp_plan_assign_id,
868 quota_id,
869 quota_rule_id,
870 status,
871 created_during,
872 payee_line_id,
873 trx_type,
874 processed_date,
875 creation_date,
876 created_by,
877 org_id,
878 rollup_level)
879 values(
880 cn_commission_lines_s.nextval,
881 s.commission_header_id,
882 s.direct_salesrep_id,
883 s.pre_processed_code,
884 s.revenue_class_id,
885 s.credited_salesrep_id,
886 s.credited_comp_group_id,
887 plan.role_id,
888 s.processed_period_id,
889 s.pay_period_id,
890 s.pending_status,
891 plan.srp_plan_assign_id,
892 plan.quota_id,
893 plan.quota_rule_id,
894 'POP',
895 'POP',
896 s.commission_line_id,
897 s.trx_type,
898 s.processed_date,
899 g_creation_date,
900 g_created_by,
901 g_org_id,
902 s.rollup_level);
903
904
905 UPDATE cn_commission_lines_all cl2
906 SET cl2.srp_plan_assign_id = plan.srp_plan_assign_id,
907 cl2.quota_id = plan.quota_id,
908 cl2.quota_rule_id = plan.quota_rule_id,
909 cl2.status = 'POP',
910 cl2.role_id = plan.role_id,
914 AND processed_period_id BETWEEN rep.period_id AND rep.end_period_id
911 cl2.pay_period_id = cl2.processed_period_id
912 WHERE credited_salesrep_id = rep.credited_salesrep_id
913 AND processed_date BETWEEN l_start_date AND l_end_date
915 AND credited_comp_group_id = rep.credited_comp_group_id
916 AND created_during IN ('ROLL', 'TROLL')
917 AND status = 'ROLL'
918 AND org_id = g_org_id
919 AND revenue_class_id = rep.revenue_class_id
920 AND NOT EXISTS
921 (SELECT 1
922 FROM cn_commission_lines_all
923 WHERE credited_salesrep_id = cl2.credited_salesrep_id
924 AND commission_header_id = cl2.commission_header_id
925 AND srp_plan_assign_id = plan.srp_plan_assign_id
926 AND quota_id = plan.quota_id
927 )
928 AND ((SUBSTR(pre_processed_code, 3,1) = 'P')
929 OR (SUBSTR(pre_processed_code, 3,1) = 'N'
930 AND direct_salesrep_id <> credited_salesrep_id))
931 AND ((l_indirect_credit = 'ALL')
932 OR (l_indirect_credit = 'MGR'
933 AND l_manager_flag = 'Y')
934 OR (l_indirect_credit = 'MGR'
935 AND l_manager_flag = 'N'
936 AND direct_salesrep_id = credited_salesrep_id)
937 OR (l_indirect_credit = 'NONE'
938 AND direct_salesrep_id = credited_salesrep_id));
939
940
941 end if;
942 if (g_end_date < plan.end_date) then
943 if (plan.start_date > g_end_date) then
944 l_start_date := plan.start_date;
945 else
946 l_start_date := g_end_date + 1;
947 end if;
948 l_end_date := plan.end_date;
949 g_end_date := plan.end_date;
950 update cn_commission_lines_all cl
951 set cl.srp_plan_assign_id = plan.srp_plan_assign_id,
952 cl.quota_id = plan.quota_id,
953 cl.quota_rule_id = plan.quota_rule_id,
954 cl.status = 'POP',
955 cl.role_id = plan.role_id,
956 cl.pay_period_id = cl.processed_period_id
957 where cl.credited_salesrep_id = rep.credited_salesrep_id
958 and cl.credited_comp_group_id = rep.credited_comp_group_id
959 and cl.processed_period_id between rep.period_id and rep.end_period_id
960 and cl.processed_date between l_start_date and l_end_date
961 and cl.created_during in ('ROLL', 'TROLL')
962 and cl.org_id = g_org_id
963 and cl.status IN ('ROLL')
964 and cl.quota_id IS NULL
965 and cl.revenue_class_id = rep.revenue_class_id
966 and (substr(cl.pre_processed_code, 3,1) = 'P' or
967 (substr(cl.pre_processed_code, 3,1) = 'N' and
968 cl.direct_salesrep_id <> cl.credited_salesrep_id));
969
970 end if;
971 end if;
972 i := i + 1;
973 end loop;
974 end if;
975
976 end loop;
977
978 commit;
979
980 --handle those trxs skipping POP phase. BUT only SOURCE trxs can skip POP phase.
981 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
982 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
983 'cn.plsql.cn_calc_populate_pvt.populate_batch.progress',
984 'Processing transactions that skip POP phase.');
985 end if;
986
987 l_processed_date_prev := To_date('01/01/1900', 'DD/MM/YYYY');
988 l_salesrep_id_prev := -999999;
989 l_role_id_prev := -999999;
990 l_quota_id_prev := -999999;
991 l_revenue_class_id_prev := -999999;
992
993 FOR srp IN l_skip_salesreps_csr LOOP
994 IF l_processed_date_prev <> srp.processed_date THEN
995 l_dim_hierarchy_id := null;
996
997 OPEN l_dim_hierarchy_csr(srp.processed_date);
998 FETCH l_dim_hierarchy_csr INTO l_dim_hierarchy_id;
999 CLOSE l_dim_hierarchy_csr;
1000 END IF;
1001
1002 -- must be able to figure out srp_plan_assign_id
1003 IF l_processed_date_prev <> srp.processed_date OR l_salesrep_id_prev <> srp.salesrep_id
1004 OR l_role_id_prev <> srp.role_id THEN
1005 l_srp_plan_assign_id := NULL;
1006
1007 OPEN l_spa_csr ( srp.processed_date, srp.salesrep_id, srp.role_id );
1008 FETCH l_spa_csr INTO l_srp_plan_assign_id;
1009 CLOSE l_spa_csr;
1010
1011 IF l_srp_plan_assign_id IS NULL THEN
1012 GOTO end_of_skip_loop;
1013 END IF;
1014 ELSE
1015 IF l_srp_plan_assign_id IS NULL THEN
1016 GOTO end_of_skip_loop;
1017 END IF;
1018 END IF;
1019
1020 -- must be able to figure out quota_rule_id
1021 -- IF l_quota_id_prev <> srp.quota_id OR l_revenue_class_id_prev <> srp.revenue_class_id THEN
1022 -- l_quota_rule_id := NULL;
1023
1024 IF l_dim_hierarchy_id IS NULL THEN
1025 OPEN l_quota_rule_no_hier_csr ( srp.quota_id, srp.revenue_class_id );
1026 FETCH l_quota_rule_no_hier_csr INTO l_quota_rule_id;
1027 CLOSE l_quota_rule_no_hier_csr;
1028 ELSE
1029 OPEN l_quota_rule_hier_csr ( srp.quota_id, srp.revenue_class_id );
1030 FETCH l_quota_rule_hier_csr INTO l_quota_rule_id;
1031 CLOSE l_quota_rule_hier_csr;
1032 END IF;
1033
1034 IF l_quota_rule_id IS NULL THEN
1035 GOTO end_of_skip_loop;
1036 END IF;
1037 /* ELSE
1038 IF l_quota_rule_id IS NULL THEN
1039 GOTO end_of_skip_loop;
1040 END IF;
1041 END IF;
1042 */
1046 cl.role_id = srp.role_id,
1043 IF l_srp_plan_assign_id IS NOT NULL AND l_quota_rule_id IS NOT NULL THEN
1044 UPDATE cn_commission_lines_all cl
1045 SET cl.status = 'POP',
1047 cl.srp_plan_assign_id = l_srp_plan_assign_id,
1048 cl.quota_id = srp.quota_id,
1049 cl.quota_rule_id = l_quota_rule_id,
1050 cl.pay_period_id = cl.processed_period_id
1051 WHERE cl.credited_salesrep_id = srp.salesrep_id
1052 AND cl.processed_period_id = srp.processed_period_id
1053 AND cl.processed_date = srp.processed_date
1054 AND cl.org_id = g_org_id
1055 AND cl.status IN ('ROLL')
1056 AND ((g_calc_type = 'COMMISSION' AND cl.trx_type NOT IN ('BONUS', 'GRP', 'FORECAST')) OR
1057 (g_calc_type = 'FORECAST' AND cl.trx_type = 'FORECAST'))
1058 AND exists
1059 (SELECT 1
1060 FROM cn_commission_headers_all ch
1061 WHERE ch.commission_header_id = cl.commission_header_id
1062 AND ch.role_id = srp.role_id
1063 AND ch.quota_id = srp.quota_id
1064 AND substr(ch.pre_processed_code,3,1) = 'N'
1065 AND ch.direct_salesrep_id = srp.salesrep_id );
1066 END IF;
1067
1068 <<end_of_skip_loop>>
1069
1070 l_processed_date_prev := srp.processed_date;
1071 l_salesrep_id_prev := srp.salesrep_id;
1072 l_role_id_prev := srp.role_id;
1073 l_quota_id_prev := srp.quota_id;
1074 l_revenue_class_id_prev := srp.revenue_class_id;
1075
1076 END LOOP;
1077
1078 commit;
1079
1080 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1081 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1082 'cn.plsql.cn_calc_populate_pvt.populate_batch.progress',
1083 'Updating unpopulated transactions to XPOP.');
1084 end if;
1085
1086 UPDATE cn_commission_lines_all cl
1087 SET cl.status = 'XPOP',
1088 quota_id = NULL,
1089 quota_rule_id = NULL,
1090 role_id =NULL,
1091 srp_plan_assign_id = NULL
1092 WHERE cl.commission_line_id IN
1093 (SELECT line.commission_line_id
1094 FROM cn_commission_lines_all line,
1095 cn_process_batches_all pb
1096 WHERE pb.physical_batch_id = p_physical_batch_id
1097 AND line.credited_salesrep_id = pb.salesrep_id
1098 AND line.processed_period_id BETWEEN pb.period_id AND pb.end_period_id
1099 AND line.processed_date BETWEEN pb.start_date AND pb.end_date
1100 AND line.status = 'ROLL'
1101 AND line.org_id = g_org_id
1102 AND ((g_calc_type = 'COMMISSION' AND line.trx_type NOT IN ('BONUS', 'GRP', 'FORECAST')) OR
1103 (g_calc_type = 'FORECAST' AND line.trx_type = 'FORECAST')));
1104
1105 commit;
1106
1107 -- Standard call to get message count and if count is 1, get message info.
1108 FND_MSG_PUB.Count_And_Get
1109 ( p_count => x_msg_count ,
1110 p_data => x_msg_data ,
1111 p_encoded => FND_API.G_FALSE
1112 );
1113
1114 EXCEPTION
1115 WHEN FND_API.G_EXC_ERROR THEN
1116 x_return_status := FND_API.G_RET_STS_ERROR ;
1117 FND_MSG_PUB.Count_And_Get
1118 (p_count => x_msg_count ,
1119 p_data => x_msg_data ,
1120 p_encoded => FND_API.G_FALSE
1121 );
1122
1123 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1124 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1125 FND_MSG_PUB.Count_And_Get
1126 (p_count => x_msg_count ,
1127 p_data => x_msg_data ,
1128 p_encoded => FND_API.G_FALSE
1129 );
1130
1131 WHEN OTHERS THEN
1132 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1133 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1134 THEN
1135 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
1136 END IF;
1137 FND_MSG_PUB.Count_And_Get
1138 (p_count => x_msg_count ,
1139 p_data => x_msg_data ,
1140 p_encoded => FND_API.G_FALSE
1141 );
1142
1143 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1144 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
1145 'cn.plsql.cn_calc_populate_pvt.populate_batch.exception',
1146 sqlerrm);
1147 end if;
1148 fnd_file.put_line(fnd_file.log, 'In populate_batch: '||sqlerrm);
1149 cn_message_pkg.debug('Exception occurs in cn_calc_populate_pvt.populate_batch:');
1150 cn_message_pkg.debug(sqlerrm);
1151 END populate_batch;
1152
1153 END cn_calc_populate_pvt;