1 PACKAGE BODY PER_PERUSEO1_XMLP_PKG AS
2 /* $Header: PERUSEO1B.pls 120.7.12020000.2 2012/07/04 21:51:18 amnaraya ship $ */
3 --G_FILE_TYPE TEXT_IO.FILE_TYPE;
4
5 G_IL_FEIN VARCHAR2(10);
6
7 G_FILE_NAME VARCHAR2(30);
8
9 /*Changes for Bug#12604545. Created the function MAINTAIN_EST_EMP_COUNTS
10 to resolve the issue of information not getting saved to "EEO1 Individual
11 Establishment Archive Data" Location Extra Information when "EEO Individual
12 Establishment Report (XML)" was run in Final Mode */
13
14 /* The Employee Counts for each of the Establishment will be accumulated in
15 the PL/SQL Table G_EST_COUNT_TABLE and in the After Report the counts
16 will be saved to Location Extra Information
17
18 The function can be called in three modes. BEFOREREPORT, UPDATE and AFTERREPORT
19
20 BEFOREREPORT - Takes care of Initialization part. Called from
21 PER_PERUSEO1_XMLP_PKG.BEFOREREPORT
22
23 UPDATE - Updates the data in PL/SQL table with the Establishment Employee Counts
24 Called from Q_1 Query of XML Data Template PERUSEO1 (Delivered in PERRPEO1_XML.xml)
25
26 AFTERREPORT - Saves the data in PL/SQL table to Location Extra Information*/
27
28 FUNCTION MAINTAIN_EST_EMP_COUNTS (C_MODE IN VARCHAR2,
29 C_EST_NODE_ID IN NUMBER,
30 C_TOT_CAT IN NUMBER,
31 C_TOT_WMALE IN NUMBER,
32 C_TOT_BMALE IN NUMBER,
33 C_TOT_HMALE IN NUMBER,
34 C_TOT_AMALE IN NUMBER,
35 C_TOT_IMALE IN NUMBER,
36 C_TOT_WFEMALE IN NUMBER,
37 C_TOT_BFEMALE IN NUMBER,
38 C_TOT_HFEMALE IN NUMBER,
39 C_TOT_AFEMALE IN NUMBER,
40 C_TOT_IFEMALE IN NUMBER,
41 C_TOT_HLMALE IN NUMBER,
42 C_TOT_HLFEMALE IN NUMBER,
43 C_TOT_TMRACESMALE IN NUMBER,
44 C_TOT_TMRACESFEMALE IN NUMBER)
45
46 RETURN NUMBER
47
48 IS
49
50 L_EST_NODE_ID NUMBER;
51 L_CF_CREATEUPD_ARCHEITFORMULA NUMBER;
52
53 BEGIN
54
55 IF C_MODE = 'BEFOREREPORT' THEN
56
57 G_EST_COUNT_TABLE.DELETE;
58 P_CONC_REQUEST_ID := fnd_profile.value ('CONC_REQUEST_ID');
59
60 END IF;
61
62 IF C_MODE = 'UPDATE' THEN
63
64 IF G_EST_COUNT_TABLE.EXISTS(C_EST_NODE_ID) THEN
65
66 /*Add the latest counts to existing counts in PL/SQL table for the Establishment*/
67
68 G_EST_COUNT_TABLE(C_EST_NODE_ID).TOT_CAT := G_EST_COUNT_TABLE(C_EST_NODE_ID).TOT_CAT + C_TOT_CAT;
69 G_EST_COUNT_TABLE(C_EST_NODE_ID).TOT_WMALE := G_EST_COUNT_TABLE(C_EST_NODE_ID).TOT_WMALE + C_TOT_WMALE;
70 G_EST_COUNT_TABLE(C_EST_NODE_ID).TOT_BMALE := G_EST_COUNT_TABLE(C_EST_NODE_ID).TOT_BMALE + C_TOT_BMALE;
71 G_EST_COUNT_TABLE(C_EST_NODE_ID).TOT_HMALE := G_EST_COUNT_TABLE(C_EST_NODE_ID).TOT_HMALE + C_TOT_HMALE;
72 G_EST_COUNT_TABLE(C_EST_NODE_ID).TOT_AMALE := G_EST_COUNT_TABLE(C_EST_NODE_ID).TOT_AMALE + C_TOT_AMALE;
73 G_EST_COUNT_TABLE(C_EST_NODE_ID).TOT_IMALE := G_EST_COUNT_TABLE(C_EST_NODE_ID).TOT_IMALE + C_TOT_IMALE;
74 G_EST_COUNT_TABLE(C_EST_NODE_ID).TOT_WFEMALE := G_EST_COUNT_TABLE(C_EST_NODE_ID).TOT_WFEMALE + C_TOT_WFEMALE;
75 G_EST_COUNT_TABLE(C_EST_NODE_ID).TOT_BFEMALE := G_EST_COUNT_TABLE(C_EST_NODE_ID).TOT_BFEMALE + C_TOT_BFEMALE;
76 G_EST_COUNT_TABLE(C_EST_NODE_ID).TOT_HFEMALE := G_EST_COUNT_TABLE(C_EST_NODE_ID).TOT_HFEMALE + C_TOT_HFEMALE;
77 G_EST_COUNT_TABLE(C_EST_NODE_ID).TOT_AFEMALE := G_EST_COUNT_TABLE(C_EST_NODE_ID).TOT_AFEMALE + C_TOT_AFEMALE;
78 G_EST_COUNT_TABLE(C_EST_NODE_ID).TOT_IFEMALE := G_EST_COUNT_TABLE(C_EST_NODE_ID).TOT_IFEMALE + C_TOT_IFEMALE;
79 G_EST_COUNT_TABLE(C_EST_NODE_ID).TOT_HLMALE := G_EST_COUNT_TABLE(C_EST_NODE_ID).TOT_HLMALE + C_TOT_HLMALE;
80 G_EST_COUNT_TABLE(C_EST_NODE_ID).TOT_HLFEMALE := G_EST_COUNT_TABLE(C_EST_NODE_ID).TOT_HLFEMALE + C_TOT_HLFEMALE;
81 G_EST_COUNT_TABLE(C_EST_NODE_ID).TOT_TMRACESMALE := G_EST_COUNT_TABLE(C_EST_NODE_ID).TOT_TMRACESMALE + C_TOT_TMRACESMALE;
82 G_EST_COUNT_TABLE(C_EST_NODE_ID).TOT_TMRACESFEMALE := G_EST_COUNT_TABLE(C_EST_NODE_ID).TOT_TMRACESFEMALE+ C_TOT_TMRACESFEMALE;
83
84 ELSE
85
86 /*Counts not present for this Establishment in PL/SQL table. Create them*/
87
88 G_EST_COUNT_TABLE(C_EST_NODE_ID).TOT_CAT := C_TOT_CAT;
89 G_EST_COUNT_TABLE(C_EST_NODE_ID).TOT_WMALE := C_TOT_WMALE;
90 G_EST_COUNT_TABLE(C_EST_NODE_ID).TOT_BMALE := C_TOT_BMALE;
91 G_EST_COUNT_TABLE(C_EST_NODE_ID).TOT_HMALE := C_TOT_HMALE;
92 G_EST_COUNT_TABLE(C_EST_NODE_ID).TOT_AMALE := C_TOT_AMALE;
93 G_EST_COUNT_TABLE(C_EST_NODE_ID).TOT_IMALE := C_TOT_IMALE;
94 G_EST_COUNT_TABLE(C_EST_NODE_ID).TOT_WFEMALE := C_TOT_WFEMALE;
95 G_EST_COUNT_TABLE(C_EST_NODE_ID).TOT_BFEMALE := C_TOT_BFEMALE;
96 G_EST_COUNT_TABLE(C_EST_NODE_ID).TOT_HFEMALE := C_TOT_HFEMALE;
97 G_EST_COUNT_TABLE(C_EST_NODE_ID).TOT_AFEMALE := C_TOT_AFEMALE;
98 G_EST_COUNT_TABLE(C_EST_NODE_ID).TOT_IFEMALE := C_TOT_IFEMALE;
99 G_EST_COUNT_TABLE(C_EST_NODE_ID).TOT_HLMALE := C_TOT_HLMALE;
100 G_EST_COUNT_TABLE(C_EST_NODE_ID).TOT_HLFEMALE := C_TOT_HLFEMALE;
101 G_EST_COUNT_TABLE(C_EST_NODE_ID).TOT_TMRACESMALE := C_TOT_TMRACESMALE;
102 G_EST_COUNT_TABLE(C_EST_NODE_ID).TOT_TMRACESFEMALE := C_TOT_TMRACESFEMALE;
103
104 END IF;
105
106 END IF;
107
108 IF C_MODE = 'AFTERREPORT' THEN
109
110 IF G_EST_COUNT_TABLE.COUNT > 0 THEN
111
112 L_EST_NODE_ID := G_EST_COUNT_TABLE.FIRST;
113
114 WHILE L_EST_NODE_ID IS NOT NULL
115 LOOP
116 L_CF_CREATEUPD_ARCHEITFORMULA :=
117 CF_CREATEUPD_ARCHEITFORMULA( L_EST_NODE_ID,
118 G_EST_COUNT_TABLE(L_EST_NODE_ID).TOT_CAT,
119 G_EST_COUNT_TABLE(L_EST_NODE_ID).TOT_WMALE,
120 G_EST_COUNT_TABLE(L_EST_NODE_ID).TOT_BMALE,
121 G_EST_COUNT_TABLE(L_EST_NODE_ID).TOT_HMALE,
122 G_EST_COUNT_TABLE(L_EST_NODE_ID).TOT_AMALE,
123 G_EST_COUNT_TABLE(L_EST_NODE_ID).TOT_IMALE,
124 G_EST_COUNT_TABLE(L_EST_NODE_ID).TOT_WFEMALE,
125 G_EST_COUNT_TABLE(L_EST_NODE_ID).TOT_BFEMALE,
126 G_EST_COUNT_TABLE(L_EST_NODE_ID).TOT_HFEMALE,
127 G_EST_COUNT_TABLE(L_EST_NODE_ID).TOT_AFEMALE,
128 G_EST_COUNT_TABLE(L_EST_NODE_ID).TOT_IFEMALE,
129 G_EST_COUNT_TABLE(L_EST_NODE_ID).TOT_HLMALE,
130 G_EST_COUNT_TABLE(L_EST_NODE_ID).TOT_HLFEMALE,
131 G_EST_COUNT_TABLE(L_EST_NODE_ID).TOT_TMRACESMALE,
132 G_EST_COUNT_TABLE(L_EST_NODE_ID).TOT_TMRACESFEMALE);
133 L_EST_NODE_ID := G_EST_COUNT_TABLE.NEXT(L_EST_NODE_ID);
134 END LOOP;
135
136 G_EST_COUNT_TABLE.DELETE;
137
138 END IF;
139
140 END IF;
141
142 RETURN (1);
143
144 END;
145
146 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
147 L_DUMMY VARCHAR2(1);
148 TEST_YEAR DATE;
149 TEST_YEAR1 DATE;
150 L_LOCATION_CODE VARCHAR2(60);
151 L_LOCATION_ID NUMBER(15);
152 L_BUFFER VARCHAR2(1000);
153 G_DELIMITER VARCHAR2(1) := ',';
154 G_EOL VARCHAR2(1) := FND_GLOBAL.LOCAL_CHR(10);
155 L_MAINTAIN_EST_EMP_COUNTS NUMBER(2);
156 temp boolean;
157 BEGIN
158 --HR_STANDARD.EVENT('BEFORE REPORT');
159 L_MAINTAIN_EST_EMP_COUNTS := MAINTAIN_EST_EMP_COUNTS('BEFOREREPORT');
160 C_ALL_TOTAL := 0;
161 P_PAYROLL_PERIOD_DATE_START_T := FND_DATE.CANONICAL_TO_DATE(P_PAYROLL_PERIOD_DATE_START);
162 P_PAYROLL_PERIOD_DATE_END_T := FND_DATE.CANONICAL_TO_DATE(P_PAYROLL_PERIOD_DATE_END);
163
164 -- Moved these 2 lines from top to here - bug#12534614
165 temp := P_REPORT_YEARVALIDTRIGGER;
166 CP_PREV_YEAR_FILED := TO_CHAR(TO_NUMBER(P_REPORT_YEAR - 1));
167
168 C_PAYROLL_PERIOD_DATE_START := P_PAYROLL_PERIOD_DATE_START_T;
169 C_PAYROLL_PERIOD_DATE_END := P_PAYROLL_PERIOD_DATE_END_T;
170 C_REPORT_MODE := P_REPORT_MODE;
171 C_REPORT_YEAR := P_REPORT_YEAR;
172 C_BUSINESS_GROUP_NAME := GET_BUSINESS_GROUP(P_BUSINESS_GROUP_ID);
173 /*SRW.MESSAGE('35'
174 ,'previous year ' || CP_PREV_YEAR_FILED)*/NULL;
175 SELECT
176 PGH.NAME,
177 PGV.VERSION_NUMBER,
178 PGN.ENTITY_ID,
179 PGN.HIERARCHY_NODE_ID
180 INTO C_HIERARCHY_NAME,C_HIERARCHY_VERSION_NUM,C_PARENT_ORG_ID,C_PARENT_NODE_ID
181 FROM
182 PER_GEN_HIERARCHY PGH,
183 PER_GEN_HIERARCHY_VERSIONS PGV,
184 PER_GEN_HIERARCHY_NODES PGN
185 WHERE PGH.HIERARCHY_ID = P_HIERARCHY_ID
186 AND PGH.HIERARCHY_ID = PGV.HIERARCHY_ID
187 AND PGV.HIERARCHY_VERSION_ID = P_HIERARCHY_VERSION_ID
188 AND PGN.HIERARCHY_VERSION_ID = PGV.HIERARCHY_VERSION_ID
189 AND PGN.NODE_TYPE = 'PAR';
190 SELECT
191 COUNT('h_node')
192 INTO C_NO_OF_ESTABLISHMENTS
193 FROM
194 PER_GEN_HIERARCHY_NODES PGHN
195 WHERE PGHN.HIERARCHY_VERSION_ID = P_HIERARCHY_VERSION_ID
196 AND PGHN.NODE_TYPE = 'EST';
197 /*SRW.MESSAGE('10'
198 ,'number of establishments: ' || C_NO_OF_ESTABLISHMENTS)*/NULL;
199 BEGIN
200 SELECT
201 null
202 INTO L_DUMMY
203 FROM
204 HR_ALL_ORGANIZATION_UNITS
205 WHERE ORGANIZATION_ID = C_PARENT_ORG_ID
206 AND LOCATION_ID is not null;
207 EXCEPTION
208 WHEN NO_DATA_FOUND THEN
209 FND_MESSAGE.SET_NAME('PER'
210 ,'PER_75228_ORG_LOC_MISSING');
211 /*SRW.MESSAGE('10'
212 ,FND_MESSAGE.GET)*/NULL;
213 RAISE;
214 END;
215 BEGIN
216 SELECT
217 null
218 INTO L_DUMMY
219 FROM
220 HR_ORGANIZATION_INFORMATION
221 WHERE ORGANIZATION_ID = C_PARENT_ORG_ID
222 AND ORG_INFORMATION_CONTEXT = 'EEO_Spec';
223 EXCEPTION
224 WHEN NO_DATA_FOUND THEN
225 FND_MESSAGE.SET_NAME('PER'
226 ,'PER_75229_EEO_CLASS_MISSING');
227 /*SRW.MESSAGE('10'
228 ,FND_MESSAGE.GET)*/NULL;
229 RAISE;
230 END;
231 BEGIN
232 SELECT
233 null
234 INTO L_DUMMY
235 FROM
236 HR_LOCATION_EXTRA_INFO HLEI1,
237 HR_LOCATION_EXTRA_INFO HLEI2,
238 PER_GEN_HIERARCHY_NODES PGN,
239 HR_LOCATIONS_ALL ELOC
240 WHERE PGN.HIERARCHY_VERSION_ID = P_HIERARCHY_VERSION_ID
241 AND PGN.NODE_TYPE = 'EST'
242 AND ELOC.LOCATION_ID = PGN.ENTITY_ID
243 AND HLEI1.LOCATION_ID = ELOC.LOCATION_ID
244 AND HLEI1.INFORMATION_TYPE = 'EEO-1 Specific Information'
245 AND HLEI1.LEI_INFORMATION_CATEGORY = 'EEO-1 Specific Information'
246 AND HLEI2.LOCATION_ID = ELOC.LOCATION_ID
247 AND HLEI2.INFORMATION_TYPE = 'Establishment Information'
248 AND HLEI2.LEI_INFORMATION_CATEGORY = 'Establishment Information';
249 EXCEPTION
250 WHEN NO_DATA_FOUND THEN
251 FND_MESSAGE.SET_NAME('PER'
252 ,'PER_75230_EST_CLASS_MISSING');
253 /*SRW.MESSAGE('10'
254 ,FND_MESSAGE.GET)*/NULL;
255 RAISE;
256 WHEN OTHERS THEN
257 NULL;
258 END;
259 BEGIN
260 SELECT
261 ELOC.LOCATION_ID,
262 ELOC.LOCATION_CODE
263 INTO L_LOCATION_ID,L_LOCATION_CODE
264 FROM
265 HR_LOCATION_EXTRA_INFO HLEI1,
266 PER_GEN_HIERARCHY_NODES PGN,
267 HR_LOCATIONS_ALL ELOC
268 WHERE PGN.HIERARCHY_VERSION_ID = P_HIERARCHY_VERSION_ID
269 AND PGN.NODE_TYPE = 'EST'
270 AND ELOC.LOCATION_ID = PGN.ENTITY_ID
271 AND HLEI1.LOCATION_ID = ELOC.LOCATION_ID
272 AND HLEI1.INFORMATION_TYPE = 'EEO-1 Archive Information'
273 AND HLEI1.LEI_INFORMATION_CATEGORY = 'EEO-1 Archive Information';
274 EXCEPTION
275 WHEN NO_DATA_FOUND THEN
276 /*SRW.MESSAGE('20'
277 ,'Either (1) there are no establishments within your hierarchy with the extra')*/NULL;
278 /*SRW.MESSAGE('20'
279 ,'information type EEO-1 Archive Information attatched - in which case ')*/NULL;
280 /*SRW.MESSAGE('20'
281 ,'please attatch to appropriate responsibility in the information security screen.')*/NULL;
282 /*SRW.MESSAGE('20'
283 ,' ')*/NULL;
284 /*SRW.MESSAGE('20'
285 ,'or (2) the extra information type EEO-1 Archive Information exists for location ')*/NULL;
286 /*SRW.MESSAGE('20'
287 ,L_LOCATION_CODE || 'but does not yet contain data. If this is your first time to file ')*/NULL;
288 /*SRW.MESSAGE('20'
289 ,'(ie if this is a report type 9) then do not worry about this. ')*/NULL;
290 /*SRW.MESSAGE('20'
291 ,'However if you filed last year then the EIT will have to be filled with last years')*/NULL;
292 /*SRW.MESSAGE('20'
293 ,'totals, either manually or by running the report for last year in final mode.')*/NULL;
294 WHEN OTHERS THEN
295 NULL;
296 END;
297 BEGIN
298 BEGIN
299 SELECT
300 '1',
301 ELOC.LOCATION_ID,
302 ELOC.LOCATION_CODE
303 INTO L_DUMMY,L_LOCATION_ID,L_LOCATION_CODE
304 FROM
305 HR_LOCATION_EXTRA_INFO HLEI1,
306 PER_GEN_HIERARCHY_NODES PGN,
307 HR_LOCATIONS_ALL ELOC
308 WHERE PGN.HIERARCHY_VERSION_ID = P_HIERARCHY_VERSION_ID
309 AND PGN.NODE_TYPE = 'EST'
310 AND ELOC.LOCATION_ID = PGN.ENTITY_ID
311 AND HLEI1.LOCATION_ID = ELOC.LOCATION_ID
312 AND HLEI1.INFORMATION_TYPE = 'EEO-1 Specific Information'
313 AND HLEI1.LEI_INFORMATION_CATEGORY = 'EEO-1 Specific Information'
314 AND HLEI1.LEI_INFORMATION9 = 'Y';
315 EXCEPTION
316 WHEN NO_DATA_FOUND THEN
317 NULL;
318 WHEN OTHERS THEN
319 NULL;
320 END;
321 /*SRW.MESSAGE('444'
322 ,'l_dummy, l_location_id, l_location_code ' || L_DUMMY || ' ' || L_LOCATION_ID || ' ' || L_LOCATION_CODE)*/NULL;
323 IF L_DUMMY = '1' THEN
324 BEGIN
325 SELECT
326 2
327 INTO L_DUMMY
328 FROM
329 HR_LOCATION_EXTRA_INFO
330 WHERE LEI_INFORMATION1 = CP_PREV_YEAR_FILED
331 AND LEI_INFORMATION_CATEGORY = 'EEO-1 Archive Information';
332 EXCEPTION
333 WHEN NO_DATA_FOUND THEN
334 /*SRW.MESSAGE('555'
335 ,'l_dummy, l_location_id, l_location_code ' || L_DUMMY || ' ' || L_LOCATION_ID || ' ' || L_LOCATION_CODE)*/NULL;
336 /*SRW.MESSAGE('30'
337 ,' ')*/NULL;
338 /*SRW.MESSAGE('30'
339 ,'The extra information type EEO-1 Archive Information exists for ' || L_LOCATION_CODE)*/NULL;
340 /*SRW.MESSAGE('30'
341 ,'but does not contain data for last years totals. ')*/NULL;
342 /*SRW.MESSAGE('30'
343 ,' ')*/NULL;
344 /*SRW.MESSAGE('30'
345 ,'As you filed last year then this EIT will have to be filled with last years')*/NULL;
346 /*SRW.MESSAGE('30'
347 ,'totals, either manually or by running the report for last year in final mode.')*/NULL;
348 /*SRW.MESSAGE('30'
349 ,' ')*/NULL;
350 /*SRW.MESSAGE('30'
351 ,'To do this without encountering this error again, set the reported previously field')*/NULL;
352 /*SRW.MESSAGE('30'
353 ,'under location/extra info/EEO-1 Specific Data to No and then run the report in ')*/NULL;
354 /*SRW.MESSAGE('30'
355 ,'Final mode for last years dates.')*/NULL;
356 /*SRW.MESSAGE('30'
357 ,' ')*/NULL;
358 /*SRW.MESSAGE('30'
359 ,'Then set the Reported Previously field back to Yes and run the report for ')*/NULL;
360 /*SRW.MESSAGE('30'
361 ,'this year as normal')*/NULL;
362 /*SRW.MESSAGE('30'
363 ,' ')*/NULL;
364 WHEN OTHERS THEN
365 NULL;
366 END;
367 END IF;
368 END;
369 IF P_AUDIT_REPORT = 'Y' THEN
370 --OPEN;
371 L_BUFFER := 'Person Id' || G_DELIMITER || 'Last Name' ||
372 G_DELIMITER || 'First Name' || G_DELIMITER || 'Employee Number' ||
373 G_DELIMITER || 'Gender' || G_DELIMITER || 'Ethnic Origin' || G_DELIMITER ||
374 'Assignment Id' || G_DELIMITER || 'Job Id' || G_DELIMITER || 'Job Name' || G_DELIMITER ||
375 'Location Id' || G_DELIMITER || 'Location Code' || G_DELIMITER || G_EOL;
376 --PUT(L_BUFFER);
377 FND_FILE.PUT_LINE(FND_FILE.LOG,L_BUFFER);
378 END IF;
379 RETURN TRUE;
380 END BEFOREREPORT;
381
382 /* Modified the P_REPORT_YEARVALIDTRIGGER for bug#12534614. Removed the usage
383 of FND_DATE.CANONICAL_TO_DATE with P_PAYROLL_PERIOD_DATE_END_T, since it is
384 already converted to DATE.
385 */
386 FUNCTION P_REPORT_YEARVALIDTRIGGER RETURN BOOLEAN IS
387 C_DATE_FORMAT varchar2(25):='DD-MON-YY';
388 BEGIN
389 IF P_PAYROLL_PERIOD_DATE_END_T IS NOT NULL THEN
390 P_REPORT_YEAR := TO_CHAR(to_date(P_PAYROLL_PERIOD_DATE_END_T,C_DATE_FORMAT),'RRRR');
391 ELSE
392 P_REPORT_YEAR := TO_CHAR(SYSDATE
393 ,'RRRR');
394 END IF;
395 RETURN (TRUE);
396 END P_REPORT_YEARVALIDTRIGGER;
397
398 /*
399 FUNCTION P_REPORT_YEARVALIDTRIGGER RETURN BOOLEAN IS
400 BEGIN
401 IF P_PAYROLL_PERIOD_DATE_END_T IS NOT NULL THEN
402 P_REPORT_YEAR := TO_CHAR(FND_DATE.CANONICAL_TO_DATE(P_PAYROLL_PERIOD_DATE_END_T)
403 ,'YYYY');
404 ELSE
405 P_REPORT_YEAR := TO_CHAR(SYSDATE
406 ,'YYYY');
407 END IF;
408 RETURN (TRUE);
409 END P_REPORT_YEARVALIDTRIGGER;
410 */
411
412 FUNCTION AFTERREPORT RETURN BOOLEAN IS
413
414 L_MAINTAIN_EST_EMP_COUNTS NUMBER(2);
415
416 BEGIN
417 --HR_STANDARD.EVENT('AFTER REPORT');
418 L_MAINTAIN_EST_EMP_COUNTS := MAINTAIN_EST_EMP_COUNTS('AFTERREPORT');
419 FND_FILE.PUT_LINE(1
420 ,'Total employees of the establishments: ' || C_ALL_TOTAL);
421 FND_FILE.PUT_LINE(1
422 ,' ');
423 IF P_AUDIT_REPORT = 'Y' THEN
424 --CLOSE;
425 null;
426 END IF;
427 RETURN (TRUE);
428 END AFTERREPORT;
429
430 FUNCTION CF_SET_DEFAULTSFORMULA RETURN NUMBER IS
431 CURSOR C_DEFAULTS IS
432 SELECT
433 ORG_INFORMATION1,
434 ORG_INFORMATION2,
435 ORG_INFORMATION3,
436 ORG_INFORMATION4,
437 ORG_INFORMATION5,
438 ORG_INFORMATION6,
439 ORG_INFORMATION8,
440 ORG_INFORMATION9,
441 ORG_INFORMATION11,
442 ORG_INFORMATION12
443 FROM
444 HR_ORGANIZATION_INFORMATION
445 WHERE ORGANIZATION_ID = C_PARENT_ORG_ID
446 AND ORG_INFORMATION_CONTEXT = 'VETS_EEO_Dup';
447 L_DEFAULT C_DEFAULTS%ROWTYPE;
448 BEGIN
449 OPEN C_DEFAULTS;
450 FETCH C_DEFAULTS
451 INTO L_DEFAULT;
452 IF C_DEFAULTS%NOTFOUND THEN
453 NULL;
454 ELSE
455 C_DEF_SIC := L_DEFAULT.ORG_INFORMATION1;
456 C_DEF_NAICS := NVL(L_DEFAULT.ORG_INFORMATION2
457 ,L_DEFAULT.ORG_INFORMATION1);
458 C_DEF_GRE := L_DEFAULT.ORG_INFORMATION3;
459 C_DEF_DUNS := L_DEFAULT.ORG_INFORMATION4;
460 C_DEF_GOV_CON := L_DEFAULT.ORG_INFORMATION5;
461 C_DEF_APPRENT := L_DEFAULT.ORG_INFORMATION6;
462 C_DEF_ACTIV_1 := UPPER(LTRIM(RTRIM(L_DEFAULT.ORG_INFORMATION8)));
463 C_DEF_ACTIV_2 := UPPER(LTRIM(RTRIM(L_DEFAULT.ORG_INFORMATION9)));
464 C_DEF_ACTIV_3 := UPPER(LTRIM(RTRIM(L_DEFAULT.ORG_INFORMATION11)));
465 C_DEF_ACTIV_4 := UPPER(LTRIM(RTRIM(L_DEFAULT.ORG_INFORMATION12)));
466 END IF;
467 CLOSE C_DEFAULTS;
468 RETURN NULL;
469 END CF_SET_DEFAULTSFORMULA;
470
471 FUNCTION C_TOT_EMPSFORMULA(EST_NODE_ID IN NUMBER
472 ,AFFILIATED IN VARCHAR2
473 ,EST_REP_NAME IN VARCHAR2
474 ,HEADQUARTERS IN VARCHAR2
475 ,EST_UNIT IN VARCHAR2) RETURN NUMBER IS
476 L_COUNT_EMPS NUMBER := 0;
477 BEGIN
478 SELECT COUNT(PEO.PERSON_ID)
479 INTO L_COUNT_EMPS
480 FROM PER_ALL_PEOPLE_F PEO,
481 PER_ALL_ASSIGNMENTS_F ASS,
482 --PER_JOBS_VL JOB - Replaced PER_JOBS_VL with PER_JOBS - bug#11736960
483 PER_JOBS JOB
484 WHERE PEO.PERSON_ID = ASS.PERSON_ID
485 --
486 /* Commented for bug#11736960 starts
487 AND PEO.PER_INFORMATION1 is not NULL
488 Commented for bug#11736960 ends */
489 --
490 AND JOB.JOB_INFORMATION_CATEGORY = 'US'
491 AND P_PAYROLL_PERIOD_DATE_START_T <= NVL(JOB.DATE_TO, P_PAYROLL_PERIOD_DATE_END_T)
492 AND P_PAYROLL_PERIOD_DATE_END_T >= JOB.DATE_FROM
493 AND JOB.JOB_INFORMATION1 is not NULL
494 AND ASS.JOB_ID = JOB.JOB_ID
495 AND PEO.EFFECTIVE_START_DATE = (
496 SELECT
497 MAX(PEO1.EFFECTIVE_START_DATE)
498 FROM
499 --PER_PEOPLE_F PEO1 - Replaced PER_PEOPLE_F with PER_ALL_PEOPLE_F -bug#11736960
500 PER_ALL_PEOPLE_F PEO1
501 WHERE P_PAYROLL_PERIOD_DATE_START_T <= PEO1.EFFECTIVE_END_DATE
502 AND P_PAYROLL_PERIOD_DATE_END_T >= PEO1.EFFECTIVE_START_DATE
503 AND PEO.PERSON_ID = PEO1.PERSON_ID
504 AND PEO1.CURRENT_EMPLOYEE_FLAG = 'Y' )
505 AND ASS.EFFECTIVE_START_DATE = (
506 SELECT
507 MAX(ASS1.EFFECTIVE_START_DATE)
508 FROM
509 PER_ALL_ASSIGNMENTS_F ASS1,
510 PER_ASSIGNMENT_STATUS_TYPES AST --Added for bug#12594720
511 WHERE P_PAYROLL_PERIOD_DATE_START_T <= ASS1.EFFECTIVE_END_DATE
512 AND P_PAYROLL_PERIOD_DATE_END_T >= ASS1.EFFECTIVE_START_DATE
513 AND ASS.PERSON_ID = ASS1.PERSON_ID
514 AND ASS1.ASSIGNMENT_TYPE = 'E'
515 AND ASS1.PRIMARY_FLAG = 'Y'
516 AND ASS1.ASSIGNMENT_STATUS_TYPE_ID = AST.ASSIGNMENT_STATUS_TYPE_ID
517 AND AST.PER_SYSTEM_STATUS <> 'TERM_ASSIGN')
518 AND ASS.ASSIGNMENT_TYPE = 'E'
519 AND ASS.PRIMARY_FLAG = 'Y'
520 AND ASS.BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID
521 AND PEO.BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID
522 AND JOB.BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID
523 AND EXISTS (
524 SELECT
525 'X'
526 FROM
527 HR_ORGANIZATION_INFORMATION HOI1
528 WHERE TO_CHAR(ASS.ASSIGNMENT_STATUS_TYPE_ID) = HOI1.ORG_INFORMATION1
529 AND HOI1.ORG_INFORMATION_CONTEXT = 'Reporting Statuses'
530 AND HOI1.ORGANIZATION_ID = P_BUSINESS_GROUP_ID
531 )
532 AND EXISTS (
533 SELECT
534 'X'
535 FROM HR_ORGANIZATION_INFORMATION HOI2
536 where ASS.EMPLOYMENT_CATEGORY = HOI2.ORG_INFORMATION1
537 AND HOI2.ORGANIZATION_ID = P_BUSINESS_GROUP_ID
538 AND HOI2.ORG_INFORMATION_CONTEXT = 'Reporting Categories'
539 )
540 /* Replaced this exists clasue with two seperate exists condition inorder to
541 avoid Merge Join Cartesian.
542 AND EXISTS (
543 SELECT
544 'X'
545 FROM
546 HR_ORGANIZATION_INFORMATION HOI1,
547 HR_ORGANIZATION_INFORMATION HOI2
548 WHERE TO_CHAR(ASS.ASSIGNMENT_STATUS_TYPE_ID) = HOI1.ORG_INFORMATION1
549 AND HOI1.ORG_INFORMATION_CONTEXT = 'Reporting Statuses'
550 AND HOI1.ORGANIZATION_ID = P_BUSINESS_GROUP_ID
551 AND ASS.EMPLOYMENT_CATEGORY = HOI2.ORG_INFORMATION1
552 AND HOI2.ORGANIZATION_ID = P_BUSINESS_GROUP_ID
553 AND HOI2.ORG_INFORMATION_CONTEXT = 'Reporting Categories' )
554 */
555 AND P_PAYROLL_PERIOD_DATE_START_T <= ASS.EFFECTIVE_END_DATE
556 AND P_PAYROLL_PERIOD_DATE_END_T >= ASS.EFFECTIVE_START_DATE
557 AND EXISTS (
558 SELECT 1
559 FROM
560 PER_GEN_HIERARCHY_NODES PGN
561 WHERE PGN.HIERARCHY_VERSION_ID = P_HIERARCHY_VERSION_ID
562 AND ( PGN.HIERARCHY_NODE_ID = C_TOT_EMPSFORMULA.EST_NODE_ID
563 OR PGN.PARENT_HIERARCHY_NODE_ID = C_TOT_EMPSFORMULA.EST_NODE_ID )
564 AND PGN.NODE_TYPE in ( 'EST' , 'LOC' )
565 AND ASS.LOCATION_ID=PGN.ENTITY_ID
566 )
567 /* Replaced this in clause with the above exists clause.
568 AND ASS.LOCATION_ID in (
569 SELECT
570 DISTINCT
571 PGN.ENTITY_ID
572 FROM
573 PER_GEN_HIERARCHY_NODES PGN
574 WHERE PGN.HIERARCHY_VERSION_ID = P_HIERARCHY_VERSION_ID
575 AND ( PGN.HIERARCHY_NODE_ID = C_TOT_EMPSFORMULA.EST_NODE_ID
576 OR PGN.PARENT_HIERARCHY_NODE_ID = C_TOT_EMPSFORMULA.EST_NODE_ID )
577 AND PGN.NODE_TYPE in ( 'EST' , 'LOC' ) )
578 */
579 -- Added for bug#11736960
580 AND EXISTS (SELECT 'X'
581 FROM PER_PEOPLE_EXTRA_INFO PEI
582 WHERE PEI.PERSON_ID = PEO.PERSON_ID
583 AND PEI.INFORMATION_TYPE = 'US_ETHNIC_ORIGIN'
584 AND (PEI.PEI_INFORMATION1 = 'Y'
585 OR PEI.PEI_INFORMATION2 = 'Y'
586 OR PEI.PEI_INFORMATION3 = 'Y'
587 OR PEI.PEI_INFORMATION4 = 'Y'
588 OR PEI.PEI_INFORMATION5 = 'Y'
589 OR PEI.PEI_INFORMATION6 = 'Y'
590 OR PEI.PEI_INFORMATION7 = 'Y'
591 )
592 );
593
594 /*SRW.MESSAGE(99
595 ,'est_node_id : ' || EST_NODE_ID)*/NULL;
596 /*SRW.MESSAGE(99
597 ,'l_couunt_emps : ' || L_COUNT_EMPS)*/NULL;
598 IF L_COUNT_EMPS > 99 THEN
599 C_100_EMPS := 'Y';
600 ELSE
601 C_100_EMPS := 'N';
602 END IF;
603 CP_TOT_EMPS := L_COUNT_EMPS;
604 CP_REPORT_YEAR := P_REPORT_YEAR;
605 C_AFFILIATED := AFFILIATED;
606
607 -- Added for bug#12575094
608 IF C_NO_OF_ESTABLISHMENTS = 1 THEN
609 CP_REPORT_TYPE := 'Single Establishment Employer Report - Type 1';
610 ELSIF C_NO_OF_ESTABLISHMENTS > 1
611 AND HEADQUARTERS = 'Y' THEN
612 CP_REPORT_TYPE := 'Headquarters Report - Type 3';
613 ELSIF CP_TOT_EMPS < 50 THEN
614 CP_REPORT_TYPE := 'Establishment Report - Type 8';
615 ELSIF EST_UNIT IS NULL AND CP_TOT_EMPS >= 50 THEN
616 CP_REPORT_TYPE := 'Establishment Report - Type 9';
617 ELSE
618 CP_REPORT_TYPE := 'Establishment Report - Type 4';
619 END IF;
620
621 -- IF CP_REPORT_TYPE = 'Headquarters Report - Type 3' OR CP_TOT_EMPS > P_MINIMUM_NO_OF_EMPLOYEES OR CP_REPORT_TYPE = 'Single Establishment Employer Report - Type 1' THEN
622 C_ALL_TOTAL := C_ALL_TOTAL + L_COUNT_EMPS;
623 FND_FILE.PUT_LINE(1
624 ,CP_REPORT_TYPE);
625 FND_FILE.PUT_LINE(1
626 ,'Establishment reporting name: ' || EST_REP_NAME);
627 FND_FILE.PUT_LINE(1
628 ,'Total employees : ' || L_COUNT_EMPS);
629 FND_FILE.PUT_LINE(1
630 ,' ');
631 -- END IF;
632 RETURN (L_COUNT_EMPS);
633 END C_TOT_EMPSFORMULA;
634
635 FUNCTION CF_SET_EST_ACTIVFORMULA(EST_NODE_ID IN NUMBER) RETURN NUMBER IS
636 CURSOR C_EST_ACT IS
637 SELECT
638 NVL(HLEI.LEI_INFORMATION5
639 ,C_DEF_ACTIV_1),
640 NVL(HLEI.LEI_INFORMATION6
641 ,C_DEF_ACTIV_2),
642 NVL(HLEI.LEI_INFORMATION7
643 ,C_DEF_ACTIV_3),
644 NVL(HLEI.LEI_INFORMATION8
645 ,C_DEF_ACTIV_4)
646 FROM
647 HR_LOCATION_EXTRA_INFO HLEI,
648 PER_GEN_HIERARCHY_NODES PGHN
649 WHERE HLEI.INFORMATION_TYPE = 'EEO-1 Specific Information'
650 AND HLEI.LEI_INFORMATION_CATEGORY = 'EEO-1 Specific Information'
651 AND HLEI.LOCATION_ID = PGHN.ENTITY_ID
652 AND PGHN.PARENT_HIERARCHY_NODE_ID = C_PARENT_NODE_ID
653 AND PGHN.HIERARCHY_NODE_ID = EST_NODE_ID
654 AND PGHN.NODE_TYPE = 'EST';
655 L_EST_ACT_1 VARCHAR2(60) := NULL;
656 L_EST_ACT_2 VARCHAR2(60) := NULL;
657 L_EST_ACT_3 VARCHAR2(60) := NULL;
658 L_EST_ACT_4 VARCHAR2(60) := NULL;
659 BEGIN
660 OPEN C_EST_ACT;
661 FETCH C_EST_ACT
662 INTO L_EST_ACT_1,L_EST_ACT_2,L_EST_ACT_3,L_EST_ACT_4;
663 C_EST_ACTIV_1 := UPPER(LTRIM(RTRIM(L_EST_ACT_1)));
664 C_EST_ACTIV_2 := UPPER(LTRIM(RTRIM(L_EST_ACT_2)));
665 C_EST_ACTIV_3 := UPPER(LTRIM(RTRIM(L_EST_ACT_3)));
666 C_EST_ACTIV_4 := UPPER(LTRIM(RTRIM(L_EST_ACT_4)));
667 RETURN NULL;
668 END CF_SET_EST_ACTIVFORMULA;
669
670 FUNCTION C_TOT_CATFORMULA(C_TOT_HLMALE IN NUMBER
671 ,C_TOT_HLFEMALE IN NUMBER
672 ,C_TOT_TMRACESMALE IN NUMBER
673 ,C_TOT_TMRACESFEMALE IN NUMBER
674 ,C_TOT_WMALE IN NUMBER
675 ,C_TOT_BMALE IN NUMBER
676 ,C_TOT_HMALE IN NUMBER
677 ,C_TOT_AMALE IN NUMBER
678 ,C_TOT_IMALE IN NUMBER
679 ,C_TOT_WFEMALE IN NUMBER
680 ,C_TOT_BFEMALE IN NUMBER
681 ,C_TOT_HFEMALE IN NUMBER
682 ,C_TOT_AFEMALE IN NUMBER
683 ,C_TOT_IFEMALE IN NUMBER) RETURN NUMBER IS
684 L_TOT_CAT NUMBER(10);
685 BEGIN
686 L_TOT_CAT := C_TOT_HLMALE + C_TOT_HLFEMALE + C_TOT_TMRACESMALE + C_TOT_TMRACESFEMALE + C_TOT_WMALE + C_TOT_BMALE + C_TOT_HMALE + C_TOT_AMALE + C_TOT_IMALE + C_TOT_WFEMALE + C_TOT_BFEMALE + C_TOT_HFEMALE + C_TOT_AFEMALE + C_TOT_IFEMALE;
687 RETURN (L_TOT_CAT);
688 END C_TOT_CATFORMULA;
689
690 FUNCTION CF_SET_REPFORMULA(HEADQUARTERS IN VARCHAR2
691 ,EST_UNIT IN VARCHAR2) RETURN NUMBER IS
692 BEGIN
693
694 -- Added for bug#12575094
695 IF C_NO_OF_ESTABLISHMENTS = 1 THEN
696 CP_REPORT_TYPE := 'Single Establishment Employer Report - Type 1';
697 ELSIF C_NO_OF_ESTABLISHMENTS > 1
698 AND HEADQUARTERS = 'Y' THEN
699 CP_REPORT_TYPE := 'Headquarters Report - Type 3';
700 ELSIF CP_TOT_EMPS < 50 THEN
701 CP_REPORT_TYPE := 'Establishment Report - Type 8';
702 ELSIF EST_UNIT IS NULL AND CP_TOT_EMPS >= 50 THEN
703 CP_REPORT_TYPE := 'Establishment Report - Type 9';
704 ELSE
705 CP_REPORT_TYPE := 'Establishment Report - Type 4';
706 END IF;
707
708 /* IF C_NO_OF_ESTABLISHMENTS = 1 THEN
709 CP_REPORT_TYPE := 'Single Establishment Employer Report - Type 1';
710 ELSIF C_NO_OF_ESTABLISHMENTS > 1 AND HEADQUARTERS = 'Y' THEN
711 CP_REPORT_TYPE := 'Headquarters Report - Type 3';
712 ELSIF EST_UNIT IS NULL THEN
713 CP_REPORT_TYPE := 'Establishment Report - Type 9';
714 ELSE
715 CP_REPORT_TYPE := 'Establishment Report - Type 4';
716 END IF; */
717
718 CP_PAYROLL_PERIOD_DATE_START := C_PAYROLL_PERIOD_DATE_START;
719 CP_PAYROLL_PERIOD_DATE_END := C_PAYROLL_PERIOD_DATE_END;
720 RETURN NULL;
721 END CF_SET_REPFORMULA;
722
723 FUNCTION CF_SET_CURR_HWFORMULA(ESTAB_STATE IN VARCHAR2
724 ,CONS_JOB_CATEGORY_CODE IN VARCHAR2
725 ,EST_NODE_ID IN NUMBER) RETURN NUMBER IS
726 BEGIN
727 IF ESTAB_STATE = 'HI' THEN
728 SELECT
729 COUNT('person'),
730 COUNT(DECODE(PEO.SEX
731 ,'M'
732 ,1
733 ,NULL)),
734 COUNT(DECODE(PEO.SEX
735 ,'F'
736 ,1
737 ,NULL))
738 INTO CP_HW_CAT,CP_HW_MALE,CP_HW_FEMALE
739 FROM
740 PER_ALL_PEOPLE_F PEO,
741 PER_ALL_ASSIGNMENTS_F ASS,
742 --PER_JOBS_VL JOB - Replaced PER_JOBS_VL with PER_JOBS - bug#11736960
743 PER_JOBS JOB
744 WHERE PEO.PERSON_ID = ASS.PERSON_ID
745 --
746 /* Commented for bug#11736960 starts
747 AND PEO.PER_INFORMATION1 is not NULL
748 Commented for bug#11736960 ends */
749 --
750 AND JOB.JOB_INFORMATION_CATEGORY = 'US'
751 AND P_PAYROLL_PERIOD_DATE_START_T <= NVL(JOB.DATE_TO
752 ,P_PAYROLL_PERIOD_DATE_END_T)
753 AND P_PAYROLL_PERIOD_DATE_END_T >= JOB.DATE_FROM
754 AND JOB.JOB_INFORMATION1 = CONS_JOB_CATEGORY_CODE
755 AND ASS.JOB_ID = JOB.JOB_ID
756 AND ASS.BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID
757 AND PEO.BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID
758 AND JOB.BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID
759 AND PEO.EFFECTIVE_START_DATE = (
760 SELECT
761 MAX(PEO1.EFFECTIVE_START_DATE)
762 FROM
763 PER_ALL_PEOPLE_F PEO1
764 WHERE P_PAYROLL_PERIOD_DATE_START_T <= PEO1.EFFECTIVE_END_DATE
765 AND P_PAYROLL_PERIOD_DATE_END_T >= PEO1.EFFECTIVE_START_DATE
766 AND PEO.PERSON_ID = PEO1.PERSON_ID
767 AND PEO1.CURRENT_EMPLOYEE_FLAG = 'Y' )
768 AND ASS.EFFECTIVE_START_DATE = (
769 SELECT
770 MAX(ASS1.EFFECTIVE_START_DATE)
771 FROM
772 PER_ALL_ASSIGNMENTS_F ASS1,
773 PER_ASSIGNMENT_STATUS_TYPES AST --Added for bug#12594720
774 WHERE P_PAYROLL_PERIOD_DATE_START_T <= ASS1.EFFECTIVE_END_DATE
775 AND P_PAYROLL_PERIOD_DATE_END_T >= ASS1.EFFECTIVE_START_DATE
776 AND ASS.PERSON_ID = ASS1.PERSON_ID
777 AND ASS1.ASSIGNMENT_TYPE = 'E'
778 AND ASS1.PRIMARY_FLAG = 'Y'
779 AND ASS1.ASSIGNMENT_STATUS_TYPE_ID = AST.ASSIGNMENT_STATUS_TYPE_ID
780 AND AST.PER_SYSTEM_STATUS <> 'TERM_ASSIGN')
781 AND ASS.ASSIGNMENT_TYPE = 'E'
782 AND ASS.PRIMARY_FLAG = 'Y'
783 AND EXISTS (
784 SELECT
785 'X'
786 FROM
787 HR_ORGANIZATION_INFORMATION HOI1
788 WHERE TO_CHAR(ASS.ASSIGNMENT_STATUS_TYPE_ID) = HOI1.ORG_INFORMATION1
789 AND HOI1.ORG_INFORMATION_CONTEXT = 'Reporting Statuses'
790 AND HOI1.ORGANIZATION_ID = P_BUSINESS_GROUP_ID
791 )
792 AND EXISTS (
793 SELECT
794 'X'
795 FROM HR_ORGANIZATION_INFORMATION HOI2
796 where ASS.EMPLOYMENT_CATEGORY = HOI2.ORG_INFORMATION1
797 AND HOI2.ORGANIZATION_ID = P_BUSINESS_GROUP_ID
798 AND HOI2.ORG_INFORMATION_CONTEXT = 'Reporting Categories'
799 )
800 /* Replaced this exists clasue with two seperate exists condition inorder to
801 avoid Merge Join Cartesian.
802 AND EXISTS (
803 SELECT
804 'X'
805 FROM
806 HR_ORGANIZATION_INFORMATION HOI1,
807 HR_ORGANIZATION_INFORMATION HOI2
808 WHERE TO_CHAR(ASS.ASSIGNMENT_STATUS_TYPE_ID) = HOI1.ORG_INFORMATION1
809 AND HOI1.ORG_INFORMATION_CONTEXT = 'Reporting Statuses'
810 AND HOI1.ORGANIZATION_ID = P_BUSINESS_GROUP_ID
811 AND ASS.EMPLOYMENT_CATEGORY = HOI2.ORG_INFORMATION1
812 AND HOI2.ORGANIZATION_ID = P_BUSINESS_GROUP_ID
813 AND HOI2.ORG_INFORMATION_CONTEXT = 'Reporting Categories' )
814 */
815 AND EXISTS (
816 SELECT 1
817 FROM
818 PER_GEN_HIERARCHY_NODES PGN
819 WHERE PGN.HIERARCHY_VERSION_ID = P_HIERARCHY_VERSION_ID
820 AND ( PGN.HIERARCHY_NODE_ID = EST_NODE_ID
821 OR PGN.PARENT_HIERARCHY_NODE_ID = EST_NODE_ID )
822 AND PGN.NODE_TYPE in ( 'EST' , 'LOC' )
823 AND ASS.LOCATION_ID = PGN.ENTITY_ID
824 )
825 /* Replaced this in clause with the above exists clause.
826 AND ASS.LOCATION_ID in (
827 SELECT
828 DISTINCT
829 PGN.ENTITY_ID
830 FROM
831 PER_GEN_HIERARCHY_NODES PGN
832 WHERE ( PGN.HIERARCHY_NODE_ID = EST_NODE_ID
833 OR PGN.PARENT_HIERARCHY_NODE_ID = EST_NODE_ID )
834 AND PGN.HIERARCHY_VERSION_ID = P_HIERARCHY_VERSION_ID
835 AND PGN.NODE_TYPE in ( 'EST' , 'LOC' ) )
836 */
837 -- Added for bug#11736960
838 AND EXISTS (SELECT 'X'
839 FROM PER_PEOPLE_EXTRA_INFO PEI
840 WHERE PEI.PERSON_ID = PEO.PERSON_ID
841 AND PEI.INFORMATION_TYPE = 'US_ETHNIC_ORIGIN'
842 AND (PEI.PEI_INFORMATION1 = 'Y'
843 OR PEI.PEI_INFORMATION2 = 'Y'
844 OR PEI.PEI_INFORMATION3 = 'Y'
845 OR PEI.PEI_INFORMATION4 = 'Y'
846 OR PEI.PEI_INFORMATION5 = 'Y'
847 OR PEI.PEI_INFORMATION6 = 'Y'
848 OR PEI.PEI_INFORMATION7 = 'Y'
849 )
850 );
851 ELSE
852 NULL;
853 END IF;
854 RETURN (NULL);
855 END CF_SET_CURR_HWFORMULA;
856
857 FUNCTION CF_SET_AUD_HWFORMULA(ESTAB_STATE IN VARCHAR2
858 ,EST_NODE_ID IN NUMBER) RETURN NUMBER IS
859 HW_AUD_TOT VARCHAR2(10);
860 HW_AUD_MALE VARCHAR2(10);
861 HW_AUD_FEMALE VARCHAR2(10);
862 BEGIN
863 IF ESTAB_STATE = 'HI' THEN
864 BEGIN
865 SELECT
866 LEI_INFORMATION3 P_TOTAL,
867 TO_NUMBER((LEI_INFORMATION4 + LEI_INFORMATION5 + LEI_INFORMATION6 + LEI_INFORMATION7 + LEI_INFORMATION8 + LEI_INFORMATION14 + LEI_INFORMATION16)) TOTALMALE,
868 TO_NUMBER((LEI_INFORMATION9 + LEI_INFORMATION10 + LEI_INFORMATION11 + LEI_INFORMATION12 + LEI_INFORMATION13 + LEI_INFORMATION15 + LEI_INFORMATION17)) TOTALFEM
869 INTO HW_AUD_TOT,HW_AUD_MALE,HW_AUD_FEMALE
870 FROM
871 HR_LOCATION_EXTRA_INFO LEI
872 WHERE LEI_INFORMATION1 = CP_PREV_YEAR_FILED
873 AND TO_CHAR(LEI.LOCATION_ID) in (
874 SELECT
875 DISTINCT
876 PGN.ENTITY_ID
877 FROM
878 PER_GEN_HIERARCHY_NODES PGN
879 WHERE PGN.HIERARCHY_VERSION_ID = P_HIERARCHY_VERSION_ID
880 AND ( PGN.HIERARCHY_NODE_ID = EST_NODE_ID
881 OR PGN.PARENT_HIERARCHY_NODE_ID = EST_NODE_ID )
882 AND PGN.NODE_TYPE in ( 'EST' , 'LOC' ) );
883
884 -- Added for bug#12575094
885 CP_HW_AUD_TOT := TO_NUMBER(HW_AUD_TOT);
886 CP_HW_AUD_MALE := TO_NUMBER(HW_AUD_MALE);
887 CP_HW_AUD_FEMALE := TO_NUMBER(HW_AUD_FEMALE);
888
889 --CP_HW_AUD_TOT := NULL;
890 --CP_HW_AUD_MALE := NULL;
891 --CP_HW_AUD_FEMALE := NULL;
892
893 EXCEPTION
894 WHEN NO_DATA_FOUND THEN
895 FND_MESSAGE.SET_NAME('PER'
896 ,'PER_75230_EST_CLASS_MISSING');
897 /*SRW.MESSAGE('10'
898 ,FND_MESSAGE.GET)*/NULL;
899 /*SRW.MESSAGE('20'
900 ,'message will talk about how there needs to be eit set up and assigned to resp')*/NULL;
901 /*SRW.MESSAGE('30'
902 ,'also will suggest running report for previous year to fill in the figures.')*/NULL;
903 WHEN OTHERS THEN
904 NULL;
905 END;
906 END IF;
907 RETURN NULL;
908 END CF_SET_AUD_HWFORMULA;
909
910 FUNCTION CF_100_EMPSFORMULA RETURN CHAR IS
911 L_COUNT_EMPS NUMBER := 0;
912 BEGIN
913 SELECT
914 COUNT('X')
915 INTO L_COUNT_EMPS
916 FROM
917 PER_ALL_ASSIGNMENTS_F ASS,
918 PER_ALL_PEOPLE_F PEO,
919 --PER_JOBS_VL JOB - Replaced PER_JOBS_VL wth PER_JOBS - bug#11736960
920 PER_JOBS JOB
921 WHERE PEO.PERSON_ID = ASS.PERSON_ID
922 --
923 /* Commented for bug#11736960 starts
924 AND PEO.PER_INFORMATION1 is not NULL
925 Commented for bug#11736960 ends */
926 --
927 AND JOB.JOB_INFORMATION_CATEGORY = 'US'
928 AND P_PAYROLL_PERIOD_DATE_START_T <= NVL(JOB.DATE_TO
929 ,P_PAYROLL_PERIOD_DATE_END_T)
930 AND P_PAYROLL_PERIOD_DATE_END_T >= JOB.DATE_FROM
931 AND JOB.JOB_INFORMATION1 is not null
932 AND ASS.JOB_ID = JOB.JOB_ID
933 AND ASS.BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID
934 --
935 -- Added the check for businees_group_id and person effective start date
936 -- to eliminate count of duplicate record - bug#11736960
937 AND PEO.BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID
938 AND JOB.BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID
939 AND PEO.EFFECTIVE_START_DATE =
940 (SELECT MAX(PEO1.EFFECTIVE_START_DATE)
941 FROM PER_ALL_PEOPLE_F PEO1
942 WHERE P_PAYROLL_PERIOD_DATE_START_T
943 <= PEO1.EFFECTIVE_END_DATE
944 AND P_PAYROLL_PERIOD_DATE_END_T
945 >= PEO1.EFFECTIVE_START_DATE
946 AND PEO.PERSON_ID = PEO1.PERSON_ID
947 AND PEO1.CURRENT_EMPLOYEE_FLAG = 'Y'
948 )
949 --
950 AND ASS.ASSIGNMENT_TYPE = 'E'
951 AND ASS.PRIMARY_FLAG = 'Y'
952 AND ASS.EFFECTIVE_START_DATE = (
953 SELECT
954 MAX(ASS1.EFFECTIVE_START_DATE)
955 FROM
956 PER_ALL_ASSIGNMENTS_F ASS1,
957 PER_ASSIGNMENT_STATUS_TYPES AST --Added for bug#12594720
958 WHERE P_PAYROLL_PERIOD_DATE_START_T <= ASS1.EFFECTIVE_END_DATE
959 AND P_PAYROLL_PERIOD_DATE_END_T >= ASS1.EFFECTIVE_START_DATE
960 AND ASS.PERSON_ID = ASS1.PERSON_ID
961 AND ASS1.ASSIGNMENT_TYPE = 'E'
962 AND ASS1.PRIMARY_FLAG = 'Y'
963 AND ASS1.ASSIGNMENT_STATUS_TYPE_ID = AST.ASSIGNMENT_STATUS_TYPE_ID
964 AND AST.PER_SYSTEM_STATUS <> 'TERM_ASSIGN')
965 AND EXISTS (
966 SELECT
967 'X'
968 FROM
969 HR_ORGANIZATION_INFORMATION HOI1
970 WHERE TO_CHAR(ASS.ASSIGNMENT_STATUS_TYPE_ID) = HOI1.ORG_INFORMATION1
971 AND HOI1.ORG_INFORMATION_CONTEXT = 'Reporting Statuses'
972 AND HOI1.ORGANIZATION_ID = P_BUSINESS_GROUP_ID
973 )
974 AND EXISTS (
975 SELECT
976 'X'
977 FROM HR_ORGANIZATION_INFORMATION HOI2
978 where ASS.EMPLOYMENT_CATEGORY = HOI2.ORG_INFORMATION1
979 AND HOI2.ORGANIZATION_ID = P_BUSINESS_GROUP_ID
980 AND HOI2.ORG_INFORMATION_CONTEXT = 'Reporting Categories'
981 )
982 /* Replaced this exists clasue with two seperate exists condition inorder to
983 avoid Merge Join Cartesian.
984 AND EXISTS (
985 SELECT
986 'X'
987 FROM
988 HR_ORGANIZATION_INFORMATION HOI1,
989 HR_ORGANIZATION_INFORMATION HOI2
990 WHERE TO_CHAR(ASS.ASSIGNMENT_STATUS_TYPE_ID) = HOI1.ORG_INFORMATION1
991 AND HOI1.ORG_INFORMATION_CONTEXT = 'Reporting Statuses'
992 AND HOI1.ORGANIZATION_ID = P_BUSINESS_GROUP_ID
993 AND ASS.EMPLOYMENT_CATEGORY = HOI2.ORG_INFORMATION1
994 AND HOI2.ORGANIZATION_ID = P_BUSINESS_GROUP_ID
995 AND HOI2.ORG_INFORMATION_CONTEXT = 'Reporting Categories' )
996 */
997 /* Commented as not requires - bug#11736960
998 AND P_PAYROLL_PERIOD_DATE_START_T <= ASS.EFFECTIVE_END_DATE
999 AND P_PAYROLL_PERIOD_DATE_END_T >= ASS.EFFECTIVE_START_DATE
1000 */
1001 AND EXISTS (
1002 SELECT 1
1003 FROM
1004 PER_GEN_HIERARCHY_NODES
1005 WHERE HIERARCHY_VERSION_ID = P_HIERARCHY_VERSION_ID
1006 AND ASS.LOCATION_ID = ENTITY_ID)
1007 /* Replaced in clause with exists clause - bug#11736960
1008 AND ASS.LOCATION_ID in (
1009 SELECT
1010 DISTINCT
1011 ENTITY_ID
1012 FROM
1013 PER_GEN_HIERARCHY_NODES
1014 WHERE HIERARCHY_VERSION_ID = P_HIERARCHY_VERSION_ID )
1015 */
1016 -- Added for bug#11736960
1017 AND EXISTS (SELECT 'X'
1018 FROM PER_PEOPLE_EXTRA_INFO PEI
1019 WHERE PEI.PERSON_ID = PEO.PERSON_ID
1020 AND PEI.INFORMATION_TYPE = 'US_ETHNIC_ORIGIN'
1021 AND (PEI.PEI_INFORMATION1 = 'Y'
1022 OR PEI.PEI_INFORMATION2 = 'Y'
1023 OR PEI.PEI_INFORMATION3 = 'Y'
1024 OR PEI.PEI_INFORMATION4 = 'Y'
1025 OR PEI.PEI_INFORMATION5 = 'Y'
1026 OR PEI.PEI_INFORMATION6 = 'Y'
1027 OR PEI.PEI_INFORMATION7 = 'Y'
1028 )
1029 );
1030 IF L_COUNT_EMPS >= 100 THEN
1031 RETURN ('Y');
1032 ELSE
1033 RETURN ('N');
1034 END IF;
1035 END CF_100_EMPSFORMULA;
1036
1037 FUNCTION CF_CREATEUPD_ARCHEITFORMULA(EST_NODE_ID IN NUMBER
1038 ,C_TOT_CAT IN NUMBER
1039 ,C_TOT_WMALE IN NUMBER
1040 ,C_TOT_BMALE IN NUMBER
1041 ,C_TOT_HMALE IN NUMBER
1042 ,C_TOT_AMALE IN NUMBER
1043 ,C_TOT_IMALE IN NUMBER
1044 ,C_TOT_WFEMALE IN NUMBER
1045 ,C_TOT_BFEMALE IN NUMBER
1046 ,C_TOT_HFEMALE IN NUMBER
1047 ,C_TOT_AFEMALE IN NUMBER
1048 ,C_TOT_IFEMALE IN NUMBER
1049 ,C_TOT_HLMALE IN NUMBER
1050 ,C_TOT_HLFEMALE IN NUMBER
1051 ,C_TOT_TMRACESMALE IN NUMBER
1052 ,C_TOT_TMRACESFEMALE IN NUMBER) RETURN NUMBER IS
1053 PRAGMA AUTONOMOUS_TRANSACTION; /*Added for Bug#12599302 */
1054 P_UPDATE VARCHAR2(1) := 'C';
1055 L_LOCATION_ID VARCHAR2(60);
1056 -- L_LOCATION_CODE VARCHAR2(50); Commented for fix of #13785825
1057 L_LOCATION_CODE HR_LOCATIONS_ALL.LOCATION_CODE%TYPE; -- changed declaration for #13785825
1058 L_LOCATION_EXTRA_INFO_ID NUMBER := NULL;
1059 L_OBJECT_VERSION_NUMBER NUMBER := NULL;
1060 L_EIT_COUNT NUMBER := 0;
1061 L_MIN_YEAR VARCHAR2(4) := NULL;
1062 BEGIN
1063 -- Commented IF cluase for bug#12575094
1064 --IF CP_REPORT_TYPE = 'Establishment Report - Type 4' AND CP_TOT_EMPS <= P_MINIMUM_NO_OF_EMPLOYEES THEN
1065 -- NULL;
1066 --ELSE
1067 IF P_REPORT_MODE = 'F' THEN
1068 BEGIN
1069 SELECT
1070 ELOC.LOCATION_ID,
1071 ELOC.LOCATION_CODE
1072 INTO L_LOCATION_ID,L_LOCATION_CODE
1073 FROM
1074 PER_GEN_HIERARCHY_NODES PGN,
1075 HR_LOCATIONS_ALL ELOC
1076 WHERE ( HIERARCHY_NODE_ID = EST_NODE_ID
1077 OR PARENT_HIERARCHY_NODE_ID = EST_NODE_ID )
1078 AND HIERARCHY_VERSION_ID = P_HIERARCHY_VERSION_ID
1079 AND PGN.NODE_TYPE = 'EST'
1080 AND ELOC.LOCATION_ID = PGN.ENTITY_ID;
1081 END;
1082 BEGIN
1083 SELECT
1084 'U',
1085 LOCATION_EXTRA_INFO_ID
1086 INTO P_UPDATE,L_LOCATION_EXTRA_INFO_ID
1087 FROM
1088 HR_LOCATION_EXTRA_INFO
1089 WHERE LEI_INFORMATION1 = P_REPORT_YEAR
1090 AND LEI_INFORMATION_CATEGORY = 'EEO-1 Archive Information'
1091 AND LOCATION_ID = L_LOCATION_ID;
1092 EXCEPTION
1093 WHEN NO_DATA_FOUND THEN
1094 P_UPDATE := 'C';
1095 /*SRW.MESSAGE('20'
1096 ,' ')*/NULL;
1097 /*SRW.MESSAGE('20'
1098 ,'need to create new eit for location ' || L_LOCATION_ID || ' ' || L_LOCATION_CODE)*/NULL;
1099 WHEN OTHERS THEN
1100 NULL;
1101 END;
1102 IF P_UPDATE = 'U' THEN
1103 /*SRW.MESSAGE('10'
1104 ,'p_update ' || P_UPDATE || ' location_id to update is ' || L_LOCATION_ID || ' ' || L_LOCATION_CODE)*/NULL;
1105 /*SRW.MESSAGE('20'
1106 ,' p_location_extra_info_id to delete for update is ' || L_LOCATION_EXTRA_INFO_ID)*/NULL;
1107 BEGIN
1108 SELECT
1109 OBJECT_VERSION_NUMBER
1110 INTO L_OBJECT_VERSION_NUMBER
1111 FROM
1112 HR_LOCATION_EXTRA_INFO
1113 WHERE LOCATION_EXTRA_INFO_ID = L_LOCATION_EXTRA_INFO_ID;
1114 END;
1115 /*SRW.MESSAGE('25'
1116 ,'object version number to delete for update is ' || L_OBJECT_VERSION_NUMBER)*/NULL;
1117 BEGIN
1118 HR_LOCATION_EXTRA_INFO_API.DELETE_LOCATION_EXTRA_INFO(P_VALIDATE => FALSE
1119 ,P_LOCATION_EXTRA_INFO_ID => L_LOCATION_EXTRA_INFO_ID
1120 ,P_OBJECT_VERSION_NUMBER => L_OBJECT_VERSION_NUMBER);
1121 END;
1122 COMMIT;
1123 P_UPDATE := 'C';
1124 END IF;
1125 IF P_UPDATE = 'C' THEN
1126 /*SRW.MESSAGE('21'
1127 ,'p_update ' || P_UPDATE || ' location_id ' || L_LOCATION_ID)*/NULL;
1128 /*SRW.MESSAGE('22'
1129 ,'need to create new eit')*/NULL;
1130 BEGIN
1131 HR_LOCATION_EXTRA_INFO_API.CREATE_LOCATION_EXTRA_INFO(P_VALIDATE => FALSE
1132 ,P_LOCATION_ID => L_LOCATION_ID
1133 ,P_INFORMATION_TYPE => 'EEO-1 Archive Information'
1134 ,P_LEI_INFORMATION_CATEGORY => 'EEO-1 Archive Information'
1135 ,P_LEI_INFORMATION1 => P_REPORT_YEAR
1136 ,P_LEI_INFORMATION2 => P_CONC_REQUEST_ID
1137 ,P_LEI_INFORMATION3 => C_TOT_CAT
1138 ,P_LEI_INFORMATION4 => C_TOT_WMALE
1139 ,P_LEI_INFORMATION5 => C_TOT_BMALE
1140 ,P_LEI_INFORMATION6 => C_TOT_HMALE
1141 ,P_LEI_INFORMATION7 => C_TOT_AMALE
1142 ,P_LEI_INFORMATION8 => C_TOT_IMALE
1143 ,P_LEI_INFORMATION9 => C_TOT_WFEMALE
1144 ,P_LEI_INFORMATION10 => C_TOT_BFEMALE
1145 ,P_LEI_INFORMATION11 => C_TOT_HFEMALE
1146 ,P_LEI_INFORMATION12 => C_TOT_AFEMALE
1147 ,P_LEI_INFORMATION13 => C_TOT_IFEMALE
1148 ,P_LEI_INFORMATION14 => C_TOT_HLMALE
1149 ,P_LEI_INFORMATION15 => C_TOT_HLFEMALE
1150 ,P_LEI_INFORMATION16 => C_TOT_TMRACESMALE
1151 ,P_LEI_INFORMATION17 => C_TOT_TMRACESFEMALE
1152 ,P_LOCATION_EXTRA_INFO_ID => L_LOCATION_EXTRA_INFO_ID
1153 ,P_OBJECT_VERSION_NUMBER => L_OBJECT_VERSION_NUMBER);
1154 END;
1155 COMMIT;
1156 /*SRW.MESSAGE('23'
1157 ,' ')*/NULL;
1158 /*SRW.MESSAGE('23'
1159 ,'eit created for location_id ' || L_LOCATION_ID || ' year ' || P_REPORT_YEAR)*/NULL;
1160 /*SRW.MESSAGE('24'
1161 ,'out params: location_extra_info_id is ' || L_LOCATION_EXTRA_INFO_ID)*/NULL;
1162 /*SRW.MESSAGE('25'
1163 ,'object version number is ' || L_OBJECT_VERSION_NUMBER)*/NULL;
1164 /*SRW.MESSAGE('25'
1165 ,'grand total is ' || C_TOT_CAT)*/NULL;
1166 /*SRW.MESSAGE('23'
1167 ,' ')*/NULL;
1168 END IF;
1169 BEGIN
1170 BEGIN
1171 SELECT
1172 count(*)
1173 INTO L_EIT_COUNT
1174 FROM
1175 HR_LOCATION_EXTRA_INFO LEI
1176 WHERE LOCATION_ID = L_LOCATION_ID
1177 AND INFORMATION_TYPE = 'EEO-1 Archive Information';
1178 EXCEPTION
1179 WHEN NO_DATA_FOUND THEN
1180 NULL;
1181 WHEN OTHERS THEN
1182 NULL;
1183 END;
1184 IF L_EIT_COUNT > 4 THEN
1185 BEGIN
1186 SELECT
1187 MIN(LEI_INFORMATION1)
1188 INTO L_MIN_YEAR
1189 FROM
1190 HR_LOCATION_EXTRA_INFO LEI
1191 WHERE LOCATION_ID = L_LOCATION_ID
1192 AND INFORMATION_TYPE = 'EEO-1 Archive Information';
1193 END;
1194 BEGIN
1195 SELECT
1196 LOCATION_EXTRA_INFO_ID,
1197 OBJECT_VERSION_NUMBER
1198 INTO L_LOCATION_EXTRA_INFO_ID,L_OBJECT_VERSION_NUMBER
1199 FROM
1200 HR_LOCATION_EXTRA_INFO LEI
1201 WHERE LEI_INFORMATION1 = L_MIN_YEAR
1202 AND INFORMATION_TYPE = 'EEO-1 Archive Information'
1203 AND LOCATION_ID = L_LOCATION_ID;
1204 END;
1205 BEGIN
1206 HR_LOCATION_EXTRA_INFO_API.DELETE_LOCATION_EXTRA_INFO(P_VALIDATE => FALSE
1207 ,P_LOCATION_EXTRA_INFO_ID => L_LOCATION_EXTRA_INFO_ID
1208 ,P_OBJECT_VERSION_NUMBER => L_OBJECT_VERSION_NUMBER);
1209 END;
1210 /*SRW.MESSAGE('999999'
1211 ,' there are over 4 Archive EITs for location id ' || L_LOCATION_ID || ' so deleting for year ' || L_MIN_YEAR)*/NULL;
1212 END IF;
1213 END;
1214 END IF;
1215 --END IF; --CP_REPORT_TYPE
1216 RETURN (NULL);
1217 --#13785825 Added the exception block so that proper error message is raised
1218 EXCEPTION
1219 WHEN OTHERS THEN
1220 ROLLBACK;
1221 RAISE;
1222 END CF_CREATEUPD_ARCHEITFORMULA;
1223
1224 FUNCTION CF_AUDIT_REPORT(CONS_JOB_CATEGORY_CODE IN VARCHAR2
1225 ,EST_NODE_ID IN NUMBER) RETURN NUMBER IS
1226 CURSOR C_PERSONS IS
1227 SELECT
1228 PEO.PERSON_ID,
1229 PEO.FIRST_NAME,
1230 PEO.LAST_NAME,
1231 PEO.SEX,
1232 -- PEO.PER_INFORMATION1 ETHNIC, /* Commented for bug#11736960 */
1233 PEO.EMPLOYEE_NUMBER,
1234 ASS.ASSIGNMENT_ID,
1235 ASS.LOCATION_ID,
1236 HL.LOCATION_CODE,
1237 JOB.NAME JOB_NAME,
1238 ASS.JOB_ID
1239 FROM
1240 PER_ALL_PEOPLE_F PEO,
1241 PER_ALL_ASSIGNMENTS_F ASS,
1242 --PER_JOBS_VL JOB, - Replaced PER_JOBS_VL with PER_JOBS -bug#11736960
1243 PER_JOBS JOB,
1244 HR_LOCATIONS_ALL HL
1245 WHERE PEO.PERSON_ID = ASS.PERSON_ID
1246 --
1247 /* Commented for bug#11736960 starts
1248 AND PEO.PER_INFORMATION1 is not null
1249 Commented for bug#11736960 ends */
1250 --
1251 AND PEO.PER_INFORMATION_CATEGORY = 'US'
1252 AND JOB.JOB_INFORMATION_CATEGORY = 'US'
1253 AND P_PAYROLL_PERIOD_DATE_START_T <= NVL(JOB.DATE_TO
1254 ,P_PAYROLL_PERIOD_DATE_END_T)
1255 AND P_PAYROLL_PERIOD_DATE_END_T >= JOB.DATE_FROM
1256 AND JOB.JOB_INFORMATION1 = CONS_JOB_CATEGORY_CODE
1257 AND ASS.JOB_ID = JOB.JOB_ID
1258 AND PEO.EFFECTIVE_START_DATE = (
1259 SELECT
1260 MAX(PEO1.EFFECTIVE_START_DATE)
1261 FROM
1262 --PER_PEOPLE_F PEO1 - Replaced PER_PEOPLE_F with PER_ALL_PEOPLE_F
1263 PER_ALL_PEOPLE_F PEO1
1264 WHERE P_PAYROLL_PERIOD_DATE_START_T <= PEO1.EFFECTIVE_END_DATE
1265 AND P_PAYROLL_PERIOD_DATE_END_T >= PEO1.EFFECTIVE_START_DATE
1266 AND PEO.PERSON_ID = PEO1.PERSON_ID
1267 AND PEO1.CURRENT_EMPLOYEE_FLAG = 'Y' )
1268 AND ASS.EFFECTIVE_START_DATE = (
1269 SELECT
1270 MAX(ASS1.EFFECTIVE_START_DATE)
1271 FROM
1272 --PER_ASSIGNMENTS_F ASS1 - Replaced PER_ASSIGNMENTS_F with PER_ALL_ASSIGNMENTS_F
1273 PER_ALL_ASSIGNMENTS_F ASS1,
1274 PER_ASSIGNMENT_STATUS_TYPES AST --Added for bug#12594720
1275 WHERE P_PAYROLL_PERIOD_DATE_START_T <= ASS1.EFFECTIVE_END_DATE
1276 AND P_PAYROLL_PERIOD_DATE_END_T >= ASS1.EFFECTIVE_START_DATE
1277 AND ASS.PERSON_ID = ASS1.PERSON_ID
1278 AND ASS1.ASSIGNMENT_TYPE = 'E'
1279 AND ASS1.PRIMARY_FLAG = 'Y'
1280 AND ASS1.ASSIGNMENT_STATUS_TYPE_ID = AST.ASSIGNMENT_STATUS_TYPE_ID
1281 AND AST.PER_SYSTEM_STATUS <> 'TERM_ASSIGN')
1282 AND ASS.ASSIGNMENT_TYPE = 'E'
1283 AND ASS.PRIMARY_FLAG = 'Y'
1284 AND ASS.BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID
1285 AND PEO.BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID
1286 AND JOB.BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID
1287 AND EXISTS (
1288 SELECT
1289 'X'
1290 FROM
1291 HR_ORGANIZATION_INFORMATION HOI1
1292 WHERE TO_CHAR(ASS.ASSIGNMENT_STATUS_TYPE_ID) = HOI1.ORG_INFORMATION1
1293 AND HOI1.ORG_INFORMATION_CONTEXT = 'Reporting Statuses'
1294 AND HOI1.ORGANIZATION_ID = P_BUSINESS_GROUP_ID
1295 )
1296 AND EXISTS (
1297 SELECT
1298 'X'
1299 FROM HR_ORGANIZATION_INFORMATION HOI2
1300 where ASS.EMPLOYMENT_CATEGORY = HOI2.ORG_INFORMATION1
1301 AND HOI2.ORGANIZATION_ID = P_BUSINESS_GROUP_ID
1302 AND HOI2.ORG_INFORMATION_CONTEXT = 'Reporting Categories'
1303 )
1304 /* Replaced this exists clasue with two seperate exists condition inorder to
1305 avoid Merge Join Cartesian.
1306 AND EXISTS (
1307 SELECT
1308 'X'
1309 FROM
1310 HR_ORGANIZATION_INFORMATION HOI1,
1311 HR_ORGANIZATION_INFORMATION HOI2
1312 WHERE TO_CHAR(ASS.ASSIGNMENT_STATUS_TYPE_ID) = HOI1.ORG_INFORMATION1
1313 AND HOI1.ORG_INFORMATION_CONTEXT = 'Reporting Statuses'
1314 AND HOI1.ORGANIZATION_ID = P_BUSINESS_GROUP_ID
1315 AND ASS.EMPLOYMENT_CATEGORY = HOI2.ORG_INFORMATION1
1316 AND HOI2.ORGANIZATION_ID = P_BUSINESS_GROUP_ID
1317 AND HOI2.ORG_INFORMATION_CONTEXT = 'Reporting Categories' )
1318 */
1319 AND HL.LOCATION_ID = ASS.LOCATION_ID
1320 AND EXISTS (
1321 SELECT 1
1322 FROM
1323 PER_GEN_HIERARCHY_NODES PGN
1324 WHERE PGN.HIERARCHY_VERSION_ID = P_HIERARCHY_VERSION_ID
1325 AND ( PGN.HIERARCHY_NODE_ID = EST_NODE_ID
1326 OR PGN.PARENT_HIERARCHY_NODE_ID = EST_NODE_ID )
1327 AND PGN.NODE_TYPE in ( 'EST' , 'LOC' )
1328 AND ASS.LOCATION_ID = PGN.ENTITY_ID
1329 )
1330 /* Replaced this in clause with the above exists clause.
1331 AND ASS.LOCATION_ID in (
1332 SELECT
1333 DISTINCT
1334 PGN.ENTITY_ID
1335 FROM
1336 PER_GEN_HIERARCHY_NODES PGN
1337 WHERE PGN.HIERARCHY_VERSION_ID = P_HIERARCHY_VERSION_ID
1338 AND ( PGN.HIERARCHY_NODE_ID = EST_NODE_ID
1339 OR PGN.PARENT_HIERARCHY_NODE_ID = EST_NODE_ID )
1340 AND PGN.NODE_TYPE in ( 'EST' , 'LOC' ) )
1341 */
1342 -- Added for bug#11736960
1343 AND EXISTS (SELECT 'X'
1344 FROM PER_PEOPLE_EXTRA_INFO PEI
1345 WHERE PEI.PERSON_ID = PEO.PERSON_ID
1346 AND PEI.INFORMATION_TYPE = 'US_ETHNIC_ORIGIN'
1347 AND (PEI.PEI_INFORMATION1 = 'Y'
1348 OR PEI.PEI_INFORMATION2 = 'Y'
1349 OR PEI.PEI_INFORMATION3 = 'Y'
1350 OR PEI.PEI_INFORMATION4 = 'Y'
1351 OR PEI.PEI_INFORMATION5 = 'Y'
1352 OR PEI.PEI_INFORMATION6 = 'Y'
1353 OR PEI.PEI_INFORMATION7 = 'Y'
1354 )
1355 )
1356 ORDER BY
1357 LAST_NAME;
1358 L_BUFFER VARCHAR2(2000);
1359 G_DELIMITER VARCHAR2(1) := ',';
1360 G_EOL VARCHAR2(1) := FND_GLOBAL.LOCAL_CHR(10);
1361 BEGIN
1362 IF P_AUDIT_REPORT = 'Y' THEN
1363 -- Commented IF clause for bug#12575094
1364 --IF (CP_REPORT_TYPE = 'Establishment Report - Type 4' OR CP_REPORT_TYPE = 'Establishment Report - Type 9') AND CP_TOT_EMPS <= P_MINIMUM_NO_OF_EMPLOYEES THEN
1365 -- RETURN NULL;
1366 --ELSE
1367 FOR per IN C_PERSONS LOOP
1368 /* Replaced NVL(PER.ETHNIC, ' ') with a function call to PER_US_HR_UTILITY_PKG for bug#11736960 */
1369 L_BUFFER := PER.PERSON_ID || G_DELIMITER || PER.LAST_NAME || G_DELIMITER || NVL(PER.FIRST_NAME
1370 ,' ') || G_DELIMITER || NVL(PER.EMPLOYEE_NUMBER
1371 ,' ') || G_DELIMITER || NVL(PER.SEX
1372 ,' ') || G_DELIMITER || PER_US_HR_UTILITY_PKG.DERIVE_ALIEN_ETHNIC_ORIGIN(PER.PERSON_ID)
1373 || G_DELIMITER || PER.ASSIGNMENT_ID || G_DELIMITER || NVL(TO_CHAR(PER.JOB_ID)
1374 ,' ') || G_DELIMITER || NVL(PER.JOB_NAME
1375 ,' ') || G_DELIMITER || NVL(TO_CHAR(PER.LOCATION_ID)
1376 ,' ') || G_DELIMITER || NVL(PER.LOCATION_CODE
1377 ,' ') || G_DELIMITER || G_EOL;
1378 --PUT(L_BUFFER);
1379 FND_FILE.PUT_LINE(FND_FILE.LOG,L_BUFFER);
1380 END LOOP;
1381 -- END IF; --CP_REPORT_TYPE
1382 END IF;
1383 RETURN NULL;
1384 END CF_AUDIT_REPORT;
1385
1386 FUNCTION C_DEF_SIC_P RETURN VARCHAR2 IS
1387 BEGIN
1388 RETURN C_DEF_SIC;
1389 END C_DEF_SIC_P;
1390
1391 FUNCTION C_DEF_NAICS_P RETURN VARCHAR2 IS
1392 BEGIN
1393 RETURN C_DEF_NAICS;
1394 END C_DEF_NAICS_P;
1395
1396 FUNCTION C_DEF_GRE_P RETURN VARCHAR2 IS
1397 BEGIN
1398 RETURN C_DEF_GRE;
1399 END C_DEF_GRE_P;
1400
1401 FUNCTION C_DEF_DUNS_P RETURN VARCHAR2 IS
1402 BEGIN
1403 RETURN C_DEF_DUNS;
1404 END C_DEF_DUNS_P;
1405
1406 FUNCTION C_DEF_GOV_CON_P RETURN VARCHAR2 IS
1407 BEGIN
1408 RETURN C_DEF_GOV_CON;
1409 END C_DEF_GOV_CON_P;
1410
1411 FUNCTION C_DEF_APPRENT_P RETURN VARCHAR2 IS
1412 BEGIN
1413 RETURN C_DEF_APPRENT;
1414 END C_DEF_APPRENT_P;
1415
1416 FUNCTION C_DEF_ACTIV_1_P RETURN VARCHAR2 IS
1417 BEGIN
1418 RETURN C_DEF_ACTIV_1;
1419 END C_DEF_ACTIV_1_P;
1420
1421 FUNCTION C_DEF_ACTIV_2_P RETURN VARCHAR2 IS
1422 BEGIN
1423 RETURN C_DEF_ACTIV_2;
1424 END C_DEF_ACTIV_2_P;
1425
1426 FUNCTION C_DEF_ACTIV_3_P RETURN VARCHAR2 IS
1427 BEGIN
1428 RETURN C_DEF_ACTIV_3;
1429 END C_DEF_ACTIV_3_P;
1430
1431 FUNCTION C_DEF_ACTIV_4_P RETURN VARCHAR2 IS
1432 BEGIN
1433 RETURN C_DEF_ACTIV_4;
1434 END C_DEF_ACTIV_4_P;
1435
1436 FUNCTION CP_REPORT_YEAR_P RETURN VARCHAR2 IS
1437 BEGIN
1438 RETURN CP_REPORT_YEAR;
1439 END CP_REPORT_YEAR_P;
1440
1441 FUNCTION CP_TOT_EMPS_P RETURN NUMBER IS
1442 BEGIN
1443 RETURN CP_TOT_EMPS;
1444 END CP_TOT_EMPS_P;
1445
1446 FUNCTION C_100_EMPS_P RETURN VARCHAR2 IS
1447 BEGIN
1448 RETURN C_100_EMPS;
1449 END C_100_EMPS_P;
1450
1451 FUNCTION C_EST_ACTIV_1_P RETURN VARCHAR2 IS
1452 BEGIN
1453 RETURN C_EST_ACTIV_1;
1454 END C_EST_ACTIV_1_P;
1455
1456 FUNCTION C_EST_ACTIV_2_P RETURN VARCHAR2 IS
1457 BEGIN
1458 RETURN C_EST_ACTIV_2;
1459 END C_EST_ACTIV_2_P;
1460
1461 FUNCTION C_EST_ACTIV_3_P RETURN VARCHAR2 IS
1462 BEGIN
1463 RETURN C_EST_ACTIV_3;
1464 END C_EST_ACTIV_3_P;
1465
1466 FUNCTION C_EST_ACTIV_4_P RETURN VARCHAR2 IS
1467 BEGIN
1468 RETURN C_EST_ACTIV_4;
1469 END C_EST_ACTIV_4_P;
1470
1471 FUNCTION C_AFFILIATED_P RETURN VARCHAR2 IS
1472 BEGIN
1473 RETURN C_AFFILIATED;
1474 END C_AFFILIATED_P;
1475
1476 FUNCTION CP_PAYROLL_PERIOD_DATE_START_P RETURN DATE IS
1477 BEGIN
1478 RETURN CP_PAYROLL_PERIOD_DATE_START;
1479 END CP_PAYROLL_PERIOD_DATE_START_P;
1480
1481 FUNCTION CP_PAYROLL_PERIOD_DATE_END_P RETURN DATE IS
1482 BEGIN
1483 RETURN CP_PAYROLL_PERIOD_DATE_END;
1484 END CP_PAYROLL_PERIOD_DATE_END_P;
1485
1486 FUNCTION CP_REPORT_TYPE_P RETURN VARCHAR2 IS
1487 BEGIN
1488 RETURN CP_REPORT_TYPE;
1489 END CP_REPORT_TYPE_P;
1490
1491 FUNCTION CP_HW_AUD_MALE_P RETURN NUMBER IS
1492 BEGIN
1493 RETURN CP_HW_AUD_MALE;
1494 END CP_HW_AUD_MALE_P;
1495
1496 FUNCTION CP_HW_AUD_FEMALE_P RETURN NUMBER IS
1497 BEGIN
1498 RETURN CP_HW_AUD_FEMALE;
1499 END CP_HW_AUD_FEMALE_P;
1500
1501 FUNCTION CP_HW_AUD_TOT_P RETURN NUMBER IS
1502 BEGIN
1503 RETURN CP_HW_AUD_TOT;
1504 END CP_HW_AUD_TOT_P;
1505
1506 FUNCTION CP_HW_CAT_P RETURN NUMBER IS
1507 BEGIN
1508 RETURN CP_HW_CAT;
1509 END CP_HW_CAT_P;
1510
1511 FUNCTION CP_HW_FEMALE_P RETURN NUMBER IS
1512 BEGIN
1513 RETURN CP_HW_FEMALE;
1514 END CP_HW_FEMALE_P;
1515
1516 FUNCTION CP_HW_MALE_P RETURN NUMBER IS
1517 BEGIN
1518 RETURN CP_HW_MALE;
1519 END CP_HW_MALE_P;
1520
1521 FUNCTION C_BUSINESS_GROUP_NAME_P RETURN VARCHAR2 IS
1522 BEGIN
1523 RETURN C_BUSINESS_GROUP_NAME;
1524 END C_BUSINESS_GROUP_NAME_P;
1525
1526 FUNCTION C_HIERARCHY_NAME_P RETURN VARCHAR2 IS
1527 BEGIN
1528 RETURN C_HIERARCHY_NAME;
1529 END C_HIERARCHY_NAME_P;
1530
1531 FUNCTION C_HIERARCHY_VERSION_NUM_P RETURN NUMBER IS
1532 BEGIN
1533 RETURN C_HIERARCHY_VERSION_NUM;
1534 END C_HIERARCHY_VERSION_NUM_P;
1535
1536 FUNCTION C_PARENT_ORG_ID_P RETURN NUMBER IS
1537 BEGIN
1538 RETURN C_PARENT_ORG_ID;
1539 END C_PARENT_ORG_ID_P;
1540
1541 FUNCTION C_PARENT_NODE_ID_P RETURN NUMBER IS
1542 BEGIN
1543 RETURN C_PARENT_NODE_ID;
1544 END C_PARENT_NODE_ID_P;
1545
1546 FUNCTION CP_PREV_YEAR_FILED_P RETURN VARCHAR2 IS
1547 BEGIN
1548 RETURN CP_PREV_YEAR_FILED;
1549 END CP_PREV_YEAR_FILED_P;
1550
1551 FUNCTION C_PRIOD_END_DATE_P RETURN DATE IS
1552 BEGIN
1553 RETURN C_PRIOD_END_DATE;
1554 END C_PRIOD_END_DATE_P;
1555
1556 FUNCTION C_ALL_TOTAL_P RETURN NUMBER IS
1557 BEGIN
1558 RETURN C_ALL_TOTAL;
1559 END C_ALL_TOTAL_P;
1560
1561 FUNCTION C_NO_OF_ESTABLISHMENTS_P RETURN NUMBER IS
1562 BEGIN
1563 RETURN C_NO_OF_ESTABLISHMENTS;
1564 END C_NO_OF_ESTABLISHMENTS_P;
1565
1566 FUNCTION C_PAYROLL_PERIOD_DATE_START_P RETURN DATE IS
1567 BEGIN
1568 RETURN C_PAYROLL_PERIOD_DATE_START;
1569 END C_PAYROLL_PERIOD_DATE_START_P;
1570
1571 FUNCTION C_PAYROLL_PERIOD_DATE_END_P RETURN DATE IS
1572 BEGIN
1573 RETURN C_PAYROLL_PERIOD_DATE_END;
1574 END C_PAYROLL_PERIOD_DATE_END_P;
1575
1576 FUNCTION C_REPORT_MODE_P RETURN VARCHAR2 IS
1577 BEGIN
1578 RETURN C_REPORT_MODE;
1579 END C_REPORT_MODE_P;
1580
1581 FUNCTION C_REPORT_YEAR_P RETURN NUMBER IS
1582 BEGIN
1583 RETURN C_REPORT_YEAR;
1584 END C_REPORT_YEAR_P;
1585
1586 FUNCTION GET_BUSINESS_GROUP(P_BUSINESS_GROUP_ID IN NUMBER) RETURN VARCHAR2 IS
1587 X0 VARCHAR2(2000);
1588 BEGIN
1589 begin
1590 X0 := HR_REPORTS.GET_BUSINESS_GROUP(P_BUSINESS_GROUP_ID);
1591 end;
1592 RETURN X0;
1593 END GET_BUSINESS_GROUP;
1594
1595
1596 END PER_PERUSEO1_XMLP_PKG;
[Home] [Help]