[Home] [Help]
PACKAGE BODY: APPS.PAY_DK_ARCHIVE
Source
1 PACKAGE BODY PAY_DK_ARCHIVE AS
2 /* $Header: pydkparc.pkb 120.6.12000000.6 2007/08/31 10:22:11 saurai noship $ */
3
4
5 ----------------- Globals , Record types , Tables -------------------------------------------------
6
7 g_debug boolean := hr_utility.debug_enabled;
8
9 /*Bug fix 6193177*/
10 TYPE element_rec IS RECORD (
11 classification_name VARCHAR2(60)
12 ,element_name VARCHAR2(60)
13 ,element_type_id NUMBER
14 ,input_value_id NUMBER
15 ,element_type VARCHAR2(1)
16 --,uom VARCHAR2(1)
17 ,uom VARCHAR2(20)
18 ,archive_flag VARCHAR2(1));
19
20 TYPE balance_rec IS RECORD (
21 balance_name VARCHAR2(60),
22 defined_balance_id NUMBER,
23 balance_type_id NUMBER);
24
25 TYPE lock_rec IS RECORD ( archive_assact_id NUMBER);
26
27 TYPE tax_card_rec IS RECORD (inp_val_name pay_input_values_f.NAME%type , screen_entry_val pay_element_entry_values_f.SCREEN_ENTRY_VALUE%type );
28
29 TYPE bal_val_rec IS RECORD ( bal_name ff_database_items.USER_NAME%type , bal_val NUMBER(10,2) );
30
31
32 TYPE tax_card_table IS TABLE OF tax_card_rec INDEX BY BINARY_INTEGER;
33 TYPE bal_val_table IS TABLE OF bal_val_rec INDEX BY BINARY_INTEGER;
34 TYPE element_table IS TABLE OF element_rec INDEX BY BINARY_INTEGER;
35 TYPE balance_table IS TABLE OF balance_rec INDEX BY BINARY_INTEGER;
36 TYPE lock_table IS TABLE OF lock_rec INDEX BY BINARY_INTEGER;
37
38 g_tax_card_tab tax_card_table;
39 g_bal_val bal_val_table;
40 g_element_table element_table;
41 g_user_balance_table balance_table;
42 g_lock_table lock_table;
43 g_index NUMBER := -1;
44 g_index_assact NUMBER := -1;
45 g_index_bal NUMBER := -1;
46 g_package VARCHAR2(33) := ' PAY_DK_ARCHIVE.';
47 g_payroll_action_id NUMBER;
48 g_arc_payroll_action_id NUMBER;
49 g_business_group_id NUMBER;
50 g_format_mask VARCHAR2(50);
51 g_err_num NUMBER;
52 g_errm VARCHAR2(150);
53
54 ------------------------------ FUNCTION GET_PARAMETER --------------------------------------------------------------------
55
56 /* GET PARAMETER */
57 FUNCTION GET_PARAMETER(
58 p_parameter_string IN VARCHAR2
59 ,p_token IN VARCHAR2
60 ,p_segment_number IN NUMBER default NULL ) RETURN VARCHAR2
61 IS
62
63 l_parameter pay_payroll_actions.legislative_parameters%TYPE:=NULL;
64 l_start_pos NUMBER;
65 l_delimiter VARCHAR2(1):=' ';
66 l_proc VARCHAR2(40):= g_package||' get parameter ';
67
68 BEGIN
69 -- fnd_file.put_line(fnd_file.log,'Entering Function GET_PARAMETER');
70 --
71 IF g_debug THEN
72 hr_utility.set_location(' Entering Function GET_PARAMETER',10);
73 END IF;
74 l_start_pos := instr(' '||p_parameter_string,l_delimiter||p_token||'=');
75 --
76
77 IF l_start_pos = 0 THEN
78 l_delimiter := '|';
79 l_start_pos := instr(' '||p_parameter_string,l_delimiter||p_token||'=');
80 END IF;
81
82 IF l_start_pos <> 0 THEN
83 l_start_pos := l_start_pos + length(p_token||'=');
84 l_parameter := substr(p_parameter_string, l_start_pos, instr(p_parameter_string||' ', l_delimiter,l_start_pos) - l_start_pos);
85
86 IF p_segment_number IS NOT NULL THEN
87 l_parameter := ':'||l_parameter||':';
88 l_parameter := substr(l_parameter,
89 instr(l_parameter,':',1,p_segment_number)+1,
90 instr(l_parameter,':',1,p_segment_number+1) -1
91 - instr(l_parameter,':',1,p_segment_number));
92 END IF;
93 END IF;
94 --
95 RETURN l_parameter;
96
97 IF g_debug THEN
98 hr_utility.set_location(' Leaving Function GET_PARAMETER',20);
99 END IF;
100 -- fnd_file.put_line(fnd_file.log,'Leaving Function GET_PARAMETER');
101 END;
102
103 --------------------------------- PROCEDURE GET_ALL_PARAMETERS -----------------------------------------------------------------
104
105 /* GET ALL PARAMETERS */
106 PROCEDURE GET_ALL_PARAMETERS(
107 p_payroll_action_id IN NUMBER
108 ,p_business_group_id OUT NOCOPY NUMBER
109 ,p_start_date OUT NOCOPY VARCHAR2
110 ,p_end_date OUT NOCOPY VARCHAR2
111 ,p_effective_date OUT NOCOPY DATE
112 ,p_payroll_id OUT NOCOPY VARCHAR2
113 ,p_consolidation_set OUT NOCOPY VARCHAR2) IS
114 --
115 CURSOR csr_parameter_info(p_payroll_action_id NUMBER) IS
116 SELECT PAY_DK_ARCHIVE.GET_PARAMETER(legislative_parameters,'PAYROLL_ID')
117 ,PAY_DK_ARCHIVE.GET_PARAMETER(legislative_parameters,'CONSOLIDATION_SET_ID')
118 ,PAY_DK_ARCHIVE.GET_PARAMETER(legislative_parameters,'START_DATE')
119 ,PAY_DK_ARCHIVE.GET_PARAMETER(legislative_parameters,'END_DATE')
120 ,effective_date
121 ,business_group_id
122 FROM pay_payroll_actions
123 WHERE payroll_action_id = p_payroll_action_id;
124
125 l_proc VARCHAR2(240):= g_package||' GET_ALL_PARAMETERS ';
126 --
127
128 BEGIN
129 -- fnd_file.put_line(fnd_file.log,'Entering Procedure GET_ALL_PARAMETERS');
130
131 OPEN csr_parameter_info (p_payroll_action_id);
132 FETCH csr_parameter_info INTO p_payroll_id
133 ,p_consolidation_set
134 ,p_start_date
135 ,p_end_date
136 ,p_effective_date
137 ,p_business_group_id;
138 CLOSE csr_parameter_info;
139 --
140 IF g_debug THEN
141 hr_utility.set_location(' Leaving Procedure GET_ALL_PARAMETERS',30);
142 END IF;
143 -- fnd_file.put_line(fnd_file.log,'Leaving Procedure GET_ALL_PARAMETERS');
144
145 END GET_ALL_PARAMETERS;
146
147 ----------------------------------- PROCEDURE RANGE_CODE ---------------------------------------------------------------
148
149 /* RANGE CODE */
150 PROCEDURE RANGE_CODE (p_payroll_action_id IN NUMBER
151 ,p_sql OUT NOCOPY VARCHAR2)
152 IS
153
154 -----------------------------------------------------
155 -- MESSAGES
156 ----------------------------------------------------
157 -- Cursor to get the messages from Busineess Group:Payslip Info
158 CURSOR csr_get_message(p_bus_grp_id NUMBER) IS
159 SELECT org_information6 message
160 FROM hr_organization_information
161 WHERE organization_id = p_bus_grp_id
162 AND org_information_context = 'Business Group:Payslip Info'
163 AND org_information1 = 'MESG';
164
165 -----------------------------------------------------------------
166 -- BALANCES
167 -----------------------------------------------------------------
168
169 /* Cursor to retrieve Other Balances Information */
170 CURSOR csr_get_balance(p_bus_grp_id NUMBER) IS
171 SELECT org_information4 balance_type_id
172 ,org_information5 balance_dim_id
173 ,org_information7 narrative
174 FROM hr_organization_information
175 WHERE organization_id = p_bus_grp_id
176 AND org_information_context = 'Business Group:Payslip Info'
177 AND org_information1 = 'BALANCE';
178
179 /* Cursor to fetch defined balance id */
180 CURSOR csr_def_balance(bal_type_id NUMBER, bal_dim_id NUMBER) IS
181 SELECT defined_balance_id
182 FROM pay_defined_balances
183 WHERE balance_type_id = bal_type_id
184 AND balance_dimension_id = bal_dim_id;
185
186 -----------------------------------------------------
187 --ELEMENTS
188 ----------------------------------------------------
189
190 /* Cursor to retrieve Time Period Information */
191 CURSOR csr_time_periods(p_run_payact_id NUMBER ,p_payroll_id NUMBER) IS
192 SELECT ptp.end_date end_date,
193 ptp.start_date start_date,
194 ptp.period_name period_name,
195 ppf.payroll_name payroll_name
196 FROM per_time_periods ptp
197 ,pay_payroll_actions ppa
198 ,pay_payrolls_f ppf
199 WHERE ptp.payroll_id = ppa.payroll_id
200 AND ppa.payroll_action_id = p_run_payact_id
201 AND ppa.payroll_id = ppf.payroll_id
202 AND ppf.payroll_id = NVL(p_payroll_id , ppf.payroll_id)
203 AND ppa.date_earned BETWEEN ptp.start_date AND ptp.end_date
204 AND ppa.date_earned BETWEEN ppf.effective_start_date AND ppf.effective_end_date;
205
206 --------------------------------------------------------------
207 -- Additional Element
208 --------------------------------------------------------------
209
210 /* Cursor to retrieve Additional Element Information */
211 CURSOR csr_get_element(p_bus_grp_id NUMBER, p_date_earned DATE) IS
212 SELECT hoi.org_information2 element_type_id
213 ,hoi.org_information3 input_value_id
214 ,hoi.org_information7 element_narrative
215 ,pec.classification_name
216 ,piv.uom
217 FROM hr_organization_information hoi
218 ,pay_element_classifications pec
219 ,pay_element_types_f pet
220 ,pay_input_values_f piv
221 WHERE hoi.organization_id = p_bus_grp_id
222 AND hoi.org_information_context = 'Business Group:Payslip Info'
223 AND hoi.org_information1 = 'ELEMENT'
224 AND hoi.org_information2 = pet.element_type_id
225 AND pec.classification_id = pet.classification_id
226 AND piv.input_value_id = hoi.org_information3
227 AND p_date_earned BETWEEN piv.effective_start_date AND piv.effective_end_date;
228
229 --------------
230
231 rec_time_periods csr_time_periods%ROWTYPE;
232 rec_get_balance csr_get_balance%ROWTYPE;
233 rec_get_message csr_get_message%ROWTYPE;
234 rec_get_element csr_get_element%ROWTYPE;
235 l_action_info_id NUMBER;
236 l_ovn NUMBER;
237 l_business_group_id NUMBER;
238 l_start_date VARCHAR2(30);
239 l_end_date VARCHAR2(30);
240 l_effective_date DATE;
241 l_consolidation_set NUMBER;
242 l_defined_balance_id NUMBER := 0;
243 l_count NUMBER := 0;
244 l_prev_prepay NUMBER := 0;
245 l_canonical_start_date DATE;
246 l_canonical_end_date DATE;
247 l_payroll_id NUMBER;
248 l_prepay_action_id NUMBER;
249 l_actid NUMBER;
250 l_assignment_id NUMBER;
251 l_action_sequence NUMBER;
252 l_assact_id NUMBER;
253 l_pact_id NUMBER;
254 l_flag NUMBER := 0;
255 l_element_context VARCHAR2(5);
256
257 ----------------
258
259 BEGIN
260 -- fnd_file.put_line(fnd_file.log,'Entering Procedure RANGE_CODE');
261 IF g_debug THEN
262 hr_utility.set_location(' Entering Procedure RANGE_CODE',40);
263 END IF;
264
265 PAY_DK_ARCHIVE.GET_ALL_PARAMETERS(p_payroll_action_id
266 ,l_business_group_id
267 ,l_start_date
268 ,l_end_date
269 ,l_effective_date
270 ,l_payroll_id
271 ,l_consolidation_set);
272
273 l_canonical_start_date := TO_DATE(l_start_date,'YYYY/MM/DD');
274 l_canonical_end_date := TO_DATE(l_end_date,'YYYY/MM/DD');
275
276 -- get the messages from Busineess Group:Payslip Info
277 OPEN csr_get_message(l_business_group_id);
278 LOOP
279 FETCH csr_get_message INTO rec_get_message;
280 EXIT WHEN csr_get_message%NOTFOUND;
281
282 -- archive the messages
283 pay_action_information_api.create_action_information (
284 p_action_information_id => l_action_info_id
285 ,p_action_context_id => p_payroll_action_id
286 ,p_action_context_type => 'PA'
287 ,p_object_version_number => l_ovn
288 ,p_effective_date => l_effective_date
289 ,p_source_id => NULL
290 ,p_source_text => NULL
291 ,p_action_information_category => 'EMPLOYEE OTHER INFORMATION'
292 ,p_action_information1 => l_business_group_id
293 ,p_action_information2 => 'MESG' -- Message Context
294 ,p_action_information3 => NULL
295 ,p_action_information4 => NULL
296 ,p_action_information5 => NULL
297 ,p_action_information6 => rec_get_message.message);
298
299 END LOOP;
300 CLOSE csr_get_message;
301
302 -------------------------------------------------------------------------------------
303 -- Initialize Balance Definitions
304 -------------------------------------------------------------------------------------
305
306 -- get the balances from Busineess Group:Payslip Info
307 OPEN csr_get_balance(l_business_group_id);
308 LOOP
309 FETCH csr_get_balance INTO rec_get_balance;
310 EXIT WHEN csr_get_balance%NOTFOUND;
311
312 -- get the defined balance id for the balances got above
313 OPEN csr_def_balance(rec_get_balance.balance_type_id,rec_get_balance.balance_dim_id);
314 FETCH csr_def_balance INTO l_defined_balance_id;
315 CLOSE csr_def_balance;
316
317 BEGIN
318 -- check if the balance has already been archived
319 SELECT 1 INTO l_flag
320 FROM pay_action_information
321 WHERE action_information_category = 'EMEA BALANCE DEFINITION'
322 AND action_context_id = p_payroll_action_id
323 AND action_information2 = l_defined_balance_id
324 AND action_information6 = 'OBAL'
325 AND action_information4 = rec_get_balance.narrative;
326
327 EXCEPTION WHEN NO_DATA_FOUND THEN
328
329 -- archive the balance definition as it has not been archived before
330 pay_action_information_api.create_action_information (
331 p_action_information_id => l_action_info_id
332 ,p_action_context_id => p_payroll_action_id
333 ,p_action_context_type => 'PA'
334 ,p_object_version_number => l_ovn
335 ,p_effective_date => l_effective_date
336 ,p_source_id => NULL
337 ,p_source_text => NULL
338 ,p_action_information_category => 'EMEA BALANCE DEFINITION'
339 ,p_action_information1 => NULL
340 ,p_action_information2 => l_defined_balance_id
341 ,p_action_information4 => rec_get_balance.narrative
342 ,p_action_information6 => 'OBAL');
343
344 WHEN OTHERS THEN
345 NULL;
346 END;
347
348 END LOOP;
349 CLOSE csr_get_balance;
350
351 -----------------------------------------------------------------------------
352 --Initialize Element Definitions
353 -----------------------------------------------------------------------------
354
355 g_business_group_id := l_business_group_id;
356
357 ARCHIVE_ELEMENT_INFO(p_payroll_action_id => p_payroll_action_id
358 ,p_effective_date => l_effective_date
359 ,p_date_earned => l_canonical_end_date
360 ,p_pre_payact_id => NULL);
361
362 -----------------------------------------------------------------------------
363 --Archive Additional Element Definitions
364 -----------------------------------------------------------------------------
365
366 l_element_context := 'F';
367
368 OPEN csr_get_element(l_business_group_id,l_canonical_end_date);
369 LOOP
370 FETCH csr_get_element INTO rec_get_element;
371 EXIT WHEN csr_get_element%NOTFOUND;
372 BEGIN
373 -- check if the element definition has already been archived
374 SELECT 1 INTO l_flag
375 FROM pay_action_information
376 WHERE action_context_id = p_payroll_action_id
377 AND action_information_category = 'EMEA ELEMENT DEFINITION'
378 AND action_information2 = rec_get_element.element_type_id
379 AND action_information3 = rec_get_element.input_value_id
380 AND action_information5 = l_element_context;
381
382 EXCEPTION WHEN NO_DATA_FOUND THEN
383 -- archive the element definition since it has not been archived
384
385 pay_action_information_api.create_action_information (
386 p_action_information_id => l_action_info_id
387 ,p_action_context_id => p_payroll_action_id
388 ,p_action_context_type => 'PA'
389 ,p_object_version_number => l_ovn
390 ,p_effective_date => l_effective_date
391 ,p_source_id => NULL
392 ,p_source_text => NULL
393 ,p_action_information_category => 'EMEA ELEMENT DEFINITION'
394 ,p_action_information1 => NULL
395 ,p_action_information2 => rec_get_element.element_type_id
396 ,p_action_information3 => rec_get_element.input_value_id
397 ,p_action_information4 => rec_get_element.element_narrative
398 ,p_action_information5 => l_element_context
399 ,p_action_information6 => rec_get_element.uom
400 ,p_action_information7 => l_element_context);
401
402 WHEN OTHERS THEN
403 NULL;
404 END;
405
406 END LOOP;
407 CLOSE csr_get_element;
408
409 p_sql := 'SELECT DISTINCT person_id
410 FROM per_people_f ppf
411 ,pay_payroll_actions ppa
412 WHERE ppa.payroll_action_id = :payroll_action_id
413 AND ppa.business_group_id = ppf.business_group_id
414 ORDER BY ppf.person_id';
415
416 IF g_debug THEN
417 hr_utility.set_location(' Leaving Procedure RANGE_CODE',50);
418 END IF;
419
420 -- fnd_file.put_line(fnd_file.log,'Leaving Procedure RANGE_CODE');
421
422 EXCEPTION
423 WHEN OTHERS THEN
424 -- Return cursor that selects no rows
425 p_sql := 'select 1 from dual where to_char(:payroll_action_id) = dummy';
426
427 END RANGE_CODE;
428
429 ---------------------------------- PROCEDURE ASSIGNMENT_ACTION_CODE ----------------------------------------------------------------
430
431 /* ASSIGNMENT ACTION CODE */
432 PROCEDURE ASSIGNMENT_ACTION_CODE
433 (p_payroll_action_id IN NUMBER
434 ,p_start_person IN NUMBER
435 ,p_end_person IN NUMBER
436 ,p_chunk IN NUMBER)
437 IS
438
439 -----------
440
441 CURSOR csr_prepaid_assignments(p_payroll_action_id NUMBER,
442 p_start_person NUMBER,
443 p_end_person NUMBER,
444 p_payroll_id NUMBER,
445 p_consolidation_id NUMBER,
446 l_canonical_start_date DATE,
447 l_canonical_end_date DATE)
448 IS
449 SELECT act.assignment_id assignment_id,
450 act.assignment_action_id run_action_id,
451 act1.assignment_action_id prepaid_action_id
452 FROM pay_payroll_actions ppa,
453 pay_payroll_actions appa,
454 pay_payroll_actions appa2,
455 pay_assignment_actions act,
456 pay_assignment_actions act1,
457 pay_action_interlocks pai,
458 per_all_assignments_f as1
459 WHERE ppa.payroll_action_id = p_payroll_action_id
460 AND appa.consolidation_set_id = p_consolidation_id
461 AND appa.effective_date BETWEEN l_canonical_start_date AND l_canonical_end_date
462 AND as1.person_id BETWEEN p_start_person AND p_end_person
463 AND appa.action_type IN ('R','Q') -- Payroll Run or Quickpay Run
464 AND act.payroll_action_id = appa.payroll_action_id
465 AND act.source_action_id IS NULL -- Master Action
466 AND as1.assignment_id = act.assignment_id
467 AND ppa.effective_date BETWEEN as1.effective_start_date AND as1.effective_end_date
468 AND act.action_status = 'C' -- Completed
469 AND act.assignment_action_id = pai.locked_action_id
470 AND act1.assignment_action_id = pai.locking_action_id
471 AND act1.action_status = 'C' -- Completed
472 AND act1.payroll_action_id = appa2.payroll_action_id
473 AND appa2.action_type IN ('P','U') -- Prepayments or Quickpay Prepayments
474 AND appa2.effective_date BETWEEN l_canonical_start_date AND l_canonical_end_date
475 AND (as1.payroll_id = p_payroll_id OR p_payroll_id IS NULL)
476
477 AND NOT EXISTS (SELECT /* + ORDERED */ NULL
478 FROM pay_action_interlocks pai1,
479 pay_assignment_actions act2,
480 pay_payroll_actions appa3
481 WHERE pai1.locked_action_id = act.assignment_action_id
482 AND act2.assignment_action_id= pai1.locking_action_id
483 AND act2.payroll_action_id = appa3.payroll_action_id
484 AND appa3.action_type = 'X'
485 AND appa3.action_status = 'C'
486 AND appa3.report_type = 'PYDKARCHIVE')
487
488 AND NOT EXISTS ( SELECT /* + ORDERED */ NULL
489 FROM pay_action_interlocks pai1,
490 pay_assignment_actions act2,
491 pay_payroll_actions appa3
492 WHERE pai1.locked_action_id = act.assignment_action_id
493 AND act2.assignment_action_id= pai1.locking_action_id
494 AND act2.payroll_action_id = appa3.payroll_action_id
495 AND appa3.action_type = 'V'
496 AND appa3.action_status = 'C')
497
498 ORDER BY act.assignment_id;
499
500 -----------
501
502 l_count NUMBER := 0;
503 l_prev_prepay NUMBER := 0;
504 l_business_group_id NUMBER;
505 l_start_date VARCHAR2(20);
506 l_end_date VARCHAR2(20);
507 l_canonical_start_date DATE;
508 l_canonical_end_date DATE;
509 l_effective_date DATE;
510 l_payroll_id NUMBER;
511 l_consolidation_set NUMBER;
512 l_prepay_action_id NUMBER;
513 l_actid NUMBER;
514 l_assignment_id NUMBER;
515 l_action_sequence NUMBER;
516 l_assact_id NUMBER;
517 l_pact_id NUMBER;
518 l_flag NUMBER := 0;
519 l_defined_balance_id NUMBER := 0;
520 l_action_info_id NUMBER;
521 l_ovn NUMBER;
522 ----------------
523
524 BEGIN
525
526 -- fnd_file.put_line(fnd_file.log,'Entering Procedure ASSIGNMENT_ACTION_CODE');
527 IF g_debug THEN
528 hr_utility.set_location(' Entering Procedure ASSIGNMENT_ACTION_CODE',60);
529 END IF;
530
531 PAY_DK_ARCHIVE.GET_ALL_PARAMETERS(p_payroll_action_id
532 ,l_business_group_id
533 ,l_start_date
534 ,l_end_date
535 ,l_effective_date
536 ,l_payroll_id
537 ,l_consolidation_set);
538
539 l_canonical_start_date := TO_DATE(l_start_date,'YYYY/MM/DD');
540 l_canonical_end_date := TO_DATE(l_end_date,'YYYY/MM/DD');
541 l_prepay_action_id := 0;
542
543 FOR rec_prepaid_assignments IN csr_prepaid_assignments(p_payroll_action_id
544 ,p_start_person
545 ,p_end_person
546 ,l_payroll_id
547 ,l_consolidation_set
548 ,l_canonical_start_date
549 ,l_canonical_end_date) LOOP
550
551 IF l_prepay_action_id <> rec_prepaid_assignments.prepaid_action_id THEN
552
553 SELECT pay_assignment_actions_s.NEXTVAL
554 INTO l_actid
555 FROM dual;
556 --
557 g_index_assact := g_index_assact + 1;
558 g_lock_table(g_index_assact).archive_assact_id := l_actid; /* For Element archival */
559
560 -- Create the archive assignment action
561 hr_nonrun_asact.insact(l_actid
562 ,rec_prepaid_assignments.assignment_id
563 ,p_payroll_action_id
564 ,p_chunk
565 ,NULL);
566 -- Create archive to prepayment assignment action interlock
567 --
568 hr_nonrun_asact.insint(l_actid,rec_prepaid_assignments.prepaid_action_id);
569 END IF;
570
571 -- create archive to master assignment action interlock
572 hr_nonrun_asact.insint(l_actid,rec_prepaid_assignments.run_action_id);
573 l_prepay_action_id := rec_prepaid_assignments.prepaid_action_id;
574
575 END LOOP;
576
577 IF g_debug THEN
578 hr_utility.set_location(' Leaving Procedure ASSIGNMENT_ACTION_CODE',70);
579 END IF;
580 -- fnd_file.put_line(fnd_file.log,'Leaving Procedure ASSIGNMENT_ACTION_CODE');
581
582 END ASSIGNMENT_ACTION_CODE;
583
584 ------------------------------------- PROCEDURE INITIALIZATION_CODE -------------------------------------------------------------
585
586 /* INITIALIZATION CODE */
587
588 PROCEDURE INITIALIZATION_CODE(p_payroll_action_id IN NUMBER)
589 IS
590
591 -------------
592
593 CURSOR csr_prepay_id IS
594 SELECT distinct prepay_payact.payroll_action_id prepay_payact_id
595 ,run_payact.date_earned date_earned
596 FROM pay_action_interlocks archive_intlck
597 ,pay_assignment_actions prepay_assact
598 ,pay_payroll_actions prepay_payact
599 ,pay_action_interlocks prepay_intlck
600 ,pay_assignment_actions run_assact
601 ,pay_payroll_actions run_payact
602 ,pay_assignment_actions archive_assact
603 WHERE archive_intlck.locking_action_id = archive_assact.assignment_action_id
604 and archive_assact.payroll_action_id = p_payroll_action_id
605 AND prepay_assact.assignment_action_id = archive_intlck.locked_action_id
606 AND prepay_payact.payroll_action_id = prepay_assact.payroll_action_id
607 AND prepay_payact.action_type IN ('U','P')
608 AND prepay_intlck.locking_action_id = prepay_assact.assignment_action_id
609 AND run_assact.assignment_action_id = prepay_intlck.locked_action_id
610 AND run_payact.payroll_action_id = run_assact.payroll_action_id
611 AND run_payact.action_type IN ('Q', 'R')
612 ORDER BY prepay_payact.payroll_action_id;
613
614 --------------
615
616 /* Cursor to retrieve Run Assignment Action Ids */
617 CURSOR csr_runact_id IS
618 SELECT distinct prepay_payact.payroll_action_id prepay_payact_id
619 ,run_payact.date_earned date_earned
620 ,run_payact.payroll_action_id run_payact_id
621 FROM pay_action_interlocks archive_intlck
622 ,pay_assignment_actions prepay_assact
623 ,pay_payroll_actions prepay_payact
624 ,pay_action_interlocks prepay_intlck
625 ,pay_assignment_actions run_assact
626 ,pay_payroll_actions run_payact
627 ,pay_assignment_actions archive_assact
628 WHERE archive_intlck.locking_action_id = archive_assact.assignment_action_id
629 and archive_assact.payroll_action_id = p_payroll_action_id
630 AND prepay_assact.assignment_action_id = archive_intlck.locked_action_id
631 AND prepay_payact.payroll_action_id = prepay_assact.payroll_action_id
632 AND prepay_payact.action_type IN ('U','P')
633 AND prepay_intlck.locking_action_id = prepay_assact.assignment_action_id
634 AND run_assact.assignment_action_id = prepay_intlck.locked_action_id
635 AND run_payact.payroll_action_id = run_assact.payroll_action_id
636 AND run_payact.action_type IN ('Q', 'R')
637 ORDER BY prepay_payact.payroll_action_id;
638
639 -------------
640
641 rec_prepay_id csr_prepay_id%ROWTYPE;
642 rec_runact_id csr_runact_id%ROWTYPE;
643 l_action_info_id NUMBER;
644 l_ovn NUMBER;
645 l_count NUMBER := 0;
646 l_business_group_id NUMBER;
647 l_start_date VARCHAR2(20);
648 l_end_date VARCHAR2(20);
649 l_effective_date DATE;
650 l_payroll_id NUMBER;
651 l_consolidation_set NUMBER;
652 l_prev_prepay NUMBER := 0;
653
654 ---------------
655
656 BEGIN
657
658 -- fnd_file.put_line(fnd_file.log,'Entering Procedure INITIALIZATION_CODE');
659
660 IF g_debug THEN
661 hr_utility.set_location(' Entering Procedure INITIALIZATION_CODE',80);
662 END IF;
663
664 /*fnd_file.put_line(fnd_file.log,'In INIT_CODE 0');*/
665
666 GET_ALL_PARAMETERS(p_payroll_action_id
667 ,l_business_group_id
668 ,l_start_date
669 ,l_end_date
670 ,l_effective_date
671 ,l_payroll_id
672 ,l_consolidation_set);
673
674 g_arc_payroll_action_id := p_payroll_action_id;
675 g_business_group_id := l_business_group_id;
676
677 /* Archive Element Details */
678 OPEN csr_prepay_id;
679 LOOP
680 FETCH csr_prepay_id INTO rec_prepay_id;
681 EXIT WHEN csr_prepay_id%NOTFOUND;
682 ---------------------------------------------------------
683 --Initialize Global tables once every prepayment payroll
684 --action id and once every thread
685 ---------------------------------------------------------
686 IF (rec_prepay_id.prepay_payact_id <> l_prev_prepay) THEN
687 ARCHIVE_ADD_ELEMENT(p_archive_assact_id => NULL,
688 p_assignment_action_id => NULL,
689 p_assignment_id => NULL,
690 p_payroll_action_id => p_payroll_action_id,
691 p_date_earned => rec_prepay_id.date_earned,
692 p_effective_date => l_effective_date,
693 p_pre_payact_id => rec_prepay_id.prepay_payact_id,
694 p_archive_flag => 'N');
695
696 END IF;
697
698 l_prev_prepay := rec_prepay_id.prepay_payact_id;
699 END LOOP;
700
701 CLOSE csr_prepay_id;
702
703 /* Initialize Global tables for Balances */
704 ARCHIVE_OTH_BALANCE(p_archive_assact_id => NULL,
705 p_assignment_action_id => NULL,
706 p_assignment_id => NULL,
707 p_payroll_action_id => p_payroll_action_id,
708 p_record_count => NULL,
709 p_pre_payact_id => NULL, --rec_prepay_id.prepay_payact_id,
710 p_effective_date => l_effective_date,
711 p_date_earned => NULL,
712 p_archive_flag => 'N');
713
714 IF g_debug THEN
715 hr_utility.set_location(' Leaving Procedure INITIALIZATION_CODE',90);
716 END IF;
717 -- fnd_file.put_line(fnd_file.log,'Leaving Procedure INITIALIZATION_CODE');
718
719
720 EXCEPTION WHEN OTHERS THEN
721 g_err_num := SQLCODE;
722 -- fnd_file.put_line(fnd_file.log,'ORA_ERR: ' || g_err_num || 'In INITIALIZATION_CODE');
723
724 IF g_debug THEN
725 hr_utility.set_location('ORA_ERR: ' || g_err_num || 'In INITIALIZATION_CODE',180);
726 END IF;
727
728 -- fnd_file.put_line(fnd_file.log,'Leaving Procedure INITIALIZATION_CODE');
729
730 END INITIALIZATION_CODE;
731
732 ------------------------------------- PROCEDURE SETUP_ELEMENT_DEFINITIONS -------------------------------------------------------------
733
734 PROCEDURE SETUP_ELEMENT_DEFINITIONS( p_classification_name IN VARCHAR2
735 ,p_element_name IN VARCHAR2
736 ,p_element_type_id IN NUMBER
737 ,p_input_value_id IN NUMBER
738 ,p_element_type IN VARCHAR2
739 ,p_uom IN VARCHAR2
740 ,p_archive_flag IN VARCHAR2)
741 IS
742
743 BEGIN
744
745 IF g_debug THEN
746 hr_utility.set_location(' Entering Procedure SETUP_ELEMENT_DEFINITIONS',100);
747 END IF;
748
749 g_index := g_index + 1;
750 /* Initialize global tables that hold Additional Element details */
751 g_element_table(g_index).classification_name := p_classification_name;
752 g_element_table(g_index).element_name := p_element_name;
753 g_element_table(g_index).element_type := p_element_type;
754 g_element_table(g_index).element_type_id := p_element_type_id;
755 g_element_table(g_index).input_value_id := p_input_value_id;
756 g_element_table(g_index).uom := p_uom;
757 g_element_table(g_index).archive_flag := p_archive_flag;
758
759 IF g_debug THEN
760 hr_utility.set_location(' Leaving Procedure SETUP_ELEMENT_DEFINITIONS',110);
761 END IF;
762
763 END SETUP_ELEMENT_DEFINITIONS;
764
765 ------------------------------------ PROCEDURE SETUP_BALANCE_DEFINITIONS --------------------------------------------------------------
766
767 PROCEDURE SETUP_BALANCE_DEFINITIONS(p_balance_name IN VARCHAR2
768 ,p_defined_balance_id IN NUMBER
769 ,p_balance_type_id IN NUMBER)
770 IS
771 BEGIN
772
773 IF g_debug THEN
774 hr_utility.set_location(' Entering Procedure SETUP_BALANCE_DEFINITIONS',120);
775 END IF;
776
777 g_index_bal := g_index_bal + 1;
778 /* Initialize global tables that hold Other Balances details */
779 g_user_balance_table(g_index_bal).balance_name := p_balance_name;
780 g_user_balance_table(g_index_bal).defined_balance_id := p_defined_balance_id;
781 g_user_balance_table(g_index_bal).balance_type_id := p_balance_type_id;
782
783 --fnd_file.put_line(fnd_file.log,'SETUP_BALANCE_DEFINITIONS ' ||p_balance_name);
784
785 IF g_debug THEN
786 hr_utility.set_location(' Leaving Procedure SETUP_BALANCE_DEFINITIONS',130);
787 END IF;
788
789 END SETUP_BALANCE_DEFINITIONS;
790
791 ------------------------------------ FUNCTION GET_COUNTRY_NAME --------------------------------------------------------------
792
793 /* GET COUNTRY NAME FROM CODE */
794
795 FUNCTION GET_COUNTRY_NAME(p_territory_code VARCHAR2)
796 RETURN VARCHAR2
797 IS
798
799 CURSOR csr_get_territory_name(p_territory_code VARCHAR2) Is
800 SELECT territory_short_name
801 FROM fnd_territories_vl
802 WHERE territory_code = p_territory_code;
803
804 l_country fnd_territories_vl.territory_short_name%TYPE;
805
806 BEGIN
807
808 IF g_debug THEN
809 hr_utility.set_location(' Entering Function GET_COUNTRY_NAME',140);
810 END IF;
811
812 OPEN csr_get_territory_name(p_territory_code);
813 FETCH csr_get_territory_name into l_country;
814 CLOSE csr_get_territory_name;
815
816 RETURN l_country;
817
818 IF g_debug THEN
819 hr_utility.set_location(' Leaving Function GET_COUNTRY_NAME',150);
820 END IF;
821
822 END GET_COUNTRY_NAME;
823
824 --------------------------------------- PROCEDURE ARCHIVE_EMPLOYEE_DETAILS -----------------------------------------------------------
825
826 /* EMPLOYEE DETAILS REGION */
827
828 PROCEDURE ARCHIVE_EMPLOYEE_DETAILS (p_archive_assact_id IN NUMBER
829 ,p_assignment_id IN NUMBER
830 ,p_assignment_action_id IN NUMBER
831 ,p_payroll_action_id IN NUMBER
832 ,p_time_period_id IN NUMBER
833 ,p_date_earned IN DATE
834 ,p_pay_date_earned IN DATE
835 ,p_effective_date IN DATE) IS
836
837 -------------
838 /* Cursor to retrieve person details about Employee */
839 CURSOR csr_person_details(p_assignment_id NUMBER) IS
840 SELECT ppf.person_id person_id,
841 ppf.full_name full_name,
842 ppf.national_identifier ni_number,
843 ppf.nationality nationality,
844 pps.date_start start_date,
845 ppf.employee_number emp_num,
846 ppf.first_name first_name,
847 ppf.last_name last_name,
848 ppf.title title,
849 paf.location_id loc_id,
850 paf.organization_id org_id, -- HR Org at Asg level
851 paf.job_id job_id,
852 paf.position_id pos_id,
853 paf.grade_id grade_id,
854 paf.business_group_id bus_grp_id
855 FROM per_assignments_f paf,
856 per_all_people_f ppf,
857 per_periods_of_service pps
858 WHERE paf.person_id = ppf.person_id
859 AND paf.assignment_id = p_assignment_id
860 AND pps.person_id = ppf.person_id
861 AND p_date_earned BETWEEN paf.effective_start_date AND paf.effective_end_date
862 AND p_date_earned BETWEEN ppf.effective_start_date AND ppf.effective_end_date;
863
864 -------------
865 /* Cursor to retrieve primary address of Employee */
866 CURSOR csr_primary_address(p_person_id NUMBER) IS
867 SELECT pa.person_id person_id,
868 pa.style style,
869 pa.address_type ad_type,
870 pa.country country,
871 pa.region_1 R1,
872 pa.region_2 R2,
873 pa.region_3 R3,
874 pa.town_or_city city,
875 pa.address_line1 AL1,
876 pa.address_line2 AL2,
877 pa.address_line3 AL3,
878 pa.postal_code postal_code
879 FROM per_addresses pa
880 WHERE pa.primary_flag = 'Y'
881 AND pa.person_id = p_person_id
882 AND p_effective_date BETWEEN pa.date_from AND NVL(pa.date_to,to_date('31-12-4712','DD-MM-YYYY'));
883
884 -------------
885 /* Cursor to retrieve Employer's Address */
886 CURSOR csr_employer_address(p_organization_id NUMBER) IS
887 SELECT hla.style style
888 ,hla.country country
889 ,hla.address_line_1 AL1
890 ,hla.address_line_2 AL2
891 ,hla.address_line_3 AL3
892 ,hla.postal_code postal_code
893 FROM hr_locations_all hla
894 ,hr_organization_units hou
895 WHERE hou.organization_id = p_organization_id
896 AND hou.location_id = hla.location_id;
897 -------------
898 CURSOR csr_organization_address(p_organization_id NUMBER) IS
899 SELECT hla.style style
900 ,hla.address_line_1 AL1
901 ,hla.address_line_2 AL2
902 ,hla.address_line_3 AL3
903 ,hla.country country
904 ,hla.postal_code postal_code
905 FROM hr_locations_all hla,
906 hr_organization_units hoa
907 WHERE hla.location_id = hoa.location_id
908 AND hoa.organization_id = p_organization_id
909 AND p_effective_date BETWEEN hoa.date_from AND NVL(hoa.date_to,to_date('31-12-4712','DD-MM-YYYY'));
910
911 --------------
912 /* Cursor to retrieve Business Group Id */
913 CURSOR csr_bus_grp_id(p_organization_id NUMBER) IS
914 SELECT business_group_id
915 FROM hr_organization_units
916 WHERE organization_id = p_organization_id;
917 --------------
918 /* Cursor to retrieve Currency */
919 CURSOR csr_currency(p_bg_id NUMBER) IS
920 SELECT org_information10
921 FROM hr_organization_information
922 WHERE organization_id = p_bg_id
923 AND org_information_context = 'Business Group Information';
924
925 --------------
926 l_bg_id NUMBER;
927 --------------
928
929 /*
930 CURSOR csr_legal_employer (p_organization_id NUMBER) IS
931 SELECT hoi3.organization_id
932 FROM HR_ORGANIZATION_UNITS o1
933 , HR_ORGANIZATION_INFORMATION hoi1
934 , HR_ORGANIZATION_INFORMATION hoi2
935 , HR_ORGANIZATION_INFORMATION hoi3
936 WHERE o1.business_group_id =l_bg_id
937 AND hoi1.organization_id = o1.organization_id
938 AND hoi1.organization_id = p_organization_id
939 AND hoi1.org_information1 = 'DK_LOCAL_UNIT'
940 AND hoi1.org_information_context = 'CLASS'
941 AND o1.organization_id = hoi2.org_information1
942 AND hoi2.ORG_INFORMATION_CONTEXT='DK_LOCAL_UNITS'
943 AND hoi2.organization_id = hoi3.organization_id
944 AND hoi3.ORG_INFORMATION_CONTEXT='CLASS'
945 AND hoi3.org_information1 = 'HR_LEGAL_EMPLOYER';
946 */
947
948 -------------
949 /* Cursor to retrieve Grade of Employee */
950 CURSOR csr_grade(p_grade_id NUMBER) IS
951 SELECT pg.name
952 FROM per_grades pg
953 WHERE pg.grade_id = p_grade_id;
954 -------------
955 /* Cursor to retrieve Position of Employee */
956 CURSOR csr_position(p_position_id NUMBER) IS
957 SELECT pap.name
958 FROM per_all_positions pap
959 WHERE pap.position_id = p_position_id;
960 -------------
961 CURSOR csr_job (p_job_id NUMBER)IS
962 SELECT name
963 FROM per_jobs
964 WHERE job_id = p_job_id;
965 -------------
966 /* Cursor to retrieve Cost Center */
967 CURSOR csr_cost_center(p_assignment_id NUMBER) IS
968 SELECT concatenated_segments
969 FROM pay_cost_allocations_v
970 WHERE assignment_id=p_assignment_id
971 AND p_date_earned BETWEEN effective_start_date AND effective_end_date;
972 -------------
973 /* Cursor to pick up Payroll Location */
974 CURSOR csr_pay_location(p_location_id NUMBER) IS
975 SELECT location_code location
976 FROM hr_locations_all
977 WHERE location_id = p_location_id;
978 -------------
979 /* Cursor to pick Hire Date*/
980 CURSOR csr_hire_date (p_assignment_id NUMBER) IS
981 SELECT date_start
982 FROM per_periods_of_service pps,
983 per_all_assignments_f paa
984 WHERE pps.period_of_service_id = paa.period_of_service_id
985 AND p_date_earned between paa.effective_start_date and paa.effective_end_date
986 AND paa.assignment_id = p_assignment_id;
987 -------------
988 /*Cursor to pick local unit*/
989 /*
990 cursor csr_scl_details (p_assignment_id NUMBER) IS
991 SELECT segment2
992 from per_all_assignments_f paaf
993 ,HR_SOFT_CODING_KEYFLEX hsck
994 where paaf.assignment_id= p_assignment_id
995 and p_date_earned BETWEEN paaf.effective_start_date and paaf.effective_end_date
996 and paaf.SOFT_CODING_KEYFLEX_ID = hsck.SOFT_CODING_KEYFLEX_ID;
997 */
998
999 /*Cursor to pick Legal Employer */
1000
1001 cursor csr_scl_details (p_assignment_id NUMBER) IS
1002 SELECT segment1
1003 from per_all_assignments_f paaf
1004 ,HR_SOFT_CODING_KEYFLEX hsck
1005 where paaf.assignment_id= p_assignment_id
1006 and p_date_earned BETWEEN paaf.effective_start_date and paaf.effective_end_date
1007 and paaf.SOFT_CODING_KEYFLEX_ID = hsck.SOFT_CODING_KEYFLEX_ID;
1008
1009 --------------
1010
1011 /*Cursor to pick Legal Employer Phone Numbers*/
1012
1013 CURSOR csr_le_phone (l_legal_employer_id NUMBER) IS
1014 SELECT org_information3 le_phone_num
1015 FROM hr_organization_information hoi
1016 WHERE organization_id = l_legal_employer_id
1017 AND org_information_context = 'ORG_CONTACT_DETAILS'
1018 AND org_information1 = 'PHONE' ;
1019
1020 --------------
1021
1022 /*Cursor to pick Legal Employer CVR Number */
1023
1024 CURSOR csr_le_cvr (l_legal_employer_id NUMBER) IS
1025 SELECT ORG_INFORMATION1
1026 FROM hr_organization_information hoi
1027 WHERE organization_id = l_legal_employer_id
1028 AND org_information_context = 'DK_LEGAL_ENTITY_DETAILS' ;
1029
1030 --------------
1031
1032 /* Cursor added to fetch the organization name */
1033
1034 CURSOR csr_org_name (org_id NUMBER) IS
1035 SELECT name
1036 FROM hr_organization_units
1037 WHERE organization_id = org_id ;
1038
1039 -------------
1040 rec_person_details csr_person_details%ROWTYPE;
1041 rec_primary_address csr_primary_address%ROWTYPE;
1042 rec_employer_address csr_employer_address%ROWTYPE;
1043 rec_org_address csr_organization_address%ROWTYPE;
1044 l_nationality per_all_people_f.nationality%TYPE;
1045 l_position per_all_positions.name%TYPE;
1046 l_hire_date per_periods_of_service.date_start%TYPE;
1047 l_grade per_grades.name%TYPE;
1048 l_currency hr_organization_information.org_information10%TYPE;
1049 l_organization hr_organization_units.name%TYPE;
1050 l_pay_location hr_locations_all.address_line_1%TYPE;
1051 l_postal_code VARCHAR2(80);
1052 l_country VARCHAR2(30);
1053 l_emp_postal_code VARCHAR2(80);
1054 l_emp_country VARCHAR2(30);
1055 l_org_city VARCHAR2(20);
1056 l_org_country VARCHAR2(30);
1057 l_action_info_id NUMBER;
1058 l_ovn NUMBER;
1059 l_person_id NUMBER;
1060 l_employer_name hr_organization_units.name%TYPE;
1061 l_local_unit_id hr_organization_units.organization_id%TYPE;
1062 l_legal_employer_id hr_organization_units.organization_id%TYPE;
1063 l_job PER_JOBS.NAME%TYPE;
1064 l_org_struct_ver_id hr_organization_information.org_information1%TYPE;
1065 l_top_org_id per_org_structure_elements.organization_id_parent%TYPE;
1066 l_cost_center pay_cost_allocations_v.concatenated_segments%TYPE;
1067 l_defined_balance_id NUMBER;
1068 l_balance_value NUMBER;
1069 l_formatted_value VARCHAR2(50) := NULL;
1070 l_org_exists NUMBER :=0;
1071 le_phone_num VARCHAR2(240);
1072 le_phone_num_str VARCHAR2(1000);
1073 l_cvr_num VARCHAR2(240);
1074 -- l_lower_base NUMBER :=0;
1075 -- l_upper_base NUMBER :=0;
1076 -------------
1077
1078 BEGIN
1079
1080 IF g_debug THEN
1081 hr_utility.set_location(' Entering Procedure ARCHIVE_EMPLOYEE_DETAILS',160);
1082 END IF;
1083
1084 /*fnd_file.put_line(fnd_file.log,'Entering Procedure ARCHIVE_EMPLOYEE_DETAILS');*/
1085
1086 /* PERSON AND ADDRESS DETAILS */
1087 OPEN csr_person_details(p_assignment_id);
1088 FETCH csr_person_details INTO rec_person_details;
1089 CLOSE csr_person_details;
1090
1091 OPEN csr_primary_address(rec_person_details.person_id);
1092 FETCH csr_primary_address INTO rec_primary_address;
1093 CLOSE csr_primary_address;
1094
1095 -- rec_person_details.org_id is the org_id of the HR org at asg level
1096
1097 OPEN csr_organization_address(rec_person_details.org_id);
1098 FETCH csr_organization_address INTO rec_org_address;
1099 CLOSE csr_organization_address;
1100
1101 /*fnd_file.put_line(fnd_file.log,'Entering Procedure ARCHIVE_EMPLOYEE_DETAILS 2');*/
1102
1103 /* GRADE AND POSITION */
1104
1105 /* Changed IF condition construct to fix Bug 3583862 */
1106 IF(rec_person_details.pos_id IS NOT NULL) THEN
1107 OPEN csr_position(rec_person_details.pos_id);
1108 FETCH csr_position INTO l_position;
1109 CLOSE csr_position;
1110 END IF;
1111
1112 IF(rec_person_details.grade_id IS NOT NULL) THEN
1113 OPEN csr_grade(rec_person_details.grade_id);
1114 FETCH csr_grade INTO l_grade;
1115 CLOSE csr_grade;
1116 END IF;
1117
1118 /*fnd_file.put_line(fnd_file.log,'Entering Procedure ARCHIVE_EMPLOYEE_DETAILS 3');*/
1119
1120 /* CURRENCY */
1121
1122
1123 -- rec_person_details.org_id is the org_id of the HR org at asg level
1124 OPEN csr_bus_grp_id(rec_person_details.org_id);
1125 FETCH csr_bus_grp_id INTO l_bg_id;
1126 CLOSE csr_bus_grp_id;
1127
1128 OPEN csr_currency(l_bg_id);
1129 FETCH csr_currency INTO l_currency;
1130 CLOSE csr_currency;
1131
1132 g_format_mask := FND_CURRENCY.GET_FORMAT_MASK(l_currency,40);
1133
1134 /* COST CENTER */
1135 OPEN csr_cost_center(p_assignment_id);
1136 FETCH csr_cost_center INTO l_cost_center;
1137 CLOSE csr_cost_center;
1138
1139
1140 /* HIRE DATE */
1141 OPEN csr_hire_date(p_assignment_id);
1142 FETCH csr_hire_date INTO l_hire_date;
1143 CLOSE csr_hire_date;
1144
1145 /*NATIONALITY*/
1146 l_nationality := hr_general.decode_lookup('NATIONALITY',rec_person_details.nationality);
1147
1148 /*fnd_file.put_line(fnd_file.log,'Entering Procedure ARCHIVE_EMPLOYEE_DETAILS 4');*/
1149
1150 /*Local Unit*/
1151 /*
1152 OPEN csr_scl_details(p_assignment_id);
1153 FETCH csr_scl_details INTO l_local_unit_id;
1154 CLOSE csr_scl_details;
1155 */
1156
1157 /*
1158 OPEN csr_legal_employer(l_local_unit_id);
1159 FETCH csr_legal_employer INTO l_legal_employer_id;
1160 CLOSE csr_legal_employer;
1161 */
1162
1163 /*Legal Employer */
1164
1165 OPEN csr_scl_details(p_assignment_id);
1166 FETCH csr_scl_details INTO l_legal_employer_id ;
1167 CLOSE csr_scl_details;
1168
1169
1170 OPEN csr_employer_address(l_legal_employer_id);
1171 FETCH csr_employer_address INTO rec_employer_address;
1172 CLOSE csr_employer_address;
1173
1174 IF(rec_person_details.loc_id IS NOT NULL) THEN
1175 l_pay_location := NULL;
1176
1177 OPEN csr_pay_location(rec_person_details.loc_id);
1178 FETCH csr_pay_location INTO l_pay_location;
1179 CLOSE csr_pay_location;
1180 ELSE
1181 l_pay_location := NULL;
1182 END IF;
1183
1184
1185 IF(rec_person_details.job_id IS NOT NULL) THEN
1186
1187 OPEN csr_job(rec_person_details.job_id);
1188 FETCH csr_job INTO l_job;
1189 CLOSE csr_job;
1190 ELSE
1191 l_job := NULL;
1192 END IF;
1193
1194 /*
1195 -- HR ORG at asg level Name
1196 SELECT name INTO l_organization
1197 FROM hr_organization_units
1198 WHERE organization_id = rec_person_details.org_id;
1199
1200 -- Legal Employer Name
1201 SELECT name INTO l_employer_name
1202 FROM hr_organization_units
1203 WHERE organization_id = l_legal_employer_id;
1204 */
1205
1206 -- HR ORG at asg level Name
1207 OPEN csr_org_name (rec_person_details.org_id) ;
1208 FETCH csr_org_name INTO l_organization ;
1209 CLOSE csr_org_name ;
1210
1211 -- Legal Employer Name
1212 OPEN csr_org_name (l_legal_employer_id) ;
1213 FETCH csr_org_name INTO l_employer_name ;
1214 CLOSE csr_org_name ;
1215
1216
1217 /*fnd_file.put_line(fnd_file.log,'Entering Procedure ARCHIVE_EMPLOYEE_DETAILS 5');*/
1218
1219 IF rec_primary_address.style = 'DK' THEN
1220 l_postal_code := hr_general.decode_lookup('DK_POSTCODE_TOWN',rec_primary_address.postal_code);
1221 ELSE
1222 l_postal_code := rec_primary_address.postal_code;
1223 END IF;
1224
1225 l_country := PAY_DK_ARCHIVE.get_country_name(rec_primary_address.country);
1226
1227 IF rec_employer_address.style = 'DK' THEN
1228 l_emp_postal_code := hr_general.decode_lookup('DK_POSTCODE_TOWN',rec_employer_address.postal_code);
1229 ELSE
1230 l_emp_postal_code := rec_employer_address.postal_code;
1231 END IF;
1232
1233 l_emp_country := PAY_DK_ARCHIVE.get_country_name(rec_employer_address.country);
1234
1235 /* Getting Legal Employer Phone Number String */
1236
1237 le_phone_num_str := NULL;
1238
1239 OPEN csr_le_phone (l_legal_employer_id);
1240 LOOP
1241 FETCH csr_le_phone INTO le_phone_num;
1242 EXIT WHEN csr_le_phone%NOTFOUND;
1243
1244 IF (csr_le_phone%ROWCOUNT = 1)
1245 THEN
1246 le_phone_num_str := le_phone_num ;
1247 ELSE
1248 le_phone_num_str := le_phone_num_str || ' , ' || le_phone_num ;
1249 END IF;
1250
1251 END LOOP;
1252 CLOSE csr_le_phone;
1253
1254 /* Get Legal Employer CVR Number */
1255
1256 OPEN csr_le_cvr (l_legal_employer_id ) ;
1257 FETCH csr_le_cvr INTO l_cvr_num ;
1258 CLOSE csr_le_cvr ;
1259
1260
1261 /*fnd_file.put_line(fnd_file.log,'Entering Procedure ARCHIVE_EMPLOYEE_DETAILS gosi reference');*/
1262
1263 /* INSERT PERSON DETAILS */
1264
1265 pay_action_information_api.create_action_information (
1266 p_action_information_id => l_action_info_id
1267 ,p_action_context_id => p_archive_assact_id
1268 ,p_action_context_type => 'AAP'
1269 ,p_object_version_number => l_ovn
1270 ,p_effective_date => p_effective_date
1271 ,p_source_id => NULL
1272 ,p_source_text => NULL
1273 ,p_action_information_category => 'EMPLOYEE DETAILS'
1274 ,p_action_information1 => rec_person_details.full_name
1275 ,p_action_information2 => l_legal_employer_id -- rec_person_details.org_id -- org_id of HR Org at asg level
1276 ,p_action_information4 => rec_person_details.ni_number
1277 ,p_action_information7 => l_grade
1278 ,p_action_information10 => rec_person_details.emp_num
1279 ,p_action_information12 => fnd_date.date_to_displaydate(l_hire_date) -- fnd_date.date_to_canonical(l_hire_date)
1280 ,p_action_information15 => l_organization -- name of HR Org at asg level
1281 ,p_action_information16 => p_time_period_id
1282 ,p_action_information17 => l_job
1283 ,p_action_information18 => l_employer_name -- Legal Employer Name
1284 ,p_action_information19 => l_position
1285 ,p_action_information25 => le_phone_num_str
1286 ,p_action_information30 => l_pay_location
1287 ,p_assignment_id => p_assignment_id);
1288
1289
1290 /* INSERT ADDRESS DETAILS */
1291 IF rec_primary_address.AL1 IS NOT NULL THEN /* CHECK IF EMPLOYEE HAS BEEN GIVEN A PRIMARY ADDRESS */
1292 pay_action_information_api.create_action_information (
1293 p_action_information_id => l_action_info_id
1294 ,p_action_context_id => p_archive_assact_id
1295 ,p_action_context_type => 'AAP'
1296 ,p_object_version_number => l_ovn
1297 ,p_effective_date => p_effective_date
1298 ,p_source_id => NULL
1299 ,p_source_text => NULL
1300 ,p_action_information_category => 'ADDRESS DETAILS'
1301 ,p_action_information1 => rec_primary_address.person_id
1302 ,p_action_information5 => rec_primary_address.AL1
1303 ,p_action_information6 => rec_primary_address.AL2
1304 ,p_action_information7 => rec_primary_address.AL3
1305 ,p_action_information12 => l_postal_code
1306 ,p_action_information13 => l_country
1307 ,p_action_information14 => 'Employee Address'
1308 ,p_assignment_id => p_assignment_id);
1309 ELSE
1310 /* INSERT EMPLOYER ADDRESS AS EMPLOYEE'S PRIMARY ADDRESS */
1311 pay_action_information_api.create_action_information (
1312 p_action_information_id => l_action_info_id
1313 ,p_action_context_id => p_archive_assact_id
1314 ,p_action_context_type => 'AAP'
1315 ,p_object_version_number => l_ovn
1316 ,p_effective_date => p_effective_date
1317 ,p_source_id => NULL
1318 ,p_source_text => NULL
1319 ,p_action_information_category => 'ADDRESS DETAILS'
1320 ,p_action_information1 => rec_primary_address.person_id
1321 ,p_action_information5 => NULL
1322 ,p_action_information6 => NULL
1323 ,p_action_information7 => NULL
1324 ,p_action_information8 => NULL
1325 ,p_action_information9 => NULL
1326 ,p_action_information10 => NULL
1327 ,p_action_information11 => NULL
1328 ,p_action_information12 => NULL
1329 ,p_action_information13 => NULL
1330 ,p_action_information14 => 'Employee Address'
1331 ,p_assignment_id => p_assignment_id);
1332 END IF;
1333
1334 /*fnd_file.put_line(fnd_file.log,'Entering Procedure ARCHIVE_EMPLOYEE_DETAILS 9');*/
1335
1336 /* INSERT EMPLOYER'S ADDRESS (ORGANIZATION ADDRESS)*/
1337
1338 BEGIN
1339 l_org_exists := 0;
1340
1341 SELECT 1
1342 INTO l_org_exists
1343 FROM pay_action_information
1344 WHERE action_context_id = p_payroll_action_id
1345 AND action_information1 = l_legal_employer_id -- rec_person_details.org_id
1346 AND effective_date = p_effective_date
1347 AND action_information_category = 'ADDRESS DETAILS';
1348
1349 EXCEPTION
1350
1351 WHEN NO_DATA_FOUND THEN
1352 pay_action_information_api.create_action_information (
1353 p_action_information_id => l_action_info_id
1354 ,p_action_context_id => p_payroll_action_id
1355 ,p_action_context_type => 'PA'
1356 ,p_object_version_number => l_ovn
1357 ,p_effective_date => p_effective_date
1358 ,p_source_id => NULL
1359 ,p_source_text => NULL
1360 ,p_action_information_category => 'ADDRESS DETAILS'
1361 ,p_action_information1 => l_legal_employer_id -- rec_person_details.org_id
1362 ,p_action_information5 => rec_employer_address.AL1
1363 ,p_action_information6 => rec_employer_address.AL2
1364 ,p_action_information7 => rec_employer_address.AL3
1365 ,p_action_information12 => l_emp_postal_code
1366 ,p_action_information13 => l_emp_country
1367 ,p_action_information14 => 'Employer Address'
1368 ,p_action_information26 => l_cvr_num ); -- using Localization Specific1 for Legal Employer CVR Number
1369
1370 WHEN OTHERS THEN
1371 NULL;
1372 END;
1373
1374 /*fnd_file.put_line(fnd_file.log,'Entering Procedure ARCHIVE_EMPLOYEE_DETAILS 10');*/
1375
1376 --
1377 IF g_debug THEN
1378 hr_utility.set_location(' Leaving Procedure ARCHIVE_EMPLOYEE_DETAILS',170);
1379 END IF;
1380 --
1381
1382 EXCEPTION WHEN OTHERS THEN
1383 g_err_num := SQLCODE;
1384 /*fnd_file.put_line(fnd_file.log,'ORA_ERR: ' || g_err_num || 'In ARCHIVE_EMPLOYEE_DETAILS');*/
1385
1386 IF g_debug THEN
1387 hr_utility.set_location('ORA_ERR: ' || g_err_num || 'In ARCHIVE_EMPLOYEE_DETAILS',180);
1388 END IF;
1389
1390 END ARCHIVE_EMPLOYEE_DETAILS;
1391
1392 ----------------------------------- PROCEDURE ARCHIVE_ELEMENT_INFO ---------------------------------------------------------------
1393
1394 /* EARNINGS REGION, DEDUCTIONS REGION */
1395
1396 PROCEDURE ARCHIVE_ELEMENT_INFO(p_payroll_action_id IN NUMBER
1397 ,p_effective_date IN DATE
1398 ,p_date_earned IN DATE
1399 ,p_pre_payact_id IN NUMBER)
1400 IS
1401 ----------------
1402
1403 /* Cursor to retrieve Earnings Element Information */
1404
1405 CURSOR csr_ear_element_info IS
1406 SELECT nvl(pettl.reporting_name,pettl.element_name) rep_name
1407 ,et.element_type_id element_type_id
1408 ,iv.input_value_id input_value_id
1409 ,iv.uom uom
1410 FROM pay_element_types_f et
1411 , pay_element_types_f_tl pettl
1412 , pay_input_values_f iv
1413 , pay_element_classifications classification
1414 WHERE et.element_type_id = iv.element_type_id
1415 AND et.element_type_id = pettl.element_type_id
1416 AND pettl.language = USERENV('LANG')
1417 AND iv.name = 'Pay Value'
1418 AND classification.classification_id = et.classification_id
1419 AND classification.classification_name
1420 IN ('Direct Payments','Income','Special Pay')
1421 AND p_date_earned BETWEEN et.effective_start_date AND et.effective_end_date
1422 AND p_date_earned BETWEEN iv.effective_start_date AND iv.effective_end_date
1423 AND ((et.business_group_id IS NULL AND et.legislation_code = 'DK')
1424 OR (et.business_group_id = g_business_group_id AND et.legislation_code IS NULL));
1425
1426 -----------------
1427
1428 /* Cursor to retrieve Deduction Element Information */
1429 CURSOR csr_ded_element_info IS
1430 SELECT nvl(pettl.reporting_name,pettl.element_name) rep_name
1431 ,et.element_type_id element_type_id
1432 ,iv.input_value_id input_value_id
1433 ,iv.uom uom
1434 FROM pay_element_types_f et
1435 , pay_element_types_f_tl pettl
1436 , pay_input_values_f iv
1437 , pay_element_classifications classification
1438 WHERE et.element_type_id = iv.element_type_id
1439 AND et.element_type_id = pettl.element_type_id
1440 AND pettl.language = USERENV('LANG')
1441 AND iv.name = 'Pay Value'
1442 AND classification.classification_id = et.classification_id
1443 AND classification.classification_name
1444 IN ('Involuntary Deductions','Pre-Tax Deductions','Statutory Deductions','Voluntary Deductions')
1445 AND p_date_earned BETWEEN et.effective_start_date AND et.effective_end_date
1446 AND p_date_earned BETWEEN iv.effective_start_date AND iv.effective_end_date
1447 AND ((et.business_group_id IS NULL AND et.legislation_code = 'DK')
1448 OR (et.business_group_id = g_business_group_id AND et.legislation_code IS NULL));
1449
1450
1451 ---------------------
1452 l_action_info_id NUMBER;
1453 l_ovn NUMBER;
1454 l_flag NUMBER := 0;
1455 ----------------------
1456 BEGIN
1457
1458
1459 IF g_debug THEN
1460 hr_utility.set_location(' Entering Procedure ARCHIVE_ELEMENT_INFO',210);
1461 END IF;
1462
1463 ------------------------
1464 /* EARNINGS ELEMENT */
1465
1466 FOR rec_earnings IN csr_ear_element_info LOOP
1467
1468 BEGIN
1469 hr_utility.trace('p_payroll_action_id***************** '||p_payroll_action_id);
1470 -- check if the Element definition has already been archived
1471 SELECT 1 INTO l_flag
1472 FROM pay_action_information
1473 WHERE action_context_id = p_payroll_action_id
1474 AND action_information_category = 'EMEA ELEMENT DEFINITION'
1475 AND action_information2 = rec_earnings.element_type_id
1476 AND action_information3 = rec_earnings.input_value_id
1477 AND action_information5 = 'E';
1478
1479
1480
1481 EXCEPTION WHEN NO_DATA_FOUND THEN
1482 -- archive the element definition as it has not been archived
1483 hr_utility.trace(' rec_earnings.element_type_id ******** '||rec_earnings.element_type_id);
1484
1485 pay_action_information_api.create_action_information (
1486 p_action_information_id => l_action_info_id
1487 ,p_action_context_id => p_payroll_action_id
1488 ,p_action_context_type => 'PA'
1489 ,p_object_version_number => l_ovn
1490 ,p_effective_date => p_effective_date
1491 ,p_source_id => NULL
1492 ,p_source_text => NULL
1493 ,p_action_information_category => 'EMEA ELEMENT DEFINITION'
1494 ,p_action_information1 => p_pre_payact_id
1495 ,p_action_information2 => rec_earnings.element_type_id
1496 ,p_action_information3 => rec_earnings.input_value_id
1497 ,p_action_information4 => rec_earnings.rep_name
1498 ,p_action_information5 => 'E'
1499 ,p_action_information6 => rec_earnings.uom
1500 ,p_action_information7 => 'E'
1501 ); --Earnings Element Context
1502 WHEN OTHERS THEN
1503 NULL;
1504 END;
1505 END LOOP;
1506 ---------------------------
1507 /* DEDUCTION ELEMENT */
1508
1509 FOR rec_deduction IN csr_ded_element_info LOOP
1510 BEGIN
1511 -- check if the Element definition has already been archived
1512 SELECT 1 INTO l_flag
1513 FROM pay_action_information
1514 WHERE action_context_id = p_payroll_action_id
1515 AND action_information_category = 'EMEA ELEMENT DEFINITION'
1516 AND action_information2 = rec_deduction.element_type_id
1517 AND action_information3 = rec_deduction.input_value_id
1518 AND action_information5 = 'D';
1519
1520 EXCEPTION WHEN NO_DATA_FOUND THEN
1521 -- archive the element definition as it has not been archived
1522 pay_action_information_api.create_action_information (
1523 p_action_information_id => l_action_info_id
1524 ,p_action_context_id => p_payroll_action_id
1525 ,p_action_context_type => 'PA'
1526 ,p_object_version_number => l_ovn
1527 ,p_effective_date => p_effective_date
1528 ,p_source_id => NULL
1529 ,p_source_text => NULL
1530 ,p_action_information_category => 'EMEA ELEMENT DEFINITION'
1531 ,p_action_information1 => p_pre_payact_id
1532 ,p_action_information2 => rec_deduction.element_type_id
1533 ,p_action_information3 => rec_deduction.input_value_id
1534 ,p_action_information4 => rec_deduction.rep_name
1535 ,p_action_information5 => 'D'
1536 ,p_action_information6 => rec_deduction.uom
1537 ,p_action_information7 => 'D'
1538 ); --Deduction Element Context
1539
1540 /*WHEN OTHERS THEN
1541 NULL;*/
1542 END;
1543 END LOOP;
1544
1545 IF g_debug THEN
1546 hr_utility.set_location(' Leaving Procedure ARCHIVE_ELEMENT_INFO',220);
1547 END IF;
1548
1549 EXCEPTION WHEN OTHERS THEN
1550 g_err_num := SQLCODE;
1551 /*fnd_file.put_line(fnd_file.log,'ORA_ERR: ' || g_err_num || 'In ARCHIVE_ELEMENT_INFO');*/
1552
1553 IF g_debug THEN
1554 hr_utility.set_location('ORA_ERR: ' || g_err_num || 'ARCHIVE_ELEMENT_INFO',230);
1555 END IF;
1556
1557 END ARCHIVE_ELEMENT_INFO;
1558
1559
1560 ------------------------------------ FUNCTION GET_DEFINED_BALANCE_ID --------------------------------------------------------------
1561
1562 /* GET DEFINED BALANCE ID */
1563
1564 FUNCTION GET_DEFINED_BALANCE_ID(p_user_name IN VARCHAR2) RETURN NUMBER
1565 IS
1566
1567 /* Cursor to retrieve Defined Balance Id */
1568
1569 CURSOR csr_def_bal_id(p_user_name VARCHAR2) IS
1570 SELECT u.creator_id
1571 FROM ff_user_entities u,
1572 ff_database_items d
1573 WHERE d.user_name = p_user_name
1574 AND u.user_entity_id = d.user_entity_id
1575 AND (u.legislation_code = 'DK' )
1576 AND (u.business_group_id IS NULL )
1577 AND u.creator_type = 'B';
1578
1579 l_defined_balance_id ff_user_entities.user_entity_id%TYPE;
1580
1581 BEGIN
1582
1583 IF g_debug THEN
1584 hr_utility.set_location(' Entering Function GET_DEFINED_BALANCE_ID',240);
1585 END IF;
1586
1587 OPEN csr_def_bal_id(p_user_name);
1588 FETCH csr_def_bal_id INTO l_defined_balance_id;
1589 CLOSE csr_def_bal_id;
1590 RETURN l_defined_balance_id;
1591
1592 IF g_debug THEN
1593 hr_utility.set_location(' Leaving Function GET_DEFINED_BALANCE_ID',250);
1594 END IF;
1595
1596 END GET_DEFINED_BALANCE_ID;
1597 --------------------------------------------------------------------------------------------------
1598 /* PAYMENT INFORMATION REGION */
1599 PROCEDURE ARCHIVE_PAYMENT_INFO(p_archive_assact_id IN NUMBER,
1600 p_prepay_assact_id IN NUMBER,
1601 p_assignment_id IN NUMBER,
1602 p_date_earned IN DATE,
1603 p_effective_date IN DATE)
1604 IS
1605 -------------
1606 /* Cursor to fetch ppm and opm ids to check which payment method to archive */
1607 CURSOR csr_chk(p_prepay_assact_id NUMBER) IS
1608 SELECT personal_payment_method_id ppm_id,
1609 org_payment_method_id opm_id
1610 FROM pay_pre_payments
1611 WHERE assignment_action_id = p_prepay_assact_id;
1612
1613 ------------
1614 /* Cursor to check if bank details are attached with ppm */
1615 CURSOR csr_chk_bank(p_ppm_id NUMBER) IS
1616 SELECT ppm.external_account_id
1617 FROM pay_personal_payment_methods_f ppm
1618 WHERE ppm.personal_payment_method_id = p_ppm_id
1619 AND p_date_earned BETWEEN ppm.effective_start_date AND ppm.effective_end_date;
1620 -------------
1621 /* Cursor to retrieve Organization Payment Method Information */
1622 CURSOR csr_get_org_pay(p_prepay_assact_id NUMBER, opm_id NUMBER) IS
1623 SELECT pop.org_payment_method_id opm_id,
1624 pop.org_payment_method_name opm_name,
1625 ppttl.payment_type_name pay_type,
1626 ppp.value value
1627 FROM pay_org_payment_methods_f pop,
1628 pay_assignment_actions paa,
1629 pay_payment_types ppt,
1630 pay_payment_types_tl ppttl,
1631 pay_pre_payments ppp
1632 WHERE paa.assignment_action_id = p_prepay_assact_id
1633 AND ppt.payment_type_id = pop.payment_type_id
1634 AND ppt.payment_type_id = ppttl.payment_type_id
1635 AND ppttl.language = userenv('LANG')
1636 AND ppp.org_payment_method_id = pop.org_payment_method_id
1637 AND pop.org_payment_method_id = opm_id
1638 AND ppp.assignment_action_id = paa.assignment_action_id
1639 AND p_date_earned BETWEEN pop.effective_start_date AND pop.effective_end_date;
1640 -------------
1641 /* Cursor to retrieve Personal Payment Method Info*/
1642 CURSOR csr_get_pers_pay(p_prepay_assact_id NUMBER, ppm_id NUMBER) IS
1643 SELECT pea.segment1 bank_reg_num,
1644 pea.segment2 branch,
1645 pea.segment3 acct_num,
1646 ppm.org_payment_method_id opm_id,
1647 pop.external_account_id,
1648 pop.org_payment_method_name opm_name,
1649 ppm.personal_payment_method_id ppm_id,
1650 ppttl.payment_type_name pay_type,
1651 ppp.value value
1652 FROM pay_external_accounts pea,
1653 pay_org_payment_methods_f pop,
1654 pay_personal_payment_methods_f ppm,
1655 pay_assignment_actions paa,
1656 pay_payment_types ppt,
1657 pay_payment_types_tl ppttl,
1658 pay_pre_payments ppp
1659 WHERE
1660 -- pea.id_flex_num=20 AND
1661 pea.external_account_id = NVL(ppm.external_account_id,pop.external_account_id)
1662 AND paa.assignment_action_id = p_prepay_assact_id
1663 AND paa.assignment_id = ppm.assignment_id
1664 AND ppm.org_payment_method_id = pop.org_payment_method_id
1665 AND ppm.personal_payment_method_id = ppm_id
1666 AND ppt.payment_type_id = pop.payment_type_id
1667 AND ppt.payment_type_id = ppttl.payment_type_id
1668 AND ppttl.language = userenv('LANG')
1669 AND ppp.assignment_action_id = paa.assignment_action_id
1670 AND ppp.personal_payment_method_id = ppm.personal_payment_method_id
1671 AND p_date_earned BETWEEN pop.effective_start_date AND pop.effective_end_date
1672 AND p_date_earned BETWEEN ppm.effective_start_date AND ppm.effective_end_date;
1673 -------------
1674 l_bank_reg_num VARCHAR2(50);
1675 l_action_info_id NUMBER;
1676 l_ovn NUMBER;
1677 l_org NUMBER;
1678 l_pers VARCHAR2(40) := NULL;
1679 l_ext_acct NUMBER;
1680 rec_chk csr_chk%ROWTYPE;
1681 l_pay_value VARCHAR2(50) := NULL;
1682 ------------
1683
1684 BEGIN
1685
1686 IF g_debug THEN
1687 hr_utility.set_location(' Entering Procedure ARCHIVE_PAYMENT_INFO',260);
1688 END IF;
1689
1690 /*fnd_file.put_line(fnd_file.log,'In ARCHIVE_PAYMENT_INFO 1');*/
1691
1692 OPEN csr_chk(p_prepay_assact_id);
1693 LOOP
1694 FETCH csr_chk INTO rec_chk;
1695 EXIT WHEN csr_chk%NOTFOUND;
1696
1697 IF rec_chk.ppm_id IS NOT NULL THEN
1698 FOR rec_pers_pay IN csr_get_pers_pay(p_prepay_assact_id,rec_chk.ppm_id) LOOP
1699
1700 OPEN csr_chk_bank(rec_chk.ppm_id);
1701 FETCH csr_chk_bank INTO l_ext_acct;
1702 CLOSE csr_chk_bank;
1703
1704 l_pay_value := to_char (rec_pers_pay.value,g_format_mask);
1705
1706 IF (l_ext_acct IS NOT NULL) THEN
1707
1708 /*fnd_file.put_line(fnd_file.log,'In ARCHIVE_PAYMENT_INFO 2');*/
1709
1710 --l_bank_reg_num := rec_pers_pay.bank_reg_num;
1711 l_bank_reg_num := rec_pers_pay.bank_reg_num || ' ' ||
1712 hr_general.decode_lookup('HR_DK_BANK_REGISTRATION',rec_pers_pay.bank_reg_num);
1713
1714 pay_action_information_api.create_action_information (
1715 p_action_information_id => l_action_info_id
1716 ,p_action_context_id => p_archive_assact_id
1717 ,p_action_context_type => 'AAP'
1718 ,p_object_version_number => l_ovn
1719 ,p_effective_date => p_effective_date
1720 ,p_source_id => NULL
1721 ,p_source_text => NULL
1722 ,p_action_information_category => 'EMPLOYEE NET PAY DISTRIBUTION'
1723 ,p_action_information1 => rec_pers_pay.opm_id -- NULL
1724 ,p_action_information2 => rec_pers_pay.ppm_id
1725 ,p_action_information5 => l_bank_reg_num
1726 ,p_action_information6 => rec_pers_pay.branch
1727 ,p_action_information7 => rec_pers_pay.acct_num -- NULL
1728 ,p_action_information8 => NULL -- rec_pers_pay.acct_num
1729 ,p_action_information9 => NULL
1730 ,p_action_information10 => NULL
1731 ,p_action_information11 => NULL
1732 ,p_action_information12 => NULL
1733 ,p_action_information13 => NULL
1734 ,p_action_information14 => NULL
1735 ,p_action_information15 => NULL
1736 ,p_action_information16 => fnd_number.number_to_canonical(rec_pers_pay.value) --l_pay_value
1737 ,p_action_information17 => NULL
1738 ,p_action_information18 => rec_pers_pay.opm_name -- rec_pers_pay.pay_type
1739 ,p_assignment_id => p_assignment_id);
1740 ELSE
1741
1742 /*fnd_file.put_line(fnd_file.log,'In ARCHIVE_PAYMENT_INFO 3');*/
1743
1744 pay_action_information_api.create_action_information (
1745 p_action_information_id => l_action_info_id
1746 ,p_action_context_id => p_archive_assact_id
1747 ,p_action_context_type => 'AAP'
1748 ,p_object_version_number => l_ovn
1749 ,p_effective_date => p_effective_date
1750 ,p_source_id => NULL
1751 ,p_source_text => NULL
1752 ,p_action_information_category => 'EMPLOYEE NET PAY DISTRIBUTION'
1753 ,p_action_information1 => rec_pers_pay.opm_id
1754 ,p_action_information2 => rec_pers_pay.ppm_id
1755 ,p_action_information5 => NULL
1756 ,p_action_information6 => NULL
1757 ,p_action_information7 => NULL
1758 ,p_action_information8 => NULL
1759 ,p_action_information9 => NULL
1760 ,p_action_information10 => NULL
1761 ,p_action_information11 => NULL
1762 ,p_action_information12 => NULL
1763 ,p_action_information13 => NULL
1764 ,p_action_information14 => NULL
1765 ,p_action_information15 => NULL
1766 ,p_action_information16 => fnd_number.number_to_canonical(rec_pers_pay.value) --l_pay_value
1767 ,p_action_information17 => NULL
1768 ,p_action_information18 => rec_pers_pay.opm_name -- rec_pers_pay.pay_type
1769 ,p_assignment_id => p_assignment_id);
1770 END IF;
1771 END LOOP;
1772 /*fnd_file.put_line(fnd_file.log,'In ARCHIVE_PAYMENT_INFO 4');*/
1773
1774 END IF;
1775
1776 IF (rec_chk.opm_id IS NOT NULL AND rec_chk.ppm_id IS NULL) THEN
1777
1778 /*fnd_file.put_line(fnd_file.log,'In ARCHIVE_PAYMENT_INFO 5');*/
1779
1780 FOR rec_org_pay IN csr_get_org_pay(p_prepay_assact_id,rec_chk.opm_id) LOOP
1781
1782 l_pay_value := to_char (rec_org_pay.value,g_format_mask);
1783
1784 pay_action_information_api.create_action_information (
1785 p_action_information_id => l_action_info_id
1786 ,p_action_context_id => p_archive_assact_id
1787 ,p_action_context_type => 'AAP'
1788 ,p_object_version_number => l_ovn
1789 ,p_effective_date => p_effective_date
1790 ,p_source_id => NULL
1791 ,p_source_text => NULL
1792 ,p_action_information_category => 'EMPLOYEE NET PAY DISTRIBUTION'
1793 ,p_action_information1 => rec_org_pay.opm_id
1794 ,p_action_information2 => NULL
1795 ,p_action_information5 => NULL
1796 ,p_action_information6 => NULL
1797 ,p_action_information7 => NULL
1798 ,p_action_information8 => NULL
1799 ,p_action_information9 => NULL
1800 ,p_action_information10 => NULL
1801 ,p_action_information11 => NULL
1802 ,p_action_information12 => NULL
1803 ,p_action_information13 => NULL
1804 ,p_action_information14 => NULL
1805 ,p_action_information15 => NULL
1806 ,p_action_information16 => fnd_number.number_to_canonical(rec_org_pay.value) --l_pay_value
1807 ,p_action_information17 => NULL
1808 ,p_action_information18 => rec_org_pay.opm_name -- rec_org_pay.pay_type
1809 ,p_assignment_id => p_assignment_id);
1810 END LOOP;
1811
1812 END IF;
1813
1814 END LOOP;
1815 CLOSE csr_chk;
1816
1817 IF g_debug THEN
1818 hr_utility.set_location(' Leaving Procedure ARCHIVE_PAYMENT_INFO',270);
1819 END IF;
1820
1821 EXCEPTION WHEN OTHERS THEN
1822 g_err_num := SQLCODE;
1823
1824 /*fnd_file.put_line(fnd_file.log,'ORA_ERR: ' || g_err_num || 'In ARCHIVE_PAYMENT_INFO');*/
1825
1826 IF g_debug THEN
1827 hr_utility.set_location('ORA_ERR: ' || g_err_num || 'In ARCHIVE_PAYMENT_INFO',280);
1828 END IF;
1829
1830 END ARCHIVE_PAYMENT_INFO;
1831 ----------------------------------------- PROCEDURE ARCHIVE_ACCRUAL_PLAN ---------------------------------------------------------
1832
1833 /* ACCRUALS REGION */
1834
1835 /* PROCEDURE ARCHIVE_ACCRUAL_PLAN ( p_assignment_id IN NUMBER
1836 ,p_date_earned IN DATE
1837 ,p_effective_date IN DATE
1838 ,p_archive_assact_id IN NUMBER
1839 ,p_run_assignment_action_id IN NUMBER
1840 ,p_period_end_date IN DATE
1841 ,p_period_start_date IN DATE
1842 )
1843 IS
1844 --
1845 -- Cursor to get the Leave Balance Details .
1846 CURSOR csr_leave_balance
1847 IS
1848 --
1849 SELECT pap.accrual_plan_name
1850 ,hr_general_utilities.get_lookup_meaning('US_PTO_ACCRUAL',pap.accrual_category)
1851 ,pap.accrual_units_of_measure
1852 ,ppa.payroll_id
1853 ,pap.business_group_id
1854 ,pap.accrual_plan_id
1855 FROM pay_accrual_plans pap
1856 ,pay_element_types_f pet
1857 ,pay_element_links_f pel
1858 ,pay_element_entries_f pee
1859 ,pay_assignment_actions paa
1860 ,pay_payroll_actions ppa
1861 WHERE pet.element_type_id = pap.accrual_plan_element_type_id
1862 AND pel.element_type_id = pet.element_type_id
1863 AND pee.element_link_id = pel.element_link_id
1864 AND paa.assignment_id = pee.assignment_id
1865 AND ppa.payroll_action_id = paa.payroll_action_id
1866 AND ppa.action_type IN ('R','Q')
1867 AND ppa.action_status = 'C'
1868 AND ppa.date_earned BETWEEN pet.effective_start_date
1869 AND pet.effective_end_date
1870 AND ppa.date_earned BETWEEN pel.effective_start_date
1871 AND pel.effective_end_date
1872 AND ppa.date_earned BETWEEN pee.effective_start_date
1873 AND pee.effective_end_date
1874 AND paa.assignment_id = p_assignment_id
1875 AND paa.assignment_action_id = p_run_assignment_action_id;
1876 --
1877 l_action_info_id NUMBER;
1878 l_accrual_plan_id pay_accrual_plans.accrual_plan_id%type;
1879 l_accrual_plan_name pay_accrual_plans.accrual_plan_name%type;
1880 l_accrual_category pay_accrual_plans.accrual_category%type;
1881 l_accrual_uom pay_accrual_plans.accrual_units_of_measure%type;
1882 l_payroll_id pay_all_payrolls_f.payroll_id%type;
1883 l_business_group_id NUMBER;
1884 l_effective_date DATE;
1885 l_annual_leave_balance NUMBER;
1886 l_ovn NUMBER;
1887 l_leave_taken NUMBER;
1888 l_start_date DATE;
1889 l_end_date DATE;
1890 l_accrual_end_date DATE;
1891 l_accrual NUMBER;
1892 l_total_leave_taken NUMBER;
1893 l_procedure VARCHAR2(100) := g_package || '.archive_accrual_details';
1894 --
1895 BEGIN
1896 --
1897 IF g_debug THEN
1898 hr_utility.set_location(' Entering Procedure ARCHIVE_ACCRUAL_PLAN',290);
1899 END IF;
1900 OPEN csr_leave_balance;
1901 FETCH csr_leave_balance INTO
1902 l_accrual_plan_name
1903 ,l_accrual_category
1904 ,l_accrual_uom
1905 ,l_payroll_id
1906 ,l_business_group_id
1907 ,l_accrual_plan_id;
1908 IF csr_leave_balance%FOUND THEN
1909 --
1910 -- Call to get annual leave balance
1911 per_accrual_calc_functions.get_net_accrual
1912 (
1913 p_assignment_id => p_assignment_id -- number in
1914 ,p_plan_id => l_accrual_plan_id -- number in
1915 ,p_payroll_id => l_payroll_id -- number in
1916 ,p_business_group_id => l_business_group_id -- number in
1917 ,p_calculation_date => p_date_earned -- date in
1918 ,p_start_date => l_start_date -- date out
1919 ,p_end_date => l_end_date -- date out
1920 ,p_accrual_end_date => l_accrual_end_date -- date out
1921 ,p_accrual => l_accrual -- number out
1922 ,p_net_entitlement => l_annual_leave_balance -- number out
1923 );
1924 IF l_annual_leave_balance IS NULL THEN
1925 --
1926 l_annual_leave_balance := 0;
1927 --
1928 END IF;
1929 l_leave_taken := per_accrual_calc_functions.get_absence
1930 (
1931 p_assignment_id
1932 ,l_accrual_plan_id
1933 ,p_period_end_date
1934 ,p_period_start_date
1935 );
1936 l_ovn :=1;
1937 IF l_accrual_plan_name IS NOT NULL THEN
1938 --
1939 pay_action_information_api.create_action_information (
1940 p_action_information_id => l_action_info_id
1941 ,p_action_context_id => p_archive_assact_id
1942 ,p_action_context_type => 'AAP'
1943 ,p_object_version_number => l_ovn
1944 ,p_effective_date => p_effective_date
1945 ,p_source_id => NULL
1946 ,p_source_text => NULL
1947 ,p_action_information_category => 'EMPLOYEE ACCRUALS'
1948 ,p_action_information4 => l_accrual_plan_name
1949 ,p_action_information5 => fnd_number.number_to_canonical(l_leave_taken)
1950 ,p_action_information6 => fnd_number.number_to_canonical(l_annual_leave_balance)
1951 ,p_assignment_id => p_assignment_id);
1952 --
1953 END IF;
1954 --
1955 --
1956 END IF;
1957 --
1958 CLOSE csr_leave_balance;
1959 IF g_debug THEN
1960 hr_utility.set_location(' Leaving Procedure ARCHIVE_ACCRUAL_PLAN',300);
1961 END IF;
1962 --
1963 EXCEPTION
1964 WHEN OTHERS THEN
1965 IF csr_leave_balance%ISOPEN THEN
1966 --
1967 CLOSE csr_leave_balance;
1968 --
1969 END IF;
1970 --
1971 g_err_num := SQLCODE;
1972 --fnd_file.put_line(fnd_file.log,'ORA_ERR: ' || g_err_num || 'In ARCHIVE_ACCRUAL_PLAN');
1973 IF g_debug THEN
1974 hr_utility.set_location('ORA_ERR: ' || g_err_num || ' In ARCHIVE_ACCRUAL_PLAN',310);
1975 END IF;
1976 RAISE;
1977 END ARCHIVE_ACCRUAL_PLAN;*/
1978
1979 ----------------------------------- PROCEDURE ARCHIVE_ADD_ELEMENT ---------------------------------------------------------------
1980
1981 /* ADDITIONAL ELEMENTS REGION */
1982
1983 PROCEDURE ARCHIVE_ADD_ELEMENT(p_archive_assact_id IN NUMBER,
1984 p_assignment_action_id IN NUMBER,
1985 p_assignment_id IN NUMBER,
1986 p_payroll_action_id IN NUMBER,
1987 p_date_earned IN DATE,
1988 p_effective_date IN DATE,
1989 p_pre_payact_id IN NUMBER,
1990 p_archive_flag IN VARCHAR2) IS
1991
1992 ------------------------------
1993 /* Cursor to retrieve Additional Element Information */
1994 CURSOR csr_get_element(p_bus_grp_id NUMBER) IS
1995 SELECT hoi.org_information2 element_type_id
1996 ,hoi.org_information3 input_value_id
1997 ,hoi.org_information7 element_narrative
1998 ,pec.classification_name
1999 ,piv.uom
2000 FROM hr_organization_information hoi
2001 ,pay_element_classifications pec
2002 ,pay_element_types_f pet
2003 ,pay_input_values_f piv
2004 WHERE hoi.organization_id = p_bus_grp_id
2005 AND hoi.org_information_context = 'Business Group:Payslip Info'
2006 AND hoi.org_information1 = 'ELEMENT'
2007 AND hoi.org_information2 = pet.element_type_id
2008 AND pec.classification_id = pet.classification_id
2009 AND piv.input_value_id = hoi.org_information3
2010 AND p_date_earned BETWEEN piv.effective_start_date AND piv.effective_end_date;
2011
2012 -------------------------------
2013 /* Cursor to retrieve run result value of Additional Elements */
2014 CURSOR csr_result_value(p_iv_id NUMBER
2015 ,p_ele_type_id NUMBER
2016 ,p_assignment_action_id NUMBER) IS
2017 SELECT rrv.result_value
2018 FROM pay_run_result_values rrv
2019 ,pay_run_results rr
2020 ,pay_assignment_actions paa
2021 ,pay_payroll_actions ppa
2022 WHERE rrv.input_value_id = p_iv_id
2023 AND rr.element_type_id = p_ele_type_id
2024 AND rr.run_result_id = rrv.run_result_id
2025 AND rr.assignment_action_id = paa.assignment_action_id
2026 AND paa.assignment_action_id = p_assignment_action_id
2027 AND ppa.payroll_action_id = paa.payroll_action_id
2028 AND ppa.action_type IN ('Q','R')
2029 AND rrv.result_value IS NOT NULL;
2030 ------------------------------
2031
2032 rec_get_element csr_get_element%ROWTYPE;
2033 /*Bug fix 6193177*/
2034 -- l_result_value pay_run_result_values.result_value%TYPE := 0;
2035 l_result_value pay_run_result_values.result_value%TYPE ;
2036 l_action_info_id NUMBER;
2037 l_ovn NUMBER;
2038 l_element_context VARCHAR2(10);
2039 l_index NUMBER := 0;
2040 l_formatted_value VARCHAR2(50) := NULL;
2041 l_flag NUMBER := 0;
2042 ------------------------------
2043
2044 BEGIN
2045
2046 IF g_debug THEN
2047 hr_utility.set_location(' Entering Procedure ARCHIVE_ADD_ELEMENT',320);
2048 END IF;
2049
2050 IF p_archive_flag = 'N' THEN
2051 ---------------------------------------------------
2052 --Check if global table has already been populated
2053 ---------------------------------------------------
2054 IF g_element_table.count = 0 THEN
2055
2056 OPEN csr_get_element(g_business_group_id);
2057 LOOP
2058 FETCH csr_get_element INTO rec_get_element;
2059 EXIT WHEN csr_get_element%NOTFOUND;
2060
2061 l_element_context := 'F'; --Additional Element Context
2062 SETUP_ELEMENT_DEFINITIONS(p_classification_name => rec_get_element.classification_name
2063 ,p_element_name => rec_get_element.element_narrative
2064 ,p_element_type_id => rec_get_element.element_type_id
2065 ,p_input_value_id => rec_get_element.input_value_id
2066 ,p_element_type => l_element_context
2067 ,p_uom => rec_get_element.uom
2068 ,p_archive_flag => p_archive_flag);
2069
2070 END LOOP;
2071 CLOSE csr_get_element;
2072 END IF;
2073
2074 ELSIF p_archive_flag = 'Y' AND g_element_table.count > 0 THEN
2075
2076 FOR l_index IN g_element_table.first.. g_element_table.last LOOP
2077 l_result_value := NULL;
2078
2079 BEGIN
2080 /*
2081 OPEN csr_result_value(g_element_table(l_index).input_value_id
2082 ,g_element_table(l_index).element_type_id
2083 ,p_assignment_action_id);
2084 FETCH csr_result_value INTO l_result_value;
2085 CLOSE csr_result_value;
2086 */
2087
2088 -- Fix to handle Multiple Element Entries
2089
2090 /* get the element run result value */
2091 OPEN csr_result_value(g_element_table(l_index).input_value_id
2092 ,g_element_table(l_index).element_type_id
2093 ,p_assignment_action_id);
2094 LOOP
2095 FETCH csr_result_value INTO l_result_value;
2096 EXIT WHEN csr_result_value%NOTFOUND;
2097
2098
2099 IF l_result_value is not null THEN
2100 pay_action_information_api.create_action_information (
2101 p_action_information_id => l_action_info_id
2102 ,p_action_context_id => p_archive_assact_id
2103 ,p_action_context_type => 'AAP'
2104 ,p_object_version_number => l_ovn
2105 ,p_effective_date => p_effective_date
2106 ,p_source_id => NULL
2107 ,p_source_text => NULL
2108 ,p_action_information_category => 'EMEA ELEMENT INFO'
2109 ,p_action_information1 => g_element_table(l_index).element_type_id
2110 ,p_action_information2 => g_element_table(l_index).input_value_id
2111 ,p_action_information3 => g_element_table(l_index).element_type
2112 ,p_action_information4 => l_result_value --l_formatted_value
2113 ,p_action_information9 => 'Additional Element'
2114 ,p_assignment_id => p_assignment_id);
2115 END IF;
2116
2117 END LOOP;
2118 CLOSE csr_result_value;
2119
2120 -- End Fix to handle Multiple Element Entries
2121
2122 EXCEPTION WHEN OTHERS THEN
2123 g_err_num := SQLCODE;
2124 /*fnd_file.put_line(fnd_file.log,'ORA_ERR: ' || g_err_num || 'In ARCHIVE_ADD_ELEMENT');*/
2125
2126 IF g_debug THEN
2127 hr_utility.set_location('ORA_ERR: ' || g_err_num || 'In ARCHIVE_ADD_ELEMENT',330);
2128 END IF;
2129 END;
2130 END LOOP;
2131
2132 END IF;
2133
2134 IF g_debug THEN
2135 hr_utility.set_location(' Leaving Procedure ARCHIVE_ADD_ELEMENT',340);
2136 END IF;
2137
2138 END ARCHIVE_ADD_ELEMENT;
2139
2140 ---------------------------------- PROCEDURE ARCHIVE_OTH_BALANCE ----------------------------------------------------------------
2141
2142 /* OTHER BALANCES REGION */
2143
2144 PROCEDURE ARCHIVE_OTH_BALANCE (p_archive_assact_id IN NUMBER,
2145 p_assignment_action_id IN NUMBER,
2146 p_assignment_id IN NUMBER,
2147 p_payroll_action_id IN NUMBER,
2148 p_record_count IN NUMBER,
2149 p_pre_payact_id IN NUMBER,
2150 p_effective_date IN DATE,
2151 p_date_earned IN DATE,
2152 p_archive_flag IN VARCHAR2) IS
2153
2154 ------------------
2155 /* Cursor to retrieve Other Balances Information */
2156 CURSOR csr_get_balance(p_bus_grp_id NUMBER) IS
2157 SELECT org_information4 balance_type_id
2158 ,org_information5 balance_dim_id
2159 ,org_information7 narrative
2160 FROM hr_organization_information
2161 WHERE organization_id = p_bus_grp_id
2162 AND org_information_context = 'Business Group:Payslip Info'
2163 AND org_information1 = 'BALANCE';
2164
2165 -----------------
2166 /* Cursor to retrieve Tax Unit Id for setting context */
2167 CURSOR csr_tax_unit (p_run_assact_id NUMBER) IS
2168 SELECT paa.tax_unit_id
2169 FROM pay_assignment_actions paa
2170 WHERE paa.assignment_action_id = p_run_assact_id;
2171 -----------------
2172 /* Cursor to fetch defined balance id */
2173 CURSOR csr_def_balance(bal_type_id NUMBER, bal_dim_id NUMBER) IS
2174 SELECT defined_balance_id
2175 FROM pay_defined_balances
2176 WHERE balance_type_id = bal_type_id
2177 AND balance_dimension_id = bal_dim_id;
2178 ----------------
2179 rec_get_balance csr_get_balance%ROWTYPE;
2180 l_balance_value NUMBER := 0;
2181 l_action_info_id NUMBER;
2182 l_ovn NUMBER;
2183 l_index NUMBER;
2184 l_tu_id NUMBER;
2185 l_defined_balance_id NUMBER :=0;
2186 l_formatted_value VARCHAR2(50) := NULL;
2187 l_flag NUMBER := 0;
2188
2189 ---------------
2190
2191 BEGIN
2192
2193 IF g_debug THEN
2194 hr_utility.set_location(' Entering Procedure ARCHIVE_OTH_BALANCE',350);
2195 END IF;
2196
2197 -- fnd_file.put_line(fnd_file.log,'In ARCHIVE_OTH_BALANCE 0');
2198 /*fnd_file.put_line(fnd_file.log,'Entering In ARCHIVE_OTH_BALANCE global');*/
2199
2200 IF p_archive_flag = 'N' THEN
2201 ---------------------------------------------------
2202 --Check if global table has already been populated
2203 ---------------------------------------------------
2204 IF g_user_balance_table.count = 0 THEN
2205 OPEN csr_get_balance(g_business_group_id);
2206 LOOP
2207 FETCH csr_get_balance INTO rec_get_balance;
2208 EXIT WHEN csr_get_balance%NOTFOUND;
2209
2210 OPEN csr_def_balance(rec_get_balance.balance_type_id,rec_get_balance.balance_dim_id);
2211 FETCH csr_def_balance INTO l_defined_balance_id;
2212 CLOSE csr_def_balance;
2213
2214 PAY_DK_ARCHIVE.SETUP_BALANCE_DEFINITIONS
2215 (p_balance_name => rec_get_balance.narrative
2216 ,p_defined_balance_id => l_defined_balance_id
2217 ,p_balance_type_id => rec_get_balance.balance_type_id);
2218 END LOOP;
2219 CLOSE csr_get_balance;
2220 END IF;
2221
2222 ---------------------------------------------------
2223 -- For Tax Card details ,
2224 -- Check if global table has already been populated
2225 -- if not then populate the values
2226 ---------------------------------------------------
2227 IF g_tax_card_tab.count = 0 THEN
2228
2229 g_tax_card_tab(1).inp_val_name := 'Method of Receipt';
2230 g_tax_card_tab(2).inp_val_name := 'Tax Card Type';
2231 g_tax_card_tab(3).inp_val_name := 'Tax Percentage';
2232 g_tax_card_tab(4).inp_val_name := 'Tax Free Threshold';
2233 g_tax_card_tab(5).inp_val_name := 'Monthly Tax Deduction';
2234 g_tax_card_tab(6).inp_val_name := 'Bi Weekly Tax Deduction';
2235 g_tax_card_tab(7).inp_val_name := 'Weekly Tax Deduction';
2236 g_tax_card_tab(8).inp_val_name := 'Daily Tax Deduction';
2237 g_tax_card_tab(9).inp_val_name := 'Registration Date';
2238 g_tax_card_tab(10).inp_val_name := 'Date Returned';
2239
2240 END IF;
2241
2242 ---------------------------------------------------
2243 -- For Mandatory Balance Details ,
2244 -- Check if global table has already been populated
2245 -- if not hen populate the values
2246 ---------------------------------------------------
2247
2248 IF g_bal_val.count = 0 THEN
2249
2250 g_bal_val(1).bal_name := 'AMBABLE_PAY_ASG_YTD'; -- AMB able income ytd
2251 g_bal_val(2).bal_name := 'EMPLOYEE_TAX_ASG_YTD'; -- Tax ytd
2252 g_bal_val(3).bal_name := 'HOLIDAYABLE_PAY_ASG_YTD'; -- Holiday able income ytd
2253 g_bal_val(4).bal_name := 'EMPLOYEE_ATP_DEDUCTIONS_ASG_YTD'; -- ATP contribution ytd
2254 g_bal_val(5).bal_name := 'EMPLOYEE_SP_DEDUCTIONS_ASG_YTD'; -- Special Pension ytd
2255
2256 g_bal_val(6).bal_name := 'EMPLOYER_PENSION_DEDUCTIONS_ASG_YTD'; -- Employer Pension ytd
2257 g_bal_val(7).bal_name := 'EMPLOYEE_PENSION_DEDUCTIONS_ASG_YTD'; -- Employee Pension ytd
2258 g_bal_val(8).bal_name := 'EMPLOYEE_AMB_DEDUCTION_ASG_YTD'; -- For AMB Contribution ytd
2259
2260 g_bal_val(9).bal_name := 'HOLIDAY_ACCRUAL_AMOUNT_ASG_YTD'; -- FOR Calculated holiday pay ytd (Salaried)
2261
2262 -- HOLIDAY_ACCRUAL_AMOUNT_ASG_YTD
2263 -- HOLIDAY_ACCRUAL_PAY_ASG_YTD
2264
2265 -- g_bal_val(10).bal_name := 'INITIAL_HOLIDAY_ENTITLEMENT_DAYS_ASG_HOLIDAY_YTD'; -- Holidays remaining with pay
2266 g_bal_val(10).bal_name := 'INITIAL_HOL_ENTITLEMENT_DAYS_ASG_HOLIDAY_YTD'; -- Holidays remaining with pay
2267 g_bal_val(11).bal_name := 'HOLIDAY_PAID_DAYS_ASG_HOLIDAY_YTD'; -- Holidays remaining with pay
2268
2269 g_bal_val(12).bal_name := 'HOLIDAY_UNPAID_DAYS_ASG_HOLIDAY_YTD'; -- Holidays remaining without pay
2270
2271 g_bal_val(13).bal_name := 'TOTAL_G_DAGE_PAY_ASG_YTD'; -- G-day's (money)
2272 g_bal_val(14).bal_name := 'TOTAL_G_DAGE_DAYS_ASG_YTD'; -- G-day's (number of day's)
2273
2274 -- Bug Fix 4704284 : start
2275
2276 -- g_bal_val(15).bal_name := 'TAXABLE_PAY_ASG_YTD'; -- FOR Rest Amount of F Card
2277 g_bal_val(15).bal_name := 'F_TAX_BASE_ASG_YTD'; -- FOR Rest Amount of F Card
2278
2279 -- g_bal_val(16).bal_name := 'TAXABLE_PAY_ASG_PTD'; -- Taxable Income
2280 g_bal_val(16).bal_name := 'EMPLOYEE_TAXABLE_BASE_ASG_PTD'; -- Taxable Income
2281
2282 -- Bug Fix 4704284 : end
2283
2284 g_bal_val(17).bal_name := 'EMPLOYEE_TAX_ASG_PTD'; -- Tax in period
2285
2286 g_bal_val(18).bal_name := 'HOLIDAY_ACCRUAL_PAY_ASG_YTD'; -- FOR Calculated holiday pay ytd (Hourly Paid)
2287
2288 -- Additional Balances for Holiday Pay Changes
2289
2290 g_bal_val(19).bal_name := 'HOLIDAY_BANK_PAY_ASG_YTD'; -- FOR AMBable income ytd
2291
2292 g_bal_val(20).bal_name := 'HOLIDAY_TAX_ASG_YTD'; -- FOR Tax ytd
2293
2294 g_bal_val(21).bal_name := 'HOLIDAY_SP_ASG_YTD'; -- FOR Special Pension ytd
2295
2296 -- Additional Balances for Holiday Pay Changes
2297 -- Taxable Income = EMPLOYEE_TAXABLE_BASE_ASG_PTD + ( l_income_from_hol_Pay - HOLIDAY_AMB_ASG_PTD - HOLIDAY_SP_ASG_PTD )
2298
2299 -- if employee is salaried ,
2300 -- then l_income_from_hol_Pay = Salaried Hol Accrual Amount_ASG_PTD
2301 -- + Salaried Hol Curr Entit Amount_ASG_PTD
2302 -- + Salaried Hol Next Entit Amount_ASG_PTD )
2303 -- else (employee is hourly paid)
2304 -- then l_income_from_hol_Pay = HOLIDAY_BANK_PAY_ASG_PTD
2305
2306
2307 g_bal_val(22).bal_name := 'HOLIDAY_BANK_PAY_ASG_PTD'; -- FOR Taxable Income
2308 g_bal_val(23).bal_name := 'HOLIDAY_AMB_ASG_PTD'; -- FOR Taxable Income
2309 g_bal_val(24).bal_name := 'HOLIDAY_SP_ASG_PTD'; -- FOR Taxable Income
2310
2311 -- Additional Balances for Holiday Pay Changes
2312 -- Tax in period = EMPLOYEE_TAX_ASG_PTD + HOLIDAY_TAX_ASG_PTD
2313
2314 g_bal_val(25).bal_name := 'HOLIDAY_TAX_ASG_PTD'; -- FOR Tax in period
2315
2316 g_bal_val(26).bal_name := 'SALARIED_HOL_ACCRUAL_AMOUNT_ASG_PTD'; -- FOR Taxable Income
2317 g_bal_val(27).bal_name := 'SALARIED_HOL_CURR_ENTIT_AMOUNT_ASG_PTD'; -- FOR Taxable Income
2318 g_bal_val(28).bal_name := 'SALARIED_HOL_NEXT_ENTIT_AMOUNT_ASG_PTD'; -- FOR Taxable Income
2319
2320 -- Bug Fix 5080969
2321
2322 -- AMB Contribution ytd = EMPLOYEE_AMB_DEDUCTION_ASG_YTD + HOLIDAY_AMB_ASG_YTD
2323
2324 g_bal_val(29).bal_name := 'HOLIDAY_AMB_ASG_YTD'; -- For AMB Contribution ytd
2325
2326
2327
2328 END IF;
2329
2330 ELSIF p_archive_flag = 'Y' THEN
2331
2332 OPEN csr_tax_unit(p_assignment_action_id);
2333 FETCH csr_tax_unit INTO l_tu_id;
2334 CLOSE csr_tax_unit;
2335
2336 PAY_BALANCE_PKG.SET_CONTEXT('TAX_UNIT_ID',l_tu_id);
2337 PAY_BALANCE_PKG.SET_CONTEXT('DATE_EARNED',fnd_date.date_to_canonical(p_date_earned));
2338
2339 IF g_user_balance_table.count > 0 THEN
2340 -- fnd_file.put_line(fnd_file.log,'In ARCHIVE_OTH_BALANCE 1');
2341
2342 FOR l_index IN g_user_balance_table.first.. g_user_balance_table.last LOOP
2343
2344 l_balance_value := pay_balance_pkg.get_value(g_user_balance_table(l_index).defined_balance_id,p_assignment_action_id);
2345
2346 IF l_balance_value > 0 THEN
2347 -- fnd_file.put_line(fnd_file.log,'In ARCHIVE_OTH_BALANCE 2 :' || l_balance_value);
2348 pay_action_information_api.create_action_information (
2349 p_action_information_id => l_action_info_id
2350 ,p_action_context_id => p_archive_assact_id
2351 ,p_action_context_type => 'AAP'
2352 ,p_object_version_number => l_ovn
2353 ,p_effective_date => p_effective_date
2354 ,p_source_id => NULL
2355 ,p_source_text => NULL
2356 ,p_action_information_category => 'EMEA BALANCES'
2357 ,p_action_information1 => g_user_balance_table(l_index).defined_balance_id
2358 ,p_action_information2 => 'OBAL' --Other Balances Context
2359 ,p_action_information4 => fnd_number.number_to_canonical(l_balance_value) --l_formatted_value
2360 ,p_action_information5 => NULL
2361 ,p_action_information6 => 'Other Balances'
2362 ,p_assignment_id => p_assignment_id);
2363 END IF;
2364 END LOOP;
2365 END IF; /* For table count check */
2366 END IF;
2367
2368 EXCEPTION WHEN OTHERS THEN
2369 g_err_num := SQLCODE;
2370 -- fnd_file.put_line(fnd_file.log,'ORA_ERR: ' || g_err_num || 'In ARCHIVE_OTH_BALANCE'||SQLERRM);
2371
2372 IF g_debug THEN
2373 hr_utility.set_location('ORA_ERR: ' || g_err_num || 'In ARCHIVE_OTH_BALANCE',360);
2374 END IF;
2375
2376 END ARCHIVE_OTH_BALANCE;
2377
2378 ----------------------------------------- PROCEDURE ARCHIVE_CODE ---------------------------------------------------------
2379
2380 /* ARCHIVE CODE */
2381
2382 PROCEDURE ARCHIVE_CODE(p_assignment_action_id IN NUMBER
2383 ,p_effective_date IN DATE)
2384 IS
2385
2386 ----------------------
2387 /* Cursor to retrieve Payroll and Prepayment related Ids for Archival */
2388 CURSOR csr_archive_ids (p_locking_action_id NUMBER) IS
2389 SELECT prepay_assact.assignment_action_id prepay_assact_id
2390 ,prepay_assact.assignment_id prepay_assgt_id
2391 ,prepay_payact.payroll_action_id prepay_payact_id
2392 ,prepay_payact.effective_date prepay_effective_date
2393 ,run_assact.assignment_id run_assgt_id
2394 ,run_assact.assignment_action_id run_assact_id
2395 ,run_payact.payroll_action_id run_payact_id
2396 ,run_payact.payroll_id payroll_id
2397 FROM pay_action_interlocks archive_intlck
2398 ,pay_assignment_actions prepay_assact
2399 ,pay_payroll_actions prepay_payact
2400 ,pay_action_interlocks prepay_intlck
2401 ,pay_assignment_actions run_assact
2402 ,pay_payroll_actions run_payact
2403 WHERE archive_intlck.locking_action_id = p_locking_action_id
2404 AND prepay_assact.assignment_action_id = archive_intlck.locked_action_id
2405 AND prepay_payact.payroll_action_id = prepay_assact.payroll_action_id
2406 AND prepay_payact.action_type IN ('U','P')
2407 AND prepay_intlck.locking_action_id = prepay_assact.assignment_action_id
2408 AND run_assact.assignment_action_id = prepay_intlck.locked_action_id
2409 AND run_payact.payroll_action_id = run_assact.payroll_action_id
2410 AND run_payact.action_type IN ('Q', 'R')
2411 ORDER BY prepay_intlck.locking_action_id,prepay_intlck.locked_action_id desc;
2412 ---------------------
2413 /* Cursor to retrieve time period information */
2414 CURSOR csr_period_end_date(p_assact_id NUMBER,p_pay_act_id NUMBER) IS
2415 SELECT ptp.end_date end_date,
2416 ptp.regular_payment_date regular_payment_date,
2417 ptp.time_period_id time_period_id,
2418 ppa.date_earned date_earned,
2419 ppa.effective_date effective_date,
2420 ptp.start_date start_date
2421 FROM per_time_periods ptp
2422 ,pay_payroll_actions ppa
2423 ,pay_assignment_actions paa
2424 WHERE ptp.payroll_id =ppa.payroll_id
2425 AND ppa.payroll_action_id =paa.payroll_action_id
2426 AND paa.assignment_action_id =p_assact_id
2427 AND ppa.payroll_action_id =p_pay_act_id
2428 AND ppa.date_earned BETWEEN ptp.start_date AND ptp.end_date;
2429 -----------------
2430 /* Cursor to retrieve Archive Payroll Action Id */
2431 CURSOR csr_archive_payact(p_assignment_action_id NUMBER) IS
2432 SELECT payroll_action_id
2433 FROM pay_assignment_actions
2434 WHERE assignment_Action_id = p_assignment_action_id;
2435 -----------------
2436 l_archive_payact_id NUMBER;
2437 l_record_count NUMBER;
2438 l_actid NUMBER;
2439 l_end_date per_time_periods.end_date%TYPE;
2440 l_pre_end_date per_time_periods.end_date%TYPE;
2441 l_reg_payment_date per_time_periods.regular_payment_date%TYPE;
2442 l_pre_reg_payment_date per_time_periods.regular_payment_date%TYPE;
2443 l_date_earned pay_payroll_actions.date_earned%TYPE;
2444 l_pre_date_earned pay_payroll_actions.date_earned%TYPE;
2445 l_effective_date pay_payroll_actions.effective_date%TYPE;
2446 l_pre_effective_date pay_payroll_actions.effective_date%TYPE;
2447 l_run_payact_id NUMBER;
2448 l_action_context_id NUMBER;
2449 g_archive_pact NUMBER;
2450 p_assactid NUMBER;
2451 l_time_period_id per_time_periods.time_period_id%TYPE;
2452 l_pre_time_period_id per_time_periods.time_period_id%TYPE;
2453 l_start_date per_time_periods.start_date%TYPE;
2454 l_pre_start_date per_time_periods.start_date%TYPE;
2455 l_fnd_session NUMBER := 0;
2456 l_prev_prepay NUMBER := 0;
2457 ------------------
2458
2459 BEGIN
2460
2461 -- fnd_file.put_line(fnd_file.log,'Entering Procedure ARCHIVE_CODE');
2462
2463 IF g_debug THEN
2464 hr_utility.set_location(' Entering Procedure ARCHIVE_CODE',380);
2465 END IF;
2466
2467 OPEN csr_archive_payact(p_assignment_action_id);
2468 FETCH csr_archive_payact INTO l_archive_payact_id;
2469 CLOSE csr_archive_payact;
2470
2471 -- fnd_file.put_line(fnd_file.log,'closed csr_archive_payact');
2472
2473 l_record_count := 0;
2474
2475 FOR rec_archive_ids IN csr_archive_ids(p_assignment_action_id) LOOP
2476
2477 OPEN csr_period_end_date(rec_archive_ids.run_assact_id,rec_archive_ids.run_payact_id);
2478 FETCH csr_period_end_date INTO l_end_date,l_reg_payment_date,l_time_period_id,l_date_earned,l_effective_date,l_start_date;
2479 CLOSE csr_period_end_date;
2480
2481 -- fnd_file.put_line(fnd_file.log,'closed csr_period_end_date');
2482
2483 OPEN csr_period_end_date(rec_archive_ids.prepay_assact_id,rec_archive_ids.prepay_payact_id);
2484 FETCH csr_period_end_date INTO l_pre_end_date,l_pre_reg_payment_date,l_pre_time_period_id,l_pre_date_earned,l_pre_effective_date,l_pre_start_date;
2485 CLOSE csr_period_end_date;
2486
2487 -- fnd_file.put_line(fnd_file.log,'closed csr_period_end_date');
2488
2489 /*fnd_file.put_line(fnd_file.log,'ARCHIVE_EMPLOYEE_DETAILS');*/
2490
2491 -------------------------------------------------------------
2492 --Archive EMPLOYEE_DETAILS, PAYMENT_INFO and BALANCES
2493 --for every prepayment assignment action id
2494 -------------------------------------------------------------
2495
2496 IF (rec_archive_ids.prepay_assact_id <> l_prev_prepay) THEN
2497
2498 -- fnd_file.put_line(fnd_file.log,'in ARCHIVE_EMPLOYEE_DETAILS');
2499
2500 /*
2501 ARCHIVE_EMPLOYEE_DETAILS(p_archive_assact_id => p_assignment_action_id
2502 ,p_assignment_id => rec_archive_ids.run_assgt_id
2503 ,p_assignment_action_id => rec_archive_ids.run_assact_id
2504 ,p_payroll_action_id => l_archive_payact_id
2505 ,p_time_period_id => l_time_period_id
2506 ,p_date_earned => l_pre_date_earned
2507 ,p_pay_date_earned => l_date_earned
2508 ,p_effective_date => p_effective_date);
2509 */
2510
2511 -- using l_date_earned (Payroll Run Date Earned) instead of l_pre_date_earned (Prepayments Date Earned)
2512 -- because the date_earned column at table pay_payroll_actions for Prepayments may not always be populated.
2513
2514 ARCHIVE_EMPLOYEE_DETAILS(p_archive_assact_id => p_assignment_action_id
2515 ,p_assignment_id => rec_archive_ids.run_assgt_id
2516 ,p_assignment_action_id => rec_archive_ids.run_assact_id
2517 ,p_payroll_action_id => l_archive_payact_id
2518 ,p_time_period_id => l_time_period_id
2519 ,p_date_earned => l_date_earned
2520 ,p_pay_date_earned => l_date_earned
2521 ,p_effective_date => p_effective_date);
2522
2523 -- fnd_file.put_line(fnd_file.log,'out ARCHIVE_EMPLOYEE_DETAILS');
2524 -- fnd_file.put_line(fnd_file.log,'in ARCHIVE_ADDL_EMP_DETAILS');
2525
2526 ARCHIVE_ADDL_EMP_DETAILS(p_archive_assact_id => p_assignment_action_id
2527 ,p_assignment_id => rec_archive_ids.run_assgt_id
2528 ,p_assignment_action_id => rec_archive_ids.run_assact_id
2529 ,p_effective_date => p_effective_date
2530 ,p_date_earned => l_date_earned
2531 ,p_payroll_action_id => l_archive_payact_id );
2532
2533
2534 -- fnd_file.put_line(fnd_file.log,'out ARCHIVE_ADDL_EMP_DETAILS');
2535 -- fnd_file.put_line(fnd_file.log,'in ARCHIVE_PAYMENT_INFO');
2536
2537 /*fnd_file.put_line(fnd_file.log,'ARCHIVE_PAYMENT_INFO');*/
2538
2539 /*
2540 ARCHIVE_PAYMENT_INFO(p_archive_assact_id => p_assignment_action_id,
2541 p_prepay_assact_id => rec_archive_ids.prepay_assact_id,
2542 p_assignment_id => rec_archive_ids.prepay_assgt_id,
2543 p_date_earned => l_pre_date_earned,
2544 p_effective_date => p_effective_date);
2545 */
2546
2547 -- using l_date_earned (Payroll Run Date Earned) instead of l_pre_date_earned (Prepayments Date Earned)
2548 -- because the date_earned column at table pay_payroll_actions for Prepayments may not always be populated.
2549
2550 ARCHIVE_PAYMENT_INFO(p_archive_assact_id => p_assignment_action_id,
2551 p_prepay_assact_id => rec_archive_ids.prepay_assact_id,
2552 p_assignment_id => rec_archive_ids.prepay_assgt_id,
2553 p_date_earned => l_date_earned,
2554 p_effective_date => p_effective_date);
2555
2556 -- fnd_file.put_line(fnd_file.log,'out ARCHIVE_PAYMENT_INFO');
2557 -- fnd_file.put_line(fnd_file.log,'in ARCHIVE_OTH_BALANCE');
2558
2559 /*fnd_file.put_line(fnd_file.log,'ARCHIVE_OTH_BALANCE');*/
2560
2561 ARCHIVE_OTH_BALANCE(p_archive_assact_id => p_assignment_action_id,
2562 p_assignment_action_id => rec_archive_ids.run_assact_id,
2563 p_assignment_id => rec_archive_ids.run_assgt_id,
2564 p_payroll_action_id => l_archive_payact_id,
2565 p_record_count => l_record_count,
2566 p_pre_payact_id => rec_archive_ids.prepay_payact_id,
2567 p_effective_date => p_effective_date,
2568 p_date_earned => l_date_earned,
2569 p_archive_flag => 'Y');
2570
2571 -- fnd_file.put_line(fnd_file.log,'out ARCHIVE_OTH_BALANCE');
2572 -- fnd_file.put_line(fnd_file.log,'before end if');
2573
2574
2575
2576 l_prev_prepay := rec_archive_ids.prepay_assact_id;
2577
2578 END IF;
2579
2580 /*fnd_file.put_line(fnd_file.log,'ARCHIVE_ACCRUAL_PLAN');*/
2581
2582 /* ARCHIVE_ACCRUAL_PLAN (p_assignment_id => rec_archive_ids.run_assgt_id,
2583 p_date_earned => l_date_earned,
2584 p_effective_date => p_effective_date,
2585 p_archive_assact_id => p_assignment_action_id,
2586 p_run_assignment_action_id => rec_archive_ids.run_assact_id,
2587 p_period_end_date => l_end_date,
2588 p_period_start_date => l_start_date);*/
2589
2590 /*fnd_file.put_line(fnd_file.log,'ARCHIVE_ADD_ELEMENT');*/
2591
2592 -- fnd_file.put_line(fnd_file.log,'in ARCHIVE_ADD_ELEMENT');
2593
2594
2595 ARCHIVE_ADD_ELEMENT(p_archive_assact_id => p_assignment_action_id,
2596 p_assignment_action_id => rec_archive_ids.run_assact_id,
2597 p_assignment_id => rec_archive_ids.run_assgt_id,
2598 p_payroll_action_id => l_archive_payact_id,
2599 p_date_earned => l_date_earned,
2600 p_effective_date => p_effective_date,
2601 p_pre_payact_id => rec_archive_ids.prepay_payact_id,
2602 p_archive_flag => 'Y');
2603
2604 -- fnd_file.put_line(fnd_file.log,'out ARCHIVE_ADD_ELEMENT');
2605 -- fnd_file.put_line(fnd_file.log,'in ARCHIVE_MAIN_ELEMENTS');
2606
2607 /*fnd_file.put_line(fnd_file.log,'Assact id: '|| p_assignment_action_id);*/
2608
2609 ARCHIVE_MAIN_ELEMENTS (p_archive_assact_id => p_assignment_action_id,
2610 p_assignment_action_id => rec_archive_ids.run_assact_id,
2611 p_assignment_id => rec_archive_ids.run_assgt_id,
2612 p_date_earned => l_date_earned,
2613 p_effective_date => p_effective_date ) ;
2614
2615 -- fnd_file.put_line(fnd_file.log,'out ARCHIVE_MAIN_ELEMENTS');
2616
2617
2618
2619 l_record_count := l_record_count + 1;
2620
2621 END LOOP;
2622
2623 IF g_debug THEN
2624 hr_utility.set_location(' Leaving Procedure ARCHIVE_CODE',390);
2625 END IF;
2626
2627 -- fnd_file.put_line(fnd_file.log,'Leaving Procedure ARCHIVE_CODE');
2628
2629 END ARCHIVE_CODE;
2630
2631
2632 ---------------------------------------- PROCEDURE ARCHIVE_ADDL_EMP_DETAILS --------------------------------------------------------------------------
2633
2634 /*Additional Employee Details*/
2635
2636 PROCEDURE ARCHIVE_ADDL_EMP_DETAILS(p_archive_assact_id IN NUMBER
2637 ,p_assignment_id IN NUMBER
2638 ,p_assignment_action_id IN NUMBER
2639 ,p_effective_date IN DATE
2640 ,p_date_earned IN DATE
2641 ,p_payroll_action_id IN NUMBER )
2642 IS
2643 -------------
2644 CURSOR CSR_ACTUAL_TERM_DATE (p_assignment_id NUMBER) IS
2645 SELECT actual_termination_date
2646 FROM per_periods_of_service pps,
2647 per_all_assignments_f paa
2648 WHERE pps.period_of_service_id = paa.period_of_service_id
2649 AND p_date_earned between paa.effective_start_date and paa.effective_end_date
2650 AND paa.assignment_id = p_assignment_id;
2651 -------------
2652
2653 CURSOR get_details(p_assignment_id NUMBER , p_input_value VARCHAR2 ) IS
2654 SELECT ee.effective_start_date effective_start_date
2655 ,eev1.screen_entry_value screen_entry_value
2656 FROM per_all_assignments_f asg1
2657 ,per_all_assignments_f asg2
2658 ,per_all_people_f per
2659 ,pay_element_links_f el
2660 ,pay_element_types_f et
2661 ,pay_input_values_f iv1
2662 ,pay_element_entries_f ee
2663 ,pay_element_entry_values_f eev1
2664 WHERE asg1.assignment_id = p_assignment_id
2665 AND p_date_earned BETWEEN asg1.effective_start_date AND asg1.effective_end_date
2666 AND p_date_earned BETWEEN asg2.effective_start_date AND asg2.effective_end_date
2667 AND per.person_id = asg1.person_id
2668 AND asg2.person_id = per.person_id
2669 AND asg2.primary_flag = 'Y'
2670 AND et.element_name = 'Tax Card'
2671 AND et.legislation_code = 'DK'
2672 AND iv1.element_type_id = et.element_type_id
2673 AND iv1.name = p_input_value
2674 AND el.business_group_id = per.business_group_id
2675 AND el.element_type_id = et.element_type_id
2676 AND ee.assignment_id = asg2.assignment_id
2677 AND ee.element_link_id = el.element_link_id
2678 AND eev1.element_entry_id = ee.element_entry_id
2679 AND eev1.input_value_id = iv1.input_value_id
2680 AND p_date_earned BETWEEN ee.effective_start_date AND ee.effective_end_date
2681 AND p_date_earned BETWEEN eev1.effective_start_date AND eev1.effective_end_date;
2682 ------------
2683 CURSOR csr_tax_details(p_assignment_id NUMBER, p_input_value VARCHAR2) IS
2684 SELECT ee.effective_start_date
2685 ,eev1.screen_entry_value screen_entry_value
2686 FROM per_all_assignments_f asg1
2687 ,pay_element_links_f el
2688 ,pay_element_types_f et
2689 ,pay_input_values_f iv1
2690 ,pay_element_entries_f ee
2691 ,pay_element_entry_values_f eev1
2692 WHERE asg1.assignment_id = p_assignment_id
2693 AND p_date_earned BETWEEN asg1.effective_start_date AND asg1.effective_end_date
2694 AND et.element_name = 'Tax'
2695 AND et.legislation_code = 'DK'
2696 AND iv1.element_type_id = et.element_type_id
2697 AND iv1.name = p_input_value
2698 AND el.element_type_id = et.element_type_id
2699 AND ee.assignment_id = asg1.assignment_id
2700 AND ee.element_link_id = el.element_link_id
2701 AND eev1.element_entry_id = ee.element_entry_id
2702 AND eev1.input_value_id = iv1.input_value_id
2703 AND p_date_earned BETWEEN ee.effective_start_date AND ee.effective_end_date
2704 AND p_date_earned BETWEEN eev1.effective_start_date AND eev1.effective_end_date;
2705 -------------
2706 CURSOR csr_tax_category (p_assignment_id NUMBER) IS
2707 SELECT segment13
2708 FROM per_all_assignments_f paa,
2709 hr_soft_coding_keyflex hsc
2710 WHERE paa.soft_coding_keyflex_id = hsc.soft_coding_keyflex_id
2711 AND p_date_earned BETWEEN paa.effective_start_date AND paa.effective_end_date
2712 AND paa.assignment_id = p_assignment_id;
2713 -------------
2714 CURSOR csr_global_value (p_global_name VARCHAR2) IS
2715 SELECT global_value
2716 FROM ff_globals_f
2717 WHERE global_name = p_global_name
2718 AND p_date_earned BETWEEN effective_start_date AND effective_end_date;
2719 -------------
2720 /* cursor to get the payroll_d */
2721 CURSOR csr_payroll (p_payroll_action_id NUMBER) IS
2722 SELECT PAY_DK_ARCHIVE.GET_PARAMETER(legislative_parameters,'PAYROLL_ID')
2723 FROM pay_payroll_actions
2724 WHERE payroll_action_id = p_payroll_action_id ;
2725
2726
2727 /* cursor to get the payroll details */
2728 CURSOR csr_payroll_details (l_payroll_id NUMBER) IS
2729 SELECT payroll_name , period_type
2730 FROM pay_all_payrolls_f
2731 WHERE payroll_id = l_payroll_id ;
2732 --------------
2733
2734 l_actual_termination_date PER_PERIODS_OF_SERVICE.ACTUAL_TERMINATION_DATE%TYPE;
2735 l_tax_card_effective_date DATE;
2736 l_tax_card_type VARCHAR2(50);
2737 l_base_rate NUMBER(5,2);
2738 l_additional_rate NUMBER(5,2);
2739 l_yearly_income_limit NUMBER(10);
2740 l_previous_income NUMBER (10);
2741 l_ovn NUMBER ;
2742 l_rec get_details%ROWTYPE;
2743 l_tax_rec csr_tax_details%ROWTYPE;
2744 l_action_info_id pay_action_information.action_information_id%TYPE;
2745 l_tax_category hr_soft_coding_keyflex.segment13%TYPE;
2746 l_defined_balance_id pay_defined_balances.defined_balance_id%TYPE;
2747
2748 -- Bug Fix 5081696
2749 l_global_tax_percent VARCHAR2(20);
2750 l_use_tax_card VARCHAR2(20);
2751
2752 l_ambable_pay_asg_ytd NUMBER(10,2);
2753 l_employee_tax_asg_ytd NUMBER(10,2);
2754 l_holidayable_pay_asg_ytd NUMBER(10,2);
2755 l_emp_atp_dedn_asg_ytd NUMBER(10,2);
2756 l_emp_sp_dedn_asg_ytd NUMBER(10,2);
2757 l_total_pension_asg_ytd NUMBER(10,2);
2758 l_emplr_pension_dedn_asg_ytd NUMBER(10,2);
2759 l_emp_pension_dedn_asg_ytd NUMBER(10,2);
2760 l_emp_amb_dedn_asg_ytd NUMBER(10,2);
2761 l_calc_holiday_pay_asg_ytd NUMBER(10,2);
2762 l_hol_rem_with_pay_asg_ytd NUMBER(10,2);
2763 l_hol_rem_without_pay NUMBER(10,2);
2764 l_total_g_dage_pay_asg_ytd NUMBER(10,2);
2765 l_total_g_dage_days_asg_ytd NUMBER(10,2);
2766 l_time_off_in_lieu_hours NUMBER(10,2);
2767 l_rest_amount_of_f_card NUMBER(10,2);
2768
2769 l_income_from_hol_Pay NUMBER ;
2770 l_income_from_hol_Pay_sal NUMBER ;
2771 l_income_from_hol_Pay_hr NUMBER ;
2772
2773 -- Bug Fix 4704284 : start
2774
2775 -- l_taxable_pay_asg_ptd NUMBER(10,2);
2776 l_emp_taxable_base_asg_ptd NUMBER(10,2);
2777
2778 -- Bug Fix 4704284 : end
2779
2780 l_employee_tax_asg_ptd NUMBER(10,2);
2781
2782 l_tax_deduction VARCHAR2(240);
2783 l_tax_percent VARCHAR2(240);
2784 l_net_pay VARCHAR2(240);
2785
2786 l_payroll_id NUMBER;
2787 l_payroll_name VARCHAR2(80);
2788 l_period_type VARCHAR2(80);
2789 l_hourly_salaried per_all_assignments_f.hourly_salaried_code%TYPE ;
2790 --------------
2791 /*
2792 TYPE tax_card_rec IS RECORD (inp_val_name pay_input_values_f.NAME%type , screen_entry_val pay_input_values_f.NAME%type );
2793
2794 TYPE bal_val_rec IS RECORD ( bal_name ff_database_items.USER_NAME%type , bal_val NUMBER(10,2) );
2795
2796
2797 TYPE tax_card_table IS TABLE OF tax_card_rec INDEX BY BINARY_INTEGER;
2798
2799 TYPE bal_val_table IS TABLE OF bal_val_rec INDEX BY BINARY_INTEGER;
2800
2801
2802 g_tax_card_tab tax_card_table;
2803 g_bal_val bal_val_table;
2804
2805 */
2806
2807 -------------
2808
2809 BEGIN
2810
2811 -- fnd_file.put_line(fnd_file.log,'inside ARCHIVE_ADDL_EMP_DETAILS');
2812
2813 OPEN CSR_ACTUAL_TERM_DATE (p_assignment_id);
2814 FETCH CSR_ACTUAL_TERM_DATE INTO l_actual_termination_date;
2815 CLOSE CSR_ACTUAL_TERM_DATE;
2816
2817 -- fnd_file.put_line(fnd_file.log,'closed CSR_ACTUAL_TERM_DATE');
2818
2819 -- fnd_file.put_line(fnd_file.log,'before FOR g_tax_card_tab');
2820
2821 FOR l_index IN g_tax_card_tab.first.. g_tax_card_tab.last LOOP
2822
2823 OPEN get_details( p_assignment_id ,g_tax_card_tab(l_index).inp_val_name );
2824 FETCH get_details INTO l_rec;
2825 CLOSE get_details;
2826
2827 g_tax_card_tab(l_index).screen_entry_val := l_rec.screen_entry_value ;
2828
2829 END LOOP;
2830
2831 -- fnd_file.put_line(fnd_file.log,'end loop FOR g_tax_card_tab');
2832
2833 l_tax_card_effective_date := l_rec.effective_start_date;
2834
2835
2836 -- Bug Fix 5081696 : Start
2837
2838 -- get the tax card type
2839 l_tax_card_type := g_tax_card_tab(2).screen_entry_val ;
2840
2841 -- fnd_file.put_line(fnd_file.log,' l_tax_card_type = '||l_tax_card_type );
2842
2843 -- get the global value for tax percentage
2844
2845 OPEN csr_global_value ('DK_NO_TAX_CARD_RATE') ;
2846 FETCH csr_global_value INTO l_global_tax_percent ;
2847 CLOSE csr_global_value ;
2848
2849 -- fnd_file.put_line(fnd_file.log,'l_global_tax_percent = '||l_global_tax_percent );
2850
2851 -- get the 'Use Tax Card' input vale from Tax element
2852
2853 OPEN csr_tax_details (p_assignment_id, 'Use Tax Card') ;
2854 FETCH csr_tax_details INTO l_tax_rec ;
2855 CLOSE csr_tax_details ;
2856
2857 l_use_tax_card := l_tax_rec.screen_entry_value ;
2858
2859 -- fnd_file.put_line(fnd_file.log,'l_use_tax_card = '||l_use_tax_card );
2860
2861 -- For an employee with Tax Card type as 'No Tax Card'
2862 -- or
2863 -- For an employee with "Use tax card" as 'No' in the tax element
2864 -- even if any tax percentage is mentioned or any other details are mentioned in the tax card,
2865 -- the tax calculation is always done based on the global tax percentage.
2866 -- Hence even in the payslip the tax percentage should be displayed as DK_NO_TAX_CARD_RATE
2867
2868 IF ( (l_tax_card_type = 'NTC') OR (l_use_tax_card = 'N') ) THEN
2869 l_tax_percent := l_global_tax_percent ;
2870 -- fnd_file.put_line(fnd_file.log,' Overriding l_tax_percent ');
2871 ELSE
2872 l_tax_percent := g_tax_card_tab(3).screen_entry_val ;
2873 -- fnd_file.put_line(fnd_file.log,' Tax Card l_tax_percent ');
2874 END IF;
2875
2876 -- fnd_file.put_line(fnd_file.log,' l_tax_percent = '|| l_tax_percent);
2877
2878 -- Bug Fix 5081696 : End
2879
2880 ---------------------
2881
2882 -- fnd_file.put_line(fnd_file.log,'getting DK_TAX_CARD_TYPE');
2883
2884 -- Getting the display value for Tax Card type
2885 g_tax_card_tab(2).screen_entry_val := hr_general.decode_lookup('DK_TAX_CARD_TYPE',g_tax_card_tab(2).screen_entry_val);
2886
2887 -- fnd_file.put_line(fnd_file.log,'archiving ADDL EMPLOYEE DETAILS');
2888
2889 pay_action_information_api.create_action_information (
2890 p_action_information_id => l_action_info_id
2891 ,p_action_context_id => p_archive_assact_id
2892 ,p_action_context_type => 'AAP'
2893 ,p_object_version_number => l_ovn
2894 ,p_effective_date => p_effective_date
2895 ,p_source_id => NULL
2896 ,p_source_text => NULL
2897 ,p_action_information_category => 'ADDL EMPLOYEE DETAILS'
2898 ,p_action_information4 => g_tax_card_tab(1).screen_entry_val -- Method of Receipt
2899 ,p_action_information5 => g_tax_card_tab(2).screen_entry_val -- Tax Card Type
2900 --,p_action_information6 => g_tax_card_tab(3).screen_entry_val -- Tax Percentage
2901 ,p_action_information6 => l_tax_percent -- Tax Percentage -- Bug Fix 5081696
2902 ,p_action_information7 => g_tax_card_tab(4).screen_entry_val -- Tax Free Threshold
2903 ,p_action_information8 => g_tax_card_tab(5).screen_entry_val -- Monthly Tax Deduction
2904 ,p_action_information9 => g_tax_card_tab(6).screen_entry_val -- Bi Weekly Tax Deduction
2905 ,p_action_information10 => g_tax_card_tab(7).screen_entry_val -- Weekly Tax Deduction
2906 ,p_action_information11 => g_tax_card_tab(8).screen_entry_val -- Daily Tax Deduction
2907 ,p_action_information12 => fnd_date.date_to_displaydate(fnd_date.canonical_to_date(g_tax_card_tab(9).screen_entry_val)) -- Registration Date
2908 --,p_action_information12 => g_tax_card_tab(9).screen_entry_val -- Registration Date
2909 ,p_action_information13 => fnd_date.date_to_displaydate(fnd_date.canonical_to_date(g_tax_card_tab(10).screen_entry_val)) -- Date Returned
2910 --,p_action_information13 => g_tax_card_tab(10).screen_entry_val -- Date Returned
2911 ,p_assignment_id => p_assignment_id );
2912
2913 -- fnd_file.put_line(fnd_file.log,'finished archiving ADDL EMPLOYEE DETAILS');
2914
2915 -------------------------------------------------------------------------------
2916
2917 -- fnd_file.put_line(fnd_file.log,'begin FOR g_bal_val');
2918
2919 -- fnd_file.put_line(fnd_file.log,'g_bal_val.first = '||to_char(g_bal_val.first));
2920 -- fnd_file.put_line(fnd_file.log,'g_bal_val.last = '||to_char(g_bal_val.last));
2921
2922
2923 FOR l_index IN g_bal_val.first.. g_bal_val.last LOOP
2924
2925 -- fnd_file.put_line(fnd_file.log,'l_index = '||to_char(l_index));
2926
2927 l_defined_balance_id := GET_DEFINED_BALANCE_ID( g_bal_val(l_index).bal_name );
2928
2929 -- fnd_file.put_line(fnd_file.log,'l_defined_balance_id = '||to_char(l_defined_balance_id));
2930 -- fnd_file.put_line(fnd_file.log,'p_assignment_action_id = '||to_char(p_assignment_action_id));
2931
2932 g_bal_val(l_index).bal_val := pay_balance_pkg.get_value(l_defined_balance_id, p_assignment_action_id);
2933
2934 -- fnd_file.put_line(fnd_file.log,'g_bal_val(l_index).bal_val = '||to_char(g_bal_val(l_index).bal_val));
2935
2936 END LOOP;
2937
2938 -- fnd_file.put_line(fnd_file.log,'end loop FOR g_bal_val');
2939 -- fnd_file.put_line(fnd_file.log,'start asigning balance values');
2940
2941 /* For reference : DBIs used for various balances and reporting values
2942
2943 g_bal_val(1).bal_name := 'AMBABLE_PAY_ASG_YTD'; -- AMB able income ytd
2944 g_bal_val(2).bal_name := 'EMPLOYEE_TAX_ASG_YTD'; -- Tax ytd
2945 g_bal_val(3).bal_name := 'HOLIDAYABLE_PAY_ASG_YTD'; -- Holiday able income ytd
2946 g_bal_val(4).bal_name := 'EMPLOYEE_ATP_DEDUCTIONS_ASG_YTD'; -- ATP contribution ytd
2947 g_bal_val(5).bal_name := 'EMPLOYEE_SP_DEDUCTIONS_ASG_YTD'; -- Special Pension ytd
2948 g_bal_val(6).bal_name := 'EMPLOYER_PENSION_DEDUCTIONS_ASG_YTD'; -- Employer Pension ytd
2949 g_bal_val(7).bal_name := 'EMPLOYEE_PENSION_DEDUCTIONS_ASG_YTD'; -- Employee Pension ytd
2950 g_bal_val(8).bal_name := 'EMPLOYEE_AMB_DEDUCTION_ASG_YTD'; -- For AMB Contribution ytd
2951 g_bal_val(9).bal_name := 'HOLIDAY_ACCRUAL_AMOUNT_ASG_YTD'; -- FOR Calculated holiday pay ytd (Salaried)
2952 g_bal_val(10).bal_name := 'INITIAL_HOL_ENTITLEMENT_DAYS_ASG_HOLIDAY_YTD'; -- Holidays remaining with pay
2953 g_bal_val(11).bal_name := 'HOLIDAY_PAID_DAYS_ASG_HOLIDAY_YTD'; -- Holidays remaining with pay
2954 g_bal_val(12).bal_name := 'HOLIDAY_UNPAID_DAYS_ASG_HOLIDAY_YTD'; -- Holidays remaining without pay
2955 g_bal_val(13).bal_name := 'TOTAL_G_DAGE_PAY_ASG_YTD'; -- G-day's (money)
2956 g_bal_val(14).bal_name := 'TOTAL_G_DAGE_DAYS_ASG_YTD'; -- G-day's (number of day's)
2957 g_bal_val(15).bal_name := 'F_TAX_BASE_ASG_YTD'; -- FOR Rest Amount of F Card
2958 g_bal_val(16).bal_name := 'EMPLOYEE_TAXABLE_BASE_ASG_PTD'; -- Taxable Income
2959 g_bal_val(17).bal_name := 'EMPLOYEE_TAX_ASG_PTD'; -- Tax in period
2960 g_bal_val(18).bal_name := 'HOLIDAY_ACCRUAL_PAY_ASG_YTD'; -- FOR Calculated holiday pay ytd (Hourly Paid)
2961 g_bal_val(19).bal_name := 'HOLIDAY_BANK_PAY_ASG_YTD'; -- FOR AMBable income ytd
2962 g_bal_val(20).bal_name := 'HOLIDAY_TAX_ASG_YTD'; -- FOR Tax ytd
2963 g_bal_val(21).bal_name := 'HOLIDAY_SP_ASG_YTD'; -- FOR Special Pension ytd
2964 g_bal_val(22).bal_name := 'HOLIDAY_BANK_PAY_ASG_PTD'; -- FOR Taxable Income
2965 g_bal_val(23).bal_name := 'HOLIDAY_AMB_ASG_PTD'; -- FOR Taxable Income
2966 g_bal_val(24).bal_name := 'HOLIDAY_SP_ASG_PTD'; -- FOR Taxable Income
2967 g_bal_val(25).bal_name := 'HOLIDAY_TAX_ASG_PTD'; -- FOR Tax in period
2968 g_bal_val(26).bal_name := 'SALARIED_HOL_ACCRUAL_AMOUNT_ASG_PTD'; -- FOR Taxable Income
2969 g_bal_val(27).bal_name := 'SALARIED_HOL_CURR_ENTIT_AMOUNT_ASG_PTD'; -- FOR Taxable Income
2970 g_bal_val(28).bal_name := 'SALARIED_HOL_NEXT_ENTIT_AMOUNT_ASG_PTD'; -- FOR Taxable Income
2971 g_bal_val(29).bal_name := 'HOLIDAY_AMB_ASG_YTD'; -- For AMB Contribution ytd
2972
2973 */ -- End Reference
2974
2975 -- l_ambable_pay_asg_ytd changed for Holiday Pay Changes
2976 -- l_ambable_pay_asg_ytd := g_bal_val(1).bal_val ;
2977 l_ambable_pay_asg_ytd := g_bal_val(1).bal_val + g_bal_val(19).bal_val ;
2978
2979 -- l_employee_tax_asg_ytd changed for Holiday Pay Changes
2980 -- l_employee_tax_asg_ytd := g_bal_val(2).bal_val ;
2981 l_employee_tax_asg_ytd := g_bal_val(2).bal_val + g_bal_val(20).bal_val ;
2982
2983
2984 l_holidayable_pay_asg_ytd := g_bal_val(3).bal_val ;
2985 l_emp_atp_dedn_asg_ytd := g_bal_val(4).bal_val ;
2986
2987
2988 -- l_emp_sp_dedn_asg_ytd changed for Holiday Pay Changes
2989 -- l_emp_sp_dedn_asg_ytd := g_bal_val(5).bal_val ;
2990 l_emp_sp_dedn_asg_ytd := g_bal_val(5).bal_val + g_bal_val(21).bal_val ;
2991
2992 l_emplr_pension_dedn_asg_ytd := g_bal_val(6).bal_val ;
2993 l_emp_pension_dedn_asg_ytd := g_bal_val(7).bal_val ;
2994 l_total_pension_asg_ytd := l_emp_pension_dedn_asg_ytd + l_emplr_pension_dedn_asg_ytd ;
2995
2996 -- Bug Fix 5080969
2997 -- AMB Contribution ytd = EMPLOYEE_AMB_DEDUCTION_ASG_YTD + HOLIDAY_AMB_ASG_YTD
2998 -- l_emp_amb_dedn_asg_ytd := g_bal_val(8).bal_val ;
2999 l_emp_amb_dedn_asg_ytd := g_bal_val(8).bal_val + g_bal_val(29).bal_val;
3000
3001 --l_calc_holiday_pay_asg_ytd := g_bal_val(9).bal_val ;
3002 l_hol_rem_with_pay_asg_ytd := g_bal_val(10).bal_val - g_bal_val(11).bal_val ;
3003 l_hol_rem_without_pay := g_bal_val(12).bal_val ;
3004
3005 l_total_g_dage_pay_asg_ytd := g_bal_val(13).bal_val ;
3006 l_total_g_dage_days_asg_ytd := g_bal_val(14).bal_val ;
3007
3008 -- l_rest_amount_of_f_card := greatest( (g_tax_card_tab(4).screen_entry_val - g_bal_val(15).bal_val) , 0 ) ;
3009 l_rest_amount_of_f_card := greatest( ( nvl(g_tax_card_tab(4).screen_entry_val,0) - g_bal_val(15).bal_val) , 0 ) ;
3010 -- Tax Free Allowance (from Tax Card) - Taxable Income Year to date
3011
3012 -- Bug Fix 4704284 : start
3013
3014 -- l_taxable_pay_asg_ptd := g_bal_val(16).bal_val ;
3015
3016
3017 -- Additional Balances for Holiday Pay Changes
3018 -- Taxable Income = EMPLOYEE_TAXABLE_BASE_ASG_PTD + ( l_income_from_hol_Pay - HOLIDAY_AMB_ASG_PTD - HOLIDAY_SP_ASG_PTD )
3019
3020 -- if employee is salaried ,
3021 -- then l_income_from_hol_Pay = Salaried Hol Accrual Amount_ASG_PTD
3022 -- + Salaried Hol Curr Entit Amount_ASG_PTD
3023 -- + Salaried Hol Next Entit Amount_ASG_PTD )
3024 -- else (employee is hourly paid)
3025 -- then l_income_from_hol_Pay = HOLIDAY_BANK_PAY_ASG_PTD
3026
3027 l_income_from_hol_Pay_hr := g_bal_val(22).bal_val ;
3028 l_income_from_hol_Pay_sal := g_bal_val(26).bal_val + g_bal_val(27).bal_val + g_bal_val(28).bal_val ;
3029
3030 /* pgopal - Bug 5747199,hourly/salaried issue fix*/
3031
3032 /*SELECT decode (l_period_type, 'Calendar Month', l_income_from_hol_Pay_sal, l_income_from_hol_Pay_hr)
3033 INTO l_income_from_hol_Pay
3034 FROM dual ;*/
3035
3036 l_hourly_salaried := pay_dk_general.get_hour_sal_flag(p_assignment_id,p_effective_date);
3037
3038 IF l_hourly_salaried IS NULL THEN
3039 IF l_period_type = 'Calendar Month' THEN
3040 l_hourly_salaried := 'S';
3041 ELSE
3042 l_hourly_salaried := 'H';
3043 END IF ;
3044 END IF ;
3045
3046 SELECT decode (l_hourly_salaried, 'S', l_income_from_hol_Pay_sal, l_income_from_hol_Pay_hr)
3047 INTO l_income_from_hol_Pay
3048 FROM dual ;
3049
3050
3051 -- l_emp_taxable_base_asg_ptd changed for Holiday Pay Changes
3052 -- l_emp_taxable_base_asg_ptd := g_bal_val(16).bal_val ;
3053 -- l_emp_taxable_base_asg_ptd := g_bal_val(16).bal_val + ( g_bal_val(22).bal_val - g_bal_val(23).bal_val - g_bal_val(24).bal_val ) ;
3054 l_emp_taxable_base_asg_ptd := g_bal_val(16).bal_val + ( l_income_from_hol_Pay - g_bal_val(23).bal_val - g_bal_val(24).bal_val ) ;
3055
3056
3057 -- Bug Fix 4704284 : end
3058
3059 -- l_employee_tax_asg_ptd changed for Holiday Pay Changes
3060 -- l_employee_tax_asg_ptd := g_bal_val(17).bal_val ;
3061 l_employee_tax_asg_ptd := g_bal_val(17).bal_val + g_bal_val(25).bal_val ;
3062
3063
3064 -- Tax percentage value already fetched above
3065 -- l_tax_percent := g_tax_card_tab(3).screen_entry_val ; -- from tax card
3066 -- fnd_file.put_line(fnd_file.log,' DK EMPLOYEE DETAILS : l_tax_percent = '|| l_tax_percent);
3067
3068
3069 -- fnd_file.put_line(fnd_file.log,'finish asigning balance values');
3070
3071 OPEN csr_payroll (p_payroll_action_id) ;
3072 FETCH csr_payroll INTO l_payroll_id;
3073 CLOSE csr_payroll ;
3074
3075 -- fnd_file.put_line(fnd_file.log,'closed csr_payroll');
3076
3077 OPEN csr_payroll_details (l_payroll_id);
3078 FETCH csr_payroll_details INTO l_payroll_name , l_period_type ;
3079 CLOSE csr_payroll_details ;
3080
3081 -- fnd_file.put_line(fnd_file.log,'closed csr_payroll_details');
3082
3083 SELECT decode (l_period_type
3084 ,'Calendar Month',g_tax_card_tab(5).screen_entry_val
3085 ,'Bi-Week',g_tax_card_tab(6).screen_entry_val
3086 ,'Week',g_tax_card_tab(7).screen_entry_val
3087 ,'Lunar Month',g_tax_card_tab(5).screen_entry_val) /* Changes for Lunar Payroll*/
3088 INTO l_tax_deduction
3089 FROM dual ;
3090
3091 -- l_calc_holiday_pay_asg_ytd := g_bal_val(9).bal_val ;
3092
3093 -- g_bal_val(9).bal_val => 'HOLIDAY_ACCRUAL_AMOUNT_ASG_YTD' -- FOR Calculated holiday pay ytd (Salaried)
3094 -- g_bal_val(18).bal_val => 'HOLIDAY_ACCRUAL_PAY_ASG_YTD' -- FOR Calculated holiday pay ytd (Hourly Paid)
3095
3096 /* pgopal - Bug 5747199,hourly/salaried issue fix*/
3097
3098 /*SELECT decode (l_period_type ,
3099 'Calendar Month',
3100 g_bal_val(9).bal_val ,
3101 g_bal_val(18).bal_val)
3102 INTO l_calc_holiday_pay_asg_ytd
3103 FROM dual ;*/
3104
3105 SELECT decode (l_hourly_salaried, 'S',
3106 g_bal_val(9).bal_val ,
3107 g_bal_val(18).bal_val)
3108 INTO l_calc_holiday_pay_asg_ytd
3109 FROM dual ;
3110
3111 /*
3112 SELECT decode (l_period_type ,
3113 'Calendar Month',
3114 707 ,
3115 808 )
3116 INTO l_calc_holiday_pay_asg_ytd
3117 FROM dual ;
3118 */
3119
3120 -- fnd_file.put_line(fnd_file.log,'after the select decode');
3121
3122 -- fnd_file.put_line(fnd_file.log,'starting archiving DK EMPLOYEE DETAILS');
3123
3124 pay_action_information_api.create_action_information (
3125 p_action_information_id => l_action_info_id
3126 ,p_action_context_id => p_archive_assact_id
3127 ,p_action_context_type => 'AAP'
3128 ,p_object_version_number => l_ovn
3129 ,p_effective_date => p_effective_date
3130 ,p_source_id => NULL
3131 ,p_source_text => NULL
3132 ,p_action_information_category => 'DK EMPLOYEE DETAILS'
3133 ,p_action_information1 => l_ambable_pay_asg_ytd -- AMB able income ytd
3134 ,p_action_information2 => l_employee_tax_asg_ytd -- Tax ytd
3135 ,p_action_information3 => l_holidayable_pay_asg_ytd -- Holiday able income ytd
3136 ,p_action_information4 => l_emp_atp_dedn_asg_ytd -- ATP contribution ytd
3137 ,p_action_information5 => l_emp_sp_dedn_asg_ytd -- Special Pension ytd
3138 ,p_action_information6 => l_total_pension_asg_ytd -- Total Pension ytd
3139 ,p_action_information7 => l_emplr_pension_dedn_asg_ytd -- Employer Pension ytd
3140 ,p_action_information8 => l_emp_pension_dedn_asg_ytd -- Employee Pension ytd
3141 ,p_action_information9 => l_emp_amb_dedn_asg_ytd -- AMB Contribution ytd
3142 ,p_action_information10 => l_calc_holiday_pay_asg_ytd -- Calculated holiday pay ytd
3143 ,p_action_information11 => l_hol_rem_with_pay_asg_ytd -- Holidays remaining with pay
3144 ,p_action_information12 => l_hol_rem_without_pay -- Holidays remaining without pay
3145 ,p_action_information13 => l_total_g_dage_pay_asg_ytd -- G-day's (money)
3146 ,p_action_information14 => l_total_g_dage_days_asg_ytd -- G-day's (number of day's)
3147 --,p_action_information15 => l_time_off_in_lieu_hours -- Time off in lieu hours
3148 ,p_action_information15 => l_rest_amount_of_f_card -- Rest Amount of F Card
3149 -- Bug Fix 4704284 : start
3150 --,p_action_information16 => l_taxable_pay_asg_ptd -- Taxable Income
3151 ,p_action_information16 => l_emp_taxable_base_asg_ptd -- Taxable Income
3152 -- Bug Fix 4704284 : end
3153 ,p_action_information17 => l_employee_tax_asg_ptd -- Tax in period
3154 ,p_action_information18 => l_tax_deduction -- Tax Deduction
3155 ,p_action_information19 => l_tax_percent -- Tax Percent
3156 --,p_action_information21 => l_net_pay -- Net Pay
3157 ,p_assignment_id => p_assignment_id);
3158
3159 -- fnd_file.put_line(fnd_file.log,'finished archiving DK EMPLOYEE DETAILS');
3160 -- fnd_file.put_line(fnd_file.log,'leaving ARCHIVE_ADDL_EMP_DETAILS');
3161
3162
3163 END ARCHIVE_ADDL_EMP_DETAILS;
3164
3165 --------------------------------------- PROCEDURE ARCHIVE_MAIN_ELEMENTS ---------------------------------------------------------
3166
3167 /* ARCHIVE EARNINGS AND DEDUCTIONS ELEMENTS REGION */
3168
3169 PROCEDURE ARCHIVE_MAIN_ELEMENTS
3170 (p_archive_assact_id IN NUMBER,
3171 p_assignment_action_id IN NUMBER,
3172 p_assignment_id IN NUMBER,
3173 p_date_earned IN DATE,
3174 p_effective_date IN DATE ) IS
3175
3176 ----------------
3177
3178 /* Cursor to retrieve Earnings Element Information */
3179
3180 CURSOR csr_ear_element_info IS
3181 SELECT nvl(pettl.reporting_name,pettl.element_name) rep_name
3182 ,et.element_type_id element_type_id
3183 ,iv.input_value_id input_value_id
3184 ,iv.uom uom
3185 ,et.processing_priority processing_priority
3186 FROM pay_element_types_f et
3187 , pay_element_types_f_tl pettl
3188 , pay_input_values_f iv
3189 , pay_element_classifications classification
3190 WHERE et.element_type_id = iv.element_type_id
3191 AND et.element_type_id = pettl.element_type_id
3192 AND pettl.language = USERENV('LANG')
3193 AND iv.name = 'Pay Value'
3194 AND classification.classification_id = et.classification_id
3195 AND classification.classification_name
3196 IN ('Direct Payments','Income','Special Pay')
3197 AND p_date_earned BETWEEN et.effective_start_date AND et.effective_end_date
3198 AND p_date_earned BETWEEN iv.effective_start_date AND iv.effective_end_date
3199 AND ((et.business_group_id IS NULL AND et.legislation_code = 'DK')
3200 OR (et.business_group_id = g_business_group_id AND et.legislation_code IS NULL));
3201
3202 ---------------
3203
3204 /* Cursor to retrieve Deduction Element Information */
3205 CURSOR csr_ded_element_info IS
3206 SELECT nvl(pettl.reporting_name,pettl.element_name) rep_name
3207 ,et.element_type_id element_type_id
3208 ,iv.input_value_id input_value_id
3209 ,iv.uom uom
3210 ,et.processing_priority processing_priority
3211 FROM pay_element_types_f et
3212 , pay_element_types_f_tl pettl
3213 , pay_input_values_f iv
3214 , pay_element_classifications classification
3215 WHERE et.element_type_id = iv.element_type_id
3216 AND et.element_type_id = pettl.element_type_id
3217 AND pettl.language = USERENV('LANG')
3218 AND iv.name = 'Pay Value'
3219 AND classification.classification_id = et.classification_id
3220 AND classification.classification_name
3221 IN ('Involuntary Deductions','Pre-Tax Deductions','Statutory Deductions','Voluntary Deductions')
3222 AND p_date_earned BETWEEN et.effective_start_date AND et.effective_end_date
3223 AND p_date_earned BETWEEN iv.effective_start_date AND iv.effective_end_date
3224 AND ((et.business_group_id IS NULL AND et.legislation_code = 'DK')
3225 OR (et.business_group_id = g_business_group_id AND et.legislation_code IS NULL));
3226
3227 ---------------
3228
3229 /* Cursor to retrieve run result value of Main Elements */
3230 /* Modified for Pension changes */
3231 CURSOR csr_result_value(p_iv_id NUMBER
3232 ,p_ele_type_id NUMBER
3233 ,p_assignment_action_id NUMBER) IS
3234 SELECT rrv.result_value
3235 /* Added for Pension changes */
3236 , rr.run_result_id
3237 FROM pay_run_result_values rrv
3238 ,pay_run_results rr
3239 ,pay_assignment_actions paa
3240 ,pay_payroll_actions ppa
3241 WHERE rrv.input_value_id = p_iv_id
3242 AND rr.element_type_id = p_ele_type_id
3243 AND rr.run_result_id = rrv.run_result_id
3244 AND rr.assignment_action_id = paa.assignment_action_id
3245 AND paa.assignment_action_id = p_assignment_action_id
3246 AND ppa.payroll_action_id = paa.payroll_action_id
3247 AND ppa.action_type IN ('Q','R')
3248 AND rrv.result_value IS NOT NULL;
3249
3250 /* Added for Pension changes */
3251 CURSOR csr_get_ded_pen_dtl(p_effective_date DATE
3252 ,p_element_type_id NUMBER
3253 ,p_input_value_id NUMBER
3254 ) IS
3255 SELECT pai.action_information_id
3256 FROM pay_action_information pai
3257 WHERE pai.action_context_type = 'PA'
3258 AND pai.effective_date = p_effective_date
3259 AND pai.action_information_category = 'EMEA ELEMENT DEFINITION'
3260 AND pai.action_information2 = to_char(p_element_type_id)
3261 AND pai.action_information3 = to_char(p_input_value_id)
3262 AND pai.action_information5 = 'D'
3263 AND pai.action_information7 = 'D';
3264
3265 /* Added for Pension changes */
3266 CURSOR csr_get_pen_iv_id(p_effective_date DATE
3267 ,p_element_type_id NUMBER) IS
3268 SELECT piv.input_value_id
3269 FROM pay_input_values_f piv
3270 WHERE piv.element_type_id = p_element_type_id
3271 AND piv.name= 'Third Party Payee'
3272 AND p_effective_date between piv.effective_start_date and piv.effective_end_date;
3273
3274
3275 CURSOR csr_get_pp_name(p_effective_date DATE ,
3276 p_run_result_id NUMBER) IS
3277 SELECT hou.name
3278 FROM
3279 pay_run_result_values rrv
3280 , pay_input_values_f iv
3281 , hr_organization_units hou
3282 WHERE rrv.run_result_id = p_run_result_id
3283 AND rrv.input_value_id = iv.input_value_id
3284 AND iv.name = 'Third Party Payee'
3285 AND p_effective_date between
3286 iv.effective_start_date and iv.effective_end_date
3287 AND hou.organization_id = fnd_number.canonical_to_number(rrv.result_value)
3288 AND p_effective_date between hou.date_from and nvl(hou.date_to, p_effective_date);
3289
3290 ---------------
3291
3292 l_result_value pay_run_result_values.result_value%TYPE := 0;
3293 l_action_info_id NUMBER;
3294 l_ovn NUMBER;
3295 l_element_context VARCHAR2(10);
3296 l_index NUMBER := 0;
3297 l_formatted_value VARCHAR2(50) := NULL;
3298 l_flag NUMBER := 0;
3299
3300 /* Added for Pension changes */
3301
3302 l_ovn_pen NUMBER;
3303 l_iv_id_pen NUMBER :=0;
3304 l_action_info_id_pen NUMBER:=0;
3305 l_new_rep_name VARCHAR2(80) ;
3306 l_rr_id_pen NUMBER :=0;
3307 l_ele_pen_context VARCHAR2(3);
3308 l_ele_pen_context_desc VARCHAR2(80);
3309 rec_get_pp_name csr_get_pp_name%ROWTYPE;
3310 ----------------
3311
3312 BEGIN
3313
3314 IF g_debug THEN
3315 hr_utility.set_location(' Entering Procedure ARCHIVE_MAIN_ELEMENTS',320);
3316 END IF;
3317
3318 -- Archiving Earnings Elements
3319
3320 FOR csr_rec IN csr_ear_element_info LOOP
3321
3322 l_result_value := NULL;
3323
3324 BEGIN
3325 /*
3326 OPEN csr_result_value(csr_rec.input_value_id ,csr_rec.element_type_id ,p_assignment_action_id);
3327 FETCH csr_result_value INTO l_result_value;
3328 CLOSE csr_result_value;
3329 */
3330
3331 -- Fix to handle Multiple Element Entries
3332
3333 /* get the element run result value */
3334 OPEN csr_result_value(csr_rec.input_value_id ,csr_rec.element_type_id ,p_assignment_action_id);
3335 LOOP
3336 /* Added for Pension changes */
3337 FETCH csr_result_value INTO l_result_value,l_rr_id_pen;
3338 EXIT WHEN csr_result_value%NOTFOUND;
3339
3340
3341 IF l_result_value is not null THEN
3342 pay_action_information_api.create_action_information (
3343 p_action_information_id => l_action_info_id
3344 ,p_action_context_id => p_archive_assact_id
3345 ,p_action_context_type => 'AAP'
3346 ,p_object_version_number => l_ovn
3347 ,p_effective_date => p_effective_date
3348 ,p_source_id => NULL
3349 ,p_source_text => NULL
3350 ,p_action_information_category => 'EMEA ELEMENT INFO'
3351 ,p_action_information1 => csr_rec.element_type_id
3352 ,p_action_information2 => csr_rec.input_value_id
3353 ,p_action_information3 => 'E'
3354 ,p_action_information4 => l_result_value --l_formatted_value
3355 ,p_action_information9 => 'Earning Element'
3356 ,p_assignment_id => p_assignment_id
3357 ,p_action_information8 => csr_rec.processing_priority
3358 );
3359 END IF;
3360
3361 END LOOP;
3362 CLOSE csr_result_value;
3363
3364 -- End Fix to handle Multiple Element Entries
3365
3366 EXCEPTION WHEN OTHERS THEN
3367 g_err_num := SQLCODE;
3368 /*fnd_file.put_line(fnd_file.log,'ORA_ERR: ' || g_err_num || 'In ARCHIVE_MAIN_ELEMENTS');*/
3369
3370 IF g_debug THEN
3371 hr_utility.set_location('ORA_ERR: ' || g_err_num || 'In ARCHIVE_MAIN_ELEMENTS',330);
3372 END IF;
3373 END;
3374 END LOOP;
3375
3376
3377
3378 -- Archiving Deduction Elements
3379
3380 FOR csr_rec IN csr_ded_element_info LOOP
3381
3382 l_result_value := NULL;
3383
3384 BEGIN
3385 /*
3386 OPEN csr_result_value(csr_rec.input_value_id ,csr_rec.element_type_id ,p_assignment_action_id);
3387 FETCH csr_result_value INTO l_result_value;
3388 CLOSE csr_result_value;
3389 */
3390
3391 -- Fix to handle Multiple Element Entries
3392
3393 /* get the element run result value */
3394 OPEN csr_result_value(csr_rec.input_value_id ,csr_rec.element_type_id ,p_assignment_action_id);
3395 LOOP
3396 /* Added for Pension changes */
3397 FETCH csr_result_value INTO l_result_value,l_rr_id_pen;
3398 EXIT WHEN csr_result_value%NOTFOUND;
3399
3400 /* Added for Pension changes -start */
3401
3402 IF( csr_rec.rep_name IN ('Pension','Employer Pension','Retro Pension','Retro Employer Pension')) THEN
3403
3404 OPEN csr_get_pen_iv_id(p_effective_date ,csr_rec.element_type_id);
3405 FETCH csr_get_pen_iv_id INTO l_iv_id_pen;
3406 CLOSE csr_get_pen_iv_id;
3407
3408 OPEN csr_get_pp_name(p_effective_date , l_rr_id_pen);
3409 FETCH csr_get_pp_name INTO rec_get_pp_name;
3410 CLOSE csr_get_pp_name;
3411
3412 OPEN csr_get_ded_pen_dtl(p_effective_date ,csr_rec.element_type_id ,csr_rec.input_value_id);
3413 FETCH csr_get_ded_pen_dtl INTO l_action_info_id_pen;
3414 CLOSE csr_get_ded_pen_dtl;
3415
3416 /* Commented to avoid updating the element definition
3417 l_new_rep_name := csr_rec.rep_name|| ' ( ' ||rec_get_pp_name.name|| ' ) ' ;
3418
3419 pay_action_information_api.update_action_information(
3420 p_action_information_id => l_action_info_id_pen
3421 ,p_object_version_number => l_ovn_pen
3422 ,p_action_information4 => l_new_rep_name); */
3423
3424 l_ele_pen_context:='PP';
3425 l_ele_pen_context_desc:= rec_get_pp_name.name;
3426
3427
3428 Else
3429 l_ele_pen_context:='D';
3430 l_ele_pen_context_desc:='Deduction Element';
3431
3432
3433
3434 END IF;
3435
3436 /* Added for Pension changes -end */
3437
3438
3439 IF l_result_value is not null THEN
3440 pay_action_information_api.create_action_information (
3441 p_action_information_id => l_action_info_id
3442 ,p_action_context_id => p_archive_assact_id
3443 ,p_action_context_type => 'AAP'
3444 ,p_object_version_number => l_ovn
3445 ,p_effective_date => p_effective_date
3446 ,p_source_id => NULL
3447 ,p_source_text => NULL
3448 ,p_action_information_category => 'EMEA ELEMENT INFO'
3449 ,p_action_information1 => csr_rec.element_type_id
3450 ,p_action_information2 => csr_rec.input_value_id
3451 ,p_action_information3 => l_ele_pen_context -- Added for Pension Changes
3452 ,p_action_information4 => l_result_value --l_formatted_value
3453 ,p_action_information9 => l_ele_pen_context_desc -- Added for Pension Changes
3454 ,p_assignment_id => p_assignment_id
3455 ,p_action_information8 => csr_rec.processing_priority
3456 );
3457 END IF;
3458
3459 END LOOP;
3460 CLOSE csr_result_value;
3461
3462 -- End Fix to handle Multiple Element Entries
3463
3464 EXCEPTION WHEN OTHERS THEN
3465 g_err_num := SQLCODE;
3466 /*fnd_file.put_line(fnd_file.log,'ORA_ERR: ' || g_err_num || 'In ARCHIVE_MAIN_ELEMENTS');*/
3467
3468 IF g_debug THEN
3469 hr_utility.set_location('ORA_ERR: ' || g_err_num || 'In ARCHIVE_MAIN_ELEMENTS',330);
3470 END IF;
3471 END;
3472 END LOOP;
3473
3474
3475 IF g_debug THEN
3476 hr_utility.set_location(' Leaving Procedure ARCHIVE_MAIN_ELEMENTS',340);
3477 END IF;
3478
3479 END ARCHIVE_MAIN_ELEMENTS;
3480
3481 ------------------------------------ End of package ----------------------------------------------------------------
3482
3483 END PAY_DK_ARCHIVE;