1 PACKAGE PER_PERUSEO1_XMLP_PKG AUTHID CURRENT_USER AS
2 /* $Header: PERUSEO1S.pls 120.3 2011/06/01 06:16:53 emunisek ship $ */
3 P_HIERARCHY_VERSION_ID NUMBER;
4
5 P_PAYROLL_PERIOD_DATE_START VARCHAR2(32767);
6 P_PAYROLL_PERIOD_DATE_START_T VARCHAR2(32767);
7
8 P_BUSINESS_GROUP_ID NUMBER;
9
10 P_HIERARCHY_ID NUMBER;
11
12 P_MINIMUM_NO_OF_EMPLOYEES NUMBER;
13
14 P_REPORT_YEAR VARCHAR2(4);
15
16 P_PAYROLL_PERIOD_DATE_END VARCHAR2(32767);
17 P_PAYROLL_PERIOD_DATE_END_T VARCHAR2(32767);
18
19 P_PAYROLL_PERIOD_DATE VARCHAR2(32767);
20
21 P_CONC_REQUEST_ID NUMBER;
22
23 P_REPORT_MODE VARCHAR2(1);
24
25 P_AUDIT_REPORT VARCHAR2(1);
26
27 C_DEF_SIC VARCHAR2(20);
28
29 C_DEF_NAICS VARCHAR2(20);
30
31 C_DEF_GRE VARCHAR2(20);
32
33 C_DEF_DUNS VARCHAR2(20);
34
35 C_DEF_GOV_CON VARCHAR2(3);
36
37 C_DEF_APPRENT VARCHAR2(3);
38
39 C_DEF_ACTIV_1 VARCHAR2(80);
40
41 C_DEF_ACTIV_2 VARCHAR2(80);
42
43 C_DEF_ACTIV_3 VARCHAR2(80);
44
45 C_DEF_ACTIV_4 VARCHAR2(80);
46
47 CP_REPORT_YEAR VARCHAR2(4);
48
49 CP_TOT_EMPS NUMBER;
50
51 C_100_EMPS VARCHAR2(1);
52
53 C_EST_ACTIV_1 VARCHAR2(80);
54
55 C_EST_ACTIV_2 VARCHAR2(80);
56
57 C_EST_ACTIV_3 VARCHAR2(80);
58
59 C_EST_ACTIV_4 VARCHAR2(80);
60
61 C_AFFILIATED VARCHAR2(1);
62
63 CP_PAYROLL_PERIOD_DATE_START DATE;
64
65 CP_PAYROLL_PERIOD_DATE_END DATE;
66
67 CP_REPORT_TYPE VARCHAR2(80);
68
69 CP_HW_AUD_MALE NUMBER;
70
71 CP_HW_AUD_FEMALE NUMBER;
72
73 CP_HW_AUD_TOT NUMBER;
74
75 CP_HW_CAT NUMBER;
76
77 CP_HW_FEMALE NUMBER;
78
79 CP_HW_MALE NUMBER;
80
81 C_BUSINESS_GROUP_NAME VARCHAR2(60);
82
83 C_HIERARCHY_NAME VARCHAR2(40);
84
85 C_HIERARCHY_VERSION_NUM NUMBER;
86
87 C_PARENT_ORG_ID NUMBER;
88
89 C_PARENT_NODE_ID NUMBER;
90
91 CP_PREV_YEAR_FILED VARCHAR2(4) := '0';
92
93 C_PRIOD_END_DATE DATE;
94
95 C_ALL_TOTAL NUMBER := 0;
96
97 C_NO_OF_ESTABLISHMENTS NUMBER;
98
99 C_PAYROLL_PERIOD_DATE_START DATE;
100
101 C_PAYROLL_PERIOD_DATE_END DATE;
102
103 C_REPORT_MODE VARCHAR2(1);
104
105 C_REPORT_YEAR NUMBER;
106
107 /*Changes for Bug#12604545. Created the function MAINTAIN_EST_EMP_COUNTS
108 to resolve the issue of information not getting saved to "EEO1 Individual
109 Establishment Archive Data" Location Extra Information when "EEO Individual
110 Establishment Report (XML) was run in Final Mode */
111
112 TYPE EST_COUNT_REC IS RECORD
113 ( YEAR CHAR(4),
114 TOT_CAT NUMBER(15),
115 TOT_WMALE NUMBER(15),
116 TOT_BMALE NUMBER(15),
117 TOT_HMALE NUMBER(15),
118 TOT_AMALE NUMBER(15),
119 TOT_IMALE NUMBER(15),
120 TOT_WFEMALE NUMBER(15),
121 TOT_BFEMALE NUMBER(15),
122 TOT_HFEMALE NUMBER(15),
123 TOT_AFEMALE NUMBER(15),
124 TOT_IFEMALE NUMBER(15),
125 TOT_HLMALE NUMBER(15),
126 TOT_HLFEMALE NUMBER(15),
127 TOT_TMRACESMALE NUMBER(15),
128 TOT_TMRACESFEMALE NUMBER(15));
129
130 TYPE EST_COUNT_TABLE IS TABLE OF EST_COUNT_REC
131 INDEX BY BINARY_INTEGER;
132
133 G_EST_COUNT_TABLE EST_COUNT_TABLE;
134
135 FUNCTION MAINTAIN_EST_EMP_COUNTS (C_MODE IN VARCHAR2,
136 C_EST_NODE_ID IN NUMBER DEFAULT 0,
137 C_TOT_CAT IN NUMBER DEFAULT 0,
138 C_TOT_WMALE IN NUMBER DEFAULT 0,
139 C_TOT_BMALE IN NUMBER DEFAULT 0,
140 C_TOT_HMALE IN NUMBER DEFAULT 0,
141 C_TOT_AMALE IN NUMBER DEFAULT 0,
142 C_TOT_IMALE IN NUMBER DEFAULT 0,
143 C_TOT_WFEMALE IN NUMBER DEFAULT 0,
144 C_TOT_BFEMALE IN NUMBER DEFAULT 0,
145 C_TOT_HFEMALE IN NUMBER DEFAULT 0,
146 C_TOT_AFEMALE IN NUMBER DEFAULT 0,
147 C_TOT_IFEMALE IN NUMBER DEFAULT 0,
148 C_TOT_HLMALE IN NUMBER DEFAULT 0,
149 C_TOT_HLFEMALE IN NUMBER DEFAULT 0,
150 C_TOT_TMRACESMALE IN NUMBER DEFAULT 0,
151 C_TOT_TMRACESFEMALE IN NUMBER DEFAULT 0) RETURN NUMBER;
152
153 /*End of Changes for Bug#12604545*/
154
155 FUNCTION BEFOREREPORT RETURN BOOLEAN;
156
157 FUNCTION P_REPORT_YEARVALIDTRIGGER RETURN BOOLEAN;
158
159 FUNCTION AFTERREPORT RETURN BOOLEAN;
160
161 FUNCTION CF_SET_DEFAULTSFORMULA RETURN NUMBER;
162
163 FUNCTION C_TOT_EMPSFORMULA(EST_NODE_ID IN NUMBER
164 ,AFFILIATED IN VARCHAR2
165 ,EST_REP_NAME IN VARCHAR2
166 ,HEADQUARTERS IN VARCHAR2
167 ,EST_UNIT IN VARCHAR2) RETURN NUMBER;
168
169 FUNCTION CF_SET_EST_ACTIVFORMULA(EST_NODE_ID IN NUMBER) RETURN NUMBER;
170
171 FUNCTION C_TOT_CATFORMULA(C_TOT_HLMALE IN NUMBER
172 ,C_TOT_HLFEMALE IN NUMBER
173 ,C_TOT_TMRACESMALE IN NUMBER
174 ,C_TOT_TMRACESFEMALE IN NUMBER
175 ,C_TOT_WMALE IN NUMBER
176 ,C_TOT_BMALE IN NUMBER
177 ,C_TOT_HMALE IN NUMBER
178 ,C_TOT_AMALE IN NUMBER
179 ,C_TOT_IMALE IN NUMBER
180 ,C_TOT_WFEMALE IN NUMBER
181 ,C_TOT_BFEMALE IN NUMBER
182 ,C_TOT_HFEMALE IN NUMBER
183 ,C_TOT_AFEMALE IN NUMBER
184 ,C_TOT_IFEMALE IN NUMBER) RETURN NUMBER;
185
186 FUNCTION CF_SET_REPFORMULA(HEADQUARTERS IN VARCHAR2
187 ,EST_UNIT IN VARCHAR2) RETURN NUMBER;
188
189 FUNCTION CF_SET_CURR_HWFORMULA(ESTAB_STATE IN VARCHAR2
190 ,CONS_JOB_CATEGORY_CODE IN VARCHAR2
191 ,EST_NODE_ID IN NUMBER) RETURN NUMBER;
192
193 FUNCTION CF_SET_AUD_HWFORMULA(ESTAB_STATE IN VARCHAR2
194 ,EST_NODE_ID IN NUMBER) RETURN NUMBER;
195
196 FUNCTION CF_100_EMPSFORMULA RETURN CHAR;
197
198 FUNCTION CF_CREATEUPD_ARCHEITFORMULA(EST_NODE_ID IN NUMBER
199 ,C_TOT_CAT IN NUMBER
200 ,C_TOT_WMALE IN NUMBER
201 ,C_TOT_BMALE IN NUMBER
202 ,C_TOT_HMALE IN NUMBER
203 ,C_TOT_AMALE IN NUMBER
204 ,C_TOT_IMALE IN NUMBER
205 ,C_TOT_WFEMALE IN NUMBER
206 ,C_TOT_BFEMALE IN NUMBER
207 ,C_TOT_HFEMALE IN NUMBER
208 ,C_TOT_AFEMALE IN NUMBER
209 ,C_TOT_IFEMALE IN NUMBER
210 ,C_TOT_HLMALE IN NUMBER
211 ,C_TOT_HLFEMALE IN NUMBER
212 ,C_TOT_TMRACESMALE IN NUMBER
213 ,C_TOT_TMRACESFEMALE IN NUMBER) RETURN NUMBER;
214
215 FUNCTION CF_AUDIT_REPORT(CONS_JOB_CATEGORY_CODE IN VARCHAR2
216 ,EST_NODE_ID IN NUMBER) RETURN NUMBER;
217
218 FUNCTION C_DEF_SIC_P RETURN VARCHAR2;
219
220 FUNCTION C_DEF_NAICS_P RETURN VARCHAR2;
221
222 FUNCTION C_DEF_GRE_P RETURN VARCHAR2;
223
224 FUNCTION C_DEF_DUNS_P RETURN VARCHAR2;
225
226 FUNCTION C_DEF_GOV_CON_P RETURN VARCHAR2;
227
228 FUNCTION C_DEF_APPRENT_P RETURN VARCHAR2;
229
230 FUNCTION C_DEF_ACTIV_1_P RETURN VARCHAR2;
231
232 FUNCTION C_DEF_ACTIV_2_P RETURN VARCHAR2;
233
234 FUNCTION C_DEF_ACTIV_3_P RETURN VARCHAR2;
235
236 FUNCTION C_DEF_ACTIV_4_P RETURN VARCHAR2;
237
238 FUNCTION CP_REPORT_YEAR_P RETURN VARCHAR2;
239
240 FUNCTION CP_TOT_EMPS_P RETURN NUMBER;
241
242 FUNCTION C_100_EMPS_P RETURN VARCHAR2;
243
244 FUNCTION C_EST_ACTIV_1_P RETURN VARCHAR2;
245
246 FUNCTION C_EST_ACTIV_2_P RETURN VARCHAR2;
247
248 FUNCTION C_EST_ACTIV_3_P RETURN VARCHAR2;
249
250 FUNCTION C_EST_ACTIV_4_P RETURN VARCHAR2;
251
252 FUNCTION C_AFFILIATED_P RETURN VARCHAR2;
253
254 FUNCTION CP_PAYROLL_PERIOD_DATE_START_P RETURN DATE;
255
256 FUNCTION CP_PAYROLL_PERIOD_DATE_END_P RETURN DATE;
257
258 FUNCTION CP_REPORT_TYPE_P RETURN VARCHAR2;
259
260 FUNCTION CP_HW_AUD_MALE_P RETURN NUMBER;
261
262 FUNCTION CP_HW_AUD_FEMALE_P RETURN NUMBER;
263
264 FUNCTION CP_HW_AUD_TOT_P RETURN NUMBER;
265
266 FUNCTION CP_HW_CAT_P RETURN NUMBER;
267
268 FUNCTION CP_HW_FEMALE_P RETURN NUMBER;
269
270 FUNCTION CP_HW_MALE_P RETURN NUMBER;
271
272 FUNCTION C_BUSINESS_GROUP_NAME_P RETURN VARCHAR2;
273
274 FUNCTION C_HIERARCHY_NAME_P RETURN VARCHAR2;
275
276 FUNCTION C_HIERARCHY_VERSION_NUM_P RETURN NUMBER;
277
278 FUNCTION C_PARENT_ORG_ID_P RETURN NUMBER;
279
280 FUNCTION C_PARENT_NODE_ID_P RETURN NUMBER;
281
282 FUNCTION CP_PREV_YEAR_FILED_P RETURN VARCHAR2;
283
284 FUNCTION C_PRIOD_END_DATE_P RETURN DATE;
285
286 FUNCTION C_ALL_TOTAL_P RETURN NUMBER;
287
288 FUNCTION C_NO_OF_ESTABLISHMENTS_P RETURN NUMBER;
289
290 FUNCTION C_PAYROLL_PERIOD_DATE_START_P RETURN DATE;
291
292 FUNCTION C_PAYROLL_PERIOD_DATE_END_P RETURN DATE;
293
294 FUNCTION C_REPORT_MODE_P RETURN VARCHAR2;
295
296 FUNCTION C_REPORT_YEAR_P RETURN NUMBER;
297
298 FUNCTION GET_BUSINESS_GROUP(P_BUSINESS_GROUP_ID IN NUMBER) RETURN VARCHAR2;
299
300 --PROCEDURE OPEN;
301
302 --PROCEDURE PUT(P_CHAR IN VARCHAR2);
303
304 --PROCEDURE CLOSE;
305
306 END PER_PERUSEO1_XMLP_PKG;