[Home] [Help]
PACKAGE BODY: APPS.PAY_DK_ARCHIVE_EHOL
Source
1 PACKAGE BODY PAY_DK_ARCHIVE_EHOL AS
2 /* $Header: pydkeholca.pkb 120.0.12020000.2 2012/07/18 05:29:44 rsahai noship $ */
3
4 g_debug BOOLEAN := hr_utility.debug_enabled;
5 g_package VARCHAR2(33) := ' PAY_DK_ARCHIVE_EHOL.';
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_start_date VARCHAR2(30); /* 9489806 */
13 g_end_date VARCHAR2(30); /* 9489806 */
14 g_payroll_type VARCHAR2(30); /* 9489806 */
15 g_payroll_id NUMBER;
16 g_year VARCHAR2(30);
17 g_payroll_period NUMBER;
18 g_test_submission VARCHAR2(1);
19 g_company_terminating VARCHAR2(1);
20 g_msg_txt VARCHAR2(300);
21 g_flag NUMBER:=0;
22 l_bincome_exception exception;
23 l_ehc_exception exception; --14078879
24 error_message boolean; --14078879
25 l_errflag VARCHAR2(1) := 'N'; --14078879
26 l_errflag_uhc VARCHAR2(1) := 'N'; --14078879
27 l_errflag_uhc1 VARCHAR2(1) := 'N'; --14078879
28 --
29 FUNCTION GET_COUNTRY_NAME(p_territory_code VARCHAR2)
30 RETURN VARCHAR2
31 IS
32
33 CURSOR csr_get_territory_name(p_territory_code VARCHAR2) Is
34 SELECT territory_short_name
35 FROM fnd_territories_vl
36 WHERE territory_code = p_territory_code;
37
38 l_country fnd_territories_vl.territory_short_name%TYPE;
39
40 BEGIN
41
42 IF g_debug THEN
43 hr_utility.set_location(' Entering Function PAY_DK_ARCHIVE_EHOL.GET_COUNTRY_NAME',111);
44 END IF;
45
46 OPEN csr_get_territory_name(p_territory_code);
47 FETCH csr_get_territory_name into l_country;
48 CLOSE csr_get_territory_name;
49
50 RETURN l_country;
51
52 IF g_debug THEN
53 hr_utility.set_location(' Leaving Function PAY_DK_ARCHIVE_EHOL.GET_COUNTRY_NAME',112);
54 END IF;
55
56 END GET_COUNTRY_NAME;
57 --
58
59 --13463885
60 FUNCTION get_defined_balance_id (
61 p_balance_name VARCHAR2,
62 p_dimension_suffix VARCHAR2
63 )
64 RETURN NUMBER
65 AS
66 CURSOR csr_defined_balance_id
67 IS
68 SELECT pdb.defined_balance_id
69 FROM pay_balance_dimensions pbd,
70 pay_balance_types pbt,
71 pay_defined_balances pdb
72 WHERE pbd.database_item_suffix = p_dimension_suffix
73 AND pbd.business_group_id IS NULL
74 AND pbd.legislation_code = 'DK'
75 AND pbt.balance_name = p_balance_name
76 AND pbt.business_group_id IS NULL
77 AND pbt.legislation_code = 'DK'
78 AND pdb.balance_type_id = pbt.balance_type_id
79 AND pdb.balance_dimension_id = pbd.balance_dimension_id
80 AND pdb.business_group_id IS NULL
81 AND pdb.legislation_code = 'DK';
82
83 l_defined_balance_id pay_defined_balances.defined_balance_id%TYPE;
84 BEGIN
85 OPEN csr_defined_balance_id;
86 FETCH csr_defined_balance_id INTO l_defined_balance_id;
87 CLOSE csr_defined_balance_id;
88 RETURN l_defined_balance_id;
89 END;
90 --13463885
91
92
93 FUNCTION GET_DEFINED_BALANCE_VALUE
94 (p_assignment_id IN NUMBER
95 ,p_balance_name IN VARCHAR2
96 ,p_balance_dim IN VARCHAR2
97 ,p_virtual_date IN DATE) RETURN NUMBER IS
98
99 l_context1 PAY_DEFINED_BALANCES.DEFINED_BALANCE_ID%TYPE;
100 l_value NUMBER;
101
102
103 CURSOR get_dbal_id(p_balance_name VARCHAR2 , p_balance_dim VARCHAR2) IS
104 SELECT pdb.defined_balance_id
105 FROM pay_defined_balances pdb
106 ,pay_balance_types pbt
107 ,pay_balance_dimensions pbd
108 WHERE pbt.legislation_code='DK'
109 AND pbt.balance_name = p_balance_name
110 AND pbd.legislation_code = 'DK'
111 AND pbd.database_item_suffix = p_balance_dim
112 AND pdb.balance_type_id = pbt.balance_type_id
113 AND pdb.balance_dimension_id = pbd.balance_dimension_id;
114
115
116 BEGIN
117
118 OPEN get_dbal_id(p_balance_name, p_balance_dim);
119 FETCH get_dbal_id INTO l_context1;
120 CLOSE get_dbal_id;
121
122 l_value := nvl(pay_balance_pkg.get_value(l_context1,p_assignment_id,p_virtual_date), 0);
123
124 RETURN l_value;
125
126 END GET_DEFINED_BALANCE_VALUE ;
127
128
129 /* GET PARAMETER */
130 FUNCTION GET_PARAMETER(
131 p_parameter_string IN VARCHAR2
132 ,p_token IN VARCHAR2
133 ,p_segment_number IN NUMBER default NULL ) RETURN VARCHAR2
134 IS
135 l_parameter pay_payroll_actions.legislative_parameters%TYPE:=NULL;
136 l_start_pos NUMBER;
137 l_delimiter VARCHAR2(1):=' ';
138 l_proc VARCHAR2(40):= g_package||' get parameter ';
139 BEGIN
140 --
141 IF g_debug THEN
142 hr_utility.set_location(' Entering Function GET_PARAMETER',10);
143 END IF;
144 l_start_pos := instr(' '||p_parameter_string,l_delimiter||p_token||'=');
145 --
146 IF l_start_pos = 0 THEN
147 l_delimiter := '|';
148 l_start_pos := instr(' '||p_parameter_string,l_delimiter||p_token||'=');
149 END IF;
150
151 IF l_start_pos <> 0 THEN
152 l_start_pos := l_start_pos + length(p_token||'=');
153 l_parameter := substr(p_parameter_string,
154 l_start_pos,
155 instr(p_parameter_string||' ',
156 l_delimiter,l_start_pos)
157 - l_start_pos);
158
159 IF p_token = 'MESSAGE_TEXT' THEN
160 l_parameter := substr(p_parameter_string,l_start_pos);
161 END IF;
162
163 IF p_segment_number IS NOT NULL THEN
164 l_parameter := ':'||l_parameter||':';
165 l_parameter := substr(l_parameter,
166 instr(l_parameter,':',1,p_segment_number)+1,
167 instr(l_parameter,':',1,p_segment_number+1) -1
168 - instr(l_parameter,':',1,p_segment_number));
169 END IF;
170 END IF;
171 --
172 IF g_debug THEN
173 hr_utility.set_location(' Leaving Function GET_PARAMETER',20);
174 END IF;
175
176 RETURN l_parameter;
177
178 END;
179
180 /* GET ALL PARAMETERS */
181 PROCEDURE GET_ALL_PARAMETERS(
182 p_payroll_action_id IN NUMBER
183 ,p_business_group_id OUT NOCOPY NUMBER
184 ,p_legal_employer_id OUT NOCOPY NUMBER
185 ,p_effective_date OUT NOCOPY DATE
186 ,p_payroll OUT NOCOPY NUMBER
187 ,p_year OUT NOCOPY NUMBER
188 ,p_payroll_type OUT NOCOPY VARCHAR2 /* 9489806 */
189 ,p_start_date OUT NOCOPY VARCHAR2 /* 9489806 */
190 ,p_end_date OUT NOCOPY VARCHAR2 /* 9489806 */
191 ,p_test_submission OUT NOCOPY VARCHAR2
192 ,p_company_terminating OUT NOCOPY VARCHAR2
193 ,p_msg_txt OUT NOCOPY VARCHAR2
194 ) IS
195
196 CURSOR csr_parameter_info(p_payroll_action_id NUMBER) IS
197 SELECT PAY_DK_ARCHIVE_EHOL.GET_PARAMETER(legislative_parameters,'LEGAL_EMPLOYER_ID')
198 ,PAY_DK_ARCHIVE_EHOL.GET_PARAMETER(legislative_parameters,'PAYROLL')
199 ,PAY_DK_ARCHIVE_EHOL.GET_PARAMETER(legislative_parameters,'YEAR')
200 ,PAY_DK_ARCHIVE_EHOL.GET_PARAMETER(legislative_parameters,'PAYROLL_TYPE')
201 ,PAY_DK_ARCHIVE_EHOL.GET_PARAMETER(legislative_parameters,'START_DATE')
202 ,PAY_DK_ARCHIVE_EHOL.GET_PARAMETER(legislative_parameters,'END_DATE')
203 ,PAY_DK_ARCHIVE_EHOL.GET_PARAMETER(legislative_parameters,'TEST_SUBMISSION')
204 ,PAY_DK_ARCHIVE_EHOL.GET_PARAMETER(legislative_parameters,'COMPANY_TERMINATING')
205 ,PAY_DK_ARCHIVE_EHOL.GET_PARAMETER(legislative_parameters,'MESSAGE_TEXT')
206 ,effective_date
207 ,business_group_id
208 FROM pay_payroll_actions
209 WHERE payroll_action_id = p_payroll_action_id;
210
211 l_proc VARCHAR2(240):= g_package||' GET_ALL_PARAMETERS ';
212 --
213 BEGIN
214
215 OPEN csr_parameter_info (p_payroll_action_id);
216
217 FETCH csr_parameter_info
218 INTO p_legal_employer_id
219 ,p_payroll
220 ,p_year
221 ,p_payroll_type
222 ,p_start_date
223 ,p_end_date
224 ,p_test_submission
225 ,p_company_terminating
226 ,p_msg_txt
227 ,p_effective_date
228 ,p_business_group_id;
229 CLOSE csr_parameter_info;
230 --
231 hr_utility.set_location(' get paramerters:p_start_date ' || p_start_date,30);
232 hr_utility.set_location(' get paramerters:p_end_date ' || p_end_date,30);
233 hr_utility.set_location(' get paramerters:p_payroll_type ' || p_payroll_type,30);
234 IF g_debug THEN
235 hr_utility.set_location(' Leaving Procedure GET_ALL_PARAMETERS',30);
236 END IF;
237
238 END GET_ALL_PARAMETERS;
239
240 /* RANGE CODE */
241 PROCEDURE RANGE_CODE
242 (p_payroll_action_id IN NUMBER
243 ,p_sql OUT NOCOPY VARCHAR2)
244 IS
245 l_action_info_id NUMBER;
246 l_ovn NUMBER;
247 l_count NUMBER := 0;
248 l_business_group_id NUMBER;
249 l_test_submission varchar2(1);
250 -- l_company_terminating varchar2(1); bug 10367494
251 l_emp_id hr_organization_units.organization_id%TYPE ;
252 l_le_name hr_organization_units.name%TYPE ;
253 l_business_id hr_organization_information.org_information1%TYPE ;
254 l_cvr_number hr_organization_information.org_information1%TYPE ;
255 l_sp_cvr_number hr_organization_information.org_information1%TYPE ;
256 l_org_type hr_organization_information.org_information1%TYPE ;
257 l_date VARCHAR2(100);
258 l_time VARCHAR2(10);
259
260 l_date_id VARCHAR2(20);
261 l_time_id VARCHAR2(20);
262
263 l_lb_num VARCHAR2(10);
264 l_unique_id VARCHAR2(50);
265 l_unique_id1 VARCHAR2(50); /* 9489806 */
266
267 l_canonical_start_date DATE; /* 9489806 */
268 l_canonical_end_date DATE; /* 9489806 */
269
270 /*Cursors */
271
272 /*Legal Employer Information*/
273 Cursor csr_Legal_Emp_Details ( csr_v_legal_emp_id hr_organization_information.ORGANIZATION_ID%TYPE)
274 IS
275 SELECT o1.name ,hoi2.ORG_INFORMATION1 , hoi2.ORG_INFORMATION2, hoi2.ORG_INFORMATION3, hoi2.ORG_INFORMATION4, hoi2.ORG_INFORMATION5, hoi2.ORG_INFORMATION6, hoi2.ORG_INFORMATION13
276 FROM hr_organization_units o1
277 , hr_organization_information hoi1
278 , hr_organization_information hoi2
279 WHERE o1.business_group_id =l_business_group_id
280 AND hoi1.organization_id = o1.organization_id
281 AND hoi1.organization_id = csr_v_legal_emp_id
282 AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
283 AND hoi1.org_information_context = 'CLASS'
284 AND o1.organization_id =hoi2.organization_id
285 AND hoi2.ORG_INFORMATION_CONTEXT='DK_LEGAL_ENTITY_DETAILS' ;
286
287 rg_Legal_Emp_Details csr_Legal_Emp_Details%rowtype;
288
289 /*Legal Employer MO, FO, EH Codes */
290 Cursor csr_Legal_Emp_MO_FO_FH ( csr_v_legal_emp_id hr_organization_information.ORGANIZATION_ID%TYPE)
291 IS
292 SELECT
293 hoi2.ORG_INFORMATION2,
294 hoi2.ORG_INFORMATION3,
295 hoi2.ORG_INFORMATION4,
296 hoi2.ORG_INFORMATION5,
297 hoi2.ORG_INFORMATION6
298 FROM hr_organization_units o1
299 , hr_organization_information hoi1
300 , hr_organization_information hoi2
301 WHERE o1.business_group_id =l_business_group_id
302 AND hoi1.organization_id = o1.organization_id
303 AND hoi1.organization_id = csr_v_legal_emp_id
304 AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
305 AND hoi1.org_information_context = 'CLASS'
306 AND o1.organization_id =hoi2.organization_id
307 AND hoi2.ORG_INFORMATION_CONTEXT='DK_DA_OFFICE_CODE';
308
309 rg_Legal_Emp_MO_FO_FH csr_Legal_Emp_MO_FO_FH%rowtype;
310
311
312 -- Service Provider information.
313 cursor service_provider_details
314 is
315 select * from hr_organization_information
316 where org_information_context = 'DK_SERVICE_PROVIDER_DETAILS'
317 and organization_id in (
318 select organization_id from hr_organization_units
319 where business_group_id= l_business_group_id);
320
321 sp service_provider_details%rowtype;
322
323 /* Payroll Time period */
324 CURSOR csr_pay_periods(p_payroll_id NUMBER, p_payroll_period NUMBER)
325 IS
326 SELECT ptp.start_date, ptp.end_date, ptp.period_name,
327 default_dd_date,
328 decode(PERIOD_TYPE
329 ,'Calendar Month','1'
330 ,'Bi-Week' ,'2'
331 ,'Week' ,'3'
332 ,'Lunar Month' ,'4') PAYROLL_PERIOD
333 from per_time_periods ptp
334 WHERE payroll_id = p_payroll_id
335 AND time_period_id = p_payroll_period;
336
337 rg_csr_pay_periods csr_pay_periods%rowtype;
338
339 /* Payroll Name */
340 CURSOR csr_payroll_name(id pay_all_payrolls_f.payroll_id%type)
341 IS
342 select payroll_name from pay_payrolls_f
343 where payroll_id=id;
344
345 -- rg_csr_payroll_name csr_payroll_name%rowtype;
346 l_payroll_name VARCHAR2(100):=NULL;
347
348 CURSOR csr_le_payrolls is /* 9489806 */
349 SELECT DISTINCT
350 ppf.payroll_name,
351 ppf.payroll_id,
352 ptp.start_date,
353 ptp.end_date,
354 ptp.period_name,
355 ptp.default_dd_date,
356 decode(ptp.PERIOD_TYPE
357 ,'Calendar Month','1'
358 ,'Bi-Week' ,'2'
359 ,'Week' ,'3'
360 ,'Lunar Month' ,'4') PAYROLL_PERIOD,
361 ptp.TIME_PERIOD_ID
362 FROM
363 pay_payrolls_f ppf,
364 per_assignments_f paf,
365 hr_organization_units hou,
366 hr_organization_information hoi,
367 hr_soft_coding_keyflex scl,
368 per_time_periods ptp
369 WHERE hou.business_group_id = l_business_group_id
370 AND hou.organization_id = g_legal_employer_id
371 AND hou.organization_id = hoi.organization_id
372 AND hoi.org_information_context = 'CLASS'
373 AND hoi.org_information1 = 'HR_LEGAL_EMPLOYER'
374 AND paf.payroll_id = ppf.payroll_id
375 AND ppf.payroll_id=NVL(g_payroll_id,ppf.payroll_id)
376 AND ppf.effective_start_date <= l_canonical_start_date and ppf.effective_end_date >= l_canonical_end_date
377 AND ptp.payroll_id=ppf.payroll_id
378 AND ptp.end_date BETWEEN l_canonical_start_date AND l_canonical_end_date
379 AND paf.SOFT_CODING_KEYFLEX_ID = scl.SOFT_CODING_KEYFLEX_ID
380 AND scl.segment1 = to_char(hou.organization_id)
381 AND ppf.business_group_id = l_business_group_id;
382
383 /* End of Cursors */
384
385
386 Cursor cur_report_type IS
387 select
388 report_type
389 from
390 pay_payroll_actions
391 where payroll_action_id = p_payroll_action_id;
392
393 l_report_type VARCHAR2(100);
394
395 CURSOR csr_use_hol_card(p_org_id NUMBER) is
396 SELECT NVL(org_information5,'N')
397 FROM hr_organization_information
398 WHERE organization_id = p_org_id
399 and org_information_context = 'DK_HOLIDAY_ENTITLEMENT_INFO';
400
401 CURSOR get_value_from_ddf(p_payroll_id NUMBER , p_date_earned DATE) IS
402 SELECT PRL_INFORMATION1, Payroll_name
403 FROM pay_payrolls_f ppf
404 WHERE PAYROLL_ID = p_payroll_id
405 AND p_date_earned BETWEEN ppf.EFFECTIVE_START_DATE AND ppf.EFFECTIVE_END_DATE;
406
407 l_use_holiday_card_1 VARCHAR2(10) := 'N';
408 l_pyrl_name_1 pay_payrolls_f.Payroll_name%type;
409 l_uhc_payroll_1 VARCHAR2(10);
410
411 BEGIN
412
413 hr_utility.set_location(' Entering Procedure RANGE_CODE',1);
414
415 IF g_debug THEN
416 hr_utility.set_location(' Entering Procedure RANGE_CODE',40);
417 END IF;
418
419 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';
420
421 g_legal_employer_id := NULL ;
422 g_effective_date := NULL ;
423 g_start_date := NULL;
424 g_end_date := NULL;
425 g_payroll_action_id := p_payroll_action_id ;
426 g_le_assignment_action_id := NULL ;
427 g_payroll_id:= NULL;
428 g_payroll_period:= NULL;
429 g_test_submission:= NULL;
430 g_year:= NULL;
431 g_company_terminating:= NULL;
432 g_msg_txt:= NULL;
433
434 hr_utility.set_location('RANGE_CODE:Before Calling all parameters',2);
435
436 PAY_DK_ARCHIVE_EHOL.GET_ALL_PARAMETERS(
437 p_payroll_action_id
438 ,l_business_group_id
439 ,g_legal_employer_id
440 ,g_effective_date
441 ,g_payroll_id
442 ,g_year
443 ,g_payroll_type
444 ,g_start_date
445 ,g_end_date
446 ,g_test_submission
447 ,g_company_terminating
448 ,g_msg_txt) ;
449
450 hr_utility.set_location('RANGE_CODE:After Calling all parameters',2);
451
452 l_canonical_start_date := fnd_date.canonical_to_date(g_start_date);
453 l_canonical_end_date := fnd_date.canonical_to_date(g_end_date);
454
455
456 OPEN csr_use_hol_card(g_legal_employer_id);
457 FETCH csr_use_hol_card INTO l_use_holiday_card_1;
458 CLOSE csr_use_hol_card;
459
460 fnd_file.put_line(fnd_file.log,'l_use_holiday_card_1:'||l_use_holiday_card_1);
461
462 --14078879
463 IF l_use_holiday_card_1 = 'N' THEN
464 Fnd_file.put_line(FND_FILE.output,'Use Holiday Card not set at employer level.' );
465 l_errflag_uhc := 'Y';
466 END IF;
467 --14078879
468
469 OPEN cur_report_type;
470 FETCH cur_report_type INTO l_report_type;
471 CLOSE cur_report_type;
472
473 IF l_report_type = 'PYDKEHOLCA_ANN' THEN
474 l_canonical_start_date := to_date('01-01-'||g_year,'dd-mm-rrrr');
475 l_canonical_end_date := to_date('31-12-'||g_year,'dd-mm-rrrr');
476 END IF;
477
478
479 IF g_payroll_id IS NOT NULL THEN
480 OPEN get_value_from_ddf(g_payroll_id, l_canonical_start_date);
481 FETCH get_value_from_ddf INTO l_uhc_payroll_1, l_pyrl_name_1;
482 CLOSE get_value_from_ddf;
483
484 fnd_file.put_line(fnd_file.log,'1-l_uhc_payroll_1:'||l_uhc_payroll_1);
485
486 l_uhc_payroll_1 := NVL(l_uhc_payroll_1,l_use_holiday_card_1);
487
488 IF l_uhc_payroll_1 = 'N' THEN
489 Fnd_file.put_line(FND_FILE.output,'Payroll:'||l_pyrl_name_1||': Use Holiday Card not set at payroll level.');
490 l_errflag_uhc := 'Y';
491 END IF;
492 END IF;
493
494
495 SELECT count(*)
496 INTO l_count
497 FROM pay_action_information
498 WHERE action_information_category = 'EMEA REPORT DETAILS'
499 AND action_information1 IN ('PYDKEHOLCA' , 'PYDKEHOLCA_ANN', 'PYDKEHOLCA_MIA')
500 AND action_context_id = p_payroll_action_id;
501
502 -- fnd_file.put_line(fnd_file.log,'Range Code 6');
503
504
505
506
507 IF l_count < 1 then
508
509 hr_utility.set_location('Entered Procedure GETDATA',10);
510
511
512 -- fnd_file.put_line(fnd_file.log,'g_legal_employer_id : '||g_legal_employer_id);
513 -- fnd_file.put_line(fnd_file.log,'l_business_group_id : '||l_business_group_id);
514 OPEN csr_Legal_Emp_Details(g_legal_employer_id);
515 FETCH csr_Legal_Emp_Details INTO rg_Legal_Emp_Details;
516
517 /* if(csr_Legal_Emp_Details%notfound) then
518 fnd_file.put_line(fnd_file.log,'No data found for the legal emp');
519 end if;*/
520
521 l_le_name := rg_Legal_Emp_Details.name ;
522 l_cvr_number := rg_Legal_Emp_Details.ORG_INFORMATION1 ;
523
524 -- fnd_file.put_line(fnd_file.log,'l_cvr_number : '||l_cvr_number);
525
526 CLOSE csr_Legal_Emp_Details;
527
528 OPEN csr_Legal_Emp_MO_FO_FH(g_legal_employer_id);
529 FETCH csr_Legal_Emp_MO_FO_FH INTO rg_Legal_Emp_MO_FO_FH;
530 CLOSE csr_Legal_Emp_MO_FO_FH;
531
532 /* Pick up the details belonging to Legal Employer
533
534 OPEN csr_Legal_Emp_Details(g_legal_employer_id);
535 FETCH csr_Legal_Emp_Details INTO rg_Legal_Emp_Details;
536 CLOSE csr_Legal_Emp_Details;
537
538 l_le_name := rg_Legal_Emp_Details.name ;
539 l_business_id := rg_Legal_Emp_Details.ORG_INFORMATION1 ; */
540
541 -- date and time
542 SELECT to_char(sysdate,'yyyy-mm-dd') INTO l_date FROM dual;
543 SELECT to_char(sysdate,'hh:mi:ss') INTO l_time FROM dual;
544
545 SELECT to_char(sysdate,'yyyymmdd') INTO l_date_id FROM dual;
546 SELECT to_char(sysdate,'hhmiss') INTO l_time_id FROM dual;
547
548 if(g_test_submission='Y') then
549 l_test_submission:='T';
550 else
551 l_test_submission:='P';
552 end if;
553
554 /* if(g_company_terminating='Y') then
555 l_company_terminating:='A';
556 else
557 l_company_terminating:=null;
558 end if; */ -- bug 10367494
559
560 --Employer Level Information
561 --l_unique_id:=lpad(substr(to_char(p_payroll_action_id), -least(length(p_payroll_action_id),8)),8,0);
562 l_unique_id := to_char(p_payroll_action_id)||l_date_id||l_time_id;
563
564 --14078879
565
566 IF l_cvr_number IS NULL THEN
567 Fnd_file.put_line(FND_FILE.output,'You have not entered the employer CVR number. Enter a valid CVR number.' );
568 l_errflag := 'Y';
569 END IF;
570
571 IF rg_Legal_Emp_Details.ORG_INFORMATION5 IS NULL THEN
572 Fnd_file.put_line(FND_FILE.output,'You have not entered the employer SE number. Enter a valid SE number.' );
573 l_errflag := 'Y';
574 END IF;
575
576 IF rg_Legal_Emp_MO_FO_FH.ORG_INFORMATION2 IS NULL THEN
577 Fnd_file.put_line(FND_FILE.output,'You have not entered the employer MO code. Enter a valid MO Code.' );
578 l_errflag := 'Y';
579 END IF;
580
581 /*
582 IF l_errflag = 'Y' THEN
583 Fnd_file.put_line(FND_FILE.LOG,'Process completed with warning(s). Please check the log file.' );
584 error_message := FND_CONCURRENT.SET_COMPLETION_STATUS('WARNING','Process completed with warning(s). Please check the log file.');
585 END IF;
586 */
587 --14078879
588
589 pay_action_information_api.create_action_information (
590 p_action_information_id => l_action_info_id
591 ,p_action_context_id => p_payroll_action_id
592 ,p_action_context_type => 'PA'
593 ,p_object_version_number => l_ovn
594 ,p_effective_date => g_effective_date
595 ,p_source_id => NULL
596 ,p_source_text => NULL
597 ,p_action_information_category => 'EMEA REPORT INFORMATION'
598 ,p_action_information1 => l_report_type --'PYDKEHOLCA', 'PYDKEHOLCA_ANN', PYDKEHOLCA_MIA
599 ,p_action_information2 => p_payroll_action_id
600 ,p_action_information3 => rg_Legal_Emp_Details.ORG_INFORMATION5 -- legal emp SE number
601 ,p_action_information4 => l_cvr_number -- legal emp CVR number
602 ,p_action_information5 => l_le_name
603 ,p_action_information6 => l_date
604 ,p_action_information7 => l_time
605 ,p_action_information8 => l_unique_id
606 ,p_action_information9 => 'Oracle Payroll'
607 ,p_action_information10 => '1.0'
608 ,p_action_information11 => '1'
609 ,p_action_information12 => '71298515'
610 ,p_action_information13 => rg_Legal_Emp_MO_FO_FH.ORG_INFORMATION2
611 ,p_action_information14 => nvl(rg_Legal_Emp_MO_FO_FH.ORG_INFORMATION3,rg_Legal_Emp_MO_FO_FH.ORG_INFORMATION2) --14078879
612 ,p_action_information15 => rg_Legal_Emp_MO_FO_FH.ORG_INFORMATION4
613 ,p_action_information16 => null
614 ,p_action_information17 => null
615 ,p_action_information18 => null
616 ,p_action_information19 => null
617 ,p_action_information20 => null
618 ,p_action_information21 => null
619 ,p_action_information22 => null
620 ,p_action_information23 => null
621 ,p_action_information24 => null
622 ,p_action_information25 => null
623 ,p_action_information26 => null
624 ,p_action_information27 => null
625 ,p_action_information28 => null
626 ,p_action_information29 => null
627 ,p_action_information30 => null);
628
629 END IF; --end l_count condition
630
631
632 IF l_errflag_uhc = 'Y' THEN
633 p_sql := 'SELECT DISTINCT person_id FROM per_people_f ppf,pay_payroll_actions ppa WHERE 1=2';
634 Raise l_ehc_exception;
635 END IF;
636
637 IF g_debug THEN
638 hr_utility.set_location(' Leaving Procedure RANGE_CODE',50);
639 END IF;
640
641 EXCEPTION
642 WHEN l_ehc_exception THEN
643 Hr_Utility.set_location('..'||'SQL-ERRM :'||SQLERRM,410);
644 Fnd_file.put_line(FND_FILE.LOG,'Process completed with error(s). Please check the output file.' );
645 error_message := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR','Process completed with error(s). Please check the output file.');
646 WHEN others THEN
647 IF g_debug THEN
648 hr_utility.set_location('error raised assignment_action_code ',5);
649 END if;
650 RAISE;
651 END RANGE_CODE;
652
653 /* ASSIGNMENT ACTION CODE */
654 PROCEDURE ASSIGNMENT_ACTION_CODE
655 (p_payroll_action_id IN NUMBER
656 ,p_start_person IN NUMBER
657 ,p_end_person IN NUMBER
658 ,p_chunk IN NUMBER )
659 IS
660
661 l_canonical_start_date DATE;
662 l_canonical_end_date DATE;
663 l_prepay_action_id NUMBER;
664 l_prev_person_id NUMBER;
665 l_prev_assg_id NUMBER;
666 l_prev_local_unit_id NUMBER;
667 l_actid NUMBER;
668
669
670 CURSOR csr_prepaid_assignments_le(p_payroll_action_id NUMBER,
671 p_start_person NUMBER,
672 p_end_person NUMBER,
673 p_legal_employer_id NUMBER,
674 p_payroll_id NUMBER,
675 p_payroll_period NUMBER,
676 l_canonical_start_date DATE,
677 l_canonical_end_date DATE)
678 IS
679 SELECT as1.person_id person_id,
680 act.assignment_id assignment_id,
681 --act.assignment_action_id run_action_id,
682 act1.assignment_action_id prepaid_action_id,
683 as1.hourly_salaried_code
684 ,as1.payroll_id
685 FROM pay_payroll_actions ppa
686 ,pay_payroll_actions appa
687 ,pay_payroll_actions appa2
688 ,pay_assignment_actions act
689 ,pay_assignment_actions act1
690 ,pay_action_interlocks pai
691 ,per_all_assignments_f as1
692 WHERE ppa.payroll_action_id = p_payroll_action_id
693 AND appa.effective_date BETWEEN l_canonical_start_date
694 AND l_canonical_end_date
695 AND as1.person_id BETWEEN p_start_person
696 AND p_end_person
697 AND appa.action_type IN ('R','Q')
698 -- Payroll Run or Quickpay Run
699 AND act.payroll_action_id = appa.payroll_action_id
700 --aapa table add time period check
701 -- AND appa.time_period_id = p_payroll_period
702 AND appa.effective_date between l_canonical_start_date and l_canonical_end_date
703 AND act.source_action_id IS NULL -- Master Action
704 AND as1.assignment_id = act.assignment_id
705 -- Add payroll id
706 AND as1.payroll_id = nvl(p_payroll_id,as1.payroll_id) /* 9489806 */
707 -- Commenting Code to Include Terminated Assignments
708 -- AND ppa.effective_date BETWEEN as1.effective_start_date
709 -- AND as1.effective_end_date
710 AND act.action_status IN ('C','S') -- 10229494
711 AND act.assignment_action_id = pai.locked_action_id
712 AND act1.assignment_action_id = pai.locking_action_id
713 AND act1.action_status IN ('C','S') -- 10229494
714 AND act1.payroll_action_id = appa2.payroll_action_id
715 AND appa2.action_type IN ('P','U')
716 AND appa2.effective_date BETWEEN l_canonical_start_date
717 AND l_canonical_end_date
718 -- Prepayments or Quickpay Prepayments
719 AND act.TAX_UNIT_ID = act1.TAX_UNIT_ID
720 AND act.TAX_UNIT_ID = p_legal_employer_id
721 ORDER BY as1.payroll_id, as1.person_id , act.assignment_id;
722
723 cursor csr_pay_periods(p_payroll_id NUMBER, p_payroll_period NUMBER)
724 is
725 select start_date, end_date from per_time_periods
726 where payroll_id = p_payroll_id
727 and time_period_id = p_payroll_period;
728
729 pp csr_pay_periods%rowtype;
730
731 Cursor cur_report_type IS
732 select
733 report_type
734 from
735 pay_payroll_actions
736 where payroll_action_id = p_payroll_action_id;
737
738 l_report_type VARCHAR2(100);
739
740 Cursor cur_term_date(p_start_date DATE, p_bg_id number, p_asg_id number)
741 IS
742 SELECT MAX( EFFECTIVE_END_DATE) EFFECTIVE_END_DATE
743 FROM per_all_assignments_f
744 WHERE
745 assignment_id = p_asg_id
746 AND EFFECTIVE_END_DATE > = p_start_date
747 AND assignment_status_type_id IN
748 (select assignment_status_type_id
749 from per_assignment_status_types
750 where per_system_status = 'ACTIVE_ASSIGN'
751 and active_flag = 'Y'
752 and (( legislation_code is null
753 and business_group_id is null)
754 OR (BUSINESS_GROUP_ID = p_bg_id)));
755
756 l_term_date DATE;
757
758
759 Cursor cur_period_type(p_payroll_id number)
760 IS
761 SELECT decode(PERIOD_TYPE
762 ,'Calendar Month','1'
763 ,'Bi-Week' ,'2'
764 ,'Week' ,'3'
765 ,'Lunar Month' ,'4') PERIOD_TYPE
766 from per_time_periods ptp
767 WHERE payroll_id = p_payroll_id
768 and rownum=1;
769
770 l_period_type VARCHAR2(50);
771 l_hourly_salaried VARCHAR2(100);
772
773 BEGIN
774 if(g_flag=0) then
775 -- fnd_file.put_line(fnd_file.log,'ASSIGNMENT_ACTION_CODE 1');
776
777 IF g_debug THEN
778 hr_utility.set_location(' Entering Procedure ASSIGNMENT_ACTION_CODE',60);
779 END IF;
780
781 PAY_DK_ARCHIVE_EHOL.GET_ALL_PARAMETERS(
782 p_payroll_action_id
783 ,g_business_group_id
784 ,g_legal_employer_id
785 ,g_effective_date
786 ,g_payroll_id
787 ,g_year
788 ,g_payroll_type
789 ,g_start_date
790 ,g_end_date
791 ,g_test_submission
792 ,g_company_terminating
793 ,g_msg_txt) ;
794
795 -- fnd_file.put_line(fnd_file.log,'ASSIGNMENT_ACTION_CODE 2');
796
797 OPEN cur_report_type;
798 FETCH cur_report_type INTO l_report_type;
799 CLOSE cur_report_type;
800
801 fnd_file.put_line(fnd_file.log,'l_report_type:'||l_report_type);
802
803 g_payroll_action_id :=p_payroll_action_id;
804
805 /*
806 open csr_pay_periods(g_payroll_id, g_payroll_period);
807 fetch csr_pay_periods into pp;
808 l_canonical_start_date := pp.start_date;
809 l_canonical_end_date := pp.end_date;
810 */
811 l_canonical_start_date := fnd_date.canonical_to_date(g_start_date);
812 l_canonical_end_date := fnd_date.canonical_to_date(g_end_date);
813
814 IF l_report_type = 'PYDKEHOLCA_ANN' THEN
815 l_canonical_start_date := to_date('01-01-'||g_year,'dd-mm-rrrr');
816 l_canonical_end_date := to_date('31-12-'||g_year,'dd-mm-rrrr');
817 END IF;
818
819
820 l_prepay_action_id := 0;
821 l_prev_person_id := 0;
822 l_prev_assg_id := 0;
823
824 -- fnd_file.put_line(fnd_file.log,'ASSIGNMENT_ACTION_CODE 3');
825
826 FOR rec_prepaid_assignments IN csr_prepaid_assignments_le(p_payroll_action_id
827 ,p_start_person
828 ,p_end_person
829 ,g_legal_employer_id
830 ,g_payroll_id
831 ,g_payroll_period
832 ,l_canonical_start_date
833 ,l_canonical_end_date)
834 LOOP
835 OPEN cur_term_date(l_canonical_start_date, g_business_group_id, rec_prepaid_assignments.assignment_id);
836 FETCH cur_term_date INTO l_term_date;
837 CLOSE cur_term_date;
838 fnd_file.put_line(fnd_file.log,'rec_prepaid_assignments.assignment_id:'||rec_prepaid_assignments.assignment_id);
839 fnd_file.put_line(fnd_file.log,'l_term_date:'||l_term_date);
840
841 l_period_type := NULL;
842 OPEN cur_period_type(rec_prepaid_assignments.payroll_id);
843 FETCH cur_period_type INTO l_period_type;
844 CLOSE cur_period_type;
845
846 fnd_file.put_line(fnd_file.log,'l_period_type:'||l_period_type);
847
848 l_hourly_salaried := NULL;
849 if rec_prepaid_assignments.HOURLY_SALARIED_CODE IS null then
850 if l_period_type=1 then
851 l_hourly_salaried :='S';
852 else
853 l_hourly_salaried := 'H';
854 end if;
855 fnd_file.put_line(fnd_file.log,'1-l_hourly_salaried:'||l_hourly_salaried);
856 ELSE
857 l_hourly_salaried := rec_prepaid_assignments.HOURLY_SALARIED_CODE;
858
859 fnd_file.put_line(fnd_file.log,'2-l_hourly_salaried:'||l_hourly_salaried);
860 end if;
861
862 IF l_report_type IN ('PYDKEHOLCA', 'PYDKEHOLCA_ANN') THEN
863 IF l_prepay_action_id <> rec_prepaid_assignments.prepaid_action_id
864 --AND l_prev_person_id <> rec_prepaid_assignments.person_id --14107465
865 AND l_prev_assg_id <> rec_prepaid_assignments.assignment_id THEN
866
867 IF (l_report_type = 'PYDKEHOLCA' and l_term_date <= l_canonical_end_date) --All Terminated
868
869 --Only Active Hourly(Annual Report)
870 OR (l_report_type = 'PYDKEHOLCA_ANN' and l_hourly_salaried ='H' and l_term_date > l_canonical_end_date)
871 THEN
872 SELECT pay_assignment_actions_s.NEXTVAL
873 INTO l_actid
874 FROM dual;
875
876 -- Create the archive assignment action
877 hr_nonrun_asact.insact(l_actid
878 ,rec_prepaid_assignments.assignment_id
879 ,p_payroll_action_id
880 ,p_chunk
881 ,NULL);
882
883 fnd_file.put_line(fnd_file.log,'ASSIGNMENT_ACTION_CODE 7');
884 END IF; --Report type
885
886 END IF;
887
888 ELSIF l_report_type = 'PYDKEHOLCA_MIA' THEN
889
890 --All active Hourly + (Terminated Hourly, Salaried)
891 IF
892 (l_report_type = 'PYDKEHOLCA_MIA'and l_hourly_salaried ='H' and l_term_date > l_canonical_end_date)
893 OR
894 (l_report_type = 'PYDKEHOLCA_MIA' and l_term_date <= l_canonical_end_date)
895 THEN
896
897 IF l_prepay_action_id <> rec_prepaid_assignments.prepaid_action_id
898 --AND l_prev_person_id <> rec_prepaid_assignments.person_id
899 AND l_prev_assg_id <> rec_prepaid_assignments.assignment_id
900 THEN
901 SELECT pay_assignment_actions_s.NEXTVAL
902 INTO l_actid
903 FROM dual;
904
905 -- Create the archive assignment action
906 hr_nonrun_asact.insact(l_actid
907 ,rec_prepaid_assignments.assignment_id
908 ,p_payroll_action_id
909 ,p_chunk
910 ,NULL);
911 fnd_file.put_line(fnd_file.log,'ASSIGNMENT_ACTION_CODE 8');
912 END IF;
913
914 END IF;
915
916 END IF;
917
918 l_prepay_action_id := rec_prepaid_assignments.prepaid_action_id;
919 l_prev_person_id := rec_prepaid_assignments.person_id;
920 l_prev_assg_id := rec_prepaid_assignments.assignment_id;
921
922 END LOOP;
923
924 IF g_debug THEN
925 hr_utility.set_location(' Leaving Procedure ASSIGNMENT_ACTION_CODE',70);
926 END IF;
927 end if; -- end g_flag condition.
928
929 EXCEPTION
930 WHEN others THEN
931 IF g_debug THEN
932 hr_utility.set_location('error raised assignment_action_code ',5);
933 END if;
934 RAISE;
935 END ASSIGNMENT_ACTION_CODE;
936
937
938 /* INITIALIZATION CODE */
939 PROCEDURE INITIALIZATION_CODE(p_payroll_action_id IN NUMBER)
940 IS
941
942 BEGIN
943 IF g_debug THEN
944 hr_utility.set_location(' Entering Procedure INITIALIZATION_CODE',80);
945 END IF;
946 -- fnd_file.put_line(fnd_file.log,'INITIALIZATION_CODE 1');
947 IF g_debug THEN
948 hr_utility.set_location(' Leaving Procedure INITIALIZATION_CODE',90);
949 END IF;
950
951 EXCEPTION
952 WHEN others THEN
953 IF g_debug THEN
954 hr_utility.set_location('error raised initialization code ',5);
955 END if;
956 RAISE;
957 END INITIALIZATION_CODE;
958
959 /* ARCHIVE CODE */
960 PROCEDURE ARCHIVE_CODE(p_assignment_action_id IN NUMBER
961 ,p_effective_date IN DATE)
962 IS
963 /* Cursor to retrieve Person Details */
964 CURSOR csr_get_person_details(p_asg_act_id NUMBER , p_asg_effective_date DATE ) IS
965 SELECT pap.national_identifier cpr , pap.person_id , pac.assignment_id, pap.full_name, to_char(pap.date_of_birth,'yyyymmdd') dob,
966 pap.first_name||' '||pap.middle_names||' '||pap.last_name pname, pap.sex, pap.start_date, pap.business_group_id,
967 assign.hourly_salaried_code HOURLY_SALARIED_CODE, assign.assignment_number, assign.organization_id, assign.primary_flag,
968 assign.location_id
969 FROM
970 pay_assignment_actions pac,
971 per_all_assignments_f assign,
972 per_all_people_f pap
973 WHERE pac.assignment_action_id = p_asg_act_id
974 AND assign.assignment_id = pac.assignment_id
975 AND assign.person_id = pap.person_id
976 AND pap.per_information_category = 'DK'
977 AND p_asg_effective_date BETWEEN assign.effective_start_date
978 AND assign.effective_end_date
979 AND p_asg_effective_date BETWEEN pap.effective_start_date
980 AND pap.effective_end_date;
981
982 rg_csr_get_person_details csr_get_person_details%rowtype;
983
984 CURSOR csr_assignments_dtl
985 ( p_payroll_id NUMBER
986 ,p_person_id NUMBER
987 ,p_le_id NUMBER
988 ,p_asg_effective_date DATE
989 ,p_assignment_id NUMBER
990 ) IS
991 SELECT distinct
992 paaf.assignment_id ASG_ID
993 ,ppf.payroll_name PAYROLL_NAME
994 ,ppf.payroll_id
995 ,paaf.assignment_number ASSIGNMENT_NUMBER
996 ,to_char(paaf.effective_start_date,'YYYYMMDD') ASG_START_DATE
997 ,paaf.effective_end_date ASG_END_DATE
998 ,substr(to_char(papf.national_identifier),1,instr(to_char(papf.national_identifier),'-')-1)||substr(to_char(papf.national_identifier),instr(to_char(papf.national_identifier),'-')+1) CPR_NO
999 ,scl.SEGMENT3 COND_OF_EMP
1000 ,scl.SEGMENT4 EMP_GRP
1001 ,scl.SEGMENT14 JOB_OCC_MKODE
1002 ,scl.SEGMENT15 JOB_STATUS_MKODE
1003 ,paaf.NORMAL_HOURS NORMAL_HOURS
1004 ,paaf.FREQUENCY FREQ
1005 ,scl.SEGMENT10 DEFAULT_WORK_PATT
1006 ,scl.SEGMENT11 HOURLY_ACCR_RATE
1007 ,scl.SEGMENT13 SAL_ALLOW_RATE
1008 ,decode(ppf.PERIOD_TYPE
1009 ,'Calendar Month','1'
1010 ,'Bi-Week' ,'2'
1011 ,'Week' ,'3'
1012 ,'Lunar Month' ,'4') PAYROLL_PERIOD
1013 ,scl.SEGMENT16 SAL_BASIS_MKODE
1014 ,scl.SEGMENT17 TIME_OFF_LIEU
1015 ,paaf.hourly_salaried_code HOURLY_SALARIED_CODE
1016 ,paaf.organization_id HR_ORG_ID
1017 ,paaf.location_id LOC_ID
1018 ,scl.SEGMENT21 MO_CODE
1019 ,scl.SEGMENT22 FO_CODE
1020 ,NVL(scl.SEGMENT23,'FH1') FH_CODE
1021 FROM
1022 per_all_people_f papf
1023 ,per_all_assignments_f paaf
1024 ,pay_payrolls_f ppf
1025 ,hr_soft_coding_keyflex scl
1026 WHERE paaf.person_id = p_person_id
1027 AND papf.PERSON_ID = paaf.PERSON_ID
1028 AND ppf.payroll_id = p_payroll_id
1029 AND paaf.payroll_id = ppf.payroll_id
1030 AND paaf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
1031 AND scl.enabled_flag = 'Y'
1032 AND paaf.assignment_id = p_assignment_id
1033 AND p_asg_effective_date BETWEEN paaf.effective_start_date AND paaf.effective_end_date
1034 AND papf.current_employee_flag = 'Y'
1035 AND scl.segment1 = to_char(p_le_id)
1036 ORDER BY asg_id, ASG_START_DATE;
1037
1038 rg_csr_assignments_dtl csr_assignments_dtl%rowtype;
1039
1040 CURSOR cur_get_hol_pay_perc(p_effective_date DATE, p_asg_id NUMBER) IS
1041 SELECT fnd_number.canonical_to_number(AEI_INFORMATION3)
1042 FROM PER_ASSIGNMENT_EXTRA_INFO
1043 WHERE
1044 ASSIGNMENT_ID = p_asg_id
1045 AND INFORMATION_TYPE = 'DK_EHOLC_HPAY_RATE'
1046 AND p_effective_date between fnd_date.canonical_to_date(AEI_INFORMATION1)
1047 AND NVL(fnd_date.canonical_to_date(AEI_INFORMATION2),to_date('31-12-4712','dd-mm-rrrr'));
1048
1049 l_hol_pay_percentage NUMBER;
1050
1051 /* Cursor to fetch the Legal Employer level Holiday Entitlement */
1052 CURSOR csr_get_hol_entit(p_le_id NUMBER, p_effective_date DATE, p_business_group_id NUMBER) IS
1053 SELECT hoi2.ORG_INFORMATION1 DEFAULT_WORK_PATT
1054 ,fnd_number.canonical_to_number(hoi2.ORG_INFORMATION3) HOURLY_ACCR_RATE
1055 ,fnd_number.canonical_to_number(hoi2.ORG_INFORMATION4) SAL_ALLOW_RATE
1056 FROM HR_ORGANIZATION_UNITS hou
1057 , HR_ORGANIZATION_INFORMATION hoi1
1058 , HR_ORGANIZATION_INFORMATION hoi2
1059 WHERE hou.business_group_id = p_business_group_id
1060 and hoi1.organization_id = hou.organization_id
1061 and hoi1.organization_id = p_le_id
1062 and hoi1.ORG_INFORMATION_CONTEXT='CLASS'
1063 and hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
1064 and hoi1.ORG_INFORMATION2 = 'Y'
1065 and hoi2.ORG_INFORMATION_CONTEXT='DK_HOLIDAY_ENTITLEMENT_INFO'
1066 and hoi2.organization_id = hoi1.organization_id
1067 and p_effective_date BETWEEN hou.DATE_FROM and nvl(hou.DATE_TO, p_effective_date);
1068
1069 rg_csr_get_hol_entit csr_get_hol_entit%rowtype;
1070
1071 /* Cursor to fetch the Legal Employer Details */
1072 CURSOR csr_get_le_details(p_le_id NUMBER, p_effective_date DATE, p_business_group_id NUMBER) IS
1073 SELECT hou.organization_id ORG_ID
1074 ,hoi2.ORG_INFORMATION1 CVR_NO
1075 ,hoi2.ORG_INFORMATION2 DS_WPCODE
1076 ,hoi2.ORG_INFORMATION6 PUNIT
1077 ,hou.name NAME
1078 ,SUBSTR(loc.ADDRESS_LINE_1,1,100) ad_line1
1079 ,SUBSTR(loc.ADDRESS_LINE_2,1,100) ad_line2
1080 ,SUBSTR(loc.ADDRESS_LINE_3,1,100) ad_line3
1081 ,loc.postal_code
1082 ,loc.TOWN_OR_CITY
1083 ,loc.COUNTRY
1084 ,loc.REGION_1
1085 ,loc.REGION_2
1086 ,loc.REGION_3
1087 ,loc.style
1088 FROM HR_ORGANIZATION_UNITS hou
1089 , HR_ORGANIZATION_INFORMATION hoi1
1090 , HR_ORGANIZATION_INFORMATION hoi2
1091 , HR_LOCATIONS loc
1092 WHERE hou.business_group_id = p_business_group_id
1093 and hou.organization_id = p_le_id
1094 and hoi1.organization_id = hou.organization_id
1095 and hou.location_id = loc.LOCATION_ID(+)
1096 and hoi1.ORG_INFORMATION_CONTEXT='CLASS'
1097 and hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
1098 and hoi1.ORG_INFORMATION2 = 'Y'
1099 and hoi2.ORG_INFORMATION_CONTEXT='DK_LEGAL_ENTITY_DETAILS'
1100 and hoi2.organization_id = hoi1.organization_id
1101 and p_effective_date BETWEEN hou.DATE_FROM and nvl(hou.DATE_TO, p_effective_date);
1102
1103 rg_csr_get_le_details csr_get_le_details%rowtype;
1104 l_le_postal_code VARCHAR2(100);
1105 l_le_country VARCHAR2(100);
1106
1107
1108 /* Cursor to fetch Person Address Details */
1109 CURSOR csr_get_per_address(p_person_id NUMBER, p_effective_date DATE, p_business_group_id NUMBER) IS
1110 SELECT
1111 pad.address_line1
1112 ,pad.address_line2
1113 ,pad.address_line3
1114 ,pad.postal_code
1115 ,pad.TOWN_OR_CITY
1116 ,pad.COUNTRY
1117 ,pad.REGION_1
1118 ,pad.REGION_2
1119 ,pad.REGION_3
1120 ,pad.style
1121 FROM per_addresses pad
1122 , per_all_people_f pap
1123 WHERE pad.person_id = pap.person_id
1124 AND pad.primary_flag = 'Y'
1125 AND pap.person_id = p_person_id
1126 AND pad.business_group_id = pap.business_group_id
1127 AND pad.business_group_id = p_business_group_id
1128 AND p_effective_date BETWEEN pap.effective_start_date AND pap.effective_end_date
1129 AND p_effective_date BETWEEN pad.date_from AND NVL(pad.date_to,to_date('31-12-4712','DD-MM-YYYY'));
1130
1131 rg_csr_get_per_address csr_get_per_address%rowtype;
1132 l_postal_code VARCHAR2(100);
1133 l_country VARCHAR2(100);
1134
1135 /* Cursor to fetch Assignment Bank Details */
1136 CURSOR csr_get_bank_details(p_assignment_id NUMBER, p_effective_date DATE, p_business_group_id NUMBER) IS
1137 select
1138 pea.SEGMENT1 Bank_Registration,
1139 pea.SEGMENT2 Bank_Branch,
1140 pea.SEGMENT3 Account_Number
1141 from
1142 pay_personal_payment_methods_f pppm
1143 ,pay_external_accounts pea
1144 where
1145 pppm.ASSIGNMENT_ID = p_assignment_id
1146 and pppm.BUSINESS_GROUP_ID = p_business_group_id
1147 and pppm.EXTERNAL_ACCOUNT_ID = pea.EXTERNAL_ACCOUNT_ID
1148 and p_effective_date between EFFECTIVE_START_DATE and nvl(EFFECTIVE_END_DATE,p_effective_date);
1149
1150 rg_csr_get_bank_details csr_get_bank_details%rowtype;
1151
1152
1153 /* Getting the latest hire date */
1154 /* For Bug 9011035 */
1155 CURSOR csr_latest_hire_date(pid per_all_people_f.person_id%type
1156 ,p_end_date DATE) IS
1157 SELECT MAX(date_start) lhd FROM per_periods_of_service
1158 WHERE person_id=pid
1159 AND date_start <= p_end_date;
1160
1161 rg_csr_latest_hire_date csr_latest_hire_date%rowtype;
1162
1163 /* Cursor to get the extra person info - Foreigner (yes/no) */
1164 CURSOR csr_get_extra_person_info(pid per_all_people_f.person_id%type) IS
1165 SELECT PEI_INFORMATION1 yes_no FROM per_people_extra_info
1166 WHERE person_id=pid
1167 AND information_type='DK_EINCOME_FORIEGN_IND';
1168
1169 rg_csr_get_extra_person_info csr_get_extra_person_info%rowtype;
1170
1171 /* Get the territory */
1172 CURSOR csr_get_territory(pid per_all_people_f.person_id%type) IS
1173 SELECT *
1174 FROM per_addresses_v
1175 WHERE person_id =pid
1176 and primary_flag='Y';
1177
1178 rg_csr_get_territory csr_get_territory%rowtype;
1179
1180 /* Cursor to get SE Number, PUCODE at HR Org level */
1181
1182 CURSOR csr_get_hr_org_info(
1183 bg_id hr_organization_units.business_group_id%type
1184 ,hr_org_id hr_organization_information.organization_id%type) IS
1185 SELECT o1.name ,hoi2.ORG_INFORMATION1 , hoi2.ORG_INFORMATION2, hoi2.ORG_INFORMATION3, hoi2.ORG_INFORMATION4,
1186 hoi2.ORG_INFORMATION5, hoi2.ORG_INFORMATION6, hoi2.ORG_INFORMATION13
1187 FROM hr_organization_units o1
1188 , hr_organization_information hoi1
1189 , hr_organization_information hoi2
1190 WHERE o1.business_group_id =bg_id
1191 AND hoi1.organization_id = o1.organization_id
1192 AND hoi1.organization_id = hr_org_id
1193 AND hoi1.org_information1 = 'HR_ORG'
1194 AND hoi1.org_information_context = 'CLASS'
1195 AND o1.organization_id =hoi2.organization_id
1196 AND hoi2.ORG_INFORMATION_CONTEXT='DK_EMPLOYMENT_DEFAULTS' ;
1197
1198 rg_csr_get_hr_org_info csr_get_hr_org_info%rowtype;
1199
1200
1201 /* Payroll Time period */
1202 CURSOR csr_pay_periods(p_payroll_id NUMBER, p_payroll_period NUMBER)
1203 IS
1204 SELECT ptp.start_date, ptp.end_date,
1205 default_dd_date,
1206 decode(PERIOD_TYPE
1207 ,'Calendar Month','1'
1208 ,'Bi-Week' ,'2'
1209 ,'Week' ,'3'
1210 ,'Lunar Month' ,'4') PAYROLL_PERIOD
1211 from per_time_periods ptp
1212 WHERE payroll_id = p_payroll_id
1213 AND time_period_id = p_payroll_period;
1214
1215 rg_csr_pay_periods csr_pay_periods%rowtype;
1216
1217 /* Cursor to get the details of the legal employer */
1218 Cursor csr_Legal_Emp_Details ( csr_v_legal_emp_id hr_organization_information.ORGANIZATION_ID%TYPE)
1219 IS
1220 SELECT o1.name ,hoi2.ORG_INFORMATION1 , hoi2.ORG_INFORMATION2, hoi2.ORG_INFORMATION3, hoi2.ORG_INFORMATION4, hoi2.ORG_INFORMATION5, hoi2.ORG_INFORMATION6, hoi2.ORG_INFORMATION13
1221 FROM hr_organization_units o1
1222 , hr_organization_information hoi1
1223 , hr_organization_information hoi2
1224 WHERE o1.business_group_id =g_business_group_id
1225 AND hoi1.organization_id = o1.organization_id
1226 AND hoi1.organization_id = csr_v_legal_emp_id
1227 AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
1228 AND hoi1.org_information_context = 'CLASS'
1229 AND o1.organization_id =hoi2.organization_id
1230 AND hoi2.ORG_INFORMATION_CONTEXT='DK_LEGAL_ENTITY_DETAILS' ;
1231
1232 rg_Legal_Emp_Details csr_Legal_Emp_Details%rowtype;
1233
1234 CURSOR csr_location_info (p_location_id hr_location_extra_info.location_id%TYPE) IS
1235 SELECT lei_information1
1236 FROM hr_location_extra_info
1237 WHERE location_id = p_location_id
1238 AND information_type='DK_LOCATION_INFO';
1239
1240 /* 9489806 */
1241 /* CURSOR csr_asg_payroll
1242 (c_canonical_start_date DATE,
1243 c_canonical_end_date DATE,
1244 c_assignment_id NUMBER
1245 ) IS
1246 SELECT --DISTINCT
1247 max(ppf.payroll_id) payroll_id,
1248 max(ptp.time_period_id) time_period_id
1249 --ppf.payroll_name,
1250 --ptp.period_name
1251
1252 FROM
1253 pay_payrolls_f ppf,
1254 per_assignments_f paf,
1255 per_time_periods ptp
1256 WHERE paf.payroll_id = ppf.payroll_id
1257 AND paf.assignment_id=c_assignment_id
1258 AND ppf.payroll_id=NVL(g_payroll_id,ppf.payroll_id)
1259 AND ppf.effective_start_date <= c_canonical_start_date and ppf.effective_end_date >= c_canonical_end_date
1260 AND ptp.payroll_id=ppf.payroll_id
1261 AND ptp.end_date BETWEEN c_canonical_start_date AND c_canonical_end_date
1262 AND ppf.business_group_id = g_business_group_id
1263 AND ptp.end_date between paf.effective_start_date and paf.effective_end_date
1264 and exists
1265 (select 1 from pay_payroll_actions ppa, pay_assignment_actions paa
1266 where ppa.payroll_action_id = paa.payroll_action_id
1267 and ppa.action_type IN ('R','Q')
1268 AND ppa.effective_date between c_canonical_start_date and c_canonical_end_date
1269 AND paa.assignment_id = paf.assignment_id
1270 AND paa.assignment_id = c_assignment_id
1271 AND ppa.time_period_id = ptp.time_period_id
1272 );
1273 */
1274 rg_csr_location_info csr_location_info%ROWTYPE;
1275
1276
1277 --
1278 l_select_str VARCHAR2(200) := ' SELECT max(ppa.payroll_id) payroll_id, max(ppa.time_period_id) time_period_id ';
1279 l_where_clause VARCHAR2(100) := ' ';
1280 l_select_qry VARCHAR2(3000);
1281
1282 TYPE csr_asg_payroll_ref IS REF CURSOR;
1283 csr_asg_payroll csr_asg_payroll_ref;
1284 --
1285
1286
1287
1288 /* Cursor to retrieve Element - Employee ATP*/
1289 CURSOR csr_get_atp_table_value(p_assignment_id NUMBER , p_start_date DATE , p_end_date DATE ) IS
1290 SELECT eev1.screen_entry_value screen_entry_value
1291 FROM per_all_assignments_f asg1
1292 ,per_all_people_f per
1293 ,pay_element_links_f el
1294 ,pay_element_types_f et
1295 ,pay_input_values_f iv1
1296 ,pay_element_entries_f ee
1297 ,pay_element_entry_values_f eev1
1298 WHERE asg1.assignment_id = p_assignment_id
1299 AND per.person_id = asg1.person_id
1300 AND et.element_name = 'Employee ATP'
1301 AND et.legislation_code = 'DK'
1302 AND iv1.element_type_id = et.element_type_id
1303 AND iv1.name = 'ATP Table'
1304 AND el.business_group_id = per.business_group_id
1305 AND el.element_type_id = et.element_type_id
1306 AND ee.element_link_id = el.element_link_id
1307 AND ee.assignment_id = asg1.assignment_id
1308 AND eev1.element_entry_id = ee.element_entry_id
1309 AND eev1.input_value_id = iv1.input_value_id
1310 AND asg1.effective_end_date >= p_start_date
1311 AND asg1.effective_start_date <= p_end_date
1312 AND per.effective_end_date >= p_start_date
1313 AND per.effective_start_date <= p_end_date
1314 AND ee.effective_end_date >= p_start_date
1315 AND ee.effective_start_date <= p_end_date
1316 AND ((eev1.effective_start_date <= p_start_date
1317 AND eev1.effective_end_date >= p_start_date )
1318 OR (eev1.effective_start_date BETWEEN p_start_date AND p_end_date
1319 AND eev1.effective_end_date >= p_end_date ));
1320
1321 rg_csr_get_atp_table_value csr_get_atp_table_value%rowtype;
1322
1323 /* Cursor to retrieve Element - Tax Card*/
1324 CURSOR csr_get_tax_card_details(p_assignment_id NUMBER , p_start_date DATE , p_end_date DATE ) IS
1325 SELECT ee.effective_start_date, eev1.screen_entry_value
1326 FROM per_all_assignments_f asg1
1327 ,per_all_people_f per
1328 ,pay_element_links_f el
1329 ,pay_element_types_f et
1330 ,pay_input_values_f iv1
1331 ,pay_element_entries_f ee
1332 ,pay_element_entry_values_f eev1
1333 WHERE asg1.assignment_id = p_assignment_id
1334 AND per.person_id = asg1.person_id
1335 AND et.element_name = 'Tax Card'
1336 AND et.legislation_code = 'DK'
1337 AND iv1.element_type_id = et.element_type_id
1338 AND iv1.name = 'Tax Card Type'
1339 AND el.business_group_id = per.business_group_id
1340 AND el.element_type_id = et.element_type_id
1341 AND ee.element_link_id = el.element_link_id
1342 AND ee.assignment_id = asg1.assignment_id
1343 AND eev1.element_entry_id = ee.element_entry_id
1344 AND eev1.input_value_id = iv1.input_value_id
1345 AND asg1.effective_end_date >= p_start_date
1346 AND asg1.effective_start_date <= p_end_date
1347 AND per.effective_end_date >= p_start_date
1348 AND per.effective_start_date <= p_end_date
1349 AND ee.effective_end_date >= p_start_date
1350 AND ee.effective_start_date <= p_end_date
1351 AND ((eev1.effective_start_date <= p_start_date
1352 AND eev1.effective_end_date >= p_start_date )
1353 OR (eev1.effective_start_date BETWEEN p_start_date AND p_end_date
1354 AND eev1.effective_end_date >= p_end_date ));
1355
1356 rg_csr_get_tax_card_details csr_get_tax_card_details%rowtype;
1357
1358 /* Cursor to know the terminator in payroll period */
1359 CURSOR csr_asg_terminator
1360 (p_asg_act_id NUMBER
1361 ,p_start_date DATE
1362 ,p_end_date DATE
1363 ,p_business_group_id NUMBER) IS
1364 SELECT MAX( EFFECTIVE_END_DATE) EFFECTIVE_END_DATE
1365 FROM per_all_assignments_f paa
1366 ,pay_assignment_actions pac
1367 WHERE pac.assignment_action_id = p_asg_act_id
1368 AND paa.assignment_id = pac.assignment_id
1369 -- AND paa.EFFECTIVE_START_DATE <= p_end_date 10322958
1370 AND paa.EFFECTIVE_END_DATE > = p_start_date
1371 AND assignment_status_type_id IN
1372 (select assignment_status_type_id
1373 from per_assignment_status_types
1374 where per_system_status = 'ACTIVE_ASSIGN'
1375 and active_flag = 'Y'
1376 and (( legislation_code is null
1377 and business_group_id is null)
1378 OR (BUSINESS_GROUP_ID = p_business_group_id)));
1379
1380 rg_csr_asg_terminator csr_asg_terminator%rowtype;
1381
1382 CURSOR csr_Get_Defined_Balance_Id(csr_v_Balance_Name FF_DATABASE_ITEMS.USER_NAME%TYPE) IS
1383 SELECT ue.creator_id
1384 FROM ff_user_entities ue, ff_database_items di
1385 WHERE di.user_name = csr_v_Balance_Name
1386 AND ue.user_entity_id = di.user_entity_id
1387 AND ue.legislation_code = 'DK'
1388 AND ue.business_group_id is NULL
1389 AND ue.creator_type = 'B';
1390 lr_Get_Defined_Balance_Id csr_Get_Defined_Balance_Id%rowtype;
1391
1392 CURSOR csr_asg_action_id(assg_id NUMBER, pid NUMBER, tid NUMBER, le_id NUMBER) IS
1393 select MAX(pac.ASSIGNMENT_ACTION_ID) id
1394 from pay_assignment_actions pac, pay_payroll_actions ppa
1395 where ppa.action_type IN ('R','Q')
1396 and pac.payroll_action_id = ppa.payroll_action_id
1397 and pac.assignment_id= assg_id
1398 and ppa.payroll_id=pid
1399 and ppa.time_period_id=tid
1400 and pac.tax_unit_id=le_id;
1401 rg_csr_asg_action_id csr_asg_action_id%rowtype;
1402
1403 --Bug 8522052 record 6003
1404 CURSOR csr_asg_extra_info (p_assgt_id NUMBER) IS
1405 SELECT *
1406 FROM per_assignment_extra_info
1407 WHERE information_type = 'DK_EINCOME_INFO'
1408 AND assignment_id = p_assgt_id;
1409
1410 rg_csr_asg_extra_info csr_asg_extra_info%ROWTYPE;
1411
1412 CURSOR csr_prepaid_actions_present(p_payroll_action_id NUMBER,
1413 p_legal_employer_id NUMBER,
1414 p_payroll_id NUMBER,
1415 p_payroll_period NUMBER,
1416 l_canonical_start_date DATE,
1417 l_canonical_end_date DATE,
1418 l_assignment_id NUMBER)
1419 IS
1420 SELECT 1
1421 FROM pay_payroll_actions ppa
1422 ,pay_payroll_actions appa
1423 ,pay_payroll_actions appa2
1424 ,pay_assignment_actions act
1425 ,pay_assignment_actions act1
1426 ,pay_action_interlocks pai
1427 ,per_all_assignments_f as1
1428
1429 WHERE ppa.payroll_action_id = p_payroll_action_id
1430 AND appa.effective_date BETWEEN l_canonical_start_date
1431 AND l_canonical_end_date
1432 AND appa.action_type IN ('R','Q')
1433 AND act.payroll_action_id = appa.payroll_action_id
1434 AND appa.time_period_id = p_payroll_period
1435 AND appa.effective_date between l_canonical_start_date and l_canonical_end_date
1436 AND act.source_action_id IS NULL -- Master Action
1437 AND as1.assignment_id = act.assignment_id
1438 AND as1.payroll_id = p_payroll_id
1439 AND act.action_status IN ('C','S') -- 10229494
1440 AND act.assignment_action_id = pai.locked_action_id
1441 AND act1.assignment_action_id = pai.locking_action_id
1442 AND act1.action_status IN ('C','S') -- 10229494
1443 AND act1.payroll_action_id = appa2.payroll_action_id
1444 AND appa2.action_type IN ('P','U')
1445 AND appa2.effective_date BETWEEN l_canonical_start_date
1446 AND l_canonical_end_date
1447 -- Prepayments or Quickpay Prepayments
1448 AND act.TAX_UNIT_ID = act1.TAX_UNIT_ID
1449 AND act.TAX_UNIT_ID = p_legal_employer_id
1450 AND as1.assignment_id = l_assignment_id;
1451
1452 --13463885
1453 CURSOR csr_use_hol_card(p_org_id NUMBER) is
1454 SELECT NVL(org_information5,'N')
1455 FROM hr_organization_information
1456 WHERE organization_id = p_org_id
1457 and org_information_context = 'DK_HOLIDAY_ENTITLEMENT_INFO';
1458
1459 CURSOR get_value_from_ddf(p_payroll_id NUMBER , p_date_earned DATE) IS
1460 SELECT PRL_INFORMATION1, Payroll_name
1461 FROM pay_payrolls_f ppf
1462 WHERE PAYROLL_ID = p_payroll_id
1463 AND p_date_earned BETWEEN ppf.EFFECTIVE_START_DATE AND ppf.EFFECTIVE_END_DATE;
1464
1465 CURSOR cur_max_asg_act_id(p_assignment_id NUMBER, p_start_date DATE, p_end_date DATE) IS
1466 SELECT fnd_number.canonical_to_number(substr(max(lpad(paa.action_sequence,15,'0')||paa.assignment_action_id),16)) asg_action_id
1467 FROM pay_assignment_actions paa,
1468 pay_payroll_actions ppa
1469 WHERE ppa.payroll_action_id = paa.payroll_action_id
1470 and paa.assignment_id = p_assignment_id
1471 and ppa.action_type in ('R','Q','I','B','V')
1472 and paa.source_action_id is null
1473 AND ppa.effective_date between p_start_date AND p_end_date;
1474
1475 CURSOR cur_table_val_exist_400(p_payroll_action_id number) IS
1476 SELECT 'Y'
1477 FROM pay_payroll_actions ppa
1478 , pay_user_tables put
1479 , pay_user_columns puc
1480 , pay_user_column_instances_f puci
1481 , pay_user_rows_f pur
1482 , pay_user_columns puc1
1483 , pay_user_column_instances_f puci1
1484 , pay_user_rows_f pur1
1485 WHERE ppa.payroll_action_id = p_payroll_action_id
1486 AND put.user_table_name = 'DK_PBS_DATA'
1487 AND put.LEGISLATION_CODE ='DK'
1488 AND puc.user_table_id = put.user_table_id
1489 AND puc.user_column_name = 'Information Type'
1490 AND puci.user_column_id = puc.user_column_id
1491 AND ppa.effective_date between puci.effective_start_date and puci.effective_end_date
1492 AND pur.user_row_id = puci.user_row_id
1493 AND ppa.effective_date between pur.effective_start_date and pur.effective_end_date
1494 and puc1.user_table_id = put.user_table_id
1495 and puc1.user_column_name = 'PBS Number'
1496 and puci1.user_column_id = puc1.user_column_id
1497 and puci1.business_group_id =ppa.business_group_id
1498 and ppa.effective_date between puci1.effective_start_date and puci1.effective_end_date
1499 and pur1.user_row_id = puci1.user_row_id
1500 and pur1.user_row_id = pur.user_row_id
1501 and ppa.effective_date between pur1.effective_start_date and pur1.effective_end_date
1502 and puci.value = '400';
1503
1504 l_usr_tbl_exist_400 VARCHAR2(1) := 'N';
1505
1506 /*Legal Employer DA Office Codes */
1507 Cursor csr_Legal_Emp_DA_Codes ( p_legal_emp_id hr_organization_information.ORGANIZATION_ID%TYPE
1508 ,p_business_group_id NUMBER)
1509 IS
1510 SELECT
1511 hoi2.ORG_INFORMATION2,
1512 hoi2.ORG_INFORMATION3,
1513 hoi2.ORG_INFORMATION4,
1514 hoi2.ORG_INFORMATION5,
1515 hoi2.ORG_INFORMATION6
1516 FROM hr_organization_units o1
1517 , hr_organization_information hoi1
1518 , hr_organization_information hoi2
1519 WHERE o1.business_group_id = p_business_group_id
1520 AND hoi1.organization_id = o1.organization_id
1521 AND hoi1.organization_id = p_legal_emp_id
1522 AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
1523 AND hoi1.org_information_context = 'CLASS'
1524 AND o1.organization_id =hoi2.organization_id
1525 AND hoi2.ORG_INFORMATION_CONTEXT='DK_DA_OFFICE_CODE';
1526
1527 rg_Legal_Emp_DA_Codes csr_Legal_Emp_DA_Codes%rowtype;
1528
1529
1530 --13463885
1531 l_5d_6d VARCHAR2(10);
1532 l_present_flag NUMBER:=0;
1533 l_assignment_action_id NUMBER;
1534 l_payroll_action_id NUMBER;
1535 l_ass_id NUMBER; /* 9489806 */
1536 l_payroll_id NUMBER;
1537 l_employment_type VARCHAR2(10):='00';
1538 l_green_land_code VARCHAR2(10):='000'; /* 8847591 */
1539 l_period_id NUMBER;
1540 l_action_context_id NUMBER;
1541 l_flag NUMBER := 0;
1542 l_action_info_id NUMBER;
1543 l_ovn NUMBER;
1544 l_dob VARCHAR2(8);
1545 l_sex VARCHAR2(10);
1546 l_tax_card_type VARCHAR2(5);
1547 l_payment_type VARCHAR2(5);
1548 l_source_text VARCHAR2(10);
1549 l_source_text2 VARCHAR2(10);
1550 l_org_type VARCHAR2(5);
1551 l_country_code varchar2(50);
1552 l_age_category varchar2(1);
1553 l_hourly_salaried varchar2(1):=null;
1554 l_start_date DATE;
1555 l_end_date DATE;
1556 l_assignment NUMBER:=null;
1557 l_primary VARCHAR2(2):=null;
1558 l_bal_date DATE;
1559 l_assignment_id NUMBER:=0;
1560 l_yes_no VARCHAR2(10);
1561 l_style VARCHAR2(30);
1562 l_town_or_city VARCHAR2(30);
1563 l_canonical_start_date DATE; /* 9489806 */
1564 l_canonical_end_date DATE;
1565
1566 l_sp_bonus NUMBER(12,2);
1567 l_free_phone NUMBER(12,2);
1568 l_multimedia NUMBER(12,2); -- bug# 9079593 fix
1569 l_trivial_matter NUMBER(12,2); -- bug# 9169583 fix
1570 l_mileage NUMBER(12,2);
1571 -- l_b_income NUMBER(12,2);
1572 l_total_atp NUMBER(12,2);
1573 l_employer_atp NUMBER(12,2);
1574 l_board_lodge NUMBER(12,2);
1575 l_car NUMBER(12,2);
1576 /* 8861878 */
1577 --
1578 l_non_taxable_travel NUMBER(12,2);
1579 l_b_income_amb NUMBER(12,2);
1580 l_b_income_non_amb NUMBER(12,2);
1581 l_a_non_amb_income NUMBER(12,2);
1582 l_pension_sev_pay NUMBER(12,2);
1583 l_physical_gift_sev_pay NUMBER(12,2);
1584 --
1585 l_car_adj NUMBER(12,2);
1586 l_amb_pay_adj NUMBER(12,2);
1587 l_emp_atp_adj NUMBER(12,2);
1588 l_sp_bonus_adj NUMBER(12,2);
1589 l_free_phone_adj NUMBER(12,2);
1590 l_multimedia_adj NUMBER(12,2); -- bug# 9079593 fix
1591 l_trivial_matter_adj NUMBER(12,2); -- bug# 9169583 fix
1592 l_mileage_adj NUMBER(12,2);
1593 l_empr_atp_adj NUMBER(12,2);
1594 l_board_lodge_adj NUMBER(12,2);
1595 l_a_income_adj NUMBER(12,2);
1596 -- l_b_income_adj NUMBER(12,2);
1597 l_hourly_holiday_pay_adj NUMBER(12,2);
1598 l_monthly_holiday_pay_adj NUMBER(12,2);
1599 /* 8861878 */
1600 --
1601 l_non_taxable_travel_adj NUMBER(12,2);
1602 l_b_income_amb_adj NUMBER(12,2);
1603 l_b_income_non_amb_adj NUMBER(12,2);
1604 l_a_income_non_amb_adj NUMBER(12,2);
1605 l_pension_sev_pay_adj NUMBER(12,2);
1606 l_physical_gift_sev_pay_adj NUMBER(12,2);
1607 --
1608 l_tot_free_res_pay_adj NUMBER(12,2);
1609 l_tot_free_res_pay NUMBER(12,2); -- bug 12943072
1610
1611 l_total_amb NUMBER(12,2);
1612 l_hol_amb NUMBER(12,2);
1613 l_hol_amb_rep NUMBER(12,2);
1614 l_emp_amb NUMBER(12,2);
1615 l_tax NUMBER(12,2);
1616 l_emp_tax NUMBER(12,2);
1617 l_holiday_tax NUMBER(12,2);
1618 l_holiday_tax_pay NUMBER(12,2);
1619 l_a_income NUMBER(12,2);
1620
1621 /* 9945754-1 */
1622 --
1623 l_hourly_hol_income NUMBER(12,2);
1624 l_hourly_hol_income_adj NUMBER(12,2);
1625 l_sal_hol_income NUMBER(12,2);
1626 --
1627
1628 l_monthly_holiday_pay NUMBER(12,2);
1629 l_hourly_holiday_pay NUMBER(12,2);
1630 l_gross_income NUMBER(12,2);
1631 l_emp_atp NUMBER(12,2);
1632 l_amb_pay NUMBER(12,2);
1633 l_amb NUMBER(12,2);
1634 l_cvr_number hr_organization_information.org_information1%TYPE ;
1635 l_cpr_number VARCHAR2(15);
1636 l_pu_code hr_organization_information.ORG_INFORMATION6%type;
1637 l_total_atp_hours NUMBER;
1638 l_se_no hr_organization_information.ORG_INFORMATION5%type; -- bug 10367494
1639 l_company_terminating varchar2(1); -- bug 10367494
1640 l_flag1 NUMBER :=0 ;
1641 l_atp NUMBER;
1642 L_6005_SIGN VARCHAR2(1);
1643 l_bg_id NUMBER;
1644 l_org_id NUMBER;
1645 l_hd VARCHAR2(100);
1646 l_loc_id NUMBER := NULL;
1647 l_pension NUMBER := 0;
1648 l_g_dage_pay NUMBER :=0; -- 10269552
1649 l_corr_flag varchar(2) :=NULL; -- 12944028
1650
1651 --13463885
1652 l_no_of_hol_6005_0203 NUMBER(12,2);
1653
1654 l_net_hol_pay_0202 NUMBER(12,2);
1655 l_hol_pay_0202 NUMBER(12,2);
1656 l_hol_pay_0202_tax NUMBER(12,2);
1657 l_hol_pay_0202_amb NUMBER(12,2);
1658 --l_hol_pay_0202_sp NUMBER(12,2);
1659
1660 l_use_holiday_card VARCHAR2(10) := 'N';
1661 l_uhc_payroll VARCHAR2(10);
1662 l_pyrl_name pay_payrolls_f.Payroll_name%type;
1663
1664 l_sal_hol_acr_amt NUMBER(12,2);
1665 l_sal_hol_acr_tax NUMBER(12,2);
1666 l_sal_hol_acr_amb NUMBER(12,2);
1667 l_sal_hol_acr_days NUMBER(12,2);
1668 l_net_hol_pay_6102_1 NUMBER(12,2);
1669 l_no_of_hol_6102_1 NUMBER(12,2);
1670
1671 l_sal_hol_cur_ent_amt NUMBER(12,2);
1672 l_sal_hol_cur_ent_tax NUMBER(12,2);
1673 l_sal_hol_cur_ent_amb NUMBER(12,2);
1674 l_sal_hol_cur_ent_days NUMBER(12,2);
1675 l_net_hol_pay_6102_2 NUMBER(12,2);
1676 l_no_of_hol_6102_2 NUMBER(12,2);
1677
1678 l_sal_hol_nxt_ent_amt NUMBER(12,2);
1679 l_sal_hol_nxt_ent_tax NUMBER(12,2);
1680 l_sal_hol_nxt_ent_amb NUMBER(12,2);
1681 l_sal_hol_nxt_ent_days NUMBER(12,2);
1682 l_net_hol_pay_6102_3 NUMBER(12,2);
1683 l_no_of_hol_6102_3 NUMBER(12,2);
1684
1685 l_sign_net_hol_pay_6102_1 VARCHAR2(1);
1686 l_sign_net_hol_pay_6102_2 VARCHAR2(1);
1687 l_sign_net_hol_pay_6102_3 VARCHAR2(1);
1688
1689 l_accr_yr VARCHAR2(10);
1690 l_last_yr VARCHAR2(10);
1691 l_curr_entit_yr VARCHAR2(10);
1692 l_nxt_entit_yr VARCHAR2(10);
1693
1694 l_max_asg_act_id NUMBER;
1695
1696 --
1697 l_Holidayable_amount_1 NUMBER(12,2);
1698 l_holiday_pay_1 NUMBER(12,2);
1699 l_hol_pay_per_holiday_1 NUMBER(12,2);
1700 l_tot_holiday_pay_1 NUMBER(12,2);
1701 l_taxable_hol_pay_1 NUMBER(12,2);
1702 l_profit_free_ch_ac_1 NUMBER(12,2);
1703 l_net_hol_pay_1 NUMBER(12,2);
1704 l_hol_pay_sick_1 NUMBER(12,2);
1705
1706 l_Holidayable_amount_2 NUMBER(12,2);
1707 l_holiday_pay_2 NUMBER(12,2);
1708 l_hol_pay_per_holiday_2 NUMBER(12,2);
1709 l_tot_holiday_pay_2 NUMBER(12,2);
1710 l_taxable_hol_pay_2 NUMBER(12,2);
1711 l_profit_free_ch_ac_2 NUMBER(12,2);
1712 l_net_hol_pay_2 NUMBER(12,2);
1713 l_hol_pay_sick_2 NUMBER(12,2);
1714
1715 l_Holidayable_amount_3 NUMBER(12,2);
1716 l_holiday_pay_3 NUMBER(12,2);
1717 l_hol_pay_per_holiday_3 NUMBER(12,2);
1718 l_tot_holiday_pay_3 NUMBER(12,2);
1719 l_taxable_hol_pay_3 NUMBER(12,2);
1720 l_profit_free_ch_ac_3 NUMBER(12,2);
1721 l_net_hol_pay_3 NUMBER(12,2);
1722 l_hol_pay_sick_3 NUMBER(12,2);
1723
1724 l_Holidayable_amount_hr NUMBER(12,2);
1725 l_holiday_pay_hr NUMBER(12,2);
1726 l_hol_pay_per_holiday_hr NUMBER(12,2);
1727 l_tot_holiday_pay_hr NUMBER(12,2);
1728 l_taxable_hol_pay_hr NUMBER(12,2);
1729 l_profit_free_ch_ac_hr NUMBER(12,2);
1730 l_net_hol_pay_hr NUMBER(12,2);
1731 l_hol_pay_sick_hr NUMBER(12,2);
1732 l_hol_tax_hr NUMBER(12,2);
1733 l_hol_amb_hr NUMBER(12,2);
1734 l_tot_no_holidays_hr NUMBER(12,2);
1735
1736 l_spl_hol_uni_agr_net_acc NUMBER(12,2);
1737 l_spl_hol_uni_agr_gross_acc NUMBER(12,2);
1738 l_pro_free_ch_ac_net_acc NUMBER(12,2);
1739 l_pro_free_ch_ac_gross_acc NUMBER(12,2);
1740 l_hol_acc_days NUMBER(12,2);
1741 l_hol_acc_amt NUMBER(12,2);
1742
1743 l_spl_hol_uni_agr_net_pmt NUMBER(12,2);
1744 l_spl_hol_uni_agr_gross_pmt NUMBER(12,2);
1745 l_pro_free_ch_ac_net_pmt NUMBER(12,2);
1746 l_pro_free_ch_ac_gross_pmt NUMBER(12,2);
1747 l_hol_days_paid NUMBER(12,2);
1748 l_hol_pay_paid NUMBER(12,2);
1749
1750 l_hol_days_paid_hr NUMBER(12,2);
1751 l_hol_pay_paid_hr NUMBER(12,2);
1752 l_hrly_hol_pay_hr NUMBER(12,2);
1753 l_hrly_hol_days_hr NUMBER(12,2);
1754
1755 l_hol_days_paid_1 NUMBER(12,2);
1756 l_hol_pay_paid_1 NUMBER(12,2);
1757 l_sal_hol_pay_1 NUMBER(12,2);
1758 l_sal_hol_days_1 NUMBER(12,2);
1759 --
1760 --13463885
1761
1762 Cursor cur_report_type(p_payroll_action_id NUMBER) IS
1763 select
1764 report_type
1765 from
1766 pay_payroll_actions
1767 where payroll_action_id = p_payroll_action_id;
1768
1769 l_report_type VARCHAR2(100);
1770
1771
1772 BEGIN
1773
1774 -- fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 1');
1775 /*Initializing all balance variables*/
1776 l_sp_bonus :=0;
1777 l_free_phone :=0;
1778 l_multimedia := 0; -- bug# 9079593 fix
1779 l_trivial_matter :=0; -- bug# 9169583 fix
1780 l_mileage :=0;
1781 -- l_b_income :=0;
1782 l_total_atp :=0;
1783 l_employer_atp :=0;
1784 l_board_lodge :=0;
1785 l_car :=0;
1786 l_car_adj := 0;
1787 l_amb_pay_adj := 0;
1788 l_emp_atp_adj := 0;
1789 l_sp_bonus_adj := 0;
1790 l_free_phone_adj := 0;
1791 l_multimedia_adj := 0; -- bug# 9079593 fix
1792 l_trivial_matter_adj := 0; -- bug# 9169583 fix
1793 l_mileage_adj := 0;
1794 l_empr_atp_adj := 0;
1795 l_board_lodge_adj := 0;
1796 l_a_income_adj := 0;
1797 -- l_b_income_adj := 0;
1798 l_hourly_holiday_pay_adj := 0;
1799 l_monthly_holiday_pay_adj := 0;
1800 l_total_amb :=0;
1801 l_hol_amb :=0;
1802 l_emp_amb :=0;
1803 l_tax :=0;
1804 l_emp_tax :=0;
1805 l_holiday_tax :=0;
1806 l_a_income :=0;
1807
1808 /* 9945754-1 */
1809 --
1810 l_hourly_hol_income :=0;
1811 l_hourly_hol_income_adj :=0;
1812 l_sal_hol_income :=0;
1813 --
1814
1815 l_monthly_holiday_pay :=0;
1816 l_hourly_holiday_pay :=0;
1817 l_gross_income :=0;
1818 l_emp_atp :=0;
1819 l_amb_pay :=0;
1820 l_amb :=0;
1821 l_bg_id :=0;
1822 l_org_id :=0;
1823 l_pension := 0;
1824 l_g_dage_pay :=0; -- 10269552
1825
1826 BEGIN
1827
1828 SELECT payroll_action_id, assignment_id
1829 INTO l_payroll_action_id,l_ass_id /* 9489806 */
1830 FROM pay_assignment_actions
1831 WHERE assignment_action_id=p_assignment_action_id;
1832 END;
1833
1834 IF g_debug THEN
1835 hr_utility.set_location(' Entering Procedure ARCHIVE_CODE',380);
1836 END IF;
1837
1838 /* Fetching report parameters */
1839 PAY_DK_ARCHIVE_EHOL.GET_ALL_PARAMETERS(
1840 l_payroll_action_id
1841 ,g_business_group_id
1842 ,g_legal_employer_id
1843 ,g_effective_date
1844 ,g_payroll_id
1845 ,g_year
1846 ,g_payroll_type
1847 ,g_start_date
1848 ,g_end_date
1849 ,g_test_submission
1850 ,g_company_terminating
1851 ,g_msg_txt) ;
1852
1853 l_canonical_start_date := fnd_date.canonical_to_date(g_start_date);
1854 l_canonical_end_date := fnd_date.canonical_to_date(g_end_date);
1855
1856
1857 OPEN cur_report_type(l_payroll_action_id);
1858 FETCH cur_report_type INTO l_report_type;
1859 CLOSE cur_report_type;
1860
1861 fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 1: l_report_type :'|| l_report_type);
1862
1863 IF l_report_type = 'PYDKEHOLCA_ANN' THEN
1864 l_canonical_start_date := to_date('01-01-'||g_year,'dd-mm-rrrr');
1865 l_canonical_end_date := to_date('31-12-'||g_year,'dd-mm-rrrr');
1866 END IF;
1867
1868 --13463885
1869 OPEN csr_use_hol_card(g_legal_employer_id);
1870 FETCH csr_use_hol_card INTO l_use_holiday_card;
1871 CLOSE csr_use_hol_card;
1872
1873 fnd_file.put_line(fnd_file.log,'l_use_holiday_card:'||l_use_holiday_card);
1874
1875 OPEN cur_table_val_exist_400(l_payroll_action_id);
1876 FETCH cur_table_val_exist_400 INTO l_usr_tbl_exist_400;
1877 CLOSE cur_table_val_exist_400;
1878
1879 fnd_file.put_line(fnd_file.log,'l_usr_tbl_exist_400:'||l_usr_tbl_exist_400);
1880 --13463885
1881
1882 OPEN csr_get_le_details(g_legal_employer_id , l_canonical_end_date, g_business_group_id);
1883 FETCH csr_get_le_details into rg_csr_get_le_details;
1884 CLOSE csr_get_le_details;
1885
1886 fnd_file.put_line(fnd_file.log,'Fetched csr_get_le_details');
1887
1888 --if(g_flag=0) then
1889 --if(g_company_terminating='N') then
1890
1891 fnd_file.put_line(fnd_file.log,'Fetched report parameters');
1892
1893 hr_utility.set_location('ARCHIVE_CODE: l_assignment_id '|| l_ass_id,380);
1894 hr_utility.set_location('ARCHIVE_CODE: Before For loop csr_asg_payroll ',380);
1895
1896 --
1897 IF g_payroll_id IS NULL THEN
1898 l_where_clause := ' and 1=1 ';
1899 ELSE
1900 l_where_clause := ' AND ppa.payroll_id= '||g_payroll_id ;
1901 END IF;
1902
1903 IF l_report_type = 'PYDKEHOLCA_MIA'
1904 THEN
1905 l_select_str := 'SELECT DISTINCT ppa.payroll_id, ppa.time_period_id ';
1906 END IF;
1907
1908 /*
1909 l_select_qry := l_select_str
1910 ||' FROM
1911 pay_payrolls_f ppf,
1912 per_assignments_f paf,
1913 per_time_periods ptp
1914 WHERE paf.payroll_id = ppf.payroll_id
1915 AND paf.assignment_id= ' || l_ass_id
1916 || l_where_clause
1917 ||' AND ppf.effective_start_date <= ' ||''''|| l_canonical_start_date ||''''|| ' and ppf.effective_end_date >= ' ||''''|| l_canonical_end_date ||''''
1918 ||' AND ptp.payroll_id=ppf.payroll_id
1919 AND ('||''''|| l_canonical_start_date ||''''|| ' BETWEEN ptp.start_date and ptp.end_date OR '||''''|| l_canonical_end_date ||''''|| ' BETWEEN
1920 ptp.start_date and ptp.end_date)'
1921 ||' AND ppf.business_group_id = ' || g_business_group_id ||
1922 ' AND (ptp.end_date between paf.effective_start_date and paf.effective_end_date OR ptp.start_date between paf.effective_start_date and paf.effective_end_date )
1923 and exists
1924 (select 1 from pay_payroll_actions ppa, pay_assignment_actions paa
1925 where ppa.payroll_action_id = paa.payroll_action_id
1926 and ppa.action_type IN ('||''''||'R'||''''||','||''''||'Q'||''''|| ') '
1927 ||' AND ppa.effective_date between '||''''|| l_canonical_start_date ||''''||' and ' ||''''|| l_canonical_end_date ||''''
1928 ||' AND paa.assignment_id = paf.assignment_id
1929 AND paa.assignment_id = ' || l_ass_id
1930 ||' AND ppa.time_period_id = ptp.time_period_id
1931 )';
1932 */
1933
1934 l_select_qry := l_select_str
1935 ||' FROM
1936 pay_payroll_actions ppa,
1937 pay_assignment_actions paa
1938 where ppa.payroll_action_id = paa.payroll_action_id
1939 and ppa.action_type IN ('||''''||'R'||''''||','||''''||'Q'||''''|| ') '
1940 ||' AND ppa.effective_date between '||''''|| l_canonical_start_date ||''''||' and ' ||''''|| l_canonical_end_date ||''''
1941 || l_where_clause
1942 ||' AND paa.assignment_id = ' || l_ass_id;
1943
1944 Fnd_file.put_line(FND_FILE.LOG,'Dynamic Query:'||l_select_qry );
1945
1946 --
1947 --For csr_asg_payroll_rec IN csr_asg_payroll(l_canonical_start_date,l_canonical_end_date,l_ass_id)
1948 OPEN csr_asg_payroll FOR l_select_qry; --Ref Cursor
1949 LOOP
1950
1951 FETCH csr_asg_payroll INTO l_payroll_id,l_period_id;
1952 EXIT WHEN csr_asg_payroll%NOTFOUND;
1953
1954 --CLOSE csr_asg_payroll;
1955 --hr_utility.set_location('ARCHIVE_CODE: Payroll Name ' || csr_asg_payroll_rec.payroll_name,380);
1956 hr_utility.set_location('ARCHIVE_CODE: l_payroll_id ' || l_payroll_id,380);
1957 --hr_utility.set_location('ARCHIVE_CODE: Period Name ' || csr_asg_payroll_rec.period_name,380);
1958 hr_utility.set_location('ARCHIVE_CODE: time_period_id ' || l_period_id,380);
1959 l_employment_type :='00';
1960 l_green_land_code :='000';
1961 --l_payroll_id:= csr_asg_payroll_rec.payroll_id;
1962 --l_period_id:= csr_asg_payroll_rec.time_period_id;
1963
1964 hr_utility.set_location('ARCHIVE_CODE:After assign l_payroll_id ' || l_payroll_id,380);
1965 hr_utility.set_location('ARCHIVE_CODE:After assign l_period_id ' || l_period_id,380);
1966 hr_utility.set_location('ARCHIVE_CODE:p_effective_date ' || p_effective_date,380);
1967
1968 OPEN csr_pay_periods(l_payroll_id,l_period_id);
1969 FETCH csr_pay_periods into rg_csr_pay_periods;
1970 l_bal_date:= rg_csr_pay_periods.end_date;
1971 CLOSE csr_pay_periods;
1972 l_start_date:=rg_csr_pay_periods.start_date;
1973 l_end_date:=rg_csr_pay_periods.end_date;
1974
1975 hr_utility.set_location('ARCHIVE_CODE: l_bal_date ' || l_bal_date,380);
1976 hr_utility.set_location('ARCHIVE_CODE: l_start_date ' || l_start_date,380);
1977 hr_utility.set_location('ARCHIVE_CODE: l_end_date ' || l_end_date,380);
1978
1979 OPEN csr_get_person_details(p_assignment_action_id, l_start_date ); -- p_effective_date 9489806 --14078879
1980 FETCH csr_get_person_details into rg_csr_get_person_details;
1981 CLOSE csr_get_person_details;
1982 l_assignment_id:=rg_csr_get_person_details.assignment_id;
1983 l_cpr_number:= rg_csr_get_person_details.cpr;
1984 l_dob:=rg_csr_get_person_details.dob;
1985 l_sex:=rg_csr_get_person_details.sex;
1986 l_bg_id:=rg_csr_get_person_details.business_group_id;
1987 l_org_id:=rg_csr_get_person_details.organization_id;
1988 l_loc_id:=rg_csr_get_person_details.location_id;
1989
1990 fnd_file.put_line(fnd_file.log,'Fetched person details');
1991 fnd_file.put_line(fnd_file.log,'rg_csr_get_person_details.person_id:'||rg_csr_get_person_details.person_id);
1992
1993 OPEN csr_assignments_dtl(l_payroll_id, rg_csr_get_person_details.person_id, g_legal_employer_id, l_start_date, l_assignment_id);
1994 FETCH csr_assignments_dtl into rg_csr_assignments_dtl;
1995 CLOSE csr_assignments_dtl;
1996
1997 l_5d_6d := rg_csr_assignments_dtl.DEFAULT_WORK_PATT;
1998
1999 fnd_file.put_line(fnd_file.log,'Fetched Assignment details:'||l_5d_6d);
2000
2001 OPEN csr_get_hol_entit(g_legal_employer_id , l_start_date, g_business_group_id);
2002 FETCH csr_get_hol_entit into rg_csr_get_hol_entit;
2003 CLOSE csr_get_hol_entit;
2004
2005 l_5d_6d := NVL(l_5d_6d,rg_csr_get_hol_entit.DEFAULT_WORK_PATT);
2006
2007 fnd_file.put_line(fnd_file.log,'Fetched csr_get_hol_entit:'||l_5d_6d);
2008
2009 IF rg_csr_get_le_details.style = 'DK' THEN
2010 l_le_postal_code := hr_general.decode_lookup('DK_POSTCODE_TOWN',rg_csr_get_le_details.postal_code);
2011 fnd_file.put_line(fnd_file.log,'DK:l_le_postal_code:'||l_postal_code);
2012 ELSE
2013 l_le_postal_code := rg_csr_get_le_details.postal_code;
2014 fnd_file.put_line(fnd_file.log,'l_le_postal_code:'||l_postal_code);
2015 END IF;
2016
2017 l_le_country := get_country_name(rg_csr_get_le_details.country);
2018 fnd_file.put_line(fnd_file.log,'l_le_country:'||l_le_country);
2019
2020 OPEN csr_get_per_address(rg_csr_get_person_details.person_id , l_start_date, g_business_group_id);
2021 FETCH csr_get_per_address into rg_csr_get_per_address;
2022 CLOSE csr_get_per_address;
2023
2024 fnd_file.put_line(fnd_file.log,'Fetched csr_get_per_address');
2025
2026 IF rg_csr_get_per_address.style = 'DK' THEN
2027 l_postal_code := hr_general.decode_lookup('DK_POSTCODE_TOWN',rg_csr_get_per_address.postal_code);
2028 fnd_file.put_line(fnd_file.log,'DK:l_postal_code:'||l_postal_code);
2029 ELSIF rg_csr_get_per_address.style = 'DK_GLB' and rg_csr_get_per_address.town_or_city is not null THEN
2030 l_postal_code := rg_csr_get_per_address.postal_code || ' ' || rg_csr_get_per_address.town_or_city;
2031 fnd_file.put_line(fnd_file.log,'DK_GLB:l_postal_code:'||l_postal_code);
2032 ELSE
2033 l_postal_code := rg_csr_get_per_address.postal_code;
2034 fnd_file.put_line(fnd_file.log,'l_postal_code:'||l_postal_code);
2035 END IF;
2036
2037 l_country := get_country_name(rg_csr_get_per_address.country);
2038 fnd_file.put_line(fnd_file.log,'l_country:'||l_country);
2039
2040 fnd_file.put_line(fnd_file.log,'l_assignment_id:'||l_assignment_id);
2041 fnd_file.put_line(fnd_file.log,'g_business_group_id:'||g_business_group_id);
2042
2043 OPEN csr_get_bank_details(l_assignment_id , l_start_date, g_business_group_id);
2044 FETCH csr_get_bank_details into rg_csr_get_bank_details;
2045 CLOSE csr_get_bank_details;
2046
2047 --14078879
2048 IF l_report_type <> 'PYDKEHOLCA_MIA' THEN
2049 IF rg_csr_get_bank_details.Account_Number is null then
2050 Fnd_file.put_line(FND_FILE.output,'Assignment Number:'||rg_csr_get_person_details.assignment_number||': You have not entered the employee bank account number. Enter a valid account number.' );
2051 l_errflag := 'Y';
2052 END IF;
2053
2054 IF rg_csr_get_per_address.address_line1 IS NULL THEN
2055 Fnd_file.put_line(FND_FILE.output,'Assignment Number:'||rg_csr_get_person_details.assignment_number||': You have not entered a complete employee address. Enter a valid address.' );
2056 l_errflag := 'Y';
2057 END IF;
2058
2059 IF rg_csr_get_le_details.ad_line1 IS NULL THEN
2060 Fnd_file.put_line(FND_FILE.output,'Assignment Number:'||rg_csr_get_person_details.assignment_number||', Employer:'||rg_csr_get_le_details.NAME||': You have not entered a complete employer address. Enter a valid address.' );
2061 l_errflag := 'Y';
2062 END IF;
2063 END IF;
2064 --14078879
2065
2066 fnd_file.put_line(fnd_file.log,'Fetched csr_get_bank_details');
2067
2068 OPEN csr_get_extra_person_info(rg_csr_get_person_details.person_id);
2069 FETCH csr_get_extra_person_info into rg_csr_get_extra_person_info;
2070
2071 IF(csr_get_extra_person_info%notfound) THEN
2072 l_yes_no:='N';
2073 ELSIF(csr_get_extra_person_info%found) THEN
2074 l_yes_no:=rg_csr_get_extra_person_info.yes_no;
2075 END IF;
2076 CLOSE csr_get_extra_person_info;
2077
2078 IF(l_yes_no='Y') THEN /* This employee is a Foreigner */
2079 --l_cpr_number:=NULL;
2080 l_cpr_number:= '00000000000'; -- Bug 8552112
2081 END IF;
2082
2083 fnd_file.put_line(fnd_file.log,'Fetched payroll period details');
2084
2085 fnd_file.put_line(fnd_file.log,'bg id:'||l_bg_id);
2086 fnd_file.put_line(fnd_file.log,'org id:'||l_org_id);
2087
2088 OPEN csr_get_hr_org_info(l_bg_id,l_org_id);
2089 FETCH csr_get_hr_org_info into rg_csr_get_hr_org_info;
2090 CLOSE csr_get_hr_org_info;
2091
2092
2093 fnd_file.put_line(fnd_file.log,'Fetched hr org info');
2094
2095 OPEN csr_Legal_Emp_Details(g_legal_employer_id);
2096 FETCH csr_Legal_Emp_Details INTO rg_Legal_Emp_Details;
2097 CLOSE csr_Legal_Emp_Details;
2098
2099 fnd_file.put_line(fnd_file.log,'Fetched legal emp details');
2100
2101 OPEN csr_Legal_Emp_DA_Codes(g_legal_employer_id, g_business_group_id);
2102 FETCH csr_Legal_Emp_DA_Codes INTO rg_Legal_Emp_DA_Codes;
2103 CLOSE csr_Legal_Emp_DA_Codes;
2104
2105 fnd_file.put_line(fnd_file.log,'Fetched emp DA codes.');
2106
2107 l_present_flag:=0;
2108 hr_utility.set_location('ARCHIVE_CODE:Before fetch l_present_flag: ' || l_present_flag,380);
2109
2110 OPEN csr_prepaid_actions_present(l_payroll_action_id,
2111 g_legal_employer_id,
2112 l_payroll_id,
2113 l_period_id,
2114 l_start_date,
2115 l_end_date,
2116 l_ass_id);
2117 FETCH csr_prepaid_actions_present INTO l_present_flag;
2118 CLOSE csr_prepaid_actions_present;
2119
2120 hr_utility.set_location('ARCHIVE_CODE:After fetch l_present_flag: ' || l_present_flag,380);
2121
2122 fnd_file.put_line(fnd_file.log,'ARCHIVE_CODE:After fetch l_present_flag: ' || l_present_flag);
2123
2124 IF (l_present_flag=1) THEN
2125
2126 -- TERMINATOR RECORD
2127 OPEN csr_asg_terminator(p_assignment_action_id, l_start_date, l_end_date, g_business_group_id);
2128 FETCH csr_asg_terminator INTO rg_csr_asg_terminator;
2129 CLOSE csr_asg_terminator; -- For bug 9011035
2130
2131 -- For Bug 9011035. fetch the hire date for the coresponding termination date
2132 OPEN csr_latest_hire_date(rg_csr_get_person_details.person_id, rg_csr_asg_terminator.effective_end_date);
2133 FETCH csr_latest_hire_date into rg_csr_latest_hire_date;
2134 CLOSE csr_latest_hire_date;
2135
2136 -- bug 10367494
2137 l_se_no :=rg_csr_get_hr_org_info.ORG_INFORMATION5; -- HR-ORG SE Number
2138 if(l_se_no IS NULL) then
2139 l_se_no:=rg_Legal_Emp_Details.ORG_INFORMATION5;
2140 end if;
2141 if(l_se_no IS NULL) then
2142 l_se_no:=rg_Legal_Emp_Details.ORG_INFORMATION1;
2143 end if;
2144
2145 --
2146 OPEN csr_asg_action_id(rg_csr_get_person_details.assignment_id,l_payroll_id,l_period_id,g_legal_employer_id);
2147 FETCH csr_asg_action_id into rg_csr_asg_action_id;
2148 CLOSE csr_asg_action_id;
2149
2150 l_hourly_salaried :=rg_csr_get_person_details.HOURLY_SALARIED_CODE;
2151 if(l_hourly_salaried IS null) then
2152 if (rg_csr_pay_periods.PAYROLL_PERIOD=1) then
2153 l_hourly_salaried:='S';
2154 else
2155 l_hourly_salaried := 'H';
2156 end if;
2157 end if;
2158
2159 --
2160
2161 fnd_file.put_line(fnd_file.log,'p_assignment_action_id:'||p_assignment_action_id);
2162 fnd_file.put_line(fnd_file.log,'l_bal_date:'||l_bal_date);
2163
2164 l_cvr_number:= rg_Legal_Emp_Details.ORG_INFORMATION1;
2165
2166
2167 --13463885
2168 OPEN get_value_from_ddf(l_payroll_id, l_canonical_start_date);
2169 FETCH get_value_from_ddf INTO l_uhc_payroll, l_pyrl_name;
2170 CLOSE get_value_from_ddf;
2171
2172 fnd_file.put_line(fnd_file.log,'l_uhc_payroll:'||l_uhc_payroll);
2173 fnd_file.put_line(fnd_file.log,'g_end_date:'||g_end_date||'--l_canonical_start_date--'||l_canonical_start_date);
2174
2175 l_uhc_payroll := NVL(l_uhc_payroll,l_use_holiday_card);
2176
2177 fnd_file.put_line(fnd_file.log,'l_use_holiday_card:'||l_use_holiday_card);
2178
2179 --14078879
2180 IF l_cpr_number is NULL THEN
2181 Fnd_file.put_line(FND_FILE.output,'Assignment Number:'||rg_csr_get_person_details.assignment_number||': You have not entered the employee CPR number. Enter a valid CPR number.' );
2182 l_errflag := 'Y';
2183 END IF;
2184
2185 IF l_report_type <> 'PYDKEHOLCA_MIA' THEN
2186 IF l_se_no is null then
2187 Fnd_file.put_line(FND_FILE.output,'Assignment Number:'||rg_csr_get_person_details.assignment_number||': You have not entered the employee SE number. Enter a valid SE number.' );
2188 l_errflag := 'Y';
2189 END IF;
2190 END IF;
2191 --14078879
2192
2193 --14078879
2194 IF l_uhc_payroll = 'N' AND g_payroll_id IS NULL THEN
2195 Fnd_file.put_line(FND_FILE.output,'Assignment Number:'||rg_csr_get_person_details.assignment_number||', Payroll:'||l_pyrl_name||': Use Holiday Card not set at payroll level.');
2196 l_errflag_uhc1 := 'Y';
2197 END IF;
2198 --14078879
2199
2200 IF (l_use_holiday_card ='Y' AND l_uhc_payroll ='Y') AND l_usr_tbl_exist_400 = 'N' THEN
2201
2202 pay_action_information_api.create_action_information (
2203 p_action_information_id => l_action_info_id
2204 ,p_action_context_id => p_assignment_action_id
2205 ,p_action_context_type => 'AAP'
2206 ,p_object_version_number => l_ovn
2207 ,p_effective_date => g_effective_date
2208 ,p_source_id => NULL
2209 ,p_source_text => NULL
2210 ,p_action_information_category => 'EMEA REPORT INFORMATION'
2211 ,p_action_information1 => l_report_type -- 'PYDKEHOLCA' / 'PYDKEHOLCA_ANN'
2212 ,p_action_information2 => l_payroll_action_id
2213 ,p_action_information3 => 'DK EMPLOYEE DETAILS'
2214 ,p_action_information4 => rg_csr_get_person_details.person_id --Person ID
2215 ,p_action_information5 => rg_csr_get_person_details.assignment_id --Assignment ID
2216 ,p_action_information6 => substr(l_cpr_number,1,6)||substr(l_cpr_number,8,4) --CPR Number
2217 ,p_action_information7 => to_char(rg_csr_latest_hire_date.lhd,'rrrr-mm-dd') --Person Hire Date
2218 ,p_action_information8 => to_char(rg_csr_asg_terminator.EFFECTIVE_END_DATE,'rrrr-mm-dd') --Termination date
2219 ,p_action_information9 => rg_csr_get_person_details.assignment_number --Assignment Number
2220 ,p_action_information10 => to_char(l_start_date,'rrrr-mm-dd') --effective start date
2221 ,p_action_information11 => rg_csr_get_person_details.full_name --Employee Name
2222 ,p_action_information12 => rg_csr_assignments_dtl.MO_CODE --MO Code
2223 ,p_action_information13 => NVL(rg_csr_assignments_dtl.FO_CODE, rg_csr_assignments_dtl.MO_CODE) --FO Code --14078879
2224 ,p_action_information14 => l_se_no
2225 ,p_action_information15 => null
2226 ,p_action_information16 => null
2227 ,p_action_information17 => null
2228 ,p_action_information18 => null
2229 ,p_action_information19 => null
2230 ,p_action_information20 => null
2231 ,p_action_information21 => Null
2232 ,p_action_information22 => null
2233 ,p_action_information23 => null
2234 ,p_action_information24 => null
2235 ,p_action_information25 => null
2236 ,p_action_information26 => null
2237 ,p_action_information27 => to_char(l_start_date,'rrrr-mm-dd')
2238 ,p_action_information28 => to_char(l_end_date,'rrrr-mm-dd')
2239 ,p_action_information29 => l_period_id
2240 ,p_action_information30 => l_payroll_id --Payroll_ID
2241 );
2242
2243
2244 l_accr_yr := to_char(l_canonical_end_date,'RRRR');
2245 l_last_yr := to_char(TRUNC(l_canonical_end_date,'YEAR') - 1, 'RRRR');
2246
2247 IF to_char(l_canonical_end_date,'MM') <='04' THEN
2248 l_curr_entit_yr := to_char(to_number(l_last_yr)- 1);
2249 l_nxt_entit_yr := l_last_yr;
2250 ELSE
2251 l_curr_entit_yr := l_last_yr;
2252 l_nxt_entit_yr := '0000';
2253 END IF;
2254
2255 fnd_file.put_line(fnd_file.log,'l_accr_yr:'||l_accr_yr);
2256 fnd_file.put_line(fnd_file.log,'l_last_yr:'||l_last_yr);
2257 fnd_file.put_line(fnd_file.log,'l_curr_entit_yr:'||l_curr_entit_yr);
2258 fnd_file.put_line(fnd_file.log,'l_nxt_entit_yr:'||l_nxt_entit_yr);
2259
2260 OPEN cur_max_asg_act_id(l_assignment_id, l_canonical_start_date, l_canonical_end_date);
2261 FETCH cur_max_asg_act_id INTO l_max_asg_act_id;
2262 CLOSE cur_max_asg_act_id;
2263
2264 fnd_file.put_line(fnd_file.log,'l_max_asg_act_id:'||l_max_asg_act_id);
2265
2266 IF l_hourly_salaried='H' THEN
2267
2268 fnd_file.put_line(fnd_file.log,'Entered l_hourly_salaried=H:');
2269
2270 l_holiday_pay_hr := NVL(pay_balance_pkg.get_value(get_defined_balance_id('Hourly Holiday Pay','_PAYMENTS'),l_max_asg_act_id,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'TRUE'),0);
2271 fnd_file.put_line(fnd_file.log,'l_holiday_pay_hr:'||l_holiday_pay_hr);
2272
2273 l_hol_tax_hr := NVL(pay_balance_pkg.get_value(get_defined_balance_id('Hourly Holiday Tax','_PAYMENTS'),l_max_asg_act_id,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'TRUE'),0);
2274 fnd_file.put_line(fnd_file.log,'l_hol_tax_hr:'||l_hol_tax_hr);
2275
2276 l_hol_amb_hr := NVL(pay_balance_pkg.get_value(get_defined_balance_id('Hourly Holiday AMB','_PAYMENTS'),l_max_asg_act_id,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'TRUE'),0);
2277 fnd_file.put_line(fnd_file.log,'l_hol_amb_hr:'||l_hol_amb_hr);
2278
2279 --l_hol_pay_0202_sp := NVL(GET_DEFINED_BALANCE_VALUE(l_assignment_id, 'Hourly Holiday SP','_PAYMENTS' ,l_bal_date),0);
2280
2281 --l_net_hol_pay_0202 := l_hol_pay_0202 - (l_hol_pay_0202_tax + l_hol_pay_0202_amb );
2282 --fnd_file.put_line(fnd_file.log,'l_net_hol_pay_0202:'||l_net_hol_pay_0202);
2283
2284 l_Holidayable_amount_hr := NVL(pay_balance_pkg.get_value(get_defined_balance_id('Holidayable Pay','_ASG_YTD'),l_max_asg_act_id,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'TRUE'),0);
2285 fnd_file.put_line(fnd_file.log,'l_Holidayable_amount_hr:'||l_Holidayable_amount_hr);
2286
2287 l_tot_holiday_pay_hr := l_holiday_pay_hr + l_hol_tax_hr + l_hol_amb_hr ;
2288 fnd_file.put_line(fnd_file.log,'l_tot_holiday_pay_hr:'||l_tot_holiday_pay_hr);
2289
2290 l_taxable_hol_pay_hr := l_tot_holiday_pay_hr - l_hol_amb_hr ;
2291 fnd_file.put_line(fnd_file.log,'l_taxable_hol_pay_hr:'||l_taxable_hol_pay_hr);
2292
2293 l_tot_no_holidays_hr := NVL(pay_balance_pkg.get_value(get_defined_balance_id('Hourly Holiday Days','_PAYMENTS'),l_max_asg_act_id,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'TRUE'),0);
2294 fnd_file.put_line(fnd_file.log,'l_tot_no_holidays_hr:'||l_tot_no_holidays_hr);
2295
2296 IF l_tot_no_holidays_hr <> 0 THEN
2297 l_hol_pay_per_holiday_hr := (l_holiday_pay_hr / l_tot_no_holidays_hr);
2298 END IF;
2299 fnd_file.put_line(fnd_file.log,'l_hol_pay_per_holiday_hr:'||l_hol_pay_per_holiday_hr);
2300
2301 l_profit_free_ch_ac_hr := NVL(pay_balance_pkg.get_value(get_defined_balance_id('Profit From Free Choice Account Accrual','_ASG_YTD'),l_max_asg_act_id,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'TRUE'),0);
2302 fnd_file.put_line(fnd_file.log,'l_profit_free_ch_ac_hr:'||l_profit_free_ch_ac_hr);
2303
2304 l_net_hol_pay_hr := l_holiday_pay_hr + l_profit_free_ch_ac_hr ;
2305 fnd_file.put_line(fnd_file.log,'l_net_hol_pay_hr:'||l_net_hol_pay_hr);
2306
2307 l_hol_pay_sick_hr := NVL(pay_balance_pkg.get_value(get_defined_balance_id('Sick Pay Subject to Holiday Accrual','_ASG_YTD'),l_max_asg_act_id,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'TRUE'),0);
2308 fnd_file.put_line(fnd_file.log,'l_hol_pay_sick_hr:'||l_hol_pay_sick_hr);
2309
2310 --
2311 l_hol_days_paid_hr := NVL(pay_balance_pkg.get_value(get_defined_balance_id('Holiday Days Paid','_ASG_PTD'),l_max_asg_act_id,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'TRUE'),0);
2312 fnd_file.put_line(fnd_file.log,'l_hol_days_paid_hr:'||l_hol_days_paid_hr);
2313
2314 l_hol_pay_paid_hr := NVL(pay_balance_pkg.get_value(get_defined_balance_id('Holiday Pay Paid','_ASG_PTD'),l_max_asg_act_id,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'TRUE'),0);
2315 fnd_file.put_line(fnd_file.log,'l_hol_pay_paid_hr:'||l_hol_pay_paid_hr);
2316
2317 l_hrly_hol_pay_hr := NVL(pay_balance_pkg.get_value(get_defined_balance_id('Hourly Holiday Pay','_ASG_HOLIDAY_YTD'),l_max_asg_act_id,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'TRUE'),0);
2318 fnd_file.put_line(fnd_file.log,'l_hrly_hol_pay_hr:'||l_hrly_hol_pay_hr);
2319
2320 l_hrly_hol_days_hr := NVL(pay_balance_pkg.get_value(get_defined_balance_id('Hourly Holiday Days','_ASG_HOLIDAY_YTD'),l_max_asg_act_id,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'TRUE'),0);
2321 fnd_file.put_line(fnd_file.log,'l_hrly_hol_days_hr:'||l_hrly_hol_days_hr);
2322 --
2323
2324 ELSIF l_hourly_salaried='S' THEN
2325
2326 fnd_file.put_line(fnd_file.log,'Entered l_hourly_salaried=S:');
2327
2328 l_sal_hol_acr_amt := NVL(pay_balance_pkg.get_value(get_defined_balance_id('Salaried Hol Accrual Amount','_PAYMENTS'),l_max_asg_act_id,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'TRUE'),0);
2329 fnd_file.put_line(fnd_file.log,'l_sal_hol_acr_amt:'||l_sal_hol_acr_amt);
2330
2331 l_sal_hol_acr_tax := NVL(pay_balance_pkg.get_value(get_defined_balance_id('Salaried Hol Accrual Tax','_PAYMENTS'),l_max_asg_act_id,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'TRUE'),0);
2332 fnd_file.put_line(fnd_file.log,'l_sal_hol_acr_tax:'||l_sal_hol_acr_tax);
2333
2334 l_sal_hol_acr_amb := NVL(pay_balance_pkg.get_value(get_defined_balance_id('Salaried Hol Accrual AMB','_PAYMENTS'),l_max_asg_act_id,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'TRUE'),0);
2335 fnd_file.put_line(fnd_file.log,'l_sal_hol_acr_amb:'||l_sal_hol_acr_amb);
2336
2337 l_sal_hol_acr_days := NVL(pay_balance_pkg.get_value(get_defined_balance_id('Salaried Hol Accrual Days','_PAYMENTS'),l_max_asg_act_id,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'TRUE'),0);
2338 fnd_file.put_line(fnd_file.log,'l_sal_hol_acr_days:'||l_sal_hol_acr_days);
2339
2340 --l_net_hol_pay_6102_1 := l_sal_hol_acr_amt - (l_sal_hol_acr_tax + l_sal_hol_acr_amb);
2341
2342 l_Holidayable_amount_1 := NVL(pay_balance_pkg.get_value(get_defined_balance_id('Holidayable Pay','_ASG_YTD'),l_max_asg_act_id,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'TRUE'),0);
2343 fnd_file.put_line(fnd_file.log,'l_Holidayable_amount_1:'||l_Holidayable_amount_1);
2344
2345 l_holiday_pay_1 := l_sal_hol_acr_amt;
2346 fnd_file.put_line(fnd_file.log,'l_holiday_pay_1:'||l_holiday_pay_1);
2347
2348 l_tot_holiday_pay_1 := l_sal_hol_acr_amt + l_sal_hol_acr_tax + l_sal_hol_acr_amb ;
2349 fnd_file.put_line(fnd_file.log,'l_tot_holiday_pay_1:'||l_tot_holiday_pay_1);
2350
2351 l_taxable_hol_pay_1 := l_tot_holiday_pay_1 - l_sal_hol_acr_amb ;
2352 fnd_file.put_line(fnd_file.log,'l_taxable_hol_pay_1:'||l_taxable_hol_pay_1);
2353
2354 IF l_sal_hol_acr_days <> 0 THEN
2355 l_hol_pay_per_holiday_1 := (l_holiday_pay_1 / l_sal_hol_acr_days);
2356 END IF;
2357 fnd_file.put_line(fnd_file.log,'l_hol_pay_per_holiday_1:'||l_hol_pay_per_holiday_1);
2358
2359 l_profit_free_ch_ac_1 := NVL(pay_balance_pkg.get_value(get_defined_balance_id('Profit From Free Choice Account Accrual','_ASG_YTD'),l_max_asg_act_id,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'TRUE'),0);
2360 fnd_file.put_line(fnd_file.log,'l_profit_free_ch_ac_1:'||l_profit_free_ch_ac_1);
2361
2362 l_net_hol_pay_1 := l_holiday_pay_1 + l_profit_free_ch_ac_1 ;
2363 fnd_file.put_line(fnd_file.log,'l_net_hol_pay_1:'||l_net_hol_pay_1);
2364
2365 l_hol_pay_sick_1 := NVL(pay_balance_pkg.get_value(get_defined_balance_id('Sick Pay Subject to Holiday Accrual','_ASG_YTD'),l_max_asg_act_id,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'TRUE'),0);
2366 fnd_file.put_line(fnd_file.log,'l_hol_pay_sick_1:'||l_hol_pay_sick_1);
2367
2368 --
2369 l_sal_hol_cur_ent_amt := NVL(pay_balance_pkg.get_value(get_defined_balance_id('Salaried Hol Curr Entit Amount','_PAYMENTS'),l_max_asg_act_id,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'TRUE'),0);
2370 fnd_file.put_line(fnd_file.log,'l_sal_hol_cur_ent_amt:'||l_sal_hol_cur_ent_amt);
2371
2372 l_sal_hol_cur_ent_tax := NVL(pay_balance_pkg.get_value(get_defined_balance_id('Salaried Hol Curr Entit Tax','_PAYMENTS'),l_max_asg_act_id,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'TRUE'),0);
2373 fnd_file.put_line(fnd_file.log,'l_sal_hol_cur_ent_tax:'||l_sal_hol_cur_ent_tax);
2374
2375 l_sal_hol_cur_ent_amb := NVL(pay_balance_pkg.get_value(get_defined_balance_id('Salaried Hol Curr Entit AMB','_PAYMENTS'),l_max_asg_act_id,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'TRUE'),0);
2376 fnd_file.put_line(fnd_file.log,'l_sal_hol_cur_ent_amb:'||l_sal_hol_cur_ent_amb);
2377
2378 l_sal_hol_cur_ent_days := NVL(pay_balance_pkg.get_value(get_defined_balance_id('Salaried Hol Curr Entit Days','_PAYMENTS'),l_max_asg_act_id,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'TRUE'),0);
2379 fnd_file.put_line(fnd_file.log,'l_sal_hol_cur_ent_days:'||l_sal_hol_cur_ent_days);
2380
2381 --l_net_hol_pay_6102_2 := l_sal_hol_cur_ent_amt - (l_sal_hol_cur_ent_tax + l_sal_hol_cur_ent_amb);
2382
2383 --l_Holidayable_amount_3 := NVL(pay_balance_pkg.get_value(get_defined_balance_id('Holidayable Pay','_ASG_YTD'),l_max_asg_act_id,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'TRUE'),0);
2384 --fnd_file.put_line(fnd_file.log,'l_Holidayable_amount_3:'||l_Holidayable_amount_3);
2385
2386 l_holiday_pay_3 := l_sal_hol_cur_ent_amt;
2387 fnd_file.put_line(fnd_file.log,'l_holiday_pay_3:'||l_holiday_pay_3);
2388
2389 l_tot_holiday_pay_3 := l_sal_hol_cur_ent_amt + l_sal_hol_cur_ent_tax + l_sal_hol_cur_ent_amb ;
2390 fnd_file.put_line(fnd_file.log,'l_tot_holiday_pay_3:'||l_tot_holiday_pay_3);
2391
2392 l_taxable_hol_pay_3 := l_tot_holiday_pay_3 - l_sal_hol_cur_ent_amb ;
2393 fnd_file.put_line(fnd_file.log,'l_taxable_hol_pay_3:'||l_taxable_hol_pay_3);
2394
2395 IF l_sal_hol_cur_ent_amt <> 0 THEN
2396 l_hol_pay_per_holiday_3 := (l_holiday_pay_3 / l_sal_hol_cur_ent_amt);
2397 END IF;
2398 fnd_file.put_line(fnd_file.log,'l_hol_pay_per_holiday_3:'||l_hol_pay_per_holiday_3);
2399
2400 l_profit_free_ch_ac_3 := NVL(pay_balance_pkg.get_value(get_defined_balance_id('Profit From Free Choice Account Accrual','_ASG_YTD'),l_max_asg_act_id,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'TRUE'),0);
2401 fnd_file.put_line(fnd_file.log,'l_profit_free_ch_ac_3:'||l_profit_free_ch_ac_3);
2402
2403 l_net_hol_pay_3 := l_holiday_pay_3 + l_profit_free_ch_ac_3 ;
2404 fnd_file.put_line(fnd_file.log,'l_net_hol_pay_3:'||l_net_hol_pay_3);
2405
2406 l_hol_pay_sick_3 := NVL(pay_balance_pkg.get_value(get_defined_balance_id('Sick Pay Subject to Holiday Accrual','_ASG_YTD'),l_max_asg_act_id,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'TRUE'),0);
2407 fnd_file.put_line(fnd_file.log,'l_hol_pay_sick_3:'||l_hol_pay_sick_3);
2408
2409 --
2410 l_sal_hol_nxt_ent_amt := NVL(pay_balance_pkg.get_value(get_defined_balance_id('Salaried Hol Next Entit Amount','_PAYMENTS'),l_max_asg_act_id,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'TRUE'),0);
2411 fnd_file.put_line(fnd_file.log,'l_sal_hol_nxt_ent_amt:'||l_sal_hol_nxt_ent_amt);
2412
2413 l_sal_hol_nxt_ent_tax := NVL(pay_balance_pkg.get_value(get_defined_balance_id('Salaried Hol Next Entit Tax','_PAYMENTS'),l_max_asg_act_id,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'TRUE'),0);
2414 fnd_file.put_line(fnd_file.log,'l_sal_hol_nxt_ent_tax:'||l_sal_hol_nxt_ent_tax);
2415
2416 l_sal_hol_nxt_ent_amb := NVL(pay_balance_pkg.get_value(get_defined_balance_id('Salaried Hol Next Entit AMB','_PAYMENTS'),l_max_asg_act_id,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'TRUE'),0);
2417 fnd_file.put_line(fnd_file.log,'l_sal_hol_nxt_ent_amb:'||l_sal_hol_nxt_ent_amb);
2418
2419 l_sal_hol_nxt_ent_days := NVL(pay_balance_pkg.get_value(get_defined_balance_id('Salaried Hol Next Entit Days','_PAYMENTS'),l_max_asg_act_id,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'TRUE'),0);
2420 fnd_file.put_line(fnd_file.log,'l_sal_hol_nxt_ent_days:'||l_sal_hol_nxt_ent_days);
2421
2422 --l_net_hol_pay_6102_3 := l_sal_hol_nxt_ent_amt - (l_sal_hol_nxt_ent_tax + l_sal_hol_nxt_ent_amb);
2423
2424 --l_Holidayable_amount_2 := NVL(pay_balance_pkg.get_value(get_defined_balance_id('Holidayable Pay','_ASG_YTD'),l_max_asg_act_id,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'TRUE'),0);
2425 --fnd_file.put_line(fnd_file.log,'l_Holidayable_amount_2:'||l_Holidayable_amount_2);
2426
2427 l_holiday_pay_2 := l_sal_hol_nxt_ent_amt;
2428 fnd_file.put_line(fnd_file.log,'l_holiday_pay_2:'||l_holiday_pay_2);
2429
2430 l_tot_holiday_pay_2 := l_sal_hol_nxt_ent_amt + l_sal_hol_nxt_ent_tax + l_sal_hol_nxt_ent_amb ;
2431 fnd_file.put_line(fnd_file.log,'l_tot_holiday_pay_2:'||l_tot_holiday_pay_2);
2432
2433 l_taxable_hol_pay_2 := l_tot_holiday_pay_2 - l_sal_hol_nxt_ent_amb ;
2434 fnd_file.put_line(fnd_file.log,'l_taxable_hol_pay_2:'||l_taxable_hol_pay_2);
2435
2436 IF l_sal_hol_nxt_ent_amt <> 0 THEN
2437 l_hol_pay_per_holiday_2 := (l_holiday_pay_2 / l_sal_hol_nxt_ent_amt);
2438 END IF;
2439 fnd_file.put_line(fnd_file.log,'l_hol_pay_per_holiday_2:'||l_hol_pay_per_holiday_2);
2440
2441 l_profit_free_ch_ac_2 := NVL(pay_balance_pkg.get_value(get_defined_balance_id('Profit From Free Choice Account Accrual','_ASG_YTD'),l_max_asg_act_id,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'TRUE'),0);
2442 fnd_file.put_line(fnd_file.log,'l_profit_free_ch_ac_2:'||l_profit_free_ch_ac_2);
2443
2444 l_net_hol_pay_2 := l_holiday_pay_2 + l_profit_free_ch_ac_2 ;
2445 fnd_file.put_line(fnd_file.log,'l_net_hol_pay_2:'||l_net_hol_pay_2);
2446
2447 l_hol_pay_sick_2 := NVL(pay_balance_pkg.get_value(get_defined_balance_id('Sick Pay Subject to Holiday Accrual','_ASG_YTD'),l_max_asg_act_id,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'TRUE'),0);
2448 fnd_file.put_line(fnd_file.log,'l_hol_pay_sick_2:'||l_hol_pay_sick_2);
2449 --
2450 l_hol_days_paid_1 := NVL(pay_balance_pkg.get_value(get_defined_balance_id('Holiday Days Paid','_ASG_PTD'),l_max_asg_act_id,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'TRUE'),0);
2451 fnd_file.put_line(fnd_file.log,'l_hol_days_paid_1:'||l_hol_days_paid_1);
2452
2453 l_hol_pay_paid_1 := NVL(pay_balance_pkg.get_value(get_defined_balance_id('Holiday Pay Paid','_ASG_PTD'),l_max_asg_act_id,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'TRUE'),0);
2454 fnd_file.put_line(fnd_file.log,'l_hol_pay_paid_1:'||l_hol_pay_paid_1);
2455
2456 l_sal_hol_pay_1 := NVL(pay_balance_pkg.get_value(get_defined_balance_id('Salaried Hol Curr Entit Amount','_ASG_HOLIDAY_YTD'),l_max_asg_act_id,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'TRUE'),0);
2457 fnd_file.put_line(fnd_file.log,'l_sal_hol_pay_1:'||l_sal_hol_pay_1);
2458
2459 l_sal_hol_days_1 := NVL(pay_balance_pkg.get_value(get_defined_balance_id('Salaried Hol Curr Entit Days','_ASG_HOLIDAY_YTD'),l_max_asg_act_id,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'TRUE'),0);
2460 fnd_file.put_line(fnd_file.log,'l_sal_hol_days_1:'||l_sal_hol_days_1);
2461
2462 --
2463
2464 END IF;
2465 END IF;
2466
2467 fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 15');
2468
2469 --
2470 IF l_report_type = 'PYDKEHOLCA' THEN
2471 --13463885
2472 IF (l_use_holiday_card ='Y' AND l_uhc_payroll ='Y') AND l_usr_tbl_exist_400 = 'N' THEN
2473
2474 IF l_hourly_salaried='H' THEN
2475
2476 --IF l_tot_no_holidays_hr <> 0 OR l_holiday_pay_hr <> 0 THEN
2477 --YEAR1
2478 pay_action_information_api.create_action_information (
2479 p_action_information_id => l_action_info_id
2480 ,p_action_context_id => p_assignment_action_id
2481 ,p_action_context_type => 'AAP'
2482 ,p_object_version_number => l_ovn
2483 ,p_effective_date => g_effective_date
2484 ,p_source_id => NULL
2485 ,p_source_text => NULL
2486 ,p_action_information_category => 'EMEA REPORT INFORMATION'
2487 ,p_action_information1 => 'PYDKEHOLCA'
2488 ,p_action_information2 => l_payroll_action_id
2489 ,p_action_information3 => 'DK HCARD INFO'
2490 ,p_action_information4 => rg_csr_get_person_details.person_id --Person ID
2491 ,p_action_information5 => rg_csr_get_person_details.assignment_id --Assignment ID
2492 ,p_action_information6 => l_accr_yr --Accrual year
2493 ,p_action_information7 => l_accr_yr||'-01-01' --Accrual year From
2494 ,p_action_information8 => l_accr_yr||'-12-31' --Accrual year To
2495 ,p_action_information9 => rg_csr_assignments_dtl.FH_CODE --FH Code
2496 ,p_action_information10 => rg_csr_get_bank_details.Account_Number --Bank A/c #
2497 ,p_action_information11 => l_hourly_salaried --Salaried/Hourly (S/H)
2498 ,p_action_information12 => l_5d_6d --5/6 Day Working
2499 ,p_action_information13 => rg_csr_get_person_details.full_name --Employee Name
2500 ,p_action_information14 => rg_csr_get_per_address.address_line1 --ADDRESS1
2501 ,p_action_information15 => rg_csr_get_per_address.address_line2 --ADDRESS2
2502 ,p_action_information16 => rg_csr_get_per_address.address_line3 --ADDRESS3
2503 ,p_action_information17 => rg_csr_get_per_address.town_or_city --ADDRESS4
2504 ,p_action_information18 => l_postal_code --ADDRESS5
2505 ,p_action_information19 => l_country --ADDRESS6
2506 ,p_action_information20 => rg_csr_get_le_details.NAME --LE Name
2507 ,p_action_information21 => rg_csr_get_le_details.ad_line1 --LE ADDRESS1
2508 ,p_action_information22 => rg_csr_get_le_details.ad_line2 --LE ADDRESS2
2509 ,p_action_information23 => rg_csr_get_le_details.ad_line3 --LE ADDRESS3
2510 ,p_action_information24 => rg_csr_get_le_details.town_or_city --LE ADDRESS4
2511 ,p_action_information25 => l_le_postal_code --LE ADDRESS5
2512 ,p_action_information26 => l_le_country --LE ADDRESS6
2513 ,p_action_information27 => null
2514 ,p_action_information28 => null
2515 ,p_action_information29 => null
2516 ,p_action_information30 => l_payroll_id --Payroll_ID
2517 );
2518
2519 OPEN cur_get_hol_pay_perc(to_date('31-12'||l_accr_yr,'dd-mm-rrrr'), l_assignment_id );
2520 FETCH cur_get_hol_pay_perc into l_hol_pay_percentage;
2521 CLOSE cur_get_hol_pay_perc;
2522
2523 fnd_file.put_line(fnd_file.log,'1-Fetched cur_get_hol_pay_perc');
2524 fnd_file.put_line(fnd_file.log,'1-l_hol_pay_percentage:'||l_hol_pay_percentage);
2525
2526 IF l_hol_pay_percentage IS NULL
2527 THEN
2528 l_hol_pay_percentage := NVL(rg_csr_assignments_dtl.HOURLY_ACCR_RATE,rg_csr_get_hol_entit.HOURLY_ACCR_RATE);
2529 END IF;
2530
2531 fnd_file.put_line(fnd_file.log,'2-l_hol_pay_percentage:'||l_hol_pay_percentage);
2532
2533 pay_action_information_api.create_action_information (
2534 p_action_information_id => l_action_info_id
2535 ,p_action_context_id => p_assignment_action_id
2536 ,p_action_context_type => 'AAP'
2537 ,p_object_version_number => l_ovn
2538 ,p_effective_date => g_effective_date
2539 ,p_source_id => NULL
2540 ,p_source_text => NULL
2541 ,p_action_information_category => 'EMEA REPORT INFORMATION'
2542 ,p_action_information1 => 'PYDKEHOLCA'
2543 ,p_action_information2 => l_payroll_action_id
2544 ,p_action_information3 => 'DK HCARD INFO ADD'
2545 ,p_action_information4 => rg_csr_get_person_details.person_id --Person ID
2546 ,p_action_information5 => rg_csr_get_person_details.assignment_id --Assignment ID
2547 ,p_action_information6 => l_accr_yr --Accrual year
2548 ,p_action_information7 => l_Holidayable_amount_hr --Holidayable amount
2549 ,p_action_information8 => l_tot_no_holidays_hr --Total No of Holidays
2550 ,p_action_information9 => l_hol_pay_percentage --Holiday pay percentage
2551 ,p_action_information10 => l_holiday_pay_hr --Holiday pay
2552 ,p_action_information11 => l_hol_pay_sick_hr --Holiday pay for sicknes
2553 ,p_action_information12 => l_hol_pay_per_holiday_hr --Holiday pay per holiday
2554 ,p_action_information13 => l_tot_holiday_pay_hr --Total holiday pay
2555 ,p_action_information14 => l_hol_amb_hr --Calculated AMB contribution
2556 ,p_action_information15 => l_taxable_hol_pay_hr --Tax able holiday pay
2557 ,p_action_information16 => l_hol_tax_hr --Calculated Tax
2558 ,p_action_information17 => l_holiday_pay_hr --Net holiday pay
2559 ,p_action_information18 => l_profit_free_ch_ac_hr --Profit from free choice account
2560 ,p_action_information19 => l_net_hol_pay_hr --Net holiday pay
2561 ,p_action_information20 => g_msg_txt --Free Text Field
2562 ,p_action_information21 => l_hol_days_paid_hr
2563 ,p_action_information22 => l_hol_pay_paid_hr
2564 ,p_action_information23 => l_hrly_hol_pay_hr
2565 ,p_action_information24 => l_hrly_hol_days_hr
2566 ,p_action_information25 => null
2567 ,p_action_information26 => null
2568 ,p_action_information27 => null
2569 ,p_action_information28 => null
2570 ,p_action_information29 => null
2571 ,p_action_information30 => l_payroll_id --Payroll_ID
2572 );
2573
2574 fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 19-1: Inserted PYDKEHOLCA - 1');
2575 l_hol_pay_percentage := NULL;
2576
2577 --END IF;
2578
2579 END IF;
2580 END IF;
2581 --13463885
2582
2583 --13463885
2584 IF (l_use_holiday_card ='Y' AND l_uhc_payroll ='Y') AND l_usr_tbl_exist_400 = 'N' THEN
2585
2586 IF l_hourly_salaried='S' /* AND l_holiday_pay_1 <> 0 */ THEN
2587 --fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 15-1');
2588 --YEAR1
2589 pay_action_information_api.create_action_information (
2590 p_action_information_id => l_action_info_id
2591 ,p_action_context_id => p_assignment_action_id
2592 ,p_action_context_type => 'AAP'
2593 ,p_object_version_number => l_ovn
2594 ,p_effective_date => g_effective_date
2595 ,p_source_id => NULL
2596 ,p_source_text => NULL
2597 ,p_action_information_category => 'EMEA REPORT INFORMATION'
2598 ,p_action_information1 => 'PYDKEHOLCA'
2599 ,p_action_information2 => l_payroll_action_id
2600 ,p_action_information3 => 'DK HCARD INFO'
2601 ,p_action_information4 => rg_csr_get_person_details.person_id --Person ID
2602 ,p_action_information5 => rg_csr_get_person_details.assignment_id --Assignment ID
2603 ,p_action_information6 => l_accr_yr --Accrual year
2604 ,p_action_information7 => l_accr_yr||'-01-01' --Accrual year From
2605 ,p_action_information8 => l_accr_yr||'-12-31' --Accrual year To
2606 ,p_action_information9 => rg_csr_assignments_dtl.FH_CODE --FH Code
2607 ,p_action_information10 => rg_csr_get_bank_details.Account_Number --Bank A/c #
2608 ,p_action_information11 => l_hourly_salaried --Salaried/Hourly (S/H)
2609 ,p_action_information12 => l_5d_6d --5/6 Day Working
2610 ,p_action_information13 => rg_csr_get_person_details.full_name --Employee Name
2611 ,p_action_information14 => rg_csr_get_per_address.address_line1 --ADDRESS1
2612 ,p_action_information15 => rg_csr_get_per_address.address_line2 --ADDRESS2
2613 ,p_action_information16 => rg_csr_get_per_address.address_line3 --ADDRESS3
2614 ,p_action_information17 => rg_csr_get_per_address.town_or_city --ADDRESS4
2615 ,p_action_information18 => l_postal_code --ADDRESS5
2616 ,p_action_information19 => l_country --ADDRESS6
2617 ,p_action_information20 => rg_csr_get_le_details.NAME --LE Name
2618 ,p_action_information21 => rg_csr_get_le_details.ad_line1 --LE ADDRESS1
2619 ,p_action_information22 => rg_csr_get_le_details.ad_line2 --LE ADDRESS2
2620 ,p_action_information23 => rg_csr_get_le_details.ad_line3 --LE ADDRESS3
2621 ,p_action_information24 => rg_csr_get_le_details.town_or_city --LE ADDRESS4
2622 ,p_action_information25 => l_le_postal_code --LE ADDRESS5
2623 ,p_action_information26 => l_le_country --LE ADDRESS6
2624 ,p_action_information27 => null
2625 ,p_action_information28 => null
2626 ,p_action_information29 => null
2627 ,p_action_information30 => l_payroll_id --Payroll_ID
2628 );
2629
2630 OPEN cur_get_hol_pay_perc(to_date('31-12'||l_accr_yr,'dd-mm-rrrr'), l_assignment_id );
2631 FETCH cur_get_hol_pay_perc into l_hol_pay_percentage;
2632 CLOSE cur_get_hol_pay_perc;
2633
2634 fnd_file.put_line(fnd_file.log,'3-Fetched cur_get_hol_pay_perc');
2635 fnd_file.put_line(fnd_file.log,'3-l_hol_pay_percentage:'||l_hol_pay_percentage);
2636
2637 IF l_hol_pay_percentage IS NULL
2638 THEN
2639 l_hol_pay_percentage := NVL(rg_csr_assignments_dtl.SAL_ALLOW_RATE,rg_csr_get_hol_entit.SAL_ALLOW_RATE);
2640 END IF;
2641
2642 fnd_file.put_line(fnd_file.log,'4-l_hol_pay_percentage:'||l_hol_pay_percentage);
2643
2644 pay_action_information_api.create_action_information (
2645 p_action_information_id => l_action_info_id
2646 ,p_action_context_id => p_assignment_action_id
2647 ,p_action_context_type => 'AAP'
2648 ,p_object_version_number => l_ovn
2649 ,p_effective_date => g_effective_date
2650 ,p_source_id => NULL
2651 ,p_source_text => NULL
2652 ,p_action_information_category => 'EMEA REPORT INFORMATION'
2653 ,p_action_information1 => 'PYDKEHOLCA'
2654 ,p_action_information2 => l_payroll_action_id
2655 ,p_action_information3 => 'DK HCARD INFO ADD'
2656 ,p_action_information4 => rg_csr_get_person_details.person_id --Person ID
2657 ,p_action_information5 => rg_csr_get_person_details.assignment_id --Assignment ID
2658 ,p_action_information6 => l_accr_yr --Accrual year
2659 ,p_action_information7 => l_Holidayable_amount_1 --Holidayable amount
2660 ,p_action_information8 => l_sal_hol_acr_days --Total No of Holidays
2661 ,p_action_information9 => l_hol_pay_percentage --Holiday pay percentage
2662 ,p_action_information10 => l_holiday_pay_1 --Holiday pay
2663 ,p_action_information11 => l_hol_pay_sick_1 --Holiday pay for sicknes
2664 ,p_action_information12 => l_hol_pay_per_holiday_1 --Holiday pay per holiday
2665 ,p_action_information13 => l_tot_holiday_pay_1 --Total holiday pay
2666 ,p_action_information14 => l_sal_hol_acr_amb --Calculated AMB contribution
2667 ,p_action_information15 => l_taxable_hol_pay_1 --Tax able holiday pay
2668 ,p_action_information16 => l_sal_hol_acr_tax --Calculated Tax
2669 ,p_action_information17 => l_holiday_pay_1 --Net holiday pay
2670 ,p_action_information18 => l_profit_free_ch_ac_1 --Profit from free choice account
2671 ,p_action_information19 => l_net_hol_pay_1 --Net holiday pay
2672 ,p_action_information20 => g_msg_txt --Free Text Field
2673 ,p_action_information21 => l_hol_days_paid_1
2674 ,p_action_information22 => l_hol_pay_paid_1
2675 ,p_action_information23 => l_sal_hol_pay_1
2676 ,p_action_information24 => l_sal_hol_days_1
2677 ,p_action_information25 => null
2678 ,p_action_information26 => null
2679 ,p_action_information27 => null
2680 ,p_action_information28 => null
2681 ,p_action_information29 => null
2682 ,p_action_information30 => l_payroll_id --Payroll_ID
2683 );
2684 fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 20: Inserted PYDKEHOLCA - 2');
2685 l_hol_pay_percentage := NULL;
2686 END IF;
2687
2688 IF l_hourly_salaried='S' /* AND l_holiday_pay_2 <> 0 */ THEN
2689 --fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 15-3');
2690 --YEAR2
2691 pay_action_information_api.create_action_information (
2692 p_action_information_id => l_action_info_id
2693 ,p_action_context_id => p_assignment_action_id
2694 ,p_action_context_type => 'AAP'
2695 ,p_object_version_number => l_ovn
2696 ,p_effective_date => g_effective_date
2697 ,p_source_id => NULL
2698 ,p_source_text => NULL
2699 ,p_action_information_category => 'EMEA REPORT INFORMATION'
2700 ,p_action_information1 => 'PYDKEHOLCA'
2701 ,p_action_information2 => l_payroll_action_id
2702 ,p_action_information3 => 'DK HCARD INFO'
2703 ,p_action_information4 => rg_csr_get_person_details.person_id --Person ID
2704 ,p_action_information5 => rg_csr_get_person_details.assignment_id --Assignment ID
2705 ,p_action_information6 => l_nxt_entit_yr --Accrual year
2706 ,p_action_information7 => l_nxt_entit_yr||'-01-01' --Accrual year From
2707 ,p_action_information8 => l_nxt_entit_yr||'-12-31' --Accrual year To
2708 ,p_action_information9 => rg_csr_assignments_dtl.FH_CODE --FH Code
2709 ,p_action_information10 => rg_csr_get_bank_details.Account_Number --Bank A/c #
2710 ,p_action_information11 => l_hourly_salaried --Salaried/Hourly (S/H)
2711 ,p_action_information12 => l_5d_6d --5/6 Day Working
2712 ,p_action_information13 => rg_csr_get_person_details.full_name --Employee Name
2713 ,p_action_information14 => rg_csr_get_per_address.address_line1 --ADDRESS1
2714 ,p_action_information15 => rg_csr_get_per_address.address_line2 --ADDRESS2
2715 ,p_action_information16 => rg_csr_get_per_address.address_line3 --ADDRESS3
2716 ,p_action_information17 => rg_csr_get_per_address.town_or_city --ADDRESS4
2717 ,p_action_information18 => l_postal_code --ADDRESS5
2718 ,p_action_information19 => l_country --ADDRESS6
2719 ,p_action_information20 => rg_csr_get_le_details.NAME --LE Name
2720 ,p_action_information21 => rg_csr_get_le_details.ad_line1 --LE ADDRESS1
2721 ,p_action_information22 => rg_csr_get_le_details.ad_line2 --LE ADDRESS2
2722 ,p_action_information23 => rg_csr_get_le_details.ad_line3 --LE ADDRESS3
2723 ,p_action_information24 => rg_csr_get_le_details.town_or_city --LE ADDRESS4
2724 ,p_action_information25 => l_le_postal_code --LE ADDRESS5
2725 ,p_action_information26 => l_le_country --LE ADDRESS6
2726 ,p_action_information27 => null
2727 ,p_action_information28 => null
2728 ,p_action_information29 => null
2729 ,p_action_information30 => l_payroll_id --Payroll_ID
2730 );
2731
2732 OPEN cur_get_hol_pay_perc(to_date('31-12'||l_nxt_entit_yr,'dd-mm-rrrr'), l_assignment_id );
2733 FETCH cur_get_hol_pay_perc into l_hol_pay_percentage;
2734 CLOSE cur_get_hol_pay_perc;
2735
2736 fnd_file.put_line(fnd_file.log,'7-Fetched cur_get_hol_pay_perc');
2737 fnd_file.put_line(fnd_file.log,'7-l_hol_pay_percentage:'||l_hol_pay_percentage);
2738
2739 IF l_hol_pay_percentage IS NULL
2740 THEN
2741 l_hol_pay_percentage := NVL(rg_csr_assignments_dtl.SAL_ALLOW_RATE,rg_csr_get_hol_entit.SAL_ALLOW_RATE);
2742 END IF;
2743
2744 fnd_file.put_line(fnd_file.log,'8-l_hol_pay_percentage:'||l_hol_pay_percentage);
2745
2746 l_Holidayable_amount_2 := PAY_DK_HOLIDAY_PAY_PKG.get_prev_bal
2747 (rg_csr_get_person_details.assignment_id
2748 , 'Holidayable Pay'
2749 , '_ASG_YTD'
2750 , to_date('31-12-'||l_nxt_entit_yr,'dd-mm-rrrr')
2751 );
2752 fnd_file.put_line(fnd_file.log,'l_Holidayable_amount_2:'||l_Holidayable_amount_2);
2753
2754 pay_action_information_api.create_action_information (
2755 p_action_information_id => l_action_info_id
2756 ,p_action_context_id => p_assignment_action_id
2757 ,p_action_context_type => 'AAP'
2758 ,p_object_version_number => l_ovn
2759 ,p_effective_date => g_effective_date
2760 ,p_source_id => NULL
2761 ,p_source_text => NULL
2762 ,p_action_information_category => 'EMEA REPORT INFORMATION'
2763 ,p_action_information1 => 'PYDKEHOLCA'
2764 ,p_action_information2 => l_payroll_action_id
2765 ,p_action_information3 => 'DK HCARD INFO ADD'
2766 ,p_action_information4 => rg_csr_get_person_details.person_id --Person ID
2767 ,p_action_information5 => rg_csr_get_person_details.assignment_id --Assignment ID
2768 ,p_action_information6 => l_nxt_entit_yr --Accrual year
2769 ,p_action_information7 => l_Holidayable_amount_2 --Holidayable amount
2770 ,p_action_information8 => l_sal_hol_nxt_ent_days --Total No of Holidays
2771 ,p_action_information9 => l_hol_pay_percentage --Holiday pay percentage
2772 ,p_action_information10 => l_holiday_pay_2 --Holiday pay
2773 ,p_action_information11 => l_hol_pay_sick_2 --Holiday pay for sicknes
2774 ,p_action_information12 => l_hol_pay_per_holiday_2 --Holiday pay per holiday
2775 ,p_action_information13 => l_tot_holiday_pay_2 --Total holiday pay
2776 ,p_action_information14 => l_sal_hol_nxt_ent_amb --Calculated AMB contribution
2777 ,p_action_information15 => l_taxable_hol_pay_2 --Tax able holiday pay
2778 ,p_action_information16 => l_sal_hol_nxt_ent_tax --Calculated Tax
2779 ,p_action_information17 => l_holiday_pay_2 --Net holiday pay
2780 ,p_action_information18 => l_profit_free_ch_ac_2 --Profit from free choice account
2781 ,p_action_information19 => l_net_hol_pay_2 --Net holiday pay
2782 ,p_action_information20 => g_msg_txt --Free Text Field
2783 ,p_action_information21 => null
2784 ,p_action_information22 => null
2785 ,p_action_information23 => null
2786 ,p_action_information24 => null
2787 ,p_action_information25 => null
2788 ,p_action_information26 => null
2789 ,p_action_information27 => null
2790 ,p_action_information28 => null
2791 ,p_action_information29 => null
2792 ,p_action_information30 => l_payroll_id --Payroll_ID
2793 );
2794 l_hol_pay_percentage := NULL;
2795 fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 20: Inserted PYDKEHOLCA - 3');
2796 END IF;
2797
2798 IF l_hourly_salaried='S' /* AND l_holiday_pay_3 <> 0 */ THEN
2799 --fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 15-2');
2800 --YEAR3
2801 pay_action_information_api.create_action_information (
2802 p_action_information_id => l_action_info_id
2803 ,p_action_context_id => p_assignment_action_id
2804 ,p_action_context_type => 'AAP'
2805 ,p_object_version_number => l_ovn
2806 ,p_effective_date => g_effective_date
2807 ,p_source_id => NULL
2808 ,p_source_text => NULL
2809 ,p_action_information_category => 'EMEA REPORT INFORMATION'
2810 ,p_action_information1 => 'PYDKEHOLCA'
2811 ,p_action_information2 => l_payroll_action_id
2812 ,p_action_information3 => 'DK HCARD INFO'
2813 ,p_action_information4 => rg_csr_get_person_details.person_id --Person ID
2814 ,p_action_information5 => rg_csr_get_person_details.assignment_id --Assignment ID
2815 ,p_action_information6 => l_curr_entit_yr --Accrual year
2816 ,p_action_information7 => l_curr_entit_yr||'-01-01' --Accrual year From
2817 ,p_action_information8 => l_curr_entit_yr||'-12-31' --Accrual year To
2818 ,p_action_information9 => rg_csr_assignments_dtl.FH_CODE --FH Code
2819 ,p_action_information10 => rg_csr_get_bank_details.Account_Number --Bank A/c #
2820 ,p_action_information11 => l_hourly_salaried --Salaried/Hourly (S/H)
2821 ,p_action_information12 => l_5d_6d --5/6 Day Working
2822 ,p_action_information13 => rg_csr_get_person_details.full_name --Employee Name
2823 ,p_action_information14 => rg_csr_get_per_address.address_line1 --ADDRESS1
2824 ,p_action_information15 => rg_csr_get_per_address.address_line2 --ADDRESS2
2825 ,p_action_information16 => rg_csr_get_per_address.address_line3 --ADDRESS3
2826 ,p_action_information17 => rg_csr_get_per_address.town_or_city --ADDRESS4
2827 ,p_action_information18 => l_postal_code --ADDRESS5
2828 ,p_action_information19 => l_country --ADDRESS6
2829 ,p_action_information20 => rg_csr_get_le_details.NAME --LE Name
2830 ,p_action_information21 => rg_csr_get_le_details.ad_line1 --LE ADDRESS1
2831 ,p_action_information22 => rg_csr_get_le_details.ad_line2 --LE ADDRESS2
2832 ,p_action_information23 => rg_csr_get_le_details.ad_line3 --LE ADDRESS3
2833 ,p_action_information24 => rg_csr_get_le_details.town_or_city --LE ADDRESS4
2834 ,p_action_information25 => l_le_postal_code --LE ADDRESS5
2835 ,p_action_information26 => l_le_country --LE ADDRESS6
2836 ,p_action_information27 => null
2837 ,p_action_information28 => null
2838 ,p_action_information29 => null
2839 ,p_action_information30 => l_payroll_id --Payroll_ID
2840 );
2841
2842 OPEN cur_get_hol_pay_perc(to_date('31-12'||l_curr_entit_yr,'dd-mm-rrrr'), l_assignment_id );
2843 FETCH cur_get_hol_pay_perc into l_hol_pay_percentage;
2844 CLOSE cur_get_hol_pay_perc;
2845
2846 fnd_file.put_line(fnd_file.log,'5-Fetched cur_get_hol_pay_perc');
2847 fnd_file.put_line(fnd_file.log,'5-l_hol_pay_percentage:'||l_hol_pay_percentage);
2848
2849 IF l_hol_pay_percentage IS NULL
2850 THEN
2851 l_hol_pay_percentage := NVL(rg_csr_assignments_dtl.SAL_ALLOW_RATE,rg_csr_get_hol_entit.SAL_ALLOW_RATE);
2852 END IF;
2853
2854 fnd_file.put_line(fnd_file.log,'6-l_hol_pay_percentage:'||l_hol_pay_percentage);
2855
2856 l_Holidayable_amount_3 := PAY_DK_HOLIDAY_PAY_PKG.get_prev_bal
2857 (rg_csr_get_person_details.assignment_id
2858 , 'Holidayable Pay'
2859 , '_ASG_YTD'
2860 , to_date('31-12-'||l_curr_entit_yr,'dd-mm-rrrr')
2861 );
2862 fnd_file.put_line(fnd_file.log,'l_Holidayable_amount_3:'||l_Holidayable_amount_3);
2863
2864 pay_action_information_api.create_action_information (
2865 p_action_information_id => l_action_info_id
2866 ,p_action_context_id => p_assignment_action_id
2867 ,p_action_context_type => 'AAP'
2868 ,p_object_version_number => l_ovn
2869 ,p_effective_date => g_effective_date
2870 ,p_source_id => NULL
2871 ,p_source_text => NULL
2872 ,p_action_information_category => 'EMEA REPORT INFORMATION'
2873 ,p_action_information1 => 'PYDKEHOLCA'
2874 ,p_action_information2 => l_payroll_action_id
2875 ,p_action_information3 => 'DK HCARD INFO ADD'
2876 ,p_action_information4 => rg_csr_get_person_details.person_id --Person ID
2877 ,p_action_information5 => rg_csr_get_person_details.assignment_id --Assignment ID
2878 ,p_action_information6 => l_curr_entit_yr --Accrual year
2879 ,p_action_information7 => l_Holidayable_amount_3 --Holidayable amount
2880 ,p_action_information8 => l_sal_hol_cur_ent_days --Total No of Holidays
2881 ,p_action_information9 => l_hol_pay_percentage --Holiday pay percentage
2882 ,p_action_information10 => l_holiday_pay_3 --Holiday pay
2883 ,p_action_information11 => l_hol_pay_sick_3 --Holiday pay for sicknes
2884 ,p_action_information12 => l_hol_pay_per_holiday_3 --Holiday pay per holiday
2885 ,p_action_information13 => l_tot_holiday_pay_3 --Total holiday pay
2886 ,p_action_information14 => l_sal_hol_cur_ent_amb --Calculated AMB contribution
2887 ,p_action_information15 => l_taxable_hol_pay_3 --Tax able holiday pay
2888 ,p_action_information16 => l_sal_hol_cur_ent_tax --Calculated Tax
2889 ,p_action_information17 => l_holiday_pay_3 --Net holiday pay
2890 ,p_action_information18 => l_profit_free_ch_ac_3 --Profit from free choice account
2891 ,p_action_information19 => l_net_hol_pay_3 --Net holiday pay
2892 ,p_action_information20 => g_msg_txt --Free Text Field
2893 ,p_action_information21 => null
2894 ,p_action_information22 => null
2895 ,p_action_information23 => null
2896 ,p_action_information24 => null
2897 ,p_action_information25 => null
2898 ,p_action_information26 => null
2899 ,p_action_information27 => null
2900 ,p_action_information28 => null
2901 ,p_action_information29 => null
2902 ,p_action_information30 => l_payroll_id --Payroll_ID
2903 );
2904 l_hol_pay_percentage := NULL;
2905 fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 20: Inserted PYDKEHOLCA - 4');
2906 END IF;
2907 END IF;
2908 --13463885
2909
2910 ELSIF l_report_type = 'PYDKEHOLCA_ANN' THEN
2911
2912 fnd_file.put_line(fnd_file.log,'ARCHIVE CODE PYDKEHOLCA_ANN');
2913
2914 IF (l_use_holiday_card ='Y' AND l_uhc_payroll ='Y') AND l_usr_tbl_exist_400 = 'N' THEN
2915
2916 l_holiday_pay_hr := NVL(pay_balance_pkg.get_value(get_defined_balance_id('Hourly Holiday Pay','_ASG_HOLIDAY_YTD'),l_max_asg_act_id,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'TRUE'),0);
2917 fnd_file.put_line(fnd_file.log,'l_holiday_pay_hr:'||l_holiday_pay_hr);
2918
2919 l_hol_tax_hr := NVL(pay_balance_pkg.get_value(get_defined_balance_id('Hourly Holiday Tax','_ASG_HOLIDAY_YTD'),l_max_asg_act_id,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'TRUE'),0);
2920 fnd_file.put_line(fnd_file.log,'l_hol_tax_hr:'||l_hol_tax_hr);
2921
2922 l_hol_amb_hr := NVL(pay_balance_pkg.get_value(get_defined_balance_id('Hourly Holiday AMB','_ASG_HOLIDAY_YTD'),l_max_asg_act_id,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'TRUE'),0);
2923 fnd_file.put_line(fnd_file.log,'l_hol_amb_hr:'||l_hol_amb_hr);
2924 /*
2925 l_Holidayable_amount_hr := NVL(pay_balance_pkg.get_value(get_defined_balance_id('Holidayable Pay','_ASG_HOLIDAY_YTD'),l_max_asg_act_id,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'TRUE'),0);
2926 fnd_file.put_line(fnd_file.log,'l_Holidayable_amount_hr:'||l_Holidayable_amount_hr);
2927 */
2928 l_tot_holiday_pay_hr := l_holiday_pay_hr + l_hol_tax_hr + l_hol_amb_hr ;
2929 fnd_file.put_line(fnd_file.log,'l_tot_holiday_pay_hr:'||l_tot_holiday_pay_hr);
2930
2931 l_taxable_hol_pay_hr := l_tot_holiday_pay_hr - l_hol_amb_hr ;
2932 fnd_file.put_line(fnd_file.log,'l_taxable_hol_pay_hr:'||l_taxable_hol_pay_hr);
2933
2934 l_tot_no_holidays_hr := NVL(pay_balance_pkg.get_value(get_defined_balance_id('Hourly Holiday Days','_ASG_HOLIDAY_YTD'),l_max_asg_act_id,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'TRUE'),0);
2935 fnd_file.put_line(fnd_file.log,'l_tot_no_holidays_hr:'||l_tot_no_holidays_hr);
2936
2937 IF l_tot_no_holidays_hr <> 0 THEN
2938 l_hol_pay_per_holiday_hr := (l_holiday_pay_hr / l_tot_no_holidays_hr);
2939 END IF;
2940 fnd_file.put_line(fnd_file.log,'l_hol_pay_per_holiday_hr:'||l_hol_pay_per_holiday_hr);
2941 /*
2942 l_profit_free_ch_ac_hr := NVL(pay_balance_pkg.get_value(get_defined_balance_id('Profit From Free Choice Account Accrual','_ASG_HOLIDAY_YTD'),l_max_asg_act_id,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'TRUE'),0);
2943 fnd_file.put_line(fnd_file.log,'l_profit_free_ch_ac_hr:'||l_profit_free_ch_ac_hr);
2944 */
2945 l_net_hol_pay_hr := l_holiday_pay_hr + l_profit_free_ch_ac_hr ;
2946 fnd_file.put_line(fnd_file.log,'l_net_hol_pay_hr:'||l_net_hol_pay_hr);
2947 /*
2948 l_hol_pay_sick_hr := NVL(pay_balance_pkg.get_value(get_defined_balance_id('Sick Pay Subject to Holiday Accrual','_ASG_HOLIDAY_YTD'),l_max_asg_act_id,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'TRUE'),0);
2949 fnd_file.put_line(fnd_file.log,'l_hol_pay_sick_hr:'||l_hol_pay_sick_hr);
2950 */
2951 --
2952 l_hol_days_paid_hr := NVL(pay_balance_pkg.get_value(get_defined_balance_id('Holiday Days Paid','_ASG_HOLIDAY_YTD'),l_max_asg_act_id,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'TRUE'),0);
2953 fnd_file.put_line(fnd_file.log,'l_hol_days_paid_hr:'||l_hol_days_paid_hr);
2954
2955 l_hol_pay_paid_hr := NVL(pay_balance_pkg.get_value(get_defined_balance_id('Holiday Pay Paid','_ASG_HOLIDAY_YTD'),l_max_asg_act_id,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'TRUE'),0);
2956 fnd_file.put_line(fnd_file.log,'l_hol_pay_paid_hr:'||l_hol_pay_paid_hr);
2957 /*
2958 l_hrly_hol_pay_hr := NVL(pay_balance_pkg.get_value(get_defined_balance_id('Hourly Holiday Pay','_ASG_HOLIDAY_YTD'),l_max_asg_act_id,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'TRUE'),0);
2959 fnd_file.put_line(fnd_file.log,'l_hrly_hol_pay_hr:'||l_hrly_hol_pay_hr);
2960
2961 l_hrly_hol_days_hr := NVL(pay_balance_pkg.get_value(get_defined_balance_id('Hourly Holiday Days','_ASG_HOLIDAY_YTD'),l_max_asg_act_id,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'TRUE'),0);
2962 fnd_file.put_line(fnd_file.log,'l_hrly_hol_days_hr:'||l_hrly_hol_days_hr);
2963 */
2964 --
2965 pay_action_information_api.create_action_information (
2966 p_action_information_id => l_action_info_id
2967 ,p_action_context_id => p_assignment_action_id
2968 ,p_action_context_type => 'AAP'
2969 ,p_object_version_number => l_ovn
2970 ,p_effective_date => g_effective_date
2971 ,p_source_id => NULL
2972 ,p_source_text => NULL
2973 ,p_action_information_category => 'EMEA REPORT INFORMATION'
2974 ,p_action_information1 => 'PYDKEHOLCA_ANN'
2975 ,p_action_information2 => l_payroll_action_id
2976 ,p_action_information3 => 'DK HCARD INFO'
2977 ,p_action_information4 => rg_csr_get_person_details.person_id --Person ID
2978 ,p_action_information5 => rg_csr_get_person_details.assignment_id --Assignment ID
2979 ,p_action_information6 => l_accr_yr --Accrual year
2980 ,p_action_information7 => l_accr_yr||'-01-01' --Accrual year From
2981 ,p_action_information8 => l_accr_yr||'-12-31' --Accrual year To
2982 ,p_action_information9 => rg_csr_assignments_dtl.FH_CODE --FH Code
2983 ,p_action_information10 => rg_csr_get_bank_details.Account_Number --Bank A/c #
2984 ,p_action_information11 => l_hourly_salaried --Salaried/Hourly (S/H)
2985 ,p_action_information12 => l_5d_6d --5/6 Day Working
2986 ,p_action_information13 => rg_csr_get_person_details.full_name --Employee Name
2987 ,p_action_information14 => rg_csr_get_per_address.address_line1 --ADDRESS1
2988 ,p_action_information15 => rg_csr_get_per_address.address_line2 --ADDRESS2
2989 ,p_action_information16 => rg_csr_get_per_address.address_line3 --ADDRESS3
2990 ,p_action_information17 => rg_csr_get_per_address.town_or_city --ADDRESS4
2991 ,p_action_information18 => l_postal_code --ADDRESS5
2992 ,p_action_information19 => l_country --ADDRESS6
2993 ,p_action_information20 => rg_csr_get_le_details.NAME --LE Name
2994 ,p_action_information21 => rg_csr_get_le_details.ad_line1 --LE ADDRESS1
2995 ,p_action_information22 => rg_csr_get_le_details.ad_line2 --LE ADDRESS2
2996 ,p_action_information23 => rg_csr_get_le_details.ad_line3 --LE ADDRESS3
2997 ,p_action_information24 => rg_csr_get_le_details.town_or_city --LE ADDRESS4
2998 ,p_action_information25 => l_le_postal_code --LE ADDRESS5
2999 ,p_action_information26 => l_le_country --LE ADDRESS6
3000 ,p_action_information27 => null
3001 ,p_action_information28 => null
3002 ,p_action_information29 => null
3003 ,p_action_information30 => l_payroll_id --Payroll_ID
3004 );
3005
3006 fnd_file.put_line(fnd_file.log,'X-Fetched cur_get_hol_pay_perc');
3007
3008 OPEN cur_get_hol_pay_perc(to_date('31-12'||l_accr_yr,'dd-mm-rrrr'), l_assignment_id );
3009 FETCH cur_get_hol_pay_perc into l_hol_pay_percentage;
3010 CLOSE cur_get_hol_pay_perc;
3011
3012 fnd_file.put_line(fnd_file.log,'X-l_hol_pay_percentage:'||l_hol_pay_percentage);
3013
3014 IF l_hol_pay_percentage IS NULL
3015 THEN
3016 l_hol_pay_percentage := NVL(rg_csr_assignments_dtl.SAL_ALLOW_RATE,rg_csr_get_hol_entit.SAL_ALLOW_RATE);
3017 END IF;
3018
3019 fnd_file.put_line(fnd_file.log,'Y-l_hol_pay_percentage:'||l_hol_pay_percentage);
3020
3021 pay_action_information_api.create_action_information (
3022 p_action_information_id => l_action_info_id
3023 ,p_action_context_id => p_assignment_action_id
3024 ,p_action_context_type => 'AAP'
3025 ,p_object_version_number => l_ovn
3026 ,p_effective_date => g_effective_date
3027 ,p_source_id => NULL
3028 ,p_source_text => NULL
3029 ,p_action_information_category => 'EMEA REPORT INFORMATION'
3030 ,p_action_information1 => 'PYDKEHOLCA_ANN'
3031 ,p_action_information2 => l_payroll_action_id
3032 ,p_action_information3 => 'DK HCARD INFO ADD'
3033 ,p_action_information4 => rg_csr_get_person_details.person_id --Person ID
3034 ,p_action_information5 => rg_csr_get_person_details.assignment_id --Assignment ID
3035 ,p_action_information6 => l_accr_yr --Accrual year
3036 ,p_action_information7 => l_Holidayable_amount_hr --Holidayable amount
3037 ,p_action_information8 => l_tot_no_holidays_hr --Total No of Holidays
3038 ,p_action_information9 => l_hol_pay_percentage --Holiday pay percentage
3039 ,p_action_information10 => l_holiday_pay_hr --Holiday pay
3040 ,p_action_information11 => l_hol_pay_sick_hr --Holiday pay for sicknes
3041 ,p_action_information12 => l_hol_pay_per_holiday_hr --Holiday pay per holiday
3042 ,p_action_information13 => l_tot_holiday_pay_hr --Total holiday pay
3043 ,p_action_information14 => l_hol_amb_hr --Calculated AMB contribution
3044 ,p_action_information15 => l_taxable_hol_pay_hr --Tax able holiday pay
3045 ,p_action_information16 => l_hol_tax_hr --Calculated Tax
3046 ,p_action_information17 => l_holiday_pay_hr --Net holiday pay
3047 ,p_action_information18 => l_profit_free_ch_ac_hr --Profit from free choice account
3048 ,p_action_information19 => l_net_hol_pay_hr --Net holiday pay
3049 ,p_action_information20 => g_msg_txt --Free Text Field
3050 ,p_action_information21 => l_hol_days_paid_hr --Holidays spend
3051 ,p_action_information22 => l_hol_pay_paid_hr --Holiday pay paid out
3052 ,p_action_information23 => l_hrly_hol_pay_hr --Remaining net holiday pay
3053 ,p_action_information24 => l_hrly_hol_days_hr --Remaining holidays
3054 ,p_action_information25 => null
3055 ,p_action_information26 => null
3056 ,p_action_information27 => null
3057 ,p_action_information28 => null
3058 ,p_action_information29 => null
3059 ,p_action_information30 => l_payroll_id --Payroll_ID
3060 );
3061 l_hol_pay_percentage := NULL;
3062 fnd_file.put_line(fnd_file.log,'ARCHIVE CODE PYDKEHOLCA_ANN - 1:');
3063 END IF;
3064
3065 ELSIF l_report_type = 'PYDKEHOLCA_MIA' THEN
3066
3067 fnd_file.put_line(fnd_file.log,'ARCHIVE CODE PYDKEHOLCA_MIA');
3068
3069 IF (l_use_holiday_card ='Y' AND l_uhc_payroll ='Y') AND l_usr_tbl_exist_400 = 'N' THEN
3070
3071 IF NVL(rg_Legal_Emp_DA_Codes.ORG_INFORMATION5,'GROSS') = 'NET'
3072 THEN
3073 l_spl_hol_uni_agr_net_acc := NVL(pay_balance_pkg.get_value(get_defined_balance_id('Special Holiday Union Agreement Accrual','_ASG_PTD'),l_max_asg_act_id,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'TRUE'),0);
3074 fnd_file.put_line(fnd_file.log,'l_spl_hol_uni_agr_ne_acct:'||l_spl_hol_uni_agr_net_acc);
3075 ELSE
3076 l_spl_hol_uni_agr_gross_acc := NVL(pay_balance_pkg.get_value(get_defined_balance_id('Special Holiday Union Agreement Accrual','_ASG_PTD'),l_max_asg_act_id,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'TRUE'),0);
3077 fnd_file.put_line(fnd_file.log,'l_spl_hol_uni_agr_gross_acc:'||l_spl_hol_uni_agr_gross_acc);
3078 END IF;
3079
3080 IF NVL(rg_Legal_Emp_DA_Codes.ORG_INFORMATION6,'GROSS') = 'NET'
3081 THEN
3082 l_pro_free_ch_ac_net_acc := NVL(pay_balance_pkg.get_value(get_defined_balance_id('Profit From Free Choice Account Accrual','_ASG_PTD'),l_max_asg_act_id,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'TRUE'),0);
3083 fnd_file.put_line(fnd_file.log,'l_pro_free_ch_ac_net_acc:'||l_pro_free_ch_ac_net_acc);
3084 ELSE
3085 l_pro_free_ch_ac_gross_acc := NVL(pay_balance_pkg.get_value(get_defined_balance_id('Profit From Free Choice Account Accrual','_ASG_PTD'),l_max_asg_act_id,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'TRUE'),0);
3086 fnd_file.put_line(fnd_file.log,'l_pro_free_ch_ac_gross_acc:'||l_pro_free_ch_ac_gross_acc);
3087 END IF;
3088
3089 l_hol_acc_days := NVL(pay_balance_pkg.get_value(get_defined_balance_id('Holiday Accrual Days','_ASG_PTD'),l_max_asg_act_id,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'TRUE'),0);
3090 fnd_file.put_line(fnd_file.log,'l_hol_acc_days:'||l_hol_acc_days);
3091
3092 l_hol_acc_amt := NVL(pay_balance_pkg.get_value(get_defined_balance_id('Holiday Accrual Amount','_ASG_PTD'),l_max_asg_act_id,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'TRUE'),0);
3093 fnd_file.put_line(fnd_file.log,'l_hol_acc_amt:'||l_hol_acc_amt);
3094
3095 --Accrual Reporting (Indberetningstype: Optjening)
3096 pay_action_information_api.create_action_information (
3097 p_action_information_id => l_action_info_id
3098 ,p_action_context_id => p_assignment_action_id
3099 ,p_action_context_type => 'AAP'
3100 ,p_object_version_number => l_ovn
3101 ,p_effective_date => g_effective_date
3102 ,p_source_id => NULL
3103 ,p_source_text => NULL
3104 ,p_action_information_category => 'EMEA REPORT INFORMATION'
3105 ,p_action_information1 => 'PYDKEHOLCA_MIA'
3106 ,p_action_information2 => l_payroll_action_id
3107 ,p_action_information3 => 'DK HCARD ACCRUAL'
3108 ,p_action_information4 => rg_csr_get_person_details.person_id --Person ID
3109 ,p_action_information5 => rg_csr_get_person_details.assignment_id --Assignment ID
3110 ,p_action_information6 => to_char(l_start_date,'RRRR') --Accrual year
3111 ,p_action_information7 => NULL --SH contribution Net pay
3112 ,p_action_information8 => NULL --SH contribution Gross pay
3113 ,p_action_information9 => l_spl_hol_uni_agr_net_acc --Special Holidays from Union Agreement Net pay
3114 ,p_action_information10 => l_spl_hol_uni_agr_gross_acc --Special Holidays from Union Agreement Gross pay
3115 ,p_action_information11 => l_pro_free_ch_ac_net_acc --Free choice account net pay
3116 ,p_action_information12 => l_pro_free_ch_ac_gross_acc --Free choice account Gross pay
3117 ,p_action_information13 => l_hol_acc_days --Holidays accrual
3118 ,p_action_information14 => l_hol_acc_amt --Holiday pay accrual
3119 ,p_action_information15 => null --Currency
3120 ,p_action_information16 => null
3121 ,p_action_information17 => null
3122 ,p_action_information18 => null
3123 ,p_action_information19 => null
3124 ,p_action_information20 => null
3125 ,p_action_information21 => null
3126 ,p_action_information22 => null
3127 ,p_action_information23 => null
3128 ,p_action_information24 => null
3129 ,p_action_information25 => null
3130 ,p_action_information26 => null
3131 ,p_action_information27 => null
3132 ,p_action_information28 => l_period_id
3133 ,p_action_information29 => 'Optjening' --
3134 ,p_action_information30 => l_payroll_id --Payroll_ID
3135 );
3136
3137 IF NVL(rg_Legal_Emp_DA_Codes.ORG_INFORMATION5,'GROSS') = 'NET'
3138 THEN
3139 l_spl_hol_uni_agr_net_pmt := NVL(pay_balance_pkg.get_value(get_defined_balance_id('Special Holiday Union Agreement Payment','_ASG_PTD'),l_max_asg_act_id,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'TRUE'),0);
3140 fnd_file.put_line(fnd_file.log,'l_spl_hol_uni_agr_net_pmt:'||l_spl_hol_uni_agr_net_pmt);
3141 ELSE
3142 l_spl_hol_uni_agr_gross_pmt := NVL(pay_balance_pkg.get_value(get_defined_balance_id('Special Holiday Union Agreement Payment','_ASG_PTD'),l_max_asg_act_id,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'TRUE'),0);
3143 fnd_file.put_line(fnd_file.log,'l_spl_hol_uni_agr_gross_pmt:'||l_spl_hol_uni_agr_gross_pmt);
3144 END IF;
3145
3146 IF NVL(rg_Legal_Emp_DA_Codes.ORG_INFORMATION6,'GROSS') = 'NET'
3147 THEN
3148 l_pro_free_ch_ac_net_pmt := NVL(pay_balance_pkg.get_value(get_defined_balance_id('Profit From Free Choice Account Payment','_ASG_PTD'),l_max_asg_act_id,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'TRUE'),0);
3149 fnd_file.put_line(fnd_file.log,'l_pro_free_ch_ac_net_pmt:'||l_pro_free_ch_ac_net_pmt);
3150 ELSE
3151 l_pro_free_ch_ac_gross_pmt := NVL(pay_balance_pkg.get_value(get_defined_balance_id('Profit From Free Choice Account Payment','_ASG_PTD'),l_max_asg_act_id,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'TRUE'),0);
3152 fnd_file.put_line(fnd_file.log,'l_pro_free_ch_ac_gross_pmt:'||l_pro_free_ch_ac_gross_pmt);
3153 END IF;
3154
3155 l_hol_days_paid := NVL(pay_balance_pkg.get_value(get_defined_balance_id('Holiday Days Paid','_ASG_PTD'),l_max_asg_act_id,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'TRUE'),0);
3156 fnd_file.put_line(fnd_file.log,'l_hol_days_paid:'||l_hol_days_paid);
3157
3158 l_hol_pay_paid := NVL(pay_balance_pkg.get_value(get_defined_balance_id('Holiday Pay Paid','_ASG_PTD'),l_max_asg_act_id,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'TRUE'),0);
3159 fnd_file.put_line(fnd_file.log,'l_hol_pay_paid:'||l_hol_pay_paid);
3160
3161 IF l_start_date >= to_date('01-01-'||to_char(l_start_date,'RRRR'),'dd-mm-rrrr') AND
3162 l_start_date <= to_date('30-04-'||to_char(l_start_date,'RRRR'),'dd-mm-rrrr')
3163 THEN
3164 l_accr_yr := to_char(l_start_date,'RRRR') - 2;
3165 ELSIF
3166 l_start_date >= to_date('01-05-'||to_char(l_start_date,'RRRR'),'dd-mm-rrrr') AND
3167 l_start_date <= to_date('31-12-'||to_char(l_start_date,'RRRR'),'dd-mm-rrrr')
3168 THEN
3169 l_accr_yr := to_char(l_start_date,'RRRR') - 1;
3170 END IF;
3171
3172 --Holidays Paid Reporting (Indberetningstype: Nedskrivning)
3173 pay_action_information_api.create_action_information (
3174 p_action_information_id => l_action_info_id
3175 ,p_action_context_id => p_assignment_action_id
3176 ,p_action_context_type => 'AAP'
3177 ,p_object_version_number => l_ovn
3178 ,p_effective_date => g_effective_date
3179 ,p_source_id => NULL
3180 ,p_source_text => NULL
3181 ,p_action_information_category => 'EMEA REPORT INFORMATION'
3182 ,p_action_information1 => 'PYDKEHOLCA_MIA'
3183 ,p_action_information2 => l_payroll_action_id
3184 ,p_action_information3 => 'DK HCARD PAYMENTS'
3185 ,p_action_information4 => rg_csr_get_person_details.person_id --Person ID
3186 ,p_action_information5 => rg_csr_get_person_details.assignment_id --Assignment ID
3187 ,p_action_information6 => l_accr_yr --Accrual year
3188 ,p_action_information7 => NULL --SH contribution Net pay
3189 ,p_action_information8 => NULL --SH contribution Gross pay
3190 ,p_action_information9 => l_spl_hol_uni_agr_net_pmt --Special Holidays from Union Agreement Net pay
3191 ,p_action_information10 => l_spl_hol_uni_agr_gross_pmt --Special Holidays from Union Agreement Gross pay
3192 ,p_action_information11 => l_pro_free_ch_ac_net_pmt --Free choice account net pay
3193 ,p_action_information12 => l_pro_free_ch_ac_gross_pmt --Free choice account Gross pay
3194 ,p_action_information13 => l_hol_days_paid --Holidays paid
3195 ,p_action_information14 => l_hol_pay_paid --Holiday pay paid
3196 ,p_action_information15 => null --Currency
3197 ,p_action_information16 => null
3198 ,p_action_information17 => null
3199 ,p_action_information18 => null
3200 ,p_action_information19 => null
3201 ,p_action_information20 => null
3202 ,p_action_information21 => null
3203 ,p_action_information22 => null
3204 ,p_action_information23 => null
3205 ,p_action_information24 => null
3206 ,p_action_information25 => null
3207 ,p_action_information26 => null
3208 ,p_action_information27 => null
3209 ,p_action_information28 => l_period_id
3210 ,p_action_information29 => 'Nedskrivning' --
3211 ,p_action_information30 => l_payroll_id --Payroll_ID
3212 );
3213
3214 fnd_file.put_line(fnd_file.log,'ARCHIVE CODE PYDKEHOLCA_MIA - 1:');
3215 END IF;
3216
3217 END IF; --l_report_type
3218
3219 END IF; --l_present_flag
3220 END LOOP; --Ref Cursor
3221
3222 --end if; -- end company terminating check condition.
3223 --end if; -- g_flag
3224
3225 -- fnd_file.put_line(fnd_file.log,'ARCHIVE CODE 16');
3226 IF g_debug THEN
3227 hr_utility.set_location(' Leaving Procedure ARCHIVE_CODE',390);
3228 END IF;
3229
3230 -- fnd_file.put_line(fnd_file.log,'ARCHIVE CODE END');
3231
3232 EXCEPTION
3233 WHEN others THEN
3234 IF g_debug THEN
3235 hr_utility.set_location('error raised in archive code ',5);
3236 END if;
3237 RAISE;
3238 END ARCHIVE_CODE;
3239
3240 PROCEDURE DEINITIALIZATION_CODE
3241 (p_payroll_action_id in pay_payroll_actions.payroll_action_id%type) is
3242 BEGIN
3243 fnd_file.put_line(fnd_file.log,'DE-INITIALIZATION CODE START 1');
3244 IF l_errflag = 'Y' THEN
3245 Fnd_file.put_line(FND_FILE.LOG,'Process completed with warning(s). Please check the output file.' );
3246 error_message := FND_CONCURRENT.SET_COMPLETION_STATUS('WARNING','Process completed with warning(s). Please check the output file.');
3247 END IF;
3248 IF l_errflag_uhc1 = 'Y' OR l_errflag_uhc = 'Y' THEN
3249 Fnd_file.put_line(FND_FILE.LOG,'Process completed with error(s). Please check the output file.' );
3250 error_message := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR','Process completed with error(s). Please check the output file.');
3251 END IF;
3252
3253 fnd_file.put_line(fnd_file.log,'DE-INITIALIZATION CODE END 2');
3254 EXCEPTION
3255 WHEN others THEN
3256 IF g_debug THEN
3257 hr_utility.set_location('error raised in DEINITIALIZATION_CODE ',999);
3258 END if;
3259 RAISE;
3260 END;
3261
3262 PROCEDURE generate_xml(
3263 errbuf out nocopy varchar2
3264 ,retcode out nocopy varchar2
3265 ,p_payroll_act_id in number
3266 ) IS
3267
3268 CURSOR cur_emea_report_info(p_payroll_action_id IN NUMBER, p_report_type VARCHAR2) IS
3269 SELECT *
3270 FROM
3271 PAY_ACTION_INFORMATION
3272 WHERE
3273 ACTION_CONTEXT_ID = p_payroll_action_id
3274 AND ACTION_CONTEXT_TYPE = 'PA'
3275 AND ACTION_INFORMATION_CATEGORY = 'EMEA REPORT INFORMATION'
3276 AND ACTION_INFORMATION1 = p_report_type; --'PYDKEHOLCA';
3277
3278 cur_emea_report_info_rec cur_emea_report_info%ROWTYPE;
3279
3280 CURSOR cur_all_payrolls(p_payroll_action_id IN NUMBER, p_report_type VARCHAR2) IS
3281 SELECT distinct PAI.ACTION_INFORMATION30 Payroll_id
3282 FROM
3283 PAY_ASSIGNMENT_ACTIONS PAA,
3284 PAY_ACTION_INFORMATION PAI
3285 WHERE
3286 PAA.PAYROLL_ACTION_ID = p_payroll_action_id
3287 AND PAI.ACTION_CONTEXT_ID = PAA.ASSIGNMENT_ACTION_ID
3288 AND PAI.ACTION_CONTEXT_TYPE = 'AAP'
3289 AND PAI.ACTION_INFORMATION_CATEGORY = 'EMEA REPORT INFORMATION'
3290 AND PAI.ACTION_INFORMATION1 = p_report_type --'PYDKEHOLCA'
3291 AND PAI.ACTION_INFORMATION3 = 'DK EMPLOYEE DETAILS';
3292
3293 CURSOR cur_emp_dtls(p_payroll_action_id NUMBER, p_payroll_id PAY_ACTION_INFORMATION.ACTION_INFORMATION30%TYPE
3294 ,p_report_type VARCHAR2)
3295 IS
3296 SELECT PAI.*
3297 FROM
3298 PAY_ASSIGNMENT_ACTIONS PAA,
3299 PAY_ACTION_INFORMATION PAI
3300 WHERE
3301 PAA.PAYROLL_ACTION_ID = p_payroll_action_id
3302 AND PAI.ACTION_CONTEXT_ID = PAA.ASSIGNMENT_ACTION_ID
3303 AND PAI.ACTION_CONTEXT_TYPE = 'AAP'
3304 AND PAI.ACTION_INFORMATION_CATEGORY = 'EMEA REPORT INFORMATION'
3305 AND PAI.ACTION_INFORMATION1 = p_report_type --'PYDKEHOLCA'
3306 AND PAI.ACTION_INFORMATION3 = 'DK EMPLOYEE DETAILS'
3307 AND PAI.ACTION_INFORMATION30 = p_payroll_id
3308 ORDER BY PAI.ACTION_INFORMATION4, PAI.ACTION_INFORMATION5;
3309
3310 CURSOR cur_dk_hcard_info(p_payroll_action_id NUMBER,
3311 p_payroll_id PAY_ACTION_INFORMATION.ACTION_INFORMATION30%TYPE,
3312 p_person_id PAY_ACTION_INFORMATION.ACTION_INFORMATION4%TYPE,
3313 p_asg_id PAY_ACTION_INFORMATION.ACTION_INFORMATION5%TYPE,
3314 p_hcard_info VARCHAR2,
3315 p_report_type VARCHAR2) IS
3316 SELECT PAI.*
3317 FROM
3318 PAY_ASSIGNMENT_ACTIONS PAA,
3319 PAY_ACTION_INFORMATION PAI
3320 WHERE
3321 PAA.PAYROLL_ACTION_ID = p_payroll_action_id
3322 AND PAI.ACTION_CONTEXT_ID = PAA.ASSIGNMENT_ACTION_ID
3323 AND PAI.ACTION_CONTEXT_TYPE = 'AAP'
3324 AND PAI.ACTION_INFORMATION_CATEGORY = 'EMEA REPORT INFORMATION'
3325 AND PAI.ACTION_INFORMATION1 = p_report_type --'PYDKEHOLCA'
3326 AND PAI.ACTION_INFORMATION3 = p_hcard_info --'DK HCARD INFO' / 'DK HCARD INFO ADD'
3327 AND PAI.ACTION_INFORMATION30 = p_payroll_id
3328 AND PAI.ACTION_INFORMATION4 = p_person_id
3329 AND PAI.ACTION_INFORMATION5 = p_asg_id
3330 ORDER BY ACTION_INFORMATION6 DESC;
3331
3332 CURSOR cur_dk_hcard_info_add(p_payroll_action_id NUMBER,
3333 p_payroll_id PAY_ACTION_INFORMATION.ACTION_INFORMATION30%TYPE,
3334 p_person_id PAY_ACTION_INFORMATION.ACTION_INFORMATION4%TYPE,
3335 p_asg_id PAY_ACTION_INFORMATION.ACTION_INFORMATION5%TYPE,
3336 p_hcard_info VARCHAR2,
3337 p_year PAY_ACTION_INFORMATION.ACTION_INFORMATION6%TYPE,
3338 p_report_type VARCHAR2) IS
3339 SELECT PAI.*
3340 FROM
3341 PAY_ASSIGNMENT_ACTIONS PAA,
3342 PAY_ACTION_INFORMATION PAI
3343 WHERE
3344 PAA.PAYROLL_ACTION_ID = p_payroll_action_id
3345 AND PAI.ACTION_CONTEXT_ID = PAA.ASSIGNMENT_ACTION_ID
3346 AND PAI.ACTION_CONTEXT_TYPE = 'AAP'
3347 AND PAI.ACTION_INFORMATION_CATEGORY = 'EMEA REPORT INFORMATION'
3348 AND PAI.ACTION_INFORMATION1 = p_report_type --'PYDKEHOLCA'
3349 AND PAI.ACTION_INFORMATION3 = p_hcard_info --'DK HCARD INFO' / 'DK HCARD INFO ADD'
3350 AND PAI.ACTION_INFORMATION30 = p_payroll_id
3351 AND PAI.ACTION_INFORMATION4 = p_person_id
3352 AND PAI.ACTION_INFORMATION5 = p_asg_id
3353 AND PAI.ACTION_INFORMATION6 = p_year
3354 ORDER BY ACTION_INFORMATION6 DESC;
3355
3356 CURSOR cur_all_periods(p_payroll_action_id IN NUMBER)
3357 IS
3358 SELECT
3359 ACTION_INFORMATION29 Period_id,
3360 ACTION_INFORMATION27 period_from, --14078879
3361 ACTION_INFORMATION28 period_to, --14078879
3362 ACTION_INFORMATION30 payroll_id
3363 FROM
3364 PAY_ACTION_INFORMATION
3365 WHERE
3366 ACTION_INFORMATION1 = 'PYDKEHOLCA_MIA'
3367 AND ACTION_INFORMATION2 = p_payroll_action_id
3368 AND ACTION_INFORMATION3 = 'DK EMPLOYEE DETAILS'
3369 --AND ACTION_INFORMATION30 = p_payroll_id
3370 AND ACTION_INFORMATION4 IS NOT NULL --14078879
3371 AND ACTION_INFORMATION5 IS NOT NULL --14078879
3372 GROUP BY ACTION_INFORMATION30, ACTION_INFORMATION29, ACTION_INFORMATION28, ACTION_INFORMATION27;
3373
3374 CURSOR cur_emp_dtls_mia(p_payroll_action_id NUMBER, p_payroll_id PAY_ACTION_INFORMATION.ACTION_INFORMATION30%TYPE
3375 ,p_period_id PAY_ACTION_INFORMATION.ACTION_INFORMATION29%TYPE)
3376 IS
3377 SELECT PAI.*
3378 FROM
3379 PAY_ASSIGNMENT_ACTIONS PAA,
3380 PAY_ACTION_INFORMATION PAI
3381 WHERE
3382 PAA.PAYROLL_ACTION_ID = p_payroll_action_id
3383 AND PAI.ACTION_CONTEXT_ID = PAA.ASSIGNMENT_ACTION_ID
3384 AND PAI.ACTION_CONTEXT_TYPE = 'AAP'
3385 AND PAI.ACTION_INFORMATION_CATEGORY = 'EMEA REPORT INFORMATION'
3386 AND PAI.ACTION_INFORMATION1 = 'PYDKEHOLCA_MIA'
3387 AND PAI.ACTION_INFORMATION3 = 'DK EMPLOYEE DETAILS'
3388 AND PAI.ACTION_INFORMATION30 = p_payroll_id
3389 AND PAI.ACTION_INFORMATION29 = p_period_id
3390 AND PAI.ACTION_INFORMATION4 IS NOT NULL --14078879
3391 AND PAI.ACTION_INFORMATION5 IS NOT NULL --14078879
3392 ORDER BY PAI.ACTION_INFORMATION4, PAI.ACTION_INFORMATION5;
3393
3394 CURSOR cur_dk_hcard_accr_pmnt(p_payroll_action_id NUMBER,
3395 p_payroll_id PAY_ACTION_INFORMATION.ACTION_INFORMATION30%TYPE,
3396 p_person_id PAY_ACTION_INFORMATION.ACTION_INFORMATION4%TYPE,
3397 p_asg_id PAY_ACTION_INFORMATION.ACTION_INFORMATION5%TYPE,
3398 p_period_id PAY_ACTION_INFORMATION.ACTION_INFORMATION28%TYPE,
3399 p_hcard_info PAY_ACTION_INFORMATION.ACTION_INFORMATION3%TYPE) IS
3400 SELECT PAI.*
3401 FROM
3402 PAY_ASSIGNMENT_ACTIONS PAA,
3403 PAY_ACTION_INFORMATION PAI
3404 WHERE
3405 PAA.PAYROLL_ACTION_ID = p_payroll_action_id
3406 AND PAI.ACTION_CONTEXT_ID = PAA.ASSIGNMENT_ACTION_ID
3407 AND PAI.ACTION_CONTEXT_TYPE = 'AAP'
3408 AND PAI.ACTION_INFORMATION_CATEGORY = 'EMEA REPORT INFORMATION'
3409 AND PAI.ACTION_INFORMATION1 = 'PYDKEHOLCA_MIA'
3410 AND PAI.ACTION_INFORMATION3 = p_hcard_info --'DK HCARD ACCRUAL' / DK HCARD PAYMENTS
3411 AND PAI.ACTION_INFORMATION30 = p_payroll_id
3412 AND PAI.ACTION_INFORMATION4 = p_person_id
3413 AND PAI.ACTION_INFORMATION5 = p_asg_id
3414 AND PAI.ACTION_INFORMATION28 = p_period_id;
3415 --ORDER BY ACTION_INFORMATION6 DESC;
3416
3417 Cursor cur_report_type IS
3418 select
3419 report_type
3420 from
3421 pay_payroll_actions
3422 where payroll_action_id = p_payroll_act_id;
3423
3424 l_report_type VARCHAR2(100);
3425
3426
3427 l_business_group_id NUMBER;
3428 l_test_submission VARCHAR2(20);
3429 l_Funktionaer VARCHAR2(10);
3430 l_Arbejder VARCHAR2(10);
3431 l_Optjent_5_dages_uge VARCHAR2(10);
3432 l_Optjent_6_dages_uge VARCHAR2(10);
3433 l_Udskriv_Fritekstfelt VARCHAR2(10);
3434
3435 l_version VARCHAR2(200);
3436 l_root_tag VARCHAR2(50):= '<Indberetninger>';
3437 l_root_tag_end VARCHAR2(50):= '</Indberetninger>';
3438 BEGIN
3439
3440 FND_FILE.PUT_LINE(FND_FILE.LOG, '1: p_payroll_act_id:' || p_payroll_act_id);
3441
3442 OPEN cur_report_type;
3443 FETCH cur_report_type INTO l_report_type;
3444 CLOSE cur_report_type;
3445
3446 fnd_file.put_line(fnd_file.log,'l_report_type generate_xml:'||l_report_type);
3447
3448 PAY_DK_ARCHIVE_EHOL.GET_ALL_PARAMETERS(
3449 p_payroll_act_id
3450 ,l_business_group_id
3451 ,g_legal_employer_id
3452 ,g_effective_date
3453 ,g_payroll_id
3454 ,g_year
3455 ,g_payroll_type
3456 ,g_start_date
3457 ,g_end_date
3458 ,g_test_submission
3459 ,g_company_terminating
3460 ,g_msg_txt) ;
3461
3462 FND_FILE.PUT_LINE(FND_FILE.LOG, '2: l_business_group_id:' ||l_business_group_id);
3463
3464 IF g_test_submission = 'N' THEN
3465 l_test_submission := 'PROD';
3466 ELSE
3467 l_test_submission := 'TEST';
3468 END IF;
3469
3470 l_version := '<?xml version="1.0" encoding="UTF-8" standalone="no"?>';
3471
3472 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_version);
3473
3474 IF l_report_type <> 'PYDKEHOLCA_MIA' THEN
3475 FOR cur_emea_report_info_rec IN cur_emea_report_info(p_payroll_act_id, l_report_type)
3476 LOOP
3477
3478 FND_FILE.PUT_LINE(FND_FILE.LOG, '3: cur_emea_report_info');
3479
3480 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_root_tag);
3481 --
3482 FND_FILE.PUT(FND_FILE.OUTPUT,'<Hoved>');
3483 FND_FILE.PUT(FND_FILE.OUTPUT,'<Identifikation>');
3484 --
3485 FND_FILE.PUT(FND_FILE.OUTPUT,'<Transaktion>Udstedelse af eFeriekort</Transaktion>');
3486 FND_FILE.PUT(FND_FILE.OUTPUT,'<Modtager>ACF</Modtager>');
3487 FND_FILE.PUT(FND_FILE.OUTPUT,'<ACF_Version>1.4</ACF_Version>');
3488 FND_FILE.PUT(FND_FILE.OUTPUT,'<Indberetningstype>' || l_test_submission || '</Indberetningstype>');
3489 FND_FILE.PUT(FND_FILE.OUTPUT,'<Medie>Fil</Medie>');
3490 FND_FILE.PUT(FND_FILE.OUTPUT,'<Dannet_dato>' || cur_emea_report_info_rec.action_information6 || '</Dannet_dato>');
3491 FND_FILE.PUT(FND_FILE.OUTPUT,'<Dannet_klokken>' || cur_emea_report_info_rec.action_information7 || '</Dannet_klokken>');
3492 FND_FILE.PUT(FND_FILE.OUTPUT,'<Koersels_ID>' || cur_emea_report_info_rec.action_information8 || '</Koersels_ID>');
3493 FND_FILE.PUT(FND_FILE.OUTPUT,'<Reference1></Reference1>');
3494 FND_FILE.PUT(FND_FILE.OUTPUT,'<Indberetter>');
3495 FND_FILE.PUT(FND_FILE.OUTPUT,'<Indberetter_Type>Medlemsvirksomhed</Indberetter_Type>');
3496 FND_FILE.PUT(FND_FILE.OUTPUT,'<Indberetter_Navn>' || cur_emea_report_info_rec.action_information5 || '</Indberetter_Navn>');
3497 FND_FILE.PUT(FND_FILE.OUTPUT,'<Indberetter_CVRnr>' || cur_emea_report_info_rec.action_information4 || '</Indberetter_CVRnr>');
3498 FND_FILE.PUT(FND_FILE.OUTPUT,'<Indberetter_SEnr>' || cur_emea_report_info_rec.action_information3 || '</Indberetter_SEnr>');
3499 FND_FILE.PUT(FND_FILE.OUTPUT,'<Indberetter_Kontaktperson></Indberetter_Kontaktperson>');
3500 FND_FILE.PUT(FND_FILE.OUTPUT,'<Reference2></Reference2>');
3501 FND_FILE.PUT(FND_FILE.OUTPUT,'</Indberetter>');
3502 FND_FILE.PUT(FND_FILE.OUTPUT,'<System>');
3503 FND_FILE.PUT(FND_FILE.OUTPUT,'<Genereret_Af>' || cur_emea_report_info_rec.action_information9 || '</Genereret_Af>');
3504 FND_FILE.PUT(FND_FILE.OUTPUT,'<Version>' || cur_emea_report_info_rec.action_information10 || '</Version>');
3505 FND_FILE.PUT(FND_FILE.OUTPUT,'<UDBID>' || cur_emea_report_info_rec.action_information12 || '</UDBID>');
3506 FND_FILE.PUT(FND_FILE.OUTPUT,'<SYSID>' || cur_emea_report_info_rec.action_information11 || '</SYSID>');
3507 FND_FILE.PUT(FND_FILE.OUTPUT,'<OPDATO>' || cur_emea_report_info_rec.action_information6 || '</OPDATO>');
3508 FND_FILE.PUT(FND_FILE.OUTPUT,'</System>');
3509 --
3510 FND_FILE.PUT(FND_FILE.OUTPUT,'</Identifikation>');
3511 FND_FILE.PUT(FND_FILE.OUTPUT,'</Hoved>');
3512 --
3513 FND_FILE.PUT(FND_FILE.OUTPUT,'<Data>');
3514 FND_FILE.PUT(FND_FILE.OUTPUT,'<Indberetning>');
3515 --
3516 FND_FILE.PUT(FND_FILE.OUTPUT,'<Ident>');
3517 FND_FILE.PUT(FND_FILE.OUTPUT,'<Virksomheds_Navn>' || cur_emea_report_info_rec.action_information5 || '</Virksomheds_Navn>');
3518 FND_FILE.PUT(FND_FILE.OUTPUT,'<Virksomheds_CVRnr>' || cur_emea_report_info_rec.action_information4 || '</Virksomheds_CVRnr>');
3519 FND_FILE.PUT(FND_FILE.OUTPUT,'<Virksomheds_SEnr>' || cur_emea_report_info_rec.action_information3 || '</Virksomheds_SEnr>');
3520 FND_FILE.PUT(FND_FILE.OUTPUT,'<Reference3></Reference3>');
3521 FND_FILE.PUT(FND_FILE.OUTPUT,'<MO_kode>' || cur_emea_report_info_rec.action_information13 || '</MO_kode>');
3522 FND_FILE.PUT(FND_FILE.OUTPUT,'<FO_kode>' || cur_emea_report_info_rec.action_information14 || '</FO_kode>');
3523 FND_FILE.PUT(FND_FILE.OUTPUT,'</Ident>');
3524 --
3525 FND_FILE.PUT(FND_FILE.OUTPUT,'<Loenmodtagere>');
3526 FND_FILE.PUT(FND_FILE.OUTPUT,'<Loenmodtager>');
3527 --
3528 FOR cur_all_payrolls_rec IN cur_all_payrolls(p_payroll_act_id, l_report_type)
3529 LOOP
3530 FND_FILE.PUT_LINE(FND_FILE.LOG, '4: cur_all_payrolls');
3531
3532 FOR cur_emp_dtls_rec IN cur_emp_dtls(p_payroll_act_id, cur_all_payrolls_rec.payroll_id, l_report_type)
3533 LOOP
3534 FND_FILE.PUT_LINE(FND_FILE.LOG, '5: cur_emp_dtls');
3535 FND_FILE.PUT_LINE(FND_FILE.LOG, '5: payroll_id:'||cur_all_payrolls_rec.payroll_id);
3536
3537 FND_FILE.PUT(FND_FILE.OUTPUT,'<Navn>' || cur_emp_dtls_rec.action_information11 || '</Navn>');
3538 FND_FILE.PUT(FND_FILE.OUTPUT,'<Medarbejdernummer>' || cur_emp_dtls_rec.action_information9 || '</Medarbejdernummer>');
3539 FND_FILE.PUT(FND_FILE.OUTPUT,'<CPR_nummer>' || cur_emp_dtls_rec.action_information6 || '</CPR_nummer>');
3540 FND_FILE.PUT(FND_FILE.OUTPUT,'<Tiltraadt_dato>' || cur_emp_dtls_rec.action_information7 || '</Tiltraadt_dato>');
3541 FND_FILE.PUT(FND_FILE.OUTPUT,'<Fratraadt_dato>' || cur_emp_dtls_rec.action_information8 || '</Fratraadt_dato>');
3542 FND_FILE.PUT(FND_FILE.OUTPUT,'<MO_kode>' || cur_emp_dtls_rec.action_information12 || '</MO_kode>');
3543 FND_FILE.PUT(FND_FILE.OUTPUT,'<FO_kode>' || cur_emp_dtls_rec.action_information13 || '</FO_kode>');
3544 FND_FILE.PUT(FND_FILE.OUTPUT,'<Reference4></Reference4>');
3545
3546 FOR cur_dk_hcard_info_rec IN cur_dk_hcard_info(p_payroll_act_id,
3547 cur_all_payrolls_rec.payroll_id,
3548 cur_emp_dtls_rec.ACTION_INFORMATION4,
3549 cur_emp_dtls_rec.ACTION_INFORMATION5,
3550 'DK HCARD INFO',
3551 l_report_type)
3552 LOOP
3553 FND_FILE.PUT_LINE(FND_FILE.LOG, '6: cur_dk_hcard_info_rec');
3554 FND_FILE.PUT_LINE(FND_FILE.LOG, '6: person_id:'||cur_emp_dtls_rec.ACTION_INFORMATION4);
3555 FND_FILE.PUT_LINE(FND_FILE.LOG, '6: Asg_id:'||cur_emp_dtls_rec.ACTION_INFORMATION5);
3556 FND_FILE.PUT_LINE(FND_FILE.LOG, '6: payroll_id:'||cur_all_payrolls_rec.payroll_id);
3557
3558 FOR cur_dk_hcard_info_add_rec IN cur_dk_hcard_info_add(p_payroll_act_id,
3559 cur_all_payrolls_rec.payroll_id,
3560 cur_emp_dtls_rec.ACTION_INFORMATION4,
3561 cur_emp_dtls_rec.ACTION_INFORMATION5,
3562 'DK HCARD INFO ADD',
3563 cur_dk_hcard_info_rec.ACTION_INFORMATION6,
3564 l_report_type)
3565 LOOP
3566 FND_FILE.PUT_LINE(FND_FILE.LOG, '7: cur_dk_hcard_info_add_rec: p_year:'||cur_dk_hcard_info_rec.ACTION_INFORMATION6);
3567
3568 FND_FILE.PUT(FND_FILE.OUTPUT,'<eFeriekortoplysninger>');
3569 FND_FILE.PUT(FND_FILE.OUTPUT,'<Optjeningsaar>' || cur_dk_hcard_info_rec.action_information6 || '</Optjeningsaar>' );
3570 FND_FILE.PUT(FND_FILE.OUTPUT,'<Optjeningsperiode_Fra>' || cur_dk_hcard_info_rec.action_information7 || '</Optjeningsperiode_Fra>' );
3571 FND_FILE.PUT(FND_FILE.OUTPUT,'<Optjeningsperiode_Til>' || cur_dk_hcard_info_rec.action_information8 || '</Optjeningsperiode_Til>' );
3572 FND_FILE.PUT(FND_FILE.OUTPUT,'<FH_kode>' || cur_dk_hcard_info_rec.action_information9 || '</FH_kode>' );
3573 FND_FILE.PUT(FND_FILE.OUTPUT,'<Betalingsinfo>' || cur_dk_hcard_info_rec.action_information9 || '</Betalingsinfo>' );
3574
3575 IF cur_dk_hcard_info_rec.action_information11 = 'S'
3576 THEN
3577 l_Funktionaer := 'TRUE';
3578 l_Arbejder := 'FALSE';
3579 ELSE
3580 l_Funktionaer := 'FALSE';
3581 l_Arbejder := 'TRUE';
3582 END IF;
3583
3584 FND_FILE.PUT(FND_FILE.OUTPUT,'<Funktionaer>' || l_Funktionaer || '</Funktionaer>' );
3585 FND_FILE.PUT(FND_FILE.OUTPUT,'<Arbejder>' || l_Arbejder || '</Arbejder>' );
3586
3587 IF cur_dk_hcard_info_rec.action_information12 = '5DAY'
3588 THEN
3589 l_Optjent_5_dages_uge := 'TRUE';
3590 l_Optjent_6_dages_uge := 'FALSE';
3591 ELSE
3592 l_Optjent_5_dages_uge := 'FALSE';
3593 l_Optjent_6_dages_uge := 'TRUE';
3594 END IF;
3595
3596 FND_FILE.PUT(FND_FILE.OUTPUT,'<Optjent_5_dages_uge>' || l_Optjent_5_dages_uge || '</Optjent_5_dages_uge>' );
3597 FND_FILE.PUT(FND_FILE.OUTPUT,'<Optjent_6_dages_uge>' || l_Optjent_6_dages_uge || '</Optjent_6_dages_uge>' );
3598
3599 FND_FILE.PUT(FND_FILE.OUTPUT,'<Loenmodtagers_Adresse>' );
3600 FND_FILE.PUT(FND_FILE.OUTPUT,'<Navn>' || cur_dk_hcard_info_rec.action_information13 || '</Navn>' );
3601 FND_FILE.PUT(FND_FILE.OUTPUT,'<Adresse_Linie1>' || cur_dk_hcard_info_rec.action_information14 || '</Adresse_Linie1>' );
3602 FND_FILE.PUT(FND_FILE.OUTPUT,'<Adresse_Linie2>' || cur_dk_hcard_info_rec.action_information15 || '</Adresse_Linie2>' );
3603 FND_FILE.PUT(FND_FILE.OUTPUT,'<Adresse_Linie3>' || cur_dk_hcard_info_rec.action_information16 || '</Adresse_Linie3>' );
3604 FND_FILE.PUT(FND_FILE.OUTPUT,'<Adresse_Linie4>' || cur_dk_hcard_info_rec.action_information17 || '</Adresse_Linie4>' );
3605 FND_FILE.PUT(FND_FILE.OUTPUT,'<Adresse_Linie5>' || cur_dk_hcard_info_rec.action_information18 || '</Adresse_Linie5>' );
3606 FND_FILE.PUT(FND_FILE.OUTPUT,'<Adresse_Linie6>' || cur_dk_hcard_info_rec.action_information19 || '</Adresse_Linie6>' );
3607 FND_FILE.PUT(FND_FILE.OUTPUT,'</Loenmodtagers_Adresse>' );
3608
3609 FND_FILE.PUT(FND_FILE.OUTPUT,'<Udstedende_Virksomheds_Adresse>' );
3610
3611 FND_FILE.PUT(FND_FILE.OUTPUT,'<Navn>' || cur_dk_hcard_info_rec.action_information20 || '</Navn>' );
3612 FND_FILE.PUT(FND_FILE.OUTPUT,'<Adresse_Linie1>' || cur_dk_hcard_info_rec.action_information21 || '</Adresse_Linie1>' );
3613 FND_FILE.PUT(FND_FILE.OUTPUT,'<Adresse_Linie2>' || cur_dk_hcard_info_rec.action_information22 || '</Adresse_Linie2>' );
3614 FND_FILE.PUT(FND_FILE.OUTPUT,'<Adresse_Linie3>' || cur_dk_hcard_info_rec.action_information23 || '</Adresse_Linie3>' );
3615 FND_FILE.PUT(FND_FILE.OUTPUT,'<Adresse_Linie4>' || cur_dk_hcard_info_rec.action_information24 || '</Adresse_Linie4>' );
3616 FND_FILE.PUT(FND_FILE.OUTPUT,'<Adresse_Linie5>' || cur_dk_hcard_info_rec.action_information25 || '</Adresse_Linie5>' );
3617 FND_FILE.PUT(FND_FILE.OUTPUT,'<Adresse_Linie6>' || cur_dk_hcard_info_rec.action_information26 || '</Adresse_Linie6>' );
3618 FND_FILE.PUT(FND_FILE.OUTPUT,'</Udstedende_Virksomheds_Adresse>' );
3619
3620 FND_FILE.PUT(FND_FILE.OUTPUT,'<Beloeb>' );
3621 FND_FILE.PUT(FND_FILE.OUTPUT,'<Ferieberettiget_loen_beloeb>' || cur_dk_hcard_info_add_rec.action_information7 || '</Ferieberettiget_loen_beloeb>' );
3622 FND_FILE.PUT(FND_FILE.OUTPUT,'<Feriedage_ialt>' || cur_dk_hcard_info_add_rec.action_information8 || '</Feriedage_ialt>' );
3623 FND_FILE.PUT(FND_FILE.OUTPUT,'<Feriegodtgoerelse_procent>' || cur_dk_hcard_info_add_rec.action_information9 || '</Feriegodtgoerelse_procent>' );
3624 FND_FILE.PUT(FND_FILE.OUTPUT,'<Feriegodtgoerelse_beloeb>' || cur_dk_hcard_info_add_rec.action_information10 || '</Feriegodtgoerelse_beloeb>' );
3625 FND_FILE.PUT(FND_FILE.OUTPUT,'<Sygeferiegodtgoerelse_beloeb>' || cur_dk_hcard_info_add_rec.action_information11 || '</Sygeferiegodtgoerelse_beloeb>' );
3626 FND_FILE.PUT(FND_FILE.OUTPUT,'<Feriepenge_pr_dag_beloeb>' || cur_dk_hcard_info_add_rec.action_information12 || '</Feriepenge_pr_dag_beloeb>' );
3627 FND_FILE.PUT(FND_FILE.OUTPUT,'<Feriegodtgoerelse_ialt_beloeb>' || cur_dk_hcard_info_add_rec.action_information13 || '</Feriegodtgoerelse_ialt_beloeb>' );
3628 FND_FILE.PUT(FND_FILE.OUTPUT,'<Afregnet_AMbidrag_beloeb>' || cur_dk_hcard_info_add_rec.action_information14 || '</Afregnet_AMbidrag_beloeb>' );
3629 FND_FILE.PUT(FND_FILE.OUTPUT,'<Skattegrundlag_Feriegodtgoerelse_beloeb>' || cur_dk_hcard_info_add_rec.action_information15 || '</Skattegrundlag_Feriegodtgoerelse_beloeb>' );
3630 FND_FILE.PUT(FND_FILE.OUTPUT,'<Afregnet_Askat_beloeb>' || cur_dk_hcard_info_add_rec.action_information16 || '</Afregnet_Askat_beloeb>' );
3631 FND_FILE.PUT(FND_FILE.OUTPUT,'<Netto_feriepenge_beloeb>' || cur_dk_hcard_info_add_rec.action_information17 || '</Netto_feriepenge_beloeb>' );
3632 FND_FILE.PUT(FND_FILE.OUTPUT,'<SH_Fritvalgoverskud_beloeb>' || cur_dk_hcard_info_add_rec.action_information18 || '</SH_Fritvalgoverskud_beloeb>' );
3633 FND_FILE.PUT(FND_FILE.OUTPUT,'<AMbidrag_beloeb>' || '0' || '</AMbidrag_beloeb>' );
3634 FND_FILE.PUT(FND_FILE.OUTPUT,'<Skattegrundlag_SH_Fritvalg_beloeb>' || '0' || '</Skattegrundlag_SH_Fritvalg_beloeb>' );
3635 FND_FILE.PUT(FND_FILE.OUTPUT,'<Askat_beloeb>' || '0' || '</Askat_beloeb>' );
3636 FND_FILE.PUT(FND_FILE.OUTPUT,'<SH_Fritvalgoverskud_netto_beloeb>' || '0' || '</SH_Fritvalgoverskud_netto_beloeb>' );
3637 FND_FILE.PUT(FND_FILE.OUTPUT,'<Netto_til_ferie_ialt_beloeb>' || cur_dk_hcard_info_add_rec.action_information19 || '</Netto_til_ferie_ialt_beloeb>' );
3638 FND_FILE.PUT(FND_FILE.OUTPUT,'<Valuta>DKK</Valuta>' );
3639 FND_FILE.PUT(FND_FILE.OUTPUT,'<Fritekstfelt>' || cur_dk_hcard_info_add_rec.action_information20 || '</Fritekstfelt>' );
3640
3641 IF cur_dk_hcard_info_add_rec.action_information20 IS NOT NULL
3642 THEN
3643 l_Udskriv_Fritekstfelt := 'TRUE';
3644 ELSE
3645 l_Udskriv_Fritekstfelt := 'FALSE';
3646 END IF;
3647
3648 FND_FILE.PUT(FND_FILE.OUTPUT,'<Udskriv_Fritekstfelt>' || l_Udskriv_Fritekstfelt || '</Udskriv_Fritekstfelt>' );
3649
3650 FND_FILE.PUT(FND_FILE.OUTPUT,'<Nedskrivning>' );
3651 FND_FILE.PUT(FND_FILE.OUTPUT,'<Udbetalt_feriedage>' || cur_dk_hcard_info_add_rec.action_information21 || '</Udbetalt_feriedage>' );
3652 FND_FILE.PUT(FND_FILE.OUTPUT,'<Udbetalt_feriepenge_beloeb>' || cur_dk_hcard_info_add_rec.action_information22 || '</Udbetalt_feriepenge_beloeb>' );
3653 FND_FILE.PUT(FND_FILE.OUTPUT,'<Rest_netto_feriepenge_beloeb>' || cur_dk_hcard_info_add_rec.action_information23 || '</Rest_netto_feriepenge_beloeb>' );
3654 FND_FILE.PUT(FND_FILE.OUTPUT,'<Rest_feriedage>' || cur_dk_hcard_info_add_rec.action_information24 || '</Rest_feriedage>' );
3655 FND_FILE.PUT(FND_FILE.OUTPUT,'</Nedskrivning>' );
3656 FND_FILE.PUT(FND_FILE.OUTPUT,'</Beloeb>' );
3657 FND_FILE.PUT(FND_FILE.OUTPUT,'</eFeriekortoplysninger>' );
3658
3659 END LOOP; --DK HCARD INFO ADD
3660 END LOOP; --DK HCARD INFO
3661 END LOOP; --DK EMPLOYEE DETAILS
3662 END LOOP; --ALL PAYROLLS
3663 --
3664 FND_FILE.PUT(FND_FILE.OUTPUT,'</Loenmodtager>');
3665 FND_FILE.PUT(FND_FILE.OUTPUT,'</Loenmodtagere>');
3666 --
3667 FND_FILE.PUT(FND_FILE.OUTPUT,'</Indberetning>');
3668 FND_FILE.PUT(FND_FILE.OUTPUT,'</Data>');
3669 --
3670 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_root_tag_end);
3671 END LOOP; --cur_emea_report_info
3672
3673 ELSE --l_report_type = 'PYDKEHOLCA_MIA'
3674
3675 FOR cur_emea_report_info_rec IN cur_emea_report_info(p_payroll_act_id, l_report_type)
3676 LOOP
3677
3678 FND_FILE.PUT_LINE(FND_FILE.LOG, '3-1: cur_emea_report_info');
3679
3680 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_root_tag);
3681 --
3682 FND_FILE.PUT(FND_FILE.OUTPUT,'<Hoved>');
3683 FND_FILE.PUT(FND_FILE.OUTPUT,'<Identifikation>');
3684 --
3685 FND_FILE.PUT(FND_FILE.OUTPUT,'<Transaktion>Indberetning til ACF</Transaktion>');
3686 FND_FILE.PUT(FND_FILE.OUTPUT,'<Modtager>ACF</Modtager>');
3687 FND_FILE.PUT(FND_FILE.OUTPUT,'<ACF_Version>1.4</ACF_Version>');
3688 FND_FILE.PUT(FND_FILE.OUTPUT,'<Indberetningstype>' || l_test_submission || '</Indberetningstype>');
3689 FND_FILE.PUT(FND_FILE.OUTPUT,'<Medie>Fil</Medie>');
3690 FND_FILE.PUT(FND_FILE.OUTPUT,'<Dannet_dato>' || cur_emea_report_info_rec.action_information6 || '</Dannet_dato>');
3691 FND_FILE.PUT(FND_FILE.OUTPUT,'<Dannet_klokken>' || cur_emea_report_info_rec.action_information7 || '</Dannet_klokken>');
3692 FND_FILE.PUT(FND_FILE.OUTPUT,'<Koersels_ID>' || cur_emea_report_info_rec.action_information8 || '</Koersels_ID>');
3693 FND_FILE.PUT(FND_FILE.OUTPUT,'<Reference1></Reference1>');
3694 FND_FILE.PUT(FND_FILE.OUTPUT,'<Indberetter>');
3695 FND_FILE.PUT(FND_FILE.OUTPUT,'<Indberetter_Type>Medlemsvirksomhed</Indberetter_Type>');
3696 FND_FILE.PUT(FND_FILE.OUTPUT,'<Indberetter_Navn>' || cur_emea_report_info_rec.action_information5 || '</Indberetter_Navn>');
3697 FND_FILE.PUT(FND_FILE.OUTPUT,'<Indberetter_CVRnr>' || cur_emea_report_info_rec.action_information4 || '</Indberetter_CVRnr>');
3698 FND_FILE.PUT(FND_FILE.OUTPUT,'<Indberetter_SEnr>' || cur_emea_report_info_rec.action_information3 || '</Indberetter_SEnr>');
3699 FND_FILE.PUT(FND_FILE.OUTPUT,'<Indberetter_Kontaktperson></Indberetter_Kontaktperson>');
3700 FND_FILE.PUT(FND_FILE.OUTPUT,'<Reference2></Reference2>');
3701 FND_FILE.PUT(FND_FILE.OUTPUT,'</Indberetter>');
3702 FND_FILE.PUT(FND_FILE.OUTPUT,'<System>');
3703 FND_FILE.PUT(FND_FILE.OUTPUT,'<Genereret_Af>' || cur_emea_report_info_rec.action_information9 || '</Genereret_Af>');
3704 FND_FILE.PUT(FND_FILE.OUTPUT,'<Version>' || cur_emea_report_info_rec.action_information10 || '</Version>');
3705 FND_FILE.PUT(FND_FILE.OUTPUT,'<UDBID>' || cur_emea_report_info_rec.action_information12 || '</UDBID>');
3706 FND_FILE.PUT(FND_FILE.OUTPUT,'<SYSID>' || cur_emea_report_info_rec.action_information11 || '</SYSID>');
3707 FND_FILE.PUT(FND_FILE.OUTPUT,'<OPDATO>' || cur_emea_report_info_rec.action_information6 || '</OPDATO>');
3708 FND_FILE.PUT(FND_FILE.OUTPUT,'</System>');
3709 --
3710 FND_FILE.PUT(FND_FILE.OUTPUT,'</Identifikation>');
3711 FND_FILE.PUT(FND_FILE.OUTPUT,'</Hoved>');
3712 --
3713 FND_FILE.PUT(FND_FILE.OUTPUT,'<Data>');
3714 FND_FILE.PUT(FND_FILE.OUTPUT,'<Indberetning>');
3715 --
3716 FOR cur_all_periods_rec IN cur_all_periods(p_payroll_act_id)
3717 LOOP
3718 FND_FILE.PUT_LINE(FND_FILE.LOG, '4-1: cur_all_periods');
3719
3720 FND_FILE.PUT(FND_FILE.OUTPUT,'<Ident>');
3721 FND_FILE.PUT(FND_FILE.OUTPUT,'<Virksomheds_Navn>' || cur_emea_report_info_rec.action_information5 || '</Virksomheds_Navn>');
3722 FND_FILE.PUT(FND_FILE.OUTPUT,'<Virksomheds_CVRnr>' || cur_emea_report_info_rec.action_information4 || '</Virksomheds_CVRnr>');
3723 FND_FILE.PUT(FND_FILE.OUTPUT,'<Virksomheds_SEnr>' || cur_emea_report_info_rec.action_information3 || '</Virksomheds_SEnr>');
3724 FND_FILE.PUT(FND_FILE.OUTPUT,'<Reference3></Reference3>');
3725 FND_FILE.PUT(FND_FILE.OUTPUT,'<MO_kode>' || cur_emea_report_info_rec.action_information13 || '</MO_kode>');
3726 FND_FILE.PUT(FND_FILE.OUTPUT,'<FO_kode>' || cur_emea_report_info_rec.action_information14 || '</FO_kode>');
3727
3728 FND_FILE.PUT(FND_FILE.OUTPUT,'<Loenperiode_Fra>' || cur_all_periods_rec.period_from || '</Loenperiode_Fra>');
3729 FND_FILE.PUT(FND_FILE.OUTPUT,'<Loenperiode_Til>' || cur_all_periods_rec.period_to || '</Loenperiode_Til>');
3730
3731 FND_FILE.PUT(FND_FILE.OUTPUT,'</Ident>');
3732 --
3733 FND_FILE.PUT(FND_FILE.OUTPUT,'<Loenmodtagere>');
3734 --
3735 FOR cur_emp_dtls_mia_rec IN cur_emp_dtls_mia(p_payroll_act_id, cur_all_periods_rec.payroll_id, cur_all_periods_rec.period_id)
3736 LOOP
3737 FND_FILE.PUT_LINE(FND_FILE.LOG, '5-1: cur_emp_dtls_mia_rec');
3738 FND_FILE.PUT_LINE(FND_FILE.LOG, '5-2: payroll_id:'||cur_all_periods_rec.payroll_id);
3739
3740 FND_FILE.PUT(FND_FILE.OUTPUT,'<Loenmodtager>');
3741 FND_FILE.PUT(FND_FILE.OUTPUT,'<Navn>' || cur_emp_dtls_mia_rec.action_information11 || '</Navn>');
3742 FND_FILE.PUT(FND_FILE.OUTPUT,'<Medarbejdernummer>' || cur_emp_dtls_mia_rec.action_information9 || '</Medarbejdernummer>');
3743 FND_FILE.PUT(FND_FILE.OUTPUT,'<CPR_nummer>' || cur_emp_dtls_mia_rec.action_information6 || '</CPR_nummer>');
3744 FND_FILE.PUT(FND_FILE.OUTPUT,'<Tiltraadt_dato>' || cur_emp_dtls_mia_rec.action_information7 || '</Tiltraadt_dato>');
3745 FND_FILE.PUT(FND_FILE.OUTPUT,'<Fratraadt_dato>' || cur_emp_dtls_mia_rec.action_information8 || '</Fratraadt_dato>');
3746 FND_FILE.PUT(FND_FILE.OUTPUT,'<MO_kode>' || nvl(cur_emp_dtls_mia_rec.action_information12,cur_emea_report_info_rec.action_information13) || '</MO_kode>'); --14078879
3747 FND_FILE.PUT(FND_FILE.OUTPUT,'<FO_kode>' || nvl(cur_emp_dtls_mia_rec.action_information13,cur_emea_report_info_rec.action_information14) || '</FO_kode>'); --14078879
3748 FND_FILE.PUT(FND_FILE.OUTPUT,'<Reference4></Reference4>');
3749
3750 FND_FILE.PUT(FND_FILE.OUTPUT,'<Bevaegelser>');
3751 FOR cur_dk_hcard_accr_rec IN cur_dk_hcard_accr_pmnt(p_payroll_act_id,
3752 cur_all_periods_rec.payroll_id,
3753 cur_emp_dtls_mia_rec.ACTION_INFORMATION4,
3754 cur_emp_dtls_mia_rec.ACTION_INFORMATION5,
3755 cur_all_periods_rec.period_id,
3756 'DK HCARD ACCRUAL'
3757 )
3758 LOOP
3759 FND_FILE.PUT_LINE(FND_FILE.LOG, '6-1: cur_dk_hcard_accr_rec');
3760 FND_FILE.PUT_LINE(FND_FILE.LOG, '6-1: person_id:'||cur_emp_dtls_mia_rec.ACTION_INFORMATION4);
3761 FND_FILE.PUT_LINE(FND_FILE.LOG, '6-1: Asg_id:'||cur_emp_dtls_mia_rec.ACTION_INFORMATION5);
3762 FND_FILE.PUT_LINE(FND_FILE.LOG, '6-1: period_id:'||cur_all_periods_rec.period_id);
3763
3764 FND_FILE.PUT(FND_FILE.OUTPUT,'<Bevaegelse> ');
3765 FND_FILE.PUT(FND_FILE.OUTPUT,'<Indberetningstype>Optjening</Indberetningstype>');
3766
3767 FND_FILE.PUT(FND_FILE.OUTPUT,'<Optjeningsaar>' || cur_dk_hcard_accr_rec.action_information6 || '</Optjeningsaar>' );
3768 FND_FILE.PUT(FND_FILE.OUTPUT,'<SHsaldo_Netto_Beloeb></SHsaldo_Netto_Beloeb>' );
3769 FND_FILE.PUT(FND_FILE.OUTPUT,'<SHsaldo_Brutto_Beloeb></SHsaldo_Brutto_Beloeb>' );
3770 FND_FILE.PUT(FND_FILE.OUTPUT,'<Feriefridagssaldo_Netto_Beloeb>' || cur_dk_hcard_accr_rec.action_information9 || '</Feriefridagssaldo_Netto_Beloeb>' );
3771 FND_FILE.PUT(FND_FILE.OUTPUT,'<Feriefridagssaldo_Brutto_Beloeb>' || cur_dk_hcard_accr_rec.action_information10 || '</Feriefridagssaldo_Brutto_Beloeb>' );
3772 FND_FILE.PUT(FND_FILE.OUTPUT,'<Fritvalgssaldo_Netto_Beloeb>' || cur_dk_hcard_accr_rec.action_information11 || '</Fritvalgssaldo_Netto_Beloeb>' );
3773 FND_FILE.PUT(FND_FILE.OUTPUT,'<Fritvalgssaldo_Brutto_Beloeb>' || cur_dk_hcard_accr_rec.action_information12 || '</Fritvalgssaldo_Brutto_Beloeb>' );
3774 FND_FILE.PUT(FND_FILE.OUTPUT,'<Feriesaldo_Dage>' || cur_dk_hcard_accr_rec.action_information13 || '</Feriesaldo_Dage>' );
3775 FND_FILE.PUT(FND_FILE.OUTPUT,'<Feriesaldo_Beloeb>' || cur_dk_hcard_accr_rec.action_information14 || '</Feriesaldo_Beloeb>' );
3776 FND_FILE.PUT(FND_FILE.OUTPUT,'<Valuta>DKK</Valuta>' );
3777 FND_FILE.PUT(FND_FILE.OUTPUT,'</Bevaegelse>' );
3778 END LOOP; --DK HCARD ACCRUAL
3779
3780 FOR cur_dk_hcard_pmnt_rec IN cur_dk_hcard_accr_pmnt(p_payroll_act_id,
3781 cur_all_periods_rec.payroll_id,
3782 cur_emp_dtls_mia_rec.ACTION_INFORMATION4,
3783 cur_emp_dtls_mia_rec.ACTION_INFORMATION5,
3784 cur_all_periods_rec.period_id,
3785 'DK HCARD PAYMENTS'
3786 )
3787 LOOP
3788 FND_FILE.PUT_LINE(FND_FILE.LOG, '6-2: cur_dk_hcard_pmnt_rec');
3789 FND_FILE.PUT_LINE(FND_FILE.LOG, '6-2: person_id:'||cur_emp_dtls_mia_rec.ACTION_INFORMATION4);
3790 FND_FILE.PUT_LINE(FND_FILE.LOG, '6-2: Asg_id:'||cur_emp_dtls_mia_rec.ACTION_INFORMATION5);
3791 FND_FILE.PUT_LINE(FND_FILE.LOG, '6-2: period_id:'||cur_all_periods_rec.period_id);
3792
3793 FND_FILE.PUT(FND_FILE.OUTPUT,'<Bevaegelse> ');
3794 FND_FILE.PUT(FND_FILE.OUTPUT,'<Indberetningstype>Nedskrivning</Indberetningstype>');
3795
3796 FND_FILE.PUT(FND_FILE.OUTPUT,'<Optjeningsaar>' || cur_dk_hcard_pmnt_rec.action_information6 || '</Optjeningsaar>' );
3797 FND_FILE.PUT(FND_FILE.OUTPUT,'<SHsaldo_Netto_Beloeb></SHsaldo_Netto_Beloeb>' );
3798 FND_FILE.PUT(FND_FILE.OUTPUT,'<SHsaldo_Brutto_Beloeb></SHsaldo_Brutto_Beloeb>' );
3799 FND_FILE.PUT(FND_FILE.OUTPUT,'<Feriefridagssaldo_Netto_Beloeb>' || cur_dk_hcard_pmnt_rec.action_information9 || '</Feriefridagssaldo_Netto_Beloeb>' );
3800 FND_FILE.PUT(FND_FILE.OUTPUT,'<Feriefridagssaldo_Brutto_Beloeb>' || cur_dk_hcard_pmnt_rec.action_information10 || '</Feriefridagssaldo_Brutto_Beloeb>' );
3801 FND_FILE.PUT(FND_FILE.OUTPUT,'<Fritvalgssaldo_Netto_Beloeb>' || cur_dk_hcard_pmnt_rec.action_information11 || '</Fritvalgssaldo_Netto_Beloeb>' );
3802 FND_FILE.PUT(FND_FILE.OUTPUT,'<Fritvalgssaldo_Brutto_Beloeb>' || cur_dk_hcard_pmnt_rec.action_information12 || '</Fritvalgssaldo_Brutto_Beloeb>' );
3803 FND_FILE.PUT(FND_FILE.OUTPUT,'<Feriesaldo_Dage>' || cur_dk_hcard_pmnt_rec.action_information13 || '</Feriesaldo_Dage>' );
3804 FND_FILE.PUT(FND_FILE.OUTPUT,'<Feriesaldo_Beloeb>' || cur_dk_hcard_pmnt_rec.action_information14 || '</Feriesaldo_Beloeb>' );
3805 FND_FILE.PUT(FND_FILE.OUTPUT,'<Valuta>DKK</Valuta>' );
3806 FND_FILE.PUT(FND_FILE.OUTPUT,'</Bevaegelse>' );
3807 END LOOP; --DK HCARD PAYMENTS
3808
3809 FND_FILE.PUT(FND_FILE.OUTPUT,'</Bevaegelser>');
3810 FND_FILE.PUT(FND_FILE.OUTPUT,'</Loenmodtager>');
3811 END LOOP; --DK EMPLOYEE DETAILS
3812
3813 FND_FILE.PUT(FND_FILE.OUTPUT,'</Loenmodtagere>');
3814 END LOOP; --ALL PERIODS
3815 END LOOP; --cur_emea_report_info
3816 --
3817 FND_FILE.PUT(FND_FILE.OUTPUT,'</Indberetning>');
3818 FND_FILE.PUT(FND_FILE.OUTPUT,'</Data>');
3819 --
3820 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_root_tag_end);
3821
3822 END IF; --l_report_type
3823
3824 END generate_xml;
3825
3826 BEGIN
3827 g_payroll_action_id :=NULL;
3828 g_le_assignment_action_id :=NULL;
3829 g_business_group_id :=NULL;
3830 g_legal_employer_id :=NULL;
3831 g_effective_date :=NULL;
3832 g_payroll_id :=NULL;
3833 g_year :=NULL;
3834 g_payroll_period :=NULL;
3835 g_test_submission :=NULL;
3836 g_company_terminating :=NULL;
3837 g_msg_txt :=NULL;
3838
3839 END PAY_DK_ARCHIVE_EHOL;