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