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