[Home] [Help]
PACKAGE BODY: APPS.HR_UPLOAD_PROPOSAL_API
Source
1 Package Body hr_upload_proposal_api as
2 /* $Header: hrpypapi.pkb 120.30.12010000.13 2009/01/16 09:51:09 schowdhu ship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := 'hr_upload_proposal_api.';
7 MAX_COMP_NO number(10) := 10;
8 --
9 -- define plsql table types
10 --
11 TYPE t_of_number2 is table of
12 per_pay_proposal_components.change_percentage%TYPE
13 index by binary_integer;
14 --
15 TYPE t_of_number is table of
16 per_pay_proposal_components.component_id%TYPE
17 index by binary_integer;
18 --
19 TYPE t_of_char is table of
20 per_pay_proposal_components.component_reason%TYPE
21 index by binary_integer;
22
23 --------vkodedal 7-mar-07
24 -----------------------------------------------------------------------------
25 -- | ---------------------------< update_last_change_date>-----------------
26 -----------------------------------------------------------------------------
27 --
28 -- Description
29 -- This procedure is used to correct the last_change_date of the next record with the current change date.
30 -- This procedure is called when a record is inserted, or deleted so that correct last_change_date is maintained
31 -- in the immediate next proposal.
32 -- Assignment Id and Change date of the inserted or change date of the proposal previous to the deleted proposal
33 -- are passed as in parameters.
34 --
35 Procedure update_last_change_date(p_assignment_id in number
36 ,p_change_date in date) is
37
38 cursor csr_next_proposal is
39 select pay_proposal_id, rowid
40 from per_pay_proposals
41 where assignment_id = p_assignment_id
42 and change_date=(
43 select min(change_date)
44 from per_pay_proposals
45 where assignment_id = p_assignment_id
46 and change_date > p_change_date);
47
48 l_last_change_date per_pay_proposals.last_change_date%TYPE;
49 l_pay_proposal_id per_pay_proposals.pay_proposal_id%TYPE;
50 l_row_id rowid;
51 l_proc varchar2(30):= 'update_last_change_date';
52
53 begin
54
55 hr_utility.set_location('Entering:'||l_proc,5);
56 hr_utility.set_location('p_assignment_id:'||p_assignment_id,15);
57 hr_utility.set_location('p_change_date:'||p_change_date,25);
58
59 l_last_change_date:=p_change_date;
60
61 OPEN csr_next_proposal;
62 FETCH csr_next_proposal into l_pay_proposal_id,l_row_id;
63
64 hr_utility.set_location('l_pay_proposal_id:'||l_pay_proposal_id,15);
65
66 if csr_next_proposal%FOUND then
67 hr_utility.set_location('Cursor found:',25);
68
69 update per_pay_proposals
70 set last_change_date = l_last_change_date
71 where rowid=l_row_id;
72 hr_utility.set_location('Updated successfuly:',35);
73 end if;
74
75 CLOSE csr_next_proposal;
76
77 hr_utility.set_location('Leaving:'||l_proc,5);
78
79 EXCEPTION
80 When others then
81 --
82 -- An unexpected error has occured
83 --
84 hr_utility.set_location('When Others:'||l_proc,5);
85 raise;
86 --
87 end update_last_change_date;
88
89 --
90 --
91 -----------------------------------------------------------------------------
92 -- | ---------------------------< end_date_proposed_proposal>-----------------
93 -----------------------------------------------------------------------------
94 --
95 -- Description
96 -- This procedure is used to end date the proposed proposals
97 -- which exists prior to the p_date_to. It is called for
98 -- proposed proposals only.
99 --
100 -- Procedure added to enddate proposed proposals only. Bug#7386307 by schowdhu
101
102 Procedure end_date_proposed_proposal(p_assignment_id in number
103 ,p_date_to in date) is
104
105 Cursor csr_prev_prop_details
106 is
107 select pay_proposal_id,date_to
108 from per_pay_proposals
109 where assignment_id = p_assignment_id
110 and change_date =(select max(change_date)
111 from per_pay_proposals
112 where assignment_id = p_assignment_id
113 and change_date < p_date_to+1 and approved = 'N');
114
115 l_date_to per_pay_proposals.date_to%TYPE;
116 l_pay_proposal_id per_pay_proposals.pay_proposal_id%TYPE;
117 l_proc varchar2(30):= 'end_date_proposed_proposal';
118
119 begin
120
121 hr_utility.set_location('Entering:'||l_proc,5);
122 hr_utility.set_location('p_assignment_id:'||p_assignment_id,15);
123 hr_utility.set_location('p_date_to:'||p_date_to,25);
124
125 OPEN csr_prev_prop_details;
126 FETCH csr_prev_prop_details into l_pay_proposal_id,l_date_to;
127
128 hr_utility.set_location('l_pay_proposal_id:'||l_pay_proposal_id,15);
129 hr_utility.set_location('l_date_to:'||l_date_to,25);
130
131 if csr_prev_prop_details%FOUND then
132 hr_utility.set_location('Cursor found:',35);
133 if l_date_to is null OR l_date_to > p_date_to then
134 hr_utility.set_location('About to update',45);
135 update per_pay_proposals
136 set date_to = p_date_to,
137 -- added by vkodedal fix for 6831216
138 last_update_date = sysdate,
139 last_updated_by = fnd_global.user_id,
140 last_update_login = fnd_global.login_id
141 -- end of fix
142 where assignment_id = p_assignment_id
143 and pay_proposal_id = l_pay_proposal_id;
144 hr_utility.set_location('Updated successfuly:',85);
145 end if;
146 end if;
147 CLOSE csr_prev_prop_details;
148
149 hr_utility.set_location('Leaving:'||l_proc,5);
150
151 end end_date_proposed_proposal;
152 --
153 --
154 -----------------------------------------------------------------------------
155 -- | ---------------------------< end_date_approved_proposal>-----------------
156 -----------------------------------------------------------------------------
157 --
158 -- Description
159 -- This procedure is used to end date the approved proposals
160 -- which exists prior to the p_date_to. It is called for
161 -- approved proposals only. All the proposed proposals
162 -- prior to the p_date_to will be deleted.
163 --
164 -- Procedure added to enddate approved proposals only. Bug#7386307 by schowdhu
165
166 -- changed by schowdhu for 7673294 05-jan-08
167 Procedure end_date_approved_proposal(p_assignment_id in number
168 ,p_date_to in date
169 ,p_pay_proposal_id in number default null) is
170
171 Cursor csr_prev_prop_details
172 is
173 select pay_proposal_id, date_to
174 from per_pay_proposals
175 where assignment_id = p_assignment_id
176 and change_date =(select max(change_date)
177 from per_pay_proposals
178 where assignment_id = p_assignment_id
179 and change_date < p_date_to+1 and approved = 'Y');
180
181 -- cursor added to find the proposed proposals to be deleted
182 -- by schowdhu Bug #7386307
183
184 cursor get_all_proposed_proposals
185 is
186 select pay_proposal_id,object_version_number, business_group_id
187 from per_pay_proposals
188 where assignment_id = p_assignment_id
189 --added for the bug 7673294 to exclude the calling proposal
190 and pay_proposal_id <> p_pay_proposal_id
191 and change_date < p_date_to+1
192 and approved = 'N';
193
194 l_date_to per_pay_proposals.date_to%TYPE;
195 l_pay_proposal_id per_pay_proposals.pay_proposal_id%TYPE;
196 l_proc varchar2(30):= 'end_date_approved_proposal';
197 l_change_date per_pay_proposals.change_date%TYPE;
198 l_del_warn boolean;
199
200 begin
201
202 hr_utility.set_location('Entering:'||l_proc,5);
203 hr_utility.set_location('p_assignment_id:'||p_assignment_id,15);
204 hr_utility.set_location('p_date_to:'||p_date_to,25);
205
206 OPEN csr_prev_prop_details;
207 FETCH csr_prev_prop_details into l_pay_proposal_id,l_date_to;
208
209 if csr_prev_prop_details%FOUND then
210 hr_utility.set_location('Cursor found:',35);
211 if l_date_to is null OR l_date_to > p_date_to then
212 hr_utility.set_location('About to update',45);
213 update per_pay_proposals
214 set date_to = p_date_to,
215 -- added by vkodedal fix for 6831216
216 last_update_date = sysdate,
217 last_updated_by = fnd_global.user_id,
218 last_update_login = fnd_global.login_id
219 -- end of fix
220 where assignment_id = p_assignment_id
221 and pay_proposal_id = l_pay_proposal_id;
222 hr_utility.set_location('Updated successfuly:',85);
223 end if;
224 end if;
225 CLOSE csr_prev_prop_details;
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 -- | ---------------------------< end_date_salary_proposal>-----------------
247 -----------------------------------------------------------------------------
248 --
249 /* Procedure modified. Bug#7386307 by schowdhu */
250
251 Procedure end_date_salary_proposal(p_assignment_id in number
252 ,p_date_to in date
253 ,p_proposal_id in number default NULL) is
254 /*
255 Cursor csr_prev_prop_details
256 is
257 select pay_proposal_id,date_to
258 from per_pay_proposals
259 where assignment_id = p_assignment_id
260 and change_date =(select max(change_date)
261 from per_pay_proposals
262 where assignment_id = p_assignment_id
263 and change_date < p_date_to+1);
264
265 l_date_to per_pay_proposals.date_to%TYPE;
266 l_pay_proposal_id per_pay_proposals.pay_proposal_id%TYPE;
267 */
268 l_proc varchar2(30):= 'end_date_salary_proposal';
269 l_approved per_pay_proposals.approved%TYPE;
270
271 -- changed by schowdhu for 7673294 05-jan-08
272 Cursor chk_approved_flg
273 is
274 select approved
275 from per_pay_proposals
276 where pay_proposal_id = nvl(p_proposal_id, pay_proposal_id)
277 and assignment_id = p_assignment_id
278 and change_date = p_date_to +1 ;
279 begin
280
281 hr_utility.set_location('Entering:'||l_proc,5);
282 hr_utility.set_location('p_assignment_id:'||p_assignment_id,15);
283 hr_utility.set_location('p_date_to:'||p_date_to,25);
284 /*
285 OPEN csr_prev_prop_details;
286 FETCH csr_prev_prop_details into l_pay_proposal_id,l_date_to;
287
288 hr_utility.set_location('l_pay_proposal_id:'||l_pay_proposal_id,15);
289 hr_utility.set_location('l_date_to:'||l_date_to,25);
290
291
292 if csr_prev_prop_details%FOUND then
293 hr_utility.set_location('Cursor found:',35);
294 if l_date_to is null OR l_date_to > p_date_to then
295 hr_utility.set_location('About to update',45);
296 update per_pay_proposals
297 set date_to = p_date_to,
298 -- added by vkodedal fix for 6831216
299 last_update_date = sysdate,
300 last_updated_by = fnd_global.user_id,
301 last_update_login = fnd_global.login_id
302 -- end of fix
303 where assignment_id = p_assignment_id
304 and pay_proposal_id = l_pay_proposal_id;
305 hr_utility.set_location('Updated successfuly:',85);
306 end if;
307 end if;
308 CLOSE csr_prev_prop_details;
309 */
310 OPEN chk_approved_flg;
311 FETCH chk_approved_flg into l_approved;
312 if l_approved = 'N' then
313 end_date_proposed_proposal (p_assignment_id, p_date_to);
314 else
315 -- changed by schowdhu for 7673294 05-jan-08
316 end_date_approved_proposal (p_assignment_id, p_date_to, p_proposal_id);
317 end if;
318 CLOSE chk_approved_flg;
319
320 hr_utility.set_location('Leaving:'||l_proc,5);
321
322 end end_date_salary_proposal;
323
324 -----------------------------------------------------------------------------
325 -- | ---------------------------< create_sql_table>--------------------------
326 -----------------------------------------------------------------------------
327 --
328 -- Description
329 -- This procedure is used to initialise 5 sql_tables.
330 -- the plsql_tables are the out parameters.
331 --
332 --
333 procedure create_sql_table
334 (p_component_reason_1 in varchar2 default null
335 ,p_change_amount_1 in number default null
336 ,p_change_percentage_1 in number default null
337 ,p_component_id_1 in number default null
338 ,p_approved_1 in varchar2
339 ,p_object_version_number_1 in number default null
340 --
341 ,p_component_reason_2 in varchar2 default null
342 ,p_change_amount_2 in number default null
343 ,p_change_percentage_2 in number default null
344 ,p_component_id_2 in number default null
345 ,p_approved_2 in varchar2
346 ,p_object_version_number_2 in number default null
347 --
348 ,p_component_reason_3 in varchar2 default null
349 ,p_change_amount_3 in number default null
350 ,p_change_percentage_3 in number default null
351 ,p_component_id_3 in number default null
352 ,p_approved_3 in varchar2
353 ,p_object_version_number_3 in number default null
354 --
355 ,p_component_reason_4 in varchar2 default null
356 ,p_change_amount_4 in number default null
357 ,p_change_percentage_4 in number default null
358 ,p_component_id_4 in number default null
359 ,p_approved_4 in varchar2
360 ,p_object_version_number_4 in number default null
361 --
362 ,p_component_reason_5 in varchar2 default null
363 ,p_change_amount_5 in number default null
364 ,p_change_percentage_5 in number default null
365 ,p_component_id_5 in number default null
366 ,p_approved_5 in varchar2
367 ,p_object_version_number_5 in number default null
368 --
369 ,p_component_reason_6 in varchar2 default null
370 ,p_change_amount_6 in number default null
371 ,p_change_percentage_6 in number default null
372 ,p_component_id_6 in number default null
373 ,p_approved_6 in varchar2
374 ,p_object_version_number_6 in number default null
375 --
376 ,p_component_reason_7 in varchar2 default null
377 ,p_change_amount_7 in number default null
378 ,p_change_percentage_7 in number default null
379 ,p_component_id_7 in number default null
380 ,p_approved_7 in varchar2
381 ,p_object_version_number_7 in number default null
382 --
383 ,p_component_reason_8 in varchar2 default null
384 ,p_change_amount_8 in number default null
385 ,p_change_percentage_8 in number default null
386 ,p_component_id_8 in number default null
387 ,p_approved_8 in varchar2
388 ,p_object_version_number_8 in number default null
389 --
390 ,p_component_reason_9 in varchar2 default null
391 ,p_change_amount_9 in number default null
392 ,p_change_percentage_9 in number default null
393 ,p_component_id_9 in number default null
394 ,p_approved_9 in varchar2
395 ,p_object_version_number_9 in number default null
396 --
397 ,p_component_reason_10 in varchar2 default null
398 ,p_change_amount_10 in number default null
399 ,p_change_percentage_10 in number default null
400 ,p_component_id_10 in number default null
401 ,p_approved_10 in varchar2
402 ,p_object_version_number_10 in number default null
403 ,p_comp_reason_table out nocopy t_of_char
404 ,p_change_amount_table out nocopy t_of_number2
405 ,p_change_percentage_table out nocopy t_of_number2
406 ,p_comp_id_table out nocopy t_of_number
407 ,p_approved_table out nocopy t_of_char
408 ,p_ovn_table out nocopy t_of_number
409 ) is
410 --
411 --
412 begin
413 --
414 p_comp_reason_table(1) := p_component_reason_1;
415 p_comp_reason_table(2) := p_component_reason_2;
416 p_comp_reason_table(3) := p_component_reason_3;
417 p_comp_reason_table(4) := p_component_reason_4;
418 p_comp_reason_table(5) := p_component_reason_5;
419 p_comp_reason_table(6) := p_component_reason_6;
420 p_comp_reason_table(7) := p_component_reason_7;
421 p_comp_reason_table(8) := p_component_reason_8;
422 p_comp_reason_table(9) := p_component_reason_9;
423 p_comp_reason_table(10) := p_component_reason_10;
424 --
425 p_change_amount_table(1) := p_change_amount_1;
426 p_change_amount_table(2) := p_change_amount_2;
427 p_change_amount_table(3) := p_change_amount_3;
428 p_change_amount_table(4) := p_change_amount_4;
429 p_change_amount_table(5) := p_change_amount_5;
430 p_change_amount_table(6) := p_change_amount_6;
431 p_change_amount_table(7) := p_change_amount_7;
432 p_change_amount_table(8) := p_change_amount_8;
433 p_change_amount_table(9) := p_change_amount_9;
434 p_change_amount_table(10):= p_change_amount_10;
435 --
436 p_change_percentage_table(1) := p_change_percentage_1;
437 p_change_percentage_table(2) := p_change_percentage_2;
438 p_change_percentage_table(3) := p_change_percentage_3;
439 p_change_percentage_table(4) := p_change_percentage_4;
440 p_change_percentage_table(5) := p_change_percentage_5;
441 p_change_percentage_table(6) := p_change_percentage_6;
442 p_change_percentage_table(7) := p_change_percentage_7;
443 p_change_percentage_table(8) := p_change_percentage_8;
444 p_change_percentage_table(9) := p_change_percentage_9;
445 p_change_percentage_table(10):= p_change_percentage_10;
446 --
447 p_ovn_table(1) := p_object_version_number_1;
448 p_ovn_table(2) := p_object_version_number_2;
449 p_ovn_table(3) := p_object_version_number_3;
450 p_ovn_table(4) := p_object_version_number_4;
451 p_ovn_table(5) := p_object_version_number_5;
452 p_ovn_table(6) := p_object_version_number_6;
453 p_ovn_table(7) := p_object_version_number_7;
454 p_ovn_table(8) := p_object_version_number_8;
455 p_ovn_table(9) := p_object_version_number_9;
456 p_ovn_table(10) := p_object_version_number_10;
457 --
458 p_approved_table(1) := p_approved_1;
459 p_approved_table(2) := p_approved_2;
460 p_approved_table(3) := p_approved_3;
461 p_approved_table(4) := p_approved_4;
462 p_approved_table(5) := p_approved_5;
463 p_approved_table(6) := p_approved_6;
464 p_approved_table(7) := p_approved_7;
465 p_approved_table(8) := p_approved_8;
466 p_approved_table(9) := p_approved_9;
467 p_approved_table(10) := p_approved_10;
468 --
469 p_comp_id_table(1) := p_component_id_1;
470 p_comp_id_table(2) := p_component_id_2;
471 p_comp_id_table(3) := p_component_id_3;
472 p_comp_id_table(4) := p_component_id_4;
473 p_comp_id_table(5) := p_component_id_5;
474 p_comp_id_table(6) := p_component_id_6;
475 p_comp_id_table(7) := p_component_id_7;
476 p_comp_id_table(8) := p_component_id_8;
477 p_comp_id_table(9) := p_component_id_9;
478 p_comp_id_table(10) := p_component_id_10;
479 --
480 end create_sql_table;
481 --
482 -------------------------------------------------------------------------
483 --| -----------------------< get_value >---------------------------------
484 ------------------------------------------------------------------------
485 --
486 -- Description:
487 -- this function takes a plsql_table and an integer as it argument
488 -- and it returns a number format NUMBER(9,2)
489 -- This function is used when the local parameter need to be initilased
490 -- with procedure parameter.
491 --
492 --
493 function get_value(p_table t_of_number2 ,i number) return number is
494 begin
495 return p_table(i);
496 end get_value;
497 --
498 --
499 -------------------------------------------------------------------------
500 --| -----------------------< get_value >---------------------------------
501 ------------------------------------------------------------------------
502 --
503 -- Description:
504 -- this function takes a plsql_table and an integer as it argument
505 -- and it returns a number.
506 -- This function is used when the local parameter need to be initilased
507 -- with procedure parameter.
508 --
509 --
510 function get_value(p_table t_of_number ,i number) return number is
511 begin
512 return p_table(i);
513 end get_value;
514 --
515 --
516 -------------------------------------------------------------------------
517 --| -----------------------< get_value >---------------------------------
518 ------------------------------------------------------------------------
519 --
520 -- Description:
521 -- this function takes a plsql_table and an integer as it argument
522 -- and it returns a varchar2.
523 -- This function is used when the local parameter need to be initilased
524 -- with procedure parameter.
525 --
526 --
527 function get_value(p_table t_of_char ,i number) return varchar2 is
528 begin
529 return p_table(i);
530 end get_value;
531 --
532 ----------------------------------------------------------------------------
533 --|-------------------------< is_component_exist >---------------------------
534 ---------------------------------------------------------------------------
535 -- Description
536 -- This function loops through all the components and check if they
537 -- are not null. This function returns true if one or more components
538 -- are not null otherwise it returns false.
539 --
540 function is_component_exist
541 (
542 p_change_amount_table t_of_number2
543 ,p_change_percentage_table t_of_number2
544 ) return boolean is
545 --
546 l_out boolean := false;
547 begin
548 --
549 for i in 1 .. MAX_COMP_NO
550 LOOP
551 if (
552 p_change_amount_table(i) IS NOT NULL OR
553 p_change_percentage_table(i) IS NOT NULL) then
554 l_out := true;
555 end if;
556 end loop;
557 --
558 return l_out;
559 end is_component_exist;
560 --
561 -- ----------------------------------------------------------------------------
562 -- |--------------------------< upload_salary_proposal >----------------------|
563 -- ----------------------------------------------------------------------------
564 --
565 procedure upload_salary_proposal
566 (p_validate in boolean
567 ,p_change_date in date
568 ,p_business_group_id in number
569 ,p_assignment_id in number
570 ,p_proposed_salary in number
571 ,p_proposal_reason in varchar2
572 ,p_next_sal_review_date in date -- Bug 1620922
573 ,p_forced_ranking in number
574 ,p_date_to in date
575 ,p_pay_proposal_id in out nocopy number
576 ,p_object_version_number in out nocopy number
577 --
578 ,p_component_reason_1 in varchar2
579 ,p_change_amount_1 in number
580 ,p_change_percentage_1 in number
581 ,p_approved_1 in varchar2
582 ,p_component_id_1 in out nocopy number
583 ,p_ppc_object_version_number_1 in out nocopy number
584 --
585 ,p_component_reason_2 in varchar2
586 ,p_change_amount_2 in number
587 ,p_change_percentage_2 in number
588 ,p_approved_2 in varchar2
589 ,p_component_id_2 in out nocopy number
590 ,p_ppc_object_version_number_2 in out nocopy number
591 --
592 ,p_component_reason_3 in varchar2
593 ,p_change_amount_3 in number
594 ,p_change_percentage_3 in number
595 ,p_approved_3 in varchar2
596 ,p_component_id_3 in out nocopy number
597 ,p_ppc_object_version_number_3 in out nocopy number
598 --
599 ,p_component_reason_4 in varchar2
600 ,p_change_amount_4 in number
601 ,p_change_percentage_4 in number
602 ,p_approved_4 in varchar2
603 ,p_component_id_4 in out nocopy number
604 ,p_ppc_object_version_number_4 in out nocopy number
605 --
606 ,p_component_reason_5 in varchar2
607 ,p_change_amount_5 in number
608 ,p_change_percentage_5 in number
609 ,p_approved_5 in varchar2
610 ,p_component_id_5 in out nocopy number
611 ,p_ppc_object_version_number_5 in out nocopy number
612 --
613 ,p_component_reason_6 in varchar2
614 ,p_change_amount_6 in number
615 ,p_change_percentage_6 in number
616 ,p_approved_6 in varchar2
617 ,p_component_id_6 in out nocopy number
618 ,p_ppc_object_version_number_6 in out nocopy number
619 --
620 ,p_component_reason_7 in varchar2
621 ,p_change_amount_7 in number
622 ,p_change_percentage_7 in number
623 ,p_approved_7 in varchar2
624 ,p_component_id_7 in out nocopy number
625 ,p_ppc_object_version_number_7 in out nocopy number
626 --
627 ,p_component_reason_8 in varchar2
628 ,p_change_amount_8 in number
629 ,p_change_percentage_8 in number
630 ,p_approved_8 in varchar2
631 ,p_component_id_8 in out nocopy number
632 ,p_ppc_object_version_number_8 in out nocopy number
633 --
634 ,p_component_reason_9 in varchar2
635 ,p_change_amount_9 in number
636 ,p_change_percentage_9 in number
637 ,p_approved_9 in varchar2
638 ,p_component_id_9 in out nocopy number
639 ,p_ppc_object_version_number_9 in out nocopy number
640 --
641 ,p_component_reason_10 in varchar2
642 ,p_change_amount_10 in number
643 ,p_change_percentage_10 in number
644 ,p_approved_10 in varchar2
645 ,p_component_id_10 in out nocopy number
646 ,p_ppc_object_version_number_10 in out nocopy number
647 --
648 ,p_pyp_proposed_sal_warning out nocopy boolean
649 ,p_additional_comp_warning out nocopy boolean
650 --
651 /* Added for Desc flex support for Web ADI */
652 ,p_attribute_category in varchar2 default null
653 ,p_attribute1 in varchar2 default null
654 ,p_attribute2 in varchar2 default null
655 ,p_attribute3 in varchar2 default null
656 ,p_attribute4 in varchar2 default null
657 ,p_attribute5 in varchar2 default null
658 ,p_attribute6 in varchar2 default null
659 ,p_attribute7 in varchar2 default null
660 ,p_attribute8 in varchar2 default null
661 ,p_attribute9 in varchar2 default null
662 ,p_attribute10 in varchar2 default null
663 ,p_attribute11 in varchar2 default null
664 ,p_attribute12 in varchar2 default null
665 ,p_attribute13 in varchar2 default null
666 ,p_attribute14 in varchar2 default null
667 ,p_attribute15 in varchar2 default null
668 ,p_attribute16 in varchar2 default null
669 ,p_attribute17 in varchar2 default null
670 ,p_attribute18 in varchar2 default null
671 ,p_attribute19 in varchar2 default null
672 ,p_attribute20 in varchar2 default null
673 ) is
674 --
675 -- Declare cursors and local variables
676 --
677
678 l_proc varchar2(72) := g_package||'upload_salary_proposal';
679 l_is_component_not_null boolean;
680 l_component_cal boolean := false;
681 l_inv_next_sal_date_warning boolean;
682 l_inv_next_perf_date_warning boolean;
683 l_approved_warning boolean;
684 l_payroll_warning boolean;
685 l_proposed_salary_warning boolean;
686 l_last_proposed_salary per_pay_proposals.proposed_salary_n%TYPE;
687 l_pay_proposal_id per_pay_proposals.pay_proposal_id%TYPE;
688 l_object_version_number per_pay_proposals.object_version_number%TYPE;
689 l_pyp_proposed_sal_warning boolean;
690 l_additional_comp_warning boolean;
691 l_autoApprove varchar2(1); --added by vkodedal 08-Oct-2007 ER auto Approve first proposal
692 l_approved VARCHAR2(1);
693 --
694 --
695 l_proposed_salary per_pay_proposals.proposed_salary_n%TYPE;
696 l_proposed_salary_db per_pay_proposals.proposed_salary_n%TYPE;
697 l_change_date per_pay_proposals.change_date%TYPE;
698 l_multiple_components per_pay_proposals.multiple_components%TYPE;
699 l_pyp_approved per_pay_proposals.approved%TYPE;
700 l_proposal_reason per_pay_proposals.proposal_reason%TYPE;
701 l_pyp_object_version_number
702 per_pay_proposals.object_version_number%TYPE;
703 --
704 l_change_amount per_pay_proposal_components.change_amount_n%TYPE;
705 l_change_percentage per_pay_proposal_components.change_percentage%TYPE;
706 l_component_reason per_pay_proposal_components.component_reason%TYPE;
707 l_ppc_approved per_pay_proposal_components.approved%TYPE;
708 l_ppc_object_version_number
709 per_pay_proposal_components.object_version_number%TYPE;
710 --
711 l_change_amount_in per_pay_proposal_components.change_amount_n%TYPE;
712 l_change_percentage_in per_pay_proposal_components.change_percentage%TYPE;
713 l_component_reason_in per_pay_proposal_components.component_reason%TYPE;
714 l_approved_in per_pay_proposal_components.approved%TYPE;
715 l_object_version_number_in
716 per_pay_proposal_components.object_version_number%TYPE;
717 l_component_id_in per_pay_proposal_components.component_id%TYPE;
718 l_component_sum number(20,6):=0;
719 l_total_component_sum number (20,6):=0;
720 l_comp_update boolean := false;
721 l_prop_update boolean := false;
722 --
723 l_pyp_pay_pro_id per_pay_proposals.pay_proposal_id%TYPE:=
724 p_pay_proposal_id;
725 l_pyp_ovn per_pay_proposals.object_version_number%TYPE:=
726 p_object_version_number;
727 --
728 l_component_id_1 per_pay_proposal_components.component_id%TYPE:=
729 p_component_id_1;
730 l_ppc_ovn_1 per_pay_proposal_components.object_version_number%TYPE:=
731 p_ppc_object_version_number_1;
732 --
733 l_component_id_2 per_pay_proposal_components.component_id%TYPE:=
734 p_component_id_2;
735 l_ppc_ovn_2 per_pay_proposal_components.object_version_number%TYPE:=
736 p_ppc_object_version_number_2;
737 --
738 l_component_id_3 per_pay_proposal_components.component_id%TYPE:=
739 p_component_id_3;
740 l_ppc_ovn_3 per_pay_proposal_components.object_version_number%TYPE:=
741 p_ppc_object_version_number_3;
742 --
743 l_component_id_4 per_pay_proposal_components.component_id%TYPE:=
744 p_component_id_4;
745 l_ppc_ovn_4 per_pay_proposal_components.object_version_number%TYPE:=
746 p_ppc_object_version_number_4;
747 --
748 l_component_id_5 per_pay_proposal_components.component_id%TYPE:=
749 p_component_id_5;
750 l_ppc_ovn_5 per_pay_proposal_components.object_version_number%TYPE:=
751 p_ppc_object_version_number_5;
752 --
753 l_component_id_6 per_pay_proposal_components.component_id%TYPE:=
754 p_component_id_6;
755 l_ppc_ovn_6 per_pay_proposal_components.object_version_number%TYPE:=
756 p_ppc_object_version_number_6;
757 --
758 l_component_id_7 per_pay_proposal_components.component_id%TYPE:=
759 p_component_id_7;
760 l_ppc_ovn_7 per_pay_proposal_components.object_version_number%TYPE:=
761 p_ppc_object_version_number_7;
762 --
763 l_component_id_8 per_pay_proposal_components.component_id%TYPE:=
764 p_component_id_8;
765 l_ppc_ovn_8 per_pay_proposal_components.object_version_number%TYPE:=
766 p_ppc_object_version_number_8;
767 --
768 l_component_id_9 per_pay_proposal_components.component_id%TYPE:=
769 p_component_id_9;
770 l_ppc_ovn_9 per_pay_proposal_components.object_version_number%TYPE:=
771 p_ppc_object_version_number_9;
772 --
773 l_component_id_10 per_pay_proposal_components.component_id%TYPE:=
774 p_component_id_10;
775 l_ppc_ovn_10 per_pay_proposal_components.object_version_number%TYPE:=
776 p_ppc_object_version_number_10;
777
778
779 lt_pay_proposal_id number := p_pay_proposal_id;
780 lt_object_version_number number := p_object_version_number;
781 lt_component_id_1 number := p_component_id_1;
782 lt_ppc_ovn_1 number := p_ppc_object_version_number_1;
783 lt_component_id_2 number := p_component_id_2;
784 lt_ppc_ovn_2 number := p_ppc_object_version_number_2;
785 lt_component_id_3 number := p_component_id_3;
786 lt_ppc_ovn_3 number := p_ppc_object_version_number_3;
787 lt_component_id_4 number := p_component_id_4;
788 lt_ppc_ovn_4 number := p_ppc_object_version_number_4;
789 lt_component_id_5 number := p_component_id_5;
790 lt_ppc_ovn_5 number := p_ppc_object_version_number_5;
791 lt_component_id_6 number := p_component_id_6;
792 lt_ppc_ovn_6 number := p_ppc_object_version_number_6;
793 lt_component_id_7 number := p_component_id_7;
794 lt_ppc_ovn_7 number := p_ppc_object_version_number_7;
795 lt_component_id_8 number := p_component_id_8;
796 lt_ppc_ovn_8 number := p_ppc_object_version_number_8;
797 lt_component_id_9 number := p_component_id_9;
798 lt_ppc_ovn_9 number := p_ppc_object_version_number_9;
799 lt_component_id_10 number := p_component_id_10;
800 lt_ppc_ovn_10 number := p_ppc_object_version_number_10;
801 l_date_to PER_PAY_PROPOSALS.DATE_TO%TYPE;
802 l_next_change_date PER_PAY_PROPOSALS.CHANGE_DATE%TYPE;
803 --
804 -- plsql table types
805 --
806 l_comp_reason_table t_of_char;
807 l_comp_id_table t_of_number;
808 l_ovn_table t_of_number;
809 l_approved_table t_of_char;
810 l_change_amount_table t_of_number2;
811 l_change_percentage_table t_of_number2;
812 --
813 -- elements
814 --
815 l_next_sal_review_date per_pay_proposals.next_sal_review_date%TYPE;
816
817 l_element_type_id pay_element_types_f.element_type_id%TYPE;
818 l_input_value_id pay_input_values_f.input_value_id%TYPE;
819 l_element_link_id pay_element_links_f.element_link_id%TYPE;
820 l_element_entry_id pay_element_entries_f.element_entry_id%TYPE;
821 l_trunc_date date := p_change_date;
822 l_effective_end_date date;
823 l_effective_start_date date := l_trunc_date;
824 --
825 -- Cursor to get the sum of all the components for a salary proposal.
826 -- This cursor is used to ensure that all the components are accounted for.
827 -- For example, if a user has more than ten components then this
828 -- business process processes the first 10 and add any additionals as well.
829 --
830 cursor csr_sum_of_components
831 (c_pay_proposal_id per_pay_proposals.pay_proposal_id%TYPE) is
832 select sum(change_amount_n)
833 from per_pay_proposal_components
834 where pay_proposal_id = c_pay_proposal_id;
835
836 --
837 --
838 -- Cursor to get the element_type and input value details
839 -- this cursor is used when the first proposal gets approved.
840 --
841 cursor csr_get_element_detail is
842 select pet.element_type_id,
843 piv.input_value_id
844 from
845 pay_element_types_f pet,
846 pay_input_values_f piv,
847 per_pay_bases ppb,
848 per_all_assignments_f asg
849
850 where
851 pet.element_type_id = piv.element_type_id
852 and l_trunc_date BETWEEN pet.effective_start_date
853 and pet.effective_end_date
854 and piv.input_value_id = ppb.input_value_id
855 and l_trunc_date BETWEEN piv.effective_start_date
856 AND piv.effective_end_date
857 and ppb.pay_basis_id = asg.pay_basis_id
858 and asg.assignment_id = p_assignment_id
859 and l_trunc_date BETWEEN asg.effective_start_date
860 AND asg.effective_end_date;
861 --
862 -- cursor to get the last_proposed_salary which has been approved.
863 --
864 cursor csr_get_last_approved_salary is
865 select proposed_salary_n
866 from per_pay_proposals
867 where assignment_id = p_assignment_id
868 and change_date < l_trunc_date
869 order by change_date desc;
870 --
871 -- Cursor to get the proposal details
872 --
873 cursor csr_get_proposal_detail is
874 select change_date,
875 proposed_salary_n,
876 multiple_components,
877 approved,
878 proposal_reason,
879 object_version_number
880 from per_pay_proposals
881 where assignment_id = p_assignment_id
882 and pay_proposal_id = p_pay_proposal_id
883 and business_group_id = p_business_group_id;
884 --
885 -- cursor to get the component details
886 --
887 cursor csr_get_component_detail
888 (c_component_id per_pay_proposal_components.component_id%TYPE) is
889 select component_reason,
890 change_amount_n,
891 change_percentage,
892 approved,
893 object_version_number
894 from per_pay_proposal_components
895 where component_id = c_component_id
896 and business_group_id = p_business_group_id;
897 --
898 -- changed by schowdhu for bug #7693247 16-jan-2009
899 -- included p_change_date as input param
900 Cursor next_change_date(p_change_date DATE)
901 IS
902 select min(change_date)
903 from per_pay_proposals
904 where assignment_id = p_assignment_id
905 and change_date > p_change_date;
906
907 begin
908 hr_utility.set_location('Entering:'|| l_proc, 5);
909 --
910 -- Issue a savepoint
911 --
912 savepoint upload_salary_proposal;
913 --
914 -- initialise the local parameters
915 --
916 l_proposed_salary := p_proposed_salary;
917 l_pay_proposal_id := p_pay_proposal_id;
918 l_object_version_number := p_object_version_number ;
919 l_component_id_1 := p_component_id_1;
920 l_ppc_ovn_1 := p_ppc_object_version_number_1;
921 l_component_id_2 := p_component_id_2;
922 l_ppc_ovn_2 := p_ppc_object_version_number_2;
923 l_component_id_3 := p_component_id_3;
924 l_ppc_ovn_3 := p_ppc_object_version_number_3;
925 l_component_id_4 := p_component_id_4;
926 l_ppc_ovn_4 := p_ppc_object_version_number_4;
927 l_component_id_5 := p_component_id_5;
928 l_ppc_ovn_5 := p_ppc_object_version_number_5;
929 l_component_id_6 := p_component_id_6;
930 l_ppc_ovn_6 := p_ppc_object_version_number_6;
931 l_component_id_7 := p_component_id_7;
932 l_ppc_ovn_7 := p_ppc_object_version_number_7;
933 l_component_id_8 := p_component_id_8;
934 l_ppc_ovn_8 := p_ppc_object_version_number_8;
935 l_component_id_9 := p_component_id_9;
936 l_ppc_ovn_9 := p_ppc_object_version_number_9;
937 l_component_id_10 := p_component_id_10;
938 l_ppc_ovn_10 := p_ppc_object_version_number_10;
939
940 -- Truncate the time portion
941 --
942 l_change_date := trunc(p_change_date);
943 l_next_sal_review_date := trunc(p_next_sal_review_date);
944 --
945 -- Call Before Process User Hook upload_salary_proposal
946 --
947
948 l_date_to := p_date_to;
949
950 if l_date_to is null then
951
952 OPEN next_change_date(l_change_date);
953 fetch next_change_date into l_next_change_date;
954 close next_change_date;
955
956 if l_next_change_date is null then
957 l_date_to:= hr_general.end_of_time;
958 else
959 l_date_to := l_next_change_date-1;
960 end if;
961
962 end if;
963
964 begin
965 hr_upload_proposal_bk1.upload_salary_proposal_b
966 (
967 p_change_date => l_change_date
968 ,p_business_group_id => p_business_group_id
969 ,p_assignment_id => p_assignment_id
970 ,p_proposed_salary => p_proposed_salary
971 ,p_proposal_reason => p_proposal_reason
972 ,p_next_sal_review_date => l_next_sal_review_date -- Bug 1620922
973 ,p_forced_ranking => p_forced_ranking
974 ,p_date_to => l_date_to
975 ,p_pay_proposal_id => p_pay_proposal_id
976 ,p_object_version_number => p_object_version_number
977 --
978 ,p_component_reason_1 => p_component_reason_1
979 ,p_change_amount_1 => p_change_amount_1
980 ,p_change_percentage_1 => p_change_percentage_1
981 ,p_approved_1 => p_approved_1
982 ,p_component_id_1 => p_component_id_1
983 ,p_ppc_object_version_number_1 => p_ppc_object_version_number_1
984 --
985 ,p_component_reason_2 => p_component_reason_2
986 ,p_change_amount_2 => p_change_amount_2
987 ,p_change_percentage_2 => p_change_percentage_2
988 ,p_approved_2 => p_approved_2
989 ,p_component_id_2 => p_component_id_2
990 ,p_ppc_object_version_number_2 => p_ppc_object_version_number_2
991 --
992 ,p_component_reason_3 => p_component_reason_3
993 ,p_change_amount_3 => p_change_amount_3
994 ,p_change_percentage_3 => p_change_percentage_3
995 ,p_approved_3 => p_approved_3
996 ,p_component_id_3 => p_component_id_3
997 ,p_ppc_object_version_number_3 => p_ppc_object_version_number_3
998 --
999 ,p_component_reason_4 => p_component_reason_4
1000 ,p_change_amount_4 => p_change_amount_4
1001 ,p_change_percentage_4 => p_change_percentage_4
1002 ,p_approved_4 => p_approved_4
1003 ,p_component_id_4 => p_component_id_4
1004 ,p_ppc_object_version_number_4 => p_ppc_object_version_number_4
1005 --
1006 ,p_component_reason_5 => p_component_reason_5
1007 ,p_change_amount_5 => p_change_amount_5
1008 ,p_change_percentage_5 => p_change_percentage_5
1009 ,p_approved_5 => p_approved_5
1010 ,p_component_id_5 => p_component_id_5
1011 ,p_ppc_object_version_number_5 => p_ppc_object_version_number_5
1012 --
1013 ,p_component_reason_6 => p_component_reason_6
1014 ,p_change_amount_6 => p_change_amount_6
1015 ,p_change_percentage_6 => p_change_percentage_6
1016 ,p_approved_6 => p_approved_6
1017 ,p_component_id_6 => p_component_id_6
1018 ,p_ppc_object_version_number_6 => p_ppc_object_version_number_6
1019 --
1020 ,p_component_reason_7 => p_component_reason_7
1021 ,p_change_amount_7 => p_change_amount_7
1022 ,p_change_percentage_7 => p_change_percentage_7
1023 ,p_approved_7 => p_approved_7
1024 ,p_component_id_7 => p_component_id_7
1025 ,p_ppc_object_version_number_7 => p_ppc_object_version_number_7
1026 --
1027 ,p_component_reason_8 => p_component_reason_8
1028 ,p_change_amount_8 => p_change_amount_8
1029 ,p_change_percentage_8 => p_change_percentage_8
1030 ,p_approved_8 => p_approved_8
1031 ,p_component_id_8 => p_component_id_8
1032 ,p_ppc_object_version_number_8 => p_ppc_object_version_number_8
1033 --
1034 ,p_component_reason_9 => p_component_reason_9
1035 ,p_change_amount_9 => p_change_amount_9
1036 ,p_change_percentage_9 => p_change_percentage_9
1037 ,p_approved_9 => p_approved_9
1038 ,p_component_id_9 => p_component_id_9
1039 ,p_ppc_object_version_number_9 => p_ppc_object_version_number_9
1040 --
1041 ,p_component_reason_10 => p_component_reason_10
1042 ,p_change_amount_10 => p_change_amount_10
1043 ,p_change_percentage_10 => p_change_percentage_10
1044 ,p_approved_10 => p_approved_10
1045 ,p_component_id_10 => p_component_id_10
1046 ,p_ppc_object_version_number_10 => p_ppc_object_version_number_10
1047 );
1048 exception
1049 when hr_api.cannot_find_prog_unit then
1050 hr_api.cannot_find_prog_unit_error
1051 (p_module_name => 'UPLOAD_SALARY_PROPOSAL'
1052 ,p_hook_type => 'BP'
1053 );
1054 --
1055 -- End of API User Hook for the before hook of upload_salary_proposal
1056 --
1057 end;
1058 --
1059 -- make a table from the components parameter
1060 --
1061 create_sql_table
1062 (p_component_reason_1 => p_component_reason_1
1063 ,p_change_amount_1 => p_change_amount_1
1064 ,p_change_percentage_1 => p_change_percentage_1
1065 ,p_component_id_1 => p_component_id_1
1066 ,p_approved_1 => p_approved_1
1067 ,p_object_version_number_1 => p_ppc_object_version_number_1
1068 --
1069 ,p_component_reason_2 => p_component_reason_2
1070 ,p_change_amount_2 => p_change_amount_2
1071 ,p_change_percentage_2 => p_change_percentage_2
1072 ,p_component_id_2 => p_component_id_2
1073 ,p_approved_2 => p_approved_2
1074 ,p_object_version_number_2 => p_ppc_object_version_number_2
1075 --
1076 ,p_component_reason_3 => p_component_reason_3
1077 ,p_change_amount_3 => p_change_amount_3
1078 ,p_change_percentage_3 => p_change_percentage_3
1079 ,p_component_id_3 => p_component_id_3
1080 ,p_approved_3 => p_approved_3
1081 ,p_object_version_number_3 => p_ppc_object_version_number_3
1082 --
1083 ,p_component_reason_4 => p_component_reason_4
1084 ,p_change_amount_4 => p_change_amount_4
1085 ,p_change_percentage_4 => p_change_percentage_4
1086 ,p_component_id_4 => p_component_id_4
1087 ,p_approved_4 => p_approved_4
1088 ,p_object_version_number_4 => p_ppc_object_version_number_4
1089 --
1090 ,p_component_reason_5 => p_component_reason_5
1091 ,p_change_amount_5 => p_change_amount_5
1092 ,p_change_percentage_5 => p_change_percentage_5
1093 ,p_component_id_5 => p_component_id_5
1094 ,p_approved_5 => p_approved_5
1095 ,p_object_version_number_5 => p_ppc_object_version_number_5
1096 --
1097 ,p_component_reason_6 => p_component_reason_6
1098 ,p_change_amount_6 => p_change_amount_6
1099 ,p_change_percentage_6 => p_change_percentage_6
1100 ,p_component_id_6 => p_component_id_6
1101 ,p_approved_6 => p_approved_6
1102 ,p_object_version_number_6 => p_ppc_object_version_number_6
1103 --
1104 ,p_component_reason_7 => p_component_reason_7
1105 ,p_change_amount_7 => p_change_amount_7
1106 ,p_change_percentage_7 => p_change_percentage_7
1107 ,p_component_id_7 => p_component_id_7
1108 ,p_approved_7 => p_approved_7
1109 ,p_object_version_number_7 => p_ppc_object_version_number_7
1110 --
1111 ,p_component_reason_8 => p_component_reason_8
1112 ,p_change_amount_8 => p_change_amount_8
1113 ,p_change_percentage_8 => p_change_percentage_8
1114 ,p_component_id_8 => p_component_id_8
1115 ,p_approved_8 => p_approved_8
1116 ,p_object_version_number_8 => p_ppc_object_version_number_8
1117 --
1118 ,p_component_reason_9 => p_component_reason_9
1119 ,p_change_amount_9 => p_change_amount_9
1120 ,p_change_percentage_9 => p_change_percentage_9
1121 ,p_component_id_9 => p_component_id_9
1122 ,p_approved_9 => p_approved_9
1123 ,p_object_version_number_9 => p_ppc_object_version_number_9
1124 --
1125 ,p_component_reason_10 => p_component_reason_10
1126 ,p_change_amount_10 => p_change_amount_10
1127 ,p_change_percentage_10 => p_change_percentage_10
1128 ,p_component_id_10 => p_component_id_10
1129 ,p_approved_10 => p_approved_10
1130 ,p_object_version_number_10 => p_ppc_object_version_number_10
1131 --
1132 ,p_comp_reason_table => l_comp_reason_table
1133 ,p_change_amount_table => l_change_amount_table
1134 ,p_change_percentage_table => l_change_percentage_table
1135 ,p_comp_id_table => l_comp_id_table
1136 ,p_approved_table => l_approved_table
1137 ,p_ovn_table => l_ovn_table
1138 );
1139
1140 --
1141 -- The following parameter is used to verify whether any
1142 -- components is set for a salary_proposal
1143 --
1144 l_is_component_not_null := is_component_exist
1145 (
1146 l_change_amount_table
1147 ,l_change_percentage_table
1148 );
1149 hr_utility.set_location(l_proc, 10);
1150 --
1151 -- get the last salary proposal.
1152 --
1153 open csr_get_last_approved_salary;
1154 fetch csr_get_last_approved_salary into l_last_proposed_salary;
1155 if csr_get_last_approved_salary%notfound then
1156 hr_utility.set_location(l_proc, 15);
1157 --
1158 -- this means that, this is the first proposal, hence apply
1159 -- create proposal (i.e.first) or update proposal depending
1160 -- on the value of the l_pay_proposal_id
1161 --
1162 if (l_pay_proposal_id is null) then
1163 --
1164 --
1165 -- raise the component error if any of the components
1166 -- were set. Note: the first proposal has no components.
1167 --
1168 if (l_is_component_not_null) then
1169 hr_utility.set_location(l_proc, 20);
1170 close csr_get_last_approved_salary;
1171 hr_utility.set_message(801,'HR_51312_PPC_COMP_NOT_ALLOWED');
1172 hr_utility.raise_error;
1173 end if;
1174 --
1175 if (l_trunc_date IS NULL AND p_proposed_salary IS NULL AND
1176 p_proposal_reason IS NULL ) then
1177 --
1178 -- this means that no action need to be taken for this record
1179 --
1180 hr_utility.set_location(l_proc, 25);
1181 --
1182 else
1183 --
1184 -- p_next_sal_review_date is being set to null if it is
1185 -- defaulted to EOT
1186 if l_next_sal_review_date = hr_api.g_date then
1187 l_next_sal_review_date := null;
1188 end if;
1189 --
1190 -- This means that the record need to be inserted
1191 -- Note that the proposal gets approved automatically,
1192 -- because it is the first proposal.
1193 --
1194 hr_utility.set_location(l_proc, 30);
1195 l_multiple_components := 'N';
1196 --
1197 -- insert an unapproved single component salary proposal
1198 -- record in per_pay_proposal_table using the row_handler
1199 --
1200 --vkodedal 05-Oct-2007 ER to satisfy satutory requirement
1201 --Retain auto approve first proposal functionality if profile is null or set to Yes
1202 l_approved :='N';
1203 l_autoApprove:=fnd_profile.value('HR_AUTO_APPROVE_FIRST_PROPOSAL');
1204 if(l_autoApprove is null or l_autoApprove ='Y') then
1205 hr_utility.set_location(l_proc, 32);
1206 l_approved:='Y';
1207 end if;
1208 per_pyp_ins.ins
1209 (p_pay_proposal_id => l_pay_proposal_id
1210 ,p_assignment_id => p_assignment_id
1211 ,p_business_group_id => p_business_group_id
1212 ,p_change_date => l_change_date
1213 ,p_proposed_salary_n => p_proposed_salary
1214 ,p_proposal_reason => p_proposal_reason
1215 ,p_next_sal_review_date => l_next_sal_review_date -- Bug 1620922
1216 ,p_forced_ranking => p_forced_ranking
1217 ,p_date_to => l_date_to
1218 ,p_approved => l_approved
1219 ,p_multiple_components => l_multiple_components
1220 ,p_inv_next_sal_date_warning
1221 => l_inv_next_sal_date_warning
1222 ,p_object_version_number
1223 => l_pyp_object_version_number
1224 ,p_proposed_salary_warning
1225 => l_proposed_salary_warning
1226 ,p_approved_warning
1227 => l_approved_warning
1228 ,p_payroll_warning
1229 => l_payroll_warning
1230 ,p_attribute_category => p_attribute_category
1231 ,p_attribute1 => p_attribute1
1232 ,p_attribute2 => p_attribute2
1233 ,p_attribute3 => p_attribute3
1234 ,p_attribute4 => p_attribute4
1235 ,p_attribute5 => p_attribute5
1236 ,p_attribute6 => p_attribute6
1237 ,p_attribute7 => p_attribute7
1238 ,p_attribute8 => p_attribute8
1239 ,p_attribute9 => p_attribute9
1240 ,p_attribute10 => p_attribute10
1241 ,p_attribute11 => p_attribute11
1242 ,p_attribute12 => p_attribute12
1243 ,p_attribute13 => p_attribute13
1244 ,p_attribute14 => p_attribute14
1245 ,p_attribute15 => p_attribute15
1246 ,p_attribute16 => p_attribute16
1247 ,p_attribute17 => p_attribute17
1248 ,p_attribute18 => p_attribute18
1249 ,p_attribute19 => p_attribute19
1250 ,p_attribute20 => p_attribute20
1251 );
1252
1253 if (l_approved='Y') then
1254 --
1255 -- Now maintain element entries;
1256 --
1257 open csr_get_element_detail;
1258 fetch csr_get_element_detail into l_element_type_id,
1259 l_input_value_id;
1260 if csr_get_element_detail%notfound then
1261 hr_utility.set_location(l_proc,35);
1262 close csr_get_element_detail;
1263 hr_utility.set_message(801,'HR_289855_SAL_ASS_NOT_SAL_ELIG');
1264 hr_utility.raise_error;
1265 else
1266 close csr_get_element_detail;
1267 if (l_element_type_id IS NULL OR l_input_value_id IS NULL)
1268 then
1269 hr_utility.set_location(l_proc,40);
1270 hr_utility.set_message
1271 (801,'HR_289855_SAL_ASS_NOT_SAL_ELIG');
1272 hr_utility.raise_error;
1273 else
1274 l_element_link_id := hr_entry_api.get_link
1275 (p_assignment_id
1276 ,l_element_type_id
1277 ,l_trunc_date);
1278 if l_element_link_id IS NULL then
1279 hr_utility.set_message
1280 (801,'HR_13016_SAL_ELE_NOT_ELIG');
1281 hr_utility.raise_error;
1282 end if;
1283 --
1284 -- Now we insert an element entry for this proposal
1285 -- by calling the insert_element_entry_api.
1286 --
1287 hr_entry_api.insert_element_entry
1288 (p_effective_start_date => l_effective_start_date
1289 ,p_effective_end_date => l_effective_end_date
1290 ,p_element_entry_id => l_element_entry_id
1291 ,p_assignment_id => p_assignment_id
1292 ,p_element_link_id => l_element_link_id
1293 ,p_creator_type => 'SP'
1294 ,p_entry_type => 'E'
1295 ,p_creator_id => l_pay_proposal_id
1296 ,p_input_value_id1 => l_input_value_id
1297 ,p_entry_value1 => p_proposed_salary
1298 );
1299 --changes for Position Control check on Salary proposal
1300 pqh_psf_bus.chk_position_budget( p_assignment_id => p_assignment_id
1301 ,p_element_type_id => l_element_type_id
1302 ,p_input_value_id => l_input_value_id
1303 ,p_effective_date => p_change_date
1304 ,p_called_from => 'SAL');
1305 --End changes for position control rule on sal proposal
1306 --
1307 end if;
1308 --
1309 end if;
1310 end if;
1311 end if;
1312 --
1313 end if;
1314 --
1315 else -- This salary proposal is not the only one.
1316 --
1317 -- this is when the an unapproved proposal exists. Therefore
1318 -- the proposal is not the first one.
1319 --
1320 if (l_pay_proposal_id is NOT NULL) then
1321 --
1322 -- get the proposal details and check that the proposal has not
1323 -- been changed through the HRMS
1324 --
1325 open csr_get_proposal_detail;
1326 fetch csr_get_proposal_detail into l_change_date,l_proposed_salary_db,
1327 l_multiple_components,l_pyp_approved,l_proposal_reason,
1328 l_pyp_object_version_number;
1329 if csr_get_proposal_detail%notfound then
1330 hr_utility.set_location(l_proc,45);
1331 close csr_get_proposal_detail;
1332 hr_utility.set_message(801,'HR_51310_PPC_INVAL_PRO_ID');
1333 hr_utility.raise_error;
1334 else
1335 close csr_get_proposal_detail;
1336 --
1337 -- check that the object version number is the same (the record has
1338 -- not been changed since it has been out via the HRMS)
1339 --
1340 if (l_pyp_object_version_number <> p_object_version_number) then
1341 hr_utility.set_location(l_proc,50);
1342 hr_utility.set_message(801,'HR_51348_PYP_RECORD_CHG');
1343 hr_utility.raise_error;
1344 end if;
1345 --
1346 -- Check that the proposal is multiple or single component.
1347 -- if it is single_component and some components exists then
1348 -- raise error.
1349 --
1350 if (l_multiple_components = 'N') then
1351 if (l_is_component_not_null) then
1352 hr_utility.set_location(l_proc,55);
1353 hr_utility.set_message(801,'HR_51312_PPC_COMP_NOT_ALLOWED');
1354 hr_utility.raise_error;
1355 else
1356 --
1357 -- check that the change_date has not been updated
1358 -- This extra check is done here since the update api has
1359 -- no change_date parameter. And since the usermay change
1360 -- the change date we need to do this validation before
1361 -- calling the update routine
1362 --
1363 if (l_trunc_date <> l_change_date) then
1364 hr_utility.set_location(l_proc,60);
1365 hr_utility.set_message(801,'HR_51349_PYP_CNT_UPD_CHG_DATE');
1366 hr_utility.raise_error;
1367 end if;
1368 --
1369 --
1370 -- update the salary proposal
1371 --
1372 per_pyp_upd.upd
1373 (p_pay_proposal_id => l_pay_proposal_id
1374 ,p_proposal_reason => p_proposal_reason
1375 ,p_next_sal_review_date => l_next_sal_review_date -- Bug 1620922
1376 ,p_proposed_salary_n => p_proposed_salary
1377 ,p_forced_ranking => p_forced_ranking
1378 ,p_validate => false
1379 ,p_object_version_number => l_pyp_object_version_number
1380 ,p_inv_next_sal_date_warning
1381 => l_inv_next_sal_date_warning
1382 ,p_proposed_salary_warning => l_proposed_salary_warning
1383 ,p_approved_warning => l_approved_warning
1384 ,p_payroll_warning => l_payroll_warning
1385 ,p_attribute_category => p_attribute_category
1386 ,p_attribute1 => p_attribute1
1387 ,p_attribute2 => p_attribute2
1388 ,p_attribute3 => p_attribute3
1389 ,p_attribute4 => p_attribute4
1390 ,p_attribute5 => p_attribute5
1391 ,p_attribute6 => p_attribute6
1392 ,p_attribute7 => p_attribute7
1393 ,p_attribute8 => p_attribute8
1394 ,p_attribute9 => p_attribute9
1395 ,p_attribute10 => p_attribute10
1396 ,p_attribute11 => p_attribute11
1397 ,p_attribute12 => p_attribute12
1398 ,p_attribute13 => p_attribute13
1399 ,p_attribute14 => p_attribute14
1400 ,p_attribute15 => p_attribute15
1401 ,p_attribute16 => p_attribute16
1402 ,p_attribute17 => p_attribute17
1403 ,p_attribute18 => p_attribute18
1404 ,p_attribute19 => p_attribute19
1405 ,p_attribute20 => p_attribute20
1406 );
1407 --
1408 hr_utility.set_location(l_proc,65);
1409 --
1410 end if;
1411 else
1412 --
1413 -- l_multiple_components = 'Y'
1414 -- This is when the proposal has multiple components.
1415 -- We need to calculate the sum of the components in this case
1416 --
1417 hr_utility.set_location(l_proc,70);
1418 l_component_cal := TRUE;
1419 --
1420 end if;
1421 --
1422 end if; -- end of csr_get_proposal_detail notfound
1423 else
1424 -- l_pay_proposal is null
1425 --
1426 -- This is when the proposal is not the first proposal and it has not
1427 -- already existed( i.e. it need to be created).
1428 -- first of all we need to determine whether the proposal is of type
1429 -- multiple components or not. This is done by checking the components.
1430 --
1431 hr_utility.set_location(l_proc,75);
1432 if (l_is_component_not_null) then
1433 l_multiple_components := 'Y';
1434 l_component_cal := TRUE;
1435 hr_utility.set_location(l_proc,80);
1436 else l_multiple_components := 'N';
1437 hr_utility.set_location(l_proc,85);
1438 --
1439 end if;
1440 --
1441 if l_next_sal_review_date = hr_api.g_date then
1442 l_next_sal_review_date := null;
1443 end if;
1444
1445 -- insert an unapproved single component salary proposal record in
1446 -- per_pay_proposals table using the row_handler
1447 --
1448 end_date_salary_proposal(p_assignment_id => p_assignment_id
1449 ,p_date_to => p_change_date-1);
1450 ----------vkodedal 7-mar-07
1451 -- Update the last_change_date for the next proposal
1452 --
1453 update_last_change_date(p_assignment_id, p_change_date);
1454
1455 per_pyp_ins.ins
1456 (p_pay_proposal_id => l_pay_proposal_id
1457 ,p_assignment_id => p_assignment_id
1458 ,p_business_group_id => p_business_group_id
1459 ,p_change_date => l_change_date
1460 ,p_proposal_reason => p_proposal_reason
1461 ,p_next_sal_review_date => l_next_sal_review_date -- Bug 1620922
1462 ,p_proposed_salary_n => p_proposed_salary
1463 ,p_forced_ranking => p_forced_ranking
1464 ,p_date_to => l_date_to
1465 ,p_approved => 'N'
1466 ,p_multiple_components => l_multiple_components
1467 ,p_object_version_number => l_pyp_object_version_number
1468 ,p_validate => false
1469 ,p_inv_next_sal_date_warning
1470 => l_inv_next_sal_date_warning
1471 ,p_proposed_salary_warning
1472 => l_proposed_salary_warning
1473 ,p_approved_warning
1474 => l_approved_warning
1475 ,p_payroll_warning
1476 => l_payroll_warning
1477 --vkodedal 03-Sep-07 -fix for 6244195 -webadi issue -dff not getting uploaded
1478 ,p_attribute_category => p_attribute_category
1479 ,p_attribute1 => p_attribute1
1480 ,p_attribute2 => p_attribute2
1481 ,p_attribute3 => p_attribute3
1482 ,p_attribute4 => p_attribute4
1483 ,p_attribute5 => p_attribute5
1484 ,p_attribute6 => p_attribute6
1485 ,p_attribute7 => p_attribute7
1486 ,p_attribute8 => p_attribute8
1487 ,p_attribute9 => p_attribute9
1488 ,p_attribute10 => p_attribute10
1489 ,p_attribute11 => p_attribute11
1490 ,p_attribute12 => p_attribute12
1491 ,p_attribute13 => p_attribute13
1492 ,p_attribute14 => p_attribute14
1493 ,p_attribute15 => p_attribute15
1494 ,p_attribute16 => p_attribute16
1495 ,p_attribute17 => p_attribute17
1496 ,p_attribute18 => p_attribute18
1497 ,p_attribute19 => p_attribute19
1498 ,p_attribute20 => p_attribute20
1499 );
1500 --
1501 hr_utility.set_location(l_proc,90);
1502 --
1503 end if; -- l_pay_proposal_id is not null
1504 --
1505 end if; -- csr_get_last_approved_salary not found
1506 --
1507 if (csr_get_last_approved_salary%isopen) then
1508 close csr_get_last_approved_salary;
1509 hr_utility.set_location(l_proc,95);
1510 end if;
1511 --
1512 if (csr_get_proposal_detail%isopen) then
1513 close csr_get_proposal_detail;
1514 hr_utility.set_location(l_proc,100);
1515 end if;
1516 --
1517 -- Now loop round the components and insert the components followed by
1518 -- updating the appropriate proposal
1519 --
1520 if(l_component_cal = true and l_last_proposed_salary IS NOT NULL) then
1521 hr_utility.set_location(l_proc,105);
1522 for i in 1 .. MAX_COMP_NO
1523 loop
1524 --
1525 -- set the value of the local parameters to be the appropriate
1526 -- component value.
1527 --
1528 l_comp_update := false;
1529 l_change_amount_in := get_value(l_change_amount_table,i);
1530 l_change_percentage_in := get_value(l_change_percentage_table,i);
1531 l_component_reason_in := get_value(l_comp_reason_table, i);
1532 l_component_id_in := get_value(l_comp_id_table, i);
1533 l_approved_in := get_value(l_approved_table,i);
1534 l_object_version_number_in := get_value(l_ovn_table,i);
1535
1536
1537 if (l_component_id_in IS NOT NULL ) then
1538 hr_utility.set_location(l_proc,110);
1539 open csr_get_component_detail (l_component_id_in);
1540 fetch csr_get_component_detail into l_component_reason, l_change_amount,
1541 l_change_percentage,l_ppc_approved, l_ppc_object_version_number;
1542 if csr_get_component_detail%notfound then
1543 close csr_get_component_detail;
1544 hr_utility.set_location(l_proc,115);
1545 hr_utility.set_message(801,'HR_51319_PPC_INVAL_COMP_ID');
1546 hr_utility.raise_error;
1547 else
1548 close csr_get_component_detail;
1549 hr_utility.set_location(l_proc,120);
1550 --
1551 -- check that the record has not been changed since last time.
1552 --
1553 if (l_ppc_object_version_number <> l_object_version_number_in) then
1554 hr_utility.set_location(l_proc,125);
1555 hr_utility.set_message(801,'HR_51455_PPC_RECORD_CHANGED');
1556 hr_utility.raise_error;
1557 end if;
1558 --
1559 -- Do nothing if the component has not changed at all.
1560 --
1561 if (l_component_reason = l_component_reason_in AND
1562 nvl(l_change_amount,hr_api.g_number)
1563 = nvl(l_change_amount_in,hr_api.g_number) AND
1564 nvl(l_change_percentage,hr_api.g_number)
1565 = nvl(l_change_percentage_in,hr_api.g_number) AND
1566 l_ppc_approved = l_approved_in ) then
1567 --
1568 -- this means that the component has not changed
1569 -- we just adding the amount.
1570 --
1571 hr_utility.set_location(l_proc,130);
1572 l_component_sum
1573 := l_component_sum + l_change_amount;
1574 --
1575 elsif (l_component_reason = l_component_reason_in AND
1576 nvl(l_change_amount,hr_api.g_number)
1577 = nvl(l_change_amount_in,hr_api.g_number) AND
1578 nvl(l_change_percentage,hr_api.g_number)
1579 = nvl(l_change_percentage_in,hr_api.g_number) AND
1580 l_ppc_approved <> l_approved_in ) then
1581 --
1582 -- this means that the component approval status has changed.
1583 -- We need to update the record.Set the update flag to true.
1584 --
1585 --
1586 l_component_sum
1587 := l_component_sum + l_change_amount;
1588 l_comp_update := true;
1589 --
1590 hr_utility.set_location(l_proc,135);
1591 --
1592 -- issue an error if updating an approved component
1593 --
1594 elsif (l_ppc_approved = 'Y' AND
1595 (l_component_reason <> l_component_reason_in OR
1596 nvl(l_change_amount,hr_api.g_number)
1597 <> nvl(l_change_amount_in,hr_api.g_number) OR
1598 nvl(l_change_percentage,hr_api.g_number)
1599 <> nvl(l_change_percentage_in,hr_api.g_number))) then
1600 hr_utility.set_location(l_proc,140);
1601 hr_utility.set_message(801,'HR_51454_PPC_CANT_UPD_COMP');
1602 hr_utility.raise_error;
1603 elsif (l_ppc_approved = 'N') then
1604 --
1605 -- This is when the component is not already approved and some
1606 -- changes has taken place, hence we need to update or delete
1607 -- the record as appropriate.
1608 --
1609 if (l_change_amount_in IS NULL) then
1610 if(l_change_percentage_in IS NOT NULL AND
1611 l_change_percentage_in <> 0) then
1612 --
1613 -- calculate the component_sum from the change_percentage
1614 -- and update the record.
1615 --
1616 hr_utility.set_location(l_proc,145);
1617 l_component_sum := l_component_sum +
1618 l_last_proposed_salary
1619 * l_change_percentage_in /100;
1620 --
1621 -- Set the l_comp_update and l_prop_update to true.
1622 -- update the record and set the approved flag to Y
1623 --
1624 l_prop_update := true;
1625 l_comp_update := true;
1626 hr_utility.set_location(l_proc,150);
1627 else
1628 --
1629 -- the change_amount and the change percentage are
1630 -- both null, this means that the component need to
1631 -- be deleted
1632 --
1633 per_ppc_del.del
1634 (p_component_id => l_component_id_in
1635 ,p_object_version_number => l_object_version_number_in
1636 ,p_validate => false
1637 );
1638 --
1639 -- subtract the previous change_amount from the component_sum
1640 --
1641 l_component_sum
1642 := l_component_sum - l_change_amount;
1643
1644 l_prop_update := true;
1645 --
1646 hr_utility.set_location(l_proc,155);
1647 --
1648 end if; -- l_change_percentage IS NOT NULL
1649 else
1650 --
1651 -- this is when the change_amount is not null
1652 -- first sum up the component and then update the component
1653 --
1654 l_component_sum
1655 := l_component_sum + l_change_amount_in;
1656 --
1657 -- Set the comp_update and prop_update to true
1658 --
1659 l_comp_update := true;
1660 l_prop_update := true;
1661 hr_utility.set_location(l_proc,160);
1662 end if; -- l_change_amount_is not null
1663 --
1664 end if;
1665 --
1666 -- Now update the component if the l_comp_update is true
1667 --
1668 if (l_comp_update) then
1669 per_ppc_upd.upd
1670 (p_component_id => l_component_id_in
1671 ,p_component_reason => l_component_reason_in
1672 ,p_change_amount_n => l_change_amount_in
1673 ,p_change_percentage => l_change_percentage_in
1674 ,p_approved => l_approved_in
1675 ,p_object_version_number => l_object_version_number_in
1676 ,p_validate => false
1677 );
1678 hr_utility.set_location(l_proc, 165);
1679 end if;
1680 --
1681 end if; -- csr_get_component_details
1682 --
1683 else /* l_component_id_in IS NOT NULL*/
1684 --
1685 -- component_id is null. Therefore, we are inserting a new
1686 -- record in the db.
1687 --
1688 if (l_change_amount_in IS NOT NULL OR
1689 (l_change_percentage_in IS NOT NULL AND
1690 l_change_percentage_in <> 0)) then
1691
1692 l_comp_update := true;
1693 l_prop_update := true;
1694 if (l_change_amount_in IS NOT NULL ) then
1695 l_component_sum := l_component_sum +
1696 l_change_amount_in;
1697 hr_utility.set_location(l_proc, 170);
1698 else
1699 if (l_change_percentage_in IS NOT NULL) then
1700 l_component_sum := l_component_sum +
1701 l_last_proposed_salary *
1702 l_change_percentage_in /100;
1703 hr_utility.set_location(l_proc, 175);
1704 end if;
1705 --
1706 end if;
1707 --
1708 per_ppc_ins.ins
1709 (p_component_id => l_component_id_in
1710 ,p_pay_proposal_id => l_pay_proposal_id
1711 ,p_business_group_id => p_business_group_id
1712 ,p_component_reason => l_component_reason_in
1713 ,p_change_amount_n => l_change_amount_in
1714 ,p_change_percentage => l_change_percentage_in
1715 ,p_approved => l_approved_in
1716 ,p_object_version_number => l_object_version_number_in
1717 ,p_validate => false
1718 );
1719 hr_utility.set_location(l_proc, 180);
1720 --
1721 end if;
1722 end if; -- the l_component_id is null
1723 --
1724 -- Set the output parameters
1725 --
1726 if (i = 1) then
1727 l_component_id_1 := l_component_id_in;
1728 l_ppc_ovn_1 := l_object_version_number_in;
1729 elsif (i= 2) then
1730 l_component_id_2 := l_component_id_in;
1731 l_ppc_ovn_2 := l_object_version_number_in;
1732 elsif (i= 3) then
1733 l_component_id_3 := l_component_id_in;
1734 l_ppc_ovn_3 := l_object_version_number_in;
1735 elsif (i= 4) then
1736 l_component_id_4 := l_component_id_in;
1737 l_ppc_ovn_4 := l_object_version_number_in;
1738 elsif (i= 5) then
1739 l_component_id_5 := l_component_id_in;
1740 l_ppc_ovn_5 := l_object_version_number_in;
1741 elsif (i= 6) then
1742 l_component_id_6 := l_component_id_in;
1743 l_ppc_ovn_6 := l_object_version_number_in;
1744 elsif (i= 7) then
1745 l_component_id_7 := l_component_id_in;
1746 l_ppc_ovn_7 := l_object_version_number_in;
1747 elsif (i= 8) then
1748 l_component_id_8 := l_component_id_in;
1749 l_ppc_ovn_8 := l_object_version_number_in;
1750 elsif (i= 9) then
1751 l_component_id_9 := l_component_id_in;
1752 l_ppc_ovn_9 := l_object_version_number_in;
1753 elsif (i= 10) then
1754 l_component_id_10 := l_component_id_in;
1755 l_ppc_ovn_10 := l_object_version_number_in;
1756 --
1757 end if;
1758 --
1759 end LOOP;
1760 --
1761 -- Now we need to update the pay_proposal table
1762 -- We need to double check that there is no other components exists other
1763 -- than those ten which we processed above. This is done by getting
1764 -- the sum of all the existing components.
1765 --
1766 --
1767 open csr_sum_of_components (l_pay_proposal_id);
1768 fetch csr_sum_of_components into l_total_component_sum;
1769 if csr_sum_of_components%found then
1770 close csr_sum_of_components;
1771 --
1772 -- check that the component sum from the above calculation
1773 -- is the same as the total_component_sum from the cursor.
1774 -- If they are not the same then set the inofrmational parameter
1775 -- to TRUE.
1776 --
1777 if (l_total_component_sum <> l_component_sum) then
1778 p_additional_comp_warning := TRUE;
1779 else
1780 p_additional_comp_warning := FALSE;
1781 end if;
1782 --
1783 l_proposed_salary := l_total_component_sum + l_last_proposed_salary;
1784 end if;
1785 --
1786 -- update the salary proposal if the l_prop_update is true;
1787 --
1788 if (l_prop_update) then
1789 per_pyp_upd.upd
1790 (p_pay_proposal_id => l_pay_proposal_id
1791 ,p_proposal_reason => p_proposal_reason
1792 ,p_next_sal_review_date => l_next_sal_review_date -- Bug 1620922
1793 ,p_proposed_salary_n => l_proposed_salary
1794 ,p_forced_ranking => p_forced_ranking
1795 ,p_object_version_number => l_pyp_object_version_number
1796 ,p_inv_next_sal_date_warning => l_inv_next_sal_date_warning
1797 ,p_proposed_salary_warning => l_proposed_salary_warning
1798 ,p_approved_warning => l_approved_warning
1799 ,p_payroll_warning => l_payroll_warning
1800 ,p_validate => false
1801 );
1802 --
1803 hr_utility.set_location(l_proc, 185);
1804 end if;
1805 end if; -- L_component_cal
1806 --
1807 -- Call After Process User Hook upload_salary_proposal
1808 --
1809 begin
1810 hr_upload_proposal_bk1.upload_salary_proposal_a
1811 (
1812 p_change_date => l_change_date
1813 ,p_business_group_id => p_business_group_id
1814 ,p_assignment_id => p_assignment_id
1815 ,p_proposed_salary => p_proposed_salary
1816 ,p_proposal_reason => p_proposal_reason
1817 ,p_next_sal_review_date => l_next_sal_review_date -- Bug 1620922
1818 ,p_forced_ranking => p_forced_ranking
1819 ,p_date_to => l_date_to
1820 ,p_pay_proposal_id => l_pay_proposal_id
1821 ,p_object_version_number => l_pyp_object_version_number
1822 --
1823 ,p_component_reason_1 => p_component_reason_1
1824 ,p_change_amount_1 => p_change_amount_1
1825 ,p_change_percentage_1 => p_change_percentage_1
1826 ,p_approved_1 => p_approved_1
1827 ,p_component_id_1 => l_component_id_1
1828 ,p_ppc_object_version_number_1 => l_ppc_ovn_1
1829 --
1830 ,p_component_reason_2 => p_component_reason_2
1831 ,p_change_amount_2 => p_change_amount_2
1832 ,p_change_percentage_2 => p_change_percentage_2
1833 ,p_approved_2 => p_approved_2
1834 ,p_component_id_2 => l_component_id_2
1835 ,p_ppc_object_version_number_2 => l_ppc_ovn_2
1836 --
1837 ,p_component_reason_3 => p_component_reason_3
1838 ,p_change_amount_3 => p_change_amount_3
1839 ,p_change_percentage_3 => p_change_percentage_3
1840 ,p_approved_3 => p_approved_3
1841 ,p_component_id_3 => l_component_id_3
1842 ,p_ppc_object_version_number_3 => l_ppc_ovn_3
1843 --
1844 ,p_component_reason_4 => p_component_reason_4
1845 ,p_change_amount_4 => p_change_amount_4
1846 ,p_change_percentage_4 => p_change_percentage_4
1847 ,p_approved_4 => p_approved_4
1848 ,p_component_id_4 => l_component_id_4
1849 ,p_ppc_object_version_number_4 => l_ppc_ovn_4
1850 --
1851 ,p_component_reason_5 => p_component_reason_5
1852 ,p_change_amount_5 => p_change_amount_5
1853 ,p_change_percentage_5 => p_change_percentage_5
1854 ,p_approved_5 => p_approved_5
1855 ,p_component_id_5 => l_component_id_5
1856 ,p_ppc_object_version_number_5 => l_ppc_ovn_5
1857 --
1858 ,p_component_reason_6 => p_component_reason_6
1859 ,p_change_amount_6 => p_change_amount_6
1860 ,p_change_percentage_6 => p_change_percentage_6
1861 ,p_approved_6 => p_approved_6
1862 ,p_component_id_6 => l_component_id_6
1863 ,p_ppc_object_version_number_6 => l_ppc_ovn_6
1864 --
1865 ,p_component_reason_7 => p_component_reason_7
1866 ,p_change_amount_7 => p_change_amount_7
1867 ,p_change_percentage_7 => p_change_percentage_7
1868 ,p_approved_7 => p_approved_7
1869 ,p_component_id_7 => l_component_id_7
1870 ,p_ppc_object_version_number_7 => l_ppc_ovn_7
1871 --
1872 ,p_component_reason_8 => p_component_reason_8
1873 ,p_change_amount_8 => p_change_amount_8
1874 ,p_change_percentage_8 => p_change_percentage_8
1875 ,p_approved_8 => p_approved_8
1876 ,p_component_id_8 => l_component_id_8
1877 ,p_ppc_object_version_number_8 => l_ppc_ovn_8
1878 --
1879 ,p_component_reason_9 => p_component_reason_9
1880 ,p_change_amount_9 => p_change_amount_9
1881 ,p_change_percentage_9 => p_change_percentage_9
1882 ,p_approved_9 => p_approved_9
1883 ,p_component_id_9 => l_component_id_9
1884 ,p_ppc_object_version_number_9 => l_ppc_ovn_9
1885 --
1886 ,p_component_reason_10 => p_component_reason_10
1887 ,p_change_amount_10 => p_change_amount_10
1888 ,p_change_percentage_10 => p_change_percentage_10
1889 ,p_approved_10 => p_approved_10
1890 ,p_component_id_10 => l_component_id_10
1891 ,p_ppc_object_version_number_10 => l_ppc_ovn_10
1892 ,p_pyp_proposed_sal_warning => l_pyp_proposed_sal_warning
1893 ,p_additional_comp_warning => l_additional_comp_warning
1894 );
1895 exception
1896 when hr_api.cannot_find_prog_unit then
1897 hr_api.cannot_find_prog_unit_error
1898 (p_module_name => 'UPLOAD_SALARY_PROPOSAL'
1899 ,p_hook_type => 'AP'
1900 );
1901 --
1902 -- End of API User Hook for the after hook of upload_salary_proposal
1903 --
1904 end;
1905 --
1906 -- when in validation only mode raise the Validate_Enabled exception
1907 --
1908 if p_validate then
1909 raise hr_api.validate_enabled;
1910 end if;
1911 --
1912 -- Set output parameters
1913 --
1914 p_pay_proposal_id := l_pay_proposal_id;
1915 p_object_version_number := l_pyp_object_version_number;
1916 p_pyp_proposed_sal_warning := l_proposed_salary_warning;
1917 p_additional_comp_warning := l_additional_comp_warning;
1918 p_component_id_1 := l_component_id_1;
1919 p_ppc_object_version_number_1 := l_ppc_ovn_1;
1920 p_component_id_2 := l_component_id_2;
1921 p_ppc_object_version_number_2 := l_ppc_ovn_2;
1922 p_component_id_3 := l_component_id_3;
1923 p_ppc_object_version_number_3 := l_ppc_ovn_3;
1924 p_component_id_4 := l_component_id_4;
1925 p_ppc_object_version_number_4 := l_ppc_ovn_4;
1926 p_component_id_5 := l_component_id_5;
1927 p_ppc_object_version_number_5 := l_ppc_ovn_5;
1928 p_component_id_6 := l_component_id_6;
1929 p_ppc_object_version_number_6 := l_ppc_ovn_6;
1930 p_component_id_7 := l_component_id_7;
1931 p_ppc_object_version_number_7 := l_ppc_ovn_7;
1932 p_component_id_8 := l_component_id_8;
1933 p_ppc_object_version_number_8 := l_ppc_ovn_8;
1934 p_component_id_9 := l_component_id_9;
1935 p_ppc_object_version_number_9 := l_ppc_ovn_9;
1936 p_component_id_10 := l_component_id_10;
1937 p_ppc_object_version_number_10:= l_ppc_ovn_10;
1938
1939 --
1940 hr_utility.set_location(' Leaving:'||l_proc, 190);
1941 exception
1942 when hr_api.validate_enabled then
1943 --
1944 -- As the Validate_Enabled exception has been raised
1945 -- we must rollback to the savepoint
1946 --
1947 ROLLBACK TO upload_salary_proposal;
1948 --
1949 -- Only set output warning arguments
1950 -- (Any key or derived arguments must be set to its initial
1951 -- value when validation only mode is being used.)
1952 --
1953 p_pyp_proposed_sal_warning := l_proposed_salary_warning;
1954 p_additional_comp_warning := l_additional_comp_warning;
1955 p_pay_proposal_id := lt_pay_proposal_id;
1956 p_object_version_number := lt_object_version_number;
1957
1958 p_component_id_1 := lt_component_id_1;
1959 p_ppc_object_version_number_1 := lt_ppc_ovn_1;
1960 p_component_id_2 := lt_component_id_2;
1961 p_ppc_object_version_number_2 := lt_ppc_ovn_2;
1962 p_component_id_3 := lt_component_id_3;
1963 p_ppc_object_version_number_3 := lt_ppc_ovn_3;
1964 p_component_id_4 := lt_component_id_4;
1965 p_ppc_object_version_number_4 := lt_ppc_ovn_4;
1966 p_component_id_5 := lt_component_id_5;
1967 p_ppc_object_version_number_5 := lt_ppc_ovn_5;
1968 p_component_id_6 := lt_component_id_6;
1969 p_ppc_object_version_number_6 := lt_ppc_ovn_6;
1970 p_component_id_7 := lt_component_id_7;
1971 p_ppc_object_version_number_7 := lt_ppc_ovn_7;
1972 p_component_id_8 := lt_component_id_8;
1973 p_ppc_object_version_number_8 := lt_ppc_ovn_8;
1974 p_component_id_9 := lt_component_id_9;
1975 p_ppc_object_version_number_9 := lt_ppc_ovn_9;
1976 p_component_id_10 := lt_component_id_10;
1977 p_ppc_object_version_number_10:= lt_ppc_ovn_10;
1978 --
1979 hr_utility.set_location(' Leaving:'||l_proc, 195);
1980 --
1981 when others then
1982 --
1983 -- A validation or unexpected error has occurred
1984 --
1985 -- Added as part part of fix to bug 632474
1986 --
1987 ROLLBACK TO upload_salary_proposal;
1988 --
1989 -- Reset IN OUT and set OUT parameters.
1990 p_pay_proposal_id := lt_pay_proposal_id;
1991 p_object_version_number := lt_object_version_number;
1992 p_pyp_proposed_sal_warning := null;
1993 p_additional_comp_warning := null;
1994 p_component_id_1 := lt_component_id_1;
1995 p_ppc_object_version_number_1 := lt_ppc_ovn_1;
1996 p_component_id_2 := lt_component_id_2;
1997 p_ppc_object_version_number_2 := lt_ppc_ovn_2;
1998 p_component_id_3 := lt_component_id_3;
1999 p_ppc_object_version_number_3 := lt_ppc_ovn_3;
2000 p_component_id_4 := lt_component_id_4;
2001 p_ppc_object_version_number_4 := lt_ppc_ovn_4;
2002 p_component_id_5 := lt_component_id_5;
2003 p_ppc_object_version_number_5 := lt_ppc_ovn_5;
2004 p_component_id_6 := lt_component_id_6;
2005 p_ppc_object_version_number_6 := lt_ppc_ovn_6;
2006 p_component_id_7 := lt_component_id_7;
2007 p_ppc_object_version_number_7 := lt_ppc_ovn_7;
2008 p_component_id_8 := lt_component_id_8;
2009 p_ppc_object_version_number_8 := lt_ppc_ovn_8;
2010 p_component_id_9 := lt_component_id_9;
2011 p_ppc_object_version_number_9 := lt_ppc_ovn_9;
2012 p_component_id_10 := lt_component_id_10;
2013 p_ppc_object_version_number_10:= lt_ppc_ovn_10;
2014 raise;
2015 --
2016 -- End of fix.
2017 --
2018 end upload_salary_proposal;
2019 --
2020 end hr_upload_proposal_api;