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