[Home] [Help]
PACKAGE BODY: APPS.PAY_PAYRP940_XMLP_PKG
Source
1 PACKAGE BODY PAY_PAYRP940_XMLP_PKG AS
2 /* $Header: PAYUS940B.pls 120.1 2008/03/18 06:27:35 srikrish noship $ */
3 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
4 BEGIN
5 -- HR_STANDARD.EVENT('BEFORE REPORT');
6 C_GAZ_START_DATE := TO_DATE('01-01-' || P_TAX_YEAR
7 ,'DD-MM-YYYY');
8 C_BUSINESS_GROUP_NAME := HR_REPORTS.GET_BUSINESS_GROUP(P_BUSINESS_GROUP_ID);
9 IF P_TAX_UNIT_ID IS NOT NULL THEN
10 C_TAX_UNIT := HR_US_REPORTS.GET_ORG_NAME(P_TAX_UNIT_ID
11 ,P_BUSINESS_GROUP_ID);
12 END IF;
13 SELECT
14 DECODE(COUNT(PAA.ASSIGNMENT_ID)
15 ,0
16 ,'F'
17 ,'T')
18 INTO P_FLAG
19 FROM
20 PAY_ASSIGNMENT_ACTIONS PAA,
21 PAY_PAYROLL_ACTIONS PACT
22 WHERE PACT.EFFECTIVE_DATE <= TO_DATE('31-12-' || P_TAX_YEAR
23 ,'DD-MM-YYYY')
24 AND PACT.EFFECTIVE_DATE >= TO_DATE('01-01-' || P_TAX_YEAR
25 ,'DD-MM-YYYY')
26 AND PACT.BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID
27 AND PACT.PAYROLL_ACTION_ID = PAA.PAYROLL_ACTION_ID
28 AND PACT.ACTION_TYPE in ( 'R' , 'Q' , 'I' , 'V' , 'B' )
29 AND PAA.TAX_UNIT_ID = P_TAX_UNIT_ID;
30 RETURN (TRUE);
31 END BEFOREREPORT;
32
33 FUNCTION C_TRACEFORMULA RETURN VARCHAR2 IS
34 BEGIN
35 IF P_TRACE = 'Y' THEN
36 EXECUTE IMMEDIATE
37 'ALTER SESSION SET SQL_TRACE=TRUE';
38 END IF;
39 RETURN NULL;
40 END C_TRACEFORMULA;
41
42 FUNCTION C_STATE_EINFORMULA(TAX_UNIT_ID IN NUMBER
43 ,STATE_CODE IN VARCHAR2) RETURN VARCHAR2 IS
44 BEGIN
45 DECLARE
46 CURSOR MY_CURSOR IS
47 SELECT
48 ORG_INFORMATION2
49 FROM
50 HR_ORGANIZATION_INFORMATION
51 WHERE ORGANIZATION_ID = TAX_UNIT_ID
52 AND ORG_INFORMATION_CONTEXT = 'State Tax Rules'
53 AND ORG_INFORMATION1 = STATE_CODE;
54 STATE_ID VARCHAR2(25);
55 BEGIN
56 OPEN MY_CURSOR;
57 FETCH MY_CURSOR
58 INTO STATE_ID;
59 CLOSE MY_CURSOR;
60 IF STATE_ID IS NOT NULL THEN
61 RETURN (STATE_ID);
62 ELSE
63 RETURN ('NO STATE EIN');
64 END IF;
65 END;
66 RETURN NULL;
67 END C_STATE_EINFORMULA;
68
69 FUNCTION C_TOTAL_PAYMENTSFORMULA(TAX_UNIT_ID IN NUMBER
70 ,C_GAZ_END_DATE IN DATE) RETURN NUMBER IS
71 BEGIN
72 DECLARE
73 CURSOR C_GET_FED_VALUES(P_TAX_UNIT_ID IN NUMBER) IS
74 SELECT
75 D_TAX_OTD_VALUE,
76 D_WAGE_OTD_VALUE,
77 D_FORMAT_CODE
78 FROM
79 PAY_US_FEDERAL_TAX_BAL_GRE_V
80 WHERE D_BALANCE_SET_NAME = '940_FED_YTD'
81 AND D_TAX_UNIT_ID = P_TAX_UNIT_ID;
82 L_REDUCED_SUBJ_WHABLE NUMBER;
83 L_GROSS_EARNINGS NUMBER;
84 L_REGULAR_EARNINGS NUMBER;
85 L_PRE_TAX NUMBER;
86 L_FUTA_TAXABLE NUMBER;
87 L_VALUE2 NUMBER;
88 L_VALUE3 NUMBER;
89 L_VALUE4 NUMBER;
90 L_VALUE7 NUMBER;
91 L_VALUE8 NUMBER;
92 L_VALUE9 NUMBER;
93 L_VALUE10 NUMBER;
94 LL_VALUE5 NUMBER;
95 L_SUPP_EARN NUMBER;
96 L_PRE_TAX_FUTA NUMBER;
97 L_FORMAT_CODE VARCHAR2(1);
98 L_TAX_OTD_VALUE NUMBER;
99 L_WAGE_OTD_VALUE NUMBER;
100 BEGIN
101 IF CP_940_FED_STATUS <> 'Y' THEN
102 PAY_US_TAXBAL_VIEW_PKG.US_GP_MULTIPLE_GRE_YTD(P_TAX_UNIT_ID => TAX_UNIT_ID
103 ,P_EFFECTIVE_DATE => C_GAZ_END_DATE
104 ,P_BALANCE_NAME1 => 'Gross Earnings'
105 ,P_BALANCE_NAME2 => NULL
106 ,P_BALANCE_NAME3 => 'Regular Earnings'
107 ,P_BALANCE_NAME4 => NULL
108 ,P_BALANCE_NAME5 => 'Pre Tax Deductions'
109 ,P_BALANCE_NAME6 => 'FUTA Taxable'
110 ,P_BALANCE_NAME7 => NULL
111 ,P_BALANCE_NAME8 => NULL
112 ,P_BALANCE_NAME9 => NULL
113 ,P_BALANCE_NAME10 => NULL
114 ,P_VALUE1 => L_GROSS_EARNINGS
115 ,P_VALUE2 => L_VALUE2
116 ,P_VALUE3 => L_REGULAR_EARNINGS
117 ,P_VALUE4 => L_VALUE4
118 ,P_VALUE5 => L_PRE_TAX
119 ,P_VALUE6 => L_FUTA_TAXABLE
120 ,P_VALUE7 => L_VALUE7
121 ,P_VALUE8 => L_VALUE8
122 ,P_VALUE9 => L_VALUE9
123 ,P_VALUE10 => L_VALUE10);
124 PAY_US_TAXBAL_VIEW_PKG.US_GP_SUBJECT_TO_TAX_GRE_YTD(P_BALANCE_NAME1 => 'Supplemental Earnings for FUTA'
125 ,P_BALANCE_NAME2 => NULL
126 ,P_BALANCE_NAME3 => NULL
127 ,P_BALANCE_NAME4 => 'Pre Tax Deductions for FUTA'
128 ,P_BALANCE_NAME5 => NULL
129 ,P_EFFECTIVE_DATE => C_GAZ_END_DATE
130 ,P_TAX_UNIT_ID => TAX_UNIT_ID
131 ,P_VALUE1 => L_SUPP_EARN
132 ,P_VALUE2 => L_VALUE2
133 ,P_VALUE3 => L_VALUE3
134 ,P_VALUE4 => L_PRE_TAX_FUTA
135 ,P_VALUE5 => LL_VALUE5);
136 L_REDUCED_SUBJ_WHABLE := (L_REGULAR_EARNINGS + L_SUPP_EARN) - (L_PRE_TAX - L_PRE_TAX_FUTA);
137 ELSE
138 OPEN C_GET_FED_VALUES(TAX_UNIT_ID);
139 LOOP
140 FETCH C_GET_FED_VALUES
141 INTO L_TAX_OTD_VALUE,L_WAGE_OTD_VALUE,L_FORMAT_CODE;
142 EXIT WHEN C_GET_FED_VALUES%NOTFOUND;
143 IF L_FORMAT_CODE = '1' THEN
144 L_GROSS_EARNINGS := L_WAGE_OTD_VALUE;
145 ELSIF L_FORMAT_CODE = '2' THEN
146 L_FUTA_TAXABLE := L_TAX_OTD_VALUE;
147 L_REDUCED_SUBJ_WHABLE := L_WAGE_OTD_VALUE;
148 ELSE
149 L_REDUCED_SUBJ_WHABLE := 0;
150 L_FUTA_TAXABLE := 0;
151 L_GROSS_EARNINGS := 0;
152 END IF;
153 END LOOP;
154 CLOSE C_GET_FED_VALUES;
155 END IF;
156 CP_EXEMPT_PAYMENTS := L_GROSS_EARNINGS - L_REDUCED_SUBJ_WHABLE;
157 IF CP_EXEMPT_PAYMENTS = 0 THEN
158 CP_EXEMPT_PAYMENTS := NULL;
159 END IF;
160 IF CP_EXEMPT_PAYMENTS < 0 THEN
161 /*SRW.MESSAGE('001'
162 ,'Tax Unit ID: ' || TO_CHAR(TAX_UNIT_ID) || ' has negative Exempt Payments. Please check.')*/NULL;
163 END IF;
164 CP_EXCESS_PAYMENTS := L_REDUCED_SUBJ_WHABLE - L_FUTA_TAXABLE;
165 IF CP_EXCESS_PAYMENTS = 0 THEN
166 CP_EXCESS_PAYMENTS := NULL;
167 END IF;
168 IF CP_EXCESS_PAYMENTS < 0 THEN
169 /*SRW.MESSAGE('001'
170 ,'Tax Unit ID: ' || TO_CHAR(TAX_UNIT_ID) || ' has negative Excess Payments. Please check.')*/NULL;
171 END IF;
172 IF L_GROSS_EARNINGS < 0 THEN
173 /*SRW.MESSAGE('001'
174 ,'Tax Unit ID: ' || TO_CHAR(TAX_UNIT_ID) || ' has negative Total Payments. Please check.')*/NULL;
175 END IF;
176 IF P_DEBUG = 'Y' THEN
177 /*SRW.MESSAGE('789'
178 ,'Total Payments Bal Result' || TO_CHAR(L_GROSS_EARNINGS))*/NULL;
179 END IF;
180 RETURN (L_GROSS_EARNINGS);
181 EXCEPTION
182 WHEN NO_DATA_FOUND THEN
183 /*SRW.MESSAGE('001'
184 ,'No data found in ' || TO_CHAR(TAX_UNIT_ID) || ' for Total Payments')*/NULL;
185 END;
186 RETURN NULL;
187 END C_TOTAL_PAYMENTSFORMULA;
188
189 FUNCTION C_GROSS_FUTA_TAXFORMULA(C_TOTAL_TAXABLE_WAGES IN NUMBER
190 ,TAX_UNIT_ID IN NUMBER) RETURN NUMBER IS
191 BEGIN
192 DECLARE
193 L_BAL_RESULT NUMBER;
194 BEGIN
195 L_BAL_RESULT := NVL(C_TOTAL_TAXABLE_WAGES
196 ,0) * 0.062;
197 IF L_BAL_RESULT = 0 THEN
198 L_BAL_RESULT := NULL;
199 END IF;
200 IF L_BAL_RESULT < 0 THEN
201 /*SRW.MESSAGE('001'
202 ,'Tax Unit ID: ' || TO_CHAR(TAX_UNIT_ID) || ' has negative Gross FUTA Tax. Please check.')*/NULL;
203 END IF;
204 IF P_DEBUG = 'Y' THEN
205 /*SRW.MESSAGE('789'
206 ,'Gross FUTA Tax Bal Result' || TO_CHAR(L_BAL_RESULT))*/NULL;
207 END IF;
208 RETURN (L_BAL_RESULT);
209 END;
210 RETURN NULL;
211 END C_GROSS_FUTA_TAXFORMULA;
212
213 FUNCTION C_MAXIMUM_CREDITFORMULA(C_TOTAL_TAXABLE_WAGES IN NUMBER
214 ,TAX_UNIT_ID IN NUMBER) RETURN NUMBER IS
215 BEGIN
216 DECLARE
217 L_BAL_RESULT NUMBER;
218 BEGIN
219 L_BAL_RESULT := NVL(C_TOTAL_TAXABLE_WAGES
220 ,0) * 0.054;
221 IF L_BAL_RESULT = 0 THEN
222 L_BAL_RESULT := NULL;
223 END IF;
224 IF L_BAL_RESULT < 0 THEN
225 /*SRW.MESSAGE('001'
226 ,'Tax Unit ID: ' || TO_CHAR(TAX_UNIT_ID) || ' has negative Maximum Credit. Please check.')*/NULL;
227 END IF;
228 IF P_DEBUG = 'Y' THEN
229 /*SRW.MESSAGE('789'
230 ,'Maximum Credit Bal Result' || TO_CHAR(L_BAL_RESULT))*/NULL;
231 END IF;
232 RETURN (L_BAL_RESULT);
233 END;
234 RETURN NULL;
235 END C_MAXIMUM_CREDITFORMULA;
236
237 FUNCTION C_TAXABLE_PAYROLLFORMULA(C_GAZ_END_DATE IN DATE
238 ,TAX_UNIT_ID IN NUMBER
239 ,JURISDICTION_CODE IN VARCHAR2
240 ,VALUE IN NUMBER
241 ,STATE_CODE IN VARCHAR2) RETURN NUMBER IS
242 BEGIN
243 DECLARE
244 L_BAL_RESULT NUMBER;
245 L_NY_BAL_RESULT NUMBER;
246 L_VALUE2 NUMBER;
247 L_VALUE3 NUMBER;
248 L_VALUE4 NUMBER;
249 L_VALUE5 NUMBER;
250 L_VALUE6 NUMBER;
251 L_VALUE7 NUMBER;
252 BEGIN
253 IF CP_940_STATE_STATUS <> 'Y' THEN
254 PAY_US_TAXBAL_VIEW_PKG.US_GP_GRE_JD_YTD(P_BALANCE_NAME1 => 'SUI ER Taxable'
255 ,P_BALANCE_NAME2 => NULL
256 ,P_BALANCE_NAME3 => NULL
257 ,P_BALANCE_NAME4 => NULL
258 ,P_BALANCE_NAME5 => NULL
259 ,P_BALANCE_NAME6 => NULL
260 ,P_BALANCE_NAME7 => NULL
261 ,P_EFFECTIVE_DATE => C_GAZ_END_DATE
262 ,P_TAX_UNIT_ID => TAX_UNIT_ID
263 ,P_STATE_CODE => JURISDICTION_CODE
264 ,P_VALUE1 => L_BAL_RESULT
265 ,P_VALUE2 => L_VALUE2
266 ,P_VALUE3 => L_VALUE3
267 ,P_VALUE4 => L_VALUE4
268 ,P_VALUE5 => L_VALUE5
269 ,P_VALUE6 => L_VALUE6
270 ,P_VALUE7 => L_VALUE7);
271 /*SRW.MESSAGE('777'
272 ,'Balances are not valid. ')*/NULL;
273 ELSE
274 L_BAL_RESULT := VALUE;
275 END IF;
276 IF L_BAL_RESULT < 0 THEN
277 /*SRW.MESSAGE('001'
278 ,'Tax Unit ID: ' || TO_CHAR(TAX_UNIT_ID) || ' in ' || STATE_CODE || ' has negative Total Payroll. Please check.')*/NULL;
279 END IF;
280 IF P_DEBUG = 'Y' THEN
281 /*SRW.MESSAGE('789'
282 ,'Taxable Payroll Bal Result' || TO_CHAR(L_BAL_RESULT))*/NULL;
283 END IF;
284 RETURN (L_BAL_RESULT);
285 EXCEPTION
286 WHEN NO_DATA_FOUND THEN
287 /*SRW.MESSAGE('789'
288 ,'No data found in ' || TO_CHAR(TAX_UNIT_ID) || ' for Taxable Payroll')*/NULL;
289 END;
290 RETURN NULL;
291 END C_TAXABLE_PAYROLLFORMULA;
292
293 FUNCTION C_TOTAL_EXEMPT_PAYMENTSFORMULA(TAX_UNIT_ID IN NUMBER) RETURN NUMBER IS
294 BEGIN
295 DECLARE
296 L_BAL_RESULT NUMBER;
297 BEGIN
298 L_BAL_RESULT := NVL(CP_EXEMPT_PAYMENTS
299 ,0) + NVL(CP_EXCESS_PAYMENTS
300 ,0);
301 IF L_BAL_RESULT = 0 THEN
302 L_BAL_RESULT := NULL;
303 END IF;
304 IF L_BAL_RESULT < 0 THEN
305 /*SRW.MESSAGE('001'
306 ,'Tax Unit ID: ' || TO_CHAR(TAX_UNIT_ID) || ' has negative Total Exempt Payments. Please check.')*/NULL;
307 END IF;
308 IF P_DEBUG = 'Y' THEN
309 /*SRW.MESSAGE('789'
310 ,'Total Exempt Payments Bal Result' || TO_CHAR(L_BAL_RESULT))*/NULL;
311 END IF;
312 RETURN (L_BAL_RESULT);
313 END;
314 RETURN NULL;
315 END C_TOTAL_EXEMPT_PAYMENTSFORMULA;
316
317 FUNCTION C_TOTAL_TAXABLE_WAGESFORMULA(C_TOTAL_PAYMENTS IN NUMBER
318 ,C_TOTAL_EXEMPT_PAYMENTS IN NUMBER
319 ,TAX_UNIT_ID IN NUMBER) RETURN NUMBER IS
320 BEGIN
321 DECLARE
322 L_BAL_RESULT NUMBER;
323 BEGIN
324 L_BAL_RESULT := NVL(C_TOTAL_PAYMENTS
325 ,0) - NVL(C_TOTAL_EXEMPT_PAYMENTS
326 ,0);
327 IF L_BAL_RESULT = 0 THEN
328 L_BAL_RESULT := NULL;
329 END IF;
330 IF L_BAL_RESULT < 0 THEN
331 /*SRW.MESSAGE('001'
332 ,'Tax Unit ID: ' || TO_CHAR(TAX_UNIT_ID) || ' has negative Total Taxable Wages. Please check.')*/NULL;
333 END IF;
334 IF P_DEBUG = 'Y' THEN
335 /*SRW.MESSAGE('789'
336 ,'Total Taxable Wages Bal Result' || TO_CHAR(L_BAL_RESULT))*/NULL;
337 END IF;
338 RETURN (L_BAL_RESULT);
339 END;
340 RETURN NULL;
341 END C_TOTAL_TAXABLE_WAGESFORMULA;
342
343 FUNCTION C_EXPERIENCE_RATE_1FORMULA(TAX_UNIT_ID IN NUMBER
344 ,STATE_CODE IN VARCHAR2) RETURN VARCHAR2 IS
345 BEGIN
346 DECLARE
347 CURSOR MY_CURSOR IS
348 SELECT
349 ORG_INFORMATION6,
350 ORG_INFORMATION7
351 FROM
352 HR_ORGANIZATION_INFORMATION
353 WHERE ORGANIZATION_ID = TAX_UNIT_ID
354 AND ORG_INFORMATION_CONTEXT = 'State Tax Rules'
355 AND ORG_INFORMATION1 = STATE_CODE;
356 EXP_RATE_1 VARCHAR2(25);
357 EXP_RATE_2 VARCHAR2(25);
358 BEGIN
359 OPEN MY_CURSOR;
360 FETCH MY_CURSOR
361 INTO EXP_RATE_1,EXP_RATE_2;
362 CLOSE MY_CURSOR;
363 C_EXPERIENCE_RATE2 := EXP_RATE_2;
364 IF EXP_RATE_1 IS NOT NULL THEN
365 RETURN (EXP_RATE_1);
366 ELSE
367 RETURN ('N/A');
368 END IF;
369 EXCEPTION
370 WHEN NO_DATA_FOUND THEN
371 /*SRW.MESSAGE('789'
372 ,'No data found in ' || TO_CHAR(TAX_UNIT_ID) || ' for Experience Rate 1')*/NULL;
373 END;
374 RETURN NULL;
375 END C_EXPERIENCE_RATE_1FORMULA;
376
377 FUNCTION C_CONTRIBUTIONSFORMULA(C_TAXABLE_PAYROLL IN NUMBER) RETURN NUMBER IS
378 BEGIN
379 DECLARE
380 L_BAL_RESULT NUMBER;
381 BEGIN
382 L_BAL_RESULT := NVL(C_TAXABLE_PAYROLL
383 ,0) * 0.054;
384 IF P_DEBUG = 'Y' THEN
385 /*SRW.MESSAGE('789'
386 ,'Contributions Bal Result' || TO_CHAR(L_BAL_RESULT))*/NULL;
387 END IF;
388 RETURN (L_BAL_RESULT);
389 END;
390 RETURN NULL;
391 END C_CONTRIBUTIONSFORMULA;
392
393 FUNCTION C_CONTRIBUTIONS_PAYABLEFORMULA(C_EXPERIENCE_RATE_1 IN VARCHAR2
394 ,C_TAXABLE_PAYROLL IN NUMBER) RETURN NUMBER IS
395 BEGIN
396 DECLARE
397 L_BAL_RESULT NUMBER;
398 BEGIN
399 IF C_EXPERIENCE_RATE_1 <> 'N/A' THEN
400 IF C_EXPERIENCE_RATE_1 > 0 THEN
401 L_BAL_RESULT := NVL(C_TAXABLE_PAYROLL
402 ,0) * (NVL(C_EXPERIENCE_RATE_1
403 ,0) * 0.01);
404 ELSE
405 L_BAL_RESULT := 0;
406 END IF;
407 ELSE
408 L_BAL_RESULT := 0;
409 END IF;
410 IF P_DEBUG = 'Y' THEN
411 /*SRW.MESSAGE('789'
412 ,'Contributions Payable Bal Result' || TO_CHAR(L_BAL_RESULT))*/NULL;
413 END IF;
414 RETURN (L_BAL_RESULT);
415 END;
416 RETURN NULL;
417 END C_CONTRIBUTIONS_PAYABLEFORMULA;
418
419 FUNCTION C_ADDITIONAL_CREDITFORMULA(C_TAXABLE_PAYROLL IN NUMBER
420 ,CF_COMPUTATION_RATE IN NUMBER) RETURN NUMBER IS
421 L_BAL_RESULT NUMBER;
422 BEGIN
423 L_BAL_RESULT := NVL(C_TAXABLE_PAYROLL * CF_COMPUTATION_RATE
424 ,0);
425 IF P_DEBUG = 'Y' THEN
426 /*SRW.MESSAGE('789'
427 ,'Contributions Payable Bal Result' || TO_CHAR(L_BAL_RESULT))*/NULL;
428 END IF;
429 RETURN (L_BAL_RESULT);
430 END C_ADDITIONAL_CREDITFORMULA;
431
432 FUNCTION C_GAZ_END_DATEFORMULA RETURN DATE IS
433 BEGIN
434 DECLARE
435 L_END_DATE DATE;
436 BEGIN
437 L_END_DATE := TO_DATE('31-12-' || P_TAX_YEAR
438 ,'DD-MM-YYYY');
439 RETURN (L_END_DATE);
440 END;
441 RETURN NULL;
442 END C_GAZ_END_DATEFORMULA;
443
444 FUNCTION BEFOREPFORM RETURN BOOLEAN IS
445 BEGIN
446 RETURN (TRUE);
447 END BEFOREPFORM;
448
449 FUNCTION CP_EXEMPT_PAYMENTSFORMULA RETURN NUMBER IS
450 BEGIN
451 RETURN NULL;
452 END CP_EXEMPT_PAYMENTSFORMULA;
453
454 FUNCTION CP_EXCESS_PAYMENTSFORMULA RETURN NUMBER IS
455 BEGIN
456 RETURN NULL;
457 END CP_EXCESS_PAYMENTSFORMULA;
458
459 FUNCTION AFTERPFORM RETURN BOOLEAN IS
460 L_START_DATE DATE;
461 L_END_DATE DATE;
462 L_STATUS VARCHAR2(1);
463 BEGIN
464 L_START_DATE := TO_DATE('01-01-' || P_TAX_YEAR
465 ,'DD-MM-YYYY');
466 L_END_DATE := TO_DATE('31-12-' || P_TAX_YEAR
467 ,'DD-MM-YYYY');
468 CP_940_FED_STATUS := PAY_US_PAYROLL_UTILS.CHECK_BALANCE_STATUS(L_START_DATE
469 ,P_BUSINESS_GROUP_ID
470 ,'940_FED');
471 CP_940_STATE_STATUS := PAY_US_PAYROLL_UTILS.CHECK_BALANCE_STATUS(L_START_DATE
472 ,P_BUSINESS_GROUP_ID
473 ,'940_STATE');
474 IF CP_940_STATE_STATUS = 'Y' OR CP_940_FED_STATUS = 'Y' THEN
475 PAY_BALANCE_PKG.SET_CONTEXT('BALANCE_DATE'
476 ,FND_DATE.DATE_TO_CANONICAL(L_END_DATE));
477 PAY_BALANCE_PKG.SET_CONTEXT('DATE_EARNED'
478 ,FND_DATE.DATE_TO_CANONICAL(L_END_DATE));
479 PAY_US_BALANCE_VIEW_PKG.SET_SESSION_VAR('GROUP_RB_REPORT'
480 ,'TRUE');
481 PAY_US_BALANCE_VIEW_PKG.SET_SESSION_VAR('GROUP_RB_SDATE'
482 ,L_START_DATE);
483 PAY_US_BALANCE_VIEW_PKG.SET_SESSION_VAR('GROUP_RB_EDATE'
484 ,L_END_DATE);
485 END IF;
486 CP_SELECT_COL1 := NULL;
487 CP_SELECT_COL2 := NULL;
488 CP_WHERE_CLAUSE := ' ';
489 CP_FROM_TABLE := ' ';
490 IF CP_940_STATE_STATUS = 'Y' THEN
491 CP_SELECT_COL1 := 'v.d_wage_otd_value ';
492 CP_SELECT_COL2 := 'v.d_tax_unit_id ';
493 CP_WHERE_CLAUSE := CP_WHERE_CLAUSE || 'and psr.jurisdiction_code = v.d_state_code ' || ' ';
494 CP_WHERE_CLAUSE := CP_WHERE_CLAUSE || 'and v.d_balance_set_name = ''940_STATE_YTD'' ' || ' ';
495 CP_WHERE_CLAUSE := CP_WHERE_CLAUSE || 'and v.d_tax_type = ''SUI'' ';
496 CP_FROM_TABLE := 'pay_us_state_tax_bal_gre_v';
497 ELSE
498 CP_SELECT_COL1 := '1 ';
499 CP_SELECT_COL2 := 'v.tax_unit_id ';
500 CP_WHERE_CLAUSE := CP_WHERE_CLAUSE || 'and psr.jurisdiction_code = v.state_code ';
501 CP_FROM_TABLE := 'pay_us_tax_unit_states_v ';
502 END IF;
503 IF P_TAX_YEAR = '2005' THEN
504 CP_NY_REDUCTION_RATE := 0.006;
505 ELSIF P_TAX_YEAR = '2004' THEN
506 CP_NY_REDUCTION_RATE := 0.003;
507 ELSE
508 CP_NY_REDUCTION_RATE := 0.0;
509 END IF;
510 RETURN (TRUE);
511 END AFTERPFORM;
512
513 FUNCTION AFTERREPORT RETURN BOOLEAN IS
514 BEGIN
515 -- HR_STANDARD.EVENT('AFTER REPORT');
516 RETURN (TRUE);
517 END AFTERREPORT;
518
519 FUNCTION C_NY_WAGESFORMULA(TAX_UNIT_ID IN NUMBER
520 ,C_GAZ_END_DATE IN DATE) RETURN NUMBER IS
521 L_NY_VALUE NUMBER;
522 L_STATE_CODE VARCHAR2(2);
523 L_VALUE2 NUMBER;
524 L_VALUE3 NUMBER;
525 L_VALUE4 NUMBER;
526 L_VALUE5 NUMBER;
527 L_VALUE6 NUMBER;
528 L_VALUE7 NUMBER;
529 L_TEMP NUMBER;
530 CURSOR GET_NY_WAGE IS
531 SELECT
532 DISTINCT
533 SUBSTR(D_STATE_CODE
534 ,1
535 ,2),
536 D_WAGE_OTD_VALUE
537 FROM
538 PAY_US_STATE_TAX_BAL_GRE_V
539 WHERE D_STATE_CODE = '33-000-0000'
540 AND D_BALANCE_SET_NAME = '940_STATE_YTD'
541 AND D_TAX_TYPE = 'FUTA'
542 AND D_TAX_UNIT_ID = TAX_UNIT_ID;
543 BEGIN
544 IF NVL(P_STATE_CODE
545 ,'NY') = 'NY' THEN
546 IF CP_940_STATE_STATUS <> 'Y' THEN
547 PAY_US_TAXBAL_VIEW_PKG.US_GP_GRE_JD_YTD(P_BALANCE_NAME1 => 'FUTA Taxable'
548 ,P_BALANCE_NAME2 => NULL
549 ,P_BALANCE_NAME3 => NULL
550 ,P_BALANCE_NAME4 => NULL
551 ,P_BALANCE_NAME5 => NULL
552 ,P_BALANCE_NAME6 => NULL
553 ,P_BALANCE_NAME7 => NULL
554 ,P_EFFECTIVE_DATE => C_GAZ_END_DATE
555 ,P_TAX_UNIT_ID => TAX_UNIT_ID
556 ,P_STATE_CODE => '33-000-0000'
557 ,P_VALUE1 => L_NY_VALUE
558 ,P_VALUE2 => L_VALUE2
559 ,P_VALUE3 => L_VALUE3
560 ,P_VALUE4 => L_VALUE4
561 ,P_VALUE5 => L_VALUE5
562 ,P_VALUE6 => L_VALUE6
563 ,P_VALUE7 => L_VALUE7);
564 ELSE
565 OPEN GET_NY_WAGE;
566 FETCH GET_NY_WAGE
567 INTO L_STATE_CODE,L_NY_VALUE;
568 IF GET_NY_WAGE%NOTFOUND THEN
569 CLOSE GET_NY_WAGE;
570 RETURN 0;
571 END IF;
572 CLOSE GET_NY_WAGE;
573 END IF;
574 RETURN L_NY_VALUE;
575 ELSE
576 RETURN 0;
577 END IF;
578 END C_NY_WAGESFORMULA;
579
580 FUNCTION C_NY_RATEFORMULA(C_NY_WAGES IN NUMBER) RETURN NUMBER IS
581 BEGIN
582 IF C_NY_WAGES <> 0 THEN
583 RETURN C_NY_WAGES * CP_NY_REDUCTION_RATE;
584 ELSE
585 RETURN 0;
586 END IF;
587 END C_NY_RATEFORMULA;
588
589 FUNCTION CF_FUTA_TAX_BEF_ADJFORMULA(C_TOTAL_TAXABLE_WAGES IN NUMBER) RETURN NUMBER IS
590 BEGIN
591 RETURN (C_TOTAL_TAXABLE_WAGES * .008);
592 END CF_FUTA_TAX_BEF_ADJFORMULA;
593
594 FUNCTION CF_COMPUTATION_RATEFORMULA(C_EXPERIENCE_RATE_1 IN VARCHAR2) RETURN NUMBER IS
595 L_EXP_RATE NUMBER;
596 L_COMP_RATE NUMBER;
597 BEGIN
598 IF C_EXPERIENCE_RATE_1 <> 'N/A' THEN
599 L_EXP_RATE := TO_NUMBER(C_EXPERIENCE_RATE_1) * .01;
600 IF L_EXP_RATE < .054 THEN
601 L_COMP_RATE := 0.054 - L_EXP_RATE;
602 IF P_DEBUG = 'Y' THEN
603 /*SRW.MESSAGE('11'
604 ,' l_exp_rate = || ' || L_EXP_RATE)*/NULL;
605 /*SRW.MESSAGE('11'
606 ,' l_comp_rate = || ' || L_COMP_RATE)*/NULL;
607 END IF;
608 RETURN L_COMP_RATE;
609 ELSE
610 RETURN 0;
611 END IF;
612 END IF;
613 RETURN 0;
614 END CF_COMPUTATION_RATEFORMULA;
615
616 FUNCTION CF_LINE_9FORMULA(TAX_UNIT_ID IN NUMBER
617 ,C_GAZ_END_DATE IN DATE
618 ,C_TOTAL_TAXABLE_WAGES IN NUMBER) RETURN NUMBER IS
619 L_SUI_ER_SUBJ NUMBER;
620 L_SUI_ER_TAXABLE NUMBER;
621 L_LINE_9 NUMBER := 0;
622 L_VALUE2 NUMBER;
623 L_VALUE3 NUMBER;
624 L_VALUE4 NUMBER;
625 L_VALUE5 NUMBER;
626 L_VALUE6 NUMBER;
627 L_VALUE7 NUMBER;
628 L_VALUE8 NUMBER;
629 L_VALUE9 NUMBER;
630 L_VALUE10 NUMBER;
631 BEGIN
632 PAY_US_TAXBAL_VIEW_PKG.US_GP_MULTIPLE_GRE_YTD(P_TAX_UNIT_ID => TAX_UNIT_ID
633 ,P_EFFECTIVE_DATE => C_GAZ_END_DATE
634 ,P_BALANCE_NAME1 => 'SUI ER Subj Whable'
635 ,P_BALANCE_NAME2 => NULL
636 ,P_BALANCE_NAME3 => NULL
637 ,P_BALANCE_NAME4 => NULL
638 ,P_BALANCE_NAME5 => NULL
639 ,P_BALANCE_NAME6 => NULL
640 ,P_BALANCE_NAME7 => NULL
641 ,P_BALANCE_NAME8 => NULL
642 ,P_BALANCE_NAME9 => NULL
643 ,P_BALANCE_NAME10 => NULL
644 ,P_VALUE1 => L_SUI_ER_SUBJ
645 ,P_VALUE2 => L_VALUE2
646 ,P_VALUE3 => L_VALUE3
647 ,P_VALUE4 => L_VALUE4
648 ,P_VALUE5 => L_VALUE5
649 ,P_VALUE6 => L_VALUE6
650 ,P_VALUE7 => L_VALUE7
651 ,P_VALUE8 => L_VALUE8
652 ,P_VALUE9 => L_VALUE9
653 ,P_VALUE10 => L_VALUE10);
654 IF L_SUI_ER_SUBJ = 0 THEN
655 L_LINE_9 := C_TOTAL_TAXABLE_WAGES * .054;
656 END IF;
657 RETURN L_LINE_9;
658 END CF_LINE_9FORMULA;
659
660 FUNCTION CP_EXEMPT_PAYMENTS_P RETURN NUMBER IS
661 BEGIN
662 RETURN CP_EXEMPT_PAYMENTS;
663 END CP_EXEMPT_PAYMENTS_P;
664
665 FUNCTION CP_EXCESS_PAYMENTS_P RETURN NUMBER IS
666 BEGIN
667 RETURN CP_EXCESS_PAYMENTS;
668 END CP_EXCESS_PAYMENTS_P;
669
670 FUNCTION C_EXPERIENCE_RATE2_P RETURN VARCHAR2 IS
671 BEGIN
672 RETURN C_EXPERIENCE_RATE2;
673 END C_EXPERIENCE_RATE2_P;
674
675 FUNCTION C_TAX_UNIT_P RETURN VARCHAR2 IS
676 BEGIN
677 RETURN C_TAX_UNIT;
678 END C_TAX_UNIT_P;
679
680 FUNCTION C_BUSINESS_GROUP_NAME_P RETURN VARCHAR2 IS
681 BEGIN
682 RETURN C_BUSINESS_GROUP_NAME;
683 END C_BUSINESS_GROUP_NAME_P;
684
685 FUNCTION C_GAZ_START_DATE_P RETURN DATE IS
686 BEGIN
687 RETURN C_GAZ_START_DATE;
688 END C_GAZ_START_DATE_P;
689
690 FUNCTION GET_BUDGET(P_BUDGET_ID IN NUMBER) RETURN VARCHAR2 IS
691 X0 VARCHAR2(2000);
692 BEGIN
693 /* STPROC.INIT('begin :X0 := HR_REPORTS.GET_BUDGET(:P_BUDGET_ID); end;');
694 STPROC.BIND_O(X0);
695 STPROC.BIND_I(P_BUDGET_ID);
696 STPROC.EXECUTE;
697 STPROC.RETRIEVE(1
698 ,X0);*/
699 RETURN NULL;
700 END GET_BUDGET;
701
702 FUNCTION GET_BUDGET_VERSION(P_BUDGET_ID IN NUMBER
703 ,P_BUDGET_VERSION_ID IN NUMBER) RETURN VARCHAR2 IS
704 X0 VARCHAR2(2000);
705 BEGIN
706 /* STPROC.INIT('begin :X0 := HR_REPORTS.GET_BUDGET_VERSION(:P_BUDGET_ID, :P_BUDGET_VERSION_ID); end;');
707 STPROC.BIND_O(X0);
708 STPROC.BIND_I(P_BUDGET_ID);
709 STPROC.BIND_I(P_BUDGET_VERSION_ID);
710 STPROC.EXECUTE;
711 STPROC.RETRIEVE(1
712 ,X0);*/
713 RETURN NULL;
714 END GET_BUDGET_VERSION;
715
716 PROCEDURE GET_ORGANIZATION(P_ORGANIZATION_ID IN NUMBER
717 ,P_ORG_NAME OUT NOCOPY VARCHAR2
718 ,P_ORG_TYPE OUT NOCOPY VARCHAR2) IS
719 BEGIN
720 /* STPROC.INIT('begin HR_REPORTS.GET_ORGANIZATION(:P_ORGANIZATION_ID, :P_ORG_NAME, :P_ORG_TYPE); end;');
721 STPROC.BIND_I(P_ORGANIZATION_ID);
722 STPROC.BIND_O(P_ORG_NAME);
723 STPROC.BIND_O(P_ORG_TYPE);
724 STPROC.EXECUTE;
725 STPROC.RETRIEVE(2
726 ,P_ORG_NAME);
727 STPROC.RETRIEVE(3
728 ,P_ORG_TYPE);*/
729 NULL;
730 END GET_ORGANIZATION;
731
732 FUNCTION GET_JOB(P_JOB_ID IN NUMBER) RETURN VARCHAR2 IS
733 X0 VARCHAR2(2000);
734 BEGIN
735 /* STPROC.INIT('begin :X0 := HR_REPORTS.GET_JOB(:P_JOB_ID); end;');
736 STPROC.BIND_O(X0);
737 STPROC.BIND_I(P_JOB_ID);
738 STPROC.EXECUTE;
739 STPROC.RETRIEVE(1
740 ,X0);*/
741 RETURN NULL;
742 END GET_JOB;
743
744 FUNCTION GET_POSITION(P_POSITION_ID IN NUMBER) RETURN VARCHAR2 IS
745 X0 VARCHAR2(2000);
746 BEGIN
747 /* STPROC.INIT('begin :X0 := HR_REPORTS.GET_POSITION(:P_POSITION_ID); end;');
748 STPROC.BIND_O(X0);
749 STPROC.BIND_I(P_POSITION_ID);
750 STPROC.EXECUTE;
751 STPROC.RETRIEVE(1
752 ,X0);*/
753 RETURN NULL;
754 END GET_POSITION;
755
756 FUNCTION GET_GRADE(P_GRADE_ID IN NUMBER) RETURN VARCHAR2 IS
757 X0 VARCHAR2(2000);
758 BEGIN
759 /* STPROC.INIT('begin :X0 := HR_REPORTS.GET_GRADE(:P_GRADE_ID); end;');
760 STPROC.BIND_O(X0);
761 STPROC.BIND_I(P_GRADE_ID);
762 STPROC.EXECUTE;
763 STPROC.RETRIEVE(1
764 ,X0);*/
765 RETURN NULL;
766 END GET_GRADE;
767
768 FUNCTION GET_STATUS(P_BUSINESS_GROUP_ID IN NUMBER
769 ,P_ASSIGNMENT_STATUS_TYPE_ID IN NUMBER
770 ,P_LEGISLATION_CODE IN VARCHAR2) RETURN VARCHAR2 IS
771 X0 VARCHAR2(2000);
772 BEGIN
773 /* STPROC.INIT('begin :X0 := HR_REPORTS.GET_STATUS(:P_BUSINESS_GROUP_ID, :P_ASSIGNMENT_STATUS_TYPE_ID, :P_LEGISLATION_CODE); end;');
774 STPROC.BIND_O(X0);
775 STPROC.BIND_I(P_BUSINESS_GROUP_ID);
776 STPROC.BIND_I(P_ASSIGNMENT_STATUS_TYPE_ID);
777 STPROC.BIND_I(P_LEGISLATION_CODE);
778 STPROC.EXECUTE;
779 STPROC.RETRIEVE(1
780 ,X0);*/
781 RETURN NULL;
782 END GET_STATUS;
783
784 FUNCTION GET_ABS_TYPE(P_ABS_ATT_TYPE_ID IN NUMBER) RETURN VARCHAR2 IS
785 X0 VARCHAR2(2000);
786 BEGIN
787 /* STPROC.INIT('begin :X0 := HR_REPORTS.GET_ABS_TYPE(:P_ABS_ATT_TYPE_ID); end;');
788 STPROC.BIND_O(X0);
789 STPROC.BIND_I(P_ABS_ATT_TYPE_ID);
790 STPROC.EXECUTE;
791 STPROC.RETRIEVE(1
792 ,X0);*/
793 RETURN NULL;
794 END GET_ABS_TYPE;
795
796 PROCEDURE GET_TIME_PERIOD(P_TIME_PERIOD_ID IN NUMBER
797 ,P_PERIOD_NAME OUT NOCOPY VARCHAR2
798 ,P_START_DATE OUT NOCOPY DATE
799 ,P_END_DATE OUT NOCOPY DATE) IS
800 BEGIN
801 /* STPROC.INIT('begin HR_REPORTS.GET_TIME_PERIOD(:P_TIME_PERIOD_ID, :P_PERIOD_NAME, :P_START_DATE, :P_END_DATE); end;');
802 STPROC.BIND_I(P_TIME_PERIOD_ID);
803 STPROC.BIND_O(P_PERIOD_NAME);
804 STPROC.BIND_O(P_START_DATE);
805 STPROC.BIND_O(P_END_DATE);
806 STPROC.EXECUTE;
807 STPROC.RETRIEVE(2
808 ,P_PERIOD_NAME);
809 STPROC.RETRIEVE(3
810 ,P_START_DATE);
811 STPROC.RETRIEVE(4
812 ,P_END_DATE);*/
813 NULL;
814 END GET_TIME_PERIOD;
815
816 FUNCTION GET_BUSINESS_GROUP(P_BUSINESS_GROUP_ID IN NUMBER) RETURN VARCHAR2 IS
817 X0 VARCHAR2(2000);
818 BEGIN
819 /* STPROC.INIT('begin :X0 := HR_REPORTS.GET_BUSINESS_GROUP(:P_BUSINESS_GROUP_ID); end;');
820 STPROC.BIND_O(X0);
821 STPROC.BIND_I(P_BUSINESS_GROUP_ID);
822 STPROC.EXECUTE;
823 STPROC.RETRIEVE(1
824 ,X0);*/
825 RETURN NULL;
826 END GET_BUSINESS_GROUP;
827
828 FUNCTION COUNT_ORG_SUBORDINATES(P_ORG_STRUCTURE_VERSION_ID IN NUMBER
829 ,P_PARENT_ORGANIZATION_ID IN NUMBER) RETURN NUMBER IS
830 X0 NUMBER;
831 BEGIN
832 /* STPROC.INIT('begin :X0 := HR_REPORTS.COUNT_ORG_SUBORDINATES(:P_ORG_STRUCTURE_VERSION_ID, :P_PARENT_ORGANIZATION_ID); end;');
833 STPROC.BIND_O(X0);
834 STPROC.BIND_I(P_ORG_STRUCTURE_VERSION_ID);
835 STPROC.BIND_I(P_PARENT_ORGANIZATION_ID);
836 STPROC.EXECUTE;
837 STPROC.RETRIEVE(1
838 ,X0);*/
839 RETURN NULL;
840 END COUNT_ORG_SUBORDINATES;
841
842 FUNCTION COUNT_POS_SUBORDINATES(P_POS_STRUCTURE_VERSION_ID IN NUMBER
843 ,P_PARENT_POSITION_ID IN NUMBER) RETURN NUMBER IS
844 X0 NUMBER;
845 BEGIN
846 /* STPROC.INIT('begin :X0 := HR_REPORTS.COUNT_POS_SUBORDINATES(:P_POS_STRUCTURE_VERSION_ID, :P_PARENT_POSITION_ID); end;');
847 STPROC.BIND_O(X0);
848 STPROC.BIND_I(P_POS_STRUCTURE_VERSION_ID);
849 STPROC.BIND_I(P_PARENT_POSITION_ID);
850 STPROC.EXECUTE;
851 STPROC.RETRIEVE(1
852 ,X0);*/
853 RETURN NULL;
854 END COUNT_POS_SUBORDINATES;
855
856 PROCEDURE GET_ORGANIZATION_HIERARCHY(P_ORGANIZATION_STRUCTURE_ID IN NUMBER
857 ,P_ORG_STRUCTURE_VERSION_ID IN NUMBER
858 ,P_ORG_STRUCTURE_NAME OUT NOCOPY VARCHAR2
859 ,P_ORG_VERSION OUT NOCOPY NUMBER
860 ,P_VERSION_START_DATE OUT NOCOPY DATE
861 ,P_VERSION_END_DATE OUT NOCOPY DATE) IS
862 BEGIN
863 /* 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;');
864 STPROC.BIND_I(P_ORGANIZATION_STRUCTURE_ID);
865 STPROC.BIND_I(P_ORG_STRUCTURE_VERSION_ID);
866 STPROC.BIND_O(P_ORG_STRUCTURE_NAME);
867 STPROC.BIND_O(P_ORG_VERSION);
868 STPROC.BIND_O(P_VERSION_START_DATE);
869 STPROC.BIND_O(P_VERSION_END_DATE);
870 STPROC.EXECUTE;
871 STPROC.RETRIEVE(3
872 ,P_ORG_STRUCTURE_NAME);
873 STPROC.RETRIEVE(4
874 ,P_ORG_VERSION);
875 STPROC.RETRIEVE(5
876 ,P_VERSION_START_DATE);
877 STPROC.RETRIEVE(6
878 ,P_VERSION_END_DATE);*/NULL;
879 END GET_ORGANIZATION_HIERARCHY;
880
881 PROCEDURE GET_POSITION_HIERARCHY(P_POSITION_STRUCTURE_ID IN NUMBER
882 ,P_POS_STRUCTURE_VERSION_ID IN NUMBER
883 ,P_POS_STRUCTURE_NAME OUT NOCOPY VARCHAR2
884 ,P_POS_VERSION OUT NOCOPY NUMBER
885 ,P_VERSION_START_DATE OUT NOCOPY DATE
886 ,P_VERSION_END_DATE OUT NOCOPY DATE) IS
887 BEGIN
888 /* 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;');
889 STPROC.BIND_I(P_POSITION_STRUCTURE_ID);
890 STPROC.BIND_I(P_POS_STRUCTURE_VERSION_ID);
891 STPROC.BIND_O(P_POS_STRUCTURE_NAME);
892 STPROC.BIND_O(P_POS_VERSION);
893 STPROC.BIND_O(P_VERSION_START_DATE);
894 STPROC.BIND_O(P_VERSION_END_DATE);
895 STPROC.EXECUTE;
896 STPROC.RETRIEVE(3
897 ,P_POS_STRUCTURE_NAME);
898 STPROC.RETRIEVE(4
899 ,P_POS_VERSION);
900 STPROC.RETRIEVE(5
901 ,P_VERSION_START_DATE);
902 STPROC.RETRIEVE(6
903 ,P_VERSION_END_DATE);*/NULL;
904 END GET_POSITION_HIERARCHY;
905
906 FUNCTION GET_LOOKUP_MEANING(P_LOOKUP_TYPE IN VARCHAR2
907 ,P_LOOKUP_CODE IN VARCHAR2) RETURN VARCHAR2 IS
908 X0 VARCHAR2(2000);
909 BEGIN
910 /*STPROC.INIT('begin :X0 := HR_REPORTS.GET_LOOKUP_MEANING(:P_LOOKUP_TYPE, :P_LOOKUP_CODE); end;');
911 STPROC.BIND_O(X0);
912 STPROC.BIND_I(P_LOOKUP_TYPE);
913 STPROC.BIND_I(P_LOOKUP_CODE);
914 STPROC.EXECUTE;
915 STPROC.RETRIEVE(1
916 ,X0);*/
917 RETURN NULL;
918 END GET_LOOKUP_MEANING;
919
920 FUNCTION PERSON_MATCHING_SKILLS(P_PERSON_ID IN NUMBER
921 ,P_JOB_POSITION_ID IN NUMBER
922 ,P_JOB_POSITION_TYPE IN VARCHAR2
923 ,P_MATCHING_LEVEL IN VARCHAR2
924 ,P_NO_OF_ESSENTIAL IN NUMBER
925 ,P_NO_OF_DESIRABLE IN NUMBER) RETURN BOOLEAN IS
926 X0 BOOLEAN;
927 BEGIN
928 /* 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;');
929 STPROC.BIND_I(P_PERSON_ID);
930 STPROC.BIND_I(P_JOB_POSITION_ID);
931 STPROC.BIND_I(P_JOB_POSITION_TYPE);
932 STPROC.BIND_I(P_MATCHING_LEVEL);
933 STPROC.BIND_I(P_NO_OF_ESSENTIAL);
934 STPROC.BIND_I(P_NO_OF_DESIRABLE);
935 STPROC.BIND_O(X0);
936 STPROC.EXECUTE;
937 STPROC.RETRIEVE(7
938 ,X0);*/
939 RETURN NULL;
940 END PERSON_MATCHING_SKILLS;
941
942 FUNCTION GET_PAYROLL_NAME(P_SESSION_DATE IN DATE
943 ,P_PAYROLL_ID IN NUMBER) RETURN VARCHAR2 IS
944 X0 VARCHAR2(2000);
945 BEGIN
946 /* STPROC.INIT('begin :X0 := HR_REPORTS.GET_PAYROLL_NAME(:P_SESSION_DATE, :P_PAYROLL_ID); end;');
947 STPROC.BIND_O(X0);
948 STPROC.BIND_I(P_SESSION_DATE);
949 STPROC.BIND_I(P_PAYROLL_ID);
950 STPROC.EXECUTE;
951 STPROC.RETRIEVE(1
952 ,X0);*/
953 RETURN NULL;
954 END GET_PAYROLL_NAME;
955
956 FUNCTION GET_ELEMENT_NAME(P_SESSION_DATE IN DATE
957 ,P_ELEMENT_TYPE_ID IN NUMBER) RETURN VARCHAR2 IS
958 X0 VARCHAR2(2000);
959 BEGIN
960 /* STPROC.INIT('begin :X0 := HR_REPORTS.GET_ELEMENT_NAME(:P_SESSION_DATE, :P_ELEMENT_TYPE_ID); end;');
961 STPROC.BIND_O(X0);
962 STPROC.BIND_I(P_SESSION_DATE);
963 STPROC.BIND_I(P_ELEMENT_TYPE_ID);
964 STPROC.EXECUTE;
965 STPROC.RETRIEVE(1
966 ,X0);*/
967 RETURN NULL;
968 END GET_ELEMENT_NAME;
969
970 PROCEDURE GEN_PARTIAL_MATCHING_LEXICAL(P_CONCATENATED_SEGMENTS IN VARCHAR2
971 ,P_ID_FLEX_NUM IN NUMBER
972 ,P_MATCHING_LEXICAL IN OUT NOCOPY VARCHAR2) IS
973 BEGIN
974 /* STPROC.INIT('begin HR_REPORTS.GEN_PARTIAL_MATCHING_LEXICAL(:P_CONCATENATED_SEGMENTS, :P_ID_FLEX_NUM, :P_MATCHING_LEXICAL); end;');
975 STPROC.BIND_I(P_CONCATENATED_SEGMENTS);
976 STPROC.BIND_I(P_ID_FLEX_NUM);
977 STPROC.BIND_IO(P_MATCHING_LEXICAL);
978 STPROC.EXECUTE;
979 STPROC.RETRIEVE(3
980 ,P_MATCHING_LEXICAL);*/NULL;
981 END GEN_PARTIAL_MATCHING_LEXICAL;
982
983 PROCEDURE GET_ATTRIBUTES(P_CONCATENATED_SEGMENTS IN VARCHAR2
984 ,P_NAME IN VARCHAR2
985 ,P_SEGMENTS_USED OUT NOCOPY NUMBER
986 ,P_VALUE1 OUT NOCOPY VARCHAR2
987 ,P_VALUE2 OUT NOCOPY VARCHAR2
988 ,P_VALUE3 OUT NOCOPY VARCHAR2
989 ,P_VALUE4 OUT NOCOPY VARCHAR2
990 ,P_VALUE5 OUT NOCOPY VARCHAR2
991 ,P_VALUE6 OUT NOCOPY VARCHAR2
992 ,P_VALUE7 OUT NOCOPY VARCHAR2
993 ,P_VALUE8 OUT NOCOPY VARCHAR2
994 ,P_VALUE9 OUT NOCOPY VARCHAR2
995 ,P_VALUE10 OUT NOCOPY VARCHAR2
996 ,P_VALUE11 OUT NOCOPY VARCHAR2
997 ,P_VALUE12 OUT NOCOPY VARCHAR2
998 ,P_VALUE13 OUT NOCOPY VARCHAR2
999 ,P_VALUE14 OUT NOCOPY VARCHAR2
1000 ,P_VALUE15 OUT NOCOPY VARCHAR2
1001 ,P_VALUE16 OUT NOCOPY VARCHAR2
1002 ,P_VALUE17 OUT NOCOPY VARCHAR2
1003 ,P_VALUE18 OUT NOCOPY VARCHAR2
1004 ,P_VALUE19 OUT NOCOPY VARCHAR2
1005 ,P_VALUE20 OUT NOCOPY VARCHAR2
1006 ,P_VALUE21 OUT NOCOPY VARCHAR2
1007 ,P_VALUE22 OUT NOCOPY VARCHAR2
1008 ,P_VALUE23 OUT NOCOPY VARCHAR2
1009 ,P_VALUE24 OUT NOCOPY VARCHAR2
1010 ,P_VALUE25 OUT NOCOPY VARCHAR2
1011 ,P_VALUE26 OUT NOCOPY VARCHAR2
1012 ,P_VALUE27 OUT NOCOPY VARCHAR2
1013 ,P_VALUE28 OUT NOCOPY VARCHAR2
1014 ,P_VALUE29 OUT NOCOPY VARCHAR2
1015 ,P_VALUE30 OUT NOCOPY VARCHAR2) IS
1016 BEGIN
1017 /* 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,
1018 :P_VALUE7, :P_VALUE8, :P_VALUE9, :P_VALUE10, :P_VALUE11, :P_VALUE12, :P_VALUE13, :P_VALUE14, :P_VALUE15, :P_VALUE16,
1019 :P_VALUE17, :P_VALUE18, :P_VALUE19, :P_VALUE20, :P_VALUE21, :P_VALUE22, :P_VALUE23, :P_VALUE24, :P_VALUE25, :P_VALUE26, :P_VALUE27, :P_VALUE28, :P_VALUE29, :P_VALUE30); end;');
1020 STPROC.BIND_I(P_CONCATENATED_SEGMENTS);
1021 STPROC.BIND_I(P_NAME);
1022 STPROC.BIND_O(P_SEGMENTS_USED);
1023 STPROC.BIND_O(P_VALUE1);
1024 STPROC.BIND_O(P_VALUE2);
1025 STPROC.BIND_O(P_VALUE3);
1026 STPROC.BIND_O(P_VALUE4);
1027 STPROC.BIND_O(P_VALUE5);
1028 STPROC.BIND_O(P_VALUE6);
1029 STPROC.BIND_O(P_VALUE7);
1030 STPROC.BIND_O(P_VALUE8);
1031 STPROC.BIND_O(P_VALUE9);
1032 STPROC.BIND_O(P_VALUE10);
1033 STPROC.BIND_O(P_VALUE11);
1034 STPROC.BIND_O(P_VALUE12);
1035 STPROC.BIND_O(P_VALUE13);
1036 STPROC.BIND_O(P_VALUE14);
1037 STPROC.BIND_O(P_VALUE15);
1038 STPROC.BIND_O(P_VALUE16);
1039 STPROC.BIND_O(P_VALUE17);
1040 STPROC.BIND_O(P_VALUE18);
1041 STPROC.BIND_O(P_VALUE19);
1042 STPROC.BIND_O(P_VALUE20);
1043 STPROC.BIND_O(P_VALUE21);
1044 STPROC.BIND_O(P_VALUE22);
1045 STPROC.BIND_O(P_VALUE23);
1046 STPROC.BIND_O(P_VALUE24);
1047 STPROC.BIND_O(P_VALUE25);
1048 STPROC.BIND_O(P_VALUE26);
1049 STPROC.BIND_O(P_VALUE27);
1050 STPROC.BIND_O(P_VALUE28);
1051 STPROC.BIND_O(P_VALUE29);
1052 STPROC.BIND_O(P_VALUE30);
1053 STPROC.EXECUTE;
1054 STPROC.RETRIEVE(3
1055 ,P_SEGMENTS_USED);
1056 STPROC.RETRIEVE(4
1057 ,P_VALUE1);
1058 STPROC.RETRIEVE(5
1059 ,P_VALUE2);
1060 STPROC.RETRIEVE(6
1061 ,P_VALUE3);
1062 STPROC.RETRIEVE(7
1063 ,P_VALUE4);
1064 STPROC.RETRIEVE(8
1065 ,P_VALUE5);
1066 STPROC.RETRIEVE(9
1067 ,P_VALUE6);
1068 STPROC.RETRIEVE(10
1069 ,P_VALUE7);
1070 STPROC.RETRIEVE(11
1071 ,P_VALUE8);
1072 STPROC.RETRIEVE(12
1073 ,P_VALUE9);
1074 STPROC.RETRIEVE(13
1075 ,P_VALUE10);
1076 STPROC.RETRIEVE(14
1077 ,P_VALUE11);
1078 STPROC.RETRIEVE(15
1079 ,P_VALUE12);
1080 STPROC.RETRIEVE(16
1081 ,P_VALUE13);
1082 STPROC.RETRIEVE(17
1083 ,P_VALUE14);
1084 STPROC.RETRIEVE(18
1085 ,P_VALUE15);
1086 STPROC.RETRIEVE(19
1087 ,P_VALUE16);
1088 STPROC.RETRIEVE(20
1089 ,P_VALUE17);
1090 STPROC.RETRIEVE(21
1091 ,P_VALUE18);
1092 STPROC.RETRIEVE(22
1093 ,P_VALUE19);
1094 STPROC.RETRIEVE(23
1095 ,P_VALUE20);
1096 STPROC.RETRIEVE(24
1097 ,P_VALUE21);
1098 STPROC.RETRIEVE(25
1099 ,P_VALUE22);
1100 STPROC.RETRIEVE(26
1101 ,P_VALUE23);
1102 STPROC.RETRIEVE(27
1103 ,P_VALUE24);
1104 STPROC.RETRIEVE(28
1105 ,P_VALUE25);
1106 STPROC.RETRIEVE(29
1107 ,P_VALUE26);
1108 STPROC.RETRIEVE(30
1109 ,P_VALUE27);
1110 STPROC.RETRIEVE(31
1111 ,P_VALUE28);
1112 STPROC.RETRIEVE(32
1113 ,P_VALUE29);
1114 STPROC.RETRIEVE(33
1115 ,P_VALUE30);*/NULL;
1116 END GET_ATTRIBUTES;
1117
1118 PROCEDURE GET_SEGMENTS(P_CONCATENATED_SEGMENTS IN VARCHAR2
1119 ,P_ID_FLEX_NUM IN NUMBER
1120 ,P_SEGMENTS_USED OUT NOCOPY NUMBER
1121 ,P_VALUE1 OUT NOCOPY VARCHAR2
1122 ,P_VALUE2 OUT NOCOPY VARCHAR2
1123 ,P_VALUE3 OUT NOCOPY VARCHAR2
1124 ,P_VALUE4 OUT NOCOPY VARCHAR2
1125 ,P_VALUE5 OUT NOCOPY VARCHAR2
1126 ,P_VALUE6 OUT NOCOPY VARCHAR2
1127 ,P_VALUE7 OUT NOCOPY VARCHAR2
1128 ,P_VALUE8 OUT NOCOPY VARCHAR2
1129 ,P_VALUE9 OUT NOCOPY VARCHAR2
1130 ,P_VALUE10 OUT NOCOPY VARCHAR2
1131 ,P_VALUE11 OUT NOCOPY VARCHAR2
1132 ,P_VALUE12 OUT NOCOPY VARCHAR2
1133 ,P_VALUE13 OUT NOCOPY VARCHAR2
1134 ,P_VALUE14 OUT NOCOPY VARCHAR2
1135 ,P_VALUE15 OUT NOCOPY VARCHAR2
1136 ,P_VALUE16 OUT NOCOPY VARCHAR2
1137 ,P_VALUE17 OUT NOCOPY VARCHAR2
1138 ,P_VALUE18 OUT NOCOPY VARCHAR2
1139 ,P_VALUE19 OUT NOCOPY VARCHAR2
1140 ,P_VALUE20 OUT NOCOPY VARCHAR2
1141 ,P_VALUE21 OUT NOCOPY VARCHAR2
1142 ,P_VALUE22 OUT NOCOPY VARCHAR2
1143 ,P_VALUE23 OUT NOCOPY VARCHAR2
1144 ,P_VALUE24 OUT NOCOPY VARCHAR2
1145 ,P_VALUE25 OUT NOCOPY VARCHAR2
1146 ,P_VALUE26 OUT NOCOPY VARCHAR2
1147 ,P_VALUE27 OUT NOCOPY VARCHAR2
1148 ,P_VALUE28 OUT NOCOPY VARCHAR2
1149 ,P_VALUE29 OUT NOCOPY VARCHAR2
1150 ,P_VALUE30 OUT NOCOPY VARCHAR2) IS
1151 BEGIN
1152 /* STPROC.INIT('begin HR_REPORTS.GET_SEGMENTS(:P_CONCATENATED_SEGMENTS, :P_ID_FLEX_NUM, :P_SEGMENTS_USED, :P_VALUE1, :P_VALUE2, :P_VALUE3, :P_VALUE4, :P_VALUE5,
1153 :P_VALUE6, :P_VALUE7, :P_VALUE8, :P_VALUE9, :P_VALUE10, :P_VALUE11, :P_VALUE12, :P_VALUE13, :P_VALUE14, :P_VALUE15, :P_VALUE16, :P_VALUE17, :P_VALUE18, :P_VALUE19,
1154 :P_VALUE20, :P_VALUE21, :P_VALUE22, :P_VALUE23, :P_VALUE24, :P_VALUE25, :P_VALUE26, :P_VALUE27, :P_VALUE28, :P_VALUE29, :P_VALUE30); end;');
1155 STPROC.BIND_I(P_CONCATENATED_SEGMENTS);
1156 STPROC.BIND_I(P_ID_FLEX_NUM);
1157 STPROC.BIND_O(P_SEGMENTS_USED);
1158 STPROC.BIND_O(P_VALUE1);
1159 STPROC.BIND_O(P_VALUE2);
1160 STPROC.BIND_O(P_VALUE3);
1161 STPROC.BIND_O(P_VALUE4);
1162 STPROC.BIND_O(P_VALUE5);
1163 STPROC.BIND_O(P_VALUE6);
1164 STPROC.BIND_O(P_VALUE7);
1165 STPROC.BIND_O(P_VALUE8);
1166 STPROC.BIND_O(P_VALUE9);
1167 STPROC.BIND_O(P_VALUE10);
1168 STPROC.BIND_O(P_VALUE11);
1169 STPROC.BIND_O(P_VALUE12);
1170 STPROC.BIND_O(P_VALUE13);
1171 STPROC.BIND_O(P_VALUE14);
1172 STPROC.BIND_O(P_VALUE15);
1173 STPROC.BIND_O(P_VALUE16);
1174 STPROC.BIND_O(P_VALUE17);
1175 STPROC.BIND_O(P_VALUE18);
1176 STPROC.BIND_O(P_VALUE19);
1177 STPROC.BIND_O(P_VALUE20);
1178 STPROC.BIND_O(P_VALUE21);
1179 STPROC.BIND_O(P_VALUE22);
1180 STPROC.BIND_O(P_VALUE23);
1181 STPROC.BIND_O(P_VALUE24);
1182 STPROC.BIND_O(P_VALUE25);
1183 STPROC.BIND_O(P_VALUE26);
1184 STPROC.BIND_O(P_VALUE27);
1185 STPROC.BIND_O(P_VALUE28);
1186 STPROC.BIND_O(P_VALUE29);
1187 STPROC.BIND_O(P_VALUE30);
1188 STPROC.EXECUTE;
1189 STPROC.RETRIEVE(3
1190 ,P_SEGMENTS_USED);
1191 STPROC.RETRIEVE(4
1192 ,P_VALUE1);
1193 STPROC.RETRIEVE(5
1194 ,P_VALUE2);
1195 STPROC.RETRIEVE(6
1196 ,P_VALUE3);
1197 STPROC.RETRIEVE(7
1198 ,P_VALUE4);
1199 STPROC.RETRIEVE(8
1200 ,P_VALUE5);
1201 STPROC.RETRIEVE(9
1202 ,P_VALUE6);
1203 STPROC.RETRIEVE(10
1204 ,P_VALUE7);
1205 STPROC.RETRIEVE(11
1206 ,P_VALUE8);
1207 STPROC.RETRIEVE(12
1208 ,P_VALUE9);
1209 STPROC.RETRIEVE(13
1210 ,P_VALUE10);
1211 STPROC.RETRIEVE(14
1212 ,P_VALUE11);
1213 STPROC.RETRIEVE(15
1214 ,P_VALUE12);
1215 STPROC.RETRIEVE(16
1216 ,P_VALUE13);
1217 STPROC.RETRIEVE(17
1218 ,P_VALUE14);
1219 STPROC.RETRIEVE(18
1220 ,P_VALUE15);
1221 STPROC.RETRIEVE(19
1222 ,P_VALUE16);
1223 STPROC.RETRIEVE(20
1224 ,P_VALUE17);
1225 STPROC.RETRIEVE(21
1226 ,P_VALUE18);
1227 STPROC.RETRIEVE(22
1228 ,P_VALUE19);
1229 STPROC.RETRIEVE(23
1230 ,P_VALUE20);
1231 STPROC.RETRIEVE(24
1232 ,P_VALUE21);
1233 STPROC.RETRIEVE(25
1234 ,P_VALUE22);
1235 STPROC.RETRIEVE(26
1236 ,P_VALUE23);
1237 STPROC.RETRIEVE(27
1238 ,P_VALUE24);
1239 STPROC.RETRIEVE(28
1240 ,P_VALUE25);
1241 STPROC.RETRIEVE(29
1242 ,P_VALUE26);
1243 STPROC.RETRIEVE(30
1244 ,P_VALUE27);
1245 STPROC.RETRIEVE(31
1246 ,P_VALUE28);
1247 STPROC.RETRIEVE(32
1248 ,P_VALUE29);
1249 STPROC.RETRIEVE(33
1250 ,P_VALUE30);*/NULL;
1251 END GET_SEGMENTS;
1252
1253 PROCEDURE GET_DESC_FLEX(P_APPL_SHORT_NAME IN VARCHAR2
1254 ,P_DESC_FLEX_NAME IN VARCHAR2
1255 ,P_TABLE_ALIAS IN VARCHAR2
1256 ,P_TITLE OUT NOCOPY VARCHAR2
1257 ,P_LABEL_EXPR OUT NOCOPY VARCHAR2
1258 ,P_COLUMN_EXPR OUT NOCOPY VARCHAR2) IS
1259 BEGIN
1260 /* 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;');
1261 STPROC.BIND_I(P_APPL_SHORT_NAME);
1262 STPROC.BIND_I(P_DESC_FLEX_NAME);
1263 STPROC.BIND_I(P_TABLE_ALIAS);
1264 STPROC.BIND_O(P_TITLE);
1265 STPROC.BIND_O(P_LABEL_EXPR);
1266 STPROC.BIND_O(P_COLUMN_EXPR);
1267 STPROC.EXECUTE;
1268 STPROC.RETRIEVE(4
1269 ,P_TITLE);
1270 STPROC.RETRIEVE(5
1271 ,P_LABEL_EXPR);
1272 STPROC.RETRIEVE(6
1273 ,P_COLUMN_EXPR);*/NULL;
1274 END GET_DESC_FLEX;
1275
1276 PROCEDURE GET_DESC_FLEX_CONTEXT(P_APPL_SHORT_NAME IN VARCHAR2
1277 ,P_DESC_FLEX_NAME IN VARCHAR2
1278 ,P_TABLE_ALIAS IN VARCHAR2
1279 ,P_TITLE OUT NOCOPY VARCHAR2
1280 ,P_LABEL_EXPR OUT NOCOPY VARCHAR2
1281 ,P_COLUMN_EXPR OUT NOCOPY VARCHAR2) IS
1282 BEGIN
1283 /* 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;');
1284 STPROC.BIND_I(P_APPL_SHORT_NAME);
1285 STPROC.BIND_I(P_DESC_FLEX_NAME);
1286 STPROC.BIND_I(P_TABLE_ALIAS);
1287 STPROC.BIND_O(P_TITLE);
1288 STPROC.BIND_O(P_LABEL_EXPR);
1289 STPROC.BIND_O(P_COLUMN_EXPR);
1290 STPROC.EXECUTE;
1291 STPROC.RETRIEVE(4
1292 ,P_TITLE);
1293 STPROC.RETRIEVE(5
1294 ,P_LABEL_EXPR);
1295 STPROC.RETRIEVE(6
1296 ,P_COLUMN_EXPR);*/NULL;
1297 END GET_DESC_FLEX_CONTEXT;
1298
1299 PROCEDURE GET_DVLPR_DESC_FLEX(P_APPL_SHORT_NAME IN VARCHAR2
1300 ,P_DESC_FLEX_NAME IN VARCHAR2
1301 ,P_DESC_FLEX_CONTEXT IN VARCHAR2
1302 ,P_TABLE_ALIAS IN VARCHAR2
1303 ,P_TITLE OUT NOCOPY VARCHAR2
1304 ,P_LABEL_EXPR OUT NOCOPY VARCHAR2
1305 ,P_COLUMN_EXPR OUT NOCOPY VARCHAR2) IS
1306 BEGIN
1307 /* 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;');
1308 STPROC.BIND_I(P_APPL_SHORT_NAME);
1309 STPROC.BIND_I(P_DESC_FLEX_NAME);
1310 STPROC.BIND_I(P_DESC_FLEX_CONTEXT);
1311 STPROC.BIND_I(P_TABLE_ALIAS);
1312 STPROC.BIND_O(P_TITLE);
1313 STPROC.BIND_O(P_LABEL_EXPR);
1314 STPROC.BIND_O(P_COLUMN_EXPR);
1315 STPROC.EXECUTE;
1316 STPROC.RETRIEVE(5
1317 ,P_TITLE);
1318 STPROC.RETRIEVE(6
1319 ,P_LABEL_EXPR);
1320 STPROC.RETRIEVE(7
1321 ,P_COLUMN_EXPR);*/NULL;
1322 END GET_DVLPR_DESC_FLEX;
1323
1324 FUNCTION GET_PERSON_NAME(P_SESSION_DATE IN DATE
1325 ,P_PERSON_ID IN NUMBER) RETURN VARCHAR2 IS
1326 X0 VARCHAR2(2000);
1327 BEGIN
1328 /* STPROC.INIT('begin :X0 := HR_REPORTS.GET_PERSON_NAME(:P_SESSION_DATE, :P_PERSON_ID); end;');
1329 STPROC.BIND_O(X0);
1330 STPROC.BIND_I(P_SESSION_DATE);
1331 STPROC.BIND_I(P_PERSON_ID);
1332 STPROC.EXECUTE;
1333 STPROC.RETRIEVE(1
1334 ,X0);*/
1335 RETURN NULL;
1336 END GET_PERSON_NAME;
1337
1338 FUNCTION GET_SALARY(P_BUSINESS_GROUP_ID IN NUMBER
1339 ,P_ASSIGNMENT_ID IN NUMBER
1340 ,P_REPORT_DATE IN DATE) RETURN NUMBER IS
1341 X0 NUMBER;
1342 BEGIN
1343 /* STPROC.INIT('begin :X0 := HR_US_REPORTS.GET_SALARY(:P_BUSINESS_GROUP_ID, :P_ASSIGNMENT_ID, :P_REPORT_DATE); end;');
1344 STPROC.BIND_O(X0);
1345 STPROC.BIND_I(P_BUSINESS_GROUP_ID);
1346 STPROC.BIND_I(P_ASSIGNMENT_ID);
1347 STPROC.BIND_I(P_REPORT_DATE);
1348 STPROC.EXECUTE;
1349 STPROC.RETRIEVE(1
1350 ,X0);*/
1351 RETURN NULL;
1352 END GET_SALARY;
1353
1354 PROCEDURE GET_NEW_HIRE_CONTACT(P_PERSON_ID IN NUMBER
1355 ,P_BUSINESS_GROUP_ID IN NUMBER
1356 ,P_REPORT_DATE IN DATE
1357 ,P_CONTACT_NAME OUT NOCOPY VARCHAR2
1358 ,P_CONTACT_TITLE OUT NOCOPY VARCHAR2
1359 ,P_CONTACT_PHONE OUT NOCOPY VARCHAR2) IS
1360 BEGIN
1361 /* STPROC.INIT('begin HR_US_REPORTS.GET_NEW_HIRE_CONTACT(:P_PERSON_ID, :P_BUSINESS_GROUP_ID, :P_REPORT_DATE, :P_CONTACT_NAME, :P_CONTACT_TITLE, :P_CONTACT_PHONE); end;');
1362 STPROC.BIND_I(P_PERSON_ID);
1363 STPROC.BIND_I(P_BUSINESS_GROUP_ID);
1364 STPROC.BIND_I(P_REPORT_DATE);
1365 STPROC.BIND_O(P_CONTACT_NAME);
1366 STPROC.BIND_O(P_CONTACT_TITLE);
1367 STPROC.BIND_O(P_CONTACT_PHONE);
1368 STPROC.EXECUTE;
1369 STPROC.RETRIEVE(4
1370 ,P_CONTACT_NAME);
1371 STPROC.RETRIEVE(5
1372 ,P_CONTACT_TITLE);
1373 STPROC.RETRIEVE(6
1374 ,P_CONTACT_PHONE);*/
1375 NULL;
1376 END GET_NEW_HIRE_CONTACT;
1377
1378 PROCEDURE GET_ADDRESS(P_LOCATION_ID IN NUMBER
1379 ,P_ADDRESS OUT NOCOPY VARCHAR2) IS
1380 BEGIN
1381 /*STPROC.INIT('begin HR_US_REPORTS.GET_ADDRESS(:P_LOCATION_ID, :P_ADDRESS); end;');
1382 STPROC.BIND_I(P_LOCATION_ID);
1383 STPROC.BIND_O(P_ADDRESS);
1384 STPROC.EXECUTE;
1385 STPROC.RETRIEVE(2
1386 ,P_ADDRESS);*/NULL;
1387 END GET_ADDRESS;
1388
1389 PROCEDURE GET_EMPLOYEE_ADDRESS(P_PERSON_ID IN NUMBER
1390 ,P_ADDRESS OUT NOCOPY VARCHAR2) IS
1391 BEGIN
1392 /* STPROC.INIT('begin HR_US_REPORTS.GET_EMPLOYEE_ADDRESS(:P_PERSON_ID, :P_ADDRESS); end;');
1393 STPROC.BIND_I(P_PERSON_ID);
1394 STPROC.BIND_O(P_ADDRESS);
1395 STPROC.EXECUTE;
1396 STPROC.RETRIEVE(2
1397 ,P_ADDRESS);*/NULL;
1398 END GET_EMPLOYEE_ADDRESS;
1399
1400 PROCEDURE GET_COUNTY_ADDRESS(P_LOCATION_ID IN NUMBER
1401 ,P_ADDRESS OUT NOCOPY VARCHAR2) IS
1402 BEGIN
1403 /* STPROC.INIT('begin HR_US_REPORTS.GET_COUNTY_ADDRESS(:P_LOCATION_ID, :P_ADDRESS); end;');
1404 STPROC.BIND_I(P_LOCATION_ID);
1405 STPROC.BIND_O(P_ADDRESS);
1406 STPROC.EXECUTE;
1407 STPROC.RETRIEVE(2
1408 ,P_ADDRESS);*/NULL;
1409 END GET_COUNTY_ADDRESS;
1410
1411 PROCEDURE GET_ACTIVITY(P_ESTABLISHMENT_ID IN NUMBER
1412 ,P_ACTIVITY OUT NOCOPY VARCHAR2) IS
1413 BEGIN
1414 /* STPROC.INIT('begin HR_US_REPORTS.GET_ACTIVITY(:P_ESTABLISHMENT_ID, :P_ACTIVITY); end;');
1415 STPROC.BIND_I(P_ESTABLISHMENT_ID);
1416 STPROC.BIND_O(P_ACTIVITY);
1417 STPROC.EXECUTE;
1418 STPROC.RETRIEVE(2
1419 ,P_ACTIVITY);*/NULL;
1420 END GET_ACTIVITY;
1421
1422 FUNCTION GET_CONSOLIDATION_SET(P_CONSOLIDATION_SET_ID IN NUMBER) RETURN VARCHAR2 IS
1423 X0 VARCHAR2(2000);
1424 BEGIN
1425 /* STPROC.INIT('begin :X0 := HR_US_REPORTS.GET_CONSOLIDATION_SET(:P_CONSOLIDATION_SET_ID); end;');
1426 STPROC.BIND_O(X0);
1427 STPROC.BIND_I(P_CONSOLIDATION_SET_ID);
1428 STPROC.EXECUTE;
1429 STPROC.RETRIEVE(1
1430 ,X0);*/
1431 RETURN NULL;
1432 END GET_CONSOLIDATION_SET;
1433
1434 FUNCTION GET_PAYMENT_TYPE_NAME(P_PAYMENT_TYPE_ID IN NUMBER) RETURN VARCHAR2 IS
1435 X0 VARCHAR2(2000);
1436 BEGIN
1437 /* STPROC.INIT('begin :X0 := HR_US_REPORTS.GET_PAYMENT_TYPE_NAME(:P_PAYMENT_TYPE_ID); end;');
1438 STPROC.BIND_O(X0);
1439 STPROC.BIND_I(P_PAYMENT_TYPE_ID);
1440 STPROC.EXECUTE;
1441 STPROC.RETRIEVE(1
1442 ,X0);*/
1443 RETURN NULL;
1444 END GET_PAYMENT_TYPE_NAME;
1445
1446 FUNCTION GET_ELEMENT_TYPE_NAME(P_ELEMENT_TYPE_ID IN NUMBER) RETURN VARCHAR2 IS
1447 X0 VARCHAR2(2000);
1448 BEGIN
1449 /* STPROC.INIT('begin :X0 := HR_US_REPORTS.GET_ELEMENT_TYPE_NAME(:P_ELEMENT_TYPE_ID); end;');
1450 STPROC.BIND_O(X0);
1451 STPROC.BIND_I(P_ELEMENT_TYPE_ID);
1452 STPROC.EXECUTE;
1453 STPROC.RETRIEVE(1
1454 ,X0);*/
1455 RETURN NULL;
1456 END GET_ELEMENT_TYPE_NAME;
1457
1458 FUNCTION GET_TAX_UNIT(P_TAX_UNIT_ID IN NUMBER) RETURN VARCHAR2 IS
1459 X0 VARCHAR2(2000);
1460 BEGIN
1461 /* STPROC.INIT('begin :X0 := HR_US_REPORTS.GET_TAX_UNIT(:P_TAX_UNIT_ID); end;');
1462 STPROC.BIND_O(X0);
1463 STPROC.BIND_I(P_TAX_UNIT_ID);
1464 STPROC.EXECUTE;
1465 STPROC.RETRIEVE(1
1466 ,X0);*/
1467 RETURN NULL;
1468 END GET_TAX_UNIT;
1469
1470 FUNCTION GET_PERSON_NAME(P_PERSON_ID IN NUMBER) RETURN VARCHAR2 IS
1471 X0 VARCHAR2(2000);
1472 BEGIN
1473 /* STPROC.INIT('begin :X0 := HR_US_REPORTS.GET_PERSON_NAME(:P_PERSON_ID); end;');
1474 STPROC.BIND_O(X0);
1475 STPROC.BIND_I(P_PERSON_ID);
1476 STPROC.EXECUTE;
1477 STPROC.RETRIEVE(1
1478 ,X0);*/
1479 RETURN NULL;
1480 END GET_PERSON_NAME;
1481
1482 FUNCTION GET_PAYROLL_ACTION(P_PAYROLL_ACTION_ID IN NUMBER) RETURN VARCHAR2 IS
1483 X0 VARCHAR2(2000);
1484 BEGIN
1485 /* STPROC.INIT('begin :X0 := HR_US_REPORTS.GET_PAYROLL_ACTION(:P_PAYROLL_ACTION_ID); end;');
1486 STPROC.BIND_O(X0);
1487 STPROC.BIND_I(P_PAYROLL_ACTION_ID);
1488 STPROC.EXECUTE;
1489 STPROC.RETRIEVE(1
1490 ,X0);*/
1491 RETURN NULL;
1492 END GET_PAYROLL_ACTION;
1493
1494 FUNCTION GET_LEGISLATION_CODE(P_BUSINESS_GROUP_ID IN NUMBER) RETURN VARCHAR2 IS
1495 X0 VARCHAR2(2000);
1496 BEGIN
1497 /* STPROC.INIT('begin :X0 := HR_US_REPORTS.GET_LEGISLATION_CODE(:P_BUSINESS_GROUP_ID); end;');
1498 STPROC.BIND_O(X0);
1499 STPROC.BIND_I(P_BUSINESS_GROUP_ID);
1500 STPROC.EXECUTE;
1501 STPROC.RETRIEVE(1
1502 ,X0);*/
1503 RETURN NULL;
1504 END GET_LEGISLATION_CODE;
1505
1506 FUNCTION GET_DEFINED_BALANCE_ID(P_BALANCE_NAME IN VARCHAR2
1507 ,P_DIMENSION_SUFFIX IN VARCHAR2
1508 ,P_BUSINESS_GROUP_ID IN NUMBER) RETURN NUMBER IS
1509 X0 NUMBER;
1510 BEGIN
1511 /* STPROC.INIT('begin :X0 := HR_US_REPORTS.GET_DEFINED_BALANCE_ID(:P_BALANCE_NAME, :P_DIMENSION_SUFFIX, :P_BUSINESS_GROUP_ID); end;');
1512 STPROC.BIND_O(X0);
1513 STPROC.BIND_I(P_BALANCE_NAME);
1514 STPROC.BIND_I(P_DIMENSION_SUFFIX);
1515 STPROC.BIND_I(P_BUSINESS_GROUP_ID);
1516 STPROC.EXECUTE;
1517 STPROC.RETRIEVE(1
1518 ,X0);*/
1519 RETURN NULL;
1520 END GET_DEFINED_BALANCE_ID;
1521
1522 FUNCTION GET_STARTUP_DEFINED_BALANCE(P_REPORTING_NAME IN VARCHAR2
1523 ,P_DIMENSION_SUFFIX IN VARCHAR2) RETURN NUMBER IS
1524 X0 NUMBER;
1525 BEGIN
1526 /* STPROC.INIT('begin :X0 := HR_US_REPORTS.GET_STARTUP_DEFINED_BALANCE(:P_REPORTING_NAME, :P_DIMENSION_SUFFIX); end;');
1527 STPROC.BIND_O(X0);
1528 STPROC.BIND_I(P_REPORTING_NAME);
1529 STPROC.BIND_I(P_DIMENSION_SUFFIX);
1530 STPROC.EXECUTE;
1531 STPROC.RETRIEVE(1
1532 ,X0);*/
1533 RETURN NULL;
1534 END GET_STARTUP_DEFINED_BALANCE;
1535
1536 FUNCTION GET_DEFINED_BALANCE_BY_TYPE(P_BOX_NUM IN VARCHAR2
1537 ,P_DIMENSION_SUFFIX IN VARCHAR2) RETURN NUMBER IS
1538 X0 NUMBER;
1539 BEGIN
1540 /* STPROC.INIT('begin :X0 := HR_US_REPORTS.GET_DEFINED_BALANCE_BY_TYPE(:P_BOX_NUM, :P_DIMENSION_SUFFIX); end;');
1541 STPROC.BIND_O(X0);
1542 STPROC.BIND_I(P_BOX_NUM);
1543 STPROC.BIND_I(P_DIMENSION_SUFFIX);
1544 STPROC.EXECUTE;
1545 STPROC.RETRIEVE(1
1546 ,X0);
1547 RETURN X0;*/
1548 RETURN NULL;
1549 END GET_DEFINED_BALANCE_BY_TYPE;
1550
1551 FUNCTION GET_BEN_CLASS_NAME(P_SESSION_DATE IN DATE
1552 ,P_BENEFIT_CLASSIFICATION_ID IN NUMBER) RETURN VARCHAR2 IS
1553 X0 VARCHAR2(2000);
1554 BEGIN
1555 /* STPROC.INIT('begin :X0 := HR_US_REPORTS.GET_BEN_CLASS_NAME(:P_SESSION_DATE, :P_BENEFIT_CLASSIFICATION_ID); end;');
1556 STPROC.BIND_O(X0);
1557 STPROC.BIND_I(P_SESSION_DATE);
1558 STPROC.BIND_I(P_BENEFIT_CLASSIFICATION_ID);
1559 STPROC.EXECUTE;
1560 STPROC.RETRIEVE(1
1561 ,X0);
1562 RETURN X0;*/
1563 RETURN NULL;
1564 END GET_BEN_CLASS_NAME;
1565
1566 FUNCTION GET_COBRA_QUALIFYING_EVENT(P_QUALIFYING_EVENT IN VARCHAR2) RETURN VARCHAR2 IS
1567 X0 VARCHAR2(2000);
1568 BEGIN
1569 /* STPROC.INIT('begin :X0 := HR_US_REPORTS.GET_COBRA_QUALIFYING_EVENT(:P_QUALIFYING_EVENT); end;');
1570 STPROC.BIND_O(X0);
1571 STPROC.BIND_I(P_QUALIFYING_EVENT);
1572 STPROC.EXECUTE;
1573 STPROC.RETRIEVE(1
1574 ,X0);
1575 RETURN X0;*/
1576 RETURN NULL;
1577 END GET_COBRA_QUALIFYING_EVENT;
1578
1579 FUNCTION GET_COBRA_STATUS(P_COBRA_STATUS IN VARCHAR2) RETURN VARCHAR2 IS
1580 X0 VARCHAR2(2000);
1581 BEGIN
1582 /* STPROC.INIT('begin :X0 := HR_US_REPORTS.GET_COBRA_STATUS(:P_COBRA_STATUS); end;');
1583 STPROC.BIND_O(X0);
1584 STPROC.BIND_I(P_COBRA_STATUS);
1585 STPROC.EXECUTE;
1586 STPROC.RETRIEVE(1
1587 ,X0);
1588 RETURN X0;*/
1589 RETURN NULL;
1590 END GET_COBRA_STATUS;
1591
1592 FUNCTION GET_EST_TAX_UNIT(P_STARTING_ORG_ID IN NUMBER
1593 ,P_ORG_STRUCTURE_VERSION_ID IN NUMBER) RETURN NUMBER IS
1594 X0 NUMBER;
1595 BEGIN
1596 /* STPROC.INIT('begin :X0 := HR_US_REPORTS.GET_EST_TAX_UNIT(:P_STARTING_ORG_ID, :P_ORG_STRUCTURE_VERSION_ID); end;');
1597 STPROC.BIND_O(X0);
1598 STPROC.BIND_I(P_STARTING_ORG_ID);
1599 STPROC.BIND_I(P_ORG_STRUCTURE_VERSION_ID);
1600 STPROC.EXECUTE;
1601 STPROC.RETRIEVE(1
1602 ,X0);
1603 RETURN X0;*/
1604 RETURN NULL;
1605 END GET_EST_TAX_UNIT;
1606
1607 FUNCTION GET_ORG_HIERARCHY_NAME(P_ORG_STRUCTURE_VERSION_ID IN NUMBER) RETURN VARCHAR2 IS
1608 X0 VARCHAR2(2000);
1609 BEGIN
1610 /* STPROC.INIT('begin :X0 := HR_US_REPORTS.GET_ORG_HIERARCHY_NAME(:P_ORG_STRUCTURE_VERSION_ID); end;');
1611 STPROC.BIND_O(X0);
1612 STPROC.BIND_I(P_ORG_STRUCTURE_VERSION_ID);
1613 STPROC.EXECUTE;
1614 STPROC.RETRIEVE(1
1615 ,X0);
1616 RETURN X0;*/
1617 RETURN NULL;
1618 END GET_ORG_HIERARCHY_NAME;
1619
1620 FUNCTION GET_STATE_NAME(P_STATE_CODE IN VARCHAR2) RETURN VARCHAR2 IS
1621 X0 VARCHAR2(2000);
1622 BEGIN
1623 /* STPROC.INIT('begin :X0 := HR_US_REPORTS.GET_STATE_NAME(:P_STATE_CODE); end;');
1624 STPROC.BIND_O(X0);
1625 STPROC.BIND_I(P_STATE_CODE);
1626 STPROC.EXECUTE;
1627 STPROC.RETRIEVE(1
1628 ,X0);
1629 RETURN X0;*/
1630 RETURN NULL;
1631 END GET_STATE_NAME;
1632
1633 FUNCTION GET_ORG_NAME(P_ORGANIZATION_ID IN NUMBER
1634 ,P_BUSINESS_GROUP_ID IN NUMBER) RETURN VARCHAR2 IS
1635 X0 VARCHAR2(2000);
1636 BEGIN
1637 /* STPROC.INIT('begin :X0 := HR_US_REPORTS.GET_ORG_NAME(:P_ORGANIZATION_ID, :P_BUSINESS_GROUP_ID); end;');
1638 STPROC.BIND_O(X0);
1639 STPROC.BIND_I(P_ORGANIZATION_ID);
1640 STPROC.BIND_I(P_BUSINESS_GROUP_ID);
1641 STPROC.EXECUTE;
1642 STPROC.RETRIEVE(1
1643 ,X0);*/
1644 RETURN NULL;
1645 END GET_ORG_NAME;
1646
1647 FUNCTION GET_CAREER_PATH_NAME(P_CAREER_PATH_ID IN NUMBER
1648 ,P_BUSINESS_GROUP_ID IN NUMBER) RETURN VARCHAR2 IS
1649 X0 VARCHAR2(2000);
1650 BEGIN
1651 /* STPROC.INIT('begin :X0 := HR_US_REPORTS.GET_CAREER_PATH_NAME(:P_CAREER_PATH_ID, :P_BUSINESS_GROUP_ID); end;');
1652 STPROC.BIND_O(X0);
1653 STPROC.BIND_I(P_CAREER_PATH_ID);
1654 STPROC.BIND_I(P_BUSINESS_GROUP_ID);
1655 STPROC.EXECUTE;
1656 STPROC.RETRIEVE(1
1657 ,X0);*/
1658 RETURN NULL;
1659 END GET_CAREER_PATH_NAME;
1660
1661 FUNCTION GET_AAP_ORG_ID(P_AAP_NAME IN VARCHAR2
1662 ,P_BUSINESS_GROUP_ID IN NUMBER) RETURN NUMBER IS
1663 X0 NUMBER;
1664 BEGIN
1665 /* STPROC.INIT('begin :X0 := HR_US_REPORTS.GET_AAP_ORG_ID(:P_AAP_NAME, :P_BUSINESS_GROUP_ID); end;');
1666 STPROC.BIND_O(X0);
1667 STPROC.BIND_I(P_AAP_NAME);
1668 STPROC.BIND_I(P_BUSINESS_GROUP_ID);
1669 STPROC.EXECUTE;
1670 STPROC.RETRIEVE(1
1671 ,X0);*/
1672 RETURN NULL;
1673 END GET_AAP_ORG_ID;
1674
1675 PROCEDURE GET_ADDRESS_31(P_LOCATION_ID IN NUMBER
1676 ,P_ADDRESS OUT NOCOPY VARCHAR2) IS
1677 BEGIN
1678 /* STPROC.INIT('begin HR_US_REPORTS.GET_ADDRESS_31(:P_LOCATION_ID, :P_ADDRESS); end;');
1679 STPROC.BIND_I(P_LOCATION_ID);
1680 STPROC.BIND_O(P_ADDRESS);
1681 STPROC.EXECUTE;
1682 STPROC.RETRIEVE(2
1683 ,P_ADDRESS);*/NULL;
1684 END GET_ADDRESS_31;
1685
1686 FUNCTION GET_LOCATION_CODE(P_LOCATION_ID IN NUMBER) RETURN VARCHAR2 IS
1687 X0 VARCHAR2(2000);
1688 BEGIN
1689 /*STPROC.INIT('begin :X0 := HR_US_REPORTS.GET_LOCATION_CODE(:P_LOCATION_ID); end;');
1690 STPROC.BIND_O(X0);
1691 STPROC.BIND_I(P_LOCATION_ID);
1692 STPROC.EXECUTE;
1693 STPROC.RETRIEVE(1
1694 ,X0);*/
1695 RETURN NULL;
1696 END GET_LOCATION_CODE;
1697
1698 PROCEDURE GET_ADDRESS_3LINES(P_PERSON_ID IN NUMBER
1699 ,P_EFFECTIVE_DATE IN DATE
1700 ,P_ADDR_LINE1 OUT NOCOPY VARCHAR2
1701 ,P_ADDR_LINE2 OUT NOCOPY VARCHAR2
1702 ,P_CITY_STATE_ZIP OUT NOCOPY VARCHAR2) IS
1703 BEGIN
1704 /* 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;');
1705 STPROC.BIND_I(P_PERSON_ID);
1706 STPROC.BIND_I(P_EFFECTIVE_DATE);
1707 STPROC.BIND_O(P_ADDR_LINE1);
1708 STPROC.BIND_O(P_ADDR_LINE2);
1709 STPROC.BIND_O(P_CITY_STATE_ZIP);
1710 STPROC.EXECUTE;
1711 STPROC.RETRIEVE(3
1712 ,P_ADDR_LINE1);
1713 STPROC.RETRIEVE(4
1714 ,P_ADDR_LINE2);
1715 STPROC.RETRIEVE(5
1716 ,P_CITY_STATE_ZIP);*/NULL;
1717 END GET_ADDRESS_3LINES;
1718
1719 FUNCTION US_TAX_BALANCE_REP(P_ASG_LOCK IN BOOLEAN
1720 ,P_TAX_BALANCE_CATEGORY IN VARCHAR2
1721 ,P_TAX_TYPE IN VARCHAR2
1722 ,P_EE_OR_ER IN VARCHAR2
1723 ,P_TIME_TYPE IN VARCHAR2
1724 ,P_ASG_TYPE IN VARCHAR2
1725 ,P_GRE_ID_CONTEXT IN NUMBER
1726 ,P_JD_CONTEXT IN VARCHAR2
1727 ,P_ASSIGNMENT_ACTION_ID IN NUMBER
1728 ,P_ASSIGNMENT_ID IN NUMBER
1729 ,P_VIRTUAL_DATE IN DATE
1730 ,P_PAYROLL_ACTION_ID IN NUMBER) RETURN NUMBER IS
1731 X0 NUMBER;
1732 BEGIN
1733 /* STPROC.INIT('declare X0P_ASG_LOCK BOOLEAN; begin X0P_ASG_LOCK := sys.diutil.int_to_bool(:P_ASG_LOCK); :X0 :=
1734 PAY_US_TAX_BALS_PKG.US_TAX_BALANCE_REP(X0P_ASG_LOCK, :P_TAX_BALANCE_CATEGORY, :P_TAX_TYPE, :P_EE_OR_ER,
1735 :P_TIME_TYPE, :P_ASG_TYPE, :P_GRE_ID_CONTEXT, :P_JD_CONTEXT, :P_ASSIGNMENT_ACTION_ID, :P_ASSIGNMENT_ID, :P_VIRTUAL_DATE, :P_PAYROLL_ACTION_ID); end;');
1736 STPROC.BIND_I(P_ASG_LOCK);
1737 STPROC.BIND_O(X0);
1738 STPROC.BIND_I(P_TAX_BALANCE_CATEGORY);
1739 STPROC.BIND_I(P_TAX_TYPE);
1740 STPROC.BIND_I(P_EE_OR_ER);
1741 STPROC.BIND_I(P_TIME_TYPE);
1742 STPROC.BIND_I(P_ASG_TYPE);
1743 STPROC.BIND_I(P_GRE_ID_CONTEXT);
1744 STPROC.BIND_I(P_JD_CONTEXT);
1745 STPROC.BIND_I(P_ASSIGNMENT_ACTION_ID);
1746 STPROC.BIND_I(P_ASSIGNMENT_ID);
1747 STPROC.BIND_I(P_VIRTUAL_DATE);
1748 STPROC.BIND_I(P_PAYROLL_ACTION_ID);
1749 STPROC.EXECUTE;
1750 STPROC.RETRIEVE(2
1751 ,X0);*/
1752 RETURN NULL;
1753 END US_TAX_BALANCE_REP;
1754
1755 FUNCTION US_TAX_BALANCE_REP(P_ASG_LOCK IN BOOLEAN
1756 ,P_TAX_BALANCE_CATEGORY IN VARCHAR2
1757 ,P_TAX_TYPE IN VARCHAR2
1758 ,P_EE_OR_ER IN VARCHAR2
1759 ,P_TIME_TYPE IN VARCHAR2
1760 ,P_ASG_TYPE IN VARCHAR2
1761 ,P_GRE_ID_CONTEXT IN NUMBER
1762 ,P_JD_CONTEXT IN VARCHAR2
1763 ,P_ASSIGNMENT_ACTION_ID IN NUMBER
1764 ,P_ASSIGNMENT_ID IN NUMBER
1765 ,P_VIRTUAL_DATE IN DATE) RETURN NUMBER IS
1766 X0 NUMBER;
1767 BEGIN
1768 /* STPROC.INIT('declare X0P_ASG_LOCK BOOLEAN; begin X0P_ASG_LOCK := sys.diutil.int_to_bool(:P_ASG_LOCK); :X0 := PAY_US_TAX_BALS_PKG.US_TAX_BALANCE_REP(X0P_ASG_LOCK,
1769 :P_TAX_BALANCE_CATEGORY, :P_TAX_TYPE, :P_EE_OR_ER, :P_TIME_TYPE, :P_ASG_TYPE, :P_GRE_ID_CONTEXT, :P_JD_CONTEXT, :P_ASSIGNMENT_ACTION_ID, :P_ASSIGNMENT_ID, :P_VIRTUAL_DATE); end;');
1770 STPROC.BIND_I(P_ASG_LOCK);
1771 STPROC.BIND_O(X0);
1772 STPROC.BIND_I(P_TAX_BALANCE_CATEGORY);
1773 STPROC.BIND_I(P_TAX_TYPE);
1774 STPROC.BIND_I(P_EE_OR_ER);
1775 STPROC.BIND_I(P_TIME_TYPE);
1776 STPROC.BIND_I(P_ASG_TYPE);
1777 STPROC.BIND_I(P_GRE_ID_CONTEXT);
1778 STPROC.BIND_I(P_JD_CONTEXT);
1779 STPROC.BIND_I(P_ASSIGNMENT_ACTION_ID);
1780 STPROC.BIND_I(P_ASSIGNMENT_ID);
1781 STPROC.BIND_I(P_VIRTUAL_DATE);
1782 STPROC.EXECUTE;
1783 STPROC.RETRIEVE(2
1784 ,X0);*/
1785 RETURN NULL;
1786 END US_TAX_BALANCE_REP;
1787
1788 FUNCTION US_TAX_BALANCE(P_TAX_BALANCE_CATEGORY IN VARCHAR2
1789 ,P_TAX_TYPE IN VARCHAR2
1790 ,P_EE_OR_ER IN VARCHAR2
1791 ,P_TIME_TYPE IN VARCHAR2
1792 ,P_ASG_TYPE IN VARCHAR2
1793 ,P_GRE_ID_CONTEXT IN NUMBER
1794 ,P_JD_CONTEXT IN VARCHAR2
1795 ,P_ASSIGNMENT_ACTION_ID IN NUMBER
1796 ,P_ASSIGNMENT_ID IN NUMBER
1797 ,P_VIRTUAL_DATE IN DATE) RETURN NUMBER IS
1798 X0 NUMBER;
1799 BEGIN
1800 /*STPROC.INIT('begin :X0 := PAY_US_TAX_BALS_PKG.US_TAX_BALANCE(:P_TAX_BALANCE_CATEGORY, :P_TAX_TYPE, :P_EE_OR_ER, :P_TIME_TYPE, :P_ASG_TYPE, :P_GRE_ID_CONTEXT, :P_JD_CONTEXT, :P_ASSIGNMENT_ACTION_ID, :P_ASSIGNMENT_ID, :P_VIRTUAL_DATE); end;');
1801 STPROC.BIND_O(X0);
1802 STPROC.BIND_I(P_TAX_BALANCE_CATEGORY);
1803 STPROC.BIND_I(P_TAX_TYPE);
1804 STPROC.BIND_I(P_EE_OR_ER);
1805 STPROC.BIND_I(P_TIME_TYPE);
1806 STPROC.BIND_I(P_ASG_TYPE);
1807 STPROC.BIND_I(P_GRE_ID_CONTEXT);
1808 STPROC.BIND_I(P_JD_CONTEXT);
1809 STPROC.BIND_I(P_ASSIGNMENT_ACTION_ID);
1810 STPROC.BIND_I(P_ASSIGNMENT_ID);
1811 STPROC.BIND_I(P_VIRTUAL_DATE);
1812 STPROC.EXECUTE;
1813 STPROC.RETRIEVE(1
1814 ,X0);*/
1815 RETURN NULL;
1816 END US_TAX_BALANCE;
1817
1818 FUNCTION US_TAX_BALANCE(P_TAX_BALANCE_CATEGORY IN VARCHAR2
1819 ,P_TAX_TYPE IN VARCHAR2
1820 ,P_EE_OR_ER IN VARCHAR2
1821 ,P_TIME_TYPE IN VARCHAR2
1822 ,P_ASG_TYPE IN VARCHAR2
1823 ,P_GRE_ID_CONTEXT IN NUMBER
1824 ,P_JD_CONTEXT IN VARCHAR2
1825 ,P_ASSIGNMENT_ACTION_ID IN NUMBER
1826 ,P_ASSIGNMENT_ID IN NUMBER
1827 ,P_VIRTUAL_DATE IN DATE
1828 ,P_PAYROLL_ACTION_ID IN NUMBER) RETURN NUMBER IS
1829 X0 NUMBER;
1830 BEGIN
1831 /* STPROC.INIT('begin :X0 := PAY_US_TAX_BALS_PKG.US_TAX_BALANCE(:P_TAX_BALANCE_CATEGORY, :P_TAX_TYPE, :P_EE_OR_ER, :P_TIME_TYPE, :P_ASG_TYPE,
1832 :P_GRE_ID_CONTEXT, :P_JD_CONTEXT, :P_ASSIGNMENT_ACTION_ID, :P_ASSIGNMENT_ID, :P_VIRTUAL_DATE, :P_PAYROLL_ACTION_ID); end;');
1833 STPROC.BIND_O(X0);
1834 STPROC.BIND_I(P_TAX_BALANCE_CATEGORY);
1835 STPROC.BIND_I(P_TAX_TYPE);
1836 STPROC.BIND_I(P_EE_OR_ER);
1837 STPROC.BIND_I(P_TIME_TYPE);
1838 STPROC.BIND_I(P_ASG_TYPE);
1839 STPROC.BIND_I(P_GRE_ID_CONTEXT);
1840 STPROC.BIND_I(P_JD_CONTEXT);
1841 STPROC.BIND_I(P_ASSIGNMENT_ACTION_ID);
1842 STPROC.BIND_I(P_ASSIGNMENT_ID);
1843 STPROC.BIND_I(P_VIRTUAL_DATE);
1844 STPROC.BIND_I(P_PAYROLL_ACTION_ID);
1845 STPROC.EXECUTE;
1846 STPROC.RETRIEVE(1
1847 ,X0);*/
1848 RETURN NULL;
1849 END US_TAX_BALANCE;
1850
1851 FUNCTION US_TAX_BALANCE(P_TAX_BALANCE_CATEGORY IN VARCHAR2
1852 ,P_TAX_TYPE IN VARCHAR2
1853 ,P_EE_OR_ER IN VARCHAR2
1854 ,P_TIME_TYPE IN VARCHAR2
1855 ,P_ASG_TYPE IN VARCHAR2
1856 ,P_GRE_ID_CONTEXT IN NUMBER
1857 ,P_JD_CONTEXT IN VARCHAR2
1858 ,P_ASSIGNMENT_ACTION_ID IN NUMBER
1859 ,P_ASSIGNMENT_ID IN NUMBER
1860 ,P_VIRTUAL_DATE IN DATE
1861 ,P_PAYROLL_ACTION_ID IN NUMBER
1862 ,P_ASG_LOCK IN BOOLEAN) RETURN NUMBER IS
1863 X0 NUMBER;
1864 BEGIN
1865 /* STPROC.INIT('declare X0P_ASG_LOCK BOOLEAN; begin X0P_ASG_LOCK := sys.diutil.int_to_bool(:P_ASG_LOCK); :X0 := PAY_US_TAX_BALS_PKG.US_TAX_BALANCE(
1866 :P_TAX_BALANCE_CATEGORY, :P_TAX_TYPE, :P_EE_OR_ER, :P_TIME_TYPE, :P_ASG_TYPE, :P_GRE_ID_CONTEXT, :P_JD_CONTEXT, :P_ASSIGNMENT_ACTION_ID, :P_ASSIGNMENT_ID,
1867 :P_VIRTUAL_DATE, :P_PAYROLL_ACTION_ID, X0P_ASG_LOCK); end;');
1868 STPROC.BIND_I(P_ASG_LOCK);
1869 STPROC.BIND_O(X0);
1870 STPROC.BIND_I(P_TAX_BALANCE_CATEGORY);
1871 STPROC.BIND_I(P_TAX_TYPE);
1872 STPROC.BIND_I(P_EE_OR_ER);
1873 STPROC.BIND_I(P_TIME_TYPE);
1874 STPROC.BIND_I(P_ASG_TYPE);
1875 STPROC.BIND_I(P_GRE_ID_CONTEXT);
1876 STPROC.BIND_I(P_JD_CONTEXT);
1877 STPROC.BIND_I(P_ASSIGNMENT_ACTION_ID);
1878 STPROC.BIND_I(P_ASSIGNMENT_ID);
1879 STPROC.BIND_I(P_VIRTUAL_DATE);
1880 STPROC.BIND_I(P_PAYROLL_ACTION_ID);
1881 STPROC.EXECUTE;
1882 STPROC.RETRIEVE(2
1883 ,X0);*/
1884 RETURN NULL;
1885 END US_TAX_BALANCE;
1886
1887 function R_State1FormatTrigger(C_EXPERIENCE_RATE_1 IN varchar2,
1888 c_experience_rate2 IN Varchar2,
1889 C_TAXABLE_PAYROLL IN varchar2) return boolean is
1890 l_exp1 number := 0;
1891 l_exp2 number := 0;
1892 begin
1893 if c_experience_rate_1 = 'N/A' then
1894 l_exp1 := 0;
1895 else
1896 l_exp1 := nvl(to_number(c_experience_rate_1),0);
1897 end if;
1898
1899 if (c_experience_rate2 = 'N/A' or c_experience_rate2 is null) then
1900 l_exp2 := 0;
1901 else
1902 l_exp2 := nvl(to_number(c_experience_rate2),0);
1903 end if;
1904 if (l_exp1 > 5.4) or (l_exp2 > 5.4) then
1905 return (FALSE);
1906 end if;
1907
1908 If c_taxable_payroll = 0 or c_taxable_payroll IS NULL then
1909 return(FALSE);
1910 else
1911 return(TRUE);
1912 end if;
1913
1914
1915 return (TRUE);
1916
1917 end;
1918 END PAY_PAYRP940_XMLP_PKG;