[Home] [Help]
PACKAGE BODY: APPS.PQP_GB_PSI_SALARY_HISTORY
Source
1 PACKAGE BODY PQP_GB_PSI_SALARY_HISTORY AS
2 -- /* $Header: pqpgbpssal.pkb 120.0.12000000.6 2007/06/27 15:52:13 rlingama noship $ */
3 --
4 --
5 --
6 --
7 --
8 -- Exceptions
9 hr_application_error exception;
10 pragma exception_init (hr_application_error, -20001);
11
12 g_nested_level NUMBER(5) := pqp_utilities.g_nested_level;
13 -- ----------------------------------------------------------------------------
14 -- |--------------------------------< debug >---------------------------------|
15 -- ----------------------------------------------------------------------------
16
17 PROCEDURE DEBUG (p_trace_message IN VARCHAR2
18 , p_trace_location IN NUMBER DEFAULT NULL)
19 IS
20
21 --
22 BEGIN
23 --
24 IF g_debug THEN
25 pqp_utilities.DEBUG (
26 p_trace_message => p_trace_message
27 ,p_trace_location => p_trace_location
28 );
29 END IF;
30 --
31 END DEBUG;
32
33
34 -- This procedure is used for debug purposes
35 -- debug_enter checks the debug flag and sets the trace on/off
36 --
37 -- ----------------------------------------------------------------------------
38 -- |----------------------------< debug_enter >-------------------------------|
39 -- ----------------------------------------------------------------------------
40
41 PROCEDURE debug_enter (p_proc_name IN VARCHAR2, p_trace_on IN VARCHAR2)
42 IS
43 BEGIN
44 --
45 IF g_debug THEN
46 IF pqp_utilities.g_nested_level = 0 THEN
47 hr_utility.trace_on(NULL, 'REQID'); -- Pipe name REQIDnnnnn
48 END IF;
49 pqp_utilities.debug_enter (
50 p_proc_name => p_proc_name
51 ,p_trace_on => p_trace_on
52 );
53 END IF;
54 --
55
56 END debug_enter;
57
58
59 -- This procedure is used for debug purposes
60 --
61 -- ----------------------------------------------------------------------------
62 -- |----------------------------< debug_exit >--------------------------------|
63 -- ----------------------------------------------------------------------------
64
65 PROCEDURE debug_exit (p_proc_name IN VARCHAR2, p_trace_off IN VARCHAR2)
66 IS
67 BEGIN
68 --
69 IF g_debug THEN
70 pqp_utilities.debug_exit (
71 p_proc_name => p_proc_name
72 ,p_trace_off => p_trace_off
73 );
74
75 IF pqp_utilities.g_nested_level = 0 THEN
76 hr_utility.trace_off;
77 END IF;
78 END IF;
79 --
80 END debug_exit;
81
82
83 -- This procedure is used for debug purposes
84 --
85 -- ----------------------------------------------------------------------------
86 -- |----------------------------< debug_others >------------------------------|
87 -- ----------------------------------------------------------------------------
88
89 PROCEDURE debug_others (p_proc_name IN VARCHAR2, p_proc_step IN NUMBER)
90 IS
91 BEGIN
92 --
93 pqp_utilities.debug_others (
94 p_proc_name => p_proc_name
95 ,p_proc_step => p_proc_step
96 );
97 --
98 END debug_others;
99 -- ----------------------------------------------------------------------------
100 -- |--------------------< reset_salary_history_globals >----------------------|
101 -- ----------------------------------------------------------------------------
102 PROCEDURE reset_salary_history_globals
103 IS
104 l_proc varchar2(72) := g_package||'.set_salary_history_globals';
105 BEGIN
106 debug_enter(l_proc);
107
108 debug_exit(l_proc);
109 END reset_salary_history_globals;
110 ---
111 -- ----------------------------------------------------------------------------
112 -- |------------------------< set_salary_rate_name >--------------------------|
113 -- ----------------------------------------------------------------------------
114 PROCEDURE set_salary_rate_name
115 IS
116 c_seeded_basic_sal_rate_name CONSTANT VARCHAR2(80) := 'PenServer Pensionable Salary';
117 c_seeded_basic_sal_rate_code CONSTANT VARCHAR2(80) := 'PEN_SALARY';
118 l_basic_sal_rate_code VARCHAR2(80);
119 l_config_values PQP_UTILITIES.t_config_values;
120 l_proc varchar2(72) := g_package||'.set_salary_rate_name';
121 l_itr NUMBER;
122 BEGIN
123 debug_enter(l_proc);
124 --reset salary rate name
125 g_basic_sal_rate_name := NULL;
126
127 pqp_gb_psi_functions.get_rate_usr_func_name
128 (
129 p_business_group_id => g_business_group_id
130 ,p_legislation_code => g_legislation_code
131 ,p_interface_name => 'SALARY'
132 ,p_rate_name => g_basic_sal_rate_name
133 ,p_rate_code => l_basic_sal_rate_code
134 ,p_usr_rate_function => g_user_rate_function
135 ,p_sal_ele_fte_attr => g_sal_ele_fte_attr
136 );
137
138 IF l_basic_sal_rate_code IS NULL THEN
139 -- if there is no configuration provided for the basic salary rate, use the seeded rate type
140 g_basic_sal_rate_name := c_seeded_basic_sal_rate_name;
141 l_basic_sal_rate_code := c_seeded_basic_sal_rate_code;
142 END IF;
143
144 debug('g_basic_sal_rate_name: '||g_basic_sal_rate_name);
145 debug('l_basic_sal_rate_code: '||l_basic_sal_rate_code);
146 debug('g_user_rate_function: '||g_user_rate_function);
147 debug_exit(l_proc);
148 END set_salary_rate_name;
149 ---
150 -- ----------------------------------------------------------------------------
151 -- |-------------------< set_unigrade_config_values >-------------------------|
152 -- ----------------------------------------------------------------------------
153 PROCEDURE set_unigrade_config_values
154 IS
155 l_proc varchar2(72) := g_package||'.set_unigrade_config_values';
156 l_config_values PQP_UTILITIES.t_config_values;
157 l_error NUMBER;
158 BEGIN
159 debug_enter(l_proc);
160 PQP_UTILITIES.get_config_type_values(
161 p_configuration_type => 'PQP_GB_PENSERVER_UNIGRD_MAP'
162 ,p_business_group_id => g_business_group_id
163 ,p_legislation_code => g_legislation_code
164 ,p_tab_config_values => l_config_values
165 );
166 IF l_config_values.COUNT > 0 THEN
167 g_unigrade_source := l_config_values(l_config_values.FIRST).pcv_information1;
168 g_assignment_context := l_config_values(l_config_values.FIRST).pcv_information2;
169 g_assignment_column := l_config_values(l_config_values.FIRST).pcv_information3;
170 g_people_group_column := l_config_values(l_config_values.FIRST).pcv_information4;
171
172 debug('g_unigrade_source: '||g_unigrade_source,20);
173 debug('g_assignment_context: '||g_assignment_context,20);
174 debug('g_assignment_column: '||g_assignment_column,20);
175 debug('g_people_group_column: '||g_people_group_column,20);
176
177 IF g_unigrade_source = 'PEOPLE_GROUP' THEN
178 -- override to uniformed grade flag can be in People Group Flexfield
179 debug('override to uniformed grade flag can be in People Group Flexfield',30);
180
181 IF g_people_group_column IS NULL THEN
182 debug('Error: People Group Column is null in the Unigrade Config Value');
183 PQP_GB_PSI_FUNCTIONS.store_extract_exceptions
184 (p_extract_type => 'SALARY'
185 ,p_error_number => 94444
186 ,p_error_text => 'BEN_94444_NO_PEOPLE_GRP_COLUMN'
187 ,p_error_warning_flag => 'E'
188 );
189 END IF; --IF g_people_group_column IS NULL
190
191
192 ELSE --IF l_source = 'PEOPLE_GROUP'
193 -- override to uniformed grade flag can be in Assignment Flexfield
194 debug('override to uniformed grade flag can be in Assignment Flexfield',20);
195 IF g_assignment_context IS NULL THEN
196
197 debug('Error: Assignment Column is null in the Unigrade Config Value',30);
198 PQP_GB_PSI_FUNCTIONS.store_extract_exceptions
199 (p_extract_type => 'SALARY'
200 ,p_error_number => 94446
201 ,p_error_text => 'BEN_94446_NO_ASSG_CONTEXT'
202 ,p_error_warning_flag => 'E'
203 );
204 END IF; --IF g_assignment_column IS NOT NULL
205 IF g_assignment_column IS NULL THEN
206
207 debug('Error: Assignment Column is null in the Unigrade Config Value',30);
208 PQP_GB_PSI_FUNCTIONS.store_extract_exceptions
209 (p_extract_type => 'SALARY'
210 ,p_error_number => 94445
211 ,p_error_text => 'BEN_94445_NO_ASSIGNMENT_COLUMN'
212 ,p_error_warning_flag => 'E'
213 );
214 END IF; --IF g_assignment_column IS NOT NULL
215 END IF; --IF l_source = 'PEOPLE_GROUP'
216 ELSE
217 debug('No Configuration for Uniformed Grade Override',20);
218 PQP_GB_PSI_FUNCTIONS.store_extract_exceptions
219 (p_extract_type => 'SALARY'
220 ,p_error_number => 94440
221 ,p_error_text => 'BEN_94440_NO_UNIGRD_CONFIG'
222 ,p_error_warning_flag => 'E'
223 );
224 END IF; --IF l_config_information_values.COUNT >0
225 debug_exit(l_proc);
226 END set_unigrade_config_values;
227 ---
228 -- ----------------------------------------------------------------------------
229 -- |--------------------< set_salary_history_globals >------------------------|
230 -- ----------------------------------------------------------------------------
231 PROCEDURE set_salary_history_globals
232 (
233 p_business_group_id IN NUMBER
234 ,p_assignment_id IN NUMBER
235 ,p_effective_date IN DATE
236 )
237 IS
238 l_proc varchar2(72) := g_package||'.set_salary_history_globals';
239
240 BEGIN --set_salary_history_globals
241 debug_enter(l_proc);
242 -- set global business group id
243 g_business_group_id := p_business_group_id;
244 debug('g_business_group_id: '||g_business_group_id,10);
245
246 set_salary_rate_name();
247
248 set_unigrade_config_values();
249
250 debug_exit(l_proc);
251 EXCEPTION
252 WHEN others THEN
253 IF SQLCODE <> hr_utility.hr_error_number
254 THEN
255 debug_others (l_proc, 10);
256 IF g_debug
257 THEN
258 DEBUG ( 'Leaving: '
259 || l_proc, -999);
260 END IF;
261 fnd_message.raise_error;
262 ELSE
263 RAISE;
264 END IF;
265 END set_salary_history_globals;
266 ---
267 -- ----------------------------------------------------------------------------
268 -- |-----------------------< set_assignment_globals >--------------------------|
269 -- ----------------------------------------------------------------------------
270 PROCEDURE set_assignment_globals
271 (
272 p_assignment_id IN NUMBER
273 ,p_effective_date IN DATE
274 )
275 IS
276 l_proc varchar2(72) := g_package||'.set_assignment_globals';
277 BEGIN -- set_assignment_globals
278 debug_enter(l_proc);
279 debug('Inputs are: ',10);
280 debug('p_assignment_id: '||p_assignment_id,10);
281 debug('p_effective_date: '||to_char(p_effective_date,'dd/mm/yyyy'),10);
282
283 PQP_GB_PSI_FUNCTIONS.init_st_end_date_glob();
284 -- reset assignment level globals
285 g_salary_start_date := NULL;
286 g_salary_end_date := NULL;
287
288 -- set global assignment_id
289 g_assignment_id := p_assignment_id;
290 debug('g_assignment_id: '||g_assignment_id,10);
291
292 -- set the global events table
293 g_pay_proc_evt_tab := ben_ext_person.g_pay_proc_evt_tab;
294 -- set global person id
295 g_person_id := PQP_GB_PSI_FUNCTIONS.get_current_extract_person
296 (
297 p_assignment_id => p_assignment_id
298 );
299 debug('g_person_id: '||g_person_id,10);
300
301 g_grade_chg_date := hr_api.g_eot;
302
303 debug_exit(l_proc);
304 EXCEPTION
305 WHEN others THEN
306 IF SQLCODE <> hr_utility.hr_error_number
307 THEN
308 debug_others (l_proc, 10);
309 IF g_debug
310 THEN
311 DEBUG ( 'Leaving: '
312 || l_proc, -999);
313 END IF;
314 fnd_message.raise_error;
315 ELSE
316 RAISE;
317 END IF;
318 END set_assignment_globals;
319 ---
320 -- ----------------------------------------------------------------------------
321 -- |------------------< salary_cutover_ext_criteria >---------------------|
322 -- ----------------------------------------------------------------------------
323 FUNCTION salary_cutover_ext_criteria
324 (
325 p_business_group_id IN NUMBER
326 ,p_assignment_id IN NUMBER
327 ,p_effective_date IN DATE
328 )RETURN VARCHAR2
329 IS
330 l_include varchar2(1) := 'Y';
331 l_proc varchar2(72) := g_package||'.salary_cutover_ext_criteria';
332 l_debug VARCHAR2(1);
333 l_error NUMBER;
334 BEGIN
335 debug_enter(l_proc);
336 debug('Inputs are: ');
337 debug('p_business_group_id: '||p_business_group_id);
338 debug('p_assignment_id: '||p_assignment_id);
339 debug('p_effective_date: '||to_char(p_effective_date,'dd/mm/yyyy'));
340 -- reset salary globals
341 g_current_run := 'CUTOVER';
342
343
344 IF g_business_group_id IS NULL
345 OR p_business_group_id <> nvl(g_business_group_id,0) THEN
346
347 g_business_group_id := p_business_group_id;
348 -- set the global debug value
349 g_debug := pqp_gb_psi_functions.check_debug(g_business_group_id);
350
351 debug_enter(l_proc);
352 debug('Inputs are: ');
353 debug('p_business_group_id: '||p_business_group_id);
354 debug('p_assignment_id: '||p_assignment_id);
355 debug('p_effective_date: '||to_char(p_effective_date,'dd/mm/yyyy'));
356
357 PQP_GB_PSI_FUNCTIONS.set_shared_globals
358 (p_business_group_id => p_business_group_id
359 ,p_paypoint => g_paypoint
360 ,p_cutover_date => g_cutover_date
361 ,p_ext_dfn_id => g_ext_dfn_id
362 );
363 g_effective_date := p_effective_date;
364
365 set_salary_history_globals
366 (
367 p_business_group_id => p_business_group_id
368 ,p_assignment_id => p_assignment_id
369 ,p_effective_date => p_effective_date
370 );
371 --Raise extract exceptions which are stored while checking for the setup
372 debug('Raising the set-up errors, with input parameter as S');
373 PQP_GB_PSI_FUNCTIONS.raise_extract_exceptions('S');
374 END IF;
375 l_include := PQP_GB_PSI_FUNCTIONS.chk_penserver_basic_criteria
376 (p_business_group_id => p_business_group_id
377 ,p_effective_date => p_effective_date
378 ,p_assignment_id => p_assignment_id
379 ,p_person_dtl => g_curr_person_dtls
380 ,p_assignment_dtl => g_curr_assg_dtls
381 );
382 IF l_include <> 'N'
383 AND (g_assignment_id IS NULL
384 OR p_assignment_id <> nvl(g_assignment_id,0)) THEN
385
386 set_assignment_globals
387 (
388 p_assignment_id => p_assignment_id
389 ,p_effective_date => p_effective_date
390 );
391 END IF;
392
393 g_salary_start_date := g_effective_date;
394 g_salary_end_date := NULL;
395 debug('Returning : '||l_include,10);
396 debug_exit(l_proc);
397 return l_include;
398 EXCEPTION
399 WHEN others THEN
400 IF SQLCODE <> hr_utility.hr_error_number
401 THEN
402 debug_others (l_proc, 10);
403 IF g_debug
404 THEN
405 DEBUG ( 'Leaving: '
406 || l_proc, -999);
407 END IF;
408 fnd_message.raise_error;
409 ELSE
410 RAISE;
411 END IF;
412 END salary_cutover_ext_criteria;
413 -- ----------------------------------------------------------------------------
414 -- |---------------------< salary_periodic_ext_criteria >---------------------|
415 -- ----------------------------------------------------------------------------
416 FUNCTION salary_periodic_ext_criteria
417 (
418 p_business_group_id IN NUMBER
419 ,p_assignment_id IN NUMBER
420 ,p_effective_date IN DATE
421 )RETURN VARCHAR2
422 IS
423 l_include varchar2(1) := 'Y';
424 l_proc varchar2(72) := g_package||'.salary_periodic_ext_criteria';
425 l_error NUMBER;
426 l_curr_evt_index NUMBER;
427 ----
428 PROCEDURE show_events
429 IS
430 l_proc varchar2(72) := g_package||'.show_events';
431 BEGIN
432 IF g_debug THEN
433 debug_enter(l_proc);
434 IF g_pay_proc_evt_tab.COUNT > 0 THEN
435 debug('====== Detailed Output =======');
436 FOR i IN g_pay_proc_evt_tab.FIRST..g_pay_proc_evt_tab.LAST
437 LOOP
438 debug('----------');
439 debug('Record :'||i);
440 debug('----------');
441 debug('dated_table_id :'||g_pay_proc_evt_tab(i).dated_table_id ,20);
442 debug('datetracked_event :'||g_pay_proc_evt_tab(i).datetracked_event,20);
443 debug('surrogate_key :'||g_pay_proc_evt_tab(i).surrogate_key ,20);
444 debug('column_name :'||g_pay_proc_evt_tab(i).column_name ,20);
445 debug('update_type :'||g_pay_proc_evt_tab(i).update_type ,20);
446 debug('effective_date :'||to_char(g_pay_proc_evt_tab(i).effective_date,'DD/MM/YYYY'),20);
447 debug('creation_date :'||to_char(g_pay_proc_evt_tab(i).actual_date,'DD/MM/YYYY'),20);
448 debug('old_value :'||g_pay_proc_evt_tab(i).old_value ,20);
449 debug('new_value :'||g_pay_proc_evt_tab(i).new_value ,20);
450 debug('change_values :'||g_pay_proc_evt_tab(i).change_values ,20);
451 debug('proration_type :'||g_pay_proc_evt_tab(i).proration_type ,20);
452 debug('change_mode :'||g_pay_proc_evt_tab(i).change_mode ,20);
453 END LOOP;
454 ELSE
455 debug('No Events',20);
456 END IF;
457 debug_exit(l_proc);
458 END IF;
459 END show_events;
460 ----
461 BEGIN --salary_periodic_ext_criteria
462 debug_enter(l_proc);
463 debug('Inputs are: ',10);
464 debug('p_business_group_id: '||p_business_group_id,10);
465 debug('p_assignment_id: '||p_assignment_id,10);
466 debug('p_effective_date: '||to_char(p_effective_date,'dd/mm/yyyy'),10);
467 -- reset salary globals
468 g_current_run := 'PERIODIC';
469
470 IF nvl(g_effective_date,c_highest_date) <> p_effective_date
471 OR p_assignment_id <> nvl(g_assignment_id,0) THEN -- for grade fix(6156192)
472 -- reset globals for every new date
473 g_grade_chg_date := hr_api.g_eot;
474 g_todays_grade_code := '###';
475 END IF;
476
477 g_effective_date := p_effective_date;
478
479
480 IF g_business_group_id IS NULL
481 OR p_business_group_id <> nvl(g_business_group_id,0) THEN
482
483 g_business_group_id := p_business_group_id;
484 -- set the global debug value
485 g_debug := pqp_gb_psi_functions.check_debug(to_char(g_business_group_id));
486 debug_enter(l_proc);
487 debug('Inputs are: ');
488 debug('p_business_group_id: '||p_business_group_id);
489 debug('p_assignment_id: '||p_assignment_id);
490 debug('p_effective_date: '||to_char(p_effective_date,'dd/mm/yyyy'));
491
492 PQP_GB_PSI_FUNCTIONS.set_shared_globals
493 (p_business_group_id => p_business_group_id
494 ,p_paypoint => g_paypoint
495 ,p_cutover_date => g_cutover_date
496 ,p_ext_dfn_id => g_ext_dfn_id
497 );
498
499 set_salary_history_globals
500 (
501 p_business_group_id => p_business_group_id
502 ,p_assignment_id => p_assignment_id
503 ,p_effective_date => p_effective_date
504 );
505 --Raise extract exceptions which are stored while checking for the setup
506 debug('Raising the set-up errors, with input parameter as S');
507 PQP_GB_PSI_FUNCTIONS.raise_extract_exceptions('S');
508 END IF; --IF g_business_group_id IS NULL
509
510 l_include := PQP_GB_PSI_FUNCTIONS.chk_penserver_basic_criteria
511 (p_business_group_id => p_business_group_id
512 ,p_effective_date => p_effective_date
513 ,p_assignment_id => p_assignment_id
514 ,p_person_dtl => g_curr_person_dtls
515 ,p_assignment_dtl => g_curr_assg_dtls
516 );
517
518 IF l_include <> 'N' THEN
519
520 IF g_assignment_id IS NULL
521 OR p_assignment_id <> nvl(g_assignment_id,0) THEN
522
523 set_assignment_globals
524 (
525 p_assignment_id => p_assignment_id
526 ,p_effective_date => p_effective_date
527 );
528 -- use the following for only debugging purposes
529 show_events();
530 END IF;
531
532 l_curr_evt_index := ben_ext_person.g_chg_pay_evt_index;
533
534 debug('----------');
535 debug('Record :'||l_curr_evt_index);
536 debug('----------');
537 debug('dated_table_id :'||g_pay_proc_evt_tab(l_curr_evt_index).dated_table_id ,20);
538 debug('datetracked_event :'||g_pay_proc_evt_tab(l_curr_evt_index).datetracked_event,20);
539 debug('surrogate_key :'||g_pay_proc_evt_tab(l_curr_evt_index).surrogate_key ,20);
540 debug('column_name :'||g_pay_proc_evt_tab(l_curr_evt_index).column_name ,20);
541 debug('update_type :'||g_pay_proc_evt_tab(l_curr_evt_index).update_type ,20);
542 debug('effective_date :'||to_char(g_pay_proc_evt_tab(l_curr_evt_index).effective_date,'DD/MM/YYYY'),20);
543 debug('actual_date :'||to_char(g_pay_proc_evt_tab(l_curr_evt_index).actual_date,'DD/MM/YYYY'),20);
544 debug('old_value :'||g_pay_proc_evt_tab(l_curr_evt_index).old_value ,20);
545 debug('new_value :'||g_pay_proc_evt_tab(l_curr_evt_index).new_value ,20);
546 debug('change_values :'||g_pay_proc_evt_tab(l_curr_evt_index).change_values ,20);
547 debug('proration_type :'||g_pay_proc_evt_tab(l_curr_evt_index).proration_type ,20);
548 debug('change_mode :'||g_pay_proc_evt_tab(l_curr_evt_index).change_mode ,20);
549
550 IF l_include <> 'N' THEN
551 debug('Calling the common include event proc');
552 l_include := pqp_gb_psi_functions.include_event
553 (p_actual_date => g_pay_proc_evt_tab(l_curr_evt_index).actual_date
554 ,p_effective_date => g_pay_proc_evt_tab(l_curr_evt_index).effective_date
555 );
556
557 debug('include_event returned: '||l_include);
558
559 END IF; --IF l_include <> 'N'
560
561 IF l_include <> 'N' THEN
562 -- set start and end dates.
563 --l_error := set_salary_start_end_date();
564 l_error := PQP_GB_PSI_FUNCTIONS.get_start_end_date
565 (
566 p_assignment_id => g_assignment_id
567 ,p_business_group_id => g_business_group_id
568 ,p_effective_date => g_effective_date
569 ,p_start_date => g_salary_start_date
570 ,p_end_date => g_salary_end_date
571 );
572 IF g_salary_start_date IS NULL
573 OR g_salary_start_date > NVL(g_salary_end_date,c_highest_date) THEN
574 IF g_current_run = 'PERIODIC' THEN
575
576 g_salary_start_date := NULL;
577 g_salary_end_date := NULL;
578 l_include := 'N';
579
580 ELSIF g_current_run = 'CUTOVER' THEN
581
582 g_salary_start_date := g_effective_date;
583
584 END IF;--IF g_current_run = 'PERIODIC'
585
586 END IF;-- IF g_salary_start_date IS NULL
587
588 END IF; --IF l_include <> 'N'
589 END IF; --IF l_include <> 'N'
590 pqp_gb_psi_functions.process_retro_event(l_include);
591 debug('Returning : '||l_include,10);
592 debug_exit(l_proc);
593 return l_include;
594 EXCEPTION
595 WHEN others THEN
596 IF SQLCODE <> hr_utility.hr_error_number
597 THEN
598 debug_others (l_proc, 10);
599 IF g_debug
600 THEN
601 DEBUG ( 'Leaving: '
602 || l_proc, -999);
603 END IF;
604 fnd_message.raise_error;
605 ELSE
606 RAISE;
607 END IF;
608 END salary_periodic_ext_criteria;
609 ---
610 -- ----------------------------------------------------------------------------
611 -- |-------------------------< get_salary_start_date >-------------------------|
612 -- ----------------------------------------------------------------------------
613 FUNCTION get_salary_start_date
614 (
615 p_effective_date IN DATE
616 ,p_output OUT NOCOPY VARCHAR2
617 ) RETURN NUMBER
618 IS
619 CURSOR csr_hire_date
620 IS
621 select PPS.DATE_START --DECODE(PER.CURRENT_EMPLOYEE_FLAG,'Y',PPS.DATE_START,NULL)
622 from per_all_people_f PER, per_periods_of_service PPS
623 where per.person_id = g_person_id
624 and pps.person_id = g_person_id
625 and g_effective_date
626 between per.effective_start_date
627 and NVL(per.effective_end_date,c_highest_date)
628 and g_effective_date
629 between pps.date_start
630 and NVL(pps.last_standard_process_date,c_highest_date);
631
632 l_proc varchar2(72) := g_package||'.get_salary_start_date';
633 l_hire_date DATE := NULL;
634 l_error NUMBER;
635 BEGIN
636 debug_enter(l_proc);
637 IF g_current_run = 'CUTOVER' THEN
638 OPEN csr_hire_date;
639 FETCH csr_hire_date INTO l_hire_date;
640 CLOSE csr_hire_date;
641
642 IF l_hire_date IS NULL THEN
643 -- Raise error
644 debug('This person does not have joining date. Please check and correct person details.');
645 l_error := PQP_GB_PSI_FUNCTIONS.raise_extract_error
646 (p_error_number => 94450
647 ,p_error_text => 'BEN_94450_NO_JOINING_DATE'
648 ,p_token1 => to_char(g_effective_date,'dd-MON-yyyy')
649 );
650 p_output := NULL;
651 debug_exit(l_proc);
652 return 0;
653 END IF; --IF l_hire_date IS NULL
654 debug('l_hire_date: '||l_hire_date);
655 p_output := to_char(l_hire_date,'dd/mm/yyyy');
656
657 ELSE --IF g_current_run = 'CUTOVER'
658 p_output := to_char(g_salary_start_date,'dd/mm/yyyy');
659 END IF;
660
661
662 debug_exit(l_proc);
663 return 0;
664 EXCEPTION
665 WHEN others THEN
666 IF SQLCODE <> hr_utility.hr_error_number
667 THEN
668 debug_others (l_proc, 10);
669 IF g_debug
670 THEN
671 DEBUG ( 'Leaving: '
672 || l_proc, -999);
673 END IF;
674 fnd_message.raise_error;
675 ELSE
676 RAISE;
677 END IF;
678 END get_salary_start_date;
679 ---
680 ----------------------------------------------------------------------------
681 -- |---------------------------< get_salary_end_date >---------------------------|
682 -- ----------------------------------------------------------------------------
683 FUNCTION get_salary_end_date
684 (
685 p_effective_date IN DATE
686 ,p_output OUT NOCOPY VARCHAR2
687 ) RETURN NUMBER
688 IS
689 l_proc varchar2(72) := g_package||'.get_salary_end_date';
690 BEGIN
691 debug_enter(l_proc);
692 IF g_current_run = 'CUTOVER' THEN
693 p_output := NULL;
694 debug_exit(l_proc);
695 return 0;
696 END IF;
697 IF g_salary_end_date IS NOT NULL THEN
698 p_output := to_char(g_salary_end_date,'dd/mm/yyyy');
699 ELSE
700 p_output := NULL;
701 END IF;
702 debug_exit(l_proc);
703 return 0;
704 EXCEPTION
705 WHEN others THEN
706 IF SQLCODE <> hr_utility.hr_error_number
707 THEN
708 debug_others (l_proc, 10);
709 IF g_debug
710 THEN
711 DEBUG ( 'Leaving: '
712 || l_proc, -999);
713 END IF;
714 fnd_message.raise_error;
715 ELSE
716 RAISE;
717 END IF;
718 END get_salary_end_date;
719 ---
720 -- ----------------------------------------------------------------------------
721 -- |-------------------------< get_contract_type >-----------------------------|
722 -- ----------------------------------------------------------------------------
723 FUNCTION get_contract_type
724 (
725 p_effective_date IN DATE
726 ,p_output OUT NOCOPY VARCHAR2
727 ) RETURN NUMBER
728 IS
729 l_proc varchar2(72) := g_package||'.get_contract_type';
730 l_contract_type pqp_assignment_attributes_f.contract_type%type;
731 l_work_pattern pqp_assignment_attributes_f.work_pattern%type;
732 l_period_divisor VARCHAR2(10);
733 l_error_msg VARCHAR2(100);
734 l_err_no NUMBER;
735 l_error NUMBER;
736 BEGIN
737 debug_enter(l_proc);
738
739 l_error := PQP_GB_PSI_FUNCTIONS.get_contract_type
740 (
741 p_assignment_id => g_assignment_id
742 ,p_business_group_id => g_business_group_id
743 ,p_effective_date => p_effective_date
744 ,p_contract_type => p_output
745 );
746
747 debug('PenServer Contract Type: '||p_output);
748 debug_exit(l_proc);
749 return l_error;
750 EXCEPTION
751 WHEN others THEN
752 IF SQLCODE <> hr_utility.hr_error_number
753 THEN
754 debug_others (l_proc, 10);
755 IF g_debug
756 THEN
757 DEBUG ( 'Leaving: '
758 || l_proc, -999);
759 END IF;
760 fnd_message.raise_error;
761 ELSE
762 RAISE;
763 END IF;
764 END get_contract_type;
765 ---
766 ------------------------------------------------------------------------------
767 --|-------------------------< get_salary_notional_pay >-----------------------|
768 -- ----------------------------------------------------------------------------
769 FUNCTION get_salary_notional_pay
770 (
771 p_effective_date IN DATE
772 ,p_output OUT NOCOPY VARCHAR2
773 ) RETURN NUMBER
774 IS
775 l_proc varchar2(72) := g_package||'.get_salary_notional_pay';
776 l_notional_pay NUMBER;
777 l_error NUMBER;
778 l_fte_value NUMBER;
779 BEGIN
780 debug_enter(l_proc);
781
782 ----------------------------------------------
783 l_error := PQP_GB_PSI_FUNCTIONS.get_notional_pay
784 (
785 p_assignment_id => g_assignment_id
786 ,p_business_group_id => g_business_group_id
787 ,p_effective_date => g_salary_start_date
788 ,p_name => g_basic_sal_rate_name
789 ,p_rt_element => 'R'
790 ,p_rate => p_output
791 ,p_custom_function => g_user_rate_function
792 );
793
794 l_notional_pay := fnd_number.canonical_to_number(p_output);
795
796 IF nvl(g_sal_ele_fte_attr,'NONE') = 'ALL' THEN
797 l_fte_value := PQP_GB_PSI_FUNCTIONS.get_fte_value
798 (
799 p_assignment_id => g_assignment_id
800 ,p_effective_date => g_effective_date
801 );
802 debug('l_fte_value: '||l_fte_value);
803 IF l_fte_value > 0 THEN
804 -- fte value is returned as -1 when the value is not there.
805 l_notional_pay := l_notional_pay/nvl(l_fte_value,1);
806 END IF;
807 END IF;
808
809 -- the following if clause is added in 115.23
810 IF NOT ( l_notional_pay >= -99999999.99 AND l_notional_pay <= 999999999.99 ) THEN
811 -- raise error that the bonus amount is out of range
812 -- bug fix 4998232
813 debug('ERROR: Bonus Amount out of range: '||l_notional_pay,20);
814 l_error := PQP_GB_PSI_FUNCTIONS.raise_extract_error
815 (p_error_number => 94569
816 ,p_error_text => 'BEN_94569_INV_SALARY_AMOUNT'
817 ,p_token1 => to_char(g_effective_date,'dd/mm/yyyy')
818 ,p_token2 => p_output
819 );
820 ELSE
821 IF l_notional_pay < 0 THEN
822 p_output := ltrim(rtrim(to_char(l_notional_pay,'099999999D99')));
823 ELSE
824 p_output := ltrim(rtrim(to_char(l_notional_pay,'099999999D99')));
825 END IF;
826 END IF;
827
828 g_notional_pay := p_output;
829 debug('Notional Pay: '||p_output,10);
830 debug_exit(l_proc);
831 return 0;
832 ----------------------------------------------
833 EXCEPTION
834 WHEN others THEN
835 IF SQLCODE <> hr_utility.hr_error_number
836 THEN
837 debug_others (l_proc, 10);
838 IF g_debug
839 THEN
840 DEBUG ( 'Leaving: '
841 || l_proc, -999);
842 END IF;
843 fnd_message.raise_error;
844 ELSE
845 RAISE;
846 END IF;
847 END get_salary_notional_pay;
848 ---
849 -- ----------------------------------------------------------------------------
850 -- |---------------------------< get_uniformed_grade >-------------------------|
851 -- ----------------------------------------------------------------------------
852 FUNCTION get_uniformed_grade
853 (
854 p_effective_date IN DATE
855 ,p_output OUT NOCOPY VARCHAR2
856 ) RETURN NUMBER
857 IS
858 l_proc varchar2(72) := g_package||'.get_uniformed_grade';
859 l_grade_id VARCHAR2(30);
860 l_people_group_id NUMBER;
861 l_grade_code VARCHAR2(30);
862 l_uniform_grade_flag VARCHAR2(30);
863 l_flag VARCHAR2(1) := 'N';
864 TYPE base_table_ref_csr_typ IS REF CURSOR;
865 c_base_table base_table_ref_csr_typ;
866 l_query VARCHAR2(1000);
867 l_error NUMBER;
868 BEGIN
869 debug_enter(l_proc);
870
871 -- get the configuration values of people group flexfield
872 IF g_unigrade_source = 'PEOPLE_GROUP' AND g_people_group_column IS NOT NULL THEN
873 -- override to uniformed grade flag can be in People Group Flexfield
874 debug('override to uniformed grade flag can be in People Group Flexfield',30);
875 l_people_group_id := g_curr_assg_dtls.people_group_id;
876
877 IF l_people_group_id IS NULL THEN
878 -- Raise warning
879 debug('People Group flexfield details not attached',40);
880 ELSE
881 pqp_utilities.get_kflex_value
882 (p_entity_name => 'PAY_PEOPLE_GROUPS'
883 ,p_key_column_name => 'PEOPLE_GROUP_ID'
884 ,p_key_column_value => l_people_group_id
885 ,p_segment_column_name => g_people_group_column
886 ,p_segment_column_value => l_uniform_grade_flag
887 );
888 IF l_uniform_grade_flag IS NOT NULL THEN
889 l_flag := 'Y';
890 ELSE
891 debug('Value not entered for Uniformed Grade Flag',40);
892 END IF; --IF l_uniform_grade_flag IS NOT NULL
893
894 END IF;--IF l_people_group_id IS NULL
895
896
897 ELSIF g_unigrade_source = 'ASSIGNMENT'
898 AND g_assignment_column IS NOT NULL
899 AND g_assignment_context IS NOT NULL THEN
900 -- override to uniformed grade flag can be in Assignment Flexfield
901 debug('override to uniformed grade flag can be in Assignment Flexfield',20);
902
903 /*l_query := 'select '||g_assignment_column||' '||'
904 from per_all_assignments_f '||
905 'where business_group_id = '||g_business_group_id||' '||
906 'and assignment_id = '||g_assignment_id||' '||
907 'and ASS_ATTRIBUTE_CATEGORY = '||''''||g_assignment_context||''''||
908 'and to_date('||''''||TO_CHAR(p_effective_date,'dd/mm/yyyy')||''''||
909 ',''dd/mm/yyyy'')'||' between effective_start_date
910 and effective_end_date';*/
911
912 l_query := 'select '||g_assignment_column||' '||
913 'from per_all_assignments_f '||' '||
914 'where business_group_id = '||g_business_group_id||' '||
915 'and assignment_id = '||g_assignment_id||' ';
916 IF g_assignment_context <> 'Global Data Elements' THEN
917 l_query := l_query||
918 'and ASS_ATTRIBUTE_CATEGORY = '''||g_assignment_context||''' ';
919 -- fixed this in v115.26
920 END IF;
921 l_query := l_query||
922 'and to_date('||''''||TO_CHAR(p_effective_date,'dd/mm/yyyy')||''''||
923 ',''dd/mm/yyyy'')'||' between effective_start_date '||
924 'and effective_end_date';
925 --debug('l_query: '||l_query,30);
926
927 OPEN c_base_table FOR l_query;
928 FETCH c_base_table INTO l_uniform_grade_flag;
929 CLOSE c_base_table;
930 IF l_uniform_grade_flag IS NOT NULL THEN
931 l_flag := 'Y';
932 END IF;
933
934 END IF; -- IF g_unigrade_source = 'PEOPLE_GROUP'
935
936 IF l_flag = 'Y' THEN
937 IF l_uniform_grade_flag NOT IN ('Y','N','y','n') THEN
938 -- DATA ERROR
939 debug('ERROR: The Overridden Uniformed Grade Flag is neither Y nor N',20);
940 l_error := PQP_GB_PSI_FUNCTIONS.raise_extract_error
941 (p_error_number => 94443
942 ,p_error_text => 'BEN_94443_INVALID_UNIGRD_FLAG'
943 ,p_token1 => to_char(g_effective_date,'dd-MON-yyyy')
944 ,p_token2 => p_output
945 );
946 debug_exit(l_proc);
947 return 0;
948 ELSE
949 p_output := UPPER(l_uniform_grade_flag);
950 END IF;
951 END IF;--IF l_flag = 'Y' AND p_output <> 'N' AND p_output <> 'Y'
952
953 debug_exit(l_proc);
954 return 0;
955 EXCEPTION
956 WHEN others THEN
957 IF SQLCODE <> hr_utility.hr_error_number
958 THEN
959 debug_others (l_proc, 10);
960 IF g_debug
961 THEN
962 DEBUG ( 'Leaving: '
963 || l_proc, -999);
964 END IF;
965 fnd_message.raise_error;
966 ELSE
967 RAISE;
968 END IF;
969 END get_uniformed_grade;
970 ---
971 -- ----------------------------------------------------------------------------
972 -- |---------------------------< get_grade_code >-------------------------|
973 -- ----------------------------------------------------------------------------
974 FUNCTION get_grade_code
975 (p_grade_id NUMBER
976 ) RETURN VARCHAR2
977 IS
978 l_proc varchar2(72) := g_package||'.get_grade_code';
979 l_grade_code VARCHAR2(30);
980 l_uniform_grade_flag VARCHAR2(30);
981 BEGIN
982 debug_enter(l_proc);
983 debug('p_grade_id: '||p_grade_id);
984
985 IF p_grade_id IS NULL THEN
986 debug('Returning : NULL');
987 debug_exit(l_proc);
988 return NULL;
989 END IF;
990
991 IF g_grade_codes.exists(p_grade_id) THEN
992 debug('available in the cache',20);
993 l_grade_code := g_grade_codes(p_grade_id);
994 ELSE
995 debug('not available in the cache',20);
996 OPEN csr_get_grade_extra_info
997 (
998 p_grade_id => p_grade_id
999 );
1000 FETCH csr_get_grade_extra_info INTO l_grade_code,l_uniform_grade_flag;
1001 CLOSE csr_get_grade_extra_info;
1002
1003 debug('l_grade_code: '||l_grade_code);
1004 debug('l_uniform_grade_flag: '||l_uniform_grade_flag);
1005
1006 g_grade_codes(p_grade_id) := l_grade_code;
1007 END IF;
1008
1009 debug('Returning : '||l_grade_code);
1010 debug_exit(l_proc);
1011 return l_grade_code;
1012 EXCEPTION
1013 WHEN others THEN
1014 IF SQLCODE <> hr_utility.hr_error_number
1015 THEN
1016 debug_others (l_proc, 10);
1017 IF g_debug
1018 THEN
1019 DEBUG ( 'Leaving: '
1020 || l_proc, -999);
1021 END IF;
1022 fnd_message.raise_error;
1023 ELSE
1024 RAISE;
1025 END IF;
1026 END get_grade_code;
1027 -- ----------------------------------------------------------------------------
1028 -- |--------------------------< is_today_grade_change >-----------------------------|
1029 -- Description: This Procedure is to check if there is a change on grade on current
1030 -- processign date.
1031 -- ----------------------------------------------------------------------------
1032 FUNCTION is_today_grade_change
1033 (p_old_value OUT NOCOPY VARCHAR2
1034 ,p_new_value OUT NOCOPY VARCHAR2
1035 ) RETURN VARCHAR2
1036 IS
1037 l_proc varchar2(72) := g_package||'.is_today_grade_change';
1038 l_grade_event_exists VARCHAR2(1) := 'N';
1039 l_index NUMBER;
1040 l_chg_table_id NUMBER;
1041 l_chg_column_name VARCHAR2(30);
1042 l_chg_table VARCHAR2(30);
1043 l_chg_type VARCHAR2(10);
1044 l_chg_date DATE;
1045 l_chg_surrogate_key NUMBER;
1046 l_is_fte_abv VARCHAR2(1);
1047 l_old_value VARCHAR2(20);
1048 l_new_value VARCHAR2(20);
1049 l_change_value varchar2(80);
1050 l_arrow_pos NUMBER;
1051 BEGIN
1052 debug_enter(l_proc);
1053 debug('g_effective_date: '||g_effective_date);
1054 debug('g_grade_chg_date: '||g_grade_chg_date);
1055
1056 IF g_grade_chg_date = g_effective_date THEN
1057 -- if current event date is already processed
1058 debug('Returning: Y');
1059 debug_exit(l_proc);
1060 return 'Y';
1061 ELSIF g_grade_chg_date <> c_highest_date THEN
1062 debug('Returning: N');
1063 debug_exit(l_proc);
1064 return 'N';
1065 END IF;
1066
1067 l_index := ben_ext_person.g_chg_pay_evt_index;
1068 LOOP
1069 l_chg_type := g_pay_proc_evt_tab(l_index).update_type;
1070 l_chg_table_id := g_pay_proc_evt_tab(l_index).dated_table_id;
1071 l_chg_table := PQP_GB_PSI_FUNCTIONS.get_dated_table_name(l_chg_table_id);
1072 l_chg_date := g_pay_proc_evt_tab(l_index).effective_date;
1073 l_chg_column_name := g_pay_proc_evt_tab(l_index).column_name;
1074
1075 debug('l_chg_date: '||l_chg_date);
1076 debug('l_chg_table: '||l_chg_table);
1077 debug('l_chg_column_name: '||l_chg_column_name);
1078 debug('l_chg_type: '||l_chg_type);
1079
1080 IF g_effective_date < g_pay_proc_evt_tab(l_index).effective_date THEN
1081 debug('finished processing all the events on g_effective_date');
1082 EXIT;
1083 END IF; --IF g_effective_date
1084 debug('l_chg_table: '||l_chg_table||' l_chg_type: '||l_chg_type);
1085
1086 -- check for salary element end
1087 IF l_chg_table = 'PER_ALL_ASSIGNMENTS_F'
1088 AND l_chg_column_name = 'GRADE_ID' THEN
1089 debug('Grade event ');
1090 IF l_chg_type = 'U' THEN
1091 debug('For update type pick the value from new_value',40);
1092 l_old_value := g_pay_proc_evt_tab(l_index).old_value;
1093 l_new_value := g_pay_proc_evt_tab(l_index).new_value;
1094 ELSIF l_chg_type = 'C' THEN
1095 debug('For correction type pic the value by parsing change_values',40);
1096 l_change_value := g_pay_proc_evt_tab(l_index).change_values;
1097 l_arrow_pos := instr(l_change_value,'->');
1098 debug('l_change_value: '||l_change_value,40);
1099 l_old_value := ltrim(rtrim(SUBSTR(l_change_value,1,l_arrow_pos-1)));
1100 l_new_value := ltrim(rtrim(SUBSTR(l_change_value,l_arrow_pos+2)));
1101 END IF;
1102
1103 debug('l_old_value: '||l_old_value);
1104 debug('l_new_value: '||l_new_value);
1105
1106 -- latest versions of pay_interpreter_pkg return the string <null>
1107 -- when the old_value/new_value is null
1108 -- the non-numeric exception is caught here.
1109 IF l_old_value IS NOT NULL THEN
1110 BEGIN
1111 p_old_value := get_grade_code(fnd_number.canonical_to_number(l_old_value));
1112 EXCEPTION
1113 WHEN others THEN
1114 debug('l_old_value is not numeric');
1115 END;
1116
1117 END IF;
1118
1119 IF l_new_value IS NOT NULL THEN
1120 BEGIN
1121 p_new_value := get_grade_code(fnd_number.canonical_to_number(l_new_value));
1122 EXCEPTION
1123 WHEN others THEN
1124 debug('l_old_value is not numeric');
1125 END;
1126
1127 END IF;
1128
1129 g_grade_chg_date := g_effective_date;
1130 l_grade_event_exists := 'Y';
1131 EXIT;
1132 END IF;
1133
1134 -- looping condition
1135 IF l_index = g_pay_proc_evt_tab.LAST THEN
1136 EXIT;
1137 ELSE
1138 l_index := g_pay_proc_evt_tab.NEXT(l_index);
1139 END IF;
1140 END LOOP; -- LOOP
1141
1142 debug('p_old_value: '||p_old_value);
1143 debug('p_new_value: '||p_new_value);
1144 debug('Returning: '||l_grade_event_exists);
1145 debug_exit(l_proc);
1146 return l_grade_event_exists;
1147 END is_today_grade_change;
1148 -- ----------------------------------------------------------------------------
1149 -- |---------------------------< get_grade_code >-----------------------------|
1150 -- ----------------------------------------------------------------------------
1151 FUNCTION get_grade_code
1152 (
1153 p_effective_date IN DATE
1154 ,p_output OUT NOCOPY VARCHAR2
1155 ) RETURN NUMBER
1156 IS
1157 l_proc varchar2(72) := g_package||'.get_grade_code';
1158 l_grade_id per_all_assignments_f.grade_id%type;
1159 l_grade_change VARCHAR2(1);
1160 l_grade_code VARCHAR2(10);
1161 l_error NUMBER;
1162 l_old_value VARCHAR2(20);
1163 l_new_value VARCHAR2(20);
1164 BEGIN
1165 debug_enter(l_proc);
1166 -- get the grade code of the person from the for the effective date passed
1167
1168 l_grade_id := g_curr_assg_dtls.grade_id;
1169
1170 IF g_current_run = 'PERIODIC' THEN
1171 debug('for Periodic runs');
1172 debug('g_todays_grade_code: '||g_todays_grade_code);
1173 IF g_todays_grade_code = '###' THEN
1174 debug('grade code NOT set');
1175 /* for first even on a the current event date,
1176 calculate the grade code and set the value */
1177 l_grade_change := is_today_grade_change
1178 (p_old_value => l_old_value
1179 ,p_new_value => l_new_value
1180 );
1181 IF l_grade_change = 'Y' THEN
1182 -- there is a grade change on current date
1183 IF l_old_value IS NULL
1184 AND l_new_value IS NULL THEN
1185 -- condition when there was no grade code and
1186 -- there is no grade code now also
1187 -- report a space, to prevent reporting *s
1188 l_grade_code := ' ';
1189 ELSE
1190 -- on all cases report the new value
1191 -- when the value is null, the value will be padded by *s
1192 -- when the value is not null, actual value is reported
1193 l_grade_code := l_new_value;
1194 END IF;
1195 ELSE -- IF l_grade_change = 'Y'
1196 -- there is no grade change on current date
1197
1198 IF l_grade_id IS NULL THEN -- bugfix 5902824
1199 l_grade_code := ' ';
1200 ELSE
1201 l_grade_code := get_grade_code(l_grade_id);
1202 IF l_grade_code IS NULL THEN
1203 -- when there is no change on grade code and the value is null
1204 -- report a space, to prevent reporting *s
1205 l_grade_code := ' ';
1206 END IF;
1207 END IF;
1208
1209 END IF;
1210
1211 g_todays_grade_code := l_grade_code;
1212
1213 ELSE
1214
1215 debug('grade code already set');
1216 /* if the grade code is already set for the current event date
1217 use the global grade code */
1218 l_grade_code := g_todays_grade_code;
1219 END IF;
1220 ELSE -- IF g_current_run = 'PERIODIC'
1221 debug('for cutover runs');
1222 l_grade_code := get_grade_code(l_grade_id);
1223 IF l_grade_code IS NULL THEN
1224 -- when there is no grade code, on cutover run,
1225 -- report a space, to prevent reporting *s
1226 l_grade_code := ' ';
1227 END IF;
1228 END IF;
1229
1230 /*OPEN csr_get_grade_extra_info
1231 (
1232 p_grade_id => l_grade_id
1233 );
1234 FETCH csr_get_grade_extra_info INTO l_grade_code,l_uniform_grade_flag;
1235 CLOSE csr_get_grade_extra_info;*/
1236
1237 IF l_grade_id IS NOT NULL THEN
1238 IF ltrim(l_grade_code) IS NULL THEN
1239 -- Raise warning when a grade is attahced which has no grade code
1240 debug('Warning: Grade attached to the person has no extra information: '||l_grade_id,30);
1241 l_error := PQP_GB_PSI_FUNCTIONS.raise_extract_warning
1242 (p_error_number => 94448
1243 ,p_error_text => 'BEN_94448_NO_GRADE_EIT'
1244 ,p_token1 => to_char(g_effective_date,'dd-MON-yyyy')
1245 );
1246 ELSIF NOT pqp_gb_psi_functions.is_alphanumeric(l_grade_code) THEN
1247 -- Raise error when the grade code is not alphanumeric
1248 debug('ERROR: Grade code is invalid: '||p_output,30);
1249 l_error := PQP_GB_PSI_FUNCTIONS.raise_extract_error
1250 (p_error_number => 94447
1251 ,p_error_text => 'BEN_94447_INV_GRD_CODE'
1252 ,p_token1 => to_char(g_effective_date,'dd-MON-yyyy')
1253 ,p_token2 => l_grade_code
1254 );
1255 END IF;
1256 END IF;
1257
1258 p_output := l_grade_code;
1259
1260 debug('p_output: '||p_output,10);
1261 debug_exit(l_proc);
1262 return 0;
1263 EXCEPTION
1264 WHEN others THEN
1265 IF SQLCODE <> hr_utility.hr_error_number
1266 THEN
1267 debug_others (l_proc, 10);
1268 IF g_debug
1269 THEN
1270 DEBUG ( 'Leaving: '
1271 || l_proc, -999);
1272 END IF;
1273 fnd_message.raise_error;
1274 ELSE
1275 RAISE;
1276 END IF;
1277 END get_grade_code;
1278 ---
1279 -- ----------------------------------------------------------------------------
1280 -- |------------------------< get_salary_actual_pay >--------------------------|
1281 -- ----------------------------------------------------------------------------
1282 FUNCTION get_salary_actual_pay
1283 (
1284 p_effective_date IN DATE
1285 ,p_output OUT NOCOPY VARCHAR2
1286 )RETURN NUMBER
1287 IS
1288 l_proc varchar2(72) := g_package||'.get_salary_actual_pay';
1289 l_actual_pay NUMBER;
1290 l_error NUMBER;
1291 --
1292 BEGIN
1293 debug_enter(l_proc);
1294 l_error := PQP_GB_PSI_FUNCTIONS.get_actual_pay
1295 (
1296 p_assignment_id => g_assignment_id
1297 ,p_notional_pay => g_notional_pay
1298 ,p_effective_date => g_salary_start_date
1299 ,p_output => p_output
1300 );
1301
1302 l_actual_pay := fnd_number.canonical_to_number(p_output);
1303
1304 -- the following if clause is added in 115.23
1305 IF l_actual_pay < 0 THEN
1306 p_output := ltrim(rtrim(to_char(l_actual_pay,'099999999D99')));
1307 ELSE
1308 p_output := ltrim(rtrim(to_char(l_actual_pay,'099999999D99')));
1309 END IF;
1310
1311 debug('Actual Pay: '||p_output,10);
1312 debug_exit(l_proc);
1313 return l_error;
1314 EXCEPTION
1315 WHEN others THEN
1316 IF SQLCODE <> hr_utility.hr_error_number
1317 THEN
1318 debug_others (l_proc, 10);
1319 IF g_debug
1320 THEN
1321 DEBUG ( 'Leaving: '
1322 || l_proc, -999);
1323 END IF;
1324 fnd_message.raise_error;
1325 ELSE
1326 RAISE;
1327 END IF;
1328 END get_salary_actual_pay;
1329 ---
1330 -- ----------------------------------------------------------------------------
1331 -- |---------------------< salary_data_element_value >-------------------------|
1332 -- ----------------------------------------------------------------------------
1333 FUNCTION salary_data_element_value
1334 (
1335 p_ext_user_value IN VARCHAR2
1336 ,p_output_value OUT NOCOPY VARCHAR2
1337 ) RETURN NUMBER
1338 IS
1339 l_proc varchar2(72) := g_package||'.salary_data_element_value';
1340 l_error NUMBER;
1341
1342 BEGIN --salary_data_element_value
1343
1344 debug_enter(l_proc);
1345 debug('p_ext_user_value: '||p_ext_user_value,10);
1346
1347 IF p_ext_user_value = 'SalaryStartDate' THEN
1348 l_error := get_salary_start_date
1349 (
1350 p_effective_date => g_effective_date
1351 ,p_output => p_output_value
1352 );
1353 ELSIF p_ext_user_value = 'SalaryEndDate' THEN
1354 l_error := get_salary_end_date
1355 (
1356 p_effective_date => g_effective_date
1357 ,p_output => p_output_value
1358 );
1359 ELSIF p_ext_user_value = 'SalaryNotionalPay' THEN
1360 l_error := get_salary_notional_pay
1361 (
1362 p_effective_date => g_effective_date
1363 ,p_output => p_output_value
1364 );
1365 ELSIF p_ext_user_value = 'ContractType' THEN
1366 l_error := get_contract_type
1367 (
1368 p_effective_date => g_effective_date
1369 ,p_output => p_output_value
1370 );
1371 ELSIF p_ext_user_value = 'UniformedGrade' THEN
1372 l_error := get_uniformed_grade
1373 (
1374 p_effective_date => g_effective_date
1375 ,p_output => p_output_value
1376 );
1377 ELSIF p_ext_user_value = 'GradeCode' THEN
1378 l_error := get_grade_code
1379 (
1380 p_effective_date => g_effective_date
1381 ,p_output => p_output_value
1382 );
1383 ELSIF p_ext_user_value = 'SalaryActualPay' THEN
1384 l_error := get_salary_actual_pay
1385 (
1386 p_effective_date => g_effective_date
1387 ,p_output => p_output_value
1388 );
1389 END IF;
1390 debug('p_output_value: '||p_output_value,10);
1391 debug_exit(l_proc);
1392 return l_error;
1393 EXCEPTION
1394 WHEN others THEN
1395 IF SQLCODE <> hr_utility.hr_error_number
1396 THEN
1397 debug_others (l_proc, 10);
1398 IF g_debug
1399 THEN
1400 DEBUG ( 'Leaving: '
1401 || l_proc, -999);
1402 END IF;
1403 fnd_message.raise_error;
1404 ELSE
1405 RAISE;
1406 END IF;
1407 END salary_data_element_value;
1408 ------
1409 -- ----------------------------------------------------------------------------
1410 -- |----------------------< salary_post_processing >--------------------------|
1411 -- Description: This is the post-processing rule for the Salary History.
1412 -- ----------------------------------------------------------------------------
1413 FUNCTION salary_post_processing RETURN VARCHAR2
1414 IS
1415 l_proc varchar2(72) := g_package||'.salary_post_processing';
1416 BEGIN
1417 debug_enter(l_proc);
1418
1419 --Raise extract exceptions which are stored while processing the data elements
1420 --PQP_GB_PSI_FUNCTIONS.raise_extract_exceptions();
1421
1422 --call the common post processing function
1423 PQP_GB_PSI_FUNCTIONS.common_post_process(g_business_group_id);
1424
1425 debug_exit(l_proc);
1426 return 'Y';
1427 EXCEPTION
1428 WHEN others THEN
1429 IF SQLCODE <> hr_utility.hr_error_number
1430 THEN
1431 debug_others (l_proc, 10);
1432 IF g_debug
1433 THEN
1434 DEBUG ( 'Leaving: '
1435 || l_proc, -999);
1436 END IF;
1437 fnd_message.raise_error;
1438 ELSE
1439 RAISE;
1440 END IF;
1441 END salary_post_processing;
1442 ------
1443 END PQP_GB_PSI_SALARY_HISTORY;