[Home] [Help]
PACKAGE BODY: APPS.PSB_PAY_ELEMENT_RATES_PVT
Source
1 PACKAGE BODY PSB_PAY_ELEMENT_RATES_PVT AS
2 /* $Header: PSBVRTSB.pls 120.2 2005/07/13 11:29:25 shtripat ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):= 'PSB_PAY_ELEMENT_RATES_PVT';
5
6 TYPE g_elemrates_rec_type IS RECORD
7 ( pay_element_rate_id NUMBER,
8 pay_element_id NUMBER,
9 pay_element_option_id NUMBER,
10 effective_start_date DATE,
11 effective_end_date DATE,
12 worksheet_id NUMBER,
13 element_value_type VARCHAR2(2),
14 element_value NUMBER,
15 pay_basis VARCHAR2(15),
16 formula_id NUMBER,
17 maximum_value NUMBER,
18 mid_value NUMBER,
19 minimum_value NUMBER,
20 currency_code VARCHAR2(10),
21 proper_subset VARCHAR2(1) );
22
23 TYPE g_elemrates_tbl_type IS TABLE OF g_elemrates_rec_type
24 INDEX BY BINARY_INTEGER;
25
26 g_element_rates g_elemrates_tbl_type;
27 g_num_element_rates NUMBER;
28
29 /* ----------------------------------------------------------------------- */
30
31 PROCEDURE INSERT_ROW
32 ( p_api_version IN NUMBER,
33 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
34 p_commit IN VARCHAR2 := FND_API.G_FALSE,
35 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
36 p_return_status OUT NOCOPY VARCHAR2,
37 p_msg_count OUT NOCOPY NUMBER,
38 p_msg_data OUT NOCOPY VARCHAR2,
39 --
40 P_PAY_ELEMENT_RATE_ID in NUMBER,
41 P_PAY_ELEMENT_OPTION_ID in NUMBER,
42 P_PAY_ELEMENT_ID in NUMBER,
43 P_EFFECTIVE_START_DATE in DATE,
44 P_EFFECTIVE_END_DATE in DATE,
45 P_WORKSHEET_ID in NUMBER,
46 P_ELEMENT_VALUE_TYPE in VARCHAR2,
47 P_ELEMENT_VALUE in NUMBER,
48 P_PAY_BASIS in VARCHAR2,
49 P_FORMULA_ID in NUMBER,
50 P_MAXIMUM_VALUE in NUMBER,
51 P_MID_VALUE in NUMBER,
52 P_MINIMUM_VALUE in NUMBER,
53 P_CURRENCY_CODE IN VARCHAR2,
54 P_LAST_UPDATE_DATE in DATE,
55 P_LAST_UPDATED_BY in NUMBER,
56 P_LAST_UPDATE_LOGIN in NUMBER,
57 P_CREATED_BY in NUMBER,
58 P_CREATION_DATE in DATE
59 ) IS
60
61 l_api_name CONSTANT VARCHAR2(30) := 'INSERT_ROW';
62 l_api_version CONSTANT NUMBER := 1.0;
63 l_row_id varchar2(40);
64 --
65 cursor c1 is
66 select ROWID from psb_pay_element_rates
67 where pay_element_rate_id = p_pay_element_rate_id ;
68
69 BEGIN
70
71 -- Standard Start of API savepoint
72
73 SAVEPOINT INSERT_ROW_PVT;
74
75 -- Standard call to check for call compatibility.
76
77 if not FND_API.Compatible_API_Call (l_api_version,
78 p_api_version,
79 l_api_name,
80 G_PKG_NAME)
81 then
82 raise FND_API.G_EXC_UNEXPECTED_ERROR;
83 end if;
84
85 -- Initialize message list if p_init_msg_list is set to TRUE.
86
87 if FND_API.to_Boolean (p_init_msg_list) then
88 FND_MSG_PUB.initialize;
89 end if;
90
91 -- Initialize API return status to success
92
93 p_return_status := FND_API.G_RET_STS_SUCCESS;
94
95
96 -- API body
97 INSERT INTO psb_pay_element_rates
98 (
99 PAY_ELEMENT_RATE_ID ,
100 PAY_ELEMENT_ID ,
101 PAY_ELEMENT_OPTION_ID ,
102 EFFECTIVE_START_DATE ,
103 EFFECTIVE_END_DATE ,
104 WORKSHEET_ID ,
105 ELEMENT_VALUE_TYPE ,
106 ELEMENT_VALUE ,
107 PAY_BASIS ,
108 FORMULA_ID ,
109 MAXIMUM_VALUE ,
110 MID_VALUE ,
111 MINIMUM_VALUE ,
112 CURRENCY_CODE ,
113 LAST_UPDATE_DATE ,
114 LAST_UPDATED_BY ,
115 LAST_UPDATE_LOGIN ,
116 CREATED_BY ,
117 CREATION_DATE
118 )
119 VALUES
120 (
121 P_PAY_ELEMENT_RATE_ID ,
122 P_PAY_ELEMENT_ID ,
123 P_PAY_ELEMENT_OPTION_ID ,
124 P_EFFECTIVE_START_DATE ,
125 P_EFFECTIVE_END_DATE ,
126 P_WORKSHEET_ID ,
127 P_ELEMENT_VALUE_TYPE ,
128 P_ELEMENT_VALUE ,
129 P_PAY_BASIS ,
130 P_FORMULA_ID ,
131 P_MAXIMUM_VALUE ,
132 P_MID_VALUE ,
133 P_MINIMUM_VALUE ,
134 P_CURRENCY_CODE ,
135 P_LAST_UPDATE_DATE ,
136 P_LAST_UPDATED_BY ,
137 P_LAST_UPDATE_LOGIN ,
138 P_CREATED_BY ,
139 P_CREATION_DATE
140 );
141
142 open c1;
143 fetch c1 into l_row_id;
144 if (c1%notfound) then
145 close c1;
146 raise no_data_found;
147 end if;
148 -- End of API body.
149
150 -- Standard check of p_commit.
151
152 if FND_API.to_Boolean (p_commit) then
153 commit work;
154 end if;
155
156 -- Standard call to get message count and if count is 1, get message info.
157
158 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
159 p_data => p_msg_data);
160
161 EXCEPTION
162
163 when FND_API.G_EXC_ERROR then
164
165 rollback to INSERT_ROW_PVT;
166
167 p_return_status := FND_API.G_RET_STS_ERROR;
168
169 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
170 p_data => p_msg_data);
171
172
173 when FND_API.G_EXC_UNEXPECTED_ERROR then
174
175 rollback to INSERT_ROW_PVT;
176
177 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
178
179 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
180 p_data => p_msg_data);
181
182
183 when OTHERS then
184
185 rollback to INSERT_ROW_PVT;
186
187 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
188
189 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) then
190
191 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
192 l_api_name);
193 end if;
194
195 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
196 p_data => p_msg_data);
197
198 END INSERT_ROW;
199
200 /* ----------------------------------------------------------------------- */
201
202 PROCEDURE UPDATE_ROW
203 ( p_api_version IN NUMBER,
204 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
205 p_commit IN VARCHAR2 := FND_API.G_FALSE,
206 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
207 p_return_status OUT NOCOPY VARCHAR2,
208 p_msg_count OUT NOCOPY NUMBER,
209 p_msg_data OUT NOCOPY VARCHAR2,
210 --
211 P_PAY_ELEMENT_RATE_ID in NUMBER,
212 P_EFFECTIVE_START_DATE in DATE := FND_API.G_MISS_DATE,
213 P_EFFECTIVE_END_DATE in DATE := FND_API.G_MISS_DATE,
214 P_ELEMENT_VALUE_TYPE in VARCHAR2,
215 P_ELEMENT_VALUE in NUMBER,
216 P_PAY_BASIS in VARCHAR2,
217 P_FORMULA_ID in NUMBER,
218 P_MAXIMUM_VALUE in NUMBER,
219 P_MID_VALUE in NUMBER,
220 P_MINIMUM_VALUE in NUMBER,
221 P_LAST_UPDATE_DATE in DATE,
222 P_LAST_UPDATED_BY in NUMBER,
223 P_LAST_UPDATE_LOGIN in NUMBER
224 ) IS
225
226 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_ROW';
227 l_api_version CONSTANT NUMBER := 1.0;
228
229 BEGIN
230
231 -- Standard Start of API savepoint
232
233 SAVEPOINT UPDATE_ROW_PVT;
234
235 -- Standard call to check for call compatibility.
236
237 if not FND_API.Compatible_API_Call (l_api_version,
238 p_api_version,
239 l_api_name,
240 G_PKG_NAME)
241 then
242 raise FND_API.G_EXC_UNEXPECTED_ERROR;
243 end if;
244
245 -- Initialize message list if p_init_msg_list is set to TRUE.
246
247 if FND_API.to_Boolean (p_init_msg_list) then
248 FND_MSG_PUB.initialize;
249 end if;
250
251 -- Initialize API return status to success
252
253 p_return_status := FND_API.G_RET_STS_SUCCESS;
254
255 -- API body
256 UPDATE psb_pay_element_rates SET
257 EFFECTIVE_START_DATE = DECODE(P_EFFECTIVE_START_DATE, FND_API.G_MISS_DATE, EFFECTIVE_START_DATE, P_EFFECTIVE_START_DATE),
258 EFFECTIVE_END_DATE = DECODE(P_EFFECTIVE_END_DATE, FND_API.G_MISS_DATE, EFFECTIVE_END_DATE, P_EFFECTIVE_END_DATE),
259 ELEMENT_VALUE_TYPE = P_ELEMENT_VALUE_TYPE ,
260 ELEMENT_VALUE = P_ELEMENT_VALUE ,
261 PAY_BASIS = P_PAY_BASIS ,
262 FORMULA_ID = P_FORMULA_ID ,
263 MAXIMUM_VALUE = P_MAXIMUM_VALUE ,
264 MID_VALUE = P_MID_VALUE ,
265 MINIMUM_VALUE = P_MINIMUM_VALUE ,
266 LAST_UPDATE_DATE = P_LAST_UPDATE_DATE ,
267 LAST_UPDATED_BY = P_LAST_UPDATED_BY ,
268 LAST_UPDATE_LOGIN = P_LAST_UPDATE_LOGIN
269 WHERE PAY_ELEMENT_RATE_ID = P_PAY_ELEMENT_RATE_ID;
270
271 if (SQL%NOTFOUND) then
272 RAISE NO_DATA_FOUND;
273 end if;
274
275 -- End of API body.
276
277 -- Standard check of p_commit.
278
279 if FND_API.to_Boolean (p_commit) then
280 commit work;
281 end if;
282
283 -- Standard call to get message count and if count is 1, get message info.
284
285 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
286 p_data => p_msg_data);
287
288 EXCEPTION
289
290 when FND_API.G_EXC_ERROR then
291
292 rollback to UPDATE_ROW_PVT;
293
294 p_return_status := FND_API.G_RET_STS_ERROR;
295
296 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
297 p_data => p_msg_data);
298
299
300 when FND_API.G_EXC_UNEXPECTED_ERROR then
301
302 rollback to UPDATE_ROW_PVT;
303
304 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
305
306 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
307 p_data => p_msg_data);
308
309
310 when OTHERS then
311
312 rollback to UPDATE_ROW_PVT;
313
314 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
315
316 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) then
317
318 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
319 l_api_name);
320 end if;
321
322 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
323 p_data => p_msg_data);
324
325 END UPDATE_ROW;
326
327 /* ----------------------------------------------------------------------- */
328
329 PROCEDURE DELETE_ROW
330 ( p_api_version IN NUMBER,
331 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
332 p_commit IN VARCHAR2 := FND_API.G_FALSE,
333 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
334 p_return_status OUT NOCOPY VARCHAR2,
335 p_msg_count OUT NOCOPY NUMBER,
336 p_msg_data OUT NOCOPY VARCHAR2,
337 --
338 P_PAY_ELEMENT_RATE_ID in NUMBER
339 ) IS
340
341 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_ROW';
342 l_api_version CONSTANT NUMBER := 1.0;
343
344 BEGIN
345
346 -- Standard Start of API savepoint
347
348 SAVEPOINT DELETE_ROW_PVT;
349
350 -- Standard call to check for call compatibility.
351
352 if not FND_API.Compatible_API_Call (l_api_version,
353 p_api_version,
354 l_api_name,
355 G_PKG_NAME)
356 then
357 raise FND_API.G_EXC_UNEXPECTED_ERROR;
358 end if;
359
360 -- Initialize message list if p_init_msg_list is set to TRUE.
361
362 if FND_API.to_Boolean (p_init_msg_list) then
363 FND_MSG_PUB.initialize;
364 end if;
365
366
367 --Delete the record
368 DELETE FROM psb_pay_element_rates
369 WHERE pay_element_rate_id = p_pay_element_rate_id;
370
371
372 if (SQL%NOTFOUND) then
373 RAISE NO_DATA_FOUND;
374 end if;
375
376 -- Standard call to get message count and if count is 1, get message info.
377
378 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
379 p_data => p_msg_data);
380
381 -- Standard check of p_commit.
382
383 if FND_API.to_Boolean (p_commit) then
384 commit work;
385 end if;
386
387
388 EXCEPTION
389
390 when FND_API.G_EXC_ERROR then
391
392 rollback to DELETE_ROW_PVT;
393
394 p_return_status := FND_API.G_RET_STS_ERROR;
395
396 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
397 p_data => p_msg_data);
398
399
400 when FND_API.G_EXC_UNEXPECTED_ERROR then
401
402 rollback to DELETE_ROW_PVT;
403
407 p_data => p_msg_data);
404 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
405
406 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
408
409
410 when OTHERS then
411
412 rollback to DELETE_ROW_PVT;
413
414 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
415
416 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) then
417
418 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
419 l_api_name);
420 end if;
421
422 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
423 p_data => p_msg_data);
424 END DELETE_ROW;
425
426 /* ----------------------------------------------------------------------- */
427
428 PROCEDURE LOCK_ROW(
429 p_api_version IN NUMBER,
430 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
431 p_commit IN VARCHAR2 := FND_API.G_FALSE,
432 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
433 p_return_status OUT NOCOPY VARCHAR2,
434 p_msg_count OUT NOCOPY NUMBER,
435 p_msg_data OUT NOCOPY VARCHAR2,
436 --
437 p_row_locked OUT NOCOPY VARCHAR2,
438 --
439 P_PAY_ELEMENT_RATE_ID in NUMBER,
440 P_PAY_ELEMENT_OPTION_ID in NUMBER,
441 P_PAY_ELEMENT_ID in NUMBER,
442 P_EFFECTIVE_START_DATE in DATE,
443 P_EFFECTIVE_END_DATE in DATE,
444 P_WORKSHEET_ID in NUMBER,
445 P_ELEMENT_VALUE_TYPE in VARCHAR2,
446 P_ELEMENT_VALUE in NUMBER,
447 P_PAY_BASIS in VARCHAR2,
448 P_FORMULA_ID in NUMBER,
449 P_MAXIMUM_VALUE in NUMBER,
450 P_MID_VALUE in NUMBER,
451 P_MINIMUM_VALUE in NUMBER,
452 P_CURRENCY_CODE IN VARCHAR2
453
454 ) IS
455
456 l_api_name CONSTANT VARCHAR2(30) := 'LOCK_ROW';
457 l_api_version CONSTANT NUMBER := 1.0;
458 --
459 counter number;
460
461 CURSOR C IS SELECT * FROM PSB_PAY_ELEMENT_RATES
462 WHERE pay_element_rate_id = p_pay_element_rate_id
463 FOR UPDATE of PAY_ELEMENT_RATE_Id NOWAIT;
464 Recinfo C%ROWTYPE;
465
466 BEGIN
467 --
468 SAVEPOINT Lock_Row_Pvt ;
469 --
470 IF NOT FND_API.Compatible_API_Call ( l_api_version,
471 p_api_version,
472 l_api_name,
473 G_PKG_NAME )
474 THEN
475 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
476 END IF;
477 --
478
479 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
480 FND_MSG_PUB.initialize ;
481 END IF;
482 --
483 p_return_status := FND_API.G_RET_STS_SUCCESS ;
484 p_row_locked := FND_API.G_TRUE ;
485 --
486 OPEN C;
487 --
488 FETCH C INTO Recinfo;
489 IF (C%NOTFOUND) then
490 CLOSE C;
491 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
492 FND_MSG_PUB.Add;
493 RAISE FND_API.G_EXC_ERROR ;
494 END IF;
495
496 IF
497 (
498 (Recinfo.pay_element_rate_id = p_pay_element_rate_id)
499 AND (Recinfo.pay_element_id = p_pay_element_id)
500 AND (Recinfo.effective_start_date = p_effective_start_date)
501
502 AND ( (Recinfo.pay_element_option_id = p_pay_element_option_id)
503 OR ( (Recinfo.pay_element_option_id IS NULL)
504 AND (p_pay_element_option_id IS NULL)))
505
506 AND ( (Recinfo.effective_end_date = p_effective_end_date)
507 OR ( (Recinfo.effective_end_date IS NULL)
508 AND (p_effective_end_date IS NULL)))
509
510 AND ( (Recinfo.worksheet_id = p_worksheet_id)
511 OR ( (Recinfo.worksheet_id IS NULL)
512 AND (p_worksheet_id IS NULL)))
513
514 AND ( (Recinfo.element_value_type = p_element_value_type)
515 OR ( (Recinfo.element_value_type IS NULL)
516 AND (p_element_value_type IS NULL)))
517
518 AND ( (Recinfo.element_value = p_element_value)
519 OR ( (Recinfo.element_value IS NULL)
520 AND (p_element_value IS NULL)))
521
522 AND ( (Recinfo.pay_basis = p_pay_basis)
523 OR ( (Recinfo.pay_basis IS NULL)
524 AND (p_pay_basis IS NULL)))
525
526 AND ( (Recinfo.formula_id = p_formula_id)
527 OR ( (Recinfo.formula_id IS NULL)
528 AND (p_formula_id IS NULL)))
529
530 AND ( (Recinfo.maximum_value = p_maximum_value)
531 OR ( (Recinfo.maximum_value IS NULL)
532 AND (p_maximum_value IS NULL)))
533
534 AND ( (Recinfo.mid_value = p_mid_value)
535 OR ( (Recinfo.mid_value IS NULL)
536 AND (p_mid_value IS NULL)))
537
538 AND ( (Recinfo.minimum_value = p_minimum_value)
539 OR ( (Recinfo.minimum_value IS NULL)
540 AND (p_minimum_value IS NULL)))
541
542 AND ( (Recinfo.currency_code = p_currency_code)
543 OR ( (Recinfo.currency_code IS NULL)
544 AND (p_currency_code IS NULL)))
545 )
546
547 THEN
548 Null;
549 ELSE
553 END IF;
550 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
551 FND_MSG_PUB.Add;
552 RAISE FND_API.G_EXC_ERROR ;
554
555 --
556 IF FND_API.To_Boolean ( p_commit ) THEN
557 COMMIT WORK;
558 END iF;
559 --
560 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
561 p_data => p_msg_data );
562 --
563 EXCEPTION
564 --
565 WHEN APP_EXCEPTION.RECORD_LOCK_EXCEPTION THEN
566 --
567 ROLLBACK TO Lock_Row_Pvt ;
568 p_row_locked := FND_API.G_FALSE;
569 p_return_status := FND_API.G_RET_STS_ERROR;
570 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
571 p_data => p_msg_data );
572 --
573 WHEN FND_API.G_EXC_ERROR THEN
574 --
575 ROLLBACK TO Lock_Row_Pvt ;
576 p_return_status := FND_API.G_RET_STS_ERROR;
577 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
578 p_data => p_msg_data );
579 --
580 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
581 --
582 ROLLBACK TO Lock_Row_Pvt ;
583 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
584 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
585 p_data => p_msg_data );
586 --
587 WHEN OTHERS THEN
588 --
589 ROLLBACK TO Lock_Row_Pvt ;
590 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
591 --
592 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
593 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
594 l_api_name);
595 END if;
596 --
597 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
598 p_data => p_msg_data );
599 END LOCK_ROW;
600
601 /* ----------------------------------------------------------------------- */
602
603 PROCEDURE Delete_Element_Rates
604 ( p_api_version IN NUMBER,
605 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
606 p_commit IN VARCHAR2 := FND_API.G_FALSE,
607 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
608 p_return_status OUT NOCOPY VARCHAR2,
609 p_msg_count OUT NOCOPY NUMBER,
610 p_msg_data OUT NOCOPY VARCHAR2,
611 p_worksheet_id IN NUMBER
612 ) IS
613
614 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Element_Rates';
615 l_api_version CONSTANT NUMBER := 1.0;
616
617 BEGIN
618
619 -- Standard Start of API savepoint
620
621 SAVEPOINT Delete_Element_Rates_Pvt;
622
623
624 -- Standard call to check for call compatibility
625
626 if not FND_API.Compatible_API_Call (l_api_version,
627 p_api_version,
628 l_api_name,
629 G_PKG_NAME)
630 then
631 raise FND_API.G_EXC_UNEXPECTED_ERROR;
632 end if;
633
634
635 -- Initialize message list if p_init_msg_list is set to TRUE
636
637 if FND_API.to_Boolean (p_init_msg_list) then
638 FND_MSG_PUB.initialize;
639 end if;
640
641 delete from PSB_PAY_ELEMENT_RATES
642 where worksheet_id = p_worksheet_id;
643
644
645 -- Standard check of p_commit
646
647 if FND_API.to_Boolean (p_commit) then
648 commit work;
649 end if;
650
651
652 -- Initialize API return status to success
653
654 p_return_status := FND_API.G_RET_STS_SUCCESS;
655
656
657 -- Standard call to get message count and if count is 1, get message info
658
659 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
660 p_data => p_msg_data);
661
662 EXCEPTION
663
664 when FND_API.G_EXC_ERROR then
665 rollback to Delete_Element_Rates_Pvt;
666 p_return_status := FND_API.G_RET_STS_ERROR;
667
668 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
669 p_data => p_msg_data);
670
671
672 when FND_API.G_EXC_UNEXPECTED_ERROR then
673 rollback to Delete_Element_Rates_Pvt;
674 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
675
676 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
677 p_data => p_msg_data);
678
679
680 when OTHERS then
681 rollback to Delete_Element_Rates_Pvt;
682 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
683
684 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) then
685
686 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
687 l_api_name);
688 end if;
689
690 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
691 p_data => p_msg_data);
692
693 END Delete_Element_Rates;
694
695 /* ----------------------------------------------------------------------- */
696
697 PROCEDURE Modify_Element_Rates
698 ( p_api_version IN NUMBER,
699 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
700 p_commit IN VARCHAR2 := FND_API.G_FALSE,
701 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
702 p_return_status OUT NOCOPY VARCHAR2,
703 p_msg_count OUT NOCOPY NUMBER,
704 p_msg_data OUT NOCOPY VARCHAR2,
705 p_pay_element_id IN NUMBER,
706 p_pay_element_option_id IN NUMBER,
707 p_effective_start_date IN DATE,
708 p_effective_end_date IN DATE,
712 p_pay_basis IN VARCHAR2,
709 p_worksheet_id IN NUMBER,
710 p_element_value_type IN VARCHAR2,
711 p_element_value IN NUMBER,
713 p_formula_id IN NUMBER,
714 p_maximum_value IN NUMBER,
715 p_mid_value IN NUMBER,
716 p_minimum_value IN NUMBER,
717 p_currency_code IN VARCHAR2
718 ) IS
719
720 l_api_name CONSTANT VARCHAR2(30) := 'Modify_Element_Rates';
721 l_api_version CONSTANT NUMBER := 1.0;
722
723 cursor c_Seq is
724 select psb_pay_element_rates_s.nextval RateID
725 from dual;
726
727 cursor c_Rates is
728 select pay_element_rate_id,
729 pay_element_id,
730 pay_element_option_id,
731 effective_start_date,
732 effective_end_date,
733 worksheet_id,
734 element_value_type,
735 element_value,
736 pay_basis,
737 formula_id,
738 maximum_value,
739 mid_value,
740 minimum_value,
741 currency_code
742 from PSB_PAY_ELEMENT_RATES
743 where nvl(pay_element_option_id, FND_API.G_MISS_NUM) = nvl(p_pay_element_option_id, FND_API.G_MISS_NUM)
744 and nvl(worksheet_id, FND_API.G_MISS_NUM) = nvl(p_worksheet_id, FND_API.G_MISS_NUM)
745 and nvl(currency_code, FND_API.G_MISS_CHAR) = nvl(p_currency_code, FND_API.G_MISS_CHAR)
746 and ((((p_effective_end_date is not null)
747 and ((effective_start_date <= p_effective_end_date)
748 and (effective_end_date is null))
749 or ((effective_start_date between p_effective_start_date and p_effective_end_date)
750 or (effective_end_date between p_effective_start_date and p_effective_end_date)
751 or ((effective_start_date < p_effective_start_date)
752 and (effective_end_date > p_effective_end_date)))))
753 or ((p_effective_end_date is null)
754 and (nvl(effective_end_date, p_effective_start_date) >= p_effective_start_date)))
755 and pay_element_id = p_pay_element_id;
756
757 l_userid NUMBER;
758 l_loginid NUMBER;
759
760 l_init_index BINARY_INTEGER;
761 l_rate_index BINARY_INTEGER;
762
763 l_pay_element_rate_id NUMBER;
764
765 l_created_record VARCHAR2(1) := FND_API.G_FALSE;
766 l_updated_record VARCHAR2(1);
767
768 l_return_status VARCHAR2(1);
769
770 BEGIN
771
772 -- Standard Start of API savepoint
773
774 SAVEPOINT Modify_Element_Rates_Pvt;
775
776
777 -- Standard call to check for call compatibility
778
779 if not FND_API.Compatible_API_Call (l_api_version,
780 p_api_version,
781 l_api_name,
782 G_PKG_NAME)
783 then
784 raise FND_API.G_EXC_UNEXPECTED_ERROR;
785 end if;
786
787
788 -- Initialize message list if p_init_msg_list is set to TRUE
789
790 if FND_API.to_Boolean (p_init_msg_list) then
791 FND_MSG_PUB.initialize;
792 end if;
793
794 l_userid := FND_GLOBAL.USER_ID;
795 l_loginid := FND_GLOBAL.LOGIN_ID;
796
797 for l_init_index in 1..g_element_rates.Count loop
798 g_element_rates(l_init_index).pay_element_rate_id := null;
799 g_element_rates(l_init_index).pay_element_id := null;
800 g_element_rates(l_init_index).pay_element_option_id := null;
801 g_element_rates(l_init_index).effective_start_date := null;
802 g_element_rates(l_init_index).effective_end_date := null;
803 g_element_rates(l_init_index).worksheet_id := null;
804 g_element_rates(l_init_index).element_value_type := null;
805 g_element_rates(l_init_index).element_value := null;
806 g_element_rates(l_init_index).pay_basis := null;
807 g_element_rates(l_init_index).formula_id := null;
808 g_element_rates(l_init_index).maximum_value := null;
809 g_element_rates(l_init_index).mid_value := null;
810 g_element_rates(l_init_index).minimum_value := null;
811 g_element_rates(l_init_index).currency_code := null;
812 g_element_rates(l_init_index).proper_subset := null;
813 end loop;
814
815 g_num_element_rates := 0;
816
817 for c_Rates_Rec in c_Rates loop
818 g_num_element_rates := g_num_element_rates + 1;
819
820 g_element_rates(g_num_element_rates).pay_element_rate_id := c_Rates_Rec.pay_element_rate_id;
821 g_element_rates(g_num_element_rates).pay_element_id := c_Rates_Rec.pay_element_id;
822 g_element_rates(g_num_element_rates).pay_element_option_id := c_Rates_Rec.pay_element_option_id;
823 g_element_rates(g_num_element_rates).effective_start_date := c_Rates_Rec.effective_start_date;
824 g_element_rates(g_num_element_rates).effective_end_date := c_Rates_Rec.effective_end_date;
825 g_element_rates(g_num_element_rates).worksheet_id := c_Rates_Rec.worksheet_id;
826 g_element_rates(g_num_element_rates).element_value_type := c_Rates_Rec.element_value_type;
827 g_element_rates(g_num_element_rates).element_value := c_Rates_Rec.element_value;
828 g_element_rates(g_num_element_rates).pay_basis := c_Rates_Rec.pay_basis;
829 g_element_rates(g_num_element_rates).formula_id := c_Rates_Rec.formula_id;
830 g_element_rates(g_num_element_rates).maximum_value := c_Rates_Rec.maximum_value;
834
831 g_element_rates(g_num_element_rates).mid_value := c_Rates_Rec.mid_value;
832 g_element_rates(g_num_element_rates).minimum_value := c_Rates_Rec.minimum_value;
833 g_element_rates(g_num_element_rates).currency_code := c_Rates_Rec.currency_code;
835 if (((p_effective_end_date is not null) and
836 (c_Rates_Rec.effective_start_date between p_effective_start_date and p_effective_end_date) and
837 (c_Rates_Rec.effective_end_date between p_effective_start_date and p_effective_end_date)) or
838 ((p_effective_end_date is null) and
839 (c_Rates_Rec.effective_start_date >= p_effective_start_date))) then
840 g_element_rates(g_num_element_rates).proper_subset := FND_API.G_TRUE;
841 else
842 g_element_rates(g_num_element_rates).proper_subset := FND_API.G_FALSE;
843 end if;
844
845 end loop;
846
847 if g_num_element_rates = 0 then
848 begin
849
850 for c_Seq_Rec in c_Seq loop
851 l_pay_element_rate_id := c_Seq_Rec.RateID;
852 end loop;
853
854 Insert_Row
855 (p_api_version => 1.0,
856 p_return_status => l_return_status,
857 p_msg_count => p_msg_count,
858 p_msg_data => p_msg_data,
859 p_pay_element_rate_id => l_pay_element_rate_id,
860 p_pay_element_option_id => p_pay_element_option_id,
861 p_pay_element_id => p_pay_element_id,
862 p_effective_start_date => p_effective_start_date,
863 p_effective_end_date => p_effective_end_date,
864 p_worksheet_id => p_worksheet_id,
865 p_element_value_type => p_element_value_type,
866 p_element_value => p_element_value,
867 p_pay_basis => p_pay_basis,
868 p_formula_id => p_formula_id,
869 p_maximum_value => p_maximum_value,
870 p_mid_value => p_mid_value,
871 p_minimum_value => p_minimum_value,
872 p_currency_code => p_currency_code,
873 p_last_update_date => sysdate,
874 p_last_updated_by => l_userid,
875 p_last_update_login => l_loginid,
876 p_created_by => l_userid,
877 p_creation_date => sysdate);
878
879 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
880 raise FND_API.G_EXC_ERROR;
881 end if;
882
883 end;
884 else
885 begin
886
887 for l_rate_index in 1..g_num_element_rates loop
888
889 l_updated_record := FND_API.G_FALSE;
890
891 if ((g_num_element_rates = 1) and
892 (g_element_rates(l_rate_index).effective_start_date = p_effective_start_date)) then
893 begin
894
895 Update_Row
896 (p_api_version => 1.0,
897 p_return_status => l_return_status,
898 p_msg_count => p_msg_count,
899 p_msg_data => p_msg_data,
900 p_pay_element_rate_id => g_element_rates(l_rate_index).pay_element_rate_id,
901 p_effective_end_date => p_effective_end_date,
902 p_element_value_type => p_element_value_type,
903 p_element_value => p_element_value,
904 p_pay_basis => p_pay_basis,
905 p_formula_id => p_formula_id,
906 p_maximum_value => p_maximum_value,
907 p_mid_value => p_mid_value,
908 p_minimum_value => p_minimum_value,
909 p_last_update_date => sysdate,
910 p_last_updated_by => l_userid,
911 p_last_update_login => l_loginid);
912
913 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
914 raise FND_API.G_EXC_ERROR;
915 end if;
916
917 end;
918 else
919 begin
920
921 if ((g_element_rates(l_rate_index).effective_start_date < (p_effective_start_date - 1)) and
922 ((g_element_rates(l_rate_index).effective_end_date is null) or
923 (g_element_rates(l_rate_index).effective_end_date > (p_effective_start_date - 1)))) then
924 begin
925
926 Update_Row
927 (p_api_version => 1.0,
928 p_return_status => l_return_status,
929 p_msg_count => p_msg_count,
930 p_msg_data => p_msg_data,
931 p_pay_element_rate_id => g_element_rates(l_rate_index).pay_element_rate_id,
932 p_effective_end_date => p_effective_start_date - 1,
933 p_element_value_type => g_element_rates(l_rate_index).element_value_type,
934 p_element_value => g_element_rates(l_rate_index).element_value,
935 p_pay_basis => g_element_rates(l_rate_index).pay_basis,
936 p_formula_id => g_element_rates(l_rate_index).formula_id,
937 p_maximum_value => g_element_rates(l_rate_index).maximum_value,
938 p_mid_value => g_element_rates(l_rate_index).mid_value,
939 p_minimum_value => g_element_rates(l_rate_index).minimum_value,
940 p_last_update_date => sysdate,
941 p_last_updated_by => l_userid,
942 p_last_update_login => l_loginid);
943
944 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
945 raise FND_API.G_EXC_ERROR;
946 else
947 l_updated_record := FND_API.G_TRUE;
948 end if;
949
950 end;
951 elsif ((g_element_rates(l_rate_index).effective_start_date > p_effective_start_date) and
952 ((p_effective_end_date is not null) and
953 ((g_element_rates(l_rate_index).effective_end_date is null) or
954 (g_element_rates(l_rate_index).effective_end_date > (p_effective_end_date + 1))))) then
955 begin
956
957 Update_Row
958 (p_api_version => 1.0,
959 p_return_status => l_return_status,
960 p_msg_count => p_msg_count,
964 p_element_value_type => g_element_rates(l_rate_index).element_value_type,
961 p_msg_data => p_msg_data,
962 p_pay_element_rate_id => g_element_rates(l_rate_index).pay_element_rate_id,
963 p_effective_start_date => p_effective_end_date + 1,
965 p_element_value => g_element_rates(l_rate_index).element_value,
966 p_pay_basis => g_element_rates(l_rate_index).pay_basis,
967 p_formula_id => g_element_rates(l_rate_index).formula_id,
968 p_maximum_value => g_element_rates(l_rate_index).maximum_value,
969 p_mid_value => g_element_rates(l_rate_index).mid_value,
970 p_minimum_value => g_element_rates(l_rate_index).minimum_value,
971 p_last_update_date => sysdate,
972 p_last_updated_by => l_userid,
973 p_last_update_login => l_loginid);
974
975 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
976 raise FND_API.G_EXC_ERROR;
977 else
978 l_updated_record := FND_API.G_TRUE;
979 end if;
980
981 end;
982 end if;
983
984 if not FND_API.to_Boolean(l_created_record) then
985 begin
986
987 for c_Seq_Rec in c_Seq loop
988 l_pay_element_rate_id := c_Seq_Rec.RateID;
989 end loop;
990
991 Insert_Row
992 (p_api_version => 1.0,
993 p_return_status => l_return_status,
994 p_msg_count => p_msg_count,
995 p_msg_data => p_msg_data,
996 p_pay_element_rate_id => l_pay_element_rate_id,
997 p_pay_element_option_id => p_pay_element_option_id,
998 p_pay_element_id => p_pay_element_id,
999 p_effective_start_date => p_effective_start_date,
1000 p_effective_end_date => p_effective_end_date,
1001 p_worksheet_id => p_worksheet_id,
1002 p_element_value_type => p_element_value_type,
1003 p_element_value => p_element_value,
1004 p_pay_basis => p_pay_basis,
1005 p_formula_id => p_formula_id,
1006 p_maximum_value => p_maximum_value,
1007 p_mid_value => p_mid_value,
1008 p_minimum_value => p_minimum_value,
1009 p_currency_code => p_currency_code,
1010 p_last_update_date => sysdate,
1011 p_last_updated_by => l_userid,
1012 p_last_update_login => l_loginid,
1013 p_created_by => l_userid,
1014 p_creation_date => sysdate);
1015
1016 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1017 raise FND_API.G_EXC_ERROR;
1018 else
1019 l_created_record := FND_API.G_TRUE;
1020 end if;
1021
1022 end;
1023 end if;
1024
1025 if p_effective_end_date is not null then
1026 begin
1027
1028 if nvl(g_element_rates(l_rate_index).effective_end_date, (p_effective_end_date + 1)) > (p_effective_end_date + 1) then
1029 begin
1030
1031 if FND_API.to_Boolean(l_updated_record) then
1032 begin
1033
1034 for c_Seq_Rec in c_Seq loop
1035 l_pay_element_rate_id := c_Seq_Rec.RateID;
1036 end loop;
1037
1038 Insert_Row
1039 (p_api_version => 1.0,
1040 p_return_status => l_return_status,
1041 p_msg_count => p_msg_count,
1042 p_msg_data => p_msg_data,
1043 p_pay_element_rate_id => l_pay_element_rate_id,
1044 p_pay_element_option_id => g_element_rates(l_rate_index).pay_element_option_id,
1045 p_pay_element_id => g_element_rates(l_rate_index).pay_element_id,
1046 p_effective_start_date => p_effective_end_date + 1,
1047 p_effective_end_date => g_element_rates(l_rate_index).effective_end_date,
1048 p_worksheet_id => g_element_rates(l_rate_index).worksheet_id,
1049 p_element_value_type => g_element_rates(l_rate_index).element_value_type,
1050 p_element_value => g_element_rates(l_rate_index).element_value,
1051 p_pay_basis => g_element_rates(l_rate_index).pay_basis,
1052 p_formula_id => g_element_rates(l_rate_index).formula_id,
1053 p_maximum_value => g_element_rates(l_rate_index).maximum_value,
1054 p_mid_value => g_element_rates(l_rate_index).mid_value,
1055 p_minimum_value => g_element_rates(l_rate_index).minimum_value,
1056 p_currency_code => g_element_rates(l_rate_index).currency_code,
1057 p_last_update_date => sysdate,
1058 p_last_updated_by => l_userid,
1059 p_last_update_login => l_loginid,
1060 p_created_by => l_userid,
1061 p_creation_date => sysdate);
1062
1063 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1064 raise FND_API.G_EXC_ERROR;
1065 end if;
1066
1067 end;
1068 else
1069 begin
1070
1071 Update_Row
1072 (p_api_version => 1.0,
1073 p_return_status => l_return_status,
1074 p_msg_count => p_msg_count,
1075 p_msg_data => p_msg_data,
1076 p_pay_element_rate_id => g_element_rates(l_rate_index).pay_element_rate_id,
1077 p_effective_start_date => p_effective_end_date + 1,
1078 p_effective_end_date => g_element_rates(l_rate_index).effective_end_date,
1079 p_element_value_type => g_element_rates(l_rate_index).element_value_type,
1080 p_element_value => g_element_rates(l_rate_index).element_value,
1081 p_pay_basis => g_element_rates(l_rate_index).pay_basis,
1082 p_formula_id => g_element_rates(l_rate_index).formula_id,
1083 p_maximum_value => g_element_rates(l_rate_index).maximum_value,
1084 p_mid_value => g_element_rates(l_rate_index).mid_value,
1088 p_last_update_login => l_loginid);
1085 p_minimum_value => g_element_rates(l_rate_index).minimum_value,
1086 p_last_update_date => sysdate,
1087 p_last_updated_by => l_userid,
1089
1090 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1091 raise FND_API.G_EXC_ERROR;
1092 end if;
1093
1094 end;
1095 end if;
1096
1097 end;
1098 end if;
1099
1100 end;
1101 end if;
1102
1103 end;
1104 end if;
1105
1106 end loop;
1107
1108 end;
1109 end if;
1110
1111 if FND_API.to_Boolean(l_created_record) then
1112 begin
1113
1114 for l_rate_index in 1..g_num_element_rates loop
1115
1116 if FND_API.to_Boolean(g_element_rates(l_rate_index).proper_subset) then
1117 begin
1118
1119 Delete_Row
1120 (p_api_version => 1.0,
1121 p_return_status => l_return_status,
1122 p_msg_count => p_msg_count,
1123 p_msg_data => p_msg_data,
1124 p_pay_element_rate_id => g_element_rates(l_rate_index).pay_element_rate_id);
1125
1126 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1127 raise FND_API.G_EXC_ERROR;
1128 end if;
1129
1130 end;
1131 end if;
1132
1133 end loop;
1134
1135 end;
1136 end if;
1137
1138
1139 -- Standard check of p_commit
1140
1141 if FND_API.to_Boolean (p_commit) then
1142 commit work;
1143 end if;
1144
1145
1146 -- Initialize API return status to success
1147
1148 p_return_status := FND_API.G_RET_STS_SUCCESS;
1149
1150
1151 -- Standard call to get message count and if count is 1, get message info
1152
1153 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
1154 p_data => p_msg_data);
1155
1156 EXCEPTION
1157
1158 when FND_API.G_EXC_ERROR then
1159 rollback to Modify_Element_Rates_Pvt;
1160 p_return_status := FND_API.G_RET_STS_ERROR;
1161
1162 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
1163 p_data => p_msg_data);
1164
1165
1166 when FND_API.G_EXC_UNEXPECTED_ERROR then
1167 rollback to Modify_Element_Rates_Pvt;
1168 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1169
1170 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
1171 p_data => p_msg_data);
1172
1173
1174 when OTHERS then
1175 rollback to Modify_Element_Rates_Pvt;
1176 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1177
1178 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) then
1179
1180 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
1181 l_api_name);
1182 end if;
1183
1184 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
1185 p_data => p_msg_data);
1186
1187 END Modify_Element_Rates;
1188
1189 /*-------------------------------------------------------------------------*/
1190
1191
1192
1193 /*==========================================================================+
1194 | PROCEDURE Check_Date_Range_Overlap |
1195 +==========================================================================*/
1196 --
1197 -- This API checks for overlapping date ranges in 'PSB_PAY_ELEMENT_RATES'
1198 -- table.
1199 --
1200 PROCEDURE Check_Date_Range_Overlap
1201 (
1202 p_api_version IN NUMBER,
1203 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1204 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1205 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1206 p_return_status OUT NOCOPY VARCHAR2,
1207 p_msg_count OUT NOCOPY NUMBER,
1208 p_msg_data OUT NOCOPY VARCHAR2,
1209 --
1210 p_pay_element_id IN NUMBER,
1211 p_pay_element_option_id IN NUMBER,
1212 p_overlap_found_flag OUT NOCOPY VARCHAR2
1213 )
1214 IS
1215 --
1216 l_api_name CONSTANT VARCHAR2(30) := 'Check_Date_Range_Overlap';
1217 l_api_version CONSTANT NUMBER := 1.0;
1218 --
1219 l_count_end_dates NUMBER ;
1220 l_count NUMBER ;
1221 --
1222 BEGIN
1223 --
1224 SAVEPOINT Check_Date_Range_Overlap_Pvt ;
1225 --
1226 IF NOT FND_API.Compatible_API_Call ( l_api_version,
1227 p_api_version,
1228 l_api_name,
1229 G_PKG_NAME )
1230 THEN
1231 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1232 END IF;
1233 --
1234
1235 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
1236 FND_MSG_PUB.initialize ;
1237 END IF;
1238 --
1239 p_return_status := FND_API.G_RET_STS_SUCCESS ;
1240 p_overlap_found_flag := FND_API.G_FALSE ;
1241 --
1242
1243 --
1244 -- Only one date rannge can be end-dates.
1245 --
1246 SELECT count(*) INTO l_count_end_dates
1247 FROM psb_pay_element_rates
1248 WHERE pay_element_id = p_pay_element_id
1249 AND (
1250 p_pay_element_option_id IS NULL
1251 OR
1252 pay_element_option_id = p_pay_element_option_id
1253 )
1254 AND effective_end_date IS NULL ;
1255
1256 IF l_count_end_dates > 1 THEN
1257 p_overlap_found_flag := FND_API.G_TRUE ;
1258 FND_MESSAGE.Set_Name('PSB', 'PSB_MANY_OPEN_ENDED_DATES');
1259 FND_MSG_PUB.Add;
1260 RAISE FND_API.G_EXC_ERROR ;
1261 END IF ;
1262
1263
1264 FOR l_rates_rec IN
1265 (
1266 SELECT pay_element_rate_id, effective_start_date
1267 FROM psb_pay_element_rates
1268 WHERE pay_element_id = p_pay_element_id
1269 AND (
1270 p_pay_element_option_id IS NULL
1271 OR
1272 pay_element_option_id = p_pay_element_option_id
1273 )
1274 )
1275 LOOP
1276 --
1277
1278 SELECT count(*) INTO l_count
1279 FROM psb_pay_element_rates
1280 WHERE pay_element_id = p_pay_element_id
1281 AND (
1282 p_pay_element_option_id IS NULL
1283 OR
1284 pay_element_option_id = p_pay_element_option_id
1285 )
1286 AND pay_element_rate_id <> l_rates_rec.pay_element_rate_id
1287 AND (
1288 (
1289 effective_end_date IS NULL AND
1290 l_rates_rec.effective_start_date >= effective_start_date
1291 )
1292 OR
1293 (
1294 l_rates_rec.effective_start_date
1295 BETWEEN effective_start_date AND effective_end_date
1296 )
1297 ) ;
1298
1299 IF l_count > 0 THEN
1300 p_overlap_found_flag := FND_API.G_TRUE ;
1301 FND_MESSAGE.Set_Name('PSB', 'PSB_DUP_DATE_RANGE');
1302 FND_MSG_PUB.Add;
1303 RAISE FND_API.G_EXC_ERROR ;
1304 END IF ;
1305 --
1306 END LOOP;
1307
1308 --
1309 IF FND_API.To_Boolean ( p_commit ) THEN
1310 COMMIT WORK;
1311 END iF;
1312 --
1313 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
1314 p_data => p_msg_data );
1315 --
1316 EXCEPTION
1317 --
1318 WHEN FND_API.G_EXC_ERROR THEN
1319 --
1320 ROLLBACK TO Check_Date_Range_Overlap_Pvt ;
1321 p_return_status := FND_API.G_RET_STS_ERROR;
1322 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
1323 p_data => p_msg_data );
1324 --
1325 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1326 --
1327 ROLLBACK TO Check_Date_Range_Overlap_Pvt ;
1328 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1329 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
1330 p_data => p_msg_data );
1331 --
1332 WHEN OTHERS THEN
1333 --
1334 ROLLBACK TO Check_Date_Range_Overlap_Pvt ;
1335 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1336 --
1337 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1338 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
1339 l_api_name);
1340 END if;
1341 --
1342 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
1343 p_data => p_msg_data );
1344 --
1345 END Check_Date_Range_Overlap;
1346 /*-------------------------------------------------------------------------*/
1347
1348
1349 END PSB_PAY_ELEMENT_RATES_PVT;