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