[Home] [Help]
PACKAGE BODY: APPS.CN_ROLE_PMT_PLANS_PVT
Source
1 PACKAGE BODY CN_ROLE_PMT_PLANS_PVT AS
2 /* $Header: cnprptpb.pls 120.13 2006/08/23 10:29:14 sjustina noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CN_ROLE_PMT_PLANS_PVT';
5 G_FILE_NAME CONSTANT VARCHAR2(12) := 'cnprptpb.pls';
6 G_LAST_UPDATE_DATE DATE := sysdate;
7 G_LAST_UPDATED_BY NUMBER := fnd_global.user_id;
8 G_CREATION_DATE DATE := sysdate;
9 G_CREATED_BY NUMBER := fnd_global.user_id;
10 G_LAST_UPDATE_LOGIN NUMBER := fnd_global.login_id;
11 G_MISS_JOB_TITLE NUMBER := -99;
12
13 G_ROWID VARCHAR2(15);
14 G_PROGRAM_TYPE VARCHAR2(30);
15
16 -- ----------------------------------------------------------------------------*
17 -- Function : valid_role_name
18 -- Desc : check if the role_name exists in cn_roles
19 -- ---------------------------------------------------------------------------*
20 FUNCTION valid_role_name
21 (
22 p_role_name cn_roles.name%TYPE
23 ) RETURN BOOLEAN IS
24
25 CURSOR l_cur(l_role_name cn_roles.name%TYPE) IS
26 SELECT *
27 FROM cn_roles
28 WHERE name = l_role_name;
29
30 l_rec l_cur%ROWTYPE;
31
32 BEGIN
33
34 OPEN l_cur(p_role_name);
35 FETCH l_cur INTO l_rec;
36 IF (l_cur%notfound) THEN
37 CLOSE l_cur;
38 RETURN FALSE;
39 ELSE
40 CLOSE l_cur;
41 RETURN TRUE;
42 END IF;
43
44 END valid_role_name;
45
46 -- ----------------------------------------------------------------------------*
47 -- Function : valid_pmt_plan_id
48 -- Desc : check if the pmt_plan_id exists in cn_pmt_plans
49 -- ---------------------------------------------------------------------------*
50 FUNCTION valid_pmt_plan_id
51 (
52 p_pmt_plan_id cn_pmt_plans.pmt_plan_id%TYPE,
53 p_org_id cn_pmt_plans.org_id%TYPE
54 ) RETURN BOOLEAN IS
55
56 CURSOR l_cur(l_pmt_plan_id cn_pmt_plans.pmt_plan_id%TYPE, l_org_id cn_pmt_plans.org_id%TYPE) IS
57 SELECT *
58 FROM cn_pmt_plans
59 WHERE pmt_plan_id = l_pmt_plan_id
60 AND org_id = l_org_id;
61
62 l_rec l_cur%ROWTYPE;
63
64 BEGIN
65
66 OPEN l_cur(p_pmt_plan_id, p_org_id);
67 FETCH l_cur INTO l_rec;
68 IF (l_cur%notfound) THEN
69 CLOSE l_cur;
70 RETURN FALSE;
71 ELSE
72 CLOSE l_cur;
73 RETURN TRUE;
74 END IF;
75
76 END valid_pmt_plan_id;
77
78
79 -- ----------------------------------------------------------------------------*
80 -- Function: date_range_within
81 -- Desc : check if one date range has an intersection with
82 -- another date range.
83 -- private function added by Julia Huang for bug 3135619
84 -- ----------------------------------------------------------------------------*
85 FUNCTION date_range_within
86 (
87 a_start_date DATE,
88 a_end_date DATE,
89 b_start_date DATE,
90 b_end_date DATE
91 ) RETURN BOOLEAN IS
92 BEGIN
93 IF ( a_start_date <= NVL( b_end_date, a_start_date)
94 AND b_start_date <= NVL(a_end_date, b_start_date) )
95 THEN
96 RETURN TRUE;
97 ELSE
98 RETURN FALSE;
99 END IF;
100
101 END date_range_within;
102
103
104 -- ----------------------------------------------------------------------------*
105 -- Procedure: check_valid_insert
106 -- Desc : check if the record is valid to insert into cn_role_pmt_plans
107 -- called in create_role_pmt_plan before inserting a role-pmtplan
108 -- assignment
109 -- ----------------------------------------------------------------------------*
110 PROCEDURE check_valid_insert
111 (
112 x_return_status OUT NOCOPY VARCHAR2,
113 x_msg_count OUT NOCOPY NUMBER,
114 x_msg_data OUT NOCOPY VARCHAR2,
115 p_role_pmt_plan_rec IN role_pmt_plan_rec_type,
116 x_role_id OUT NOCOPY cn_roles.role_id%TYPE,
117 x_pmt_plan_id OUT NOCOPY cn_pmt_plans.pmt_plan_id%TYPE,
118 p_loading_status IN VARCHAR2,
119 x_loading_status OUT NOCOPY VARCHAR2
120 ) IS
121
122 l_api_name CONSTANT VARCHAR2(30) := 'check_valid_insert';
123
124 CURSOR l_cur(l_role_id cn_roles.role_id%TYPE,l_org_id cn_pmt_plans.org_id%TYPE) IS
125 SELECT start_date, end_date, pmt_plan_id
126 FROM cn_role_pmt_plans
127 WHERE role_id = l_role_id
128 AND org_id = l_org_id;
129
130 CURSOR l_pp_cur(l_pmt_plan_id cn_pmt_plans.pmt_plan_id%TYPE, l_org_id cn_pmt_plans.org_id%TYPE) IS
131 SELECT start_date, end_date
132 FROM cn_pmt_plans
133 WHERE pmt_plan_id = l_pmt_plan_id
134 AND org_id = l_org_id;
135
136 l_pp_rec l_pp_cur%ROWTYPE;
137 l_pp_payment_group_code cn_pmt_plans.payment_group_code%TYPE;
138 l_payment_group_code cn_pmt_plans.payment_group_code%TYPE;
139
140 BEGIN
141
142 -- Initialize API return status to success
143 x_return_status := FND_API.G_RET_STS_SUCCESS;
144 x_loading_status := p_loading_status;
145
146 -- Start of API body
147
148 -- validate the following issues
149
150 -- role_name can not be missing or null
151 IF (cn_api.chk_miss_null_char_para
152 (p_char_para => p_role_pmt_plan_rec.role_name,
153 p_obj_name => G_ROLE_NAME,
154 p_loading_status => x_loading_status,
155 x_loading_status => x_loading_status) = FND_API.G_TRUE) THEN
156 RAISE FND_API.G_EXC_ERROR ;
157 END IF;
158
159 -- pmt_plan_name can not be missing or null
160 IF (cn_api.chk_miss_null_char_para
161 (p_char_para => p_role_pmt_plan_rec.pmt_plan_name,
162 p_obj_name => G_PP_NAME,
163 p_loading_status => x_loading_status,
164 x_loading_status => x_loading_status) = FND_API.G_TRUE) THEN
165 RAISE FND_API.G_EXC_ERROR ;
166 END IF;
167
168 -- start_date can not be null
169 -- start_date can not be missing
170 -- start_date < end_date if end_date is null
171 IF ( (cn_api.invalid_date_range
172 (p_start_date => p_role_pmt_plan_rec.start_date,
173 p_end_date => p_role_pmt_plan_rec.end_date,
174 p_end_date_nullable => FND_API.G_TRUE,
175 p_loading_status => x_loading_status,
176 x_loading_status => x_loading_status)) = FND_API.G_TRUE ) THEN
177 RAISE FND_API.G_EXC_ERROR ;
178 END IF;
179
180 -- role_name must exist in cn_roles
181 IF NOT valid_role_name(p_role_pmt_plan_rec.role_name) THEN
182 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
183 fnd_message.set_name('CN', 'CN_RL_ASGN_ROLE_NOT_EXIST');
184 FND_MESSAGE.SET_TOKEN('ROLE_NAME',p_role_pmt_plan_rec.role_name);
185 fnd_msg_pub.ADD;
186 END IF;
187 x_loading_status := 'CN_RL_ASGN_ROLE_NOT_EXIST';
188 RAISE fnd_api.g_exc_error;
189 ELSE
190 x_role_id := cn_api.get_role_id(p_role_pmt_plan_rec.role_name);
191 END IF;
192
193 -- pmt_plan_id must exist in cn_pmt_plans
194 IF NOT valid_pmt_plan_id(p_role_pmt_plan_rec.pmt_plan_id, p_role_pmt_plan_rec.org_id) THEN
195 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
196 fnd_message.set_name('CN', 'CN_RL_ASGN_PP_NOT_EXIST');
197 fnd_message.set_token('PMT_PLAN',p_role_pmt_plan_rec.pmt_plan_name);
198 fnd_msg_pub.ADD;
199 END IF;
200 x_loading_status := 'CN_RL_ASGN_PP_NOT_EXIST';
201 RAISE fnd_api.g_exc_error;
202 ELSE
203 --x_pmt_plan_id := cn_api.get_pp_id(p_role_pmt_plan_rec.pmt_plan_name,p_role_pmt_plan_rec.org_id);
204 x_pmt_plan_id := p_role_pmt_plan_rec.pmt_plan_id;
205 END IF;
206
207 -- (start_date, end_date) is within comp plan's (start_date, end_date)
208 OPEN l_pp_cur(p_role_pmt_plan_rec.pmt_plan_id,p_role_pmt_plan_rec.org_id);
209 FETCH l_pp_cur INTO l_pp_rec;
210
211 IF (l_pp_cur%notfound) THEN
212 -- normally this won't happen as it has been valided previously
213 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
214 fnd_message.set_name ('CN', 'CN_RL_ASGN_PP_NOT_EXIST');
215 fnd_message.set_token('PMT_PLAN',p_role_pmt_plan_rec.pmt_plan_name);
216 fnd_msg_pub.ADD;
217 END IF;
218 x_loading_status := 'CN_RL_ASGN_PP_NOT_EXIST';
219 CLOSE l_pp_cur;
220 RAISE fnd_api.g_exc_error;
221 ELSE
222 --Commented out by Julia Huang for bug 3135619.
223 --IF NOT cn_api.date_range_within(p_role_pmt_plan_rec.start_date,
224 IF NOT date_range_within(p_role_pmt_plan_rec.start_date,
225 p_role_pmt_plan_rec.end_date,
226 l_pp_rec.start_date,
227 l_pp_rec.end_date) THEN
228 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
229 fnd_message.set_name ('CN', 'CN_RL_PP_DATE_RANGE_NOT_WITHIN');
230 FND_MESSAGE.SET_TOKEN('START_DATE',p_role_pmt_plan_rec.start_date);
231 FND_MESSAGE.SET_TOKEN('END_DATE',p_role_pmt_plan_rec.end_date);
232 FND_MESSAGE.SET_TOKEN('PP_START_DATE',l_pp_rec.start_date);
233 FND_MESSAGE.SET_TOKEN('PP_END_DATE',l_pp_rec.end_date);
234 FND_MESSAGE.SET_TOKEN('PMT_PLAN_NAME',p_role_pmt_plan_rec.pmt_plan_name);
235 fnd_msg_pub.ADD;
236 END IF;
237 x_loading_status := 'CN_RL_PP_DATE_RANGE_NOT_WITHIN';
238 CLOSE l_pp_cur;
239 RAISE fnd_api.g_exc_error;
240 END IF;
241
242 --bug 3560026 by Julia Huang on 4/7/04 -begin
243 IF ( p_role_pmt_plan_rec.start_date < l_pp_rec.start_date )
244 THEN
245 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)
246 THEN
247 fnd_message.set_name ('CN', 'CN_RL_PP_SD_LESS_THAN_PP_SD');
248 FND_MESSAGE.SET_TOKEN('START_DATE',p_role_pmt_plan_rec.start_date);
249 FND_MESSAGE.SET_TOKEN('END_DATE',p_role_pmt_plan_rec.end_date);
250 FND_MESSAGE.SET_TOKEN('PP_START_DATE',l_pp_rec.start_date);
251 FND_MESSAGE.SET_TOKEN('PP_END_DATE',l_pp_rec.end_date);
252 FND_MESSAGE.SET_TOKEN('PMT_PLAN_NAME',p_role_pmt_plan_rec.pmt_plan_name);
253 fnd_msg_pub.ADD;
254 END IF;
255
256 x_loading_status := 'CN_RL_PP_SD_LESS_THAN_PP_SD';
257 CLOSE l_pp_cur;
258 RAISE fnd_api.g_exc_error;
259 END IF;
260 --bug 3560026 by Julia Huang on 4/7/04 -end
261
262 CLOSE l_pp_cur;
263 END IF;
264
265 -- If existing any same role_id in cn_role_pmt_plans THEN
266 -- check no overlap and no gap
267 FOR l_rec IN l_cur(x_role_id,p_role_pmt_plan_rec.org_id)
268 LOOP
269
270 select payment_group_code into
271 l_payment_group_code
272 from cn_pmt_plans
273 where pmt_plan_id = l_rec.pmt_plan_id;
274
275 select payment_group_code into
276 l_pp_payment_group_code
277 from cn_pmt_plans
278 where pmt_plan_id = p_role_pmt_plan_rec.pmt_plan_id
279 and org_id = p_role_pmt_plan_rec.org_id;
280
281 IF ((cn_api.date_range_overlap(l_rec.start_date,
282 l_rec.end_date,
283 p_role_pmt_plan_rec.start_date,
284 p_role_pmt_plan_rec.end_date))
285 AND
286
287 (l_pp_payment_group_code = l_payment_group_code))
288
289 THEN
290
291 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
292 fnd_message.set_name ('CN', 'CN_RL_ROLE_PMT_PLAN_OVERLAP');
293 FND_MESSAGE.SET_TOKEN('PMT_PLAN_NAME',cn_api.get_pp_name(l_rec.pmt_plan_id));
294 fnd_message.set_token('START_DATE',l_rec.start_date);
295 fnd_message.set_token('END_DATE',l_rec.end_date);
296 fnd_msg_pub.ADD;
297 END IF;
298 x_loading_status := 'CN_RL_ROLE_PMT_PLAN_OVERLAP';
299 RAISE fnd_api.g_exc_error;
300 END IF;
301 END LOOP;
302
303 -- End of API body.
304
305 -- Standard call to get message count and if count is 1, get message info.
306 FND_MSG_PUB.Count_And_Get
307 (
308 p_count => x_msg_count ,
309 p_data => x_msg_data ,
310 p_encoded => FND_API.G_FALSE
311 );
312
313 EXCEPTION
314 WHEN FND_API.G_EXC_ERROR THEN
315 x_return_status := FND_API.G_RET_STS_ERROR ;
316 FND_MSG_PUB.Count_And_Get
317 (
318 p_count => x_msg_count ,
319 p_data => x_msg_data ,
320 p_encoded => FND_API.G_FALSE
321 );
322 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
323 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
324 x_loading_status := 'UNEXPECTED_ERR';
325 FND_MSG_PUB.Count_And_Get
326 (
327 p_count => x_msg_count ,
328 p_data => x_msg_data ,
329 p_encoded => FND_API.G_FALSE
330 );
331 WHEN OTHERS THEN
332 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
333 x_loading_status := 'UNEXPECTED_ERR';
334 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
335 THEN
336 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
337 END IF;
338 FND_MSG_PUB.Count_And_Get
339 (
340 p_count => x_msg_count ,
341 p_data => x_msg_data ,
342 p_encoded => FND_API.G_FALSE
343 );
344 END check_valid_insert;
345
346
347 -- ----------------------------------------------------------------------------*
348 -- Procedure: check_valid_update
349 -- Desc : check if the record is valid to update in cn_role_pmt_plans
350 -- called in update_role_pmt_plan before updating a role
351 -- ----------------------------------------------------------------------------*
352 PROCEDURE check_valid_update
353 (
354 x_return_status OUT NOCOPY VARCHAR2,
355 x_msg_count OUT NOCOPY NUMBER,
356 x_msg_data OUT NOCOPY VARCHAR2,
357 p_role_pmt_plan_rec_old IN role_pmt_plan_rec_type,
358 p_role_pmt_plan_rec_new IN role_pmt_plan_rec_type,
359 x_role_pmt_plan_id_old OUT NOCOPY cn_role_pmt_plans.role_pmt_plan_id%TYPE,
360 x_role_id OUT NOCOPY cn_roles.role_id%TYPE,
361 x_pmt_plan_id OUT NOCOPY cn_pmt_plans.pmt_plan_id%TYPE,
362 x_date_update_only OUT NOCOPY VARCHAR2,
363 p_loading_status IN VARCHAR2,
364 x_loading_status OUT NOCOPY VARCHAR2
365 ) IS
366
367 l_api_name CONSTANT VARCHAR2(30) := 'check_valid_update';
368 tmp_start_date cn_role_pmt_plans.start_date%TYPE;
369 tmp_end_date cn_role_pmt_plans.end_date%TYPE;
370
371 CURSOR l_cur(l_role_id cn_role_plans.role_id%TYPE,
372 l_role_pmt_plan_id cn_role_pmt_plans.role_pmt_plan_id%TYPE) IS
373 SELECT start_date, end_date, pmt_plan_id
374 FROM cn_role_pmt_plans
375 WHERE role_id = l_role_id AND
376 role_pmt_plan_id <> l_role_pmt_plan_id;
377
378 CURSOR l_old_cur(l_role_pmt_plan_id cn_role_pmt_plans.role_pmt_plan_id%TYPE) IS
379 SELECT *
380 FROM cn_role_pmt_plans
381 WHERE role_pmt_plan_id = l_role_pmt_plan_id;
382
383 l_old_rec l_old_cur%ROWTYPE;
384
385 CURSOR l_pp_cur(l_pmt_plan_id cn_pmt_plans.pmt_plan_id%TYPE) IS
386 SELECT start_date, end_date
387 FROM cn_pmt_plans
388 WHERE pmt_plan_id = l_pmt_plan_id;
389
390 l_pp_rec l_pp_cur%ROWTYPE;
391
392 BEGIN
393
394 -- Initialize API return status to success
395 x_return_status := FND_API.G_RET_STS_SUCCESS;
396 x_loading_status := p_loading_status;
397
398 -- Start of API body
399
400 -- validate the following issues
401
402 -- old role_plan_id must exist in cn_role_plans
403 x_role_pmt_plan_id_old :=
404 cn_api.get_role_pmt_plan_id(p_role_pmt_plan_rec_old.role_name,
405 p_role_pmt_plan_rec_old.pmt_plan_name,
406 p_role_pmt_plan_rec_old.start_date,
407 p_role_pmt_plan_rec_old.end_date,
408 p_role_pmt_plan_rec_old.org_id);
409
410 IF (x_role_pmt_plan_id_old IS NULL) THEN
411 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
412 fnd_message.set_name ('CN', 'CN_RL_UPD_ROLE_PP_NOT_EXIST');
413 fnd_msg_pub.ADD;
414 END IF;
415 x_loading_status := 'CN_RL_UPD_ROLE_PP_NOT_EXIST';
416 RAISE fnd_api.g_exc_error;
417 END IF;
418
419 -- new role_name can not be null
420 -- note that new role_name can be missing
421 IF (cn_api.chk_null_char_para
422 (p_char_para => p_role_pmt_plan_rec_new.role_name,
423 p_obj_name => G_ROLE_NAME,
424 p_loading_status => x_loading_status,
425 x_loading_status => x_loading_status) = FND_API.G_TRUE) THEN
426 RAISE FND_API.G_EXC_ERROR ;
427 END IF;
428
429 -- new pmt_plan_name can not be null
430 -- note that new pmt_plan_name can be missing
431 IF (cn_api.chk_null_char_para
432 (p_char_para => p_role_pmt_plan_rec_new.pmt_plan_name,
433 p_obj_name => G_PP_NAME,
434 p_loading_status => x_loading_status,
435 x_loading_status => x_loading_status) = FND_API.G_TRUE) THEN
436 RAISE FND_API.G_EXC_ERROR ;
437 END IF;
438
439 -- new start_date can not be null
440 -- note that new start_date can be missing
441 IF (cn_api.chk_null_date_para
442 (p_date_para => p_role_pmt_plan_rec_new.start_date,
443 p_obj_name => G_START_DATE,
444 p_loading_status => x_loading_status,
445 x_loading_status => x_loading_status) = FND_API.G_TRUE) THEN
446 RAISE FND_API.G_EXC_ERROR;
447 END IF;
448
449 -- if new start_date is missing then
450 -- tmp_start_date := old start_date
451 -- else
452 -- tmp_start_date := new start_date
453 -- end if
454
455 -- if new end_date is missing then
456 -- tmp_end_date := old end_date
457 -- else
458 -- tmp_end_date := new end_date
459 -- end if
460
461 -- check tmp_start_date < tmp_end_date if tmp_end_date is not null
462
463
464 OPEN l_old_cur(x_role_pmt_plan_id_old);
465 FETCH l_old_cur INTO l_old_rec;
466 IF (l_old_cur%notfound) THEN
467 -- normally, this should not happen as the existance has
468 -- been validated previously
469 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
470 fnd_message.set_name ('CN', 'CN_RL_UPD_ROLE_PP_NOT_EXIST');
471 fnd_msg_pub.ADD;
472 END IF;
473 x_loading_status := 'CN_RL_UPD_ROLE_PP_NOT_EXIST';
474 CLOSE l_old_cur;
475 RAISE fnd_api.g_exc_error;
476 ELSE
477 IF (p_role_pmt_plan_rec_new.start_date = fnd_api.g_miss_date) THEN
478 tmp_start_date := l_old_rec.start_date;
479 ELSE
480 tmp_start_date := p_role_pmt_plan_rec_new.start_date;
481 END IF;
482 IF (p_role_pmt_plan_rec_new.end_date = fnd_api.g_miss_date) THEN
483 tmp_end_date := l_old_rec.end_date;
484 ELSE
485 tmp_end_date := p_role_pmt_plan_rec_new.end_date;
486 END IF;
487 IF (tmp_end_date IS NOT NULL) AND (tmp_start_date > tmp_end_date) THEN
488 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
489 fnd_message.set_name('CN', 'CN_RL_INVALID_DATE_RANGE');
490 fnd_message.set_token('START_DATE',tmp_start_date);
491 fnd_message.set_token('END_DATE',tmp_end_date);
492 fnd_msg_pub.ADD;
493 END IF;
494 x_loading_status := 'CN_RL_INVALID_DATE_RANGE';
495 CLOSE l_old_cur;
496 RAISE fnd_api.g_exc_error;
497 END IF;
498 CLOSE l_old_cur;
499 END IF;
500
501 -- if new role_name is not missing then new role_name must exist in cn_roles
502 IF (p_role_pmt_plan_rec_new.role_name <> fnd_api.g_miss_char) THEN
503 x_role_id := cn_api.get_role_id(p_role_pmt_plan_rec_new.role_name);
504 IF (x_role_id IS NULL) THEN
505 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
506 fnd_message.set_name('CN', 'CN_RL_ASGN_ROLE_NOT_EXIST');
507 fnd_message.set_token('ROLE_NAME',p_role_pmt_plan_rec_new.role_name);
508 fnd_msg_pub.ADD;
509 END IF;
510 x_loading_status := 'CN_RL_ASGN_ROLE_NOT_EXIST';
511 RAISE fnd_api.g_exc_error;
512 END IF;
513 ELSE
514 OPEN l_old_cur(x_role_pmt_plan_id_old);
515 FETCH l_old_cur INTO l_old_rec;
516 IF (l_old_cur%notfound) THEN
517 -- normally, this should not happen as the existance has
518 -- been validated previously
519 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
520 fnd_message.set_name ('CN', 'CN_RL_UPD_ROLE_PP_NOT_EXIST');
521 fnd_msg_pub.ADD;
522 END IF;
523 x_loading_status := 'CN_RL_UPD_ROLE_PP_NOT_EXIST';
524 CLOSE l_old_cur;
525 RAISE fnd_api.g_exc_error;
526 ELSE
527 x_role_id := l_old_rec.role_id;
528 CLOSE l_old_cur;
529 END IF;
530 END IF;
531
532 -- if new pmt_plan_name is not missing then
533 -- new pmt_plan_name must exist in cn_pmt_plans
534 IF (p_role_pmt_plan_rec_new.pmt_plan_name <> fnd_api.g_miss_char) THEN
535 x_pmt_plan_id := cn_api.get_pp_id(p_role_pmt_plan_rec_new.pmt_plan_name,p_role_pmt_plan_rec_new.org_id);
536 IF (x_pmt_plan_id IS NULL) THEN
537 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
538 fnd_message.set_name ('CN', 'CN_RL_ASGN_PP_NOT_EXIST');
539 fnd_message.set_token('PMT_PLAN',p_role_pmt_plan_rec_new.pmt_plan_name);
540 fnd_msg_pub.ADD;
541 END IF;
542 x_loading_status := 'CN_RL_ASGN_PP_NOT_EXIST';
543 RAISE fnd_api.g_exc_error;
544 END IF;
545 ELSE
546 OPEN l_old_cur(x_role_pmt_plan_id_old);
547 FETCH l_old_cur INTO l_old_rec;
548 IF (l_old_cur%notfound) THEN
549 -- normally, this should not happen as the existance has
550 -- been validated previously
551 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
552 fnd_message.set_name ('CN', 'CN_RL_UPD_ROLE_PP_NOT_EXIST');
553 fnd_msg_pub.ADD;
554 END IF;
555 x_loading_status := 'CN_RL_UPD_ROLE_PP_NOT_EXIST';
556 CLOSE l_old_cur;
557 RAISE fnd_api.g_exc_error;
558 ELSE
559 x_pmt_plan_id := l_old_rec.pmt_plan_id;
560 CLOSE l_old_cur;
561 END IF;
562 END IF;
563
564 -- (start_date, end_date) is within pmt plan's (start_date, end_date)
565 OPEN l_pp_cur(x_pmt_plan_id);
566 FETCH l_pp_cur INTO l_pp_rec;
567 IF (l_pp_cur%notfound) THEN
568 -- normally this won't happen as it has been valided previously
569 x_loading_status := 'CN_RL_ASGN_PP_NOT_EXIST';
570 CLOSE l_pp_cur;
571 RAISE fnd_api.g_exc_error;
572 ELSE
573 --Commented out by Julia Huang for bug 3135619.
574 --IF NOT cn_api.date_range_within(tmp_start_date,
575 IF NOT date_range_within(tmp_start_date,
576 tmp_end_date,
577 l_pp_rec.start_date,
578 l_pp_rec.end_date) THEN
579 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
580 fnd_message.set_name ('CN', 'CN_RL_PP_DATE_RANGE_NOT_WITHIN');
581 FND_MESSAGE.SET_TOKEN('START_DATE',tmp_start_date);
582 FND_MESSAGE.SET_TOKEN('END_DATE',tmp_end_date);
583 FND_MESSAGE.SET_TOKEN('PP_START_DATE',l_pp_rec.start_date);
584 FND_MESSAGE.SET_TOKEN('PP_END_DATE',l_pp_rec.end_date);
585 FND_MESSAGE.SET_TOKEN('PMT_PLAN_NAME',cn_api.get_pp_name(x_pmt_plan_id));
586 fnd_msg_pub.ADD;
587 END IF;
588 x_loading_status := 'CN_RL_PP_DATE_RANGE_NOT_WITHIN';
589 CLOSE l_pp_cur;
590 RAISE fnd_api.g_exc_error;
591 END IF;
592
593 --bug 3560026 by Julia Huang on 4/7/04 -begin
594 IF ( tmp_start_date < l_pp_rec.start_date )
595 THEN
596 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error)
597 THEN
598 fnd_message.set_name ('CN', 'CN_RL_PP_SD_LESS_THAN_PP_SD');
599 FND_MESSAGE.SET_TOKEN('START_DATE',tmp_start_date);
600 FND_MESSAGE.SET_TOKEN('END_DATE',tmp_end_date);
601 FND_MESSAGE.SET_TOKEN('PP_START_DATE',l_pp_rec.start_date);
602 FND_MESSAGE.SET_TOKEN('PP_END_DATE',l_pp_rec.end_date);
603 FND_MESSAGE.SET_TOKEN('PMT_PLAN_NAME',cn_api.get_pp_name(x_pmt_plan_id));
604 fnd_msg_pub.ADD;
605 END IF;
606
607 x_loading_status := 'CN_RL_PP_SD_LESS_THAN_PP_SD';
608 CLOSE l_pp_cur;
609 RAISE fnd_api.g_exc_error;
610 END IF;
611 --bug 3560026 by Julia Huang on 4/7/04 -end
612
613 CLOSE l_pp_cur;
614 END IF;
615
616
617 -- If existing any same role_id in cn_role_pmt_plans THEN
618 -- check no overlap
619 FOR l_rec IN l_cur(x_role_id,x_role_pmt_plan_id_old)
620 LOOP
621 IF cn_api.date_range_overlap(l_rec.start_date,
622 l_rec.end_date,
623 tmp_start_date,
624 tmp_end_date) THEN
625 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
626 fnd_message.set_name ('CN', 'CN_RL_ROLE_PMT_PLAN_OVERLAP');
627 FND_MESSAGE.SET_TOKEN('PMT_PLAN_NAME',cn_api.get_pp_name(l_rec.pmt_plan_id));
628 fnd_message.set_token('START_DATE',l_rec.start_date);
629 fnd_message.set_token('END_DATE',l_rec.end_date);
630 fnd_msg_pub.ADD;
631 END IF;
632 x_loading_status := 'CN_RL_ROLE_PMT_PLAN_OVERLAP';
633 RAISE fnd_api.g_exc_error;
634 END IF;
635 END LOOP;
636
637 -- Checking if it is date_update_only
638 OPEN l_old_cur(x_role_pmt_plan_id_old);
639 FETCH l_old_cur INTO l_old_rec;
640 IF (l_old_cur%notfound) THEN
641 -- normally, this should not happen as the existence has
642 -- been validated previously
643 x_loading_status := 'CN_RL_UPD_ROLE_PP_NOT_EXIST';
644 CLOSE l_old_cur;
645 RAISE fnd_api.g_exc_error;
646 ELSE
647 IF ((x_role_id <> l_old_rec.role_id) OR
648 (x_pmt_plan_id <> l_old_rec.pmt_plan_id)) THEN
649 x_date_update_only := FND_API.G_FALSE;
650 ELSE
651 x_date_update_only := FND_API.G_TRUE;
652 END IF;
653 CLOSE l_old_cur;
654 END IF;
655
656 -- End of API body.
657
658 -- Standard call to get message count and if count is 1, get message info.
659 FND_MSG_PUB.Count_And_Get
660 (
661 p_count => x_msg_count ,
662 p_data => x_msg_data ,
663 p_encoded => FND_API.G_FALSE
664 );
665
666 EXCEPTION
667 WHEN FND_API.G_EXC_ERROR THEN
668 x_return_status := FND_API.G_RET_STS_ERROR ;
669 FND_MSG_PUB.Count_And_Get
670 (
671 p_count => x_msg_count ,
672 p_data => x_msg_data ,
673 p_encoded => FND_API.G_FALSE
674 );
675 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
676 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
677 x_loading_status := 'UNEXPECTED_ERR';
678 FND_MSG_PUB.Count_And_Get
679 (
680 p_count => x_msg_count ,
681 p_data => x_msg_data ,
682 p_encoded => FND_API.G_FALSE
683 );
684 WHEN OTHERS THEN
685 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
686 x_loading_status := 'UNEXPECTED_ERR';
687 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
688 THEN
689 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
690 END IF;
691 FND_MSG_PUB.Count_And_Get
692 (
693 p_count => x_msg_count ,
694 p_data => x_msg_data ,
695 p_encoded => FND_API.G_FALSE
696 );
697 END check_valid_update;
698
699
700 -- ----------------------------------------------------------------------------*
701 -- Procedure: check_valid_delete
702 -- Desc : check if the record is valid to delete from cn_role_pmt_plans
703 -- called in delete_role_pmt_plan before deleting a role
704 -- ----------------------------------------------------------------------------*
705 PROCEDURE check_valid_delete
706 (
707 x_return_status OUT NOCOPY VARCHAR2,
708 x_msg_count OUT NOCOPY NUMBER,
709 x_msg_data OUT NOCOPY VARCHAR2,
710 p_role_pmt_plan_rec IN role_pmt_plan_rec_type,
711 x_role_pmt_plan_id OUT NOCOPY NUMBER,
712 p_loading_status IN VARCHAR2,
713 x_loading_status OUT NOCOPY VARCHAR2
714 ) IS
715
716 l_api_name CONSTANT VARCHAR2(30) := 'check_valid_delete';
717
718 BEGIN
719
720 -- Initialize API return status to success
721 x_return_status := FND_API.G_RET_STS_SUCCESS;
722 x_loading_status := p_loading_status;
723
724 -- Start of API body
725
726 -- Valide the following issues
727
728 -- Checke if the p_role_pmt_plan_id does exist.
729
730 x_role_pmt_plan_id := p_role_pmt_plan_rec.role_pmt_plan_id;
731
732 /*x_role_pmt_plan_id := cn_api.get_role_pmt_plan_id(p_role_pmt_plan_rec.role_name,
733 p_role_pmt_plan_rec.pmt_plan_name,
734 p_role_pmt_plan_rec.start_date,
735 p_role_pmt_plan_rec.end_date,
736 p_role_pmt_plan_rec.org_id);*/
737 IF (x_role_pmt_plan_id IS NULL) THEN
738 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
739 FND_MESSAGE.SET_NAME('CN' ,'CN_RL_DEL_ROLE_PP_NOT_EXIST');
740 FND_MSG_PUB.Add;
741 END IF;
742 x_loading_status := 'CN_RL_DEL_ROLE_PP_NOT_EXIST';
743 RAISE FND_API.G_EXC_ERROR ;
744 END IF;
745
746 -- End of API body.
747
748 -- Standard call to get message count and if count is 1, get message info.
749
750 FND_MSG_PUB.Count_And_Get
751 (
752 p_count => x_msg_count ,
753 p_data => x_msg_data ,
754 p_encoded => FND_API.G_FALSE
755 );
756
757 EXCEPTION
758 WHEN FND_API.G_EXC_ERROR THEN
759 x_return_status := FND_API.G_RET_STS_ERROR ;
760 FND_MSG_PUB.Count_And_Get
761 (
762 p_count => x_msg_count ,
763 p_data => x_msg_data ,
764 p_encoded => FND_API.G_FALSE
765 );
766 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
767 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
768 x_loading_status := 'UNEXPECTED_ERR';
769 FND_MSG_PUB.Count_And_Get
770 (
771 p_count => x_msg_count ,
772 p_data => x_msg_data ,
773 p_encoded => FND_API.G_FALSE
774 );
775 WHEN OTHERS THEN
776 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
777 x_loading_status := 'UNEXPECTED_ERR';
778 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
779 THEN
780 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
781 END IF;
782 FND_MSG_PUB.Count_And_Get
783 (
784 p_count => x_msg_count ,
785 p_data => x_msg_data ,
786 p_encoded => FND_API.G_FALSE
787 );
788
789 END check_valid_delete;
790
791
792 -- --------------------------------------------------------------------------*
793 -- Procedure: srp_pmt_plan_asgn_for_insert
794 -- --------------------------------------------------------------------------*
795 PROCEDURE srp_pmt_plan_asgn_for_insert
796 (p_role_id IN cn_roles.role_id%TYPE,
797 p_role_pmt_plan_id IN cn_role_pmt_plans.role_pmt_plan_id%TYPE,
798 p_suppress_flag IN VARCHAR2 := 'N',
799 x_return_status OUT NOCOPY VARCHAR2,
800 p_loading_status IN VARCHAR2,
801 x_loading_status OUT NOCOPY VARCHAR2 ) IS
802
803 /* CURSOR l_cur IS
804 select sr.srp_role_id srp_role_id,
805 nvl(srd.job_title_id, G_MISS_JOB_TITLE) job_title_id,
806 nvl(srd.plan_activate_status, 'NOT_PUSHED') push_status
807 from cn_srp_roles sr,
808 cn_srp_role_dtls srd
809 where role_id = p_role_id
810 and srd.role_model_id is NULL
811 -- CHANGED FOR MODELING IMPACT
812 and sr.srp_role_id = srd.srp_role_id(+);*/
813
814
815 --To exclude 'TBH' category. Modified by Julia Huang on 4/7/2004 for bug 3560026.
816 /*
817 CURSOR l_cur IS
818 select *
819 from cn_srp_roles
820 where role_id = p_role_id
821 ;
822 */
823 CURSOR l_cur (l_org_id cn_pmt_plans.org_id%TYPE) IS
824 select csr.*
825 from cn_srp_roles csr, cn_salesreps cs
826 where csr.role_id = p_role_id
827 and csr.salesrep_id = cs.salesrep_id
828 and csr.org_id = cs.org_id
829 and csr.org_id = l_org_id;
830
831 l_rec l_cur%ROWTYPE;
832
833 l_return_status VARCHAR2(2000);
834 l_msg_count NUMBER;
835 l_msg_data VARCHAR2(2000);
836 l_srp_pmt_plan_id cn_srp_pmt_plans.srp_pmt_plan_id%TYPE;
837 l_org_id cn_srp_pmt_plans.org_id%TYPE;
838 l_loading_status VARCHAR2(2000);
839
840
841 BEGIN
842
843 -- Initialize API return status to success
844 x_return_status := FND_API.G_RET_STS_SUCCESS;
845 x_loading_status := p_loading_status;
846
847 select org_id into l_org_id
848 from cn_role_pmt_plans
849 where role_pmt_plan_id = p_role_pmt_plan_id;
850
851
852 FOR l_rec IN l_cur(l_org_id)
853 LOOP
854
855 -- dbms_output.put_line('insert into cn_srp_pmt_plans...');
856 -- dbms_output.put_line('p_srp_role_id = ' || l_rec.srp_role_id);
857 -- dbms_output.put_line('p_role_pmt_plan_id = ' || p_role_pmt_plan_id);
858
859 CN_SRP_PMT_PLANS_PUB.create_mass_asgn_srp_pmt_plan
860 (p_api_version => 1.0,
861 x_return_status => l_return_status,
862 x_msg_count => l_msg_count,
863 x_msg_data => l_msg_data,
864 p_srp_role_id => l_rec.srp_role_id,
865 p_role_pmt_plan_id => p_role_pmt_plan_id,
866 x_srp_pmt_plan_id => l_srp_pmt_plan_id,
867 x_loading_status => l_loading_status);
868
869 -- Bug 5125998
870 -- After discussing with Fred Mburu on MAY 30 2006
871 -- it was understood that this API is going to be called
872 -- only from the front end.
873 -- Apparently in 11.5.10 even when there was an issue for mass
874 -- assignment of the payment plan, the error rows were silently
875 -- suppressed and valid rows were committed. (Partial commit implementation)
876
877 IF (p_suppress_flag = 'Y') THEN
878 l_msg_count := 0;
879 l_msg_data := '';
880 fnd_msg_pub.initialize;
881 x_return_status := FND_API.G_RET_STS_SUCCESS;
882 ELSE
883 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
884 x_return_status := l_return_status;
885 x_loading_status := l_loading_status;
886 EXIT;
887 END IF;
888 END IF;
889
890 --IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
891 -- x_return_status := l_return_status;
892 -- x_loading_status := l_loading_status;
893 -- EXIT;
894 --END IF;
895 -- end if;
896 END LOOP;
897 END srp_pmt_plan_asgn_for_insert;
898
899
900 -- --------------------------------------------------------------------------*
901 -- Procedure: srp_pmt_pmt_plan_asgn_for_update
902 -- --------------------------------------------------------------------------*
903 PROCEDURE srp_pmt_plan_asgn_for_update
904 (p_role_pmt_plan_id IN cn_role_pmt_plans.role_pmt_plan_id%TYPE,
905 p_role_id IN cn_roles.role_id%TYPE,
906 p_date_update_only IN VARCHAR2,
907 x_return_status OUT NOCOPY VARCHAR2,
908 p_loading_status IN VARCHAR2,
909 x_loading_status OUT NOCOPY VARCHAR2 ) IS
910
911
912 /* CURSOR l_cur IS
913 select sr.srp_role_id srp_role_id,
914 nvl(srd.job_title_id, G_MISS_JOB_TITLE) job_title_id,
915 nvl(srd.plan_activate_status, 'NOT_PUSHED') push_status
916 from cn_srp_roles sr,
917 cn_srp_role_dtls srd
918 where role_id = p_role_id
919 and srd.role_model_id is NULL
920 -- CHANGED FOR MODELING IMPACT
921 and sr.srp_role_id = srd.srp_role_id(+);*/
922
923 CURSOR l_cur (l_org_id cn_role_pmt_plans.org_id%TYPE) IS
924 select srp_role_id
925 from cn_srp_roles
926 where role_id = p_role_id
927 and org_id = l_org_id;
928
929 l_rec l_cur%ROWTYPE;
930
931 l_return_status VARCHAR2(2000);
932 l_msg_count NUMBER;
933 l_msg_data VARCHAR2(2000);
934 l_srp_pmt_plan_assign_id cn_srp_pmt_plans.srp_pmt_plan_id%TYPE;
935 l_org_id cn_srp_pmt_plans.org_id%TYPE;
936 l_loading_status VARCHAR2(2000);
937
938 BEGIN
939
940 -- Initialize API return status to success
941 x_return_status := FND_API.G_RET_STS_SUCCESS;
942 x_loading_status := p_loading_status;
943
944 -- see here if it is necessary to update cn_srp_plan_assigns...
945 -- the create_module here is OSC.
946 -- if the job title not assigned yet (original OSC case) or
947 -- status is PUSHED (salesrep push done, treat as OSC record), then
948 -- call SPA.update
949
950 select org_id into l_org_id
951 from cn_role_pmt_plans
952 where role_pmt_plan_id = p_role_pmt_plan_id;
953
954 FOR l_rec IN l_cur(l_org_id) LOOP
955
956 CN_SRP_PMT_PLANS_PUB.update_mass_asgn_srp_pmt_plan
957 (p_api_version => 1.0,
958 x_return_status => l_return_status,
959 x_msg_count => l_msg_count,
960 x_msg_data => l_msg_data,
961 p_srp_role_id => l_rec.srp_role_id,
962 p_role_pmt_plan_id => p_role_pmt_plan_id,
963 x_loading_status => l_loading_status);
964
965 --IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
966 -- x_return_status := l_return_status;
967 -- x_loading_status := l_loading_status;
968 -- EXIT;
969 --END IF;
970 END LOOP;
971
972 END srp_pmt_plan_asgn_for_update;
973
974
975 -- --------------------------------------------------------------------------*
976 -- Procedure: srp_pmt_plan_asgn_for_delete
977 -- --------------------------------------------------------------------------*
978 PROCEDURE srp_pmt_plan_asgn_for_delete
979 (p_role_id IN cn_roles.role_id%TYPE,
980 p_role_pmt_plan_id IN cn_role_pmt_plans.role_pmt_plan_id%TYPE,
981 p_suppress_flag IN VARCHAR2 := 'N',
982 x_return_status OUT NOCOPY VARCHAR2,
983 p_loading_status IN VARCHAR2,
984 x_loading_status OUT NOCOPY VARCHAR2 ) IS
985
986 CURSOR l_cur (l_org_id cn_role_pmt_plans.org_id%TYPE) IS
987 SELECT srp_role_id
988 FROM cn_srp_roles
989 WHERE role_id = p_role_id
990 AND org_id = l_org_id;
991
992 l_rec l_cur%ROWTYPE;
993
994 l_return_status VARCHAR2(2000);
995 l_msg_count NUMBER;
996 l_msg_data VARCHAR2(2000);
997 l_srp_pmt_plan_id cn_srp_pmt_plans.srp_pmt_plan_id%TYPE;
998 l_org_id cn_srp_pmt_plans.org_id%TYPE;
999 l_loading_status VARCHAR2(2000);
1000
1001 BEGIN
1002
1003 -- Initialize API return status to success
1004 x_return_status := FND_API.G_RET_STS_SUCCESS;
1005 x_loading_status := p_loading_status;
1006
1007 select org_id into l_org_id
1008 from cn_role_pmt_plans
1009 where role_pmt_plan_id = p_role_pmt_plan_id;
1010
1011 FOR l_rec IN l_cur(l_org_id)
1012 LOOP
1013
1014 CN_SRP_PMT_PLANS_PUB.delete_mass_asgn_srp_pmt_plan
1015 (
1016 p_api_version => 1.0,
1017 p_init_msg_list => fnd_api.g_true,
1018 p_commit => fnd_api.g_true,
1019 p_validation_level => fnd_api.g_valid_level_full,
1020 x_return_status => l_return_status,
1021 x_msg_count => l_msg_count,
1022 x_msg_data => l_msg_data,
1023 p_srp_role_id => l_rec.srp_role_id,
1024 p_role_pmt_plan_id => p_role_pmt_plan_id,
1025 x_loading_status => l_loading_status);
1026
1027 -- Bug 5125998
1028 -- After discussing with Fred Mburu on MAY 30 2006
1029 -- it was understood that this API is going to be called
1030 -- only from the front end.
1031 -- Apparently in 11.5.10 even when there was an issue for mass
1032 -- assignment of the payment plan, the error rows were silently
1033 -- suppressed and valid rows were committed. (Partial commit implementation)
1034
1035 IF (p_suppress_flag = 'Y') THEN
1036 l_msg_count := 0;
1037 l_msg_data := '';
1038 x_return_status := FND_API.G_RET_STS_SUCCESS;
1039 ELSE
1040 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1041 x_return_status := l_return_status;
1042 x_loading_status := l_loading_status;
1043 EXIT;
1044 END IF;
1045 END IF;
1046
1047 --IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1048 -- x_return_status := l_return_status;
1049 -- x_loading_status := l_loading_status;
1050 -- EXIT;
1051 --END IF;
1052
1053 END LOOP;
1054 END srp_pmt_plan_asgn_for_delete;
1055
1056
1057 -- --------------------------------------------------------------------------*
1058 -- Procedure: create_role_pmt_plan
1059 -- --------------------------------------------------------------------------*
1060 PROCEDURE Create_Role_Pmt_Plan
1061 (
1062 p_api_version IN NUMBER ,
1063 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
1064 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
1065 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
1066 x_return_status OUT NOCOPY VARCHAR2 ,
1067 x_loading_status OUT NOCOPY VARCHAR2 ,
1068 x_msg_count OUT NOCOPY NUMBER ,
1069 x_msg_data OUT NOCOPY VARCHAR2 ,
1070 p_role_pmt_plan_rec IN role_pmt_plan_rec_type := G_MISS_ROLE_PMT_PLAN_REC
1071 ) IS
1072
1073 l_api_name CONSTANT VARCHAR2(30) := 'Create_Role_Pmt_Plan';
1074 l_api_version CONSTANT NUMBER := 1.0;
1075 l_role_pmt_plan_id cn_role_pmt_plans.role_pmt_plan_id%TYPE;
1076 l_role_id cn_roles.role_id%TYPE;
1077 l_pmt_plan_id cn_pmt_plans.pmt_plan_id%TYPE;
1078
1079
1080 -- Declaration for user hooks
1081 l_rec role_pmt_plan_rec_type;
1082 l_OAI_array JTF_USR_HKS.oai_data_array_type;
1083 l_bind_data_id NUMBER ;
1084
1085
1086 BEGIN
1087 -- Standard Start of API savepoint
1088 SAVEPOINT create_role_pmt_plan;
1089
1090 -- Standard call to check for call compatibility.
1091 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
1092 p_api_version ,
1093 l_api_name ,
1094 G_PKG_NAME )
1095 THEN
1096 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1097 END IF;
1098
1099 -- Initialize message list if p_init_msg_list is set to TRUE.
1100 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1101 FND_MSG_PUB.initialize;
1102 END IF;
1103
1104 -- Initialize API return status to success
1105 x_return_status := FND_API.G_RET_STS_SUCCESS;
1106 x_loading_status := 'CN_INSERTED';
1107
1108 -- Assign the parameter to a local variable to be passed to Pre, Post
1109 -- and Business APIs
1110 l_rec := p_role_pmt_plan_rec;
1111
1112 -- Start of API body
1113
1114
1115 check_valid_insert
1116 ( x_return_status => x_return_status,
1117 x_msg_count => x_msg_count,
1118 x_msg_data => x_msg_data,
1119 p_role_pmt_plan_rec => p_role_pmt_plan_rec,
1120 x_role_id => l_role_id,
1121 x_pmt_plan_id => l_pmt_plan_id,
1122 p_loading_status => x_loading_status, -- in
1123 x_loading_status => x_loading_status -- out
1124 );
1125
1126 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
1127 RAISE FND_API.G_EXC_ERROR ;
1128 ELSE
1129
1130 SELECT cn_role_pmt_plans_s.NEXTVAL INTO l_role_pmt_plan_id
1131 FROM dual;
1132
1133 cn_role_pmt_plans_pkg.INSERT_ROW
1134 (
1135 x_org_id => p_role_pmt_plan_rec.org_id
1136 ,x_role_pmt_plan_id => l_role_pmt_plan_id
1137 ,x_role_id => l_role_id
1138 ,x_pmt_plan_id => l_pmt_plan_id
1139 ,x_start_date => p_role_pmt_plan_rec.start_date
1140 ,x_end_date => p_role_pmt_plan_rec.end_date
1141 ,x_attribute_category => p_role_pmt_plan_rec.ATTRIBUTE_CATEGORY
1142 ,x_attribute1 => p_role_pmt_plan_rec.ATTRIBUTE1
1143 ,x_attribute2 => p_role_pmt_plan_rec.ATTRIBUTE2
1144 ,x_attribute3 => p_role_pmt_plan_rec.ATTRIBUTE3
1145 ,x_attribute4 => p_role_pmt_plan_rec.ATTRIBUTE4
1146 ,x_attribute5 => p_role_pmt_plan_rec.ATTRIBUTE5
1147 ,x_attribute6 => p_role_pmt_plan_rec.ATTRIBUTE6
1148 ,x_attribute7 => p_role_pmt_plan_rec.ATTRIBUTE7
1149 ,x_attribute8 => p_role_pmt_plan_rec.ATTRIBUTE8
1150 ,x_attribute9 => p_role_pmt_plan_rec.ATTRIBUTE9
1151 ,x_attribute10 => p_role_pmt_plan_rec.ATTRIBUTE10
1152 ,x_attribute11 => p_role_pmt_plan_rec.ATTRIBUTE11
1153 ,x_attribute12 => p_role_pmt_plan_rec.ATTRIBUTE12
1154 ,x_attribute13 => p_role_pmt_plan_rec.ATTRIBUTE13
1155 ,x_attribute14 => p_role_pmt_plan_rec.ATTRIBUTE14
1156 ,x_attribute15 => p_role_pmt_plan_rec.ATTRIBUTE15
1157 ,x_created_by => g_created_by
1158 ,x_creation_date => g_creation_date
1159 ,x_last_update_login => g_last_update_login
1160 ,x_last_update_date => g_last_update_date
1161 ,x_last_updated_by => g_last_updated_by);
1162
1163 -- Call srp-plan assignment API to insert
1164
1165 srp_pmt_plan_asgn_for_insert(p_role_id => l_role_id,
1166 p_role_pmt_plan_id => l_role_pmt_plan_id,
1167 p_suppress_flag => 'Y',
1168 x_return_status => x_return_status,
1169 p_loading_status => x_loading_status,
1170 x_loading_status => x_loading_status);
1171
1172 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
1173 RAISE FND_API.G_EXC_ERROR;
1174 END IF;
1175
1176 END IF;
1177
1178
1179 -- End of API body
1180
1181 -- Standard check of p_commit.
1182
1183 IF FND_API.To_Boolean( p_commit ) THEN
1184 COMMIT WORK;
1185 END IF;
1186
1187
1188 -- Standard call to get message count and if count is 1, get message info.
1189
1190 FND_MSG_PUB.Count_And_Get
1191 (
1192 p_count => x_msg_count ,
1193 p_data => x_msg_data ,
1194 p_encoded => FND_API.G_FALSE
1195 );
1196
1197 EXCEPTION
1198 WHEN FND_API.G_EXC_ERROR THEN
1199 ROLLBACK TO create_role_pmt_plan;
1200 x_return_status := FND_API.G_RET_STS_ERROR ;
1201 FND_MSG_PUB.Count_And_Get
1202 (
1203 p_count => x_msg_count ,
1204 p_data => x_msg_data ,
1205 p_encoded => FND_API.G_FALSE
1206 );
1207 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1208 ROLLBACK TO create_role_pmt_plan;
1209 x_loading_status := 'UNEXPECTED_ERR';
1210 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1211 FND_MSG_PUB.Count_And_Get
1212 (
1213 p_count => x_msg_count ,
1214 p_data => x_msg_data ,
1215 p_encoded => FND_API.G_FALSE
1216 );
1217 WHEN OTHERS THEN
1218 ROLLBACK TO create_role_pmt_plan;
1219 x_loading_status := 'UNEXPECTED_ERR';
1220 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1221 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1222 THEN
1223 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
1224 END IF;
1225 FND_MSG_PUB.Count_And_Get
1226 (
1227 p_count => x_msg_count ,
1228 p_data => x_msg_data ,
1229 p_encoded => FND_API.G_FALSE
1230 );
1231
1232 END create_role_pmt_plan;
1233
1234
1235 -- --------------------------------------------------------------------------*
1236 -- Procedure: Update_Role_Pmt_Plan
1237 -- --------------------------------------------------------------------------*
1238 PROCEDURE Update_Role_Pmt_Plan
1239 ( p_api_version IN NUMBER ,
1240 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
1241 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
1242 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1243 x_return_status OUT NOCOPY VARCHAR2 ,
1244 x_loading_status OUT NOCOPY VARCHAR2 ,
1245 x_msg_count OUT NOCOPY NUMBER ,
1246 x_msg_data OUT NOCOPY VARCHAR2 ,
1247 p_role_pmt_plan_rec_old IN role_pmt_plan_rec_type := G_MISS_ROLE_PMT_PLAN_REC,
1248 p_ovn IN cn_role_pmt_plans.object_version_number%TYPE,
1249 p_role_pmt_plan_rec_new IN role_pmt_plan_rec_type := G_MISS_ROLE_PMT_PLAN_REC
1250 ) IS
1251
1252 l_api_name CONSTANT VARCHAR2(30) := 'Update_Role_Pmt_Plan';
1253 l_api_version CONSTANT NUMBER := 1.0;
1254 l_role_pmt_plan_id_old cn_role_pmt_plans.role_pmt_plan_id%TYPE;
1255 l_role_id cn_roles.role_id%TYPE;
1256 l_pmt_plan_id cn_pmt_plans.pmt_plan_id%TYPE;
1257 l_date_update_only VARCHAR2(1);
1258
1259 -- Declaration for user hooks
1260 l_rec_old role_pmt_plan_rec_type;
1261 l_rec_new role_pmt_plan_rec_type;
1262 l_OAI_array JTF_USR_HKS.oai_data_array_type;
1263 l_bind_data_id NUMBER ;
1264
1265 BEGIN
1266 -- Standard Start of API savepoint
1267
1268 SAVEPOINT update_role_pmt_plan;
1269
1270 -- Standard call to check for call compatibility.
1271
1272 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
1273 p_api_version ,
1274 l_api_name ,
1275 G_PKG_NAME )
1276 THEN
1277 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1278 END IF;
1279
1280 -- Initialize message list if p_init_msg_list is set to TRUE.
1281 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1282 FND_MSG_PUB.initialize;
1283 END IF;
1284
1285 -- Initialize API return status to success
1286 x_return_status := FND_API.G_RET_STS_SUCCESS;
1287 x_loading_status := 'CN_UPDATED';
1288
1289
1290 -- Assign the parameter to a local variable to be passed to Pre, Post
1291 -- and Business APIs
1292 l_rec_old := p_role_pmt_plan_rec_old;
1293 l_rec_new := p_role_pmt_plan_rec_old;
1294
1295
1296 -- Start of API body
1297
1298 check_valid_update
1299 ( x_return_status => x_return_status,
1300 x_msg_count => x_msg_count,
1301 x_msg_data => x_msg_data,
1302 p_role_pmt_plan_rec_old => p_role_pmt_plan_rec_old,
1303 p_role_pmt_plan_rec_new => p_role_pmt_plan_rec_new,
1304 x_role_pmt_plan_id_old => l_role_pmt_plan_id_old,
1305 x_role_id => l_role_id,
1306 x_pmt_plan_id => l_pmt_plan_id,
1307 x_date_update_only => l_date_update_only,
1308 p_loading_status => x_loading_status, -- in
1309 x_loading_status => x_loading_status -- out
1310 );
1311
1312 -- x_return_status is failure for all failure cases,
1313
1314 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
1315 RAISE FND_API.G_EXC_ERROR;
1316 ELSE
1317
1318 cn_role_pmt_plans_pkg.UPDATE_ROW
1319 (
1320 x_org_id => p_role_pmt_plan_rec_new.org_id
1321 ,x_role_pmt_plan_id => l_role_pmt_plan_id_old
1322 ,x_role_id => l_role_id
1323 ,x_pmt_plan_id => l_pmt_plan_id
1324 ,x_start_date => p_role_pmt_plan_rec_new.start_date
1325 ,x_end_date => p_role_pmt_plan_rec_new.end_date
1326 ,x_attribute_category => p_role_pmt_plan_rec_new.ATTRIBUTE_CATEGORY
1327 ,x_attribute1 => p_role_pmt_plan_rec_new.ATTRIBUTE1
1328 ,x_attribute2 => p_role_pmt_plan_rec_new.ATTRIBUTE2
1329 ,x_attribute3 => p_role_pmt_plan_rec_new.ATTRIBUTE3
1330 ,x_attribute4 => p_role_pmt_plan_rec_new.ATTRIBUTE4
1331 ,x_attribute5 => p_role_pmt_plan_rec_new.ATTRIBUTE5
1332 ,x_attribute6 => p_role_pmt_plan_rec_new.ATTRIBUTE6
1333 ,x_attribute7 => p_role_pmt_plan_rec_new.ATTRIBUTE7
1334 ,x_attribute8 => p_role_pmt_plan_rec_new.ATTRIBUTE8
1335 ,x_attribute9 => p_role_pmt_plan_rec_new.ATTRIBUTE9
1336 ,x_attribute10 => p_role_pmt_plan_rec_new.ATTRIBUTE10
1337 ,x_attribute11 => p_role_pmt_plan_rec_new.ATTRIBUTE11
1338 ,x_attribute12 => p_role_pmt_plan_rec_new.ATTRIBUTE12
1339 ,x_attribute13 => p_role_pmt_plan_rec_new.ATTRIBUTE13
1340 ,x_attribute14 => p_role_pmt_plan_rec_new.ATTRIBUTE14
1341 ,x_attribute15 => p_role_pmt_plan_rec_new.ATTRIBUTE15
1342 ,x_created_by => g_created_by
1343 ,x_creation_date => g_creation_date
1344 ,x_last_update_login => g_last_update_login
1345 ,x_last_update_date => g_last_update_date
1346 ,x_last_updated_by => g_last_updated_by
1347 ,x_object_version_number => p_ovn);
1348
1349 -- Call srp assignment API to update
1350
1351 -- IF UPDATE is only for start_date and end_date THEN call srp_plan_assigns.update
1352 -- IF the update will change comp plan then
1353 -- call srp_plan_assign.delete then insert
1354
1355
1356 srp_pmt_plan_asgn_for_update(p_role_pmt_plan_id => l_role_pmt_plan_id_old,
1357 p_role_id => l_role_id,
1358 p_date_update_only => l_date_update_only,
1359 x_return_status => x_return_status,
1360 p_loading_status => x_loading_status,
1361 x_loading_status => x_loading_status);
1362
1363 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
1364 RAISE FND_API.G_EXC_ERROR;
1365 END IF;
1366
1367 END IF;
1368
1369 -- End of API body
1370
1371 -- Standard check of p_commit.
1372
1373 IF FND_API.To_Boolean( p_commit ) THEN
1374 COMMIT WORK;
1375 END IF;
1376
1377 -- Standard call to get message count and if count is 1, get message info.
1378
1379 FND_MSG_PUB.Count_And_Get
1380 (
1381 p_count => x_msg_count ,
1382 p_data => x_msg_data ,
1383 p_encoded => FND_API.G_FALSE
1384 );
1385
1386 EXCEPTION
1387 WHEN FND_API.G_EXC_ERROR THEN
1388 ROLLBACK TO update_role_pmt_plan;
1389 x_return_status := FND_API.G_RET_STS_ERROR ;
1390 FND_MSG_PUB.Count_And_Get
1391 (
1392 p_count => x_msg_count ,
1393 p_data => x_msg_data ,
1394 p_encoded => FND_API.G_FALSE
1395 );
1396 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1397 ROLLBACK TO update_role_pmt_plan;
1398 x_loading_status := 'UNEXPECTED_ERR';
1399 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1400 FND_MSG_PUB.Count_And_Get
1401 (
1402 p_count => x_msg_count ,
1403 p_data => x_msg_data ,
1404 p_encoded => FND_API.G_FALSE
1405 );
1406 WHEN OTHERS THEN
1407 ROLLBACK TO update_role_pmt_plan;
1408 x_loading_status := 'UNEXPECTED_ERR';
1409 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1410 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1411 THEN
1412 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
1413 END IF;
1414 FND_MSG_PUB.Count_And_Get
1415 (
1416 p_count => x_msg_count ,
1417 p_data => x_msg_data ,
1418 p_encoded => FND_API.G_FALSE
1419 );
1420
1421 END update_role_pmt_plan;
1422
1423
1424 -- --------------------------------------------------------------------------*
1425 -- Procedure: Delete_Role_Pmt_Plan
1426 -- --------------------------------------------------------------------------*
1427 PROCEDURE Delete_Role_Pmt_Plan
1428 ( p_api_version IN NUMBER ,
1429 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
1430 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
1431 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1432 x_return_status OUT NOCOPY VARCHAR2 ,
1433 x_loading_status OUT NOCOPY VARCHAR2 ,
1434 x_msg_count OUT NOCOPY NUMBER ,
1435 x_msg_data OUT NOCOPY VARCHAR2 ,
1436 p_role_pmt_plan_rec IN role_pmt_plan_rec_type := G_MISS_ROLE_PMT_PLAN_REC
1437 ) IS
1438
1439 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Role_Pmt_Plan';
1440 l_api_version CONSTANT NUMBER := 1.0;
1441 l_role_pmt_plan_id cn_role_pmt_plans.role_pmt_plan_id%TYPE;
1442 l_role_id cn_roles.role_id%TYPE;
1443
1444 -- Declaration for user hooks
1445 l_rec role_pmt_plan_rec_type;
1446 l_OAI_array JTF_USR_HKS.oai_data_array_type;
1447 l_bind_data_id NUMBER ;
1448
1449
1450 BEGIN
1451 -- Standard Start of API savepoint
1452
1453 SAVEPOINT delete_role_pmt_plan;
1454
1455 -- Standard call to check for call compatibility.
1456
1457 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
1458 p_api_version ,
1459 l_api_name ,
1460 G_PKG_NAME )
1461 THEN
1462 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1463 END IF;
1464
1465 -- Initialize message list if p_init_msg_list is set to TRUE.
1466 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1467 FND_MSG_PUB.initialize;
1468 END IF;
1469
1470 -- Initialize API return status to success
1471 x_return_status := FND_API.G_RET_STS_SUCCESS;
1472 x_loading_status := 'CN_DELETED';
1473
1474
1475 -- Assign the parameter to a local variable to be passed to Pre, Post
1476 -- and Business APIs
1477 l_rec := p_role_pmt_plan_rec;
1478
1479 -- Start of API body
1480
1481 check_valid_delete
1482 ( x_return_status => x_return_status,
1483 x_msg_count => x_msg_count,
1484 x_msg_data => x_msg_data,
1485 p_role_pmt_plan_rec => p_role_pmt_plan_rec,
1486 x_role_pmt_plan_id => l_role_pmt_plan_id,
1487 p_loading_status => x_loading_status, -- in
1488 x_loading_status => x_loading_status -- out
1489 );
1490
1491 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
1492 RAISE FND_API.G_EXC_ERROR ;
1493 ELSE
1494
1495 -- need to call srp assignment API to delete
1496
1497 l_role_id := cn_api.get_role_id(p_role_pmt_plan_rec.role_name);
1498 srp_pmt_plan_asgn_for_delete(p_role_id => l_role_id,
1499 p_role_pmt_plan_id => l_role_pmt_plan_id,
1500 p_suppress_flag => 'Y',
1501 x_return_status => x_return_status,
1502 p_loading_status => x_loading_status,
1503 x_loading_status => x_loading_status);
1504
1505 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
1506 RAISE FND_API.G_EXC_ERROR;
1507 END IF;
1508
1509 -- delete_row
1510 cn_role_pmt_plans_pkg.delete_row(x_role_pmt_plan_id => l_role_pmt_plan_id);
1511
1512 END IF;
1513
1514 -- End of API body
1515
1516 -- Standard check of p_commit.
1517
1518 IF FND_API.To_Boolean( p_commit ) THEN
1519 COMMIT WORK;
1520 END IF;
1521
1522
1523 -- Standard call to get message count and if count is 1, get message info.
1524
1525 FND_MSG_PUB.Count_And_Get
1526 (
1527 p_count => x_msg_count ,
1528 p_data => x_msg_data ,
1529 p_encoded => FND_API.G_FALSE
1530 );
1531
1532 EXCEPTION
1533 WHEN FND_API.G_EXC_ERROR THEN
1534 ROLLBACK TO delete_role_pmt_plan;
1535 x_return_status := FND_API.G_RET_STS_ERROR ;
1536 FND_MSG_PUB.Count_And_Get
1537 (
1538 p_count => x_msg_count ,
1539 p_data => x_msg_data ,
1540 p_encoded => FND_API.G_FALSE
1541 );
1542 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1543 ROLLBACK TO delete_role_pmt_plan;
1544 x_loading_status := 'UNEXPECTED_ERR';
1545 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1546 FND_MSG_PUB.Count_And_Get
1547 (
1548 p_count => x_msg_count ,
1549 p_data => x_msg_data ,
1550 p_encoded => FND_API.G_FALSE
1551 );
1552 WHEN OTHERS THEN
1553 ROLLBACK TO delete_role_pmt_plan;
1554 x_loading_status := 'UNEXPECTED_ERR';
1555 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1556 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1557 THEN
1558 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
1559 END IF;
1560 FND_MSG_PUB.Count_And_Get
1561 (
1562 p_count => x_msg_count ,
1563 p_data => x_msg_data ,
1564 p_encoded => FND_API.G_FALSE
1565 );
1566 END delete_role_pmt_plan;
1567
1568 FUNCTION date_range_overlap
1569 (
1570 a_start_date DATE,
1571 a_end_date DATE,
1572 b_start_date DATE,
1573 b_end_date DATE
1574 ) RETURN NUMBER IS
1575
1576 BEGIN
1577
1578 IF (a_end_date IS NOT NULL) THEN
1579 IF (b_end_date IS NOT NULL) THEN
1580 IF ((b_start_date BETWEEN a_start_date AND a_end_date) OR
1581 (b_end_date BETWEEN a_start_date AND a_end_date) OR
1582 (a_start_date BETWEEN b_start_date AND b_end_date) OR
1583 (a_end_date BETWEEN b_start_date AND b_end_date)) THEN
1584 RETURN 1; -- overlap
1585 END IF;
1586 ELSE
1587 IF (b_start_date <= a_end_date) THEN
1588 RETURN 1; -- overlap
1589 END IF;
1590 END IF;
1591 ELSE
1592 IF (b_end_date IS NOT NULL) THEN
1593 IF (b_end_date >= a_start_date) THEN
1594 RETURN 1; -- overlap
1595 END IF;
1596 ELSE
1597 RETURN 1; -- overlap
1598 END IF;
1599 END IF;
1600
1601 RETURN 0; -- not overlap
1602
1603 END date_range_overlap;
1604
1605 FUNCTION date_range_diff_present
1606 (
1607 a_start_date DATE,
1608 a_end_date DATE,
1609 b_start_date DATE,
1610 b_end_date DATE
1611 ) RETURN NUMBER IS
1612 x_date_range_tbl cn_api.date_range_tbl_type;
1613 BEGIN
1614 cn_api.get_date_range_diff(a_start_date,
1615 a_end_date,
1616 b_start_date,
1617 b_end_date,
1618 x_date_range_tbl);
1619 IF x_date_range_tbl IS NOT NULL THEN
1620 IF x_date_range_tbl.count > 0 THEN
1621 return 1;
1622 end if;
1623 else
1624 return 0;
1625 end if;
1626 return 0;
1627
1628
1629 END date_range_diff_present;
1630
1631 FUNCTION date_range_intersect
1632 (
1633 a_start_date DATE,
1634 a_end_date DATE,
1635 b_start_date DATE,
1636 b_end_date DATE
1637 ) RETURN NUMBER IS
1638 x_start_date DATE;
1639 x_end_date DATE;
1640 BEGIN
1641 if(cn_api.date_range_overlap(a_start_date,
1642 a_end_date,
1643 b_start_date,
1644 b_end_date)) THEN
1645 cn_api.get_date_range_intersect(a_start_date,
1646 a_end_date,
1647 b_start_date,
1648 b_end_date,
1649 x_start_date,
1650 x_end_date);
1651 IF x_start_date IS NOT NULL THEN
1652 return 1;
1653 else
1654 return 0;
1655 end if;
1656 return 0;
1657 END IF;
1658 return 0;
1659
1660 END date_range_intersect;
1661
1662 END CN_ROLE_PMT_PLANS_PVT;