[Home] [Help]
PACKAGE BODY: APPS.PAY_DK_ARCHIVE_EIN
Source
1 PACKAGE BODY PAY_DK_ARCHIVE_EIN AS
2 /* $Header: pydkeina.pkb 120.1.12010000.2 2008/10/14 14:37:00 pvelugul ship $ */
3
4 g_debug BOOLEAN := hr_utility.debug_enabled;
5 g_package VARCHAR2(33) := ' PAY_DK_ARCHIVE_EIN.';
6 g_payroll_action_id NUMBER ;
7 g_le_assignment_action_id NUMBER ;
8 g_business_group_id NUMBER;
9 g_legal_employer_id NUMBER;
10 --Create globals
11 g_effective_date DATE;
12 g_payroll_id NUMBER;
13 g_payroll_period NUMBER;
14 g_test_submission VARCHAR2(1);
15 g_company_terminating VARCHAR2(1);
16 g_flag NUMBER:=0;
17
18
19 FUNCTION GET_DEFINED_BALANCE_VALUE
20 (p_assignment_id IN NUMBER
21 ,p_balance_name IN VARCHAR2
22 ,p_balance_dim IN VARCHAR2
23 ,p_virtual_date IN DATE) RETURN NUMBER IS
24
25 l_context1 PAY_DEFINED_BALANCES.DEFINED_BALANCE_ID%TYPE;
26 l_value NUMBER;
27
28
29 CURSOR get_dbal_id(p_balance_name VARCHAR2 , p_balance_dim VARCHAR2) IS
30 SELECT pdb.defined_balance_id
31 FROM pay_defined_balances pdb
32 ,pay_balance_types pbt
33 ,pay_balance_dimensions pbd
34 WHERE pbt.legislation_code='DK'
35 AND pbt.balance_name = p_balance_name
36 AND pbd.legislation_code = 'DK'
37 AND pbd.database_item_suffix = p_balance_dim
38 AND pdb.balance_type_id = pbt.balance_type_id
39 AND pdb.balance_dimension_id = pbd.balance_dimension_id;
40
41
42 BEGIN
43
44 OPEN get_dbal_id(p_balance_name, p_balance_dim);
45 FETCH get_dbal_id INTO l_context1;
46 CLOSE get_dbal_id;
47
48 l_value := pay_balance_pkg.get_value(l_context1,p_assignment_id,p_virtual_date);
49
50 RETURN l_value;
51
52 END GET_DEFINED_BALANCE_VALUE ;
53
54
55 /* GET PARAMETER */
56 FUNCTION GET_PARAMETER(
57 p_parameter_string IN VARCHAR2
58 ,p_token IN VARCHAR2
59 ,p_segment_number IN NUMBER default NULL ) RETURN VARCHAR2
60 IS
61 l_parameter pay_payroll_actions.legislative_parameters%TYPE:=NULL;
62 l_start_pos NUMBER;
63 l_delimiter VARCHAR2(1):=' ';
64 l_proc VARCHAR2(40):= g_package||' get parameter ';
65 BEGIN
66 --
67 IF g_debug THEN
68 hr_utility.set_location(' Entering Function GET_PARAMETER',10);
69 END IF;
70 l_start_pos := instr(' '||p_parameter_string,l_delimiter||p_token||'=');
71 --
72 IF l_start_pos = 0 THEN
73 l_delimiter := '|';
74 l_start_pos := instr(' '||p_parameter_string,l_delimiter||p_token||'=');
75 END IF;
76
77 IF l_start_pos <> 0 THEN
78 l_start_pos := l_start_pos + length(p_token||'=');
79 l_parameter := substr(p_parameter_string,
80 l_start_pos,
81 instr(p_parameter_string||' ',
82 l_delimiter,l_start_pos)
83 - l_start_pos);
84 IF p_segment_number IS NOT NULL THEN
85 l_parameter := ':'||l_parameter||':';
86 l_parameter := substr(l_parameter,
87 instr(l_parameter,':',1,p_segment_number)+1,
88 instr(l_parameter,':',1,p_segment_number+1) -1
89 - instr(l_parameter,':',1,p_segment_number));
90 END IF;
91 END IF;
92 --
93 IF g_debug THEN
94 hr_utility.set_location(' Leaving Function GET_PARAMETER',20);
95 END IF;
96
97 RETURN l_parameter;
98
99 END;
100
101 /* GET ALL PARAMETERS */
102 PROCEDURE GET_ALL_PARAMETERS(
103 p_payroll_action_id IN NUMBER
104 ,p_business_group_id OUT NOCOPY NUMBER
105 ,p_legal_employer_id OUT NOCOPY NUMBER
106 ,p_effective_date OUT NOCOPY DATE
107 ,p_payroll OUT NOCOPY NUMBER
108 ,p_payroll_period OUT NOCOPY NUMBER
109 ,p_test_submission OUT NOCOPY VARCHAR2
110 ,p_company_terminating OUT NOCOPY VARCHAR2
111 ) IS
112
113 CURSOR csr_parameter_info(p_payroll_action_id NUMBER) IS
114 SELECT PAY_DK_ARCHIVE_EIN.GET_PARAMETER(legislative_parameters,'LEGAL_EMPLOYER_ID')
115 ,PAY_DK_ARCHIVE_EIN.GET_PARAMETER(legislative_parameters,'PAYROLL')
116 ,PAY_DK_ARCHIVE_EIN.GET_PARAMETER(legislative_parameters,'PAYROLL_PERIOD')
117 ,PAY_DK_ARCHIVE_EIN.GET_PARAMETER(legislative_parameters,'TEST_SUBMISSION')
118 ,PAY_DK_ARCHIVE_EIN.GET_PARAMETER(legislative_parameters,'COMPANY_TERMINATING')
119 ,effective_date
120 ,business_group_id
121 FROM pay_payroll_actions
122 WHERE payroll_action_id = p_payroll_action_id;
123
124 l_proc VARCHAR2(240):= g_package||' GET_ALL_PARAMETERS ';
125 --
126 BEGIN
127
128 OPEN csr_parameter_info (p_payroll_action_id);
129
130 FETCH csr_parameter_info
131 INTO p_legal_employer_id
132 ,p_payroll
133 ,p_payroll_period
134 ,p_test_submission
135 ,p_company_terminating
136 ,p_effective_date
137 ,p_business_group_id;
138 CLOSE csr_parameter_info;
139 --
140 IF g_debug THEN
141 hr_utility.set_location(' Leaving Procedure GET_ALL_PARAMETERS',30);
142 END IF;
143
144 END GET_ALL_PARAMETERS;
145
146 /* RANGE CODE */
147 PROCEDURE RANGE_CODE
148 (p_payroll_action_id IN NUMBER
149 ,p_sql OUT NOCOPY VARCHAR2)
150 IS
151 l_action_info_id NUMBER;
152 l_ovn NUMBER;
153 l_count NUMBER := 0;
154 l_business_group_id NUMBER;
155 l_test_submission varchar2(1);
156 l_company_terminating varchar2(1);
157 l_emp_id hr_organization_units.organization_id%TYPE ;
158 l_le_name hr_organization_units.name%TYPE ;
159 l_business_id hr_organization_information.org_information1%TYPE ;
160 l_cvr_number hr_organization_information.org_information1%TYPE ;
161 l_sp_cvr_number hr_organization_information.org_information1%TYPE ;
162 l_org_type hr_organization_information.org_information1%TYPE ;
163 l_date VARCHAR2(100);
164 l_time VARCHAR2(10);
165 l_lb_num VARCHAR2(10);
166
167 /*Cursors */
168
169 /*Legal Employer Information*/
170 Cursor csr_Legal_Emp_Details ( csr_v_legal_emp_id hr_organization_information.ORGANIZATION_ID%TYPE)
171 IS
172 SELECT o1.name ,hoi2.ORG_INFORMATION1 , hoi2.ORG_INFORMATION2, hoi2.ORG_INFORMATION3, hoi2.ORG_INFORMATION4, hoi2.ORG_INFORMATION5, hoi2.ORG_INFORMATION6, hoi2.ORG_INFORMATION13
173 FROM hr_organization_units o1
174 , hr_organization_information hoi1
175 , hr_organization_information hoi2
176 WHERE o1.business_group_id =l_business_group_id
177 AND hoi1.organization_id = o1.organization_id
178 AND hoi1.organization_id = csr_v_legal_emp_id
179 AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
180 AND hoi1.org_information_context = 'CLASS'
181 AND o1.organization_id =hoi2.organization_id
182 AND hoi2.ORG_INFORMATION_CONTEXT='DK_LEGAL_ENTITY_DETAILS' ;
183
184 rg_Legal_Emp_Details csr_Legal_Emp_Details%rowtype;
185
186 -- Service Provider information.
187 cursor service_provider_details
188 is
189 select * from hr_organization_information
190 where org_information_context = 'DK_SERVICE_PROVIDER_DETAILS'
191 and organization_id in (
192 select organization_id from hr_organization_units
193 where business_group_id= l_business_group_id);
194
195 sp service_provider_details%rowtype;
196
197 /* Payroll Time period */
198 CURSOR csr_pay_periods(p_payroll_id NUMBER, p_payroll_period NUMBER)
199 IS
200 SELECT ptp.start_date, ptp.end_date, ptp.period_name,
201 default_dd_date,
202 decode(PERIOD_TYPE
203 ,'Calendar Month','1'
204 ,'Bi-Week' ,'2'
205 ,'Week' ,'3'
206 ,'Lunar Month' ,'4') PAYROLL_PERIOD
207 from per_time_periods ptp
208 WHERE payroll_id = p_payroll_id
209 AND time_period_id = p_payroll_period;
210
211 rg_csr_pay_periods csr_pay_periods%rowtype;
212
213 /* Payroll Name */
214 CURSOR csr_payroll_name(id pay_all_payrolls_f.payroll_id%type)
215 IS
216 select payroll_name from pay_payrolls_f
217 where payroll_id=id;
218
219 rg_csr_payroll_name csr_payroll_name%rowtype;
220
221 /* End of Cursors */
222 BEGIN
223
224 -- fnd_file.put_line(fnd_file.log,'Range Code 1');
225
226 IF g_debug THEN
227 hr_utility.set_location(' Entering Procedure RANGE_CODE',40);
228 END IF;
229
230 p_sql := 'SELECT DISTINCT person_id FROM per_people_f ppf,pay_payroll_actions ppa WHERE ppa.payroll_action_id = :payroll_action_id AND ppa.business_group_id = ppf.business_group_id ORDER BY ppf.person_id';
231
232 -- fnd_file.put_line(fnd_file.log,'Range Code 2');
233
234 g_legal_employer_id := NULL ;
235 g_effective_date := NULL ;
236 g_payroll_action_id := p_payroll_action_id ;
237 g_le_assignment_action_id := NULL ;
238 g_payroll_id:=NULL;
239 g_payroll_period:=NULL;
240 g_test_submission:=NULL;
241 g_company_terminating:=NULL;
242
243 -- fnd_file.put_line(fnd_file.log,'Range Code 3');
244
245 PAY_DK_ARCHIVE_EIN.GET_ALL_PARAMETERS(
246 p_payroll_action_id
247 ,l_business_group_id
248 ,g_legal_employer_id
249 ,g_effective_date
250 ,g_payroll_id
251 ,g_payroll_period
252 ,g_test_submission
253 ,g_company_terminating) ;
254
255
256 -- fnd_file.put_line(fnd_file.log,'Range Code 4');
257
258 SELECT count(*)
259 INTO l_count
260 FROM pay_action_information
261 WHERE action_information_category = 'EMEA REPORT DETAILS'
262 AND action_information1 = 'PYDKEINA'
263 AND action_context_id = p_payroll_action_id;
264
265 -- fnd_file.put_line(fnd_file.log,'Range Code 6');
266
267 IF l_count < 1 then
268
269 hr_utility.set_location('Entered Procedure GETDATA',10);
270
271
272 -- fnd_file.put_line(fnd_file.log,'g_legal_employer_id : '||g_legal_employer_id);
273 -- fnd_file.put_line(fnd_file.log,'l_business_group_id : '||l_business_group_id);
274 OPEN csr_Legal_Emp_Details(g_legal_employer_id);
275 FETCH csr_Legal_Emp_Details INTO rg_Legal_Emp_Details;
276
277 /* if(csr_Legal_Emp_Details%notfound) then
278 fnd_file.put_line(fnd_file.log,'No data found for the legal emp');
279 end if;*/
280
281 l_le_name := rg_Legal_Emp_Details.name ;
282 l_cvr_number := rg_Legal_Emp_Details.ORG_INFORMATION1 ;
283
284 -- fnd_file.put_line(fnd_file.log,'l_cvr_number : '||l_cvr_number);
285
286 CLOSE csr_Legal_Emp_Details;
287
288 /* Pick up the details belonging to Legal Employer
289
290 OPEN csr_Legal_Emp_Details(g_legal_employer_id);
291 FETCH csr_Legal_Emp_Details INTO rg_Legal_Emp_Details;
292 CLOSE csr_Legal_Emp_Details;
293
294 l_le_name := rg_Legal_Emp_Details.name ;
295 l_business_id := rg_Legal_Emp_Details.ORG_INFORMATION1 ; */
296
297 -- date and time
298 SELECT to_char(sysdate,'yyyymmdd') INTO l_date FROM dual;
299 SELECT to_char(sysdate,'hhmiss') INTO l_time FROM dual;
300
301 if(g_test_submission='Y') then
302 l_test_submission:='T';
303 else
304 l_test_submission:='P';
305 end if;
306
307 if(g_company_terminating='Y') then
308 l_company_terminating:='A';
309 else
310 l_company_terminating:=null;
311 end if;
312
313 --Employer Level Information
314 if(rg_Legal_Emp_Details.ORG_INFORMATION3 = 'Y') then -- if the legal emp has data supplier set to 'Y'
315 pay_action_information_api.create_action_information (
316 p_action_information_id => l_action_info_id
317 ,p_action_context_id => p_payroll_action_id
318 ,p_action_context_type => 'PA'
319 ,p_object_version_number => l_ovn
320 ,p_effective_date => g_effective_date
321 ,p_source_id => NULL
322 ,p_source_text => NULL
323 ,p_action_information_category => 'EMEA REPORT INFORMATION'
324 ,p_action_information1 => 'PYDKEINA'
325 ,p_action_information2 => p_payroll_action_id
326 ,p_action_information3 => '1000'
327 ,p_action_information4 => l_date
328 ,p_action_information5 => l_time
329 ,p_action_information6 => rg_Legal_Emp_Details.ORG_INFORMATION5 -- legal emp SE number
330 ,p_action_information7 => l_cvr_number -- legal emp CVR number
331 ,p_action_information8 => '01' -- for Legal emp
332 ,p_action_information9 => '0'
333 ,p_action_information10 => 'Oracle Payroll'
334 ,p_action_information11 => '1'
335 ,p_action_information12 => l_cvr_number -- main sender ID
336 ,p_action_information13 => '2.0'
337 ,p_action_information14 => l_test_submission
338 ,p_action_information15 => 'E'
339 ,p_action_information16 => null
340 ,p_action_information17 => null
341 ,p_action_information18 => null
342 ,p_action_information19 => null
343 ,p_action_information20 => null
344 ,p_action_information21 => null
345 ,p_action_information22 => null
346 ,p_action_information23 => null
347 ,p_action_information24 => null
348 ,p_action_information25 => null
349 ,p_action_information26 => null
350 ,p_action_information27 => null
351 ,p_action_information28 => null
352 ,p_action_information29 => null
353 ,p_action_information30 => null);
354
355 else
356
357 open service_provider_details;
358 fetch service_provider_details into sp;
359 l_sp_cvr_number := sp.org_information1;
360
361 -- check the value of e-income data supplier
362 if(sp.org_information3<>'Y') then
363 fnd_file.put_line(fnd_file.log,HR_DK_UTILITY.GET_MESSAGE('PAY','HR_377103_DK_EINCOME_STATUS'));
364 g_flag:=1;
365 else
366 pay_action_information_api.create_action_information (
367 p_action_information_id => l_action_info_id
368 ,p_action_context_id => p_payroll_action_id
369 ,p_action_context_type => 'PA'
370 ,p_object_version_number => l_ovn
371 ,p_effective_date => g_effective_date
372 ,p_source_id => NULL
373 ,p_source_text => NULL
374 ,p_action_information_category => 'EMEA REPORT INFORMATION'
375 ,p_action_information1 => 'PYDKEINA'
376 ,p_action_information2 => p_payroll_action_id
377 ,p_action_information3 => '1000'
378 ,p_action_information4 => l_date
379 ,p_action_information5 => l_time -- time
380 ,p_action_information6 => l_sp_cvr_number -- service provider CVR number
381 ,p_action_information7 => l_sp_cvr_number -- service provider CVR number
382 ,p_action_information8 => '02' -- 02 for Service provider
383 ,p_action_information9 => '0'
384 ,p_action_information10 => 'Oracle Payroll'
385 ,p_action_information11 => '1'
386 ,p_action_information12 => l_sp_cvr_number -- main sender ID (service provider CVR number)
387 ,p_action_information13 => '2.0'
388 ,p_action_information14 => l_test_submission
389 ,p_action_information15 => 'E'
390 ,p_action_information16 => null
391 ,p_action_information17 => null
392 ,p_action_information18 => null
393 ,p_action_information19 => null
394 ,p_action_information20 => null
395 ,p_action_information21 => null
396 ,p_action_information22 => null
397 ,p_action_information23 => null
398 ,p_action_information24 => null
399 ,p_action_information25 => null
400 ,p_action_information26 => null
401 ,p_action_information27 => null
402 ,p_action_information28 => null
403 ,p_action_information29 => null
404 ,p_action_information30 => null);
405 end if;
409 if(g_flag=0) then
406 close service_provider_details;
407 end if;
408
410 -- Record 2001
411 pay_action_information_api.create_action_information (
412 p_action_information_id => l_action_info_id
413 ,p_action_context_id => p_payroll_action_id
414 ,p_action_context_type => 'PA'
415 ,p_object_version_number => l_ovn
416 ,p_effective_date => g_effective_date
417 ,p_source_id => NULL
418 ,p_source_text => NULL
419 ,p_action_information_category => 'EMEA REPORT INFORMATION'
420 ,p_action_information1 => 'PYDKEINA'
421 ,p_action_information2 => p_payroll_action_id
422 ,p_action_information3 => '2001'
423 ,p_action_information4 => l_cvr_number -- legal emp CVR number
424 ,p_action_information5 => l_company_terminating -- is null if company termiating is 'N'
425 ,p_action_information6 => 'DKK'
426 ,p_action_information7 => null
427 ,p_action_information8 => null
428 ,p_action_information9 => null
429 ,p_action_information10 => null
430 ,p_action_information11 => null
431 ,p_action_information12 => null
432 ,p_action_information13 => null
433 ,p_action_information14 => null
434 ,p_action_information15 => null
435 ,p_action_information16 => null
436 ,p_action_information17 => null
437 ,p_action_information18 => null
438 ,p_action_information19 => null
439 ,p_action_information20 => null
440 ,p_action_information21 => null
441 ,p_action_information22 => null
442 ,p_action_information23 => null
443 ,p_action_information24 => null
444 ,p_action_information25 => null
445 ,p_action_information26 => null
446 ,p_action_information27 => null
447 ,p_action_information28 => null
448 ,p_action_information29 => null
449 ,p_action_information30 => null);
450
451
452 open csr_pay_periods(g_payroll_id, g_payroll_period);
453 fetch csr_pay_periods into rg_csr_pay_periods;
454 close csr_pay_periods;
455
456 if(g_company_terminating='N') then
457 -- Record 5000
458 pay_action_information_api.create_action_information (
459 p_action_information_id => l_action_info_id
460 ,p_action_context_id => p_payroll_action_id
461 ,p_action_context_type => 'PA'
462 ,p_object_version_number => l_ovn
463 ,p_effective_date => g_effective_date
464 ,p_source_id => NULL
465 ,p_source_text => NULL
466 ,p_action_information_category => 'EMEA REPORT INFORMATION'
467 ,p_action_information1 => 'PYDKEINA'
468 ,p_action_information2 => p_payroll_action_id
469 ,p_action_information3 => '5000'
470 ,p_action_information4 => to_char(rg_csr_pay_periods.start_date,'yyyymmdd')
471 ,p_action_information5 => to_char(rg_csr_pay_periods.end_date,'yyyymmdd')
472 ,p_action_information6 => to_char(rg_csr_pay_periods.default_dd_date,'yyyymmdd')
473 ,p_action_information7 => 'B'
474 ,p_action_information8 => '000' --Greenland code
475 ,p_action_information9 => '00' --Normal employee code
476 ,p_action_information10 => null
477 ,p_action_information11 => null
478 ,p_action_information12 => null
479 ,p_action_information13 => null
480 ,p_action_information14 => null
481 ,p_action_information15 => null
482 ,p_action_information16 => null
483 ,p_action_information17 => null
484 ,p_action_information18 => null
485 ,p_action_information19 => null
486 ,p_action_information20 => null
487 ,p_action_information21 => null
488 ,p_action_information22 => null
489 ,p_action_information23 => null
490 ,p_action_information24 => null
491 ,p_action_information25 => null
492 ,p_action_information26 => null
493 ,p_action_information27 => null
494 ,p_action_information28 => null
495 ,p_action_information29 => null
496 ,p_action_information30 => null);
497 end if;
498
499
500 open csr_payroll_name(g_payroll_id);
501 fetch csr_payroll_name into rg_csr_payroll_name;
502 close csr_payroll_name;
503
504 -- fnd_file.put_line(fnd_file.log,'Range Code 14');
505 --Report Level Information
506 pay_action_information_api.create_action_information (
507 p_action_information_id => l_action_info_id
508 ,p_action_context_id => p_payroll_action_id
509 ,p_action_context_type => 'PA'
510 ,p_object_version_number => l_ovn
511 ,p_effective_date => g_effective_date
512 ,p_source_id => NULL
513 ,p_source_text => NULL
514 ,p_action_information_category => 'EMEA REPORT DETAILS'
515 ,p_action_information1 => 'PYDKEINA'
516 ,p_action_information2 => l_le_name
520 ,p_action_information6 => HR_GENERAL.DECODE_LOOKUP('YES_NO',g_company_terminating) -- COMPANY TERMINATING
517 ,p_action_information3 => rg_csr_payroll_name.payroll_name -- PAYROLL NAME
518 ,p_action_information4 => rg_csr_pay_periods.period_name -- PAYROLL PERIOD NAME
519 ,p_action_information5 => HR_GENERAL.DECODE_LOOKUP('YES_NO',g_test_submission) -- TEST SUBMISSION
521 ,p_action_information7 => null
522 ,p_action_information8 => null
523 ,p_action_information9 => null
524 ,p_action_information10 => null
525 ,p_action_information11 => null
526 ,p_action_information12 => null
527 ,p_action_information13 => null
528 ,p_action_information14 => null
529 ,p_action_information15 => null
530 ,p_action_information16 => null
531 ,p_action_information17 => null
532 ,p_action_information18 => null
533 ,p_action_information19 => null
534 ,p_action_information20 => null
535 ,p_action_information21 => null
536 ,p_action_information22 => null
537 ,p_action_information23 => null
538 ,p_action_information24 => null
539 ,p_action_information25 => null
540 ,p_action_information26 => null
541 ,p_action_information27 => null
542 ,p_action_information28 => null
543 ,p_action_information29 => null
544 ,p_action_information30 => null );
545
546 -- fnd_file.put_line(fnd_file.log,'Range Code 15');
547 end if; -- end g_flag condition
548
549 END IF;
550
551 IF g_debug THEN
552 hr_utility.set_location(' Leaving Procedure RANGE_CODE',50);
553 END IF;
554
555 EXCEPTION
556 WHEN others THEN
557 IF g_debug THEN
558 hr_utility.set_location('error raised assignment_action_code ',5);
559 END if;
560 RAISE;
561 END RANGE_CODE;
562
563 /* ASSIGNMENT ACTION CODE */
564 PROCEDURE ASSIGNMENT_ACTION_CODE
565 (p_payroll_action_id IN NUMBER
566 ,p_start_person IN NUMBER
567 ,p_end_person IN NUMBER
568 ,p_chunk IN NUMBER )
569 IS
570
571 l_canonical_start_date DATE;
572 l_canonical_end_date DATE;
573 l_prepay_action_id NUMBER;
574 l_prev_person_id NUMBER;
575 l_prev_local_unit_id NUMBER;
576 l_actid NUMBER;
577
578
579 CURSOR csr_prepaid_assignments_le(p_payroll_action_id NUMBER,
580 p_start_person NUMBER,
581 p_end_person NUMBER,
582 p_legal_employer_id NUMBER,
583 p_payroll_id NUMBER,
584 p_payroll_period NUMBER,
585 l_canonical_start_date DATE,
586 l_canonical_end_date DATE)
587 IS
588 SELECT as1.person_id person_id,
589 act.assignment_id assignment_id,
590 act.assignment_action_id run_action_id,
591 act1.assignment_action_id prepaid_action_id
592 FROM pay_payroll_actions ppa
593 ,pay_payroll_actions appa
594 ,pay_payroll_actions appa2
595 ,pay_assignment_actions act
596 ,pay_assignment_actions act1
597 ,pay_action_interlocks pai
598 ,per_all_assignments_f as1
599 WHERE ppa.payroll_action_id = p_payroll_action_id
600 AND appa.effective_date BETWEEN l_canonical_start_date
601 AND l_canonical_end_date
602 AND as1.person_id BETWEEN p_start_person
603 AND p_end_person
604 AND appa.action_type IN ('R','Q')
605 -- Payroll Run or Quickpay Run
606 AND act.payroll_action_id = appa.payroll_action_id
607 --aapa table add time period check
608 AND appa.time_period_id = p_payroll_period
609 AND act.source_action_id IS NULL -- Master Action
610 AND as1.assignment_id = act.assignment_id
611 -- Add payroll id
612 AND as1.payroll_id = p_payroll_id
613 -- Commenting Code to Include Terminated Assignments
614 -- AND ppa.effective_date BETWEEN as1.effective_start_date
615 -- AND as1.effective_end_date
616 AND act.action_status = 'C' -- Completed
617 AND act.assignment_action_id = pai.locked_action_id
618 AND act1.assignment_action_id = pai.locking_action_id
619 AND act1.action_status = 'C' -- Completed
620 AND act1.payroll_action_id = appa2.payroll_action_id
621 AND appa2.action_type IN ('P','U')
622 AND appa2.effective_date BETWEEN l_canonical_start_date
623 AND l_canonical_end_date
624 -- Prepayments or Quickpay Prepayments
625 AND act.TAX_UNIT_ID = act1.TAX_UNIT_ID
626 AND act.TAX_UNIT_ID = p_legal_employer_id
627 ORDER BY as1.person_id , act.assignment_id;
628
629 cursor csr_pay_periods(p_payroll_id NUMBER, p_payroll_period NUMBER)
630 is
631 select start_date, end_date from per_time_periods
632 where payroll_id = p_payroll_id
633 and time_period_id = p_payroll_period;
634
635 pp csr_pay_periods%rowtype;
636
637 BEGIN
638 if(g_flag=0) then
639 -- fnd_file.put_line(fnd_file.log,'ASSIGNMENT_ACTION_CODE 1');
643 END IF;
640
641 IF g_debug THEN
642 hr_utility.set_location(' Entering Procedure ASSIGNMENT_ACTION_CODE',60);
644
645 PAY_DK_ARCHIVE_EIN.GET_ALL_PARAMETERS(
646 p_payroll_action_id
647 ,g_business_group_id
648 ,g_legal_employer_id
649 ,g_effective_date
650 ,g_payroll_id
651 ,g_payroll_period
652 ,g_test_submission
653 ,g_company_terminating) ;
654
655 -- fnd_file.put_line(fnd_file.log,'ASSIGNMENT_ACTION_CODE 2');
656
657 g_payroll_action_id :=p_payroll_action_id;
658
659 open csr_pay_periods(g_payroll_id, g_payroll_period);
660 fetch csr_pay_periods into pp;
661 l_canonical_start_date := pp.start_date;
662 l_canonical_end_date := pp.end_date;
663
664 l_prepay_action_id := 0;
665 l_prev_person_id := 0;
666
667 -- fnd_file.put_line(fnd_file.log,'ASSIGNMENT_ACTION_CODE 3');
668
669 FOR rec_prepaid_assignments IN csr_prepaid_assignments_le(p_payroll_action_id
670 ,p_start_person
671 ,p_end_person
672 ,g_legal_employer_id
673 ,g_payroll_id
674 ,g_payroll_period
675 ,l_canonical_start_date
676 ,l_canonical_end_date)
677 LOOP
678 IF l_prepay_action_id <> rec_prepaid_assignments.prepaid_action_id
679 AND l_prev_person_id <> rec_prepaid_assignments.person_id THEN
680
681 SELECT pay_assignment_actions_s.NEXTVAL
682 INTO l_actid
683 FROM dual;
684
685 -- Create the archive assignment action
686 hr_nonrun_asact.insact(l_actid
687 ,rec_prepaid_assignments.assignment_id
688 ,p_payroll_action_id
689 ,p_chunk
690 ,NULL);
691
692 -- fnd_file.put_line(fnd_file.log,'ASSIGNMENT_ACTION_CODE 7');
693
694 END IF;
695 l_prepay_action_id := rec_prepaid_assignments.prepaid_action_id;
696 l_prev_person_id := rec_prepaid_assignments.person_id;
697 END LOOP;
698
699 IF g_debug THEN
700 hr_utility.set_location(' Leaving Procedure ASSIGNMENT_ACTION_CODE',70);
701 END IF;
702 end if; -- end g_flag condition.
703
704 EXCEPTION
705 WHEN others THEN
706 IF g_debug THEN
707 hr_utility.set_location('error raised assignment_action_code ',5);
708 END if;
709 RAISE;
710 END ASSIGNMENT_ACTION_CODE;
711
712
713 /* INITIALIZATION CODE */
714 PROCEDURE INITIALIZATION_CODE(p_payroll_action_id IN NUMBER)
715 IS
716
717 BEGIN
718 IF g_debug THEN
719 hr_utility.set_location(' Entering Procedure INITIALIZATION_CODE',80);
720 END IF;
721 -- fnd_file.put_line(fnd_file.log,'INITIALIZATION_CODE 1');
722 IF g_debug THEN
723 hr_utility.set_location(' Leaving Procedure INITIALIZATION_CODE',90);
724 END IF;
725
726 EXCEPTION
727 WHEN others THEN
728 IF g_debug THEN
729 hr_utility.set_location('error raised initialization code ',5);
730 END if;
731 RAISE;
732 END INITIALIZATION_CODE;
733
734 /* ARCHIVE CODE */
735 PROCEDURE ARCHIVE_CODE(p_assignment_action_id IN NUMBER
736 ,p_effective_date IN DATE)
737 IS
738 /* Cursor to retrieve Person Details */
739 CURSOR csr_get_person_details(p_asg_act_id NUMBER , p_asg_effective_date DATE ) IS
740 SELECT pap.national_identifier cpr , pap.person_id , pac.assignment_id, pap.full_name,
741 pap.start_date, pap.business_group_id, assign.hourly_salaried_code HOURLY_SALARIED_CODE,
742 assign.assignment_number, assign.organization_id, assign.primary_flag
743 FROM
744 pay_assignment_actions pac,
745 per_all_assignments_f assign,
746 per_all_people_f pap
747 WHERE pac.assignment_action_id = p_asg_act_id
748 AND assign.assignment_id = pac.assignment_id
749 AND assign.person_id = pap.person_id
750 AND pap.per_information_category = 'DK'
751 AND p_asg_effective_date BETWEEN assign.effective_start_date
752 AND assign.effective_end_date
753 AND p_asg_effective_date BETWEEN pap.effective_start_date
754 AND pap.effective_end_date;
755
756 rg_csr_get_person_details csr_get_person_details%rowtype;
757
758 /* Getting the latest hire date */
759 CURSOR csr_latest_hire_date(pid per_all_people_f.person_id%type) IS
760 SELECT MAX(date_start) lhd FROM per_periods_of_service
761 WHERE person_id=pid;
762
763 rg_csr_latest_hire_date csr_latest_hire_date%rowtype;
764
765 /* Cursor to get SE Number, PUCODE at HR Org level */
766
767 CURSOR csr_get_hr_org_info(
768 bg_id hr_organization_units.business_group_id%type
769 ,hr_org_id hr_organization_information.organization_id%type) IS
770 SELECT o1.name ,hoi2.ORG_INFORMATION1 , hoi2.ORG_INFORMATION2, hoi2.ORG_INFORMATION3, hoi2.ORG_INFORMATION4,
771 hoi2.ORG_INFORMATION5, hoi2.ORG_INFORMATION6, hoi2.ORG_INFORMATION13
772 FROM hr_organization_units o1
773 , hr_organization_information hoi1
774 , hr_organization_information hoi2
775 WHERE o1.business_group_id =bg_id
776 AND hoi1.organization_id = o1.organization_id
777 AND hoi1.organization_id = hr_org_id
778 AND hoi1.org_information1 = 'HR_ORG'
779 AND hoi1.org_information_context = 'CLASS'
780 AND o1.organization_id =hoi2.organization_id
781 AND hoi2.ORG_INFORMATION_CONTEXT='DK_EMPLOYMENT_DEFAULTS' ;
782
783 rg_csr_get_hr_org_info csr_get_hr_org_info%rowtype;
784
785 /* Payroll Time period */
786 CURSOR csr_pay_periods(p_payroll_id NUMBER, p_payroll_period NUMBER)
787 IS
788 SELECT ptp.start_date, ptp.end_date,
789 default_dd_date,
790 decode(PERIOD_TYPE
791 ,'Calendar Month','1'
792 ,'Bi-Week' ,'2'
793 ,'Week' ,'3'
794 ,'Lunar Month' ,'4') PAYROLL_PERIOD
795 from per_time_periods ptp
796 WHERE payroll_id = p_payroll_id
797 AND time_period_id = p_payroll_period;
798
799 rg_csr_pay_periods csr_pay_periods%rowtype;
800
801 /* Cursor to get the details of the legal employer */
802 Cursor csr_Legal_Emp_Details ( csr_v_legal_emp_id hr_organization_information.ORGANIZATION_ID%TYPE)
803 IS
804 SELECT o1.name ,hoi2.ORG_INFORMATION1 , hoi2.ORG_INFORMATION2, hoi2.ORG_INFORMATION3, hoi2.ORG_INFORMATION4, hoi2.ORG_INFORMATION5, hoi2.ORG_INFORMATION6, hoi2.ORG_INFORMATION13
805 FROM hr_organization_units o1
806 , hr_organization_information hoi1
807 , hr_organization_information hoi2
808 WHERE o1.business_group_id =g_business_group_id
809 AND hoi1.organization_id = o1.organization_id
810 AND hoi1.organization_id = csr_v_legal_emp_id
811 AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
812 AND hoi1.org_information_context = 'CLASS'
813 AND o1.organization_id =hoi2.organization_id
814 AND hoi2.ORG_INFORMATION_CONTEXT='DK_LEGAL_ENTITY_DETAILS' ;
815
816 rg_Legal_Emp_Details csr_Legal_Emp_Details%rowtype;
817
818 /* Cursor to retrieve Element - Employee ATP*/
819 CURSOR csr_get_atp_table_value(p_assignment_id NUMBER , p_start_date DATE , p_end_date DATE ) IS
820 SELECT eev1.screen_entry_value screen_entry_value
821 FROM per_all_assignments_f asg1
822 ,per_all_people_f per
823 ,pay_element_links_f el
824 ,pay_element_types_f et
825 ,pay_input_values_f iv1
826 ,pay_element_entries_f ee
827 ,pay_element_entry_values_f eev1
828 WHERE asg1.assignment_id = p_assignment_id
829 AND per.person_id = asg1.person_id
830 AND et.element_name = 'Employee ATP'
831 AND et.legislation_code = 'DK'
832 AND iv1.element_type_id = et.element_type_id
833 AND iv1.name = 'ATP Table'
834 AND el.business_group_id = per.business_group_id
835 AND el.element_type_id = et.element_type_id
836 AND ee.element_link_id = el.element_link_id
837 AND ee.assignment_id = asg1.assignment_id
838 AND eev1.element_entry_id = ee.element_entry_id
839 AND eev1.input_value_id = iv1.input_value_id
840 AND asg1.effective_end_date >= p_start_date
841 AND asg1.effective_start_date <= p_end_date
842 AND per.effective_end_date >= p_start_date
843 AND per.effective_start_date <= p_end_date
844 AND ee.effective_end_date >= p_start_date
845 AND ee.effective_start_date <= p_end_date
846 AND ((eev1.effective_start_date <= p_start_date
847 AND eev1.effective_end_date >= p_start_date )
848 OR (eev1.effective_start_date BETWEEN p_start_date AND p_end_date
849 AND eev1.effective_end_date >= p_end_date ));
850
851 rg_csr_get_atp_table_value csr_get_atp_table_value%rowtype;
852
853 /* Cursor to retrieve Element - Tax Card*/
854 CURSOR csr_get_tax_card_details(p_assignment_id NUMBER , p_start_date DATE , p_end_date DATE ) IS
855 SELECT ee.effective_start_date, eev1.screen_entry_value
856 FROM per_all_assignments_f asg1
857 ,per_all_people_f per
858 ,pay_element_links_f el
859 ,pay_element_types_f et
860 ,pay_input_values_f iv1
861 ,pay_element_entries_f ee
862 ,pay_element_entry_values_f eev1
863 WHERE asg1.assignment_id = p_assignment_id
864 AND per.person_id = asg1.person_id
865 AND et.element_name = 'Tax Card'
866 AND et.legislation_code = 'DK'
867 AND iv1.element_type_id = et.element_type_id
868 AND iv1.name = 'Tax Card Type'
869 AND el.business_group_id = per.business_group_id
870 AND el.element_type_id = et.element_type_id
871 AND ee.element_link_id = el.element_link_id
872 AND ee.assignment_id = asg1.assignment_id
873 AND eev1.element_entry_id = ee.element_entry_id
874 AND eev1.input_value_id = iv1.input_value_id
875 AND asg1.effective_end_date >= p_start_date
876 AND asg1.effective_start_date <= p_end_date
877 AND per.effective_end_date >= p_start_date
878 AND per.effective_start_date <= p_end_date
879 AND ee.effective_end_date >= p_start_date
880 AND ee.effective_start_date <= p_end_date
881 AND ((eev1.effective_start_date <= p_start_date
882 AND eev1.effective_end_date >= p_start_date )
883 OR (eev1.effective_start_date BETWEEN p_start_date AND p_end_date
884 AND eev1.effective_end_date >= p_end_date ));
885
886 rg_csr_get_tax_card_details csr_get_tax_card_details%rowtype;
887
888 /* Cursor to know the terminator in payroll period */
892 ,p_end_date DATE
889 CURSOR csr_asg_terminator
890 (p_asg_act_id NUMBER
891 ,p_start_date DATE
893 ,p_business_group_id NUMBER) IS
894 SELECT MAX( EFFECTIVE_END_DATE) EFFECTIVE_END_DATE
895 FROM per_all_assignments_f paa
896 ,pay_assignment_actions pac
897 WHERE pac.assignment_action_id = p_asg_act_id
898 AND paa.assignment_id = pac.assignment_id
899 AND paa.EFFECTIVE_START_DATE <= p_end_date
900 AND paa.EFFECTIVE_END_DATE > = p_start_date
901 AND assignment_status_type_id IN
902 (select assignment_status_type_id
903 from per_assignment_status_types
904 where per_system_status = 'ACTIVE_ASSIGN'
905 and active_flag = 'Y'
906 and (( legislation_code is null
907 and business_group_id is null)
908 OR (BUSINESS_GROUP_ID = p_business_group_id)));
909
910 rg_csr_asg_terminator csr_asg_terminator%rowtype;
911
912 CURSOR csr_Get_Defined_Balance_Id(csr_v_Balance_Name FF_DATABASE_ITEMS.USER_NAME%TYPE) IS
913 SELECT ue.creator_id
914 FROM ff_user_entities ue, ff_database_items di
915 WHERE di.user_name = csr_v_Balance_Name
916 AND ue.user_entity_id = di.user_entity_id
917 AND ue.legislation_code = 'DK'
918 AND ue.business_group_id is NULL
919 AND ue.creator_type = 'B';
920 lr_Get_Defined_Balance_Id csr_Get_Defined_Balance_Id%rowtype;
921
922 CURSOR csr_asg_action_id(assg_id NUMBER, pid NUMBER, tid NUMBER, le_id NUMBER) IS
923 select pac.ASSIGNMENT_ACTION_ID id
924 from pay_assignment_actions pac, pay_payroll_actions ppa
925 where ppa.action_type IN ('R','Q')
926 and pac.payroll_action_id = ppa.payroll_action_id
927 and pac.assignment_id= assg_id
928 and ppa.payroll_id=pid
929 and ppa.time_period_id=tid
930 and pac.tax_unit_id=le_id;
931 rg_csr_asg_action_id csr_asg_action_id%rowtype;
932
933
934 l_assignment_action_id NUMBER;
935 l_payroll_action_id NUMBER;
936 l_action_context_id NUMBER;
937 l_flag NUMBER := 0;
938 l_action_info_id NUMBER;
939 l_ovn NUMBER;
940 l_tax_card_type VARCHAR2(5);
941 l_payment_type VARCHAR2(5);
942 l_source_text VARCHAR2(10);
943 l_source_text2 VARCHAR2(10);
944 l_org_type VARCHAR2(5);
945 l_country_code varchar2(50);
946 l_age_category varchar2(1);
947 l_hourly_salaried varchar2(1):=null;
948 l_start_date DATE;
949 l_end_date DATE;
950 l_assignment NUMBER:=null;
951 l_primary VARCHAR2(2):=null;
952 l_bal_date DATE;
953 l_assignment_id NUMBER:=0;
954
955 l_sp_bonus NUMBER(12,2);
956 l_free_phone NUMBER(12,2);
957 l_mileage NUMBER(12,2);
958 l_total_atp NUMBER(12,2);
959 l_employer_atp NUMBER(12,2);
960 l_board_lodge NUMBER(12,2);
961 l_car NUMBER(12,2);
962 l_total_amb NUMBER(12,2);
963 l_hol_amb NUMBER(12,2);
964 l_hol_amb_rep NUMBER(12,2);
965 l_emp_amb NUMBER(12,2);
966 l_tax NUMBER(12,2);
967 l_emp_tax NUMBER(12,2);
968 l_holiday_tax NUMBER(12,2);
969 l_holiday_tax_pay NUMBER(12,2);
970 l_a_income NUMBER(12,2);
971 l_monthly_holiday_pay NUMBER(12,2);
972 l_hourly_holiday_pay NUMBER(12,2);
973 l_gross_income NUMBER(12,2);
974 l_emp_atp NUMBER(12,2);
975 l_amb_pay NUMBER(12,2);
976 l_amb NUMBER(12,2);
977 l_cvr_number hr_organization_information.org_information1%TYPE ;
978 l_cpr_number VARCHAR(15);
979 l_pu_code hr_organization_information.ORG_INFORMATION6%type;
980 l_total_atp_hours NUMBER;
981 l_atp NUMBER;
982 L_6005_SIGN VARCHAR2(1);
983 l_bg_id NUMBER;
984 l_org_id NUMBER;
985 l_hd VARCHAR2(100);
986
987 PROCEDURE rec_6001(
988 pid pay_action_information.action_information1%type
989 ,aid pay_action_information.action_information1%type
990 ,code pay_action_information.action_information1%type
991 ,amt pay_action_information.action_information1%type
992 ,sgn pay_action_information.action_information1%type
993 )
994 IS
995 l_sgn pay_action_information.action_information1%type:=NULL;
996 amt1 pay_action_information.action_information1%type:=NULL;
997 BEGIN
998
999 if(sgn='-1') then
1000 l_sgn:='-';
1001 else
1002 l_sgn:='+';
1003 end if;
1004
1005 amt1:=lpad(amt,16,'0');
1006 --Record - 6001
1007 pay_action_information_api.create_action_information (
1008 p_action_information_id => l_action_info_id
1009 ,p_action_context_id => p_assignment_action_id
1010 ,p_action_context_type => 'AAP'
1011 ,p_object_version_number => l_ovn
1012 ,p_effective_date => g_effective_date
1013 ,p_source_id => NULL
1014 ,p_source_text => NULL
1015 ,p_action_information_category => 'EMEA REPORT INFORMATION'
1016 ,p_action_information1 => 'PYDKEINA'
1017 ,p_action_information2 => l_payroll_action_id
1018 ,p_action_information3 => '6001'
1019 ,p_action_information4 => pid --rg_csr_get_person_details.PERSON_ID
1020 ,p_action_information5 => aid --rg_csr_get_person_details.assignment_ID
1021 ,p_action_information6 => code -- code according to the element entry reported
1025 ,p_action_information10 => null
1022 ,p_action_information7 => amt1 -- element value
1023 ,p_action_information8 => l_sgn -- (+/-)
1024 ,p_action_information9 => null
1026 ,p_action_information11 => null
1027 ,p_action_information12 => null
1028 ,p_action_information13 => null
1029 ,p_action_information14 => null
1030 ,p_action_information15 => null
1031 ,p_action_information16 => null
1032 ,p_action_information17 => null
1033 ,p_action_information18 => null
1034 ,p_action_information19 => null
1035 ,p_action_information20 => null
1036 ,p_action_information21 => null
1037 ,p_action_information22 => null
1038 ,p_action_information23 => null
1039 ,p_action_information24 => null
1040 ,p_action_information25 => null
1041 ,p_action_information26 => null
1042 ,p_action_information27 => null
1043 ,p_action_information28 => null
1044 ,p_action_information29 => null
1045 ,p_action_information30 => null);
1046 END;
1047
1048 BEGIN
1049 -- fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 1');
1050 /*Initializing all balance variables*/
1051 l_sp_bonus :=0;
1052 l_free_phone :=0;
1053 l_mileage :=0;
1054 l_total_atp :=0;
1055 l_employer_atp :=0;
1056 l_board_lodge :=0;
1057 l_car :=0;
1058 l_total_amb :=0;
1059 l_hol_amb :=0;
1060 l_emp_amb :=0;
1061 l_tax :=0;
1062 l_emp_tax :=0;
1063 l_holiday_tax :=0;
1064 l_a_income :=0;
1065 l_monthly_holiday_pay :=0;
1066 l_hourly_holiday_pay :=0;
1067 l_gross_income :=0;
1068 l_emp_atp :=0;
1069 l_amb_pay :=0;
1070 l_amb :=0;
1071 l_bg_id :=0;
1072 l_org_id :=0;
1073
1074 BEGIN
1075
1076 SELECT payroll_action_id
1077 INTO l_payroll_action_id
1078 FROM pay_assignment_actions
1079 WHERE assignment_action_id=p_assignment_action_id;
1080 END;
1081
1082 IF g_debug THEN
1083 hr_utility.set_location(' Entering Procedure ARCHIVE_CODE',380);
1084 END IF;
1085
1086 /* Fetching report parameters */
1087 PAY_DK_ARCHIVE_EIN.GET_ALL_PARAMETERS(
1088 l_payroll_action_id
1089 ,g_business_group_id
1090 ,g_legal_employer_id
1091 ,g_effective_date
1092 ,g_payroll_id
1093 ,g_payroll_period
1094 ,g_test_submission
1095 ,g_company_terminating) ;
1096
1097 if(g_flag=0) then
1098 if(g_company_terminating='N') then
1099
1100 -- fnd_file.put_line(fnd_file.log,'Fetched report parameters');
1101
1102 OPEN csr_get_person_details(p_assignment_action_id, p_effective_date);
1103 FETCH csr_get_person_details into rg_csr_get_person_details;
1104 CLOSE csr_get_person_details;
1105 l_assignment_id:=rg_csr_get_person_details.assignment_id;
1106 l_bg_id:=rg_csr_get_person_details.business_group_id;
1107 l_org_id:=rg_csr_get_person_details.organization_id;
1108
1109 -- fnd_file.put_line(fnd_file.log,'Fetched person details');
1110
1111 OPEN csr_pay_periods(g_payroll_id,g_payroll_period);
1112 FETCH csr_pay_periods into rg_csr_pay_periods;
1113 l_bal_date:= rg_csr_pay_periods.end_date;
1114 CLOSE csr_pay_periods;
1115
1116 l_start_date:=rg_csr_pay_periods.start_date;
1117 l_end_date:=rg_csr_pay_periods.end_date;
1118
1119 -- fnd_file.put_line(fnd_file.log,'Fetched payroll period details');
1120
1121 OPEN csr_get_atp_table_value(l_assignment_id, l_start_date, l_end_date);
1122 FETCH csr_get_atp_table_value into rg_csr_get_atp_table_value;
1123 CLOSE csr_get_atp_table_value;
1124
1125 -- fnd_file.put_line(fnd_file.log,'Fetched atp table value');
1126
1127 -- fnd_file.put_line(fnd_file.log,'bg id:'||l_bg_id);
1128 -- fnd_file.put_line(fnd_file.log,'org id:'||l_org_id);
1129 OPEN csr_get_hr_org_info(l_bg_id,l_org_id);
1130 FETCH csr_get_hr_org_info into rg_csr_get_hr_org_info;
1131 CLOSE csr_get_hr_org_info;
1132
1133
1134 -- fnd_file.put_line(fnd_file.log,'Fetched hr org info');
1135
1136 OPEN csr_Legal_Emp_Details(g_legal_employer_id);
1137 FETCH csr_Legal_Emp_Details INTO rg_Legal_Emp_Details;
1138 CLOSE csr_Legal_Emp_Details;
1139
1140 -- fnd_file.put_line(fnd_file.log,'Fetched legal emp details');
1141
1142 OPEN csr_get_tax_card_details(l_assignment_id, l_start_date, l_end_date);
1143 FETCH csr_get_tax_card_details INTO rg_csr_get_tax_card_details;
1144 CLOSE csr_get_tax_card_details;
1145
1146 if(rg_csr_get_tax_card_details.screen_entry_value='H') then
1147 l_tax_card_type:='1';
1148 else
1149 l_tax_card_type:='2';
1150 end if;
1151
1152
1153 /* Code for getting the hire date from PER_PEOPLE_V*/
1154 /* begin
1155
1156 select to_char(HIRE_DATE) into l_hd from PER_PEOPLE_V where NATIONAL_IDENTIFIER=rg_csr_get_person_details.cpr ;
1157 fnd_file.put_line(fnd_file.log,'hire date from per_people_v : '||l_hd);
1158
1159 exception
1160 when others then
1161 null;
1162 end; */
1163
1164 /* OPEN csr_latest_hire_date(rg_csr_get_person_details.person_id);
1165 FETCH csr_latest_hire_date into rg_csr_latest_hire_date;
1169 if((rg_csr_latest_hire_date.lhd between l_start_date and l_end_date) and to_char(l_end_date,'mm')<'06' and to_char(l_end_date,'yyyy')<='2008') then
1166 CLOSE csr_latest_hire_date;
1167
1168 l_cpr_number:= rg_csr_get_person_details.cpr;
1170 -- fnd_file.put_line(fnd_file.log,'Inserting starters');
1171 --Record - 2101
1172 pay_action_information_api.create_action_information (
1173 p_action_information_id => l_action_info_id
1174 ,p_action_context_id => p_assignment_action_id
1175 ,p_action_context_type => 'AAP'
1176 ,p_object_version_number => l_ovn
1177 ,p_effective_date => g_effective_date
1178 ,p_source_id => NULL
1179 ,p_source_text => NULL
1180 ,p_action_information_category => 'EMEA REPORT INFORMATION'
1181 ,p_action_information1 => 'PYDKEINA'
1182 ,p_action_information2 => l_payroll_action_id
1183 ,p_action_information3 => '2101'
1184 ,p_action_information4 => rg_csr_get_person_details.person_id
1185 ,p_action_information5 => rg_csr_get_person_details.assignment_id
1186 ,p_action_information6 => substr(l_cpr_number,1,6)||substr(l_cpr_number,8,4)
1187 ,p_action_information7 => to_char(rg_csr_latest_hire_date.lhd,'yyyymmdd') --latest hire date
1188 ,p_action_information8 => null -- no end date
1189 ,p_action_information9 => l_tax_card_type -- tax card type
1190 ,p_action_information10 => to_char(rg_csr_get_tax_card_details.effective_start_date,'yyyymmdd') -- effective start date
1191 ,p_action_information11 => null
1192 ,p_action_information12 => null
1193 ,p_action_information13 => null
1194 ,p_action_information14 => null
1195 ,p_action_information15 => null
1196 ,p_action_information16 => null
1197 ,p_action_information17 => null
1198 ,p_action_information18 => null
1199 ,p_action_information19 => null
1200 ,p_action_information20 => null
1201 ,p_action_information21 => null
1202 ,p_action_information22 => null
1203 ,p_action_information23 => null
1204 ,p_action_information24 => null
1205 ,p_action_information25 => null
1206 ,p_action_information26 => null
1207 ,p_action_information27 => null
1208 ,p_action_information28 => null
1209 ,p_action_information29 => null
1210 ,p_action_information30 => null);
1211 end if; */
1212
1213 -- TERMINATOR RECORD
1214 OPEN csr_asg_terminator(p_assignment_action_id, l_start_date, l_end_date, g_business_group_id);
1215 FETCH csr_asg_terminator INTO rg_csr_asg_terminator;
1216
1217 if(rg_csr_asg_terminator.effective_end_date <= l_end_date) then
1218 -- fnd_file.put_line(fnd_file.log,'Inserting terminators');
1219 --Record - 2101
1220 pay_action_information_api.create_action_information (
1221 p_action_information_id => l_action_info_id
1222 ,p_action_context_id => p_assignment_action_id
1223 ,p_action_context_type => 'AAP'
1224 ,p_object_version_number => l_ovn
1225 ,p_effective_date => g_effective_date
1226 ,p_source_id => NULL
1227 ,p_source_text => NULL
1228 ,p_action_information_category => 'EMEA REPORT INFORMATION'
1229 ,p_action_information1 => 'PYDKEINA'
1230 ,p_action_information2 => l_payroll_action_id
1231 ,p_action_information3 => '2101'
1232 ,p_action_information4 => rg_csr_get_person_details.person_id
1233 ,p_action_information5 => rg_csr_get_person_details.assignment_id
1234 ,p_action_information6 => substr(l_cpr_number,1,6)||substr(l_cpr_number,8,4)
1235 ,p_action_information7 => to_char(rg_csr_latest_hire_date.lhd,'yyyymmdd') --latest hire date
1236 ,p_action_information8 => to_char(rg_csr_asg_terminator.EFFECTIVE_END_DATE,'yyyymmdd') -- end date
1237 ,p_action_information9 => l_tax_card_type -- tax card type
1238 ,p_action_information10 => to_char(rg_csr_get_tax_card_details.effective_start_date,'yyyymmdd') -- effective start date
1239 ,p_action_information11 => null
1240 ,p_action_information12 => null
1241 ,p_action_information13 => null
1242 ,p_action_information14 => null
1243 ,p_action_information15 => null
1244 ,p_action_information16 => null
1245 ,p_action_information17 => null
1246 ,p_action_information18 => null
1247 ,p_action_information19 => null
1248 ,p_action_information20 => null
1249 ,p_action_information21 => null
1250 ,p_action_information22 => null
1251 ,p_action_information23 => null
1252 ,p_action_information24 => null
1253 ,p_action_information25 => null
1254 ,p_action_information26 => null
1255 ,p_action_information27 => null
1256 ,p_action_information28 => null
1257 ,p_action_information29 => null
1258 ,p_action_information30 => null);
1259 end if;
1260
1261 CLOSE csr_asg_terminator;
1262
1263 -- fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 2');
1264
1265 l_pu_code :=rg_csr_get_hr_org_info.ORG_INFORMATION6; -- HR-ORG Production Unit Code
1266 if(l_pu_code IS NULL) then
1267 l_pu_code:=rg_Legal_Emp_Details.ORG_INFORMATION6;
1271
1268 end if;
1269
1270 -- fnd_file.put_line(fnd_file.log,'PU code:'||l_pu_code);
1272 l_cvr_number:= rg_Legal_Emp_Details.ORG_INFORMATION1;
1273
1274 -- fnd_file.put_line(fnd_file.log,'Person name : '||rg_csr_get_person_details.full_name);
1275
1276 --Record - 6000
1277 pay_action_information_api.create_action_information (
1278 p_action_information_id => l_action_info_id
1279 ,p_action_context_id => p_assignment_action_id
1280 ,p_action_context_type => 'AAP'
1281 ,p_object_version_number => l_ovn
1282 ,p_effective_date => g_effective_date
1283 ,p_source_id => NULL
1284 ,p_source_text => NULL
1285 ,p_action_information_category => 'EMEA REPORT INFORMATION'
1286 ,p_action_information1 => 'PYDKEINA'
1287 ,p_action_information2 => l_payroll_action_id
1288 ,p_action_information3 => '6000'
1289 ,p_action_information4 => rg_csr_get_person_details.person_id
1290 ,p_action_information5 => rg_csr_get_person_details.assignment_id
1291 ,p_action_information6 => l_cvr_number --cvr number
1292 ,p_action_information7 => substr(l_cpr_number,1,6)||substr(l_cpr_number,8,4)
1293 ,p_action_information8 => rg_csr_get_person_details.assignment_number -- assignment number
1294 ,p_action_information9 => '0000'
1295 ,p_action_information10 => l_pu_code --DK production unit code
1296 ,p_action_information11 => null
1297 ,p_action_information12 => null
1298 ,p_action_information13 => null
1299 ,p_action_information14 => null
1300 ,p_action_information15 => null
1301 ,p_action_information16 => null
1302 ,p_action_information17 => null
1303 ,p_action_information18 => null
1304 ,p_action_information19 => null
1305 ,p_action_information20 => null
1306 ,p_action_information21 => null
1307 ,p_action_information22 => null
1308 ,p_action_information23 => null
1309 ,p_action_information24 => null
1310 ,p_action_information25 => null
1311 ,p_action_information26 => null
1312 ,p_action_information27 => null
1313 ,p_action_information28 => null
1314 ,p_action_information29 => null
1315 ,p_action_information30 => null);
1316
1317 /* fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 4');
1318
1319 fnd_file.put_line(fnd_file.log,'p_assignment_action_id:'||p_assignment_action_id);
1320 fnd_file.put_line(fnd_file.log,'l_bal_date:'||l_bal_date); */
1321 -- Record 6001
1322 l_amb_pay := GET_DEFINED_BALANCE_VALUE(l_assignment_id, 'AMBable Pay','_ASG_PTD' ,l_bal_date);
1323 -- fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 4.5');
1324 l_emp_atp := GET_DEFINED_BALANCE_VALUE(l_assignment_id, 'Employee ATP Deductions','_ASG_PTD' ,l_bal_date);
1325 l_gross_income := l_amb_pay + l_emp_atp;
1326
1327 if(l_gross_income<>0) then
1328 REC_6001(rg_csr_get_person_details.PERSON_ID, rg_csr_get_person_details.assignment_ID, '0200',abs(l_gross_income)*1000000,SIGN(l_gross_income));
1329 end if;
1330
1331 -- fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 5');
1332 l_hourly_holiday_pay := GET_DEFINED_BALANCE_VALUE(l_assignment_id, 'Holiday Accrual Pay','_ASG_PTD' ,l_bal_date);
1333
1334 l_monthly_holiday_pay := GET_DEFINED_BALANCE_VALUE(l_assignment_id, 'Holiday Allowance Paid','_ASG_PTD' ,l_bal_date);
1335
1336 l_hourly_salaried :=rg_csr_get_person_details.HOURLY_SALARIED_CODE;
1337 if(l_hourly_salaried IS null) then
1338 if (rg_csr_pay_periods.PAYROLL_PERIOD=1) then
1339 l_hourly_salaried:='S';
1340 else
1341 l_hourly_salaried := 'H';
1342 end if;
1343 end if;
1344
1345 if(l_hourly_salaried='S' and l_monthly_holiday_pay<>0) then
1346 REC_6001(rg_csr_get_person_details.PERSON_ID, rg_csr_get_person_details.assignment_ID, '0201',abs(l_monthly_holiday_pay)*1000000,SIGN(l_monthly_holiday_pay));
1347 elsif(l_hourly_salaried='H' and l_hourly_holiday_pay<>0) then
1348 REC_6001(rg_csr_get_person_details.PERSON_ID, rg_csr_get_person_details.assignment_ID, '0201',abs(l_hourly_holiday_pay)*1000000,SIGN(l_hourly_holiday_pay));
1349 end if;
1350
1351 l_a_income := GET_DEFINED_BALANCE_VALUE(l_assignment_id, 'AMBable Pay','_ASG_PTD' ,l_bal_date);
1352
1353 if(l_a_income<>0) then
1354 REC_6001(rg_csr_get_person_details.PERSON_ID, rg_csr_get_person_details.assignment_ID, '0013',abs(l_a_income)*1000000,SIGN(l_a_income));
1355 end if;
1356
1357 -- fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 6');
1358
1359 OPEN csr_asg_action_id(rg_csr_get_person_details.assignment_id,g_payroll_id,g_payroll_period,g_legal_employer_id);
1360 FETCH csr_asg_action_id into rg_csr_asg_action_id;
1361 CLOSE csr_asg_action_id;
1362
1363 OPEN csr_Get_Defined_Balance_Id('HOURLY_HOLIDAY_TAX_PAYMENTS');
1364 FETCH csr_Get_Defined_Balance_Id INTO lr_Get_Defined_Balance_Id;
1365 CLOSE csr_Get_Defined_Balance_Id;
1366
1367 l_emp_tax := GET_DEFINED_BALANCE_VALUE(l_assignment_id, 'Employee Tax','_ASG_PTD' ,l_bal_date);
1368 -- l_holiday_tax := GET_DEFINED_BALANCE_VALUE(l_assignment_id, 'Holiday Tax','_ASG_PTD' ,l_bal_date);
1369 l_holiday_tax_pay := pay_balance_pkg.get_value(lr_Get_Defined_Balance_Id.creator_id, rg_csr_asg_action_id.id);
1373 if(l_tax<>0) then
1370 -- fnd_file.put_line(fnd_file.log,'Hourly Holiday Tax_Payments : '||l_holiday_tax_pay);
1371 l_tax := FLOOR(l_emp_tax)+FLOOR(l_holiday_tax_pay);
1372
1374 REC_6001(rg_csr_get_person_details.PERSON_ID, rg_csr_get_person_details.assignment_ID, '0015',abs(l_tax)*1000000,SIGN(l_tax));
1375 end if;
1376
1377 OPEN csr_Get_Defined_Balance_Id('HOLIDAY_AMB_REPORTING_ASG_RUN');
1378 FETCH csr_Get_Defined_Balance_Id INTO lr_Get_Defined_Balance_Id;
1379 CLOSE csr_Get_Defined_Balance_Id;
1380
1381 l_emp_amb := GET_DEFINED_BALANCE_VALUE(l_assignment_id, 'Employee AMB Deduction','_ASG_PTD' ,l_bal_date);
1382 -- l_hol_amb := GET_DEFINED_BALANCE_VALUE(l_assignment_id, 'Holiday AMB','_ASG_PTD' ,l_bal_date);
1383 l_hol_amb_rep := pay_balance_pkg.get_value(lr_Get_Defined_Balance_Id.creator_id, rg_csr_asg_action_id.id);
1384 -- fnd_file.put_line(fnd_file.log,'Holiday AMB Reporting (ASG RUN) : '||l_hol_amb_rep);
1385 l_total_amb := FLOOR(l_emp_amb)+FLOOR(l_hol_amb_rep);
1386
1387 if(l_total_amb<>0) then
1388 REC_6001(rg_csr_get_person_details.PERSON_ID, rg_csr_get_person_details.assignment_ID, '0016',abs(l_total_amb)*1000000,SIGN(l_total_amb));
1389 end if;
1390
1391 l_car := GET_DEFINED_BALANCE_VALUE(l_assignment_id, 'Total Taxable Car Amount','_ASG_PTD' ,l_bal_date);
1392 if(l_car<>0) then
1393 REC_6001(rg_csr_get_person_details.PERSON_ID, rg_csr_get_person_details.assignment_ID,'0019',abs(l_car)*1000000,SIGN(l_car));
1394 end if;
1395
1396 -- fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 8');
1397 l_board_lodge := GET_DEFINED_BALANCE_VALUE(l_assignment_id, 'Total Board and Lodge Amount','_ASG_PTD' ,l_bal_date);
1398 if(l_board_lodge<>0) then
1399 REC_6001(rg_csr_get_person_details.PERSON_ID, rg_csr_get_person_details.assignment_ID,'0021',abs(l_board_lodge)*1000000,SIGN(l_board_lodge));
1400 end if;
1401
1402 -- fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 9');
1403 l_employer_atp := GET_DEFINED_BALANCE_VALUE(l_assignment_id, 'Employer ATP Deductions','_ASG_PTD' ,l_bal_date);
1404 l_total_atp := l_emp_atp+l_employer_atp;
1405 if(l_total_atp<>0) then
1406 REC_6001(rg_csr_get_person_details.PERSON_ID, rg_csr_get_person_details.assignment_ID,'0046',abs(l_total_atp)*1000000,SIGN(l_total_atp));
1407 end if;
1408
1409 -- fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 10');
1410 l_mileage := GET_DEFINED_BALANCE_VALUE(l_assignment_id, 'Total Mileage Claimed Paid','_ASG_PTD' ,l_bal_date);
1411 if(l_mileage<>0) then
1412 REC_6001(rg_csr_get_person_details.PERSON_ID, rg_csr_get_person_details.assignment_ID,'0048',abs(l_mileage)*1000000,SIGN(l_mileage));
1413 end if;
1414
1415 -- fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 11');
1416 l_free_phone := GET_DEFINED_BALANCE_VALUE(l_assignment_id, 'Total Taxable Phone Amount','_ASG_PTD' ,l_bal_date);
1417 if(l_free_phone<>0) then
1418 REC_6001(rg_csr_get_person_details.PERSON_ID, rg_csr_get_person_details.assignment_ID,'0054',abs(l_free_phone)*1000000,SIGN(l_free_phone));
1419 end if;
1420
1421 -- fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 12');
1422 l_sp_bonus := GET_DEFINED_BALANCE_VALUE(l_assignment_id, 'Special Pay','_ASG_PTD' ,l_bal_date);
1423 if(l_sp_bonus<>0) then
1424 REC_6001(rg_csr_get_person_details.PERSON_ID, rg_csr_get_person_details.assignment_ID,'0069',abs(l_sp_bonus)*1000000,SIGN(l_sp_bonus));
1425 end if;
1426
1427 -- fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 13');
1428 --Record - 6002
1429 pay_action_information_api.create_action_information (
1430 p_action_information_id => l_action_info_id
1431 ,p_action_context_id => p_assignment_action_id
1432 ,p_action_context_type => 'AAP'
1433 ,p_object_version_number => l_ovn
1434 ,p_effective_date => g_effective_date
1435 ,p_source_id => NULL
1436 ,p_source_text => NULL
1437 ,p_action_information_category => 'EMEA REPORT INFORMATION'
1438 ,p_action_information1 => 'PYDKEINA'
1439 ,p_action_information2 => l_payroll_action_id
1440 ,p_action_information3 => '6002'
1441 ,p_action_information4 => rg_csr_get_person_details.PERSON_ID
1442 ,p_action_information5 => rg_csr_get_person_details.assignment_ID
1443 ,p_action_information6 => '0500'
1444 ,p_action_information7 => '6750000005'
1445 ,p_action_information8 => null
1446 ,p_action_information9 => null
1447 ,p_action_information10 => null
1448 ,p_action_information11 => null
1449 ,p_action_information12 => null
1450 ,p_action_information13 => null
1451 ,p_action_information14 => null
1452 ,p_action_information15 => null
1453 ,p_action_information16 => null
1454 ,p_action_information17 => null
1455 ,p_action_information18 => null
1456 ,p_action_information19 => null
1457 ,p_action_information20 => null
1458 ,p_action_information21 => null
1459 ,p_action_information22 => null
1460 ,p_action_information23 => null
1461 ,p_action_information24 => null
1462 ,p_action_information25 => null
1463 ,p_action_information26 => null
1464 ,p_action_information27 => null
1465 ,p_action_information28 => null
1466 ,p_action_information29 => null
1467 ,p_action_information30 => null);
1468
1469 -- fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 14');
1470 --Record - 6004
1471 pay_action_information_api.create_action_information (
1472 p_action_information_id => l_action_info_id
1473 ,p_action_context_id => p_assignment_action_id
1474 ,p_action_context_type => 'AAP'
1475 ,p_object_version_number => l_ovn
1476 ,p_effective_date => g_effective_date
1477 ,p_source_id => NULL
1478 ,p_source_text => NULL
1479 ,p_action_information_category => 'EMEA REPORT INFORMATION'
1480 ,p_action_information1 => 'PYDKEINA'
1481 ,p_action_information2 => l_payroll_action_id
1482 ,p_action_information3 => '6004'
1483 ,p_action_information4 => rg_csr_get_person_details.PERSON_ID
1484 ,p_action_information5 => rg_csr_get_person_details.assignment_ID
1485 ,p_action_information6 => '0045'
1486 ,p_action_information7 => rg_csr_get_atp_table_value.screen_entry_value -- Input Value(atp table) of Employee ATP
1487 ,p_action_information8 => null
1488 ,p_action_information9 => null
1489 ,p_action_information10 => null
1490 ,p_action_information11 => null
1491 ,p_action_information12 => null
1492 ,p_action_information13 => null
1493 ,p_action_information14 => null
1494 ,p_action_information15 => null
1495 ,p_action_information16 => null
1496 ,p_action_information17 => null
1497 ,p_action_information18 => null
1498 ,p_action_information19 => null
1499 ,p_action_information20 => null
1500 ,p_action_information21 => null
1501 ,p_action_information22 => null
1502 ,p_action_information23 => null
1503 ,p_action_information24 => null
1504 ,p_action_information25 => null
1505 ,p_action_information26 => null
1506 ,p_action_information27 => null
1507 ,p_action_information28 => null
1508 ,p_action_information29 => null
1509 ,p_action_information30 => null);
1510
1511 l_total_atp_hours := GET_DEFINED_BALANCE_VALUE(l_assignment_id, 'Total ATP Hours','_ASG_PTD' ,l_bal_date);
1512 l_total_atp_hours := ROUND(l_total_atp_hours,2); -- rounding to 2 decimals
1513
1514 -- fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 15');
1515 --Record - 6005
1516 pay_action_information_api.create_action_information (
1517 p_action_information_id => l_action_info_id
1518 ,p_action_context_id => p_assignment_action_id
1519 ,p_action_context_type => 'AAP'
1520 ,p_object_version_number => l_ovn
1521 ,p_effective_date => g_effective_date
1522 ,p_source_id => NULL
1523 ,p_source_text => NULL
1524 ,p_action_information_category => 'EMEA REPORT INFORMATION'
1525 ,p_action_information1 => 'PYDKEINA'
1526 ,p_action_information2 => l_payroll_action_id
1527 ,p_action_information3 => '6005'
1528 ,p_action_information4 => rg_csr_get_person_details.PERSON_ID
1529 ,p_action_information5 => rg_csr_get_person_details.assignment_ID
1530 ,p_action_information6 => '0200'
1531 ,p_action_information7 => lpad((l_total_atp_hours*100),8,0)
1532 ,p_action_information8 => '+'
1533 ,p_action_information9 => null
1534 ,p_action_information10 => null
1535 ,p_action_information11 => null
1536 ,p_action_information12 => null
1537 ,p_action_information13 => null
1538 ,p_action_information14 => null
1539 ,p_action_information15 => null
1540 ,p_action_information16 => null
1541 ,p_action_information17 => null
1542 ,p_action_information18 => null
1543 ,p_action_information19 => null
1544 ,p_action_information20 => null
1545 ,p_action_information21 => null
1546 ,p_action_information22 => null
1547 ,p_action_information23 => null
1548 ,p_action_information24 => null
1549 ,p_action_information25 => null
1550 ,p_action_information26 => null
1551 ,p_action_information27 => null
1552 ,p_action_information28 => null
1553 ,p_action_information29 => null
1554 ,p_action_information30 => null);
1555
1556 end if; -- end company terminating check condition.
1557 end if; -- g_flag
1558
1559 -- fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 16');
1560 IF g_debug THEN
1561 hr_utility.set_location(' Leaving Procedure ARCHIVE_CODE',390);
1562 END IF;
1563
1564 -- fnd_file.put_line(fnd_file.log,'ARCHIVE CODE END');
1565
1566 EXCEPTION
1567 WHEN others THEN
1568 IF g_debug THEN
1569 hr_utility.set_location('error raised in archive code ',5);
1570 END if;
1571 RAISE;
1572 END ARCHIVE_CODE;
1573
1574 PROCEDURE DEINITIALIZATION_CODE
1575 (p_payroll_action_id in pay_payroll_actions.payroll_action_id%type) is
1576
1577 /* Cursors to fetch data for record numbering*/
1578
1579 CURSOR csr_all_rec ( p_payroll_action_id NUMBER) IS
1580 SELECT *
1581 FROM pay_action_information pai
1582 WHERE pai.action_information_category = 'EMEA REPORT INFORMATION'
1583 AND pai.action_information1 = 'PYDKEINA'
1584 AND pai.action_information2 = to_char(p_payroll_action_id)
1585 AND pai.action_information3 IN ('1000','2001','2101','5000','6000')
1586 ORDER BY pai.action_information3,action_context_id DESC
1587 FOR UPDATE;
1588
1589 CURSOR csr_asg_all_rec ( p_payroll_action_id NUMBER,p_person_id NUMBER) IS
1590 SELECT *
1591 FROM pay_action_information pai
1592 WHERE pai.action_information_category = 'EMEA REPORT INFORMATION'
1593 AND pai.action_information1 = 'PYDKEINA'
1594 AND pai.action_information2 = to_char(p_payroll_action_id)
1595 AND pai.action_information4 = to_char(p_person_id)
1596 AND pai.action_information3 IN ('6001','6002','6004','6005')
1597 ORDER BY pai.action_information3,action_context_id,pai.action_information6 DESC
1598 FOR UPDATE;
1599
1600
1601
1602 l_action_info_id NUMBER;
1603 l_ovn NUMBER;
1604 l_end_code NUMBER;
1605
1606
1607 BEGIN
1608 l_end_code:=0 ;
1609 if(g_flag=0) then
1610 -- fnd_file.put_line(fnd_file.log,'DE-INITIALIZATION CODE 1');
1611
1612 FOR rg_csr_all_rec IN csr_all_rec( p_payroll_action_id)
1613 LOOP
1614 l_end_code:=l_end_code + 1 ;
1615 -- fnd_file.put_line(fnd_file.log,'DE-INITIALIZATION CODE 2');
1616
1617 UPDATE pay_action_information pai
1618 SET pai.action_information30 =LPAD(l_end_code,7,'0')
1619 WHERE CURRENT OF csr_all_rec;
1620
1621 /* fnd_file.put_line(fnd_file.log,'DE-INITIALIZATION CODE 3');
1622 fnd_file.put_line(fnd_file.log,'payroll action id:'||p_payroll_action_id);
1623 fnd_file.put_line(fnd_file.log,'action info 4:'||rg_csr_all_rec.action_information4); */
1624
1625 IF rg_csr_all_rec.action_information3='6000' THEN
1626 FOR rg_csr_asg_all_rec IN csr_asg_all_rec( p_payroll_action_id,to_number(rg_csr_all_rec.action_information4))
1627 LOOP
1628 l_end_code:=l_end_code + 1 ;
1629 UPDATE pay_action_information pai
1630 SET pai.action_information30 =LPAD(l_end_code,7,'0')
1631 WHERE CURRENT OF csr_asg_all_rec;
1632 -- fnd_file.put_line(fnd_file.log,'DE-INITIALIZATION CODE 4');
1633 END LOOP;
1634 END IF;
1635 -- fnd_file.put_line(fnd_file.log,'DE-INITIALIZATION CODE 5');
1636 END LOOP;
1637 l_end_code:=l_end_code + 1 ;
1638
1639 -- fnd_file.put_line(fnd_file.log,'DE-INITIALIZATION CODE 6');
1640
1641 pay_action_information_api.create_action_information (
1642 p_action_information_id=> l_action_info_id,
1643 p_action_context_id=> p_payroll_action_id,
1644 p_action_context_type=> 'PA',
1645 p_object_version_number=> l_ovn,
1646 p_effective_date=> g_effective_date,
1647 p_source_id=> NULL,
1648 p_source_text=> NULL,
1649 p_action_information_category=> 'EMEA REPORT INFORMATION',
1650 p_action_information1=> 'PYDKEINA',
1651 p_action_information2=> p_payroll_action_id,
1652 p_action_information3=> '9999',
1653 p_action_information4=> l_end_code,
1654 p_action_information5=> NULL,
1655 p_action_information6=> NULL,
1656 p_action_information7=> NULL,
1657 p_action_information8=> NULL,
1658 p_action_information9=> NULL,
1659 p_action_information10=> NULL,
1660 p_action_information11=> NULL,
1661 p_action_information12=> NULL,
1662 p_action_information13=> NULL,
1663 p_action_information14=> NULL,
1664 p_action_information15=> NULL,
1665 p_action_information16=> NULL,
1666 p_action_information17=> NULL,
1667 p_action_information18=> NULL,
1668 p_action_information19=> NULL,
1669 p_action_information20=> NULL,
1670 p_action_information21=> NULL,
1671 p_action_information22=> NULL,
1672 p_action_information23=> NULL,
1673 p_action_information24=> NULL,
1674 p_action_information25=> NULL,
1675 p_action_information26=> NULL,
1676 p_action_information27=> NULL,
1677 p_action_information28=> NULL,
1678 p_action_information29=> NULL,
1679 p_action_information30=> LPAD(l_end_code,7,'0')
1680 );
1681
1682 end if; -- g_flag
1683 -- fnd_file.put_line(fnd_file.log,'DE-INITIALIZATION CODE 7');
1684 IF g_debug THEN
1685 hr_utility.set_location(' Leaving Procedure DEINITIALIZATION_CODE',390);
1686 END IF;
1687
1688 -- fnd_file.put_line(fnd_file.log,'DE-INITIALIZATION CODE 8');
1689 EXCEPTION
1690 WHEN others THEN
1691 IF g_debug THEN
1692 hr_utility.set_location('error raised in DEINITIALIZATION_CODE ',5);
1693 END if;
1694 RAISE;
1695 END;
1696
1697 BEGIN
1698
1699 g_payroll_action_id :=NULL;
1700 g_le_assignment_action_id :=NULL;
1701 g_business_group_id :=NULL;
1702 g_legal_employer_id :=NULL;
1703 g_effective_date :=NULL;
1704 g_payroll_id :=NULL;
1705 g_payroll_period :=NULL;
1706 g_test_submission :=NULL;
1707 g_company_terminating :=NULL;
1708
1709 END PAY_DK_ARCHIVE_EIN;