1 PACKAGE BODY PER_US_EEO4A_EXP_PKG AS
2 /* $Header: peuseeo4aexp.pkb 120.2.12020000.1 2012/07/16 10:19:05 agarai noship $ */
3 /*
7 * All rights reserved. *
4 ******************************************************************
5 * *
6 * Copyright (C) 1993 Oracle Corporation. *
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 *
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 *
15 * the express written permission of Oracle Corporation, *
16 * 500 Oracle Parkway, Redwood City, CA, 94065. *
17 * *
18 ******************************************************************
19
20 Name : PER_US_EEO4A_EXP_PKG
21 File Name : peuseeo4aexp.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 09-DEC-2011 agarai 115.0 13112110 Created for the EEO4A
30 Exception Report.
31 16-MAR-2012 agarai 115.1 13849850 Replaced SYSDATE with
32 l_report_date in
33 l_query1, l_query2,
34 l_query3, l_query4,
35 l_query5 and l_query6 to
36 validate organization_id
37 of employees based on
38 l_report_date instead of
39 SYSDATE.
40 ****************************************************************************/
41
42 /** procedure to generate the EEO4A Exception report **/
43 PROCEDURE generate_exception_report( errbuf OUT NOCOPY VARCHAR2
44 ,retcode OUT NOCOPY NUMBER
45 ,p_business_group_id varchar2
46 , p_reporting_year number) IS
47
48 l_query1 varchar2(10000);
49 l_query2 varchar2(10000);
50 l_query3 varchar2(10000);
51 l_query4 varchar2(10000);
52 l_query5 varchar2(10000);
53 l_query6 varchar2(10000);
54
55 l_report_date date;
56 l_xml_string varchar2(32000);
57 l_business_group_name varchar2(240);
58 l_result CLOB;
59
60 qryCtx DBMS_XMLGEN.ctxHandle;
61
62 BEGIN
63 l_report_date := to_date ('30-06'|| p_reporting_year,'dd-mm-yyyy');
64
65 -- l_query1 will get the people for whom EEO4A Ethnic Origin information is not entered.
66 --Bug 13849850: Replaced SYSDATE with l_report_date to validate the organization_id of the employee based on l_report_date instead of SYSDATE
67 l_query1 :='SELECT peo.full_name name,
68 peo.employee_number employee_number,
69 ''EEO4A Ethnic Origin (EIT) information is missing'' exception_reason
70 FROM per_all_people_f peo,
71 per_all_assignments_f ass,
72 per_assignment_status_types past,
73 per_pay_proposals ppp,
74 per_jobs job,
75 per_pay_bases ppb,
76 hr_lookups hl
77 WHERE peo.person_id = ass.person_id
78 AND ass.pay_basis_id = ppb.pay_basis_id
79 AND ass.assignment_id = ppp.assignment_id
80 AND hl.lookup_code = job.job_information1
81 AND job.job_information1 IS NOT NULL
82 AND job.job_information_category = ''US''
83 AND hl.lookup_type = ''US_EEO4_JOB_CATEGORIES''
84 AND ass.job_id = job.job_id
85 AND ass.primary_flag = ''Y''
86 AND ass.employment_category IS NOT NULL
87 AND ppp.change_date = ( SELECT MAX(change_date)
88 FROM per_pay_proposals pro
89 WHERE ppp.assignment_id = pro.assignment_id
90 AND pro.change_date <= ''' || to_char(l_report_date) || '''
91 AND pro.approved = ''Y'' )
92 AND ass.organization_id IN (SELECT organization_id
93 FROM hr_all_organization_units
94 WHERE business_group_id = ' || p_business_group_id || '
95 AND ''' || to_char(l_report_date) || ''' BETWEEN date_from AND NVL(date_to,''' || to_char(l_report_date) || ''') )
96 AND ass.assignment_status_type_id = past.assignment_status_type_id
97 AND peo.current_employee_flag = ''Y''
98 AND ass.assignment_type = ''E''
102 AND (NOT EXISTS (SELECT ''X''
99 AND ''' || to_char(l_report_date) || ''' BETWEEN peo.effective_start_date AND peo.effective_end_date
100 AND ''' || to_char(l_report_date) || ''' BETWEEN ass.effective_start_date AND ass.effective_end_date
101 AND past.per_system_status <> ''TERM_ASSIGN''
103 FROM per_people_extra_info ppei
104 WHERE ppei.person_id = peo.person_id
105 AND ppei.information_type = ''US_EEO4A_ETHNIC_ORIGIN'')
106 )';
107
108
109 -- l_query2 will get the people with invalid EEO4A Ethnic Origin information.
110 --Bug 13849850: Replaced SYSDATE with l_report_date to validate the organization_id of the employee based on l_report_date instead of SYSDATE
111 l_query2 := 'SELECT peo.full_name name,
112 peo.employee_number employee_number,
113 DECODE((DECODE(ppei.pei_information1, ''Y'', 1, ''N'', 0)
114 + DECODE(ppei.pei_information2, ''Y'', 1, ''N'', 0)
118 + DECODE(ppei.pei_information6, ''Y'', 1, ''N'', 0)
115 + DECODE(ppei.pei_information3, ''Y'', 1, ''N'', 0)
116 + DECODE(ppei.pei_information4, ''Y'', 1, ''N'', 0)
117 + DECODE(ppei.pei_information5, ''Y'', 1, ''N'', 0)
119 ), 0, ''No individual race field is set to yes in EEO4A Ethnic Origin (EIT)'',
120 ''More than one individual race field is set to yes in EEO4A Ethnic Origin (EIT)'') exception_reason
121 FROM per_all_people_f peo,
122 per_all_assignments_f ass,
123 per_assignment_status_types past,
124 per_pay_proposals ppp,
125 per_jobs job,
126 per_pay_bases ppb,
127 hr_lookups hl,
128 per_people_extra_info ppei
129 WHERE peo.person_id = ass.person_id
130 AND ass.pay_basis_id = ppb.pay_basis_id
131 AND ass.assignment_id = ppp.assignment_id
132 AND hl.lookup_code = job.job_information1
133 AND job.job_information1 IS NOT NULL
134 AND job.job_information_category = ''US''
135 AND hl.lookup_type = ''US_EEO4_JOB_CATEGORIES''
136 AND ass.job_id = job.job_id
137 AND ass.primary_flag = ''Y''
138 AND ass.employment_category IS NOT NULL
139 AND ppp.change_date = ( SELECT MAX(change_date)
140 FROM per_pay_proposals pro
141 WHERE ppp.assignment_id = pro.assignment_id
142 AND pro.change_date <= ''' || to_char(l_report_date) || '''
143 AND pro.approved = ''Y'' )
144 AND ass.organization_id IN (SELECT organization_id
145 FROM hr_all_organization_units
146 WHERE business_group_id = ' || p_business_group_id || '
147 AND ''' || to_char(l_report_date) || ''' BETWEEN date_from AND NVL(date_to,''' || to_char(l_report_date) || ''') )
148 AND ass.assignment_status_type_id = past.assignment_status_type_id
149 AND peo.current_employee_flag = ''Y''
150 AND ass.assignment_type = ''E''
151 AND ''' || to_char(l_report_date) || ''' BETWEEN peo.effective_start_date AND peo.effective_end_date
152 AND ''' || to_char(l_report_date) || ''' BETWEEN ass.effective_start_date AND ass.effective_end_date
153 AND past.per_system_status <> ''TERM_ASSIGN''
154 AND ppei.person_id = peo.person_id
155 AND ppei.information_type = ''US_EEO4A_ETHNIC_ORIGIN''
156 AND (DECODE(ppei.pei_information1, ''Y'', 1, ''N'', 0)
157 + DECODE(ppei.pei_information2, ''Y'', 1, ''N'', 0)
158 + DECODE(ppei.pei_information3, ''Y'', 1, ''N'', 0)
159 + DECODE(ppei.pei_information4, ''Y'', 1, ''N'', 0)
160 + DECODE(ppei.pei_information5, ''Y'', 1, ''N'', 0)
161 + DECODE(ppei.pei_information6, ''Y'', 1, ''N'', 0)
162 ) != 1';
163
164 -- l_query3 will get the people with invalid Job category.
165 --Bug 13849850: Replaced SYSDATE with l_report_date to validate the organization_id of the employee based on l_report_date instead of SYSDATE
166 l_query3 :='SELECT peo.full_name name,
167 peo.employee_number employee_number,
168 ''Job information is missing or is not of EEO-4 category'' exception_reason
169 FROM per_all_people_f peo,
170 per_all_assignments_f ass,
171 per_assignment_status_types past,
172 per_pay_proposals ppp,
173 per_pay_bases ppb
174 WHERE peo.person_id = ass.person_id
175 AND ass.pay_basis_id = ppb.pay_basis_id
176 AND ass.assignment_id = ppp.assignment_id
177 AND ass.primary_flag = ''Y''
178 AND ppp.change_date = ( SELECT MAX(change_date)
179 FROM per_pay_proposals pro
180 WHERE ppp.assignment_id = pro.assignment_id
181 AND pro.change_date <= ''' || to_char(l_report_date) || '''
182 AND pro.approved = ''Y'' )
183 AND ass.organization_id IN (SELECT organization_id
184 FROM hr_all_organization_units
185 WHERE business_group_id = ' || p_business_group_id || '
186 AND ''' || to_char(l_report_date) || ''' BETWEEN date_from AND NVL(date_to,''' || to_char(l_report_date) || ''') )
187 AND ass.assignment_status_type_id = past.assignment_status_type_id
188 AND peo.current_employee_flag = ''Y''
189 AND ass.assignment_type = ''E''
190 AND ''' || to_char(l_report_date) || ''' BETWEEN peo.effective_start_date AND peo.effective_end_date
191 AND ''' || to_char(l_report_date) || ''' BETWEEN ass.effective_start_date AND ass.effective_end_date
192 AND past.per_system_status <> ''TERM_ASSIGN''
193 AND (NOT EXISTS (SELECT ''X''
194 FROM per_jobs job,
195 hr_lookups hl
196 WHERE job.job_information1 = hl.lookup_code
197 AND job.job_id = ass.job_id
198 AND job.job_information_category = ''US''
199 AND hl.lookup_type = ''US_EEO4_JOB_CATEGORIES'')
200 )';
201
202 -- l_query4 will get the employees with Assignment Category information missing
203 --Bug 13849850: Replaced SYSDATE with l_report_date to validate the organization_id of the employee based on l_report_date instead of SYSDATE
204 l_query4 := 'SELECT peo.full_name name,
205 peo.employee_number employee_number,
206 ''Assignment Category information is missing'' exception_reason
207 FROM per_all_people_f peo,
208 per_all_assignments_f ass,
209 per_assignment_status_types past,
210 per_pay_proposals ppp,
211 per_jobs job,
212 per_pay_bases ppb,
213 hr_lookups hl
214 WHERE peo.person_id = ass.person_id
215 AND ass.pay_basis_id = ppb.pay_basis_id
216 AND ass.assignment_id = ppp.assignment_id
217 AND hl.lookup_code = job.job_information1
218 AND job.job_information1 IS NOT NULL
219 AND job.job_information_category = ''US''
220 AND hl.lookup_type = ''US_EEO4_JOB_CATEGORIES''
221 AND ass.job_id = job.job_id
222 AND ass.primary_flag = ''Y''
223 AND ppp.change_date = ( SELECT MAX(change_date)
224 FROM per_pay_proposals pro
225 WHERE ppp.assignment_id = pro.assignment_id
226 AND pro.change_date <= ''' || to_char(l_report_date) || '''
227 AND pro.approved = ''Y'' )
228 AND ass.organization_id IN (SELECT organization_id
229 FROM hr_all_organization_units
230 WHERE business_group_id = ' || p_business_group_id || '
231 AND ''' || to_char(l_report_date) || ''' BETWEEN date_from AND NVL(date_to,''' || to_char(l_report_date) || ''') )
232 AND ass.assignment_status_type_id = past.assignment_status_type_id
233 AND peo.current_employee_flag = ''Y''
234 AND ass.assignment_type = ''E''
235 AND ''' || to_char(l_report_date) || ''' BETWEEN peo.effective_start_date AND peo.effective_end_date
236 AND ''' || to_char(l_report_date) || ''' BETWEEN ass.effective_start_date AND ass.effective_end_date
237 AND past.per_system_status <> ''TERM_ASSIGN''
238 AND ass.employment_category IS NULL';
239
240 -- l_query5 will get the people with EEO Function missing in Job Information
241 --Bug 13849850: Replaced SYSDATE with l_report_date to validate the organization_id of the employee based on l_report_date instead of SYSDATE
242 l_query5 := 'SELECT peo.full_name name,
243 peo.employee_number employee_number,
244 ''EEO Function is missing in Job information'' exception_reason
245 FROM per_all_people_f peo,
246 per_all_assignments_f ass,
247 per_assignment_status_types past,
248 per_pay_proposals ppp,
249 per_jobs job,
250 per_pay_bases ppb,
251 hr_lookups hl
252 WHERE peo.person_id = ass.person_id
253 AND ass.pay_basis_id = ppb.pay_basis_id
254 AND ass.assignment_id = ppp.assignment_id
255 AND hl.lookup_code = job.job_information1
256 AND job.job_information1 IS NOT NULL
257 AND job.job_information_category = ''US''
258 AND hl.lookup_type = ''US_EEO4_JOB_CATEGORIES''
259 AND ass.job_id = job.job_id
260 AND ass.primary_flag = ''Y''
261 AND ppp.change_date = ( SELECT MAX(change_date)
262 FROM per_pay_proposals pro
263 WHERE ppp.assignment_id = pro.assignment_id
264 AND pro.change_date <= ''' || to_char(l_report_date) || '''
265 AND pro.approved = ''Y'' )
266 AND ass.organization_id IN (SELECT organization_id
267 FROM hr_all_organization_units
268 WHERE business_group_id = ' || p_business_group_id || '
269 AND ''' || to_char(l_report_date) || ''' BETWEEN date_from AND NVL(date_to,''' || to_char(l_report_date) || ''') )
270 AND ass.assignment_status_type_id = past.assignment_status_type_id
271 AND peo.current_employee_flag = ''Y''
272 AND ass.assignment_type = ''E''
273 AND ''' || to_char(l_report_date) || ''' BETWEEN peo.effective_start_date AND peo.effective_end_date
274 AND ''' || to_char(l_report_date) || ''' BETWEEN ass.effective_start_date AND ass.effective_end_date
278 -- l_query6 will get the people wih both EEO4 and EEO4A ethnic origins
275 AND past.per_system_status <> ''TERM_ASSIGN''
276 AND job.job_information7 IS NULL';
277
279 --Bug 13849850: Replaced SYSDATE with l_report_date to validate the organization_id of the employee based on l_report_date instead of SYSDATE
280 l_query6 :='SELECT peo.full_name name,
281 peo.employee_number employee_number,
282 ''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
283 FROM per_all_people_f peo,
284 per_all_assignments_f ass,
285 per_assignment_status_types past,
286 per_pay_proposals ppp,
287 per_jobs job,
288 per_pay_bases ppb,
289 hr_lookups hl
290 WHERE peo.person_id = ass.person_id
291 AND ass.pay_basis_id = ppb.pay_basis_id
292 AND ass.assignment_id = ppp.assignment_id
293 AND hl.lookup_code = job.job_information1
294 AND job.job_information1 IS NOT NULL
295 AND job.job_information_category = ''US''
296 AND hl.lookup_type = ''US_EEO4_JOB_CATEGORIES''
297 AND ass.job_id = job.job_id
298 AND ass.primary_flag = ''Y''
299 AND ass.employment_category IS NOT NULL
300 AND ppp.change_date = ( SELECT MAX(change_date)
301 FROM per_pay_proposals pro
302 WHERE ppp.assignment_id = pro.assignment_id
303 AND pro.change_date <= ''' || to_char(l_report_date) || '''
304 AND pro.approved = ''Y'' )
305 AND ass.organization_id IN (SELECT organization_id
306 FROM hr_all_organization_units
307 WHERE business_group_id = ' || p_business_group_id || '
311 AND ass.assignment_type = ''E''
308 AND ''' || to_char(l_report_date) || ''' BETWEEN date_from AND NVL(date_to,''' || to_char(l_report_date) || ''') )
309 AND ass.assignment_status_type_id = past.assignment_status_type_id
310 AND peo.current_employee_flag = ''Y''
312 AND ''' || to_char(l_report_date) || ''' BETWEEN peo.effective_start_date AND peo.effective_end_date
313 AND ''' || to_char(l_report_date) || ''' BETWEEN ass.effective_start_date AND ass.effective_end_date
314 AND past.per_system_status <> ''TERM_ASSIGN''
315 AND (EXISTS (SELECT ''X''
316 FROM per_people_extra_info ppei
317 WHERE ppei.person_id = peo.person_id
318 AND ppei.information_type = ''US_ETHNIC_ORIGIN''
319 AND (DECODE(ppei.pei_information1, ''Y'', 1,0)
320 + DECODE(ppei.pei_information2, ''Y'', 1,0)
321 + DECODE(ppei.pei_information3, ''Y'', 1,0)
322 + DECODE(ppei.pei_information4, ''Y'', 1,0)
323 + DECODE(ppei.pei_information5, ''Y'', 1,0)
324 + DECODE(ppei.pei_information6, ''Y'', 1,0)
325 ) >= 1
326 )
327 )
328 AND (EXISTS (SELECT ''X''
329 FROM per_people_extra_info ppei
330 WHERE ppei.person_id = peo.person_id
331 AND ppei.information_type = ''US_EEO4A_ETHNIC_ORIGIN''
332 AND (DECODE(ppei.pei_information1, ''Y'', 1,0)
333 + DECODE(ppei.pei_information2, ''Y'', 1,0)
334 + DECODE(ppei.pei_information3, ''Y'', 1,0)
335 + DECODE(ppei.pei_information4, ''Y'', 1,0)
336 + DECODE(ppei.pei_information5, ''Y'', 1,0)
337 + DECODE(ppei.pei_information6, ''Y'', 1,0)
338 ) >= 1
339 )
340 )';
341
342 --execute l_query3
343 IF (hr_utility.debug_enabled) THEN
344 FND_FILE.PUT_LINE(FND_FILE.LOG,l_query3);
345 END IF;
346
350 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_xml_string);
347 qryCtx := dbms_xmlgen.newContext (l_query3);
348 l_xml_string := '<?xml version="1.0"?>';
349 FND_FILE.PUT_LINE(FND_FILE.LOG,l_xml_string);
351
352 l_xml_string := '<ROWSET>';
353 FND_FILE.PUT_LINE(FND_FILE.LOG,l_xml_string);
354 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_xml_string);
355
356 --Load 5 records at a time
357 DBMS_XMLGEN.setMaxRows(qryCtx, 5);
358
359 LOOP
360 --save the XML into the CLOB field
361 l_result := DBMS_XMLGEN.getXML(qryCtx);
362
363 --Extract the 5 records
364 l_xml_string := substr( l_result, instr(l_result,'<ROW>',1),instr(l_result,'</ROWSET>',-1) - instr(l_result,'<ROW>',1));
365 EXIT WHEN DBMS_XMLGEN.getNumRowsProcessed(qryCtx) = 0;
366
367 FND_FILE.PUT_LINE(FND_FILE.LOG,l_xml_string);
368 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_xml_string);
369 END LOOP;
370
371 --execute l_query5
372 IF (hr_utility.debug_enabled) THEN
373 FND_FILE.PUT_LINE(FND_FILE.LOG,l_query5);
374 END IF;
375 qryCtx := dbms_xmlgen.newContext (l_query5);
376
377 --Load 5 records at a time
378 DBMS_XMLGEN.setMaxRows(qryCtx, 5);
379
380 LOOP
381 --save the XML into the CLOB field
382 l_result := DBMS_XMLGEN.getXML(qryCtx);
383
384 --Extract the 5 records
385 l_xml_string := substr( l_result, instr(l_result,'<ROW>',1),instr(l_result,'</ROWSET>',-1) - instr(l_result,'<ROW>',1));
386
387 EXIT WHEN DBMS_XMLGEN.getNumRowsProcessed(qryCtx) = 0;
388
389 FND_FILE.PUT_LINE(FND_FILE.LOG,l_xml_string);
390 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_xml_string);
391 END LOOP;
392
393 --execute l_query4
394 IF (hr_utility.debug_enabled) THEN
395 FND_FILE.PUT_LINE(FND_FILE.LOG,l_query4);
396 END IF;
397 qryCtx := dbms_xmlgen.newContext (l_query4);
398
399 --Load 5 records at a time
400 DBMS_XMLGEN.setMaxRows(qryCtx, 5);
401
402 LOOP
403 --save the XML into the CLOB field
404 l_result := DBMS_XMLGEN.getXML(qryCtx);
405
406 --Extract the 5 records
407 l_xml_string := substr( l_result, instr(l_result,'<ROW>',1),instr(l_result,'</ROWSET>',-1) - instr(l_result,'<ROW>',1));
408
409 EXIT WHEN DBMS_XMLGEN.getNumRowsProcessed(qryCtx) = 0;
410
411 FND_FILE.PUT_LINE(FND_FILE.LOG,l_xml_string);
412 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_xml_string);
413 END LOOP;
414
415 --execute l_query1
416 IF (hr_utility.debug_enabled) THEN
417 FND_FILE.PUT_LINE(FND_FILE.LOG,l_query1);
418 END IF;
419 qryCtx := dbms_xmlgen.newContext (l_query1);
420
421 --Load 5 records at a time
422 DBMS_XMLGEN.setMaxRows(qryCtx, 5);
423
424 LOOP
425 --save the XML into the CLOB field
426 l_result := DBMS_XMLGEN.getXML(qryCtx);
427
428 --Extract the 5 records
429 l_xml_string := substr( l_result, instr(l_result,'<ROW>',1),instr(l_result,'</ROWSET>',-1) - instr(l_result,'<ROW>',1));
430
431 EXIT WHEN DBMS_XMLGEN.getNumRowsProcessed(qryCtx) = 0;
432
433 FND_FILE.PUT_LINE(FND_FILE.LOG,l_xml_string);
434 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_xml_string);
435 END LOOP;
436
437 --execute l_query2
438 IF (hr_utility.debug_enabled) THEN
439 FND_FILE.PUT_LINE(FND_FILE.LOG,l_query2);
440 END IF;
441 qryCtx := dbms_xmlgen.newContext (l_query2);
442
443 --Load 5 records at a time
444 DBMS_XMLGEN.setMaxRows(qryCtx, 5);
445
446 LOOP
447 --save the XML into the CLOB field
448 l_result := DBMS_XMLGEN.getXML(qryCtx);
449
450 --Extract the 5 records
451 l_xml_string := substr( l_result, instr(l_result,'<ROW>',1),instr(l_result,'</ROWSET>',-1) - instr(l_result,'<ROW>',1));
452
453 EXIT WHEN DBMS_XMLGEN.getNumRowsProcessed(qryCtx) = 0;
454
455 FND_FILE.PUT_LINE(FND_FILE.LOG,l_xml_string);
456 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_xml_string);
457 END LOOP;
458
459 --execute l_query6
460 IF (hr_utility.debug_enabled) THEN
461 FND_FILE.PUT_LINE(FND_FILE.LOG,l_query6);
462 END IF;
463 qryCtx := dbms_xmlgen.newContext (l_query6);
464
465 --Load 5 records at a time
466 DBMS_XMLGEN.setMaxRows(qryCtx, 5);
467
468 LOOP
469 --save the XML into the CLOB field
470 l_result := DBMS_XMLGEN.getXML(qryCtx);
471
472 --Extract the 5 records
473 l_xml_string := substr( l_result, instr(l_result,'<ROW>',1),instr(l_result,'</ROWSET>',-1) - instr(l_result,'<ROW>',1));
474
475 EXIT WHEN DBMS_XMLGEN.getNumRowsProcessed(qryCtx) = 0;
476
477 FND_FILE.PUT_LINE(FND_FILE.LOG,l_xml_string);
478 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_xml_string);
479 END LOOP;
480
481 -- get the name of the business group.
482 SELECT name into l_business_group_name
483 FROM hr_organization_units
484 WHERE organization_id = p_business_group_id
485 AND business_group_id = p_business_group_id;
486
487 -- generate the header tags
488 l_xml_string := '<C_BUSINESS_GROUP_NAME> '|| l_business_group_name ||' </C_BUSINESS_GROUP_NAME>
489 <C_REPORT_DATE> ' || to_char(l_report_date, 'dd-Mon-yyyy') ||' </C_REPORT_DATE>
490 <C_REPORT_YEAR> ' || p_reporting_year ||' </C_REPORT_YEAR>
491 </ROWSET>';
492
493 FND_FILE.PUT_LINE(FND_FILE.LOG,l_xml_string);
494 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_xml_string);
495
496 END generate_exception_report;
497
498 END PER_US_EEO4A_EXP_PKG;
499
[Home] [Help]