[Home] [Help]
PACKAGE BODY: APPS.CN_CALC_SUBLEDGER_PVT
Source
1 PACKAGE BODY cn_calc_subledger_pvt AS
2 -- $Header: cnvcsubb.pls 120.8 2007/11/03 07:32:19 achanda ship $
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CN_CALC_SUBLEDGER_PVT';
5 g_org_id NUMBER;
6
7 FUNCTION get_max_period(p_quota_id NUMBER,
8 p_period_id NUMBER,
9 p_srp_plan_assign_id NUMBER) RETURN NUMBER IS
10 l_max_period_id NUMBER(15);
11 BEGIN
12 SELECT max(p2.cal_period_id)
13 INTO l_max_period_id
14 FROM cn_cal_per_int_types_all p2,
15 cn_srp_period_quotas_all cspq
16 WHERE p2.interval_type_id = (select interval_type_id
17 from cn_quotas_all
18 where quota_id = p_quota_id)
19 AND p2.interval_number = (select p1.interval_number
20 from cn_cal_per_int_types_all p1
21 where p1.cal_period_id = p_period_id
22 and p1.org_id = g_org_id
23 and p1.interval_type_id = (select interval_type_id
24 from cn_quotas_all
25 where quota_id = p_quota_id))
26 AND p2.org_id = g_org_id
27 AND cspq.srp_plan_assign_id = p_srp_plan_assign_id
28 AND cspq.quota_id = p_quota_id
29 AND cspq.period_id = p2.cal_period_id;
30
31 RETURN l_max_period_id;
32
33 END get_max_period;
34
35 --
36 -- Desc :
37 --
38 --
39 --
40 -- Parameters :
41 -- IN : p_api_version NUMBER Require
42 -- p_init_msg_list VARCHAR2 Optional (FND_API.G_FALSE)
43 -- p_commit VARCHAR2 Optional (FND_API.G_FALSE)
44 -- p_validation_level NUMBER Optional (FND_API.G_VALID_LEVEL_FULL)
45 -- OUT : x_return_status VARCHAR2(1)
46 -- x_msg_count NUMBER
47 -- x_msg_data VARCHAR2(2000)
48 -- IN : p_srp_subledger srp_subledger_rec_type Required
49 --
50 --
51 --
52 -- Version : Current version 1.0
53 -- Initial version 1.0
54 --
55 -- Notes :
56 --
57 -- End of comments
58
59 PROCEDURE update_srp_subledger
60 ( p_api_version IN NUMBER,
61 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
62 p_commit IN VARCHAR2 := FND_API.G_FALSE,
63 p_validation_level IN VARCHAR2 := FND_API.G_VALID_LEVEL_FULL,
64
65 x_return_status OUT NOCOPY VARCHAR2,
66 x_msg_count OUT NOCOPY NUMBER,
67 x_msg_data OUT NOCOPY VARCHAR2,
68
69 p_srp_subledger IN srp_subledger_rec_type
70 ) IS
71
72 l_api_name CONSTANT VARCHAR2(30) := 'Update_srp_subledger';
73 l_api_version CONSTANT NUMBER :=1.0;
74
75 G_PAYEE_ROLE CONSTANT NUMBER := 54;
76
77 l_earnings NUMBER;
78 l_salesrep_id NUMBER;
79 l_start_date DATE ;
80 l_end_date DATE ;
81
82 l_role_id NUMBER(15);
83 l_quota_id NUMBER(15);
84 l_credit_type_id NUMBER(15);
85 l_period_id NUMBER(15);
86
87 l_calc_type VARCHAR2(30);
88
89 l_delta_subledger cn_srp_periods_pvt.delta_srp_period_rec_type
90 := CN_SRP_PERIODS_PVT.G_MISS_DELTA_SRP_PERIOD_REC;
91
92 CURSOR salesrep_cr IS
93 SELECT salesrep_id, start_date, end_date, period_id, end_period_id
94 FROM cn_process_batches_all
95 WHERE physical_batch_id = p_srp_subledger.physical_batch_id
96 ORDER BY process_batch_id;
97
98 l_salesrep salesrep_cr%ROWTYPE;
99
100 l_dummy number;
101
102 cursor end_calc is
103 select 1
104 from cn_process_batches_all
105 where physical_batch_id = p_srp_subledger.physical_batch_id
106 and trx_batch_id = physical_batch_id
107 and rownum = 1;
108
109 cursor payees_cr is
110 select sp.srp_period_id,
111 nvl(sum(cl.commission_amount),0) comm_earned_ptd
112 from cn_srp_periods_all sp,
113 cn_srp_payee_assigns_all spa,
114 (select b.payee_id,
115 min(a.start_date) start_date,
116 max(a.end_date) end_date,
117 min(a.period_id) period_id,
118 max(a.end_period_id) end_period_id
119 from cn_process_batches_all a,
120 cn_srp_payee_assigns_all b
121 where a.logical_batch_id = (select logical_batch_id
122 from cn_process_batches_all
123 where physical_batch_id = p_srp_subledger.physical_batch_id
124 and rownum = 1)
125 and a.salesrep_id = b.salesrep_id
126 and a.org_id = b.org_id
127 and a.start_date <= nvl(b.end_date, a.end_date)
128 and a.end_date >= b.start_date
129 group by b.payee_id) pb,
130 cn_commission_lines_all cl
131 where pb.payee_id = spa.payee_id
132 and pb.start_date <= nvl(spa.end_date, pb.end_date)
133 and pb.end_date >= spa.start_date
134 and spa.org_id = g_org_id
135 and sp.salesrep_id = spa.payee_id
136 and sp.period_id between pb.period_id and pb.end_period_id
137 and sp.quota_id = spa.quota_id
138 and exists (select 1 from cn_quotas_all q
139 where q.quota_id = sp.quota_id
140 and q.incentive_type_code = l_calc_type)
141 and cl.credited_salesrep_id(+) = spa.salesrep_id
142 and cl.srp_payee_assign_id(+) = spa.srp_payee_assign_id
143 and (cl.processed_period_id is null or cl.processed_period_id = sp.period_id)
144 and (cl.quota_id is null or cl.quota_id = sp.quota_id)
145 and cl.status(+) = 'CALC'
146 and cl.pending_status(+) = 'N'
147 and (cl.trx_type is null or cl.trx_type not in ('ADV', 'REC', 'CHG', 'FORECAST', 'BONUS'))
148 and (cl.credit_type_id is null or cl.credit_type_id = sp.credit_type_id)
149 and sp.role_id = g_payee_role
150 group by sp.srp_period_id;
151
152 cursor sync_recs_cr is
153 select sp.salesrep_id, sp.credit_type_id, min(sp.period_id) period_id
154 from cn_srp_periods_all sp,
155 cn_srp_payee_assigns_all spa,
156 cn_process_batches_all pb
157 where pb.logical_batch_id = (select logical_batch_id
158 from cn_process_batches_all
159 where physical_batch_id = p_srp_subledger.physical_batch_id
160 and rownum = 1)
161 and pb.salesrep_id = spa.salesrep_id
162 and spa.org_id = g_org_id
163 and pb.start_date <= nvl(spa.end_date, pb.end_date)
164 and pb.end_date >= spa.start_date
165 and sp.salesrep_id = spa.payee_id
166 and sp.period_id between pb.period_id and pb.end_period_id
167 and sp.quota_id = spa.quota_id
168 and exists (select 1 from cn_quotas_all q
169 where q.quota_id = sp.quota_id
170 and q.incentive_type_code = l_calc_type)
171 and sp.role_id = g_payee_role
172 group by sp.salesrep_id, sp.credit_type_id;
173
174 CURSOR subledger_cr(p_start_period_id NUMBER, p_end_period_id NUMBER) IS
175 SELECT srp_period_id,
176 salesrep_id,
177 role_id,
178 quota_id,
179 credit_type_id,
180 period_id,
181 end_date,
182 Nvl(balance3_ctd,0) earnings_ptd,
183 Nvl(balance2_dtd,0) earnings_due_ptd
184 FROM cn_srp_periods_all
185 WHERE salesrep_id = l_salesrep_id
186 AND org_id = g_org_id
187 AND period_id between p_start_period_id and p_end_period_id
188 AND start_date <= l_end_date
189 AND end_date >= l_start_date
190 --clku , bug 2655685
191 AND role_id <> G_PAYEE_ROLE
192 AND quota_id <> -9999
193 ORDER BY period_id;
194
195
196 l_subledger subledger_cr%ROWTYPE;
197
198 CURSOR get_distinct_roles IS
199 SELECT distinct role_id, credit_type_id
200 FROM cn_srp_periods_all
201 WHERE salesrep_id = l_salesrep_id
202 AND org_id = g_org_id
203 AND (((start_date <= l_start_date)
204 AND( end_date >= l_start_date))
205 OR ((start_date <= l_end_date)
206 AND (end_date >= l_end_date))
207 OR ((start_date >= l_start_date)
208 AND (end_date <= l_end_date)))
209 --clku , bug 2655685
210 AND quota_id <> -9999;
211
212 CURSOR comm_cr IS
213 SELECT SUM(Decode(cl.pending_status, 'Y', 0,
214 Decode(cl.trx_type, 'ADV', 0, 'REC', 0, 'CHG', 0,
215 'FORECAST', 0, 'BONUS', 0,
216 Nvl(cl.commission_amount,0)))) comm_earned_ptd
217 FROM cn_commission_lines_all cl
218 WHERE cl.credited_salesrep_id = l_salesrep_id
219 AND cl.pay_period_id = l_period_id
220 AND cl.role_id = l_role_id
221 AND cl.quota_id = l_quota_id
222 AND cl.status = 'CALC'
223 AND exists (select 1 from cn_quotas_all
224 where quota_id = cl.quota_id
225 and credit_type_id = l_credit_type_id)
226 AND cl.srp_payee_assign_id is NULL;-- only line added to the previously existing query for fixing bug#2495614
227
228
229 CURSOR bonus_cr IS
230 SELECT SUM(Decode(cl.pending_status, 'Y', 0,
231 cl.commission_amount)) bonus_earned_ptd
232 FROM cn_commission_lines_all cl,
233 cn_commission_headers_all ch,
234 cn_srp_plan_assigns_all cspa,
235 cn_role_plans_all crp,
236 cn_quotas_all cq
237 WHERE cl.credited_salesrep_id = l_salesrep_id
238 AND cl.org_id = g_org_id
239 AND ch.commission_header_id = cl.commission_header_id
240 AND cl.pay_period_id = l_period_id
241 AND cl.quota_id = cq.quota_id
242 AND cq.credit_type_id = l_credit_type_id
243 AND cl.srp_plan_assign_id = cspa.srp_plan_assign_id
244 AND cspa.role_plan_id = crp.role_plan_id
245 AND crp.role_id = l_role_id
246 AND cl.quota_id = l_quota_id
247 AND ch.trx_type = 'BONUS'
248 AND cl.status = 'CALC'
249 -- only line added to the previously existing query for fixing bug#2495614
250 AND cl.srp_payee_assign_id is NULL;
251
252
253 l_loading_status varchar2(50);
254 l_return_status VARCHAR2(50);
255 l_msg_count NUMBER;
256 l_msg_data VARCHAR2(2000);
257
258 --added for bug 2495614
259 l_srp_role_id number;
260 l_payeeassigned boolean := TRUE;
261
262 -- mblum for bug 2761303
263 sync_needed boolean := FALSE;
264 l_start_period_id number;
265
266 -- clku , bug 2433243
267 l_int_type_code VARCHAR2(30);
268 BEGIN
269 -- Standard Start of API savepoint
270 SAVEPOINT update_srp_subledger;
271
272 -- Standard call to check for call compatibility.
273 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
274 p_api_version ,
275 l_api_name ,
276 G_PKG_NAME )
277 THEN
278 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
279 END IF;
280
281 -- Initialize message list if p_init_msg_list is set to TRUE.
282 IF FND_API.to_Boolean( p_init_msg_list ) THEN
283 FND_MSG_PUB.initialize;
284 END IF;
285
286 -- Initialize API return status to success
287 x_return_status := FND_API.G_RET_STS_SUCCESS;
288
289 if (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
290 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
291 'cn.plsql.cn_calc_subledger_pvt.update_srp_subledger.begin',
292 'Beginning of update_srp_subledger ...');
293 end if;
294
295 cn_message_pkg.debug('Start updating payment subledgers ... ');
296
297 -- Code starts here
298 select org_id into g_org_id
299 from cn_process_batches_all
300 where physical_batch_id = p_srp_subledger.physical_batch_id
301 and rownum = 1;
302
303 l_calc_type := cn_calc_sub_batches_pkg.get_calc_type
304 (p_srp_subledger.physical_batch_id);
305
306 -- Loop for each salesrep in the physical batch
307 FOR l_salesrep IN salesrep_cr LOOP
308 -- loop for each salesrep, period, role, credit_type combination
309 l_salesrep_id := l_salesrep.salesrep_id;
310 l_start_date := l_salesrep.start_date;
311 l_end_date := l_salesrep.end_date;
312 sync_needed := false;
313
314 cn_message_pkg.debug('Updating balances for rep (ID='||l_salesrep_id||')');
315 FOR l_subledger IN subledger_cr(l_salesrep.period_id, l_salesrep.end_period_id) LOOP
316 l_period_id := l_subledger.period_id;
317 l_role_id := l_subledger.role_id;
318 l_quota_id := l_subledger.quota_id;
319 l_credit_type_id := l_subledger.credit_type_id;
320
321 l_earnings := 0;
322 IF (l_calc_type = 'COMMISSION') THEN
323 OPEN comm_cr;
324 FETCH comm_cr INTO l_earnings;
325 CLOSE comm_cr;
326 ELSIF (l_calc_type = 'BONUS') THEN
327 OPEN bonus_cr;
328 FETCH bonus_cr INTO l_earnings;
329 CLOSE bonus_cr;
330 ELSE
331 -- wrong calc_type, raise an error
332 RAISE FND_API.g_exc_error;
333 END IF;
334
335 -- clku, 2655685, change cn_quotas to cn_quotas_all
336 -- to handle the deleted PE
337 IF l_quota_id is not null THEN
338 select incentive_type_code
339 into l_int_type_code
340 from cn_quotas_all
341 where quota_id = l_quota_id;
342
343 If l_int_type_code = l_calc_type THEN
344 l_delta_subledger.srp_period_id := l_subledger.srp_period_id;
345 l_delta_subledger.del_balance3_ctd := nvl(l_earnings, 0) - l_subledger.earnings_ptd;
346 l_delta_subledger.del_balance2_dtd := nvl(l_earnings, 0) - l_subledger.earnings_due_ptd;
347
348 -- call update API
349 if (l_delta_subledger.del_balance3_ctd <> 0 OR l_delta_subledger.del_balance2_dtd <> 0) then
350 CN_SRP_PERIODS_PVT.Update_Delta_Srp_Pds_No_Sync
351 (p_api_version => 1.0,
352 x_return_status => l_return_status,
353 x_msg_count => l_msg_count,
354 x_msg_data => l_msg_data,
355 p_del_srp_prd_rec => l_delta_subledger,
356 x_loading_status => l_loading_status
357 );
358 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
359 cn_message_pkg.debug('Exception occurs in cn_srp_periods_pvt.update_delta_srp_periods:');
360 cn_message_pkg.debug(l_msg_data);
361 raise FND_API.g_exc_error;
362 end if;
363
364 if sync_needed = false then
365 sync_needed := true;
366 l_start_period_id := l_period_id;
367 end if;
368 end if;
369 END If;
370 END IF;
371 END LOOP; -- for l_srp_subledger cursor loop;
372
373 -- sync bals
374 if sync_needed then
375 -- loop through all roles and credit types to be updated
376 for r in get_distinct_roles loop
377 if r.role_id <> G_PAYEE_ROLE then
378 cn_message_pkg.debug('Synchonizing balances for salesrep (salesrep_id='||l_salesrep_id ||
379 ' role_id=' || r.role_id || ' start_period_id=' || l_start_period_id||')');
380
381 CN_SRP_PERIODS_PVT.Sync_Accum_Balances_Start_Pd
382 (p_salesrep_id => l_salesrep_id,
383 p_credit_type_id => r.credit_type_id,
384 p_role_id => r.role_id,
385 p_start_period_id => l_start_period_id,
386 p_org_id => g_org_id);
387 end if;
388 end loop;
389 end if;
390
391 commit;
392
393 END LOOP; -- for l_salesrep cursor loop
394
395 -- at the very end of the whole calculation process, update payee subledger
396 open end_calc;
397 fetch end_calc into l_dummy;
398 close end_calc;
399
400 if (l_dummy = 1) then
401 for payee in payees_cr loop
402 update cn_srp_periods_all
403 set balance2_dtd = payee.comm_earned_ptd,
404 balance3_ctd = payee.comm_earned_ptd
405 where srp_period_id = payee.srp_period_id;
406 end loop;
407
408 for rec in sync_recs_cr loop
409 cn_srp_periods_pvt.sync_accum_balances_start_pd
410 (p_salesrep_id => rec.salesrep_id,
411 p_credit_type_id => rec.credit_type_id,
412 p_role_id => g_payee_role,
413 p_start_period_id => rec.period_id,
414 p_org_id => g_org_id);
415 end loop;
416
417 end if;
418
419 if (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
420 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
421 'cn.plsql.cn_calc_subledger_pvt.update_srp_subledger.end',
422 'End of update_srp_subledger ...');
423 end if;
424
425 -- Standard check of p_commit.
426 IF FND_API.To_Boolean( p_commit ) THEN
427 COMMIT WORK;
428 END IF;
429
430 cn_message_pkg.debug('Finish updating payment subledgers ');
431
432 -- Standard call to get message count and if count is 1, get message info.
433 FND_MSG_PUB.Count_And_Get
434 ( p_count => x_msg_count ,
438
435 p_data => x_msg_data ,
436 p_encoded => FND_API.G_FALSE
437 );
439 EXCEPTION
440
441 WHEN FND_API.G_EXC_ERROR THEN
442 ROLLBACK TO update_srp_subledger;
443 x_return_status := FND_API.G_RET_STS_ERROR ;
444 FND_MSG_PUB.Count_And_Get
445 (p_count => x_msg_count ,
446 p_data => x_msg_data ,
447 p_encoded => FND_API.G_FALSE
448 );
449
450 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
451 ROLLBACK TO update_srp_subledger;
452 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
453 FND_MSG_PUB.Count_And_Get
454 (p_count => x_msg_count ,
455 p_data => x_msg_data ,
456 p_encoded => FND_API.G_FALSE
457 );
458
459 WHEN OTHERS THEN
460 ROLLBACK TO update_srp_subledger;
461 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
462 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
463 THEN
464 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
465 END IF;
466 FND_MSG_PUB.Count_And_Get
467 (p_count => x_msg_count ,
468 p_data => x_msg_data ,
469 p_encoded => FND_API.G_FALSE
470 );
471
472 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
473 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
474 'cn.plsql.cn_calc_subledger_pvt.update_srp_subledger.exception',
475 sqlerrm);
476 end if;
477
478 fnd_file.put_line(fnd_file.log, 'EXCEPTION in update_srp_subledger: '||sqlerrm);
479 cn_message_pkg.debug('Exception occurs in cn_calc_subledger_pvt.update_srp_subledger: ');
480 cn_message_pkg.debug(sqlerrm);
481 END update_srp_subledger;
482
483 -- API name : update_srp_pe_subledger
484 -- Type : Private.
485 -- Pre-reqs :
486 -- Usage :
487 --
488 -- Desc :
489 --
490 --
491 --
492 -- Parameters :
493 -- IN : p_api_version NUMBER Require
494 -- p_init_msg_list VARCHAR2 Optional (FND_API.G_FALSE)
495 -- p_commit VARCHAR2 Optional (FND_API.G_FALSE)
496 -- p_validation_level NUMBER Optional (FND_API.G_VALID_LEVEL_FULL)
497 -- OUT : x_return_status VARCHAR2(1)
498 -- x_msg_count NUMBER
499 -- x_msg_data VARCHAR2(2000)
500 -- IN : p_srp_pe_subledger srp_pe_subledger_rec_type Require
501 -- p_mode IN VARCHAR2 := 'A'
502 --
503 --
504 -- Version : Current version 1.0
505 -- Initial version 1.0
506 --
507 -- Notes :
508 --
509 -- End of comments
510
511 PROCEDURE update_srp_pe_subledger
512 ( p_api_version IN NUMBER,
513 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
514 p_commit IN VARCHAR2 := FND_API.G_FALSE,
515 p_validation_level IN VARCHAR2 := FND_API.G_VALID_LEVEL_FULL,
516
517 x_return_status OUT NOCOPY VARCHAR2,
518 x_msg_count OUT NOCOPY NUMBER,
519 x_msg_data OUT NOCOPY VARCHAR2,
520
521 p_srp_pe_subledger IN srp_pe_subledger_rec_type,
522 p_mode IN VARCHAR2 := 'A'
523 ) IS
524
525 l_api_name CONSTANT VARCHAR2(30) := 'Update_srp_pe_subledger';
526 l_api_version CONSTANT NUMBER :=1.0;
527
528 l_max_period_id cn_period_statuses.period_id%TYPE;
529 l_start_period_id cn_period_statuses.period_id%TYPE;
530
531 CURSOR comm_bonus_cr IS
532 SELECT SUM(Decode(cl.pending_status, 'Y', 0,
533 Decode(cl.trx_type, 'ADV', 0, 'REC', 0, 'CHG', 0, 'FORECAST', 0,
534 Nvl(cl.commission_amount,0)))) comm_earned_ptd,
535 SUM(Decode(cl.pending_status, 'Y', 0,
536 Decode(cl.trx_type, 'ADV', cl.commission_amount, 0))) adv_paid_ptd,
537 SUM(Decode(cl.pending_status, 'Y', 0,
538 Decode(cl.trx_type, 'REC', cl.commission_amount, 0))) adv_earned_ptd,
539 SUM(Decode(cl.pending_status, 'Y', 0,
540 Decode(cl.trx_type, 'CHG', cl.commission_amount, 0))) rec_amount_ptd,
541 SUM(Decode(cl.pending_status, 'Y', cl.commission_amount, 0)) comm_pending_ptd,
542 SUM(ch.transaction_amount) transaction_amount_ptd
543 FROM cn_commission_lines cl, cn_commission_headers_all ch
544 WHERE
545 cl.credited_salesrep_id = p_srp_pe_subledger.salesrep_id
546 --for payee enh. bug#2495614 above condition is replaced by the following code
547 --(
548 -- (cl.credited_salesrep_id = p_srp_pe_subledger.salesrep_id
549 -- and
550 -- cl.srp_payee_assign_id IS NULL)
551 -- OR
552 -- (
553 -- cl.srp_payee_assign_id IS NOT NULL
554 -- AND EXISTS
555 -- (
556 -- Select 'X' from cn_srp_payee_assigns cspa
557 -- where cspa.srp_payee_assign_id = cl.srp_payee_assign_id
558 -- and cspa.payee_id = p_srp_pe_subledger.salesrep_id
559 -- )
560 -- )
561 --)
562 AND cl.processed_period_id = p_srp_pe_subledger.accu_period_id
563 AND cl.quota_id = p_srp_pe_subledger.quota_id
564 AND cl.srp_plan_assign_id = p_srp_pe_subledger.srp_plan_assign_id
565 AND cl.status = 'CALC'
566 AND cl.commission_header_id = ch.commission_header_id;
567
568 CURSOR quota_type_cr IS
572 f.trx_group_code trx_group_code,
569 SELECT f.calc_formula_id calc_formula_id,
570 q.quota_type_code quota_type_code,
571 q.package_name package_name,
573 q.org_id
574 FROM cn_calc_formulas_all f,
575 cn_quotas_all q
576 WHERE q.quota_id = p_srp_pe_subledger.quota_id
577 AND f.calc_formula_id(+) = q.calc_formula_id
578 AND f.org_id(+) = q.org_id;
579
580 CURSOR revenue_classes is
581 SELECT revenue_class_id
582 FROM cn_srp_per_quota_rc_all
583 WHERE salesrep_id = p_srp_pe_subledger.salesrep_id
584 AND period_id = p_srp_pe_subledger.accu_period_id
585 AND quota_id = p_srp_pe_subledger.quota_id
586 AND srp_plan_assign_id = p_srp_pe_subledger.srp_plan_assign_id;
587
588 CURSOR periods_cr IS
589 SELECT spq.period_id period_id,
590 spq.srp_period_quota_id,
591 Nvl(spq.transaction_amount_ptd, 0) transaction_amount_ptd,
592 Nvl(spq.commission_payed_ptd, 0) commission_payed_ptd,
593 Nvl(spq.input_achieved_ptd,0) input_achieved_ptd,
594 Nvl(spq.output_achieved_ptd,0) output_achieved_ptd,
595 Nvl(spq.perf_achieved_ptd,0) perf_achieved_ptd,
596 Nvl(spq.advance_recovered_ptd,0) advance_recovered_ptd,
597 Nvl(spq.advance_to_rec_ptd,0) advance_to_rec_ptd,
598 Nvl(spq.recovery_amount_ptd,0) recovery_amount_ptd,
599 Nvl(spq.comm_pend_ptd,0) comm_pend_ptd,
600 Nvl(spq.transaction_amount_itd, 0) transaction_amount_itd,
601 Nvl(spq.commission_payed_itd,0) commission_payed_itd,
602 Nvl(spq.input_achieved_itd,0) input_achieved_itd,
603 Nvl(spq.output_achieved_itd,0) output_achieved_itd,
604 Nvl(spq.perf_achieved_itd,0) perf_achieved_itd,
605 Nvl(spq.advance_recovered_itd,0) advance_recovered_itd ,
606 Nvl(spq.advance_to_rec_itd,0) advance_to_rec_itd,
607 Nvl(spq.recovery_amount_itd,0) recovery_amount_itd,
608 Nvl(spq.comm_pend_itd,0)comm_pend_itd
609 FROM cn_srp_period_quotas_all spq
610 WHERE salesrep_id = p_srp_pe_subledger.salesrep_id
611 AND period_id >= p_srp_pe_subledger.accu_period_id
612 AND quota_id = p_srp_pe_subledger.quota_id
613 AND srp_plan_assign_id = p_srp_pe_subledger.srp_plan_assign_id
614 AND period_id <= l_max_period_id
615 ORDER BY spq.period_id ASC;
616
617 CURSOR periods_ext(p_srp_period_quota_id NUMBER) IS
618 SELECT nvl(input_achieved_ptd, 0) input_achieved_ptd,
619 nvl(input_achieved_itd, 0) input_achieved_itd,
620 input_sequence
621 FROM cn_srp_period_quotas_ext_all
622 WHERE srp_period_quota_id = p_srp_period_quota_id
623 ORDER BY input_sequence;
624
625 comm_bonus comm_bonus_cr%ROWTYPE;
626
627 quota_type quota_type_cr%ROWTYPE;
628
629 l_transaction_amount_itd NUMBER;
630 l_commission_payed_itd NUMBER;
631 l_input_achieved_itd NUMBER;
632 l_output_achieved_itd NUMBER;
633 l_perf_achieved_itd NUMBER;
634 l_advance_recovered_itd NUMBER;
635 l_advance_to_rec_itd NUMBER;
636 l_recovery_amount_itd NUMBER;
637 l_comm_pend_itd NUMBER;
638
639 l_srp_period_quota_id NUMBER(15);
640 l_input_achieved_itd_tbl cn_formula_common_pkg.num_table_type;
641
642 l_sql_stmt VARCHAR2(2000);
643 BEGIN
644
645 cn_message_pkg.debug('Start updating calculation subledgers ... ');
646
647 -- Standard Start of API savepoint
648 SAVEPOINT update_srp_pe_subledger;
649
650 -- Standard call to check for call compatibility.
651 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
652 p_api_version ,
653 l_api_name ,
654 G_PKG_NAME )
655 THEN
656 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
657 END IF;
658
659 -- Initialize message list if p_init_msg_list is set to TRUE.
660 IF FND_API.to_Boolean( p_init_msg_list ) THEN
661 FND_MSG_PUB.initialize;
662 END IF;
663
664 -- Initialize API return status to success
665 x_return_status := FND_API.G_RET_STS_SUCCESS;
666
667 -- Codes start here
668 if (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
669 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
670 'cn.plsql.cn_calc_subledger_pvt.update_srp_pe_subledger.begin',
671 'Beginning of update_srp_pe_subledger (srp_plan_assign_id='
672 ||p_srp_pe_subledger.srp_plan_assign_id|| ' and quota_id='||p_srp_pe_subledger.quota_id);
673 end if;
674
675 select org_id into g_org_id
676 from cn_srp_plan_assigns_all
677 where srp_plan_assign_id = p_srp_pe_subledger.srp_plan_assign_id;
678
679 -- get the max period in this interval
680
681 l_max_period_id := get_max_period(p_quota_id => p_srp_pe_subledger.quota_id,
682 p_period_id => p_srp_pe_subledger.accu_period_id,
683 p_srp_plan_assign_id => p_srp_pe_subledger.srp_plan_assign_id);
684
685 l_start_period_id := cn_formula_common_pkg.get_start_period_id(p_srp_pe_subledger.quota_id, p_srp_pe_subledger.accu_period_id);
686
687 cn_message_pkg.debug('Last period in the interval is ' || l_max_period_id);
688
689 -- update cn_srp_period_quotas for the current period.
690
691 OPEN comm_bonus_cr;
695
692 FETCH comm_bonus_cr INTO comm_bonus;
693
694 IF (comm_bonus_cr%notfound) THEN
696 cn_message_pkg.debug('No commission lines');
697
698 END IF;
699
700 IF p_mode = 'A' THEN
701
702 -- All Columns need to be updated
703 UPDATE cn_srp_period_quotas_all
704 SET input_achieved_ptd = p_srp_pe_subledger.input_ptd(1) ,
705 input_achieved_itd = p_srp_pe_subledger.input_itd(1) ,
706
707 output_achieved_ptd = p_srp_pe_subledger.output_ptd ,
708 output_achieved_itd = p_srp_pe_subledger.output_itd ,
709
710 perf_achieved_ptd = p_srp_pe_subledger.perf_ptd ,
711 perf_achieved_itd = p_srp_pe_subledger.perf_itd ,
712
713 transaction_amount_ptd = Nvl(comm_bonus.transaction_amount_ptd,0),
714 transaction_amount_itd = Decode(period_id, l_start_period_id, Nvl(comm_bonus.transaction_amount_ptd, 0),
715 Nvl(transaction_amount_itd,0) - Nvl(transaction_amount_ptd ,0)
716 + Nvl(comm_bonus.transaction_amount_ptd, 0)),
717
718 commission_payed_ptd = Nvl(comm_bonus.comm_earned_ptd,0),
719 commission_payed_itd = Decode(period_id, l_start_period_id, Nvl(comm_bonus.comm_earned_ptd, 0),
720 Nvl(commission_payed_itd,0) - Nvl(commission_payed_ptd ,0)
721 + Nvl(comm_bonus.comm_earned_ptd, 0)),
722
723 advance_recovered_ptd = Nvl(comm_bonus.adv_earned_ptd,0),
724 advance_recovered_itd = Decode(period_id, l_start_period_id, Nvl(comm_bonus.adv_earned_ptd, 0),
725 Nvl(advance_recovered_itd,0) - Nvl(advance_recovered_ptd,0)
726 + Nvl(comm_bonus.adv_earned_ptd, 0)),
727
728 advance_to_rec_ptd = Nvl(comm_bonus.adv_paid_ptd,0),
729 advance_to_rec_itd = Decode(period_id, l_start_period_id, Nvl(comm_bonus.adv_paid_ptd, 0),
730 Nvl(advance_to_rec_itd,0) - Nvl(advance_to_rec_ptd,0)
731 + Nvl(comm_bonus.adv_paid_ptd, 0)),
732
733 recovery_amount_ptd = Nvl(comm_bonus.rec_amount_ptd,0),
734 recovery_amount_itd = Decode(period_id, l_start_period_id, Nvl(comm_bonus.rec_amount_ptd, 0),
735 Nvl(recovery_amount_itd,0) - Nvl(recovery_amount_ptd,0)
736 + Nvl(comm_bonus.rec_amount_ptd, 0)),
737
738 comm_pend_ptd = Nvl(comm_bonus.comm_pending_ptd, 0),
739 comm_pend_itd = Decode(period_id, l_start_period_id, Nvl(comm_bonus.comm_pending_ptd, 0),
740 Nvl(comm_pend_itd,0) - Nvl(comm_pend_ptd,0)
741 + Nvl(comm_bonus.comm_pending_ptd, 0)),
742
743 rollover = Decode(period_id, l_max_period_id, p_srp_pe_subledger.rollover, NULL),
744 LAST_UPDATE_DATE = sysdate,
745 LAST_UPDATED_BY = fnd_global.user_id,
746 LAST_UPDATE_LOGIN = fnd_global.login_id
747
748 WHERE salesrep_id = p_srp_pe_subledger.salesrep_id
749 AND period_id = p_srp_pe_subledger.accu_period_id
750 AND quota_id = p_srp_pe_subledger.quota_id
751 AND srp_plan_assign_id = p_srp_pe_subledger.srp_plan_assign_id
752 returning srp_period_quota_id INTO l_srp_period_quota_id;
753
754 FOR i IN 2 .. p_srp_pe_subledger.input_ptd.COUNT LOOP
755 UPDATE cn_srp_period_quotas_ext_all
756 SET input_achieved_ptd = p_srp_pe_subledger.input_ptd(i) ,
757 input_achieved_itd = p_srp_pe_subledger.input_itd(i) ,
758 last_update_date = Sysdate,
759 last_updated_by = fnd_global.user_id,
760 last_update_login = fnd_global.login_id
761 WHERE srp_period_quota_id = l_srp_period_quota_id
762 AND input_sequence = i;
763 END LOOP;
764
765 ELSE
766
767 -- Update only commission related columns
768
769 UPDATE cn_srp_period_quotas_all
770 SET
771 transaction_amount_ptd = Nvl(comm_bonus.transaction_amount_ptd,0),
772 transaction_amount_itd = Decode(period_id, l_start_period_id, Nvl(comm_bonus.transaction_amount_ptd, 0),
773 Nvl(transaction_amount_itd,0) - Nvl(transaction_amount_ptd ,0)
774 + Nvl(comm_bonus.transaction_amount_ptd, 0)),
775
776 commission_payed_ptd = Nvl(comm_bonus.comm_earned_ptd,0),
777 commission_payed_itd = Decode(period_id, l_start_period_id, Nvl(comm_bonus.comm_earned_ptd, 0),
778 Nvl(commission_payed_itd,0) - Nvl(commission_payed_ptd ,0)
779 + Nvl(comm_bonus.comm_earned_ptd, 0)),
780
781 advance_recovered_ptd = Nvl(comm_bonus.adv_earned_ptd,0),
782 advance_recovered_itd = Decode(period_id, l_start_period_id, Nvl(comm_bonus.adv_earned_ptd, 0),
783 Nvl(advance_recovered_itd,0) - Nvl(advance_recovered_ptd,0)
784 + Nvl(comm_bonus.adv_earned_ptd, 0)),
785
786 advance_to_rec_ptd = Nvl(comm_bonus.adv_paid_ptd,0),
787 advance_to_rec_itd = Decode(period_id, l_start_period_id, Nvl(comm_bonus.adv_paid_ptd, 0),
788 Nvl(advance_to_rec_itd,0) - Nvl(advance_to_rec_ptd,0)
789 + Nvl(comm_bonus.adv_paid_ptd, 0)),
790
791 recovery_amount_ptd = Nvl(comm_bonus.rec_amount_ptd,0),
792 recovery_amount_itd = Decode(period_id, l_start_period_id, Nvl(comm_bonus.rec_amount_ptd, 0),
793 Nvl(recovery_amount_itd,0) - Nvl(recovery_amount_ptd,0)
794 + Nvl(comm_bonus.rec_amount_ptd, 0)),
795
796 comm_pend_ptd = Nvl(comm_bonus.comm_pending_ptd, 0),
797 comm_pend_itd = Decode(period_id, l_start_period_id, Nvl(comm_bonus.comm_pending_ptd, 0),
798 Nvl(comm_pend_itd,0) - Nvl(comm_pend_ptd,0)
799 + Nvl(comm_bonus.comm_pending_ptd, 0)),
800
801 LAST_UPDATE_DATE = sysdate,
802 LAST_UPDATED_BY = fnd_global.user_id,
803 LAST_UPDATE_LOGIN = fnd_global.login_id
804
808 AND srp_plan_assign_id = p_srp_pe_subledger.srp_plan_assign_id;
805 WHERE salesrep_id = p_srp_pe_subledger.salesrep_id
806 AND period_id = p_srp_pe_subledger.accu_period_id
807 AND quota_id = p_srp_pe_subledger.quota_id
809
810 END IF;
811
812 CLOSE comm_bonus_cr;
813
814 -- update cn_srp_per_quota_rc for the current period
815 OPEN quota_type_cr;
816 FETCH quota_type_cr INTO quota_type;
817
818 IF (quota_type_cr%notfound) THEN
819 -- quota_type is not FORMULA, should not call this procedure
820 NULL;
821 ELSIF (quota_type.quota_type_code = 'EXTERNAL') THEN
822 IF (quota_type.package_name IS NULL) THEN
823 NULL;
824 ELSE
825 declare
826 no_component EXCEPTION;
827 PRAGMA EXCEPTION_INIT(no_component, -6550);
828 begin
829 l_sql_stmt := ' Begin ' || quota_type.package_name ||'.update_revclass_perf ( :salesrep_id, :period_id, :quota_id, :srp_plan_assign_id ); End; ';
830
831 execute immediate l_sql_stmt using p_srp_pe_subledger.salesrep_id,
832 p_srp_pe_subledger.accu_period_id, p_srp_pe_subledger.quota_id,
833 p_srp_pe_subledger.srp_plan_assign_id;
834 exception
835
836 when no_component then
837 FOR class IN revenue_classes LOOP
838 UPDATE cn_srp_per_quota_rc_all rc
839 SET period_to_date =
840 (SELECT nvl(sum(cl.perf_achieved), 0)
841 FROM cn_commission_lines_all cl,
842 cn_quota_rules_all qr
843 WHERE cl.credited_Salesrep_id = p_srp_pe_subledger.salesrep_id
844 AND cl.quota_id = p_srp_pe_subledger.quota_id
845 AND cl.processed_period_id = p_srp_pe_subledger.accu_period_id
846 AND cl.status = 'CALC'
847 AND cl.trx_type NOT IN ( 'FORECAST', 'BONUS')
848 AND cl.quota_rule_id = qr.quota_rule_id
849 AND qr.revenue_class_id = class.revenue_class_id
850 AND qr.quota_id = p_srp_pe_subledger.quota_id
851 AND cl.srp_plan_assign_id = p_srp_pe_subledger.srp_plan_assign_id)
852 WHERE salesrep_id = p_srp_pe_subledger.salesrep_id
853 AND period_id = p_srp_pe_subledger.accu_period_id
854 AND quota_id = p_srp_pe_subledger.quota_id
855 AND srp_plan_assign_id = p_srp_pe_subledger.srp_plan_assign_id
856 AND revenue_class_id = class.revenue_class_id;
857 END LOOP;
858 when others then
859 NULL;
860 end;
861 END IF;
862 ELSE
863 IF (quota_type.trx_group_code = 'GROUP') THEN
864 -- in case of group by case, there is no perf_achieved on each transaction
865 -- need to call the procedure in formula package using DSQL
866 l_sql_stmt := ' Begin cn_formula_'|| abs(quota_type.calc_formula_id) || '_' || abs(quota_type.org_id)
867 ||'_pkg.update_revclass_perf ( :salesrep_id, :period_id, :quota_id, '||
868 ':srp_plan_assign_id ); End; ';
869
870 execute immediate l_sql_stmt using p_srp_pe_subledger.salesrep_id,
871 p_srp_pe_subledger.accu_period_id, p_srp_pe_subledger.quota_id,
872 p_srp_pe_subledger.srp_plan_assign_id;
873
874 ELSE
875 -- sum transactions
876 FOR class IN revenue_classes LOOP
877 UPDATE cn_srp_per_quota_rc_all rc
878 SET period_to_date =
879 (SELECT nvl(sum(cl.perf_achieved), 0)
880 FROM cn_commission_lines_all cl,
881 cn_quota_rules_all qr
882 WHERE cl.credited_Salesrep_id = p_srp_pe_subledger.salesrep_id
883 AND cl.quota_id = p_srp_pe_subledger.quota_id
884 AND cl.processed_period_id = p_srp_pe_subledger.accu_period_id
885 AND cl.status = 'CALC'
886 AND cl.trx_type NOT IN ( 'FORECAST', 'BONUS')
887 AND cl.quota_rule_id = qr.quota_rule_id
888 AND qr.revenue_class_id = class.revenue_class_id
889 AND qr.quota_id = p_srp_pe_subledger.quota_id
890 AND cl.srp_plan_assign_id = p_srp_pe_subledger.srp_plan_assign_id)
891 WHERE salesrep_id = p_srp_pe_subledger.salesrep_id
892 AND period_id = p_srp_pe_subledger.accu_period_id
893 AND quota_id = p_srp_pe_subledger.quota_id
894 AND srp_plan_assign_id = p_srp_pe_subledger.srp_plan_assign_id
895 AND revenue_class_id = class.revenue_class_id;
896 END LOOP;
897 END IF;
898 END IF;
899
900 CLOSE quota_type_cr;
901
902 -- update cn_srp_period_quotas for the subsequence
903 FOR period IN periods_cr LOOP
904
905 IF (period.period_id = p_srp_pe_subledger.accu_period_id) THEN
906 -- in case of current period, get start ytd
907 l_transaction_amount_itd := period.transaction_amount_itd;
908 l_commission_payed_itd := period.commission_payed_itd;
909 l_input_achieved_itd := period.input_achieved_itd;
910 l_output_achieved_itd := period.output_achieved_itd;
911 l_perf_achieved_itd := period.perf_achieved_itd;
912 l_advance_recovered_itd := period.advance_recovered_itd;
913 l_advance_to_rec_itd := period.advance_to_rec_itd;
914 l_recovery_amount_itd := period.recovery_amount_itd;
915 l_comm_pend_itd := period.comm_pend_itd;
916
917 FOR period_ext IN periods_ext(period.srp_period_quota_id) LOOP
921 ELSE
918 l_input_achieved_itd_tbl(period_ext.input_sequence) := period_ext.input_achieved_itd;
919 END LOOP;
920
922 -- future period
923 l_transaction_amount_itd := l_transaction_amount_itd + period.transaction_amount_ptd;
924 l_commission_payed_itd := l_commission_payed_itd + period.commission_payed_ptd;
925 l_input_achieved_itd := l_input_achieved_itd + period.input_achieved_ptd;
926 l_output_achieved_itd := l_output_achieved_itd + period.output_achieved_ptd;
927 l_perf_achieved_itd := l_perf_achieved_itd + period.perf_achieved_ptd;
928 l_advance_recovered_itd := l_advance_recovered_itd + period.advance_recovered_ptd;
929 l_advance_to_rec_itd := l_advance_to_rec_itd + period.advance_to_rec_ptd;
930 l_recovery_amount_itd :=l_recovery_amount_itd + period.recovery_amount_ptd ;
931 l_comm_pend_itd := l_comm_pend_itd + period.comm_pend_ptd;
932
933 UPDATE cn_srp_period_quotas_all
934 SET
935 transaction_amount_itd = l_transaction_amount_itd,
936 commission_payed_itd = l_commission_payed_itd,
937 input_achieved_itd = l_input_achieved_itd,
938 output_achieved_itd = l_output_achieved_itd,
939 perf_achieved_itd = l_perf_achieved_itd,
940 advance_recovered_itd = l_advance_recovered_itd,
941 advance_to_rec_itd = l_advance_to_rec_itd,
942 recovery_amount_itd = l_recovery_amount_itd,
943 comm_pend_itd = l_comm_pend_itd,
944 LAST_UPDATE_DATE = sysdate,
945 LAST_UPDATED_BY = fnd_global.user_id,
946 LAST_UPDATE_LOGIN = fnd_global.login_id
947 WHERE salesrep_id = p_srp_pe_subledger.salesrep_id
948 AND period_id = period.period_id
949 AND quota_id = p_srp_pe_subledger.quota_id
950 AND srp_plan_assign_id = p_srp_pe_subledger.srp_plan_assign_id;
951
952
953 FOR period_ext IN periods_ext(period.srp_period_quota_id) LOOP
954 l_input_achieved_itd_tbl(period_ext.input_sequence) := l_input_achieved_itd_tbl(period_ext.input_sequence) + period_ext.input_achieved_ptd;
955
956 UPDATE cn_srp_period_quotas_ext_all
957 SET input_achieved_itd = l_input_achieved_itd_tbl(period_ext.input_sequence),
958 LAST_UPDATE_DATE = sysdate,
959 LAST_UPDATED_BY = fnd_global.user_id,
960 LAST_UPDATE_LOGIN = fnd_global.login_id
961 WHERE srp_period_quota_id = period.srp_period_quota_id
962 AND input_sequence = period_ext.input_sequence;
963 END LOOP;
964 END IF;
965 END LOOP;
966
967 if (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
968 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
969 'cn.plsql.cn_calc_subledger_pvt.update_srp_pe_subledger.end',
970 'End of update_srp_pe_subledger ...');
971 end if;
972
973 -- Standard check of p_commit.
974 IF FND_API.To_Boolean( p_commit ) THEN
975 COMMIT WORK;
976 END IF;
977
978
979 -- Standard call to get message count and if count is 1, get message info.
980 FND_MSG_PUB.Count_And_Get
981 ( p_count => x_msg_count ,
982 p_data => x_msg_data ,
983 p_encoded => FND_API.G_FALSE
984 );
985 cn_message_pkg.debug('Finish updating calculation subledgers ');
986 EXCEPTION
987
988 WHEN FND_API.G_EXC_ERROR THEN
989 ROLLBACK TO update_srp_pe_subledger;
990 x_return_status := FND_API.G_RET_STS_ERROR ;
991 FND_MSG_PUB.Count_And_Get
992 (p_count => x_msg_count ,
993 p_data => x_msg_data ,
994 p_encoded => FND_API.G_FALSE
995 );
996
997 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
998 ROLLBACK TO update_srp_pe_subledger;
999 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1000 FND_MSG_PUB.Count_And_Get
1001 (p_count => x_msg_count ,
1002 p_data => x_msg_data ,
1003 p_encoded => FND_API.G_FALSE
1004 );
1005
1006 WHEN OTHERS THEN
1007 ROLLBACK TO update_srp_pe_subledger;
1008 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1009 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1010 THEN
1011 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
1012 END IF;
1013 FND_MSG_PUB.Count_And_Get
1014 (p_count => x_msg_count ,
1015 p_data => x_msg_data ,
1016 p_encoded => FND_API.G_FALSE
1017 );
1018
1019 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1020 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
1021 'cn.plsql.cn_calc_subledger_pvt.update_srp_pe_subledger.exception',
1022 sqlerrm);
1023 end if;
1024
1025 fnd_file.put_line(fnd_file.log, 'EXCEPTION in update_srp_pe_subledger: '||sqlerrm);
1026
1027 cn_message_pkg.debug('Exception occurs in update_srp_pe_subledger: ');
1028 cn_message_pkg.debug(sqlerrm);
1029
1030 END update_srp_pe_subledger;
1031
1032
1033 PROCEDURE post_je_batch
1034 ( p_api_version IN NUMBER,
1035 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1036 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1037 p_validation_level IN VARCHAR2 := FND_API.G_VALID_LEVEL_FULL,
1038
1039 x_return_status OUT NOCOPY VARCHAR2,
1040 x_msg_count OUT NOCOPY NUMBER,
1041 x_msg_data OUT NOCOPY VARCHAR2,
1042
1043 p_je_batch IN je_batch_rec_type
1044 ) IS
1045 BEGIN
1049 PROCEDURE roll_quotas_forecast(p_salesrep_id NUMBER,
1046 NULL;
1047 END post_je_batch;
1048
1050 p_period_id NUMBER,
1051 p_quota_id NUMBER,
1052 p_srp_plan_assign_id NUMBER) IS
1053
1054 BEGIN
1055
1056 NULL;
1057
1058 END roll_quotas_forecast;
1059
1060 END cn_calc_subledger_pvt;