[Home] [Help]
PACKAGE BODY: APPS.PAY_NO_ABS_STATISTICS_REPORT
Source
1 PACKAGE BODY PAY_NO_ABS_STATISTICS_REPORT AS
2 /* $Header: pynoabsr.pkb 120.0.12000000.1 2007/05/22 05:47:46 rajesrin noship $ */
3 absmale abstab;
4 absfemale abstab;
5 abstotal abstab;
6 FUNCTION get_archive_payroll_action_id (p_payroll_action_id IN NUMBER)
7 RETURN NUMBER
8 IS
9 l_payroll_action_id NUMBER;
10 BEGIN
11 IF p_payroll_action_id IS NULL
12 THEN
13 BEGIN
14 SELECT payroll_action_id
15 INTO l_payroll_action_id
16 FROM pay_payroll_actions ppa,
17 fnd_conc_req_summary_v fcrs,
18 fnd_conc_req_summary_v fcrs1
19 WHERE fcrs.request_id = fnd_global.conc_request_id
20 AND fcrs.priority_request_id = fcrs1.priority_request_id
21 AND ppa.request_id BETWEEN fcrs1.request_id
22 AND fcrs.request_id
23 AND ppa.request_id = fcrs1.request_id;
24 EXCEPTION
25 WHEN OTHERS
26 THEN
27 NULL;
28 END;
29 ELSE
30 l_payroll_action_id := p_payroll_action_id;
31 END IF;
32 RETURN l_payroll_action_id;
33 END;
34 PROCEDURE get_data (
35 p_business_group_id in varchar2,
36 p_payroll_action_id IN VARCHAR2,
37 p_template_name IN VARCHAR2,
38 p_xml OUT NOCOPY CLOB
39 )
40 IS
41 /* Start of declaration*/
42 -- Variables needed for the report
43 l_sum NUMBER;
44 l_counter NUMBER := 1;
45 l_payroll_action_id pay_action_information.action_information1%TYPE;
46 l_legal_employer_id number;
47 l_legal_employer HR_ORGANIZATION_UNITS.name%type;
48 l_quater VARCHAR2(3);
49 l_quater_flag VARCHAR2(2):='Y';
50 l_quater_count NUMBER:=0;
51 l_actual_working_days NUMBER;
52 l_absent_days NUMBER;
53 l_sickness_percentage NUMBER;
54 l_effective_date date;
55
56 CURSOR csr_legal_employer is select o.name,fnd_date.canonical_to_date(pai.action_information5) action_information5
57 from
58 HR_ORGANIZATION_UNITS o,
59 HR_ORGANIZATION_INFORMATION hoi1,
60 pay_action_information pai
61 where pai.action_context_id = l_payroll_action_id
62 and pai.action_information_category ='EMEA REPORT DETAILS'
63 and o.organization_id = pai.action_information2
64 and hoi1.organization_id = o.organization_id
65 and hoi1.org_information_context = 'CLASS'
66 and hoi1.org_information1 = 'HR_LEGAL_EMPLOYER';
67
68 CURSOR csr_abs_stat_data (csr_v_pa_id IN VARCHAR2)
69 IS
70 select pai.action_information1 report_type,
71 pai.action_information3 quater,
72 pai.action_information4 possible_working_days,
73 pai.action_information5 sex,
74 pai.action_information6 occur_1_3_days_sc,
75 pai.action_information7 sc_1_3_abs_days,
76 pai.action_information8 dc_1_3_days_Occur,
77 pai.action_information9 dc_1_3_abs_days,
78 pai.action_information10 sick_4_16_days_Occur,
79 pai.action_information11 sick_4_16_abs_days,
80 pai.action_information12 More_Than_16days_Occur,
81 pai.action_information13 More_Than_16days_Abs,
82 pai.action_information14 Child_Minders_Ocrs,
83 pai.action_information15 Child_Minders_Days,
84 pai.action_information16 Paternal_Leave_ocrs,
85 pai.action_information17 Paternal_leave_days,
86 pai.action_information18 other_leave_ocrs,
87 pai.action_information19 other_leave_days,
88 pai.action_information20 sick_more_8w_ocrs,
89 pai.action_information21 sick_more_8w_days,
90 pai.action_information22 other_abs_paid_ocrs,
91 pai.action_information23 other_abs_paid_days,
92 pai.action_information24 business_group_id
93 from pay_action_information pai
94 where pai.action_context_id = csr_v_pa_id
95 and action_information_category ='EMEA REPORT INFORMATION'
96 and action_information1 = 'PYNOABSA'
97 order by pai.action_information3;
98
99 BEGIN
100
101 l_payroll_action_id := get_archive_payroll_action_id (p_payroll_action_id);
102
103 open csr_legal_employer;
104 fetch csr_legal_employer into l_legal_employer,l_effective_date;
105 close csr_legal_employer;
106
107 absmale(0).initialized:='N';
108 absfemale(0).initialized:='N';
109 abstotal(0).initialized:='N';
110 abstotal(1).initialized:='N';
111 abstotal(2).initialized:='N';
112 abstotal(3).initialized:='N';
113 abstotal(4).initialized:='N';
114
118 gplsqltable (l_counter).tagvalue := to_char(l_effective_date,'DD.Mon.YYYY');
115 gplsqltable (0).tagname := 'LEGAL_EMPLOYER';
116 gplsqltable (0).tagvalue := l_legal_employer;
117 gplsqltable (l_counter).tagname := 'EFFECTIVE_DATE';
119 l_counter := l_counter+ 1;
120 FOR csr_abs_stat_datas IN csr_abs_stat_data (l_payroll_action_id)
121 LOOP
122 IF l_quater_flag = 'Y' THEN
123 l_quater := csr_abs_stat_datas.quater;
124 l_quater_flag := 'N';
125 END IF;
126 IF l_quater <> csr_abs_stat_datas.quater THEN
127
128 l_actual_working_days := nvl(abstotal(l_quater_count).possible_working_days,0)
129 - nvl(abstotal(l_quater_count).sick_1_3_days_sc,0)
130 - nvl(abstotal(l_quater_count).sick_1_3_days_dc,0)
131 - nvl(abstotal(l_quater_count).sick_4_16_days,0)
132 - nvl(abstotal(l_quater_count).sick_more_16_days,0)
133 - nvl(abstotal(l_quater_count).cms_abs_days,0)
134 - nvl(abstotal(l_quater_count).parental_abs_days,0)
135 - nvl(abstotal(l_quater_count).other_abs_days,0)
136 - nvl(abstotal(l_quater_count).other_abs_paid_days,0);
137
138 l_absent_days := nvl(abstotal(l_quater_count).sick_1_3_days_sc,0)
139 + nvl(abstotal(l_quater_count).sick_1_3_days_dc,0)
140 + nvl(abstotal(l_quater_count).sick_4_16_days,0)
141 + nvl(abstotal(l_quater_count).sick_more_16_days,0);
142
143 If nvl(abstotal(l_quater_count).possible_working_days,0) <> 0 then
144 l_sickness_percentage := (l_absent_days /abstotal(l_quater_count).possible_working_days)*100;
145 end if;
146
147 gplsqltable (l_counter).tagname := 'QUARTER';
148 gplsqltable (l_counter).tagvalue := l_quater;
149 l_counter := l_counter+ 1;
150
151 gplsqltable (l_counter).tagname := 'START';
152 gplsqltable (l_counter).tagvalue:= 'START';
153 l_counter := l_counter+ 1;
154 gplsqltable (l_counter).tagname := 'Q_SEX';
155 gplsqltable (l_counter).tagvalue:= abstotal(l_quater_count).quatertag||' '||l_quater;
156 l_counter := l_counter+ 1;
157
158 l_quater := csr_abs_stat_datas.quater;
159
160 gplsqltable (l_counter).tagname := 'Q_POSSIBLE_WORKING_DAYS';
161 gplsqltable (l_counter).tagvalue:= round(abstotal(l_quater_count).possible_working_days);
162 l_counter := l_counter + 1;
163 gplsqltable (l_counter).tagname := 'Q_ACTUAL_WORKING_DAYS';
164 gplsqltable (l_counter).tagvalue:= round(l_actual_working_days);
165 l_counter := l_counter + 1;
166 gplsqltable (l_counter).tagname := 'Q_OCCUR_1_3_DAYS_SC';
167 gplsqltable (l_counter).tagvalue := abstotal(l_quater_count).sick_1_3_ocr_sc;
168 l_counter := l_counter + 1;
169 gplsqltable (l_counter).tagname := 'Q_SC_1_3_ABS_DAYS';
170 gplsqltable (l_counter).tagvalue := round(abstotal(l_quater_count).sick_1_3_days_sc);
171 l_counter := l_counter + 1;
172 gplsqltable (l_counter).tagname := 'Q_DC_1_3_DAYS_OCCUR';
173 gplsqltable (l_counter).tagvalue:= abstotal(l_quater_count).sick_1_3_ocr_dc;
174 l_counter := l_counter + 1;
175 gplsqltable (l_counter).tagname := 'Q_DC_1_3_ABS_DAYS';
176 gplsqltable (l_counter).tagvalue := round(abstotal(l_quater_count).sick_1_3_days_dc);
177 l_counter := l_counter + 1;
178 gplsqltable (l_counter).tagname := 'Q_SICK_4_16_DAYS_OCCUR';
179 gplsqltable (l_counter).tagvalue:= abstotal(l_quater_count).sick_4_16_ocrs;
180 l_counter := l_counter+ 1;
181 gplsqltable (l_counter).tagname := 'Q_SICK_4_16_ABS_DAYS';
182 gplsqltable (l_counter).tagvalue:= round(abstotal(l_quater_count).sick_4_16_days);
183 l_counter := l_counter + 1;
184 gplsqltable (l_counter).tagname := 'Q_MORE_THAN_16DAYS_OCCUR';
185 gplsqltable (l_counter).tagvalue := abstotal(l_quater_count).sick_more_16_ocrs;
186 l_counter := l_counter + 1;
187 gplsqltable (l_counter).tagname := 'Q_MORE_THAN_16DAYS_ABS';
188 gplsqltable (l_counter).tagvalue := round(abstotal(l_quater_count).sick_more_16_days);
189 l_counter := l_counter + 1;
190
191 gplsqltable (l_counter).tagname := 'Q_MORE_THAN_8W_OCCUR';
192 gplsqltable (l_counter).tagvalue := abstotal(l_quater_count).sick_more_8w_ocrs;
193 l_counter := l_counter + 1;
194
195 gplsqltable (l_counter).tagname := 'Q_MORE_THAN_8W_DAYS';
196 gplsqltable (l_counter).tagvalue := round(abstotal(l_quater_count).sick_more_8w_days);
197 l_counter := l_counter + 1;
198
199 gplsqltable (l_counter).tagname := 'Q_CHILD_MINDERS_OCRS';
200 gplsqltable (l_counter).tagvalue := abstotal(l_quater_count).cms_abs_ocrs;
201 l_counter := l_counter + 1;
202 gplsqltable (l_counter).tagname := 'Q_CHILD_MINDERS_DAYS';
203 gplsqltable (l_counter).tagvalue := round(abstotal(l_quater_count).cms_abs_days);
204 l_counter := l_counter + 1;
205 gplsqltable (l_counter).tagname := 'Q_PATERNAL_LEAVE_OCRS';
206 gplsqltable (l_counter).tagvalue := abstotal(l_quater_count).parental_abs_ocrs;
207 l_counter := l_counter + 1;
208 gplsqltable (l_counter).tagname := 'Q_PATERNAL_LEAVE_DAYS';
209 gplsqltable (l_counter).tagvalue := round(abstotal(l_quater_count).parental_abs_days);
210 l_counter := l_counter + 1;
211 gplsqltable (l_counter).tagname := 'Q_OTHER_LEAVE_OCRS';
212 gplsqltable (l_counter).tagvalue := abstotal(l_quater_count).other_abs_ocrs;
213 l_counter := l_counter + 1;
214 gplsqltable (l_counter).tagname := 'Q_OTHER_LEAVE_DAYS';
215 gplsqltable (l_counter).tagvalue := round(abstotal(l_quater_count).other_abs_days);
216 l_counter := l_counter + 1;
217
218 gplsqltable (l_counter).tagname := 'Q_OTHER_PAID_LEAVE_OCRS';
219 gplsqltable (l_counter).tagvalue := abstotal(l_quater_count).other_abs_paid_ocrs;
220 l_counter := l_counter + 1;
221 gplsqltable (l_counter).tagname := 'Q_OTHER_PAID_LEAVE_DAYS';
225 gplsqltable (l_counter).tagname := 'Q_SICK_PERCENTAGE';
222 gplsqltable (l_counter).tagvalue := round(abstotal(l_quater_count).other_abs_paid_days);
223 l_counter := l_counter + 1;
224
226 gplsqltable (l_counter).tagvalue := round(l_sickness_percentage,1);
227 l_counter := l_counter + 1;
228
229 gplsqltable (l_counter).tagname := 'END';
230 gplsqltable (l_counter).tagvalue := 'END';
231 l_counter := l_counter + 1;
232 END IF;
233 l_quater_count := to_number(substr(csr_abs_stat_datas.quater,2))-1;
234
235 l_actual_working_days := nvl(csr_abs_stat_datas.possible_working_days,0)
236 - nvl(csr_abs_stat_datas.sc_1_3_abs_days,0)
237 - nvl(csr_abs_stat_datas.dc_1_3_abs_days,0)
238 - nvl(csr_abs_stat_datas.sick_4_16_Abs_Days,0)
239 - nvl(csr_abs_stat_datas.More_Than_16days_Abs,0)
240 - nvl(csr_abs_stat_datas.Child_Minders_Days,0)
241 - nvl(csr_abs_stat_datas.Paternal_leave_days,0)
242 - nvl(csr_abs_stat_datas.other_leave_days,0)
243 - nvl(csr_abs_stat_datas.other_abs_paid_days,0);
244
245
246 l_absent_days := nvl(csr_abs_stat_datas.sc_1_3_abs_days,0)
247 + nvl(csr_abs_stat_datas.dc_1_3_abs_days,0)
248 + nvl(csr_abs_stat_datas.sick_4_16_Abs_Days,0)
249 + nvl(csr_abs_stat_datas.More_Than_16days_Abs,0);
250
251 If nvl(csr_abs_stat_datas.possible_working_days,0) <> 0 then
252 l_sickness_percentage := (l_absent_days /nvl(csr_abs_stat_datas.possible_working_days,0))*100;
253 End if;
254
255
256 abstotal(4).initialized:='Y';
257 abstotal(4).quatertag:= hr_general.decode_lookup('NO_FORM_LABELS','ASR04');
258 gplsqltable (l_counter).tagname := 'QUARTER';
259 gplsqltable (l_counter).tagvalue := csr_abs_stat_datas.quater;
260 l_counter := l_counter+ 1;
261 gplsqltable (l_counter).tagname := 'START';
262 gplsqltable (l_counter).tagvalue := 'START';
263 l_counter := l_counter+ 1;
264 gplsqltable (l_counter).tagname := csr_abs_stat_datas.sex||'_SEX';
265 gplsqltable (l_counter).tagvalue := hr_general.decode_lookup('SEX',csr_abs_stat_datas.sex);
266 l_counter := l_counter+ 1;
267 gplsqltable (l_counter).tagname := csr_abs_stat_datas.sex||'_POSSIBLE_WORKING_DAYS';
268 gplsqltable (l_counter).tagvalue := csr_abs_stat_datas.possible_working_days;
269 abstotal(4).possible_working_days:=nvl(abstotal(4).possible_working_days,0)+nvl(csr_abs_stat_datas.possible_working_days,0);
270 l_counter := l_counter + 1;
271
272 gplsqltable (l_counter).tagname := csr_abs_stat_datas.sex||'_ACTUAL_WORKING_DAYS';
273 gplsqltable (l_counter).tagvalue := l_actual_working_days;
274 l_counter := l_counter + 1;
275
276 gplsqltable (l_counter).tagname := csr_abs_stat_datas.sex||'_OCCUR_1_3_DAYS_SC';
277 gplsqltable (l_counter).tagvalue:= csr_abs_stat_datas.occur_1_3_days_sc;
278 abstotal(4).sick_1_3_ocr_sc :=nvl(abstotal(4).sick_1_3_ocr_sc,0)+nvl(csr_abs_stat_datas.occur_1_3_days_sc,0);
279 l_counter := l_counter + 1;
280 gplsqltable (l_counter).tagname := csr_abs_stat_datas.sex||'_SC_1_3_ABS_DAYS';
281 gplsqltable (l_counter).tagvalue:= csr_abs_stat_datas.sc_1_3_abs_days;
282 abstotal(4).sick_1_3_days_sc := nvl(abstotal(4).sick_1_3_days_sc,0)+nvl(csr_abs_stat_datas.sc_1_3_abs_days,0);
283 l_counter := l_counter + 1;
284 gplsqltable (l_counter).tagname := csr_abs_stat_datas.sex||'_DC_1_3_DAYS_OCCUR';
285 gplsqltable (l_counter).tagvalue:=csr_abs_stat_datas.dc_1_3_days_Occur;
286 abstotal(4).sick_1_3_ocr_dc :=nvl(abstotal(4).sick_1_3_ocr_dc,0)+nvl(csr_abs_stat_datas.dc_1_3_days_Occur,0);
287 l_counter := l_counter + 1;
288 gplsqltable (l_counter).tagname := csr_abs_stat_datas.sex||'_DC_1_3_ABS_DAYS';
289 gplsqltable (l_counter).tagvalue:= csr_abs_stat_datas.dc_1_3_abs_days;
290 abstotal(4).sick_1_3_days_dc :=nvl(abstotal(4).sick_1_3_days_dc,0)+nvl(csr_abs_stat_datas.dc_1_3_abs_days,0);
291 l_counter := l_counter + 1;
292 gplsqltable (l_counter).tagname := csr_abs_stat_datas.sex||'_SICK_4_16_DAYS_OCCUR';
293 gplsqltable (l_counter).tagvalue:=csr_abs_stat_datas.sick_4_16_days_Occur;
294 abstotal(4).sick_4_16_ocrs :=nvl(abstotal(4).sick_4_16_ocrs,0)+nvl(csr_abs_stat_datas.sick_4_16_days_Occur,0);
295 l_counter := l_counter+ 1;
296 gplsqltable (l_counter).tagname := csr_abs_stat_datas.sex||'_SICK_4_16_ABS_DAYS';
297 gplsqltable (l_counter).tagvalue:=csr_abs_stat_datas.sick_4_16_abs_days;
298 abstotal(4).sick_4_16_days :=nvl(abstotal(4).sick_4_16_days,0)+nvl(csr_abs_stat_datas.sick_4_16_Abs_Days,0);
299 l_counter := l_counter + 1;
300
301 gplsqltable (l_counter).tagname := csr_abs_stat_datas.sex||'_MORE_THAN_16DAYS_OCCUR';
302 gplsqltable (l_counter).tagvalue:= csr_abs_stat_datas.More_Than_16days_Occur;
303 abstotal(4).sick_more_16_ocrs :=nvl(abstotal(4).sick_more_16_ocrs,0)+nvl(csr_abs_stat_datas.More_Than_16days_Occur,0);
304 l_counter := l_counter + 1;
305 gplsqltable (l_counter).tagname := csr_abs_stat_datas.sex||'_MORE_THAN_16DAYS_ABS';
306 gplsqltable (l_counter).tagvalue:= csr_abs_stat_datas.More_Than_16days_Abs;
307 abstotal(4).sick_more_16_days :=nvl(abstotal(4).sick_more_16_days,0)+nvl(csr_abs_stat_datas.More_Than_16days_Abs,0);
308 l_counter := l_counter + 1;
309
310 gplsqltable (l_counter).tagname := csr_abs_stat_datas.sex||'_MORE_THAN_8W_OCCUR';
311 gplsqltable (l_counter).tagvalue:= csr_abs_stat_datas.sick_more_8w_ocrs;
312 abstotal(4).sick_more_8w_ocrs :=nvl(abstotal(4).sick_more_8w_ocrs,0)+nvl(csr_abs_stat_datas.sick_more_8w_ocrs,0);
313 l_counter := l_counter + 1;
314
315 gplsqltable (l_counter).tagname := csr_abs_stat_datas.sex||'_MORE_THAN_8W_DAYS';
319
316 gplsqltable (l_counter).tagvalue:= csr_abs_stat_datas.sick_more_8w_days;
317 abstotal(4).sick_more_8w_days :=nvl(abstotal(4).sick_more_8w_days,0)+nvl(csr_abs_stat_datas.sick_more_8w_days,0);
318 l_counter := l_counter + 1;
320
321 gplsqltable (l_counter).tagname := csr_abs_stat_datas.sex||'_CHILD_MINDERS_OCRS';
322 gplsqltable (l_counter).tagvalue:= csr_abs_stat_datas.Child_Minders_Ocrs;
323 abstotal(4).cms_abs_ocrs :=nvl(abstotal(4).cms_abs_ocrs,0)+nvl(csr_abs_stat_datas.Child_Minders_Ocrs,0);
324 l_counter := l_counter + 1;
325 gplsqltable (l_counter).tagname := csr_abs_stat_datas.sex||'_CHILD_MINDERS_DAYS';
326 gplsqltable (l_counter).tagvalue:= csr_abs_stat_datas.Child_Minders_Days;
327 abstotal(4).cms_abs_days :=nvl(abstotal(4).cms_abs_days,0)+nvl(csr_abs_stat_datas.Child_Minders_Days,0);
328 l_counter := l_counter + 1;
329 gplsqltable (l_counter).tagname := csr_abs_stat_datas.sex||'_PATERNAL_LEAVE_OCRS';
330 gplsqltable (l_counter).tagvalue:= csr_abs_stat_datas.Paternal_Leave_ocrs;
331 abstotal(4).parental_abs_ocrs :=nvl(abstotal(4).parental_abs_ocrs,0)+nvl(csr_abs_stat_datas.Paternal_Leave_ocrs,0);
332 l_counter := l_counter + 1;
333 gplsqltable (l_counter).tagname := csr_abs_stat_datas.sex||'_PATERNAL_LEAVE_DAYS';
334 gplsqltable (l_counter).tagvalue:= csr_abs_stat_datas.Paternal_leave_days;
335 abstotal(4).parental_abs_days :=nvl(abstotal(4).parental_abs_days,0)+nvl(csr_abs_stat_datas.Paternal_leave_days,0);
336 l_counter := l_counter + 1;
337 gplsqltable (l_counter).tagname := csr_abs_stat_datas.sex||'_OTHER_LEAVE_OCRS';
338 gplsqltable (l_counter).tagvalue:= csr_abs_stat_datas.other_leave_ocrs;
339 abstotal(4).other_abs_ocrs :=nvl(abstotal(4).other_abs_ocrs,0)+nvl(csr_abs_stat_datas.other_leave_ocrs,0);
340 l_counter := l_counter + 1;
341 gplsqltable (l_counter).tagname := csr_abs_stat_datas.sex||'_OTHER_LEAVE_DAYS';
342 gplsqltable (l_counter).tagvalue:= csr_abs_stat_datas.other_leave_days;
343 abstotal(4).other_abs_days :=nvl(abstotal(4).other_abs_days,0)+nvl(csr_abs_stat_datas.other_leave_days,0);
344 l_counter := l_counter + 1;
345
346 gplsqltable (l_counter).tagname := csr_abs_stat_datas.sex||'_OTHER_PAID_LEAVE_OCRS';
347 gplsqltable (l_counter).tagvalue:= csr_abs_stat_datas.other_abs_paid_ocrs;
348 abstotal(4).other_abs_paid_ocrs :=nvl(abstotal(4).other_abs_paid_ocrs,0)+nvl(csr_abs_stat_datas.other_abs_paid_ocrs,0);
349 l_counter := l_counter + 1;
350 gplsqltable (l_counter).tagname := csr_abs_stat_datas.sex||'_OTHER_PAID_LEAVE_DAYS';
351 gplsqltable (l_counter).tagvalue:= csr_abs_stat_datas.other_abs_paid_days;
352 abstotal(4).other_abs_paid_days :=nvl(abstotal(4).other_abs_paid_days,0)+nvl(csr_abs_stat_datas.other_abs_paid_days,0);
353 l_counter := l_counter + 1;
354
355 gplsqltable (l_counter).tagname := csr_abs_stat_datas.sex||'_SICK_PERCENTAGE';
356 gplsqltable (l_counter).tagvalue := round(l_sickness_percentage,1);
357 l_counter := l_counter + 1;
358
359 gplsqltable (l_counter).tagname := 'END';
360 gplsqltable (l_counter).tagvalue := 'END';
361 l_counter := l_counter + 1;
362 IF csr_abs_stat_datas.sex = 'M' THEN
363 absmale(0).initialized:='Y';
364 absmale(0).quatertag:=hr_general.decode_lookup('NO_FORM_LABELS','ASR02');
365 absmale(0).possible_working_days:= nvl(absmale(0).possible_working_days,0)+nvl(csr_abs_stat_datas.possible_working_days,0);
366 absmale(0).sick_1_3_ocr_sc :=nvl(absmale(0).sick_1_3_ocr_sc,0)+nvl(csr_abs_stat_datas.occur_1_3_days_sc,0);
367 absmale(0).sick_1_3_days_sc :=nvl(absmale(0).sick_1_3_days_sc,0)+nvl(csr_abs_stat_datas.sc_1_3_abs_days,0);
368 absmale(0).sick_1_3_ocr_dc :=nvl(absmale(0).sick_1_3_ocr_dc,0)+nvl(csr_abs_stat_datas.dc_1_3_days_Occur,0);
369 absmale(0).sick_1_3_days_dc :=nvl(absmale(0).sick_1_3_days_dc,0)+nvl(csr_abs_stat_datas.dc_1_3_abs_days,0);
370 absmale(0).sick_4_16_ocrs :=nvl(absmale(0).sick_4_16_ocrs,0)+nvl(csr_abs_stat_datas.sick_4_16_days_Occur,0);
371 absmale(0).sick_4_16_days :=nvl(absmale(0).sick_4_16_days,0)+nvl(csr_abs_stat_datas.sick_4_16_Abs_Days,0);
372 absmale(0).sick_more_16_ocrs :=nvl(absmale(0).sick_more_16_ocrs,0)+nvl(csr_abs_stat_datas.More_Than_16days_Occur,0);
373 absmale(0).sick_more_16_days :=nvl(absmale(0).sick_more_16_days,0)+nvl(csr_abs_stat_datas.More_Than_16days_Abs,0);
374 absmale(0).cms_abs_ocrs :=nvl(absmale(0).cms_abs_ocrs,0)+nvl(csr_abs_stat_datas.Child_Minders_Ocrs,0);
375 absmale(0).cms_abs_days :=nvl(absmale(0).cms_abs_days,0)+nvl(csr_abs_stat_datas.Child_Minders_Days,0);
376 absmale(0).parental_abs_ocrs :=nvl(absmale(0).parental_abs_ocrs,0)+nvl(csr_abs_stat_datas.Paternal_Leave_ocrs,0);
377 absmale(0).parental_abs_days :=nvl(absmale(0).parental_abs_days,0)+nvl(csr_abs_stat_datas.Paternal_leave_days,0);
378 absmale(0).other_abs_ocrs :=nvl(absmale(0).other_abs_ocrs,0)+nvl(csr_abs_stat_datas.other_leave_ocrs,0);
379 absmale(0).other_abs_days :=nvl(absmale(0).other_abs_days,0)+nvl(csr_abs_stat_datas.other_leave_days,0);
380 absmale(0).sick_more_8w_ocrs :=nvl(absmale(0).sick_more_8w_ocrs,0)+nvl(csr_abs_stat_datas.sick_more_8w_ocrs,0);
381 absmale(0).sick_more_8w_days :=nvl(absmale(0).sick_more_8w_days,0)+nvl(csr_abs_stat_datas.sick_more_8w_days,0);
382 absmale(0).other_abs_paid_ocrs:=nvl(absmale(0).other_abs_paid_ocrs,0)+nvl(csr_abs_stat_datas.other_abs_paid_ocrs,0);
383 absmale(0).other_abs_paid_days:=nvl(absmale(0).other_abs_paid_days,0)+nvl(csr_abs_stat_datas.other_abs_paid_days,0);
384 ELSIF csr_abs_stat_datas.sex = 'F' THEN
385 absfemale(0).initialized:='Y';
386 absfemale(0).quatertag:=hr_general.decode_lookup('NO_FORM_LABELS','ASR03');
387 absfemale(0).possible_working_days :=nvl(absfemale(0).possible_working_days,0)+nvl(csr_abs_stat_datas.possible_working_days,0);
388 absfemale(0).sick_1_3_ocr_sc :=nvl(absfemale(0).sick_1_3_ocr_sc,0)+nvl(csr_abs_stat_datas.occur_1_3_days_sc,0);
389 absfemale(0).sick_1_3_days_sc :=nvl(absfemale(0).sick_1_3_days_sc,0)+nvl(csr_abs_stat_datas.sc_1_3_abs_days,0);
390 absfemale(0).sick_1_3_ocr_dc :=nvl(absfemale(0).sick_1_3_ocr_dc,0)+nvl(csr_abs_stat_datas.dc_1_3_days_Occur,0);
394 absfemale(0).sick_more_16_ocrs :=nvl(absfemale(0).sick_more_16_ocrs,0)+nvl(csr_abs_stat_datas.More_Than_16days_Occur,0);
391 absfemale(0).sick_1_3_days_dc :=nvl(absfemale(0).sick_1_3_days_dc,0)+nvl(csr_abs_stat_datas.dc_1_3_abs_days,0);
392 absfemale(0).sick_4_16_ocrs :=nvl(absfemale(0).sick_4_16_ocrs,0)+nvl(csr_abs_stat_datas.sick_4_16_days_Occur,0);
393 absfemale(0).sick_4_16_days :=nvl(absfemale(0).sick_4_16_days,0)+nvl(csr_abs_stat_datas.sick_4_16_Abs_Days,0);
395 absfemale(0).sick_more_16_days :=nvl(absfemale(0).sick_more_16_days,0)+nvl(csr_abs_stat_datas.More_Than_16days_Abs,0);
396 absfemale(0).cms_abs_ocrs :=nvl(absfemale(0).cms_abs_ocrs,0)+nvl(csr_abs_stat_datas.Child_Minders_Ocrs,0);
397 absfemale(0).cms_abs_days :=nvl(absfemale(0).cms_abs_days,0)+nvl(csr_abs_stat_datas.Child_Minders_Days,0);
398 absfemale(0).parental_abs_ocrs :=nvl(absfemale(0).parental_abs_ocrs,0)+nvl(csr_abs_stat_datas.Paternal_Leave_ocrs,0);
399 absfemale(0).parental_abs_days :=nvl(absfemale(0).parental_abs_days,0)+nvl(csr_abs_stat_datas.Paternal_leave_days,0);
400 absfemale(0).other_abs_ocrs :=nvl(absfemale(0).other_abs_ocrs,0)+nvl(csr_abs_stat_datas.other_leave_ocrs,0);
401 absfemale(0).other_abs_days :=nvl(absfemale(0).other_abs_days,0)+nvl(csr_abs_stat_datas.other_leave_days,0);
402 absfemale(0).sick_more_8w_ocrs :=nvl(absfemale(0).sick_more_8w_ocrs,0)+nvl(csr_abs_stat_datas.sick_more_8w_ocrs,0);
403 absfemale(0).sick_more_8w_days :=nvl(absfemale(0).sick_more_8w_days,0)+nvl(csr_abs_stat_datas.sick_more_8w_days,0);
404 absfemale(0).other_abs_paid_ocrs :=nvl(absfemale(0).other_abs_paid_ocrs,0)+nvl(csr_abs_stat_datas.other_abs_paid_ocrs,0);
405 absfemale(0).other_abs_paid_days :=nvl(absfemale(0).other_abs_paid_days,0)+nvl(csr_abs_stat_datas.other_abs_paid_days,0);
406 END IF;
407
408 abstotal(l_quater_count).initialized:='Y';
409 abstotal(l_quater_count).quatertag:=hr_general.decode_lookup('NO_FORM_LABELS','ASR01');
410 abstotal(l_quater_count).possible_working_days :=nvl(abstotal(l_quater_count).possible_working_days,0)+nvl(csr_abs_stat_datas.possible_working_days,0);
411 abstotal(l_quater_count).sick_1_3_ocr_sc :=nvl(abstotal(l_quater_count).sick_1_3_ocr_sc,0)+nvl(csr_abs_stat_datas.occur_1_3_days_sc,0);
412 abstotal(l_quater_count).sick_1_3_days_sc :=nvl(abstotal(l_quater_count).sick_1_3_days_sc,0)+nvl(csr_abs_stat_datas.sc_1_3_abs_days,0);
413 abstotal(l_quater_count).sick_1_3_ocr_dc :=nvl(abstotal(l_quater_count).sick_1_3_ocr_dc,0)+nvl(csr_abs_stat_datas.dc_1_3_days_Occur,0);
414 abstotal(l_quater_count).sick_1_3_days_dc :=nvl(abstotal(l_quater_count).sick_1_3_days_dc,0)+nvl(csr_abs_stat_datas.dc_1_3_abs_days,0);
415 abstotal(l_quater_count).sick_4_16_ocrs :=nvl(abstotal(l_quater_count).sick_4_16_ocrs,0)+nvl(csr_abs_stat_datas.sick_4_16_days_Occur,0);
416 abstotal(l_quater_count).sick_4_16_days :=nvl(abstotal(l_quater_count).sick_4_16_days,0)+nvl(csr_abs_stat_datas.sick_4_16_Abs_Days,0);
417 abstotal(l_quater_count).sick_more_16_ocrs :=nvl(abstotal(l_quater_count).sick_more_16_ocrs,0)+nvl(csr_abs_stat_datas.More_Than_16days_Occur,0);
418 abstotal(l_quater_count).sick_more_16_days :=nvl(abstotal(l_quater_count).sick_more_16_days,0)+nvl(csr_abs_stat_datas.More_Than_16days_Abs,0);
419 abstotal(l_quater_count).cms_abs_ocrs :=nvl(abstotal(l_quater_count).cms_abs_ocrs,0)+nvl(csr_abs_stat_datas.Child_Minders_Ocrs,0);
420 abstotal(l_quater_count).cms_abs_days :=nvl(abstotal(l_quater_count).cms_abs_days,0)+nvl(csr_abs_stat_datas.Child_Minders_Days,0);
424 abstotal(l_quater_count).other_abs_days :=nvl(abstotal(l_quater_count).other_abs_days,0)+nvl(csr_abs_stat_datas.other_leave_days,0);
421 abstotal(l_quater_count).parental_abs_ocrs :=nvl(abstotal(l_quater_count).parental_abs_ocrs,0)+nvl(csr_abs_stat_datas.Paternal_Leave_ocrs,0);
422 abstotal(l_quater_count).parental_abs_days :=nvl(abstotal(l_quater_count).parental_abs_days,0)+nvl(csr_abs_stat_datas.Paternal_leave_days,0);
423 abstotal(l_quater_count).other_abs_ocrs :=nvl(abstotal(l_quater_count).other_abs_ocrs,0)+nvl(csr_abs_stat_datas.other_leave_ocrs,0);
425 abstotal(l_quater_count).sick_more_8w_ocrs :=nvl(abstotal(l_quater_count).sick_more_8w_ocrs,0)+nvl(csr_abs_stat_datas.sick_more_8w_ocrs,0);
426 abstotal(l_quater_count).sick_more_8w_days :=nvl(abstotal(l_quater_count).sick_more_8w_days,0)+nvl(csr_abs_stat_datas.sick_more_8w_days,0);
427 abstotal(l_quater_count).other_abs_paid_ocrs :=nvl(abstotal(l_quater_count).other_abs_paid_ocrs,0)+nvl(csr_abs_stat_datas.other_abs_paid_ocrs,0);
428 abstotal(l_quater_count).other_abs_paid_days :=nvl(abstotal(l_quater_count).other_abs_paid_days,0)+nvl(csr_abs_stat_datas.other_abs_paid_days,0);
429 END LOOP;
430
431 /* Start of Last Quater Total */
432
433 l_actual_working_days := nvl(abstotal(l_quater_count).possible_working_days,0)
434 - nvl(abstotal(l_quater_count).sick_1_3_days_sc,0)
435 - nvl(abstotal(l_quater_count).sick_1_3_days_dc,0)
436 - nvl(abstotal(l_quater_count).sick_4_16_days,0)
437 - nvl(abstotal(l_quater_count).sick_more_16_days,0)
438 - nvl(abstotal(l_quater_count).cms_abs_days,0)
439 - nvl(abstotal(l_quater_count).parental_abs_days,0)
440 - nvl(abstotal(l_quater_count).other_abs_days,0)
441 - nvl(abstotal(l_quater_count).other_abs_paid_days,0);
442
443 l_absent_days := nvl(abstotal(l_quater_count).sick_1_3_days_sc,0)
444 + nvl(abstotal(l_quater_count).sick_1_3_days_dc,0)
445 + nvl(abstotal(l_quater_count).sick_4_16_days,0)
446 + nvl(abstotal(l_quater_count).sick_more_16_days,0);
447
448 If nvl(abstotal(l_quater_count).possible_working_days,0) <> 0 then
449 l_sickness_percentage := (l_absent_days /abstotal(l_quater_count).possible_working_days)*100;
450 End if;
451
452
453 gplsqltable (l_counter).tagname := 'QUARTER';
454 gplsqltable (l_counter).tagvalue := l_quater;
455 l_counter := l_counter+ 1;
456
457 gplsqltable (l_counter).tagname := 'START';
458 gplsqltable (l_counter).tagvalue:= 'START';
459 l_counter := l_counter+ 1;
460 gplsqltable (l_counter).tagname := 'Q_SEX';
461 gplsqltable (l_counter).tagvalue:= abstotal(l_quater_count).quatertag||' '||l_quater;
462 l_counter := l_counter+ 1;
463 gplsqltable (l_counter).tagname := 'Q_POSSIBLE_WORKING_DAYS';
464 gplsqltable (l_counter).tagvalue:= round(abstotal(l_quater_count).possible_working_days);
465 l_counter := l_counter + 1;
466 gplsqltable (l_counter).tagname := 'Q_ACTUAL_WORKING_DAYS';
467 gplsqltable (l_counter).tagvalue:= round(l_actual_working_days);
468 l_counter := l_counter + 1;
469 gplsqltable (l_counter).tagname := 'Q_OCCUR_1_3_DAYS_SC';
470 gplsqltable (l_counter).tagvalue := abstotal(l_quater_count).sick_1_3_ocr_sc;
471 l_counter := l_counter + 1;
472 gplsqltable (l_counter).tagname := 'Q_SC_1_3_ABS_DAYS';
473 gplsqltable (l_counter).tagvalue := round(abstotal(l_quater_count).sick_1_3_days_sc);
474 l_counter := l_counter + 1;
475 gplsqltable (l_counter).tagname := 'Q_DC_1_3_DAYS_OCCUR';
476 gplsqltable (l_counter).tagvalue:= abstotal(l_quater_count).sick_1_3_ocr_dc;
477 l_counter := l_counter + 1;
478 gplsqltable (l_counter).tagname := 'Q_DC_1_3_ABS_DAYS';
479 gplsqltable (l_counter).tagvalue := round(abstotal(l_quater_count).sick_1_3_days_dc);
480 l_counter := l_counter + 1;
481 gplsqltable (l_counter).tagname := 'Q_SICK_4_16_DAYS_OCCUR';
482 gplsqltable (l_counter).tagvalue:= abstotal(l_quater_count).sick_4_16_ocrs;
483 l_counter := l_counter+ 1;
484 gplsqltable (l_counter).tagname := 'Q_SICK_4_16_ABS_DAYS';
485 gplsqltable (l_counter).tagvalue:= round(abstotal(l_quater_count).sick_4_16_days);
486 l_counter := l_counter + 1;
487 gplsqltable (l_counter).tagname := 'Q_MORE_THAN_16DAYS_OCCUR';
488 gplsqltable (l_counter).tagvalue := abstotal(l_quater_count).sick_more_16_ocrs;
489 l_counter := l_counter + 1;
490 gplsqltable (l_counter).tagname := 'Q_MORE_THAN_16DAYS_ABS';
491 gplsqltable (l_counter).tagvalue := round(abstotal(l_quater_count).sick_more_16_days);
492 l_counter := l_counter + 1;
493
494 gplsqltable (l_counter).tagname := 'Q_MORE_THAN_8W_OCCUR';
495 gplsqltable (l_counter).tagvalue := abstotal(l_quater_count).sick_more_8w_ocrs;
496 l_counter := l_counter + 1;
497
498 gplsqltable (l_counter).tagname := 'Q_MORE_THAN_8W_DAYS';
499 gplsqltable (l_counter).tagvalue := round(abstotal(l_quater_count).sick_more_8w_days);
500 l_counter := l_counter + 1;
501
502 gplsqltable (l_counter).tagname := 'Q_CHILD_MINDERS_OCRS';
503 gplsqltable (l_counter).tagvalue := abstotal(l_quater_count).cms_abs_ocrs;
504 l_counter := l_counter + 1;
505 gplsqltable (l_counter).tagname := 'Q_CHILD_MINDERS_DAYS';
506 gplsqltable (l_counter).tagvalue := round(abstotal(l_quater_count).cms_abs_days);
507 l_counter := l_counter + 1;
508 gplsqltable (l_counter).tagname := 'Q_PATERNAL_LEAVE_OCRS';
512 gplsqltable (l_counter).tagvalue := round(abstotal(l_quater_count).parental_abs_days);
509 gplsqltable (l_counter).tagvalue := abstotal(l_quater_count).parental_abs_ocrs;
510 l_counter := l_counter + 1;
511 gplsqltable (l_counter).tagname := 'Q_PATERNAL_LEAVE_DAYS';
513 l_counter := l_counter + 1;
514 gplsqltable (l_counter).tagname := 'Q_OTHER_LEAVE_OCRS';
515 gplsqltable (l_counter).tagvalue := abstotal(l_quater_count).other_abs_ocrs;
516 l_counter := l_counter + 1;
517 gplsqltable (l_counter).tagname := 'Q_OTHER_LEAVE_DAYS';
518 gplsqltable (l_counter).tagvalue := round(abstotal(l_quater_count).other_abs_days);
519 l_counter := l_counter + 1;
520
521 gplsqltable (l_counter).tagname := 'Q_OTHER_PAID_LEAVE_OCRS';
522 gplsqltable (l_counter).tagvalue := abstotal(l_quater_count).other_abs_paid_ocrs;
523 l_counter := l_counter + 1;
524 gplsqltable (l_counter).tagname := 'Q_OTHER_PAID_LEAVE_DAYS';
525 gplsqltable (l_counter).tagvalue := round(abstotal(l_quater_count).other_abs_paid_days);
526 l_counter := l_counter + 1;
527
528 gplsqltable (l_counter).tagname := 'Q_SICK_PERCENTAGE';
529 gplsqltable (l_counter).tagvalue := round(l_sickness_percentage,1);
530 l_counter := l_counter + 1;
531
532 gplsqltable (l_counter).tagname := 'END';
533 gplsqltable (l_counter).tagvalue := 'END';
534 l_counter := l_counter + 1;
535 /* End of Last Quater Total*/
536 /* Start of Male Total */
537
538 l_actual_working_days := nvl(absmale(0).possible_working_days,0)
539 - nvl(absmale(0).sick_1_3_days_sc,0)
540 - nvl(absmale(0).sick_1_3_days_dc,0)
541 - nvl(absmale(0).sick_4_16_days,0)
542 - nvl(absmale(0).sick_more_16_days,0)
543 - nvl(absmale(0).cms_abs_days,0)
544 - nvl(absmale(0).parental_abs_days,0)
545 - nvl(absmale(0).other_abs_days,0)
546 - nvl(absmale(0).other_abs_paid_days,0);
547
548 l_absent_days := nvl(absmale(0).sick_1_3_days_sc,0)
549 + nvl(absmale(0).sick_1_3_days_dc,0)
550 + nvl(absmale(0).sick_4_16_days,0)
551 + nvl(absmale(0).sick_more_16_days,0);
552
553 If nvl(absmale(0).possible_working_days,0) <> 0 then
554 l_sickness_percentage := (l_absent_days /absmale(0).possible_working_days)*100;
555 End if;
556
557
558 gplsqltable (l_counter).tagname := 'QUARTER';
559 gplsqltable (l_counter).tagvalue := 'Total';
560 l_counter := l_counter+ 1;
561 gplsqltable (l_counter).tagname := 'START';
562 gplsqltable (l_counter).tagvalue:= 'START';
563 l_counter := l_counter+ 1;
564 gplsqltable (l_counter).tagname := 'M_SEX';
565 gplsqltable (l_counter).tagvalue:= absmale(0).quatertag;
566 l_counter := l_counter+ 1;
567 gplsqltable (l_counter).tagname := 'M_POSSIBLE_WORKING_DAYS';
568 gplsqltable (l_counter).tagvalue:= round(absmale(0).possible_working_days);
569 l_counter := l_counter + 1;
570
571 gplsqltable (l_counter).tagname := 'M_ACTUAL_WORKING_DAYS';
572 gplsqltable (l_counter).tagvalue:= round(l_actual_working_days);
573 l_counter := l_counter + 1;
574
575 gplsqltable (l_counter).tagname := 'M_OCCUR_1_3_DAYS_SC';
576 gplsqltable (l_counter).tagvalue := absmale(0).sick_1_3_ocr_sc;
577 l_counter := l_counter + 1;
578 gplsqltable (l_counter).tagname := 'M_SC_1_3_ABS_DAYS';
579 gplsqltable (l_counter).tagvalue := round(absmale(0).sick_1_3_days_sc);
580 l_counter := l_counter + 1;
581 gplsqltable (l_counter).tagname := 'M_DC_1_3_DAYS_OCCUR';
582 gplsqltable (l_counter).tagvalue:= absmale(0).sick_1_3_ocr_dc;
583 l_counter := l_counter + 1;
584 gplsqltable (l_counter).tagname := 'M_DC_1_3_ABS_DAYS';
585 gplsqltable (l_counter).tagvalue := round(absmale(0).sick_1_3_days_dc);
586 l_counter := l_counter + 1;
587 gplsqltable (l_counter).tagname := 'M_SICK_4_16_DAYS_OCCUR';
588 gplsqltable (l_counter).tagvalue:= absmale(0).sick_4_16_ocrs;
589 l_counter := l_counter+ 1;
590 gplsqltable (l_counter).tagname := 'M_SICK_4_16_ABS_DAYS';
591 gplsqltable (l_counter).tagvalue:= round(absmale(0).sick_4_16_days);
592 l_counter := l_counter + 1;
593 gplsqltable (l_counter).tagname := 'M_MORE_THAN_16DAYS_OCCUR';
594 gplsqltable (l_counter).tagvalue := absmale(0).sick_more_16_ocrs;
595 l_counter := l_counter + 1;
596 gplsqltable (l_counter).tagname := 'M_MORE_THAN_16DAYS_ABS';
597 gplsqltable (l_counter).tagvalue := round(absmale(0).sick_more_16_days);
598 l_counter := l_counter + 1;
599
600 gplsqltable (l_counter).tagname := 'M_MORE_THAN_8W_OCCUR';
601 gplsqltable (l_counter).tagvalue := absmale(0).sick_more_8w_ocrs;
602 l_counter := l_counter + 1;
603 gplsqltable (l_counter).tagname := 'M_MORE_THAN_8W_DAYS';
604 gplsqltable (l_counter).tagvalue := round(absmale(0).sick_more_8w_days);
605 l_counter := l_counter + 1;
606
607 gplsqltable (l_counter).tagname := 'M_CHILD_MINDERS_OCRS';
608 gplsqltable (l_counter).tagvalue := absmale(0).cms_abs_ocrs;
609 l_counter := l_counter + 1;
610 gplsqltable (l_counter).tagname := 'M_CHILD_MINDERS_DAYS';
611 gplsqltable (l_counter).tagvalue := round(absmale(0).cms_abs_days);
612 l_counter := l_counter + 1;
613 gplsqltable (l_counter).tagname := 'M_PATERNAL_LEAVE_OCRS';
614 gplsqltable (l_counter).tagvalue := absmale(0).parental_abs_ocrs;
615 l_counter := l_counter + 1;
616 gplsqltable (l_counter).tagname := 'M_PATERNAL_LEAVE_DAYS';
617 gplsqltable (l_counter).tagvalue := round(absmale(0).parental_abs_days);
618 l_counter := l_counter + 1;
619 gplsqltable (l_counter).tagname := 'M_OTHER_LEAVE_OCRS';
620 gplsqltable (l_counter).tagvalue := absmale(0).other_abs_ocrs;
624 l_counter := l_counter + 1;
621 l_counter := l_counter + 1;
622 gplsqltable (l_counter).tagname := 'M_OTHER_LEAVE_DAYS';
623 gplsqltable (l_counter).tagvalue := round(absmale(0).other_abs_days);
625
626 gplsqltable (l_counter).tagname := 'M_OTHER_PAID_LEAVE_OCRS';
627 gplsqltable (l_counter).tagvalue := absmale(0).other_abs_paid_ocrs;
628 l_counter := l_counter + 1;
629 gplsqltable (l_counter).tagname := 'M_OTHER_PAID_LEAVE_DAYS';
630 gplsqltable (l_counter).tagvalue := round(absmale(0).other_abs_paid_days);
631 l_counter := l_counter + 1;
632
633 gplsqltable (l_counter).tagname := 'M_SICK_PERCENTAGE';
634 gplsqltable (l_counter).tagvalue := round(l_sickness_percentage,1);
635 l_counter := l_counter + 1;
636
637 gplsqltable (l_counter).tagname := 'END';
638 gplsqltable (l_counter).tagvalue := 'END';
639 l_counter := l_counter + 1;
640 /* End of Male Total */
641 /* Start of Female Total */
642
643 l_actual_working_days := nvl(absfemale(0).possible_working_days,0)
644 - nvl(absfemale(0).sick_1_3_days_sc,0)
645 - nvl(absfemale(0).sick_1_3_days_dc,0)
646 - nvl(absfemale(0).sick_4_16_days,0)
647 - nvl(absfemale(0).sick_more_16_days,0)
648 - nvl(absfemale(0).cms_abs_days,0)
649 - nvl(absfemale(0).parental_abs_days,0)
650 - nvl(absfemale(0).other_abs_days,0)
651 - nvl(absfemale(0).other_abs_paid_days,0);
652
653 l_absent_days := nvl(absfemale(0).sick_1_3_days_sc,0)
654 + nvl(absfemale(0).sick_1_3_days_dc,0)
655 + nvl(absfemale(0).sick_4_16_days,0)
656 + nvl(absfemale(0).sick_more_16_days,0);
657
658 If nvl(absfemale(0).possible_working_days,0) <> 0 then
659 l_sickness_percentage := (l_absent_days /absfemale(0).possible_working_days)*100;
660 end if;
661
662 gplsqltable (l_counter).tagname := 'START';
663 gplsqltable (l_counter).tagvalue:= 'START';
664 l_counter := l_counter+ 1;
665 gplsqltable (l_counter).tagname := 'F_SEX';
666 gplsqltable (l_counter).tagvalue:= absfemale(0).quatertag;
667 l_counter := l_counter+ 1;
668 gplsqltable (l_counter).tagname := 'F_POSSIBLE_WORKING_DAYS';
669 gplsqltable (l_counter).tagvalue:= round(absfemale(0).possible_working_days);
670 l_counter := l_counter + 1;
671 gplsqltable (l_counter).tagname := 'F_ACTUAL_WORKING_DAYS';
672 gplsqltable (l_counter).tagvalue:= round(l_actual_working_days);
673 l_counter := l_counter + 1;
674 gplsqltable (l_counter).tagname := 'F_OCCUR_1_3_DAYS_SC';
675 gplsqltable (l_counter).tagvalue := absfemale(0).sick_1_3_ocr_sc;
676 l_counter := l_counter + 1;
677 gplsqltable (l_counter).tagname := 'F_SC_1_3_ABS_DAYS';
678 gplsqltable (l_counter).tagvalue := round(absfemale(0).sick_1_3_days_sc);
679 l_counter := l_counter + 1;
680 gplsqltable (l_counter).tagname := 'F_DC_1_3_DAYS_OCCUR';
681 gplsqltable (l_counter).tagvalue:= absfemale(0).sick_1_3_ocr_dc;
682 l_counter := l_counter + 1;
683 gplsqltable (l_counter).tagname := 'F_DC_1_3_ABS_DAYS';
684 gplsqltable (l_counter).tagvalue := round(absfemale(0).sick_1_3_days_dc);
685 l_counter := l_counter + 1;
686 gplsqltable (l_counter).tagname := 'F_SICK_4_16_DAYS_OCCUR';
687 gplsqltable (l_counter).tagvalue:= absfemale(0).sick_4_16_ocrs;
688 l_counter := l_counter+ 1;
689 gplsqltable (l_counter).tagname := 'F_SICK_4_16_ABS_DAYS';
690 gplsqltable (l_counter).tagvalue:= round(absfemale(0).sick_4_16_days);
691 l_counter := l_counter + 1;
692 gplsqltable (l_counter).tagname := 'F_MORE_THAN_16DAYS_OCCUR';
693 gplsqltable (l_counter).tagvalue := absfemale(0).sick_more_16_ocrs;
694 l_counter := l_counter + 1;
695 gplsqltable (l_counter).tagname := 'F_MORE_THAN_16DAYS_ABS';
696 gplsqltable (l_counter).tagvalue := round(absfemale(0).sick_more_16_days);
697 l_counter := l_counter + 1;
698 gplsqltable (l_counter).tagname := 'F_MORE_THAN_8W_OCCUR';
699 gplsqltable (l_counter).tagvalue := absfemale(0).sick_more_8w_ocrs;
700 l_counter := l_counter + 1;
701 gplsqltable (l_counter).tagname := 'F_MORE_THAN_8W_DAYS';
702 gplsqltable (l_counter).tagvalue := round(absfemale(0).sick_more_8w_days);
703 l_counter := l_counter + 1;
704 gplsqltable (l_counter).tagname := 'F_CHILD_MINDERS_OCRS';
705 gplsqltable (l_counter).tagvalue := absfemale(0).cms_abs_ocrs;
706 l_counter := l_counter + 1;
707 gplsqltable (l_counter).tagname := 'F_CHILD_MINDERS_DAYS';
708 gplsqltable (l_counter).tagvalue := round(absfemale(0).cms_abs_days);
709 l_counter := l_counter + 1;
710 gplsqltable (l_counter).tagname := 'F_PATERNAL_LEAVE_OCRS';
711 gplsqltable (l_counter).tagvalue := absfemale(0).parental_abs_ocrs;
712 l_counter := l_counter + 1;
713 gplsqltable (l_counter).tagname := 'F_PATERNAL_LEAVE_DAYS';
714 gplsqltable (l_counter).tagvalue := round(absfemale(0).parental_abs_days);
715 l_counter := l_counter + 1;
716 gplsqltable (l_counter).tagname := 'F_OTHER_LEAVE_OCRS';
717 gplsqltable (l_counter).tagvalue := absfemale(0).other_abs_ocrs;
718 l_counter := l_counter + 1;
719 gplsqltable (l_counter).tagname := 'F_OTHER_LEAVE_DAYS';
720 gplsqltable (l_counter).tagvalue := round(absfemale(0).other_abs_days);
721 l_counter := l_counter + 1;
722
723 gplsqltable (l_counter).tagname := 'F_OTHER_PAID_LEAVE_OCRS';
724 gplsqltable (l_counter).tagvalue := absfemale(0).other_abs_paid_ocrs;
725 l_counter := l_counter + 1;
726 gplsqltable (l_counter).tagname := 'F_OTHER_PAID_LEAVE_DAYS';
727 gplsqltable (l_counter).tagvalue := round(absfemale(0).other_abs_paid_days);
728 l_counter := l_counter + 1;
729
730 gplsqltable (l_counter).tagname := 'F_SICK_PERCENTAGE';
731 gplsqltable (l_counter).tagvalue := round(l_sickness_percentage,1);
732 l_counter := l_counter + 1;
733
737 /* End of Female Total */
734 gplsqltable (l_counter).tagname := 'END';
735 gplsqltable (l_counter).tagvalue := 'END';
736 l_counter := l_counter + 1;
738 /* Start of Yearly Total */
739
740 l_actual_working_days := nvl(abstotal(4).possible_working_days,0)
741 - nvl(abstotal(4).sick_1_3_days_sc,0)
742 - nvl(abstotal(4).sick_1_3_days_dc,0)
743 - nvl(abstotal(4).sick_4_16_days,0)
744 - nvl(abstotal(4).sick_more_16_days,0)
745 - nvl(abstotal(4).cms_abs_days,0)
746 - nvl(abstotal(4).parental_abs_days,0)
747 - nvl(abstotal(4).other_abs_days,0)
748 - nvl(abstotal(4).other_abs_paid_days,0);
749
750 l_absent_days := nvl(abstotal(4).sick_1_3_days_sc,0)
751 + nvl(abstotal(4).sick_1_3_days_dc,0)
752 + nvl(abstotal(4).sick_4_16_days,0)
753 + nvl(abstotal(4).sick_more_16_days,0);
754
755 If nvl(abstotal(4).possible_working_days,0) <> 0 then
756 l_sickness_percentage := (l_absent_days /abstotal(4).possible_working_days)*100;
757 end if;
758
759 gplsqltable (l_counter).tagname := 'START';
760 gplsqltable (l_counter).tagvalue:= 'START';
761 l_counter := l_counter+ 1;
762 gplsqltable (l_counter).tagname := 'Q_SEX';
763 gplsqltable (l_counter).tagvalue:= abstotal(4).quatertag;
764 l_counter := l_counter+ 1;
765 gplsqltable (l_counter).tagname := 'Q_POSSIBLE_WORKING_DAYS';
766 gplsqltable (l_counter).tagvalue:= round(abstotal(4).possible_working_days);
767 l_counter := l_counter + 1;
768 gplsqltable (l_counter).tagname := 'Q_ACTUAL_WORKING_DAYS';
769 gplsqltable (l_counter).tagvalue:= round(l_actual_working_days);
770 l_counter := l_counter + 1;
771 gplsqltable (l_counter).tagname := 'Q_OCCUR_1_3_DAYS_SC';
772 gplsqltable (l_counter).tagvalue := abstotal(4).sick_1_3_ocr_sc;
773 l_counter := l_counter + 1;
774 gplsqltable (l_counter).tagname := 'Q_SC_1_3_ABS_DAYS';
775 gplsqltable (l_counter).tagvalue := round(abstotal(4).sick_1_3_days_sc);
776 l_counter := l_counter + 1;
777 gplsqltable (l_counter).tagname := 'Q_DC_1_3_DAYS_OCCUR';
778 gplsqltable (l_counter).tagvalue:= abstotal(4).sick_1_3_ocr_dc;
779 l_counter := l_counter + 1;
780 gplsqltable (l_counter).tagname := 'Q_DC_1_3_ABS_DAYS';
781 gplsqltable (l_counter).tagvalue := round(abstotal(4).sick_1_3_days_dc);
782 l_counter := l_counter + 1;
783 gplsqltable (l_counter).tagname := 'Q_SICK_4_16_DAYS_OCCUR';
784 gplsqltable (l_counter).tagvalue:= abstotal(4).sick_4_16_ocrs;
785 l_counter := l_counter+ 1;
786 gplsqltable (l_counter).tagname := 'Q_SICK_4_16_ABS_DAYS';
787 gplsqltable (l_counter).tagvalue:= round(abstotal(4).sick_4_16_days);
788 l_counter := l_counter + 1;
789 gplsqltable (l_counter).tagname := 'Q_MORE_THAN_16DAYS_OCCUR';
790 gplsqltable (l_counter).tagvalue := abstotal(4).sick_more_16_ocrs;
791 l_counter := l_counter + 1;
792 gplsqltable (l_counter).tagname := 'Q_MORE_THAN_16DAYS_ABS';
793 gplsqltable (l_counter).tagvalue := round(abstotal(4).sick_more_16_days);
794 l_counter := l_counter + 1;
795
796 gplsqltable (l_counter).tagname := 'Q_MORE_THAN_8W_OCCUR';
797 gplsqltable (l_counter).tagvalue := abstotal(4).sick_more_8w_ocrs;
798 l_counter := l_counter + 1;
799 gplsqltable (l_counter).tagname := 'Q_MORE_THAN_8W_DAYS';
800 gplsqltable (l_counter).tagvalue := round(abstotal(4).sick_more_8w_days);
801 l_counter := l_counter + 1;
802
803 gplsqltable (l_counter).tagname := 'Q_CHILD_MINDERS_OCRS';
804 gplsqltable (l_counter).tagvalue := abstotal(4).cms_abs_ocrs;
805 l_counter := l_counter + 1;
806 gplsqltable (l_counter).tagname := 'Q_CHILD_MINDERS_DAYS';
807 gplsqltable (l_counter).tagvalue := round(abstotal(4).cms_abs_days);
808 l_counter := l_counter + 1;
809 gplsqltable (l_counter).tagname := 'Q_PATERNAL_LEAVE_OCRS';
810 gplsqltable (l_counter).tagvalue := abstotal(4).parental_abs_ocrs;
811 l_counter := l_counter + 1;
812 gplsqltable (l_counter).tagname := 'Q_PATERNAL_LEAVE_DAYS';
813 gplsqltable (l_counter).tagvalue := round(abstotal(4).parental_abs_days);
814 l_counter := l_counter + 1;
815 gplsqltable (l_counter).tagname := 'Q_OTHER_LEAVE_OCRS';
816 gplsqltable (l_counter).tagvalue := abstotal(4).other_abs_ocrs;
817 l_counter := l_counter + 1;
818 gplsqltable (l_counter).tagname := 'Q_OTHER_LEAVE_DAYS';
819 gplsqltable (l_counter).tagvalue := round(abstotal(4).other_abs_days);
820 l_counter := l_counter + 1;
821
822 gplsqltable (l_counter).tagname := 'Q_OTHER_PAID_LEAVE_OCRS';
823 gplsqltable (l_counter).tagvalue := abstotal(4).other_abs_paid_ocrs;
824 l_counter := l_counter + 1;
825 gplsqltable (l_counter).tagname := 'Q_OTHER_PAID_LEAVE_DAYS';
826 gplsqltable (l_counter).tagvalue := round(abstotal(4).other_abs_paid_days);
827 l_counter := l_counter + 1;
828
829 gplsqltable (l_counter).tagname := 'Q_SICK_PERCENTAGE';
830 gplsqltable (l_counter).tagvalue := round(l_sickness_percentage,1);
831 l_counter := l_counter + 1;
832
833 gplsqltable (l_counter).tagname := 'END';
834 gplsqltable (l_counter).tagvalue := 'END';
835 l_counter := l_counter + 1;
836 writetoclob (p_xml);
837
838 END get_data;
839 -----------------------------------------------------------------------------------------------------------------
840 PROCEDURE writetoclob (p_xfdf_clob OUT NOCOPY CLOB)
841 IS
842 l_xfdf_string CLOB;
843 l_str1 VARCHAR2 (1000);
844 l_str2 VARCHAR2 (20);
845 l_str3 VARCHAR2 (20);
846 l_str4 VARCHAR2 (20);
847 l_str5 VARCHAR2 (20);
848 l_str6 VARCHAR2 (30);
849 l_str7 VARCHAR2 (1000);
850 l_str8 VARCHAR2 (240);
854 l_str12 VARCHAR2 (20);
851 l_str9 VARCHAR2 (240);
852 l_str10 VARCHAR2 (20);
853 l_str11 VARCHAR2 (20);
855 current_index PLS_INTEGER;
856 l_counter PLS_INTEGER;
857 l_IANA_charset VARCHAR2 (50);
858 l_quater varchar2(30);
859 l_quater_flag VARCHAR2(3):='Y';
860 BEGIN
861 l_IANA_charset :=hr_no_utility.get_IANA_charset ;
862 hr_utility.set_location ('Entering WritetoCLOB ', 70);
863 l_str1 := '<?xml version="1.0" encoding="'||l_IANA_charset||'"?> <ROOT>';
864 l_str2 := '<';
865 l_str3 := '>';
866 l_str4 := '</';
867 l_str5 := '>';
868 l_str6 := '</ROOT>';
869 l_str7 := '<?xml version="1.0" encoding="'||l_IANA_charset||'"?> <ROOT></ROOT>';
870 l_str10 := '<QUARTER>';
871 l_str11 := '</QUARTER>';
872 l_str12 := 'QUARTER_VAL';
873 DBMS_LOB.createtemporary (l_xfdf_string, FALSE, DBMS_LOB.CALL);
874 DBMS_LOB.OPEN (l_xfdf_string, DBMS_LOB.lob_readwrite);
875 current_index := 0;
876 IF gplsqltable.COUNT > 0 THEN
877 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str1), l_str1);
878 FOR table_counter IN gplsqltable.FIRST .. gplsqltable.LAST
879 LOOP
880 l_str8 := gplsqltable (table_counter).tagname;
881 l_str9 := gplsqltable (table_counter).tagvalue;
882 hr_utility.set_location(' l_quater : '||l_quater,10);
883 hr_utility.set_location(' l_str8 : '||l_str8,20);
884 hr_utility.set_location(' l_str9 : '||l_str9,30);
885 IF l_quater <> gplsqltable (table_counter).tagvalue and l_str8 = 'QUARTER' and l_quater is not null THEN
886 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str4), l_str4);
887 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str8), l_str8);
888 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str5), l_str5);
889 l_quater := gplsqltable (table_counter).tagvalue;
890 l_quater_flag:='Y';
891 END IF;
892 IF l_str8 = 'QUARTER' and l_quater_flag = 'Y' then
893 l_quater:=gplsqltable (table_counter).tagvalue;
894 IF l_str9 IS NOT NULL THEN
895 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str2), l_str2);
896 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str8), l_str8);
897 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str3), l_str3);
898 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str2), l_str2);
899 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str12), l_str12);
900 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str3), l_str3);
901 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str9), l_str9);
902 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str4), l_str4);
903 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str12), l_str12);
904 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str5), l_str5);
905
906 Else
907 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str2), l_str2);
908 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str8), l_str8);
909 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str3), l_str3);
910 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str2), l_str2);
911 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str12), l_str12);
912 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str3), l_str3);
913 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str4), l_str4);
914 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str12), l_str12);
915 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str5), l_str5);
916
917 End if;
918 l_quater_flag:='N';
919 End if;
920 IF l_str8 <> 'QUARTER' THEN
921
922 /* IF l_str9 = 'END' THEN
923 DBMS_LOB.writeappend (l_xfdf_string,LENGTH (l_str11),l_str11);
924 ELSIF l_str9 = 'START' THEN
925 DBMS_LOB.writeappend (l_xfdf_string,LENGTH (l_str10),l_str10);*/
926
927 IF l_str9 IS NOT NULL THEN
928 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str2), l_str2);
929 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str8), l_str8);
930 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str3), l_str3);
931 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str9), l_str9);
932 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str4), l_str4);
933 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str8), l_str8);
934 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str5), l_str5);
935 ELSE
936 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str2), l_str2);
937 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str8), l_str8);
938 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str3), l_str3);
939 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str4), l_str4);
940 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str8), l_str8);
941 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str5), l_str5);
942 END IF;
943 END IF;
944 IF table_counter = gplsqltable.LAST THEN
945 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str4), l_str4);
946 DBMS_LOB.writeappend (l_xfdf_string, LENGTH ('QUARTER'), 'QUARTER');
947 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str5), l_str5);
948 END IF;
949 END LOOP;
950 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str6), l_str6);
951 ELSE
952 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str7), l_str7);
953 END IF;
954 p_xfdf_clob := l_xfdf_string;
955
956 EXCEPTION
957 WHEN OTHERS
958 THEN
959 hr_utility.TRACE ( 'sqlerrm '
960 || SQLERRM);
961 hr_utility.raise_error;
962 END writetoclob;
963 END PAY_NO_ABS_STATISTICS_REPORT;