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