[Home] [Help]
PACKAGE BODY: APPS.PAY_DK_ARCHIVE
Source
1 PACKAGE BODY PAY_DK_ARCHIVE AS
2 /* $Header: pydkparc.pkb 120.30 2012/01/19 09:04:10 rpahune ship $ */
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 ,code.org_information2||','||hoi.org_information7 element_narrative --changes to payslip w.r.t bug - 7229247
215 ,pec.classification_name
216 ,piv.uom
217 FROM hr_organization_information hoi
218 ,hr_organization_information code
219 ,pay_element_classifications pec
220 ,pay_element_types_f pet
221 ,pay_input_values_f piv
222 WHERE hoi.organization_id = p_bus_grp_id
223 AND hoi.org_information_context = 'Business Group:Payslip Info'
224 AND hoi.org_information1 = 'ELEMENT'
225 AND hoi.org_information2 = pet.element_type_id
226 AND pec.classification_id = pet.classification_id
227 AND piv.input_value_id = hoi.org_information3
228 AND p_date_earned BETWEEN piv.effective_start_date AND piv.effective_end_date
229 AND code.organization_id (+)= p_bus_grp_id --changes to payslip w.r.t bug - 7229247
230 AND code.org_information_context (+)='DK_SOE_ELEMENT_ADD_DETAILS' --changes to payslip w.r.t bug - 7229247
231 AND pet.element_type_id = code.org_information1(+); --changes to payslip w.r.t bug - 7229247
232
233
234 --------------
235
236 rec_time_periods csr_time_periods%ROWTYPE;
237 rec_get_balance csr_get_balance%ROWTYPE;
238 rec_get_message csr_get_message%ROWTYPE;
239 rec_get_element csr_get_element%ROWTYPE;
240 l_action_info_id NUMBER;
241 l_ovn NUMBER;
242 l_business_group_id NUMBER;
243 l_start_date VARCHAR2(30);
244 l_end_date VARCHAR2(30);
245 l_effective_date DATE;
246 l_consolidation_set NUMBER;
247 l_defined_balance_id NUMBER := 0;
248 l_count NUMBER := 0;
249 l_prev_prepay NUMBER := 0;
250 l_canonical_start_date DATE;
251 l_canonical_end_date DATE;
252 l_payroll_id NUMBER;
253 l_prepay_action_id NUMBER;
254 l_actid NUMBER;
255 l_assignment_id NUMBER;
256 l_action_sequence NUMBER;
257 l_assact_id NUMBER;
258 l_pact_id NUMBER;
259 l_flag NUMBER := 0;
260 l_element_context VARCHAR2(5);
261
262 ----------------
263
264 BEGIN
265 -- fnd_file.put_line(fnd_file.log,'Entering Procedure RANGE_CODE');
266 IF g_debug THEN
267 hr_utility.set_location(' Entering Procedure RANGE_CODE',40);
268 END IF;
269
270 PAY_DK_ARCHIVE.GET_ALL_PARAMETERS(p_payroll_action_id
271 ,l_business_group_id
272 ,l_start_date
273 ,l_end_date
274 ,l_effective_date
275 ,l_payroll_id
276 ,l_consolidation_set);
277
278 l_canonical_start_date := TO_DATE(l_start_date,'YYYY/MM/DD');
279 l_canonical_end_date := TO_DATE(l_end_date,'YYYY/MM/DD');
280
281 -- get the messages from Busineess Group:Payslip Info
282 OPEN csr_get_message(l_business_group_id);
283 LOOP
284 FETCH csr_get_message INTO rec_get_message;
285 EXIT WHEN csr_get_message%NOTFOUND;
286
287 -- archive the messages
288 pay_action_information_api.create_action_information (
289 p_action_information_id => l_action_info_id
290 ,p_action_context_id => p_payroll_action_id
291 ,p_action_context_type => 'PA'
292 ,p_object_version_number => l_ovn
293 ,p_effective_date => l_effective_date
294 ,p_source_id => NULL
295 ,p_source_text => NULL
296 ,p_action_information_category => 'EMPLOYEE OTHER INFORMATION'
297 ,p_action_information1 => l_business_group_id
298 ,p_action_information2 => 'MESG' -- Message Context
299 ,p_action_information3 => NULL
300 ,p_action_information4 => NULL
301 ,p_action_information5 => NULL
302 ,p_action_information6 => rec_get_message.message);
303
304 END LOOP;
305 CLOSE csr_get_message;
306
307 -------------------------------------------------------------------------------------
308 -- Initialize Balance Definitions
309 -------------------------------------------------------------------------------------
310
311 -- get the balances from Busineess Group:Payslip Info
312 OPEN csr_get_balance(l_business_group_id);
313 LOOP
314 FETCH csr_get_balance INTO rec_get_balance;
315 EXIT WHEN csr_get_balance%NOTFOUND;
316
317 -- get the defined balance id for the balances got above
318 OPEN csr_def_balance(rec_get_balance.balance_type_id,rec_get_balance.balance_dim_id);
319 FETCH csr_def_balance INTO l_defined_balance_id;
320 CLOSE csr_def_balance;
321
322 BEGIN
323 -- check if the balance has already been archived
324 SELECT 1 INTO l_flag
325 FROM pay_action_information
326 WHERE action_information_category = 'EMEA BALANCE DEFINITION'
327 AND action_context_id = p_payroll_action_id
328 AND action_information2 = l_defined_balance_id
329 AND action_information6 = 'OBAL'
330 AND action_information4 = rec_get_balance.narrative;
331
332 EXCEPTION WHEN NO_DATA_FOUND THEN
333
334 -- archive the balance definition as it has not been archived before
335 pay_action_information_api.create_action_information (
336 p_action_information_id => l_action_info_id
337 ,p_action_context_id => p_payroll_action_id
338 ,p_action_context_type => 'PA'
339 ,p_object_version_number => l_ovn
340 ,p_effective_date => l_effective_date
341 ,p_source_id => NULL
342 ,p_source_text => NULL
343 ,p_action_information_category => 'EMEA BALANCE DEFINITION'
344 ,p_action_information1 => NULL
345 ,p_action_information2 => l_defined_balance_id
346 ,p_action_information4 => rec_get_balance.narrative
347 ,p_action_information6 => 'OBAL');
348
349 WHEN OTHERS THEN
350 NULL;
351 END;
352
353 END LOOP;
354 CLOSE csr_get_balance;
355
356
357
358 -----------------------------------------------------------------------------
359 --Initialize Element Definitions
360 -----------------------------------------------------------------------------
361
362 g_business_group_id := l_business_group_id;
363
364 ARCHIVE_ELEMENT_INFO(p_payroll_action_id => p_payroll_action_id
365 ,p_effective_date => l_effective_date
366 ,p_date_earned => l_canonical_end_date
367 ,p_pre_payact_id => NULL);
368
369 -----------------------------------------------------------------------------
370 --Archive Additional Element Definitions
371 -----------------------------------------------------------------------------
372
373 l_element_context := 'F';
374
375 OPEN csr_get_element(l_business_group_id,l_canonical_end_date);
376 LOOP
377 FETCH csr_get_element INTO rec_get_element;
378 EXIT WHEN csr_get_element%NOTFOUND;
379 BEGIN
380 -- check if the element definition has already been archived
381 SELECT 1 INTO l_flag
382 FROM pay_action_information
383 WHERE action_context_id = p_payroll_action_id
384 AND action_information_category = 'EMEA ELEMENT DEFINITION'
385 AND action_information2 = rec_get_element.element_type_id
386 AND action_information3 = rec_get_element.input_value_id
387 AND action_information5 = l_element_context;
388
389 EXCEPTION WHEN NO_DATA_FOUND THEN
390 -- archive the element definition since it has not been archived
391
392 pay_action_information_api.create_action_information (
393 p_action_information_id => l_action_info_id
394 ,p_action_context_id => p_payroll_action_id
395 ,p_action_context_type => 'PA'
396 ,p_object_version_number => l_ovn
397 ,p_effective_date => l_effective_date
398 ,p_source_id => NULL
399 ,p_source_text => NULL
400 ,p_action_information_category => 'EMEA ELEMENT DEFINITION'
401 ,p_action_information1 => NULL
402 ,p_action_information2 => rec_get_element.element_type_id
403 ,p_action_information3 => rec_get_element.input_value_id
404 ,p_action_information4 => rec_get_element.element_narrative
405 ,p_action_information5 => l_element_context
406 ,p_action_information6 => rec_get_element.uom
407 ,p_action_information7 => l_element_context);
408
409 WHEN OTHERS THEN
410 NULL;
411 END;
412
413 END LOOP;
414 CLOSE csr_get_element;
415
416 p_sql := 'SELECT DISTINCT person_id
417 FROM per_people_f ppf
418 ,pay_payroll_actions ppa
419 WHERE ppa.payroll_action_id = :payroll_action_id
420 AND ppa.business_group_id = ppf.business_group_id
421 ORDER BY ppf.person_id';
422
423 IF g_debug THEN
424 hr_utility.set_location(' Leaving Procedure RANGE_CODE',50);
425 END IF;
426
427 -- fnd_file.put_line(fnd_file.log,'Leaving Procedure RANGE_CODE');
428
429 EXCEPTION
430 WHEN OTHERS THEN
431 -- Return cursor that selects no rows
432 p_sql := 'select 1 from dual where to_char(:payroll_action_id) = dummy';
433
434 END RANGE_CODE;
435
436 ---------------------------------- PROCEDURE ASSIGNMENT_ACTION_CODE ----------------------------------------------------------------
437
438 /* ASSIGNMENT ACTION CODE */
439 PROCEDURE ASSIGNMENT_ACTION_CODE
440 (p_payroll_action_id IN NUMBER
441 ,p_start_person IN NUMBER
442 ,p_end_person IN NUMBER
443 ,p_chunk IN NUMBER)
444 IS
445
446 -----------
447
448 CURSOR csr_prepaid_assignments(p_payroll_action_id NUMBER,
449 p_start_person NUMBER,
450 p_end_person NUMBER,
451 p_payroll_id NUMBER,
452 p_consolidation_id NUMBER,
453 l_canonical_start_date DATE,
454 l_canonical_end_date DATE)
455 IS
456 SELECT act.assignment_id assignment_id,
457 act.assignment_action_id run_action_id,
458 act1.assignment_action_id prepaid_action_id
459 FROM pay_payroll_actions ppa,
460 pay_payroll_actions appa,
461 pay_payroll_actions appa2,
462 pay_assignment_actions act,
463 pay_assignment_actions act1,
464 pay_action_interlocks pai,
465 per_all_assignments_f as1
466 WHERE ppa.payroll_action_id = p_payroll_action_id
467 AND appa.consolidation_set_id = p_consolidation_id
468 AND appa.effective_date BETWEEN l_canonical_start_date AND l_canonical_end_date
469 AND as1.person_id BETWEEN p_start_person AND p_end_person
470 AND appa.action_type IN ('R','Q') -- Payroll Run or Quickpay Run
471 AND act.payroll_action_id = appa.payroll_action_id
472 AND act.source_action_id IS NULL -- Master Action
473 AND as1.assignment_id = act.assignment_id
474 AND ppa.effective_date BETWEEN as1.effective_start_date AND as1.effective_end_date
475 AND act.action_status IN ('C','S') -- 10229494
476 AND act.assignment_action_id = pai.locked_action_id
477 AND act1.assignment_action_id = pai.locking_action_id
478 AND act1.action_status IN ('C','S') -- 10229494
479 AND act1.payroll_action_id = appa2.payroll_action_id
480 AND appa2.action_type IN ('P','U') -- Prepayments or Quickpay Prepayments
481 AND appa2.effective_date BETWEEN l_canonical_start_date AND l_canonical_end_date
482 AND (as1.payroll_id = p_payroll_id OR p_payroll_id IS NULL)
483
484 AND NOT EXISTS (SELECT /* + ORDERED */ NULL
485 FROM pay_action_interlocks pai1,
486 pay_assignment_actions act2,
487 pay_payroll_actions appa3
488 WHERE pai1.locked_action_id = act.assignment_action_id
489 AND act2.assignment_action_id= pai1.locking_action_id
490 AND act2.payroll_action_id = appa3.payroll_action_id
491 AND appa3.action_type = 'X'
492 AND appa3.action_status = 'C'
493 AND appa3.report_type = 'PYDKARCHIVE')
494
495 AND NOT EXISTS ( SELECT /* + ORDERED */ NULL
496 FROM pay_action_interlocks pai1,
497 pay_assignment_actions act2,
498 pay_payroll_actions appa3
499 WHERE pai1.locked_action_id = act.assignment_action_id
500 AND act2.assignment_action_id= pai1.locking_action_id
501 AND act2.payroll_action_id = appa3.payroll_action_id
502 AND appa3.action_type = 'V'
503 AND appa3.action_status = 'C')
504
505 ORDER BY act.assignment_id;
506
507 -----------
508
509 l_count NUMBER := 0;
510 l_prev_prepay NUMBER := 0;
511 l_business_group_id NUMBER;
512 l_start_date VARCHAR2(20);
513 l_end_date VARCHAR2(20);
514 l_canonical_start_date DATE;
515 l_canonical_end_date DATE;
516 l_effective_date DATE;
517 l_payroll_id NUMBER;
518 l_consolidation_set NUMBER;
519 l_prepay_action_id NUMBER;
520 l_actid NUMBER;
521 l_assignment_id NUMBER;
522 l_action_sequence NUMBER;
523 l_assact_id NUMBER;
524 l_pact_id NUMBER;
525 l_flag NUMBER := 0;
526 l_defined_balance_id NUMBER := 0;
527 l_action_info_id NUMBER;
528 l_ovn NUMBER;
529 ----------------
530
531 BEGIN
532
533 -- fnd_file.put_line(fnd_file.log,'Entering Procedure ASSIGNMENT_ACTION_CODE');
534 IF g_debug THEN
535 hr_utility.set_location(' Entering Procedure ASSIGNMENT_ACTION_CODE',60);
536 END IF;
537
538 PAY_DK_ARCHIVE.GET_ALL_PARAMETERS(p_payroll_action_id
539 ,l_business_group_id
540 ,l_start_date
541 ,l_end_date
542 ,l_effective_date
543 ,l_payroll_id
544 ,l_consolidation_set);
545
546 l_canonical_start_date := TO_DATE(l_start_date,'YYYY/MM/DD');
547 l_canonical_end_date := TO_DATE(l_end_date,'YYYY/MM/DD');
548 l_prepay_action_id := 0;
549
550 FOR rec_prepaid_assignments IN csr_prepaid_assignments(p_payroll_action_id
551 ,p_start_person
552 ,p_end_person
553 ,l_payroll_id
554 ,l_consolidation_set
555 ,l_canonical_start_date
556 ,l_canonical_end_date) LOOP
557
558 IF l_prepay_action_id <> rec_prepaid_assignments.prepaid_action_id THEN
559
560 SELECT pay_assignment_actions_s.NEXTVAL
561 INTO l_actid
562 FROM dual;
563 --
564 g_index_assact := g_index_assact + 1;
565 g_lock_table(g_index_assact).archive_assact_id := l_actid; /* For Element archival */
566
567 -- Create the archive assignment action
568 hr_nonrun_asact.insact(l_actid
569 ,rec_prepaid_assignments.assignment_id
570 ,p_payroll_action_id
571 ,p_chunk
572 ,NULL);
573 -- Create archive to prepayment assignment action interlock
574 --
575 hr_nonrun_asact.insint(l_actid,rec_prepaid_assignments.prepaid_action_id);
576 END IF;
577
578 -- create archive to master assignment action interlock
579 hr_nonrun_asact.insint(l_actid,rec_prepaid_assignments.run_action_id);
580 l_prepay_action_id := rec_prepaid_assignments.prepaid_action_id;
581
582 END LOOP;
583
584 IF g_debug THEN
585 hr_utility.set_location(' Leaving Procedure ASSIGNMENT_ACTION_CODE',70);
586 END IF;
587 -- fnd_file.put_line(fnd_file.log,'Leaving Procedure ASSIGNMENT_ACTION_CODE');
588
589 END ASSIGNMENT_ACTION_CODE;
590
591 ------------------------------------- PROCEDURE INITIALIZATION_CODE -------------------------------------------------------------
592
593 /* INITIALIZATION CODE */
594
595 PROCEDURE INITIALIZATION_CODE(p_payroll_action_id IN NUMBER)
596 IS
597
598 -------------
599
600 CURSOR csr_prepay_id IS
601 SELECT distinct prepay_payact.payroll_action_id prepay_payact_id
602 ,run_payact.date_earned date_earned
603 FROM pay_action_interlocks archive_intlck
604 ,pay_assignment_actions prepay_assact
605 ,pay_payroll_actions prepay_payact
606 ,pay_action_interlocks prepay_intlck
607 ,pay_assignment_actions run_assact
608 ,pay_payroll_actions run_payact
609 ,pay_assignment_actions archive_assact
610 WHERE archive_intlck.locking_action_id = archive_assact.assignment_action_id
611 and archive_assact.payroll_action_id = p_payroll_action_id
612 AND prepay_assact.assignment_action_id = archive_intlck.locked_action_id
613 AND prepay_payact.payroll_action_id = prepay_assact.payroll_action_id
614 AND prepay_payact.action_type IN ('U','P')
615 AND prepay_intlck.locking_action_id = prepay_assact.assignment_action_id
616 AND run_assact.assignment_action_id = prepay_intlck.locked_action_id
617 AND run_payact.payroll_action_id = run_assact.payroll_action_id
618 AND run_payact.action_type IN ('Q', 'R')
619 ORDER BY prepay_payact.payroll_action_id;
620
621 --------------
622
623 /* Cursor to retrieve Run Assignment Action Ids */
624 CURSOR csr_runact_id IS
625 SELECT distinct prepay_payact.payroll_action_id prepay_payact_id
626 ,run_payact.date_earned date_earned
627 ,run_payact.payroll_action_id run_payact_id
628 FROM pay_action_interlocks archive_intlck
629 ,pay_assignment_actions prepay_assact
630 ,pay_payroll_actions prepay_payact
631 ,pay_action_interlocks prepay_intlck
632 ,pay_assignment_actions run_assact
633 ,pay_payroll_actions run_payact
634 ,pay_assignment_actions archive_assact
635 WHERE archive_intlck.locking_action_id = archive_assact.assignment_action_id
636 and archive_assact.payroll_action_id = p_payroll_action_id
637 AND prepay_assact.assignment_action_id = archive_intlck.locked_action_id
638 AND prepay_payact.payroll_action_id = prepay_assact.payroll_action_id
639 AND prepay_payact.action_type IN ('U','P')
640 AND prepay_intlck.locking_action_id = prepay_assact.assignment_action_id
641 AND run_assact.assignment_action_id = prepay_intlck.locked_action_id
642 AND run_payact.payroll_action_id = run_assact.payroll_action_id
643 AND run_payact.action_type IN ('Q', 'R')
644 ORDER BY prepay_payact.payroll_action_id;
645
646 -------------
647
648 rec_prepay_id csr_prepay_id%ROWTYPE;
649 rec_runact_id csr_runact_id%ROWTYPE;
650 l_action_info_id NUMBER;
651 l_ovn NUMBER;
652 l_count NUMBER := 0;
653 l_business_group_id NUMBER;
654 l_start_date VARCHAR2(20);
655 l_end_date VARCHAR2(20);
656 l_effective_date DATE;
657 l_payroll_id NUMBER;
658 l_consolidation_set NUMBER;
659 l_prev_prepay NUMBER := 0;
660
661 ---------------
662
663 BEGIN
664
665 -- fnd_file.put_line(fnd_file.log,'Entering Procedure INITIALIZATION_CODE');
666
667 IF g_debug THEN
668 hr_utility.set_location(' Entering Procedure INITIALIZATION_CODE',80);
669 END IF;
670
671 /*fnd_file.put_line(fnd_file.log,'In INIT_CODE 0');*/
672
673
674
675 GET_ALL_PARAMETERS(p_payroll_action_id
676 ,l_business_group_id
677 ,l_start_date
678 ,l_end_date
679 ,l_effective_date
680 ,l_payroll_id
681 ,l_consolidation_set);
682
683 g_arc_payroll_action_id := p_payroll_action_id;
684 g_business_group_id := l_business_group_id;
685
686 /* Archive Element Details */
687 OPEN csr_prepay_id;
688 LOOP
689 FETCH csr_prepay_id INTO rec_prepay_id;
690 EXIT WHEN csr_prepay_id%NOTFOUND;
691 ---------------------------------------------------------
692 --Initialize Global tables once every prepayment payroll
693 --action id and once every thread
694 ---------------------------------------------------------
695 IF (rec_prepay_id.prepay_payact_id <> l_prev_prepay) THEN
696 ARCHIVE_ADD_ELEMENT(p_archive_assact_id => NULL,
697 p_assignment_action_id => NULL,
698 p_assignment_id => NULL,
699 p_payroll_action_id => p_payroll_action_id,
700 p_date_earned => rec_prepay_id.date_earned,
701 p_effective_date => l_effective_date,
702 p_pre_payact_id => rec_prepay_id.prepay_payact_id,
703 p_archive_flag => 'N');
704
705 END IF;
706
707 l_prev_prepay := rec_prepay_id.prepay_payact_id;
708 END LOOP;
709
710 CLOSE csr_prepay_id;
711
712 /* Initialize Global tables for Balances */
713 ARCHIVE_OTH_BALANCE(p_archive_assact_id => NULL,
714 p_assignment_action_id => NULL,
715 p_assignment_id => NULL,
716 p_payroll_action_id => p_payroll_action_id,
717 p_record_count => NULL,
718 p_pre_payact_id => NULL, --rec_prepay_id.prepay_payact_id,
719 p_effective_date => l_effective_date,
720 p_date_earned => NULL,
721 p_archive_flag => 'N');
722
723 IF g_debug THEN
724 hr_utility.set_location(' Leaving Procedure INITIALIZATION_CODE',90);
725 END IF;
726 -- fnd_file.put_line(fnd_file.log,'Leaving Procedure INITIALIZATION_CODE');
727
728
729 EXCEPTION WHEN OTHERS THEN
730 g_err_num := SQLCODE;
731 -- fnd_file.put_line(fnd_file.log,'ORA_ERR: ' || g_err_num || 'In INITIALIZATION_CODE');
732
733 IF g_debug THEN
734 hr_utility.set_location('ORA_ERR: ' || g_err_num || 'In INITIALIZATION_CODE',180);
735 END IF;
736
737 -- fnd_file.put_line(fnd_file.log,'Leaving Procedure INITIALIZATION_CODE');
738
739 END INITIALIZATION_CODE;
740
741 ------------------------------------- PROCEDURE SETUP_ELEMENT_DEFINITIONS -------------------------------------------------------------
742
743 PROCEDURE SETUP_ELEMENT_DEFINITIONS( p_classification_name IN VARCHAR2
744 ,p_element_name IN VARCHAR2
745 ,p_element_type_id IN NUMBER
746 ,p_input_value_id IN NUMBER
747 ,p_element_type IN VARCHAR2
748 ,p_uom IN VARCHAR2
749 ,p_archive_flag IN VARCHAR2)
750 IS
751
752 BEGIN
753
754 IF g_debug THEN
755 hr_utility.set_location(' Entering Procedure SETUP_ELEMENT_DEFINITIONS',100);
756 END IF;
757
758 g_index := g_index + 1;
759 /* Initialize global tables that hold Additional Element details */
760 g_element_table(g_index).classification_name := p_classification_name;
761 g_element_table(g_index).element_name := p_element_name;
762 g_element_table(g_index).element_type := p_element_type;
763 g_element_table(g_index).element_type_id := p_element_type_id;
764 g_element_table(g_index).input_value_id := p_input_value_id;
765 g_element_table(g_index).uom := p_uom;
766 g_element_table(g_index).archive_flag := p_archive_flag;
767
768 IF g_debug THEN
769 hr_utility.set_location(' Leaving Procedure SETUP_ELEMENT_DEFINITIONS',110);
770 END IF;
771
772 END SETUP_ELEMENT_DEFINITIONS;
773
774 ------------------------------------ PROCEDURE SETUP_BALANCE_DEFINITIONS --------------------------------------------------------------
775
776 PROCEDURE SETUP_BALANCE_DEFINITIONS(p_balance_name IN VARCHAR2
777 ,p_defined_balance_id IN NUMBER
778 ,p_balance_type_id IN NUMBER)
779 IS
780 BEGIN
781
782 IF g_debug THEN
783 hr_utility.set_location(' Entering Procedure SETUP_BALANCE_DEFINITIONS',120);
784 END IF;
785
786 g_index_bal := g_index_bal + 1;
787 /* Initialize global tables that hold Other Balances details */
788 g_user_balance_table(g_index_bal).balance_name := p_balance_name;
789 g_user_balance_table(g_index_bal).defined_balance_id := p_defined_balance_id;
790 g_user_balance_table(g_index_bal).balance_type_id := p_balance_type_id;
791
792 --fnd_file.put_line(fnd_file.log,'SETUP_BALANCE_DEFINITIONS ' ||p_balance_name);
793
794 IF g_debug THEN
795 hr_utility.set_location(' Leaving Procedure SETUP_BALANCE_DEFINITIONS',130);
796 END IF;
797
798 END SETUP_BALANCE_DEFINITIONS;
799
800 ------------------------------------ FUNCTION GET_COUNTRY_NAME --------------------------------------------------------------
801
802 /* GET COUNTRY NAME FROM CODE */
803
804 FUNCTION GET_COUNTRY_NAME(p_territory_code VARCHAR2)
805 RETURN VARCHAR2
806 IS
807
808 CURSOR csr_get_territory_name(p_territory_code VARCHAR2) Is
809 SELECT territory_short_name
810 FROM fnd_territories_vl
811 WHERE territory_code = p_territory_code;
812
813 l_country fnd_territories_vl.territory_short_name%TYPE;
814
815 BEGIN
816
817 IF g_debug THEN
818 hr_utility.set_location(' Entering Function GET_COUNTRY_NAME',140);
819 END IF;
820
821 OPEN csr_get_territory_name(p_territory_code);
822 FETCH csr_get_territory_name into l_country;
823 CLOSE csr_get_territory_name;
824
825 RETURN l_country;
826
827 IF g_debug THEN
828 hr_utility.set_location(' Leaving Function GET_COUNTRY_NAME',150);
829 END IF;
830
831 END GET_COUNTRY_NAME;
832
833 --------------------------------------- PROCEDURE ARCHIVE_EMPLOYEE_DETAILS -----------------------------------------------------------
834
835 /* EMPLOYEE DETAILS REGION */
836
837 PROCEDURE ARCHIVE_EMPLOYEE_DETAILS (p_archive_assact_id IN NUMBER
838 ,p_assignment_id IN NUMBER
839 ,p_assignment_action_id IN NUMBER
840 ,p_payroll_action_id IN NUMBER
841 ,p_time_period_id IN NUMBER
842 ,p_date_earned IN DATE
843 ,p_pay_date_earned IN DATE
844 ,p_effective_date IN DATE) IS
845
846 -------------
847 /* Cursor to retrieve person details about Employee */
848 CURSOR csr_person_details(p_assignment_id NUMBER) IS
849 SELECT ppf.person_id person_id,
850 ppf.full_name full_name,
851 ppf.national_identifier ni_number,
852 ppf.nationality nationality,
853 pps.date_start start_date,
854 ppf.employee_number emp_num,
855 ppf.first_name first_name,
856 ppf.last_name last_name,
857 ppf.title title,
858 paf.location_id loc_id,
859 paf.organization_id org_id, -- HR Org at Asg level
860 paf.job_id job_id,
861 paf.position_id pos_id,
862 paf.grade_id grade_id,
863 paf.business_group_id bus_grp_id
864 FROM per_assignments_f paf,
865 per_all_people_f ppf,
866 per_periods_of_service pps
867 WHERE paf.person_id = ppf.person_id
868 AND paf.assignment_id = p_assignment_id
869 AND pps.person_id = ppf.person_id
870 AND p_date_earned BETWEEN paf.effective_start_date AND paf.effective_end_date
871 AND p_date_earned BETWEEN ppf.effective_start_date AND ppf.effective_end_date;
872
873 -------------
874 /* Cursor to retrieve primary address of Employee */
875 CURSOR csr_primary_address(p_person_id NUMBER) IS
876 SELECT pa.person_id person_id,
877 pa.style style,
878 pa.address_type ad_type,
879 pa.country country,
880 pa.region_1 R1,
881 pa.region_2 R2,
882 pa.region_3 R3,
883 pa.town_or_city city,
884 pa.address_line1 AL1,
885 pa.address_line2 AL2,
886 pa.address_line3 AL3,
887 pa.postal_code postal_code
888 FROM per_addresses pa
889 WHERE pa.primary_flag = 'Y'
890 AND pa.person_id = p_person_id
891 AND p_effective_date BETWEEN pa.date_from AND NVL(pa.date_to,to_date('31-12-4712','DD-MM-YYYY'));
892
893 -------------
894 /* Cursor to retrieve Employer's Address */
895 CURSOR csr_employer_address(p_organization_id NUMBER) IS
896 SELECT hla.style style
897 ,hla.country country
898 ,hla.address_line_1 AL1
899 ,hla.address_line_2 AL2
900 ,hla.address_line_3 AL3
901 ,hla.postal_code postal_code
902 FROM hr_locations_all hla
903 ,hr_organization_units hou
904 WHERE hou.organization_id = p_organization_id
905 AND hou.location_id = hla.location_id;
906 -------------
907 CURSOR csr_organization_address(p_organization_id NUMBER) IS
908 SELECT hla.style style
909 ,hla.address_line_1 AL1
910 ,hla.address_line_2 AL2
911 ,hla.address_line_3 AL3
912 ,hla.country country
913 ,hla.postal_code postal_code
914 FROM hr_locations_all hla,
915 hr_organization_units hoa
916 WHERE hla.location_id = hoa.location_id
917 AND hoa.organization_id = p_organization_id
918 AND p_effective_date BETWEEN hoa.date_from AND NVL(hoa.date_to,to_date('31-12-4712','DD-MM-YYYY'));
919
920 --------------
921 /* Cursor to retrieve Business Group Id */
922 CURSOR csr_bus_grp_id(p_organization_id NUMBER) IS
923 SELECT business_group_id
924 FROM hr_organization_units
925 WHERE organization_id = p_organization_id;
926 --------------
927 /* Cursor to retrieve Currency */
928 CURSOR csr_currency(p_bg_id NUMBER) IS
929 SELECT org_information10
930 FROM hr_organization_information
931 WHERE organization_id = p_bg_id
932 AND org_information_context = 'Business Group Information';
933
934 --------------
935 l_bg_id NUMBER;
936 --------------
937
938 /*
939 CURSOR csr_legal_employer (p_organization_id NUMBER) IS
940 SELECT hoi3.organization_id
941 FROM HR_ORGANIZATION_UNITS o1
942 , HR_ORGANIZATION_INFORMATION hoi1
943 , HR_ORGANIZATION_INFORMATION hoi2
944 , HR_ORGANIZATION_INFORMATION hoi3
945 WHERE o1.business_group_id =l_bg_id
946 AND hoi1.organization_id = o1.organization_id
947 AND hoi1.organization_id = p_organization_id
948 AND hoi1.org_information1 = 'DK_LOCAL_UNIT'
949 AND hoi1.org_information_context = 'CLASS'
950 AND o1.organization_id = hoi2.org_information1
951 AND hoi2.ORG_INFORMATION_CONTEXT='DK_LOCAL_UNITS'
952 AND hoi2.organization_id = hoi3.organization_id
953 AND hoi3.ORG_INFORMATION_CONTEXT='CLASS'
954 AND hoi3.org_information1 = 'HR_LEGAL_EMPLOYER';
955 */
956
957 -------------
958 /* Cursor to retrieve Grade of Employee */
959 CURSOR csr_grade(p_grade_id NUMBER) IS
960 SELECT pg.name
961 FROM per_grades pg
962 WHERE pg.grade_id = p_grade_id;
963 -------------
964 /* Cursor to retrieve Position of Employee */
965 CURSOR csr_position(p_position_id NUMBER) IS
966 SELECT pap.name
967 FROM per_all_positions pap
968 WHERE pap.position_id = p_position_id;
969 -------------
970 CURSOR csr_job (p_job_id NUMBER)IS
971 SELECT name
972 FROM per_jobs
973 WHERE job_id = p_job_id;
974 -------------
975 /* Cursor to retrieve Cost Center */
976 CURSOR csr_cost_center(p_assignment_id NUMBER) IS
977 SELECT concatenated_segments
978 FROM pay_cost_allocations_v
979 WHERE assignment_id=p_assignment_id
980 AND p_date_earned BETWEEN effective_start_date AND effective_end_date;
981 -------------
982 /* Cursor to pick up Payroll Location */
983 CURSOR csr_pay_location(p_location_id NUMBER) IS
984 SELECT location_code location
985 FROM hr_locations_all
986 WHERE location_id = p_location_id;
987 -------------
988 /* Cursor to pick Hire Date*/
989 CURSOR csr_hire_date (p_assignment_id NUMBER) IS
990 SELECT date_start
991 FROM per_periods_of_service pps,
992 per_all_assignments_f paa
993 WHERE pps.period_of_service_id = paa.period_of_service_id
994 AND p_date_earned between paa.effective_start_date and paa.effective_end_date
995 AND paa.assignment_id = p_assignment_id;
996 -------------
997 /*Cursor to pick local unit*/
998 /*
999 cursor csr_scl_details (p_assignment_id NUMBER) IS
1000 SELECT segment2
1001 from per_all_assignments_f paaf
1002 ,HR_SOFT_CODING_KEYFLEX hsck
1003 where paaf.assignment_id= p_assignment_id
1004 and p_date_earned BETWEEN paaf.effective_start_date and paaf.effective_end_date
1005 and paaf.SOFT_CODING_KEYFLEX_ID = hsck.SOFT_CODING_KEYFLEX_ID;
1006 */
1007
1008 /*Cursor to pick Legal Employer */
1009
1010 cursor csr_scl_details (p_assignment_id NUMBER) IS
1011 SELECT segment1
1012 from per_all_assignments_f paaf
1013 ,HR_SOFT_CODING_KEYFLEX hsck
1014 where paaf.assignment_id= p_assignment_id
1015 and p_date_earned BETWEEN paaf.effective_start_date and paaf.effective_end_date
1016 and paaf.SOFT_CODING_KEYFLEX_ID = hsck.SOFT_CODING_KEYFLEX_ID;
1017
1018 --------------
1019
1020 /*Cursor to pick Legal Employer Phone Numbers*/
1021
1022 CURSOR csr_le_phone (l_legal_employer_id NUMBER) IS
1023 SELECT org_information3 le_phone_num
1024 FROM hr_organization_information hoi
1025 WHERE organization_id = l_legal_employer_id
1026 AND org_information_context = 'ORG_CONTACT_DETAILS'
1027 AND org_information1 = 'PHONE' ;
1028
1029 --------------
1030
1031 /*Cursor to pick Legal Employer CVR Number */
1032
1033 CURSOR csr_le_cvr (l_legal_employer_id NUMBER) IS
1034 SELECT ORG_INFORMATION1
1035 FROM hr_organization_information hoi
1036 WHERE organization_id = l_legal_employer_id
1037 AND org_information_context = 'DK_LEGAL_ENTITY_DETAILS' ;
1038
1039 --------------
1040
1041 /* Cursor added to fetch the organization name */
1042
1043 CURSOR csr_org_name (org_id NUMBER) IS
1044 SELECT name
1045 FROM hr_organization_units
1046 WHERE organization_id = org_id ;
1047
1048 -------------
1049 rec_person_details csr_person_details%ROWTYPE;
1050 rec_primary_address csr_primary_address%ROWTYPE;
1051 rec_employer_address csr_employer_address%ROWTYPE;
1052 rec_org_address csr_organization_address%ROWTYPE;
1053 l_nationality per_all_people_f.nationality%TYPE;
1054 l_position per_all_positions.name%TYPE;
1055 l_hire_date per_periods_of_service.date_start%TYPE;
1056 l_grade per_grades.name%TYPE;
1057 l_currency hr_organization_information.org_information10%TYPE;
1058 l_organization hr_organization_units.name%TYPE;
1059 l_pay_location hr_locations_all.address_line_1%TYPE;
1060 l_postal_code VARCHAR2(80);
1061 l_country VARCHAR2(30);
1062 l_emp_postal_code VARCHAR2(80);
1063 l_emp_country VARCHAR2(30);
1064 l_org_city VARCHAR2(20);
1065 l_org_country VARCHAR2(30);
1066 l_action_info_id NUMBER;
1067 l_ovn NUMBER;
1068 l_person_id NUMBER;
1069 l_employer_name hr_organization_units.name%TYPE;
1070 l_local_unit_id hr_organization_units.organization_id%TYPE;
1071 l_legal_employer_id hr_organization_units.organization_id%TYPE;
1072 l_job PER_JOBS.NAME%TYPE;
1073 l_org_struct_ver_id hr_organization_information.org_information1%TYPE;
1074 l_top_org_id per_org_structure_elements.organization_id_parent%TYPE;
1075 l_cost_center pay_cost_allocations_v.concatenated_segments%TYPE;
1076 l_defined_balance_id NUMBER;
1077 l_balance_value NUMBER;
1078 l_formatted_value VARCHAR2(50) := NULL;
1079 l_org_exists NUMBER :=0;
1080 le_phone_num VARCHAR2(240);
1081 le_phone_num_str VARCHAR2(1000);
1082 l_cvr_num VARCHAR2(240);
1083 -- l_lower_base NUMBER :=0;
1084 -- l_upper_base NUMBER :=0;
1085 -------------
1086
1087 BEGIN
1088
1089 IF g_debug THEN
1090 hr_utility.set_location(' Entering Procedure ARCHIVE_EMPLOYEE_DETAILS',160);
1091 END IF;
1092
1093 /*fnd_file.put_line(fnd_file.log,'Entering Procedure ARCHIVE_EMPLOYEE_DETAILS');*/
1094
1095
1096 /* PERSON AND ADDRESS DETAILS */
1097 OPEN csr_person_details(p_assignment_id);
1098 FETCH csr_person_details INTO rec_person_details;
1099 CLOSE csr_person_details;
1100
1101 OPEN csr_primary_address(rec_person_details.person_id);
1102 FETCH csr_primary_address INTO rec_primary_address;
1103 CLOSE csr_primary_address;
1104
1105 -- rec_person_details.org_id is the org_id of the HR org at asg level
1106
1107 OPEN csr_organization_address(rec_person_details.org_id);
1108 FETCH csr_organization_address INTO rec_org_address;
1109 CLOSE csr_organization_address;
1110
1111 /*fnd_file.put_line(fnd_file.log,'Entering Procedure ARCHIVE_EMPLOYEE_DETAILS 2');*/
1112
1113 /* GRADE AND POSITION */
1114
1115 /* Changed IF condition construct to fix Bug 3583862 */
1116 IF(rec_person_details.pos_id IS NOT NULL) THEN
1117 OPEN csr_position(rec_person_details.pos_id);
1118 FETCH csr_position INTO l_position;
1119 CLOSE csr_position;
1120 END IF;
1121
1122 IF(rec_person_details.grade_id IS NOT NULL) THEN
1123 OPEN csr_grade(rec_person_details.grade_id);
1124 FETCH csr_grade INTO l_grade;
1125 CLOSE csr_grade;
1126 END IF;
1127
1128 /*fnd_file.put_line(fnd_file.log,'Entering Procedure ARCHIVE_EMPLOYEE_DETAILS 3');*/
1129
1130 /* CURRENCY */
1131
1132
1133 -- rec_person_details.org_id is the org_id of the HR org at asg level
1134 OPEN csr_bus_grp_id(rec_person_details.org_id);
1135 FETCH csr_bus_grp_id INTO l_bg_id;
1136 CLOSE csr_bus_grp_id;
1137
1138 OPEN csr_currency(l_bg_id);
1139 FETCH csr_currency INTO l_currency;
1140 CLOSE csr_currency;
1141
1142 g_format_mask := FND_CURRENCY.GET_FORMAT_MASK(l_currency,40);
1143
1144 /* COST CENTER */
1145 OPEN csr_cost_center(p_assignment_id);
1146 FETCH csr_cost_center INTO l_cost_center;
1147 CLOSE csr_cost_center;
1148
1149
1150 /* HIRE DATE */
1151 OPEN csr_hire_date(p_assignment_id);
1152 FETCH csr_hire_date INTO l_hire_date;
1153 CLOSE csr_hire_date;
1154
1155 /*NATIONALITY*/
1156 l_nationality := hr_general.decode_lookup('NATIONALITY',rec_person_details.nationality);
1157
1158 /*fnd_file.put_line(fnd_file.log,'Entering Procedure ARCHIVE_EMPLOYEE_DETAILS 4');*/
1159
1160 /*Local Unit*/
1161 /*
1162 OPEN csr_scl_details(p_assignment_id);
1163 FETCH csr_scl_details INTO l_local_unit_id;
1164 CLOSE csr_scl_details;
1165 */
1166
1167 /*
1168 OPEN csr_legal_employer(l_local_unit_id);
1169 FETCH csr_legal_employer INTO l_legal_employer_id;
1170 CLOSE csr_legal_employer;
1171 */
1172
1173 /*Legal Employer */
1174
1175 OPEN csr_scl_details(p_assignment_id);
1176 FETCH csr_scl_details INTO l_legal_employer_id ;
1177 CLOSE csr_scl_details;
1178
1179
1180 OPEN csr_employer_address(l_legal_employer_id);
1181 FETCH csr_employer_address INTO rec_employer_address;
1182 CLOSE csr_employer_address;
1183
1184 IF(rec_person_details.loc_id IS NOT NULL) THEN
1185 l_pay_location := NULL;
1186
1187 OPEN csr_pay_location(rec_person_details.loc_id);
1188 FETCH csr_pay_location INTO l_pay_location;
1189 CLOSE csr_pay_location;
1190 ELSE
1191 l_pay_location := NULL;
1192 END IF;
1193
1194
1195 IF(rec_person_details.job_id IS NOT NULL) THEN
1196
1197 OPEN csr_job(rec_person_details.job_id);
1198 FETCH csr_job INTO l_job;
1199 CLOSE csr_job;
1200 ELSE
1201 l_job := NULL;
1202 END IF;
1203
1204 /*
1205 -- HR ORG at asg level Name
1206 SELECT name INTO l_organization
1207 FROM hr_organization_units
1208 WHERE organization_id = rec_person_details.org_id;
1209
1210 -- Legal Employer Name
1211 SELECT name INTO l_employer_name
1212 FROM hr_organization_units
1213 WHERE organization_id = l_legal_employer_id;
1214 */
1215
1216 -- HR ORG at asg level Name
1217 OPEN csr_org_name (rec_person_details.org_id) ;
1218 FETCH csr_org_name INTO l_organization ;
1219 CLOSE csr_org_name ;
1220
1221 -- Legal Employer Name
1222 OPEN csr_org_name (l_legal_employer_id) ;
1223 FETCH csr_org_name INTO l_employer_name ;
1224 CLOSE csr_org_name ;
1225
1226
1227 /*fnd_file.put_line(fnd_file.log,'Entering Procedure ARCHIVE_EMPLOYEE_DETAILS 5');*/
1228
1229 IF rec_primary_address.style = 'DK' THEN
1230 l_postal_code := hr_general.decode_lookup('DK_POSTCODE_TOWN',rec_primary_address.postal_code);
1231 ELSIF rec_primary_address.style = 'DK_GLB' and rec_primary_address.city is not null THEN /* 10327065 start */
1232 l_postal_code := rec_primary_address.postal_code || ' ' || rec_primary_address.city;
1233 /* 10327065 end */
1234 ELSE
1235 l_postal_code := rec_primary_address.postal_code;
1236 END IF;
1237
1238 l_country := PAY_DK_ARCHIVE.get_country_name(rec_primary_address.country);
1239
1240 IF rec_employer_address.style = 'DK' THEN
1241 l_emp_postal_code := hr_general.decode_lookup('DK_POSTCODE_TOWN',rec_employer_address.postal_code);
1242 ELSE
1243 l_emp_postal_code := rec_employer_address.postal_code;
1244 END IF;
1245
1246 l_emp_country := PAY_DK_ARCHIVE.get_country_name(rec_employer_address.country);
1247
1248 /* Getting Legal Employer Phone Number String */
1249
1250 le_phone_num_str := NULL;
1251
1252 OPEN csr_le_phone (l_legal_employer_id);
1253 LOOP
1254 FETCH csr_le_phone INTO le_phone_num;
1255 EXIT WHEN csr_le_phone%NOTFOUND;
1256
1257 IF (csr_le_phone%ROWCOUNT = 1)
1258 THEN
1259 le_phone_num_str := le_phone_num ;
1260 ELSE
1261 le_phone_num_str := le_phone_num_str || ' , ' || le_phone_num ;
1262 END IF;
1263
1264 END LOOP;
1265 CLOSE csr_le_phone;
1266
1267 /* Get Legal Employer CVR Number */
1268
1269 OPEN csr_le_cvr (l_legal_employer_id ) ;
1270 FETCH csr_le_cvr INTO l_cvr_num ;
1271 CLOSE csr_le_cvr ;
1272
1273
1274 /*fnd_file.put_line(fnd_file.log,'Entering Procedure ARCHIVE_EMPLOYEE_DETAILS gosi reference');*/
1275
1276 /* INSERT PERSON DETAILS */
1277
1278 pay_action_information_api.create_action_information (
1279 p_action_information_id => l_action_info_id
1280 ,p_action_context_id => p_archive_assact_id
1281 ,p_action_context_type => 'AAP'
1282 ,p_object_version_number => l_ovn
1283 ,p_effective_date => p_effective_date
1284 ,p_source_id => NULL
1285 ,p_source_text => NULL
1286 ,p_action_information_category => 'EMPLOYEE DETAILS'
1287 ,p_action_information1 => rec_person_details.full_name
1288 ,p_action_information2 => l_legal_employer_id -- rec_person_details.org_id -- org_id of HR Org at asg level
1289 ,p_action_information4 => rec_person_details.ni_number
1290 ,p_action_information7 => l_grade
1291 ,p_action_information10 => rec_person_details.emp_num
1292 ,p_action_information12 => fnd_date.date_to_displaydate(l_hire_date) -- fnd_date.date_to_canonical(l_hire_date)
1293 ,p_action_information15 => l_organization -- name of HR Org at asg level
1294 ,p_action_information16 => p_time_period_id
1295 ,p_action_information17 => l_job
1296 ,p_action_information18 => l_employer_name -- Legal Employer Name
1297 ,p_action_information19 => l_position
1298 ,p_action_information25 => le_phone_num_str
1299 ,p_action_information30 => l_pay_location
1300 ,p_assignment_id => p_assignment_id);
1301
1302
1303 /* INSERT ADDRESS DETAILS */
1304 IF rec_primary_address.AL1 IS NOT NULL THEN /* CHECK IF EMPLOYEE HAS BEEN GIVEN A PRIMARY ADDRESS */
1305 pay_action_information_api.create_action_information (
1306 p_action_information_id => l_action_info_id
1307 ,p_action_context_id => p_archive_assact_id
1308 ,p_action_context_type => 'AAP'
1309 ,p_object_version_number => l_ovn
1310 ,p_effective_date => p_effective_date
1311 ,p_source_id => NULL
1312 ,p_source_text => NULL
1313 ,p_action_information_category => 'ADDRESS DETAILS'
1314 ,p_action_information1 => rec_primary_address.person_id
1315 ,p_action_information5 => rec_primary_address.AL1
1316 ,p_action_information6 => rec_primary_address.AL2
1317 ,p_action_information7 => rec_primary_address.AL3
1318 ,p_action_information8 => rec_primary_address.city -- 10327065
1319 ,p_action_information12 => l_postal_code
1320 ,p_action_information13 => l_country
1321 ,p_action_information14 => 'Employee Address'
1322 ,p_assignment_id => p_assignment_id);
1323 ELSE
1324 /* INSERT EMPLOYER ADDRESS AS EMPLOYEE'S PRIMARY ADDRESS */
1325 pay_action_information_api.create_action_information (
1326 p_action_information_id => l_action_info_id
1327 ,p_action_context_id => p_archive_assact_id
1328 ,p_action_context_type => 'AAP'
1329 ,p_object_version_number => l_ovn
1330 ,p_effective_date => p_effective_date
1331 ,p_source_id => NULL
1332 ,p_source_text => NULL
1333 ,p_action_information_category => 'ADDRESS DETAILS'
1334 ,p_action_information1 => rec_primary_address.person_id
1335 ,p_action_information5 => NULL
1336 ,p_action_information6 => NULL
1337 ,p_action_information7 => NULL
1338 ,p_action_information8 => NULL
1339 ,p_action_information9 => NULL
1340 ,p_action_information10 => NULL
1341 ,p_action_information11 => NULL
1342 ,p_action_information12 => NULL
1343 ,p_action_information13 => NULL
1344 ,p_action_information14 => 'Employee Address'
1345 ,p_assignment_id => p_assignment_id);
1346 END IF;
1347
1348 /*fnd_file.put_line(fnd_file.log,'Entering Procedure ARCHIVE_EMPLOYEE_DETAILS 9');*/
1349
1350 /* INSERT EMPLOYER'S ADDRESS (ORGANIZATION ADDRESS)*/
1351
1352 BEGIN
1353 l_org_exists := 0;
1354
1355 SELECT 1
1356 INTO l_org_exists
1357 FROM pay_action_information
1358 WHERE action_context_id = p_payroll_action_id
1359 AND action_information1 = l_legal_employer_id -- rec_person_details.org_id
1360 AND effective_date = p_effective_date
1361 AND action_information_category = 'ADDRESS DETAILS';
1362
1363 EXCEPTION
1364
1365 WHEN NO_DATA_FOUND THEN
1366 pay_action_information_api.create_action_information (
1367 p_action_information_id => l_action_info_id
1368 ,p_action_context_id => p_payroll_action_id
1369 ,p_action_context_type => 'PA'
1370 ,p_object_version_number => l_ovn
1371 ,p_effective_date => p_effective_date
1372 ,p_source_id => NULL
1373 ,p_source_text => NULL
1374 ,p_action_information_category => 'ADDRESS DETAILS'
1375 ,p_action_information1 => l_legal_employer_id -- rec_person_details.org_id
1376 ,p_action_information5 => rec_employer_address.AL1
1377 ,p_action_information6 => rec_employer_address.AL2
1378 ,p_action_information7 => rec_employer_address.AL3
1379 ,p_action_information12 => l_emp_postal_code
1380 ,p_action_information13 => l_emp_country
1381 ,p_action_information14 => 'Employer Address'
1382 ,p_action_information26 => l_cvr_num ); -- using Localization Specific1 for Legal Employer CVR Number
1383
1384 WHEN OTHERS THEN
1385 NULL;
1386 END;
1387
1388 /*fnd_file.put_line(fnd_file.log,'Entering Procedure ARCHIVE_EMPLOYEE_DETAILS 10');*/
1389
1390
1391 --
1392 IF g_debug THEN
1393 hr_utility.set_location(' Leaving Procedure ARCHIVE_EMPLOYEE_DETAILS',170);
1394 END IF;
1395 --
1396
1397 EXCEPTION WHEN OTHERS THEN
1398 g_err_num := SQLCODE;
1399 /*fnd_file.put_line(fnd_file.log,'ORA_ERR: ' || g_err_num || 'In ARCHIVE_EMPLOYEE_DETAILS');*/
1400
1401 IF g_debug THEN
1402 hr_utility.set_location('ORA_ERR: ' || g_err_num || 'In ARCHIVE_EMPLOYEE_DETAILS',180);
1403 END IF;
1404
1405 END ARCHIVE_EMPLOYEE_DETAILS;
1406
1407 ----------------------------------- PROCEDURE ARCHIVE_ELEMENT_INFO ---------------------------------------------------------------
1408
1409 /* EARNINGS REGION, DEDUCTIONS REGION */
1410
1411 PROCEDURE ARCHIVE_ELEMENT_INFO(p_payroll_action_id IN NUMBER
1412 ,p_effective_date IN DATE
1413 ,p_date_earned IN DATE
1414 ,p_pre_payact_id IN NUMBER)
1415 IS
1416 ----------------
1417
1418 /* Cursor to retrieve Earnings Element Information */
1419
1420 CURSOR csr_ear_element_info IS
1421 SELECT code.org_information2||','||nvl(pettl.reporting_name,pettl.element_name) rep_name
1422 ,et.element_type_id element_type_id
1423 ,iv.input_value_id input_value_id
1424 ,iv.uom uom
1425 FROM pay_element_types_f et
1426 , pay_element_types_f_tl pettl
1427 , pay_input_values_f iv
1428 , pay_element_classifications classification
1429 ,hr_organization_information code
1430 WHERE et.element_type_id = iv.element_type_id
1431 AND et.element_type_id = pettl.element_type_id
1432 AND pettl.language = USERENV('LANG')
1433 AND iv.name = 'Pay Value'
1434 AND classification.classification_id = et.classification_id
1435 AND classification.classification_name
1436 IN ('Direct Payments','Income','Special Pay','B Income') /* 8849449 */
1437 AND p_date_earned BETWEEN et.effective_start_date AND et.effective_end_date
1438 AND p_date_earned BETWEEN iv.effective_start_date AND iv.effective_end_date
1439 AND ((et.business_group_id IS NULL AND et.legislation_code = 'DK')
1440 OR (et.business_group_id = g_business_group_id AND et.legislation_code IS NULL))
1441 -- Adding the Conditions below for Payslip Format Changes - w.r.t bug - 7229247
1442 -- Start
1443 and code.organization_id(+) = g_business_group_id
1444 and code.org_information_context (+)='DK_SOE_ELEMENT_ADD_DETAILS'
1445 and et.element_type_id = code.org_information1 (+);
1446 -- End
1447
1448 -----------------
1449
1450 /* Cursor to retrieve Deduction Element Information */
1451 CURSOR csr_ded_element_info IS
1452 SELECT code.org_information2||','||nvl(pettl.reporting_name,pettl.element_name) rep_name
1453 ,et.element_type_id element_type_id
1454 ,iv.input_value_id input_value_id
1455 ,iv.uom uom
1456 FROM pay_element_types_f et
1457 , pay_element_types_f_tl pettl
1458 , pay_input_values_f iv
1459 , pay_element_classifications classification
1460 ,hr_organization_information code
1461 WHERE et.element_type_id = iv.element_type_id
1462 AND et.element_type_id = pettl.element_type_id
1463 AND pettl.language = USERENV('LANG')
1464 AND iv.name = 'Pay Value'
1465 AND classification.classification_id = et.classification_id
1466 AND classification.classification_name
1467 IN ('Involuntary Deductions','Pre-Tax Deductions','Statutory Deductions','Voluntary Deductions')
1468 AND p_date_earned BETWEEN et.effective_start_date AND et.effective_end_date
1469 AND p_date_earned BETWEEN iv.effective_start_date AND iv.effective_end_date
1470 AND ((et.business_group_id IS NULL AND et.legislation_code = 'DK')
1471 OR (et.business_group_id = g_business_group_id AND et.legislation_code IS NULL))
1472 -- Adding the Conditions below for Payslip Format Changes - w.r.t bug - 7229247
1473 -- Start
1474 AND code.organization_id(+) = g_business_group_id
1475 AND code.org_information_context (+)='DK_SOE_ELEMENT_ADD_DETAILS'
1476 AND et.element_type_id = code.org_information1 (+);
1477 -- End
1478
1479 ---------------------
1480 l_action_info_id NUMBER;
1481 l_ovn NUMBER;
1482 l_flag NUMBER := 0;
1483 ----------------------
1484 BEGIN
1485
1486
1487 IF g_debug THEN
1488 hr_utility.set_location(' Entering Procedure ARCHIVE_ELEMENT_INFO',210);
1489 END IF;
1490
1491 ------------------------
1492 /* EARNINGS ELEMENT */
1493
1494 FOR rec_earnings IN csr_ear_element_info LOOP
1495
1496 BEGIN
1497 hr_utility.trace('p_payroll_action_id***************** '||p_payroll_action_id);
1498 -- check if the Element definition has already been archived
1499 SELECT 1 INTO l_flag
1500 FROM pay_action_information
1501 WHERE action_context_id = p_payroll_action_id
1502 AND action_information_category = 'EMEA ELEMENT DEFINITION'
1503 AND action_information2 = rec_earnings.element_type_id
1504 AND action_information3 = rec_earnings.input_value_id
1505 AND action_information5 = 'E';
1506
1507
1508
1509 EXCEPTION WHEN NO_DATA_FOUND THEN
1510 -- archive the element definition as it has not been archived
1511 hr_utility.trace(' rec_earnings.element_type_id ******** '||rec_earnings.element_type_id);
1512
1513 pay_action_information_api.create_action_information (
1514 p_action_information_id => l_action_info_id
1515 ,p_action_context_id => p_payroll_action_id
1516 ,p_action_context_type => 'PA'
1517 ,p_object_version_number => l_ovn
1518 ,p_effective_date => p_effective_date
1519 ,p_source_id => NULL
1520 ,p_source_text => NULL
1521 ,p_action_information_category => 'EMEA ELEMENT DEFINITION'
1522 ,p_action_information1 => p_pre_payact_id
1523 ,p_action_information2 => rec_earnings.element_type_id
1524 ,p_action_information3 => rec_earnings.input_value_id
1525 ,p_action_information4 => rec_earnings.rep_name
1526 ,p_action_information5 => 'E'
1527 ,p_action_information6 => rec_earnings.uom
1528 ,p_action_information7 => 'E'
1529 ); --Earnings Element Context
1530 WHEN OTHERS THEN
1531 NULL;
1532 END;
1533 END LOOP;
1534 ---------------------------
1535 /* DEDUCTION ELEMENT */
1536
1537 FOR rec_deduction IN csr_ded_element_info LOOP
1538 BEGIN
1539 -- check if the Element definition has already been archived
1540 SELECT 1 INTO l_flag
1541 FROM pay_action_information
1542 WHERE action_context_id = p_payroll_action_id
1543 AND action_information_category = 'EMEA ELEMENT DEFINITION'
1544 AND action_information2 = rec_deduction.element_type_id
1545 AND action_information3 = rec_deduction.input_value_id
1546 AND action_information5 = 'D';
1547
1548 EXCEPTION WHEN NO_DATA_FOUND THEN
1549 -- archive the element definition as it has not been archived
1550 pay_action_information_api.create_action_information (
1551 p_action_information_id => l_action_info_id
1552 ,p_action_context_id => p_payroll_action_id
1553 ,p_action_context_type => 'PA'
1554 ,p_object_version_number => l_ovn
1555 ,p_effective_date => p_effective_date
1556 ,p_source_id => NULL
1557 ,p_source_text => NULL
1558 ,p_action_information_category => 'EMEA ELEMENT DEFINITION'
1559 ,p_action_information1 => p_pre_payact_id
1560 ,p_action_information2 => rec_deduction.element_type_id
1561 ,p_action_information3 => rec_deduction.input_value_id
1562 ,p_action_information4 => rec_deduction.rep_name
1563 ,p_action_information5 => 'D'
1564 ,p_action_information6 => rec_deduction.uom
1565 ,p_action_information7 => 'D'
1566 ); --Deduction Element Context
1567
1568 /*WHEN OTHERS THEN
1569 NULL;*/
1570 END;
1571 END LOOP;
1572
1573 IF g_debug THEN
1574 hr_utility.set_location(' Leaving Procedure ARCHIVE_ELEMENT_INFO',220);
1575 END IF;
1576
1577 EXCEPTION WHEN OTHERS THEN
1578 g_err_num := SQLCODE;
1579 /*fnd_file.put_line(fnd_file.log,'ORA_ERR: ' || g_err_num || 'In ARCHIVE_ELEMENT_INFO');*/
1580
1581 IF g_debug THEN
1582 hr_utility.set_location('ORA_ERR: ' || g_err_num || 'ARCHIVE_ELEMENT_INFO',230);
1583 END IF;
1584
1585 END ARCHIVE_ELEMENT_INFO;
1586
1587
1588 ------------------------------------ FUNCTION GET_DEFINED_BALANCE_ID --------------------------------------------------------------
1589
1590 /* GET DEFINED BALANCE ID */
1591
1592 FUNCTION GET_DEFINED_BALANCE_ID(p_user_name IN VARCHAR2) RETURN NUMBER
1593 IS
1594
1595 /* Cursor to retrieve Defined Balance Id */
1596
1597 CURSOR csr_def_bal_id(p_user_name VARCHAR2) IS
1598 SELECT u.creator_id
1599 FROM ff_user_entities u,
1600 ff_database_items d
1601 WHERE d.user_name = p_user_name
1602 AND u.user_entity_id = d.user_entity_id
1603 AND (u.legislation_code = 'DK' )
1604 AND (u.business_group_id IS NULL )
1605 AND u.creator_type = 'B';
1606
1607 l_defined_balance_id ff_user_entities.user_entity_id%TYPE;
1608
1609 BEGIN
1610
1611 IF g_debug THEN
1612 hr_utility.set_location(' Entering Function GET_DEFINED_BALANCE_ID',240);
1613 END IF;
1614
1615 OPEN csr_def_bal_id(p_user_name);
1616 FETCH csr_def_bal_id INTO l_defined_balance_id;
1617 CLOSE csr_def_bal_id;
1618 RETURN l_defined_balance_id;
1619
1620 IF g_debug THEN
1621 hr_utility.set_location(' Leaving Function GET_DEFINED_BALANCE_ID',250);
1622 END IF;
1623
1624 END GET_DEFINED_BALANCE_ID;
1625 --------------------------------------------------------------------------------------------------
1626 /* PAYMENT INFORMATION REGION */
1627 PROCEDURE ARCHIVE_PAYMENT_INFO(p_archive_assact_id IN NUMBER,
1628 p_prepay_assact_id IN NUMBER,
1629 p_assignment_id IN NUMBER,
1630 p_date_earned IN DATE,
1631 p_effective_date IN DATE)
1632 IS
1633 -------------
1634 /* Cursor to fetch ppm and opm ids to check which payment method to archive */
1635 CURSOR csr_chk(p_prepay_assact_id NUMBER) IS
1636 SELECT personal_payment_method_id ppm_id,
1637 org_payment_method_id opm_id
1638 FROM pay_pre_payments
1639 WHERE assignment_action_id = p_prepay_assact_id;
1640
1641 ------------
1642 /* Cursor to check if bank details are attached with ppm */
1643 CURSOR csr_chk_bank(p_ppm_id NUMBER) IS
1644 SELECT ppm.external_account_id
1645 FROM pay_personal_payment_methods_f ppm
1646 WHERE ppm.personal_payment_method_id = p_ppm_id
1647 AND p_date_earned BETWEEN ppm.effective_start_date AND ppm.effective_end_date;
1648 -------------
1649 /* Cursor to retrieve Organization Payment Method Information */
1650 CURSOR csr_get_org_pay(p_prepay_assact_id NUMBER, opm_id NUMBER) IS
1651 SELECT pop.org_payment_method_id opm_id,
1652 pop.org_payment_method_name opm_name,
1653 ppttl.payment_type_name pay_type,
1654 ppp.value value
1655 FROM pay_org_payment_methods_f pop,
1656 pay_assignment_actions paa,
1657 pay_payment_types ppt,
1658 pay_payment_types_tl ppttl,
1659 pay_pre_payments ppp
1660 WHERE paa.assignment_action_id = p_prepay_assact_id
1661 AND ppt.payment_type_id = pop.payment_type_id
1662 AND ppt.payment_type_id = ppttl.payment_type_id
1663 AND ppttl.language = userenv('LANG')
1664 AND ppp.org_payment_method_id = pop.org_payment_method_id
1665 AND pop.org_payment_method_id = opm_id
1666 AND ppp.assignment_action_id = paa.assignment_action_id
1667 AND p_date_earned BETWEEN pop.effective_start_date AND pop.effective_end_date;
1668 -------------
1669 /* Cursor to retrieve Personal Payment Method Info*/
1670 CURSOR csr_get_pers_pay(p_prepay_assact_id NUMBER, ppm_id NUMBER) IS
1671 SELECT pea.segment1 bank_reg_num,
1672 pea.segment2 branch,
1673 pea.segment3 acct_num,
1674 ppm.org_payment_method_id opm_id,
1675 pop.external_account_id,
1676 pop.org_payment_method_name opm_name,
1677 ppm.personal_payment_method_id ppm_id,
1678 ppttl.payment_type_name pay_type,
1679 ppp.value value
1680 FROM pay_external_accounts pea,
1681 pay_org_payment_methods_f pop,
1682 pay_personal_payment_methods_f ppm,
1683 pay_assignment_actions paa,
1684 pay_payment_types ppt,
1685 pay_payment_types_tl ppttl,
1686 pay_pre_payments ppp
1687 WHERE
1688 -- pea.id_flex_num=20 AND
1689 pea.external_account_id = NVL(ppm.external_account_id,pop.external_account_id)
1690 AND paa.assignment_action_id = p_prepay_assact_id
1691 AND paa.assignment_id = ppm.assignment_id
1692 AND ppm.org_payment_method_id = pop.org_payment_method_id
1693 AND ppm.personal_payment_method_id = ppm_id
1694 AND ppt.payment_type_id = pop.payment_type_id
1695 AND ppt.payment_type_id = ppttl.payment_type_id
1696 AND ppttl.language = userenv('LANG')
1697 AND ppp.assignment_action_id = paa.assignment_action_id
1698 AND ppp.personal_payment_method_id = ppm.personal_payment_method_id
1699 AND p_date_earned BETWEEN pop.effective_start_date AND pop.effective_end_date
1700 AND p_date_earned BETWEEN ppm.effective_start_date AND ppm.effective_end_date;
1701 -------------
1702 l_bank_reg_num VARCHAR2(50);
1703 l_action_info_id NUMBER;
1704 l_ovn NUMBER;
1705 l_org NUMBER;
1706 l_pers VARCHAR2(40) := NULL;
1707 l_ext_acct NUMBER;
1708 rec_chk csr_chk%ROWTYPE;
1709 l_pay_value VARCHAR2(50) := NULL;
1710 ------------
1711
1712 BEGIN
1713
1714 IF g_debug THEN
1715 hr_utility.set_location(' Entering Procedure ARCHIVE_PAYMENT_INFO',260);
1716 END IF;
1717
1718 /*fnd_file.put_line(fnd_file.log,'In ARCHIVE_PAYMENT_INFO 1');*/
1719
1720 OPEN csr_chk(p_prepay_assact_id);
1721 LOOP
1722 FETCH csr_chk INTO rec_chk;
1723 EXIT WHEN csr_chk%NOTFOUND;
1724
1725 IF rec_chk.ppm_id IS NOT NULL THEN
1726 FOR rec_pers_pay IN csr_get_pers_pay(p_prepay_assact_id,rec_chk.ppm_id) LOOP
1727
1728 OPEN csr_chk_bank(rec_chk.ppm_id);
1729 FETCH csr_chk_bank INTO l_ext_acct;
1730 CLOSE csr_chk_bank;
1731
1732 l_pay_value := to_char (rec_pers_pay.value,g_format_mask);
1733
1734 IF (l_ext_acct IS NOT NULL) THEN
1735
1736 /*fnd_file.put_line(fnd_file.log,'In ARCHIVE_PAYMENT_INFO 2');*/
1737
1738 --l_bank_reg_num := rec_pers_pay.bank_reg_num;
1739 l_bank_reg_num := rec_pers_pay.bank_reg_num || ' ' ||
1740 hr_general.decode_lookup('HR_DK_BANK_REGISTRATION',rec_pers_pay.bank_reg_num);
1741
1742 pay_action_information_api.create_action_information (
1743 p_action_information_id => l_action_info_id
1744 ,p_action_context_id => p_archive_assact_id
1745 ,p_action_context_type => 'AAP'
1746 ,p_object_version_number => l_ovn
1747 ,p_effective_date => p_effective_date
1748 ,p_source_id => NULL
1749 ,p_source_text => NULL
1750 ,p_action_information_category => 'EMPLOYEE NET PAY DISTRIBUTION'
1751 ,p_action_information1 => rec_pers_pay.opm_id -- NULL
1752 ,p_action_information2 => rec_pers_pay.ppm_id
1753 ,p_action_information5 => l_bank_reg_num
1754 ,p_action_information6 => rec_pers_pay.branch
1755 ,p_action_information7 => rec_pers_pay.acct_num -- NULL
1756 ,p_action_information8 => NULL -- rec_pers_pay.acct_num
1757 ,p_action_information9 => NULL
1758 ,p_action_information10 => NULL
1759 ,p_action_information11 => NULL
1760 ,p_action_information12 => NULL
1761 ,p_action_information13 => NULL
1762 ,p_action_information14 => NULL
1763 ,p_action_information15 => NULL
1764 ,p_action_information16 => fnd_number.number_to_canonical(rec_pers_pay.value) /* 9316928 */
1765 ,p_action_information17 => NULL
1766 ,p_action_information18 => rec_pers_pay.opm_name -- rec_pers_pay.pay_type
1767 ,p_assignment_id => p_assignment_id);
1768 ELSE
1769
1770 /*fnd_file.put_line(fnd_file.log,'In ARCHIVE_PAYMENT_INFO 3');*/
1771
1772 pay_action_information_api.create_action_information (
1773 p_action_information_id => l_action_info_id
1774 ,p_action_context_id => p_archive_assact_id
1775 ,p_action_context_type => 'AAP'
1776 ,p_object_version_number => l_ovn
1777 ,p_effective_date => p_effective_date
1778 ,p_source_id => NULL
1779 ,p_source_text => NULL
1780 ,p_action_information_category => 'EMPLOYEE NET PAY DISTRIBUTION'
1781 ,p_action_information1 => rec_pers_pay.opm_id
1782 ,p_action_information2 => rec_pers_pay.ppm_id
1783 ,p_action_information5 => NULL
1784 ,p_action_information6 => NULL
1785 ,p_action_information7 => NULL
1786 ,p_action_information8 => NULL
1787 ,p_action_information9 => NULL
1788 ,p_action_information10 => NULL
1789 ,p_action_information11 => NULL
1790 ,p_action_information12 => NULL
1791 ,p_action_information13 => NULL
1792 ,p_action_information14 => NULL
1793 ,p_action_information15 => NULL
1794 ,p_action_information16 => fnd_number.number_to_canonical(rec_pers_pay.value) /* 9316928 */
1795 ,p_action_information17 => NULL
1796 ,p_action_information18 => rec_pers_pay.opm_name -- rec_pers_pay.pay_type
1797 ,p_assignment_id => p_assignment_id);
1798 END IF;
1799 END LOOP;
1800 /*fnd_file.put_line(fnd_file.log,'In ARCHIVE_PAYMENT_INFO 4');*/
1801
1802 END IF;
1803
1804 IF (rec_chk.opm_id IS NOT NULL AND rec_chk.ppm_id IS NULL) THEN
1805
1806 /*fnd_file.put_line(fnd_file.log,'In ARCHIVE_PAYMENT_INFO 5');*/
1807
1808 FOR rec_org_pay IN csr_get_org_pay(p_prepay_assact_id,rec_chk.opm_id) LOOP
1809
1810 l_pay_value := to_char (rec_org_pay.value,g_format_mask);
1811
1812 pay_action_information_api.create_action_information (
1813 p_action_information_id => l_action_info_id
1814 ,p_action_context_id => p_archive_assact_id
1815 ,p_action_context_type => 'AAP'
1816 ,p_object_version_number => l_ovn
1817 ,p_effective_date => p_effective_date
1818 ,p_source_id => NULL
1819 ,p_source_text => NULL
1820 ,p_action_information_category => 'EMPLOYEE NET PAY DISTRIBUTION'
1821 ,p_action_information1 => rec_org_pay.opm_id
1822 ,p_action_information2 => NULL
1823 ,p_action_information5 => NULL
1824 ,p_action_information6 => NULL
1825 ,p_action_information7 => NULL
1826 ,p_action_information8 => NULL
1827 ,p_action_information9 => NULL
1828 ,p_action_information10 => NULL
1829 ,p_action_information11 => NULL
1830 ,p_action_information12 => NULL
1831 ,p_action_information13 => NULL
1832 ,p_action_information14 => NULL
1833 ,p_action_information15 => NULL
1834 ,p_action_information16 => fnd_number.number_to_canonical(rec_org_pay.value) --l_pay_value
1835 ,p_action_information17 => NULL
1836 ,p_action_information18 => rec_org_pay.opm_name -- rec_org_pay.pay_type
1837 ,p_assignment_id => p_assignment_id);
1838 END LOOP;
1839
1840 END IF;
1841
1842 END LOOP;
1843 CLOSE csr_chk;
1844
1845 IF g_debug THEN
1846 hr_utility.set_location(' Leaving Procedure ARCHIVE_PAYMENT_INFO',270);
1847 END IF;
1848
1849 EXCEPTION WHEN OTHERS THEN
1850 g_err_num := SQLCODE;
1851
1852 /*fnd_file.put_line(fnd_file.log,'ORA_ERR: ' || g_err_num || 'In ARCHIVE_PAYMENT_INFO');*/
1853
1854 IF g_debug THEN
1855 hr_utility.set_location('ORA_ERR: ' || g_err_num || 'In ARCHIVE_PAYMENT_INFO',280);
1856 END IF;
1857
1858 END ARCHIVE_PAYMENT_INFO;
1859 ----------------------------------------- PROCEDURE ARCHIVE_ACCRUAL_PLAN ---------------------------------------------------------
1860
1861 /* ACCRUALS REGION */
1862
1863 /* PROCEDURE ARCHIVE_ACCRUAL_PLAN ( p_assignment_id IN NUMBER
1864 ,p_date_earned IN DATE
1865 ,p_effective_date IN DATE
1866 ,p_archive_assact_id IN NUMBER
1867 ,p_run_assignment_action_id IN NUMBER
1868 ,p_period_end_date IN DATE
1869 ,p_period_start_date IN DATE
1870 )
1871 IS
1872 --
1873 -- Cursor to get the Leave Balance Details .
1874 CURSOR csr_leave_balance
1875 IS
1876 --
1877 SELECT pap.accrual_plan_name
1878 ,hr_general_utilities.get_lookup_meaning('US_PTO_ACCRUAL',pap.accrual_category)
1879 ,pap.accrual_units_of_measure
1880 ,ppa.payroll_id
1881 ,pap.business_group_id
1882 ,pap.accrual_plan_id
1883 FROM pay_accrual_plans pap
1884 ,pay_element_types_f pet
1885 ,pay_element_links_f pel
1886 ,pay_element_entries_f pee
1887 ,pay_assignment_actions paa
1888 ,pay_payroll_actions ppa
1889 WHERE pet.element_type_id = pap.accrual_plan_element_type_id
1890 AND pel.element_type_id = pet.element_type_id
1891 AND pee.element_link_id = pel.element_link_id
1892 AND paa.assignment_id = pee.assignment_id
1893 AND ppa.payroll_action_id = paa.payroll_action_id
1894 AND ppa.action_type IN ('R','Q')
1895 AND ppa.action_status = 'C'
1896 AND ppa.date_earned BETWEEN pet.effective_start_date
1897 AND pet.effective_end_date
1898 AND ppa.date_earned BETWEEN pel.effective_start_date
1899 AND pel.effective_end_date
1900 AND ppa.date_earned BETWEEN pee.effective_start_date
1901 AND pee.effective_end_date
1902 AND paa.assignment_id = p_assignment_id
1903 AND paa.assignment_action_id = p_run_assignment_action_id;
1904 --
1905 l_action_info_id NUMBER;
1906 l_accrual_plan_id pay_accrual_plans.accrual_plan_id%type;
1907 l_accrual_plan_name pay_accrual_plans.accrual_plan_name%type;
1908 l_accrual_category pay_accrual_plans.accrual_category%type;
1909 l_accrual_uom pay_accrual_plans.accrual_units_of_measure%type;
1910 l_payroll_id pay_all_payrolls_f.payroll_id%type;
1911 l_business_group_id NUMBER;
1912 l_effective_date DATE;
1913 l_annual_leave_balance NUMBER;
1914 l_ovn NUMBER;
1915 l_leave_taken NUMBER;
1916 l_start_date DATE;
1917 l_end_date DATE;
1918 l_accrual_end_date DATE;
1919 l_accrual NUMBER;
1920 l_total_leave_taken NUMBER;
1921 l_procedure VARCHAR2(100) := g_package || '.archive_accrual_details';
1922 --
1923 BEGIN
1924 --
1925 IF g_debug THEN
1926 hr_utility.set_location(' Entering Procedure ARCHIVE_ACCRUAL_PLAN',290);
1927 END IF;
1928 OPEN csr_leave_balance;
1929 FETCH csr_leave_balance INTO
1930 l_accrual_plan_name
1931 ,l_accrual_category
1932 ,l_accrual_uom
1933 ,l_payroll_id
1934 ,l_business_group_id
1935 ,l_accrual_plan_id;
1936 IF csr_leave_balance%FOUND THEN
1937 --
1938 -- Call to get annual leave balance
1939 per_accrual_calc_functions.get_net_accrual
1940 (
1941 p_assignment_id => p_assignment_id -- number in
1942 ,p_plan_id => l_accrual_plan_id -- number in
1943 ,p_payroll_id => l_payroll_id -- number in
1944 ,p_business_group_id => l_business_group_id -- number in
1945 ,p_calculation_date => p_date_earned -- date in
1946 ,p_start_date => l_start_date -- date out
1947 ,p_end_date => l_end_date -- date out
1948 ,p_accrual_end_date => l_accrual_end_date -- date out
1949 ,p_accrual => l_accrual -- number out
1950 ,p_net_entitlement => l_annual_leave_balance -- number out
1951 );
1952 IF l_annual_leave_balance IS NULL THEN
1953 --
1954 l_annual_leave_balance := 0;
1955 --
1956 END IF;
1957 l_leave_taken := per_accrual_calc_functions.get_absence
1958 (
1959 p_assignment_id
1960 ,l_accrual_plan_id
1961 ,p_period_end_date
1962 ,p_period_start_date
1963 );
1964 l_ovn :=1;
1965 IF l_accrual_plan_name IS NOT NULL THEN
1966 --
1967 pay_action_information_api.create_action_information (
1968 p_action_information_id => l_action_info_id
1969 ,p_action_context_id => p_archive_assact_id
1970 ,p_action_context_type => 'AAP'
1971 ,p_object_version_number => l_ovn
1972 ,p_effective_date => p_effective_date
1973 ,p_source_id => NULL
1974 ,p_source_text => NULL
1975 ,p_action_information_category => 'EMPLOYEE ACCRUALS'
1976 ,p_action_information4 => l_accrual_plan_name
1977 ,p_action_information5 => fnd_number.number_to_canonical(l_leave_taken)
1978 ,p_action_information6 => fnd_number.number_to_canonical(l_annual_leave_balance)
1979 ,p_assignment_id => p_assignment_id);
1980 --
1981 END IF;
1982 --
1983 --
1984 END IF;
1985 --
1986 CLOSE csr_leave_balance;
1987 IF g_debug THEN
1988 hr_utility.set_location(' Leaving Procedure ARCHIVE_ACCRUAL_PLAN',300);
1989 END IF;
1990 --
1991 EXCEPTION
1992 WHEN OTHERS THEN
1993 IF csr_leave_balance%ISOPEN THEN
1994 --
1995 CLOSE csr_leave_balance;
1996 --
1997 END IF;
1998 --
1999 g_err_num := SQLCODE;
2000 --fnd_file.put_line(fnd_file.log,'ORA_ERR: ' || g_err_num || 'In ARCHIVE_ACCRUAL_PLAN');
2001 IF g_debug THEN
2002 hr_utility.set_location('ORA_ERR: ' || g_err_num || ' In ARCHIVE_ACCRUAL_PLAN',310);
2003 END IF;
2004 RAISE;
2005 END ARCHIVE_ACCRUAL_PLAN;*/
2006
2007 ----------------------------------- PROCEDURE ARCHIVE_ADD_ELEMENT ---------------------------------------------------------------
2008
2009 /* ADDITIONAL ELEMENTS REGION */
2010
2011 PROCEDURE ARCHIVE_ADD_ELEMENT(p_archive_assact_id IN NUMBER,
2012 p_assignment_action_id IN NUMBER,
2013 p_assignment_id IN NUMBER,
2014 p_payroll_action_id IN NUMBER,
2015 p_date_earned IN DATE,
2016 p_effective_date IN DATE,
2017 p_pre_payact_id IN NUMBER,
2018 p_archive_flag IN VARCHAR2) IS
2019
2020 ------------------------------
2021 /* Cursor to retrieve Additional Element Information */
2022 CURSOR csr_get_element(p_bus_grp_id NUMBER) IS
2023 SELECT hoi.org_information2 element_type_id
2024 ,hoi.org_information3 input_value_id
2025 ,hoi.org_information7 element_narrative
2026 ,pec.classification_name
2027 ,piv.uom
2028 FROM hr_organization_information hoi
2029 ,pay_element_classifications pec
2030 ,pay_element_types_f pet
2031 ,pay_input_values_f piv
2032 WHERE hoi.organization_id = p_bus_grp_id
2033 AND hoi.org_information_context = 'Business Group:Payslip Info'
2034 AND hoi.org_information1 = 'ELEMENT'
2035 AND hoi.org_information2 = pet.element_type_id
2036 AND pec.classification_id = pet.classification_id
2037 AND piv.input_value_id = hoi.org_information3
2038 AND p_date_earned BETWEEN piv.effective_start_date AND piv.effective_end_date;
2039
2040 -------------------------------
2041 /* Cursor to retrieve run result value of Additional Elements */
2042 CURSOR csr_result_value(p_iv_id NUMBER
2043 ,p_ele_type_id NUMBER
2044 ,p_assignment_action_id NUMBER) IS
2045 SELECT rrv.result_value,rr.element_entry_id
2046 FROM pay_run_result_values rrv
2047 ,pay_run_results rr
2048 ,pay_assignment_actions paa
2049 ,pay_payroll_actions ppa
2050 WHERE rrv.input_value_id = p_iv_id
2051 AND rr.element_type_id = p_ele_type_id
2052 AND rr.run_result_id = rrv.run_result_id
2053 AND rr.assignment_action_id = paa.assignment_action_id
2054 AND paa.assignment_action_id = p_assignment_action_id
2055 AND ppa.payroll_action_id = paa.payroll_action_id
2056 AND ppa.action_type IN ('Q','R')
2057 AND rrv.result_value IS NOT NULL;
2058 ------------------------------
2059 -- Adding the Conditions below for Payslip Format Changes - w.r.t bug - 7229247
2060 -- Start
2061 /* Cursor to retrieve run result value of Main Elements */
2062 CURSOR csr_result_value_EE(p_iv_id NUMBER
2063 ,p_ele_type_id NUMBER
2064 ,p_assignment_action_id NUMBER
2065 ,p_EE_ID NUMBER) IS
2066 SELECT rrv.result_value
2067 FROM pay_run_result_values rrv
2068 ,pay_run_results rr
2069 ,pay_assignment_actions paa
2070 ,pay_payroll_actions ppa
2071 WHERE rrv.input_value_id = p_iv_id
2072 AND rr.element_type_id = p_ele_type_id
2073 AND rr.run_result_id = rrv.run_result_id
2074 AND rr.assignment_action_id = paa.assignment_action_id
2075 AND paa.assignment_action_id = p_assignment_action_id
2076 AND ppa.payroll_action_id = paa.payroll_action_id
2077 AND ppa.action_type IN ('Q','R')
2078 AND rrv.result_value IS NOT NULL
2079 AND rr.element_entry_id = p_EE_ID;
2080
2081 -----------------------------------------------------------------------------
2082 -- Cursor to pick up segment from DK_SOE_ELEMENT_ADD_DETAILS context Org Developer DF
2083 CURSOR csr_group_by( p_ele_type_id NUMBER ) IS
2084 select ORG_INFORMATION3,ORG_INFORMATION6,ORG_INFORMATION7,ORG_INFORMATION8,ORG_INFORMATION9,ORG_INFORMATION10
2085 from hr_organization_information code
2086 where code.organization_id = g_business_group_id
2087 and code.org_information_context ='DK_SOE_ELEMENT_ADD_DETAILS'
2088 and code.org_information1 = fnd_number.number_to_canonical(p_ele_type_id);
2089
2090 rec_group_by csr_group_by%ROWTYPE;
2091 -----------------------------------------------------------------------------
2092 /* Cursor to retrieve sum of run result value for an given Main Element */
2093 -----------------------------------------------------------------------------
2094 CURSOR csr_sum_of_result_values(p_iv_id NUMBER
2095 ,p_ele_type_id NUMBER
2096 ,p_assignment_action_id NUMBER
2097 ) IS
2098 SELECT sum(fnd_number.canonical_to_number(rrv.result_value)) result_value
2099 ,count(rrv.RUN_RESULT_ID) record_count
2100 ,rrv.result_value UNIT_PRICE
2101 FROM pay_run_result_values rrv
2102 ,pay_run_results rr
2103 ,pay_assignment_actions paa
2104 ,pay_payroll_actions ppa
2105 WHERE rrv.input_value_id = p_iv_id
2106 AND rr.element_type_id = p_ele_type_id
2107 AND rr.run_result_id = rrv.run_result_id
2108 AND rr.assignment_action_id = paa.assignment_action_id
2109 AND paa.assignment_action_id = p_assignment_action_id
2110 AND ppa.payroll_action_id = paa.payroll_action_id
2111 AND ppa.action_type IN ('Q','R')
2112 AND rrv.result_value IS NOT NULL
2113 group by rrv.result_value;
2114
2115 rec_sum_of_result_values csr_sum_of_result_values%ROWTYPE;
2116 -----------------------------------------------------------------------------
2117
2118 -----------------------------------------------------------------------------
2119 /* Cursor to retrieve sum of all run result value for an given Main Element */
2120 -----------------------------------------------------------------------------
2121 CURSOR csr_all_sum_result_values(p_iv_id_UNIT NUMBER,p_iv_id_AMOUNT NUMBER,p_iv_id_UNIT_PRICE NUMBER
2122 ,p_ele_type_id NUMBER
2123 ,p_assignment_action_id NUMBER
2124 ) IS
2125 SELECT rrv3.result_value UNIT_PRICE ,
2126 sum(fnd_number.canonical_to_number(rrv1.result_value)) UNIT,
2127 sum(fnd_number.canonical_to_number(rrv2.result_value)) AMOUNT
2128 FROM pay_run_result_values rrv1
2129 ,pay_run_results rr1
2130 ,pay_assignment_actions paa
2131 ,pay_payroll_actions ppa
2132 ,pay_run_result_values rrv2
2133 ,pay_run_results rr2
2134 ,pay_run_result_values rrv3
2135 ,pay_run_results rr3
2136 WHERE rrv1.input_value_id = p_iv_id_UNIT
2137 AND rr1.element_type_id = p_ele_type_id
2138 AND rr1.run_result_id = rrv1.run_result_id
2139 AND rr1.assignment_action_id = paa.assignment_action_id
2140 AND paa.assignment_action_id = p_assignment_action_id
2141 AND ppa.payroll_action_id = paa.payroll_action_id
2142 AND ppa.action_type IN ('Q','R')
2143 and rrv2.input_value_id = p_iv_id_AMOUNT
2144 AND rr2.run_result_id = rrv2.run_result_id
2145 AND NVL(rr2.element_entry_id,-1) = NVL(rr1.element_entry_id,-1) -- Bug#9289267 fix
2146 AND rr2.assignment_action_id = paa.assignment_action_id -- Code added for Recurring Elements Cumulation
2147 and rrv3.input_value_id = p_iv_id_UNIT_PRICE
2148 AND rr3.run_result_id = rrv3.run_result_id
2149 AND NVL(rr3.element_entry_id,-1) = NVL(rr1.element_entry_id,-1) -- Bug#9289267 fix
2150 AND rr3.assignment_action_id = paa.assignment_action_id -- Code added for Recurring Elements Cumulation
2151 group by rrv3.result_value;
2152
2153
2154 -----------------------------------------------------------------------------
2155 -----------------------------------------------------------------------------
2156 /* Cursor to retrieve sum of all run result value for an given Main Element */
2157 -----------------------------------------------------------------------------
2158 CURSOR csr_all_result_values(p_iv_id_UNIT NUMBER,p_iv_id_AMOUNT NUMBER,p_iv_id_UNIT_PRICE NUMBER
2159 ,p_ele_type_id NUMBER
2160 ,p_assignment_action_id NUMBER
2161 ) IS
2162 SELECT rrv3.result_value UNIT_PRICE , rrv1.result_value UNIT, rrv2.result_value AMOUNT
2163 FROM pay_run_result_values rrv1
2164 ,pay_run_results rr1
2165 ,pay_assignment_actions paa
2166 ,pay_payroll_actions ppa
2167 ,pay_run_result_values rrv2
2168 ,pay_run_results rr2
2169 ,pay_run_result_values rrv3
2170 ,pay_run_results rr3
2171 WHERE rrv1.input_value_id = p_iv_id_UNIT
2172 AND rr1.element_type_id = p_ele_type_id
2173 AND rr1.run_result_id = rrv1.run_result_id
2174 AND rr1.assignment_action_id = paa.assignment_action_id
2175 AND paa.assignment_action_id = p_assignment_action_id
2176 AND ppa.payroll_action_id = paa.payroll_action_id
2177 AND ppa.action_type IN ('Q','R')
2178 and rrv2.input_value_id = p_iv_id_AMOUNT
2179 AND rr2.run_result_id = rrv2.run_result_id
2180 AND NVL(rr2.element_entry_id,-1) = NVL(rr1.element_entry_id,-1) -- Bug#9289267 fix
2181 AND rr2.assignment_action_id = paa.assignment_action_id -- Code added for Recurring Elements Cumulation
2182 and rrv3.input_value_id = p_iv_id_UNIT_PRICE
2183 AND rr3.run_result_id = rrv3.run_result_id
2184 AND NVL(rr3.element_entry_id,-1) = NVL(rr1.element_entry_id,-1) -- Bug#9289267 fix
2185 AND rr3.assignment_action_id = paa.assignment_action_id; -- Code added for Recurring Elements Cumulation
2186
2187
2188
2189 -----------------------------------------------------------------------------
2190 -- End Changes for Payslip w.r.t bug - 7229247
2191
2192 rec_get_element csr_get_element%ROWTYPE;
2193 /*Bug fix 6193177*/
2194 -- l_result_value pay_run_result_values.result_value%TYPE := 0;
2195 l_result_value pay_run_result_values.result_value%TYPE ;
2196 l_action_info_id NUMBER;
2197 l_ovn NUMBER;
2198 l_element_context VARCHAR2(10);
2199 l_index NUMBER := 0;
2200 l_formatted_value VARCHAR2(50) := NULL;
2201 l_flag NUMBER := 0;
2202 ------------------------------
2203 -- Changes for Payslip format -w.r.t bug - 7229247
2204 -- Start
2205 l_group_by NUMBER(10);
2206 l_unit_price NUMBER ;
2207 l_amount NUMBER;
2208 l_UNIT NUMBER;
2209 -- End
2210
2211 BEGIN
2212
2213 IF g_debug THEN
2214 hr_utility.set_location(' Entering Procedure ARCHIVE_ADD_ELEMENT',320);
2215 END IF;
2216 /*
2217 IF p_archive_flag = 'N' THEN
2218 ---------------------------------------------------
2219 --Check if global table has already been populated
2220 ---------------------------------------------------
2221 IF g_element_table.count = 0 THEN
2222
2223 OPEN csr_get_element(g_business_group_id);
2224 LOOP
2225 FETCH csr_get_element INTO rec_get_element;
2226 EXIT WHEN csr_get_element%NOTFOUND;
2227
2228 l_element_context := 'F'; --Additional Element Context
2229 SETUP_ELEMENT_DEFINITIONS(p_classification_name => rec_get_element.classification_name
2230 ,p_element_name => rec_get_element.element_narrative
2231 ,p_element_type_id => rec_get_element.element_type_id
2232 ,p_input_value_id => rec_get_element.input_value_id
2233 ,p_element_type => l_element_context
2234 ,p_uom => rec_get_element.uom
2235 ,p_archive_flag => p_archive_flag);
2236
2237 END LOOP;
2238 CLOSE csr_get_element;
2239 END IF;
2240
2241 ELSIF p_archive_flag = 'Y' AND g_element_table.count > 0 THEN
2242
2243 FOR l_index IN g_element_table.first.. g_element_table.last LOOP
2244 l_result_value := NULL;
2245
2246 BEGIN
2247 /*
2248 OPEN csr_result_value(g_element_table(l_index).input_value_id
2249 ,g_element_table(l_index).element_type_id
2250 ,p_assignment_action_id);
2251 FETCH csr_result_value INTO l_result_value;
2252 CLOSE csr_result_value;
2253 */
2254
2255 -- Fix to handle Multiple Element Entries
2256
2257 /* get the element run result value */
2258 /* OPEN csr_result_value(g_element_table(l_index).input_value_id
2259 ,g_element_table(l_index).element_type_id
2260 ,p_assignment_action_id);
2261 LOOP
2262 FETCH csr_result_value INTO l_result_value;
2263 EXIT WHEN csr_result_value%NOTFOUND;
2264
2265
2266 IF l_result_value is not null THEN
2267 pay_action_information_api.create_action_information (
2268 p_action_information_id => l_action_info_id
2269 ,p_action_context_id => p_archive_assact_id
2270 ,p_action_context_type => 'AAP'
2271 ,p_object_version_number => l_ovn
2272 ,p_effective_date => p_effective_date
2273 ,p_source_id => NULL
2274 ,p_source_text => NULL
2275 ,p_action_information_category => 'EMEA ELEMENT INFO'
2276 ,p_action_information1 => g_element_table(l_index).element_type_id
2277 ,p_action_information2 => g_element_table(l_index).input_value_id
2278 ,p_action_information3 => g_element_table(l_index).element_type
2279 ,p_action_information4 => l_result_value --l_formatted_value
2280 ,p_action_information9 => 'Additional Element'
2281 ,p_assignment_id => p_assignment_id);
2282 END IF;
2283
2284 END LOOP;
2285 CLOSE csr_result_value;
2286
2287 -- End Fix to handle Multiple Element Entries
2288
2289 EXCEPTION WHEN OTHERS THEN
2290 g_err_num := SQLCODE;
2291 /*fnd_file.put_line(fnd_file.log,'ORA_ERR: ' || g_err_num || 'In ARCHIVE_ADD_ELEMENT');*/
2292
2293 /* IF g_debug THEN
2294 hr_utility.set_location('ORA_ERR: ' || g_err_num || 'In ARCHIVE_ADD_ELEMENT',330);
2295 END IF;
2296 END;
2297 END LOOP;
2298
2299 END IF;
2300 */
2301 -----------------------------------------------------------------------------------------
2302 -----------------------------------------------------------------------------------------
2303 -- Adding the Conditions below for Payslip Format Changes - w.r.t bug - 7229247
2304 /**************************************Start********************************************/
2305 IF p_archive_flag = 'N' THEN
2306 ---------------------------------------------------
2307 --Check if global table has already been populated
2308 ---------------------------------------------------
2309 IF g_element_table.count = 0 THEN
2310 OPEN csr_get_element(g_business_group_id);
2311 LOOP
2312 FETCH csr_get_element INTO rec_get_element;
2313 EXIT WHEN csr_get_element%NOTFOUND;
2314 l_element_context := 'F'; --Additional Element Context
2315 SETUP_ELEMENT_DEFINITIONS(p_classification_name => rec_get_element.classification_name
2316 ,p_element_name => rec_get_element.element_narrative
2317 ,p_element_type_id => rec_get_element.element_type_id
2318 ,p_input_value_id => rec_get_element.input_value_id
2319 ,p_element_type => l_element_context
2320 ,p_uom => rec_get_element.uom
2321 ,p_archive_flag => p_archive_flag);
2322 END LOOP;
2323 CLOSE csr_get_element;
2324 END IF;
2325 ELSIF p_archive_flag = 'Y' AND g_element_table.count > 0 THEN
2326 FOR l_index IN g_element_table.first.. g_element_table.last LOOP
2327 l_result_value := NULL;
2328 l_group_by :=null;
2329 l_unit_price :=null;
2330 BEGIN
2331
2332 -- Conditions below are added to flush the Record Set After a Successful Query
2333 -- so that it starts afresh for the next element
2334 rec_group_by.ORG_INFORMATION6:= NULL;
2335 rec_group_by.ORG_INFORMATION3:= NULL;
2336
2337 OPEN csr_group_by(g_element_table(l_index).element_type_id );
2338 FETCH csr_group_by
2339 INTO rec_group_by;
2340 CLOSE csr_group_by;
2341
2342
2343 -- The se_soe contains
2344 -- segment 3 = > I or O
2345 -- segment 6 = > Y or N
2346 -- segment 7 = > Input ID UNIT
2347 -- segment 8 = > Input ID UNIT PRICE
2348 -- segment 9 = > Input ID Amount
2349 IF ( (rec_group_by.ORG_INFORMATION3 = 'O') or (rec_group_by.ORG_INFORMATION3 IS NULL) )
2350 THEN
2351 -- Case for Group by or NOT
2352 -- Segemnt 6 is allowed here, as it makes sense.
2353 IF ( rec_group_by.ORG_INFORMATION6 = 'N' or (rec_group_by.ORG_INFORMATION3 IS NULL) )
2354 THEN
2355 -- This csae is for individual representation of each element.
2356 -- Unit and unit price should be absent.
2357 FOR csr_result_rec IN csr_result_value(g_element_table(l_index).input_value_id
2358 ,g_element_table(l_index).element_type_id
2359 ,p_assignment_action_id)
2360 LOOP
2361 IF csr_result_rec.result_value is not null THEN
2362 pay_action_information_api.create_action_information (
2363 p_action_information_id => l_action_info_id
2364 ,p_action_context_id => p_archive_assact_id
2365 ,p_action_context_type => 'AAP'
2366 ,p_object_version_number => l_ovn
2367 ,p_effective_date => p_effective_date
2368 ,p_source_id => NULL
2369 ,p_source_text => NULL
2370 ,p_action_information_category => 'EMEA ELEMENT INFO'
2371 ,p_action_information1 => g_element_table(l_index).element_type_id
2372 ,p_action_information2 => g_element_table(l_index).input_value_id
2373 ,p_action_information3 => g_element_table(l_index).element_type
2374 ,p_action_information4 => csr_result_rec.result_value /* l_formatted_value 9316928 */
2375 ,p_action_information8 => ''
2376 ,p_action_information9 => 'Additional Element:'
2377 ,p_assignment_id => p_assignment_id);
2378
2379 END IF;
2380 END LOOP;
2381
2382 ELSIF rec_group_by.ORG_INFORMATION6 = 'Y'
2383 THEN
2384 -- This case is for Grouping by pay value of each element.
2385 -- unit and unit price should be present
2386 FOR csr_result_rec IN csr_sum_of_result_values(g_element_table(l_index).input_value_id
2387 ,g_element_table(l_index).element_type_id
2388 ,p_assignment_action_id )
2389 LOOP
2390 IF csr_result_rec.result_value is not null THEN
2391 pay_action_information_api.create_action_information (
2392 p_action_information_id => l_action_info_id
2393 ,p_action_context_id => p_archive_assact_id
2394 ,p_action_context_type => 'AAP'
2395 ,p_object_version_number => l_ovn
2396 ,p_effective_date => p_effective_date
2397 ,p_source_id => NULL
2398 ,p_source_text => NULL
2399 ,p_action_information_category => 'EMEA ELEMENT INFO'
2400 ,p_action_information1 => g_element_table(l_index).element_type_id
2401 ,p_action_information2 => g_element_table(l_index).input_value_id
2402 ,p_action_information3 => g_element_table(l_index).element_type
2403 ,p_action_information4 => fnd_number.number_to_canonical(csr_result_rec.result_value) /* l_formatted_value 9316928 select query has canonical to num */
2404 ,p_action_information8 => csr_result_rec.record_count
2405 ,p_action_information9 => 'Additional Element unit per price:'||csr_result_rec.UNIT_PRICE
2406 ,p_assignment_id => p_assignment_id);
2407
2408
2409 END IF;
2410 END LOOP;
2411 END IF;
2412 ELSIF rec_group_by.ORG_INFORMATION3 = 'I'
2413 THEN
2414 -- Case for UNIT,PRICE,AMOUNT
2415 -- Segment 7,8,9 is allowed
2416 -- Segemnt 6 is not allowed here, as it doesn't makes sense.
2417 -- segment 7 = > Input ID UNIT
2418 -- segment 8 = > Input ID UNIT PRICE
2419 -- segment 9 = > Input ID Amount
2420 IF rec_group_by.ORG_INFORMATION7 IS NOT NULL AND
2421 rec_group_by.ORG_INFORMATION8 IS NOT NULL AND
2422 rec_group_by.ORG_INFORMATION9 IS NOT NULL
2423 THEN
2424 -- All three are selected, we can group by three in single query
2425
2426 IF rec_group_by.ORG_INFORMATION10 = 'Y'
2427 THEN
2428 FOR csr_result_rec IN csr_all_sum_result_values(rec_group_by.ORG_INFORMATION7,rec_group_by.ORG_INFORMATION9
2429 ,rec_group_by.ORG_INFORMATION8
2430 ,g_element_table(l_index).element_type_id
2431 ,p_assignment_action_id )
2432 LOOP
2433 IF csr_result_rec.AMOUNT is not null THEN
2434 pay_action_information_api.create_action_information (
2435 p_action_information_id => l_action_info_id
2436 ,p_action_context_id => p_archive_assact_id
2437 ,p_action_context_type => 'AAP'
2438 ,p_object_version_number => l_ovn
2439 ,p_effective_date => p_effective_date
2440 ,p_source_id => NULL
2441 ,p_source_text => NULL
2442 ,p_action_information_category => 'EMEA ELEMENT INFO'
2443 ,p_action_information1 => g_element_table(l_index).element_type_id
2444 ,p_action_information2 => g_element_table(l_index).input_value_id
2445 ,p_action_information3 => g_element_table(l_index).element_type
2446 ,p_action_information4 => fnd_number.number_to_canonical(csr_result_rec.AMOUNT) /* l_formatted_value 9316928 select query applied caninical to num */
2447 ,p_action_information8 => fnd_number.number_to_canonical(csr_result_rec.UNIT) /* l_formatted_value 9316928 select query applied caninical to num */
2448 ,p_action_information9 => 'Additional Element unit per price:'||csr_result_rec.UNIT_PRICE
2449 ,p_assignment_id => p_assignment_id);
2450
2451 END IF;
2452 END LOOP;
2453 ELSE
2454 FOR csr_result_rec IN csr_all_result_values(rec_group_by.ORG_INFORMATION7,rec_group_by.ORG_INFORMATION9 ,rec_group_by.ORG_INFORMATION8
2455 ,g_element_table(l_index).element_type_id
2456 ,p_assignment_action_id )
2457 LOOP
2458 IF csr_result_rec.AMOUNT is not null THEN
2459 pay_action_information_api.create_action_information (
2460 p_action_information_id => l_action_info_id
2461 ,p_action_context_id => p_archive_assact_id
2462 ,p_action_context_type => 'AAP'
2463 ,p_object_version_number => l_ovn
2464 ,p_effective_date => p_effective_date
2465 ,p_source_id => NULL
2466 ,p_source_text => NULL
2467 ,p_action_information_category => 'EMEA ELEMENT INFO'
2468 ,p_action_information1 => g_element_table(l_index).element_type_id
2469 ,p_action_information2 => g_element_table(l_index).input_value_id
2470 ,p_action_information3 => g_element_table(l_index).element_type
2471 ,p_action_information4 => csr_result_rec.AMOUNT /* l_formatted_value 9316928 */
2472 ,p_action_information8 => csr_result_rec.UNIT
2473 ,p_action_information9 => 'Additional Element unit per price:'||csr_result_rec.UNIT_PRICE
2474 ,p_assignment_id => p_assignment_id);
2475
2476 END IF;
2477 END LOOP;
2478
2479 END IF;-- end of rec_group_by.ORG_INFORMATION10 = 'Y'
2480 ELSE -- Three inputs are not selected.
2481 -- have to get the each input value id and find value for each
2482 -- and archive it if the amount is not null
2483
2484 -- Case for UNIT,PRICE,AMOUNT
2485 -- Segment 7,8,9 is allowed
2486 -- Segemnt 6 is not allowed here, as it doesn't makes sense.
2487 -- segment 7 = > Input ID UNIT
2488 -- segment 8 = > Input ID UNIT PRICE
2489 -- segment 9 = > Input ID Amount
2490 IF rec_group_by.ORG_INFORMATION9 IS NOT NULL
2491 THEN
2492 -- amount should not be null
2493 -- find the amount value and element entry id of this element
2494 -- for this element entry find the the other selected columns like UNIT , UNIT PRICE if selected
2495 --
2496 FOR csr_result_rec IN csr_result_value(rec_group_by.ORG_INFORMATION9 ,g_element_table(l_index).element_type_id ,p_assignment_action_id)
2497 LOOP
2498
2499 -- we have EE id
2500 l_amount := fnd_number.canonical_to_number(csr_result_rec.result_value);
2501 IF rec_group_by.ORG_INFORMATION8 IS NOT NULL
2502 THEN
2503 OPEN csr_result_value_EE(rec_group_by.ORG_INFORMATION8 ,g_element_table(l_index).element_type_id ,p_assignment_action_id,csr_result_rec.element_entry_id );
2504 FETCH csr_result_value_EE
2505 INTO l_unit_price;
2506 CLOSE csr_result_value_EE;
2507 ELSE
2508 l_unit_price :=NULL;
2509 END IF; -- End if of segment 8 , unit price
2510
2511 IF rec_group_by.ORG_INFORMATION7 IS NOT NULL
2512 THEN
2513 OPEN csr_result_value_EE(rec_group_by.ORG_INFORMATION7 ,g_element_table(l_index).element_type_id ,p_assignment_action_id,csr_result_rec.element_entry_id ); -- Bug#9289267 fix
2514 FETCH csr_result_value_EE
2515 INTO l_unit;
2516 CLOSE csr_result_value_EE;
2517 ELSE
2518 l_unit :=NULL;
2519 END IF; -- End if of segment 7 , unit
2520
2521 -- Resume again
2522 IF csr_result_rec.result_value is not null THEN
2523 pay_action_information_api.create_action_information (
2524 p_action_information_id => l_action_info_id
2525 ,p_action_context_id => p_archive_assact_id
2526 ,p_action_context_type => 'AAP'
2527 ,p_object_version_number => l_ovn
2528 ,p_effective_date => p_effective_date
2529 ,p_source_id => NULL
2530 ,p_source_text => NULL
2531 ,p_action_information_category => 'EMEA ELEMENT INFO'
2532 ,p_action_information1 => g_element_table(l_index).element_type_id
2533 ,p_action_information2 => g_element_table(l_index).input_value_id
2534 ,p_action_information3 => g_element_table(l_index).element_type
2535 ,p_action_information4 => fnd_number.number_to_canonical(l_amount) /* l_formatted_value l_amount is applied canonical to num earlier */
2536 ,p_action_information8 => l_unit
2537 ,p_action_information9 => 'Additional Element unit per price:'||l_unit_price
2538 ,p_assignment_id => p_assignment_id);
2539
2540 END IF; -- end of csr_result_rec.result_value is not null
2541 END LOOP;
2542 END IF; -- end of rec_group_by.ORG_INFORMATION9 IS NOT NULL
2543
2544 END IF;
2545
2546
2547 END IF;
2548
2549 EXCEPTION WHEN OTHERS THEN
2550 g_err_num := SQLCODE;
2551 /*fnd_file.put_line(fnd_file.log,'ORA_ERR: ' || g_err_num || 'In ARCHIVE_ADD_ELEMENT');*/
2552 IF g_debug THEN
2553 hr_utility.set_location('ORA_ERR: ' || g_err_num || 'In ARCHIVE_ADD_ELEMENT',330);
2554 END IF;
2555 END;
2556 END LOOP;
2557 END IF;
2558
2559 IF g_debug THEN
2560 hr_utility.set_location(' Leaving Procedure ARCHIVE_ADD_ELEMENT',340);
2561 END IF;
2562
2563 END ARCHIVE_ADD_ELEMENT;
2564
2565 ---------------------------------- PROCEDURE ARCHIVE_OTH_BALANCE ----------------------------------------------------------------
2566
2567 /* OTHER BALANCES REGION */
2568
2569 PROCEDURE ARCHIVE_OTH_BALANCE (p_archive_assact_id IN NUMBER,
2570 p_assignment_action_id IN NUMBER,
2571 p_assignment_id IN NUMBER,
2572 p_payroll_action_id IN NUMBER,
2573 p_record_count IN NUMBER,
2574 p_pre_payact_id IN NUMBER,
2575 p_effective_date IN DATE,
2576 p_date_earned IN DATE,
2577 p_archive_flag IN VARCHAR2) IS
2578
2579 ------------------
2580 /* Cursor to retrieve Other Balances Information */
2581 CURSOR csr_get_balance(p_bus_grp_id NUMBER) IS
2582 SELECT org_information4 balance_type_id
2583 ,org_information5 balance_dim_id
2584 ,org_information7 narrative
2585 FROM hr_organization_information
2586 WHERE organization_id = p_bus_grp_id
2587 AND org_information_context = 'Business Group:Payslip Info'
2588 AND org_information1 = 'BALANCE';
2589
2590 -----------------
2591 /* Cursor to retrieve Tax Unit Id for setting context */
2592 CURSOR csr_tax_unit (p_run_assact_id NUMBER) IS
2593 SELECT paa.tax_unit_id
2594 FROM pay_assignment_actions paa
2595 WHERE paa.assignment_action_id = p_run_assact_id;
2596 -----------------
2597 /* Cursor to fetch defined balance id */
2598 CURSOR csr_def_balance(bal_type_id NUMBER, bal_dim_id NUMBER) IS
2599 SELECT defined_balance_id
2600 FROM pay_defined_balances
2601 WHERE balance_type_id = bal_type_id
2602 AND balance_dimension_id = bal_dim_id;
2603 ----------------
2604 rec_get_balance csr_get_balance%ROWTYPE;
2605 l_balance_value NUMBER := 0;
2606 l_action_info_id NUMBER;
2607 l_ovn NUMBER;
2608 l_index NUMBER;
2609 l_tu_id NUMBER;
2610 l_defined_balance_id NUMBER :=0;
2611 l_formatted_value VARCHAR2(50) := NULL;
2612 l_flag NUMBER := 0;
2613
2614 ---------------
2615
2616 BEGIN
2617
2618 IF g_debug THEN
2619 hr_utility.set_location(' Entering Procedure ARCHIVE_OTH_BALANCE',350);
2620 END IF;
2621
2622 -- fnd_file.put_line(fnd_file.log,'In ARCHIVE_OTH_BALANCE 0');
2623 /*fnd_file.put_line(fnd_file.log,'Entering In ARCHIVE_OTH_BALANCE global');*/
2624
2625 IF p_archive_flag = 'N' THEN
2626 ---------------------------------------------------
2627 --Check if global table has already been populated
2628 ---------------------------------------------------
2629 IF g_user_balance_table.count = 0 THEN
2630 OPEN csr_get_balance(g_business_group_id);
2631 LOOP
2632 FETCH csr_get_balance INTO rec_get_balance;
2633 EXIT WHEN csr_get_balance%NOTFOUND;
2634
2635 OPEN csr_def_balance(rec_get_balance.balance_type_id,rec_get_balance.balance_dim_id);
2636 FETCH csr_def_balance INTO l_defined_balance_id;
2637 CLOSE csr_def_balance;
2638
2639 PAY_DK_ARCHIVE.SETUP_BALANCE_DEFINITIONS
2640 (p_balance_name => rec_get_balance.narrative
2641 ,p_defined_balance_id => l_defined_balance_id
2642 ,p_balance_type_id => rec_get_balance.balance_type_id);
2643 END LOOP;
2644 CLOSE csr_get_balance;
2645 END IF;
2646
2647 ---------------------------------------------------
2648 -- For Tax Card details ,
2649 -- Check if global table has already been populated
2650 -- if not then populate the values
2651 ---------------------------------------------------
2652 IF g_tax_card_tab.count = 0 THEN
2653
2654 g_tax_card_tab(1).inp_val_name := 'Method of Receipt';
2655 g_tax_card_tab(2).inp_val_name := 'Tax Card Type';
2656 g_tax_card_tab(3).inp_val_name := 'Tax Percentage';
2657 g_tax_card_tab(4).inp_val_name := 'Tax Free Threshold';
2658 g_tax_card_tab(5).inp_val_name := 'Monthly Tax Deduction';
2659 g_tax_card_tab(6).inp_val_name := 'Bi Weekly Tax Deduction';
2660 g_tax_card_tab(7).inp_val_name := 'Weekly Tax Deduction';
2661 g_tax_card_tab(8).inp_val_name := 'Daily Tax Deduction';
2662 g_tax_card_tab(9).inp_val_name := 'Registration Date';
2663 g_tax_card_tab(10).inp_val_name := 'Date Returned';
2664
2665 END IF;
2666
2667 ---------------------------------------------------
2668 -- For Mandatory Balance Details ,
2669 -- Check if global table has already been populated
2670 -- if not hen populate the values
2671 ---------------------------------------------------
2672
2673 IF g_bal_val.count = 0 THEN
2674
2675 g_bal_val(1).bal_name := 'AMBABLE_PAY_ASG_YTD'; -- AMB able income ytd
2676 g_bal_val(2).bal_name := 'EMPLOYEE_TAX_ASG_YTD'; -- Tax ytd
2677 g_bal_val(3).bal_name := 'HOLIDAYABLE_PAY_ASG_YTD'; -- Holiday able income ytd
2678 g_bal_val(4).bal_name := 'EMPLOYEE_ATP_DEDUCTIONS_ASG_YTD'; -- ATP contribution ytd
2679 g_bal_val(5).bal_name := 'EMPLOYEE_SP_DEDUCTIONS_ASG_YTD'; -- Special Pension ytd
2680
2681 g_bal_val(6).bal_name := 'EMPLOYER_PENSION_DEDUCTIONS_ASG_YTD'; -- Employer Pension ytd
2682 g_bal_val(7).bal_name := 'EMPLOYEE_PENSION_DEDUCTIONS_ASG_YTD'; -- Employee Pension ytd
2683 g_bal_val(8).bal_name := 'EMPLOYEE_AMB_DEDUCTION_ASG_YTD'; -- For AMB Contribution ytd
2684
2685 g_bal_val(9).bal_name := 'HOLIDAY_ACCRUAL_AMOUNT_ASG_YTD'; -- FOR Calculated holiday pay ytd (Salaried)
2686
2687 -- HOLIDAY_ACCRUAL_AMOUNT_ASG_YTD
2688 -- HOLIDAY_ACCRUAL_PAY_ASG_YTD
2689
2690 -- g_bal_val(10).bal_name := 'INITIAL_HOLIDAY_ENTITLEMENT_DAYS_ASG_HOLIDAY_YTD'; -- Holidays remaining with pay
2691 g_bal_val(10).bal_name := 'INITIAL_HOL_ENTITLEMENT_DAYS_ASG_HOLIDAY_YTD'; -- Holidays remaining with pay
2692 g_bal_val(11).bal_name := 'HOLIDAY_PAID_DAYS_ASG_HOLIDAY_YTD'; -- Holidays remaining with pay
2693
2694 g_bal_val(12).bal_name := 'HOLIDAY_UNPAID_DAYS_ASG_HOLIDAY_YTD'; -- Holidays remaining without pay
2695
2696 g_bal_val(13).bal_name := 'TOTAL_G_DAGE_PAY_ASG_YTD'; -- G-day's (money)
2697 g_bal_val(14).bal_name := 'TOTAL_G_DAGE_DAYS_ASG_YTD'; -- G-day's (number of day's)
2698
2699 -- Bug Fix 4704284 : start
2700
2701 -- g_bal_val(15).bal_name := 'TAXABLE_PAY_ASG_YTD'; -- FOR Rest Amount of F Card
2702 g_bal_val(15).bal_name := 'F_TAX_BASE_ASG_YTD'; -- FOR Rest Amount of F Card
2703
2704 -- g_bal_val(16).bal_name := 'TAXABLE_PAY_ASG_PTD'; -- Taxable Income
2705 g_bal_val(16).bal_name := 'EMPLOYEE_TAXABLE_BASE_ASG_PTD'; -- Taxable Income
2706
2707 -- Bug Fix 4704284 : end
2708
2709 g_bal_val(17).bal_name := 'EMPLOYEE_TAX_ASG_PTD'; -- Tax in period
2710
2711 g_bal_val(18).bal_name := 'HOLIDAY_ACCRUAL_PAY_ASG_YTD'; -- FOR Calculated holiday pay ytd (Hourly Paid)
2712
2713 -- Additional Balances for Holiday Pay Changes
2714
2715 g_bal_val(19).bal_name := 'HOLIDAY_BANK_PAY_ASG_YTD'; -- FOR AMBable income ytd
2716
2717 g_bal_val(20).bal_name := 'HOLIDAY_TAX_ASG_YTD'; -- FOR Tax ytd
2718
2719 g_bal_val(21).bal_name := 'HOLIDAY_SP_ASG_YTD'; -- FOR Special Pension ytd
2720
2721 -- Additional Balances for Holiday Pay Changes
2722 -- Taxable Income = EMPLOYEE_TAXABLE_BASE_ASG_PTD + ( l_income_from_hol_Pay - HOLIDAY_AMB_ASG_PTD - HOLIDAY_SP_ASG_PTD )
2723
2724 -- if employee is salaried ,
2725 -- then l_income_from_hol_Pay = Salaried Hol Accrual Amount_ASG_PTD
2726 -- + Salaried Hol Curr Entit Amount_ASG_PTD
2727 -- + Salaried Hol Next Entit Amount_ASG_PTD )
2728 -- else (employee is hourly paid)
2729 -- then l_income_from_hol_Pay = HOLIDAY_BANK_PAY_ASG_PTD
2730
2731 /* 10006902 start */
2732 --g_bal_val(22).bal_name := 'HOLIDAY_BANK_PAY_ASG_PTD'; -- FOR Taxable Income
2733 g_bal_val(22).bal_name := 'HOLIDAY_ACCRUAL_PAY_ASG_PTD';
2734 --g_bal_val(23).bal_name := 'HOLIDAY_AMB_ASG_PTD'; -- FOR Taxable Income
2735 g_bal_val(23).bal_name := 'HOLIDAY_AMB_REPORTING_ASG_PTD';
2736 /* 10006902 end */
2737 g_bal_val(24).bal_name := 'HOLIDAY_SP_ASG_PTD'; -- FOR Taxable Income
2738
2739 -- Additional Balances for Holiday Pay Changes
2740 -- Tax in period = EMPLOYEE_TAX_ASG_PTD + HOLIDAY_TAX_ASG_PTD
2741
2742 g_bal_val(25).bal_name := 'HOLIDAY_TAX_ASG_PTD'; -- FOR Tax in period
2743
2744 g_bal_val(26).bal_name := 'SALARIED_HOL_ACCRUAL_AMOUNT_ASG_PTD'; -- FOR Taxable Income
2745 g_bal_val(27).bal_name := 'SALARIED_HOL_CURR_ENTIT_AMOUNT_ASG_PTD'; -- FOR Taxable Income
2746 g_bal_val(28).bal_name := 'SALARIED_HOL_NEXT_ENTIT_AMOUNT_ASG_PTD'; -- FOR Taxable Income
2747
2748 -- Bug Fix 5080969
2749
2750 -- AMB Contribution ytd = EMPLOYEE_AMB_DEDUCTION_ASG_YTD + HOLIDAY_AMB_ASG_YTD
2751
2752 g_bal_val(29).bal_name := 'HOLIDAY_AMB_ASG_YTD'; -- For AMB Contribution ytd
2753 g_bal_val(30).bal_name := 'HOLIDAYABLE_PAY_REDUCTION_ASG_YTD'; -- Holidayable income reduction for spent hols
2754
2755 /* 10006902 start */
2756 g_bal_val(31).bal_name := 'TOTAL_AMBABLE_PAY_ASG_YTD';
2757 g_bal_val(32).bal_name := 'TOTAL_AMB_ASG_YTD';
2758 g_bal_val(33).bal_name := 'TOTAL_TAX_ASG_YTD';
2759 g_bal_val(34).bal_name := 'TOTAL_HOLIDAYABLE_PAY_ASG_YTD';
2760
2761 g_bal_val(35).bal_name := 'SALARIED_HOL_ACCRUAL_TAX_ASG_PTD';
2762 g_bal_val(36).bal_name := 'SALARIED_HOL_CURR_ENTIT_TAX_ASG_PTD';
2763 g_bal_val(37).bal_name := 'SALARIED_HOL_NEXT_ENTIT_TAX_ASG_PTD';
2764
2765 g_bal_val(38).bal_name := 'TOTAL_TAX_ASG_PTD';
2766
2767 /* 10006902 end */
2768
2769
2770 END IF;
2771
2772 ELSIF p_archive_flag = 'Y' THEN
2773
2774 OPEN csr_tax_unit(p_assignment_action_id);
2775 FETCH csr_tax_unit INTO l_tu_id;
2776 CLOSE csr_tax_unit;
2777
2778 PAY_BALANCE_PKG.SET_CONTEXT('TAX_UNIT_ID',l_tu_id);
2779 PAY_BALANCE_PKG.SET_CONTEXT('DATE_EARNED',fnd_date.date_to_canonical(p_date_earned));
2780
2781 IF g_user_balance_table.count > 0 THEN
2782 -- fnd_file.put_line(fnd_file.log,'In ARCHIVE_OTH_BALANCE 1');
2783
2784 FOR l_index IN g_user_balance_table.first.. g_user_balance_table.last LOOP
2785
2786 l_balance_value := pay_balance_pkg.get_value(g_user_balance_table(l_index).defined_balance_id,p_assignment_action_id);
2787
2788 IF l_balance_value > 0 THEN
2789 -- fnd_file.put_line(fnd_file.log,'In ARCHIVE_OTH_BALANCE 2 :' || l_balance_value);
2790 pay_action_information_api.create_action_information (
2791 p_action_information_id => l_action_info_id
2792 ,p_action_context_id => p_archive_assact_id
2793 ,p_action_context_type => 'AAP'
2794 ,p_object_version_number => l_ovn
2795 ,p_effective_date => p_effective_date
2796 ,p_source_id => NULL
2797 ,p_source_text => NULL
2798 ,p_action_information_category => 'EMEA BALANCES'
2799 ,p_action_information1 => g_user_balance_table(l_index).defined_balance_id
2800 ,p_action_information2 => 'OBAL' --Other Balances Context
2801 ,p_action_information4 => fnd_number.number_to_canonical(l_balance_value) --l_formatted_value
2802 ,p_action_information5 => NULL
2803 ,p_action_information6 => 'Other Balances'
2804 ,p_assignment_id => p_assignment_id);
2805 END IF;
2806 END LOOP;
2807 END IF; /* For table count check */
2808 END IF;
2809
2810 EXCEPTION WHEN OTHERS THEN
2811 g_err_num := SQLCODE;
2812 -- fnd_file.put_line(fnd_file.log,'ORA_ERR: ' || g_err_num || 'In ARCHIVE_OTH_BALANCE'||SQLERRM);
2813
2814 IF g_debug THEN
2815 hr_utility.set_location('ORA_ERR: ' || g_err_num || 'In ARCHIVE_OTH_BALANCE',360);
2816 END IF;
2817
2818 END ARCHIVE_OTH_BALANCE;
2819
2820 ----------------------------------------- PROCEDURE ARCHIVE_CODE ---------------------------------------------------------
2821
2822 /* ARCHIVE CODE */
2823
2824 PROCEDURE ARCHIVE_CODE(p_assignment_action_id IN NUMBER
2825 ,p_effective_date IN DATE)
2826 IS
2827
2828 ----------------------
2829 /* Cursor to retrieve Payroll and Prepayment related Ids for Archival */
2830 CURSOR csr_archive_ids (p_locking_action_id NUMBER) IS
2831 SELECT prepay_assact.assignment_action_id prepay_assact_id
2832 ,prepay_assact.assignment_id prepay_assgt_id
2833 ,prepay_payact.payroll_action_id prepay_payact_id
2834 ,prepay_payact.effective_date prepay_effective_date
2835 ,run_assact.assignment_id run_assgt_id
2836 ,run_assact.assignment_action_id run_assact_id
2837 ,run_payact.payroll_action_id run_payact_id
2838 ,run_payact.payroll_id payroll_id
2839 FROM pay_action_interlocks archive_intlck
2840 ,pay_assignment_actions prepay_assact
2841 ,pay_payroll_actions prepay_payact
2842 ,pay_action_interlocks prepay_intlck
2843 ,pay_assignment_actions run_assact
2844 ,pay_payroll_actions run_payact
2845 WHERE archive_intlck.locking_action_id = p_locking_action_id
2846 AND prepay_assact.assignment_action_id = archive_intlck.locked_action_id
2847 AND prepay_payact.payroll_action_id = prepay_assact.payroll_action_id
2848 AND prepay_payact.action_type IN ('U','P')
2849 AND prepay_intlck.locking_action_id = prepay_assact.assignment_action_id
2850 AND run_assact.assignment_action_id = prepay_intlck.locked_action_id
2851 AND run_payact.payroll_action_id = run_assact.payroll_action_id
2852 AND run_payact.action_type IN ('Q', 'R')
2853 ORDER BY prepay_intlck.locking_action_id,prepay_intlck.locked_action_id desc;
2854 ---------------------
2855 /* Cursor to retrieve time period information */
2856 CURSOR csr_period_end_date(p_assact_id NUMBER,p_pay_act_id NUMBER) IS
2857 SELECT ptp.end_date end_date,
2858 ptp.regular_payment_date regular_payment_date,
2859 ptp.time_period_id time_period_id,
2860 ppa.date_earned date_earned,
2861 ppa.effective_date effective_date,
2862 ptp.start_date start_date
2863 FROM per_time_periods ptp
2864 ,pay_payroll_actions ppa
2865 ,pay_assignment_actions paa
2866 WHERE ptp.payroll_id =ppa.payroll_id
2867 AND ppa.payroll_action_id =paa.payroll_action_id
2868 AND paa.assignment_action_id =p_assact_id
2869 AND ppa.payroll_action_id =p_pay_act_id
2870 AND ppa.date_earned BETWEEN ptp.start_date AND ptp.end_date;
2871 -----------------
2872 /* Cursor to retrieve Archive Payroll Action Id */
2873 CURSOR csr_archive_payact(p_assignment_action_id NUMBER) IS
2874 SELECT payroll_action_id
2875 FROM pay_assignment_actions
2876 WHERE assignment_Action_id = p_assignment_action_id;
2877 -----------------
2878 l_archive_payact_id NUMBER;
2879 l_record_count NUMBER;
2880 l_actid NUMBER;
2881 l_end_date per_time_periods.end_date%TYPE;
2882 l_pre_end_date per_time_periods.end_date%TYPE;
2883 l_reg_payment_date per_time_periods.regular_payment_date%TYPE;
2884 l_pre_reg_payment_date per_time_periods.regular_payment_date%TYPE;
2885 l_date_earned pay_payroll_actions.date_earned%TYPE;
2886 l_pre_date_earned pay_payroll_actions.date_earned%TYPE;
2887 l_effective_date pay_payroll_actions.effective_date%TYPE;
2888 l_pre_effective_date pay_payroll_actions.effective_date%TYPE;
2889 l_run_payact_id NUMBER;
2890 l_action_context_id NUMBER;
2891 g_archive_pact NUMBER;
2892 p_assactid NUMBER;
2893 l_time_period_id per_time_periods.time_period_id%TYPE;
2894 l_pre_time_period_id per_time_periods.time_period_id%TYPE;
2895 l_start_date per_time_periods.start_date%TYPE;
2896 l_pre_start_date per_time_periods.start_date%TYPE;
2897 l_fnd_session NUMBER := 0;
2898 l_prev_prepay NUMBER := 0;
2899 ------------------
2900
2901 BEGIN
2902
2903 -- fnd_file.put_line(fnd_file.log,'Entering Procedure ARCHIVE_CODE');
2904
2905 IF g_debug THEN
2906 hr_utility.set_location(' Entering Procedure ARCHIVE_CODE',380);
2907 END IF;
2908
2909 OPEN csr_archive_payact(p_assignment_action_id);
2910 FETCH csr_archive_payact INTO l_archive_payact_id;
2911 CLOSE csr_archive_payact;
2912
2913 -- fnd_file.put_line(fnd_file.log,'closed csr_archive_payact');
2914
2915 l_record_count := 0;
2916
2917 FOR rec_archive_ids IN csr_archive_ids(p_assignment_action_id) LOOP
2918
2919 OPEN csr_period_end_date(rec_archive_ids.run_assact_id,rec_archive_ids.run_payact_id);
2920 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;
2921 CLOSE csr_period_end_date;
2922
2923 -- fnd_file.put_line(fnd_file.log,'closed csr_period_end_date');
2924
2925 OPEN csr_period_end_date(rec_archive_ids.prepay_assact_id,rec_archive_ids.prepay_payact_id);
2926 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;
2927 CLOSE csr_period_end_date;
2928
2929 -- fnd_file.put_line(fnd_file.log,'closed csr_period_end_date');
2930
2931 /*fnd_file.put_line(fnd_file.log,'ARCHIVE_EMPLOYEE_DETAILS');*/
2932
2933 -------------------------------------------------------------
2934 --Archive EMPLOYEE_DETAILS, PAYMENT_INFO and BALANCES
2935 --for every prepayment assignment action id
2936 -------------------------------------------------------------
2937
2938 IF (rec_archive_ids.prepay_assact_id <> l_prev_prepay) THEN
2939
2940 -- fnd_file.put_line(fnd_file.log,'in ARCHIVE_EMPLOYEE_DETAILS');
2941
2942 /*
2943 ARCHIVE_EMPLOYEE_DETAILS(p_archive_assact_id => p_assignment_action_id
2944 ,p_assignment_id => rec_archive_ids.run_assgt_id
2945 ,p_assignment_action_id => rec_archive_ids.run_assact_id
2946 ,p_payroll_action_id => l_archive_payact_id
2947 ,p_time_period_id => l_time_period_id
2948 ,p_date_earned => l_pre_date_earned
2949 ,p_pay_date_earned => l_date_earned
2950 ,p_effective_date => p_effective_date);
2951 */
2952
2953 -- using l_date_earned (Payroll Run Date Earned) instead of l_pre_date_earned (Prepayments Date Earned)
2954 -- because the date_earned column at table pay_payroll_actions for Prepayments may not always be populated.
2955
2956 ARCHIVE_EMPLOYEE_DETAILS(p_archive_assact_id => p_assignment_action_id
2957 ,p_assignment_id => rec_archive_ids.run_assgt_id
2958 ,p_assignment_action_id => rec_archive_ids.run_assact_id
2959 ,p_payroll_action_id => l_archive_payact_id
2960 ,p_time_period_id => l_time_period_id
2961 ,p_date_earned => l_date_earned
2962 ,p_pay_date_earned => l_date_earned
2963 ,p_effective_date => p_effective_date);
2964
2965 -- fnd_file.put_line(fnd_file.log,'out ARCHIVE_EMPLOYEE_DETAILS');
2966 -- fnd_file.put_line(fnd_file.log,'in ARCHIVE_ADDL_EMP_DETAILS');
2967
2968 ARCHIVE_ADDL_EMP_DETAILS(p_archive_assact_id => p_assignment_action_id
2969 ,p_assignment_id => rec_archive_ids.run_assgt_id
2970 ,p_assignment_action_id => rec_archive_ids.run_assact_id
2971 ,p_effective_date => p_effective_date
2972 ,p_date_earned => l_date_earned
2973 ,p_payroll_action_id => l_archive_payact_id );
2974
2975
2976 -- fnd_file.put_line(fnd_file.log,'out ARCHIVE_ADDL_EMP_DETAILS');
2977 -- fnd_file.put_line(fnd_file.log,'in ARCHIVE_PAYMENT_INFO');
2978
2979 /*fnd_file.put_line(fnd_file.log,'ARCHIVE_PAYMENT_INFO');*/
2980
2981 /*
2982 ARCHIVE_PAYMENT_INFO(p_archive_assact_id => p_assignment_action_id,
2983 p_prepay_assact_id => rec_archive_ids.prepay_assact_id,
2984 p_assignment_id => rec_archive_ids.prepay_assgt_id,
2985 p_date_earned => l_pre_date_earned,
2986 p_effective_date => p_effective_date);
2987 */
2988
2989 -- using l_date_earned (Payroll Run Date Earned) instead of l_pre_date_earned (Prepayments Date Earned)
2990 -- because the date_earned column at table pay_payroll_actions for Prepayments may not always be populated.
2991
2992 ARCHIVE_PAYMENT_INFO(p_archive_assact_id => p_assignment_action_id,
2993 p_prepay_assact_id => rec_archive_ids.prepay_assact_id,
2994 p_assignment_id => rec_archive_ids.prepay_assgt_id,
2995 p_date_earned => l_date_earned,
2996 p_effective_date => p_effective_date);
2997
2998 -- fnd_file.put_line(fnd_file.log,'out ARCHIVE_PAYMENT_INFO');
2999 -- fnd_file.put_line(fnd_file.log,'in ARCHIVE_OTH_BALANCE');
3000
3001 /*fnd_file.put_line(fnd_file.log,'ARCHIVE_OTH_BALANCE');*/
3002
3003 ARCHIVE_OTH_BALANCE(p_archive_assact_id => p_assignment_action_id,
3004 p_assignment_action_id => rec_archive_ids.run_assact_id,
3005 p_assignment_id => rec_archive_ids.run_assgt_id,
3006 p_payroll_action_id => l_archive_payact_id,
3007 p_record_count => l_record_count,
3008 p_pre_payact_id => rec_archive_ids.prepay_payact_id,
3009 p_effective_date => p_effective_date,
3010 p_date_earned => l_date_earned,
3011 p_archive_flag => 'Y');
3012
3013 -- fnd_file.put_line(fnd_file.log,'out ARCHIVE_OTH_BALANCE');
3014 -- fnd_file.put_line(fnd_file.log,'before end if');
3015
3016
3017
3018 l_prev_prepay := rec_archive_ids.prepay_assact_id;
3019
3020 END IF;
3021
3022 /*fnd_file.put_line(fnd_file.log,'ARCHIVE_ACCRUAL_PLAN');*/
3023
3024 /* ARCHIVE_ACCRUAL_PLAN (p_assignment_id => rec_archive_ids.run_assgt_id,
3025 p_date_earned => l_date_earned,
3026 p_effective_date => p_effective_date,
3027 p_archive_assact_id => p_assignment_action_id,
3028 p_run_assignment_action_id => rec_archive_ids.run_assact_id,
3029 p_period_end_date => l_end_date,
3030 p_period_start_date => l_start_date);*/
3031
3032 /*fnd_file.put_line(fnd_file.log,'ARCHIVE_ADD_ELEMENT');*/
3033
3034 -- fnd_file.put_line(fnd_file.log,'in ARCHIVE_ADD_ELEMENT');
3035
3036
3037 ARCHIVE_ADD_ELEMENT(p_archive_assact_id => p_assignment_action_id,
3038 p_assignment_action_id => rec_archive_ids.run_assact_id,
3039 p_assignment_id => rec_archive_ids.run_assgt_id,
3040 p_payroll_action_id => l_archive_payact_id,
3041 p_date_earned => l_date_earned,
3042 p_effective_date => p_effective_date,
3043 p_pre_payact_id => rec_archive_ids.prepay_payact_id,
3044 p_archive_flag => 'Y');
3045
3046 -- fnd_file.put_line(fnd_file.log,'out ARCHIVE_ADD_ELEMENT');
3047 -- fnd_file.put_line(fnd_file.log,'in ARCHIVE_MAIN_ELEMENTS');
3048
3049 /*fnd_file.put_line(fnd_file.log,'Assact id: '|| p_assignment_action_id);*/
3050
3051 ARCHIVE_MAIN_ELEMENTS (p_archive_assact_id => p_assignment_action_id,
3052 p_assignment_action_id => rec_archive_ids.run_assact_id,
3053 p_assignment_id => rec_archive_ids.run_assgt_id,
3054 p_date_earned => l_date_earned,
3055 p_effective_date => p_effective_date ) ;
3056
3057 -- fnd_file.put_line(fnd_file.log,'out ARCHIVE_MAIN_ELEMENTS');
3058
3059
3060
3061 l_record_count := l_record_count + 1;
3062
3063 END LOOP;
3064
3065 IF g_debug THEN
3066 hr_utility.set_location(' Leaving Procedure ARCHIVE_CODE',390);
3067 END IF;
3068
3069 -- fnd_file.put_line(fnd_file.log,'Leaving Procedure ARCHIVE_CODE');
3070
3071 END ARCHIVE_CODE;
3072
3073
3074 ---------------------------------------- PROCEDURE ARCHIVE_ADDL_EMP_DETAILS --------------------------------------------------------------------------
3075
3076 /*Additional Employee Details*/
3077
3078 PROCEDURE ARCHIVE_ADDL_EMP_DETAILS(p_archive_assact_id IN NUMBER
3079 ,p_assignment_id IN NUMBER
3080 ,p_assignment_action_id IN NUMBER
3081 ,p_effective_date IN DATE
3082 ,p_date_earned IN DATE
3083 ,p_payroll_action_id IN NUMBER )
3084 IS
3085 -------------
3086 CURSOR CSR_ACTUAL_TERM_DATE (p_assignment_id NUMBER) IS
3087 SELECT actual_termination_date
3088 FROM per_periods_of_service pps,
3089 per_all_assignments_f paa
3090 WHERE pps.period_of_service_id = paa.period_of_service_id
3091 AND p_date_earned between paa.effective_start_date and paa.effective_end_date
3092 AND paa.assignment_id = p_assignment_id;
3093 -------------
3094
3095 CURSOR get_details(p_assignment_id NUMBER , p_input_value VARCHAR2 ) IS
3096 SELECT ee.effective_start_date effective_start_date
3097 ,eev1.screen_entry_value screen_entry_value
3098 FROM per_all_assignments_f asg1
3099 ,per_all_assignments_f asg2
3100 ,per_all_people_f per
3101 ,pay_element_links_f el
3102 ,pay_element_types_f et
3103 ,pay_input_values_f iv1
3104 ,pay_element_entries_f ee
3105 ,pay_element_entry_values_f eev1
3106 WHERE asg1.assignment_id = p_assignment_id
3107 AND p_date_earned BETWEEN asg1.effective_start_date AND asg1.effective_end_date
3108 AND p_date_earned BETWEEN asg2.effective_start_date AND asg2.effective_end_date
3109 AND per.person_id = asg1.person_id
3110 AND asg2.person_id = per.person_id
3111 AND asg2.primary_flag = 'Y'
3112 AND et.element_name = 'Tax Card'
3113 AND et.legislation_code = 'DK'
3114 AND iv1.element_type_id = et.element_type_id
3115 AND iv1.name = p_input_value
3116 AND el.business_group_id = per.business_group_id
3117 AND el.element_type_id = et.element_type_id
3118 AND ee.assignment_id = asg2.assignment_id
3119 AND ee.element_link_id = el.element_link_id
3120 AND eev1.element_entry_id = ee.element_entry_id
3121 AND eev1.input_value_id = iv1.input_value_id
3122 AND p_date_earned BETWEEN ee.effective_start_date AND ee.effective_end_date
3123 AND p_date_earned BETWEEN eev1.effective_start_date AND eev1.effective_end_date;
3124 ------------
3125 CURSOR csr_tax_details(p_assignment_id NUMBER, p_input_value VARCHAR2) IS
3126 SELECT ee.effective_start_date
3127 ,eev1.screen_entry_value screen_entry_value
3128 FROM per_all_assignments_f asg1
3129 ,pay_element_links_f el
3130 ,pay_element_types_f et
3131 ,pay_input_values_f iv1
3132 ,pay_element_entries_f ee
3133 ,pay_element_entry_values_f eev1
3134 WHERE asg1.assignment_id = p_assignment_id
3135 AND p_date_earned BETWEEN asg1.effective_start_date AND asg1.effective_end_date
3136 AND et.element_name = 'Tax'
3137 AND et.legislation_code = 'DK'
3138 AND iv1.element_type_id = et.element_type_id
3139 AND iv1.name = p_input_value
3140 AND el.element_type_id = et.element_type_id
3141 AND ee.assignment_id = asg1.assignment_id
3142 AND ee.element_link_id = el.element_link_id
3143 AND eev1.element_entry_id = ee.element_entry_id
3144 AND eev1.input_value_id = iv1.input_value_id
3145 AND p_date_earned BETWEEN ee.effective_start_date AND ee.effective_end_date
3146 AND p_date_earned BETWEEN eev1.effective_start_date AND eev1.effective_end_date;
3147 -------------
3148 CURSOR csr_tax_category (p_assignment_id NUMBER) IS
3149 SELECT segment13
3150 FROM per_all_assignments_f paa,
3151 hr_soft_coding_keyflex hsc
3152 WHERE paa.soft_coding_keyflex_id = hsc.soft_coding_keyflex_id
3153 AND p_date_earned BETWEEN paa.effective_start_date AND paa.effective_end_date
3154 AND paa.assignment_id = p_assignment_id;
3155 -------------
3156 CURSOR csr_global_value (p_global_name VARCHAR2) IS
3157 SELECT global_value
3158 FROM ff_globals_f
3159 WHERE global_name = p_global_name
3160 AND p_date_earned BETWEEN effective_start_date AND effective_end_date;
3161 -------------
3162 /* cursor to get the payroll_d */
3163 CURSOR csr_payroll (p_payroll_action_id NUMBER) IS
3164 SELECT PAY_DK_ARCHIVE.GET_PARAMETER(legislative_parameters,'PAYROLL_ID')
3165 FROM pay_payroll_actions
3166 WHERE payroll_action_id = p_payroll_action_id ;
3167
3168
3169 /* cursor to get the payroll details */
3170 CURSOR csr_payroll_details (l_payroll_id NUMBER) IS
3171 SELECT payroll_name , period_type
3172 FROM pay_all_payrolls_f
3173 WHERE payroll_id = l_payroll_id ;
3174 --------------
3175
3176 l_actual_termination_date PER_PERIODS_OF_SERVICE.ACTUAL_TERMINATION_DATE%TYPE;
3177 l_tax_card_effective_date DATE;
3178 l_tax_card_type VARCHAR2(50);
3179 l_base_rate NUMBER(5,2);
3180 l_additional_rate NUMBER(5,2);
3181 l_yearly_income_limit NUMBER(10);
3182 l_previous_income NUMBER (10);
3183 l_ovn NUMBER ;
3184 l_rec get_details%ROWTYPE;
3185 l_tax_rec csr_tax_details%ROWTYPE;
3186 l_action_info_id pay_action_information.action_information_id%TYPE;
3187 l_tax_category hr_soft_coding_keyflex.segment13%TYPE;
3188 l_defined_balance_id pay_defined_balances.defined_balance_id%TYPE;
3189
3190 -- Bug Fix 5081696
3191 l_global_tax_percent VARCHAR2(20);
3192 l_use_tax_card VARCHAR2(20);
3193
3194 l_ambable_pay_asg_ytd NUMBER(10,2);
3195 l_employee_tax_asg_ytd NUMBER(10,2);
3196 l_holidayable_pay_asg_ytd NUMBER(10,2);
3197 l_emp_atp_dedn_asg_ytd NUMBER(10,2);
3198 l_emp_sp_dedn_asg_ytd NUMBER(10,2);
3199 l_total_pension_asg_ytd NUMBER(10,2);
3200 l_emplr_pension_dedn_asg_ytd NUMBER(10,2);
3201 l_emp_pension_dedn_asg_ytd NUMBER(10,2);
3202 l_emp_amb_dedn_asg_ytd NUMBER(10,2);
3203 l_calc_holiday_pay_asg_ytd NUMBER(10,2);
3204 l_hol_rem_with_pay_asg_ytd NUMBER(10,2);
3205 l_hol_rem_without_pay NUMBER(10,2);
3206 l_total_g_dage_pay_asg_ytd NUMBER(10,2);
3207 l_total_g_dage_days_asg_ytd NUMBER(10,2);
3208 l_time_off_in_lieu_hours NUMBER(10,2);
3209 l_rest_amount_of_f_card NUMBER(10,2);
3210
3211 l_income_from_hol_Pay NUMBER ;
3212 l_income_from_hol_Pay_sal NUMBER ;
3213 l_income_from_hol_Pay_hr NUMBER ;
3214
3215 -- Bug Fix 4704284 : start
3216
3217 -- l_taxable_pay_asg_ptd NUMBER(10,2);
3218 l_emp_taxable_base_asg_ptd NUMBER(10,2);
3219
3220 -- Bug Fix 4704284 : end
3221
3222 l_employee_tax_asg_ptd NUMBER(10,2);
3223
3224 l_tax_deduction VARCHAR2(240);
3225 l_tax_percent VARCHAR2(240);
3226 l_net_pay VARCHAR2(240);
3227
3228 l_payroll_id NUMBER;
3229 l_payroll_name VARCHAR2(80);
3230 l_period_type VARCHAR2(80);
3231 l_hourly_salaried per_all_assignments_f.hourly_salaried_code%TYPE ;
3232 --------------
3233 /*
3234 TYPE tax_card_rec IS RECORD (inp_val_name pay_input_values_f.NAME%type , screen_entry_val pay_input_values_f.NAME%type );
3235
3236 TYPE bal_val_rec IS RECORD ( bal_name ff_database_items.USER_NAME%type , bal_val NUMBER(10,2) );
3237
3238
3239 TYPE tax_card_table IS TABLE OF tax_card_rec INDEX BY BINARY_INTEGER;
3240
3241 TYPE bal_val_table IS TABLE OF bal_val_rec INDEX BY BINARY_INTEGER;
3242
3243
3244 g_tax_card_tab tax_card_table;
3245 g_bal_val bal_val_table;
3246
3247 */
3248
3249 -------------
3250
3251 BEGIN
3252
3253 -- fnd_file.put_line(fnd_file.log,'inside ARCHIVE_ADDL_EMP_DETAILS');
3254
3255 OPEN CSR_ACTUAL_TERM_DATE (p_assignment_id);
3256 FETCH CSR_ACTUAL_TERM_DATE INTO l_actual_termination_date;
3257 CLOSE CSR_ACTUAL_TERM_DATE;
3258
3259 -- fnd_file.put_line(fnd_file.log,'closed CSR_ACTUAL_TERM_DATE');
3260
3261 -- fnd_file.put_line(fnd_file.log,'before FOR g_tax_card_tab');
3262
3263 FOR l_index IN g_tax_card_tab.first.. g_tax_card_tab.last LOOP
3264
3265 OPEN get_details( p_assignment_id ,g_tax_card_tab(l_index).inp_val_name );
3266 FETCH get_details INTO l_rec;
3267 CLOSE get_details;
3268
3269 g_tax_card_tab(l_index).screen_entry_val := l_rec.screen_entry_value ;
3270
3271 END LOOP;
3272
3273 -- fnd_file.put_line(fnd_file.log,'end loop FOR g_tax_card_tab');
3274
3275 l_tax_card_effective_date := l_rec.effective_start_date;
3276
3277
3278 -- Bug Fix 5081696 : Start
3279
3280 -- get the tax card type
3281 l_tax_card_type := g_tax_card_tab(2).screen_entry_val ;
3282
3283 -- fnd_file.put_line(fnd_file.log,' l_tax_card_type = '||l_tax_card_type );
3284
3285 -- get the global value for tax percentage
3286
3287 OPEN csr_global_value ('DK_NO_TAX_CARD_RATE') ;
3288 FETCH csr_global_value INTO l_global_tax_percent ;
3289 CLOSE csr_global_value ;
3290
3291 -- fnd_file.put_line(fnd_file.log,'l_global_tax_percent = '||l_global_tax_percent );
3292
3293 -- get the 'Use Tax Card' input vale from Tax element
3294
3295 OPEN csr_tax_details (p_assignment_id, 'Use Tax Card') ;
3296 FETCH csr_tax_details INTO l_tax_rec ;
3297 CLOSE csr_tax_details ;
3298
3299 l_use_tax_card := l_tax_rec.screen_entry_value ;
3300
3301 -- fnd_file.put_line(fnd_file.log,'l_use_tax_card = '||l_use_tax_card );
3302
3303 -- For an employee with Tax Card type as 'No Tax Card'
3304 -- or
3305 -- For an employee with "Use tax card" as 'No' in the tax element
3306 -- even if any tax percentage is mentioned or any other details are mentioned in the tax card,
3307 -- the tax calculation is always done based on the global tax percentage.
3308 -- Hence even in the payslip the tax percentage should be displayed as DK_NO_TAX_CARD_RATE
3309
3310 IF ( (l_tax_card_type = 'NTC') OR (l_use_tax_card = 'N') ) THEN
3311 l_tax_percent := l_global_tax_percent ;
3312 -- fnd_file.put_line(fnd_file.log,' Overriding l_tax_percent ');
3313 ELSE
3314 l_tax_percent := g_tax_card_tab(3).screen_entry_val ;
3315 -- fnd_file.put_line(fnd_file.log,' Tax Card l_tax_percent ');
3316 END IF;
3317
3318 -- fnd_file.put_line(fnd_file.log,' l_tax_percent = '|| l_tax_percent);
3319
3320 -- Bug Fix 5081696 : End
3321
3322 ---------------------
3323
3324 -- fnd_file.put_line(fnd_file.log,'getting DK_TAX_CARD_TYPE');
3325
3326 -- Getting the display value for Tax Card type
3327 g_tax_card_tab(2).screen_entry_val := hr_general.decode_lookup('DK_TAX_CARD_TYPE',g_tax_card_tab(2).screen_entry_val);
3328
3329 -- fnd_file.put_line(fnd_file.log,'archiving ADDL EMPLOYEE DETAILS');
3330
3331 pay_action_information_api.create_action_information (
3332 p_action_information_id => l_action_info_id
3333 ,p_action_context_id => p_archive_assact_id
3334 ,p_action_context_type => 'AAP'
3335 ,p_object_version_number => l_ovn
3336 ,p_effective_date => p_effective_date
3337 ,p_source_id => NULL
3338 ,p_source_text => NULL
3339 ,p_action_information_category => 'ADDL EMPLOYEE DETAILS'
3340 ,p_action_information4 => g_tax_card_tab(1).screen_entry_val -- Method of Receipt
3341 ,p_action_information5 => g_tax_card_tab(2).screen_entry_val -- Tax Card Type
3342 --,p_action_information6 => g_tax_card_tab(3).screen_entry_val -- Tax Percentage
3343 ,p_action_information6 => l_tax_percent -- Tax Percentage -- Bug Fix 5081696
3344 ,p_action_information7 => g_tax_card_tab(4).screen_entry_val -- Tax Free Threshold
3345 ,p_action_information8 => g_tax_card_tab(5).screen_entry_val -- Monthly Tax Deduction
3346 ,p_action_information9 => g_tax_card_tab(6).screen_entry_val -- Bi Weekly Tax Deduction
3347 ,p_action_information10 => g_tax_card_tab(7).screen_entry_val -- Weekly Tax Deduction
3348 ,p_action_information11 => g_tax_card_tab(8).screen_entry_val -- Daily Tax Deduction
3349 ,p_action_information12 => fnd_date.date_to_displaydate(fnd_date.canonical_to_date(g_tax_card_tab(9).screen_entry_val)) -- Registration Date
3350 --,p_action_information12 => g_tax_card_tab(9).screen_entry_val -- Registration Date
3351 ,p_action_information13 => fnd_date.date_to_displaydate(fnd_date.canonical_to_date(g_tax_card_tab(10).screen_entry_val)) -- Date Returned
3352 --,p_action_information13 => g_tax_card_tab(10).screen_entry_val -- Date Returned
3353 ,p_assignment_id => p_assignment_id );
3354
3355 -- fnd_file.put_line(fnd_file.log,'finished archiving ADDL EMPLOYEE DETAILS');
3356
3357 -------------------------------------------------------------------------------
3358
3359 -- fnd_file.put_line(fnd_file.log,'begin FOR g_bal_val');
3360
3361 -- fnd_file.put_line(fnd_file.log,'g_bal_val.first = '||to_char(g_bal_val.first));
3362 -- fnd_file.put_line(fnd_file.log,'g_bal_val.last = '||to_char(g_bal_val.last));
3363
3364
3365 FOR l_index IN g_bal_val.first.. g_bal_val.last LOOP
3366
3367 -- fnd_file.put_line(fnd_file.log,'l_index = '||to_char(l_index));
3368
3369 l_defined_balance_id := GET_DEFINED_BALANCE_ID( g_bal_val(l_index).bal_name );
3370
3371 -- fnd_file.put_line(fnd_file.log,'l_defined_balance_id = '||to_char(l_defined_balance_id));
3372 -- fnd_file.put_line(fnd_file.log,'p_assignment_action_id = '||to_char(p_assignment_action_id));
3373
3374 g_bal_val(l_index).bal_val := pay_balance_pkg.get_value(l_defined_balance_id, p_assignment_action_id);
3375
3376 -- fnd_file.put_line(fnd_file.log,'g_bal_val(l_index).bal_val = '||to_char(g_bal_val(l_index).bal_val));
3377
3378 END LOOP;
3379
3380 -- fnd_file.put_line(fnd_file.log,'end loop FOR g_bal_val');
3381 -- fnd_file.put_line(fnd_file.log,'start asigning balance values');
3382
3383 /* For reference : DBIs used for various balances and reporting values
3384
3385 g_bal_val(1).bal_name := 'AMBABLE_PAY_ASG_YTD'; -- AMB able income ytd
3386 g_bal_val(2).bal_name := 'EMPLOYEE_TAX_ASG_YTD'; -- Tax ytd
3387 g_bal_val(3).bal_name := 'HOLIDAYABLE_PAY_ASG_YTD'; -- Holiday able income ytd
3388 g_bal_val(4).bal_name := 'EMPLOYEE_ATP_DEDUCTIONS_ASG_YTD'; -- ATP contribution ytd
3389 g_bal_val(5).bal_name := 'EMPLOYEE_SP_DEDUCTIONS_ASG_YTD'; -- Special Pension ytd
3390 g_bal_val(6).bal_name := 'EMPLOYER_PENSION_DEDUCTIONS_ASG_YTD'; -- Employer Pension ytd
3391 g_bal_val(7).bal_name := 'EMPLOYEE_PENSION_DEDUCTIONS_ASG_YTD'; -- Employee Pension ytd
3392 g_bal_val(8).bal_name := 'EMPLOYEE_AMB_DEDUCTION_ASG_YTD'; -- For AMB Contribution ytd
3393 g_bal_val(9).bal_name := 'HOLIDAY_ACCRUAL_AMOUNT_ASG_YTD'; -- FOR Calculated holiday pay ytd (Salaried)
3394 g_bal_val(10).bal_name := 'INITIAL_HOL_ENTITLEMENT_DAYS_ASG_HOLIDAY_YTD'; -- Holidays remaining with pay
3395 g_bal_val(11).bal_name := 'HOLIDAY_PAID_DAYS_ASG_HOLIDAY_YTD'; -- Holidays remaining with pay
3396 g_bal_val(12).bal_name := 'HOLIDAY_UNPAID_DAYS_ASG_HOLIDAY_YTD'; -- Holidays remaining without pay
3397 g_bal_val(13).bal_name := 'TOTAL_G_DAGE_PAY_ASG_YTD'; -- G-day's (money)
3398 g_bal_val(14).bal_name := 'TOTAL_G_DAGE_DAYS_ASG_YTD'; -- G-day's (number of day's)
3399 g_bal_val(15).bal_name := 'F_TAX_BASE_ASG_YTD'; -- FOR Rest Amount of F Card
3400 g_bal_val(16).bal_name := 'EMPLOYEE_TAXABLE_BASE_ASG_PTD'; -- Taxable Income
3401 g_bal_val(17).bal_name := 'EMPLOYEE_TAX_ASG_PTD'; -- Tax in period
3402 g_bal_val(18).bal_name := 'HOLIDAY_ACCRUAL_PAY_ASG_YTD'; -- FOR Calculated holiday pay ytd (Hourly Paid)
3403 g_bal_val(19).bal_name := 'HOLIDAY_BANK_PAY_ASG_YTD'; -- FOR AMBable income ytd
3404 g_bal_val(20).bal_name := 'HOLIDAY_TAX_ASG_YTD'; -- FOR Tax ytd
3405 g_bal_val(21).bal_name := 'HOLIDAY_SP_ASG_YTD'; -- FOR Special Pension ytd
3406 -- 10006902 start
3407 --g_bal_val(22).bal_name := 'HOLIDAY_BANK_PAY_ASG_PTD'; -- FOR Taxable Income
3408 g_bal_val(22).bal_name := 'HOLIDAY_ACCRUAL_PAY_ASG_PTD';
3409 --g_bal_val(23).bal_name := 'HOLIDAY_AMB_ASG_PTD'; -- FOR Taxable Income
3410 g_bal_val(23).bal_name := 'HOLIDAY_AMB_REPORTING_ASG_PTD';
3411 -- 10006902 end
3412 g_bal_val(24).bal_name := 'HOLIDAY_SP_ASG_PTD'; -- FOR Taxable Income
3413 g_bal_val(25).bal_name := 'HOLIDAY_TAX_ASG_PTD'; -- FOR Tax in period
3414 g_bal_val(26).bal_name := 'SALARIED_HOL_ACCRUAL_AMOUNT_ASG_PTD'; -- FOR Taxable Income
3415 g_bal_val(27).bal_name := 'SALARIED_HOL_CURR_ENTIT_AMOUNT_ASG_PTD'; -- FOR Taxable Income
3416 g_bal_val(28).bal_name := 'SALARIED_HOL_NEXT_ENTIT_AMOUNT_ASG_PTD'; -- FOR Taxable Income
3417 g_bal_val(29).bal_name := 'HOLIDAY_AMB_ASG_YTD'; -- For AMB Contribution ytd
3418
3419 g_bal_val(31).bal_name := 'TOTAL_AMBABLE_PAY_ASG_YTD';
3420 g_bal_val(32).bal_name := 'TOTAL_AMB_ASG_YTD';
3421 g_bal_val(33).bal_name := 'TOTAL_TAX_ASG_YTD';
3422 g_bal_val(34).bal_name := 'TOTAL_HOLIDAYABLE_PAY_ASG_YTD';
3423
3424 g_bal_val(35).bal_name := 'SALARIED_HOL_ACCRUAL_TAX_ASG_YTD';
3425 g_bal_val(36).bal_name := 'SALARIED_HOL_CURR_ENTIT_TAX_ASG_YTD';
3426 g_bal_val(37).bal_name := 'SALARIED_HOL_NEXT_ENTIT_TAX_ASG_YTD';
3427
3428 g_bal_val(38).bal_name := 'TOTAL_TAX_ASG_PTD';
3429
3430
3431 */ -- End Reference
3432
3433 -- l_ambable_pay_asg_ytd changed for Holiday Pay Changes
3434 -- l_ambable_pay_asg_ytd := g_bal_val(1).bal_val ;
3435 /* 10006902 start */
3436 --l_ambable_pay_asg_ytd := g_bal_val(1).bal_val + g_bal_val(19).bal_val ;
3437 l_ambable_pay_asg_ytd := g_bal_val(31).bal_val ;
3438 /* 10006902 end */
3439
3440 -- l_employee_tax_asg_ytd changed for Holiday Pay Changes
3441 -- l_employee_tax_asg_ytd := g_bal_val(2).bal_val ;
3442 /* 10006902 start */
3443 --l_employee_tax_asg_ytd := g_bal_val(2).bal_val + g_bal_val(20).bal_val ;
3444 l_employee_tax_asg_ytd := g_bal_val(33).bal_val ;
3445 /* 10006902 end */
3446
3447 /* 10006902 start */
3448 --l_holidayable_pay_asg_ytd := g_bal_val(3).bal_val - g_bal_val(30).bal_val ;
3449 l_holidayable_pay_asg_ytd := g_bal_val(34).bal_val ;
3450 /* 10006902 end */
3451 l_emp_atp_dedn_asg_ytd := g_bal_val(4).bal_val ;
3452
3453
3454 -- l_emp_sp_dedn_asg_ytd changed for Holiday Pay Changes
3455 -- l_emp_sp_dedn_asg_ytd := g_bal_val(5).bal_val ;
3456 l_emp_sp_dedn_asg_ytd := g_bal_val(5).bal_val + g_bal_val(21).bal_val ;
3457
3458 l_emplr_pension_dedn_asg_ytd := g_bal_val(6).bal_val ;
3459 l_emp_pension_dedn_asg_ytd := g_bal_val(7).bal_val ;
3460 l_total_pension_asg_ytd := l_emp_pension_dedn_asg_ytd + l_emplr_pension_dedn_asg_ytd ;
3461
3462 -- Bug Fix 5080969
3463 -- AMB Contribution ytd = EMPLOYEE_AMB_DEDUCTION_ASG_YTD + HOLIDAY_AMB_ASG_YTD
3464 -- l_emp_amb_dedn_asg_ytd := g_bal_val(8).bal_val ;
3465
3466 /* 10006902 start */
3467 --l_emp_amb_dedn_asg_ytd := g_bal_val(8).bal_val + g_bal_val(29).bal_val;
3468 l_emp_amb_dedn_asg_ytd := g_bal_val(32).bal_val ;
3469 /* 10006902 end */
3470
3471 --l_calc_holiday_pay_asg_ytd := g_bal_val(9).bal_val ;
3472 l_hol_rem_with_pay_asg_ytd := g_bal_val(10).bal_val - g_bal_val(11).bal_val ;
3473 l_hol_rem_without_pay := g_bal_val(12).bal_val ;
3474
3475 l_total_g_dage_pay_asg_ytd := g_bal_val(13).bal_val ;
3476 l_total_g_dage_days_asg_ytd := g_bal_val(14).bal_val ;
3477
3478 -- l_rest_amount_of_f_card := greatest( (g_tax_card_tab(4).screen_entry_val - g_bal_val(15).bal_val) , 0 ) ;
3479 l_rest_amount_of_f_card := greatest( ( nvl(g_tax_card_tab(4).screen_entry_val,0) - g_bal_val(15).bal_val) , 0 ) ;
3480 -- Tax Free Allowance (from Tax Card) - Taxable Income Year to date
3481
3482 -- Bug Fix 4704284 : start
3483
3484 -- l_taxable_pay_asg_ptd := g_bal_val(16).bal_val ;
3485
3486
3487 -- Additional Balances for Holiday Pay Changes
3488 -- Taxable Income = EMPLOYEE_TAXABLE_BASE_ASG_PTD + ( l_income_from_hol_Pay - HOLIDAY_AMB_ASG_PTD - HOLIDAY_SP_ASG_PTD )
3489
3490 -- if employee is salaried ,
3491 -- then l_income_from_hol_Pay = Salaried Hol Accrual Amount_ASG_PTD
3492 -- + Salaried Hol Curr Entit Amount_ASG_PTD
3493 -- + Salaried Hol Next Entit Amount_ASG_PTD )
3494 -- else (employee is hourly paid)
3495 -- then l_income_from_hol_Pay = HOLIDAY_BANK_PAY_ASG_PTD
3496
3497 l_income_from_hol_Pay_hr := g_bal_val(22).bal_val ;
3498 l_income_from_hol_Pay_sal := g_bal_val(26).bal_val + g_bal_val(27).bal_val + g_bal_val(28).bal_val +
3499 g_bal_val(35).bal_val + g_bal_val(36).bal_val + g_bal_val(37).bal_val ; /* 10006902 */
3500
3501 /* pgopal - Bug 5747199,hourly/salaried issue fix*/
3502
3503 /*SELECT decode (l_period_type, 'Calendar Month', l_income_from_hol_Pay_sal, l_income_from_hol_Pay_hr)
3504 INTO l_income_from_hol_Pay
3505 FROM dual ;*/
3506
3507 l_hourly_salaried := pay_dk_general.get_hour_sal_flag(p_assignment_id,p_effective_date);
3508
3509 IF l_hourly_salaried IS NULL THEN
3510 IF l_period_type = 'Calendar Month' THEN
3511 l_hourly_salaried := 'S';
3512 ELSE
3513 l_hourly_salaried := 'H';
3514 END IF ;
3515 END IF ;
3516
3517 SELECT decode (l_hourly_salaried, 'S', l_income_from_hol_Pay_sal, l_income_from_hol_Pay_hr)
3518 INTO l_income_from_hol_Pay
3519 FROM dual ;
3520
3521
3522 -- l_emp_taxable_base_asg_ptd changed for Holiday Pay Changes
3523 -- l_emp_taxable_base_asg_ptd := g_bal_val(16).bal_val ;
3524 -- 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 ) ;
3525 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 ) ;
3526
3527
3528 -- Bug Fix 4704284 : end
3529
3530 -- l_employee_tax_asg_ptd changed for Holiday Pay Changes
3531 -- l_employee_tax_asg_ptd := g_bal_val(17).bal_val ;
3532 /* 10006902 start */
3533 -- l_employee_tax_asg_ptd := g_bal_val(17).bal_val + g_bal_val(25).bal_val ;
3534 l_employee_tax_asg_ptd := g_bal_val(38).bal_val ;
3535 /* 10006902 end */
3536
3537 -- Tax percentage value already fetched above
3538 -- l_tax_percent := g_tax_card_tab(3).screen_entry_val ; -- from tax card
3539 -- fnd_file.put_line(fnd_file.log,' DK EMPLOYEE DETAILS : l_tax_percent = '|| l_tax_percent);
3540
3541
3542 -- fnd_file.put_line(fnd_file.log,'finish asigning balance values');
3543
3544 OPEN csr_payroll (p_payroll_action_id) ;
3545 FETCH csr_payroll INTO l_payroll_id;
3546 CLOSE csr_payroll ;
3547
3548 -- fnd_file.put_line(fnd_file.log,'closed csr_payroll');
3549
3550 OPEN csr_payroll_details (l_payroll_id);
3551 FETCH csr_payroll_details INTO l_payroll_name , l_period_type ;
3552 CLOSE csr_payroll_details ;
3553
3554 -- fnd_file.put_line(fnd_file.log,'closed csr_payroll_details');
3555
3556 SELECT decode (l_period_type
3557 ,'Calendar Month',g_tax_card_tab(5).screen_entry_val
3558 ,'Bi-Week',g_tax_card_tab(6).screen_entry_val
3559 ,'Week',g_tax_card_tab(7).screen_entry_val
3560 ,'Lunar Month',fnd_number.number_to_canonical(round(fnd_number.canonical_to_number(g_tax_card_tab(5).screen_entry_val)*12/13))) /*10262139 fix*/
3561 INTO l_tax_deduction
3562 FROM dual ;
3563
3564 -- l_calc_holiday_pay_asg_ytd := g_bal_val(9).bal_val ;
3565
3566 -- g_bal_val(9).bal_val => 'HOLIDAY_ACCRUAL_AMOUNT_ASG_YTD' -- FOR Calculated holiday pay ytd (Salaried)
3567 -- g_bal_val(18).bal_val => 'HOLIDAY_ACCRUAL_PAY_ASG_YTD' -- FOR Calculated holiday pay ytd (Hourly Paid)
3568
3569 /* pgopal - Bug 5747199,hourly/salaried issue fix*/
3570
3571 /*SELECT decode (l_period_type ,
3572 'Calendar Month',
3573 g_bal_val(9).bal_val ,
3574 g_bal_val(18).bal_val)
3575 INTO l_calc_holiday_pay_asg_ytd
3576 FROM dual ;*/
3577
3578 SELECT decode (l_hourly_salaried, 'S',
3579 g_bal_val(9).bal_val ,
3580 g_bal_val(18).bal_val)
3581 INTO l_calc_holiday_pay_asg_ytd
3582 FROM dual ;
3583
3584 /*
3585 SELECT decode (l_period_type ,
3586 'Calendar Month',
3587 707 ,
3588 808 )
3589 INTO l_calc_holiday_pay_asg_ytd
3590 FROM dual ;
3591 */
3592
3593 -- fnd_file.put_line(fnd_file.log,'after the select decode');
3594
3595 -- fnd_file.put_line(fnd_file.log,'starting archiving DK EMPLOYEE DETAILS');
3596
3597 pay_action_information_api.create_action_information (
3598 p_action_information_id => l_action_info_id
3599 ,p_action_context_id => p_archive_assact_id
3600 ,p_action_context_type => 'AAP'
3601 ,p_object_version_number => l_ovn
3602 ,p_effective_date => p_effective_date
3603 ,p_source_id => NULL
3604 ,p_source_text => NULL
3605 ,p_action_information_category => 'DK EMPLOYEE DETAILS'
3606 ,p_action_information1 => l_ambable_pay_asg_ytd -- AMB able income ytd
3607 ,p_action_information2 => l_employee_tax_asg_ytd -- Tax ytd
3608 ,p_action_information3 => l_holidayable_pay_asg_ytd -- Holiday able income ytd
3609 ,p_action_information4 => l_emp_atp_dedn_asg_ytd -- ATP contribution ytd
3610 ,p_action_information5 => l_emp_sp_dedn_asg_ytd -- Special Pension ytd
3611 ,p_action_information6 => l_total_pension_asg_ytd -- Total Pension ytd
3612 ,p_action_information7 => l_emplr_pension_dedn_asg_ytd -- Employer Pension ytd
3613 ,p_action_information8 => l_emp_pension_dedn_asg_ytd -- Employee Pension ytd
3614 ,p_action_information9 => l_emp_amb_dedn_asg_ytd -- AMB Contribution ytd
3615 ,p_action_information10 => l_calc_holiday_pay_asg_ytd -- Calculated holiday pay ytd
3616 ,p_action_information11 => l_hol_rem_with_pay_asg_ytd -- Holidays remaining with pay
3617 ,p_action_information12 => l_hol_rem_without_pay -- Holidays remaining without pay
3618 ,p_action_information13 => l_total_g_dage_pay_asg_ytd -- G-day's (money)
3619 ,p_action_information14 => l_total_g_dage_days_asg_ytd -- G-day's (number of day's)
3620 --,p_action_information15 => l_time_off_in_lieu_hours -- Time off in lieu hours
3621 ,p_action_information15 => l_rest_amount_of_f_card -- Rest Amount of F Card
3622 -- Bug Fix 4704284 : start
3623 --,p_action_information16 => l_taxable_pay_asg_ptd -- Taxable Income
3624 ,p_action_information16 => l_emp_taxable_base_asg_ptd -- Taxable Income
3625 -- Bug Fix 4704284 : end
3626 ,p_action_information17 => l_employee_tax_asg_ptd -- Tax in period
3627 ,p_action_information18 => l_tax_deduction -- Tax Deduction
3628 ,p_action_information19 => l_tax_percent -- Tax Percent
3629 --,p_action_information21 => l_net_pay -- Net Pay
3630 ,p_assignment_id => p_assignment_id);
3631
3632 -- fnd_file.put_line(fnd_file.log,'finished archiving DK EMPLOYEE DETAILS');
3633 -- fnd_file.put_line(fnd_file.log,'leaving ARCHIVE_ADDL_EMP_DETAILS');
3634
3635
3636 END ARCHIVE_ADDL_EMP_DETAILS;
3637
3638 --------------------------------------- PROCEDURE ARCHIVE_MAIN_ELEMENTS ---------------------------------------------------------
3639
3640 /* ARCHIVE EARNINGS AND DEDUCTIONS ELEMENTS REGION */
3641
3642 PROCEDURE ARCHIVE_MAIN_ELEMENTS
3643 (p_archive_assact_id IN NUMBER,
3644 p_assignment_action_id IN NUMBER,
3645 p_assignment_id IN NUMBER,
3646 p_date_earned IN DATE,
3647 p_effective_date IN DATE ) IS
3648
3649 ----------------
3650
3651 /* Cursor to retrieve Earnings Element Information */
3652
3653 CURSOR csr_ear_element_info IS
3654 SELECT nvl(pettl.reporting_name,pettl.element_name) rep_name
3655 ,et.element_type_id element_type_id
3656 ,iv.input_value_id input_value_id
3657 ,iv.uom uom
3658 ,et.processing_priority processing_priority
3659 FROM pay_element_types_f et
3660 , pay_element_types_f_tl pettl
3661 , pay_input_values_f iv
3662 , pay_element_classifications classification
3663 WHERE et.element_type_id = iv.element_type_id
3664 AND et.element_type_id = pettl.element_type_id
3665 AND pettl.language = USERENV('LANG')
3666 AND iv.name = 'Pay Value'
3667 AND classification.classification_id = et.classification_id
3668 AND classification.classification_name
3669 IN ('Direct Payments','Income','Special Pay','B Income') /* 8849449 */
3670 AND p_date_earned BETWEEN et.effective_start_date AND et.effective_end_date
3671 AND p_date_earned BETWEEN iv.effective_start_date AND iv.effective_end_date
3672 AND ((et.business_group_id IS NULL AND et.legislation_code = 'DK')
3673 OR (et.business_group_id = g_business_group_id AND et.legislation_code IS NULL));
3674
3675 ---------------
3676
3677 /* Cursor to retrieve Deduction Element Information */
3678 CURSOR csr_ded_element_info IS
3679 SELECT nvl(pettl.reporting_name,pettl.element_name) rep_name
3680 ,et.element_type_id element_type_id
3681 ,iv.input_value_id input_value_id
3682 ,iv.uom uom
3683 ,et.processing_priority processing_priority
3684 FROM pay_element_types_f et
3685 , pay_element_types_f_tl pettl
3686 , pay_input_values_f iv
3687 , pay_element_classifications classification
3688 WHERE et.element_type_id = iv.element_type_id
3689 AND et.element_type_id = pettl.element_type_id
3690 AND pettl.language = USERENV('LANG')
3691 AND iv.name = 'Pay Value'
3692 AND classification.classification_id = et.classification_id
3693 AND classification.classification_name
3694 IN ('Involuntary Deductions','Pre-Tax Deductions','Statutory Deductions','Voluntary Deductions')
3695 AND p_date_earned BETWEEN et.effective_start_date AND et.effective_end_date
3696 AND p_date_earned BETWEEN iv.effective_start_date AND iv.effective_end_date
3697 AND ((et.business_group_id IS NULL AND et.legislation_code = 'DK')
3698 OR (et.business_group_id = g_business_group_id AND et.legislation_code IS NULL));
3699
3700 ---------------
3701
3702 /* Cursor to retrieve run result value of Main Elements */
3703 /* Modified for Pension changes */
3704 CURSOR csr_result_value(p_iv_id NUMBER
3705 ,p_ele_type_id NUMBER
3706 ,p_assignment_action_id NUMBER) IS
3707 SELECT rrv.result_value result_value,
3708 rr.element_entry_id element_entry_id
3709 /* Added for Pension changes */
3710 , rr.run_result_id run_result_id
3711 FROM pay_run_result_values rrv
3712 ,pay_run_results rr
3713 ,pay_assignment_actions paa
3714 ,pay_payroll_actions ppa
3715 WHERE rrv.input_value_id = p_iv_id
3716 AND rr.element_type_id = p_ele_type_id
3717 AND rr.run_result_id = rrv.run_result_id
3718 AND rr.assignment_action_id = paa.assignment_action_id
3719 AND paa.assignment_action_id = p_assignment_action_id
3720 AND ppa.payroll_action_id = paa.payroll_action_id
3721 AND ppa.action_type IN ('Q','R')
3722 AND rrv.result_value IS NOT NULL;
3723
3724 /* Added for Pension changes */
3725 CURSOR csr_get_ded_pen_dtl(p_effective_date DATE
3726 ,p_element_type_id NUMBER
3727 ,p_input_value_id NUMBER
3728 ) IS
3729 SELECT pai.action_information_id
3730 FROM pay_action_information pai
3731 WHERE pai.action_context_type = 'PA'
3732 AND pai.effective_date = p_effective_date
3733 AND pai.action_information_category = 'EMEA ELEMENT DEFINITION'
3734 AND pai.action_information2 = to_char(p_element_type_id)
3735 AND pai.action_information3 = to_char(p_input_value_id)
3736 AND pai.action_information5 = 'D'
3737 AND pai.action_information7 = 'D';
3738
3739 /* Added for Pension changes */
3740 CURSOR csr_get_pen_iv_id(p_effective_date DATE
3741 ,p_element_type_id NUMBER) IS
3742 SELECT piv.input_value_id
3743 FROM pay_input_values_f piv
3744 WHERE piv.element_type_id = p_element_type_id
3745 AND piv.name= 'Third Party Payee'
3746 AND p_effective_date between piv.effective_start_date and piv.effective_end_date;
3747 -------
3748 CURSOR csr_get_pp_name(p_effective_date DATE ,
3749 p_run_result_id NUMBER) IS
3750 SELECT hou.name
3751 FROM
3752 pay_run_result_values rrv
3753 , pay_input_values_f iv
3754 , hr_organization_units hou
3755 WHERE rrv.run_result_id = p_run_result_id
3756 AND rrv.input_value_id = iv.input_value_id
3757 AND iv.name = 'Third Party Payee'
3758 AND p_effective_date between
3759 iv.effective_start_date and iv.effective_end_date
3760 AND fnd_number.number_to_canonical(hou.organization_id) = rrv.result_value -- Bug 7656164
3761 --AND hou.organization_id = fnd_number.canonical_to_number(rrv.result_value)
3762 AND p_effective_date between hou.date_from and nvl(hou.date_to, p_effective_date);
3763
3764 ---------------------------------------------------------------------------------
3765 ---------------------------------------------------------------------------------
3766 -- Adding the Conditions below for Payslip Format Changes - w.r.t bug - 7229247
3767 /******************************** Start******************************/
3768 ---------------
3769 -- Cursor to pick up segment from DK_SOE_ELEMENT_ADD_DETAILS context Org Developer DF
3770 CURSOR csr_group_by( p_ele_type_id NUMBER ) IS
3771 select ORG_INFORMATION3,ORG_INFORMATION6,ORG_INFORMATION7,ORG_INFORMATION8,ORG_INFORMATION9,ORG_INFORMATION10
3772 from hr_organization_information code
3773 where code.organization_id = g_business_group_id
3774 and code.org_information_context ='DK_SOE_ELEMENT_ADD_DETAILS'
3775 and code.org_information1 =fnd_number.number_to_canonical(p_ele_type_id);
3776
3777 ------------------------------------------------------------------------------------
3778 /* Cursor to retrieve run result value of Main Elements */
3779 ------------------------------------------------------------------------------------
3780 CURSOR csr_result_value_EE(p_iv_id NUMBER
3781 ,p_ele_type_id NUMBER
3782 ,p_assignment_action_id NUMBER
3783 ,p_EE_ID NUMBER) IS
3784 SELECT rrv.result_value
3785 FROM pay_run_result_values rrv
3786 ,pay_run_results rr
3787 ,pay_assignment_actions paa
3788 ,pay_payroll_actions ppa
3789 WHERE rrv.input_value_id = p_iv_id
3790 AND rr.element_type_id = p_ele_type_id
3791 AND rr.run_result_id = rrv.run_result_id
3792 AND rr.assignment_action_id = paa.assignment_action_id
3793 AND paa.assignment_action_id = p_assignment_action_id
3794 AND ppa.payroll_action_id = paa.payroll_action_id
3795 AND ppa.action_type IN ('Q','R')
3796 AND rrv.result_value IS NOT NULL
3797 AND rr.element_entry_id = p_EE_ID;
3798 -----------------------------------------------------------------------------
3799 /* Cursor to retrieve sum of run result value for an given Main Element */
3800 -----------------------------------------------------------------------------
3801 CURSOR csr_sum_of_result_values(p_iv_id NUMBER
3802 ,p_ele_type_id NUMBER
3803 ,p_assignment_action_id NUMBER
3804 ) IS
3805 SELECT sum(fnd_number.canonical_to_number(rrv.result_value)) result_value
3806 ,count(rrv.RUN_RESULT_ID) record_count
3807 ,rrv.result_value UNIT_PRICE
3808 FROM pay_run_result_values rrv
3809 ,pay_run_results rr
3810 ,pay_assignment_actions paa
3811 ,pay_payroll_actions ppa
3812 WHERE rrv.input_value_id = p_iv_id
3813 AND rr.element_type_id = p_ele_type_id
3814 AND rr.run_result_id = rrv.run_result_id
3815 AND rr.assignment_action_id = paa.assignment_action_id
3816 AND paa.assignment_action_id = p_assignment_action_id
3817 AND ppa.payroll_action_id = paa.payroll_action_id
3818 AND ppa.action_type IN ('Q','R')
3819 AND rrv.result_value IS NOT NULL
3820 group by rrv.result_value;
3821
3822 rec_group_by csr_group_by%ROWTYPE;
3823
3824
3825 ------------------------------------------------------------------------------------------------
3826 /* Cursor to retrieve sum of all run result value for an given Main Element */
3827 ------------------------------------------------------------------------------------------------
3828 CURSOR csr_all_sum_result_values(p_iv_id_UNIT NUMBER,p_iv_id_AMOUNT NUMBER,p_iv_id_UNIT_PRICE NUMBER
3829 ,p_ele_type_id NUMBER
3830 ,p_assignment_action_id NUMBER
3831 ) IS
3832 SELECT rrv3.result_value UNIT_PRICE ,
3833 sum(fnd_number.canonical_to_number(rrv1.result_value)) UNIT,
3834 sum(fnd_number.canonical_to_number(rrv2.result_value)) AMOUNT
3835 FROM pay_run_result_values rrv1
3836 ,pay_run_results rr1
3837 ,pay_assignment_actions paa
3838 ,pay_payroll_actions ppa
3839 ,pay_run_result_values rrv2
3840 ,pay_run_results rr2
3841 ,pay_run_result_values rrv3
3842 ,pay_run_results rr3
3843 WHERE rrv1.input_value_id = p_iv_id_UNIT
3844 AND rr1.element_type_id = p_ele_type_id
3845 AND rr1.run_result_id = rrv1.run_result_id
3846 AND rr1.assignment_action_id = paa.assignment_action_id
3847 AND paa.assignment_action_id = p_assignment_action_id
3848 AND ppa.payroll_action_id = paa.payroll_action_id
3849 AND ppa.action_type IN ('Q','R')
3850 and rrv2.input_value_id = p_iv_id_AMOUNT
3851 AND rr2.run_result_id = rrv2.run_result_id
3852 AND NVL(rr2.element_entry_id,-1) = NVL(rr1.element_entry_id,-1) -- Bug#9289267 fix
3853 AND rr2.assignment_action_id = paa.assignment_action_id -- Code added for Recurring Elements Cumulation
3854 and rrv3.input_value_id = p_iv_id_UNIT_PRICE
3855 AND rr3.run_result_id = rrv3.run_result_id
3856 AND NVL(rr3.element_entry_id,-1) = NVL(rr1.element_entry_id,-1) -- Bug#9289267 fix
3857 AND rr3.assignment_action_id = paa.assignment_action_id -- Code added for Recurring Elements Cumulation
3858 group by rrv3.result_value;
3859
3860 -----------------------------------------------------------------------------------------------------
3861 -----------------------------------------------------------------------------------------------------
3862 /* Cursor to retrieve sum of all run result value for an given Main Element */
3863 -----------------------------------------------------------------------------------------------------
3864 CURSOR csr_all_result_values(p_iv_id_UNIT NUMBER,p_iv_id_AMOUNT NUMBER,p_iv_id_UNIT_PRICE NUMBER
3865 ,p_ele_type_id NUMBER
3866 ,p_assignment_action_id NUMBER
3867 ) IS
3868 SELECT rrv3.result_value UNIT_PRICE ,
3869 rrv1.result_value UNIT,
3870 rrv2.result_value AMOUNT
3871 FROM pay_run_result_values rrv1
3872 ,pay_run_results rr1
3873 ,pay_assignment_actions paa
3874 ,pay_payroll_actions ppa
3875 ,pay_run_result_values rrv2
3876 ,pay_run_results rr2
3877 ,pay_run_result_values rrv3
3878 ,pay_run_results rr3
3879 WHERE rrv1.input_value_id = p_iv_id_UNIT
3880 AND rr1.element_type_id = p_ele_type_id
3881 AND rr1.run_result_id = rrv1.run_result_id
3882 AND rr1.assignment_action_id = paa.assignment_action_id
3883 AND paa.assignment_action_id = p_assignment_action_id
3884 AND ppa.payroll_action_id = paa.payroll_action_id
3885 AND ppa.action_type IN ('Q','R')
3886 and rrv2.input_value_id = p_iv_id_AMOUNT
3887 AND rr2.run_result_id = rrv2.run_result_id
3888 AND NVL(rr2.element_entry_id,-1) = NVL(rr1.element_entry_id,-1) -- Bug#9289267 fix
3889 AND rr2.assignment_action_id = paa.assignment_action_id -- Code added for Recurring Elements Cumulation
3890 and rrv3.input_value_id = p_iv_id_UNIT_PRICE
3891 AND rr3.run_result_id = rrv3.run_result_id
3892 AND NVL(rr3.element_entry_id,-1) = NVL(rr1.element_entry_id,-1) -- Bug#9289267 fix
3893 AND rr3.assignment_action_id = paa.assignment_action_id; -- Code added for Recurring Elements Cumulation
3894
3895
3896 -----------------------------------------------------------------------------------------------
3897 /************************************************End******************************************************/
3898
3899
3900
3901
3902 l_result_value pay_run_result_values.result_value%TYPE :=0 ;
3903 l_action_info_id NUMBER;
3904 l_ovn NUMBER;
3905 l_element_context VARCHAR2(10);
3906 l_index NUMBER := 0;
3907 l_formatted_value VARCHAR2(50) := NULL;
3908 l_flag NUMBER := 0;
3909
3910 /* Added for Pension changes */
3911
3912 l_ovn_pen NUMBER;
3913 l_iv_id_pen NUMBER :=0;
3914 l_action_info_id_pen NUMBER:=0;
3915 l_new_rep_name VARCHAR2(80) ;
3916 l_rr_id_pen NUMBER :=0;
3917 l_ele_pen_context VARCHAR2(3);
3918 l_ele_pen_context_desc VARCHAR2(80);
3919 rec_get_pp_name csr_get_pp_name%ROWTYPE;
3920 rec_result_val csr_result_value%ROWTYPE;
3921
3922
3923
3924 /*Added for Payslip format Changes - bug 7229247*/
3925 l_group_by number;
3926 l_unit_price NUMBER ;
3927 l_unit NUMBER ;
3928 l_amount NUMBER ;
3929
3930 ----------------
3931
3932
3933 BEGIN
3934
3935 IF g_debug THEN
3936 hr_utility.set_location(' Entering Procedure ARCHIVE_MAIN_ELEMENTS',320);
3937 END IF;
3938 /*********************************************************************************************/
3939 /**********************************************Old Code - Starts******************************/
3940 /********************************************************************************************
3941
3942 -- Archiving Earnings Elements
3943
3944 FOR csr_rec IN csr_ear_element_info LOOP
3945
3946 l_result_value := NULL;
3947 /* Payslip Format Changes
3948 -- Start
3949 l_group_by :=null;
3950 l_unit_price :=null;
3951 -- End
3952
3953 BEGIN
3954 /*
3955 OPEN csr_result_value(csr_rec.input_value_id ,csr_rec.element_type_id ,p_assignment_action_id);
3956 FETCH csr_result_value INTO l_result_value;
3957 CLOSE csr_result_value;
3958 */
3959
3960 -- Fix to handle Multiple Element Entries
3961
3962 /* get the element run result value
3963 OPEN csr_result_value(csr_rec.input_value_id ,csr_rec.element_type_id ,p_assignment_action_id);
3964 LOOP
3965 /* Added for Pension changes
3966 FETCH csr_result_value INTO l_result_value,l_rr_id_pen;
3967 EXIT WHEN csr_result_value%NOTFOUND;
3968
3969
3970 IF l_result_value is not null THEN
3971 pay_action_information_api.create_action_information (
3972 p_action_information_id => l_action_info_id
3973 ,p_action_context_id => p_archive_assact_id
3974 ,p_action_context_type => 'AAP'
3975 ,p_object_version_number => l_ovn
3976 ,p_effective_date => p_effective_date
3977 ,p_source_id => NULL
3978 ,p_source_text => NULL
3979 ,p_action_information_category => 'EMEA ELEMENT INFO'
3980 ,p_action_information1 => csr_rec.element_type_id
3981 ,p_action_information2 => csr_rec.input_value_id
3982 ,p_action_information3 => 'E'
3983 ,p_action_information4 => l_result_value --l_formatted_value
3984 ,p_action_information9 => 'Earning Element'
3985 ,p_assignment_id => p_assignment_id
3986 ,p_action_information8 => csr_rec.processing_priority
3987 );
3988 END IF;
3989
3990 END LOOP;
3991 CLOSE csr_result_value;
3992
3993 -- End Fix to handle Multiple Element Entries
3994
3995 EXCEPTION WHEN OTHERS THEN
3996 g_err_num := SQLCODE;
3997 /*fnd_file.put_line(fnd_file.log,'ORA_ERR: ' || g_err_num || 'In ARCHIVE_MAIN_ELEMENTS');
3998
3999 IF g_debug THEN
4000 hr_utility.set_location('ORA_ERR: ' || g_err_num || 'In ARCHIVE_MAIN_ELEMENTS',330);
4001 END IF;
4002 END;
4003 END LOOP;
4004
4005
4006
4007 -- Archiving Deduction Elements
4008
4009 FOR csr_rec IN csr_ded_element_info LOOP
4010
4011 l_result_value := NULL;
4012
4013 BEGIN
4014 /*
4015 OPEN csr_result_value(csr_rec.input_value_id ,csr_rec.element_type_id ,p_assignment_action_id);
4016 FETCH csr_result_value INTO l_result_value;
4017 CLOSE csr_result_value;
4018 */
4019
4020 -- Fix to handle Multiple Element Entries
4021
4022 /* get the element run result value
4023 OPEN csr_result_value(csr_rec.input_value_id ,csr_rec.element_type_id ,p_assignment_action_id);
4024 LOOP
4025 /* Added for Pension changes
4026 FETCH csr_result_value INTO l_result_value,l_rr_id_pen;
4027 EXIT WHEN csr_result_value%NOTFOUND;
4028
4029 /* Added for Pension changes -start */
4030
4031 /* IF( csr_rec.rep_name IN ('Pension','Employer Pension','Retro Pension','Retro Employer Pension')) THEN
4032
4033 OPEN csr_get_pen_iv_id(p_effective_date ,csr_rec.element_type_id);
4034 FETCH csr_get_pen_iv_id INTO l_iv_id_pen;
4035 CLOSE csr_get_pen_iv_id;
4036
4037 OPEN csr_get_pp_name(p_effective_date , l_rr_id_pen);
4038 FETCH csr_get_pp_name INTO rec_get_pp_name;
4039 CLOSE csr_get_pp_name;
4040
4041 OPEN csr_get_ded_pen_dtl(p_effective_date ,csr_rec.element_type_id ,csr_rec.input_value_id);
4042 FETCH csr_get_ded_pen_dtl INTO l_action_info_id_pen;
4043 CLOSE csr_get_ded_pen_dtl;
4044
4045 /* Commented to avoid updating the element definition
4046 l_new_rep_name := csr_rec.rep_name|| ' ( ' ||rec_get_pp_name.name|| ' ) ' ;
4047
4048 pay_action_information_api.update_action_information(
4049 p_action_information_id => l_action_info_id_pen
4050 ,p_object_version_number => l_ovn_pen
4051 ,p_action_information4 => l_new_rep_name); */
4052 /*
4053 l_ele_pen_context:='PP';
4054 l_ele_pen_context_desc:= rec_get_pp_name.name;
4055
4056
4057 Else
4058 l_ele_pen_context:='D';
4059 l_ele_pen_context_desc:='Deduction Element';
4060
4061
4062
4063 END IF; */
4064
4065 /* Added for Pension changes -end */
4066
4067 /*
4068 IF l_result_value is not null THEN
4069 pay_action_information_api.create_action_information (
4070 p_action_information_id => l_action_info_id
4071 ,p_action_context_id => p_archive_assact_id
4072 ,p_action_context_type => 'AAP'
4073 ,p_object_version_number => l_ovn
4074 ,p_effective_date => p_effective_date
4075 ,p_source_id => NULL
4076 ,p_source_text => NULL
4077 ,p_action_information_category => 'EMEA ELEMENT INFO'
4078 ,p_action_information1 => csr_rec.element_type_id
4079 ,p_action_information2 => csr_rec.input_value_id
4080 ,p_action_information3 => l_ele_pen_context -- Added for Pension Changes
4081 ,p_action_information4 => l_result_value --l_formatted_value
4082 ,p_action_information9 => l_ele_pen_context_desc -- Added for Pension Changes
4083 ,p_assignment_id => p_assignment_id
4084 ,p_action_information8 => csr_rec.processing_priority
4085 );
4086 END IF;
4087
4088 END LOOP;
4089 CLOSE csr_result_value; */
4090
4091 -- End Fix to handle Multiple Element Entries
4092 /************************************************* Old Code Ends **************************************************************/
4093
4094 ----------------------------------------------------------------------------------------------------------------------------------
4095
4096 /*******************************************************************************************************************************/
4097 /**********Adding the Conditions below for Payslip Format Changes - w.r.t bug - 7229247*****************************************/
4098 /*******************************************Start*******************************************************************************/
4099 -- Archiving Earnings Elements
4100
4101
4102
4103 FOR csr_rec IN csr_ear_element_info
4104 LOOP
4105
4106 l_result_value := null;
4107 l_group_by := null;
4108 l_unit_price :=null;
4109
4110 BEGIN
4111 -- Conditions below are added to flush the Record Set After a Successful Query
4112 -- so that it starts afresh for the next element
4113 rec_group_by.ORG_INFORMATION6:= NULL;
4114 rec_group_by.ORG_INFORMATION3:= NULL;
4115
4116 OPEN csr_group_by(csr_rec.element_type_id );
4117 FETCH csr_group_by INTO rec_group_by;
4118 CLOSE csr_group_by;
4119
4120 -- The se_soe contains
4121 -- segment 3 = > I or O
4122 -- segment 6 = > Y or N
4123 -- segment 7 = > Input ID UNIT
4124 -- segment 8 = > Input ID UNIT PRICE
4125 -- segment 9 = > Input ID Amount
4126 fnd_file.put_line(fnd_file.log,'p_assignment_action_id'||p_assignment_action_id);
4127 fnd_file.put_line(fnd_file.log,'csr_rec.rep_name'|| csr_rec.rep_name);
4128 fnd_file.put_line(fnd_file.log,'rec_group_by.ORG_INFORMATION3'|| rec_group_by.ORG_INFORMATION3);
4129 fnd_file.put_line(fnd_file.log,'rec_group_by.ORG_INFORMATION6'|| rec_group_by.ORG_INFORMATION6);
4130 fnd_file.put_line(fnd_file.log,'rec_group_by.ORG_INFORMATION7'|| rec_group_by.ORG_INFORMATION7);
4131 fnd_file.put_line(fnd_file.log,'rec_group_by.ORG_INFORMATION8'|| rec_group_by.ORG_INFORMATION8);
4132 fnd_file.put_line(fnd_file.log,'rec_group_by.ORG_INFORMATION9'|| rec_group_by.ORG_INFORMATION9);
4133
4134 IF ( (rec_group_by.ORG_INFORMATION3 = 'O') or (rec_group_by.ORG_INFORMATION3 IS NULL) )
4135 THEN
4136 -- Case for Group by or NOT
4137 -- Segemnt 6 is allowed here, as it makes sense.
4138
4139
4140 IF ( rec_group_by.ORG_INFORMATION6 = 'N' or (rec_group_by.ORG_INFORMATION3 IS NULL) )
4141 THEN
4142 -- This case is for individual representation of each element.
4143 -- unit and unit price should be absent.
4144 -- fnd_file.put_line(fnd_file.log,'p_assignment_action_id'||p_assignment_action_id);
4145
4146 FOR csr_result_rec IN csr_result_value(csr_rec.input_value_id ,csr_rec.element_type_id ,p_assignment_action_id)
4147 LOOP
4148
4149
4150 IF csr_result_rec.result_value is not null THEN
4151 fnd_file.put_line(fnd_file.log,' 1 csr_result_rec.result_value '||csr_result_rec.result_value);
4152 pay_action_information_api.create_action_information (
4153 p_action_information_id => l_action_info_id
4154 ,p_action_context_id => p_archive_assact_id
4155 ,p_action_context_type => 'AAP'
4156 ,p_object_version_number => l_ovn
4157 ,p_effective_date => p_effective_date
4158 ,p_source_id => NULL
4159 ,p_source_text => NULL
4160 ,p_action_information_category => 'EMEA ELEMENT INFO'
4161 ,p_action_information1 => csr_rec.element_type_id
4162 ,p_action_information2 => csr_rec.input_value_id
4163 ,p_action_information3 => 'E'
4164 ,p_action_information4 => csr_result_rec.result_value --l_formatted_value 9316928
4165 ,p_action_information8 => ''
4166 ,p_action_information9 => 'Earning Element:'
4167 ,p_action_information10 => csr_rec.processing_priority /* 9358829 */
4168 ,p_assignment_id => p_assignment_id);
4169 fnd_file.put_line(fnd_file.log,' 1 csr_result_rec.result_value '||csr_result_rec.result_value);
4170
4171 END IF;
4172 END LOOP;
4173
4174
4175 ELSIF rec_group_by.ORG_INFORMATION6 = 'Y'
4176 THEN
4177
4178
4179 -- This case is for Grouping by pay value of each element.
4180 -- unit and unit price should be present
4181 FOR csr_result_rec IN csr_sum_of_result_values(csr_rec.input_value_id
4182 ,csr_rec.element_type_id
4183 ,p_assignment_action_id )
4184 LOOP
4185
4186
4187
4188 IF csr_result_rec.result_value is not null THEN
4189 fnd_file.put_line(fnd_file.log,' 2 csr_result_rec.result_value '||csr_result_rec.result_value);
4190 pay_action_information_api.create_action_information (
4191 p_action_information_id => l_action_info_id
4192 ,p_action_context_id => p_archive_assact_id
4193 ,p_action_context_type => 'AAP'
4194 ,p_object_version_number => l_ovn
4195 ,p_effective_date => p_effective_date
4196 ,p_source_id => NULL
4197 ,p_source_text => NULL
4198 ,p_action_information_category => 'EMEA ELEMENT INFO'
4199 ,p_action_information1 => csr_rec.element_type_id
4200 ,p_action_information2 => csr_rec.input_value_id
4201 ,p_action_information3 => 'E'
4202 ,p_action_information4 => fnd_number.number_to_canonical(csr_result_rec.result_value) /* l_formatted_value 9316928 select query aplied cannonical to num */
4203 ,p_action_information8 => csr_result_rec.record_count
4204 ,p_action_information9 => 'Earning Element unit per price:'||csr_result_rec.UNIT_PRICE
4205 ,p_action_information10 => csr_rec.processing_priority /* 9358829 */
4206 ,p_assignment_id => p_assignment_id);
4207 fnd_file.put_line(fnd_file.log,' 2 csr_result_rec.result_value '||csr_result_rec.result_value);
4208
4209 END IF;
4210 END LOOP;
4211 END IF;
4212
4213
4214 ELSIF rec_group_by.ORG_INFORMATION3 = 'I'
4215 THEN
4216 -- Case for UNIT,PRICE,AMOUNT
4217 -- Segment 7,8,9 is allowed
4218 -- Segemnt 6 is not allowed here, as it doesn't makes sense.
4219 -- segment 7 = > Input ID UNIT
4220 -- segment 8 = > Input ID UNIT PRICE
4221 -- segment 9 = > Input ID Amount
4222 IF rec_group_by.ORG_INFORMATION7 IS NOT NULL AND
4223 rec_group_by.ORG_INFORMATION8 IS NOT NULL AND
4224 rec_group_by.ORG_INFORMATION9 IS NOT NULL
4225 THEN
4226 -- All three are selected, we can group by three in single query
4227
4228 IF rec_group_by.ORG_INFORMATION10 = 'Y'
4229 THEN
4230 FOR csr_result_rec IN csr_all_sum_result_values(rec_group_by.ORG_INFORMATION7,rec_group_by.ORG_INFORMATION9,rec_group_by.ORG_INFORMATION8,csr_rec.element_type_id,p_assignment_action_id)
4231 LOOP
4232
4233 IF csr_result_rec.AMOUNT is not null THEN
4234 fnd_file.put_line(fnd_file.log,' 3 csr_result_rec.AMOUNT '||csr_result_rec.AMOUNT);
4235 pay_action_information_api.create_action_information (
4236 p_action_information_id => l_action_info_id
4237 ,p_action_context_id => p_archive_assact_id
4238 ,p_action_context_type => 'AAP'
4239 ,p_object_version_number => l_ovn
4240 ,p_effective_date => p_effective_date
4241 ,p_source_id => NULL
4242 ,p_source_text => NULL
4243 ,p_action_information_category => 'EMEA ELEMENT INFO'
4244 ,p_action_information1 => csr_rec.element_type_id
4245 ,p_action_information2 => csr_rec.input_value_id
4246 ,p_action_information3 => 'E'
4247 ,p_action_information4 => fnd_number.number_to_canonical(csr_result_rec.AMOUNT) /* l_formatted_value 9316928 not removing canonical because select query does a canonical to number */
4248 ,p_action_information8 => fnd_number.number_to_canonical(csr_result_rec.UNIT) /* l_formatted_value 9316928 not removing canonical because select query does a canonical to number */
4249 ,p_action_information9 => 'Earning Element unit per price:'||csr_result_rec.UNIT_PRICE
4250 ,p_action_information10 => csr_rec.processing_priority /* 9358829 */
4251 ,p_assignment_id => p_assignment_id);
4252 fnd_file.put_line(fnd_file.log,' 3 csr_result_rec.AMOUNT '||csr_result_rec.AMOUNT);
4253
4254 END IF;
4255 END LOOP;
4256 ELSE
4257 FOR csr_result_rec IN csr_all_result_values(rec_group_by.ORG_INFORMATION7,rec_group_by.ORG_INFORMATION9,rec_group_by.ORG_INFORMATION8,csr_rec.element_type_id,p_assignment_action_id )
4258 LOOP
4259
4260 IF csr_result_rec.AMOUNT is not null THEN
4261 fnd_file.put_line(fnd_file.log,' 4 csr_result_rec.AMOUNT '||csr_result_rec.AMOUNT);
4262 pay_action_information_api.create_action_information (
4263 p_action_information_id => l_action_info_id
4264 ,p_action_context_id => p_archive_assact_id
4265 ,p_action_context_type => 'AAP'
4266 ,p_object_version_number => l_ovn
4267 ,p_effective_date => p_effective_date
4268 ,p_source_id => NULL
4269 ,p_source_text => NULL
4270 ,p_action_information_category => 'EMEA ELEMENT INFO'
4271 ,p_action_information1 => csr_rec.element_type_id
4272 ,p_action_information2 => csr_rec.input_value_id
4273 ,p_action_information3 => 'E'
4274 ,p_action_information4 => csr_result_rec.AMOUNT /* l_formatted_value 9316928 */
4275 ,p_action_information8 => csr_result_rec.UNIT
4276 ,p_action_information9 => 'Earning Element unit per price:'||csr_result_rec.UNIT_PRICE
4277 ,p_action_information10 => csr_rec.processing_priority /* 9358829 */
4278 ,p_assignment_id => p_assignment_id);
4279 fnd_file.put_line(fnd_file.log,' 4 csr_result_rec.AMOUNT '||csr_result_rec.AMOUNT);
4280
4281 END IF; -- end of csr_result_rec.AMOUNT is not null
4282 END LOOP;
4283
4284 END IF; -- end of rec_group_by.ORG_INFORMATION10 = 'Y'
4285 ELSE -- Three inputs are not selected.
4286 -- have to get the each input value id and find value for each
4287 -- and archive it if the amount is not null
4288
4289 -- Case for UNIT,PRICE,AMOUNT
4290 -- Segment 7,8,9 is allowed
4291 -- Segemnt 6 is not allowed here, as it doesn't makes sense.
4292 -- segment 7 = > Input ID UNIT
4293 -- segment 8 = > Input ID UNIT PRICE
4294 -- segment 9 = > Input ID Amount
4295
4296 IF rec_group_by.ORG_INFORMATION9 IS NOT NULL
4297 THEN
4298 -- amount should not be null
4299 -- find the amount value and element entry id of this element
4300 -- for this element entry find the the other selected columns like UNIT , UNIT PRICE if selected
4301 --
4302 FOR csr_result_rec IN csr_result_value(rec_group_by.ORG_INFORMATION9 ,csr_rec.element_type_id ,p_assignment_action_id)
4303 LOOP
4304
4305 -- we have EE id
4306 l_amount := fnd_number.canonical_to_number( csr_result_rec.result_value); /* 9316928 */
4307
4308 IF rec_group_by.ORG_INFORMATION8 IS NOT NULL
4309 THEN
4310 OPEN csr_result_value_EE(rec_group_by.ORG_INFORMATION8 ,csr_rec.element_type_id ,p_assignment_action_id,csr_result_rec.element_entry_id );
4311 FETCH csr_result_value_EE
4312 INTO l_unit_price;
4313 CLOSE csr_result_value_EE;
4314 ELSE
4315 l_unit_price :=NULL;
4316 END IF; -- End if of segment 8 , unit price
4317
4318 IF rec_group_by.ORG_INFORMATION7 IS NOT NULL
4319 THEN
4320 OPEN csr_result_value_EE(rec_group_by.ORG_INFORMATION7 ,csr_rec.element_type_id ,p_assignment_action_id,csr_result_rec.element_entry_id ); -- Bug#9289267 fix
4321 FETCH csr_result_value_EE
4322 INTO l_unit;
4323 CLOSE csr_result_value_EE;
4324 ELSE
4325 l_unit :=NULL;
4326 END IF; -- End if of segment 7 , unit
4327
4328 -- Resume again
4329
4330 IF csr_result_rec.result_value is not null THEN
4331 fnd_file.put_line(fnd_file.log,' 5 l_amount '||l_amount);
4332 pay_action_information_api.create_action_information (
4333 p_action_information_id => l_action_info_id
4334 ,p_action_context_id => p_archive_assact_id
4335 ,p_action_context_type => 'AAP'
4336 ,p_object_version_number => l_ovn
4337 ,p_effective_date => p_effective_date
4338 ,p_source_id => NULL
4339 ,p_source_text => NULL
4340 ,p_action_information_category => 'EMEA ELEMENT INFO'
4341 ,p_action_information1 => csr_rec.element_type_id
4342 ,p_action_information2 => csr_rec.input_value_id
4343 ,p_action_information3 => 'E'
4344 ,p_action_information4 => fnd_number.number_to_canonical(l_amount) /* 9316928 canonical to num is applied on l_amount */
4345 ,p_action_information8 => l_unit
4346 ,p_action_information9 => 'Earning Element unit per price:'||l_unit_price
4347 ,p_action_information10 => csr_rec.processing_priority /* 9358829 */
4348 ,p_assignment_id => p_assignment_id);
4349 fnd_file.put_line(fnd_file.log,' 5 l_amount '||l_amount);
4350
4351 END IF; -- end of csr_result_rec.result_value is not null
4352 END LOOP;
4353 END IF; -- end of rec_group_by.ORG_INFORMATION9 IS NOT NULL
4354
4355 END IF;
4356
4357 END IF; -- End of rec_group_by.ORG_INFORMATION3 = 'I'
4358
4359 EXCEPTION WHEN OTHERS THEN
4360 g_err_num := SQLCODE;
4361 fnd_file.put_line(fnd_file.log,'ORA_ERR: Earnings' || g_err_num || 'In ARCHIVE_MAIN_ELEMENTS');
4362 IF g_debug THEN
4363 hr_utility.set_location('ORA_ERR: ' || g_err_num || 'In ARCHIVE_MAIN_ELEMENTS',330);
4364 END IF;
4365
4366
4367 END;
4368 END LOOP;
4369
4370
4371
4372 -- Archiving Deduction Elements
4373
4374 FOR csr_rec IN csr_ded_element_info LOOP
4375
4376 -- fnd_file.put_line(fnd_file.log,'Deduction Elements REP_NAME:'||csr_rec.rep_name);
4377
4378 l_result_value := null;
4379 rec_group_by := NULL;
4380
4381 BEGIN
4382
4383 -- Conditions below are added to flush the Record Set After a Successful Query
4384 -- so that it starts afresh for the next element
4385 rec_group_by.ORG_INFORMATION6:= NULL;
4386 rec_group_by.ORG_INFORMATION3:= NULL;
4387
4388 OPEN csr_group_by(csr_rec.element_type_id );
4389 FETCH csr_group_by INTO rec_group_by;
4390 CLOSE csr_group_by;
4391 -- The se_soe contains
4392 -- segment 3 = > I or O
4393 -- segment 6 = > Y or N
4394 -- segment 7 = > Input ID UNIT
4395 -- segment 8 = > Input ID UNIT PRICE
4396 -- segment 9 = > Input ID Amount
4397 --fnd_file.put_line(fnd_file.log,'Vetri1 : ' || 'In ARCHIVE_MAIN_ELEMENTS'||rec_group_by.ORG_INFORMATION3);
4398
4399
4400
4401 IF ( (rec_group_by.ORG_INFORMATION3 = 'O') or (rec_group_by.ORG_INFORMATION3 IS NULL) )
4402 THEN
4403 -- Case for Group by or NOT
4404 -- Segemnt 6 is allowed here, as it makes sense.
4405
4406 IF ( rec_group_by.ORG_INFORMATION6 = 'N' or (rec_group_by.ORG_INFORMATION3 IS NULL) )
4407 THEN
4408 -- This csae iis for individual representation of each element.
4409 -- unit and unit price should be absent.
4410
4411 FOR csr_result_rec IN csr_result_value(csr_rec.input_value_id ,csr_rec.element_type_id ,p_assignment_action_id)
4412 LOOP
4413
4414 l_result_value := csr_result_rec.result_value ;
4415
4416 /* Added for Pension changes -start */
4417
4418 IF( csr_rec.rep_name IN ('Pension','Employer Pension','Retro Pension','Retro Employer Pension')) THEN
4419
4420 -- fnd_file.put_line(fnd_file.log,'Deduction Elements P1:'||csr_rec.rep_name);
4421
4422 -- rec_result_val := NULL;
4423 --OPEN csr_result_value(csr_rec.input_value_id ,csr_rec.element_type_id ,p_assignment_action_id);
4424 --FETCH csr_result_value INTO rec_result_val;
4425 --CLOSE csr_result_value;
4426 --l_rr_id_pen := rec_result_val.run_result_id ;
4427
4428 l_rr_id_pen := csr_result_rec.run_result_id;
4429
4430 OPEN csr_get_pen_iv_id(p_effective_date ,csr_rec.element_type_id);
4431 FETCH csr_get_pen_iv_id INTO l_iv_id_pen;
4432 CLOSE csr_get_pen_iv_id;
4433
4434
4435
4436 OPEN csr_get_pp_name(p_effective_date , l_rr_id_pen);
4437 FETCH csr_get_pp_name INTO rec_get_pp_name;
4438 CLOSE csr_get_pp_name;
4439
4440 -- fnd_file.put_line(fnd_file.log,'rec_get_pp_name.name :'||rec_get_pp_name.name );
4441
4442
4443 OPEN csr_get_ded_pen_dtl(p_effective_date ,csr_rec.element_type_id ,csr_rec.input_value_id);
4444 FETCH csr_get_ded_pen_dtl INTO l_action_info_id_pen;
4445 CLOSE csr_get_ded_pen_dtl;
4446
4447
4448 /* Commented to avoid updating the element definition
4449 l_new_rep_name := csr_rec.rep_name|| ' ( ' ||rec_get_pp_name.name|| ' ) ' ;
4450
4451 pay_action_information_api.update_action_information(
4452 p_action_information_id => l_action_info_id_pen
4453 ,p_object_version_number => l_ovn_pen
4454 ,p_action_information4 => l_new_rep_name); */
4455
4456 -- fnd_file.put_line(fnd_file.log,'Deduction Elements :'||rec_get_pp_name.name);
4457
4458 l_ele_pen_context:='PP';
4459 l_ele_pen_context_desc:= rec_get_pp_name.name||':';
4460
4461 Else
4462 l_ele_pen_context:='D';
4463 l_ele_pen_context_desc:='Deduction Element:';
4464
4465
4466 END IF;
4467
4468 /* Added for Pension changes -end */
4469
4470
4471 IF l_result_value is not null THEN
4472 -- fnd_file.put_line(fnd_file.log,'D 1 : l_result_value '|| l_result_value );
4473
4474 pay_action_information_api.create_action_information (
4475 p_action_information_id => l_action_info_id
4476 ,p_action_context_id => p_archive_assact_id
4477 ,p_action_context_type => 'AAP'
4478 ,p_object_version_number => l_ovn
4479 ,p_effective_date => p_effective_date
4480 ,p_source_id => NULL
4481 ,p_source_text => NULL
4482 ,p_action_information_category => 'EMEA ELEMENT INFO'
4483 ,p_action_information1 => csr_rec.element_type_id
4484 ,p_action_information2 => csr_rec.input_value_id
4485 ,p_action_information3 => l_ele_pen_context -- Added for Pension Changes
4486 ,p_action_information4 => l_result_value /* l_formatted_value kandhan 12 */
4487 ,p_action_information9 => l_ele_pen_context_desc -- Added for Pension Changes
4488 ,p_assignment_id => p_assignment_id
4489 ,p_action_information8 => ''
4490 ,p_action_information10 => csr_rec.processing_priority /* 9358829 */
4491 );
4492 -- fnd_file.put_line(fnd_file.log,'D 1 : l_result_value '|| l_result_value );
4493 END IF;
4494 END LOOP;
4495 ----------------------------------------------------------------------------------------------------------------------------------------------
4496 ELSIF rec_group_by.ORG_INFORMATION6 = 'Y'
4497 THEN
4498 -- This case is for Grouping by pay value of each element.
4499 -- unit and unit price should be present
4500 FOR csr_result_rec IN csr_sum_of_result_values(csr_rec.input_value_id ,csr_rec.element_type_id ,p_assignment_action_id )
4501
4502 LOOP
4503
4504 l_result_value := csr_result_rec.result_value ;
4505
4506 IF( csr_rec.rep_name IN ('Pension','Employer Pension','Retro Pension','Retro Employer Pension')) THEN
4507
4508 rec_result_val := NULL;
4509 OPEN csr_result_value(csr_rec.input_value_id ,csr_rec.element_type_id ,p_assignment_action_id);
4510 FETCH csr_result_value INTO rec_result_val;
4511 CLOSE csr_result_value;
4512 l_rr_id_pen := rec_result_val.run_result_id ;
4513
4514 OPEN csr_get_pen_iv_id(p_effective_date ,csr_rec.element_type_id);
4515 FETCH csr_get_pen_iv_id INTO l_iv_id_pen;
4516 CLOSE csr_get_pen_iv_id;
4517
4518 OPEN csr_get_pp_name(p_effective_date ,l_rr_id_pen);
4519 FETCH csr_get_pp_name INTO rec_get_pp_name;
4520 CLOSE csr_get_pp_name;
4521
4522 OPEN csr_get_ded_pen_dtl(p_effective_date ,csr_rec.element_type_id ,csr_rec.input_value_id);
4523 FETCH csr_get_ded_pen_dtl INTO l_action_info_id_pen;
4524 CLOSE csr_get_ded_pen_dtl;
4525
4526
4527 l_ele_pen_context:='PP';
4528 l_ele_pen_context_desc:= rec_get_pp_name.name;
4529
4530 Else
4531 l_ele_pen_context:='D';
4532 l_ele_pen_context_desc:='Deduction Element unit per price';
4533
4534 END IF;
4535
4536 /* Added for Pension changes -end */
4537
4538
4539
4540
4541
4542 IF l_result_value is not null THEN
4543 -- fnd_file.put_line(fnd_file.log,'D 2 : l_result_value '|| l_result_value );
4544 pay_action_information_api.create_action_information (
4545 p_action_information_id => l_action_info_id
4546 ,p_action_context_id => p_archive_assact_id
4547 ,p_action_context_type => 'AAP'
4548 ,p_object_version_number => l_ovn
4549 ,p_effective_date => p_effective_date
4550 ,p_source_id => NULL
4551 ,p_source_text => NULL
4552 ,p_action_information_category => 'EMEA ELEMENT INFO'
4553 ,p_action_information1 => csr_rec.element_type_id
4554 ,p_action_information2 => csr_rec.input_value_id
4555 ,p_action_information3 => l_ele_pen_context -- Added for Pension Changes
4556 ,p_action_information4 => fnd_number.number_to_canonical(l_result_value) /* l_formatted_value 9316928 select query applies canonical to number */
4557 ,p_action_information9 => l_ele_pen_context_desc||':'||csr_result_rec.UNIT_PRICE -- Added for Pension Changes
4558 ,p_assignment_id => p_assignment_id
4559 ,p_action_information8 => csr_result_rec.record_count
4560 ,p_action_information10 => csr_rec.processing_priority /* 9358829 */
4561 );
4562 -- fnd_file.put_line(fnd_file.log,'D 2 : l_result_value '|| l_result_value );
4563 END IF;
4564 END LOOP;
4565 END IF;
4566 ELSIF rec_group_by.ORG_INFORMATION3 = 'I'
4567 THEN
4568 -- Case for UNIT,PRICE,AMOUNT
4569 -- Segment 7,8,9 is allowed
4570 -- Segemnt 6 is not allowed here, as it doesn't makes sense.
4571 -- segment 7 = > Input ID UNIT
4572 -- segment 8 = > Input ID UNIT PRICE
4573 -- segment 9 = > Input ID Amount
4574 IF rec_group_by.ORG_INFORMATION7 IS NOT NULL AND
4575 rec_group_by.ORG_INFORMATION8 IS NOT NULL AND
4576 rec_group_by.ORG_INFORMATION9 IS NOT NULL
4577 THEN
4578 -- All three are selected, we can group by three in single query
4579 IF rec_group_by.ORG_INFORMATION10 = 'Y'
4580 THEN
4581 FOR csr_result_rec IN csr_all_sum_result_values(rec_group_by.ORG_INFORMATION7,rec_group_by.ORG_INFORMATION9,rec_group_by.ORG_INFORMATION8,csr_rec.element_type_id,p_assignment_action_id )
4582 LOOP
4583
4584
4585 IF( csr_rec.rep_name IN ('Pension','Employer Pension','Retro Pension','Retro Employer Pension')) THEN
4586
4587 rec_result_val := NULL;
4588 OPEN csr_result_value(csr_rec.input_value_id ,csr_rec.element_type_id ,p_assignment_action_id);
4589 FETCH csr_result_value INTO rec_result_val;
4590 CLOSE csr_result_value;
4591 l_rr_id_pen := rec_result_val.run_result_id ;
4592
4593
4594 OPEN csr_get_pen_iv_id(p_effective_date ,csr_rec.element_type_id);
4595 FETCH csr_get_pen_iv_id INTO l_iv_id_pen;
4596 CLOSE csr_get_pen_iv_id;
4597
4598 OPEN csr_get_pp_name(p_effective_date , l_rr_id_pen);
4599 FETCH csr_get_pp_name INTO rec_get_pp_name;
4600 CLOSE csr_get_pp_name;
4601
4602 OPEN csr_get_ded_pen_dtl(p_effective_date ,csr_rec.element_type_id ,csr_rec.input_value_id);
4603 FETCH csr_get_ded_pen_dtl INTO l_action_info_id_pen;
4604 CLOSE csr_get_ded_pen_dtl;
4605
4606
4607 l_ele_pen_context:='PP';
4608 l_ele_pen_context_desc := rec_get_pp_name.name;
4609
4610 Else
4611 l_ele_pen_context:='D';
4612 l_ele_pen_context_desc := 'Deduction Element unit per price';
4613
4614 END IF;
4615
4616 /*End*/
4617
4618
4619
4620 IF csr_result_rec.AMOUNT is not null THEN
4621 -- fnd_file.put_line(fnd_file.log,'D 3 : csr_result_rec.AMOUNT '|| csr_result_rec.AMOUNT );
4622 pay_action_information_api.create_action_information (
4623 p_action_information_id => l_action_info_id
4624 ,p_action_context_id => p_archive_assact_id
4625 ,p_action_context_type => 'AAP'
4626 ,p_object_version_number => l_ovn
4627 ,p_effective_date => p_effective_date
4628 ,p_source_id => NULL
4629 ,p_source_text => NULL
4630 ,p_action_information_category => 'EMEA ELEMENT INFO'
4631 ,p_action_information1 => csr_rec.element_type_id
4632 ,p_action_information2 => csr_rec.input_value_id
4633 ,p_action_information3 => l_ele_pen_context
4634 ,p_action_information4 => fnd_number.number_to_canonical(csr_result_rec.AMOUNT) /* l_formatted_value select query applies canonical to number */
4635 ,p_action_information8 => csr_result_rec.UNIT
4636 ,p_action_information9 => l_ele_pen_context_desc||':'||csr_result_rec.UNIT_PRICE
4637 ,p_action_information10 => csr_rec.processing_priority /* 9358829 */
4638 ,p_assignment_id => p_assignment_id);
4639 -- fnd_file.put_line(fnd_file.log,'D 3 : csr_result_rec.AMOUNT '|| csr_result_rec.AMOUNT );
4640
4641
4642 END IF;
4643 END LOOP;
4644 ELSE
4645 FOR csr_result_rec IN csr_all_result_values(rec_group_by.ORG_INFORMATION7,rec_group_by.ORG_INFORMATION9,rec_group_by.ORG_INFORMATION8,csr_rec.element_type_id,p_assignment_action_id)
4646 LOOP
4647
4648 /* Added for Pension changes -start */
4649
4650 IF( csr_rec.rep_name IN ('Pension','Employer Pension','Retro Pension','Retro Employer Pension')) THEN
4651
4652 rec_result_val := NULL;
4653 OPEN csr_result_value(csr_rec.input_value_id ,csr_rec.element_type_id ,p_assignment_action_id);
4654 FETCH csr_result_value INTO rec_result_val;
4655 CLOSE csr_result_value;
4656 l_rr_id_pen := rec_result_val.run_result_id ;
4657
4658 OPEN csr_get_pen_iv_id(p_effective_date ,csr_rec.element_type_id);
4659 FETCH csr_get_pen_iv_id INTO l_iv_id_pen;
4660 CLOSE csr_get_pen_iv_id;
4661
4662 OPEN csr_get_pp_name(p_effective_date , l_rr_id_pen);
4663 FETCH csr_get_pp_name INTO rec_get_pp_name;
4664 CLOSE csr_get_pp_name;
4665
4666 OPEN csr_get_ded_pen_dtl(p_effective_date ,csr_rec.element_type_id ,csr_rec.input_value_id);
4667 FETCH csr_get_ded_pen_dtl INTO l_action_info_id_pen;
4668 CLOSE csr_get_ded_pen_dtl;
4669
4670 l_ele_pen_context:='PP';
4671 l_ele_pen_context_desc := rec_get_pp_name.name;
4672
4673 Else
4674 l_ele_pen_context:='D';
4675 l_ele_pen_context_desc := 'Deduction Element unit per price';
4676
4677 END IF;
4678
4679 /* End */
4680
4681 IF csr_result_rec.AMOUNT is not null THEN
4682 -- fnd_file.put_line(fnd_file.log,'D 4 : csr_result_rec.AMOUNT '|| csr_result_rec.AMOUNT );
4683 pay_action_information_api.create_action_information (
4684 p_action_information_id => l_action_info_id
4685 ,p_action_context_id => p_archive_assact_id
4686 ,p_action_context_type => 'AAP'
4687 ,p_object_version_number => l_ovn
4688 ,p_effective_date => p_effective_date
4689 ,p_source_id => NULL
4690 ,p_source_text => NULL
4691 ,p_action_information_category => 'EMEA ELEMENT INFO'
4692 ,p_action_information1 => csr_rec.element_type_id
4693 ,p_action_information2 => csr_rec.input_value_id
4694 ,p_action_information3 => l_ele_pen_context
4695 ,p_action_information4 => csr_result_rec.AMOUNT /* l_formatted_value 9316928 */
4696 ,p_action_information8 => csr_result_rec.UNIT
4697 ,p_action_information9 => l_ele_pen_context_desc||':'||csr_result_rec.UNIT_PRICE
4698 ,p_action_information10 => csr_rec.processing_priority /* 9358829 */
4699 ,p_assignment_id => p_assignment_id);
4700 -- fnd_file.put_line(fnd_file.log,'D 4 : csr_result_rec.AMOUNT '|| csr_result_rec.AMOUNT );
4701
4702 END IF;
4703
4704
4705 END LOOP;
4706
4707 END IF;-- end of rec_group_by.ORG_INFORMATION10 = 'Y'
4708 ELSE -- Three inputs are not selected.
4709 -- have to get the each input value id and find value for each
4710 -- and archive it if the amount is not null
4711 -- Case for UNIT,PRICE,AMOUNT
4712 -- Segment 7,8,9 is allowed
4713 -- Segemnt 6 is not allowed here, as it doesn't makes sense.
4714 -- segment 7 = > Input ID UNIT
4715 -- segment 8 = > Input ID UNIT PRICE
4716 -- segment 9 = > Input ID Amount
4717 IF rec_group_by.ORG_INFORMATION9 IS NOT NULL
4718 THEN
4719 -- amount should not be null
4720 -- find the amount value and element entry id of this element
4721 -- for this element entry find the the other selected columns like UNIT , UNIT PRICE if selected
4722 --
4723 FOR csr_result_rec IN csr_result_value(rec_group_by.ORG_INFORMATION9 ,csr_rec.element_type_id ,p_assignment_action_id)
4724 LOOP
4725
4726 -- we have EE id
4727 l_amount := fnd_number.canonical_to_number(csr_result_rec.result_value);
4728 IF rec_group_by.ORG_INFORMATION8 IS NOT NULL
4729 THEN
4730 OPEN csr_result_value_EE(rec_group_by.ORG_INFORMATION8 ,csr_rec.element_type_id ,p_assignment_action_id,csr_result_rec.element_entry_id );
4731 FETCH csr_result_value_EE
4732 INTO l_unit_price;
4733 CLOSE csr_result_value_EE;
4734 ELSE
4735 l_unit_price :=NULL;
4736 END IF; -- End if of segment 8 , unit price
4737
4738 IF rec_group_by.ORG_INFORMATION7 IS NOT NULL
4739 THEN
4740 OPEN csr_result_value_EE(rec_group_by.ORG_INFORMATION7 ,csr_rec.element_type_id ,p_assignment_action_id,csr_result_rec.element_entry_id ); -- Bug#9289267 fix
4741 FETCH csr_result_value_EE
4742 INTO l_unit;
4743 CLOSE csr_result_value_EE;
4744 ELSE
4745 l_unit :=NULL;
4746 END IF; -- End if of segment 7 , unit
4747
4748 -- Resume again
4749
4750
4751 /* Added for Pension changes -start */
4752
4753
4754 IF( csr_rec.rep_name IN ('Pension','Employer Pension','Retro Pension','Retro Employer Pension')) THEN
4755
4756 --rec_result_val := NULL;
4757 --OPEN csr_result_value(csr_rec.input_value_id ,csr_rec.element_type_id ,p_assignment_action_id);
4758 --FETCH csr_result_value INTO rec_result_val;
4759 --CLOSE csr_result_value;
4760 l_rr_id_pen := csr_result_rec.run_result_id ;
4761
4762 OPEN csr_get_pen_iv_id(p_effective_date ,csr_rec.element_type_id);
4763 FETCH csr_get_pen_iv_id INTO l_iv_id_pen;
4764 CLOSE csr_get_pen_iv_id;
4765
4766 OPEN csr_get_pp_name(p_effective_date , l_rr_id_pen);
4767 FETCH csr_get_pp_name INTO rec_get_pp_name;
4768 CLOSE csr_get_pp_name;
4769
4770 OPEN csr_get_ded_pen_dtl(p_effective_date ,csr_rec.element_type_id ,csr_rec.input_value_id);
4771 FETCH csr_get_ded_pen_dtl INTO l_action_info_id_pen;
4772 CLOSE csr_get_ded_pen_dtl;
4773
4774 l_ele_pen_context:='PP';
4775 l_ele_pen_context_desc := rec_get_pp_name.name;
4776
4777 ELSE
4778 l_ele_pen_context:='D';
4779 l_ele_pen_context_desc := 'Deduction Element unit per price';
4780
4781 END IF;
4782
4783 /* End */
4784
4785
4786 IF csr_result_rec.result_value is not null THEN
4787 -- fnd_file.put_line(fnd_file.log,'D 5 : csr_result_rec.result_value '|| csr_result_rec.result_value );
4788 pay_action_information_api.create_action_information (
4789 p_action_information_id => l_action_info_id
4790 ,p_action_context_id => p_archive_assact_id
4791 ,p_action_context_type => 'AAP'
4792 ,p_object_version_number => l_ovn
4793 ,p_effective_date => p_effective_date
4794 ,p_source_id => NULL
4795 ,p_source_text => NULL
4796 ,p_action_information_category => 'EMEA ELEMENT INFO'
4797 ,p_action_information1 => csr_rec.element_type_id
4798 ,p_action_information2 => csr_rec.input_value_id
4799 ,p_action_information3 => l_ele_pen_context
4800 ,p_action_information4 => fnd_number.number_to_canonical(l_amount) /* l_formatted_value applied canonical to num to l_amount earlier */
4801 ,p_action_information8 => l_unit
4802 ,p_action_information9 => l_ele_pen_context_desc||':'||l_unit_price
4803 ,p_action_information10 => csr_rec.processing_priority /* 9358829 */
4804 ,p_assignment_id => p_assignment_id);
4805 -- fnd_file.put_line(fnd_file.log,'D 5 : csr_result_rec.result_value '|| csr_result_rec.result_value );
4806
4807 END IF; -- end of csr_result_rec.result_value is not null
4808 END LOOP;
4809 END IF; -- end of rec_group_by.ORG_INFORMATION9 IS NOT NULL
4810 END IF;
4811 END IF;
4812
4813 /*******************************************End Changes*********************************************************************************/
4814
4815 EXCEPTION WHEN OTHERS THEN
4816 g_err_num := SQLCODE;
4817 fnd_file.put_line(fnd_file.log,'ORA_ERR: Deductions' || g_err_num || 'In ARCHIVE_MAIN_ELEMENTS');
4818
4819 IF g_debug THEN
4820 hr_utility.set_location('ORA_ERR: ' || g_err_num || 'In ARCHIVE_MAIN_ELEMENTS',330);
4821 END IF;
4822 END;
4823 END LOOP;
4824
4825
4826 IF g_debug THEN
4827 hr_utility.set_location(' Leaving Procedure ARCHIVE_MAIN_ELEMENTS',340);
4828 END IF;
4829
4830 END ARCHIVE_MAIN_ELEMENTS;
4831
4832 ------------------------------------ End of package ----------------------------------------------------------------
4833
4834 END PAY_DK_ARCHIVE;