DBA Data[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;