[Home] [Help]
PACKAGE BODY: APPS.PAY_JP_IWHT_ARCH_PKG
Source
1 PACKAGE BODY pay_jp_iwht_arch_pkg AS
2 -- $Header: pyjpiwar.pkb 120.2.12020000.3 2013/03/14 11:56:45 dduvvuri ship $
3 -- *************************************************************************
4 -- * Copyright (c) Oracle Corporation Japan,2009 Product Development.
5 -- * All rights reserved
6 -- *************************************************************************
7 -- *
8 -- * PROGRAM NAME
9 -- * PAYJLWL.pkb
10 -- *
11 -- * DESCRIPTION
12 -- * This script creates the package body of pay_jp_iwht_arch_pkg
13 -- *
14 -- * USAGE
15 -- * To install sqlplus <apps_user>/<apps_pwd> @payjpwlarchpkg.pkb
16 -- * To Execute sqlplus <apps_user>/<apps_pwd> EXEC payjpwlarchpkg.<procedure name>
17 -- *
18 -- * PROGRAM LIST
19 -- * ==========
20 -- * NAME DESCRIPTION
21 -- * ----------------- --------------------------------------------------
22 -- * RANGE_CODE
23 -- * INITIALIZATION_CODE
24 -- * ASSIGNMENT_ACTION_CODE
25 -- * ARCHIVE_CODE
26 -- *
27 -- * DEPENDENCIES
28 -- * None
29 -- *
30 -- * CALLED BY
31 -- * Concurrent Program
32 -- *
33 -- * LAST UPDATE DATE 05-Feb-2010
34 -- * Date the program has been modified for the last time
35 -- *
36 -- * HISTORY
37 -- * =======
38 -- *
39 -- * VERSION DATE AUTHOR(S) DESCRIPTION
40 -- * ------- ----------- -----------------------------------------------------------
41 -- * 120.0.12010000.1 05-Feb-2010 MPOTHALA Creation
42 -- * 120.1.12010000.2 08-Mar-2010 MPOTHALA update after unit testing
43 -- * 120.1.12010000.3 09-Mar-2010 MPOTHALA To fix bugs of the bug #9437454
44 -- * 120.1.12010000.4 26-Mar-2010 MPOTHALA To fix bugs of the bug #9525922,9509191
45 -- * 120.1.12010000.5 29-Mar-2010 MPOTHALA To fix bugs of the bug #9525922,9509191
46 -- * 120.1.12010000.6 31-Mar-2010 MPOTHALA To fix bugs of the bug #9569078
47 -- * 120.1.12010000.7 31-Mar-2010 MPOTHALA To fix bugs of the bug #9554515
48 -- * 120.1.12010000.8 13-May-2010 MPOTHALA Fixed assignment set issue
49 -- * 120.1.12010000.9 09-Mar-2013 DDUVVURI 2013 termination allowance changes for bug 16084826
50 -- * 120.1.12010000.10 14-Mar-2013 DDUVVURI modified for a qa raised issue in previous version
51 -- *********************************************************************************
52 --Declaration of constant global variables
53 --
54 gc_package CONSTANT VARCHAR2(60) := 'pay_jp_iwht_arch_pkg.';
55 gc_report_type CONSTANT VARCHAR2(60) := 'JP_IWHT_ARCH';
56 --
57 -- Global to store package name for tracing.
58 -- Declaration of global variables
59 gn_arc_payroll_action_id pay_payroll_actions.payroll_action_id%type;
60 gn_business_group_id hr_all_organization_units.organization_id%type;
61 gn_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE;
62 gb_debug BOOLEAN;
63 gd_end_date DATE;
64 gd_start_date DATE;
65 gd_effective_date DATE;
66 gd_ystart_date DATE;
67 gd_yend_date DATE;
68
69 --
70 PROCEDURE range_code ( p_payroll_action_id IN pay_payroll_actions.payroll_action_id%TYPE
71 ,p_sql OUT NOCOPY VARCHAR2
72 )
73 --***************************************************************************
74 -- PROCEDURE
75 -- RANGE_CODE
76 --
77 -- DESCRIPTION
78 -- This procedure returns a sql string to select a range
79 -- of assignments eligible for archival
80 --
81 -- ACCESS
82 -- PUBLIC
83 --
84 -- PARAMETERS
85 -- ==========
86 -- NAME TYPE DESCRIPTION
87 ------------------- -------- ---------------------------------------
88 -- p_payroll_action_id IN This parameter passes Payroll Action Id.
89 -- p_sql OUT This parameter retunrs SQL Query.
90 --
91 -- PREREQUISITES
92 -- None
93 --
94 -- CALLED BY
95 -- None
96 --*************************************************************************
97 IS
98
99 lc_procedure VARCHAR2(200);
100
101 BEGIN
102 --
103 gb_debug := hr_utility.debug_enabled;
104 --
105 IF gb_debug THEN
106 lc_procedure := gc_package||'RANGE_CODE';
107 hr_utility.set_location('Entering '||lc_procedure,1);
108 END IF ;
109 -------------------------------------------------------------------------
110 -- Archive the payroll action level data and EIT defintions.
111 -- sql string to SELECT a range of assignments eligible for archival.
112 -------------------------------------------------------------------------
113 p_sql := ' SELECT distinct p.person_id' ||
114 ' FROM per_people_f p,' ||
115 ' pay_payroll_actions pa' ||
116 ' WHERE pa.payroll_action_id = :payroll_action_id' ||
117 ' AND p.business_group_id = pa.business_group_id' ||
118 ' ORDER BY p.person_id';
119 --
120 IF gb_debug THEN
121 hr_utility.set_location('Leaving '||lc_procedure,1000);
122 END IF;
123 --
124 IF gb_debug THEN
125 hr_utility.set_location(lc_procedure,10);
126 END IF;
127 --
128 END range_code;
129 --
130 PROCEDURE initialize ( p_payroll_action_id in pay_payroll_actions.payroll_action_id%TYPE )
131 --*************************************************************************
132 -- PROCEDURE
133 -- initialize
134 --
135 -- DESCRIPTION
136 -- This procedure is used to set global contexts
137 --
138 -- ACCESS
139 -- PUBLIC
140 --
141 -- PARAMETERS
142 -- ==========
143 -- NAME TYPE DESCRIPTION
144 -- ----------------- -------- ---------------------------------------
145 -- p_payroll_action_id IN This parameter passes Payroll Action Id
146 --
147 -- PREREQUISITES
148 -- None
149 --
150 -- CALLED BY
151 -- INITIALIZATION_CODE
152 --*************************************************************************
153 IS
154 --
155 CURSOR lcr_params(p_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE)
156 --*************************************************************************
157 --
158 -- CURSOR lcr_params
159 --
160 -- DESCRIPTION
161 -- Fetches User Parameters from legislative_paramters column.
162 --
163 -- PARAMETERS
164 -- ==========
165 -- NAME TYPE DESCRIPTION
166 ------------------- -------- ---------------------------------------------
167 -- p_payroll_action_id IN This parameter passes the Payroll Action Id.
168 --
169 -- PREREQUISITES
170 -- None
171 --
172 -- CALLED BY
173 -- initialize procedure
174 --
175 --**********************************************************************
176 IS
177 SELECT business_group_id
178 ,effective_date
179 ,fnd_number.canonical_to_number(pay_core_utils.get_parameter('ITAX_ORGANIZATION_ID',legislative_parameters))
180 ,fnd_number.canonical_to_number(pay_core_utils.get_parameter('PAYROLL_ID',legislative_parameters))
181 ,fnd_date.canonical_to_date(pay_core_utils.get_parameter('TERMINATION_DATE_FROM', legislative_parameters))
182 ,fnd_date.canonical_to_date(pay_core_utils.get_parameter('TERMINATION_DATE_TO',legislative_parameters))
183 ,fnd_number.canonical_to_number(pay_core_utils.get_parameter('ASSIGNMENT_SET_ID',legislative_parameters))
184 ,pay_core_utils.get_parameter('REARCHIVE_FLAG',legislative_parameters)
185 FROM pay_payroll_actions PPA
186 WHERE PPA.payroll_action_id = p_payroll_action_id;
187 -- Local Variables
188 lc_procedure VARCHAR2(200);
189 i NUMBER := 0;
190 lc_legislative_parameters pay_payroll_actions.legislative_parameters%type;
191 --
192 BEGIN
193 --
194 gb_debug :=hr_utility.debug_enabled ;
195 lc_procedure := gc_package||'initialize';
196 --
197 IF gb_debug THEN
198 hr_utility.set_location('Entering '||lc_procedure,1);
199 END IF;
200 -------------------------------------------------------------------------
201 -- initialization_code to set the global tables for EIT
202 -- that will be used by each thread in multi-threading.
203 -------------------------------------------------------------------------
204 gn_arc_payroll_action_id := p_payroll_action_id;
205 -------------------------------------------------------------------------
206 -- Fetch the parameters passed by user into global variable.
207 -------------------------------------------------------------------------
208 OPEN lcr_params(p_payroll_action_id);
209 FETCH lcr_params
213 ,gr_parameters.payroll_id
210 INTO gr_parameters.business_group_id
211 ,gr_parameters.effective_date
212 ,gr_parameters.withholding_agent_id
214 ,gr_parameters.termination_date_from
215 ,gr_parameters.termination_date_to
216 ,gr_parameters.assignment_set_id
217 ,gr_parameters.rearchive_flag;
218 CLOSE lcr_params;
219 --
220 IF gb_debug THEN
221 hr_utility.set_location('p_payroll_action_id......... = ' || p_payroll_action_id,30);
222 hr_utility.set_location('gr_parameters.business_group_id.......= ' || gr_parameters.business_group_id,30);
223 hr_utility.set_location('gr_parameters.effective_date.......= ' || gr_parameters.effective_date,30);
224 hr_utility.set_location('gr_parameters.withholding_agent_id..........= ' ||gr_parameters.withholding_agent_id,30);
225 hr_utility.set_location('gr_parameters.payroll_id......= ' || gr_parameters.payroll_id,30);
226 hr_utility.set_location('gr_parameters.gr_parameters.termination_date_from...= ' || gr_parameters.termination_date_from ,30);
227 hr_utility.set_location('gr_parameters.termination_date_to.....= ' || gr_parameters.termination_date_to,30);
228 hr_utility.set_location('gr_parameters.assignment_set_id .....= ' || gr_parameters.assignment_set_id ,30);
229 hr_utility.set_location('gr_parameters.rearchive_flag.......= ' || gr_parameters.rearchive_flag,30);
230 END IF;
231 --
232 gd_ystart_date := TRUNC(gr_parameters.effective_date, 'YYYY');
233 gd_yend_date := ADD_MONTHS(gd_ystart_date, 12) - 1;
234 gn_business_group_id := gr_parameters.business_group_id ;
235 gn_payroll_action_id := p_payroll_action_id;
236 -------------------------------------------------------------------------
237 -- Fetch the Organization information into global type
238 -------------------------------------------------------------------------
239 --
240 IF gb_debug THEN
241 hr_utility.set_location('Leaving '||lc_procedure,1000);
242 END IF;
243 --
244 EXCEPTION
245 WHEN OTHERS THEN
246 hr_utility.set_location('Error in '||lc_procedure,999999);
247 RAISE;
248 END initialize;
249 --
250 PROCEDURE initialization_code ( p_payroll_action_id IN pay_payroll_actions.payroll_action_id%TYPE )
251 --***************************************************************************
252 -- PROCEDURE
253 -- INITIALIZATION_CODE
254 --
255 -- DESCRIPTION
256 -- This procedure is used to set global contexts
257 --
258 -- ACCESS
259 -- PUBLIC
260 --
261 -- PARAMETERS
262 --==========
263 -- NAME TYPE DESCRIPTION
264 ------------------- -------- ---------------------------------------
265 -- p_payroll_action_id IN This parameter passes Payroll Action Id
266 --
267 -- PREREQUISITES
268 -- None
269 --
270 -- CALLED BY
271 -- None
272 --***********************************************************************
273 IS
274 -- Local Variables
275 lc_procedure VARCHAR2(200);
276 --
277 BEGIN
278 --
279 gb_debug :=hr_utility.debug_enabled ;
280 --
281 IF gb_debug THEN
282 lc_procedure := gc_package||'INITIALIZATION_CODE';
283 hr_utility.set_location('Entering '||lc_procedure,1);
284 END IF;
285 -----------------------------------------------------------
286 -- initialization_code to set the global tables for EIT
287 -- that will be used by each thread in multi-threading.
288 -----------------------------------------------------------
289 gn_arc_payroll_action_id := p_payroll_action_id;
290 -----------------------------------------------------------
291 -- Fetch the parameters passed by user into global variable
292 -- initialize procedure
293 -----------------------------------------------------------
294 initialize(p_payroll_action_id);
295 --
296 IF gb_debug THEN
297 hr_utility.set_location('Leaving '||lc_procedure,1000);
298 END IF;
299 --
300 EXCEPTION
301 WHEN OTHERS THEN
302 hr_utility.set_location('Error in '||lc_procedure,999999);
303 RAISE;
304 END initialization_code;
305 --
306
307 FUNCTION proc_lookup_meaning( p_lookup_type IN hr_lookups.lookup_type%TYPE
308 ,p_lookup_code IN hr_lookups.lookup_code%TYPE)
309 RETURN VARCHAR2 IS
310 --***************************************************************************
311 -- PROCEDURE
312 -- DELETE_ASSACT
313 --
314 -- DESCRIPTION
315 -- This procedure is used to return meaning
316 --
317 -- ACCESS
318 -- PUBLIC
319 --
320 -- PARAMETERS
321 --==========
322 -- NAME TYPE DESCRIPTION
323 ------------------- -------- ---------------------------------------
324 -- p_lookup_type IN hr_lookups.lookup_type%TYPE
325 -- p_lookup_code IN hr_lookups.lookup_code%TYPE
326 --
327 -- PREREQUISITES
328 -- None
329 --
330 -- CALLED BY
331 -- None
332 --***********************************************************************
333 -- Local Variables
334 CURSOR lcu_lookup_meaning(p_lookup_type hr_lookups.lookup_type%TYPE
335 ,p_lookup_code hr_lookups.lookup_code%TYPE)
336 IS
337 SELECT meaning
338 FROM hr_lookups
339 WHERE lookup_type = p_lookup_type
340 AND lookup_code = p_lookup_code;
344 --
341 --
342 lc_procedure VARCHAR2(200);
343 lc_meaning hr_lookups.meaning%TYPE;
345 BEGIN
346 --
347 gb_debug :=hr_utility.debug_enabled ;
348 --
349 IF gb_debug THEN
350 lc_procedure := gc_package||'proc_lookup_meaning';
351 hr_utility.set_location('Entering '||lc_procedure,1);
352 END IF;
353 -----------------------------------------------------------
354 -- Fetch the parameters passed by user into global variable
355 -- initialize procedure
356 -----------------------------------------------------------
357 --
358 OPEN lcu_lookup_meaning(p_lookup_type
359 ,p_lookup_code
360 );
361 FETCH lcu_lookup_meaning INTO lc_meaning;
362 CLOSE lcu_lookup_meaning;
363 --
364 RETURN lc_meaning;
365 --
366 IF gb_debug THEN
367 hr_utility.set_location('Leaving '||lc_procedure,1000);
368 END IF;
369 --
370 EXCEPTION
371 WHEN OTHERS THEN
372 hr_utility.set_location('Error in '||lc_procedure,999999);
373 RAISE;
374 RETURN NULL;
375 END proc_lookup_meaning;
376 --
377 FUNCTION get_with_hold_agent(p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
378 ,p_effective_date IN DATE)
379 --************************************************************************
380 -- FUNCTION
381 -- pay_balance_result_value
382 --
383 -- DESCRIPTION
384 -- To Retrive Pay Run Result Values
385 --
386 -- ACCESS
387 -- PRIVATE
388 --
389 -- PREREQUISITES
390 -- None
391 --
392 -- CALLED BY
393 -- archive_code
394 --************************************************************************
395 RETURN NUMBER
396 IS
397 --
398 lc_procedure VARCHAR2(200);
399 ln_with_hold_agent NUMBER;
400 --
401 BEGIN
402 --
403 gb_debug := hr_utility.debug_enabled;
404 --
405 IF gb_debug THEN
406 lc_procedure := gc_package||'get_with_holding_id';
407 hr_utility.set_location('Entering '||lc_procedure,1);
408 END IF;
409 --
410 ln_with_hold_agent := pay_jp_balance_pkg.get_entry_value_char(p_element_name => 'COM_ITX_INFO'
411 ,p_input_value_name => 'WITHHOLD_AGENT'
412 ,p_assignment_id => p_assignment_id
413 ,p_effective_date => p_effective_date -- Bug 9044516
414 );
415
416 --
417 IF gb_debug THEN
418 hr_utility.set_location('Leaving '||lc_procedure,1000);
419 END IF;
420 --
421 RETURN ln_with_hold_agent;
422 --
423 EXCEPTION
424 WHEN NO_DATA_FOUND THEN
425 IF gb_debug THEN
426 hr_utility.set_location('No Data Found Exception in ln_with_hold_agent',10);
427 END IF;
428 RETURN NULL;
429 --
430 WHEN OTHERS THEN
431 hr_utility.set_location('Error in '||lc_procedure,999999);
432 RAISE;
433 RETURN NULL;
434 END get_with_hold_agent;
435 --
436 PROCEDURE delete_assact ( p_assignment_id IN per_all_assignments_f.assignment_id%TYPE)
437 --***************************************************************************
438 -- PROCEDURE
439 -- DELETE_ASSACT
440 --
441 -- DESCRIPTION
442 -- This procedure is used to set global contexts
443 --
444 -- ACCESS
445 -- PUBLIC
446 --
447 -- PARAMETERS
448 --==========
449 -- NAME TYPE DESCRIPTION
450 ------------------- -------- ---------------------------------------
451 -- p_payroll_action_id IN This parameter passes Payroll Action Id
452 -- p_assignment_id IN This parameter passes Assignment Id
453 --
454 -- PREREQUISITES
455 -- None
456 --
457 -- CALLED BY
458 -- None
459 --***********************************************************************
460 IS
461 -- Local Variables
462 CURSOR lcu_action_information_id(p_assignment_id per_all_assignments_f.assignment_id%TYPE)
463 IS
464 SELECT PAI.object_version_number
465 ,PAI.action_information_id
466 ,PAC.assignment_action_id
467 FROM pay_action_information PAI
468 ,pay_assignment_actions PAC
469 ,pay_payroll_actions PPA
470 WHERE PAI.action_context_id = PAC.assignment_action_id
471 AND PAC.assignment_id = p_assignment_id
472 AND PAC.payroll_action_id = PPA.payroll_action_id
473 AND PAI.action_context_type = 'AAP'
474 AND PPA.report_type = gc_report_type;
475 --
476 lc_procedure VARCHAR2(200);
477 --
478 BEGIN
479 --
480 gb_debug :=hr_utility.debug_enabled ;
481 --
482 IF gb_debug THEN
483 lc_procedure := gc_package||'delete_assact';
484 hr_utility.set_location('Entering '||lc_procedure,1);
485 END IF;
486 -----------------------------------------------------------
487 -- Fetch the parameters passed by user into global variable
488 -- initialize procedure
489 -----------------------------------------------------------
490 --
494 ( p_validate => FALSE
491 FOR lr_emp_assignment_det in lcu_action_information_id(p_assignment_id)
492 LOOP
493 pay_action_information_api.delete_action_information
495 ,p_action_information_id => lr_emp_assignment_det.action_information_id
496 ,p_object_version_number => lr_emp_assignment_det.object_version_number);
497 END LOOP;
498 --
499 IF gb_debug THEN
500 hr_utility.set_location('Leaving '||lc_procedure,1000);
501 END IF;
502 --
503 EXCEPTION
504 WHEN OTHERS THEN
505 hr_utility.set_location('Error in '||lc_procedure,999999);
506 RAISE;
507 END delete_assact;
508 --
509 FUNCTION range_person_on
510 --************************************************************************
511 -- FUNCTION
512 -- range_person_on
513 --
514 -- DESCRIPTION
515 -- Checks if RANGE_PERSON_ID is enabled for
516 -- Archive process.
517 --
518 -- ACCESS
519 -- PRIVATE
520 --
521 -- PREREQUISITES
522 -- None
523 --
524 -- CALLED BY
525 -- assignment_action_code
526 --************************************************************************
527 RETURN BOOLEAN
528 IS
529 --
530 CURSOR lcu_action_parameter
531 IS
532 SELECT parameter_value
533 FROM pay_action_parameters
534 WHERE parameter_name = 'RANGE_PERSON_ID';
535 --
536 lb_return BOOLEAN;
537 lc_action_param_val VARCHAR2(30);
538 --
539 BEGIN
540 --
541 gb_debug := hr_utility.debug_enabled;
542 --
543 IF gb_debug THEN
544 hr_utility.set_location('Entering range_person_on',10);
545 END IF;
546 --
547 OPEN lcu_action_parameter;
548 FETCH lcu_action_parameter INTO lc_action_param_val;
549 CLOSE lcu_action_parameter;
550 --
551 IF lc_action_param_val = 'Y' THEN
552 lb_return := TRUE;
553 IF gb_debug THEN
554 hr_utility.set_location('Range Person = True',10);
555 END IF;
556 ELSE
557 lb_return := FALSE;
558 END IF;
559 --
560 IF gb_debug THEN
561 hr_utility.set_location('Leaving range_person_on',10);
562 END IF;
563 RETURN lb_return;
564 --
565 EXCEPTION WHEN NO_DATA_FOUND THEN
566 IF gb_debug THEN
567 hr_utility.set_location('No Data Found Exception in range_person_on',10);
568 END IF;
569 lb_return := FALSE;
570 RETURN lb_return;
571 END range_person_on;
572 --
573 --
574 PROCEDURE assignment_action_code( p_payroll_action_id IN pay_payroll_actions.payroll_action_id%type
575 ,p_start_person IN per_all_people_f.person_id%type
576 ,p_end_person IN per_all_people_f.person_id%type
577 ,p_chunk IN NUMBER
578 )
579 --************************************************************************
580 -- PROCEDURE
581 -- ASSIGNMENT_ACTION_CODE
582 --
583 -- DESCRIPTION
584 -- This procedure further restricts the assignment_id's returned by range_code
585 -- This procedure gets the parameters given by user and restricts
586 -- the assignments to be archived
587 -- it then calls hr_nonrun.insact to create an assignment action id
588 -- it then archives Payroll Run assignment action id details
589 -- in pay_Action_information with context 'AU_ARCHIVE_ASG_DETAILS'
590 -- for each assignment.
591 -- There are different cursors for choosing the assignment ids.
592 -- Depending on the parameters passed,the appropriate cursor is used.
593 --
594 -- ACCESS
595 -- PUBLIC
596 --
597 -- PARAMETERS
598 -- ==========
599 -- NAME TYPE DESCRIPTION
600 -- ----------------- -------- ---------------------------------------
601 -- p_payroll_action_id IN This parameter passes Payroll Action Id
602 -- p_start_person IN This parameter passes Start Person Id
603 -- p_end_person IN This parameter passes End Person Id
604 -- p_chunk OUT This parameter passes Chunk Number
605 --
606 -- PREREQUISITES
607 -- None
608 --
609 -- CALLED BY
610 -- PYUGEN process
611 --***********************************************************************/
612 IS
613 --
614 CURSOR lcu_emp_assignment_det_r(p_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE
615 ,p_business_group_id per_assignments_f.business_group_id%TYPE
616 ,p_effective_date DATE
617 ,p_payroll_id pay_payrolls_f.payroll_id%TYPE
618 ,p_with_hold_id hr_all_organization_units.organization_id%TYPE
619 ,p_termination_date_from DATE
620 ,p_termination_date_to DATE
621 )
622 IS
623 SELECT PAF.assignment_id
624 ,PPS.actual_termination_date
625 FROM per_assignments_f PAF
626 ,per_people_f PPF
627 ,per_periods_of_service PPS
628 ,pay_population_ranges PPR
629 ,pay_payroll_actions PPA
630 WHERE PAF.person_id = PPF.person_id
631 AND PPF.person_id = PPS.person_id
632 AND PPA.payroll_action_id = p_payroll_action_id
633 AND PPA.payroll_action_id = PPR.payroll_action_id
634 AND PPR.chunk_number = p_chunk
635 AND PPR.person_id = PPF.person_id
636 AND PAF.business_group_id = p_business_group_id
637 AND PPS.period_of_service_id = NVL(PAF.period_of_service_id,PPS.period_of_service_id)
638 AND NVL(PAF.payroll_id,-999) = NVL(p_payroll_id,NVL(PAF.payroll_id,-999))
639 AND NVL(get_with_hold_agent(PAF.assignment_id,NVL(PPS.actual_termination_date,p_effective_date)),-999) = NVL(p_with_hold_id,NVL(get_with_hold_agent(PAF.assignment_id,NVL(PPS.actual_termination_date,p_effective_date)),-999))
643 AND TRUNC(NVL(PPS.actual_termination_date,p_effective_date)) BETWEEN PPF.effective_start_date AND PPF.effective_end_date
640 AND ( TRUNC(PPS.actual_termination_date) BETWEEN TRUNC(NVL(p_termination_date_from,PPS.actual_termination_date)) AND TRUNC(NVL(p_termination_date_to,PPS.actual_termination_date))
641 OR
642 (p_termination_date_from IS NULL AND p_termination_date_to IS NULL)) -- #Bug 9527179
644 AND TRUNC(NVL(PPS.actual_termination_date,p_effective_date)) BETWEEN PAF.effective_start_date AND PAF.effective_end_date
645 AND EXISTS(SELECT 1
646 FROM pay_jp_pre_tax PPT,
647 pay_assignment_actions PAA,
648 pay_payroll_actions PPA
649 WHERE PPA.effective_date BETWEEN gd_ystart_date AND gd_yend_date
650 AND PAA.assignment_id = PAF.assignment_id
651 AND PPA.business_group_id + 0 = p_business_group_id
652 AND PPA.action_type in ('R', 'Q', 'B', 'I')
653 AND PAA.payroll_action_id = PPA.payroll_action_id
654 AND PAA.action_status = 'C'
655 AND PPT.assignment_action_id = PAA.assignment_action_id
656 AND PPT.action_status = 'C'
657 AND PPT.salary_category = 'TERM'
658 AND NVL(PPT.itax_organization_id,-999) = NVL(NVL(p_with_hold_id,PPT.itax_organization_id),-999)
659 AND NOT EXISTS(
660 SELECT null
661 FROM pay_payroll_actions PPAV,
662 pay_assignment_actions PAAV,
663 pay_action_interlocks PAI
664 WHERE PAI.locked_action_id = PAA.assignment_action_id
665 AND PAAV.assignment_action_id = PAI.locking_action_id
666 AND PPAV.payroll_action_id = PAAV.payroll_action_id
667 AND PPAV.action_type = 'V'))
668 ORDER BY PAF.assignment_id;
669 --
670 CURSOR lcu_emp_assignment_det ( p_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE
671 ,p_start_person_id per_all_people_f.person_id%TYPE
672 ,p_end_person_id per_all_people_f.person_id%TYPE
673 ,p_business_group_id per_assignments_f.business_group_id%TYPE
674 ,p_effective_date DATE
675 ,p_payroll_id pay_payrolls_f.payroll_id%TYPE
676 ,p_with_hold_id hr_all_organization_units.organization_id%TYPE
677 ,p_termination_date_from DATE
678 ,p_termination_date_to DATE
679 )
680 IS
681 SELECT PAF.assignment_id
682 ,PPS.actual_termination_date
683 FROM per_assignments_f PAF
684 ,per_people_f PPF
685 ,per_periods_of_service PPS
686 WHERE PAF.person_id = PPF.person_id
687 AND PPF.person_id = PPS.person_id
688 AND PAF.business_group_id = p_business_group_id
689 AND PPS.period_of_service_id = NVL(PAF.period_of_service_id,PPS.period_of_service_id)
690 AND PPF.person_id BETWEEN p_start_person_id AND p_end_person_id
691 AND NVL(PAF.payroll_id,-999) = NVL(p_payroll_id,NVL(PAF.payroll_id,-999))
692 AND NVL(get_with_hold_agent(PAF.assignment_id,NVL(PPS.actual_termination_date,p_effective_date)),-999) = NVL(p_with_hold_id,NVL(get_with_hold_agent(PAF.assignment_id,NVL(PPS.actual_termination_date,p_effective_date)),-999))
693 AND ( TRUNC(PPS.actual_termination_date) BETWEEN TRUNC(NVL(p_termination_date_from,PPS.actual_termination_date)) AND TRUNC(NVL(p_termination_date_to,PPS.actual_termination_date))
694 OR
695 (p_termination_date_from IS NULL AND p_termination_date_to IS NULL)) -- #Bug 9527179
696 AND TRUNC(NVL(PPS.actual_termination_date,p_effective_date)) BETWEEN PPF.effective_start_date AND PPF.effective_end_date
697 AND TRUNC(NVL(PPS.actual_termination_date,p_effective_date)) BETWEEN PAF.effective_start_date AND PAF.effective_end_date
698 AND EXISTS(SELECT 1
699 FROM pay_jp_pre_tax PPT,
700 pay_assignment_actions PAA,
701 pay_payroll_actions PPA
702 WHERE PPA.effective_date BETWEEN gd_ystart_date AND gd_yend_date
703 AND PAA.assignment_id = PAF.assignment_id
704 AND PPA.business_group_id + 0 = p_business_group_id
705 AND PPA.action_type in ('R', 'Q', 'B', 'I')
706 AND PAA.payroll_action_id = PPA.payroll_action_id
707 AND PAA.action_status = 'C'
708 AND PPT.assignment_action_id = PAA.assignment_action_id
709 AND PPT.action_status = 'C'
710 AND PPT.salary_category = 'TERM'
711 AND NVL(PPT.itax_organization_id,-999) = NVL(NVL(p_with_hold_id,PPT.itax_organization_id),-999)
712 AND NOT EXISTS(
713 SELECT null
714 FROM pay_payroll_actions PPAV,
715 pay_assignment_actions PAAV,
716 pay_action_interlocks PAI
717 WHERE PAI.locked_action_id = PAA.assignment_action_id
718 AND PAAV.assignment_action_id = PAI.locking_action_id
719 AND PPAV.payroll_action_id = PAAV.payroll_action_id
720 AND PPAV.action_type = 'V'))
721 ORDER BY PAF.assignment_id;
722 --
723 CURSOR lcu_next_action_id
724 IS
725 SELECT pay_assignment_actions_s.NEXTVAL
726 FROM dual;
727 --
728 -- Local Variables
729 lc_procedure VARCHAR2(200);
730 lc_subject_yyyymm VARCHAR2(240);
731 lc_archive_exists VARCHAR2(1) := 'N';
732 lc_previous_month VARCHAR2(10);
733 lc_include_flag VARCHAR2(1) := 'N';
734 --
735 ln_action_info_id pay_action_information.action_information_id%TYPE;
736 ln_obj_version_num pay_action_information.object_version_number%TYPE;
737 ln_master_pact_id NUMBER;
738 ln_next_assignment_action_id NUMBER;
739 ln_org_pact_id NUMBER;
740 --
741 ld_termination_date_from DATE;
742 ld_termination_date_to DATE;
743 --
744 BEGIN
745 --
746 gb_debug := hr_utility.debug_enabled ;
747 --
748 IF gb_debug THEN
749 lc_procedure := gc_package||'assignment_action_code';
750 hr_utility.set_location('Entering ' || lc_procedure,1);
751 hr_utility.set_location('Person Range '||p_start_person||' - '||p_end_person,1);
752 END IF;
753 --
754 -- initialization_code to set the global tables for EIT
755 -- that will be used by each thread in multi-threading.
756 --
757 initialize(p_payroll_action_id);
758 --
759 gn_business_group_id := gr_parameters.business_group_id ;
760 gn_payroll_action_id := p_payroll_action_id;
761 --
762 IF range_person_on THEN
763 --
764 IF gb_debug THEN
765 hr_utility.set_location('before range person1 on loop',20);
766 END IF;
767 --
768 FOR lr_emp_assignment_det_r in lcu_emp_assignment_det_r(p_payroll_action_id
769 ,gr_parameters.business_group_id
770 ,gr_parameters.effective_date
771 ,gr_parameters.payroll_id
772 ,gr_parameters.withholding_agent_id
773 ,gr_parameters.termination_date_from
774 ,gr_parameters.termination_date_to
775 )
776 LOOP
777 --
778 OPEN lcu_next_action_id;
779 FETCH lcu_next_action_id INTO ln_next_assignment_action_id;
780 CLOSE lcu_next_action_id;
781 --
782 IF gb_debug THEN
783 hr_utility.set_location('p_payroll_action_id.........= '||p_payroll_action_id,20);
784 hr_utility.set_location('l_next_assignment_action_id..= '||ln_next_assignment_action_id,20);
785 hr_utility.set_location('lr_emp_assignment_det_r.assignment_id...= '||lr_emp_assignment_det_r.assignment_id,20);
786 END IF;
787 --
788 -- Create the archive assignment actions
789 --
790 IF NVL(gr_parameters.assignment_set_id,0) = 0 THEN
791
792 -- Create the archive assignment actions
793 hr_nonrun_asact.insact(ln_next_assignment_action_id
794 ,lr_emp_assignment_det_r.assignment_id
795 ,p_payroll_action_id
796 ,p_chunk
797 );
798
799 ELSE
800 lc_include_flag := hr_jp_ast_utility_pkg.assignment_set_validate(p_assignment_set_id => gr_parameters.assignment_set_id
801 ,p_assignment_id => lr_emp_assignment_det_r.assignment_id
802 ,p_effective_date => NVL(lr_emp_assignment_det_r.actual_termination_date,gr_parameters.effective_date) -- #Bug No 9508028
803 ,p_populate_fs_flag => 'Y' -- #Bug No 9508028
804 );
805
806 IF gb_debug THEN
807 hr_utility.set_location('lc_include_flag after check.= '||lc_include_flag ,20);
808 END IF;
809 --
810 IF lc_include_flag = 'Y' THEN
811 --
812 OPEN lcu_next_action_id;
813 FETCH lcu_next_action_id INTO ln_next_assignment_action_id;
814 CLOSE lcu_next_action_id;
815 --
816
817 -- Create the archive assignment actions
818 hr_nonrun_asact.insact(ln_next_assignment_action_id
819 ,lr_emp_assignment_det_r.assignment_id
820 ,p_payroll_action_id
821 ,p_chunk
822 );
823
824 --
825 END IF;
826 END IF;
827 --
828 END LOOP;
829 --
830 ELSE
831 --
832 IF gb_debug THEN
833 hr_utility.set_location('range_person_on_loop2',302);
834 END IF;
835 --
836 FOR lr_emp_assignment_det in lcu_emp_assignment_det(p_payroll_action_id
837 ,p_start_person
838 ,p_end_person
839 ,gr_parameters.business_group_id
840 ,gr_parameters.effective_date
841 ,gr_parameters.payroll_id
842 ,gr_parameters.withholding_agent_id
843 ,gr_parameters.termination_date_from
844 ,gr_parameters.termination_date_to
845 )
846 LOOP
847 --
848 OPEN lcu_next_action_id;
849 FETCH lcu_next_action_id INTO ln_next_assignment_action_id;
850 CLOSE lcu_next_action_id;
851 --
852 IF gb_debug THEN
853 hr_utility.set_location('p_payroll_action_id.........= '||p_payroll_action_id,20);
854 hr_utility.set_location('l_next_assignment_action_id.= '||ln_next_assignment_action_id,20);
855 hr_utility.set_location('lr_emp_assignment_det.assignment_id.......= '||lr_emp_assignment_det.assignment_id,20);
856 END IF;
857 --
858 -- Create the archive assignment actions
859 --
860 IF NVL(gr_parameters.assignment_set_id,0) = 0 THEN
861
862 -- Create the archive assignment actions
863
864 hr_nonrun_asact.insact(ln_next_assignment_action_id
865 ,lr_emp_assignment_det.assignment_id
866 ,p_payroll_action_id
867 ,p_chunk
868 );
869 --
870 ELSE
871 lc_include_flag := hr_jp_ast_utility_pkg.assignment_set_validate(p_assignment_set_id => gr_parameters.assignment_set_id
872 ,p_assignment_id => lr_emp_assignment_det.assignment_id
873 ,p_effective_date => NVL(lr_emp_assignment_det.actual_termination_date,gr_parameters.effective_date) -- #Bug No 9508028
874 ,p_populate_fs_flag => 'Y' -- #Bug No 9508028
875 );
876 IF gb_debug THEN
877 hr_utility.set_location('lc_include_flag after check.= '||lc_include_flag ,20);
878 END IF;
879 --
880 IF lc_include_flag = 'Y' THEN
881
882 --
883 OPEN lcu_next_action_id;
884 FETCH lcu_next_action_id INTO ln_next_assignment_action_id;
885 CLOSE lcu_next_action_id;
886
887 -- Create the archive assignment actions
888 hr_nonrun_asact.insact(ln_next_assignment_action_id
889 ,lr_emp_assignment_det.assignment_id
890 ,p_payroll_action_id
891 ,p_chunk
892 );
893 --
894 END IF;
895 END IF;
896 --
897 lc_include_flag := NULL; -- #Bug No 9508028
898
899 --
900 END LOOP;
901 --
902 END IF;
903 --
904 EXCEPTION
905 WHEN OTHERS THEN
906 hr_utility.set_location('Error in '||lc_procedure,999999);
907 RAISE;
908 END assignment_action_code;
909 --
910 PROCEDURE archive_code ( p_assignment_action_id IN pay_assignment_actions.assignment_action_id%type
911 , p_effective_date IN pay_payroll_actions.effective_date%type
912 )
913 --************************************************************************
914 -- PROCEDURE
915 -- ARCHIVE_CODE
916 --
917 -- DESCRIPTION
918 -- If employee details not previously archived,proc archives employee
919 -- details in pay_Action_information with context 'JP_EMPOYEE_DETAILS'
920 --
921 -- ACCESS
922 -- PUBLIC
923 --
924 -- PARAMETERS
925 -- ==========
926 -- NAME TYPE DESCRIPTION
927 -- ----------------- -------- ---------------------------------------
928 -- p_assignment_action_id IN This parameter passes Assignment Action Id
929 -- p_effective_date IN This parameter passes Effective Date
930 --
931 -- PREREQUISITES
932 -- None
933 --
934 -- CALLED BY
935 -- None
936 --************************************************************************/
937 IS
938 --
939 CURSOR lcu_get_assignment_id ( p_assignment_action_id pay_assignment_actions.assignment_action_id%type )
940 IS
941 SELECT assignment_id
942 FROM pay_assignment_actions
943 WHERE assignment_action_id = p_assignment_action_id;
944 --
945 CURSOR lcu_employee_details ( p_assignment_id per_all_assignments_f.assignment_id%TYPE
946 , p_effective_date DATE
947 )
948 IS
949 SELECT PPF.person_id person_id
950 ,PPF.employee_number employee_number
951 ,PPF.first_name first_name_kana
952 ,PPF.last_name last_name_kana
953 ,PPF.per_information19 first_name_kanji
954 ,PPF.per_information18 last_name_kanji
955 ,PPS.date_start hire_date
956 ,PPS.actual_termination_date termination_date
957 ,PAF.assignment_id assignment_id
958 ,PAF.payroll_id payroll_id
959 ,get_with_hold_agent(p_assignment_id,NVL(PPS.actual_termination_date,p_effective_date)) withhold_agent_id
960 ,DECODE(PADR.address_id,NULL,PADC.town_or_city,PADR.town_or_city) district_code
961 ,DECODE(PADR.address_id,NULL,PADC.address_line1 ,PADR.address_line1) address_line1
962 ,DECODE(PADR.address_id,NULL,PADC.address_line2 ,PADR.address_line2) address_line2
963 ,DECODE(PADR.address_id,NULL,PADC.address_line3 ,PADR.address_line3) address_line3
964 FROM per_people_f PPF
965 , per_assignments_f PAF
966 , per_periods_of_service PPS
967 , per_addresses PADR
968 , per_addresses PADC
969 WHERE PAF.person_id = PPF.person_id
970 AND PPS.person_id = PPF.person_id
971 AND PAF.assignment_id = p_assignment_id
972 AND PADR.person_id(+) = PPF.person_id
973 AND PADR.address_type(+) = 'JP_R'
974 AND TRUNC(p_effective_date) BETWEEN TRUNC(NVL(PADR.date_from(+),p_effective_date)) AND NVL(PADR.date_to(+),TO_DATE('31/12/4712','DD/MM/YYYY')) --bug #9554515
975 AND PADC.person_id(+) = PPF.person_id
976 AND PADC.address_type(+) = 'JP_C'
977 AND TRUNC(p_effective_date) BETWEEN TRUNC(NVL(PADC.date_from(+),p_effective_date)) AND NVL(PADC.date_to(+),TO_DATE('31/12/4712','DD/MM/YYYY')) --bug #9554515
978 AND PPS.period_of_service_id = NVL(PAF.period_of_service_id,PPS.period_of_service_id) -- #Bug 9569078
979 AND EXISTS(SELECT 1
980 FROM pay_jp_pre_tax PPT,
981 pay_assignment_actions PAA,
982 pay_payroll_actions PPA
983 WHERE PPA.effective_date BETWEEN gd_ystart_date AND gd_yend_date
984 AND PAA.assignment_id = PAF.assignment_id
985 AND PPA.action_type in ('R', 'Q', 'B', 'I')
986 AND PAA.payroll_action_id = PPA.payroll_action_id
987 AND PAA.action_status = 'C'
988 AND PPT.assignment_action_id = PAA.assignment_action_id
989 AND PPT.action_status = 'C'
990 AND PPT.salary_category = 'TERM'
991 AND TRUNC(NVL(PPS.actual_termination_date,PPA.effective_date)) BETWEEN PPF.effective_start_date AND PPF.effective_end_date
992 AND TRUNC(NVL(PPS.actual_termination_date,PPA.effective_date)) BETWEEN PAF.effective_start_date AND PAF.effective_end_date
993 AND NOT EXISTS(
994 SELECT null
995 FROM pay_payroll_actions PPAV,
996 pay_assignment_actions PAAV,
997 pay_action_interlocks PAI
998 WHERE PAI.locked_action_id = PAA.assignment_action_id
999 AND PAAV.assignment_action_id = PAI.locking_action_id
1000 AND PPAV.payroll_action_id = PAAV.payroll_action_id
1001 AND PPAV.action_type = 'V'))
1002 ORDER BY PAF.assignment_id,PPF.effective_start_date;
1003 --
1004 CURSOR lcu_swot_details(p_itax_organization_id NUMBER)
1005 IS
1006 SELECT HOI.org_information1
1007 ,HOI.org_information6
1008 ,HOI.org_information7
1009 ,HOI.org_information8
1010 ,HOI.org_information12
1011 FROM hr_all_organization_units HOU,
1012 hr_organization_information HOI
1013 WHERE HOU.organization_id = p_itax_organization_id
1014 AND HOI.organization_id(+) = hou.organization_id
1015 AND HOI.org_information_context(+) = 'JP_TAX_SWOT_INFO';
1016 --
1017 CURSOR lcu_address_details(p_person_id NUMBER
1018 ,p_effective_date DATE)
1019 IS
1020 SELECT PAD.town_or_city jan_1st_district_code
1021 ,PAD.address_line1 jan_1st_address_line1
1022 ,PAD.address_line2 jan_1st_address_line2
1023 ,PAD.address_line3 jan_1st_address_line3
1024 FROM per_addresses PAD
1025 WHERE PAD.person_id = p_person_id
1026 AND ((PAD.address_type = 'JP_R')
1027 OR
1028 (PAD.address_type = 'JP_C'))
1029 AND p_effective_date BETWEEN NVL(PAD.date_from,p_effective_date) AND NVL(PAD.date_to,TO_DATE('31/12/4712','DD/MM/YYYY'));
1030 --
1031 CURSOR lcu_tax_details(p_assignment_id NUMBER
1032 ,p_business_group_id NUMBER)
1033 IS
1034 SELECT NVL(sum(ppt.taxable_sal_amt + ppt.taxable_mat_amt), 0) termination_money
1035 ,NVL(sum(ppt.itax), 0) withholding_tax
1036 ,NVL(sum(ppt.sp_ltax_shi), 0) muncipal_tax -- Bug 9525922
1037 ,NVL(sum(ppt.sp_ltax_to), 0) prefectural_tax -- Bug 9525922
1038 FROM pay_jp_pre_tax PPT,
1039 pay_assignment_actions PAA,
1040 pay_payroll_actions PPA
1041 WHERE PPA.effective_date BETWEEN gd_ystart_date AND gd_yend_date
1042 AND PPA.business_group_id + 0 = p_business_group_id
1043 AND PAA.assignment_id = p_assignment_id
1044 AND PPA.action_type in ('R', 'Q', 'B', 'I')
1045 AND PAA.payroll_action_id = PPA.payroll_action_id
1046 AND PAA.action_status = 'C'
1047 AND PPT.assignment_action_id = PAA.assignment_action_id
1048 AND PPT.action_status = 'C'
1049 AND PPT.salary_category = 'TERM'
1050 AND NOT EXISTS(
1051 SELECT null
1052 FROM pay_payroll_actions PPAV,
1053 pay_assignment_actions PAAV,
1054 pay_action_interlocks PAI
1055 WHERE PAI.locked_action_id = PAA.assignment_action_id
1056 AND PAAV.assignment_action_id = PAI.locking_action_id
1057 AND PPAV.payroll_action_id = PAAV.payroll_action_id
1058 AND PPAV.action_type = 'V');
1059 --
1060 CURSOR lcu_assact_details(p_assignment_id NUMBER
1061 ,p_business_group_id NUMBER)
1062 IS
1063 SELECT PAA.assignment_action_id
1064 ,PPA.effective_date
1065 ,PPA.date_earned
1066 FROM pay_jp_pre_tax PPT,
1067 pay_assignment_actions PAA,
1068 pay_payroll_actions PPA
1069 WHERE PPA.effective_date BETWEEN gd_ystart_date AND gd_yend_date
1070 AND PPA.business_group_id + 0 = p_business_group_id
1071 AND PAA.assignment_id = p_assignment_id
1072 AND PPA.action_type in ('R', 'Q', 'B', 'I')
1073 AND PAA.payroll_action_id = PPA.payroll_action_id
1074 AND PAA.action_status = 'C'
1078 AND NOT EXISTS(
1075 AND PPT.assignment_action_id = PAA.assignment_action_id
1076 AND PPT.action_status = 'C'
1077 AND PPT.salary_category = 'TERM'
1079 SELECT null
1080 FROM pay_payroll_actions PPAV,
1081 pay_assignment_actions PAAV,
1082 pay_action_interlocks PAI
1083 WHERE PAI.locked_action_id = PAA.assignment_action_id
1084 AND PAAV.assignment_action_id = PAI.locking_action_id
1085 AND PPAV.payroll_action_id = PAAV.payroll_action_id
1086 AND PPAV.action_type = 'V');
1087 --
1088 CURSOR lcu_get_bal_id
1089 IS
1090 SELECT PDB.defined_balance_id
1091 FROM pay_balance_types PBT
1092 ,pay_balance_dimensions PBD
1093 ,pay_defined_balances PDB
1094 WHERE PBT.balance_name = 'B_TRM_INCOME_EXM'
1095 AND PBD.database_item_suffix = '_ASG_RUN'
1096 AND PBT.balance_type_id = PDB.balance_type_id
1097 AND PBD.balance_dimension_id = PDB.balance_dimension_id;
1098 --
1099 CURSOR get_bal_id_spc
1100 IS
1101 SELECT PDB.defined_balance_id
1102 FROM pay_balance_types PBT
1103 ,pay_balance_dimensions PBD
1104 ,pay_defined_balances PDB
1105 WHERE PBT.balance_name = 'B_TRM_INC_DEDN_SPC_EXC'
1106 AND PBD.database_item_suffix = '_ASG_RUN'
1107 AND PBT.balance_type_id = PDB.balance_type_id
1108 AND PBD.balance_dimension_id = PDB.balance_dimension_id;
1109 --
1110 CURSOR get_date_string(p_start_date DATE,p_end_date DATE)
1111 IS
1112 SELECT TO_CHAR(p_start_date, 'EYY.MM.DD', 'NLS_CALENDAR=''Japanese Imperial''')
1113 ||' - '||
1114 TO_CHAR(p_end_date, 'EYY.MM.DD', 'NLS_CALENDAR=''Japanese Imperial''')
1115 FROM dual;
1116 --
1117 CURSOR lcu_prev_archive (p_assignment_id per_all_assignments_f.assignment_id%TYPE)
1118 IS
1119 SELECT 'Y'
1120 FROM pay_action_information PAI
1121 ,pay_assignment_actions PAC
1122 ,pay_payroll_actions PPA
1123 WHERE PAI.action_context_id = PAC.assignment_action_id
1124 AND PAC.assignment_id = p_assignment_id
1125 AND PAC.payroll_action_id = PPA.payroll_action_id
1126 AND PAI.action_context_type = 'AAP'
1127 AND PPA.report_type = gc_report_type;
1128 --
1129 lc_procedure VARCHAR2(200);
1130 lc_swot_address_line1 VARCHAR2(150);
1131 lc_swot_address_line2 VARCHAR2(150);
1132 lc_swot_address_line3 VARCHAR2(150);
1133 lc_swot_phone_number VARCHAR2(150);
1134 lc_swot_employer VARCHAR2(150);
1135 lc_description1 pay_action_information.action_information23%TYPE;
1136 lc_description2 pay_action_information.action_information24%TYPE;
1137 lc_descfield pay_action_information.action_information23%TYPE;
1138 lc_descfield2 pay_action_information.action_information23%TYPE;
1139 lc_descfield3 pay_action_information.action_information23%TYPE;
1140 lc_descfield4 pay_action_information.action_information24%TYPE;
1141 lc_descfield5 pay_action_information.action_information24%TYPE;
1142 lc_1st_jan_district_code per_addresses.town_or_city%TYPE;
1143 lc_1st_jan_address_line1 per_addresses.address_line1%TYPE;
1144 lc_1st_jan_address_line2 per_addresses.address_line2%TYPE;
1145 lc_1st_jan_address_line3 per_addresses.address_line3%TYPE;
1146 lc_note_submit_flag VARCHAR2(10);
1147 lc_archive VARCHAR2(10) DEFAULT 'N';
1148 lc_check_flag VARCHAR2(10) DEFAULT 'N';
1149 l_allow_str VARCHAR2(60);
1150 l_yos_str VARCHAR2(60);
1151 l_ovr_yos_str VARCHAR2(60);
1152 lc_description_temp pay_action_information.action_information23%TYPE;
1153
1154 --
1155 ln_action_info_id pay_action_information.action_information_id%TYPE;
1156 ln_obj_version_num pay_action_information.object_version_number%TYPE;
1157 ln_tax_action_info_id pay_action_information.action_information_id%TYPE;
1158 ln_tax_obj_version_num pay_action_information.object_version_number%TYPE;
1159 ln_assignment_id per_all_assignments_f.assignment_id%TYPE;
1160 ln_service_years NUMBER;
1161 ln_termination_money NUMBER;
1162 ln_withholidng_tax NUMBER;
1163 ln_muncipal_tax NUMBER;
1164 ln_prefectural_tax NUMBER;
1165 ln_def_balance_id pay_defined_balances.defined_balance_id%TYPE;
1166 ln_def_balance_id_spc pay_defined_balances.defined_balance_id%TYPE;
1167 ln_term_ded_amt NUMBER;
1168 ln_term_ded_amt_spc NUMBER;
1169 ln_amt NUMBER;
1170 ln_term_ass_act_id pay_assignment_actions.assignment_action_id%TYPE;
1171 --
1172 ld_term_payment_date pay_payroll_actions.effective_date%TYPE;
1173 ld_date_earned pay_payroll_actions.date_earned%TYPE;
1174 ld_start_date DATE;
1175 l_yos NUMBER := null;
1176 l_ovr_yos NUMBER := null;
1177 l_st_date DATE := null;
1178 l_end_date DATE := null;
1179 l_value NUMBER := 0;
1180 l_date_range_str VARCHAR2(60);
1181
1182 --
1183 BEGIN
1184 --
1185 gb_debug := hr_utility.debug_enabled ;
1186 --
1187 IF gb_debug THEN
1188 --
1189 lc_procedure := gc_package||'ARCHIVE_CODE';
1190 hr_utility.set_location('Entering ' || lc_procedure,1);
1191 --
1192 END IF;
1193 --
1194 -- Fetch the Assignemnt Id
1195 --
1196 OPEN lcu_get_assignment_id(p_assignment_action_id);
1197 FETCH lcu_get_assignment_id INTO ln_assignment_id;
1198 CLOSE lcu_get_assignment_id;
1199 --
1200 OPEN lcu_get_bal_id;
1201 FETCH lcu_get_bal_id INTO ln_def_balance_id;
1202 CLOSE lcu_get_bal_id;
1203 --
1204 OPEN get_bal_id_spc;
1205 FETCH get_bal_id_spc INTO ln_def_balance_id_spc;
1206 CLOSE get_bal_id_spc;
1207 --
1208 IF (gr_parameters.rearchive_flag = 'Y') THEN
1209 --
1210 delete_assact(ln_assignment_id);
1211 lc_archive := 'Y';
1212 --
1213 ELSE
1214 --
1215 -- Checking whether record exists for this assignment
1216 --
1217 OPEN lcu_prev_archive(ln_assignment_id);
1218 FETCH lcu_prev_archive INTO lc_check_flag;
1219 CLOSE lcu_prev_archive;
1220 --
1221 IF lc_check_flag = 'Y' THEN
1222 --
1223 lc_archive := 'N';
1224 --
1225 ELSE
1226 --
1227 lc_archive := 'Y';
1228 --
1229 END IF;
1230 --
1231 END IF;
1232 --
1233 IF lc_archive = 'Y' THEN
1234 --
1235 FOR lr_emp_rec IN lcu_employee_details(ln_assignment_id,gr_parameters.effective_date)
1236 LOOP
1237 --
1238 --SWOT Details
1239 --
1240 IF gd_ystart_date >= TRUNC(lr_emp_rec.hire_date) THEN
1241 ld_start_date:= gd_ystart_date;
1242 ELSE
1243 ld_start_date:= TRUNC(lr_emp_rec.hire_date);
1244 END IF;
1245 --
1246 OPEN lcu_swot_details(lr_emp_rec.withhold_agent_id);
1247 FETCH lcu_swot_details INTO lc_swot_employer
1248 ,lc_swot_address_line1
1249 ,lc_swot_address_line2
1250 ,lc_swot_address_line3
1251 ,lc_swot_phone_number;
1252 CLOSE lcu_swot_details;
1253 --
1254 OPEN lcu_address_details(lr_emp_rec.person_id
1255 ,ld_start_date);
1256 FETCH lcu_address_details INTO lc_1st_jan_district_code
1257 ,lc_1st_jan_address_line1
1258 ,lc_1st_jan_address_line2
1259 ,lc_1st_jan_address_line3;
1260 CLOSE lcu_address_details;
1261 --
1262 FOR lr_get_assact_bal IN lcu_assact_details(lr_emp_rec.assignment_id
1263 ,gr_parameters.business_group_id
1264 )
1265 LOOP
1266 ln_amt := pay_jp_balance_pkg.get_balance_value(ln_def_balance_id,lr_get_assact_bal.assignment_action_id);
1267 ln_term_ded_amt_spc := pay_jp_balance_pkg.get_balance_value(ln_def_balance_id_spc,lr_get_assact_bal.assignment_action_id);
1268
1269 l_value := 0;
1270
1271 l_value := nvl(pay_jp_balance_pkg.get_balance_value_asg_run(hr_jp_id_pkg.balance_type_id('B_TRM_TXBL_ERN_MONEY',NULL,'JP')
1272 ,lr_get_assact_bal.assignment_action_id),0)
1273 + nvl(pay_jp_balance_pkg.get_balance_value_asg_run(hr_jp_id_pkg.balance_type_id('B_TRM_TXBL_ERN_KIND',NULL,'JP')
1274 ,lr_get_assact_bal.assignment_action_id),0);
1275
1276 IF ln_amt IS NOT NULL THEN
1277 IF l_value > 0 THEN
1278 ln_term_ded_amt := NVL(ln_term_ded_amt,0) + NVL(ln_amt,0);
1279 END IF;
1280 ln_term_ass_act_id := lr_get_assact_bal.assignment_action_id;
1281 ld_term_payment_date := lr_get_assact_bal.effective_date;
1282 ld_date_earned := lr_get_assact_bal.date_earned;
1283 END IF;
1284
1285 IF ln_term_ded_amt_spc IS NOT NULL THEN
1286 ln_term_ded_amt := NVL(ln_term_ded_amt,0) + NVL(ln_term_ded_amt_spc,0);
1287 END IF;
1288
1289 END LOOP;
1290 --
1291 -- Fetching service years
1292 --
1293 ln_service_years := pay_jp_balance_pkg.get_result_value_number('TRM_INCOME_DCT','SERVICE_YEARS',ln_term_ass_act_id);
1294 --
1295 -- Fetching Notification Flag
1296 --
1297 lc_note_submit_flag := pay_jp_balance_pkg.get_entry_value_char('COM_TRM_INFO','SUBMIT_FLAG',lr_emp_rec.assignment_id,ld_date_earned);
1298 --
1299 -- Fetching Term_ Withholding Tax Report Infromation
1300 --
1301 IF gb_debug THEN
1302 --
1303 hr_utility.set_location('Date Earned = ' || ld_date_earned,10);
1304 --
1305 END IF;
1306 --
1307 lc_descfield := pay_jp_balance_pkg.get_entry_value_char('TRM_WITHHOLD_TAX_REPORT_INFO','DESC_FIELD',lr_emp_rec.assignment_id,ld_date_earned); --Bug 9509191
1308 lc_descfield2 := pay_jp_balance_pkg.get_entry_value_char('TRM_WITHHOLD_TAX_REPORT_INFO','DESC_FIELD2',lr_emp_rec.assignment_id,ld_date_earned);
1312 --
1309 lc_descfield3 := pay_jp_balance_pkg.get_entry_value_char('TRM_WITHHOLD_TAX_REPORT_INFO','DESC_FIELD3',lr_emp_rec.assignment_id,ld_date_earned);
1310 lc_descfield4 := pay_jp_balance_pkg.get_entry_value_char('TRM_WITHHOLD_TAX_REPORT_INFO','DESC_FIELD4',lr_emp_rec.assignment_id,ld_date_earned);
1311 lc_descfield5 := pay_jp_balance_pkg.get_entry_value_char('TRM_WITHHOLD_TAX_REPORT_INFO','DESC_FIELD5',lr_emp_rec.assignment_id,ld_date_earned); --Bug 9509191
1313 lc_description1 := lc_descfield || lc_descfield2 || lc_descfield3;
1314 lc_description2 := lc_descfield4 || lc_descfield5;
1315
1316 l_value := 0;
1317 lc_description_temp := '';
1318
1319 l_value := nvl(pay_jp_balance_pkg.get_balance_value_asg_run(hr_jp_id_pkg.balance_type_id('B_TRM_TXBL_ERN_MONEY_SPC_EXC',NULL,'JP')
1320 ,ln_term_ass_act_id),0)
1321 + nvl(pay_jp_balance_pkg.get_balance_value_asg_run(hr_jp_id_pkg.balance_type_id('B_TRM_TXBL_ERN_KIND_SPC_EXC',NULL,'JP')
1322 ,ln_term_ass_act_id),0);
1323
1324 IF l_value > 0 THEN
1325
1326 IF l_value > 0 THEN
1327 fnd_message.set_name('PAY','PAY_JP_TRM_SPC_EXC_ALLOW_AMT');
1328 fnd_message.set_token('VALUE',to_char(l_value,fnd_currency.get_format_mask('JPY',40)));
1329 l_allow_str := fnd_message.get;
1330 lc_description_temp := l_allow_str;
1331 END IF;
1332
1333 l_yos := nvl(pay_jp_balance_pkg.get_entry_value_number('TRM_SPECIFIC_EXEC','YOS',lr_emp_rec.assignment_id,ld_date_earned),0);
1334
1335 IF l_yos > 0 THEN
1336 fnd_message.set_name('PAY','PAY_JP_TRM_SPC_EXC_YOS');
1337 fnd_message.set_token('NUM',l_yos);
1338 l_yos_str := fnd_message.get;
1339 lc_description_temp := lc_description_temp || l_yos_str;
1340 END IF;
1341
1342 l_st_date := pay_jp_balance_pkg.get_entry_value_date('TRM_SPECIFIC_EXEC','EXC_START_DATE',lr_emp_rec.assignment_id,ld_date_earned);
1343 l_end_date := pay_jp_balance_pkg.get_entry_value_date('TRM_SPECIFIC_EXEC','EXC_END_DATE',lr_emp_rec.assignment_id,ld_date_earned);
1344
1345 IF l_yos > 0 AND l_st_date IS NOT NULL AND l_end_date IS NOT NULL THEN
1346
1347 OPEN get_date_string(l_st_date,l_end_date);
1348 FETCH get_date_string INTO l_date_range_str;
1349 CLOSE get_date_string;
1350
1351 lc_description_temp := lc_description_temp || '(' || l_date_range_str || ')';
1352
1353 END IF;
1354
1355 l_ovr_yos := nvl(pay_jp_balance_pkg.get_entry_value_number('TRM_SPECIFIC_EXEC','OVR_YOS',lr_emp_rec.assignment_id,ld_date_earned),0);
1356
1357 IF l_ovr_yos > 0 THEN
1358 fnd_message.set_name('PAY','PAY_JP_TRM_SPC_EXC_OVR_YOS');
1359 fnd_message.set_token('NUM',l_ovr_yos);
1360 l_ovr_yos_str := fnd_message.get;
1361 lc_description_temp := lc_description_temp || l_ovr_yos_str;
1362 END IF;
1363
1364 IF lc_description_temp IS NOT NULL THEN
1365 lc_description1 := lc_description_temp || lc_description1;
1366 END IF;
1367
1368 END IF;
1369
1370 --
1371 --JP_IWHT_EMP Info
1372 --
1373 pay_action_information_api.create_action_information
1374 (
1375 p_validate => FALSE
1376 ,p_action_context_id => p_assignment_action_id
1377 ,p_action_context_type => 'AAP'
1378 ,p_action_information_category => 'JP_IWHT_EMP'
1379 ,p_tax_unit_id => NULL
1380 ,p_jurisdiction_code => NULL
1381 ,p_source_id => NULL
1382 ,p_source_text => NULL
1383 ,p_tax_group => NULL
1384 ,p_effective_date => p_effective_date
1385 ,p_assignment_id => fnd_number.number_to_canonical(lr_emp_rec.assignment_id)
1386 ,p_action_information1 => lr_emp_rec.employee_number
1387 ,p_action_information2 => lr_emp_rec.last_name_kana
1388 ,p_action_information3 => lr_emp_rec.first_name_kana
1389 ,p_action_information4 => lr_emp_rec.last_name_kanji
1390 ,p_action_information5 => lr_emp_rec.first_name_kanji
1391 ,p_action_information6 => lr_emp_rec.district_code
1392 ,p_action_information7 => lr_emp_rec.address_line1
1393 ,p_action_information8 => lr_emp_rec.address_line2
1394 ,p_action_information9 => lr_emp_rec.address_line3
1395 ,p_action_information10 => lc_1st_jan_district_code
1396 ,p_action_information11 => lc_1st_jan_address_line1
1397 ,p_action_information12 => lc_1st_jan_address_line2
1398 ,p_action_information13 => lc_1st_jan_address_line3
1399 ,p_action_information14 => fnd_date.date_to_canonical(lr_emp_rec.hire_date)
1400 ,p_action_information15 => fnd_date.date_to_canonical(lr_emp_rec.termination_date)
1401 ,p_action_information16 => ln_service_years
1402 ,p_action_information17 => lr_emp_rec.withhold_agent_id
1403 ,p_action_information18 => lc_swot_employer
1404 ,p_action_information19 => lc_swot_address_line1
1405 ,p_action_information20 => lc_swot_address_line2
1406 ,p_action_information21 => lc_swot_address_line3
1407 ,p_action_information22 => lc_swot_phone_number
1408 ,p_action_information23 => lc_description1
1409 ,p_action_information24 => lc_description2
1410 ,p_action_information_id => ln_action_info_id
1411 ,p_object_version_number => ln_obj_version_num
1412 );
1413 --
1414 lc_1st_jan_district_code := NULL;
1415 lc_1st_jan_address_line1 := NULL;
1416 lc_1st_jan_address_line2 := NULL;
1417 lc_1st_jan_address_line3 := NULL;
1418 --
1419 -- JP_IWHT_TAX Info ---------------
1420 --
1421 OPEN lcu_tax_details(lr_emp_rec.assignment_id,gr_parameters.business_group_id);
1422 FETCH lcu_tax_details INTO ln_termination_money
1423 ,ln_withholidng_tax
1424 ,ln_muncipal_tax
1425 ,ln_prefectural_tax;
1426 CLOSE lcu_tax_details;
1427 --
1428 pay_action_information_api.create_action_information
1429 (
1430 p_validate => FALSE
1431 ,p_action_context_id => p_assignment_action_id
1432 ,p_action_context_type => 'AAP'
1433 ,p_action_information_category => 'JP_IWHT_TAX'
1434 ,p_tax_unit_id => NULL
1435 ,p_jurisdiction_code => NULL
1436 ,p_source_id => NULL
1437 ,p_source_text => NULL
1438 ,p_tax_group => NULL
1439 ,p_effective_date => p_effective_date
1440 ,p_assignment_id => fnd_number.number_to_canonical(lr_emp_rec.assignment_id)
1441 ,p_action_information1 => NVL(lc_note_submit_flag,'N')
1442 ,p_action_information2 => fnd_number.number_to_canonical(ln_termination_money)
1443 ,p_action_information3 => fnd_number.number_to_canonical(ln_withholidng_tax)
1444 ,p_action_information4 => fnd_number.number_to_canonical(ln_muncipal_tax)
1445 ,p_action_information5 => fnd_number.number_to_canonical(ln_prefectural_tax)
1446 ,p_action_information6 => fnd_number.number_to_canonical(ln_term_ded_amt)
1447 ,p_action_information7 => fnd_date.date_to_canonical(ld_term_payment_date)
1448 ,p_action_information8 => fnd_date.date_to_canonical(ld_date_earned)
1449 ,p_action_information_id => ln_tax_action_info_id
1450 ,p_object_version_number => ln_tax_obj_version_num
1451 );
1452 --
1453 --END OF JP_IWHT_TAX Info ---------------
1454 --
1455 ln_termination_money := NULL;
1456 ln_withholidng_tax := NULL;
1457 ln_muncipal_tax := NULL;
1458 ln_prefectural_tax := NULL;
1459 ln_term_ass_act_id := NULL;
1460 ld_term_payment_date := NULL;
1461 ld_date_earned := NULL;
1462 lc_archive := 'N';
1463 --
1464 END LOOP;
1465 --
1466 END IF;
1467 --
1468 IF gb_debug THEN
1469 --
1470 hr_utility.set_location('Leaving ' || lc_procedure,10);
1471 --
1472 END IF;
1473 --
1474 EXCEPTION
1475 WHEN OTHERS THEN
1476 --
1477 hr_utility.set_location('Error in '||lc_procedure,999999);
1478 RAISE;
1479 END archive_code;
1480 --
1481 PROCEDURE deinitialize_code(p_payroll_action_id IN NUMBER)
1482 --************************************************************************
1483 -- PROCEDURE
1484 -- deinitialize_code
1485 --
1486 -- DESCRIPTION
1487 -- This package is used to remove temporary action codes
1488 --
1489 -- ACCESS
1490 -- PUBLIC
1491 --
1492 -- PARAMETERS
1493 -- ==========
1494 -- NAME TYPE DESCRIPTION
1495 -- ----------------- -------- ---------------------------------------
1496 -- p_payroll_action_id IN This parameter passes Assignment Action Id
1497 --
1498 -- PREREQUISITES
1499 -- None
1500 --
1501 -- CALLED BY
1502 -- None
1503 --************************************************************************/
1504
1505 IS
1506 --
1507 CURSOR lcu_assacts IS
1508 SELECT PAA.assignment_action_id
1509 FROM pay_assignment_actions PAA
1510 ,pay_payroll_actions PPA
1511 WHERE PAA.payroll_action_id = PPA.payroll_action_id
1512 AND PAA.action_status = 'C'
1513 AND PPA.report_type = gc_report_type
1514 AND NOT EXISTS( SELECT NULL
1515 FROM pay_action_information PAI
1516 WHERE PAI.action_context_id = PAA.assignment_action_id
1517 AND PAI.action_context_type = 'AAP');
1518
1519 --
1520 lc_proc CONSTANT VARCHAR2(61) := gc_package || 'deinitialise_code';
1521 --
1522 BEGIN
1523 --
1524 gb_debug := hr_utility.debug_enabled ;
1525 --
1526 IF gb_debug THEN
1527 hr_utility.set_location('Entering: ' || lc_proc, 10);
1528 END IF;
1529 --
1530 FOR l_rec IN lcu_assacts LOOP
1531 py_rollback_pkg.rollback_ass_action(l_rec.assignment_action_id);
1532 END LOOP;
1533 --
1534 IF gb_debug THEN
1535 --
1536 hr_utility.set_location('Leaving ' || lc_proc,20);
1537 --
1538 END IF;
1539 --
1540 END deinitialize_code;
1541 --
1542 END pay_jp_iwht_arch_pkg;