[Home] [Help]
PACKAGE BODY: APPS.CN_PMTPLAN_PVT
Source
1 PACKAGE BODY CN_PMTPLAN_PVT as
2 -- $Header: cnvpplnb.pls 120.11 2006/10/26 10:25:58 sjustina ship $
3 --
4 --
5 -- Procedure : Get_PmtPlan_ID
6 -- Description : This procedure is used to get the ID for the pmt plan
7 --
8 --
9 PROCEDURE Get_PmtPlan_ID
10 (
11 x_return_status OUT NOCOPY VARCHAR2 ,
12 x_msg_count OUT NOCOPY NUMBER ,
13 x_msg_data OUT NOCOPY VARCHAR2 ,
14 p_PmtPlan_rec IN PmtPlan_Rec_Type,
15 p_loading_status IN VARCHAR2,
16 x_pmt_plan_id OUT NOCOPY NUMBER,
17 x_loading_status OUT NOCOPY VARCHAR2,
18 x_status OUT NOCOPY VARCHAR2
19 ) IS
20
21 l_api_name CONSTANT VARCHAR2(30) := 'Get_PmtPlan_ID';
22
23 CURSOR get_PmtPlan_id is
24 SELECT pmt_plan_id
25 FROM cn_pmt_plans
26 WHERE name = p_PmtPlan_rec.name
27 AND start_date = p_PmtPlan_rec.start_date
28 AND end_date = p_PmtPlan_rec.end_date
29 AND org_id = p_PmtPlan_rec.org_id;
30
31 --If end date is null, then use the following cursor
32 CURSOR get_PmtPlan_id2 is
33 SELECT pmt_plan_id
34 FROM cn_pmt_plans
35 WHERE name = p_PmtPlan_rec.name
36 AND start_date = p_PmtPlan_rec.start_date
37 AND org_id = p_PmtPlan_rec.org_id;
38
39 l_get_PmtPlan_id_rec get_PmtPlan_id%ROWTYPE;
40 L_PKG_NAME CONSTANT VARCHAR2(30) := 'CN_PmtPlan_PVT';
41
42
43 BEGIN
44
45 -- Initialize API return status to success
46 x_return_status := FND_API.G_RET_STS_SUCCESS;
47 x_loading_status := p_loading_status;
48 x_status := p_loading_status ;
49
50 --Open appropriate cursor and fetch the payment plan ID
51 IF p_PmtPlan_rec.end_date IS NOT NULL
52 THEN
53
54 OPEN get_PmtPlan_id;
55 FETCH get_PmtPlan_id INTO l_get_PmtPlan_id_rec;
56 IF get_PmtPlan_id%ROWCOUNT = 0
57 THEN
58 x_status := 'NEW PMT PLAN';
59 SELECT nvl(p_PmtPlan_rec.pmt_plan_id,cn_pmt_plans_s.nextval)
60 INTO x_pmt_plan_id
61 FROM dual;
62 ELSIF get_PmtPlan_id%ROWCOUNT = 1
63 THEN
64 x_status := 'PMT PLAN EXISTS';
65 x_pmt_plan_id := l_get_PmtPlan_id_rec.pmt_plan_id;
66 END IF;
67 CLOSE get_PmtPlan_id;
68 ELSE
69 OPEN get_PmtPlan_id2;
70 FETCH get_PmtPlan_id2 INTO l_get_PmtPlan_id_rec;
71 IF get_PmtPlan_id2%ROWCOUNT = 0
72 THEN
73 x_status := 'NEW PMT PLAN';
74 SELECT nvl(p_PmtPlan_rec.pmt_plan_id,cn_pmt_plans_s.nextval)
78 ELSIF get_PmtPlan_id2%ROWCOUNT = 1
75 INTO x_pmt_plan_id
76 FROM dual;
77
79 THEN
80 x_status := 'PMT PLAN EXISTS';
81 x_pmt_plan_id := l_get_PmtPlan_id_rec.pmt_plan_id;
82 END IF;
83 CLOSE get_PmtPlan_id2;
84 END IF;
85
86 EXCEPTION
87 WHEN OTHERS THEN
88
89 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
90 x_loading_status := 'UNEXPECTED_ERR';
91 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
92 THEN
93 FND_MSG_PUB.Add_Exc_Msg( L_PKG_NAME ,l_api_name );
94 END IF;
95
96 END Get_PmtPlan_ID;
97
98
99 --
100 --
101 -- Procedure : Validate_PmtPlan
102 -- Description : This procedure is used to validate the parameters that
103 -- have been passed to create a pmt plan.
104 --
105 --
106 PROCEDURE Validate_PmtPlan
107 (
108 x_return_status OUT NOCOPY VARCHAR2 ,
109 x_msg_count OUT NOCOPY NUMBER ,
110 x_msg_data OUT NOCOPY VARCHAR2 ,
111 p_PmtPlan_rec IN PmtPlan_Rec_Type,
112 p_loading_status IN VARCHAR2,
113 x_loading_status OUT NOCOPY VARCHAR2,
114 x_status OUT NOCOPY VARCHAR2
115 ) IS
116
117 l_count NUMBER;
118 l_pg_count NUMBER;
119 l_pay_interval_type_id NUMBER;
120 l_credit_type_id NUMBER;
121 l_api_name CONSTANT VARCHAR2(30) := 'Validate_PmtPlan';
122 L_PKG_NAME CONSTANT VARCHAR2(30) := 'CN_PmtPlan_PVT';
123
124 CURSOR get_credit_type_id IS
125 SELECT credit_type_id
126 FROM cn_credit_types
127 WHERE name = p_PmtPlan_rec.credit_type_name
128 and org_id = p_PmtPlan_rec.org_id;
129
130 CURSOR get_pay_interval_type_id IS
131 SELECT interval_type_id
132 FROM cn_interval_types
133 WHERE name = p_pmtplan_rec.pay_interval_type_name
134 and org_id = p_PmtPlan_rec.org_id;
135
136 BEGIN
137
138 -- Initialize API return status to success
139 x_return_status := FND_API.G_RET_STS_SUCCESS;
140 x_loading_status := p_loading_status ;
141 x_status := FND_API.G_RET_STS_SUCCESS;
142
143 --
144 -- Check for missing and null parameters.
145 --
146 IF ( (cn_api.chk_miss_char_para
147 (p_char_para => p_PmtPlan_rec.name,
148 p_para_name => 'Pmt Plan Name',
149 p_loading_status => x_loading_status,
150 x_loading_status => x_loading_status)) = FND_API.G_TRUE )
151 THEN
152 RAISE FND_API.G_EXC_ERROR ;
153 END IF;
154
155 IF ( (cn_api.chk_null_char_para
156 (p_char_para => p_PmtPlan_rec.name,
157 p_obj_name => 'Pmt Plan Name',
158 p_loading_status => x_loading_status,
159 x_loading_status => x_loading_status)) = FND_API.G_TRUE )
160 THEN
161 RAISE FND_API.G_EXC_ERROR ;
162 END IF;
163
164 IF g_mode = 'INSERT'
165 THEN
166
167 IF ( (cn_api.chk_miss_char_para
168 (p_char_para => p_PmtPlan_rec.credit_type_name,
169 p_para_name => 'Credit Type Name',
170 p_loading_status => x_loading_status,
171 x_loading_status => x_loading_status)) = FND_API.G_TRUE )
172 THEN
173 RAISE FND_API.G_EXC_ERROR ;
174 END IF;
175
176 IF ( (cn_api.chk_null_char_para
177 (p_char_para => p_PmtPlan_rec.credit_type_name,
178 p_obj_name => 'Credit Type Name',
179 p_loading_status => x_loading_status,
180 x_loading_status => x_loading_status)) = FND_API.G_TRUE )
181 THEN
182 RAISE FND_API.G_EXC_ERROR ;
183 END IF;
184 END IF;
185
186 IF ( (cn_api.chk_miss_date_para
187 (p_date_para => p_PmtPlan_rec.start_date,
188 p_para_name => 'Start Date',
189 p_loading_status => x_loading_status,
190 x_loading_status => x_loading_status)) = FND_API.G_TRUE )
191 THEN
192 RAISE FND_API.G_EXC_ERROR ;
193 END IF;
194
195 IF ( (cn_api.chk_null_date_para
196 (p_date_para => p_PmtPlan_rec.start_date,
197 p_obj_name => 'Start Date',
198 p_loading_status => x_loading_status,
199 x_loading_status => x_loading_status)) = FND_API.G_TRUE )
200 THEN
201 RAISE FND_API.G_EXC_ERROR ;
202 END IF;
203
204 --Check to ensure start date is less than end date
205 --If not, raise an error
206 IF p_PmtPlan_rec.end_date IS NOT NULL
207 AND p_pmtplan_rec.start_date IS NOT NULL
208 AND (p_PmtPlan_rec.start_date > p_PmtPlan_rec.end_date)
209 THEN
210 --Error condition
211 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
212 THEN
213 fnd_message.set_name('CN', 'CN_INVALID_DATE_RANGE');
214 fnd_msg_pub.add;
215 END IF;
216
217 x_loading_status := 'CN_INVALID_DATE_RANGE';
218 RAISE FND_API.G_EXC_ERROR;
219 END IF;
220
221 -- Check Max amount must > Min amount
222 IF p_pmtplan_rec.minimum_amount IS NOT NULL
223 AND p_pmtplan_rec.maximum_amount IS NOT NULL
224 THEN
225
226 IF (p_pmtplan_rec.maximum_amount < p_pmtplan_rec.minimum_amount)
227 THEN
228 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
229 FND_MESSAGE.SET_NAME ('CN' , 'CN_SPP_MAX_LT_MIN');
230 FND_MSG_PUB.Add;
231 END IF;
232 x_loading_status := 'CN_SPP_MAX_LT_MIN';
233 RAISE FND_API.G_EXC_ERROR ;
234 END IF;
235 END IF;
236
237 --Check for min_rec_flag and max_rec_flag
238 IF p_pmtplan_rec.min_rec_flag IS NOT NULL
239 THEN
243 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
240 IF p_pmtplan_rec.min_rec_flag NOT IN ('Y', 'N')
241 THEN
242 --Error condition
244 THEN
245 fnd_message.set_name('CN', 'CN_INVALID_PMT_PLAN_FLAGS');
246 fnd_msg_pub.add;
247 END IF;
248
249 x_status := 'CN_INVALID_PMT_PLAN_FLAGS';
250 x_loading_status := 'CN_INVALID_PMT_PLAN_FLAGS';
251 RAISE FND_API.G_EXC_ERROR;
252 END IF;
253 END IF;
254
255 IF p_pmtplan_rec.max_rec_flag IS NOT NULL
256 THEN
257 IF p_pmtplan_rec.max_rec_flag NOT IN ('Y', 'N')
258 THEN
259
260 --Error condition
261 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
262 THEN
263 fnd_message.set_name('CN', 'CN_INVALID_PMT_PLAN_FLAGS');
264 fnd_msg_pub.add;
265 END IF;
266
267 x_status := 'CN_INVALID_PMT_PLAN_FLAGS';
268 x_loading_status := 'CN_INVALID_PMT_PLAN_FLAGS';
269 RAISE FND_API.G_EXC_ERROR;
270 END IF;
271 END IF;
272
273 --Added by Sundar Venkat Null check for Payment_Group_Code
274
275
276 IF ( (cn_api.chk_miss_char_para
277 (p_char_para => p_PmtPlan_rec.payment_group_code,
278 p_para_name => 'Payment Group Code',
279 p_loading_status => x_loading_status,
280 x_loading_status => x_loading_status)) = FND_API.G_TRUE )
281 THEN
282 RAISE FND_API.G_EXC_ERROR ;
283 END IF;
284
285 IF ( (cn_api.chk_null_char_para
286 (p_char_para => p_PmtPlan_rec.payment_group_code,
287 p_obj_name => 'Payment Group Code',
288 p_loading_status => x_loading_status,
289 x_loading_status => x_loading_status)) = FND_API.G_TRUE )
290 THEN
291 RAISE FND_API.G_EXC_ERROR ;
292 END IF;
293
294 -- Check for a valid payment group code. Added by Raja Ramasamy on 7-oct-2005
295
296 select count(1) into l_pg_count from cn_lookups
297 where lookup_type like 'PAYMENT_GROUP_CODE'
298 and lookup_code = p_PmtPlan_rec.payment_group_code;
299
300 if (l_pg_count = 0)
301 then
302 --Error condition
303 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
304 THEN
305 fnd_message.set_name('CN', 'CN_PAY_INVALID_PG_CODE');
306 fnd_msg_pub.add;
307 END IF;
308
309 x_status := 'CN_PAY_INVALID_PG_CODE';
310 x_loading_status := 'CN_PAY_INVALID_PG_CODE';
311 RAISE FND_API.G_EXC_ERROR;
312 end if;
313
314 -- Since payment plan names are unique in an org, check if a record already exists with the same name.
315
316 SELECT COUNT(*)
317 INTO l_count
318 FROM cn_pmt_plans
319 WHERE name = p_PmtPlan_rec.name
320 and org_id = p_PmtPlan_rec.org_id;
321
322 IF (l_count <> 0) THEN
323 x_status := 'PMT PLAN EXISTS';
324 END IF ;
325
326 -- Validate for invalid credit type
327 OPEN get_credit_type_id;
328 FETCH get_credit_type_id INTO l_credit_type_id;
329 IF get_credit_type_id%ROWCOUNT = 0
330 THEN
331
332 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
333 THEN
334
335 fnd_message.set_name('CN', 'CN_INVALID_CREDIT_TYPE');
336 fnd_msg_pub.add;
337 END IF;
338
339 x_loading_status := 'CN_INVALID_CREDIT_TYPE';
340 CLOSE get_credit_type_id;
341 RAISE FND_API.G_EXC_ERROR;
342 END IF;
343
344 CLOSE get_credit_type_id;
345
346 -- Validate for correct pay interval type id
347
348 IF p_pmtplan_rec.pay_interval_type_name IS NOT NULL
349 THEN
350 OPEN get_pay_interval_type_id;
351 FETCH get_pay_interval_type_id INTO l_pay_interval_type_id;
352 IF get_pay_interval_type_id%ROWCOUNT = 0 OR
353 l_pay_interval_type_id NOT IN (-1000, -1001, -1002)
354 THEN
355 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
356 THEN
357 fnd_message.set_name('CN', 'CN_INVALID_PAY_INTERVAL');
358 fnd_msg_pub.add;
359 END IF;
360 x_loading_status := 'CN_INVALID_PAY_INTERVAL';
361 CLOSE get_pay_interval_type_id;
362 RAISE FND_API.G_EXC_ERROR;
363 END IF;
364 CLOSE get_pay_interval_type_id;
365 END IF;
366
367
368 -- End of Validate Pmt Plans.
369 -- Standard call to get message count and if count is 1,
370 -- get message info.
371
372 FND_MSG_PUB.Count_And_Get
373 (
374 p_count => x_msg_count,
375 p_data => x_msg_data,
376 p_encoded => FND_API.G_FALSE
377 );
378
379 EXCEPTION
380 WHEN FND_API.G_EXC_ERROR THEN
381 x_return_status := FND_API.G_RET_STS_ERROR ;
382
383 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
384 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
385 x_loading_status := 'UNEXPECTED_ERR';
386
387 WHEN OTHERS THEN
388 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
389 x_loading_status := 'UNEXPECTED_ERR';
390 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
391 THEN
392 FND_MSG_PUB.Add_Exc_Msg( L_PKG_NAME ,l_api_name );
393 END IF;
394
395 END Validate_PmtPlan;
396
397 --
398 -- Procedure : Create_PmtPlan
399 -- Description: Public API to create a pmt plan
400 -- Calls : validate_pmt_plan
404 p_api_version IN NUMBER,
401 -- CN_Pmt_Plans_Pkg.Begin_Record
402 --
403 PROCEDURE Create_PmtPlan(
405 p_init_msg_list IN VARCHAR2 ,
406 p_commit IN VARCHAR2,
407 p_validation_level IN NUMBER,
408 x_return_status OUT NOCOPY VARCHAR2,
409 x_msg_count OUT NOCOPY NUMBER,
410 x_msg_data OUT NOCOPY VARCHAR2,
411 p_PmtPlan_rec IN OUT NOCOPY PmtPlan_Rec_Type,
412 x_loading_status OUT NOCOPY VARCHAR2,
413 x_status OUT NOCOPY VARCHAR2
414 ) IS
415
416 l_api_name CONSTANT VARCHAR2(30) := 'Create_PmtPlan';
417 l_api_version CONSTANT NUMBER := 1.0;
418
419 l_pmt_plan_id NUMBER;
420 l_credit_type_id NUMBER;
421 l_pay_interval_type_id NUMBER;
422
423 l_recoverable_interval_type_id NUMBER;
424
425 l_pay_against_commission VArchar2(02);
426
427 L_PKG_NAME CONSTANT VARCHAR2(30) := 'CN_PmtPlan_PVT';
428 L_LAST_UPDATE_DATE DATE := sysdate;
429 L_LAST_UPDATED_BY NUMBER := fnd_global.user_id;
430 L_CREATION_DATE DATE := sysdate;
431 L_CREATED_BY NUMBER := fnd_global.user_id;
432 L_LAST_UPDATE_LOGIN NUMBER := fnd_global.login_id;
433 L_ROWID VARCHAR2(30);
434 L_PROGRAM_TYPE VARCHAR2(30);
435
436 CURSOR get_credit_type_id IS
437 SELECT credit_type_id
438 FROM cn_credit_types
439 WHERE name = p_PmtPlan_rec.credit_type_name
440 and org_id = p_PmtPlan_rec.org_id;
441
442 CURSOR get_pay_interval_type_id IS
443 SELECT interval_type_id
444 FROM cn_interval_types
445 WHERE name = p_pmtplan_rec.pay_interval_type_name
446 and org_id = p_PmtPlan_rec.org_id;
447
448
449 CURSOR get_rec_interval_type_id IS
450 SELECT interval_type_id
451 FROM cn_interval_types
452 WHERE name = p_pmtplan_rec.recoverable_interval_type
453 and org_id = p_PmtPlan_rec.org_id;
454
455 BEGIN
456
457 --
458 -- Standard Start of API savepoint
459 --
460 SAVEPOINT Create_PmtPlan;
461 --
462 -- Standard call to check for call compatibility.
463 --
464 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
465 p_api_version ,
466 l_api_name ,
467 L_PKG_NAME )
468 THEN
469 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
470 END IF;
471 --
472 -- Initialize message list if p_init_msg_list is set to TRUE.
473 --
474 IF FND_API.to_Boolean( p_init_msg_list ) THEN
475 FND_MSG_PUB.initialize;
476 END IF;
477 --
478 -- Initialize API return status to success
479 --
480 x_return_status := FND_API.G_RET_STS_SUCCESS;
481 x_loading_status := 'CN_INSERTED';
482 --
483 -- API body
484 --
485
486 --
487 --Initialize g_mode
488 --
489 g_mode := 'INSERT';
490
491 Validate_PmtPlan(
492 x_return_status => x_return_status,
493 x_msg_count => x_msg_count,
494 x_msg_data => x_msg_data,
495 p_PmtPlan_rec => p_PmtPlan_rec,
496 p_loading_status => x_loading_status,
497 x_loading_status => x_loading_status,
498 x_status => x_status
499 );
500
501 IF ( x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
502 RAISE FND_API.G_EXC_ERROR ;
503 ELSIF ( x_return_status = FND_API.G_RET_STS_SUCCESS ) AND ( x_status <> 'PMT PLAN EXISTS' )
504 THEN
505
506 get_PmtPlan_id(
507 x_return_status => x_return_status,
508 x_msg_count => x_msg_count,
509 x_msg_data => x_msg_data,
510 p_PmtPlan_rec => p_PmtPlan_rec,
511 x_pmt_plan_id => l_pmt_plan_id,
512 p_loading_status => x_loading_status,
513 x_loading_status => x_loading_status,
514 x_status => x_status
515 );
516
517 p_PmtPlan_rec.pmt_plan_id := l_pmt_plan_id;
518
519 -- At this point, credit type is already validated in Validate_PmtPlan
520 -- Get the credit type id for the given credit type name
521 OPEN get_credit_type_id;
522 FETCH get_credit_type_id INTO l_credit_type_id;
523
524 -- At this point, Pay interval type name is already validate in Validate_pmtPlan method
525 -- Get the Pay interval type id for the given pay interval type name
526 IF p_pmtplan_rec.pay_interval_type_name IS NOT NULL
527 THEN
528 OPEN get_pay_interval_type_id;
529 FETCH get_pay_interval_type_id INTO l_pay_interval_type_id;
530 else
531 l_pay_interval_type_id := -1000;
532 END IF;
533
534 -- Recoverable Interval type
535
536 IF p_pmtplan_rec.recoverable_interval_type IS NOT NULL
537 THEN
538 OPEN get_rec_interval_type_id;
539 FETCH get_rec_interval_type_id INTO l_recoverable_interval_type_id;
540 CLOSE get_rec_interval_type_id;
541 END IF;
542
543 l_pay_against_commission:= p_pmtplan_rec.pay_against_commission ;
544
545
546 if l_recoverable_interval_type_id is NULL OR
547 l_recoverable_interval_type_id = -1000 THEN
548
549 if l_pay_interval_type_id = -1000 THEN
550
551 l_recoverable_interval_type_id := -1000;
555
552 l_pay_against_commission := p_pmtplan_rec.pay_against_commission;
553
554 elsif l_pay_interval_type_id <> -1000 THEN
556 l_recoverable_interval_type_id := l_pay_interval_type_id;
557 --l_pay_against_commission := 'N';
558 -- Added by Kumar find a bug
559 l_pay_against_commission := p_pmtplan_rec.pay_against_commission;
560
561 end if;
562
563 else
564
565 if ( l_recoverable_interval_type_id = -1001 and
566 l_pay_interval_type_id = -1002 ) THEN
567
568 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
569 THEN
570 fnd_message.set_name('CN', 'CN_INVALID_PAY_INT_AND_REC_INT');
571 fnd_msg_pub.add;
572 END IF;
573
574 x_status := 'CN_INV_PAY_INT_AND_REC';
575 x_loading_status := 'CCN_INV_PAY_INT_AND_REC';
576 RAISE FND_API.G_EXC_ERROR;
577 end if;
578
579 end if;
580
581 -- added on 02/nov/2001 only the additional and
582 IF l_recoverable_interval_type_id IN ( -1001, -1002 ) AND
583 l_recoverable_interval_type_id <> l_pay_interval_type_id and
584 nvl(l_pay_against_commission,'Y') <> 'N' THEN
585 --Error condition
586 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
587 THEN
588 fnd_message.set_name('CN', 'CN_INVALID_REC_AND_PAC');
589 fnd_msg_pub.add;
590 END IF;
591
592 x_status := 'CN_INVALID_REC_AND_PAC';
593 x_loading_status := 'CCN_INVALID_REC_AND_PAC';
594 RAISE FND_API.G_EXC_ERROR;
595 END IF;
596
597 -- added on 02/nov/2001 only the additional and
598 IF nvl(l_recoverable_interval_type_id,0) NOT IN ( -1001, -1002 ) AND
599 l_recoverable_interval_type_id <> l_pay_interval_type_id and
600 nvl(l_pay_against_commission,'Y') = 'N' THEN
601 --Error condition
602 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
603 THEN
604 fnd_message.set_name('CN', 'CN_INVALID_REC_AND_PAC');
605 fnd_msg_pub.add;
606 END IF;
607
608 x_status := 'CN_INVALID_REC_AND_PAC';
609 x_loading_status := 'CCN_INVALID_REC_AND_PAC';
610 RAISE FND_API.G_EXC_ERROR;
611 END IF;
612
613 CN_Pmt_Plans_Pkg.Begin_Record
614 (
615 x_operation => 'INSERT',
616 x_rowid => L_ROWID,
617 x_org_id => p_PmtPlan_rec.org_id,
618 x_pmt_plan_id => p_PmtPlan_rec.pmt_plan_id,
619 x_name => p_PmtPlan_rec.name,
620 x_minimum_amount => p_PmtPlan_rec.minimum_amount,
621 x_maximum_amount => p_PmtPlan_rec.maximum_amount,
622 x_min_rec_flag => Nvl(p_PmtPlan_rec.min_rec_flag, 'Y'),
623 x_max_rec_flag => Nvl(p_PmtPlan_rec.max_rec_flag, 'Y'),
624 x_max_recovery_amount => p_PmtPlan_rec.max_recovery_amount,
625 x_credit_type_id => l_credit_type_id,
626 x_pay_interval_type_id => l_pay_interval_type_id,
627 x_start_date => p_PmtPlan_rec.start_date,
628 x_end_date => p_PmtPlan_rec.end_date,
629 x_object_version_number => p_PmtPlan_rec.object_version_number,
630 x_recoverable_interval_type_id => l_recoverable_interval_type_id,
631 x_pay_against_commission => l_pay_against_commission,
632 x_attribute_category => p_PmtPlan_rec.attribute_category,
633 x_attribute1 => p_PmtPlan_rec.attribute1,
634 x_attribute2 => p_PmtPlan_rec.attribute2,
635 x_attribute3 => p_PmtPlan_rec.attribute3,
636 x_attribute4 => p_PmtPlan_rec.attribute4,
637 x_attribute5 => p_PmtPlan_rec.attribute5,
638 x_attribute6 => p_PmtPlan_rec.attribute6,
639 x_attribute7 => p_PmtPlan_rec.attribute7,
640 x_attribute8 => p_PmtPlan_rec.attribute8,
641 x_attribute9 => p_PmtPlan_rec.attribute9,
642 x_attribute10 => p_PmtPlan_rec.attribute10,
643 x_attribute11 => p_PmtPlan_rec.attribute10,
644 x_attribute12 => p_PmtPlan_rec.attribute12,
645 x_attribute13 => p_PmtPlan_rec.attribute13,
646 x_attribute14 => p_PmtPlan_rec.attribute14,
647 x_attribute15 => p_PmtPlan_rec.attribute15,
648 x_last_update_date => l_last_update_date,
649 x_last_updated_by => l_last_updated_by,
650 x_creation_date => l_creation_date,
651 x_created_by => l_created_by,
652 x_last_update_login => l_last_update_login,
653 x_program_type => l_program_type,
654 x_payment_group_code => p_PmtPlan_rec.payment_group_code
655 );
656
657 x_loading_status := 'CN_INSERTED';
658 ELSE
659 -- The pmt plan already exists - Raise an Error Meassge
660 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
661 THEN
662 FND_MESSAGE.SET_NAME ('CN' , 'CN_PMT_PLAN_EXISTS');
663 FND_MSG_PUB.Add;
664 END IF;
665 x_loading_status := 'CN_PMT_PLAN_EXISTS';
666 RAISE FND_API.G_EXC_ERROR ;
667 END IF;
668
669 -- End of API body.
670
671 -- Standard check of p_commit.
672 IF FND_API.To_Boolean( p_commit ) THEN
673 COMMIT WORK;
674 END IF;
675
676 --
677 -- Standard call to get message count and if count is 1, get message info.
678 --
679
680 FND_MSG_PUB.Count_And_Get
681 (
682 p_count => x_msg_count ,
683 p_data => x_msg_data ,
684 p_encoded => FND_API.G_FALSE
685 );
686
687 EXCEPTION
688 WHEN FND_API.G_EXC_ERROR THEN
689 ROLLBACK TO Create_PmtPlan;
693 p_count => x_msg_count ,
690 x_return_status := FND_API.G_RET_STS_ERROR ;
691 FND_MSG_PUB.Count_And_Get
692 (
694 p_data => x_msg_data ,
695 p_encoded => FND_API.G_FALSE
696 );
697 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
698 ROLLBACK TO Create_PmtPlan;
699 x_loading_status := 'UNEXPECTED_ERR';
700 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
701 FND_MSG_PUB.Count_And_Get
702 (
703 p_count => x_msg_count ,
704 p_data => x_msg_data ,
705 p_encoded => FND_API.G_FALSE
706 );
707 WHEN OTHERS THEN
708 ROLLBACK TO Create_PmtPlan;
709 x_loading_status := 'UNEXPECTED_ERR';
710 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
711 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
712 THEN
713 FND_MSG_PUB.Add_Exc_Msg( L_PKG_NAME ,l_api_name );
714 END IF;
715 FND_MSG_PUB.Count_And_Get
716 (
717 p_count => x_msg_count ,
718 p_data => x_msg_data ,
719 p_encoded => FND_API.G_FALSE
720 );
721 END Create_PmtPlan;
722
723 --
724 -- Procedure : Update PmtPlan
725 -- Description : This is a public procedure to update pmt plans
726 -- Calls : validate_pmt_plan
727 -- CN_Pmt_Plans_Pkg.Begin_Record
728 --
729
730 PROCEDURE Update_PmtPlan (
731 p_api_version IN NUMBER,
732 p_init_msg_list IN VARCHAR2,
733 p_commit IN VARCHAR2,
734 p_validation_level IN NUMBER,
735 x_return_status OUT NOCOPY VARCHAR2,
736 x_msg_count OUT NOCOPY NUMBER,
737 x_msg_data OUT NOCOPY VARCHAR2,
738 p_old_PmtPlan_rec IN PmtPlan_rec_type,
739 p_PmtPlan_rec IN OUT NOCOPY PmtPlan_rec_type,
740 x_status OUT NOCOPY VARCHAR2,
741 x_loading_status OUT NOCOPY VARCHAR2
742 ) IS
743
744 l_api_name CONSTANT VARCHAR2(30) := 'Update_PmtPlan';
745 l_api_version CONSTANT NUMBER := 1.0;
746 l_PmtPlans_rec PmtPlan_rec_type;
747 l_org_id NUMBER;
748 l_pmt_plan_id NUMBER;
749 l_credit_type_id NUMBER;
750 l_pay_interval_type_id NUMBER;
751 l_rec_interval_type_id NUMBER;
752 l_pay_Against_commission VArchar2(02);
753
754 l_recoverable_interval_type_id NUMBER;
755 l_count NUMBER := 0;
756 l_start_date DATE;
757 l_end_date DATE;
758 l_null_end_date_srps NUMBER := 0;
759
760 L_PKG_NAME CONSTANT VARCHAR2(30) := 'CN_PmtPlan_PVT';
761 L_LAST_UPDATE_DATE DATE := sysdate;
762 L_LAST_UPDATED_BY NUMBER := fnd_global.user_id;
763 L_CREATION_DATE DATE := sysdate;
764 L_CREATED_BY NUMBER := fnd_global.user_id;
765 L_LAST_UPDATE_LOGIN NUMBER := fnd_global.login_id;
766 L_ROWID VARCHAR2(30);
767 L_PROGRAM_TYPE VARCHAR2(30);
768
769 CURSOR get_credit_type_id IS
770 SELECT credit_type_id
771 FROM cn_credit_types
772 WHERE name = p_PmtPlan_rec.credit_type_name
773 and org_id = p_PmtPlan_rec.org_id;
774
775
776 CURSOR get_pay_interval_type_id IS
777 SELECT interval_type_id
778 FROM cn_interval_types
779 WHERE name = p_pmtplan_rec.pay_interval_type_name
780 AND org_id = p_PmtPlan_rec.org_id;
781
782 CURSOR get_pmt_plan (p_pmt_plan_id NUMBER) IS
783 SELECT *
784 FROM cn_pmt_plans
785 WHERE pmt_plan_id = p_pmt_plan_id
786 AND org_id = p_PmtPlan_rec.org_id;
787
788 l_pp_rec get_pmt_plan%ROWTYPE;
789
790 l_old_PmtPlan_rec PmtPlan_rec_type;
791 l_pp_oldrec get_pmt_plan%ROWTYPE;
792
793 CURSOR get_credit_type_curs ( l_credit_type_id VArchar2) IS
794 SELECT name
795 FROM cn_credit_types
796 WHERE credit_type_id = l_credit_type_id
797 AND org_id = p_PmtPlan_rec.org_id;
798
799 CURSOR get_interval_type_curs ( l_interval_type_id Varchar2 ) IS
800 SELECT name
801 FROM cn_interval_types
802 WHERE interval_type_id = l_interval_type_id
803 AND org_id = p_PmtPlan_rec.org_id;
804
805 CURSOR l_ovn_csr IS
806 SELECT nvl(object_version_number,1)
807 FROM cn_pmt_plans
808 WHERE pmt_plan_id = p_old_PmtPlan_rec.pmt_plan_id
809 AND org_id = p_old_PmtPlan_rec.org_id;
810
811 l_object_version_number NUMBER;
812
813 CURSOR get_rec_interval_type_curs ( l_rec_interval_type_id NUMBER ) IS
814 SELECT name
815 FROM cn_interval_types
816 WHERE interval_type_id = l_rec_interval_type_id
817 AND org_id = p_PmtPlan_rec.org_id;
818
819
820 CURSOR get_rec_interval_type_id ( l_rec_interval_type VARCHAR2 ) IS
821 SELECT interval_type_id
822 FROM cn_interval_types
823 WHERE name = l_rec_interval_type
824 AND org_id = p_PmtPlan_rec.org_id;
825
826 BEGIN
827
828 --
829 -- Standard Start of API savepoint
830 --
831 SAVEPOINT Update_PmtPlan;
832 --
833 -- Standard call to check for call compatibility.
834 --
835 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
836 p_api_version ,
837 l_api_name ,
838 L_PKG_NAME )
839 THEN
840 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
841 END IF;
842 --
843 -- Initialize message list if p_init_msg_list is set to TRUE.
844 --
845 IF FND_API.to_Boolean( p_init_msg_list ) THEN
846 FND_MSG_PUB.initialize;
847 END IF;
848 --
852 x_loading_status := 'CN_UPDATED';
849 -- Initialize API return status to success
850 --
851 x_return_status := FND_API.G_RET_STS_SUCCESS;
853 --
854 -- API body
855 --
856
857 --
858 --Initialize g_mode
859 --
860 g_mode := 'UPDATE';
861
862 --
863 -- get the old Record
864 --
865
866 open get_pmt_plan( p_old_PmtPlan_rec.pmt_plan_id);
867 fetch get_pmt_plan into l_pp_oldrec;
868 close get_pmt_plan;
869
870 --
871 -- get credit types
872 --
873 open get_credit_type_curs( l_pp_oldrec.credit_type_id );
874 fetch get_credit_type_curs into l_old_PmtPlan_rec.credit_type_name;
875 close get_credit_type_curs;
876
877 --
878 -- get interval types
879 --
880 open get_interval_type_curs( l_pp_oldrec.pay_interval_type_id );
881 fetch get_interval_type_curs into l_old_PmtPlan_rec.pay_interval_type_name;
882 close get_interval_type_curs;
883
884 --
885 -- get recoverable interval types
886 --
887
888 open get_rec_interval_type_curs( l_pp_oldrec.recoverable_interval_type_id );
889 fetch get_rec_interval_type_curs into l_old_PmtPlan_rec.recoverable_interval_type;
890 close get_rec_interval_type_curs;
891
892 l_old_PmtPlan_rec.org_id := l_pp_oldrec.org_id;
893 l_old_PmtPlan_rec.pmt_plan_id := l_pp_oldrec.pmt_plan_id;
894 l_old_PmtPlan_rec.name := l_pp_oldrec.name;
895 l_old_PmtPlan_rec.minimum_amount := l_pp_oldrec.minimum_amount;
896 l_old_PmtPlan_rec.maximum_amount := l_pp_oldrec.maximum_amount;
897 l_old_PmtPlan_rec.min_rec_flag := l_pp_oldrec.min_rec_flag;
898 l_old_PmtPlan_rec.max_rec_flag := l_pp_oldrec.max_rec_flag;
899 l_old_PmtPlan_rec.start_date := l_pp_oldrec.start_date;
900 l_old_PmtPlan_rec.end_date := l_pp_oldrec.end_date;
901 l_old_PmtPlan_rec.object_version_number := l_pp_oldrec.object_version_number;
902
903 -- Validation
904 --
905 --
906 --Validate if start date is less than end date
907 --
908 IF p_pmtplan_rec.start_date <> fnd_api.g_miss_date
909 AND p_pmtplan_rec.start_date IS NULL
910 THEN
911 --Error condition
912 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
913 THEN
914 fnd_message.set_name('CN', 'CN_PP_SDT_CANNOT_NULL');
915 fnd_msg_pub.add;
916 END IF;
917
918 x_loading_status := 'CN_PP_SDT_CANNOT_NULL';
919 RAISE FND_API.G_EXC_ERROR;
920 END IF;
921
922 IF p_pmtplan_rec.start_date IS NOT NULL --start date has been updated
923 THEN
924 IF p_PmtPlan_rec.end_date IS NOT NULL
925 AND (p_PmtPlan_rec.start_date > p_PmtPlan_rec.end_date)
926 THEN
927 --Error condition
928 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
929 THEN
930 fnd_message.set_name('CN', 'CN_INVALID_DATE_RANGE');
931 fnd_msg_pub.add;
932 END IF;
933
934 x_loading_status := 'CN_INVALID_DATE_RANGE';
935 RAISE FND_API.G_EXC_ERROR;
936 END IF;
937 ELSE
938 IF l_old_PmtPlan_rec.end_date IS NOT NULL
939 AND (p_PmtPlan_rec.start_date > l_old_pmtPlan_Rec.end_date)
940 THEN
941 --Error condition
942 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
943 THEN
944 fnd_message.set_name('CN', 'CN_INVALID_DATE_RANGE');
945 fnd_msg_pub.add;
946 END IF;
947
948 x_loading_status := 'CN_INVALID_DATE_RANGE';
949 RAISE FND_API.G_EXC_ERROR;
950 END IF;
951 END IF;
952
953 get_PmtPlan_id(
954 x_return_status => x_return_status,
955 x_msg_count => x_msg_count,
956 x_msg_data => x_msg_data,
957 p_PmtPlan_rec => l_old_pmtPlan_Rec,
958 p_loading_status => x_loading_status,
959 x_pmt_plan_id => l_pmt_plan_id,
960 x_loading_status => x_loading_status,
961 x_status => x_status
962 );
963
964 -- check if the object version number is the same
965 OPEN l_ovn_csr;
966 FETCH l_ovn_csr INTO l_object_version_number;
967 CLOSE l_ovn_csr;
968
969 IF (l_object_version_number <>
970 p_pmtplan_rec.object_version_number) THEN
971
972 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
973 THEN
974 fnd_message.set_name('CN', 'CN_INVALID_OBJECT_VERSION');
975 fnd_msg_pub.add;
976 END IF;
977
978 x_loading_status := 'CN_INVALID_OBJECT_VERSION';
979 RAISE FND_API.G_EXC_ERROR;
980
981 end if;
982
983 IF ( x_return_status <> FND_API.G_RET_STS_SUCCESS )
984 THEN
985
986 RAISE fnd_api.g_exc_error;
987
988 ELSIF x_status <> 'PMT PLAN EXISTS'
989 THEN
990
991 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
992 THEN
993 FND_MESSAGE.SET_NAME ('CN' , 'CN_INVALID_PMT_PLAN');
994 fnd_message.set_token('PMT_PLAN_NAME', l_old_pmtPlan_Rec.name);
995 FND_MSG_PUB.Add;
996 END IF;
997
998 x_loading_status := 'CN_INVALID_PMT_PLAN';
999 RAISE FND_API.G_EXC_ERROR ;
1000
1001 END IF;
1002
1003 SELECT COUNT(1)
1004 INTO l_count
1005 FROM cn_srp_pmt_plans
1006 WHERE pmt_plan_id = l_pmt_plan_id;
1007
1008 -- If pmt plan has been assigned, select current definition of pmt plan
1009 -- Ensure min_rec_flag and max_rec_flag are not updated
1010 -- Start date and end date can only be updated in such a way that they do not
1011 -- affect the assignment dates
1012
1016 OPEN get_pmt_plan(l_pmt_plan_id);
1013 IF l_count <> 0
1014 THEN
1015 --select current definition of pmt plan and compare with new definition
1017 FETCH get_pmt_plan INTO l_pp_rec;
1018 CLOSE get_pmt_plan;
1019
1020 IF ( nvl(p_pmtplan_rec.min_rec_flag,'N') IS NOT NULL
1021 AND nvl(p_pmtplan_rec.min_rec_flag,'N') <> l_pp_rec.min_rec_flag)
1022 OR ( nvl(p_pmtplan_rec.max_rec_flag,'N') IS NOT NULL
1023 AND nvl(p_pmtplan_rec.max_rec_flag,'N') <> l_pp_rec.max_rec_flag)
1024 THEN
1025
1026 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1027 THEN
1028 FND_MESSAGE.SET_NAME ('CN' , 'CN_REC_FLG_UPD_NA');
1029 FND_MSG_PUB.Add;
1030 END IF;
1031
1032 x_loading_status := 'CN_REC_FLG_UPD_NA';
1033 RAISE FND_API.G_EXC_ERROR ;
1034 ELSE IF(p_pmtplan_rec.payment_group_code <> l_pp_rec.payment_group_code)
1035 THEN
1036 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1037 THEN
1038 FND_MESSAGE.SET_NAME ('CN' , 'CN_PYMT_GRP_CODE_UPD_NA');
1039 FND_MSG_PUB.Add;
1040 END IF;
1041
1042 x_loading_status := 'CN_PYMT_GRP_CODE_UPD_NA';
1043 RAISE FND_API.G_EXC_ERROR ;
1044 END IF;
1045 END IF;
1046
1047 SELECT MIN(start_date)
1048 INTO l_start_date
1049 FROM cn_srp_pmt_plans
1050 WHERE pmt_plan_id = l_pmt_plan_id;
1051
1052 SELECT MAX(end_date)
1053 INTO l_end_date
1054 FROM cn_srp_pmt_plans
1055 WHERE pmt_plan_id = l_pmt_plan_id
1056 AND end_date IS NOT NULL;
1057
1058 SELECT count(1)
1059 INTO l_null_end_date_srps
1060 FROM cn_srp_pmt_plans
1061 WHERE pmt_plan_id = l_pmt_plan_id
1062 AND end_date IS NULL;
1063
1064 IF l_start_date < p_pmtplan_rec.start_date
1065 OR l_end_date > p_pmtplan_rec.end_date
1066 OR l_null_end_date_srps = 1
1067 THEN
1068
1069 --Error condition
1070 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
1071 THEN
1072 fnd_message.set_name('CN', 'CN_PMT_PLAN_CHANGE_NA');
1073 fnd_msg_pub.add;
1074 END IF;
1075
1076 x_status := 'CN_PMT_PLAN_CHANGE_NA';
1077 x_loading_status := 'CN_PMT_PLAN_CHANGE_NA';
1078 RAISE FND_API.G_EXC_ERROR;
1079
1080 END IF;
1081
1082 END IF;
1083
1084 Validate_PmtPlan(
1085 x_return_status => x_return_status,
1086 x_msg_count => x_msg_count,
1087 x_msg_data => x_msg_data,
1088 p_PmtPlan_rec => p_PmtPlan_rec,
1089 p_loading_status => x_loading_status,
1090 x_loading_status => x_loading_status,
1091 x_status => x_status
1092 );
1093
1094 IF ( x_return_status <> FND_API.G_RET_STS_SUCCESS)
1095 THEN
1096 RAISE FND_API.G_EXC_ERROR ;
1097 ELSIF ( x_return_status = FND_API.G_RET_STS_SUCCESS )
1098 -- AND ( x_status = 'PMT PLAN EXISTS' )
1099 THEN
1100
1101 -- At this point, credit type is already validated in Validate_PmtPlan
1102 -- Get the credit type id for the given credit type name
1103 OPEN get_credit_type_id;
1104 FETCH get_credit_type_id INTO l_credit_type_id;
1105
1106 -- At this point, Pay interval type name is already validate in Validate_pmtPlan method
1107 -- Get the Pay interval type id for the given pay interval type name
1108 IF p_pmtplan_rec.pay_interval_type_name IS NOT NULL
1109 THEN
1110 OPEN get_pay_interval_type_id;
1111 FETCH get_pay_interval_type_id INTO l_pay_interval_type_id;
1112 else
1113 l_pay_interval_type_id := -1000;
1114 END IF;
1115
1119 THEN
1116 l_pay_against_commission:= p_pmtplan_rec.pay_against_commission ;
1117
1118 IF p_pmtplan_rec.recoverable_interval_type IS NOT NULL
1120 OPEN get_rec_interval_type_id (p_pmtplan_rec.recoverable_interval_type) ;
1121 FETCH get_rec_interval_type_id INTO l_recoverable_interval_type_id;
1122 CLOSE get_rec_interval_type_id;
1123 END IF;
1124
1125 if l_recoverable_interval_type_id is NULL OR
1126 l_recoverable_interval_type_id = -1000 THEN
1127
1128 if l_pay_interval_type_id = -1000 THEN
1129
1130 l_recoverable_interval_type_id := -1000;
1131 l_pay_against_commission := p_pmtplan_rec.pay_against_commission;
1132
1133 elsif l_pay_interval_type_id <> -1000 THEN
1134
1135 --l_recoverable_interval_type_id := l_pay_interval_type_id;
1136 /**Added by sjustina**/
1137 if( l_recoverable_interval_type_id = -1000 and
1138 l_recoverable_interval_type_id <> l_Pay_interval_type_id and
1139 l_pay_interval_type_id = -1002 ) THEN
1140
1141 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
1142 THEN
1143 fnd_message.set_name('CN', 'CN_INVALID_PAY_INT_AND_REC_INT');
1144 fnd_msg_pub.add;
1145 END IF;
1146
1147 x_status := 'CN_INV_PAY_INT_AND_REC';
1148 x_loading_status := 'CN_INV_PAY_INT_AND_REC';
1149 RAISE FND_API.G_EXC_ERROR;
1150
1151 elsif( l_recoverable_interval_type_id = -1000 and
1152 l_recoverable_interval_type_id <> l_Pay_interval_type_id and
1153 l_pay_interval_type_id = -1001 ) THEN
1154
1155 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
1156 THEN
1157 fnd_message.set_name('CN', 'CN_INVALID_PAY_INT_AND_REC_INT');
1158 fnd_msg_pub.add;
1159 END IF;
1160
1161 x_status := 'CN_INV_PAY_INT_AND_REC';
1162 x_loading_status := 'CN_INV_PAY_INT_AND_REC';
1163 RAISE FND_API.G_EXC_ERROR;
1164 end if;
1165 /**End of code Added by sjustina**/
1166 l_pay_against_commission := p_pmtplan_rec.pay_against_commission;
1167
1168 end if;
1169
1170 else
1171
1172 if ( l_recoverable_interval_type_id = -1001 and
1173 l_recoverable_interval_type_id <> l_Pay_interval_type_id and
1174 l_pay_interval_type_id = -1002 ) THEN
1175
1176 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
1177 THEN
1178 fnd_message.set_name('CN', 'CN_INVALID_PAY_INT_AND_REC_INT');
1179 fnd_msg_pub.add;
1180 END IF;
1181
1182 x_status := 'CN_INV_PAY_INT_AND_REC';
1183 x_loading_status := 'CCN_INV_PAY_INT_AND_REC';
1184 RAISE FND_API.G_EXC_ERROR;
1185
1186 end if;
1187
1188 end if;
1189
1190 IF l_recoverable_interval_type_id IN ( -1001, -1002 ) AND
1191 l_recoverable_interval_type_id <> l_Pay_interval_type_id and
1192 nvl(p_pmtplan_rec.pay_against_commission,'Y') <> 'N' THEN
1193 --Error condition
1194 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
1195 THEN
1196 fnd_message.set_name('CN', 'CN_INVALID_REC_AND_PAC');
1197 fnd_msg_pub.add;
1198 END IF;
1199
1200 x_loading_status := 'CN_INVALID_REC_AND_PAC';
1201 RAISE FND_API.G_EXC_ERROR;
1202 END IF;
1203
1204 Cn_Pmt_Plans_Pkg.Begin_Record(
1205 x_operation => 'UPDATE',
1206 x_rowid => L_ROWID,
1207 x_org_id => p_PmtPlan_rec.org_id,
1208 x_pmt_plan_id => l_pmt_plan_id,
1209 x_name => p_PmtPlan_rec.name,
1210 x_minimum_amount => p_PmtPlan_rec.minimum_amount,
1211 x_maximum_amount => p_PmtPlan_rec.maximum_amount,
1212 x_min_rec_flag => p_PmtPlan_rec.min_rec_flag,
1213 x_max_rec_flag => p_PmtPlan_rec.max_rec_flag,
1214 x_max_recovery_amount => p_PmtPlan_rec.max_recovery_amount,
1215 x_credit_type_id => l_credit_type_id,
1216 x_pay_interval_type_id => l_pay_interval_type_id,
1217 x_start_date => p_PmtPlan_rec.start_date,
1218 x_end_date => p_PmtPlan_rec.end_date,
1219 x_object_version_number => p_PmtPlan_rec.object_version_number,
1220 x_recoverable_interval_type_id => l_recoverable_interval_type_id,
1221 x_pay_against_commission => l_pay_against_commission,
1222 x_attribute_category => p_PmtPlan_rec.attribute_category,
1223 x_attribute1 => p_PmtPlan_rec.attribute1,
1224 x_attribute2 => p_PmtPlan_rec.attribute2,
1225 x_attribute3 => p_PmtPlan_rec.attribute3,
1226 x_attribute4 => p_PmtPlan_rec.attribute4,
1227 x_attribute5 => p_PmtPlan_rec.attribute5,
1228 x_attribute6 => p_PmtPlan_rec.attribute6,
1229 x_attribute7 => p_PmtPlan_rec.attribute7,
1230 x_attribute8 => p_PmtPlan_rec.attribute8,
1231 x_attribute9 => p_PmtPlan_rec.attribute9,
1232 x_attribute10 => p_PmtPlan_rec.attribute10,
1233 x_attribute11 => p_PmtPlan_rec.attribute10,
1234 x_attribute12 => p_PmtPlan_rec.attribute12,
1235 x_attribute13 => p_PmtPlan_rec.attribute13,
1236 x_attribute14 => p_PmtPlan_rec.attribute14,
1237 x_attribute15 => p_PmtPlan_rec.attribute15,
1238 x_last_update_date => l_last_update_date,
1239 x_last_updated_by => l_last_updated_by,
1240 x_creation_date => l_creation_date,
1241 x_created_by => l_created_by,
1242 x_last_update_login => l_last_update_login,
1243 x_program_type => l_program_type,
1244 x_payment_group_code => p_PmtPlan_rec.payment_group_code
1245 );
1246 x_loading_status := 'CN_UPDATED';
1247 END IF;
1248
1249 -- End of API body.
1250 -- Standard check of p_commit.
1251 --
1252 IF FND_API.To_Boolean( p_commit ) THEN
1253 COMMIT WORK;
1254 END IF;
1255
1256 --
1257 -- Standard call to get message count and if count is 1, get message info.
1258 --
1259 FND_MSG_PUB.Count_And_Get
1260 (
1261 p_count => x_msg_count ,
1262 p_data => x_msg_data ,
1263 p_encoded => FND_API.G_FALSE
1264 );
1265 EXCEPTION
1266 WHEN FND_API.G_EXC_ERROR THEN
1267 ROLLBACK TO Update_PmtPlan;
1268 x_return_status := FND_API.G_RET_STS_ERROR ;
1269 FND_MSG_PUB.Count_And_Get
1270 (
1271 p_count => x_msg_count ,
1272 p_data => x_msg_data ,
1273 p_encoded => FND_API.G_FALSE
1274 );
1275 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1276 ROLLBACK TO Update_PmtPlan;
1277
1278 x_loading_status := 'UNEXPECTED_ERR';
1279 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1280 FND_MSG_PUB.Count_And_Get
1281 (
1282 p_count => x_msg_count ,
1283 p_data => x_msg_data ,
1284 p_encoded => FND_API.G_FALSE
1285 );
1286 WHEN OTHERS THEN
1287 ROLLBACK TO Update_PmtPlan;
1288 x_loading_status := 'UNEXPECTED_ERR';
1289 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1290 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1291 THEN
1292 FND_MSG_PUB.Add_Exc_Msg( L_PKG_NAME ,l_api_name );
1293 END IF;
1294 FND_MSG_PUB.Count_And_Get
1295 (
1296 p_count => x_msg_count ,
1297 p_data => x_msg_data ,
1298 p_encoded => FND_API.G_FALSE
1299 );
1300 END Update_PmtPlan;
1301
1302 --
1303 -- Procedure Name : Delete Pmt Plans
1304 --
1305 --
1306 PROCEDURE Delete_PmtPlan
1307 ( p_api_version IN NUMBER,
1308 p_init_msg_list IN VARCHAR2,
1309 p_commit IN VARCHAR2,
1310 p_validation_level IN NUMBER,
1311 x_return_status OUT NOCOPY VARCHAR2,
1312 x_msg_count OUT NOCOPY NUMBER,
1313 x_msg_data OUT NOCOPY VARCHAR2,
1314 p_PmtPlan_rec IN PmtPlan_rec_type ,
1315 x_status OUT NOCOPY VARCHAR2,
1316 x_loading_status OUT NOCOPY VARCHAR2
1317 ) IS
1318
1319 l_api_name CONSTANT VARCHAR2(30)
1320 := 'Delete_PmtPlan';
1321 l_api_version CONSTANT NUMBER := 1.0;
1322 l_pmt_plan_id NUMBER;
1323 l_count NUMBER;
1324 l_role_count NUMBER;
1325
1326 L_PKG_NAME CONSTANT VARCHAR2(30) := 'CN_PmtPlan_PVT';
1327 L_LAST_UPDATE_DATE DATE := sysdate;
1328 L_LAST_UPDATED_BY NUMBER := fnd_global.user_id;
1329 L_CREATION_DATE DATE := sysdate;
1330 L_CREATED_BY NUMBER := fnd_global.user_id;
1331 L_LAST_UPDATE_LOGIN NUMBER := fnd_global.login_id;
1332 L_ROWID VARCHAR2(30);
1333 L_PROGRAM_TYPE VARCHAR2(30);
1334 l_object_version_number NUMBER;
1335
1336 BEGIN
1337 --
1338 -- Standard Start of API savepoint
1339 --
1340 SAVEPOINT Delete_PmtPlan ;
1341 --
1342 -- Standard call to check for call compatibility.
1343 --
1344 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
1345 p_api_version ,
1346 l_api_name ,
1347 L_PKG_NAME )
1348 THEN
1349 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1350 END IF;
1351 --
1352 -- Initialize message list if p_init_msg_list is set to TRUE.
1353 --
1354 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1358 -- Initialize API return status to success
1355 FND_MSG_PUB.initialize;
1356 END IF;
1357 --
1359 --
1360 x_return_status := FND_API.G_RET_STS_SUCCESS;
1361 x_loading_status := 'CN_DELETED';
1362 --
1363 -- API Body
1364 --
1365
1366 --
1367 --Initialize g_mode
1368 --
1369 g_mode := 'DELETE';
1370
1371 get_PmtPlan_id(
1372 x_return_status => x_return_status,
1373 x_msg_count => x_msg_count,
1374 x_msg_data => x_msg_data,
1375 p_PmtPlan_rec => p_PmtPlan_rec,
1376 p_loading_status => x_loading_status,
1377 x_pmt_plan_id => l_pmt_plan_id,
1378 x_loading_status => x_loading_status,
1379 x_status => x_status
1380 );
1381
1382 IF ( x_return_status <> FND_API.G_RET_STS_SUCCESS )
1383 THEN
1384
1385 RAISE fnd_api.g_exc_error;
1386
1387 ELSIF x_status <> 'PMT PLAN EXISTS'
1388 THEN
1389
1390 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1391 THEN
1392 FND_MESSAGE.SET_NAME ('CN' , 'CN_INVALID_PMT_PLAN');
1393 fnd_message.set_token('PMT_PLAN_NAME', p_PmtPlan_rec.name);
1394 FND_MSG_PUB.Add;
1395 END IF;
1396
1397 x_loading_status := 'CN_INVALID_PMT_PLAN';
1398 RAISE FND_API.G_EXC_ERROR ;
1399
1400 END IF;
1401
1402 -- Payment plan cannot be deleted if there are salesreps assiged to the payment plan
1403 SELECT COUNT(1)
1404 INTO l_count
1405 FROM cn_srp_pmt_plans
1406 WHERE pmt_plan_id = l_pmt_plan_id;
1407 IF l_count <> 0
1408 THEN
1409 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
1410 THEN
1411 fnd_message.set_name('CN', 'CN_DELETE_NA');
1412 fnd_msg_pub.add;
1413 END IF;
1414
1415 x_loading_status := 'CN_DELETE_NA';
1416 RAISE FND_API.G_EXC_ERROR;
1417 END IF;
1418
1419 -- Payment plan cannot be deleted if there are roles assiged to the payment plan
1420 SELECT COUNT(1)
1421 INTO l_count
1422 FROM cn_role_pmt_plans
1423 WHERE pmt_plan_id = l_pmt_plan_id;
1424 IF l_count <> 0
1425 THEN
1426 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
1427 THEN
1428 fnd_message.set_name('CN', 'CN_DELETE_NA');
1429 fnd_msg_pub.add;
1430 END IF;
1431 x_loading_status := 'CN_DELETE_NA';
1432 RAISE FND_API.G_EXC_ERROR;
1433 END IF;
1434
1435 cn_pmt_plans_pkg.begin_record
1436 (
1437 x_operation => 'DELETE',
1438 x_rowid => L_ROWID,
1439 x_org_id => p_PmtPlan_rec.org_id,
1440 x_pmt_plan_id => l_pmt_plan_id,
1441 x_name => null,
1442 x_minimum_amount => null,
1443 x_maximum_amount => null,
1444 x_min_rec_flag => null,
1445 x_max_rec_flag => null,
1446 x_max_recovery_amount => null,
1447 x_credit_type_id => null,
1448 x_pay_interval_type_id => null,
1449 x_start_date => null,
1450 x_end_date => null,
1451 x_object_version_number => l_object_version_number,
1452 x_recoverable_interval_type_id => null,
1453 x_pay_against_commission => null,
1454 x_attribute_category => null,
1455 x_attribute1 => null,
1456 x_attribute2 => null,
1457 x_attribute3 => null,
1458 x_attribute4 => null,
1459 x_attribute5 => null,
1460 x_attribute6 => null,
1461 x_attribute7 => null,
1462 x_attribute8 => null,
1463 x_attribute9 => null,
1464 x_attribute10 => null,
1465 x_attribute11 => null,
1466 x_attribute12 => null,
1467 x_attribute13 => null,
1468 x_attribute14 => null,
1469 x_attribute15 => null,
1470 x_last_update_date => null,
1471 x_last_updated_by => l_last_updated_by,
1472 x_creation_date => l_creation_date,
1473 x_created_by => l_created_by,
1474 x_last_update_login => l_last_update_login,
1475 x_program_type => l_program_type,
1476 x_payment_group_code => p_PmtPlan_rec.payment_group_code
1477 );
1478 x_loading_status := 'CN_DELETED';
1479
1480 -- End of API body.
1481 -- Standard check of p_commit.
1482
1483 IF FND_API.To_Boolean( p_commit ) THEN
1484 COMMIT WORK;
1485 END IF;
1486
1487 --
1488 -- Standard call to get message count and if count is 1, get message info.
1489 --
1490 FND_MSG_PUB.Count_And_Get
1491 (
1492 p_count => x_msg_count ,
1493 p_data => x_msg_data ,
1494 p_encoded => FND_API.G_FALSE
1495 );
1496 EXCEPTION
1497 WHEN FND_API.G_EXC_ERROR THEN
1498 ROLLBACK TO Delete_PmtPlan;
1499 x_return_status := FND_API.G_RET_STS_ERROR ;
1500 FND_MSG_PUB.Count_And_Get
1501 (
1502 p_count => x_msg_count ,
1503 p_data => x_msg_data ,
1504 p_encoded => FND_API.G_FALSE
1505 );
1506 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1507 ROLLBACK TO Delete_PmtPlan;
1508 x_loading_status := 'UNEXPECTED_ERR';
1509 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1510 FND_MSG_PUB.Count_And_Get
1511 (
1512 p_count => x_msg_count ,
1513 p_data => x_msg_data ,
1514 p_encoded => FND_API.G_FALSE
1515 );
1516 WHEN OTHERS THEN
1517 ROLLBACK TO Delete_PmtPlan;
1518 x_loading_status := 'UNEXPECTED_ERR';
1519 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1520 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1521 THEN
1522 FND_MSG_PUB.Add_Exc_Msg( L_PKG_NAME ,l_api_name );
1523 END IF;
1524 FND_MSG_PUB.Count_And_Get
1525 (
1526 p_count => x_msg_count ,
1527 p_data => x_msg_data ,
1528 p_encoded => FND_API.G_FALSE
1529 );
1530
1531 END Delete_PmtPlan;
1532
1533 END CN_PmtPlan_PVT ;