[Home] [Help]
PACKAGE BODY: APPS.PQH_PQHEEO5_XMLP_PKG
Source
1 PACKAGE BODY PQH_PQHEEO5_XMLP_PKG AS
2 /* $Header: PQHEEO5B.pls 120.4 2008/04/23 07:39:35 amakrish noship $ */
3 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
4 L_QUERY_TEXT VARCHAR2(2000);
5 L_FR VARCHAR2(2000);
6 L_FT VARCHAR2(2000);
7 L_PR VARCHAR2(2000);
8 L_PT VARCHAR2(2000);
9 BEGIN
10 --HR_STANDARD.EVENT('BEFORE REPORT');
11 PQH_EMPLOYMENT_CATEGORY.FETCH_EMPL_CATEGORIES(P_BUSINESS_GROUP_ID
12 ,L_FR
13 ,L_FT
14 ,L_PR
15 ,L_PT);
16 CP_FR := L_FR;
17 CP_FT := L_FT;
18 CP_PR := L_PR;
19 CP_PT := L_PT;
20 CP_REPORT_DATE := TO_DATE(P_REPORT_DAY_MONTH || '-' || P_REPORT_YEAR
21 ,'DD-MM-YYYY');
22 LP_REPORT_DATE := to_char(CP_REPORT_DATE,'DD-MON-YYYY');
23 RETURN TRUE;
24 END BEFOREREPORT;
25
26 FUNCTION CF_TOTAL_TITLEFORMULA0005(EMPLOYMENT_CATEGORY IN VARCHAR2) RETURN CHAR IS
27 L_TOTAL_TITLE VARCHAR2(200) := 'TOTAL OTHERS';
28 L_EMP_CATEGORY VARCHAR2(1) := SUBSTR(EMPLOYMENT_CATEGORY
29 ,4
30 ,1);
31 BEGIN
32 IF L_EMP_CATEGORY = 'F' THEN
33 L_TOTAL_TITLE := '19.TOTAL';
34 ELSIF L_EMP_CATEGORY = 'P' THEN
35 L_TOTAL_TITLE := '22.TOTAL';
36 ELSIF L_EMP_CATEGORY = 'N' THEN
37 L_TOTAL_TITLE := '28.TOTAL';
38 END IF;
39 --L_TOTAL_TITLE := EMPLOYMENT_CATEGORY || ' - ' || L_EMP_CATEGORY;
40
41 RETURN L_TOTAL_TITLE;
42 END CF_TOTAL_TITLEFORMULA0005;
43
44 FUNCTION CF_NO_OF_ANNEXESFORMULA RETURN NUMBER IS
45 L_NO_OF_ANNEXES NUMBER;
46 L_NO_OF_SCHOOLS NUMBER;
47 BEGIN
48 SELECT
49 COUNT(1)
50 INTO
51 L_NO_OF_ANNEXES
52 FROM
53 ( SELECT
54 ASS.LOCATION_ID
55 FROM
56 PER_ALL_ASSIGNMENTS_F ASS,
57 HR_ORGANIZATION_UNITS_V HOU,
58 PER_ALL_PEOPLE_F PEO
59 WHERE ASS.ORGANIZATION_ID = HOU.ORGANIZATION_ID
60 AND ASS.PERSON_ID = PEO.PERSON_ID
61 AND CP_REPORT_DATE between ASS.EFFECTIVE_START_DATE
62 AND ASS.EFFECTIVE_END_DATE
63 AND CP_REPORT_DATE between PEO.EFFECTIVE_START_DATE
64 AND PEO.EFFECTIVE_END_DATE
65 AND HOU.BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID
66 UNION
67 SELECT
68 LOCATION_ID
69 FROM
70 HR_ALL_ORGANIZATION_UNITS
71 WHERE BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID
72 AND NVL(DATE_TO
73 ,CP_REPORT_DATE + 1) >= CP_REPORT_DATE );
74 SELECT
75 COUNT(1)
76 INTO
77 L_NO_OF_SCHOOLS
78 FROM
79 HR_ORGANIZATION_UNITS_V HOU
80 WHERE HOU.BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID;
81 CP_NO_OF_SCHOOLS := L_NO_OF_SCHOOLS;
82 RETURN (L_NO_OF_ANNEXES);
83 END CF_NO_OF_ANNEXESFORMULA;
84
85 FUNCTION BEFOREPFORM RETURN BOOLEAN IS
86 BEGIN
87 RETURN (TRUE);
88 END BEFOREPFORM;
89
90 FUNCTION AFTERPFORM RETURN BOOLEAN IS
91 BEGIN
92 RETURN (TRUE);
93 END AFTERPFORM;
94
95 FUNCTION AFTERREPORT RETURN BOOLEAN IS
96 BEGIN
97 --HR_STANDARD.EVENT('AFTER REPORT');
98 RETURN (TRUE);
99 END AFTERREPORT;
100
101 FUNCTION CP_REPORT_DATE_P RETURN DATE IS
102 BEGIN
103 RETURN CP_REPORT_DATE;
104 END CP_REPORT_DATE_P;
105
106 FUNCTION CP_NO_OF_SCHOOLS_P RETURN NUMBER IS
107 BEGIN
108 RETURN CP_NO_OF_SCHOOLS;
109 END CP_NO_OF_SCHOOLS_P;
110
111 FUNCTION CP_FR_P RETURN VARCHAR2 IS
112 BEGIN
113 RETURN CP_FR;
114 END CP_FR_P;
115
116 FUNCTION CP_FT_P RETURN VARCHAR2 IS
117 BEGIN
118 RETURN CP_FT;
119 END CP_FT_P;
120
121 FUNCTION CP_PR_P RETURN VARCHAR2 IS
122 BEGIN
123 RETURN CP_PR;
124 END CP_PR_P;
125
126 FUNCTION CP_PT_P RETURN VARCHAR2 IS
127 BEGIN
128 RETURN CP_PT;
129 END CP_PT_P;
130 /*
131 PROCEDURE ASSERT_CONDITION(P_CONDITION IN BOOLEAN) IS
132 BEGIN
133 STPROC.INIT('declare P_CONDITION BOOLEAN;
134 begin P_CONDITION := sys.diutil.int_to_bool(:P_CONDITION);
135 HR_GENERAL.ASSERT_CONDITION(P_CONDITION); end;');
136 STPROC.BIND_I(P_CONDITION);
137 STPROC.EXECUTE;
138 END ASSERT_CONDITION;
139
140 FUNCTION GET_BUSINESS_GROUP_ID RETURN NUMBER IS
141 X0 NUMBER;
142 BEGIN
143 STPROC.INIT('begin :X0 := HR_GENERAL.GET_BUSINESS_GROUP_ID; end;');
144 STPROC.BIND_O(X0);
145 STPROC.EXECUTE;
146 STPROC.RETRIEVE(1
147 ,X0);
148 RETURN X0;
149 END GET_BUSINESS_GROUP_ID;
150
151 FUNCTION CHK_APPLICATION_ID(P_APPLICATION_ID IN NUMBER) RETURN VARCHAR2 IS
152 X0 VARCHAR2(2000);
153 BEGIN
154 STPROC.INIT('begin :X0 := HR_GENERAL.CHK_APPLICATION_ID(:P_APPLICATION_ID); end;');
155 STPROC.BIND_O(X0);
156 STPROC.BIND_I(P_APPLICATION_ID);
157 STPROC.EXECUTE;
158 STPROC.RETRIEVE(1
159 ,X0);
160 RETURN X0;
161 END CHK_APPLICATION_ID;
162
163 FUNCTION CORE_HR_APP_ID(P_APPLICATION_ID IN NUMBER) RETURN VARCHAR2 IS
164 X0 VARCHAR2(2000);
165 BEGIN
166 STPROC.INIT('begin :X0 := HR_GENERAL.CORE_HR_APP_ID(:P_APPLICATION_ID); end;');
167 STPROC.BIND_O(X0);
168 STPROC.BIND_I(P_APPLICATION_ID);
169 STPROC.EXECUTE;
170 STPROC.RETRIEVE(1
171 ,X0);
172 RETURN X0;
173 END CORE_HR_APP_ID;
174
175 FUNCTION VERTICAL_APP_ID(P_APPLICATION_ID IN NUMBER) RETURN VARCHAR2 IS
176 X0 VARCHAR2(2000);
177 BEGIN
178 STPROC.INIT('begin :X0 := HR_GENERAL.VERTICAL_APP_ID(:P_APPLICATION_ID); end;');
179 STPROC.BIND_O(X0);
180 STPROC.BIND_I(P_APPLICATION_ID);
181 STPROC.EXECUTE;
182 STPROC.RETRIEVE(1
183 ,X0);
184 RETURN X0;
185 END VERTICAL_APP_ID;
186
187 FUNCTION HRMS_OBJECT(P_OBJECT_NAME IN VARCHAR2) RETURN VARCHAR2 IS
188 X0 VARCHAR2(2000);
189 BEGIN
190 STPROC.INIT('begin :X0 := HR_GENERAL.HRMS_OBJECT(:P_OBJECT_NAME); end;');
191 STPROC.BIND_O(X0);
192 STPROC.BIND_I(P_OBJECT_NAME);
193 STPROC.EXECUTE;
194 STPROC.RETRIEVE(1
195 ,X0);
196 RETURN X0;
197 END HRMS_OBJECT;
198
199 FUNCTION GET_APPLICATION_SHORT_NAME(P_APPLICATION_ID IN VARCHAR2) RETURN VARCHAR2 IS
200 X0 VARCHAR2(2000);
201 BEGIN
202 STPROC.INIT('begin :X0 := HR_GENERAL.GET_APPLICATION_SHORT_NAME(:P_APPLICATION_ID); end;');
203 STPROC.BIND_O(X0);
204 STPROC.BIND_I(P_APPLICATION_ID);
205 STPROC.EXECUTE;
206 STPROC.RETRIEVE(1
207 ,X0);
208 RETURN X0;
209 END GET_APPLICATION_SHORT_NAME;
210
211 FUNCTION EFFECTIVE_DATE RETURN DATE IS
212 X0 DATE;
213 BEGIN
214 STPROC.INIT('begin :X0 := HR_GENERAL.EFFECTIVE_DATE; end;');
215 STPROC.BIND_O(X0);
216 STPROC.EXECUTE;
217 STPROC.RETRIEVE(1
218 ,X0);
219 RETURN X0;
220 END EFFECTIVE_DATE;
221
222 FUNCTION START_OF_TIME RETURN DATE IS
223 X0 DATE;
224 BEGIN
225 STPROC.INIT('begin :X0 := HR_GENERAL.START_OF_TIME; end;');
226 STPROC.BIND_O(X0);
227 STPROC.EXECUTE;
228 STPROC.RETRIEVE(1
229 ,X0);
230 RETURN X0;
231 END START_OF_TIME;
232
233 FUNCTION END_OF_TIME RETURN DATE IS
234 X0 DATE;
235 BEGIN
236 STPROC.INIT('begin :X0 := HR_GENERAL.END_OF_TIME; end;');
237 STPROC.BIND_O(X0);
238 STPROC.EXECUTE;
239 STPROC.RETRIEVE(1
240 ,X0);
241 RETURN X0;
242 END END_OF_TIME;
243
244 FUNCTION PAY_VALUE RETURN VARCHAR2 IS
245 X0 VARCHAR2(2000);
246 BEGIN
247 STPROC.INIT('begin :X0 := HR_GENERAL.PAY_VALUE; end;');
248 STPROC.BIND_O(X0);
249 STPROC.EXECUTE;
250 STPROC.RETRIEVE(1
251 ,X0);
252 RETURN X0;
253 END PAY_VALUE;
254
255 FUNCTION MONEY_UNIT RETURN VARCHAR2 IS
256 X0 VARCHAR2(2000);
257 BEGIN
258 STPROC.INIT('begin :X0 := HR_GENERAL.MONEY_UNIT; end;');
259 STPROC.BIND_O(X0);
260 STPROC.EXECUTE;
261 STPROC.RETRIEVE(1
262 ,X0);
263 RETURN X0;
264 END MONEY_UNIT;
265
266 FUNCTION DEFAULT_CURRENCY_CODE(P_LEGISLATION_CODE IN VARCHAR2) RETURN VARCHAR2 IS
267 X0 VARCHAR2(2000);
268 BEGIN
269 STPROC.INIT('begin :X0 := HR_GENERAL.DEFAULT_CURRENCY_CODE(:P_LEGISLATION_CODE); end;');
270 STPROC.BIND_O(X0);
271 STPROC.BIND_I(P_LEGISLATION_CODE);
272 STPROC.EXECUTE;
273 STPROC.RETRIEVE(1
274 ,X0);
275 RETURN X0;
276 END DEFAULT_CURRENCY_CODE;
277
278 FUNCTION DEFAULT_CURRENCY_CODE(P_BUSINESS_GROUP_ID IN NUMBER) RETURN VARCHAR2 IS
279 X0 VARCHAR2(2000);
280 BEGIN
281 STPROC.INIT('begin :X0 := HR_GENERAL.DEFAULT_CURRENCY_CODE(:P_BUSINESS_GROUP_ID); end;');
282 STPROC.BIND_O(X0);
283 STPROC.BIND_I(P_BUSINESS_GROUP_ID);
284 STPROC.EXECUTE;
285 STPROC.RETRIEVE(1
286 ,X0);
287 RETURN X0;
288 END DEFAULT_CURRENCY_CODE;
289
290 FUNCTION LOCATION_VALID(P_LOCATION_ID IN NUMBER
291 ,P_DATE IN DATE
292 ,P_ERROR_IF_INVALID IN BOOLEAN) RETURN BOOLEAN IS
293 X0 BOOLEAN;
294 BEGIN
295 STPROC.INIT('declare X0rv BOOLEAN; X0P_ERROR_IF_INVALID BOOLEAN; begin X0P_ERROR_IF_INVALID := sys.diutil.int_to_bool(:P_ERROR_IF_INVALID);
296 X0rv := HR_GENERAL.LOCATION_VALID(:P_LOCATION_ID, :P_DATE, X0P_ERROR_IF_INVALID); :X0 := sys.diutil.bool_to_int(X0rv); end;');
297 STPROC.BIND_I(P_ERROR_IF_INVALID);
298 STPROC.BIND_I(P_LOCATION_ID);
299 STPROC.BIND_I(P_DATE);
300 STPROC.BIND_O(X0);
301 STPROC.EXECUTE;
302 STPROC.RETRIEVE(4
303 ,X0);
304 RETURN X0;
305 END LOCATION_VALID;
306
307 FUNCTION DECODE_LOOKUP(P_LOOKUP_TYPE IN VARCHAR2
308 ,P_LOOKUP_CODE IN VARCHAR2) RETURN VARCHAR2 IS
309 X0 VARCHAR2(2000);
310 BEGIN
311 STPROC.INIT('begin :X0 := HR_GENERAL.DECODE_LOOKUP(:P_LOOKUP_TYPE, :P_LOOKUP_CODE); end;');
312 STPROC.BIND_O(X0);
313 STPROC.BIND_I(P_LOOKUP_TYPE);
314 STPROC.BIND_I(P_LOOKUP_CODE);
315 STPROC.EXECUTE;
316 STPROC.RETRIEVE(1
317 ,X0);
318 RETURN X0;
319 END DECODE_LOOKUP;
320
321 FUNCTION DECODE_GRADE(P_GRADE_ID IN NUMBER) RETURN VARCHAR2 IS
322 X0 VARCHAR2(2000);
323 BEGIN
324 STPROC.INIT('begin :X0 := HR_GENERAL.DECODE_GRADE(:P_GRADE_ID); end;');
325 STPROC.BIND_O(X0);
326 STPROC.BIND_I(P_GRADE_ID);
327 STPROC.EXECUTE;
328 STPROC.RETRIEVE(1
329 ,X0);
330 RETURN X0;
331 END DECODE_GRADE;
332
333 FUNCTION DECODE_PAYROLL(P_PAYROLL_ID IN NUMBER) RETURN VARCHAR2 IS
334 X0 VARCHAR2(2000);
335 BEGIN
336 STPROC.INIT('begin :X0 := HR_GENERAL.DECODE_PAYROLL(:P_PAYROLL_ID); end;');
337 STPROC.BIND_O(X0);
338 STPROC.BIND_I(P_PAYROLL_ID);
339 STPROC.EXECUTE;
340 STPROC.RETRIEVE(1
341 ,X0);
342 RETURN X0;
343 END DECODE_PAYROLL;
344
345 FUNCTION GET_SALARY(P_PAY_BASIS_ID IN NUMBER
346 ,P_ASSIGNMENT_ID IN NUMBER) RETURN VARCHAR2 IS
347 X0 VARCHAR2(2000);
348 BEGIN
349 STPROC.INIT('begin :X0 := HR_GENERAL.GET_SALARY(:P_PAY_BASIS_ID, :P_ASSIGNMENT_ID); end;');
350 STPROC.BIND_O(X0);
351 STPROC.BIND_I(P_PAY_BASIS_ID);
352 STPROC.BIND_I(P_ASSIGNMENT_ID);
353 STPROC.EXECUTE;
354 STPROC.RETRIEVE(1
355 ,X0);
356 RETURN X0;
357 END GET_SALARY;
358
359 FUNCTION DECODE_JOB(P_JOB_ID IN NUMBER) RETURN VARCHAR2 IS
360 X0 VARCHAR2(2000);
361 BEGIN
362 STPROC.INIT('begin :X0 := HR_GENERAL.DECODE_JOB(:P_JOB_ID); end;');
363 STPROC.BIND_O(X0);
364 STPROC.BIND_I(P_JOB_ID);
365 STPROC.EXECUTE;
366 STPROC.RETRIEVE(1
367 ,X0);
368 RETURN X0;
369 END DECODE_JOB;
370
371 FUNCTION DECODE_POSITION(P_POSITION_ID IN NUMBER) RETURN VARCHAR2 IS
372 X0 VARCHAR2(2000);
373 BEGIN
374 STPROC.INIT('begin :X0 := HR_GENERAL.DECODE_POSITION(:P_POSITION_ID); end;');
375 STPROC.BIND_O(X0);
376 STPROC.BIND_I(P_POSITION_ID);
377 STPROC.EXECUTE;
378 STPROC.RETRIEVE(1
379 ,X0);
380 RETURN X0;
381 END DECODE_POSITION;
382
383 FUNCTION DECODE_LOCATION(P_LOCATION_ID IN NUMBER) RETURN VARCHAR2 IS
384 X0 VARCHAR2(2000);
385 BEGIN
386 STPROC.INIT('begin :X0 := HR_GENERAL.DECODE_LOCATION(:P_LOCATION_ID); end;');
387 STPROC.BIND_O(X0);
388 STPROC.BIND_I(P_LOCATION_ID);
389 STPROC.EXECUTE;
390 STPROC.RETRIEVE(1
391 ,X0);
392 RETURN X0;
393 END DECODE_LOCATION;
394
395 FUNCTION DECODE_PAY_BASIS(P_PAY_BASIS_ID IN NUMBER) RETURN VARCHAR2 IS
396 X0 VARCHAR2(2000);
397 BEGIN
398 STPROC.INIT('begin :X0 := HR_GENERAL.DECODE_PAY_BASIS(:P_PAY_BASIS_ID); end;');
399 STPROC.BIND_O(X0);
400 STPROC.BIND_I(P_PAY_BASIS_ID);
401 STPROC.EXECUTE;
402 STPROC.RETRIEVE(1
403 ,X0);
404 RETURN X0;
405 END DECODE_PAY_BASIS;
406
407 FUNCTION DECODE_ASS_STATUS_TYPE(P_ASSIGNMENT_STATUS_TYPE_ID IN NUMBER
408 ,P_BUSINESS_GROUP_ID IN NUMBER) RETURN VARCHAR2 IS
409 X0 VARCHAR2(2000);
410 BEGIN
411 STPROC.INIT('begin :X0 := HR_GENERAL.DECODE_ASS_STATUS_TYPE(:P_ASSIGNMENT_STATUS_TYPE_ID, :P_BUSINESS_GROUP_ID); end;');
412 STPROC.BIND_O(X0);
413 STPROC.BIND_I(P_ASSIGNMENT_STATUS_TYPE_ID);
414 STPROC.BIND_I(P_BUSINESS_GROUP_ID);
415 STPROC.EXECUTE;
416 STPROC.RETRIEVE(1
417 ,X0);
418 RETURN X0;
419 END DECODE_ASS_STATUS_TYPE;
420
421 FUNCTION GET_WORK_PHONE(P_PERSON_ID IN NUMBER) RETURN VARCHAR2 IS
422 X0 VARCHAR2(2000);
423 BEGIN
424 STPROC.INIT('begin :X0 := HR_GENERAL.GET_WORK_PHONE(:P_PERSON_ID); end;');
425 STPROC.BIND_O(X0);
426 STPROC.BIND_I(P_PERSON_ID);
427 STPROC.EXECUTE;
428 STPROC.RETRIEVE(1
429 ,X0);
430 RETURN X0;
431 END GET_WORK_PHONE;
432
433 FUNCTION GET_HOME_PHONE(P_PERSON_ID IN NUMBER) RETURN VARCHAR2 IS
434 X0 VARCHAR2(2000);
435 BEGIN
436 STPROC.INIT('begin :X0 := HR_GENERAL.GET_HOME_PHONE(:P_PERSON_ID); end;');
437 STPROC.BIND_O(X0);
438 STPROC.BIND_I(P_PERSON_ID);
439 STPROC.EXECUTE;
440 STPROC.RETRIEVE(1
441 ,X0);
442 RETURN X0;
443 END GET_HOME_PHONE;
444
445 PROCEDURE INIT_FORMS(P_BUSINESS_GROUP_ID IN NUMBER
446 ,P_SHORT_NAME OUT NOCOPY VARCHAR2
447 ,P_BG_NAME OUT NOCOPY VARCHAR2
448 ,P_BG_CURRENCY_CODE OUT NOCOPY VARCHAR2
449 ,P_LEGISLATION_CODE OUT NOCOPY VARCHAR2
450 ,P_SESSION_DATE IN OUT NOCOPY DATE
451 ,P_SES_YESTERDAY OUT NOCOPY DATE
452 ,P_START_OF_TIME OUT NOCOPY DATE
453 ,P_END_OF_TIME OUT NOCOPY DATE
454 ,P_SYS_DATE OUT NOCOPY DATE
455 ,P_ENABLE_HR_TRACE IN BOOLEAN) IS
456 BEGIN
457 STPROC.INIT('declare P_ENABLE_HR_TRACE BOOLEAN; begin P_ENABLE_HR_TRACE := sys.diutil.int_to_bool(:P_ENABLE_HR_TRACE); HR_GENERAL.INIT_FORMS(:P_BUSINESS_GROUP_ID, :P_SHORT_NAME, :P_BG_NAME, :P_BG_CURRENCY_CODE,
458 :P_LEGISLATION_CODE, :P_SESSION_DATE, :P_SES_YESTERDAY, :P_START_OF_TIME, :P_END_OF_TIME, :P_SYS_DATE, P_ENABLE_HR_TRACE); end;');
459 STPROC.BIND_I(P_ENABLE_HR_TRACE);
460 STPROC.BIND_I(P_BUSINESS_GROUP_ID);
461 STPROC.BIND_O(P_SHORT_NAME);
462 STPROC.BIND_O(P_BG_NAME);
463 STPROC.BIND_O(P_BG_CURRENCY_CODE);
464 STPROC.BIND_O(P_LEGISLATION_CODE);
465 STPROC.BIND_IO(P_SESSION_DATE);
466 STPROC.BIND_O(P_SES_YESTERDAY);
467 STPROC.BIND_O(P_START_OF_TIME);
468 STPROC.BIND_O(P_END_OF_TIME);
469 STPROC.BIND_O(P_SYS_DATE);
470 STPROC.EXECUTE;
471 STPROC.RETRIEVE(3
472 ,P_SHORT_NAME);
473 STPROC.RETRIEVE(4
474 ,P_BG_NAME);
475 STPROC.RETRIEVE(5
476 ,P_BG_CURRENCY_CODE);
477 STPROC.RETRIEVE(6
478 ,P_LEGISLATION_CODE);
479 STPROC.RETRIEVE(7
480 ,P_SESSION_DATE);
481 STPROC.RETRIEVE(8
482 ,P_SES_YESTERDAY);
483 STPROC.RETRIEVE(9
484 ,P_START_OF_TIME);
485 STPROC.RETRIEVE(10
486 ,P_END_OF_TIME);
487 STPROC.RETRIEVE(11
488 ,P_SYS_DATE);
489 END INIT_FORMS;
490
491 FUNCTION CHK_GEOCODES_INSTALLED RETURN VARCHAR2 IS
492 X0 VARCHAR2(2000);
493 BEGIN
494 STPROC.INIT('begin :X0 := HR_GENERAL.CHK_GEOCODES_INSTALLED; end;');
495 STPROC.BIND_O(X0);
496 STPROC.EXECUTE;
497 STPROC.RETRIEVE(1
498 ,X0);
499 RETURN X0;
500 END CHK_GEOCODES_INSTALLED;
501
502 FUNCTION GET_BUDGET(P_BUDGET_ID IN NUMBER) RETURN VARCHAR2 IS
503 X0 VARCHAR2(2000);
504 BEGIN
505 STPROC.INIT('begin :X0 := HR_REPORTS.GET_BUDGET(:P_BUDGET_ID); end;');
506 STPROC.BIND_O(X0);
507 STPROC.BIND_I(P_BUDGET_ID);
508 STPROC.EXECUTE;
509 STPROC.RETRIEVE(1
510 ,X0);
511 RETURN X0;
512 END GET_BUDGET;
513
514 FUNCTION GET_BUDGET_VERSION(P_BUDGET_ID IN NUMBER
515 ,P_BUDGET_VERSION_ID IN NUMBER) RETURN VARCHAR2 IS
516 X0 VARCHAR2(2000);
517 BEGIN
518 STPROC.INIT('begin :X0 := HR_REPORTS.GET_BUDGET_VERSION(:P_BUDGET_ID, :P_BUDGET_VERSION_ID); end;');
519 STPROC.BIND_O(X0);
520 STPROC.BIND_I(P_BUDGET_ID);
521 STPROC.BIND_I(P_BUDGET_VERSION_ID);
522 STPROC.EXECUTE;
523 STPROC.RETRIEVE(1
524 ,X0);
525 RETURN X0;
526 END GET_BUDGET_VERSION;
527
528 PROCEDURE GET_ORGANIZATION(P_ORGANIZATION_ID IN NUMBER
529 ,P_ORG_NAME OUT NOCOPY VARCHAR2
530 ,P_ORG_TYPE OUT NOCOPY VARCHAR2) IS
531 BEGIN
532 STPROC.INIT('begin HR_REPORTS.GET_ORGANIZATION(:P_ORGANIZATION_ID, :P_ORG_NAME, :P_ORG_TYPE); end;');
533 STPROC.BIND_I(P_ORGANIZATION_ID);
534 STPROC.BIND_O(P_ORG_NAME);
535 STPROC.BIND_O(P_ORG_TYPE);
536 STPROC.EXECUTE;
537 STPROC.RETRIEVE(2
538 ,P_ORG_NAME);
539 STPROC.RETRIEVE(3
540 ,P_ORG_TYPE);
541 END GET_ORGANIZATION;
542
543 FUNCTION GET_JOB(P_JOB_ID IN NUMBER) RETURN VARCHAR2 IS
544 X0 VARCHAR2(2000);
545 BEGIN
546 STPROC.INIT('begin :X0 := HR_REPORTS.GET_JOB(:P_JOB_ID); end;');
547 STPROC.BIND_O(X0);
548 STPROC.BIND_I(P_JOB_ID);
549 STPROC.EXECUTE;
550 STPROC.RETRIEVE(1
551 ,X0);
552 RETURN X0;
553 END GET_JOB;
554
555 FUNCTION GET_POSITION(P_POSITION_ID IN NUMBER) RETURN VARCHAR2 IS
556 X0 VARCHAR2(2000);
557 BEGIN
558 STPROC.INIT('begin :X0 := HR_REPORTS.GET_POSITION(:P_POSITION_ID); end;');
559 STPROC.BIND_O(X0);
560 STPROC.BIND_I(P_POSITION_ID);
561 STPROC.EXECUTE;
562 STPROC.RETRIEVE(1
563 ,X0);
564 RETURN X0;
565 END GET_POSITION;
566
567 FUNCTION GET_GRADE(P_GRADE_ID IN NUMBER) RETURN VARCHAR2 IS
568 X0 VARCHAR2(2000);
569 BEGIN
570 STPROC.INIT('begin :X0 := HR_REPORTS.GET_GRADE(:P_GRADE_ID); end;');
571 STPROC.BIND_O(X0);
572 STPROC.BIND_I(P_GRADE_ID);
573 STPROC.EXECUTE;
574 STPROC.RETRIEVE(1
575 ,X0);
576 RETURN X0;
577 END GET_GRADE;
578
579 FUNCTION GET_STATUS(P_BUSINESS_GROUP_ID IN NUMBER
580 ,P_ASSIGNMENT_STATUS_TYPE_ID IN NUMBER
581 ,P_LEGISLATION_CODE IN VARCHAR2) RETURN VARCHAR2 IS
582 X0 VARCHAR2(2000);
583 BEGIN
584 STPROC.INIT('begin :X0 := HR_REPORTS.GET_STATUS(:P_BUSINESS_GROUP_ID, :P_ASSIGNMENT_STATUS_TYPE_ID, :P_LEGISLATION_CODE); end;');
585 STPROC.BIND_O(X0);
586 STPROC.BIND_I(P_BUSINESS_GROUP_ID);
587 STPROC.BIND_I(P_ASSIGNMENT_STATUS_TYPE_ID);
588 STPROC.BIND_I(P_LEGISLATION_CODE);
589 STPROC.EXECUTE;
590 STPROC.RETRIEVE(1
591 ,X0);
592 RETURN X0;
593 END GET_STATUS;
594
595 FUNCTION GET_ABS_TYPE(P_ABS_ATT_TYPE_ID IN NUMBER) RETURN VARCHAR2 IS
596 X0 VARCHAR2(2000);
597 BEGIN
598 STPROC.INIT('begin :X0 := HR_REPORTS.GET_ABS_TYPE(:P_ABS_ATT_TYPE_ID); end;');
599 STPROC.BIND_O(X0);
600 STPROC.BIND_I(P_ABS_ATT_TYPE_ID);
601 STPROC.EXECUTE;
602 STPROC.RETRIEVE(1
603 ,X0);
604 RETURN X0;
605 END GET_ABS_TYPE;
606
607 PROCEDURE GET_TIME_PERIOD(P_TIME_PERIOD_ID IN NUMBER
608 ,P_PERIOD_NAME OUT NOCOPY VARCHAR2
609 ,P_START_DATE OUT NOCOPY DATE
610 ,P_END_DATE OUT NOCOPY DATE) IS
611 BEGIN
612 STPROC.INIT('begin HR_REPORTS.GET_TIME_PERIOD(:P_TIME_PERIOD_ID, :P_PERIOD_NAME, :P_START_DATE, :P_END_DATE); end;');
613 STPROC.BIND_I(P_TIME_PERIOD_ID);
614 STPROC.BIND_O(P_PERIOD_NAME);
615 STPROC.BIND_O(P_START_DATE);
616 STPROC.BIND_O(P_END_DATE);
617 STPROC.EXECUTE;
618 STPROC.RETRIEVE(2
619 ,P_PERIOD_NAME);
620 STPROC.RETRIEVE(3
621 ,P_START_DATE);
622 STPROC.RETRIEVE(4
623 ,P_END_DATE);
624 END GET_TIME_PERIOD;
625
626 FUNCTION GET_BUSINESS_GROUP(P_BUSINESS_GROUP_ID IN NUMBER) RETURN VARCHAR2 IS
627 X0 VARCHAR2(2000);
628 BEGIN
629 STPROC.INIT('begin :X0 := HR_REPORTS.GET_BUSINESS_GROUP(:P_BUSINESS_GROUP_ID); end;');
630 STPROC.BIND_O(X0);
631 STPROC.BIND_I(P_BUSINESS_GROUP_ID);
632 STPROC.EXECUTE;
633 STPROC.RETRIEVE(1
634 ,X0);
635 RETURN X0;
636 END GET_BUSINESS_GROUP;
637
638 FUNCTION COUNT_ORG_SUBORDINATES(P_ORG_STRUCTURE_VERSION_ID IN NUMBER
639 ,P_PARENT_ORGANIZATION_ID IN NUMBER) RETURN NUMBER IS
640 X0 NUMBER;
641 BEGIN
642 STPROC.INIT('begin :X0 := HR_REPORTS.COUNT_ORG_SUBORDINATES(:P_ORG_STRUCTURE_VERSION_ID, :P_PARENT_ORGANIZATION_ID); end;');
643 STPROC.BIND_O(X0);
644 STPROC.BIND_I(P_ORG_STRUCTURE_VERSION_ID);
645 STPROC.BIND_I(P_PARENT_ORGANIZATION_ID);
646 STPROC.EXECUTE;
647 STPROC.RETRIEVE(1
648 ,X0);
649 RETURN X0;
650 END COUNT_ORG_SUBORDINATES;
651
652 FUNCTION COUNT_POS_SUBORDINATES(P_POS_STRUCTURE_VERSION_ID IN NUMBER
653 ,P_PARENT_POSITION_ID IN NUMBER) RETURN NUMBER IS
654 X0 NUMBER;
655 BEGIN
656 STPROC.INIT('begin :X0 := HR_REPORTS.COUNT_POS_SUBORDINATES(:P_POS_STRUCTURE_VERSION_ID, :P_PARENT_POSITION_ID); end;');
657 STPROC.BIND_O(X0);
658 STPROC.BIND_I(P_POS_STRUCTURE_VERSION_ID);
659 STPROC.BIND_I(P_PARENT_POSITION_ID);
660 STPROC.EXECUTE;
661 STPROC.RETRIEVE(1
662 ,X0);
663 RETURN X0;
664 END COUNT_POS_SUBORDINATES;
665
666 PROCEDURE GET_ORGANIZATION_HIERARCHY(P_ORGANIZATION_STRUCTURE_ID IN NUMBER
667 ,P_ORG_STRUCTURE_VERSION_ID IN NUMBER
668 ,P_ORG_STRUCTURE_NAME OUT NOCOPY VARCHAR2
669 ,P_ORG_VERSION OUT NOCOPY NUMBER
670 ,P_VERSION_START_DATE OUT NOCOPY DATE
671 ,P_VERSION_END_DATE OUT NOCOPY DATE) IS
672 BEGIN
673 STPROC.INIT('begin HR_REPORTS.GET_ORGANIZATION_HIERARCHY(:P_ORGANIZATION_STRUCTURE_ID, :P_ORG_STRUCTURE_VERSION_ID,
674 :P_ORG_STRUCTURE_NAME, :P_ORG_VERSION, :P_VERSION_START_DATE, :P_VERSION_END_DATE); end;');
675 STPROC.BIND_I(P_ORGANIZATION_STRUCTURE_ID);
676 STPROC.BIND_I(P_ORG_STRUCTURE_VERSION_ID);
677 STPROC.BIND_O(P_ORG_STRUCTURE_NAME);
678 STPROC.BIND_O(P_ORG_VERSION);
679 STPROC.BIND_O(P_VERSION_START_DATE);
680 STPROC.BIND_O(P_VERSION_END_DATE);
681 STPROC.EXECUTE;
682 STPROC.RETRIEVE(3
683 ,P_ORG_STRUCTURE_NAME);
684 STPROC.RETRIEVE(4
685 ,P_ORG_VERSION);
686 STPROC.RETRIEVE(5
687 ,P_VERSION_START_DATE);
688 STPROC.RETRIEVE(6
689 ,P_VERSION_END_DATE);
690 END GET_ORGANIZATION_HIERARCHY;
691
692 PROCEDURE GET_POSITION_HIERARCHY(P_POSITION_STRUCTURE_ID IN NUMBER
693 ,P_POS_STRUCTURE_VERSION_ID IN NUMBER
694 ,P_POS_STRUCTURE_NAME OUT NOCOPY VARCHAR2
695 ,P_POS_VERSION OUT NOCOPY NUMBER
696 ,P_VERSION_START_DATE OUT NOCOPY DATE
697 ,P_VERSION_END_DATE OUT NOCOPY DATE) IS
698 BEGIN
699 STPROC.INIT('begin HR_REPORTS.GET_POSITION_HIERARCHY(:P_POSITION_STRUCTURE_ID, :P_POS_STRUCTURE_VERSION_ID, :P_POS_STRUCTURE_NAME,
700 :P_POS_VERSION, :P_VERSION_START_DATE, :P_VERSION_END_DATE); end;');
701 STPROC.BIND_I(P_POSITION_STRUCTURE_ID);
702 STPROC.BIND_I(P_POS_STRUCTURE_VERSION_ID);
703 STPROC.BIND_O(P_POS_STRUCTURE_NAME);
704 STPROC.BIND_O(P_POS_VERSION);
705 STPROC.BIND_O(P_VERSION_START_DATE);
706 STPROC.BIND_O(P_VERSION_END_DATE);
707 STPROC.EXECUTE;
708 STPROC.RETRIEVE(3
709 ,P_POS_STRUCTURE_NAME);
710 STPROC.RETRIEVE(4
711 ,P_POS_VERSION);
712 STPROC.RETRIEVE(5
713 ,P_VERSION_START_DATE);
714 STPROC.RETRIEVE(6
715 ,P_VERSION_END_DATE);
716 END GET_POSITION_HIERARCHY;
717
718 FUNCTION GET_LOOKUP_MEANING(P_LOOKUP_TYPE IN VARCHAR2
719 ,P_LOOKUP_CODE IN VARCHAR2) RETURN VARCHAR2 IS
720 X0 VARCHAR2(2000);
721 BEGIN
722 STPROC.INIT('begin :X0 := HR_REPORTS.GET_LOOKUP_MEANING(:P_LOOKUP_TYPE, :P_LOOKUP_CODE); end;');
723 STPROC.BIND_O(X0);
724 STPROC.BIND_I(P_LOOKUP_TYPE);
725 STPROC.BIND_I(P_LOOKUP_CODE);
726 STPROC.EXECUTE;
727 STPROC.RETRIEVE(1
728 ,X0);
729 RETURN X0;
730 END GET_LOOKUP_MEANING;
731
732 FUNCTION PERSON_MATCHING_SKILLS(P_PERSON_ID IN NUMBER
733 ,P_JOB_POSITION_ID IN NUMBER
734 ,P_JOB_POSITION_TYPE IN VARCHAR2
735 ,P_MATCHING_LEVEL IN VARCHAR2
736 ,P_NO_OF_ESSENTIAL IN NUMBER
737 ,P_NO_OF_DESIRABLE IN NUMBER) RETURN BOOLEAN IS
738 X0 BOOLEAN;
739 BEGIN
740 STPROC.INIT('declare X0rv BOOLEAN; begin X0rv := HR_REPORTS.PERSON_MATCHING_SKILLS(:P_PERSON_ID, :P_JOB_POSITION_ID, :P_JOB_POSITION_TYPE,
741 :P_MATCHING_LEVEL, :P_NO_OF_ESSENTIAL, :P_NO_OF_DESIRABLE); :X0 := sys.diutil.bool_to_int(X0rv); end;');
742 STPROC.BIND_I(P_PERSON_ID);
743 STPROC.BIND_I(P_JOB_POSITION_ID);
744 STPROC.BIND_I(P_JOB_POSITION_TYPE);
745 STPROC.BIND_I(P_MATCHING_LEVEL);
746 STPROC.BIND_I(P_NO_OF_ESSENTIAL);
747 STPROC.BIND_I(P_NO_OF_DESIRABLE);
748 STPROC.BIND_O(X0);
749 STPROC.EXECUTE;
750 STPROC.RETRIEVE(7
751 ,X0);
752 RETURN X0;
753 END PERSON_MATCHING_SKILLS;
754
755 FUNCTION GET_PAYROLL_NAME(P_SESSION_DATE IN DATE
756 ,P_PAYROLL_ID IN NUMBER) RETURN VARCHAR2 IS
757 X0 VARCHAR2(2000);
758 BEGIN
759 STPROC.INIT('begin :X0 := HR_REPORTS.GET_PAYROLL_NAME(:P_SESSION_DATE, :P_PAYROLL_ID); end;');
760 STPROC.BIND_O(X0);
761 STPROC.BIND_I(P_SESSION_DATE);
762 STPROC.BIND_I(P_PAYROLL_ID);
763 STPROC.EXECUTE;
764 STPROC.RETRIEVE(1
765 ,X0);
766 RETURN X0;
767 END GET_PAYROLL_NAME;
768
769 FUNCTION GET_ELEMENT_NAME(P_SESSION_DATE IN DATE
770 ,P_ELEMENT_TYPE_ID IN NUMBER) RETURN VARCHAR2 IS
771 X0 VARCHAR2(2000);
772 BEGIN
773 STPROC.INIT('begin :X0 := HR_REPORTS.GET_ELEMENT_NAME(:P_SESSION_DATE, :P_ELEMENT_TYPE_ID); end;');
774 STPROC.BIND_O(X0);
775 STPROC.BIND_I(P_SESSION_DATE);
776 STPROC.BIND_I(P_ELEMENT_TYPE_ID);
777 STPROC.EXECUTE;
778 STPROC.RETRIEVE(1
779 ,X0);
780 RETURN X0;
781 END GET_ELEMENT_NAME;
782
783 PROCEDURE GEN_PARTIAL_MATCHING_LEXICAL(P_CONCATENATED_SEGMENTS IN VARCHAR2
784 ,P_ID_FLEX_NUM IN NUMBER
785 ,P_MATCHING_LEXICAL IN OUT NOCOPY VARCHAR2) IS
786 BEGIN
787 STPROC.INIT('begin HR_REPORTS.GEN_PARTIAL_MATCHING_LEXICAL(:P_CONCATENATED_SEGMENTS, :P_ID_FLEX_NUM, :P_MATCHING_LEXICAL); end;');
788 STPROC.BIND_I(P_CONCATENATED_SEGMENTS);
789 STPROC.BIND_I(P_ID_FLEX_NUM);
790 STPROC.BIND_IO(P_MATCHING_LEXICAL);
791 STPROC.EXECUTE;
792 STPROC.RETRIEVE(3
793 ,P_MATCHING_LEXICAL);
794 END GEN_PARTIAL_MATCHING_LEXICAL;
795
796 PROCEDURE GET_ATTRIBUTES(P_CONCATENATED_SEGMENTS IN VARCHAR2
797 ,P_NAME IN VARCHAR2
798 ,P_SEGMENTS_USED OUT NOCOPY NUMBER
799 ,P_VALUE1 OUT NOCOPY VARCHAR2
800 ,P_VALUE2 OUT NOCOPY VARCHAR2
801 ,P_VALUE3 OUT NOCOPY VARCHAR2
802 ,P_VALUE4 OUT NOCOPY VARCHAR2
803 ,P_VALUE5 OUT NOCOPY VARCHAR2
804 ,P_VALUE6 OUT NOCOPY VARCHAR2
805 ,P_VALUE7 OUT NOCOPY VARCHAR2
806 ,P_VALUE8 OUT NOCOPY VARCHAR2
807 ,P_VALUE9 OUT NOCOPY VARCHAR2
808 ,P_VALUE10 OUT NOCOPY VARCHAR2
809 ,P_VALUE11 OUT NOCOPY VARCHAR2
810 ,P_VALUE12 OUT NOCOPY VARCHAR2
811 ,P_VALUE13 OUT NOCOPY VARCHAR2
812 ,P_VALUE14 OUT NOCOPY VARCHAR2
813 ,P_VALUE15 OUT NOCOPY VARCHAR2
814 ,P_VALUE16 OUT NOCOPY VARCHAR2
815 ,P_VALUE17 OUT NOCOPY VARCHAR2
816 ,P_VALUE18 OUT NOCOPY VARCHAR2
817 ,P_VALUE19 OUT NOCOPY VARCHAR2
818 ,P_VALUE20 OUT NOCOPY VARCHAR2
819 ,P_VALUE21 OUT NOCOPY VARCHAR2
820 ,P_VALUE22 OUT NOCOPY VARCHAR2
821 ,P_VALUE23 OUT NOCOPY VARCHAR2
822 ,P_VALUE24 OUT NOCOPY VARCHAR2
823 ,P_VALUE25 OUT NOCOPY VARCHAR2
824 ,P_VALUE26 OUT NOCOPY VARCHAR2
825 ,P_VALUE27 OUT NOCOPY VARCHAR2
826 ,P_VALUE28 OUT NOCOPY VARCHAR2
827 ,P_VALUE29 OUT NOCOPY VARCHAR2
828 ,P_VALUE30 OUT NOCOPY VARCHAR2) IS
829 BEGIN
830 STPROC.INIT('begin HR_REPORTS.GET_ATTRIBUTES(:P_CONCATENATED_SEGMENTS, :P_NAME, :P_SEGMENTS_USED, :P_VALUE1, :P_VALUE2, :P_VALUE3, :P_VALUE4, :P_VALUE5, :P_VALUE6, :P_VALUE7, :P_VALUE8,
831 :P_VALUE9, :P_VALUE10, :P_VALUE11, :P_VALUE12, :P_VALUE13, :P_VALUE14, :P_VALUE15, :P_VALUE16, :P_VALUE17, :P_VALUE18, :P_VALUE19, :P_VALUE20, :P_VALUE21, :P_VALUE22, :P_VALUE23, :P_VALUE24,
832 :P_VALUE25, :P_VALUE26, :P_VALUE27, :P_VALUE28, :P_VALUE29, :P_VALUE30); end;');
833 STPROC.BIND_I(P_CONCATENATED_SEGMENTS);
834 STPROC.BIND_I(P_NAME);
835 STPROC.BIND_O(P_SEGMENTS_USED);
836 STPROC.BIND_O(P_VALUE1);
837 STPROC.BIND_O(P_VALUE2);
838 STPROC.BIND_O(P_VALUE3);
839 STPROC.BIND_O(P_VALUE4);
840 STPROC.BIND_O(P_VALUE5);
841 STPROC.BIND_O(P_VALUE6);
842 STPROC.BIND_O(P_VALUE7);
843 STPROC.BIND_O(P_VALUE8);
844 STPROC.BIND_O(P_VALUE9);
845 STPROC.BIND_O(P_VALUE10);
846 STPROC.BIND_O(P_VALUE11);
847 STPROC.BIND_O(P_VALUE12);
848 STPROC.BIND_O(P_VALUE13);
849 STPROC.BIND_O(P_VALUE14);
850 STPROC.BIND_O(P_VALUE15);
851 STPROC.BIND_O(P_VALUE16);
852 STPROC.BIND_O(P_VALUE17);
853 STPROC.BIND_O(P_VALUE18);
854 STPROC.BIND_O(P_VALUE19);
855 STPROC.BIND_O(P_VALUE20);
856 STPROC.BIND_O(P_VALUE21);
857 STPROC.BIND_O(P_VALUE22);
858 STPROC.BIND_O(P_VALUE23);
859 STPROC.BIND_O(P_VALUE24);
860 STPROC.BIND_O(P_VALUE25);
861 STPROC.BIND_O(P_VALUE26);
862 STPROC.BIND_O(P_VALUE27);
863 STPROC.BIND_O(P_VALUE28);
864 STPROC.BIND_O(P_VALUE29);
865 STPROC.BIND_O(P_VALUE30);
866 STPROC.EXECUTE;
867 STPROC.RETRIEVE(3
868 ,P_SEGMENTS_USED);
869 STPROC.RETRIEVE(4
870 ,P_VALUE1);
871 STPROC.RETRIEVE(5
872 ,P_VALUE2);
873 STPROC.RETRIEVE(6
874 ,P_VALUE3);
875 STPROC.RETRIEVE(7
876 ,P_VALUE4);
877 STPROC.RETRIEVE(8
878 ,P_VALUE5);
879 STPROC.RETRIEVE(9
880 ,P_VALUE6);
881 STPROC.RETRIEVE(10
882 ,P_VALUE7);
883 STPROC.RETRIEVE(11
884 ,P_VALUE8);
885 STPROC.RETRIEVE(12
886 ,P_VALUE9);
887 STPROC.RETRIEVE(13
888 ,P_VALUE10);
889 STPROC.RETRIEVE(14
890 ,P_VALUE11);
891 STPROC.RETRIEVE(15
892 ,P_VALUE12);
893 STPROC.RETRIEVE(16
894 ,P_VALUE13);
895 STPROC.RETRIEVE(17
896 ,P_VALUE14);
897 STPROC.RETRIEVE(18
898 ,P_VALUE15);
899 STPROC.RETRIEVE(19
900 ,P_VALUE16);
901 STPROC.RETRIEVE(20
902 ,P_VALUE17);
903 STPROC.RETRIEVE(21
904 ,P_VALUE18);
905 STPROC.RETRIEVE(22
906 ,P_VALUE19);
907 STPROC.RETRIEVE(23
908 ,P_VALUE20);
909 STPROC.RETRIEVE(24
910 ,P_VALUE21);
911 STPROC.RETRIEVE(25
912 ,P_VALUE22);
913 STPROC.RETRIEVE(26
914 ,P_VALUE23);
915 STPROC.RETRIEVE(27
916 ,P_VALUE24);
917 STPROC.RETRIEVE(28
918 ,P_VALUE25);
919 STPROC.RETRIEVE(29
920 ,P_VALUE26);
921 STPROC.RETRIEVE(30
922 ,P_VALUE27);
923 STPROC.RETRIEVE(31
924 ,P_VALUE28);
925 STPROC.RETRIEVE(32
926 ,P_VALUE29);
927 STPROC.RETRIEVE(33
928 ,P_VALUE30);
929 END GET_ATTRIBUTES;
930
931 PROCEDURE GET_SEGMENTS(P_CONCATENATED_SEGMENTS IN VARCHAR2
932 ,P_ID_FLEX_NUM IN NUMBER
933 ,P_SEGMENTS_USED OUT NOCOPY NUMBER
934 ,P_VALUE1 OUT NOCOPY VARCHAR2
935 ,P_VALUE2 OUT NOCOPY VARCHAR2
936 ,P_VALUE3 OUT NOCOPY VARCHAR2
937 ,P_VALUE4 OUT NOCOPY VARCHAR2
938 ,P_VALUE5 OUT NOCOPY VARCHAR2
939 ,P_VALUE6 OUT NOCOPY VARCHAR2
940 ,P_VALUE7 OUT NOCOPY VARCHAR2
941 ,P_VALUE8 OUT NOCOPY VARCHAR2
942 ,P_VALUE9 OUT NOCOPY VARCHAR2
943 ,P_VALUE10 OUT NOCOPY VARCHAR2
944 ,P_VALUE11 OUT NOCOPY VARCHAR2
945 ,P_VALUE12 OUT NOCOPY VARCHAR2
946 ,P_VALUE13 OUT NOCOPY VARCHAR2
947 ,P_VALUE14 OUT NOCOPY VARCHAR2
948 ,P_VALUE15 OUT NOCOPY VARCHAR2
949 ,P_VALUE16 OUT NOCOPY VARCHAR2
950 ,P_VALUE17 OUT NOCOPY VARCHAR2
951 ,P_VALUE18 OUT NOCOPY VARCHAR2
952 ,P_VALUE19 OUT NOCOPY VARCHAR2
953 ,P_VALUE20 OUT NOCOPY VARCHAR2
954 ,P_VALUE21 OUT NOCOPY VARCHAR2
955 ,P_VALUE22 OUT NOCOPY VARCHAR2
956 ,P_VALUE23 OUT NOCOPY VARCHAR2
957 ,P_VALUE24 OUT NOCOPY VARCHAR2
958 ,P_VALUE25 OUT NOCOPY VARCHAR2
959 ,P_VALUE26 OUT NOCOPY VARCHAR2
960 ,P_VALUE27 OUT NOCOPY VARCHAR2
961 ,P_VALUE28 OUT NOCOPY VARCHAR2
962 ,P_VALUE29 OUT NOCOPY VARCHAR2
963 ,P_VALUE30 OUT NOCOPY VARCHAR2) IS
964 BEGIN
965 STPROC.INIT('begin HR_REPORTS.GET_SEGMENTS(:P_CONCATENATED_SEGMENTS, :P_ID_FLEX_NUM, :P_SEGMENTS_USED, :P_VALUE1, :P_VALUE2, :P_VALUE3,
966 \:P_VALUE4, :P_VALUE5, :P_VALUE6, :P_VALUE7, :P_VALUE8, :P_VALUE9, :P_VALUE10, :P_VALUE11, :P_VALUE12, :P_VALUE13, :P_VALUE14, :P_VALUE15,
967 :P_VALUE16, :P_VALUE17, :P_VALUE18, :P_VALUE19, :P_VALUE20, :P_VALUE21, :P_VALUE22, :P_VALUE23, :P_VALUE24, :P_VALUE25, :P_VALUE26, :P_VALUE27,
968 :P_VALUE28, :P_VALUE29, :P_VALUE30); end;');
969 STPROC.BIND_I(P_CONCATENATED_SEGMENTS);
970 STPROC.BIND_I(P_ID_FLEX_NUM);
971 STPROC.BIND_O(P_SEGMENTS_USED);
972 STPROC.BIND_O(P_VALUE1);
973 STPROC.BIND_O(P_VALUE2);
974 STPROC.BIND_O(P_VALUE3);
975 STPROC.BIND_O(P_VALUE4);
976 STPROC.BIND_O(P_VALUE5);
977 STPROC.BIND_O(P_VALUE6);
978 STPROC.BIND_O(P_VALUE7);
979 STPROC.BIND_O(P_VALUE8);
980 STPROC.BIND_O(P_VALUE9);
981 STPROC.BIND_O(P_VALUE10);
982 STPROC.BIND_O(P_VALUE11);
983 STPROC.BIND_O(P_VALUE12);
984 STPROC.BIND_O(P_VALUE13);
985 STPROC.BIND_O(P_VALUE14);
986 STPROC.BIND_O(P_VALUE15);
987 STPROC.BIND_O(P_VALUE16);
988 STPROC.BIND_O(P_VALUE17);
989 STPROC.BIND_O(P_VALUE18);
990 STPROC.BIND_O(P_VALUE19);
991 STPROC.BIND_O(P_VALUE20);
992 STPROC.BIND_O(P_VALUE21);
993 STPROC.BIND_O(P_VALUE22);
994 STPROC.BIND_O(P_VALUE23);
995 STPROC.BIND_O(P_VALUE24);
996 STPROC.BIND_O(P_VALUE25);
997 STPROC.BIND_O(P_VALUE26);
998 STPROC.BIND_O(P_VALUE27);
999 STPROC.BIND_O(P_VALUE28);
1000 STPROC.BIND_O(P_VALUE29);
1001 STPROC.BIND_O(P_VALUE30);
1002 STPROC.EXECUTE;
1003 STPROC.RETRIEVE(3
1004 ,P_SEGMENTS_USED);
1005 STPROC.RETRIEVE(4
1006 ,P_VALUE1);
1007 STPROC.RETRIEVE(5
1008 ,P_VALUE2);
1009 STPROC.RETRIEVE(6
1010 ,P_VALUE3);
1011 STPROC.RETRIEVE(7
1012 ,P_VALUE4);
1013 STPROC.RETRIEVE(8
1014 ,P_VALUE5);
1015 STPROC.RETRIEVE(9
1016 ,P_VALUE6);
1017 STPROC.RETRIEVE(10
1018 ,P_VALUE7);
1019 STPROC.RETRIEVE(11
1020 ,P_VALUE8);
1021 STPROC.RETRIEVE(12
1022 ,P_VALUE9);
1023 STPROC.RETRIEVE(13
1024 ,P_VALUE10);
1025 STPROC.RETRIEVE(14
1026 ,P_VALUE11);
1027 STPROC.RETRIEVE(15
1028 ,P_VALUE12);
1029 STPROC.RETRIEVE(16
1030 ,P_VALUE13);
1031 STPROC.RETRIEVE(17
1032 ,P_VALUE14);
1033 STPROC.RETRIEVE(18
1034 ,P_VALUE15);
1035 STPROC.RETRIEVE(19
1036 ,P_VALUE16);
1037 STPROC.RETRIEVE(20
1038 ,P_VALUE17);
1039 STPROC.RETRIEVE(21
1040 ,P_VALUE18);
1041 STPROC.RETRIEVE(22
1042 ,P_VALUE19);
1043 STPROC.RETRIEVE(23
1044 ,P_VALUE20);
1045 STPROC.RETRIEVE(24
1046 ,P_VALUE21);
1047 STPROC.RETRIEVE(25
1048 ,P_VALUE22);
1049 STPROC.RETRIEVE(26
1050 ,P_VALUE23);
1051 STPROC.RETRIEVE(27
1052 ,P_VALUE24);
1053 STPROC.RETRIEVE(28
1054 ,P_VALUE25);
1055 STPROC.RETRIEVE(29
1056 ,P_VALUE26);
1057 STPROC.RETRIEVE(30
1058 ,P_VALUE27);
1059 STPROC.RETRIEVE(31
1060 ,P_VALUE28);
1061 STPROC.RETRIEVE(32
1062 ,P_VALUE29);
1063 STPROC.RETRIEVE(33
1064 ,P_VALUE30);
1065 END GET_SEGMENTS;
1066
1067 PROCEDURE GET_DESC_FLEX(P_APPL_SHORT_NAME IN VARCHAR2
1068 ,P_DESC_FLEX_NAME IN VARCHAR2
1069 ,P_TABLE_ALIAS IN VARCHAR2
1070 ,P_TITLE OUT NOCOPY VARCHAR2
1071 ,P_LABEL_EXPR OUT NOCOPY VARCHAR2
1072 ,P_COLUMN_EXPR OUT NOCOPY VARCHAR2) IS
1073 BEGIN
1074 STPROC.INIT('begin HR_REPORTS.GET_DESC_FLEX(:P_APPL_SHORT_NAME, :P_DESC_FLEX_NAME, :P_TABLE_ALIAS, :P_TITLE, :P_LABEL_EXPR,
1075 :P_COLUMN_EXPR); end;');
1076 STPROC.BIND_I(P_APPL_SHORT_NAME);
1077 STPROC.BIND_I(P_DESC_FLEX_NAME);
1078 STPROC.BIND_I(P_TABLE_ALIAS);
1079 STPROC.BIND_O(P_TITLE);
1080 STPROC.BIND_O(P_LABEL_EXPR);
1081 STPROC.BIND_O(P_COLUMN_EXPR);
1082 STPROC.EXECUTE;
1083 STPROC.RETRIEVE(4
1084 ,P_TITLE);
1085 STPROC.RETRIEVE(5
1086 ,P_LABEL_EXPR);
1087 STPROC.RETRIEVE(6
1088 ,P_COLUMN_EXPR);
1089 END GET_DESC_FLEX;
1090
1091 PROCEDURE GET_DESC_FLEX_CONTEXT(P_APPL_SHORT_NAME IN VARCHAR2
1092 ,P_DESC_FLEX_NAME IN VARCHAR2
1093 ,P_TABLE_ALIAS IN VARCHAR2
1094 ,P_TITLE OUT NOCOPY VARCHAR2
1095 ,P_LABEL_EXPR OUT NOCOPY VARCHAR2
1096 ,P_COLUMN_EXPR OUT NOCOPY VARCHAR2) IS
1097 BEGIN
1098 STPROC.INIT('begin HR_REPORTS.GET_DESC_FLEX_CONTEXT(:P_APPL_SHORT_NAME, :P_DESC_FLEX_NAME, :P_TABLE_ALIAS, :P_TITLE,
1099 :P_LABEL_EXPR, :P_COLUMN_EXPR); end;');
1100 STPROC.BIND_I(P_APPL_SHORT_NAME);
1101 STPROC.BIND_I(P_DESC_FLEX_NAME);
1102 STPROC.BIND_I(P_TABLE_ALIAS);
1103 STPROC.BIND_O(P_TITLE);
1104 STPROC.BIND_O(P_LABEL_EXPR);
1105 STPROC.BIND_O(P_COLUMN_EXPR);
1106 STPROC.EXECUTE;
1107 STPROC.RETRIEVE(4
1108 ,P_TITLE);
1109 STPROC.RETRIEVE(5
1110 ,P_LABEL_EXPR);
1111 STPROC.RETRIEVE(6
1112 ,P_COLUMN_EXPR);
1113 END GET_DESC_FLEX_CONTEXT;
1114
1115 PROCEDURE GET_DVLPR_DESC_FLEX(P_APPL_SHORT_NAME IN VARCHAR2
1116 ,P_DESC_FLEX_NAME IN VARCHAR2
1117 ,P_DESC_FLEX_CONTEXT IN VARCHAR2
1118 ,P_TABLE_ALIAS IN VARCHAR2
1119 ,P_TITLE OUT NOCOPY VARCHAR2
1120 ,P_LABEL_EXPR OUT NOCOPY VARCHAR2
1121 ,P_COLUMN_EXPR OUT NOCOPY VARCHAR2) IS
1122 BEGIN
1123 STPROC.INIT('begin HR_REPORTS.GET_DVLPR_DESC_FLEX(:P_APPL_SHORT_NAME, :P_DESC_FLEX_NAME, :P_DESC_FLEX_CONTEXT,
1124 :P_TABLE_ALIAS, :P_TITLE, :P_LABEL_EXPR, :P_COLUMN_EXPR); end;');
1125 STPROC.BIND_I(P_APPL_SHORT_NAME);
1126 STPROC.BIND_I(P_DESC_FLEX_NAME);
1127 STPROC.BIND_I(P_DESC_FLEX_CONTEXT);
1128 STPROC.BIND_I(P_TABLE_ALIAS);
1129 STPROC.BIND_O(P_TITLE);
1130 STPROC.BIND_O(P_LABEL_EXPR);
1131 STPROC.BIND_O(P_COLUMN_EXPR);
1132 STPROC.EXECUTE;
1133 STPROC.RETRIEVE(5
1134 ,P_TITLE);
1135 STPROC.RETRIEVE(6
1136 ,P_LABEL_EXPR);
1137 STPROC.RETRIEVE(7
1138 ,P_COLUMN_EXPR);
1139 END GET_DVLPR_DESC_FLEX;
1140
1141 FUNCTION GET_PERSON_NAME(P_SESSION_DATE IN DATE
1142 ,P_PERSON_ID IN NUMBER) RETURN VARCHAR2 IS
1143 X0 VARCHAR2(2000);
1144 BEGIN
1145 STPROC.INIT('begin :X0 := HR_REPORTS.GET_PERSON_NAME(:P_SESSION_DATE, :P_PERSON_ID); end;');
1146 STPROC.BIND_O(X0);
1147 STPROC.BIND_I(P_SESSION_DATE);
1148 STPROC.BIND_I(P_PERSON_ID);
1149 STPROC.EXECUTE;
1150 STPROC.RETRIEVE(1
1151 ,X0);
1152 RETURN X0;
1153 END GET_PERSON_NAME;
1154
1155 FUNCTION GET_SALARY(P_BUSINESS_GROUP_ID IN NUMBER
1156 ,P_ASSIGNMENT_ID IN NUMBER
1157 ,P_REPORT_DATE IN DATE) RETURN NUMBER IS
1158 X0 NUMBER;
1159 BEGIN
1160 STPROC.INIT('begin :X0 := HR_US_REPORTS.GET_SALARY(:P_BUSINESS_GROUP_ID, :P_ASSIGNMENT_ID,
1161 :P_REPORT_DATE); end;');
1162 STPROC.BIND_O(X0);
1163 STPROC.BIND_I(P_BUSINESS_GROUP_ID);
1164 STPROC.BIND_I(P_ASSIGNMENT_ID);
1165 STPROC.BIND_I(P_REPORT_DATE);
1166 STPROC.EXECUTE;
1167 STPROC.RETRIEVE(1
1168 ,X0);
1169 RETURN X0;
1170 END GET_SALARY;
1171
1172 PROCEDURE GET_NEW_HIRE_CONTACT(P_PERSON_ID IN NUMBER
1173 ,P_BUSINESS_GROUP_ID IN NUMBER
1174 ,P_REPORT_DATE IN DATE
1175 ,P_CONTACT_NAME OUT NOCOPY VARCHAR2
1176 ,P_CONTACT_TITLE OUT NOCOPY VARCHAR2
1177 ,P_CONTACT_PHONE OUT NOCOPY VARCHAR2) IS
1178 BEGIN
1179 STPROC.INIT('begin HR_US_REPORTS.GET_NEW_HIRE_CONTACT(:P_PERSON_ID, :
1180 P_BUSINESS_GROUP_ID, :P_REPORT_DATE, :P_CONTACT_NAME, :P_CONTACT_TITLE,
1181 :P_CONTACT_PHONE); end;');
1182 STPROC.BIND_I(P_PERSON_ID);
1183 STPROC.BIND_I(P_BUSINESS_GROUP_ID);
1184 STPROC.BIND_I(P_REPORT_DATE);
1185 STPROC.BIND_O(P_CONTACT_NAME);
1186 STPROC.BIND_O(P_CONTACT_TITLE);
1187 STPROC.BIND_O(P_CONTACT_PHONE);
1188 STPROC.EXECUTE;
1189 STPROC.RETRIEVE(4
1190 ,P_CONTACT_NAME);
1191 STPROC.RETRIEVE(5
1192 ,P_CONTACT_TITLE);
1193 STPROC.RETRIEVE(6
1194 ,P_CONTACT_PHONE);
1195 END GET_NEW_HIRE_CONTACT;
1196
1197 PROCEDURE GET_ADDRESS(P_LOCATION_ID IN NUMBER
1198 ,P_ADDRESS OUT NOCOPY VARCHAR2) IS
1199 BEGIN
1200 STPROC.INIT('begin HR_US_REPORTS.GET_ADDRESS(:P_LOCATION_ID, :P_ADDRESS); end;');
1201 STPROC.BIND_I(P_LOCATION_ID);
1202 STPROC.BIND_O(P_ADDRESS);
1203 STPROC.EXECUTE;
1204 STPROC.RETRIEVE(2
1205 ,P_ADDRESS);
1206 END GET_ADDRESS;
1207
1208 PROCEDURE GET_EMPLOYEE_ADDRESS(P_PERSON_ID IN NUMBER
1209 ,P_ADDRESS OUT NOCOPY VARCHAR2) IS
1210 BEGIN
1211 STPROC.INIT('begin HR_US_REPORTS.GET_EMPLOYEE_ADDRESS(:P_PERSON_ID, :P_ADDRESS); end;');
1212 STPROC.BIND_I(P_PERSON_ID);
1213 STPROC.BIND_O(P_ADDRESS);
1214 STPROC.EXECUTE;
1215 STPROC.RETRIEVE(2
1216 ,P_ADDRESS);
1217 END GET_EMPLOYEE_ADDRESS;
1218
1219 PROCEDURE GET_COUNTY_ADDRESS(P_LOCATION_ID IN NUMBER
1220 ,P_ADDRESS OUT NOCOPY VARCHAR2) IS
1221 BEGIN
1222 STPROC.INIT('begin HR_US_REPORTS.GET_COUNTY_ADDRESS(:P_LOCATION_ID, :P_ADDRESS); end;');
1223 STPROC.BIND_I(P_LOCATION_ID);
1224 STPROC.BIND_O(P_ADDRESS);
1225 STPROC.EXECUTE;
1226 STPROC.RETRIEVE(2
1227 ,P_ADDRESS);
1228 END GET_COUNTY_ADDRESS;
1229
1230 PROCEDURE GET_ACTIVITY(P_ESTABLISHMENT_ID IN NUMBER
1231 ,P_ACTIVITY OUT NOCOPY VARCHAR2) IS
1232 BEGIN
1233 STPROC.INIT('begin HR_US_REPORTS.GET_ACTIVITY(:P_ESTABLISHMENT_ID, :P_ACTIVITY); end;');
1234 STPROC.BIND_I(P_ESTABLISHMENT_ID);
1235 STPROC.BIND_O(P_ACTIVITY);
1236 STPROC.EXECUTE;
1237 STPROC.RETRIEVE(2
1238 ,P_ACTIVITY);
1239 END GET_ACTIVITY;
1240
1241 FUNCTION GET_CONSOLIDATION_SET(P_CONSOLIDATION_SET_ID IN NUMBER) RETURN VARCHAR2 IS
1242 X0 VARCHAR2(2000);
1243 BEGIN
1244 STPROC.INIT('begin :X0 := HR_US_REPORTS.GET_CONSOLIDATION_SET(:P_CONSOLIDATION_SET_ID); end;');
1245 STPROC.BIND_O(X0);
1246 STPROC.BIND_I(P_CONSOLIDATION_SET_ID);
1247 STPROC.EXECUTE;
1248 STPROC.RETRIEVE(1
1249 ,X0);
1250 RETURN X0;
1251 END GET_CONSOLIDATION_SET;
1252
1253 FUNCTION GET_PAYMENT_TYPE_NAME(P_PAYMENT_TYPE_ID IN NUMBER) RETURN VARCHAR2 IS
1254 X0 VARCHAR2(2000);
1255 BEGIN
1256 STPROC.INIT('begin :X0 := HR_US_REPORTS.GET_PAYMENT_TYPE_NAME(:P_PAYMENT_TYPE_ID); end;');
1257 STPROC.BIND_O(X0);
1258 STPROC.BIND_I(P_PAYMENT_TYPE_ID);
1259 STPROC.EXECUTE;
1260 STPROC.RETRIEVE(1
1261 ,X0);
1262 RETURN X0;
1263 END GET_PAYMENT_TYPE_NAME;
1264
1265 FUNCTION GET_ELEMENT_TYPE_NAME(P_ELEMENT_TYPE_ID IN NUMBER) RETURN VARCHAR2 IS
1266 X0 VARCHAR2(2000);
1267 BEGIN
1268 STPROC.INIT('begin :X0 := HR_US_REPORTS.GET_ELEMENT_TYPE_NAME(:P_ELEMENT_TYPE_ID); end;');
1269 STPROC.BIND_O(X0);
1270 STPROC.BIND_I(P_ELEMENT_TYPE_ID);
1271 STPROC.EXECUTE;
1272 STPROC.RETRIEVE(1
1273 ,X0);
1274 RETURN X0;
1275 END GET_ELEMENT_TYPE_NAME;
1276
1277 FUNCTION GET_TAX_UNIT(P_TAX_UNIT_ID IN NUMBER) RETURN VARCHAR2 IS
1278 X0 VARCHAR2(2000);
1279 BEGIN
1280 STPROC.INIT('begin :X0 := HR_US_REPORTS.GET_TAX_UNIT(:P_TAX_UNIT_ID); end;');
1281 STPROC.BIND_O(X0);
1282 STPROC.BIND_I(P_TAX_UNIT_ID);
1283 STPROC.EXECUTE;
1284 STPROC.RETRIEVE(1
1285 ,X0);
1286 RETURN X0;
1287 END GET_TAX_UNIT;
1288
1289 FUNCTION GET_PERSON_NAME(P_PERSON_ID IN NUMBER) RETURN VARCHAR2 IS
1290 X0 VARCHAR2(2000);
1291 BEGIN
1292 STPROC.INIT('begin :X0 := HR_US_REPORTS.GET_PERSON_NAME(:P_PERSON_ID); end;');
1293 STPROC.BIND_O(X0);
1294 STPROC.BIND_I(P_PERSON_ID);
1295 STPROC.EXECUTE;
1296 STPROC.RETRIEVE(1
1297 ,X0);
1298 RETURN X0;
1299 END GET_PERSON_NAME;
1300
1301 FUNCTION GET_PAYROLL_ACTION(P_PAYROLL_ACTION_ID IN NUMBER) RETURN VARCHAR2 IS
1302 X0 VARCHAR2(2000);
1303 BEGIN
1304 STPROC.INIT('begin :X0 := HR_US_REPORTS.GET_PAYROLL_ACTION(:P_PAYROLL_ACTION_ID); end;');
1305 STPROC.BIND_O(X0);
1306 STPROC.BIND_I(P_PAYROLL_ACTION_ID);
1307 STPROC.EXECUTE;
1308 STPROC.RETRIEVE(1
1309 ,X0);
1310 RETURN X0;
1311 END GET_PAYROLL_ACTION;
1312
1313 FUNCTION GET_LEGISLATION_CODE(P_BUSINESS_GROUP_ID IN NUMBER) RETURN VARCHAR2 IS
1314 X0 VARCHAR2(2000);
1315 BEGIN
1316 STPROC.INIT('begin :X0 := HR_US_REPORTS.GET_LEGISLATION_CODE(:P_BUSINESS_GROUP_ID); end;');
1317 STPROC.BIND_O(X0);
1318 STPROC.BIND_I(P_BUSINESS_GROUP_ID);
1319 STPROC.EXECUTE;
1320 STPROC.RETRIEVE(1
1321 ,X0);
1322 RETURN X0;
1323 END GET_LEGISLATION_CODE;
1324
1325 FUNCTION GET_DEFINED_BALANCE_ID(P_BALANCE_NAME IN VARCHAR2
1326 ,P_DIMENSION_SUFFIX IN VARCHAR2
1327 ,P_BUSINESS_GROUP_ID IN NUMBER) RETURN NUMBER IS
1328 X0 NUMBER;
1329 BEGIN
1330 STPROC.INIT('begin :X0 := HR_US_REPORTS.GET_DEFINED_BALANCE_ID(:P_BALANCE_NAME, :P_DIMENSION_SUFFIX, :P_BUSINESS_GROUP_ID); end;');
1331 STPROC.BIND_O(X0);
1332 STPROC.BIND_I(P_BALANCE_NAME);
1333 STPROC.BIND_I(P_DIMENSION_SUFFIX);
1334 STPROC.BIND_I(P_BUSINESS_GROUP_ID);
1335 STPROC.EXECUTE;
1336 STPROC.RETRIEVE(1
1337 ,X0);
1338 RETURN X0;
1339 END GET_DEFINED_BALANCE_ID;
1340
1341 FUNCTION GET_STARTUP_DEFINED_BALANCE(P_REPORTING_NAME IN VARCHAR2
1342 ,P_DIMENSION_SUFFIX IN VARCHAR2) RETURN NUMBER IS
1343 X0 NUMBER;
1344 BEGIN
1345 STPROC.INIT('begin :X0 := HR_US_REPORTS.GET_STARTUP_DEFINED_BALANCE(:P_REPORTING_NAME, :P_DIMENSION_SUFFIX); end;');
1346 STPROC.BIND_O(X0);
1347 STPROC.BIND_I(P_REPORTING_NAME);
1348 STPROC.BIND_I(P_DIMENSION_SUFFIX);
1349 STPROC.EXECUTE;
1350 STPROC.RETRIEVE(1
1351 ,X0);
1352 RETURN X0;
1353 END GET_STARTUP_DEFINED_BALANCE;
1354
1355 FUNCTION GET_DEFINED_BALANCE_BY_TYPE(P_BOX_NUM IN VARCHAR2
1356 ,P_DIMENSION_SUFFIX IN VARCHAR2) RETURN NUMBER IS
1357 X0 NUMBER;
1358 BEGIN
1359 STPROC.INIT('begin :X0 := HR_US_REPORTS.GET_DEFINED_BALANCE_BY_TYPE(:P_BOX_NUM, :P_DIMENSION_SUFFIX); end;');
1360 STPROC.BIND_O(X0);
1361 STPROC.BIND_I(P_BOX_NUM);
1362 STPROC.BIND_I(P_DIMENSION_SUFFIX);
1363 STPROC.EXECUTE;
1364 STPROC.RETRIEVE(1
1365 ,X0);
1366 RETURN X0;
1367 END GET_DEFINED_BALANCE_BY_TYPE;
1368
1369 FUNCTION GET_BEN_CLASS_NAME(P_SESSION_DATE IN DATE
1370 ,P_BENEFIT_CLASSIFICATION_ID IN NUMBER) RETURN VARCHAR2 IS
1371 X0 VARCHAR2(2000);
1372 BEGIN
1373 STPROC.INIT('begin :X0 := HR_US_REPORTS.GET_BEN_CLASS_NAME(:P_SESSION_DATE, :P_BENEFIT_CLASSIFICATION_ID); end;');
1374 STPROC.BIND_O(X0);
1375 STPROC.BIND_I(P_SESSION_DATE);
1376 STPROC.BIND_I(P_BENEFIT_CLASSIFICATION_ID);
1377 STPROC.EXECUTE;
1378 STPROC.RETRIEVE(1
1379 ,X0);
1380 RETURN X0;
1381 END GET_BEN_CLASS_NAME;
1382
1383 FUNCTION GET_COBRA_QUALIFYING_EVENT(P_QUALIFYING_EVENT IN VARCHAR2) RETURN VARCHAR2 IS
1384 X0 VARCHAR2(2000);
1385 BEGIN
1386 STPROC.INIT('begin :X0 := HR_US_REPORTS.GET_COBRA_QUALIFYING_EVENT(:P_QUALIFYING_EVENT); end;');
1387 STPROC.BIND_O(X0);
1388 STPROC.BIND_I(P_QUALIFYING_EVENT);
1389 STPROC.EXECUTE;
1390 STPROC.RETRIEVE(1
1391 ,X0);
1392 RETURN X0;
1393 END GET_COBRA_QUALIFYING_EVENT;
1394
1395 FUNCTION GET_COBRA_STATUS(P_COBRA_STATUS IN VARCHAR2) RETURN VARCHAR2 IS
1396 X0 VARCHAR2(2000);
1397 BEGIN
1398 STPROC.INIT('begin :X0 := HR_US_REPORTS.GET_COBRA_STATUS(:P_COBRA_STATUS); end;');
1399 STPROC.BIND_O(X0);
1400 STPROC.BIND_I(P_COBRA_STATUS);
1401 STPROC.EXECUTE;
1402 STPROC.RETRIEVE(1
1403 ,X0);
1404 RETURN X0;
1405 END GET_COBRA_STATUS;
1406
1407 FUNCTION GET_EST_TAX_UNIT(P_STARTING_ORG_ID IN NUMBER
1408 ,P_ORG_STRUCTURE_VERSION_ID IN NUMBER) RETURN NUMBER IS
1409 X0 NUMBER;
1410 BEGIN
1411 STPROC.INIT('begin :X0 := HR_US_REPORTS.GET_EST_TAX_UNIT(:P_STARTING_ORG_ID, :P_ORG_STRUCTURE_VERSION_ID); end;');
1412 STPROC.BIND_O(X0);
1413 STPROC.BIND_I(P_STARTING_ORG_ID);
1414 STPROC.BIND_I(P_ORG_STRUCTURE_VERSION_ID);
1415 STPROC.EXECUTE;
1416 STPROC.RETRIEVE(1
1417 ,X0);
1418 RETURN X0;
1419 END GET_EST_TAX_UNIT;
1420
1421 FUNCTION GET_ORG_HIERARCHY_NAME(P_ORG_STRUCTURE_VERSION_ID IN NUMBER) RETURN VARCHAR2 IS
1422 X0 VARCHAR2(2000);
1423 BEGIN
1424 STPROC.INIT('begin :X0 := HR_US_REPORTS.GET_ORG_HIERARCHY_NAME(:P_ORG_STRUCTURE_VERSION_ID); end;');
1425 STPROC.BIND_O(X0);
1426 STPROC.BIND_I(P_ORG_STRUCTURE_VERSION_ID);
1427 STPROC.EXECUTE;
1428 STPROC.RETRIEVE(1
1429 ,X0);
1430 RETURN X0;
1431 END GET_ORG_HIERARCHY_NAME;
1432
1433 FUNCTION GET_STATE_NAME(P_STATE_CODE IN VARCHAR2) RETURN VARCHAR2 IS
1434 X0 VARCHAR2(2000);
1435 BEGIN
1436 STPROC.INIT('begin :X0 := HR_US_REPORTS.GET_STATE_NAME(:P_STATE_CODE); end;');
1437 STPROC.BIND_O(X0);
1438 STPROC.BIND_I(P_STATE_CODE);
1439 STPROC.EXECUTE;
1440 STPROC.RETRIEVE(1
1441 ,X0);
1442 RETURN X0;
1443 END GET_STATE_NAME;
1444
1445 FUNCTION GET_ORG_NAME(P_ORGANIZATION_ID IN NUMBER
1446 ,P_BUSINESS_GROUP_ID IN NUMBER) RETURN VARCHAR2 IS
1447 X0 VARCHAR2(2000);
1448 BEGIN
1449 STPROC.INIT('begin :X0 := HR_US_REPORTS.GET_ORG_NAME(:P_ORGANIZATION_ID, :P_BUSINESS_GROUP_ID); end;');
1450 STPROC.BIND_O(X0);
1451 STPROC.BIND_I(P_ORGANIZATION_ID);
1452 STPROC.BIND_I(P_BUSINESS_GROUP_ID);
1453 STPROC.EXECUTE;
1454 STPROC.RETRIEVE(1
1455 ,X0);
1456 RETURN X0;
1457 END GET_ORG_NAME;
1458
1459 FUNCTION GET_CAREER_PATH_NAME(P_CAREER_PATH_ID IN NUMBER
1460 ,P_BUSINESS_GROUP_ID IN NUMBER) RETURN VARCHAR2 IS
1461 X0 VARCHAR2(2000);
1462 BEGIN
1463 STPROC.INIT('begin :X0 := HR_US_REPORTS.GET_CAREER_PATH_NAME(:P_CAREER_PATH_ID, :P_BUSINESS_GROUP_ID); end;');
1464 STPROC.BIND_O(X0);
1465 STPROC.BIND_I(P_CAREER_PATH_ID);
1466 STPROC.BIND_I(P_BUSINESS_GROUP_ID);
1467 STPROC.EXECUTE;
1468 STPROC.RETRIEVE(1
1469 ,X0);
1470 RETURN X0;
1471 END GET_CAREER_PATH_NAME;
1472
1473 FUNCTION GET_AAP_ORG_ID(P_AAP_NAME IN VARCHAR2
1474 ,P_BUSINESS_GROUP_ID IN NUMBER) RETURN NUMBER IS
1475 X0 NUMBER;
1476 BEGIN
1477 STPROC.INIT('begin :X0 := HR_US_REPORTS.GET_AAP_ORG_ID(:P_AAP_NAME, :P_BUSINESS_GROUP_ID); end;');
1478 STPROC.BIND_O(X0);
1479 STPROC.BIND_I(P_AAP_NAME);
1480 STPROC.BIND_I(P_BUSINESS_GROUP_ID);
1481 STPROC.EXECUTE;
1482 STPROC.RETRIEVE(1
1483 ,X0);
1484 RETURN X0;
1485 END GET_AAP_ORG_ID;
1486
1487 PROCEDURE GET_ADDRESS_31(P_LOCATION_ID IN NUMBER
1488 ,P_ADDRESS OUT NOCOPY VARCHAR2) IS
1489 BEGIN
1490 STPROC.INIT('begin HR_US_REPORTS.GET_ADDRESS_31(:P_LOCATION_ID, :P_ADDRESS); end;');
1491 STPROC.BIND_I(P_LOCATION_ID);
1492 STPROC.BIND_O(P_ADDRESS);
1493 STPROC.EXECUTE;
1494 STPROC.RETRIEVE(2
1495 ,P_ADDRESS);
1496 END GET_ADDRESS_31;
1497
1498 FUNCTION GET_LOCATION_CODE(P_LOCATION_ID IN NUMBER) RETURN VARCHAR2 IS
1499 X0 VARCHAR2(2000);
1500 BEGIN
1501 STPROC.INIT('begin :X0 := HR_US_REPORTS.GET_LOCATION_CODE(:P_LOCATION_ID); end;');
1502 STPROC.BIND_O(X0);
1503 STPROC.BIND_I(P_LOCATION_ID);
1504 STPROC.EXECUTE;
1505 STPROC.RETRIEVE(1
1506 ,X0);
1507 RETURN X0;
1508 END GET_LOCATION_CODE;
1509
1510 PROCEDURE GET_ADDRESS_3LINES(P_PERSON_ID IN NUMBER
1511 ,P_EFFECTIVE_DATE IN DATE
1512 ,P_ADDR_LINE1 OUT NOCOPY VARCHAR2
1513 ,P_ADDR_LINE2 OUT NOCOPY VARCHAR2
1514 ,P_CITY_STATE_ZIP OUT NOCOPY VARCHAR2) IS
1515 BEGIN
1516 STPROC.INIT('begin HR_US_REPORTS.GET_ADDRESS_3LINES(:P_PERSON_ID, :P_EFFECTIVE_DATE, :P_ADDR_LINE1, :P_ADDR_LINE2, :P_CITY_STATE_ZIP); end;');
1517 STPROC.BIND_I(P_PERSON_ID);
1518 STPROC.BIND_I(P_EFFECTIVE_DATE);
1519 STPROC.BIND_O(P_ADDR_LINE1);
1520 STPROC.BIND_O(P_ADDR_LINE2);
1521 STPROC.BIND_O(P_CITY_STATE_ZIP);
1522 STPROC.EXECUTE;
1523 STPROC.RETRIEVE(3
1524 ,P_ADDR_LINE1);
1525 STPROC.RETRIEVE(4
1526 ,P_ADDR_LINE2);
1527 STPROC.RETRIEVE(5
1528 ,P_CITY_STATE_ZIP);
1529 END GET_ADDRESS_3LINES;
1530 */
1531
1532 FUNCTION SALARY_RANGE(P_EMP_CATEGORY IN VARCHAR2
1533 ,P_SALARY IN NUMBER) RETURN NUMBER IS
1534 BEGIN
1535 IF P_EMP_CATEGORY in ('FT','FR') THEN
1536 IF P_SALARY BETWEEN 0 AND 15999 THEN
1537 RETURN 1;
1538 ELSIF P_SALARY BETWEEN 16000 AND 19999 THEN
1539 RETURN 2;
1540 ELSIF P_SALARY BETWEEN 20000 AND 2499 THEN
1541 RETURN 3;
1542 ELSIF P_SALARY BETWEEN 25000 AND 32999 THEN
1543 RETURN 4;
1544 ELSIF P_SALARY BETWEEN 33000 AND 42999 THEN
1545 RETURN 5;
1546 ELSIF P_SALARY BETWEEN 43000 AND 54999 THEN
1547 RETURN 6;
1548 ELSIF P_SALARY BETWEEN 55000 AND 69999 THEN
1549 RETURN 7;
1550 ELSIF P_SALARY > 70000 THEN
1551 RETURN 8;
1552 END IF;
1553 ELSE
1554 RETURN 0;
1555 END IF;
1556 END SALARY_RANGE;
1557
1558 FUNCTION EMPLOYMENT_CATEGORY(P_EMP_CATEGORY IN VARCHAR2
1559 ,P_HIRE_DATE IN DATE) RETURN VARCHAR2 IS
1560 L_REPORT_DATE DATE := TO_DATE('01-10' || TO_CHAR(SYSDATE
1561 ,'YYYY')
1562 ,'DD-MM-YYYY');
1563 BEGIN
1564 IF P_EMP_CATEGORY in ('FT','FR') THEN
1565 IF P_HIRE_DATE BETWEEN TRUNC(L_REPORT_DATE - 364
1566 ,'MON') + 1 AND L_REPORT_DATE THEN
1567 RETURN 'NEW HIRES DURING FISCAL YEAR - PERMANENT FULL TIME ONLY';
1568 ELSE
1569 RETURN 'FULL-TIME EMPLOYEES';
1570 END IF;
1571 ELSE
1572 RETURN 'OTHER THAN FULL-TIME EMPLOYEES';
1573 END IF;
1574 END EMPLOYMENT_CATEGORY;
1575
1576 END PQH_PQHEEO5_XMLP_PKG;