[Home] [Help]
PACKAGE BODY: APPS.PSB_POSITIONS_PVT
Source
1 PACKAGE BODY PSB_POSITIONS_PVT AS
2 /* $Header: PSBVPOSB.pls 120.26 2006/02/17 12:40:34 matthoma ship $ */
3 --
4 -- Global Variables
5 --
6
7 G_PKG_NAME CONSTANT VARCHAR2(30):= 'PSB_POSITIONS_PVT';
8 G_DBUG VARCHAR2(2000);
9
10 TYPE g_assign_rec_type IS RECORD
11 ( position_assignment_id NUMBER,
12 data_extract_id NUMBER,
13 worksheet_id NUMBER,
14 position_id NUMBER,
15 assignment_type VARCHAR2(10),
16 attribute_id NUMBER,
17 attribute_value_id NUMBER,
18 --UTF8 changes for Bug No : 2615261
19 attribute_value VARCHAR2(240),
20 pay_element_id NUMBER,
21 pay_element_option_id NUMBER,
22 effective_start_date DATE,
23 effective_end_date DATE,
24 element_value_type VARCHAR2(2),
25 element_value NUMBER,
26 currency_code VARCHAR2(10),
27 pay_basis VARCHAR2(10),
28 employee_id NUMBER,
29 primary_employee_flag VARCHAR2(1),
30 global_default_flag VARCHAR2(1),
31 assignment_default_rule_id NUMBER,
32 modify_flag VARCHAR2(1),
33 delete_flag VARCHAR2(1) );
34
35 TYPE g_assign_tbl_type IS TABLE OF g_assign_rec_type
36 INDEX BY BINARY_INTEGER;
37
38 -- Number array.
39 TYPE Number_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER ;
40
41 -- Character array.
42 TYPE Character_tbl_type IS TABLE OF VARCHAR2(150) INDEX BY BINARY_INTEGER ;
43
44 -- Date array.
45 TYPE Date_tbl_type IS TABLE OF DATE INDEX BY BINARY_INTEGER ;
46
47 g_assign g_assign_tbl_type;
48 g_num_assign NUMBER;
49 g_validation_mode VARCHAR2(15);
50
51 /* ------------------Private Procedure Declarations----------------------- */
52
53 PROCEDURE MODIFY_ASSIGNMENT_WS
54 ( p_return_status OUT NOCOPY VARCHAR2,
55 p_position_assignment_id IN OUT NOCOPY NUMBER,
56 p_data_extract_id IN NUMBER,
57 p_worksheet_id IN NUMBER,
58 p_position_id IN NUMBER,
59 p_assignment_type IN VARCHAR2,
60 p_attribute_id IN NUMBER,
61 p_attribute_value_id IN NUMBER,
62 p_attribute_value IN VARCHAR2,
63 p_pay_element_id IN NUMBER,
64 p_pay_element_option_id IN NUMBER,
65 p_effective_start_date IN DATE,
66 p_effective_end_date IN DATE,
67 p_element_value_type IN VARCHAR2,
68 p_element_value IN NUMBER,
69 p_currency_code IN VARCHAR2,
70 p_pay_basis IN VARCHAR2,
71 p_employee_id IN NUMBER,
72 p_primary_employee_flag IN VARCHAR2,
73 p_global_default_flag IN VARCHAR2,
74 p_assignment_default_rule_id IN NUMBER,
75 p_modify_flag IN VARCHAR2,
76 p_rowid IN OUT NOCOPY VARCHAR2
77 );
78
79 PROCEDURE CREATE_ASSIGNMENT_POSITION
80 ( p_return_status OUT NOCOPY VARCHAR2,
81 p_worksheet_id IN NUMBER,
82 p_data_extract_id IN NUMBER,
83 p_position_id IN NUMBER,
84 p_position_start_date IN DATE,
85 p_position_end_date IN DATE,
86 p_ruleset_id IN NUMBER
87 );
88
89 PROCEDURE CREATE_DISTRIBUTION_POSITION
90 ( p_return_status OUT NOCOPY VARCHAR2,
91 p_worksheet_id IN NUMBER,
92 p_data_extract_id IN NUMBER,
93 p_position_id IN NUMBER,
94 p_position_start_date IN DATE,
95 p_position_end_date IN DATE,
96 p_ruleset_id IN NUMBER
97 );
98
99 PROCEDURE CREATE_ELEMENT_ASSIGNMENT
100 ( p_return_status OUT NOCOPY VARCHAR2,
101 p_worksheet_id IN NUMBER,
102 p_data_extract_id IN NUMBER,
103 p_position_id IN NUMBER,
104 p_position_start_date IN DATE,
105 p_position_end_date IN DATE
106 );
107
108 /* For Bug 4644241 --> Reverting Back to the old fix
109 This will maintain the old functionality */
110 PROCEDURE Apply_Global_Default
111 ( p_return_status OUT NOCOPY VARCHAR2,
112 p_worksheet_id IN NUMBER,
113 p_data_extract_id IN NUMBER,
114 p_position_id IN NUMBER,
115 p_position_start_date IN DATE,
116 p_position_end_date IN DATE
117 );
118
119
120 PROCEDURE OUTPUT_MESSAGE_TO_TABLE
121 ( p_worksheet_id IN NUMBER,
122 p_return_status OUT NOCOPY VARCHAR2
123 );
124
125 PROCEDURE VALIDATE_POSITION
126 ( p_worksheet_id IN NUMBER,
127 p_position_id IN NUMBER,
128 p_name IN VARCHAR2,
129 p_employee_number IN VARCHAR2,
130 p_data_extract_id IN NUMBER,
131 p_root_budget_group_id IN NUMBER,
132 p_set_of_books_id IN NUMBER,
133 p_budget_calendar_id IN NUMBER,
134 p_chart_of_accounts_id IN NUMBER,
135 p_position_start_date IN DATE,
136 p_position_end_date IN DATE,
137 p_startdate_pp IN DATE,
138 p_enddate_cy IN DATE,
139 p_effective_start_date IN DATE,
140 p_effective_end_date IN DATE,
141 p_error_flag IN OUT NOCOPY VARCHAR2,
142 p_return_status OUT NOCOPY VARCHAR2
143 );
144
145 PROCEDURE VALIDATE_DISTRIBUTION
146 ( p_position_id IN NUMBER,
147 p_worksheet_id IN NUMBER,
148 p_name IN VARCHAR2,
149 p_employee_number IN VARCHAR2,
150 p_position_flag IN OUT NOCOPY VARCHAR2,
151 p_data_extract_id IN NUMBER,
152 p_root_budget_group_id IN NUMBER,
153 p_set_of_books_id IN NUMBER,
154 p_budget_calendar_id IN NUMBER,
155 p_chart_of_accounts_id IN NUMBER,
156 p_startdate_pp IN DATE,
157 p_enddate_cy IN DATE,
158 p_effective_start_date IN DATE,
159 p_effective_end_date IN DATE,
160 p_error_flag IN OUT NOCOPY VARCHAR2,
161 p_return_status OUT NOCOPY VARCHAR2
162 );
163
164 PROCEDURE SET_POS_HEADING
165 ( p_position_flag IN OUT NOCOPY VARCHAR2,
166 p_position_name IN VARCHAR2,
167 p_employee_number IN VARCHAR2,
168 p_error_flag IN OUT NOCOPY VARCHAR2
169 );
170
171 -- Bug 1308558. Mass Position Assginment Rules
172 -- new api created for applying the Element and Attribute
173 -- assignments to positions
174 /* Bug 4273099 moved this to package spec
175 PROCEDURE Apply_Position_Default_Rules
176 ( p_api_version IN NUMBER,
177 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
178 p_commit IN VARCHAR2 := FND_API.G_FALSE,
179 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
180 x_return_status OUT NOCOPY VARCHAR2,
181 x_msg_count OUT NOCOPY NUMBER,
182 x_msg_data OUT NOCOPY VARCHAR2,
183 p_position_assignment_id IN OUT NOCOPY NUMBER,
184 p_data_extract_id IN NUMBER,
185 p_position_id IN NUMBER,
186 p_assignment_type IN VARCHAR2,
187 p_attribute_id IN NUMBER,
188 p_attribute_value_id IN NUMBER,
189 p_attribute_value IN VARCHAR2,
190 p_pay_element_id IN NUMBER,
191 p_pay_element_option_id IN NUMBER,
192 p_effective_start_date IN DATE,
193 p_effective_end_date IN DATE,
194 p_element_value_type IN VARCHAR2,
195 p_element_value IN NUMBER,
196 p_currency_code IN VARCHAR2,
197 p_pay_basis IN VARCHAR2,
198 p_employee_id IN NUMBER,
199 p_primary_employee_flag IN VARCHAR2,
200 p_global_default_flag IN VARCHAR2,
201 p_assignment_default_rule_id IN NUMBER,
202 p_modify_flag IN VARCHAR2,
203 p_mode IN VARCHAR2 := 'R'
204 );
205 */
206
207 /*===========================================================================+
208 | PROCEDURE pd |
209 +===========================================================================*/
210 -- API to print debug information, used during development only.
211 PROCEDURE pd( p_message IN VARCHAR2) IS
212 BEGIN
213 NULL ;
214 --DBMS_OUTPUT.Put_Line(p_message) ;
215 END pd ;
216 /*---------------------------------------------------------------------------*/
217
218
219 /*----------------------- Table Handler Procedures ----------------------- */
220 PROCEDURE INSERT_ROW (
221 p_api_version in number,
222 p_init_msg_list in varchar2 := fnd_api.g_false,
223 p_commit in varchar2 := fnd_api.g_false,
224 p_validation_level in number := fnd_api.g_valid_level_full,
225 p_return_status OUT NOCOPY varchar2,
226 p_msg_count OUT NOCOPY number,
227 p_msg_data OUT NOCOPY varchar2,
228 p_rowid in OUT NOCOPY varchar2,
229 p_position_id in number,
230 -- de by org
231 p_organization_id in number := NULL,
232 p_data_extract_id in number,
233 p_position_definition_id in number,
234 p_hr_position_id in number,
235 p_hr_employee_id in number := fnd_api.g_miss_num ,
236 p_business_group_id in number,
237 p_budget_group_id in number := fnd_api.g_miss_num ,
238 p_effective_start_date in date,
239 p_effective_end_date in date,
240 p_set_of_books_id in number,
241 p_vacant_position_flag in varchar2,
242 p_availability_status in varchar2 := fnd_api.g_miss_char ,
243 p_transaction_id in number := fnd_api.g_miss_num ,
244 p_transaction_status in varchar2 := fnd_api.g_miss_char ,
245 p_new_position_flag in varchar2 := fnd_api.g_miss_char ,
246 p_attribute1 in varchar2,
247 p_attribute2 in varchar2,
248 p_attribute3 in varchar2,
249 p_attribute4 in varchar2,
250 p_attribute5 in varchar2,
251 p_attribute6 in varchar2,
252 p_attribute7 in varchar2,
253 p_attribute8 in varchar2,
254 p_attribute9 in varchar2,
255 p_attribute10 in varchar2,
256 p_attribute11 in varchar2,
257 p_attribute12 in varchar2,
258 p_attribute13 in varchar2,
259 p_attribute14 in varchar2,
260 p_attribute15 in varchar2,
261 p_attribute16 in varchar2,
262 p_attribute17 in varchar2,
263 p_attribute18 in varchar2,
264 p_attribute19 in varchar2,
265 p_attribute20 in varchar2,
266 p_attribute_category in varchar2,
267 p_name in varchar2,
268 p_mode in varchar2 := 'R'
269
270 ) is
271 cursor C is select ROWID from PSB_POSITIONS
272 where POSITION_ID = P_POSITION_ID;
273 P_LAST_UPDATE_DATE DATE;
274 P_LAST_UPDATED_BY NUMBER;
275 P_LAST_UPDATE_LOGIN NUMBER;
276 --
277 l_api_name CONSTANT VARCHAR2(30) := 'Insert_Row' ;
278 l_api_version CONSTANT NUMBER := 1.0 ;
279 l_return_status VARCHAR2(1);
280 --
281 l_hr_employee_id psb_positions.hr_employee_id%TYPE ;
282 l_budget_group_id psb_positions.budget_group_id%TYPE;
283 l_availability_status psb_positions.availability_status%TYPE;
284 l_transaction_id psb_positions.transaction_id%TYPE;
285 l_transaction_status psb_positions.transaction_status%TYPE;
286 l_new_position_flag psb_positions.new_position_flag%TYPE;
287 --
288 BEGIN
289 --
290 SAVEPOINT Insert_Row ;
291 --
292 if FND_API.to_Boolean (p_init_msg_list) then
293 FND_MSG_PUB.initialize;
294 end if;
295 --
296 p_return_status := FND_API.G_RET_STS_SUCCESS ;
297 --
298 P_LAST_UPDATE_DATE := SYSDATE;
299 if(P_MODE = 'I') then
300 P_LAST_UPDATED_BY := 1;
301 P_LAST_UPDATE_LOGIN := 0;
302 elsif (P_MODE = 'R') then
303 P_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
304 if P_LAST_UPDATED_BY is NULL then
305 P_LAST_UPDATED_BY := -1;
306 end if;
307 P_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
308 if P_LAST_UPDATE_LOGIN is NULL then
309 P_LAST_UPDATE_LOGIN := -1;
310 end if;
311 else
312 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
313 FND_MSG_PUB.Add ;
314 raise FND_API.G_EXC_ERROR;
315 end if;
316 --
317
318 -- Resolve p_hr_employee_id parameter.
319 IF p_hr_employee_id = FND_API.G_MISS_NUM THEN
320 l_hr_employee_id := NULL ;
321 ELSE
322 l_hr_employee_id := p_hr_employee_id ;
323 END IF;
324
325 -- Resolve p_budget_group_id parameter.
326 IF p_budget_group_id = FND_API.G_MISS_NUM THEN
327 l_budget_group_id := NULL ;
328 ELSE
329 l_budget_group_id := p_budget_group_id ;
330 END IF;
331
332 -- Resolve p_availability_status , p_transaction_id and p_transaction_status
333 -- parameters.
334
335 IF p_availability_status = FND_API.G_MISS_CHAR THEN
336 l_availability_status := NULL ;
337 ELSE
338 l_availability_status := p_availability_status ;
339 END IF;
340
341 IF p_transaction_id = FND_API.G_MISS_NUM THEN
342 l_transaction_id := NULL ;
343 ELSE
344 l_transaction_id := p_transaction_id ;
345 END IF;
346
347 IF p_transaction_status = FND_API.G_MISS_CHAR THEN
348 l_transaction_status := NULL ;
349 ELSE
350 l_transaction_status := p_transaction_status ;
351 END IF;
352
353 -- Resolve p_new_position_flag parameter.
354 IF p_new_position_flag = FND_API.G_MISS_CHAR THEN
355 l_new_position_flag := NULL ;
356 ELSE
357 l_new_position_flag := p_new_position_flag ;
358 END IF;
359
360 insert into PSB_POSITIONS (
361 position_id ,
362 organization_id ,
363 data_extract_id ,
364 position_definition_id,
365 hr_position_id ,
366 hr_employee_id ,
367 business_group_id ,
368 budget_group_id ,
369 effective_start_date ,
370 effective_end_date ,
371 set_of_books_id ,
372 vacant_position_flag ,
373 availability_status ,
374 transaction_id ,
375 transaction_status ,
376 new_position_flag ,
377 attribute1 ,
378 attribute2 ,
379 attribute3 ,
380 attribute4 ,
381 attribute5 ,
382 attribute6 ,
383 attribute7 ,
384 attribute8 ,
385 attribute9 ,
386 attribute10 ,
387 attribute11 ,
388 attribute12 ,
389 attribute13 ,
390 attribute14 ,
391 attribute15 ,
392 attribute16 ,
393 attribute17 ,
394 attribute18 ,
395 attribute19 ,
396 attribute20 ,
397 attribute_category ,
398 name ,
399 creation_date ,
400 created_by ,
401 last_update_date ,
402 last_updated_by ,
403 last_update_login
404 )
405 values
406 (
407 p_position_id ,
408 p_organization_id ,
409 p_data_extract_id ,
410 p_position_definition_id ,
411 p_hr_position_id ,
412 l_hr_employee_id ,
413 p_business_group_id ,
414 l_budget_group_id ,
415 p_effective_start_date,
416 p_effective_end_date ,
417 p_set_of_books_id ,
418 p_vacant_position_flag,
419 l_availability_status ,
420 l_transaction_id ,
421 l_transaction_status ,
422 l_new_position_flag ,
423 p_attribute1 ,
424 p_attribute2 ,
425 p_attribute3 ,
426 p_attribute4 ,
427 p_attribute5 ,
428 p_attribute6 ,
429 p_attribute7 ,
430 p_attribute8 ,
431 p_attribute9 ,
432 p_attribute10 ,
433 p_attribute11 ,
434 p_attribute12 ,
435 p_attribute13 ,
436 p_attribute14 ,
437 p_attribute15 ,
438 p_attribute16 ,
439 p_attribute17 ,
440 p_attribute18 ,
441 p_attribute19 ,
442 p_attribute20 ,
443 p_attribute_category ,
444 p_name ,
445 p_last_update_date ,
446 p_last_updated_by ,
447 p_last_update_date ,
448 p_last_updated_by ,
449 p_last_update_login
450 );
451 --
452 open c;
453 fetch c into P_ROWID;
454 if (c%notfound) then
455 close c;
456 raise FND_API.G_EXC_ERROR ;
457 end if;
458 close c;
459 --
460 --
461 -- Standard check of p_commit.
462 if FND_API.to_Boolean (p_commit) then
463 commit work;
464 end if;
465 -- Standard call to get message count and if count is 1, get message info.
466 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
467 p_data => p_msg_data);
468 --
469 EXCEPTION
470 --
471 when FND_API.G_EXC_ERROR then
472 --
473 rollback to INSERT_ROW ;
474 p_return_status := FND_API.G_RET_STS_ERROR;
475 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
476 p_data => p_msg_data);
477 --
478 when FND_API.G_EXC_UNEXPECTED_ERROR then
479 --
480 rollback to INSERT_ROW ;
481 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
482 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
483 p_data => p_msg_data);
484 --
485 when OTHERS then
486 --
487 rollback to INSERT_ROW ;
488 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
489 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) then
490 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
491 l_api_name);
492 END if;
493 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
494 p_data => p_msg_data);
495 --
496 END INSERT_ROW;
497 --
498 /* ----------------------------------------------------------------------- */
499
500 PROCEDURE LOCK_ROW (
501 p_api_version in number,
502 p_init_msg_list in varchar2 := fnd_api.g_false,
503 p_commit in varchar2 := fnd_api.g_false,
504 p_validation_level in number := fnd_api.g_valid_level_full,
505 p_return_status OUT NOCOPY varchar2,
506 p_msg_count OUT NOCOPY number,
507 p_msg_data OUT NOCOPY varchar2,
508 p_row_locked OUT NOCOPY varchar2,
509 p_position_id in number,
510 p_data_extract_id in number,
511 p_position_definition_id in number,
512 p_hr_position_id in number,
513 p_business_group_id in number,
514 p_effective_start_date in date,
515 p_effective_end_date in date,
516 p_set_of_books_id in number,
517 p_vacant_position_flag in varchar2,
518 p_attribute1 in varchar2,
519 p_attribute2 in varchar2,
520 p_attribute3 in varchar2,
521 p_attribute4 in varchar2,
522 p_attribute5 in varchar2,
523 p_attribute6 in varchar2,
524 p_attribute7 in varchar2,
525 p_attribute8 in varchar2,
526 p_attribute9 in varchar2,
527 p_attribute10 in varchar2,
528 p_attribute11 in varchar2,
529 p_attribute12 in varchar2,
530 p_attribute13 in varchar2,
531 p_attribute14 in varchar2,
532 p_attribute15 in varchar2,
533 p_attribute16 in varchar2,
534 p_attribute17 in varchar2,
535 p_attribute18 in varchar2,
536 p_attribute19 in varchar2,
537 p_attribute20 in varchar2,
538 p_attribute_category in varchar2,
539 p_name in varchar2
540
541 ) is
542 cursor c1 is select
543 position_id ,
544 data_extract_id ,
545 position_definition_id ,
546 hr_position_id ,
547 business_group_id ,
548 effective_start_date,
549 effective_end_date ,
550 set_of_books_id ,
551 vacant_position_flag,
552 attribute1 ,
553 attribute2 ,
554 attribute3 ,
555 attribute4 ,
556 attribute5 ,
557 attribute6 ,
558 attribute7 ,
559 attribute8 ,
560 attribute9 ,
561 attribute10 ,
562 attribute11 ,
563 attribute12 ,
564 attribute13 ,
565 attribute14 ,
566 attribute15 ,
567 attribute16 ,
568 attribute17 ,
569 attribute18 ,
570 attribute19 ,
571 attribute20 ,
572 attribute_category ,
573 name
574 from PSB_POSITIONS
575 where position_id = P_position_id
576 for update of position_id nowait;
577 tlinfo c1%rowtype;
578 --
579 l_api_name CONSTANT VARCHAR2(30) := 'Lock_Row' ;
580 l_api_version CONSTANT NUMBER := 1.0 ;
581 l_return_status VARCHAR2(1);
582 --
583 BEGIN
584 --
585 SAVEPOINT Lock_Row ;
586 --
587 if FND_API.to_Boolean (p_init_msg_list) then
588 FND_MSG_PUB.initialize;
589 end if;
590 --
591 p_return_status := FND_API.G_RET_STS_SUCCESS ;
592 p_row_locked := FND_API.G_TRUE ;
593 --
594 open c1;
595 fetch c1 into tlinfo;
596 if (c1%notfound) then
597 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
598 fnd_msg_pub.add ;
599 close c1;
600 raise fnd_api.g_exc_error ;
601 end if;
602 close c1;
603
604 --
605 if ( (tlinfo.position_id = P_position_id)
606 AND (tlinfo.data_extract_id = P_data_extract_id)
607 AND (tlinfo.effective_start_date = P_effective_start_date)
608
609 AND ((tlinfo.position_definition_id = P_position_definition_id)
610 OR ((tlinfo.position_definition_id is null)
611 AND (P_position_definition_id is null)))
612
613 AND ((tlinfo.hr_position_id = P_hr_position_id)
614 OR ((tlinfo.hr_position_id is null)
615 AND (P_hr_position_id is null)))
616
617 AND ((tlinfo.business_group_id = P_business_group_id )
618 OR ((tlinfo.business_group_id is null)
619 AND (P_business_group_id is null)))
620
621 AND ((tlinfo.effective_end_date = P_effective_end_date)
622 OR ((tlinfo.effective_end_date is null)
623 AND (P_effective_end_date is null)))
624
625 AND ((tlinfo.set_of_books_id = P_set_of_books_id)
626 OR ((tlinfo.set_of_books_id is null)
627 AND (P_set_of_books_id is null)))
628
629 AND ((tlinfo.name = P_name)
630 OR ((tlinfo.name is null)
631 AND (P_name is null)))
632
633 AND ((tlinfo.vacant_position_flag = P_vacant_position_flag)
634 OR ((tlinfo.vacant_position_flag is null)
635 AND (P_vacant_position_flag is null)))
636
637 AND ((tlinfo.attribute_category = P_attribute_category)
638 OR ((tlinfo.attribute_category is null)
639 AND (P_attribute_category is null)))
640
641 AND ((tlinfo.ATTRIBUTE1 = P_ATTRIBUTE1)
642 OR ((tlinfo.ATTRIBUTE1 is null)
643 AND (P_ATTRIBUTE1 is null)))
644 AND ((tlinfo.ATTRIBUTE2 = P_ATTRIBUTE2)
645 OR ((tlinfo.ATTRIBUTE2 is null)
646 AND (P_ATTRIBUTE2 is null)))
647 AND ((tlinfo.ATTRIBUTE3 = P_ATTRIBUTE3)
648 OR ((tlinfo.ATTRIBUTE3 is null)
649 AND (P_ATTRIBUTE3 is null)))
650 AND ((tlinfo.ATTRIBUTE4 = P_ATTRIBUTE4)
651 OR ((tlinfo.ATTRIBUTE4 is null)
652 AND (P_ATTRIBUTE4 is null)))
653 AND ((tlinfo.ATTRIBUTE5 = P_ATTRIBUTE5)
654 OR ((tlinfo.ATTRIBUTE5 is null)
655 AND (P_ATTRIBUTE5 is null)))
656 AND ((tlinfo.ATTRIBUTE6 = P_ATTRIBUTE6)
657 OR ((tlinfo.ATTRIBUTE6 is null)
658 AND (P_ATTRIBUTE6 is null)))
659 AND ((tlinfo.ATTRIBUTE7 = P_ATTRIBUTE7)
660 OR ((tlinfo.ATTRIBUTE7 is null)
661 AND (P_ATTRIBUTE7 is null)))
662 AND ((tlinfo.ATTRIBUTE8 = P_ATTRIBUTE8)
663 OR ((tlinfo.ATTRIBUTE8 is null)
664 AND (P_ATTRIBUTE8 is null)))
665 AND ((tlinfo.ATTRIBUTE9 = P_ATTRIBUTE9)
666 OR ((tlinfo.ATTRIBUTE9 is null)
667 AND (P_ATTRIBUTE9 is null)))
668 AND ((tlinfo.ATTRIBUTE10 = P_ATTRIBUTE10)
669 OR ((tlinfo.ATTRIBUTE10 is null)
670 AND (P_ATTRIBUTE10 is null)))
671 AND ((tlinfo.ATTRIBUTE11 = P_ATTRIBUTE11)
672 OR ((tlinfo.ATTRIBUTE11 is null)
673 AND (P_ATTRIBUTE11 is null)))
674 AND ((tlinfo.ATTRIBUTE12 = P_ATTRIBUTE12)
675 OR ((tlinfo.ATTRIBUTE12 is null)
676 AND (P_ATTRIBUTE12 is null)))
677 AND ((tlinfo.ATTRIBUTE13 = P_ATTRIBUTE13)
678 OR ((tlinfo.ATTRIBUTE13 is null)
679 AND (P_ATTRIBUTE13 is null)))
680 AND ((tlinfo.ATTRIBUTE14 = P_ATTRIBUTE14)
681 OR ((tlinfo.ATTRIBUTE14 is null)
682 AND (P_ATTRIBUTE14 is null)))
683 AND ((tlinfo.ATTRIBUTE15 = P_ATTRIBUTE15)
684 OR ((tlinfo.ATTRIBUTE15 is null)
685 AND (P_ATTRIBUTE15 is null)))
686 AND ((tlinfo.ATTRIBUTE16 = P_ATTRIBUTE16)
687 OR ((tlinfo.ATTRIBUTE16 is null)
688 AND (P_ATTRIBUTE16 is null)))
689 AND ((tlinfo.ATTRIBUTE17 = P_ATTRIBUTE17)
690 OR ((tlinfo.ATTRIBUTE17 is null)
691 AND (P_ATTRIBUTE17 is null)))
692 AND ((tlinfo.ATTRIBUTE18 = P_ATTRIBUTE18)
693 OR ((tlinfo.ATTRIBUTE18 is null)
694 AND (P_ATTRIBUTE18 is null)))
695 AND ((tlinfo.ATTRIBUTE19 = P_ATTRIBUTE19)
696 OR ((tlinfo.ATTRIBUTE19 is null)
697 AND (P_ATTRIBUTE19 is null)))
698 AND ((tlinfo.ATTRIBUTE20 = P_ATTRIBUTE20)
699 OR ((tlinfo.ATTRIBUTE20 is null)
700 AND (P_ATTRIBUTE20 is null)))
701
702 ) then
703 null;
704 else
705 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
706 fnd_msg_pub.add ;
707 raise fnd_api.g_exc_error ;
708 end if;
709
710 EXCEPTION
711 when app_exception.record_lock_exception then
712 --
713 rollback to LOCK_ROW ;
714 p_row_locked := FND_API.G_FALSE ;
715 p_return_status := FND_API.G_RET_STS_ERROR;
716 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
717 p_data => p_msg_data);
718 --
719 when FND_API.G_EXC_ERROR then
720 --
721 rollback to LOCK_ROW ;
722 p_return_status := FND_API.G_RET_STS_ERROR;
723 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
724 p_data => p_msg_data);
725 --
726 when FND_API.G_EXC_UNEXPECTED_ERROR then
727 --
728 rollback to LOCK_ROW ;
729 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
730 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
731 p_data => p_msg_data);
732 --
733 when OTHERS then
734 --
735 rollback to LOCK_ROW ;
736 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
737 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) then
738 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
739 l_api_name);
740 END if;
741 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
742 p_data => p_msg_data);
743 --
744 END LOCK_ROW;
745 --
746 /* ----------------------------------------------------------------------- */
747
748 PROCEDURE UPDATE_ROW (
749 p_api_version in number,
750 p_init_msg_list in varchar2 := fnd_api.g_false,
751 p_commit in varchar2 := fnd_api.g_false,
752 p_validation_level in number := fnd_api.g_valid_level_full,
753 p_return_status OUT NOCOPY varchar2,
754 p_msg_count OUT NOCOPY number,
755 p_msg_data OUT NOCOPY varchar2,
756 p_position_id in number,
757 -- de by org
758 p_organization_id in number := NULL,
759 p_data_extract_id in number,
760 p_position_definition_id in number,
761 p_hr_position_id in number,
762 p_hr_employee_id in number := fnd_api.g_miss_num ,
763 p_business_group_id in number,
764 p_budget_group_id in number := fnd_api.g_miss_num ,
765 p_effective_start_date in date,
766 p_effective_end_date in date,
767 p_set_of_books_id in number,
768 p_vacant_position_flag in varchar2,
769 p_availability_status in varchar2 := fnd_api.g_miss_char ,
770 p_transaction_id in number := fnd_api.g_miss_num ,
771 p_transaction_status in varchar2 := fnd_api.g_miss_char ,
772 p_new_position_flag in varchar2 := fnd_api.g_miss_char ,
773 p_attribute1 in varchar2,
774 p_attribute2 in varchar2,
775 p_attribute3 in varchar2,
776 p_attribute4 in varchar2,
777 p_attribute5 in varchar2,
778 p_attribute6 in varchar2,
779 p_attribute7 in varchar2,
780 p_attribute8 in varchar2,
781 p_attribute9 in varchar2,
782 p_attribute10 in varchar2,
783 p_attribute11 in varchar2,
784 p_attribute12 in varchar2,
785 p_attribute13 in varchar2,
786 p_attribute14 in varchar2,
787 p_attribute15 in varchar2,
788 p_attribute16 in varchar2,
789 p_attribute17 in varchar2,
790 p_attribute18 in varchar2,
791 p_attribute19 in varchar2,
792 p_attribute20 in varchar2,
793 p_attribute_category in varchar2,
794 p_name in varchar2,
795 p_mode in varchar2 := 'R'
796
797 ) is
798 P_LAST_UPDATE_DATE DATE;
799 P_LAST_UPDATED_BY NUMBER;
800 P_LAST_UPDATE_LOGIN NUMBER;
801 --
802 l_api_name CONSTANT VARCHAR2(30) := 'Update Row';
803 l_api_version CONSTANT NUMBER := 1.0 ;
804 l_return_status VARCHAR2(1);
805 --
806 l_hr_employee_id psb_positions.hr_employee_id%TYPE ;
807 l_budget_group_id psb_positions.budget_group_id%TYPE ;
808 l_availability_status psb_positions.availability_status%TYPE;
809 l_transaction_id psb_positions.transaction_id%TYPE;
810 l_transaction_status psb_positions.transaction_status%TYPE;
811 l_new_position_flag psb_positions.new_position_flag%TYPE;
812 --
813
814 BEGIN
815 --
816 SAVEPOINT Update_Row ;
817 --
818 if FND_API.to_Boolean (p_init_msg_list) then
819 FND_MSG_PUB.initialize;
820 end if;
821
822 -- Initialize API return status to success
823
824 p_return_status := FND_API.G_RET_STS_SUCCESS ;
825
826 --
827 P_LAST_UPDATE_DATE := SYSDATE;
828 if(P_MODE = 'I') then
829 P_LAST_UPDATED_BY := 1;
830 P_LAST_UPDATE_LOGIN := 0;
831 elsif (P_MODE = 'R') then
832 P_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
833 if P_LAST_UPDATED_BY is NULL then
834 P_LAST_UPDATED_BY := -1;
835 end if;
836 P_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
837 if P_LAST_UPDATE_LOGIN is NULL then
838 P_LAST_UPDATE_LOGIN := -1;
839 end if;
840 else
841 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
842 FND_MSG_PUB.Add ;
843 raise FND_API.G_EXC_ERROR ;
844 end if;
845
846
847 -- Resolve p_hr_employee_id parameter.
848 IF p_hr_employee_id = FND_API.G_MISS_NUM THEN
849 l_hr_employee_id := NULL ;
850 ELSE
851 l_hr_employee_id := p_hr_employee_id ;
852 END IF;
853
854 -- Resolve p_budget_group_id parameter.
855 IF p_budget_group_id = FND_API.G_MISS_NUM THEN
856 l_budget_group_id := NULL ;
857 ELSE
858 l_budget_group_id := p_budget_group_id ;
859 END IF;
860
861 -- Resolve p_availability_status , p_transaction_id and p_transaction_status
862 -- parameters.
863
864 IF p_availability_status = FND_API.G_MISS_CHAR THEN
865 l_availability_status := NULL ;
866 ELSE
867 l_availability_status := p_availability_status ;
868 END IF;
869
870 IF p_transaction_id = FND_API.G_MISS_NUM THEN
871 l_transaction_id := NULL ;
872 ELSE
873 l_transaction_id := p_transaction_id ;
874 END IF;
875
876 IF p_transaction_status = FND_API.G_MISS_CHAR THEN
877 l_transaction_status := NULL ;
878 ELSE
879 l_transaction_status := p_transaction_status ;
880 END IF;
881
882 -- Resolve p_new_position_flag parameter.
883 IF p_new_position_flag = FND_API.G_MISS_CHAR THEN
884 l_new_position_flag := NULL ;
885 ELSE
886 l_new_position_flag := p_new_position_flag ;
887 END IF;
888
889 --
890 -- do the update of the record
891 --
892 update PSB_POSITIONS set
893 position_id = p_position_id ,
894 -- de by org
895 organization_id = nvl(p_organization_id,organization_id),
896 data_extract_id = p_data_extract_id ,
897 position_definition_id = p_position_definition_id ,
898 hr_position_id = p_hr_position_id ,
899 hr_employee_id = l_hr_employee_id ,
900 business_group_id = p_business_group_id ,
901 budget_group_id = l_budget_group_id ,
902 effective_start_Date = p_effective_start_date ,
903 effective_end_date = p_effective_end_date ,
904 set_of_books_id = p_set_of_books_id ,
905 vacant_position_flag = p_vacant_position_flag ,
906 availability_status = l_availability_status ,
907 transaction_id = l_transaction_id ,
908 transaction_status = l_transaction_status ,
909 new_position_flag = l_new_position_flag ,
910 attribute1 = p_attribute1 ,
911 attribute2 = p_attribute2 ,
912 attribute3 = p_attribute3 ,
913 attribute4 = p_attribute4 ,
914 attribute5 = p_attribute5 ,
915 attribute6 = p_attribute6 ,
916 attribute7 = p_attribute7 ,
917 attribute8 = p_attribute8 ,
918 attribute9 = p_attribute9 ,
919 attribute10 = p_attribute10 ,
920 attribute11 = p_attribute11 ,
921 attribute12 = p_attribute12 ,
922 attribute13 = p_attribute13 ,
923 attribute14 = p_attribute14 ,
924 attribute15= p_attribute15 ,
925 attribute16 = p_attribute16 ,
926 attribute17 = p_attribute17 ,
927 attribute18 = p_attribute18 ,
928 attribute19 = p_attribute19 ,
929 attribute20 = p_attribute20 ,
930 attribute_category = p_attribute_category ,
931 name = p_name ,
932 last_update_date = p_last_update_date,
933 last_updated_by = p_last_updated_by ,
934 last_update_login = p_last_update_login
935 where position_id = p_position_id
936 ;
937 if (sql%notfound) then
938 -- raise no_data_found;
939 raise FND_API.G_EXC_ERROR ;
940 end if;
941 --
942 -- Standard check of p_commit.
943 --
944 if FND_API.to_Boolean (p_commit) then
945 commit work;
946 end if;
947
948 -- Standard call to get message count and if count is 1, get message info.
949
950 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
951 p_data => p_msg_data);
952 --
953 EXCEPTION
954
955 when FND_API.G_EXC_ERROR then
956 --
957 rollback to Update_Row ;
958 p_return_status := FND_API.G_RET_STS_ERROR;
959 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
960 p_data => p_msg_data);
961 --
962 when FND_API.G_EXC_UNEXPECTED_ERROR then
963 --
964 rollback to Update_Row ;
965 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
966 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
967 p_data => p_msg_data);
968 --
969 when OTHERS then
970 --
971 rollback to Update_Row ;
972 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
973 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) then
974 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
975 l_api_name);
976 end if;
977 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
978 p_data => p_msg_data);
979 --
980
981 END UPDATE_ROW;
982 --
983 /* ----------------------------------------------------------------------- */
984
985 PROCEDURE ADD_ROW (
986 p_api_version in number,
987 p_init_msg_list in varchar2 := fnd_api.g_false,
988 p_commit in varchar2 := fnd_api.g_false,
989 p_validation_level in number := fnd_api.g_valid_level_full,
990 p_return_status OUT NOCOPY varchar2,
991 p_msg_count OUT NOCOPY number,
992 p_msg_data OUT NOCOPY varchar2,
993 p_rowid in OUT NOCOPY varchar2,
994 p_position_id in number,
995 p_organization_id in number,
996 p_data_extract_id in number,
997 p_position_definition_id in number,
998 p_hr_position_id in number,
999 p_business_group_id in number,
1000 p_effective_start_date in date,
1001 p_effective_end_date in date,
1002 p_set_of_books_id in number,
1003 p_vacant_position_flag in varchar2,
1004 p_attribute1 in varchar2,
1005 p_attribute2 in varchar2,
1006 p_attribute3 in varchar2,
1007 p_attribute4 in varchar2,
1008 p_attribute5 in varchar2,
1009 p_attribute6 in varchar2,
1010 p_attribute7 in varchar2,
1011 p_attribute8 in varchar2,
1012 p_attribute9 in varchar2,
1013 p_attribute10 in varchar2,
1014 p_attribute11 in varchar2,
1015 p_attribute12 in varchar2,
1016 p_attribute13 in varchar2,
1017 p_attribute14 in varchar2,
1018 p_attribute15 in varchar2,
1019 p_attribute16 in varchar2,
1020 p_attribute17 in varchar2,
1021 p_attribute18 in varchar2,
1022 p_attribute19 in varchar2,
1023 p_attribute20 in varchar2,
1024 p_attribute_category in varchar2,
1025 p_name in varchar2,
1026 p_mode in varchar2 := 'R'
1027
1028
1029 ) is
1030 cursor c1 is select rowid from PSB_POSITIONS
1031 where position_id = p_position_id
1032 ;
1033 dummy c1%rowtype;
1034 --
1035 l_api_name CONSTANT VARCHAR2(30) := 'Add Row' ;
1036 l_api_version CONSTANT NUMBER := 1.0 ;
1037 --
1038 BEGIN
1039 --
1040 SAVEPOINT Add_Row ;
1041 --
1042 -- Initialize message list if p_init_msg_list is set to TRUE.
1043 --
1044 if FND_API.to_Boolean (p_init_msg_list) then
1045 FND_MSG_PUB.initialize;
1046 end if;
1047 --
1048 p_return_status := FND_API.G_RET_STS_SUCCESS ;
1049 --
1050 open c1;
1051 fetch c1 into dummy;
1052 if (c1%notfound) then
1053 close c1;
1054 INSERT_ROW (
1055 p_api_version => p_api_version,
1056 p_init_msg_list => p_init_msg_list,
1057 p_commit => p_commit,
1058 p_validation_level => p_validation_level,
1059 p_return_status => p_return_status,
1060 p_msg_count => p_msg_count,
1061 p_msg_data => p_msg_data,
1062 p_rowid => p_rowid,
1063 p_position_id => p_position_id,
1064 p_organization_id => p_organization_id,
1065 p_data_extract_id => p_data_extract_id,
1066 p_position_definition_id => p_position_definition_id ,
1067 p_hr_position_id => p_hr_position_id,
1068 p_business_group_id => p_business_group_id,
1069 p_effective_start_date => p_effective_start_date,
1070 p_effective_end_date => p_effective_end_date,
1071 p_set_of_books_id => p_set_of_books_id,
1072 p_vacant_position_flag => p_vacant_position_flag,
1073 p_attribute1 => p_attribute1 ,
1074 p_attribute2 => p_attribute2 ,
1075 p_attribute3 => p_attribute3 ,
1076 p_attribute4 => p_attribute4 ,
1077 p_attribute5 => p_attribute5 ,
1078 p_attribute6 => p_attribute6 ,
1079 p_attribute7 => p_attribute7 ,
1080 p_attribute8 => p_attribute8 ,
1081 p_attribute9 => p_attribute9 ,
1082 p_attribute10 => p_attribute10 ,
1083 p_attribute11 => p_attribute11 ,
1084 p_attribute12 => p_attribute12 ,
1085 p_attribute13 => p_attribute13 ,
1086 p_attribute14 => p_attribute14 ,
1087 p_attribute15 => p_attribute15 ,
1088 p_attribute16 => p_attribute16 ,
1089 p_attribute17 => p_attribute17 ,
1090 p_attribute18 => p_attribute18 ,
1091 p_attribute19 => p_attribute19 ,
1092 p_attribute20 => p_attribute20 ,
1093 p_attribute_category => p_attribute_category ,
1094 p_name => p_name ,
1095 p_mode => p_mode
1096 );
1097 --
1098 if FND_API.to_Boolean (p_commit) then
1099 commit work;
1100 end if;
1101 -- Standard call to get message count and if count is 1, get message info.
1102 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
1103 p_data => p_msg_data);
1104
1105 return;
1106 end if;
1107
1108 close c1;
1109
1110 UPDATE_ROW (
1111 p_api_version => p_api_version,
1112 p_init_msg_list => p_init_msg_list,
1113 p_commit => p_commit,
1114 p_validation_level => p_validation_level,
1115 p_return_status => p_return_status,
1116 p_msg_count => p_msg_count,
1117 p_msg_data => p_msg_data,
1118 p_position_id => p_position_id,
1119 p_organization_id => p_organization_id,
1120 p_data_extract_id => p_data_extract_id,
1121 p_position_definition_id => p_position_definition_id ,
1122 p_hr_position_id => p_hr_position_id ,
1123 p_business_group_id => p_business_group_id ,
1124 p_effective_start_date => p_effective_start_date,
1125 p_effective_end_date => p_effective_end_date ,
1126 p_set_of_books_id => p_set_of_books_id ,
1127 p_vacant_position_flag => p_vacant_position_flag,
1128 p_attribute1 => p_attribute1 ,
1129 p_attribute2 => p_attribute2 ,
1130 p_attribute3 => p_attribute3 ,
1131 p_attribute4 => p_attribute4 ,
1132 p_attribute5 => p_attribute5 ,
1133 p_attribute6 => p_attribute6 ,
1134 p_attribute7 => p_attribute7 ,
1135 p_attribute8 => p_attribute8 ,
1136 p_attribute9 => p_attribute9 ,
1137 p_attribute10 => p_attribute10 ,
1138 p_attribute11 => p_attribute11 ,
1139 p_attribute12 => p_attribute12 ,
1140 p_attribute13 => p_attribute13 ,
1141 p_attribute14 => p_attribute14 ,
1142 p_attribute15 => p_attribute15 ,
1143 p_attribute16 => p_attribute16 ,
1144 p_attribute17 => p_attribute17 ,
1145 p_attribute18 => p_attribute18 ,
1146 p_attribute19 => p_attribute19 ,
1147 p_attribute20 => p_attribute20 ,
1148 p_attribute_category => p_attribute_category ,
1149 p_name => p_name ,
1150 p_mode => p_mode
1151 );
1152 -- Standard check of p_commit.
1153
1154 if FND_API.to_Boolean (p_commit) then
1155 commit work;
1156 end if;
1157
1158 -- Standard call to get message count and if count is 1, get message info.
1159
1160 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
1161 p_data => p_msg_data);
1162
1163 END ADD_ROW;
1164 --
1165 /* ----------------------------------------------------------------------- */
1166
1167 PROCEDURE DELETE_ROW (
1168 p_api_version in number,
1169 p_init_msg_list in varchar2 := fnd_api.g_false,
1170 p_commit in varchar2 := fnd_api.g_false,
1171 p_validation_level in number := fnd_api.g_valid_level_full,
1172 p_return_status OUT NOCOPY varchar2,
1173 p_msg_count OUT NOCOPY number,
1174 p_msg_data OUT NOCOPY varchar2,
1175 p_position_id in number
1176 ) is
1177 --
1178 l_api_name CONSTANT VARCHAR2(30) := 'Delete Row' ;
1179 l_api_version CONSTANT NUMBER := 1.0 ;
1180
1181 l_return_status VARCHAR2(1);
1182 l_count NUMBER;
1183 --
1184 BEGIN
1185 --
1186 SAVEPOINT Delete_Row ;
1187 --
1188 -- Initialize message list if p_init_msg_list is set to TRUE.
1189 --
1190 if FND_API.to_Boolean (p_init_msg_list) then
1191 FND_MSG_PUB.initialize;
1192 end if;
1193 --
1194 p_return_status := FND_API.G_RET_STS_SUCCESS ;
1195 --
1196 -- validate first
1197
1198 SELECT count(*) into l_count
1199 FROM psb_ws_position_lines
1200 WHERE position_id = p_position_id;
1201
1202 IF (l_count <> 0) THEN
1203 fnd_message.set_name('PSB', 'PSB_POSITION_IN_WORKSHEET');
1204 fnd_msg_pub.add ;
1205 raise fnd_api.g_exc_error ;
1206 END IF;
1207 --
1208
1209 delete from PSB_POSITIONS
1210 where position_id = p_position_id;
1211 if (sql%notfound) THEN
1212 null;
1213 end if;
1214
1215 -- Standard check of p_commit.
1216 --
1217 if FND_API.to_Boolean (p_commit) then
1218 commit work;
1219 end if;
1220
1221 -- Standard call to get message count and if count is 1, get message info.
1222
1223 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
1224 p_data => p_msg_data);
1225 --
1226 EXCEPTION
1227 when FND_API.G_EXC_ERROR then
1228 --
1229 rollback to Delete_Row;
1230 p_return_status := FND_API.G_RET_STS_ERROR;
1231 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
1232 p_data => p_msg_data);
1233 --
1234 when FND_API.G_EXC_UNEXPECTED_ERROR then
1235 --
1236 rollback to Delete_Row;
1237 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1238 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
1239 p_data => p_msg_data);
1240 --
1241 when OTHERS then
1242 --
1243 rollback to Delete_Row ;
1244 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1245 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) then
1246 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
1247 l_api_name);
1248 end if;
1249 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
1250 p_data => p_msg_data);
1251 --
1252 END DELETE_ROW;
1253
1254 /*----------------------------------------------------------------*/
1255
1256 PROCEDURE Delete_Assignments
1257 ( p_api_version IN NUMBER,
1258 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1259 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1260 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1261 p_return_status OUT NOCOPY VARCHAR2,
1262 p_msg_count OUT NOCOPY NUMBER,
1263 p_msg_data OUT NOCOPY VARCHAR2,
1264 p_worksheet_id IN NUMBER
1265 ) IS
1266
1267 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Assignments';
1268 l_api_version CONSTANT NUMBER := 1.0 ;
1269
1270 BEGIN
1271
1272 SAVEPOINT Delete_Assignments_Pvt;
1273
1274 if FND_API.to_Boolean (p_init_msg_list) then
1275 FND_MSG_PUB.initialize;
1276 end if;
1277
1278 delete from PSB_POSITION_ASSIGNMENTS
1279 where worksheet_id = p_worksheet_id;
1280
1281
1282 -- Standard check of p_commit.
1283
1284 if FND_API.to_Boolean (p_commit) then
1285 commit work;
1286 end if;
1287
1288
1289 -- Initialize API return status to success
1290
1291 p_return_status := FND_API.G_RET_STS_SUCCESS;
1292
1293 -- Standard call to get message count and if count is 1, get message info.
1294
1295 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
1296 p_data => p_msg_data);
1297
1298 EXCEPTION
1299
1300 when FND_API.G_EXC_ERROR then
1301 rollback to Delete_Assignments_Pvt;
1302 p_return_status := FND_API.G_RET_STS_ERROR;
1303
1304 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
1305 p_data => p_msg_data);
1306
1307 when FND_API.G_EXC_UNEXPECTED_ERROR then
1308 rollback to Delete_Assignments_Pvt;
1309 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1310
1311 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
1312 p_data => p_msg_data);
1313
1314 when OTHERS then
1315 rollback to Delete_Assignments_Pvt;
1316 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1317
1318 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) then
1319 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
1320 l_api_name);
1321 end if;
1322
1323 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
1324 p_data => p_msg_data);
1325
1326 END Delete_Assignments;
1327
1328 /*----------------------------------------------------------------*/
1329
1330 PROCEDURE Delete_Assignment_Employees
1331 ( p_api_version IN NUMBER,
1332 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1333 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1334 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1335 p_return_status OUT NOCOPY VARCHAR2,
1336 p_msg_count OUT NOCOPY NUMBER,
1337 p_msg_data OUT NOCOPY VARCHAR2,
1338 p_data_extract_id IN NUMBER
1339 ) IS
1340
1341 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Assignment_Employees';
1342 l_api_version CONSTANT NUMBER := 1.0 ;
1343
1344 BEGIN
1345
1346 SAVEPOINT Delete_Assignment_Emp_Pvt;
1347
1348 if FND_API.to_Boolean (p_init_msg_list) then
1349 FND_MSG_PUB.initialize;
1350 end if;
1351
1352 delete from PSB_POSITION_ASSIGNMENTS
1353 where assignment_type = 'EMPLOYEE'
1354 and data_extract_id = p_data_extract_id;
1355
1356
1357 -- Standard check of p_commit.
1358
1359 if FND_API.to_Boolean (p_commit) then
1360 commit work;
1361 end if;
1362
1363
1364 -- Initialize API return status to success
1365
1366 p_return_status := FND_API.G_RET_STS_SUCCESS;
1367
1368 -- Standard call to get message count and if count is 1, get message info.
1369
1370 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
1371 p_data => p_msg_data);
1372
1373 EXCEPTION
1374
1375 when FND_API.G_EXC_ERROR then
1376 rollback to Delete_Assignment_Emp_Pvt;
1377 p_return_status := FND_API.G_RET_STS_ERROR;
1378
1379 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
1380 p_data => p_msg_data);
1381
1382 when FND_API.G_EXC_UNEXPECTED_ERROR then
1383 rollback to Delete_Assignment_Emp_Pvt;
1384 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1385
1386 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
1387 p_data => p_msg_data);
1388
1389 when OTHERS then
1390 rollback to Delete_Assignment_Emp_Pvt;
1391 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1392
1393 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) then
1394 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
1395 l_api_name);
1396 end if;
1397
1398 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
1399 p_data => p_msg_data);
1400
1401 END Delete_Assignment_Employees;
1402
1403 /*----------------------------------------------------------------*/
1404
1405 PROCEDURE Modify_Assignment
1406 ( p_api_version IN NUMBER,
1407 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1408 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1409 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1410 p_return_status OUT NOCOPY VARCHAR2,
1411 p_msg_count OUT NOCOPY NUMBER,
1412 p_msg_data OUT NOCOPY VARCHAR2,
1413 p_position_assignment_id IN OUT NOCOPY NUMBER,
1414 p_data_extract_id IN NUMBER,
1415 p_worksheet_id IN NUMBER,
1416 p_position_id IN NUMBER,
1417 p_assignment_type IN VARCHAR2,
1418 p_attribute_id IN NUMBER,
1419 p_attribute_value_id IN NUMBER,
1420 p_attribute_value IN VARCHAR2,
1421 p_pay_element_id IN NUMBER,
1422 p_pay_element_option_id IN NUMBER,
1423 p_effective_start_date IN DATE,
1424 p_effective_end_date IN DATE,
1425 p_element_value_type IN VARCHAR2,
1426 p_element_value IN NUMBER,
1427 p_currency_code IN VARCHAR2,
1428 p_pay_basis IN VARCHAR2,
1429 p_employee_id IN NUMBER,
1430 p_primary_employee_flag IN VARCHAR2,
1431 p_global_default_flag IN VARCHAR2,
1432 p_assignment_default_rule_id IN NUMBER,
1433 p_modify_flag IN VARCHAR2,
1434 p_rowid IN OUT NOCOPY VARCHAR2,
1435 p_mode IN VARCHAR2 := 'R'
1436 ) IS
1437
1438 l_api_name CONSTANT VARCHAR2(30) := 'Modify_Assignment';
1439 l_api_version CONSTANT NUMBER := 1.0 ;
1440
1441 cursor c_Overlap is
1442 select position_assignment_id,
1443 data_extract_id,
1444 worksheet_id,
1445 position_id,
1446 assignment_type,
1447 attribute_id,
1448 attribute_value_id,
1449 attribute_value,
1450 pay_element_id,
1451 pay_element_option_id,
1452 effective_start_date,
1453 effective_end_date,
1454 element_value_type,
1455 element_value,
1456 currency_code,
1457 pay_basis,
1458 employee_id,
1459 primary_employee_flag,
1460 global_default_flag,
1461 assignment_default_rule_id,
1462 modify_flag
1463 from PSB_POSITION_ASSIGNMENTS
1464 where (worksheet_id is null or worksheet_id = p_worksheet_id)
1465 and (((p_assignment_type = 'ATTRIBUTE')
1466 and (attribute_id = p_attribute_id))
1467 or ((p_assignment_type = 'EMPLOYEE')
1468 and (employee_id = p_employee_id))
1469 or ((p_assignment_type = 'ELEMENT')
1470 and (pay_element_id = p_pay_element_id)
1471 and ((p_currency_code is null) or (currency_code = p_currency_code))))
1472 and ((((p_effective_end_date is not null)
1473 and ((effective_start_date <= p_effective_end_date)
1474 and (effective_end_date is null))
1475 or ((effective_start_date between p_effective_start_date and p_effective_end_date)
1476 or (effective_end_date between p_effective_start_date and p_effective_end_date)
1477 or ((effective_start_date < p_effective_start_date)
1478 and (effective_end_date > p_effective_end_date)))))
1479 or ((p_effective_end_date is null)
1480 and (nvl(effective_end_date, p_effective_start_date) >= p_effective_start_date)))
1481 and position_id = p_position_id;
1482
1483 --
1484 -- c_salary_overlap returns all salary for the position which overlaps the input record
1485 -- and exclude any base record which have any overlapping WS assignment for the p_worksheet_id
1486 -- This cursor is used when the input record is a salary element; all others use c_overlap
1487 --
1488 -- Salary should not have any duplicate for any date range regarless of the salary element
1489 -- and are now processed regarless of the salary element
1490 --
1491 -- When an ovelap exists, the input salary (regardless of the pay_element_id) supercedes the
1492 -- overlap records. These overlaps are processed using the same logic as c_overlap.
1493 -- The salary overlap is treated as a set so there is no test that the pay_element_id is =
1494 -- p_pay_element_id.
1495 --
1496 -- This changed the way the form displays the salary since they are now treated as a set
1497 --
1498 cursor c_Salary_Overlap is
1499 select a.position_assignment_id,
1500 a.data_extract_id,
1501 a.worksheet_id,
1502 a.position_id,
1503 a.assignment_type,
1504 a.attribute_id,
1505 a.attribute_value_id,
1506 a.attribute_value,
1507 a.pay_element_id,
1508 a.pay_element_option_id,
1509 a.effective_start_date,
1510 a.effective_end_date,
1511 a.element_value_type,
1512 a.element_value,
1513 a.currency_code,
1514 a.pay_basis,
1515 a.employee_id,
1516 a.primary_employee_flag,
1517 a.global_default_flag,
1518 a.assignment_default_rule_id,
1519 a.modify_flag
1520 from PSB_POSITION_ASSIGNMENTS a,
1521 PSB_PAY_ELEMENTS el
1522 where
1523 ( (nvl(a.worksheet_id, FND_API.G_MISS_NUM) = nvl(p_worksheet_id, FND_API.G_MISS_NUM)) OR
1524 (p_worksheet_id is not null and worksheet_id is null
1525 and not exists
1526 (select 1 from
1527 psb_position_assignments c ,psb_pay_elements pe2
1528 where c.position_id = a.position_id
1529 and c.pay_element_id = pe2.pay_element_id
1530 and pe2.salary_flag = 'Y'
1531 and c.worksheet_id = p_worksheet_id
1532 and ( (
1533 nvl(c.effective_start_date,PSB_POSITIONS_PVT.GET_end_date+1) between
1534 nvl(a.effective_start_date,PSB_POSITIONS_PVT.GET_end_date) and
1535 nvl(a.effective_end_date,nvl(PSB_POSITIONS_PVT.GET_end_date,
1536 c.effective_start_date ))) or (
1537 nvl(a.effective_start_date,PSB_POSITIONS_PVT.GET_end_date+1) between
1538 nvl(c.effective_start_date,PSB_POSITIONS_PVT.GET_end_date) and
1539 nvl(c.effective_end_date,nvl(PSB_POSITIONS_PVT.GET_end_date,
1540 a.effective_start_date ))) )
1541 )
1542 )
1543 )
1544 and ( (p_currency_code is null) or (currency_code = p_currency_code))
1545 and ((((p_effective_end_date is not null)
1546 and ((effective_start_date <= p_effective_end_date)
1547 and (effective_end_date is null))
1548 or ((effective_start_date between p_effective_start_date and p_effective_end_date)
1549 or (effective_end_date between p_effective_start_date and p_effective_end_date)
1550 or ((effective_start_date < p_effective_start_date)
1551 and (effective_end_date > p_effective_end_date)))))
1552 or ((p_effective_end_date is null)
1553 and (nvl(effective_end_date, p_effective_start_date) >= p_effective_start_date)))
1554 and position_id = p_position_id
1555 and a.assignment_type = 'ELEMENT'
1556 and a.pay_element_id = el.pay_element_id
1557 and el.salary_flag = 'Y';
1558
1559 cursor c_Salary is
1560 select salary_flag
1561 from PSB_PAY_ELEMENTS
1562 where pay_element_id = p_pay_element_id;
1563
1564 /*For Bug No : 2847566 Start*/
1565 Cursor C_Get_Pay_Basis Is
1566 Select pay_basis
1567 From psb_position_assignments
1568 Where ((worksheet_id is null) or (worksheet_id = p_worksheet_id))
1569 And assignment_type = 'ELEMENT'
1570 And position_id = p_position_id
1571 And pay_basis is not null
1572 And ROWNUM < 2;
1573
1574 l_pay_basis VARCHAR2(10);
1575 /*For Bug No : 2847566 End*/
1576
1577
1578 l_return_status VARCHAR2(1);
1579 l_msg_count NUMBER;
1580 l_msg_data VARCHAR2(2000);
1581
1582 l_rowid VARCHAR2(100);
1583 l_position_assignment_id NUMBER;
1584 l_worksheet_id NUMBER;
1585 l_out_worksheet_id NUMBER;
1586 l_out_start_date DATE;
1587 l_out_end_date DATE;
1588
1589 l_salary_flag VARCHAR2(1);
1590 l_salary_failed VARCHAR2(1);
1591
1592 l_init_index BINARY_INTEGER;
1593 l_assign_index BINARY_INTEGER;
1594
1595 l_created_record VARCHAR2(1):= FND_API.G_FALSE;
1596 l_updated_record VARCHAR2(1);
1597
1598 l_ws_overlap VARCHAR2(1):= FND_API.G_FALSE;
1599
1600 l_userid NUMBER;
1601 l_loginid NUMBER;
1602
1603 /* start bug no 4213882 */
1604 l_element_id NUMBER;
1605 /* end bug no 4213882 */
1606
1607 BEGIN
1608
1609 if FND_API.to_Boolean (p_init_msg_list) then
1610 FND_MSG_PUB.initialize;
1611 end if;
1612
1613 l_userid := FND_GLOBAL.USER_ID;
1614 l_loginid := FND_GLOBAL.LOGIN_ID;
1615
1616
1617 --++
1618 -- modified update_row to pass pay_element_id and modify with input p_pay_element_id for salary overlap
1619 -- added order by on cursors
1620
1621 update PSB_POSITION_ASSIGNMENTS
1622 set attribute_value_id = decode(p_attribute_value_id, null, attribute_value_id, p_attribute_value_id),
1623 attribute_value = decode(p_attribute_value, null, attribute_value, p_attribute_value),
1624 pay_element_option_id = decode(p_pay_element_option_id, null, pay_element_option_id, p_pay_element_option_id),
1625 element_value_type = decode(p_element_value_type, null, element_value_type, p_element_value_type),
1626 element_value = decode(p_element_value, null, element_value, p_element_value),
1627 currency_code = decode(p_currency_code, null, currency_code, p_currency_code),
1628 pay_basis = decode(p_pay_basis, null, pay_basis, p_pay_basis),
1629 primary_employee_flag = decode(p_primary_employee_flag, null, primary_employee_flag, p_primary_employee_flag),
1630 global_default_flag = decode(p_global_default_flag, null, global_default_flag, p_global_default_flag),
1631 assignment_default_rule_id = decode(p_assignment_default_rule_id, null, assignment_default_rule_id, p_assignment_default_rule_id),
1632 modify_flag = decode(p_modify_flag, null, modify_flag, p_modify_flag),
1633 last_update_date = sysdate,
1634 last_updated_by = l_userid,
1635 last_update_login = l_loginid
1636 where nvl(worksheet_id, FND_API.G_MISS_NUM) = nvl(p_worksheet_id, FND_API.G_MISS_NUM)
1637 and (((p_assignment_type = 'ELEMENT') and (pay_element_id = p_pay_element_id))
1638 or ((p_assignment_type = 'ATTRIBUTE') and (attribute_id = p_attribute_id))
1639 or ((p_assignment_type = 'EMPLOYEE') and (employee_id = p_employee_id)))
1640 and nvl(effective_end_date, FND_API.G_MISS_DATE) = nvl(p_effective_end_date, FND_API.G_MISS_DATE)
1641 and effective_start_date = p_effective_start_date
1642 and position_id = p_position_id;
1643
1644
1645 if SQL%NOTFOUND then
1646 --
1647 -- When no exact match is found for the input record, we process it
1648 -- (i) set the array using either of c_overlap or c_salary_overlap depeding on salary flag
1649 -- (ii) if no match found then just do an insert
1650 -- (iii) if match found and modify_flag is 'N'
1651 -- (iv) if match found and modify_flag is 'Y' or null
1652 --
1653 begin
1654
1655 l_salary_failed := FND_API.G_FALSE;
1656
1657 /*For Bug No : 2847566 Start*/
1658 l_pay_basis := p_pay_basis;
1659 /*For Bug No : 2847566 End*/
1660
1661 if p_assignment_type = 'ELEMENT' then
1662
1663 for c_Salary_Rec in c_Salary loop
1664 l_salary_flag := c_Salary_Rec.salary_flag;
1665 end loop;
1666
1667 /*For Bug No : 2847566 Start*/
1668 --following logic has been added to ensure that pay_basis
1669 -- is being inserted for allsalary type elements. This is
1670 --particularly required when there are salary elements
1671 --defined in PSB. We need to get pay_basis from existing element assignment
1672
1673 if (l_salary_flag = 'Y' and p_pay_basis is null) then
1674
1675 for C_Get_Pay_Basis_Rec in C_Get_Pay_Basis loop
1676 l_pay_basis := C_Get_Pay_Basis_Rec.pay_basis;
1677 end loop;
1678
1679 end if;
1680 /*For Bug No : 2847566 End*/
1681
1682 end if;
1683
1684 for l_init_index in 1..g_assign.Count loop
1685 g_assign(l_init_index).position_assignment_id := null;
1686 g_assign(l_init_index).data_extract_id := null;
1687 g_assign(l_init_index).worksheet_id := null;
1688 g_assign(l_init_index).position_id := null;
1689 g_assign(l_init_index).assignment_type := null;
1690 g_assign(l_init_index).attribute_id := null;
1691 g_assign(l_init_index).attribute_value_id := null;
1692 g_assign(l_init_index).attribute_value := null;
1693 g_assign(l_init_index).pay_element_id := null;
1694 g_assign(l_init_index).pay_element_option_id := null;
1695 g_assign(l_init_index).effective_start_date := null;
1696 g_assign(l_init_index).effective_end_date := null;
1697 g_assign(l_init_index).element_value_type := null;
1698 g_assign(l_init_index).element_value := null;
1699 g_assign(l_init_index).currency_code := null;
1700 g_assign(l_init_index).pay_basis := null;
1701 g_assign(l_init_index).employee_id := null;
1702 g_assign(l_init_index).primary_employee_flag := null;
1703 g_assign(l_init_index).global_default_flag := null;
1704 g_assign(l_init_index).assignment_default_rule_id := null;
1705 g_assign(l_init_index).modify_flag := null;
1706 g_assign(l_init_index).delete_flag := null;
1707 end loop;
1708
1709 g_num_assign := 0;
1710
1711 --
1712 -- set the array using either cursor depending on salary flag
1713 -- set l_ws_overlap if any overlap is WS specific. This flag will be used to control
1714 -- processing of the base overlap
1715 --
1716 if l_salary_flag = 'Y' then
1717
1718 for c_Overlap_Rec in c_Salary_Overlap loop
1719 g_num_assign := g_num_assign + 1;
1720
1721 g_assign(g_num_assign).position_assignment_id := c_Overlap_Rec.position_assignment_id;
1722 g_assign(g_num_assign).data_extract_id := c_Overlap_Rec.data_extract_id;
1723 g_assign(g_num_assign).worksheet_id := c_Overlap_Rec.worksheet_id;
1724 g_assign(g_num_assign).position_id := c_Overlap_Rec.position_id;
1725 g_assign(g_num_assign).assignment_type := c_Overlap_Rec.assignment_type;
1726 g_assign(g_num_assign).attribute_id := c_Overlap_Rec.attribute_id;
1727 g_assign(g_num_assign).attribute_value_id := c_Overlap_Rec.attribute_value_id;
1728 g_assign(g_num_assign).attribute_value := c_Overlap_Rec.attribute_value;
1729 g_assign(g_num_assign).pay_element_id := c_Overlap_Rec.pay_element_id;
1730 g_assign(g_num_assign).pay_element_option_id := c_Overlap_Rec.pay_element_option_id;
1731 g_assign(g_num_assign).effective_start_date := c_Overlap_Rec.effective_start_date;
1732 g_assign(g_num_assign).effective_end_date := c_Overlap_Rec.effective_end_date;
1733 g_assign(g_num_assign).element_value_type := c_Overlap_Rec.element_value_type;
1734 g_assign(g_num_assign).element_value := c_Overlap_Rec.element_value;
1735 g_assign(g_num_assign).currency_code := c_Overlap_Rec.currency_code;
1736 g_assign(g_num_assign).pay_basis := c_Overlap_Rec.pay_basis;
1737 g_assign(g_num_assign).employee_id := c_Overlap_Rec.employee_id;
1738 g_assign(g_num_assign).primary_employee_flag := c_Overlap_Rec.primary_employee_flag;
1739 g_assign(g_num_assign).global_default_flag := c_Overlap_Rec.global_default_flag;
1740 g_assign(g_num_assign).assignment_default_rule_id := c_Overlap_Rec.assignment_default_rule_id;
1741 g_assign(g_num_assign).modify_flag := c_Overlap_Rec.modify_flag;
1742 g_assign(g_num_assign).delete_flag := FND_API.G_TRUE;
1743
1744 if g_assign(g_num_assign).worksheet_id = p_worksheet_id then
1745 begin
1746
1747 if not FND_API.to_Boolean(l_ws_overlap) then
1748 l_ws_overlap := FND_API.G_TRUE;
1749 end if;
1750
1751 end;
1752 end if;
1753
1754 end loop;
1755 else
1756
1757 for c_Overlap_Rec in c_Overlap loop
1758 g_num_assign := g_num_assign + 1;
1759
1760 g_assign(g_num_assign).position_assignment_id := c_Overlap_Rec.position_assignment_id;
1761 g_assign(g_num_assign).data_extract_id := c_Overlap_Rec.data_extract_id;
1762 g_assign(g_num_assign).worksheet_id := c_Overlap_Rec.worksheet_id;
1763 g_assign(g_num_assign).position_id := c_Overlap_Rec.position_id;
1764 g_assign(g_num_assign).assignment_type := c_Overlap_Rec.assignment_type;
1765 g_assign(g_num_assign).attribute_id := c_Overlap_Rec.attribute_id;
1766 g_assign(g_num_assign).attribute_value_id := c_Overlap_Rec.attribute_value_id;
1767 g_assign(g_num_assign).attribute_value := c_Overlap_Rec.attribute_value;
1768 g_assign(g_num_assign).pay_element_id := c_Overlap_Rec.pay_element_id;
1769 g_assign(g_num_assign).pay_element_option_id := c_Overlap_Rec.pay_element_option_id;
1770 g_assign(g_num_assign).effective_start_date := c_Overlap_Rec.effective_start_date;
1771 g_assign(g_num_assign).effective_end_date := c_Overlap_Rec.effective_end_date;
1772 g_assign(g_num_assign).element_value_type := c_Overlap_Rec.element_value_type;
1773 g_assign(g_num_assign).element_value := c_Overlap_Rec.element_value;
1774 g_assign(g_num_assign).currency_code := c_Overlap_Rec.currency_code;
1775 g_assign(g_num_assign).pay_basis := c_Overlap_Rec.pay_basis;
1776 g_assign(g_num_assign).employee_id := c_Overlap_Rec.employee_id;
1777 g_assign(g_num_assign).primary_employee_flag := c_Overlap_Rec.primary_employee_flag;
1778 g_assign(g_num_assign).global_default_flag := c_Overlap_Rec.global_default_flag;
1779 g_assign(g_num_assign).assignment_default_rule_id := c_Overlap_Rec.assignment_default_rule_id;
1780 g_assign(g_num_assign).modify_flag := c_Overlap_Rec.modify_flag;
1781 g_assign(g_num_assign).delete_flag := FND_API.G_TRUE;
1782
1783 if g_assign(g_num_assign).worksheet_id = p_worksheet_id then
1784 begin
1785
1786 if not FND_API.to_Boolean(l_ws_overlap) then
1787 l_ws_overlap := FND_API.G_TRUE;
1788 end if;
1789
1790 end;
1791 end if;
1792
1793 end loop;
1794 end if;
1795
1796 --
1797 -- no overlap found
1798 -- modified this routine to remove salary validation since salary now uses a
1799 -- different cursor which includes all salary. If there is no overlap, then it means
1800 -- there was no overlap of any salary so we can insert the input
1801 --
1802
1803 if ((g_num_assign = 0) and
1804 ((p_modify_flag is null) or (p_modify_flag = 'Y'))) then
1805 begin
1806
1807 /* No Overlaps, Input not for protecting assignment: direct insert */
1808 -- removed salary validation
1809 begin
1810
1811 PSB_POSITION_ASSIGNMENTS_PVT.Insert_Row
1812 (p_api_version => 1.0,
1813 p_return_status => l_return_status,
1814 p_msg_count => l_msg_count,
1815 p_msg_data => l_msg_data,
1816 p_rowid => l_rowid,
1817 p_position_assignment_id => l_position_assignment_id,
1818 p_data_extract_id => p_data_extract_id,
1819 p_worksheet_id => p_worksheet_id,
1820 p_position_id => p_position_id,
1821 p_assignment_type => p_assignment_type,
1822 p_attribute_id => p_attribute_id,
1823 p_attribute_value_id => p_attribute_value_id,
1824 p_attribute_value => p_attribute_value,
1825 p_pay_element_id => p_pay_element_id,
1826 p_pay_element_option_id => p_pay_element_option_id,
1827 p_effective_start_date => p_effective_start_date,
1828 p_effective_end_date => p_effective_end_date,
1829 p_element_value_type => p_element_value_type,
1830 p_element_value => p_element_value,
1831 p_currency_code => p_currency_code,
1832 /* For Bug No. 2847566 Start */
1833 --p_pay_basis => p_pay_basis,
1834 p_pay_basis => l_pay_basis,
1835 /* For Bug No. 2847566 End */
1836 p_employee_id => p_employee_id,
1837 p_primary_employee_flag => p_primary_employee_flag,
1838 p_global_default_flag => p_global_default_flag,
1839 p_assignment_default_rule_id => p_assignment_default_rule_id,
1840 p_modify_flag => p_modify_flag,
1841 p_mode => p_mode);
1842
1843 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1844 raise FND_API.G_EXC_ERROR;
1845 end if;
1846
1847 p_rowid := l_rowid;
1848 p_position_assignment_id := l_position_assignment_id;
1849
1850 end;
1851
1852 end;
1853 else
1854 begin
1855
1856 --
1857 -- 1 or more Base or Worksheet Overlaps exists for overlap records where a worksheet overlap exists or do not exist
1858 -- p_modify_flag of 'N' means the assignment is protected from changes
1859 -- the p_modify_flag is set to 'N' only when there are element constraints
1860 --
1861 -- modified 'if' statement to include test on pay_element_id and option_id if input is a salary element
1862 --
1863 if p_modify_flag = 'N' then
1864 begin
1865
1866 /* Set Protected Flag for Position Assignment */
1867
1868 for l_assign_index in 1..g_num_assign loop
1869
1870 if FND_API.to_Boolean(l_ws_overlap) then
1871 begin
1872
1873 /* Worksheet Overlap, Update Protected Flag for the Assignment */
1874 -- modified 'if' statement to include test on pay_element_id and option_id if input is a salary element
1875
1876 if ((g_assign(l_assign_index).worksheet_id = p_worksheet_id) and
1877 ((nvl(g_assign(l_assign_index).pay_element_option_id, FND_API.G_MISS_NUM) = nvl(p_pay_element_option_id, FND_API.G_MISS_NUM)))
1878 OR
1879 (l_salary_flag='Y'
1880 and nvl(g_assign(l_assign_index).pay_element_option_id, FND_API.G_MISS_NUM) = nvl(p_pay_element_option_id, FND_API.G_MISS_NUM)
1881 and nvl(g_assign(l_assign_index).pay_element_id, FND_API.G_MISS_NUM) = nvl(p_pay_element_id, FND_API.G_MISS_NUM)
1882 )
1883 ) then
1884 begin
1885
1886 PSB_POSITION_ASSIGNMENTS_PVT.Update_Row
1887 (p_api_version => 1.0,
1888 p_return_status => l_return_status,
1889 p_msg_count => l_msg_count,
1890 p_msg_data => l_msg_data,
1891 p_position_assignment_id => g_assign(l_assign_index).position_assignment_id,
1892 p_modify_flag => p_modify_flag,
1893 p_mode => p_mode);
1894
1895 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1896 raise FND_API.G_EXC_ERROR;
1897 end if;
1898
1899 g_assign(l_assign_index).delete_flag := FND_API.G_FALSE;
1900
1901 end;
1902 end if;
1903
1904 end;
1905 else
1906 begin
1907
1908 /* There is No Worksheet Overlap, so Replicate Base Overlap and update Protected Flag for the Assignment */
1909 -- modified 'if' statement to include test on pay_element_id and option_id if input is a salary element
1910
1911
1912 if ((nvl(g_assign(l_assign_index).pay_element_option_id, FND_API.G_MISS_NUM) = nvl(p_pay_element_option_id, FND_API.G_MISS_NUM)
1913 )
1914 OR
1915 (l_salary_flag='Y'
1916 and nvl(g_assign(l_assign_index).pay_element_option_id, FND_API.G_MISS_NUM) = nvl(p_pay_element_option_id, FND_API.G_MISS_NUM)
1917 and nvl(g_assign(l_assign_index).pay_element_id, FND_API.G_MISS_NUM) = nvl(p_pay_element_id, FND_API.G_MISS_NUM)
1918 )
1919 ) then
1920 begin
1921
1922 Modify_Assignment_WS
1923 (p_return_status => l_return_status,
1924 p_position_assignment_id => l_position_assignment_id,
1925 p_data_extract_id => p_data_extract_id,
1926 p_worksheet_id => p_worksheet_id,
1927 p_position_id => p_position_id,
1928 p_assignment_type => g_assign(l_assign_index).assignment_type,
1929 p_attribute_id => g_assign(l_assign_index).attribute_id,
1930 p_attribute_value_id => g_assign(l_assign_index).attribute_value_id,
1931 p_attribute_value => g_assign(l_assign_index).attribute_value,
1932 p_pay_element_id => g_assign(l_assign_index).pay_element_id,
1933 p_pay_element_option_id => g_assign(l_assign_index).pay_element_option_id,
1934 p_effective_start_date => greatest(g_assign(l_assign_index).effective_start_date, p_effective_start_date),
1935 p_effective_end_date => least(nvl(g_assign(l_assign_index).effective_end_date, p_effective_end_date), p_effective_end_date),
1936 p_element_value_type => g_assign(l_assign_index).element_value_type,
1937 p_element_value => g_assign(l_assign_index).element_value,
1938 p_currency_code => nvl(p_currency_code, g_assign(l_assign_index).currency_code),
1939 p_pay_basis => g_assign(l_assign_index).pay_basis,
1940 p_employee_id => g_assign(l_assign_index).employee_id,
1941 p_primary_employee_flag => g_assign(l_assign_index).primary_employee_flag,
1942 p_global_default_flag => g_assign(l_assign_index).global_default_flag,
1943 p_assignment_default_rule_id => g_assign(l_assign_index).assignment_default_rule_id,
1944 p_modify_flag => p_modify_flag,
1945 p_rowid => l_rowid);
1946
1947 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1948 raise FND_API.G_EXC_ERROR;
1949 end if;
1950
1951 p_rowid := l_rowid;
1952 p_position_assignment_id := l_position_assignment_id;
1953
1954 end;
1955 end if;
1956
1957 end;
1958 end if;
1959
1960 end loop;
1961
1962 end; /* end Check for modify_flag 'N'*/
1963 else
1964 begin
1965
1966 --
1967 -- overlap exists and modify_flag is 'Y' or null
1968 -- for each record in the ovelap,
1969 -- check input dates with the overlap records
1970 -- (i) start_date matches
1971 -- (ii) overlap dates and overlap records have the same worksheet id
1972 -- - depending on the overlap's start date and input's start date either
1973 -- update the overlap's start date or create a new record
1974 -- - depending on the overlap's end date and input's end date either
1975 -- update the overlap's end date or create a new record
1976 -- - modified the update row to pass pay_element_id and pay_element_option_id due to the salary change
1977 -- (iii) overlap dates and overlap records is base and input is not base (= p_worksheet_id) and
1978 -- there is NO worksheet overlap in the overlap records.
1979 -- (If there are worksheet overlaps, it will will be processed in (ii) ).
1980 -- - always create the input record since the overlap is a base
1981 -- - create a record from the input's end date +1 to the overlaps's end date
1982 -- if the overlap's end date is beyond the input's end date
1983 --
1984 -- Modified the all update_row api to pass pay_element_id and pay_element_option_id due to the salary change
1985
1986 for l_assign_index in 1..g_num_assign loop
1987
1988 l_updated_record := FND_API.G_FALSE;
1989
1990 /* Effective Start Date Matches */
1991
1992 -- (i) start_date matches
1993 --
1994 -- this logic is performed for form changes of base assignments when in modify position WS or budget revision
1995 -- for which p_worksheet_id is not null. The form initially shows the base where the start date is not
1996 -- updateable. When changing for example the end date, this routine will create a WS specific record.
1997 -- If the original record is a WS specific and the end date is modified, the overlap is updated
1998 --
1999 -- If the change was done from Modify Positions form which processes only base, then just update the record
2000 --
2001 -- From the form, the only possible routines processed for this api are :
2002 -- - 1 overlap only where effective date matches either base or Ws specific
2003 -- - no overlap since the form already tests for overlaps (g_num_assign is 0)
2004 --
2005 -- This logic is also performed when called from other than form and the start date matches
2006 -- ** This poses a problem when the start date matches with overlap and the routine is called from other than
2007 -- form. This routine will be performed, but the other overlaps will be processed using the other routines
2008 -- depending on the date. The result will be overlap assignments.
2009 -- Note that the overlap cursors do not control the way the record is retrieved so the overlap records
2010 -- may come in any order, i.e., overlap with date matches as the first record or last record or middle record
2011 --
2012 if g_assign(l_assign_index).effective_start_date = p_effective_start_date then
2013 begin
2014 if ((nvl(g_assign(l_assign_index).worksheet_id, FND_API.G_MISS_NUM) = nvl(p_worksheet_id, FND_API.G_MISS_NUM)) and
2015 ((g_assign(l_assign_index).modify_flag is null) or (g_assign(l_assign_index).modify_flag = 'Y'))) then
2016 begin
2017
2018 --+ pass input pay_element_id and pay_element_option_id so that salary overlap will result
2019 --+ of update will have the new input salary values
2020
2021 PSB_POSITION_ASSIGNMENTS_PVT.Update_Row
2022 (p_api_version => 1.0,
2023 p_return_status => l_return_status,
2024 p_msg_count => l_msg_count,
2025 p_msg_data => l_msg_data,
2026 p_position_assignment_id => g_assign(l_assign_index).position_assignment_id,
2027 p_pay_element_option_id => p_pay_element_option_id,
2028 p_attribute_value_id => p_attribute_value_id,
2029 p_attribute_value => p_attribute_value,
2030 p_effective_end_date => p_effective_end_date,
2031 p_pay_element_id => p_pay_element_id,
2032 p_element_value_type => p_element_value_type,
2033 p_element_value => p_element_value,
2034 p_global_default_flag => p_global_default_flag,
2035 p_assignment_default_rule_id => p_assignment_default_rule_id,
2036 p_modify_flag => p_modify_flag,
2037 p_pay_basis => g_assign(l_assign_index).pay_basis,
2038 p_employee_id => p_employee_id,
2039 p_primary_employee_flag => p_primary_employee_flag,
2040 p_mode => p_mode);
2041
2042 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2043 raise FND_API.G_EXC_ERROR;
2044 end if;
2045
2046 g_assign(l_assign_index).delete_flag := FND_API.G_FALSE;
2047
2048 end;
2049 elsif ((g_assign(l_assign_index).worksheet_id is null) and (p_worksheet_id is not null) and
2050 (not FND_API.to_Boolean(l_ws_overlap))) then
2051 begin
2052 PSB_POSITION_ASSIGNMENTS_PVT.Insert_Row
2053 (p_api_version => 1.0,
2054 p_return_status => l_return_status,
2055 p_msg_count => l_msg_count,
2056 p_msg_data => l_msg_data,
2057 p_rowid => l_rowid,
2058 p_position_assignment_id => l_position_assignment_id,
2059 p_data_extract_id => p_data_extract_id,
2060 p_worksheet_id => p_worksheet_id,
2061 p_position_id => p_position_id,
2062 p_assignment_type => p_assignment_type,
2063 p_attribute_id => p_attribute_id,
2064 p_attribute_value_id => p_attribute_value_id,
2065 p_attribute_value => p_attribute_value,
2066 p_pay_element_id => p_pay_element_id,
2067 p_pay_element_option_id => p_pay_element_option_id,
2068 p_effective_start_date => p_effective_start_date,
2069 p_effective_end_date => p_effective_end_date,
2070 p_element_value_type => p_element_value_type,
2071 p_element_value => p_element_value,
2072 p_currency_code => nvl(p_currency_code, g_assign(l_assign_index).currency_code),
2073 p_pay_basis => g_assign(l_assign_index).pay_basis,
2074 p_employee_id => p_employee_id,
2075 p_primary_employee_flag => p_primary_employee_flag,
2076 p_global_default_flag => p_global_default_flag,
2077 p_assignment_default_rule_id => p_assignment_default_rule_id,
2078 p_modify_flag => p_modify_flag,
2079 p_mode => p_mode);
2080
2081 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2082 raise FND_API.G_EXC_ERROR ;
2083 end if;
2084
2085 p_rowid := l_rowid;
2086 p_position_assignment_id := l_position_assignment_id;
2087
2088 end;
2089 end if;
2090
2091 end;
2092
2093 /* Effective Dates Overlap */
2094
2095 -- (ii) overlap dates and overlap records have the same worksheet id
2096 -- process overlap records here
2097 -- modified if statement to include test on p_effective_end_date is null so that if
2098 -- overlap.start_date > input.start_date and p_end is null the overlap will not be ignored
2099 -- modified plus to minus for end date test ('OR' condition) to include overlaps with end date = input end date
2100 -- since these records are ignored and input is not processed
2101
2102 elsif (((g_assign(l_assign_index).effective_start_date <= (p_effective_start_date - 1)) and
2103 ((g_assign(l_assign_index).effective_end_date is null) or
2104 (p_effective_end_date is null) or
2105 (g_assign(l_assign_index).effective_end_date > (p_effective_start_date - 1)))) or
2106 ((g_assign(l_assign_index).effective_start_date > p_effective_start_date) and
2107 ((g_assign(l_assign_index).effective_end_date is null) or
2108 (p_effective_end_date is null) or
2109 (g_assign(l_assign_index).effective_end_date > (p_effective_end_date - 1))))) then
2110 begin
2111
2112 if ((nvl(g_assign(l_assign_index).worksheet_id, FND_API.G_MISS_NUM) = nvl(p_worksheet_id, FND_API.G_MISS_NUM)) and
2113 ((g_assign(l_assign_index).modify_flag is null) or (g_assign(l_assign_index).modify_flag = 'Y'))) then
2114 begin
2115
2116 if ((g_assign(l_assign_index).effective_start_date < (p_effective_start_date - 1)) and
2117 ((g_assign(l_assign_index).effective_end_date is null) or
2118 (g_assign(l_assign_index).effective_end_date > (p_effective_start_date - 1)))) then
2119 begin
2120 --++ pass input pay_element_option_id so that if input is salary, the updated row will
2121 --++ reflect the input value
2122
2123 /* start bug no 4213882 */
2124 IF PSB_HR_POPULATE_DATA_PVT.g_pop_assignment = 'Y' AND
2125 PSB_HR_POPULATE_DATA_PVT.g_extract_method = 'REFRESH' THEN
2126 l_element_id := g_assign(l_assign_index).pay_element_id;
2127 ELSE
2128 l_element_id := p_pay_element_id;
2129 END IF;
2130 /* end bug no 4213882 */
2131
2132 PSB_POSITION_ASSIGNMENTS_PVT.Update_Row
2133 (p_api_version => 1.0,
2134 p_return_status => l_return_status,
2135 p_msg_count => l_msg_count,
2136 p_msg_data => l_msg_data,
2137 p_position_assignment_id => g_assign(l_assign_index).position_assignment_id,
2138 /* Bug No 2259505 Start */
2139 -- Uncommented the first line and commented the second line
2140 p_pay_element_option_id => g_assign(l_assign_index).pay_element_option_id,
2141 -- p_pay_element_option_id => p_pay_element_option_id,
2142 /* Bug No 2259505 End */
2143 p_attribute_value_id => g_assign(l_assign_index).attribute_value_id,
2144 p_attribute_value => g_assign(l_assign_index).attribute_value,
2145 p_effective_end_date => p_effective_start_date - 1,
2146 /* start bug no 4213882 */
2147 p_pay_element_id => l_element_id,
2148 /* End bug no 4213882 */
2149 p_element_value_type => g_assign(l_assign_index).element_value_type,
2150 p_element_value => g_assign(l_assign_index).element_value,
2151 p_global_default_flag => g_assign(l_assign_index).global_default_flag,
2152 p_assignment_default_rule_id => g_assign(l_assign_index).assignment_default_rule_id,
2153 p_modify_flag => g_assign(l_assign_index).modify_flag,
2154 p_pay_basis => g_assign(l_assign_index).pay_basis,
2155 p_employee_id => g_assign(l_assign_index).employee_id,
2156 p_primary_employee_flag => g_assign(l_assign_index).primary_employee_flag,
2157 p_mode => p_mode);
2158
2159 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2160 raise FND_API.G_EXC_ERROR;
2161 else
2162 l_updated_record := FND_API.G_TRUE;
2163 end if;
2164
2165 g_assign(l_assign_index).delete_flag := FND_API.G_FALSE;
2166
2167 end;
2168 elsif ((g_assign(l_assign_index).effective_start_date > p_effective_start_date) and
2169 ((p_effective_end_date is not null) and
2170 ((g_assign(l_assign_index).effective_end_date is null) or
2171 (g_assign(l_assign_index).effective_end_date > (p_effective_end_date - 1))))) then
2172 begin
2173
2174 --++ pass input pay_element_option_id so that if input is salary, the updated row will
2175 --++ reflect the input value
2176 /* start bug no 4213882 */
2177 IF PSB_HR_POPULATE_DATA_PVT.g_pop_assignment = 'Y' AND
2178 PSB_HR_POPULATE_DATA_PVT.g_extract_method = 'REFRESH' THEN
2179 l_element_id := g_assign(l_assign_index).pay_element_id;
2180 ELSE
2181 l_element_id := p_pay_element_id;
2182 END IF;
2183 /* end bug no 4213882 */
2184
2185 PSB_POSITION_ASSIGNMENTS_PVT.Update_Row
2186 (p_api_version => 1.0,
2187 p_return_status => l_return_status,
2188 p_msg_count => l_msg_count,
2189 p_msg_data => l_msg_data,
2190 p_position_assignment_id => g_assign(l_assign_index).position_assignment_id,
2191 /* Bug No 2259505 Start */
2192 -- Commented the first line and Uncommented the second line
2193 -- p_pay_element_option_id => p_pay_element_option_id,
2194 p_pay_element_option_id => g_assign(l_assign_index).pay_element_option_id,
2195 /* Bug No 2259505 End */
2196 p_attribute_value_id => g_assign(l_assign_index).attribute_value_id,
2197 p_attribute_value => g_assign(l_assign_index).attribute_value,
2198 p_effective_start_date => p_effective_end_date + 1,
2199 /* start bug no 4213882 */
2200 p_pay_element_id => l_element_id,
2201 /* start bug no 4213882 */
2202 p_element_value_type => g_assign(l_assign_index).element_value_type,
2203 p_element_value => g_assign(l_assign_index).element_value,
2204 p_global_default_flag => g_assign(l_assign_index).global_default_flag,
2205 p_assignment_default_rule_id => g_assign(l_assign_index).assignment_default_rule_id,
2206 p_modify_flag => g_assign(l_assign_index).modify_flag,
2207 p_pay_basis => g_assign(l_assign_index).pay_basis,
2208 p_employee_id => g_assign(l_assign_index).employee_id,
2209 p_primary_employee_flag => g_assign(l_assign_index).primary_employee_flag,
2210 p_mode => p_mode);
2211
2212 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2213 raise FND_API.G_EXC_ERROR;
2214 else
2215 l_updated_record := FND_API.G_FALSE;
2216 end if;
2217
2218 g_assign(l_assign_index).delete_flag := FND_API.G_FALSE;
2219
2220 end;
2221 end if;
2222
2223 if not FND_API.to_Boolean(l_created_record) then
2224 begin
2225 /* start bug 4153562 */
2226 -- check for the extract method and check whether parameter start date
2227 -- is greater than the overlap record start date.
2228 IF NOT ((PSB_HR_POPULATE_DATA_PVT.g_extract_method = 'REFRESH') AND
2229 (g_assign(l_assign_index).effective_start_date > p_effective_start_date) AND
2230 (p_effective_end_date is null)) THEN
2231 /* end bug 4153562 */
2232
2233 PSB_POSITION_ASSIGNMENTS_PVT.Insert_Row
2234 (p_api_version => 1.0,
2235 p_return_status => l_return_status,
2236 p_msg_count => l_msg_count,
2237 p_msg_data => l_msg_data,
2238 p_rowid => l_rowid,
2239 p_position_assignment_id => l_position_assignment_id,
2240 p_data_extract_id => p_data_extract_id,
2241 p_worksheet_id => p_worksheet_id,
2242 p_position_id => p_position_id,
2243 p_assignment_type => p_assignment_type,
2244 p_attribute_id => p_attribute_id,
2245 p_attribute_value_id => p_attribute_value_id,
2246 p_attribute_value => p_attribute_value,
2247 p_pay_element_id => p_pay_element_id,
2248 p_pay_element_option_id => p_pay_element_option_id,
2249 p_effective_start_date => p_effective_start_date,
2250 p_effective_end_date => p_effective_end_date,
2251 p_element_value_type => p_element_value_type,
2252 p_element_value => p_element_value,
2253 p_currency_code => p_currency_code,
2254 p_pay_basis => g_assign(l_assign_index).pay_basis,
2255 p_employee_id => p_employee_id,
2256 p_primary_employee_flag => p_primary_employee_flag,
2257 p_global_default_flag => p_global_default_flag,
2258 p_assignment_default_rule_id => p_assignment_default_rule_id,
2259 p_modify_flag => p_modify_flag,
2260 p_mode => p_mode);
2261
2262 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2263 raise FND_API.G_EXC_ERROR;
2264 else
2265 l_created_record := FND_API.G_TRUE;
2266 end if;
2267
2268 p_rowid := l_rowid;
2269 p_position_assignment_id := l_position_assignment_id;
2270
2271 /* start bug 4153562 */
2272 END IF;
2273 /* end bug 4153562 */
2274
2275 end;
2276 end if;
2277
2278 if p_effective_end_date is not null then
2279 begin
2280
2281 if nvl(g_assign(l_assign_index).effective_end_date, (p_effective_end_date + 1)) > (p_effective_end_date + 1) then
2282 begin
2283
2284 if FND_API.to_Boolean(l_updated_record) then
2285 begin
2286
2287 PSB_POSITION_ASSIGNMENTS_PVT.Insert_Row
2288 (p_api_version => 1.0,
2289 p_return_status => l_return_status,
2290 p_msg_count => l_msg_count,
2291 p_msg_data => l_msg_data,
2292 p_rowid => l_rowid,
2293 p_position_assignment_id => l_position_assignment_id,
2294 p_data_extract_id => g_assign(l_assign_index).data_extract_id,
2295 p_worksheet_id => p_worksheet_id,
2296 p_position_id => g_assign(l_assign_index).position_id,
2297 p_assignment_type => g_assign(l_assign_index).assignment_type,
2298 p_attribute_id => g_assign(l_assign_index).attribute_id,
2299 p_attribute_value_id => g_assign(l_assign_index).attribute_value_id,
2300 p_attribute_value => g_assign(l_assign_index).attribute_value,
2301 p_pay_element_id => g_assign(l_assign_index).pay_element_id,
2302 p_pay_element_option_id => g_assign(l_assign_index).pay_element_option_id,
2303 p_effective_start_date => p_effective_end_date + 1,
2304 p_effective_end_date => g_assign(l_assign_index).effective_end_date,
2305 p_element_value_type => g_assign(l_assign_index).element_value_type,
2306 p_element_value => g_assign(l_assign_index).element_value,
2307 p_currency_code => g_assign(l_assign_index).currency_code,
2308 p_pay_basis => g_assign(l_assign_index).pay_basis,
2309 p_employee_id => g_assign(l_assign_index).employee_id,
2310 p_primary_employee_flag => g_assign(l_assign_index).primary_employee_flag,
2311 p_global_default_flag => g_assign(l_assign_index).global_default_flag,
2312 p_assignment_default_rule_id => g_assign(l_assign_index).assignment_default_rule_id,
2313 p_modify_flag => g_assign(l_assign_index).modify_flag,
2314 p_mode => p_mode);
2315
2316 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2317 raise FND_API.G_EXC_ERROR;
2318 end if;
2319
2320 p_rowid := l_rowid;
2321 p_position_assignment_id := l_position_assignment_id;
2322
2323 end;
2324 else
2325 begin
2326
2327 --++ pass input pay_element_option_id so that if input is salary, the updated row will
2328 --++ reflect the input value
2329 PSB_POSITION_ASSIGNMENTS_PVT.Update_Row
2330 (p_api_version => 1.0,
2331 p_return_status => l_return_status,
2332 p_msg_count => l_msg_count,
2333 p_msg_data => l_msg_data,
2334 p_position_assignment_id => g_assign(l_assign_index).position_assignment_id,
2335 /* Bug No 2259505 Start */
2336 -- Uncommented the first line and commented the second line
2337 p_pay_element_option_id => g_assign(l_assign_index).pay_element_option_id,
2338 -- p_pay_element_option_id => p_pay_element_option_id,
2339 /* Bug No 2259505 End */
2340 p_attribute_value_id => g_assign(l_assign_index).attribute_value_id,
2341 p_attribute_value => g_assign(l_assign_index).attribute_value,
2342 p_effective_start_date => p_effective_end_date + 1,
2343 p_effective_end_date => g_assign(l_assign_index).effective_end_date,
2344 p_pay_element_id => p_pay_element_id,
2345 p_element_value_type => g_assign(l_assign_index).element_value_type,
2346 p_element_value => g_assign(l_assign_index).element_value,
2347 p_pay_basis => g_assign(l_assign_index).pay_basis,
2348 p_employee_id => g_assign(l_assign_index).employee_id,
2349 p_primary_employee_flag => g_assign(l_assign_index).primary_employee_flag,
2350 p_global_default_flag => g_assign(l_assign_index).global_default_flag,
2351 p_assignment_default_rule_id => g_assign(l_assign_index).assignment_default_rule_id,
2352 p_modify_flag => g_assign(l_assign_index).modify_flag,
2353 p_mode => p_mode);
2354
2355 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2356 raise FND_API.G_EXC_ERROR;
2357 end if;
2358
2359 g_assign(l_assign_index).delete_flag := FND_API.G_FALSE;
2360
2361 end;
2362 end if;
2363
2364 end;
2365 end if;
2366
2367 end;
2368 end if;
2369
2370 end;
2371 --
2372 -- (iii) overlap dates and overlap records is base and input is not base (= p_worksheet_id) and
2373 -- there is NO worksheet overlap in the overlap records.
2374 --
2375 elsif ((g_assign(l_assign_index).worksheet_id is null) and (p_worksheet_id is not null) and
2376 (not FND_API.to_Boolean(l_ws_overlap))) then
2377 begin
2378
2379 if ((g_assign(l_assign_index).effective_start_date <= (p_effective_start_date - 1)) and
2380 ((g_assign(l_assign_index).effective_end_date is null) or
2381 (g_assign(l_assign_index).effective_end_date > (p_effective_start_date - 1)))) then
2382 begin
2383
2384 Modify_Assignment_WS
2385 (p_return_status => l_return_status,
2386 p_position_assignment_id => l_position_assignment_id,
2387 p_data_extract_id => p_data_extract_id,
2388 p_worksheet_id => p_worksheet_id,
2389 p_position_id => p_position_id,
2390 p_assignment_type => g_assign(l_assign_index).assignment_type,
2391 p_attribute_id => g_assign(l_assign_index).attribute_id,
2392 p_attribute_value_id => g_assign(l_assign_index).attribute_value_id,
2393 p_attribute_value => g_assign(l_assign_index).attribute_value,
2394 p_pay_element_id => g_assign(l_assign_index).pay_element_id,
2395 p_pay_element_option_id => g_assign(l_assign_index).pay_element_option_id,
2396 p_effective_start_date => g_assign(l_assign_index).effective_start_date,
2397 p_effective_end_date => p_effective_start_date - 1,
2398 p_element_value_type => g_assign(l_assign_index).element_value_type,
2399 p_element_value => g_assign(l_assign_index).element_value,
2400 p_currency_code => nvl(p_currency_code, g_assign(l_assign_index).currency_code),
2401 p_pay_basis => g_assign(l_assign_index).pay_basis,
2402 p_employee_id => g_assign(l_assign_index).employee_id,
2403 p_primary_employee_flag => g_assign(l_assign_index).primary_employee_flag,
2404 p_global_default_flag => g_assign(l_assign_index).global_default_flag,
2405 p_assignment_default_rule_id => g_assign(l_assign_index).assignment_default_rule_id,
2406 p_modify_flag => g_assign(l_assign_index).modify_flag,
2407 p_rowid => l_rowid);
2408
2409 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2410 raise FND_API.G_EXC_ERROR;
2411 end if;
2412
2413 p_rowid := l_rowid;
2414 p_position_assignment_id := l_position_assignment_id;
2415
2416 end;
2417 elsif ((g_assign(l_assign_index).effective_start_date > p_effective_start_date) and
2418 ((p_effective_end_date is not null) and
2419 ((g_assign(l_assign_index).effective_end_date is null) or
2420 (g_assign(l_assign_index).effective_end_date > (p_effective_end_date + 1))))) then
2421 begin
2422
2423 Modify_Assignment_WS
2424 (p_return_status => l_return_status,
2425 p_position_assignment_id => l_position_assignment_id,
2426 p_data_extract_id => p_data_extract_id,
2427 p_worksheet_id => p_worksheet_id,
2428 p_position_id => p_position_id,
2429 p_assignment_type => g_assign(l_assign_index).assignment_type,
2430 p_attribute_id => g_assign(l_assign_index).attribute_id,
2431 p_attribute_value_id => g_assign(l_assign_index).attribute_value_id,
2432 p_attribute_value => g_assign(l_assign_index).attribute_value,
2433 p_pay_element_id => g_assign(l_assign_index).pay_element_id,
2434 p_pay_element_option_id => g_assign(l_assign_index).pay_element_option_id,
2435 p_effective_start_date => p_effective_end_date + 1,
2436 p_effective_end_date => g_assign(l_assign_index).effective_end_date,
2437 p_element_value_type => g_assign(l_assign_index).element_value_type,
2438 p_element_value => g_assign(l_assign_index).element_value,
2439 p_currency_code => nvl(p_currency_code, g_assign(l_assign_index).currency_code),
2440 p_pay_basis => g_assign(l_assign_index).pay_basis,
2441 p_employee_id => g_assign(l_assign_index).employee_id,
2442 p_primary_employee_flag => g_assign(l_assign_index).primary_employee_flag,
2443 p_global_default_flag => g_assign(l_assign_index).global_default_flag,
2444 p_assignment_default_rule_id => g_assign(l_assign_index).assignment_default_rule_id,
2445 p_modify_flag => g_assign(l_assign_index).modify_flag,
2446 p_rowid => l_rowid);
2447
2448 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2449 raise FND_API.G_EXC_ERROR;
2450 end if;
2451
2452 p_rowid := l_rowid;
2453 p_position_assignment_id := l_position_assignment_id;
2454
2455 end;
2456 end if;
2457
2458 if not FND_API.to_Boolean(l_created_record) then
2459 begin
2460
2461 PSB_POSITION_ASSIGNMENTS_PVT.Insert_Row
2462 (p_api_version => 1.0,
2463 p_return_status => l_return_status,
2464 p_msg_count => l_msg_count,
2465 p_msg_data => l_msg_data,
2466 p_rowid => l_rowid,
2467 p_position_assignment_id => l_position_assignment_id,
2468 p_data_extract_id => p_data_extract_id,
2469 p_worksheet_id => p_worksheet_id,
2470 p_position_id => p_position_id,
2471 p_assignment_type => p_assignment_type,
2472 p_attribute_id => p_attribute_id,
2473 p_attribute_value_id => p_attribute_value_id,
2474 p_attribute_value => p_attribute_value,
2475 p_pay_element_id => p_pay_element_id,
2476 p_pay_element_option_id => p_pay_element_option_id,
2477 p_effective_start_date => p_effective_start_date,
2478 p_effective_end_date => p_effective_end_date,
2479 p_element_value_type => p_element_value_type,
2480 p_element_value => p_element_value,
2481 p_currency_code => nvl(p_currency_code, g_assign(l_assign_index).currency_code),
2482 p_pay_basis => g_assign(l_assign_index).pay_basis,
2483 p_employee_id => p_employee_id,
2484 p_primary_employee_flag => p_primary_employee_flag,
2485 p_global_default_flag => p_global_default_flag,
2486 p_assignment_default_rule_id => p_assignment_default_rule_id,
2487 p_modify_flag => p_modify_flag,
2488 p_mode => p_mode);
2489
2490 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2491 raise FND_API.G_EXC_ERROR;
2492 else
2493 l_created_record := FND_API.G_TRUE;
2494 end if;
2495
2496 p_rowid := l_rowid;
2497 p_position_assignment_id := l_position_assignment_id;
2498
2499 end;
2500 end if;
2501
2502 if p_effective_end_date is not null then
2503 begin
2504
2505 if nvl(g_assign(l_assign_index).effective_end_date, (p_effective_end_date + 1)) > (p_effective_end_date + 1) then
2506 begin
2507
2508 Modify_Assignment_WS
2509 (p_return_status => l_return_status,
2510 p_position_assignment_id => l_position_assignment_id,
2511 p_data_extract_id => g_assign(l_assign_index).data_extract_id,
2512 p_worksheet_id => p_worksheet_id,
2513 p_position_id => g_assign(l_assign_index).position_id,
2514 p_assignment_type => g_assign(l_assign_index).assignment_type,
2515 p_attribute_id => g_assign(l_assign_index).attribute_id,
2516 p_attribute_value_id => g_assign(l_assign_index).attribute_value_id,
2517 p_attribute_value => g_assign(l_assign_index).attribute_value,
2518 p_pay_element_id => g_assign(l_assign_index).pay_element_id,
2519 p_pay_element_option_id => g_assign(l_assign_index).pay_element_option_id,
2520 p_effective_start_date => p_effective_end_date + 1,
2521 p_effective_end_date => g_assign(l_assign_index).effective_end_date,
2522 p_element_value_type => g_assign(l_assign_index).element_value_type,
2523 p_element_value => g_assign(l_assign_index).element_value,
2524 p_currency_code => g_assign(l_assign_index).currency_code,
2525 p_pay_basis => g_assign(l_assign_index).pay_basis,
2526 p_employee_id => g_assign(l_assign_index).employee_id,
2527 p_primary_employee_flag => g_assign(l_assign_index).primary_employee_flag,
2528 p_global_default_flag => g_assign(l_assign_index).global_default_flag,
2529 p_assignment_default_rule_id => g_assign(l_assign_index).assignment_default_rule_id,
2530 p_modify_flag => g_assign(l_assign_index).modify_flag,
2531 p_rowid => l_rowid);
2532
2533 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2534 raise FND_API.G_EXC_ERROR;
2535 end if;
2536
2537 p_rowid := l_rowid;
2538 p_position_assignment_id := l_position_assignment_id;
2539
2540 end;
2541 end if;
2542
2543 end;
2544 end if;
2545
2546 end;
2547 end if;
2548
2549 end;
2550 end if;
2551
2552 end loop;
2553
2554 end;
2555 end if;
2556
2557 end;
2558 end if;
2559
2560 --+ finally, delete all overlap records with delete_flag set only if WS id is not null
2561 -- ** Deleting only worksheet specific records is a problem
2562 -- (i) This could result in created or updated records which overlaps the original overlaps
2563 -- that were not deleted for base assignments.
2564 -- i.e., input record: 01-jun-99 -- 01-jul-00
2565 -- overlap 01-jul-97 -- 29-jun-99
2566 -- 30-jun-99 -- 01-jul-00 ** this record is not processed because it did not
2567 -- pass the date test
2568 --
2569 -- Result: 01-jul-97 -- 31-may-99 updated record
2570 -- 01-jul-99 -- 01-jul-00 new record
2571 -- 30-jun-99 -- 01-jul-00 original overlap not deleted
2572 --
2573 -- (ii) This logic, in combination with ignoring overlaps which do not meet the input start/end dates
2574 -- will cause in deleting any WS specific record but not process the input record. This will
2575 -- result in only the original base assignments.
2576 --
2577 for l_assign_index in 1..g_num_assign loop
2578
2579 if (
2580 /* start bug 4153562 */
2581 -- we need to delete in case we have any overlap records
2582 (PSB_HR_POPULATE_DATA_PVT.g_extract_method = 'REFRESH') OR
2583 /* end bug 4153562 */
2584 (FND_API.to_Boolean(g_assign(l_assign_index).delete_flag)) and (g_assign(l_assign_index).worksheet_id is not null)
2585 ) then
2586 begin
2587
2588 /* Start bug 4153562 */
2589 -- if the method is refresh and the overlap start date
2590 -- is greater than the input start date, then delete the overlap record
2591 -- as it again created the overlap record. This is only in case of refresh
2592 -- for worksheet specific records, it is bound to create the records, this
2593 -- logic is not being used.
2594
2595 IF (PSB_HR_POPULATE_DATA_PVT.g_extract_method = 'REFRESH') THEN
2596 IF ( g_assign(l_assign_index).effective_start_date > p_effective_start_date ) THEN
2597
2598 PSB_POSITION_ASSIGNMENTS_PVT.Delete_Row
2599 (p_api_version => 1.0,
2600 p_return_status => l_return_status,
2601 p_msg_count => l_msg_count,
2602 p_msg_data => l_msg_data,
2603 p_position_assignment_id => g_assign(l_assign_index).position_assignment_id);
2604
2605 END IF;
2606 ELSE
2607 /* End bug 4153562 */
2608
2609 PSB_POSITION_ASSIGNMENTS_PVT.Delete_Row
2610 (p_api_version => 1.0,
2611 p_return_status => l_return_status,
2612 p_msg_count => l_msg_count,
2613 p_msg_data => l_msg_data,
2614 p_position_assignment_id => g_assign(l_assign_index).position_assignment_id);
2615
2616 /* Start Bug 4153562 */
2617 END IF;
2618 /* End Bug 4153562 */
2619
2620 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2621 raise FND_API.G_EXC_ERROR;
2622 end if;
2623
2624 end;
2625 end if;
2626
2627 end loop;
2628
2629 end;
2630 end if;
2631
2632 -- adding this for position control integration so that positions are automatically added to
2633 -- position sets when attribute assignments are changed inside the worksheet
2634
2635 if p_rowid is not null then
2636 begin
2637
2638 PSB_BUDGET_POSITION_PVT.Add_Position_To_Position_Sets
2639 (p_api_version => 1.0,
2640 p_return_status => l_return_status,
2641 p_msg_count => l_msg_count,
2642 p_msg_data => l_msg_data,
2643 p_position_id => p_position_id);
2644
2645 end;
2646 end if;
2647
2648 p_return_status := FND_API.G_RET_STS_SUCCESS;
2649
2650
2651 -- Standard check of p_commit.
2652
2653 if FND_API.to_Boolean (p_commit) then
2654 commit work;
2655 end if;
2656
2657 -- Standard call to get message count and if count is 1, get message info.
2658
2659 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
2660 p_data => p_msg_data);
2661
2662 EXCEPTION
2663
2664 when FND_API.G_EXC_ERROR then
2665 p_return_status := FND_API.G_RET_STS_ERROR;
2666
2667 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
2668 p_data => p_msg_data);
2669
2670 when FND_API.G_EXC_UNEXPECTED_ERROR then
2671 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2672
2673 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
2674 p_data => p_msg_data);
2675
2676 when OTHERS then
2677 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2678
2679 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) then
2680 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
2681 l_api_name);
2682 end if;
2683
2684 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
2685 p_data => p_msg_data);
2686
2687 END Modify_Assignment;
2688
2689 /*----------------------------------------------------------------*/
2690
2691 PROCEDURE Modify_Assignment_WS
2692 ( p_return_status OUT NOCOPY VARCHAR2,
2693 p_position_assignment_id IN OUT NOCOPY NUMBER,
2694 p_data_extract_id IN NUMBER,
2695 p_worksheet_id IN NUMBER,
2696 p_position_id IN NUMBER,
2697 p_assignment_type IN VARCHAR2,
2698 p_attribute_id IN NUMBER,
2699 p_attribute_value_id IN NUMBER,
2700 p_attribute_value IN VARCHAR2,
2701 p_pay_element_id IN NUMBER,
2702 p_pay_element_option_id IN NUMBER,
2703 p_effective_start_date IN DATE,
2704 p_effective_end_date IN DATE,
2705 p_element_value_type IN VARCHAR2,
2706 p_element_value IN NUMBER,
2707 p_currency_code IN VARCHAR2,
2708 p_pay_basis IN VARCHAR2,
2709 p_employee_id IN NUMBER,
2710 p_primary_employee_flag IN VARCHAR2,
2711 p_global_default_flag IN VARCHAR2,
2712 p_assignment_default_rule_id IN NUMBER,
2713 p_modify_flag IN VARCHAR2,
2714 p_rowid IN OUT NOCOPY VARCHAR2
2715 ) IS
2716
2717 cursor c_Overlap is
2718 select position_assignment_id
2719 from PSB_POSITION_ASSIGNMENTS
2720 where worksheet_id = p_worksheet_id
2721 and (((p_assignment_type = 'ATTRIBUTE')
2722 and (attribute_id = p_attribute_id))
2723 or ((p_assignment_type = 'EMPLOYEE')
2724 and (employee_id = p_employee_id))
2725 or ((p_assignment_type = 'ELEMENT')
2726 and (pay_element_id = p_pay_element_id)
2727 and ((p_currency_code is null) or (currency_code = p_currency_code))))
2728 and ((((p_effective_end_date is not null)
2729 and ((effective_start_date <= p_effective_end_date)
2730 and (effective_end_date is null))
2731 or ((effective_start_date between p_effective_start_date and p_effective_end_date)
2732 or (effective_end_date between p_effective_start_date and p_effective_end_date)
2733 or ((effective_start_date < p_effective_start_date)
2734 and (effective_end_date > p_effective_end_date)))))
2735 or ((p_effective_end_date is null)
2736 and (nvl(effective_end_date, p_effective_start_date) >= p_effective_start_date)))
2737 and position_id = p_position_id;
2738
2739 cursor c_Salary is
2740 select salary_flag
2741 from PSB_PAY_ELEMENTS
2742 where pay_element_id = p_pay_element_id;
2743
2744 l_return_status VARCHAR2(1);
2745 l_msg_count NUMBER;
2746 l_msg_data VARCHAR2(2000);
2747
2748 l_salary_flag VARCHAR2(1);
2749 l_salary_failed VARCHAR2(1);
2750
2751 l_position_assignment_id NUMBER;
2752 l_rowid VARCHAR2(100);
2753 l_assignment_found VARCHAR2(1) := FND_API.G_FALSE;
2754
2755 BEGIN
2756
2757 for c_Overlap_Rec in c_Overlap loop
2758 l_assignment_found := FND_API.G_TRUE;
2759 end loop;
2760
2761 if not FND_API.to_Boolean(l_assignment_found) then
2762 -- removed salary validation since we now process salary as a set of all salary elements
2763 -- and not individually as the input salary(pay element id). The main api modify_assignments
2764 -- should process all the overlaps records - deleting all existing overlaps
2765
2766 begin
2767
2768 PSB_POSITION_ASSIGNMENTS_PVT.Insert_Row
2769 (p_api_version => 1.0,
2770 p_return_status => l_return_status,
2771 p_msg_count => l_msg_count,
2772 p_msg_data => l_msg_data,
2773 p_rowid => l_rowid,
2774 p_position_assignment_id => l_position_assignment_id,
2775 p_data_extract_id => p_data_extract_id,
2776 p_worksheet_id => p_worksheet_id,
2777 p_position_id => p_position_id,
2778 p_assignment_type => p_assignment_type,
2779 p_attribute_id => p_attribute_id,
2780 p_attribute_value_id => p_attribute_value_id,
2781 p_attribute_value => p_attribute_value,
2782 p_pay_element_id => p_pay_element_id,
2783 p_pay_element_option_id => p_pay_element_option_id,
2784 p_effective_start_date => p_effective_start_date,
2785 p_effective_end_date => p_effective_end_date,
2786 p_element_value_type => p_element_value_type,
2787 p_element_value => p_element_value,
2788 p_currency_code => p_currency_code,
2789 p_pay_basis => p_pay_basis,
2790 p_employee_id => p_employee_id,
2791 p_primary_employee_flag => p_primary_employee_flag,
2792 p_global_default_flag => p_global_default_flag,
2793 p_assignment_default_rule_id => p_assignment_default_rule_id,
2794 p_modify_flag => p_modify_flag);
2795
2796 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2797 raise FND_API.G_EXC_ERROR;
2798 end if;
2799
2800 p_rowid := l_rowid;
2801 p_position_assignment_id := l_position_assignment_id;
2802
2803
2804 end;
2805 end if;
2806
2807 p_return_status := FND_API.G_RET_STS_SUCCESS;
2808
2809
2810 EXCEPTION
2811
2812 when FND_API.G_EXC_ERROR then
2813 p_return_status := FND_API.G_RET_STS_ERROR;
2814
2815 when FND_API.G_EXC_UNEXPECTED_ERROR then
2816 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2817
2818 when OTHERS then
2819 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2820
2821 END Modify_Assignment_WS;
2822
2823 /* ------------------------------------------------------------------------- */
2824
2825 PROCEDURE Create_Default_Assignments
2826 ( p_api_version IN NUMBER,
2827 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2828 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2829 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2830 p_return_status OUT NOCOPY VARCHAR2,
2831 p_msg_count OUT NOCOPY NUMBER,
2832 p_msg_data OUT NOCOPY VARCHAR2,
2833 p_worksheet_id IN NUMBER := FND_API.G_MISS_NUM,
2834 p_data_extract_id IN NUMBER,
2835 p_position_id IN NUMBER := FND_API.G_MISS_NUM,
2836 p_position_start_date IN DATE := FND_API.G_MISS_DATE,
2837 p_position_end_date IN DATE := FND_API.G_MISS_DATE,
2838 p_ruleset_id IN NUMBER
2839 )
2840 IS
2841 --
2842 l_api_name CONSTANT VARCHAR2(30) := 'Create_Default_Assignments';
2843 l_api_version CONSTANT NUMBER := 1.0;
2844 --
2845 l_return_status VARCHAR2(1);
2846 l_position_start_date DATE;
2847 l_position_end_date DATE;
2848 l_position_id_tbl Number_tbl_type;
2849 l_vacant_position_flag_tbl Character_tbl_type;
2850 l_effective_start_date_tbl Date_tbl_type;
2851 l_effective_end_date_tbl Date_tbl_type;
2852 --
2853 CURSOR l_positions_csr IS
2854 SELECT position_id,
2855 vacant_position_flag,
2856 effective_start_date,
2857 effective_end_date
2858 FROM psb_positions
2859 WHERE data_extract_id = p_data_extract_id ;
2860 --
2861 CURSOR c_Position IS
2862 SELECT effective_start_date,
2863 effective_end_date
2864 FROM psb_positions
2865 WHERE position_id = p_position_id ;
2866 --
2867 BEGIN
2868
2869 -- Standard call to check for call compatibility.
2870 IF NOT FND_API.Compatible_API_Call (l_api_version,
2871 p_api_version,
2872 l_api_name,
2873 G_PKG_NAME)
2874 THEN
2875 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2876 END IF ;
2877
2878 -- Check if default rules to be applied for all positions or not.
2879 IF p_position_id = FND_API.G_MISS_NUM THEN
2880
2881 -- Apply default rules to the all positions.
2882 OPEN l_positions_csr ;
2883 LOOP
2884
2885 l_position_id_tbl.DELETE ;
2886 FETCH l_positions_csr BULK COLLECT INTO l_position_id_tbl ,
2887 l_vacant_position_flag_tbl ,
2888 l_effective_start_date_tbl ,
2889 l_effective_end_date_tbl
2890 LIMIT 500 ;
2891
2892 IF l_position_id_tbl.COUNT = 0 THEN
2893 EXIT;
2894 END IF;
2895
2896 -- Loop to process positions in the current bulk fetch.
2897 FOR i IN 1..l_position_id_tbl.COUNT
2898 LOOP
2899
2900 /* For Bug 4644241 --> Reverting Back to the old fix
2901 This will maintain the old functionality. Added Ruleset ID Check */
2902
2903 IF ( p_ruleset_id IS NOT NULL ) OR
2904 ( l_vacant_position_flag_tbl(i) = 'Y' AND p_ruleset_id IS NULL) THEN
2905 -- added the extra parameter p_ruleset_id
2906
2907 Create_Assignment_Position
2908 ( p_return_status => l_return_status,
2909 p_worksheet_id => p_worksheet_id,
2910 p_data_extract_id => p_data_extract_id,
2911 p_position_id => l_position_id_tbl(i),
2912 p_position_start_date => l_effective_start_date_tbl(i),
2913 p_position_end_date => l_effective_end_date_tbl(i),
2914 p_ruleset_id => p_ruleset_id
2915 ) ;
2916
2917 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2918 RAISE FND_API.G_EXC_ERROR;
2919 END IF ;
2920
2921 -- 1308558. added the extra parameter p_ruleset_id
2922 Create_Distribution_Position
2923 ( p_return_status => l_return_status,
2924 p_worksheet_id => p_worksheet_id,
2925 p_data_extract_id => p_data_extract_id,
2926 p_position_id => l_position_id_tbl(i),
2927 p_position_start_date => l_effective_start_date_tbl(i),
2928 p_position_end_date => l_effective_end_date_tbl(i),
2929 p_ruleset_id => p_ruleset_id
2930 );
2931
2932 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2933 RAISE FND_API.G_EXC_ERROR;
2934 END IF ;
2935
2936 /* For Bug 4644241 --> Reverting Back to the old fix
2937 This will maintain the old functionality. Added Ruleset ID Check */
2938
2939 ELSIF ( l_vacant_position_flag_tbl(i) IS NULL OR
2940 l_vacant_position_flag_tbl(i) = 'N' ) AND ( p_ruleset_id IS NULL)
2941 THEN
2942
2943 Create_Element_Assignment
2944 ( p_return_status => l_return_status,
2945 p_worksheet_id => p_worksheet_id,
2946 p_data_extract_id => p_data_extract_id,
2947 p_position_id => l_position_id_tbl(i),
2948 p_position_start_date => l_effective_start_date_tbl(i),
2949 p_position_end_date => l_effective_end_date_tbl(i)
2950 ) ;
2951 --
2952 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2953 RAISE FND_API.G_EXC_ERROR;
2954 END IF ;
2955
2956 END IF ;
2957
2958 /* For Bug 4644241 --> Reverting Back to the old fix
2959 This will maintain the old functionality. Reimplementing the apply_global_default
2960 API. */
2961
2962 IF (p_ruleset_id IS NULL) THEN
2963 Apply_Global_Default
2964 ( p_return_status => l_return_status,
2965 p_worksheet_id => p_worksheet_id,
2966 p_data_extract_id => p_data_extract_id,
2967 p_position_id => l_position_id_tbl(i),
2968 p_position_start_date => l_effective_start_date_tbl(i),
2969 p_position_end_date => l_effective_end_date_tbl(i)
2970 );
2971 --
2972 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2973 RAISE FND_API.G_EXC_ERROR;
2974 END IF;
2975 END IF;
2976
2977
2978 END LOOP ;
2979 -- End loop to process positions in the current bulk fetch.
2980
2981 -- Commit all processed positions to keep memory consumption small.
2982 COMMIT ;
2983
2984 END LOOP ;
2985 -- End applying default rules to the all positions.
2986
2987 ELSE
2988
2989 -- Apply default rules to the given position only.
2990 if ((p_position_start_date = FND_API.G_MISS_DATE) or
2991 (p_position_end_date = FND_API.G_MISS_DATE))
2992 then
2993 --
2994 for c_Position_Rec in c_Position loop
2995 l_position_start_date := c_Position_Rec.effective_start_date;
2996 l_position_end_date := c_Position_Rec.effective_end_date;
2997 end loop;
2998 --
2999 end if;
3000
3001 if p_position_start_date <> FND_API.G_MISS_DATE then
3002 l_position_start_date := p_position_start_date;
3003 end if;
3004
3005 if p_position_end_date <> FND_API.G_MISS_DATE then
3006 l_position_end_date := p_position_end_date;
3007 end if;
3008
3009 -- 1308558. added the extra parameter p_ruleset_id
3010 Create_Assignment_Position
3011 (p_return_status => l_return_status,
3012 p_worksheet_id => p_worksheet_id,
3013 p_data_extract_id => p_data_extract_id,
3014 p_position_id => p_position_id,
3015 p_position_start_date => l_position_start_date,
3016 p_position_end_date => l_position_end_date,
3017 p_ruleset_id => p_ruleset_id);
3018
3019 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
3020 raise FND_API.G_EXC_ERROR;
3021 end if;
3022
3023 -- 1308558. added the extra parameter p_ruleset_id
3024 Create_Distribution_Position
3025 (p_return_status => l_return_status,
3026 p_worksheet_id => p_worksheet_id,
3027 p_data_extract_id => p_data_extract_id,
3028 p_position_id => p_position_id,
3029 p_position_start_date => l_position_start_date,
3030 p_position_end_date => l_position_end_date,
3031 p_ruleset_id => p_ruleset_id);
3032
3033 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
3034 raise FND_API.G_EXC_ERROR;
3035 end if;
3036
3037 /* For Bug 4644241 --> Reverting Back to the old fix
3038 This will maintain the old functionality. Reimplementing the apply_global_default
3039 API. */
3040
3041 IF (p_ruleset_id IS NULL) THEN
3042 Apply_Global_Default
3043 (p_return_status => l_return_status,
3044 p_worksheet_id => p_worksheet_id,
3045 p_data_extract_id => p_data_extract_id,
3046 p_position_id => p_position_id,
3047 p_position_start_date => l_position_start_date,
3048 p_position_end_date => l_position_end_date);
3049
3050 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
3051 raise FND_API.G_EXC_ERROR;
3052 end if;
3053 END IF;
3054
3055 -- End applying default rules to the given position only.
3056
3057 END IF ;
3058 -- End checkng if default rules to be applied for all positions.
3059
3060 -- Initialize API return status to success
3061 p_return_status := FND_API.G_RET_STS_SUCCESS;
3062
3063 -- Standard check of p_commit.
3064 if FND_API.to_Boolean (p_commit) then
3065 commit work;
3066 end if;
3067
3068 -- Standard call to get message count and if count is 1, get message info.
3069 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
3070 p_data => p_msg_data);
3071 EXCEPTION
3072
3073 when FND_API.G_EXC_ERROR then
3074 p_return_status := FND_API.G_RET_STS_ERROR;
3075
3076 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
3077 p_data => p_msg_data);
3078
3079 when FND_API.G_EXC_UNEXPECTED_ERROR then
3080 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3081
3082 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
3083 p_data => p_msg_data);
3084
3085 when OTHERS then
3086 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3087
3088 if FND_MSG_PUB.Check_Msg_Level
3089 (p_message_level => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3090 then
3091 FND_MSG_PUB.Add_Exc_Msg
3092 (p_pkg_name => G_PKG_NAME,
3093 p_procedure_name => l_api_name);
3094 end if;
3095
3096 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
3097 p_data => p_msg_data);
3098
3099 END Create_Default_Assignments;
3100
3101 /* ------------------------------------------------------------------------- */
3102
3103 -- 1308558 Mass Position Assignment Rules Enhancement
3104 -- added the extra parameter p_ruleset_id for passing the
3105 -- id for the default ruleset
3106
3107 PROCEDURE Create_Assignment_Position
3108 ( p_return_status OUT NOCOPY VARCHAR2,
3109 p_worksheet_id IN NUMBER,
3110 p_data_extract_id IN NUMBER,
3111 p_position_id IN NUMBER,
3112 p_position_start_date IN DATE,
3113 p_position_end_date IN DATE,
3114 p_ruleset_id IN NUMBER
3115 ) IS
3116
3117 l_worksheet_id NUMBER;
3118 l_posasgn_id NUMBER;
3119 l_rowid VARCHAR2(100);
3120
3121 l_msg_count NUMBER;
3122 l_msg_data VARCHAR2(2000);
3123
3124 l_return_status VARCHAR2(1);
3125
3126 /* For Bug 4644241 --> Reverting Back to the old fix
3127 This will maintain the old functionality */
3128
3129 CURSOR c_Assignments is
3130 SELECT a.default_rule_id,
3131 b.priority,
3132 b.global_default_flag,
3133 a.assignment_type,
3134 a.attribute_id,
3135 a.attribute_value_id,
3136 a.attribute_value,
3137 a.pay_element_id,
3138 a.pay_element_option_id,
3139 a.pay_basis,
3140 a.element_value_type,
3141 a.element_value,
3142 a.currency_code
3143 FROM PSB_DEFAULT_ASSIGNMENTS a,
3144 PSB_DEFAULTS b,
3145 PSB_SET_RELATIONS c,
3146 PSB_BUDGET_POSITIONS d
3147 WHERE a.default_rule_id = b.default_rule_id
3148 AND b.priority is not null
3149 AND b.default_rule_id = c.default_rule_id
3150 AND c.account_position_set_id = d.account_position_set_id
3151 AND d.data_extract_id = p_data_extract_id
3152 AND d.position_id = p_position_id
3153 order by b.priority;
3154
3155
3156 /* 1308558 In the following cursor added the join for
3157 selecting only the assignments for a given default ruleset */
3158
3159 -- Bug 4237598 Modified the following cursor
3160 -- so that it will pick rule details for global
3161 -- and non-global default rules
3162
3163 -- Bug 5040737 used order by 2 clause in the following cursor
3164 CURSOR c_Assignment_Ruleset IS
3165 SELECT a.default_rule_id,
3166 f.priority priority,
3167 b.global_default_flag,
3168 b.overwrite,
3169 a.assignment_type,
3170 a.attribute_id,
3171 a.attribute_value_id,
3172 a.attribute_value,
3173 a.pay_element_id,
3174 a.pay_element_option_id,
3175 a.pay_basis,
3176 a.element_value_type,
3177 a.element_value,
3178 a.currency_code
3179 FROM psb_default_assignments a,
3180 psb_defaults b,
3181 psb_set_relations c,
3182 psb_budget_positions d,
3183 psb_entity_set e,
3184 psb_entity_assignment f
3185 WHERE a.default_rule_id = b.default_rule_id
3186 -- AND f.priority IS NOT NULL
3187 AND b.default_rule_id = c.default_rule_id
3188 AND c.account_position_set_id = d.account_position_set_id
3189 AND d.data_extract_id = p_data_extract_id
3190 AND d.position_id = p_position_id
3191 AND e.entity_set_id = f.entity_set_id
3192 AND f.entity_id = b.default_rule_id
3193 AND e.data_extract_id = p_data_extract_id
3194 AND e.entity_type = 'DEFAULT_RULE'
3195 AND e.entity_set_id = p_ruleset_id
3196 UNION
3197 SELECT a.default_rule_id,
3198 d.priority priority,
3199 b.global_default_flag,
3200 b.overwrite,
3201 a.assignment_type,
3202 a.attribute_id,
3203 a.attribute_value_id,
3204 a.attribute_value,
3205 a.pay_element_id,
3206 a.pay_element_option_id,
3207 a.pay_basis,
3208 a.element_value_type,
3209 a.element_value,
3210 a.currency_code
3211 FROM psb_default_assignments a,
3212 psb_defaults b,
3213 psb_entity_set c,
3214 psb_entity_assignment d
3215 WHERE a.default_rule_id = b.default_rule_id
3216 AND b.global_default_flag = 'Y'
3217 AND b.data_extract_id = p_data_extract_id
3218 AND c.entity_set_id = d.entity_set_id
3219 AND b.default_rule_id = d.entity_id
3220 AND c.data_extract_id = p_data_extract_id
3221 AND c.entity_type = 'DEFAULT_RULE'
3222 AND c.entity_set_id = p_ruleset_id
3223 ORDER BY 2;
3224
3225 BEGIN
3226
3227
3228 if p_worksheet_id = FND_API.G_MISS_NUM then
3229 l_worksheet_id := null;
3230 else
3231 l_worksheet_id := p_worksheet_id;
3232 end if;
3233
3234 -- 1308558.Mass Position Assignment Rules
3235
3236 IF p_ruleset_id IS NULL THEN
3237
3238 FOR c_Assignments_Rec in c_Assignments LOOP
3239
3240 /* For Bug 4644241 --> Reverting Back to the old fix
3241 This will maintain the old functionality */
3242
3243 Apply_Position_Default_Rules
3244 (p_api_version => 1.0,
3245 x_return_status => l_return_status,
3246 x_msg_count => l_msg_count,
3247 x_msg_data => l_msg_data,
3248 p_position_assignment_id => l_posasgn_id,
3249 p_data_extract_id => p_data_extract_id,
3250 p_position_id => p_position_id,
3251 p_assignment_type => c_Assignments_Rec.assignment_type,
3252 p_attribute_id => c_Assignments_Rec.attribute_id,
3253 p_attribute_value_id => c_Assignments_Rec.attribute_value_id,
3254 p_attribute_value => c_Assignments_Rec.attribute_value,
3255 p_pay_element_id => c_Assignments_Rec.pay_element_id,
3256 p_pay_element_option_id => c_Assignments_Rec.pay_element_option_id,
3257 p_effective_start_date => p_position_start_date,
3258 p_effective_end_date => p_position_end_date,
3259 p_element_value_type => c_Assignments_Rec.element_value_type,
3260 p_element_value => c_Assignments_Rec.element_value,
3261 p_currency_code => c_Assignments_Rec.currency_code,
3262 p_pay_basis => c_Assignments_Rec.pay_basis,
3263 p_employee_id => null,
3264 p_primary_employee_flag => null,
3265 p_global_default_flag => c_Assignments_Rec.global_default_flag,
3266 p_assignment_default_rule_id => c_Assignments_Rec.default_rule_id,
3267 p_modify_flag => 'Y',
3268 p_worksheet_id => null);
3269
3270 IF l_return_status <> fnd_api.g_ret_sts_success THEN
3271 raise FND_API.G_EXC_ERROR;
3272 END IF;
3273
3274 END LOOP;
3275
3276 ELSE
3277
3278 FOR c_Assignments_Rec in c_Assignment_Ruleset
3279 LOOP
3280
3281 Apply_Position_Default_Rules
3282 (p_api_version => 1.0,
3283 x_return_status => l_return_status,
3284 x_msg_count => l_msg_count,
3285 x_msg_data => l_msg_data,
3286 p_position_assignment_id => l_posasgn_id,
3287 p_data_extract_id => p_data_extract_id,
3288 p_position_id => p_position_id,
3289 p_assignment_type => c_Assignments_Rec.assignment_type,
3290 p_attribute_id => c_Assignments_Rec.attribute_id,
3291 p_attribute_value_id => c_Assignments_Rec.attribute_value_id,
3292 p_attribute_value => c_Assignments_Rec.attribute_value,
3293 p_pay_element_id => c_Assignments_Rec.pay_element_id,
3294 p_pay_element_option_id => c_Assignments_Rec.pay_element_option_id,
3295 p_effective_start_date => p_position_start_date,
3296 p_effective_end_date => p_position_end_date,
3297 p_element_value_type => c_Assignments_Rec.element_value_type,
3298 p_element_value => c_Assignments_Rec.element_value,
3299 p_currency_code => c_Assignments_Rec.currency_code,
3300 p_pay_basis => c_Assignments_Rec.pay_basis,
3301 p_employee_id => null,
3302 p_primary_employee_flag => null,
3303 p_global_default_flag => c_Assignments_Rec.global_default_flag,
3304 p_assignment_default_rule_id => c_Assignments_Rec.default_rule_id,
3305 p_modify_flag => c_Assignments_Rec.overwrite,
3306 p_worksheet_id=> null);
3307 END LOOP;
3308
3309 /* Moved the check inside the for loop
3310 as a part of bug fix 4644241 */
3311
3312 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
3313 raise FND_API.G_EXC_ERROR;
3314 end if;
3315
3316 END IF;
3317
3318
3319 -- Initialize API return status to success
3320
3321 p_return_status := FND_API.G_RET_STS_SUCCESS;
3322
3323
3324 EXCEPTION
3325
3326 when FND_API.G_EXC_ERROR then
3327 p_return_status := FND_API.G_RET_STS_ERROR;
3328
3329 when FND_API.G_EXC_UNEXPECTED_ERROR then
3330 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3331
3332 when OTHERS then
3333 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3334
3335 END Create_Assignment_Position;
3336
3337 /* ------------------------------------------------------------------------- */
3338
3339 -- 1308558 Mass Position Assignment Rules Enhancement
3340 -- added the extra parameter p_ruleset_id for passing the
3341 -- id for the default ruleset
3342
3343 PROCEDURE Create_Distribution_Position
3344 ( p_return_status OUT NOCOPY VARCHAR2,
3345 p_worksheet_id IN NUMBER,
3346 p_data_extract_id IN NUMBER,
3347 p_position_id IN NUMBER,
3348 p_position_start_date IN DATE,
3349 p_position_end_date IN DATE,
3350 p_ruleset_id IN NUMBER
3351 ) IS
3352
3353 l_msg_count NUMBER;
3354 l_msg_data VARCHAR2(2000);
3355
3356 l_default_rule_id NUMBER;
3357 l_priority NUMBER;
3358 l_global_default_flag VARCHAR2(1);
3359
3360 l_local_dist_exists VARCHAR2(1) := FND_API.G_FALSE;
3361 l_global_dist_exists VARCHAR2(1) := FND_API.G_FALSE;
3362
3363 l_distribution_id NUMBER;
3364 l_rowid VARCHAR2(100);
3365
3366 l_return_status VARCHAR2(1);
3367 l_overwrite_flag VARCHAR2(1);
3368
3369
3370 /* For Bug 4644241 --> Reverting Back to the old fix
3371 This will maintain the old functionality */
3372 l_exists VARCHAR2(30);
3373
3374
3375 /* For Bug 4644241 --> Reverting Back to the old fix
3376 This will maintain the old functionality. The old cursor c_priority
3377 is retained */
3378
3379 CURSOR c_Priority IS
3380 SELECT a.default_rule_id,
3381 a.priority,
3382 a.global_default_flag
3383 FROM PSB_DEFAULTS a,
3384 PSB_SET_RELATIONS b,
3385 PSB_BUDGET_POSITIONS c
3386 WHERE EXISTS
3387 (SELECT 1
3388 FROM PSB_DEFAULT_ACCOUNT_DISTRS d
3389 WHERE d.default_rule_id = a.default_rule_id)
3390 AND a.priority is not null
3391 AND a.default_rule_id = b.default_rule_id
3392 AND b.account_position_set_id = c.account_position_set_id
3393 AND c.data_extract_id = p_data_extract_id
3394 AND c.position_id = p_position_id
3395 ORDER BY a.priority;
3396
3397
3398
3399 -- 1308558 modified the following cursor to select only
3400 -- the details for a given default ruleset
3401
3402 -- Bug 4237598 Modified the following cursor
3403 -- so that it will pick rule details for global
3404 -- and non-global default rules
3405
3406 -- Bug 5040737 used order by 2 clause in the following cursor
3407 CURSOR c_Priority_ruleset IS
3408 SELECT a.default_rule_id,
3409 f.priority priority,
3410 a.global_default_flag,
3411 a.overwrite
3412 FROM psb_defaults a,
3413 psb_set_relations b,
3414 psb_budget_positions c,
3415 psb_entity_set e,
3416 psb_entity_assignment f
3417 WHERE EXISTS -- Bug 4226623 added the exists clause
3418 (SELECT 1
3419 FROM PSB_DEFAULT_ACCOUNT_DISTRS d
3420 WHERE d.default_rule_id = a.default_rule_id)
3421 -- AND f.priority is not null
3422 AND a.default_rule_id = b.default_rule_id
3423 AND b.account_position_set_id = c.account_position_set_id
3424 AND c.data_extract_id = p_data_extract_id
3425 AND c.position_id = p_position_id
3426 AND e.entity_set_id = f.entity_set_id
3427 AND f.entity_id = a.default_rule_id
3428 AND e.data_extract_id = p_data_extract_id
3429 AND e.entity_type = 'DEFAULT_RULE'
3430 AND e.entity_set_id = p_ruleset_id
3431 UNION
3432 SELECT a.default_rule_id,
3433 c.priority priority,
3434 a.global_default_flag,
3435 a.overwrite
3436 FROM psb_defaults a,
3437 psb_entity_set b,
3438 psb_entity_assignment c
3439 WHERE EXISTS (SELECT 1
3440 FROM PSB_DEFAULT_ACCOUNT_DISTRS d
3441 WHERE d.default_rule_id = a.default_rule_id)
3442 AND a.global_default_flag = 'Y'
3443 AND a.data_extract_id = p_data_extract_id
3444 AND b.entity_set_id = c.entity_set_id
3445 AND a.default_rule_id = c.entity_id
3446 AND b.data_extract_id = p_data_extract_id
3447 AND b.entity_type = 'DEFAULT_RULE'
3448 AND b.entity_set_id = p_ruleset_id
3449 ORDER BY 2;
3450
3451 /* For Bug 4644241 --> Reverting Back to the old fix
3452 This will maintain the old functionality */
3453 TYPE l_global_dist_csr_type IS REF CURSOR;
3454 l_global_dist_csr l_global_dist_csr_type;
3455
3456
3457 cursor c_Dist is
3458 select chart_of_accounts_id,
3459 code_combination_id,
3460 distribution_percent
3461 from PSB_DEFAULT_ACCOUNT_DISTRS
3462 where default_rule_id = l_default_rule_id;
3463
3464 CURSOR l_distribution_id_csr
3465 IS
3466 SELECT *
3467 FROM PSB_POSITION_PAY_DISTRIBUTIONS
3468 WHERE (((p_position_end_date IS NOT NULL)
3469 AND (((effective_start_date <= p_position_end_date)
3470 AND (effective_end_date IS NULL))
3471 OR ((effective_start_date BETWEEN p_position_start_date AND p_position_end_date)
3472 OR (effective_end_date BETWEEN p_position_start_date AND p_position_end_date)
3473 OR ((effective_start_date < p_position_start_date)
3474 AND (effective_end_date > p_position_end_date)))))
3475 OR ((p_position_end_date IS NULL)
3476 AND (NVL(effective_end_date, p_position_start_date) >= p_position_start_date)))
3477 AND data_extract_id = p_data_extract_id
3478 AND position_id = p_position_id
3479 /* Bug 4545909 Start */
3480 AND ((worksheet_id IS NULL AND NOT EXISTS
3481 (SELECT 1 FROM psb_position_pay_distributions
3482 WHERE worksheet_id = p_worksheet_id
3483 AND position_id = p_position_id))
3484 OR worksheet_id = p_worksheet_id
3485 OR(worksheet_id IS NULL AND p_worksheet_id IS NULL));
3486 /* Bug 4545909 End */
3487
3488 BEGIN
3489
3490 IF p_ruleset_id is NULL THEN
3491
3492 FOR c_Priority_Rec in c_Priority LOOP
3493
3494 IF c_Priority_Rec.priority <> nvl(l_priority, FND_API.G_MISS_NUM) THEN
3495 l_default_rule_id := c_Priority_Rec.default_rule_id;
3496 l_priority := c_Priority_Rec.priority;
3497 l_global_default_flag := c_Priority_Rec.global_default_flag;
3498 END IF;
3499
3500 l_local_dist_exists := FND_API.G_TRUE;
3501
3502 END LOOP;
3503
3504 /* For Bug 4644241 --> Reverting Back to the old fix
3505 This will maintain the old functionality */
3506 OPEN l_global_dist_csr FOR
3507 SELECT 'Exists'
3508 FROM dual
3509 WHERE EXISTS
3510 (SELECT 1
3511 FROM PSB_DEFAULT_ACCOUNT_DISTRS a,
3512 PSB_DEFAULTS b
3513 WHERE a.default_rule_id = b.default_rule_id
3514 AND b.global_default_flag = 'Y'
3515 AND b.data_extract_id = p_data_extract_id);
3516 FETCH l_global_dist_csr INTO l_exists;
3517 CLOSE l_global_dist_csr;
3518
3519 IF l_exists IS NOT NULL THEN
3520 l_global_dist_exists := FND_API.G_TRUE;
3521 END IF;
3522
3523 IF ((FND_API.to_Boolean(l_local_dist_exists)) OR
3524 (FND_API.to_Boolean(l_global_dist_exists))) THEN
3525 BEGIN
3526
3527 PSB_POSITION_PAY_DISTR_PVT.Delete_Distributions_Position
3528 (p_api_version => 1.0,
3529 p_return_status => l_return_status,
3530 p_msg_count => l_msg_count,
3531 p_msg_data => l_msg_data,
3532 p_position_id => p_position_id);
3533
3534 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3535 raise FND_API.G_EXC_ERROR;
3536 END IF;
3537
3538 END;
3539 END IF;
3540
3541 /* For Bug 4644241 --> Reverting Back to the old fix
3542 This will maintain the old functionality */
3543
3544 IF NOT FND_API.to_Boolean(l_global_dist_exists) THEN
3545
3546 FOR c_Dist_Rec in c_Dist LOOP
3547
3548 PSB_POSITION_PAY_DISTR_PVT.Modify_Distribution_WS
3549 (p_api_version => 1.0,
3550 p_return_status => l_return_status,
3551 p_msg_count => l_msg_count,
3552 p_msg_data => l_msg_data,
3553 p_worksheet_id => p_worksheet_id,
3554 p_distribution_id => l_distribution_id,
3555 p_position_id => p_position_id,
3556 p_data_extract_id => p_data_extract_id,
3557 p_effective_start_date => p_position_start_date,
3558 p_effective_end_date => p_position_end_date,
3559 p_chart_of_accounts_id => c_Dist_Rec.chart_of_accounts_id,
3560 p_code_combination_id => c_Dist_Rec.code_combination_id,
3561 p_distribution_percent => c_Dist_Rec.distribution_percent,
3562 p_global_default_flag => l_global_default_flag,
3563 p_distribution_default_rule_id => l_default_rule_id,
3564 p_rowid => l_rowid);
3565
3566 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3567 raise FND_API.G_EXC_ERROR;
3568 END IF;
3569 END LOOP;
3570 END IF;
3571
3572 ELSE -- gets executed when p_ruleset_id is not null
3573
3574 FOR c_Priority_Rec in c_Priority_ruleset LOOP
3575
3576 -- IF c_Priority_Rec.priority <> nvl(l_priority, FND_API.G_MISS_NUM) THEN
3577 l_default_rule_id := c_Priority_Rec.default_rule_id;
3578 l_priority := c_Priority_Rec.priority;
3579 l_global_default_flag := c_Priority_Rec.global_default_flag;
3580 -- END IF;
3581
3582 IF NVL(c_priority_rec.global_default_flag,'N') = 'N' THEN
3583 l_local_dist_exists := FND_API.G_TRUE;
3584 END IF;
3585
3586 l_overwrite_flag := c_priority_rec.overwrite;
3587
3588 IF l_overwrite_flag IS NULL THEN
3589 l_overwrite_flag := 'N';
3590 END IF;
3591
3592 /* For Bug 4644241 --> Reverting Back to the old fix
3593 This will maintain the old functionality */
3594
3595 OPEN l_global_dist_csr FOR
3596 SELECT 'Exists'
3597 FROM dual
3598 WHERE EXISTS
3599 (SELECT 1
3600 FROM PSB_DEFAULT_ACCOUNT_DISTRS a,
3601 PSB_DEFAULTS b
3602 WHERE a.default_rule_id = b.default_rule_id
3603 AND b.global_default_flag = 'Y'
3604 AND b.data_extract_id = p_data_extract_id
3605 AND a.default_rule_id = l_default_rule_id
3606 );
3607 FETCH l_global_dist_csr INTO l_exists;
3608 CLOSE l_global_dist_csr;
3609
3610
3611 IF l_exists IS NOT NULL THEN
3612 l_global_dist_exists := FND_API.G_TRUE;
3613 END IF;
3614
3615 IF l_overwrite_flag <> 'N' THEN
3616
3617 IF ((FND_API.to_Boolean(l_local_dist_exists)) OR
3618 (FND_API.to_Boolean(l_global_dist_exists))) THEN
3619 BEGIN
3620
3621 PSB_POSITION_PAY_DISTR_PVT.Delete_Distributions_Position
3622 (p_api_version => 1.0,
3623 p_return_status => l_return_status,
3624 p_msg_count => l_msg_count,
3625 p_msg_data => l_msg_data,
3626 p_position_id => p_position_id,
3627 p_worksheet_id => NULL);
3628
3629 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3630 raise FND_API.G_EXC_ERROR;
3631 END IF;
3632 END;
3633 END IF;
3634 END IF;
3635
3636 g_distr_percent_total:= 0;
3637
3638 FOR l_distribution_id_csr_rec IN l_distribution_id_csr
3639 LOOP
3640 g_distr_percent_total
3641 := g_distr_percent_total + l_distribution_id_csr_rec.distribution_percent;
3642 END LOOP;
3643 -- Bug 4237598 commented the following condition
3644 -- IF NOT FND_API.to_Boolean(l_global_dist_exists) THEN
3645 -- BEGIN
3646
3647 FOR c_Dist_Rec in c_Dist LOOP
3648
3649
3650 PSB_POSITION_PAY_DISTR_PVT.Modify_Distribution_WS
3651 (p_api_version => 1.0,
3652 p_return_status => l_return_status,
3653 p_msg_count => l_msg_count,
3654 p_msg_data => l_msg_data,
3655 p_worksheet_id => NULL,
3656 p_distribution_id => l_distribution_id,
3657 p_position_id => p_position_id,
3658 p_data_extract_id => p_data_extract_id,
3659 p_effective_start_date => p_position_start_date,
3660 p_effective_end_date => p_position_end_date,
3661 p_modify_flag => l_overwrite_flag,
3662 p_chart_of_accounts_id => c_Dist_Rec.chart_of_accounts_id,
3663 p_code_combination_id => c_Dist_Rec.code_combination_id,
3664 p_distribution_percent => c_Dist_Rec.distribution_percent,
3665 p_global_default_flag => l_global_default_flag,
3666 p_distribution_default_rule_id => l_default_rule_id,
3667 p_rowid => l_rowid,
3668 p_ruleset_id => p_ruleset_id);
3669
3670 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3671 raise FND_API.G_EXC_ERROR;
3672 END IF;
3673
3674 END LOOP;
3675
3676 -- END;
3677 -- END IF;
3678
3679 END LOOP;
3680
3681 END IF;
3682
3683 -- Initialize API return status to success
3684
3685 p_return_status := FND_API.G_RET_STS_SUCCESS;
3686
3687
3688 EXCEPTION
3689
3690 when FND_API.G_EXC_ERROR then
3691 p_return_status := FND_API.G_RET_STS_ERROR;
3692
3693 when FND_API.G_EXC_UNEXPECTED_ERROR then
3694 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3695
3696 when OTHERS then
3697 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3698
3699 END Create_Distribution_Position;
3700
3701 /* ------------------------------------------------------------------------- */
3702
3703 PROCEDURE Create_Element_Assignment
3704 ( p_return_status OUT NOCOPY VARCHAR2,
3705 p_worksheet_id IN NUMBER,
3706 p_data_extract_id IN NUMBER,
3707 p_position_id IN NUMBER,
3708 p_position_start_date IN DATE,
3709 p_position_end_date IN DATE
3710 ) IS
3711
3712 l_api_name CONSTANT VARCHAR2(30) := 'Create_Element_Assignment';
3713 l_api_version CONSTANT NUMBER := 1.0;
3714
3715 l_worksheet_id NUMBER;
3716 l_posasgn_id NUMBER;
3717 l_rowid VARCHAR2(100);
3718
3719 l_msg_count NUMBER;
3720 l_msg_data VARCHAR2(2000);
3721
3722 l_return_status VARCHAR2(1);
3723
3724 cursor c_Assignments is
3725 select a.default_rule_id,
3726 b.priority,
3727 b.global_default_flag,
3728 a.pay_element_id,
3729 a.pay_element_option_id,
3730 a.pay_basis,
3731 a.element_value_type,
3732 a.element_value,
3733 a.currency_code
3734 from PSB_DEFAULT_ASSIGNMENTS a,
3735 PSB_DEFAULTS b,
3736 PSB_SET_RELATIONS c,
3737 PSB_BUDGET_POSITIONS d
3738 where EXISTS
3739 ( select 1
3740 from PSB_PAY_ELEMENTS pe
3741 where pe.salary_flag <> 'Y'
3742 and pe.data_extract_id = p_data_extract_id
3743 and pe.pay_element_id = a.pay_element_id
3744 )
3745 and a.assignment_type = 'ELEMENT'
3746 and a.default_rule_id = b.default_rule_id
3747 and b.priority is not null
3748 and b.default_rule_id = c.default_rule_id
3749 and c.account_position_set_id = d.account_position_set_id
3750 and d.data_extract_id = p_data_extract_id
3751 and d.position_id = p_position_id
3752 order by b.priority;
3753
3754 BEGIN
3755
3756 if p_worksheet_id = FND_API.G_MISS_NUM then
3757 l_worksheet_id := null;
3758 else
3759 l_worksheet_id := p_worksheet_id;
3760 end if;
3761
3762 /* for bug 4644241 --> Changed the procedure from Modify Assignment to
3763 apply_default_rules. This is taken care to see that no overlapping
3764 assignment gets created. */
3765
3766 for c_Assignments_Rec in c_Assignments loop
3767
3768 Apply_Position_Default_Rules
3769 (p_api_version => 1.0,
3770 x_return_status => l_return_status,
3771 x_msg_count => l_msg_count,
3772 x_msg_data => l_msg_data,
3773 p_position_assignment_id => l_posasgn_id,
3774 p_data_extract_id => p_data_extract_id,
3775 p_position_id => p_position_id,
3776 p_assignment_type => 'ELEMENT',
3777 p_attribute_id => null,
3778 p_attribute_value_id => null,
3779 p_attribute_value => null,
3780 p_pay_element_id => c_Assignments_Rec.pay_element_id,
3781 p_pay_element_option_id => c_Assignments_Rec.pay_element_option_id,
3782 p_effective_start_date => p_position_start_date,
3783 p_effective_end_date => p_position_end_date,
3784 p_element_value_type => c_Assignments_Rec.element_value_type,
3785 p_element_value => c_Assignments_Rec.element_value,
3786 p_currency_code => c_Assignments_Rec.currency_code,
3787 p_pay_basis => c_Assignments_Rec.pay_basis,
3788 p_employee_id => null,
3789 p_primary_employee_flag => null,
3790 p_global_default_flag => c_Assignments_Rec.global_default_flag,
3791 p_assignment_default_rule_id => c_Assignments_Rec.default_rule_id,
3792 p_modify_flag => 'Y',
3793 p_worksheet_id => null);
3794
3795 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
3796 raise FND_API.G_EXC_ERROR;
3797 end if;
3798
3799 end loop;
3800
3801
3802 -- Initialize API return status to success
3803
3804 p_return_status := FND_API.G_RET_STS_SUCCESS;
3805
3806
3807 EXCEPTION
3808
3809 when FND_API.G_EXC_ERROR then
3810 p_return_status := FND_API.G_RET_STS_ERROR;
3811
3812 when FND_API.G_EXC_UNEXPECTED_ERROR then
3813 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3814
3815 when OTHERS then
3816 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3817
3818 END Create_Element_Assignment;
3819
3820 /* For Bug 4644241 --> Reverting Back to the old fix
3821 This will maintain the old functionality. Old Apply Global
3822 Default before Mass position assignment enhancement retained.
3823 */
3824
3825 /* ------------------------------------------------------------------------- */
3826
3827 PROCEDURE Apply_Global_Default
3828 ( p_return_status OUT NOCOPY VARCHAR2,
3829 p_worksheet_id IN NUMBER,
3830 p_data_extract_id IN NUMBER,
3831 p_position_id IN NUMBER,
3832 p_position_start_date IN DATE,
3833 p_position_end_date IN DATE
3834 ) IS
3835
3836 l_worksheet_id NUMBER;
3837 l_posasgn_id NUMBER;
3838 l_rowid VARCHAR2(100);
3839
3840 l_distribution_id NUMBER;
3841
3842 l_msg_count NUMBER;
3843 l_msg_data VARCHAR2(2000);
3844
3845 l_return_status VARCHAR2(1);
3846
3847 -- For Bug 4644241
3848 l_distr_exists BOOLEAN;
3849
3850
3851
3852 cursor c_Assignments is
3853 select a.default_rule_id,
3854 a.assignment_type,
3855 a.attribute_id,
3856 a.attribute_value_id,
3857 a.attribute_value,
3858 a.pay_element_id,
3859 a.pay_element_option_id,
3860 a.pay_basis,
3861 a.element_value_type,
3862 a.element_value,
3863 a.currency_code
3864 from PSB_DEFAULT_ASSIGNMENTS a,
3865 PSB_DEFAULTS b
3866 where a.default_rule_id = b.default_rule_id
3867 and b.global_default_flag = 'Y'
3868 and b.data_extract_id = p_data_extract_id;
3869
3870 cursor c_Dist is
3871 select a.default_rule_id,
3872 a.chart_of_accounts_id,
3873 a.code_combination_id,
3874 a.distribution_percent
3875 from PSB_DEFAULT_ACCOUNT_DISTRS a,
3876 PSB_DEFAULTS b
3877 where a.default_rule_id = b.default_rule_id
3878 and b.global_default_flag = 'Y'
3879 and b.data_extract_id = p_data_extract_id;
3880
3881 BEGIN
3882
3883 if p_worksheet_id = FND_API.G_MISS_NUM then
3884 l_worksheet_id := null;
3885 else
3886 l_worksheet_id := p_worksheet_id;
3887 end if;
3888
3889 /* For Bug 4644241 --> Change the API call from modify assignment to
3890 apply_position_default_rules. This will take care of not creating
3891 overlapping assignments */
3892
3893 for c_Assignments_Rec in c_Assignments loop
3894
3895 Apply_Position_Default_Rules
3896 (p_api_version => 1.0,
3897 x_return_status => l_return_status,
3898 x_msg_count => l_msg_count,
3899 x_msg_data => l_msg_data,
3900 p_position_assignment_id => l_posasgn_id,
3901 p_data_extract_id => p_data_extract_id,
3902 p_position_id => p_position_id,
3903 p_assignment_type => c_Assignments_Rec.assignment_type,
3904 p_attribute_id => c_Assignments_Rec.attribute_id,
3905 p_attribute_value_id => c_Assignments_Rec.attribute_value_id,
3906 p_attribute_value => c_Assignments_Rec.attribute_value,
3907 p_pay_element_id => c_Assignments_Rec.pay_element_id,
3908 p_pay_element_option_id => c_Assignments_Rec.pay_element_option_id,
3909 p_effective_start_date => p_position_start_date,
3910 p_effective_end_date => p_position_end_date,
3911 p_element_value_type => c_Assignments_Rec.element_value_type,
3912 p_element_value => c_Assignments_Rec.element_value,
3913 p_currency_code => c_Assignments_Rec.currency_code,
3914 p_pay_basis => c_Assignments_Rec.pay_basis,
3915 p_employee_id => null,
3916 p_primary_employee_flag => null,
3917 p_global_default_flag => 'Y',
3918 p_assignment_default_rule_id => c_Assignments_Rec.default_rule_id,
3919 p_modify_flag => 'Y',
3920 p_worksheet_id => null);
3921
3922 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
3923 raise FND_API.G_EXC_ERROR;
3924 end if;
3925
3926 end loop;
3927
3928 /* for bug 4644241 --> Make sure that the distribution is
3929 100 % and does not go beyond that */
3930 l_distr_exists := TRUE;
3931
3932 IF l_worksheet_id IS NULL THEN
3933 FOR l_pos_distr_rec IN ( SELECT 1
3934 FROM dual
3935 WHERE NOT EXISTS ( SELECT 1
3936 FROM psb_position_pay_distributions
3937 WHERE position_id = p_position_id
3938 AND data_extract_id = p_data_extract_id
3939 AND worksheet_id IS NULL))
3940 LOOP
3941 l_distr_exists := FALSE;
3942 END LOOP;
3943 ELSE
3944 FOR l_pos_distr_rec IN ( SELECT 1
3945 FROM dual
3946 WHERE NOT EXISTS ( SELECT 1
3947 FROM psb_position_pay_distributions
3948 WHERE position_id = p_position_id
3949 AND data_extract_id = p_data_extract_id
3950 AND worksheet_id = l_worksheet_id))
3951 LOOP
3952 l_distr_exists := FALSE;
3953 END LOOP;
3954 END IF;
3955
3956 for c_Dist_Rec in c_Dist loop
3957
3958 IF NOT l_distr_exists THEN
3959
3960 PSB_POSITION_PAY_DISTR_PVT.Modify_Distribution_WS
3961 (p_api_version => 1.0,
3962 p_return_status => l_return_status,
3963 p_msg_count => l_msg_count,
3964 p_msg_data => l_msg_data,
3965 p_worksheet_id => l_worksheet_id,
3966 p_distribution_id => l_distribution_id,
3967 p_position_id => p_position_id,
3968 p_data_extract_id => p_data_extract_id,
3969 p_effective_start_date => p_position_start_date,
3970 p_effective_end_date => p_position_end_date,
3971 p_chart_of_accounts_id => c_Dist_Rec.chart_of_accounts_id,
3972 p_code_combination_id => c_Dist_Rec.code_combination_id,
3973 p_distribution_percent => c_Dist_Rec.distribution_percent,
3974 p_global_default_flag => 'Y',
3975 p_distribution_default_rule_id => c_Dist_Rec.default_rule_id,
3976 p_rowid => l_rowid);
3977
3978 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
3979 raise FND_API.G_EXC_ERROR;
3980 end if;
3981
3982 END IF;
3983
3984
3985 end loop;
3986
3987
3988 -- Initialize API return status to success
3989
3990 p_return_status := FND_API.G_RET_STS_SUCCESS;
3991
3992
3993 EXCEPTION
3994
3995 when FND_API.G_EXC_ERROR then
3996 p_return_status := FND_API.G_RET_STS_ERROR;
3997
3998 when FND_API.G_EXC_UNEXPECTED_ERROR then
3999 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4000
4001 when OTHERS then
4002 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4003
4004 END Apply_Global_Default;
4005
4006 /*-----------------------------------------------------------------*/
4007
4008
4009 PROCEDURE Initialize_View ( p_worksheet_id in number,
4010 p_start_date in date,
4011 p_end_date in date,
4012 p_select_date in date := fnd_api.g_miss_date
4013 ) IS
4014
4015 BEGIN
4016
4017 g_Worksheet_ID := p_Worksheet_ID;
4018 g_Start_Date := p_start_date;
4019 g_End_Date := p_end_date;
4020
4021 if p_select_date <> fnd_api.g_miss_date then
4022 g_Select_Date := p_Select_date ;
4023 else
4024 g_Select_Date := Null ;
4025 end if;
4026
4027 if (p_worksheet_id IS NULL) then
4028 g_worksheet_flag := 'N' ;
4029 else
4030 g_worksheet_flag := 'Y' ;
4031 end if ;
4032
4033 END Initialize_View;
4034
4035 /*-----------------------------------------------------------------*/
4036
4037 PROCEDURE Define_Worksheet_Values (
4038 p_api_version in number,
4039 p_init_msg_list in varchar2 := fnd_api.g_false,
4040 p_commit in varchar2 := fnd_api.g_false,
4041 p_validation_level in number := fnd_api.g_valid_level_full,
4042 p_return_status OUT NOCOPY varchar2,
4043 p_msg_count OUT NOCOPY number,
4044 p_msg_data OUT NOCOPY varchar2,
4045 p_worksheet_id in number,
4046 p_position_id in number,
4047 p_pos_effective_start_date in date := FND_API.G_MISS_DATE,
4048 p_pos_effective_end_date in date := FND_API.G_MISS_DATE,
4049 p_budget_source in varchar2 := FND_API.G_MISS_CHAR,
4050 p_out_worksheet_id OUT NOCOPY number,
4051 p_out_start_date OUT NOCOPY date,
4052 p_out_end_date OUT NOCOPY date) IS
4053
4054 l_api_name CONSTANT VARCHAR2(30) := 'Define_Worksheet_Values';
4055 l_worksheet_id NUMBER ;
4056 l_global_worksheet_id NUMBER ;
4057 l_local_copy_flag VARCHAR2(1) ;
4058 l_budget_calendar_id NUMBER ;
4059 l_cal_start_date DATE ;
4060 l_cal_end_date DATE ;
4061 l_pos_effective_start_date DATE ;
4062 l_pos_effective_end_date DATE ;
4063 l_out_start_date DATE ;
4064 l_out_end_date DATE ;
4065 l_out_worksheet_id NUMBER ;
4066 l_return_status VARCHAR2(1);
4067 --
4068 cursor position_csr IS
4069 SELECT effective_start_date ,
4070 effective_end_date
4071 FROM psb_positions
4072 WHERE position_id = p_position_id ;
4073 cursor worksheet_csr IS
4074 SELECT worksheet_id,local_copy_flag,global_worksheet_id,
4075 budget_calendar_id
4076 FROM psb_worksheets
4077 WHERE worksheet_id = p_worksheet_id ;
4078 cursor calendar_csr IS
4079 SELECT min(start_date) , max(end_date)
4080 FROM psb_worksheets w,psb_budget_periods b
4081 WHERE b.budget_calendar_id = w.budget_calendar_id AND
4082 w.worksheet_id = l_out_worksheet_id AND
4083 budget_period_type = 'Y';
4084 cursor rev_csr IS
4085 SELECT decode(global_budget_revision,'Y',budget_revision_id,global_budget_revision_id)
4086 FROM psb_budget_revisions
4087 WHERE budget_revision_id = p_worksheet_id;
4088
4089
4090 BEGIN
4091
4092 --
4093 if FND_API.to_Boolean (p_init_msg_list) then
4094 FND_MSG_PUB.initialize;
4095 end if;
4096
4097 -- STEP 1
4098 -- determine the worksheet id
4099 -- distributed worksheets should use global WS id
4100 -- local copy should be its WS id
4101 -- for revision, use revision id and start/end date
4102
4103 if p_worksheet_id IS NULL THEN
4104 -- this is for base assignment
4105 l_out_worksheet_id := p_worksheet_id ;
4106 else
4107
4108 if nvl(p_budget_source,'BP') = 'BR' THEN
4109 -- revision
4110 OPEN rev_csr ;
4111 FETCH rev_csr INTO l_out_worksheet_id;
4112
4113 -- revision do not need date values so they may be null.. use position
4114 -- revision now has same structure as worksheet
4115
4116 if (rev_csr%NOTFOUND) THEN
4117 FND_MESSAGE.SET_NAME('PSB', 'PSB_REVISION_NOT_FOUND') ;
4118 FND_MSG_PUB.Add ;
4119 raise FND_API.G_EXC_ERROR ;
4120 end if;
4121 CLOSE rev_csr ;
4122
4123 else
4124
4125 -- worksheet processing
4126
4127 OPEN worksheet_csr ;
4128 FETCH worksheet_csr INTO l_worksheet_id ,
4129 l_local_copy_flag ,
4130 l_global_worksheet_id ,
4131 l_budget_calendar_id ;
4132 if (worksheet_csr%NOTFOUND) THEN
4133 FND_MESSAGE.SET_NAME('PSB', 'PSB_WORKSHEET_NOT_FOUND') ;
4134 FND_MSG_PUB.Add ;
4135 raise FND_API.G_EXC_ERROR ;
4136 end if;
4137 CLOSE worksheet_csr ;
4138
4139 if (l_local_copy_flag = 'Y') THEN
4140 l_out_worksheet_id := l_worksheet_id ;
4141 else
4142 if (l_global_worksheet_id IS NOT NULL) THEN
4143 l_out_worksheet_id := l_global_worksheet_id ;
4144 -- if not global worksheet,use global worksheet id
4145 else
4146 l_out_worksheet_id := l_worksheet_id ;
4147 -- if global worksheet, global worksheet id is null so use
4148 -- the input worksheet id
4149 end if;
4150 end if ;
4151
4152 -- get calendar min/max to compare with position's eff start/end date
4153
4154 OPEN calendar_csr ;
4155 FETCH calendar_csr INTO l_cal_start_date,
4156 l_cal_end_date ;
4157 if (calendar_csr%NOTFOUND) THEN
4158 FND_MESSAGE.SET_NAME('PSB', 'PSB_CALENDAR_NOT_FOUND') ;
4159 FND_MSG_PUB.Add ;
4160 raise FND_API.G_EXC_ERROR ;
4161 end if;
4162 --
4163 CLOSE calendar_csr ;
4164
4165 end if; -- end of ws vs rev
4166
4167 end if ;
4168
4169 -- STEP 2
4170 -- get position start/end date if not available, calling prg will
4171 -- just input position id
4172
4173 l_pos_effective_start_date := p_pos_effective_start_date;
4174 l_pos_effective_end_date := p_pos_effective_end_date;
4175 -- is the default
4176
4177 if (p_position_id IS NOT NULL) then
4178 -- get pos effec date from table
4179 OPEN position_csr ;
4180 FETCH position_csr INTO l_pos_effective_start_date,
4181 l_pos_effective_end_date ;
4182 if (position_csr%NOTFOUND) then
4183 FND_MESSAGE.SET_NAME('PSB', 'PSB_POSITION_NOT_FOUND') ;
4184 FND_MSG_PUB.Add ;
4185 raise FND_API.G_EXC_ERROR ;
4186 end if;
4187 CLOSE position_csr ;
4188 end if;
4189
4190 --
4191 -- supersede value of out_dates with input start and end dates
4192 -- if position id is not null to use the input values in case
4193 -- users changes the dates without saving them.
4194 -- If position id is null such as when creating new positions
4195 -- from forms and position has not been saved yet, use input dates
4196 --
4197 if (p_pos_effective_start_date <> FND_API.G_MISS_DATE ) then
4198
4199 l_out_start_date := p_pos_effective_start_date ;
4200 l_out_end_date := p_pos_effective_end_date ;
4201 --
4202 end if;
4203
4204 -- STEP 3.
4205 -- next determine what date to use
4206 -- always use position start date since this is more constricting
4207 -- than calendar date; this will allow them also to assign with s
4208 -- start date before the calendar start date which is true of
4209 -- a base assignment
4210 -- if from maintain position or budget revision, always use position date
4211
4212 if (p_worksheet_id IS NULL) then
4213 l_out_start_date := p_pos_effective_start_date ;
4214 l_out_end_date := p_pos_effective_end_date ;
4215 -- from maintain positios
4216 elsif nvl(p_budget_source,'BP') = 'BR' THEN
4217 l_out_start_date := l_pos_effective_start_date ;
4218 l_out_end_date := p_pos_effective_end_date ;
4219 -- for bg rev, use position's start date and passed end date
4220 -- end date needed b/c assignments_v will not work if it is null
4221 -- BR should pass an end date
4222 else
4223
4224 l_out_start_date := l_pos_effective_start_date ;
4225
4226 if (l_pos_effective_end_date IS NULL) then
4227 l_out_end_date := l_cal_end_date;
4228 else
4229 -- use earliest end date
4230 if (l_cal_end_date <= l_pos_effective_end_date) then
4231 l_out_end_date := l_cal_end_date;
4232 else
4233 l_out_end_date := l_pos_effective_end_date ;
4234 end if;
4235
4236 end if;
4237
4238 end if;
4239
4240 --
4241 -- move to OUT NOCOPY parameters
4242 p_out_worksheet_id := l_out_worksheet_id ;
4243 p_out_start_date := l_out_start_date ;
4244 p_out_end_date := l_out_end_date ;
4245
4246 p_return_status := FND_API.G_RET_STS_SUCCESS ;
4247
4248 -- Standard call to get message count and if count is 1, get message info.
4249
4250 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
4251 p_data => p_msg_data);
4252 --
4253
4254 EXCEPTION
4255 --
4256 when FND_API.G_EXC_ERROR then
4257 --
4258 p_return_status := FND_API.G_RET_STS_ERROR;
4259 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
4260 p_data => p_msg_data);
4261 --
4262 when FND_API.G_EXC_UNEXPECTED_ERROR then
4263 --
4264 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4265 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
4266 p_data => p_msg_data);
4267 --
4268 when OTHERS then
4269 --
4270 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4271 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) then
4272 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
4273 l_api_name);
4274 end if;
4275 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
4276 p_data => p_msg_data);
4277 --
4278 END Define_Worksheet_Values ;
4279
4280 /* -------------------------------------------------------------------- */
4281 --
4282 -- Validate_Salary validates that there is only one salary for a date.
4283 -- This is called from the application form of any of the positions form
4284 -- and from PSBWPI2B.pls
4285 -- The cursor was modified by expanding the where clause on worksheet_id
4286 -- (i) so that base salary with no WS salary are also selected by the cursor;
4287 -- this fixes a bug where entering a WS specific salary that overlapped
4288 -- the base did not give an error from the form
4289 --
4290 -- (ii) and conversely, excludes those base for which there exists a WS specific
4291 -- salary of any salary element;
4292 --
4293 --
4294 PROCEDURE Validate_Salary
4295 ( p_api_version IN NUMBER,
4296 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
4297 p_commit IN VARCHAR2 := FND_API.G_FALSE,
4298 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
4299 p_return_status OUT NOCOPY VARCHAR2,
4300 p_msg_count OUT NOCOPY NUMBER,
4301 p_msg_data OUT NOCOPY VARCHAR2,
4302 p_worksheet_id IN NUMBER,
4303 p_position_id IN NUMBER,
4304 p_effective_start_date IN DATE,
4305 p_effective_end_date IN DATE,
4306 p_pay_element_id IN NUMBER,
4307 p_data_extract_id IN NUMBER,
4308 p_rowid IN VARCHAR2
4309 ) IS
4310
4311 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Salary';
4312 l_api_version CONSTANT NUMBER := 1.0;
4313
4314 cursor c_Overlap is
4315 select 'Salary Overlaps'
4316 from PSB_POSITION_ASSIGNMENTS a,
4317 PSB_PAY_ELEMENTS b
4318 where ((((p_effective_end_date is not null)
4319 and ((a.effective_start_date <= p_effective_end_date)
4320 and (a.effective_end_date is null))
4321 or ((a.effective_start_date between p_effective_start_date and p_effective_end_date)
4322 or (a.effective_end_date between p_effective_start_date and p_effective_end_date)
4323 or ((a.effective_start_date < p_effective_start_date)
4324 and (a.effective_end_date > p_effective_end_date)))))
4325 or ((p_effective_end_date is null)
4326 and (nvl(a.effective_end_date, p_effective_start_date) >= p_effective_start_date)))
4327 and ( (nvl(a.worksheet_id, FND_API.G_MISS_NUM) = nvl(p_worksheet_id, FND_API.G_MISS_NUM)) OR
4328 (p_worksheet_id is not null and worksheet_id is null
4329 and not exists
4330 (select 1 from
4331 psb_position_assignments c ,psb_pay_elements pe2
4332 where c.position_id = a.position_id
4333 and c.pay_element_id = pe2.pay_element_id
4334 and pe2.salary_flag = 'Y'
4335 and c.worksheet_id = p_worksheet_id
4336 and ( (
4337 nvl(c.effective_start_date,PSB_POSITIONS_PVT.GET_end_date+1) between
4338 nvl(a.effective_start_date,PSB_POSITIONS_PVT.GET_end_date) and
4339 nvl(a.effective_end_date,nvl(PSB_POSITIONS_PVT.GET_end_date,
4340 c.effective_start_date ))) or (
4341 nvl(a.effective_start_date,PSB_POSITIONS_PVT.GET_end_date+1) between
4342 nvl(c.effective_start_date,PSB_POSITIONS_PVT.GET_end_date) and
4343 nvl(c.effective_end_date,nvl(PSB_POSITIONS_PVT.GET_end_date,
4344 a.effective_start_date ))) )
4345 )
4346 )
4347 )
4348 and a.pay_element_id = b.pay_element_id
4349 and a.position_id = p_position_id
4350 and b.pay_element_id <> p_pay_element_id
4351 and b.salary_flag = 'Y'
4352 and b.data_extract_id = p_data_extract_id;
4353
4354
4355 l_return_status VARCHAR2(1);
4356 l_salary_overlaps VARCHAR2(1) := FND_API.G_FALSE;
4357
4358 BEGIN
4359
4360 -- Standard call to check for call compatibility.
4361
4362 if not FND_API.Compatible_API_Call (l_api_version,
4363 p_api_version,
4364 l_api_name,
4365 G_PKG_NAME)
4366 then
4367 raise FND_API.G_EXC_UNEXPECTED_ERROR;
4368 end if;
4369
4370 if FND_API.to_Boolean (p_init_msg_list) then
4371 FND_MSG_PUB.initialize;
4372 end if;
4373
4374 for c_Overlap_Rec in c_Overlap loop
4375 l_salary_overlaps := FND_API.G_TRUE;
4376 end loop;
4377
4378 if FND_API.to_Boolean(l_salary_overlaps) then
4379 begin
4380
4381 if p_rowid is not null then
4382 FND_MESSAGE.SET_NAME('PSB', 'PSB_MULTIPLE_SALARY_IN_PERIOD');
4383 FND_MSG_PUB.Add;
4384 end if;
4385
4386 raise FND_API.G_EXC_ERROR;
4387
4388 end;
4389 end if;
4390
4391 p_return_status := FND_API.G_RET_STS_SUCCESS;
4392
4393
4394 -- Standard call to get message count and if count is 1, get message info.
4395
4396 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
4397 p_data => p_msg_data);
4398
4399
4400 EXCEPTION
4401
4402 when FND_API.G_EXC_ERROR then
4403 p_return_status := FND_API.G_RET_STS_ERROR;
4404
4405 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
4406 p_data => p_msg_data);
4407
4408 when FND_API.G_EXC_UNEXPECTED_ERROR then
4409 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4410
4411 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
4412 p_data => p_msg_data);
4413
4414 when OTHERS then
4415 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4416
4417 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) then
4418 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
4419 l_api_name);
4420 end if;
4421
4422 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
4423 p_data => p_msg_data);
4424
4425 END Validate_Salary;
4426
4427 /* -------------------------------------------------------------------- */
4428
4429 -- Calling program should check p_return_status
4430 -- if successful, check p_validation_status of either 'S'-uccessful or 'E'-rror
4431 --
4432 -- p_worksheet_id is the global worksheet id
4433 --
4434
4435
4436 PROCEDURE Position_WS_Validation
4437 ( p_api_version in number,
4438 p_init_msg_list in varchar2 := fnd_api.g_false,
4439 p_commit in varchar2 := fnd_api.g_false,
4440 p_validation_level in number := fnd_api.g_valid_level_full,
4441 p_return_status OUT NOCOPY varchar2,
4442 p_msg_count OUT NOCOPY number,
4443 p_msg_data OUT NOCOPY varchar2,
4444 p_worksheet_id in number,
4445 p_validation_status OUT NOCOPY varchar2,
4446 p_validation_mode IN VARCHAR2
4447 ) IS
4448
4449 l_api_name CONSTANT VARCHAR2(30) := 'Position_WS_Validation';
4450 l_api_version CONSTANT NUMBER := 1.0;
4451 l_return_status VARCHAR2(1);
4452 l_msg_count NUMBER;
4453 l_msg_data VARCHAR2(2000);
4454
4455 l_data_extract_id NUMBER;
4456 l_budget_calendar_id NUMBER;
4457 l_budget_group_id NUMBER;
4458 l_root_budget_group_id NUMBER;
4459 l_chart_of_accounts_id NUMBER;
4460 l_set_of_books_id NUMBER;
4461 l_error_flag VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
4462
4463 CURSOR c_ws IS
4464 SELECT data_extract_id,
4465 budget_calendar_id,
4466 budget_group_id
4467 FROM psb_worksheets
4468 WHERE worksheet_id = p_worksheet_id;
4469
4470 CURSOR c_bg IS
4471 SELECT nvl(root_budget_group_id,budget_group_id) ,
4472 nvl(root_chart_of_accounts_id,chart_of_accounts_id),
4473 nvl(root_set_of_books_id,set_of_books_id)
4474 FROM psb_budget_groups_v
4475 WHERE budget_group_id = l_budget_group_id;
4476
4477 BEGIN
4478 -- Standard call to check for call compatibility.
4479
4480 g_validation_mode := p_validation_mode;
4481
4482 if not FND_API.Compatible_API_Call (l_api_version,
4483 p_api_version,
4484 l_api_name,
4485 G_PKG_NAME)
4486 then
4487 raise FND_API.G_EXC_UNEXPECTED_ERROR;
4488 end if;
4489
4490 if FND_API.to_Boolean (p_init_msg_list) then
4491 FND_MSG_PUB.initialize;
4492 end if;
4493
4494 OPEN c_ws;
4495 FETCH c_ws INTO
4496 l_data_extract_id,
4497 l_budget_calendar_id,
4498 l_budget_group_id;
4499 CLOSE c_ws;
4500
4501 OPEN c_bg;
4502 FETCH c_bg INTO
4503 l_root_budget_group_id,
4504 l_chart_of_accounts_id,
4505 l_set_of_books_id;
4506 CLOSE c_bg;
4507
4508
4509 PSB_WS_ACCT1.Flex_Info
4510 (p_flex_code => l_chart_of_accounts_id,
4511 p_return_status => l_return_status);
4512
4513 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4514 raise FND_API.G_EXC_ERROR;
4515 END IF;
4516
4517
4518 -- get calendar start/end dates
4519 if l_budget_calendar_id <> nvl(PSB_WS_ACCT1.g_budget_calendar_id, FND_API.G_MISS_NUM) then
4520 begin
4521
4522 PSB_WS_ACCT1.Cache_Budget_Calendar(p_return_status => p_return_status,
4523 p_budget_calendar_id => l_budget_calendar_id);
4524
4525 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4526 RAISE FND_API.G_EXC_ERROR ;
4527 END IF ;
4528
4529 end;
4530 end if;
4531
4532 --++++++++
4533
4534
4535 FOR l_positions_rec IN
4536 (SELECT pos.position_id,
4537 pos.effective_start_date,
4538 pos.effective_end_date,
4539 pos.name ,
4540 emp.employee_number
4541 FROM psb_positions pos,
4542 psb_employees emp
4543 WHERE pos.data_extract_id = l_data_extract_id
4544 AND pos.hr_employee_id = emp.hr_employee_id(+)
4545 AND emp.data_extract_id(+) = l_data_extract_id
4546 ORDER BY name
4547 )
4548
4549 LOOP
4550
4551
4552 VALIDATE_POSITION ( p_worksheet_id => p_worksheet_id,
4553 p_position_id => l_positions_rec.position_id,
4554 p_name => l_positions_rec.name,
4555 p_employee_number => l_positions_rec.employee_number,
4556 p_data_extract_id => l_data_extract_id,
4557 p_root_budget_group_id => l_root_budget_group_id,
4558 p_set_of_books_id => l_set_of_books_id,
4559 p_budget_calendar_id => l_budget_calendar_id,
4560 p_chart_of_accounts_id => l_chart_of_accounts_id,
4561 p_position_start_date => l_positions_rec.effective_start_date,
4562 p_position_end_date => l_positions_rec.effective_end_date,
4563 p_startdate_pp => PSB_WS_ACCT1.g_startdate_pp,
4564 p_enddate_cy => PSB_WS_ACCT1.g_enddate_cy,
4565 p_effective_start_date => PSB_WS_ACCT1.g_startdate_cy,
4566 p_effective_end_date => PSB_WS_ACCT1.g_end_est_date,
4567 p_error_flag => l_error_flag,
4568 p_return_status => l_return_status);
4569
4570
4571
4572 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4573 RAISE FND_API.G_EXC_ERROR ;
4574 END IF ;
4575
4576 END LOOP;
4577 --
4578
4579 IF p_validation_mode = 'STANDALONE' THEN
4580
4581 IF NVL(l_error_flag,FND_API.G_RET_STS_SUCCESS) =
4582 FND_API.G_RET_STS_SUCCESS THEN
4583
4584 FND_MESSAGE.SET_NAME('PSB', 'PSB_VAL_LINE');
4585 FND_MSG_PUB.ADD;
4586 FND_MESSAGE.SET_NAME('PSB', 'PSB_NO_WS_VALID_ERR');
4587 FND_MSG_PUB.ADD;
4588 FND_MESSAGE.SET_NAME('PSB', 'PSB_VAL_LINE');
4589 FND_MSG_PUB.ADD;
4590 END IF;
4591
4592 END IF;
4593
4594 Output_Message_To_Table(p_worksheet_id,
4595 p_return_status);
4596
4597 p_validation_status := l_error_flag ;
4598 p_return_status := FND_API.G_RET_STS_SUCCESS;
4599
4600 -- Standard check of p_commit.
4601
4602 if FND_API.to_Boolean (p_commit) then
4603 commit work;
4604 end if;
4605
4606 -- Standard call to get message count and if count is 1, get message info.
4607
4608 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
4609 p_data => p_msg_data);
4610
4611 EXCEPTION
4612
4613 when FND_API.G_EXC_ERROR then
4614 p_return_status := FND_API.G_RET_STS_ERROR;
4615
4616 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
4617 p_data => p_msg_data);
4618
4619 when FND_API.G_EXC_UNEXPECTED_ERROR then
4620 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4621
4622 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
4623 p_data => p_msg_data);
4624
4625 when OTHERS then
4626 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4627
4628 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) then
4629 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
4630 l_api_name);
4631 end if;
4632
4633 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
4634 p_data => p_msg_data);
4635
4636 END Position_WS_Validation;
4637
4638 /* -------------------------------------------------------------------- */
4639
4640 PROCEDURE VALIDATE_POSITION
4641 ( p_worksheet_id IN NUMBER,
4642 p_position_id IN NUMBER,
4643 p_name IN VARCHAR2,
4644 p_employee_number IN VARCHAR2,
4645 p_data_extract_id IN NUMBER,
4646 p_root_budget_group_id IN NUMBER,
4647 p_set_of_books_id IN NUMBER,
4648 p_budget_calendar_id IN NUMBER,
4649 p_chart_of_accounts_id IN NUMBER,
4650 p_position_start_date IN DATE,
4651 p_position_end_date IN DATE,
4652 p_startdate_pp IN DATE,
4653 p_enddate_cy IN DATE,
4654 p_effective_start_date IN DATE,
4655 p_effective_end_date IN DATE,
4656 p_error_flag IN OUT NOCOPY VARCHAR2,
4657 p_return_status OUT NOCOPY VARCHAR2
4658 ) IS
4659
4660 l_api_name CONSTANT VARCHAR2(30) := 'VALIDATE_POSITION';
4661
4662 l_job_exists VARCHAR2(1) := FND_API.G_FALSE;
4663 l_salary_exists VARCHAR2(1) := FND_API.G_FALSE;
4664 l_pay_basis_invalid VARCHAR2(1) := FND_API.G_FALSE;
4665 l_hourly_salary_exists VARCHAR2(1) := FND_API.G_FALSE;
4666 l_weekly_hours_exists VARCHAR2(1) := FND_API.G_TRUE;
4667 l_salary_distr_exists VARCHAR2(1) := FND_API.G_FALSE;
4668 l_calc_exists VARCHAR2(1) := FND_API.G_FALSE;
4669 l_return_status VARCHAR2(1);
4670 l_position_flag VARCHAR2(1) := NULL;
4671 l_salary_start_date DATE;
4672 l_salary_end_date DATE;
4673 l_data_extract_id NUMBER;
4674 l_budget_calendar_id NUMBER;
4675 l_budget_group_id NUMBER;
4676 l_chart_of_accounts_id NUMBER;
4677
4678
4679 CURSOR c_ws IS
4680 SELECT data_extract_id,
4681 budget_calendar_id,
4682 budget_group_id
4683 FROM psb_worksheets
4684 WHERE worksheet_id = p_worksheet_id;
4685
4686 CURSOR c_job IS
4687 SELECT 'Job Exists'
4688 FROM dual
4689 WHERE exists
4690 (SELECT 1
4691 FROM psb_attribute_values patv,
4692 psb_position_assignments pass,
4693 psb_attributes pat
4694 WHERE patv.attribute_value_id = pass.attribute_value_id
4695 AND (pass.worksheet_id is NULL OR pass.worksheet_id = p_worksheet_id)
4696 AND pass.attribute_id = pat.attribute_id
4697 AND pass.position_id = p_position_id
4698 AND pat.system_attribute_type = 'JOB_CLASS');
4699 --++ just check that a job exist regardless of date. WS creation does not
4700 --++ use the job's date
4701
4702 CURSOR c_salary IS
4703 SELECT pass.effective_start_date,pass.effective_end_date,pass.pay_basis
4704 FROM psb_pay_elements pe,
4705 psb_position_assignments pass
4706 WHERE pe.salary_flag = 'Y'
4707 AND pe.pay_element_id = pass.pay_element_id
4708 AND (pass.worksheet_id is NULL OR pass.worksheet_id = p_worksheet_id)
4709 AND (((pass.effective_start_date <= p_effective_end_date)
4710 and (pass.effective_end_date is null))
4711 or ((pass.effective_start_date between p_effective_start_date and p_effective_end_date)
4712 or (pass.effective_end_date between p_effective_start_date and p_effective_end_date)
4713 or ((pass.effective_start_date < p_effective_start_date)
4714 and (pass.effective_end_date > p_effective_end_date))))
4715 AND pass.position_id = p_position_id ;
4716 --++ salary cursor
4717
4718 CURSOR c_pay_basis IS
4719 SELECT 'Invalid Pay Basis'
4720 FROM DUAL
4721 WHERE EXISTS
4722 (SELECT 1
4723 FROM psb_pay_elements pe,
4724 psb_position_assignments pass
4725 WHERE NVL(pass.pay_basis,'DUMMY') NOT IN ('ANNUAL', 'HOURLY', 'MONTHLY', 'PERIOD')
4726 AND pe.salary_flag = 'Y'
4727 AND pe.pay_element_id = pass.pay_element_id
4728 AND (pass.worksheet_id is NULL OR pass.worksheet_id = p_worksheet_id)
4729 AND (((pass.effective_start_date <= p_effective_end_date)
4730 AND (pass.effective_end_date is null))
4731 OR ((pass.effective_start_date between p_effective_start_date and p_effective_end_date)
4732 OR (pass.effective_end_date between p_effective_start_date and p_effective_end_date)
4733 OR ((pass.effective_start_date < p_effective_start_date)
4734 AND (pass.effective_end_date > p_effective_end_date))))
4735 AND pass.position_id = p_position_id);
4736
4737 /* Bug No 1920021 Start */
4738 /* --- Commented the following 11 Lines ---
4739 CURSOR c_Calc_Periods IS
4740 SELECt bp.start_date,
4741 bp.end_date
4742 FROM psb_budget_periods bp
4743 WHERE bp.budget_period_type = 'C'
4744 AND bp.budget_calendar_id = p_budget_calendar_id
4745 ORDER by bp.start_date;
4746 -- get calculation period (proposed year) for the calendar
4747 -- to be used in validation of default wkly hours
4748
4749 CURSOR c_weekly_hours (calc_start_date DATE, calc_end_date DATE) IS
4750 --- */
4751
4752 CURSOR c_weekly_hours IS
4753 SELECT 'Default Weekly Hours Exists'
4754 FROM dual
4755 WHERE exists
4756 (SELECT 1
4757 FROM psb_attributes pat,
4758 psb_position_assignments pass
4759 WHERE pat.attribute_id = pass.attribute_id
4760 AND pat.system_attribute_type = 'DEFAULT_WEEKLY_HOURS'
4761 AND (pass.worksheet_id is NULL OR pass.worksheet_id = p_worksheet_id)
4762 AND pass.position_id = p_position_id);
4763
4764 /* --- Commented the following 8 Lines ---
4765 AND (((pass.effective_start_date <= calc_end_date)
4766 and (pass.effective_end_date is null))
4767 or ((pass.effective_start_date between calc_start_date and calc_end_date)
4768 or (pass.effective_end_date between calc_start_date and calc_end_date)
4769 or ((pass.effective_start_date < calc_start_date)
4770 and (pass.effective_end_date > calc_end_date))))
4771 AND pass.position_id = p_position_id);
4772 --++ that wkly hours exists within c_calc_periods (calculation pd)
4773 --- */
4774
4775 /* Bug No 1920021 End */
4776
4777
4778
4779 l_calc_start_date DATE;
4780 l_calc_end_date DATE;
4781 l_end_est_date DATE;
4782 l_startdate_cy DATE;
4783 l_ccid_val FND_FLEX_EXT.SegmentArray;
4784 l_seg_val FND_FLEX_EXT.SegmentArray;
4785 l_ccid NUMBER;
4786 l_ccid_overwritten NUMBER;
4787 l_flex_delimiter VARCHAR2(1);
4788 l_concat_segments VARCHAR2(2000);
4789 l_last_index NUMBER;
4790 l_dynamic_insert_flag VARCHAR2(1) := 'N';
4791 l_firstpp BOOLEAN := TRUE;
4792
4793 BEGIN
4794
4795 OPEN c_ws;
4796 FETCH c_ws INTO
4797 l_data_extract_id,
4798 l_budget_calendar_id,
4799 l_budget_group_id;
4800 CLOSE c_ws;
4801
4802 /* Bug 3247574 start.
4803 Changes done for Worksheet Exception Report */
4804 FOR c_budyr_rec IN(
4805 SELECT a.budget_period_id,
4806 a.budget_year_type_id,
4807 b.year_category_type,
4808 period_distribution_type,
4809 calculation_period_type,
4810 a.name,
4811 a.start_date,
4812 a.end_date
4813 FROM psb_budget_year_types b,
4814 psb_budget_periods a
4815 WHERE b.budget_year_type_id = a.budget_year_type_id
4816 AND a.budget_period_type = 'Y'
4817 AND a.budget_calendar_id = P_budget_calendar_id
4818 ORDER BY a.start_date)
4819 LOOP
4820
4821
4822 IF c_budyr_rec.year_category_type = 'PP' THEN
4823
4824 IF l_firstpp THEN
4825 l_firstpp := FALSE;
4826 l_end_est_date := c_BudYr_Rec.End_Date;
4827 END IF;
4828
4829
4830 IF c_budyr_rec.end_date > l_end_est_date THEN
4831 l_end_est_date := c_BudYr_Rec.end_date;
4832 END IF;
4833
4834 END IF;
4835
4836 IF c_budyr_rec.year_category_type = 'CY' THEN
4837 l_startdate_cy := c_budyr_rec.Start_Date;
4838 END IF;
4839
4840 END LOOP;
4841
4842
4843 FOR c_dist_ws_rec IN(
4844 SELECT code_combination_id,
4845 distribution_percent,
4846 effective_start_date,
4847 effective_end_date
4848 FROM psb_position_pay_distributions a
4849 WHERE code_combination_id is not null
4850 AND chart_of_accounts_id = p_chart_of_accounts_id
4851 AND (worksheet_id is null
4852 AND NOT EXISTS
4853 (SELECT 1
4854 FROM psb_position_pay_distributions c
4855 WHERE (
4856 ( NVL(c.effective_start_date, l_end_est_date + 1)
4857 BETWEEN NVL(a.effective_start_date, l_end_est_date)
4858 AND NVL(a.effective_end_date, NVL(l_end_est_date, c.effective_start_date)))
4859 OR ( NVL(a.effective_start_date, l_end_est_date + 1)
4860 BETWEEN NVL(c.effective_start_date, l_end_est_date)
4861 AND NVL(c.effective_end_date, NVL(l_end_est_date, a.effective_start_date)))
4862 )
4863 AND c.position_id = a.position_id
4864 AND c.chart_of_accounts_id = p_chart_of_accounts_id
4865 AND c.code_combination_id is null
4866 AND c.worksheet_id = p_worksheet_id
4867 ))
4868 AND position_id = p_position_id
4869 ORDER BY distribution_percent desc)
4870 LOOP
4871
4872 l_ccid := c_dist_ws_rec.code_combination_id;
4873
4874 END LOOP;
4875
4876
4877 --++++++++
4878 -- start of processing
4879 --++++++++
4880 --new validations
4881
4882
4883 /* The following code checks for non-existent account
4884 combinations in GL */
4885
4886 -- for getting segment count
4887
4888 FOR c_seg_count_rec IN(SELECT COUNT(segment_num) segment_count
4889 FROM fnd_id_flex_segments
4890 WHERE id_flex_code = 'GL#'
4891 AND ID_FLEX_NUM = p_chart_of_accounts_id
4892 AND ENABLED_FLAG = 'Y')
4893
4894 LOOP
4895 l_last_index := c_seg_count_rec.segment_count;
4896 END LOOP;
4897
4898 IF l_ccid is NOT NULL THEN
4899
4900 FOR c_dist_rec in(SELECT DISTINCT a.code_combination_id, a.segment1, a.segment2,
4901 a.segment3, a.segment4,
4902 a.segment5, a.segment6, a.segment7, a.segment8,
4903 a.segment9, a.segment10, a.segment11, a.segment12,
4904 a.segment13, a.segment14, a.segment15, a.segment16,
4905 a.segment17, a.segment18, a.segment19, a.segment20,
4906 a.segment21, a.segment22, a.segment23, a.segment24,
4907 a.segment25, a.segment26, a.segment27, a.segment28,
4908 a.segment29, a.segment30,
4909 a.effective_start_date, a.effective_end_date,
4910 e.position_id
4911 FROM
4912 psb_pay_element_distributions a,
4913 psb_pay_elements b,
4914 psb_element_pos_Set_groups c,
4915 psb_set_relations d,
4916 psb_budget_positions e,
4917 psb_position_assignments f
4918 WHERE
4919 a.position_set_group_id = c.position_set_group_id
4920 AND b.pay_element_id = c.pay_element_id
4921 AND b.data_extract_id = l_data_extract_id
4922 AND b.data_extract_id = e.data_extract_id
4923 AND d.account_position_set_id = e.account_position_set_id
4924 AND c.position_set_group_id = d.position_set_group_id
4925 AND e.position_id = p_position_id
4926 AND e.position_id = f.position_id
4927 AND f.assignment_type = 'ELEMENT'
4928 AND f.pay_element_id = b.pay_element_id)
4929
4930 /* Bug 3692601 Start */
4931 -- AND a.code_combination_id IS NULL)
4932 /* Bug 3692601 End */
4933 LOOP
4934
4935 /* Bug 3692601 Start */
4936 IF c_dist_rec.code_combination_id IS NULL THEN
4937 /* Bug 3692601 End */
4938
4939 FOR l_init_index in 1..l_last_index
4940 LOOP
4941 l_seg_val(l_init_index) := NULL;
4942 l_ccid_val(l_init_index) := NULL;
4943 END LOOP;
4944
4945 IF NOT FND_FLEX_EXT.Get_Segments
4946 (application_short_name => 'SQLGL',
4947 key_flex_code => 'GL#',
4948 structure_number => p_chart_of_accounts_id,
4949 combination_id => l_ccid,
4950 n_segments => l_last_index,
4951 segments => l_ccid_val) THEN
4952
4953 FND_MSG_PUB.Add;
4954 raise FND_API.G_EXC_ERROR;
4955 END IF;
4956
4957 FOR l_index IN 1..l_last_index LOOP
4958
4959 IF ((PSB_WS_ACCT1.g_seg_name(l_index) = 'SEGMENT1') AND
4960 (c_Dist_Rec.segment1 IS NOT NULL)) THEN
4961 l_seg_val(l_index) := c_Dist_Rec.segment1;
4962
4963 ELSIF ((PSB_WS_ACCT1.g_seg_name(l_index) = 'SEGMENT2') AND
4964 (c_Dist_Rec.segment2 IS NOT NULL)) THEN
4965 l_seg_val(l_index) := c_Dist_Rec.segment2;
4966
4967 ELSIF ((PSB_WS_ACCT1.g_seg_name(l_index) = 'SEGMENT3') AND
4968 (c_Dist_Rec.segment3 IS NOT NULL)) THEN
4969 l_seg_val(l_index) := c_Dist_Rec.segment3;
4970
4971 ELSIF ((PSB_WS_ACCT1.g_seg_name(l_index) = 'SEGMENT4') AND
4972 (c_Dist_Rec.segment4 IS NOT NULL)) THEN
4973 l_seg_val(l_index) := c_Dist_Rec.segment4;
4974
4975 ELSIF ((PSB_WS_ACCT1.g_seg_name(l_index) = 'SEGMENT5') AND
4976 (c_Dist_Rec.segment5 IS NOT NULL)) THEN
4977 l_seg_val(l_index) := c_Dist_Rec.segment5;
4978
4979 ELSIF ((PSB_WS_ACCT1.g_seg_name(l_index) = 'SEGMENT6') AND
4980 (c_Dist_Rec.segment6 IS NOT NULL)) THEN
4981 l_seg_val(l_index) := c_Dist_Rec.segment6;
4982
4983 ELSIF ((PSB_WS_ACCT1.g_seg_name(l_index) = 'SEGMENT7') AND
4984 (c_Dist_Rec.segment7 IS NOT NULL)) THEN
4985 l_seg_val(l_index) := c_Dist_Rec.segment7;
4986
4987 ELSIF ((PSB_WS_ACCT1.g_seg_name(l_index) = 'SEGMENT8') AND
4988 (c_Dist_Rec.segment8 IS NOT NULL)) THEN
4989 l_seg_val(l_index) := c_Dist_Rec.segment8;
4990
4991 ELSIF ((PSB_WS_ACCT1.g_seg_name(l_index) = 'SEGMENT9') AND
4992 (c_Dist_Rec.segment9 IS NOT NULL)) THEN
4993 l_seg_val(l_index) := c_Dist_Rec.segment9;
4994
4995 ELSIF ((PSB_WS_ACCT1.g_seg_name(l_index) = 'SEGMENT10') AND
4996 (c_Dist_Rec.segment10 IS NOT NULL)) THEN
4997 l_seg_val(l_index) := c_Dist_Rec.segment10;
4998
4999 ELSIF ((PSB_WS_ACCT1.g_seg_name(l_index) = 'SEGMENT11') AND
5000 (c_Dist_Rec.segment11 IS NOT NULL)) THEN
5001 l_seg_val(l_index) := c_Dist_Rec.segment11;
5002
5003 ELSIF ((PSB_WS_ACCT1.g_seg_name(l_index) = 'SEGMENT12') AND
5004 (c_Dist_Rec.segment12 IS NOT NULL)) THEN
5005 l_seg_val(l_index) := c_Dist_Rec.segment12;
5006
5007 ELSIF ((PSB_WS_ACCT1.g_seg_name(l_index) = 'SEGMENT13') AND
5008 (c_Dist_Rec.segment13 IS NOT NULL)) THEN
5009 l_seg_val(l_index) := c_Dist_Rec.segment13;
5010
5011 ELSIF ((PSB_WS_ACCT1.g_seg_name(l_index) = 'SEGMENT14') AND
5012 (c_Dist_Rec.segment14 IS NOT NULL)) THEN
5013 l_seg_val(l_index) := c_Dist_Rec.segment14;
5014
5015 ELSIF ((PSB_WS_ACCT1.g_seg_name(l_index) = 'SEGMENT15') AND
5016 (c_Dist_Rec.segment15 IS NOT NULL)) THEN
5017 l_seg_val(l_index) := c_Dist_Rec.segment15;
5018
5019 ELSIF ((PSB_WS_ACCT1.g_seg_name(l_index) = 'SEGMENT16') AND
5020 (c_Dist_Rec.segment16 IS NOT NULL)) THEN
5021 l_seg_val(l_index) := c_Dist_Rec.segment16;
5022
5023 ELSIF ((PSB_WS_ACCT1.g_seg_name(l_index) = 'SEGMENT17') AND
5024 (c_Dist_Rec.segment17 IS NOT NULL)) THEN
5025 l_seg_val(l_index) := c_Dist_Rec.segment17;
5026
5027 ELSIF ((PSB_WS_ACCT1.g_seg_name(l_index) = 'SEGMENT18') AND
5028 (c_Dist_Rec.segment18 IS NOT NULL)) THEN
5029 l_seg_val(l_index) := c_Dist_Rec.segment18;
5030
5031 ELSIF ((PSB_WS_ACCT1.g_seg_name(l_index) = 'SEGMENT19') AND
5032 (c_Dist_Rec.segment19 IS NOT NULL)) THEN
5033 l_seg_val(l_index) := c_Dist_Rec.segment19;
5034
5035 ELSIF ((PSB_WS_ACCT1.g_seg_name(l_index) = 'SEGMENT20') AND
5036 (c_Dist_Rec.segment20 IS NOT NULL)) THEN
5037 l_seg_val(l_index) := c_Dist_Rec.segment20;
5038
5039 ELSIF ((PSB_WS_ACCT1.g_seg_name(l_index) = 'SEGMENT21') AND
5040 (c_Dist_Rec.segment21 IS NOT NULL)) THEN
5041 l_seg_val(l_index) := c_Dist_Rec.segment21;
5042
5043 ELSIF ((PSB_WS_ACCT1.g_seg_name(l_index) = 'SEGMENT22') AND
5044 (c_Dist_Rec.segment22 IS NOT NULL)) THEN
5045 l_seg_val(l_index) := c_Dist_Rec.segment22;
5046
5047 ELSIF ((PSB_WS_ACCT1.g_seg_name(l_index) = 'SEGMENT23') AND
5048 (c_Dist_Rec.segment23 IS NOT NULL)) THEN
5049 l_seg_val(l_index) := c_Dist_Rec.segment23;
5050
5051 ELSIF ((PSB_WS_ACCT1.g_seg_name(l_index) = 'SEGMENT24') AND
5052 (c_Dist_Rec.segment24 IS NOT NULL)) THEN
5053 l_seg_val(l_index) := c_Dist_Rec.segment24;
5054
5055 ELSIF ((PSB_WS_ACCT1.g_seg_name(l_index) = 'SEGMENT25') AND
5056 (c_Dist_Rec.segment25 IS NOT NULL)) THEN
5057 l_seg_val(l_index) := c_Dist_Rec.segment25;
5058
5059 ELSIF ((PSB_WS_ACCT1.g_seg_name(l_index) = 'SEGMENT26') AND
5060 (c_Dist_Rec.segment26 IS NOT NULL)) THEN
5061 l_seg_val(l_index) := c_Dist_Rec.segment26;
5062
5063 ELSIF ((PSB_WS_ACCT1.g_seg_name(l_index) = 'SEGMENT27') AND
5064 (c_Dist_Rec.segment27 IS NOT NULL)) THEN
5065 l_seg_val(l_index) := c_Dist_Rec.segment27;
5066
5067 ELSIF ((PSB_WS_ACCT1.g_seg_name(l_index) = 'SEGMENT28') AND
5068 (c_Dist_Rec.segment28 IS NOT NULL)) THEN
5069 l_seg_val(l_index) := c_Dist_Rec.segment28;
5070
5071 ELSIF ((PSB_WS_ACCT1.g_seg_name(l_index) = 'SEGMENT29') AND
5072 (c_Dist_Rec.segment29 IS NOT NULL)) THEN
5073 l_seg_val(l_index) := c_Dist_Rec.segment29;
5074
5075 ELSIF ((PSB_WS_ACCT1.g_seg_name(l_index) = 'SEGMENT30') AND
5076 (c_Dist_Rec.segment30 IS NOT NULL)) THEN
5077 l_seg_val(l_index) := c_Dist_Rec.segment30;
5078 ELSE
5079 l_seg_val(l_index) := l_ccid_val(l_index);
5080 END IF;
5081
5082
5083 END LOOP;
5084
5085
5086 l_flex_delimiter := FND_FLEX_EXT.Get_Delimiter
5087 (application_short_name => 'SQLGL',
5088 key_flex_code => 'GL#',
5089 structure_number => p_chart_of_accounts_id);
5090
5091 l_concat_segments := FND_FLEX_EXT.Concatenate_Segments
5092 (n_segments => l_last_index,
5093 segments => l_seg_val,
5094 delimiter => l_flex_delimiter);
5095
5096
5097 IF NOT fnd_flex_keyval.validate_segs(operation =>'FIND_COMBINATION',
5098 appl_short_name =>'SQLGL',
5099 key_flex_code =>'GL#',
5100 structure_number =>p_chart_of_accounts_id,
5101 concat_segments =>l_concat_segments) THEN
5102
5103 BEGIN
5104 /* Bug 3692601 Start */
5105
5106 IF NVL(g_validation_mode,'WSC') <> 'STANDALONE'
5107 AND NVL(p_error_flag,FND_API.G_RET_STS_SUCCESS) <> FND_API.G_RET_STS_ERROR THEN
5108 SET_POS_HEADING(l_position_flag ,p_name,
5109 p_employee_number,p_error_flag);
5110
5111 p_error_flag := FND_API.G_RET_STS_SUCCESS;
5112 ELSE
5113 SET_POS_HEADING(l_position_flag ,p_name,
5114 p_employee_number,p_error_flag);
5115
5116 END IF;
5117
5118 FND_MESSAGE.SET_NAME('PSB', 'PSB_GL_CCID_FAILURE');
5119 FND_MESSAGE.SET_TOKEN('ACCOUNT', l_concat_segments);
5120 FND_MSG_PUB.ADD;
5121 /* Bug 3692601 End */
5122 END;
5123 /* Bug 3692601 Start */
5124 ELSE
5125 l_ccid_overwritten := FND_FLEX_EXT.get_ccid
5126 (application_short_name => 'SQLGL',
5127 key_flex_code => 'GL#',
5128 structure_number => p_chart_of_accounts_id,
5129 validation_date => SYSDATE,
5130 concatenated_segments => l_concat_segments);
5131 IF l_ccid_overwritten > 0 THEN
5132 FOR cc_rec IN
5133 (SELECT detail_budgeting_allowed_flag, summary_flag
5134 FROM GL_CODE_COMBINATIONS
5135 WHERE code_combination_id = l_ccid_overwritten
5136 )
5137 LOOP
5138 IF cc_rec.detail_budgeting_allowed_flag = 'N'
5139 OR cc_rec.summary_flag = 'Y' THEN
5140 SET_POS_HEADING(l_position_flag, p_name,p_employee_number, p_error_flag);
5141 FND_MESSAGE.Set_Name('PSB', 'PSB_SUMMARY_DETAIL_BUDGETING');
5142 FND_MESSAGE.SET_TOKEN('ACCOUNT', l_concat_segments);
5143 FND_MSG_PUB.ADD;
5144 END IF;
5145 END LOOP;
5146 END IF;
5147 /* Bug 3692601 End */
5148 END IF;
5149
5150 /* Bug 3692601 Start */
5151 ELSE
5152 FOR cc_rec IN
5153 (SELECT detail_budgeting_allowed_flag, summary_flag
5154 FROM GL_CODE_COMBINATIONS
5155 WHERE code_combination_id = c_dist_rec.code_combination_id
5156 )
5157 LOOP
5158 IF cc_rec.detail_budgeting_allowed_flag = 'N'
5159 OR cc_rec.summary_flag = 'Y' THEN
5160 l_concat_segments := FND_FLEX_EXT.Get_Segs
5161 (application_short_name => 'SQLGL',
5162 key_flex_code => 'GL#',
5163 structure_number => p_chart_of_accounts_id,
5164 combination_id => c_dist_rec.code_combination_id);
5165 SET_POS_HEADING(l_position_flag, p_name,p_employee_number, p_error_flag);
5166 FND_MESSAGE.Set_Name('PSB', 'PSB_SUMMARY_DETAIL_BUDGETING');
5167 FND_MESSAGE.SET_TOKEN('ACCOUNT', l_concat_segments);
5168 FND_MSG_PUB.ADD;
5169 END IF;
5170 END LOOP;
5171 END IF;
5172 /* Bug 3692601 End */
5173
5174 END LOOP;
5175 END IF;
5176
5177 /* Bug 3247574 End */
5178
5179 FOR l_calcperiod_index in 1..PSB_WS_ACCT1.g_num_calc_periods LOOP
5180
5181 l_calc_start_date := PSB_WS_ACCT1.g_calculation_periods(l_calcperiod_index).start_date;
5182 l_calc_end_date := PSB_WS_ACCT1.g_calculation_periods(l_calcperiod_index).end_date;
5183
5184 END LOOP;
5185
5186 for c_job_rec in c_job loop
5187 l_job_exists := FND_API.G_TRUE;
5188 end loop;
5189
5190 IF NOT FND_API.to_Boolean(l_job_exists) THEN
5191 SET_POS_HEADING(l_position_flag,
5192 p_name,p_employee_number,p_error_flag );
5193 FND_MESSAGE.SET_NAME('PSB', 'PSB_VAL_NO_JOB_ATTRIBUTE');
5194 FND_MSG_PUB.Add;
5195 END IF;
5196
5197 --++ paybasis
5198 /* Bug 3247574 Start
5199 Changes done for Worksheet Exception Report */
5200 /* The following code checks for Invalid Pay Basis
5201 attached to a position */
5202
5203 FOR c_pay_basis_rec in c_pay_basis LOOP
5204 l_pay_basis_invalid := FND_API.G_TRUE;
5205 END LOOP;
5206
5207 IF FND_API.to_Boolean(l_pay_basis_invalid) THEN
5208 /* SET_POS_HEADING(l_position_flag ,
5209 p_name,p_employee_number,p_error_flag );
5210 FND_MESSAGE.SET_NAME('PSB', 'PSB_VAL_INVALID_PAY_BASIS');
5211 FND_MSG_PUB.Add; */
5212 SET_POS_HEADING(l_position_flag,
5213 p_name,p_employee_number,p_error_flag );
5214 FND_MESSAGE.SET_NAME('PSB', 'PSB_INVALID_SALARY_BASIS');
5215 FND_MESSAGE.SET_TOKEN('POSITION', p_name);
5216 FND_MESSAGE.SET_TOKEN('START_DATE', l_calc_start_date);
5217 FND_MESSAGE.SET_TOKEN('END_DATE', l_calc_end_date);
5218 FND_MSG_PUB.ADD;
5219 END IF;
5220
5221 /* Bug 3247574 End */
5222
5223 --+ validate that salary exists in calendar and that
5224 --+ salary distribution exists for each of salary assignments
5225 --+ if salary has HOURLY pay basis, then check that default wkly hours exists
5226 --+ for each calculation period of the salary
5227
5228
5229 for c_salary_rec in c_salary loop
5230
5231 l_salary_exists := FND_API.G_TRUE;
5232
5233 --+ get the stinger date between calendar date and salary date
5234 --+ p_effective_end_date is always not null since it the calendar's end date
5235
5236 l_salary_start_date := greatest(c_salary_rec.effective_start_date, p_effective_start_date);
5237 l_salary_end_date := least(nvl(c_salary_rec.effective_end_date, p_effective_end_date), p_effective_end_date);
5238
5239 --++ if hourly salary basis, check that wkly salary exists for each of calculation pd
5240 --++ within salary range
5241 if c_salary_rec.pay_basis = 'HOURLY' then
5242
5243 /* Bug No 1920021 Start */
5244 /* --- Commented the following 8 Lines ---
5245 for c_calc_periods_rec in c_calc_periods loop
5246
5247 if (((l_salary_start_date <= c_calc_periods_rec.end_date)
5248 and (l_salary_end_date is null))
5249 or ((l_salary_start_date between c_calc_periods_rec.start_date and c_calc_periods_rec.end_date)
5250 or (l_salary_end_date between c_calc_periods_rec.start_date and c_calc_periods_rec.end_date)
5251 or ((l_salary_start_date < c_calc_periods_rec.start_date)
5252 and (l_salary_end_date > c_calc_periods_rec.end_date)))) then
5253 --- */
5254 /* Bug No 1920021 End */
5255
5256 begin
5257
5258 /* Bug No 1920021 Start */
5259 --- l_calc_exists := FND_API.G_FALSE;
5260 l_weekly_hours_exists := FND_API.G_FALSE;
5261 /* Bug No 1920021 End */
5262
5263 for c_weekly_hours_rec in c_weekly_hours loop
5264
5265 /* Bug No 1920021 Start */
5266 /* --- Commented the following 2 Lines, added 3rd line --- */
5267 -- (c_calc_periods_rec.start_date, c_calc_periods_rec.end_date) loop
5268
5269 --- l_calc_exists := FND_API.G_TRUE;
5270 l_weekly_hours_exists := FND_API.G_TRUE;
5271 /* Bug No 1920021 End */
5272
5273 end loop;
5274
5275 /* Bug No 1920021 Start */
5276 --- Commented the following 3 Lines ---
5277 -- IF NOT FND_API.to_Boolean(l_calc_exists) THEN
5278 -- l_weekly_hours_exists := FND_API.G_FALSE;
5279 -- END IF;
5280 /* Bug No 1920021 End */
5281
5282 end;
5283
5284 /* Bug No 1920021 Start */
5285 /* --- Commented the following 2 Lines --- */
5286 -- end if;
5287
5288 -- end loop;
5289 /* Bug No 1920021 End */
5290
5291 if NOT FND_API.to_Boolean(l_weekly_hours_exists) THEN
5292 SET_POS_HEADING(l_position_flag ,
5293 p_name,p_employee_number,p_error_flag );
5294 FND_MESSAGE.SET_NAME('PSB', 'PSB_VAL_NO_DEFAULT_WEEK_HOURS');
5295 FND_MSG_PUB.Add;
5296 end if;
5297
5298 end if;
5299
5300 --++ then check if there are distributions within the salary date range comp_date
5301
5302 VALIDATE_DISTRIBUTION(
5303 p_position_id => p_position_id,
5304 p_worksheet_id => p_worksheet_id,
5305 p_name => p_name,
5306 p_employee_number => p_employee_number,
5307 p_position_flag => l_position_flag,
5308 p_data_extract_id => p_data_extract_id,
5309 p_root_budget_group_id => p_root_budget_group_id,
5310 p_set_of_books_id => p_set_of_books_id,
5311 p_budget_calendar_id => p_budget_calendar_id,
5312 p_chart_of_accounts_id => p_chart_of_accounts_id,
5313 p_startdate_pp => p_startdate_pp,
5314 p_enddate_cy => p_enddate_cy,
5315 p_effective_start_date => l_salary_start_date,
5316 p_effective_end_date => l_salary_end_date,
5317 p_error_flag => p_error_flag,
5318 p_return_status => l_return_status);
5319
5320 if l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
5321 raise FND_API.G_EXC_ERROR ;
5322 end if ;
5323
5324 end loop; -- end salary_rec
5325
5326
5327 IF NOT FND_API.to_Boolean(l_salary_exists) THEN
5328
5329 SET_POS_HEADING(l_position_flag ,
5330 p_name,p_employee_number,p_error_flag );
5331 FND_MESSAGE.SET_NAME('PSB', 'PSB_VAL_MISSING_SALARY');
5332 FND_MSG_PUB.Add;
5333 END IF;
5334
5335 p_return_status := FND_API.G_RET_STS_SUCCESS;
5336
5337 EXCEPTION
5338
5339 WHEN FND_API.G_EXC_ERROR then
5340
5341 p_return_status := FND_API.G_RET_STS_ERROR;
5342
5343 WHEN FND_API.G_EXC_UNEXPECTED_ERROR then
5344
5345 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5346
5347 WHEN OTHERS then
5348
5349 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5350
5351 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) then
5352 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
5353 l_api_name);
5354 END IF;
5355
5356 END VALIDATE_POSITION;
5357
5358 /* -------------------------------------------------------------------- */
5359
5360 PROCEDURE VALIDATE_DISTRIBUTION
5361 ( p_position_id IN NUMBER,
5362 p_worksheet_id IN NUMBER,
5363 p_name IN VARCHAR2,
5364 p_employee_number IN VARCHAR2,
5365 p_position_flag IN OUT NOCOPY VARCHAR2,
5366 p_data_extract_id IN NUMBER,
5367 p_root_budget_group_id IN NUMBER,
5368 p_set_of_books_id IN NUMBER,
5369 p_budget_calendar_id IN NUMBER,
5370 p_chart_of_accounts_id IN NUMBER,
5371 p_startdate_pp IN DATE,
5372 p_enddate_cy IN DATE,
5373 p_effective_start_date IN DATE,
5374 p_effective_end_date IN DATE,
5375 p_error_flag IN OUT NOCOPY VARCHAR2,
5376 p_return_status OUT NOCOPY VARCHAR2
5377 ) IS
5378
5379 l_api_name CONSTANT VARCHAR2(30) := 'VALIDATE_DISTRIBUTION';
5380 l_return_status VARCHAR2(1);
5381 l_msg_count NUMBER;
5382 l_msg_data VARCHAR2(2000);
5383 --
5384 l_check_allowed_ret_status VARCHAR2(1);
5385 l_concat_segments VARCHAR2(2000);
5386 l_out_budget_group_id NUMBER;
5387 l_out_ccid NUMBER;
5388 l_startdate_pp DATE;
5389 l_enddate_cy DATE;
5390 l_salary_distr_exists VARCHAR2(1) := FND_API.G_FALSE;
5391
5392 --
5393
5394 CURSOR c_sum IS
5395 SELECT x_sum.sum_tot,x_sum.start_date
5396 FROM
5397 (SELECT SUM(distribution_percent) sum_tot ,
5398 effective_start_date start_date
5399 FROM psb_position_pay_distributions
5400 WHERE code_combination_id IS NOT NULL
5401 AND worksheet_id is null
5402 AND position_id = p_position_id
5403 AND (((effective_start_date <= p_effective_end_date)
5404 AND (effective_end_date is null))
5405 OR ((effective_start_date between p_effective_start_date and p_effective_end_date)
5406 OR (effective_end_date between p_effective_start_date and p_effective_end_date)
5407 OR ((effective_start_date < p_effective_start_date)
5408 AND (effective_end_date > p_effective_end_date))))
5409 GROUP BY position_id,effective_start_date
5410 ) x_sum
5411 WHERE x_sum.sum_tot <> 100
5412 UNION
5413 SELECT x_sum.sum_tot,x_sum.start_date
5414 FROM
5415 (SELECT SUM(distribution_percent) sum_tot ,
5416 effective_start_date start_date
5417 FROM psb_position_pay_distributions
5418 WHERE code_combination_id IS NOT NULL
5419 AND worksheet_id = p_worksheet_id
5420 AND position_id = p_position_id
5421 AND (((effective_start_date <= p_effective_end_date)
5422 AND (effective_end_date is null))
5423 OR ((effective_start_date between p_effective_start_date and p_effective_end_date)
5424 OR (effective_end_date between p_effective_start_date and p_effective_end_date)
5425 OR ((effective_start_date < p_effective_start_date)
5426 AND (effective_end_date > p_effective_end_date))))
5427 GROUP BY position_id,effective_start_date
5428 ) x_sum
5429 WHERE x_sum.sum_tot <> 100;
5430
5431 BEGIN
5432
5433 FOR l_s_distributions_rec IN c_sum
5434 LOOP
5435
5436 SET_POS_HEADING(p_position_flag ,
5437 p_name,p_employee_number,p_error_flag );
5438 FND_MESSAGE.SET_NAME('PSB', 'PSB_VAL_INCOMPLETE_DISTR');
5439 FND_MSG_PUB.Add;
5440
5441 END LOOP;
5442
5443 --++
5444
5445 FOR l_distributions_rec IN
5446
5447 (SELECT position_id,
5448 code_combination_id
5449 FROM psb_position_pay_distributions
5450 WHERE position_id = p_position_id
5451 AND code_combination_id IS NOT NULL
5452 AND (((effective_start_date <= p_effective_end_date)
5453 AND (effective_end_date is null))
5454 OR ((effective_start_date between p_effective_start_date and p_effective_end_date)
5455 OR (effective_end_date between p_effective_start_date and p_effective_end_date)
5456 OR ((effective_start_date < p_effective_start_date)
5457 AND (effective_end_date > p_effective_end_date))))
5458
5459 )
5460
5461 LOOP
5462
5463 l_salary_distr_exists := FND_API.G_TRUE; -- distribution exists
5464
5465 l_concat_segments := FND_FLEX_EXT.Get_Segs(
5466 application_short_name => 'SQLGL',
5467 key_flex_code => 'GL#',
5468 structure_number => p_chart_of_accounts_id,
5469 combination_id => l_distributions_rec.code_combination_id
5470 );
5471 -- concatenated
5472
5473 PSB_VALIDATE_ACCT_PVT.Validate_Account (
5474 p_api_version => 1.0,
5475 p_commit => FND_API.G_FALSE,
5476 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
5477 p_return_status => l_return_status,
5478 p_msg_count => l_msg_count,
5479 p_msg_data => l_msg_data,
5480 p_parent_budget_group_id => p_root_budget_group_id,
5481 p_startdate_pp => p_startdate_pp,
5482 p_enddate_cy => p_enddate_cy ,
5483 p_set_of_books_id => p_set_of_books_id,
5484 p_flex_code => p_chart_of_accounts_id,
5485 p_create_budget_account => FND_API.G_FALSE,
5486 p_worksheet_id => p_worksheet_id,
5487 p_in_ccid => l_distributions_rec.code_combination_id,
5488 p_out_ccid => l_out_ccid,
5489 p_budget_group_id => l_out_budget_group_id
5490 );
5491
5492 IF l_return_status <> FND_API.G_RET_STS_SUCCESS then
5493
5494 SET_POS_HEADING(p_position_flag ,
5495 p_name,p_employee_number,p_error_flag );
5496 FND_MESSAGE.SET_NAME('PSB', 'PSB_VAL_CCID_NOTIN_BG');
5497 FND_MESSAGE.SET_TOKEN('CCID', l_concat_segments);
5498 FND_MSG_PUB.Add;
5499
5500 ELSE
5501 -- validate in in account range
5502 -- ... next check if the budget group belongs to the worksheet's
5503 -- ... budget group's hierarchy
5504 -- ... call wrapper
5505
5506
5507 l_check_allowed_ret_status:= PSB_POSITIONS_I_PVT.Check_Allowed
5508 (
5509 p_api_version => 1.0,
5510 p_init_msg_list => FND_API.G_FALSE,
5511 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
5512 p_msg_count => l_msg_count,
5513 p_msg_data => l_msg_data,
5514 p_worksheet_id => p_worksheet_id,
5515 p_position_budget_group_id => l_out_budget_group_id
5516 ) ;
5517
5518 IF l_check_allowed_ret_status <> FND_API.G_TRUE THEN
5519 SET_POS_HEADING(p_position_flag ,
5520 p_name,p_employee_number,p_error_flag );
5521 FND_MESSAGE.SET_NAME('PSB', 'PSB_VAL_INVALID_CCID_IN_BG');
5522 FND_MESSAGE.SET_TOKEN('CCID', l_concat_segments);
5523 FND_MSG_PUB.Add;
5524
5525 END IF;
5526
5527 --
5528 END IF;
5529
5530
5531 END LOOP;
5532
5533 IF NOT FND_API.to_Boolean(l_salary_distr_exists) THEN
5534 SET_POS_HEADING(p_position_flag ,
5535 p_name,p_employee_number,p_error_flag );
5536 FND_MESSAGE.SET_NAME('PSB', 'PSB_VAL_MISSING_DISTRIBUTIONS');
5537 FND_MSG_PUB.Add;
5538 END IF;
5539
5540 p_return_status := FND_API.G_RET_STS_SUCCESS;
5541 --
5542 EXCEPTION
5543
5544 WHEN FND_API.G_EXC_ERROR then
5545
5546 p_return_status := FND_API.G_RET_STS_ERROR;
5547
5548 WHEN FND_API.G_EXC_UNEXPECTED_ERROR then
5549
5550 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5551
5552 WHEN OTHERS then
5553
5554 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5555
5556 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) then
5557 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
5558 l_api_name);
5559 END IF;
5560
5561 END VALIDATE_DISTRIBUTION;
5562
5563 /* -------------------------------------------------------------------- */
5564
5565 PROCEDURE Output_Message_To_Table(p_worksheet_id IN NUMBER,
5566 p_return_status OUT NOCOPY VARCHAR2) AS
5567
5568 l_api_name CONSTANT VARCHAR2(30) := 'Output_Message_To_Table';
5569 l_reqid NUMBER;
5570 l_rep_req_id NUMBER;
5571 l_userid NUMBER;
5572 l_msg_count NUMBER;
5573 l_msg_buf varchar2(1000);
5574
5575 BEGIN
5576
5577
5578 delete from PSB_ERROR_MESSAGES
5579 where source_process = 'POSITION_WORKSHEET_EXCEPTION'
5580 and process_id = p_worksheet_id;
5581
5582 l_reqid := FND_GLOBAL.CONC_REQUEST_ID;
5583 l_userid := FND_GLOBAL.USER_ID;
5584
5585 FND_MSG_PUB.Count_And_Get ( p_count => l_msg_count,
5586 p_data => l_msg_buf );
5587
5588 PSB_MESSAGE_S.Insert_Error ( p_source_process => 'POSITION_WORKSHEET_EXCEPTION',
5589 p_process_id => p_worksheet_id,
5590 p_msg_count => l_msg_count,
5591 p_msg_data => l_msg_buf,
5592 p_desc_sequence => FND_API.G_FALSE) ;
5593
5594 -- initialize error message stack --
5595 FND_MSG_PUB.initialize;
5596
5597 p_return_status := FND_API.G_RET_STS_SUCCESS;
5598
5599 END Output_Message_To_Table;
5600
5601 /* -------------------------------------------------------------------- */
5602
5603 PROCEDURE SET_POS_HEADING(
5604 p_position_flag IN OUT NOCOPY VARCHAR2,
5605 p_position_name IN VARCHAR2,
5606 p_employee_number IN VARCHAR2,
5607 p_error_flag IN OUT NOCOPY VARCHAR2
5608 ) IS
5609
5610 BEGIN
5611 IF p_position_flag IS NULL THEN
5612 -- header
5613 p_position_flag := 'Y';
5614 FND_MESSAGE.SET_NAME('PSB', 'PSB_VAL_LINE');
5615 FND_MSG_PUB.Add;
5616 FND_MESSAGE.SET_NAME('PSB', 'PSB_VAL_POSITION');
5617 FND_MESSAGE.SET_TOKEN('NAME', p_position_name);
5618 FND_MESSAGE.SET_TOKEN('EMP', p_employee_number);
5619 FND_MSG_PUB.Add;
5620 FND_MESSAGE.SET_NAME('PSB', 'PSB_VAL_LINE');
5621 FND_MSG_PUB.Add;
5622
5623 END IF;
5624 p_error_flag := FND_API.G_RET_STS_ERROR ;
5625
5626
5627 END SET_POS_HEADING;
5628
5629
5630 /* ------------------------------------------------------------------------- */
5631
5632 -- Check whether the Budget Group for a Position is allowed within a
5633 -- Worksheet. This is invoked by the Worksheet Modification module when
5634 -- creating new Positions
5635
5636 FUNCTION Rev_Check_Allowed
5637 ( p_api_version IN NUMBER,
5638 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_NONE,
5639 p_startdate_pp IN DATE,
5640 p_enddate_cy IN DATE,
5641 p_worksheet_id IN NUMBER,
5642 p_position_budget_group_id IN NUMBER
5643 ) RETURN VARCHAR2 IS
5644
5645 l_api_name CONSTANT VARCHAR2(30) := 'Check_Allowed';
5646 l_api_version CONSTANT NUMBER := 1.0;
5647
5648 l_budget_group_id NUMBER;
5649
5650 l_return_status VARCHAR2(1) := FND_API.G_FALSE;
5651
5652 cursor c_Allowed is
5653 select 'Valid'
5654 from PSB_BUDGET_GROUPS
5655 where budget_group_type = 'R'
5656 and (p_startdate_pp is null or effective_start_date <= p_startdate_pp)
5657 and (effective_end_date is null
5658 or effective_end_date >= p_enddate_cy)
5659 and budget_group_id = p_position_budget_group_id
5660 start with budget_group_id = l_budget_group_id
5661 connect by prior budget_group_id = parent_budget_group_id;
5662 -- validation for budget revision that ccid belong to bg hierarchy
5663 -- pp date is position end date w/c may be null; cy date is pos start date
5664
5665 cursor c_WS (Worksheet NUMBER) is
5666 select budget_group_id
5667 from PSB_BUDGET_REVISIONS
5668 where budget_revision_id = Worksheet;
5669
5670 BEGIN
5671
5672 -- Standard call to check for call compatibility
5673
5674 if not FND_API.Compatible_API_Call (l_api_version,
5675 p_api_version,
5676 l_api_name,
5677 G_PKG_NAME)
5678 then
5679 raise FND_API.G_EXC_UNEXPECTED_ERROR;
5680 end if;
5681
5682 for c_WS_Rec in c_WS (p_worksheet_id) loop
5683 l_budget_group_id := c_WS_Rec.budget_group_id;
5684 end loop;
5685
5686 for c_Allowed_Rec in c_Allowed loop
5687 l_return_status := FND_API.G_TRUE;
5688 end loop;
5689
5690 return l_return_status;
5691
5692
5693 EXCEPTION
5694
5695 when FND_API.G_EXC_ERROR then
5696 return FND_API.G_FALSE;
5697
5698 when FND_API.G_EXC_UNEXPECTED_ERROR then
5699 return FND_API.G_FALSE;
5700
5701 when OTHERS then
5702 return FND_API.G_FALSE;
5703
5704 if FND_MSG_PUB.Check_Msg_Level
5705 (p_message_level => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
5706 then
5707 FND_MSG_PUB.Add_Exc_Msg
5708 (p_pkg_name => G_PKG_NAME,
5709 p_procedure_name => l_api_name);
5710 end if;
5711
5712 END Rev_Check_Allowed;
5713
5714 FUNCTION Get_Worksheet_ID RETURN NUMBER IS
5715 BEGIN
5716 Return g_Worksheet_ID;
5717 END Get_Worksheet_ID;
5718
5719 FUNCTION Get_Start_Date RETURN DATE IS
5720 BEGIN
5721 Return g_Start_Date;
5722 END Get_Start_Date;
5723
5724 FUNCTION Get_End_Date RETURN DATE IS
5725 BEGIN
5726 Return g_End_Date;
5727 END Get_End_Date;
5728
5729 FUNCTION Get_Select_Date RETURN DATE IS
5730 BEGIN
5731 Return g_Select_Date;
5732 END Get_Select_Date;
5733
5734
5735 FUNCTION Get_Worksheet_Flag RETURN varchar2 IS
5736 BEGIN
5737 Return g_Worksheet_Flag;
5738 END Get_Worksheet_Flag;
5739
5740 /* ------------------------------------------------------------------------- */
5741
5742 -- Get Debug Information
5743 FUNCTION get_debug RETURN VARCHAR2 IS
5744 BEGIN
5745 return(g_dbug);
5746 END get_debug;
5747
5748 /* ----------------------------------------------------------------------- */
5749
5750 /* Start Bug 3422919 */
5751
5752 FUNCTION get_employee_id
5753 (
5754 p_data_extract_id IN NUMBER,
5755 p_worksheet_id IN NUMBER := NULL,
5756 p_position_id IN NUMBER
5757 ) RETURN NUMBER IS
5758
5759 l_emp_id NUMBER;
5760
5761 BEGIN
5762
5763 IF p_worksheet_id IS NULL THEN
5764
5765 SELECT emp.employee_id
5766 INTO l_emp_id
5767 FROM psb_employees emp, psb_position_assignments pavb
5768 WHERE pavb.position_id = p_position_id
5769 AND pavb.assignment_type = 'EMPLOYEE'
5770 AND emp.data_extract_id = p_data_extract_id
5771 AND emp.employee_id = pavb.employee_id
5772 AND rownum=1
5773 AND pavb.worksheet_id IS NULL
5774 ORDER BY pavb.effective_start_date DESC;
5775
5776 ELSE
5777 SELECT emp.employee_id
5778 INTO l_emp_id
5779 FROM psb_employees emp, psb_position_assignments pavb
5780 WHERE pavb.position_id = p_position_id
5781 AND pavb.assignment_type = 'EMPLOYEE'
5782 AND emp.data_extract_id = p_data_extract_id
5783 AND emp.employee_id = pavb.employee_id
5784 AND rownum=1
5785 AND (pavb.worksheet_id = p_worksheet_id
5786 OR pavb.worksheet_id IS NULL)
5787 ORDER BY pavb.effective_start_date DESC,
5788 NVL(pavb.worksheet_id,0) DESC;
5789
5790 END IF;
5791
5792 RETURN(l_emp_id);
5793
5794 EXCEPTION
5795 WHEN NO_DATA_FOUND THEN
5796 RETURN(NULL);
5797 WHEN OTHERS THEN
5798 RETURN(NULL);
5799
5800 END get_employee_id;
5801
5802 FUNCTION get_employee_number
5803 (
5804 p_data_extract_id IN NUMBER,
5805 p_worksheet_id IN NUMBER := NULL,
5806 p_position_id IN NUMBER
5807 ) RETURN VARCHAR2 IS
5808
5809 l_emp_number VARCHAR2(240);
5810
5811 BEGIN
5812
5813 IF p_worksheet_id IS NULL THEN
5814
5815 SELECT emp.employee_number
5816 INTO l_emp_number
5817 FROM psb_employees emp, psb_position_assignments pavb
5818 WHERE pavb.position_id = p_position_id
5819 AND pavb.assignment_type = 'EMPLOYEE'
5820 AND emp.data_extract_id = p_data_extract_id
5821 AND emp.employee_id = pavb.employee_id
5822 AND rownum=1
5823 AND pavb.worksheet_id IS NULL
5824 ORDER BY pavb.effective_start_date DESC;
5825
5826 ELSE
5827 SELECT emp.employee_number
5828 INTO l_emp_number
5829 FROM psb_employees emp, psb_position_assignments pavb
5830 WHERE pavb.position_id = p_position_id
5831 AND pavb.assignment_type = 'EMPLOYEE'
5832 AND emp.data_extract_id = p_data_extract_id
5833 AND emp.employee_id = pavb.employee_id
5834 AND rownum=1
5835 AND (pavb.worksheet_id = p_worksheet_id
5836 OR pavb.worksheet_id IS NULL)
5837 ORDER BY pavb.effective_start_date DESC,
5838 NVL(pavb.worksheet_id,0) DESC;
5839
5840 END IF;
5841
5842 RETURN(l_emp_number);
5843
5844 EXCEPTION
5845 WHEN NO_DATA_FOUND THEN
5846 RETURN(NULL);
5847 WHEN OTHERS THEN
5848 RETURN(NULL);
5849
5850 END get_employee_number;
5851
5852
5853 FUNCTION get_employee_name
5854 (
5855 p_data_extract_id IN NUMBER,
5856 p_worksheet_id IN NUMBER := NULL,
5857 p_position_id IN NUMBER
5858 ) RETURN VARCHAR2 IS
5859
5860 l_emp_name VARCHAR2(240);
5861
5862 BEGIN
5863
5864 IF p_worksheet_id IS NULL THEN
5865
5866 SELECT emp.full_name
5867 INTO l_emp_name
5868 FROM psb_employees emp, psb_position_assignments pavb
5869 WHERE pavb.position_id = p_position_id
5870 AND pavb.assignment_type = 'EMPLOYEE'
5871 AND emp.data_extract_id = p_data_extract_id
5872 AND emp.employee_id = pavb.employee_id
5873 AND rownum=1
5874 AND pavb.worksheet_id IS NULL
5875 ORDER BY pavb.effective_start_date DESC;
5876
5877 ELSE
5878 SELECT emp.full_name
5879 INTO l_emp_name
5880 FROM psb_employees emp, psb_position_assignments pavb
5881 WHERE pavb.position_id = p_position_id
5882 AND pavb.assignment_type = 'EMPLOYEE'
5883 AND emp.data_extract_id = p_data_extract_id
5884 AND emp.employee_id = pavb.employee_id
5885 AND rownum=1
5886 AND (pavb.worksheet_id = p_worksheet_id
5887 OR pavb.worksheet_id IS NULL)
5888 ORDER BY pavb.effective_start_date DESC,
5889 NVL(pavb.worksheet_id,0) DESC;
5890
5891 END IF;
5892
5893 RETURN(l_emp_name);
5894
5895 EXCEPTION
5896 WHEN NO_DATA_FOUND THEN
5897 RETURN(NULL);
5898 WHEN OTHERS THEN
5899 RETURN(NULL);
5900
5901 END get_employee_name;
5902
5903
5904 FUNCTION get_job_name
5905 ( p_data_extract_id IN NUMBER,
5906 p_worksheet_id IN NUMBER := NULL,
5907 p_position_id IN NUMBER
5908 ) RETURN VARCHAR2 IS
5909
5910 l_job_name VARCHAR2(240);
5911
5912 BEGIN
5913
5914 IF p_worksheet_id IS NULL THEN
5915
5916 SELECT patv.attribute_value
5917 INTO l_job_name
5918 FROM psb_attribute_values patv,
5919 psb_position_assignments pava
5920 WHERE patv.attribute_value_id = pava.attribute_value_id
5921 AND pava.position_id = p_position_id
5922 AND patv.data_extract_id = p_data_extract_id
5923 AND rownum=1
5924 AND EXISTS
5925 (SELECT 1 FROM psb_attributes pat
5926 WHERE pat.attribute_id = pava.attribute_id
5927 AND pat.system_attribute_type = 'JOB_CLASS')
5928 AND pava.worksheet_id IS NULL
5929 ORDER BY pava.effective_start_date DESC;
5930 ELSE
5931
5932 SELECT patv.attribute_value
5933 INTO l_job_name
5934 FROM psb_attribute_values patv,
5935 psb_position_assignments pava
5936 WHERE patv.attribute_value_id = pava.attribute_value_id
5937 AND pava.position_id = p_position_id
5938 AND patv.data_extract_id = p_data_extract_id
5939 AND rownum=1
5940 AND exists (SELECT 1 from psb_attributes pat
5941 WHERE pat.attribute_id = pava.attribute_id
5942 AND pat.system_attribute_type = 'JOB_CLASS')
5943 AND (pava.worksheet_id = p_worksheet_id
5944 OR pava.worksheet_id IS NULL)
5945 ORDER BY pava.effective_start_date DESC,
5946 NVL(pava.worksheet_id,0) DESC;
5947 END IF;
5948
5949 RETURN(l_job_name);
5950
5951 EXCEPTION
5952 WHEN NO_DATA_FOUND THEN
5953 RETURN(NULL);
5954 WHEN OTHERS THEN
5955 RETURN(NULL);
5956
5957 END get_job_name;
5958
5959 /* End Bug 3422919 */
5960
5961 /* Bug 1308558 Start */
5962 -- new api created for applying the Element and Attribute
5963 -- assignments to positions
5964
5965 PROCEDURE Apply_Position_Default_Rules
5966 ( p_api_version IN NUMBER,
5967 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
5968 p_commit IN VARCHAR2 := FND_API.G_FALSE,
5969 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
5970 x_return_status OUT NOCOPY VARCHAR2,
5971 x_msg_count OUT NOCOPY NUMBER,
5972 x_msg_data OUT NOCOPY VARCHAR2,
5973 p_position_assignment_id IN OUT NOCOPY NUMBER,
5974 p_data_extract_id IN NUMBER,
5975 p_position_id IN NUMBER,
5976 p_assignment_type IN VARCHAR2,
5977 p_attribute_id IN NUMBER,
5978 p_attribute_value_id IN NUMBER,
5979 p_attribute_value IN VARCHAR2,
5980 p_pay_element_id IN NUMBER,
5981 p_pay_element_option_id IN NUMBER,
5982 p_effective_start_date IN DATE,
5983 p_effective_end_date IN DATE,
5984 p_element_value_type IN VARCHAR2,
5985 p_element_value IN NUMBER,
5986 p_currency_code IN VARCHAR2,
5987 p_pay_basis IN VARCHAR2,
5988 p_employee_id IN NUMBER,
5989 p_primary_employee_flag IN VARCHAR2,
5990 p_global_default_flag IN VARCHAR2,
5991 p_assignment_default_rule_id IN NUMBER,
5992 p_modify_flag IN VARCHAR2,
5993 p_mode IN VARCHAR2 := 'R' ,
5994 p_worksheet_id IN NUMBER
5995 ) IS
5996
5997 l_api_name CONSTANT VARCHAR2(30) := 'Apply_Position_Default_Rules';
5998 l_api_version CONSTANT NUMBER := 1.0 ;
5999 l_position_assignment_id NUMBER;
6000 l_matching_assmt BOOLEAN := FALSE;
6001 l_return_status VARCHAR2(1);
6002 l_msg_count NUMBER;
6003 l_msg_data VARCHAR2(2000);
6004 l_rowid VARCHAR2(100);
6005 l_pay_basis VARCHAR2(10);
6006 l_pay_element_id NUMBER;
6007
6008 l_userid NUMBER;
6009 l_loginid NUMBER;
6010 l_pos_salary_flag VARCHAR2(1) := 'N';
6011 l_def_salary_flag VARCHAR2(1) := 'N';
6012
6013 CURSOR l_salary IS
6014 SELECT salary_flag
6015 FROM PSB_PAY_ELEMENTS ppay
6016 WHERE ppay.pay_element_id = l_pay_element_id;
6017
6018
6019 CURSOR l_get_pay_basis IS
6020 SELECT pay_basis
6021 FROM psb_position_assignments past
6022 WHERE past.assignment_type = 'ELEMENT'
6023 AND past.position_id = p_position_id
6024 AND past.pay_basis IS NOT NULL
6025 AND ROWNUM < 2;
6026
6027 l_count NUMBER;
6028 l_de_exists BOOLEAN := FALSE;
6029 l_element_id NUMBER;
6030
6031 CURSOR l_exists IS SELECT assignment_type,pay_element_id
6032 FROM psb_position_assignments
6033 WHERE (((p_assignment_type = 'ELEMENT') AND (p_assignment_type = assignment_type))
6034 OR ((p_assignment_type = 'ATTRIBUTE') AND (attribute_id = p_attribute_id))
6035 OR ((p_assignment_type = 'EMPLOYEE') AND (employee_id = p_employee_id)))
6036 AND data_extract_id = p_data_extract_id
6037 AND position_id = p_position_id and worksheet_id IS NULL;
6038
6039 CURSOR l_element IS SELECT pay_element_id,salary_flag
6040 FROM psb_pay_elements
6041 WHERE pay_element_id = l_element_id;
6042
6043
6044 BEGIN
6045 SAVEPOINT Apply_Position_Default_Rules;
6046
6047 l_userid := FND_GLOBAL.USER_ID;
6048 l_loginid := FND_GLOBAL.LOGIN_ID;
6049
6050
6051 IF p_assignment_type = 'ELEMENT' THEN
6052 l_pay_element_id := p_pay_element_id;
6053
6054 FOR l_Salary_Rec IN l_Salary
6055 LOOP
6056 l_def_salary_flag := l_Salary_Rec.salary_flag;
6057 END LOOP;
6058 END IF;
6059
6060 /* Bug 4545909 Start */
6061 FOR l_exists_rec IN l_exists
6062 LOOP
6063 IF l_exists_rec.assignment_type = 'ELEMENT' THEN
6064 l_element_id := l_exists_rec.pay_element_id;
6065 FOR l_element_rec in l_element
6066 LOOP
6067 IF l_element_rec.salary_flag = 'Y' and p_pay_basis IS NOT NULL THEN
6068 l_de_exists := TRUE;
6069 ELSIF l_element_rec.pay_element_id = p_pay_element_id THEN
6070 l_de_exists := TRUE;
6071 END IF;
6072 END LOOP;
6073 ELSE
6074 l_de_exists := TRUE;
6075 END IF;
6076 END LOOP;
6077 /* Bug 4545909 End */
6078
6079 -- following code processes overwrite default rules.
6080 IF p_modify_flag = 'Y' THEN
6081 -- bug 5002080 changed the set clause for modify_flag below
6082 UPDATE PSB_POSITION_ASSIGNMENTS
6083 SET attribute_value_id = DECODE(p_attribute_value_id, NULL, attribute_value_id, p_attribute_value_id),
6084 attribute_value = DECODE(p_attribute_value, NULL, attribute_value, p_attribute_value),
6085 pay_element_option_id = DECODE(p_pay_element_option_id, NULL, pay_element_option_id, p_pay_element_option_id),
6086 element_value_type = DECODE(p_element_value_type, NULL, element_value_type, p_element_value_type),
6087 element_value = DECODE(p_element_value, NULL, element_value, p_element_value),
6088 currency_code = DECODE(p_currency_code, NULL, currency_code, p_currency_code),
6089 pay_basis = DECODE(p_pay_basis, NULL, pay_basis, p_pay_basis),
6090 primary_employee_flag = DECODE(p_primary_employee_flag, NULL, primary_employee_flag, p_primary_employee_flag),
6091 global_default_flag = DECODE(p_global_default_flag, NULL, global_default_flag, p_global_default_flag),
6092 assignment_default_rule_id = DECODE(p_assignment_default_rule_id, NULL, assignment_default_rule_id, p_assignment_default_rule_id),
6093 modify_flag = DECODE(p_modify_flag, NULL, modify_flag, 'Y'),
6094 last_update_date = SYSDATE,
6095 last_updated_by = l_userid,
6096 last_update_login = l_loginid
6097 WHERE (((p_assignment_type = 'ELEMENT') AND (pay_element_id = p_pay_element_id))
6098 OR ((p_assignment_type = 'ATTRIBUTE') AND (attribute_id = p_attribute_id))
6099 OR ((p_assignment_type = 'EMPLOYEE') AND (employee_id = p_employee_id)))
6100 AND data_extract_id = p_data_extract_id
6101 AND position_id = p_position_id
6102 AND (worksheet_id = p_worksheet_id OR (worksheet_id IS NULL AND p_worksheet_id IS NULL)); -- bug 4545909
6103
6104
6105 IF SQL%NOTFOUND THEN
6106
6107
6108 l_pay_basis := p_pay_basis;
6109
6110 IF p_assignment_type = 'ELEMENT' THEN
6111
6112
6113 IF (l_def_salary_flag = 'Y') THEN
6114
6115 FOR l_assignment_rec IN (SELECT past.position_assignment_id
6116 FROM psb_position_assignments past ,
6117 psb_pay_elements ppay
6118 WHERE past.data_extract_id = p_data_extract_id
6119 AND ((worksheet_id IS NULL AND p_worksheet_id IS NULL)
6120 OR worksheet_id = p_worksheet_id)
6121 AND past.position_id = p_position_id
6122 AND past.pay_element_id = ppay.pay_element_id
6123 AND past.assignment_type = 'ELEMENT'
6124 AND ppay.salary_flag = 'Y'
6125 AND(((p_effective_end_date IS NOT NULL)
6126 AND (((past.effective_start_date <= p_effective_end_date)
6127 AND (past.effective_end_date IS NULL))
6128 OR ((past.effective_start_date BETWEEN p_effective_start_date AND p_effective_end_date)
6129 OR (past.effective_end_date BETWEEN p_effective_start_date AND p_effective_end_date)
6130 OR ((past.effective_start_date < p_effective_start_date)
6131 AND (past.effective_end_date > p_effective_end_date)))))
6132 OR ((p_effective_end_date IS NULL)
6133 AND (NVL(past.effective_end_date, p_effective_start_date) >= p_effective_start_date)))
6134 )
6135 LOOP
6136
6137 PSB_POSITION_ASSIGNMENTS_PVT.delete_row
6138 (p_api_version => 1.0,
6139 p_return_status => l_return_status,
6140 p_msg_count => l_msg_count,
6141 p_msg_data => l_msg_data,
6142 p_position_assignment_id => l_assignment_rec.position_assignment_id );
6143 END LOOP;
6144
6145
6146 IF (p_pay_basis IS NULL) THEN
6147
6148 FOR l_Get_Pay_Basis_Rec IN l_Get_Pay_Basis LOOP
6149 l_pay_basis := l_Get_Pay_Basis_Rec.pay_basis;
6150 END LOOP;
6151
6152 END IF;
6153
6154 END IF;
6155 END IF;
6156
6157 -- Bug 4545909 added the following IF clause
6158 -- the first insert_row call create the worksheet specific record
6159 -- the second insert_row call create the extract specific record
6160 IF l_de_exists THEN
6161
6162 PSB_POSITION_ASSIGNMENTS_PVT.Insert_Row
6163 (p_api_version => 1.0,
6164 p_return_status => l_return_status,
6165 p_msg_count => l_msg_count,
6166 p_msg_data => l_msg_data,
6167 p_rowid => l_rowid,
6168 p_position_assignment_id => l_position_assignment_id,
6169 p_data_extract_id => p_data_extract_id,
6170 p_worksheet_id => p_worksheet_id,
6171 p_position_id => p_position_id,
6172 p_assignment_type => p_assignment_type,
6173 p_attribute_id => p_attribute_id,
6174 p_attribute_value_id => p_attribute_value_id,
6175 p_attribute_value => p_attribute_value,
6176 p_pay_element_id => p_pay_element_id,
6177 p_pay_element_option_id => p_pay_element_option_id,
6178 p_effective_start_date => p_effective_start_date,
6179 p_effective_end_date => p_effective_end_date,
6180 p_element_value_type => p_element_value_type,
6181 p_element_value => p_element_value,
6182 p_currency_code => p_currency_code,
6183 p_pay_basis => l_pay_basis,
6184 p_employee_id => p_employee_id,
6185 p_primary_employee_flag => p_primary_employee_flag,
6186 p_global_default_flag => p_global_default_flag,
6187 p_assignment_default_rule_id => p_assignment_default_rule_id,
6188 p_modify_flag => p_modify_flag,
6189 p_mode => p_mode);
6190
6191 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
6192 RAISE FND_API.G_EXC_ERROR;
6193 END IF;
6194
6195 ELSE
6196
6197 PSB_POSITION_ASSIGNMENTS_PVT.Insert_Row
6198 (p_api_version => 1.0,
6199 p_return_status => l_return_status,
6200 p_msg_count => l_msg_count,
6201 p_msg_data => l_msg_data,
6202 p_rowid => l_rowid,
6203 p_position_assignment_id => l_position_assignment_id,
6204 p_data_extract_id => p_data_extract_id,
6205 p_worksheet_id => NULL,
6206 p_position_id => p_position_id,
6207 p_assignment_type => p_assignment_type,
6208 p_attribute_id => p_attribute_id,
6209 p_attribute_value_id => p_attribute_value_id,
6210 p_attribute_value => p_attribute_value,
6211 p_pay_element_id => p_pay_element_id,
6212 p_pay_element_option_id => p_pay_element_option_id,
6213 p_effective_start_date => p_effective_start_date,
6214 p_effective_end_date => p_effective_end_date,
6215 p_element_value_type => p_element_value_type,
6216 p_element_value => p_element_value,
6217 p_currency_code => p_currency_code,
6218 p_pay_basis => l_pay_basis,
6219 p_employee_id => p_employee_id,
6220 p_primary_employee_flag => p_primary_employee_flag,
6221 p_global_default_flag => p_global_default_flag,
6222 p_assignment_default_rule_id => p_assignment_default_rule_id,
6223 p_modify_flag => p_modify_flag,
6224 p_mode => p_mode);
6225
6226 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
6227 RAISE FND_API.G_EXC_ERROR;
6228 END IF;
6229 END IF;
6230
6231 END IF;
6232
6233 ELSE
6234
6235 l_matching_assmt := FALSE;
6236
6237 FOR l_pos_assignment_rec IN( SELECT *
6238 FROM psb_position_assignments past
6239 WHERE past.data_extract_id = p_data_extract_id
6240 AND past.position_id = p_position_id
6241 AND ((worksheet_id IS NULL AND NOT EXISTS
6242 (SELECT 1 FROM psb_position_assignments ppa
6243 WHERE ppa.worksheet_id = p_worksheet_id
6244 AND ppa.position_id = p_position_id AND
6245 (p_assignment_type = 'ATTRIBUTE' AND past.attribute_id = ppa.attribute_id) OR
6246 (p_assignment_type = 'ELEMENT' AND past.pay_element_id = ppa.pay_element_id)))
6247 OR worksheet_id = p_worksheet_id
6248 OR (worksheet_id IS NULL AND p_worksheet_id IS NULL))
6249 AND (((p_effective_end_date IS NOT NULL)
6250 AND (((past.effective_start_date <= p_effective_end_date)
6251 AND (past.effective_end_date IS NULL))
6252 OR ((past.effective_start_date BETWEEN p_effective_start_date AND p_effective_end_date)
6253 OR (past.effective_end_date BETWEEN p_effective_start_date AND p_effective_end_date)
6254 OR ((past.effective_start_date < p_effective_start_date)
6255 AND (past.effective_end_date > p_effective_end_date)))))
6256 OR ((p_effective_end_date IS NULL)
6257 AND (NVL(past.effective_end_date, p_effective_start_date) >= p_effective_start_date)))
6258 )
6259
6260 LOOP
6261 l_pos_salary_flag := 'N';
6262
6263 IF l_pos_assignment_rec.assignment_type = 'ELEMENT' THEN
6264
6265 l_pay_element_id := l_pos_assignment_rec.pay_element_id;
6266
6267 FOR l_Salary_Rec IN l_Salary
6268 LOOP
6269 l_pos_salary_flag := l_Salary_Rec.salary_flag;
6270 END LOOP;
6271 END IF;
6272
6273
6274
6275 IF (p_assignment_type = 'ATTRIBUTE' AND p_attribute_id = l_pos_assignment_rec.attribute_id) OR
6276 (p_assignment_type = 'ELEMENT' AND p_pay_element_id = l_pos_assignment_rec.pay_element_id) OR
6277 (p_assignment_type = 'ELEMENT' AND p_assignment_type = l_pos_assignment_rec.assignment_type
6278 AND l_pos_salary_flag= 'Y' AND l_def_salary_flag= 'Y') THEN
6279
6280
6281 l_matching_assmt := TRUE;
6282
6283 END IF;
6284
6285 END LOOP;
6286
6287
6288 IF l_matching_assmt <> TRUE THEN
6289
6290 -- Bug 4545909. added the following IF clause
6291 IF l_de_exists THEN
6292 PSB_POSITION_ASSIGNMENTS_PVT.Insert_Row
6293 (p_api_version => 1.0,
6294 p_return_status => l_return_status,
6295 p_msg_count => l_msg_count,
6296 p_msg_data => l_msg_data,
6297 p_rowid => l_rowid,
6298 p_position_assignment_id => l_position_assignment_id,
6299 p_data_extract_id => p_data_extract_id,
6300 p_worksheet_id => p_worksheet_id,
6301 p_position_id => p_position_id,
6302 p_assignment_type => p_assignment_type,
6303 p_attribute_id => p_attribute_id,
6304 p_attribute_value_id => p_attribute_value_id,
6305 p_attribute_value => p_attribute_value,
6306 p_pay_element_id => p_pay_element_id,
6307 p_pay_element_option_id => p_pay_element_option_id,
6308 p_effective_start_date => p_effective_start_date,
6309 p_effective_end_date => p_effective_end_date,
6310 p_element_value_type => p_element_value_type,
6311 p_element_value => p_element_value,
6312 p_currency_code => p_currency_code,
6313 p_pay_basis => l_pay_basis,
6314 p_employee_id => p_employee_id,
6315 p_primary_employee_flag => p_primary_employee_flag,
6316 p_global_default_flag => p_global_default_flag,
6317 p_assignment_default_rule_id => p_assignment_default_rule_id,
6318 -- p_modify_flag => p_modify_flag,
6319 p_modify_flag => 'Y', -- bug 5002080
6320 p_mode => p_mode);
6321 ELSE
6322 PSB_POSITION_ASSIGNMENTS_PVT.Insert_Row
6323 (p_api_version => 1.0,
6324 p_return_status => l_return_status,
6325 p_msg_count => l_msg_count,
6326 p_msg_data => l_msg_data,
6327 p_rowid => l_rowid,
6328 p_position_assignment_id => l_position_assignment_id,
6329 p_data_extract_id => p_data_extract_id,
6330 p_worksheet_id => NULL,
6331 p_position_id => p_position_id,
6332 p_assignment_type => p_assignment_type,
6333 p_attribute_id => p_attribute_id,
6334 p_attribute_value_id => p_attribute_value_id,
6335 p_attribute_value => p_attribute_value,
6336 p_pay_element_id => p_pay_element_id,
6337 p_pay_element_option_id => p_pay_element_option_id,
6338 p_effective_start_date => p_effective_start_date,
6339 p_effective_end_date => p_effective_end_date,
6340 p_element_value_type => p_element_value_type,
6341 p_element_value => p_element_value,
6342 p_currency_code => p_currency_code,
6343 p_pay_basis => l_pay_basis,
6344 p_employee_id => p_employee_id,
6345 p_primary_employee_flag => p_primary_employee_flag,
6346 p_global_default_flag => p_global_default_flag,
6347 p_assignment_default_rule_id => p_assignment_default_rule_id,
6348 -- p_modify_flag => p_modify_flag,
6349 p_modify_flag => 'Y', -- bug 5002080
6350 p_mode => p_mode);
6351 END IF;
6352
6353 END IF;
6354
6355 END IF;
6356
6357 IF FND_API.to_Boolean (p_commit) THEN
6358 COMMIT WORK;
6359 END IF;
6360
6361
6362 -- Initialize API return status to success
6363
6364 x_return_status := FND_API.G_RET_STS_SUCCESS;
6365
6366
6367 -- Standard call to get message count and if count is 1, get message info
6368
6369 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,
6370 p_data => x_msg_data);
6371
6372 EXCEPTION
6373
6374 WHEN FND_API.G_EXC_ERROR THEN
6375 ROLLBACK TO Apply_Position_Default_Rules;
6376 x_return_status := FND_API.G_RET_STS_ERROR;
6377
6378 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,
6379 p_data => x_msg_data);
6380
6381 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
6382 ROLLBACK TO Apply_Position_Default_Rules;
6383 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
6384
6385 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,
6386 p_data => x_msg_data);
6387
6388 WHEN OTHERS THEN
6389 ROLLBACK TO Apply_Position_Default_Rules;
6390 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
6391
6392 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
6393
6394 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
6395 l_api_name);
6396 END IF;
6397 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,
6398 p_data => x_msg_data);
6399
6400 End Apply_Position_Default_Rules;
6401 /* Bug 1308558 End */
6402
6403 END PSB_POSITIONS_PVT ;