[Home] [Help]
PACKAGE BODY: APPS.PQP_BUDGET_MAINTENANCE
Source
1 PACKAGE BODY pqp_budget_maintenance AS
2 /* $Header: pqabvmaintain.pkb 120.14 2012/01/19 07:26:29 rpahune ship $ */
3 g_package_name VARCHAR2(31) := 'pqp_budget_maintenance.';
4 g_debug BOOLEAN := hr_utility.debug_enabled;
5 hr_application_error EXCEPTION;
6 PRAGMA EXCEPTION_INIT (hr_application_error, -20001);
7 -- output record structure
8 TYPE g_output_file_rec_type IS RECORD(
9 assignment_id per_all_assignments_f.assignment_id%TYPE
10 ,status VARCHAR2(80)
11 ,uom VARCHAR2(80)
12 ,employee_number per_all_people_f.employee_number%TYPE
13 ,assignment_number per_all_assignments_f.assignment_number%TYPE
14 ,effective_date per_all_assignments_f.effective_start_date%TYPE
15 ,old_budget_value per_assignment_budget_values_f.VALUE%TYPE
16 ,change_type VARCHAR2(80)
17 ,new_budget_value per_assignment_budget_values_f.VALUE%TYPE
18 ,MESSAGE fnd_new_messages.MESSAGE_TEXT%TYPE
19 );
20
21 TYPE t_output_file_record_type IS TABLE OF g_output_file_rec_type
22 INDEX BY BINARY_INTEGER;
23
24 g_output_file_records t_output_file_record_type; -- do not include in clear cache
25 g_column_separator VARCHAR2(10) := ' , ';
26 -- global Variables for concurrent program
27 g_person_id NUMBER;
28 g_formula_id NUMBER;
29 g_assignment_set_id NUMBER;
30 g_parameter_list pay_payroll_actions.legislative_parameters%TYPE;
31 g_uom VARCHAR2(30);
32 g_action VARCHAR2(30);
33 g_effective_date DATE;
34 g_payroll_id NUMBER;
35 g_contract pqp_assignment_attributes_f.contract_type%TYPE;
36 -- global variables for storing configuration values
37 g_configuration_data csr_get_configuration_data%ROWTYPE;
38 g_additional_information csr_get_configuration_data%ROWTYPE;
39 -- global variables for legislative_data
40 g_business_group_id per_business_groups.business_group_id%TYPE;
41 g_legislation_code per_business_groups.legislation_code%TYPE;
42 -- cache for configuration value ids
43 g_defn_configuration_id pqp_configuration_values.configuration_value_id%TYPE;
44 g_additional_config_id pqp_configuration_values.configuration_value_id%TYPE;
45 g_not_cached_constants BOOLEAN;
46 g_is_concurrent_program_run BOOLEAN := FALSE;
47 -- global for printing header of the output file
48 g_is_header_printed BOOLEAN :=FALSE;
49
50 CURSOR get_business_group_id(p_assignment_id NUMBER)
51 IS
52 SELECT business_group_id
53 FROM per_all_assignments_f
54 WHERE assignment_id = p_assignment_id;
55
56 --
57 --
58 CURSOR get_legislation_code(p_business_group_id NUMBER)
59 IS
60 SELECT legislation_code
61 FROM per_business_groups
62 WHERE business_group_id = p_business_group_id;
63
64 PROCEDURE debug(
65 p_trace_message IN VARCHAR2
66 ,p_trace_location IN NUMBER DEFAULT NULL
67 )
68 IS
69 BEGIN
70 --
71 IF NOT g_is_concurrent_program_run
72 THEN
73 pqp_utilities.debug(p_trace_message, p_trace_location);
74 ELSE
75 IF p_trace_location IS NULL
76 THEN
77 fnd_file.put_line(fnd_file.LOG, p_trace_message);
78 ELSE
79 fnd_file.put_line(fnd_file.LOG
80 , RPAD(p_trace_message, 80, ' ')
81 || TO_CHAR(p_trace_location)
82 );
83 END IF;
84 END IF;
85 END DEBUG;
86
87 PROCEDURE debug_enter(
88 p_proc_name IN VARCHAR2
89 ,p_trace_on IN VARCHAR2 DEFAULT NULL
90 )
91 IS
92 BEGIN
93 IF NOT g_is_concurrent_program_run
94 THEN
95 pqp_utilities.debug_enter(p_proc_name, p_trace_on);
96 ELSE
97 fnd_file.put_line(fnd_file.LOG, RPAD(p_proc_name, 80, ' ') || '+0');
98 END IF;
99 END debug_enter;
100
101 PROCEDURE debug_exit(
102 p_proc_name IN VARCHAR2
103 ,p_trace_off IN VARCHAR2 DEFAULT NULL
104 )
105 IS
106 BEGIN
107 IF NOT g_is_concurrent_program_run
108 THEN
109 pqp_utilities.debug_exit(p_proc_name, p_trace_off);
110 ELSE
111 fnd_file.put_line(fnd_file.LOG, RPAD(p_proc_name, 80, ' ') || '-0');
112 END IF;
113 END debug_exit;
114
115 PROCEDURE debug_others(
116 p_proc_name IN VARCHAR2
117 ,p_proc_step IN NUMBER DEFAULT NULL
118 )
119 IS
120 BEGIN
121 pqp_utilities.debug_others(p_proc_name, p_proc_step);
122 END debug_others;
123
124 PROCEDURE clear_cache
125 IS
126 BEGIN
127 --
128 -- cache for get_installation_status
129 -- g_application_id := NULL;
130 -- g_status := NULL;
131 --
132 -- cache for concurrent process
133 g_parameter_list := NULL;
134 g_person_id := NULL;
135 g_formula_id := NULL;
136 g_assignment_set_id := NULL;
137 g_uom := NULL;
138 g_action := NULL;
139 g_effective_date := NULL;
140 g_payroll_id := NULL;
141 g_contract := NULL;
142 g_tab_asg_set_amnds.DELETE;
143 -- cache for legislative data
144 g_business_group_id := NULL;
145 g_legislation_code := NULL;
146 --cache for configuration id
147 g_defn_configuration_id := NULL;
148 g_additional_config_id := NULL;
149 -- cache for configuration value
150 g_configuration_data := NULL;
151 g_additional_information := NULL;
152 -- cache for load_cached_constants
153 g_not_cached_constants := TRUE;
154 END clear_cache;
155
156 ----------------------------------------------------------------------
157 --------PROCEDURE FOR LOAD CACHE-----------------------------------
158 ---------------------------------------------------------------------
159 PROCEDURE load_cache(
160 p_uom IN VARCHAR2
161 ,p_business_group_id IN NUMBER
162 ,p_legislation_code IN VARCHAR2
163 ,p_information_category IN VARCHAR2
164 ,p_configuration_data IN OUT NOCOPY csr_get_configuration_data%ROWTYPE
165 )
166 IS
167 l_log_string VARCHAR2(4000);
168 l_proc_step NUMBER(20, 10) := 0;
169 l_proc_name VARCHAR2(61) := g_package_name || 'load_cache';
170 BEGIN
171 IF g_debug
172 THEN
173 debug_enter(l_proc_name);
174 debug('p_uom: ' || p_uom);
175 debug('p_business_group_id: ' || p_business_group_id);
176 debug('p_legislation_code: ' || p_legislation_code);
177 debug('p_information_category: ' || p_information_category);
178 END IF;
179
180 -- fetch the required configuration data for PQP_ABVM_DEFINITION
181
182 OPEN csr_get_configuration_data(p_uom => p_uom
183 ,p_business_group_id => p_business_group_id
184 ,p_legislation_code => p_legislation_code
185 ,p_information_category => p_information_category
186 );
187 FETCH csr_get_configuration_data INTO p_configuration_data;
188 CLOSE csr_get_configuration_data;
189
190 IF g_debug
191 THEN
192 debug_exit(l_proc_name);
193 END IF;
194 EXCEPTION
195 WHEN OTHERS
196 THEN
197 clear_cache;
198
199 IF SQLCODE <> hr_utility.hr_error_number
200 THEN
201 debug_others(l_proc_name, l_proc_step);
202
203 IF g_debug
204 THEN
205 debug('Leaving: ' || l_proc_name, -999);
206 END IF;
207
208 hr_utility.raise_error;
209 ELSE
210 RAISE;
211 END IF;
212 END load_cache;
213
214 /* ----------------------------------------------------------- */
215 /* --------------------- Load Cache--------------------------- */
216 /* ----------------------------------------------------------- */
217 PROCEDURE load_cache(p_payroll_action_id IN NUMBER)
218 IS
219 BEGIN
220 -- initialise globals to null before reloading
221 g_parameter_list := NULL;
222 g_uom := NULL;
223 g_action := NULL;
224 g_effective_date := NULL;
225 g_business_group_id := NULL;
226 g_payroll_id := NULL;
227 g_contract := NULL;
228 GET_PARAMETER_LIST(p_pay_action_id => p_payroll_action_id -- IN
229 ,p_parameter_list => g_parameter_list -- OUT
230 );
231 g_uom :=
232 get_parameter_value('UOM', g_parameter_list);
233 g_action :=
234 get_parameter_value('ACTION', g_parameter_list);
235 g_effective_date :=
236 fnd_date.canonical_to_date(get_parameter_value('EFFECTIVE DATE'
237 ,g_parameter_list
238 )
239 );
240 g_business_group_id := fnd_profile.VALUE('PER_BUSINESS_GROUP_ID');
241 g_effective_date :=
242 fnd_date.canonical_to_date(get_parameter_value('EFFECTIVE DATE'
243 ,g_parameter_list
244 )
245 );
246 g_payroll_id :=
247 get_parameter_value('PAYROLL', g_parameter_list);
248 g_contract :=
249 get_parameter_value('CONTRACT', g_parameter_list);
250 g_not_cached_constants := FALSE;
251 END load_cache;
252
253 ------------------------------------------------------------
254 --------CONVERT_RECORD_TO_OUTPUTSTRING----------------------
255 ------------------------------------------------------------
256 FUNCTION convert_record_to_outputstring(
257 p_output_file_record g_output_file_rec_type
258 )
259 RETURN VARCHAR2
260 IS
261 l_proc_step NUMBER(20, 10):= 0;
262 l_proc_name VARCHAR2(61)
263 := g_package_name || 'convert_record_to_outputstring';
264 l_outputstring VARCHAR2(4000);
265 BEGIN -- convert_record_to_outputstring
266 IF g_debug
267 THEN
268 debug_enter(l_proc_name);
269 END IF;
270
271 l_outputstring :=
272 RPAD(NVL(p_output_file_record.status, ' '), 30, ' ')
273 || g_column_separator
274 || RPAD(NVL(p_output_file_record.uom, ' '), 30, ' ')
275 || g_column_separator
276 || RPAD(NVL(p_output_file_record.employee_number, ' '), 20, ' ')
277 || g_column_separator
278 || RPAD(NVL(p_output_file_record.assignment_number
279 , 'AsgId:' || p_output_file_record.assignment_id
280 )
281 ,30
282 ,' '
283 )
284 || g_column_separator
285 || RPAD(NVL(fnd_date.date_to_displaydate(p_output_file_record.effective_date
286 )
287 ,' '
288 )
289 ,15
290 ,' '
291 )
292 || g_column_separator
293 || RPAD(NVL(TO_CHAR(p_output_file_record.old_budget_value), ' ')
294 ,30
295 ,' '
296 )
297 || g_column_separator
298 || RPAD(NVL(p_output_file_record.change_type, ' '), 15, ' ')
299 || g_column_separator
300 || RPAD(NVL(TO_CHAR(p_output_file_record.new_budget_value), ' ')
301 ,30
302 ,' '
303 )
304 || g_column_separator
305 || RPAD(p_output_file_record.MESSAGE, 400, ' ');
306
307 IF g_debug
308 THEN
309 debug_exit(l_proc_name);
310 debug('l_outputstring_1_200:' || SUBSTR(l_outputstring, 1, 200));
311 debug('l_outputstring_201_400:' || SUBSTR(l_outputstring, 201, 200));
312 debug('l_outputstring_401_600:' || SUBSTR(l_outputstring, 401, 600));
313 debug('l_outputstring_601_800:' || SUBSTR(l_outputstring, 601, 800));
314 END IF;
315
316 RETURN l_outputstring;
317 EXCEPTION
318 WHEN OTHERS
319 THEN
320 clear_cache;
321
322 IF SQLCODE <> hr_utility.hr_error_number
323 THEN
324 debug_others(l_proc_name, l_proc_step);
325
326 IF g_debug
327 THEN
328 debug('Leaving: ' || l_proc_name, -999);
329 END IF;
330
331 hr_utility.raise_error;
332 ELSE
333 RAISE;
334 END IF;
335 END convert_record_to_outputstring;
336
337 ---------------------------------------------------------------------
338 ----------------WRITE_OUTPUT_FILE_RECORDS----------------------------
339 ---------------------------------------------------------------------
340 PROCEDURE write_output_file_records
341 IS
342 l_proc_step NUMBER(20, 10):= 0;
343 l_proc_name VARCHAR2(61)
344 := g_package_name || 'write_output_file_records';
345 i BINARY_INTEGER;
346 BEGIN -- write_output_file_records
347 IF g_debug
348 THEN
349 debug_enter(l_proc_name);
350 END IF;
351 -- prepare output file header
352 --
353 IF NOT g_is_header_printed THEN
354 fnd_file.put_line(fnd_file.output
355 , RPAD('Status', 30, ' ')
356 || g_column_separator
357 || RPAD('UOM', 30, ' ')
358 || g_column_separator
359 || RPAD('Employee Number', 20, ' ')
360 || g_column_separator
361 || RPAD('Assignment_Number', 30, ' ')
362 || g_column_separator
363 || RPAD('Effective Date', 15, ' ')
364 || g_column_separator
365 || RPAD('Budget Value - Before Change', 30, ' ')
366 || g_column_separator
367 || RPAD('Change Type', 15, ' ')
368 || g_column_separator
369 || RPAD('Budget Value - After Change', 30, ' ')
370 || g_column_separator
371 || RPAD('Message', 400, ' ')
372 );
373 fnd_file.put_line(fnd_file.output
374 , RPAD('-', 30, '-')
375 || g_column_separator
376 || RPAD('-', 30, '-')
377 || g_column_separator
378 || RPAD('-', 20, '-')
379 || g_column_separator
380 || RPAD('-', 30, '-')
381 || g_column_separator
382 || RPAD('-', 15, '-')
383 || g_column_separator
384 || RPAD('-', 30, '-')
385 || g_column_separator
386 || RPAD('-', 15, '-')
387 || g_column_separator
388 || RPAD('-', 30, '-')
389 || g_column_separator
390 || RPAD('-', 400, '-')
391 );
392 g_is_header_printed := TRUE;
393
394 END IF;
395
396 i := g_output_file_records.FIRST;
397
398 WHILE i IS NOT NULL
399 LOOP
400 fnd_file.put_line(fnd_file.output
401 ,convert_record_to_outputstring(g_output_file_records(i
402 )
403 )
404 );
405 i := g_output_file_records.NEXT(i);
406 END LOOP;
407
408 IF g_debug
409 THEN
410 debug_exit(l_proc_name);
411 END IF;
412 EXCEPTION
413 WHEN OTHERS
414 THEN
415 clear_cache;
416
417 IF SQLCODE <> hr_utility.hr_error_number
418 THEN
419 debug_others(l_proc_name, l_proc_step);
420
421 IF g_debug
422 THEN
423 debug('Leaving: ' || l_proc_name, -999);
424 END IF;
425
426 hr_utility.raise_error;
427 ELSE
428 RAISE;
429 END IF;
430 END write_output_file_records;
431
432 ------------------------------------------------------------
433 --------------------- Get Parameter List -------------------
434 ------------------------------------------------------------
435 PROCEDURE GET_PARAMETER_LIST(
436 p_pay_action_id IN NUMBER
437 ,p_parameter_list OUT NOCOPY VARCHAR2
438 )
439 IS
440 --
441 CURSOR csr_get_param_string
442 IS
443 SELECT legislative_parameters
444 FROM pay_payroll_actions
445 WHERE payroll_action_id = p_pay_action_id;
446
447 l_proc_step NUMBER(38, 10) := 0;
448 l_proc_name VARCHAR2(61)
449 := g_package_name || 'get_parameter_list';
450 l_parameter_list pay_payroll_actions.legislative_parameters%TYPE;
451 --
452 BEGIN
453 --
454 IF g_debug
455 THEN
456 debug_enter(l_proc_name);
457 debug('p_pay_action_id: ' || p_pay_action_id);
458 END IF;
459
460 l_proc_step := 10;
461 l_parameter_list := NULL;
462 -- Get the parameter list from legislative parameters
463 -- for this payroll action id
464
465 OPEN csr_get_param_string;
466 FETCH csr_get_param_string INTO l_parameter_list;
467 CLOSE csr_get_param_string;
468 p_parameter_list := l_parameter_list;
469 l_proc_step := 20;
470
471 IF g_debug
472 THEN
473 debug_exit(l_proc_name);
474 END IF;
475 EXCEPTION
476 WHEN OTHERS
477 THEN
478 clear_cache;
479
480 IF SQLCODE <> hr_utility.hr_error_number
481 THEN
482 debug_others(l_proc_name, l_proc_step);
483
484 IF g_debug
485 THEN
486 debug('Leaving: ' || l_proc_name, -999);
487 END IF;
488
489 hr_utility.raise_error;
490 ELSE
491 RAISE;
492 END IF;
493 --
494 END GET_PARAMETER_LIST;
495
496 /* ------------------------------------------------------------ */
497 /* --------------------- Get Parameter Value ------------------ */
498 /* ------------------------------------------------------------ */
499 FUNCTION get_parameter_value(
500 p_string IN VARCHAR2
501 ,p_parameter_list IN VARCHAR2
502 )
503 RETURN VARCHAR2
504 IS
505 --
506
507 l_proc_step NUMBER(38, 10) := 0;
508 l_proc_name VARCHAR2(61) := g_package_name || 'get_parameter_value';
509 l_start_ptr NUMBER;
510 l_end_ptr NUMBER;
511 l_token_val pay_payroll_actions.legislative_parameters%TYPE;
512 l_par_value pay_payroll_actions.legislative_parameters%TYPE;
513 --
514 BEGIN
515 --
516 IF g_debug
517 THEN
518 debug_enter(l_proc_name);
519 debug('p_string: ' || p_string);
520 debug('p_parameter_list: ' || p_parameter_list);
521 END IF;
522
523 l_proc_step := 10;
524
525 IF g_debug
526 THEN
527 debug(l_proc_name, l_proc_step);
528 END IF;
529
530 l_token_val := p_string || '="';
531
532 --Modifications for the bug 13332282 starts here
533 IF (p_string = 'EFFECTIVE DATE') THEN
534 l_token_val := 'FTE ';
535 l_start_ptr :=
536 INSTR(p_parameter_list, l_token_val)
537 + LENGTH(l_token_val);
538 l_end_ptr := l_start_ptr + 19;
539 ELSE
540 l_start_ptr :=
541 INSTR(p_parameter_list, l_token_val)
542 + LENGTH(l_token_val);
543 l_end_ptr := INSTR(p_parameter_list, '"', l_start_ptr);
544 END IF;
545 --Modifications for the bug 13332282 ends here
546
547 IF l_end_ptr = 0
548 THEN
549 l_end_ptr := LENGTH(p_parameter_list) + 1;
550 END IF;
551
552 l_proc_step := 20;
553
554 IF g_debug
555 THEN
556 debug(l_proc_name, l_proc_step);
557 debug('Start Ptr: ' || l_start_ptr);
558 debug('End Ptr: ' || l_end_ptr);
559 END IF;
560
561 IF INSTR(p_parameter_list, l_token_val) = 0
562 THEN
563 l_par_value := NULL;
564 -- dbms_output.put_line('par_value: '||par_value);
565 ELSE
566 l_par_value :=
567 SUBSTR(p_parameter_list, l_start_ptr
568 ,(l_end_ptr - l_start_ptr));
569 -- dbms_output.put_line('par_value: '||par_value);
570 END IF;
571
572 l_proc_step := 30;
573
574 IF g_debug
575 THEN
576 debug('l_par_value: ' || l_par_value);
577 debug_exit(l_proc_name);
578 END IF;
579
580 RETURN l_par_value;
581 EXCEPTION
582 WHEN OTHERS
583 THEN
584 clear_cache;
585
586 IF SQLCODE <> hr_utility.hr_error_number
587 THEN
588 debug_others(l_proc_name, l_proc_step);
589
590 IF g_debug
591 THEN
592 debug('Leaving: ' || l_proc_name, -999);
593 END IF;
594
595 hr_utility.raise_error;
596 ELSE
597 RAISE;
598 END IF;
599 --
600 END get_parameter_value;
601
602 ------------------------------------------------------------
603 --------------------- Get Assignment Set Details -----------
604 ------------------------------------------------------------
605 PROCEDURE get_asg_set_details(
606 p_assignment_set_id IN NUMBER
607 ,p_formula_id OUT NOCOPY NUMBER
608 ,p_tab_asg_set_amnds OUT NOCOPY t_asg_set_amnds
609 )
610 IS
611 --
612 -- Cursor to get information about assignment set
613 CURSOR csr_get_asg_set_info(c_asg_set_id NUMBER)
614 IS
615 SELECT formula_id
616 FROM hr_assignment_sets ags
617 WHERE assignment_set_id = c_asg_set_id
618 AND EXISTS(SELECT 1
619 FROM hr_assignment_set_criteria agsc
620 WHERE agsc.assignment_set_id = ags.assignment_set_id);
621
622 -- Cursor to get assignment ids from asg set amendments
623 CURSOR csr_get_asg_amnd(c_asg_set_id NUMBER)
624 IS
625 SELECT assignment_id, NVL(include_or_exclude
626 ,'I') include_or_exclude
627 FROM hr_assignment_set_amendments
628 WHERE assignment_set_id = c_asg_set_id;
629
630 l_proc_step NUMBER(38, 10) := 0;
631 l_proc_name VARCHAR2(61)
632 := g_package_name || 'get_asg_set_details';
633 l_asg_set_amnds csr_get_asg_amnd%ROWTYPE;
634 l_tab_asg_set_amnds t_asg_set_amnds;
635 l_formula_id NUMBER;
636 --
637 BEGIN
638 --
639 IF g_debug
640 THEN
641 debug_enter(l_proc_name);
642 debug('p_assignment_set_id: ' || p_assignment_set_id);
643 END IF;
644
645 l_proc_step := 10;
646 -- Check whether the assignment set id has a criteria
647 -- if a formula id is attached or check whether this
648 -- is an amendments only
649
650
651 l_formula_id := NULL;
652 OPEN csr_get_asg_set_info(p_assignment_set_id);
653 FETCH csr_get_asg_set_info INTO l_formula_id;
654
655 IF g_debug
656 THEN
657 debug(l_proc_name, l_proc_step);
658 debug('l_formula_id: ' || l_formula_id);
659 END IF;
660
661 IF csr_get_asg_set_info%FOUND
662 THEN
663 -- Criteria exists check for formula id
664 IF l_formula_id IS NULL
665 THEN
666 -- Raise error as the criteria is not generated
667 hr_utility.set_message(8303, 'PQP_230458_ABV_ASGSET_NO_FMLA');
668 fnd_file.put_line(fnd_file.LOG
669 , RPAD('Error', 30) || ': ' || hr_utility.get_message
670 );
671 fnd_file.put_line(fnd_file.LOG, ' ');
672 l_proc_step := 20;
673
674 IF g_debug
675 THEN
676 debug(l_proc_name, l_proc_step);
677 debug('Error: ' || hr_utility.get_message);
678 END IF;
679
680 CLOSE csr_get_asg_set_info;
681 hr_utility.raise_error;
682 END IF; -- End if of formula id is null check ...
683 END IF; -- End if of asg criteria row found check ...
684
685 CLOSE csr_get_asg_set_info;
686 l_proc_step := 30;
687 OPEN csr_get_asg_amnd(p_assignment_set_id);
688 LOOP
689 FETCH csr_get_asg_amnd INTO l_asg_set_amnds;
690 EXIT WHEN csr_get_asg_amnd%NOTFOUND;
691 l_tab_asg_set_amnds(l_asg_set_amnds.assignment_id) :=
692 l_asg_set_amnds.include_or_exclude;
693
694 IF g_debug
695 THEN
696 debug( 'l_tab_asg_set_amnds('
697 || l_asg_set_amnds.assignment_id
698 || '): '
699 || l_asg_set_amnds.include_or_exclude
700 );
701 END IF;
702 END LOOP;
703
704 CLOSE csr_get_asg_amnd;
705 p_formula_id := l_formula_id;
706 p_tab_asg_set_amnds := l_tab_asg_set_amnds;
707 l_proc_step := 40;
708
709 IF g_debug
710 THEN
711 debug('l_tab_asg_set_amnds.COUNT: ' || l_tab_asg_set_amnds.COUNT);
712 debug_exit(l_proc_name);
713 END IF;
714 EXCEPTION
715 WHEN OTHERS
716 THEN
717 clear_cache;
718
719 IF SQLCODE <> hr_utility.hr_error_number
720 THEN
721 debug_others(l_proc_name, l_proc_step);
722
723 IF g_debug
724 THEN
725 debug('Leaving: ' || l_proc_name, -999);
726 END IF;
727
728 hr_utility.raise_error;
729 ELSE
730 RAISE;
731 END IF;
732 --
733 END get_asg_set_details;
734
735 --
736 /* ------------------------------------------------------------ */
737 /* --------------------- Range Cursor ------------------------- */
738 /* ------------------------------------------------------------ */
739 PROCEDURE range_cursor(
740 p_pay_action_id IN NUMBER
741 ,p_sqlstr OUT NOCOPY VARCHAR2
742 )
743 IS
744 --
745 -- Cursor to check whether at least one amendment
746 -- has an inclusion
747 CURSOR csr_get_asg_amnd_incl(c_asg_set_id NUMBER)
748 IS
749 SELECT 'X'
750 FROM hr_assignment_set_amendments
751 WHERE assignment_set_id = c_asg_set_id
752 AND NVL(include_or_exclude, 'I') =
753 'I' -- hard coded as it's from lookup code
754 AND ROWNUM < 2;
755
756 l_proc_step NUMBER(38, 10) := 0;
757 l_proc_name VARCHAR2(61) := g_package_name || 'range_cursor';
758 l_person_id NUMBER;
759 l_assignment_id NUMBER;
760 l_assignment_set_id NUMBER;
761 l_string VARCHAR2(32000);
762 l_exists VARCHAR2(10);
763 l_formula_id NUMBER;
764 l_tab_asg_set_amnds t_asg_set_amnds;
765 --
766 BEGIN
767 --
768 IF g_debug
769 THEN
770 debug_enter(l_proc_name);
771 debug('p_pay_action_id: ' || p_pay_action_id);
772 END IF;
773
774 -- Initialize global variables
775
776 l_string := NULL;
777 g_person_id := NULL;
778 g_formula_id := NULL;
779 g_tab_asg_set_amnds.DELETE;
780 g_assignment_set_id := NULL;
781 l_formula_id := NULL;
782 l_person_id := NULL;
783 l_assignment_set_id := NULL;
784 g_business_group_id := NULL;
785 g_parameter_list := NULL;
786 g_uom := NULL;
787 g_action := NULL;
788 g_effective_date := NULL;
789 g_payroll_id := NULL;
790 g_contract := NULL;
791 -- Get business group id
792 g_business_group_id := fnd_profile.VALUE('PER_BUSINESS_GROUP_ID');
793 -- Get parameter list
794 l_proc_step := 10;
795
796 IF g_debug
797 THEN
798 debug(l_proc_name, l_proc_step);
799 END IF;
800
801 GET_PARAMETER_LIST(p_pay_action_id => p_pay_action_id -- IN
802 ,p_parameter_list => g_parameter_list -- OUT
803 );
804 -- Get person id from get_parameter_value
805 l_proc_step := 20;
806
807 IF g_debug
808 THEN
809 debug(l_proc_name, l_proc_step);
810 END IF;
811
812 l_person_id :=
813 get_parameter_value(p_string => 'PERSON' -- IN
814 ,p_parameter_list => g_parameter_list -- IN
815 );
816
817 IF g_uom IS NULL
818 THEN
819 -- load cache
820 l_proc_step := 25;
821 load_cache(p_payroll_action_id => p_pay_action_id);
822 END IF;
823
824 IF g_debug
825 THEN
826 debug(l_proc_name, l_proc_step);
827 debug('Person ID: ' || l_person_id);
828 debug('g_uom: ' || g_uom);
829 debug('g_action: ' || g_action);
830 debug('g_effective_date: ' || g_effective_date);
831 debug('g_business_group_id: ' || g_business_group_id);
832 debug('g_payroll_id: ' || g_payroll_id);
833 debug('g_contract: ' || g_contract);
834 END IF;
835
836 IF l_person_id IS NULL
837 THEN
838 l_string :=
839 'SELECT DISTINCT person_id FROM per_people_f ppf
840 ,pay_payroll_actions ppa
841 WHERE ppf.business_group_id = ppa.business_group_id
842 AND ppa.payroll_action_id = :payroll_action_id
843 ORDER BY ppf.person_id';
844 ELSE -- l_person_id IS NOT NULL
845 l_string :=
846 'SELECT DISTINCT person_id FROM per_people_f ppf
847 ,pay_payroll_actions ppa
848 WHERE ppf.business_group_id = ppa.business_group_id
849 AND ppa.payroll_action_id = :payroll_action_id
850 AND ppf.person_id = '
851 || l_person_id
852 || ' ORDER BY ppf.person_id';
853 -- Store the person id in a global variable
854 g_person_id := l_person_id;
855 END IF; -- End if of person id is null check ...
856
857 -- In addition to checks for person id
858 -- We may have to determine whether an assignment set
859 -- has been supplied
860
861 -- Get assignment set id from get_parameter_value
862
863 l_proc_step := 40;
864
865 IF g_debug
866 THEN
867 debug(l_proc_name, l_proc_step);
868 debug('l_string: ' || l_string);
869 debug('g_person_id: ' || g_person_id);
870 END IF;
871
872 l_assignment_set_id :=
873 get_parameter_value(p_string => 'ASSIGNMENT SET' -- IN
874 ,p_parameter_list => g_parameter_list -- IN
875 );
876
877 IF l_assignment_set_id IS NOT NULL
878 THEN
879 l_proc_step := 50;
880 g_assignment_set_id := l_assignment_set_id;
881 -- call local procedure to get assignment set details
882 get_asg_set_details(p_assignment_set_id => l_assignment_set_id
883 ,p_formula_id => l_formula_id
884 ,p_tab_asg_set_amnds => l_tab_asg_set_amnds
885 );
886
887 IF g_debug
888 THEN
889 debug(l_proc_name, l_proc_step);
890 debug('l_formula_id: ' || l_formula_id);
891 debug('l_tab_asg_set_amnds.COUNT: ', l_tab_asg_set_amnds.COUNT);
892 END IF;
893
894 l_proc_step := 60;
895 g_tab_asg_set_amnds := l_tab_asg_set_amnds;
896
897 IF l_formula_id IS NOT NULL
898 THEN
899 -- we will use the selective logic at assignment
900 -- action level, store the formula id in the
901 -- global variable
902 g_formula_id := l_formula_id;
903
904 IF g_debug
905 THEN
906 debug('g_formula_id: ' || g_formula_id);
907 END IF;
908 -- PS: If both are specified then we are not going
909 -- to modify the range cursor with the assignments in
910 -- the assignment set, this also applies to exclude only
911 -- amendments
912
913 -- Create a temporary table dynamically
914 -- to store all the person ids
915 -- drop the table before creating one
916
917
918 -- BEGIN
919 -- SELECT 'x' INTO l_exists
920 -- FROM pqp_person_id_temp
921 -- WHERE rownum < 2;
922 -- EXECUTE IMMEDIATE 'DROP TABLE pqp_person_id_temp';
923 -- IF g_debug THEN
924 -- debug(l_proc_name, l_proc_step);
925 -- debug('g_amendment_exits: '||g_amendment_exists);
926 -- END IF;
927 -- EXCEPTION
928 -- WHEN no_data_found THEN
929 -- null;
930 -- END;
931 -- l_proc_step := 80;
932 --
933 -- l_create_string := 'CREATE TABLE pqp_person_id_temp
934 -- AS SELECT DISTINCT paa.person_id
935 -- ,paa.assignment_id
936 -- ,haa.include_or_exclude
937 -- FROM per_all_assignments_f paa
938 -- ,hr_assignment_set_amendments haa
939 -- WHERE paa.assignment_id = haa.assignment_id
940 -- AND haa.assignment_set_id = '
941 -- || l_assignment_set_id;
942 -- EXECUTE IMMEDIATE l_create_string;
943 --
944 -- IF g_debug THEN
945 -- debug(l_proc_name, l_proc_step);
946 -- debug('l_create_string: '||l_create_string);
947 -- END IF;
948
949 ELSE -- formula id is null
950
951 -- Modify the sql string only if there is at least
952 -- one inclusion in the assignment set amendment
953 -- and if the assignment set is not based on criteria
954 OPEN csr_get_asg_amnd_incl(l_assignment_set_id);
955 FETCH csr_get_asg_amnd_incl INTO l_exists;
956
957 IF csr_get_asg_amnd_incl%FOUND
958 THEN
959 l_proc_step := 70;
960 l_string :=
961 'SELECT DISTINCT person_id
962 FROM per_all_assignments_f paa
963 ,hr_assignment_set_amendments hasa
964 ,pay_payroll_actions ppa
965 WHERE paa.business_group_id = ppa.business_group_id
966 AND ppa.payroll_action_id = :payroll_action_id
967 AND paa.assignment_id = hasa.assignment_id
968 AND NVL(hasa.include_or_exclude,'
969 || '''I'''
970 || ') = '
971 || '''I'''
972 || ' AND hasa.assignment_set_id = '
973 || l_assignment_set_id;
974 END IF; -- End if of inclusion amendments found ...
975
976 CLOSE csr_get_asg_amnd_incl;
977
978 IF g_debug
979 THEN
980 debug(l_proc_name, l_proc_step);
981 debug('l_string: ' || l_string);
982 END IF;
983 END IF; -- End if of formula id not null check ...
984 END IF; -- End if of assignment set id not null check ...
985
986 p_sqlstr := l_string;
987 l_proc_step := 80;
988
989 IF g_debug
990 THEN
991 debug(l_proc_name, l_proc_step);
992 debug('l_string: ' || l_string);
993 debug_exit(l_proc_name);
994 END IF;
995 EXCEPTION
996 WHEN OTHERS
997 THEN
998 clear_cache;
999
1000 IF SQLCODE <> hr_utility.hr_error_number
1001 THEN
1002 debug_others(l_proc_name, l_proc_step);
1003
1004 IF g_debug
1005 THEN
1006 debug('Leaving: ' || l_proc_name, -999);
1007 END IF;
1008
1009 hr_utility.raise_error;
1010 ELSE
1011 RAISE;
1012 END IF;
1013 --
1014 END range_cursor;
1015
1016 --
1017 /* ------------------------------------------------------------ */
1018 /* ------------ Check Asg Qualifies for Assignment Set -------- */
1019 /* ------------------------------------------------------------ */
1020 FUNCTION chk_is_asg_in_asg_set(
1021 p_assignment_id IN NUMBER
1022 ,p_formula_id IN NUMBER
1023 ,p_tab_asg_set_amnds IN t_asg_set_amnds
1024 ,p_effective_date IN DATE
1025 )
1026 RETURN VARCHAR2
1027 IS
1028 --
1029 -- Cursor to get session date
1030 CURSOR csr_get_session_date
1031 IS
1032 SELECT NVL(effective_date, SYSDATE)
1033 FROM fnd_sessions
1034 WHERE session_id = USERENV('SESSIONID');
1035
1036 l_proc_step NUMBER(38, 10) := 0;
1037 l_proc_name VARCHAR2(61)
1038 := g_package_name || 'chk_is_asg_in_asg_set';
1039 l_session_date DATE;
1040 l_include_flag VARCHAR2(10);
1041 l_tab_asg_set_amnds t_asg_set_amnds;
1042 l_inputs ff_exec.inputs_t;
1043 l_outputs ff_exec.outputs_t;
1044 --
1045 BEGIN
1046 --
1047
1048 IF g_debug
1049 THEN
1050 debug_enter(l_proc_name);
1051 debug('p_assignment_id: ' || p_assignment_id);
1052 debug('p_formula_id: ' || p_formula_id);
1053 debug('p_effective_date: ' || p_effective_date);
1054 END IF;
1055
1056 l_include_flag := 'N';
1057 l_tab_asg_set_amnds := p_tab_asg_set_amnds;
1058 l_proc_step := 10;
1059
1060 -- Check whether the assignment exists in the collection
1061 -- first as the static assignment set overrides the
1062 -- criteria one
1063 IF l_tab_asg_set_amnds.EXISTS(p_assignment_id)
1064 THEN
1065 -- Check whether to include or exclude
1066 IF l_tab_asg_set_amnds(p_assignment_id) = 'I'
1067 THEN
1068 l_include_flag := 'Y';
1069 ELSIF l_tab_asg_set_amnds(p_assignment_id) = 'E'
1070 THEN
1071 l_include_flag := 'N';
1072 END IF; -- End if of include or exclude flag check ...
1073 ELSIF p_formula_id IS NOT NULL
1074 THEN
1075 -- assignment does not exist in assignment set amendments
1076 -- check whether a formula criteria exists for this
1077 -- assignment set
1078 -- Initialize the formula
1079 l_proc_step := 30;
1080 ff_exec.init_formula(p_formula_id => p_formula_id
1081 ,p_effective_date => p_effective_date
1082 ,p_inputs => l_inputs
1083 ,p_outputs => l_outputs
1084 );
1085
1086 IF g_debug
1087 THEN
1088 debug(l_proc_name, l_proc_step);
1089 debug('p_formula_id: ' || p_formula_id);
1090 debug('p_effective_date: ' || p_effective_date);
1091 END IF;
1092
1093 l_proc_step := 40;
1094
1095 -- Get session date
1096 -- OPEN csr_get_session_date;
1097 -- FETCH csr_get_session_date INTO l_session_date;
1098 -- CLOSE csr_get_session_date;
1099
1100 -- Set the inputs first
1101 -- Loop through them to set the contexts
1102
1103 FOR i IN l_inputs.FIRST .. l_inputs.LAST
1104 LOOP
1105 IF l_inputs(i).NAME = 'ASSIGNMENT_ID'
1106 THEN
1107 l_inputs(i).VALUE := p_assignment_id;
1108 ELSIF l_inputs(i).NAME = 'DATE_EARNED'
1109 THEN
1110 l_inputs(i).VALUE :=
1111 fnd_date.date_to_canonical(p_effective_date);
1112 END IF;
1113
1114 IF g_debug
1115 THEN
1116 debug('l_inputs(' || i || ').name: ' || l_inputs(i).NAME);
1117 debug('l_inputs(' || i || ').value: ' || l_inputs(i).VALUE);
1118 END IF;
1119 END LOOP;
1120
1121 l_proc_step := 50;
1122
1123 IF g_debug
1124 THEN
1125 debug(l_proc_name, l_proc_step);
1126 END IF;
1127
1128 -- Run the formula
1129 ff_exec.run_formula(l_inputs, l_outputs);
1130 -- Check whether the assignment has to be included
1131 -- by checking the output flag
1132
1133 l_proc_step := 60;
1134
1135 FOR i IN l_outputs.FIRST .. l_outputs.LAST
1136 LOOP
1137 IF g_debug
1138 THEN
1139 debug('l_outputs(' || i || ').name: ' || l_outputs(i).NAME);
1140 debug('l_outputs(' || i || ').value: ' || l_outputs(i).VALUE);
1141 END IF;
1142
1143 IF l_outputs(i).NAME = 'INCLUDE_FLAG'
1144 THEN
1145 IF l_outputs(i).VALUE = 'Y'
1146 THEN
1147 l_include_flag := 'Y';
1148 ELSIF l_outputs(i).VALUE = 'N'
1149 THEN
1150 l_include_flag := 'N';
1151 END IF;
1152
1153 EXIT;
1154 END IF;
1155 END LOOP;
1156 END IF; -- End if of assignment exists in amendments check ...
1157
1158 l_proc_step := 70;
1159
1160 IF g_debug
1161 THEN
1162 debug(l_proc_name, l_proc_step);
1163 debug('l_include_flag: ' || l_include_flag);
1164 debug_exit(l_proc_name);
1165 END IF;
1166
1167 RETURN l_include_flag;
1168 EXCEPTION
1169
1170 WHEN hr_application_error
1171 THEN
1172 RETURN l_include_flag;
1173
1174 WHEN OTHERS
1175 THEN
1176 clear_cache;
1177
1178 IF SQLCODE <> hr_utility.hr_error_number
1179 THEN
1180 debug_others(l_proc_name, l_proc_step);
1181
1182 IF g_debug
1183 THEN
1184 debug('Leaving: ' || l_proc_name, -999);
1185 END IF;
1186
1187 hr_utility.raise_error;
1188 ELSE
1189 RAISE;
1190 END IF;
1191 --
1192 END chk_is_asg_in_asg_set;
1193
1194 --
1195 /* ------------------------------------------------------------ */
1196 /* --------------------- Action Creation ---------------------- */
1197 /* ------------------------------------------------------------ */
1198 PROCEDURE action_creation(
1199 p_pay_action_id IN NUMBER
1200 ,p_start_person IN NUMBER
1201 ,p_end_person IN NUMBER
1202 ,p_chunk IN NUMBER
1203 )
1204 IS
1205 --
1206 -- Cursor to fetch assignments based on person id
1207 CURSOR csr_get_eff_assignments(
1208 c_assignment_id NUMBER
1209 ,c_business_group_id NUMBER
1210 ,c_effective_date DATE
1211 )
1212 IS
1213 SELECT asg.assignment_id assignment_id, asg.payroll_id
1214 FROM per_all_assignments_f asg
1215 WHERE asg.person_id BETWEEN p_start_person AND p_end_person
1216 AND asg.assignment_id = NVL(c_assignment_id, asg.assignment_id)
1217 AND asg.business_group_id = c_business_group_id
1218 AND ( c_effective_date BETWEEN asg.effective_start_date
1219 AND asg.effective_end_date
1220 OR ( asg.effective_start_date > c_effective_date
1221 AND asg.effective_end_date =
1222 (SELECT MIN(asg2.effective_end_date)
1223 FROM per_all_assignments_f asg2
1224 WHERE asg2.assignment_id = asg.assignment_id)
1225 )
1226 )
1227 AND asg.assignment_type NOT IN ('C', 'A', 'B') -- Bug 6847750, 7718235
1228 ORDER BY asg.assignment_id;
1229
1230 -- Cursor to get next value from assignment action seq
1231 CURSOR csr_get_asg_action_seq
1232 IS
1233 SELECT pay_assignment_actions_s.NEXTVAL
1234 FROM DUAL;
1235
1236 -- Cursor to get assignments from assignment amendments
1237 -- that does not fall within the effective date range
1238 CURSOR csr_get_asg_out_date(
1239 c_assignment_set_id NUMBER
1240 ,c_effective_date DATE
1241 )
1242 IS
1243 SELECT asg.assignment_id
1244 FROM per_all_assignments_f asg
1245 ,hr_assignment_set_amendments hasa
1246 WHERE asg.assignment_id = hasa.assignment_id
1247 AND hasa.assignment_set_id = c_assignment_set_id
1248 AND NVL(hasa.include_or_exclude, 'I') = 'I'
1249 AND asg.person_id BETWEEN p_start_person AND p_end_person
1250 AND asg.effective_end_date < c_effective_date
1251 AND NOT EXISTS(
1252 SELECT 1
1253 FROM per_all_assignments_f asg2
1254 WHERE asg2.assignment_id = asg.assignment_id
1255 AND ( c_effective_date
1256 BETWEEN asg2.effective_start_date
1257 AND asg2.effective_end_date
1258 OR asg2.effective_start_date > c_effective_date
1259 ))
1260 ORDER BY asg.assignment_id;
1261
1262 -- Cursor to check for assignment contract
1263 CURSOR csr_chk_asg_contract(
1264 c_assignment_id NUMBER
1265 ,c_contract VARCHAR2
1266 ,c_effective_date DATE
1267 )
1268 IS
1269 SELECT 'X'
1270 FROM pqp_assignment_attributes_f
1271 WHERE assignment_id = c_assignment_id
1272 AND contract_type = c_contract
1273 AND ( c_effective_date BETWEEN effective_start_date
1274 AND effective_end_date
1275 OR effective_start_date > c_effective_date
1276 );
1277
1278 l_proc_step NUMBER(38, 10) := 0;
1279 l_proc_name VARCHAR2(61)
1280 := g_package_name || 'action_creation';
1281 l_assignment_id NUMBER;
1282 l_assignment_set_id NUMBER;
1283 l_payroll_id NUMBER;
1284 l_contract pqp_assignment_attributes_f.contract_type%TYPE;
1285 l_business_group_id per_business_groups.business_group_id%TYPE;
1286 l_effective_date DATE;
1287 l_tab_asg_set_amnds t_asg_set_amnds;
1288 l_include_flag VARCHAR2(10);
1289 l_exists VARCHAR2(10);
1290 l_report_assignment NUMBER;
1291 l_asg_action_seq NUMBER;
1292 -- Bug 6147019 Begin
1293 l_formula_id NUMBER;
1294 -- Bug 6147019 End
1295 BEGIN
1296 --
1297 IF g_debug
1298 THEN
1299 debug_enter(l_proc_name);
1300 debug('p_pay_action_id: ' || p_pay_action_id);
1301 debug('p_start_person: ' || p_start_person);
1302 debug('p_end_person: ' || p_end_person);
1303 debug('p_chunk: ' || p_chunk);
1304 END IF;
1305
1306 l_proc_step := 10;
1307
1308 IF g_uom IS NULL
1309 THEN
1310 -- load cache
1311 load_cache(p_payroll_action_id => p_pay_action_id);
1312 END IF;
1313
1314 l_assignment_id :=
1315 get_parameter_value('ASSIGNMENT', g_parameter_list);
1316 -- Bug 6147019 Begin l_assignment_set_id := g_assignment_set_id;
1317 l_assignment_set_id :=
1318 get_parameter_value(p_string => 'ASSIGNMENT SET' -- IN
1319 ,p_parameter_list => g_parameter_list -- IN
1320 );
1321 -- Bug 6147019 End
1322 l_effective_date := g_effective_date;
1323 l_payroll_id := g_payroll_id;
1324 l_contract := g_contract;
1325 l_business_group_id := g_business_group_id;
1326
1327 IF g_debug
1328 THEN
1329 debug(l_proc_name, l_proc_step);
1330 debug('l_assignment_id: ' || l_assignment_id);
1331 debug('l_assignment_set: ' || l_assignment_set_id);
1332 debug('l_payroll: ' || l_payroll_id);
1333 debug('l_contract: ' || l_contract);
1334 debug('l_effective_date: ' || l_effective_date);
1335 END IF;
1336
1337 -- Bug 6147019 Begin
1338 IF l_assignment_set_id IS NOT NULL
1339 THEN
1340 l_proc_step := 15;
1341 g_assignment_set_id := l_assignment_set_id;
1342 -- call local procedure to get assignment set details
1343 get_asg_set_details(p_assignment_set_id => l_assignment_set_id
1344 ,p_formula_id => l_formula_id
1345 ,p_tab_asg_set_amnds => l_tab_asg_set_amnds
1346 );
1347 IF l_formula_id IS NOT NULL
1348 THEN
1349 -- we will use the selective logic at assignment
1350 -- action level, store the formula id in the
1351 -- global variable
1352 g_formula_id := l_formula_id;
1353
1354 IF g_debug
1355 THEN
1356 debug('g_formula_id: ' || g_formula_id);
1357 END IF;
1358 END IF;
1359
1360 END IF;
1361 -- Bug 6147019 End
1362 -- Log Messages
1363 fnd_file.put_line(fnd_file.LOG
1364 , RPAD('Assignment Set Id', 30)
1365 || ': '
1366 || l_assignment_set_id
1367 );
1368 fnd_file.put_line(fnd_file.LOG
1369 , RPAD('Payroll Id', 30) || ': ' || l_payroll_id
1370 );
1371 fnd_file.put_line(fnd_file.LOG
1372 , RPAD('Contract', 30) || ': ' || l_contract);
1373 fnd_file.put_line(fnd_file.LOG
1374 , RPAD('Effective Date', 30)
1375 || ': '
1376 || fnd_date.date_to_displaydate(l_effective_date)
1377 );
1378 l_proc_step := 20;
1379
1380 -- Loop through effective assignments for this person
1381 -- and check whether an assignment action has to be created
1382 -- after satisfying several criteria
1383 FOR l_asg_rec IN csr_get_eff_assignments(l_assignment_id
1384 ,l_business_group_id
1385 ,l_effective_date
1386 )
1387 LOOP
1388 l_include_flag := 'N';
1389 -- Log messages
1390 fnd_file.put_line(fnd_file.LOG
1391 , RPAD('Processing Assignment', 30)
1392 || ': '
1393 || l_asg_rec.assignment_id
1394 );
1395
1396 --
1397 -- Check whether an assignmet set is specified
1398 --
1399 IF l_assignment_set_id IS NOT NULL
1400 THEN
1401 -- Check whether this assignment is in the assignment set
1402 l_proc_step := 30;
1403 l_include_flag :=
1404 chk_is_asg_in_asg_set(p_assignment_id => l_asg_rec.assignment_id
1405 ,p_formula_id => g_formula_id
1406 ,p_tab_asg_set_amnds => g_tab_asg_set_amnds
1407 ,p_effective_date => l_effective_date
1408 );
1409 ELSE -- assignment set is null
1410 l_include_flag := 'Y';
1411 END IF; -- End if of assignment_set IS NOT NULL check ...
1412
1413 l_proc_step := 60;
1414
1415 IF g_debug
1416 THEN
1417 debug(l_proc_name, l_proc_step);
1418 debug('Assignment ID: ' || l_asg_rec.assignment_id);
1419 debug('l_include_flag: ' || l_include_flag);
1420 END IF;
1421
1422 IF l_include_flag = 'Y'
1423 THEN
1424 l_proc_step := 70;
1425
1426 -- Check whether a payroll has been specified
1427 IF l_payroll_id IS NOT NULL
1428 THEN
1429 -- Check whether the payroll id of assignment matches with
1430 -- this payroll id
1431
1432 IF l_payroll_id = l_asg_rec.payroll_id
1433 THEN
1434 l_include_flag := 'Y';
1435 ELSE
1436 l_include_flag := 'N';
1437 END IF;
1438
1439 IF g_debug
1440 THEN
1441 debug(l_proc_name, l_proc_step);
1442 debug('l_asg_rec.payroll_id: ' || l_asg_rec.payroll_id);
1443 debug('l_include_flag: ' || l_include_flag);
1444 END IF;
1445 END IF; -- End if of payroll id not null check ...
1446
1447 l_proc_step := 80;
1448
1449 IF g_debug
1450 THEN
1451 debug(l_proc_name, l_proc_step);
1452 debug('l_include_flag: ' || l_include_flag);
1453 END IF;
1454
1455 IF l_include_flag = 'Y' AND l_contract IS NOT NULL
1456 THEN
1457 l_proc_step := 100;
1458 -- Check whether this assignment belongs to this contract
1459 OPEN csr_chk_asg_contract(l_asg_rec.assignment_id
1460 ,l_contract
1461 ,l_effective_date
1462 );
1463 FETCH csr_chk_asg_contract INTO l_exists;
1464
1465 IF csr_chk_asg_contract%FOUND
1466 THEN
1467 l_include_flag := 'Y';
1468 ELSE
1469 l_include_flag := 'N';
1470 END IF;
1471
1472 CLOSE csr_chk_asg_contract;
1473
1474 IF g_debug
1475 THEN
1476 debug(l_proc_name, l_proc_step);
1477 debug('l_include_flag: ' || l_include_flag);
1478 END IF;
1479 END IF; -- End if of contract not null check ...
1480 END IF; -- End if of l_include_flag = 'Y' check ...
1481
1482 l_proc_step := 110;
1483
1484 IF g_debug
1485 THEN
1486 debug(l_proc_name, l_proc_step);
1487 debug('l_include_flag: ' || l_include_flag);
1488 END IF;
1489
1490 IF l_include_flag = 'Y'
1491 THEN
1492 -- Log messages
1493 fnd_file.put_line(fnd_file.LOG
1494 , RPAD('Include Assignment', 30) || ': Yes'
1495 );
1496 -- Create the assignment action to represent the person
1497 OPEN csr_get_asg_action_seq;
1498 FETCH csr_get_asg_action_seq INTO l_asg_action_seq;
1499 CLOSE csr_get_asg_action_seq;
1500 fnd_file.put_line(fnd_file.LOG
1501 , RPAD('Assignment Action Id', 30)
1502 || ': '
1503 || l_asg_action_seq
1504 );
1505 -- insert into pay_assignment_actions
1506 hr_nonrun_asact.insact(l_asg_action_seq
1507 ,l_asg_rec.assignment_id
1508 ,p_pay_action_id
1509 ,p_chunk
1510 ,NULL
1511 );
1512 ELSE
1513 -- Log Messages
1514 fnd_file.put_line(fnd_file.LOG
1515 , RPAD('Include Assignment', 30) || ': No'
1516 );
1517 END IF; -- END if of l_include_flag = 'Y' check ...
1518 END LOOP;
1519
1520 -- Report all assignments that are in the static assignment sets
1521 -- that fall outside the effective date range
1522 -- i.e. within or in the future
1523
1524 IF g_tab_asg_set_amnds.COUNT > 0
1525 THEN
1526 l_proc_step := 120;
1527 OPEN csr_get_asg_out_date(l_assignment_set_id, l_effective_date);
1528 LOOP
1529 FETCH csr_get_asg_out_date INTO l_report_assignment;
1530 EXIT WHEN csr_get_asg_out_date%NOTFOUND;
1531
1532 IF l_proc_step = 120 THEN
1533 fnd_file.put_line(fnd_file.LOG
1534 ,'The following assignments in the static assignment set were unprocessed:'
1535 );
1536 END IF;
1537 l_proc_step := 121;
1538
1539 fnd_file.put_line(fnd_file.LOG
1540 , RPAD('Assignment ID', 30)
1541 || ': '
1542 || l_report_assignment
1543 );
1544
1545 IF g_debug
1546 THEN
1547 debug('l_report_assignment: ' || l_report_assignment);
1548 END IF;
1549 END LOOP;
1550
1551 CLOSE csr_get_asg_out_date;
1552 END IF; -- End if of assignment amendments exist
1553
1554 IF g_debug
1555 THEN
1556 debug(l_proc_name, l_proc_step);
1557 debug_exit(l_proc_name);
1558 END IF;
1559 EXCEPTION
1560 WHEN OTHERS
1561 THEN
1562 clear_cache;
1563
1564 IF SQLCODE <> hr_utility.hr_error_number
1565 THEN
1566 debug_others(l_proc_name, l_proc_step);
1567
1568 IF g_debug
1569 THEN
1570 debug('Leaving: ' || l_proc_name, -999);
1571 END IF;
1572
1573 hr_utility.raise_error;
1574 ELSE
1575 RAISE;
1576 END IF;
1577 END action_creation;
1578
1579 /* ------------------------------------------------------------ */
1580 /* --------------------- Archive Data ------------------------- */
1581 /* ------------------------------------------------------------ */
1582 PROCEDURE archive_data(
1583 p_assignment_action_id IN NUMBER
1584 ,p_effective_date IN DATE
1585 )
1586 IS
1587 CURSOR csr_assignment_id(p_assignment_action_id NUMBER)
1588 IS
1589 SELECT assignment_id, payroll_action_id
1590 FROM pay_assignment_actions
1591 WHERE assignment_action_id = p_assignment_action_id;
1592
1593 l_asg_action_details csr_assignment_id%ROWTYPE;
1594 l_proc_step NUMBER(38, 10) := 0;
1595 l_proc_name VARCHAR2(61) := g_package_name || 'archive_data';
1596 BEGIN
1597 g_is_concurrent_program_run := TRUE;
1598 g_debug := hr_utility.debug_enabled;
1599 g_output_file_records.DELETE;
1600
1601 IF g_debug
1602 THEN
1603 debug_enter(l_proc_name);
1604 debug('p_assignment_action_id: ' || p_assignment_action_id);
1605 debug('p_effective_date: ' || p_effective_date);
1606 END IF;
1607
1608 OPEN csr_assignment_id(p_assignment_action_id);
1609 FETCH csr_assignment_id INTO l_asg_action_details;
1610 CLOSE csr_assignment_id;
1611 l_proc_step := 10;
1612
1613 IF g_debug
1614 THEN
1615 debug(l_proc_name, l_proc_step);
1616 debug( 'l_asg_action_details.assignment_id: '
1617 || l_asg_action_details.assignment_id
1618 );
1619 debug( 'l_asg_action_details.payroll_action_id: '
1620 || l_asg_action_details.payroll_action_id
1621 );
1622 END IF;
1623
1624 IF g_uom IS NULL
1625 THEN
1626 load_cache(l_asg_action_details.payroll_action_id);
1627 END IF;
1628
1629 pqp_budget_maintenance.maintain_abv_for_assignment(p_uom => g_uom
1630 ,p_assignment_id => l_asg_action_details.assignment_id
1631 ,p_business_group_id => g_business_group_id
1632 ,p_effective_date => g_effective_date
1633 ,p_action => g_action
1634 );
1635
1636 IF g_debug
1637 THEN
1638 debug_exit(l_proc_name);
1639 END IF;
1640 EXCEPTION
1641 WHEN OTHERS
1642 THEN
1643 clear_cache;
1644
1645 IF SQLCODE <> hr_utility.hr_error_number
1646 THEN
1647 debug_others(l_proc_name, l_proc_step);
1648
1649 IF g_debug
1650 THEN
1651 debug('Leaving: ' || l_proc_name, -999);
1652 END IF;
1653
1654 hr_utility.raise_error;
1655 ELSE
1656 RAISE;
1657 END IF;
1658 END archive_data;
1659
1660 -------------------------------------------------
1661 ----------SORT_EVENT_DATES---------------------
1662 -------------------------------------------------
1663
1664 PROCEDURE sort_event_dates(
1665 p_base_table IN OUT NOCOPY t_indexed_dates
1666 ,p_compare_table IN OUT NOCOPY pqp_table_of_dates
1667 )
1668 IS
1669 l_current NUMBER;
1670 l_proc_step NUMBER(20, 10) := 0;
1671 l_proc_name VARCHAR2(61) := g_package_name || 'sort_event_dates';
1672 BEGIN
1673 IF g_debug
1674 THEN
1675 debug_enter(l_proc_name);
1676 END IF;
1677
1678 --
1679 -- This procedure is called when we execute the custom function
1680 -- to populate impact dates. This takes care of the fact that user
1681 -- function returned dates may not be sorted.
1682
1683 l_current := p_compare_table.FIRST;
1684
1685 WHILE l_current IS NOT NULL
1686 LOOP
1687 l_proc_step :=
1688 10
1689 + l_current / 100000;
1690
1691 IF g_debug
1692 THEN
1693 debug(l_proc_name, l_proc_step);
1694 END IF;
1695
1696 p_base_table(TO_CHAR(p_compare_table(l_current), 'j')) :=
1697 p_compare_table(l_current);
1698 l_current :=
1699 p_compare_table.NEXT(l_current);
1700 END LOOP; -- WHILE l_current IS NOT NULL
1701
1702 IF g_debug
1703 THEN
1704 debug('Sorted List of dates');
1705 l_current := p_base_table.FIRST;
1706
1707 WHILE l_current IS NOT NULL
1708 LOOP
1709 debug(p_base_table(l_current));
1710 l_current := p_base_table.NEXT(l_current);
1711 END LOOP;
1712
1713 debug_exit(l_proc_name);
1714 END IF;
1715 EXCEPTION
1716 WHEN OTHERS
1717 THEN
1718 clear_cache;
1719
1720 IF SQLCODE <> hr_utility.hr_error_number
1721 THEN
1722 debug_others(l_proc_name, l_proc_step);
1723
1724 IF g_debug
1725 THEN
1726 debug('Leaving: ' || l_proc_name, -999);
1727 END IF;
1728
1729 hr_utility.raise_error;
1730 ELSE
1731 RAISE;
1732 END IF;
1733 END sort_event_dates;
1734
1735
1736 -------------------------------------------------------------
1737 ---------------GET_EARLIEST_FTE_DATE-------------------------
1738 -------------------------------------------------------------
1739
1740 FUNCTION get_earliest_possible_fte_date(p_assignment_id NUMBER
1741 ,p_effective_date DATE)
1742 RETURN DATE
1743 IS
1744 l_proc_step NUMBER(20, 10) := 0;
1745 l_proc_name VARCHAR2(61)
1746 := g_package_name || 'get_earliest_possible_FTE_date';
1747
1748 CURSOR csr_min_aat_start_date(p_assignment_id NUMBER)
1749 IS
1750 SELECT MIN(aat.effective_start_date)
1751 FROM pqp_assignment_attributes_f aat
1752 WHERE aat.assignment_id = p_assignment_id
1753 AND aat.contract_type IS NOT NULL;
1754
1755 CURSOR csr_min_asg_start_date(p_assignment_id NUMBER)
1756 IS
1757 SELECT MIN(asg.effective_start_date)
1758 FROM per_all_assignments_f asg
1759 WHERE asg.assignment_id = p_assignment_id
1760 AND asg.normal_hours IS NOT NULL;
1761
1762 l_aat_effective_start_date DATE;
1763 l_asg_effective_start_date DATE;
1764 l_earliest_effective_date DATE;
1765 BEGIN -- get_earliest_possible_FTE_date
1766 IF g_debug
1767 THEN
1768 debug_enter(l_proc_name);
1769 debug('p_assignment_id:' || p_assignment_id);
1770 END IF;
1771
1772 OPEN csr_min_aat_start_date(p_assignment_id);
1773 FETCH csr_min_aat_start_date INTO l_aat_effective_start_date;
1774 IF csr_min_aat_start_date%NOTFOUND
1775 OR
1776 l_aat_effective_start_date IS NULL
1777 THEN
1778 l_proc_step := 10;
1779 IF g_debug
1780 THEN
1781 debug(l_proc_name, l_proc_step);
1782 END IF;
1783 CLOSE csr_min_aat_start_date;
1784 hr_utility.set_message(8303, 'PQP_230113_AAT_MISSING_CONTRCT');
1785 hr_utility.set_message_token('EFFECTIVEDATE',
1786 fnd_date.date_to_displaydate(p_effective_date)
1787 );
1788 hr_utility.raise_error;
1789 END IF;
1790 CLOSE csr_min_aat_start_date;
1791
1792 OPEN csr_min_asg_start_date(p_assignment_id);
1793 FETCH csr_min_asg_start_date INTO l_asg_effective_start_date;
1794 IF csr_min_asg_start_date%NOTFOUND
1795 OR
1796 l_asg_effective_start_date IS NULL
1797 THEN
1798 l_proc_step := 20;
1799 IF g_debug
1800 THEN
1801 debug(l_proc_name, l_proc_step);
1802 END IF;
1803 CLOSE csr_min_asg_start_date;
1804 hr_utility.set_message(8303, 'PQP_230456_FTE_NO_ASG_DETAILS');
1805 hr_utility.set_message_token('EFFECTIVEDATE',
1806 fnd_date.date_to_displaydate(p_effective_date)
1807 );
1808 hr_utility.raise_error;
1809 END IF;
1810 CLOSE csr_min_asg_start_date;
1811
1812 IF g_debug
1813 THEN
1814 debug('l_aat_effective_start_date:' || l_aat_effective_start_date);
1815 debug('l_asg_effective_start_date:' || l_asg_effective_start_date);
1816 END IF;
1817
1818 l_earliest_effective_date :=
1819 GREATEST(l_aat_effective_start_date, l_asg_effective_start_date);
1820
1821 l_proc_step := 30;
1822 IF g_debug
1823 THEN
1824 debug(l_proc_name,l_proc_step);
1825 debug( 'l_earliest_effective_date:'
1826 || fnd_date.date_to_canonical(l_earliest_effective_date)
1827 );
1828 debug_exit(l_proc_name);
1829 END IF;
1830
1831 RETURN l_earliest_effective_date;
1832 EXCEPTION
1833 WHEN OTHERS
1834 THEN
1835 clear_cache;
1836
1837 IF SQLCODE <> hr_utility.hr_error_number
1838 THEN
1839 debug_others(l_proc_name, l_proc_step);
1840
1841 IF g_debug
1842 THEN
1843 debug('Leaving: ' || l_proc_name, -999);
1844 END IF;
1845
1846 hr_utility.raise_error;
1847 ELSE
1848 RAISE;
1849 END IF;
1850 END get_earliest_possible_fte_date;
1851
1852 ----------------------------------------------------------------------
1853 ------------------MAINTAIN_ABV_FOR_ASSIGNMENT-------------------------
1854 ----------------------------------------------------------------------
1855
1856 PROCEDURE maintain_abv_for_assignment(
1857 p_uom IN VARCHAR2
1858 ,p_assignment_id IN NUMBER
1859 ,p_business_group_id IN NUMBER
1860 ,p_effective_date IN DATE
1861 ,p_action IN VARCHAR2
1862 )
1863 IS
1864 l_proc_step NUMBER(20, 10) := 0;
1865 l_proc_name VARCHAR2(61)
1866 := g_package_name || 'maintain_abv_for_assignment';
1867 l_current NUMBER;
1868 l_effective_date DATE;
1869 l_log_string VARCHAR2(4000);
1870 l_uom pqp_configuration_values.pcv_information1%TYPE;
1871 l_event_dates_source pqp_configuration_values.pcv_information1%TYPE;
1872 l_track_event_group_id pqp_configuration_values.pcv_information1%TYPE;
1873 l_custom_function_name pqp_configuration_values.pcv_information1%TYPE;
1874 l_budget_fast_formula_id pqp_configuration_values.pcv_information1%TYPE;
1875 l_this_change_date DATE;
1876 l_last_change_date DATE;
1877 t_impact_dates t_indexed_dates; -- table containing the final ordered dates
1878 l_maintenance_information csr_get_configuration_data%ROWTYPE;
1879 c_verify CONSTANT VARCHAR2(20) := 'Verify';
1880
1881 --7636627
1882 l_asg_effective_end_date Date;
1883
1884 CURSOR csr_max_asg_end_date(p_assignment_id NUMBER)
1885 IS
1886 SELECT MAX(asg.effective_end_date)
1887 FROM per_all_assignments_f asg
1888 WHERE asg.assignment_id = p_assignment_id
1889 AND asg.normal_hours IS NOT NULL;
1890 --
1891 BEGIN
1892 SAVEPOINT maintain_abv_savepoint;
1893
1894 IF NOT g_is_concurrent_program_run
1895 THEN
1896 g_debug := hr_utility.debug_enabled;
1897 END IF;
1898
1899 IF g_debug
1900 THEN
1901 debug_enter(l_proc_name);
1902 debug('p_assignment_id: ' || p_assignment_id);
1903 debug('p_effective_date: ' || p_effective_date);
1904 debug('p_uom: ' || p_uom);
1905 debug( 'g_configuration_data.pcv_information1: '
1906 || g_configuration_data.pcv_information1
1907 );
1908 debug('p_business_group_id: ' || p_business_group_id);
1909 debug('g_business_group_id: ' || g_business_group_id);
1910 END IF;
1911
1912 --
1913 -- if the cached process definition uom or business group
1914 -- is not equal to the current uom or business group obtain the
1915 -- new configuration values
1916 --
1917 IF ( (p_uom <> NVL(g_configuration_data.pcv_information1, '~null'))
1918 OR (p_business_group_id <> g_business_group_id)
1919 )
1920 THEN
1921 l_proc_step := 5;
1922
1923 IF g_debug
1924 THEN
1925 debug(l_proc_name, l_proc_step);
1926 END IF;
1927
1928 -- empty cache before populating
1929 --
1930 g_business_group_id := NULL;
1931 g_legislation_code := NULL;
1932 g_configuration_data := NULL;
1933 OPEN get_business_group_id(p_assignment_id => p_assignment_id);
1934 FETCH get_business_group_id INTO g_business_group_id;
1935 IF get_business_group_id%NOTFOUND
1936 THEN
1937 l_proc_step := 10;
1938 IF g_debug
1939 THEN
1940 debug(l_proc_name, l_proc_step);
1941 END IF;
1942 CLOSE get_business_group_id;
1943 RAISE NO_DATA_FOUND;
1944 END IF;
1945 CLOSE get_business_group_id;
1946
1947 OPEN get_legislation_code(p_business_group_id => g_business_group_id);
1948 FETCH get_legislation_code INTO g_legislation_code;
1949 IF get_legislation_code%NOTFOUND
1950 THEN
1951 l_proc_step := 20;
1952 IF g_debug
1953 THEN
1954 debug(l_proc_name, l_proc_step);
1955 END IF;
1956 CLOSE get_legislation_code;
1957 RAISE NO_DATA_FOUND;
1958 END IF;
1959 CLOSE get_legislation_code;
1960
1961 l_proc_step := 30;
1962 IF g_debug
1963 THEN
1964 debug(l_proc_name, l_proc_step);
1965 debug('g_business_group_id: ' || g_business_group_id);
1966 debug('g_legislation_code: ' || g_legislation_code);
1967 END IF;
1968
1969 -- check for maintenance enabled
1970 -- if disabled signal error and stop processing
1971 load_cache(p_uom => p_uom
1972 ,p_business_group_id => g_business_group_id
1973 ,p_legislation_code => g_legislation_code
1974 ,p_information_category => c_abvm_maintenance
1975 ,p_configuration_data => l_maintenance_information
1976 );
1977
1978 IF g_debug
1979 THEN
1980 debug( 'l_maintenance_information.pcv_information1: '
1981 || l_maintenance_information.pcv_information1
1982 );
1983 debug( 'l_maintenance_information.pcv_information2: '
1984 || l_maintenance_information.pcv_information2
1985 );
1986 END IF;
1987
1988 l_proc_step := 40;
1989 IF (l_maintenance_information.pcv_information2 <> 'Y')
1990 THEN
1991 IF g_debug
1992 THEN
1993 debug(l_proc_name, l_proc_step);
1994 END IF;
1995 hr_utility.raise_error;
1996 END IF;
1997
1998 l_log_string := NULL;
1999
2000 IF g_is_concurrent_program_run
2001 THEN
2002 SELECT NAME
2003 INTO l_log_string
2004 FROM per_business_groups_perf
2005 WHERE business_group_id = g_business_group_id;
2006
2007 fnd_file.put_line(fnd_file.LOG
2008 , RPAD('Business Group', 30, ' ')
2009 || ': '
2010 || l_log_string
2011 );
2012 fnd_file.put_line(fnd_file.LOG
2013 , RPAD('Effective Date', 30, ' ')
2014 || ': '
2015 || fnd_date.date_to_displaydate(p_effective_date
2016 )
2017 );
2018 END IF;
2019
2020 load_cache(p_uom => p_uom
2021 ,p_business_group_id => g_business_group_id
2022 ,p_legislation_code => g_legislation_code
2023 ,p_information_category => c_abvm_definition
2024 ,p_configuration_data => g_configuration_data
2025 );
2026
2027 -- g_configuration_data
2028 -- UOM pcv_information1
2029 -- Event Dates Source pcv_information2
2030 -- Event Dates - Event Group pcv_information3
2031 -- Event Dates - Custom Function pcv_information4
2032 -- Budget Value Formula pcv_information5
2033
2034 l_log_string := NULL;
2035
2036 IF g_is_concurrent_program_run
2037 THEN
2038 -- make log entry for configuration data used for batch process run
2039 -- making an entry here ensures that log is made only when the
2040 -- configuration data changes
2041 fnd_file.put_line(fnd_file.LOG
2042 , RPAD('Process Definition', 30, ' ')
2043 || ': '
2044 || g_configuration_data.configuration_name
2045 );
2046
2047 fnd_file.put_line(fnd_file.LOG, RPAD('UOM', 30, ' ') || ': '
2048 ||g_configuration_data.pcv_information1);
2049 fnd_file.put_line(fnd_file.LOG
2050 , RPAD('Event Dates Source', 30, ' ')
2051 || ': '
2052 || g_configuration_data.pcv_information2
2053 );
2054 -- log event group
2055 IF g_configuration_data.pcv_information3 IS NOT NULL
2056 THEN
2057 SELECT event_group_name
2058 INTO l_log_string
2059 FROM pay_event_groups
2060 WHERE event_group_id = g_configuration_data.pcv_information3;
2061 END IF;
2062
2063 l_proc_step := 50;
2064
2065 IF g_debug
2066 THEN
2067 debug(l_proc_name, l_proc_step);
2068 END IF;
2069
2070 fnd_file.put_line(fnd_file.LOG
2071 , RPAD('Track Event Group', 30, ' ')
2072 || ': '
2073 || l_log_string
2074 );
2075 fnd_file.put_line(fnd_file.LOG
2076 , RPAD('Custom Function', 30, ' ')
2077 || ': '
2078 || g_configuration_data.pcv_information4
2079 );
2080 -- log fast formula
2081 SELECT formula_name
2082 INTO l_log_string
2083 FROM ff_formulas_f
2084 WHERE formula_id = g_configuration_data.pcv_information5;
2085
2086 l_proc_step := 60;
2087
2088 IF g_debug
2089 THEN
2090 debug(l_proc_name, l_proc_step);
2091 END IF;
2092
2093 fnd_file.put_line(fnd_file.LOG
2094 , RPAD('Budget Fast Formula', 30, ' ')
2095 || ': '
2096 || l_log_string
2097 );
2098 END IF; -- IF g_is_concurrent_program_run
2099 END IF; -- IF ((p_uom <> nvl(g_definition_data_record.uom,'~null'))
2100
2101 g_defn_configuration_id :=
2102 g_configuration_data.configuration_value_id;
2103 l_uom := g_configuration_data.pcv_information1;
2104 l_event_dates_source := g_configuration_data.pcv_information2;
2105 l_track_event_group_id := g_configuration_data.pcv_information3;
2106 l_custom_function_name := g_configuration_data.pcv_information4;
2107 l_budget_fast_formula_id := g_configuration_data.pcv_information5;
2108 l_proc_step := 70;
2109
2110 IF g_debug
2111 THEN
2112 debug(l_proc_name, l_proc_step);
2113 debug('g_defn_configuration_id: ' || g_defn_configuration_id);
2114 debug('UOM: ' || l_uom);
2115 debug('Event Dates Source: ' || l_event_dates_source);
2116 debug('Track Event Group: ' || l_track_event_group_id);
2117 debug('Custom Function: ' || l_custom_function_name);
2118 debug('Budget Fast Formula: ' || l_budget_fast_formula_id);
2119 END IF;
2120
2121
2122 -- create output records for concurrent process
2123 -- and fix the start calculation date
2124 IF g_is_concurrent_program_run
2125 THEN
2126 l_proc_step := 75;
2127 IF g_debug
2128 THEN
2129 debug(l_proc_name, l_proc_step);
2130 END IF;
2131
2132 g_output_file_records(1).assignment_id :=
2133 p_assignment_id;
2134 g_output_file_records(g_output_file_records.FIRST).uom :=
2135 p_uom;
2136
2137 SELECT employee_number
2138 INTO g_output_file_records(g_output_file_records.FIRST).employee_number
2139 FROM per_all_people_f a
2140 WHERE a.person_id =
2141 (SELECT asg.person_id
2142 FROM per_all_assignments_f asg
2143 WHERE asg.assignment_id = p_assignment_id AND ROWNUM < 2)
2144 AND effective_start_date = (SELECT MAX(b.effective_start_date)
2145 FROM per_all_people_f b
2146 WHERE b.person_id = a.person_id);
2147
2148 l_proc_step := 80;
2149
2150 IF g_debug
2151 THEN
2152 debug(l_proc_name, l_proc_step);
2153 END IF;
2154
2155 SELECT assignment_number
2156 INTO g_output_file_records(g_output_file_records.FIRST).assignment_number
2157 FROM per_all_assignments_f a
2158 WHERE a.assignment_id = p_assignment_id
2159 AND a.effective_start_date =
2160 (SELECT MAX(b.effective_start_date)
2161 FROM per_all_assignments_f b
2162 WHERE b.assignment_id = a.assignment_id);
2163
2164 END IF; -- IF g_is_concurrent_program_run
2165
2166 -- to support all assignments which have a later starting date than the effective
2167 -- date passed, the earliest possible effective date of the assignment will be used
2168 -- note : this is applicable for all UOMs, the only criteria being that the
2169 -- inbuilt custom function is being used
2170
2171 IF ( g_is_concurrent_program_run
2172 AND
2173 (LOWER(l_custom_function_name) =
2174 'pqp_budget_maintenance.get_fte_event_dates')
2175 )
2176 THEN
2177 l_proc_step := 90;
2178 IF g_debug
2179 THEN
2180 debug(l_proc_name, l_proc_step);
2181 END IF;
2182
2183 l_effective_date :=
2184 get_earliest_possible_fte_date(p_assignment_id
2185 ,p_effective_date);
2186 ELSE
2187 l_effective_date := p_effective_date;
2188 END IF; -- IF ( LOWER(l_custom_function_name)...
2189
2190 -- enter effective date in output record
2191 IF g_is_concurrent_program_run
2192 THEN
2193 g_output_file_records(g_output_file_records.FIRST).effective_date :=
2194 l_effective_date;
2195 END IF;
2196
2197 l_proc_step := 100;
2198 IF g_debug
2199 THEN
2200 debug(l_proc_name, l_proc_step);
2201 debug('l_effective_date: ' || l_effective_date);
2202 END IF;
2203
2204 -- empty table of dates before populating for assignment
2205 t_impact_dates.DELETE;
2206 get_event_dates(p_uom => l_uom
2207 ,p_assignment_id => p_assignment_id
2208 ,p_business_group_id => p_business_group_id
2209 ,p_event_dates_source => l_event_dates_source
2210 ,p_event_group_id => l_track_event_group_id
2211 ,p_custom_function => l_custom_function_name
2212 ,p_effective_date => l_effective_date
2213 ,p_impact_dates => t_impact_dates
2214 );
2215 --
2216 -- irrespective of the configuration value options the final
2217 -- impact dates should be populated in t_impact_dates in sorted order
2218 --
2219 -- insert the first row as of the effective date calculated previously
2220 update_value_for_event_dates(p_uom => p_uom
2221 ,p_assignment_id => p_assignment_id
2222 ,p_business_group_id => g_business_group_id
2223 ,p_formula_id => l_budget_fast_formula_id
2224 ,p_action => p_action
2225 ,p_effective_date => l_effective_date
2226 );
2227 -- t_impact_dates is a sorted and unique dates table
2228 -- based on a julian index
2229 -- all duplicates have already been removed during sorting
2230 -- hence duplicate elimination logic need not be implemented here
2231
2232 l_last_change_date := l_effective_date;
2233 l_current :=
2234 t_impact_dates.NEXT(TO_CHAR(l_effective_date, 'J'));
2235
2236 -- 7636627
2237 OPEN csr_max_asg_end_date(p_assignment_id);
2238 FETCH csr_max_asg_end_date INTO l_asg_effective_end_date;
2239 IF csr_max_asg_end_date%NOTFOUND
2240 OR
2241 l_asg_effective_end_date IS NULL
2242 THEN
2243 l_proc_step := 101;
2244 IF g_debug
2245 THEN
2246 debug(l_proc_name, l_proc_step);
2247 END IF;
2248 END IF;
2249 CLOSE csr_max_asg_end_date;
2250 --
2251
2252 WHILE l_current IS NOT NULL
2253 LOOP
2254 l_proc_step := 100 + l_current / 100000;
2255 IF g_debug
2256 THEN
2257 debug(l_proc_name, l_proc_step);
2258 debug('t_impact_dates(l_current): ' || t_impact_dates(l_current));
2259 END IF;
2260
2261 l_this_change_date := t_impact_dates(l_current);
2262 IF (l_this_change_date <= l_last_change_date)
2263 THEN
2264 -- check to ensure that the current processing date is not less than or equal
2265 -- to the previous change date
2266 -- if so , signal error and stop further processing
2267 IF g_debug
2268 THEN
2269 debug(l_proc_name, l_proc_step);
2270 END IF;
2271 RAISE NO_DATA_FOUND;
2272 END IF;
2273
2274
2275 If l_this_change_date <= l_asg_effective_end_date THEN -- 7636627
2276 IF g_is_concurrent_program_run and p_action <> c_verify
2277 THEN
2278 g_output_file_records(g_output_file_records.LAST + 1).assignment_id :=
2279 p_assignment_id;
2280 g_output_file_records(g_output_file_records.LAST).uom :=
2281 g_output_file_records(g_output_file_records.LAST - 1).uom;
2282 g_output_file_records(g_output_file_records.LAST).employee_number :=
2283 g_output_file_records(g_output_file_records.LAST - 1).employee_number;
2284 g_output_file_records(g_output_file_records.LAST).assignment_number :=
2285 g_output_file_records(g_output_file_records.LAST - 1).assignment_number;
2286 g_output_file_records(g_output_file_records.LAST).effective_date :=
2287 t_impact_dates(l_current);
2288 END IF;
2289
2290 update_value_for_event_dates(p_uom => p_uom
2291 ,p_assignment_id => p_assignment_id
2292 ,p_business_group_id => g_business_group_id
2293 ,p_formula_id => l_budget_fast_formula_id
2294 ,p_action => p_action
2295 ,p_effective_date => t_impact_dates(l_current
2296 )
2297 );
2298 End IF;
2299 l_current := t_impact_dates.NEXT(l_current);
2300 END LOOP; -- WHILE l_current IS NOT NULL
2301
2302 --ROLLBACK TO maintain_abv_savepoint;
2303
2304 -- when action is VERIFY and the run is succesful enter status in output record
2305 IF p_action = c_verify AND g_is_concurrent_program_run THEN
2306 g_output_file_records(g_output_file_records.LAST).status :=
2307 'Verified';
2308 END IF;
2309
2310 IF g_is_concurrent_program_run THEN
2311 -- write the output records
2312 write_output_file_records;
2313 END IF;
2314
2315 IF g_debug
2316 THEN
2317 debug_exit(l_proc_name);
2318 END IF;
2319
2320 EXCEPTION
2321 WHEN OTHERS
2322 THEN
2323 clear_cache;
2324 IF SQLCODE <> hr_utility.hr_error_number
2325 THEN
2326 debug_others(l_proc_name, l_proc_step);
2327 IF g_debug THEN
2328 debug('Leaving: ' || l_proc_name, -999);
2329 END IF;
2330 IF g_is_concurrent_program_run THEN
2331 g_output_file_records(g_output_file_records.LAST).status :=
2332 'Errored(Fatal)';
2333 g_output_file_records(g_output_file_records.LAST).MESSAGE :=
2334 l_proc_name
2335 || '{'
2336 || fnd_number.number_to_canonical(l_proc_step)
2337 || '}: '
2338 || SUBSTRB(SQLERRM, 1, 2000);
2339
2340 fnd_file.put_line(fnd_file.LOG
2341 , RPAD(NVL(g_output_file_records(g_output_file_records.LAST
2342 ).employee_number
2343 , 'Asg_Id:' || p_assignment_id
2344 )
2345 ,15
2346 ,' '
2347 )
2348 || g_column_separator
2349 || RPAD(g_output_file_records(g_output_file_records.LAST
2350 ).MESSAGE
2351 ,400
2352 ,' '
2353 )
2354 );
2355 write_output_file_records;
2356 g_output_file_records.DELETE;
2357 END IF;
2358 hr_utility.raise_error;
2359
2360 ELSE
2361 IF g_is_concurrent_program_run THEN
2362 g_output_file_records(g_output_file_records.LAST).status :=
2363 'Errored';
2364 g_output_file_records(g_output_file_records.LAST).MESSAGE :=
2365 hr_utility.get_message;
2366
2367 fnd_file.put_line(fnd_file.LOG
2368 , RPAD(NVL(g_output_file_records(g_output_file_records.LAST
2369 ).employee_number
2370 , 'Asg_Id:' || p_assignment_id
2371 )
2372 ,15
2373 ,' '
2374 )
2375 || g_column_separator
2376 || RPAD(g_output_file_records(g_output_file_records.LAST
2377 ).MESSAGE
2378 ,400
2379 ,' '
2380 )
2381 );
2382 write_output_file_records;
2383 g_output_file_records.DELETE; -- do not include in clear cache
2384 END IF;
2385 RAISE;
2386 END IF;
2387 END maintain_abv_for_assignment;
2388
2389 ---------------------------------------------------------------------------
2390 -----------------GET_EVENT_DATES-----------------------------------------
2391 ---------------------------------------------------------------------------
2392
2393 PROCEDURE get_event_dates(
2394 p_uom IN VARCHAR2
2395 ,p_assignment_id IN NUMBER
2396 ,p_business_group_id IN NUMBER
2397 ,p_event_dates_source IN VARCHAR2
2398 ,p_event_group_id IN NUMBER
2399 ,p_custom_function IN VARCHAR2
2400 ,p_effective_date IN DATE
2401 ,p_impact_dates IN OUT NOCOPY t_indexed_dates
2402 )
2403 IS
2404 l_proc_step NUMBER(20, 10) := 0;
2405 l_proc_name VARCHAR2(61)
2406 := g_package_name || 'get_event_dates';
2407
2408 c_custom_function CONSTANT VARCHAR2(30) := 'A Custom Function';
2409 c_event_group CONSTANT VARCHAR2(30) := 'An Event Group';
2410 c_custom_event_group CONSTANT VARCHAR2(30) := 'Both Event Group and Function';
2411
2412 t_event_dates pqp_table_of_dates;
2413 BEGIN
2414 IF g_debug
2415 THEN
2416 debug_enter(l_proc_name, l_proc_step);
2417 debug('p_uom: ' || p_uom);
2418 debug('p_assignment_id: ' || p_assignment_id);
2419 debug('p_business_group_id: ' || p_business_group_id);
2420 debug('p_event_dates_source: ' || p_event_dates_source);
2421 debug('p_event_group_id: ' || p_event_group_id);
2422 debug('p_custom_function: ' || p_custom_function);
2423 debug('p_effective_date: ' || p_effective_date);
2424 END IF;
2425
2426 -- branch on event dates source in configuration values
2427 -- event group
2428 -- custom function
2429 -- event group and custom function
2430 IF (p_event_dates_source = c_event_group)
2431 THEN
2432 --
2433 -- event dates source is payroll events
2434 --
2435 l_proc_step := 10;
2436
2437 IF g_debug
2438 THEN
2439 debug(l_proc_name, l_proc_step);
2440 END IF;
2441
2442 -- here p_impact dates is passed by reference
2443 -- and will be populated with the final set of dates
2444 -- sort_event_dates procedure cannot be used for this
2445 -- as the arguements passed to it are of type nested
2446 -- table and index by table and in this case we would
2447 -- require both to be index by tables
2448 get_change_dates_from_dti(p_assignment_id => p_assignment_id
2449 ,p_business_group_id => p_business_group_id
2450 ,p_event_group_id => p_event_group_id
2451 ,p_calculation_date => p_effective_date
2452 ,p_impact_dates => p_impact_dates
2453 );
2454 ELSIF(p_event_dates_source = c_custom_function)
2455 THEN
2456 --
2457 -- event dates source is custom function
2458 --
2459 l_proc_step := 20;
2460
2461 IF g_debug
2462 THEN
2463 debug(l_proc_name, l_proc_step);
2464 END IF;
2465
2466 --
2467 -- here the sort function will be used to sort the dates returned
2468 -- by custom function in t_event_dates into p_impact_dates
2469 execute_custom_function(p_uom => p_uom
2470 ,p_assignment_id => p_assignment_id
2471 ,p_business_group_id => p_business_group_id
2472 ,p_custom_function => p_custom_function
2473 ,p_effective_date => p_effective_date
2474 ,p_event_dates => t_event_dates
2475 );
2476 sort_event_dates(p_base_table => p_impact_dates
2477 ,p_compare_table => t_event_dates
2478 );
2479 ELSIF(p_event_dates_source = c_custom_event_group)
2480 THEN
2481 --
2482 -- dates will be fetched using both custom function
2483 -- and datetrack interpreter
2484 --
2485 l_proc_step := 30;
2486
2487 IF g_debug
2488 THEN
2489 debug(l_proc_name, l_proc_step);
2490 END IF;
2491
2492 --
2493 -- get_change_dates_from_dti populates
2494 -- p_impact_dates with sorted impact dates
2495 --
2496 get_change_dates_from_dti(p_assignment_id => p_assignment_id
2497 ,p_business_group_id => p_business_group_id
2498 ,p_event_group_id => p_event_group_id
2499 ,p_calculation_date => p_effective_date
2500 ,p_impact_dates => p_impact_dates
2501 );
2502 --
2503 -- execute_custom_function will populate dates in t_event_dates
2504 --
2505 execute_custom_function(p_uom => p_uom
2506 ,p_assignment_id => p_assignment_id
2507 ,p_business_group_id => p_business_group_id
2508 ,p_custom_function => p_custom_function
2509 ,p_effective_date => p_effective_date
2510 ,p_event_dates => t_event_dates
2511 );
2512 --
2513 --
2514 sort_event_dates(p_base_table => p_impact_dates
2515 ,p_compare_table => t_event_dates
2516 );
2517 ELSE
2518 --
2519 --error check, code should never reach here
2520 --
2521 IF g_debug
2522 THEN
2523 debug('Invalid value for Event Dates Source.');
2524 END IF;
2525 END IF; --IF (l_event_dates_source = 'P') THEN
2526
2527 IF g_debug
2528 THEN
2529 debug_exit(l_proc_name);
2530 END IF;
2531 EXCEPTION
2532 WHEN OTHERS
2533 THEN
2534 clear_cache;
2535
2536 IF SQLCODE <> hr_utility.hr_error_number
2537 THEN
2538 debug_others(l_proc_name, l_proc_step);
2539
2540 IF g_debug
2541 THEN
2542 debug('Leaving: ' || l_proc_name, -999);
2543 END IF;
2544
2545 hr_utility.raise_error;
2546 ELSE
2547 RAISE;
2548 END IF;
2549 END get_event_dates;
2550
2551 ---------------------------------------------------------------------------------
2552 ----------PAYROLL EVENT DATES -----------------------
2553 ---------------------------------------------------------------------------------
2554 PROCEDURE get_change_dates_from_dti(
2555 p_assignment_id IN NUMBER
2556 ,p_business_group_id IN NUMBER
2557 ,p_event_group_id IN NUMBER
2558 ,p_calculation_date IN DATE
2559 ,p_impact_dates IN OUT NOCOPY t_indexed_dates
2560 )
2561 IS
2562 l_proc_step NUMBER(20, 10) := 0;
2563 l_proc_name VARCHAR2(61)
2564 := g_package_name || 'get_change_dates_from_dti';
2565
2566 l_event_group_name pay_event_groups.event_group_name%TYPE;
2567 l_no_of_events NUMBER; -- count of total number of events tracked
2568 l_cntr NUMBER;
2569 l_proration_dates pay_interpreter_pkg.t_proration_dates_table_type;
2570 l_proration_changes pay_interpreter_pkg.t_proration_type_table_type;
2571
2572 CURSOR csr_event_group_name(p_event_group_id NUMBER)
2573 IS
2574 SELECT event_group_name
2575 FROM pay_event_groups
2576 WHERE event_group_id = p_event_group_id;
2577 BEGIN
2578 IF g_debug
2579 THEN
2580 debug_enter(l_proc_name);
2581 debug('p_assignment_id: ' || p_assignment_id);
2582 debug('p_business_group_id: ' || p_business_group_id);
2583 debug('p_event_group_id: ' || p_event_group_id);
2584 debug('p_calculation_date: ' || p_calculation_date);
2585 debug('p_process_mode: ENTRY_EFFECTIVE_DATE');
2586 debug('p_start_date: ' || p_calculation_date);
2587 debug('p_end_date: ' || hr_api.g_eot);
2588 END IF;
2589
2590 --
2591 --required to know if the entry exists at/after p_calculation_date regardless of when it is created
2592 --hence processing mode used will be ENTRY_EFFECTIVE_DATE
2593 --
2594 OPEN csr_event_group_name(p_event_group_id);
2595 FETCH csr_event_group_name INTO l_event_group_name;
2596 IF csr_event_group_name%NOTFOUND
2597 THEN
2598 l_proc_step := 10;
2599 IF g_debug
2600 THEN
2601 debug(l_proc_name, l_proc_step);
2602 END IF;
2603 CLOSE csr_event_group_name;
2604 RAISE NO_DATA_FOUND;
2605 END IF;
2606 CLOSE csr_event_group_name;
2607
2608 IF g_debug
2609 THEN
2610 debug('l_event_group_name: ' || l_event_group_name);
2611 END IF;
2612
2613 l_no_of_events :=
2614 pqp_utilities.get_events(p_assignment_id => p_assignment_id
2615 ,p_element_entry_id => NULL
2616 ,p_business_group_id => p_business_group_id
2617 ,p_process_mode => 'ENTRY_EFFECTIVE_DATE'
2618 ,p_event_group_name => l_event_group_name
2619 ,p_start_date => p_calculation_date
2620 ,p_end_date => hr_api.g_eot -- hardcoded as end of time
2621 ,t_proration_dates => l_proration_dates -- OUT
2622 ,t_proration_change_type => l_proration_changes -- OUT
2623 );
2624
2625 l_proc_step := 20;
2626 IF g_debug
2627 THEN
2628 debug(l_proc_name, l_proc_step);
2629 debug('l_no_of_events: ' || l_no_of_events);
2630 END IF;
2631
2632 -- clear global cache of dates before populating
2633 -- this is a required step as the final table of dates must be on julian index
2634 p_impact_dates.DELETE;
2635
2636 l_cntr := l_proration_dates.FIRST;
2637
2638 WHILE l_cntr IS NOT NULL
2639 LOOP
2640 l_proc_step :=
2641 20
2642 + l_cntr / 100000;
2643
2644 IF g_debug
2645 THEN
2646 debug(l_proc_name, l_proc_step);
2647 END IF;
2648
2649 p_impact_dates(TO_CHAR(l_proration_dates(l_cntr), 'j')) :=
2650 l_proration_dates(l_cntr);
2651 l_cntr :=
2652 l_proration_dates.NEXT(l_cntr);
2653 END LOOP;
2654
2655 IF g_debug
2656 THEN
2657 l_cntr := p_impact_dates.FIRST;
2658
2659 WHILE l_cntr IS NOT NULL
2660 LOOP
2661 debug('p_impact_dates(l_cntr): ' || p_impact_dates(l_cntr));
2662 l_cntr := p_impact_dates.NEXT(l_cntr);
2663 END LOOP;
2664
2665 debug_exit(l_proc_name);
2666 END IF;
2667 EXCEPTION
2668 WHEN OTHERS
2669 THEN
2670 clear_cache;
2671
2672 IF SQLCODE <> hr_utility.hr_error_number
2673 THEN
2674 debug_others(l_proc_name, l_proc_step);
2675
2676 IF g_debug
2677 THEN
2678 debug('Leaving: ' || l_proc_name, -999);
2679 END IF;
2680
2681 hr_utility.raise_error;
2682 ELSE
2683 RAISE;
2684 END IF;
2685 END get_change_dates_from_dti;
2686
2687 --------------------------------------------------------------------------
2688 ----------------PROCEDURE FOR DYNAMIC EXECUTION OF CUSTOM FUNCTION-------
2689 --------------------------------------------------------------------------
2690 PROCEDURE execute_custom_function(
2691 p_uom IN VARCHAR2
2692 ,p_assignment_id IN NUMBER
2693 ,p_business_group_id IN NUMBER
2694 ,p_custom_function IN VARCHAR2
2695 ,p_effective_date IN DATE
2696 ,p_event_dates IN OUT NOCOPY pqp_table_of_dates
2697 )
2698 IS
2699 l_proc_step NUMBER(20, 10) := 0;
2700 l_proc_name VARCHAR2(61)
2701 := g_package_name || 'execute_custom_function';
2702
2703 sqlstr VARCHAR2(1000);
2704 BEGIN
2705 IF g_debug
2706 THEN
2707 debug_enter(l_proc_name);
2708 debug('p_uom: ' || p_uom);
2709 debug('p_assignment_id: ' || p_assignment_id);
2710 debug('p_business_group_id: ' || p_business_group_id);
2711 debug('p_custom_function: ' || p_custom_function);
2712 debug('p_effective_date: ' || p_effective_date);
2713 END IF;
2714
2715 --
2716 -- dynamic function template
2717 --
2718 -- PROCEDURE get_fte_event_dates
2719 -- ( p_uom IN VARCHAR2
2720 -- ,p_assignment_id IN NUMBER
2721 -- ,p_business_group_id IN NUMBER
2722 -- ,p_effective_date IN DATE
2723 -- ,p_event_dates IN OUT NOCOPY pqp_table_of_dates
2724 -- ) RETURN NUMBER;
2725
2726 -- hardwired function call
2727 -- IF g_definition_data_record.custom_function_name = 'pqp_budget_maintenance.get_fte_event_dates' THEN
2728 --
2729 -- get_fte_event_dates
2730 -- ( p_uom => p_uom
2731 -- ,p_assignment_id => p_assignment_id
2732 -- ,p_business_group_id => p_business_group_id
2733 -- ,p_effective_date => p_effective_date
2734 -- ,p_event_dates => p_event_dates
2735 -- );
2736 --
2737
2738 IF g_debug
2739 THEN
2740 l_proc_step := 10;
2741 debug(l_proc_name, l_proc_step);
2742 END IF;
2743
2744 sqlstr :=
2745 'BEGIN '
2746 || p_custom_function
2747 || '( :uom, :assignment_id, :business_group_id, :effective_date, :p_event_dates); END;';
2748
2749 IF g_debug
2750 THEN
2751 debug('sqlstr: ' || sqlstr);
2752 END IF;
2753
2754 EXECUTE IMMEDIATE sqlstr
2755 USING p_uom
2756 , p_assignment_id
2757 , p_business_group_id
2758 , p_effective_date
2759 ,IN OUT p_event_dates;
2760
2761 IF g_debug
2762 THEN
2763 debug_exit(l_proc_name);
2764 END IF;
2765 EXCEPTION
2766 WHEN OTHERS
2767 THEN
2768 clear_cache;
2769
2770 IF SQLCODE <> hr_utility.hr_error_number
2771 THEN
2772 debug_others(l_proc_name, l_proc_step);
2773
2774 IF g_debug
2775 THEN
2776 debug('Leaving: ' || l_proc_name, -999);
2777 END IF;
2778
2779 hr_utility.raise_error;
2780 ELSE
2781 RAISE;
2782 END IF;
2783 END execute_custom_function;
2784
2785 ----------------------------------------------------------
2786 --------------GET_FTE_EVENT_DATES--------------------------
2787 ----------------------------------------------------------
2788
2789 PROCEDURE get_fte_event_dates(
2790 p_uom IN VARCHAR2
2791 ,p_assignment_id IN NUMBER
2792 ,p_business_group_id IN NUMBER
2793 ,p_effective_date IN DATE
2794 ,p_event_dates IN OUT NOCOPY pqp_table_of_dates
2795 )
2796 IS
2797 l_coverage pqp_configuration_values.pcv_information1%TYPE;
2798 l_proc_step NUMBER(20, 10) := 0;
2799 l_proc_name VARCHAR2(61)
2800 := g_package_name || 'get_fte_event_dates';
2801
2802 CURSOR csr_pqp_contract_table(p_legislation_code VARCHAR2)
2803 IS
2804 SELECT user_table_id
2805 FROM pay_user_tables
2806 WHERE user_table_name = 'PQP_CONTRACT_TYPES'
2807 AND legislation_code = p_legislation_code;
2808
2809 CURSOR csr_assignment_contract(
2810 p_assignment_id NUMBER
2811 ,p_effective_date DATE
2812 ,p_pqp_contract_table_id NUMBER
2813 )
2814 IS
2815 SELECT pur.user_row_id
2816 FROM pqp_assignment_attributes_f aat, pay_user_rows_f pur
2817 WHERE aat.assignment_id = p_assignment_id
2818 AND p_effective_date BETWEEN aat.effective_start_date
2819 AND aat.effective_end_date
2820 AND pur.user_table_id = p_pqp_contract_table_id
2821 AND pur.business_group_id = aat.business_group_id
2822 AND pur.row_low_range_or_name = aat.contract_type
2823 AND aat.effective_start_date BETWEEN pur.effective_start_date
2824 AND pur.effective_end_date;
2825
2826 CURSOR csr_get_udt_change_dates(
2827 p_effective_start_date IN DATE
2828 ,p_pqp_contract_table_id IN NUMBER
2829 ,p_user_row_id IN NUMBER
2830 )
2831 IS
2832 SELECT inst2.effective_start_date
2833 FROM pay_user_column_instances_f inst1
2834 ,pay_user_column_instances_f inst2
2835 WHERE ( inst1.effective_start_date >= p_effective_start_date
2836 OR p_effective_start_date BETWEEN inst1.effective_start_date
2837 AND inst1.effective_end_date
2838 )
2839 AND inst1.user_row_id = p_user_row_id
2840 AND inst2.user_column_instance_id =
2841 inst1.user_column_instance_id
2842 AND inst2.effective_start_date = inst1.effective_end_date + 1
2843 AND NVL(inst2.VALUE, '{null}') <> NVL(inst1.VALUE, '~NULL~')
2844 ORDER BY 1;
2845
2846 --
2847 CURSOR csr_get_all_change_dates(
2848 p_assignment_id IN NUMBER
2849 ,p_effective_start_date IN DATE
2850 ,p_pqp_contract_table_id IN NUMBER
2851 ,p_user_row_id IN NUMBER
2852 )
2853 IS
2854 SELECT asg2.effective_start_date
2855 FROM per_all_assignments_f asg1, per_all_assignments_f asg2
2856 WHERE asg1.assignment_id = p_assignment_id
2857 AND ( asg1.effective_start_date >= p_effective_start_date
2858 OR p_effective_start_date BETWEEN asg1.effective_start_date
2859 AND asg1.effective_end_date
2860 )
2861 AND asg2.assignment_id = asg1.assignment_id
2862 AND asg2.effective_start_date = asg1.effective_end_date + 1
2863 AND NVL(asg2.normal_hours, -1) <> NVL(asg1.normal_hours, -2)
2864 UNION ALL
2865 SELECT aat2.effective_start_date
2866 FROM pqp_assignment_attributes_f aat1
2867 ,pqp_assignment_attributes_f aat2
2868 WHERE aat1.assignment_id = p_assignment_id
2869 AND ( aat1.effective_start_date >= p_effective_start_date
2870 OR p_effective_start_date BETWEEN aat1.effective_start_date
2871 AND aat1.effective_end_date
2872 )
2873 AND aat1.assignment_id = aat2.assignment_id
2874 AND aat2.effective_start_date = aat1.effective_end_date + 1
2875 AND NVL(aat2.contract_type, '{null}') <>
2876 NVL(aat1.contract_type, '[NULL]')
2877 UNION ALL
2878 SELECT inst2.effective_start_date
2879 FROM pay_user_column_instances_f inst1
2880 ,pay_user_column_instances_f inst2
2881 WHERE ( inst1.effective_start_date >= p_effective_start_date
2882 OR p_effective_start_date BETWEEN inst1.effective_start_date
2883 AND inst1.effective_end_date
2884 )
2885 AND inst1.user_row_id = p_user_row_id
2886 AND inst2.user_column_instance_id =
2887 inst1.user_column_instance_id
2888 AND inst2.effective_start_date = inst1.effective_end_date + 1
2889 AND NVL(inst2.VALUE, '{null}') <> NVL(inst1.VALUE, '~NULL~')
2890 ORDER BY 1;
2891
2892 l_legislation_code VARCHAR2(10);
2893 --
2894 l_pqp_contract_table_id pay_user_tables.user_table_id%TYPE;
2895 --
2896 l_assignment_contract csr_assignment_contract%ROWTYPE;
2897
2898 c_udt CONSTANT pqp_configuration_values.pcv_information1%TYPE
2899 := 'User Table Values';
2900 c_assignment_udt CONSTANT pqp_configuration_values.pcv_information1%TYPE
2901 := 'Assignment, User Table Values';
2902 l_maintenance_information csr_get_configuration_data%ROWTYPE;
2903 l_count NUMBER;
2904 l_log_string VARCHAR2(4000);
2905 BEGIN
2906 IF g_debug
2907 THEN
2908 debug_enter(l_proc_name);
2909 debug('p_uom: ' || p_uom);
2910 debug('p_assignment_id: ' || p_assignment_id);
2911 debug('p_business_group_id: ' || p_business_group_id);
2912 debug('p_effective_date: ' || p_effective_date);
2913 END IF;
2914
2915 OPEN get_legislation_code(p_business_group_id);
2916 FETCH get_legislation_code INTO l_legislation_code;
2917 CLOSE get_legislation_code;
2918
2919 l_proc_step := 10;
2920 IF g_debug THEN
2921 debug(l_proc_name,l_proc_step);
2922 debug('l_legislation_code: '||l_legislation_code);
2923 debug('p_uom: ' || p_uom);
2924 debug( 'g_additional_information.pcv_information1: '
2925 || g_additional_information.pcv_information1
2926 );
2927 END IF;
2928
2929 IF (p_uom <> NVL(g_additional_information.pcv_information1, '~null'))
2930 THEN
2931 -- empty cache of configuration values before populating
2932 -- include check for when to load
2933 g_additional_information := NULL;
2934 g_additional_config_id := NULL;
2935
2936 l_proc_step := 20;
2937 IF g_debug
2938 THEN
2939 debug(l_proc_name, l_proc_step);
2940 END IF;
2941
2942 load_cache(p_uom => p_uom
2943 ,p_business_group_id => p_business_group_id
2944 ,p_legislation_code => l_legislation_code
2945 ,p_information_category => c_abvm_fte_additional
2946 ,p_configuration_data => g_additional_information
2947 );
2948
2949 l_proc_step := 30;
2950 IF g_debug
2951 THEN
2952 debug(l_proc_name, l_proc_step);
2953 END IF;
2954
2955 l_log_string := NULL;
2956 IF g_is_concurrent_program_run
2957 THEN
2958 fnd_file.put_line(fnd_file.LOG
2959 , RPAD('Seeded FTE Configuration', 30, ' ')
2960 || ': '
2961 || g_additional_information.configuration_name
2962 );
2963 fnd_file.put_line(fnd_file.LOG
2964 , RPAD('Coverage', 30, ' ') || ': ' || g_additional_information.pcv_information2
2965 );
2966 END IF;
2967 END IF; -- IF (p_uom <> NVL(g_additional_information.pcv_information1, '~null'))
2968
2969 g_additional_config_id :=
2970 g_additional_information.configuration_value_id;
2971 l_coverage := g_additional_information.pcv_information2;
2972
2973 l_proc_step := 40;
2974 IF g_debug
2975 THEN
2976 debug(l_proc_name, l_proc_step);
2977 debug('g_additional_config_id: ' || g_additional_config_id);
2978 debug('l_coverage: ' || l_coverage);
2979 END IF;
2980
2981 OPEN csr_pqp_contract_table(p_legislation_code => l_legislation_code);
2982 FETCH csr_pqp_contract_table INTO l_pqp_contract_table_id;
2983 IF csr_pqp_contract_table%NOTFOUND
2984 THEN
2985 l_proc_step := 50;
2986 IF g_debug
2987 THEN
2988 debug(l_proc_name, l_proc_step);
2989 END IF;
2990 CLOSE csr_pqp_contract_table;
2991 RAISE NO_DATA_FOUND;
2992 END IF;
2993 CLOSE csr_pqp_contract_table;
2994
2995 l_proc_step := 60;
2996 IF g_debug
2997 THEN
2998 debug(l_proc_name, l_proc_step);
2999 debug('l_pqp_contract_table_id:' || l_pqp_contract_table_id);
3000 END IF;
3001
3002 OPEN csr_assignment_contract(p_assignment_id => p_assignment_id
3003 ,p_effective_date => p_effective_date
3004 ,p_pqp_contract_table_id => l_pqp_contract_table_id
3005 );
3006 FETCH csr_assignment_contract INTO l_assignment_contract;
3007 IF csr_assignment_contract%NOTFOUND
3008 THEN
3009 l_proc_step := 70;
3010 IF g_debug
3011 THEN
3012 debug(l_proc_name, l_proc_step);
3013 END IF;
3014 CLOSE csr_assignment_contract;
3015
3016 load_cache(p_uom => p_uom
3017 ,p_business_group_id => g_business_group_id
3018 ,p_legislation_code => g_legislation_code
3019 ,p_information_category => c_abvm_maintenance
3020 ,p_configuration_data => l_maintenance_information
3021 );
3022
3023 IF g_debug
3024 THEN
3025 debug( 'l_maintenance_information.pcv_information1: '
3026 || l_maintenance_information.pcv_information1
3027 );
3028 debug( 'l_maintenance_information.pcv_information2: '
3029 || l_maintenance_information.pcv_information2
3030 );
3031 END IF;
3032
3033 IF g_is_concurrent_program_run
3034 OR l_maintenance_information.pcv_information2 = 'Y'
3035 THEN
3036 l_proc_step := 75;
3037 IF g_debug
3038 THEN
3039 debug(l_proc_name, l_proc_step);
3040 END IF;
3041 hr_utility.set_message(8303, 'PQP_230113_AAT_MISSING_CONTRCT');
3042 hr_utility.set_message_token('EFFECTIVEDATE'
3043 ,fnd_date.date_to_displaydate(p_effective_date
3044 )
3045 );
3046 hr_utility.raise_error;
3047 END IF;
3048
3049 END IF;
3050 CLOSE csr_assignment_contract;
3051
3052
3053 l_proc_step := 80;
3054 IF g_debug
3055 THEN
3056 debug(l_proc_name, l_proc_step);
3057 debug( 'l_assignment_contract.user_row_id:'
3058 || l_assignment_contract.user_row_id
3059 );
3060 END IF;
3061
3062 --
3063 --coverage values 'User Table Values' Table Values PQP_CONTRACT_TYPES
3064 -- 'Assignment, User Table Values' Assignment Details,Extra Details Of Service, PQP_CONTRACT_TYPES
3065 --
3066
3067 IF l_coverage = c_udt
3068 THEN
3069 l_proc_step := 90;
3070
3071 IF g_debug
3072 THEN
3073 debug(l_proc_name, l_proc_step);
3074 debug( 'p_effective_date:'
3075 || fnd_date.date_to_canonical(p_effective_date)
3076 );
3077 debug('l_pqp_contract_table_id:' || l_pqp_contract_table_id);
3078 debug( 'l_assignment_contract.user_row_id:'
3079 || l_assignment_contract.user_row_id
3080 );
3081 END IF;
3082
3083 OPEN csr_get_udt_change_dates(p_effective_start_date => p_effective_date
3084 ,p_pqp_contract_table_id => l_pqp_contract_table_id
3085 ,p_user_row_id => l_assignment_contract.user_row_id
3086 );
3087 FETCH csr_get_udt_change_dates BULK COLLECT INTO p_event_dates;
3088 CLOSE csr_get_udt_change_dates;
3089 ELSIF l_coverage = c_assignment_udt
3090 THEN
3091 l_proc_step := 100;
3092
3093 IF g_debug
3094 THEN
3095 debug(l_proc_name, l_proc_step);
3096 debug('p_assignment_id: ' || p_assignment_id);
3097 debug('p_effective_date: ' || p_effective_date);
3098 debug('l_pqp_contract_table_id: ' || l_pqp_contract_table_id);
3099 debug( 'l_assignment_contract.user_row_id: '
3100 || l_assignment_contract.user_row_id
3101 );
3102 END IF;
3103
3104 OPEN csr_get_all_change_dates(p_assignment_id => p_assignment_id
3105 ,p_effective_start_date => p_effective_date
3106 ,p_pqp_contract_table_id => l_pqp_contract_table_id
3107 ,p_user_row_id => l_assignment_contract.user_row_id
3108 );
3109 FETCH csr_get_all_change_dates BULK COLLECT INTO p_event_dates;
3110 CLOSE csr_get_all_change_dates;
3111
3112 ELSE -- code should never reach here
3113 IF g_debug
3114 THEN
3115 debug('Invalid value in PQP_ABVM_UOM_ADDITIONAL: COVERAGE');
3116 END IF;
3117 END IF; --IF l_coverage = 'UDT'
3118
3119 l_proc_step := 108;
3120 IF g_debug
3121 THEN
3122 debug(l_proc_name, l_proc_step);
3123 l_count := p_event_dates.FIRST;
3124 WHILE l_count IS NOT NULL
3125 LOOP
3126 IF g_debug
3127 THEN
3128 debug('p_event_dates(l_count): ' || p_event_dates(l_count));
3129 END IF;
3130 l_count := p_event_dates.NEXT(l_count);
3131 END LOOP;
3132
3133 debug_exit(l_proc_name);
3134 END IF;
3135
3136 EXCEPTION
3137 WHEN OTHERS
3138 THEN
3139 clear_cache;
3140 IF SQLCODE <> hr_utility.hr_error_number
3141 THEN
3142 debug_others(l_proc_name, l_proc_step);
3143
3144 IF g_debug
3145 THEN
3146 debug('Leaving: ' || l_proc_name, -999);
3147 END IF;
3148
3149 hr_utility.raise_error;
3150 ELSE
3151 RAISE;
3152 END IF;
3153 END get_fte_event_dates;
3154
3155 ----------------------------------------------------------
3156 -----------UPDATE_VALUE_FOR_EVENT_DATES-------------------
3157 ----------------------------------------------------------
3158 PROCEDURE update_value_for_event_dates(
3159 p_uom IN VARCHAR2
3160 ,p_assignment_id IN NUMBER
3161 ,p_business_group_id IN NUMBER
3162 ,p_formula_id IN NUMBER
3163 ,p_action IN VARCHAR2
3164 ,p_effective_date IN DATE
3165 )
3166 IS
3167 CURSOR csr_formula_name(p_formula_id NUMBER)
3168 IS
3169 SELECT formula_name
3170 FROM ff_formulas_f
3171 WHERE formula_id = p_formula_id;
3172
3173 l_inputs ff_exec.inputs_t; -- fast formula inputs
3174 l_outputs ff_exec.outputs_t; -- fast formula outputs
3175 l_results NUMBER;
3176 l_formula_name ff_formulas_f.formula_name%TYPE;
3177 c_action CONSTANT VARCHAR2(20) := 'Verify';
3178 l_message VARCHAR2(1000);
3179 l_proc_step NUMBER(20, 10) := 0;
3180 l_proc_name VARCHAR2(61)
3181 := g_package_name || 'update_value_for_event_dates';
3182 BEGIN
3183 IF g_debug
3184 THEN
3185 debug_enter(l_proc_name);
3186 debug('p_uom: ' || p_uom);
3187 debug('p_assignment_id: ' || p_assignment_id);
3188 debug('p_business_group_id: ' || p_business_group_id);
3189 debug('p_effective_date: ' || p_effective_date);
3190 debug('p_formula_id: ' || p_formula_id);
3191 END IF;
3192
3193 ff_exec.init_formula(p_formula_id, p_effective_date, l_inputs
3194 ,l_outputs);
3195 l_proc_step := 10;
3196
3197 IF g_debug
3198 THEN
3199 debug(l_proc_name, l_proc_step);
3200 END IF;
3201
3202 FOR l_in_cnt IN l_inputs.FIRST .. l_inputs.LAST
3203 LOOP
3204 -- set formula contexts
3205 l_proc_step := 10 + l_in_cnt/100000;
3206 IF g_debug
3207 THEN
3208 debug(l_proc_name, l_proc_step);
3209 END IF;
3210
3211 IF (l_inputs(l_in_cnt).NAME = 'ASSIGNMENT_ID')
3212 THEN
3213 l_inputs(l_in_cnt).VALUE := p_assignment_id;
3214 ELSIF(l_inputs(l_in_cnt).NAME = 'DATE_EARNED')
3215 THEN
3216 l_inputs(l_in_cnt).VALUE :=
3217 fnd_date.date_to_canonical(p_effective_date);
3218 ELSIF(l_inputs(l_in_cnt).NAME = 'BUSINESS_GROUP_ID')
3219 THEN
3220 l_inputs(l_in_cnt).VALUE := p_business_group_id;
3221 END IF;
3222
3223 IF g_debug
3224 THEN
3225 debug( 'input: '
3226 || l_inputs(l_in_cnt).NAME
3227 || ' = '
3228 || l_inputs(l_in_cnt).VALUE
3229 );
3230 END IF;
3231 END LOOP;
3232
3233 ff_exec.run_formula(l_inputs, l_outputs, FALSE); -- dbi caching set to false
3234 --
3235 -- update the abv value obtained
3236 --
3237 l_proc_step := 20;
3238
3239 IF g_debug
3240 THEN
3241 debug(l_proc_name, l_proc_step);
3242 END IF;
3243
3244 FOR l_out_cnt IN l_outputs.FIRST .. l_outputs.LAST
3245 LOOP
3246 l_proc_step := 25;
3247 IF g_debug THEN
3248 debug(l_proc_name,l_proc_step);
3249 END IF;
3250
3251 IF l_outputs(l_out_cnt).NAME = 'ERROR_MESSAGE'
3252 THEN
3253 IF l_outputs(l_out_cnt).VALUE IS NOT NULL
3254 THEN
3255 --
3256 -- output error message
3257 --
3258 l_proc_step := 30;
3259 IF g_debug
3260 THEN
3261 debug(l_outputs(l_out_cnt).VALUE);
3262 debug(l_proc_name, l_proc_step);
3263 END IF;
3264
3265 OPEN csr_formula_name(p_formula_id);
3266 FETCH csr_formula_name INTO l_formula_name;
3267 CLOSE csr_formula_name;
3268
3269 hr_utility.set_message(8303, 'PQP_230459_ABV_FORMULA_ERROR');
3270 hr_utility.set_message_token('FORMULANAME',l_formula_name);
3271 hr_utility.set_message_token('MESSAGE',l_outputs(l_out_cnt).VALUE);
3272 hr_utility.raise_error;
3273 END IF; -- IF l_outputs(l_out_cnt).value IS NOT NULL THEN
3274
3275 ELSIF( UPPER(l_outputs(l_out_cnt).NAME) =
3276 TRANSLATE(UPPER(hr_general.decode_lookup('BUDGET_MEASUREMENT_TYPE'
3277 ,p_uom)),' ','_')
3278 OR UPPER(l_outputs(l_out_cnt).NAME) = UPPER(p_uom)
3279 )
3280 THEN
3281
3282 l_proc_step := 35;
3283 IF g_debug THEN
3284 debug(l_proc_name,l_proc_step);
3285 END IF;
3286
3287 IF g_is_concurrent_program_run and (p_action <> c_action)
3288 THEN
3289 g_output_file_records(g_output_file_records.LAST).new_budget_value :=
3290 fnd_number.canonical_to_number(l_outputs(l_out_cnt).VALUE);
3291 END IF;
3292
3293 IF l_outputs(l_out_cnt).VALUE IS NOT NULL
3294 THEN
3295 l_proc_step := 40;
3296
3297 IF g_debug
3298 THEN
3299 debug(l_proc_name, l_proc_step);
3300 debug('fnd_number.canonical_to_number(l_outputs(l_out_cnt).VALUE): '||fnd_number.canonical_to_number(l_outputs(l_out_cnt).VALUE));
3301 END IF;
3302
3303 update_and_store_abv(p_uom => p_uom
3304 ,p_assignment_id => p_assignment_id
3305 ,p_business_group_id => p_business_group_id
3306 ,p_abv_value => fnd_number.canonical_to_number(l_outputs(l_out_cnt
3307 ).VALUE) -- bug 4372165
3308 ,p_action => p_action
3309 ,p_effective_date => p_effective_date
3310 );
3311 --
3312 -- else formula has returned a null assignment budget value
3313 --
3314 ELSE
3315 l_proc_step := 50;
3316 IF g_debug
3317 THEN
3318 debug(l_proc_name, l_proc_step);
3319 END IF;
3320 END IF; --IF l_outputs(l_out_cnt).value IS NOT NULL THEN
3321
3322 ELSE -- l_outputs(l_out_cnt).name <> p_uom
3323 l_proc_step := 60;
3324 IF g_debug
3325 THEN
3326 debug(l_proc_name, l_proc_step);
3327 debug('l_outputs(l_out_cnt).NAME: '||l_outputs(l_out_cnt).NAME);
3328 debug('l_outputs(l_out_cnt).VALUE: '||fnd_number.canonical_to_number(l_outputs(l_out_cnt).VALUE));
3329 END IF;
3330
3331 OPEN csr_formula_name(p_formula_id);
3332 FETCH csr_formula_name INTO l_formula_name;
3333 CLOSE csr_formula_name;
3334
3335 hr_utility.set_message(8303, 'PQP_230459_ABV_FORMULA_ERROR');
3336 hr_utility.set_message_token('FORMULANAME',l_formula_name);
3337 l_message := 'The UOM being processed "'||p_uom||'" does not match the UOM "'
3338 ||l_outputs(l_out_cnt).NAME||'" returned';
3339 hr_utility.set_message_token('MESSAGE',l_message);
3340 hr_utility.raise_error;
3341
3342 END IF; --IF l_outputs(l_out_cnt).name = 'ERROR_MESSAGE'
3343 END LOOP;-- FOR l_out_cnt IN l_outputs.FIRST .. l_outputs.LAST
3344
3345 IF g_debug
3346 THEN
3347 debug_exit(l_proc_name);
3348 END IF;
3349 EXCEPTION
3350 WHEN OTHERS
3351 THEN
3352 clear_cache;
3353
3354 IF SQLCODE <> hr_utility.hr_error_number
3355 THEN
3356 debug_others(l_proc_name, l_proc_step);
3357
3358 IF g_debug
3359 THEN
3360 debug('Leaving: ' || l_proc_name, -999);
3361 END IF;
3362
3363 hr_utility.raise_error;
3364 ELSE
3365 RAISE;
3366 END IF;
3367 END update_value_for_event_dates;
3368
3369 -------------------------------------------------------------------
3370 -------------------UPDATE_AND_STORE_ABV----------------------------
3371 -------------------------------------------------------------------
3372
3373 PROCEDURE update_and_store_abv(
3374 p_uom IN VARCHAR2
3375 ,p_assignment_id IN NUMBER
3376 ,p_business_group_id IN NUMBER
3377 ,p_abv_value IN NUMBER
3378 ,p_action IN VARCHAR2
3379 ,p_effective_date IN DATE
3380 )
3381 IS
3382 CURSOR csr_abv_exists(p_assignment_id NUMBER, p_uom VARCHAR2)
3383 IS
3384 SELECT 1
3385 FROM per_assignment_budget_values_f
3386 WHERE assignment_id = p_assignment_id AND unit = p_uom
3387 AND ROWNUM < 2;
3388
3389 CURSOR csr_effective_abv(
3390 p_assignment_id NUMBER
3391 ,p_effective_date DATE
3392 ,p_uom VARCHAR2
3393 )
3394 IS
3395 SELECT assignment_budget_value_id, VALUE, effective_start_date
3396 ,effective_end_date, object_version_number
3397 FROM per_assignment_budget_values_f
3398 WHERE assignment_id = p_assignment_id
3399 AND unit = p_uom
3400 AND p_effective_date BETWEEN effective_start_date
3401 AND effective_end_date;
3402
3403 CURSOR csr_chk_future_abv_rows(
3404 p_assignment_budget_value_id NUMBER
3405 ,p_effective_date DATE
3406 )
3407 IS
3408 SELECT effective_end_date
3409 FROM per_assignment_budget_values_f
3410 WHERE assignment_budget_value_id = p_assignment_budget_value_id
3411 AND effective_start_date > p_effective_date
3412 AND ROWNUM < 2;
3413
3414 l_proc_step NUMBER(20, 10) := 0;
3415 l_proc_name VARCHAR2(61)
3416 := g_package_name || 'update_and_store_abv';
3417 l_exists NUMBER;
3418 l_effective_abv_row csr_effective_abv%ROWTYPE;
3419 l_future_end_date DATE;
3420 l_datetrack_mode VARCHAR2(30);
3421 c_verify CONSTANT VARCHAR2(20) := 'Verify';
3422 BEGIN
3423 IF g_debug
3424 THEN
3425 debug_enter(l_proc_name);
3426 debug('p_uom: ' || p_uom);
3427 debug('p_assignment_id: ' || p_assignment_id);
3428 debug('p_business_group_id: ' || p_business_group_id);
3429 debug('p_effective_date: ' || p_effective_date);
3430 debug('p_abv_value: ' || p_abv_value);
3431 END IF;
3432
3433 --
3434 -- Check if there are already existing abv rows for this assignment
3435 -- and uom
3436 OPEN csr_abv_exists(p_assignment_id => p_assignment_id,
3437 p_uom => p_uom);
3438 FETCH csr_abv_exists INTO l_exists;
3439
3440 IF csr_abv_exists%NOTFOUND AND (p_action <> c_verify)
3441 THEN
3442 -- No existing abv rows
3443 -- Therefore create a new row
3444 -- Datetrack mode = Insert
3445 l_proc_step := 10;
3446
3447 IF g_debug
3448 THEN
3449 debug(l_proc_name, l_proc_step);
3450 END IF;
3451
3452 per_abv_ins.ins(p_effective_date => p_effective_date
3453 ,p_business_group_id => p_business_group_id
3454 ,p_assignment_id => p_assignment_id
3455 ,p_unit => p_uom
3456 ,p_value => p_abv_value
3457 ,p_request_id => NULL
3458 ,p_program_application_id => NULL
3459 ,p_program_id => NULL
3460 ,p_program_update_date => NULL
3461 ,p_assignment_budget_value_id => l_effective_abv_row.assignment_budget_value_id
3462 ,p_object_version_number => l_effective_abv_row.object_version_number
3463 ,p_effective_start_date => l_effective_abv_row.effective_start_date
3464 ,p_effective_end_date => l_effective_abv_row.effective_end_date
3465 );
3466
3467 IF g_is_concurrent_program_run
3468 THEN
3469 g_output_file_records(g_output_file_records.LAST).status :=
3470 'Processed';
3471 g_output_file_records(g_output_file_records.LAST).change_type :=
3472 'INSERT';
3473 END IF;
3474 ELSIF csr_abv_exists%FOUND
3475 THEN
3476 l_proc_step := 20;
3477
3478 IF g_debug
3479 THEN
3480 debug(l_proc_name, l_proc_step);
3481 END IF;
3482
3483 -- Obtain data for already existing ABV row
3484 --
3485 OPEN csr_effective_abv(p_assignment_id => p_assignment_id
3486 ,p_effective_date => p_effective_date
3487 ,p_uom => p_uom
3488 );
3489 FETCH csr_effective_abv INTO l_effective_abv_row;
3490
3491 IF csr_effective_abv%NOTFOUND
3492 THEN
3493 -- Indicates that as of the effective date passed there is no
3494 -- existing ABV rows and yet there are future ABV rows existing
3495 -- Error out, as this is not a valid case
3496 l_proc_step :=
3497 30;
3498
3499 IF g_debug
3500 THEN
3501 debug(l_proc_name, l_proc_step);
3502 END IF;
3503 CLOSE csr_effective_abv;
3504 hr_utility.set_message(8303, 'PQP_230460_ABV_FUTURE_ROWS');
3505 hr_utility.set_message_token('ABVUOM', p_uom);
3506 hr_utility.raise_error;
3507 END IF; -- IF csr_effective_abv%NOTFOUND
3508
3509 CLOSE csr_effective_abv;
3510 IF p_action <> c_verify THEN
3511 IF g_debug
3512 THEN
3513 debug('ROUND(p_abv_value,5):' || ROUND(p_abv_value, 5));
3514 debug('p_effective_date: ' || p_effective_date);
3515 debug( 'l_effective_abv_row.assignment_budget_value_id:'
3516 || l_effective_abv_row.assignment_budget_value_id
3517 );
3518 debug( 'l_effective_abv_row.object_version_number:'
3519 || l_effective_abv_row.object_version_number
3520 );
3521 debug( 'l_effective_abv_row.effective_start_date:'
3522 || fnd_date.date_to_canonical(l_effective_abv_row.effective_start_date
3523 )
3524 );
3525 debug( 'l_effective_abv_row.effective_end_date:'
3526 || fnd_date.date_to_canonical(l_effective_abv_row.effective_end_date
3527 )
3528 );
3529 debug('l_effective_abv_row.value:' || l_effective_abv_row.VALUE);
3530 END IF;
3531
3532 IF g_is_concurrent_program_run
3533 THEN
3534 g_output_file_records(g_output_file_records.LAST).old_budget_value :=
3535 l_effective_abv_row.VALUE;
3536 END IF;
3537
3538 -- Obtain details of existing future ABV rows
3539 --
3540 OPEN csr_chk_future_abv_rows(l_effective_abv_row.assignment_budget_value_id
3541 ,p_effective_date
3542 );
3543 FETCH csr_chk_future_abv_rows INTO l_future_end_date;
3544
3545 IF csr_chk_future_abv_rows%FOUND
3546 THEN
3547 --
3548 --
3549 -- For updates, if future rows exist, use update override.
3550 -- This has been agreed as a valid requirement
3551 --
3552 l_datetrack_mode := 'UPDATE_OVERRIDE';
3553 ELSE
3554 IF (l_effective_abv_row.effective_start_date <> p_effective_date)
3555 THEN
3556 l_datetrack_mode := 'UPDATE';
3557 ELSE
3558 l_datetrack_mode := 'CORRECTION';
3559 END IF;
3560 END IF; --IF csr_chk_future_abv_rows%FOUND
3561
3562 CLOSE csr_chk_future_abv_rows;
3563 l_proc_step := 40;
3564
3565 IF g_debug
3566 THEN
3567 debug(l_proc_name, l_proc_step);
3568 debug('l_datetrack_mode: ' || l_datetrack_mode);
3569 END IF;
3570
3571 IF l_datetrack_mode <> 'UPDATE_OVERRIDE'
3572 THEN
3573 --
3574 -- only do a datetrack UPDATE or correction if the value is different
3575 --
3576 l_proc_step := 45;
3577
3578 IF g_debug
3579 THEN
3580 debug(l_proc_name, l_proc_step);
3581 debug( 'ROUND(fnd_number.canonical_to_number(l_effective_abv_row.value),5): '
3582 || ROUND(fnd_number.canonical_to_number(l_effective_abv_row.VALUE), 5)
3583 ); -- bug 4372165
3584 debug('ROUND(p_abv_value,5): ' || ROUND(p_abv_value, 5));
3585 END IF;
3586
3587 IF ROUND(fnd_number.canonical_to_number(l_effective_abv_row.VALUE), 5) <> ROUND(p_abv_value, 5)
3588 THEN
3589 l_proc_step := 50;
3590
3591 IF g_debug
3592 THEN
3593 debug(l_proc_name, l_proc_step);
3594 END IF;
3595
3596 per_abv_upd.upd(p_effective_date => p_effective_date
3597 ,p_datetrack_mode => l_datetrack_mode
3598 ,p_assignment_budget_value_id => l_effective_abv_row.assignment_budget_value_id
3599 ,p_object_version_number => l_effective_abv_row.object_version_number
3600 ,p_unit => p_uom
3601 ,p_value => p_abv_value
3602 ,p_request_id => NULL
3603 ,p_program_application_id => NULL
3604 ,p_program_id => NULL
3605 ,p_program_update_date => NULL
3606 ,p_effective_start_date => l_effective_abv_row.effective_start_date
3607 ,p_effective_end_date => l_effective_abv_row.effective_end_date
3608 );
3609
3610 IF g_is_concurrent_program_run
3611 THEN
3612 g_output_file_records(g_output_file_records.LAST).status :=
3613 'Processed';
3614 g_output_file_records(g_output_file_records.LAST).change_type :=
3615 l_datetrack_mode;
3616 END IF;
3617 ELSE
3618 l_proc_step := 60;
3619
3620 IF g_debug
3621 THEN
3622 debug(l_proc_name, l_proc_step);
3623 END IF;
3624
3625 IF g_is_concurrent_program_run
3626 THEN
3627 g_output_file_records(g_output_file_records.LAST).status :=
3628 'Processed(No Change)';
3629 g_output_file_records(g_output_file_records.LAST).change_type :=
3630 l_datetrack_mode;
3631 END IF;
3632 END IF; --IF ROUND(l_effective_abv_row.value,5) <> ROUND(p_abv_value,5)
3633 ELSE -- l_datetrack_mode = 'UPDATE_OVERRIDE' THEN
3634 IF g_debug
3635 THEN
3636 IF g_is_concurrent_program_run
3637 THEN
3638 debug('g_is_concurrent_program_run:TRUE');
3639 ELSE
3640 debug('g_is_concurrent_program_run:FALSE');
3641 END IF;
3642 END IF;
3643
3644 IF ( g_is_concurrent_program_run
3645 AND ROUND(fnd_number.canonical_to_number(l_effective_abv_row.VALUE), 5) <>
3646 ROUND(p_abv_value, 5) --bug 4372165
3647 )
3648 OR NOT g_is_concurrent_program_run
3649 THEN
3650 IF l_effective_abv_row.effective_start_date <>
3651 p_effective_date
3652 THEN
3653 l_proc_step := 70;
3654
3655 IF g_debug
3656 THEN
3657 debug(l_proc_name, l_proc_step);
3658 END IF;
3659
3660 per_abv_upd.upd(p_effective_date => p_effective_date
3661 ,p_datetrack_mode => l_datetrack_mode
3662 ,p_assignment_budget_value_id => l_effective_abv_row.assignment_budget_value_id
3663 ,p_object_version_number => l_effective_abv_row.object_version_number -- new param added
3664 ,p_unit => p_uom
3665 ,p_value => p_abv_value
3666 ,p_request_id => NULL
3667 ,p_program_application_id => NULL
3668 ,p_program_id => NULL
3669 ,p_program_update_date => NULL
3670 ,p_effective_start_date => l_effective_abv_row.effective_start_date
3671 ,p_effective_end_date => l_effective_abv_row.effective_end_date
3672 );
3673 ELSE -- l_effective_abv_row.effective_start_date = p_effective_date
3674 l_proc_step := 80;
3675
3676 IF g_debug
3677 THEN
3678 debug(l_proc_name, l_proc_step);
3679 END IF;
3680
3681 l_datetrack_mode := hr_api.g_future_change;
3682 per_abv_del.del(p_effective_date => p_effective_date
3683 ,p_datetrack_mode => l_datetrack_mode
3684 ,p_assignment_budget_value_id => l_effective_abv_row.assignment_budget_value_id
3685 ,p_object_version_number => l_effective_abv_row.object_version_number
3686 ,p_effective_start_date => l_effective_abv_row.effective_start_date
3687 ,p_effective_end_date => l_effective_abv_row.effective_end_date
3688 );
3689 l_datetrack_mode := hr_api.g_correction;
3690 per_abv_upd.upd(p_effective_date => p_effective_date
3691 ,p_datetrack_mode => l_datetrack_mode
3692 ,p_assignment_budget_value_id => l_effective_abv_row.assignment_budget_value_id
3693 ,p_object_version_number => l_effective_abv_row.object_version_number
3694 ,p_unit => p_uom
3695 ,p_value => p_abv_value
3696 ,p_request_id => NULL
3697 ,p_program_application_id => NULL
3698 ,p_program_id => NULL
3699 ,p_program_update_date => NULL
3700 ,p_effective_start_date => l_effective_abv_row.effective_start_date
3701 ,p_effective_end_date => l_effective_abv_row.effective_end_date
3702 );
3703 END IF; -- IF l_effective_abv_row.effective_start_date <> p_effective_date
3704
3705 IF g_is_concurrent_program_run
3706 THEN
3707 g_output_file_records(g_output_file_records.LAST).change_type :=
3708 'UPDATE_OVERRIDE';
3709 g_output_file_records(g_output_file_records.LAST).status :=
3710 'Processed';
3711 END IF;
3712 ELSE -- IF ( g_is_concurrent_program_run AND ROUND(l_effective_abv_row.value,5) <> ROUND(p_abv_value,5)...
3713 l_proc_step := 90;
3714
3715 IF g_debug
3716 THEN
3717 debug(l_proc_name, l_proc_step);
3718 END IF;
3719
3720 IF g_is_concurrent_program_run
3721 THEN
3722 g_output_file_records(g_output_file_records.LAST).change_type :=
3723 'UPDATE_OVERRIDE';
3724 g_output_file_records(g_output_file_records.LAST).status :=
3725 'Processed(No Change)';
3726 END IF;
3727 END IF; -- IF ( g_is_concurrent_program_run AND ROUND(l_effective_abv_row.value,5) <> ROUND(p_abv_value,5)...
3728 END IF; -- IF l_datetrack_mode <> 'UPDATE_OVERRIDE' THEN
3729 END IF; -- IF p_action <> c_verify THEN
3730 ELSE --code should never reach here
3731 l_proc_step := 100;
3732
3733 IF g_debug
3734 THEN
3735 debug(l_proc_name, l_proc_step);
3736 END IF;
3737 END IF; --IF csr_abv_exists%NOTFOUND
3738
3739 CLOSE csr_abv_exists;
3740 l_proc_step := 110;
3741
3742 IF g_debug
3743 THEN
3744 debug(l_proc_name, l_proc_step);
3745 debug( 'l_effective_abv_row.assignment_budget_value_id:'
3746 || l_effective_abv_row.assignment_budget_value_id
3747 );
3748 debug( 'l_effective_abv_row.object_version_number:'
3749 || l_effective_abv_row.object_version_number
3750 );
3751 debug( 'l_effective_abv_row.effective_start_date:'
3752 || fnd_date.date_to_canonical(l_effective_abv_row.effective_start_date
3753 )
3754 );
3755 debug( 'l_effective_abv_row.effective_end_date:'
3756 || fnd_date.date_to_canonical(l_effective_abv_row.effective_end_date
3757 )
3758 );
3759 debug('l_effective_abv_row.value:' || l_effective_abv_row.VALUE);
3760 debug('ROUND(p_abv_value,5):' || ROUND(p_abv_value, 5));
3761 debug_exit(l_proc_name);
3762 END IF;
3763 EXCEPTION
3764 WHEN OTHERS
3765 THEN
3766 clear_cache;
3767
3768 IF SQLCODE <> hr_utility.hr_error_number
3769 THEN
3770 debug_others(l_proc_name, l_proc_step);
3771
3772 IF g_debug
3773 THEN
3774 debug('Leaving: ' || l_proc_name, -999);
3775 END IF;
3776
3777 hr_utility.raise_error;
3778 ELSE
3779 RAISE;
3780 END IF;
3781 END update_and_store_abv;
3782
3783 /* ------------------------------------------------------------ */
3784 /* --------------------- Deinitialise ------------------------- */
3785 /* ------------------------------------------------------------ */
3786 PROCEDURE deinitialization_code(p_pay_action_id IN NUMBER)
3787 IS
3788 -- Cursor to fetch assignment actions that are set to status
3789 -- complete
3790 CURSOR csr_get_comp_asg_acts
3791 IS
3792 SELECT assignment_action_id
3793 FROM pay_assignment_actions
3794 WHERE payroll_action_id = p_pay_action_id AND action_status = 'C';
3795
3796 -- Cursor to get count of assignment actions for
3797 -- a given payroll action
3798 CURSOR csr_get_asg_act_cnt
3799 IS
3800 SELECT COUNT(*)
3801 FROM pay_assignment_actions
3802 WHERE payroll_action_id = p_pay_action_id;
3803
3804 l_proc_step NUMBER(38, 10) := 0;
3805 l_proc_name VARCHAR2(61)
3806 := g_package_name || 'deinitialization_code';
3807 l_asg_action_id NUMBER;
3808 l_count NUMBER;
3809 BEGIN
3810 IF g_debug
3811 THEN
3812 debug_enter(l_proc_name);
3813 debug('p_pay_action_id: ' || p_pay_action_id);
3814 END IF;
3815
3816 /* Comment the following as we do not want to
3817 delete assignment actions
3818
3819 l_proc_step := 10;
3820 -- Get the assignment actions
3821 OPEN csr_get_comp_asg_acts;
3822 LOOP
3823 FETCH csr_get_comp_asg_acts INTO l_asg_action_id;
3824 EXIT WHEN csr_get_comp_asg_acts%NOTFOUND;
3825
3826 -- Delete from pay_action_interlocks
3827 IF g_debug
3828 THEN
3829 debug(l_proc_name, l_proc_step);
3830 debug('l_asg_action_id: ' || l_asg_action_id);
3831 END IF;
3832
3833 l_proc_step := 20;
3834 DELETE FROM pay_action_interlocks
3835 WHERE locking_action_id = l_asg_action_id;
3836
3837 IF g_debug
3838 THEN
3839 debug(l_proc_name, l_proc_step);
3840 debug(SQL%ROWCOUNT || ' pay_action_interlocks rows deleted');
3841 END IF;
3842
3843 l_proc_step := 30;
3844 -- Delete from pay_message_lines
3845 DELETE FROM pay_message_lines
3846 WHERE source_id = l_asg_action_id AND source_type = 'A';
3847
3848 IF g_debug
3849 THEN
3850 debug(l_proc_name, l_proc_step);
3851 debug(SQL%ROWCOUNT || ' pay_message_lines rows deleted');
3852 END IF;
3853
3854 l_proc_step := 40;
3855 -- Delete from assignment actions
3856 DELETE FROM pay_assignment_actions
3857 WHERE assignment_action_id = l_asg_action_id;
3858
3859 IF g_debug
3860 THEN
3861 debug(l_proc_name, l_proc_step);
3862 debug(SQL%ROWCOUNT || ' pay_assignment_action rows deleted');
3863 END IF;
3864 END LOOP;
3865 CLOSE csr_get_comp_asg_acts;
3866
3867 l_proc_step := 50;
3868 l_count := NULL;
3869 OPEN csr_get_asg_act_cnt;
3870 FETCH csr_get_asg_act_cnt INTO l_count;
3871 CLOSE csr_get_asg_act_cnt;
3872
3873 IF g_debug
3874 THEN
3875 debug(l_proc_name, l_proc_step);
3876 debug('l_count: ' || l_count);
3877 END IF;
3878
3879 IF l_count = 0
3880 THEN
3881 -- Delete underlying tables
3882 l_proc_step := 60;
3883
3884 DELETE FROM pay_message_lines
3885 WHERE source_id = p_pay_action_id AND source_type = 'P';
3886
3887 IF g_debug
3888 THEN
3889 debug(l_proc_name, l_proc_step);
3890 debug(SQL%ROWCOUNT || ' pay_message_lines rows deleted');
3891 END IF;
3892
3893 -- Delete pay_population_ranges
3894 l_proc_step := 70;
3895
3896 DELETE FROM pay_population_ranges
3897 WHERE payroll_action_id = p_pay_action_id;
3898
3899 IF g_debug
3900 THEN
3901 debug(l_proc_name, l_proc_step);
3902 debug(SQL%ROWCOUNT || ' pay_population_ranges rows deleted');
3903 END IF;
3904
3905 -- Delete pay_payroll_actions
3906 l_proc_step := 80;
3907 DELETE FROM pay_payroll_actions
3908 WHERE payroll_action_id = p_pay_action_id;
3909
3910 IF g_debug
3911 THEN
3912 debug(l_proc_name, l_proc_step);
3913 debug(SQL%ROWCOUNT || ' pay_payroll_actions rows deleted');
3914 END IF;
3915 END IF; -- End if of l_count = 0 check ...
3916
3917 */
3918 IF g_debug
3919 THEN
3920 debug_exit(l_proc_name);
3921 END IF;
3922
3923 hr_utility.trace_off;
3924 EXCEPTION
3925 WHEN OTHERS
3926 THEN
3927 clear_cache;
3928
3929 IF SQLCODE <> hr_utility.hr_error_number
3930 THEN
3931 debug_others(l_proc_name, l_proc_step);
3932
3933 IF g_debug
3934 THEN
3935 debug('Leaving: ' || l_proc_name, -999);
3936 END IF;
3937
3938 hr_utility.raise_error;
3939 ELSE
3940 RAISE;
3941 END IF;
3942 END deinitialization_code;
3943 END pqp_budget_maintenance;