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