[Home] [Help]
PACKAGE BODY: APPS.HR_JPBAL
Source
1 PACKAGE BODY hr_jpbal AS
2 /* $Header: pyjpbal.pkb 120.5 2011/04/27 03:25:09 keyazawa ship $ */
3 /* ------------------------------------------------------------------------------------ */
4 c_package constant varchar2(31) := 'hr_jpbal.';
5 /* ------------------------------------------------------------------------------------
6 -- EXPIRY CHECKING CODE
7 --
8 -- Check the expiry of latest balance table.
9 -- If current assignment action*s start period equal to the start period
10 -- of the latest assignment action in latest balance table,
11 -- and if out parameter type is number, this function returns 0 (this means false).
12 -- Otherwise, if out parameter type is date, (which is utilized in balance adjustment
13 -- to maintain latest balance,) this function returns last date in the period.
14 --
15 -- Now this check_expiry code supports only effective_date based dimension.
16 -- date_earned based dimension is not supported fully in Oracle*Applications
17 -- from the perspective of latest balance, e.g. legislation rule BAL_ADJ_LAT_BAL.
18 -- ------------------------------------------------------------------------------------ */
19 --
20 /* General Process */
21 --
22 PROCEDURE check_expiry(
23 p_owner_payroll_action_id IN NUMBER, -- latest balance pact
24 p_user_payroll_action_id IN NUMBER, -- current pact
25 p_owner_assignment_action_id IN NUMBER, -- latest balance assact
26 p_user_assignment_action_id IN NUMBER, -- current assact
27 p_owner_effective_date IN DATE, -- latest balance effective_date
28 p_user_effective_date IN DATE, -- current effective_date
29 p_dimension_name IN VARCHAR2,
30 p_expiry_information OUT NOCOPY NUMBER)
31 IS
32 l_proc varchar2(61);
33 --
34 l_dimension_date_type varchar2(255);
35 l_owner_period_end_date DATE;
36 l_business_group_id NUMBER;
37 l_user_date_earned DATE;
38 l_owner_date_earned DATE;
39 BEGIN
40 -- To solve gscc error
41 l_proc := c_package || 'check_expiry';
42 --
43 hr_utility.set_location('Entering : ' || l_proc, 10);
44 --
45 hr_utility.trace('dimension_name : ' || p_dimension_name);
46 hr_utility.trace('user_pact : ' || to_char(p_user_payroll_action_id));
47 hr_utility.trace('user_assact : ' || to_char(p_user_assignment_action_id));
48 hr_utility.trace('user_effective_date : ' || to_char(p_user_effective_date));
49 hr_utility.trace('owner_pact : ' || to_char(p_owner_payroll_action_id));
50 hr_utility.trace('owner_assact : ' || to_char(p_owner_assignment_action_id));
51 hr_utility.trace('owner_effective_date : ' || to_char(p_owner_effective_date));
52 --
53 l_dimension_date_type := rtrim(substrb(rpad(p_dimension_name, 44), -14));
54 --
55 IF l_dimension_date_type = 'EFFECTIVE_DATE' THEN
56 hr_utility.trace('Date Type : EFFECTIVE_DATE');
57 --
58 l_owner_period_end_date := hr_jprts.dimension_reset_last_date(
59 p_dimension_name,
60 p_owner_effective_date);
61 --
62 hr_utility.trace('owner_period_end_date : ' || to_char(l_owner_period_end_date));
63 --
64 IF p_user_effective_date > l_owner_period_end_date THEN
65 p_expiry_information := 1;
66 ELSE
67 p_expiry_information := 0;
68 END IF;
69 --
70 -- Bug.2597843. DATE_EARNED expiry checking.
71 -- Note there's no guarantee that DATE_EARNED is sequential like EFFECTIVE_DATE.
72 -- If your business process does not guarantee DATE_EARNED to be sequential,
73 -- never use DATE_EARNED based dimensions.
74 -- Additionally, feed info on DATE_EARNED must be exactly the same as that of EFFECTIVE_DATE
75 -- because feeding by PYUGEN checks the feed info as of EFFECTIVE_DATE, not DATE_EARNED.
76 --
77 ELSIF l_dimension_date_type = 'DATE_EARNED' THEN
78 hr_utility.trace('Date Type : DATE_EARNED');
79 --
80 select ppa_user.business_group_id,
81 ppa_user.date_earned,
82 ppa_owner.date_earned
83 into l_business_group_id,
84 l_user_date_earned,
85 l_owner_date_earned
86 from pay_payroll_actions ppa_owner,
87 pay_payroll_actions ppa_user
88 where ppa_user.payroll_action_id = p_user_payroll_action_id
89 and ppa_owner.payroll_action_id = p_owner_payroll_action_id;
90 --
91 IF p_dimension_name = hr_jprts.g_asg_fytd_jp THEN
92 l_owner_period_end_date := hr_jprts.dim_reset_last_date_userdef(
93 p_dimension_name,
94 l_owner_date_earned,
95 'FLEX',
96 null,
97 l_business_group_id);
98 ELSE
99 l_owner_period_end_date := hr_jprts.dimension_reset_last_date(
100 p_dimension_name,
101 l_owner_date_earned);
102 END IF;
103 --
104 hr_utility.trace('user_date_earned : ' || to_char(l_user_date_earned));
105 hr_utility.trace('owner_date_earned : ' || to_char(l_owner_date_earned));
106 hr_utility.trace('owner_period_end_date : ' || to_char(l_owner_period_end_date));
107 --
108 if l_user_date_earned > l_owner_period_end_date then
109 p_expiry_information := 1;
110 else
111 p_expiry_information := 0;
112 end if;
113 ELSE
114 fnd_message.set_name('PAY', 'This dimension is invalid');
115 fnd_message.raise_error;
116 END IF;
117 --
118 hr_utility.trace('expiry_information : ' || to_char(p_expiry_information));
119 hr_utility.set_location('Leaving : ' || l_proc, 40);
120 END check_expiry;
121 --
122 /* Balance Adjustment */
123 --
124 PROCEDURE check_expiry(
125 p_owner_payroll_action_id IN NUMBER, -- latest balance pact
126 p_user_payroll_action_id IN NUMBER, -- current pact
127 p_owner_assignment_action_id IN NUMBER, -- latest balance assact
128 p_user_assignment_action_id IN NUMBER, -- current assact
129 p_owner_effective_date IN DATE, -- latest balance effective_date
130 p_user_effective_date IN DATE, -- current effective_date
131 p_dimension_name IN VARCHAR2,
132 p_expiry_information OUT NOCOPY DATE)
133 IS
134 l_proc varchar2(61);
135 --
136 l_dimension_date_type varchar2(255);
137 l_business_group_id NUMBER(15);
138 l_owner_date_earned DATE;
139 --
140 cursor csr_business_group is
141 SELECT OWNER.business_group_id,
142 OWNER.date_earned
143 FROM pay_payroll_actions OWNER
144 WHERE OWNER.payroll_action_id = p_owner_payroll_action_id;
145 BEGIN
146 -- To solve gscc error
147 l_proc := c_package || 'check_expiry';
148 --
149 hr_utility.set_location('Entering : ' || l_proc, 10);
150 --
151 hr_utility.trace('dimension_name : ' || p_dimension_name);
152 --
153 l_dimension_date_type := rtrim(substrb(rpad(p_dimension_name, 44), -14));
154 --
155 IF l_dimension_date_type = 'EFFECTIVE_DATE' THEN
156 hr_utility.trace('Date Type : EFFECTIVE_DATE');
157 --
158 p_expiry_information := hr_jprts.dimension_reset_last_date(
159 p_dimension_name,
160 p_owner_effective_date);
161 --
162 -- Bug.2597843. DATE_EARNED expiry checking.
163 -- Note there's no guarantee that DATE_EARNED is sequential like EFFECTIVE_DATE.
164 -- If your business process does not guarantee DATE_EARNED to be sequential,
165 -- never use DATE_EARNED based dimensions.
166 --
167 ELSIF l_dimension_date_type = 'DATE_EARNED' THEN
168 hr_utility.trace('Date Type : DATE_EARNED');
169 --
170 open csr_business_group;
171 fetch csr_business_group into
172 l_business_group_id,
173 l_owner_date_earned;
174 close csr_business_group;
175 --
176 IF p_dimension_name = hr_jprts.g_asg_fytd_jp THEN
177 p_expiry_information := hr_jprts.dim_reset_last_date_userdef(
178 p_dimension_name,
179 l_owner_date_earned,
180 'FLEX',
181 null,
182 l_business_group_id);
183 ELSE
184 p_expiry_information := hr_jprts.dimension_reset_last_date(
185 p_dimension_name,
186 l_owner_date_earned);
187 END IF;
188 ELSE
189 fnd_message.set_name('PAY', 'This dimension is invalid');
190 fnd_message.raise_error;
191 END IF;
192 --
193 hr_utility.trace('expiry_information : ' || to_char(p_expiry_information));
194 hr_utility.set_location('Leaving : ' || l_proc, 20);
195 END check_expiry;
196 --
197 -- Fix bug#3083339: Added function get_element_reference to retrieve element
198 -- level balance references reporting purposes.
199 /* ----------------------------------------------------------------------------
200 --
201 -- GET_ELEMENT_REFERENCE
202 --
203 -- This function returns an element name for identification purposes, which is
204 -- prefixed by _E depending on the balance, and used as the reported dimension
205 -- name.
206 -- ------------------------------------------------------------------------- */
207 -- This function is used in PAY_JP_PAYJPBAL_VALUES_V of Balance View Form.
208 FUNCTION get_element_reference(
209 p_run_result_id IN NUMBER,
210 p_database_item_suffix IN VARCHAR2) RETURN VARCHAR2 IS
211 --
212 l_reference VARCHAR2(80);
213 --
214 CURSOR get_element_name(p_run_result_id NUMBER) IS
215 SELECT /*+ ORDERED */
216 pet.element_name
217 FROM
218 pay_run_results prr,
219 pay_run_result_values prrv,
220 pay_input_values piv,
221 pay_element_types pet
222 WHERE prr.run_result_id = p_run_result_id
223 AND prrv.run_result_id = prr.run_result_id
224 AND piv.input_value_id = prrv.input_value_id
225 AND pet.element_type_id = piv.element_type_id;
226 --
227 BEGIN
228 --
229 OPEN get_element_name(p_run_result_id);
230 FETCH get_element_name INTO l_reference;
231 CLOSE get_element_name;
232 --
233 l_reference := p_database_item_suffix || '(' || l_reference || ')';
234 --
235 RETURN l_reference;
236 --
237 END get_element_reference;
238 --
239 /* ------------------------------------------------------------------------------------
240 --
241 -- DATE_EARNED_FC
242 --
243 -- This procedure is used for feed checking as of DATE_EARNED.
244 -- PYUGEN supports only EFFECITVE_DATE feed checking, so another feed checking
245 -- as of DATE_EARNED required.
246 --
247 -- ------------------------------------------------------------------------------------ */
248 /*
249 procedure date_earned_fc(
250 p_payroll_action_id in number,
251 p_asg_action_id in number,
252 p_assignment_id in number,
253 p_effective_date in date,
254 p_dimension_name in varchar2,
255 p_iv_id in number,
256 p_bt_id in number,
257 p_contexts in varchar2,
258 p_feed_flag in out nocopy number,
259 p_feed_scale in out nocopy number)
260 is
261 l_proc varchar2(61);
262 begin
263 -- To solve gscc error
264 l_proc := c_package || 'date_earned_fc';
265 --
266 hr_utility.set_location('Entering : ' || l_proc, 10);
267 --
268 select pbf.scale
269 into p_feed_scale
270 from pay_balance_feeds_f pbf,
271 pay_payroll_actions ppa
272 where ppa.payroll_action_id = p_payroll_action_id
273 and pbf.balance_type_id = p_bt_id
274 and pbf.input_value_id = p_iv_id
275 and ppa.date_earned
276 between pbf.effective_start_date and pbf.effective_end_date;
277 --
278 p_feed_flag := 1;
279 --
280 hr_utility.set_location('Leaving : ' || l_proc, 20);
281 exception
282 when no_data_found then
283 hr_utility.set_location('Leaving : ' || l_proc, 25);
284 p_feed_flag := 0;
285 p_feed_scale := 0;
286 end date_earned_fc;
287 */
288 --
289 /* ------------------------------------------------------------------------------------
290 --
291 -- DIMENSION RELEVANT (private)
292 --
293 -- This function checks that a value is required for the dimension
294 -- for this particular balance type. If so, the defined balance is returned.
295 --
296 -- ------------------------------------------------------------------------------------ */
297 FUNCTION dimension_relevant(p_balance_type_id IN NUMBER,
298 p_dimension_name IN VARCHAR2)
299 RETURN NUMBER IS
300 --
301 l_defined_balance_id NUMBER;
302 --
303 cursor relevant(
304 c_balance_type_id IN NUMBER,
305 c_dimension_name IN VARCHAR2)
306 is
307 SELECT /*+ ORDERED */
308 pdb.defined_balance_id
309 FROM pay_defined_balances pdb,
310 pay_balance_dimensions pbd
311 WHERE pdb.balance_type_id = c_balance_type_id
312 AND pbd.balance_dimension_id = pdb.balance_dimension_id
313 AND pbd.dimension_name = c_dimension_name;
314 --
315 BEGIN
316 --
317 open relevant(p_balance_type_id, p_dimension_name);
318 fetch relevant into l_defined_balance_id;
319 close relevant;
320 --
321 RETURN l_defined_balance_id;
322 --
323 END dimension_relevant;
324 --
325 /* ------------------------------------------------------------------------------------
326 --
327 -- GET_LATEST_ELEMENT_BAL (Private)
328 --
329 -- Calculate latest balances for element dimensions
330 --
331 -- ------------------------------------------------------------------------------------ */
332 FUNCTION get_latest_element_bal(
333 p_assignment_action_id IN NUMBER,
334 p_defined_bal_id IN NUMBER,
335 p_source_id IN NUMBER)
336 --
337 RETURN NUMBER IS
338 --
339 l_balance NUMBER;
340 l_db_item_suffix VARCHAR2(30);
341 l_defined_bal_id NUMBER;
342 --
343 cursor element_latest_bal(
344 c_assignment_action_id IN NUMBER,
345 c_defined_bal_id IN NUMBER,
346 c_source_id IN NUMBER)
347 is
348 SELECT palb.value
349 FROM pay_assignment_latest_balances palb,
350 pay_balance_context_values pbcv
351 WHERE pbcv.context_id = c_source_id
352 AND palb.latest_balance_id = pbcv.latest_balance_id
353 AND palb.assignment_action_id = c_assignment_action_id
354 AND palb.defined_balance_id = c_defined_bal_id;
355 --
356 BEGIN
357 --
358 open element_latest_bal(
359 p_assignment_action_id,
360 p_defined_bal_id,
361 p_source_id);
362 fetch element_latest_bal into l_balance;
363 close element_latest_bal;
364 --
365 RETURN l_balance;
366 --
367 END get_latest_element_bal;
368 --
369 /* ------------------------------------------------------------------------------------
370 --
371 -- GET CORRECT TYPE (private)
372 --
373 -- This is a validation check to ensure that the assignment action is of the
374 -- correct type. This is called from all assignment action mode functions.
375 -- The assignment id is returned (and not assignment action id) because
376 -- this is to be used in the expired latest balance check. This function thus
377 -- has two uses - to validate the assignment action, and give the corresponding
378 -- assignmment id for that action.
379 --
380 -- ------------------------------------------------------------------------------------ */
381 FUNCTION get_correct_type(p_assignment_action_id IN NUMBER)
382 --
383 RETURN NUMBER IS
384 --
385 l_assignment_id NUMBER;
386 --
387 cursor get_corr_type (c_assignment_action_id IN NUMBER)
388 is
389 SELECT /*+ ORDERED */
390 paa.assignment_id
391 FROM pay_assignment_actions paa,
392 pay_payroll_actions ppa
393 WHERE paa.assignment_action_id = c_assignment_action_id
394 AND ppa.payroll_action_id = paa.payroll_action_id
395 AND ppa.action_type in ('R', 'Q', 'I', 'V', 'B');
396 --
397 BEGIN
398 --
399 open get_corr_type(p_assignment_action_id);
400 fetch get_corr_type into l_assignment_id;
401 close get_corr_type;
402 --
403 RETURN l_assignment_id;
404 --
405 END get_correct_type;
406 --
407 /* ------------------------------------------------------------------------------------
408 --
409 -- GET LATEST ACTION ID (private)
410 --
411 -- This function returns the latest assignment action ID given an assignment
412 -- and effective date. This is called from all Date Mode functions.
413 --
414 -- ------------------------------------------------------------------------------------ */
415 FUNCTION get_latest_action_id (p_assignment_id IN NUMBER,
416 p_effective_date IN DATE)
417 RETURN NUMBER IS
418 --
419 l_assignment_action_id NUMBER;
420 --
421 cursor get_latest_id (c_assignment_id IN NUMBER,
422 c_effective_date IN DATE)
423 is
424 SELECT /*+ ORDERED */
425 TO_NUMBER(substr(max(lpad(paa.action_sequence,15,'0')||
426 paa.assignment_action_id),16))
427 FROM pay_assignment_actions paa,
428 pay_payroll_actions ppa
429 WHERE paa.assignment_id = c_assignment_id
430 AND ppa.payroll_action_id = paa.payroll_action_id
431 AND ppa.effective_date <= c_effective_date
432 AND ppa.action_type in ('R', 'Q', 'I', 'V', 'B');
433 --
434 BEGIN
435 --
436 open get_latest_id(p_assignment_id, p_effective_date);
437 fetch get_latest_id into l_assignment_action_id;
438 close get_latest_id;
439 --
440 RETURN l_assignment_action_id;
441 --
442 END get_latest_action_id;
443 --
444 /* ------------------------------------------------------------------------------------
445 --
446 -- BALANCE --
447 --
448 -- FASTFORMULA cover for evaluating balances based on assignment_action_id
449 --
450 -- ------------------------------------------------------------------------------------ */
451 FUNCTION balance(
452 p_assignment_action_id IN NUMBER,
453 p_defined_balance_id IN NUMBER) RETURN NUMBER
454 IS
455 l_balance NUMBER;
456 l_assignment_id NUMBER;
457 l_balance_type_id NUMBER;
458 l_effective_date DATE ;
459 l_date_earned DATE ;
460 l_from_date DATE;
461 l_to_date DATE;
462 l_action_sequence NUMBER;
463 l_action_type pay_payroll_actions.action_type%TYPE;
464 l_business_group_id NUMBER;
465 l_dimension_name pay_balance_dimensions.dimension_name%TYPE;
466 l_dimension_jp_type VARCHAR2(15);
467 l_latest_value_exists VARCHAR(2);
468 --
469 cursor action_context
470 is
471 SELECT BAL_ASSACT.assignment_id,
472 BAL_ASSACT.action_sequence,
473 BACT.action_type,
474 BACT.effective_date,
475 BACT.date_earned,
476 BACT.business_group_id
477 FROM pay_payroll_actions BACT,
478 pay_assignment_actions BAL_ASSACT
479 WHERE BAL_ASSACT.assignment_action_id = p_assignment_action_id
480 AND BACT.payroll_action_id = BAL_ASSACT.payroll_action_id;
481 --
482 cursor balance_dimension
483 is
484 SELECT DB.balance_type_id,
485 DIM.dimension_name
486 FROM pay_balance_dimensions DIM,
487 pay_defined_balances DB
488 WHERE DB.defined_balance_id = p_defined_balance_id
489 AND DIM.balance_dimension_id = DB.balance_dimension_id;
490 BEGIN
491 --
492 -- get the context of the using action
493 --
494 OPEN action_context;
495 FETCH action_context INTO
496 l_assignment_id,
497 l_action_sequence,
498 l_action_type,
499 l_effective_date,
500 l_date_earned,
501 l_business_group_id;
502 CLOSE action_context;
503 --
504 -- from the item name determine what balance and dimension it is
505 --
506 OPEN balance_dimension;
507 FETCH balance_dimension INTO
508 l_balance_type_id,
509 l_dimension_name;
510 CLOSE balance_dimension;
511 --
512 if l_dimension_name in (
513 hr_jprts.g_asg_run,
514 hr_jprts.g_asg_mtd_jp,
515 hr_jprts.g_ast_ytd_jp,
516 hr_jprts.g_asg_aug2jul_jp,
517 hr_jprts.g_asg_jul2jun_jp,
518 hr_jprts.g_asg_proc_ptd,
519 hr_jprts.g_asg_itd,
520 hr_jprts.g_payment,
521 hr_jprts.g_asg_fytd2_jp
522 ) then
523 l_balance := pay_balance_pkg.get_value(
524 p_defined_balance_id,
525 p_assignment_action_id);
526 --
527 elsif l_dimension_name = hr_jprts.g_retro then
528 l_balance := hr_jprts.retro_jp(
529 p_assignment_action_id,
530 l_balance_type_id);
531 --
532 -- This function can not call the calculation of Element dimension because it needs source_id.
533 --
534 --
535 elsif l_dimension_name in (
536 hr_jprts.g_element_itd,
537 hr_jprts.g_element_ptd) then
538 fnd_message.set_name('PAY', 'This dimension is invalid');
539 fnd_message.raise_error;
540 --
541 elsif l_dimension_name = hr_jprts.g_asg_fytd_jp then
542 l_from_date := hr_jprts.dimension_reset_date_userdef(
543 l_dimension_name,
544 l_date_earned,
545 'FLEX',
546 null,
547 l_business_group_id);
548 l_to_date := l_date_earned;
549 --
550 l_balance := hr_jprts.calc_bal_date_earned(
551 l_assignment_id,
552 l_balance_type_id,
553 l_from_date,
554 l_to_date,
555 l_action_sequence);
556 --
557 -- User Defined Dimension
558 -- pay_balance_pkg.get_value cannot be used for user defined dimension.
559 -- route refer to pay_jp_balances_v(call hr_jpbal.balance this code).
560 --
561 -- rtrim is used for support dimension that was created before bug2597843 fix.
562 --
563 elsif substrb(rtrim(l_dimension_name),-8) = 'USER-REG' then
564 --
565 -- UTF8 support
566 --
567 l_dimension_jp_type := rtrim(substrb(rpad(l_dimension_name, 44), -14));
568 --
569 IF l_dimension_jp_type = 'EFFECTIVE_DATE' THEN
570 --
571 l_from_date := hr_jprts.dimension_reset_date(
572 l_dimension_name,
573 l_effective_date);
574 l_to_date := l_effective_date;
575 --
576 l_balance := hr_jprts.calc_bal_eff_date(
577 l_assignment_id,
578 l_balance_type_id,
579 l_from_date,
580 l_to_date,
581 l_action_sequence);
582 elsif l_dimension_jp_type = 'DATE_EARNED' then
583 --
584 l_from_date := hr_jprts.dimension_reset_date(
585 l_dimension_name,
586 l_date_earned);
587 l_to_date := l_date_earned;
588 --
589 l_balance := hr_jprts.calc_bal_date_earned(
590 l_assignment_id,
591 l_balance_type_id,
592 l_from_date,
593 l_to_date,
594 l_action_sequence);
595 end if;
596 -- Specified dimension is not supported
597 else
598 fnd_message.set_name('PAY', 'This dimension is invalid');
599 fnd_message.raise_error;
600 end if;
601 --
602 RETURN l_balance;
603 END balance;
604 --
605 /* ------------------------------------------------------------------------------------
606 --
607 -- BALANCE
608 --
609 -- FASTFORMULA cover for evaluating balances based on assignment_action_id.
610 -- If input parameter is item_name, this function call upper balance function.
611 --
612 -- ------------------------------------------------------------------------------------ */
613 FUNCTION balance(
614 p_assignment_action_id IN NUMBER,
615 p_item_name IN VARCHAR2)
616 RETURN NUMBER
617 IS
618 l_balance NUMBER;
619 CURSOR csr_assact
620 IS
621 SELECT /*+ ORDERED */
622 pdb.defined_balance_id
623 FROM pay_assignment_actions paa,
624 pay_payroll_actions ppa,
625 ff_database_items ffd,
626 ff_user_entities ffu,
627 hr_organization_information hoi,
628 pay_defined_balances pdb
629 WHERE paa.assignment_action_id=p_assignment_action_id
630 AND ppa.payroll_action_id=paa.payroll_action_id
631 AND ffd.user_name=p_item_name
632 AND ffu.user_entity_id=ffd.user_entity_id
633 AND ffu.creator_type='B'
634 AND nvl(ffu.business_group_id,ppa.business_group_id)=ppa.business_group_id
635 AND hoi.organization_id=ppa.business_group_id
636 AND hoi.org_information_context='Business Group Information'
637 AND nvl(ffu.legislation_code,hoi.org_information9)=hoi.org_information9
638 AND pdb.defined_balance_id=ffu.creator_id;
639 BEGIN
640 -- To solve gscc error.
641 l_balance := 0;
642 --
643 for l_rec in csr_assact loop
644 l_balance := balance(
645 p_assignment_action_id,
646 l_rec.defined_balance_id);
647 end loop;
648 --
649 RETURN l_balance;
650 END balance;
651 --
652 /* ------------------------------------------------------------------------------------
653 --
654 -- CREATE DIMENSION
655 --
656 -- Create the user defined dimension.
657 -- Now end user can not Ele-level,Person-level dimension.
658 -- Because 'SRS_USERBAL_LEVEL' that is lookup_type in hr_lookups table
659 -- does not have lookup_codes(ELEMENT, PERSON).
660 -- So end user can not select dimension type. Only ASSIGNEMNT level.
661 -- (hr_jpbal.balance doesn't have the parameter of element_entry_id)
662 --
663 -- ------------------------------------------------------------------------------------ */
664 PROCEDURE create_dimension(
665 errbuf OUT NOCOPY VARCHAR2,
666 retcode OUT NOCOPY NUMBER,
667 p_business_group_id IN NUMBER,
668 p_suffix IN VARCHAR2,
669 p_level IN VARCHAR2,
670 p_dim_date_type IN VARCHAR2,
671 p_start_dd_mm IN VARCHAR2,
672 p_frequency IN NUMBER)
673 IS
674 l_start_dd_mm varchar2(5);
675 l_database_item_suffix pay_balance_dimensions.database_item_suffix%type;
676 l_dimension_name pay_balance_dimensions.dimension_name%type;
677 l_route_id number;
678 l_balance_dimension_id number;
679 l_route_text ff_routes.text%type;
680 l_dimension_type pay_balance_dimensions.dimension_type%type;
681 l_expiry_checking_level pay_balance_dimensions.expiry_checking_level%type;
682 l_expiry_checking_code pay_balance_dimensions.expiry_checking_code%type;
683 l_description pay_balance_dimensions.description%type;
684 l_request_id number := fnd_profile.value('CONC_REQUEST_ID');
685 l_rowid rowid;
686 --
687 cursor csr_language_code is
688 select language_code
689 from fnd_languages
690 where installed_flag in ('B', 'I');
691 BEGIN
692 --
693 -- disable in progress of online patch
694 if ad_zd.get_edition('PATCH') is not null then
695 --
696 fnd_message.set_name('FND','AD_ZD_DISABLED_FEATURE');
697 fnd_message.raise_error;
698 --
699 end if;
700 --
701 errbuf := NULL;
702 retcode := 0;
703 --
704 -- Check DD-MM
705 --
706 l_start_dd_mm := to_char(to_date(p_start_dd_mm || '-' || '2000', 'DD-MM-YYYY'), 'DD-MM');
707 --
708 -- Fill the dimension name
709 -- Bug.2597843 Removed trailing space characters
710 --
711 l_database_item_suffix := upper('_ASG_'||p_suffix);
712 l_dimension_name := rpad(l_database_item_suffix, 30)
713 || rpad(p_dim_date_type, 15)
714 || l_start_dd_mm
715 || ' RESET'
716 || TO_CHAR(p_frequency, '00')
717 || ' USER-REG';
718 -- ---------------------------
719 -- INSERT INTO FF_ROUTES --
720 -- ---------------------------
721 select ff_routes_s.nextval
722 into l_route_id
723 from dual;
724 --
725 select pay_balance_dimensions_s.nextval
726 into l_balance_dimension_id
727 from dual;
728 --
729 l_route_text :=
730 ' pay_jp_balances_v TARGET,
731 pay_dummy_feeds_v FEED
732 WHERE TARGET.assignment_action_id = &B1
733 AND TARGET.balance_type_id = &U1
734 AND TARGET.balance_dimension_id = ' || to_char(l_balance_dimension_id);
735 --
736 insert into ff_routes(
737 route_id,
738 route_name,
739 user_defined_flag,
740 description,
741 text)
742 values( l_route_id,
743 'ROUTE_NAME_' || to_char(l_route_id),
744 'N',
745 'Route for User Defined Assignment Balance Dimension ' || l_dimension_name,
746 l_route_text);
747 -- -----------------------------------------
748 -- INSERT INTO FF_ROUTE_CONTEXT_USAGES --
749 -- -----------------------------------------
750 insert into ff_route_context_usages(
751 route_id,
752 context_id,
753 sequence_no)
754 select l_route_id,
755 context_id,
756 1
757 FROM ff_contexts
758 WHERE context_name = 'ASSIGNMENT_ACTION_ID';
759 -- ------------------------------------
760 -- INSERT INTO FF_ROUTE_PARAMETER --
761 -- ------------------------------------
762 insert into ff_route_parameters(
763 route_parameter_id,
764 route_id,
765 sequence_no,
766 parameter_name,
767 data_type)
768 values( ff_route_parameters_s.nextval,
769 l_route_id,
770 1,
771 'Balance Type Id',
772 'N');
773 -- -----------------------------
774 -- CREATION DIMENSION NAME --
775 -- -----------------------------
776 -- Bug.2597843
777 -- DATE_EARNED based dimension is not supported fully,
778 -- so latest balance should not be created.
779 -- Dimension type is set to 'N'(Not fed, Not stored).
780 -- This solution of dimension_type = 'N' for DATE_EARNED based dimension
781 -- is rejected from the perspective of performance.
782 -- Yes, there's limitation which causes inconsistency between latest balance
783 -- and sum of result values by route, but now, we are going to leave
784 -- this DATE_EARNED dimension bugs.
785 --
786 -- If we are going to support dimension_type = 'F'(Fed, Not Stored) for
787 -- DATE_EARNED dimension, feed_checking_type needs to be "F"
788 -- which means feed_checking_code is called for every run result
789 -- to check feed information as of DATE_EARNED, not EFFECTIVE_DATE
790 -- which is default behavior. This will cause serere performance issue.
791 --
792 l_dimension_type := 'A';
793 l_expiry_checking_level := 'P';
794 l_expiry_checking_code := 'hr_jpbal.check_expiry';
795 l_description := hr_jp_standard_pkg.get_message('PAY', 'PAY_JP_USER_DEF_ASG_DIM_DESC', 'US', 'REQUEST_ID', to_char(l_request_id));
796 --
797 insert into pay_balance_dimensions(
798 balance_dimension_id,
799 business_group_id,
800 legislation_code,
801 route_id,
802 database_item_suffix,
803 dimension_name,
804 dimension_type,
805 description,
806 legislation_subgroup,
807 payments_flag,
808 expiry_checking_level,
809 expiry_checking_code,
810 feed_checking_type,
811 feed_checking_code,
812 -- for Run Balances
813 SAVE_RUN_BALANCE_ENABLED,
814 DIMENSION_LEVEL,
815 PERIOD_TYPE,
816 START_DATE_CODE,
817 -- for Group Level dimension (run balance compliant)
818 ASG_ACTION_BALANCE_DIM_ID,
819 -- for hrdyndbi DBI Generator (run balance compliant)
820 DATABASE_ITEM_FUNCTION)
821 values( l_balance_dimension_id,
822 p_business_group_id,
823 null,
824 l_route_id,
825 l_database_item_suffix,
826 l_dimension_name,
827 l_dimension_type,
828 l_description,
829 null,
830 'N',
831 l_expiry_checking_level,
832 l_expiry_checking_code,
833 null,
834 null,
835 null,
836 null,
837 null,
838 null,
839 null,
840 null);
841 --
842 for l_rec in csr_language_code loop
843 --
844 -- no solution to translate multi byte underscore through message
845 if l_rec.language_code = 'JA' then
846 --
847 l_database_item_suffix := '_A'||to_multi_byte('_')||p_suffix;
848 --
849 else
850 --
851 l_database_item_suffix := hr_jp_standard_pkg.get_message('PAY', 'PAY_JP_USER_DEF_ASG_DIM_SUFFIX', l_rec.language_code, 'SUFFIX', p_suffix);
852 --
853 end if;
854 --
855 l_description := hr_jp_standard_pkg.get_message('PAY', 'PAY_JP_USER_DEF_ASG_DIM_DESC', l_rec.language_code, 'REQUEST_ID', to_char(l_request_id));
856 --
857 begin
858 select rowid
859 into l_rowid
860 from pay_balance_dimensions_tl
861 where balance_dimension_id = l_balance_dimension_id
862 and language = l_rec.language_code
863 for update nowait;
864 --
865 update pay_balance_dimensions_tl
866 set dimension_name = l_database_item_suffix,
867 database_item_suffix = l_database_item_suffix,
868 description = l_description
869 where rowid = l_rowid;
870 exception
871 when no_data_found then
872 insert into pay_balance_dimensions_tl(
873 BALANCE_DIMENSION_ID,
874 LANGUAGE,
875 SOURCE_LANG,
876 DIMENSION_NAME,
877 DATABASE_ITEM_SUFFIX,
878 DESCRIPTION)
879 values( l_balance_dimension_id,
880 l_rec.language_code,
881 l_rec.language_code,
882 l_database_item_suffix,
883 l_database_item_suffix,
884 l_description);
885 end;
886 end loop;
887 END create_dimension;
888 --
889 /* ------------------------------------------------------------------------------------
890 --
891 -- CALC_BALANCE_DATE --
892 --
893 -- This is the function for calculating assignment processing
894 -- of any dimension in date mode
895 --
896 -- ------------------------------------------------------------------------------------ */
897 --
898 -- This function only support USER-REG dimension.
899 --
900 FUNCTION calc_balance_date(
901 p_assignment_id IN NUMBER,
902 p_balance_type_id IN NUMBER,
903 p_effective_date IN DATE,
904 p_dimension_name IN VARCHAR2)
905 RETURN NUMBER
906 IS
907 --
908 l_assignment_action_id NUMBER;
909 l_assact_id_for_effect NUMBER;
910 l_assact_id_for_earned NUMBER;
911 l_balance NUMBER;
912 l_dimension_jp_type VARCHAR2(15);
913 l_last_effective_date DATE;
914 l_last_date_earned DATE;
915 l_frequency NUMBER;
916 l_start_dd_mm VARCHAR2(6);
917 l_next_start_date DATE;
918 l_defined_balance_id NUMBER;
919 --
920 /* -- c_effective_date <= session_date */
921 cursor get_latest_id_for_earned (
922 c_assignment_id IN NUMBER,
923 c_effective_date IN DATE)
924 is
925 SELECT TO_NUMBER(substr(max(lpad(ASSACT.action_sequence,15,'0')||
926 ASSACT.assignment_action_id),16))
927 FROM pay_payroll_actions PACT,
928 pay_assignment_actions ASSACT
929 WHERE ASSACT.assignment_id = c_assignment_id
930 AND PACT.payroll_action_id = ASSACT.payroll_action_id
931 AND PACT.date_earned <= c_effective_date
932 AND PACT.action_type in ('R', 'Q', 'I', 'V', 'B');
933 --
934 cursor last_date (c_assignment_action_id IN NUMBER)
935 is
936 SELECT ppa.effective_date effect_date,
937 ppa.date_earned earned_date
938 FROM pay_payroll_actions ppa,
939 pay_assignment_actions paa
940 WHERE paa.assignment_action_id = c_assignment_action_id
941 AND paa.payroll_action_id = ppa.payroll_action_id;
942 --
943 l_last_date last_date%ROWTYPE;
944 --
945 BEGIN
946 --
947 l_defined_balance_id := dimension_relevant(p_balance_type_id, p_dimension_name);
948 /* -- check relevant dimension. if it is not so(defined_balance_id is null), return null */
949 --
950 if l_defined_balance_id is not null then
951 --
952 l_assact_id_for_effect := get_latest_action_id( p_assignment_id,
953 p_effective_date);
954 --
955 /* -- p_effective_date <= session_date */
956 OPEN get_latest_id_for_earned( p_assignment_id,
957 p_effective_date);
958 FETCH get_latest_id_for_earned INTO l_assact_id_for_earned;
959 CLOSE get_latest_id_for_earned;
960 --
961 -- Fix bug#3051183: Corrected to support UTF8.
962 --
963 l_dimension_jp_type := RTRIM(SUBSTRB(RPAD(p_dimension_name, 44), -14));
964 --
965 IF l_dimension_jp_type = 'EFFECTIVE_DATE' THEN
966 --
967 IF l_assact_id_for_effect is null THEN
968 l_balance := 0;
969 ELSE
970 --
971 OPEN last_date(l_assact_id_for_effect);
972 FETCH last_date INTO l_last_date;
973 l_last_effective_date := l_last_date.effect_date;
974 CLOSE last_date;
975 --
976 l_assignment_action_id := l_assact_id_for_effect;
977 l_next_start_date := hr_jprts.dimension_reset_last_date(p_dimension_name,l_last_effective_date) + 1;
978 --
979 /* -- p_effective_date <= session_date */
980 if l_next_start_date <= p_effective_date then
981 l_balance := 0;
982 else
983 l_balance := balance( l_assignment_action_id,
984 l_defined_balance_id);
985 end if;
986 END IF;
987 --
988 ELSIF l_dimension_jp_type = 'DATE_EARNED' then
989 --
990 IF l_assact_id_for_earned is null THEN
991 l_balance := 0;
992 ELSE
993 --
994 OPEN last_date(l_assact_id_for_earned);
995 FETCH last_date INTO l_last_date;
996 l_last_date_earned := l_last_date.earned_date;
997 CLOSE last_date;
998 --
999 l_assignment_action_id := l_assact_id_for_earned;
1000 l_next_start_date := hr_jprts.dimension_reset_last_date(p_dimension_name,l_last_date_earned) + 1;
1001 --
1002 /* -- p_effective_date <= session_date */
1003 if l_next_start_date <= p_effective_date then
1004 l_balance := 0;
1005 else
1006 l_balance := balance( l_assignment_action_id,
1007 l_defined_balance_id);
1008 end if;
1009 END IF;
1010 END IF;
1011 else l_balance := null;
1012 end if;
1013 --
1014 RETURN l_balance;
1015 END calc_balance_date;
1016 --
1017 /* ------------------------------------------------------------------------------------
1018 --
1019 -- CALC_ASG_RUN_ACTION --
1020 --
1021 -- This is the function for calculating assignment runs in
1022 -- assignment action mode
1023 --
1024 -- ------------------------------------------------------------------------------------ */
1025 FUNCTION calc_asg_run_action(
1026 p_assignment_action_id IN NUMBER,
1027 p_balance_type_id IN NUMBER)
1028 RETURN NUMBER
1029 IS
1030 --
1031 l_assignment_action_id NUMBER;
1032 l_balance NUMBER;
1033 l_assignment_id NUMBER;
1034 --
1035 BEGIN
1036 --
1037 l_assignment_id := get_correct_type(p_assignment_action_id);
1038 IF l_assignment_id is null THEN
1039 --
1040 /* -- The assignment action is not a payroll or quickpay type, so return null */
1041 --
1042 l_balance := null;
1043 ELSE
1044 --
1045 l_balance := calc_asg_run(
1046 p_assignment_action_id => p_assignment_action_id,
1047 p_balance_type_id => p_balance_type_id,
1048 p_assignment_id => l_assignment_id);
1049 END IF;
1050 --
1051 RETURN l_balance;
1052 END calc_asg_run_action;
1053 --
1054 /* ------------------------------------------------------------------------------------
1055 --
1056 -- CALC_ASG_RUN_DATE --
1057 --
1058 -- This is the function for calculating assignment run in
1059 -- DATE MODE
1060 --
1061 -- ------------------------------------------------------------------------------------ */
1062 FUNCTION calc_asg_run_date(
1063 p_assignment_id IN NUMBER,
1064 p_balance_type_id IN NUMBER,
1065 p_effective_date IN DATE)
1066 RETURN NUMBER
1067 IS
1068 --
1069 l_assignment_action_id NUMBER;
1070 l_balance NUMBER;
1071 l_end_date DATE;
1072 l_defined_balance_id NUMBER;
1073 --
1074 cursor expired_time_period(c_assignment_action_id IN NUMBER)
1075 is
1076 SELECT /*+ ORDERED */
1077 ptp.end_date
1078 FROM pay_assignment_actions paa,
1079 pay_payroll_actions ppa,
1080 per_time_periods ptp
1081 WHERE paa.assignment_action_id = c_assignment_action_id
1082 AND ppa.payroll_action_id = paa.payroll_action_id
1083 AND ptp.time_period_id = ppa.time_period_id;
1084 --
1085 BEGIN
1086 --
1087 l_defined_balance_id := dimension_relevant(p_balance_type_id, hr_jprts.g_asg_run);
1088 /* -- check relevant dimension. if it is not so(defined_balance_id is null), return null */
1089 if l_defined_balance_id is not null then
1090 --
1091 l_assignment_action_id := get_latest_action_id(
1092 p_assignment_id,
1093 p_effective_date);
1094 IF l_assignment_action_id is null THEN
1095 l_balance := 0;
1096 ELSE
1097 OPEN expired_time_period(l_assignment_action_id);
1098 FETCH expired_time_period INTO l_end_date;
1099 CLOSE expired_time_period;
1100 --
1101 if l_end_date < p_effective_date then
1102 l_balance := 0;
1103 else
1104 l_balance := calc_asg_run(
1105 p_assignment_action_id => l_assignment_action_id,
1106 p_balance_type_id => p_balance_type_id,
1107 p_assignment_id => p_assignment_id);
1108 end if;
1109 END IF;
1110 else l_balance := null;
1111 end if;
1112 --
1113 RETURN l_balance;
1114 END calc_asg_run_date;
1115 --
1116 /* ------------------------------------------------------------------------------------
1117 --
1118 -- CALC_ASG_RUN --
1119 --
1120 -- calculate balances for Assignment Run
1121 --
1122 -- ------------------------------------------------------------------------------------ */
1123 /* -- Run
1124 -- the simplest dimension retrieves run values where the context
1125 -- is this assignment action and this balance feed. Balance is the
1126 -- specified input value. The related payroll action determines the
1127 -- date effectivity of the feeds */
1128 FUNCTION calc_asg_run(
1129 p_assignment_action_id IN NUMBER,
1130 p_balance_type_id IN NUMBER,
1131 p_assignment_id IN NUMBER)
1132 RETURN NUMBER
1133 IS
1134 --
1135 --
1136 l_balance NUMBER;
1137 l_defined_bal_id NUMBER;
1138 --
1139 BEGIN
1140 --
1141 /* --Do we need to work out nocopy a value for this dimension/balance combination.
1142 --Used dimension_name in dimension_relevant because of unique column */
1143 --
1144 l_defined_bal_id := dimension_relevant(p_balance_type_id, hr_jprts.g_asg_run);
1145 if l_defined_bal_id is not null then
1146 --
1147 /* -- Run balances will never have a value in pay_assignment_latest_balances
1148 -- table, as they are only used for the duration of the payroll run.
1149 -- We therefore don't need to check the table, time can be saved by
1150 -- simply calling the route code, which is incidentally the most
1151 -- performant (ie simple) route. */
1152 --
1153 l_balance := pay_balance_pkg.get_value(
1154 l_defined_bal_id,
1155 p_assignment_action_id);
1156 --
1157 else l_balance := null;
1158 end if;
1159 --
1160 RETURN l_balance;
1161 --
1162 END calc_asg_run;
1163 --
1164 /* ------------------------------------------------------------------------------------
1165 --
1166 -- CALC_ASG_PROC_PTD_ACTION
1167 --
1168 -- This is the function for calculating assignment processing
1169 -- period to date in assignment action mode
1170 --
1171 -- ------------------------------------------------------------------------------------ */
1172 FUNCTION calc_asg_proc_ptd_action(
1173 p_assignment_action_id IN NUMBER,
1174 p_balance_type_id IN NUMBER)
1175 RETURN NUMBER
1176 IS
1177 --
1178 l_assignment_action_id NUMBER;
1179 l_balance NUMBER;
1180 l_assignment_id NUMBER;
1181 --
1182 BEGIN
1183 --
1184 l_assignment_id := get_correct_type(p_assignment_action_id);
1185 IF l_assignment_id is null THEN
1186 --
1187 /* -- The assignment action is not a payroll or quickpay type, so return null */
1188 --
1189 l_balance := null;
1190 ELSE
1191 --
1192 l_balance := calc_asg_proc_ptd(
1193 p_assignment_action_id => p_assignment_action_id,
1194 p_balance_type_id => p_balance_type_id,
1195 p_assignment_id => l_assignment_id);
1196 END IF;
1197 --
1198 RETURN l_balance;
1199 END calc_asg_proc_ptd_action;
1200 --
1201 /* ------------------------------------------------------------------------------------
1202 --
1203 -- CALC_ASG_PROC_PTD_DATE
1204 --
1205 -- This is the function for calculating assignment processing
1206 -- period to date in date mode
1207 --
1208 -- ------------------------------------------------------------------------------------ */
1209 FUNCTION calc_asg_proc_ptd_date(
1210 p_assignment_id IN NUMBER,
1211 p_balance_type_id IN NUMBER,
1212 p_effective_date IN DATE)
1213 RETURN NUMBER
1214 IS
1215 --
1216 l_assignment_action_id NUMBER;
1217 l_balance NUMBER;
1218 l_end_date DATE;
1219 l_defined_balance_id NUMBER;
1220 --
1221 /* -- Has the processing time period expired */
1222 --
1223 cursor expired_time_period(c_assignment_action_id IN NUMBER)
1224 is
1225 SELECT /*+ ORDERED */
1226 ptp.end_date
1227 FROM pay_assignment_actions paa,
1228 pay_payroll_actions ppa,
1229 per_time_periods ptp
1230 WHERE paa.assignment_action_id = c_assignment_action_id
1231 AND ppa.payroll_action_id = paa.payroll_action_id
1232 AND ptp.time_period_id = ppa.time_period_id;
1233 --
1234 BEGIN
1235 --
1236 l_defined_balance_id := dimension_relevant(p_balance_type_id, hr_jprts.g_asg_proc_ptd);
1237 /* -- check relevant dimension. if it is not so(defined_balance_id is null), return null */
1238 --
1239 if l_defined_balance_id is not null then
1240 --
1241 l_assignment_action_id := get_latest_action_id( p_assignment_id,
1242 p_effective_date);
1243 IF l_assignment_action_id is null THEN
1244 l_balance := 0;
1245 ELSE
1246 OPEN expired_time_period(l_assignment_action_id);
1247 FETCH expired_time_period INTO l_end_date;
1248 CLOSE expired_time_period;
1249 --
1250 if l_end_date < p_effective_date then
1251 l_balance := 0;
1252 else
1253 l_balance := calc_asg_proc_ptd(
1254 p_assignment_action_id => l_assignment_action_id,
1255 p_balance_type_id => p_balance_type_id,
1256 p_assignment_id => p_assignment_id);
1257 end if;
1258 END IF;
1259 else l_balance := null;
1260 end if;
1261 --
1262 RETURN l_balance;
1263 END calc_asg_proc_ptd_date;
1264 --
1265 /* ------------------------------------------------------------------------------------
1266 --
1267 -- CALC_ASG_PROC_PTD --
1268 --
1269 -- calculate balances for Assignment process period to date
1270 --
1271 -- ------------------------------------------------------------------------------------ */
1272 /* -- This dimension is the total for an assignment within the processing
1273 -- period of his current payroll, OR if the assignment has transferred
1274 -- payroll within the current processing period, it is the total since
1275 -- he joined the current payroll.
1276 --
1277 -- This dimension should be used for the period dimension of balances
1278 -- which are reset to zero on transferring payroll. */
1279 --
1280 FUNCTION calc_asg_proc_ptd(
1281 p_assignment_action_id IN NUMBER,
1282 p_balance_type_id IN NUMBER,
1283 p_assignment_id IN NUMBER)
1284 --
1285 RETURN NUMBER
1286 IS
1287 --
1288 l_expired_balance NUMBER;
1289 l_assignment_action_id NUMBER;
1290 l_balance NUMBER;
1291 l_latest_value_exists VARCHAR2(2);
1292 l_action_eff_date DATE;
1293 l_end_date DATE;
1294 l_defined_bal_id NUMBER;
1295 --
1296 BEGIN
1297 --
1298 l_defined_bal_id := dimension_relevant(p_balance_type_id, hr_jprts.g_asg_proc_ptd);
1299 --
1300 if l_defined_bal_id is not null then
1301 l_balance := pay_balance_pkg.get_value(
1302 l_defined_bal_id,
1303 p_assignment_action_id);
1304 --
1305 else
1306 l_balance := null;
1307 end if;
1308 --
1309 RETURN l_balance;
1310 END calc_asg_proc_ptd;
1311 --
1312 /* ------------------------------------------------------------------------------------
1313 --
1314 -- CALC_ASG_MTD_JP_ACTION --
1315 --
1316 -- This is the function for calculating JP specific assignment processing
1317 -- month to date in assignment action mode
1318 --
1319 -- ------------------------------------------------------------------------------------ */
1320 FUNCTION calc_asg_mtd_jp_action(
1321 p_assignment_action_id IN NUMBER,
1322 p_balance_type_id IN NUMBER)
1323 -- p_dimension_name IN VARCHAR2)
1324 RETURN NUMBER
1325 IS
1326 --
1327 l_assignment_action_id NUMBER;
1328 l_balance NUMBER;
1329 l_assignment_id NUMBER;
1330 --
1331 BEGIN
1332 --
1333 l_assignment_id := get_correct_type(p_assignment_action_id);
1334 IF l_assignment_id is null THEN
1335 --
1336 /* -- The assignment action is not a payroll or quickpay type, so return null */
1337 --
1338 l_balance := null;
1339 ELSE
1340 --
1341 l_balance := calc_asg_mtd_jp(
1342 p_assignment_action_id => p_assignment_action_id,
1343 p_balance_type_id => p_balance_type_id,
1344 p_assignment_id => l_assignment_id);
1345 -- p_dimension_name => p_dimension_name);
1346 END IF;
1347 --
1348 RETURN l_balance;
1349 END calc_asg_mtd_jp_action;
1350 --
1351 /* ------------------------------------------------------------------------------------
1352 --
1353 -- CALC_ASG_MTD_JP_DATE --
1354 --
1355 -- This is the function for calculating JP specific assignment processing
1356 -- month to date in date mode
1357 --
1358 -- ------------------------------------------------------------------------------------ */
1359 --
1360 FUNCTION calc_asg_mtd_jp_date(
1361 p_assignment_id IN NUMBER,
1362 p_balance_type_id IN NUMBER,
1363 p_effective_date IN DATE)
1364 -- p_dimension_name IN VARCHAR2)
1365 RETURN NUMBER
1366 IS
1367 --
1368 l_assignment_action_id NUMBER;
1369 l_balance NUMBER;
1370 l_last_start_date DATE;
1371 l_defined_balance_id NUMBER;
1372 --
1373 cursor last_start_date(c_assignment_action_id IN NUMBER)
1374 is
1375 SELECT /*+ ORDERED */
1376 trunc(ppa.effective_date,'MM')
1377 FROM pay_assignment_actions paa,
1378 pay_payroll_actions ppa
1379 WHERE paa.assignment_action_id = c_assignment_action_id
1380 AND ppa.payroll_action_id = paa.payroll_action_id;
1381 --
1382 BEGIN
1383 --
1384 l_defined_balance_id := dimension_relevant( p_balance_type_id,
1385 hr_jprts.g_asg_mtd_jp);
1386 /* -- check relevant dimension. if it is not so(defined_balance_id is null), return null */
1387 --
1388 if l_defined_balance_id is not null then
1389 --
1390 l_assignment_action_id := get_latest_action_id(
1391 p_assignment_id,
1392 p_effective_date);
1393 IF l_assignment_action_id is null THEN
1394 l_balance := 0;
1395 ELSE
1396 OPEN last_start_date(l_assignment_action_id);
1397 FETCH last_start_date INTO l_last_start_date;
1398 CLOSE last_start_date;
1399 --
1400 if add_months(l_last_start_date,1) <= p_effective_date then
1401 l_balance := 0;
1402 else
1403 l_balance := calc_asg_mtd_jp(
1404 p_assignment_action_id => l_assignment_action_id,
1405 p_balance_type_id => p_balance_type_id,
1406 p_assignment_id => p_assignment_id);
1407 -- P_dimension_name => p_dimension_name);
1408 end if;
1409 END IF;
1410 else
1411 l_balance := null;
1412 end if;
1413 --
1414 RETURN l_balance;
1415 END calc_asg_mtd_jp_date;
1416 --
1417 /* ------------------------------------------------------------------------------------
1418 --
1419 -- CALC_ASG_MTD_JP --
1420 --
1421 -- Calculate balances for JP specific Assignment process month to date
1422 --
1423 -- ------------------------------------------------------------------------------------ */
1424 FUNCTION calc_asg_mtd_jp(
1425 p_assignment_action_id IN NUMBER,
1426 p_balance_type_id IN NUMBER,
1427 p_assignment_id IN NUMBER)
1428 -- p_dimension_name IN VARCHAR2)
1429 RETURN NUMBER
1430 IS
1431 --
1432 --
1433 l_expired_balance NUMBER;
1434 l_assignment_action_id NUMBER;
1435 l_balance NUMBER;
1436 l_latest_value_exists VARCHAR2(2);
1437 l_action_eff_date DATE;
1438 l_end_date DATE;
1439 l_defined_bal_id NUMBER;
1440 --
1441 BEGIN
1442 --
1443 l_defined_bal_id := dimension_relevant(p_balance_type_id,hr_jprts.g_asg_mtd_jp);
1444 --
1445 if l_defined_bal_id is not null then
1446 l_balance := pay_balance_pkg.get_value(
1447 l_defined_bal_id,
1448 p_assignment_action_id);
1449 --
1450 else
1451 l_balance := null;
1452 end if;
1453 --
1454 RETURN l_balance;
1455 END calc_asg_mtd_jp;
1456 --
1457 /* ------------------------------------------------------------------------------------
1458 --
1459 -- CALC_ASG_YTD_JP_ACTION --
1460 --
1461 -- This is the function for calculating JP specific assignment processing
1462 -- year to date in assignment action mode
1463 --
1464 -- ------------------------------------------------------------------------------------ */
1465 FUNCTION calc_asg_ytd_jp_action(
1466 p_assignment_action_id IN NUMBER,
1467 p_balance_type_id IN NUMBER)
1468 -- p_dimension_name IN VARCHAR2)
1469 RETURN NUMBER
1470 IS
1471 --
1472 l_assignment_action_id NUMBER;
1473 l_balance NUMBER;
1474 l_assignment_id NUMBER;
1475 --
1476 BEGIN
1477 --
1478 l_assignment_id := get_correct_type(p_assignment_action_id);
1479 IF l_assignment_id is null THEN
1480 --
1481 /* -- The assignment action is not a payroll or quickpay type, so return null */
1482 --
1483 l_balance := null;
1484 ELSE
1485 --
1486 l_balance := calc_asg_ytd_jp(
1487 p_assignment_action_id,
1488 p_balance_type_id,
1489 l_assignment_id);
1490 -- p_dimension_name);
1491 END IF;
1492 --
1493 RETURN l_balance;
1494 END calc_asg_ytd_jp_action;
1495 --
1496 /* ------------------------------------------------------------------------------------
1497 --
1498 -- CALC_ASG_YTD_JP_DATE --
1499 --
1500 -- This is the function for calculating JP specific assignment processing
1501 -- year to date in date mode
1502 --
1503 -- ------------------------------------------------------------------------------------ */
1504 FUNCTION calc_asg_ytd_jp_date(
1505 p_assignment_id IN NUMBER,
1506 p_balance_type_id IN NUMBER,
1507 p_effective_date IN DATE)
1508 -- p_dimension_name IN VARCHAR2)
1509 RETURN NUMBER
1510 IS
1511 --
1512 l_assignment_action_id NUMBER;
1513 l_balance NUMBER;
1514 l_last_start_date DATE;
1515 l_defined_balance_id NUMBER;
1516 --
1517 cursor last_start_date(c_assignment_action_id IN NUMBER)
1518 is
1519 SELECT /*+ ORDERED */
1520 trunc(ppa.effective_date,'YYYY')
1521 FROM pay_assignment_actions paa,
1522 pay_payroll_actions ppa
1523 WHERE paa.assignment_action_id = c_assignment_action_id
1524 AND ppa.payroll_action_id = paa.payroll_action_id;
1525 --
1526 BEGIN
1527 --
1528 l_defined_balance_id := dimension_relevant(p_balance_type_id, hr_jprts.g_ast_ytd_jp);
1529 /* -- check relevant dimension. if it is not so(defined_balance_id is null), return null */
1530 --
1531 if l_defined_balance_id is not null then
1532 --
1533 l_assignment_action_id := get_latest_action_id(p_assignment_id,
1534 p_effective_date);
1535 IF l_assignment_action_id is null THEN
1536 l_balance := 0;
1537 ELSE
1538 OPEN last_start_date(l_assignment_action_id);
1539 FETCH last_start_date INTO l_last_start_date;
1540 CLOSE last_start_date;
1541 --
1542 if add_months(l_last_start_date,12) <= p_effective_date then
1543 l_balance := 0;
1544 else
1545 l_balance := calc_asg_ytd_jp(
1546 l_assignment_action_id,
1547 p_balance_type_id,
1548 p_assignment_id);
1549 -- p_dimension_name);
1550 end if;
1551 END IF;
1552 else
1553 l_balance := null;
1554 end if;
1555 --
1556 RETURN l_balance;
1557 END calc_asg_ytd_jp_date;
1558 --
1559 /* ------------------------------------------------------------------------------------
1560 --
1561 -- CALC_ASG_YTD_JP --
1562 --
1563 -- Calculate balances for JP specific Assignment process year to date
1564 --
1565 -- ------------------------------------------------------------------------------------ */
1566 FUNCTION calc_asg_ytd_jp(
1567 p_assignment_action_id IN NUMBER,
1568 p_balance_type_id IN NUMBER,
1569 p_assignment_id IN NUMBER)
1570 -- p_dimension_name IN VARCHAR2)
1571 RETURN NUMBER
1572 IS
1573 --
1574 l_expired_balance NUMBER;
1575 l_assignment_action_id NUMBER;
1576 l_balance NUMBER;
1577 l_latest_value_exists VARCHAR2(2);
1578 l_action_eff_date DATE;
1579 l_end_date DATE;
1580 l_defined_bal_id NUMBER;
1581 --
1582 BEGIN
1583 --
1584 l_defined_bal_id := dimension_relevant(p_balance_type_id, hr_jprts.g_ast_ytd_jp);
1585 --
1586 if l_defined_bal_id is not null then
1587 l_balance := pay_balance_pkg.get_value(
1588 l_defined_bal_id,
1589 p_assignment_action_id);
1590 --
1591 else
1592 l_balance := null;
1593 end if;
1594 --
1595 RETURN l_balance;
1596 END calc_asg_ytd_jp;
1597 --
1598 /* ------------------------------------------------------------------------------------
1599 --
1600 -- CALC_ASG_FYTD_JP_ACTION --
1601 --
1602 -- This is the function for calculating JP specific assignment processing
1603 -- Financial year to date in assignment action mode
1604 --
1605 -- ------------------------------------------------------------------------------------ */
1606 FUNCTION calc_asg_fytd_jp_action(
1607 p_assignment_action_id IN NUMBER,
1608 p_balance_type_id IN NUMBER)
1609 -- p_dimension_name IN VARCHAR2)
1610 RETURN NUMBER
1611 IS
1612 --
1613 l_assignment_action_id NUMBER;
1614 l_balance NUMBER;
1615 l_assignment_id NUMBER;
1616 --
1617 BEGIN
1618 --
1619 l_assignment_id := get_correct_type(p_assignment_action_id);
1620 IF l_assignment_id is null THEN
1621 --
1622 /* -- The assignment action is not a payroll or quickpay type, so return null */
1623 --
1624 l_balance := null;
1625 ELSE
1626 --
1627 l_balance := calc_asg_fytd_jp(
1628 p_assignment_action_id => p_assignment_action_id,
1629 p_balance_type_id => p_balance_type_id,
1630 p_assignment_id => l_assignment_id);
1631 -- p_dimension_name => p_dimension_name);
1632 END IF;
1633 --
1634 RETURN l_balance;
1635 END calc_asg_fytd_jp_action;
1636 --
1637 /* ------------------------------------------------------------------------------------
1638 --
1639 -- CALC_ASG_FYTD_JP_DATE --
1640 --
1641 -- This is the function for calculating JP specific assignment processing
1642 -- Financial year to date in date mode
1643 --
1644 -- ------------------------------------------------------------------------------------ */
1645 FUNCTION calc_asg_fytd_jp_date(
1646 p_assignment_id IN NUMBER,
1647 p_balance_type_id IN NUMBER,
1648 p_effective_date IN DATE)
1649 -- p_dimension_name IN VARCHAR2)
1650 RETURN NUMBER
1651 IS
1652 --
1653 l_assignment_action_id NUMBER;
1654 l_balance NUMBER;
1655 l_last_start_date DATE;
1656 l_date_earned DATE;
1657 l_defined_balance_id NUMBER;
1658 --
1659 cursor last_start_date(c_assignment_action_id IN NUMBER)
1660 is
1661 SELECT /*+ ORDERED */
1662 add_months(nvl(FND_DATE.CANONICAL_TO_DATE(HROG.org_information11),trunc(PACT.date_earned,'YYYY')),
1663 floor(months_between(PACT.date_earned,nvl(FND_DATE.CANONICAL_TO_DATE(HROG.org_information11),
1664 trunc(PACT.date_earned,'YYYY')))/12)*12) last_start_date
1665 FROM pay_assignment_actions ASSACT,
1666 pay_payroll_actions PACT,
1667 hr_organization_information HROG
1668 WHERE ASSACT.assignment_action_id = c_assignment_action_id
1669 AND PACT.payroll_action_id = ASSACT.payroll_action_id
1670 AND HROG.organization_id = PACT.business_group_id
1671 AND HROG.org_information_context = 'Business Group Information';
1672 --
1673 cursor get_latest_id (c_assignment_id IN NUMBER,
1674 c_effective_date IN DATE)
1675 is
1676 SELECT /*+ ORDERED */
1677 TO_NUMBER(substr(max(lpad(ASSACT.action_sequence,15,'0')||
1678 ASSACT.assignment_action_id),16))
1679 FROM pay_assignment_actions ASSACT,
1680 pay_payroll_actions PACT
1681 WHERE ASSACT.assignment_id = c_assignment_id
1682 AND PACT.payroll_action_id = ASSACT.payroll_action_id
1683 AND PACT.date_earned <= c_effective_date
1684 AND PACT.action_type in ('R', 'Q', 'I', 'V', 'B');
1685 --
1686 BEGIN
1687 --
1688 l_defined_balance_id := dimension_relevant(p_balance_type_id, hr_jprts.g_asg_fytd_jp);
1689 /* -- check relevant dimension. if it is not so(defined_balance_id is null), return null */
1690 --
1691 if l_defined_balance_id is not null then
1692 --
1693 /* -- p_effective_date <= session_date */
1694 OPEN get_latest_id( p_assignment_id,
1695 p_effective_date);
1696 FETCH get_latest_id INTO l_assignment_action_id;
1697 CLOSE get_latest_id;
1698 --
1699 IF l_assignment_action_id is null THEN
1700 l_balance := 0;
1701 ELSE
1702 OPEN last_start_date(l_assignment_action_id);
1703 FETCH last_start_date INTO l_last_start_date;
1704 CLOSE last_start_date;
1705 --
1706 /* -- p_effective_date <= session_date */
1707 if add_months(l_last_start_date,12) <= p_effective_date then
1708 l_balance := 0;
1709 else
1710 l_balance := calc_asg_fytd_jp(
1711 p_assignment_action_id => l_assignment_action_id,
1712 p_balance_type_id => p_balance_type_id,
1713 p_assignment_id => p_assignment_id);
1714 -- p_dimension_name => p_dimension_name);
1715 end if;
1716 END IF;
1717 else
1718 l_balance := null;
1719 end if;
1720 --
1721 RETURN l_balance;
1722 END calc_asg_fytd_jp_date;
1723 --
1724 /* ------------------------------------------------------------------------------------
1725 --
1726 -- CALC_ASG_FYTD_JP --
1727 --
1728 -- Calculate balances for JP specific Assignment process financial year to date
1729 --
1730 -- ------------------------------------------------------------------------------------ */
1731 FUNCTION calc_asg_fytd_jp(
1732 p_assignment_action_id IN NUMBER,
1733 p_balance_type_id IN NUMBER,
1734 p_assignment_id IN NUMBER)
1735 -- p_dimension_name IN VARCHAR2)
1736 RETURN NUMBER
1737 IS
1738 --
1739 l_balance NUMBER;
1740 l_defined_bal_id NUMBER;
1741 --
1742 BEGIN
1743 --
1744 /* --Do we need to work out nocopy a value for this dimension/balance combination.
1745 --Used dimension_name in dimension_relevant because of unique column */
1746 --
1747 l_defined_bal_id := dimension_relevant(p_balance_type_id, hr_jprts.g_asg_fytd_jp);
1748 if l_defined_bal_id is not null then
1749 --
1750 /* -- This balances will never have a value in pay_assignment_latest_balances
1751 -- table, as they are only used for the duration of the payroll run.
1752 -- We therefore don't need to check the table, time can be saved by
1753 -- simply calling the route code, which is incidentally the most
1754 -- performant (ie simple) route. */
1755 --
1756 -- Remain hr_jprts.asg_fytd_jp function since better than
1757 -- using dimension_reset_date_userdef/calc_bal_date_earned
1758 --
1759 l_balance := hr_jprts.asg_fytd_jp(
1760 p_assignment_action_id,
1761 p_balance_type_id);
1762 --
1763 else
1764 l_balance := null;
1765 end if;
1766 --
1767 RETURN l_balance;
1768 END calc_asg_fytd_jp;
1769 --
1770 --
1771 /* ------------------------------------------------------------------------------------
1772 --
1773 -- CALC_ASG_FYTD2_JP_ACTION --
1774 --
1775 -- This is the function for calculating JP specific assignment processing
1776 -- Business year to date in assignment action mode
1777 --
1778 -- ------------------------------------------------------------------------------------ */
1779 FUNCTION calc_asg_fytd2_jp_action(
1780 p_assignment_action_id IN NUMBER,
1781 p_balance_type_id IN NUMBER)
1782 RETURN NUMBER
1783 IS
1784 --
1785 l_assignment_action_id NUMBER;
1786 l_balance NUMBER;
1787 l_assignment_id NUMBER;
1788 --
1789 BEGIN
1790 --
1791 l_assignment_id := get_correct_type(p_assignment_action_id);
1792 --
1793 IF l_assignment_id is null THEN
1794 --
1795 /* -- The assignment action is not a payroll or quickpay type, so return null */
1796 --
1797 l_balance := null;
1798 ELSE
1799 --
1800 l_balance := calc_asg_fytd2_jp(
1801 p_assignment_action_id => p_assignment_action_id,
1802 p_balance_type_id => p_balance_type_id,
1803 p_assignment_id => l_assignment_id);
1804 END IF;
1805 --
1806 RETURN l_balance;
1807 END calc_asg_fytd2_jp_action;
1808 --
1809 /* ------------------------------------------------------------------------------------
1810 --
1811 -- CALC_ASG_FYTD_JP2_DATE --
1812 --
1813 -- This is the function for calculating JP specific assignment processing
1814 -- Business year to date in date mode
1815 --
1816 -- ------------------------------------------------------------------------------------ */
1817 FUNCTION calc_asg_fytd2_jp_date(
1818 p_assignment_id IN NUMBER,
1819 p_balance_type_id IN NUMBER,
1820 p_effective_date IN DATE)
1821 RETURN NUMBER
1822 IS
1823 --
1824 l_assignment_action_id NUMBER;
1825 l_balance NUMBER;
1826 l_last_start_date DATE;
1827 l_effective_date DATE;
1828 l_defined_balance_id NUMBER;
1829 --
1830 cursor last_start_date(c_assignment_action_id IN NUMBER)
1831 is
1832 SELECT /*+ ORDERED */
1833 add_months(nvl(FND_DATE.CANONICAL_TO_DATE(HROG.org_information11),trunc(PACT.effective_date,'YYYY')),
1834 floor(months_between(PACT.effective_date,nvl(FND_DATE.CANONICAL_TO_DATE(HROG.org_information11),
1835 trunc(PACT.effective_date,'YYYY')))/12)*12) last_start_date
1836 FROM pay_assignment_actions ASSACT,
1837 pay_payroll_actions PACT,
1838 hr_organization_information HROG
1839 WHERE ASSACT.assignment_action_id = c_assignment_action_id
1840 AND PACT.payroll_action_id = ASSACT.payroll_action_id
1841 AND HROG.organization_id = PACT.business_group_id
1842 AND HROG.org_information_context = 'Business Group Information';
1843 --
1844 cursor get_latest_id (
1845 c_assignment_id IN NUMBER,
1846 c_effective_date IN DATE)
1847 is
1848 SELECT /*+ ORDERED */
1849 TO_NUMBER(substr(max(lpad(ASSACT.action_sequence,15,'0')||
1850 ASSACT.assignment_action_id),16))
1851 FROM pay_assignment_actions ASSACT,
1852 pay_payroll_actions PACT
1853 WHERE ASSACT.assignment_id = c_assignment_id
1854 AND PACT.payroll_action_id = ASSACT.payroll_action_id
1855 AND PACT.effective_date <= c_effective_date
1856 AND PACT.action_type in ('R', 'Q', 'I', 'V', 'B');
1857 --
1858 BEGIN
1859 --
1860 l_defined_balance_id := dimension_relevant(p_balance_type_id, hr_jprts.g_asg_fytd2_jp);
1861 /* -- check relevant dimension. if it is not so(defined_balance_id is null), return null */
1862 --
1863 if l_defined_balance_id is not null then
1864 --
1865 /* -- p_effective_date <= session_date */
1866 OPEN get_latest_id(
1867 p_assignment_id,
1868 p_effective_date);
1869 FETCH get_latest_id INTO l_assignment_action_id;
1870 CLOSE get_latest_id;
1871 --
1872 IF l_assignment_action_id is null THEN
1873 l_balance := 0;
1874 ELSE
1875 OPEN last_start_date(l_assignment_action_id);
1876 FETCH last_start_date INTO l_last_start_date;
1877 CLOSE last_start_date;
1878 --
1879 /* -- p_effective_date <= session_date */
1880 if add_months(l_last_start_date,12) <= p_effective_date then
1881 l_balance := 0;
1882 else
1883 l_balance := calc_asg_fytd2_jp(
1884 p_assignment_action_id => l_assignment_action_id,
1885 p_balance_type_id => p_balance_type_id,
1886 p_assignment_id => p_assignment_id);
1887 end if;
1888 END IF;
1889 else
1890 l_balance := null;
1891 end if;
1892 --
1893 RETURN l_balance;
1894 END calc_asg_fytd2_jp_date;
1895 --
1896 /* ------------------------------------------------------------------------------------
1897 --
1898 -- CALC_ASG_FYTD2_JP --
1899 --
1900 -- Calculate balances for JP specific Assignment process financial year to date
1901 --
1902 -- ------------------------------------------------------------------------------------ */
1903 FUNCTION calc_asg_fytd2_jp(
1904 p_assignment_action_id IN NUMBER,
1905 p_balance_type_id IN NUMBER,
1906 p_assignment_id IN NUMBER)
1907 RETURN NUMBER
1908 IS
1909 --
1910 l_balance NUMBER;
1911 l_defined_bal_id NUMBER;
1912 --
1913 BEGIN
1914 --
1915 l_defined_bal_id := dimension_relevant(p_balance_type_id, hr_jprts.g_asg_fytd2_jp);
1916 --
1917 if l_defined_bal_id is not null then
1918 --
1919 l_balance := pay_balance_pkg.get_value(
1920 l_defined_bal_id,
1921 p_assignment_action_id);
1922 --
1923 else
1924 l_balance := null;
1925 end if;
1926 --
1927 RETURN l_balance;
1928 END calc_asg_fytd2_jp;
1929 --
1930 /* ------------------------------------------------------------------------------------
1931 --
1932 -- CALC_ASG_APR2MAR_JP_ACTION
1933 --
1934 -- This is the function for calculating JP specific assignment process
1935 -- between Apr and Mar in assignment action mode
1936 --
1937 -- ------------------------------------------------------------------------------------ */
1938 function calc_asg_apr2mar_jp_action(
1939 p_assignment_action_id in number,
1940 p_balance_type_id in number)
1941 return number
1942 is
1943 --
1944 l_assignment_action_id number;
1945 l_balance number;
1946 l_assignment_id number;
1947 --
1948 begin
1949 --
1950 l_assignment_id := get_correct_type(p_assignment_action_id);
1951 --
1952 if l_assignment_id is null then
1953 --
1954 l_balance := null;
1955 --
1956 else
1957 --
1958 l_balance := calc_asg_apr2mar_jp(
1959 p_assignment_action_id => p_assignment_action_id,
1960 p_balance_type_id => p_balance_type_id,
1961 p_assignment_id => l_assignment_id);
1962 --
1963 end if;
1964 --
1965 return l_balance;
1966 end calc_asg_apr2mar_jp_action;
1967 --
1968 /* ------------------------------------------------------------------------------------
1969 --
1970 -- CALC_ASG_APR2MAR_JP_DATE
1971 --
1972 -- This is the function for calculating JP specific assignment process
1973 -- between Apr and Mar in date mode
1974 --
1975 -- ------------------------------------------------------------------------------------ */
1976 function calc_asg_apr2mar_jp_date(
1977 p_assignment_id in number,
1978 p_balance_type_id in number,
1979 p_effective_date in date)
1980 return number
1981 is
1982 --
1983 l_assignment_action_id number;
1984 l_balance number;
1985 l_last_start_date date;
1986 l_defined_balance_id number;
1987 --
1988 cursor last_start_date(c_assignment_action_id in number)
1989 is
1990 select /*+ ORDERED */
1991 add_months(trunc(add_months(ppa.effective_date,9),'YYYY'),-9)
1992 from pay_assignment_actions paa,
1993 pay_payroll_actions ppa
1994 where paa.assignment_action_id = c_assignment_action_id
1995 and ppa.payroll_action_id = paa.payroll_action_id;
1996 --
1997 begin
1998 --
1999 l_defined_balance_id := dimension_relevant(p_balance_type_id, pyjpexc.c_asg_aprtd);
2000 --
2001 if l_defined_balance_id is not null then
2002 --
2003 l_assignment_action_id := get_latest_action_id(
2004 p_assignment_id,
2005 p_effective_date);
2006 --
2007 if l_assignment_action_id is null then
2008 l_balance := 0;
2009 else
2010 --
2011 open last_start_date(l_assignment_action_id);
2012 fetch last_start_date into l_last_start_date;
2013 close last_start_date;
2014 --
2015 if add_months(l_last_start_date,12) <= p_effective_date then
2016 l_balance := 0;
2017 else
2018 --
2019 l_balance := calc_asg_apr2mar_jp(
2020 p_assignment_action_id => l_assignment_action_id,
2021 p_balance_type_id => p_balance_type_id,
2022 p_assignment_id => p_assignment_id);
2023 --
2024 end if;
2025 --
2026 end if;
2027 --
2028 else
2029 --
2030 l_balance := null;
2031 --
2032 end if;
2033 --
2034 return l_balance;
2035 end calc_asg_apr2mar_jp_date;
2036 --
2037 /* ------------------------------------------------------------------------------------
2038 ---
2039 --
2040 -- CALC_ASG_APR2MAR_JP --
2041 --
2042 -- Calculate balances for JP specific Assignment process between Apr and Mar
2043 --
2044 -- ------------------------------------------------------------------------------------ */
2045 function calc_asg_apr2mar_jp(
2046 p_assignment_action_id in number,
2047 p_balance_type_id in number,
2048 p_assignment_id in number)
2049 return number
2050 is
2051 --
2052 l_expired_balance number;
2053 l_assignment_action_id number;
2054 l_balance number;
2055 l_latest_value_exists varchar2(2);
2056 l_action_eff_date date;
2057 l_end_date date;
2058 l_defined_bal_id number;
2059 --
2060 begin
2061 --
2062 l_defined_bal_id := dimension_relevant(p_balance_type_id, pyjpexc.c_asg_aprtd);
2063 --
2064 if l_defined_bal_id is not null then
2065 --
2066 l_balance := pay_balance_pkg.get_value(
2067 l_defined_bal_id,
2068 p_assignment_action_id);
2069 --
2070 else
2071 l_balance := null;
2072 end if;
2073 --
2074 return l_balance;
2075 end calc_asg_apr2mar_jp;
2076 --
2077 /* ------------------------------------------------------------------------------------
2078 --
2079 -- CALC_ASG_AUG2JUL_JP_ACTION --
2080 --
2081 -- This is the function for calculating JP specific assignment process
2082 -- between Jan and Aug in assignment action mode
2083 --
2084 -- ------------------------------------------------------------------------------------ */
2085 FUNCTION calc_asg_aug2jul_jp_action(
2086 p_assignment_action_id IN NUMBER,
2087 p_balance_type_id IN NUMBER)
2088 -- p_dimension_name IN VARCHAR2)
2089 RETURN NUMBER
2090 IS
2091 --
2092 l_assignment_action_id NUMBER;
2093 l_balance NUMBER;
2094 l_assignment_id NUMBER;
2095 --
2096 BEGIN
2097 --
2098 l_assignment_id := get_correct_type(p_assignment_action_id);
2099 IF l_assignment_id is null THEN
2100 --
2101 /* -- The assignment action is not a payroll or quickpay type, so return null */
2102 --
2103 l_balance := null;
2104 ELSE
2105 --
2106 l_balance := calc_asg_aug2jul_jp(
2107 p_assignment_action_id => p_assignment_action_id,
2108 p_balance_type_id => p_balance_type_id,
2109 p_assignment_id => l_assignment_id);
2110 -- p_dimension_name => p_dimension_name);
2111 END IF;
2112 --
2113 RETURN l_balance;
2114 END calc_asg_aug2jul_jp_action;
2115 --
2116 /* ------------------------------------------------------------------------------------
2117 --
2118 -- CALC_ASG_AUG2JUL_JP_DATE --
2119 --
2120 -- This is the function for calculating JP specific assignment process
2121 -- between Jan and Aug in date mode
2122 --
2123 -- ------------------------------------------------------------------------------------ */
2124 FUNCTION calc_asg_aug2jul_jp_date(
2125 p_assignment_id IN NUMBER,
2126 p_balance_type_id IN NUMBER,
2127 p_effective_date IN DATE)
2128 -- p_dimension_name IN VARCHAR2)
2129 RETURN NUMBER
2130 IS
2131 --
2132 l_assignment_action_id NUMBER;
2133 l_balance NUMBER;
2134 l_last_start_date DATE;
2135 l_defined_balance_id NUMBER;
2136 --
2137 cursor last_start_date(c_assignment_action_id IN NUMBER)
2138 is
2139 SELECT /*+ ORDERED */
2140 add_months(trunc(add_months(ppa.effective_date,5),'YYYY'),-5)
2141 FROM pay_assignment_actions paa,
2142 pay_payroll_actions ppa
2143 WHERE paa.assignment_action_id = c_assignment_action_id
2144 AND ppa.payroll_action_id = paa.payroll_action_id;
2145 --
2146 BEGIN
2147 --
2148 l_defined_balance_id := dimension_relevant(p_balance_type_id, hr_jprts.g_asg_aug2jul_jp);
2149 /* -- check relevant dimension. if it is not so(defined_balance_id is null), return null */
2150 --
2151 if l_defined_balance_id is not null then
2152 --
2153 l_assignment_action_id := get_latest_action_id(
2154 p_assignment_id,
2155 p_effective_date);
2156 IF l_assignment_action_id is null THEN
2157 l_balance := 0;
2158 ELSE
2159 OPEN last_start_date(l_assignment_action_id);
2160 FETCH last_start_date INTO l_last_start_date;
2161 CLOSE last_start_date;
2162 --
2163 if add_months(l_last_start_date,12) <= p_effective_date then
2164 l_balance := 0;
2165 else
2166 l_balance := calc_asg_aug2jul_jp(
2167 p_assignment_action_id => l_assignment_action_id,
2168 p_balance_type_id => p_balance_type_id,
2169 p_assignment_id => p_assignment_id);
2170 -- p_dimension_name => p_dimension_name);
2171 end if;
2172 END IF;
2173 else
2174 l_balance := null;
2175 end if;
2176 --
2177 RETURN l_balance;
2178 END calc_asg_aug2jul_jp_date;
2179 --
2180 /* ------------------------------------------------------------------------------------
2181 ---
2182 --
2183 -- CALC_ASG_AUG2JUL_JP --
2184 --
2185 -- Calculate balances for JP specific Assignment process between Jan and Aug
2186 --
2187 -- ------------------------------------------------------------------------------------ */
2188 FUNCTION calc_asg_aug2jul_jp(
2189 p_assignment_action_id IN NUMBER,
2190 p_balance_type_id IN NUMBER,
2191 p_assignment_id IN NUMBER)
2192 -- p_dimension_name IN VARCHAR2)
2193 RETURN NUMBER
2194 IS
2195 --
2196 l_expired_balance NUMBER;
2197 l_assignment_action_id NUMBER;
2198 l_balance NUMBER;
2199 l_latest_value_exists VARCHAR2(2);
2200 l_action_eff_date DATE;
2201 l_end_date DATE;
2202 l_defined_bal_id NUMBER;
2203 --
2204 BEGIN
2205 --
2206 l_defined_bal_id := dimension_relevant(p_balance_type_id, hr_jprts.g_asg_aug2jul_jp);
2207 --
2208 if l_defined_bal_id is not null then
2209 l_balance := pay_balance_pkg.get_value(
2210 l_defined_bal_id,
2211 p_assignment_action_id);
2212 --
2213 else
2214 l_balance := null;
2215 end if;
2216 --
2217 RETURN l_balance;
2218 END calc_asg_aug2jul_jp;
2219 --
2220 /* ------------------------------------------------------------------------------------
2221 --
2222 -- CALC_ASG_JUL2JUN_JP_ACTION --
2223 --
2224 -- This is the function for calculating JP specific assignment process
2225 -- between Jan and Jul in assignment action mode
2226 --
2227 -- ------------------------------------------------------------------------------------ */
2228 FUNCTION calc_asg_jul2jun_jp_action(
2229 p_assignment_action_id IN NUMBER,
2230 p_balance_type_id IN NUMBER)
2231 RETURN NUMBER
2232 IS
2233 --
2234 l_assignment_action_id NUMBER;
2235 l_balance NUMBER;
2236 l_assignment_id NUMBER;
2237 --
2238 BEGIN
2239 --
2240 l_assignment_id := get_correct_type(p_assignment_action_id);
2241 IF l_assignment_id is null THEN
2242 --
2243 /* -- The assignment action is not a payroll or quickpay type, so return null */
2244 --
2245 l_balance := null;
2246 ELSE
2247 --
2248 l_balance := calc_asg_jul2jun_jp(
2249 p_assignment_action_id => p_assignment_action_id,
2250 p_balance_type_id => p_balance_type_id,
2251 p_assignment_id => l_assignment_id);
2252 END IF;
2253 --
2254 RETURN l_balance;
2255 END calc_asg_jul2jun_jp_action;
2256 --
2257 /* ------------------------------------------------------------------------------------
2258 --
2259 -- CALC_ASG_JUL2JUN_JP_DATE --
2260 --
2261 -- This is the function for calculating JP specific assignment process
2262 -- between Jan and Jul in date mode
2263 --
2264 -- ------------------------------------------------------------------------------------ */
2265 FUNCTION calc_asg_jul2jun_jp_date(
2266 p_assignment_id IN NUMBER,
2267 p_balance_type_id IN NUMBER,
2268 p_effective_date IN DATE)
2269 RETURN NUMBER
2270 IS
2271 --
2272 l_assignment_action_id NUMBER;
2273 l_balance NUMBER;
2274 l_last_start_date DATE;
2275 l_defined_balance_id NUMBER;
2276 --
2277 cursor last_start_date(c_assignment_action_id IN NUMBER)
2278 is
2279 SELECT /*+ ORDERED */
2280 add_months(trunc(add_months(ppa.effective_date,6),'YYYY'),-6)
2281 FROM pay_assignment_actions paa,
2282 pay_payroll_actions ppa
2283 WHERE paa.assignment_action_id = c_assignment_action_id
2284 AND paa.payroll_action_id = ppa.payroll_action_id;
2285 --
2286 BEGIN
2287 --
2288 l_defined_balance_id := dimension_relevant(p_balance_type_id, hr_jprts.g_asg_jul2jun_jp);
2289 /* -- check relevant dimension. if it is not so(defined_balance_id is null), return null */
2290 --
2291 if l_defined_balance_id is not null then
2292 --
2293 l_assignment_action_id := get_latest_action_id(
2294 p_assignment_id,
2295 p_effective_date);
2296 IF l_assignment_action_id is null THEN
2297 l_balance := 0;
2298 ELSE
2299 OPEN last_start_date(l_assignment_action_id);
2300 FETCH last_start_date INTO l_last_start_date;
2301 CLOSE last_start_date;
2302 --
2303 if add_months(l_last_start_date,12) <= p_effective_date then
2304 l_balance := 0;
2305 else
2306 l_balance := calc_asg_jul2jun_jp(
2307 p_assignment_action_id => l_assignment_action_id,
2308 p_balance_type_id => p_balance_type_id,
2309 p_assignment_id => p_assignment_id);
2310 end if;
2311 END IF;
2312 else
2313 l_balance := null;
2314 end if;
2315 --
2316 RETURN l_balance;
2317 END calc_asg_jul2jun_jp_date;
2318 --
2319 /* ------------------------------------------------------------------------------------
2320 ---
2321 --
2322 -- CALC_ASG_JUL2JUN_JP --
2323 --
2324 -- Calculate balances for JP specific Assignment process between Jan and Jul
2325 --
2326 -- ------------------------------------------------------------------------------------ */
2327 FUNCTION calc_asg_jul2jun_jp(
2328 p_assignment_action_id IN NUMBER,
2329 p_balance_type_id IN NUMBER,
2330 p_assignment_id IN NUMBER)
2331 RETURN NUMBER
2332 IS
2333 --
2334 l_expired_balance NUMBER;
2335 l_assignment_action_id NUMBER;
2336 l_balance NUMBER;
2337 l_latest_value_exists VARCHAR2(2);
2338 l_action_eff_date DATE;
2339 l_end_date DATE;
2340 l_defined_bal_id NUMBER;
2341 --
2342 BEGIN
2343 --
2344 l_defined_bal_id := dimension_relevant(p_balance_type_id, hr_jprts.g_asg_jul2jun_jp);
2345 --
2346 if l_defined_bal_id is not null then
2347 l_balance := pay_balance_pkg.get_value(
2348 l_defined_bal_id,
2349 p_assignment_action_id);
2350 --
2351 else
2352 l_balance := null;
2353 end if;
2354 --
2355 RETURN l_balance;
2356 END calc_asg_jul2jun_jp;
2357 --
2358 /* ------------------------------------------------------------------------------------
2359 ---
2360 --
2361 -- CALC_ASG_ITD_ACTION --
2362 --
2363 -- This is the function for calculating assignment
2364 -- Inception to date in assignment action mode
2365 --
2366 -- ------------------------------------------------------------------------------------ */
2367 FUNCTION calc_asg_itd_action(
2368 p_assignment_action_id IN NUMBER,
2369 p_balance_type_id IN NUMBER)
2370 RETURN NUMBER
2371 IS
2372 --
2373 l_assignment_action_id NUMBER;
2374 l_balance NUMBER;
2375 l_assignment_id NUMBER;
2376 l_effective_date DATE;
2377 --
2378 BEGIN
2379 --
2380 l_assignment_id := get_correct_type(p_assignment_action_id);
2381 IF l_assignment_id is null THEN
2382 --
2383 /* -- The assignment action is not a payroll or quickpay type, so return null */
2384 --
2385 l_balance := null;
2386 ELSE
2387 --
2388 l_balance := calc_asg_itd(
2389 p_assignment_action_id => p_assignment_action_id,
2390 p_balance_type_id => p_balance_type_id,
2391 p_assignment_id => l_assignment_id);
2392 END IF;
2393 --
2394 RETURN l_balance;
2395 end calc_asg_itd_action;
2396 --
2397 /* ------------------------------------------------------------------------------------
2398 ---
2399 --
2400 -- CALC_ASG_ITD_DATE --
2401 --
2402 -- This is the function for calculating assignment inception to
2403 -- date in DATE MODE
2404 --
2405 -- ------------------------------------------------------------------------------------ */
2406 FUNCTION calc_asg_itd_date(
2407 p_assignment_id IN NUMBER,
2408 p_balance_type_id IN NUMBER,
2409 p_effective_date IN DATE)
2410 RETURN NUMBER
2411 IS
2412 --
2413 l_assignment_action_id NUMBER;
2414 l_balance NUMBER;
2415 l_end_date DATE;
2416 l_defined_balance_id NUMBER;
2417 --
2418 BEGIN
2419 --
2420 l_defined_balance_id := dimension_relevant(p_balance_type_id, hr_jprts.g_asg_itd);
2421 /* -- check relevant dimension. if it is not so(defined_balance_id is null), return null */
2422 --
2423 if l_defined_balance_id is not null then
2424 --
2425 l_assignment_action_id := get_latest_action_id( p_assignment_id,
2426 p_effective_date);
2427 IF l_assignment_action_id is null THEN
2428 l_balance := 0;
2429 ELSE
2430 l_balance := calc_asg_itd(
2431 p_assignment_action_id => l_assignment_action_id,
2432 p_balance_type_id => p_balance_type_id,
2433 p_assignment_id => p_assignment_id);
2434 END IF;
2435 else
2436 l_balance := null;
2437 end if;
2438 --
2439 RETURN l_balance;
2440 end calc_asg_itd_date;
2441 --
2442 /* ------------------------------------------------------------------------------------
2443 ---
2444 --
2445 -- CALC_ASG_ITD --
2446 --
2447 -- calculate balances for Assignment Inception to Date
2448 --
2449 -- ------------------------------------------------------------------------------------ */
2450 /* -- Sum of all run items since inception. */
2451 --
2452 FUNCTION calc_asg_itd(
2453 p_assignment_action_id IN NUMBER,
2454 p_balance_type_id IN NUMBER,
2455 p_assignment_id IN NUMBER)
2456 -- p_effective_date IN DATE DEFAULT NULL) -- in for consistency
2457 RETURN NUMBER
2458 IS
2459 --
2460 l_balance NUMBER;
2461 l_latest_value_exists VARCHAR2(2);
2462 l_assignment_action_id NUMBER;
2463 l_action_eff_date DATE;
2464 l_defined_bal_id NUMBER;
2465 --
2466 BEGIN
2467 --
2468 l_defined_bal_id := dimension_relevant(p_balance_type_id, hr_jprts.g_asg_itd);
2469 --
2470 if l_defined_bal_id is not null then
2471 l_balance := pay_balance_pkg.get_value(
2472 l_defined_bal_id,
2473 p_assignment_action_id);
2474 --
2475 else
2476 l_balance := null;
2477 end if;
2478 --
2479 RETURN l_balance;
2480 --
2481 END calc_asg_itd;
2482 --
2483 /* ------------------------------------------------------------------------------------
2484 --
2485 -- CALC_RETRO_ACTION --
2486 --
2487 -- Actually, this function is not used so that hr_routes.retro_jp does not exist.
2488 -- This is the function for calculating retro pay process
2489 -- in assignment action mode.
2490 --
2491 -- ------------------------------------------------------------------------------------ */
2492 FUNCTION calc_retro_action(
2493 p_assignment_action_id IN NUMBER,
2494 p_balance_type_id IN NUMBER)
2495 RETURN NUMBER
2496 IS
2497 --
2498 l_assignment_action_id NUMBER;
2499 l_balance NUMBER;
2500 l_assignment_id NUMBER;
2501 l_effective_date DATE;
2502 --
2503 BEGIN
2504 --
2505 l_assignment_id := get_correct_type(p_assignment_action_id);
2506 IF l_assignment_id is null THEN
2507 --
2508 /* -- The assignment action is not a payroll or quickpay type, so return null */
2509 --
2510 l_balance := null;
2511 ELSE
2512 --
2513 l_balance := calc_retro(
2514 p_assignment_action_id => p_assignment_action_id,
2515 p_balance_type_id => p_balance_type_id,
2516 p_assignment_id => l_assignment_id);
2517 END IF;
2518 --
2519 RETURN l_balance;
2520 END calc_retro_action;
2521 --
2522 /* ------------------------------------------------------------------------------------
2523 --
2524 -- CALC_RETRO_DATE --
2525 --
2526 -- Actually, this function is not used so that hr_routes.retro_jp does not exist.
2527 -- This is the function for calculating retro pay process
2528 -- in date mode.
2529 --
2530 -- ------------------------------------------------------------------------------------ */
2531 FUNCTION calc_retro_date(
2532 p_assignment_id IN NUMBER,
2533 p_balance_type_id IN NUMBER,
2534 p_effective_date IN DATE)
2535 RETURN NUMBER
2536 IS
2537 --
2538 l_assignment_action_id NUMBER;
2539 l_balance NUMBER;
2540 l_end_date DATE;
2541 l_defined_balance_id NUMBER;
2542 --
2543 cursor expired_time_period(c_assignment_action_id IN NUMBER)
2544 is
2545 SELECT /*+ ORDERED */
2546 ptp.end_date
2547 FROM pay_assignment_actions paa,
2548 pay_payroll_actions ppa,
2549 per_time_periods ptp
2550 WHERE paa.assignment_action_id = c_assignment_action_id
2551 AND ppa.payroll_action_id = paa.payroll_action_id
2552 AND ptp.time_period_id = ppa.time_period_id;
2553 --
2554 BEGIN
2555 --
2556 l_defined_balance_id := dimension_relevant(p_balance_type_id, hr_jprts.g_retro);
2557 /* -- check relevant dimension. if it is not so(defined_balance_id is null), return null */
2558 --
2559 if l_defined_balance_id is not null then
2560 --
2561 l_assignment_action_id := get_latest_action_id(
2562 p_assignment_id,
2563 p_effective_date);
2564 IF l_assignment_action_id is null THEN
2565 l_balance := 0;
2566 ELSE
2567 OPEN expired_time_period(l_assignment_action_id);
2568 FETCH expired_time_period INTO l_end_date;
2569 CLOSE expired_time_period;
2570 --
2571 if l_end_date < p_effective_date then
2572 l_balance := 0;
2573 else
2574 l_balance := calc_retro(
2575 p_assignment_action_id => l_assignment_action_id,
2576 p_balance_type_id => p_balance_type_id,
2577 p_assignment_id => p_assignment_id);
2578 end if;
2579 END IF;
2580 else
2581 l_balance := null;
2582 end if;
2583 --
2584 RETURN l_balance;
2585 END calc_retro_date;
2586 --
2587 /* ------------------------------------------------------------------------------------
2588 --
2589 -- CALC_RETRO --
2590 --
2591 -- Actually, this function is not used so that hr_routes.retro_jp does not exist.
2592 -- calculate balances for retro pay process
2593 --
2594 -- ------------------------------------------------------------------------------------ */
2595 FUNCTION calc_retro(
2596 p_assignment_action_id IN NUMBER,
2597 p_balance_type_id IN NUMBER,
2598 p_assignment_id IN NUMBER)
2599 RETURN NUMBER
2600 IS
2601 --
2602 l_balance NUMBER;
2603 l_defined_bal_id NUMBER;
2604 --
2605 BEGIN
2606 --
2607 /* --Do we need to work out nocopy a value for this dimension/balance combination.
2608 --Used dimension_name in dimension_relevant because of unique column */
2609 --
2610 l_defined_bal_id := dimension_relevant(p_balance_type_id, hr_jprts.g_retro);
2611 if l_defined_bal_id is not null then
2612 --
2613 /* -- Run balances will never have a value in pay_assignment_latest_balances
2614 -- table, as they are only used for the duration of the payroll run.
2615 -- We therefore don't need to check the table, time can be saved by
2616 -- simply calling the route code, which is incidentally the most
2617 -- performant (ie simple) route. */
2618 --
2619 /* -- Actually, this function is not used so that hr_routes.retro_jp does not exist. */
2620 l_balance := hr_jprts.retro_jp(
2621 p_assignment_action_id,
2622 p_balance_type_id);
2623 --
2624 else
2625 l_balance := null;
2626 end if;
2627 --
2628 RETURN l_balance;
2629 END calc_retro;
2630 --
2631 /* ------------------------------------------------------------------------------------
2632 --
2633 -- CALC_PAYMENT_ACTION --
2634 --
2635 -- This is the function for calculating payment process
2636 -- in assignment action mode
2637 --
2638 -- ------------------------------------------------------------------------------------ */
2639 FUNCTION calc_payment_action(
2640 p_assignment_action_id IN NUMBER,
2641 p_balance_type_id IN NUMBER)
2642 RETURN NUMBER
2643 IS
2644 --
2645 l_assignment_action_id NUMBER;
2646 l_balance NUMBER;
2647 l_assignment_id NUMBER;
2648 l_effective_date DATE;
2649 --
2650 BEGIN
2651 --
2652 l_assignment_id := get_correct_type(p_assignment_action_id);
2653 IF l_assignment_id is null THEN
2654 --
2655 /* -- The assignment action is not a payroll or quickpay type, so return null */
2656 --
2657 l_balance := null;
2658 ELSE
2659 --
2660 l_balance := calc_payment(
2661 p_assignment_action_id => p_assignment_action_id,
2662 p_balance_type_id => p_balance_type_id,
2663 p_assignment_id => l_assignment_id);
2664 END IF;
2665 --
2666 RETURN l_balance;
2667 END calc_payment_action;
2668 --
2669 /* ------------------------------------------------------------------------------------
2670 --
2671 -- CALC_PAYMENT_DATE --
2672 --
2673 -- This is the function for calculating payment process
2674 -- in date mode
2675 --
2676 -- ------------------------------------------------------------------------------------ */
2677 FUNCTION calc_payment_date(
2678 p_assignment_id IN NUMBER,
2679 p_balance_type_id IN NUMBER,
2680 p_effective_date IN DATE)
2681 RETURN NUMBER
2682 IS
2683 --
2684 l_assignment_action_id NUMBER;
2685 l_balance NUMBER;
2686 l_end_date DATE;
2687 l_defined_balance_id NUMBER;
2688 --
2689 cursor expired_time_period(c_assignment_action_id IN NUMBER)
2690 is
2691 SELECT /*+ ORDERED */
2692 ptp.end_date
2693 FROM pay_assignment_actions paa,
2694 pay_payroll_actions ppa,
2695 per_time_periods ptp
2696 WHERE paa.assignment_action_id = c_assignment_action_id
2697 AND ppa.payroll_action_id = paa.payroll_action_id
2698 AND ptp.time_period_id = ppa.time_period_id;
2699 --
2700 BEGIN
2701 --
2702 l_defined_balance_id := dimension_relevant(p_balance_type_id, hr_jprts.g_payment);
2703 /* -- check relevant dimension. if it is not so(defined_balance_id is null), return null */
2704 --
2705 if l_defined_balance_id is not null then
2706 --
2707 l_assignment_action_id := get_latest_action_id(
2708 p_assignment_id,
2709 p_effective_date);
2710 IF l_assignment_action_id is null THEN
2711 l_balance := 0;
2712 ELSE
2713 OPEN expired_time_period(l_assignment_action_id);
2714 FETCH expired_time_period INTO l_end_date;
2715 CLOSE expired_time_period;
2716 --
2717 if l_end_date < p_effective_date then
2718 l_balance := 0;
2719 else
2720 l_balance := calc_payment(
2721 p_assignment_action_id => l_assignment_action_id,
2722 p_balance_type_id => p_balance_type_id,
2723 p_assignment_id => p_assignment_id);
2724 end if;
2725 END IF;
2726 else
2727 l_balance := null;
2728 end if;
2729 --
2730 RETURN l_balance;
2731 END calc_payment_date;
2732 --
2733 /* ------------------------------------------------------------------------------------
2734 --
2735 -- CALC_PAYMENT --
2736 --
2737 -- Calculate balances for payment process
2738 --
2739 -- ------------------------------------------------------------------------------------ */
2740 FUNCTION calc_payment(
2741 p_assignment_action_id IN NUMBER,
2742 p_balance_type_id IN NUMBER,
2743 p_assignment_id IN NUMBER)
2744 RETURN NUMBER
2745 IS
2746 --
2747 l_expired_balance NUMBER;
2748 l_balance NUMBER;
2749 l_latest_value_exists VARCHAR2(2);
2750 l_assignment_action_id NUMBER;
2751 l_action_eff_date DATE;
2752 l_end_date DATE;
2753 l_defined_bal_id NUMBER;
2754 --
2755 BEGIN
2756 --
2757 l_defined_bal_id := dimension_relevant(p_balance_type_id, hr_jprts.g_payment);
2758 --
2759 if l_defined_bal_id is not null then
2760 l_balance := pay_balance_pkg.get_value(
2761 l_defined_bal_id,
2762 p_assignment_action_id);
2763 --
2764 else
2765 l_balance := null;
2766 end if;
2767 --
2768 RETURN l_balance;
2769 END calc_payment;
2770 --
2771 /* ------------------------------------------------------------------------------------
2772 --
2773 -- CALC_ELEMENT_ITD_BAL
2774 --
2775 -- This is the function for calculating element itd balance
2776 --
2777 -- ------------------------------------------------------------------------------------ */
2778 FUNCTION calc_element_itd_bal(p_assignment_action_id IN NUMBER,
2779 p_balance_type_id IN NUMBER,
2780 p_source_id IN NUMBER)
2781 RETURN NUMBER IS
2782 --
2783 l_balance NUMBER;
2784 l_defined_bal_id NUMBER;
2785 --
2786 BEGIN
2787 --
2788 l_defined_bal_id := dimension_relevant(p_balance_type_id, hr_jprts.g_element_itd);
2789 if l_defined_bal_id is not null then
2790 l_balance := get_latest_element_bal(
2791 p_assignment_action_id,
2792 l_defined_bal_id,
2793 p_source_id);
2794 if l_balance is null then
2795 --
2796 -- Fix bug#3083339: Set context to identify element name in the element level
2797 -- dimension rows.
2798 --
2799 -- This set source_id will be used in pay_balance_pkg.get_value function.
2800 pay_balance_pkg.set_context(
2801 p_context_name => 'ORIGINAL_ENTRY_ID',
2802 p_context_value => p_source_id);
2803 --
2804 l_balance := pay_balance_pkg.get_value(
2805 l_defined_bal_id,
2806 p_assignment_action_id);
2807 end if;
2808 else
2809 l_balance := null;
2810 --
2811 end if;
2812 --
2813 RETURN l_balance;
2814 END calc_element_itd_bal;
2815 --
2816 /* ------------------------------------------------------------------------------------
2817 --
2818 -- CALC_ELEMENT_PTD_BAL
2819 --
2820 -- This is the function for calculating element ptd balance
2821 --
2822 -- ------------------------------------------------------------------------------------ */
2823 FUNCTION calc_element_ptd_bal(p_assignment_action_id IN NUMBER,
2824 p_balance_type_id IN NUMBER,
2825 p_source_id IN NUMBER)
2826 RETURN NUMBER IS
2827 --
2828 l_balance NUMBER;
2829 l_defined_bal_id NUMBER;
2830 --
2831 BEGIN
2832 --
2833 l_defined_bal_id := dimension_relevant(p_balance_type_id, hr_jprts.g_element_ptd);
2834 if l_defined_bal_id is not null then
2835 --
2836 l_balance := get_latest_element_bal(
2837 p_assignment_action_id,
2838 l_defined_bal_id,
2839 p_source_id);
2840 if l_balance is null then
2841 --
2842 -- This set source_id will be used in pay_balance_pkg.get_value function.
2843 pay_balance_pkg.set_context(
2844 p_context_name => 'ORIGINAL_ENTRY_ID',
2845 p_context_value => p_source_id);
2846 l_balance := pay_balance_pkg.get_value(
2847 l_defined_bal_id,
2848 p_assignment_action_id);
2849 --l_balance := hr_routes.element_ptd(
2850 -- p_assignment_action_id,
2851 -- p_balance_type_id,
2852 -- p_source_id);
2853 end if;
2854 else
2855 l_balance := null;
2856 --
2857 end if;
2858 --
2859 RETURN l_balance;
2860 END calc_element_ptd_bal;
2861 --
2862 /* ------------------------------------------------------------------------------------
2863 --
2864 -- CALC_ALL_BALANCES
2865 -- -- assignment action Mode -
2866 --
2867 -- This is the generic overloaded function for calculating all balances
2868 -- in assignment action mode. NB Element level balances cannot be called
2869 -- from here as they require further context.
2870 --
2871 -- ------------------------------------------------------------------------------------ */
2872 FUNCTION calc_all_balances(
2873 p_assignment_action_id IN NUMBER,
2874 p_defined_balance_id IN NUMBER)
2875 --
2876 RETURN NUMBER
2877 IS
2878 --
2879 l_balance NUMBER;
2880 l_balance_type_id NUMBER;
2881 l_dimension_name VARCHAR2(80);
2882 --
2883 cursor get_balance_type_id(c_defined_balance_id IN NUMBER)
2884 IS
2885 SELECT /*+ ORDERED */
2886 pdb.balance_type_id,
2887 pbd.dimension_name
2888 FROM pay_defined_balances pdb,
2889 pay_balance_dimensions pbd
2890 WHERE pdb.defined_balance_id = c_defined_balance_id
2891 AND pbd.balance_dimension_id = pdb.balance_dimension_id;
2892 --
2893 BEGIN
2894 --
2895 OPEN get_balance_type_id(p_defined_balance_id);
2896 FETCH get_balance_type_id INTO l_balance_type_id, l_dimension_name;
2897 CLOSE get_balance_type_id;
2898 --
2899 If l_dimension_name = hr_jprts.g_asg_run then
2900 l_balance := calc_asg_run_action(
2901 p_assignment_action_id,
2902 l_balance_type_id);
2903 ELSIF l_dimension_name = hr_jprts.g_asg_mtd_jp then
2904 l_balance := calc_asg_mtd_jp_action(
2905 p_assignment_action_id,
2906 l_balance_type_id);
2907 -- l_dimension_name);
2908 ELSIF l_dimension_name = hr_jprts.g_ast_ytd_jp then
2909 l_balance := calc_asg_ytd_jp_action(
2910 p_assignment_action_id,
2911 l_balance_type_id);
2912 -- l_dimension_name);
2913 --
2914 elsif l_dimension_name = pyjpexc.c_asg_aprtd then
2915 --
2916 l_balance := calc_asg_apr2mar_jp_action(
2917 p_assignment_action_id,
2918 l_balance_type_id);
2919 --
2920 ELSIF l_dimension_name = hr_jprts.g_asg_aug2jul_jp then
2921 l_balance := calc_asg_aug2jul_jp_action(
2922 p_assignment_action_id,
2923 l_balance_type_id);
2924 -- l_dimension_name);
2925 ELSIF l_dimension_name = hr_jprts.g_asg_jul2jun_jp then
2926 -- calc_asg_jul2jun_jp_action is necessary
2927 -- this should be used in pay_jp_bal_matrix_by_act_v
2928 l_balance := calc_asg_jul2jun_jp_action(
2929 p_assignment_action_id,
2930 l_balance_type_id);
2931 ELSIF l_dimension_name = hr_jprts.g_asg_proc_ptd then
2932 l_balance := calc_asg_proc_ptd_action(
2933 p_assignment_action_id,
2934 l_balance_type_id);
2935 ELSIF l_dimension_name = hr_jprts.g_asg_itd then
2936 l_balance := calc_asg_itd_action(p_assignment_action_id,
2937 l_balance_type_id);
2938 ELSIF l_dimension_name = hr_jprts.g_asg_fytd_jp then
2939 l_balance := calc_asg_fytd_jp_action(
2940 p_assignment_action_id,
2941 l_balance_type_id);
2942 -- l_dimension_name);
2943 ELSIF l_dimension_name = hr_jprts.g_asg_fytd2_jp then
2944 -- calc_asg_fytd2_jp_action might be necessary
2945 l_balance := calc_asg_fytd2_jp_action(
2946 p_assignment_action_id,
2947 l_balance_type_id);
2948 /* -- Actually, this function is not used so that hr_routes.retro_jp does not exist. */
2949 ELSIF l_dimension_name = hr_jprts.g_retro then
2950 l_balance := calc_retro_action(p_assignment_action_id,
2951 l_balance_type_id);
2952 ELSIF l_dimension_name = hr_jprts.g_payment then
2953 l_balance := calc_payment_action(
2954 p_assignment_action_id,
2955 l_balance_type_id);
2956 /* -- This function can not call the calculation of Element dimension.
2957 -- Because it needs source_id.
2958 -- ELSIF l_dimension_name = hr_jprts.g_element_itd then
2959 -- fnd_message.set_name('PAY','This dimension is invalid');
2960 -- fnd_message.raise_error;
2961 --l_balance := NULL;
2962 -- ELSIF l_dimension_name = hr_jprts.g_element_ptd then
2963 -- fnd_message.set_name('PAY','This dimension is invalid');
2964 -- fnd_message.raise_error;
2965 -- l_balance := NULL; */
2966 --ELSE the balance must be for a USER-REG level dimension
2967 ELSE
2968 l_balance := balance(p_assignment_action_id, p_defined_balance_id);
2969 End If;
2970 --
2971 RETURN l_balance;
2972 END calc_all_balances;
2973 --
2974 /* ------------------------------------------------------------------------------------
2975 --
2976 -- CALC_ALL_BALANCES
2977 -- - Date Mode -
2978 --
2979 -- This is the overloaded generic function for calculating all balances
2980 -- in Date Mode. NB Element level balances cannot be obtained from here as
2981 -- they require further context.
2982 --
2983 -- ------------------------------------------------------------------------------------ */
2984 FUNCTION calc_all_balances(
2985 p_effective_date IN DATE,
2986 p_assignment_id IN NUMBER,
2987 p_defined_balance_id IN NUMBER)
2988 --
2989 RETURN NUMBER
2990 IS
2991 --
2992 l_balance NUMBER;
2993 l_balance_type_id NUMBER;
2994 l_dimension_name VARCHAR2(80);
2995 l_assignment_action_id NUMBER;
2996 --
2997 cursor get_balance_type_id(c_defined_balance_id IN NUMBER)
2998 IS
2999 SELECT /*+ ORDERED */
3000 pdb.balance_type_id,
3001 pbd.dimension_name
3002 FROM pay_defined_balances pdb,
3003 pay_balance_dimensions pbd
3004 WHERE pdb.defined_balance_id = c_defined_balance_id
3005 AND pbd.balance_dimension_id = pdb.balance_dimension_id;
3006 --
3007 BEGIN
3008 --
3009 OPEN get_balance_type_id(p_defined_balance_id);
3010 FETCH get_balance_type_id INTO l_balance_type_id, l_dimension_name;
3011 CLOSE get_balance_type_id;
3012 --
3013 If l_dimension_name = hr_jprts.g_asg_run then
3014 l_balance := calc_asg_run_date(
3015 p_assignment_id,
3016 l_balance_type_id,
3017 p_effective_date);
3018 ELSIF l_dimension_name = hr_jprts.g_asg_mtd_jp then
3019 l_balance := calc_asg_mtd_jp_date(
3020 p_assignment_id,
3021 l_balance_type_id,
3022 p_effective_date);
3023 ELSIF l_dimension_name = hr_jprts.g_ast_ytd_jp then
3024 l_balance := calc_asg_ytd_jp_date(
3025 p_assignment_id,
3026 l_balance_type_id,
3027 p_effective_date);
3028 --
3029 elsif l_dimension_name = pyjpexc.c_asg_aprtd then
3030 --
3031 l_balance := calc_asg_apr2mar_jp_date(
3032 p_assignment_id,
3033 l_balance_type_id,
3034 p_effective_date);
3035 --
3036 ELSIF l_dimension_name = hr_jprts.g_asg_aug2jul_jp then
3037 l_balance := calc_asg_aug2jul_jp_date(
3038 p_assignment_id,
3039 l_balance_type_id,
3040 p_effective_date);
3041 ELSIF l_dimension_name = hr_jprts.g_asg_jul2jun_jp then
3042 -- calc_asg_jul2jun_jp_date is necessary
3043 -- this should be used in pay_jp_bal_matrix_by_date_v
3044 l_balance := calc_asg_jul2jun_jp_date(
3045 p_assignment_id,
3046 l_balance_type_id,
3047 p_effective_date);
3048 ELSIF l_dimension_name = hr_jprts.g_asg_proc_ptd then
3049 l_balance := calc_asg_proc_ptd_date(
3050 p_assignment_id,
3051 l_balance_type_id,
3052 p_effective_date);
3053 ELSIF l_dimension_name = hr_jprts.g_asg_itd then
3054 l_balance := calc_asg_itd_date(
3055 p_assignment_id,
3056 l_balance_type_id,
3057 p_effective_date);
3058 ELSIF l_dimension_name = hr_jprts.g_asg_fytd_jp then
3059 l_balance := calc_asg_fytd_jp_date(
3060 p_assignment_id,
3061 l_balance_type_id,
3062 p_effective_date);
3063 ELSIF l_dimension_name = hr_jprts.g_asg_fytd2_jp then
3064 -- calc_asg_fytd2_jp_action might be necessary
3065 l_balance := calc_asg_fytd2_jp_date(
3066 p_assignment_id,
3067 l_balance_type_id,
3068 p_effective_date);
3069 /* -- Actually, this function is not used so that hr_routes.retro_jp does not exist. */
3070 ELSIF l_dimension_name = hr_jprts.g_retro then
3071 l_balance := calc_retro_date(
3072 p_assignment_id,
3073 l_balance_type_id,
3074 p_effective_date);
3075 ELSIF l_dimension_name = hr_jprts.g_payment then
3076 l_balance := calc_payment_date(
3077 p_assignment_id,
3078 l_balance_type_id,
3079 p_effective_date);
3080 /* -- This function can not call the calculation of Element dimension.
3081 -- Because it needs source_id.
3082 -- ELSIF l_dimension_name = hr_jprts.g_element_itd then
3083 -- fnd_message.set_name('PAY','This dimension is invalid');
3084 -- fnd_message.raise_error;
3085 -- l_balance := NULL;
3086 -- ELSIF l_dimension_name = hr_jprts.g_element_ptd then
3087 -- fnd_message.set_name('PAY','This dimension is invalid');
3088 -- fnd_message.raise_error;
3089 -- l_balance := NULL; */
3090 ----
3091 -- This comment is no more effective because new function has been added
3092 -- for hr_jprts.g_asg_jul2jun_jp and hr_jprts.g_asg_fytd2_jp
3093 --
3094 ELSE
3095 --This will trap USER-REG level balances
3096 l_balance := calc_balance_date(
3097 p_assignment_id,
3098 l_balance_type_id,
3099 p_effective_date,
3100 l_dimension_name);
3101 END IF;
3102 --
3103 RETURN l_balance;
3104 --
3105 END calc_all_balances;
3106 --
3107 END hr_jpbal;