1 PACKAGE BODY PER_US_EEO4_EXP_PKG AS
2 /* $Header: peruseeo4exp.pkb 120.5.12020000.3 2012/07/05 04:58:08 amnaraya ship $ */
3 /*
4 ******************************************************************
5 * *
6 * Copyright (C) 1993 Oracle Corporation. *
7 * All rights reserved. *
8 * *
9 * This material has been provided pursuant to an agreement *
10 * containing restrictions on its use. The material is also *
11 * protected by copyright law. No part of this material may *
15 * the express written permission of Oracle Corporation, *
12 * be copied or distributed, transmitted or transcribed, IN *
13 * any form or by any means, electronic, mechanical, magnetic, *
14 * manual, or otherwise, or disclosed to third parties without *
16 * 500 Oracle Parkway, Redwood City, CA, 94065. *
17 * *
18 ******************************************************************
19
20 Name : PER_US_EEO4_EXP_PKG
21 File Name : peruseeo4exp.pkb
22
23 Description : This package creates XML file for EEO4 exception Report.
24
25 Change List
26 -----------
27 Date Name Vers Bug No Description
28 ----------- ---------- ------ ------- --------------------------
29 18-JUN-2007 jdevasah 115.0 Created.
30 20-APR-2011 nvelaga 115.1 11736960 Modified the package to support the changes
31 115.2 to Ethnic Origin Data capture.
32 115.3 Commented the existing l_query and created
33 two new querys l_query1 and l_query2.
34 03-JUN-2011 nvelaga 115.4 12570805 Added 2 queries l_query3 and l_query4
35 to report employees with invalid Job
36 or employment category.
37 Added the function call pqh_employment_category.
38 fetch_empl_categories for use in queries.
39 23-SEP-2011 nvelaga 115.5 13010481 Modified the query l_query4 to report employees
40 only if the employment_category information is
41 missing, since all the employment_categories
42 are valid and reported in EEO4 report.
43 Corrected the queries l_query1 and l_query2.
44 Commented the function call pqh_employment_category.
45 fetch_empl_categories.
46 Modified the logic to print the query to output
47 file only if debug mode is enabled.
48 28-SEP-2011 nvelaga 115.6 13010481 Corrected the queries l_query1 and l_query2 to check
49 for employment category information.
50 16-MAR-2012 agarai 115.7 13849850 Replaced SYSDATE by l_report_date in l_query1,
51 l_query2, l_query3 and l_query4 to validate
52 the organization_id of employees based on
53 l_report_date instead of SYSDATE.
54 07-MAY-2012 agarai 115.8 14034810 Added l_query5 to report employees
55 having both EEO4 and EEO4A type
56 of ethnic origin data.
57 ****************************************************************************/
58
59
60 procedure generate_exception_report( errbuf OUT NOCOPY VARCHAR2
61 ,retcode OUT NOCOPY NUMBER
62 ,p_business_group_id varchar2
63 , p_reporting_year number) is
64
65 qryCtx DBMS_XMLGEN.ctxHandle;
66 l_result CLOB;
67 -- Commented for bug#11736960
68 --l_query varchar2(10000);
69 l_report_date date;
70 l_xml_string varchar2(32000);
71 l_business_group_name varchar2(240);
72 --l_report_end_date date;
73
74 -- Added for bug#11736960
75 l_query1 varchar2(10000);
76 l_query2 varchar2(10000);
77 -- Added for bug#12570805
78 l_query3 varchar2(10000);
79 l_query4 varchar2(10000);
80 l_query5 varchar2(10000);--added for bug 14034810
81
82 l_fr VARCHAR2(2000);
83 l_ft VARCHAR2(2000);
84 l_pr VARCHAR2(2000);
85 l_pt VARCHAR2(2000);
86
87 begin
88 l_report_date := to_date ('30-06'|| p_reporting_year,'dd-mm-yyyy');
89
90 /* Commented for bug#13010481
91 -- Added for bug#12570805
92 pqh_employment_category.fetch_empl_categories(p_business_group_id
93 ,l_fr
94 ,l_ft
95 ,l_pr
96 ,l_pt); */
97
98 -- Added l_query1 and l_query2 for bug#11736960, to support the changes to Ethnic Origin Data capture.
99
100 -- l_query1 will get the people for whom Ethnic Origin information is not entered.
101 --Bug 13849850: Replaced SYSDATE with l_report_date to validate the organization_id of employees based on l_report_date instead of SYSDATE
102 l_query1 := 'SELECT peo.full_name name,
103 peo.employee_number employee_number,
104 ''Ethnic Origin (EIT) information is missing'' exception_reason
105 FROM per_all_people_f peo,
106 per_all_assignments_f ass,
107 per_assignment_status_types past,
108 per_pay_proposals ppp,
109 per_jobs job,
110 per_pay_bases ppb,
111 hr_lookups hl
112 WHERE peo.person_id = ass.person_id
113 AND ass.pay_basis_id = ppb.pay_basis_id
114 AND ass.assignment_id = ppp.assignment_id
115 AND hl.lookup_code = job.job_information1
116 AND job.job_information1 IS NOT NULL
117 AND job.job_information_category = ''US''
118 AND hl.lookup_type = ''US_EEO4_JOB_CATEGORIES''
119 AND ass.job_id = job.job_id
120 AND ass.primary_flag = ''Y''
121 AND ass.employment_category IS NOT NULL
122 AND ppp.change_date = ( SELECT MAX(change_date)
123 FROM per_pay_proposals pro
124 WHERE ppp.assignment_id = pro.assignment_id
125 AND pro.change_date <= ''' || to_char(l_report_date) || '''
126 AND pro.approved = ''Y'' )
127 AND ass.organization_id IN (SELECT organization_id
128 FROM hr_all_organization_units
129 WHERE business_group_id = ' || p_business_group_id || '
130 AND ''' || to_char(l_report_date) || ''' BETWEEN date_from AND NVL(date_to,''' || to_char(l_report_date) || ''') )
131 AND ass.assignment_status_type_id = past.assignment_status_type_id
132 AND peo.current_employee_flag = ''Y''
133 AND ass.assignment_type = ''E''
134 AND ''' || to_char(l_report_date) || ''' BETWEEN peo.effective_start_date AND peo.effective_end_date
135 AND ''' || to_char(l_report_date) || ''' BETWEEN ass.effective_start_date AND ass.effective_end_date
136 AND past.per_system_status <> ''TERM_ASSIGN''
137 AND (NOT EXISTS (SELECT ''X''
138 FROM per_people_extra_info ppei
139 WHERE ppei.person_id = peo.person_id
140 AND ppei.information_type = ''US_ETHNIC_ORIGIN'')
141 )';
142
143 -- l_query2 will get the people with invalid Ethnic Origin information.
144 --Bug 13849850: Replaced SYSDATE with l_report_date to validate the organization_id of employees based on l_report_date instead of SYSDATE
145 l_query2 := 'SELECT peo.full_name name,
146 peo.employee_number employee_number,
147 DECODE((DECODE(ppei.pei_information1, ''Y'', 1, ''N'', 0)
148 + DECODE(ppei.pei_information2, ''Y'', 1, ''N'', 0)
149 + DECODE(ppei.pei_information3, ''Y'', 1, ''N'', 0)
150 + DECODE(ppei.pei_information4, ''Y'', 1, ''N'', 0)
151 + DECODE(ppei.pei_information5, ''Y'', 1, ''N'', 0)
152 + DECODE(ppei.pei_information6, ''Y'', 1, ''N'', 0)
153 ), 0, ''No individual race field is set to yes in Ethnic Origin (EIT)'',
154 ''More than one individual race field is set to yes in Ethnic Origin (EIT)'') exception_reason
155 FROM per_all_people_f peo,
156 per_all_assignments_f ass,
157 per_assignment_status_types past,
158 per_pay_proposals ppp,
159 per_jobs job,
160 per_pay_bases ppb,
161 hr_lookups hl,
162 per_people_extra_info ppei
163 WHERE peo.person_id = ass.person_id
164 AND ass.pay_basis_id = ppb.pay_basis_id
165 AND ass.assignment_id = ppp.assignment_id
166 AND hl.lookup_code = job.job_information1
167 AND job.job_information1 IS NOT NULL
168 AND job.job_information_category = ''US''
169 AND hl.lookup_type = ''US_EEO4_JOB_CATEGORIES''
170 AND ass.job_id = job.job_id
171 AND ass.primary_flag = ''Y''
172 AND ass.employment_category IS NOT NULL
173 AND ppp.change_date = ( SELECT MAX(change_date)
174 FROM per_pay_proposals pro
175 WHERE ppp.assignment_id = pro.assignment_id
176 AND pro.change_date <= ''' || to_char(l_report_date) || '''
177 AND pro.approved = ''Y'' )
178 AND ass.organization_id IN (SELECT organization_id
182 AND ass.assignment_status_type_id = past.assignment_status_type_id
179 FROM hr_all_organization_units
180 WHERE business_group_id = ' || p_business_group_id || '
181 AND ''' || to_char(l_report_date) || ''' BETWEEN date_from AND NVL(date_to,''' || to_char(l_report_date) || ''') )
183 AND peo.current_employee_flag = ''Y''
184 AND ass.assignment_type = ''E''
185 AND ''' || to_char(l_report_date) || ''' BETWEEN peo.effective_start_date AND peo.effective_end_date
186 AND ''' || to_char(l_report_date) || ''' BETWEEN ass.effective_start_date AND ass.effective_end_date
187 AND past.per_system_status <> ''TERM_ASSIGN''
188 AND ppei.person_id = peo.person_id
189 AND ppei.information_type = ''US_ETHNIC_ORIGIN''
190 AND (DECODE(ppei.pei_information1, ''Y'', 1, ''N'', 0)
191 + DECODE(ppei.pei_information2, ''Y'', 1, ''N'', 0)
192 + DECODE(ppei.pei_information3, ''Y'', 1, ''N'', 0)
193 + DECODE(ppei.pei_information4, ''Y'', 1, ''N'', 0)
194 + DECODE(ppei.pei_information5, ''Y'', 1, ''N'', 0)
195 + DECODE(ppei.pei_information6, ''Y'', 1, ''N'', 0)
196 ) != 1';
197
198 -- l_query3 will get the people with invalid Job category.
199 --Bug 13849850: Replaced SYSDATE with l_report_date to validate the organization_id of employees based on l_report_date instead of SYSDATE
200 l_query3 := 'SELECT peo.full_name name,
201 peo.employee_number employee_number,
202 ''Job information is missing or is not of EEO-4 category'' exception_reason
203 FROM per_all_people_f peo,
204 per_all_assignments_f ass,
205 per_assignment_status_types past,
206 per_pay_proposals ppp,
207 per_pay_bases ppb
208 WHERE peo.person_id = ass.person_id
209 AND ass.pay_basis_id = ppb.pay_basis_id
210 AND ass.assignment_id = ppp.assignment_id
211 AND ass.primary_flag = ''Y''
212 AND ppp.change_date = ( SELECT MAX(change_date)
213 FROM per_pay_proposals pro
217 AND ass.organization_id IN (SELECT organization_id
214 WHERE ppp.assignment_id = pro.assignment_id
215 AND pro.change_date <= ''' || to_char(l_report_date) || '''
216 AND pro.approved = ''Y'' )
218 FROM hr_all_organization_units
219 WHERE business_group_id = ' || p_business_group_id || '
220 AND ''' || to_char(l_report_date) || ''' BETWEEN date_from AND NVL(date_to,''' || to_char(l_report_date) || ''') )
221 AND ass.assignment_status_type_id = past.assignment_status_type_id
222 AND peo.current_employee_flag = ''Y''
223 AND ass.assignment_type = ''E''
224 AND ''' || to_char(l_report_date) || ''' BETWEEN peo.effective_start_date AND peo.effective_end_date
225 AND ''' || to_char(l_report_date) || ''' BETWEEN ass.effective_start_date AND ass.effective_end_date
226 AND past.per_system_status <> ''TERM_ASSIGN''
227 AND (NOT EXISTS (SELECT ''X''
228 FROM per_jobs job,
229 hr_lookups hl
230 WHERE job.job_information1 = hl.lookup_code
231 AND job.job_id = ass.job_id
232 AND job.job_information_category = ''US''
233 AND hl.lookup_type = ''US_EEO4_JOB_CATEGORIES'')
234 )';
235
236 -- l_query4 will get the people with invalid Employment category.
237 --Bug 13849850: Replaced SYSDATE with l_report_date to validate the organization_id of employees based on l_report_date instead of SYSDATE
238 l_query4 := 'SELECT peo.full_name name,
239 peo.employee_number employee_number,
240 ''Employment Category information is missing'' exception_reason
241 FROM per_all_people_f peo,
242 per_all_assignments_f ass,
243 per_assignment_status_types past,
244 per_pay_proposals ppp,
245 per_jobs job,
246 per_pay_bases ppb,
247 hr_lookups hl
248 WHERE peo.person_id = ass.person_id
249 AND ass.pay_basis_id = ppb.pay_basis_id
250 AND ass.assignment_id = ppp.assignment_id
251 AND hl.lookup_code = job.job_information1
252 AND job.job_information1 IS NOT NULL
253 AND job.job_information_category = ''US''
254 AND hl.lookup_type = ''US_EEO4_JOB_CATEGORIES''
255 AND ass.job_id = job.job_id
256 AND ass.primary_flag = ''Y''
257 AND ppp.change_date = ( SELECT MAX(change_date)
258 FROM per_pay_proposals pro
259 WHERE ppp.assignment_id = pro.assignment_id
260 AND pro.change_date <= ''' || to_char(l_report_date) || '''
261 AND pro.approved = ''Y'' )
262 AND ass.organization_id IN (SELECT organization_id
263 FROM hr_all_organization_units
264 WHERE business_group_id = ' || p_business_group_id || '
265 AND ''' || to_char(l_report_date) || ''' BETWEEN date_from AND NVL(date_to,''' || to_char(l_report_date) || ''') )
266 AND ass.assignment_status_type_id = past.assignment_status_type_id
267 AND peo.current_employee_flag = ''Y''
268 AND ass.assignment_type = ''E''
269 AND ''' || to_char(l_report_date) || ''' BETWEEN peo.effective_start_date AND peo.effective_end_date
270 AND ''' || to_char(l_report_date) || ''' BETWEEN ass.effective_start_date AND ass.effective_end_date
271 AND past.per_system_status <> ''TERM_ASSIGN''
272 AND ass.employment_category IS NULL';
273
274 -- l_query5 will get the people wih both EEO4 and EEO4A ethnic origins
275 --added for bug 14034810
276 l_query5 :='SELECT peo.full_name name,
277 peo.employee_number employee_number,
278 ''Both EEO4 Ethnic Origin and EEO4A Ethnic Origin (EIT) information exist : Employee can have only one type of ethnic origin(EEO4 or EEO4A)'' exception_reason
279 FROM per_all_people_f peo,
280 per_all_assignments_f ass,
281 per_assignment_status_types past,
282 per_pay_proposals ppp,
283 per_jobs job,
284 per_pay_bases ppb,
285 hr_lookups hl
286 WHERE peo.person_id = ass.person_id
287 AND ass.pay_basis_id = ppb.pay_basis_id
288 AND ass.assignment_id = ppp.assignment_id
289 AND hl.lookup_code = job.job_information1
290 AND job.job_information1 IS NOT NULL
291 AND job.job_information_category = ''US''
292 AND hl.lookup_type = ''US_EEO4_JOB_CATEGORIES''
293 AND ass.job_id = job.job_id
294 AND ass.primary_flag = ''Y''
295 AND ass.employment_category IS NOT NULL
296 AND ppp.change_date = ( SELECT MAX(change_date)
297 FROM per_pay_proposals pro
298 WHERE ppp.assignment_id = pro.assignment_id
299 AND pro.change_date <= ''' || to_char(l_report_date) || '''
300 AND pro.approved = ''Y'' )
301 AND ass.organization_id IN (SELECT organization_id
302 FROM hr_all_organization_units
303 WHERE business_group_id = ' || p_business_group_id || '
304 AND ''' || to_char(l_report_date) || ''' BETWEEN date_from AND NVL(date_to,''' || to_char(l_report_date) || ''') )
305 AND ass.assignment_status_type_id = past.assignment_status_type_id
306 AND peo.current_employee_flag = ''Y''
307 AND ass.assignment_type = ''E''
308 AND ''' || to_char(l_report_date) || ''' BETWEEN peo.effective_start_date AND peo.effective_end_date
309 AND ''' || to_char(l_report_date) || ''' BETWEEN ass.effective_start_date AND ass.effective_end_date
310 AND past.per_system_status <> ''TERM_ASSIGN''
311 AND (EXISTS (SELECT ''X''
312 FROM per_people_extra_info ppei
313 WHERE ppei.person_id = peo.person_id
314 AND ppei.information_type = ''US_ETHNIC_ORIGIN''
315 AND (DECODE(ppei.pei_information1, ''Y'', 1,0)
316 + DECODE(ppei.pei_information2, ''Y'', 1,0)
317 + DECODE(ppei.pei_information3, ''Y'', 1,0)
318 + DECODE(ppei.pei_information4, ''Y'', 1,0)
319 + DECODE(ppei.pei_information5, ''Y'', 1,0)
320 + DECODE(ppei.pei_information6, ''Y'', 1,0)
321 ) >= 1
322 )
323 )
324 AND (EXISTS (SELECT ''X''
325 FROM per_people_extra_info ppei
326 WHERE ppei.person_id = peo.person_id
327 AND ppei.information_type = ''US_EEO4A_ETHNIC_ORIGIN''
328 AND (DECODE(ppei.pei_information1, ''Y'', 1,0)
329 + DECODE(ppei.pei_information2, ''Y'', 1,0)
330 + DECODE(ppei.pei_information3, ''Y'', 1,0)
331 + DECODE(ppei.pei_information4, ''Y'', 1,0)
332 + DECODE(ppei.pei_information5, ''Y'', 1,0)
333 + DECODE(ppei.pei_information6, ''Y'', 1,0)
334 ) >= 1
335 )
336 )';
337
338 /* Commneted for bug#11736960 starts
339 l_query := 'SELECT peo.full_name name,
340 peo.employee_number employee_number,
341 decode(peo.per_information1,
342 13, ''Ethnic Origin is "Two or More Races" and "Additional Ethnic Code" is missing'',
343 null, ''Ethnic Origin is missing'') exception_reason
344 FROM per_all_people_f peo,
345 per_all_assignments_f ass,
346 per_assignment_status_types past,
347 per_pay_proposals ppp,
348 per_jobs job,
349 per_pay_bases ppb,
350 hr_lookups hl
351 WHERE peo.person_id = ass.person_id
352 AND ass.pay_basis_id = ppb.pay_basis_id
353 AND ass.assignment_id = ppp.assignment_id
354 AND hl.lookup_code = job.job_information1
355 AND job.job_information1 IS NOT NULL
356 AND job.job_information_category = ''US''
357 AND hl.lookup_type = ''US_EEO4_JOB_CATEGORIES''
358 AND ass.job_id = job.job_id
359 AND ass.primary_flag = ''Y''
360 AND ppp.change_date = ( SELECT MAX(change_date)
361 FROM per_pay_proposals pro
362 WHERE ppp.assignment_id = pro.assignment_id
363 AND pro.change_date <= ''' || to_char(l_report_date) || '''
364 AND pro.approved = ''Y'' )
365 AND ass.organization_id IN (SELECT organization_id
366 FROM hr_all_organization_units
367 WHERE business_group_id = ' || p_business_group_id || '
368 AND SYSDATE BETWEEN date_from AND NVL(date_to,SYSDATE) )
369 AND ass.assignment_status_type_id = past.assignment_status_type_id
370 AND peo.current_employee_flag = ''Y''
371 AND ass.assignment_type = ''E''
372 AND ''' || to_char(l_report_date) || ''' BETWEEN peo.effective_start_date AND peo.effective_end_date
373 AND ''' || to_char(l_report_date) || ''' BETWEEN ass.effective_start_date AND ass.effective_end_date
374 AND past.per_system_status <> ''TERM_ASSIGN''
375 AND (peo.per_information1 is null
376 OR (peo.per_information1 =''13''
377 AND not EXISTS (SELECT 1
378 FROM per_people_extra_info ppei
379 WHERE ppei.information_type=''PER_US_ADDL_ETHNIC_CAT''
380 AND ppei.pei_information5 IS not NULL
381 AND ppei.person_id=peo.person_id)
382 )
383 )';
384 Commneted for bug#11736960 ends */
385
386 -- Added for bug#12570805
387 IF (hr_utility.debug_enabled) THEN
388 FND_FILE.PUT_LINE(FND_FILE.LOG,l_query3);
389 END IF;
390 qryCtx := dbms_xmlgen.newContext (l_query3);
391
392 l_xml_string := '<?xml version="1.0"?>';
393 FND_FILE.PUT_LINE(FND_FILE.LOG,l_xml_string);
394 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_xml_string);
395
396 l_xml_string := '<ROWSET>';
397 FND_FILE.PUT_LINE(FND_FILE.LOG,l_xml_string);
398 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_xml_string);
399
400 --Load 5 records at a time
401 DBMS_XMLGEN.setMaxRows(qryCtx, 5);
402 LOOP
403 --save the XML into the CLOB field
404 l_result := DBMS_XMLGEN.getXML(qryCtx);
405 l_xml_string := substr( l_result, instr(l_result,'<ROW>',1),instr(l_result,'</ROWSET>',-1) - instr(l_result,'<ROW>',1));
406 --insert into tab_clob values (to_char(l_xml_string));
407 EXIT WHEN DBMS_XMLGEN.getNumRowsProcessed(qryCtx) = 0;
408 FND_FILE.PUT_LINE(FND_FILE.LOG,l_xml_string);
409 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_xml_string);
410
411 END LOOP;
412
413 -- Added for bug#12570805
414 IF (hr_utility.debug_enabled) THEN
415 FND_FILE.PUT_LINE(FND_FILE.LOG,l_query4);
416 END IF;
417 qryCtx := dbms_xmlgen.newContext (l_query4);
418
419 --Load 5 records at a time
420 DBMS_XMLGEN.setMaxRows(qryCtx, 5);
421 LOOP
422 --save the XML into the CLOB field
423 l_result := DBMS_XMLGEN.getXML(qryCtx);
424 l_xml_string := substr( l_result, instr(l_result,'<ROW>',1),instr(l_result,'</ROWSET>',-1) - instr(l_result,'<ROW>',1));
425 --insert into tab_clob values (to_char(l_xml_string));
426 EXIT WHEN DBMS_XMLGEN.getNumRowsProcessed(qryCtx) = 0;
427 FND_FILE.PUT_LINE(FND_FILE.LOG,l_xml_string);
428 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_xml_string);
429
430 END LOOP;
431
432 -- Added for bug#11736960
433 IF (hr_utility.debug_enabled) THEN
437
434 FND_FILE.PUT_LINE(FND_FILE.LOG,l_query1);
435 END IF;
436 --FND_FILE.PUT_LINE(FND_FILE.LOG,l_query);
438 -- Added for bug#11736960
439 qryCtx := dbms_xmlgen.newContext (l_query1);
440 --qryCtx := dbms_xmlgen.newContext (l_query);
441
442 --Load 5 records at a time
443 DBMS_XMLGEN.setMaxRows(qryCtx, 5);
444 LOOP
445 --save the XML into the CLOB field
446 l_result := DBMS_XMLGEN.getXML(qryCtx);
447 l_xml_string := substr( l_result, instr(l_result,'<ROW>',1),instr(l_result,'</ROWSET>',-1) - instr(l_result,'<ROW>',1));
448 --insert into tab_clob values (to_char(l_xml_string));
452
449 EXIT WHEN DBMS_XMLGEN.getNumRowsProcessed(qryCtx) = 0;
450 FND_FILE.PUT_LINE(FND_FILE.LOG,l_xml_string);
451 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_xml_string);
453 END LOOP;
454
455 -- Added for bug#11736960
456 IF (hr_utility.debug_enabled) THEN
457 FND_FILE.PUT_LINE(FND_FILE.LOG,l_query2);
458 END IF;
459
460 qryCtx := dbms_xmlgen.newContext (l_query2);
461
462 --Load 5 records at a time
463 DBMS_XMLGEN.setMaxRows(qryCtx, 5);
464 LOOP
465 --save the XML into the CLOB field
466 l_result := DBMS_XMLGEN.getXML(qryCtx);
467 l_xml_string := substr( l_result, instr(l_result,'<ROW>',1),instr(l_result,'</ROWSET>',-1) - instr(l_result,'<ROW>',1));
468 --insert into tab_clob values (to_char(l_xml_string));
469 EXIT WHEN DBMS_XMLGEN.getNumRowsProcessed(qryCtx) = 0;
470 FND_FILE.PUT_LINE(FND_FILE.LOG,l_xml_string);
471 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_xml_string);
472
473 END LOOP;
474
475 --execute l_query5,added for bug 14034810
476 IF (hr_utility.debug_enabled) THEN
477 FND_FILE.PUT_LINE(FND_FILE.LOG,l_query5);
478 END IF;
479 qryCtx := dbms_xmlgen.newContext (l_query5);
480
481 --Load 5 records at a time
482 DBMS_XMLGEN.setMaxRows(qryCtx, 5);
483
484 LOOP
485 --save the XML into the CLOB field
486 l_result := DBMS_XMLGEN.getXML(qryCtx);
487
488 --Extract the 5 records
489 l_xml_string := substr( l_result, instr(l_result,'<ROW>',1),instr(l_result,'</ROWSET>',-1) - instr(l_result,'<ROW>',1));
490
491 EXIT WHEN DBMS_XMLGEN.getNumRowsProcessed(qryCtx) = 0;
492
493 FND_FILE.PUT_LINE(FND_FILE.LOG,l_xml_string);
494 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_xml_string);
495 END LOOP;
496
497 -- Get Name of the business group.
498 SELECT name into l_business_group_name from hr_organization_units
499 WHERE organization_id = p_business_group_id
500 AND business_group_id = p_business_group_id;
501
502 -- Load Header tags
503 l_xml_string := '<C_BUSINESS_GROUP_NAME> '|| l_business_group_name ||' </C_BUSINESS_GROUP_NAME>
504 <C_REPORT_DATE> ' || to_char(l_report_date, 'dd-Mon-yyyy') ||' </C_REPORT_DATE>
505 <C_REPORT_YEAR> ' || p_reporting_year ||' </C_REPORT_YEAR>
506 </ROWSET>';
507 FND_FILE.PUT_LINE(FND_FILE.LOG,l_xml_string);
508 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_xml_string);
509
510 end generate_exception_report;
511
512 END PER_US_EEO4_EXP_PKG;
513
514
[Home] [Help]