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