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