[Home] [Help]
PACKAGE BODY: APPS.HR_MAINTAIN_PROPOSAL_API
Source
1 Package Body hr_maintain_proposal_api as
2 /* $Header: hrpypapi.pkb 120.30.12010000.13 2009/01/16 09:51:09 schowdhu 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 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
1443 update_last_change_date(l_assignment_id, l_change_date);
1444 --
1445 hr_general.g_data_migrator_mode := 'N';
1446 --
1447 per_pyp_upd.upd
1448 (p_pay_proposal_id => p_pay_proposal_id
1449 ,p_comments => p_comments
1450 ,p_change_date => l_change_date
1451 ,p_next_sal_review_date => l_next_sal_review_date
1452 ,p_proposal_reason => p_proposal_reason
1453 ,p_proposed_salary_n => p_proposed_salary_n
1454 ,p_forced_ranking => p_forced_ranking
1455 ,p_date_to => l_date_to
1456 ,p_performance_review_id => p_performance_review_id
1457 ,p_attribute_category => p_attribute_category
1458 ,p_attribute1 => p_attribute1
1459 ,p_attribute2 => p_attribute2
1460 ,p_attribute3 => p_attribute3
1461 ,p_attribute4 => p_attribute4
1462 ,p_attribute5 => p_attribute5
1463 ,p_attribute6 => p_attribute6
1464 ,p_attribute7 => p_attribute7
1465 ,p_attribute8 => p_attribute8
1466 ,p_attribute9 => p_attribute9
1467 ,p_attribute10 => p_attribute10
1468 ,p_attribute11 => p_attribute11
1469 ,p_attribute12 => p_attribute12
1470 ,p_attribute13 => p_attribute13
1471 ,p_attribute14 => p_attribute14
1472 ,p_attribute15 => p_attribute15
1473 ,p_attribute16 => p_attribute16
1474 ,p_attribute17 => p_attribute17
1475 ,p_attribute18 => p_attribute18
1476 ,p_attribute19 => p_attribute19
1477 ,p_attribute20 => p_attribute20
1478 ,p_object_version_number => l_object_version_number
1479 ,p_multiple_components => p_multiple_components
1480 ,p_approved => p_approved
1481 ,p_validate => false
1482 ,p_inv_next_sal_date_warning
1483 => l_inv_next_sal_date_warning
1484 ,p_proposed_salary_warning => l_proposed_salary_warning
1485 ,p_approved_warning => l_approved_warning
1486 ,p_payroll_warning => l_payroll_warning
1487 );
1488
1489 -- Now we maintain an element entry for this proposal
1490 -- by calling the maintain_elements_bsp.
1491 --
1492 hr_utility.set_location(l_proc,10);
1493
1494 if(p_approved='Y') then
1495 hr_utility.set_location(l_proc,15);
1496 open get_element;
1497 fetch get_element into l_element_entry_id,l_assignment_id;
1498 if get_element%found then
1499 close get_element;
1500 hr_utility.set_location(l_proc,20);
1501 --
1502 if p_proposed_salary_n = hr_api.g_number then
1503 l_proposed_salary_n:=per_pyp_shd.g_old_rec.proposed_salary_n;
1504 else
1505 l_proposed_salary_n:=p_proposed_salary_n;
1506 end if;
1507 maintain_elements_bsp(p_pay_proposal_id
1508 ,l_assignment_id
1509 ,l_change_date
1510 ,l_date_to
1511 ,l_element_entry_id
1512 ,l_proposed_salary_n);
1513 else
1514 hr_utility.set_location(l_proc,25);
1515 l_element_entry_id := null;
1516 select pyp.assignment_id
1517 into l_assignment_id
1518 from per_pay_proposals pyp
1519 where pyp.pay_proposal_id=p_pay_proposal_id;
1520 if p_proposed_salary_n = hr_api.g_number then
1521 l_proposed_salary_n:=per_pyp_shd.g_old_rec.proposed_salary_n;
1522 else
1523 l_proposed_salary_n:=p_proposed_salary_n;
1524 end if;
1525 maintain_elements_bsp(p_pay_proposal_id
1526 ,l_assignment_id
1527 ,l_change_date
1528 ,l_date_to
1529 ,l_element_entry_id
1530 ,l_proposed_salary_n);
1531 close get_element;
1532 end if;
1533 end if;
1534
1535 --
1536 -- Call After Process User Hook for update_salary_proposal
1537 --
1538 begin
1539 hr_maintain_proposal_bk2.update_salary_proposal_a
1540 (
1541 p_pay_proposal_id => p_pay_proposal_id,
1542 p_change_date => l_change_date,
1543 p_comments => p_comments,
1544 p_next_sal_review_date => l_next_sal_review_date,
1545 p_proposal_reason => p_proposal_reason,
1546 p_proposed_salary_n => p_proposed_salary_n,
1547 p_forced_ranking => p_forced_ranking,
1548 p_date_to => l_date_to,
1549 p_performance_review_id => p_performance_review_id,
1550 p_attribute_category => p_attribute_category,
1551 p_attribute1 => p_attribute1,
1552 p_attribute2 => p_attribute2,
1553 p_attribute3 => p_attribute3,
1554 p_attribute4 => p_attribute4,
1555 p_attribute5 => p_attribute5,
1556 p_attribute6 => p_attribute6,
1557 p_attribute7 => p_attribute7,
1558 p_attribute8 => p_attribute8,
1559 p_attribute9 => p_attribute9,
1560 p_attribute10 => p_attribute10,
1561 p_attribute11 => p_attribute11,
1562 p_attribute12 => p_attribute12,
1563 p_attribute13 => p_attribute13,
1564 p_attribute14 => p_attribute14,
1565 p_attribute15 => p_attribute15,
1566 p_attribute16 => p_attribute16,
1567 p_attribute17 => p_attribute17,
1568 p_attribute18 => p_attribute18,
1569 p_attribute19 => p_attribute19,
1570 p_attribute20 => p_attribute20,
1571 p_object_version_number => l_object_version_number,
1572 p_multiple_components => p_multiple_components,
1573 p_approved => p_approved,
1574 p_inv_next_sal_date_warning => l_inv_next_sal_date_warning,
1575 p_proposed_salary_warning => l_proposed_salary_warning,
1576 p_approved_warning => l_approved_warning,
1577 p_payroll_warning => l_payroll_warning
1578 );
1579 exception
1580 when hr_api.cannot_find_prog_unit then
1581 hr_api.cannot_find_prog_unit_error
1582 (p_module_name => 'UPDATE_SALARY_PROPOSAL'
1583 ,p_hook_type => 'AP'
1584 );
1585 --
1586 -- End of the after hook for update_salary_proposal
1587 --
1588 end;
1589 --
1590 -- If we are validating then raise the Validate_Enabled exception
1591 --
1592 If p_validate then
1593 Raise HR_Api.Validate_Enabled;
1594 End If;
1595 --
1596 -- Set OUT parameters
1597 --
1598 p_object_version_number := l_object_version_number;
1599 p_inv_next_sal_date_warning := l_inv_next_sal_date_warning;
1600 p_proposed_salary_warning := l_proposed_salary_warning;
1601 p_approved_warning := l_approved_warning;
1602 p_payroll_warning := l_payroll_warning;
1603 --
1604 hr_utility.set_location('Leaving: '||l_proc,20);
1605 --
1606 Exception
1607 When HR_Api.Validate_Enabled Then
1608 --
1609 -- As the Validate_Enabled exception has been raised
1610 -- we must rollback to the savepoint
1611 --
1612 ROLLBACK TO update_salary_proposal;
1613 --
1614 -- Only set output warning arguments
1615 -- (Any key or derived arguments must be set to null
1616 -- when validation only mode is being used.)
1617 --
1618 p_inv_next_sal_date_warning := l_inv_next_sal_date_warning;
1619 p_proposed_salary_warning := l_proposed_salary_warning;
1620 p_approved_warning := l_approved_warning;
1621 p_payroll_warning := l_payroll_warning;
1622 p_object_version_number := l_temp_ovn;
1623 --
1624 hr_utility.set_location(' Leaving:'||l_proc, 25);
1625 --
1626 When others then
1627 --
1628 -- A validation or unexpected error has occured
1629 --
1630 ROLLBACK TO update_salary_proposal;
1631 p_inv_next_sal_date_warning := null;
1632 p_proposed_salary_warning := null;
1633 p_approved_warning := null;
1634 p_payroll_warning := null;
1635 p_object_version_number := l_temp_ovn;
1636 hr_utility.set_location(' Leaving:'||l_proc, 30);
1637 raise;
1638 --
1639 end update_salary_proposal;
1640 --
1641 -- ----------------------------------------------------------------------------
1642 -- |----------------------< update_salary_proposal >--------------------------|
1643 -- ----------------------------------------------------------------------------
1644 ----------------------- Without the new date_to parameter --------------------
1645 -- ----------------------------------------------------------------------------
1646 --
1647 Procedure update_salary_proposal(
1648 p_pay_proposal_id in number,
1649 p_change_date in date,
1650 p_comments in varchar2,
1651 p_next_sal_review_date in date,
1652 p_proposal_reason in varchar2,
1653 p_proposed_salary_n in number,
1654 p_forced_ranking in number,
1655 p_performance_review_id in number,
1656 p_attribute_category in varchar2,
1657 p_attribute1 in varchar2,
1658 p_attribute2 in varchar2,
1659 p_attribute3 in varchar2,
1660 p_attribute4 in varchar2,
1661 p_attribute5 in varchar2,
1662 p_attribute6 in varchar2,
1663 p_attribute7 in varchar2,
1664 p_attribute8 in varchar2,
1665 p_attribute9 in varchar2,
1666 p_attribute10 in varchar2,
1667 p_attribute11 in varchar2,
1668 p_attribute12 in varchar2,
1669 p_attribute13 in varchar2,
1670 p_attribute14 in varchar2,
1671 p_attribute15 in varchar2,
1672 p_attribute16 in varchar2,
1673 p_attribute17 in varchar2,
1674 p_attribute18 in varchar2,
1675 p_attribute19 in varchar2,
1676 p_attribute20 in varchar2,
1677 p_object_version_number in out nocopy number,
1678 p_multiple_components in varchar2,
1679 p_approved in varchar2,
1680 p_validate in boolean,
1681 p_inv_next_sal_date_warning out nocopy boolean,
1682 p_proposed_salary_warning out nocopy boolean,
1683 p_approved_warning out nocopy boolean,
1684 p_payroll_warning out nocopy boolean) is
1685 --
1686 --
1687 Cursor csr_date_to
1688 IS
1689 select date_to
1690 from per_pay_proposals
1691 where pay_proposal_id = p_pay_proposal_id;
1692
1693 l_date_to PER_PAY_PROPOSALS.date_to%TYPE default null;
1694
1695 begin
1696
1697 OPEN csr_date_to;
1698 FETCH csr_date_to into l_date_to;
1699 CLOSE csr_date_to;
1700
1701 hr_maintain_proposal_api.update_salary_proposal
1702 (p_pay_proposal_id => p_pay_proposal_id
1703 ,p_change_date => p_change_date
1704 ,p_comments => p_comments
1705 ,p_next_sal_review_date => p_next_sal_review_date
1706 ,p_proposal_reason => p_proposal_reason
1707 ,p_proposed_salary_n => p_proposed_salary_n
1708 ,p_forced_ranking => p_forced_ranking
1709 ,p_date_to => l_date_to
1710 ,p_performance_review_id => p_performance_review_id
1711 ,p_attribute_category => p_attribute_category
1712 ,p_attribute1 => p_attribute1
1713 ,p_attribute2 => p_attribute2
1714 ,p_attribute3 => p_attribute3
1715 ,p_attribute4 => p_attribute4
1716 ,p_attribute5 => p_attribute5
1717 ,p_attribute6 => p_attribute6
1718 ,p_attribute7 => p_attribute7
1719 ,p_attribute8 => p_attribute8
1720 ,p_attribute9 => p_attribute9
1721 ,p_attribute10 => p_attribute10
1722 ,p_attribute11 => p_attribute11
1723 ,p_attribute12 => p_attribute12
1724 ,p_attribute13 => p_attribute13
1725 ,p_attribute14 => p_attribute14
1726 ,p_attribute15 => p_attribute15
1727 ,p_attribute16 => p_attribute16
1728 ,p_attribute17 => p_attribute17
1729 ,p_attribute18 => p_attribute18
1730 ,p_attribute19 => p_attribute19
1731 ,p_attribute20 => p_attribute20
1732 ,p_object_version_number => p_object_version_number
1733 ,p_multiple_components => p_multiple_components
1734 ,p_approved => p_approved
1735 ,p_validate => p_validate
1736 ,p_inv_next_sal_date_warning => p_inv_next_sal_date_warning
1737 ,p_proposed_salary_warning => p_proposed_salary_warning
1738 ,p_approved_warning => p_approved_warning
1739 ,p_payroll_warning => p_payroll_warning
1740 );
1741 --
1742 end update_salary_proposal;
1743 --
1744 --
1745 -- ----------------------------------------------------------------------------
1746 -- |----------------------< approve_salary_proposal >--------------------------|
1747 -- ----------------------------------------------------------------------------
1748 --
1749 Procedure approve_salary_proposal(
1750 p_pay_proposal_id in number,
1751 p_change_date in date,
1752 p_proposed_salary_n in number,
1753 p_object_version_number in out nocopy number,
1754 p_validate in boolean,
1755 p_inv_next_sal_date_warning out nocopy boolean,
1756 p_proposed_salary_warning out nocopy boolean,
1757 p_approved_warning out nocopy boolean,
1758 p_payroll_warning out nocopy boolean,
1759 p_error_text out nocopy varchar2) is
1760 --
1761 l_proc varchar2(72) := g_package||'approve_salary_proposal';
1762 l_change_date per_pay_proposals.change_date%TYPE;
1763 l_object_version_number per_pay_proposals.object_version_number%TYPE;
1764 l_element_entry_id pay_element_entries_f.element_entry_id%TYPE;
1765 l_inv_next_sal_date_warning boolean;
1766 l_proposed_salary_warning boolean;
1767 l_approved_warning boolean;
1768 l_payroll_warning boolean;
1769 l_error_text varchar2(72);
1770 l_assignment_id per_pay_proposals.assignment_id%type;
1771 l_proposed_salary_n per_pay_proposals.proposed_salary_n%TYPE;
1772 l_date_to per_pay_proposals.date_to%TYPE;
1773 l_temp_ovn number := p_object_version_number;
1774 --
1775 --
1776 -- cursor get_element IS
1777 -- select pee.element_entry_id
1778 -- , pyp.assignment_id
1779 -- from pay_element_entries_f pee
1780 -- , per_pay_proposals pyp
1781 -- where pyp.pay_proposal_id=p_pay_proposal_id
1782 -- and pee.assignment_id=pyp.assignment_id
1783 -- and NVL(l_change_date,to_date('31-12-4127','DD-MM-YYYY')) between
1784 -- pee.effective_start_date and pee.effective_end_date
1785 -- and pee.creator_type='SP';
1786 --
1787 -- Bug 1732348 Fix
1788
1789 cursor get_element IS
1790 select pee.element_entry_id
1791 from pay_element_entries_f pee
1792 where pee.assignment_id=l_assignment_id
1793 and NVL(l_change_date,to_date('31-12-4127','DD-MM-YYYY')) between
1794 pee.effective_start_date and pee.effective_end_date
1795 and pee.creator_type='SP';
1796
1797 cursor csr_get_date_to
1798 is
1799 select date_to
1800 from per_pay_proposals
1801 where pay_proposal_id = p_pay_proposal_id;
1802
1803 begin
1804 hr_utility.set_location('Entering:'||l_proc,5);
1805 --
1806 -- Issue a savepoint
1807 --
1808 savepoint approve_salary_proposal;
1809 --
1810 -- Process Logic
1811 --
1812 l_object_version_number := p_object_version_number;
1813 --
1814 -- get the old record details
1815 --
1816 per_pyp_shd.lck
1817 (p_pay_proposal_id => p_pay_proposal_id
1818 ,p_object_version_number => p_object_version_number);
1819 --
1820 if p_change_date = hr_api.g_date then
1821 l_change_date:=per_pyp_shd.g_old_rec.change_date;
1822 else
1823 l_change_date := trunc(p_change_date);
1824 end if;
1825 --
1826 -- Call Before Process User Hook for approve_salary_proposal
1827 --
1828 begin
1829 hr_maintain_proposal_bk3.approve_salary_proposal_b
1830 (
1831 p_pay_proposal_id => p_pay_proposal_id,
1832 p_change_date => l_change_date,
1833 p_proposed_salary_n => p_proposed_salary_n,
1834 p_object_version_number => p_object_version_number
1835 );
1836 exception
1837 when hr_api.cannot_find_prog_unit then
1838 hr_api.cannot_find_prog_unit_error
1839 (p_module_name => 'APPROVE_SALARY_PROPOSAL'
1840 ,p_hook_type => 'BP'
1841 );
1842 --
1843 -- End of the after hook for approve_salary_proposal
1844 --
1845 end;
1846 --
1847 --
1848 /* last approved proposal is end-dated if proposals are
1849 approved through SalMgmt screen. Bug#7386307 by schowdhu
1850 */
1851 l_assignment_id:=per_pyp_shd.g_old_rec.assignment_id;
1852 --
1853 if (nvl(per_pyp_shd.g_old_rec.approved,'N')<>'Y') then
1854 end_date_approved_proposal(l_assignment_id, l_change_date-1, p_pay_proposal_id);
1855 end if;
1856 update_last_change_date(l_assignment_id, l_change_date);
1857 --
1858
1859 if(nvl(per_pyp_shd.g_old_rec.approved,'N')<>'Y') THEN
1860 --
1861 -- only do this if the salary is not approved.
1862 --
1863 per_pyp_upd.upd
1864 (p_pay_proposal_id => p_pay_proposal_id
1865 ,p_change_date => l_change_date
1866 ,p_object_version_number => l_object_version_number
1867 ,p_proposed_salary_n => p_proposed_salary_n
1868 ,p_approved => 'Y'
1869 ,p_validate => false
1870 ,p_inv_next_sal_date_warning
1871 => l_inv_next_sal_date_warning
1872 ,p_proposed_salary_warning => l_proposed_salary_warning
1873 ,p_approved_warning => l_approved_warning
1874 ,p_payroll_warning => l_payroll_warning
1875 );
1876
1877 -- Now we maintain an element entry for this proposal
1878 -- by calling the maintain_elements_bsp.
1879 --
1880 hr_utility.set_location(l_proc,10);
1881 --
1882 -- Bug 1732348 Fix
1883 l_assignment_id:=per_pyp_shd.g_old_rec.assignment_id;
1884 open get_element;
1885 fetch get_element into l_element_entry_id; -- ,l_assignment_id;
1886 close get_element;
1887 --
1888 --
1889 if p_proposed_salary_n = hr_api.g_number then
1890 l_proposed_salary_n:=per_pyp_shd.g_old_rec.proposed_salary_n;
1891 else
1892 l_proposed_salary_n:=p_proposed_salary_n;
1893 end if;
1894 --
1895 maintain_elements_bsp(p_pay_proposal_id
1896 ,l_assignment_id
1897 ,l_change_date
1898 ,l_date_to
1899 ,l_element_entry_id
1900 ,l_proposed_salary_n);
1901 --
1902 --
1903 end if;
1904 --
1905 --
1906 --
1907 -- Call After Process User Hook for approve_salary_proposal
1908 --
1909 begin
1910 hr_maintain_proposal_bk3.approve_salary_proposal_a
1911 (
1912 p_pay_proposal_id => p_pay_proposal_id,
1913 p_change_date => l_change_date,
1914 p_proposed_salary_n => p_proposed_salary_n,
1915 p_object_version_number => l_object_version_number,
1916 p_inv_next_sal_date_warning => l_inv_next_sal_date_warning,
1917 p_proposed_salary_warning => l_proposed_salary_warning,
1918 p_approved_warning => l_approved_warning,
1919 p_payroll_warning => l_payroll_warning,
1920 p_error_text => l_error_text
1921 );
1922 exception
1923 when hr_api.cannot_find_prog_unit then
1924 hr_api.cannot_find_prog_unit_error
1925 (p_module_name => 'APPROVE_SALARY_PROPOSAL'
1926 ,p_hook_type => 'AP'
1927 );
1928 --
1929 -- End of the after hook for update_salary_proposal
1930 --
1931 end;
1932 --
1933 -- If we are validating then raise the Validate_Enabled exception
1934 If p_validate then
1935 Raise HR_Api.Validate_Enabled;
1936 End If;
1937 --
1938 --
1939 -- Set OUT parameters
1940 --
1941 p_object_version_number := l_object_version_number;
1942 p_inv_next_sal_date_warning := l_inv_next_sal_date_warning;
1943 p_proposed_salary_warning := l_proposed_salary_warning;
1944 p_approved_warning := l_approved_warning;
1945 p_payroll_warning := l_payroll_warning;
1946 p_error_text := l_error_text;
1947 --
1948 hr_utility.set_location('Leaving: '||l_proc,15);
1949 --
1950 Exception
1951 When HR_Api.Validate_Enabled Then
1952 --
1953 -- As the Validate_Enabled exception has been raised
1954 -- we must rollback to the savepoint
1955 --
1956 ROLLBACK TO approve_salary_proposal;
1957 --
1958 -- Only set output warning arguments
1959 -- (Any key or derived arguments must be set to null
1960 -- when validation only mode is being used.)
1961 --
1962 p_inv_next_sal_date_warning := l_inv_next_sal_date_warning;
1963 p_proposed_salary_warning := l_proposed_salary_warning;
1964 p_approved_warning := l_approved_warning;
1965 p_payroll_warning := l_payroll_warning;
1966 p_error_text := null;
1967 p_object_version_number := l_temp_ovn;
1968 --
1969 hr_utility.set_location(' Leaving:'||l_proc, 20);
1970 --
1971 When others then
1972 --
1973 -- A validation or unexpected error has occured
1974 --
1975 ROLLBACK TO approve_salary_proposal;
1976 --
1977 -- Reset IN OUT and set OUT parameters.
1978 p_inv_next_sal_date_warning := null;
1979 p_proposed_salary_warning := null;
1980 p_approved_warning := null;
1981 p_payroll_warning := null;
1982 p_object_version_number := l_temp_ovn;
1983 p_error_text := sqlerrm;
1984 --
1985 hr_utility.set_location(' Leaving:'||l_proc, 25);
1986 -- raise;
1987 --
1988 end approve_salary_proposal;
1989 --
1990 -------------------------------------------------------------------------------
1991 -- |----------------------< delete_salary_proposal >--------------------------|
1992 -- ----------------------------------------------------------------------------
1993 --
1994 /* procedure to delete a complete salary proposal, including it's components
1995 */
1996 Procedure delete_salary_proposal(p_pay_proposal_id in number
1997 ,p_business_group_id in number
1998 ,p_object_version_number in number
1999 ,p_validate in boolean
2000 ,p_salary_warning out nocopy boolean) is
2001 --
2002 l_salary_warning boolean;
2003 l_proc varchar2(72) := g_package||'delete_salary_proposal';
2004 l_element_entry_id pay_element_entries_f.element_entry_id%TYPE;
2005 l_change_date date;
2006 v_zap_entry boolean;
2007 v_delete_next_change boolean;
2008 v_zap VARCHAR2(1):='N';
2009 l_approved per_pay_proposals.approved%TYPE;
2010 l_future_element_entries number;
2011 l_assignment_id number;
2012 l_last_change_date date;
2013 --
2014 cursor components is
2015 select ppc.component_id
2016 , ppc.object_version_number
2017 from per_pay_proposal_components ppc
2018 where ppc.pay_proposal_id=p_pay_proposal_id
2019 and ppc.business_group_id=p_business_group_id;
2020
2021 cursor elements is
2022 select pee.element_entry_id
2023 , pro.change_date
2024 from per_pay_proposals_v2 pro
2025 , pay_element_entries_f pee
2026 where pro.pay_proposal_id=p_pay_proposal_id
2027 and pro.assignment_id=pee.assignment_id
2028 and pee.creator_type='SP'
2029 and pro.change_date between pee.effective_start_date
2030 and pee.effective_end_date;
2031
2032 Cursor csr_future_element_entries(p_change_date in date,p_element_entry_id in number)
2033 IS
2034 select count(*)
2035 from pay_element_entries_f
2036 where element_entry_id = p_element_entry_id
2037 and effective_start_date > p_change_date;
2038
2039 Cursor csr_proposal_details
2040 IS
2041 select approved, last_change_date, assignment_id
2042 from per_pay_proposals
2043 where pay_proposal_id = p_pay_proposal_id;
2044
2045
2046 begin
2047 hr_utility.set_location('Entering:'||l_proc,5);
2048 --
2049 --
2050 -- Issue a savepoint
2051 --
2052 savepoint delete_salary_proposal;
2053 --
2054 --
2055 -- Call Before Process User Hook for delete_salary_proposal
2056 --
2057 begin
2058 hr_maintain_proposal_bk4.delete_salary_proposal_b
2059 (
2060 p_pay_proposal_id => p_pay_proposal_id
2061 ,p_business_group_id => p_business_group_id
2062 ,p_object_version_number => p_object_version_number
2063 );
2064 exception
2065 when hr_api.cannot_find_prog_unit then
2066 hr_api.cannot_find_prog_unit_error
2067 (p_module_name => 'DELETE_SALARY_PROPOSAL'
2068 ,p_hook_type => 'BP'
2069 );
2070 --
2071 -- End of the before hook for delete_salary_proposal
2072 --
2073 end;
2074 --
2075 --
2076 -- first of all delete all of the components
2077 --
2078 FOR l_components IN components LOOP
2079 per_ppc_del.del(p_component_id=>l_components.component_id
2080 ,p_object_version_number=>l_components.object_version_number
2081 ,p_validation_strength=>'WEAK'
2082 ,p_validate=>FALSE);
2083 END LOOP;
2084 hr_utility.set_location(l_proc,10);
2085
2086 OPEN Csr_proposal_details;
2087 fetch Csr_proposal_details into l_approved,l_last_change_date, l_assignment_id;
2088 close Csr_proposal_details;
2089 --
2090 if l_approved = 'Y' then
2091
2092 open elements;
2093 fetch elements into l_element_entry_id,l_change_date;
2094 close elements;
2095 --
2096 -- delete the element entries
2097 --
2098 if (l_element_entry_id is not null) then
2099 --
2100 hr_utility.set_location(l_proc,15);
2101 begin
2102 --
2103 select 'Y'
2104 into v_zap
2105 from sys.dual
2106 where exists
2107 (select 1
2108 from sys.dual
2109 where l_change_date =
2110 (select min(effective_start_date)
2111 from pay_element_entries_f
2112 where element_entry_id = l_element_entry_id));
2113 --
2114 exception
2115 when no_data_found
2116 then
2117 v_delete_next_change := TRUE;
2118 hr_utility.set_location(l_proc,20);
2119 --
2120 end;
2121 --
2122 v_zap_entry := (v_zap = 'Y');
2123 end if;
2124
2125 end if;
2126 --
2127 -- Extend date_to of previous Salary Proposal
2128 --added by vkodedal to fix 6001309
2129 hr_general.g_data_migrator_mode := 'Y';
2130 --
2131 extend_salary_proposal_to_date(p_pay_proposal_id);
2132 --
2133
2134 -- added by schowdhu to fix 7205132
2135 update_last_change_date(l_assignment_id, l_last_change_date);
2136
2137 hr_general.g_data_migrator_mode := 'N';
2138 --
2139 --
2140
2141 -- then delete the proposal
2142 --
2143 hr_utility.set_location(l_proc,25);
2144 per_pyp_del.del(p_pay_proposal_id=>p_pay_proposal_id
2145 ,p_object_version_number=>p_object_version_number
2146 ,p_validate=>FALSE
2147 ,p_salary_warning=>l_salary_warning);
2148 --
2149
2150 if l_approved = 'Y' then
2151 if (v_zap_entry = TRUE)
2152 then
2153
2154 OPEN csr_future_element_entries(l_change_date,l_element_entry_id);
2155 FETCH csr_future_element_entries into l_future_element_entries;
2156 CLOSE csr_future_element_entries;
2157
2158 if l_future_element_entries > 0 then
2159
2160 hr_utility.set_message(800,'PER_SAL_FIRST_PROPOSAL_DELETE');
2161 hr_utility.raise_error;
2162
2163 end if;
2164
2165
2166 hr_utility.set_location(l_proc,30);
2167 -- call API to zap entry
2168 hr_entry_api.delete_element_entry
2169 ('ZAP',
2170 l_change_date,
2171 l_element_entry_id);
2172 elsif (v_delete_next_change = TRUE)
2173 then
2174 hr_utility.set_location(l_proc,35);
2175 -- Call API to do a 'DELETE_NEXT_CHANGE'
2176 hr_entry_api.delete_element_entry
2177 ('DELETE_NEXT_CHANGE',
2178 l_change_date - 1,
2179 l_element_entry_id);
2180 end if;
2181 end if;
2182 --
2183 -- Call After Process User Hook for delete_salary_proposal
2184 --
2185 begin
2186 hr_maintain_proposal_bk4.delete_salary_proposal_a
2187 (
2188 p_pay_proposal_id => p_pay_proposal_id
2189 ,p_business_group_id => p_business_group_id
2190 ,p_object_version_number => p_object_version_number
2191 ,p_salary_warning => l_salary_warning
2192 );
2193 exception
2194 when hr_api.cannot_find_prog_unit then
2195 hr_api.cannot_find_prog_unit_error
2196 (p_module_name => 'DELETE_SALARY_PROPOSAL'
2197 ,p_hook_type => 'AP'
2198 );
2199 --
2200 -- End of the after hook for delete_salary_proposal
2201 --
2202 end;
2203 --
2204 ----------vkodedal 7-mar-07
2205 -- Update the last_change_date for the next proposal
2206 --
2207 -- commented out by schowdhu to fix 7205132
2208 -- update_last_change_date(l_assignment_id, l_last_change_date);
2209 --
2210 -- If we are validating then raise the Validate_Enabled exception
2211 --
2212 If p_validate then
2213 Raise HR_Api.Validate_Enabled;
2214 End If;
2215 --
2216 -- Set OUT parameters
2217 --
2218 p_salary_warning := l_salary_warning;
2219 --
2220 hr_utility.set_location('Leaving: '||l_proc,40);
2221 --
2222 Exception
2223 When HR_Api.Validate_Enabled Then
2224 --
2225 -- As the Validate_Enabled exception has been raised
2226 -- we must rollback to the savepoint
2227 --
2228 ROLLBACK TO delete_salary_proposal;
2229 --
2230 -- Only set output warning arguments
2231 -- (Any key or derived arguments must be set to null
2232 -- when validation only mode is being used.)
2233 --
2234 p_salary_warning := l_salary_warning;
2235
2236 When others then
2237 --
2238 -- A validation or unexpected error has occured
2239 --
2240 ROLLBACK TO delete_salary_proposal;
2241 p_salary_warning := null;
2242 raise;
2243 --
2244 end delete_salary_proposal;
2245
2246 --
2247 --
2248 -- ----------------------------------------------------------------------------
2249 -- |---------------------< insert_proposal_component >------------------------|
2250 -- ----------------------------------------------------------------------------
2251 --
2252 procedure insert_proposal_component(
2253 p_component_id out nocopy number,
2254 p_pay_proposal_id in number,
2255 p_business_group_id in number,
2256 p_approved in varchar2,
2257 p_component_reason in varchar2,
2258 p_change_amount_n in number,
2259 p_change_percentage in number,
2260 p_comments in varchar2,
2261 p_attribute_category in varchar2,
2262 p_attribute1 in varchar2,
2263 p_attribute2 in varchar2,
2264 p_attribute3 in varchar2,
2265 p_attribute4 in varchar2,
2266 p_attribute5 in varchar2,
2267 p_attribute6 in varchar2,
2268 p_attribute7 in varchar2,
2269 p_attribute8 in varchar2,
2270 p_attribute9 in varchar2,
2271 p_attribute10 in varchar2,
2272 p_attribute11 in varchar2,
2273 p_attribute12 in varchar2,
2274 p_attribute13 in varchar2,
2275 p_attribute14 in varchar2,
2276 p_attribute15 in varchar2,
2277 p_attribute16 in varchar2,
2278 p_attribute17 in varchar2,
2279 p_attribute18 in varchar2,
2280 p_attribute19 in varchar2,
2281 p_attribute20 in varchar2,
2282 p_validation_strength in varchar2,
2283 p_object_version_number out nocopy number,
2284 p_validate in boolean
2285 ) is
2286 --
2287 l_proc varchar2(72) := g_package||'insert_proposal_component';
2288 l_component_id per_pay_proposal_components.component_id%TYPE;
2289 l_object_version_number per_pay_proposal_components.object_version_number%TYPE;
2290 --
2291 begin
2292 hr_utility.set_location('Entering:'||l_proc,5);
2293 --
2294 --
2295 -- Issue a savepoint
2296 --
2297 savepoint insert_proposal_components;
2298 --
2299 --
2300 -- Call Before Process User Hook for insert_proposal_components
2301 --
2302 begin
2303 hr_maintain_proposal_bk5.insert_proposal_component_b
2304 (
2305 p_pay_proposal_id => p_pay_proposal_id,
2306 p_business_group_id => p_business_group_id,
2307 p_approved => p_approved,
2308 p_component_reason => p_component_reason,
2309 p_change_amount_n => p_change_amount_n,
2310 p_change_percentage => p_change_percentage,
2311 p_comments => p_comments,
2312 p_attribute_category => p_attribute_category,
2313 p_attribute1 => p_attribute1,
2314 p_attribute2 => p_attribute2,
2315 p_attribute3 => p_attribute3,
2316 p_attribute4 => p_attribute4,
2317 p_attribute5 => p_attribute5,
2318 p_attribute6 => p_attribute6,
2319 p_attribute7 => p_attribute7,
2320 p_attribute8 => p_attribute8,
2321 p_attribute9 => p_attribute9,
2322 p_attribute10 => p_attribute10,
2323 p_attribute11 => p_attribute11,
2324 p_attribute12 => p_attribute12,
2325 p_attribute13 => p_attribute13,
2326 p_attribute14 => p_attribute14,
2327 p_attribute15 => p_attribute15,
2328 p_attribute16 => p_attribute16,
2329 p_attribute17 => p_attribute17,
2330 p_attribute18 => p_attribute18,
2331 p_attribute19 => p_attribute19,
2332 p_attribute20 => p_attribute20,
2333 p_validation_strength => p_validation_strength
2334 );
2335 exception
2336 when hr_api.cannot_find_prog_unit then
2337 hr_api.cannot_find_prog_unit_error
2338 (p_module_name => 'INSERT_PROPOSAL_COMPONENTS'
2339 ,p_hook_type => 'BP'
2340 );
2341 --
2342 -- End of the after hook for insert_proposal_components
2343 --
2344 end;
2345 --
2346 --
2347 per_ppc_ins.ins (
2348 p_component_id => l_component_id
2349 ,p_pay_proposal_id => p_pay_proposal_id
2350 ,p_business_group_id => p_business_group_id
2351 ,p_approved => p_approved
2352 ,p_component_reason => p_component_reason
2353 ,p_change_amount_n => p_change_amount_n
2354 ,p_change_percentage => p_change_percentage
2355 ,p_comments => p_comments
2356 ,p_attribute_category => p_attribute_category
2357 ,p_attribute1 => p_attribute1
2358 ,p_attribute2 => p_attribute2
2359 ,p_attribute3 => p_attribute3
2360 ,p_attribute4 => p_attribute4
2361 ,p_attribute5 => p_attribute5
2362 ,p_attribute6 => p_attribute6
2363 ,p_attribute7 => p_attribute7
2364 ,p_attribute8 => p_attribute8
2365 ,p_attribute9 => p_attribute9
2366 ,p_attribute10 => p_attribute10
2367 ,p_attribute11 => p_attribute11
2368 ,p_attribute12 => p_attribute12
2369 ,p_attribute13 => p_attribute13
2370 ,p_attribute14 => p_attribute14
2371 ,p_attribute15 => p_attribute15
2372 ,p_attribute16 => p_attribute16
2373 ,p_attribute17 => p_attribute17
2374 ,p_attribute18 => p_attribute18
2375 ,p_attribute19 => p_attribute19
2376 ,p_attribute20 => p_attribute20
2377 ,p_object_version_number => l_object_version_number
2378 ,p_validation_strength => p_validation_strength
2379 ,p_validate => FALSE);
2380 --
2381 --
2382 -- Call After Process User Hook for insert_proposal_components
2383 --
2384 begin
2385 hr_maintain_proposal_bk5.insert_proposal_component_a
2386 (
2387 p_component_id => l_component_id,
2388 p_pay_proposal_id => p_pay_proposal_id,
2389 p_business_group_id => p_business_group_id,
2390 p_approved => p_approved,
2391 p_component_reason => p_component_reason,
2392 p_change_amount_n => p_change_amount_n,
2393 p_change_percentage => p_change_percentage,
2394 p_comments => p_comments,
2395 p_attribute_category => p_attribute_category,
2396 p_attribute1 => p_attribute1,
2397 p_attribute2 => p_attribute2,
2398 p_attribute3 => p_attribute3,
2399 p_attribute4 => p_attribute4,
2400 p_attribute5 => p_attribute5,
2401 p_attribute6 => p_attribute6,
2402 p_attribute7 => p_attribute7,
2403 p_attribute8 => p_attribute8,
2404 p_attribute9 => p_attribute9,
2405 p_attribute10 => p_attribute10,
2406 p_attribute11 => p_attribute11,
2407 p_attribute12 => p_attribute12,
2408 p_attribute13 => p_attribute13,
2409 p_attribute14 => p_attribute14,
2410 p_attribute15 => p_attribute15,
2411 p_attribute16 => p_attribute16,
2412 p_attribute17 => p_attribute17,
2413 p_attribute18 => p_attribute18,
2414 p_attribute19 => p_attribute19,
2415 p_attribute20 => p_attribute20,
2416 p_validation_strength => p_validation_strength,
2417 p_object_version_number => l_object_version_number
2418 );
2419 exception
2420 when hr_api.cannot_find_prog_unit then
2421 hr_api.cannot_find_prog_unit_error
2422 (p_module_name => 'INSERT_PROPOSAL_COMPONENTS'
2423 ,p_hook_type => 'AP'
2424 );
2425 --
2426 -- End of the after hook for insert_proposal_components
2427 --
2428 end;
2429 --
2430 -- If we are validating then raise the Validate_Enabled exception
2431 --
2432 If p_validate then
2433 Raise HR_Api.Validate_Enabled;
2434 End If;
2435 --
2436 -- Set OUT parameters
2437 --
2438 p_component_id := l_component_id;
2439 p_object_version_number := l_object_version_number;
2440 --
2441 hr_utility.set_location('Leaving: '||l_proc,10);
2442 --
2443 Exception
2444 When HR_Api.Validate_Enabled Then
2445 --
2446 -- As the Validate_Enabled exception has been raised
2447 -- we must rollback to the savepoint
2448 --
2449 ROLLBACK TO insert_proposal_components;
2450 --
2451 -- Only set output warning arguments
2452 -- (Any key or derived arguments must be set to null
2453 -- when validation only mode is being used.)
2454 --
2455 p_component_id := null;
2456 p_object_version_number := null;
2457 --
2458 When others then
2459 --
2460 -- A validation or unexpected error has occured
2461 --
2462 ROLLBACK TO insert_proposal_components;
2463 -- Set OUT parameters.
2464 p_component_id := null;
2465 p_object_version_number := null;
2466 raise;
2467 --
2468 end insert_proposal_component;
2469 --
2470 -- ----------------------------------------------------------------------------
2471 -- |---------------------< update_proposal_component >------------------------|
2472 -- ----------------------------------------------------------------------------
2473 --
2474 procedure update_proposal_component(
2475 p_component_id in number,
2476 p_approved in varchar2,
2477 p_component_reason in varchar2,
2478 p_change_amount_n in number,
2479 p_change_percentage in number,
2480 p_comments in varchar2,
2481 p_attribute_category in varchar2,
2482 p_attribute1 in varchar2,
2483 p_attribute2 in varchar2,
2484 p_attribute3 in varchar2,
2485 p_attribute4 in varchar2,
2486 p_attribute5 in varchar2,
2487 p_attribute6 in varchar2,
2488 p_attribute7 in varchar2,
2489 p_attribute8 in varchar2,
2490 p_attribute9 in varchar2,
2491 p_attribute10 in varchar2,
2492 p_attribute11 in varchar2,
2493 p_attribute12 in varchar2,
2494 p_attribute13 in varchar2,
2495 p_attribute14 in varchar2,
2496 p_attribute15 in varchar2,
2497 p_attribute16 in varchar2,
2498 p_attribute17 in varchar2,
2499 p_attribute18 in varchar2,
2500 p_attribute19 in varchar2,
2501 p_attribute20 in varchar2,
2502 p_validation_strength in varchar2,
2503 p_object_version_number in out nocopy number,
2504 p_validate in boolean
2505 ) is
2506 --
2507 l_proc varchar2(72) := g_package||'update_proposal_component';
2508 l_object_version_number per_pay_proposal_components.object_version_number%TYPE;
2509 l_temp_ovn number := p_object_version_number;
2510 --
2511 begin
2512 hr_utility.set_location('Entering:'||l_proc,10);
2513 --
2514 --
2515 -- Issue a savepoint
2516 --
2517 savepoint update_proposal_components;
2518 --
2519 -- Process Logic
2520 --
2521 l_object_version_number := p_object_version_number;
2522 --
2523 -- Call Before Process User Hook for update_proposal_components
2524 --
2525 begin
2526 hr_maintain_proposal_bk6.update_proposal_component_b
2527 (
2528 p_component_id => p_component_id,
2529 p_approved => p_approved,
2530 p_component_reason => p_component_reason,
2531 p_change_amount_n => p_change_amount_n,
2532 p_change_percentage => p_change_percentage,
2533 p_comments => p_comments,
2534 p_attribute_category => p_attribute_category,
2535 p_attribute1 => p_attribute1,
2536 p_attribute2 => p_attribute2,
2537 p_attribute3 => p_attribute3,
2538 p_attribute4 => p_attribute4,
2539 p_attribute5 => p_attribute5,
2540 p_attribute6 => p_attribute6,
2541 p_attribute7 => p_attribute7,
2542 p_attribute8 => p_attribute8,
2543 p_attribute9 => p_attribute9,
2544 p_attribute10 => p_attribute10,
2545 p_attribute11 => p_attribute11,
2546 p_attribute12 => p_attribute12,
2547 p_attribute13 => p_attribute13,
2548 p_attribute14 => p_attribute14,
2549 p_attribute15 => p_attribute15,
2550 p_attribute16 => p_attribute16,
2551 p_attribute17 => p_attribute17,
2552 p_attribute18 => p_attribute18,
2553 p_attribute19 => p_attribute19,
2554 p_attribute20 => p_attribute20,
2555 p_validation_strength => p_validation_strength,
2556 p_object_version_number => p_object_version_number
2557 );
2558 exception
2559 when hr_api.cannot_find_prog_unit then
2560 hr_api.cannot_find_prog_unit_error
2561 (p_module_name => 'UPDATE_PROPOSAL_COMPONENTS'
2562 ,p_hook_type => 'BP'
2563 );
2564 --
2565 -- End of the after hook for update_proposal_components
2566 --
2567 end;
2568 --
2569 --
2570 per_ppc_upd.upd (
2571 p_component_id => p_component_id
2572 ,p_approved => p_approved
2573 ,p_component_reason => p_component_reason
2574 ,p_change_amount_n => p_change_amount_n
2575 ,p_change_percentage => p_change_percentage
2576 ,p_comments => p_comments
2577 ,p_attribute_category => p_attribute_category
2578 ,p_attribute1 => p_attribute1
2579 ,p_attribute2 => p_attribute2
2580 ,p_attribute3 => p_attribute3
2581 ,p_attribute4 => p_attribute4
2582 ,p_attribute5 => p_attribute5
2583 ,p_attribute6 => p_attribute6
2584 ,p_attribute7 => p_attribute7
2585 ,p_attribute8 => p_attribute8
2586 ,p_attribute9 => p_attribute9
2587 ,p_attribute10 => p_attribute10
2588 ,p_attribute11 => p_attribute11
2589 ,p_attribute12 => p_attribute12
2590 ,p_attribute13 => p_attribute13
2591 ,p_attribute14 => p_attribute14
2592 ,p_attribute15 => p_attribute15
2593 ,p_attribute16 => p_attribute16
2594 ,p_attribute17 => p_attribute17
2595 ,p_attribute18 => p_attribute18
2596 ,p_attribute19 => p_attribute19
2597 ,p_attribute20 => p_attribute20
2598 ,p_object_version_number => l_object_version_number
2599 ,p_validation_strength => p_validation_strength
2600 ,p_validate => FALSE);
2601 --
2602 --
2603 -- Call After Process User Hook for update_proposal_components
2604 --
2605 begin
2606 hr_maintain_proposal_bk6.update_proposal_component_a
2607 (
2608 p_component_id => p_component_id,
2609 p_approved => p_approved,
2610 p_component_reason => p_component_reason,
2611 p_change_amount_n => p_change_amount_n,
2612 p_change_percentage => p_change_percentage,
2613 p_comments => p_comments,
2614 p_attribute_category => p_attribute_category,
2615 p_attribute1 => p_attribute1,
2616 p_attribute2 => p_attribute2,
2617 p_attribute3 => p_attribute3,
2618 p_attribute4 => p_attribute4,
2619 p_attribute5 => p_attribute5,
2620 p_attribute6 => p_attribute6,
2621 p_attribute7 => p_attribute7,
2622 p_attribute8 => p_attribute8,
2623 p_attribute9 => p_attribute9,
2624 p_attribute10 => p_attribute10,
2625 p_attribute11 => p_attribute11,
2626 p_attribute12 => p_attribute12,
2627 p_attribute13 => p_attribute13,
2628 p_attribute14 => p_attribute14,
2629 p_attribute15 => p_attribute15,
2630 p_attribute16 => p_attribute16,
2631 p_attribute17 => p_attribute17,
2632 p_attribute18 => p_attribute18,
2633 p_attribute19 => p_attribute19,
2634 p_attribute20 => p_attribute20,
2635 p_validation_strength => p_validation_strength,
2636 p_object_version_number => l_object_version_number
2637 );
2638 exception
2639 when hr_api.cannot_find_prog_unit then
2640 hr_api.cannot_find_prog_unit_error
2641 (p_module_name => 'UPDATE_PROPOSAL_COMPONENTS'
2642 ,p_hook_type => 'AP'
2643 );
2644 --
2645 -- End of the after hook for update_proposal_components
2646 --
2647 end;
2648 --
2649 -- If we are validating then raise the Validate_Enabled exception
2650 --
2651 If p_validate then
2652 Raise HR_Api.Validate_Enabled;
2653 End If;
2654 --
2655 -- Set OUT parameters
2656 --
2657 p_object_version_number := l_object_version_number;
2658 --
2659 hr_utility.set_location('Leaving: '||l_proc,20);
2660 --
2661 Exception
2662 When HR_Api.Validate_Enabled Then
2663 --
2664 -- As the Validate_Enabled exception has been raised
2665 -- we must rollback to the savepoint
2666 --
2667 ROLLBACK TO update_proposal_components;
2668 p_object_version_number := l_temp_ovn;
2669
2670 When others then
2671 --
2672 -- A validation or unexpected error has occured
2673 --
2674 ROLLBACK TO update_proposal_components;
2675 p_object_version_number := l_temp_ovn;
2676 hr_utility.set_location(' Leaving:'||l_proc, 25);
2677 raise;
2678 --
2679 end update_proposal_component;
2680 --
2681 -- ----------------------------------------------------------------------------
2682 -- |---------------------< delete_proposal_component >------------------------|
2683 -- ----------------------------------------------------------------------------
2684 --
2685 Procedure delete_proposal_component(
2686 p_component_id in number,
2687 p_validation_strength in varchar2,
2688 p_object_version_number in number,
2689 p_validate in boolean) is
2690 --
2691 l_proc varchar2(72) := g_package||'delete_proposal_component';
2692 --
2693 begin
2694 hr_utility.set_location('Entering:'||l_proc,5);
2695 --
2696 --
2697 -- Issue a savepoint
2698 --
2699 savepoint delete_proposal_components;
2700 --
2701 --
2702 -- Call Before Process User Hook for delete_proposal_components
2703 --
2704 begin
2705 hr_maintain_proposal_bk7.delete_proposal_component_b
2706 (
2707 p_component_id => p_component_id,
2708 p_validation_strength => p_validation_strength,
2709 p_object_version_number => p_object_version_number
2710 );
2711 exception
2712 when hr_api.cannot_find_prog_unit then
2713 hr_api.cannot_find_prog_unit_error
2714 (p_module_name => 'DELETE_PROPOSAL_COMPONENTS'
2715 ,p_hook_type => 'BP'
2716 );
2717 --
2718 -- End of the after hook for delete_proposal_components
2719 --
2720 end;
2721 --
2722 --
2723 per_ppc_del.del
2724 (p_component_id => p_component_id
2725 ,p_object_version_number => p_object_version_number
2726 ,p_validation_strength => p_validation_strength
2727 ,p_validate => FALSE);
2728 --
2729 --
2730 -- Call After Process User Hook for approve_salary_proposal
2731 --
2732 begin
2733 hr_maintain_proposal_bk7.delete_proposal_component_a
2734 (
2735 p_component_id => p_component_id,
2736 p_validation_strength => p_validation_strength,
2737 p_object_version_number => p_object_version_number
2738 );
2739 exception
2740 when hr_api.cannot_find_prog_unit then
2741 hr_api.cannot_find_prog_unit_error
2742 (p_module_name => 'DELETE_PROPOSAL_COMPONENTS'
2743 ,p_hook_type => 'AP'
2744 );
2745 --
2746 -- End of the after hook for delete_proposal_components
2747 --
2748 end;
2749 --
2750 -- If we are validating then raise the Validate_Enabled exception
2751 --
2752 If p_validate then
2753 Raise HR_Api.Validate_Enabled;
2754 End If;
2755 --
2756 hr_utility.set_location('Leaving: '||l_proc,10);
2757 --
2758 Exception
2759 When HR_Api.Validate_Enabled Then
2760 --
2761 -- As the Validate_Enabled exception has been raised
2762 -- we must rollback to the savepoint
2763 --
2764 ROLLBACK TO delete_proposal_components;
2765 When others then
2766 --
2767 -- A validation or unexpected error has occured
2768 --
2769 ROLLBACK TO delete_proposal_components;
2770 raise;
2771 --
2772 end delete_proposal_component;
2773 --
2774 -- ----------------------------------------------------------------------------
2775 -- |---------------------< cre_or_upd_salary_proposal >-----------------------|
2776 -- ----------------------------------------------------------------------------
2777 --
2778 procedure cre_or_upd_salary_proposal(
2779 p_validate in boolean,
2780 p_pay_proposal_id in out nocopy number,
2781 p_object_version_number in out nocopy number,
2782 p_business_group_id in number,
2783 p_assignment_id in number,
2784 p_change_date in date,
2785 p_comments in varchar2,
2786 p_next_sal_review_date in date,
2787 p_proposal_reason in varchar2,
2788 p_proposed_salary_n in number,
2789 p_forced_ranking in number,
2790 p_date_to in date,
2791 p_performance_review_id in number,
2792 p_attribute_category in varchar2,
2793 p_attribute1 in varchar2,
2794 p_attribute2 in varchar2,
2795 p_attribute3 in varchar2,
2796 p_attribute4 in varchar2,
2797 p_attribute5 in varchar2,
2798 p_attribute6 in varchar2,
2799 p_attribute7 in varchar2,
2800 p_attribute8 in varchar2,
2801 p_attribute9 in varchar2,
2802 p_attribute10 in varchar2,
2803 p_attribute11 in varchar2,
2804 p_attribute12 in varchar2,
2805 p_attribute13 in varchar2,
2806 p_attribute14 in varchar2,
2807 p_attribute15 in varchar2,
2808 p_attribute16 in varchar2,
2809 p_attribute17 in varchar2,
2810 p_attribute18 in varchar2,
2811 p_attribute19 in varchar2,
2812 p_attribute20 in varchar2,
2813 p_multiple_components in varchar2,
2814 p_approved in varchar2,
2815 p_inv_next_sal_date_warning out nocopy boolean,
2816 p_proposed_salary_warning out nocopy boolean,
2817 p_approved_warning out nocopy boolean,
2818 p_payroll_warning out nocopy boolean
2819 ) as
2820 l_proc varchar2(72) := g_package||'cre_or_upd_salary_proposal';
2821 l_api_updating boolean;
2822 l_pyp_rec per_pyp_shd.g_rec_type;
2823 l_null_pyp_rec per_pyp_shd.g_rec_type;
2824 l_inv_next_sal_date_warning boolean;
2825 l_proposed_salary_warning boolean;
2826 l_approved_warning boolean;
2827 l_payroll_warning boolean;
2828 l_element_entry_id number;
2829 l_dummy number;
2830 l_pay_proposal_id number;
2831 l_object_version_number number;
2832 l_date_to date := p_date_to;
2833 l_autoApprove varchar2(1); --added by vkodedal 10-Apr-2008 ER auto Approve first proposal
2834 l_temp_ovn number := p_object_version_number;
2835 l_temp_pay_proposal_id number := p_pay_proposal_id;
2836 --
2837 cursor first_proposal is
2838 select 1 from per_pay_proposals
2839 where assignment_id=p_assignment_id;
2840 --
2841 cursor asg_type is
2842 select asg.assignment_type
2843 from per_all_assignments_f asg
2844 where asg.assignment_id=p_assignment_id
2845 and p_change_date between asg.effective_start_date
2846 and asg.effective_end_date;
2847 --
2848 l_asg_type per_all_assignments_f.assignment_type%type;
2849 --
2850 begin
2851 --
2852 hr_utility.set_location('Entering:'|| l_proc, 10);
2853 --
2854 savepoint cre_or_upd_salary_proposal;
2855 --
2856
2857 -- Remember IN OUT parameters.
2858
2859 l_pay_proposal_id := p_pay_proposal_id;
2860 l_object_version_number := p_object_version_number;
2861
2862 l_api_updating := per_pyp_shd.api_updating
2863 (p_pay_proposal_id => p_pay_proposal_id
2864 ,p_object_version_number => p_object_version_number);
2865 hr_utility.set_location(l_proc, 20);
2866
2867 if (l_api_updating and l_date_to = hr_api.g_date) then
2868 l_date_to := hr_general.end_of_time;
2869 end if;
2870
2871 --
2872 -- set the record
2873 --
2874 l_pyp_rec :=
2875 per_pyp_shd.convert_args
2876 (p_pay_proposal_id
2877 ,p_assignment_id
2878 ,p_business_group_id
2879 ,p_change_date
2880 ,p_comments
2881 ,null
2882 ,p_next_sal_review_date
2883 ,p_proposal_reason
2884 ,p_proposed_salary_n
2885 ,p_forced_ranking
2886 ,l_date_to
2887 ,p_performance_review_id
2888 ,p_attribute_category
2889 ,p_attribute1
2890 ,p_attribute2
2891 ,p_attribute3
2892 ,p_attribute4
2893 ,p_attribute5
2894 ,p_attribute6
2895 ,p_attribute7
2896 ,p_attribute8
2897 ,p_attribute9
2898 ,p_attribute10
2899 ,p_attribute11
2900 ,p_attribute12
2901 ,p_attribute13
2902 ,p_attribute14
2903 ,p_attribute15
2904 ,p_attribute16
2905 ,p_attribute17
2906 ,p_attribute18
2907 ,p_attribute19
2908 ,p_attribute20
2909 ,p_object_version_number
2910 ,p_multiple_components
2911 ,p_approved
2912 );
2913 if not l_api_updating then
2914 --
2915 -- set g_old_rec to null
2916 --
2917 per_pyp_shd.g_old_rec:=l_null_pyp_rec;
2918 hr_utility.set_location(l_proc, 30);
2919 --
2920 -- convert the null values
2921 --
2922 hr_utility.set_location(l_proc, 40);
2923 per_pyp_upd.convert_defs(l_pyp_rec);
2924 --
2925 hr_utility.set_location(l_proc, 45);
2926 --
2927 open first_proposal;
2928 fetch first_proposal into l_dummy;
2929 if first_proposal%notfound then
2930 close first_proposal;
2931 l_pyp_rec.multiple_components:='N';
2932 open asg_type;
2933 fetch asg_type into l_asg_type;
2934 close asg_type;
2935 if l_asg_type='E' then
2936 --vkodedal 10-Apr-2008 ER to satisfy satutory requirement
2937 --Retain auto approve first proposal functionality if profile is null or set to Yes
2938
2939 l_autoApprove:=fnd_profile.value('HR_AUTO_APPROVE_FIRST_PROPOSAL');
2940 if(l_autoApprove is null or l_autoApprove ='Y') then
2941 hr_utility.set_location(l_proc, 47);
2942 l_pyp_rec.approved:='Y';
2943 end if;
2944
2945 else
2946 l_pyp_rec.approved:='N';
2947 end if;
2948 else
2949 close first_proposal;
2950 end if;
2951 -- insert the data
2952 --
2953 hr_utility.set_location(l_proc, 50);
2954 hr_maintain_proposal_api.insert_salary_proposal
2955 (p_pay_proposal_id => l_pyp_rec.pay_proposal_id
2956 ,p_assignment_id => l_pyp_rec.assignment_id
2957 ,p_business_group_id => l_pyp_rec.business_group_id
2958 ,p_change_date => l_pyp_rec.change_date
2959 ,p_comments => l_pyp_rec.comments
2960 ,p_next_sal_review_date => l_pyp_rec.next_sal_review_date
2961 ,p_proposal_reason => l_pyp_rec.proposal_reason
2962 ,p_proposed_salary_n => l_pyp_rec.proposed_salary_n
2963 ,p_forced_ranking => l_pyp_rec.forced_ranking
2964 ,p_date_to => l_pyp_rec.date_to
2965 ,p_performance_review_id => l_pyp_rec.performance_review_id
2966 ,p_attribute_category => l_pyp_rec.attribute_category
2967 ,p_attribute1 => l_pyp_rec.attribute1
2968 ,p_attribute2 => l_pyp_rec.attribute2
2969 ,p_attribute3 => l_pyp_rec.attribute3
2970 ,p_attribute4 => l_pyp_rec.attribute4
2971 ,p_attribute5 => l_pyp_rec.attribute5
2972 ,p_attribute6 => l_pyp_rec.attribute6
2973 ,p_attribute7 => l_pyp_rec.attribute7
2974 ,p_attribute8 => l_pyp_rec.attribute8
2975 ,p_attribute9 => l_pyp_rec.attribute9
2976 ,p_attribute10 => l_pyp_rec.attribute10
2977 ,p_attribute11 => l_pyp_rec.attribute11
2978 ,p_attribute12 => l_pyp_rec.attribute12
2979 ,p_attribute13 => l_pyp_rec.attribute13
2980 ,p_attribute14 => l_pyp_rec.attribute14
2981 ,p_attribute15 => l_pyp_rec.attribute15
2982 ,p_attribute16 => l_pyp_rec.attribute16
2983 ,p_attribute17 => l_pyp_rec.attribute17
2984 ,p_attribute18 => l_pyp_rec.attribute18
2985 ,p_attribute19 => l_pyp_rec.attribute19
2986 ,p_attribute20 => l_pyp_rec.attribute20
2987 ,p_object_version_number => l_pyp_rec.object_version_number
2988 ,p_multiple_components => nvl(l_pyp_rec.multiple_components,'N')
2989 ,p_approved => nvl(l_pyp_rec.approved,'N')
2990 ,p_validate => FALSE
2991 ,p_element_entry_id => l_element_entry_id
2992 ,p_inv_next_sal_date_warning => l_inv_next_sal_date_warning
2993 ,p_proposed_salary_warning => l_proposed_salary_warning
2994 ,p_approved_warning => l_approved_warning
2995 ,p_payroll_warning => l_payroll_warning
2996 );
2997 hr_utility.set_location(l_proc, 60);
2998 else
2999 --
3000 -- updating not inserting
3001 --
3002 hr_utility.set_location(l_proc, 70);
3003 per_pyp_shd.lck
3004 (p_pay_proposal_id => p_pay_proposal_id
3005 ,p_object_version_number => p_object_version_number);
3006 --
3007 -- convert the null values
3008 --
3009 hr_utility.set_location(l_proc, 80);
3010 per_pyp_upd.convert_defs(l_pyp_rec);
3011 --
3012 -- update the data
3013 --
3014 hr_utility.set_location(l_proc, 90);
3015 --
3016 hr_maintain_proposal_api.update_salary_proposal
3017 (p_pay_proposal_id => l_pyp_rec.pay_proposal_id
3018 ,p_change_date => l_pyp_rec.change_date
3019 ,p_comments => l_pyp_rec.comments
3020 ,p_next_sal_review_date => l_pyp_rec.next_sal_review_date
3021 ,p_proposal_reason => l_pyp_rec.proposal_reason
3022 ,p_proposed_salary_n => l_pyp_rec.proposed_salary_n
3023 ,p_forced_ranking => l_pyp_rec.forced_ranking
3024 ,p_date_to => l_pyp_rec.date_to
3025 ,p_performance_review_id => l_pyp_rec.performance_review_id
3026 ,p_attribute_category => l_pyp_rec.attribute_category
3027 ,p_attribute1 => l_pyp_rec.attribute1
3028 ,p_attribute2 => l_pyp_rec.attribute2
3029 ,p_attribute3 => l_pyp_rec.attribute3
3030 ,p_attribute4 => l_pyp_rec.attribute4
3031 ,p_attribute5 => l_pyp_rec.attribute5
3032 ,p_attribute6 => l_pyp_rec.attribute6
3033 ,p_attribute7 => l_pyp_rec.attribute7
3034 ,p_attribute8 => l_pyp_rec.attribute8
3035 ,p_attribute9 => l_pyp_rec.attribute9
3036 ,p_attribute10 => l_pyp_rec.attribute10
3037 ,p_attribute11 => l_pyp_rec.attribute11
3038 ,p_attribute12 => l_pyp_rec.attribute12
3039 ,p_attribute13 => l_pyp_rec.attribute13
3040 ,p_attribute14 => l_pyp_rec.attribute14
3041 ,p_attribute15 => l_pyp_rec.attribute15
3042 ,p_attribute16 => l_pyp_rec.attribute16
3043 ,p_attribute17 => l_pyp_rec.attribute17
3044 ,p_attribute18 => l_pyp_rec.attribute18
3045 ,p_attribute19 => l_pyp_rec.attribute19
3046 ,p_attribute20 => l_pyp_rec.attribute20
3047 ,p_object_version_number => l_pyp_rec.object_version_number
3048 ,p_multiple_components => l_pyp_rec.multiple_components
3049 ,p_approved => l_pyp_rec.approved
3050 ,p_validate => FALSE
3051 ,p_inv_next_sal_date_warning => l_inv_next_sal_date_warning
3052 ,p_proposed_salary_warning => l_proposed_salary_warning
3053 ,p_approved_warning => l_approved_warning
3054 ,p_payroll_warning => l_payroll_warning
3055 );
3056 --
3057 hr_utility.set_location(l_proc, 100);
3058 --
3059 end if;
3060 --
3061 if p_validate then
3062 raise hr_api.validate_enabled;
3063 end if;
3064 --
3065 hr_utility.set_location(l_proc, 110);
3066 --
3067 p_pay_proposal_id := l_pyp_rec.pay_proposal_id;
3068 p_object_version_number := l_pyp_rec.object_version_number;
3069 p_inv_next_sal_date_warning := l_inv_next_sal_date_warning;
3070 p_proposed_salary_warning := l_proposed_salary_warning;
3071 p_approved_warning := l_approved_warning;
3072 p_payroll_warning := l_payroll_warning;
3073 --
3074 hr_utility.set_location('Leaving:'||l_proc, 120);
3075 --
3076 exception
3077 when hr_api.validate_enabled then
3078 rollback to cre_or_upd_salary_proposal;
3079 p_pay_proposal_id := null;
3080 p_object_version_number := null;
3081 p_inv_next_sal_date_warning := l_inv_next_sal_date_warning;
3082 p_proposed_salary_warning := l_proposed_salary_warning;
3083 p_approved_warning := l_approved_warning;
3084 p_payroll_warning := l_payroll_warning;
3085 hr_utility.set_location('Leaving:'||l_proc, 130);
3086 when others then
3087 rollback to cre_or_upd_salary_proposal;
3088 -- Reset IN OUT and OUT parameters.
3089 p_pay_proposal_id := l_temp_pay_proposal_id;
3090 p_object_version_number := l_temp_ovn;
3091 p_inv_next_sal_date_warning := null;
3092 p_proposed_salary_warning := null;
3093 p_approved_warning := null;
3094 p_payroll_warning := null;
3095 hr_utility.set_location('Leaving:'||l_proc, 140);
3096 raise;
3097 --
3098 end cre_or_upd_salary_proposal;
3099 --
3100 -- ----------------------------------------------------------------------------
3101 -- |---------------------< cre_or_upd_salary_proposal >-----------------------|
3102 -- ---------------------------------------------------------------------------
3103 -- ------------------ Without the new date_to parameter ------------------
3104 -- ----------------------------------------------------------------------------
3105 --
3106 procedure cre_or_upd_salary_proposal(
3107 p_validate in boolean,
3108 p_pay_proposal_id in out nocopy number,
3109 p_object_version_number in out nocopy number,
3110 p_business_group_id in number,
3111 p_assignment_id in number,
3112 p_change_date in date,
3113 p_comments in varchar2,
3114 p_next_sal_review_date in date,
3115 p_proposal_reason in varchar2,
3116 p_proposed_salary_n in number,
3117 p_forced_ranking in number,
3118 p_performance_review_id in number,
3119 p_attribute_category in varchar2,
3120 p_attribute1 in varchar2,
3121 p_attribute2 in varchar2,
3122 p_attribute3 in varchar2,
3123 p_attribute4 in varchar2,
3124 p_attribute5 in varchar2,
3125 p_attribute6 in varchar2,
3126 p_attribute7 in varchar2,
3127 p_attribute8 in varchar2,
3128 p_attribute9 in varchar2,
3129 p_attribute10 in varchar2,
3130 p_attribute11 in varchar2,
3131 p_attribute12 in varchar2,
3132 p_attribute13 in varchar2,
3133 p_attribute14 in varchar2,
3134 p_attribute15 in varchar2,
3135 p_attribute16 in varchar2,
3136 p_attribute17 in varchar2,
3137 p_attribute18 in varchar2,
3138 p_attribute19 in varchar2,
3139 p_attribute20 in varchar2,
3140 p_multiple_components in varchar2,
3141 p_approved in varchar2,
3142 p_inv_next_sal_date_warning out nocopy boolean,
3143 p_proposed_salary_warning out nocopy boolean,
3144 p_approved_warning out nocopy boolean,
3145 p_payroll_warning out nocopy boolean
3146 ) as
3147
3148
3149
3150 Cursor csr_date_to
3151 IS
3152 select date_to
3153 from per_pay_proposals
3154 where pay_proposal_id = p_pay_proposal_id;
3155
3156 l_date_to PER_PAY_PROPOSALS.date_to%TYPE default null;
3157
3158 begin
3159
3160 if p_pay_proposal_id is not null then
3161
3162 OPEN csr_date_to;
3163 FETCH csr_date_to into l_date_to;
3164 CLOSE csr_date_to;
3165
3166 end if;
3167
3168 hr_maintain_proposal_api.cre_or_upd_salary_proposal
3169 (p_validate => p_validate
3170 ,p_pay_proposal_id => p_pay_proposal_id
3171 ,p_object_version_number => p_object_version_number
3172 ,p_business_group_id => p_business_group_id
3173 ,p_assignment_id => p_assignment_id
3174 ,p_change_date => p_change_date
3175 ,p_comments => p_comments
3176 ,p_next_sal_review_date => p_next_sal_review_date
3177 ,p_proposal_reason => p_proposal_reason
3178 ,p_proposed_salary_n => p_proposed_salary_n
3179 ,p_forced_ranking => p_forced_ranking
3180 ,p_date_to => null
3181 ,p_performance_review_id => p_performance_review_id
3182 ,p_attribute_category => p_attribute_category
3183 ,p_attribute1 => p_attribute1
3184 ,p_attribute2 => p_attribute2
3185 ,p_attribute3 => p_attribute3
3186 ,p_attribute4 => p_attribute4
3187 ,p_attribute5 => p_attribute5
3188 ,p_attribute6 => p_attribute6
3189 ,p_attribute7 => p_attribute7
3190 ,p_attribute8 => p_attribute8
3191 ,p_attribute9 => p_attribute9
3192 ,p_attribute10 => p_attribute10
3193 ,p_attribute11 => p_attribute11
3194 ,p_attribute12 => p_attribute12
3195 ,p_attribute13 => p_attribute13
3196 ,p_attribute14 => p_attribute14
3197 ,p_attribute15 => p_attribute15
3198 ,p_attribute16 => p_attribute16
3199 ,p_attribute17 => p_attribute17
3200 ,p_attribute18 => p_attribute18
3201 ,p_attribute19 => p_attribute19
3202 ,p_attribute20 => p_attribute20
3203 ,p_multiple_components => p_multiple_components
3204 ,p_approved => p_approved
3205 ,p_inv_next_sal_date_warning => p_inv_next_sal_date_warning
3206 ,p_proposed_salary_warning => p_proposed_salary_warning
3207 ,p_approved_warning => p_approved_warning
3208 ,p_payroll_warning => p_payroll_warning
3209 );
3210 --
3211 end cre_or_upd_salary_proposal;
3212 --
3213 --
3214 -------------------------------------------------------------------------------
3215 -- |----------------------< delete_salary_history >---------------------------|
3216 -- ----------------------------------------------------------------------------
3217 --
3218 /* Procedure to delete salary proposals and components
3219 of an assignment before a given date.
3220
3221 Parameters:
3222 p_assignment_id Assignment Id
3223 p_date Date
3224 */
3225 Procedure delete_salary_history( p_assignment_id in number
3226 ,p_date in date) is
3227 --
3228 l_salary_warning boolean;
3229 l_proc varchar2(72) := g_package||'delete_salary_history';
3230 l_change_date date;
3231 --
3232 cursor c_del_salary_proposals( p_assignment_id in number
3233 ,p_date in date) is
3234 select pay_proposal_id, business_group_id, object_version_number
3235 from per_pay_proposals
3236 where assignment_id = p_assignment_id
3237 and change_date <
3238 (select max(change_date)
3239 from per_pay_proposals
3240 where assignment_id = p_assignment_id
3241 and change_date <= p_date
3242 );
3243 --
3244 cursor components(p_pay_proposal_id number, p_business_group_id number) is
3245 select ppc.component_id
3246 , ppc.object_version_number
3247 from per_pay_proposal_components ppc
3248 where ppc.pay_proposal_id=p_pay_proposal_id
3249 and ppc.business_group_id=p_business_group_id;
3250 --
3251 begin
3252 hr_utility.set_location('Entering:'||l_proc,5);
3253 --
3254 --
3255 -- Issue a savepoint
3256 --
3257 savepoint delete_salary_history;
3258 --
3259 --
3260 FOR r_del_sp in c_del_salary_proposals( p_assignment_id ,p_date)
3261 LOOP
3262 hr_utility.set_location('Inside loop salary_proposal - '||
3263 r_del_sp.pay_proposal_id||l_proc,10);
3264 --
3265 -- first of all delete all of the components
3266 --
3267 FOR l_components IN components(r_del_sp.pay_proposal_id,
3268 r_del_sp.business_group_id)
3269 LOOP
3270 hr_utility.set_location('Inside loop component - '||
3271 l_components.component_id||l_proc,15);
3272 per_ppc_del.del(p_component_id=>l_components.component_id
3273 ,p_object_version_number=>l_components.object_version_number
3274 ,p_validation_strength=>'WEAK'
3275 ,p_validate=>FALSE);
3276 END LOOP;
3277 hr_utility.set_location(l_proc,20);
3278 --
3279 -- Update CWB table that created/updated the salary proposal
3280 --
3281 update BEN_CWB_PERSON_RATES
3282 set PAY_PROPOSAL_ID = null
3283 where PAY_PROPOSAL_ID = r_del_sp.pay_proposal_id;
3284 --
3285 -- Delete Salary Proposal
3286 --
3287 delete per_pay_proposals
3288 where pay_proposal_id = r_del_sp.pay_proposal_id;
3289 --
3290 hr_utility.set_location(l_proc,25);
3291 --
3292 END LOOP;
3293 hr_utility.set_location('Exiting:'||l_proc,50);
3294 EXCEPTION
3295 When others then
3296 --
3297 -- A validation or unexpected error has occured
3298 --
3299 hr_utility.set_location('When Others:'||l_proc,55);
3300 ROLLBACK TO delete_salary_proposal;
3301 raise;
3302 --
3303 --
3304 end;
3305 --
3306 end hr_maintain_proposal_api;
3307