[Home] [Help]
PACKAGE BODY: APPS.HR_MAINTAIN_PROPOSAL_API
Source
1 Package Body hr_maintain_proposal_api as
2 /* $Header: hrpypapi.pkb 120.47 2011/08/16 12:36:25 akaranam ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' hr_maintain_proposal_api.'; -- Global package name
9
10 function get_dt_mode(p_session_date in date,
11 p_element_entry_id in number)
12 RETURN varchar2
13
14 IS
15
16 l_dtmode varchar2(30);
17
18 begin
19
20 l_dtmode := PQH_GSP_POST_PROCESS.DT_Mode
21 (P_EFFECTIVE_DATE => p_session_date
22 ,P_BASE_TABLE_NAME => 'PAY_ELEMENT_ENTRIES_F'
23 ,P_BASE_KEY_COLUMN => 'ELEMENT_ENTRY_ID'
24 ,P_BASE_KEY_VALUE => p_element_entry_id);
25
26 return l_dtmode;
27 end get_dt_mode;
28
29 --------vkodedal 7-mar-07
30 -----------------------------------------------------------------------------
31 -- | ---------------------------< update_last_change_date>-----------------
32 -----------------------------------------------------------------------------
33 --
34 -- Description
35 -- This procedure is used to correct the last_change_date of the next record with the current change date.
36 -- This procedure is called when a record is inserted, or deleted so that correct last_change_date is maintained
37 -- in the immediate next proposal.
38 -- Assignment Id and Change date of the inserted or change date of the proposal previous to the deleted proposal
39 -- are passed as in parameters.
40 --
41 Procedure update_last_change_date(p_assignment_id in number
42 ,p_change_date in date) is
43
44 cursor csr_next_proposal is
45 select pay_proposal_id, rowid
46 from per_pay_proposals
47 where assignment_id = p_assignment_id
48 and change_date=(
49 select min(change_date)
50 from per_pay_proposals
51 where assignment_id = p_assignment_id
52 and change_date > p_change_date);
53
54 l_last_change_date per_pay_proposals.last_change_date%TYPE;
55 l_pay_proposal_id per_pay_proposals.pay_proposal_id%TYPE;
56 l_row_id rowid;
57 l_proc varchar2(30):= 'update_last_change_date';
58
59 begin
60
61 hr_utility.set_location('Entering:'||l_proc,5);
62 hr_utility.set_location('p_assignment_id:'||p_assignment_id,15);
63 hr_utility.set_location('p_change_date:'||p_change_date,25);
64
65 l_last_change_date:=p_change_date;
66
67 OPEN csr_next_proposal;
68 FETCH csr_next_proposal into l_pay_proposal_id,l_row_id;
69
70 hr_utility.set_location('l_pay_proposal_id:'||l_pay_proposal_id,15);
71
72 if csr_next_proposal%FOUND then
73 hr_utility.set_location('Cursor found:',25);
74
75 --added by schowdhu to fix 7205132
76
77 update per_pay_proposals
78 set last_change_date = l_last_change_date,
79 last_updated_by = fnd_global.user_id,
80 last_update_login = fnd_global.login_id
81 where rowid=l_row_id;
82
83 -- commented out by schowdhu to fix 7205132
84 /*
85 update per_pay_proposals
86 set last_change_date = l_last_change_date
87 where rowid=l_row_id;
88 */
89 hr_utility.set_location('Updated successfuly:',35);
90 end if;
91
92 CLOSE csr_next_proposal;
93
94 hr_utility.set_location('Leaving:'||l_proc,5);
95
96 EXCEPTION
97 When others then
98 --
99 -- An unexpected error has occured
100 --
101 hr_utility.set_location('When Others:'||l_proc,5);
102 raise;
103 --
104 end update_last_change_date;
105 --
106 --
107 /* Procedure added to enddate proposed proposals only. Bug#7386307 by schowdhu */
108
109 Procedure end_date_proposed_proposal(p_assignment_id in number
110 ,p_date_to in date) is
111
112 Cursor csr_prev_prop_details
113 is
114 select pay_proposal_id,date_to
115 from per_pay_proposals
116 where assignment_id = p_assignment_id
117 and change_date =(select max(change_date)
118 from per_pay_proposals
119 where assignment_id = p_assignment_id
120 and change_date < p_date_to+1 and approved = 'N');
121
122 l_date_to per_pay_proposals.date_to%TYPE;
123 l_pay_proposal_id per_pay_proposals.pay_proposal_id%TYPE;
124 l_proc varchar2(30):= 'end_date_proposed_proposal';
125
126 begin
127
128 hr_utility.set_location('Entering:'||l_proc,5);
129 hr_utility.set_location('p_assignment_id:'||p_assignment_id,15);
130 hr_utility.set_location('p_date_to:'||p_date_to,25);
131
132 OPEN csr_prev_prop_details;
133 FETCH csr_prev_prop_details into l_pay_proposal_id,l_date_to;
134
135 hr_utility.set_location('l_pay_proposal_id:'||l_pay_proposal_id,15);
136 hr_utility.set_location('l_date_to:'||l_date_to,25);
137
138 if csr_prev_prop_details%FOUND then
139 hr_utility.set_location('Cursor found:',35);
140 if l_date_to is null OR l_date_to > p_date_to then
141 hr_utility.set_location('About to update',45);
142 update per_pay_proposals
143 set date_to = p_date_to,
144 -- added by vkodedal fix for 6831216
145 last_update_date = sysdate,
146 last_updated_by = fnd_global.user_id,
147 last_update_login = fnd_global.login_id
148 -- end of fix
149 where assignment_id = p_assignment_id
150 and pay_proposal_id = l_pay_proposal_id;
151 hr_utility.set_location('Updated successfuly:',85);
152 end if;
153 end if;
154 CLOSE csr_prev_prop_details;
155
156 hr_utility.set_location('Leaving:'||l_proc,5);
157
158 end end_date_proposed_proposal;
159 --
160 --
161
162 /* Procedure added to enddate approved proposals only. Bug#7386307 by schowdhu */
163
164 -- changed by schowdhu for 7673294 05-jan-08
165
166 Procedure end_date_approved_proposal(p_assignment_id in number
167 ,p_date_to in date
168 ,p_proposal_id in number default NULL) is
169
170 Cursor csr_prev_prop_details
171 is
172 select pay_proposal_id, date_to
173 from per_pay_proposals
174 where assignment_id = p_assignment_id
175 and change_date =(select max(change_date)
176 from per_pay_proposals
177 where assignment_id = p_assignment_id
178 and change_date < p_date_to+1 and approved = 'Y');
179
180 -- cursor added to find the proposed proposals to be deleted
181 -- by schowdhu Bug #7386307
182
183 cursor get_all_proposed_proposals
184 is
185 select pay_proposal_id,object_version_number, business_group_id
186 from per_pay_proposals
187 where assignment_id = p_assignment_id
188 --added for the bug 7673294 to exclude the calling proposal
189 and (p_proposal_id IS NULL OR pay_proposal_id <> p_proposal_id)
190 and change_date < p_date_to+1
191 and approved = 'N';
192
193 l_date_to per_pay_proposals.date_to%TYPE;
194 l_pay_proposal_id per_pay_proposals.pay_proposal_id%TYPE;
195 l_proc varchar2(30):= 'end_date_approved_proposal';
196 l_change_date per_pay_proposals.change_date%TYPE;
197 l_del_warn boolean;
198
199 begin
200
201 hr_utility.set_location('Entering:'||l_proc,5);
202 hr_utility.set_location('p_assignment_id:'||p_assignment_id,15);
203 hr_utility.set_location('p_date_to:'||p_date_to,25);
204
205 OPEN csr_prev_prop_details;
206 FETCH csr_prev_prop_details into l_pay_proposal_id,l_date_to;
207
208 if csr_prev_prop_details%FOUND then
209 hr_utility.set_location('Cursor found:',35);
210 if l_date_to is null OR l_date_to > p_date_to then
211 hr_utility.set_location('About to update',45);
212 update per_pay_proposals
213 set date_to = p_date_to,
214 -- added by vkodedal fix for 6831216
215 last_update_date = sysdate,
216 last_updated_by = fnd_global.user_id,
217 last_update_login = fnd_global.login_id
218 -- end of fix
219 where assignment_id = p_assignment_id
220 and pay_proposal_id = l_pay_proposal_id;
221 hr_utility.set_location('Updated successfuly:',85);
222 end if;
223 end if;
224 CLOSE csr_prev_prop_details;
225
226 -- This condition is added to delete the proposed proposals in case it is not yet done from
227 -- the OA layer. schowdhu - 01-Dec-2008
228 if( HR_MAINTAIN_PROPOSAL_API.g_deleted_from_oa = 'N')
229 then
230 hr_utility.set_location('Within delete from OA',90);
231 -- now delete all the inactivated proposed proposals. Bug#7386307 by schowdhu
232 for a in get_all_proposed_proposals loop
233 hr_maintain_proposal_api.delete_salary_proposal
234 (p_pay_proposal_id => a.pay_proposal_id
235 ,p_business_group_id => a.business_group_id
236 ,p_object_version_number => a.object_version_number
237 ,p_salary_warning => l_del_warn);
238 end loop;
239 end if;
240
241 hr_utility.set_location('Leaving:'||l_proc,100);
242
243 end end_date_approved_proposal;
244 --
245 --
246
247 --
248 -----------------------------------------------------------------------------
249 -- | ---------------------------< end_date_salary_proposal>-----------------
250 -----------------------------------------------------------------------------
251 --
252 /* Procedure modified. Bug#7386307 by schowdhu */
253
254 -- changed by schowdhu for 7673294 05-jan-08
255 Procedure end_date_salary_proposal(p_assignment_id in number
256 ,p_date_to in date
257 ,p_proposal_id in number default NULL) is
258 /*
259 Cursor csr_prev_prop_details
260 is
261 select pay_proposal_id,date_to
262 from per_pay_proposals
263 where assignment_id = p_assignment_id
264 and change_date =(select max(change_date)
265 from per_pay_proposals
266 where assignment_id = p_assignment_id
267 and change_date < p_date_to+1);
268
269 l_date_to per_pay_proposals.date_to%TYPE;
270 l_pay_proposal_id per_pay_proposals.pay_proposal_id%TYPE;
271 */
272 l_proc varchar2(30):= 'end_date_salary_proposal';
273 l_approved per_pay_proposals.approved%TYPE;
274 -- changed by schowdhu for 7673294 05-jan-08
275
276 Cursor chk_approved_flg
277 is
278 select approved
279 from per_pay_proposals
280 where pay_proposal_id = nvl(p_proposal_id, pay_proposal_id)
281 and assignment_id = p_assignment_id
282 and change_date = p_date_to +1 ;
283 begin
284
285 hr_utility.set_location('Entering:'||l_proc,5);
286 hr_utility.set_location('p_assignment_id:'||p_assignment_id,15);
287 hr_utility.set_location('p_date_to:'||p_date_to,25);
288 /*
289 OPEN csr_prev_prop_details;
290 FETCH csr_prev_prop_details into l_pay_proposal_id,l_date_to;
291
292 hr_utility.set_location('l_pay_proposal_id:'||l_pay_proposal_id,15);
293 hr_utility.set_location('l_date_to:'||l_date_to,25);
294
295
296 if csr_prev_prop_details%FOUND then
297 hr_utility.set_location('Cursor found:',35);
298 if l_date_to is null OR l_date_to > p_date_to then
299 hr_utility.set_location('About to update',45);
300 update per_pay_proposals
301 set date_to = p_date_to,
302 -- added by vkodedal fix for 6831216
303 last_update_date = sysdate,
304 last_updated_by = fnd_global.user_id,
305 last_update_login = fnd_global.login_id
306 -- end of fix
307 where assignment_id = p_assignment_id
308 and pay_proposal_id = l_pay_proposal_id;
309 hr_utility.set_location('Updated successfuly:',85);
310 end if;
311 end if;
312 CLOSE csr_prev_prop_details;
313 */
314 OPEN chk_approved_flg;
315 FETCH chk_approved_flg into l_approved;
316 if l_approved = 'N' then
317 end_date_proposed_proposal (p_assignment_id, p_date_to);
318 else
319 -- changed by schowdhu for 7673294 05-jan-08
320 end_date_approved_proposal (p_assignment_id, p_date_to, p_proposal_id);
321 end if;
322 CLOSE chk_approved_flg;
323
324 hr_utility.set_location('Leaving:'||l_proc,5);
325
326 end end_date_salary_proposal;
327
328
329
330 Procedure extend_salary_proposal_to_date(p_pay_proposal_id in number) is
331 --
332 cursor c_pay_proposal is
333 select assignment_id, change_date, date_to,last_change_date, approved
334 from per_pay_proposals
335 where pay_proposal_id = p_pay_proposal_id;
336 --
337 cursor c_asg_pay_basis_id(p_assignment_id number, p_date date) is
338 select pay_basis_id
339 from per_all_assignments_f
340 where assignment_id = p_assignment_id
341 and p_date between effective_start_date and effective_end_date;
342 --
343 l_proc varchar2(72) := g_package||'extend_salary_proposal_to_date';
344 l_assignment_id number;
345 l_deleted_proposal_date date;
346 l_date_to date;
347 l_last_change_date date;
348 l_curr_pay_basis_id number;
349 l_prev_pay_basis_id number;
350 l_approved per_pay_proposals.approved%TYPE;
351 --
352 begin
353 --
354
355 hr_utility.set_location('Entering:'||l_proc,5);
356 --
357 if (p_pay_proposal_id is not null)
358 then
359 open c_pay_proposal;
360 fetch c_pay_proposal into l_assignment_id, l_deleted_proposal_date, l_date_to,l_last_change_date, l_approved;
361 close c_pay_proposal;
362
363 open c_asg_pay_basis_id(l_assignment_id , l_deleted_proposal_date);
364 fetch c_asg_pay_basis_id into l_curr_pay_basis_id;
365 close c_asg_pay_basis_id;
366 --
367 hr_utility.set_location('Current pay_basis_id:'||l_curr_pay_basis_id,10);
368 --
369 --
370 open c_asg_pay_basis_id(l_assignment_id , l_deleted_proposal_date-1);
371 fetch c_asg_pay_basis_id into l_prev_pay_basis_id;
372 close c_asg_pay_basis_id;
373 --
374 hr_utility.set_location('Previous pay_basis_id:'||l_prev_pay_basis_id,20);
375 --
376
377 --
378 -- if pay_basis_id for the assignment is different don't extend
379 --
380 if (l_curr_pay_basis_id is null or l_prev_pay_basis_id is null
381 or l_curr_pay_basis_id<>l_prev_pay_basis_id) then
382 --
383 hr_utility.set_location('Exiting1:'||l_proc,50);
384 --
385 return;
386 end if;
387 /* changed to adjust the date_to after deletion of a proposal. Bug#7386307 by schowdhu */
388 if (l_approved = 'N') then
389
390 update per_pay_proposals
391 set date_to = l_date_to,
392 -- added by vkodedal fix for 6831216
393 last_update_date = sysdate,
394 last_updated_by = fnd_global.user_id,
395 last_update_login = fnd_global.login_id
396 -- end of fix
397 where assignment_id = l_assignment_id
398 and (l_deleted_proposal_date -1) between change_date and date_to
399 and approved = 'N';
400 end if;
401
402 if (l_approved = 'Y') then
403
404 update per_pay_proposals
405 set date_to = l_date_to,
406 last_update_date = sysdate,
407 last_updated_by = fnd_global.user_id,
408 last_update_login = fnd_global.login_id
409 where assignment_id = l_assignment_id
410 and approved = 'Y'
411 and change_date = ( select max(change_date)
412 from per_pay_proposals
413 where assignment_id = l_assignment_id
414 and change_date < l_deleted_proposal_date
415 and approved = 'Y' );
416
417 end if;
418 /*
419 update per_pay_proposals
420 set last_change_date = l_last_change_date
421 where assignment_id = l_assignment_id
422 and change_date =
423 (select min(t.change_date)
424 from per_pay_proposals t
425 where t.assignment_id = l_assignment_id
426 and t.change_date > l_deleted_proposal_date
427 );
428 */
429
430 end if;
431 --
432 hr_utility.set_location('Exiting2:'||l_proc,50);
433
434 --
435 end;
436
437
438 --
439 -- ----------------------------------------------------------------------------
440 -- |-------------------------< maintain_elements >-----------------------------|
441 -- ----------------------------------------------------------------------------
442 -- {Start Of Comments}
443 --
444 -- Description:
445 -- This business support process keeps track of the element entries of
446 -- a proposal, altering them as neccesary.
447 --
448 -- Pre Conditions:
449 --
450 -- In Parameters:
451 -- p_pay_proposal_id
452 -- The pay_proposal_id of the proposal to be altered
453 -- p_assignment_id
454 -- The assignment_id of the proposal to be altered
455 -- p_change_date
456 -- The date on which the change take effect
457 -- p_element_entry_id
458 -- The element entry id of the maintaind element
459 -- p_proposed_salary
460 -- The salary to give the element
461 --
462 -- Post Success:
463 -- An element will be inserted or changed without being committed
464 --
465 -- Post Failure:
466 -- If an error has occurred, an error message will be supplied with the work
467 -- rolled back.
468 --
469 -- Developer Implementation Notes:
470 -- this is only for calls from the insert_proposal_api
471 -- and update_proposal_api
472 --
473 -- Access Status:
474 -- Internal Development Use Only.
475 --
476 -- {End Of Comments}
477 -- ---------------------------------------------------------------------------
478 --
479 Procedure maintain_elements_bsp(p_pay_proposal_id in number
480 ,p_assignment_id in number
481 ,p_change_date in date
482 ,p_date_to in date
483 ,p_element_entry_id in out nocopy number
484 ,p_proposed_salary in number) is
485 --
486 l_element_type_id pay_element_types_f.element_type_id%TYPE;
487 l_input_value_id pay_input_values_f.input_value_id%TYPE;
488 l_element_link_id pay_element_links_f.element_link_id%TYPE;
489 l_effective_end_date date;
490 l_effective_start_date date := p_change_date;
491 l_first_date date;
492 l_last_date date;
493 l_date_to date;
494 l_pay_annualization_factor NUMBER;
495 l_payroll_annualization_factor NUMBER;
496 l_entry_value NUMBER;
497 l_proc varchar2(72) := g_package||'maintain_elements_bsp';
498 l_dummy number;
499 l_dummy_v varchar2(300);
500 l_entry_value2 NUMBER;
501 l_input_value_id2 pay_input_values_f.input_value_id%TYPE;
502 l_payroll_value VARCHAR2(80);
503 l_next_change_date per_pay_proposals.change_date%TYPE;
504 l_Entry_End_Date pay_element_entries_f.effective_end_date%TYPE;
505 l_pay_basis per_pay_bases.pay_basis%TYPE;
506 --
507 --
508 cursor csr_get_element_detail is
509 select pet.element_type_id,
510 piv.input_value_id,
511 ppb.pay_annualization_factor,
512 ppb.pay_basis
513 from
514 pay_element_types_f pet,
515 pay_input_values_f piv,
516 per_pay_bases ppb,
517 per_all_assignments_f asg
518
519 where
520 pet.element_type_id = piv.element_type_id
521 and p_change_date BETWEEN pet.effective_start_date
522 and pet.effective_end_date
523 and piv.input_value_id = ppb.input_value_id
524 and p_change_date BETWEEN piv.effective_start_date
525 AND piv.effective_end_date
526 and ppb.pay_basis_id = asg.pay_basis_id
527 and asg.assignment_id = p_assignment_id
528 and p_change_date BETWEEN asg.effective_start_date
529 AND asg.effective_end_date;
530 --
531 cursor get_first_date is
532 select min(effective_start_date)
533 from pay_element_entries_f
534 where element_entry_id=p_element_entry_id;
535
536 Cursor get_last_date
537 is
538 select max(effective_end_date)
539 from pay_element_entries_f
540 where element_entry_id=p_element_entry_id;
541 --
542 cursor get_existing_date is
543 select 1
544 from pay_element_entries_f
545 where effective_start_date=p_change_date
546 and element_entry_id=p_element_entry_id;
547 --
548 cursor get_payroll_element(p_element_type_id NUMBER) is
549 select piv.input_value_id
550 from pay_input_values_f piv
551 , hr_lookups hrl
552 where piv.element_type_id=p_element_type_id
553 and piv.name=hrl.meaning
554 and p_change_date between
555 piv.effective_start_date
556 and piv.effective_end_date
557 and hrl.lookup_type='NAME_TRANSLATIONS'
558 and hrl.lookup_code='PAYROLL_VALUE'
559 and p_change_date between
560 nvl(hrl.start_date_active,p_change_date)
561 and nvl(hrl.end_date_active,p_change_date);
562
563 cursor Csr_Entry_End_Date is
564 select effective_end_date
565 from pay_element_entries_f
566 where effective_start_date=p_change_date
567 and element_entry_id=p_element_entry_id;
568 --
569 Cursor get_next_approved_change_date
570 IS
571 select min(change_date)
572 from per_pay_proposals
573 where assignment_id = p_assignment_id
574 and change_date > p_change_date;
575
576 Cursor csr_element_entries
577 IS
578 select element_entry_id,effective_start_date,effective_end_date
579 from pay_element_entries_f
580 where effective_end_date between p_change_date and l_date_to-1
581 and element_entry_id = p_element_entry_id;
582
583 --
584 --
585 begin
586 --
587 hr_utility.set_location('Entering:'||l_proc,5);
588 l_date_to := p_date_to;
589
590 open csr_get_element_detail;
591 fetch csr_get_element_detail into l_element_type_id,
592 l_input_value_id,
593 l_pay_annualization_factor,
594 l_pay_basis;
595 if csr_get_element_detail%notfound then
596 hr_utility.set_location(l_proc,10);
597 close csr_get_element_detail;
598 hr_utility.set_message(801,'HR_289855_SAL_ASS_NOT_SAL_ELIG');
599 hr_utility.raise_error;
600 else
601 close csr_get_element_detail;
602 per_pay_proposals_populate.get_payroll(p_assignment_id
603 ,p_change_date
604 ,l_dummy_v
605 ,l_payroll_annualization_factor);
606 --
607 hr_utility.set_location(l_proc,15);
608 --
609 if(l_pay_annualization_factor is null and l_pay_basis='PERIOD') then
610 l_pay_annualization_factor:=l_payroll_annualization_factor;
611 end if;
612 l_entry_value:=p_proposed_salary;
613 l_entry_value2:=p_proposed_salary
614 *l_pay_annualization_factor
615 /l_payroll_annualization_factor;
616 --
617 if (l_element_type_id IS NULL OR l_input_value_id IS NULL)
618 then
619 hr_utility.set_location(l_proc,20);
620 hr_utility.set_message(801,'HR_289855_SAL_ASS_NOT_SAL_ELIG');
621 hr_utility.raise_error;
622 else
623 --
624 -- get the payroll element if it exists
625 --
626 open get_payroll_element(l_element_type_id);
627 fetch get_payroll_element into l_input_value_id2;
628 if(get_payroll_element%notfound) then
629 hr_utility.set_location(l_proc,25);
630 close get_payroll_element;
631 l_input_value_id2:=null;
632 l_entry_value2:=null;
633 else
634 close get_payroll_element;
635 end if;
636 --
637 if (p_element_entry_id is null) THEN
638 -- this must be a new entry so do the inserting of the element
639 --
640 hr_utility.set_location(l_proc,30);
641 l_element_link_id := hr_entry_api.get_link
642 (p_assignment_id
643 ,l_element_type_id
644 ,p_change_date);
645 if l_element_link_id IS NULL then
646 hr_utility.set_location(l_proc,35);
647 hr_utility.set_message(801,'HR_13016_SAL_ELE_NOT_ELIG');
648 hr_utility.raise_error;
649 end if;
650 hr_utility.set_location(l_proc,40);
651 open get_existing_date;
652 fetch get_existing_date into l_dummy;
653 if(get_existing_date%found) then
654 close get_existing_date;
655 hr_utility.set_location(l_proc,45);
656 hr_utility.set_message(801,'HR_13003_SAL_SAL_CHANGE_EXISTS');
657 hr_utility.raise_error;
658 else
659 close get_existing_date;
660 --
661 -- Now we insert an element entry for this proposal
662 -- by calling the insert_element_entry_api.
663 --
664 hr_utility.set_location(l_proc,50);
665 hr_entry_api.insert_element_entry
666 (p_effective_start_date =>l_effective_start_date
667 ,p_effective_end_date => l_effective_end_date
668 ,p_element_entry_id => p_element_entry_id
669 ,p_assignment_id => p_assignment_id
670 ,p_element_link_id => l_element_link_id
671 ,p_creator_type => 'SP'
672 ,p_entry_type => 'E'
673 ,p_creator_id => p_pay_proposal_id
674 ,p_input_value_id1 => l_input_value_id
675 ,p_entry_value1 => l_entry_value
676 ,p_input_value_id2 => l_input_value_id2
677 ,p_entry_value2 => l_entry_value2
678 );
679 end if;
680
681 else
682 hr_utility.set_location(l_proc,65);
683 hr_entry_api.update_element_entry
684 (p_dt_update_mode => get_dt_mode(p_change_date,p_element_entry_id)
685 ,p_session_date => p_change_date
686 ,p_check_for_update => 'Y'
687 ,p_element_entry_id => p_element_entry_id
688 ,p_creator_type => 'SP'
689 ,p_creator_id => p_pay_proposal_id
690 ,p_input_value_id1 => l_input_value_id
691 ,p_entry_value1 => l_entry_value
692 ,p_input_value_id2 => l_input_value_id2
693 ,p_entry_value2 => l_entry_value2
694 );
695 end if;
696
697 hr_utility.set_location('Effective End Date of the element entry:'||l_effective_end_date,69);
698
699 OPEN get_last_date;
700 FETCH get_last_date INTO l_last_date;
701 CLOSE get_last_date;
702
703 IF l_date_to > l_last_date THEN
704
705 UPDATE per_pay_proposals
706 SET date_to = l_last_date
707 WHERE pay_proposal_id = p_pay_proposal_id;
708
709 l_date_to := l_last_date;
710
711 END IF;
712
713 OPEN Csr_Entry_End_Date;
714 FETCH Csr_Entry_End_Date into l_Entry_End_Date;
715 CLOSE Csr_Entry_End_Date;
716
717 IF l_date_to > l_Entry_End_Date THEN
718 FOR i in csr_element_entries LOOP
719 hr_utility.set_location('Inside the loop'||i.effective_start_date,69);
720 hr_entry_api.delete_element_entry
721 ('DELETE_NEXT_CHANGE',
722 i.effective_end_date,
723 p_element_entry_id);
724 END LOOP;
725
726 ELSIF l_date_to < l_Entry_End_Date THEN
727 hr_entry_api.update_element_entry
728 (p_dt_update_mode => get_dt_mode(l_date_to+1,p_element_entry_id)
729 ,p_session_date => l_date_to+1
730 ,p_check_for_update => 'Y'
731 ,p_element_entry_id => p_element_entry_id
732 ,p_creator_type => 'SP'
733 ,p_creator_id => p_pay_proposal_id
734 ,p_input_value_id1 => l_input_value_id
735 ,p_entry_value1 => 0
736 ,p_input_value_id2 => l_input_value_id2
737 ,p_entry_value2 => 0
738 );
739 ELSE
740 hr_utility.set_location('The element entry end date and the date_to are matching',89);
741 END IF; --l_date_to and element entry date comparisons
742 end if;
743 end if;
744
745 --changes for Position Control check on Salary proposal
746 pqh_psf_bus.chk_position_budget( p_assignment_id => p_assignment_id
747 ,p_element_type_id => l_element_type_id
748 ,p_input_value_id => l_input_value_id
749 ,p_effective_date => p_change_date
750 ,p_called_from => 'SAL');
751 --End changes for position control rule on sal proposal
752 --
753 hr_utility.set_location('Leaving:'||l_proc,70);
754 end maintain_elements_bsp;
755 --
756 -- ----------------------------------------------------------------------------
757 -- |----------------------< insert_salary_proposal >--------------------------|
758 -- ----------------------------------------------------------------------------
759 --
760 Procedure insert_salary_proposal(
761 p_pay_proposal_id out nocopy number,
762 p_assignment_id in number,
763 p_business_group_id in number,
764 p_change_date in date, -- Bug 918219
765 p_comments in varchar2,
766 p_next_sal_review_date in date,
767 p_proposal_reason in varchar2,
768 p_proposed_salary_n in number,
769 p_forced_ranking in number,
770 p_date_to in date,
771 p_performance_review_id in number,
772 p_attribute_category in varchar2,
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_object_version_number out nocopy number,
794 p_multiple_components in varchar2, -- 918219
795 p_approved in varchar2, -- 918219
796 p_validate in boolean,
797 p_element_entry_id in out nocopy number,
798 p_inv_next_sal_date_warning out nocopy boolean,
799 p_proposed_salary_warning out nocopy boolean,
800 p_approved_warning out nocopy boolean,
801 p_payroll_warning out nocopy boolean ) is
802 --
803
804 -- changed by schowdhu for bug #7693247 16-jan-2009
805 -- included p_change_date as input param
806
807 Cursor next_change_date(p_change_date DATE)
808 IS
809 select min(change_date)
810 from per_pay_proposals
811 where assignment_id = p_assignment_id
812 and change_date > p_change_date
813 and approved = p_approved;
814
815 l_proc varchar2(72) := g_package||'insert_salary_proposal';
816 l_pay_proposal_id per_pay_proposals.pay_proposal_id%TYPE;
817 l_change_date per_pay_proposals.change_date%TYPE;
818 l_date_to per_pay_proposals.date_to%TYPE;
819 l_next_sal_review_date per_pay_proposals.next_sal_review_date%TYPE;
820 l_object_version_number per_pay_proposals.object_version_number%TYPE;
821 l_element_entry_id pay_element_entries_f.element_entry_id%TYPE;
822 l_inv_next_sal_date_warning boolean;
823 l_proposed_salary_warning boolean;
824 l_approved_warning boolean;
825 l_payroll_warning boolean;
826 l_temp_element_entry_id number := p_element_entry_id;
827 l_payroll_value number;
828 l_next_change_date per_pay_proposals.change_date%TYPE;
829
830 --
831 --
832 begin
833 hr_utility.set_location('Entering:'||l_proc,10);
834 --
835 -- Issue a savepoint
836 --
837 savepoint insert_salary_proposal;
838 --
839 -- Truncate dates
840 l_change_date := trunc(p_change_date);
841 l_next_sal_review_date := trunc(p_next_sal_review_date);
842
843 l_date_to := p_date_to;
844
845 if l_date_to is null or l_date_to = hr_general.end_of_time then
846
847 OPEN next_change_date(l_change_date);
848 fetch next_change_date into l_next_change_date;
849 close next_change_date;
850
851 if l_next_change_date is null then
852 l_date_to:= hr_general.end_of_time;
853 else
854 l_date_to := l_next_change_date-1;
855 end if;
856
857 end if;
858
859 --
860 -- Process Logic
861 --
862 l_element_entry_id := p_element_entry_id;
863
864
865
866 -- Call Before Process User Hook for insert_salary_proposal
867 --
868 begin
869 hr_maintain_proposal_bk1.insert_salary_proposal_b
870 (
871 p_assignment_id => p_assignment_id,
872 p_business_group_id => p_business_group_id,
873 p_change_date => l_change_date,
874 p_comments => p_comments,
875 p_next_sal_review_date => l_next_sal_review_date,
876 p_proposal_reason => p_proposal_reason,
877 p_proposed_salary_n => p_proposed_salary_n,
878 p_forced_ranking => p_forced_ranking,
879 p_date_to => l_date_to,
880 p_performance_review_id => p_performance_review_id,
881 p_attribute_category => p_attribute_category,
882 p_attribute1 => p_attribute1,
883 p_attribute2 => p_attribute2,
884 p_attribute3 => p_attribute3,
885 p_attribute4 => p_attribute4,
886 p_attribute5 => p_attribute5,
887 p_attribute6 => p_attribute6,
888 p_attribute7 => p_attribute7,
889 p_attribute8 => p_attribute8,
890 p_attribute9 => p_attribute9,
891 p_attribute10 => p_attribute10,
892 p_attribute11 => p_attribute11,
893 p_attribute12 => p_attribute12,
894 p_attribute13 => p_attribute13,
895 p_attribute14 => p_attribute14,
896 p_attribute15 => p_attribute15,
897 p_attribute16 => p_attribute16,
898 p_attribute17 => p_attribute17,
899 p_attribute18 => p_attribute18,
900 p_attribute19 => p_attribute19,
901 p_attribute20 => p_attribute20,
902 p_multiple_components => p_multiple_components,
903 p_approved => p_approved,
904 p_element_entry_id => p_element_entry_id
905 );
906 exception
907 when hr_api.cannot_find_prog_unit then
908 hr_api.cannot_find_prog_unit_error
909 (p_module_name => 'INSERT_SALARY_PROPOSAL'
910 ,p_hook_type => 'BP'
911 );
912 --
913 -- End of the before hook for insert_salary_proposal
914 --
915 end;
916 --
917 --
918 -- End Date Previous Salary Proposal with p_change_date-1
919 --
920 --added by vkodedal to fix 6001309
921 --
922 hr_general.g_data_migrator_mode := 'Y';
923 --
924
925 /* Salary proposals to be end dated depending upon the
926 status of the calling proposal. Bug#7386307 by schowdhu
927 */
928
929 if (p_approved = 'N') then
930 end_date_proposed_proposal(p_assignment_id, p_change_date-1);
931 else
932 -- changed by schowdhu for 7673294 05-jan-08
933 end_date_approved_proposal(p_assignment_id => p_assignment_id
934 ,p_date_to => p_change_date-1
935 ,p_proposal_id => NULL);
936
937 end if;
938
939 --added by schowdhu to fix 7205132
940 update_last_change_date(p_assignment_id, p_change_date);
941
942 hr_general.g_data_migrator_mode := 'N';
943 ----------vkodedal 7-mar-07
944 --
945 -- Update the last_change_date for the next proposal
946 --
947 --commented out by schowdhu to fix 7205132
948 -- update_last_change_date(p_assignment_id, p_change_date);
949
950 per_pyp_ins.ins
951 (p_pay_proposal_id => l_pay_proposal_id
952 ,p_assignment_id => p_assignment_id
953 ,p_business_group_id => p_business_group_id
954 ,p_change_date => l_change_date
955 ,p_comments => p_comments
956 ,p_next_sal_review_date => l_next_sal_review_date
957 ,p_proposal_reason => p_proposal_reason
958 ,p_proposed_salary_n => p_proposed_salary_n
959 ,p_forced_ranking => p_forced_ranking
960 ,p_date_to => l_date_to
961 ,p_performance_review_id => p_performance_review_id
962 ,p_attribute_category => p_attribute_category
963 ,p_attribute1 => p_attribute1
964 ,p_attribute2 => p_attribute2
965 ,p_attribute3 => p_attribute3
966 ,p_attribute4 => p_attribute4
967 ,p_attribute5 => p_attribute5
968 ,p_attribute6 => p_attribute6
969 ,p_attribute7 => p_attribute7
970 ,p_attribute8 => p_attribute8
971 ,p_attribute9 => p_attribute9
972 ,p_attribute10 => p_attribute10
973 ,p_attribute11 => p_attribute11
974 ,p_attribute12 => p_attribute12
975 ,p_attribute13 => p_attribute13
976 ,p_attribute14 => p_attribute14
977 ,p_attribute15 => p_attribute15
978 ,p_attribute16 => p_attribute16
979 ,p_attribute17 => p_attribute17
980 ,p_attribute18 => p_attribute18
981 ,p_attribute19 => p_attribute19
982 ,p_attribute20 => p_attribute20
983 ,p_object_version_number => l_object_version_number
984 ,p_multiple_components => p_multiple_components
985 ,p_approved => p_approved
986 ,p_validate => false
987 ,p_inv_next_sal_date_warning
988 => l_inv_next_sal_date_warning
989 ,p_proposed_salary_warning
990 => l_proposed_salary_warning
991 ,p_approved_warning
992 => l_approved_warning
993 ,p_payroll_warning
994 => l_payroll_warning
995 );
996 --
997 -- Now maintain element entries;
998 --
999 -- Added by ggnanagu
1000 -- To get the element entry id, if its passed as null
1001 --
1002 if l_element_entry_id is null then
1003 per_pay_proposals_populate.GET_ELEMENT_ID(p_assignment_id => p_assignment_id,
1004 p_business_group_id => p_business_group_id,
1005 p_change_date => p_change_date,
1006 p_payroll_value => l_payroll_value,
1007 p_element_entry_id => l_element_entry_id);
1008 end if;
1009 --
1010 hr_utility.set_location(l_proc,20);
1011 if(p_approved='Y') then
1012 hr_utility.set_location(l_proc,30);
1013 -- parameter p_element_entry_id is change to l_element_entry_id to fix the bug#3488239.
1014 maintain_elements_bsp(l_pay_proposal_id
1015 ,p_assignment_id
1016 ,p_change_date
1017 ,l_date_to
1018 ,l_element_entry_id
1019 ,p_proposed_salary_n);
1020 end if;
1021 --
1022 hr_utility.set_location(l_proc, 40);
1023 --
1024 -- Call After Process User Hook for insert_salary_proposal
1025 --
1026 begin
1027 hr_maintain_proposal_bk1.insert_salary_proposal_a
1028 (
1029 p_pay_proposal_id => l_pay_proposal_id,
1030 p_assignment_id => p_assignment_id,
1031 p_business_group_id => p_business_group_id,
1032 p_change_date => l_change_date,
1033 p_comments => p_comments,
1034 p_next_sal_review_date => l_next_sal_review_date,
1035 p_proposal_reason => p_proposal_reason,
1036 p_proposed_salary_n => p_proposed_salary_n,
1037 p_forced_ranking => p_forced_ranking,
1038 p_date_to => l_date_to,
1039 p_performance_review_id => p_performance_review_id,
1040 p_attribute_category => p_attribute_category,
1041 p_attribute1 => p_attribute1,
1042 p_attribute2 => p_attribute2,
1043 p_attribute3 => p_attribute3,
1044 p_attribute4 => p_attribute4,
1045 p_attribute5 => p_attribute5,
1046 p_attribute6 => p_attribute6,
1047 p_attribute7 => p_attribute7,
1048 p_attribute8 => p_attribute8,
1049 p_attribute9 => p_attribute9,
1050 p_attribute10 => p_attribute10,
1051 p_attribute11 => p_attribute11,
1052 p_attribute12 => p_attribute12,
1053 p_attribute13 => p_attribute13,
1054 p_attribute14 => p_attribute14,
1055 p_attribute15 => p_attribute15,
1056 p_attribute16 => p_attribute16,
1057 p_attribute17 => p_attribute17,
1058 p_attribute18 => p_attribute18,
1059 p_attribute19 => p_attribute19,
1060 p_attribute20 => p_attribute20,
1061 p_object_version_number => l_object_version_number,
1062 p_multiple_components => p_multiple_components,
1063 p_approved => p_approved,
1064 p_element_entry_id => l_element_entry_id,
1065 p_inv_next_sal_date_warning => l_inv_next_sal_date_warning,
1066 p_proposed_salary_warning => l_proposed_salary_warning,
1067 p_approved_warning => l_approved_warning,
1068 p_payroll_warning => l_payroll_warning
1069 );
1070 exception
1071 when hr_api.cannot_find_prog_unit then
1072 hr_api.cannot_find_prog_unit_error
1073 (p_module_name => 'INSERT_SALARY_PROPOSAL'
1074 ,p_hook_type => 'AP'
1075 );
1076 --
1077 -- End of after hook for insert_salary_proposal
1078 --
1079 end;
1080 --
1081 -- If we are validating then raise the Validate_Enabled exception
1082 --
1083 If p_validate then
1084 Raise HR_Api.Validate_Enabled;
1085 End If;
1086 --
1087 -- Set OUT parameters
1088 --
1089 p_pay_proposal_id := l_pay_proposal_id;
1090 p_object_version_number := l_object_version_number;
1091 p_element_entry_id := l_element_entry_id;
1092 p_inv_next_sal_date_warning := l_inv_next_sal_date_warning;
1093 p_proposed_salary_warning := l_proposed_salary_warning;
1094 p_approved_warning := l_approved_warning;
1095 p_payroll_warning := l_payroll_warning;
1096 --
1097 hr_utility.set_location('Leaving: '||l_proc,50);
1098 --
1099 Exception
1100 When HR_Api.Validate_Enabled Then
1101 --
1102 -- As the Validate_Enabled exception has been raised
1103 -- we must rollback to the savepoint
1104 --
1105 ROLLBACK TO insert_salary_proposal;
1106 --
1107 -- Only set output warning arguments
1108 -- (Any key or derived arguments must be set to null
1109 -- when validation only mode is being used.)
1110 --
1111 p_pay_proposal_id := null;
1112 p_object_version_number := null;
1113 p_inv_next_sal_date_warning := l_inv_next_sal_date_warning;
1114 p_proposed_salary_warning := l_proposed_salary_warning;
1115 p_approved_warning := l_approved_warning;
1116 p_payroll_warning := l_payroll_warning;
1117
1118 p_element_entry_id := l_temp_element_entry_id;
1119 --
1120 hr_utility.set_location(' Leaving:'||l_proc, 60);
1121 --
1122 When others then
1123 --
1124 -- A validation or unexpected error has occured
1125 --
1126 ROLLBACK TO insert_salary_proposal;
1127 p_pay_proposal_id := null;
1128 p_object_version_number := null;
1129 p_inv_next_sal_date_warning := null;
1130 p_proposed_salary_warning := null;
1131 p_approved_warning := null;
1132 p_payroll_warning := null;
1133 p_element_entry_id := l_temp_element_entry_id;
1134 hr_utility.set_location(' Leaving:'||l_proc, 70);
1135 raise;
1136 --
1137 end insert_salary_proposal;
1138 --
1139 -- ----------------------------------------------------------------------------
1140 -- |----------------------< insert_salary_proposal >--------------------------|
1141 -------------------------------------------------------------------------------
1142 ----------------------- Without the new date_to parameter -------------------
1143 -------------------------------------------------------------------------------
1144 --
1145 Procedure insert_salary_proposal(
1146 p_pay_proposal_id out nocopy number,
1147 p_assignment_id in number,
1148 p_business_group_id in number,
1149 p_change_date in date, -- Bug 918219
1150 p_comments in varchar2,
1151 p_next_sal_review_date in date,
1152 p_proposal_reason in varchar2,
1153 p_proposed_salary_n in number,
1154 p_forced_ranking in number,
1155 p_performance_review_id in number,
1156 p_attribute_category in varchar2,
1157 p_attribute1 in varchar2,
1158 p_attribute2 in varchar2,
1159 p_attribute3 in varchar2,
1160 p_attribute4 in varchar2,
1161 p_attribute5 in varchar2,
1162 p_attribute6 in varchar2,
1163 p_attribute7 in varchar2,
1164 p_attribute8 in varchar2,
1165 p_attribute9 in varchar2,
1166 p_attribute10 in varchar2,
1167 p_attribute11 in varchar2,
1168 p_attribute12 in varchar2,
1169 p_attribute13 in varchar2,
1170 p_attribute14 in varchar2,
1171 p_attribute15 in varchar2,
1172 p_attribute16 in varchar2,
1173 p_attribute17 in varchar2,
1174 p_attribute18 in varchar2,
1175 p_attribute19 in varchar2,
1176 p_attribute20 in varchar2,
1177 p_object_version_number out nocopy number,
1178 p_multiple_components in varchar2, -- 918219
1179 p_approved in varchar2, -- 918219
1180 p_validate in boolean,
1181 p_element_entry_id in out nocopy number,
1182 p_inv_next_sal_date_warning out nocopy boolean,
1183 p_proposed_salary_warning out nocopy boolean,
1184 p_approved_warning out nocopy boolean,
1185 p_payroll_warning out nocopy boolean ) is
1186 --
1187 --
1188 begin
1189 hr_maintain_proposal_api.insert_salary_proposal
1190 (p_pay_proposal_id => p_pay_proposal_id
1191 ,p_assignment_id => p_assignment_id
1192 ,p_business_group_id => p_business_group_id
1193 ,p_change_date => p_change_date
1194 ,p_comments => p_comments
1195 ,p_next_sal_review_date => p_next_sal_review_date
1196 ,p_proposal_reason => p_proposal_reason
1197 ,p_proposed_salary_n => p_proposed_salary_n
1198 ,p_forced_ranking => p_forced_ranking
1199 ,p_date_to => null
1200 ,p_performance_review_id => p_performance_review_id
1201 ,p_attribute_category => p_attribute_category
1202 ,p_attribute1 => p_attribute1
1203 ,p_attribute2 => p_attribute2
1204 ,p_attribute3 => p_attribute3
1205 ,p_attribute4 => p_attribute4
1206 ,p_attribute5 => p_attribute5
1207 ,p_attribute6 => p_attribute6
1208 ,p_attribute7 => p_attribute7
1209 ,p_attribute8 => p_attribute8
1210 ,p_attribute9 => p_attribute9
1211 ,p_attribute10 => p_attribute10
1212 ,p_attribute11 => p_attribute11
1213 ,p_attribute12 => p_attribute12
1214 ,p_attribute13 => p_attribute13
1215 ,p_attribute14 => p_attribute14
1216 ,p_attribute15 => p_attribute15
1217 ,p_attribute16 => p_attribute16
1218 ,p_attribute17 => p_attribute17
1219 ,p_attribute18 => p_attribute18
1220 ,p_attribute19 => p_attribute19
1221 ,p_attribute20 => p_attribute20
1222 ,p_object_version_number => p_object_version_number
1223 ,p_multiple_components => p_multiple_components
1224 ,p_approved => p_approved
1225 ,p_validate => p_validate
1226 ,p_element_entry_id => p_element_entry_id
1227 ,p_inv_next_sal_date_warning => p_inv_next_sal_date_warning
1228 ,p_proposed_salary_warning => p_proposed_salary_warning
1229 ,p_approved_warning => p_approved_warning
1230 ,p_payroll_warning => p_payroll_warning
1231 );
1232 --
1233 end insert_salary_proposal;
1234 --
1235 -- ----------------------------------------------------------------------------
1236 -- |----------------------< update_salary_proposal >--------------------------|
1237 -- ----------------------------------------------------------------------------
1238 --
1239 Procedure update_salary_proposal(
1240 p_pay_proposal_id in number,
1241 p_change_date in date,
1242 p_comments in varchar2,
1243 p_next_sal_review_date in date,
1244 p_proposal_reason in varchar2,
1245 p_proposed_salary_n in number,
1246 p_forced_ranking in number,
1247 p_date_to in date,
1248 p_performance_review_id in number,
1249 p_attribute_category in varchar2,
1250 p_attribute1 in varchar2,
1251 p_attribute2 in varchar2,
1252 p_attribute3 in varchar2,
1253 p_attribute4 in varchar2,
1254 p_attribute5 in varchar2,
1255 p_attribute6 in varchar2,
1256 p_attribute7 in varchar2,
1257 p_attribute8 in varchar2,
1258 p_attribute9 in varchar2,
1259 p_attribute10 in varchar2,
1260 p_attribute11 in varchar2,
1261 p_attribute12 in varchar2,
1262 p_attribute13 in varchar2,
1263 p_attribute14 in varchar2,
1264 p_attribute15 in varchar2,
1265 p_attribute16 in varchar2,
1266 p_attribute17 in varchar2,
1267 p_attribute18 in varchar2,
1268 p_attribute19 in varchar2,
1269 p_attribute20 in varchar2,
1270 p_object_version_number in out nocopy number,
1271 p_multiple_components in varchar2,
1272 p_approved in varchar2,
1273 p_validate in boolean,
1274 p_inv_next_sal_date_warning out nocopy boolean,
1275 p_proposed_salary_warning out nocopy boolean,
1276 p_approved_warning out nocopy boolean,
1277 p_payroll_warning out nocopy boolean) is
1278 --
1279 l_proc varchar2(72) := g_package||'update_salary_proposal';
1280 l_element_type_id pay_element_types_f.element_type_id%TYPE;
1281 l_input_value_id pay_input_values_f.input_value_id%TYPE;
1282 l_element_link_id pay_element_links_f.element_link_id%TYPE;
1283 l_element_entry_id pay_element_entries_f.element_entry_id%TYPE;
1284 l_change_date per_pay_proposals.change_date%TYPE;
1285 l_next_sal_review_date per_pay_proposals.next_sal_review_date%TYPE;
1286 l_next_change_date per_pay_proposals.change_date%TYPE;
1287 l_date_to per_pay_proposals.date_to%TYPE;
1288 l_object_version_number per_pay_proposals.object_version_number%TYPE;
1289 l_inv_next_sal_date_warning boolean;
1290 l_proposed_salary_warning boolean;
1291 l_approved_warning boolean;
1292 l_payroll_warning boolean;
1293 l_assignment_id per_pay_proposals.assignment_id%TYPE;
1294 l_proposed_salary_n per_pay_proposals.proposed_salary_n%TYPE;
1295 l_temp_ovn number := p_object_version_number;
1296 --
1297 --
1298
1299 Cursor get_assignment_id
1300 is
1301 select assignment_id
1302 from per_pay_proposals
1303 where pay_proposal_id = p_pay_proposal_id;
1304
1305 cursor get_element IS
1306 select pee.element_entry_id
1307 , pyp.assignment_id
1308 from pay_element_entries_f pee
1309 , per_pay_proposals pyp
1310 where pyp.pay_proposal_id=p_pay_proposal_id
1311 and pee.assignment_id=pyp.assignment_id
1312 and NVL(l_change_date,to_date('31-12-4127','DD-MM-YYYY')) between
1313 pee.effective_start_date and pee.effective_end_date
1314 and pee.creator_type='SP';
1315
1316 -- changes the cursor to add approved = p_approve schowdhu - 04-Dec-2008
1317 -- changed by schowdhu for bug #7693247 16-jan-2009
1318 -- included p_change_date as input param
1319
1320 Cursor next_change_date(p_change_date DATE)
1321 IS
1322 select min(change_date)
1323 from per_pay_proposals pro
1324 where assignment_id = l_assignment_id
1325 and change_date > p_change_date
1326 and approved = p_approved;
1327
1328 --
1329 begin
1330
1331 hr_utility.set_location('Entering:'||l_proc,5);
1332 --
1333 -- Issue a savepoint
1334 --
1335 savepoint update_salary_proposal;
1336 --
1337 --
1338 -- get the old record details
1339 --
1340 per_pyp_shd.lck
1341 (p_pay_proposal_id => p_pay_proposal_id
1342 ,p_object_version_number => p_object_version_number);
1343 --
1344 if p_change_date = hr_api.g_date then
1345 l_change_date:=per_pyp_shd.g_old_rec.change_date;
1346 else
1347 l_change_date := trunc(p_change_date);
1348 end if;
1349 --
1350
1351 l_date_to := p_date_to;
1352
1353 OPEN get_assignment_id;
1354 fetch get_assignment_id into l_assignment_id;
1355 CLOSE get_assignment_id;
1356 -- changed if condition schowdhu - 04-Dec-2008
1357
1358 if l_date_to is null or l_date_to = hr_general.end_of_time then
1359
1360 OPEN next_change_date(l_change_date);
1361 fetch next_change_date into l_next_change_date;
1362 close next_change_date;
1363
1364 if l_next_change_date is null then
1365 l_date_to:= hr_general.end_of_time;
1366 else
1367 l_date_to := l_next_change_date-1;
1368 end if;
1369
1370 end if;
1371
1372
1373 l_next_sal_review_date := trunc(p_next_sal_review_date);
1374 --
1375 -- Process Logic
1376 --
1377 l_object_version_number := p_object_version_number;
1378 --
1379 -- Call Before Process User Hook for update_salary_proposal
1380 --
1381 begin
1382 hr_maintain_proposal_bk2.update_salary_proposal_b
1383 (
1384 p_pay_proposal_id => p_pay_proposal_id,
1385 p_change_date => l_change_date,
1386 p_comments => p_comments,
1387 p_next_sal_review_date => l_next_sal_review_date,
1388 p_proposal_reason => p_proposal_reason,
1389 p_proposed_salary_n => p_proposed_salary_n,
1390 p_forced_ranking => p_forced_ranking,
1391 p_date_to => l_date_to,
1392 p_performance_review_id => p_performance_review_id,
1393 p_attribute_category => p_attribute_category,
1394 p_attribute1 => p_attribute1,
1395 p_attribute2 => p_attribute2,
1396 p_attribute3 => p_attribute3,
1397 p_attribute4 => p_attribute4,
1398 p_attribute5 => p_attribute5,
1399 p_attribute6 => p_attribute6,
1400 p_attribute7 => p_attribute7,
1401 p_attribute8 => p_attribute8,
1402 p_attribute9 => p_attribute9,
1403 p_attribute10 => p_attribute10,
1404 p_attribute11 => p_attribute11,
1405 p_attribute12 => p_attribute12,
1406 p_attribute13 => p_attribute13,
1407 p_attribute14 => p_attribute14,
1408 p_attribute15 => p_attribute15,
1409 p_attribute16 => p_attribute16,
1410 p_attribute17 => p_attribute17,
1411 p_attribute18 => p_attribute18,
1412 p_attribute19 => p_attribute19,
1413 p_attribute20 => p_attribute20,
1414 p_object_version_number => p_object_version_number,
1415 p_multiple_components => p_multiple_components,
1416 p_approved => p_approved
1417 );
1418 exception
1419 when hr_api.cannot_find_prog_unit then
1420 hr_api.cannot_find_prog_unit_error
1421 (p_module_name => 'UPDATE_SALARY_PROPOSAL'
1422 ,p_hook_type => 'BP'
1423 );
1424 --
1425 -- End of the before hook for update_salary_proposal
1426 --
1427 end;
1428
1429 /* logic added to end date salary proposals accordingly while
1430 updating a proposal Bug#7386307 by schowdhu
1431 */
1432
1433 hr_general.g_data_migrator_mode := 'Y';
1434 --
1435 if (p_approved = 'N') then
1436 end_date_proposed_proposal(l_assignment_id, l_change_date-1);
1437 else
1438 -- when the underlying salary basis changes update_salary_proposal
1439 -- is called without p_approved. Bug#7386307 by schowdhu
1440 end_date_approved_proposal(l_assignment_id, l_change_date-1, p_pay_proposal_id);
1441 end if;
1442 --start of comments for bug 12839611
1443 --update_last_change_date(l_assignment_id, l_change_date);
1444 --end of comments for bug 12839611
1445 --
1446 hr_general.g_data_migrator_mode := 'N';
1447 --
1448 per_pyp_upd.upd
1449 (p_pay_proposal_id => p_pay_proposal_id
1450 ,p_comments => p_comments
1451 ,p_change_date => l_change_date
1452 ,p_next_sal_review_date => l_next_sal_review_date
1453 ,p_proposal_reason => p_proposal_reason
1454 ,p_proposed_salary_n => p_proposed_salary_n
1455 ,p_forced_ranking => p_forced_ranking
1456 ,p_date_to => l_date_to
1457 ,p_performance_review_id => p_performance_review_id
1458 ,p_attribute_category => p_attribute_category
1459 ,p_attribute1 => p_attribute1
1460 ,p_attribute2 => p_attribute2
1461 ,p_attribute3 => p_attribute3
1462 ,p_attribute4 => p_attribute4
1463 ,p_attribute5 => p_attribute5
1464 ,p_attribute6 => p_attribute6
1465 ,p_attribute7 => p_attribute7
1466 ,p_attribute8 => p_attribute8
1467 ,p_attribute9 => p_attribute9
1468 ,p_attribute10 => p_attribute10
1469 ,p_attribute11 => p_attribute11
1470 ,p_attribute12 => p_attribute12
1471 ,p_attribute13 => p_attribute13
1472 ,p_attribute14 => p_attribute14
1473 ,p_attribute15 => p_attribute15
1474 ,p_attribute16 => p_attribute16
1475 ,p_attribute17 => p_attribute17
1476 ,p_attribute18 => p_attribute18
1477 ,p_attribute19 => p_attribute19
1478 ,p_attribute20 => p_attribute20
1479 ,p_object_version_number => l_object_version_number
1480 ,p_multiple_components => p_multiple_components
1481 ,p_approved => p_approved
1482 ,p_validate => false
1483 ,p_inv_next_sal_date_warning
1484 => l_inv_next_sal_date_warning
1485 ,p_proposed_salary_warning => l_proposed_salary_warning
1486 ,p_approved_warning => l_approved_warning
1487 ,p_payroll_warning => l_payroll_warning
1488 );
1489
1490 -- Now we maintain an element entry for this proposal
1491 -- by calling the maintain_elements_bsp.
1492 --
1493 hr_utility.set_location(l_proc,10);
1494
1495 if(p_approved='Y') then
1496 hr_utility.set_location(l_proc,15);
1497 open get_element;
1498 fetch get_element into l_element_entry_id,l_assignment_id;
1499 if get_element%found then
1500 close get_element;
1501 hr_utility.set_location(l_proc,20);
1502 --
1503 if p_proposed_salary_n = hr_api.g_number then
1504 l_proposed_salary_n:=per_pyp_shd.g_old_rec.proposed_salary_n;
1505 else
1506 l_proposed_salary_n:=p_proposed_salary_n;
1507 end if;
1508 maintain_elements_bsp(p_pay_proposal_id
1509 ,l_assignment_id
1510 ,l_change_date
1511 ,l_date_to
1512 ,l_element_entry_id
1513 ,l_proposed_salary_n);
1514 else
1515 hr_utility.set_location(l_proc,25);
1516 l_element_entry_id := null;
1517 select pyp.assignment_id
1518 into l_assignment_id
1519 from per_pay_proposals pyp
1520 where pyp.pay_proposal_id=p_pay_proposal_id;
1521 if p_proposed_salary_n = hr_api.g_number then
1522 l_proposed_salary_n:=per_pyp_shd.g_old_rec.proposed_salary_n;
1523 else
1524 l_proposed_salary_n:=p_proposed_salary_n;
1525 end if;
1526 maintain_elements_bsp(p_pay_proposal_id
1527 ,l_assignment_id
1528 ,l_change_date
1529 ,l_date_to
1530 ,l_element_entry_id
1531 ,l_proposed_salary_n);
1532 close get_element;
1533 end if;
1534 end if;
1535
1536 --
1537 -- Call After Process User Hook for update_salary_proposal
1538 --
1539 begin
1540 hr_maintain_proposal_bk2.update_salary_proposal_a
1541 (
1542 p_pay_proposal_id => p_pay_proposal_id,
1543 p_change_date => l_change_date,
1544 p_comments => p_comments,
1545 p_next_sal_review_date => l_next_sal_review_date,
1546 p_proposal_reason => p_proposal_reason,
1547 p_proposed_salary_n => p_proposed_salary_n,
1548 p_forced_ranking => p_forced_ranking,
1549 p_date_to => l_date_to,
1550 p_performance_review_id => p_performance_review_id,
1551 p_attribute_category => p_attribute_category,
1552 p_attribute1 => p_attribute1,
1553 p_attribute2 => p_attribute2,
1554 p_attribute3 => p_attribute3,
1555 p_attribute4 => p_attribute4,
1556 p_attribute5 => p_attribute5,
1557 p_attribute6 => p_attribute6,
1558 p_attribute7 => p_attribute7,
1559 p_attribute8 => p_attribute8,
1560 p_attribute9 => p_attribute9,
1561 p_attribute10 => p_attribute10,
1562 p_attribute11 => p_attribute11,
1563 p_attribute12 => p_attribute12,
1564 p_attribute13 => p_attribute13,
1565 p_attribute14 => p_attribute14,
1566 p_attribute15 => p_attribute15,
1567 p_attribute16 => p_attribute16,
1568 p_attribute17 => p_attribute17,
1569 p_attribute18 => p_attribute18,
1570 p_attribute19 => p_attribute19,
1571 p_attribute20 => p_attribute20,
1572 p_object_version_number => l_object_version_number,
1573 p_multiple_components => p_multiple_components,
1574 p_approved => p_approved,
1575 p_inv_next_sal_date_warning => l_inv_next_sal_date_warning,
1576 p_proposed_salary_warning => l_proposed_salary_warning,
1577 p_approved_warning => l_approved_warning,
1578 p_payroll_warning => l_payroll_warning
1579 );
1580 exception
1581 when hr_api.cannot_find_prog_unit then
1582 hr_api.cannot_find_prog_unit_error
1583 (p_module_name => 'UPDATE_SALARY_PROPOSAL'
1584 ,p_hook_type => 'AP'
1585 );
1586 --
1587 -- End of the after hook for update_salary_proposal
1588 --
1589 end;
1590 --
1591 -- If we are validating then raise the Validate_Enabled exception
1592 --
1593 If p_validate then
1594 Raise HR_Api.Validate_Enabled;
1595 End If;
1596 --
1597 -- Set OUT parameters
1598 --
1599 p_object_version_number := l_object_version_number;
1600 p_inv_next_sal_date_warning := l_inv_next_sal_date_warning;
1601 p_proposed_salary_warning := l_proposed_salary_warning;
1602 p_approved_warning := l_approved_warning;
1603 p_payroll_warning := l_payroll_warning;
1604 --
1605 hr_utility.set_location('Leaving: '||l_proc,20);
1606 --
1607 Exception
1608 When HR_Api.Validate_Enabled Then
1609 --
1610 -- As the Validate_Enabled exception has been raised
1611 -- we must rollback to the savepoint
1612 --
1613 ROLLBACK TO update_salary_proposal;
1614 --
1615 -- Only set output warning arguments
1616 -- (Any key or derived arguments must be set to null
1617 -- when validation only mode is being used.)
1618 --
1619 p_inv_next_sal_date_warning := l_inv_next_sal_date_warning;
1620 p_proposed_salary_warning := l_proposed_salary_warning;
1621 p_approved_warning := l_approved_warning;
1622 p_payroll_warning := l_payroll_warning;
1623 p_object_version_number := l_temp_ovn;
1624 --
1625 hr_utility.set_location(' Leaving:'||l_proc, 25);
1626 --
1627 When others then
1628 --
1629 -- A validation or unexpected error has occured
1630 --
1631 ROLLBACK TO update_salary_proposal;
1632 p_inv_next_sal_date_warning := null;
1633 p_proposed_salary_warning := null;
1634 p_approved_warning := null;
1635 p_payroll_warning := null;
1636 p_object_version_number := l_temp_ovn;
1637 hr_utility.set_location(' Leaving:'||l_proc, 30);
1638 raise;
1639 --
1640 end update_salary_proposal;
1641 --
1642 -- ----------------------------------------------------------------------------
1643 -- |----------------------< update_salary_proposal >--------------------------|
1644 -- ----------------------------------------------------------------------------
1645 ----------------------- Without the new date_to parameter --------------------
1646 -- ----------------------------------------------------------------------------
1647 --
1648 Procedure update_salary_proposal(
1649 p_pay_proposal_id in number,
1650 p_change_date in date,
1651 p_comments in varchar2,
1652 p_next_sal_review_date in date,
1653 p_proposal_reason in varchar2,
1654 p_proposed_salary_n in number,
1655 p_forced_ranking in number,
1656 p_performance_review_id in number,
1657 p_attribute_category in varchar2,
1658 p_attribute1 in varchar2,
1659 p_attribute2 in varchar2,
1660 p_attribute3 in varchar2,
1661 p_attribute4 in varchar2,
1662 p_attribute5 in varchar2,
1663 p_attribute6 in varchar2,
1664 p_attribute7 in varchar2,
1665 p_attribute8 in varchar2,
1666 p_attribute9 in varchar2,
1667 p_attribute10 in varchar2,
1668 p_attribute11 in varchar2,
1669 p_attribute12 in varchar2,
1670 p_attribute13 in varchar2,
1671 p_attribute14 in varchar2,
1672 p_attribute15 in varchar2,
1673 p_attribute16 in varchar2,
1674 p_attribute17 in varchar2,
1675 p_attribute18 in varchar2,
1676 p_attribute19 in varchar2,
1677 p_attribute20 in varchar2,
1678 p_object_version_number in out nocopy number,
1679 p_multiple_components in varchar2,
1680 p_approved in varchar2,
1681 p_validate in boolean,
1682 p_inv_next_sal_date_warning out nocopy boolean,
1683 p_proposed_salary_warning out nocopy boolean,
1684 p_approved_warning out nocopy boolean,
1685 p_payroll_warning out nocopy boolean) is
1686 --
1687 --
1688 Cursor csr_date_to
1689 IS
1690 select date_to
1691 from per_pay_proposals
1692 where pay_proposal_id = p_pay_proposal_id;
1693
1694 l_date_to PER_PAY_PROPOSALS.date_to%TYPE default null;
1695
1696 begin
1697
1698 OPEN csr_date_to;
1699 FETCH csr_date_to into l_date_to;
1700 CLOSE csr_date_to;
1701
1702 hr_maintain_proposal_api.update_salary_proposal
1703 (p_pay_proposal_id => p_pay_proposal_id
1704 ,p_change_date => p_change_date
1705 ,p_comments => p_comments
1706 ,p_next_sal_review_date => p_next_sal_review_date
1707 ,p_proposal_reason => p_proposal_reason
1708 ,p_proposed_salary_n => p_proposed_salary_n
1709 ,p_forced_ranking => p_forced_ranking
1710 ,p_date_to => l_date_to
1711 ,p_performance_review_id => p_performance_review_id
1712 ,p_attribute_category => p_attribute_category
1713 ,p_attribute1 => p_attribute1
1714 ,p_attribute2 => p_attribute2
1715 ,p_attribute3 => p_attribute3
1716 ,p_attribute4 => p_attribute4
1717 ,p_attribute5 => p_attribute5
1718 ,p_attribute6 => p_attribute6
1719 ,p_attribute7 => p_attribute7
1720 ,p_attribute8 => p_attribute8
1721 ,p_attribute9 => p_attribute9
1722 ,p_attribute10 => p_attribute10
1723 ,p_attribute11 => p_attribute11
1724 ,p_attribute12 => p_attribute12
1725 ,p_attribute13 => p_attribute13
1726 ,p_attribute14 => p_attribute14
1727 ,p_attribute15 => p_attribute15
1728 ,p_attribute16 => p_attribute16
1729 ,p_attribute17 => p_attribute17
1730 ,p_attribute18 => p_attribute18
1731 ,p_attribute19 => p_attribute19
1732 ,p_attribute20 => p_attribute20
1733 ,p_object_version_number => p_object_version_number
1734 ,p_multiple_components => p_multiple_components
1735 ,p_approved => p_approved
1736 ,p_validate => p_validate
1737 ,p_inv_next_sal_date_warning => p_inv_next_sal_date_warning
1738 ,p_proposed_salary_warning => p_proposed_salary_warning
1739 ,p_approved_warning => p_approved_warning
1740 ,p_payroll_warning => p_payroll_warning
1741 );
1742 --
1743 end update_salary_proposal;
1744 --
1745 --
1746 -- ----------------------------------------------------------------------------
1747 -- |----------------------< approve_salary_proposal >--------------------------|
1748 -- ----------------------------------------------------------------------------
1749 --
1750 Procedure approve_salary_proposal(
1751 p_pay_proposal_id in number,
1752 p_change_date in date,
1753 p_proposed_salary_n in number,
1754 p_object_version_number in out nocopy number,
1755 p_validate in boolean,
1756 p_inv_next_sal_date_warning out nocopy boolean,
1757 p_proposed_salary_warning out nocopy boolean,
1758 p_approved_warning out nocopy boolean,
1759 p_payroll_warning out nocopy boolean,
1760 p_error_text out nocopy varchar2) is
1761 --
1762 l_proc varchar2(72) := g_package||'approve_salary_proposal';
1763 l_change_date per_pay_proposals.change_date%TYPE;
1764 l_object_version_number per_pay_proposals.object_version_number%TYPE;
1765 l_element_entry_id pay_element_entries_f.element_entry_id%TYPE;
1766 l_inv_next_sal_date_warning boolean;
1767 l_proposed_salary_warning boolean;
1768 l_approved_warning boolean;
1769 l_payroll_warning boolean;
1770 l_error_text varchar2(72);
1771 l_assignment_id per_pay_proposals.assignment_id%type;
1772 l_proposed_salary_n per_pay_proposals.proposed_salary_n%TYPE;
1773 l_date_to per_pay_proposals.date_to%TYPE;
1774 l_temp_ovn number := p_object_version_number;
1775 --
1776 --
1777 -- cursor get_element IS
1778 -- select pee.element_entry_id
1779 -- , pyp.assignment_id
1780 -- from pay_element_entries_f pee
1781 -- , per_pay_proposals pyp
1782 -- where pyp.pay_proposal_id=p_pay_proposal_id
1783 -- and pee.assignment_id=pyp.assignment_id
1784 -- and NVL(l_change_date,to_date('31-12-4127','DD-MM-YYYY')) between
1785 -- pee.effective_start_date and pee.effective_end_date
1786 -- and pee.creator_type='SP';
1787 --
1788 -- Bug 1732348 Fix
1789
1790 cursor get_element IS
1791 select pee.element_entry_id
1792 from pay_element_entries_f pee
1793 where pee.assignment_id=l_assignment_id
1794 and NVL(l_change_date,to_date('31-12-4127','DD-MM-YYYY')) between
1795 pee.effective_start_date and pee.effective_end_date
1796 and pee.creator_type='SP';
1797
1798 cursor csr_get_date_to
1799 is
1800 select date_to
1801 from per_pay_proposals
1802 where pay_proposal_id = p_pay_proposal_id;
1803
1804 begin
1805 hr_utility.set_location('Entering:'||l_proc,5);
1806 --
1807 -- Issue a savepoint
1808 --
1809 savepoint approve_salary_proposal;
1810 --
1811 -- Process Logic
1812 --
1813 l_object_version_number := p_object_version_number;
1814 --
1815 -- get the old record details
1816 --
1817 per_pyp_shd.lck
1818 (p_pay_proposal_id => p_pay_proposal_id
1819 ,p_object_version_number => p_object_version_number);
1820 --
1821 if p_change_date = hr_api.g_date then
1822 l_change_date:=per_pyp_shd.g_old_rec.change_date;
1823 else
1824 l_change_date := trunc(p_change_date);
1825 end if;
1826 --
1827 -- Call Before Process User Hook for approve_salary_proposal
1828 --
1829 begin
1830 hr_maintain_proposal_bk3.approve_salary_proposal_b
1831 (
1832 p_pay_proposal_id => p_pay_proposal_id,
1833 p_change_date => l_change_date,
1834 p_proposed_salary_n => p_proposed_salary_n,
1835 p_object_version_number => p_object_version_number
1836 );
1837 exception
1838 when hr_api.cannot_find_prog_unit then
1839 hr_api.cannot_find_prog_unit_error
1840 (p_module_name => 'APPROVE_SALARY_PROPOSAL'
1841 ,p_hook_type => 'BP'
1842 );
1843 --
1844 -- End of the after hook for approve_salary_proposal
1845 --
1846 end;
1847 --
1848 --
1849 /* last approved proposal is end-dated if proposals are
1850 approved through SalMgmt screen. Bug#7386307 by schowdhu
1851 */
1852 l_assignment_id:=per_pyp_shd.g_old_rec.assignment_id;
1853 --
1854 if (nvl(per_pyp_shd.g_old_rec.approved,'N')<>'Y') then
1855 end_date_approved_proposal(l_assignment_id, l_change_date-1, p_pay_proposal_id);
1856 end if;
1857 update_last_change_date(l_assignment_id, l_change_date);
1858 --
1859
1860 if(nvl(per_pyp_shd.g_old_rec.approved,'N')<>'Y') THEN
1861 --
1862 -- only do this if the salary is not approved.
1863 --
1864 per_pyp_upd.upd
1865 (p_pay_proposal_id => p_pay_proposal_id
1866 ,p_change_date => l_change_date
1867 ,p_object_version_number => l_object_version_number
1868 ,p_proposed_salary_n => p_proposed_salary_n
1869 ,p_approved => 'Y'
1870 ,p_validate => false
1871 ,p_inv_next_sal_date_warning
1872 => l_inv_next_sal_date_warning
1873 ,p_proposed_salary_warning => l_proposed_salary_warning
1874 ,p_approved_warning => l_approved_warning
1875 ,p_payroll_warning => l_payroll_warning
1876 );
1877
1878 -- Now we maintain an element entry for this proposal
1879 -- by calling the maintain_elements_bsp.
1880 --
1881 hr_utility.set_location(l_proc,10);
1882 --
1883 -- Bug 1732348 Fix
1884 l_assignment_id:=per_pyp_shd.g_old_rec.assignment_id;
1885 open get_element;
1886 fetch get_element into l_element_entry_id; -- ,l_assignment_id;
1887 close get_element;
1888 --
1889 --
1890 if p_proposed_salary_n = hr_api.g_number then
1891 l_proposed_salary_n:=per_pyp_shd.g_old_rec.proposed_salary_n;
1892 else
1893 l_proposed_salary_n:=p_proposed_salary_n;
1894 end if;
1895 --
1896 maintain_elements_bsp(p_pay_proposal_id
1897 ,l_assignment_id
1898 ,l_change_date
1899 ,l_date_to
1900 ,l_element_entry_id
1901 ,l_proposed_salary_n);
1902 --
1903 --
1904 end if;
1905 --
1906 --
1907 --
1908 -- Call After Process User Hook for approve_salary_proposal
1909 --
1910 begin
1911 hr_maintain_proposal_bk3.approve_salary_proposal_a
1912 (
1913 p_pay_proposal_id => p_pay_proposal_id,
1914 p_change_date => l_change_date,
1915 p_proposed_salary_n => p_proposed_salary_n,
1916 p_object_version_number => l_object_version_number,
1917 p_inv_next_sal_date_warning => l_inv_next_sal_date_warning,
1918 p_proposed_salary_warning => l_proposed_salary_warning,
1919 p_approved_warning => l_approved_warning,
1920 p_payroll_warning => l_payroll_warning,
1921 p_error_text => l_error_text
1922 );
1923 exception
1924 when hr_api.cannot_find_prog_unit then
1925 hr_api.cannot_find_prog_unit_error
1926 (p_module_name => 'APPROVE_SALARY_PROPOSAL'
1927 ,p_hook_type => 'AP'
1928 );
1929 --
1930 -- End of the after hook for update_salary_proposal
1931 --
1932 end;
1933 --
1934 -- If we are validating then raise the Validate_Enabled exception
1935 If p_validate then
1936 Raise HR_Api.Validate_Enabled;
1937 End If;
1938 --
1939 --
1940 -- Set OUT parameters
1941 --
1942 p_object_version_number := l_object_version_number;
1943 p_inv_next_sal_date_warning := l_inv_next_sal_date_warning;
1944 p_proposed_salary_warning := l_proposed_salary_warning;
1945 p_approved_warning := l_approved_warning;
1946 p_payroll_warning := l_payroll_warning;
1947 p_error_text := l_error_text;
1948 --
1949 hr_utility.set_location('Leaving: '||l_proc,15);
1950 --
1951 Exception
1952 When HR_Api.Validate_Enabled Then
1953 --
1954 -- As the Validate_Enabled exception has been raised
1955 -- we must rollback to the savepoint
1956 --
1957 ROLLBACK TO approve_salary_proposal;
1958 --
1959 -- Only set output warning arguments
1960 -- (Any key or derived arguments must be set to null
1961 -- when validation only mode is being used.)
1962 --
1963 p_inv_next_sal_date_warning := l_inv_next_sal_date_warning;
1964 p_proposed_salary_warning := l_proposed_salary_warning;
1965 p_approved_warning := l_approved_warning;
1966 p_payroll_warning := l_payroll_warning;
1967 p_error_text := null;
1968 p_object_version_number := l_temp_ovn;
1969 --
1970 hr_utility.set_location(' Leaving:'||l_proc, 20);
1971 --
1972 When others then
1973 --
1974 -- A validation or unexpected error has occured
1975 --
1976 ROLLBACK TO approve_salary_proposal;
1977 --
1978 -- Reset IN OUT and set OUT parameters.
1979 p_inv_next_sal_date_warning := null;
1980 p_proposed_salary_warning := null;
1981 p_approved_warning := null;
1982 p_payroll_warning := null;
1983 p_object_version_number := l_temp_ovn;
1984 p_error_text := sqlerrm;
1985 --
1986 hr_utility.set_location(' Leaving:'||l_proc, 25);
1987 -- raise;
1988 --
1989 end approve_salary_proposal;
1990 --
1991 -------------------------------------------------------------------------------
1992 -- |----------------------< delete_salary_proposal >--------------------------|
1993 -- ----------------------------------------------------------------------------
1994 --
1995 /* procedure to delete a complete salary proposal, including it's components
1996 */
1997 Procedure delete_salary_proposal(p_pay_proposal_id in number
1998 ,p_business_group_id in number
1999 ,p_object_version_number in number
2000 ,p_validate in boolean
2001 ,p_salary_warning out nocopy boolean) is
2002 --
2003 l_salary_warning boolean;
2004 l_proc varchar2(72) := g_package||'delete_salary_proposal';
2005 l_element_entry_id pay_element_entries_f.element_entry_id%TYPE;
2006 l_change_date date;
2007 v_zap_entry boolean;
2008 v_delete_next_change boolean;
2009 v_zap VARCHAR2(1):='N';
2010 l_approved per_pay_proposals.approved%TYPE;
2011 l_future_element_entries number;
2012 l_assignment_id number;
2013 l_last_change_date date;
2014 --
2015 cursor components is
2016 select ppc.component_id
2017 , ppc.object_version_number
2018 from per_pay_proposal_components ppc
2019 where ppc.pay_proposal_id=p_pay_proposal_id
2020 and ppc.business_group_id=p_business_group_id;
2021
2022 cursor elements is
2023 select pee.element_entry_id
2024 , pro.change_date
2025 from per_pay_proposals_v2 pro
2026 , pay_element_entries_f pee
2027 where pro.pay_proposal_id=p_pay_proposal_id
2028 and pro.assignment_id=pee.assignment_id
2029 and pee.creator_type='SP'
2030 and pro.change_date between pee.effective_start_date
2031 and pee.effective_end_date;
2032
2033 Cursor csr_future_element_entries(p_change_date in date,p_element_entry_id in number,p_pay_proposal_id in number)
2034 IS
2035 select count(*)
2036 from pay_element_entries_f
2037 where element_entry_id = p_element_entry_id
2038 and effective_start_date > p_change_date
2039 and creator_id <> p_pay_proposal_id; --vkodedal 01-aug-2010 10062799
2040
2041 Cursor csr_proposal_details
2042 IS
2043 select approved, last_change_date, assignment_id
2044 from per_pay_proposals
2045 where pay_proposal_id = p_pay_proposal_id;
2046
2047
2048 begin
2049 hr_utility.set_location('Entering:'||l_proc,5);
2050 --
2051 --
2052 -- Issue a savepoint
2053 --
2054 savepoint delete_salary_proposal;
2055 --
2056 --
2057 -- Call Before Process User Hook for delete_salary_proposal
2058 --
2059 begin
2060 hr_maintain_proposal_bk4.delete_salary_proposal_b
2061 (
2062 p_pay_proposal_id => p_pay_proposal_id
2063 ,p_business_group_id => p_business_group_id
2064 ,p_object_version_number => p_object_version_number
2065 );
2066 exception
2067 when hr_api.cannot_find_prog_unit then
2068 hr_api.cannot_find_prog_unit_error
2069 (p_module_name => 'DELETE_SALARY_PROPOSAL'
2070 ,p_hook_type => 'BP'
2071 );
2072 --
2073 -- End of the before hook for delete_salary_proposal
2074 --
2075 end;
2076 --
2077 --
2078 -- first of all delete all of the components
2079 --
2080 FOR l_components IN components LOOP
2081 per_ppc_del.del(p_component_id=>l_components.component_id
2082 ,p_object_version_number=>l_components.object_version_number
2083 ,p_validation_strength=>'WEAK'
2084 ,p_validate=>FALSE);
2085 END LOOP;
2086 hr_utility.set_location(l_proc,10);
2087
2088 OPEN Csr_proposal_details;
2089 fetch Csr_proposal_details into l_approved,l_last_change_date, l_assignment_id;
2090 close Csr_proposal_details;
2091 --
2092 if l_approved = 'Y' then
2093
2094 open elements;
2095 fetch elements into l_element_entry_id,l_change_date;
2096 close elements;
2097 --
2098 -- delete the element entries
2099 --
2100 if (l_element_entry_id is not null) then
2101 --
2102 hr_utility.set_location(l_proc,15);
2103 begin
2104 --
2105 select 'Y'
2106 into v_zap
2107 from sys.dual
2108 where exists
2109 (select 1
2110 from sys.dual
2111 where l_change_date =
2112 (select min(effective_start_date)
2113 from pay_element_entries_f
2114 where element_entry_id = l_element_entry_id));
2115 --
2116 exception
2117 when no_data_found
2118 then
2119 v_delete_next_change := TRUE;
2120 hr_utility.set_location(l_proc,20);
2121 --
2122 end;
2123 --
2124 v_zap_entry := (v_zap = 'Y');
2125 end if;
2126
2127 end if;
2128 --
2129 -- Extend date_to of previous Salary Proposal
2130 --added by vkodedal to fix 6001309
2131 hr_general.g_data_migrator_mode := 'Y';
2132 --
2133 extend_salary_proposal_to_date(p_pay_proposal_id);
2134 --
2135
2136 -- added by schowdhu to fix 7205132
2137 update_last_change_date(l_assignment_id, l_last_change_date);
2138
2139 hr_general.g_data_migrator_mode := 'N';
2140 --
2141 --
2142
2143 -- then delete the proposal
2144 --
2145 hr_utility.set_location(l_proc,25);
2146 per_pyp_del.del(p_pay_proposal_id=>p_pay_proposal_id
2147 ,p_object_version_number=>p_object_version_number
2148 ,p_validate=>FALSE
2149 ,p_salary_warning=>l_salary_warning);
2150 --
2151
2152 if l_approved = 'Y' then
2153 if (v_zap_entry = TRUE)
2154 then
2155
2156 OPEN csr_future_element_entries(l_change_date,l_element_entry_id,p_pay_proposal_id);
2157 FETCH csr_future_element_entries into l_future_element_entries;
2158 CLOSE csr_future_element_entries;
2159
2160 if l_future_element_entries > 0 then
2161
2162 hr_utility.set_message(800,'PER_SAL_FIRST_PROPOSAL_DELETE');
2163 hr_utility.raise_error;
2164
2165 end if;
2166
2167
2168 hr_utility.set_location(l_proc,30);
2169 -- call API to zap entry
2170 hr_entry_api.delete_element_entry
2171 ('ZAP',
2172 l_change_date,
2173 l_element_entry_id);
2174 elsif (v_delete_next_change = TRUE)
2175 then
2176 hr_utility.set_location(l_proc,35);
2177 -- Call API to do a 'DELETE_NEXT_CHANGE'
2178 hr_entry_api.delete_element_entry
2179 ('DELETE_NEXT_CHANGE',
2180 l_change_date - 1,
2181 l_element_entry_id);
2182 end if;
2183 end if;
2184 --
2185 -- Call After Process User Hook for delete_salary_proposal
2186 --
2187 begin
2188 hr_maintain_proposal_bk4.delete_salary_proposal_a
2189 (
2190 p_pay_proposal_id => p_pay_proposal_id
2191 ,p_business_group_id => p_business_group_id
2192 ,p_object_version_number => p_object_version_number
2193 ,p_salary_warning => l_salary_warning
2194 );
2195 exception
2196 when hr_api.cannot_find_prog_unit then
2197 hr_api.cannot_find_prog_unit_error
2198 (p_module_name => 'DELETE_SALARY_PROPOSAL'
2199 ,p_hook_type => 'AP'
2200 );
2201 --
2202 -- End of the after hook for delete_salary_proposal
2203 --
2204 end;
2205 --
2206 ----------vkodedal 7-mar-07
2207 -- Update the last_change_date for the next proposal
2208 --
2209 -- commented out by schowdhu to fix 7205132
2210 -- update_last_change_date(l_assignment_id, l_last_change_date);
2211 --
2212 -- If we are validating then raise the Validate_Enabled exception
2213 --
2214 If p_validate then
2215 Raise HR_Api.Validate_Enabled;
2216 End If;
2217 --
2218 -- Set OUT parameters
2219 --
2220 p_salary_warning := l_salary_warning;
2221 --
2222 hr_utility.set_location('Leaving: '||l_proc,40);
2223 --
2224 Exception
2225 When HR_Api.Validate_Enabled Then
2226 --
2227 -- As the Validate_Enabled exception has been raised
2228 -- we must rollback to the savepoint
2229 --
2230 ROLLBACK TO delete_salary_proposal;
2231 --
2232 -- Only set output warning arguments
2233 -- (Any key or derived arguments must be set to null
2234 -- when validation only mode is being used.)
2235 --
2236 p_salary_warning := l_salary_warning;
2237
2238 When others then
2239 --
2240 -- A validation or unexpected error has occured
2241 --
2242 ROLLBACK TO delete_salary_proposal;
2243 p_salary_warning := null;
2244 raise;
2245 --
2246 end delete_salary_proposal;
2247
2248 --
2249 --
2250 -- ----------------------------------------------------------------------------
2251 -- |---------------------< insert_proposal_component >------------------------|
2252 -- ----------------------------------------------------------------------------
2253 --
2254 procedure insert_proposal_component(
2255 p_component_id out nocopy number,
2256 p_pay_proposal_id in number,
2257 p_business_group_id in number,
2258 p_approved in varchar2,
2259 p_component_reason in varchar2,
2260 p_change_amount_n in number,
2261 p_change_percentage in number,
2262 p_comments in varchar2,
2263 p_attribute_category in varchar2,
2264 p_attribute1 in varchar2,
2265 p_attribute2 in varchar2,
2266 p_attribute3 in varchar2,
2267 p_attribute4 in varchar2,
2268 p_attribute5 in varchar2,
2269 p_attribute6 in varchar2,
2270 p_attribute7 in varchar2,
2271 p_attribute8 in varchar2,
2272 p_attribute9 in varchar2,
2273 p_attribute10 in varchar2,
2274 p_attribute11 in varchar2,
2275 p_attribute12 in varchar2,
2276 p_attribute13 in varchar2,
2277 p_attribute14 in varchar2,
2278 p_attribute15 in varchar2,
2279 p_attribute16 in varchar2,
2280 p_attribute17 in varchar2,
2281 p_attribute18 in varchar2,
2282 p_attribute19 in varchar2,
2283 p_attribute20 in varchar2,
2284 p_validation_strength in varchar2,
2285 p_object_version_number out nocopy number,
2286 p_validate in boolean
2287 ) is
2288 --
2289 l_proc varchar2(72) := g_package||'insert_proposal_component';
2290 l_component_id per_pay_proposal_components.component_id%TYPE;
2291 l_object_version_number per_pay_proposal_components.object_version_number%TYPE;
2292 --
2293 begin
2294 hr_utility.set_location('Entering:'||l_proc,5);
2295 --
2296 --
2297 -- Issue a savepoint
2298 --
2299 savepoint insert_proposal_components;
2300 --
2301 --
2302 -- Call Before Process User Hook for insert_proposal_components
2303 --
2304 begin
2305 hr_maintain_proposal_bk5.insert_proposal_component_b
2306 (
2307 p_pay_proposal_id => p_pay_proposal_id,
2308 p_business_group_id => p_business_group_id,
2309 p_approved => p_approved,
2310 p_component_reason => p_component_reason,
2311 p_change_amount_n => p_change_amount_n,
2312 p_change_percentage => p_change_percentage,
2313 p_comments => p_comments,
2314 p_attribute_category => p_attribute_category,
2315 p_attribute1 => p_attribute1,
2316 p_attribute2 => p_attribute2,
2317 p_attribute3 => p_attribute3,
2318 p_attribute4 => p_attribute4,
2319 p_attribute5 => p_attribute5,
2320 p_attribute6 => p_attribute6,
2321 p_attribute7 => p_attribute7,
2322 p_attribute8 => p_attribute8,
2323 p_attribute9 => p_attribute9,
2324 p_attribute10 => p_attribute10,
2325 p_attribute11 => p_attribute11,
2326 p_attribute12 => p_attribute12,
2327 p_attribute13 => p_attribute13,
2328 p_attribute14 => p_attribute14,
2329 p_attribute15 => p_attribute15,
2330 p_attribute16 => p_attribute16,
2331 p_attribute17 => p_attribute17,
2332 p_attribute18 => p_attribute18,
2333 p_attribute19 => p_attribute19,
2334 p_attribute20 => p_attribute20,
2335 p_validation_strength => p_validation_strength
2336 );
2337 exception
2338 when hr_api.cannot_find_prog_unit then
2339 hr_api.cannot_find_prog_unit_error
2340 (p_module_name => 'INSERT_PROPOSAL_COMPONENTS'
2341 ,p_hook_type => 'BP'
2342 );
2343 --
2344 -- End of the after hook for insert_proposal_components
2345 --
2346 end;
2347 --
2348 --
2349 per_ppc_ins.ins (
2350 p_component_id => l_component_id
2351 ,p_pay_proposal_id => p_pay_proposal_id
2352 ,p_business_group_id => p_business_group_id
2353 ,p_approved => p_approved
2354 ,p_component_reason => p_component_reason
2355 ,p_change_amount_n => p_change_amount_n
2356 ,p_change_percentage => p_change_percentage
2357 ,p_comments => p_comments
2358 ,p_attribute_category => p_attribute_category
2359 ,p_attribute1 => p_attribute1
2360 ,p_attribute2 => p_attribute2
2361 ,p_attribute3 => p_attribute3
2362 ,p_attribute4 => p_attribute4
2363 ,p_attribute5 => p_attribute5
2364 ,p_attribute6 => p_attribute6
2365 ,p_attribute7 => p_attribute7
2366 ,p_attribute8 => p_attribute8
2367 ,p_attribute9 => p_attribute9
2368 ,p_attribute10 => p_attribute10
2369 ,p_attribute11 => p_attribute11
2370 ,p_attribute12 => p_attribute12
2371 ,p_attribute13 => p_attribute13
2372 ,p_attribute14 => p_attribute14
2373 ,p_attribute15 => p_attribute15
2374 ,p_attribute16 => p_attribute16
2375 ,p_attribute17 => p_attribute17
2376 ,p_attribute18 => p_attribute18
2377 ,p_attribute19 => p_attribute19
2378 ,p_attribute20 => p_attribute20
2379 ,p_object_version_number => l_object_version_number
2380 ,p_validation_strength => p_validation_strength
2381 ,p_validate => FALSE);
2382 --
2383 --
2384 -- Call After Process User Hook for insert_proposal_components
2385 --
2386 begin
2387 hr_maintain_proposal_bk5.insert_proposal_component_a
2388 (
2389 p_component_id => l_component_id,
2390 p_pay_proposal_id => p_pay_proposal_id,
2391 p_business_group_id => p_business_group_id,
2392 p_approved => p_approved,
2393 p_component_reason => p_component_reason,
2394 p_change_amount_n => p_change_amount_n,
2395 p_change_percentage => p_change_percentage,
2396 p_comments => p_comments,
2397 p_attribute_category => p_attribute_category,
2398 p_attribute1 => p_attribute1,
2399 p_attribute2 => p_attribute2,
2400 p_attribute3 => p_attribute3,
2401 p_attribute4 => p_attribute4,
2402 p_attribute5 => p_attribute5,
2403 p_attribute6 => p_attribute6,
2404 p_attribute7 => p_attribute7,
2405 p_attribute8 => p_attribute8,
2406 p_attribute9 => p_attribute9,
2407 p_attribute10 => p_attribute10,
2408 p_attribute11 => p_attribute11,
2409 p_attribute12 => p_attribute12,
2410 p_attribute13 => p_attribute13,
2411 p_attribute14 => p_attribute14,
2412 p_attribute15 => p_attribute15,
2413 p_attribute16 => p_attribute16,
2414 p_attribute17 => p_attribute17,
2415 p_attribute18 => p_attribute18,
2416 p_attribute19 => p_attribute19,
2417 p_attribute20 => p_attribute20,
2418 p_validation_strength => p_validation_strength,
2419 p_object_version_number => l_object_version_number
2420 );
2421 exception
2422 when hr_api.cannot_find_prog_unit then
2423 hr_api.cannot_find_prog_unit_error
2424 (p_module_name => 'INSERT_PROPOSAL_COMPONENTS'
2425 ,p_hook_type => 'AP'
2426 );
2427 --
2428 -- End of the after hook for insert_proposal_components
2429 --
2430 end;
2431 --
2432 -- If we are validating then raise the Validate_Enabled exception
2433 --
2434 If p_validate then
2435 Raise HR_Api.Validate_Enabled;
2436 End If;
2437 --
2438 -- Set OUT parameters
2439 --
2440 p_component_id := l_component_id;
2441 p_object_version_number := l_object_version_number;
2442 --
2443 hr_utility.set_location('Leaving: '||l_proc,10);
2444 --
2445 Exception
2446 When HR_Api.Validate_Enabled Then
2447 --
2448 -- As the Validate_Enabled exception has been raised
2449 -- we must rollback to the savepoint
2450 --
2451 ROLLBACK TO insert_proposal_components;
2452 --
2453 -- Only set output warning arguments
2454 -- (Any key or derived arguments must be set to null
2455 -- when validation only mode is being used.)
2456 --
2457 p_component_id := null;
2458 p_object_version_number := null;
2459 --
2460 When others then
2461 --
2462 -- A validation or unexpected error has occured
2463 --
2464 ROLLBACK TO insert_proposal_components;
2465 -- Set OUT parameters.
2466 p_component_id := null;
2467 p_object_version_number := null;
2468 raise;
2469 --
2470 end insert_proposal_component;
2471 --
2472 -- ----------------------------------------------------------------------------
2473 -- |---------------------< update_proposal_component >------------------------|
2474 -- ----------------------------------------------------------------------------
2475 --
2476 procedure update_proposal_component(
2477 p_component_id in number,
2478 p_approved in varchar2,
2479 p_component_reason in varchar2,
2480 p_change_amount_n in number,
2481 p_change_percentage in number,
2482 p_comments in varchar2,
2483 p_attribute_category in varchar2,
2484 p_attribute1 in varchar2,
2485 p_attribute2 in varchar2,
2486 p_attribute3 in varchar2,
2487 p_attribute4 in varchar2,
2488 p_attribute5 in varchar2,
2489 p_attribute6 in varchar2,
2490 p_attribute7 in varchar2,
2491 p_attribute8 in varchar2,
2492 p_attribute9 in varchar2,
2493 p_attribute10 in varchar2,
2494 p_attribute11 in varchar2,
2495 p_attribute12 in varchar2,
2496 p_attribute13 in varchar2,
2497 p_attribute14 in varchar2,
2498 p_attribute15 in varchar2,
2499 p_attribute16 in varchar2,
2500 p_attribute17 in varchar2,
2501 p_attribute18 in varchar2,
2502 p_attribute19 in varchar2,
2503 p_attribute20 in varchar2,
2504 p_validation_strength in varchar2,
2505 p_object_version_number in out nocopy number,
2506 p_validate in boolean
2507 ) is
2508 --
2509 l_proc varchar2(72) := g_package||'update_proposal_component';
2510 l_object_version_number per_pay_proposal_components.object_version_number%TYPE;
2511 l_temp_ovn number := p_object_version_number;
2512 --
2513 begin
2514 hr_utility.set_location('Entering:'||l_proc,10);
2515 --
2516 --
2517 -- Issue a savepoint
2518 --
2519 savepoint update_proposal_components;
2520 --
2521 -- Process Logic
2522 --
2523 l_object_version_number := p_object_version_number;
2524 --
2525 -- Call Before Process User Hook for update_proposal_components
2526 --
2527 begin
2528 hr_maintain_proposal_bk6.update_proposal_component_b
2529 (
2530 p_component_id => p_component_id,
2531 p_approved => p_approved,
2532 p_component_reason => p_component_reason,
2533 p_change_amount_n => p_change_amount_n,
2534 p_change_percentage => p_change_percentage,
2535 p_comments => p_comments,
2536 p_attribute_category => p_attribute_category,
2537 p_attribute1 => p_attribute1,
2538 p_attribute2 => p_attribute2,
2539 p_attribute3 => p_attribute3,
2540 p_attribute4 => p_attribute4,
2541 p_attribute5 => p_attribute5,
2542 p_attribute6 => p_attribute6,
2543 p_attribute7 => p_attribute7,
2544 p_attribute8 => p_attribute8,
2545 p_attribute9 => p_attribute9,
2546 p_attribute10 => p_attribute10,
2547 p_attribute11 => p_attribute11,
2548 p_attribute12 => p_attribute12,
2549 p_attribute13 => p_attribute13,
2550 p_attribute14 => p_attribute14,
2551 p_attribute15 => p_attribute15,
2552 p_attribute16 => p_attribute16,
2553 p_attribute17 => p_attribute17,
2554 p_attribute18 => p_attribute18,
2555 p_attribute19 => p_attribute19,
2556 p_attribute20 => p_attribute20,
2557 p_validation_strength => p_validation_strength,
2558 p_object_version_number => p_object_version_number
2559 );
2560 exception
2561 when hr_api.cannot_find_prog_unit then
2562 hr_api.cannot_find_prog_unit_error
2563 (p_module_name => 'UPDATE_PROPOSAL_COMPONENTS'
2564 ,p_hook_type => 'BP'
2565 );
2566 --
2567 -- End of the after hook for update_proposal_components
2568 --
2569 end;
2570 --
2571 --
2572 per_ppc_upd.upd (
2573 p_component_id => p_component_id
2574 ,p_approved => p_approved
2575 ,p_component_reason => p_component_reason
2576 ,p_change_amount_n => p_change_amount_n
2577 ,p_change_percentage => p_change_percentage
2578 ,p_comments => p_comments
2579 ,p_attribute_category => p_attribute_category
2580 ,p_attribute1 => p_attribute1
2581 ,p_attribute2 => p_attribute2
2582 ,p_attribute3 => p_attribute3
2583 ,p_attribute4 => p_attribute4
2584 ,p_attribute5 => p_attribute5
2585 ,p_attribute6 => p_attribute6
2586 ,p_attribute7 => p_attribute7
2587 ,p_attribute8 => p_attribute8
2588 ,p_attribute9 => p_attribute9
2589 ,p_attribute10 => p_attribute10
2590 ,p_attribute11 => p_attribute11
2591 ,p_attribute12 => p_attribute12
2592 ,p_attribute13 => p_attribute13
2593 ,p_attribute14 => p_attribute14
2594 ,p_attribute15 => p_attribute15
2595 ,p_attribute16 => p_attribute16
2596 ,p_attribute17 => p_attribute17
2597 ,p_attribute18 => p_attribute18
2598 ,p_attribute19 => p_attribute19
2599 ,p_attribute20 => p_attribute20
2600 ,p_object_version_number => l_object_version_number
2601 ,p_validation_strength => p_validation_strength
2602 ,p_validate => FALSE);
2603 --
2604 --
2605 -- Call After Process User Hook for update_proposal_components
2606 --
2607 begin
2608 hr_maintain_proposal_bk6.update_proposal_component_a
2609 (
2610 p_component_id => p_component_id,
2611 p_approved => p_approved,
2612 p_component_reason => p_component_reason,
2613 p_change_amount_n => p_change_amount_n,
2614 p_change_percentage => p_change_percentage,
2615 p_comments => p_comments,
2616 p_attribute_category => p_attribute_category,
2617 p_attribute1 => p_attribute1,
2618 p_attribute2 => p_attribute2,
2619 p_attribute3 => p_attribute3,
2620 p_attribute4 => p_attribute4,
2621 p_attribute5 => p_attribute5,
2622 p_attribute6 => p_attribute6,
2623 p_attribute7 => p_attribute7,
2624 p_attribute8 => p_attribute8,
2625 p_attribute9 => p_attribute9,
2626 p_attribute10 => p_attribute10,
2627 p_attribute11 => p_attribute11,
2628 p_attribute12 => p_attribute12,
2629 p_attribute13 => p_attribute13,
2630 p_attribute14 => p_attribute14,
2631 p_attribute15 => p_attribute15,
2632 p_attribute16 => p_attribute16,
2633 p_attribute17 => p_attribute17,
2634 p_attribute18 => p_attribute18,
2635 p_attribute19 => p_attribute19,
2636 p_attribute20 => p_attribute20,
2637 p_validation_strength => p_validation_strength,
2638 p_object_version_number => l_object_version_number
2639 );
2640 exception
2641 when hr_api.cannot_find_prog_unit then
2642 hr_api.cannot_find_prog_unit_error
2643 (p_module_name => 'UPDATE_PROPOSAL_COMPONENTS'
2644 ,p_hook_type => 'AP'
2645 );
2646 --
2647 -- End of the after hook for update_proposal_components
2648 --
2649 end;
2650 --
2651 -- If we are validating then raise the Validate_Enabled exception
2652 --
2653 If p_validate then
2654 Raise HR_Api.Validate_Enabled;
2655 End If;
2656 --
2657 -- Set OUT parameters
2658 --
2659 p_object_version_number := l_object_version_number;
2660 --
2661 hr_utility.set_location('Leaving: '||l_proc,20);
2662 --
2663 Exception
2664 When HR_Api.Validate_Enabled Then
2665 --
2666 -- As the Validate_Enabled exception has been raised
2667 -- we must rollback to the savepoint
2668 --
2669 ROLLBACK TO update_proposal_components;
2670 p_object_version_number := l_temp_ovn;
2671
2672 When others then
2673 --
2674 -- A validation or unexpected error has occured
2675 --
2676 ROLLBACK TO update_proposal_components;
2677 p_object_version_number := l_temp_ovn;
2678 hr_utility.set_location(' Leaving:'||l_proc, 25);
2679 raise;
2680 --
2681 end update_proposal_component;
2682 --
2683 -- ----------------------------------------------------------------------------
2684 -- |---------------------< delete_proposal_component >------------------------|
2685 -- ----------------------------------------------------------------------------
2686 --
2687 Procedure delete_proposal_component(
2688 p_component_id in number,
2689 p_validation_strength in varchar2,
2690 p_object_version_number in number,
2691 p_validate in boolean) is
2692 --
2693 l_proc varchar2(72) := g_package||'delete_proposal_component';
2694 --
2695 begin
2696 hr_utility.set_location('Entering:'||l_proc,5);
2697 --
2698 --
2699 -- Issue a savepoint
2700 --
2701 savepoint delete_proposal_components;
2702 --
2703 --
2704 -- Call Before Process User Hook for delete_proposal_components
2705 --
2706 begin
2707 hr_maintain_proposal_bk7.delete_proposal_component_b
2708 (
2709 p_component_id => p_component_id,
2710 p_validation_strength => p_validation_strength,
2711 p_object_version_number => p_object_version_number
2712 );
2713 exception
2714 when hr_api.cannot_find_prog_unit then
2715 hr_api.cannot_find_prog_unit_error
2716 (p_module_name => 'DELETE_PROPOSAL_COMPONENTS'
2717 ,p_hook_type => 'BP'
2718 );
2719 --
2720 -- End of the after hook for delete_proposal_components
2721 --
2722 end;
2723 --
2724 --
2725 per_ppc_del.del
2726 (p_component_id => p_component_id
2727 ,p_object_version_number => p_object_version_number
2728 ,p_validation_strength => p_validation_strength
2729 ,p_validate => FALSE);
2730 --
2731 --
2732 -- Call After Process User Hook for approve_salary_proposal
2733 --
2734 begin
2735 hr_maintain_proposal_bk7.delete_proposal_component_a
2736 (
2737 p_component_id => p_component_id,
2738 p_validation_strength => p_validation_strength,
2739 p_object_version_number => p_object_version_number
2740 );
2741 exception
2742 when hr_api.cannot_find_prog_unit then
2743 hr_api.cannot_find_prog_unit_error
2744 (p_module_name => 'DELETE_PROPOSAL_COMPONENTS'
2745 ,p_hook_type => 'AP'
2746 );
2747 --
2748 -- End of the after hook for delete_proposal_components
2749 --
2750 end;
2751 --
2752 -- If we are validating then raise the Validate_Enabled exception
2753 --
2754 If p_validate then
2755 Raise HR_Api.Validate_Enabled;
2756 End If;
2757 --
2758 hr_utility.set_location('Leaving: '||l_proc,10);
2759 --
2760 Exception
2761 When HR_Api.Validate_Enabled Then
2762 --
2763 -- As the Validate_Enabled exception has been raised
2764 -- we must rollback to the savepoint
2765 --
2766 ROLLBACK TO delete_proposal_components;
2767 When others then
2768 --
2769 -- A validation or unexpected error has occured
2770 --
2771 ROLLBACK TO delete_proposal_components;
2772 raise;
2773 --
2774 end delete_proposal_component;
2775 --
2776 -- ----------------------------------------------------------------------------
2777 -- |---------------------< cre_or_upd_salary_proposal >-----------------------|
2778 -- ----------------------------------------------------------------------------
2779 --
2780 procedure cre_or_upd_salary_proposal(
2781 p_validate in boolean,
2782 p_pay_proposal_id in out nocopy number,
2783 p_object_version_number in out nocopy number,
2784 p_business_group_id in number,
2785 p_assignment_id in number,
2786 p_change_date in date,
2787 p_comments in varchar2,
2788 p_next_sal_review_date in date,
2789 p_proposal_reason in varchar2,
2790 p_proposed_salary_n in number,
2791 p_forced_ranking in number,
2792 p_date_to in date,
2793 p_performance_review_id in number,
2794 p_attribute_category in varchar2,
2795 p_attribute1 in varchar2,
2796 p_attribute2 in varchar2,
2797 p_attribute3 in varchar2,
2798 p_attribute4 in varchar2,
2799 p_attribute5 in varchar2,
2800 p_attribute6 in varchar2,
2801 p_attribute7 in varchar2,
2802 p_attribute8 in varchar2,
2803 p_attribute9 in varchar2,
2804 p_attribute10 in varchar2,
2805 p_attribute11 in varchar2,
2806 p_attribute12 in varchar2,
2807 p_attribute13 in varchar2,
2808 p_attribute14 in varchar2,
2809 p_attribute15 in varchar2,
2810 p_attribute16 in varchar2,
2811 p_attribute17 in varchar2,
2812 p_attribute18 in varchar2,
2813 p_attribute19 in varchar2,
2814 p_attribute20 in varchar2,
2815 p_multiple_components in varchar2,
2816 p_approved in varchar2,
2817 p_inv_next_sal_date_warning out nocopy boolean,
2818 p_proposed_salary_warning out nocopy boolean,
2819 p_approved_warning out nocopy boolean,
2820 p_payroll_warning out nocopy boolean
2821 ) as
2822 l_proc varchar2(72) := g_package||'cre_or_upd_salary_proposal';
2823 l_api_updating boolean;
2824 l_pyp_rec per_pyp_shd.g_rec_type;
2825 l_null_pyp_rec per_pyp_shd.g_rec_type;
2826 l_inv_next_sal_date_warning boolean;
2827 l_proposed_salary_warning boolean;
2828 l_approved_warning boolean;
2829 l_payroll_warning boolean;
2830 l_element_entry_id number;
2831 l_dummy number;
2832 l_pay_proposal_id number;
2833 l_object_version_number number;
2834 l_date_to date := p_date_to;
2835 l_autoApprove varchar2(1); --added by vkodedal 10-Apr-2008 ER auto Approve first proposal
2836 l_temp_ovn number := p_object_version_number;
2837 l_temp_pay_proposal_id number := p_pay_proposal_id;
2838 --
2839 cursor first_proposal is
2840 select 1 from per_pay_proposals
2841 where assignment_id=p_assignment_id;
2842 --
2843 cursor asg_type is
2844 select asg.assignment_type
2845 from per_all_assignments_f asg
2846 where asg.assignment_id=p_assignment_id
2847 and p_change_date between asg.effective_start_date
2848 and asg.effective_end_date;
2849 --
2850 l_asg_type per_all_assignments_f.assignment_type%type;
2851 --
2852 begin
2853 --
2854 hr_utility.set_location('Entering:'|| l_proc, 10);
2855 --
2856 savepoint cre_or_upd_salary_proposal;
2857 --
2858
2859 -- Remember IN OUT parameters.
2860
2861 l_pay_proposal_id := p_pay_proposal_id;
2862 l_object_version_number := p_object_version_number;
2863
2864 l_api_updating := per_pyp_shd.api_updating
2865 (p_pay_proposal_id => p_pay_proposal_id
2866 ,p_object_version_number => p_object_version_number);
2867 hr_utility.set_location(l_proc, 20);
2868
2869 if (l_api_updating and l_date_to = hr_api.g_date) then
2870 l_date_to := hr_general.end_of_time;
2871 end if;
2872
2873 --
2874 -- set the record
2875 --
2876 l_pyp_rec :=
2877 per_pyp_shd.convert_args
2878 (p_pay_proposal_id
2879 ,p_assignment_id
2880 ,p_business_group_id
2881 ,p_change_date
2882 ,p_comments
2883 ,null
2884 ,p_next_sal_review_date
2885 ,p_proposal_reason
2886 ,p_proposed_salary_n
2887 ,p_forced_ranking
2888 ,l_date_to
2889 ,p_performance_review_id
2890 ,p_attribute_category
2891 ,p_attribute1
2892 ,p_attribute2
2893 ,p_attribute3
2894 ,p_attribute4
2895 ,p_attribute5
2896 ,p_attribute6
2897 ,p_attribute7
2898 ,p_attribute8
2899 ,p_attribute9
2900 ,p_attribute10
2901 ,p_attribute11
2902 ,p_attribute12
2903 ,p_attribute13
2904 ,p_attribute14
2905 ,p_attribute15
2906 ,p_attribute16
2907 ,p_attribute17
2908 ,p_attribute18
2909 ,p_attribute19
2910 ,p_attribute20
2911 ,p_object_version_number
2912 ,p_multiple_components
2913 ,p_approved
2914 );
2915 if not l_api_updating then
2916 --
2917 -- set g_old_rec to null
2918 --
2919 per_pyp_shd.g_old_rec:=l_null_pyp_rec;
2920 hr_utility.set_location(l_proc, 30);
2921 --
2922 -- convert the null values
2923 --
2924 hr_utility.set_location(l_proc, 40);
2925 per_pyp_upd.convert_defs(l_pyp_rec);
2926 --
2927 hr_utility.set_location(l_proc, 45);
2928 --
2929 open first_proposal;
2930 fetch first_proposal into l_dummy;
2931 if first_proposal%notfound then
2932 close first_proposal;
2933 l_pyp_rec.multiple_components:='N';
2934 open asg_type;
2935 fetch asg_type into l_asg_type;
2936 close asg_type;
2937 if l_asg_type='E' then
2938 --vkodedal 10-Apr-2008 ER to satisfy satutory requirement
2939 --Retain auto approve first proposal functionality if profile is null or set to Yes
2940
2941 l_autoApprove:=fnd_profile.value('HR_AUTO_APPROVE_FIRST_PROPOSAL');
2942 if(l_autoApprove is null or l_autoApprove ='Y') then
2943 hr_utility.set_location(l_proc, 47);
2944 l_pyp_rec.approved:='Y';
2945 end if;
2946
2947 else
2948 l_pyp_rec.approved:='N';
2949 end if;
2950 else
2951 close first_proposal;
2952 end if;
2953 -- insert the data
2954 --
2955 hr_utility.set_location(l_proc, 50);
2956 hr_maintain_proposal_api.insert_salary_proposal
2957 (p_pay_proposal_id => l_pyp_rec.pay_proposal_id
2958 ,p_assignment_id => l_pyp_rec.assignment_id
2959 ,p_business_group_id => l_pyp_rec.business_group_id
2960 ,p_change_date => l_pyp_rec.change_date
2961 ,p_comments => l_pyp_rec.comments
2962 ,p_next_sal_review_date => l_pyp_rec.next_sal_review_date
2963 ,p_proposal_reason => l_pyp_rec.proposal_reason
2964 ,p_proposed_salary_n => l_pyp_rec.proposed_salary_n
2965 ,p_forced_ranking => l_pyp_rec.forced_ranking
2966 ,p_date_to => l_pyp_rec.date_to
2967 ,p_performance_review_id => l_pyp_rec.performance_review_id
2968 ,p_attribute_category => l_pyp_rec.attribute_category
2969 ,p_attribute1 => l_pyp_rec.attribute1
2970 ,p_attribute2 => l_pyp_rec.attribute2
2971 ,p_attribute3 => l_pyp_rec.attribute3
2972 ,p_attribute4 => l_pyp_rec.attribute4
2973 ,p_attribute5 => l_pyp_rec.attribute5
2974 ,p_attribute6 => l_pyp_rec.attribute6
2975 ,p_attribute7 => l_pyp_rec.attribute7
2976 ,p_attribute8 => l_pyp_rec.attribute8
2977 ,p_attribute9 => l_pyp_rec.attribute9
2978 ,p_attribute10 => l_pyp_rec.attribute10
2979 ,p_attribute11 => l_pyp_rec.attribute11
2980 ,p_attribute12 => l_pyp_rec.attribute12
2981 ,p_attribute13 => l_pyp_rec.attribute13
2982 ,p_attribute14 => l_pyp_rec.attribute14
2983 ,p_attribute15 => l_pyp_rec.attribute15
2984 ,p_attribute16 => l_pyp_rec.attribute16
2985 ,p_attribute17 => l_pyp_rec.attribute17
2986 ,p_attribute18 => l_pyp_rec.attribute18
2987 ,p_attribute19 => l_pyp_rec.attribute19
2988 ,p_attribute20 => l_pyp_rec.attribute20
2989 ,p_object_version_number => l_pyp_rec.object_version_number
2990 ,p_multiple_components => nvl(l_pyp_rec.multiple_components,'N')
2991 ,p_approved => nvl(l_pyp_rec.approved,'N')
2992 ,p_validate => FALSE
2993 ,p_element_entry_id => l_element_entry_id
2994 ,p_inv_next_sal_date_warning => l_inv_next_sal_date_warning
2995 ,p_proposed_salary_warning => l_proposed_salary_warning
2996 ,p_approved_warning => l_approved_warning
2997 ,p_payroll_warning => l_payroll_warning
2998 );
2999 hr_utility.set_location(l_proc, 60);
3000 else
3001 --
3002 -- updating not inserting
3003 --
3004 hr_utility.set_location(l_proc, 70);
3005 per_pyp_shd.lck
3006 (p_pay_proposal_id => p_pay_proposal_id
3007 ,p_object_version_number => p_object_version_number);
3008 --
3009 -- convert the null values
3010 --
3011 hr_utility.set_location(l_proc, 80);
3012 per_pyp_upd.convert_defs(l_pyp_rec);
3013 --
3014 -- update the data
3015 --
3016 hr_utility.set_location(l_proc, 90);
3017 --
3018 hr_maintain_proposal_api.update_salary_proposal
3019 (p_pay_proposal_id => l_pyp_rec.pay_proposal_id
3020 ,p_change_date => l_pyp_rec.change_date
3021 ,p_comments => l_pyp_rec.comments
3022 ,p_next_sal_review_date => l_pyp_rec.next_sal_review_date
3023 ,p_proposal_reason => l_pyp_rec.proposal_reason
3024 ,p_proposed_salary_n => l_pyp_rec.proposed_salary_n
3025 ,p_forced_ranking => l_pyp_rec.forced_ranking
3026 ,p_date_to => l_pyp_rec.date_to
3027 ,p_performance_review_id => l_pyp_rec.performance_review_id
3028 ,p_attribute_category => l_pyp_rec.attribute_category
3029 ,p_attribute1 => l_pyp_rec.attribute1
3030 ,p_attribute2 => l_pyp_rec.attribute2
3031 ,p_attribute3 => l_pyp_rec.attribute3
3032 ,p_attribute4 => l_pyp_rec.attribute4
3033 ,p_attribute5 => l_pyp_rec.attribute5
3034 ,p_attribute6 => l_pyp_rec.attribute6
3035 ,p_attribute7 => l_pyp_rec.attribute7
3036 ,p_attribute8 => l_pyp_rec.attribute8
3037 ,p_attribute9 => l_pyp_rec.attribute9
3038 ,p_attribute10 => l_pyp_rec.attribute10
3039 ,p_attribute11 => l_pyp_rec.attribute11
3040 ,p_attribute12 => l_pyp_rec.attribute12
3041 ,p_attribute13 => l_pyp_rec.attribute13
3042 ,p_attribute14 => l_pyp_rec.attribute14
3043 ,p_attribute15 => l_pyp_rec.attribute15
3044 ,p_attribute16 => l_pyp_rec.attribute16
3045 ,p_attribute17 => l_pyp_rec.attribute17
3046 ,p_attribute18 => l_pyp_rec.attribute18
3047 ,p_attribute19 => l_pyp_rec.attribute19
3048 ,p_attribute20 => l_pyp_rec.attribute20
3049 ,p_object_version_number => l_pyp_rec.object_version_number
3050 ,p_multiple_components => l_pyp_rec.multiple_components
3051 ,p_approved => l_pyp_rec.approved
3052 ,p_validate => FALSE
3053 ,p_inv_next_sal_date_warning => l_inv_next_sal_date_warning
3054 ,p_proposed_salary_warning => l_proposed_salary_warning
3055 ,p_approved_warning => l_approved_warning
3056 ,p_payroll_warning => l_payroll_warning
3057 );
3058 --
3059 hr_utility.set_location(l_proc, 100);
3060 --
3061 end if;
3062 --
3063 if p_validate then
3064 raise hr_api.validate_enabled;
3065 end if;
3066 --
3067 hr_utility.set_location(l_proc, 110);
3068 --
3069 p_pay_proposal_id := l_pyp_rec.pay_proposal_id;
3070 p_object_version_number := l_pyp_rec.object_version_number;
3071 p_inv_next_sal_date_warning := l_inv_next_sal_date_warning;
3072 p_proposed_salary_warning := l_proposed_salary_warning;
3073 p_approved_warning := l_approved_warning;
3074 p_payroll_warning := l_payroll_warning;
3075 --
3076 hr_utility.set_location('Leaving:'||l_proc, 120);
3077 --
3078 exception
3079 when hr_api.validate_enabled then
3080 rollback to cre_or_upd_salary_proposal;
3081 p_pay_proposal_id := null;
3082 p_object_version_number := null;
3083 p_inv_next_sal_date_warning := l_inv_next_sal_date_warning;
3084 p_proposed_salary_warning := l_proposed_salary_warning;
3085 p_approved_warning := l_approved_warning;
3086 p_payroll_warning := l_payroll_warning;
3087 hr_utility.set_location('Leaving:'||l_proc, 130);
3088 when others then
3089 rollback to cre_or_upd_salary_proposal;
3090 -- Reset IN OUT and OUT parameters.
3091 p_pay_proposal_id := l_temp_pay_proposal_id;
3092 p_object_version_number := l_temp_ovn;
3093 p_inv_next_sal_date_warning := null;
3094 p_proposed_salary_warning := null;
3095 p_approved_warning := null;
3096 p_payroll_warning := null;
3097 hr_utility.set_location('Leaving:'||l_proc, 140);
3098 raise;
3099 --
3100 end cre_or_upd_salary_proposal;
3101 --
3102 -- ----------------------------------------------------------------------------
3103 -- |---------------------< cre_or_upd_salary_proposal >-----------------------|
3104 -- ---------------------------------------------------------------------------
3105 -- ------------------ Without the new date_to parameter ------------------
3106 -- ----------------------------------------------------------------------------
3107 --
3108 procedure cre_or_upd_salary_proposal(
3109 p_validate in boolean,
3110 p_pay_proposal_id in out nocopy number,
3111 p_object_version_number in out nocopy number,
3112 p_business_group_id in number,
3113 p_assignment_id in number,
3114 p_change_date in date,
3115 p_comments in varchar2,
3116 p_next_sal_review_date in date,
3117 p_proposal_reason in varchar2,
3118 p_proposed_salary_n in number,
3119 p_forced_ranking in number,
3120 p_performance_review_id in number,
3121 p_attribute_category in varchar2,
3122 p_attribute1 in varchar2,
3123 p_attribute2 in varchar2,
3124 p_attribute3 in varchar2,
3125 p_attribute4 in varchar2,
3126 p_attribute5 in varchar2,
3127 p_attribute6 in varchar2,
3128 p_attribute7 in varchar2,
3129 p_attribute8 in varchar2,
3130 p_attribute9 in varchar2,
3131 p_attribute10 in varchar2,
3132 p_attribute11 in varchar2,
3133 p_attribute12 in varchar2,
3134 p_attribute13 in varchar2,
3135 p_attribute14 in varchar2,
3136 p_attribute15 in varchar2,
3137 p_attribute16 in varchar2,
3138 p_attribute17 in varchar2,
3139 p_attribute18 in varchar2,
3140 p_attribute19 in varchar2,
3141 p_attribute20 in varchar2,
3142 p_multiple_components in varchar2,
3143 p_approved in varchar2,
3144 p_inv_next_sal_date_warning out nocopy boolean,
3145 p_proposed_salary_warning out nocopy boolean,
3146 p_approved_warning out nocopy boolean,
3147 p_payroll_warning out nocopy boolean
3148 ) as
3149
3150
3151
3152 Cursor csr_date_to
3153 IS
3154 select date_to
3155 from per_pay_proposals
3156 where pay_proposal_id = p_pay_proposal_id;
3157
3158 l_date_to PER_PAY_PROPOSALS.date_to%TYPE default null;
3159
3160 begin
3161
3162 if p_pay_proposal_id is not null then
3163
3164 OPEN csr_date_to;
3165 FETCH csr_date_to into l_date_to;
3166 CLOSE csr_date_to;
3167
3168 end if;
3169
3170 hr_maintain_proposal_api.cre_or_upd_salary_proposal
3171 (p_validate => p_validate
3172 ,p_pay_proposal_id => p_pay_proposal_id
3173 ,p_object_version_number => p_object_version_number
3174 ,p_business_group_id => p_business_group_id
3175 ,p_assignment_id => p_assignment_id
3176 ,p_change_date => p_change_date
3177 ,p_comments => p_comments
3178 ,p_next_sal_review_date => p_next_sal_review_date
3179 ,p_proposal_reason => p_proposal_reason
3180 ,p_proposed_salary_n => p_proposed_salary_n
3181 ,p_forced_ranking => p_forced_ranking
3182 ,p_date_to => null
3183 ,p_performance_review_id => p_performance_review_id
3184 ,p_attribute_category => p_attribute_category
3185 ,p_attribute1 => p_attribute1
3186 ,p_attribute2 => p_attribute2
3187 ,p_attribute3 => p_attribute3
3188 ,p_attribute4 => p_attribute4
3189 ,p_attribute5 => p_attribute5
3190 ,p_attribute6 => p_attribute6
3191 ,p_attribute7 => p_attribute7
3192 ,p_attribute8 => p_attribute8
3193 ,p_attribute9 => p_attribute9
3194 ,p_attribute10 => p_attribute10
3195 ,p_attribute11 => p_attribute11
3196 ,p_attribute12 => p_attribute12
3197 ,p_attribute13 => p_attribute13
3198 ,p_attribute14 => p_attribute14
3199 ,p_attribute15 => p_attribute15
3200 ,p_attribute16 => p_attribute16
3201 ,p_attribute17 => p_attribute17
3202 ,p_attribute18 => p_attribute18
3203 ,p_attribute19 => p_attribute19
3204 ,p_attribute20 => p_attribute20
3205 ,p_multiple_components => p_multiple_components
3206 ,p_approved => p_approved
3207 ,p_inv_next_sal_date_warning => p_inv_next_sal_date_warning
3208 ,p_proposed_salary_warning => p_proposed_salary_warning
3209 ,p_approved_warning => p_approved_warning
3210 ,p_payroll_warning => p_payroll_warning
3211 );
3212 --
3213 end cre_or_upd_salary_proposal;
3214 --
3215 --
3216 -------------------------------------------------------------------------------
3217 -- |----------------------< delete_salary_history >---------------------------|
3218 -- ----------------------------------------------------------------------------
3219 --
3220 /* Procedure to delete salary proposals and components
3221 of an assignment before a given date.
3222
3223 Parameters:
3224 p_assignment_id Assignment Id
3225 p_date Date
3226 */
3227 Procedure delete_salary_history( p_assignment_id in number
3228 ,p_date in date) is
3229 --
3230 l_salary_warning boolean;
3231 l_proc varchar2(72) := g_package||'delete_salary_history';
3232 l_change_date date;
3233 --
3234 cursor c_del_salary_proposals( p_assignment_id in number
3235 ,p_date in date) is
3236 select pay_proposal_id, business_group_id, object_version_number
3237 from per_pay_proposals
3238 where assignment_id = p_assignment_id
3239 and change_date <
3240 (select max(change_date)
3241 from per_pay_proposals
3242 where assignment_id = p_assignment_id
3243 and change_date <= p_date
3244 );
3245 --
3246 cursor components(p_pay_proposal_id number, p_business_group_id number) is
3247 select ppc.component_id
3248 , ppc.object_version_number
3249 from per_pay_proposal_components ppc
3250 where ppc.pay_proposal_id=p_pay_proposal_id
3251 and ppc.business_group_id=p_business_group_id;
3252 --
3253 begin
3254 hr_utility.set_location('Entering:'||l_proc,5);
3255 --
3256 --
3257 -- Issue a savepoint
3258 --
3259 savepoint delete_salary_history;
3260 --
3261 --
3262 FOR r_del_sp in c_del_salary_proposals( p_assignment_id ,p_date)
3263 LOOP
3264 hr_utility.set_location('Inside loop salary_proposal - '||
3265 r_del_sp.pay_proposal_id||l_proc,10);
3266 --
3267 -- first of all delete all of the components
3268 --
3269 FOR l_components IN components(r_del_sp.pay_proposal_id,
3270 r_del_sp.business_group_id)
3271 LOOP
3272 hr_utility.set_location('Inside loop component - '||
3273 l_components.component_id||l_proc,15);
3274 per_ppc_del.del(p_component_id=>l_components.component_id
3275 ,p_object_version_number=>l_components.object_version_number
3276 ,p_validation_strength=>'WEAK'
3277 ,p_validate=>FALSE);
3278 END LOOP;
3279 hr_utility.set_location(l_proc,20);
3280 --
3281 -- Update CWB table that created/updated the salary proposal
3282 --
3283 update BEN_CWB_PERSON_RATES
3284 set PAY_PROPOSAL_ID = null
3285 where PAY_PROPOSAL_ID = r_del_sp.pay_proposal_id;
3286 --
3287 -- Delete Salary Proposal
3288 --
3289 delete per_pay_proposals
3290 where pay_proposal_id = r_del_sp.pay_proposal_id;
3291 --
3292 hr_utility.set_location(l_proc,25);
3293 --
3294 END LOOP;
3295 hr_utility.set_location('Exiting:'||l_proc,50);
3296 EXCEPTION
3297 When others then
3298 --
3299 -- A validation or unexpected error has occured
3300 --
3301 hr_utility.set_location('When Others:'||l_proc,55);
3302 ROLLBACK TO delete_salary_proposal;
3303 raise;
3304 --
3305 --
3306 end;
3307 --
3308 end hr_maintain_proposal_api;
3309