[Home] [Help]
PACKAGE BODY: APPS.PER_PERUSEOC_XMLP_PKG
Source
4
1 PACKAGE BODY PER_PERUSEOC_XMLP_PKG AS
2 /* $Header: PERUSEOCB.pls 120.7 2011/06/01 10:38:16 nvelaga ship $ */
3 --G_FILE_TYPE TEXT_IO.FILE_TYPE;
5 G_IL_FEIN VARCHAR2(10);
6
7 G_FILE_NAME VARCHAR2(30);
8
9 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
10 L_BUFFER VARCHAR2(400);
11 G_DELIMITER VARCHAR2(1) := ',';
12 G_EOL VARCHAR2(1) := FND_GLOBAL.LOCAL_CHR(10);
13 temp boolean;
14 BEGIN
15
16 --HR_STANDARD.EVENT('BEFORE REPORT');
17
18 /*SRW.MESSAGE('35'
19 ,'previous year ' || CP_PREV_YEAR_FILED)*/NULL;
20 P_PAYROLL_PERIOD_DATE_START_T := FND_DATE.CANONICAL_TO_DATE(P_PAYROLL_PERIOD_DATE_START);
21 P_PAYROLL_PERIOD_DATE_END_T := FND_DATE.CANONICAL_TO_DATE(P_PAYROLL_PERIOD_DATE_END);
22
23 temp := P_REPORT_YEARVALIDTRIGGER;
24 -- Moved CP_PREV_YEAR_FILED from first line to here - bug#12534614
25 CP_PREV_YEAR_FILED := TO_CHAR(TO_NUMBER(P_REPORT_YEAR - 1));
26
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_BUSINESS_GROUP_NAME := GET_BUSINESS_GROUP(P_BUSINESS_GROUP_ID);
30 C_REPORT_MODE := P_REPORT_MODE;
31 SELECT
32 PGH.NAME,
33 PGV.VERSION_NUMBER,
34 PGN.ENTITY_ID,
35 PGN.HIERARCHY_NODE_ID
36 INTO C_HIERARCHY_NAME,C_HIERARCHY_VERSION_NUM,C_PARENT_ORG_ID,C_PARENT_NODE_ID
37 FROM
38 PER_GEN_HIERARCHY PGH,
39 PER_GEN_HIERARCHY_VERSIONS PGV,
40 PER_GEN_HIERARCHY_NODES PGN
41 WHERE PGH.HIERARCHY_ID = P_HIERARCHY_ID
42 AND PGH.HIERARCHY_ID = PGV.HIERARCHY_ID
43 AND PGV.HIERARCHY_VERSION_ID = P_HIERARCHY_VERSION_ID
44 AND PGN.HIERARCHY_VERSION_ID = PGV.HIERARCHY_VERSION_ID
45 AND PGN.NODE_TYPE = 'PAR';
46 /*SRW.MESSAGE('36'
47 ,'c_hierarchy_name ' || C_HIERARCHY_NAME)*/NULL;
48 /*SRW.MESSAGE('37'
49 ,'c_hierarchy_version_num ' || C_HIERARCHY_VERSION_NUM)*/NULL;
50 /*SRW.MESSAGE('38'
51 ,'c_parent_org_id ' || C_PARENT_ORG_ID)*/NULL;
52 /*SRW.MESSAGE('39'
53 ,'c_parent_node_id ' || C_PARENT_NODE_ID)*/NULL;
54 SELECT
55 COUNT(PGHN.HIERARCHY_NODE_ID)
56 INTO C_NO_OF_ESTABLISHMENTS
57 FROM
58 PER_GEN_HIERARCHY_NODES PGHN
59 WHERE PGHN.HIERARCHY_VERSION_ID = P_HIERARCHY_VERSION_ID
60 AND PGHN.NODE_TYPE = 'EST';
61 /*SRW.MESSAGE('40'
62 ,'p_audit_report : ' || P_AUDIT_REPORT)*/NULL;
63 IF P_AUDIT_REPORT = 'Y' THEN
64 --OPEN;
65 L_BUFFER := 'Person Id' || G_DELIMITER || 'Last Name' || G_DELIMITER || 'First Name'
66 || G_DELIMITER || 'Employee Number' || G_DELIMITER || 'Gender' || G_DELIMITER ||
67 'Ethnic Origin' || G_DELIMITER || 'Assignment Id' || G_DELIMITER || 'Job Id' ||
68 G_DELIMITER || 'Job Name' || G_DELIMITER || 'Location Id' || G_DELIMITER || 'Location Code'
69 || G_DELIMITER || G_EOL;
70 -- PUT(L_BUFFER);
71 FND_FILE.PUT_LINE(FND_FILE.LOG,L_BUFFER);
72 NULL;
73 END IF;
74 RETURN TRUE;
75 END BEFOREREPORT;
76
77 FUNCTION P_REPORT_YEARVALIDTRIGGER RETURN BOOLEAN IS
78 C_DATE_FORMAT varchar2(25):='DD-MON-YY';
79 BEGIN
80 IF P_PAYROLL_PERIOD_DATE_END_T IS NOT NULL THEN
81 P_REPORT_YEAR := TO_CHAR(to_date(P_PAYROLL_PERIOD_DATE_END_T,C_DATE_FORMAT),'RRRR');
82 ELSE
83 P_REPORT_YEAR := TO_CHAR(SYSDATE
84 ,'RRRR');
85 END IF;
86 RETURN (TRUE);
87 END P_REPORT_YEARVALIDTRIGGER;
88
89 FUNCTION AFTERREPORT RETURN BOOLEAN IS
90 BEGIN
91 --HR_STANDARD.EVENT('AFTER REPORT');
92 IF P_AUDIT_REPORT = 'Y' THEN
93 -- CLOSE;
94 NULL;
95 END IF;
96 RETURN (TRUE);
97 END AFTERREPORT;
98
99 FUNCTION CF_SET_DEFAULTSFORMULA RETURN NUMBER IS
100 BEGIN
101 C_DEF_SIC := '';
102 C_DEF_NAICS := '';
103 C_DEF_GRE := '';
104 C_DEF_DUNS := '';
105 C_DEF_GOV_CON := '';
106 C_DEF_APPRENT := '';
107 C_DEF_ACTIV_1 := '';
108 C_DEF_ACTIV_2 := '';
109 C_DEF_ACTIV_3 := '';
110 C_DEF_ACTIV_4 := '';
111 RETURN NULL;
112 END CF_SET_DEFAULTSFORMULA;
113
114 FUNCTION C_TOT_EMPSFORMULA RETURN NUMBER IS
115 BEGIN
116 C_100_EMPS := '';
117 RETURN 0;
118 END C_TOT_EMPSFORMULA;
119
120 FUNCTION CF_SET_EST_ACTIVFORMULA RETURN NUMBER IS
121 BEGIN
122 C_EST_ACTIV_1 := '';
123 C_EST_ACTIV_2 := '';
124 C_EST_ACTIV_3 := '';
125 C_EST_ACTIV_4 := '';
126 RETURN NULL;
127 END CF_SET_EST_ACTIVFORMULA;
128
129 FUNCTION C_TOT_CATFORMULA(C_TOT_HLMALE IN NUMBER
130 ,C_TOT_HLFEMALE IN NUMBER
131 ,C_TOT_TMRACESMALE IN NUMBER
132 ,C_TOT_TMRACESFEMALE IN NUMBER
133 ,C_TOT_WMALE IN NUMBER
134 ,C_TOT_BMALE IN NUMBER
135 ,C_TOT_HMALE IN NUMBER
136 ,C_TOT_AMALE IN NUMBER
137 ,C_TOT_IMALE IN NUMBER
138 ,C_TOT_WFEMALE IN NUMBER
139 ,C_TOT_BFEMALE IN NUMBER
140 ,C_TOT_HFEMALE IN NUMBER
141 ,C_TOT_AFEMALE IN NUMBER
142 ,C_TOT_IFEMALE IN NUMBER) RETURN NUMBER IS
143 L_TOT_CAT NUMBER(10);
144 BEGIN
145 L_TOT_CAT := C_TOT_HLMALE + C_TOT_HLFEMALE + C_TOT_TMRACESMALE + C_TOT_TMRACESFEMALE +
146 C_TOT_WMALE + C_TOT_BMALE + C_TOT_HMALE + C_TOT_AMALE + C_TOT_IMALE + C_TOT_WFEMALE +
147 C_TOT_BFEMALE + C_TOT_HFEMALE + C_TOT_AFEMALE + C_TOT_IFEMALE;
148 RETURN (L_TOT_CAT);
149 END C_TOT_CATFORMULA;
150
151 FUNCTION CF_SET_REPFORMULA RETURN NUMBER IS
152 BEGIN
153 C_REPORT_TYPE := 'Consolidated Report - Type 2';
154 C_REPORT_YEAR := P_REPORT_YEAR;
155 RETURN NULL;
156 END CF_SET_REPFORMULA;
157
158 FUNCTION CF_CREATEUPD_ARCHEITFORMULA(C_TOT_CAT IN NUMBER
159 ,C_TOT_WMALE IN NUMBER
160 ,C_TOT_BMALE IN NUMBER
161 ,C_TOT_HMALE IN NUMBER
162 ,C_TOT_AMALE IN NUMBER
163 ,C_TOT_IMALE IN NUMBER
164 ,C_TOT_WFEMALE IN NUMBER
165 ,C_TOT_BFEMALE IN NUMBER
166 ,C_TOT_HFEMALE IN NUMBER
167 ,C_TOT_AFEMALE IN NUMBER
168 ,C_TOT_IFEMALE IN NUMBER
169 ,C_TOT_HLMALE IN NUMBER
170 ,C_TOT_HLFEMALE IN NUMBER
171 ,C_TOT_TMRACESMALE IN NUMBER
172 ,C_TOT_TMRACESFEMALE IN NUMBER) RETURN NUMBER IS
173 P_UPDATE VARCHAR2(1) := 'C';
174 L_EFFECTIVE_DATE DATE := SYSDATE;
175 L_ORG_INFORMATION_ID NUMBER := NULL;
176 L_OBJECT_VERSION_NUMBER NUMBER := NULL;
177 L_ORGANIZATION_ID NUMBER(15,0);
178 L_LOCATION_CODE VARCHAR2(20);
179 L_LOCATION_EXTRA_INFO_ID NUMBER := NULL;
180 L_EIT_COUNT NUMBER := 0;
181 L_MIN_YEAR VARCHAR2(4) := NULL;
182 BEGIN
183 IF P_REPORT_MODE = 'F' THEN
184 BEGIN
185 SELECT
186 'U',
187 ORG_INFORMATION_ID
188 INTO P_UPDATE,L_ORG_INFORMATION_ID
189 FROM
190 HR_ORGANIZATION_INFORMATION
191 WHERE ORG_INFORMATION1 = P_REPORT_YEAR
192 AND ORGANIZATION_ID = C_PARENT_ORG_ID -- Added for bug#12575094
193 AND ORG_INFORMATION_CONTEXT = 'EEO_Archive';
194 EXCEPTION
195 WHEN NO_DATA_FOUND THEN
196 P_UPDATE := 'C';
197 /*SRW.MESSAGE('20'
198 ,' ')*/NULL;
199 /*SRW.MESSAGE('20'
200 ,'need to create new eit')*/NULL;
201 /*SRW.MESSAGE('20'
202 ,' ')*/NULL;
203 /*SRW.MESSAGE('9999'
204 ,'IMPORTANT. IF YOU FILED LAST YEAR BUT LAST YEARS DATES')*/NULL;
205 /*SRW.MESSAGE('9999'
206 ,'ARE NOT BEING PRINTED, PLEASE EITHER MANUALLY ENTER DATA')*/NULL;
207 /*SRW.MESSAGE('9999'
208 ,'IN THE ORGANIZATION EIT, OR RE-RUN THIS REPORT IN FINAL ')*/NULL;
209 /*SRW.MESSAGE('9999'
210 ,'MODE FOR LAST YEARS DATES. ')*/NULL;
211 /*SRW.MESSAGE('20'
212 ,' ')*/NULL;
213 WHEN OTHERS THEN
214 NULL;
215 END;
216 IF P_UPDATE = 'U' THEN
217 /*SRW.MESSAGE('21'
218 ,'p_update ' || P_UPDATE || ' org_id to update is ' || C_PARENT_ORG_ID)*/NULL;
219 /*SRW.MESSAGE('22'
220 ,' org_information_id to delete for update is ' || L_ORG_INFORMATION_ID)*/NULL;
221 BEGIN
222 SELECT
223 OBJECT_VERSION_NUMBER
224 INTO L_OBJECT_VERSION_NUMBER
225 FROM
226 HR_ORGANIZATION_INFORMATION
227 WHERE ORG_INFORMATION_ID = L_ORG_INFORMATION_ID;
228 END;
229 /*SRW.MESSAGE('25'
230 ,'object version number to delete for update is ' || L_OBJECT_VERSION_NUMBER)*/NULL;
231 BEGIN
232 HR_ORGANIZATION_API.DELETE_ORG_MANAGER(P_VALIDATE => FALSE
233 ,P_ORG_INFORMATION_ID => L_ORG_INFORMATION_ID
234 ,P_OBJECT_VERSION_NUMBER => L_OBJECT_VERSION_NUMBER);
235 END;
236 COMMIT;
237 P_UPDATE := 'C';
238 END IF;
239 IF P_UPDATE = 'C' THEN
240 /*SRW.MESSAGE('30'
241 ,' ')*/NULL;
242 /*SRW.MESSAGE('31'
243 ,'p_update ' || P_UPDATE || ' org id ' || C_PARENT_ORG_ID)*/NULL;
244 /*SRW.MESSAGE('32'
245 ,'need to create new eit')*/NULL;
246 BEGIN
247 HR_ORGANIZATION_API.CREATE_ORG_INFORMATION(P_VALIDATE => FALSE
248 ,P_EFFECTIVE_DATE => L_EFFECTIVE_DATE
249 ,P_ORGANIZATION_ID => C_PARENT_ORG_ID
250 ,P_ORG_INFO_TYPE_CODE => 'EEO_Archive'
251 ,P_ORG_INFORMATION1 => P_REPORT_YEAR
252 ,P_ORG_INFORMATION2 => P_CONC_REQUEST_ID
253 ,P_ORG_INFORMATION3 => C_TOT_CAT
254 ,P_ORG_INFORMATION4 => C_TOT_WMALE
255 ,P_ORG_INFORMATION5 => C_TOT_BMALE
256 ,P_ORG_INFORMATION6 => C_TOT_HMALE
257 ,P_ORG_INFORMATION7 => C_TOT_AMALE
258 ,P_ORG_INFORMATION8 => C_TOT_IMALE
259 ,P_ORG_INFORMATION9 => C_TOT_WFEMALE
260 ,P_ORG_INFORMATION10 => C_TOT_BFEMALE
261 ,P_ORG_INFORMATION11 => C_TOT_HFEMALE
262 ,P_ORG_INFORMATION12 => C_TOT_AFEMALE
263 ,P_ORG_INFORMATION13 => C_TOT_IFEMALE
264 ,P_ORG_INFORMATION14 => C_TOT_HLMALE
265 ,P_ORG_INFORMATION15 => C_TOT_HLFEMALE
266 ,P_ORG_INFORMATION16 => C_TOT_TMRACESMALE
267 ,P_ORG_INFORMATION17 => C_TOT_TMRACESFEMALE
268 ,P_ORG_INFORMATION_ID => L_ORG_INFORMATION_ID
269 ,P_OBJECT_VERSION_NUMBER => L_OBJECT_VERSION_NUMBER);
270 END;
271 COMMIT;
272 /*SRW.MESSAGE('23'
273 ,' ')*/NULL;
274 /*SRW.MESSAGE('23'
275 ,'eit created for org id ' || C_PARENT_ORG_ID || ' year ' || P_REPORT_YEAR)*/NULL;
276 /*SRW.MESSAGE('24'
277 ,'out params: org_information_id is ' || L_ORG_INFORMATION_ID)*/NULL;
278 /*SRW.MESSAGE('25'
279 ,'object version number is ' || L_OBJECT_VERSION_NUMBER)*/NULL;
280 /*SRW.MESSAGE('23'
281 ,' ')*/NULL;
282 END IF;
283 BEGIN
284 BEGIN
285 SELECT
286 count(*)
287 INTO L_EIT_COUNT
288 FROM
289 HR_ORGANIZATION_INFORMATION
290 WHERE ORGANIZATION_ID = C_PARENT_ORG_ID
291 AND ORG_INFORMATION_CONTEXT = 'EEO_Archive';
292 EXCEPTION
293 WHEN NO_DATA_FOUND THEN
294 NULL;
295 WHEN OTHERS THEN
296 NULL;
297 END;
298 IF L_EIT_COUNT > 4 THEN
299 BEGIN
300 SELECT
301 MIN(ORG_INFORMATION1)
302 INTO L_MIN_YEAR
303 FROM
304 HR_ORGANIZATION_INFORMATION
305 WHERE ORGANIZATION_ID = C_PARENT_ORG_ID
306 AND ORG_INFORMATION_CONTEXT = 'EEO_Archive';
307 END;
308 BEGIN
309 SELECT
310 ORG_INFORMATION_ID,
311 OBJECT_VERSION_NUMBER
312 INTO L_ORG_INFORMATION_ID,L_OBJECT_VERSION_NUMBER
313 FROM
314 HR_ORGANIZATION_INFORMATION
315 WHERE ORG_INFORMATION1 = L_MIN_YEAR
316 AND ORG_INFORMATION_CONTEXT = 'EEO_Archive'
317 AND ORGANIZATION_ID = C_PARENT_ORG_ID;
318 END;
319 BEGIN
320 HR_ORGANIZATION_API.DELETE_ORG_MANAGER(P_VALIDATE => FALSE
321 ,P_ORG_INFORMATION_ID => L_ORG_INFORMATION_ID
322 ,P_OBJECT_VERSION_NUMBER => L_OBJECT_VERSION_NUMBER);
323 END;
324 /*SRW.MESSAGE('999999'
325 ,' there are over 4 Archive EITs for organization id ' || C_PARENT_ORG_ID ||
326 ' so deleting for year ' || L_MIN_YEAR)*/NULL;
327 END IF;
328 END;
329 END IF;
330 RETURN (NULL);
331 END CF_CREATEUPD_ARCHEITFORMULA;
332
333 FUNCTION CF_AUDIT_REPORTFORMULA(CONS_JOB_CATEGORY_CODE IN VARCHAR2) RETURN NUMBER IS
334 CURSOR C_PERSONS IS
335 SELECT
336 PEO.PERSON_ID,
337 PEO.FIRST_NAME,
338 PEO.LAST_NAME,
339 PEO.SEX,
340 -- PEO.PER_INFORMATION1 ETHNIC, /* Commented for bug#11736960 */
341 PEO.EMPLOYEE_NUMBER,
342 ASS.ASSIGNMENT_ID,
343 ASS.LOCATION_ID,
344 LOC.LOCATION_CODE,
345 JOB.NAME JOB_NAME,
346 ASS.JOB_ID
347 FROM
348 PER_ALL_PEOPLE_F PEO,
349 PER_ASSIGNMENTS_F ASS,
350 --PER_JOBS_VL JOB, - Replaced PER_JOBS_VL with PER_JOBS -bug#11736960
351 PER_JOBS JOB,
352 HR_LOCATIONS_ALL LOC
353 WHERE PEO.PERSON_ID = ASS.PERSON_ID
354 --
355 /* Commented for bug#11736960 starts
356 AND PEO.PER_INFORMATION1 is not null
357 Commented for bug#11736960 ends */
358 --
359 AND PEO.PER_INFORMATION_CATEGORY = 'US'
360 AND JOB.JOB_INFORMATION_CATEGORY = 'US'
361 AND P_PAYROLL_PERIOD_DATE_START_T <= NVL(JOB.DATE_TO
362 ,P_PAYROLL_PERIOD_DATE_END_T)
363 AND P_PAYROLL_PERIOD_DATE_END_T >= JOB.DATE_FROM
364 AND JOB.JOB_INFORMATION1 = CONS_JOB_CATEGORY_CODE
365 AND ASS.JOB_ID = JOB.JOB_ID
366 AND PEO.EFFECTIVE_START_DATE = (
367 SELECT
368 MAX(PEO1.EFFECTIVE_START_DATE)
369 FROM
370 --PER_PEOPLE_F PEO1 - Replaced PER_PEOPLE_F with PER_ALL_PEOPLE_F
371 PER_ALL_PEOPLE_F PEO1
372 WHERE P_PAYROLL_PERIOD_DATE_START_T <= PEO1.EFFECTIVE_END_DATE
373 AND P_PAYROLL_PERIOD_DATE_END_T >= PEO1.EFFECTIVE_START_DATE
374 AND PEO.PERSON_ID = PEO1.PERSON_ID
375 AND PEO1.CURRENT_EMPLOYEE_FLAG = 'Y' )
376 AND ASS.EFFECTIVE_START_DATE = (
377 SELECT
378 MAX(ASS1.EFFECTIVE_START_DATE)
379 FROM
380 PER_ASSIGNMENTS_F ASS1,
381 PER_ASSIGNMENT_STATUS_TYPES AST --Added for bug#12594720
382 WHERE P_PAYROLL_PERIOD_DATE_START_T <= ASS1.EFFECTIVE_END_DATE
383 AND P_PAYROLL_PERIOD_DATE_END_T >= ASS1.EFFECTIVE_START_DATE
384 AND ASS.PERSON_ID = ASS1.PERSON_ID
385 AND ASS1.ASSIGNMENT_TYPE = 'E'
386 AND ASS1.PRIMARY_FLAG = 'Y'
387 AND ASS1.ASSIGNMENT_STATUS_TYPE_ID = AST.ASSIGNMENT_STATUS_TYPE_ID
388 AND AST.PER_SYSTEM_STATUS <> 'TERM_ASSIGN')
389 AND ASS.ASSIGNMENT_TYPE = 'E'
390 AND ASS.PRIMARY_FLAG = 'Y'
391 AND ASS.BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID
392 AND PEO.BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID
393 AND JOB.BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID
394 AND EXISTS (
395 SELECT
396 'X'
397 FROM
398 HR_ORGANIZATION_INFORMATION HOI1
399 WHERE TO_CHAR(ASS.ASSIGNMENT_STATUS_TYPE_ID) = HOI1.ORG_INFORMATION1
400 AND HOI1.ORG_INFORMATION_CONTEXT = 'Reporting Statuses'
401 AND HOI1.ORGANIZATION_ID = P_BUSINESS_GROUP_ID
402 )
403 AND EXISTS (
404 SELECT
405 'X'
406 FROM HR_ORGANIZATION_INFORMATION HOI2
407 where ASS.EMPLOYMENT_CATEGORY = HOI2.ORG_INFORMATION1
408 AND HOI2.ORGANIZATION_ID = P_BUSINESS_GROUP_ID
409 AND HOI2.ORG_INFORMATION_CONTEXT = 'Reporting Categories'
410 )
411 /* Replaced this exists clasue with two seperate exists condition inorder to
412 avoid Merge Join Cartesian.
413 AND EXISTS (
414 SELECT
415 'X'
416 FROM
417 HR_ORGANIZATION_INFORMATION HOI1,
418 HR_ORGANIZATION_INFORMATION HOI2
419 WHERE TO_CHAR(ASS.ASSIGNMENT_STATUS_TYPE_ID) = HOI1.ORG_INFORMATION1
420 AND HOI1.ORG_INFORMATION_CONTEXT = 'Reporting Statuses'
421 AND HOI1.ORGANIZATION_ID = P_BUSINESS_GROUP_ID
422 AND ASS.EMPLOYMENT_CATEGORY = HOI2.ORG_INFORMATION1
423 AND HOI2.ORGANIZATION_ID = P_BUSINESS_GROUP_ID
424 AND HOI2.ORG_INFORMATION_CONTEXT = 'Reporting Categories' )
425 */
426 AND ASS.LOCATION_ID = LOC.LOCATION_ID
427 AND ASS.LOCATION_ID in (
428 SELECT
429 ENTITY_ID
430 FROM
431 PER_GEN_HIERARCHY_NODES PGHN,
432 HR_LOCATION_EXTRA_INFO HLEI1,
433 HR_LOCATION_EXTRA_INFO HLEI2
434 WHERE PGHN.NODE_TYPE <> 'PAR'
435 AND HIERARCHY_VERSION_ID = P_HIERARCHY_VERSION_ID
436 AND TO_CHAR(HLEI1.LOCATION_ID) = PGHN.ENTITY_ID
437 AND TO_CHAR(HLEI2.LOCATION_ID) = PGHN.ENTITY_ID
438 AND HLEI1.LOCATION_ID = HLEI2.LOCATION_ID
439 AND HLEI1.INFORMATION_TYPE = 'EEO-1 Specific Information'
440 AND HLEI1.LEI_INFORMATION_CATEGORY = 'EEO-1 Specific Information'
441 AND HLEI2.INFORMATION_TYPE = 'Establishment Information'
442 AND HLEI2.LEI_INFORMATION_CATEGORY = 'Establishment Information' )
443 -- Added for bug#11736960
444 AND EXISTS (SELECT 'X'
445 FROM PER_PEOPLE_EXTRA_INFO PEI
446 WHERE PEI.PERSON_ID = PEO.PERSON_ID
447 AND PEI.INFORMATION_TYPE = 'US_ETHNIC_ORIGIN'
448 AND (PEI.PEI_INFORMATION1 = 'Y'
449 OR PEI.PEI_INFORMATION2 = 'Y'
450 OR PEI.PEI_INFORMATION3 = 'Y'
451 OR PEI.PEI_INFORMATION4 = 'Y'
452 OR PEI.PEI_INFORMATION5 = 'Y'
453 OR PEI.PEI_INFORMATION6 = 'Y'
454 OR PEI.PEI_INFORMATION7 = 'Y'
455 )
456 );
457 L_BUFFER VARCHAR2(2000);
458 G_DELIMITER VARCHAR2(1) := ',';
459 G_EOL VARCHAR2(1) := FND_GLOBAL.LOCAL_CHR(10);
460 BEGIN
461 IF P_AUDIT_REPORT = 'Y' THEN
462 FOR per IN C_PERSONS LOOP
463 /* Replaced NVL(PER.ETHNIC, ' ') with a function call to PER_US_HR_UTILITY_PKG for bug#11736960 */
464 L_BUFFER := PER.PERSON_ID || G_DELIMITER || PER.LAST_NAME || G_DELIMITER || NVL(PER.FIRST_NAME
465 ,' ') || G_DELIMITER || NVL(PER.EMPLOYEE_NUMBER
466 ,' ') || G_DELIMITER || NVL(PER.SEX
467 ,' ') || G_DELIMITER || PER_US_HR_UTILITY_PKG.DERIVE_ALIEN_ETHNIC_ORIGIN(PER.PERSON_ID)
468 || G_DELIMITER || PER.ASSIGNMENT_ID || G_DELIMITER || NVL(TO_CHAR(PER.JOB_ID)
469 ,' ') || G_DELIMITER || NVL(PER.JOB_NAME
470 ,' ') || G_DELIMITER || NVL(TO_CHAR(PER.LOCATION_ID)
471 ,' ') || G_DELIMITER || NVL(PER.LOCATION_CODE
472 ,' ') || G_DELIMITER || G_EOL;
473 --PUT(L_BUFFER);
474 FND_FILE.PUT_LINE(FND_FILE.LOG,L_BUFFER);
475 END LOOP;
476 END IF;
477 RETURN NULL;
478 END CF_AUDIT_REPORTFORMULA;
479
480 FUNCTION C_DEF_SIC_P RETURN VARCHAR2 IS
481 BEGIN
482 RETURN C_DEF_SIC;
483 END C_DEF_SIC_P;
484
485 FUNCTION C_DEF_NAICS_P RETURN VARCHAR2 IS
486 BEGIN
487 RETURN C_DEF_NAICS;
488 END C_DEF_NAICS_P;
489
490 FUNCTION C_DEF_GRE_P RETURN NUMBER IS
491 BEGIN
492 RETURN C_DEF_GRE;
493 END C_DEF_GRE_P;
494
495 FUNCTION C_DEF_DUNS_P RETURN VARCHAR2 IS
496 BEGIN
497 RETURN C_DEF_DUNS;
498 END C_DEF_DUNS_P;
499
500 FUNCTION C_DEF_GOV_CON_P RETURN VARCHAR2 IS
501 BEGIN
502 RETURN C_DEF_GOV_CON;
503 END C_DEF_GOV_CON_P;
504
505 FUNCTION C_DEF_APPRENT_P RETURN VARCHAR2 IS
506 BEGIN
507 RETURN C_DEF_APPRENT;
508 END C_DEF_APPRENT_P;
509
510 FUNCTION C_DEF_ACTIV_1_P RETURN VARCHAR2 IS
511 BEGIN
512 RETURN C_DEF_ACTIV_1;
513 END C_DEF_ACTIV_1_P;
514
515 FUNCTION C_DEF_ACTIV_2_P RETURN VARCHAR2 IS
516 BEGIN
517 RETURN C_DEF_ACTIV_2;
518 END C_DEF_ACTIV_2_P;
519
520 FUNCTION C_DEF_ACTIV_3_P RETURN VARCHAR2 IS
521 BEGIN
522 RETURN C_DEF_ACTIV_3;
523 END C_DEF_ACTIV_3_P;
524
525 FUNCTION C_DEF_ACTIV_4_P RETURN VARCHAR2 IS
526 BEGIN
527 RETURN C_DEF_ACTIV_4;
528 END C_DEF_ACTIV_4_P;
529
530 FUNCTION C_100_EMPS_P RETURN VARCHAR2 IS
531 BEGIN
532 RETURN C_100_EMPS;
533 END C_100_EMPS_P;
534
535 FUNCTION C_EST_ACTIV_1_P RETURN VARCHAR2 IS
536 BEGIN
537 RETURN C_EST_ACTIV_1;
538 END C_EST_ACTIV_1_P;
539
540 FUNCTION C_EST_ACTIV_2_P RETURN VARCHAR2 IS
541 BEGIN
542 RETURN C_EST_ACTIV_2;
543 END C_EST_ACTIV_2_P;
544
545 FUNCTION C_EST_ACTIV_3_P RETURN VARCHAR2 IS
546 BEGIN
547 RETURN C_EST_ACTIV_3;
548 END C_EST_ACTIV_3_P;
549
550 FUNCTION C_EST_ACTIV_4_P RETURN VARCHAR2 IS
551 BEGIN
552 RETURN C_EST_ACTIV_4;
553 END C_EST_ACTIV_4_P;
554
555 FUNCTION C_BUSINESS_GROUP_NAME_P RETURN VARCHAR2 IS
556 BEGIN
557 RETURN C_BUSINESS_GROUP_NAME;
558 END C_BUSINESS_GROUP_NAME_P;
559
560 FUNCTION C_HIERARCHY_NAME_P RETURN VARCHAR2 IS
561 BEGIN
562 RETURN C_HIERARCHY_NAME;
563 END C_HIERARCHY_NAME_P;
564
565 FUNCTION C_HIERARCHY_VERSION_NUM_P RETURN NUMBER IS
566 BEGIN
567 RETURN C_HIERARCHY_VERSION_NUM;
568 END C_HIERARCHY_VERSION_NUM_P;
569
570 FUNCTION C_PARENT_ORG_ID_P RETURN NUMBER IS
571 BEGIN
572 RETURN C_PARENT_ORG_ID;
573 END C_PARENT_ORG_ID_P;
574
575 FUNCTION C_PARENT_NODE_ID_P RETURN NUMBER IS
576 BEGIN
577 RETURN C_PARENT_NODE_ID;
578 END C_PARENT_NODE_ID_P;
579
580 FUNCTION C_NO_OF_ESTABLISHMENTS_P RETURN NUMBER IS
581 BEGIN
582 RETURN C_NO_OF_ESTABLISHMENTS;
583 END C_NO_OF_ESTABLISHMENTS_P;
584
585 FUNCTION C_REPORT_TYPE_P RETURN VARCHAR2 IS
586 BEGIN
587 RETURN C_REPORT_TYPE;
588 END C_REPORT_TYPE_P;
589
590 FUNCTION CP_PREV_YEAR_FILED_P RETURN VARCHAR2 IS
591 BEGIN
592 RETURN CP_PREV_YEAR_FILED;
593 END CP_PREV_YEAR_FILED_P;
594
595 FUNCTION C_REPORT_YEAR_P RETURN VARCHAR2 IS
596 BEGIN
597 RETURN C_REPORT_YEAR;
598 END C_REPORT_YEAR_P;
599
600 FUNCTION C_PAYROLL_PERIOD_DATE_START_P RETURN DATE IS
601 BEGIN
602 RETURN C_PAYROLL_PERIOD_DATE_START;
603 END C_PAYROLL_PERIOD_DATE_START_P;
604
605 FUNCTION C_PAYROLL_PERIOD_DATE_END_P RETURN DATE IS
606 BEGIN
607 RETURN C_PAYROLL_PERIOD_DATE_END;
608 END C_PAYROLL_PERIOD_DATE_END_P;
609
610 FUNCTION C_REPORT_MODE_P RETURN VARCHAR2 IS
611 BEGIN
612 RETURN C_REPORT_MODE;
613 END C_REPORT_MODE_P;
614
615 PROCEDURE ASSERT_CONDITION(P_CONDITION IN BOOLEAN) IS
616 BEGIN
617 /*STPROC.INIT('declare P_CONDITION BOOLEAN;
618 begin P_CONDITION := sys.diutil.int_to_bool(:P_CONDITION);
619 HR_GENERAL.ASSERT_CONDITION(P_CONDITION); end;');
620 STPROC.BIND_I(P_CONDITION);
621 STPROC.EXECUTE;*/null;
622 END ASSERT_CONDITION;
623
624 FUNCTION GET_BUSINESS_GROUP_ID RETURN NUMBER IS
625 X0 NUMBER;
626 BEGIN
627 X0 := HR_GENERAL.GET_BUSINESS_GROUP_ID;
628 /*STPROC.INIT('begin :X0 := HR_GENERAL.GET_BUSINESS_GROUP_ID; end;');
629 STPROC.BIND_O(X0);
630 STPROC.EXECUTE;
631 STPROC.RETRIEVE(1
632 ,X0);*/null;
633 RETURN X0;
634 END GET_BUSINESS_GROUP_ID;
635
636 FUNCTION CHK_APPLICATION_ID(P_APPLICATION_ID IN NUMBER) RETURN VARCHAR2 IS
637 X0 VARCHAR2(2000);
638 BEGIN
639 /*STPROC.INIT('begin :X0 := HR_GENERAL.CHK_APPLICATION_ID(:P_APPLICATION_ID); end;');
640 STPROC.BIND_O(X0);
641 STPROC.BIND_I(P_APPLICATION_ID);
642 STPROC.EXECUTE;
643 STPROC.RETRIEVE(1
644 ,X0);*/null;
645 RETURN X0;
646 END CHK_APPLICATION_ID;
647
648 FUNCTION CORE_HR_APP_ID(P_APPLICATION_ID IN NUMBER) RETURN VARCHAR2 IS
649 X0 VARCHAR2(2000);
650 BEGIN
651 /*STPROC.INIT('begin :X0 := HR_GENERAL.CORE_HR_APP_ID(:P_APPLICATION_ID); end;');
652 STPROC.BIND_O(X0);
653 STPROC.BIND_I(P_APPLICATION_ID);
654 STPROC.EXECUTE;
655 STPROC.RETRIEVE(1
656 ,X0);*/null;
657 RETURN X0;
658 END CORE_HR_APP_ID;
659
660 FUNCTION VERTICAL_APP_ID(P_APPLICATION_ID IN NUMBER) RETURN VARCHAR2 IS
661 X0 VARCHAR2(2000);
662 BEGIN
663 /*STPROC.INIT('begin :X0 := HR_GENERAL.VERTICAL_APP_ID(:P_APPLICATION_ID); end;');
664 STPROC.BIND_O(X0);
665 STPROC.BIND_I(P_APPLICATION_ID);
666 STPROC.EXECUTE;
667 STPROC.RETRIEVE(1
668 ,X0);*/null;
669 RETURN X0;
670 END VERTICAL_APP_ID;
671
672 FUNCTION HRMS_OBJECT(P_OBJECT_NAME IN VARCHAR2) RETURN VARCHAR2 IS
673 X0 VARCHAR2(2000);
674 BEGIN
675 /* STPROC.INIT('begin :X0 := HR_GENERAL.HRMS_OBJECT(:P_OBJECT_NAME); end;');
676 STPROC.BIND_O(X0);
677 STPROC.BIND_I(P_OBJECT_NAME);
678 STPROC.EXECUTE;
679 STPROC.RETRIEVE(1
680 ,X0);*/null;
681 RETURN X0;
682 END HRMS_OBJECT;
683
684 FUNCTION GET_APPLICATION_SHORT_NAME(P_APPLICATION_ID IN VARCHAR2) RETURN VARCHAR2 IS
685 X0 VARCHAR2(2000);
686 BEGIN
687 /*STPROC.INIT('begin :X0 := HR_GENERAL.GET_APPLICATION_SHORT_NAME(:P_APPLICATION_ID); end;');
688 STPROC.BIND_O(X0);
689 STPROC.BIND_I(P_APPLICATION_ID);
690 STPROC.EXECUTE;
691 STPROC.RETRIEVE(1
692 ,X0);*/null;
693 RETURN X0;
694 END GET_APPLICATION_SHORT_NAME;
695
696 FUNCTION EFFECTIVE_DATE RETURN DATE IS
697 X0 DATE;
698 BEGIN
699 /*STPROC.INIT('begin :X0 := HR_GENERAL.EFFECTIVE_DATE; end;');
700 STPROC.BIND_O(X0);
701 STPROC.EXECUTE;
702 STPROC.RETRIEVE(1
703 ,X0);*/null;
704 RETURN X0;
705 END EFFECTIVE_DATE;
706
707 FUNCTION START_OF_TIME RETURN DATE IS
708 X0 DATE;
709 BEGIN
710 /*STPROC.INIT('begin :X0 := HR_GENERAL.START_OF_TIME; end;');
711 STPROC.BIND_O(X0);
712 STPROC.EXECUTE;
713 STPROC.RETRIEVE(1
714 ,X0);*/null;
715 RETURN X0;
716 END START_OF_TIME;
717
718 FUNCTION END_OF_TIME RETURN DATE IS
719 X0 DATE;
720 BEGIN
721 /*STPROC.INIT('begin :X0 := HR_GENERAL.END_OF_TIME; end;');
722 STPROC.BIND_O(X0);
723 STPROC.EXECUTE;
724 STPROC.RETRIEVE(1
725 ,X0);*/null;
726 RETURN X0;
727 END END_OF_TIME;
728
729 FUNCTION PAY_VALUE RETURN VARCHAR2 IS
730 X0 VARCHAR2(2000);
731 BEGIN
732 /*STPROC.INIT('begin :X0 := HR_GENERAL.PAY_VALUE; end;');
733 STPROC.BIND_O(X0);
734 STPROC.EXECUTE;
735 STPROC.RETRIEVE(1
736 ,X0);*/null;
737 RETURN X0;
738 END PAY_VALUE;
739
740 FUNCTION MONEY_UNIT RETURN VARCHAR2 IS
741 X0 VARCHAR2(2000);
742 BEGIN
743 /*STPROC.INIT('begin :X0 := HR_GENERAL.MONEY_UNIT; end;');
744 STPROC.BIND_O(X0);
745 STPROC.EXECUTE;
746 STPROC.RETRIEVE(1
747 ,X0);*/null;
748 RETURN X0;
749 END MONEY_UNIT;
750
751 FUNCTION DEFAULT_CURRENCY_CODE(P_LEGISLATION_CODE IN VARCHAR2) RETURN VARCHAR2 IS
752 X0 VARCHAR2(2000);
753 BEGIN
754 /*STPROC.INIT('begin :X0 := HR_GENERAL.DEFAULT_CURRENCY_CODE(:P_LEGISLATION_CODE); end;');
755 STPROC.BIND_O(X0);
756 STPROC.BIND_I(P_LEGISLATION_CODE);
757 STPROC.EXECUTE;
758 STPROC.RETRIEVE(1
759 ,X0);*/null;
760 RETURN X0;
761 END DEFAULT_CURRENCY_CODE;
762
763 FUNCTION DEFAULT_CURRENCY_CODE(P_BUSINESS_GROUP_ID IN NUMBER) RETURN VARCHAR2 IS
764 X0 VARCHAR2(2000);
765 BEGIN
766 /*STPROC.INIT('begin :X0 := HR_GENERAL.DEFAULT_CURRENCY_CODE(:P_BUSINESS_GROUP_ID); end;');
767 STPROC.BIND_O(X0);
768 STPROC.BIND_I(P_BUSINESS_GROUP_ID);
769 STPROC.EXECUTE;
770 STPROC.RETRIEVE(1
771 ,X0);*/null;
772 RETURN X0;
773 END DEFAULT_CURRENCY_CODE;
774
775 FUNCTION LOCATION_VALID(P_LOCATION_ID IN NUMBER
776 ,P_DATE IN DATE
777 ,P_ERROR_IF_INVALID IN BOOLEAN) RETURN BOOLEAN IS
778 X0 BOOLEAN;
779 BEGIN
780 /*STPROC.INIT('declare X0rv BOOLEAN; X0P_ERROR_IF_INVALID BOOLEAN;
781 begin X0P_ERROR_IF_INVALID := sys.diutil.int_to_bool(:P_ERROR_IF_INVALID);
782 X0rv := HR_GENERAL.LOCATION_VALID(:P_LOCATION_ID, :P_DATE, X0P_ERROR_IF_INVALID);
783 :X0 := sys.diutil.bool_to_int(X0rv); end;');
784 STPROC.BIND_I(P_ERROR_IF_INVALID);
785 STPROC.BIND_I(P_LOCATION_ID);
786 STPROC.BIND_I(P_DATE);
787 STPROC.BIND_O(X0);
788 STPROC.EXECUTE;
789 STPROC.RETRIEVE(4
790 ,X0);*/null;
791 RETURN X0;
792 END LOCATION_VALID;
793
794 FUNCTION DECODE_LOOKUP(P_LOOKUP_TYPE IN VARCHAR2
795 ,P_LOOKUP_CODE IN VARCHAR2) RETURN VARCHAR2 IS
796 X0 VARCHAR2(2000);
797 BEGIN
798 /* STPROC.INIT('begin :X0 := HR_GENERAL.DECODE_LOOKUP(:P_LOOKUP_TYPE, :P_LOOKUP_CODE); end;');
799 STPROC.BIND_O(X0);
800 STPROC.BIND_I(P_LOOKUP_TYPE);
801 STPROC.BIND_I(P_LOOKUP_CODE);
802 STPROC.EXECUTE;
803 STPROC.RETRIEVE(1
804 ,X0);*/null;
805 RETURN X0;
806 END DECODE_LOOKUP;
807
808 FUNCTION DECODE_GRADE(P_GRADE_ID IN NUMBER) RETURN VARCHAR2 IS
809 X0 VARCHAR2(2000);
810 BEGIN
811 /*STPROC.INIT('begin :X0 := HR_GENERAL.DECODE_GRADE(:P_GRADE_ID); end;');
812 STPROC.BIND_O(X0);
813 STPROC.BIND_I(P_GRADE_ID);
814 STPROC.EXECUTE;
815 STPROC.RETRIEVE(1
816 ,X0);*/null;
817 RETURN X0;
818 END DECODE_GRADE;
819
820 FUNCTION DECODE_PAYROLL(P_PAYROLL_ID IN NUMBER) RETURN VARCHAR2 IS
821 X0 VARCHAR2(2000);
822 BEGIN
823 /*STPROC.INIT('begin :X0 := HR_GENERAL.DECODE_PAYROLL(:P_PAYROLL_ID); end;');
824 STPROC.BIND_O(X0);
825 STPROC.BIND_I(P_PAYROLL_ID);
826 STPROC.EXECUTE;
827 STPROC.RETRIEVE(1
828 ,X0);*/null;
829 RETURN X0;
830 END DECODE_PAYROLL;
831
832 FUNCTION GET_SALARY(P_PAY_BASIS_ID IN NUMBER
833 ,P_ASSIGNMENT_ID IN NUMBER) RETURN VARCHAR2 IS
834 X0 VARCHAR2(2000);
835 BEGIN
836 /*STPROC.INIT('begin :X0 := HR_GENERAL.GET_SALARY(:P_PAY_BASIS_ID, :P_ASSIGNMENT_ID); end;');
837 STPROC.BIND_O(X0);
838 STPROC.BIND_I(P_PAY_BASIS_ID);
839 STPROC.BIND_I(P_ASSIGNMENT_ID);
840 STPROC.EXECUTE;
841 STPROC.RETRIEVE(1
842 ,X0);*/null;
843 RETURN X0;
844 END GET_SALARY;
845
846 FUNCTION DECODE_JOB(P_JOB_ID IN NUMBER) RETURN VARCHAR2 IS
847 X0 VARCHAR2(2000);
848 BEGIN
849 /*STPROC.INIT('begin :X0 := HR_GENERAL.DECODE_JOB(:P_JOB_ID); end;');
850 STPROC.BIND_O(X0);
851 STPROC.BIND_I(P_JOB_ID);
852 STPROC.EXECUTE;
853 STPROC.RETRIEVE(1
854 ,X0);*/null;
855 RETURN X0;
856 END DECODE_JOB;
857
858 FUNCTION DECODE_POSITION(P_POSITION_ID IN NUMBER) RETURN VARCHAR2 IS
859 X0 VARCHAR2(2000);
860 BEGIN
861 /*STPROC.INIT('begin :X0 := HR_GENERAL.DECODE_POSITION(:P_POSITION_ID); end;');
862 STPROC.BIND_O(X0);
863 STPROC.BIND_I(P_POSITION_ID);
864 STPROC.EXECUTE;
865 STPROC.RETRIEVE(1
866 ,X0);*/null;
867 RETURN X0;
868 END DECODE_POSITION;
869
870 FUNCTION DECODE_LOCATION(P_LOCATION_ID IN NUMBER) RETURN VARCHAR2 IS
871 X0 VARCHAR2(2000);
872 BEGIN
873 /* STPROC.INIT('begin :X0 := HR_GENERAL.DECODE_LOCATION(:P_LOCATION_ID); end;');
874 STPROC.BIND_O(X0);
875 STPROC.BIND_I(P_LOCATION_ID);
876 STPROC.EXECUTE;
877 STPROC.RETRIEVE(1
878 ,X0);*/
879 RETURN X0;
880 END DECODE_LOCATION;
881
882 FUNCTION DECODE_PAY_BASIS(P_PAY_BASIS_ID IN NUMBER) RETURN VARCHAR2 IS
883 X0 VARCHAR2(2000);
884 BEGIN
885 /*STPROC.INIT('begin :X0 := HR_GENERAL.DECODE_PAY_BASIS(:P_PAY_BASIS_ID); end;');
886 STPROC.BIND_O(X0);
887 STPROC.BIND_I(P_PAY_BASIS_ID);
888 STPROC.EXECUTE;
889 STPROC.RETRIEVE(1
890 ,X0);*/null;
891 RETURN X0;
892 END DECODE_PAY_BASIS;
893
894 FUNCTION DECODE_ASS_STATUS_TYPE(P_ASSIGNMENT_STATUS_TYPE_ID IN NUMBER
895 ,P_BUSINESS_GROUP_ID IN NUMBER) RETURN VARCHAR2 IS
896 X0 VARCHAR2(2000);
897 BEGIN
898 /*STPROC.INIT('begin :X0 := HR_GENERAL.DECODE_ASS_STATUS_TYPE(:P_ASSIGNMENT_STATUS_TYPE_ID, :P_BUSINESS_GROUP_ID); end;');
899 STPROC.BIND_O(X0);
900 STPROC.BIND_I(P_ASSIGNMENT_STATUS_TYPE_ID);
901 STPROC.BIND_I(P_BUSINESS_GROUP_ID);
902 STPROC.EXECUTE;
903 STPROC.RETRIEVE(1
904 ,X0);*/null;
905 RETURN X0;
906 END DECODE_ASS_STATUS_TYPE;
907
908 FUNCTION GET_WORK_PHONE(P_PERSON_ID IN NUMBER) RETURN VARCHAR2 IS
909 X0 VARCHAR2(2000);
910 BEGIN
911 /*STPROC.INIT('begin :X0 := HR_GENERAL.GET_WORK_PHONE(:P_PERSON_ID); end;');
912 STPROC.BIND_O(X0);
913 STPROC.BIND_I(P_PERSON_ID);
914 STPROC.EXECUTE;
915 STPROC.RETRIEVE(1
916 ,X0);*/null;
917 RETURN X0;
918 END GET_WORK_PHONE;
919
920 FUNCTION GET_HOME_PHONE(P_PERSON_ID IN NUMBER) RETURN VARCHAR2 IS
921 X0 VARCHAR2(2000);
922 BEGIN
923 /*STPROC.INIT('begin :X0 := HR_GENERAL.GET_HOME_PHONE(:P_PERSON_ID); end;');
924 STPROC.BIND_O(X0);
925 STPROC.BIND_I(P_PERSON_ID);
926 STPROC.EXECUTE;
927 STPROC.RETRIEVE(1
928 ,X0);*/null;
929 RETURN X0;
930 END GET_HOME_PHONE;
931
932 PROCEDURE INIT_FORMS(P_BUSINESS_GROUP_ID IN NUMBER
933 ,P_SHORT_NAME OUT NOCOPY VARCHAR2
934 ,P_BG_NAME OUT NOCOPY VARCHAR2
935 ,P_BG_CURRENCY_CODE OUT NOCOPY VARCHAR2
936 ,P_LEGISLATION_CODE OUT NOCOPY VARCHAR2
937 ,P_SESSION_DATE IN OUT NOCOPY DATE
938 ,P_SES_YESTERDAY OUT NOCOPY DATE
939 ,P_START_OF_TIME OUT NOCOPY DATE
940 ,P_END_OF_TIME OUT NOCOPY DATE
941 ,P_SYS_DATE OUT NOCOPY DATE
942 ,P_ENABLE_HR_TRACE IN BOOLEAN) IS
943 BEGIN
944 /*STPROC.INIT('declare P_ENABLE_HR_TRACE BOOLEAN;
945 begin P_ENABLE_HR_TRACE := sys.diutil.int_to_bool(:P_ENABLE_HR_TRACE);
946 HR_GENERAL.INIT_FORMS(:P_BUSINESS_GROUP_ID, :P_SHORT_NAME, :P_BG_NAME,
947 :P_BG_CURRENCY_CODE, :P_LEGISLATION_CODE, :P_SESSION_DATE, :P_SES_YESTERDAY,
948 :P_START_OF_TIME, :P_END_OF_TIME, :P_SYS_DATE, P_ENABLE_HR_TRACE); end;');
949 STPROC.BIND_I(P_ENABLE_HR_TRACE);
950 STPROC.BIND_I(P_BUSINESS_GROUP_ID);
951 STPROC.BIND_O(P_SHORT_NAME);
952 STPROC.BIND_O(P_BG_NAME);
953 STPROC.BIND_O(P_BG_CURRENCY_CODE);
954 STPROC.BIND_O(P_LEGISLATION_CODE);
955 STPROC.BIND_IO(P_SESSION_DATE);
956 STPROC.BIND_O(P_SES_YESTERDAY);
957 STPROC.BIND_O(P_START_OF_TIME);
958 STPROC.BIND_O(P_END_OF_TIME);
959 STPROC.BIND_O(P_SYS_DATE);
960 STPROC.EXECUTE;
961 STPROC.RETRIEVE(3
962 ,P_SHORT_NAME);
963 STPROC.RETRIEVE(4
964 ,P_BG_NAME);
965 STPROC.RETRIEVE(5
966 ,P_BG_CURRENCY_CODE);
967 STPROC.RETRIEVE(6
968 ,P_LEGISLATION_CODE);
969 STPROC.RETRIEVE(7
970 ,P_SESSION_DATE);
971 STPROC.RETRIEVE(8
972 ,P_SES_YESTERDAY);
973 STPROC.RETRIEVE(9
974 ,P_START_OF_TIME);
975 STPROC.RETRIEVE(10
976 ,P_END_OF_TIME);
977 STPROC.RETRIEVE(11
978 ,P_SYS_DATE);*/null;
979 END INIT_FORMS;
980
981 FUNCTION CHK_GEOCODES_INSTALLED RETURN VARCHAR2 IS
982 X0 VARCHAR2(2000);
983 BEGIN
984 /*STPROC.INIT('begin :X0 := HR_GENERAL.CHK_GEOCODES_INSTALLED; end;');
985 STPROC.BIND_O(X0);
986 STPROC.EXECUTE;
987 STPROC.RETRIEVE(1
988 ,X0);*/null;
989 RETURN X0;
990 END CHK_GEOCODES_INSTALLED;
991
992 FUNCTION GET_BUDGET(P_BUDGET_ID IN NUMBER) RETURN VARCHAR2 IS
993 X0 VARCHAR2(2000);
994 BEGIN
995 /*STPROC.INIT('begin :X0 := HR_REPORTS.GET_BUDGET(:P_BUDGET_ID); end;');
996 STPROC.BIND_O(X0);
997 STPROC.BIND_I(P_BUDGET_ID);
998 STPROC.EXECUTE;
999 STPROC.RETRIEVE(1
1000 ,X0);*/null;
1001 RETURN X0;
1002 END GET_BUDGET;
1003
1004 FUNCTION GET_BUDGET_VERSION(P_BUDGET_ID IN NUMBER
1005 ,P_BUDGET_VERSION_ID IN NUMBER) RETURN VARCHAR2 IS
1006 X0 VARCHAR2(2000);
1007 BEGIN
1008 /*STPROC.INIT('begin :X0 := HR_REPORTS.GET_BUDGET_VERSION(:P_BUDGET_ID, :P_BUDGET_VERSION_ID); end;');
1009 STPROC.BIND_O(X0);
1010 STPROC.BIND_I(P_BUDGET_ID);
1011 STPROC.BIND_I(P_BUDGET_VERSION_ID);
1012 STPROC.EXECUTE;
1013 STPROC.RETRIEVE(1
1014 ,X0);*/null;
1015 RETURN X0;
1016 END GET_BUDGET_VERSION;
1017
1018 PROCEDURE GET_ORGANIZATION(P_ORGANIZATION_ID IN NUMBER
1019 ,P_ORG_NAME OUT NOCOPY VARCHAR2
1020 ,P_ORG_TYPE OUT NOCOPY VARCHAR2) IS
1021 BEGIN
1022 /*STPROC.INIT('begin HR_REPORTS.GET_ORGANIZATION(:P_ORGANIZATION_ID, :P_ORG_NAME, :P_ORG_TYPE); end;');
1023 STPROC.BIND_I(P_ORGANIZATION_ID);
1024 STPROC.BIND_O(P_ORG_NAME);
1025 STPROC.BIND_O(P_ORG_TYPE);
1026 STPROC.EXECUTE;
1027 STPROC.RETRIEVE(2
1028 ,P_ORG_NAME);
1029 STPROC.RETRIEVE(3
1030 ,P_ORG_TYPE);*/null;
1031 END GET_ORGANIZATION;
1032
1033 FUNCTION GET_JOB(P_JOB_ID IN NUMBER) RETURN VARCHAR2 IS
1034 X0 VARCHAR2(2000);
1035 BEGIN
1036 /*STPROC.INIT('begin :X0 := HR_REPORTS.GET_JOB(:P_JOB_ID); end;');
1037 STPROC.BIND_O(X0);
1038 STPROC.BIND_I(P_JOB_ID);
1039 STPROC.EXECUTE;
1040 STPROC.RETRIEVE(1
1041 ,X0);*/null;
1042 RETURN X0;
1043 END GET_JOB;
1044
1045 FUNCTION GET_POSITION(P_POSITION_ID IN NUMBER) RETURN VARCHAR2 IS
1046 X0 VARCHAR2(2000);
1047 BEGIN
1048 /*STPROC.INIT('begin :X0 := HR_REPORTS.GET_POSITION(:P_POSITION_ID); end;');
1049 STPROC.BIND_O(X0);
1050 STPROC.BIND_I(P_POSITION_ID);
1051 STPROC.EXECUTE;
1052 STPROC.RETRIEVE(1
1053 ,X0);*/null;
1054 RETURN X0;
1055 END GET_POSITION;
1056
1057 FUNCTION GET_GRADE(P_GRADE_ID IN NUMBER) RETURN VARCHAR2 IS
1058 X0 VARCHAR2(2000);
1059 BEGIN
1060 /*STPROC.INIT('begin :X0 := HR_REPORTS.GET_GRADE(:P_GRADE_ID); end;');
1061 STPROC.BIND_O(X0);
1062 STPROC.BIND_I(P_GRADE_ID);
1063 STPROC.EXECUTE;
1064 STPROC.RETRIEVE(1
1065 ,X0);*/null;
1066 RETURN X0;
1067 END GET_GRADE;
1068
1069 FUNCTION GET_STATUS(P_BUSINESS_GROUP_ID IN NUMBER
1070 ,P_ASSIGNMENT_STATUS_TYPE_ID IN NUMBER
1071 ,P_LEGISLATION_CODE IN VARCHAR2) RETURN VARCHAR2 IS
1072 X0 VARCHAR2(2000);
1073 BEGIN
1074 /*STPROC.INIT('begin :X0 := HR_REPORTS.GET_STATUS(:P_BUSINESS_GROUP_ID, :P_ASSIGNMENT_STATUS_TYPE_ID, :P_LEGISLATION_CODE); end;');
1075 STPROC.BIND_O(X0);
1076 STPROC.BIND_I(P_BUSINESS_GROUP_ID);
1077 STPROC.BIND_I(P_ASSIGNMENT_STATUS_TYPE_ID);
1078 STPROC.BIND_I(P_LEGISLATION_CODE);
1079 STPROC.EXECUTE;
1080 STPROC.RETRIEVE(1
1081 ,X0);*/null;
1082 RETURN X0;
1083 END GET_STATUS;
1084
1085 FUNCTION GET_ABS_TYPE(P_ABS_ATT_TYPE_ID IN NUMBER) RETURN VARCHAR2 IS
1086 X0 VARCHAR2(2000);
1087 BEGIN
1088 /*STPROC.INIT('begin :X0 := HR_REPORTS.GET_ABS_TYPE(:P_ABS_ATT_TYPE_ID); end;');
1089 STPROC.BIND_O(X0);
1090 STPROC.BIND_I(P_ABS_ATT_TYPE_ID);
1091 STPROC.EXECUTE;
1092 STPROC.RETRIEVE(1
1093 ,X0);*/null;
1094 RETURN X0;
1095 END GET_ABS_TYPE;
1096
1097 PROCEDURE GET_TIME_PERIOD(P_TIME_PERIOD_ID IN NUMBER
1098 ,P_PERIOD_NAME OUT NOCOPY VARCHAR2
1099 ,P_START_DATE OUT NOCOPY DATE
1100 ,P_END_DATE OUT NOCOPY DATE) IS
1101 BEGIN
1102 /*STPROC.INIT('begin HR_REPORTS.GET_TIME_PERIOD(:P_TIME_PERIOD_ID, :P_PERIOD_NAME, :P_START_DATE, :P_END_DATE); end;');
1103 STPROC.BIND_I(P_TIME_PERIOD_ID);
1104 STPROC.BIND_O(P_PERIOD_NAME);
1105 STPROC.BIND_O(P_START_DATE);
1106 STPROC.BIND_O(P_END_DATE);
1107 STPROC.EXECUTE;
1108 STPROC.RETRIEVE(2
1109 ,P_PERIOD_NAME);
1110 STPROC.RETRIEVE(3
1111 ,P_START_DATE);
1112 STPROC.RETRIEVE(4
1113 ,P_END_DATE);*/null;
1114 END GET_TIME_PERIOD;
1115
1116 FUNCTION GET_BUSINESS_GROUP(P_BUSINESS_GROUP_ID IN NUMBER) RETURN VARCHAR2 IS
1117 X0 VARCHAR2(2000);
1118 BEGIN
1119 X0:= HR_REPORTS.GET_BUSINESS_GROUP(P_BUSINESS_GROUP_ID);
1120 /*STPROC.INIT('begin :X0 := HR_REPORTS.GET_BUSINESS_GROUP(:P_BUSINESS_GROUP_ID); end;');
1121 STPROC.BIND_O(X0);
1122 STPROC.BIND_I(P_BUSINESS_GROUP_ID);
1123 STPROC.EXECUTE;
1124 STPROC.RETRIEVE(1
1125 ,X0);*/null;
1126 RETURN X0;
1127 END GET_BUSINESS_GROUP;
1128
1129 FUNCTION COUNT_ORG_SUBORDINATES(P_ORG_STRUCTURE_VERSION_ID IN NUMBER
1130 ,P_PARENT_ORGANIZATION_ID IN NUMBER) RETURN NUMBER IS
1131 X0 NUMBER;
1132 BEGIN
1133 /*STPROC.INIT('begin :X0 := HR_REPORTS.COUNT_ORG_SUBORDINATES(:P_ORG_STRUCTURE_VERSION_ID, :P_PARENT_ORGANIZATION_ID); end;');
1134 STPROC.BIND_O(X0);
1135 STPROC.BIND_I(P_ORG_STRUCTURE_VERSION_ID);
1136 STPROC.BIND_I(P_PARENT_ORGANIZATION_ID);
1137 STPROC.EXECUTE;
1138 STPROC.RETRIEVE(1
1139 ,X0);*/null;
1140 RETURN X0;
1141 END COUNT_ORG_SUBORDINATES;
1142
1143 FUNCTION COUNT_POS_SUBORDINATES(P_POS_STRUCTURE_VERSION_ID IN NUMBER
1144 ,P_PARENT_POSITION_ID IN NUMBER) RETURN NUMBER IS
1145 X0 NUMBER;
1146 BEGIN
1147 /*STPROC.INIT('begin :X0 := HR_REPORTS.COUNT_POS_SUBORDINATES(:P_POS_STRUCTURE_VERSION_ID, :P_PARENT_POSITION_ID); end;');
1148 STPROC.BIND_O(X0);
1149 STPROC.BIND_I(P_POS_STRUCTURE_VERSION_ID);
1150 STPROC.BIND_I(P_PARENT_POSITION_ID);
1151 STPROC.EXECUTE;
1152 STPROC.RETRIEVE(1
1153 ,X0);*/null;
1154 RETURN X0;
1155 END COUNT_POS_SUBORDINATES;
1156
1157 PROCEDURE GET_ORGANIZATION_HIERARCHY(P_ORGANIZATION_STRUCTURE_ID IN NUMBER
1158 ,P_ORG_STRUCTURE_VERSION_ID IN NUMBER
1159 ,P_ORG_STRUCTURE_NAME OUT NOCOPY VARCHAR2
1160 ,P_ORG_VERSION OUT NOCOPY NUMBER
1161 ,P_VERSION_START_DATE OUT NOCOPY DATE
1162 ,P_VERSION_END_DATE OUT NOCOPY DATE) IS
1163 BEGIN
1164 /*STPROC.INIT('begin HR_REPORTS.GET_ORGANIZATION_HIERARCHY(:P_ORGANIZATION_STRUCTURE_ID,
1165 :P_ORG_STRUCTURE_VERSION_ID, :P_ORG_STRUCTURE_NAME, :P_ORG_VERSION, :P_VERSION_START_DATE,
1166 :P_VERSION_END_DATE); end;');
1167 STPROC.BIND_I(P_ORGANIZATION_STRUCTURE_ID);
1168 STPROC.BIND_I(P_ORG_STRUCTURE_VERSION_ID);
1169 STPROC.BIND_O(P_ORG_STRUCTURE_NAME);
1170 STPROC.BIND_O(P_ORG_VERSION);
1171 STPROC.BIND_O(P_VERSION_START_DATE);
1172 STPROC.BIND_O(P_VERSION_END_DATE);
1173 STPROC.EXECUTE;
1174 STPROC.RETRIEVE(3
1175 ,P_ORG_STRUCTURE_NAME);
1176 STPROC.RETRIEVE(4
1177 ,P_ORG_VERSION);
1178 STPROC.RETRIEVE(5
1179 ,P_VERSION_START_DATE);
1180 STPROC.RETRIEVE(6
1181 ,P_VERSION_END_DATE);*/null;
1182 END GET_ORGANIZATION_HIERARCHY;
1183
1184 PROCEDURE GET_POSITION_HIERARCHY(P_POSITION_STRUCTURE_ID IN NUMBER
1185 ,P_POS_STRUCTURE_VERSION_ID IN NUMBER
1186 ,P_POS_STRUCTURE_NAME OUT NOCOPY VARCHAR2
1187 ,P_POS_VERSION OUT NOCOPY NUMBER
1188 ,P_VERSION_START_DATE OUT NOCOPY DATE
1189 ,P_VERSION_END_DATE OUT NOCOPY DATE) IS
1190 BEGIN
1191 /*STPROC.INIT('begin HR_REPORTS.GET_POSITION_HIERARCHY(:P_POSITION_STRUCTURE_ID, :P_POS_STRUCTURE_VERSION_ID, :P_POS_STRUCTURE_NAME,
1192 :P_POS_VERSION, :P_VERSION_START_DATE, :P_VERSION_END_DATE); end;');
1193 STPROC.BIND_I(P_POSITION_STRUCTURE_ID);
1194 STPROC.BIND_I(P_POS_STRUCTURE_VERSION_ID);
1195 STPROC.BIND_O(P_POS_STRUCTURE_NAME);
1196 STPROC.BIND_O(P_POS_VERSION);
1197 STPROC.BIND_O(P_VERSION_START_DATE);
1198 STPROC.BIND_O(P_VERSION_END_DATE);
1199 STPROC.EXECUTE;
1200 STPROC.RETRIEVE(3
1201 ,P_POS_STRUCTURE_NAME);
1202 STPROC.RETRIEVE(4
1203 ,P_POS_VERSION);
1204 STPROC.RETRIEVE(5
1205 ,P_VERSION_START_DATE);
1206 STPROC.RETRIEVE(6
1207 ,P_VERSION_END_DATE);*/null;
1208 END GET_POSITION_HIERARCHY;
1209
1210 FUNCTION GET_LOOKUP_MEANING(P_LOOKUP_TYPE IN VARCHAR2
1211 ,P_LOOKUP_CODE IN VARCHAR2) RETURN VARCHAR2 IS
1212 X0 VARCHAR2(2000);
1213 BEGIN
1214 /*STPROC.INIT('begin :X0 := HR_REPORTS.GET_LOOKUP_MEANING(:P_LOOKUP_TYPE, :P_LOOKUP_CODE); end;');
1215 STPROC.BIND_O(X0);
1216 STPROC.BIND_I(P_LOOKUP_TYPE);
1217 STPROC.BIND_I(P_LOOKUP_CODE);
1218 STPROC.EXECUTE;
1219 STPROC.RETRIEVE(1
1220 ,X0);*/null;
1221 RETURN X0;
1222 END GET_LOOKUP_MEANING;
1223
1224 FUNCTION PERSON_MATCHING_SKILLS(P_PERSON_ID IN NUMBER
1225 ,P_JOB_POSITION_ID IN NUMBER
1226 ,P_JOB_POSITION_TYPE IN VARCHAR2
1227 ,P_MATCHING_LEVEL IN VARCHAR2
1228 ,P_NO_OF_ESSENTIAL IN NUMBER
1229 ,P_NO_OF_DESIRABLE IN NUMBER) RETURN BOOLEAN IS
1230 X0 BOOLEAN;
1231 BEGIN
1232 /*STPROC.INIT('declare X0rv BOOLEAN; begin X0rv := HR_REPORTS.PERSON_MATCHING_SKILLS(:P_PERSON_ID, :P_JOB_POSITION_ID,
1233 :P_JOB_POSITION_TYPE, :P_MATCHING_LEVEL, :P_NO_OF_ESSENTIAL, :P_NO_OF_DESIRABLE); :X0 := sys.diutil.bool_to_int(X0rv); end;');
1234 STPROC.BIND_I(P_PERSON_ID);
1235 STPROC.BIND_I(P_JOB_POSITION_ID);
1236 STPROC.BIND_I(P_JOB_POSITION_TYPE);
1237 STPROC.BIND_I(P_MATCHING_LEVEL);
1238 STPROC.BIND_I(P_NO_OF_ESSENTIAL);
1239 STPROC.BIND_I(P_NO_OF_DESIRABLE);
1240 STPROC.BIND_O(X0);
1241 STPROC.EXECUTE;
1242 STPROC.RETRIEVE(7
1243 ,X0);*/null;
1244 RETURN X0;
1245 END PERSON_MATCHING_SKILLS;
1246
1247 FUNCTION GET_PAYROLL_NAME(P_SESSION_DATE IN DATE
1248 ,P_PAYROLL_ID IN NUMBER) RETURN VARCHAR2 IS
1249 X0 VARCHAR2(2000);
1250 BEGIN
1251 /*STPROC.INIT('begin :X0 := HR_REPORTS.GET_PAYROLL_NAME(:P_SESSION_DATE, :P_PAYROLL_ID); end;');
1252 STPROC.BIND_O(X0);
1253 STPROC.BIND_I(P_SESSION_DATE);
1254 STPROC.BIND_I(P_PAYROLL_ID);
1255 STPROC.EXECUTE;
1256 STPROC.RETRIEVE(1
1257 ,X0);*/null;
1258 RETURN X0;
1259 END GET_PAYROLL_NAME;
1260
1261 FUNCTION GET_ELEMENT_NAME(P_SESSION_DATE IN DATE
1262 ,P_ELEMENT_TYPE_ID IN NUMBER) RETURN VARCHAR2 IS
1263 X0 VARCHAR2(2000);
1264 BEGIN
1265 /*STPROC.INIT('begin :X0 := HR_REPORTS.GET_ELEMENT_NAME(:P_SESSION_DATE, :P_ELEMENT_TYPE_ID); end;');
1266 STPROC.BIND_O(X0);
1267 STPROC.BIND_I(P_SESSION_DATE);
1268 STPROC.BIND_I(P_ELEMENT_TYPE_ID);
1269 STPROC.EXECUTE;
1270 STPROC.RETRIEVE(1
1271 ,X0);*/null;
1272 RETURN X0;
1273 END GET_ELEMENT_NAME;
1274
1275 PROCEDURE GEN_PARTIAL_MATCHING_LEXICAL(P_CONCATENATED_SEGMENTS IN VARCHAR2
1276 ,P_ID_FLEX_NUM IN NUMBER
1277 ,P_MATCHING_LEXICAL IN OUT NOCOPY VARCHAR2) IS
1278 BEGIN
1279 /*STPROC.INIT('begin HR_REPORTS.GEN_PARTIAL_MATCHING_LEXICAL(:P_CONCATENATED_SEGMENTS, :P_ID_FLEX_NUM, :P_MATCHING_LEXICAL); end;');
1280 STPROC.BIND_I(P_CONCATENATED_SEGMENTS);
1281 STPROC.BIND_I(P_ID_FLEX_NUM);
1282 STPROC.BIND_IO(P_MATCHING_LEXICAL);
1283 STPROC.EXECUTE;
1284 STPROC.RETRIEVE(3
1285 ,P_MATCHING_LEXICAL);*/null;
1286 END GEN_PARTIAL_MATCHING_LEXICAL;
1287
1288 PROCEDURE GET_ATTRIBUTES(P_CONCATENATED_SEGMENTS IN VARCHAR2
1289 ,P_NAME IN VARCHAR2
1290 ,P_SEGMENTS_USED OUT NOCOPY NUMBER
1291 ,P_VALUE1 OUT NOCOPY VARCHAR2
1292 ,P_VALUE2 OUT NOCOPY VARCHAR2
1293 ,P_VALUE3 OUT NOCOPY VARCHAR2
1294 ,P_VALUE4 OUT NOCOPY VARCHAR2
1295 ,P_VALUE5 OUT NOCOPY VARCHAR2
1296 ,P_VALUE6 OUT NOCOPY VARCHAR2
1297 ,P_VALUE7 OUT NOCOPY VARCHAR2
1298 ,P_VALUE8 OUT NOCOPY VARCHAR2
1299 ,P_VALUE9 OUT NOCOPY VARCHAR2
1300 ,P_VALUE10 OUT NOCOPY VARCHAR2
1301 ,P_VALUE11 OUT NOCOPY VARCHAR2
1302 ,P_VALUE12 OUT NOCOPY VARCHAR2
1303 ,P_VALUE13 OUT NOCOPY VARCHAR2
1304 ,P_VALUE14 OUT NOCOPY VARCHAR2
1305 ,P_VALUE15 OUT NOCOPY VARCHAR2
1306 ,P_VALUE16 OUT NOCOPY VARCHAR2
1307 ,P_VALUE17 OUT NOCOPY VARCHAR2
1308 ,P_VALUE18 OUT NOCOPY VARCHAR2
1309 ,P_VALUE19 OUT NOCOPY VARCHAR2
1310 ,P_VALUE20 OUT NOCOPY VARCHAR2
1311 ,P_VALUE21 OUT NOCOPY VARCHAR2
1312 ,P_VALUE22 OUT NOCOPY VARCHAR2
1313 ,P_VALUE23 OUT NOCOPY VARCHAR2
1314 ,P_VALUE24 OUT NOCOPY VARCHAR2
1315 ,P_VALUE25 OUT NOCOPY VARCHAR2
1316 ,P_VALUE26 OUT NOCOPY VARCHAR2
1317 ,P_VALUE27 OUT NOCOPY VARCHAR2
1318 ,P_VALUE28 OUT NOCOPY VARCHAR2
1319 ,P_VALUE29 OUT NOCOPY VARCHAR2
1320 ,P_VALUE30 OUT NOCOPY VARCHAR2) IS
1321 BEGIN
1322 /*STPROC.INIT('begin HR_REPORTS.GET_ATTRIBUTES(:P_CONCATENATED_SEGMENTS, :P_NAME, :P_SEGMENTS_USED, :P_VALUE1, :P_VALUE2,
1323 :P_VALUE3, :P_VALUE4, :P_VALUE5, :P_VALUE6, :P_VALUE7, :P_VALUE8, :P_VALUE9, :P_VALUE10, :P_VALUE11, :P_VALUE12, :P_VALUE13,
1324 :P_VALUE14, :P_VALUE15, :P_VALUE16, :P_VALUE17, :P_VALUE18, :P_VALUE19, :P_VALUE20, :P_VALUE21, :P_VALUE22, :P_VALUE23, :P_VALUE24,
1325 :P_VALUE25, :P_VALUE26, :P_VALUE27, :P_VALUE28, :P_VALUE29, :P_VALUE30); end;');
1326 STPROC.BIND_I(P_CONCATENATED_SEGMENTS);
1327 STPROC.BIND_I(P_NAME);
1328 STPROC.BIND_O(P_SEGMENTS_USED);
1329 STPROC.BIND_O(P_VALUE1);
1330 STPROC.BIND_O(P_VALUE2);
1331 STPROC.BIND_O(P_VALUE3);
1332 STPROC.BIND_O(P_VALUE4);
1333 STPROC.BIND_O(P_VALUE5);
1334 STPROC.BIND_O(P_VALUE6);
1335 STPROC.BIND_O(P_VALUE7);
1336 STPROC.BIND_O(P_VALUE8);
1337 STPROC.BIND_O(P_VALUE9);
1338 STPROC.BIND_O(P_VALUE10);
1339 STPROC.BIND_O(P_VALUE11);
1340 STPROC.BIND_O(P_VALUE12);
1341 STPROC.BIND_O(P_VALUE13);
1342 STPROC.BIND_O(P_VALUE14);
1343 STPROC.BIND_O(P_VALUE15);
1344 STPROC.BIND_O(P_VALUE16);
1345 STPROC.BIND_O(P_VALUE17);
1346 STPROC.BIND_O(P_VALUE18);
1347 STPROC.BIND_O(P_VALUE19);
1348 STPROC.BIND_O(P_VALUE20);
1349 STPROC.BIND_O(P_VALUE21);
1350 STPROC.BIND_O(P_VALUE22);
1351 STPROC.BIND_O(P_VALUE23);
1352 STPROC.BIND_O(P_VALUE24);
1353 STPROC.BIND_O(P_VALUE25);
1354 STPROC.BIND_O(P_VALUE26);
1355 STPROC.BIND_O(P_VALUE27);
1356 STPROC.BIND_O(P_VALUE28);
1357 STPROC.BIND_O(P_VALUE29);
1358 STPROC.BIND_O(P_VALUE30);
1359 STPROC.EXECUTE;
1360 STPROC.RETRIEVE(3
1361 ,P_SEGMENTS_USED);
1362 STPROC.RETRIEVE(4
1363 ,P_VALUE1);
1364 STPROC.RETRIEVE(5
1365 ,P_VALUE2);
1366 STPROC.RETRIEVE(6
1367 ,P_VALUE3);
1368 STPROC.RETRIEVE(7
1369 ,P_VALUE4);
1370 STPROC.RETRIEVE(8
1371 ,P_VALUE5);
1372 STPROC.RETRIEVE(9
1373 ,P_VALUE6);
1374 STPROC.RETRIEVE(10
1375 ,P_VALUE7);
1376 STPROC.RETRIEVE(11
1377 ,P_VALUE8);
1378 STPROC.RETRIEVE(12
1379 ,P_VALUE9);
1380 STPROC.RETRIEVE(13
1381 ,P_VALUE10);
1382 STPROC.RETRIEVE(14
1383 ,P_VALUE11);
1384 STPROC.RETRIEVE(15
1385 ,P_VALUE12);
1386 STPROC.RETRIEVE(16
1387 ,P_VALUE13);
1388 STPROC.RETRIEVE(17
1389 ,P_VALUE14);
1390 STPROC.RETRIEVE(18
1391 ,P_VALUE15);
1392 STPROC.RETRIEVE(19
1393 ,P_VALUE16);
1394 STPROC.RETRIEVE(20
1395 ,P_VALUE17);
1396 STPROC.RETRIEVE(21
1397 ,P_VALUE18);
1398 STPROC.RETRIEVE(22
1399 ,P_VALUE19);
1400 STPROC.RETRIEVE(23
1401 ,P_VALUE20);
1402 STPROC.RETRIEVE(24
1403 ,P_VALUE21);
1404 STPROC.RETRIEVE(25
1405 ,P_VALUE22);
1406 STPROC.RETRIEVE(26
1407 ,P_VALUE23);
1408 STPROC.RETRIEVE(27
1409 ,P_VALUE24);
1410 STPROC.RETRIEVE(28
1411 ,P_VALUE25);
1412 STPROC.RETRIEVE(29
1413 ,P_VALUE26);
1414 STPROC.RETRIEVE(30
1415 ,P_VALUE27);
1416 STPROC.RETRIEVE(31
1417 ,P_VALUE28);
1418 STPROC.RETRIEVE(32
1419 ,P_VALUE29);
1420 STPROC.RETRIEVE(33
1421 ,P_VALUE30);*/null;
1422 END GET_ATTRIBUTES;
1423
1424 PROCEDURE GET_SEGMENTS(P_CONCATENATED_SEGMENTS IN VARCHAR2
1425 ,P_ID_FLEX_NUM IN NUMBER
1426 ,P_SEGMENTS_USED OUT NOCOPY NUMBER
1427 ,P_VALUE1 OUT NOCOPY VARCHAR2
1428 ,P_VALUE2 OUT NOCOPY VARCHAR2
1429 ,P_VALUE3 OUT NOCOPY VARCHAR2
1430 ,P_VALUE4 OUT NOCOPY VARCHAR2
1431 ,P_VALUE5 OUT NOCOPY VARCHAR2
1432 ,P_VALUE6 OUT NOCOPY VARCHAR2
1433 ,P_VALUE7 OUT NOCOPY VARCHAR2
1434 ,P_VALUE8 OUT NOCOPY VARCHAR2
1435 ,P_VALUE9 OUT NOCOPY VARCHAR2
1436 ,P_VALUE10 OUT NOCOPY VARCHAR2
1437 ,P_VALUE11 OUT NOCOPY VARCHAR2
1438 ,P_VALUE12 OUT NOCOPY VARCHAR2
1439 ,P_VALUE13 OUT NOCOPY VARCHAR2
1440 ,P_VALUE14 OUT NOCOPY VARCHAR2
1441 ,P_VALUE15 OUT NOCOPY VARCHAR2
1442 ,P_VALUE16 OUT NOCOPY VARCHAR2
1443 ,P_VALUE17 OUT NOCOPY VARCHAR2
1444 ,P_VALUE18 OUT NOCOPY VARCHAR2
1445 ,P_VALUE19 OUT NOCOPY VARCHAR2
1446 ,P_VALUE20 OUT NOCOPY VARCHAR2
1447 ,P_VALUE21 OUT NOCOPY VARCHAR2
1448 ,P_VALUE22 OUT NOCOPY VARCHAR2
1449 ,P_VALUE23 OUT NOCOPY VARCHAR2
1450 ,P_VALUE24 OUT NOCOPY VARCHAR2
1451 ,P_VALUE25 OUT NOCOPY VARCHAR2
1452 ,P_VALUE26 OUT NOCOPY VARCHAR2
1453 ,P_VALUE27 OUT NOCOPY VARCHAR2
1454 ,P_VALUE28 OUT NOCOPY VARCHAR2
1455 ,P_VALUE29 OUT NOCOPY VARCHAR2
1456 ,P_VALUE30 OUT NOCOPY VARCHAR2) IS
1457 BEGIN
1458 /*STPROC.INIT('begin HR_REPORTS.GET_SEGMENTS(:P_CONCATENATED_SEGMENTS, :P_ID_FLEX_NUM, :P_SEGMENTS_USED, :P_VALUE1, :P_VALUE2,
1459 :P_VALUE3, :P_VALUE4, :P_VALUE5, :P_VALUE6, :P_VALUE7, :P_VALUE8, :P_VALUE9, :P_VALUE10, :P_VALUE11, :P_VALUE12, :P_VALUE13,
1460 :P_VALUE14, :P_VALUE15, :P_VALUE16, :P_VALUE17, :P_VALUE18, :P_VALUE19, :P_VALUE20, :P_VALUE21, :P_VALUE22, :P_VALUE23, :P_VALUE24,
1461 :P_VALUE25, :P_VALUE26, :P_VALUE27, :P_VALUE28, :P_VALUE29, :P_VALUE30); end;');
1462 STPROC.BIND_I(P_CONCATENATED_SEGMENTS);
1463 STPROC.BIND_I(P_ID_FLEX_NUM);
1464 STPROC.BIND_O(P_SEGMENTS_USED);
1465 STPROC.BIND_O(P_VALUE1);
1466 STPROC.BIND_O(P_VALUE2);
1467 STPROC.BIND_O(P_VALUE3);
1468 STPROC.BIND_O(P_VALUE4);
1469 STPROC.BIND_O(P_VALUE5);
1470 STPROC.BIND_O(P_VALUE6);
1471 STPROC.BIND_O(P_VALUE7);
1472 STPROC.BIND_O(P_VALUE8);
1473 STPROC.BIND_O(P_VALUE9);
1474 STPROC.BIND_O(P_VALUE10);
1475 STPROC.BIND_O(P_VALUE11);
1476 STPROC.BIND_O(P_VALUE12);
1477 STPROC.BIND_O(P_VALUE13);
1478 STPROC.BIND_O(P_VALUE14);
1479 STPROC.BIND_O(P_VALUE15);
1480 STPROC.BIND_O(P_VALUE16);
1481 STPROC.BIND_O(P_VALUE17);
1482 STPROC.BIND_O(P_VALUE18);
1483 STPROC.BIND_O(P_VALUE19);
1484 STPROC.BIND_O(P_VALUE20);
1485 STPROC.BIND_O(P_VALUE21);
1486 STPROC.BIND_O(P_VALUE22);
1487 STPROC.BIND_O(P_VALUE23);
1488 STPROC.BIND_O(P_VALUE24);
1489 STPROC.BIND_O(P_VALUE25);
1490 STPROC.BIND_O(P_VALUE26);
1491 STPROC.BIND_O(P_VALUE27);
1492 STPROC.BIND_O(P_VALUE28);
1493 STPROC.BIND_O(P_VALUE29);
1494 STPROC.BIND_O(P_VALUE30);
1495 STPROC.EXECUTE;
1496 STPROC.RETRIEVE(3
1497 ,P_SEGMENTS_USED);
1498 STPROC.RETRIEVE(4
1499 ,P_VALUE1);
1500 STPROC.RETRIEVE(5
1501 ,P_VALUE2);
1502 STPROC.RETRIEVE(6
1503 ,P_VALUE3);
1504 STPROC.RETRIEVE(7
1505 ,P_VALUE4);
1506 STPROC.RETRIEVE(8
1507 ,P_VALUE5);
1508 STPROC.RETRIEVE(9
1509 ,P_VALUE6);
1510 STPROC.RETRIEVE(10
1511 ,P_VALUE7);
1512 STPROC.RETRIEVE(11
1513 ,P_VALUE8);
1514 STPROC.RETRIEVE(12
1515 ,P_VALUE9);
1516 STPROC.RETRIEVE(13
1517 ,P_VALUE10);
1518 STPROC.RETRIEVE(14
1519 ,P_VALUE11);
1520 STPROC.RETRIEVE(15
1521 ,P_VALUE12);
1522 STPROC.RETRIEVE(16
1523 ,P_VALUE13);
1524 STPROC.RETRIEVE(17
1525 ,P_VALUE14);
1526 STPROC.RETRIEVE(18
1527 ,P_VALUE15);
1528 STPROC.RETRIEVE(19
1529 ,P_VALUE16);
1530 STPROC.RETRIEVE(20
1531 ,P_VALUE17);
1532 STPROC.RETRIEVE(21
1533 ,P_VALUE18);
1534 STPROC.RETRIEVE(22
1535 ,P_VALUE19);
1536 STPROC.RETRIEVE(23
1537 ,P_VALUE20);
1538 STPROC.RETRIEVE(24
1539 ,P_VALUE21);
1540 STPROC.RETRIEVE(25
1541 ,P_VALUE22);
1542 STPROC.RETRIEVE(26
1543 ,P_VALUE23);
1544 STPROC.RETRIEVE(27
1545 ,P_VALUE24);
1546 STPROC.RETRIEVE(28
1547 ,P_VALUE25);
1548 STPROC.RETRIEVE(29
1549 ,P_VALUE26);
1550 STPROC.RETRIEVE(30
1551 ,P_VALUE27);
1552 STPROC.RETRIEVE(31
1553 ,P_VALUE28);
1554 STPROC.RETRIEVE(32
1555 ,P_VALUE29);
1556 STPROC.RETRIEVE(33
1557 ,P_VALUE30);*/null;
1558 END GET_SEGMENTS;
1559
1560 PROCEDURE GET_DESC_FLEX(P_APPL_SHORT_NAME IN VARCHAR2
1561 ,P_DESC_FLEX_NAME IN VARCHAR2
1562 ,P_TABLE_ALIAS IN VARCHAR2
1563 ,P_TITLE OUT NOCOPY VARCHAR2
1564 ,P_LABEL_EXPR OUT NOCOPY VARCHAR2
1565 ,P_COLUMN_EXPR OUT NOCOPY VARCHAR2) IS
1566 BEGIN
1567 /*STPROC.INIT('begin HR_REPORTS.GET_DESC_FLEX(:P_APPL_SHORT_NAME, :P_DESC_FLEX_NAME, :P_TABLE_ALIAS, :P_TITLE, :P_LABEL_EXPR,
1568 :P_COLUMN_EXPR); end;');
1569 STPROC.BIND_I(P_APPL_SHORT_NAME);
1570 STPROC.BIND_I(P_DESC_FLEX_NAME);
1571 STPROC.BIND_I(P_TABLE_ALIAS);
1572 STPROC.BIND_O(P_TITLE);
1573 STPROC.BIND_O(P_LABEL_EXPR);
1574 STPROC.BIND_O(P_COLUMN_EXPR);
1575 STPROC.EXECUTE;
1576 STPROC.RETRIEVE(4
1577 ,P_TITLE);
1578 STPROC.RETRIEVE(5
1579 ,P_LABEL_EXPR);
1580 STPROC.RETRIEVE(6
1581 ,P_COLUMN_EXPR);*/null;
1582 END GET_DESC_FLEX;
1583
1584 PROCEDURE GET_DESC_FLEX_CONTEXT(P_APPL_SHORT_NAME IN VARCHAR2
1585 ,P_DESC_FLEX_NAME IN VARCHAR2
1586 ,P_TABLE_ALIAS IN VARCHAR2
1587 ,P_TITLE OUT NOCOPY VARCHAR2
1588 ,P_LABEL_EXPR OUT NOCOPY VARCHAR2
1589 ,P_COLUMN_EXPR OUT NOCOPY VARCHAR2) IS
1590 BEGIN
1591 /*STPROC.INIT('begin HR_REPORTS.GET_DESC_FLEX_CONTEXT(:P_APPL_SHORT_NAME, :P_DESC_FLEX_NAME, :P_TABLE_ALIAS, :P_TITLE, :P_LABEL_EXPR,
1592 :P_COLUMN_EXPR); end;');
1593 STPROC.BIND_I(P_APPL_SHORT_NAME);
1594 STPROC.BIND_I(P_DESC_FLEX_NAME);
1595 STPROC.BIND_I(P_TABLE_ALIAS);
1596 STPROC.BIND_O(P_TITLE);
1597 STPROC.BIND_O(P_LABEL_EXPR);
1598 STPROC.BIND_O(P_COLUMN_EXPR);
1599 STPROC.EXECUTE;
1600 STPROC.RETRIEVE(4
1601 ,P_TITLE);
1602 STPROC.RETRIEVE(5
1603 ,P_LABEL_EXPR);
1604 STPROC.RETRIEVE(6
1605 ,P_COLUMN_EXPR);*/null;
1606 END GET_DESC_FLEX_CONTEXT;
1607
1608 PROCEDURE GET_DVLPR_DESC_FLEX(P_APPL_SHORT_NAME IN VARCHAR2
1609 ,P_DESC_FLEX_NAME IN VARCHAR2
1610 ,P_DESC_FLEX_CONTEXT IN VARCHAR2
1611 ,P_TABLE_ALIAS IN VARCHAR2
1612 ,P_TITLE OUT NOCOPY VARCHAR2
1613 ,P_LABEL_EXPR OUT NOCOPY VARCHAR2
1614 ,P_COLUMN_EXPR OUT NOCOPY VARCHAR2) IS
1615 BEGIN
1616 /*STPROC.INIT('begin HR_REPORTS.GET_DVLPR_DESC_FLEX(:P_APPL_SHORT_NAME, :P_DESC_FLEX_NAME, :P_DESC_FLEX_CONTEXT, :P_TABLE_ALIAS,
1617 :P_TITLE, :P_LABEL_EXPR, :P_COLUMN_EXPR); end;');
1618 STPROC.BIND_I(P_APPL_SHORT_NAME);
1619 STPROC.BIND_I(P_DESC_FLEX_NAME);
1620 STPROC.BIND_I(P_DESC_FLEX_CONTEXT);
1621 STPROC.BIND_I(P_TABLE_ALIAS);
1622 STPROC.BIND_O(P_TITLE);
1623 STPROC.BIND_O(P_LABEL_EXPR);
1624 STPROC.BIND_O(P_COLUMN_EXPR);
1625 STPROC.EXECUTE;
1626 STPROC.RETRIEVE(5
1627 ,P_TITLE);
1628 STPROC.RETRIEVE(6
1629 ,P_LABEL_EXPR);
1630 STPROC.RETRIEVE(7
1631 ,P_COLUMN_EXPR);*/null;
1632 END GET_DVLPR_DESC_FLEX;
1633
1634 FUNCTION GET_PERSON_NAME(P_SESSION_DATE IN DATE
1635 ,P_PERSON_ID IN NUMBER) RETURN VARCHAR2 IS
1636 X0 VARCHAR2(2000);
1637 BEGIN
1638 /*STPROC.INIT('begin :X0 := HR_REPORTS.GET_PERSON_NAME(:P_SESSION_DATE, :P_PERSON_ID); end;');
1639 STPROC.BIND_O(X0);
1640 STPROC.BIND_I(P_SESSION_DATE);
1641 STPROC.BIND_I(P_PERSON_ID);
1642 STPROC.EXECUTE;
1643 STPROC.RETRIEVE(1
1644 ,X0);*/null;
1645 RETURN X0;
1646 END GET_PERSON_NAME;
1647
1648 FUNCTION GET_SALARY(P_BUSINESS_GROUP_ID IN NUMBER
1649 ,P_ASSIGNMENT_ID IN NUMBER
1650 ,P_REPORT_DATE IN DATE) RETURN NUMBER IS
1651 X0 NUMBER;
1652 BEGIN
1653 /*STPROC.INIT('begin :X0 := HR_US_REPORTS.GET_SALARY(:P_BUSINESS_GROUP_ID, :P_ASSIGNMENT_ID, :P_REPORT_DATE); end;');
1654 STPROC.BIND_O(X0);
1655 STPROC.BIND_I(P_BUSINESS_GROUP_ID);
1656 STPROC.BIND_I(P_ASSIGNMENT_ID);
1657 STPROC.BIND_I(P_REPORT_DATE);
1658 STPROC.EXECUTE;
1659 STPROC.RETRIEVE(1
1660 ,X0);*/null;
1661 RETURN X0;
1662 END GET_SALARY;
1663
1664 PROCEDURE GET_NEW_HIRE_CONTACT(P_PERSON_ID IN NUMBER
1665 ,P_BUSINESS_GROUP_ID IN NUMBER
1666 ,P_REPORT_DATE IN DATE
1667 ,P_CONTACT_NAME OUT NOCOPY VARCHAR2
1668 ,P_CONTACT_TITLE OUT NOCOPY VARCHAR2
1669 ,P_CONTACT_PHONE OUT NOCOPY VARCHAR2) IS
1670 BEGIN
1671 /*STPROC.INIT('begin HR_US_REPORTS.GET_NEW_HIRE_CONTACT(:P_PERSON_ID, :P_BUSINESS_GROUP_ID, :P_REPORT_DATE, :P_CONTACT_NAME,
1672 :P_CONTACT_TITLE, :P_CONTACT_PHONE); end;');
1673 STPROC.BIND_I(P_PERSON_ID);
1674 STPROC.BIND_I(P_BUSINESS_GROUP_ID);
1675 STPROC.BIND_I(P_REPORT_DATE);
1676 STPROC.BIND_O(P_CONTACT_NAME);
1677 STPROC.BIND_O(P_CONTACT_TITLE);
1678 STPROC.BIND_O(P_CONTACT_PHONE);
1679 STPROC.EXECUTE;
1680 STPROC.RETRIEVE(4
1681 ,P_CONTACT_NAME);
1682 STPROC.RETRIEVE(5
1683 ,P_CONTACT_TITLE);
1684 STPROC.RETRIEVE(6
1685 ,P_CONTACT_PHONE);*/null;
1686 END GET_NEW_HIRE_CONTACT;
1687
1688 PROCEDURE GET_ADDRESS(P_LOCATION_ID IN NUMBER
1689 ,P_ADDRESS OUT NOCOPY VARCHAR2) IS
1690 BEGIN
1691 /*STPROC.INIT('begin HR_US_REPORTS.GET_ADDRESS(:P_LOCATION_ID, :P_ADDRESS); end;');
1692 STPROC.BIND_I(P_LOCATION_ID);
1693 STPROC.BIND_O(P_ADDRESS);
1694 STPROC.EXECUTE;
1695 STPROC.RETRIEVE(2
1696 ,P_ADDRESS);*/null;
1697 END GET_ADDRESS;
1698
1699 PROCEDURE GET_EMPLOYEE_ADDRESS(P_PERSON_ID IN NUMBER
1700 ,P_ADDRESS OUT NOCOPY VARCHAR2) IS
1701 BEGIN
1702 /*STPROC.INIT('begin HR_US_REPORTS.GET_EMPLOYEE_ADDRESS(:P_PERSON_ID, :P_ADDRESS); end;');
1703 STPROC.BIND_I(P_PERSON_ID);
1704 STPROC.BIND_O(P_ADDRESS);
1705 STPROC.EXECUTE;
1706 STPROC.RETRIEVE(2
1707 ,P_ADDRESS);*/null;
1708 END GET_EMPLOYEE_ADDRESS;
1709
1710 PROCEDURE GET_COUNTY_ADDRESS(P_LOCATION_ID IN NUMBER
1711 ,P_ADDRESS OUT NOCOPY VARCHAR2) IS
1712 BEGIN
1713 /*STPROC.INIT('begin HR_US_REPORTS.GET_COUNTY_ADDRESS(:P_LOCATION_ID, :P_ADDRESS); end;');
1714 STPROC.BIND_I(P_LOCATION_ID);
1715 STPROC.BIND_O(P_ADDRESS);
1716 STPROC.EXECUTE;
1717 STPROC.RETRIEVE(2
1718 ,P_ADDRESS);*/null;
1719 END GET_COUNTY_ADDRESS;
1720
1721 PROCEDURE GET_ACTIVITY(P_ESTABLISHMENT_ID IN NUMBER
1722 ,P_ACTIVITY OUT NOCOPY VARCHAR2) IS
1723 BEGIN
1724 /*STPROC.INIT('begin HR_US_REPORTS.GET_ACTIVITY(:P_ESTABLISHMENT_ID, :P_ACTIVITY); end;');
1725 STPROC.BIND_I(P_ESTABLISHMENT_ID);
1726 STPROC.BIND_O(P_ACTIVITY);
1727 STPROC.EXECUTE;
1728 STPROC.RETRIEVE(2
1729 ,P_ACTIVITY);*/null;
1730 END GET_ACTIVITY;
1731
1732 FUNCTION GET_CONSOLIDATION_SET(P_CONSOLIDATION_SET_ID IN NUMBER) RETURN VARCHAR2 IS
1733 X0 VARCHAR2(2000);
1734 BEGIN
1735 /*STPROC.INIT('begin :X0 := HR_US_REPORTS.GET_CONSOLIDATION_SET(:P_CONSOLIDATION_SET_ID); end;');
1736 STPROC.BIND_O(X0);
1737 STPROC.BIND_I(P_CONSOLIDATION_SET_ID);
1738 STPROC.EXECUTE;
1739 STPROC.RETRIEVE(1
1740 ,X0);*/null;
1741 RETURN X0;
1742 END GET_CONSOLIDATION_SET;
1743
1744 FUNCTION GET_PAYMENT_TYPE_NAME(P_PAYMENT_TYPE_ID IN NUMBER) RETURN VARCHAR2 IS
1745 X0 VARCHAR2(2000);
1746 BEGIN
1747 /*STPROC.INIT('begin :X0 := HR_US_REPORTS.GET_PAYMENT_TYPE_NAME(:P_PAYMENT_TYPE_ID); end;');
1748 STPROC.BIND_O(X0);
1749 STPROC.BIND_I(P_PAYMENT_TYPE_ID);
1750 STPROC.EXECUTE;
1751 STPROC.RETRIEVE(1
1752 ,X0);*/null;
1753 RETURN X0;
1754 END GET_PAYMENT_TYPE_NAME;
1755
1756 FUNCTION GET_ELEMENT_TYPE_NAME(P_ELEMENT_TYPE_ID IN NUMBER) RETURN VARCHAR2 IS
1757 X0 VARCHAR2(2000);
1758 BEGIN
1759 /*STPROC.INIT('begin :X0 := HR_US_REPORTS.GET_ELEMENT_TYPE_NAME(:P_ELEMENT_TYPE_ID); end;');
1760 STPROC.BIND_O(X0);
1761 STPROC.BIND_I(P_ELEMENT_TYPE_ID);
1762 STPROC.EXECUTE;
1763 STPROC.RETRIEVE(1
1764 ,X0);*/null;
1765 RETURN X0;
1766 END GET_ELEMENT_TYPE_NAME;
1767
1768 FUNCTION GET_TAX_UNIT(P_TAX_UNIT_ID IN NUMBER) RETURN VARCHAR2 IS
1769 X0 VARCHAR2(2000);
1770 BEGIN
1771 /*STPROC.INIT('begin :X0 := HR_US_REPORTS.GET_TAX_UNIT(:P_TAX_UNIT_ID); end;');
1772 STPROC.BIND_O(X0);
1773 STPROC.BIND_I(P_TAX_UNIT_ID);
1774 STPROC.EXECUTE;
1775 STPROC.RETRIEVE(1
1776 ,X0);*/null;
1777 RETURN X0;
1778 END GET_TAX_UNIT;
1779
1780 FUNCTION GET_PERSON_NAME(P_PERSON_ID IN NUMBER) RETURN VARCHAR2 IS
1781 X0 VARCHAR2(2000);
1782 BEGIN
1783 /*STPROC.INIT('begin :X0 := HR_US_REPORTS.GET_PERSON_NAME(:P_PERSON_ID); end;');
1784 STPROC.BIND_O(X0);
1785 STPROC.BIND_I(P_PERSON_ID);
1786 STPROC.EXECUTE;
1787 STPROC.RETRIEVE(1
1788 ,X0);*/null;
1789 RETURN X0;
1790 END GET_PERSON_NAME;
1791
1792 FUNCTION GET_PAYROLL_ACTION(P_PAYROLL_ACTION_ID IN NUMBER) RETURN VARCHAR2 IS
1793 X0 VARCHAR2(2000);
1794 BEGIN
1795 /*STPROC.INIT('begin :X0 := HR_US_REPORTS.GET_PAYROLL_ACTION(:P_PAYROLL_ACTION_ID); end;');
1796 STPROC.BIND_O(X0);
1797 STPROC.BIND_I(P_PAYROLL_ACTION_ID);
1798 STPROC.EXECUTE;
1799 STPROC.RETRIEVE(1
1800 ,X0);*/null;
1801 RETURN X0;
1802 END GET_PAYROLL_ACTION;
1803
1804 FUNCTION GET_LEGISLATION_CODE(P_BUSINESS_GROUP_ID IN NUMBER) RETURN VARCHAR2 IS
1805 X0 VARCHAR2(2000);
1806 BEGIN
1807 /*STPROC.INIT('begin :X0 := HR_US_REPORTS.GET_LEGISLATION_CODE(:P_BUSINESS_GROUP_ID); end;');
1808 STPROC.BIND_O(X0);
1809 STPROC.BIND_I(P_BUSINESS_GROUP_ID);
1810 STPROC.EXECUTE;
1811 STPROC.RETRIEVE(1
1812 ,X0);*/null;
1813 RETURN X0;
1814 END GET_LEGISLATION_CODE;
1815
1816 FUNCTION GET_DEFINED_BALANCE_ID(P_BALANCE_NAME IN VARCHAR2
1817 ,P_DIMENSION_SUFFIX IN VARCHAR2
1818 ,P_BUSINESS_GROUP_ID IN NUMBER) RETURN NUMBER IS
1819 X0 NUMBER;
1820 BEGIN
1821 /*STPROC.INIT('begin :X0 := HR_US_REPORTS.GET_DEFINED_BALANCE_ID(:P_BALANCE_NAME, :P_DIMENSION_SUFFIX, :P_BUSINESS_GROUP_ID); end;');
1822 STPROC.BIND_O(X0);
1823 STPROC.BIND_I(P_BALANCE_NAME);
1824 STPROC.BIND_I(P_DIMENSION_SUFFIX);
1825 STPROC.BIND_I(P_BUSINESS_GROUP_ID);
1826 STPROC.EXECUTE;
1827 STPROC.RETRIEVE(1
1828 ,X0);*/null;
1829 RETURN X0;
1830 END GET_DEFINED_BALANCE_ID;
1831
1832 FUNCTION GET_STARTUP_DEFINED_BALANCE(P_REPORTING_NAME IN VARCHAR2
1833 ,P_DIMENSION_SUFFIX IN VARCHAR2) RETURN NUMBER IS
1834 X0 NUMBER;
1835 BEGIN
1836 /*STPROC.INIT('begin :X0 := HR_US_REPORTS.GET_STARTUP_DEFINED_BALANCE(:P_REPORTING_NAME, :P_DIMENSION_SUFFIX); end;');
1837 STPROC.BIND_O(X0);
1838 STPROC.BIND_I(P_REPORTING_NAME);
1839 STPROC.BIND_I(P_DIMENSION_SUFFIX);
1840 STPROC.EXECUTE;
1841 STPROC.RETRIEVE(1
1842 ,X0);*/null;
1843 RETURN X0;
1844 END GET_STARTUP_DEFINED_BALANCE;
1845
1846 FUNCTION GET_DEFINED_BALANCE_BY_TYPE(P_BOX_NUM IN VARCHAR2
1847 ,P_DIMENSION_SUFFIX IN VARCHAR2) RETURN NUMBER IS
1848 X0 NUMBER;
1849 BEGIN
1850 /*STPROC.INIT('begin :X0 := HR_US_REPORTS.GET_DEFINED_BALANCE_BY_TYPE(:P_BOX_NUM, :P_DIMENSION_SUFFIX); end;');
1851 STPROC.BIND_O(X0);
1852 STPROC.BIND_I(P_BOX_NUM);
1853 STPROC.BIND_I(P_DIMENSION_SUFFIX);
1854 STPROC.EXECUTE;
1855 STPROC.RETRIEVE(1
1856 ,X0);*/null;
1857 RETURN X0;
1858 END GET_DEFINED_BALANCE_BY_TYPE;
1859
1860 FUNCTION GET_BEN_CLASS_NAME(P_SESSION_DATE IN DATE
1861 ,P_BENEFIT_CLASSIFICATION_ID IN NUMBER) RETURN VARCHAR2 IS
1862 X0 VARCHAR2(2000);
1863 BEGIN
1864 /*STPROC.INIT('begin :X0 := HR_US_REPORTS.GET_BEN_CLASS_NAME(:P_SESSION_DATE, :P_BENEFIT_CLASSIFICATION_ID); end;');
1865 STPROC.BIND_O(X0);
1866 STPROC.BIND_I(P_SESSION_DATE);
1867 STPROC.BIND_I(P_BENEFIT_CLASSIFICATION_ID);
1868 STPROC.EXECUTE;
1869 STPROC.RETRIEVE(1
1870 ,X0);*/null;
1871 RETURN X0;
1872 END GET_BEN_CLASS_NAME;
1873
1874 FUNCTION GET_COBRA_QUALIFYING_EVENT(P_QUALIFYING_EVENT IN VARCHAR2) RETURN VARCHAR2 IS
1875 X0 VARCHAR2(2000);
1876 BEGIN
1877 /*STPROC.INIT('begin :X0 := HR_US_REPORTS.GET_COBRA_QUALIFYING_EVENT(:P_QUALIFYING_EVENT); end;');
1878 STPROC.BIND_O(X0);
1879 STPROC.BIND_I(P_QUALIFYING_EVENT);
1880 STPROC.EXECUTE;
1881 STPROC.RETRIEVE(1
1882 ,X0);*/null;
1883 RETURN X0;
1884 END GET_COBRA_QUALIFYING_EVENT;
1885
1886 FUNCTION GET_COBRA_STATUS(P_COBRA_STATUS IN VARCHAR2) RETURN VARCHAR2 IS
1887 X0 VARCHAR2(2000);
1888 BEGIN
1889 /*STPROC.INIT('begin :X0 := HR_US_REPORTS.GET_COBRA_STATUS(:P_COBRA_STATUS); end;');
1890 STPROC.BIND_O(X0);
1891 STPROC.BIND_I(P_COBRA_STATUS);
1892 STPROC.EXECUTE;
1893 STPROC.RETRIEVE(1
1894 ,X0);*/null;
1895 RETURN X0;
1896 END GET_COBRA_STATUS;
1897
1898 FUNCTION GET_EST_TAX_UNIT(P_STARTING_ORG_ID IN NUMBER
1899 ,P_ORG_STRUCTURE_VERSION_ID IN NUMBER) RETURN NUMBER IS
1900 X0 NUMBER;
1901 BEGIN
1902 /*STPROC.INIT('begin :X0 := HR_US_REPORTS.GET_EST_TAX_UNIT(:P_STARTING_ORG_ID, :P_ORG_STRUCTURE_VERSION_ID); end;');
1903 STPROC.BIND_O(X0);
1904 STPROC.BIND_I(P_STARTING_ORG_ID);
1905 STPROC.BIND_I(P_ORG_STRUCTURE_VERSION_ID);
1906 STPROC.EXECUTE;
1907 STPROC.RETRIEVE(1
1908 ,X0);*/null;
1909 RETURN X0;
1910 END GET_EST_TAX_UNIT;
1911
1912 FUNCTION GET_ORG_HIERARCHY_NAME(P_ORG_STRUCTURE_VERSION_ID IN NUMBER) RETURN VARCHAR2 IS
1913 X0 VARCHAR2(2000);
1914 BEGIN
1915 /*STPROC.INIT('begin :X0 := HR_US_REPORTS.GET_ORG_HIERARCHY_NAME(:P_ORG_STRUCTURE_VERSION_ID); end;');
1916 STPROC.BIND_O(X0);
1917 STPROC.BIND_I(P_ORG_STRUCTURE_VERSION_ID);
1918 STPROC.EXECUTE;
1919 STPROC.RETRIEVE(1
1920 ,X0);*/null;
1921 RETURN X0;
1922 END GET_ORG_HIERARCHY_NAME;
1923
1924 FUNCTION GET_STATE_NAME(P_STATE_CODE IN VARCHAR2) RETURN VARCHAR2 IS
1925 X0 VARCHAR2(2000);
1926 BEGIN
1927 /*STPROC.INIT('begin :X0 := HR_US_REPORTS.GET_STATE_NAME(:P_STATE_CODE); end;');
1928 STPROC.BIND_O(X0);
1929 STPROC.BIND_I(P_STATE_CODE);
1930 STPROC.EXECUTE;
1931 STPROC.RETRIEVE(1
1932 ,X0);*/null;
1933 RETURN X0;
1934 END GET_STATE_NAME;
1935
1936 FUNCTION GET_ORG_NAME(P_ORGANIZATION_ID IN NUMBER
1937 ,P_BUSINESS_GROUP_ID IN NUMBER) RETURN VARCHAR2 IS
1938 X0 VARCHAR2(2000);
1939 BEGIN
1940 /*STPROC.INIT('begin :X0 := HR_US_REPORTS.GET_ORG_NAME(:P_ORGANIZATION_ID, :P_BUSINESS_GROUP_ID); end;');
1941 STPROC.BIND_O(X0);
1942 STPROC.BIND_I(P_ORGANIZATION_ID);
1943 STPROC.BIND_I(P_BUSINESS_GROUP_ID);
1944 STPROC.EXECUTE;
1945 STPROC.RETRIEVE(1
1946 ,X0);*/null;
1947 RETURN X0;
1948 END GET_ORG_NAME;
1949
1950 FUNCTION GET_CAREER_PATH_NAME(P_CAREER_PATH_ID IN NUMBER
1951 ,P_BUSINESS_GROUP_ID IN NUMBER) RETURN VARCHAR2 IS
1952 X0 VARCHAR2(2000);
1953 BEGIN
1954 /*STPROC.INIT('begin :X0 := HR_US_REPORTS.GET_CAREER_PATH_NAME(:P_CAREER_PATH_ID, :P_BUSINESS_GROUP_ID); end;');
1955 STPROC.BIND_O(X0);
1956 STPROC.BIND_I(P_CAREER_PATH_ID);
1957 STPROC.BIND_I(P_BUSINESS_GROUP_ID);
1958 STPROC.EXECUTE;
1959 STPROC.RETRIEVE(1
1960 ,X0);*/null;
1961 RETURN X0;
1962 END GET_CAREER_PATH_NAME;
1963
1964 FUNCTION GET_AAP_ORG_ID(P_AAP_NAME IN VARCHAR2
1965 ,P_BUSINESS_GROUP_ID IN NUMBER) RETURN NUMBER IS
1966 X0 NUMBER;
1967 BEGIN
1968 /*STPROC.INIT('begin :X0 := HR_US_REPORTS.GET_AAP_ORG_ID(:P_AAP_NAME, :P_BUSINESS_GROUP_ID); end;');
1969 STPROC.BIND_O(X0);
1970 STPROC.BIND_I(P_AAP_NAME);
1971 STPROC.BIND_I(P_BUSINESS_GROUP_ID);
1972 STPROC.EXECUTE;
1973 STPROC.RETRIEVE(1
1974 ,X0);*/null;
1975 RETURN X0;
1976 END GET_AAP_ORG_ID;
1977
1978 PROCEDURE GET_ADDRESS_31(P_LOCATION_ID IN NUMBER
1979 ,P_ADDRESS OUT NOCOPY VARCHAR2) IS
1980 BEGIN
1981 /*STPROC.INIT('begin HR_US_REPORTS.GET_ADDRESS_31(:P_LOCATION_ID, :P_ADDRESS); end;');
1982 STPROC.BIND_I(P_LOCATION_ID);
1983 STPROC.BIND_O(P_ADDRESS);
1984 STPROC.EXECUTE;
1985 STPROC.RETRIEVE(2
1986 ,P_ADDRESS);*/null;
1987 END GET_ADDRESS_31;
1988
1989 FUNCTION GET_LOCATION_CODE(P_LOCATION_ID IN NUMBER) RETURN VARCHAR2 IS
1990 X0 VARCHAR2(2000);
1991 BEGIN
1992 /*STPROC.INIT('begin :X0 := HR_US_REPORTS.GET_LOCATION_CODE(:P_LOCATION_ID); end;');
1993 STPROC.BIND_O(X0);
1994 STPROC.BIND_I(P_LOCATION_ID);
1995 STPROC.EXECUTE;
1996 STPROC.RETRIEVE(1
1997 ,X0);*/null;
1998 RETURN X0;
1999 END GET_LOCATION_CODE;
2000
2001 PROCEDURE GET_ADDRESS_3LINES(P_PERSON_ID IN NUMBER
2002 ,P_EFFECTIVE_DATE IN DATE
2003 ,P_ADDR_LINE1 OUT NOCOPY VARCHAR2
2004 ,P_ADDR_LINE2 OUT NOCOPY VARCHAR2
2005 ,P_CITY_STATE_ZIP OUT NOCOPY VARCHAR2) IS
2006 BEGIN
2007 /*STPROC.INIT('begin HR_US_REPORTS.GET_ADDRESS_3LINES(:P_PERSON_ID, :P_EFFECTIVE_DATE, :P_ADDR_LINE1, :P_ADDR_LINE2,
2008 :P_CITY_STATE_ZIP); end;');
2009 STPROC.BIND_I(P_PERSON_ID);
2010 STPROC.BIND_I(P_EFFECTIVE_DATE);
2011 STPROC.BIND_O(P_ADDR_LINE1);
2012 STPROC.BIND_O(P_ADDR_LINE2);
2013 STPROC.BIND_O(P_CITY_STATE_ZIP);
2014 STPROC.EXECUTE;
2015 STPROC.RETRIEVE(3
2016 ,P_ADDR_LINE1);
2017 STPROC.RETRIEVE(4
2018 ,P_ADDR_LINE2);
2019 STPROC.RETRIEVE(5
2020 ,P_CITY_STATE_ZIP);*/null;
2021 END GET_ADDRESS_3LINES;
2022
2023
2024 END PER_PERUSEOC_XMLP_PKG;