eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.PAY_DK_SICKNESS_DP201

Source


1 PACKAGE BODY PAY_DK_SICKNESS_DP201 AS
2 /* $Header: pydkdp201.pkb 120.9.12000000.2 2007/05/08 07:01:23 saurai noship $ */
3 	g_debug   boolean   :=  hr_utility.debug_enabled;
4 	g_err_num		NUMBER;
5 
6 -------------------------------------------------------------------------------------------------------
7 
8 /*Function to get the defined balance id */
9 FUNCTION GET_DEFINED_BALANCE_ID
10 	(p_balance_name   		IN  VARCHAR2
11 	,p_dbi_suffix     		IN  VARCHAR2
12 	,p_business_group_id		IN NUMBER)
13 	RETURN NUMBER IS
14 
15 l_defined_balance_id 		NUMBER;
16 
17 BEGIN
18 
19 SELECT
20 	pdb.defined_balance_id
21 INTO
22 	l_defined_balance_id
23 FROM
27 WHERE
24 	pay_defined_balances      pdb
25 	,pay_balance_types         pbt
26 	,pay_balance_dimensions    pbd
28 	pbd.database_item_suffix = p_dbi_suffix
29 	AND    (pbd.legislation_code = 'DK' OR pbt.business_group_id = p_business_group_id)
30 	AND    pbt.balance_name = p_balance_name
31 	AND    (pbt.legislation_code = 'DK' OR pbt.business_group_id = p_business_group_id)
32 	AND    pdb.balance_type_id = pbt.balance_type_id
33 	AND    pdb.balance_dimension_id = pbd.balance_dimension_id
34 	AND    (pdb.legislation_code = 'DK' OR pbt.business_group_id = p_business_group_id);
35 
36 l_defined_balance_id := NVL(l_defined_balance_id,0);
37 
38 RETURN l_defined_balance_id ;
39 END GET_DEFINED_BALANCE_ID;
40 ---------------------------------------------------------------------------------------------------
41 
42 /*Procedure which returns the last pay (after pre-Paymnents) of an assignment*/
43 PROCEDURE LAST_PAY
44 	( p_business_group_id IN NUMBER
45 	,p_assignment_id IN NUMBER
46 	,p_effective_date IN DATE
47 	,p_pay OUT NOCOPY VARCHAR2
48 	)
49 		   IS
50 
51 CURSOR csr_asg_act_id IS
52 SELECT
53 *
54 FROM
55 (
56 SELECT
57 	paa1.assignment_action_id
58 FROM
59 	pay_assignment_actions paa1
60 	,pay_payroll_actions ppa1
61 	,pay_assignment_actions paa2
62 	,pay_payroll_actions ppa2
63 	,pay_action_interlocks  pai
64 	,pay_payrolls_f ppf
65 WHERE
66 	paa1.assignment_id = p_assignment_id
67 	AND paa1.action_status = 'C'
68 	AND paa1.payroll_action_id = ppa1.payroll_action_id
69 	AND ppa1.action_type IN ('R','Q')  -- Payroll Run or Quickpay Run
70 	AND ppa1.date_earned <= p_effective_date
71 
72 	--for prepayments
73 	AND    paa2.action_status           = 'C' -- Completed
74 	AND    paa2.assignment_action_id    = pai.locking_action_id
75 	AND    paa2.payroll_action_id       = ppa2.payroll_action_id
76 	AND    ppa2.action_type            IN ('P','U')
77 	AND    ppa2.date_earned <= p_effective_date
78 
79 	AND paa1.ASSIGNMENT_ACTION_ID = pai.locked_action_id
80 
81 	AND ppf.payroll_id = ppa1.payroll_id
82 	ORDER BY ppa1.date_earned desc)
83 WHERE
84 	ROWNUM=1;
85 
86 /*Cursor to fetch the balance values- Sum of balance values belonging to salary reporting category minus
87 Employee AMB Deduction balance value*/
88 
89 CURSOR csr_pay(asg_act_id NUMBER ) IS
90 SELECT
91 	SUM (pay_balance_pkg.get_value(
92 	     get_defined_balance_id(pbt.balance_name,'_ASG_PTD',p_business_group_id)
93 		,asg_act_id)) - pay_balance_pkg.get_value(
94 	     get_defined_balance_id('Employee AMB Deduction','_ASG_PTD',p_business_group_id)
95 		,asg_act_id) pay_value
96 FROM
97 	pay_balance_types pbt
98 	,pay_balance_categories_f pbcf
99 WHERE
100 	pbcf.legislation_code = 'DK'
101 	AND pbcf.category_name = 'Salary Reporting'
102 	AND pbcf.balance_category_id = pbt.balance_category_id;
103 
104 l_asg_act_id number;
105 
106 BEGIN
107 
108 OPEN csr_asg_act_id;
109 FETCH csr_asg_act_id INTO l_asg_act_id;
110 CLOSE csr_asg_act_id;
111 
112 OPEN csr_pay(l_asg_act_id);
113 FETCH csr_pay INTO p_pay;
114 CLOSE csr_pay;
115 
116 END LAST_PAY;
117 -------------------------------------------------------------------------------------------------------------------------
118 /*Function to return the working hours per week of an assignment*/
119 FUNCTION WORKING_HOURS_PER_WEEK
120 		(p_assignment_id IN NUMBER
121 		,p_leg_emp_id IN NUMBER
122 		,p_effective_date IN DATE)
123 		 RETURN NUMBER IS
124 
125 l_hours per_all_assignments_f.normal_hours%TYPE;
126 l_freq per_all_assignments_f.frequency%TYPE;
127 l_default_work_pattern VARCHAR2(30);
128 l_work_days_week NUMBER ;
129 l_value NUMBER ;
130 
131 CURSOR csr_asg_freq_hours IS
132 SELECT
133 	paaf.frequency
134 	,paaf.normal_hours
135 	,hsck.segment10 default_work_pattern
136  FROM
137 	 per_all_assignments_f paaf
138 	,hr_soft_coding_keyflex hsck
139 WHERE
140 	paaf.assignment_id = p_assignment_id
141 	AND paaf.assignment_status_type_id = 1
142 	AND p_effective_date BETWEEN paaf.effective_start_date AND paaf.effective_end_date
143 	AND hsck.soft_coding_keyflex_id = paaf.soft_coding_keyflex_id;
144 
145 
146 CURSOR csr_le_freq_hours IS
147 SELECT
148 	hoi1.org_information4
149 	,hoi1.org_information3
150 	,hoi2.org_information1
151 FROM
152 	hr_organization_information hoi1
153 	,hr_organization_information hoi2
154 WHERE
155 	hoi1.organization_id = p_leg_emp_id
156 	AND hoi2.organization_id = hoi1.organization_id
157 	AND hoi1.org_information_context =  'DK_EMPLOYMENT_DEFAULTS'
158 	AND hoi2.org_information_context(+) =  'DK_HOLIDAY_ENTITLEMENT_INFO';
159 
160 BEGIN
161 l_work_days_week := 5; -- By default the work pattern will be 5 days
162 
163 /*To fetch the assignment hours*/
164 OPEN csr_asg_freq_hours;
165 FETCH csr_asg_freq_hours INTO l_freq,l_hours,l_default_work_pattern;
166 CLOSE csr_asg_freq_hours;
167 
168 /* If assignment hours not present then to take the hours from the legal employer EIT 'Work hours'*/
169 IF l_hours IS NULL THEN
170 open csr_le_freq_hours;
171 FETCH csr_le_freq_hours INTO l_freq,l_hours,l_default_work_pattern;
172 CLOSE csr_le_freq_hours;
173 END IF ;
174 
175 IF (l_default_work_pattern = '5DAY') THEN
176 l_work_days_week := 5;
177 ELSIF  (l_default_work_pattern = '6DAY') THEN
178 l_work_days_week := 6;
179 END IF ;
180 
181 IF (l_freq = 'D') THEN
182 l_value := ROUND (l_hours * l_work_days_week,2);
183 ELSIF (l_freq = 'W') THEN
184 l_value := l_hours;
185 ELSIF (l_freq = 'M') THEN
186 l_value := ROUND (l_hours * (l_work_days_week/22),2);
187 ELSIF (l_freq = 'Y') THEN
188 l_value := ROUND (l_hours * (l_work_days_week/260),2);
189 END IF;
190 
191 RETURN l_value;
192 
193 END WORKING_HOURS_PER_WEEK;
194 
195 
196 ----------------------------------------------------------------------------------
197 /*Procudure to get the sick leave details for reporting*/
198 /*Bug 5059274 fix- Added p_start_date and p_end_Date parameters*/
199 PROCEDURE POPULATE_DETAILS
200 	(p_template_name in VARCHAR2
201 	,p_assignment_id NUMBER DEFAULT NULL
202 	,p_person_id NUMBER
203 	,p_start_date IN VARCHAR2
204 	,p_end_date IN VARCHAR2
205 	,p_le_phone_number IN VARCHAR2
206 	,p_le_email_addr IN varchar2
207 	,p_business_group_id NUMBER
208 	, p_xml out nocopy clob) IS
209 
210 /*Parameters to store the start and end dates, in order to provide date validation the parameters
211   are accepeted as varchar2 and converted into date type*/
212 l_start_date DATE;
213 l_end_date DATE;
214 
215 --Cursor to fetch employee details
216 CURSOR csr_emp(p_effective_date DATE) IS
217 SELECT
218 /*	papf.first_name first_name
219 	,papf.middle_names middle_name
220 	,papf.last_name last_name*/
221 	/*Bug 5049222 fix- Employee name formatting*/
222 	--SUBSTR (papf.first_name,1,40)||NVL2(papf.middle_names,','||SUBSTR(papf.middle_names,1,40),NULL)||','||SUBSTR(papf.last_name,1,40) ename
223 	/*Reopened Bug 5049222 fix*/
224      SUBSTR (papf.last_name,1,40)||', '||SUBSTR(papf.first_name,1,40)||NVL2(papf.middle_names,' '||SUBSTR(papf.middle_names,1,40),NULL) ename
225 	,papf.national_identifier national_identifier
226 	,papf.start_date person_start_date
227 	,pa.address_line1 address_line1
228 	,pa.address_line2 address_line2
229 	,pa.postal_code postal_code
230 FROM
231 	per_all_people_f papf
232 	,per_addresses   pa
233 WHERE
234 	papf.person_id = p_person_id
235 	AND p_effective_date between papf.effective_start_date and papf.effective_end_date
236 	AND papf.current_employee_flag = 'Y'
237 	AND pa.person_id(+) = papf.person_id
238 	AND pa.primary_flag(+) = 'Y'
239 	AND p_effective_date BETWEEN pa.date_from(+) AND nvl(pa.date_to(+),TO_DATE('31/12/4712','DD/MM/YYYY'));
240 
241 /*Bug fix- 5059274 cursor to get the assignment details based on p_start_date and p_end_date*/
242 CURSOR csr_asg IS
243 SELECT
244 	paaf.person_id person_id
245 	,paaf.assignment_id asg_id
246 	,paaf.assignment_number asg_num
247 	,pj.name job_title
248 	,MIN(paaf.effective_start_date) asg_start_date
249 	,MAX(paaf.effective_end_date) asg_end_date
250 	,peef.effective_start_date absence_start_date
251 	,peef.effective_end_date absence_end_date
252 	,peevf.screen_entry_value absence_reason
253 	,paaf.payroll_id payroll_id
254 	,ppf.period_type period_type
255 FROM
256 	per_all_assignments_f  paaf
257 	,pay_element_entries_f peef
258 	,pay_element_types_f petf
259 	,per_jobs pj
260 	,pay_element_entry_values_f peevf
261 	,pay_input_values_f pivf
262 	,pay_payrolls_f ppf
263 WHERE
264     paaf.person_id = p_person_id
265 	AND paaf.assignment_id = NVL(p_assignment_id,paaf.assignment_id)
266 	AND paaf.assignment_status_type_id = 1
267 	AND petf.element_name LIKE 'Absent Sick' -- To check for the Sickness element
268 	AND petf.legislation_code ='DK'
269     AND peef.element_type_id = petf.element_type_id
270     AND peef.assignment_id = paaf.assignment_id
271 
272     AND peef.effective_start_date BETWEEN l_start_date and l_end_date
273 
274 	AND pivf.element_type_id = peef.element_type_id
275 	AND pivf.NAME = 'Absent Reason'
276 	AND peevf.element_entry_id= peef.element_entry_id
277 	AND peevf.input_value_id = pivf.input_value_id
278 	AND pj.job_id(+) = paaf.job_id
279 	AND ppf.payroll_id(+) = paaf.payroll_id
280 	GROUP BY paaf.person_id
281 	,paaf.assignment_id
282 	,paaf.assignment_number
283 	,pj.name
284 	,peef.effective_start_date
285 	,peef.effective_end_date
286 	,peevf.screen_entry_value
287 	,paaf.payroll_id
288     ,ppf.period_type;
289 
290 /*Cursor to pick up the legal employer details*/
291 CURSOR csr_le(p_le_id NUMBER) IS
292 SELECT
293 --	haou.organization_id le_id
294 	 SUBSTR(haou.NAME,1,30) le_name
295 	,substr(hl.address_line_1,1,30) le_address1
296 	,decode(hl.postal_code,NULL,' ',','||substr(hl.postal_code,1,5)) le_postalcode
297 	,substr(p_le_phone_number,1,30) le_phone
298 	,substr(p_le_email_addr,1,30) le_email
299 FROM
300 --	per_all_assignments_f paaf
301 --	,hr_soft_coding_keyflex hsck
302 	hr_all_organization_units haou
303 	,hr_locations hl
304 WHERE
305 --	paaf.assignment_id = p_assignment_id
306 --	AND l_run_date BETWEEN paaf.effective_start_date AND paaf.effective_end_date
307 --	AND paaf.assignment_status_type_id = 1
308 --	AND hsck.soft_coding_keyflex_id = paaf.soft_coding_keyflex_id
309 	haou.organization_id = p_le_id
310 	AND hl.location_id(+) = haou.location_id;
311 
312 /*Bug 5049222 fix- Cursor to get the cvr number*/
313 CURSOR csr_le_cvr(p_le_id NUMBER) IS
314 SELECT
315 hoi.org_information1 le_cvr_num
316 FROM
317 hr_organization_information hoi
318 WHERE
319 hoi.organization_id = p_le_id
320 AND hoi.org_information_context = 'DK_LEGAL_ENTITY_DETAILS';
321 
322 /*Bug fix- 5059274 cursor to get the payroll period start date in which the
323 absence start date falls*/
324 CURSOR csr_payroll_start_date(p_payroll_id NUMBER,p_absence_start_date DATE)
325 IS
326 SELECT
327     ptp.start_date
328 FROM
329     per_time_periods ptp
330 WHERE
331     ptp.payroll_id = p_payroll_id
332     AND p_absence_start_date BETWEEN ptp.start_date AND ptp.end_Date;
333 
334 /*Bug fix- 5059274 cursor to get the soft coded details based on the payroll period start date*/
335 CURSOR csr_soft_coded_details(p_assignment_id IN NUMBER,p_effective_date DATE) IS
336 SELECT
337     NVL(hsck.segment18,'N') section28
338    	,hsck.segment4 employee_group
339 	,hsck.segment1 le_id
340 	,hsck.segment10 asg_work_pattern
341 FROM
342 	per_all_assignments_f paaf
343 	,hr_soft_coding_keyflex hsck
344 WHERE
345 	paaf.assignment_id = p_assignment_id
346 	AND p_effective_date BETWEEN paaf.effective_start_date AND paaf.effective_end_date
347 	AND hsck.soft_coding_keyflex_id = paaf.soft_coding_keyflex_id;
348 
349 /*Bug fix- 5059274 cursor to get the section 27 details based on the payroll period start date*/
350 CURSOR csr_section27(p_effective_date DATE, p_le_id NUMBER) IS
351 SELECT
352     hoi.org_information1
353 FROM
354     hr_organization_information hoi
355 WHERE
356     hoi.organization_id = p_le_id
357     AND hoi.org_information_context = 'DK_SICKPAY_DEFAULTS'
358     AND p_effective_date BETWEEN fnd_date.canonical_to_date(hoi.org_information2) and fnd_date.canonical_to_date(hoi.org_information3)
359 ORDER  BY hoi.org_information2;
360 
361 CURSOR csr_le_work_pattern(p_org_id NUMBER) is
362 SELECT
363     org_information1 le_work_pattern
364 FROM
365     hr_organization_information
366 WHERE
367     organization_id = p_org_id
368     AND org_information_context = 'DK_HOLIDAY_ENTITLEMENT_INFO';
369 
370   /* Bug 5045710 fix- cursor to get the global values*/
371 CURSOR csr_get_global_values(p_global_name VARCHAR2,p_effective_date DATE ) IS
372  SELECT
373        fgf.global_value
374  FROM
375        ff_globals_f fgf
376  WHERE
377  fgf.legislation_code = 'DK'
378  AND fgf.GLOBAL_NAME = p_global_name
379  AND p_effective_date BETWEEN fgf.effective_start_date AND fgf.effective_end_date;
380 
381 -- Variable declaration
382 l_var_work_hrs NUMBER ;
383 l_8weeks_work_hrs NUMBER ;
384 emp_rec csr_emp%ROWTYPE ;
385 asg_rec csr_asg%ROWTYPE ;
386 le_rec csr_le%ROWTYPE ;
387 xml_ctr  number;
388 l_pay VARCHAR2(150);
389 --l_period_type pay_payrolls_f.period_type%TYPE;
390 l_check VARCHAR2(1);
391 le_cvr_rec csr_le_cvr%ROWTYPE; --Bug 5049222 fix
392 l_absence_days NUMBER ;
393 l_section27 hr_organization_information.org_information1%TYPE;
394 --l_section28 VARCHAR2(10) ;
395 soft_coded_rec csr_soft_coded_details%ROWTYPE;
396 l_payroll_proc_start_date DATE ;
397 l_74hrs VARCHAR2(150);
398 l_worked_hours NUMBER ;
399 l_worked_days NUMBER ;
400 
401 
402 l_dk_max_sick_days_allowed NUMBER ;
403 l_dk_5day_pattern_min_days NUMBER ;
404 l_dk_6day_pattern_min_days NUMBER ;
405 l_dk_sickpay_min_work_hours NUMBER ;
406 le_work_pattern_rec csr_le_work_pattern%ROWTYPE ;
407 l_work_pattern VARCHAR2(150);
408 
409 
410 BEGIN
411 l_check := 'X';
412 xml_ctr := 0;
413 l_work_pattern := '5DAY'; -- By default work pattern is 5 days
414 l_start_date :=FND_DATE.CANONICAL_TO_DATE(p_start_date);
415 l_end_date   :=FND_DATE.CANONICAL_TO_DATE(p_end_date);
416 
417 
418 FOR asg_rec IN csr_asg LOOP
419 l_absence_days := NVL(asg_rec.absence_end_date,l_end_date) - asg_rec.absence_start_date+1;
420 
421 OPEN csr_payroll_start_date(asg_rec.payroll_id,asg_rec.absence_start_date);
422 FETCH csr_payroll_start_date INTO l_payroll_proc_start_date;
423 CLOSE csr_payroll_start_date;
424 
425 IF csr_asg%ROWCOUNT = 1 THEN
426 OPEN csr_emp(l_payroll_proc_start_date);
427 FETCH csr_emp INTO emp_rec;
428 CLOSE csr_emp;
429 END IF ;
430 
431 OPEN csr_soft_coded_details(asg_rec.asg_id,l_payroll_proc_start_date);
432 FETCH csr_soft_coded_details INTO soft_coded_rec;
433 CLOSE csr_soft_coded_details;
434 
435 OPEN csr_section27(l_payroll_proc_start_date,soft_coded_rec.le_id);
436 FETCH csr_section27 INTO l_section27;
437 CLOSE csr_section27;
438 
439 /* Bug 5045710 fix- Getting the global values*/
440 OPEN csr_get_global_values('DK_MAX_SICK_DAYS_ALLOWED',l_payroll_proc_start_date);
441 FETCH csr_get_global_values INTO l_dk_max_sick_days_allowed;
442 CLOSE csr_get_global_values;
443 
444 OPEN csr_get_global_values('DK_5DAY_PATTERN_MIN_DAYS',l_payroll_proc_start_date);
445 FETCH csr_get_global_values INTO l_dk_5day_pattern_min_days;
446 CLOSE csr_get_global_values;
447 
448 OPEN csr_get_global_values('DK_6DAY_PATTERN_MIN_DAYS',l_payroll_proc_start_date);
449 FETCH csr_get_global_values INTO l_dk_6day_pattern_min_days;
450 CLOSE csr_get_global_values;
451 
452 OPEN csr_get_global_values('DK_SICKPAY_MIN_WORK_HOURS',l_payroll_proc_start_date);
453 FETCH csr_get_global_values INTO l_dk_sickpay_min_work_hours;
454 CLOSE csr_get_global_values;
455 
456 --IF asg_rec.person_id <> l_person_id THEN
457 IF l_absence_days >=l_dk_max_sick_days_allowed OR soft_coded_rec.section28 ='Y' OR l_section27 = 'Y' OR asg_rec.absence_reason ='ABS_WA' THEN
458 --END IF;
459 
460 OPEN csr_le(soft_coded_rec.le_id);
461 FETCH csr_le INTO le_rec;
462 close csr_le;
463 
464 OPEN csr_le_cvr(soft_coded_rec.le_id);
465 FETCH csr_le_cvr INTO le_cvr_rec;
466 CLOSE csr_le_cvr;
467        /*Bug 5049222 fix*/
468 	xml_tab(xml_ctr).tagname  := 'emp_name';
469 	xml_tab(xml_ctr).tagvalue := hr_dk_utility.REPLACE_SPECIAL_CHARS(emp_rec.ename);
470 	xml_ctr := xml_ctr +1;
471 
472 	xml_tab(xml_ctr).tagname  := 'address_line1';
473 	xml_tab(xml_ctr).tagvalue := hr_dk_utility.REPLACE_SPECIAL_CHARS(substr(emp_rec.address_line1,1,40));
474 	xml_ctr := xml_ctr +1;
475 
476 	xml_tab(xml_ctr).tagname  := 'address_line2';
477 	xml_tab(xml_ctr).tagvalue := hr_dk_utility.REPLACE_SPECIAL_CHARS(substr(emp_rec.address_line2,1,40));
478 	xml_ctr := xml_ctr +1;
479 
480 	xml_tab(xml_ctr).tagname  := 'postal_code';
481 	xml_tab(xml_ctr).tagvalue := hr_dk_utility.REPLACE_SPECIAL_CHARS(substr(emp_rec.postal_code,1,40));
482 	xml_ctr := xml_ctr +1;
483 
484 	xml_tab(xml_ctr).tagname  := 'asg_num';
485 	xml_tab(xml_ctr).tagvalue := asg_rec.asg_num;
486 	xml_ctr := xml_ctr +1;
487 
488        /*Bug 5049222 fix*/
489 	xml_tab(xml_ctr).tagname  := 'le_cvr_number';
490 	xml_tab(xml_ctr).tagvalue := le_cvr_rec.le_cvr_num;
491 	xml_ctr := xml_ctr +1;
492 
493 	xml_tab(xml_ctr).tagname  := 'national_identifier';
494 	xml_tab(xml_ctr).tagvalue := emp_rec.national_identifier;
495 	xml_ctr := xml_ctr +1;
496 
497 	xml_tab(xml_ctr).tagname  := 'job_title';
498 	xml_tab(xml_ctr).tagvalue := hr_dk_utility.REPLACE_SPECIAL_CHARS(asg_rec.job_title);
499 	xml_ctr := xml_ctr +1;
500 
501 	IF ((asg_rec.absence_start_date - asg_rec.asg_start_date) >= 90) THEN
502 	xml_tab(xml_ctr).tagname  := '13_weeks_yes';
503 	xml_tab(xml_ctr).tagvalue := l_check;
504 	xml_ctr := xml_ctr +1;
505 	ELSE
506 	xml_tab(xml_ctr).tagname  := '13_weeks_no';
507 	xml_tab(xml_ctr).tagvalue := l_check;
508 	xml_ctr := xml_ctr +1;
509 	xml_tab(xml_ctr).tagname  := 'asg_start_date';
510 	xml_tab(xml_ctr).tagvalue := asg_rec.asg_start_date;
511 	xml_ctr := xml_ctr +1;
512 	END IF ;
513 
514 	xml_tab(xml_ctr).tagname  := 'last_day_at_work';
515 	xml_tab(xml_ctr).tagvalue := asg_rec.absence_start_date - 1;
516 	xml_ctr := xml_ctr +1;
517 
518 	/*Has the employee resume work*/
519 	IF (asg_rec.absence_end_date <= l_end_date) THEN
520 	xml_tab(xml_ctr).tagname := 'absence_end_yes';
521 	xml_tab(xml_ctr).tagvalue := l_check;
522 	xml_ctr := xml_ctr +1;
523 
524 	xml_tab(xml_ctr).tagname := 'absence_end_date';
525 	/*asg_rec.absence_end_date + 1 to get the date on which the employee resumed work*/
526 	xml_tab(xml_ctr).tagvalue := asg_rec.absence_end_date + 1;
527 	xml_ctr := xml_ctr +1;
528 	ELSE
529 	xml_tab(xml_ctr).tagname := 'absence_end_no';
530 	xml_tab(xml_ctr).tagvalue := l_check;
531 	xml_ctr := xml_ctr +1;
532 	END IF;
533 
534 	/*check whether the absence reason is work accident*/
535 	IF (asg_rec.absence_reason = 'ABS_WA') THEN
536 	xml_tab(xml_ctr).tagname := 'work_accident_yes';
537 	xml_tab(xml_ctr).tagvalue := l_check;
538 	xml_ctr := xml_ctr +1;
539 	ELSE
540 	xml_tab(xml_ctr).tagname := 'work_accident_no';
541 	xml_tab(xml_ctr).tagvalue := l_check;
542 	xml_ctr := xml_ctr +1;
543 	END IF;
544 
545 IF ((asg_rec.absence_start_date - asg_rec.asg_start_date)>=29 ) THEN
546 /*call to get the varying working hours for the last four weeks*/
547 l_var_work_hrs := pay_dk_sickpay_pkg.get_worked_hours(
548 					   asg_rec.asg_id
549 					  ,asg_rec.absence_start_date - 29
550 					  ,asg_rec.absence_start_date - 1) / 4;
551 
552 /*If work schedule is not present then call working_hours_per_week which returns the working_hours
553 based on the details present at the assignment level if not at the LE level*/
554 IF( l_var_work_hrs = -1) THEN
555 l_var_work_hrs:= working_hours_per_week(asg_rec.asg_id,soft_coded_rec.le_id,l_payroll_proc_start_date);
556 END IF ;
557 END IF ;
558 	xml_tab(xml_ctr).tagname := 'var_work_hrs';
559 	xml_tab(xml_ctr).tagvalue := l_var_work_hrs;
560 	xml_ctr := xml_ctr +1;
561 
562 
563 /*Special Employment Arrangement*/
564 
565 IF (soft_coded_rec.employee_group = 7) THEN
566 	xml_tab(xml_ctr).tagname := 'special_job';
567 	xml_tab(xml_ctr).tagvalue := l_check;
568 	xml_ctr := xml_ctr +1;
569 ELSIF (soft_coded_rec.employee_group = 8) THEN
570  	xml_tab(xml_ctr).tagname := 'job_training';
571 	xml_tab(xml_ctr).tagvalue := l_check;
572 	xml_ctr := xml_ctr +1;
573 ELSIF (soft_coded_rec.employee_group = 6) THEN
574 	xml_tab(xml_ctr).tagname := 'flexible_job_sec1';
575 	xml_tab(xml_ctr).tagvalue := l_check;
576     xml_ctr := xml_ctr +1;
577 	xml_tab(xml_ctr).tagname := 'flexible_job_sec3'; --Bug 5049222 fix
578 	xml_tab(xml_ctr).tagvalue := l_check;
579 	xml_ctr := xml_ctr +1;
580 END IF ;
581 
582 
583 
584 /* call to get the last pay for the assignment*/
585 IF asg_rec.absence_end_date IS  NOT  NULL  AND asg_rec.absence_end_date <= l_end_date THEN
586     last_pay(p_business_group_id,asg_rec.asg_id,asg_rec.absence_end_date,l_pay);
587 ELSE
588     last_pay(p_business_group_id,asg_rec.asg_id,l_end_date,l_pay);
589 END IF ;
590 --l_pay := TRIM(TO_CHAR(ROUND(l_pay,2),'99g99g99g990d99','NLS_NUMERIC_CHARACTERS = '',.'''));
591 --l_pay := TRIM(TO_CHAR(ROUND(NVL(FND_NUMBER.canonical_to_number(l_pay),0),2),'999G999G990D99'));
592 l_pay := TRIM(TO_CHAR(ROUND(FND_NUMBER.canonical_to_number(l_pay),2),'999G999G990D99'));
593 	/*The employee's pay condition*/
594 	/* Changes for Lunar Payroll*/
595 	IF (asg_rec.period_type in ('Calendar Month','Lunar Month')) THEN
596 	xml_tab(xml_ctr).tagname := 'sal_per_month';
597 	xml_tab(xml_ctr).tagvalue := l_pay;
598 	xml_ctr := xml_ctr +1;
599 	xml_tab(xml_ctr).tagname := 'monthly_payroll';
600 	xml_tab(xml_ctr).tagvalue := l_check;
601 	xml_ctr := xml_ctr +1;
602 
603 	ELSIF(asg_rec.period_type = 'Bi-Week') THEN
604 	xml_tab(xml_ctr).tagname := 'sal_per_bi_week';
605 	xml_tab(xml_ctr).tagvalue := l_pay;
606 	xml_ctr := xml_ctr +1;
607 	xml_tab(xml_ctr).tagname := 'bi_weekly_payroll';
608 	xml_tab(xml_ctr).tagvalue := l_check;
609 	xml_ctr := xml_ctr +1;
610 
611 	ELSIF(asg_rec.period_type = 'Week') THEN
612 	xml_tab(xml_ctr).tagname := 'sal_per_week';
613 	xml_tab(xml_ctr).tagvalue := l_pay;
614 	xml_ctr := xml_ctr +1;
615 	xml_tab(xml_ctr).tagname := 'weekly_payroll';
616 	xml_tab(xml_ctr).tagvalue := l_check;
617 	xml_ctr := xml_ctr +1;
618 	END IF;
619 
620 	xml_tab(xml_ctr).tagname := 'last_salary';
621 	xml_tab(xml_ctr).tagvalue := l_pay;
622 	xml_ctr := xml_ctr +1;
623 /*Bug 5059274 fix- Continuos payment details not displayed*/
624 /*	IF (asg_rec.asg_end_date <> TO_DATE('31/12/4712','DD/MM/YYYY')) THEN
625 	xml_tab(xml_ctr).tagname := 'cont_payment_no';
626 	xml_tab(xml_ctr).tagvalue := l_check;
627 	xml_ctr := xml_ctr +1;
628 	xml_tab(xml_ctr).tagname := 'asg_end_date';
629 	xml_tab(xml_ctr).tagvalue := asg_rec.asg_end_date;
630 	xml_ctr := xml_ctr +1;
631 
632 	ELSIF  (asg_rec.asg_end_date = TO_DATE('31/12/4712','DD/MM/YYYY')) THEN
633 	xml_tab(xml_ctr).tagname := 'cont_payment_yes';
634 	xml_tab(xml_ctr).tagvalue := l_check;
635 	xml_ctr := xml_ctr +1;
636 	END IF;*/
637 	xml_tab(xml_ctr).tagname := 'abs_start_date';
638 	xml_tab(xml_ctr).tagvalue := asg_rec.absence_start_date;
639 	xml_ctr := xml_ctr +1;
640 
641         /*Bug 5040140 fix- if the end date is null then absence end date field in the report
642 	   should be blank*/
643 	xml_tab(xml_ctr).tagname := 'abs_end_date';
644 	IF asg_rec.absence_end_date = TO_DATE('31/12/4712','DD/MM/YYYY') THEN
645 	xml_tab(xml_ctr).tagvalue := NULL ;
646 	ELSE
647 	xml_tab(xml_ctr).tagvalue := asg_rec.absence_end_date;
648 	END IF ;
649 	xml_ctr := xml_ctr +1;
650 
651 	/*xml_tab(xml_ctr).tagname := 'abs_end_date';
652 	xml_tab(xml_ctr).tagvalue := asg_rec.absence_end_date;
653 	xml_ctr := xml_ctr +1;*/
654 
655 	xml_tab(xml_ctr).tagname := 'le_name';
656 	xml_tab(xml_ctr).tagvalue := hr_dk_utility.REPLACE_SPECIAL_CHARS(le_rec.le_name);
657 	xml_ctr := xml_ctr +1;
658 
659 	xml_tab(xml_ctr).tagname := 'le_address_pcode';
660 	xml_tab(xml_ctr).tagvalue := hr_dk_utility.REPLACE_SPECIAL_CHARS(le_rec.le_address1)||hr_dk_utility.REPLACE_SPECIAL_CHARS(le_rec.le_postalcode);
661 	xml_ctr := xml_ctr +1;
662 
663 	xml_tab(xml_ctr).tagname := 'le_phone';
664 	xml_tab(xml_ctr).tagvalue := hr_dk_utility.REPLACE_SPECIAL_CHARS(le_rec.le_phone);
665 	xml_ctr := xml_ctr +1;
666 
667 	xml_tab(xml_ctr).tagname := 'le_email';
668 	xml_tab(xml_ctr).tagvalue := hr_dk_utility.REPLACE_SPECIAL_CHARS(le_rec.le_email);
669 	xml_ctr := xml_ctr +1;
670 
671 /*Before 8 weeks*/
672 /*IF  ((asg_rec.absence_start_date - asg_rec.asg_start_date) < 57) THEN
673 IF  ((asg_rec.absence_start_date - emp_rec.person_start_date ) <57) THEN
674 	xml_tab(xml_ctr).tagname := 'before_8_weeks';
675 	xml_tab(xml_ctr).tagvalue := l_check;
676 	xml_ctr := xml_ctr +1;
677 END IF ;
678 END IF ;*/
679 
680 /*call to get the  working hours for the last eight weeks*/
681 /*l_8weeks_work_hrs := pay_dk_sickpay_pkg.get_worked_hours(
682 					   asg_rec.asg_id
683 					  ,asg_rec.absence_start_date - 57
684 					  ,asg_rec.absence_start_date - 1) ;
685 
686 
687 IF( l_8weeks_work_hrs = -1) THEN
688 l_8weeks_work_hrs:= working_hours_per_week(asg_rec.asg_id,soft_coded_rec.le_id,l_payroll_proc_start_date) * 8;
689 END IF ;*/
690 
691 /*Bug 5045710 fix*/
692 
693 IF (soft_coded_rec.asg_work_pattern IS NULL) THEN
694 /*if work pattern not defined at the assignment level, get from the legal employer level*/
695     OPEN csr_le_work_pattern(soft_coded_rec.le_id);
696     FETCH csr_le_work_pattern INTO le_work_pattern_rec;
697     l_work_pattern := le_work_pattern_rec.le_work_pattern;
698     CLOSE csr_le_work_pattern;
699 ELSE
700     l_work_pattern := soft_coded_rec.asg_work_pattern;
701 END IF;
702 
703 /*Call to get the worked days*/
704 l_worked_days := pay_dk_sickpay_pkg.get_worked_days
705 			(asg_rec.asg_id
706 			,asg_rec.asg_start_date
707 			,asg_rec.absence_start_date-1);
708 
709 IF (l_work_pattern = '6DAY') THEN
710 /* Reopened bug 5045710 fix- checking the flag if the worked days is less than 48 days*/
711     IF l_worked_days < l_dk_6day_pattern_min_days THEN -- check if the worked days < 48 days
712 	xml_tab(xml_ctr).tagname := 'before_8_weeks';
713 	xml_tab(xml_ctr).tagvalue := l_check;
714 	xml_ctr := xml_ctr +1;
715     ELSE
716 	l_worked_days:= l_dk_6day_pattern_min_days;
717     END IF ;
718 ELSE
719     /*By default work pattern will be 5 days*/
720     /* Reopened bug 5045710 fix- checking the flag if the worked days is less than 40 days*/
721     IF l_worked_days < l_dk_5day_pattern_min_days THEN -- check if the worked days < 40 days
722 	xml_tab(xml_ctr).tagname := 'before_8_weeks';
723 	xml_tab(xml_ctr).tagvalue := l_check;
724 	xml_ctr := xml_ctr +1;
725     ELSE
726        l_worked_days:= l_dk_5day_pattern_min_days;
727     END IF ;
728 END IF ;
729 
730 l_74hrs := pay_dk_sickpay_pkg.get_worked_hours_flag
731                     (asg_rec.asg_id
732                      ,l_worked_days
733                      ,l_dk_sickpay_min_work_hours
734                      ,asg_rec.absence_start_date-1);
735 
736 --IF l_8weeks_work_hrs < 74 THEN
737 
738 IF l_74hrs = 'N' THEN
739 	xml_tab(xml_ctr).tagname := '8weeks_work_hrs';
740 	xml_tab(xml_ctr).tagvalue := l_check;
741 	xml_ctr := xml_ctr +1;
742 
743 END IF ;
744 
745 IF (l_section27 = 'Y') THEN
746 	xml_tab(xml_ctr).tagname := 'section27';
747 	xml_tab(xml_ctr).tagvalue := l_check;
748 	xml_ctr := xml_ctr +1;
749 END IF;
750 
751 IF (soft_coded_rec.section28 = 'Y') THEN
752 	xml_tab(xml_ctr).tagname := 'section28';
753 	xml_tab(xml_ctr).tagvalue := l_check;
754 	xml_ctr := xml_ctr +1;
755 END IF;
756 
757 	xml_tab(xml_ctr).tagname := 'se_nr';
758 	xml_tab(xml_ctr).tagvalue := '   ';
759 	xml_ctr := xml_ctr +1;
760 END IF ;
761 --l_person_id := asg_rec.person_id;
762 END LOOP ;
763 
764 write_to_clob(p_xml);
765 
766 EXCEPTION WHEN OTHERS THEN
767 NULL ;
768 END POPULATE_DETAILS;
769 
770 ------------------------------------------------------------------------------------------------------------
771 
772 procedure WRITE_TO_CLOB (p_xml out nocopy clob) is
773 l_xfdf_string clob;
774 l_str1 varchar2(240);
775 l_str2 varchar2(240);
776 l_str3 varchar2(240);
777 l_str4 varchar2(240);
778 l_str5 varchar2(240);
779 l_IANA_charset VARCHAR2 (50);
780 
781 BEGIN
782 
783 l_str1 := '<field name="';
784 l_str2 := '">';
785 l_str3 := '<value>';
786 l_str4 := '</value></field>';
787 l_str5 := '</xfdf>';
791 dbms_lob.createtemporary(p_xml,false,dbms_lob.call);
788 l_IANA_charset :=PAY_DK_GENERAL.get_IANA_charset ;
789 
790 
792 
793 /*Setting the Character Set Dynamically*/
794 --p_xml := '<?xml version = "1.0" encoding = "UTF-8"?>';
795 p_xml := '<?xml version = "1.0" encoding = "'||l_IANA_charset||'"?>';
796 p_xml := p_xml || '<xfdf xmlns="http://ns.adobe.com/xfdf/" xml:space="preserve">';
797 dbms_lob.open(p_xml,dbms_lob.lob_readwrite);
798 if xml_tab.count > 0 then
799  for xml_ctr in xml_tab.first .. xml_tab.last LOOP
800  dbms_lob.writeappend( p_xml, length(l_str1),l_str1 );
801  dbms_lob.writeappend( p_xml, length(xml_tab(xml_ctr).tagname), xml_tab(xml_ctr).tagname);
802  dbms_lob.writeappend( p_xml, length(l_str2),l_str2 );
803  dbms_lob.writeappend( p_xml, length(l_str3),l_str3 );
804  dbms_lob.writeappend( p_xml, length(nvl(xml_tab(xml_ctr).tagvalue,' ')),nvl(xml_tab(xml_ctr).tagvalue,' '));
805  dbms_lob.writeappend( p_xml, length(l_str4),l_str4 );
806  end loop;
807 end if;
808 dbms_lob.writeappend( p_xml, length(l_str5),l_str5 );
809 --dbms_lob.createtemporary(p_xml,true);
810 --clob_to_blob(l_xfdf_string,p_xml);
811 exception
812 when others then
813 hr_utility.trace('sqleerm ' || sqlerrm);
814 hr_utility.raise_error;
815 end write_to_clob;
816 -------------------------------------------------------------------------------------------------------------------------
817  END PAY_DK_SICKNESS_DP201;