1 PACKAGE BODY PER_US_IPEDS_EXP_PKG AS
2 /* $Header: perusipedsexp.pkb 120.1 2007/07/26 14:13:02 jdevasah noship $ */
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_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
31 ****************************************************************************/
32
33
34 procedure generate_exception_report( errbuf OUT NOCOPY VARCHAR2
35 ,retcode OUT NOCOPY NUMBER
36 ,p_business_group_id varchar2
37 , p_report_date varchar2) is
38
39 qryCtx DBMS_XMLGEN.ctxHandle;
40 l_result CLOB;
41 l_query varchar2(10000);
42 l_reporting_year number(4);
43 l_xml_string varchar2(32000);
44 l_business_group_name varchar2(240);
45 l_report_date date;
46 l_fr VARCHAR2(2000);
47 l_ft VARCHAR2(2000);
48 l_pr VARCHAR2(2000);
49 l_pt VARCHAR2(2000);
50 begin
51
52 FND_FILE.PUT_LINE(FND_FILE.LOG,'p_reporting_date =' || p_report_date);
53 l_report_date := fnd_date.canonical_to_date(p_report_date); --to_date(p_report_date,'YYYY/MM/DD HH24:MI:SS');
54 pqh_employment_category.fetch_empl_categories(p_business_group_id
55 ,l_fr
56 ,l_ft
57 ,l_pr
58 ,l_pt);
59
60 FND_FILE.PUT_LINE(FND_FILE.LOG,'l_reporting_year =' || l_reporting_year);
61 l_query := 'SELECT peo.full_name name,
62 peo.employee_number employee_number,
63 decode(peo.per_information1,
64 13, ''Ethnic Origin is "Two or More Races" and "Additional Ethnic Code" is missing'',
65 null, ''Ethnic Origin is missing'') exception_reason
66 FROM per_all_people_f peo,
67 per_all_assignments_f ass,
68 per_assignment_status_types ast,
69 per_jobs job,
70 hr_lookups hl
71 WHERE peo.person_id = ass.person_id
72 AND NOT EXISTS (SELECT 1 FROM per_people_extra_info pei
73 WHERE peo.person_id = pei.person_id
74 AND pei.information_type = ''PER_US_VISA_DETAILS''
75 AND fnd_date.date_to_canonical(''' || to_char(l_report_date) || ''') between pei.pei_information7 and pei.pei_information8
76 AND pei.pei_information9 IN (''04'',''05'',''06'',''07'',''12''))
77 AND peo.current_employee_flag = ''Y''
78 AND hl.lookup_code = job.job_information8
79 AND pqh_employment_category.identify_empl_category(ass.employment_category,
80 ' || l_fr || ',' || l_ft || ','
81 || l_pr ||',' || l_pt || ') IN (''FR'',''PR'')
82 AND hl.lookup_type = ''US_IPEDS_JOB_CATEGORIES''
83 AND job.job_information_category = ''US''
84 AND ''' || to_char(l_report_date) || ''' BETWEEN peo.effective_start_date AND peo.effective_end_date
85 AND ''' || to_char(l_report_date) || ''' BETWEEN ass.effective_start_date AND ass.effective_end_date
86 AND ass.primary_flag = ''Y''
87 AND ass.assignment_status_type_id = ast.assignment_status_type_id
88 AND ast.per_system_status <> ''TERM_ASSIGN''
89 AND ass.job_id = job.job_id
90 AND ass.assignment_type = ''E''
91 AND ass.organization_id IN (
92 SELECT organization_id
93 FROM hr_all_organization_units
94 WHERE business_group_id = '||p_business_group_id || ')
95 AND (peo.per_information1 is null
96 OR (peo.per_information1 =''13''
97 AND not EXISTS (SELECT 1
98 FROM per_people_extra_info ppei
99 WHERE ppei.information_type=''PER_US_ADDL_ETHNIC_CAT''
100 AND ppei.pei_information5 IS not NULL
101 AND ppei.person_id=peo.person_id)
102 )
103 )';
104
105 FND_FILE.PUT_LINE(FND_FILE.LOG,l_query);
106
107 qryCtx := dbms_xmlgen.newContext (l_query);
108
109 l_xml_string := '<?xml version="1.0"?>';
110 FND_FILE.PUT_LINE(FND_FILE.LOG,l_xml_string);
111 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_xml_string);
112
113 l_xml_string := '<ROWSET>';
114 FND_FILE.PUT_LINE(FND_FILE.LOG,l_xml_string);
115 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_xml_string);
116
117 --Load 5 records at a time
118 DBMS_XMLGEN.setMaxRows(qryCtx, 5);
119 LOOP
120 --save the XML into the CLOB field
121 l_result := DBMS_XMLGEN.getXML(qryCtx);
122 l_xml_string := substr( l_result, instr(l_result,'<ROW>',1),instr(l_result,'</ROWSET>',-1) - instr(l_result,'<ROW>',1));
123 EXIT WHEN DBMS_XMLGEN.getNumRowsProcessed(qryCtx) = 0;
124 FND_FILE.PUT_LINE(FND_FILE.LOG,l_xml_string);
125 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_xml_string);
126
127 END LOOP;
128
129 -- Get Name of the business group.
130 SELECT name into l_business_group_name from hr_organization_units
131 WHERE organization_id = p_business_group_id
132 AND business_group_id = p_business_group_id;
133
134 -- Load Header tags
135 l_xml_string := '<C_BUSINESS_GROUP_NAME> '|| l_business_group_name ||' </C_BUSINESS_GROUP_NAME>
136 <C_REPORT_DATE> ' || to_char(l_report_date, 'dd-Mon-yyyy') ||' </C_REPORT_DATE>
137 <C_REPORT_YEAR> ' || to_char(l_report_date, 'yyyy') ||' </C_REPORT_YEAR>
138 </ROWSET>';
139 FND_FILE.PUT_LINE(FND_FILE.LOG,l_xml_string);
140 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_xml_string);
141
142 end generate_exception_report;
143
144 END PER_US_IPEDS_EXP_PKG;
145
146
[Home] [Help]