[Home] [Help]
PACKAGE BODY: APPS.CN_WKSHT_CT_UP_PUB
Source
1 PACKAGE BODY CN_WKSHT_CT_UP_PUB as
2 -- $Header: cnvwkcdb.pls 120.2 2006/02/13 15:24:08 fmburu noship $
3
4 --============================================================================
5 --Modified by Julia Huang for bug 2803102.
6 --This procedure is modified using refresh worksheet instead of
7 --doing 'delete worksheet' and 'create worksheet'.
8 --============================================================================
9 Procedure Create_delete_Wrkhst
10 ( p_api_version IN NUMBER,
11 p_init_msg_list IN VARCHAR2,
12 p_commit IN VARCHAR2,
13 p_validation_level IN NUMBER,
14 x_return_status OUT NOCOPY VARCHAR2,
15 x_msg_count OUT NOCOPY NUMBER,
16 x_msg_data OUT NOCOPY VARCHAR2,
17 p_salesrep_id IN NUMBER,
18 p_srp_pmt_asgn_id IN NUMBER,
19 p_payrun_id IN NUMBER,
20 x_status OUT NOCOPY VARCHAR2,
21 x_loading_status OUT NOCOPY VARCHAR2
22 ) IS
23
24 l_api_name CONSTANT VARCHAR2(30) := 'Create_delete_Wrkhst';
25 l_api_version CONSTANT NUMBER := 1.0;
26
27 --Bug 3670308 by Julia Huang on 6/4/04
28 --Cartesian join is caused by the lack of relationship between cn_payruns and cn_pmt_plans where cn_payruns
29 --and cn_pmt_plans are driving tables as determined by the CBO.
30 CURSOR get_wksht IS
31 /*
32 SELECT pw.payment_worksheet_id,
33 pw.salesrep_id
34 FROM cn_payment_worksheets pw,
35 cn_payruns p,
36 cn_srp_pmt_plans_v ppa
37 WHERE ppa.salesrep_id = p_salesrep_id
38 and ppa.srp_pmt_plan_id = p_srp_pmt_asgn_id
39 and p.payrun_id = p_payrun_id
40 and p.pay_period_id = ppa.period_id
41 and pw.salesrep_id = ppa.salesrep_id
42 and pw.payrun_id = p.payrun_id
43 and pw.quota_id IS NULL
44 and p.status = 'UNPAID' ;
45 */
46 SELECT pw.payment_worksheet_id,
47 pw.salesrep_id
48 FROM cn_payment_worksheets pw, cn_payruns p, cn_period_statuses ps
49 WHERE pw.salesrep_id = p_salesrep_id
50 AND p.payrun_id = p_payrun_id
51 AND p.org_id = ps.org_id
52 AND ps.period_id = p.pay_period_id
53 AND pw.payrun_id = p.payrun_id
54 AND pw.quota_id IS NULL
55 AND p.status = 'UNPAID'
56 AND EXISTS (SELECT 1 FROM cn_srp_pmt_plans ppa
57 WHERE ppa.srp_pmt_plan_id = p_srp_pmt_asgn_id
58 AND ppa.salesrep_id = pw.salesrep_id
59 AND ppa.start_date <= ps.end_date
60 AND Nvl(ppa.end_date,ps.end_date) >= ps.start_date);
61
62 wksht_recs get_wksht%ROWTYPE;
63 wksht_recs1 CN_Payment_Worksheet_PVT.worksheet_rec_type;
64 l_ovn NUMBER ;
65 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CN_WKSHT_CT_UP_PUB';
66
67
68 BEGIN
69 --
70 -- Standard Start of API savepoint
71 --
72
73 SAVEPOINT Create_delete_Wrkhst;
74 --
75 -- Standard call to check for call compatibility.
76 --
77
78 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
79 p_api_version ,
80 l_api_name ,
81 G_PKG_NAME )
82 THEN
83 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
84 END IF;
85 --
86 -- Initialize message list if p_init_msg_list is set to TRUE.
87 --
88 IF FND_API.to_Boolean( p_init_msg_list ) THEN
89 FND_MSG_PUB.initialize;
90 END IF;
91 --
92 -- Initialize API return status to success
93 --
94 x_return_status := FND_API.G_RET_STS_SUCCESS;
95
96 --
97 -- API body
98
99 OPEN get_wksht;
100 LOOP
101 FETCH get_wksht into wksht_recs;
102 exit when get_wksht%NOTFOUND;
103
104 x_loading_status := 'CN_REFRESHED';
105
106
107 CN_Payment_Worksheet_PVT.Update_Worksheet
108 ( p_api_version => p_api_version,
109 p_init_msg_list => p_init_msg_list,
110 p_commit => p_commit,
111 p_validation_level=> p_validation_level,
112 x_return_status => x_return_status,
113 x_msg_count => x_msg_count,
114 x_msg_data => x_msg_data,
115 p_worksheet_id => wksht_recs.payment_worksheet_id,
116 p_operation => 'REFRESH',
117 x_status => x_status,
118 x_loading_status => x_loading_status,
119 x_ovn => l_ovn
120 );
121
122
123 IF x_return_status <> fnd_api.g_ret_sts_success
124 THEN
125 RAISE fnd_api.g_exc_error;
126 END IF;
127
128 END LOOP;
129 close get_wksht;
130
131 -- End of API body.
132
133
134 -- Standard check of p_commit.
135 IF FND_API.To_Boolean( p_commit ) THEN
136 COMMIT WORK;
137 END IF;
138
139
140
141 --
142 -- Standard call to get message count and if count is 1, get message info.
143 --
144
145 FND_MSG_PUB.Count_And_Get
146 (
147 p_count => x_msg_count ,
148 p_data => x_msg_data ,
149 p_encoded => FND_API.G_FALSE
150 );
151
152
153 EXCEPTION
154 WHEN FND_API.G_EXC_ERROR THEN
155 ROLLBACK TO Create_delete_Wrkhst;
156
157 x_return_status := FND_API.G_RET_STS_ERROR ;
158 FND_MSG_PUB.Count_And_Get
159 (
160 p_count => x_msg_count ,
161 p_data => x_msg_data ,
162 p_encoded => FND_API.G_FALSE
163 );
164 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
165 ROLLBACK TO Create_delete_Wrkhst;
166
167 x_loading_status := 'UNEXPECTED_ERR';
168 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
169 FND_MSG_PUB.Count_And_Get
170 (
171 p_count => x_msg_count ,
172 p_data => x_msg_data ,
173 p_encoded => FND_API.G_FALSE
174 );
175 WHEN OTHERS THEN
176
177
178 ROLLBACK TO Create_delete_Wrkhst;
179 x_loading_status := 'UNEXPECTED_ERR';
180 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
181 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
182 THEN
183 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
184 END IF;
185 FND_MSG_PUB.Count_And_Get
186 (
187 p_count => x_msg_count ,
188 p_data => x_msg_data ,
189 p_encoded => FND_API.G_FALSE
190 );
191 END Create_delete_Wrkhst ;
192
193 --============================================================================
194 --Modified by Julia Huang for bug 2803102.
195 --This procedure is modified using refresh worksheet instead of
196 --doing 'delete worksheet' and 'create worksheet'.
197 --============================================================================
198
199 Procedure Apply_payment_plan_upd
200 ( p_api_version IN NUMBER,
201 p_init_msg_list IN VARCHAR2,
202 p_commit IN VARCHAR2,
203 p_validation_level IN NUMBER,
204 x_return_status OUT NOCOPY VARCHAR2,
205 x_msg_count OUT NOCOPY NUMBER,
206 x_msg_data OUT NOCOPY VARCHAR2,
207 p_salesrep_id IN NUMBER,
208 p_srp_pmt_asgn_id IN NUMBER,
209 p_payrun_id IN NUMBER,
210 p_old_srp_pmt_plans_rec IN srp_pmt_plans_rec_type,
211 p_srp_pmt_plans_rec IN srp_pmt_plans_rec_type,
212 x_status OUT NOCOPY VARCHAR2,
213 x_loading_status OUT NOCOPY VARCHAR2
214 ) IS
215
216 l_api_name CONSTANT VARCHAR2(30) := 'Apply_Payment_Plan_Upd';
217 l_api_version CONSTANT NUMBER := 1.0;
218 oldrec CN_SRP_PMT_PLANS_PUB.srp_pmt_plans_rec_type;
219 newrec CN_SRP_PMT_PLANS_PUB.srp_pmt_plans_rec_type;
220
221 --Bug 3670308 by Julia Huang on 6/4/04
222 CURSOR get_wksht IS
223 SELECT pw.payment_worksheet_id,
224 pw.salesrep_id,
225 p.object_version_number
226 FROM cn_payment_worksheets pw, cn_payruns p, cn_period_statuses ps
227 WHERE pw.salesrep_id = p_salesrep_id
228 AND p.payrun_id = p_payrun_id
229 AND ps.period_id = p.pay_period_id
230 AND pw.payrun_id = p.payrun_id
231 AND ps.org_id = p.org_id
232 AND pw.quota_id IS NULL
233 AND p.status = 'UNPAID'
234 AND EXISTS (SELECT 1 FROM cn_srp_pmt_plans ppa
235 WHERE ppa.srp_pmt_plan_id = p_srp_pmt_asgn_id
236 AND ppa.salesrep_id = pw.salesrep_id
237 AND ppa.start_date <= ps.end_date
238 AND Nvl(ppa.end_date,ps.end_date) >= ps.start_date);
239
240 wksht_recs get_wksht%ROWTYPE;
241
242 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CN_WKSHT_CT_UP_PUB';
243
244 BEGIN
245 --
246 -- Standard Start of API savepoint
247 --
248
249 SAVEPOINT Apply_payment_plan_upd;
250 --
251 -- Standard call to check for call compatibility.
252 --
253
254 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
255 p_api_version ,
256 l_api_name ,
257 G_PKG_NAME )
258 THEN
259 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
260 END IF;
261 --
262 -- Initialize message list if p_init_msg_list is set to TRUE.
263 --
264 IF FND_API.to_Boolean( p_init_msg_list ) THEN
265 FND_MSG_PUB.initialize;
266 END IF;
267 --
268 -- Initialize API return status to success
269 --
270 x_return_status := FND_API.G_RET_STS_SUCCESS;
271
272 --
273 -- API body
274
275 oldrec.salesrep_type := p_old_srp_pmt_plans_rec.salesrep_type;
276 oldrec.emp_num := p_old_srp_pmt_plans_rec.emp_num;
277 oldrec.pmt_plan_name := p_old_srp_pmt_plans_rec.pmt_plan_name;
278 oldrec.minimum_amount := p_old_srp_pmt_plans_rec.minimum_amount;
279 oldrec.maximum_amount := p_old_srp_pmt_plans_rec.maximum_amount;
280 oldrec.start_date := p_old_srp_pmt_plans_rec.start_date;
281 oldrec.end_date := p_old_srp_pmt_plans_rec.end_date;
282
283 newrec.salesrep_type := p_srp_pmt_plans_rec.salesrep_type;
284 newrec.emp_num := p_srp_pmt_plans_rec.emp_num;
285 newrec.pmt_plan_name := p_srp_pmt_plans_rec.pmt_plan_name;
286 newrec.minimum_amount := p_srp_pmt_plans_rec.minimum_amount;
287 newrec.maximum_amount := p_srp_pmt_plans_rec.maximum_amount;
288 newrec.start_date := p_srp_pmt_plans_rec.start_date;
289 newrec.end_date := p_srp_pmt_plans_rec.end_date;
290
291 x_loading_status := 'CN_UPDATED';
292
293 CN_SRP_PMT_PLANS_PUB.Update_Srp_Pmt_Plan
294 ( p_api_version => p_api_version,
295 p_init_msg_list => p_init_msg_list,
296 p_commit => p_commit,
297 p_validation_level=> p_validation_level,
298 x_return_status => x_return_status,
299 x_msg_count => x_msg_count,
300 x_msg_data => x_msg_data,
301 p_old_srp_pmt_plans_rec => oldrec,
302 p_srp_pmt_plans_rec => newrec,
303 x_loading_status => x_loading_status );
304
305 if x_loading_status <> 'CN_UPDATED' then
306 RAISE fnd_api.g_exc_error;
307 end if;
308
309 OPEN get_wksht;
310 LOOP
311 FETCH get_wksht into wksht_recs;
312 exit when get_wksht%NOTFOUND;
313
314 x_loading_status := 'CN_REFRESHED';
315
316 CN_Payment_Worksheet_PVT.Update_Worksheet
317 ( p_api_version => p_api_version,
318 p_init_msg_list => p_init_msg_list,
319 p_commit => p_commit,
320 p_validation_level=> p_validation_level,
321 x_return_status => x_return_status,
322 x_msg_count => x_msg_count,
323 x_msg_data => x_msg_data,
324 p_worksheet_id => wksht_recs.payment_worksheet_id,
325 p_operation => 'REFRESH',
326 x_status => x_status,
327 x_loading_status => x_loading_status,
328 x_ovn => wksht_recs.object_version_number
329 );
330
331 IF x_return_status <> fnd_api.g_ret_sts_success
332 THEN
333 RAISE fnd_api.g_exc_error;
334 END IF;
335
336 END LOOP;
337 close get_wksht;
338
339 -- End of API body.
340 -- Standard check of p_commit.
341 IF FND_API.To_Boolean( p_commit ) THEN
342 COMMIT WORK;
343 END IF;
344
345 --
346 -- Standard call to get message count and if count is 1, get message info.
347 --
348
349 FND_MSG_PUB.Count_And_Get
350 (
351 p_count => x_msg_count ,
352 p_data => x_msg_data ,
353 p_encoded => FND_API.G_FALSE
354 );
355
356
357 EXCEPTION
358 WHEN FND_API.G_EXC_ERROR THEN
359 ROLLBACK TO Apply_payment_plan_upd;
360
361 x_return_status := FND_API.G_RET_STS_ERROR ;
362 FND_MSG_PUB.Count_And_Get
363 (
364 p_count => x_msg_count ,
365 p_data => x_msg_data ,
366 p_encoded => FND_API.G_FALSE
367 );
368 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
369 ROLLBACK TO Apply_payment_plan_upd;
370
371 x_loading_status := 'UNEXPECTED_ERR';
372 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
373 FND_MSG_PUB.Count_And_Get
374 (
375 p_count => x_msg_count ,
376 p_data => x_msg_data ,
377 p_encoded => FND_API.G_FALSE
378 );
379 WHEN OTHERS THEN
380
381
382 ROLLBACK TO Apply_payment_plan_upd ;
383 x_loading_status := 'UNEXPECTED_ERR';
384 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
385 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
386 THEN
387 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
388 END IF;
389 FND_MSG_PUB.Count_And_Get
390 (
391 p_count => x_msg_count ,
392 p_data => x_msg_data ,
393 p_encoded => FND_API.G_FALSE
394 );
395 END Apply_payment_plan_upd;
396
397 --============================================================================
398 Procedure Apply_payment_plan_del
399 ( p_api_version IN NUMBER,
400 p_init_msg_list IN VARCHAR2,
401 p_commit IN VARCHAR2,
402 p_validation_level IN NUMBER,
403 x_return_status OUT NOCOPY VARCHAR2,
404 x_msg_count OUT NOCOPY NUMBER,
405 x_msg_data OUT NOCOPY VARCHAR2,
406 p_salesrep_id IN NUMBER,
407 p_srp_pmt_asgn_id IN NUMBER,
408 p_payrun_id IN NUMBER,
409 p_srp_pmt_plans_rec IN srp_pmt_plans_rec_type,
410 x_status OUT NOCOPY VARCHAR2,
411 x_loading_status OUT NOCOPY VARCHAR2
412 ) IS
413
414 l_api_name CONSTANT VARCHAR2(30) := 'Apply_Payment_Plan_del';
415 l_api_version CONSTANT NUMBER := 1.0;
416 newrec CN_SRP_PMT_PLANS_PUB.srp_pmt_plans_rec_type;
417
418 --Bug 3670308 by Julia Huang on 6/4/04
419 CURSOR get_wksht IS
420 SELECT pw.payment_worksheet_id,
421 pw.salesrep_id,
422 p.object_version_number
423 FROM cn_payment_worksheets pw, cn_payruns p, cn_period_statuses ps
424 WHERE pw.salesrep_id = p_salesrep_id
425 AND p.payrun_id = p_payrun_id
426 AND ps.period_id = p.pay_period_id
427 AND ps.org_id = p.org_id
428 AND pw.payrun_id = p.payrun_id
429 AND pw.quota_id IS NULL
430 AND p.status = 'UNPAID'
431 AND EXISTS (SELECT 1 FROM cn_srp_pmt_plans ppa
432 WHERE ppa.srp_pmt_plan_id = p_srp_pmt_asgn_id
433 AND ppa.salesrep_id = pw.salesrep_id
434 AND ppa.start_date <= ps.end_date
435 AND Nvl(ppa.end_date,ps.end_date) >= ps.start_date);
436
437 wksht_recs get_wksht%ROWTYPE;
438 wksht_recs1 CN_Payment_Worksheet_PVT.worksheet_rec_type;
439
440 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CN_WKSHT_CT_UP_PUB';
441
442 BEGIN
443 --
444 -- Standard Start of API savepoint
445 --
446
447 SAVEPOINT Apply_payment_plan_del;
448 --
449 -- Standard call to check for call compatibility.
450 --
451
452 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
453 p_api_version ,
454 l_api_name ,
455 G_PKG_NAME )
456 THEN
457 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
458 END IF;
459 --
460 -- Initialize message list if p_init_msg_list is set to TRUE.
461 --
462 IF FND_API.to_Boolean( p_init_msg_list ) THEN
463 FND_MSG_PUB.initialize;
464 END IF;
465 --
466 -- Initialize API return status to success
467 --
468 x_return_status := FND_API.G_RET_STS_SUCCESS;
469 x_loading_status := 'CN_DELETED';
470
471 --
472 -- API body
473
474 OPEN get_wksht;
475 LOOP
476 FETCH get_wksht into wksht_recs;
477 exit when get_wksht%NOTFOUND;
478
479 x_loading_status := 'CN_DELETED';
480
481 CN_Payment_Worksheet_PVT.Delete_Worksheet
482 ( p_api_version => p_api_version,
483 p_init_msg_list => p_init_msg_list,
484 p_commit => p_commit,
485 p_validation_level=> p_validation_level,
486 x_return_status => x_return_status,
487 x_msg_count => x_msg_count,
488 x_msg_data => x_msg_data,
489 p_worksheet_id => wksht_recs.payment_worksheet_id,
490 x_status => x_status,
491 x_loading_status => x_loading_status,
492 p_validation_only => 'N',
493 p_ovn => wksht_recs.object_version_number);
494
495 if x_loading_status <> 'CN_DELETED' then
496 RAISE fnd_api.g_exc_error;
497 end if;
498
499 END LOOP;
500 close get_wksht;
501
502 wksht_recs1.payrun_id := p_payrun_id ;
503 wksht_recs1.salesrep_id := p_salesrep_id ;
504
505 x_loading_status := 'CN_DELETED';
506
507
508 newrec.salesrep_type := p_srp_pmt_plans_rec.salesrep_type;
509 newrec.emp_num := p_srp_pmt_plans_rec.emp_num;
510 newrec.pmt_plan_name := p_srp_pmt_plans_rec.pmt_plan_name;
511 newrec.minimum_amount := p_srp_pmt_plans_rec.minimum_amount;
512 newrec.maximum_amount := p_srp_pmt_plans_rec.maximum_amount;
513 newrec.start_date := p_srp_pmt_plans_rec.start_date;
514 newrec.end_date := p_srp_pmt_plans_rec.end_date;
515
516 CN_SRP_PMT_PLANS_PUB.Delete_Srp_Pmt_Plan
517 ( p_api_version => p_api_version,
518 p_init_msg_list => p_init_msg_list,
519 p_commit => p_commit,
520 p_validation_level=> p_validation_level,
521 x_return_status => x_return_status,
522 x_msg_count => x_msg_count,
523 x_msg_data => x_msg_data,
524 p_srp_pmt_plans_rec => newrec,
525 x_loading_status => x_loading_status );
526
527 if x_loading_status <> 'CN_DELETED' then
528 RAISE fnd_api.g_exc_error;
529 end if;
530
531 x_loading_status := 'CN_INSERTED';
532
533 CN_Payment_Worksheet_PVT.Create_Worksheet
534 ( p_api_version => p_api_version,
535 p_init_msg_list => p_init_msg_list,
536 p_commit => p_commit,
537 p_validation_level=> p_validation_level,
538 x_return_status => x_return_status,
539 x_msg_count => x_msg_count,
540 x_msg_data => x_msg_data,
541 p_worksheet_rec => wksht_recs1,
542 x_loading_status => x_loading_status,
543 x_status => x_status );
544
545 if x_loading_status <> 'CN_INSERTED' then
546 RAISE fnd_api.g_exc_error;
547 end if;
548 -- End of API body.
549
550
551 -- Standard check of p_commit.
552 IF FND_API.To_Boolean( p_commit ) THEN
553 COMMIT WORK;
554 END IF;
555
556
557
558 --
559 -- Standard call to get message count and if count is 1, get message info.
560 --
561
562 FND_MSG_PUB.Count_And_Get
563 (
564 p_count => x_msg_count ,
565 p_data => x_msg_data ,
566 p_encoded => FND_API.G_FALSE
567 );
568
569
570 EXCEPTION
571 WHEN FND_API.G_EXC_ERROR THEN
572 ROLLBACK TO Apply_payment_plan_del;
573
574 x_return_status := FND_API.G_RET_STS_ERROR ;
575 FND_MSG_PUB.Count_And_Get
576 (
577 p_count => x_msg_count ,
578 p_data => x_msg_data ,
579 p_encoded => FND_API.G_FALSE
580 );
581 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
582 ROLLBACK TO Apply_payment_plan_del;
583
584 x_loading_status := 'UNEXPECTED_ERR';
585 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
586 FND_MSG_PUB.Count_And_Get
587 (
588 p_count => x_msg_count ,
589 p_data => x_msg_data ,
590 p_encoded => FND_API.G_FALSE
591 );
592 WHEN OTHERS THEN
593
594
595 ROLLBACK TO Apply_payment_plan_del ;
596 x_loading_status := 'UNEXPECTED_ERR';
597 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
598 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
599 THEN
600 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
601 END IF;
602 FND_MSG_PUB.Count_And_Get
603 (
604 p_count => x_msg_count ,
605 p_data => x_msg_data ,
606 p_encoded => FND_API.G_FALSE
607 );
608 END Apply_payment_plan_del;
609
610 --============================================================================
611 Procedure Apply_payment_plan_cre
612 ( p_api_version IN NUMBER,
613 p_init_msg_list IN VARCHAR2,
614 p_commit IN VARCHAR2,
615 p_validation_level IN NUMBER,
616 x_return_status OUT NOCOPY VARCHAR2,
617 x_msg_count OUT NOCOPY NUMBER,
618 x_msg_data OUT NOCOPY VARCHAR2,
619 p_salesrep_id IN NUMBER,
620 p_srp_pmt_asgn_id IN NUMBER,
621 p_payrun_id IN NUMBER,
622 p_srp_pmt_plans_rec IN srp_pmt_plans_rec_type,
623 x_status OUT NOCOPY VARCHAR2,
624 x_loading_status OUT NOCOPY VARCHAR2
625 ) IS
626
627 l_api_name CONSTANT VARCHAR2(30) := 'Apply_Payment_Plan_del';
628 l_api_version CONSTANT NUMBER := 1.0;
629 newrec CN_SRP_PMT_PLANS_PUB.srp_pmt_plans_rec_type;
630
631 --Bug 3670308 by Julia Huang on 6/4/04
632 CURSOR get_wksht IS
633 /*
634 SELECT pw.payment_worksheet_id,
635 pw.salesrep_id
636 FROM cn_payment_worksheets pw,
637 cn_payruns p,
638 cn_srp_pmt_plans_v ppa
639 WHERE ppa.salesrep_id = p_salesrep_id
640 and ppa.srp_pmt_plan_id = p_srp_pmt_asgn_id
641 and p.payrun_id = p_payrun_id
642 and p.pay_period_id = ppa.period_id
643 and pw.salesrep_id = ppa.salesrep_id
644 and pw.payrun_id = p.payrun_id
645 AND pw.quota_id is null
646 and p.status = 'UNPAID' ;
647 */
648 SELECT pw.payment_worksheet_id,
649 pw.salesrep_id,
650 p.object_version_number
651 FROM cn_payment_worksheets pw, cn_payruns p, cn_period_statuses ps
652 WHERE pw.salesrep_id = p_salesrep_id
653 AND p.payrun_id = p_payrun_id
654 AND ps.period_id = p.pay_period_id
655 AND ps.org_id = p.org_id
656 AND pw.payrun_id = p.payrun_id
657 AND pw.quota_id IS NULL
658 AND p.status = 'UNPAID'
659 AND EXISTS (SELECT 1 FROM cn_srp_pmt_plans ppa
660 WHERE ppa.srp_pmt_plan_id = p_srp_pmt_asgn_id
661 AND ppa.salesrep_id = pw.salesrep_id
662 AND ppa.start_date <= ps.end_date
663 AND Nvl(ppa.end_date,ps.end_date) >= ps.start_date);
664
665 wksht_recs get_wksht%ROWTYPE;
666 wksht_recs1 CN_Payment_Worksheet_PVT.worksheet_rec_type;
667
668 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CN_WKSHT_CT_UP_PUB';
669
670 l_srp_pmt_plan_id NUMBER;
671
672 BEGIN
673 --
674 -- Standard Start of API savepoint
675 --
676
677 SAVEPOINT Apply_payment_plan_cre;
678 --
679 -- Standard call to check for call compatibility.
680 --
681
682 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
683 p_api_version ,
684 l_api_name ,
685 G_PKG_NAME )
686 THEN
687 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
688 END IF;
689 --
690 -- Initialize message list if p_init_msg_list is set to TRUE.
691 --
692 IF FND_API.to_Boolean( p_init_msg_list ) THEN
693 FND_MSG_PUB.initialize;
694 END IF;
695 --
696 -- Initialize API return status to success
697 --
698 x_return_status := FND_API.G_RET_STS_SUCCESS;
699 x_loading_status := 'CN_DELETED';
700
701 --
702 -- API body
703
704 OPEN get_wksht;
705 LOOP
706 FETCH get_wksht into wksht_recs;
707 exit when get_wksht%NOTFOUND;
708
709 x_loading_status := 'CN_DELETED';
710
711 CN_Payment_Worksheet_PVT.Delete_Worksheet
712 ( p_api_version => p_api_version,
713 p_init_msg_list => p_init_msg_list,
714 p_commit => p_commit,
715 p_validation_level=> p_validation_level,
716 x_return_status => x_return_status,
717 x_msg_count => x_msg_count,
718 x_msg_data => x_msg_data,
719 p_worksheet_id => wksht_recs.payment_worksheet_id,
720 x_status => x_status,
721 x_loading_status => x_loading_status,
722 p_validation_only => 'N',
723 p_ovn => wksht_recs.object_version_number);
724
725 if x_loading_status <> 'CN_DELETED' then
726 RAISE fnd_api.g_exc_error;
727 end if;
728
729 END LOOP;
730 close get_wksht;
731
732 wksht_recs1.payrun_id := p_payrun_id ;
733 wksht_recs1.salesrep_id := p_salesrep_id ;
734
735 x_loading_status := 'CN_INSERTED';
736
737
738 newrec.salesrep_type := p_srp_pmt_plans_rec.salesrep_type;
739 newrec.emp_num := p_srp_pmt_plans_rec.emp_num;
740 newrec.pmt_plan_name := p_srp_pmt_plans_rec.pmt_plan_name;
741 newrec.minimum_amount := p_srp_pmt_plans_rec.minimum_amount;
742 newrec.maximum_amount := p_srp_pmt_plans_rec.maximum_amount;
743 newrec.start_date := p_srp_pmt_plans_rec.start_date;
744 newrec.end_date := p_srp_pmt_plans_rec.end_date;
745
746 CN_SRP_PMT_PLANS_PUB.Create_Srp_Pmt_Plan
747 ( p_api_version => p_api_version,
748 p_init_msg_list => p_init_msg_list,
749 p_commit => p_commit,
750 p_validation_level=> p_validation_level,
751 x_return_status => x_return_status,
752 x_msg_count => x_msg_count,
753 x_msg_data => x_msg_data,
754 p_srp_pmt_plans_rec => newrec,
755 x_srp_pmt_plan_id => l_srp_pmt_plan_id,
756 x_loading_status => x_loading_status );
757
758 if x_loading_status <> 'CN_INSERTED' then
759 RAISE fnd_api.g_exc_error;
760 end if;
761
762 x_loading_status := 'CN_INSERTED';
763
764 CN_Payment_Worksheet_PVT.Create_Worksheet
765 ( p_api_version => p_api_version,
766 p_init_msg_list => p_init_msg_list,
767 p_commit => p_commit,
768 p_validation_level=> p_validation_level,
769 x_return_status => x_return_status,
770 x_msg_count => x_msg_count,
771 x_msg_data => x_msg_data,
772 p_worksheet_rec => wksht_recs1,
773 x_loading_status => x_loading_status,
774 x_status => x_status );
775
776 if x_loading_status <> 'CN_INSERTED' then
777 RAISE fnd_api.g_exc_error;
778 end if;
779 -- End of API body.
780
781
782 -- Standard check of p_commit.
783 IF FND_API.To_Boolean( p_commit ) THEN
784 COMMIT WORK;
785 END IF;
786
787
788
789 --
790 -- Standard call to get message count and if count is 1, get message info.
791 --
792
793 FND_MSG_PUB.Count_And_Get
794 (
795 p_count => x_msg_count ,
796 p_data => x_msg_data ,
797 p_encoded => FND_API.G_FALSE
798 );
799
800
801 EXCEPTION
802 WHEN FND_API.G_EXC_ERROR THEN
803 ROLLBACK TO Apply_payment_plan_cre;
804
805 x_return_status := FND_API.G_RET_STS_ERROR ;
806 FND_MSG_PUB.Count_And_Get
807 (
808 p_count => x_msg_count ,
809 p_data => x_msg_data ,
810 p_encoded => FND_API.G_FALSE
811 );
812 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
813 ROLLBACK TO Apply_payment_plan_cre;
814
815 x_loading_status := 'UNEXPECTED_ERR';
816 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
817 FND_MSG_PUB.Count_And_Get
818 (
819 p_count => x_msg_count ,
820 p_data => x_msg_data ,
821 p_encoded => FND_API.G_FALSE
822 );
823 WHEN OTHERS THEN
824
825
826 ROLLBACK TO Apply_payment_plan_cre ;
827 x_loading_status := 'UNEXPECTED_ERR';
828 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
829 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
830 THEN
831 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
832 END IF;
833 FND_MSG_PUB.Count_And_Get
834 (
835 p_count => x_msg_count ,
836 p_data => x_msg_data ,
837 p_encoded => FND_API.G_FALSE
838 );
839 END Apply_payment_plan_cre;
840
841 END CN_WKSHT_CT_UP_PUB;