4 ******************************************************************
1 PACKAGE BODY PER_US_IPEDS_EXP_PKG AS
2 /* $Header: perusipedsexp.pkb 120.2.12020000.2 2012/11/29 11:52:02 nkjaladi ship $ */
3 /*
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_IPEDS_EXP_PKG
21 File Name : perusipedsexp.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 11-MAY-2009 nvelaga 115.2 11736960 Modified the package to support the changes
31 115.3 to Ethnic Origin Data capture.
32 Commented the existing l_query and created
33 two new querys l_query1 and l_query2.
34 26-NOV-2012 nkjaladi 115.4 14532440 Modified the IPEDS
35 exception according to
36 2012 IPEDS report changes.
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_report_date VARCHAR2
45 ,p_tenured VARCHAR2 -- #14532440
46 ,p_race_gender VARCHAR2 -- #14532440
47 )
48 IS
49 qryCtx DBMS_XMLGEN.ctxHandle;
50 l_result CLOB;
51
52 -- Added for bug#11736960
53 l_query1 varchar2(10000);
54 l_query2 varchar2(10000);
55 l_query3 varchar2(10000);
56 l_query4 varchar2(10000);
57 l_query5 varchar2(10000);
58 l_query6 varchar2(10000);
59 l_query7 varchar2(10000);
60 l_query8 varchar2(10000);
61 --l_query varchar2(10000);
62 l_reporting_year number(4);
63 l_xml_string varchar2(32000);
64 l_business_group_name varchar2(240);
65 l_report_date date;
66 l_fr VARCHAR2(2000);
67 l_ft VARCHAR2(2000);
68 l_pr VARCHAR2(2000);
69 l_pt VARCHAR2(2000);
70 begin
71
72 FND_FILE.PUT_LINE(FND_FILE.LOG,'p_reporting_date =' || p_report_date);
73 l_report_date := fnd_date.canonical_to_date(p_report_date); --to_date(p_report_date,'YYYY/MM/DD HH24:MI:SS');
74 pqh_employment_category.fetch_empl_categories(p_business_group_id
75 ,l_fr
76 ,l_ft
77 ,l_pr
78 ,l_pt);
82 /* Commented for bug#11736960
79
80 FND_FILE.PUT_LINE(FND_FILE.LOG,'l_reporting_year =' || l_reporting_year);
81
83 l_query := 'SELECT peo.full_name name,
84 peo.employee_number employee_number,
85 decode(peo.per_information1,
86 13, ''Ethnic Origin is "Two or More Races" and "Additional Ethnic Code" is missing'',
87 null, ''Ethnic Origin is missing'') exception_reason
88 FROM per_all_people_f peo,
89 per_all_assignments_f ass,
90 per_assignment_status_types ast,
91 per_jobs job,
92 hr_lookups hl
93 WHERE peo.person_id = ass.person_id
94 AND NOT EXISTS (SELECT 1 FROM per_people_extra_info pei
95 WHERE peo.person_id = pei.person_id
96 AND pei.information_type = ''PER_US_VISA_DETAILS''
97 AND fnd_date.date_to_canonical(''' || to_char(l_report_date) || ''') between pei.pei_information7 and pei.pei_information8
98 AND pei.pei_information9 IN (''04'',''05'',''06'',''07'',''12''))
99 AND peo.current_employee_flag = ''Y''
100 AND hl.lookup_code = job.job_information8
101 AND pqh_employment_category.identify_empl_category(ass.employment_category,
102 ' || l_fr || ',' || l_ft || ','
103 || l_pr ||',' || l_pt || ') IN (''FR'',''PR'')
104 AND hl.lookup_type = ''US_IPEDS_JOB_CATEGORIES''
105 AND job.job_information_category = ''US''
106 AND ''' || to_char(l_report_date) || ''' BETWEEN peo.effective_start_date AND peo.effective_end_date
107 AND ''' || to_char(l_report_date) || ''' BETWEEN ass.effective_start_date AND ass.effective_end_date
108 AND ass.primary_flag = ''Y''
109 AND ass.assignment_status_type_id = ast.assignment_status_type_id
110 AND ast.per_system_status <> ''TERM_ASSIGN''
111 AND ass.job_id = job.job_id
112 AND ass.assignment_type = ''E''
113 AND ass.organization_id IN (
114 SELECT organization_id
115 FROM hr_all_organization_units
116 WHERE business_group_id = '||p_business_group_id || ')
117 AND (peo.per_information1 is null
118 OR (peo.per_information1 =''13''
119 AND not EXISTS (SELECT 1
120 FROM per_people_extra_info ppei
121 WHERE ppei.information_type=''PER_US_ADDL_ETHNIC_CAT''
122 AND ppei.pei_information5 IS not NULL
123 AND ppei.person_id=peo.person_id)
124 )
125 )';
126 */
127
128 -- Added l_query1 and l_query2 for bug#11736960, to support the changes to Ethnic Origin Data capture.
129
130 l_query1 := 'SELECT peo.full_name name,
131 peo.employee_number employee_number,
132 ''Warning: Ethnic Origin(EIT) or Visa Details information is missing, '' ||
133 ''However the employee gets counted in IPEDS report under "Unknown Race"'' exception_reason
134 FROM per_all_people_f peo,
135 per_all_assignments_f ass,
136 per_assignment_status_types ast,
137 per_jobs job,
138 hr_lookups hl
139 WHERE peo.person_id = ass.person_id
140 AND NOT EXISTS (SELECT 1 FROM per_people_extra_info pei
141 WHERE peo.person_id = pei.person_id
142 AND pei.information_type = ''PER_US_VISA_DETAILS''
143 AND fnd_date.date_to_canonical(''' || to_char(l_report_date) || ''') between pei.pei_information7 and pei.pei_information8
144 AND pei.pei_information9 IN (''04'',''05'',''06'',''07'',''12''))
145 AND peo.current_employee_flag = ''Y''
146 AND hl.lookup_code = job.job_information8
147 AND pqh_employment_category.identify_empl_category(ass.employment_category,
148 ' || l_fr || ',' || l_ft || ','
149 || l_pr ||',' || l_pt || ') IN (''FR'',''PR'')
150 AND hl.lookup_type = ''US_IPEDS_JOB_CATEGORIES''
151 AND hl.enabled_flag = ''Y''
152 AND job.job_information_category = ''US''
153 AND ''' || to_char(l_report_date) || ''' BETWEEN peo.effective_start_date AND peo.effective_end_date
154 AND ''' || to_char(l_report_date) || ''' BETWEEN ass.effective_start_date AND ass.effective_end_date
155 AND ass.primary_flag = ''Y''
156 AND ass.assignment_status_type_id = ast.assignment_status_type_id
157 AND ast.per_system_status <> ''TERM_ASSIGN''
158 AND ass.job_id = job.job_id
159 AND ass.assignment_type = ''E''
160 AND ass.organization_id IN (
161 SELECT organization_id
162 FROM hr_all_organization_units
163 WHERE business_group_id = '||p_business_group_id || ')
164 AND NOT EXISTS (SELECT ''X''
165 FROM per_people_extra_info ppei
166 WHERE ppei.information_type = ''US_ETHNIC_ORIGIN''
170 FROM per_people_extra_info ppei
167 AND ppei.person_id = peo.person_id
168 )
169 AND NOT EXISTS(SELECT ''X''
171 WHERE ppei.person_id = peo.person_id
172 AND ppei.information_type = ''PER_US_VISA_DETAILS''
173 AND ''' || to_char(l_report_date) || ''' BETWEEN ppei.pei_information7 AND ppei.pei_information8
174 AND ppei.pei_information9 IN (''04'',''05'',''06'',''07'',''12'')
175 )';
176
177 l_query2 := 'SELECT peo.full_name name,
178 peo.employee_number employee_number,
179 DECODE(
180 (DECODE(ppei.pei_information1, ''Y'', 1, ''N'', 0)
181 + DECODE(ppei.pei_information2, ''Y'', 1, ''N'', 0)
182 + DECODE(ppei.pei_information3, ''Y'', 1, ''N'', 0)
183 + DECODE(ppei.pei_information4, ''Y'', 1, ''N'', 0)
184 + DECODE(ppei.pei_information5, ''Y'', 1, ''N'', 0)
185 + DECODE(ppei.pei_information6, ''Y'', 1, ''N'', 0)
186 ), 0, DECODE(ppei.pei_information7,
187 ''Y'', ''Warning: "Two or More race" field is set to "Yes" and no individual race field is set to "Yes" in Ethnic Origin(EIT), '' ||
188 ''However the employee gets counted in IPEDS report under "Two or More races"'',
189 ''N'', ''Warning: All race fields are set to "No" in Ethnic Origin(EIT), However the employee gets counted in IPEDS report '' ||
190 ''under "Unknown Race"''
191 ),
192 ''Warning: "Two or More race" field is set to "Yes" and only one individual race field is set to "Yes" in Ethnic Origin(EIT), '' ||
193 ''However the employee gets counted in IPEDS report under "Hispanic/Latino" or "Two or More races"''
194 ) exception_reason
195 FROM per_all_people_f peo,
196 per_all_assignments_f ass,
197 per_assignment_status_types ast,
198 per_jobs job,
199 hr_lookups hl,
200 per_people_extra_info ppei
201 WHERE peo.person_id = ass.person_id
202 AND NOT EXISTS (SELECT 1 FROM per_people_extra_info pei
203 WHERE peo.person_id = pei.person_id
204 AND pei.information_type = ''PER_US_VISA_DETAILS''
205 AND fnd_date.date_to_canonical(''' || to_char(l_report_date) || ''') between pei.pei_information7 and pei.pei_information8
206 AND pei.pei_information9 IN (''04'',''05'',''06'',''07'',''12''))
207 AND peo.current_employee_flag = ''Y''
208 AND hl.lookup_code = job.job_information8
209 AND pqh_employment_category.identify_empl_category(ass.employment_category,
210 ' || l_fr || ',' || l_ft || ','
211 || l_pr ||',' || l_pt || ') IN (''FR'',''PR'')
212 AND hl.lookup_type = ''US_IPEDS_JOB_CATEGORIES''
213 AND hl.enabled_flag = ''Y''
214 AND job.job_information_category = ''US''
215 AND ''' || to_char(l_report_date) || ''' BETWEEN peo.effective_start_date AND peo.effective_end_date
216 AND ''' || to_char(l_report_date) || ''' BETWEEN ass.effective_start_date AND ass.effective_end_date
217 AND ass.primary_flag = ''Y''
218 AND ass.assignment_status_type_id = ast.assignment_status_type_id
219 AND ast.per_system_status <> ''TERM_ASSIGN''
220 AND ass.job_id = job.job_id
221 AND ass.assignment_type = ''E''
222 AND ass.organization_id IN (
223 SELECT organization_id
224 FROM hr_all_organization_units
225 WHERE business_group_id = '||p_business_group_id || ')
226 AND ppei.information_type = ''US_ETHNIC_ORIGIN''
227 AND ppei.person_id = peo.person_id
228 AND ((DECODE(ppei.pei_information1, ''Y'', 1, ''N'', 0)
229 + DECODE(ppei.pei_information2, ''Y'', 1, ''N'', 0)
230 + DECODE(ppei.pei_information3, ''Y'', 1, ''N'', 0)
231 + DECODE(ppei.pei_information4, ''Y'', 1, ''N'', 0)
232 + DECODE(ppei.pei_information5, ''Y'', 1, ''N'', 0)
233 + DECODE(ppei.pei_information6, ''Y'', 1, ''N'', 0)
234 ) = 0
235 OR (ppei.pei_information7 = ''Y''
236 AND (DECODE(ppei.pei_information1, ''Y'', 1, ''N'', 0)
237 + DECODE(ppei.pei_information2, ''Y'', 1, ''N'', 0)
238 + DECODE(ppei.pei_information3, ''Y'', 1, ''N'', 0)
239 + DECODE(ppei.pei_information4, ''Y'', 1, ''N'', 0)
240 + DECODE(ppei.pei_information5, ''Y'', 1, ''N'', 0)
241 + DECODE(ppei.pei_information6, ''Y'', 1, ''N'', 0)
242 ) = 1
243 )
244 )
245 AND NOT EXISTS(SELECT ''X''
246 FROM per_people_extra_info ppei
250 AND ppei.pei_information9 IN (''04'',''05'',''06'',''07'',''12'')
247 WHERE ppei.person_id = peo.person_id
248 AND ppei.information_type = ''PER_US_VISA_DETAILS''
249 AND ''' || to_char(l_report_date) || ''' BETWEEN ppei.pei_information7 AND ppei.pei_information8
251 )';
252
253 -- #14831074 changes start
254 -- l_query3 will get the people with invalid Job category.
255 l_query3 := 'SELECT peo.full_name name,
256 peo.employee_number employee_number,
257 ''Job information is missing or is not of IPEDS category'' exception_reason
258 FROM per_all_people_f peo,
259 per_all_assignments_f ass,
260 per_assignment_status_types past
261 WHERE peo.person_id = ass.person_id
262 AND ass.primary_flag = ''Y''
263 AND ass.organization_id IN (SELECT organization_id
264 FROM hr_all_organization_units
265 WHERE business_group_id = ' || p_business_group_id || '
266 AND ''' || to_char(l_report_date) || ''' BETWEEN date_from AND NVL(date_to,''' || to_char(l_report_date) || ''') )
267 AND ass.assignment_status_type_id = past.assignment_status_type_id
268 AND peo.current_employee_flag = ''Y''
269 AND ass.assignment_type = ''E''
270 AND ''' || to_char(l_report_date) || ''' BETWEEN peo.effective_start_date AND peo.effective_end_date
271 AND ''' || to_char(l_report_date) || ''' BETWEEN ass.effective_start_date AND ass.effective_end_date
272 AND past.per_system_status <> ''TERM_ASSIGN''
273 AND peo.business_group_id = '||p_business_group_id || '
274 AND ass.job_id IS NOT NULL
275 AND (NOT EXISTS (SELECT ''X''
276 FROM per_jobs job,
277 hr_lookups hl
278 WHERE job.job_information8 = hl.lookup_code
279 AND job.job_id = ass.job_id
280 AND job.job_information_category = ''US''
281 AND hl.lookup_type = ''US_IPEDS_JOB_CATEGORIES''
282 AND hl.enabled_flag = ''Y''
283 AND hl.lookup_code <> 12)
284 )';
285
286 -- l_query4 will get the people with No Employment category.
287 l_query4 := 'SELECT peo.full_name name,
288 peo.employee_number employee_number,
289 ''Employment Category information is missing'' exception_reason
290 FROM per_all_people_f peo,
291 per_all_assignments_f ass,
292 per_assignment_status_types past,
293 per_jobs job,
294 hr_lookups hl
295 WHERE peo.person_id = ass.person_id
296 AND hl.lookup_code = job.job_information8
297 AND job.job_information8 IS NOT NULL
298 AND job.job_information_category = ''US''
299 AND hl.lookup_type = ''US_IPEDS_JOB_CATEGORIES''
300 AND hl.enabled_flag = ''Y''
301 AND ass.job_id = job.job_id
302 AND ass.primary_flag = ''Y''
303 AND ass.organization_id IN (SELECT organization_id
304 FROM hr_all_organization_units
305 WHERE business_group_id = ' || p_business_group_id || '
306 AND ''' || to_char(l_report_date) || ''' BETWEEN date_from AND NVL(date_to,''' || to_char(l_report_date) || ''') )
307 AND ass.assignment_status_type_id = past.assignment_status_type_id
308 AND peo.current_employee_flag = ''Y''
309 AND ass.assignment_type = ''E''
310 AND peo.business_group_id = '||p_business_group_id || '
311 AND ''' || to_char(l_report_date) || ''' BETWEEN peo.effective_start_date AND peo.effective_end_date
312 AND ''' || to_char(l_report_date) || ''' BETWEEN ass.effective_start_date AND ass.effective_end_date
313 AND past.per_system_status <> ''TERM_ASSIGN''
314 AND ass.employment_category IS NULL';
315
316 -- l_query5 will get the people with invalid Gender.
317 l_query5 := 'SELECT peo.full_name name,
318 peo.employee_number employee_number,
319 ''Gender is missing'' exception_reason
320 FROM per_all_people_f peo,
321 per_all_assignments_f ass,
322 per_assignment_status_types past
323 WHERE peo.person_id = ass.person_id
324 AND ass.primary_flag = ''Y''
325 AND ass.organization_id IN (SELECT organization_id
326 FROM hr_all_organization_units
327 WHERE business_group_id = ' || p_business_group_id || '
328 AND ''' || to_char(l_report_date) || ''' BETWEEN date_from AND NVL(date_to,''' || to_char(l_report_date) || ''') )
329 AND ass.assignment_status_type_id = past.assignment_status_type_id
330 AND peo.current_employee_flag = ''Y''
331 AND ass.assignment_type = ''E''
335 AND past.per_system_status <> ''TERM_ASSIGN''
332 AND peo.business_group_id = '||p_business_group_id || '
333 AND ''' || to_char(l_report_date) || ''' BETWEEN peo.effective_start_date AND peo.effective_end_date
334 AND ''' || to_char(l_report_date) || ''' BETWEEN ass.effective_start_date AND ass.effective_end_date
336 AND peo.sex IS NULL';
337
338 -- l_query6 will get the people with No Job.
339 l_query6 := 'SELECT peo.full_name name,
340 peo.employee_number employee_number,
341 ''Job is not assigned'' exception_reason
342 FROM per_all_people_f peo,
343 per_all_assignments_f ass,
344 per_assignment_status_types past
345 WHERE peo.person_id = ass.person_id
346 AND ass.primary_flag = ''Y''
347 AND ass.organization_id IN (SELECT organization_id
348 FROM hr_all_organization_units
349 WHERE business_group_id = ' || p_business_group_id || '
350 AND ''' || to_char(l_report_date) || ''' BETWEEN date_from AND NVL(date_to,''' || to_char(l_report_date) || ''') )
351 AND ass.assignment_status_type_id = past.assignment_status_type_id
352 AND peo.current_employee_flag = ''Y''
353 AND ass.assignment_type = ''E''
354 AND peo.business_group_id = '||p_business_group_id || '
355 AND ''' || to_char(l_report_date) || ''' BETWEEN peo.effective_start_date AND peo.effective_end_date
356 AND ''' || to_char(l_report_date) || ''' BETWEEN ass.effective_start_date AND ass.effective_end_date
357 AND past.per_system_status <> ''TERM_ASSIGN''
358 AND ass.job_id IS NULL';
359
360 -- l_query7 will get the people if ipeds category is 21-24 and academic rank is blank
361 l_query7 := 'SELECT peo.full_name name,
362 peo.employee_number employee_number,
363 ''Academic Rank is missing for Instruction Job category'' exception_reason
364 FROM per_all_people_f peo,
365 per_all_assignments_f ass,
366 per_assignment_status_types past
367 WHERE peo.person_id = ass.person_id
368 AND ass.primary_flag = ''Y''
369 AND ass.organization_id IN (SELECT organization_id
370 FROM hr_all_organization_units
371 WHERE business_group_id = ' || p_business_group_id || '
372 AND ''' || to_char(l_report_date) || ''' BETWEEN date_from AND NVL(date_to,''' || to_char(l_report_date) || ''') )
373 AND ass.assignment_status_type_id = past.assignment_status_type_id
374 AND peo.current_employee_flag = ''Y''
375 AND ass.assignment_type = ''E''
376 AND ''' || to_char(l_report_date) || ''' BETWEEN peo.effective_start_date AND peo.effective_end_date
377 AND ''' || to_char(l_report_date) || ''' BETWEEN ass.effective_start_date AND ass.effective_end_date
378 AND past.per_system_status <> ''TERM_ASSIGN''
379 AND peo.business_group_id = '||p_business_group_id || '
380 AND (EXISTS (SELECT ''X''
381 FROM per_jobs job,
382 hr_lookups hl
383 WHERE job.job_information8 = hl.lookup_code
384 AND job.job_id = ass.job_id
385 AND job.job_information_category = ''US''
386 AND hl.lookup_type = ''US_IPEDS_JOB_CATEGORIES''
387 AND hl.lookup_code IN (''21'',''22'',''23'',''24'') )
388 )
389 AND (NOT EXISTS (SELECT ''X''
390 FROM per_people_extra_info ppea
391 WHERE ppea.person_id = peo.person_id
392 AND ppea.information_type = ''PQH_ACADEMIC_RANK''
393 AND ppea.pei_information1 IS NOT NULL)
394 )';
395
396 -- l_query8 will get the people if tenure status is "Not on Tenure Track" and contract data doesn't exists
397 l_query8 := 'SELECT peo.full_name name,
398 peo.employee_number employee_number,
399 ''Contract Information is not provided for faculty status Not on Tenure. This employees will not be reported.'' exception_reason
400 FROM per_all_people_f peo
401 ,per_all_assignments_f asg
402 ,per_assignment_status_types ast
403 ,per_jobs job
404 ,per_pay_proposals ppp
405 ,per_pay_bases ppb
406 ,per_people_extra_info ppet
407 WHERE peo.person_id = asg.person_id
408 AND peo.person_id = ppet.person_id
409 AND ppet.information_type = ''PQH_TENURE_STATUS''
410 AND ppet.pei_information1 IN (''03'',''05'')
411 AND peo.current_employee_flag = ''Y''
412 AND pqh_employment_category.identify_empl_category(asg.employment_category,
413 ' || l_fr || ',' || l_ft || ',' || l_pr ||',' || l_pt || ') IS NOT NULL
414 AND job.business_group_id = ' || p_business_group_id || '
415 AND job.job_information_category = ''US''
416 AND job.job_information8 IS NOT NULL
417 AND ''' || to_char(l_report_date) || ''' BETWEEN peo.effective_start_date AND peo.effective_end_date
418 AND ''' || to_char(l_report_date) || ''' BETWEEN asg.effective_start_date AND asg.effective_end_date
419 AND asg.primary_flag = ''Y''
420 AND asg.assignment_status_type_id = ast.assignment_status_type_id
421 AND ast.per_system_status <> ''TERM_ASSIGN''
422 AND asg.pay_basis_id = ppb.pay_basis_id
423 AND asg.assignment_id = ppp.assignment_id
424 AND ppp.change_date = (SELECT MAX(change_date)
425 FROM per_pay_proposals pro
426 WHERE ppp.assignment_id = pro.assignment_id
427 AND pro.change_date <= ''' || to_char(l_report_date) || '''
428 AND pro.approved = ''Y''
429 )
430 AND NVL(ppp.proposed_salary_n,0) * ppb.pay_annualization_factor > 0
431 AND asg.job_id = job.job_id
432 AND asg.assignment_type = ''E''
433 AND peo.business_group_id = '||p_business_group_id || '
434 AND EXISTS (SELECT ''Y''
435 FROM hr_all_organization_units hou
436 WHERE hou.business_group_id = '||p_business_group_id || '
437 AND hou.organization_id = asg.organization_id)
438 AND NOT EXISTS (SELECT ''Y''
439 FROM per_contracts_f pco
440 WHERE pco.person_id = peo.person_id
441 AND ''' || to_char(l_report_date) || ''' BETWEEN pco.effective_start_date AND pco.effective_end_date)';
442
443 -- #14831074 changes end
444
445
446
447 l_xml_string := '<?xml version="1.0"?>';
448
449 FND_FILE.PUT_LINE(FND_FILE.LOG,l_xml_string);
450 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_xml_string);
451
452 l_xml_string := '<ROWSET>';
453 FND_FILE.PUT_LINE(FND_FILE.LOG,l_xml_string);
454 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_xml_string);
455
456 IF p_race_gender = 'Y' THEN --#14831074
457 -- Added for bug#11736960
458 FND_FILE.PUT_LINE(FND_FILE.LOG,l_query1);
459 --FND_FILE.PUT_LINE(FND_FILE.LOG,l_query);
460
461 -- Added for bug#11736960
462 qryCtx := dbms_xmlgen.newContext (l_query1);
463 --qryCtx := dbms_xmlgen.newContext (l_query);
464
465 --Load 5 records at a time
466 DBMS_XMLGEN.setMaxRows(qryCtx, 5);
467 LOOP
468 --save the XML into the CLOB field
469 l_result := DBMS_XMLGEN.getXML(qryCtx);
470 l_xml_string := substr( l_result, instr(l_result,'<ROW>',1),instr(l_result,'</ROWSET>',-1) - instr(l_result,'<ROW>',1));
471 EXIT WHEN DBMS_XMLGEN.getNumRowsProcessed(qryCtx) = 0;
472 FND_FILE.PUT_LINE(FND_FILE.LOG,l_xml_string);
473 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_xml_string);
474
475 END LOOP;
476
477 -- Added for bug#11736960
478 FND_FILE.PUT_LINE(FND_FILE.LOG,l_query2);
479
480 qryCtx := dbms_xmlgen.newContext (l_query2);
481
482 --Load 5 records at a time
483 DBMS_XMLGEN.setMaxRows(qryCtx, 5);
484 LOOP
485 --save the XML into the CLOB field
486 l_result := DBMS_XMLGEN.getXML(qryCtx);
487 l_xml_string := substr( l_result, instr(l_result,'<ROW>',1),instr(l_result,'</ROWSET>',-1) - instr(l_result,'<ROW>',1));
488 EXIT WHEN DBMS_XMLGEN.getNumRowsProcessed(qryCtx) = 0;
489 FND_FILE.PUT_LINE(FND_FILE.LOG,l_xml_string);
490 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_xml_string);
491
492 END LOOP;
493 END IF; --#14831074
494
495 -- Added for bug #14831074 start
496 FND_FILE.PUT_LINE(FND_FILE.LOG,l_query3);
497
498 qryCtx := dbms_xmlgen.newContext (l_query3);
499
500 --Load 5 records at a time
501 DBMS_XMLGEN.setMaxRows(qryCtx, 5);
502 LOOP
503 --save the XML into the CLOB field
504 l_result := DBMS_XMLGEN.getXML(qryCtx);
505 l_xml_string := substr( l_result, instr(l_result,'<ROW>',1),instr(l_result,'</ROWSET>',-1) - instr(l_result,'<ROW>',1));
506 EXIT WHEN DBMS_XMLGEN.getNumRowsProcessed(qryCtx) = 0;
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 LOOP;
511
512 FND_FILE.PUT_LINE(FND_FILE.LOG,l_query4);
513
514 qryCtx := dbms_xmlgen.newContext (l_query4);
515
516 --Load 5 records at a time
517 DBMS_XMLGEN.setMaxRows(qryCtx, 5);
518 LOOP
519 --save the XML into the CLOB field
520 l_result := DBMS_XMLGEN.getXML(qryCtx);
521 l_xml_string := substr( l_result, instr(l_result,'<ROW>',1),instr(l_result,'</ROWSET>',-1) - instr(l_result,'<ROW>',1));
522 EXIT WHEN DBMS_XMLGEN.getNumRowsProcessed(qryCtx) = 0;
523 FND_FILE.PUT_LINE(FND_FILE.LOG,l_xml_string);
524 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_xml_string);
525
526 END LOOP;
527
528 FND_FILE.PUT_LINE(FND_FILE.LOG,l_query5);
529
530 qryCtx := dbms_xmlgen.newContext (l_query5);
531
532 --Load 5 records at a time
533 DBMS_XMLGEN.setMaxRows(qryCtx, 5);
534 LOOP
535 --save the XML into the CLOB field
536 l_result := DBMS_XMLGEN.getXML(qryCtx);
537 l_xml_string := substr( l_result, instr(l_result,'<ROW>',1),instr(l_result,'</ROWSET>',-1) - instr(l_result,'<ROW>',1));
538 EXIT WHEN DBMS_XMLGEN.getNumRowsProcessed(qryCtx) = 0;
539 FND_FILE.PUT_LINE(FND_FILE.LOG,l_xml_string);
540 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_xml_string);
541
542 END LOOP;
543
544 FND_FILE.PUT_LINE(FND_FILE.LOG,l_query6);
545
546 qryCtx := dbms_xmlgen.newContext (l_query6);
547
548 --Load 5 records at a time
549 DBMS_XMLGEN.setMaxRows(qryCtx, 5);
550 LOOP
551 --save the XML into the CLOB field
552 l_result := DBMS_XMLGEN.getXML(qryCtx);
553 l_xml_string := substr( l_result, instr(l_result,'<ROW>',1),instr(l_result,'</ROWSET>',-1) - instr(l_result,'<ROW>',1));
554 EXIT WHEN DBMS_XMLGEN.getNumRowsProcessed(qryCtx) = 0;
555 FND_FILE.PUT_LINE(FND_FILE.LOG,l_xml_string);
556 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_xml_string);
557
558 END LOOP;
559
560 FND_FILE.PUT_LINE(FND_FILE.LOG,l_query7);
561
562 qryCtx := dbms_xmlgen.newContext (l_query7);
563
564 --Load 5 records at a time
565 DBMS_XMLGEN.setMaxRows(qryCtx, 5);
566 LOOP
567 --save the XML into the CLOB field
568 l_result := DBMS_XMLGEN.getXML(qryCtx);
569 l_xml_string := substr( l_result, instr(l_result,'<ROW>',1),instr(l_result,'</ROWSET>',-1) - instr(l_result,'<ROW>',1));
570 EXIT WHEN DBMS_XMLGEN.getNumRowsProcessed(qryCtx) = 0;
571 FND_FILE.PUT_LINE(FND_FILE.LOG,l_xml_string);
572 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_xml_string);
573
574 END LOOP;
575
576 IF (NVL(p_tenured,'N') = 'Y') THEN
577 FND_FILE.PUT_LINE(FND_FILE.LOG,l_query8);
578
579 qryCtx := dbms_xmlgen.newContext (l_query8);
580
581 --Load 5 records at a time
582 DBMS_XMLGEN.setMaxRows(qryCtx, 5);
583 LOOP
584 --save the XML into the CLOB field
585 l_result := DBMS_XMLGEN.getXML(qryCtx);
586 l_xml_string := substr( l_result, instr(l_result,'<ROW>',1),instr(l_result,'</ROWSET>',-1) - instr(l_result,'<ROW>',1));
587 EXIT WHEN DBMS_XMLGEN.getNumRowsProcessed(qryCtx) = 0;
588 FND_FILE.PUT_LINE(FND_FILE.LOG,l_xml_string);
589 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_xml_string);
590 END LOOP;
591 END IF;
592 -- Added for bug #14831074 end
593
594 -- Get Name of the business group.
595 SELECT name into l_business_group_name from hr_organization_units
596 WHERE organization_id = p_business_group_id
597 AND business_group_id = p_business_group_id;
598
599 -- Load Header tags
600 l_xml_string := '<C_BUSINESS_GROUP_NAME> '|| l_business_group_name ||' </C_BUSINESS_GROUP_NAME>
601 <C_REPORT_DATE> ' || to_char(l_report_date, 'dd-Mon-yyyy') ||' </C_REPORT_DATE>
602 <C_REPORT_YEAR> ' || to_char(l_report_date, 'yyyy') ||' </C_REPORT_YEAR>
603 </ROWSET>';
604 FND_FILE.PUT_LINE(FND_FILE.LOG,l_xml_string);
605 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_xml_string);
606
607 end generate_exception_report;
608
609 END PER_US_IPEDS_EXP_PKG;
610
611
[Home] [Help]