[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.2.12000000.5 2007/04/13 05:46:41 dduvvuri noship $ */
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 --------------------------------------------------------------------------
736 --
737
738 PROCEDURE initialization_code (
739 p_payroll_action_id IN NUMBER
740 )
741 IS
742 --
743 l_procedure VARCHAR2(100) ;
744 l_balance_name pay_balance_types.balance_name%TYPE;
745 l_defined_balance_id pay_defined_balances.defined_balance_id%TYPE;
746 --
747
748 --
749 -- Cursor to fetch element type id and input value id for 'SSCWT Information' element
750 -- which are later used to fetch input value of the element for corresponding assignments.
751 --
752 CURSOR csr_get_element_ids(c_financial_year DATE)
753 IS
754 SELECT pet.element_type_id, piv.input_value_id
755 FROM pay_element_types_f pet
756 ,pay_input_values_f piv
757 WHERE pet.element_name = 'SSCWT Information'
758 AND pet.element_type_id = piv.element_type_id
759 AND piv.name = 'SSCWT Rate'
760 AND c_financial_year BETWEEN pet.effective_start_date
761 AND pet.effective_end_date
762 AND c_financial_year BETWEEN piv.effective_start_date
763 AND piv.effective_end_date ;
764 --
765
766 --
767 -- Modified the cursor to have all three balances name and Dimension so as to
768 -- avoid executing it thrice for three balances.
769 --
770 -- Cursor to fetch defined Balance ID
771 --
772
773 --
774 -- Bug 3609069
775 -- Removed Balance 'Employer Specified Superannuation Contributions'
776 --
777 -- Bug 5846247
778 -- Added Balance 'KiwiSaver Employer Contributions'
779
780 CURSOR csr_defined_balances
781 IS
782 SELECT defined.defined_balance_id
783 ,bal.balance_name balance_name
784 FROM pay_balance_types bal
785 , pay_balance_dimensions dim
786 , pay_defined_balances defined
787 WHERE bal.legislation_code = g_legislation_code
788 AND bal.balance_name IN ( 'Ordinary Taxable Earnings'
789 ,'Extra Emolument Taxable Earnings'
790 ,'KiwiSaver Employer Contributions'
791 )
792 AND dim.legislation_code = g_legislation_code
793 AND dim.dimension_name = '_ASG_YTD'
794 AND bal.balance_type_id = defined.balance_type_id
795 AND dim.balance_dimension_id = defined.balance_dimension_id;
796 --
797
798 BEGIN
799 --
800 --
801 -- print the debug messages if debug is enabled.
802 --
803 g_debug := hr_utility.debug_enabled;
804
805 IF g_debug THEN
806 l_procedure := g_package || 'initialization_code' ;
807 hr_utility.set_location('Entering ' || l_procedure,10);
808 END IF;
809
810 -- Get the legislative parameters and store them in global variables.
811 get_legislative_parameters(p_payroll_action_id);
812
813 IF g_debug THEN
814 hr_utility.set_location('p_payroll_action_id -> ' || p_payroll_action_id, 15);
815 END IF;
816
817 -- Fetch element_type_id and input_value_id into global variables.
818 OPEN csr_get_element_ids(g_financial_year) ;
819 FETCH csr_get_element_ids INTO g_element_type_id, g_input_value_id ;
820 CLOSE csr_get_element_ids ;
821
822 IF g_debug THEN
823 hr_utility.set_location('g_element_type_id -> ' ||g_element_type_id ,20);
824 hr_utility.set_location('g_input_value_id -> ' ||g_input_value_id ,30);
825 END IF;
826
827 --
828 -- Modified the cursor to have all three balances name and Dimension so as to
829 -- avoid executing it thrice for three balances.
830 --
831 -- Fetch Balance IDs into global variables.
832
833 FOR csr_bal_rec IN csr_defined_balances
834 LOOP
835 IF csr_bal_rec.balance_name = 'Ordinary Taxable Earnings' THEN
836 g_def_balance_tab(1).defined_balance_id := csr_bal_rec.defined_balance_id;
837
838 -- Bug 3609069
839 -- Removed code to handle balance 'Employer Specified Superannuation Contributions'
840 --
841
842 ELSIF csr_bal_rec.balance_name = 'Extra Emolument Taxable Earnings' THEN
843 g_def_balance_tab(2).defined_balance_id := csr_bal_rec.defined_balance_id;
844
845 -- Bug 5846247
846 -- Added Balance 'KiwiSaver Employer Contributions'
847 ELSIF csr_bal_rec.balance_name = 'KiwiSaver Employer Contributions' THEN
848 g_def_balance_tab(3).defined_balance_id := csr_bal_rec.defined_balance_id;
849
850 END IF;
851 IF g_debug THEN
852 hr_utility.set_location('Balance Name -> ' || csr_bal_rec.balance_name,40);
853 hr_utility.set_location('Defined Balance ID -> ' ||csr_bal_rec.defined_balance_id ,50);
854 END IF;
855
856 END LOOP;
857
858 IF g_debug THEN
859 hr_utility.set_location('Leaving ' || l_procedure,60);
860 END IF;
861
862 --
863 EXCEPTION
864 WHEN OTHERS THEN
865
866 IF csr_get_element_ids%ISOPEN THEN
867 --
868 CLOSE csr_get_element_ids;
869 --
870 END IF;
871
872 IF csr_defined_balances%ISOPEN THEN
873 --
874 CLOSE csr_defined_balances;
875 --
876 END IF;
877
878 IF g_debug THEN
879 hr_utility.set_location('Error in ' || l_procedure,30);
880 END IF;
881
882 RAISE;
883 --
884 END initialization_code;
885
886
887 --------------------------------------------------------------------------
888 -- --
889 -- Name : ARCHIVE_CODE --
890 -- Type : PROCEDURE --
891 -- Access : Public --
892 -- Description : Procedure to archive the details of an employees --
893 -- SSCWT Rates. --
894 -- --
895 -- Parameters : --
896 -- IN : p_assignment_action_id NUMBER --
897 -- p_effective_date DATE --
898 -- --
899 -- OUT : N/A --
900 -- --
901 -- Change History : --
902 --------------------------------------------------------------------------
903 -- Rev# Date Userid Description --
904 --------------------------------------------------------------------------
905 -- 115.0 22-JAN-2004 sshankar Initial Version --
906 -- 115.1 28-JAN-2004 sshankar Modified cursor csr_assignment_action --
907 -- to handle cases where Balance --
908 -- adjustment is also run. --
909 -- 115.2 31-MAY-2004 sshankar Changed code so as not to include --
910 -- SSCWT contributions for last year --
911 -- while calculating last year earnings. --
912 -- (Bug 3609069) --
913 -- 115.8 01-MAR-2007 dduvvuri Changed g_def_balance_tab(3).balance_value --
914 -- to g_def_balance_tab(2).balance_value --
915 -- 115.9 02-MAR-2007 dduvvuri Added Bug Reference,Fix Description at --
916 -- place of change in the previous version --
917 -- 115.10 12-Apr-2007 dduvvuri Added KiwiSaver Employer Contributions to the
918 -- Yearly Earnings.
919 --------------------------------------------------------------------------
920 --
921
922 PROCEDURE archive_code(p_assignment_action_id IN NUMBER
923 ,p_effective_date IN DATE)
924 IS
925 --
926 -- Local Variables
927 --
928 l_next_assignment_action_id NUMBER;
929 l_procedure VARCHAR2(100);
930 l_flag BOOLEAN;
931
932 l_yearly_value NUMBER ;
933 l_action_info_id NUMBER ;
934 l_ovn NUMBER ;
935
936 l_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE;
937 l_assignment_id per_assignments_f.assignment_id%TYPE;
938 l_sscwt_new_rate NUMBER;
939 l_sscwt_old_rate NUMBER;
940 l_periods NUMBER;
941 l_total_periods NUMBER;
942 l_employee_full_name per_people_f.full_name%TYPE;
943 l_assignment_number per_assignments_f.assignment_number%TYPE;
944 l_element_entry_id pay_element_entries_f.element_entry_id%TYPE;
945 --
946 -- Cursor Declarations
947 --
948 CURSOR csr_employees
949 (c_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE
950 ,c_business_group_id per_people_f.business_group_id%TYPE)
951 IS
952 SELECT person.full_name
953 ,assignment.assignment_number
954 ,assignment.assignment_id
955 ,periods_in_span(service.date_start, g_financial_year
956 , assignment.assignment_id) periods
957 ,period_types.number_per_fiscal_year total_periods
958 FROM per_people_f person,
959 per_assignments_f assignment,
960 per_periods_of_service service,
961 pay_payrolls_f payroll,
962 per_time_period_types period_types,
963 pay_assignment_actions actions
964 WHERE person.business_group_id = c_business_group_id
965 AND actions.assignment_action_id = c_assignment_action_id
966 AND assignment.assignment_id = actions.assignment_id
967 AND assignment.person_id = person.person_id
968 AND assignment.business_group_id = person.business_group_id
969 AND service.period_of_service_id = assignment.period_of_service_id
970 AND payroll.business_group_id = person.business_group_id
971 AND payroll.payroll_id = assignment.payroll_id
972 AND period_types.period_type = payroll.period_type
973 AND g_financial_year BETWEEN person.effective_start_date
974 AND person.effective_end_date
975 AND g_financial_year BETWEEN assignment.effective_start_date
976 AND assignment.effective_end_date
977 AND g_financial_year BETWEEN payroll.effective_start_date
978 AND payroll.effective_end_date
979 AND g_financial_year BETWEEN service.date_start
980 AND NVL(service.actual_termination_date, TO_DATE('31-12-4712', 'DD-MM-YYYY'));
981
982 --
983 CURSOR csr_assignment_action(c_financial_year DATE
984 ,c_assignment_id per_assignments_f.assignment_id%TYPE
985 )
986 IS
987 SELECT max_asg_act.assignment_action_id
988 FROM pay_assignment_actions max_asg_act
989 WHERE max_asg_act.assignment_id = c_assignment_id
990 AND max_asg_act.action_sequence = (
991 SELECT max(asg_action.action_sequence) action_sequence
992 FROM pay_assignment_actions asg_action,
993 pay_payroll_actions pay_action
994 WHERE asg_action.assignment_id = c_assignment_id
995 AND asg_action.payroll_action_id = pay_action.payroll_action_id
996 AND asg_action.action_status = 'C'
997 AND pay_action.action_status = 'C'
998 AND pay_action.action_type in ('R', 'Q', 'B')
999 AND pay_action.effective_date BETWEEN add_months(c_financial_year,-12)
1000 AND c_financial_year-1);
1001 --
1002 CURSOR csr_get_old_sscwt_rate
1003 (c_input_value_id pay_input_values_f.input_value_id%TYPE
1004 ,c_element_type_id pay_input_values_f.element_type_id%TYPE
1005 ,c_assignment_id per_assignments_f.assignment_id%TYPE )
1006 IS
1007 SELECT DECODE(inputv.hot_default_flag,'Y'
1008 ,NVL(entry_value.screen_entry_value, NVL(link.default_value
1009 ,inputv.default_value)),'N',entry_value.screen_entry_value) value
1010 ,entry.element_entry_id element_entry_id
1011 FROM pay_element_entry_values_f entry_value,
1012 pay_element_entries_f entry,
1013 pay_link_input_values_f link,
1014 pay_input_values_f inputv
1015 WHERE inputv.input_value_id = c_input_value_id
1016 AND g_financial_year between inputv.effective_start_date
1017 and inputv.effective_end_date
1018 AND inputv.element_type_id + 0 = c_element_type_id
1019 AND link.input_value_id = inputv.input_value_id
1020 AND g_financial_year between link.effective_start_date
1021 and link.effective_end_date
1022 AND entry_value.input_value_id + 0 = inputv.input_value_id
1023 AND entry_value.element_entry_id = entry.element_entry_id
1024 AND entry_value.effective_start_date = entry.effective_start_date
1025 AND entry_value.effective_end_date = entry.effective_end_date
1026 AND entry.element_link_id = link.element_link_id
1027 AND entry.assignment_id = c_assignment_id
1028 AND g_financial_year between entry.effective_start_date
1029 and entry.effective_end_date
1030 AND NVL(entry.entry_type, 'E') = 'E';
1031 --
1032
1033 BEGIN
1034 --
1035 -- print the debug messages if debug is enabled.
1036 --
1037 g_debug := hr_utility.debug_enabled;
1038 IF g_debug THEN
1039 l_procedure := g_package||'archive_code';
1040 hr_utility.set_location('Entering '||l_procedure, 10);
1041 hr_utility.trace('Assignment Action ID-> ' || p_assignment_action_id);
1042 hr_utility.trace('Effective Date-> ' || p_effective_date);
1043 END IF;
1044
1045 --
1046 -- Fetch employee details like name, assignment number, number of periods
1047 -- for which the payroll is run and total number of pay periods in that
1048 -- financial year.
1049 --
1050 OPEN csr_employees(p_assignment_action_id, g_business_group_id);
1051 FETCH csr_employees INTO l_employee_full_name
1052 , l_assignment_number
1053 , l_assignment_id
1054 , l_periods
1055 , l_total_periods;
1056 CLOSE csr_employees;
1057 --
1058 IF g_debug THEN
1059 hr_utility.set_location('In '||l_procedure, 20);
1060 hr_utility.trace('Employee Full Name -> ' || l_employee_full_name);
1061 hr_utility.trace('Assignment Number -> ' || l_assignment_number);
1062 hr_utility.trace('Assignment ID -> ' || l_assignment_id);
1063 hr_utility.trace('Periods -> ' || l_periods);
1064 hr_utility.trace('Total Periods -> ' || l_total_periods);
1065 END IF;
1066
1067 --
1068 IF l_periods = 0 THEN
1069 --
1070 IF g_debug THEN
1071 hr_utility.set_location('Before creating action Information' || l_procedure, 30);
1072 END IF;
1073 --
1074
1075 pay_action_information_api.create_action_information
1076 (
1077 p_action_information_id => l_action_info_id,
1078 p_object_version_number => l_ovn,
1079 p_action_context_id => p_assignment_action_id,
1080 p_action_context_type => 'AAP',
1081 p_action_information_category => 'NZ SSCWT DETAILS',
1082 p_effective_date => g_financial_year,
1083 p_assignment_id => l_assignment_id,
1084 p_action_information1 => l_assignment_number,
1085 p_action_information2 => l_employee_full_name,
1086 p_action_information3 => null,
1087 p_action_information4 => null,
1088 p_action_information5 => null,
1089 p_action_information6 => 'FAILURE'
1090 );
1091
1092 return;
1093 END IF;
1094 --
1095 -- Archive the details of the employee. The steps are:-
1096 -- 1. Get the lastest assignment action id.
1097 -- 2. Use a local copy of defined_balance_lst table so the data is
1098 -- consistent across mutliple threads.
1099 -- 3. Calculate the balance value
1100 -- 4. Calculate the Yearly Value
1101 -- 5. Calculate the New SSCWT Rate
1102 -- 6. Fetch the value of the current SSCWT Rate using route code.
1103 -- 7. Archive the details if the Rate is to be changed.
1104 --
1105
1106 -- 1. Get the lastest assignment action id.
1107 --
1108 OPEN csr_assignment_action(g_financial_year, l_assignment_id);
1109 FETCH csr_assignment_action INTO l_assignment_action_id;
1110 CLOSE csr_assignment_action;
1111 --
1112 IF g_debug THEN
1113 hr_utility.set_location('l_assignment_action_id -> '||l_assignment_action_id, 40);
1114 hr_utility.set_location('Before calling procedure pay_balance_pkg.get_value', 50);
1115 END IF;
1116
1117 -- 3. Calculate the YTD balance values for the required balances.
1118 --
1119
1120 pay_balance_pkg.get_value
1121 (p_assignment_action_id => l_assignment_action_id
1122 ,p_defined_balance_lst => g_def_balance_tab
1123 );
1124
1125 --
1126 IF g_debug THEN
1127 hr_utility.set_location('In '||l_procedure, 60);
1128 hr_utility.trace('Ordinary Taxable Earnings -> ' || g_def_balance_tab(1).balance_value);
1129
1130 /* Change for Bug 5904043 start*/
1131 -- Changed g_def_balance_tab(3).balance_value to g_def_balance_tab(2).balance_value
1132 hr_utility.trace('Extra Emoluments Taxable Earanings -> ' || g_def_balance_tab(2).balance_value);
1133 /* Change for Bug 5904043 end*/
1134 /* Change for Bug 5846247 */
1135 hr_utility.trace('KiwiSaver Employer Contributions -> ' || g_def_balance_tab(3).balance_value);
1136 END IF;
1137
1138 -- 4. Calculate the Yearly Value
1139 --
1140
1141 -- Bug 3609069
1142 -- Removed code which includes SSCWT contributions for last year
1143 -- Bug 5846247
1144 -- Added "KiwiSaver Employer Contributions" to Yearly Value.
1145 l_yearly_value := g_def_balance_tab(1).balance_value -- Ordinary Ear
1146 + g_def_balance_tab(2).balance_value -- Extra Emol Ear
1147 + g_def_balance_tab(3).balance_value; -- KiwiSaver Employer Contributions
1148 --
1149 -- Req: Employee's who have commenced during the previous financial year
1150 -- must have their earnings converted to a yearly figure to ensure
1151 -- a correct rate of calculation.
1152 --
1153 l_yearly_value := (l_yearly_value / l_periods) * l_total_periods;
1154 IF g_debug THEN
1155 hr_utility.set_location('l_yearly_value ->' ||l_yearly_value, 70);
1156 END IF;
1157
1158 --
1159 -- 5. Calculate the New SSCWT Rate
1160
1161 l_sscwt_new_rate := hruserdt.get_table_value
1162 (g_business_group_id
1163 ,'NZ SSCWT Rate Ranges'
1164 ,'SSCWT Rate'
1165 ,trunc(l_yearly_value, 2)
1166 ,g_financial_year);
1167
1168 -- 6. Fetch the current value of SSCWT Rate using route code.
1169 --
1170 OPEN csr_get_old_sscwt_rate(g_input_value_id
1171 ,g_element_type_id
1172 ,l_assignment_id);
1173 FETCH csr_get_old_sscwt_rate into l_sscwt_old_rate, l_element_entry_id;
1174 CLOSE csr_get_old_sscwt_rate;
1175 --
1176 IF g_debug = true THEN
1177 hr_utility.set_location('In '|| l_procedure,80);
1178 hr_utility.trace('Old SSCWT Rate -> ' || l_sscwt_old_rate);
1179 hr_utility.trace('New SSCWT Rate -> ' || l_sscwt_new_rate);
1180 hr_utility.trace('Yearly Value -> ' || l_yearly_value);
1181 END IF;
1182
1183 --
1184 -- 7. Archive the details if the Rate is to be changed.
1185 --
1186 IF l_sscwt_old_rate <> l_sscwt_new_rate THEN
1187 --
1188 IF g_processing_mode = 'A' THEN
1189 --
1190 IF g_debug THEN
1191 hr_utility.set_location('Processing Automatic mode in '||l_procedure, 90);
1192 END IF;
1193 --
1194 -- 1. Update the SSCWT Rate in SSCWT Information element.
1195 --
1196 l_flag := update_sscwt_rate(p_sscwt_rate => l_sscwt_new_rate
1197 ,p_sscwt_element_entry_id => l_element_entry_id
1198 ,p_effective_date => g_financial_year);
1199 --
1200 IF l_flag = true THEN
1201 IF g_debug THEN
1202 hr_utility.set_location('On successful update of element entry value', 100);
1203 END IF;
1204
1205 pay_action_information_api.create_action_information
1206 (
1207 p_action_information_id => l_action_info_id,
1208 p_object_version_number => l_ovn,
1209 p_action_context_id => p_assignment_action_id,
1210 p_action_context_type => 'AAP',
1211 p_action_information_category => 'NZ SSCWT DETAILS',
1212 p_effective_date => g_financial_year,
1213 p_assignment_id => l_assignment_id,
1214 p_action_information1 => l_assignment_number,
1215 p_action_information2 => l_employee_full_name,
1216 p_action_information3 => l_sscwt_old_rate,
1217 p_action_information4 => l_sscwt_new_rate,
1218 p_action_information5 => l_yearly_value,
1219 p_action_information6 => 'AUTOMATIC'
1220 );
1221
1222 ELSE --if updation failed.
1223 IF g_debug THEN
1224 hr_utility.set_location('On failure of update of element entry value', 110);
1225 END IF;
1226
1227 pay_action_information_api.create_action_information
1228 (
1229 p_action_information_id => l_action_info_id,
1230 p_object_version_number => l_ovn,
1231 p_action_context_id => p_assignment_action_id,
1232 p_action_context_type => 'AAP',
1233 p_action_information_category => 'NZ SSCWT DETAILS',
1234 p_effective_date => g_financial_year,
1235 p_assignment_id => l_assignment_id,
1236 p_action_information1 => l_assignment_number,
1237 p_action_information2 => l_employee_full_name,
1238 p_action_information3 => l_sscwt_old_rate,
1239 p_action_information4 => l_sscwt_new_rate,
1240 p_action_information5 => l_yearly_value,
1241 p_action_information6 => 'FAILURE'
1242 );
1243
1244 END IF; --l_flag is true
1245
1246 ELSE --If processing mode is manual
1247 IF g_debug THEN
1248 hr_utility.set_location('Processing Manual Mode', 110);
1249 END IF;
1250
1251 pay_action_information_api.create_action_information
1252 (
1253 p_action_information_id => l_action_info_id,
1254 p_object_version_number => l_ovn,
1255 p_action_context_id => p_assignment_action_id,
1256 p_action_context_type => 'AAP',
1257 p_action_information_category => 'NZ SSCWT DETAILS',
1258 p_effective_date => g_financial_year,
1259 p_assignment_id => l_assignment_id,
1260 p_action_information1 => l_assignment_number,
1261 p_action_information2 => l_employee_full_name,
1262 p_action_information3 => l_sscwt_old_rate,
1263 p_action_information4 => l_sscwt_new_rate,
1264 p_action_information5 => l_yearly_value,
1265 p_action_information6 => 'MANUAL'
1266 );
1267
1268 END IF; --l_processing mode is automatic
1269 --
1270 IF g_debug THEN
1271 hr_utility.set_location('After Creating action Information in Maual mode', 120);
1272 END IF;
1273 --
1274 END IF; -- old sscwt rate not equal to new sscwt rate
1275
1276 IF g_debug THEN
1277 hr_utility.set_location('Leaving ' ||l_procedure, 130);
1278 END IF;
1279
1280 EXCEPTION
1281 WHEN OTHERS THEN
1282 IF csr_assignment_action%ISOPEN THEN
1283 IF g_debug THEN
1284 hr_utility.set_location('Error: Closing cursor csr_assignment_action', 140);
1285 END IF;
1286 CLOSE csr_assignment_action;
1287 END IF;
1288 IF csr_get_old_sscwt_rate%ISOPEN THEN
1289 IF g_debug THEN
1290 hr_utility.set_location('Error: Closing cursor csr_sscwt_route', 140);
1291 END IF;
1292 CLOSE csr_get_old_sscwt_rate;
1293 END IF;
1294 IF csr_employees%ISOPEN THEN
1295 IF g_debug THEN
1296 hr_utility.set_location('Error: Closing cursor csr_employees', 140);
1297 END IF;
1298 CLOSE csr_employees;
1299 END IF;
1300 IF g_debug THEN
1301 hr_utility.set_location('Error: In archive_code', 150);
1302 END IF;
1303 RAISE;
1304 END archive_code;
1305
1306
1307
1308 --------------------------------------------------------------------------
1309 -- --
1310 -- Name : DEINITIALIZE_CODE --
1311 -- Type : PROCEDURE --
1312 -- Access : Public --
1313 -- Description : Procedure to submit request for running report, --
1314 -- SSCWT Report. --
1315 -- --
1316 -- Parameters : --
1317 -- IN : p_payroll_action_id NUMBER --
1318 -- --
1319 -- OUT : N/A --
1320 -- --
1321 -- Change History : --
1322 --------------------------------------------------------------------------
1323 -- Rev# Date Userid Description --
1324 --------------------------------------------------------------------------
1325 -- 115.0 22-JAN-2004 sshankar Initial Version --
1326 -- 115.1 22-JAN-2004 sshankar Added hr_utility.debug_enabled call to--
1327 -- initialize g_debug variable. --
1328 --------------------------------------------------------------------------
1329 --
1330 PROCEDURE deinitialize_code (p_payroll_action_id IN NUMBER)
1331 IS
1332
1333 l_procedure VARCHAR2(100);
1334 --
1335 BEGIN
1336 --
1337 -- print the debug messages if debug is enabled.
1338 --
1339 g_debug := hr_utility.debug_enabled;
1340
1341 IF g_debug THEN
1342 l_procedure := g_package || 'deinitialize_code' ;
1343 hr_utility.set_location('Entering '||l_procedure,10);
1344 END IF;
1345
1346 get_legislative_parameters(p_payroll_action_id);
1347
1348 IF g_debug THEN
1349 hr_utility.set_location('After Calling get_legislative_parameters',20);
1350 END IF;
1351
1352 -- Call procedure to submit request for report.
1353 submit_sscwt_report;
1354
1355 IF g_debug THEN
1356 hr_utility.set_location('Leaving '||l_procedure,30);
1357 END IF;
1358
1359 EXCEPTION
1360 WHEN OTHERS THEN
1361 IF g_debug THEN
1362 hr_utility.set_location('Error in '||l_procedure,40);
1363 END IF;
1364 RAISE;
1365
1366 END deinitialize_code;
1367
1368 --
1369 -- Assign Global variables in this unnamed block. This is to avoid assigning global variables within procedures
1370 -- for each thread that executes the procedure.
1371 --
1372 Begin
1373 g_debug := hr_utility.debug_enabled ;
1374 g_package := 'pay_nz_sscwt_rate_archive.' ;
1375 g_start_dd_mm := '01-04-' ;
1376 g_legislation_code := 'NZ' ;
1377 g_report_short_name := 'PYNZSSRP' ;
1378 g_element_type_id := null ;
1379 g_input_value_id := null ;
1380 --
1381 END pay_nz_sscwt_rate_archive;