[Home] [Help]
PACKAGE BODY: APPS.PAY_SE_SLSR
Source
1 PACKAGE BODY pay_se_slsr AS
2 /* $Header: pyseslsr.pkb 120.0.12000000.1 2007/07/18 11:18:03 psingla noship $ */
3 PROCEDURE get_data (
4 p_business_group_id IN NUMBER,
5 p_payroll_action_id IN VARCHAR2,
6 p_template_name IN VARCHAR2,
7 p_xml OUT NOCOPY CLOB
8 ) IS
9 --Cursors needed for report
10 CURSOR csr_all_legal_employer (
11 csr_v_pa_id pay_action_information.action_context_id%TYPE,
12 p_curr_prev_std_hour VARCHAR2,
13 p_leagal_employer_id VARCHAR2
14 ) IS
15 SELECT action_information3 legal_employer_id, action_information4 legal_employer_name,
16 action_information5
17 org_number, fnd_number.canonical_to_number (action_information6) week_hours,
18 fnd_number.canonical_to_number (action_information7)
19 daily_hours,
20 fnd_number.canonical_to_number (action_information8) days_per_year,
21 fnd_number.canonical_to_number (action_information9)
22 hours_per_year,
23 fnd_date.canonical_to_date (action_information10) start_date,
24 fnd_date.canonical_to_date (action_information11)
25 end_date
26 FROM pay_action_information
27 WHERE action_context_type = 'PA'
28 AND action_context_id = csr_v_pa_id
29 AND action_information_category = 'EMEA REPORT INFORMATION'
30 AND action_information1 = 'PYSESLSA'
31 AND action_information3 = nvl (p_leagal_employer_id, action_information3)
32 AND action_information2 = p_curr_prev_std_hour;
33
34 CURSOR csr_all_legal_employer_l (
35 csr_v_pa_id pay_action_information.action_context_id%TYPE,
36 p_curr_prev_std_hour VARCHAR2,
37 p_leagal_employer_id VARCHAR2
38 ) IS
39 SELECT action_information3 legal_employer_id, action_information4 legal_employer_name,
40 action_information5
41 org_number, fnd_number.canonical_to_number (action_information6) week_hours,
42 fnd_number.canonical_to_number (action_information7)
43 daily_hours,
44 fnd_number.canonical_to_number (action_information8) days_per_year,
45 fnd_number.canonical_to_number (action_information9)
46 hours_per_year,
47 fnd_date.canonical_to_date (action_information10) start_date,
48 fnd_date.canonical_to_date (action_information11)
49 end_date
50 FROM pay_action_information
51 WHERE action_context_type = 'PA'
52 AND action_context_id = csr_v_pa_id
53 AND action_information_category = 'EMEA REPORT INFORMATION'
54 AND action_information1 = 'PYSESLSA'
55 AND action_information3 = nvl (p_leagal_employer_id, action_information3)
56 AND action_information2 = p_curr_prev_std_hour;
57
58 l_all_legal_employer csr_all_legal_employer_l%ROWTYPE;
59
60 CURSOR csr_get_report_data (
61 csr_v_pa_id pay_action_information.action_context_id%TYPE,
62 p_curr_prev_data VARCHAR2,
63 p_leagal_employer_id VARCHAR2
64 ) IS
65 SELECT fnd_number.canonical_to_number (action_information6) men_lower_age_count,
66 fnd_number.canonical_to_number (action_information7)
67 men_middle_age_count,
68 fnd_number.canonical_to_number (action_information8) men_upper_age_count,
69 fnd_number.canonical_to_number (action_information9)
70 women_lower_age_count,
71 fnd_number.canonical_to_number (action_information10) women_middle_age_count,
72 fnd_number.canonical_to_number (action_information11)
73 women_upper_age_count,
74 fnd_number.canonical_to_number (action_information12) men_lower_age_work_hour,
75 fnd_number.canonical_to_number (action_information13)
76 men_middle_age_work_hour,
77 fnd_number.canonical_to_number (action_information14) men_upper_age_work_hour,
78 fnd_number.canonical_to_number (action_information15)
79 women_lower_age_work_hour,
80 fnd_number.canonical_to_number (action_information16)
81 women_middle_age_work_hour,
82 fnd_number.canonical_to_number (action_information17)
83 women_upper_age_work_hour,
84 fnd_number.canonical_to_number (action_information18) men_l_age_sick_leaves,
85 fnd_number.canonical_to_number (action_information19)
86 men_m_age_sick_leaves,
87 fnd_number.canonical_to_number (action_information20) men_u_age_sick_leaves,
88 fnd_number.canonical_to_number (action_information21)
89 women_l_age_sick_leaves,
90 fnd_number.canonical_to_number (action_information22) women_m_age_sick_leaves,
91 fnd_number.canonical_to_number (action_information23)
92 women_u_age_sick_leaves,
93 fnd_number.canonical_to_number (action_information24) men_l_age_l_sick_leaves,
94 fnd_number.canonical_to_number (action_information25)
95 men_m_age_l_sick_leaves,
96 fnd_number.canonical_to_number (action_information26) men_u_age_l_sick_leaves,
97 fnd_number.canonical_to_number (action_information27)
98 women_l_age_l_sick_leaves,
99 fnd_number.canonical_to_number (action_information28)
100 women_m_age_l_sick_leaves,
101 fnd_number.canonical_to_number (action_information29)
102 women_u_age_l_sick_leaves
103 FROM pay_action_information
104 WHERE action_context_type = 'PA'
105 AND action_context_id = csr_v_pa_id
106 AND action_information_category = 'EMEA REPORT INFORMATION'
107 AND action_information1 = 'PYSESLSA'
108 AND action_information3 = p_leagal_employer_id
109 AND action_information2 = p_curr_prev_data;
110
111 l_csr_get_curr_report_data csr_get_report_data%ROWTYPE;
112 l_csr_get_prev_report_data csr_get_report_data%ROWTYPE;
113
114 /*
115 CURSOR csr_get_report_stat (
116 csr_v_pa_id pay_action_information.action_context_id%TYPE,
117 p_curr_prev_stat VARCHAR2,
118 p_leagal_employer_id VARCHAR2
119 ) IS
120 SELECT fnd_number.canonical_to_number (action_information6) leave_to_work_hours,
121 fnd_number.canonical_to_number (action_information7)
122 long_leave_part,
123 fnd_number.canonical_to_number (action_information8) men_leave_to_work_hour,
124 fnd_number.canonical_to_number (action_information9)
125 women_leave_to_work_hour,
126 fnd_number.canonical_to_number (action_information10) leave_to_work_hour_lower,
127 fnd_number.canonical_to_number (action_information11)
128 leave_to_work_hour_middle,
129 fnd_number.canonical_to_number (action_information12) leave_to_work_hour_upper
130 FROM pay_action_information
131 WHERE action_context_type = 'PA'
132 AND action_context_id = csr_v_pa_id
133 AND action_information_category = 'EMEA REPORT INFORMATION'
134 AND action_information1 = 'PYSESLSA'
135 AND action_information3 = p_leagal_employer_id
136 AND action_information2 = p_curr_prev_stat;*/
137 CURSOR csr_get_report_stat (
138 csr_v_pa_id pay_action_information.action_context_id%TYPE,
139 p_curr_prev_stat VARCHAR2,
140 p_leagal_employer_id VARCHAR2
141 ) IS
142 SELECT fnd_number.canonical_to_number (action_information6) leave_to_work_hours,
143 fnd_number.canonical_to_number (action_information7)
144 long_leave_part,
145 fnd_number.canonical_to_number (action_information8) men_leave_to_work_hour,
146 fnd_number.canonical_to_number (action_information9)
147 women_leave_to_work_hour,
148 fnd_number.canonical_to_number (action_information10) leave_to_work_hour_lower,
149 fnd_number.canonical_to_number (action_information11)
150 leave_to_work_hour_middle,
151 fnd_number.canonical_to_number (action_information12) leave_to_work_hour_upper
152 FROM pay_action_information
153 WHERE action_context_type = 'PA'
154 AND action_context_id = csr_v_pa_id
155 AND action_information_category = 'EMEA REPORT INFORMATION'
156 AND action_information1 = 'PYSESLSA'
157 AND action_information3 = p_leagal_employer_id
158 AND action_information2 = p_curr_prev_stat;
159
160 l_csr_get_curr_report_stat csr_get_report_stat%ROWTYPE;
161 l_csr_get_prev_report_stat csr_get_report_stat%ROWTYPE;
162 l_payroll_action_id pay_action_information.action_information1%TYPE;
163 l_counter NUMBER := 0;
164 BEGIN
165 IF p_payroll_action_id IS NULL THEN
166 BEGIN
167 SELECT payroll_action_id
168 INTO l_payroll_action_id
169 FROM pay_payroll_actions ppa, fnd_conc_req_summary_v fcrs, fnd_conc_req_summary_v fcrs1
170 WHERE fcrs.request_id = fnd_global.conc_request_id
171 AND fcrs.priority_request_id = fcrs1.priority_request_id
172 AND ppa.request_id BETWEEN fcrs1.request_id AND fcrs.request_id
173 AND ppa.request_id = fcrs1.request_id;
174 EXCEPTION
175 WHEN OTHERS THEN
176 NULL;
177 END;
178 ELSE
179 l_payroll_action_id := p_payroll_action_id;
180 END IF;
181
182 FOR i IN csr_all_legal_employer (
183 csr_v_pa_id => l_payroll_action_id,
184 p_curr_prev_std_hour => 'LE_CURR_YR_STD_HOUR',
185 p_leagal_employer_id => NULL
186 )
187 LOOP
188 xml_tab (l_counter).tagname := 'ORG_NAME';
189 xml_tab (l_counter).tagvalue := i.legal_employer_name;
190 l_counter := l_counter + 1;
191 --
192 xml_tab (l_counter).tagname := 'ORG_NUM';
193 xml_tab (l_counter).tagvalue := i.org_number;
194 l_counter := l_counter + 1;
195 --
196 xml_tab (l_counter).tagname := 'CURR_WEEK_HOURS';
197 xml_tab (l_counter).tagvalue := i.week_hours;
198 l_counter := l_counter + 1;
199 --
200 xml_tab (l_counter).tagname := 'CURR_DAILY_HOURS';
201 xml_tab (l_counter).tagvalue := i.daily_hours;
202 l_counter := l_counter + 1;
203 --
204 xml_tab (l_counter).tagname := 'CURR_DAYS_PER_YEAR';
205 xml_tab (l_counter).tagvalue := i.days_per_year;
206 l_counter := l_counter + 1;
207 --
208 xml_tab (l_counter).tagname := 'CURR_HOURS_PER_YEAR';
209 xml_tab (l_counter).tagvalue := i.hours_per_year;
210 l_counter := l_counter + 1;
211 --
212 xml_tab (l_counter).tagname := 'CURR_START_DATE';
213 xml_tab (l_counter).tagvalue := to_char (i.start_date, 'YYYYMMDD');
214 l_counter := l_counter + 1;
215 xml_tab (l_counter).tagname := 'CURR_END_DATE';
216 xml_tab (l_counter).tagvalue := to_char (i.end_date, 'YYYYMMDD');
217 l_counter := l_counter + 1;
218 OPEN csr_all_legal_employer_l (l_payroll_action_id, 'LE_PREV_YR_STD_HOUR', i.legal_employer_id);
219 FETCH csr_all_legal_employer_l INTO l_all_legal_employer;
220 CLOSE csr_all_legal_employer_l;
221 --
222 --
223 xml_tab (l_counter).tagname := 'PREV_WEEK_HOURS';
224 xml_tab (l_counter).tagvalue := l_all_legal_employer.week_hours;
225 l_counter := l_counter + 1;
226 --
227 xml_tab (l_counter).tagname := 'PREV_DAILY_HOURS';
228 xml_tab (l_counter).tagvalue := l_all_legal_employer.daily_hours;
229 l_counter := l_counter + 1;
230 --
231 xml_tab (l_counter).tagname := 'PREV_DAYS_PER_YEAR';
232 xml_tab (l_counter).tagvalue := l_all_legal_employer.days_per_year;
233 l_counter := l_counter + 1;
234 --
235 xml_tab (l_counter).tagname := 'PREV_HOURS_PER_YEAR';
236 xml_tab (l_counter).tagvalue := l_all_legal_employer.hours_per_year;
237 l_counter := l_counter + 1;
238 --
239 xml_tab (l_counter).tagname := 'PREV_START_DATE';
240 xml_tab (l_counter).tagvalue := to_char (l_all_legal_employer.start_date, 'YYYYMMDD');
241 l_counter := l_counter + 1;
242 xml_tab (l_counter).tagname := 'PREV_END_DATE';
243 xml_tab (l_counter).tagvalue := to_char (l_all_legal_employer.end_date, 'YYYYMMDD');
244 l_counter := l_counter + 1;
245 /* Get The Report data for the current year */
246 OPEN csr_get_report_data (
247 csr_v_pa_id => l_payroll_action_id,
248 p_curr_prev_data => 'LE_CURR_YR_DATA',
249 p_leagal_employer_id => i.legal_employer_id
250 );
251 FETCH csr_get_report_data INTO l_csr_get_curr_report_data;
252 CLOSE csr_get_report_data;
253 xml_tab (l_counter).tagname := 'MEN_LOWER_AGE_COUNT_C';
254 xml_tab (l_counter).tagvalue := l_csr_get_curr_report_data.men_lower_age_count;
255 l_counter := l_counter + 1;
256 --
257
258 xml_tab (l_counter).tagname := 'MEN_MIDDLE_AGE_COUNT_C';
259 xml_tab (l_counter).tagvalue := l_csr_get_curr_report_data.men_middle_age_count;
260 l_counter := l_counter + 1;
261 --
262 xml_tab (l_counter).tagname := 'MEN_UPPER_AGE_COUNT_C';
263 xml_tab (l_counter).tagvalue := l_csr_get_curr_report_data.men_upper_age_count;
264 l_counter := l_counter + 1;
265 --
266 --
267 xml_tab (l_counter).tagname := 'WOMEN_LOWER_AGE_COUNT_C';
268 xml_tab (l_counter).tagvalue := l_csr_get_curr_report_data.women_lower_age_count;
269 l_counter := l_counter + 1;
270 --
271 --
272 xml_tab (l_counter).tagname := 'WOMEN_MIDDLE_AGE_COUNT_C';
273 xml_tab (l_counter).tagvalue := l_csr_get_curr_report_data.women_middle_age_count;
274 l_counter := l_counter + 1;
275 --
276 --
277 xml_tab (l_counter).tagname := 'WOMEN_UPPER_AGE_COUNT_C';
278 xml_tab (l_counter).tagvalue := l_csr_get_curr_report_data.women_upper_age_count;
279 l_counter := l_counter + 1;
280 xml_tab (l_counter).tagname := 'TOT_EMP_COUNT_C';
281 xml_tab (l_counter).tagvalue := l_csr_get_curr_report_data.men_lower_age_count
282 + l_csr_get_curr_report_data.men_middle_age_count
283 + l_csr_get_curr_report_data.men_upper_age_count
287 l_counter := l_counter + 1;
284 + l_csr_get_curr_report_data.women_lower_age_count
285 + l_csr_get_curr_report_data.women_middle_age_count
286 + l_csr_get_curr_report_data.women_upper_age_count;
288 --
289 xml_tab (l_counter).tagname := 'MEN_LOWER_AGE_HOUR_C';
290 xml_tab (l_counter).tagvalue := l_csr_get_curr_report_data.men_lower_age_work_hour;
291 l_counter := l_counter + 1;
292 --
293 xml_tab (l_counter).tagname := 'MEN_MIDDLE_AGE_HOUR_C';
294 xml_tab (l_counter).tagvalue := l_csr_get_curr_report_data.men_middle_age_work_hour;
295 l_counter := l_counter + 1;
296 --
297 xml_tab (l_counter).tagname := 'MEN_UPPER_AGE_HOUR_C';
298 xml_tab (l_counter).tagvalue := l_csr_get_curr_report_data.men_upper_age_work_hour;
299 l_counter := l_counter + 1;
300 --
301
302 xml_tab (l_counter).tagname := 'WOMEN_LOWER_AGE_HOUR_C';
303 xml_tab (l_counter).tagvalue := l_csr_get_curr_report_data.women_lower_age_work_hour;
304 l_counter := l_counter + 1;
305 --
306
307 xml_tab (l_counter).tagname := 'WOMEN_MIDDLE_AGE_HOUR_C';
308 xml_tab (l_counter).tagvalue := l_csr_get_curr_report_data.women_middle_age_work_hour;
309 l_counter := l_counter + 1;
310 --
311
312 xml_tab (l_counter).tagname := 'WOMEN_UPPER_AGE_HOUR_C';
313 xml_tab (l_counter).tagvalue := l_csr_get_curr_report_data.women_upper_age_work_hour;
314 l_counter := l_counter + 1;
315 --
316 --
317 xml_tab (l_counter).tagname := 'TOT_WORK_HOUR_C';
318 xml_tab (l_counter).tagvalue := l_csr_get_curr_report_data.men_lower_age_work_hour
319 + l_csr_get_curr_report_data.men_middle_age_work_hour
320 + l_csr_get_curr_report_data.men_upper_age_work_hour
321 + l_csr_get_curr_report_data.women_lower_age_work_hour
322 + l_csr_get_curr_report_data.women_middle_age_work_hour
323 + l_csr_get_curr_report_data.women_upper_age_work_hour;
324 l_counter := l_counter + 1;
325 xml_tab (l_counter).tagname := 'MEN_L_AGE_SICK_LEAVES_C';
326 xml_tab (l_counter).tagvalue := l_csr_get_curr_report_data.men_l_age_sick_leaves;
327 l_counter := l_counter + 1;
328 --
329 xml_tab (l_counter).tagname := 'MEN_M_AGE_SICK_LEAVES_C';
330 xml_tab (l_counter).tagvalue := l_csr_get_curr_report_data.men_m_age_sick_leaves;
331 l_counter := l_counter + 1;
332 --
333 xml_tab (l_counter).tagname := 'MEN_U_AGE_SICK_LEAVES_C';
334 xml_tab (l_counter).tagvalue := l_csr_get_curr_report_data.men_u_age_sick_leaves;
335 l_counter := l_counter + 1;
336 --
337 --
338 xml_tab (l_counter).tagname := 'WOMEN_L_AGE_SICK_LEAVES_C';
339 xml_tab (l_counter).tagvalue := l_csr_get_curr_report_data.women_l_age_sick_leaves;
340 l_counter := l_counter + 1;
341 --
342 --
343 xml_tab (l_counter).tagname := 'WOMEN_M_AGE_SICK_LEAVES_C';
344 xml_tab (l_counter).tagvalue := l_csr_get_curr_report_data.women_m_age_sick_leaves;
345 l_counter := l_counter + 1;
346 --
347 --
348 xml_tab (l_counter).tagname := 'WOMEN_U_AGE_SICK_LEAVES_C';
349 xml_tab (l_counter).tagvalue := l_csr_get_curr_report_data.women_u_age_sick_leaves;
350 l_counter := l_counter + 1;
351 --
352 xml_tab (l_counter).tagname := 'TOT_SICK_LEAVES_C';
353 xml_tab (l_counter).tagvalue := l_csr_get_curr_report_data.men_l_age_sick_leaves
354 + l_csr_get_curr_report_data.men_m_age_sick_leaves
355 + l_csr_get_curr_report_data.men_u_age_sick_leaves
356 + l_csr_get_curr_report_data.women_l_age_sick_leaves
357 + l_csr_get_curr_report_data.women_m_age_sick_leaves
358 + l_csr_get_curr_report_data.women_u_age_sick_leaves;
359 l_counter := l_counter + 1;
360 xml_tab (l_counter).tagname := 'MEN_L_AGE_L_SICK_LEAVES_C';
361 xml_tab (l_counter).tagvalue := l_csr_get_curr_report_data.men_l_age_l_sick_leaves;
362 l_counter := l_counter + 1;
363 --
364 xml_tab (l_counter).tagname := 'MEN_M_AGE_L_SICK_LEAVES_C';
365 xml_tab (l_counter).tagvalue := l_csr_get_curr_report_data.men_m_age_l_sick_leaves;
366 l_counter := l_counter + 1;
367 --
368 xml_tab (l_counter).tagname := 'MEN_U_AGE_L_SICK_LEAVES_C';
369 xml_tab (l_counter).tagvalue := l_csr_get_curr_report_data.men_u_age_l_sick_leaves;
370 l_counter := l_counter + 1;
371 --
372 --
373 xml_tab (l_counter).tagname := 'WOMEN_L_AGE_L_SICK_LEAVES_C';
374 xml_tab (l_counter).tagvalue := l_csr_get_curr_report_data.women_l_age_l_sick_leaves;
375 l_counter := l_counter + 1;
376 --
377 --
378 xml_tab (l_counter).tagname := 'WOMEN_M_AGE_L_SICK_LEAVES_C';
379 xml_tab (l_counter).tagvalue := l_csr_get_curr_report_data.women_m_age_l_sick_leaves;
380 l_counter := l_counter + 1;
381 --
382 --
383 xml_tab (l_counter).tagname := 'WOMEN_U_AGE_L_SICK_LEAVES_C';
384 xml_tab (l_counter).tagvalue := l_csr_get_curr_report_data.women_u_age_l_sick_leaves;
385 l_counter := l_counter + 1;
386 --
387
388 xml_tab (l_counter).tagname := 'TOT_L_SICK_LEAVES_C';
389 xml_tab (l_counter).tagvalue := l_csr_get_curr_report_data.men_l_age_l_sick_leaves
393 + l_csr_get_curr_report_data.women_m_age_l_sick_leaves
390 + l_csr_get_curr_report_data.men_m_age_l_sick_leaves
391 + l_csr_get_curr_report_data.men_u_age_l_sick_leaves
392 + l_csr_get_curr_report_data.women_l_age_l_sick_leaves
394 + l_csr_get_curr_report_data.women_u_age_l_sick_leaves;
395 l_counter := l_counter + 1;
396 /* Get The Report data for the current year */
397 OPEN csr_get_report_data (
398 csr_v_pa_id => l_payroll_action_id,
399 p_curr_prev_data => 'LE_PREV_YR_DATA',
400 p_leagal_employer_id => i.legal_employer_id
401 );
402 FETCH csr_get_report_data INTO l_csr_get_prev_report_data;
403 CLOSE csr_get_report_data;
404 xml_tab (l_counter).tagname := 'MEN_LOWER_AGE_COUNT_P';
405 xml_tab (l_counter).tagvalue := l_csr_get_prev_report_data.men_lower_age_count;
406 l_counter := l_counter + 1;
407 --
408
409 xml_tab (l_counter).tagname := 'MEN_MIDDLE_AGE_COUNT_P';
410 xml_tab (l_counter).tagvalue := l_csr_get_prev_report_data.men_middle_age_count;
411 l_counter := l_counter + 1;
412 --
413 xml_tab (l_counter).tagname := 'MEN_UPPER_AGE_COUNT_P';
414 xml_tab (l_counter).tagvalue := l_csr_get_prev_report_data.men_upper_age_count;
415 l_counter := l_counter + 1;
416 --
417 --
418 xml_tab (l_counter).tagname := 'WOMEN_LOWER_AGE_COUNT_P';
419 xml_tab (l_counter).tagvalue := l_csr_get_prev_report_data.women_lower_age_count;
420 l_counter := l_counter + 1;
421 --
422 --
423 xml_tab (l_counter).tagname := 'WOMEN_MIDDLE_AGE_COUNT_P';
424 xml_tab (l_counter).tagvalue := l_csr_get_prev_report_data.women_middle_age_count;
425 l_counter := l_counter + 1;
426 --
427 --
428 xml_tab (l_counter).tagname := 'WOMEN_UPPER_AGE_COUNT_P';
429 xml_tab (l_counter).tagvalue := l_csr_get_prev_report_data.women_upper_age_count;
430 l_counter := l_counter + 1;
431 --
432 xml_tab (l_counter).tagname := 'TOT_EMP_COUNT_P';
433 xml_tab (l_counter).tagvalue := l_csr_get_prev_report_data.men_lower_age_count
434 + l_csr_get_prev_report_data.men_middle_age_count
435 + l_csr_get_prev_report_data.men_upper_age_count
436 + l_csr_get_prev_report_data.women_lower_age_count
437 + l_csr_get_prev_report_data.women_middle_age_count
438 + l_csr_get_prev_report_data.women_upper_age_count;
439 l_counter := l_counter + 1;
440 xml_tab (l_counter).tagname := 'MEN_LOWER_AGE_HOUR_P';
441 xml_tab (l_counter).tagvalue := l_csr_get_prev_report_data.men_lower_age_work_hour;
442 l_counter := l_counter + 1;
443 --
444
445 xml_tab (l_counter).tagname := 'MEN_MIDDLE_AGE_HOUR_P';
446 xml_tab (l_counter).tagvalue := l_csr_get_prev_report_data.men_middle_age_work_hour;
447 l_counter := l_counter + 1;
448 --
449 xml_tab (l_counter).tagname := 'MEN_UPPER_AGE_HOUR_P';
450 xml_tab (l_counter).tagvalue := l_csr_get_prev_report_data.men_upper_age_work_hour;
451 l_counter := l_counter + 1;
452 --
453
454 xml_tab (l_counter).tagname := 'WOMEN_LOWER_AGE_HOUR_P';
455 xml_tab (l_counter).tagvalue := l_csr_get_prev_report_data.women_lower_age_work_hour;
456 l_counter := l_counter + 1;
457 --
458
459 xml_tab (l_counter).tagname := 'WOMEN_MIDDLE_AGE_HOUR_P';
460 xml_tab (l_counter).tagvalue := l_csr_get_prev_report_data.women_middle_age_work_hour;
461 l_counter := l_counter + 1;
462 --
463
464 xml_tab (l_counter).tagname := 'WOMEN_UPPER_AGE_HOUR_P';
465 xml_tab (l_counter).tagvalue := l_csr_get_prev_report_data.women_upper_age_work_hour;
466 l_counter := l_counter + 1;
467 --
468 xml_tab (l_counter).tagname := 'TOT_WORK_HOUR_P';
469 xml_tab (l_counter).tagvalue := l_csr_get_prev_report_data.men_lower_age_work_hour
470 + l_csr_get_prev_report_data.men_middle_age_work_hour
471 + l_csr_get_prev_report_data.men_upper_age_work_hour
472 + l_csr_get_prev_report_data.women_lower_age_work_hour
473 + l_csr_get_prev_report_data.women_middle_age_work_hour
474 + l_csr_get_prev_report_data.women_upper_age_work_hour;
475 l_counter := l_counter + 1;
476 xml_tab (l_counter).tagname := 'MEN_L_AGE_SICK_LEAVES_P';
477 xml_tab (l_counter).tagvalue := l_csr_get_prev_report_data.men_l_age_sick_leaves;
478 l_counter := l_counter + 1;
479 --
480 xml_tab (l_counter).tagname := 'MEN_M_AGE_SICK_LEAVES_P';
481 xml_tab (l_counter).tagvalue := l_csr_get_prev_report_data.men_m_age_sick_leaves;
482 l_counter := l_counter + 1;
483 --
484 xml_tab (l_counter).tagname := 'MEN_U_AGE_SICK_LEAVES_P';
485 xml_tab (l_counter).tagvalue := l_csr_get_prev_report_data.men_u_age_sick_leaves;
486 l_counter := l_counter + 1;
487 --
488 --
489 xml_tab (l_counter).tagname := 'WOMEN_L_AGE_SICK_LEAVES_P';
490 xml_tab (l_counter).tagvalue := l_csr_get_prev_report_data.women_l_age_sick_leaves;
491 l_counter := l_counter + 1;
492 --
493 --
497 --
494 xml_tab (l_counter).tagname := 'WOMEN_M_AGE_SICK_LEAVES_P';
495 xml_tab (l_counter).tagvalue := l_csr_get_prev_report_data.women_m_age_sick_leaves;
496 l_counter := l_counter + 1;
498 --
499 xml_tab (l_counter).tagname := 'WOMEN_U_AGE_SICK_LEAVES_P';
500 xml_tab (l_counter).tagvalue := l_csr_get_prev_report_data.women_u_age_l_sick_leaves;
501 l_counter := l_counter + 1;
502 --
503
504 --
505 xml_tab (l_counter).tagname := 'TOT_SICK_LEAVES_P';
506 xml_tab (l_counter).tagvalue := l_csr_get_prev_report_data.men_l_age_sick_leaves
507 + l_csr_get_prev_report_data.men_m_age_sick_leaves
508 + l_csr_get_prev_report_data.men_u_age_sick_leaves
509 + l_csr_get_prev_report_data.women_l_age_sick_leaves
510 + l_csr_get_prev_report_data.women_m_age_sick_leaves
511 + l_csr_get_prev_report_data.women_u_age_sick_leaves;
512 l_counter := l_counter + 1;
513 --
514 xml_tab (l_counter).tagname := 'MEN_L_AGE_L_SICK_LEAVES_P';
515 xml_tab (l_counter).tagvalue := l_csr_get_prev_report_data.men_l_age_l_sick_leaves;
516 l_counter := l_counter + 1;
517 --
518 xml_tab (l_counter).tagname := 'MEN_M_AGE_L_SICK_LEAVES_P';
519 xml_tab (l_counter).tagvalue := l_csr_get_prev_report_data.men_m_age_l_sick_leaves;
520 l_counter := l_counter + 1;
521 --
522 xml_tab (l_counter).tagname := 'MEN_U_AGE_L_SICK_LEAVES_P';
523 xml_tab (l_counter).tagvalue := l_csr_get_prev_report_data.men_u_age_l_sick_leaves;
524 l_counter := l_counter + 1;
525 --
526 --
527 xml_tab (l_counter).tagname := 'WOMEN_L_AGE_L_SICK_LEAVES_P';
528 xml_tab (l_counter).tagvalue := l_csr_get_prev_report_data.women_l_age_l_sick_leaves;
529 l_counter := l_counter + 1;
530 --
531 --
532 xml_tab (l_counter).tagname := 'WOMEN_M_AGE_L_SICK_LEAVES_P';
533 xml_tab (l_counter).tagvalue := l_csr_get_prev_report_data.women_m_age_l_sick_leaves;
534 l_counter := l_counter + 1;
535 --
536 --
537 xml_tab (l_counter).tagname := 'WOMEN_U_AGE_L_SICK_LEAVES_P';
538 xml_tab (l_counter).tagvalue := l_csr_get_prev_report_data.women_u_age_l_sick_leaves;
539 l_counter := l_counter + 1;
540 --
541
542 xml_tab (l_counter).tagname := 'TOT_L_SICK_LEAVES_P';
543 xml_tab (l_counter).tagvalue := l_csr_get_prev_report_data.men_l_age_l_sick_leaves
544 + l_csr_get_prev_report_data.men_m_age_l_sick_leaves
545 + l_csr_get_prev_report_data.men_u_age_l_sick_leaves
546 + l_csr_get_prev_report_data.women_l_age_l_sick_leaves
547 + l_csr_get_prev_report_data.women_m_age_l_sick_leaves
548 + l_csr_get_prev_report_data.women_u_age_l_sick_leaves;
549 l_counter := l_counter + 1;
550 /* Get The Report statistics for the current year */
551 OPEN csr_get_report_stat (
552 csr_v_pa_id => l_payroll_action_id,
553 p_curr_prev_stat => 'LE_CURR_YR_STAT', --'LE_CURR_YR_STAT',
554 p_leagal_employer_id => i.legal_employer_id
555 );
556 FETCH csr_get_report_stat INTO l_csr_get_curr_report_stat;
557 CLOSE csr_get_report_stat;
558 xml_tab (l_counter).tagname := 'LEAVE_TO_WORK_HOURS_C';
559 xml_tab (l_counter).tagvalue := l_csr_get_curr_report_stat.leave_to_work_hours;
560 l_counter := l_counter + 1;
561 --
562
566 --
563 xml_tab (l_counter).tagname := 'LONG_LEAVE_PART_C';
564 xml_tab (l_counter).tagvalue := l_csr_get_curr_report_stat.long_leave_part;
565 l_counter := l_counter + 1;
567 xml_tab (l_counter).tagname := 'MEN_LEAVE_TO_WORK_HOUR_C';
568 xml_tab (l_counter).tagvalue := l_csr_get_curr_report_stat.men_leave_to_work_hour;
569 l_counter := l_counter + 1;
570 --
571 --
572 xml_tab (l_counter).tagname := 'WOMEN_LEAVE_TO_WORK_HOUR_C';
573 xml_tab (l_counter).tagvalue := l_csr_get_curr_report_stat.women_leave_to_work_hour;
574 l_counter := l_counter + 1;
575 --
576 --
577 xml_tab (l_counter).tagname := 'LEAVE_TO_WORK_HOUR_LOWER_C';
578 xml_tab (l_counter).tagvalue := l_csr_get_curr_report_stat.leave_to_work_hour_lower;
579 l_counter := l_counter + 1;
580 --
581 --
582 xml_tab (l_counter).tagname := 'LEAVE_TO_WORK_HOUR_MIDDLE_C';
583 xml_tab (l_counter).tagvalue := l_csr_get_curr_report_stat.leave_to_work_hour_middle;
584 l_counter := l_counter + 1;
585 --
586
587
588 xml_tab (l_counter).tagname := 'LEAVE_TO_WORK_HOUR_UPPER_C';
589 xml_tab (l_counter).tagvalue := l_csr_get_curr_report_stat.leave_to_work_hour_upper;
590 l_counter := l_counter + 1;
591 --
592 /* Get The Report data for the current year */
593 OPEN csr_get_report_stat (
594 csr_v_pa_id => l_payroll_action_id,
595 p_curr_prev_stat => 'LE_PREV_YR_STAT',
596 p_leagal_employer_id => i.legal_employer_id
597 );
598 FETCH csr_get_report_stat INTO l_csr_get_prev_report_stat;
599 CLOSE csr_get_report_stat;
600 xml_tab (l_counter).tagname := 'LEAVE_TO_WORK_HOURS_P';
601 xml_tab (l_counter).tagvalue := l_csr_get_prev_report_stat.leave_to_work_hours;
602 l_counter := l_counter + 1;
603 --
604 xml_tab (l_counter).tagname := 'LONG_LEAVE_PART_P';
605 xml_tab (l_counter).tagvalue := l_csr_get_prev_report_stat.long_leave_part;
606 l_counter := l_counter + 1;
607 --
608 xml_tab (l_counter).tagname := 'MEN_LEAVE_TO_WORK_HOUR_P';
609 xml_tab (l_counter).tagvalue := l_csr_get_prev_report_stat.men_leave_to_work_hour;
610 l_counter := l_counter + 1;
611 --
612 --
613 xml_tab (l_counter).tagname := 'WOMEN_LEAVE_TO_WORK_HOUR_P';
614 xml_tab (l_counter).tagvalue := l_csr_get_prev_report_stat.women_leave_to_work_hour;
615 l_counter := l_counter + 1;
616 --
617 --
618 xml_tab (l_counter).tagname := 'LEAVE_TO_WORK_HOUR_LOWER_P';
619 xml_tab (l_counter).tagvalue := l_csr_get_prev_report_stat.leave_to_work_hour_lower;
620 l_counter := l_counter + 1;
621 --
622 --
623 xml_tab (l_counter).tagname := 'LEAVE_TO_WORK_HOUR_MIDDLE_P';
624 xml_tab (l_counter).tagvalue := l_csr_get_prev_report_stat.leave_to_work_hour_middle;
625 l_counter := l_counter + 1;
626 --
627
628 xml_tab (l_counter).tagname := 'LEAVE_TO_WORK_HOUR_UPPER_P';
629 xml_tab (l_counter).tagvalue := l_csr_get_prev_report_stat.leave_to_work_hour_upper;
630 l_counter := l_counter + 1;
631 --
632
633 END LOOP;
634
635 writetoclob (p_xml);
636 --
637 END get_data;
638
639 -----------------------------------------------------------------------------------------------------------------
640 PROCEDURE writetoclob (
641 p_xfdf_clob OUT NOCOPY CLOB
642 ) IS
643 l_xfdf_string CLOB;
644 l_iana_charset VARCHAR2 (30);
645 current_index PLS_INTEGER;
646 l_str1 VARCHAR2 (1000);
647 l_str2 VARCHAR2 (20);
648 l_str3 VARCHAR2 (20);
649 l_str4 VARCHAR2 (20);
650 l_str5 VARCHAR2 (20);
651 l_str6 VARCHAR2 (30);
652 l_str7 VARCHAR2 (1000);
653 l_str8 VARCHAR2 (240);
654 l_str9 VARCHAR2 (240);
655 l_str10 VARCHAR2 (20);
656 l_str11 VARCHAR2 (20);
657 l_str12 VARCHAR2 (30);
658 l_str13 VARCHAR2 (30);
659 l_str14 VARCHAR2 (30);
660 l_str15 VARCHAR2 (30);
661 BEGIN
662 l_iana_charset := hr_se_utility.get_iana_charset;
663 l_str1 := '<?xml version="1.0" encoding="' || l_iana_charset || '"?> <ROOT><PAACR>';
664 l_str2 := '<';
665 l_str3 := '>';
666 l_str4 := '</';
667 l_str5 := '>';
668 l_str6 := '</PAACR></ROOT>';
669 l_str7 := '<?xml version="1.0" encoding="' || l_iana_charset || '"?> <ROOT></ROOT>';
670 l_str10 := '<PAACR>';
671 l_str11 := '</PAACR>';
672 l_str12 := '<FILE_HEADER_START>';
673 l_str13 := '</FILE_HEADER_START>';
674 l_str14 := '<LE_RECORD>';
675 l_str15 := '</LE_RECORD>';
676 dbms_lob.createtemporary (l_xfdf_string, FALSE , dbms_lob.CALL);
677 dbms_lob.OPEN (l_xfdf_string, dbms_lob.lob_readwrite);
678 current_index := 0;
679
680 IF xml_tab.count > 0 THEN
681 dbms_lob.writeappend (l_xfdf_string, LENGTH (l_str1), l_str1);
682 dbms_lob.writeappend (l_xfdf_string, LENGTH (l_str12), l_str12);
683
684 FOR table_counter IN xml_tab.FIRST .. xml_tab.LAST
685 LOOP
686 l_str8 := xml_tab (table_counter).tagname;
687 l_str9 := xml_tab (table_counter).tagvalue;
688
689 IF l_str8 = 'ORG_NAME' THEN
690 dbms_lob.writeappend (l_xfdf_string, LENGTH (l_str14), l_str14);
691 END IF;
692
693 IF l_str9 IS NOT NULL THEN
694 l_str9 := '<![CDATA[' || l_str9 || ']]>';
695 dbms_lob.writeappend (l_xfdf_string, LENGTH (l_str2), l_str2);
696 dbms_lob.writeappend (l_xfdf_string, LENGTH (l_str8), l_str8);
697 dbms_lob.writeappend (l_xfdf_string, LENGTH (l_str3), l_str3);
698 dbms_lob.writeappend (l_xfdf_string, LENGTH (l_str9), l_str9);
699 dbms_lob.writeappend (l_xfdf_string, LENGTH (l_str4), l_str4);
700 dbms_lob.writeappend (l_xfdf_string, LENGTH (l_str8), l_str8);
701 dbms_lob.writeappend (l_xfdf_string, LENGTH (l_str5), l_str5);
702 ELSE
703 dbms_lob.writeappend (l_xfdf_string, LENGTH (l_str2), l_str2);
704 dbms_lob.writeappend (l_xfdf_string, LENGTH (l_str8), l_str8);
705 dbms_lob.writeappend (l_xfdf_string, LENGTH (l_str3), l_str3);
706 dbms_lob.writeappend (l_xfdf_string, LENGTH (l_str4), l_str4);
707 dbms_lob.writeappend (l_xfdf_string, LENGTH (l_str8), l_str8);
708 dbms_lob.writeappend (l_xfdf_string, LENGTH (l_str5), l_str5);
709 END IF;
710
711 IF xml_tab.LAST = table_counter THEN
712 dbms_lob.writeappend (l_xfdf_string, LENGTH (l_str15), l_str15);
713 ELSIF xml_tab (table_counter + 1).tagname = 'ORG_NAME' AND l_str8 <> 'REPORT_DATE' THEN
714 dbms_lob.writeappend (l_xfdf_string, LENGTH (l_str15), l_str15);
715 END IF;
716 END LOOP;
717
718 dbms_lob.writeappend (l_xfdf_string, LENGTH (l_str13), l_str13);
719 dbms_lob.writeappend (l_xfdf_string, LENGTH (l_str6), l_str6);
720 ELSE
721 dbms_lob.writeappend (l_xfdf_string, LENGTH (l_str7), l_str7);
722 END IF;
723
724 p_xfdf_clob := l_xfdf_string;
725 hr_utility.set_location ('Leaving WritetoCLOB ', 20);
726 END writetoclob;
727 -------------------------------------------------------------------------------------------------------------------------
728
729 END pay_se_slsr;