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