1 PACKAGE BODY PER_PERUSEO1_XMLP_PKG AS
2 /* $Header: PERUSEO1B.pls 120.1 2008/01/07 13:26:43 srikrish noship $ */
3 --G_FILE_TYPE TEXT_IO.FILE_TYPE;
4
5 G_IL_FEIN VARCHAR2(10);
6
7 G_FILE_NAME VARCHAR2(30);
8
9 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
10 L_DUMMY VARCHAR2(1);
11 TEST_YEAR DATE;
12 TEST_YEAR1 DATE;
13 L_LOCATION_CODE VARCHAR2(60);
14 L_LOCATION_ID NUMBER(15);
15 L_BUFFER VARCHAR2(1000);
16 G_DELIMITER VARCHAR2(1) := ',';
17 G_EOL VARCHAR2(1) := FND_GLOBAL.LOCAL_CHR(10);
18 temp boolean;
19 BEGIN
20 --HR_STANDARD.EVENT('BEFORE REPORT');
21 temp := P_REPORT_YEARVALIDTRIGGER;
22
23 C_ALL_TOTAL := 0;
24 CP_PREV_YEAR_FILED := TO_CHAR(TO_NUMBER(P_REPORT_YEAR - 1));
25 P_PAYROLL_PERIOD_DATE_START_T := FND_DATE.CANONICAL_TO_DATE(P_PAYROLL_PERIOD_DATE_START);
26 P_PAYROLL_PERIOD_DATE_END_T := FND_DATE.CANONICAL_TO_DATE(P_PAYROLL_PERIOD_DATE_END);
27 C_PAYROLL_PERIOD_DATE_START := P_PAYROLL_PERIOD_DATE_START_T;
28 C_PAYROLL_PERIOD_DATE_END := P_PAYROLL_PERIOD_DATE_END_T;
29 C_REPORT_MODE := P_REPORT_MODE;
30 C_REPORT_YEAR := P_REPORT_YEAR;
31 C_BUSINESS_GROUP_NAME := GET_BUSINESS_GROUP(P_BUSINESS_GROUP_ID);
32 /*SRW.MESSAGE('35'
33 ,'previous year ' || CP_PREV_YEAR_FILED)*/NULL;
34 SELECT
35 PGH.NAME,
36 PGV.VERSION_NUMBER,
37 PGN.ENTITY_ID,
38 PGN.HIERARCHY_NODE_ID
39 INTO C_HIERARCHY_NAME,C_HIERARCHY_VERSION_NUM,C_PARENT_ORG_ID,C_PARENT_NODE_ID
40 FROM
41 PER_GEN_HIERARCHY PGH,
42 PER_GEN_HIERARCHY_VERSIONS PGV,
43 PER_GEN_HIERARCHY_NODES PGN
44 WHERE PGH.HIERARCHY_ID = P_HIERARCHY_ID
45 AND PGH.HIERARCHY_ID = PGV.HIERARCHY_ID
46 AND PGV.HIERARCHY_VERSION_ID = P_HIERARCHY_VERSION_ID
47 AND PGN.HIERARCHY_VERSION_ID = PGV.HIERARCHY_VERSION_ID
48 AND PGN.NODE_TYPE = 'PAR';
49 SELECT
50 COUNT('h_node')
51 INTO C_NO_OF_ESTABLISHMENTS
52 FROM
53 PER_GEN_HIERARCHY_NODES PGHN
54 WHERE PGHN.HIERARCHY_VERSION_ID = P_HIERARCHY_VERSION_ID
55 AND PGHN.NODE_TYPE = 'EST';
56 /*SRW.MESSAGE('10'
57 ,'number of establishments: ' || C_NO_OF_ESTABLISHMENTS)*/NULL;
58 BEGIN
59 SELECT
60 null
61 INTO L_DUMMY
62 FROM
63 HR_ALL_ORGANIZATION_UNITS
64 WHERE ORGANIZATION_ID = C_PARENT_ORG_ID
65 AND LOCATION_ID is not null;
66 EXCEPTION
67 WHEN NO_DATA_FOUND THEN
68 FND_MESSAGE.SET_NAME('PER'
69 ,'PER_75228_ORG_LOC_MISSING');
70 /*SRW.MESSAGE('10'
71 ,FND_MESSAGE.GET)*/NULL;
72 RAISE;
73 END;
74 BEGIN
75 SELECT
76 null
77 INTO L_DUMMY
78 FROM
79 HR_ORGANIZATION_INFORMATION
80 WHERE ORGANIZATION_ID = C_PARENT_ORG_ID
81 AND ORG_INFORMATION_CONTEXT = 'EEO_Spec';
82 EXCEPTION
83 WHEN NO_DATA_FOUND THEN
84 FND_MESSAGE.SET_NAME('PER'
85 ,'PER_75229_EEO_CLASS_MISSING');
86 /*SRW.MESSAGE('10'
87 ,FND_MESSAGE.GET)*/NULL;
88 RAISE;
89 END;
90 BEGIN
91 SELECT
92 null
93 INTO L_DUMMY
94 FROM
95 HR_LOCATION_EXTRA_INFO HLEI1,
96 HR_LOCATION_EXTRA_INFO HLEI2,
97 PER_GEN_HIERARCHY_NODES PGN,
98 HR_LOCATIONS_ALL ELOC
99 WHERE PGN.HIERARCHY_VERSION_ID = P_HIERARCHY_VERSION_ID
100 AND PGN.NODE_TYPE = 'EST'
101 AND ELOC.LOCATION_ID = PGN.ENTITY_ID
102 AND HLEI1.LOCATION_ID = ELOC.LOCATION_ID
103 AND HLEI1.INFORMATION_TYPE = 'EEO-1 Specific Information'
104 AND HLEI1.LEI_INFORMATION_CATEGORY = 'EEO-1 Specific Information'
105 AND HLEI2.LOCATION_ID = ELOC.LOCATION_ID
106 AND HLEI2.INFORMATION_TYPE = 'Establishment Information'
107 AND HLEI2.LEI_INFORMATION_CATEGORY = 'Establishment Information';
108 EXCEPTION
109 WHEN NO_DATA_FOUND THEN
110 FND_MESSAGE.SET_NAME('PER'
111 ,'PER_75230_EST_CLASS_MISSING');
112 /*SRW.MESSAGE('10'
113 ,FND_MESSAGE.GET)*/NULL;
114 RAISE;
115 WHEN OTHERS THEN
116 NULL;
117 END;
118 BEGIN
119 SELECT
120 ELOC.LOCATION_ID,
121 ELOC.LOCATION_CODE
122 INTO L_LOCATION_ID,L_LOCATION_CODE
123 FROM
124 HR_LOCATION_EXTRA_INFO HLEI1,
125 PER_GEN_HIERARCHY_NODES PGN,
126 HR_LOCATIONS_ALL ELOC
127 WHERE PGN.HIERARCHY_VERSION_ID = P_HIERARCHY_VERSION_ID
128 AND PGN.NODE_TYPE = 'EST'
129 AND ELOC.LOCATION_ID = PGN.ENTITY_ID
130 AND HLEI1.LOCATION_ID = ELOC.LOCATION_ID
131 AND HLEI1.INFORMATION_TYPE = 'EEO-1 Archive Information'
132 AND HLEI1.LEI_INFORMATION_CATEGORY = 'EEO-1 Archive Information';
133 EXCEPTION
134 WHEN NO_DATA_FOUND THEN
135 /*SRW.MESSAGE('20'
136 ,'Either (1) there are no establishments within your hierarchy with the extra')*/NULL;
137 /*SRW.MESSAGE('20'
138 ,'information type EEO-1 Archive Information attatched - in which case ')*/NULL;
139 /*SRW.MESSAGE('20'
140 ,'please attatch to appropriate responsibility in the information security screen.')*/NULL;
141 /*SRW.MESSAGE('20'
142 ,' ')*/NULL;
143 /*SRW.MESSAGE('20'
144 ,'or (2) the extra information type EEO-1 Archive Information exists for location ')*/NULL;
145 /*SRW.MESSAGE('20'
146 ,L_LOCATION_CODE || 'but does not yet contain data. If this is your first time to file ')*/NULL;
147 /*SRW.MESSAGE('20'
148 ,'(ie if this is a report type 9) then do not worry about this. ')*/NULL;
149 /*SRW.MESSAGE('20'
150 ,'However if you filed last year then the EIT will have to be filled with last years')*/NULL;
151 /*SRW.MESSAGE('20'
152 ,'totals, either manually or by running the report for last year in final mode.')*/NULL;
153 WHEN OTHERS THEN
154 NULL;
155 END;
156 BEGIN
157 BEGIN
158 SELECT
159 '1',
160 ELOC.LOCATION_ID,
161 ELOC.LOCATION_CODE
162 INTO L_DUMMY,L_LOCATION_ID,L_LOCATION_CODE
163 FROM
164 HR_LOCATION_EXTRA_INFO HLEI1,
165 PER_GEN_HIERARCHY_NODES PGN,
166 HR_LOCATIONS_ALL ELOC
170 AND HLEI1.LOCATION_ID = ELOC.LOCATION_ID
167 WHERE PGN.HIERARCHY_VERSION_ID = P_HIERARCHY_VERSION_ID
168 AND PGN.NODE_TYPE = 'EST'
169 AND ELOC.LOCATION_ID = PGN.ENTITY_ID
171 AND HLEI1.INFORMATION_TYPE = 'EEO-1 Specific Information'
172 AND HLEI1.LEI_INFORMATION_CATEGORY = 'EEO-1 Specific Information'
173 AND HLEI1.LEI_INFORMATION9 = 'Y';
174 EXCEPTION
175 WHEN NO_DATA_FOUND THEN
176 NULL;
177 WHEN OTHERS THEN
178 NULL;
179 END;
180 /*SRW.MESSAGE('444'
181 ,'l_dummy, l_location_id, l_location_code ' || L_DUMMY || ' ' || L_LOCATION_ID || ' ' || L_LOCATION_CODE)*/NULL;
182 IF L_DUMMY = '1' THEN
183 BEGIN
184 SELECT
185 2
186 INTO L_DUMMY
187 FROM
188 HR_LOCATION_EXTRA_INFO
189 WHERE LEI_INFORMATION1 = CP_PREV_YEAR_FILED
190 AND LEI_INFORMATION_CATEGORY = 'EEO-1 Archive Information';
191 EXCEPTION
192 WHEN NO_DATA_FOUND THEN
193 /*SRW.MESSAGE('555'
194 ,'l_dummy, l_location_id, l_location_code ' || L_DUMMY || ' ' || L_LOCATION_ID || ' ' || L_LOCATION_CODE)*/NULL;
195 /*SRW.MESSAGE('30'
196 ,' ')*/NULL;
197 /*SRW.MESSAGE('30'
198 ,'The extra information type EEO-1 Archive Information exists for ' || L_LOCATION_CODE)*/NULL;
199 /*SRW.MESSAGE('30'
200 ,'but does not contain data for last years totals. ')*/NULL;
201 /*SRW.MESSAGE('30'
202 ,' ')*/NULL;
203 /*SRW.MESSAGE('30'
204 ,'As you filed last year then this EIT will have to be filled with last years')*/NULL;
205 /*SRW.MESSAGE('30'
206 ,'totals, either manually or by running the report for last year in final mode.')*/NULL;
207 /*SRW.MESSAGE('30'
208 ,' ')*/NULL;
209 /*SRW.MESSAGE('30'
210 ,'To do this without encountering this error again, set the reported previously field')*/NULL;
211 /*SRW.MESSAGE('30'
212 ,'under location/extra info/EEO-1 Specific Data to No and then run the report in ')*/NULL;
213 /*SRW.MESSAGE('30'
214 ,'Final mode for last years dates.')*/NULL;
215 /*SRW.MESSAGE('30'
216 ,' ')*/NULL;
217 /*SRW.MESSAGE('30'
218 ,'Then set the Reported Previously field back to Yes and run the report for ')*/NULL;
219 /*SRW.MESSAGE('30'
220 ,'this year as normal')*/NULL;
221 /*SRW.MESSAGE('30'
222 ,' ')*/NULL;
223 WHEN OTHERS THEN
224 NULL;
225 END;
226 END IF;
227 END;
228 IF P_AUDIT_REPORT = 'Y' THEN
229 --OPEN;
230 L_BUFFER := 'Person Id' || G_DELIMITER || 'Last Name' ||
231 G_DELIMITER || 'First Name' || G_DELIMITER || 'Employee Number' ||
232 G_DELIMITER || 'Gender' || G_DELIMITER || 'Ethnic Origin' || G_DELIMITER ||
233 'Assignment Id' || G_DELIMITER || 'Job Id' || G_DELIMITER || 'Job Name' || G_DELIMITER ||
234 'Location Id' || G_DELIMITER || 'Location Code' || G_DELIMITER || G_EOL;
235 --PUT(L_BUFFER);
236 FND_FILE.PUT_LINE(FND_FILE.LOG,L_BUFFER);
237 END IF;
238 RETURN TRUE;
239 END BEFOREREPORT;
240
241 FUNCTION P_REPORT_YEARVALIDTRIGGER RETURN BOOLEAN IS
242 BEGIN
243 IF P_PAYROLL_PERIOD_DATE_END_T IS NOT NULL THEN
244 P_REPORT_YEAR := TO_CHAR(FND_DATE.CANONICAL_TO_DATE(P_PAYROLL_PERIOD_DATE_END_T)
245 ,'YYYY');
246 ELSE
247 P_REPORT_YEAR := TO_CHAR(SYSDATE
248 ,'YYYY');
249 END IF;
250 RETURN (TRUE);
251 END P_REPORT_YEARVALIDTRIGGER;
252
253 FUNCTION AFTERREPORT RETURN BOOLEAN IS
254 BEGIN
255 --HR_STANDARD.EVENT('AFTER REPORT');
256 FND_FILE.PUT_LINE(1
257 ,'Total employees of the establishments: ' || C_ALL_TOTAL);
258 FND_FILE.PUT_LINE(1
259 ,' ');
260 IF P_AUDIT_REPORT = 'Y' THEN
261 --CLOSE;
262 null;
263 END IF;
264 RETURN (TRUE);
265 END AFTERREPORT;
266
267 FUNCTION CF_SET_DEFAULTSFORMULA RETURN NUMBER IS
268 CURSOR C_DEFAULTS IS
269 SELECT
273 ORG_INFORMATION4,
270 ORG_INFORMATION1,
271 ORG_INFORMATION2,
272 ORG_INFORMATION3,
274 ORG_INFORMATION5,
275 ORG_INFORMATION6,
276 ORG_INFORMATION8,
277 ORG_INFORMATION9,
278 ORG_INFORMATION11,
279 ORG_INFORMATION12
280 FROM
281 HR_ORGANIZATION_INFORMATION
282 WHERE ORGANIZATION_ID = C_PARENT_ORG_ID
283 AND ORG_INFORMATION_CONTEXT = 'VETS_EEO_Dup';
284 L_DEFAULT C_DEFAULTS%ROWTYPE;
285 BEGIN
286 OPEN C_DEFAULTS;
287 FETCH C_DEFAULTS
288 INTO L_DEFAULT;
289 IF C_DEFAULTS%NOTFOUND THEN
290 NULL;
291 ELSE
292 C_DEF_SIC := L_DEFAULT.ORG_INFORMATION1;
293 C_DEF_NAICS := NVL(L_DEFAULT.ORG_INFORMATION2
294 ,L_DEFAULT.ORG_INFORMATION1);
295 C_DEF_GRE := L_DEFAULT.ORG_INFORMATION3;
296 C_DEF_DUNS := L_DEFAULT.ORG_INFORMATION4;
297 C_DEF_GOV_CON := L_DEFAULT.ORG_INFORMATION5;
298 C_DEF_APPRENT := L_DEFAULT.ORG_INFORMATION6;
299 C_DEF_ACTIV_1 := UPPER(LTRIM(RTRIM(L_DEFAULT.ORG_INFORMATION8)));
300 C_DEF_ACTIV_2 := UPPER(LTRIM(RTRIM(L_DEFAULT.ORG_INFORMATION9)));
301 C_DEF_ACTIV_3 := UPPER(LTRIM(RTRIM(L_DEFAULT.ORG_INFORMATION11)));
302 C_DEF_ACTIV_4 := UPPER(LTRIM(RTRIM(L_DEFAULT.ORG_INFORMATION12)));
303 END IF;
304 CLOSE C_DEFAULTS;
305 RETURN NULL;
306 END CF_SET_DEFAULTSFORMULA;
307
308 FUNCTION C_TOT_EMPSFORMULA(EST_NODE_ID IN NUMBER
309 ,AFFILIATED IN VARCHAR2
310 ,EST_REP_NAME IN VARCHAR2) RETURN NUMBER IS
311 L_COUNT_EMPS NUMBER := 0;
312 BEGIN
313 SELECT
314 COUNT(PEO.PERSON_ID)
315 INTO L_COUNT_EMPS
316 FROM
317 PER_ALL_ASSIGNMENTS_F ASS,
318 PER_ALL_PEOPLE_F PEO,
319 PER_JOBS_VL JOB
320 WHERE PEO.PERSON_ID = ASS.PERSON_ID
321 AND PEO.PER_INFORMATION1 is not NULL
322 AND JOB.JOB_INFORMATION_CATEGORY = 'US'
323 AND P_PAYROLL_PERIOD_DATE_START_T <= NVL(JOB.DATE_TO
324 ,P_PAYROLL_PERIOD_DATE_END_T)
325 AND P_PAYROLL_PERIOD_DATE_END_T >= JOB.DATE_FROM
326 AND JOB.JOB_INFORMATION1 is not NULL
327 AND ASS.JOB_ID = JOB.JOB_ID
328 AND PEO.EFFECTIVE_START_DATE = (
329 SELECT
330 MAX(PEO1.EFFECTIVE_START_DATE)
331 FROM
332 PER_PEOPLE_F PEO1
333 WHERE P_PAYROLL_PERIOD_DATE_START_T <= PEO1.EFFECTIVE_END_DATE
334 AND P_PAYROLL_PERIOD_DATE_END_T >= PEO1.EFFECTIVE_START_DATE
335 AND PEO.PERSON_ID = PEO1.PERSON_ID
336 AND PEO1.CURRENT_EMPLOYEE_FLAG = 'Y' )
337 AND ASS.EFFECTIVE_START_DATE = (
338 SELECT
339 MAX(ASS1.EFFECTIVE_START_DATE)
340 FROM
341 PER_ALL_ASSIGNMENTS_F ASS1
342 WHERE P_PAYROLL_PERIOD_DATE_START_T <= ASS1.EFFECTIVE_END_DATE
343 AND P_PAYROLL_PERIOD_DATE_END_T >= ASS1.EFFECTIVE_START_DATE
344 AND ASS.PERSON_ID = ASS1.PERSON_ID
345 AND ASS1.ASSIGNMENT_TYPE = 'E'
346 AND ASS1.PRIMARY_FLAG = 'Y' )
347 AND ASS.ASSIGNMENT_TYPE = 'E'
348 AND ASS.PRIMARY_FLAG = 'Y'
349 AND ASS.BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID
350 AND PEO.BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID
351 AND JOB.BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID
352 AND EXISTS (
353 SELECT
354 'X'
355 FROM
356 HR_ORGANIZATION_INFORMATION HOI1,
357 HR_ORGANIZATION_INFORMATION HOI2
358 WHERE TO_CHAR(ASS.ASSIGNMENT_STATUS_TYPE_ID) = HOI1.ORG_INFORMATION1
359 AND HOI1.ORG_INFORMATION_CONTEXT = 'Reporting Statuses'
360 AND HOI1.ORGANIZATION_ID = P_BUSINESS_GROUP_ID
361 AND ASS.EMPLOYMENT_CATEGORY = HOI2.ORG_INFORMATION1
362 AND HOI2.ORGANIZATION_ID = P_BUSINESS_GROUP_ID
363 AND HOI2.ORG_INFORMATION_CONTEXT = 'Reporting Categories' )
364 AND P_PAYROLL_PERIOD_DATE_START_T <= ASS.EFFECTIVE_END_DATE
365 AND P_PAYROLL_PERIOD_DATE_END_T >= ASS.EFFECTIVE_START_DATE
366 AND ASS.LOCATION_ID in (
367 SELECT
368 DISTINCT
369 PGN.ENTITY_ID
370 FROM
371 PER_GEN_HIERARCHY_NODES PGN
372 WHERE PGN.HIERARCHY_VERSION_ID = P_HIERARCHY_VERSION_ID
373 AND ( PGN.HIERARCHY_NODE_ID = C_TOT_EMPSFORMULA.EST_NODE_ID
374 OR PGN.PARENT_HIERARCHY_NODE_ID = C_TOT_EMPSFORMULA.EST_NODE_ID )
375 AND PGN.NODE_TYPE in ( 'EST' , 'LOC' ) );
376 /*SRW.MESSAGE(99
377 ,'est_node_id : ' || EST_NODE_ID)*/NULL;
378 /*SRW.MESSAGE(99
379 ,'l_couunt_emps : ' || L_COUNT_EMPS)*/NULL;
380 IF L_COUNT_EMPS > 99 THEN
381 C_100_EMPS := 'Y';
382 ELSE
383 C_100_EMPS := 'N';
384 END IF;
385 CP_TOT_EMPS := L_COUNT_EMPS;
386 CP_REPORT_YEAR := P_REPORT_YEAR;
387 C_AFFILIATED := AFFILIATED;
388 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
389 C_ALL_TOTAL := C_ALL_TOTAL + L_COUNT_EMPS;
390 FND_FILE.PUT_LINE(1
391 ,CP_REPORT_TYPE);
392 FND_FILE.PUT_LINE(1
393 ,'Establishment reporting name: ' || EST_REP_NAME);
394 FND_FILE.PUT_LINE(1
395 ,'Total employees : ' || L_COUNT_EMPS);
396 FND_FILE.PUT_LINE(1
397 ,' ');
398 END IF;
399 RETURN (L_COUNT_EMPS);
400 END C_TOT_EMPSFORMULA;
401
402 FUNCTION CF_SET_EST_ACTIVFORMULA(EST_NODE_ID IN NUMBER) RETURN NUMBER IS
403 CURSOR C_EST_ACT IS
404 SELECT
405 NVL(HLEI.LEI_INFORMATION5
406 ,C_DEF_ACTIV_1),
407 NVL(HLEI.LEI_INFORMATION6
411 NVL(HLEI.LEI_INFORMATION8
408 ,C_DEF_ACTIV_2),
409 NVL(HLEI.LEI_INFORMATION7
410 ,C_DEF_ACTIV_3),
412 ,C_DEF_ACTIV_4)
413 FROM
414 HR_LOCATION_EXTRA_INFO HLEI,
415 PER_GEN_HIERARCHY_NODES PGHN
416 WHERE HLEI.INFORMATION_TYPE = 'EEO-1 Specific Information'
417 AND HLEI.LEI_INFORMATION_CATEGORY = 'EEO-1 Specific Information'
418 AND HLEI.LOCATION_ID = PGHN.ENTITY_ID
419 AND PGHN.PARENT_HIERARCHY_NODE_ID = C_PARENT_NODE_ID
420 AND PGHN.HIERARCHY_NODE_ID = EST_NODE_ID
421 AND PGHN.NODE_TYPE = 'EST';
422 L_EST_ACT_1 VARCHAR2(60) := NULL;
423 L_EST_ACT_2 VARCHAR2(60) := NULL;
424 L_EST_ACT_3 VARCHAR2(60) := NULL;
425 L_EST_ACT_4 VARCHAR2(60) := NULL;
426 BEGIN
427 OPEN C_EST_ACT;
428 FETCH C_EST_ACT
429 INTO L_EST_ACT_1,L_EST_ACT_2,L_EST_ACT_3,L_EST_ACT_4;
430 C_EST_ACTIV_1 := UPPER(LTRIM(RTRIM(L_EST_ACT_1)));
431 C_EST_ACTIV_2 := UPPER(LTRIM(RTRIM(L_EST_ACT_2)));
432 C_EST_ACTIV_3 := UPPER(LTRIM(RTRIM(L_EST_ACT_3)));
433 C_EST_ACTIV_4 := UPPER(LTRIM(RTRIM(L_EST_ACT_4)));
434 RETURN NULL;
435 END CF_SET_EST_ACTIVFORMULA;
436
437 FUNCTION C_TOT_CATFORMULA(C_TOT_HLMALE IN NUMBER
438 ,C_TOT_HLFEMALE IN NUMBER
439 ,C_TOT_TMRACESMALE IN NUMBER
440 ,C_TOT_TMRACESFEMALE IN NUMBER
441 ,C_TOT_WMALE IN NUMBER
442 ,C_TOT_BMALE IN NUMBER
443 ,C_TOT_HMALE IN NUMBER
444 ,C_TOT_AMALE IN NUMBER
445 ,C_TOT_IMALE IN NUMBER
446 ,C_TOT_WFEMALE IN NUMBER
447 ,C_TOT_BFEMALE IN NUMBER
448 ,C_TOT_HFEMALE IN NUMBER
449 ,C_TOT_AFEMALE IN NUMBER
450 ,C_TOT_IFEMALE IN NUMBER) RETURN NUMBER IS
451 L_TOT_CAT NUMBER(10);
452 BEGIN
453 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;
454 RETURN (L_TOT_CAT);
455 END C_TOT_CATFORMULA;
456
457 FUNCTION CF_SET_REPFORMULA(HEADQUARTERS IN VARCHAR2
458 ,EST_UNIT IN VARCHAR2) RETURN NUMBER IS
459 BEGIN
460 IF C_NO_OF_ESTABLISHMENTS = 1 THEN
461 CP_REPORT_TYPE := 'Single Establishment Employer Report - Type 1';
462 ELSIF C_NO_OF_ESTABLISHMENTS > 1 AND HEADQUARTERS = 'Y' THEN
463 CP_REPORT_TYPE := 'Headquarters Report - Type 3';
464 ELSIF EST_UNIT IS NULL THEN
465 CP_REPORT_TYPE := 'Establishment Report - Type 9';
466 ELSE
467 CP_REPORT_TYPE := 'Establishment Report - Type 4';
468 END IF;
469 CP_PAYROLL_PERIOD_DATE_START := C_PAYROLL_PERIOD_DATE_START;
470 CP_PAYROLL_PERIOD_DATE_END := C_PAYROLL_PERIOD_DATE_END;
471 RETURN NULL;
472 END CF_SET_REPFORMULA;
473
474 FUNCTION CF_SET_CURR_HWFORMULA(ESTAB_STATE IN VARCHAR2
475 ,CONS_JOB_CATEGORY_CODE IN VARCHAR2
476 ,EST_NODE_ID IN NUMBER) RETURN NUMBER IS
477 BEGIN
478 IF ESTAB_STATE = 'HI' THEN
479 SELECT
480 COUNT('person'),
481 COUNT(DECODE(PEO.SEX
482 ,'M'
483 ,1
484 ,NULL)),
485 COUNT(DECODE(PEO.SEX
486 ,'F'
487 ,1
488 ,NULL))
489 INTO CP_HW_CAT,CP_HW_MALE,CP_HW_FEMALE
490 FROM
491 PER_ALL_PEOPLE_F PEO,
492 PER_ALL_ASSIGNMENTS_F ASS,
493 PER_JOBS_VL JOB
494 WHERE PEO.PERSON_ID = ASS.PERSON_ID
495 AND PEO.PER_INFORMATION1 is not NULL
496 AND JOB.JOB_INFORMATION_CATEGORY = 'US'
497 AND P_PAYROLL_PERIOD_DATE_START_T <= NVL(JOB.DATE_TO
498 ,P_PAYROLL_PERIOD_DATE_END_T)
499 AND P_PAYROLL_PERIOD_DATE_END_T >= JOB.DATE_FROM
500 AND JOB.JOB_INFORMATION1 = CONS_JOB_CATEGORY_CODE
501 AND ASS.JOB_ID = JOB.JOB_ID
502 AND ASS.BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID
503 AND PEO.BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID
504 AND JOB.BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID
505 AND PEO.EFFECTIVE_START_DATE = (
506 SELECT
507 MAX(PEO1.EFFECTIVE_START_DATE)
508 FROM
509 PER_ALL_PEOPLE_F PEO1
510 WHERE P_PAYROLL_PERIOD_DATE_START_T <= PEO1.EFFECTIVE_END_DATE
511 AND P_PAYROLL_PERIOD_DATE_END_T >= PEO1.EFFECTIVE_START_DATE
512 AND PEO.PERSON_ID = PEO1.PERSON_ID
513 AND PEO1.CURRENT_EMPLOYEE_FLAG = 'Y' )
514 AND ASS.EFFECTIVE_START_DATE = (
515 SELECT
516 MAX(ASS1.EFFECTIVE_START_DATE)
517 FROM
518 PER_ALL_ASSIGNMENTS_F ASS1
519 WHERE P_PAYROLL_PERIOD_DATE_START_T <= ASS1.EFFECTIVE_END_DATE
520 AND P_PAYROLL_PERIOD_DATE_END_T >= ASS1.EFFECTIVE_START_DATE
521 AND ASS.PERSON_ID = ASS1.PERSON_ID
522 AND ASS1.ASSIGNMENT_TYPE = 'E'
523 AND ASS1.PRIMARY_FLAG = 'Y' )
524 AND ASS.ASSIGNMENT_TYPE = 'E'
525 AND ASS.PRIMARY_FLAG = 'Y'
526 AND EXISTS (
527 SELECT
528 'X'
529 FROM
530 HR_ORGANIZATION_INFORMATION HOI1,
531 HR_ORGANIZATION_INFORMATION HOI2
532 WHERE TO_CHAR(ASS.ASSIGNMENT_STATUS_TYPE_ID) = HOI1.ORG_INFORMATION1
533 AND HOI1.ORG_INFORMATION_CONTEXT = 'Reporting Statuses'
534 AND HOI1.ORGANIZATION_ID = P_BUSINESS_GROUP_ID
535 AND ASS.EMPLOYMENT_CATEGORY = HOI2.ORG_INFORMATION1
539 SELECT
536 AND HOI2.ORGANIZATION_ID = P_BUSINESS_GROUP_ID
537 AND HOI2.ORG_INFORMATION_CONTEXT = 'Reporting Categories' )
538 AND ASS.LOCATION_ID in (
540 DISTINCT
541 PGN.ENTITY_ID
542 FROM
543 PER_GEN_HIERARCHY_NODES PGN
544 WHERE ( PGN.HIERARCHY_NODE_ID = EST_NODE_ID
545 OR PGN.PARENT_HIERARCHY_NODE_ID = EST_NODE_ID )
546 AND PGN.HIERARCHY_VERSION_ID = P_HIERARCHY_VERSION_ID
547 AND PGN.NODE_TYPE in ( 'EST' , 'LOC' ) );
548 ELSE
549 NULL;
550 END IF;
551 RETURN (NULL);
552 END CF_SET_CURR_HWFORMULA;
553
554 FUNCTION CF_SET_AUD_HWFORMULA(ESTAB_STATE IN VARCHAR2
555 ,EST_NODE_ID IN NUMBER) RETURN NUMBER IS
556 HW_AUD_TOT VARCHAR2(10);
557 HW_AUD_MALE VARCHAR2(10);
558 HW_AUD_FEMALE VARCHAR2(10);
559 BEGIN
560 IF ESTAB_STATE = 'HI' THEN
561 BEGIN
562 SELECT
563 LEI_INFORMATION3 P_TOTAL,
564 TO_NUMBER((LEI_INFORMATION4 + LEI_INFORMATION5 + LEI_INFORMATION6 + LEI_INFORMATION7 + LEI_INFORMATION8 + LEI_INFORMATION14 + LEI_INFORMATION16)) TOTALMALE,
565 TO_NUMBER((LEI_INFORMATION9 + LEI_INFORMATION10 + LEI_INFORMATION11 + LEI_INFORMATION12 + LEI_INFORMATION13 + LEI_INFORMATION15 + LEI_INFORMATION17)) TOTALFEM
566 INTO HW_AUD_TOT,HW_AUD_MALE,HW_AUD_FEMALE
567 FROM
568 HR_LOCATION_EXTRA_INFO LEI
569 WHERE LEI_INFORMATION1 = CP_PREV_YEAR_FILED
570 AND TO_CHAR(LEI.LOCATION_ID) in (
571 SELECT
572 DISTINCT
573 PGN.ENTITY_ID
574 FROM
575 PER_GEN_HIERARCHY_NODES PGN
576 WHERE PGN.HIERARCHY_VERSION_ID = P_HIERARCHY_VERSION_ID
577 AND ( PGN.HIERARCHY_NODE_ID = EST_NODE_ID
578 OR PGN.PARENT_HIERARCHY_NODE_ID = EST_NODE_ID )
579 AND PGN.NODE_TYPE in ( 'EST' , 'LOC' ) );
580 CP_HW_AUD_TOT := NULL;
581 CP_HW_AUD_MALE := NULL;
582 CP_HW_AUD_FEMALE := NULL;
583 EXCEPTION
584 WHEN NO_DATA_FOUND THEN
585 FND_MESSAGE.SET_NAME('PER'
586 ,'PER_75230_EST_CLASS_MISSING');
587 /*SRW.MESSAGE('10'
588 ,FND_MESSAGE.GET)*/NULL;
589 /*SRW.MESSAGE('20'
590 ,'message will talk about how there needs to be eit set up and assigned to resp')*/NULL;
591 /*SRW.MESSAGE('30'
592 ,'also will suggest running report for previous year to fill in the figures.')*/NULL;
593 WHEN OTHERS THEN
594 NULL;
595 END;
596 END IF;
597 RETURN NULL;
598 END CF_SET_AUD_HWFORMULA;
599
600 FUNCTION CF_100_EMPSFORMULA RETURN CHAR IS
601 L_COUNT_EMPS NUMBER := 0;
602 BEGIN
603 SELECT
604 COUNT('ass')
605 INTO L_COUNT_EMPS
606 FROM
607 PER_ALL_ASSIGNMENTS_F ASS,
608 PER_ALL_PEOPLE_F PEO,
609 PER_JOBS_VL JOB
610 WHERE PEO.PERSON_ID = ASS.PERSON_ID
611 AND PEO.PER_INFORMATION1 is not NULL
612 AND JOB.JOB_INFORMATION_CATEGORY = 'US'
613 AND P_PAYROLL_PERIOD_DATE_START_T <= NVL(JOB.DATE_TO
614 ,P_PAYROLL_PERIOD_DATE_END_T)
615 AND P_PAYROLL_PERIOD_DATE_END_T >= JOB.DATE_FROM
616 AND JOB.JOB_INFORMATION1 is not null
617 AND ASS.JOB_ID = JOB.JOB_ID
618 AND ASS.BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID
619 AND ASS.ASSIGNMENT_TYPE = 'E'
620 AND ASS.PRIMARY_FLAG = 'Y'
621 AND ASS.EFFECTIVE_START_DATE = (
622 SELECT
623 MAX(ASS1.EFFECTIVE_START_DATE)
624 FROM
625 PER_ALL_ASSIGNMENTS_F ASS1
626 WHERE P_PAYROLL_PERIOD_DATE_START_T <= ASS1.EFFECTIVE_END_DATE
627 AND P_PAYROLL_PERIOD_DATE_END_T >= ASS1.EFFECTIVE_START_DATE
628 AND ASS.PERSON_ID = ASS1.PERSON_ID
632 SELECT
629 AND ASS1.ASSIGNMENT_TYPE = 'E'
630 AND ASS1.PRIMARY_FLAG = 'Y' )
631 AND EXISTS (
633 'X'
634 FROM
635 HR_ORGANIZATION_INFORMATION HOI1,
636 HR_ORGANIZATION_INFORMATION HOI2
637 WHERE TO_CHAR(ASS.ASSIGNMENT_STATUS_TYPE_ID) = HOI1.ORG_INFORMATION1
638 AND HOI1.ORG_INFORMATION_CONTEXT = 'Reporting Statuses'
639 AND HOI1.ORGANIZATION_ID = P_BUSINESS_GROUP_ID
640 AND ASS.EMPLOYMENT_CATEGORY = HOI2.ORG_INFORMATION1
641 AND HOI2.ORGANIZATION_ID = P_BUSINESS_GROUP_ID
642 AND HOI2.ORG_INFORMATION_CONTEXT = 'Reporting Categories' )
643 AND P_PAYROLL_PERIOD_DATE_START_T <= ASS.EFFECTIVE_END_DATE
644 AND P_PAYROLL_PERIOD_DATE_END_T >= ASS.EFFECTIVE_START_DATE
645 AND ASS.LOCATION_ID in (
646 SELECT
647 DISTINCT
648 ENTITY_ID
652 IF L_COUNT_EMPS >= 100 THEN
649 FROM
650 PER_GEN_HIERARCHY_NODES
651 WHERE HIERARCHY_VERSION_ID = P_HIERARCHY_VERSION_ID );
653 RETURN ('Y');
654 ELSE
655 RETURN ('N');
656 END IF;
657 END CF_100_EMPSFORMULA;
658
659 FUNCTION CF_CREATEUPD_ARCHEITFORMULA(EST_NODE_ID IN NUMBER
660 ,C_TOT_CAT IN NUMBER
661 ,C_TOT_WMALE IN NUMBER
662 ,C_TOT_BMALE IN NUMBER
663 ,C_TOT_HMALE IN NUMBER
664 ,C_TOT_AMALE IN NUMBER
665 ,C_TOT_IMALE IN NUMBER
666 ,C_TOT_WFEMALE IN NUMBER
667 ,C_TOT_BFEMALE IN NUMBER
668 ,C_TOT_HFEMALE IN NUMBER
669 ,C_TOT_AFEMALE IN NUMBER
670 ,C_TOT_IFEMALE IN NUMBER
671 ,C_TOT_HLMALE IN NUMBER
672 ,C_TOT_HLFEMALE IN NUMBER
673 ,C_TOT_TMRACESMALE IN NUMBER
674 ,C_TOT_TMRACESFEMALE IN NUMBER) RETURN NUMBER IS
675 P_UPDATE VARCHAR2(1) := 'C';
676 L_LOCATION_ID VARCHAR2(60);
677 L_LOCATION_CODE VARCHAR2(50);
678 L_LOCATION_EXTRA_INFO_ID NUMBER := NULL;
679 L_OBJECT_VERSION_NUMBER NUMBER := NULL;
680 L_EIT_COUNT NUMBER := 0;
681 L_MIN_YEAR VARCHAR2(4) := NULL;
682 BEGIN
683 IF CP_REPORT_TYPE = 'Establishment Report - Type 4' AND CP_TOT_EMPS <= P_MINIMUM_NO_OF_EMPLOYEES THEN
684 NULL;
685 ELSE
686 IF P_REPORT_MODE = 'F' THEN
687 BEGIN
688 SELECT
689 ELOC.LOCATION_ID,
690 ELOC.LOCATION_CODE
691 INTO L_LOCATION_ID,L_LOCATION_CODE
692 FROM
693 PER_GEN_HIERARCHY_NODES PGN,
694 HR_LOCATIONS_ALL ELOC
695 WHERE ( HIERARCHY_NODE_ID = EST_NODE_ID
696 OR PARENT_HIERARCHY_NODE_ID = EST_NODE_ID )
697 AND HIERARCHY_VERSION_ID = P_HIERARCHY_VERSION_ID
698 AND PGN.NODE_TYPE = 'EST'
699 AND ELOC.LOCATION_ID = PGN.ENTITY_ID;
700 END;
701 BEGIN
702 SELECT
703 'U',
704 LOCATION_EXTRA_INFO_ID
705 INTO P_UPDATE,L_LOCATION_EXTRA_INFO_ID
706 FROM
707 HR_LOCATION_EXTRA_INFO
708 WHERE LEI_INFORMATION1 = P_REPORT_YEAR
709 AND LEI_INFORMATION_CATEGORY = 'EEO-1 Archive Information'
710 AND LOCATION_ID = L_LOCATION_ID;
711 EXCEPTION
712 WHEN NO_DATA_FOUND THEN
713 P_UPDATE := 'C';
714 /*SRW.MESSAGE('20'
715 ,' ')*/NULL;
716 /*SRW.MESSAGE('20'
717 ,'need to create new eit for location ' || L_LOCATION_ID || ' ' || L_LOCATION_CODE)*/NULL;
718 WHEN OTHERS THEN
719 NULL;
720 END;
721 IF P_UPDATE = 'U' THEN
722 /*SRW.MESSAGE('10'
723 ,'p_update ' || P_UPDATE || ' location_id to update is ' || L_LOCATION_ID || ' ' || L_LOCATION_CODE)*/NULL;
724 /*SRW.MESSAGE('20'
725 ,' p_location_extra_info_id to delete for update is ' || L_LOCATION_EXTRA_INFO_ID)*/NULL;
726 BEGIN
727 SELECT
728 OBJECT_VERSION_NUMBER
729 INTO L_OBJECT_VERSION_NUMBER
730 FROM
731 HR_LOCATION_EXTRA_INFO
732 WHERE LOCATION_EXTRA_INFO_ID = L_LOCATION_EXTRA_INFO_ID;
733 END;
734 /*SRW.MESSAGE('25'
735 ,'object version number to delete for update is ' || L_OBJECT_VERSION_NUMBER)*/NULL;
736 BEGIN
737 HR_LOCATION_EXTRA_INFO_API.DELETE_LOCATION_EXTRA_INFO(P_VALIDATE => FALSE
738 ,P_LOCATION_EXTRA_INFO_ID => L_LOCATION_EXTRA_INFO_ID
739 ,P_OBJECT_VERSION_NUMBER => L_OBJECT_VERSION_NUMBER);
740 END;
741 COMMIT;
742 P_UPDATE := 'C';
743 END IF;
744 IF P_UPDATE = 'C' THEN
745 /*SRW.MESSAGE('21'
746 ,'p_update ' || P_UPDATE || ' location_id ' || L_LOCATION_ID)*/NULL;
747 /*SRW.MESSAGE('22'
748 ,'need to create new eit')*/NULL;
749 BEGIN
750 HR_LOCATION_EXTRA_INFO_API.CREATE_LOCATION_EXTRA_INFO(P_VALIDATE => FALSE
751 ,P_LOCATION_ID => L_LOCATION_ID
752 ,P_INFORMATION_TYPE => 'EEO-1 Archive Information'
753 ,P_LEI_INFORMATION_CATEGORY => 'EEO-1 Archive Information'
754 ,P_LEI_INFORMATION1 => P_REPORT_YEAR
755 ,P_LEI_INFORMATION2 => P_CONC_REQUEST_ID
756 ,P_LEI_INFORMATION3 => C_TOT_CAT
757 ,P_LEI_INFORMATION4 => C_TOT_WMALE
758 ,P_LEI_INFORMATION5 => C_TOT_BMALE
759 ,P_LEI_INFORMATION6 => C_TOT_HMALE
760 ,P_LEI_INFORMATION7 => C_TOT_AMALE
761 ,P_LEI_INFORMATION8 => C_TOT_IMALE
765 ,P_LEI_INFORMATION12 => C_TOT_AFEMALE
762 ,P_LEI_INFORMATION9 => C_TOT_WFEMALE
763 ,P_LEI_INFORMATION10 => C_TOT_BFEMALE
764 ,P_LEI_INFORMATION11 => C_TOT_HFEMALE
766 ,P_LEI_INFORMATION13 => C_TOT_IFEMALE
767 ,P_LEI_INFORMATION14 => C_TOT_HLMALE
768 ,P_LEI_INFORMATION15 => C_TOT_HLFEMALE
769 ,P_LEI_INFORMATION16 => C_TOT_TMRACESMALE
770 ,P_LEI_INFORMATION17 => C_TOT_TMRACESFEMALE
771 ,P_LOCATION_EXTRA_INFO_ID => L_LOCATION_EXTRA_INFO_ID
772 ,P_OBJECT_VERSION_NUMBER => L_OBJECT_VERSION_NUMBER);
773 END;
774 COMMIT;
775 /*SRW.MESSAGE('23'
776 ,' ')*/NULL;
777 /*SRW.MESSAGE('23'
778 ,'eit created for location_id ' || L_LOCATION_ID || ' year ' || P_REPORT_YEAR)*/NULL;
779 /*SRW.MESSAGE('24'
780 ,'out params: location_extra_info_id is ' || L_LOCATION_EXTRA_INFO_ID)*/NULL;
781 /*SRW.MESSAGE('25'
782 ,'object version number is ' || L_OBJECT_VERSION_NUMBER)*/NULL;
783 /*SRW.MESSAGE('25'
784 ,'grand total is ' || C_TOT_CAT)*/NULL;
785 /*SRW.MESSAGE('23'
786 ,' ')*/NULL;
787 END IF;
788 BEGIN
789 BEGIN
790 SELECT
791 count(*)
792 INTO L_EIT_COUNT
793 FROM
794 HR_LOCATION_EXTRA_INFO LEI
795 WHERE LOCATION_ID = L_LOCATION_ID
796 AND INFORMATION_TYPE = 'EEO-1 Archive Information';
797 EXCEPTION
798 WHEN NO_DATA_FOUND THEN
799 NULL;
800 WHEN OTHERS THEN
801 NULL;
802 END;
803 IF L_EIT_COUNT > 4 THEN
804 BEGIN
805 SELECT
806 MIN(LEI_INFORMATION1)
807 INTO L_MIN_YEAR
808 FROM
809 HR_LOCATION_EXTRA_INFO LEI
810 WHERE LOCATION_ID = L_LOCATION_ID
811 AND INFORMATION_TYPE = 'EEO-1 Archive Information';
812 END;
813 BEGIN
814 SELECT
815 LOCATION_EXTRA_INFO_ID,
816 OBJECT_VERSION_NUMBER
817 INTO L_LOCATION_EXTRA_INFO_ID,L_OBJECT_VERSION_NUMBER
818 FROM
819 HR_LOCATION_EXTRA_INFO LEI
820 WHERE LEI_INFORMATION1 = L_MIN_YEAR
821 AND INFORMATION_TYPE = 'EEO-1 Archive Information'
822 AND LOCATION_ID = L_LOCATION_ID;
823 END;
824 BEGIN
825 HR_LOCATION_EXTRA_INFO_API.DELETE_LOCATION_EXTRA_INFO(P_VALIDATE => FALSE
826 ,P_LOCATION_EXTRA_INFO_ID => L_LOCATION_EXTRA_INFO_ID
827 ,P_OBJECT_VERSION_NUMBER => L_OBJECT_VERSION_NUMBER);
828 END;
829 /*SRW.MESSAGE('999999'
830 ,' there are over 4 Archive EITs for location id ' || L_LOCATION_ID || ' so deleting for year ' || L_MIN_YEAR)*/NULL;
831 END IF;
832 END;
833 END IF;
834 END IF;
835 RETURN (NULL);
836 END CF_CREATEUPD_ARCHEITFORMULA;
837
838 FUNCTION CF_AUDIT_REPORT(CONS_JOB_CATEGORY_CODE IN VARCHAR2
839 ,EST_NODE_ID IN NUMBER) RETURN NUMBER IS
840 CURSOR C_PERSONS IS
841 SELECT
842 PEO.PERSON_ID,
843 PEO.FIRST_NAME,
844 PEO.LAST_NAME,
845 PEO.SEX,
846 PEO.PER_INFORMATION1 ETHNIC,
847 PEO.EMPLOYEE_NUMBER,
848 ASS.ASSIGNMENT_ID,
849 ASS.LOCATION_ID,
850 HL.LOCATION_CODE,
851 JOB.NAME JOB_NAME,
852 ASS.JOB_ID
853 FROM
854 PER_ALL_PEOPLE_F PEO,
855 PER_ALL_ASSIGNMENTS_F ASS,
856 PER_JOBS_VL JOB,
857 HR_LOCATIONS_ALL HL
858 WHERE PEO.PERSON_ID = ASS.PERSON_ID
859 AND PEO.PER_INFORMATION1 is not null
860 AND PEO.PER_INFORMATION_CATEGORY = 'US'
861 AND JOB.JOB_INFORMATION_CATEGORY = 'US'
862 AND P_PAYROLL_PERIOD_DATE_START_T <= NVL(JOB.DATE_TO
863 ,P_PAYROLL_PERIOD_DATE_END_T)
864 AND P_PAYROLL_PERIOD_DATE_END_T >= JOB.DATE_FROM
865 AND JOB.JOB_INFORMATION1 = CONS_JOB_CATEGORY_CODE
866 AND ASS.JOB_ID = JOB.JOB_ID
867 AND PEO.EFFECTIVE_START_DATE = (
868 SELECT
869 MAX(PEO1.EFFECTIVE_START_DATE)
870 FROM
871 PER_PEOPLE_F PEO1
872 WHERE P_PAYROLL_PERIOD_DATE_START_T <= PEO1.EFFECTIVE_END_DATE
873 AND P_PAYROLL_PERIOD_DATE_END_T >= PEO1.EFFECTIVE_START_DATE
874 AND PEO.PERSON_ID = PEO1.PERSON_ID
875 AND PEO1.CURRENT_EMPLOYEE_FLAG = 'Y' )
876 AND ASS.EFFECTIVE_START_DATE = (
877 SELECT
878 MAX(ASS1.EFFECTIVE_START_DATE)
879 FROM
880 PER_ASSIGNMENTS_F ASS1
881 WHERE P_PAYROLL_PERIOD_DATE_START_T <= ASS1.EFFECTIVE_END_DATE
882 AND P_PAYROLL_PERIOD_DATE_END_T >= ASS1.EFFECTIVE_START_DATE
886 AND ASS.ASSIGNMENT_TYPE = 'E'
883 AND ASS.PERSON_ID = ASS1.PERSON_ID
884 AND ASS1.ASSIGNMENT_TYPE = 'E'
885 AND ASS1.PRIMARY_FLAG = 'Y' )
887 AND ASS.PRIMARY_FLAG = 'Y'
888 AND ASS.BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID
889 AND PEO.BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID
890 AND JOB.BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID
891 AND EXISTS (
892 SELECT
893 'X'
894 FROM
895 HR_ORGANIZATION_INFORMATION HOI1,
896 HR_ORGANIZATION_INFORMATION HOI2
897 WHERE TO_CHAR(ASS.ASSIGNMENT_STATUS_TYPE_ID) = HOI1.ORG_INFORMATION1
898 AND HOI1.ORG_INFORMATION_CONTEXT = 'Reporting Statuses'
899 AND HOI1.ORGANIZATION_ID = P_BUSINESS_GROUP_ID
900 AND ASS.EMPLOYMENT_CATEGORY = HOI2.ORG_INFORMATION1
901 AND HOI2.ORGANIZATION_ID = P_BUSINESS_GROUP_ID
902 AND HOI2.ORG_INFORMATION_CONTEXT = 'Reporting Categories' )
903 AND HL.LOCATION_ID = ASS.LOCATION_ID
907 PGN.ENTITY_ID
904 AND ASS.LOCATION_ID in (
905 SELECT
906 DISTINCT
908 FROM
909 PER_GEN_HIERARCHY_NODES PGN
910 WHERE PGN.HIERARCHY_VERSION_ID = P_HIERARCHY_VERSION_ID
911 AND ( PGN.HIERARCHY_NODE_ID = EST_NODE_ID
912 OR PGN.PARENT_HIERARCHY_NODE_ID = EST_NODE_ID )
913 AND PGN.NODE_TYPE in ( 'EST' , 'LOC' ) )
914 ORDER BY
915 LAST_NAME;
916 L_BUFFER VARCHAR2(2000);
917 G_DELIMITER VARCHAR2(1) := ',';
918 G_EOL VARCHAR2(1) := FND_GLOBAL.LOCAL_CHR(10);
919 BEGIN
920 IF P_AUDIT_REPORT = 'Y' THEN
921 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
922 RETURN NULL;
923 ELSE
924 FOR per IN C_PERSONS LOOP
925 L_BUFFER := PER.PERSON_ID || G_DELIMITER || PER.LAST_NAME || G_DELIMITER || NVL(PER.FIRST_NAME
926 ,' ') || G_DELIMITER || NVL(PER.EMPLOYEE_NUMBER
927 ,' ') || G_DELIMITER || NVL(PER.SEX
928 ,' ') || G_DELIMITER || NVL(PER.ETHNIC
929 ,' ') || G_DELIMITER || PER.ASSIGNMENT_ID || G_DELIMITER || NVL(PER.JOB_ID
930 ,' ') || G_DELIMITER || NVL(PER.JOB_NAME
931 ,' ') || G_DELIMITER || NVL(PER.LOCATION_ID
932 ,' ') || G_DELIMITER || NVL(PER.LOCATION_CODE
933 ,' ') || G_DELIMITER || G_EOL;
934 --PUT(L_BUFFER);
935 FND_FILE.PUT_LINE(FND_FILE.LOG,L_BUFFER);
936 END LOOP;
937 END IF;
938 END IF;
939 RETURN NULL;
940 END CF_AUDIT_REPORT;
941
942 FUNCTION C_DEF_SIC_P RETURN VARCHAR2 IS
943 BEGIN
944 RETURN C_DEF_SIC;
945 END C_DEF_SIC_P;
946
947 FUNCTION C_DEF_NAICS_P RETURN VARCHAR2 IS
948 BEGIN
949 RETURN C_DEF_NAICS;
950 END C_DEF_NAICS_P;
951
952 FUNCTION C_DEF_GRE_P RETURN VARCHAR2 IS
953 BEGIN
954 RETURN C_DEF_GRE;
955 END C_DEF_GRE_P;
956
957 FUNCTION C_DEF_DUNS_P RETURN VARCHAR2 IS
958 BEGIN
959 RETURN C_DEF_DUNS;
960 END C_DEF_DUNS_P;
961
962 FUNCTION C_DEF_GOV_CON_P RETURN VARCHAR2 IS
963 BEGIN
964 RETURN C_DEF_GOV_CON;
965 END C_DEF_GOV_CON_P;
966
967 FUNCTION C_DEF_APPRENT_P RETURN VARCHAR2 IS
968 BEGIN
969 RETURN C_DEF_APPRENT;
970 END C_DEF_APPRENT_P;
971
972 FUNCTION C_DEF_ACTIV_1_P RETURN VARCHAR2 IS
973 BEGIN
974 RETURN C_DEF_ACTIV_1;
975 END C_DEF_ACTIV_1_P;
976
977 FUNCTION C_DEF_ACTIV_2_P RETURN VARCHAR2 IS
978 BEGIN
979 RETURN C_DEF_ACTIV_2;
980 END C_DEF_ACTIV_2_P;
981
982 FUNCTION C_DEF_ACTIV_3_P RETURN VARCHAR2 IS
983 BEGIN
984 RETURN C_DEF_ACTIV_3;
985 END C_DEF_ACTIV_3_P;
986
987 FUNCTION C_DEF_ACTIV_4_P RETURN VARCHAR2 IS
988 BEGIN
989 RETURN C_DEF_ACTIV_4;
990 END C_DEF_ACTIV_4_P;
991
992 FUNCTION CP_REPORT_YEAR_P RETURN VARCHAR2 IS
993 BEGIN
994 RETURN CP_REPORT_YEAR;
995 END CP_REPORT_YEAR_P;
996
997 FUNCTION CP_TOT_EMPS_P RETURN NUMBER IS
998 BEGIN
999 RETURN CP_TOT_EMPS;
1000 END CP_TOT_EMPS_P;
1001
1002 FUNCTION C_100_EMPS_P RETURN VARCHAR2 IS
1003 BEGIN
1004 RETURN C_100_EMPS;
1005 END C_100_EMPS_P;
1006
1007 FUNCTION C_EST_ACTIV_1_P RETURN VARCHAR2 IS
1008 BEGIN
1009 RETURN C_EST_ACTIV_1;
1010 END C_EST_ACTIV_1_P;
1011
1012 FUNCTION C_EST_ACTIV_2_P RETURN VARCHAR2 IS
1013 BEGIN
1014 RETURN C_EST_ACTIV_2;
1015 END C_EST_ACTIV_2_P;
1016
1020 END C_EST_ACTIV_3_P;
1017 FUNCTION C_EST_ACTIV_3_P RETURN VARCHAR2 IS
1018 BEGIN
1019 RETURN C_EST_ACTIV_3;
1021
1022 FUNCTION C_EST_ACTIV_4_P RETURN VARCHAR2 IS
1023 BEGIN
1024 RETURN C_EST_ACTIV_4;
1025 END C_EST_ACTIV_4_P;
1026
1027 FUNCTION C_AFFILIATED_P RETURN VARCHAR2 IS
1028 BEGIN
1029 RETURN C_AFFILIATED;
1030 END C_AFFILIATED_P;
1031
1032 FUNCTION CP_PAYROLL_PERIOD_DATE_START_P RETURN DATE IS
1033 BEGIN
1034 RETURN CP_PAYROLL_PERIOD_DATE_START;
1035 END CP_PAYROLL_PERIOD_DATE_START_P;
1036
1037 FUNCTION CP_PAYROLL_PERIOD_DATE_END_P RETURN DATE IS
1038 BEGIN
1039 RETURN CP_PAYROLL_PERIOD_DATE_END;
1040 END CP_PAYROLL_PERIOD_DATE_END_P;
1041
1042 FUNCTION CP_REPORT_TYPE_P RETURN VARCHAR2 IS
1043 BEGIN
1044 RETURN CP_REPORT_TYPE;
1045 END CP_REPORT_TYPE_P;
1046
1047 FUNCTION CP_HW_AUD_MALE_P RETURN NUMBER IS
1048 BEGIN
1049 RETURN CP_HW_AUD_MALE;
1050 END CP_HW_AUD_MALE_P;
1051
1052 FUNCTION CP_HW_AUD_FEMALE_P RETURN NUMBER IS
1053 BEGIN
1054 RETURN CP_HW_AUD_FEMALE;
1055 END CP_HW_AUD_FEMALE_P;
1056
1057 FUNCTION CP_HW_AUD_TOT_P RETURN NUMBER IS
1058 BEGIN
1059 RETURN CP_HW_AUD_TOT;
1060 END CP_HW_AUD_TOT_P;
1061
1062 FUNCTION CP_HW_CAT_P RETURN NUMBER IS
1063 BEGIN
1064 RETURN CP_HW_CAT;
1065 END CP_HW_CAT_P;
1066
1067 FUNCTION CP_HW_FEMALE_P RETURN NUMBER IS
1068 BEGIN
1069 RETURN CP_HW_FEMALE;
1070 END CP_HW_FEMALE_P;
1071
1072 FUNCTION CP_HW_MALE_P RETURN NUMBER IS
1073 BEGIN
1074 RETURN CP_HW_MALE;
1075 END CP_HW_MALE_P;
1076
1077 FUNCTION C_BUSINESS_GROUP_NAME_P RETURN VARCHAR2 IS
1078 BEGIN
1079 RETURN C_BUSINESS_GROUP_NAME;
1080 END C_BUSINESS_GROUP_NAME_P;
1081
1082 FUNCTION C_HIERARCHY_NAME_P RETURN VARCHAR2 IS
1083 BEGIN
1084 RETURN C_HIERARCHY_NAME;
1085 END C_HIERARCHY_NAME_P;
1086
1087 FUNCTION C_HIERARCHY_VERSION_NUM_P RETURN NUMBER IS
1088 BEGIN
1089 RETURN C_HIERARCHY_VERSION_NUM;
1090 END C_HIERARCHY_VERSION_NUM_P;
1091
1092 FUNCTION C_PARENT_ORG_ID_P RETURN NUMBER IS
1093 BEGIN
1094 RETURN C_PARENT_ORG_ID;
1095 END C_PARENT_ORG_ID_P;
1096
1097 FUNCTION C_PARENT_NODE_ID_P RETURN NUMBER IS
1098 BEGIN
1099 RETURN C_PARENT_NODE_ID;
1100 END C_PARENT_NODE_ID_P;
1101
1102 FUNCTION CP_PREV_YEAR_FILED_P RETURN VARCHAR2 IS
1103 BEGIN
1104 RETURN CP_PREV_YEAR_FILED;
1105 END CP_PREV_YEAR_FILED_P;
1106
1107 FUNCTION C_PRIOD_END_DATE_P RETURN DATE IS
1108 BEGIN
1109 RETURN C_PRIOD_END_DATE;
1110 END C_PRIOD_END_DATE_P;
1111
1112 FUNCTION C_ALL_TOTAL_P RETURN NUMBER IS
1113 BEGIN
1114 RETURN C_ALL_TOTAL;
1115 END C_ALL_TOTAL_P;
1116
1120 END C_NO_OF_ESTABLISHMENTS_P;
1117 FUNCTION C_NO_OF_ESTABLISHMENTS_P RETURN NUMBER IS
1118 BEGIN
1119 RETURN C_NO_OF_ESTABLISHMENTS;
1121
1122 FUNCTION C_PAYROLL_PERIOD_DATE_START_P RETURN DATE IS
1123 BEGIN
1124 RETURN C_PAYROLL_PERIOD_DATE_START;
1125 END C_PAYROLL_PERIOD_DATE_START_P;
1126
1127 FUNCTION C_PAYROLL_PERIOD_DATE_END_P RETURN DATE IS
1128 BEGIN
1129 RETURN C_PAYROLL_PERIOD_DATE_END;
1130 END C_PAYROLL_PERIOD_DATE_END_P;
1131
1132 FUNCTION C_REPORT_MODE_P RETURN VARCHAR2 IS
1133 BEGIN
1134 RETURN C_REPORT_MODE;
1135 END C_REPORT_MODE_P;
1136
1137 FUNCTION C_REPORT_YEAR_P RETURN NUMBER IS
1138 BEGIN
1139 RETURN C_REPORT_YEAR;
1140 END C_REPORT_YEAR_P;
1141
1142 FUNCTION GET_BUSINESS_GROUP(P_BUSINESS_GROUP_ID IN NUMBER) RETURN VARCHAR2 IS
1143 X0 VARCHAR2(2000);
1144 BEGIN
1145 begin
1146 X0 := HR_REPORTS.GET_BUSINESS_GROUP(P_BUSINESS_GROUP_ID);
1147 end;
1148 RETURN X0;
1149 END GET_BUSINESS_GROUP;
1150
1151
1152 END PER_PERUSEO1_XMLP_PKG;
[Home] [Help]