[Home] [Help]
PACKAGE BODY: APPS.PER_KW_XDO_REPORT
Source
1 PACKAGE BODY per_kw_xdo_report AS
2 /* $Header: pekwxdor.pkb 120.4 2006/12/06 06:49:57 spendhar noship $ */
3 PROCEDURE get_disability_data
4 (p_request_id IN NUMBER
5 ,p_report_name IN VARCHAR2
6 ,p_date IN VARCHAR2 DEFAULT NULL
7 ,p_business_group_id IN NUMBER DEFAULT NULL
8 ,p_org_structure_id IN NUMBER DEFAULT NULL
9 ,p_org_structure_version_id IN NUMBER DEFAULT NULL
10 --,p_organization_id IN NUMBER DEFAULT NULL
11 ,p_legal_employer IN NUMBER DEFAULT NULL
12 ,p_disability_type IN VARCHAR2 DEFAULT NULL
13 ,p_disability_status IN VARCHAR2 DEFAULT NULL
14 ,l_xfdf_blob OUT NOCOPY BLOB) AS
15 l_parent_id NUMBER;
16 l_date DATE;
17 l_business_group_id NUMBER;
18 CURSOR csr_get_bg_id IS
19 SELECT business_group_id
20 FROM per_business_groups
21 WHERE business_group_id = nvl(p_business_group_id, business_group_id)
22 AND legislation_code = 'KW';
23 rec_get_bg_id csr_get_bg_id%ROWTYPE;
24 CURSOR csr_get_org_id IS
25 SELECT org.organization_id
26 ,org.name
27 FROM hr_all_organization_units org
28 ,hr_organization_information hoi
29 WHERE org.organization_id IN (SELECT pose.organization_id_child
30 FROM per_org_structure_elements pose
31 CONNECT BY pose.organization_id_parent = PRIOR pose.organization_id_child
32 AND pose.org_structure_version_id = p_org_structure_version_id
33 START WITH pose.organization_id_parent = nvl(p_legal_employer, l_parent_id)
34 AND pose.org_structure_version_id = p_org_structure_version_id
35 UNION
36 SELECT nvl(p_legal_employer, l_parent_id)
37 FROM DUAL)
38 AND p_org_structure_version_id IS NOT NULL
39 AND (p_legal_employer IS NOT NULL OR l_parent_id IS NOT NULL)
40 AND org.organization_id = hoi.organization_id
41 AND hoi.org_information_context = 'CLASS'
42 AND hoi.org_information1 = 'HR_LEGAL_EMPLOYER'
43 UNION
44 SELECT org.organization_id
45 ,org.name
46 FROM hr_all_organization_units org
47 ,hr_organization_information hoi
48 WHERE org.organization_id = NVL(p_legal_employer,org.organization_id)
49 AND org.business_group_id = l_business_group_id
50 AND p_org_structure_version_id IS NULL
51 AND org.organization_id = hoi.organization_id
52 AND hoi.org_information_context = 'CLASS'
53 AND hoi.org_information1 = 'HR_LEGAL_EMPLOYER';
54 rec_get_org_id csr_get_org_id%ROWTYPE;
55 CURSOR csr_get_dis_emp IS
56 SELECT /*+ INDEX(disb, PER_DISABILITIES_F_PK) */ people.employee_number
57 ,people.full_name
58 ,period.date_start
59 ,hr3.meaning disability_status
60 ,job.name job_name
61 ,pos.name position_name
62 --,emp.name employment_office
63 ,hr1.meaning disability_type
64 ,hr2.meaning reason
65 ,nvl(to_char(disb.degree),get_lookup_meaning('KW_DISABILITY_RANGE',(dis_information1))) rate
66 --, null RATE
67 ,disb.dis_information2 rep_description
68 ,disb.incident_id
69 ,disb.disability_id
70 FROM per_all_assignments_f assg
71 ,per_all_people_f people
72 ,per_disabilities_f disb
73 ,per_jobs job
74 ,per_all_positions pos
75 ,per_periods_of_service period
76 ,hr_lookups hr1
77 ,hr_lookups hr2
78 ,hr_lookups hr3
79 ,hr_all_organization_units org
80 --,hr_all_organization_units emp
81 ,hr_soft_coding_keyflex hsck
82 WHERE assg.person_id = people.person_id
83 AND assg.assignment_type = 'E'
84 AND (l_date) BETWEEN assg.effective_start_date
85 AND assg.effective_end_date
86 AND (l_date) between people.effective_start_date
87 AND people.effective_end_date
88 AND (l_date) between disb.effective_start_date
89 AND disb.effective_end_date
90 AND assg.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
91 AND assg.job_id = job.job_id(+)
92 AND assg.position_id = pos.position_id(+)
93 AND people.person_id = period.person_id
94 --AND assg.business_group_id = org.business_group_id
95 --AND assg.organization_id = rec_get_org_id.organization_id
96 AND assg.business_group_id = org.business_group_id
97 AND org.business_group_id = l_business_group_id
98 AND hr1.lookup_type = 'DISABILITY_CATEGORY'
99 AND hr1.lookup_code = disb.category
100 --AND assg.organization_id = org.organization_id
101 AND disb.person_id = people.person_id
102 AND disb.reason = hr2.lookup_code(+)
103 AND hr2.lookup_type(+) = 'DISABILITY_REASON'
104 AND disb.status = hr3.lookup_code
105 AND hr3.lookup_type = 'DISABILITY_STATUS'
106 AND org.organization_id = rec_get_org_id.organization_id
107 AND nvl(p_disability_type, hr1.lookup_code) = hr1.lookup_code
108 AND nvl(p_disability_status, hr3.lookup_code) = hr3.lookup_code
109 --AND emp.organization_id = hsck.segment1
110 AND to_char(org.organization_id) = hsck.segment1
111 AND hsck.segment1 = to_char(rec_get_org_id.organization_id)
112 ORDER BY full_name, employee_number;
113 rec_get_dis_emp csr_get_dis_emp%ROWTYPE;
114 l_org_name VARCHAR2(80);
115 l_structure_name VARCHAR2(80);
116 l_version NUMBER;
117 l_disability_type VARCHAR2(80);
118 l_disability_status VARCHAR2(80);
119 l_legal_employer VARCHAR2(80);
120 l_emp_found NUMBER;
121 i NUMBER;
122 j NUMBER;
123 l_incident_date DATE;
124 l_consultation_date DATE;
125 l_pg_count NUMBER;
126 BEGIN
127 l_pg_count := 1;
128 IF p_date IS NOT NULL THEN
129 BEGIN
130 SELECT fnd_date.canonical_to_date(p_date)
131 INTO l_date
132 FROM DUAL;
133 EXCEPTION
134 WHEN OTHERS THEN
135 l_date := TRUNC(sysdate);
136 END;
137 ELSE
138 l_date := TRUNC(sysdate);
139 END IF;
140 INSERT INTO fnd_sessions (session_id, effective_date)
141 VALUES (userenv('SESSIONID'), l_date);
142 l_parent_id := NULL;
143 l_org_name := NULL;
144 l_structure_name := NULL;
145 l_version := NULL;
146 l_disability_type := NULL;
147 l_disability_status := NULL;
148 l_legal_employer := NULL;
149 l_emp_found := 0;
150 i := 1;
151 j := 0;
152 gxmltable.DELETE;
153 gCtr := 1;
154 IF p_org_structure_version_id IS NOT NULL
155 AND p_legal_employer IS NULL THEN
156 BEGIN
157 SELECT distinct pose.organization_id_parent
158 INTO l_parent_id
159 FROM per_org_structure_elements pose
160 WHERE pose.org_structure_version_id = p_org_structure_version_id
161 AND pose.organization_id_parent NOT IN (SELECT pose1.organization_id_child
162 FROM per_org_structure_elements pose1
163 WHERE pose1.org_structure_version_id
164 = p_org_structure_version_id);
165 EXCEPTION
166 WHEN OTHERS THEN
167 l_parent_id := NULL;
168 END;
169 END IF;
170 IF p_business_group_id IS NOT NULL THEN
171 BEGIN
172 SELECT name
173 INTO l_org_name
174 FROM hr_organization_units
175 WHERE organization_id = p_business_group_id;
176 EXCEPTION
177 WHEN OTHERS THEN
178 NULL;
179 END;
180 END IF;
181 IF p_org_structure_id IS NOT NULL THEN
182 BEGIN
183 SELECT name
184 INTO l_structure_name
185 FROM per_organization_structures
186 WHERE organization_structure_id = p_org_structure_id;
187 EXCEPTION
188 WHEN OTHERS THEN
189 NULL;
190 END;
191 END IF;
192 IF p_org_structure_version_id IS NOT NULL THEN
193 BEGIN
194 SELECT version_number
195 INTO l_version
196 FROM per_org_structure_versions
197 WHERE org_structure_version_id = p_org_structure_version_id;
198 EXCEPTION
199 WHEN OTHERS THEN
200 NULL;
201 END;
202 END IF;
203 IF p_disability_type IS NOT NULL THEN
204 BEGIN
205 SELECT meaning
206 INTO l_disability_type
207 FROM hr_lookups
208 WHERE lookup_type = 'DISABILITY_CATEGORY'
209 AND lookup_code = p_disability_type;
210 EXCEPTION
211 WHEN OTHERS THEN
212 NULL;
213 END;
214 END IF;
215 IF p_disability_status IS NOT NULL THEN
216 BEGIN
217 SELECT meaning
218 INTO l_disability_status
219 FROM hr_lookups
220 WHERE lookup_type = 'DISABILITY_STATUS'
221 AND lookup_code = p_disability_status;
222 EXCEPTION
223 WHEN OTHERS THEN
224 NULL;
225 END;
226 END IF;
227 IF p_legal_employer IS NOT NULL THEN
228 BEGIN
229 SELECT name
230 INTO l_legal_employer
231 FROM hr_organization_units
232 WHERE organization_id = p_legal_employer;
233 EXCEPTION
234 WHEN OTHERS THEN
235 NULL;
236 END;
237 END IF;
238 --Populate parameter labels and values
239 gxmltable(gCtr).tagName := 'report_date_label';
240 gxmltable(gCtr).tagValue := get_lookup_meaning('KW_FORM_LABELS','REPORT_DATE_LABEL');
241 gctr := gctr + 1;
242 gxmltable(gCtr).tagName := 'page_number_label';
243 gxmltable(gCtr).tagValue := get_lookup_meaning('KW_FORM_LABELS','PAGE_NO_LABEL');
244 gctr := gctr + 1;
245 gxmltable(gCtr).tagName := 'report_date_value';
246 gxmltable(gCtr).tagValue := fnd_date.date_to_displaydate(sysdate);
247 gctr := gctr + 1;
248 gxmltable(gCtr).tagName := 'of_label';
249 gxmltable(gCtr).tagValue := get_lookup_meaning('KW_FORM_LABELS','OF_LABEL');
250 gctr := gctr + 1;
251 gxmltable(gCtr).tagName := 'one_value';
252 gxmltable(gCtr).tagValue := 1;
253 gctr := gctr + 1;
254 gxmltable(gCtr).tagName := 'count_value';
255 gxmltable(gCtr).tagValue := null;
256 gctr := gctr + 1;
257 gxmltable(gCtr).tagName := 'report_parameters_label';
258 gxmltable(gCtr).tagValue := get_lookup_meaning('KW_FORM_LABELS','REPORT_PARAMETERS_LABEL');
259 gctr := gctr + 1;
260 gxmltable(gCtr).tagName := 'organization_name_label';
261 gxmltable(gCtr).tagValue := get_lookup_meaning('KW_FORM_LABELS','ORGANIZATION_NAME_LABEL');
262 gctr := gctr + 1;
263 gxmltable(gCtr).tagName := 'organization_name_value';
264 gxmltable(gCtr).tagValue := l_org_name;
265 gctr := gctr + 1;
266 gxmltable(gCtr).tagName := 'organization_hierarchy_label';
267 gxmltable(gCtr).tagValue := get_lookup_meaning('KW_FORM_LABELS','ORGANIZATION_HIERARCHY_LABEL');
268 gctr := gctr + 1;
269 gxmltable(gCtr).tagName := 'organization_hierarchy_value';
270 gxmltable(gCtr).tagValue := l_structure_name;
271 gctr := gctr + 1;
272 gxmltable(gCtr).tagName := 'organization_version_label';
273 gxmltable(gCtr).tagValue := get_lookup_meaning('KW_FORM_LABELS','ORGANIZATION_VERSION_LABEL');
274 gctr := gctr + 1;
275 gxmltable(gCtr).tagName := 'organization_version_value';
276 gxmltable(gCtr).tagValue := l_version;
277 gctr := gctr + 1;
278 gxmltable(gCtr).tagName := 'disability_type_label';
279 gxmltable(gCtr).tagValue := get_lookup_meaning('KW_FORM_LABELS','DISABILITY_TYPE_LABEL');
280 gctr := gctr + 1;
281 gxmltable(gCtr).tagName := 'disability_type_value';
282 gxmltable(gCtr).tagValue := l_disability_type;
283 gctr := gctr + 1;
284 gxmltable(gCtr).tagName := 'disability_status_label';
285 gxmltable(gCtr).tagValue := get_lookup_meaning('KW_FORM_LABELS','DISABILITY_STATUS_LABEL');
286 gctr := gctr + 1;
287 gxmltable(gCtr).tagName := 'disability_status_value';
288 gxmltable(gCtr).tagValue := l_disability_status;
289 gctr := gctr + 1;
290 gxmltable(gCtr).tagName := 'legal_employer_label';
291 gxmltable(gCtr).tagValue := get_lookup_meaning('KW_FORM_LABELS','LEGAL_EMPLOYER_LABEL');
292 gctr := gctr + 1;
293 gxmltable(gCtr).tagName := 'legal_employer_value';
294 gxmltable(gCtr).tagValue := l_legal_employer;
295 gctr := gctr + 1;
296 gxmltable(gCtr).tagName := 'effective_date_label';
297 gxmltable(gCtr).tagValue := get_lookup_meaning('KW_FORM_LABELS','EFFECTIVE_DATE_LABEL');
298 gctr := gctr + 1;
299 gxmltable(gCtr).tagName := 'effective_date_value';
300 gxmltable(gCtr).tagValue := fnd_date.date_to_displaydate(l_date);
301 gctr := gctr + 1;
302 gxmltable(gCtr).tagName := '0';
303 gxmltable(gCtr).tagValue := ('-----------------------------------------------------');
304 gctr := gctr + 1;
305 OPEN csr_get_bg_id;
306 LOOP
307 FETCH csr_get_bg_id INTO rec_get_bg_id;
308 EXIT WHEN csr_get_bg_id%NOTFOUND;
309 l_business_group_id := rec_get_bg_id.business_group_id;
310 OPEN csr_get_org_id;
311 LOOP
312 FETCH csr_get_org_id INTO rec_get_org_id;
313 EXIT WHEN csr_get_org_id%NOTFOUND;
314 --fnd_file.put_line(fnd_file.log,'in org cursor: '||rec_get_org_id.name);
315 l_emp_found := 0;
316 i := 1;
317 OPEN csr_get_dis_emp;
318 LOOP
319 FETCH csr_get_dis_emp INTO rec_get_dis_emp;
320 EXIT WHEN csr_get_dis_emp%NOTFOUND;
321 IF i = 19 THEN
322 i := 1;
323 END IF;
324 IF l_emp_found = 0 OR i = 1 THEN
325 l_pg_count := l_pg_count + 1;
326 gxmltable(gCtr).tagName := 'report_label';
327 gxmltable(gCtr).tagValue := get_lookup_meaning('KW_FORM_LABELS','REPORT_LABEL');
328 gctr := gctr + 1;
329 gxmltable(gCtr).tagName := 'report_date_body_label';
330 gxmltable(gCtr).tagValue := get_lookup_meaning('KW_FORM_LABELS','REPORT_DATE_LABEL');
331 gctr := gctr + 1;
332 gxmltable(gCtr).tagName := 'report_date_body_value';
333 gxmltable(gCtr).tagValue := fnd_date.date_to_displaydate(sysdate);
334 gctr := gctr + 1;
335 gxmltable(gCtr).tagName := 'page_number_body_label';
336 gxmltable(gCtr).tagValue := get_lookup_meaning('KW_FORM_LABELS','PAGE_NO_LABEL');
337 gctr := gctr + 1;
338 gxmltable(gCtr).tagName := 'of_body_label';
339 gxmltable(gCtr).tagValue := get_lookup_meaning('KW_FORM_LABELS','OF_LABEL');
340 gctr := gctr + 1;
341 gxmltable(gCtr).tagName := 'current_value';
342 gxmltable(gCtr).tagValue := l_pg_count;
343 gctr := gctr + 1;
344 gxmltable(gCtr).tagName := 'count_body_value';
345 gxmltable(gCtr).tagValue := null;
346 gctr := gctr + 1;
347 gxmltable(gCtr).tagName := 'organization_label';
348 gxmltable(gCtr).tagValue := get_lookup_meaning('KW_FORM_LABELS','ORGANIZATION_LABEL');
352 gctr := gctr + 1;
349 gctr := gctr + 1;
350 gxmltable(gCtr).tagName := 'organization_value';
351 gxmltable(gCtr).tagValue := rec_get_org_id.name;
353 gxmltable(gCtr).tagName := 'employee_number_label';
354 gxmltable(gCtr).tagValue := get_lookup_meaning('KW_FORM_LABELS','EMPLOYEE_NUMBER_LABEL');
355 gctr := gctr + 1;
356 gxmltable(gCtr).tagName := 'full_name_label';
357 gxmltable(gCtr).tagValue := get_lookup_meaning('KW_FORM_LABELS','FULL_NAME_LABEL');
358 gctr := gctr + 1;
359 gxmltable(gCtr).tagName := 'start_date_label';
360 gxmltable(gCtr).tagValue := get_lookup_meaning('KW_FORM_LABELS','START_DATE_LABEL');
361 gctr := gctr + 1;
362 gxmltable(gCtr).tagName := 'job_label';
363 gxmltable(gCtr).tagValue := get_lookup_meaning('KW_FORM_LABELS','JOB_LABEL');
364 gctr := gctr + 1;
365 gxmltable(gCtr).tagName := 'disability_type_body_label';
366 gxmltable(gCtr).tagValue := get_lookup_meaning('KW_FORM_LABELS','DISABILITY_TYPE_BODY_LABEL');
367 gctr := gctr + 1;
368 gxmltable(gCtr).tagName := 'reason_label';
369 gxmltable(gCtr).tagValue := get_lookup_meaning('KW_FORM_LABELS','REASON_LABEL');
370 gctr := gctr + 1;
371 gxmltable(gCtr).tagName := 'rate_label';
372 gxmltable(gCtr).tagValue := get_lookup_meaning('KW_FORM_LABELS','RATE_LABEL');
373 gctr := gctr + 1;
374 gxmltable(gCtr).tagName := 'disability_label';
375 gxmltable(gCtr).tagValue := get_lookup_meaning('KW_FORM_LABELS','DISABILITY_LABEL');
376 gctr := gctr + 1;
377 gxmltable(gCtr).tagName := 'incident_date_label';
378 gxmltable(gCtr).tagValue := get_lookup_meaning('KW_FORM_LABELS','INCIDENT_DATE_LABEL');
379 gctr := gctr + 1;
380 gxmltable(gCtr).tagName := 'assessment_date_label';
381 gxmltable(gCtr).tagValue := get_lookup_meaning('KW_FORM_LABELS','ASSESSMENT_DATE_LABEL');
382 gctr := gctr + 1;
383 gxmltable(gCtr).tagName := '1';
384 gxmltable(gCtr).tagValue := ('--------------------');
385 gctr := gctr + 1;
386 gxmltable(gCtr).tagName := '2';
387 gxmltable(gCtr).tagValue := ('------------------------------------------');
388 gctr := gctr + 1;
389 gxmltable(gCtr).tagName := '3';
390 gxmltable(gCtr).tagValue := ('--------------------');
391 gctr := gctr + 1;
392 gxmltable(gCtr).tagName := '4';
393 gxmltable(gCtr).tagValue := ('--------------------');
394 gctr := gctr + 1;
395 gxmltable(gCtr).tagName := '5';
396 gxmltable(gCtr).tagValue := ('---------------------');
397 gctr := gctr + 1;
398 gxmltable(gCtr).tagName := '6';
399 gxmltable(gCtr).tagValue := ('------------------------');
400 gctr := gctr + 1;
401 gxmltable(gCtr).tagName := '7';
402 gxmltable(gCtr).tagValue := ('----------');
403 gctr := gctr + 1;
404 gxmltable(gCtr).tagName := '8';
405 gxmltable(gCtr).tagValue := ('-------------------------');
406 gctr := gctr + 1;
407 gxmltable(gCtr).tagName := '9';
408 gxmltable(gCtr).tagValue := ('--------------------');
409 gctr := gctr + 1;
410 gxmltable(gCtr).tagName := '10';
411 gxmltable(gCtr).tagValue := ('----------------');
412 gctr := gctr + 1;
413 l_emp_found := 1;
414 END IF;
415 --fnd_file.put_line(fnd_file.log,'in dis procedure: '||rec_get_dis_emp.employee_number||'*'||rec_get_org_id.name);
416 l_incident_date := NULL;
417 IF rec_get_dis_emp.incident_id IS NOT NULL THEN
418 BEGIN
419 SELECT incident_date
420 INTO l_incident_date
421 FROM per_work_incidents
422 WHERE incident_id = rec_get_dis_emp.incident_id;
423 EXCEPTION
424 WHEN OTHERS THEN
425 l_incident_date := NULL;
426 END;
427 END IF;
428 l_consultation_date := NULL;
429 BEGIN
430 SELECT consultation_date
431 INTO l_consultation_date
432 FROM per_disabilities_v
433 WHERE disability_id = rec_get_dis_emp.disability_id
434 AND l_date BETWEEN effective_start_date and effective_end_date;
435 EXCEPTION
436 WHEN OTHERS THEN
437 l_consultation_date := NULL;
438 END;
439 gxmltable(gCtr).tagName := 'employee_number_value'||' '||i;
440 gxmltable(gCtr).tagValue := (rec_get_dis_emp.employee_number);
441 gctr := gctr + 1;
442 gxmltable(gCtr).tagName := 'full_name_value'||' '||i;
443 gxmltable(gCtr).tagValue := (rec_get_dis_emp.full_name);
444 gctr := gctr + 1;
445 gxmltable(gCtr).tagName := 'start_date_value'||' '||i;
446 gxmltable(gCtr).tagValue := fnd_date.date_to_displaydate(rec_get_dis_emp.date_start);
447 gctr := gctr + 1;
448 gxmltable(gCtr).tagName := 'job_value'||' '||i;
449 gxmltable(gCtr).tagValue := (rec_get_dis_emp.job_name);
450 gctr := gctr + 1;
451 gxmltable(gCtr).tagName := 'disability_type_body_value'||' '||i;
452 gxmltable(gCtr).tagValue := (rec_get_dis_emp.disability_type);
453 gctr := gctr + 1;
454 gxmltable(gCtr).tagName := 'reason_value'||' '||i;
455 gxmltable(gCtr).tagValue := (rec_get_dis_emp.reason);
456 gctr := gctr + 1;
457 gxmltable(gCtr).tagName := 'rate_value'||' '||i;
458 gxmltable(gCtr).tagValue := (rec_get_dis_emp.rate);
459 gctr := gctr + 1;
460 gxmltable(gCtr).tagName := 'disability_value'||' '||i;
461 gxmltable(gCtr).tagValue := (rec_get_dis_emp.rep_description);
462 gctr := gctr + 1;
466 gxmltable(gCtr).tagName := 'assessment_date_value'||' '||i;
463 gxmltable(gCtr).tagName := 'incident_date_value'||' '||i;
464 gxmltable(gCtr).tagValue := fnd_date.date_to_displaydate(l_incident_date);
465 gctr := gctr + 1;
467 gxmltable(gCtr).tagValue := fnd_date.date_to_displaydate(l_consultation_date);
468 gctr := gctr + 1;
469 i := i + 1;
470 END LOOP;
471 CLOSE csr_get_dis_emp;
472 IF i < 18 AND l_emp_found = 1 THEN
473 FOR j in i..18 LOOP
474 gxmltable(gCtr).tagName := 'employee_number_value'||' '||j;
475 gxmltable(gCtr).tagValue := (null);
476 gctr := gctr + 1;
477 gxmltable(gCtr).tagName := 'full_name_value'||' '||j;
478 gxmltable(gCtr).tagValue := (null);
479 gctr := gctr + 1;
480 gxmltable(gCtr).tagName := 'start_date_value'||' '||j;
481 gxmltable(gCtr).tagValue := (null);
482 gctr := gctr + 1;
483 gxmltable(gCtr).tagName := 'job_value'||' '||j;
484 gxmltable(gCtr).tagValue := (null);
485 gctr := gctr + 1;
486 gxmltable(gCtr).tagName := 'disability_type_body_value'||' '||j;
487 gxmltable(gCtr).tagValue := (null);
488 gctr := gctr + 1;
489 gxmltable(gCtr).tagName := 'reason_value'||' '||j;
490 gxmltable(gCtr).tagValue := (null);
491 gctr := gctr + 1;
492 gxmltable(gCtr).tagName := 'rate_value'||' '||j;
493 gxmltable(gCtr).tagValue := (null);
494 gctr := gctr + 1;
495 gxmltable(gCtr).tagName := 'disability_value'||' '||j;
496 gxmltable(gCtr).tagValue := (null);
497 gctr := gctr + 1;
498 gxmltable(gCtr).tagName := 'incident_date_value'||' '||j;
499 gxmltable(gCtr).tagValue := (null);
500 gctr := gctr + 1;
501 gxmltable(gCtr).tagName := 'assessment_date_value'||' '||j;
502 gxmltable(gCtr).tagValue := (null);
503 gctr := gctr + 1;
504 END LOOP;
505 END IF;
506 END LOOP;
507 CLOSE csr_get_org_id;
508 END LOOP;
509 CLOSE csr_get_bg_id;
510 WritetoCLOB ( l_xfdf_blob, l_pg_count );
511 END get_disability_data;
512 --------------------------------------------------------------------------------------------------------
513 PROCEDURE get_contract_data
514 (p_request_id IN NUMBER
515 ,p_report_name IN VARCHAR2
516 ,p_date IN VARCHAR2 DEFAULT NULL
517 ,p_business_group_id IN NUMBER DEFAULT NULL
518 ,p_org_structure_id IN NUMBER DEFAULT NULL
519 ,p_org_structure_version_id IN NUMBER DEFAULT NULL
520 ,p_legal_employer IN NUMBER DEFAULT NULL
521 ,p_duration IN NUMBER
522 ,p_units IN VARCHAR2
523 ,l_xfdf_blob OUT NOCOPY BLOB) AS
524 l_parent_id NUMBER;
525 l_date DATE;
526 l_business_group_id NUMBER;
527 CURSOR csr_get_bg_id IS
528 SELECT business_group_id
529 FROM per_business_groups
530 WHERE business_group_id = nvl(p_business_group_id, business_group_id)
531 AND legislation_code = 'KW';
532 rec_get_bg_id csr_get_bg_id%ROWTYPE;
533 CURSOR csr_get_org_id IS
534 SELECT org.organization_id
535 ,org.name
536 FROM hr_all_organization_units org
537 ,hr_organization_information hoi
538 WHERE org.organization_id IN (SELECT pose.organization_id_child
539 FROM per_org_structure_elements pose
540 CONNECT BY pose.organization_id_parent = PRIOR pose.organization_id_child
541 AND pose.org_structure_version_id = p_org_structure_version_id
542 START WITH pose.organization_id_parent = nvl(p_legal_employer, l_parent_id)
543 AND pose.org_structure_version_id = p_org_structure_version_id
544 UNION
545 SELECT nvl(p_legal_employer, l_parent_id)
546 FROM DUAL)
547 AND p_org_structure_version_id IS NOT NULL
548 AND (p_legal_employer IS NOT NULL OR l_parent_id IS NOT NULL)
549 AND org.organization_id = hoi.organization_id
550 AND hoi.org_information_context = 'CLASS'
551 AND hoi.org_information1 = 'HR_LEGAL_EMPLOYER'
552 UNION
553 SELECT org.organization_id
554 ,org.name
555 FROM hr_all_organization_units org
556 ,hr_organization_information hoi
557 WHERE org.organization_id = NVL(p_legal_employer,org.organization_id)
558 AND org.business_group_id = l_business_group_id
559 AND p_org_structure_version_id IS NULL
560 AND org.organization_id = hoi.organization_id
561 AND hoi.org_information_context = 'CLASS'
562 AND hoi.org_information1 = 'HR_LEGAL_EMPLOYER';
563 rec_get_org_id csr_get_org_id%ROWTYPE;
564 CURSOR csr_get_cont_emp IS
565 SELECT people.employee_number
566 ,people.full_name
567 ,period.date_start
568 ,job.name job_name
569 ,cont.reference cont_reference
570 ,get_lookup_meaning('CONTRACT_TYPE',cont.type) cont_type
571 ,cont.ctr_information1 employment_status
572 ,fnd_date.canonical_to_date(cont.ctr_information2) expiry_date
573 FROM per_all_assignments_f assg
574 ,per_all_people_f people
575 ,per_contracts cont
576 ,per_jobs job
577 ,per_periods_of_service period
578 ,hr_all_organization_units org
579 ,hr_soft_coding_keyflex hsck
580 WHERE assg.person_id = people.person_id
581 AND assg.contract_id = cont.contract_id
585 AND (l_date) between people.effective_start_date
582 AND assg.assignment_type = 'E'
583 AND (l_date) BETWEEN assg.effective_start_date
584 AND assg.effective_end_date
586 AND people.effective_end_date
587 AND assg.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
588 AND assg.job_id = job.job_id(+)
589 AND people.person_id = period.person_id
590 -- AND assg.organization_id = org.organization_id
591 AND org.business_group_id = l_business_group_id
592 AND cont.person_id = people.person_id
593 AND cont.ctr_information_category = 'KW'
594 AND org.organization_id = rec_get_org_id.organization_id
595 AND hsck.segment1 = to_char(rec_get_org_id.organization_id)
596 AND NVL(to_date(ctr_information2,'YYYY/MM/DD HH24:MI:SS'),
597 DECODE(duration_units,'D',(active_start_date+duration),
598 'W',(active_start_date+(duration*7)),
599 'M',(add_months(active_start_date,duration)),
600 'Y',(add_months(active_start_date,(duration*12)))))
601 BETWEEN NVL(l_date,sysdate)
602 AND DECODE(p_units,'D',(NVL(l_date,sysdate)+p_duration),
603 'W',(NVL(l_date,sysdate)+(p_duration*7)),
604 'M',(add_months(NVL(l_date,sysdate),p_duration)),
605 'Y',(add_months(NVL(l_date,sysdate),(p_duration*12))))
606 ORDER BY full_name, employee_number;
607 rec_get_cont_emp csr_get_cont_emp%ROWTYPE;
608 l_org_name VARCHAR2(80);
609 l_structure_name VARCHAR2(80);
610 l_version NUMBER;
611 l_contract_type VARCHAR2(80);
612 l_employment_status VARCHAR2(80);
613 l_expiry_date DATE;
614 l_legal_employer VARCHAR2(80);
615 l_emp_found NUMBER;
616 l_duration NUMBER;
617 l_units VARCHAR2(80);
618 i NUMBER;
619 j NUMBER;
620 l_incident_date DATE;
621 l_consultation_date DATE;
622 l_pg_count NUMBER;
623 BEGIN
624 l_pg_count := 1;
625 IF p_date IS NOT NULL THEN
626 BEGIN
627 SELECT fnd_date.canonical_to_date(p_date)
628 INTO l_date
629 FROM DUAL;
630 EXCEPTION
631 WHEN OTHERS THEN
632 l_date := TRUNC(sysdate);
633 END;
634 ELSE
635 l_date := TRUNC(sysdate);
636 END IF;
637 INSERT INTO fnd_sessions (session_id, effective_date)
638 VALUES (userenv('SESSIONID'), l_date);
639 fnd_file.put_line(fnd_file.log,'l_date: '|| l_date);
640 l_parent_id := NULL;
641 l_org_name := NULL;
642 l_structure_name := NULL;
643 l_version := NULL;
644 l_legal_employer := NULL;
645 l_emp_found := 0;
646 i := 1;
647 j := 0;
648 gxmltable.DELETE;
649 gCtr := 1;
650 IF p_org_structure_version_id IS NOT NULL
651 AND p_legal_employer IS NULL THEN
652 BEGIN
653 SELECT distinct pose.organization_id_parent
654 INTO l_parent_id
655 FROM per_org_structure_elements pose
656 WHERE pose.org_structure_version_id = p_org_structure_version_id
657 AND pose.organization_id_parent NOT IN (SELECT pose1.organization_id_child
658 FROM per_org_structure_elements pose1
659 WHERE pose1.org_structure_version_id
660 = p_org_structure_version_id);
661 EXCEPTION
662 WHEN OTHERS THEN
663 l_parent_id := NULL;
664 END;
665 END IF;
666 IF p_business_group_id IS NOT NULL THEN
667 BEGIN
668 SELECT name
669 INTO l_org_name
670 FROM hr_organization_units
671 WHERE organization_id = p_business_group_id;
672 EXCEPTION
673 WHEN OTHERS THEN
674 NULL;
675 END;
676 END IF;
677 IF p_org_structure_id IS NOT NULL THEN
678 BEGIN
679 SELECT name
680 INTO l_structure_name
681 FROM per_organization_structures
682 WHERE organization_structure_id = p_org_structure_id;
683 EXCEPTION
684 WHEN OTHERS THEN
685 NULL;
686 END;
687 END IF;
688 IF p_org_structure_version_id IS NOT NULL THEN
689 BEGIN
690 SELECT version_number
691 INTO l_version
692 FROM per_org_structure_versions
693 WHERE org_structure_version_id = p_org_structure_version_id;
694 EXCEPTION
695 WHEN OTHERS THEN
696 NULL;
697 END;
698 END IF;
699 IF p_units IS NOT NULL THEN
700 BEGIN
701 SELECT meaning
702 INTO l_units
703 FROM hr_lookups
704 WHERE lookup_type = 'QUALIFYING_UNITS'
705 AND lookup_code = p_units;
706 EXCEPTION
707 WHEN OTHERS THEN
708 NULL;
709 END;
710 END IF;
711 IF p_legal_employer IS NOT NULL THEN
712 BEGIN
713 SELECT name
714 INTO l_legal_employer
715 FROM hr_organization_units
716 WHERE organization_id = p_legal_employer;
717 EXCEPTION
718 WHEN OTHERS THEN
719 NULL;
720 END;
721 END IF;
722 --Populate parameter labels and values
723 gxmltable(gCtr).tagName := 'report_date_label';
724 gxmltable(gCtr).tagValue := get_lookup_meaning('KW_FORM_LABELS','REPORT_DATE_LABEL');
725 gctr := gctr + 1;
729 gxmltable(gCtr).tagName := 'report_date_value';
726 gxmltable(gCtr).tagName := 'page_number_label';
727 gxmltable(gCtr).tagValue := get_lookup_meaning('KW_FORM_LABELS','PAGE_NO_LABEL');
728 gctr := gctr + 1;
730 gxmltable(gCtr).tagValue := fnd_date.date_to_displaydate(sysdate);
731 gctr := gctr + 1;
732 gxmltable(gCtr).tagName := 'of_label';
733 gxmltable(gCtr).tagValue := get_lookup_meaning('KW_FORM_LABELS','OF_LABEL');
734 gctr := gctr + 1;
735 gxmltable(gCtr).tagName := 'one_value';
736 gxmltable(gCtr).tagValue := 1;
737 gctr := gctr + 1;
738 gxmltable(gCtr).tagName := 'count_value';
739 gxmltable(gCtr).tagValue := null;
740 gctr := gctr + 1;
741 gxmltable(gCtr).tagName := 'report_parameters_label';
742 gxmltable(gCtr).tagValue := get_lookup_meaning('KW_FORM_LABELS','REPORT_PARAMETERS_LABEL_CTR');
743 gctr := gctr + 1;
744 gxmltable(gCtr).tagName := 'organization_name_label';
745 gxmltable(gCtr).tagValue := get_lookup_meaning('KW_FORM_LABELS','ORGANIZATION_NAME_LABEL');
746 gctr := gctr + 1;
747 gxmltable(gCtr).tagName := 'organization_name_value';
748 gxmltable(gCtr).tagValue := l_org_name;
749 gctr := gctr + 1;
750 gxmltable(gCtr).tagName := 'organization_hierarchy_label';
751 gxmltable(gCtr).tagValue := get_lookup_meaning('KW_FORM_LABELS','ORGANIZATION_HIERARCHY_LABEL');
752 gctr := gctr + 1;
753 gxmltable(gCtr).tagName := 'organization_hierarchy_value';
754 gxmltable(gCtr).tagValue := l_structure_name;
755 gctr := gctr + 1;
756 gxmltable(gCtr).tagName := 'organization_version_label';
757 gxmltable(gCtr).tagValue := get_lookup_meaning('KW_FORM_LABELS','ORGANIZATION_VERSION_LABEL');
758 gctr := gctr + 1;
759 gxmltable(gCtr).tagName := 'organization_version_value';
760 gxmltable(gCtr).tagValue := l_version;
761 gctr := gctr + 1;
762 gxmltable(gCtr).tagName := 'duration_label';
763 gxmltable(gCtr).tagValue := get_lookup_meaning('KW_FORM_LABELS','DURATION');
764 gctr := gctr + 1;
765 gxmltable(gCtr).tagName := 'duration_value';
766 gxmltable(gCtr).tagValue := p_duration;
767 gctr := gctr + 1;
768 gxmltable(gCtr).tagName := 'duration_units_label';
769 gxmltable(gCtr).tagValue := get_lookup_meaning('KW_FORM_LABELS','DURATION_UNITS');
770 gctr := gctr + 1;
771 gxmltable(gCtr).tagName := 'duration_units_value';
772 gxmltable(gCtr).tagValue := l_units;
773 gctr := gctr + 1;
774 gxmltable(gCtr).tagName := 'legal_employer_label';
775 gxmltable(gCtr).tagValue := get_lookup_meaning('KW_FORM_LABELS','LEGAL_EMPLOYER_LABEL');
776 gctr := gctr + 1;
777 gxmltable(gCtr).tagName := 'legal_employer_value';
778 gxmltable(gCtr).tagValue := l_legal_employer;
779 gctr := gctr + 1;
780 gxmltable(gCtr).tagName := 'effective_date_label';
781 gxmltable(gCtr).tagValue := get_lookup_meaning('KW_FORM_LABELS','EFFECTIVE_DATE_LABEL');
782 gctr := gctr + 1;
783 gxmltable(gCtr).tagName := 'effective_date_value';
784 gxmltable(gCtr).tagValue := fnd_date.date_to_displaydate(l_date);
785 gctr := gctr + 1;
786 gxmltable(gCtr).tagName := '0';
787 gxmltable(gCtr).tagValue := ('-----------------------------------------------------');
788 gctr := gctr + 1;
789 OPEN csr_get_bg_id;
790 LOOP
791 FETCH csr_get_bg_id INTO rec_get_bg_id;
792 EXIT WHEN csr_get_bg_id%NOTFOUND;
793 l_business_group_id := rec_get_bg_id.business_group_id;
794 fnd_file.put_line(fnd_file.log,'BG ID: '|| l_business_group_id);
795 OPEN csr_get_org_id;
796 LOOP
797 FETCH csr_get_org_id INTO rec_get_org_id;
798 fnd_file.put_line(fnd_file.log,'rec_get_org_id.organization_id: '|| rec_get_org_id.organization_id);
799 EXIT WHEN csr_get_org_id%NOTFOUND;
800 --fnd_file.put_line(fnd_file.log,'in org cursor: '||rec_get_org_id.name);
801 fnd_file.put_line(fnd_file.log,'rec_get_org_id.organization_id: '|| rec_get_org_id.organization_id);
802 l_emp_found := 0;
803 i := 1;
804 OPEN csr_get_cont_emp;
805 LOOP
806 FETCH csr_get_cont_emp INTO rec_get_cont_emp;
807 EXIT WHEN csr_get_cont_emp%NOTFOUND;
808 IF i = 19 THEN
809 i := 1;
810 END IF;
811 IF l_emp_found = 0 OR i = 1 THEN
812 l_pg_count := l_pg_count + 1;
813 gxmltable(gCtr).tagName := 'report_label';
814 gxmltable(gCtr).tagValue := get_lookup_meaning('KW_FORM_LABELS','REPORT_LABEL_CTR');
815 gctr := gctr + 1;
816 gxmltable(gCtr).tagName := 'report_date_body_label';
817 gxmltable(gCtr).tagValue := get_lookup_meaning('KW_FORM_LABELS','REPORT_DATE_LABEL');
818 gctr := gctr + 1;
819 gxmltable(gCtr).tagName := 'report_date_body_value';
820 gxmltable(gCtr).tagValue := fnd_date.date_to_displaydate(sysdate);
821 gctr := gctr + 1;
822 gxmltable(gCtr).tagName := 'page_number_body_label';
823 gxmltable(gCtr).tagValue := get_lookup_meaning('KW_FORM_LABELS','PAGE_NO_LABEL');
824 gctr := gctr + 1;
825 gxmltable(gCtr).tagName := 'of_body_label';
826 gxmltable(gCtr).tagValue := get_lookup_meaning('KW_FORM_LABELS','OF_LABEL');
827 gctr := gctr + 1;
828 gxmltable(gCtr).tagName := 'current_value';
829 gxmltable(gCtr).tagValue := l_pg_count;
830 gctr := gctr + 1;
831 gxmltable(gCtr).tagName := 'count_body_value';
832 gxmltable(gCtr).tagValue := null;
833 gctr := gctr + 1;
834 gxmltable(gCtr).tagName := 'organization_label';
835 gxmltable(gCtr).tagValue := get_lookup_meaning('KW_FORM_LABELS','ORGANIZATION_LABEL');
836 gctr := gctr + 1;
837 gxmltable(gCtr).tagName := 'organization_value';
838 gxmltable(gCtr).tagValue := rec_get_org_id.name;
839 gctr := gctr + 1;
843 gxmltable(gCtr).tagName := 'full_name_label';
840 gxmltable(gCtr).tagName := 'employee_number_label';
841 gxmltable(gCtr).tagValue := get_lookup_meaning('KW_FORM_LABELS','EMPLOYEE_NUMBER_LABEL');
842 gctr := gctr + 1;
844 gxmltable(gCtr).tagValue := get_lookup_meaning('KW_FORM_LABELS','FULL_NAME_LABEL');
845 gctr := gctr + 1;
846 gxmltable(gCtr).tagName := 'appointment_date_label';
847 gxmltable(gCtr).tagValue := get_lookup_meaning('KW_FORM_LABELS','START_DATE_LABEL');
848 gctr := gctr + 1;
849 gxmltable(gCtr).tagName := 'job_label';
850 gxmltable(gCtr).tagValue := get_lookup_meaning('KW_FORM_LABELS','JOB_LABEL');
851 gctr := gctr + 1;
852 gxmltable(gCtr).tagName := 'contract_reference_label';
853 gxmltable(gCtr).tagValue := get_lookup_meaning('KW_FORM_LABELS','CONTRACT_REFERENCE');
854 gctr := gctr + 1;
855 gxmltable(gCtr).tagName := 'contract_type_body_label';
856 gxmltable(gCtr).tagValue := get_lookup_meaning('KW_FORM_LABELS','CONTRACT_TYPE');
857 gctr := gctr + 1;
858 gxmltable(gCtr).tagName := 'employment_status_label';
859 gxmltable(gCtr).tagValue := get_lookup_meaning('KW_FORM_LABELS','EMPLOYMENT_STATUS');
860 gctr := gctr + 1;
861 gxmltable(gCtr).tagName := 'expiry_date_label';
862 gxmltable(gCtr).tagValue := get_lookup_meaning('KW_FORM_LABELS','EXPIRY_DATE');
863 gctr := gctr + 1;
864 gxmltable(gCtr).tagName := '1';
865 gxmltable(gCtr).tagValue := ('------------------');
866 gctr := gctr + 1;
867 gxmltable(gCtr).tagName := '2';
868 gxmltable(gCtr).tagValue := ('------------------------');
869 gctr := gctr + 1;
870 gxmltable(gCtr).tagName := '3';
871 gxmltable(gCtr).tagValue := ('--------------------');
872 gctr := gctr + 1;
873 gxmltable(gCtr).tagName := '4';
874 gxmltable(gCtr).tagValue := ('------------------------');
875 gctr := gctr + 1;
876 gxmltable(gCtr).tagName := '5';
877 gxmltable(gCtr).tagValue := ('-------------------------');
878 gctr := gctr + 1;
879 gxmltable(gCtr).tagName := '6';
880 gxmltable(gCtr).tagValue := ('--------------------------');
881 gctr := gctr + 1;
882 gxmltable(gCtr).tagName := '7';
883 gxmltable(gCtr).tagValue := ('---------------------');
884 gctr := gctr + 1;
885 gxmltable(gCtr).tagName := '8';
886 gxmltable(gCtr).tagValue := ('----------------------');
887 gctr := gctr + 1;
888 l_emp_found := 1;
889 END IF;
890 gxmltable(gCtr).tagName := 'employee_number_value'||' '||i;
891 gxmltable(gCtr).tagValue := (rec_get_cont_emp.employee_number);
892 gctr := gctr + 1;
893 gxmltable(gCtr).tagName := 'full_name_value'||' '||i;
894 gxmltable(gCtr).tagValue := (rec_get_cont_emp.full_name);
895 gctr := gctr + 1;
896 gxmltable(gCtr).tagName := 'appointment_date_value'||' '||i;
897 gxmltable(gCtr).tagValue := fnd_date.date_to_displaydate(rec_get_cont_emp.date_start);
898 gctr := gctr + 1;
899 gxmltable(gCtr).tagName := 'job_value'||' '||i;
900 gxmltable(gCtr).tagValue := (rec_get_cont_emp.job_name);
901 gctr := gctr + 1;
902 gxmltable(gCtr).tagName := 'reference_value'||' '||i;
903 gxmltable(gCtr).tagValue := (rec_get_cont_emp.cont_reference);
904 gctr := gctr + 1;
905 gxmltable(gCtr).tagName := 'contract_type_body_value'||' '||i;
906 gxmltable(gCtr).tagValue := (rec_get_cont_emp.cont_type);
907 gctr := gctr + 1;
908 gxmltable(gCtr).tagName := 'employment_status_value'||' '||i;
909 gxmltable(gCtr).tagValue := (rec_get_cont_emp.employment_status);
910 gctr := gctr + 1;
911 gxmltable(gCtr).tagName := 'expiry_date_value'||' '||i;
912 gxmltable(gCtr).tagValue := fnd_date.date_to_displaydate(rec_get_cont_emp.expiry_date);
913 gctr := gctr + 1;
914 i := i + 1;
915 END LOOP;
916 CLOSE csr_get_cont_emp;
917 IF i < 18 AND l_emp_found = 1 THEN
918 FOR j in i..18 LOOP
919 gxmltable(gCtr).tagName := 'employee_number_value'||' '||j;
920 gxmltable(gCtr).tagValue := (null);
921 gctr := gctr + 1;
922 gxmltable(gCtr).tagName := 'full_name_value'||' '||j;
923 gxmltable(gCtr).tagValue := (null);
924 gctr := gctr + 1;
925 gxmltable(gCtr).tagName := 'appointment_date_value'||' '||j;
926 gxmltable(gCtr).tagValue := (null);
927 gctr := gctr + 1;
928 gxmltable(gCtr).tagName := 'job_value'||' '||j;
929 gxmltable(gCtr).tagValue := (null);
930 gctr := gctr + 1;
931 gxmltable(gCtr).tagName := 'contract_reference_value'||' '||j;
932 gxmltable(gCtr).tagValue := (null);
933 gctr := gctr + 1;
934 gxmltable(gCtr).tagName := 'contract_type_body_value'||' '||j;
935 gxmltable(gCtr).tagValue := (null);
936 gctr := gctr + 1;
937 gxmltable(gCtr).tagName := 'employment_status_value'||' '||j;
938 gxmltable(gCtr).tagValue := (null);
939 gctr := gctr + 1;
940 gxmltable(gCtr).tagName := 'expiry_date_value'||' '||j;
941 gxmltable(gCtr).tagValue := (null);
942 gctr := gctr + 1;
943 END LOOP;
944 END IF;
945 END LOOP;
946 CLOSE csr_get_org_id;
947 END LOOP;
948 CLOSE csr_get_bg_id;
949 WritetoCLOB ( l_xfdf_blob, l_pg_count );
950 END get_contract_data;
951 --------------------------------------------------------------------------------------------------------
952 PROCEDURE Writetoclob
953 (p_xfdf_blob out nocopy blob
954 ,p_tot_pg_count IN NUMBER) IS
955 l_xfdf_string clob;
959 l_str4 varchar2(20);
956 l_str1 varchar2(1000);
957 l_str2 varchar2(20);
958 l_str3 varchar2(20);
960 l_str5 varchar2(20);
961 l_str6 varchar2(30);
962 l_str7 varchar2(1000);
963 l_str8 varchar2(240);
964 l_str9 varchar2(240);
965 BEGIN
966 hr_utility.set_location('Entered Procedure Write to clob ',100);
967 l_str1 := '<?xml version="1.0" encoding="UTF-8"?>
968 <xfdf xmlns="http://ns.adobe.com/xfdf/" xml:space="preserve">
969 <fields> ' ;
970 l_str2 := '<field name="';
971 l_str3 := '">';
972 l_str4 := '<value>' ;
973 l_str5 := '</value> </field>' ;
974 l_str6 := '</fields> </xfdf>';
975 l_str7 := '<?xml version="1.0" encoding="UTF-8"?>
976 <xfdf xmlns="http://ns.adobe.com/xfdf/" xml:space="preserve">
977 <fields>
978 </fields> </xfdf>';
979 dbms_lob.createtemporary(l_xfdf_string,FALSE,DBMS_LOB.CALL);
980 dbms_lob.open(l_xfdf_string,dbms_lob.lob_readwrite);
981 if gxmltable.count > 0 then
982 dbms_lob.writeAppend( l_xfdf_string, length(l_str1), l_str1 );
983 FOR ctr_table IN gxmltable.FIRST .. gxmltable.LAST LOOP
984 l_str8 := gxmltable(ctr_table).tagName;
985 l_str9 := gxmltable(ctr_table).tagValue;
986 IF gxmltable(ctr_table).tagName IN ('count_body_value', 'count_value') THEN
987 l_str9 := p_tot_pg_count;
988 END IF;
989 IF (l_str9 is not null) THEN
990 dbms_lob.writeAppend( l_xfdf_string, length(l_str2), l_str2 );
991 dbms_lob.writeAppend( l_xfdf_string, length(l_str8),l_str8);
992 dbms_lob.writeAppend( l_xfdf_string, length(l_str3), l_str3 );
993 dbms_lob.writeAppend( l_xfdf_string, length(l_str4), l_str4 );
994 dbms_lob.writeAppend( l_xfdf_string, length(l_str9), l_str9);
995 dbms_lob.writeAppend( l_xfdf_string, length(l_str5), l_str5 );
996 ELSIF (l_str9 IS NULL AND l_str8 IS NOT NULL) THEN
997 dbms_lob.writeAppend(l_xfdf_string,length(l_str2),l_str2);
998 dbms_lob.writeAppend(l_xfdf_string,length(l_str8),l_str8);
999 dbms_lob.writeAppend(l_xfdf_string,length(l_str3),l_str3);
1000 dbms_lob.writeAppend(l_xfdf_string,length(l_str4),l_str4);
1001 dbms_lob.writeAppend(l_xfdf_string,length(l_str5),l_str5);
1002 ELSE
1003 NULL;
1004 END IF;
1005 END LOOP;
1006 dbms_lob.writeAppend( l_xfdf_string, length(l_str6), l_str6 );
1007 ELSE
1008 dbms_lob.writeAppend( l_xfdf_string, length(l_str7), l_str7 );
1009 END IF;
1010 DBMS_LOB.CREATETEMPORARY(p_xfdf_blob,TRUE);
1011 clob_to_blob(l_xfdf_string,p_xfdf_blob);
1012 hr_utility.set_location('Finished Procedure Write to CLOB ,Before clob to blob ',110);
1013 EXCEPTION
1014 WHEN OTHERS then
1015 HR_UTILITY.TRACE('sqleerm ' || SQLERRM);
1016 HR_UTILITY.RAISE_ERROR;
1017 END Writetoclob;
1018 ----------------------------------------------------------------
1019 Procedure clob_to_blob(p_clob clob,
1020 p_blob IN OUT NOCOPY Blob)
1021 is
1022 l_length_clob number;
1023 l_offset pls_integer;
1024 l_varchar_buffer varchar2(32767);
1025 l_raw_buffer raw(32767);
1026 l_buffer_len number:= 20000;
1027 l_chunk_len number;
1028 l_blob blob;
1029 g_nls_db_char varchar2(60);
1030
1031 l_raw_buffer_len pls_integer;
1032 l_blob_offset pls_integer := 1;
1033
1034 begin
1035 hr_utility.set_location('Entered Procedure clob to blob',120);
1036 select userenv('LANGUAGE') into g_nls_db_char from dual;
1037 l_length_clob := dbms_lob.getlength(p_clob);
1038 l_offset := 1;
1039 while l_length_clob > 0 loop
1040 hr_utility.trace('l_length_clob '|| l_length_clob);
1041 if l_length_clob < l_buffer_len then
1042 l_chunk_len := l_length_clob;
1043 else
1044 l_chunk_len := l_buffer_len;
1045 end if;
1046 DBMS_LOB.READ(p_clob,l_chunk_len,l_offset,l_varchar_buffer);
1047 --l_raw_buffer := utl_raw.cast_to_raw(l_varchar_buffer);
1048 l_raw_buffer := utl_raw.convert(utl_raw.cast_to_raw(l_varchar_buffer),'American_America.UTF8',g_nls_db_char);
1049 l_raw_buffer_len := utl_raw.length(utl_raw.convert(utl_raw.cast_to_raw(l_varchar_buffer),'American_America.UTF8',g_nls_db_char));
1050 hr_utility.trace('l_varchar_buffer '|| l_varchar_buffer);
1051 --dbms_lob.write(p_blob,l_chunk_len, l_offset, l_raw_buffer);
1052 dbms_lob.write(p_blob,l_raw_buffer_len, l_blob_offset, l_raw_buffer);
1053 l_blob_offset := l_blob_offset + l_raw_buffer_len;
1054
1055 l_offset := l_offset + l_chunk_len;
1056 l_length_clob := l_length_clob - l_chunk_len;
1057 hr_utility.trace('l_length_blob '|| dbms_lob.getlength(p_blob));
1058 end loop;
1059 hr_utility.set_location('Finished Procedure clob to blob ',130);
1060 end;
1061
1062 ------------------------------------------------------------------
1063 PROCEDURE fetch_pdf_blob
1064 (p_report IN VARCHAR2
1065 ,P_date IN VARCHAR2
1066 ,p_pdf_blob OUT NOCOPY blob) IS
1067 BEGIN
1068 IF (p_report='Disability') THEN
1069 SELECT file_data
1070 INTO p_pdf_blob
1071 FROM fnd_lobs
1072 WHERE file_id = (SELECT MAX(file_id)
1073 FROM fnd_lobs
1074 WHERE file_name like '%PER_DIS_ar_KW.pdf');
1075 ELSE
1076 SELECT file_data
1077 INTO p_pdf_blob
1078 FROM fnd_lobs
1079 WHERE file_id = (SELECT MAX(file_id)
1080 FROM fnd_lobs
1081 WHERE file_name like '%PER_CTR_ar_KW.pdf');
1082 END IF;
1083 EXCEPTION
1084 WHEN NO_DATA_FOUND THEN
1085 NULL;
1086 END fetch_pdf_blob;
1087 -----------------------------------------------------------------
1088 FUNCTION get_lookup_meaning
1089 (p_lookup_type varchar2
1090 ,p_lookup_code varchar2)
1091 RETURN VARCHAR2 IS
1092 CURSOR csr_lookup IS
1093 select meaning
1094 from hr_lookups
1095 where lookup_type = p_lookup_type
1096 and lookup_code = p_lookup_code;
1097 l_meaning hr_lookups.meaning%type;
1098 BEGIN
1099 OPEN csr_lookup;
1100 FETCH csr_lookup INTO l_Meaning;
1101 CLOSE csr_lookup;
1102 RETURN l_meaning;
1103 END get_lookup_meaning;
1104 END per_kw_xdo_report;