[Home] [Help]
PACKAGE BODY: APPS.PAY_JP_BALANCE_PKG
Source
1 PACKAGE BODY PAY_JP_BALANCE_PKG AS
2 /* $Header: pyjpblnc.pkb 120.1.12000000.3 2007/05/21 08:28:49 keyazawa noship $ */
3 --
4 -- Cache the action parameter
5 --
6 cached boolean := FALSE;
7 g_low_volume pay_action_parameters.parameter_value%type := 'N';
8 --
9 --===============================================================================
10 FUNCTION get_business_group_id(p_assignment_action_id IN PAY_ASSIGNMENT_ACTIONS.ASSIGNMENT_ACTION_ID%TYPE)
11 --===============================================================================
12 RETURN NUMBER
13 IS
14 l_business_group_id PER_BUSINESS_GROUPS.BUSINESS_GROUP_ID%TYPE;
15
16 CURSOR get_business_group_id IS
17 select /*+ ORDERED
18 USE_NL(PAA, PPA)
19 INDEX(PAY_ASSIGNMENT_ACTIONS_PK PAA)
20 INDEX(PAY_PAYROLL_ACTIONS_PK PPA) */
21 ppa.business_group_id
22 from pay_assignment_actions paa,
23 pay_payroll_actions ppa
24 where paa.assignment_action_id = p_assignment_action_id
25 and ppa.payroll_action_id = paa.payroll_action_id;
26 BEGIN
27 OPEN get_business_group_id;
28 FETCH get_business_group_id INTO l_business_group_id;
29 if get_business_group_id%NOTFOUND then
30 l_business_group_id := NULL;
31 end if;
32 CLOSE get_business_group_id;
33
34 return l_business_group_id;
35 END get_business_group_id;
36 --
37 --===============================================================================
38 FUNCTION get_business_group_id(
39 p_assignment_id IN PER_ASSIGNMENTS_F.ASSIGNMENT_ID%TYPE,
40 p_effective_date IN DATE)
41 --===============================================================================
42 RETURN NUMBER
43 IS
44 l_business_group_id PER_BUSINESS_GROUPS.BUSINESS_GROUP_ID%TYPE;
45
46 CURSOR get_business_group_id IS
47 select /*+ INDEX(PER_ASSIGNMENTS_F_PK PA) */
48 pa.business_group_id
49 from per_assignments_f pa
50 where pa.assignment_id = p_assignment_id
51 and p_effective_date
52 between pa.effective_start_date and pa.effective_end_date;
53 BEGIN
54 OPEN get_business_group_id;
55 FETCH get_business_group_id INTO l_business_group_id;
56 if get_business_group_id%NOTFOUND then
57 l_business_group_id := NULL;
58 end if;
59 CLOSE get_business_group_id;
60
61 return l_business_group_id;
62 END get_business_group_id;
63 --
64 --===============================================================================
68 IS
65 FUNCTION get_legislation_code(p_business_group_id IN PER_BUSINESS_GROUPS.BUSINESS_GROUP_ID%TYPE)
66 --===============================================================================
67 RETURN VARCHAR2
69 l_legislation_code PER_BUSINESS_GROUPS.LEGISLATION_CODE%TYPE;
70
71 CURSOR get_legislation_code IS
72 select pbg.legislation_code
73 from per_business_groups pbg
74 where pbg.business_group_id = p_business_group_id;
75 BEGIN
76 OPEN get_legislation_code;
77 FETCH get_legislation_code INTO l_legislation_code;
78 if get_legislation_code%NOTFOUND then
79 l_legislation_code := NULL;
80 end if;
81 CLOSE get_legislation_code;
82
83 return l_legislation_code;
84 END get_legislation_code;
85 --
86 --===============================================================================
87 PROCEDURE get_element_input_id(
88 p_element_name IN PAY_ELEMENT_TYPES_F.ELEMENT_NAME%TYPE,
89 p_input_value_name IN PAY_INPUT_VALUES_F.NAME%TYPE,
90 p_business_group_id IN PER_BUSINESS_GROUPS.BUSINESS_GROUP_ID%TYPE,
91 p_element_type_id IN OUT NOCOPY NUMBER,
92 p_input_value_id IN OUT NOCOPY NUMBER)
93 --===============================================================================
94 IS
95 l_legislation_code PER_BUSINESS_GROUPS.LEGISLATION_CODE%TYPE;
96 BEGIN
97 l_legislation_code:=get_legislation_code(p_business_group_id);
98 if l_legislation_code is NULL then
99 p_element_type_id:=NULL;
100 p_input_value_id:=NULL;
101 raise NO_DATA_FOUND;
102 end if;
103
104 p_element_type_id:=get_element_type_id(p_element_name,p_business_group_id,l_legislation_code);
105 if p_element_type_id is NULL then
106 p_input_value_id:=NULL;
107 raise NO_DATA_FOUND;
108 end if;
109
110 p_input_value_id:=get_input_value_id(p_element_type_id,p_input_value_name);
111 if p_input_value_id is NULL then
112 p_element_type_id:=NULL;
113 p_input_value_id:=NULL;
114 raise NO_DATA_FOUND;
115 end if;
116 EXCEPTION
117 when NO_DATA_FOUND then
118 NULL;
119 END get_element_input_id;
120 --
121 --===============================================================================
122 FUNCTION get_defined_balance_id(p_balance_name IN PAY_BALANCE_TYPES.BALANCE_NAME%TYPE,
123 p_dimension_name IN PAY_BALANCE_DIMENSIONS.DIMENSION_NAME%TYPE,
124 p_business_group_id IN PER_BUSINESS_GROUPS.BUSINESS_GROUP_ID%TYPE)
125 --===============================================================================
126 RETURN NUMBER
127 IS
128 l_defined_balance_id NUMBER;
129 l_legislation_code PER_BUSINESS_GROUPS.LEGISLATION_CODE%TYPE;
130
131 CURSOR get_defined_balance_id IS
132 select /*+ ORDERED
133 USE_NL(PBT, PDB, PBD)
134 INDEX(PAY_BALANCE_TYPES_UK2 PBT)
135 INDEX(PAY_DEFINED_BALANCES_UK2 PDB)
136 INDEX(PAY_BALANCE_DIMENSIONS_PK PBD) */
137 pdb.defined_balance_id
138 from pay_balance_types pbt,
139 pay_defined_balances pdb,
140 pay_balance_dimensions pbd
141 where pbt.balance_name = p_balance_name
142 and nvl(pbt.business_group_id,p_business_group_id) = p_business_group_id
143 and nvl(pbt.legislation_code,l_legislation_code) = l_legislation_code
144 and pbd.dimension_name = p_dimension_name
145 and nvl(pbd.business_group_id,p_business_group_id) = p_business_group_id
146 and nvl(pbd.legislation_code,l_legislation_code) = l_legislation_code
147 and pdb.balance_type_id = pbt.balance_type_id
148 and pdb.balance_dimension_id = pbd.balance_dimension_id;
149
150 BEGIN
151 l_legislation_code := get_legislation_code(p_business_group_id);
152 if l_legislation_code is NULL then
153 return NULL;
154 end if;
155
156 OPEN get_defined_balance_id;
157 FETCH get_defined_balance_id INTO l_defined_balance_id;
158 if get_defined_balance_id%NOTFOUND then
159 l_defined_balance_id := NULL;
160 end if;
161 CLOSE get_defined_balance_id;
162
163 return l_defined_balance_id;
164 END get_defined_balance_id;
165
166 --===============================================================================
167 FUNCTION GET_BALANCE_TYPE_ID(
168 p_balance_name IN PAY_BALANCE_TYPES.BALANCE_NAME%TYPE,
169 p_business_group_id IN PER_BUSINESS_GROUPS.BUSINESS_GROUP_ID%TYPE,
170 p_legislation_code IN PER_BUSINESS_GROUPS.LEGISLATION_CODE%TYPE)
171 --===============================================================================
172 RETURN NUMBER
173 IS
174 l_balance_type_id NUMBER;
175
176 CURSOR cur_balance_type_id IS
177 select /*+ INDEX(PAY_BALANCE_TYPES_UK2 PBT) */
178 pbt.balance_type_id
179 from pay_balance_types pbt
180 where pbt.balance_name = p_balance_name
181 and nvl(pbt.business_group_id,p_business_group_id) = p_business_group_id
182 and nvl(pbt.legislation_code,p_legislation_code) = p_legislation_code;
183 BEGIN
184 OPEN cur_balance_type_id;
185 FETCH cur_balance_type_id INTO l_balance_type_id;
186 if cur_balance_type_id%NOTFOUND then
187 l_balance_type_id := NULL;
188 end if;
189 CLOSE cur_balance_type_id;
190
191 return l_balance_type_id;
192 END GET_BALANCE_TYPE_ID;
193
194 --------------------------------------------------------------
195 -- GET_BALANCE_VALUE (action mode) --
196 --------------------------------------------------------------
197 FUNCTION GET_BALANCE_VALUE(
201 RETURN NUMBER
198 P_BALANCE_NAME IN PAY_BALANCE_TYPES.BALANCE_NAME%TYPE,
199 P_DIMENSION_NAME IN PAY_BALANCE_DIMENSIONS.DIMENSION_NAME%TYPE,
200 P_ASSIGNMENT_ACTION_ID IN NUMBER)
202 IS
203 l_business_group_id PER_BUSINESS_GROUPS.BUSINESS_GROUP_ID%TYPE;
204 l_defined_balance_id PAY_DEFINED_BALANCES.DEFINED_BALANCE_ID%TYPE;
205 l_result_value NUMBER;
206 BEGIN
207 l_result_value := 0;
208
209 l_business_group_id := get_business_group_id(p_assignment_action_id);
210 if l_business_group_id is NULL then
211 return l_result_value;
212 end if;
213
214 l_defined_balance_Id := get_defined_balance_id(p_balance_name,p_dimension_name,l_business_group_id);
215 if l_defined_balance_id is NULL then
216 return l_result_value;
217 end if;
218
219 l_result_value := get_balance_value(
220 l_defined_balance_id,
221 p_assignment_action_id);
222
223 return l_result_value;
224 END GET_BALANCE_VALUE;
225
226 --------------------------------------------------------------
227 -- GET_BALANCE_VALUE (action mode) --
228 --------------------------------------------------------------
229 FUNCTION GET_BALANCE_VALUE(
230 P_DEFINED_BALANCE_ID IN NUMBER,
231 P_ASSIGNMENT_ACTION_ID IN NUMBER)
232 RETURN NUMBER
233 IS
234 l_business_group_id PER_BUSINESS_GROUPS.BUSINESS_GROUP_ID%TYPE;
235 l_defined_balance_id PAY_DEFINED_BALANCES.DEFINED_BALANCE_ID%TYPE;
236 l_result_value NUMBER;
237 BEGIN
238 l_result_value := 0;
239
240 l_result_value := pay_balance_pkg.get_value(
241 p_defined_balance_id,
242 p_assignment_action_id);
243
244 return l_result_value;
245 END GET_BALANCE_VALUE;
246
247 -------------------------------------------------------------
248 -- GET_BALANCE_VALUE (date mode) --
249 -------------------------------------------------------------
250 FUNCTION GET_BALANCE_VALUE(
251 P_BALANCE_NAME IN PAY_BALANCE_TYPES.BALANCE_NAME%TYPE,
252 P_DIMENSION_NAME IN PAY_BALANCE_DIMENSIONS.DIMENSION_NAME%TYPE,
253 P_ASSIGNMENT_ID IN NUMBER,
254 P_EFFECTIVE_DATE IN DATE)
255 RETURN NUMBER
256 IS
257 l_business_group_id PER_BUSINESS_GROUPS.BUSINESS_GROUP_ID%TYPE;
258 l_defined_balance_id PAY_DEFINED_BALANCES.DEFINED_BALANCE_ID%TYPE;
259 l_result_value NUMBER;
260 BEGIN
261 l_result_value := 0;
262
263 l_business_group_id := get_business_group_id(p_assignment_id,p_effective_date);
264 if l_business_group_id is NULL then
265 return l_result_value;
266 end if;
267
268 l_defined_balance_Id := get_defined_balance_id(p_balance_name,p_dimension_name,l_business_group_id);
269 if l_defined_balance_id is NULL then
270 return l_result_value;
271 end if;
272
273 l_result_value := get_balance_value(
274 l_defined_balance_id,
275 p_assignment_id,
276 p_effective_date);
277
278 return l_result_value;
279 END GET_BALANCE_VALUE;
280
281 -------------------------------------------------------------
282 -- GET_BALANCE_VALUE (date mode) --
283 -------------------------------------------------------------
284 FUNCTION GET_BALANCE_VALUE(
285 P_DEFINED_BALANCE_ID IN NUMBER,
286 P_ASSIGNMENT_ID IN NUMBER,
287 P_EFFECTIVE_DATE IN DATE)
288 RETURN NUMBER
289 IS
290 l_result_value NUMBER;
291 BEGIN
292 l_result_value := 0;
293
294 -- If the specified assignment is not linked to payroll,
295 -- pay_balance_pkg causes no_data_found at line 1262.
296 BEGIN
297 l_result_value := pay_balance_pkg.get_value(
298 p_defined_balance_id,
299 p_assignment_id,
300 p_effective_date);
301 EXCEPTION
302 WHEN no_data_found THEN
303 l_result_value := 0;
304 END;
305
306 return l_result_value;
307 END GET_BALANCE_VALUE;
308
309 -------------------------------------------------------------
310 -- GET_BALANCE_VALUE_ASG_RUN (for _ASG_RUN dimension only) --
311 -------------------------------------------------------------
312 FUNCTION GET_BALANCE_VALUE_ASG_RUN(
313 P_BALANCE_TYPE_ID IN NUMBER,
314 P_ASSIGNMENT_ACTION_ID IN NUMBER)
315 RETURN NUMBER
316 IS
317 l_result_value NUMBER;
318 l_defined_balance_id PAY_DEFINED_BALANCES.DEFINED_BALANCE_ID%TYPE;
319 CURSOR cur_balance_value_asg_run IS
320 SELECT /*+ ORDERED
321 USE_NL(ASSACT, PACT, FEED, RR, TARGET)
322 INDEX(PAY_ASSIGNMENT_ACTIONS_PK ASSACT)
323 INDEX(PAY_PAYROLL_ACTIONS_PK PACT)
324 INDEX(PAY_BALANCE_FEEDS_F_FK1 FEED)
325 INDEX(PAY_RUN_RESULTS_N50 RR)
326 INDEX(PAY_RUN_RESULT_VALUES_PK TARGET) */
327 nvl(sum(fnd_number.canonical_to_number(TARGET.result_value) * FEED.scale),0)
328 FROM pay_assignment_actions ASSACT,
329 pay_payroll_actions PACT,
330 pay_balance_feeds_f FEED,
331 pay_run_results RR,
332 pay_run_result_values TARGET
333 where ASSACT.assignment_action_id = p_assignment_action_id
334 and PACT.payroll_action_id = ASSACT.payroll_action_id
338 and FEED.input_value_id = TARGET.input_value_id
335 and RR.assignment_action_id = ASSACT.assignment_action_id
336 and RR.status in ('P','PA')
337 and TARGET.run_result_id = RR.run_result_id
339 and FEED.balance_type_id = p_balance_type_id
340 and PACT.effective_date between
341 FEED.effective_start_date and FEED.effective_end_date;
342
343 CURSOR cur_balance_value_asg_run_rule IS
344 -- =============================================================================
345 -- Fix bug#3331016: Removed RULE hint from statement.
346 -- -----------------------------------------------------------------------------
347 SELECT /*+ ORDERED
348 USE_NL(ASSACT, PACT, FEED, RR, TARGET)
349 INDEX(PAY_ASSIGNMENT_ACTIONS_PK ASSACT)
350 INDEX(PAY_PAYROLL_ACTIONS_PK PACT)
351 INDEX(PAY_BALANCE_FEEDS_F_FK1 FEED)
352 INDEX(PAY_RUN_RESULTS_N50 RR)
353 INDEX(PAY_RUN_RESULT_VALUES_PK TARGET) */
354 nvl(sum(fnd_number.canonical_to_number(TARGET.result_value) * FEED.scale),0)
355 FROM
356 pay_assignment_actions ASSACT,
357 pay_payroll_actions PACT,
358 pay_balance_feeds_f FEED,
359 pay_run_results RR,
360 pay_run_result_values TARGET
361 where ASSACT.assignment_action_id = p_assignment_action_id
362 and PACT.payroll_action_id = ASSACT.payroll_action_id
363 and RR.assignment_action_id = ASSACT.assignment_action_id
364 and RR.status in ('P','PA')
365 and TARGET.run_result_id = RR.run_result_id
366 and FEED.input_value_id = TARGET.input_value_id
367 and FEED.balance_type_id = p_balance_type_id
368 and PACT.effective_date between
369 FEED.effective_start_date and FEED.effective_end_date;
370 BEGIN
371 --
372 -- Use Rule hint on balances if LOW_VOLUME pay_action_paremeter set
373 --
374 l_defined_balance_id := pay_jp_balance_pkg.get_defined_balance_id (p_balance_type_id,p_assignment_action_id);
375 if (cached = FALSE) then
376 cached := TRUE;
377 begin
378 select parameter_value
379 into g_low_volume
380 from pay_action_parameters
381 where parameter_name = 'LOW_VOLUME';
382 exception
383 when others then
384 g_low_volume := 'N';
385 end;
386 end if;
387 IF l_defined_balance_id is not null THEN
388 l_result_value := pay_balance_pkg.get_value(l_defined_balance_id, p_assignment_action_id);
389 ELSE
390 if (g_low_volume = 'Y') then
391 OPEN cur_balance_value_asg_run_rule;
392 FETCH cur_balance_value_asg_run_rule INTO l_result_value;
393 if cur_balance_value_asg_run_rule%NOTFOUND then
394 l_result_value := 0;
395 end if;
396 CLOSE cur_balance_value_asg_run_rule;
397 else
398 OPEN cur_balance_value_asg_run;
399 FETCH cur_balance_value_asg_run INTO l_result_value;
400 if cur_balance_value_asg_run%NOTFOUND then
401 l_result_value := 0;
402 end if;
403 CLOSE cur_balance_value_asg_run;
404 end if;
405 END IF;
406 return l_result_value;
407 END GET_BALANCE_VALUE_ASG_RUN;
408
409 -----------------------------------------------------------
410 -- GET_RESULT_VALUE_PAY_VALUE --
411 -----------------------------------------------------------
412 FUNCTION GET_RESULT_VALUE_PAY_VALUE(
413 P_ELEMENT_NAME IN PAY_ELEMENT_TYPES_F.ELEMENT_NAME%TYPE,
414 P_ASSIGNMENT_ACTION_ID IN PAY_ASSIGNMENT_ACTIONS.ASSIGNMENT_ACTION_ID%TYPE)
415 RETURN NUMBER
416 IS
417 l_business_group_id PER_BUSINESS_GROUPS.BUSINESS_GROUP_ID%TYPE;
418 l_element_type_id PAY_ELEMENT_TYPES_F.ELEMENT_TYPE_ID%TYPE;
419 l_input_value_id PAY_INPUT_VALUES_F.INPUT_VALUE_ID%TYPE;
420 l_result_value NUMBER;
421 BEGIN
422 l_result_value := NULL;
423
424 l_business_group_id := get_business_group_id(p_assignment_action_id);
425 if l_business_group_id is NULL then
426 return l_result_value;
427 end if;
428
429 get_element_input_id(
430 p_element_name => p_element_name,
431 --bug#2002696
432 -- p_input_value_name => hr_general.decode_lookup('NAME_TRANSLATIONS','PAY VALUE'),
433 --bug#2002696
434 p_input_value_name => 'Pay Value',
435 p_business_group_id => l_business_group_id,
436 p_element_type_id => l_element_type_id,
437 p_input_value_id => l_input_value_id);
438 if l_element_type_id is NULL or l_input_value_id is NULL then
439 return l_result_value;
440 end if;
441
442 -- Modified by keyazawa at 2003/09/03 for bug#3088039
443 l_result_value:=get_result_value_number(l_element_type_id,l_input_value_id,p_assignment_action_id);
444
445 return l_result_value;
446 END GET_RESULT_VALUE_PAY_VALUE;
447
448 -----------------------------------------------------------
449 -- GET_RESULT_VALUE_PAY_VALUE --
450 -----------------------------------------------------------
451 FUNCTION GET_RESULT_VALUE_PAY_VALUE(
452 P_ELEMENT_TYPE_ID IN NUMBER,
453 P_INPUT_VALUE_ID IN NUMBER,
454 P_ASSIGNMENT_ACTION_ID IN NUMBER)
455 RETURN NUMBER
456 IS
457 -- Modified by keyazawa at 2003/09/03 for bug#3088039
458 l_result_value number;
459 --
463 CURSOR get_result_value_pay_value IS
460 -- This cursor doesn't check action_type.
461 -- This cursor restrict optimizer not to use
462 -- PAY_RUN_RESULTS_N1 index in PAY_RUN_RESULTS.
464 select /*+ ORDERED
465 USE_NL(PAA, PPA, PRR, PRRV)
466 INDEX(PAY_ASSIGNMENT_ACTIONS_PK PAA)
467 INDEX(PAY_PAYROLL_ACTIONS_PK PPA)
468 INDEX(PAY_RUN_RESULTS_N50 PRR)
469 INDEX(PAY_RUN_RESULT_VALUES_PK PRRV) */
470 sum(fnd_number.canonical_to_number(prrv.result_value))
471 from pay_assignment_actions paa,
472 pay_payroll_actions ppa,
473 pay_run_results prr,
474 pay_run_result_values prrv
475 where paa.assignment_action_id = p_assignment_action_id
476 and ppa.payroll_action_id = paa.payroll_action_id
477 and prr.assignment_action_id = paa.assignment_action_id
478 and prr.element_type_id + 0 = p_element_type_id
479 and prr.status in ('P','PA')
480 and prrv.run_result_id = prr.run_result_id
481 and prrv.input_value_id = p_input_value_id;
482 BEGIN
483 l_result_value := NULL;
484
485 OPEN get_result_value_pay_value;
486 FETCH get_result_value_pay_value INTO l_result_value;
487 if get_result_value_pay_value%NOTFOUND then
488 l_result_value := NULL;
489 end if;
490 CLOSE get_result_value_pay_value;
491
492 return l_result_value;
493 END GET_RESULT_VALUE_PAY_VALUE;
494
495 -----------------------------------------------------------
496 -- GET_RESULT_VALUE_CHAR --
497 -----------------------------------------------------------
498 FUNCTION GET_RESULT_VALUE_CHAR(
499 P_ELEMENT_NAME IN PAY_ELEMENT_TYPES_F.ELEMENT_NAME%TYPE,
500 P_INPUT_VALUE_NAME IN PAY_INPUT_VALUES_F.NAME%TYPE,
501 P_ASSIGNMENT_ACTION_ID IN PAY_ASSIGNMENT_ACTIONS.ASSIGNMENT_ACTION_ID%TYPE)
502 RETURN VARCHAR2
503 IS
504 l_business_group_id PER_BUSINESS_GROUPS.BUSINESS_GROUP_ID%TYPE;
505 l_element_type_id PAY_ELEMENT_TYPES_F.ELEMENT_TYPE_ID%TYPE;
506 l_input_value_id PAY_INPUT_VALUES_F.INPUT_VALUE_ID%TYPE;
507 l_result_value PAY_RUN_RESULT_VALUES.RESULT_VALUE%TYPE;
508 BEGIN
509 l_result_value := NULL;
510
511 l_business_group_id := get_business_group_id(p_assignment_action_id);
512 if l_business_group_id is NULL then
513 return l_result_value;
514 end if;
515
516 get_element_input_id(
517 p_element_name => p_element_name,
518 p_input_value_name => p_input_value_name,
519 p_business_group_id => l_business_group_id,
520 p_element_type_id => l_element_type_id,
521 p_input_value_id => l_input_value_id);
522 if l_element_type_id is NULL or l_input_value_id is NULL then
523 return l_result_value;
524 end if;
525
526 l_result_value:=get_result_value_char(l_element_type_id,l_input_value_id,p_assignment_action_id);
527
528 return l_result_value;
529 END get_result_value_char;
530
531 -----------------------------------------------------------
532 -- GET_RESULT_VALUE_CHAR --
533 -----------------------------------------------------------
534 FUNCTION GET_RESULT_VALUE_CHAR(
535 P_ELEMENT_TYPE_ID IN NUMBER,
536 P_INPUT_VALUE_ID IN NUMBER,
537 P_ASSIGNMENT_ACTION_ID IN PAY_ASSIGNMENT_ACTIONS.ASSIGNMENT_ACTION_ID%TYPE)
538 RETURN VARCHAR2
539 IS
540 l_result_value PAY_RUN_RESULT_VALUES.RESULT_VALUE%TYPE;
541 -- This cursor doesn't check action_type.
542 -- This cursor restrict optimizer not to use
543 -- PAY_RUN_RESULTS_N1 index in PAY_RUN_RESULTS.
544 CURSOR get_result_value IS
545 select /*+ ORDERED
546 USE_NL(PAA, PPA, PRR, PRRV)
547 INDEX(PAY_ASSIGNMENT_ACTIONS_PK PAA)
548 INDEX(PAY_PAYROLL_ACTIONS_PK PPA)
549 INDEX(PAY_RUN_RESULTS_N50 PRR)
550 INDEX(PAY_RUN_RESULT_VALUES_PK PRRV) */
551 min(prrv.result_value)
552 from pay_assignment_actions paa,
553 pay_payroll_actions ppa,
554 pay_run_results prr,
555 pay_run_result_values prrv
556 where paa.assignment_action_id = p_assignment_action_id
557 and ppa.payroll_action_id = paa.payroll_action_id
558 and prr.assignment_action_id = paa.assignment_action_id
559 and prr.element_type_id + 0 = p_element_type_id
560 and prr.status in ('P','PA')
561 and prrv.run_result_id = prr.run_result_id
562 and prrv.input_value_id = p_input_value_id;
563 BEGIN
564 l_result_value := NULL;
565
566 OPEN get_result_value;
567 FETCH get_result_value INTO l_result_value;
568 if get_result_value%NOTFOUND then
569 l_result_value := NULL;
570 end if;
571 CLOSE get_result_value;
572
573 return l_result_value;
574 END GET_RESULT_VALUE_CHAR;
575
576 -----------------------------------------------------------
577 -- GET_RESULT_VALUE_NUMBER --
578 -----------------------------------------------------------
579 FUNCTION GET_RESULT_VALUE_NUMBER(
580 P_ELEMENT_NAME IN PAY_ELEMENT_TYPES_F.ELEMENT_NAME%TYPE,
581 P_INPUT_VALUE_NAME IN PAY_INPUT_VALUES_F.NAME%TYPE,
582 P_ASSIGNMENT_ACTION_ID IN PAY_ASSIGNMENT_ACTIONS.ASSIGNMENT_ACTION_ID%TYPE)
583 RETURN NUMBER
584 IS
585 l_business_group_id PER_BUSINESS_GROUPS.BUSINESS_GROUP_ID%TYPE;
586 l_element_type_id PAY_ELEMENT_TYPES_F.ELEMENT_TYPE_ID%TYPE;
587 l_input_value_id PAY_INPUT_VALUES_F.INPUT_VALUE_ID%TYPE;
591
588 l_result_value NUMBER;
589 BEGIN
590 l_result_value := NULL;
592 l_business_group_id := get_business_group_id(p_assignment_action_id);
593 if l_business_group_id is NULL then
594 return l_result_value;
595 end if;
596
597 get_element_input_id(
598 p_element_name => p_element_name,
599 p_input_value_name => p_input_value_name,
600 p_business_group_id => l_business_group_id,
601 p_element_type_id => l_element_type_id,
602 p_input_value_id => l_input_value_id);
603 if l_element_type_id is NULL or l_input_value_id is NULL then
604 return l_result_value;
605 end if;
606
607 -- Modified by keyazawa at 2003/09/03 for bug#3088039
608 l_result_value:=get_result_value_number(l_element_type_id,l_input_value_id,p_assignment_action_id);
609
610 return l_result_value;
611 END get_result_value_number;
612
613 -----------------------------------------------------------
614 -- GET_RESULT_VALUE_NUMBER --
615 -----------------------------------------------------------
616 FUNCTION GET_RESULT_VALUE_NUMBER(
617 P_ELEMENT_TYPE_ID IN NUMBER,
618 P_INPUT_VALUE_ID IN NUMBER,
619 P_ASSIGNMENT_ACTION_ID IN PAY_ASSIGNMENT_ACTIONS.ASSIGNMENT_ACTION_ID%TYPE)
620 RETURN NUMBER
621 IS
622 l_result_value NUMBER;
623 -- This cursor doesn't check action_type.
624 -- This cursor restrict optimizer not to use
625 -- PAY_RUN_RESULTS_N1 index in PAY_RUN_RESULTS.
626 CURSOR get_result_value IS
627 select /*+ ORDERED
628 USE_NL(PAA, PPA, PRR, PRRV)
629 INDEX(PAY_ASSIGNMENT_ACTIONS_PK PAA)
630 INDEX(PAY_PAYROLL_ACTIONS_PK PPA)
631 INDEX(PAY_RUN_RESULTS_N50 PRR)
632 INDEX(PAY_RUN_RESULT_VALUES_PK PRRV) */
633 min(fnd_number.canonical_to_number(prrv.result_value))
634 from pay_assignment_actions paa,
635 pay_payroll_actions ppa,
636 pay_run_results prr,
637 pay_run_result_values prrv
638 where paa.assignment_action_id = p_assignment_action_id
639 and ppa.payroll_action_id = paa.payroll_action_id
640 and prr.assignment_action_id = paa.assignment_action_id
641 and prr.element_type_id + 0 = p_element_type_id
642 and prr.status in ('P','PA')
643 and prrv.run_result_id = prr.run_result_id
644 and prrv.input_value_id = p_input_value_id;
645 BEGIN
646 l_result_value := NULL;
647
648 OPEN get_result_value;
649 FETCH get_result_value INTO l_result_value;
650 if get_result_value%NOTFOUND then
651 l_result_value := NULL;
652 end if;
653 CLOSE get_result_value;
654
655 return l_result_value;
656 END GET_RESULT_VALUE_NUMBER;
657
658 -----------------------------------------------------------
659 -- GET_RESULT_VALUE_DATE --
660 -----------------------------------------------------------
661 FUNCTION GET_RESULT_VALUE_DATE(
662 P_ELEMENT_NAME IN PAY_ELEMENT_TYPES_F.ELEMENT_NAME%TYPE,
663 P_INPUT_VALUE_NAME IN PAY_INPUT_VALUES_F.NAME%TYPE,
664 P_ASSIGNMENT_ACTION_ID IN PAY_ASSIGNMENT_ACTIONS.ASSIGNMENT_ACTION_ID%TYPE)
665 RETURN DATE
666 IS
667 l_business_group_id PER_BUSINESS_GROUPS.BUSINESS_GROUP_ID%TYPE;
668 l_element_type_id PAY_ELEMENT_TYPES_F.ELEMENT_TYPE_ID%TYPE;
669 l_input_value_id PAY_INPUT_VALUES_F.INPUT_VALUE_ID%TYPE;
670 l_result_value DATE;
671 BEGIN
672 l_result_value := NULL;
673
674 l_business_group_id := get_business_group_id(p_assignment_action_id);
675 if l_business_group_id is NULL then
676 return l_result_value;
677 end if;
678
679 get_element_input_id(
680 p_element_name => p_element_name,
681 p_input_value_name => p_input_value_name,
682 p_business_group_id => l_business_group_id,
683 p_element_type_id => l_element_type_id,
684 p_input_value_id => l_input_value_id);
685 if l_element_type_id is NULL or l_input_value_id is NULL then
686 return l_result_value;
687 end if;
688
689 -- Modified by keyazawa at 2003/09/03 for bug#3088039
690 l_result_value:=get_result_value_date(l_element_type_id,l_input_value_id,p_assignment_action_id);
691
692 return l_result_value;
693 END get_result_value_date;
694
695 -----------------------------------------------------------
696 -- GET_RESULT_VALUE_DATE --
697 -----------------------------------------------------------
698 FUNCTION GET_RESULT_VALUE_DATE(
699 P_ELEMENT_TYPE_ID IN NUMBER,
700 P_INPUT_VALUE_ID IN NUMBER,
701 P_ASSIGNMENT_ACTION_ID IN PAY_ASSIGNMENT_ACTIONS.ASSIGNMENT_ACTION_ID%TYPE)
702 RETURN DATE
703 IS
704 l_result_value DATE;
705 -- This cursor doesn't check action_type.
706 -- This cursor restrict optimizer not to use
707 -- PAY_RUN_RESULTS_N1 index in PAY_RUN_RESULTS.
708 CURSOR get_result_value IS
709 -- Support for canonical date format
710 -- select min(to_date(prrv.result_value,'DD-MON-YYYY'))
711 select /*+ ORDERED
712 USE_NL(PAA, PPA, PRR, PRRV)
713 INDEX(PAY_ASSIGNMENT_ACTIONS_PK PAA)
714 INDEX(PAY_PAYROLL_ACTIONS_PK PPA)
715 INDEX(PAY_RUN_RESULTS_N50 PRR)
716 INDEX(PAY_RUN_RESULT_VALUES_PK PRRV) */
717 min(fnd_date.canonical_to_date(prrv.result_value))
718 from pay_assignment_actions paa,
722 where paa.assignment_action_id = p_assignment_action_id
719 pay_payroll_actions ppa,
720 pay_run_results prr,
721 pay_run_result_values prrv
723 and ppa.payroll_action_id = paa.payroll_action_id
724 and prr.assignment_action_id = paa.assignment_action_id
725 and prr.element_type_id + 0 = p_element_type_id
726 and prr.status in ('P','PA')
727 and prrv.run_result_id = prr.run_result_id
728 and prrv.input_value_id = p_input_value_id;
729 BEGIN
730 l_result_value := NULL;
731
732 OPEN get_result_value;
733 FETCH get_result_value INTO l_result_value;
734 if get_result_value%NOTFOUND then
735 l_result_value := NULL;
736 end if;
737 CLOSE get_result_value;
738
739 return l_result_value;
740 END GET_RESULT_VALUE_DATE;
741
742 -----------------------------------------------------
743 -- GET_ENTRY_VALUE_CHAR --
744 -----------------------------------------------------
745 FUNCTION GET_ENTRY_VALUE_CHAR(
746 P_ELEMENT_NAME IN PAY_ELEMENT_TYPES_F.ELEMENT_NAME%TYPE,
747 P_INPUT_VALUE_NAME IN PAY_INPUT_VALUES_F.NAME%TYPE,
748 P_ASSIGNMENT_ID IN PER_ASSIGNMENTS_F.ASSIGNMENT_ID%TYPE,
749 P_EFFECTIVE_DATE IN DATE)
750 RETURN VARCHAR2
751 IS
752 l_business_group_id PER_BUSINESS_GROUPS.BUSINESS_GROUP_ID%TYPE;
753 l_element_type_id PAY_ELEMENT_TYPES_F.ELEMENT_TYPE_ID%TYPE;
754 l_input_value_id PAY_INPUT_VALUES_F.INPUT_VALUE_ID%TYPE;
755 l_entry_value PAY_ELEMENT_ENTRY_VALUES_F.SCREEN_ENTRY_VALUE%TYPE;
756 BEGIN
757 l_entry_value := NULL;
758
759 l_business_group_id := get_business_group_id(p_assignment_id,p_effective_date);
760 if l_business_group_id is NULL then
761 return l_entry_value;
762 end if;
763
764 get_element_input_id(
765 p_element_name => p_element_name,
766 p_input_value_name => p_input_value_name,
767 p_business_group_id => l_business_group_id,
768 p_element_type_id => l_element_type_id,
769 p_input_value_id => l_input_value_id);
770 if l_element_type_id is NULL or l_input_value_id is NULL then
771 return l_entry_value;
772 end if;
773
774 l_entry_value:=get_entry_value_char(l_input_value_id,p_assignment_id,p_effective_date);
775
776 return l_entry_value;
777 END GET_ENTRY_VALUE_CHAR;
778
779 -----------------------------------------------------
780 -- GET_ENTRY_VALUE_CHAR --
781 -----------------------------------------------------
782 FUNCTION GET_ENTRY_VALUE_CHAR(
783 P_INPUT_VALUE_ID IN NUMBER,
784 P_ASSIGNMENT_ID IN NUMBER,
785 P_EFFECTIVE_DATE IN DATE)
786 RETURN VARCHAR2
787 IS
788 l_entry_value PAY_ELEMENT_ENTRY_VALUES_F.SCREEN_ENTRY_VALUE%TYPE;
789
790 CURSOR get_entry_value IS
791 select /*+ ORDERED
792 USE_NL(PIV, PLIV, PEE, PEEV)
793 INDEX(PAY_INPUT_VALUES_F_PK PIV)
794 INDEX(PAY_LINK_INPUT_VALUES_F_N2 PLIV)
795 INDEX(PAY_ELEMENT_ENTRIES_F_N51 PEE)
796 INDEX(PAY_ELEMENT_ENTRY_VALUES_F_N50 PEEV) */
797 min( decode(piv.hot_default_flag, 'Y',nvl(peev.screen_entry_value,nvl(pliv.default_value,piv.default_value)),
798 'N',peev.screen_entry_value))
799 from pay_input_values_f piv,
800 pay_link_input_values_f pliv,
801 pay_element_entries_f pee,
802 pay_element_entry_values_f peev
803 WHERE piv.input_value_id = p_input_value_id
804 and p_effective_date
805 between piv.effective_start_date and piv.effective_end_date
806 and pliv.input_value_id = piv.input_value_id
807 and p_effective_date
808 between pliv.effective_start_date and pliv.effective_end_date
809 and pee.element_link_id = pliv.element_link_id
810 and pee.assignment_id = p_assignment_id
811 and nvl(pee.entry_type,'E') = 'E'
812 and p_effective_date
813 between pee.effective_start_date and pee.effective_end_date
814 and peev.element_entry_id = pee.element_entry_id
815 and peev.effective_start_date = pee.effective_start_date
816 and peev.effective_end_date = pee.effective_end_date
817 and peev.input_value_id = piv.input_value_id;
818 BEGIN
819 l_entry_value := NULL;
820
821 OPEN get_entry_value;
822 FETCH get_entry_value INTO l_entry_value;
823 if get_entry_value%NOTFOUND then
824 l_entry_value := NULL;
825 end if;
826 CLOSE get_entry_value;
827
828 return l_entry_value;
829 END GET_ENTRY_VALUE_CHAR;
830
831 -------------------------------------------------------
832 -- GET_ENTRY_VALUE_NUMBER --
833 -------------------------------------------------------
834 FUNCTION GET_ENTRY_VALUE_NUMBER(
835 P_ELEMENT_NAME IN PAY_ELEMENT_TYPES_F.ELEMENT_NAME%TYPE,
836 P_INPUT_VALUE_NAME IN PAY_INPUT_VALUES_F.NAME%TYPE,
837 P_ASSIGNMENT_ID IN PER_ASSIGNMENTS_F.ASSIGNMENT_ID%TYPE,
838 P_EFFECTIVE_DATE IN DATE)
839 RETURN NUMBER
840 IS
841 l_business_group_id PER_BUSINESS_GROUPS.BUSINESS_GROUP_ID%TYPE;
842 l_element_type_id PAY_ELEMENT_TYPES_F.ELEMENT_TYPE_ID%TYPE;
843 l_input_value_id PAY_INPUT_VALUES_F.INPUT_VALUE_ID%TYPE;
844 -- Modified by keyazawa at 2003/09/03 for bug#3088039
845 l_entry_value number;
846 BEGIN
847 l_entry_value := NULL;
848
852 end if;
849 l_business_group_id := get_business_group_id(p_assignment_id,p_effective_date);
850 if l_business_group_id is NULL then
851 return l_entry_value;
853
854 get_element_input_id(
855 p_element_name => p_element_name,
856 p_input_value_name => p_input_value_name,
857 p_business_group_id => l_business_group_id,
858 p_element_type_id => l_element_type_id,
859 p_input_value_id => l_input_value_id);
860 if l_element_type_id is NULL or l_input_value_id is NULL then
861 return l_entry_value;
862 end if;
863
864 l_entry_value:=get_entry_value_number(l_input_value_id,p_assignment_id,p_effective_date);
865
866 return l_entry_value;
867 END GET_ENTRY_VALUE_NUMBER;
868
869 -----------------------------------------------------
870 -- GET_ENTRY_VALUE_NUMBER --
871 -----------------------------------------------------
872 FUNCTION GET_ENTRY_VALUE_NUMBER(
873 P_INPUT_VALUE_ID IN NUMBER,
874 P_ASSIGNMENT_ID IN NUMBER,
875 P_EFFECTIVE_DATE IN DATE)
876 RETURN NUMBER
877 IS
878 -- Modified by keyazawa at 2003/09/03 for bug#3088039
879 l_entry_value number;
880
881 CURSOR get_entry_value IS
882 select /*+ ORDERED
883 USE_NL(PIV, PLIV, PEE, PEEV)
884 INDEX(PAY_INPUT_VALUES_F_PK PIV)
885 INDEX(PAY_LINK_INPUT_VALUES_F_N2 PLIV)
886 INDEX(PAY_ELEMENT_ENTRIES_F_N51 PEE)
887 INDEX(PAY_ELEMENT_ENTRY_VALUES_F_N50 PEEV) */
888 min(fnd_number.canonical_to_number(decode(decode(substr(piv.uom,1,1),'M','N','N','N','I','N','H','N',null),'N',
889 decode(piv.hot_default_flag, 'Y',nvl(peev.screen_entry_value,nvl(pliv.default_value,piv.default_value)),
890 'N',peev.screen_entry_value),null)))
891 from pay_input_values_f piv,
892 pay_link_input_values_f pliv,
893 pay_element_entries_f pee,
894 pay_element_entry_values_f peev
895 WHERE piv.input_value_id = p_input_value_id
896 and p_effective_date
897 between piv.effective_start_date and piv.effective_end_date
898 and pliv.input_value_id = piv.input_value_id
899 and p_effective_date
900 between pliv.effective_start_date and pliv.effective_end_date
901 and pee.element_link_id = pliv.element_link_id
902 and pee.assignment_id = p_assignment_id
903 and nvl(pee.entry_type,'E') = 'E'
904 and p_effective_date
905 between pee.effective_start_date and pee.effective_end_date
906 and peev.element_entry_id = pee.element_entry_id
907 and peev.effective_start_date = pee.effective_start_date
908 and peev.effective_end_date = pee.effective_end_date
909 and peev.input_value_id = piv.input_value_id;
910 BEGIN
911 l_entry_value := NULL;
912
913 OPEN get_entry_value;
914 FETCH get_entry_value INTO l_entry_value;
915 if get_entry_value%NOTFOUND then
916 l_entry_value := NULL;
917 end if;
918 CLOSE get_entry_value;
919
920 return l_entry_value;
921 END GET_ENTRY_VALUE_NUMBER;
922
923 -----------------------------------------------------
924 -- GET_ENTRY_VALUE_DATE --
925 -----------------------------------------------------
926 FUNCTION GET_ENTRY_VALUE_DATE(
927 P_ELEMENT_NAME IN PAY_ELEMENT_TYPES_F.ELEMENT_NAME%TYPE,
928 P_INPUT_VALUE_NAME IN PAY_INPUT_VALUES_F.NAME%TYPE,
929 P_ASSIGNMENT_ID IN PER_ASSIGNMENTS_F.ASSIGNMENT_ID%TYPE,
930 P_EFFECTIVE_DATE IN DATE)
931 RETURN DATE
932 IS
933 l_business_group_id PER_BUSINESS_GROUPS.BUSINESS_GROUP_ID%TYPE;
934 l_element_type_id PAY_ELEMENT_TYPES_F.ELEMENT_TYPE_ID%TYPE;
935 l_input_value_id PAY_INPUT_VALUES_F.INPUT_VALUE_ID%TYPE;
936 -- Modified by keyazawa at 2003/09/03 for bug#3088039
937 l_entry_value date;
938 BEGIN
939 l_entry_value := NULL;
940
941 l_business_group_id := get_business_group_id(p_assignment_id,p_effective_date);
942 if l_business_group_id is NULL then
943 return l_entry_value;
944 end if;
945
946 get_element_input_id(
947 p_element_name => p_element_name,
948 p_input_value_name => p_input_value_name,
949 p_business_group_id => l_business_group_id,
950 p_element_type_id => l_element_type_id,
951 p_input_value_id => l_input_value_id);
952 if l_element_type_id is NULL or l_input_value_id is NULL then
953 return l_entry_value;
954 end if;
955
956 -- Modified by keyazawa at 2003/09/03 for bug#3088039
957 l_entry_value:=get_entry_value_date(l_input_value_id,p_assignment_id,p_effective_date);
958
959 return l_entry_value;
960 END GET_ENTRY_VALUE_DATE;
961
962 -----------------------------------------------------
963 -- GET_ENTRY_VALUE_DATE --
964 -----------------------------------------------------
965 FUNCTION GET_ENTRY_VALUE_DATE(
966 P_INPUT_VALUE_ID IN NUMBER,
967 P_ASSIGNMENT_ID IN NUMBER,
968 P_EFFECTIVE_DATE IN DATE)
969 RETURN DATE
970 IS
971 -- Modified by keyazawa at 2003/09/03 for bug#3088039
972 l_entry_value date;
973
974 CURSOR get_entry_value IS
975 --select min (to_date(decode(substr(piv.uom,1,1),'D',
976 -- decode(piv.hot_default_flag, 'Y',nvl(peev.screen_entry_value,nvl(pliv.default_value,piv.default_value)),
980 INDEX(PAY_INPUT_VALUES_F_PK PIV)
977 -- 'N',peev.screen_entry_value),null),'DD-MON-YYYY'))
978 select /*+ ORDERED
979 USE_NL(PIV, PLIV, PEE, PEEV)
981 INDEX(PAY_LINK_INPUT_VALUES_F_N2 PLIV)
982 INDEX(PAY_ELEMENT_ENTRIES_F_N51 PEE)
983 INDEX(PAY_ELEMENT_ENTRY_VALUES_F_N50 PEEV) */
984 min (fnd_date.canonical_to_date(decode(substr(piv.uom,1,1),'D',
985 decode(piv.hot_default_flag, 'Y',nvl(peev.screen_entry_value,nvl(pliv.default_value,piv.default_value)),
986 'N',peev.screen_entry_value),null)))
987 from pay_input_values_f piv,
988 pay_link_input_values_f pliv,
989 pay_element_entries_f pee,
990 pay_element_entry_values_f peev
991 WHERE piv.input_value_id = p_input_value_id
992 and p_effective_date
993 between piv.effective_start_date and piv.effective_end_date
994 and pliv.input_value_id = piv.input_value_id
995 and p_effective_date
996 between pliv.effective_start_date and pliv.effective_end_date
997 and pee.element_link_id = pliv.element_link_id
998 and pee.assignment_id = p_assignment_id
999 and nvl(pee.entry_type,'E') = 'E'
1000 and p_effective_date
1001 between pee.effective_start_date and pee.effective_end_date
1002 and peev.element_entry_id = pee.element_entry_id
1003 and peev.effective_start_date = pee.effective_start_date
1004 and peev.effective_end_date = pee.effective_end_date
1005 and peev.input_value_id = piv.input_value_id;
1006 BEGIN
1007 l_entry_value := NULL;
1008
1009 OPEN get_entry_value;
1010 FETCH get_entry_value INTO l_entry_value;
1011 if get_entry_value%NOTFOUND then
1012 l_entry_value := NULL;
1013 end if;
1014 CLOSE get_entry_value;
1015
1016 return l_entry_value;
1017 END GET_ENTRY_VALUE_DATE;
1018
1019 -----------------------------------------------------
1020 -- GET_ELEMENT_TYPE_ID --
1021 -----------------------------------------------------
1022 FUNCTION GET_ELEMENT_TYPE_ID(
1023 P_ELEMENT_NAME IN PAY_ELEMENT_TYPES_F.ELEMENT_NAME%TYPE,
1024 P_BUSINESS_GROUP_ID IN NUMBER,
1025 P_LEGISLATION_CODE IN PER_BUSINESS_GROUPS.LEGISLATION_CODE%TYPE)
1026 RETURN NUMBER
1027 IS
1028 l_element_type_id NUMBER;
1029
1030 CURSOR get_element_type_id IS
1031 select /*+ INDEX(PAY_ELEMENT_TYPES_F_UK2 PET) */
1032 min(pet.element_type_id)
1033 from pay_element_types_f pet
1034 where pet.element_name = p_element_name
1035 and nvl(pet.business_group_id,p_business_group_id) = p_business_group_id
1036 and nvl(pet.legislation_code,p_legislation_code) = p_legislation_code;
1037 BEGIN
1038 OPEN get_element_type_id;
1039 FETCH get_element_type_id INTO l_element_type_id;
1040 if get_element_type_id%NOTFOUND then
1041 l_element_type_id := NULL;
1042 end if;
1043 CLOSE get_element_type_id;
1044
1045 return l_element_type_id;
1046 END GET_ELEMENT_TYPE_ID;
1047
1048 -----------------------------------------------------
1049 -- GET_INPUT_VALUE_ID --
1050 -----------------------------------------------------
1051 FUNCTION GET_INPUT_VALUE_ID(
1052 P_ELEMENT_TYPE_ID IN NUMBER,
1053 P_INPUT_VALUE_NAME IN PAY_INPUT_VALUES_F.NAME%TYPE)
1054 RETURN NUMBER
1055 IS
1056 l_input_value_id NUMBER;
1057
1058 CURSOR get_input_value_id IS
1059 select /*+ INDEX(PAY_INPUT_VALUES_F_UK2 PIV) */
1060 min(piv.input_value_id)
1061 from pay_input_values_f piv
1062 where piv.element_type_id = p_element_type_id
1063 and piv.name=p_input_value_name;
1064 BEGIN
1065 OPEN get_input_value_id;
1066 FETCH get_input_value_id INTO l_input_value_id;
1067 if get_input_value_id%NOTFOUND then
1068 l_input_value_id := NULL;
1069 end if;
1070 CLOSE get_input_value_id;
1071
1072 return l_input_value_id;
1073 END GET_INPUT_VALUE_ID;
1074
1075 -----------------------------------------------------
1076 -- GET_INPUT_VALUE_ID --
1077 -----------------------------------------------------
1078 FUNCTION GET_INPUT_VALUE_ID(
1079 P_ELEMENT_NAME IN PAY_ELEMENT_TYPES_F.ELEMENT_NAME%TYPE,
1080 P_INPUT_VALUE_NAME IN PAY_INPUT_VALUES_F.NAME%TYPE,
1081 P_BUSINESS_GROUP_ID IN NUMBER,
1082 P_LEGISLATION_CODE IN PER_BUSINESS_GROUPS.LEGISLATION_CODE%TYPE)
1083 RETURN NUMBER
1084 IS
1085 l_element_type_id NUMBER;
1086 l_input_value_id NUMBER;
1087 BEGIN
1088 l_input_value_id:=NULL;
1089
1090 l_element_type_id:=get_element_type_id(p_element_name,p_business_group_id,p_legislation_code);
1091 if l_element_type_id is NULL then
1092 return l_input_value_id;
1093 end if;
1094
1095 l_input_value_id:=get_input_value_id(l_element_type_id,p_input_value_name);
1096 if l_input_value_id is NULL then
1097 l_input_value_id:=NULL;
1098 end if;
1099
1100 return l_input_value_id;
1101 END GET_INPUT_VALUE_ID;
1102 --
1103 -----------------------------------------------------
1104 -- GET_LOC_UNI_SEQ_INPUT_VALUE_ID --
1105 -----------------------------------------------------
1106 /* --------------------------------------------------
1107 -- Note: This function is only used for the element
1108 -- of unique display_sequence as JP localization
1112 FUNCTION get_loc_uni_seq_input_value_id(
1109 -- seed data. When the other argument is specified,
1110 -- null value would be returned.
1111 -------------------------------------------------- */
1113 p_element_name in pay_element_types_f.element_name%type,
1114 p_input_value_disp_seq in pay_input_values_f.display_sequence%type,
1115 p_business_group_id in number,
1116 p_legislation_code in per_business_groups.legislation_code%type)
1117 return number
1118 IS
1119 --
1120 l_element_type_id number;
1121 l_input_value_id number;
1122 --
1123 cursor csr_input_value
1124 is
1125 select piv.input_value_id
1126 from pay_input_values_f piv
1127 where piv.element_type_id = l_element_type_id
1128 and piv.display_sequence = p_input_value_disp_seq
1129 /* Validate if input value is owned as JP legislation code */
1130 and piv.legislation_code = decode(p_legislation_code,'JP',p_legislation_code,null)
1131 /* Validate if there are another input value of same display sequence */
1132 and not exists(
1133 select null
1134 from pay_input_values_f piv2
1135 where piv2.element_type_id = piv.element_type_id
1136 and piv2.display_sequence = piv.display_sequence
1137 and piv2.input_value_id <> piv.input_value_id);
1138 --
1139 BEGIN
1140 --
1141 l_input_value_id := null;
1142 --
1143 l_element_type_id:=get_element_type_id(p_element_name,p_business_group_id,p_legislation_code);
1144 if l_element_type_id is null then
1145 return l_input_value_id;
1146 end if;
1147 --
1148 -- /* Excluded the case that
1149 -- input value is not JP seed data
1150 -- or multiple same sequence input value exist. */
1151 open csr_input_value;
1152 fetch csr_input_value into l_input_value_id;
1153 if csr_input_value%notfound then
1154 l_input_value_id := null;
1155 end if;
1156 close csr_input_value;
1157 --
1158 return l_input_value_id;
1159 --
1160 END get_loc_uni_seq_input_value_id;
1161 --
1162 --===============================================================================
1163 FUNCTION GET_SAVE_RUN_BALANCE(
1164 P_BALANCE_TYPE_ID IN PAY_BALANCE_TYPES.BALANCE_TYPE_ID%TYPE,
1165 P_BUSINESS_GROUP_ID IN PER_BUSINESS_GROUPS.BUSINESS_GROUP_ID%TYPE,
1166 P_LEGISLATION_CODE IN PER_BUSINESS_GROUPS.LEGISLATION_CODE%TYPE
1167 )
1168 RETURN VARCHAR2 IS
1169 --===============================================================================
1170 l_save_run_balance PAY_DEFINED_BALANCES.SAVE_RUN_BALANCE%TYPE;
1171
1172 BEGIN
1173
1174 SELECT SAVE_RUN_BALANCE
1175 INTO l_save_run_balance
1176 FROM PAY_DEFINED_BALANCES
1177 WHERE BALANCE_TYPE_ID = P_BALANCE_TYPE_ID
1178 AND nvl(BUSINESS_GROUP_ID,p_business_group_id) = p_business_group_id
1179 AND nvl(LEGISLATION_CODE,p_legislation_code) = p_legislation_code;
1180
1181 return l_save_run_balance;
1182 EXCEPTION
1183 WHEN OTHERS THEN
1184 RETURN NULL;
1185 END GET_SAVE_RUN_BALANCE;
1186 --===============================================================================
1187 FUNCTION GET_DEFINED_BALANCE_ID(
1188 P_BALANCE_TYPE_ID IN PAY_BALANCE_TYPES.BALANCE_TYPE_ID%TYPE,
1189 P_ASSIGNMENT_ID IN PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_ID%TYPE,
1190 P_EFFECTIVE_DATE IN DATE
1191 )
1192 RETURN NUMBER IS
1193 --===============================================================================
1194 CURSOR get_balance_name(
1195 p_business_group_id PER_BUSINESS_GROUPS.BUSINESS_GROUP_ID%TYPE,
1196 p_legislation_code PER_BUSINESS_GROUPS.LEGISLATION_CODE%TYPE
1197 ) IS
1198 SELECT BALANCE_NAME
1199 FROM PAY_BALANCE_TYPES
1200 WHERE nvl(BUSINESS_GROUP_ID,p_business_group_id) = p_business_group_id
1201 AND nvl(LEGISLATION_CODE,p_legislation_code) = p_legislation_code
1202 AND BALANCE_TYPE_ID = p_balance_type_id;
1203
1204 l_defined_balance_id PAY_DEFINED_BALANCES.DEFINED_BALANCE_ID%TYPE;
1205 l_business_group_id PER_BUSINESS_GROUPS.BUSINESS_GROUP_ID%TYPE;
1206 l_legislation_code PER_BUSINESS_GROUPS.LEGISLATION_CODE%TYPE;
1207 l_balance_name PAY_BALANCE_TYPES.BALANCE_NAME%TYPE;
1208 l_dimension_name PAY_BALANCE_DIMENSIONS.DIMENSION_NAME%TYPE;
1209 l_save_run_balance PAY_DEFINED_BALANCES.SAVE_RUN_BALANCE%TYPE;
1210
1211 BEGIN
1212 l_business_group_id := get_business_group_id(P_ASSIGNMENT_ID,P_EFFECTIVE_DATE);
1213 l_legislation_code := get_legislation_code(l_business_group_id);
1214
1215 l_save_run_balance := GET_SAVE_RUN_BALANCE(P_BALANCE_TYPE_ID,l_business_group_id,l_legislation_code);
1216
1217 IF l_save_run_balance <> 'Y' THEN
1218 RETURN NULL;
1219 END IF;
1220
1221 OPEN get_balance_name(l_business_group_id,l_legislation_code);
1222 FETCH get_balance_name INTO l_balance_name;
1223 if get_balance_name%NOTFOUND then
1224 l_balance_name := NULL;
1225 end if;
1226 CLOSE get_balance_name;
1227
1228 l_dimension_name := '_ASG_RUN';
1229 l_defined_balance_id := GET_DEFINED_BALANCE_ID(l_balance_name,l_dimension_name,l_business_group_id);
1230
1231 return l_defined_balance_id;
1232 EXCEPTION
1233 WHEN OTHERS THEN
1234 RETURN NULL;
1235 END GET_DEFINED_BALANCE_ID;
1236 --
1237 --===============================================================================
1238 FUNCTION GET_DEFINED_BALANCE_ID(
1239 P_BALANCE_TYPE_ID IN PAY_BALANCE_TYPES.BALANCE_TYPE_ID%TYPE,
1240 P_ASSIGNMENT_ACTION_ID IN PAY_ASSIGNMENT_ACTIONS.ASSIGNMENT_ACTION_ID%TYPE
1241 )
1242 RETURN NUMBER IS
1243 --===============================================================================
1244 CURSOR csr_assignment_action_id IS
1245 SELECT /*+ ORDERED
1246 USE_NL(PAA, PPA)
1247 INDEX(PAY_ASSIGNMENT_ACTIONS_PK PAA)
1248 INDEX(PAY_PAYROLL_ACTIONS_PK PPA) */
1249 PAA.ASSIGNMENT_ID,
1250 PPA.EFFECTIVE_DATE
1251 FROM PAY_ASSIGNMENT_ACTIONS PAA,
1252 PAY_PAYROLL_ACTIONS PPA
1253 WHERE PAA.PAYROLL_ACTION_ID = PPA.PAYROLL_ACTION_ID
1254 AND PAA.ASSIGNMENT_ACTION_ID = p_assignment_action_id;
1255
1256 l_defined_balance_id PAY_DEFINED_BALANCES.DEFINED_BALANCE_ID%TYPE;
1257 l_assignment_id PAY_ASSIGNMENT_ACTIONS.ASSIGNMENT_ID%TYPE;
1258 l_effective_date PAY_PAYROLL_ACTIONS.EFFECTIVE_DATE%TYPE;
1259
1260 BEGIN
1261 OPEN csr_assignment_action_id;
1262 FETCH csr_assignment_action_id INTO l_assignment_id,l_effective_date;
1263 if csr_assignment_action_id%NOTFOUND then
1264 l_assignment_id := NULL;
1265 l_effective_date := NULL;
1266 end if;
1267 CLOSE csr_assignment_action_id;
1268
1269 l_defined_balance_id := GET_DEFINED_BALANCE_ID(p_balance_type_id, l_assignment_id, l_effective_date);
1270 return l_defined_balance_id;
1271 EXCEPTION
1272 WHEN OTHERS THEN
1273 RETURN NULL;
1274 END GET_DEFINED_BALANCE_ID;
1275 --
1276 END PAY_JP_BALANCE_PKG;