[Home] [Help]
PACKAGE BODY: APPS.PAY_NZ_SSCWT_RATE_ARCHIVE
Source
1 PACKAGE BODY pay_nz_sscwt_rate_archive AS
2 /* $Header: paynzssc.pkb 120.8.12020000.2 2013/03/04 11:54:37 dduvvuri ship $ */
3
4 ----------------------------------------------------------------------+
5 -- Global Variables Section
6 ----------------------------------------------------------------------+
7
8
9
10 /*Global variable to enable trace conditionally*/
11 g_debug BOOLEAN;
12
13 -- This is a global variable used to store Archive assignment action id
14 g_archive_pact NUMBER;
15
16 g_package VARCHAR2(100);
17
18 g_payroll_id pay_payrolls_f.payroll_id%TYPE;
19 g_assignment_set_id hr_assignment_sets.assignment_set_id%TYPE;
20 g_business_group_id per_people_f.business_group_id%TYPE;
21 g_financial_year DATE;
22 g_processing_mode VARCHAR2(1);
23
24 g_element_type_id pay_element_types_f.element_type_id%TYPE ;
25 g_input_value_id pay_input_values_f.input_value_id%TYPE ;
26
27 g_def_balance_tab pay_balance_pkg.t_balance_value_tab;
28
29 g_start_dd_mm VARCHAR(6) ;
30 g_legislation_code VARCHAR2(30) ;
31
32 g_report_short_name VARCHAR2(25) ;
33 -----------------------------------------------------------------------
34 -- List of private functions/procedures which are used in the package--
35 -----------------------------------------------------------------------
36
37 --------------------------------------------------------------------------
38 -- --
39 -- Name : SUBMIT_SSCWT_REPORT --
40 -- Type : PROCEDURE --
41 -- Access : Private --
42 -- Description : The procedure executes the SSCWT report and is --
43 -- called by the deinitialize_code of the archive. --
44 -- --
45 -- Parameters : --
46 -- IN : N/A --
47 -- OUT : N/A --
48 -- --
49 -- Change History : --
50 --------------------------------------------------------------------------
51 -- Rev# Date Userid Description --
52 --------------------------------------------------------------------------
53 -- 115.0 22-JAN-2004 sshankar Initial Version --
54 -- 115.1 28-JAN-2004 sshankar Removed hr_utility.debug_enabled call--
55 --------------------------------------------------------------------------
56 --
57 PROCEDURE submit_sscwt_report
58 IS
59 l_request_id NUMBER ;
60 l_procedure VARCHAR2(200);
61 --
62 BEGIN
63 --
64
65 IF g_debug THEN
66 l_procedure := g_package||'submit_sscwt_report';
67 hr_utility.set_location('Entering ' ||l_procedure, 10);
68 hr_utility.trace('Report Name -> ' || g_report_short_name);
69 hr_utility.trace('Business Group ID -> ' || g_business_group_id);
70 hr_utility.trace('Financial Year -> ' || g_financial_year);
71 hr_utility.trace('Processing Mode -> ' || g_processing_mode);
72 hr_utility.trace('Archive ID -> ' || g_archive_pact);
73 hr_utility.trace('Payroll ID -> ' || g_payroll_id);
74 hr_utility.trace('Assignment Set ID -> ' || g_assignment_set_id);
75 END IF;
76
77 --
78 -- Submit the SSCWT text report using fnd_request.submit_request
79 -- function.
80 --
81 l_request_id := fnd_request.submit_request
82 (APPLICATION => 'PER',
83 PROGRAM => g_report_short_name,
84 ARGUMENT1 => 'P_BUSINESS_GROUP_ID='||g_business_group_id,
85 ARGUMENT2 => 'P_FINANCIAL_YEAR='||to_char(g_financial_year,'YYYY'),
86 ARGUMENT3 => 'P_PROCESS_TYPE='||g_processing_mode,
87 ARGUMENT4 => 'P_ARCHIVE_PAYROLL_ACTION_ID='||g_archive_pact,
88 ARGUMENT5 => 'P_PAYROLL_ID='||g_payroll_id,
89 ARGUMENT6 => 'P_ASSIGNMENT_SET_ID='||g_assignment_set_id);
90
91 --
92 -- If the request is not submitted, then error out.
93 --
94 IF l_request_id = 0 THEN
95 hr_utility.set_location('Error submitting report', 20);
96 hr_utility.raise_error;
97 END IF;
98 --
99 IF g_debug THEN
100 hr_utility.set_location('Leaving '||l_procedure,30);
101 END IF;
102 --
103 EXCEPTION
104 WHEN OTHERS THEN
105 IF g_debug THEN
106 hr_utility.set_location('Error in ' ||l_procedure, 40);
107 END IF;
108 RAISE;
109
110 END submit_sscwt_report;
111
112 --------------------------------------------------------------------------
113 -- --
114 -- Name : UPDATE_SSCWT_RATE --
115 -- Type : FUNCTION --
116 -- Access : Private --
117 -- Description : Function to update the details of SSCWT Information --
118 -- element. --
119 -- The funciton uses dt_api to get the updation_mode. --
120 -- --
121 -- Parameters : --
122 -- IN : p_sscwt_rate NUMBER --
123 -- p_sscwt_element_entry_id pay_element_entries_f.element_entry_id%TYPE --
124 -- p_effective_date DATE --
125 -- --
126 -- OUT : N/A --
127 -- --
128 -- Change History : --
129 --------------------------------------------------------------------------
130 -- Rev# Date Userid Description --
131 --------------------------------------------------------------------------
132 -- 115.0 22-JAN-2004 sshankar Initial Version --
133 -- 115.1 28-JAN-2004 sshankar Removed hr_utility.debug_enabled call--
134 -- Removed using l_update_change_insert --
135 -- as update mode. --
136 --------------------------------------------------------------------------
137 --
138
139 FUNCTION update_sscwt_rate
140 (p_sscwt_rate IN NUMBER
141 ,p_sscwt_element_entry_id IN pay_element_entries_f.element_entry_id%TYPE
142 ,p_effective_date IN DATE)
143 RETURN BOOLEAN
144 IS
145 --
146 -- Local variables
147 --
148 l_correction BOOLEAN;
149 l_update BOOLEAN;
150 l_update_override BOOLEAN;
151 l_update_change_insert BOOLEAN;
152 l_warning BOOLEAN;
153 l_update_mode VARCHAR2(30);
154 l_procedure VARCHAR2(100);
155 l_effective_start_date DATE;
156 l_effective_end_date DATE;
157 --
158 l_object_version_number pay_element_entries_f.object_version_number%TYPE;
159
160 --
161 CURSOR csr_get_object_version
162 IS
163 SELECT object_version_number
164 FROM pay_element_entries_f
165 WHERE element_entry_id = p_sscwt_element_entry_id
166 AND p_effective_date between effective_start_date
167 and effective_end_date;
168 --
169
170 BEGIN
171 --
172
173 IF g_debug THEN
174 l_procedure := g_package || 'update_sscwt_rate';
175 hr_utility.set_location('Entering '||l_procedure, 10);
176 hr_utility.trace('SSCWT Rate -> ' || p_sscwt_rate);
177 hr_utility.trace('Element Entry ID -> ' || p_sscwt_element_entry_id);
178 END IF;
179 --
180 --
181 -- Determine the update mode to be used in element entry value updation.
182 -- This procedure will set updation mode either to one of these values as true:
183 -- Update, Correction, update Override or Update Change Imsert.
184 --
185 DT_Api.Find_DT_Upd_Modes
186 (p_effective_date => p_effective_date
187 ,p_base_table_name => 'pay_element_entries_f'
188 ,p_base_key_column => 'element_entry_id'
189 ,p_base_key_value => p_sscwt_element_entry_id
190 ,p_correction => l_correction
191 ,p_update => l_update
192 ,p_update_override => l_update_override
193 ,p_update_change_insert=> l_update_change_insert);
194 --
195
196 IF g_debug THEN
197 hr_utility.set_location('After calling DT_Api.Find_DT_Upd_MOdes', 20);
198 END IF;
199 --
200 --
201 -- Check which flag has been set by DT_API.Find_DT_Upd_Modes
202 -- Correction is always set to true hence check it's value at last as default.
203 -- If effective start date is not same as effective date, then
204 -- If any future row exists for element, then Update is false and Update override and
205 -- Update Change Insert is set to true.
206 -- If there are no future row exists then Update mode is used.
207 --
208 -- No need to use update_change_insert mode as both update_override and update_change_insert
209 -- are always set to true or false.
210 --
211 IF l_update THEN
212 l_update_mode := hr_api.g_update;
213 ELSIF l_update_override THEN
214 l_update_mode := hr_api.g_update_override;
215 ELSIF l_correction THEN
216 l_update_mode := hr_api.g_correction;
217 ELSE
218 return FALSE;
219 END IF;
220 --
221 IF g_debug THEN
222 hr_utility.set_location('Update Mode -> ' || l_update_mode, 30);
223 END IF;
224 --
225 OPEN csr_get_object_version;
226 FETCH csr_get_object_version INTO l_object_version_number;
227 CLOSE csr_get_object_version;
228 --
229 IF g_debug THEN
230 hr_utility.set_location('Object Version Number -> ' || l_object_version_number, 40);
231 END IF;
232 --
233 --
234 IF g_debug THEN
235 hr_utility.set_location('G_Input Value ID -> ' || g_input_value_id, 50);
236 END IF;
237 --
238 pay_element_entry_api.update_element_entry
239 (p_datetrack_update_mode => l_update_mode
240 ,p_effective_date => p_effective_date
241 ,p_business_group_id => g_business_group_id
242 ,p_element_entry_id => p_sscwt_element_entry_id
243 ,p_object_version_number => l_object_version_number
244 ,p_input_value_id1 => g_input_value_id
245 ,p_entry_value1 => p_sscwt_rate
246 ,p_effective_start_date => l_effective_start_date
247 ,p_effective_end_date => l_effective_end_date
248 ,p_update_warning => l_warning);
249 --
250
251 IF g_debug THEN
252 hr_utility.set_location('After calling update_element_entry ', 60);
253 hr_utility.trace('Effective Start -> '||l_effective_start_date);
254 hr_utility.trace('Effective End -> ' ||l_effective_end_date);
255
256 END IF;
257 --
258 RETURN true;
259
260 EXCEPTION
261 WHEN OTHERS THEN
262 IF g_debug THEN
263 hr_utility.set_location('Error in '|| l_procedure, 70);
264 END IF;
265 RETURN false;
266 END update_sscwt_rate;
267
268
269 --------------------------------------------------------------------------
270 -- --
271 -- Name : GET_LEGISLATIVE_PARAMETERS --
272 -- Type : PROCEDURE --
273 -- Access : Private --
274 -- Description : Sets the global variables which will be used by --
275 -- assignment actions code. Values for global --
276 -- variables are fetched from pay_payroll_actions --
277 -- 'Legislative_parameters' column. --
278 -- --
279 -- Parameters : --
280 -- IN : p_payroll_action_id NUMBER --
281 -- OUT : N/A --
282 -- --
283 -- Change History : --
284 --------------------------------------------------------------------------
285 -- Rev# Date Userid Description --
286 --------------------------------------------------------------------------
287 -- 115.0 22-JAN-2004 sshankar Initial Version --
288 -- 115.1 28-JAN-2004 sshankar Removed hr_utility.debug_enabled call--
289 -- --
290 --------------------------------------------------------------------------
291 --
292
293 PROCEDURE get_legislative_parameters(p_payroll_action_id IN NUMBER)
294 IS
295 l_procedure VARCHAR2(100) := null;
296 l_financial_year VARCHAR2(30);
297 --
298 CURSOR csr_get_parameters(p_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE)
299 IS
300 SELECT pay_core_utils.get_parameter('PAYROLL_ID'
301 ,legislative_parameters) payroll_id,
302 pay_core_utils.get_parameter('ASSIGNMENT_SET'
303 ,legislative_parameters) assignment_set_id,
304 pay_core_utils.get_parameter('BUSINESS_GROUP_ID'
305 ,legislative_parameters) business_group_id,
306 pay_core_utils.get_parameter('FINANCIAL_YEAR'
307 ,legislative_parameters) financial_year,
308 pay_core_utils.get_parameter('PROCESSING_MODE'
309 ,legislative_parameters) processing_mode
310 FROM pay_payroll_actions ppa
311 WHERE ppa.payroll_action_id = p_payroll_action_id;
312
313
314 --
315 BEGIN
316 --
317
318 IF g_debug THEN
319 l_procedure := g_package||'get_legislative_parameters';
320 hr_utility.set_location('Entering '||l_procedure, 10);
321 END IF;
322 --
323 -- Set the global variables
324 --
325 g_archive_pact := p_payroll_action_id;
326
327 OPEN csr_get_parameters(p_payroll_action_id);
328 FETCH csr_get_parameters INTO g_payroll_id
329 , g_assignment_set_id
330 , g_business_group_id
331 , l_financial_year
332 , g_processing_mode;
333 CLOSE csr_get_parameters;
334 --
335
336 -- Append 'DD-MM-' part to the Year part obtained from legislative parameters
337 g_financial_year := TO_DATE(g_start_dd_mm || l_financial_year, 'DD-MM-YYYY') ;
338
339 IF g_debug THEN
340 hr_utility.set_location('In '||l_procedure, 20);
341 hr_utility.trace('Payroll Action ID -> ' || g_archive_pact);
342 hr_utility.trace('G_Financial Year -> ' || g_financial_year);
343 hr_utility.trace('L_Financial Year -> ' || l_financial_year);
344 hr_utility.trace('Processing Mode -> ' || g_processing_mode);
345 hr_utility.trace('Payroll ID -> ' || g_payroll_id);
346 hr_utility.trace('Assignment set ID -> ' || g_assignment_set_id);
347 hr_utility.trace('Business Group ID -> ' || g_business_group_id);
348 END IF;
349 --
350 EXCEPTION
351 WHEN OTHERS THEN
352 IF csr_get_parameters%ISOPEN THEN
353 CLOSE csr_get_parameters;
354 END IF;
355 IF g_debug THEN
356 hr_utility.set_location('Error in '||l_procedure, 30);
357 END IF;
358 RAISE;
359 END get_legislative_parameters;
360 --
361
362 -----------------------------------------------------------------------
363 -- End of private function/procedure --
364 -----------------------------------------------------------------------
365
366
367 --------------------------------------------------------------------------
368 -- --
369 -- Name : PERIODS_IN_SPAN --
370 -- Type : FUNCTION --
371 -- Access : Public --
372 -- Description : Function returns the number of periods for which --
373 -- the payroll is run for a given assignment and given --
374 -- period. --
375 -- --
376 -- Parameters : --
377 -- IN : p_start_date DATE --
378 -- p_start_date DATE --
379 -- p_assignment_id per_assignments_f.assignment_id%TYPE --
380 -- --
381 -- OUT : N/A --
382 -- --
383 -- Change History : --
384 --------------------------------------------------------------------------
385 -- Rev# Date Userid Description --
386 --------------------------------------------------------------------------
387 -- 115.0 22-JAN-2004 sshankar Initial Version --
388 -- 115.1 01-Aug-2004 snekkala Modified csr_pay_periods as part of --
389 -- bug 4259438 --
390 --------------------------------------------------------------------------
391 --
392
393 FUNCTION periods_in_span
394 ( p_start_date IN DATE
395 , p_end_date IN DATE
396 , p_assignment_id IN per_assignments_f.assignment_id%TYPE)
397 RETURN NUMBER
398 IS
399 --
400 l_year NUMBER(4);
401 l_start DATE;
402 l_periods NUMBER;
403 l_procedure VARCHAR2(100);
404 --
405 CURSOR csr_pay_periods(c_start_date date)
406 IS
407 SELECT count(*)
408 FROM pay_payroll_actions ppa
409 , per_time_periods ptp
410 , pay_assignment_actions paa
411 , per_assignments_f paf
412 , pay_payrolls_f ppf
413 WHERE paa.assignment_id = paf.assignment_id
414 AND paa.payroll_action_id = ppa.payroll_action_id
415 AND ppa.action_type IN ('R', 'Q')
416 AND ptp.time_period_id = ppa.time_period_id
417 AND ppf.payroll_id = ppa.payroll_id
418 AND ppf.payroll_id = ptp.payroll_id
419 AND ppf.payroll_id = paf.payroll_id
420 AND paa.action_status = 'C'
421 AND ppa.action_status = 'C'
422 AND ppa.payroll_id = ptp.payroll_id
423 AND ptp.end_date BETWEEN c_start_date
424 AND p_end_date
425 AND p_end_date BETWEEN paf.effective_start_date
426 AND paf.effective_end_date
427 AND p_end_date BETWEEN ppf.effective_start_date
428 AND ppf.effective_end_date
429 AND paf.assignment_id = p_assignment_id;
430 --
431 BEGIN
432 --
433 g_debug := hr_utility.debug_enabled;
434 IF g_debug THEN
435 l_procedure := g_package||'periods_in_span';
436 hr_utility.set_location('Entering ' ||l_procedure, 10);
437 hr_utility.trace('Assignment ID -> ' || p_assignment_id);
438 hr_utility.trace('Start Date -> ' || p_start_date);
439 hr_utility.trace('End Date -> ' || p_end_date);
440 END IF;
441
442 -- Get the previous Fiscal year.
443 l_year := TO_NUMBER(TO_CHAR(p_end_date,'YYYY'))-1;
444
445 --
446 -- If start date is greater than the '01-APR' of the previous Year
447 -- then start would be from the p_start_date and not 01-APR of previous year.
448 IF p_start_date >= to_date(g_start_dd_mm||TO_CHAR(l_year),'DD-MM-YYYY')
449 THEN
450 l_start := p_start_date;
451 ELSE
452 l_start := TO_DATE(g_start_dd_mm||TO_CHAR(l_year),'DD-MM-YYYY');
453 END IF;
454 --
455 IF g_debug THEN
456 hr_utility.trace('Modified Start Date -> ' || l_start);
457 END IF;
458 OPEN csr_pay_periods(l_start);
459 FETCH csr_pay_periods INTO l_periods;
460 CLOSE csr_pay_periods;
461 --
462 IF g_debug THEN
463 hr_utility.set_location('Periods: ' || l_periods, 30);
464 END IF;
465 RETURN l_periods;
466 --
467 EXCEPTION
468 WHEN OTHERS THEN
469 IF csr_pay_periods%ISOPEN THEN
470 IF g_debug THEN
471 hr_utility.set_location('Error Closing cursor csr_pay_periods', 40);
472 END IF;
473 CLOSE csr_pay_periods;
474 END IF;
475 IF g_debug THEN
476 hr_utility.set_location('Error in periods_in_span', 50);
477 END IF;
478 RAISE;
479 END periods_in_span;
480
481
482 --------------------------------------------------------------------------
483 -- --
484 -- Name : RANGE_CODE --
485 -- Type : PROCEDURE --
486 -- Access : Public --
487 -- Description : This procedure returns a sql string to select all --
488 -- employees who belong to the business group. --
489 -- --
490 -- Parameters : --
491 -- IN : p_payroll_action_id NUMBER --
492 -- OUT : p_sql VARCHAR2 --
493 -- --
494 -- Change History : --
495 --------------------------------------------------------------------------
496 -- Rev# Date Userid Description --
497 --------------------------------------------------------------------------
498 -- 115.0 22-JAN-2004 sshankar Initial Version --
499 -- --
500 --------------------------------------------------------------------------
501 --
502
503 PROCEDURE range_code(
504 p_payroll_action_id IN NUMBER
505 ,p_sql OUT NOCOPY VARCHAR2
506 )
507 IS
508
509 --
510 l_procedure VARCHAR2(100) ;
511 --
512 BEGIN
513 --
514 --
515 -- print the debug messages if debug is enabled.
516 --
517 g_debug := hr_utility.debug_enabled;
518
519 IF g_debug THEN
520 l_procedure := g_package || 'range_code' ;
521 hr_utility.set_location('Entering ' || l_procedure,10);
522 END IF;
523 --
524 -- sql string to SELECT a range of assignments eligible for archival.
525 --
526
527 p_sql := ' SELECT distinct ppf.person_id' ||
528 ' FROM per_people_f ppf' ||
529 ',pay_payroll_actions ppa' ||
530 ' WHERE ppa.payroll_action_id = :payroll_action_id' ||
531 ' AND ppa.business_group_id = ppf.business_group_id' ||
532 ' ORDER BY ppf.person_id';
533
534 IF g_debug THEN
535 hr_utility.set_location('Leaving ' || l_procedure,20);
536 END IF;
537
538 --
539 EXCEPTION
540 WHEN OTHERS THEN
541 IF g_debug THEN
542 hr_utility.set_location('Error in ' || l_procedure,30);
543 END IF;
544
545 RAISE;
546 --
547 END range_code;
548
549
550 --------------------------------------------------------------------------
551 -- --
552 -- Name : ASSIGNMENT_ACTION_CODE --
553 -- Type : PROCEDURE --
554 -- Access : Public --
555 -- Description : This procedure further restricts the assignment_ids --
556 -- returned by range_code. --
557 -- It filters the assignments selected by range_code --
558 -- procedure by applying further selection criteria. --
559 -- --
560 -- Parameters : --
561 -- IN : p_payroll_action_id NUMBER --
562 -- p_start_person NUMBER --
563 -- p_end_person NUMBER --
564 -- p_chunk NUMBER --
565 -- OUT : N/A --
566 -- --
567 -- Change History : --
568 --------------------------------------------------------------------------
569 -- Rev# Date Userid Description --
570 --------------------------------------------------------------------------
571 -- 115.0 22-JAN-2004 sshankar Initial Version --
572 --------------------------------------------------------------------------
573 --
574
575 PROCEDURE assignment_action_code (
576 p_payroll_action_id IN NUMBER
577 ,p_start_person IN NUMBER
578 ,p_end_person IN NUMBER
579 ,p_chunk IN NUMBER
580 )
581 IS
582
583
584 CURSOR csr_next_action_id
585 IS
586 SELECT pay_assignment_actions_s.nextval FROM dual;
587
588
589 CURSOR csr_get_assignments
590 IS
591 SELECT DISTINCT assignment.assignment_id
592 FROM per_people_f person
593 ,per_assignments_f assignment
594 ,per_periods_of_service service
595 ,pay_element_types_f element
596 ,pay_element_links_f link
597 ,pay_element_entries_f entry
598 WHERE person.person_id BETWEEN p_start_person
599 AND p_end_person
600 AND assignment.person_id = person.person_id
601 AND assignment.business_group_id = person.business_group_id
602 AND service.period_of_service_id = assignment.period_of_service_id
603 AND element.element_name = 'SSCWT Information'
604 AND element.element_type_id = link.element_type_id
605 AND entry.element_link_id = link.element_link_id
606 AND entry.assignment_id = assignment.assignment_id
607 AND link.business_group_id = person.business_group_id
608 AND (g_payroll_id is null OR assignment.payroll_id = g_payroll_id)
609 AND hr_assignment_set.assignment_in_set(g_assignment_set_id, assignment.assignment_id) = 'Y'
610 AND g_financial_year BETWEEN person.effective_start_date
611 AND person.effective_end_date
612 AND g_financial_year BETWEEN assignment.effective_start_date
613 AND assignment.effective_end_date
614 AND g_financial_year BETWEEN element.effective_start_date
615 AND element.effective_end_date
616 AND g_financial_year BETWEEN link.effective_start_date
617 AND link.effective_end_date
618 AND g_financial_year BETWEEN entry.effective_start_date
619 AND entry.effective_end_date
620 AND g_financial_year BETWEEN service.date_start
621 AND NVL(service.actual_termination_date, TO_DATE('31-12-4712', 'DD-MM-YYYY')) ;
622
623
624 --
625 l_next_assignment_action_id NUMBER;
626 l_procedure VARCHAR2(100) ;
627 --
628
629
630
631 BEGIN
632 --
633 --
634 -- print the debug messages if debug is enabled.
635 --
636 g_debug := hr_utility.debug_enabled;
637
638 IF g_debug THEN
639 l_procedure := g_package || 'assignment_action_code' ;
640 hr_utility.set_location('Entering ' || l_procedure,10);
641 END IF;
642
643 -- Get the legislative parameters of the concurrent request for archive
644 -- and store them in global variables.
645 get_legislative_parameters(p_payroll_action_id);
646
647 IF g_debug THEN
648 hr_utility.set_location('Opening Cursor csr_get_assignments.',20);
649 END IF;
650
651 FOR csr_record IN csr_get_assignments
652
653 LOOP
654 --
655
656 IF g_debug THEN
657 hr_utility.set_location('For Assignment id.....:'||csr_record.assignment_id,30);
658 hr_utility.set_location('Creating new archive assignment action id',40);
659 END IF;
660
661 OPEN csr_next_action_id ;
662 FETCH csr_next_action_id INTO l_next_assignment_action_id ;
663 CLOSE csr_next_action_id ;
664
665 IF g_debug THEN
666 hr_utility.set_location('New archive assignment action id:'||l_next_assignment_action_id,50);
667 hr_utility.set_location('Creating the archive assignment action id for the ...:'||csr_record.assignment_id,60);
668 END IF;
669
670 -- Insert the new assignment actions
671
672
673
674 hr_nonrun_asact.insact(
675 l_next_assignment_action_id
676 ,csr_record.assignment_id
677 ,p_payroll_action_id
678 ,p_chunk
679 ,null
680 );
681
682
683
684 END LOOP;
685 IF g_debug THEN
686 hr_utility.set_location('Leaving ' || l_procedure,70);
687 END IF;
688
689
690 EXCEPTION
691 WHEN OTHERS THEN
692 IF g_debug THEN
693 hr_utility.trace('Error occured in '||l_procedure);
694 END IF;
695
696 IF csr_get_assignments%ISOPEN THEN
697 --
698 CLOSE csr_get_assignments;
699 --
700 END IF;
701
702 RAISE;
703
704 END assignment_action_code ;
705
706
707
708 --------------------------------------------------------------------------
709 -- --
710 -- Name : INITIALIZATION_CODE --
711 -- Type : PROCEDURE --
712 -- Access : Public --
713 -- Description : This procedure is used to set global contexts. --
714 -- It stores defined balance IDs and element IDs and --
715 -- Element Input value Ids into global variables. --
716 -- Parameters : --
717 -- IN : p_payroll_action_id NUMBER --
718 -- OUT : N/A --
719 -- --
720 -- Change History : --
721 --------------------------------------------------------------------------
722 -- Rev# Date Userid Description --
723 --------------------------------------------------------------------------
724 -- 115.0 22-JAN-2004 sshankar Initial Version --
725 -- 115.1 22-JAN-2004 sshankar Modified csr_defined_balances to --
726 -- include all three balances at once --
727 -- instead of calling it thrice. --
728 -- 115.2 31-MAY-2004 sshankar Changed cursor csr_defined_balances --
729 -- so as not to include balance 'Employer--
730 -- Specified Superannuation --
731 -- Contributions'. (Bug 3609069) --
732 -- 115.10 12-Apr-2007 dduvvuri Changed cursor csr_defined_balances --
733 -- so as to include "KiwiSaver Employer --
734 -- Contributions" balance --
735 -- 115.11 02-Feb-2009 dduvvuri 7668520 - Changed cursor csr_defined_balances --
736 -- so as to include "Employer Specified --
737 -- Superannuation Cont" balance --
738 --------------------------------------------------------------------------
739 --
740
741 PROCEDURE initialization_code (
742 p_payroll_action_id IN NUMBER
743 )
744 IS
745 --
746 l_procedure VARCHAR2(100) ;
747 l_balance_name pay_balance_types.balance_name%TYPE;
748 l_defined_balance_id pay_defined_balances.defined_balance_id%TYPE;
749 --
750
751 --
752 -- Cursor to fetch element type id and input value id for 'SSCWT Information' element
753 -- which are later used to fetch input value of the element for corresponding assignments.
754 --
755 CURSOR csr_get_element_ids(c_financial_year DATE)
756 IS
757 SELECT pet.element_type_id, piv.input_value_id
758 FROM pay_element_types_f pet
759 ,pay_input_values_f piv
760 WHERE pet.element_name = 'SSCWT Information'
761 AND pet.element_type_id = piv.element_type_id
762 AND piv.name = 'SSCWT Rate'
763 AND c_financial_year BETWEEN pet.effective_start_date
764 AND pet.effective_end_date
765 AND c_financial_year BETWEEN piv.effective_start_date
766 AND piv.effective_end_date ;
767 --
768
769 --
770 -- Modified the cursor to have all three balances name and Dimension so as to
771 -- avoid executing it thrice for three balances.
772 --
773 -- Cursor to fetch defined Balance ID
774 --
775
776 --
777 -- Bug 3609069
778 -- Removed Balance 'Employer Specified Superannuation Contributions'
779 --
780 -- Bug 5846247
781 -- Added Balance 'KiwiSaver Employer Contributions'
782
783 CURSOR csr_defined_balances
784 IS
785 SELECT defined.defined_balance_id
786 ,bal.balance_name balance_name
787 FROM pay_balance_types bal
788 , pay_balance_dimensions dim
789 , pay_defined_balances defined
790 WHERE bal.legislation_code = g_legislation_code
791 AND bal.balance_name IN ( 'Ordinary Taxable Earnings'
792 ,'Extra Emolument Taxable Earnings'
793 ,'KiwiSaver Employer Contributions'
794 ,'Employer Specified Superannuation Contributions' /* Added for bug 7668520 */
795 ,'Retro Ordinary Taxable Earnings'
796 )
797 AND dim.legislation_code = g_legislation_code
798 AND dim.dimension_name = '_ASG_YTD'
799 AND bal.balance_type_id = defined.balance_type_id
800 AND dim.balance_dimension_id = defined.balance_dimension_id;
801 --
802
803 BEGIN
804 --
805 --
806 -- print the debug messages if debug is enabled.
807 --
808 g_debug := hr_utility.debug_enabled;
809
810 IF g_debug THEN
811 l_procedure := g_package || 'initialization_code' ;
812 hr_utility.set_location('Entering ' || l_procedure,10);
813 END IF;
814
815 -- Get the legislative parameters and store them in global variables.
816 get_legislative_parameters(p_payroll_action_id);
817
818 IF g_debug THEN
819 hr_utility.set_location('p_payroll_action_id -> ' || p_payroll_action_id, 15);
820 END IF;
821
822 -- Fetch element_type_id and input_value_id into global variables.
823 OPEN csr_get_element_ids(g_financial_year) ;
824 FETCH csr_get_element_ids INTO g_element_type_id, g_input_value_id ;
825 CLOSE csr_get_element_ids ;
826
827 IF g_debug THEN
828 hr_utility.set_location('g_element_type_id -> ' ||g_element_type_id ,20);
829 hr_utility.set_location('g_input_value_id -> ' ||g_input_value_id ,30);
830 END IF;
831
832 --
833 -- Modified the cursor to have all three balances name and Dimension so as to
834 -- avoid executing it thrice for three balances.
835 --
836 -- Fetch Balance IDs into global variables.
837
838 FOR csr_bal_rec IN csr_defined_balances
839 LOOP
840 IF csr_bal_rec.balance_name = 'Ordinary Taxable Earnings' THEN
841 g_def_balance_tab(1).defined_balance_id := csr_bal_rec.defined_balance_id;
842
843 -- Bug 3609069
844 -- Removed code to handle balance 'Employer Specified Superannuation Contributions'
845 --
846
847 ELSIF csr_bal_rec.balance_name = 'Extra Emolument Taxable Earnings' THEN
848 g_def_balance_tab(2).defined_balance_id := csr_bal_rec.defined_balance_id;
849
850 -- Bug 5846247
851 -- Added Balance 'KiwiSaver Employer Contributions'
852 ELSIF csr_bal_rec.balance_name = 'KiwiSaver Employer Contributions' THEN
853 g_def_balance_tab(3).defined_balance_id := csr_bal_rec.defined_balance_id;
854 -- Bug 7668520
855 -- Added Balance 'Employer Specified Superannuation Contributions'
856 ELSIF csr_bal_rec.balance_name = 'Employer Specified Superannuation Contributions' THEN
857 g_def_balance_tab(4).defined_balance_id := csr_bal_rec.defined_balance_id;
858
859 ELSIF csr_bal_rec.balance_name = 'Retro Ordinary Taxable Earnings' THEN
860
861 g_def_balance_tab(5).defined_balance_id := csr_bal_rec.defined_balance_id;
862
863 END IF;
864 IF g_debug THEN
865 hr_utility.set_location('Balance Name -> ' || csr_bal_rec.balance_name,40);
866 hr_utility.set_location('Defined Balance ID -> ' ||csr_bal_rec.defined_balance_id ,50);
867 END IF;
868
869 END LOOP;
870
871 IF g_debug THEN
872 hr_utility.set_location('Leaving ' || l_procedure,60);
873 END IF;
874
875 --
876 EXCEPTION
877 WHEN OTHERS THEN
878
879 IF csr_get_element_ids%ISOPEN THEN
880 --
881 CLOSE csr_get_element_ids;
882 --
883 END IF;
884
885 IF csr_defined_balances%ISOPEN THEN
886 --
887 CLOSE csr_defined_balances;
888 --
889 END IF;
890
891 IF g_debug THEN
892 hr_utility.set_location('Error in ' || l_procedure,30);
893 END IF;
894
895 RAISE;
896 --
897 END initialization_code;
898
899
900 --------------------------------------------------------------------------
901 -- --
902 -- Name : ARCHIVE_CODE --
903 -- Type : PROCEDURE --
904 -- Access : Public --
905 -- Description : Procedure to archive the details of an employees --
906 -- SSCWT Rates. --
907 -- --
908 -- Parameters : --
909 -- IN : p_assignment_action_id NUMBER --
910 -- p_effective_date DATE --
911 -- --
912 -- OUT : N/A --
913 -- --
914 -- Change History : --
915 --------------------------------------------------------------------------
916 -- Rev# Date Userid Description --
917 --------------------------------------------------------------------------
918 -- 115.0 22-JAN-2004 sshankar Initial Version --
919 -- 115.1 28-JAN-2004 sshankar Modified cursor csr_assignment_action --
920 -- to handle cases where Balance --
921 -- adjustment is also run. --
922 -- 115.2 31-MAY-2004 sshankar Changed code so as not to include --
923 -- SSCWT contributions for last year --
924 -- while calculating last year earnings. --
925 -- (Bug 3609069) --
926 -- 115.8 01-MAR-2007 dduvvuri Changed g_def_balance_tab(3).balance_value --
927 -- to g_def_balance_tab(2).balance_value --
928 -- 115.9 02-MAR-2007 dduvvuri Added Bug Reference,Fix Description at --
929 -- place of change in the previous version --
930 -- 115.10 12-Apr-2007 dduvvuri Added KiwiSaver Employer Contributions to the
931 -- Yearly Earnings.
932 -- 115.11 02-feb-2009 dduvvuri 7668520 - Added Employer Specified Superannuation
933 -- Cont to the Yearly Earnings
934 --------------------------------------------------------------------------
935 --
936
937 PROCEDURE archive_code(p_assignment_action_id IN NUMBER
938 ,p_effective_date IN DATE)
939 IS
940 --
941 -- Local Variables
942 --
943 l_next_assignment_action_id NUMBER;
944 l_procedure VARCHAR2(100);
945 l_flag BOOLEAN;
946
947 l_yearly_value NUMBER ;
948 l_action_info_id NUMBER ;
949 l_ovn NUMBER ;
950
951 l_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE;
952 l_assignment_id per_assignments_f.assignment_id%TYPE;
953 l_sscwt_new_rate NUMBER;
954 l_sscwt_old_rate NUMBER;
955 l_periods NUMBER;
956 l_total_periods NUMBER;
957 l_employee_full_name per_people_f.full_name%TYPE;
958 l_assignment_number per_assignments_f.assignment_number%TYPE;
959 l_element_entry_id pay_element_entries_f.element_entry_id%TYPE;
960 --
961 -- Cursor Declarations
962 --
963 CURSOR csr_employees
964 (c_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE
965 ,c_business_group_id per_people_f.business_group_id%TYPE)
966 IS
967 SELECT person.full_name
968 ,assignment.assignment_number
969 ,assignment.assignment_id
970 ,periods_in_span(service.date_start, g_financial_year
971 , assignment.assignment_id) periods
972 ,period_types.number_per_fiscal_year total_periods
973 FROM per_people_f person,
974 per_assignments_f assignment,
975 per_periods_of_service service,
976 pay_payrolls_f payroll,
977 per_time_period_types period_types,
978 pay_assignment_actions actions
979 WHERE person.business_group_id = c_business_group_id
980 AND actions.assignment_action_id = c_assignment_action_id
981 AND assignment.assignment_id = actions.assignment_id
982 AND assignment.person_id = person.person_id
983 AND assignment.business_group_id = person.business_group_id
984 AND service.period_of_service_id = assignment.period_of_service_id
985 AND payroll.business_group_id = person.business_group_id
986 AND payroll.payroll_id = assignment.payroll_id
987 AND period_types.period_type = payroll.period_type
988 AND g_financial_year BETWEEN person.effective_start_date
989 AND person.effective_end_date
990 AND g_financial_year BETWEEN assignment.effective_start_date
991 AND assignment.effective_end_date
992 AND g_financial_year BETWEEN payroll.effective_start_date
993 AND payroll.effective_end_date
994 AND g_financial_year BETWEEN service.date_start
995 AND NVL(service.actual_termination_date, TO_DATE('31-12-4712', 'DD-MM-YYYY'));
996
997 --
998 CURSOR csr_assignment_action(c_financial_year DATE
999 ,c_assignment_id per_assignments_f.assignment_id%TYPE
1000 )
1001 IS
1002 SELECT max_asg_act.assignment_action_id
1003 FROM pay_assignment_actions max_asg_act
1004 WHERE max_asg_act.assignment_id = c_assignment_id
1005 AND max_asg_act.action_sequence = (
1006 SELECT max(asg_action.action_sequence) action_sequence
1007 FROM pay_assignment_actions asg_action,
1008 pay_payroll_actions pay_action
1009 WHERE asg_action.assignment_id = c_assignment_id
1010 AND asg_action.payroll_action_id = pay_action.payroll_action_id
1011 AND asg_action.action_status = 'C'
1012 AND pay_action.action_status = 'C'
1013 AND pay_action.action_type in ('R', 'Q', 'B')
1014 AND pay_action.effective_date BETWEEN add_months(c_financial_year,-12)
1015 AND c_financial_year-1);
1016 --
1017 CURSOR csr_get_old_sscwt_rate
1018 (c_input_value_id pay_input_values_f.input_value_id%TYPE
1019 ,c_element_type_id pay_input_values_f.element_type_id%TYPE
1020 ,c_assignment_id per_assignments_f.assignment_id%TYPE )
1021 IS
1022 SELECT DECODE(inputv.hot_default_flag,'Y'
1023 ,NVL(entry_value.screen_entry_value, NVL(link.default_value
1024 ,inputv.default_value)),'N',entry_value.screen_entry_value) value
1025 ,entry.element_entry_id element_entry_id
1026 FROM pay_element_entry_values_f entry_value,
1027 pay_element_entries_f entry,
1028 pay_link_input_values_f link,
1029 pay_input_values_f inputv
1030 WHERE inputv.input_value_id = c_input_value_id
1031 AND g_financial_year between inputv.effective_start_date
1032 and inputv.effective_end_date
1033 AND inputv.element_type_id + 0 = c_element_type_id
1034 AND link.input_value_id = inputv.input_value_id
1035 AND g_financial_year between link.effective_start_date
1036 and link.effective_end_date
1037 AND entry_value.input_value_id + 0 = inputv.input_value_id
1038 AND entry_value.element_entry_id = entry.element_entry_id
1039 AND entry_value.effective_start_date = entry.effective_start_date
1040 AND entry_value.effective_end_date = entry.effective_end_date
1041 AND entry.element_link_id = link.element_link_id
1042 AND entry.assignment_id = c_assignment_id
1043 AND g_financial_year between entry.effective_start_date
1044 and entry.effective_end_date
1045 AND NVL(entry.entry_type, 'E') = 'E';
1046 --
1047
1048 BEGIN
1049 --
1050 -- print the debug messages if debug is enabled.
1051 --
1052 g_debug := hr_utility.debug_enabled;
1053 IF g_debug THEN
1054 l_procedure := g_package||'archive_code';
1055 hr_utility.set_location('Entering '||l_procedure, 10);
1056 hr_utility.trace('Assignment Action ID-> ' || p_assignment_action_id);
1057 hr_utility.trace('Effective Date-> ' || p_effective_date);
1058 END IF;
1059
1060 --
1061 -- Fetch employee details like name, assignment number, number of periods
1062 -- for which the payroll is run and total number of pay periods in that
1063 -- financial year.
1064 --
1065 OPEN csr_employees(p_assignment_action_id, g_business_group_id);
1066 FETCH csr_employees INTO l_employee_full_name
1067 , l_assignment_number
1068 , l_assignment_id
1069 , l_periods
1070 , l_total_periods;
1071 CLOSE csr_employees;
1072 --
1073 IF g_debug THEN
1074 hr_utility.set_location('In '||l_procedure, 20);
1075 hr_utility.trace('Employee Full Name -> ' || l_employee_full_name);
1076 hr_utility.trace('Assignment Number -> ' || l_assignment_number);
1077 hr_utility.trace('Assignment ID -> ' || l_assignment_id);
1078 hr_utility.trace('Periods -> ' || l_periods);
1079 hr_utility.trace('Total Periods -> ' || l_total_periods);
1080 END IF;
1081
1082 --
1083 IF l_periods = 0 THEN
1084 --
1085 IF g_debug THEN
1086 hr_utility.set_location('Before creating action Information' || l_procedure, 30);
1087 END IF;
1088 --
1089
1090 pay_action_information_api.create_action_information
1091 (
1092 p_action_information_id => l_action_info_id,
1093 p_object_version_number => l_ovn,
1094 p_action_context_id => p_assignment_action_id,
1095 p_action_context_type => 'AAP',
1096 p_action_information_category => 'NZ SSCWT DETAILS',
1097 p_effective_date => g_financial_year,
1098 p_assignment_id => l_assignment_id,
1099 p_action_information1 => l_assignment_number,
1100 p_action_information2 => l_employee_full_name,
1101 p_action_information3 => null,
1102 p_action_information4 => null,
1103 p_action_information5 => null,
1104 p_action_information6 => 'FAILURE'
1105 );
1106
1107 return;
1108 END IF;
1109 --
1110 -- Archive the details of the employee. The steps are:-
1111 -- 1. Get the lastest assignment action id.
1112 -- 2. Use a local copy of defined_balance_lst table so the data is
1113 -- consistent across mutliple threads.
1114 -- 3. Calculate the balance value
1115 -- 4. Calculate the Yearly Value
1116 -- 5. Calculate the New SSCWT Rate
1117 -- 6. Fetch the value of the current SSCWT Rate using route code.
1118 -- 7. Archive the details if the Rate is to be changed.
1119 --
1120
1121 -- 1. Get the lastest assignment action id.
1122 --
1123 OPEN csr_assignment_action(g_financial_year, l_assignment_id);
1124 FETCH csr_assignment_action INTO l_assignment_action_id;
1125 CLOSE csr_assignment_action;
1126 --
1127 IF g_debug THEN
1128 hr_utility.set_location('l_assignment_action_id -> '||l_assignment_action_id, 40);
1129 hr_utility.set_location('Before calling procedure pay_balance_pkg.get_value', 50);
1130 END IF;
1131
1132 -- 3. Calculate the YTD balance values for the required balances.
1133 --
1134
1135 pay_balance_pkg.get_value
1136 (p_assignment_action_id => l_assignment_action_id
1137 ,p_defined_balance_lst => g_def_balance_tab
1138 );
1139
1140 --
1141 IF g_debug THEN
1142 hr_utility.set_location('In '||l_procedure, 60);
1143 hr_utility.trace('Ordinary Taxable Earnings -> ' || g_def_balance_tab(1).balance_value);
1144
1145 /* Change for Bug 5904043 start*/
1146 -- Changed g_def_balance_tab(3).balance_value to g_def_balance_tab(2).balance_value
1147 hr_utility.trace('Extra Emoluments Taxable Earanings -> ' || g_def_balance_tab(2).balance_value);
1148 /* Change for Bug 5904043 end*/
1149 /* Change for Bug 5846247 */
1150 hr_utility.trace('KiwiSaver Employer Contributions -> ' || g_def_balance_tab(3).balance_value);
1151 /* Added below condition for bug 7668520 */
1152 if g_financial_year >= TO_DATE(g_start_dd_mm ||'2009', 'DD-MM-YYYY') then
1153 hr_utility.trace('Employer Specified Superannuation Contributions -> ' || g_def_balance_tab(4).balance_value);
1154 end if;
1155 hr_utility.trace('Retro Ordinary Taxable Earnings -> ' || g_def_balance_tab(5).balance_value);
1156
1157 END IF;
1158
1159 -- 4. Calculate the Yearly Value
1160 --
1161
1162 -- Bug 3609069
1163 -- Removed code which includes SSCWT contributions for last year
1164 -- Bug 5846247
1165 -- Added "KiwiSaver Employer Contributions" to Yearly Value.
1166 l_yearly_value := g_def_balance_tab(1).balance_value -- Ordinary Ear
1167 + g_def_balance_tab(2).balance_value -- Extra Emol Ear
1168 + g_def_balance_tab(3).balance_value; -- KiwiSaver Employer Contributions
1169 /* Added below condition for bug 7668520 */
1170 if g_financial_year >= TO_DATE(g_start_dd_mm || '2009', 'DD-MM-YYYY') then
1171 l_yearly_value := l_yearly_value + g_def_balance_tab(4).balance_value;
1172 end if;
1173
1174 l_yearly_value := l_yearly_value + g_def_balance_tab(5).balance_value;
1175
1176 --
1177 -- Req: Employee's who have commenced during the previous financial year
1178 -- must have their earnings converted to a yearly figure to ensure
1179 -- a correct rate of calculation.
1180 --
1181 l_yearly_value := (l_yearly_value / l_periods) * l_total_periods;
1182 IF g_debug THEN
1183 hr_utility.set_location('l_yearly_value ->' ||l_yearly_value, 70);
1184 END IF;
1185
1186 --
1187 -- 5. Calculate the New SSCWT Rate
1188
1189 l_sscwt_new_rate := hruserdt.get_table_value
1190 (g_business_group_id
1191 ,'NZ SSCWT Rate Ranges'
1192 ,'SSCWT Rate'
1193 ,trunc(l_yearly_value, 2)
1194 ,g_financial_year);
1195
1196 -- 6. Fetch the current value of SSCWT Rate using route code.
1197 --
1198 OPEN csr_get_old_sscwt_rate(g_input_value_id
1199 ,g_element_type_id
1200 ,l_assignment_id);
1201 FETCH csr_get_old_sscwt_rate into l_sscwt_old_rate, l_element_entry_id;
1202 CLOSE csr_get_old_sscwt_rate;
1203 --
1204 IF g_debug = true THEN
1205 hr_utility.set_location('In '|| l_procedure,80);
1206 hr_utility.trace('Old SSCWT Rate -> ' || l_sscwt_old_rate);
1207 hr_utility.trace('New SSCWT Rate -> ' || l_sscwt_new_rate);
1208 hr_utility.trace('Yearly Value -> ' || l_yearly_value);
1209 END IF;
1210
1211 --
1212 -- 7. Archive the details if the Rate is to be changed.
1213 --
1214 IF l_sscwt_old_rate <> l_sscwt_new_rate THEN
1215 --
1216 IF g_processing_mode = 'A' THEN
1217 --
1218 IF g_debug THEN
1219 hr_utility.set_location('Processing Automatic mode in '||l_procedure, 90);
1220 END IF;
1221 --
1222 -- 1. Update the SSCWT Rate in SSCWT Information element.
1223 --
1224 l_flag := update_sscwt_rate(p_sscwt_rate => l_sscwt_new_rate
1225 ,p_sscwt_element_entry_id => l_element_entry_id
1226 ,p_effective_date => g_financial_year);
1227 --
1228 IF l_flag = true THEN
1229 IF g_debug THEN
1230 hr_utility.set_location('On successful update of element entry value', 100);
1231 END IF;
1232
1233 pay_action_information_api.create_action_information
1234 (
1235 p_action_information_id => l_action_info_id,
1236 p_object_version_number => l_ovn,
1237 p_action_context_id => p_assignment_action_id,
1238 p_action_context_type => 'AAP',
1239 p_action_information_category => 'NZ SSCWT DETAILS',
1240 p_effective_date => g_financial_year,
1241 p_assignment_id => l_assignment_id,
1242 p_action_information1 => l_assignment_number,
1243 p_action_information2 => l_employee_full_name,
1244 p_action_information3 => l_sscwt_old_rate,
1245 p_action_information4 => l_sscwt_new_rate,
1246 p_action_information5 => l_yearly_value,
1247 p_action_information6 => 'AUTOMATIC'
1248 );
1249
1250 ELSE --if updation failed.
1251 IF g_debug THEN
1252 hr_utility.set_location('On failure of update of element entry value', 110);
1253 END IF;
1254
1255 pay_action_information_api.create_action_information
1256 (
1257 p_action_information_id => l_action_info_id,
1258 p_object_version_number => l_ovn,
1259 p_action_context_id => p_assignment_action_id,
1260 p_action_context_type => 'AAP',
1261 p_action_information_category => 'NZ SSCWT DETAILS',
1262 p_effective_date => g_financial_year,
1263 p_assignment_id => l_assignment_id,
1264 p_action_information1 => l_assignment_number,
1265 p_action_information2 => l_employee_full_name,
1266 p_action_information3 => l_sscwt_old_rate,
1267 p_action_information4 => l_sscwt_new_rate,
1268 p_action_information5 => l_yearly_value,
1269 p_action_information6 => 'FAILURE'
1270 );
1271
1272 END IF; --l_flag is true
1273
1274 ELSE --If processing mode is manual
1275 IF g_debug THEN
1276 hr_utility.set_location('Processing Manual Mode', 110);
1277 END IF;
1278
1279 pay_action_information_api.create_action_information
1280 (
1281 p_action_information_id => l_action_info_id,
1282 p_object_version_number => l_ovn,
1283 p_action_context_id => p_assignment_action_id,
1284 p_action_context_type => 'AAP',
1285 p_action_information_category => 'NZ SSCWT DETAILS',
1286 p_effective_date => g_financial_year,
1287 p_assignment_id => l_assignment_id,
1288 p_action_information1 => l_assignment_number,
1289 p_action_information2 => l_employee_full_name,
1290 p_action_information3 => l_sscwt_old_rate,
1291 p_action_information4 => l_sscwt_new_rate,
1292 p_action_information5 => l_yearly_value,
1293 p_action_information6 => 'MANUAL'
1294 );
1295
1296 END IF; --l_processing mode is automatic
1297 --
1298 IF g_debug THEN
1299 hr_utility.set_location('After Creating action Information in Maual mode', 120);
1300 END IF;
1301 --
1302 END IF; -- old sscwt rate not equal to new sscwt rate
1303
1304 IF g_debug THEN
1305 hr_utility.set_location('Leaving ' ||l_procedure, 130);
1306 END IF;
1307
1308 EXCEPTION
1309 WHEN OTHERS THEN
1310 IF csr_assignment_action%ISOPEN THEN
1311 IF g_debug THEN
1312 hr_utility.set_location('Error: Closing cursor csr_assignment_action', 140);
1313 END IF;
1314 CLOSE csr_assignment_action;
1315 END IF;
1316 IF csr_get_old_sscwt_rate%ISOPEN THEN
1317 IF g_debug THEN
1318 hr_utility.set_location('Error: Closing cursor csr_sscwt_route', 140);
1319 END IF;
1320 CLOSE csr_get_old_sscwt_rate;
1321 END IF;
1322 IF csr_employees%ISOPEN THEN
1323 IF g_debug THEN
1324 hr_utility.set_location('Error: Closing cursor csr_employees', 140);
1325 END IF;
1326 CLOSE csr_employees;
1327 END IF;
1328 IF g_debug THEN
1329 hr_utility.set_location('Error: In archive_code', 150);
1330 END IF;
1331 RAISE;
1332 END archive_code;
1333
1334
1335
1336 --------------------------------------------------------------------------
1337 -- --
1338 -- Name : DEINITIALIZE_CODE --
1339 -- Type : PROCEDURE --
1340 -- Access : Public --
1341 -- Description : Procedure to submit request for running report, --
1342 -- SSCWT Report. --
1343 -- --
1344 -- Parameters : --
1345 -- IN : p_payroll_action_id NUMBER --
1346 -- --
1347 -- OUT : N/A --
1348 -- --
1349 -- Change History : --
1350 --------------------------------------------------------------------------
1351 -- Rev# Date Userid Description --
1352 --------------------------------------------------------------------------
1353 -- 115.0 22-JAN-2004 sshankar Initial Version --
1354 -- 115.1 22-JAN-2004 sshankar Added hr_utility.debug_enabled call to--
1355 -- initialize g_debug variable. --
1356 --------------------------------------------------------------------------
1357 --
1358 PROCEDURE deinitialize_code (p_payroll_action_id IN NUMBER)
1359 IS
1360
1361 l_procedure VARCHAR2(100);
1362 --
1363 BEGIN
1364 --
1365 -- print the debug messages if debug is enabled.
1366 --
1367 g_debug := hr_utility.debug_enabled;
1368
1369 IF g_debug THEN
1370 l_procedure := g_package || 'deinitialize_code' ;
1371 hr_utility.set_location('Entering '||l_procedure,10);
1372 END IF;
1373
1374 get_legislative_parameters(p_payroll_action_id);
1375
1376 IF g_debug THEN
1377 hr_utility.set_location('After Calling get_legislative_parameters',20);
1378 END IF;
1379
1380 -- Call procedure to submit request for report.
1381 submit_sscwt_report;
1382
1383 IF g_debug THEN
1384 hr_utility.set_location('Leaving '||l_procedure,30);
1385 END IF;
1386
1387 EXCEPTION
1388 WHEN OTHERS THEN
1389 IF g_debug THEN
1390 hr_utility.set_location('Error in '||l_procedure,40);
1391 END IF;
1392 RAISE;
1393
1394 END deinitialize_code;
1395
1396 --
1397 -- Assign Global variables in this unnamed block. This is to avoid assigning global variables within procedures
1398 -- for each thread that executes the procedure.
1399 --
1400 Begin
1401 g_debug := hr_utility.debug_enabled ;
1402 g_package := 'pay_nz_sscwt_rate_archive.' ;
1403 g_start_dd_mm := '01-04-' ;
1404 g_legislation_code := 'NZ' ;
1405 g_report_short_name := 'PYNZSSRP' ;
1406 g_element_type_id := null ;
1407 g_input_value_id := null ;
1408 --
1409 END pay_nz_sscwt_rate_archive;