[Home] [Help]
PACKAGE BODY: APPS.PSB_PAY_ELEMENTS_PVT
Source
1 PACKAGE BODY PSB_PAY_ELEMENTS_PVT AS
2 /* $Header: PSBVELMB.pls 120.2 2005/03/12 13:06:06 matthoma ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):= 'PSB_PAY_ELEMENTS_PVT';
5 g_dbug VARCHAR2(2000);
6 g_entity_set_id NUMBER;
7 -- The flag determines whether to print debug information or not.
8 g_debug_flag VARCHAR2(1) := 'N' ;
9
10 /* ---------------------- Private Procedures -----------------------*/
11
12 PROCEDURE debug
13 (
14 p_message IN VARCHAR2
15 ) ;
16 /* ----------------------------------------------------------------------- */
17
18 PROCEDURE Insert_Row
19 ( p_api_version IN NUMBER,
20 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
21 p_commit IN VARCHAR2 := FND_API.G_FALSE,
22 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
23 p_return_status OUT NOCOPY VARCHAR2,
24 p_msg_count OUT NOCOPY NUMBER,
25 p_msg_data OUT NOCOPY VARCHAR2,
26 --
27 P_ROW_ID in OUT NOCOPY VARCHAR2,
28 P_PAY_ELEMENT_ID in NUMBER,
29 P_BUSINESS_GROUP_ID in NUMBER,
30 P_DATA_EXTRACT_ID in NUMBER,
31 p_BUDGET_SET_ID in NUMBER := FND_API.G_MISS_NUM,
32 P_NAME in VARCHAR2,
33 P_DESCRIPTION in VARCHAR2,
34 P_ELEMENT_VALUE_TYPE in VARCHAR2,
35 P_FORMULA_ID in NUMBER,
36 P_OVERWRITE_FLAG in VARCHAR2,
37 P_REQUIRED_FLAG in VARCHAR2,
38 P_FOLLOW_SALARY in VARCHAR2,
39 P_PAY_BASIS IN VARCHAR2,
40 P_START_DATE in DATE,
41 P_END_DATE in DATE,
42 P_PROCESSING_TYPE in VARCHAR2,
43 P_PERIOD_TYPE in VARCHAR2,
44 P_PROCESS_PERIOD_TYPE in VARCHAR2,
45 P_MAX_ELEMENT_VALUE_TYPE in VARCHAR2,
46 P_MAX_ELEMENT_VALUE in NUMBER,
47 P_SALARY_FLAG in VARCHAR2,
48 P_SALARY_TYPE in VARCHAR2,
49 P_OPTION_FLAG in VARCHAR2,
50 P_HR_ELEMENT_TYPE_ID in NUMBER,
51 P_ATTRIBUTE_CATEGORY in VARCHAR2,
52 P_ATTRIBUTE1 in VARCHAR2,
53 P_ATTRIBUTE2 in VARCHAR2,
54 P_ATTRIBUTE3 in VARCHAR2,
55 P_ATTRIBUTE4 in VARCHAR2,
56 P_ATTRIBUTE5 in VARCHAR2,
57 P_ATTRIBUTE6 in VARCHAR2,
58 P_ATTRIBUTE7 in VARCHAR2,
59 P_ATTRIBUTE8 in VARCHAR2,
60 P_ATTRIBUTE9 in VARCHAR2,
61 P_ATTRIBUTE10 in VARCHAR2,
62 P_LAST_UPDATE_DATE in DATE,
63 P_LAST_UPDATED_BY in NUMBER,
64 P_LAST_UPDATE_LOGIN in NUMBER,
65 P_CREATED_BY in NUMBER,
66 P_CREATION_DATE in DATE
67 ) IS
68
69 l_api_name CONSTANT VARCHAR2(30) := 'Insert_Row';
70 l_api_version CONSTANT NUMBER := 1.0;
71 --
72 cursor c1 is
73 select ROWID from psb_pay_elements
74 where pay_element_id = p_pay_element_id;
75
76 BEGIN
77
78 -- Standard Start of API savepoint
79
80 SAVEPOINT INSERT_ROW_PVT;
81
82 -- Standard call to check for call compatibility.
83
84 if not FND_API.Compatible_API_Call (l_api_version,
85 p_api_version,
86 l_api_name,
87 G_PKG_NAME)
88 then
89 raise FND_API.G_EXC_UNEXPECTED_ERROR;
90 end if;
91
92 -- Initialize message list if p_init_msg_list is set to TRUE.
93
94 if FND_API.to_Boolean (p_init_msg_list) then
95 FND_MSG_PUB.initialize;
96 end if;
97
98 -- Initialize API return status to success
99
100 p_return_status := FND_API.G_RET_STS_SUCCESS;
101
102
103 -- API body
104 INSERT INTO psb_pay_elements
105 (
106 PAY_ELEMENT_ID ,
107 BUSINESS_GROUP_ID ,
108 DATA_EXTRACT_ID ,
109 BUDGET_SET_ID ,
110 NAME ,
111 DESCRIPTION ,
112 ELEMENT_VALUE_TYPE ,
113 FORMULA_ID ,
114 OVERWRITE_FLAG ,
115 REQUIRED_FLAG ,
116 FOLLOW_SALARY ,
117 PAY_BASIS ,
118 START_DATE ,
119 END_DATE ,
120 PROCESSING_TYPE ,
121 PERIOD_TYPE ,
122 PROCESS_PERIOD_TYPE ,
123 MAX_ELEMENT_VALUE_TYPE ,
124 MAX_ELEMENT_VALUE ,
125 SALARY_FLAG ,
126 SALARY_TYPE ,
127 OPTION_FLAG ,
128 HR_ELEMENT_TYPE_ID ,
129 ATTRIBUTE_CATEGORY ,
130 ATTRIBUTE1 ,
131 ATTRIBUTE2 ,
132 ATTRIBUTE3 ,
133 ATTRIBUTE4 ,
134 ATTRIBUTE5 ,
135 ATTRIBUTE6 ,
136 ATTRIBUTE7 ,
137 ATTRIBUTE8 ,
138 ATTRIBUTE9 ,
139 ATTRIBUTE10 ,
140 LAST_UPDATE_DATE ,
141 LAST_UPDATED_BY ,
142 LAST_UPDATE_LOGIN ,
143 CREATED_BY ,
144 CREATION_DATE
145
146 )
147 VALUES
148 (
149 P_PAY_ELEMENT_ID ,
150 P_BUSINESS_GROUP_ID ,
151 P_DATA_EXTRACT_ID ,
152 decode(P_BUDGET_SET_ID, FND_API.G_MISS_NUM,null,P_BUDGET_SET_ID) ,
153 P_NAME ,
154 P_DESCRIPTION ,
155 P_ELEMENT_VALUE_TYPE ,
156 P_FORMULA_ID ,
157 P_OVERWRITE_FLAG ,
158 P_REQUIRED_FLAG ,
159 P_FOLLOW_SALARY ,
160 P_PAY_BASIS ,
161 P_START_DATE ,
162 P_END_DATE ,
163 P_PROCESSING_TYPE ,
164 P_PERIOD_TYPE ,
165 P_PROCESS_PERIOD_TYPE ,
166 P_MAX_ELEMENT_VALUE_TYPE ,
167 P_MAX_ELEMENT_VALUE ,
168 P_SALARY_FLAG ,
169 P_SALARY_TYPE ,
170 P_OPTION_FLAG ,
171 P_HR_ELEMENT_TYPE_ID ,
172 P_ATTRIBUTE_CATEGORY ,
173 P_ATTRIBUTE1 ,
174 P_ATTRIBUTE2 ,
175 P_ATTRIBUTE3 ,
176 P_ATTRIBUTE4 ,
177 P_ATTRIBUTE5 ,
178 P_ATTRIBUTE6 ,
179 P_ATTRIBUTE7 ,
180 P_ATTRIBUTE8 ,
181 P_ATTRIBUTE9 ,
182 P_ATTRIBUTE10 ,
183 /* Bug 4222417 Start */
184 NVL(P_LAST_UPDATE_DATE,SYSDATE) ,
185 NVL(P_LAST_UPDATED_BY,FND_GLOBAL.USER_ID) ,
186 NVL(P_LAST_UPDATE_LOGIN,FND_GLOBAL.LOGIN_ID) ,
187 NVL(P_CREATED_BY,FND_GLOBAL.USER_ID) ,
188 NVL(P_CREATION_DATE,SYSDATE)
189 /* Bug 4222417 End */
190 );
191
192 open c1;
193 fetch c1 into P_ROW_ID;
194 if (c1%notfound) then
195 close c1;
196 raise no_data_found;
197 end if;
198 -- End of API body.
199
200 -- Standard check of p_commit.
201
202 if FND_API.to_Boolean (p_commit) then
203 commit work;
204 end if;
205
206 -- Standard call to get message count and if count is 1, get message info.
207
208 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
209 p_data => p_msg_data);
210
211 EXCEPTION
212
213 when FND_API.G_EXC_ERROR then
214
215 rollback to INSERT_ROW_PVT;
216
217 p_return_status := FND_API.G_RET_STS_ERROR;
218
219 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
220 p_data => p_msg_data);
221
222
223 when FND_API.G_EXC_UNEXPECTED_ERROR then
224
225 rollback to INSERT_ROW_PVT;
226
227 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
228
229 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
230 p_data => p_msg_data);
231
232
233 when OTHERS then
234
235 rollback to INSERT_ROW_PVT;
236
237 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
238
239 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) then
240
241 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
242 l_api_name);
243 end if;
244
245 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
246 p_data => p_msg_data);
247
248 END Insert_Row;
249
250
251 PROCEDURE Update_Row
252 ( p_api_version IN NUMBER,
253 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
254 p_commit IN VARCHAR2 := FND_API.G_FALSE,
255 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
256 p_return_status OUT NOCOPY VARCHAR2,
257 p_msg_count OUT NOCOPY NUMBER,
258 p_msg_data OUT NOCOPY VARCHAR2,
259 --
260 P_ROW_ID in VARCHAR2,
261 P_PAY_ELEMENT_ID in NUMBER,
262 P_BUSINESS_GROUP_ID in NUMBER,
263 P_DATA_EXTRACT_ID in NUMBER,
264 P_BUDGET_SET_ID in NUMBER := FND_API.G_MISS_NUM,
265 P_NAME in VARCHAR2,
266 P_DESCRIPTION in VARCHAR2,
267 P_ELEMENT_VALUE_TYPE in VARCHAR2,
268 P_FORMULA_ID in NUMBER,
269 P_OVERWRITE_FLAG in VARCHAR2,
270 P_REQUIRED_FLAG in VARCHAR2,
271 P_FOLLOW_SALARY in VARCHAR2,
272 P_PAY_BASIS IN VARCHAR2,
273 P_START_DATE in DATE,
274 P_END_DATE in DATE,
275 P_PROCESSING_TYPE in VARCHAR2,
276 P_PERIOD_TYPE in VARCHAR2,
277 P_PROCESS_PERIOD_TYPE in VARCHAR2,
278 P_MAX_ELEMENT_VALUE_TYPE in VARCHAR2,
279 P_MAX_ELEMENT_VALUE in NUMBER,
280 P_SALARY_FLAG in VARCHAR2,
281 P_SALARY_TYPE in VARCHAR2,
282 P_OPTION_FLAG in VARCHAR2,
283 P_HR_ELEMENT_TYPE_ID in NUMBER,
284 P_ATTRIBUTE_CATEGORY in VARCHAR2,
285 P_ATTRIBUTE1 in VARCHAR2,
286 P_ATTRIBUTE2 in VARCHAR2,
287 P_ATTRIBUTE3 in VARCHAR2,
288 P_ATTRIBUTE4 in VARCHAR2,
289 P_ATTRIBUTE5 in VARCHAR2,
290 P_ATTRIBUTE6 in VARCHAR2,
291 P_ATTRIBUTE7 in VARCHAR2,
292 P_ATTRIBUTE8 in VARCHAR2,
293 P_ATTRIBUTE9 in VARCHAR2,
294 P_ATTRIBUTE10 in VARCHAR2,
295 P_LAST_UPDATE_DATE in DATE,
296 P_LAST_UPDATED_BY in NUMBER,
297 P_LAST_UPDATE_LOGIN in NUMBER
298 ) IS
299
300 l_api_name CONSTANT VARCHAR2(30) := 'Update_Row';
301 l_api_version CONSTANT NUMBER := 1.0;
302
303 BEGIN
304
305 -- Standard Start of API savepoint
306
307 SAVEPOINT UPDATE_ROW_PVT;
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 G_PKG_NAME)
315 then
316 raise FND_API.G_EXC_UNEXPECTED_ERROR;
317 end if;
318
319 -- Initialize message list if p_init_msg_list is set to TRUE.
320
321 if FND_API.to_Boolean (p_init_msg_list) then
322 FND_MSG_PUB.initialize;
323 end if;
324
325 -- Initialize API return status to success
326
327 p_return_status := FND_API.G_RET_STS_SUCCESS;
328
329 -- API body
330 UPDATE psb_pay_elements SET
331 PAY_ELEMENT_ID = P_PAY_ELEMENT_ID,
332 BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID,
333 DATA_EXTRACT_ID = P_DATA_EXTRACT_ID,
334 BUDGET_SET_ID = decode(P_BUDGET_SET_ID,FND_API.G_MISS_NUM,null,P_BUDGET_SET_ID),
335 NAME = P_NAME,
336 DESCRIPTION = P_DESCRIPTION,
337 ELEMENT_VALUE_TYPE = P_ELEMENT_VALUE_TYPE,
338 FORMULA_ID = P_FORMULA_ID,
339 OVERWRITE_FLAG = P_OVERWRITE_FLAG,
340 REQUIRED_FLAG = P_REQUIRED_FLAG,
341 FOLLOW_SALARY = P_FOLLOW_SALARY,
342 PAY_BASIS = P_PAY_BASIS,
343 START_DATE = P_START_DATE,
344 END_DATE = P_END_DATE,
345 PROCESSING_TYPE = P_PROCESSING_TYPE,
346 PERIOD_TYPE = P_PERIOD_TYPE,
347 PROCESS_PERIOD_TYPE = P_PROCESS_PERIOD_TYPE,
348 MAX_ELEMENT_VALUE_TYPE = P_MAX_ELEMENT_VALUE_TYPE,
349 MAX_ELEMENT_VALUE = P_MAX_ELEMENT_VALUE,
350 SALARY_FLAG = P_SALARY_FLAG,
351 SALARY_TYPE = P_SALARY_TYPE,
352 OPTION_FLAG = P_OPTION_FLAG,
353 HR_ELEMENT_TYPE_ID = P_HR_ELEMENT_TYPE_ID,
354 ATTRIBUTE_CATEGORY = P_ATTRIBUTE_CATEGORY,
355 ATTRIBUTE1 = P_ATTRIBUTE1,
356 ATTRIBUTE2 = P_ATTRIBUTE2,
357 ATTRIBUTE3 = P_ATTRIBUTE3,
358 ATTRIBUTE4 = P_ATTRIBUTE4,
359 ATTRIBUTE5 = P_ATTRIBUTE5,
360 ATTRIBUTE6 = P_ATTRIBUTE6,
361 ATTRIBUTE7 = P_ATTRIBUTE7,
362 ATTRIBUTE8 = P_ATTRIBUTE8,
363 ATTRIBUTE9 = P_ATTRIBUTE9,
364 ATTRIBUTE10 = P_ATTRIBUTE10,
365 LAST_UPDATE_DATE = P_LAST_UPDATE_DATE,
366 LAST_UPDATED_BY = P_LAST_UPDATED_BY,
367 LAST_UPDATE_LOGIN = P_LAST_UPDATE_LOGIN
368 WHERE ROWID = P_ROW_ID;
369
370 if (SQL%NOTFOUND) then
371 RAISE NO_DATA_FOUND;
372 end if;
373
374 -- End of API body.
375
376 -- Standard check of p_commit.
377
378 if FND_API.to_Boolean (p_commit) then
379 commit work;
380 end if;
381
382 -- Standard call to get message count and if count is 1, get message info.
383
384 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
385 p_data => p_msg_data);
386
387 EXCEPTION
388
389 when FND_API.G_EXC_ERROR then
390
391 rollback to UPDATE_ROW_PVT;
392
393 p_return_status := FND_API.G_RET_STS_ERROR;
394
395 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
396 p_data => p_msg_data);
397
398
399 when FND_API.G_EXC_UNEXPECTED_ERROR then
400
401 rollback to UPDATE_ROW_PVT;
402
403 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
404
405 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
406 p_data => p_msg_data);
407
408
409 when OTHERS then
410
411 rollback to UPDATE_ROW_PVT;
412
413 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
414
415 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) then
416
417 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
418 l_api_name);
419 end if;
420
421 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
422 p_data => p_msg_data);
423
424 END Update_Row;
425
426
427 /*============================================================================+
428 | PROCEDURE Delete_Row |
429 +============================================================================*/
430 PROCEDURE Delete_Row
431 (
432 p_api_version IN NUMBER,
433 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
434 p_commit IN VARCHAR2 := FND_API.G_FALSE,
435 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
436 p_return_status OUT NOCOPY VARCHAR2,
437 p_msg_count OUT NOCOPY NUMBER,
438 p_msg_data OUT NOCOPY VARCHAR2,
439 --
440 p_row_id IN VARCHAR2
441 )
442 IS
443 --
444 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Row' ;
445 l_api_version CONSTANT NUMBER := 1.0 ;
446 --
447 --
448 l_return_status VARCHAR2(1) ;
449 l_msg_count NUMBER ;
450 l_msg_data VARCHAR2(2000) ;
451 --
452 l_pay_element_id psb_pay_elements.pay_element_id%TYPE ;
453 --
454 BEGIN
455 --
456 SAVEPOINT Delete_Row_Pvt;
457 --
458 IF NOT FND_API.Compatible_API_Call (l_api_version,
459 p_api_version,
460 l_api_name,
461 G_PKG_NAME)
462 THEN
463 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
464 END IF ;
465 --
466 IF FND_API.to_Boolean (p_init_msg_list) THEN
467 FND_MSG_PUB.initialize;
468 END IF;
469 --
470 p_return_status := FND_API.G_RET_STS_SUCCESS ;
471 --
472
473 -- Get pay_element_id to be used for deletion of detail records.
474 SELECT pay_element_id INTO l_pay_element_id
475 FROM psb_pay_elements
476 WHERE rowid = p_row_id ;
477
478 --
479 -- Delete element position set group related position sets.
480 --
481
482 FOR l_pos_set_group_rec IN
483 (
484 SELECT position_set_group_id
485 FROM psb_element_pos_set_groups
486 WHERE pay_element_id = l_pay_element_id
487 )
488 LOOP
489
490 PSB_Set_Relation_PVT.Delete_Entity_Relation
491 (
492 p_api_version => 1.0 ,
493 p_init_msg_list => FND_API.G_FALSE ,
494 p_commit => FND_API.G_FALSE ,
495 p_validation_level => FND_API.G_VALID_LEVEL_FULL ,
496 p_return_status => l_return_status ,
497 p_msg_count => l_msg_count ,
498 p_msg_data => l_msg_data ,
499 --
500 p_entity_type => 'PSG' ,
501 p_entity_id => l_pos_set_group_rec.position_set_group_id
502 ) ;
503 --
504 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
505 RAISE FND_API.G_EXC_ERROR ;
506 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
507 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
508 END IF;
509 --
510 END LOOP ;
511
512
513 --
514 -- Delete dependent detail records to maintain ISOLATED master-detail
515 -- form relation.
516 --
517
518 DELETE psb_pay_element_rates
519 WHERE pay_element_id = l_pay_element_id ;
520
521 DELETE psb_pay_element_options
522 WHERE pay_element_id = l_pay_element_id ;
523
524 DELETE psb_pay_element_distributions
525 WHERE position_set_group_id IN
526 (
527 SELECT position_set_group_id
528 FROM psb_element_pos_set_groups
529 WHERE pay_element_id = l_pay_element_id
530 ) ;
531
532 DELETE psb_element_pos_set_groups
533 WHERE pay_element_id = l_pay_element_id ;
534
535
536 --
537 -- Delete the master record now in psb_pay_elements.
538 --
539 DELETE psb_pay_elements
540 WHERE rowid = p_row_id ;
541
542 --
543 IF FND_API.To_Boolean ( p_commit ) THEN
544 COMMIT WORK;
545 END IF;
546 --
547 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
548 p_data => p_msg_data );
549 --
550 EXCEPTION
551 --
552 WHEN FND_API.G_EXC_ERROR THEN
553 --
554 ROLLBACK TO Delete_Row_Pvt ;
555 p_return_status := FND_API.G_RET_STS_ERROR;
556 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
557 p_data => p_msg_data );
558 --
559 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
560 --
561 ROLLBACK TO Delete_Row_Pvt ;
562 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
563 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
564 p_data => p_msg_data );
565 --
566 WHEN OTHERS THEN
567 --
568 ROLLBACK TO Delete_Row_Pvt ;
569 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
570 --
571 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
572 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
573 l_api_name);
574 END if;
575 --
576 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
577 p_data => p_msg_data );
578 --
579 END Delete_Row;
580 /*----------------------------------------------------------------------------*/
581
582
583 PROCEDURE Lock_Row
584 (
585 p_api_version IN NUMBER,
586 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
587 p_commit IN VARCHAR2 := FND_API.G_FALSE,
588 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
589 p_return_status OUT NOCOPY VARCHAR2,
590 p_msg_count OUT NOCOPY NUMBER,
591 p_msg_data OUT NOCOPY VARCHAR2,
592 --
593 p_row_locked OUT NOCOPY VARCHAR2,
594 --
595 P_ROW_ID in VARCHAR2,
596 P_PAY_ELEMENT_ID in NUMBER,
597 P_BUSINESS_GROUP_ID in NUMBER,
598 P_DATA_EXTRACT_ID in NUMBER,
599 P_BUDGET_SET_ID in NUMBER := FND_API.G_MISS_NUM,
600 P_NAME in VARCHAR2,
601 P_DESCRIPTION in VARCHAR2,
602 P_ELEMENT_VALUE_TYPE in VARCHAR2,
603 P_FORMULA_ID in NUMBER,
604 P_OVERWRITE_FLAG in VARCHAR2,
605 P_REQUIRED_FLAG in VARCHAR2,
606 P_FOLLOW_SALARY in VARCHAR2,
607 P_PAY_BASIS IN VARCHAR2,
608 P_START_DATE in DATE,
609 P_END_DATE in DATE,
610 P_PROCESSING_TYPE in VARCHAR2,
611 P_PERIOD_TYPE in VARCHAR2,
612 P_PROCESS_PERIOD_TYPE in VARCHAR2,
613 P_MAX_ELEMENT_VALUE_TYPE in VARCHAR2,
614 P_MAX_ELEMENT_VALUE in NUMBER,
615 P_SALARY_FLAG in VARCHAR2,
616 P_SALARY_TYPE in VARCHAR2,
617 P_OPTION_FLAG in VARCHAR2,
618 P_HR_ELEMENT_TYPE_ID in NUMBER,
619 P_ATTRIBUTE_CATEGORY in VARCHAR2,
620 P_ATTRIBUTE1 in VARCHAR2,
621 P_ATTRIBUTE2 in VARCHAR2,
622 P_ATTRIBUTE3 in VARCHAR2,
623 P_ATTRIBUTE4 in VARCHAR2,
624 P_ATTRIBUTE5 in VARCHAR2,
625 P_ATTRIBUTE6 in VARCHAR2,
626 P_ATTRIBUTE7 in VARCHAR2,
627 P_ATTRIBUTE8 in VARCHAR2,
628 P_ATTRIBUTE9 in VARCHAR2,
629 P_ATTRIBUTE10 in VARCHAR2
630 ) IS
631
632 l_api_name CONSTANT VARCHAR2(30) := 'Lock_Row';
633 l_api_version CONSTANT NUMBER := 1.0;
634 --
635 counter number;
636
637 CURSOR C IS SELECT * FROM PSB_PAY_ELEMENTS WHERE ROWID = p_Row_Id
638 FOR UPDATE of PAY_ELEMENT_Id NOWAIT;
639 Recinfo C%ROWTYPE;
640
641 BEGIN
642 --
643 SAVEPOINT Lock_Row_Pvt ;
644 --
645 IF NOT FND_API.Compatible_API_Call ( l_api_version,
646 p_api_version,
647 l_api_name,
648 G_PKG_NAME )
649 THEN
650 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
651 END IF;
652 --
653
654 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
655 FND_MSG_PUB.initialize ;
656 END IF;
657 --
658 p_return_status := FND_API.G_RET_STS_SUCCESS ;
659 p_row_locked := FND_API.G_TRUE ;
660 --
661 OPEN C;
662 --
663 FETCH C INTO Recinfo;
664 IF (C%NOTFOUND) then
665 CLOSE C;
666 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
667 FND_MSG_PUB.Add;
668 RAISE FND_API.G_EXC_ERROR ;
669 END IF;
670
671 IF
672 (
673 (Recinfo.pay_element_id = p_pay_element_id)
674 AND (Recinfo.business_group_id = p_business_group_id)
675 AND (Recinfo.data_extract_id = p_data_extract_id)
676 AND (Recinfo.name = p_name)
677
678 AND ( (Recinfo.budget_set_id = p_budget_set_id)
679 OR ( (Recinfo.budget_set_id IS NULL)
680 AND (p_budget_set_id IS NULL))
681 OR (p_budget_set_id = FND_API.G_MISS_NUM))
682
683 AND ( (Recinfo.description = p_description)
684 OR ( (Recinfo.description IS NULL)
685 AND (p_description IS NULL)))
686
687 AND ( (Recinfo.element_value_type = p_element_value_type)
688 OR ( (Recinfo.element_value_type IS NULL)
689 AND (p_element_value_type IS NULL)))
690
691 AND ( (Recinfo.formula_id = p_formula_id)
692 OR ( (Recinfo.formula_id IS NULL)
693 AND (p_formula_id IS NULL)))
694
695 AND ( (Recinfo.overwrite_flag = p_overwrite_flag)
696 OR ( (Recinfo.overwrite_flag IS NULL)
697 AND (p_overwrite_flag IS NULL)))
698
699 AND ( (Recinfo.required_flag = p_required_flag)
700 OR ( (Recinfo.required_flag IS NULL)
701 AND (p_required_flag IS NULL)))
702
703 AND ( (Recinfo.follow_salary = p_follow_salary)
704 OR ( (Recinfo.follow_salary IS NULL)
705 AND (p_follow_salary IS NULL)))
706
707 AND ( (Recinfo.pay_basis = p_pay_basis)
708 OR ( (Recinfo.pay_basis IS NULL)
709 AND (p_pay_basis IS NULL)))
710
711 AND ( (Recinfo.start_date = p_start_date)
712 OR ( (Recinfo.start_date IS NULL)
713 AND (p_start_date IS NULL)))
714
715 AND ( (Recinfo.end_date = p_end_date)
716 OR ( (Recinfo.end_date IS NULL)
717 AND (p_end_date IS NULL)))
718
719 AND ( (Recinfo.processing_type = p_processing_type)
720 OR ( (Recinfo.processing_type IS NULL)
721 AND (p_processing_type IS NULL)))
722
723 AND ( (Recinfo.period_type = p_period_type)
724 OR ( (Recinfo.period_type IS NULL)
725 AND (p_period_type IS NULL)))
726
727 AND ( (Recinfo.process_period_type = p_process_period_type)
728 OR ( (Recinfo.process_period_type IS NULL)
729 AND (p_process_period_type IS NULL)))
730
731 AND ( (Recinfo.max_element_value_type = p_max_element_value_type)
732 OR ( (Recinfo.max_element_value_type IS NULL)
733 AND (p_max_element_value_type IS NULL)))
734
735 AND ( (Recinfo.max_element_value = p_max_element_value)
736 OR ( (Recinfo.max_element_value IS NULL)
737 AND (p_max_element_value IS NULL)))
738
739 AND ( (Recinfo.salary_flag = p_salary_flag)
740 OR ( (Recinfo.salary_flag IS NULL)
741 AND (p_salary_flag IS NULL)))
742
743 AND ( (Recinfo.salary_type = p_salary_type)
744 OR ( (Recinfo.salary_type IS NULL)
745 AND (p_salary_type IS NULL)))
746
747 AND ( (Recinfo.option_flag = p_option_flag)
748 OR ( (Recinfo.option_flag IS NULL)
749 AND (p_option_flag IS NULL)))
750
751 AND ( (Recinfo.hr_element_type_id = p_hr_element_type_id)
752 OR ( (Recinfo.hr_element_type_id IS NULL)
753 AND (p_hr_element_type_id IS NULL)))
754
755 AND ( (Recinfo.attribute_category = p_attribute_category)
756 OR ( (Recinfo.attribute_category IS NULL)
757 AND (p_attribute_category IS NULL)))
758
759 AND ( (Recinfo.attribute1 = p_attribute1)
760 OR ( (Recinfo.attribute1 IS NULL)
761 AND (p_attribute1 IS NULL)))
762
763 AND ( (Recinfo.attribute2 = p_attribute2)
764 OR ( (Recinfo.attribute2 IS NULL)
765 AND (p_attribute2 IS NULL)))
766
767 AND ( (Recinfo.attribute3 = p_attribute3)
768 OR ( (Recinfo.attribute3 IS NULL)
769 AND (p_attribute3 IS NULL)))
770
771 AND ( (Recinfo.attribute4 = p_attribute4)
772 OR ( (Recinfo.attribute4 IS NULL)
773 AND (p_attribute4 IS NULL)))
774
775 AND ( (Recinfo.attribute5 = p_attribute5)
776 OR ( (Recinfo.attribute5 IS NULL)
777 AND (p_attribute5 IS NULL)))
778
779 AND ( (Recinfo.attribute6 = p_attribute6)
780 OR ( (Recinfo.attribute6 IS NULL)
781 AND (p_attribute6 IS NULL)))
782
783 AND ( (Recinfo.attribute7 = p_attribute7)
784 OR ( (Recinfo.attribute7 IS NULL)
785 AND (p_attribute7 IS NULL)))
786
787 AND ( (Recinfo.attribute8 = p_attribute8)
788 OR ( (Recinfo.attribute8 IS NULL)
789 AND (p_attribute8 IS NULL)))
790
791 AND ( (Recinfo.attribute9 = p_attribute9)
792 OR ( (Recinfo.attribute9 IS NULL)
793 AND (p_attribute9 IS NULL)))
794
795 AND ( (Recinfo.attribute10 = p_attribute10)
796 OR ( (Recinfo.attribute10 IS NULL)
797 AND (p_attribute10 IS NULL)))
798 )
799
800 THEN
801 Null;
802 ELSE
803 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
804 FND_MSG_PUB.Add;
805 RAISE FND_API.G_EXC_ERROR ;
806 END IF;
807
808 --
809 IF FND_API.To_Boolean ( p_commit ) THEN
810 COMMIT WORK;
811 END iF;
812 --
813 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
814 p_data => p_msg_data );
815 --
816 EXCEPTION
817 --
818 WHEN APP_EXCEPTION.RECORD_LOCK_EXCEPTION THEN
819 --
820 ROLLBACK TO Lock_Row_Pvt ;
821 p_row_locked := FND_API.G_FALSE;
822 p_return_status := FND_API.G_RET_STS_ERROR;
823 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
824 p_data => p_msg_data );
825 --
826 WHEN FND_API.G_EXC_ERROR THEN
827 --
828 ROLLBACK TO Lock_Row_Pvt ;
829 p_return_status := FND_API.G_RET_STS_ERROR;
830 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
831 p_data => p_msg_data );
832 --
833 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
834 --
835 ROLLBACK TO Lock_Row_Pvt ;
836 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
837 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
838 p_data => p_msg_data );
839 --
840 WHEN OTHERS THEN
841 --
842 ROLLBACK TO Lock_Row_Pvt ;
843 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
844 --
845 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
846 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
847 l_api_name);
848 END if;
849 --
850 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
851 p_data => p_msg_data );
852 END Lock_Row;
853
854
855 PROCEDURE Check_Unique
856 (
857 p_api_version IN NUMBER,
858 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
859 p_commit IN VARCHAR2 := FND_API.G_FALSE,
860 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
861 p_return_status OUT NOCOPY VARCHAR2,
862 p_msg_count OUT NOCOPY NUMBER,
863 p_msg_data OUT NOCOPY VARCHAR2,
864 --
865 p_Row_Id IN VARCHAR2,
866 p_Name IN VARCHAR2,
867 P_DATA_EXTRACT_ID IN NUMBER,
868 p_Return_Value IN OUT NOCOPY VARCHAR2
869 )
870 IS
871 --
872 l_api_name CONSTANT VARCHAR2(30) := 'Check_Unique';
873 l_api_version CONSTANT NUMBER := 1.0;
874 --
875 l_tmp VARCHAR2(1);
876
877 CURSOR c IS
878 SELECT '1'
879 FROM psb_pay_elements
880 WHERE name = p_name
881 AND ( (p_Row_Id IS NULL)
882 OR (RowId <> p_Row_Id) )
883 AND (DATA_EXTRACT_ID = P_DATA_EXTRACT_ID);
884 BEGIN
885 --
886 SAVEPOINT Check_Unique_Pvt ;
887 --
888 IF NOT FND_API.Compatible_API_Call ( l_api_version,
889 p_api_version,
890 l_api_name,
891 G_PKG_NAME )
892 THEN
893 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
894 END IF;
895 --
896
897 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
898 FND_MSG_PUB.initialize ;
899 END IF;
900 --
901 p_return_status := FND_API.G_RET_STS_SUCCESS ;
902 --
903
904 -- Checking the Psb_set_relations table for references.
905 OPEN c;
906 FETCH c INTO l_tmp;
907 --
908 -- p_Return_Value tells whether references exist or not.
909 IF l_tmp IS NULL THEN
910 p_Return_Value := 'FALSE';
911 ELSE
912 p_Return_Value := 'TRUE';
913 END IF;
914
915 CLOSE c;
916 --
917 IF FND_API.To_Boolean ( p_commit ) THEN
918 COMMIT WORK;
919 END iF;
920 --
921 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
922 p_data => p_msg_data );
923 --
924 EXCEPTION
925 --
926 WHEN FND_API.G_EXC_ERROR THEN
927 --
928 ROLLBACK TO Check_Unique_Pvt ;
929 p_return_status := FND_API.G_RET_STS_ERROR;
930 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
931 p_data => p_msg_data );
932 --
933 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
934 --
935 ROLLBACK TO Check_Unique_Pvt ;
936 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
937 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
938 p_data => p_msg_data );
939 --
940 WHEN OTHERS THEN
941 --
942 ROLLBACK TO Check_Unique_Pvt ;
943 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
944 --
945 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
946 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
947 l_api_name);
948 END if;
949 --
950 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
951 p_data => p_msg_data );
952 --
953 END Check_Unique;
954
955
956
957 /*============================================================================+
958 | PROCEDURE Check_References |
959 +============================================================================*/
960 PROCEDURE Check_References
961 (
962 p_api_version IN NUMBER,
963 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
964 p_commit IN VARCHAR2 := FND_API.G_FALSE,
965 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
966 p_return_status OUT NOCOPY VARCHAR2,
967 p_msg_count OUT NOCOPY NUMBER,
968 p_msg_data OUT NOCOPY VARCHAR2,
969 --
970 p_pay_element_id IN NUMBER,
971 p_return_value IN OUT NOCOPY VARCHAR2
972 )
973 IS
974 --
975 l_api_name CONSTANT VARCHAR2(30) := 'Check_References';
976 l_api_version CONSTANT NUMBER := 1.0;
977 --
978 l_tmp1 VARCHAR2(1);
979 l_tmp2 VARCHAR2(1);
980 l_tmp3 VARCHAR2(1);
981 --
982 CURSOR c1 IS
983 SELECT '1'
984 FROM psb_position_assignments
985 WHERE pay_element_id = p_pay_element_id ;
986
987 CURSOR c2 IS
988 SELECT '1'
989 FROM psb_ws_element_lines
990 WHERE pay_element_id = p_pay_element_id ;
991
992 CURSOR c3 IS
993 SELECT '1'
994 FROM psb_default_assignments
995 WHERE pay_element_id = p_pay_element_id ;
996 --
997 BEGIN
998 --
999 SAVEPOINT Check_References_Pvt ;
1000 --
1001 IF NOT FND_API.Compatible_API_Call ( l_api_version,
1002 p_api_version,
1003 l_api_name,
1004 G_PKG_NAME )
1005 THEN
1006 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1007 END IF;
1008 --
1009
1010 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
1011 FND_MSG_PUB.initialize ;
1012 END IF;
1013 --
1014 p_return_status := FND_API.G_RET_STS_SUCCESS ;
1015 --
1016
1017 OPEN c1 ;
1018 FETCH c1 INTO l_tmp1 ;
1019 CLOSE c1;
1020
1021 OPEN c2 ;
1022 FETCH c2 INTO l_tmp2 ;
1023 CLOSE c2;
1024
1025 OPEN c3 ;
1026 FETCH c3 INTO l_tmp3 ;
1027 CLOSE c3;
1028
1029 --
1030 -- p_return_value specifies whether references exist or not.
1031 --
1032 IF ( l_tmp1 IS NULL AND l_tmp2 IS NULL AND l_tmp3 IS NULL ) THEN
1033
1034 p_Return_Value := 'FALSE' ;
1035
1036 ELSE
1037
1038 p_Return_Value := 'TRUE' ;
1039
1040 END IF;
1041
1042 --
1043 IF FND_API.To_Boolean ( p_commit ) THEN
1044 COMMIT WORK;
1045 END IF;
1046 --
1047 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
1048 p_data => p_msg_data );
1049
1050 --
1051 EXCEPTION
1052 --
1053 WHEN FND_API.G_EXC_ERROR THEN
1054 --
1055 ROLLBACK TO Check_References_Pvt ;
1056 p_return_status := FND_API.G_RET_STS_ERROR;
1057 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
1058 p_data => p_msg_data );
1059 --
1060 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1061 --
1062 ROLLBACK TO Check_References_Pvt ;
1063 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1064 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
1065 p_data => p_msg_data );
1066 --
1067 WHEN OTHERS THEN
1068 --
1069 ROLLBACK TO Check_References_Pvt ;
1070 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1071 --
1072 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1073 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
1074 l_api_name);
1075 END if;
1076 --
1077 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
1078 p_data => p_msg_data );
1079 --
1080 END Check_References;
1081 /*----------------------------------------------------------------------------*/
1082
1083 /*===========================================================================+
1084 | PROCEDURE Copy_Pay_Elements |
1085 +===========================================================================*/
1086 --
1087
1088 PROCEDURE Copy_Pay_Elements
1089 ( p_api_version IN NUMBER,
1090 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1091 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1092 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1093 p_return_status OUT NOCOPY VARCHAR2,
1094 p_msg_count OUT NOCOPY NUMBER,
1095 p_msg_data OUT NOCOPY VARCHAR2,
1096 p_source_pay_element_id IN NUMBER,
1097 p_source_data_extract_id IN NUMBER,
1098 p_target_data_extract_id IN NUMBER
1099 ) AS
1100
1101 l_api_name CONSTANT VARCHAR2(30) := 'Copy_Pay_Elements';
1102 l_api_version CONSTANT NUMBER := 1.0;
1103 l_last_update_date DATE;
1104 l_last_updated_by NUMBER;
1105 l_last_update_login NUMBER;
1106 l_pay_element_id NUMBER;
1107 lr_pay_element_id NUMBER;
1108 l_pay_element_option_id NUMBER;
1109 l_pay_element_rate_id NUMBER;
1110 l_source_pay_element_option_id NUMBER;
1111 l_position_set_group_id NUMBER;
1112 l_source_set_group_id NUMBER;
1113 l_set_relation_id NUMBER;
1114 l_distribution_set_id NUMBER;
1115 l_option_flag VARCHAR2(1);
1116 prev_distribution_set_id NUMBER := -1;
1117 l_distribution_id NUMBER;
1118 l_rowid2 VARCHAR2(100);
1119 l_set_name VARCHAR2(100);
1120 l_element_dummy NUMBER;
1121 l_element_name VARCHAR2(30);
1122 l_budget_set_id NUMBER := NULL;
1123 l_rowid VARCHAR2(100);
1124 l_creation_date DATE;
1125 l_created_by NUMBER;
1126 l_status VARCHAR2(1);
1127 l_return_status VARCHAR2(1);
1128 l_msg_count NUMBER;
1129 l_msg_data VARCHAR2(1000);
1130
1131 CURSOR l_pay_element_csr is
1132 SELECT DISTINCT *
1133 FROM psb_pay_elements
1134 WHERE pay_element_id = p_source_pay_element_id;
1135
1136 CURSOR l_pay_element_options_csr is
1137 SELECT pay_element_option_id,pay_element_id,
1138 name, grade_step, sequence_number
1139 FROM psb_pay_element_options
1140 WHERE pay_element_id = p_source_pay_element_id;
1141
1142 CURSOR l_pay_element_rates_csr is
1143 SELECT pay_element_rate_id,pay_element_option_id,
1144 effective_start_date,effective_end_date,
1145 worksheet_id,element_value_type,
1146 element_value,formula_id,
1147 maximum_value,mid_value,
1148 minimum_value,currency_code,pay_basis
1149 FROM psb_pay_element_rates
1150 WHERE (((pay_element_option_id = l_source_pay_element_option_id ) AND
1151 (pay_element_id = p_source_pay_element_id)) or
1152 ((pay_element_id = p_source_pay_element_id) AND
1153 (pay_element_option_id IS NULL)))
1154 AND worksheet_id IS NULL;
1155
1156 CURSOR l_set_groups_csr is
1157 select position_set_group_id,name
1158 FROM psb_element_pos_set_groups
1159 WHERE pay_element_id = p_source_pay_element_id;
1160
1161 CURSOR l_position_sets_csr is
1162 SELECT aps.name,
1163 effective_start_date,
1164 effective_end_date
1165 FROM psb_set_relations rels, psb_account_position_sets aps
1166 WHERE rels.account_position_set_id = aps.account_position_set_id
1167 AND aps.data_extract_id = p_source_data_extract_id
1168 AND rels.position_set_group_id = l_source_set_group_id;
1169
1170 CURSOR l_account_sets_csr is
1171 SELECT account_position_set_id
1172 FROM psb_account_position_sets
1173 WHERE name = l_set_name
1174 AND data_extract_id = p_target_data_extract_id;
1175
1176 CURSOR l_account_distr_csr is
1177 SELECT distribution_id,distribution_set_id,
1178 chart_of_accounts_id,effective_start_date,
1179 effective_end_date,distribution_percent,
1180 code_combination_id,concatenated_segments,
1181 segment1,segment2,
1182 segment3,segment4,
1183 segment5,segment6,
1184 segment7,segment8,
1185 segment9,segment10,
1186 segment11,segment12,
1187 segment13,segment14,
1188 segment15,segment16,
1189 segment17,segment18,
1190 segment19,segment20,
1191 segment21,segment22,
1192 segment23,segment24,
1193 segment25,segment26,
1194 segment27,segment28,
1195 segment29,segment30
1196 FROM psb_pay_element_distributions
1197 WHERE position_set_group_id = l_source_set_group_id
1198 order by distribution_set_id;
1199
1200 BEGIN
1201
1202 -- Standard Start of API savepoint
1203
1204 SAVEPOINT Copy_Pay_Elements_Pvt;
1205
1206 -- Standard call to check for call compatibility.
1207
1208 if not FND_API.Compatible_API_Call (l_api_version,
1209 p_api_version,
1210 l_api_name,
1211 G_PKG_NAME)
1212 then
1213 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1214 end if;
1215
1216 -- Initialize message list if p_init_msg_list is set to TRUE.
1217
1218 if FND_API.to_Boolean (p_init_msg_list) then
1219 FND_MSG_PUB.initialize;
1220 end if;
1221
1222 -- Initialize API return status to success
1223
1224 p_return_status := FND_API.G_RET_STS_SUCCESS;
1225
1226 -- API body
1227 l_last_update_date := sysDATE;
1228 l_last_updated_by := FND_GLOBAL.USER_ID;
1229 l_last_update_login := FND_GLOBAL.LOGIN_ID;
1230 l_creation_date := sysDATE;
1231 l_created_by := FND_GLOBAL.USER_ID;
1232
1233 --creating a new element based on p_source_pay_element_id
1234
1235
1236 For l_pay_element_rec IN l_pay_element_csr
1237 Loop
1238
1239 l_element_name := l_pay_element_rec.name;
1240 l_option_flag := l_pay_element_rec.option_flag;
1241
1242 SELECT psb_pay_elements_s.NEXTVAL
1243 INTO l_pay_element_id
1244 FROM DUAL;
1245
1246 PSB_PAY_ELEMENTS_PVT.INSERT_ROW
1247 ( p_api_version => 1.0,
1248 p_init_msg_list => null,
1249 p_commit => null,
1250 p_validation_level => null,
1251 p_return_status => l_return_status,
1252 p_msg_count => l_msg_count,
1253 p_msg_data => l_msg_data,
1254 p_row_id => l_rowid,
1255 p_pay_element_id => l_pay_element_id,
1256 p_business_group_id => l_pay_element_rec.business_group_id,
1257 p_data_extract_id => p_target_data_extract_id,
1258 p_budget_set_id => l_budget_set_id,
1259 p_name => l_pay_element_rec.name,
1260 p_description => l_pay_element_rec.description,
1261 p_element_value_type => l_pay_element_rec.element_value_type,
1262 p_formula_id => l_pay_element_rec.formula_id,
1263 p_overwrite_flag => l_pay_element_rec.overwrite_flag,
1264 p_required_flag => l_pay_element_rec.required_flag,
1265 p_follow_salary => l_pay_element_rec.follow_salary,
1266 p_pay_basis => l_pay_element_rec.pay_basis,
1267 p_start_date => l_pay_element_rec.start_date,
1268 p_end_date => l_pay_element_rec.end_date,
1269 p_processing_type => l_pay_element_rec.processing_type,
1270 p_period_type => l_pay_element_rec.period_type,
1271 p_process_period_type => l_pay_element_rec.process_period_type,
1272 p_max_element_value_type => l_pay_element_rec.max_element_value_type,
1273 p_max_element_value => l_pay_element_rec.max_element_value,
1274 p_salary_flag => l_pay_element_rec.salary_flag,
1275 p_salary_type => l_pay_element_rec.salary_type,
1276 p_option_flag => l_pay_element_rec.option_flag,
1277 p_hr_element_type_id => l_pay_element_rec.hr_element_type_id,
1278 p_attribute_category => l_pay_element_rec.attribute_category,
1279 p_attribute1 => l_pay_element_rec.attribute1,
1280 p_attribute2 => l_pay_element_rec.attribute2,
1281 p_attribute3 => l_pay_element_rec.attribute3,
1282 p_attribute4 => l_pay_element_rec.attribute4,
1283 p_attribute5 => l_pay_element_rec.attribute5,
1284 p_attribute6 => l_pay_element_rec.attribute6,
1285 p_attribute7 => l_pay_element_rec.attribute7,
1286 p_attribute8 => l_pay_element_rec.attribute8,
1287 p_attribute9 => l_pay_element_rec.attribute9,
1288 p_attribute10 => l_pay_element_rec.attribute10,
1289 p_last_update_date => l_last_update_date,
1290 p_last_updated_by => l_last_updated_by,
1291 p_last_update_login => l_last_update_login,
1292 p_created_by => l_created_by,
1293 p_creation_date => l_creation_date
1294 );
1295
1296 debug( 'New Pay Element Created:'||l_pay_element_id);
1297
1298 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1299 debug( 'Element not Copied for Pay Element Id : ' ||
1300 p_source_pay_element_id);
1301 RAISE FND_API.G_EXC_ERROR ;
1302 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1303 debug( 'Element not Copied for Pay Element Id : ' ||
1304 p_source_pay_element_id);
1305 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1306 END IF;
1307
1308 For l_pay_element_options_rec in l_pay_element_options_csr
1309 Loop
1310
1311 l_source_pay_element_option_id :=
1312 l_pay_element_options_rec.pay_element_option_id;
1313
1314 SELECT psb_pay_element_options_s.NEXTVAL
1315 INTO l_pay_element_option_id
1316 FROM DUAL;
1317
1318 PSB_PAY_ELEMENT_OPTIONS_PVT.INSERT_ROW
1319 ( p_api_version => 1.0,
1320 p_init_msg_list => null,
1321 p_commit => null,
1322 p_validation_level => null,
1323 p_return_status => l_return_status,
1324 p_msg_count => l_msg_count,
1325 p_msg_data => l_msg_data,
1326 p_pay_element_option_id => l_pay_element_option_id,
1327 p_pay_element_id => l_pay_element_id,
1328 p_name => l_pay_element_options_rec.name,
1329 p_grade_step => l_pay_element_options_rec.grade_step,
1330 p_sequence_number => l_pay_element_options_rec.sequence_number,
1331 p_last_update_date => l_last_update_date,
1332 p_last_updated_by => l_last_updated_by,
1333 p_last_update_login => l_last_update_login,
1334 p_created_by => l_created_by,
1335 p_creation_date => l_creation_date
1336 );
1337
1338 debug( 'New Pay Element Option Created:'||l_pay_element_option_id);
1339
1340 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1341 debug( 'Element Option not Copied for Pay Element Option Id : ' ||
1342 l_pay_element_options_rec.pay_element_option_id);
1343 RAISE FND_API.G_EXC_ERROR ;
1344 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1345 debug( 'Element Option not Copied for Pay Element Option Id : ' ||
1346 l_pay_element_options_rec.pay_element_option_id);
1347 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1348 END IF;
1349
1350 For l_pay_element_rates_rec in l_pay_element_rates_csr
1351 Loop
1352
1353 SELECT psb_pay_element_rates_s.NEXTVAL
1354 INTO l_pay_element_rate_id
1355 FROM DUAL;
1356
1357 PSB_PAY_ELEMENT_RATES_PVT.INSERT_ROW
1358 ( p_api_version => 1.0,
1359 p_init_msg_list => null,
1360 p_commit => null,
1361 p_validation_level => null,
1362 p_return_status => l_return_status,
1363 p_msg_count => l_msg_count,
1364 p_msg_data => l_msg_data,
1365 p_pay_element_rate_id => l_pay_element_rate_id,
1366 p_pay_element_option_id => l_pay_element_option_id,
1367 p_pay_element_id => l_pay_element_id,
1368 p_effective_start_date => l_pay_element_rates_rec.effective_start_date,
1369 p_effective_end_date => l_pay_element_rates_rec.effective_end_date,
1370 p_worksheet_id => l_pay_element_rates_rec.worksheet_id,
1371 p_element_value_type => l_pay_element_rates_rec.element_value_type,
1372 p_element_value => l_pay_element_rates_rec.element_value,
1373 p_pay_basis => l_pay_element_rates_rec.pay_basis,
1374 p_formula_id => l_pay_element_rates_rec.formula_id,
1375 p_maximum_value => l_pay_element_rates_rec.maximum_value,
1376 p_mid_value => l_pay_element_rates_rec.mid_value,
1377 p_minimum_value => l_pay_element_rates_rec.minimum_value,
1378 p_currency_code => l_pay_element_rates_rec.currency_code,
1379 p_last_update_date => l_last_update_date,
1380 p_last_updated_by => l_last_updated_by,
1381 p_last_update_login => l_last_update_login,
1382 p_created_by => l_created_by,
1383 p_creation_date => l_creation_date
1384 ) ;
1385
1386 debug( 'New Pay Element Rate Created:'||l_pay_element_rate_id);
1387
1388 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1389 debug( 'Element Rate not Copied for Pay Element Rate Id : ' ||
1390 l_pay_element_rates_rec.pay_element_rate_id);
1391 RAISE FND_API.G_EXC_ERROR ;
1392 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1393 debug( 'Element Rate not Copied for Pay Element Rate Id : ' ||
1394 l_pay_element_rates_rec.pay_element_rate_id);
1395 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1396 END IF;
1397
1398 End Loop;
1399 End Loop;
1400
1401 if (l_option_flag <> 'Y') then
1402 For l_pay_element_rates_rec in l_pay_element_rates_csr
1403 Loop
1404
1405 SELECT psb_pay_element_rates_s.NEXTVAL
1406 INTO l_pay_element_rate_id
1407 FROM DUAL;
1408
1409 PSB_PAY_ELEMENT_RATES_PVT.INSERT_ROW
1410 ( p_api_version => 1.0,
1411 p_init_msg_list => null,
1412 p_commit => null,
1413 p_validation_level => null,
1414 p_return_status => l_return_status,
1415 p_msg_count => l_msg_count,
1416 p_msg_data => l_msg_data,
1417 p_pay_element_rate_id => l_pay_element_rate_id,
1418 p_pay_element_option_id => l_pay_element_option_id,
1419 p_pay_element_id => l_pay_element_id,
1420 p_effective_start_date =>
1421 l_pay_element_rates_rec.effective_start_date,
1422 p_effective_end_date =>
1423 l_pay_element_rates_rec.effective_end_date,
1424 p_worksheet_id => l_pay_element_rates_rec.worksheet_id,
1425 p_element_value_type =>
1426 l_pay_element_rates_rec.element_value_type,
1427 p_element_value => l_pay_element_rates_rec.element_value,
1428 p_pay_basis => l_pay_element_rates_rec.pay_basis,
1429 p_formula_id => l_pay_element_rates_rec.formula_id,
1430 p_maximum_value => l_pay_element_rates_rec.maximum_value,
1431 p_mid_value => l_pay_element_rates_rec.mid_value,
1432 p_minimum_value => l_pay_element_rates_rec.minimum_value,
1433 p_currency_code => l_pay_element_rates_rec.currency_code,
1434 p_last_update_date => l_last_update_date,
1435 p_last_updated_by => l_last_updated_by,
1436 p_last_update_login => l_last_update_login,
1437 p_created_by => l_created_by,
1438 p_creation_date => l_creation_date
1439 ) ;
1440
1441 debug( 'New Pay Element Rate Created:'||l_pay_element_rate_id);
1442
1443 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1444 debug( 'Element Rate not Copied for Pay Element Rate Id : ' ||
1445 l_pay_element_rates_rec.pay_element_rate_id);
1446 RAISE FND_API.G_EXC_ERROR ;
1447 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1448 debug( 'Element Rate not Copied for Pay Element Rate Id : ' ||
1449 l_pay_element_rates_rec.pay_element_rate_id);
1450 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1451 END IF;
1452
1453 End Loop;
1454 end if;
1455
1456 For l_set_groups_rec in l_set_groups_csr
1457 Loop
1458 l_source_set_group_id := l_set_groups_rec.position_set_group_id;
1459
1460 SELECT psb_element_pos_set_groups_s.NEXTVAL
1461 INTO l_position_set_group_id
1462 FROM DUAL;
1463
1464 PSB_ELEMENT_POS_SET_GROUPS_PVT.Insert_Row
1465 ( p_api_version => 1.0,
1466 p_init_msg_list => FND_API.G_FALSE,
1467 p_commit => FND_API.G_FALSE,
1468 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1469 p_return_status => l_return_status,
1470 p_msg_count => l_msg_count,
1471 p_msg_data => l_msg_data,
1472 --
1473 p_position_set_group_id => l_position_set_group_id,
1474 p_pay_element_id => l_pay_element_id,
1475 p_name => l_set_groups_rec.name,
1476 p_last_update_date => l_last_update_date,
1477 p_last_updated_by => l_last_updated_by,
1478 p_last_update_login => l_last_update_login,
1479 p_created_by => l_created_by,
1480 p_creation_date => l_creation_date
1481 );
1482
1483 debug( 'New Position Set Group Created:'||l_position_set_group_id);
1484
1485 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1486 debug( 'Position Set Group not Copied for Set Group Id : ' ||
1487 l_source_set_group_id);
1488 RAISE FND_API.G_EXC_ERROR ;
1489 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1490 debug( 'Position Set Group not Copied for Set Group Id : ' ||
1491 l_source_set_group_id);
1492 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1493 END IF;
1494
1495
1496 prev_distribution_set_id := -1;
1497 For l_account_distr_rec in l_account_distr_csr
1498 Loop
1499 SELECT psb_pay_element_distribution_s.NEXTVAL INTO
1500 l_distribution_id
1501 FROM DUAL;
1502
1503 if ((l_account_distr_rec.distribution_set_id <>
1504 prev_distribution_set_id)
1505 or (prev_distribution_set_id = -1)) then
1506 SELECT psb_element_distribution_set_s.NEXTVAL INTO
1507 l_distribution_set_id
1508 FROM DUAL;
1509 end if;
1510
1511 PSB_ELE_DISTRIBUTIONS_I_PVT.Insert_Row
1512 ( p_api_version => 1.0,
1513 p_init_msg_list => FND_API.G_FALSE,
1514 p_commit => FND_API.G_FALSE,
1515 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1516 p_return_status => l_return_status,
1517 p_msg_count => l_msg_count,
1518 p_msg_data => l_msg_data,
1519 --
1520 p_distribution_id => l_distribution_id,
1521 p_position_set_group_id => l_position_set_group_id,
1522 p_chart_of_accounts_id =>
1523 l_account_distr_rec.chart_of_accounts_id,
1524 p_effective_start_date =>
1525 l_account_distr_rec.effective_start_date,
1526 p_effective_end_date =>
1527 l_account_distr_rec.effective_end_date,
1528 p_distribution_percent =>
1529 l_account_distr_rec.distribution_percent,
1530 p_concatenated_segments =>
1531 l_account_distr_rec.concatenated_segments,
1532 p_code_combination_id =>
1533 l_account_distr_rec.code_combination_id,
1534 p_distribution_set_id => l_distribution_set_id,
1535 p_segment1 => l_account_distr_rec.segment1,
1536 p_segment2 => l_account_distr_rec.segment2,
1537 p_segment3 => l_account_distr_rec.segment3,
1538 p_segment4 => l_account_distr_rec.segment4,
1539 p_segment5 => l_account_distr_rec.segment5,
1540 p_segment6 => l_account_distr_rec.segment6,
1541 p_segment7 => l_account_distr_rec.segment7,
1542 p_segment8 => l_account_distr_rec.segment8,
1543 p_segment9 => l_account_distr_rec.segment9,
1544 p_segment10 => l_account_distr_rec.segment10,
1545 p_segment11 => l_account_distr_rec.segment11,
1546 p_segment12 => l_account_distr_rec.segment12,
1547 p_segment13 => l_account_distr_rec.segment13,
1548 p_segment14 => l_account_distr_rec.segment14,
1549 p_segment15 => l_account_distr_rec.segment15,
1550 p_segment16 => l_account_distr_rec.segment16,
1551 p_segment17 => l_account_distr_rec.segment17,
1552 p_segment18 => l_account_distr_rec.segment18,
1553 p_segment19 => l_account_distr_rec.segment19,
1554 p_segment20 => l_account_distr_rec.segment20,
1555 p_segment21 => l_account_distr_rec.segment21,
1556 p_segment22 => l_account_distr_rec.segment22,
1557 p_segment23 => l_account_distr_rec.segment23,
1558 p_segment24 => l_account_distr_rec.segment24,
1559 p_segment25 => l_account_distr_rec.segment25,
1560 p_segment26 => l_account_distr_rec.segment26,
1561 p_segment27 => l_account_distr_rec.segment27,
1562 p_segment28 => l_account_distr_rec.segment28,
1563 p_segment29 => l_account_distr_rec.segment29,
1564 p_segment30 => l_account_distr_rec.segment30,
1565 p_last_update_date => l_last_update_date,
1566 p_last_updated_by => l_last_updated_by,
1567 p_last_update_login => l_last_update_login,
1568 p_created_by => l_created_by,
1569 p_creation_date => l_creation_date
1570 );
1571
1572 debug( 'New Distribution Created:'||l_distribution_id);
1573
1574 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1575 debug( 'Pay Element Distribution not Copied for Distribution Id
1576 : ' || l_account_distr_rec.distribution_id);
1577 RAISE FND_API.G_EXC_ERROR ;
1578 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1579 debug( 'Pay Element Distribution not Copied for Distribution Id
1580 : ' || l_account_distr_rec.distribution_id);
1581 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1582 END IF;
1583
1584 prev_distribution_set_id := l_account_distr_rec.distribution_set_id;
1585 End Loop;
1586
1587 For l_position_sets_rec in l_position_sets_csr
1588 Loop
1589 l_set_name := l_position_sets_rec.name;
1590 For l_account_sets_rec in l_account_sets_csr
1591 Loop
1592 l_set_relation_id := null;
1593 PSB_Set_Relation_PVT.Insert_Row
1594 ( p_api_version => 1.0,
1595 p_init_msg_list => FND_API.G_FALSE,
1596 p_commit => FND_API.G_FALSE,
1597 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1598 p_return_status => l_return_status,
1599 p_msg_count => l_msg_count,
1600 p_msg_data => l_msg_data,
1601 p_Row_Id => l_rowid2,
1602 p_Set_Relation_Id => l_set_relation_id,
1603 p_Account_Position_Set_Id =>
1604 l_account_sets_rec.account_position_set_id,
1605 p_Allocation_Rule_Id => null,
1606 p_Budget_Group_Id => null,
1607 p_Budget_Workflow_Rule_Id => null,
1608 p_Constraint_Id => null,
1609 p_Default_Rule_Id => null,
1610 p_Parameter_Id => null,
1611 p_Position_Set_Group_Id => l_position_set_group_id,
1612 /* Budget Revision Rules Enhancement Start */
1613 p_rule_id => null,
1614 p_apply_balance_flag => null,
1615 /* Budget Revision Rules Enhancement End */
1616 p_Effective_Start_Date =>
1617 l_position_sets_rec.effective_start_date,
1618 p_Effective_End_Date =>
1619 l_position_sets_rec.effective_end_date,
1620 p_last_update_date => l_last_update_date,
1621 p_last_updated_by => l_last_updated_by,
1622 p_last_update_login => l_last_update_login,
1623 p_created_by => l_created_by,
1624 p_creation_date => l_creation_date
1625 );
1626 debug( 'New Relation Created for Set group ID:'||
1627 l_position_set_group_id);
1628
1629 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1630 debug( 'Set Relation not created for position set id
1631 : ' || l_account_sets_rec.account_position_set_id);
1632 RAISE FND_API.G_EXC_ERROR ;
1633 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1634 debug( 'Set Relation not created for position set id
1635 : ' || l_account_sets_rec.account_position_set_id);
1636 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1637 END IF;
1638
1639 End Loop;
1640 End Loop;
1641 End Loop;
1642 End Loop;
1643
1644 -- End of API body.
1645
1646 -- Standard check of p_commit.
1647
1648 if FND_API.to_Boolean (p_commit) then
1649 commit work;
1650 end if;
1651
1652 -- Standard call to get message count AND if count is 1, get message info.
1653
1654 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
1655 p_data => p_msg_data);
1656
1657 EXCEPTION
1658
1659 when FND_API.G_EXC_ERROR then
1660
1661 rollback to Copy_Pay_Elements_Pvt;
1662
1663 p_return_status := FND_API.G_RET_STS_ERROR;
1664
1665 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
1666 p_data => p_msg_data);
1667
1668
1669 when FND_API.G_EXC_UNEXPECTED_ERROR then
1670
1671 rollback to Copy_Pay_Elements_Pvt;
1672
1673 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1674
1675 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
1676 p_data => p_msg_data);
1677
1678
1679 when OTHERS then
1680
1681 rollback to Copy_Pay_Elements_Pvt;
1682
1683 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1684
1685 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) then
1686
1687 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
1688 l_api_name);
1689 end if;
1690
1691 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
1692 p_data => p_msg_data);
1693
1694 END Copy_Pay_Elements;
1695
1696 /*===========================================================================+
1697 | PROCEDURE debug (Private) |
1698 +===========================================================================*/
1699 --
1700 -- Private procedure to print debug info
1701
1702 PROCEDURE debug
1703 (
1704 p_message IN VARCHAR2
1705 )
1706 IS
1707 --
1708 BEGIN
1709
1710 IF g_debug_flag = 'Y' THEN
1711 null;
1712 -- dbms_output.put_line(p_message) ;
1713 END IF;
1714
1715 END debug ;
1716
1717 END PSB_PAY_ELEMENTS_PVT;