[Home] [Help]
PACKAGE BODY: APPS.PER_AE_XDO_REPORT
Source
1 PACKAGE BODY per_ae_xdo_report AS
2 /* $Header: peaexdor.pkb 120.9.12010000.5 2008/08/06 08:53:56 ubhat ship $ */
3 PROCEDURE get_visa_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_expires_in IN NUMBER
11 ,p_units IN VARCHAR2
12 ,l_xfdf_blob OUT NOCOPY BLOB) AS
13 l_parent_id NUMBER;
14 l_date DATE;
15 l_business_group_id NUMBER;
16 l_organization_id NUMBER;
17 CURSOR csr_get_bg_id IS
18 SELECT business_group_id
19 FROM per_business_groups
20 WHERE business_group_id = nvl(p_business_group_id, business_group_id)
21 AND legislation_code = 'AE';
22 rec_get_bg_id csr_get_bg_id%ROWTYPE;
23 CURSOR csr_get_org_id(c_business_group_id number) IS
24 SELECT org.organization_id
25 ,org.name
26 FROM hr_all_organization_units org
27 WHERE org.organization_id IN (SELECT pose.organization_id_child
28 FROM per_org_structure_elements pose
29 CONNECT BY pose.organization_id_parent = PRIOR pose.organization_id_child
30 AND pose.org_structure_version_id = p_org_structure_version_id
31 START WITH pose.organization_id_parent = nvl(c_business_group_id, l_parent_id)
32 AND pose.org_structure_version_id = p_org_structure_version_id
33 UNION
34 SELECT nvl(p_business_group_id, l_parent_id)
35 FROM DUAL)
36 AND p_org_structure_version_id IS NOT NULL
37 UNION
38 SELECT org.organization_id
39 ,org.name
40 FROM hr_all_organization_units org
41 WHERE org.organization_id = NVL(c_business_group_id,org.organization_id)
42 AND org.business_group_id = c_business_group_id
43 AND p_org_structure_version_id IS NULL;
44 rec_get_org_id csr_get_org_id%ROWTYPE;
45 CURSOR csr_get_visa_det(c_business_group_id number, c_organization_id number) IS
46 SELECT org.name
47 ,people.employee_number
48 ,people.full_name
49 ,dei.dei_information1 visa_number
50 ,dei.dei_information9 place_of_issue
51 ,dei.date_to expiry_date
52 ,assg.assignment_id assignment_id
53 FROM per_all_assignments_f assg
54 ,per_all_people_f people
55 ,hr_document_extra_info dei
56 ,hr_document_types_tl hdtl
57 ,hr_all_organization_units org
58 WHERE assg.person_id = people.person_id
59 AND (l_date) BETWEEN assg.effective_start_date
60 AND assg.effective_end_date
61 AND (l_date) BETWEEN people.effective_start_date
62 AND people.effective_end_date
63 AND dei.person_id = people.person_id
64 AND dei.document_type_id = hdtl.document_type_id
65 AND hdtl.document_type = 'AE_VISA'
66 AND hdtl.language = 'US'
67 AND dei.dei_information_category = hdtl.document_type
68 AND assg.organization_id = org.organization_id
69 AND org.organization_id = c_organization_id
70 AND org.business_group_id = c_business_group_id
71 --AND org.organization_id = p_business_group_id
72 AND dei.date_to
73 BETWEEN NVL(l_date,sysdate) AND
74 DECODE(p_units,'D',(NVL(l_date,sysdate)+p_expires_in),
75 'W',(NVL(l_date,sysdate)+(p_expires_in*7)),
76 'M',(add_months(NVL(l_date,sysdate),p_expires_in)),
77 'Y',(add_months(NVL(l_date,sysdate),(p_expires_in*12))))
78 ORDER BY org.name, expiry_date, full_name;
79 CURSOR csr_get_job(p_assignment_id NUMBER) IS
80 SELECT pjb.name
81 FROM per_all_assignments_f paa,
82 per_jobs pjb
83 WHERE paa.assignment_id = p_assignment_id
84 AND paa.job_id = pjb.job_id;
85 rec_get_visa_det csr_get_visa_det%ROWTYPE;
86 l_org_name VARCHAR2(80);
87 l_structure_name VARCHAR2(80);
88 l_job VARCHAR2(80);
89 l_version NUMBER;
90 l_emp_found NUMBER;
91 i NUMBER;
92 j NUMBER;
93 l_pg_count NUMBER;
94 l_units VARCHAR2(80);
95 BEGIN
96 l_pg_count := 1;
97 IF p_date IS NOT NULL THEN
98 BEGIN
99 SELECT fnd_date.canonical_to_date(p_date)
100 INTO l_date
101 FROM DUAL;
102 EXCEPTION
103 WHEN OTHERS THEN
104 l_date := TRUNC(sysdate);
105 END;
106 ELSE
107 l_date := TRUNC(sysdate);
108 END IF;
109 INSERT INTO fnd_sessions (session_id, effective_date)
110 VALUES (userenv('SESSIONID'), l_date);
111 l_parent_id := NULL;
112 l_org_name := NULL;
113 l_structure_name := NULL;
114 l_version := NULL;
115 l_emp_found := 0;
116 i := 1;
117 j := 0;
118 gxmltable.DELETE;
119 gCtr := 1;
120 IF p_org_structure_version_id IS NOT NULL
121 AND 1 IS NULL THEN
122 BEGIN
123 SELECT distinct pose.organization_id_parent
124 INTO l_parent_id
125 FROM per_org_structure_elements pose
126 WHERE pose.org_structure_version_id = p_org_structure_version_id
127 AND pose.organization_id_parent NOT IN (SELECT pose1.organization_id_child
128 FROM per_org_structure_elements pose1
129 WHERE pose1.org_structure_version_id
130 = p_org_structure_version_id);
131 EXCEPTION
132 WHEN OTHERS THEN
133 l_parent_id := NULL;
134 END;
135 END IF;
136 IF p_business_group_id IS NOT NULL THEN
137 BEGIN
138 SELECT name
139 INTO l_org_name
140 FROM hr_organization_units
141 WHERE organization_id = p_business_group_id;
142 EXCEPTION
143 WHEN OTHERS THEN
144 NULL;
145 END;
146 END IF;
147 IF p_org_structure_id IS NOT NULL THEN
148 BEGIN
149 SELECT name
150 INTO l_structure_name
151 FROM per_organization_structures
152 WHERE organization_structure_id = p_org_structure_id;
153 EXCEPTION
154 WHEN OTHERS THEN
155 NULL;
156 END;
157 END IF;
158 IF p_org_structure_version_id IS NOT NULL THEN
159 BEGIN
160 SELECT version_number
161 INTO l_version
162 FROM per_org_structure_versions
163 WHERE org_structure_version_id = p_org_structure_version_id;
164 EXCEPTION
165 WHEN OTHERS THEN
166 NULL;
167 END;
168 END IF;
169 IF p_units IS NOT NULL THEN
170 BEGIN
171 SELECT meaning
172 INTO l_units
173 FROM hr_lookups
174 WHERE lookup_type = 'QUALIFYING_UNITS'
175 AND lookup_code = p_units;
176 EXCEPTION
177 WHEN OTHERS THEN
178 NULL;
179 END;
180 END IF;
181 --Populate parameter labels and values
182 gxmltable(gCtr).tagName := 'report_date_label';
183 gxmltable(gCtr).tagValue := get_lookup_meaning('AE_FORM_LABELS','REPORT_DATE_LABEL');
184 gctr := gctr + 1;
185 gxmltable(gCtr).tagName := 'page_number_label';
186 gxmltable(gCtr).tagValue := get_lookup_meaning('AE_FORM_LABELS','PAGE_NO_LABEL');
187 gctr := gctr + 1;
188 gxmltable(gCtr).tagName := 'report_date_value';
189 gxmltable(gCtr).tagValue := fnd_date.date_to_displaydate(sysdate);
190 gctr := gctr + 1;
191 gxmltable(gCtr).tagName := 'of_label';
192 gxmltable(gCtr).tagValue := get_lookup_meaning('AE_FORM_LABELS','OF_LABEL');
193 gctr := gctr + 1;
194 gxmltable(gCtr).tagName := 'one_value';
195 gxmltable(gCtr).tagValue := 1;
196 gctr := gctr + 1;
197 gxmltable(gCtr).tagName := 'count_value';
198 gxmltable(gCtr).tagValue := null;
199 gctr := gctr + 1;
200 gxmltable(gCtr).tagName := 'report_parameters_label';
201 gxmltable(gCtr).tagValue := get_lookup_meaning('AE_FORM_LABELS','REPORT_PARAMETERS_LABEL_VISA');
202 gctr := gctr + 1;
203 gxmltable(gCtr).tagName := 'organization_name_label';
204 gxmltable(gCtr).tagValue := get_lookup_meaning('AE_FORM_LABELS','ORGANIZATION_NAME_LABEL');
205 gctr := gctr + 1;
206 gxmltable(gCtr).tagName := 'organization_name_value';
207 gxmltable(gCtr).tagValue := l_org_name;
208 gctr := gctr + 1;
209 gxmltable(gCtr).tagName := 'organization_hierarchy_label';
210 gxmltable(gCtr).tagValue := get_lookup_meaning('AE_FORM_LABELS','ORGANIZATION_HIERARCHY_LABEL');
211 gctr := gctr + 1;
212 gxmltable(gCtr).tagName := 'organization_hierarchy_value';
213 gxmltable(gCtr).tagValue := l_structure_name;
214 gctr := gctr + 1;
215 gxmltable(gCtr).tagName := 'organization_version_label';
216 gxmltable(gCtr).tagValue := get_lookup_meaning('AE_FORM_LABELS','ORGANIZATION_VERSION_LABEL');
217 gctr := gctr + 1;
218 gxmltable(gCtr).tagName := 'organization_version_value';
219 gxmltable(gCtr).tagValue := l_version;
220 gctr := gctr + 1;
221 gxmltable(gCtr).tagName := 'duration_label';
222 gxmltable(gCtr).tagValue := get_lookup_meaning('AE_FORM_LABELS','DURATION_LABEL');
223 gctr := gctr + 1;
224 gxmltable(gCtr).tagName := 'duration_value';
225 gxmltable(gCtr).tagValue := p_expires_in;
226 gctr := gctr + 1;
227 gxmltable(gCtr).tagName := 'duration_units_label';
228 gxmltable(gCtr).tagValue := get_lookup_meaning('AE_FORM_LABELS','DURATION_UNITS_LABEL');
229 gctr := gctr + 1;
230 gxmltable(gCtr).tagName := 'duration_units_value';
231 gxmltable(gCtr).tagValue := l_units;
232 gctr := gctr + 1;
233 gxmltable(gCtr).tagName := 'effective_date_label';
234 gxmltable(gCtr).tagValue := get_lookup_meaning('AE_FORM_LABELS','EFFECTIVE_DATE_LABEL');
235 gctr := gctr + 1;
236 gxmltable(gCtr).tagName := 'effective_date_value';
237 gxmltable(gCtr).tagValue := fnd_date.date_to_displaydate(l_date);
238 gctr := gctr + 1;
239 gxmltable(gCtr).tagName := '0';
240 gxmltable(gCtr).tagValue := ('-----------------------------------------------------');
241 gctr := gctr + 1;
242 OPEN csr_get_bg_id;
243 LOOP
244 FETCH csr_get_bg_id INTO rec_get_bg_id;
245 EXIT WHEN csr_get_bg_id%NOTFOUND;
246 l_business_group_id := rec_get_bg_id.business_group_id;
247 OPEN csr_get_org_id(l_business_group_id);
248 LOOP
249 FETCH csr_get_org_id INTO rec_get_org_id;
250 EXIT WHEN csr_get_org_id%NOTFOUND;
251 l_organization_id:= rec_get_org_id.organization_id;
252 l_emp_found := 0;
253 i := 1;
254 OPEN csr_get_visa_det(l_business_group_id, l_organization_id);
255 LOOP
256 FETCH csr_get_visa_det INTO rec_get_visa_det;
257 EXIT WHEN csr_get_visa_det%NOTFOUND;
258 IF i = 19 THEN
259 i := 1;
260 END IF;
261 IF l_emp_found = 0 OR i = 1 THEN
262 l_pg_count := l_pg_count + 1;
263 gxmltable(gCtr).tagName := 'report_label';
264 gxmltable(gCtr).tagValue := get_lookup_meaning('AE_FORM_LABELS','REPORT_LABEL_VISA');
265 gctr := gctr + 1;
266 gxmltable(gCtr).tagName := 'report_date_body_label';
267 gxmltable(gCtr).tagValue := get_lookup_meaning('AE_FORM_LABELS','REPORT_DATE_LABEL');
268 gctr := gctr + 1;
269 gxmltable(gCtr).tagName := 'report_date_body_value';
270 gxmltable(gCtr).tagValue := fnd_date.date_to_displaydate(sysdate);
271 gctr := gctr + 1;
272 gxmltable(gCtr).tagName := 'page_number_body_label';
273 gxmltable(gCtr).tagValue := get_lookup_meaning('AE_FORM_LABELS','PAGE_NO_LABEL');
274 gctr := gctr + 1;
275 gxmltable(gCtr).tagName := 'of_body_label';
276 gxmltable(gCtr).tagValue := get_lookup_meaning('AE_FORM_LABELS','OF_LABEL');
277 gctr := gctr + 1;
278 gxmltable(gCtr).tagName := 'current_value';
279 gxmltable(gCtr).tagValue := l_pg_count;
280 gctr := gctr + 1;
281 gxmltable(gCtr).tagName := 'count_body_value';
282 gxmltable(gCtr).tagValue := null;
283 gctr := gctr + 1;
284 gxmltable(gCtr).tagName := 'organization_label';
285 gxmltable(gCtr).tagValue := get_lookup_meaning('AE_FORM_LABELS','ORGANIZATION_LABEL');
286 gctr := gctr + 1;
287 gxmltable(gCtr).tagName := 'organization_value';
288 gxmltable(gCtr).tagValue := rec_get_org_id.name;
289 gctr := gctr + 1;
290 gxmltable(gCtr).tagName := 'employee_number_label';
291 gxmltable(gCtr).tagValue := get_lookup_meaning('AE_FORM_LABELS','EMPLOYEE_NUMBER_LABEL');
292 gctr := gctr + 1;
293 gxmltable(gCtr).tagName := 'full_name_label';
294 gxmltable(gCtr).tagValue := get_lookup_meaning('AE_FORM_LABELS','FULL_NAME_LABEL');
295 gctr := gctr + 1;
296 gxmltable(gCtr).tagName := 'job_label';
297 gxmltable(gCtr).tagValue := get_lookup_meaning('AE_FORM_LABELS','JOB_LABEL');
298 gctr := gctr + 1;
299 gxmltable(gCtr).tagName := 'visa_number_body_label';
300 gxmltable(gCtr).tagValue := get_lookup_meaning('AE_FORM_LABELS','VISA_NUMBER_BODY_LABEL');
301 gctr := gctr + 1;
302 gxmltable(gCtr).tagName := 'visa_place_of_issue_body_label';
303 gxmltable(gCtr).tagValue := get_lookup_meaning('AE_FORM_LABELS','PLACE_OF_ISSUE_LABEL');
304 gctr := gctr + 1;
305 gxmltable(gCtr).tagName := 'expiry_date_label';
306 gxmltable(gCtr).tagValue := get_lookup_meaning('AE_FORM_LABELS','EXPIRY_DATE');
307 gctr := gctr + 1;
308 gxmltable(gCtr).tagName := '1';
309 gxmltable(gCtr).tagValue := ('--------------------');
310 gctr := gctr + 1;
311 gxmltable(gCtr).tagName := '2';
312 gxmltable(gCtr).tagValue := ('------------------------------------------');
313 gctr := gctr + 1;
314 gxmltable(gCtr).tagName := '3';
315 gxmltable(gCtr).tagValue := ('--------------------');
316 gctr := gctr + 1;
317 gxmltable(gCtr).tagName := '4';
318 gxmltable(gCtr).tagValue := ('--------------------');
319 gctr := gctr + 1;
320 gxmltable(gCtr).tagName := '5';
321 gxmltable(gCtr).tagValue := ('---------------------');
322 gctr := gctr + 1;
323 gxmltable(gCtr).tagName := '6';
324 gxmltable(gCtr).tagValue := ('---------------------');
325 gctr := gctr + 1;
326 l_emp_found := 1;
327 END IF;
328 OPEN csr_get_job(rec_get_visa_det.assignment_id);
329 FETCH csr_get_job INTO l_job;
330 CLOSE csr_get_job;
331 gxmltable(gCtr).tagName := 'employee_number_value'||' '||i;
332 gxmltable(gCtr).tagValue := (rec_get_visa_det.employee_number);
333 gctr := gctr + 1;
334 gxmltable(gCtr).tagName := 'full_name_value'||' '||i;
335 gxmltable(gCtr).tagValue := (SUBSTR(rec_get_visa_det.full_name,1,80));
336 gctr := gctr + 1;
337 gxmltable(gCtr).tagName := 'job_value'||' '||i;
338 gxmltable(gCtr).tagValue := (l_job);
339 gctr := gctr + 1;
340 gxmltable(gCtr).tagName := 'visa_number_body_value'||' '||i;
341 gxmltable(gCtr).tagValue := (rec_get_visa_det.visa_number);
342 gctr := gctr + 1;
343 gxmltable(gCtr).tagName := 'visa_place_of_issue_body_value'||' '||i;
344 gxmltable(gCtr).tagValue := (get_lookup_meaning('AE_EMIRATE',rec_get_visa_det.place_of_issue));
345 gctr := gctr + 1;
346 gxmltable(gCtr).tagName := 'expiry_date_value'||' '||i;
347 gxmltable(gCtr).tagValue := (rec_get_visa_det.expiry_date);
348 gctr := gctr + 1;
349 i := i + 1;
350 END LOOP;
351 CLOSE csr_get_visa_det;
352 IF i < 18 AND l_emp_found = 1 THEN
353 FOR j in i..18 LOOP
354 gxmltable(gCtr).tagName := 'employee_number_value'||' '||j;
355 gxmltable(gCtr).tagValue := (null);
356 gctr := gctr + 1;
357 gxmltable(gCtr).tagName := 'full_name_value'||' '||j;
358 gxmltable(gCtr).tagValue := (null);
359 gctr := gctr + 1;
360 gxmltable(gCtr).tagName := 'job_value'||' '||j;
361 gxmltable(gCtr).tagValue := (null);
362 gctr := gctr + 1;
363 gxmltable(gCtr).tagName := 'visa_number_body_value'||' '||j;
364 gxmltable(gCtr).tagValue := (null);
365 gctr := gctr + 1;
366 gxmltable(gCtr).tagName := 'visa_place_of_issue_body_value'||' '||j;
367 gxmltable(gCtr).tagValue := (null);
368 gctr := gctr + 1;
369 gxmltable(gCtr).tagName := 'expiry_date_value'||' '||j;
370 gxmltable(gCtr).tagValue := (null);
371 gctr := gctr + 1;
372 END LOOP;
373 END IF;
374 END LOOP;
375 CLOSE csr_get_org_id;
376 END LOOP;
377 CLOSE csr_get_bg_id;
378 WritetoCLOB ( l_xfdf_blob, l_pg_count );
379 END get_visa_data;
380 --------------------------------------------------------------------------------------------------------
381 PROCEDURE get_passport_data
382 (p_request_id IN NUMBER
383 ,p_report_name IN VARCHAR2
384 ,p_date IN VARCHAR2 DEFAULT NULL
385 ,p_business_group_id IN NUMBER DEFAULT NULL
386 ,p_org_structure_id IN NUMBER DEFAULT NULL
387 ,p_org_structure_version_id IN NUMBER DEFAULT NULL
388 ,p_expires_in IN NUMBER
389 ,p_units IN VARCHAR2
390 ,l_xfdf_blob OUT NOCOPY BLOB) AS
391 l_parent_id NUMBER;
392 l_date DATE;
393 l_business_group_id NUMBER;
394 l_organization_id NUMBER;
395 CURSOR csr_get_bg_id IS
396 SELECT business_group_id
397 FROM per_business_groups
398 WHERE business_group_id = nvl(p_business_group_id, business_group_id)
399 AND legislation_code = 'AE';
400 rec_get_bg_id csr_get_bg_id%ROWTYPE;
401 CURSOR csr_get_org_id(c_business_group_id number) IS
402 SELECT org.organization_id
403 ,org.name
404 FROM hr_all_organization_units org
405 WHERE org.organization_id IN (SELECT pose.organization_id_child
406 FROM per_org_structure_elements pose
407 CONNECT BY pose.organization_id_parent = PRIOR pose.organization_id_child
408 AND pose.org_structure_version_id = p_org_structure_version_id
409 START WITH pose.organization_id_parent = nvl(c_business_group_id, l_parent_id)
410 AND pose.org_structure_version_id = p_org_structure_version_id
411 UNION
412 SELECT nvl(c_business_group_id, l_parent_id)
413 FROM DUAL)
414 AND p_org_structure_version_id IS NOT NULL
415 UNION
416 SELECT org.organization_id
417 ,org.name
418 FROM hr_all_organization_units org
419 WHERE org.organization_id = NVL(c_business_group_id,org.organization_id)
420 AND org.business_group_id = c_business_group_id
421 AND p_org_structure_version_id IS NULL;
422 rec_get_org_id csr_get_org_id%ROWTYPE;
423 CURSOR csr_get_pass_det(c_business_group_id number, c_organization_id number) IS
424 SELECT org.name
425 ,people.employee_number
426 ,people.full_name
427 ,dei.dei_information1 passport_number
428 ,dei.issued_at place_of_issue
429 ,dei.date_to expiry_date
430 ,assg.assignment_id assignment_id
431 FROM per_all_assignments_f assg
432 ,per_all_people_f people
433 ,hr_document_extra_info dei
434 ,hr_document_types_tl hdtl
435 ,hr_all_organization_units org
436 WHERE assg.person_id = people.person_id
437 AND (l_date) BETWEEN assg.effective_start_date
438 AND assg.effective_end_date
439 AND (l_date) BETWEEN people.effective_start_date
440 AND people.effective_end_date
441 AND dei.person_id = people.person_id
442 AND dei.document_type_id = hdtl.document_type_id
443 AND hdtl.document_type = 'AE_PASSPORT'
444 AND hdtl.language = 'US'
445 AND dei.dei_information_category = hdtl.document_type
446 AND assg.organization_id = org.organization_id
447 AND org.organization_id = c_organization_id
448 AND org.business_group_id = c_business_group_id
449 --AND org.organization_id = p_business_group_id
450 AND dei.date_to
451 BETWEEN NVL(l_date,sysdate) AND
452 DECODE(p_units,'D',(NVL(l_date,sysdate)+p_expires_in),
453 'W',(NVL(l_date,sysdate)+(p_expires_in*7)),
454 'M',(add_months(NVL(l_date,sysdate),p_expires_in)),
455 'Y',(add_months(NVL(l_date,sysdate),(p_expires_in*12))))
456 ORDER BY org.name, expiry_date, full_name;
457 CURSOR csr_get_job(p_assignment_id NUMBER) IS
458 SELECT pjb.name
459 FROM per_all_assignments_f paa,
460 per_jobs pjb
461 WHERE paa.assignment_id = p_assignment_id
462 AND paa.job_id = pjb.job_id;
463 rec_get_pass_det csr_get_pass_det%ROWTYPE;
464 l_org_name VARCHAR2(80);
465 l_structure_name VARCHAR2(80);
466 l_job VARCHAR2(80);
467 l_version NUMBER;
468 l_emp_found NUMBER;
469 i NUMBER;
470 j NUMBER;
471 l_pg_count NUMBER;
472 l_units VARCHAR2(80);
473 BEGIN
474 l_pg_count := 1;
475 IF p_date IS NOT NULL THEN
476 BEGIN
477 SELECT fnd_date.canonical_to_date(p_date)
478 INTO l_date
479 FROM DUAL;
480 EXCEPTION
481 WHEN OTHERS THEN
482 l_date := TRUNC(sysdate);
483 END;
484 ELSE
485 l_date := TRUNC(sysdate);
486 END IF;
487 INSERT INTO fnd_sessions (session_id, effective_date)
488 VALUES (userenv('SESSIONID'), l_date);
489 l_parent_id := NULL;
490 l_org_name := NULL;
491 l_structure_name := NULL;
492 l_version := NULL;
493 l_emp_found := 0;
494 i := 1;
495 j := 0;
496 gxmltable.DELETE;
497 gCtr := 1;
498 IF p_org_structure_version_id IS NOT NULL
499 AND 1 IS NULL THEN
500 BEGIN
501 SELECT distinct pose.organization_id_parent
502 INTO l_parent_id
503 FROM per_org_structure_elements pose
504 WHERE pose.org_structure_version_id = p_org_structure_version_id
505 AND pose.organization_id_parent NOT IN (SELECT pose1.organization_id_child
506 FROM per_org_structure_elements pose1
507 WHERE pose1.org_structure_version_id
508 = p_org_structure_version_id);
509 EXCEPTION
510 WHEN OTHERS THEN
511 l_parent_id := NULL;
512 END;
513 END IF;
514 IF p_business_group_id IS NOT NULL THEN
515 BEGIN
516 SELECT name
517 INTO l_org_name
518 FROM hr_organization_units
519 WHERE organization_id = p_business_group_id;
520 EXCEPTION
521 WHEN OTHERS THEN
522 NULL;
523 END;
524 END IF;
525 IF p_org_structure_id IS NOT NULL THEN
526 BEGIN
527 SELECT name
528 INTO l_structure_name
529 FROM per_organization_structures
530 WHERE organization_structure_id = p_org_structure_id;
531 EXCEPTION
532 WHEN OTHERS THEN
533 NULL;
534 END;
535 END IF;
536 IF p_org_structure_version_id IS NOT NULL THEN
537 BEGIN
538 SELECT version_number
539 INTO l_version
540 FROM per_org_structure_versions
541 WHERE org_structure_version_id = p_org_structure_version_id;
542 EXCEPTION
543 WHEN OTHERS THEN
544 NULL;
545 END;
546 END IF;
547 IF p_units IS NOT NULL THEN
548 BEGIN
549 SELECT meaning
550 INTO l_units
551 FROM hr_lookups
552 WHERE lookup_type = 'QUALIFYING_UNITS'
553 AND lookup_code = p_units;
554 EXCEPTION
555 WHEN OTHERS THEN
556 NULL;
557 END;
558 END IF;
559 --Populate parameter labels and values
560 gxmltable(gCtr).tagName := 'report_date_label';
561 gxmltable(gCtr).tagValue := get_lookup_meaning('AE_FORM_LABELS','REPORT_DATE_LABEL');
562 gctr := gctr + 1;
563 gxmltable(gCtr).tagName := 'page_number_label';
564 gxmltable(gCtr).tagValue := get_lookup_meaning('AE_FORM_LABELS','PAGE_NO_LABEL');
565 gctr := gctr + 1;
566 gxmltable(gCtr).tagName := 'report_date_value';
567 gxmltable(gCtr).tagValue := fnd_date.date_to_displaydate(sysdate);
568 gctr := gctr + 1;
569 gxmltable(gCtr).tagName := 'of_label';
570 gxmltable(gCtr).tagValue := get_lookup_meaning('AE_FORM_LABELS','OF_LABEL');
571 gctr := gctr + 1;
572 gxmltable(gCtr).tagName := 'one_value';
573 gxmltable(gCtr).tagValue := 1;
574 gctr := gctr + 1;
575 gxmltable(gCtr).tagName := 'count_value';
576 gxmltable(gCtr).tagValue := null;
577 gctr := gctr + 1;
578 gxmltable(gCtr).tagName := 'report_parameters_label';
579 gxmltable(gCtr).tagValue := get_lookup_meaning('AE_FORM_LABELS','REPORT_PARAMETERS_LABEL_PASS');
580 gctr := gctr + 1;
581 gxmltable(gCtr).tagName := 'organization_name_label';
582 gxmltable(gCtr).tagValue := get_lookup_meaning('AE_FORM_LABELS','ORGANIZATION_NAME_LABEL');
583 gctr := gctr + 1;
584 gxmltable(gCtr).tagName := 'organization_name_value';
585 gxmltable(gCtr).tagValue := l_org_name;
586 gctr := gctr + 1;
587 gxmltable(gCtr).tagName := 'organization_hierarchy_label';
588 gxmltable(gCtr).tagValue := get_lookup_meaning('AE_FORM_LABELS','ORGANIZATION_HIERARCHY_LABEL');
589 gctr := gctr + 1;
590 gxmltable(gCtr).tagName := 'organization_hierarchy_value';
591 gxmltable(gCtr).tagValue := l_structure_name;
592 gctr := gctr + 1;
593 gxmltable(gCtr).tagName := 'organization_version_label';
594 gxmltable(gCtr).tagValue := get_lookup_meaning('AE_FORM_LABELS','ORGANIZATION_VERSION_LABEL');
595 gctr := gctr + 1;
596 gxmltable(gCtr).tagName := 'organization_version_value';
597 gxmltable(gCtr).tagValue := l_version;
598 gctr := gctr + 1;
599 gxmltable(gCtr).tagName := 'duration_label';
600 gxmltable(gCtr).tagValue := get_lookup_meaning('AE_FORM_LABELS','DURATION_LABEL');
601 gctr := gctr + 1;
602 gxmltable(gCtr).tagName := 'duration_value';
603 gxmltable(gCtr).tagValue := p_expires_in;
604 gctr := gctr + 1;
605 gxmltable(gCtr).tagName := 'duration_units_label';
606 gxmltable(gCtr).tagValue := get_lookup_meaning('AE_FORM_LABELS','DURATION_UNITS_LABEL');
607 gctr := gctr + 1;
608 gxmltable(gCtr).tagName := 'duration_units_value';
609 gxmltable(gCtr).tagValue := l_units;
610 gctr := gctr + 1;
611 gxmltable(gCtr).tagName := 'effective_date_label';
612 gxmltable(gCtr).tagValue := get_lookup_meaning('AE_FORM_LABELS','EFFECTIVE_DATE_LABEL');
613 gctr := gctr + 1;
614 gxmltable(gCtr).tagName := 'effective_date_value';
615 gxmltable(gCtr).tagValue := fnd_date.date_to_displaydate(l_date);
616 gctr := gctr + 1;
617 gxmltable(gCtr).tagName := '0';
618 gxmltable(gCtr).tagValue := ('-----------------------------------------------------');
619 gctr := gctr + 1;
620 OPEN csr_get_bg_id;
621 LOOP
622 FETCH csr_get_bg_id INTO rec_get_bg_id;
623 EXIT WHEN csr_get_bg_id%NOTFOUND;
624 l_business_group_id := rec_get_bg_id.business_group_id;
625 OPEN csr_get_org_id(l_business_group_id);
626 LOOP
627 FETCH csr_get_org_id INTO rec_get_org_id;
628 EXIT WHEN csr_get_org_id%NOTFOUND;
629 l_organization_id := rec_get_org_id.organization_id;
630 l_emp_found := 0;
631 i := 1;
632 OPEN csr_get_pass_det(l_business_group_id, l_organization_id);
633 LOOP
634 FETCH csr_get_pass_det INTO rec_get_pass_det;
635 EXIT WHEN csr_get_pass_det%NOTFOUND;
636 IF i = 19 THEN
637 i := 1;
638 END IF;
639 IF l_emp_found = 0 OR i = 1 THEN
640 l_pg_count := l_pg_count + 1;
641 gxmltable(gCtr).tagName := 'report_label';
642 gxmltable(gCtr).tagValue := get_lookup_meaning('AE_FORM_LABELS','REPORT_LABEL_PASS');
643 gctr := gctr + 1;
644 gxmltable(gCtr).tagName := 'report_date_body_label';
645 gxmltable(gCtr).tagValue := get_lookup_meaning('AE_FORM_LABELS','REPORT_DATE_LABEL');
646 gctr := gctr + 1;
647 gxmltable(gCtr).tagName := 'report_date_body_value';
648 gxmltable(gCtr).tagValue := fnd_date.date_to_displaydate(sysdate);
649 gctr := gctr + 1;
650 gxmltable(gCtr).tagName := 'page_number_body_label';
651 gxmltable(gCtr).tagValue := get_lookup_meaning('AE_FORM_LABELS','PAGE_NO_LABEL');
652 gctr := gctr + 1;
653 gxmltable(gCtr).tagName := 'of_body_label';
654 gxmltable(gCtr).tagValue := get_lookup_meaning('AE_FORM_LABELS','OF_LABEL');
655 gctr := gctr + 1;
656 gxmltable(gCtr).tagName := 'current_value';
657 gxmltable(gCtr).tagValue := l_pg_count;
658 gctr := gctr + 1;
659 gxmltable(gCtr).tagName := 'count_body_value';
660 gxmltable(gCtr).tagValue := null;
661 gctr := gctr + 1;
662 gxmltable(gCtr).tagName := 'organization_label';
663 gxmltable(gCtr).tagValue := get_lookup_meaning('AE_FORM_LABELS','ORGANIZATION_LABEL');
664 gctr := gctr + 1;
665 gxmltable(gCtr).tagName := 'organization_value';
666 gxmltable(gCtr).tagValue := rec_get_org_id.name;
667 gctr := gctr + 1;
668 gxmltable(gCtr).tagName := 'employee_number_label';
669 gxmltable(gCtr).tagValue := get_lookup_meaning('AE_FORM_LABELS','EMPLOYEE_NUMBER_LABEL');
670 gctr := gctr + 1;
671 gxmltable(gCtr).tagName := 'full_name_label';
672 gxmltable(gCtr).tagValue := get_lookup_meaning('AE_FORM_LABELS','FULL_NAME_LABEL');
673 gctr := gctr + 1;
674 gxmltable(gCtr).tagName := 'job_label';
675 gxmltable(gCtr).tagValue := get_lookup_meaning('AE_FORM_LABELS','JOB_LABEL');
676 gctr := gctr + 1;
677 gxmltable(gCtr).tagName := 'passport_number_body_label';
678 gxmltable(gCtr).tagValue := get_lookup_meaning('AE_FORM_LABELS','PASSPORT_NUMBER_BODY_LABEL');
679 gctr := gctr + 1;
680 gxmltable(gCtr).tagName := 'passport_place_of_issue_body_label';
681 gxmltable(gCtr).tagValue := get_lookup_meaning('AE_FORM_LABELS','PLACE_OF_ISSUE_LABEL');
682 gctr := gctr + 1;
683 gxmltable(gCtr).tagName := 'expiry_date_label';
684 gxmltable(gCtr).tagValue := get_lookup_meaning('AE_FORM_LABELS','EXPIRY_DATE');
685 gctr := gctr + 1;
686 gxmltable(gCtr).tagName := '1';
687 gxmltable(gCtr).tagValue := ('--------------------');
688 gctr := gctr + 1;
689 gxmltable(gCtr).tagName := '2';
690 gxmltable(gCtr).tagValue := ('------------------------------------------');
691 gctr := gctr + 1;
692 gxmltable(gCtr).tagName := '3';
693 gxmltable(gCtr).tagValue := ('--------------------');
694 gctr := gctr + 1;
695 gxmltable(gCtr).tagName := '4';
696 gxmltable(gCtr).tagValue := ('--------------------');
697 gctr := gctr + 1;
698 gxmltable(gCtr).tagName := '5';
699 gxmltable(gCtr).tagValue := ('---------------------');
700 gctr := gctr + 1;
701 gxmltable(gCtr).tagName := '6';
702 gxmltable(gCtr).tagValue := ('---------------------');
703 gctr := gctr + 1;
704 l_emp_found := 1;
705 END IF;
706 OPEN csr_get_job(rec_get_pass_det.assignment_id);
707 FETCH csr_get_job INTO l_job;
708 CLOSE csr_get_job;
709 gxmltable(gCtr).tagName := 'employee_number_value'||' '||i;
710 gxmltable(gCtr).tagValue := (rec_get_pass_det.employee_number);
711 gctr := gctr + 1;
712 gxmltable(gCtr).tagName := 'full_name_value'||' '||i;
713 gxmltable(gCtr).tagValue := (SUBSTR(rec_get_pass_det.full_name,1,80));
714 gctr := gctr + 1;
715 gxmltable(gCtr).tagName := 'job_value'||' '||i;
716 gxmltable(gCtr).tagValue := (l_job);
717 gctr := gctr + 1;
718 gxmltable(gCtr).tagName := 'passport_number_body_value'||' '||i;
719 gxmltable(gCtr).tagValue := (rec_get_pass_det.passport_number);
720 gctr := gctr + 1;
721 gxmltable(gCtr).tagName := 'passport_place_of_issue_body_value'||' '||i;
722 gxmltable(gCtr).tagValue := (rec_get_pass_det.place_of_issue);
723 gctr := gctr + 1;
724 gxmltable(gCtr).tagName := 'expiry_date_value'||' '||i;
725 gxmltable(gCtr).tagValue := (rec_get_pass_det.expiry_date);
726 gctr := gctr + 1;
727 i := i + 1;
728 END LOOP;
729 CLOSE csr_get_pass_det;
730 IF i < 18 AND l_emp_found = 1 THEN
731 FOR j in i..18 LOOP
732 gxmltable(gCtr).tagName := 'employee_number_value'||' '||j;
733 gxmltable(gCtr).tagValue := (null);
734 gctr := gctr + 1;
735 gxmltable(gCtr).tagName := 'full_name_value'||' '||j;
736 gxmltable(gCtr).tagValue := (null);
737 gctr := gctr + 1;
738 gxmltable(gCtr).tagName := 'job_value'||' '||j;
739 gxmltable(gCtr).tagValue := (null);
740 gctr := gctr + 1;
741 gxmltable(gCtr).tagName := 'passport_number_body_value'||' '||j;
742 gxmltable(gCtr).tagValue := (null);
743 gctr := gctr + 1;
744 gxmltable(gCtr).tagName := 'passport_place_of_issue_body_value'||' '||j;
745 gxmltable(gCtr).tagValue := (null);
746 gctr := gctr + 1;
747 gxmltable(gCtr).tagName := 'expiry_date_value'||' '||j;
748 gxmltable(gCtr).tagValue := (null);
749 gctr := gctr + 1;
750 END LOOP;
751 END IF;
752 END LOOP;
753 CLOSE csr_get_org_id;
754 END LOOP;
755 CLOSE csr_get_bg_id;
756 WritetoCLOB ( l_xfdf_blob, l_pg_count );
757 END get_passport_data;
758 --------------------------------------------------------------------------------------------------
759 PROCEDURE get_contract_data
760 (p_request_id IN NUMBER
761 ,p_report_name IN VARCHAR2
762 ,p_date IN VARCHAR2 DEFAULT NULL
763 ,p_business_group_id IN NUMBER DEFAULT NULL
764 ,p_org_structure_id IN NUMBER DEFAULT NULL
765 ,p_org_structure_version_id IN NUMBER DEFAULT NULL
766 ,p_org_id IN NUMBER DEFAULT NULL
767 ,p_expires_in IN NUMBER
768 ,p_units IN VARCHAR2
769 ,l_xfdf_blob OUT NOCOPY BLOB) AS
770 l_parent_id NUMBER;
771 l_date DATE;
772 l_business_group_id NUMBER;
773 l_xfdf_string CLOB;
774 l_rep_param_name varchar2(100);
775 l_rep_label varchar2(100);
776 l_rep_date_label varchar2(100);
777 l_rep_date varchar2(100);
778 l_rep_page varchar2(100);
779 l_one_value varchar2(100);
780 l_count_value varchar2(100);
781 l_page_num varchar2(100);
782 l_of_label varchar2(100);
783 l_str_9 varchar2(100);
784 l_str_8 varchar2(100);
785 l_str_7 varchar2(100);
786 l_str_6 varchar2(100);
787 l_str_5 varchar2(100);
788 l_str_4 varchar2(100);
789 l_str_3 varchar2(100);
790 l_str_2 varchar2(100);
791 l_str_1 varchar2(100);
792 l_str_0 varchar2(100);
793 l_str_0a varchar2(100);
794 l_str_0b varchar2(100);
795 l_str_9b varchar2(100);
796 l_str_8b varchar2(100);
797 l_str_7b varchar2(100);
798 l_str_6b varchar2(100);
799 l_str_5b varchar2(100);
800 l_str_4b varchar2(100);
801 l_str_3b varchar2(100);
802 l_str_2b varchar2(100);
803 l_str_1b varchar2(100);
804 l_str_0bb varchar2(100);
805 l_eno varchar2(100);
806 l_fname varchar2(100);
807 l_ref varchar2(100);
808 l_type varchar2(100);
809 l_status varchar2(100);
810 l_appdate varchar2(100);
811 l_edate varchar2(100);
812 l_b_eno varchar2(100);
813 l_b_fname varchar2(100);
814 l_b_job varchar2(100);
815 l_b_ref varchar2(100);
816 l_b_type varchar2(100);
817 l_b_status varchar2(100);
818 l_b_appdate varchar2(100);
819 l_b_edate varchar2(100);
820 CURSOR csr_get_bg_id IS
821 SELECT business_group_id
822 FROM per_business_groups
823 WHERE business_group_id = nvl(p_business_group_id, business_group_id)
824 AND legislation_code = 'AE';
825 rec_get_bg_id csr_get_bg_id%ROWTYPE;
826 CURSOR csr_get_org_id IS
827 SELECT org.organization_id
828 ,org.name
829 FROM hr_all_organization_units org
830 WHERE org.organization_id IN (SELECT pose.organization_id_child
831 FROM per_org_structure_elements pose
832 CONNECT BY pose.organization_id_parent = PRIOR pose.organization_id_child
833 AND pose.org_structure_version_id = p_org_structure_version_id
834 START WITH pose.organization_id_parent = nvl(p_org_id, l_parent_id)
835 AND pose.org_structure_version_id = p_org_structure_version_id
836 UNION
837 SELECT nvl(p_org_id, l_parent_id)
838 FROM DUAL)
839 AND p_org_structure_version_id IS NOT NULL
840 UNION
841 SELECT org.organization_id
842 ,org.name
843 FROM hr_all_organization_units org
844 WHERE org.organization_id = NVL(p_org_id,org.organization_id)
845 AND org.business_group_id = p_business_group_id
846 AND p_org_structure_version_id IS NULL;
847 rec_get_org_id csr_get_org_id%ROWTYPE;
848 CURSOR csr_get_contract_det IS
849 SELECT people.employee_number
850 ,people.full_name
851 ,period.date_start
852 ,job.name job_name
853 ,cont.reference cont_reference
854 ,get_lookup_meaning('CONTRACT_TYPE',cont.type) cont_type
855 ,cont.ctr_information1 employment_status
856 ,fnd_date.canonical_to_date(cont.ctr_information2) expiry_date
857 FROM per_all_assignments_f assg
858 ,per_all_people_f people
859 ,per_contracts cont
860 ,per_jobs job
861 ,per_periods_of_service period
862 ,hr_all_organization_units org
863 WHERE assg.person_id = people.person_id
864 AND (l_date) BETWEEN assg.effective_start_date
865 AND assg.effective_end_date
866 AND (l_date) BETWEEN people.effective_start_date
867 AND people.effective_end_date
868 AND assg.job_id = job.job_id(+)
869 AND people.person_id = period.person_id
870 AND assg.organization_id = org.organization_id
871 AND org.business_group_id = p_business_group_id
872 AND org.organization_id = rec_get_org_id.organization_id
873 AND cont.person_id = people.person_id
874 AND cont.ctr_information_category = 'AE'
875 AND NVL(to_date(ctr_information2,'YYYY/MM/DD HH24:MI:SS'),
876 DECODE(duration_units,'D',(active_start_date+duration),
877 'W',(active_start_date+(duration*7)),
878 'M',(add_months(active_start_date,duration)),
879 'Y',(add_months(active_start_date,(duration*12)))))
880 BETWEEN NVL(l_date,sysdate)
881 AND DECODE(p_units,'D',(NVL(l_date,sysdate)+p_expires_in),
882 'W',(NVL(l_date,sysdate)+(p_expires_in*7)),
883 'M',(add_months(NVL(l_date,sysdate),p_expires_in)),
884 'Y',(add_months(NVL(l_date,sysdate),(p_expires_in*12))))
885 ORDER BY full_name, employee_number;
886 rec_get_contract_det csr_get_contract_det%ROWTYPE;
887 l_org_name VARCHAR2(80);
888 l_structure_name VARCHAR2(80);
889 l_job VARCHAR2(80);
890 l_version NUMBER;
891 l_emp_found NUMBER;
892 i NUMBER;
893 j NUMBER;
894 l_pg_count NUMBER;
895 l_units VARCHAR2(80);
896 BEGIN
897 l_pg_count := 1;
898 IF p_date IS NOT NULL THEN
899 BEGIN
900 SELECT fnd_date.canonical_to_date(p_date)
901 INTO l_date
902 FROM DUAL;
903 EXCEPTION
904 WHEN OTHERS THEN
905 l_date := TRUNC(sysdate);
906 END;
907 ELSE
908 l_date := TRUNC(sysdate);
909 END IF;
910 INSERT INTO fnd_sessions (session_id, effective_date)
911 VALUES (userenv('SESSIONID'), l_date);
912 l_parent_id := NULL;
913 l_org_name := NULL;
914 l_structure_name := NULL;
915 l_version := NULL;
916 l_emp_found := 0;
917 i := 1;
918 j := 0;
919 gxmltable.DELETE;
920 gCtr := 1;
921 IF p_org_structure_version_id IS NOT NULL
922 /*AND 1 IS NULL*/ THEN
923 BEGIN
924 SELECT distinct pose.organization_id_parent
925 INTO l_parent_id
926 FROM per_org_structure_elements pose
927 WHERE pose.org_structure_version_id = p_org_structure_version_id
928 AND pose.organization_id_parent NOT IN (SELECT pose1.organization_id_child
929 FROM per_org_structure_elements pose1
930 WHERE pose1.org_structure_version_id
931 = p_org_structure_version_id);
932 EXCEPTION
933 WHEN OTHERS THEN
934 l_parent_id := NULL;
935 END;
936 END IF;
937 IF /*p_business_group_id*/ p_org_id IS NOT NULL THEN
938 BEGIN
939 SELECT name
940 INTO l_org_name
941 FROM hr_organization_units
942 WHERE organization_id =/* p_business_group_id*/ p_org_id ;
943 EXCEPTION
944 WHEN OTHERS THEN
945 NULL;
946 END;
947 END IF;
948 IF p_org_structure_id IS NOT NULL THEN
949 BEGIN
950 SELECT name
951 INTO l_structure_name
952 FROM per_organization_structures
953 WHERE organization_structure_id = p_org_structure_id;
954 EXCEPTION
955 WHEN OTHERS THEN
956 NULL;
957 END;
958 END IF;
959 IF p_org_structure_version_id IS NOT NULL THEN
960 BEGIN
961 SELECT version_number
962 INTO l_version
963 FROM per_org_structure_versions
964 WHERE org_structure_version_id = p_org_structure_version_id;
965 EXCEPTION
966 WHEN OTHERS THEN
967 NULL;
968 END;
969 END IF;
970 IF p_units IS NOT NULL THEN
971 BEGIN
972 SELECT meaning
973 INTO l_units
974 FROM hr_lookups
975 WHERE lookup_type = 'QUALIFYING_UNITS'
976 AND lookup_code = p_units;
977 EXCEPTION
978 WHEN OTHERS THEN
979 NULL;
980 END;
981 END IF;
982 dbms_lob.createtemporary(l_xfdf_string,FALSE,DBMS_LOB.CALL);
983 dbms_lob.open(l_xfdf_string,dbms_lob.lob_readwrite);
984 DBMS_LOB.CREATETEMPORARY(l_xfdf_blob,TRUE);
985 clob_to_blob(l_xfdf_string,l_xfdf_blob);
986 --Populate parameter labels and values
987 /********** COMMENTED ON 3-NOV-05 FOR RTF FORMAT
988 gxmltable(gCtr).tagName := 'report_date_label';
989 gxmltable(gCtr).tagValue := get_lookup_meaning('AE_FORM_LABELS','REPORT_DATE_LABEL');
990 gctr := gctr + 1;
991 etc. etc.
992 END OF COMMENTED ON 3-NOV-05 FOR RTF FORMAT******************************/
993 dbms_lob.writeAppend( l_xfdf_string, length('<START>'),'<START>');
994 l_rep_date_label := '<REPORT-DATE-LABEL>' ||get_lookup_meaning('AE_FORM_LABELS','REPORT_DATE_LABEL')||'</REPORT-DATE-LABEL>';
995 l_rep_date := '<REPORT-DATE-VALUE>' ||fnd_date.date_to_displaydate(sysdate)||'</REPORT-DATE-VALUE>';
996 l_rep_page := '<PAGE>'||get_lookup_meaning('AE_FORM_LABELS','PAGE_NO_LABEL')||'</PAGE>';
997 l_one_value := '<ONE-VALUE>' || '1' || '</ONE-VALUE>';
998 l_rep_param_name := '<REPORT-PARAM-LABEL>'||get_lookup_meaning('AE_FORM_LABELS','REPORT_PARAMETERS_LABEL_CTR')||'</REPORT-PARAM-LABEL>';
999 l_str_9 := '<ORG-NAME-HEADER>'||get_lookup_meaning('AE_FORM_LABELS','ORGANIZATION_NAME_LABEL')||'</ORG-NAME-HEADER>';
1000 --l_str_8 := '<ORG-NAME-HEADER-VALUE>'||l_org_name||'</ORG-NAME-HEADER-VALUE>';
1001 l_str_8 := '<ORG-NAME-HEADER-VALUE>'||'<![CDATA['||l_org_name||']]>'||'</ORG-NAME-HEADER-VALUE>';
1002 l_str_7 := '<ORG-HIERARCHY-LABEL>'||get_lookup_meaning('AE_FORM_LABELS','ORGANIZATION_HIERARCHY_LABEL')||'</ORG-HIERARCHY-LABEL>';
1003 -- l_str_6 := '<ORG-HIERARCHY-VALUE>'||l_structure_name||'</ORG-HIERARCHY-VALUE>';
1004 l_str_6 := '<ORG-HIERARCHY-VALUE>'||'<![CDATA['||l_structure_name||']]>'||'</ORG-HIERARCHY-VALUE>';
1005 l_str_5 := '<ORG-VERSION-LABEL>'||get_lookup_meaning('AE_FORM_LABELS','ORGANIZATION_VERSION_LABEL')||'</ORG-VERSION-LABEL>';
1006 l_str_4 := '<ORG-VERSION-VALUE>'||l_version||'</ORG-VERSION-VALUE>';
1007 l_str_3 := '<DURATION-HEADER-LABEL>'||get_lookup_meaning('AE_FORM_LABELS','DURATION_LABEL')||'</DURATION-HEADER-LABEL>';
1008 l_str_2 := '<DURATION-HEADER-VALUE>'||p_expires_in||'</DURATION-HEADER-VALUE>';
1009 l_str_1 := '<DURATION-UNITS-LABEL>'||get_lookup_meaning('AE_FORM_LABELS','DURATION_UNITS_LABEL')||'</DURATION-UNITS-LABEL>';
1010 l_str_0 := '<DURATION-UNITS-VALUE>'||l_units||'</DURATION-UNITS-VALUE>';
1011 l_str_0a := '<EFFECTIVE-DATE-LABEL>'||get_lookup_meaning('AE_FORM_LABELS','EFFECTIVE_DATE_LABEL')||'</EFFECTIVE-DATE-LABEL>';
1012 l_str_0bb := '<EFFECTIVE-DATE-VALUE>'||fnd_date.date_to_displaydate(l_date)||'</EFFECTIVE-DATE-VALUE>';
1013 dbms_lob.writeAppend( l_xfdf_string, length(l_rep_date_label), l_rep_date_label);
1014 dbms_lob.writeAppend( l_xfdf_string, length(l_rep_date), l_rep_date);
1015 dbms_lob.writeAppend( l_xfdf_string, length(l_rep_page), l_rep_page);
1016 dbms_lob.writeAppend( l_xfdf_string, length(l_one_value), l_one_value);
1017 dbms_lob.writeAppend( l_xfdf_string, length(l_rep_param_name), l_rep_param_name);
1018 dbms_lob.writeAppend( l_xfdf_string, length(l_str_9), l_str_9);
1019 dbms_lob.writeAppend( l_xfdf_string, length(l_str_8), l_str_8);
1020 dbms_lob.writeAppend( l_xfdf_string, length(l_str_7), l_str_7);
1021 dbms_lob.writeAppend( l_xfdf_string, length(l_str_6), l_str_6);
1022 dbms_lob.writeAppend( l_xfdf_string, length(l_str_5), l_str_5);
1023 dbms_lob.writeAppend( l_xfdf_string, length(l_str_4), l_str_4);
1024 dbms_lob.writeAppend( l_xfdf_string, length(l_str_3), l_str_3);
1025 dbms_lob.writeAppend( l_xfdf_string, length(l_str_2), l_str_2);
1026 dbms_lob.writeAppend( l_xfdf_string, length(l_str_1), l_str_1);
1027 dbms_lob.writeAppend( l_xfdf_string, length(l_str_0), l_str_0);
1028 dbms_lob.writeAppend( l_xfdf_string, length(l_str_0a), l_str_0a);
1029 dbms_lob.writeAppend( l_xfdf_string, length(l_str_0bb), l_str_0bb);
1030 OPEN csr_get_bg_id;
1031 LOOP
1032 FETCH csr_get_bg_id INTO rec_get_bg_id;
1033 EXIT WHEN csr_get_bg_id%NOTFOUND;
1034 l_business_group_id := rec_get_bg_id.business_group_id;
1035 OPEN csr_get_org_id;
1036 LOOP
1037 FETCH csr_get_org_id INTO rec_get_org_id;
1038 EXIT WHEN csr_get_org_id%NOTFOUND;
1039 l_emp_found := 0;
1040 i := 1;
1041 OPEN csr_get_contract_det;
1042 LOOP
1043 FETCH csr_get_contract_det INTO rec_get_contract_det;
1044 EXIT WHEN csr_get_contract_det%NOTFOUND;
1045 IF i = 10 THEN
1046 i := 1;
1047 END IF;
1048 IF l_emp_found = 0 OR i = 1 THEN
1049 dbms_lob.writeAppend( l_xfdf_string, length('<ORG-REC>'),'<ORG-REC>');
1050 l_pg_count := l_pg_count + 1;
1051 l_rep_date_label := '<REPORT-DATE-LABEL>' ||get_lookup_meaning('AE_FORM_LABELS','REPORT_DATE_LABEL')||'</REPORT-DATE-LABEL>';
1052 l_rep_date := '<REPORT-DATE-VALUE>' ||fnd_date.date_to_displaydate(sysdate)||'</REPORT-DATE-VALUE>';
1053 l_rep_page := '<PAGE>'||get_lookup_meaning('AE_FORM_LABELS','PAGE_NO_LABEL')||'</PAGE>';
1054 l_page_num := '<CURRENT-VALUE>'||l_pg_count||'</CURRENT-VALUE>';
1055 l_rep_label := '<REPORT-LABEL>'||get_lookup_meaning('AE_FORM_LABELS','REPORT_LABEL_CTR')||'</REPORT-LABEL>';
1056 l_str_9b := '<ORG-NAME>'||get_lookup_meaning('AE_FORM_LABELS','ORGANIZATION_LABEL')||'</ORG-NAME>';
1057 -- l_str_8b := '<ORG-VALUE>'||rec_get_org_id.name||'</ORG-VALUE>';
1058 l_str_8b := '<ORG-VALUE>'||'<![CDATA['||rec_get_org_id.name||']]>'||'</ORG-VALUE>';
1059 l_str_7b := '<EMPNO-L>'||get_lookup_meaning('AE_FORM_LABELS','EMPLOYEE_NUMBER_LABEL')||'</EMPNO-L>';
1060 l_str_6b := '<FULLNAME-L>'||get_lookup_meaning('AE_FORM_LABELS','FULL_NAME_LABEL')||'</FULLNAME-L>';
1061 l_str_5b := '<JOB-L>'||get_lookup_meaning('AE_FORM_LABELS','JOB_LABEL')||'</JOB-L>';
1062 l_str_4b := '<CONREF-L>'||get_lookup_meaning('AE_FORM_LABELS','CONTRACT_REFERENCE')||'</CONREF-L>';
1063 l_str_3b := '<CONTYPE-L>'||get_lookup_meaning('AE_FORM_LABELS','CONTRACT_TYPE')||'</CONTYPE-L>';
1064 l_str_2b := '<EMPSTATUS-L>'||get_lookup_meaning('AE_FORM_LABELS','EMPLOYMENT_STATUS')||'</EMPSTATUS-L>';
1065 l_str_1b := '<APPDATE-L>'||get_lookup_meaning('AE_FORM_LABELS','START_DATE_LABEL')||'</APPDATE-L>';
1066 l_str_0b := '<EXPDATE-L>'||get_lookup_meaning('AE_FORM_LABELS','EXPIRY_DATE')||'</EXPDATE-L>';
1067 fnd_file.put_line(fnd_file.log,'Test1');
1068
1069 dbms_lob.writeAppend( l_xfdf_string, length(l_rep_date_label), l_rep_date_label);
1070 dbms_lob.writeAppend( l_xfdf_string, length(l_rep_date), l_rep_date);
1071 dbms_lob.writeAppend( l_xfdf_string, length(l_rep_page), l_rep_page);
1072 dbms_lob.writeAppend( l_xfdf_string, length(l_page_num), l_page_num);
1073 dbms_lob.writeAppend( l_xfdf_string, length(l_rep_label), l_rep_label);
1074 dbms_lob.writeAppend( l_xfdf_string, length(l_str_9b), l_str_9b);
1075 dbms_lob.writeAppend( l_xfdf_string, length(l_str_8b), l_str_8b);
1076 dbms_lob.writeAppend( l_xfdf_string, length(l_str_7b), l_str_7b);
1077 dbms_lob.writeAppend( l_xfdf_string, length(l_str_6b), l_str_6b);
1078 dbms_lob.writeAppend( l_xfdf_string, length(l_str_5b), l_str_5b);
1079 dbms_lob.writeAppend( l_xfdf_string, length(l_str_4b), l_str_4b);
1080 dbms_lob.writeAppend( l_xfdf_string, length(l_str_3b), l_str_3b);
1081 dbms_lob.writeAppend( l_xfdf_string, length(l_str_2b), l_str_2b);
1082 dbms_lob.writeAppend( l_xfdf_string, length(l_str_1b), l_str_1b);
1083 dbms_lob.writeAppend( l_xfdf_string, length(l_str_0b), l_str_0b);
1084 fnd_file.put_line(fnd_file.log,'Test2');
1085 /********* COMMENTED ON 3-NOV-05 FOR RTF FORMAT
1086 gxmltable(gCtr).tagName := 'report_label';
1087 gxmltable(gCtr).tagValue := get_lookup_meaning('AE_FORM_LABELS','REPORT_LABEL_CTR');
1088 gctr := gctr + 1;
1089 etc. etc.
1090 END OF COMMENTED ON 3-NOV-05 FOR RTF FORMAT *******************/
1091 l_emp_found := 1;
1092 END IF;
1093 /*********** COMMENTED ON 3-NOV-05 FOR RTF FORMAT
1094 gxmltable(gCtr).tagName := 'employee_number_value'||' '||i;
1095 gxmltable(gCtr).tagValue := (rec_get_contract_det.employee_number);
1096 gctr := gctr + 1;
1097 etc. etc.
1098 END OF COMMENTED ON 3-NOV-05 FOR RTF FORMAT **************/
1099 l_eno := '<ENO-' || i || '>'||(rec_get_contract_det.employee_number)||'</ENO-' || i || '>';
1100 --l_fname := '<FNAME-' || i || '>'||substr((rec_get_contract_det.full_name),1,60)||'</FNAME-' || i || '>';
1101 l_fname := '<FNAME-' || i || '>'||'<![CDATA['||substr((rec_get_contract_det.full_name),1,60)||']]>'||'</FNAME-' || i || '>';
1102 --l_job:= '<JOB-' || i || '>'|| (rec_get_contract_det.job_name)||'</JOB-' || i || '>';
1103 l_job:= '<JOB-' || i || '>'|| '<![CDATA['||(rec_get_contract_det.job_name)||']]>'||'</JOB-' || i || '>';
1104 --l_ref := '<REF-' || i || '>'|| (rec_get_contract_det.cont_reference) ||'</REF-' || i || '>';
1105 l_ref := '<REF-' || i || '>'||'<![CDATA['||(rec_get_contract_det.cont_reference)||']]>' ||'</REF-' || i || '>';
1106 --l_type := '<TYPE-' || i || '>'|| (rec_get_contract_det.cont_type) ||'</TYPE-' || i || '>';
1107 l_type := '<TYPE-' || i || '>'||'<![CDATA['||(rec_get_contract_det.cont_type)||']]>' ||'</TYPE-' || i || '>';
1108 l_status := '<STATUS-' || i || '>'|| (rec_get_contract_det.employment_status) ||'</STATUS-' || i || '>';
1109 l_appdate := '<APPDATE-' || i || '>'||fnd_date.date_to_displaydate(rec_get_contract_det.date_start)||'</APPDATE-' || i || '>';
1110 l_edate := '<EDATE-' || i || '>'|| fnd_date.date_to_displaydate(rec_get_contract_det.expiry_date) ||'</EDATE-' || i || '>';
1111 dbms_lob.writeAppend( l_xfdf_string, length(l_eno), l_eno);
1112 dbms_lob.writeAppend( l_xfdf_string, length(l_fname), l_fname);
1113 dbms_lob.writeAppend( l_xfdf_string, length(l_job), l_job);
1114 dbms_lob.writeAppend( l_xfdf_string, length(l_ref), l_ref);
1115 dbms_lob.writeAppend( l_xfdf_string, length(l_type), l_type);
1116 dbms_lob.writeAppend( l_xfdf_string, length(l_status), l_status);
1117 dbms_lob.writeAppend( l_xfdf_string, length(l_appdate), l_appdate);
1118 dbms_lob.writeAppend( l_xfdf_string, length(l_edate), l_edate);
1119 i := i + 1;
1120 IF i = 10 then
1121 dbms_lob.writeAppend( l_xfdf_string, length('</ORG-REC>'),'</ORG-REC>');
1122 END IF;
1123 END LOOP;
1124 CLOSE csr_get_contract_det;
1125 IF i < 9 AND l_emp_found = 1 THEN
1126 FOR j in i..9 LOOP
1127 /******** COMMENTED ON 3-NOV-05 FOR RTF FORMAT
1128 gxmltable(gCtr).tagName := 'employee_number_value'||' '||j;
1129 gxmltable(gCtr).tagValue := (null);
1130 gctr := gctr + 1;
1131 etc. etc.
1132 END OF COMMENTED ON 3-NOV-05 FOR RTF FORMAT *********/
1133 l_b_eno := '<ENO-' || j || '>'||null||'</ENO-' || j || '>';
1134 l_b_fname := '<FNAME-' || j || '>'|| null ||'</FNAME-' || j || '>';
1135 l_b_job:= '<JOB-' || j || '>'|| null||'</JOB-' || j || '>';
1136 l_b_ref := '<REF-' || j || '>'|| null ||'</REF-' || j || '>';
1137 l_b_type := '<TYPE-' || j || '>'|| null ||'</TYPE-' || j || '>';
1138 l_b_status := '<STATUS-' || j || '>'|| null ||'</STATUS-' || j || '>';
1139 l_b_appdate := '<APPDATE-' || j || '>'|| null ||'</APPDATE-' || j || '>';
1140 l_b_edate := '<EDATE-' || j || '>'|| null ||'</EDATE-' || j || '>';
1141 dbms_lob.writeAppend( l_xfdf_string, length(l_b_eno), l_b_eno);
1142 dbms_lob.writeAppend( l_xfdf_string, length(l_b_fname), l_b_fname);
1143 dbms_lob.writeAppend( l_xfdf_string, length(l_b_job), l_b_job);
1144 dbms_lob.writeAppend( l_xfdf_string, length(l_b_ref), l_b_ref);
1145 dbms_lob.writeAppend( l_xfdf_string, length(l_b_type), l_b_type);
1146 dbms_lob.writeAppend( l_xfdf_string, length(l_b_status), l_b_status);
1147 dbms_lob.writeAppend( l_xfdf_string, length(l_b_appdate), l_b_appdate);
1148 dbms_lob.writeAppend( l_xfdf_string, length(l_b_edate), l_b_edate);
1149 END LOOP;
1150 dbms_lob.writeAppend( l_xfdf_string, length('</ORG-REC>'),'</ORG-REC>');
1151 END IF;
1152 END LOOP;
1153 CLOSE csr_get_org_id;
1154 END LOOP;
1155 CLOSE csr_get_bg_id;
1156 dbms_lob.writeAppend( l_xfdf_string, length('</START>'),'</START>');
1157 DBMS_LOB.CREATETEMPORARY(l_xfdf_blob,TRUE);
1158 clob_to_blob(l_xfdf_string,l_xfdf_blob);
1159 /* WritetoCLOB ( l_xfdf_blob, l_pg_count );*/
1160 END get_contract_data;
1161 --------------------------------------------------------------------------------------------------------
1162 PROCEDURE Writetoclob
1163 (p_xfdf_blob out nocopy blob
1164 ,p_tot_pg_count IN NUMBER) IS
1165 l_xfdf_string clob;
1166 l_str1 varchar2(1000);
1167 l_str2 varchar2(20);
1168 l_str3 varchar2(20);
1169 l_str4 varchar2(20);
1170 l_str5 varchar2(20);
1171 l_str6 varchar2(30);
1172 l_str7 varchar2(1000);
1173 l_str8 varchar2(240);
1174 l_str9 varchar2(240);
1175 BEGIN
1176 hr_utility.set_location('Entered Procedure Write to clob ',100);
1177 l_str1 := '<?xml version="1.0" encoding="UTF-8"?>
1178 <xfdf xmlns="http://ns.adobe.com/xfdf/" xml:space="preserve">
1179 <fields> ' ;
1180 l_str2 := '<field name="';
1181 l_str3 := '">';
1182 l_str4 := '<value>' ;
1183 l_str5 := '</value> </field>' ;
1184 l_str6 := '</fields> </xfdf>';
1185 l_str7 := '<?xml version="1.0" encoding="UTF-8"?>
1186 <xfdf xmlns="http://ns.adobe.com/xfdf/" xml:space="preserve">
1187 <fields>
1188 </fields> </xfdf>';
1189 dbms_lob.createtemporary(l_xfdf_string,FALSE,DBMS_LOB.CALL);
1190 dbms_lob.open(l_xfdf_string,dbms_lob.lob_readwrite);
1191 if gxmltable.count > 0 then
1192 dbms_lob.writeAppend( l_xfdf_string, length(l_str1), l_str1 );
1193 FOR ctr_table IN gxmltable.FIRST .. gxmltable.LAST LOOP
1194 l_str8 := gxmltable(ctr_table).tagName;
1195 l_str9 := gxmltable(ctr_table).tagValue;
1196 IF gxmltable(ctr_table).tagName IN ('count_body_value', 'count_value') THEN
1197 l_str9 := p_tot_pg_count;
1198 END IF;
1199 IF (l_str9 is not null) THEN
1200 /* Added new check for bug:6721310 */
1201 l_str9 := '<![CDATA['||l_str9||']]>';
1202 dbms_lob.writeAppend( l_xfdf_string, length(l_str2), l_str2 );
1203 dbms_lob.writeAppend( l_xfdf_string, length(l_str8),l_str8);
1204 dbms_lob.writeAppend( l_xfdf_string, length(l_str3), l_str3 );
1205 dbms_lob.writeAppend( l_xfdf_string, length(l_str4), l_str4 );
1206 dbms_lob.writeAppend( l_xfdf_string, length(l_str9), l_str9);
1207 dbms_lob.writeAppend( l_xfdf_string, length(l_str5), l_str5 );
1208 ELSIF (l_str9 IS NULL AND l_str8 IS NOT NULL) THEN
1209 dbms_lob.writeAppend(l_xfdf_string,length(l_str2),l_str2);
1210 dbms_lob.writeAppend(l_xfdf_string,length(l_str8),l_str8);
1211 dbms_lob.writeAppend(l_xfdf_string,length(l_str3),l_str3);
1212 dbms_lob.writeAppend(l_xfdf_string,length(l_str4),l_str4);
1213 dbms_lob.writeAppend(l_xfdf_string,length(l_str5),l_str5);
1214 ELSE
1215 NULL;
1216 END IF;
1217 END LOOP;
1218 dbms_lob.writeAppend( l_xfdf_string, length(l_str6), l_str6 );
1219 ELSE
1220 dbms_lob.writeAppend( l_xfdf_string, length(l_str7), l_str7 );
1221 END IF;
1222 DBMS_LOB.CREATETEMPORARY(p_xfdf_blob,TRUE);
1223 clob_to_blob(l_xfdf_string,p_xfdf_blob);
1224 hr_utility.set_location('Finished Procedure Write to CLOB ,Before clob to blob ',110);
1225 EXCEPTION
1226 WHEN OTHERS then
1227 HR_UTILITY.TRACE('sqleerm ' || SQLERRM);
1228 HR_UTILITY.RAISE_ERROR;
1229 END Writetoclob;
1230 -------------------------------------------------------------------------------------------
1231 Procedure clob_to_blob(p_clob clob,
1232 p_blob IN OUT NOCOPY Blob)
1233 is
1234 l_length_clob number;
1235 l_offset pls_integer;
1236 l_varchar_buffer varchar2(32767);
1237 l_raw_buffer raw(32767);
1238 l_buffer_len number:= 20000;
1239 l_chunk_len number;
1240 l_blob blob;
1241 g_nls_db_char varchar2(60);
1242 l_raw_buffer_len pls_integer;
1243 l_blob_offset pls_integer := 1;
1244 begin
1245 hr_utility.set_location('Entered Procedure clob to blob',120);
1246 select userenv('LANGUAGE') into g_nls_db_char from dual;
1247 l_length_clob := dbms_lob.getlength(p_clob);
1248 l_offset := 1;
1249 while l_length_clob > 0 loop
1250 hr_utility.trace('l_length_clob '|| l_length_clob);
1251 if l_length_clob < l_buffer_len then
1252 l_chunk_len := l_length_clob;
1253 else
1254 l_chunk_len := l_buffer_len;
1255 end if;
1256 DBMS_LOB.READ(p_clob,l_chunk_len,l_offset,l_varchar_buffer);
1257 fnd_file.put_line(fnd_file.log,l_varchar_buffer);
1258 --l_raw_buffer := utl_raw.cast_to_raw(l_varchar_buffer);
1259 l_raw_buffer := utl_raw.convert(utl_raw.cast_to_raw(l_varchar_buffer),'American_America.UTF8',g_nls_db_char);
1260 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));
1261 hr_utility.trace('l_varchar_buffer '|| l_varchar_buffer);
1262 --dbms_lob.write(p_blob,l_chunk_len, l_offset, l_raw_buffer);
1263 dbms_lob.write(p_blob,l_raw_buffer_len, l_blob_offset, l_raw_buffer);
1264 l_blob_offset := l_blob_offset + l_raw_buffer_len;
1265 l_offset := l_offset + l_chunk_len;
1266 l_length_clob := l_length_clob - l_chunk_len;
1267 hr_utility.trace('l_length_blob '|| dbms_lob.getlength(p_blob));
1268 end loop;
1269 hr_utility.set_location('Finished Procedure clob to blob ',130);
1270 end;
1271 ------------------------------------------------------------------
1272 PROCEDURE fetch_pdf_blob
1273 (p_report IN VARCHAR2
1274 ,P_date IN VARCHAR2
1275 ,p_pdf_blob OUT NOCOPY blob) IS
1276 BEGIN
1277 IF (p_report='Visa') THEN
1278 SELECT file_data
1279 INTO p_pdf_blob
1280 FROM fnd_lobs
1281 WHERE file_id = (SELECT MAX(file_id)
1282 FROM fnd_lobs
1283 WHERE file_name like '%PER_VIS_ar_AE.pdf');
1284 ELSIF (p_report='Passport') THEN
1285 SELECT file_data
1286 INTO p_pdf_blob
1287 FROM fnd_lobs
1288 WHERE file_id = (SELECT MAX(file_id)
1289 FROM fnd_lobs
1290 WHERE file_name like '%PER_PASS_ar_AE.pdf');
1291 ELSE
1292 SELECT file_data
1293 INTO p_pdf_blob
1294 FROM fnd_lobs
1295 WHERE file_id = (SELECT MAX(file_id)
1296 FROM fnd_lobs
1297 WHERE file_name like '%PER_CTR_ar_AE.rtf');
1298 END IF;
1299 EXCEPTION
1300 WHEN NO_DATA_FOUND THEN
1301 NULL;
1302 END fetch_pdf_blob;
1303 -----------------------------------------------------------------
1304 FUNCTION get_lookup_meaning
1305 (p_lookup_type varchar2
1306 ,p_lookup_code varchar2)
1307 RETURN VARCHAR2 IS
1308 CURSOR csr_lookup IS
1309 select meaning
1310 from hr_lookups
1311 where lookup_type = p_lookup_type
1312 and lookup_code = p_lookup_code;
1313 l_meaning hr_lookups.meaning%type;
1314 BEGIN
1315 OPEN csr_lookup;
1316 FETCH csr_lookup INTO l_Meaning;
1317 CLOSE csr_lookup;
1318 RETURN l_meaning;
1319 END get_lookup_meaning;
1320 END per_ae_xdo_report;