[Home] [Help]
PACKAGE BODY: APPS.CN_ROLE_PAY_GROUPS_PVT
Source
1 PACKAGE BODY CN_ROLE_PAY_GROUPS_PVT AS
2 /* $Header: cnvrpgpb.pls 120.16 2010/12/16 02:47:35 rnagired ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CN_ROLE_PAY_GROUPS_PVT';
5 G_FILE_NAME CONSTANT VARCHAR2(12) := 'cnvrpgpb.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 procedure get_date_range_intersect(a_start_date in date, a_end_date in date,
17 b_start_date in date, b_end_date in date,
18 x_start_date out nocopy date, x_end_date out nocopy date)
19 IS
20 BEGIN
21 if ( a_start_date is null or b_start_date is null) then
22 x_start_date := null;
23 x_end_date := null;
24 elsif (a_end_date is not null and a_end_date < b_start_date)
25 or ( b_end_date is not null and a_start_date > b_end_date) then
26 x_start_date := null;
27 x_end_date := null;
28 else
29 x_start_date := greatest(a_start_date, b_start_date);
30 if a_end_date is null then
31 x_end_date := b_end_date;
32 elsif b_end_date is null then
33 x_end_date := a_end_date;
34 else
35 x_end_date := least(a_end_date, b_end_date);
36 end if;
37 end if;
38 END;
39
40 procedure get_masgn_date_intersect(
41 p_role_pay_group_id IN NUMBER,
42 p_srp_role_id IN NUMBER,
43 x_start_date OUT NOCOPY DATE,
44 x_end_date OUT NOCOPY DATE) IS
45
46 l_start_date cn_srp_pay_groups.start_date%TYPE;
47 l_end_date cn_srp_pay_groups.start_date%TYPE;
48
49
50 l_res_start_date cn_srp_pay_groups.start_date%TYPE;
51 l_res_end_date cn_srp_pay_groups.start_date%TYPE;
52
53 l_role_pg_start_date cn_srp_pay_groups.start_date%TYPE;
54 l_role_pg_end_date cn_srp_pay_groups.start_date%TYPE;
55
56 l_srp_role_start_date cn_srp_pay_groups.start_date%TYPE;
57 l_srp_role_end_date cn_srp_pay_groups.start_date%TYPE;
58
59 l_pg_start_date cn_srp_pay_groups.start_date%TYPE;
60 l_pg_end_date cn_srp_pay_groups.start_date%TYPE;
61
62 l_org_id NUMBER;
63 l_salesrep_id NUMBER;
64 l_pay_group_id NUMBER;
65 l_salesrep_name cn_salesreps.name%TYPE;
66 l_role_name jtf_rs_roles_tl.role_name%TYPE;
67
68 BEGIN
69 -- get start_date, end_date org_id and pay_group_id from role_pay_groups
70 select org_id, pay_group_id, start_date, end_date
71 into l_org_id, l_pay_group_id, l_role_pg_start_date, l_role_pg_end_date
72 from cn_role_pay_groups
73 where role_pay_group_id = p_role_pay_group_id;
74
75 -- get srp role assignment start and end dates
76 select start_date, end_date, salesrep_id
77 into l_srp_role_start_date, l_srp_role_end_date, l_salesrep_id
78 from cn_srp_roles
79 where srp_role_id = p_srp_role_id
80 and org_id = l_org_id;
81
82 -- get intersection between srp_role and role_pay_group dates
83 get_date_range_intersect(
84 a_start_date => l_srp_role_start_date,
85 a_end_date => l_srp_role_end_date,
86 b_start_date => l_role_pg_start_date,
87 b_end_date => l_role_pg_end_date,
88 x_start_date => x_start_date,
89 x_end_date => x_end_date);
90
91 l_start_date := x_start_date;
92 l_end_date := x_end_date;
93
94 -- get resource start and end dates
95 select name, start_date_active, end_date_active
96 into l_salesrep_name, l_res_start_date, l_res_end_date
97 from cn_salesreps
98 where salesrep_id = l_salesrep_id
99 and org_id = l_org_id;
100
101 -- get intersection with resource start and end dates
102 get_date_range_intersect(
103 a_start_date => l_start_date,
104 a_end_date => l_end_date,
105 b_start_date => l_res_start_date,
106 b_end_date => l_res_end_date,
107 x_start_date => x_start_date,
108 x_end_date => x_end_date);
109
110 l_start_date := x_start_date;
111 l_end_date := x_end_date;
112
113 --IF the sales rep start date is outside the range for the pay group which has the role of this sales rep. Raise Error
114 IF(l_start_date is NULL)
115 THEN
116 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
117 SELECT role_name into l_role_name
118 FROM jtf_rs_roles_tl
119 WHERE role_id =
120 (SELECT role_id
121 FROM cn_srp_roles
122 WHERE srp_role_id = p_srp_role_id
123 AND salesrep_id = l_salesrep_id)
124 AND language = USERENV('LANG');
125 fnd_message.set_name('CN', 'CN_SRP_OUTSIDE_PAYGRP_DATE');
126 FND_MESSAGE.SET_TOKEN('SALES_REP_NAME', l_salesrep_name);
127 FND_MESSAGE.SET_TOKEN('ROLE_NAME', l_role_name);
128 fnd_msg_pub.ADD;
129 END IF;
130 RAISE FND_API.G_EXC_ERROR ;
131 END IF;
132
133 -- get pay groups start and end dates
134 select start_date, end_date
135 into l_pg_start_date, l_pg_end_date
136 from cn_pay_groups
137 where pay_group_id = l_pay_group_id;
138
139 -- get intersection with pay group start and end dates
140 get_date_range_intersect(
141 a_start_date => l_start_date,
142 a_end_date => l_end_date,
143 b_start_date => l_pg_start_date,
144 b_end_date => l_pg_end_date,
145 x_start_date => x_start_date,
146 x_end_date => x_end_date);
147
148 END;
149
150 -- ----------------------------------------------------------------------------*
151 -- Function : valid_role_name
152 -- Desc : check if the role_name exists in cn_roles
153 -- ---------------------------------------------------------------------------*
154 FUNCTION valid_role_name
155 (
156 p_role_name cn_roles.name%TYPE
157 ) RETURN BOOLEAN IS
158
159 CURSOR l_cur(l_role_name cn_roles.name%TYPE) IS
160 SELECT *
161 FROM cn_roles
162 WHERE name = l_role_name;
163
164 l_rec l_cur%ROWTYPE;
165
166 BEGIN
167
168 OPEN l_cur(p_role_name);
169 FETCH l_cur INTO l_rec;
170 IF (l_cur%notfound) THEN
171 CLOSE l_cur;
172 RETURN FALSE;
173 ELSE
174 CLOSE l_cur;
175 RETURN TRUE;
176 END IF;
177
178 END valid_role_name;
179
180 -- ----------------------------------------------------------------------------*
181 -- Function : valid_pay_group_name
182 -- Desc : check if the comp_plan_name exists in cn_comp_plans
183 -- ---------------------------------------------------------------------------*
184 FUNCTION valid_pay_groups_name
185 (
186 p_pay_group_name cn_pay_groups.name%TYPE,
187 p_org_id cn_pay_groups.org_id%TYPE
188 ) RETURN BOOLEAN IS
189
190 CURSOR l_cur(l_pay_group_name cn_pay_groups.name%TYPE,l_org_id cn_pay_groups.org_id%TYPE) IS
191 SELECT *
192 FROM cn_pay_groups
193 WHERE name = l_pay_group_name and org_id =l_org_id;
194
195 l_rec l_cur%ROWTYPE;
196
197 BEGIN
198
199 OPEN l_cur(p_pay_group_name,p_org_id);
200 FETCH l_cur INTO l_rec;
201 IF (l_cur%notfound) THEN
202 CLOSE l_cur;
203 RETURN FALSE;
204 ELSE
205 CLOSE l_cur;
206 RETURN TRUE;
207 END IF;
208
209 END valid_pay_groups_name;
210
211 -- ----------------------------------------------------------------------------*
212 -- Function : valid_role_pay_group_id
213 -- Desc : check if the pay_group_id exists in cn_roles
214 -- ---------------------------------------------------------------------------*
215 FUNCTION valid_role_pay_group_id
216 (
217 p_role_pay_group_id cn_role_pay_groups.role_pay_group_id%TYPE
218 ) RETURN BOOLEAN IS
219
220 CURSOR l_cur(l_role_pay_group_id cn_role_pay_groups.role_pay_group_id%TYPE) IS
221 SELECT *
222 FROM cn_role_pay_groups
223 WHERE role_pay_group_id = l_role_pay_group_id;
224
225 l_rec l_cur%ROWTYPE;
226
227 BEGIN
228
229 OPEN l_cur(p_role_pay_group_id);
230 FETCH l_cur INTO l_rec;
231 IF (l_cur%notfound) THEN
232 CLOSE l_cur;
233 RETURN FALSE;
234 ELSE
235 CLOSE l_cur;
236 RETURN TRUE;
237 END IF;
238
239 END valid_role_pay_group_id;
240
241
242 -- ----------------------------------------------------------------------------*
243 -- Function : is_exist
244 -- Desc : check if the role_pay_group_id exists in cn_role_pay_groups
245 -- ---------------------------------------------------------------------------*
246 FUNCTION is_exist
247 (
248 p_role_pay_group_id cn_role_pay_groups.role_pay_group_id%TYPE
249 ) RETURN BOOLEAN IS
250
251 CURSOR l_cur(l_role_pay_group_id cn_role_pay_groups.role_pay_group_id%TYPE) IS
252 SELECT *
253 FROM cn_role_pay_groups
254 WHERE role_pay_group_id = l_role_pay_group_id;
255
256 l_rec l_cur%ROWTYPE;
257
258 BEGIN
259
260 OPEN l_cur(p_role_pay_group_id);
261 FETCH l_cur INTO l_rec;
262 IF (l_cur%notfound) THEN
263 CLOSE l_cur;
264 RETURN FALSE;
265 ELSE
266 CLOSE l_cur;
267 RETURN TRUE;
268 END IF;
269
270 END is_exist;
271
272 FUNCTION get_pg_id ( p_pay_group_name VARCHAR2,p_org_id NUMBER )
273 RETURN cn_pay_groups.pay_group_id%TYPE IS
274
275 l_pay_group_id cn_pay_groups.pay_group_id%TYPE;
276
277 BEGIN
278 SELECT pay_group_id
279 INTO l_pay_group_id
280 FROM cn_pay_groups
281 WHERE name = p_pay_group_name and org_id=p_org_id;
282
283 RETURN l_pay_group_id;
284
285 EXCEPTION
286 WHEN no_data_found THEN
287 RETURN NULL;
288
289 END get_pg_id;
290
291 --| ---------------------------------------------------------------------=
292 --| Function Name : get_pay_group_name
293 --| Desc : Get the pay group name using the pay group id
294 --| ---------------------------------------------------------------------=
295 FUNCTION get_pg_name (p_pay_group_id VARCHAR2 )
296 RETURN cn_pay_groups.name%TYPE IS
297
298 l_pay_group_name cn_pay_groups.name%TYPE;
299
300 BEGIN
301 SELECT name
302 INTO l_pay_group_name
303 FROM cn_pay_groups
304 WHERE pay_group_id = p_pay_group_id ;
305
306 RETURN l_pay_group_name;
307
308 EXCEPTION
309 WHEN no_data_found THEN
310 RETURN NULL;
311
312 END get_pg_name;
313
314 -- --------------------------------------------------------------------------=
315 -- Function : get_role_pay_group_id
316 -- Desc : get the role_pay_group_id if it exists in cn_role_pay_groups
317 -- --------------------------------------------------------------------------=
318 FUNCTION get_role_pay_group_id
319 (
320 p_role_name IN VARCHAR2,
321 p_pay_group_name IN VARCHAR2,
322 p_start_date IN DATE,
323 p_end_date IN DATE,
324 p_org_id IN NUMBER
325 ) RETURN cn_role_pay_groups.role_pay_group_id%TYPE IS
326
327 CURSOR l_cur(l_role_id cn_role_pay_groups.role_id%TYPE,
328 l_pay_group_id cn_role_pay_groups.pay_group_id%TYPE,
329 l_start_date cn_role_pay_groups.start_date%TYPE,
330 l_end_date cn_role_pay_groups.end_date%TYPE) IS
331 SELECT role_pay_group_id
332 FROM cn_role_pay_groups
333 WHERE role_id = l_role_id AND
334 pay_group_id = l_pay_group_id AND
335 start_date = l_start_date AND
336 ((end_date = l_end_date) OR
337 (end_date IS NULL AND l_end_date IS NULL));
338
339 l_rec l_cur%ROWTYPE;
340 l_role_id cn_role_pay_groups.role_id%TYPE;
341 l_pay_group_id cn_role_pay_groups.pay_group_id%TYPE;
342
343 BEGIN
344
345 l_role_id := cn_api.get_role_id(p_role_name);
346 l_pay_group_id := get_pg_id(p_pay_group_name,p_org_id);
347
348 OPEN l_cur(l_role_id, l_pay_group_id, p_start_date, p_end_date);
349 FETCH l_cur INTO l_rec;
350 IF (l_cur%notfound) THEN
351 CLOSE l_cur;
352 RETURN NULL;
353 ELSE
354 CLOSE l_cur;
355 RETURN l_rec.role_pay_group_id;
356 END IF;
357
358 END get_role_pay_group_id;
359
360
361 -- ----------------------------------------------------------------------------*
362 -- Procedure: check_valid_insert
363 -- Desc : check if the record is valid to insert into cn_role_pay_groups
364 -- called in create_role_pay_groups before inserting a role-paygroup
365 -- assignment
366 -- ----------------------------------------------------------------------------*
367 PROCEDURE check_valid_insert
368 (
369 x_return_status OUT NOCOPY VARCHAR2,
370 x_msg_count OUT NOCOPY NUMBER,
371 x_msg_data OUT NOCOPY VARCHAR2,
372 p_role_pay_groups_rec IN role_pay_groups_rec_type,
373 x_role_id OUT NOCOPY cn_roles.role_id%TYPE,
374 x_pay_group_id OUT NOCOPY cn_role_pay_groups.pay_group_id%TYPE,
375 p_loading_status IN VARCHAR2,
376 x_loading_status OUT NOCOPY VARCHAR2
377 ) IS
378
379 l_api_name CONSTANT VARCHAR2(30) := 'check_valid_insert';
380 l_count NUMBER := 0;
381
382 l_loading_status VARCHAR2(100);
383 l_null_date CONSTANT DATE := to_date('31-12-9999','DD-MM-YYYY');
384
385
386 CURSOR l_cur(l_role_id cn_roles.role_id%TYPE,l_org_id cn_role_pay_groups.org_id%TYPE) IS
387 SELECT start_date, end_date, pay_group_id
388 FROM cn_role_pay_groups
389 WHERE role_id = l_role_id and org_id=l_org_id;
390
391 CURSOR l_cp_cur(l_pay_group_name cn_pay_groups.name%TYPE,l_org_id cn_pay_groups.org_id%TYPE) IS
392 SELECT start_date, end_date
393 FROM cn_pay_groups
394 WHERE name = l_pay_group_name and org_id=l_org_id;
395
396 l_cp_rec l_cp_cur%ROWTYPE;
397
398 BEGIN
399
400 -- Initialize API return status to success
401 x_return_status := FND_API.G_RET_STS_SUCCESS;
402 l_loading_status := p_loading_status;
403 x_loading_status := p_loading_status;
404
405
406 -- Start of API body
407
408 -- validate the following issues
409
410 -- role_name can not be missing or null
411 IF (cn_api.chk_miss_null_char_para
412 (p_char_para => p_role_pay_groups_rec.role_name,
413 p_obj_name => G_ROLE_NAME,
414 p_loading_status => l_loading_status,
415 x_loading_status => x_loading_status) = FND_API.G_TRUE) THEN
416 RAISE FND_API.G_EXC_ERROR ;
417 END IF;
418
419 -- pay_group_name can not be missing or null
420 IF (cn_api.chk_miss_null_char_para
421 (p_char_para => p_role_pay_groups_rec.pay_groups_name,
422 p_obj_name => G_PG_NAME,
423 p_loading_status => l_loading_status,
424 x_loading_status => x_loading_status) = FND_API.G_TRUE) THEN
425 RAISE FND_API.G_EXC_ERROR ;
426 END IF;
427
428 -- start_date can not be null
429 -- start_date can not be missing
430 -- start_date < end_date if end_date is null
431 IF ( (cn_api.invalid_date_range
432 (p_start_date => p_role_pay_groups_rec.start_date,
433 p_end_date => p_role_pay_groups_rec.end_date,
434 p_end_date_nullable => FND_API.G_TRUE,
435 p_loading_status => l_loading_status,
436 x_loading_status => x_loading_status)) = FND_API.G_TRUE ) THEN
437 RAISE FND_API.G_EXC_ERROR ;
438 END IF;
439
440 -- role_name must exist in cn_roles
441 IF NOT valid_role_name(p_role_pay_groups_rec.role_name) THEN
442 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
443 fnd_message.set_name('CN', 'CN_RL_ASGN_ROLE_NOT_EXIST');
444 FND_MESSAGE.SET_TOKEN('ROLE_NAME',p_role_pay_groups_rec.role_name);
445 fnd_msg_pub.ADD;
446 END IF;
447 x_loading_status := 'CN_RL_ASGN_ROLE_NOT_EXIST';
448 RAISE fnd_api.g_exc_error;
449 ELSE
450 x_role_id := cn_api.get_role_id(p_role_pay_groups_rec.role_name);
451 END IF;
452 -- pay_group_name must exist in cn_pay_groups
453 IF NOT valid_pay_groups_name(p_role_pay_groups_rec.pay_groups_name,p_role_pay_groups_rec.org_id) THEN
454 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
455 fnd_message.set_name('CN', 'CN_RL_ASGN_PG_NOT_EXIST');
456 fnd_message.set_token('PAY_GROUP',p_role_pay_groups_rec.pay_groups_name);
457 fnd_msg_pub.ADD;
458 END IF;
459 x_loading_status := 'CN_RL_ASGN_PG_NOT_EXIST';
460 RAISE fnd_api.g_exc_error;
461 ELSE
462 x_pay_group_id := get_pg_id(p_role_pay_groups_rec.pay_groups_name,p_role_pay_groups_rec.org_id);
463 END IF;
464
465 --
466 -- Check if the current assignment dates do not fit within the effectivity of the
467 -- pay group.
468 --
469 SELECT COUNT(1)
470 INTO l_count
471 FROM cn_pay_groups
472 WHERE (( p_role_pay_groups_rec.start_date NOT BETWEEN start_date AND end_date )
473 OR (p_role_pay_groups_rec.end_date NOT BETWEEN start_date AND end_date))
474 AND pay_group_id = x_pay_group_id;
475
476 IF l_count <> 0
477 THEN
478 --Error condition
479 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
480 THEN
481 fnd_message.set_name('CN', 'CN_INVALID_ROLE_PGRP_ASGN_DT');
482 fnd_msg_pub.add;
483 END IF;
484
485 x_loading_status := 'CN_INVALID_ROLE_PGRP_ASGN_DT';
486 RAISE FND_API.G_EXC_ERROR;
487 END IF;
488
489 --
490 -- Check for overlapping assignments
491 -- Added new message CN_RL_ROLE_PAY_GROUP_OVERLAP for bug 3152146 and included cn_api.date_range_overlap() for checking date overlap
492 /* SELECT count(1)
493 INTO l_count
494 FROM cn_role_pay_groups
495 WHERE p_role_pay_groups_rec.start_date between start_date AND Nvl(end_date, p_role_pay_groups_rec.start_date)
496 AND role_id = x_role_id; */
497
498
499 FOR l_rec IN l_cur(x_role_id,p_role_pay_groups_rec.org_id)
500 LOOP
501 IF ((cn_api.date_range_overlap(l_rec.start_date,
502 l_rec.end_date,
503 p_role_pay_groups_rec.start_date,
504 p_role_pay_groups_rec.end_date)))
505 THEN
506
507
508 /* IF l_count <> 0
509 THEN
510 --Error condition
511 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
512 THEN
513 fnd_message.set_name('CN', 'CN_OVERLAP_SRP_PGRP_ASGN');
514 fnd_msg_pub.add;
515 END IF; */
516
517 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
518 fnd_message.set_name ('CN', 'CN_RL_ROLE_PAY_GROUP_OVERLAP_N');
519 fnd_message.set_token('CURRENT_PAY_GROUP ',p_role_pay_groups_rec.pay_groups_name);
520 fnd_message.set_token('ROLE_NAME',p_role_pay_groups_rec.role_name);
521 fnd_message.set_token('ROLE_START_DATE',p_role_pay_groups_rec.start_date);
522 fnd_message.set_token('ROLE_END_DATE',p_role_pay_groups_rec.end_date);
523 FND_MESSAGE.SET_TOKEN('PAY_GROUP_NAME',get_pg_name(l_rec.pay_group_id));
524 fnd_message.set_token('START_DATE',l_rec.start_date);
525 fnd_message.set_token('END_DATE',l_rec.end_date);
526 fnd_msg_pub.ADD;
527 END IF;
528
529 x_loading_status := 'CN_RL_ROLE_PAY_GROUP_OVERLAP';
530
531 --x_loading_status := 'CN_OVERLAP_SRP_PGRP_ASGN';
532
533 RAISE FND_API.G_EXC_ERROR;
534 END IF;
535 END LOOP;
536
537 --Commented the code as the overlap conditions are handled in the code above
538
539 /* SELECT count(1)
540 INTO l_count
541 FROM cn_role_pay_groups
542 WHERE Nvl(p_role_pay_groups_rec.end_date, l_null_date) between start_date
543 AND Nvl(end_date, Nvl(p_role_pay_groups_rec.end_date, l_null_date))
544 AND role_id = x_role_id;
545
546
547
548 IF l_count <> 0
549 THEN
550 --Error condition
551 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
552 THEN
553 fnd_message.set_name('CN', 'CN_OVERLAP_SRP_PGRP_ASGN');
554 fnd_msg_pub.add;
555 END IF;
556 x_loading_status := 'CN_OVERLAP_SRP_PGRP_ASGN';
557 RAISE FND_API.G_EXC_ERROR;
558 END IF;
559
560 SELECT count(1)
561 INTO l_count
562 FROM cn_role_pay_groups
563 WHERE p_role_pay_groups_rec.start_date <= start_date
564 AND Nvl(p_role_pay_groups_rec.end_date, l_null_date) >= Nvl(end_date, l_null_date) AND role_id = x_role_id;
565
566
567
568 IF l_count <> 0
569 THEN
570 --Error condition
571 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
572 THEN
573 fnd_message.set_name('CN', 'CN_OVERLAP_SRP_PGRP_ASGN');
574 fnd_msg_pub.add;
575 END IF;
576
577 x_loading_status := 'CN_OVERLAP_SRP_PGRP_ASGN';
578 RAISE FND_API.G_EXC_ERROR;
579 END IF; */
580
581 --End of API body
582 -- Standard call to get message count and if count is 1, get message info.
583 FND_MSG_PUB.Count_And_Get
584 (
585 p_count => x_msg_count ,
586 p_data => x_msg_data ,
587 p_encoded => FND_API.G_FALSE
588 );
589
590 EXCEPTION
591 WHEN FND_API.G_EXC_ERROR THEN
592 x_return_status := FND_API.G_RET_STS_ERROR ;
593 FND_MSG_PUB.Count_And_Get
594 (
595 p_count => x_msg_count ,
596 p_data => x_msg_data ,
597 p_encoded => FND_API.G_FALSE
598 );
599 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
600 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
601 x_loading_status := 'UNEXPECTED_ERR';
602 FND_MSG_PUB.Count_And_Get
603 (
604 p_count => x_msg_count ,
605 p_data => x_msg_data ,
606 p_encoded => FND_API.G_FALSE
607 );
608 WHEN OTHERS THEN
609 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
610 x_loading_status := 'UNEXPECTED_ERR';
611 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
612 THEN
613 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
614 END IF;
615 FND_MSG_PUB.Count_And_Get
616 (
617 p_count => x_msg_count ,
618 p_data => x_msg_data ,
619 p_encoded => FND_API.G_FALSE
620 );
621 END check_valid_insert;
622
623
624
625 -- ----------------------------------------------------------------------------*
626 -- Procedure: check_valid_delete
627 -- Desc : check if the record is valid to delete from cn_role_plans
628 -- called in delete_role_plan before deleting a role
629 -- ----------------------------------------------------------------------------*
630 PROCEDURE check_valid_delete
631 (
632 x_return_status OUT NOCOPY VARCHAR2,
633 x_msg_count OUT NOCOPY NUMBER,
634 x_msg_data OUT NOCOPY VARCHAR2,
635 p_role_pay_groups_rec IN role_pay_groups_rec_type,
636 x_role_pay_group_id OUT NOCOPY NUMBER,
637 p_loading_status IN VARCHAR2,
638 x_loading_status OUT NOCOPY VARCHAR2
639 ) IS
640
641 l_api_name CONSTANT VARCHAR2(30) := 'check_valid_delete';
642
643 BEGIN
644
645 -- Initialize API return status to success
646 x_return_status := FND_API.G_RET_STS_SUCCESS;
647 x_loading_status := p_loading_status;
648
649 -- Start of API body
650
651 -- Valide the following issues
652
653 -- Checke if the p_role_plan_id does exist.
654
655 x_role_pay_group_id := get_role_pay_group_id(p_role_pay_groups_rec.role_name,
656 p_role_pay_groups_rec.pay_groups_name,
657 p_role_pay_groups_rec.start_date,
658 p_role_pay_groups_rec.end_date,
659 p_role_pay_groups_rec.org_id);
660 IF (x_role_pay_group_id IS NULL) THEN
661 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
662 FND_MESSAGE.SET_NAME('CN' ,'CN_RL_DEL_ROLE_PLAN_NOT_EXIST');
663 FND_MSG_PUB.Add;
664 END IF;
665 x_loading_status := 'CN_RL_DEL_ROLE_PLAN_NOT_EXIST';
666 RAISE FND_API.G_EXC_ERROR ;
667 END IF;
668
669
670
671 -- End of API body.
672
673 -- Standard call to get message count and if count is 1, get message info.
674
675 FND_MSG_PUB.Count_And_Get
676 (
677 p_count => x_msg_count ,
678 p_data => x_msg_data ,
679 p_encoded => FND_API.G_FALSE
680 );
681
682 EXCEPTION
683 WHEN FND_API.G_EXC_ERROR THEN
684 x_return_status := FND_API.G_RET_STS_ERROR ;
685 FND_MSG_PUB.Count_And_Get
686 (
687 p_count => x_msg_count ,
688 p_data => x_msg_data ,
689 p_encoded => FND_API.G_FALSE
690 );
691 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
692 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
693 x_loading_status := 'UNEXPECTED_ERR';
694 FND_MSG_PUB.Count_And_Get
695 (
696 p_count => x_msg_count ,
697 p_data => x_msg_data ,
698 p_encoded => FND_API.G_FALSE
699 );
700 WHEN OTHERS THEN
701 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
702 x_loading_status := 'UNEXPECTED_ERR';
703 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
704 THEN
705 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
706 END IF;
707 FND_MSG_PUB.Count_And_Get
708 (
709 p_count => x_msg_count ,
710 p_data => x_msg_data ,
711 p_encoded => FND_API.G_FALSE
712 );
713
714 END check_valid_delete;
715
716
717
718 -- --------------------------------------------------------------------------*
719 -- Procedure: srp_plan_assignment_for_delete
720 -- --------------------------------------------------------------------------*
721 PROCEDURE srp_plan_assignment_for_delete
722 (p_role_id IN cn_roles.role_id%TYPE,
723 p_role_plan_id IN cn_role_plans.role_plan_id%TYPE,
724 p_salesrep_id IN cn_salesreps.salesrep_id%TYPE,
725 p_org_id IN cn_salesreps.org_id%TYPE,
726 x_return_status OUT NOCOPY VARCHAR2,
727 p_loading_status IN VARCHAR2,
728 x_loading_status OUT NOCOPY VARCHAR2 ) IS
729
730 CURSOR l_cur IS
731 SELECT srp_role_id
732 FROM cn_srp_roles
733 WHERE role_id = p_role_id and salesrep_id= p_salesrep_id and org_id=p_org_id;
734
735 l_rec l_cur%ROWTYPE;
736
737 l_return_status VARCHAR2(2000);
738 l_msg_count NUMBER;
739 l_msg_data VARCHAR2(2000);
740 l_srp_plan_assign_id cn_srp_plan_assigns.srp_plan_assign_id%TYPE;
741 l_loading_status VARCHAR2(2000);
742
743 BEGIN
744
745 -- Initialize API return status to success
746 x_return_status := FND_API.G_RET_STS_SUCCESS;
747 x_loading_status := p_loading_status;
748
749 FOR l_rec IN l_cur
750 LOOP
751
752 cn_srp_plan_assigns_pvt.delete_srp_plan_assigns
753 (
754 p_api_version => 1.0,
755 p_init_msg_list => fnd_api.g_false,
756 p_commit => fnd_api.g_false,
757 p_validation_level => fnd_api.g_valid_level_full,
758 x_return_status => l_return_status,
759 x_msg_count => l_msg_count,
760 x_msg_data => l_msg_data,
761 p_srp_role_id => l_rec.srp_role_id,
762 p_role_plan_id => p_role_plan_id,
763 x_loading_status => l_loading_status);
764
765 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
766 x_return_status := l_return_status;
767 x_loading_status := l_loading_status;
768 EXIT;
769 END IF;
770
771 END LOOP;
772 END srp_plan_assignment_for_delete;
773
774
775 -- --------------------------------------------------------------------------*
776 -- Procedure: create_role_pay_groups
777 -- --------------------------------------------------------------------------*
778 PROCEDURE Create_Role_Pay_Groups
779 (
780 p_api_version IN NUMBER,
781 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
782 p_commit IN VARCHAR2 := FND_API.G_FALSE,
783 p_validation_level IN NUMBER :=FND_API.g_valid_level_full,
784 x_return_status OUT NOCOPY VARCHAR2,
785 x_loading_status OUT NOCOPY VARCHAR2,
786 x_msg_count OUT NOCOPY NUMBER,
787 x_msg_data OUT NOCOPY VARCHAR2,
788 p_role_pay_groups_rec IN OUT NOCOPY role_pay_groups_rec_type
789 ) IS
790
791 l_api_name CONSTANT VARCHAR2(30) := 'Create_Role_Pay_Groups';
792 l_api_version CONSTANT NUMBER := 1.0;
793 l_role_pay_group_id cn_role_pay_groups.role_pay_group_id%TYPE;
794 l_role_id cn_roles.role_id%TYPE;
795 l_pay_group_id cn_pay_groups.pay_group_id%TYPE;
796 l_null_date CONSTANT DATE := to_date('31-12-9999','DD-MM-YYYY');
797 l_loading_status VARCHAR2(100);
798
799 -- Declaration for user hooks
800
801 l_OAI_array JTF_USR_HKS.oai_data_array_type;
802 l_bind_data_id NUMBER ;
803 l_count NUMBER;
804
805 --CN_Srp_PayGroup_PUB.PayGroup_assign_rec
806
807 CURSOR get_roles (p_salesrep_id cn_salesreps.salesrep_id%TYPE,p_org_id cn_salesreps.org_id%TYPE) IS
808
809 /*
810 SELECT role_id, srp_role_id,start_date, nvl(end_date,l_null_date) end_date,org_id
811 FROM cn_srp_roles
812 WHERE salesrep_id = p_salesrep_id and org_id=p_org_id; */
813
814 --raj reddy for bug fix 8845580
815 SELECT sr.role_id, sr.srp_role_id,sr.start_date, nvl(sr.end_date,l_null_date) end_date,sr.org_id
816 FROM jtf_rs_Salesreps s,
817 cn_srp_roles sr
818 WHERE sr.salesrep_id = s.salesrep_id
819 AND sr.salesrep_id = p_salesrep_id
820 AND sr.org_id = p_org_id
821 AND
822 ((end_Date_active is null)
823 OR (end_Date_active IS NOT NULL AND end_Date_active BETWEEN p_role_pay_groups_rec.start_date AND p_role_pay_groups_rec.end_date));
824
825 CURSOR get_role_plans(p_role_id cn_roles.role_id%TYPE,p_org_id cn_role_plans.org_id%TYPE) IS
826 SELECT role_plan_id
827 FROM cn_role_plans
828 WHERE role_id = p_role_id and org_id=p_org_id;
829
830 CURSOR get_plan_assigns
831 (p_role_id NUMBER,
832 p_salesrep_id NUMBER,
833 p_org_id NUMBER) IS
834 SELECT comp_plan_id,
835 start_date,
836 end_date
837 FROM cn_srp_plan_assigns
838 WHERE role_id = p_role_id
839 AND salesrep_id = p_salesrep_id and org_id=p_org_id;
840
841 /*
842 CURSOR l_srp_cur(l_role_id cn_roles.role_id%TYPE,l_org_id cn_srp_roles.org_id%TYPE) IS
843 SELECT srp_role_id,salesrep_id,start_date,end_date,org_id
844 FROM cn_srp_roles WHERE role_id = l_role_id and org_id=l_org_id;
845 */
846 --raj reddy for bug fix 8845580
847 CURSOR l_srp_cur(l_role_id cn_roles.role_id%TYPE,l_org_id cn_srp_roles.org_id%TYPE) IS
848 SELECT sr.srp_role_id,sr.salesrep_id,sr.start_date,sr.end_date,sr.org_id
849 FROM jtf_rs_Salesreps s,
850 cn_srp_roles sr
851 WHERE sr.salesrep_id = s.salesrep_id
852 AND sr.role_id = l_role_id
853 AND sr.org_id = l_org_id
854 AND
855 ((end_Date_active is null)
856 OR (end_Date_active IS NOT NULL AND end_Date_active
857 BETWEEN p_role_pay_groups_rec.start_date AND p_role_pay_groups_rec.end_date));
858
859
860 l_role_pg_rec role_pay_groups_rec_type;
861 l_rec l_srp_cur%ROWTYPE;
862 l_lock_flag VARCHAR2(1);
863 l_srp_pay_group_id cn_srp_pay_groups.srp_pay_group_id%TYPE;
864 l_object_version_number cn_srp_pay_groups.object_version_number%TYPE;
865 l_start_date DATE;
866 l_end_date DATE;
867
868 BEGIN
869 -- Standard Start of API savepoint
870 SAVEPOINT Create_Role_Pay_Groups;
871
872 -- Standard call to check for call compatibility.
873 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
874 p_api_version ,
875 l_api_name ,
876 G_PKG_NAME )
877 THEN
878 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
879 END IF;
880
881 -- Initialize message list if p_init_msg_list is set to TRUE.
882 IF FND_API.to_Boolean( p_init_msg_list ) THEN
883 FND_MSG_PUB.initialize;
884 END IF;
885
886 -- Initialize API return status to success
887 x_return_status := FND_API.G_RET_STS_SUCCESS;
888 x_loading_status := 'CN_INSERTED';
889
890 -- Assign the parameter to a local variable to be passed to Pre, Post
891 -- and Business APIs
892 l_role_pg_rec := p_role_pay_groups_rec;
893
894 l_loading_status := x_loading_status;
895
896 -- Start of API body
897 check_valid_insert
898 ( x_return_status => x_return_status,
899 x_msg_count => x_msg_count,
900 x_msg_data => x_msg_data,
901 p_role_pay_groups_rec => p_role_pay_groups_rec,
902 x_role_id => l_role_id,
903 x_pay_group_id => l_pay_group_id,
904 p_loading_status => l_loading_status, -- in
905 x_loading_status => x_loading_status -- out
906 );
907
908 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
909 RAISE FND_API.G_EXC_ERROR ;
910 END IF;
911 l_role_pay_group_id := p_role_pay_groups_rec.role_pay_group_id;
912
913 cn_role_pay_groups_pkg.INSERT_ROW
914 (
915 x_rowid => G_ROWID
916 ,x_role_pay_group_id => l_role_pay_group_id
917 ,x_role_id => l_role_id
918 ,x_pay_group_id => l_pay_group_id
919 ,x_start_date => p_role_pay_groups_rec.start_date
920 ,x_end_date => p_role_pay_groups_rec.end_date
921 ,x_attribute_category => p_role_pay_groups_rec.ATTRIBUTE_CATEGORY
922 ,x_attribute1 => p_role_pay_groups_rec.ATTRIBUTE1
923 ,x_attribute2 => p_role_pay_groups_rec.ATTRIBUTE2
924 ,x_attribute3 => p_role_pay_groups_rec.ATTRIBUTE3
925 ,x_attribute4 => p_role_pay_groups_rec.ATTRIBUTE4
926 ,x_attribute5 => p_role_pay_groups_rec.ATTRIBUTE5
927 ,x_attribute6 => p_role_pay_groups_rec.ATTRIBUTE6
928 ,x_attribute7 => p_role_pay_groups_rec.ATTRIBUTE7
929 ,x_attribute8 => p_role_pay_groups_rec.ATTRIBUTE8
930 ,x_attribute9 => p_role_pay_groups_rec.ATTRIBUTE9
931 ,x_attribute10 => p_role_pay_groups_rec.ATTRIBUTE10
932 ,x_attribute11 => p_role_pay_groups_rec.ATTRIBUTE11
933 ,x_attribute12 => p_role_pay_groups_rec.ATTRIBUTE12
934 ,x_attribute13 => p_role_pay_groups_rec.ATTRIBUTE13
935 ,x_attribute14 => p_role_pay_groups_rec.ATTRIBUTE14
936 ,x_attribute15 => p_role_pay_groups_rec.ATTRIBUTE15
937 ,x_created_by => g_created_by
938 ,x_creation_date => g_creation_date
939 ,x_last_update_login => g_last_update_login
940 ,x_last_update_date => g_last_update_date
941 ,x_last_updated_by => g_last_updated_by
942 ,x_org_id => p_role_pay_groups_rec.ORG_ID
943 ,x_object_version_number => p_role_pay_groups_rec.object_version_number);
944
945 FOR l_rec IN l_srp_cur(l_role_id,p_role_pay_groups_rec.org_id) LOOP
946 SAVEPOINT create_srp_pay_groups;
947 -- Inserted cn_api.date_range_overlap and get_date_range_intersect to check for date intersection
948
949 IF cn_api.date_range_overlap
950 (a_start_date => l_rec.start_date,
951 a_end_date => l_rec.end_date,
952 b_start_date => p_role_pay_groups_rec.start_date,
953 b_end_date => p_role_pay_groups_rec.end_date)
954 THEN
955 /*
956 -- l_rec dates are S-R intersect R-PG dates
957 cn_api.get_date_range_intersect
958 (a_start_date => l_rec.start_date,
959 a_end_date => l_rec.end_date,
960 b_start_date => p_role_pay_groups_rec.start_date,
961 b_end_date => p_role_pay_groups_rec.end_date,
962 x_start_date => l_start_date,
963 x_end_date => l_end_date);
964 */
965
966
967 /*According to discussions with PMs, the intersection dates for
968 mass assignment must be
969 1) Resource Role Assignment dates
970 2) Resource start and end dates
971 3) Role Pay Group Assignment dates
972 4) Pay Group start date and end dates
973 */
974 get_masgn_date_intersect( -- Bug fix 5511911. sjustina
975 p_srp_role_id => l_rec.srp_role_id,
976 p_role_pay_group_id => p_role_pay_groups_rec.role_pay_group_id,
977 x_start_date => l_start_date,
978 x_end_date => l_end_date);
979 l_rec.start_date := l_start_date;
980 l_rec.end_date := l_end_date;
981
982 -- Check if the current assignment dates fit within the
983 -- effectivity of the pay group.
984 -- if S-PG end date is null and PG end date is not null,
985 -- that is OK
986 SELECT COUNT(1)
987 INTO l_count
988 FROM cn_pay_groups
989 WHERE (l_rec.start_date NOT BETWEEN start_date AND end_date OR
990 (l_rec.end_date IS NOT NULL AND
991 l_rec.end_date NOT BETWEEN start_date AND end_date))
992 AND pay_group_id = l_pay_group_id;
993
994 IF l_count <> 0 THEN
995 GOTO end_create_srp_pay_groups;
996 END IF;
997
998 -- If existing any same role_id in cn_srp_pay_groups THEN
999 -- check no overlap
1000 SELECT count(1) into l_count
1001 FROM cn_srp_pay_groups
1002 WHERE salesrep_id = l_rec.salesrep_id
1003 AND org_id = p_role_pay_groups_rec.ORG_ID
1004 AND Greatest(start_date, l_rec.start_date) <=
1005 Least(Nvl(end_date, l_null_date),
1006 Nvl(l_rec.end_date, l_null_date));
1007
1008 IF l_count = 0 THEN
1009 l_lock_flag := 'N';
1010
1011 SELECT cn_srp_pay_groups_s.NEXTVAL
1012 INTO l_srp_pay_group_id
1013 FROM dual;
1014
1015 CN_SRP_Pay_Groups_Pkg.Begin_Record
1016 (x_operation => 'INSERT',
1017 x_srp_pay_group_id => l_srp_pay_group_id,
1018 x_salesrep_id => l_rec.salesrep_id,
1019 x_pay_group_id => l_pay_group_id,
1020 x_start_date => l_rec.start_date,
1021 x_end_date => l_rec.end_date,
1022 x_lock_flag => l_lock_flag,
1023 x_role_pay_group_id => l_role_pay_group_id,
1024 x_org_id => p_role_pay_groups_rec.org_id,
1025 x_attribute_category =>p_role_pay_groups_rec.attribute_category,
1026 x_attribute1 => p_role_pay_groups_rec.attribute1,
1027 x_attribute2 => p_role_pay_groups_rec.attribute2,
1028 x_attribute3 => p_role_pay_groups_rec.attribute3,
1029 x_attribute4 => p_role_pay_groups_rec.attribute4,
1030 x_attribute5 => p_role_pay_groups_rec.attribute5,
1031 x_attribute6 => p_role_pay_groups_rec.attribute6,
1032 x_attribute7 => p_role_pay_groups_rec.attribute7,
1033 x_attribute8 => p_role_pay_groups_rec.attribute8,
1034 x_attribute9 => p_role_pay_groups_rec.attribute9,
1035 x_attribute10 => p_role_pay_groups_rec.attribute10,
1036 x_attribute11 => p_role_pay_groups_rec.attribute11,
1037 x_attribute12 => p_role_pay_groups_rec.attribute12,
1038 x_attribute13 => p_role_pay_groups_rec.attribute13,
1039 x_attribute14 => p_role_pay_groups_rec.attribute14,
1040 x_attribute15 => p_role_pay_groups_rec.attribute15,
1041 x_last_update_date => g_last_update_date,
1042 x_last_updated_by => g_last_updated_by,
1043 x_creation_date => g_creation_date,
1044 x_created_by => g_created_by,
1045 x_last_update_login => g_last_update_login,
1046 x_object_version_number => l_object_version_number);
1047
1048 END IF;
1049 END IF; -- if overlap
1050
1051 -- Call srp-plan assignment API to insert
1052 -- Call cn_srp_periods_pvt api to affect the records in cn_srp_periods
1053
1054 FOR roles IN get_roles(l_rec.salesrep_id,l_rec.org_id) LOOP
1055 IF ((roles.start_date <= l_rec.start_date AND roles.end_date >=
1056 l_rec.start_date) OR
1057 (roles.start_date <= l_rec.end_date AND roles.end_date >=
1058 l_rec.end_date ) ) THEN
1059
1060 FOR role_plans IN get_role_plans(roles.role_id,roles.org_id) LOOP
1061 cn_srp_plan_assigns_pvt.Update_Srp_Plan_Assigns
1062 (
1063 p_api_version => 1.0,
1064 p_init_msg_list => FND_API.G_FALSE,
1065 p_commit => FND_API.G_FALSE,
1066 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1067 x_return_status => x_return_status,
1068 x_msg_count => x_msg_count,
1069 x_msg_data => x_msg_data,
1070 p_srp_role_id => roles.srp_role_id,
1071 p_role_plan_id => role_plans.role_plan_id,
1072 x_loading_status => x_loading_status );
1073 IF ( x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1074 RAISE fnd_api.g_exc_error;
1075 END IF;
1076
1077 END LOOP;
1078 END IF;
1079
1080 FOR plans IN get_plan_assigns(roles.role_id,l_rec.salesrep_id,l_rec.org_id) LOOP
1081 -- Added to check the start_date and end_date of plan assignment, populate the intersection
1082 -- part with the pay group assignment date.
1083
1084 IF nvl(plans.end_date,l_null_date) > nvl(l_rec.end_date,l_null_date) THEN
1085 plans.end_date := l_rec.end_date;
1086 END IF;
1087
1088 IF plans.start_date < l_rec.start_date THEN
1089 plans.start_date := l_rec.start_date;
1090 END IF;
1091
1092 IF nvl(plans.end_date, l_null_date) > plans.start_date THEN
1093
1094 cn_srp_periods_pvt.create_srp_periods
1095 ( p_api_version => p_api_version,
1096 p_init_msg_list => fnd_api.g_false,
1097 p_commit => fnd_api.g_false,
1098 p_validation_level => p_validation_level,
1099 x_return_status => x_return_status,
1100 x_msg_count => x_msg_count,
1101 x_msg_data => x_msg_data,
1102 p_salesrep_id => l_rec.salesrep_id,
1103 p_role_id => roles.role_id,
1104 p_comp_plan_id => plans.comp_plan_id,
1105 p_start_date => plans.start_date,
1106 p_end_date => plans.end_date,
1107 x_loading_status => x_loading_status);
1108 IF ( x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1109 RAISE fnd_api.g_exc_error;
1110 END IF;
1111
1112 END IF;
1113 END LOOP;
1114 END LOOP;
1115 <<end_create_srp_pay_groups>>
1116 NULL;
1117 END LOOP;
1118
1119
1120 -- End of API body
1121 -- Standard check of p_commit.
1122
1123 IF FND_API.To_Boolean( p_commit ) THEN
1124 COMMIT WORK;
1125 END IF;
1126
1127
1128 -- Standard call to get message count and if count is 1, get message info.
1129
1130 FND_MSG_PUB.Count_And_Get
1131 (
1132 p_count => x_msg_count ,
1133 p_data => x_msg_data ,
1134 p_encoded => FND_API.G_FALSE
1135 );
1136
1137 EXCEPTION
1138 WHEN FND_API.G_EXC_ERROR THEN
1139 ROLLBACK TO Create_Role_Pay_Groups;
1140 x_return_status := FND_API.G_RET_STS_ERROR ;
1141 FND_MSG_PUB.Count_And_Get
1142 (
1143 p_count => x_msg_count ,
1144 p_data => x_msg_data ,
1145 p_encoded => FND_API.G_FALSE
1146 );
1147 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1148 ROLLBACK TO Create_Role_Pay_Groups;
1149 x_loading_status := 'UNEXPECTED_ERR';
1150 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1151 FND_MSG_PUB.Count_And_Get
1152 (
1153 p_count => x_msg_count ,
1154 p_data => x_msg_data ,
1155 p_encoded => FND_API.G_FALSE
1156 );
1157 WHEN OTHERS THEN
1158 ROLLBACK TO Create_Role_Pay_Groups;
1159 x_loading_status := 'UNEXPECTED_ERR';
1160 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1161 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1162 THEN
1163 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
1164 END IF;
1165 FND_MSG_PUB.Count_And_Get
1166 (
1167 p_count => x_msg_count ,
1168 p_data => x_msg_data ,
1169 p_encoded => FND_API.G_FALSE
1170 );
1171
1172 END Create_Role_Pay_Groups;
1173
1174
1175
1176
1177 -- --------------------------------------------------------------------------*
1178 -- Procedure: Delete_Role_Pay_Groups
1179 -- --------------------------------------------------------------------------*
1180 PROCEDURE Delete_Role_Pay_Groups
1181 ( p_api_version IN NUMBER ,
1182 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
1183 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
1184 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1185 x_return_status OUT NOCOPY VARCHAR2 ,
1186 x_loading_status OUT NOCOPY VARCHAR2 ,
1187 x_msg_count OUT NOCOPY NUMBER ,
1188 x_msg_data OUT NOCOPY VARCHAR2 ,
1189 p_role_pay_groups_rec IN OUT NOCOPY role_pay_groups_rec_type
1190 ) IS
1191
1192 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Role_Pay_Groups';
1193 l_api_version CONSTANT NUMBER := 1.0;
1194 l_role_pay_group_id cn_role_pay_groups.role_pay_group_id%TYPE;
1195 l_role_id cn_roles.role_id%TYPE;
1196 l_pay_group_id cn_pay_groups.pay_group_id%TYPE;
1197 l_org_id cn_pay_groups.org_id%TYPE;
1198 -- Declaration for user hooks
1199 l_rec role_pay_groups_rec_type;
1200 l_OAI_array JTF_USR_HKS.oai_data_array_type;
1201 l_bind_data_id NUMBER ;
1202 l_null_date CONSTANT DATE := to_date('31-12-9999','DD-MM-YYYY');
1203 l_loading_status VARCHAR2(100);
1204 l_count NUMBER(15);
1205 l_start_date DATE;
1206 l_end_date DATE;
1207
1208
1209 CURSOR get_roles (p_salesrep_id cn_salesreps.salesrep_id%TYPE,p_org_id cn_salesreps.org_id%TYPE) IS
1210 SELECT role_id, srp_role_id,start_date, nvl(end_date,l_null_date) end_date,org_id
1211 FROM cn_srp_roles
1212 WHERE salesrep_id = p_salesrep_id and org_id=p_org_id;
1213
1214 --Changed the cursor to fetch role_plans for the role_id passed
1215
1216 CURSOR get_role_plans(l_role_id cn_roles.role_id%TYPE,l_org_id cn_role_plans.org_id%TYPE) IS
1217 SELECT role_plan_id,role_id
1218 FROM cn_role_plans
1219 WHERE role_id =l_role_id and org_id=l_org_id;
1220
1221 CURSOR get_plan_assigns
1222 (p_role_id NUMBER,
1223 p_salesrep_id NUMBER,
1224 p_org_id NUMBER) IS
1225 SELECT comp_plan_id,
1226 start_date,
1227 end_date,
1228 org_id
1229 FROM cn_srp_plan_assigns
1230 WHERE role_id = p_role_id
1231 AND salesrep_id = p_salesrep_id
1232 and org_id=p_org_id;
1233
1234
1235
1236 CURSOR get_salesreps(l_role_id NUMBER,l_pay_group_id cn_pay_groups.pay_group_id%TYPE,l_org_id cn_pay_groups.org_id%TYPE) IS
1237 SELECT srp_role_id,salesrep_id,start_date,end_date,org_id
1238 FROM cn_srp_roles
1239 WHERE role_id = l_role_id
1240 AND salesrep_id IN (select salesrep_id from cn_srp_pay_groups where pay_group_id=l_pay_group_id)
1241 and org_id=l_org_id;
1242
1243
1244
1245
1246 BEGIN
1247 -- Standard Start of API savepoint
1248
1249 SAVEPOINT delete_role_pay_groups;
1250
1251 -- Standard call to check for call compatibility.
1252
1253 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
1254 p_api_version ,
1255 l_api_name ,
1256 G_PKG_NAME )
1257 THEN
1258 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1259 END IF;
1260
1261 -- Initialize message list if p_init_msg_list is set to TRUE.
1262 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1263 FND_MSG_PUB.initialize;
1264 END IF;
1265
1266 -- Initialize API return status to success
1267 x_return_status := FND_API.G_RET_STS_SUCCESS;
1268 x_loading_status := 'CN_DELETED';
1269
1270
1271 -- Assign the parameter to a local variable to be passed to Pre, Post
1272 -- and Business APIs
1273 l_rec := p_role_pay_groups_rec;
1274
1275
1276 l_loading_status := x_loading_status;
1277
1278 -- Start of API body
1279 check_valid_delete
1280 ( x_return_status => x_return_status,
1281 x_msg_count => x_msg_count,
1282 x_msg_data => x_msg_data,
1283 p_role_pay_groups_rec => p_role_pay_groups_rec,
1284 x_role_pay_group_id => l_role_pay_group_id,
1285 p_loading_status => l_loading_status, -- in
1286 x_loading_status => x_loading_status -- out
1287 );
1288
1289 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
1290 RAISE FND_API.G_EXC_ERROR ;
1291 ELSE
1292 -- delete_row
1293
1294 l_role_id := cn_api.get_role_id(p_role_pay_groups_rec.role_name);
1295 l_pay_group_id := get_pg_id(p_role_pay_groups_rec.pay_groups_name,p_role_pay_groups_rec.org_id);
1296 l_org_id := p_role_pay_groups_rec.org_id;
1297 --check this
1298 cn_role_pay_groups_pkg.delete_row(x_role_pay_group_id => l_role_pay_group_id);
1299
1300 FOR salesrep IN get_salesreps(l_role_id,l_pay_group_id,l_org_id)
1301
1302 LOOP
1303 SAVEPOINT delete_srp_pay_groups;
1304 --Included a where condition to delete the srp_pay_group record for the role_pay_group_id passed
1305 delete from cn_srp_pay_groups where salesrep_id=salesrep.salesrep_id
1306 AND role_pay_group_id=l_role_pay_group_id
1307 and org_id = l_org_id
1308 AND lock_flag='N' AND pay_group_id=l_pay_group_id AND (start_date between p_role_pay_groups_rec.start_date AND nvl(p_role_pay_groups_rec.end_date,l_null_date)) AND
1309 (nvl(end_date,l_null_date) between p_role_pay_groups_rec.start_date AND nvl(p_role_pay_groups_rec.end_date,l_null_date))
1310 AND NOT EXISTS (SELECT 1 FROM cn_payment_worksheets W, cn_period_statuses prd, cn_payruns prun
1311 WHERE w.salesrep_id = salesrep.salesrep_id
1312 AND prun.pay_period_id = prd.period_id
1313 AND prun.payrun_id = w.payrun_id
1314 AND prun.pay_group_id = l_pay_group_id
1315 and prun.org_id = l_org_id
1316 AND ((prd.start_date BETWEEN p_role_pay_groups_rec.start_date AND nvl(p_role_pay_groups_rec.end_date,l_null_date)) OR
1317 (prd.end_date between p_role_pay_groups_rec.start_date AND nvl(p_role_pay_groups_rec.end_date,l_null_date)) ));
1318
1319
1320
1321 IF SQL%ROWCOUNT > 0 THEN
1322
1323 FOR role_plans IN get_role_plans(l_role_id,l_org_id)
1324 LOOP
1325 srp_plan_assignment_for_delete
1326 (p_role_id => role_plans.role_id,
1327 p_role_plan_id => role_plans.role_plan_id,
1328 p_salesrep_id => salesrep.salesrep_id,
1329 p_org_id => salesrep.org_id,
1330 x_return_status => x_return_status,
1331 p_loading_status => l_loading_status,
1332 x_loading_status => x_loading_status);
1333
1334 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
1335 RAISE fnd_api.g_exc_error;
1336 END IF;
1337 END LOOP ;
1338
1339 select count (*),min(start_date),nvl(max(end_date),l_null_date) end_date
1340 INTO l_count,l_start_date,l_end_date
1341 from cn_srp_pay_groups
1342 where salesrep_id = salesrep.salesrep_id and org_id=salesrep.org_id;
1343
1344 -- Bug fix 5200094 vensrini
1345 IF l_count = 0 THEN
1346 FOR roles IN get_roles(salesrep.salesrep_id, salesrep.org_id)
1347 LOOP
1348 FOR role_plans IN get_role_plans(roles.role_id, salesrep.org_id)
1349 LOOP
1350 srp_plan_assignment_for_delete(p_role_id => role_plans.role_id,
1351 p_role_plan_id => role_plans.role_plan_id,
1352 p_salesrep_id => salesrep.salesrep_id,
1353 p_org_id => salesrep.org_id,
1354 x_return_status => x_return_status,
1355 p_loading_status => l_loading_status,
1356 x_loading_status => x_loading_status);
1357
1358 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
1359 RAISE FND_API.G_EXC_ERROR;
1360 END IF;
1361 END LOOP ;
1362 END LOOP;
1363 END IF;
1364 -- Bug fix 5200094 vensrini
1365
1366 IF l_count > 0 THEN
1367
1368 FOR roles IN get_roles(salesrep.salesrep_id,salesrep.org_id)
1369 LOOP
1370
1371 IF ((roles.start_date <= salesrep.start_date AND roles.end_date >=
1372 salesrep.start_date) OR
1373 (roles.start_date <= salesrep.end_date AND roles.end_date >=
1374 salesrep.end_date ) ) THEN
1375
1376 FOR role_plans IN get_role_plans(roles.role_id,roles.org_id)
1377 LOOP
1378 cn_srp_plan_assigns_pvt.Update_Srp_Plan_Assigns
1379 (
1380 p_api_version => 1.0,
1381 p_init_msg_list => FND_API.G_FALSE,
1382 p_commit => FND_API.G_FALSE,
1383 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1384 x_return_status => x_return_status,
1385 x_msg_count => x_msg_count,
1386 x_msg_data => x_msg_data,
1387 p_srp_role_id => roles.srp_role_id,--changed this to support my new change(Harlen.Renu)
1388 p_role_plan_id => role_plans.role_plan_id,
1389 x_loading_status => x_loading_status );
1390 IF ( x_return_status <> FND_API.g_ret_sts_success) THEN
1391 RAISE fnd_api.g_exc_error;
1392 END IF;
1393 END LOOP;
1394 END IF;
1395
1396 FOR plans IN get_plan_assigns(roles.role_id,salesrep.salesrep_id,salesrep.org_id)
1397 LOOP
1398
1399 -- Added to check the start_date and end_date of plan assignment, populate the intersection
1400 -- part with the pay group assignment date.
1401
1402 IF nvl(plans.end_date,l_null_date) > nvl(l_end_date,l_null_date) THEN
1403 plans.end_date := l_end_date;
1404 END IF;
1405
1406 IF plans.start_date < l_start_date THEN
1407 plans.start_date := l_start_date;
1408 END IF;
1409
1410 IF nvl(plans.end_date, l_null_date) > plans.start_date THEN
1411
1412 cn_srp_periods_pvt.create_srp_periods
1413 ( p_api_version => p_api_version,
1414 p_init_msg_list => fnd_api.g_false,
1415 p_commit => fnd_api.g_false,
1416 p_validation_level => p_validation_level,
1417 x_return_status => x_return_status,
1418 x_msg_count => x_msg_count,
1419 x_msg_data => x_msg_data,
1420 p_salesrep_id => salesrep.salesrep_id,
1421 p_role_id => roles.role_id,
1422 p_comp_plan_id => plans.comp_plan_id,
1423 p_start_date => plans.start_date,
1424 p_end_date => plans.end_date,
1425 x_loading_status => x_loading_status);
1426 IF ( x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1427 RAISE fnd_api.g_exc_error;
1428 END IF;
1429
1430 END IF;
1431 END LOOP;
1432 END LOOP;
1433 END IF;
1434 END IF;
1435
1436 <<end_srp_pay_groups>>
1437 NULL;
1438 END LOOP;
1439 --Added by Harish
1440 -- if any of the salesreps with this role has any unpaid payment batch for this period,
1441 -- the paygroup mass assignment relationship is severed
1442 -- and is treated like an resource level paygroup assignment.
1443 Update cn_srp_pay_groups_all
1444 set role_pay_group_id = null
1445 where role_pay_group_id = l_role_pay_group_id;
1446 --End
1447
1448 END IF;
1449
1450
1451 -- End of API body
1452
1453 -- Standard check of p_commit.
1454
1455 IF FND_API.To_Boolean( p_commit ) THEN
1456 COMMIT WORK;
1457 END IF;
1458
1459
1460 -- Standard call to get message count and if count is 1, get message info.
1461
1462 FND_MSG_PUB.Count_And_Get
1463 (
1464 p_count => x_msg_count ,
1465 p_data => x_msg_data ,
1466 p_encoded => FND_API.G_FALSE
1467 );
1468
1469 EXCEPTION
1470 WHEN FND_API.G_EXC_ERROR THEN
1471 ROLLBACK TO Delete_Role_Pay_Groups;
1472 x_return_status := FND_API.G_RET_STS_ERROR ;
1473 FND_MSG_PUB.Count_And_Get
1474
1475
1476 (
1477 p_count => x_msg_count ,
1478 p_data => x_msg_data ,
1479 p_encoded => FND_API.G_FALSE
1480 );
1481 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1482 ROLLBACK TO Delete_Role_Pay_Groups;
1483 x_loading_status := 'UNEXPECTED_ERR';
1484 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1485 FND_MSG_PUB.Count_And_Get
1486 (
1487 p_count => x_msg_count ,
1488 p_data => x_msg_data ,
1489 p_encoded => FND_API.G_FALSE
1490 );
1491 WHEN OTHERS THEN
1492 ROLLBACK TO Delete_Role_Pay_Groups;
1493 x_loading_status := 'UNEXPECTED_ERR';
1494 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1495 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1496 THEN
1497 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
1498 END IF;
1499 FND_MSG_PUB.Count_And_Get
1500 (
1501 p_count => x_msg_count ,
1502 p_data => x_msg_data ,
1503 p_encoded => FND_API.G_FALSE
1504 );
1505 END Delete_Role_Pay_Groups;
1506
1507
1508 END CN_ROLE_PAY_GROUPS_PVT;