[Home] [Help]
PACKAGE BODY: APPS.PQP_GB_PSI_STH_HISTORY
Source
1 PACKAGE BODY PQP_GB_PSI_STH_HISTORY AS
2 -- /* $Header: pqpgbpsisth.pkb 120.2.12000000.3 2007/03/01 13:39:44 mseshadr 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
42 ,p_trace_on IN VARCHAR2 DEFAULT NULL)
43 IS
44 BEGIN
45 --
46 IF g_debug THEN
47 IF pqp_utilities.g_nested_level = 0 THEN
48 hr_utility.trace_on(NULL, 'REQID'); -- Pipe name REQIDnnnnn
49 END IF;
50 pqp_utilities.debug_enter (
51 p_proc_name => p_proc_name
52 ,p_trace_on => p_trace_on
53 );
54 END IF;
55 --
56
57 END debug_enter;
58
59
60 -- This procedure is used for debug purposes
61 --
62 -- ----------------------------------------------------------------------------
63 -- |----------------------------< debug_exit >--------------------------------|
64 -- ----------------------------------------------------------------------------
65
66 PROCEDURE debug_exit (p_proc_name IN VARCHAR2
67 ,p_trace_off IN VARCHAR2 DEFAULT NULL )
68 IS
69 BEGIN
70 --
71 IF g_debug THEN
72 pqp_utilities.debug_exit (
73 p_proc_name => p_proc_name
74 ,p_trace_off => p_trace_off
75 );
76
77 IF pqp_utilities.g_nested_level = 0 THEN
78 hr_utility.trace_off;
79 END IF;
80 END IF;
81 --
82 END debug_exit;
83
84
85 -- This procedure is used for debug purposes
86 --
87 -- ----------------------------------------------------------------------------
88 -- |----------------------------< debug_others >------------------------------|
89 -- ----------------------------------------------------------------------------
90
91 PROCEDURE debug_others (p_proc_name IN VARCHAR2, p_proc_step IN NUMBER)
92 IS
93 BEGIN
94 --
95 pqp_utilities.debug_others (
96 p_proc_name => p_proc_name
97 ,p_proc_step => p_proc_step
98 );
99 --
100 END debug_others;
101 -- ----------------------------------------------------------------------------
102 -- |---------------< get_element_type_details >-------------------|
103 -- Description:
104 -- ----------------------------------------------------------------------------
105 PROCEDURE get_element_type_details
106 (
107 p_element_type_id OUT NOCOPY t_number
108 ,p_element_name OUT NOCOPY t_varchar2
109 ,p_processing_type OUT NOCOPY t_varchar2
110 ,p_input_value_id OUT NOCOPY t_number
111 ,p_name OUT NOCOPY t_varchar2
112 ,p_uom OUT NOCOPY t_varchar2
113 ,p_unit_of_measure OUT NOCOPY t_varchar2
114 )
115 IS
116 l_proc varchar2(72) := g_package||'.get_element_type_details';
117 l_element_set_ids t_number;
118 l_index NUMBER;
119 l_match_exists VARCHAR2(10);
120
121 TYPE r_element_type_details IS RECORD
122 (
123 element_type_id t_number
124 ,element_name t_varchar2
125 ,processing_type t_varchar2
126 ,input_value_id t_number
127 ,name t_varchar2
128 ,uom t_varchar2
129 ,unit_of_measure t_varchar2
130 );
131
132 l_element_type_details r_element_type_details;
133
134 CURSOR csr_get_element_set_ids
135 IS
136 SELECT element_set_id
137 FROM pay_event_group_usages pegu
138 ,pay_event_groups peg
139 WHERE peg.event_group_name = 'PQP_GB_PSI_STH_ELEMENT_ENTRIES'
140 AND peg.legislation_code = 'GB'
141 AND peg.business_group_id IS NULL
142 AND peg.event_group_id = pegu.event_group_id;
143
144 CURSOR csr_get_element_type_details
145 (
146 p_element_set_id pay_element_set_members.element_set_id%TYPE
147 )
148 IS
149 SELECT pet.element_type_id
150 ,pet.element_name
151 ,pet.processing_type
152 ,piv.input_value_id
153 ,piv.name
154 ,piv.uom
155 ,piv.unit_of_measure
156 FROM pay_element_set_members pes
157 ,pay_input_values_v piv
158 ,pay_element_types_f pet
159 WHERE pes.element_set_id = p_element_set_id
160 AND pes.element_type_id = pet.element_type_id
161 AND pet.element_type_id = piv.element_type_id (+)
162 ORDER BY ELEMENT_NAME;
163
164 BEGIN
165 debug_enter(l_proc);
166 OPEN csr_get_element_set_ids;
167 FETCH csr_get_element_set_ids BULK COLLECT INTO l_element_set_ids;
168 CLOSE csr_get_element_set_ids;
169
170 IF l_element_set_ids.COUNT > 0 THEN
171 debug('No of element sets attached: '||l_element_set_ids.COUNT);
172 FOR i IN l_element_set_ids.FIRST..l_element_set_ids.LAST
173 LOOP
174 debug('l_element_set_ids('||i||'): '||l_element_set_ids(i));
175 OPEN csr_get_element_type_details(l_element_set_ids(i));
176 FETCH csr_get_element_type_details BULK COLLECT INTO l_element_type_details;
177 CLOSE csr_get_element_type_details;
178
179 debug('l_element_type_details.element_type_id.COUNT: '||
180 l_element_type_details.element_type_id.COUNT);
181
182 FOR i IN 1..l_element_type_details.element_type_id.COUNT LOOP
183 IF p_element_type_id.COUNT = 0 THEN
184 debug('First entry',30);
185 p_element_type_id := l_element_type_details.element_type_id;
186 p_element_name := l_element_type_details.element_name;
187 p_processing_type := l_element_type_details.processing_type;
188 p_input_value_id := l_element_type_details.input_value_id;
189 p_name := l_element_type_details.name;
190 p_uom := l_element_type_details.uom;
191 p_unit_of_measure := l_element_type_details.unit_of_measure;
192 EXIT;
193 ELSE -- count is non zero
194 l_index := p_element_type_id.LAST;
195 l_match_exists := 'N';
196 debug('p_element_type_id.COUNT: '||p_element_type_id.COUNT);
197 FOR j IN 1..p_element_type_id.COUNT LOOP
198 IF p_element_type_id(j) = l_element_type_details.element_type_id(i) AND
199 p_input_value_id(j) = l_element_type_details.input_value_id(i)
200 THEN
201 -- Combination exist so do nothing
202 debug('Element type already exists',40);
203 l_match_exists := 'Y';
204 EXIT;
205 END IF; -- End if of match exists check ...
206 END LOOP; -- j loop
207
208 debug('Out of j loop');
209 IF l_match_exists = 'N' THEN
210 -- store the information
211 l_index := l_index + 1;
212 p_element_type_id(l_index) := l_element_type_details.element_type_id(i);
213 p_element_name(l_index) := l_element_type_details.element_name(i);
214 p_processing_type(l_index) := l_element_type_details.processing_type(i);
215 p_input_value_id(l_index) := l_element_type_details.input_value_id(i);
216 p_name(l_index) := l_element_type_details.name(i);
217 p_uom(l_index) := l_element_type_details.uom(i);
218 p_unit_of_measure(l_index) := l_element_type_details.unit_of_measure(i);
219 END IF; -- End if of match does not exist ...
220 END IF; -- End if of return collection count is zero check ...
221 END LOOP; -- i loop
222
223 END LOOP;
224
225 IF g_debug THEN
226 FOR i IN 1..p_element_type_id.COUNT
227 LOOP
228 debug('**********ROW : '||i||' *******');
229 debug('element_type_id: '||p_element_type_id(i));
230 debug('element_name: '||p_element_name(i));
231 debug('processing_type: '||p_processing_type(i));
232 debug('input_value_id: '||p_input_value_id(i));
233 debug('name: '||p_name(i));
234 debug('uom: '||p_uom(i));
235 debug('unit_of_measure: '||p_unit_of_measure(i));
236 debug('*******************************');
237 END LOOP;
238 END IF;-- IF g_debug THEN
239
240 ELSE
241 debug('No element set attached');
242 END IF; --IF l_element_set_ids.COUNT) > 0
243
244 debug_exit(l_proc);
245 EXCEPTION
246 WHEN others THEN
247 IF SQLCODE <> hr_utility.hr_error_number
248 THEN
249 debug_others (l_proc, 10);
250 IF g_debug
251 THEN
252 DEBUG ( 'Leaving: '
253 || l_proc, -999);
254 END IF;
255 fnd_message.raise_error;
256 ELSE
257 RAISE;
258 END IF;
259 END get_element_type_details;
260 -- ----------------------------------------------------------------------------
261 -- |------------------------< chk_valid_elements >----------------------------|
262 -- Description:
263 -- ----------------------------------------------------------------------------
264 PROCEDURE chk_valid_elements
265 IS
266 l_proc varchar2(72) := g_package||'.chk_valid_elements';
267 l_claim_date_exists BOOLEAN;
268 l_adj_hours_exists BOOLEAN;
269 l_err_claim_date BOOLEAN;
270 l_err_adj_hours BOOLEAN;
271 l_ele_proc_type VARCHAR2(1);
272 l_index NUMBER;
273 l_prev_ele_type_id NUMBER;
274 l_curr_ele_type_id NUMBER;
275 l_element_type_details r_element_type_details;
276 l_element_name VARCHAR2(80);
277 l_last_ele_proc BOOLEAN := FALSE;
278
279 /*TYPE t_number IS TABLE OF NUMBER
280 INDEX BY BINARY_INTEGER;
281 TYPE t_varchar2 IS TABLE OF VARCHAR2(80)
282 INDEX BY BINARY_INTEGER;
283 -- these variable types are declared globally
284 */
285
286 l_element_type_ids t_number;
287 l_element_names t_varchar2;
288 l_processing_type t_varchar2;
289 l_input_value_ids t_number;
290 l_input_values t_varchar2;
291 l_unit_of_measure t_varchar2;
292 l_uom_meaning t_varchar2;
293
294 /*CURSOR csr_get_element_type_details
295 IS
296 SELECT pet.element_type_id
297 ,pet.element_name
298 ,pet.processing_type
299 ,piv.name
300 ,piv.uom
301 ,piv.unit_of_measure
302 FROM pay_event_groups peg
303 ,pay_event_group_usages pegu
304 ,pay_element_set_members pes
305 ,pay_input_values_v piv
306 ,pay_element_types_f pet
307 WHERE peg.event_group_name = 'PQP_GB_PSI_STH_ELEMENT_ENTRIES'
308 AND peg.event_group_id = pegu.event_group_id
309 AND pegu.element_set_id = pes.element_set_id
310 AND pes.element_type_id = pet.element_type_id
311 AND pet.element_type_id = piv.element_type_id (+)
312 ORDER BY ELEMENT_NAME;*/
313 -- not using the above cursor after perf fix
314 -- this cursor is split into two as in pqp_utilities.entries.effected
315 -- and made as a new procedure get_element_type_details
316
317 BEGIN
318 debug_enter(l_proc);
319
320 -- clear g_valid_element_type_details
321 g_valid_element_type_details.DELETE;
322
323 get_element_type_details
324 (
325 p_element_type_id => l_element_type_ids
326 ,p_element_name => l_element_names
327 ,p_processing_type => l_processing_type
328 ,p_input_value_id => l_input_value_ids
329 ,p_name => l_input_values
330 ,p_uom => l_unit_of_measure
331 ,p_unit_of_measure => l_uom_meaning
332 );
333
334 debug('l_element_type_ids.count: '||l_element_type_ids.count);
335
336 IF l_element_type_ids.COUNT > 0 THEN
337
338 l_claim_date_exists := FALSE;
339 l_adj_hours_exists := FALSE;
340 l_err_claim_date := FALSE;
341 l_err_adj_hours := FALSE;
342 l_index := l_element_type_ids.FIRST;
343 l_prev_ele_type_id := l_element_type_ids(l_index);
344
345
346 LOOP
347 l_curr_ele_type_id := l_element_type_ids(l_index);
348 IF g_debug THEN
349 debug('---------------------');
350 debug('Record: '||l_index);
351 debug('l_curr_ele_type_id: '||l_curr_ele_type_id,20);
352 debug('l_prev_ele_type_id: '||l_prev_ele_type_id,20);
353
354 IF l_claim_date_exists THEN
355 debug('l_claim_date_exists: TRUE',20);
356 ELSE
357 debug('l_claim_date_exists: FALSE',20);
358 END IF;
359 IF l_adj_hours_exists THEN
360 debug('l_adj_hours_exists: TRUE',20);
361 ELSE
362 debug('l_adj_hours_exists: FALSE',20);
363 END IF;
364 IF l_err_claim_date THEN
365 debug('l_err_claim_date: TRUE',20);
366 ELSE
367 debug('l_err_claim_date: FALSE',20);
368 END IF;
369 IF l_err_adj_hours THEN
370 debug('l_err_adj_hours: TRUE',20);
371 ELSE
372 debug('l_err_adj_hours: FALSE',20);
373 END IF;
374 END IF;
375
376
377 IF l_curr_ele_type_id <> l_prev_ele_type_id
378 OR (l_index = l_element_type_ids.LAST
379 AND l_last_ele_proc) THEN
380 -- new element type being processed
381 IF l_prev_ele_type_id = l_curr_ele_type_id
382 AND l_index = l_element_type_ids.LAST THEN
383 l_element_name := l_element_names(l_index);
384 l_ele_proc_type := l_processing_type(l_index);
385 ELSE
386 l_element_name := l_element_names(l_index-1);
387 l_ele_proc_type := l_processing_type(l_index-1);
388 END IF;
389
390 IF NOT l_claim_date_exists
391 AND ( g_current_layout = 'SINGLE'
392 OR
393 nvl(g_adj_hrs_source,' ') <> 'BALANCE'
394 )
395 THEN -- check only for single records
396 -- need not check for Accumulated records
397 -- if it uses balance type for adjusted hours
398 -- bug abcedfg
399
400 -- raise error that the element type has no claim date
401 debug('ERROR: No Claim Date for element type: '||l_element_names(l_index),40);
402 PQP_GB_PSI_FUNCTIONS.store_extract_exceptions
403 (p_extract_type => 'SHORT-TIME HOURS HISTORY'
404 ,p_error_number => 94502
405 ,p_error_text => 'BEN_94502_NO_CLAIM_DATE_IV'
406 ,p_token1 => l_element_name
407 ,p_error_warning_flag => 'E'
408 );
409 END IF;--IF NOT l_claim_date_exists THEN
410
411 -- IF NOT l_adj_hours_exists THEN
412 --5549469 l_adj_hours_exists not to be checked in
413 --balance mode
414 IF NOT l_adj_hours_exists
415 AND ( g_current_layout = 'SINGLE'
416 OR
417 nvl(g_adj_hrs_source,' ') <> 'BALANCE' )
418 THEN
419 -- raise error that the element type has no adjusted hours
420 debug('ERROR: No Adjusted Hours for element type: '||l_element_names(l_index),40);
421 PQP_GB_PSI_FUNCTIONS.store_extract_exceptions
422 (p_extract_type => 'SHORT-TIME HOURS HISTORY'
423 ,p_error_number => 94503
424 ,p_error_text => 'BEN_94503_NO_ADJ_HOURS_IV'
425 ,p_token1 => l_element_name
426 ,p_error_warning_flag => 'E'
427 );
428 END IF;--IF NOT l_adj_hours_exists THEN
429
430 IF (l_claim_date_exists
431 OR
432 NOT l_claim_date_exists AND nvl(g_adj_hrs_source,' ') = 'BALANCE'
433 )
434 AND (
435 l_adj_hours_exists --5902824
436 OR
437 ( NOT l_adj_hours_exists
438 AND nvl(g_adj_hrs_source,' ')='BALANCE'
439 )
440 )
441
442 AND NOT (l_err_claim_date OR l_err_adj_hours)
443 AND l_ele_proc_type = 'N' THEN -- only non-recurrign elements wil be added
444
445 debug('Element Type '||l_element_name||' Qualifies initial check',40);
446 IF l_prev_ele_type_id = l_curr_ele_type_id
447 AND l_index = l_element_type_ids.LAST THEN
448 debug('Finished processing',50);
449 g_valid_element_type_details(l_prev_ele_type_id).element_type_name
450 := l_element_names(l_index);
451 EXIT;
452 END IF;
453 l_index := l_index - 1;
454 g_valid_element_type_details(l_prev_ele_type_id).element_type_name
455 := l_element_names(l_index);
456 ELSE
457 debug('Element Type '||l_element_name||' does NOT Qualify initial check',40);
458 IF l_ele_proc_type = 'R' THEN
459 debug('WARNING: Element Type '||l_element_name||' is recurring element');
460 PQP_GB_PSI_FUNCTIONS.store_extract_exceptions
461 (p_extract_type => 'SHORT-TIME HOURS HISTORY'
462 ,p_error_number => 94529
463 ,p_error_text => 'BEN_94529_REC_STH_ELEMENT'
464 ,p_token1 => l_element_name
465 ,p_error_warning_flag => 'W'
466 );
467 END IF;
468 IF l_prev_ele_type_id = l_curr_ele_type_id
469 AND l_index = l_element_type_ids.LAST THEN
470 debug('Finished processing',50);
471 EXIT;
472 END IF;
473 l_index := l_index - 1;
474 END IF;--IF l_claim_date_exists AND l_adj_hours_exists
475
476 l_prev_ele_type_id := l_curr_ele_type_id;
477
478 -- reset values
479 l_claim_date_exists := FALSE;
480 l_adj_hours_exists := FALSE;
481 l_err_claim_date := FALSE;
482 l_err_adj_hours := FALSE;
483 l_curr_ele_type_id := l_element_type_ids(l_index);
484 debug('Start Processing new element type');
485
486 ELSE --IF l_curr_ele_type_id <> l_prev_ele_type_id
487 -- for same element type
488 IF UPPER(l_input_values(l_index)) = 'CLAIM DATE'
489 AND ( g_current_layout = 'SINGLE'
490 OR
491 nvl(g_adj_hrs_source,' ') <> 'BALANCE'
492 )
493 THEN -- check only for single records
494 -- need not check for Accumulated records
495 -- if it uses balance type for adjusted hours
496 -- bug abcedfg
497
498 IF l_unit_of_measure(l_index) = 'D' THEN
499 -- valid data type for claim date
500 debug('valid data type for claim date',30);
501 l_claim_date_exists := TRUE;
502 l_err_claim_date := FALSE;
503 ELSE
504 l_claim_date_exists := TRUE;
505 -- error on the data type of the claim date
506 l_err_claim_date := TRUE;
507 debug('ERROR: Invalid Claim Date for element type: '||l_element_names(l_index),30);
508 PQP_GB_PSI_FUNCTIONS.store_extract_exceptions
509 (p_extract_type => 'SHORT-TIME HOURS HISTORY'
510 ,p_error_number => 94504
511 ,p_error_text => 'BEN_94504_ERR_CLAIM_DATE_IV'
512 ,p_token1 => l_element_names(l_index)
513 ,p_token2 => l_uom_meaning(l_index)
514 ,p_error_warning_flag => 'E'
515 );
516 END IF;--IF l_unit_of_measure(l_index) = 'D'
517
518 END IF;--IF UPPER(l_input_values(l_index)) = 'CLAIM DATE'
519
520 --5549469
521 IF UPPER(l_input_values(l_index)) = 'ADJUSTED HOURS'
522 AND ( g_current_layout = 'SINGLE'
523 OR
524 nvl(g_adj_hrs_source,' ') <> 'BALANCE'
525 )
526 THEN
527 IF l_unit_of_measure(l_index)
528 IN ('H_DECIMAL1'
529 ,'H_DECIMAL2'
530 ,'H_DECIMAL3'
531 ,'H_HH'
532 ,'H_HHMM'
533 ,'H_HHMMSS'
534 ,'N') THEN
535 debug('valid data type for adjusted hours',30);
536 l_adj_hours_exists := TRUE;
537 l_err_adj_hours := FALSE;
538
539 ELSE
540 l_adj_hours_exists := TRUE;
541 -- error on the data type of the claim date
542 l_err_adj_hours := TRUE;
543 debug('ERROR: Invalid Adjusted Hours for element type: '||l_element_names(l_index),30);
544 PQP_GB_PSI_FUNCTIONS.store_extract_exceptions
545 (p_extract_type => 'SHORT-TIME HOURS HISTORY'
546 ,p_error_number => 94505
547 ,p_error_text => 'BEN_94505_ERR_ADJ_HOURS_IV'
548 ,p_token1 => l_element_names(l_index)
549 ,p_token2 => l_uom_meaning(l_index)
550 ,p_error_warning_flag => 'E'
551 );
552 END IF;--IF l_unit_of_measure(l_index)
553
554 END IF;--IF UPPER(l_input_values(l_index)) = 'ADJUSTED HOURS'
555
556 IF l_index = l_element_type_ids.LAST THEN
557 l_index := l_index - 1;
558 l_last_ele_proc := true;
559 END IF;
560
561 END IF;--IF l_curr_ele_type_id <> l_prev_ele_type_id THEN
562
563
564 -- loop condition
565 IF l_index = l_element_type_ids.LAST THEN
566 debug('Finished processing',30);
567 EXIT;
568 ELSE
569 l_index := l_index+1;
570 END IF;
571
572 END LOOP;
573
574 END IF; --IF l_element_type_ids.COUNT > 0
575
576 debug_exit(l_proc);
577 EXCEPTION
578 WHEN others THEN
579 IF SQLCODE <> hr_utility.hr_error_number
580 THEN
581 debug_others (l_proc, 10);
582 IF g_debug
583 THEN
584 DEBUG ( 'Leaving: '
585 || l_proc, -999);
586 END IF;
587 fnd_message.raise_error;
588 ELSE
589 RAISE;
590 END IF;
591 END chk_valid_elements;
592 ---
593 -- ----------------------------------------------------------------------------
594 -- |---------------< get_adj_hrs_conf_values >-------------------|
595 -- Description: This procedure will be called only for Accumulated records.
596 -- This is used to fetch the configuration value for the
597 -- Adjusted Hours.
598 -- ----------------------------------------------------------------------------
599 PROCEDURE get_adj_hrs_conf_values
600 IS
601 l_proc varchar2(72) := g_package||'.get_adj_hrs_conf_values';
602 l_config_values PQP_UTILITIES.t_config_values;
603 BEGIN
604 debug_enter(l_proc);
605 -- fetch the adj hrs source configuration values
606 PQP_UTILITIES.get_config_type_values(
607 p_configuration_type => 'PQP_GB_PENSERVER_STH_ADJHR_MAP'
608 ,p_business_group_id => g_business_group_id
609 ,p_legislation_code => g_legislation_code
610 ,p_tab_config_values => l_config_values
611 );
612 IF l_config_values.COUNT > 0 THEN
613 debug('Configration value exists',20);
614
615 g_adj_hrs_source := l_config_values(l_config_values.FIRST).pcv_information1;
616 g_adj_hrs_bal_type := l_config_values(l_config_values.FIRST).pcv_information2;
617
618 debug('g_adj_hrs_source: '||g_adj_hrs_source,20);
619 debug('g_adj_hrs_bal_type: '||g_adj_hrs_bal_type,20);
620
621 IF g_adj_hrs_source = 'BALANCE'
622 AND g_adj_hrs_bal_type IS NULL THEN
623 debug('ERROR: No value provided for balance type.',30);
624 PQP_GB_PSI_FUNCTIONS.store_extract_exceptions
625 (p_extract_type => 'SHORT-TIME HOURS HISTORY'
626 ,p_error_number => 94632
627 ,p_error_text => 'BEN_94632_NO_ADJ_HRS_BAL_TYPE'
628 ,p_error_warning_flag => 'E'
629 );
630 END IF;
631 ELSE
632 debug('Configration value is nto present',20);
633 -- raise error
634 PQP_GB_PSI_FUNCTIONS.store_extract_exceptions
635 (p_extract_type => 'SHORT-TIME HOURS HISTORY'
636 ,p_error_number => 94633
637 ,p_error_text => 'BEN_94633_NO_ADJ_HRS_CONFIG'
638 ,p_error_warning_flag => 'E'
639 );
640 END IF;
641 debug_exit(l_proc);
642 EXCEPTION
643 WHEN others THEN
644 IF SQLCODE <> hr_utility.hr_error_number
645 THEN
646 debug_others (l_proc, 10);
647 IF g_debug
648 THEN
649 DEBUG ( 'Leaving: '
650 || l_proc, -999);
651 END IF;
652 fnd_message.raise_error;
653 ELSE
654 RAISE;
655 END IF;
656 END get_adj_hrs_conf_values;
657 --------------
658 -- ----------------------------------------------------------------------------
659 -- |---------------< get_curr_val_from_bal >-------------------|
660 -- Description: This procedure will be called only for Accumulated records.
661 -- This is used to fetch the configuration value for the
662 -- Adjusted Hours.
663 -- ----------------------------------------------------------------------------
664 PROCEDURE get_curr_val_from_bal
665 IS
666 l_proc varchar2(72) := g_package||'.get_curr_val_from_bal';
667 l_adjusted_hours NUMBER;
668 l_start_date DATE;
669 l_end_date DATE;
670 CURSOR csr_ele_entry_dates
671 IS
672 SELECT effective_start_date,
673 effective_end_date
674 FROM PAY_ELEMENT_ENTRIES_F
675 WHERE element_entry_id = g_curr_element_entry_id;
676
677 BEGIN
678 debug_enter(l_proc);
679
680 OPEN csr_ele_entry_dates;
681 FETCH csr_ele_entry_dates INTO l_start_date, l_end_date;
682 CLOSE csr_ele_entry_dates;
683
684 l_adjusted_hours := hr_gbbal.calc_asg_proc_ptd_date
685 (p_assignment_id => g_assignment_id
686 ,p_balance_type_id => g_adj_hrs_bal_type
687 ,p_effective_date => l_end_date
688 );
689 debug('l_start_date: '||l_start_date,10);
690 debug('l_end_date: '||l_end_date,10);
691 debug('l_adjusted_hours: '||l_adjusted_hours,10);
692
693 IF g_start_date IS NULL THEN
694 --first row
695 debug('First row reported');
696 g_start_date := l_start_date;
697 g_end_date := l_end_date;
698 g_adjusted_hours := l_adjusted_hours;
699 ELSE
700 -- next rows
701 debug('Not the first row reported');
702 g_end_date := l_end_date;
703 g_adjusted_hours := g_adjusted_hours + l_adjusted_hours;
704 END IF;
705
706 debug('g_start_date: '||g_start_date,10);
707 debug('g_end_date: '||g_end_date,10);
708 debug('g_adjusted_hours: '||g_adjusted_hours,10);
709
710 debug('Marking that the current pay period is processed',10);
711 g_reported_pay_periods(fnd_number.canonical_to_number(TO_CHAR(l_start_date,'ddmmyyyy'))) := 'Y';
712
713 debug_exit(l_proc);
714 EXCEPTION
715 WHEN others THEN
716 IF SQLCODE <> hr_utility.hr_error_number
717 THEN
718 debug_others (l_proc, 10);
719 IF g_debug
720 THEN
721 DEBUG ( 'Leaving: '
722 || l_proc, -999);
723 END IF;
724 fnd_message.raise_error;
725 ELSE
726 RAISE;
727 END IF;
728 END get_curr_val_from_bal;
729 --------------
730 -- ----------------------------------------------------------------------------
731 -- |---------------< set_short_time_hours_globals >-------------------|
732 -- Description:
733 -- ----------------------------------------------------------------------------
734 PROCEDURE set_short_time_hours_globals
735 (
736 p_business_group_id IN NUMBER
737 ,p_assignment_id IN NUMBER
738 ,p_effective_date IN DATE
739 )
740 IS
741 l_proc varchar2(72) := g_package||'.set_short_time_hours_globals';
742 BEGIN
743 debug_enter(l_proc);
744 -- set global business group id
745 g_business_group_id := p_business_group_id;
746 g_valid_element_type_details.DELETE;
747 g_legislation_code := 'GB';
748
749 -- fetch the adjusted hours cofiguration value for accumulated records
750 IF g_current_layout = 'ACCUMULATED' THEN
751 debug('Fetch the adj hrs config values for accumulated records',20);
752 get_adj_hrs_conf_values();
753 END IF;
754
755 --check for the element types, thier input values and thier data types.
756 chk_valid_elements();
757
758
759 debug('g_legislation_code: '||g_legislation_code,10);
760 debug('g_business_group_id: '||g_business_group_id,10);
761 debug_exit(l_proc);
762 EXCEPTION
763 WHEN others THEN
764 IF SQLCODE <> hr_utility.hr_error_number
765 THEN
766 debug_others (l_proc, 10);
767 IF g_debug
768 THEN
769 DEBUG ( 'Leaving: '
770 || l_proc, -999);
771 END IF;
772 fnd_message.raise_error;
773 ELSE
774 RAISE;
775 END IF;
776 END set_short_time_hours_globals;
777 ---
778 -- ----------------------------------------------------------------------------
779 -- |-----------------------< set_assignment_globals >--------------------------|
780 -- ----------------------------------------------------------------------------
781 PROCEDURE set_assignment_globals
782 (
783 p_assignment_id IN NUMBER
784 ,p_effective_date IN DATE
785 )
786 IS
787 l_proc varchar2(72) := g_package||'.set_assignment_globals';
788
789 CURSOR csr_start_date
790 IS
791 select PPS.DATE_START -- DECODE(PER.CURRENT_EMPLOYEE_FLAG,'Y',PPS.DATE_START,NULL)
792 from per_all_people_f PER, per_periods_of_service PPS
793 where per.person_id = g_person_id
794 and pps.person_id = g_person_id
795 and p_effective_date between per.effective_start_date
796 and NVL(per.effective_end_date,hr_api.g_eot)
797 and p_effective_date between pps.date_start
798 and NVL(pps.final_process_date,hr_api.g_eot);
799
800 BEGIN -- set_assignment_globals
801 debug_enter(l_proc);
802 debug('Inputs are: ',10);
803 debug('p_assignment_id: '||p_assignment_id,10);
804 debug('p_effective_date: '||to_char(p_effective_date,'dd/mm/yyyy'),10);
805
806 -- reset assignment level globals
807 g_start_date := NULL;
808 g_end_date := NULL;
809 g_effective_date := NULL;
810 g_adjusted_hours := NULL;
811 g_proc_ele_entries.DELETE;
812 g_reported_claim_dates.DELETE;
813 g_reported_pay_periods.DELETE;
814
815 -- set the global events table
816 g_pay_proc_evt_tab := ben_ext_person.g_pay_proc_evt_tab;
817
818 -- set global assignment_id
819 g_assignment_id := p_assignment_id;
820 debug('g_assignment_id: '||g_assignment_id,10);
821 g_person_id := PQP_GB_PSI_FUNCTIONS.get_current_extract_person
822 (
823 p_assignment_id => p_assignment_id
824 );
825
826 --set the assignment start date
827 OPEN csr_start_date;
828 FETCH csr_start_date INTO g_assg_start_date;
829 CLOSE csr_start_date;
830
831 debug_exit(l_proc);
832 EXCEPTION
833 WHEN others THEN
834 IF SQLCODE <> hr_utility.hr_error_number
835 THEN
836 debug_others (l_proc, 10);
837 IF g_debug
838 THEN
839 DEBUG ( 'Leaving: '
840 || l_proc, -999);
841 END IF;
842 fnd_message.raise_error;
843 ELSE
844 RAISE;
845 END IF;
846 END set_assignment_globals;
847 ---
848 -- ----------------------------------------------------------------------------
849 -- |-----------------------< is_proper_adj_hours >--------------------------|
850 -- Description:
851 -- ----------------------------------------------------------------------------
852 FUNCTION is_proper_adj_hours
853 (
854 p_adj_hours IN NUMBER
855 ,p_element_name IN VARCHAR2
856 ,p_element_entry_id IN NUMBER
857 )RETURN BOOLEAN
858 IS
859 l_proc varchar2(72) := g_package||'.is_proper_adj_hours';
860 l_temp NUMBER;
861 l_return BOOLEAN := TRUE;
862 BEGIN -- is_future_claim
863 debug_enter(l_proc);
864 debug('Inputs are: ',10);
865 debug('p_adj_hours: '||p_adj_hours,10);
866
867 IF round(p_adj_hours,2) > +99999999.99 OR p_adj_hours < -99999999.99 THEN
868 -- raise ERROR
869 debug('ERROR: Adjusted hours is not in the range of -99999999.99 to +99999999.99',30);
870 l_temp := PQP_GB_PSI_FUNCTIONS.raise_extract_error
871 (p_error_number => 94538
872 ,p_error_text => 'BEN_94538_INV_ADJ_HOURS'
873 ,p_token1 => SUBSTR(p_element_name
874 ||'('||p_element_entry_id||')',1,80)
875 ,p_token2 => p_adj_hours
876 );
877 l_return := FALSE;
878 debug('Returning FALSE.',20);
879 END IF;
880
881 debug_exit(l_proc);
882 RETURN l_return;
883 EXCEPTION
884 WHEN others THEN
885 IF SQLCODE <> hr_utility.hr_error_number
886 THEN
887 debug_others (l_proc, 10);
888 IF g_debug
889 THEN
890 DEBUG ( 'Leaving: '
891 || l_proc, -999);
892 END IF;
893 fnd_message.raise_error;
894 ELSE
895 RAISE;
896 END IF;
897 END is_proper_adj_hours;
898 ---
899 -- ----------------------------------------------------------------------------
900 -- |-----------------------< chk_part_timer >--------------------------|
901 -- Description:
902 -- ----------------------------------------------------------------------------
903 PROCEDURE chk_part_timer
904 (
905 p_claim_date IN DATE
906 )
907 IS
908 l_proc varchar2(72) := g_package||'.chk_part_timer';
909 l_fte_value NUMBER;
910 l_return NUMBER;
911 CURSOR csr_get_fte_value
912 IS
913 SELECT value
914 FROM PER_ASSIGNMENT_BUDGET_VALUES_F
915 WHERE assignment_id = g_assignment_id
916 AND UNIT = 'FTE'
917 AND p_claim_date between effective_start_date
918 AND effective_end_date;
919 BEGIN -- is_future_claim
920 debug_enter(l_proc);
921 debug('Inputs are: ',10);
922 debug('p_claim_date: '||p_claim_date,10);
923
924 OPEN csr_get_fte_value;
925 FETCH csr_get_fte_value INTO l_fte_value;
926 CLOSE csr_get_fte_value;
927
928 IF l_fte_value IS NULL THEN
929 debug('WARNING: No FTE Value');
930 l_return := PQP_GB_PSI_FUNCTIONS.raise_extract_warning
931 (p_error_number => 94515
932 ,p_error_text => 'BEN_94515_NO_FTE_VALUE'
933 ,p_token1 => to_char(p_claim_date,'dd/mm/yyyy')
934 ,p_token2 => SUBSTR(g_curr_element_type_name
935 ||'('||ben_ext_person.g_chg_surrogate_key||')',1,80)
936 );
937
938 ElSIF l_fte_value >= 1 THEN
939 debug('WARNING: Full Timer');
940 l_return := PQP_GB_PSI_FUNCTIONS.raise_extract_warning
941 (p_error_number => 94516
942 ,p_error_text => 'BEN_94516_STH_FULL_TIMER'
943 ,p_token1 => to_char(p_claim_date,'dd/mm/yyyy')
944 ,p_token2 => SUBSTR(g_curr_element_type_name
945 ||'('||ben_ext_person.g_chg_surrogate_key||')',1,80)
946 );
947 END IF;
948
949 debug_exit(l_proc);
950 EXCEPTION
951 WHEN others THEN
952 IF SQLCODE <> hr_utility.hr_error_number
953 THEN
954 debug_others (l_proc, 10);
955 IF g_debug
956 THEN
957 DEBUG ( 'Leaving: '
958 || l_proc, -999);
959 END IF;
960 fnd_message.raise_error;
961 ELSE
962 RAISE;
963 END IF;
964 END chk_part_timer;
965 ---
966 -- ----------------------------------------------------------------------------
967 -- |-----------------------< set_curr_row_values >----------------------------|
968 -- Description:
969 -- ----------------------------------------------------------------------------
970 FUNCTION set_curr_row_values RETURN BOOLEAN
971 IS
972 l_proc varchar2(72) := g_package||'.set_curr_row_values';
973 l_chg_table VARCHAR2(30);
974 l_chg_type VARCHAR2(30);
975 l_chg_surrogate_key NUMBER;
976 l_chg_date DATE;
977 l_input_value_name VARCHAR2(30);
978 l_adj_hours VARCHAR2(60);
979 l_adj_hours_num NUMBER;
980 l_claim_date VARCHAR2(20);
981 l_claim_date_dt DATE;
982 l_claim_date_no NUMBER;
983 l_return NUMBER;
984
985 CURSOR csr_curr_element_input_values
986 (
987 p_element_entry_id pay_element_entries_f.element_entry_id%TYPE
988 ,p_effective_date DATE
989 )
990 IS
991 SELECT piv.NAME
992 ,peev.screen_entry_value
993 FROM pay_element_entry_values_f peev
994 ,pay_input_values_f piv
995 WHERE peev.element_entry_id = p_element_entry_id
996 AND peev.input_value_id = piv.input_value_id
997 AND UPPER(piv.NAME) IN ('CLAIM DATE','ADJUSTED HOURS')
998 --AND peev.effective_start_date = p_effective_date
999 ORDER BY piv.name;
1000
1001 CURSOR csr_get_element_type_details
1002 (
1003 p_element_entry_id NUMBER
1004 )
1005 IS
1006 SELECT peef.element_type_id
1007 ,pet.element_name
1008 FROM pay_element_entries_f peef
1009 ,pay_element_types_f pet
1010 WHERE peef.element_entry_id = p_element_entry_id
1011 AND pet.element_type_id = peef.element_type_id;
1012
1013 CURSOR csr_get_ee_id
1014 (
1015 p_ele_entry_value_id NUMBER
1016 )
1017 IS
1018 SELECT element_entry_id
1019 FROM pay_element_entry_values_f
1020 WHERE element_entry_value_id = p_ele_entry_value_id
1021 AND ROWNUM = 1;
1022
1023 BEGIN
1024 debug_enter(l_proc);
1025 l_chg_table := ben_ext_person.g_chg_pay_table;
1026 l_chg_type := ben_ext_person.g_chg_update_type;
1027 l_chg_surrogate_key := ben_ext_person.g_chg_surrogate_key;
1028 l_chg_date := ben_ext_person.g_chg_eff_dt;
1029 debug('l_chg_table: '||l_chg_table,10);
1030 debug('l_chg_type: '||l_chg_type,10);
1031 debug('l_chg_surrogate_key: '||l_chg_surrogate_key,10);
1032 debug('l_chg_date: '||l_chg_date,10);
1033
1034 IF l_chg_table <> 'PAY_ELEMENT_ENTRIES_F'
1035 AND (l_chg_type NOT IN ('I','C') ) THEN
1036
1037 debug('Not a valid event, will not be processed',20);
1038 debug_exit(l_proc);
1039 RETURN FALSE;
1040
1041 END IF;
1042
1043 g_curr_element_entry_id := fnd_number.canonical_to_number(l_chg_surrogate_key);
1044
1045 IF l_chg_type = 'C' THEN
1046 -- for correction events on pay_element_entry_values_f
1047 -- the surrogate key is element_entry_value_id
1048 -- this should be re-set to element_entry_id
1049 debug('element_entry_value_id: '||g_curr_element_entry_id,20);
1050 OPEN csr_get_ee_id(g_curr_element_entry_id);
1051 FETCH csr_get_ee_id INTO g_curr_element_entry_id;
1052 CLOSE csr_get_ee_id;
1053 debug('element_entry_id: '||g_curr_element_entry_id,20);
1054 END IF;
1055
1056 IF g_proc_ele_entries.exists(g_curr_element_entry_id) THEN
1057 debug('The element entry is already processed.',20);
1058 debug_exit(l_proc);
1059 RETURN FALSE;
1060 ELSE
1061 g_proc_ele_entries(g_curr_element_entry_id) := 'Y';
1062 END IF;
1063
1064 IF nvl(g_adj_hrs_source,' ') <> 'BALANCE' THEN
1065 -- this is for Single records or for Accumulated records
1066 -- and the adj hrs source is element entries.
1067 OPEN csr_get_element_type_details(g_curr_element_entry_id);
1068 FETCH csr_get_element_type_details INTO g_curr_element_type_id, g_curr_element_type_name;
1069 CLOSE csr_get_element_type_details;
1070
1071 IF NOT g_valid_element_type_details.EXISTS(g_curr_element_type_id) THEN
1072 debug('Not a valid element type, will not be processed',20);
1073 debug_exit(l_proc);
1074 RETURN FALSE;
1075 END IF;
1076
1077 OPEN csr_curr_element_input_values
1078 (
1079 p_element_entry_id => g_curr_element_entry_id
1080 ,p_effective_date => l_chg_date
1081 );
1082 FETCH csr_curr_element_input_values INTO l_input_value_name,l_adj_hours;
1083 debug('After first fetch',10);
1084
1085 IF csr_curr_element_input_values%FOUND THEN
1086 IF UPPER(l_input_value_name) = 'ADJUSTED HOURS' THEN
1087 debug('l_input_value_name: '||l_input_value_name
1088 ||' l_adj_hours: '||l_adj_hours,20);
1089 debug('Input value is Accumulated Hours',20);
1090
1091
1092 FETCH csr_curr_element_input_values INTO l_input_value_name,l_claim_date;
1093 debug('After second fetch',20);
1094
1095 IF csr_curr_element_input_values%FOUND THEN
1096 -- if second fetch got some value, it will be for
1097 -- claim date
1098 debug('l_input_value_name: '||l_input_value_name
1099 ||' l_claim_date: '||l_claim_date,30);
1100
1101 ELSIF g_current_layout = 'SINGLE' THEN
1102
1103 debug('No Claim Date',30);
1104 g_effective_date := NULL;
1105
1106 END IF; --IF csr_curr_element_input_values%FOUND
1107
1108 ELSE -- first input value is Claim Date
1109
1110 -- this means that there is no adjusted hours for this element
1111 IF g_current_layout = 'SINGLE' THEN
1112
1113 debug('No Adjusted Hours input value for this element',40);
1114 g_adjusted_hours := NULL;
1115
1116 END IF;
1117
1118 debug('Input value is Claim Date',30);
1119 l_claim_date := l_adj_hours;
1120
1121 END IF; --IF UPPER(l_input_value_name) = 'ADJUSTED HOURS'
1122
1123
1124 ELSIF g_current_layout = 'SINGLE' THEN
1125 g_effective_date := NULL;
1126 g_adjusted_hours := NULL;
1127 END IF; --IF csr_curr_element_input_values%FOUND
1128
1129 IF l_claim_date IS NOT NULL THEN
1130 debug('Claim Date is not null',20);
1131 l_claim_date_dt := fnd_date.canonical_to_date(l_claim_date);
1132 l_claim_date_no := fnd_number.canonical_to_number(TO_CHAR(l_claim_date_dt,'ddmmyyyy'));
1133
1134
1135
1136 IF pqp_gb_psi_functions.is_proper_claim_date(l_claim_date_dt
1137 ,g_curr_element_type_name
1138 ,g_curr_element_entry_id
1139 ,g_assg_start_date) THEN
1140
1141 IF NOT g_reported_claim_dates.EXISTS(l_claim_date_no) THEN
1142 debug('The Claim date is NOT reported',30);
1143 g_reported_claim_dates(l_claim_date_no)
1144 := g_curr_element_type_name||'('||g_curr_element_entry_id||')';
1145
1146 -- check whether the assignment is ful-time on the claim date or else raise a warning.
1147 chk_part_timer(l_claim_date_dt);
1148
1149 IF g_current_layout = 'SINGLE' THEN
1150 debug('assign the claim date to g_effective_date for SINGLE records',40);
1151 g_effective_date := l_claim_date_dt;
1152 debug('g_effective_date: '||g_effective_date);
1153 ELSE -- ACCUMULATED Records
1154 IF NVL(g_start_date,hr_api.g_eot) > l_claim_date_dt THEN
1155 debug(l_claim_date_dt||' is less than '||g_start_date,50);
1156 g_start_date := l_claim_date_dt;
1157 END IF;
1158 IF NVL(g_end_date,hr_api.g_sot) < l_claim_date_dt THEN
1159 debug(l_claim_date_dt||' is greater than '||g_end_date,50);
1160 g_end_date := l_claim_date_dt;
1161 END IF;
1162 debug('g_start_date: '||g_start_date,40);
1163 debug('g_end_date: '||g_end_date,40);
1164 END IF;--IF g_current_layout = 'SINGLE'
1165 ELSE
1166 debug('ERROR: the claim date is already reported by element entry id: '
1167 ||g_reported_claim_dates(l_claim_date_no));
1168 l_return := PQP_GB_PSI_FUNCTIONS.raise_extract_error
1169 (p_error_number => 94514
1170 ,p_error_text => 'BEN_94514_DUP_CLAIM_DATE'
1171 ,p_token1 => to_date(l_claim_date_dt,'dd/mm/yyyy')
1172 ,p_token2 => SUBSTR(g_curr_element_type_name
1173 ||'('||ben_ext_person.g_chg_surrogate_key||')',1,80)
1174 ,p_token3 => g_reported_claim_dates(l_claim_date_no)
1175 );
1176 debug('Returning : FALSE',30);
1177 debug_exit(l_proc);
1178 return FALSE;
1179 END IF;--IF NOT g_reported_claim_dates.EXISTS(l_claim_date_no)
1180 ELSE
1181 -- the claim date is not proper
1182 debug('Returning : FALSE',20);
1183 debug_exit(l_proc);
1184 return FALSE;
1185 END IF;--IF is_proper_claim_date(l_claim_date)
1186 ELSIF g_current_layout = 'SINGLE' THEN
1187 debug('No Claim Date');
1188 g_effective_date := NULL;
1189 END IF; --IF l_claim_date IS NOT NULL
1190
1191 debug('g_adjusted_hours: '||g_adjusted_hours,10);
1192
1193 l_adj_hours_num := fnd_number.canonical_to_number(l_adj_hours);
1194
1195 debug('l_adj_hours_num: '||l_adj_hours_num);
1196
1197 IF l_adj_hours IS NOT NULL
1198 AND is_proper_adj_hours(l_adj_hours_num,g_curr_element_type_name,g_curr_element_entry_id) THEN
1199
1200 debug('adjusted hours is proper and is not null',30);
1201 IF g_current_layout = 'ACCUMULATED' THEN
1202 g_adjusted_hours := NVL(g_adjusted_hours,0)
1203 +
1204 l_adj_hours_num;
1205 ELSIF g_current_layout = 'SINGLE' THEN
1206 g_adjusted_hours := l_adj_hours_num;
1207 END IF; --IF g_current_layout = 'ACCUMULATED'
1208
1209 END IF;--IF l_adj_hours IS NOT NULL
1210
1211 debug('g_adjusted_hours: '||g_adjusted_hours,10);
1212
1213 ELSE
1214 -- balance approach for accumulated records
1215 get_curr_val_from_bal();
1216
1217 END IF; -- IF nvl(g_adj_hrs_source,' ') = 'BALANCE'
1218 debug_exit(l_proc);
1219 RETURN TRUE;
1220 EXCEPTION
1221 WHEN others THEN
1222 IF SQLCODE <> hr_utility.hr_error_number
1223 THEN
1224 debug_others (l_proc, 10);
1225 IF g_debug
1226 THEN
1227 DEBUG ( 'Leaving: '
1228 || l_proc, -999);
1229 END IF;
1230 fnd_message.raise_error;
1231 ELSE
1232 RAISE;
1233 END IF;
1234 END set_curr_row_values;
1235 ----
1236 -- ----------------------------------------------------------------------------
1237 -- |--------------------< short_time_hours_criteria >----------------------|
1238 -- Description:
1239 -- ----------------------------------------------------------------------------
1240 FUNCTION short_time_hours_criteria
1241 (
1242 p_business_group_id IN NUMBER
1243 ,p_assignment_id IN NUMBER
1244 ,p_effective_date IN DATE
1245 )RETURN VARCHAR2
1246 IS
1247 l_proc varchar2(72) := g_package||'.short_time_hours_criteria';
1248 l_include VARCHAR2(1) := 'Y';
1249 l_cutover_date DATE;
1250 l_curr_evt_index NUMBER;
1251
1252 BEGIN
1253 debug_enter(l_proc);
1254 debug('Inputs are: ');
1255 debug('p_business_group_id: '||p_business_group_id,10);
1256 debug('p_assignment_id: '||p_assignment_id,10);
1257 debug('p_effective_date: '||to_char(p_effective_date,'dd/mm/yyyy'),10);
1258
1259
1260 IF g_business_group_id IS NULL
1261 OR p_business_group_id <> nvl(g_business_group_id,0) THEN
1262
1263 g_business_group_id := p_business_group_id;
1264
1265 PQP_GB_PSI_FUNCTIONS.set_shared_globals
1266 (p_business_group_id => p_business_group_id
1267 ,p_paypoint => g_paypoint
1268 ,p_cutover_date => l_cutover_date
1269 ,p_ext_dfn_id => g_ext_dfn_id
1270 );
1271
1272 set_short_time_hours_globals
1273 (
1274 p_business_group_id => p_business_group_id
1275 ,p_assignment_id => p_assignment_id
1276 ,p_effective_date => p_effective_date
1277 );
1278
1279 IF g_valid_element_type_details.COUNT > 0 THEN
1280 debug('Count of valid elements: '||g_valid_element_type_details.COUNT,20);
1281 ELSE
1282 --raise error saying that there are no valid elements
1283 debug('ERROR: No Valid Elements',30);
1284 PQP_GB_PSI_FUNCTIONS.store_extract_exceptions
1285 (p_extract_type => 'SHORT-TIME HOURS HISTORY'
1286 ,p_error_number => 94506
1287 ,p_error_text => 'BEN_94506_NO_VALID_STH_ELEMENT'
1288 ,p_error_warning_flag => 'E'
1289 );
1290 l_include := 'N';
1291
1292 END IF;--IF g_valid_element_type_details.COUNT > 0
1293
1294
1295 --Raise extract exceptions which are stored while checking for the setup
1296 debug('Raising the set-up errors, with input parameter as S',10);
1297 PQP_GB_PSI_FUNCTIONS.raise_extract_exceptions('S');
1298 END IF; --IF g_business_group_id IS NULL
1299
1300 IF l_include = 'N' THEN
1301 debug('Returning : '||l_include,20);
1302 debug_exit(l_proc);
1303 return l_include;
1304 END IF; --IF l_include = 'N'
1305
1306
1307 IF (g_assignment_id IS NULL
1308 OR p_assignment_id <> nvl(g_assignment_id,0) )
1309 OR nvl(g_adj_hrs_source,' ') = 'BALANCE' THEN
1310
1311 -- if adj hrs source is balance or fro a new assignment
1312 l_include := PQP_GB_PSI_FUNCTIONS.chk_penserver_basic_criteria
1313 (p_business_group_id => p_business_group_id
1314 ,p_effective_date => p_effective_date
1315 ,p_assignment_id => p_assignment_id
1316 ,p_person_dtl => g_curr_person_dtls
1317 ,p_assignment_dtl => g_curr_assg_dtls
1318 );
1319
1320 IF nvl(g_adj_hrs_source,' ') <> 'BALANCE' THEN
1321 -- if the adj hrs source is not balance, then calle to
1322 -- basic criteria is a dummy one.
1323 l_include := 'Y';
1324 END IF;
1325
1326 IF l_include = 'N'
1327 AND nvl(g_adj_hrs_source,' ') = 'BALANCE' THEN
1328
1329 -- reject the event if not valid on effective date
1330 -- only if the adj hrs source is balance type.
1331 -- if adj hrs source is element entries, basic criteria
1332 -- will be validated on the claim date.
1333 debug('Returning : '||l_include,30);
1334 debug_exit(l_proc);
1335 return l_include;
1336
1337 END IF; --IF l_include = 'N'
1338
1339 IF (g_assignment_id IS NULL
1340 OR p_assignment_id <> nvl(g_assignment_id,0) ) THEN
1341
1342 -- for every new assignment
1343 set_assignment_globals
1344 (
1345 p_assignment_id => p_assignment_id
1346 ,p_effective_date => p_effective_date
1347 );
1348 END IF;
1349 END IF;
1350
1351 -- bug fix 5365237
1352 IF nvl(g_adj_hrs_source,' ') = 'BALANCE'
1353 AND g_reported_pay_periods.EXISTS(fnd_number.canonical_to_number
1354 (
1355 TO_CHAR(ben_ext_person.g_chg_eff_dt,'ddmmyyyy')
1356 )
1357 )THEN
1358 -- if the current pay period is already reported.
1359 debug('Current pay period starting on :'||ben_ext_person.g_chg_eff_dt||' is already processed');
1360 l_include := 'N';
1361 debug('Returning : '||l_include,20);
1362 debug_exit(l_proc);
1363 return l_include;
1364 END IF;
1365
1366 l_curr_evt_index := ben_ext_person.g_chg_pay_evt_index;
1367 debug('----------');
1368 debug('Record :'||l_curr_evt_index);
1369 debug('----------');
1370 debug('surrogate_key :'||g_pay_proc_evt_tab(l_curr_evt_index).surrogate_key ,20);
1371 debug('update_type :'||g_pay_proc_evt_tab(l_curr_evt_index).update_type ,20);
1372 debug('effective_date :'||to_char(g_pay_proc_evt_tab(l_curr_evt_index).effective_date,'DD/MM/YYYY'),20);
1373 debug('actual_date :'||to_char(g_pay_proc_evt_tab(l_curr_evt_index).actual_date,'DD/MM/YYYY'),20);
1374 debug('----------');
1375
1376 debug('Calling the common include event proc');
1377 l_include := pqp_gb_psi_functions.include_event
1378 (p_actual_date => g_pay_proc_evt_tab(l_curr_evt_index).actual_date
1379 ,p_effective_date => g_pay_proc_evt_tab(l_curr_evt_index).effective_date
1380 ,p_run_from_cutover_date => 'Y' ---- Bugfix 4969368
1381 );
1382 debug('include_event returned: '||l_include);
1383
1384 IF l_include = 'N'
1385 OR NOT set_curr_row_values() THEN
1386 --current event is not accepted
1387 l_include := 'N';
1388 END IF;
1389
1390 pqp_gb_psi_functions.process_retro_event(l_include);
1391
1392 debug('Returning : '||l_include,10);
1393 debug_exit(l_proc);
1394 return l_include;
1395 EXCEPTION
1396 WHEN others THEN
1397 IF SQLCODE <> hr_utility.hr_error_number
1398 THEN
1399 debug_others (l_proc, 10);
1400 IF g_debug
1401 THEN
1402 DEBUG ( 'Leaving: '
1403 || l_proc, -999);
1404 END IF;
1405 fnd_message.raise_error;
1406 ELSE
1407 RAISE;
1408 END IF;
1409 END short_time_hours_criteria;
1410 ---
1411 -- ----------------------------------------------------------------------------
1412 -- |--------------------< short_time_hours_sin_criteria >----------------------|
1413 -- Description:
1414 -- ----------------------------------------------------------------------------
1415 FUNCTION short_time_hours_sin_criteria
1416 (
1417 p_business_group_id IN NUMBER
1418 ,p_assignment_id IN NUMBER
1419 ,p_effective_date IN DATE
1420 )RETURN VARCHAR2
1421 IS
1422 l_proc varchar2(72) := g_package||'.short_time_hours_sin_criteria';
1423 l_return varchar2(1) := 'Y';
1424 BEGIN
1425 debug_enter(l_proc);
1426 g_current_layout := 'SINGLE';
1427
1428 IF g_business_group_id IS NULL
1429 OR p_business_group_id <> nvl(g_business_group_id,0) THEN
1430
1431 -- set the global debug value
1432 g_debug := pqp_gb_psi_functions.check_debug(p_business_group_id);
1433 debug_enter(l_proc);
1434 END IF;
1435 l_return := short_time_hours_criteria
1436 (
1437 p_business_group_id => p_business_group_id
1438 ,p_assignment_id => p_assignment_id
1439 ,p_effective_date => p_effective_date
1440 );
1441
1442 debug('l_return: '||l_return);
1443 debug_exit(l_proc);
1444 RETURN l_return;
1445 EXCEPTION
1446 WHEN others THEN
1447 IF SQLCODE <> hr_utility.hr_error_number
1448 THEN
1449 debug_others (l_proc, 10);
1450 IF g_debug
1451 THEN
1452 DEBUG ( 'Leaving: '
1453 || l_proc, -999);
1454 END IF;
1455 fnd_message.raise_error;
1456 ELSE
1457 RAISE;
1458 END IF;
1459 END short_time_hours_sin_criteria;
1460 ---
1461 -- ----------------------------------------------------------------------------
1462 -- |--------------------< short_time_hours_acc_criteria >----------------------|
1463 -- Description:
1464 -- ----------------------------------------------------------------------------
1465 FUNCTION short_time_hours_acc_criteria
1466 (
1467 p_business_group_id IN NUMBER
1468 ,p_assignment_id IN NUMBER
1469 ,p_effective_date IN DATE
1470 )RETURN VARCHAR2
1471 IS
1472 l_proc varchar2(72) := g_package||'.short_time_hours_acc_criteria';
1473 l_return varchar2(1) := 'Y';
1474 BEGIN
1475 debug_enter(l_proc);
1476 g_current_layout := 'ACCUMULATED';
1477
1478 IF g_business_group_id IS NULL
1479 OR p_business_group_id <> nvl(g_business_group_id,0) THEN
1480
1481 -- set the global debug value
1482 g_debug := pqp_gb_psi_functions.check_debug(p_business_group_id);
1483 debug_enter(l_proc);
1484 END IF;
1485
1486 l_return := short_time_hours_criteria
1487 (
1488 p_business_group_id => p_business_group_id
1489 ,p_assignment_id => p_assignment_id
1490 ,p_effective_date => p_effective_date
1491 );
1492 debug('l_return: '||l_return);
1493 debug_exit(l_proc);
1494 return l_return;
1495 EXCEPTION
1496 WHEN others THEN
1497 IF SQLCODE <> hr_utility.hr_error_number
1498 THEN
1499 debug_others (l_proc, 10);
1500 IF g_debug
1501 THEN
1502 DEBUG ( 'Leaving: '
1503 || l_proc, -999);
1504 END IF;
1505 fnd_message.raise_error;
1506 ELSE
1507 RAISE;
1508 END IF;
1509 END short_time_hours_acc_criteria;
1510 ---
1511 -- ----------------------------------------------------------------------------
1512 -- |--------------------< short_time_hours_data_ele_val >----------------------|
1513 -- Description:
1514 -- ----------------------------------------------------------------------------
1515 FUNCTION short_time_hours_data_ele_val
1516 (
1517 p_ext_user_value IN VARCHAR2
1518 ,p_output_value OUT NOCOPY VARCHAR2
1519 ) RETURN NUMBER
1520 IS
1521 l_proc varchar2(72) := g_package||'.short_time_hours_data_ele_val';
1522 l_return NUMBER := 0;
1523 -- ----------------------------------------------------------------------------
1524 -- |---------------------------< get_effective_date >--------------------------|
1525 -- Description:
1526 -- ----------------------------------------------------------------------------
1527 FUNCTION get_effective_date
1528 (
1529 p_output_value OUT NOCOPY VARCHAR2
1530 )RETURN NUMBER
1531 IS
1532 l_proc varchar2(72) := g_package||'.get_effective_date';
1533 l_return NUMBER := 0;
1534 BEGIN
1535 debug_enter(l_proc);
1536
1537 IF g_effective_date IS NULL THEN
1538 p_output_value := NULL;
1539 debug('ERROR: No Claim Date for the element entry');
1540 l_return := PQP_GB_PSI_FUNCTIONS.raise_extract_error
1541 (p_error_number => 94507
1542 ,p_error_text => 'BEN_94507_NO_CLAIM_DATE_SIN'
1543 ,p_token1 => SUBSTR(g_curr_element_type_name
1544 ||'('||ben_ext_person.g_chg_surrogate_key||')',1,80)
1545 );
1546 ELSE
1547 p_output_value := to_char(g_effective_date,'dd/mm/yyyy');
1548 END IF;
1549
1550 debug_exit(l_proc);
1551 return l_return;
1552 EXCEPTION
1553 WHEN others THEN
1554 IF SQLCODE <> hr_utility.hr_error_number
1555 THEN
1556 debug_others (l_proc, 10);
1557 IF g_debug
1558 THEN
1559 DEBUG ( 'Leaving: '
1560 || l_proc, -999);
1561 END IF;
1562 fnd_message.raise_error;
1563 ELSE
1564 RAISE;
1565 END IF;
1566 END get_effective_date;
1567 ---
1568 -- ----------------------------------------------------------------------------
1569 -- |----------------------------< get_start_date >-----------------------------|
1570 -- Description:
1571 -- ----------------------------------------------------------------------------
1572 FUNCTION get_start_date
1573 (
1574 p_output_value OUT NOCOPY VARCHAR2
1575 )RETURN NUMBER
1576 IS
1577 l_proc varchar2(72) := g_package||'.get_start_date';
1578 l_return NUMBER := 0;
1579 l_curr_index NUMBER;
1580 BEGIN
1581 debug_enter(l_proc);
1582 l_curr_index := ben_ext_person.g_chg_pay_evt_index;
1583 IF l_curr_index = g_pay_proc_evt_tab.LAST - 1 THEN
1584 debug('Last event on the current person');
1585 IF g_start_date IS NULL THEN
1586 p_output_value := NULL;
1587 --raise error
1588 l_return := PQP_GB_PSI_FUNCTIONS.raise_extract_error
1589 (p_error_number => 94509
1590 ,p_error_text => 'BEN_94509_NO_START_END_DATE'
1591 );
1592 ELSE
1593 p_output_value := to_char(g_start_date,'dd/mm/yyyy');
1594 END IF;
1595 ELSE
1596 IF g_start_date IS NULL THEN
1597 p_output_value := NULL;
1598 ELSE
1599 p_output_value := to_char(g_start_date,'dd/mm/yyyy');
1600 END IF;
1601 END IF;
1602
1603 debug_exit(l_proc);
1604 return l_return;
1605 EXCEPTION
1606 WHEN others THEN
1607 IF SQLCODE <> hr_utility.hr_error_number
1608 THEN
1609 debug_others (l_proc, 10);
1610 IF g_debug
1611 THEN
1612 DEBUG ( 'Leaving: '
1613 || l_proc, -999);
1614 END IF;
1615 fnd_message.raise_error;
1616 ELSE
1617 RAISE;
1618 END IF;
1619 END get_start_date;
1620 ---
1621 -- ----------------------------------------------------------------------------
1622 -- |----------------------------< get_end_date >-----------------------------|
1623 -- Description:
1624 -- ----------------------------------------------------------------------------
1625 FUNCTION get_end_date
1626 (
1627 p_output_value OUT NOCOPY VARCHAR2
1628 )RETURN NUMBER
1629 IS
1630 l_proc varchar2(72) := g_package||'.get_end_date';
1631 l_return NUMBER := 0;
1632 l_curr_index NUMBER;
1633 BEGIN
1634 debug_enter(l_proc);
1635
1636 l_curr_index := ben_ext_person.g_chg_pay_evt_index;
1637
1638 IF g_end_date IS NULL THEN
1639 p_output_value := NULL;
1640 ELSE
1641 p_output_value := to_char(g_end_date,'dd/mm/yyyy');
1642 END IF;
1643
1644 debug_exit(l_proc);
1645 return l_return;
1646 EXCEPTION
1647 WHEN others THEN
1648 IF SQLCODE <> hr_utility.hr_error_number
1649 THEN
1650 debug_others (l_proc, 10);
1651 IF g_debug
1652 THEN
1653 DEBUG ( 'Leaving: '
1654 || l_proc, -999);
1655 END IF;
1656 fnd_message.raise_error;
1657 ELSE
1658 RAISE;
1659 END IF;
1660 END get_end_date;
1661 ---
1662 -- ----------------------------------------------------------------------------
1663 -- |----------------------------< get_adjusted_hours >-----------------------------|
1664 -- Description:
1665 -- ----------------------------------------------------------------------------
1666 FUNCTION get_adjusted_hours
1667 (
1668 p_output_value OUT NOCOPY VARCHAR2
1669 )RETURN NUMBER
1670 IS
1671 l_proc varchar2(72) := g_package||'.get_adjusted_hours';
1672 l_return NUMBER := 0;
1673 l_curr_index NUMBER;
1674 -- ----------------------------------------------------------------------------
1675 -- |----------------------------< format_adj_hours >-----------------------------|
1676 -- Description:
1677 -- ----------------------------------------------------------------------------
1678 FUNCTION format_adj_hours RETURN VARCHAR2
1679 IS
1680 l_proc varchar2(72) := g_package||'.format_adj_hours';
1681 l_return VARCHAR2(12) := '';
1682 BEGIN
1683 debug_enter(l_proc);
1684
1685 -- round the value of g_adjusted_hours
1686 IF g_adjusted_hours IS NULL THEN
1687 debug_exit(l_proc);
1688 RETURN l_return;
1689 END IF;
1690 debug('g_adjusted_hours before rounding: '||g_adjusted_hours);
1691 -- g_adjusted_hours := round(g_adjusted_hours,2);
1692 -- Bug fix 5152505
1693 -- adjusted hours is rounded off to the nearest quarter.
1694 g_adjusted_hours := pqp_utilities.round_value_up_down
1695 (p_value_to_round => g_adjusted_hours
1696 ,p_base_value => 0.25
1697 ,p_rounding_type => 'NEAREST'
1698 );
1699 debug('g_adjusted_hours after rounding: '||g_adjusted_hours);
1700
1701 -- format it to S09999999.99
1702
1703 l_return := to_char(g_adjusted_hours,'S09999999D99');
1704 --l_return := g_adjusted_hours;
1705 /*IF g_adjusted_hours = 0 THEN
1706 l_return := '0.00';
1707 ELSE
1708 l_return := g_adjusted_hours;
1709 END IF;
1710
1711 IF g_adjusted_hours >= 0 THEN
1712 l_return := '+'||LPAD(l_return,11,'0');
1713 ELSE
1714 l_return := '-'||LPAD(SUBSTR(l_return,2),11,'0');
1715 END IF;*/
1716
1717 debug('l_return after formatting: '||l_return);
1718 debug_exit(l_proc);
1719 return l_return;
1720 EXCEPTION
1721 WHEN others THEN
1722 IF SQLCODE <> hr_utility.hr_error_number
1723 THEN
1724 debug_others (l_proc, 10);
1725 IF g_debug
1726 THEN
1727 DEBUG ( 'Leaving: '
1728 || l_proc, -999);
1729 END IF;
1730 fnd_message.raise_error;
1731 ELSE
1732 RAISE;
1733 END IF;
1734 END format_adj_hours;
1735 ---
1736 BEGIN
1737 debug_enter(l_proc);
1738
1739 l_curr_index := ben_ext_person.g_chg_pay_evt_index;
1740
1741 IF l_curr_index = g_pay_proc_evt_tab.LAST - 1
1742 AND g_current_layout = 'ACCUMULATED' THEN
1743 debug('Last event on the current person');
1744 IF g_adjusted_hours IS NULL THEN
1745 p_output_value := NULL;
1746 --raise error
1747 l_return := PQP_GB_PSI_FUNCTIONS.raise_extract_error
1748 (p_error_number => 94510
1749 ,p_error_text => 'BEN_94510_NO_ADJ_HRS_ACC'
1750 );
1751 ELSE
1752 p_output_value := format_adj_hours();
1753 END IF;
1754 ELSE
1755 IF g_adjusted_hours IS NULL THEN
1756 p_output_value := NULL;
1757 ELSE
1758 p_output_value := format_adj_hours();
1759 END IF;
1760 END IF;
1761 IF g_current_layout = 'SINGLE' AND p_output_value IS NULL THEN
1762 -- raise error
1763 l_return := PQP_GB_PSI_FUNCTIONS.raise_extract_error
1764 (p_error_number => 94511
1765 ,p_error_text => 'BEN_94511_NO_ADJ_HRS_SIN'
1766 ,p_token1 => SUBSTR(g_curr_element_type_name
1767 ||'('||ben_ext_person.g_chg_surrogate_key||')',1,80)
1768 );
1769 END IF;
1770 debug_exit(l_proc);
1771 return l_return;
1772 END get_adjusted_hours;
1773 ---
1774 BEGIN
1775 debug_enter(l_proc);
1776 debug('p_ext_user_value: '||p_ext_user_value,10);
1777 debug('------------------------------------------');
1778 debug('g_start_date: '||g_start_date,10);
1779 debug('g_end_date: '||g_end_date,10);
1780 debug('g_effective_date: '||g_effective_date,10);
1781 debug('g_adjusted_hours: '||g_adjusted_hours,10);
1782 debug('------------------------------------------');
1783 IF p_ext_user_value = 'StartDate' THEN
1784 l_return := get_start_date
1785 (
1786 p_output_value => p_output_value
1787 );
1788 ELSIF p_ext_user_value = 'EndDate' THEN
1789 l_return := get_end_date
1790 (
1791 p_output_value => p_output_value
1792 );
1793 ELSIF p_ext_user_value = 'HoursVariation' THEN
1794 l_return := get_adjusted_hours
1795 (
1796 p_output_value => p_output_value
1797 );
1798 ELSIF p_ext_user_value = 'EffectiveDate' THEN
1799 l_return := get_effective_date
1800 (
1801 p_output_value => p_output_value
1802 );
1803 END IF;
1804
1805 debug_exit(l_proc);
1806 return l_return;
1807 EXCEPTION
1808 WHEN others THEN
1809 IF SQLCODE <> hr_utility.hr_error_number
1810 THEN
1811 debug_others (l_proc, 10);
1812 IF g_debug
1813 THEN
1814 DEBUG ( 'Leaving: '
1815 || l_proc, -999);
1816 END IF;
1817 fnd_message.raise_error;
1818 ELSE
1819 RAISE;
1820 END IF;
1821 END short_time_hours_data_ele_val;
1822 ---
1823 -- ----------------------------------------------------------------------------
1824 -- |----------------------< short_time_hours_claim_date >--------------------------|
1825 -- Description:
1826 -- ----------------------------------------------------------------------------
1827 FUNCTION short_time_hours_claim_date
1828 (
1829 p_ext_user_value IN VARCHAR2
1830 ,p_output_value OUT NOCOPY VARCHAR2
1831 ) RETURN NUMBER
1832 IS
1833 l_proc varchar2(72) := g_package||'.short_time_hours_claim_date';
1834 l_output_value VARCHAR2(10);
1835 l_return NUMBER;
1836 BEGIN
1837 debug_enter(l_proc);
1838
1839 l_return := short_time_hours_data_ele_val
1840 (
1841 p_ext_user_value => p_ext_user_value
1842 ,p_output_value => l_output_value
1843 );
1844
1845 p_output_value := to_date(l_output_value,'dd/mm/yyyy');
1846 debug('l_output_value: '||l_output_value);
1847 debug('p_output_value: '||p_output_value);
1848 debug_exit(l_proc);
1849 return l_return;
1850 EXCEPTION
1851 WHEN others THEN
1852 IF SQLCODE <> hr_utility.hr_error_number
1853 THEN
1854 debug_others (l_proc, 10);
1855 IF g_debug
1856 THEN
1857 DEBUG ( 'Leaving: '
1858 || l_proc, -999);
1859 END IF;
1860 fnd_message.raise_error;
1861 ELSE
1862 RAISE;
1863 END IF;
1864 END short_time_hours_claim_date;
1865 ------
1866 -- ----------------------------------------------------------------------------
1867 -- |----------------------< short_time_hours_post_proc >--------------------------|
1868 -- Description: This is the post-processing rule for the Short-Time Hours History.
1869 -- ----------------------------------------------------------------------------
1870 FUNCTION short_time_hours_post_proc RETURN VARCHAR2
1871 IS
1872 l_proc varchar2(72) := g_package||'.short_time_hours_post_proc';
1873 BEGIN
1874 debug_enter(l_proc);
1875
1876 --Raise extract exceptions which are stored while processing the data elements
1877 --PQP_GB_PSI_FUNCTIONS.raise_extract_exceptions();
1878
1879 PQP_GB_PSI_FUNCTIONS.common_post_process(g_business_group_id);
1880
1881 debug_exit(l_proc);
1882 return 'Y';
1883 EXCEPTION
1884 WHEN others THEN
1885 IF SQLCODE <> hr_utility.hr_error_number
1886 THEN
1887 debug_others (l_proc, 10);
1888 IF g_debug
1889 THEN
1890 DEBUG ( 'Leaving: '
1891 || l_proc, -999);
1892 END IF;
1893 fnd_message.raise_error;
1894 ELSE
1895 RAISE;
1896 END IF;
1897 END short_time_hours_post_proc;
1898 ------
1899 END PQP_GB_PSI_STH_HISTORY;