[Home] [Help]
PACKAGE BODY: APPS.PER_US_EEO5_EXP_PKG
Source
1 PACKAGE BODY PER_US_EEO5_EXP_PKG AS
2 /* $Header: peruseeo5exp.pkb 120.3 2011/06/03 09:05:01 nvelaga 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 *
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_EEO5_EXP_PKG
21 File Name : peruseeo5exp.pkb
22
23 Description : This package creates XML file for EEO5 exception Report.
24
25 Change List
26 -----------
27 Date Name Vers Bug No Description
28 ----------- ---------- ------ ------- --------------------------
29 26-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 Commented the existing l_query and created
33 two new querys l_query1 and l_query2.
34 03-JUN-2011 nvelaga 115.3 12570805 Added 2 queries l_query3 and l_query4
35 to report employees with invalid Job
36 or employment category.
37
38 ****************************************************************************/
39
40
41 procedure generate_exception_report( errbuf OUT NOCOPY VARCHAR2
42 ,retcode OUT NOCOPY NUMBER
43 ,p_business_group_id varchar2
44 , p_reporting_year number) is
45
46 qryCtx DBMS_XMLGEN.ctxHandle;
47 l_result CLOB;
48 -- Commented for bug#11736960
49 --l_query varchar2(10000);
50 l_report_date date;
51 l_xml_string varchar2(32000);
52 l_business_group_name varchar2(240);
53
54 -- Added for bug#11736960
55 l_query1 varchar2(10000);
56 l_query2 varchar2(10000);
57 -- Added for bug#12570805
58 l_query3 varchar2(10000);
59 l_query4 varchar2(10000);
60
61 l_fr VARCHAR2(2000);
62 l_ft VARCHAR2(2000);
63 l_pr VARCHAR2(2000);
64 l_pt VARCHAR2(2000);
65 begin
66 l_report_date := to_date ('30-09'|| p_reporting_year,'dd-mm-yyyy');
67 pqh_employment_category.fetch_empl_categories(p_business_group_id
68 ,l_fr
69 ,l_ft
70 ,l_pr
71 ,l_pt);
72
73 -- Added l_query1 and l_query2 for bug#11736960, to support the changes to Ethnic Origin Data capture.
74
75 -- l_query1 will get the people for whom Ethnic Origin information is not entered.
76 l_query1 := 'SELECT peo.full_name name,
77 peo.employee_number employee_number,
78 ''Ethnic Origin (EIT) information is missing'' exception_reason
79 FROM per_all_people_f peo,
80 per_all_assignments_f ass,
81 per_assignment_status_types ast,
82 per_jobs job,
83 hr_lookups hl
87 AND pqh_employment_category.identify_empl_category(ass.employment_category,
84 WHERE peo.person_id = ass.person_id
85 AND peo.current_employee_flag = ''Y''
86 AND hl.lookup_code = job.job_information1
88 ' || l_fr || ',' || l_ft || ','
89 || l_pr ||',' || l_pt || ') IN (''FR'',''PR'')
90 AND hl.lookup_type = ''US_EEO5_JOB_CATEGORIES''
91 AND job.job_information_category = ''US''
92 AND ''' || to_char(l_report_date) || ''' BETWEEN peo.effective_start_date AND peo.effective_end_date
93 AND ''' || to_char(l_report_date) || ''' BETWEEN ass.effective_start_date AND ass.effective_end_date
94 AND ass.primary_flag = ''Y''
95 AND ass.assignment_status_type_id = ast.assignment_status_type_id
96 AND ast.per_system_status <> ''TERM_ASSIGN''
97 AND ass.job_id = job.job_id
98 AND ass.assignment_type = ''E''
99 AND ass.organization_id IN (
100 SELECT organization_id
101 FROM hr_all_organization_units
102 WHERE business_group_id = '||p_business_group_id || ')
103 AND (NOT EXISTS (SELECT ''X''
104 FROM per_people_extra_info ppei
105 WHERE ppei.person_id = peo.person_id
106 AND ppei.information_type = ''US_ETHNIC_ORIGIN'')
107 )';
108
109 -- l_query2 will get the people with invalid Ethnic Origin information.
110 l_query2 := 'SELECT peo.full_name name,
111 peo.employee_number employee_number,
112 DECODE((DECODE(ppei.pei_information1, ''Y'', 1, ''N'', 0)
113 + DECODE(ppei.pei_information2, ''Y'', 1, ''N'', 0)
114 + DECODE(ppei.pei_information3, ''Y'', 1, ''N'', 0)
115 + DECODE(ppei.pei_information4, ''Y'', 1, ''N'', 0)
116 + DECODE(ppei.pei_information5, ''Y'', 1, ''N'', 0)
117 + DECODE(ppei.pei_information6, ''Y'', 1, ''N'', 0)
118 ), 0, ''No individual race field is set to yes in Ethnic Origin (EIT)'',
119 ''More than one individual race field is set to yes in Ethnic Origin (EIT)'') exception_reason
120 FROM per_all_people_f peo,
121 per_all_assignments_f ass,
122 per_assignment_status_types ast,
123 per_jobs job,
124 hr_lookups hl,
125 per_people_extra_info ppei
126 WHERE peo.person_id = ass.person_id
127 AND peo.current_employee_flag = ''Y''
128 AND hl.lookup_code = job.job_information1
129 AND pqh_employment_category.identify_empl_category(ass.employment_category,
130 ' || l_fr || ',' || l_ft || ','
131 || l_pr ||',' || l_pt || ') IN (''FR'',''PR'')
132 AND hl.lookup_type = ''US_EEO5_JOB_CATEGORIES''
133 AND job.job_information_category = ''US''
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 ass.primary_flag = ''Y''
137 AND ass.assignment_status_type_id = ast.assignment_status_type_id
138 AND ast.per_system_status <> ''TERM_ASSIGN''
139 AND ass.job_id = job.job_id
140 AND ass.assignment_type = ''E''
141 AND ass.organization_id IN (
142 SELECT organization_id
143 FROM hr_all_organization_units
144 WHERE business_group_id = '||p_business_group_id || ')
145 AND ppei.person_id = peo.person_id
146 AND ppei.information_type = ''US_ETHNIC_ORIGIN''
147 AND (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 ) != 1';
154
155 -- l_query3 will get the people with invalid Job Category.
156 l_query3 := 'SELECT peo.full_name name,
157 peo.employee_number employee_number,
158 ''Job information is missing or is not of EEO-5 category'' exception_reason
159 FROM per_all_people_f peo,
160 per_all_assignments_f ass,
161 per_assignment_status_types ast
162 WHERE peo.person_id = ass.person_id
163 AND peo.current_employee_flag = ''Y''
164 AND ''' || to_char(l_report_date) || ''' BETWEEN peo.effective_start_date AND peo.effective_end_date
165 AND ''' || to_char(l_report_date) || ''' BETWEEN ass.effective_start_date AND ass.effective_end_date
166 AND ass.primary_flag = ''Y''
167 AND ass.assignment_status_type_id = ast.assignment_status_type_id
168 AND ast.per_system_status <> ''TERM_ASSIGN''
172 FROM hr_all_organization_units
169 AND ass.assignment_type = ''E''
170 AND ass.organization_id IN (
171 SELECT organization_id
173 WHERE business_group_id = '||p_business_group_id || ')
174 AND (NOT EXISTS (SELECT ''X''
175 FROM per_jobs job,
176 hr_lookups hl
177 WHERE job.job_information1 = hl.lookup_code
178 AND job.job_id = ass.job_id
179 AND job.job_information_category = ''US''
180 AND hl.lookup_type = ''US_EEO5_JOB_CATEGORIES'')
181 )';
182
183 -- l_query4 will get the people with invalid Employment Category.
184 l_query4 := 'SELECT peo.full_name name,
185 peo.employee_number employee_number,
186 ''Employment Category inforamtion is missing or is not of reporting category'' exception_reason
187 FROM per_all_people_f peo,
188 per_all_assignments_f ass,
189 per_assignment_status_types ast,
190 per_jobs job,
191 hr_lookups hl
192 WHERE peo.person_id = ass.person_id
193 AND peo.current_employee_flag = ''Y''
194 AND hl.lookup_code = job.job_information1
195 AND hl.lookup_type = ''US_EEO5_JOB_CATEGORIES''
196 AND job.job_information_category = ''US''
197 AND ''' || to_char(l_report_date) || ''' BETWEEN peo.effective_start_date AND peo.effective_end_date
198 AND ''' || to_char(l_report_date) || ''' BETWEEN ass.effective_start_date AND ass.effective_end_date
199 AND ass.primary_flag = ''Y''
200 AND ass.assignment_status_type_id = ast.assignment_status_type_id
201 AND ast.per_system_status <> ''TERM_ASSIGN''
202 AND ass.job_id = job.job_id
203 AND ass.assignment_type = ''E''
204 AND ass.organization_id IN (
205 SELECT organization_id
206 FROM hr_all_organization_units
207 WHERE business_group_id = '||p_business_group_id || ')
208 AND pqh_employment_category.identify_empl_category(ass.employment_category,
209 ' || l_fr || ',' || l_ft || ','
210 || l_pr ||',' || l_pt || ') NOT IN (''FR'',''PR'')';
211
212 /* Commneted for bug#11736960 starts
213 l_query := 'SELECT peo.full_name name,
214 peo.employee_number employee_number,
215 decode(peo.per_information1,
216 13, ''Ethnic Origin is "Two or More Races" and "Additional Ethnic Code" is missing'',
217 null, ''Ethnic Origin is missing'') exception_reason
218 FROM per_all_people_f peo,
219 per_all_assignments_f ass,
220 per_assignment_status_types ast,
221 per_jobs job,
222 hr_lookups hl
223 WHERE peo.person_id = ass.person_id
224 AND peo.current_employee_flag = ''Y''
225 AND hl.lookup_code = job.job_information1
226 AND pqh_employment_category.identify_empl_category(ass.employment_category,
227 ' || l_fr || ',' || l_ft || ','
228 || l_pr ||',' || l_pt || ') IN (''FR'',''PR'')
229 AND hl.lookup_type = ''US_EEO5_JOB_CATEGORIES''
230 AND job.job_information_category = ''US''
231 AND ''' || to_char(l_report_date) || ''' BETWEEN peo.effective_start_date AND peo.effective_end_date
232 AND ''' || to_char(l_report_date) || ''' BETWEEN ass.effective_start_date AND ass.effective_end_date
233 AND ass.primary_flag = ''Y''
234 AND ass.assignment_status_type_id = ast.assignment_status_type_id
235 AND ast.per_system_status <> ''TERM_ASSIGN''
236 AND ass.job_id = job.job_id
237 AND ass.assignment_type = ''E''
238 AND ass.organization_id IN (
239 SELECT organization_id
240 FROM hr_all_organization_units
241 WHERE business_group_id = '||p_business_group_id || ')
242 AND (peo.per_information1 is null
243 OR (peo.per_information1 =''13''
244 AND not EXISTS (SELECT 1
245 FROM per_people_extra_info ppei
246 WHERE ppei.information_type=''PER_US_ADDL_ETHNIC_CAT''
247 AND ppei.pei_information5 IS not NULL
248 AND ppei.person_id=peo.person_id)
249 )
250 )';
251 Commneted for bug#11736960 ends */
252
253 -- Added for bug#12570805
254 FND_FILE.PUT_LINE(FND_FILE.LOG,l_query3);
255 qryCtx := dbms_xmlgen.newContext (l_query3);
256
257 l_xml_string := '<?xml version="1.0"?>';
258 FND_FILE.PUT_LINE(FND_FILE.LOG,l_xml_string);
259 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_xml_string);
260
261 l_xml_string := '<ROWSET>';
262 FND_FILE.PUT_LINE(FND_FILE.LOG,l_xml_string);
263 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_xml_string);
264
265 --Load 5 records at a time
266 DBMS_XMLGEN.setMaxRows(qryCtx, 5);
267 LOOP
268 --save the XML into the CLOB field
269 l_result := DBMS_XMLGEN.getXML(qryCtx);
270 l_xml_string := substr( l_result, instr(l_result,'<ROW>',1),instr(l_result,'</ROWSET>',-1) - instr(l_result,'<ROW>',1));
271 --insert into tab_clob values (to_char(l_xml_string));
272 EXIT WHEN DBMS_XMLGEN.getNumRowsProcessed(qryCtx) = 0;
273 FND_FILE.PUT_LINE(FND_FILE.LOG,l_xml_string);
274 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_xml_string);
275
276 END LOOP;
277
278 -- Added for bug#12570805
279 FND_FILE.PUT_LINE(FND_FILE.LOG,l_query4);
280 qryCtx := dbms_xmlgen.newContext (l_query4);
281
282 --Load 5 records at a time
283 DBMS_XMLGEN.setMaxRows(qryCtx, 5);
284 LOOP
285 --save the XML into the CLOB field
286 l_result := DBMS_XMLGEN.getXML(qryCtx);
287 l_xml_string := substr( l_result, instr(l_result,'<ROW>',1),instr(l_result,'</ROWSET>',-1) - instr(l_result,'<ROW>',1));
288 --insert into tab_clob values (to_char(l_xml_string));
289 EXIT WHEN DBMS_XMLGEN.getNumRowsProcessed(qryCtx) = 0;
290 FND_FILE.PUT_LINE(FND_FILE.LOG,l_xml_string);
291 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_xml_string);
292
293 END LOOP;
294
295 -- Added for bug#11736960
296 FND_FILE.PUT_LINE(FND_FILE.LOG,l_query1);
297 --FND_FILE.PUT_LINE(FND_FILE.LOG,l_query);
298
299 -- Added for bug#11736960
300 qryCtx := dbms_xmlgen.newContext (l_query1);
301 --qryCtx := dbms_xmlgen.newContext (l_query);
302
303 --Load 5 records at a time
304 DBMS_XMLGEN.setMaxRows(qryCtx, 5);
305 LOOP
306 --save the XML into the CLOB field
307 l_result := DBMS_XMLGEN.getXML(qryCtx);
308 l_xml_string := substr( l_result, instr(l_result,'<ROW>',1),instr(l_result,'</ROWSET>',-1) - instr(l_result,'<ROW>',1));
309 --insert into tab_clob values (to_char(l_xml_string));
310 EXIT WHEN DBMS_XMLGEN.getNumRowsProcessed(qryCtx) = 0;
311 FND_FILE.PUT_LINE(FND_FILE.LOG,l_xml_string);
312 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_xml_string);
313
314 END LOOP;
315
316 -- Added for bug#11736960
317 FND_FILE.PUT_LINE(FND_FILE.LOG,l_query2);
318
319 qryCtx := dbms_xmlgen.newContext (l_query2);
320
321 --Load 5 records at a time
322 DBMS_XMLGEN.setMaxRows(qryCtx, 5);
323 LOOP
324 --save the XML into the CLOB field
325 l_result := DBMS_XMLGEN.getXML(qryCtx);
326 l_xml_string := substr( l_result, instr(l_result,'<ROW>',1),instr(l_result,'</ROWSET>',-1) - instr(l_result,'<ROW>',1));
327 --insert into tab_clob values (to_char(l_xml_string));
328 EXIT WHEN DBMS_XMLGEN.getNumRowsProcessed(qryCtx) = 0;
329 FND_FILE.PUT_LINE(FND_FILE.LOG,l_xml_string);
330 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_xml_string);
331
332 END LOOP;
333
334 -- Get Name of the business group.
335 SELECT name into l_business_group_name from hr_organization_units
336 WHERE organization_id = p_business_group_id
337 AND business_group_id = p_business_group_id;
338
339 -- Load Header tags
340 l_xml_string := '<C_BUSINESS_GROUP_NAME> '|| l_business_group_name ||' </C_BUSINESS_GROUP_NAME>
341 <C_REPORT_DATE> ' || to_char(l_report_date, 'dd-Mon-yyyy') ||' </C_REPORT_DATE>
342 <C_REPORT_YEAR> ' || p_reporting_year ||' </C_REPORT_YEAR>
343 </ROWSET>';
344 FND_FILE.PUT_LINE(FND_FILE.LOG,l_xml_string);
345 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_xml_string);
346
347 end generate_exception_report;
348
349 END PER_US_EEO5_EXP_PKG;
350
351