[Home] [Help]
PACKAGE BODY: APPS.PAY_DK_SICKNESS_DP202
Source
1 PACKAGE BODY PAY_DK_SICKNESS_DP202 AS
2 /* $Header: pydkdp202.pkb 120.12 2012/01/19 09:01:07 rpahune ship $ */
3 g_debug boolean := hr_utility.debug_enabled;
4 g_err_num NUMBER;
5 l_run_date DATE ;
6 l_person_id NUMBER ;
7
8 -------------------------------------------------------------------------------------------------------
9 /*Function to get the defined balance id */
10 FUNCTION GET_DEFINED_BALANCE_ID
11 (p_balance_name IN VARCHAR2
12 ,p_dbi_suffix IN VARCHAR2
13 ,p_business_group_id IN NUMBER)
14 RETURN NUMBER IS
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
24 pay_defined_balances pdb
25 ,pay_balance_types pbt
26 ,pay_balance_dimensions pbd
27 WHERE
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 to get the last pay for 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 ,p_period_type OUT NOCOPY VARCHAR2
49 )
50 IS
51
52 CURSOR csr_asg_act_id IS
53 SELECT
54 *
55 FROM
56 (
57 SELECT
58 paa1.assignment_action_id,ppf.period_type
59 FROM
60 pay_assignment_actions paa1
61 ,pay_payroll_actions ppa1
62 ,pay_assignment_actions paa2
63 ,pay_payroll_actions ppa2
64 ,pay_action_interlocks pai
65 ,pay_payrolls_f ppf
66 WHERE
67 paa1.assignment_id = p_assignment_id
68 AND paa1.action_status IN ('C','S') -- 10229494
69 AND paa1.payroll_action_id = ppa1.payroll_action_id
70 AND ppa1.action_type IN ('R','Q') -- Payroll Run or Quickpay Run
71 AND ppa1.date_earned <= p_effective_date
72
73 --for prepayments
74 AND paa2.action_status IN ('C','S') -- 10229494
75 AND paa2.assignment_action_id = pai.locking_action_id
76 AND paa2.payroll_action_id = ppa2.payroll_action_id
77 AND ppa2.action_type IN ('P','U')
78 AND ppa2.date_earned <= p_effective_date
79
80 AND paa1.ASSIGNMENT_ACTION_ID = pai.locked_action_id
81
82 AND ppf.payroll_id = ppa1.payroll_id
83 ORDER BY ppa1.date_earned desc)
84 WHERE
85 ROWNUM=1;
86
87 CURSOR csr_pay(asg_act_id NUMBER ) IS
88 SELECT
89 sum(pay_balance_pkg.get_value(
90 get_defined_balance_id(pbt.balance_name,'_ASG_PTD',p_business_group_id)
91 ,asg_act_id)) - pay_balance_pkg.get_value(
92 get_defined_balance_id('Employee AMB Deduction','_ASG_PTD',p_business_group_id)
93 ,asg_act_id) pay_value
94 FROM
95 pay_balance_types pbt
96 ,pay_balance_categories_f pbcf
97 WHERE
98 pbcf.legislation_code = 'DK'
99 AND pbcf.category_name = 'Salary Reporting'
100 AND pbcf.balance_category_id = pbt.balance_category_id;
101
102 l_asg_act_id number;
103
104
105 BEGIN
106
107 OPEN csr_asg_act_id;
108 FETCH csr_asg_act_id INTO l_asg_act_id,p_period_type;
109 CLOSE csr_asg_act_id;
110
111 OPEN csr_pay(l_asg_act_id);
112 FETCH csr_pay INTO p_pay;
113 CLOSE csr_pay;
114
115 END last_pay;
116 -------------------------------------------------------------------------------------------------------------------------
117
118 /*Procudure to get the sick leave details for reporting*/
119 /*Bug 5059274 fix- Added p_start_date and p_end_Date parameters*/
120 PROCEDURE POPULATE_DETAILS
121 (p_template_name in VARCHAR2
122 ,p_assignment_id NUMBER
123 ,p_person_id NUMBER
124 ,p_start_date IN VARCHAR2
125 ,p_end_date IN VARCHAR2
126 ,p_le_phone_number IN VARCHAR2
127 ,p_le_email_addr IN varchar2
128 ,p_business_group_id NUMBER
129 , p_xml out nocopy clob)
130 IS
131
132 /*Parameters to store the start and end dates, in order to provide date validation the parameters
133 are accepeted as varchar2 and converted into date type*/
134 l_start_date DATE;
135 l_end_date DATE;
136
137 --cursor to fetch employee details
138 CURSOR csr_emp(p_effective_date DATE)IS
139 SELECT
140 *
141 FROM
142 (
143 SELECT
144 /*papf.first_name first_name
145 ,papf.middle_names middle_name
146 ,papf.last_name last_name*/
147 /*Bug 5049222 fix- Employee name formatting*/
148 --SUBSTR (papf.first_name,1,40)||NVL2(papf.middle_names,','||SUBSTR(papf.middle_names,1,40),NULL)||','||SUBSTR(papf.last_name,1,40) ename
149 /*Reopened Bug 5049222 fix*/
150 SUBSTR (papf.last_name,1,40)||', '||SUBSTR(papf.first_name,1,40)||NVL2(papf.middle_names,' '||SUBSTR(papf.middle_names,1,40),NULL) ename
151 ,papf.national_identifier national_identifier
152 FROM
153 per_all_people_f papf
154 WHERE
155 papf.person_id = p_person_id
156 AND p_effective_date between papf.effective_start_date and papf.effective_end_date
157 AND papf.current_employee_flag = 'Y')
158 GROUP BY ename,national_identifier;
159
160
161 /*Bug 5039491fix -cursor to fetch address*/
162 CURSOR csr_address(p_effective_date DATE) IS
163 SELECT
164 pa.address_line1 address_line1
165 ,pa.address_line2 address_line2
166 ,pa.postal_code postal_code
167 FROM
168 per_addresses pa
169 WHERE
170 pa.person_id = p_person_id
171 AND pa.primary_flag = 'Y'
172 AND p_effective_date BETWEEN pa.date_from AND nvl(pa.date_to,TO_DATE('31/12/4712','dd/mm/yyyy'));
173
174
175 /*Bug fix- 5059274 cursor to get the assignment details based on p_start_date and p_end_date*/
176 CURSOR csr_asg IS
177 SELECT
178 paaf.person_id person_id
179 ,paaf.assignment_id asg_id
180 ,paaf.assignment_number asg_num
181 ,MIN (paaf.effective_start_date) asg_start_date
182 ,MAX(paaf.effective_end_date) asg_end_date
183 ,peef.effective_start_date absence_start_date
184 ,peef.effective_end_date absence_end_date
185 ,paaf.payroll_id payroll_id
186 FROM
187 per_all_assignments_f paaf
188 ,pay_element_types_f petf
189 ,pay_element_entries_f peef
190 WHERE
191 paaf.person_id = p_person_id
192 AND paaf.assignment_id = NVL(p_assignment_id,paaf.assignment_id)
193 -- AND l_run_date BETWEEN paaf.effective_start_date AND paaf.effective_end_date
194 AND paaf.assignment_status_type_id = 1
195 AND petf.element_name LIKE 'Absent Sick' -- To check for the Sickness element
196 -- AND l_run_date BETWEEN petf.effective_start_date AND petf.effective_end_date
197
198 AND peef.assignment_id = paaf.assignment_id
199 AND peef.element_type_id = petf.element_type_id
200 AND peef.effective_start_date BETWEEN l_start_date and l_end_date
201 GROUP BY
202 paaf.person_id
203 ,paaf.assignment_id
204 ,paaf.assignment_number
205 ,peef.effective_start_date
206 ,peef.effective_end_date
207 ,paaf.payroll_id;
208 -- AND peef.effective_start_date = l_run_date; -- To Check whether the leave started on Run Date(i/p Paramter - 1)
209
210
211
212 CURSOR csr_le(p_effective_date DATE) IS
213 SELECT
214 haou.organization_id le_id
215 ,SUBSTR(haou.NAME,1,30) le_name
216 ,SUBSTR(hl.address_line_1,1,30) le_address1
217 ,DECODE(hl.postal_code,NULL,' ',','||substr(hl.postal_code,1,5)) le_postalcode
218 ,SUBSTR (p_le_phone_number,1,30) le_phone
219 ,substr(p_le_email_addr,1,30) le_email
220 FROM
221 per_all_assignments_f paaf
222 ,hr_soft_coding_keyflex hsck
223 ,hr_all_organization_units haou
224 ,hr_locations hl
225 WHERE
226 paaf.assignment_id = p_assignment_id
227 AND p_effective_date BETWEEN paaf.effective_start_date AND paaf.effective_end_date
228 AND paaf.assignment_status_type_id = 1
229 AND hsck.soft_coding_keyflex_id = paaf.soft_coding_keyflex_id
230 AND haou.organization_id = hsck.segment1
231 AND hl.location_id(+) = haou.location_id;
232
233 /*Bug 5049222 fix- Cursor to get the cvr number*/
234 CURSOR csr_le_cvr(p_le_id NUMBER) IS
235 SELECT
236 hoi.org_information1 le_cvr_num
237 FROM
238 hr_organization_information hoi
239 WHERE
240 hoi.organization_id = p_le_id
241 AND hoi.org_information_context = 'DK_LEGAL_ENTITY_DETAILS';
242
243 /*Bug fix- 5059274 cursor to get the payroll period start date in which the
244 absence start date falls*/
245 CURSOR csr_payroll_start_date(p_payroll_id NUMBER,p_absence_start_date DATE)
246 IS
247 SELECT
248 ptp.start_date
249 FROM
250 per_time_periods ptp
251 WHERE
252 ptp.payroll_id = p_payroll_id
253 AND p_absence_start_date BETWEEN ptp.start_date AND ptp.end_Date;
254
255
256 -- Variable declaration
257 l_var_work_hrs NUMBER ;
258 emp_rec csr_emp%ROWTYPE ;
259 asg_rec csr_asg%ROWTYPE ;
260 le_rec csr_le%ROWTYPE ;
261 addr_rec csr_address%ROWTYPE ;
262 xml_ctr number;
263 l_pay VARCHAR2(150);
264 l_period_type pay_payrolls_f.period_type%TYPE;
265 l_check VARCHAR2(1);
266 le_cvr_rec csr_le_cvr%ROWTYPE; --Bug 5049222 fix
267 l_payroll_proc_start_date DATE ;
268
269 BEGIN
270 l_check := 'X';
271 xml_ctr := 0;
272 l_start_date :=FND_DATE.CANONICAL_TO_DATE(p_start_date);
273 l_end_date :=FND_DATE.CANONICAL_TO_DATE(p_end_date);
274
275 FOR asg_rec IN csr_asg LOOP
276
277 OPEN csr_payroll_start_date(asg_rec.payroll_id,asg_rec.absence_start_date);
278 FETCH csr_payroll_start_date INTO l_payroll_proc_start_date;
279 CLOSE csr_payroll_start_date;
280
281 IF (csr_asg%ROWCOUNT = 1) THEN
282 OPEN csr_emp(l_payroll_proc_start_date);
283 FETCH csr_emp INTO emp_rec;
284 CLOSE csr_emp;
285 END IF ;
286
287 OPEN csr_address(l_payroll_proc_start_date);
288 FETCH csr_address INTO addr_rec;
289 CLOSE csr_address;
290
291 OPEN csr_le(l_payroll_proc_start_date);
292 FETCH csr_le INTO le_rec;
293 CLOSE csr_le;
294
295 OPEN csr_le_cvr(le_rec.le_id);
296 FETCH csr_le_cvr INTO le_cvr_rec;
297 CLOSE csr_le_cvr;
298
299 /*Bug 5049222 fix*/
300 xml_tab(xml_ctr).tagname := 'emp_name';
301 xml_tab(xml_ctr).tagvalue := hr_dk_utility.REPLACE_SPECIAL_CHARS(emp_rec.ename);
302 xml_ctr := xml_ctr +1;
303
304 xml_tab(xml_ctr).tagname := 'address_line1';
305 xml_tab(xml_ctr).tagvalue := hr_dk_utility.REPLACE_SPECIAL_CHARS(substr(addr_rec.address_line1,1,40));
306 xml_ctr := xml_ctr +1;
307
308 xml_tab(xml_ctr).tagname := 'address_line2';
309 xml_tab(xml_ctr).tagvalue := hr_dk_utility.REPLACE_SPECIAL_CHARS(substr(addr_rec.address_line2,1,40));
310 xml_ctr := xml_ctr +1;
311
312 xml_tab(xml_ctr).tagname := 'postal_code';
313 xml_tab(xml_ctr).tagvalue := substr(addr_rec.postal_code,1,40);
314 xml_ctr := xml_ctr +1;
315
316 xml_tab(xml_ctr).tagname := 'asg_num';
317 xml_tab(xml_ctr).tagvalue := asg_rec.asg_num;
318 xml_ctr := xml_ctr +1;
319
320 /*Bug 5049222 fix*/
321 xml_tab(xml_ctr).tagname := 'le_cvr_number';
322 xml_tab(xml_ctr).tagvalue := le_cvr_rec.le_cvr_num;
323 xml_ctr := xml_ctr +1;
324
325 xml_tab(xml_ctr).tagname := 'national_identifier';
326 xml_tab(xml_ctr).tagvalue := emp_rec.national_identifier;
327 xml_ctr := xml_ctr +1;
328
329 /*Bug 5059274 fix- Continuos payment details not displayed*/
330 /* IF (asg_rec.asg_end_date <> TO_DATE('31/12/4712','DD/MM/YYYY')) THEN
331 xml_tab(xml_ctr).tagname := 'cont_payment_no';
332 xml_tab(xml_ctr).tagvalue := l_check;
333 xml_ctr := xml_ctr +1;
334 xml_tab(xml_ctr).tagname := 'asg_end_date';
335 xml_tab(xml_ctr).tagvalue := asg_rec.asg_end_date;
336 xml_ctr := xml_ctr +1;
337
338 ELSIF (asg_rec.asg_end_date = TO_DATE('31/12/4712','DD/MM/YYYY')) THEN
339 xml_tab(xml_ctr).tagname := 'cont_payment_yes';
340 xml_tab(xml_ctr).tagvalue := l_check;
341 xml_ctr := xml_ctr +1;
342 END IF;*/
343
344 /* call to get the last pay for the assignment*/
345 IF asg_rec.absence_end_date IS NOT NULL AND asg_rec.absence_end_date <= l_end_date THEN
346 last_pay(p_business_group_id,asg_rec.asg_id,asg_rec.absence_end_date,l_pay,l_period_type);
347 ELSE
348 last_pay(p_business_group_id,asg_rec.asg_id,l_end_date,l_pay,l_period_type);
349 END IF ;
350
351 -- l_pay := TRIM(TO_CHAR(ROUND(l_pay,2),'99g99g99g990d99','NLS_NUMERIC_CHARACTERS = '',.'''));
352 -- l_pay := TRIM(TO_CHAR(ROUND(NVL(FND_NUMBER.canonical_to_number(l_pay),0),2),'999G999G990D99'));
353 l_pay := TRIM(TO_CHAR(ROUND(FND_NUMBER.canonical_to_number(l_pay),2),'999G999G990D99'));
354
355 /*The employee's pay condition*/
356 /* Changes for Lunar Payroll*/
357 IF l_period_type in ('Calendar Month','Lunar Month') THEN
358 xml_tab(xml_ctr).tagname := 'sal_per_month';
359 xml_tab(xml_ctr).tagvalue := l_pay;
360 xml_ctr := xml_ctr +1;
361
362 ELSIF(l_period_type = 'Bi-Week') THEN
363 xml_tab(xml_ctr).tagname := 'sal_per_bi_week';
364 xml_tab(xml_ctr).tagvalue := l_pay;
365 xml_ctr := xml_ctr +1;
366
367 ELSIF(l_period_type = 'Week') THEN
368 xml_tab(xml_ctr).tagname := 'sal_per_week';
369 xml_tab(xml_ctr).tagvalue := l_pay;
370 xml_ctr := xml_ctr +1;
371 END IF;
372
373 xml_tab(xml_ctr).tagname := 'last_salary';
374 xml_tab(xml_ctr).tagvalue := l_pay;
375 xml_ctr := xml_ctr +1;
376
377 xml_tab(xml_ctr).tagname := 'abs_start_date';
378 xml_tab(xml_ctr).tagvalue := asg_rec.absence_start_date;
379 xml_ctr := xml_ctr +1;
380
381 /*Bug 5040140 fix- if the end date is null then absence end date field in the report
382 should be blank*/
383 xml_tab(xml_ctr).tagname := 'abs_end_date';
384 IF asg_rec.absence_end_date = TO_DATE('31/12/4712','DD/MM/YYYY') THEN
385 xml_tab(xml_ctr).tagvalue := NULL ;
386 ELSE
387 xml_tab(xml_ctr).tagvalue := asg_rec.absence_end_date;
388 END IF ;
389 xml_ctr := xml_ctr +1;
390
391 /*xml_tab(xml_ctr).tagname := 'abs_end_date';
392 xml_tab(xml_ctr).tagvalue := asg_rec.absence_end_date;
393 xml_ctr := xml_ctr +1;*/
394
395 xml_tab(xml_ctr).tagname := 'le_name';
396 xml_tab(xml_ctr).tagvalue := hr_dk_utility.REPLACE_SPECIAL_CHARS(le_rec.le_name);
397 xml_ctr := xml_ctr +1;
398
399
400 xml_tab(xml_ctr).tagname := 'le_address_pcode';
401 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);
402 xml_ctr := xml_ctr +1;
403
404 IF (le_rec.le_phone IS NOT NULL AND le_rec.le_email IS NOT NULL ) THEN
405 xml_tab(xml_ctr).tagname := 'le_phone_email';
406 xml_tab(xml_ctr).tagvalue := hr_dk_utility.REPLACE_SPECIAL_CHARS(le_rec.le_phone) ||','||hr_dk_utility.REPLACE_SPECIAL_CHARS(le_rec.le_email);
407 xml_ctr := xml_ctr +1;
408 ELSE
409 xml_tab(xml_ctr).tagname := 'le_phone_email';
410 xml_tab(xml_ctr).tagvalue := hr_dk_utility.REPLACE_SPECIAL_CHARS(le_rec.le_phone) ||hr_dk_utility.REPLACE_SPECIAL_CHARS(le_rec.le_email);
411 xml_ctr := xml_ctr +1;
412
413
414 END IF ;
415
416 END LOOP ;
417
418 write_to_clob(p_xml);
419
420 EXCEPTION WHEN OTHERS THEN
421 NULL;
422 END populate_details;
423
424 ------------------------------------------------------------------------------------------------------------
425
426 procedure WRITE_TO_CLOB (p_xml out nocopy clob) is
427 l_xfdf_string clob;
428 l_str1 varchar2(240);
429 l_str2 varchar2(240);
430 l_str3 varchar2(240);
431 l_str4 varchar2(240);
432 l_str5 varchar2(240);
433 l_IANA_charset VARCHAR2 (50);
434
435 BEGIN
436
437 l_str1 := '<field name="';
438 l_str2 := '">';
439 l_str3 := '<value>';
440 l_str4 := '</value></field>';
441 l_str5 := '</xfdf>';
442 l_IANA_charset :=PAY_DK_GENERAL.get_IANA_charset ;
443
444 dbms_lob.createtemporary(p_xml,false,dbms_lob.call);
445
446 /*Setting the Character Set Dynamically*/
447 --p_xml := '<?xml version = "1.0" encoding = "UTF-8"?>';
448 p_xml := '<?xml version = "1.0" encoding = "'||l_IANA_charset||'"?>';
449 p_xml := p_xml || '<xfdf xmlns="http://ns.adobe.com/xfdf/" xml:space="preserve">';
450 dbms_lob.open(p_xml,dbms_lob.lob_readwrite);
451 if xml_tab.count > 0 then
452 for xml_ctr in xml_tab.first .. xml_tab.last LOOP
453 dbms_lob.writeappend( p_xml, length(l_str1),l_str1 );
454 dbms_lob.writeappend( p_xml, length(xml_tab(xml_ctr).tagname), xml_tab(xml_ctr).tagname);
455 dbms_lob.writeappend( p_xml, length(l_str2),l_str2 );
456 dbms_lob.writeappend( p_xml, length(l_str3),l_str3 );
457 dbms_lob.writeappend( p_xml, length(nvl(xml_tab(xml_ctr).tagvalue,' ')),nvl(xml_tab(xml_ctr).tagvalue,' '));
458 dbms_lob.writeappend( p_xml, length(l_str4),l_str4 );
459 end loop;
460 end if;
461 dbms_lob.writeappend( p_xml, length(l_str5),l_str5 );
462 --dbms_lob.createtemporary(p_xml,true);
463 --clob_to_blob(l_xfdf_string,p_xml);
464 exception
465 when others then
466 hr_utility.trace('sqleerm ' || sqlerrm);
467 hr_utility.raise_error;
468 end write_to_clob;
469
470 -------------------------------------------------------------------------------------------------------------------------
471 END PAY_DK_SICKNESS_DP202;