[Home] [Help]
PACKAGE BODY: APPS.HR_JPBAL
Source
1 PACKAGE BODY hr_jpbal AS
2 /* $Header: pyjpbal.pkb 120.2.12000000.2 2007/03/05 08:52:41 keyazawa noship $ */
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 --
414 -- ------------------------------------------------------------------------------------ */
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 --
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,
556 --
553 l_from_date,
554 l_to_date,
555 l_action_sequence);
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 --
690 where installed_flag in ('B', 'I');
687 cursor csr_language_code is
688 select language_code
689 from fnd_languages
691 BEGIN
692 errbuf := NULL;
693 retcode := 0;
694 --
695 -- Check DD-MM
696 --
697 l_start_dd_mm := to_char(to_date(p_start_dd_mm || '-' || '2000', 'DD-MM-YYYY'), 'DD-MM');
698 --
699 -- Fill the dimension name
700 -- Bug.2597843 Removed trailing space characters
701 --
702 l_database_item_suffix := upper('_ASG_' || '_' || p_suffix);
703 l_dimension_name := rpad(l_database_item_suffix, 30)
704 || rpad(p_dim_date_type, 15)
705 || l_start_dd_mm
706 || ' RESET'
707 || TO_CHAR(p_frequency, '00')
708 || ' USER-REG';
709 -- ---------------------------
710 -- INSERT INTO FF_ROUTES --
711 -- ---------------------------
712 select ff_routes_s.nextval
713 into l_route_id
714 from dual;
715 --
716 select pay_balance_dimensions_s.nextval
717 into l_balance_dimension_id
718 from dual;
719 --
720 l_route_text :=
721 ' pay_jp_balances_v TARGET,
722 pay_dummy_feeds_v FEED
723 WHERE TARGET.assignment_action_id = &B1
724 AND TARGET.balance_type_id = &U1
725 AND TARGET.balance_dimension_id = ' || to_char(l_balance_dimension_id);
726 --
727 insert into ff_routes(
728 route_id,
729 route_name,
730 user_defined_flag,
731 description,
732 text)
733 values( l_route_id,
734 'ROUTE_NAME_' || to_char(l_route_id),
735 'N',
736 'Route for User Defined Assignment Balance Dimension ' || l_dimension_name,
737 l_route_text);
738 -- -----------------------------------------
739 -- INSERT INTO FF_ROUTE_CONTEXT_USAGES --
740 -- -----------------------------------------
741 insert into ff_route_context_usages(
742 route_id,
743 context_id,
744 sequence_no)
745 select l_route_id,
746 context_id,
747 1
748 FROM ff_contexts
749 WHERE context_name = 'ASSIGNMENT_ACTION_ID';
750 -- ------------------------------------
751 -- INSERT INTO FF_ROUTE_PARAMETER --
752 -- ------------------------------------
753 insert into ff_route_parameters(
754 route_parameter_id,
755 route_id,
756 sequence_no,
757 parameter_name,
758 data_type)
759 values( ff_route_parameters_s.nextval,
760 l_route_id,
761 1,
762 'Balance Type Id',
763 'N');
764 -- -----------------------------
765 -- CREATION DIMENSION NAME --
766 -- -----------------------------
767 -- Bug.2597843
768 -- DATE_EARNED based dimension is not supported fully,
769 -- so latest balance should not be created.
770 -- Dimension type is set to 'N'(Not fed, Not stored).
771 -- This solution of dimension_type = 'N' for DATE_EARNED based dimension
772 -- is rejected from the perspective of performance.
773 -- Yes, there's limitation which causes inconsistency between latest balance
774 -- and sum of result values by route, but now, we are going to leave
775 -- this DATE_EARNED dimension bugs.
776 --
777 -- If we are going to support dimension_type = 'F'(Fed, Not Stored) for
778 -- DATE_EARNED dimension, feed_checking_type needs to be "F"
779 -- which means feed_checking_code is called for every run result
780 -- to check feed information as of DATE_EARNED, not EFFECTIVE_DATE
781 -- which is default behavior. This will cause serere performance issue.
782 --
783 l_dimension_type := 'A';
784 l_expiry_checking_level := 'P';
785 l_expiry_checking_code := 'hr_jpbal.check_expiry';
786 l_description := hr_jp_standard_pkg.get_message('PAY', 'PAY_JP_USER_DEF_ASG_DIM_DESC', 'US', 'REQUEST_ID', to_char(l_request_id));
787 --
788 insert into pay_balance_dimensions(
789 balance_dimension_id,
790 business_group_id,
791 legislation_code,
792 route_id,
793 database_item_suffix,
794 dimension_name,
795 dimension_type,
796 description,
797 legislation_subgroup,
798 payments_flag,
799 expiry_checking_level,
800 expiry_checking_code,
801 feed_checking_type,
802 feed_checking_code,
803 -- for Run Balances
804 SAVE_RUN_BALANCE_ENABLED,
805 DIMENSION_LEVEL,
806 PERIOD_TYPE,
807 START_DATE_CODE,
808 -- for Group Level dimension (run balance compliant)
809 ASG_ACTION_BALANCE_DIM_ID,
810 -- for hrdyndbi DBI Generator (run balance compliant)
811 DATABASE_ITEM_FUNCTION)
812 values( l_balance_dimension_id,
813 p_business_group_id,
814 null,
815 l_route_id,
816 l_database_item_suffix,
817 l_dimension_name,
818 l_dimension_type,
819 l_description,
820 null,
821 'N',
822 l_expiry_checking_level,
823 l_expiry_checking_code,
824 null,
825 null,
826 null,
827 null,
828 null,
829 null,
830 null,
831 null);
832 --
833 for l_rec in csr_language_code loop
834 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);
835 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));
836 --
837 begin
838 select rowid
839 into l_rowid
843 for update nowait;
840 from pay_balance_dimensions_tl
841 where balance_dimension_id = l_balance_dimension_id
842 and language = l_rec.language_code
844 --
845 update pay_balance_dimensions_tl
846 set dimension_name = l_database_item_suffix,
847 database_item_suffix = l_database_item_suffix,
848 description = l_description
849 where rowid = l_rowid;
850 exception
851 when no_data_found then
852 insert into pay_balance_dimensions_tl(
853 BALANCE_DIMENSION_ID,
854 LANGUAGE,
855 SOURCE_LANG,
856 DIMENSION_NAME,
857 DATABASE_ITEM_SUFFIX,
858 DESCRIPTION)
859 values( l_balance_dimension_id,
860 l_rec.language_code,
861 l_rec.language_code,
862 l_database_item_suffix,
863 l_database_item_suffix,
864 l_description);
865 end;
866 end loop;
867 END create_dimension;
868 --
869 /* ------------------------------------------------------------------------------------
870 --
871 -- CALC_BALANCE_DATE --
872 --
873 -- This is the function for calculating assignment processing
874 -- of any dimension in date mode
875 --
876 -- ------------------------------------------------------------------------------------ */
877 --
878 -- This function only support USER-REG dimension.
879 --
880 FUNCTION calc_balance_date(
881 p_assignment_id IN NUMBER,
882 p_balance_type_id IN NUMBER,
883 p_effective_date IN DATE,
884 p_dimension_name IN VARCHAR2)
885 RETURN NUMBER
886 IS
887 --
888 l_assignment_action_id NUMBER;
889 l_assact_id_for_effect NUMBER;
890 l_assact_id_for_earned NUMBER;
891 l_balance NUMBER;
892 l_dimension_jp_type VARCHAR2(15);
893 l_last_effective_date DATE;
894 l_last_date_earned DATE;
895 l_frequency NUMBER;
896 l_start_dd_mm VARCHAR2(6);
897 l_next_start_date DATE;
898 l_defined_balance_id NUMBER;
899 --
900 /* -- c_effective_date <= session_date */
901 cursor get_latest_id_for_earned (
902 c_assignment_id IN NUMBER,
903 c_effective_date IN DATE)
904 is
905 SELECT TO_NUMBER(substr(max(lpad(ASSACT.action_sequence,15,'0')||
906 ASSACT.assignment_action_id),16))
907 FROM pay_payroll_actions PACT,
908 pay_assignment_actions ASSACT
909 WHERE ASSACT.assignment_id = c_assignment_id
910 AND PACT.payroll_action_id = ASSACT.payroll_action_id
911 AND PACT.date_earned <= c_effective_date
912 AND PACT.action_type in ('R', 'Q', 'I', 'V', 'B');
913 --
914 cursor last_date (c_assignment_action_id IN NUMBER)
915 is
916 SELECT ppa.effective_date effect_date,
917 ppa.date_earned earned_date
918 FROM pay_payroll_actions ppa,
919 pay_assignment_actions paa
920 WHERE paa.assignment_action_id = c_assignment_action_id
921 AND paa.payroll_action_id = ppa.payroll_action_id;
922 --
923 l_last_date last_date%ROWTYPE;
924 --
925 BEGIN
926 --
927 l_defined_balance_id := dimension_relevant(p_balance_type_id, p_dimension_name);
928 /* -- check relevant dimension. if it is not so(defined_balance_id is null), return null */
929 --
930 if l_defined_balance_id is not null then
931 --
932 l_assact_id_for_effect := get_latest_action_id( p_assignment_id,
933 p_effective_date);
934 --
935 /* -- p_effective_date <= session_date */
936 OPEN get_latest_id_for_earned( p_assignment_id,
937 p_effective_date);
938 FETCH get_latest_id_for_earned INTO l_assact_id_for_earned;
939 CLOSE get_latest_id_for_earned;
940 --
941 -- Fix bug#3051183: Corrected to support UTF8.
942 --
943 l_dimension_jp_type := RTRIM(SUBSTRB(RPAD(p_dimension_name, 44), -14));
944 --
945 IF l_dimension_jp_type = 'EFFECTIVE_DATE' THEN
946 --
947 IF l_assact_id_for_effect is null THEN
948 l_balance := 0;
949 ELSE
950 --
951 OPEN last_date(l_assact_id_for_effect);
952 FETCH last_date INTO l_last_date;
953 l_last_effective_date := l_last_date.effect_date;
954 CLOSE last_date;
955 --
956 l_assignment_action_id := l_assact_id_for_effect;
957 l_next_start_date := hr_jprts.dimension_reset_last_date(p_dimension_name,l_last_effective_date) + 1;
958 --
959 /* -- p_effective_date <= session_date */
960 if l_next_start_date <= p_effective_date then
961 l_balance := 0;
962 else
963 l_balance := balance( l_assignment_action_id,
964 l_defined_balance_id);
965 end if;
966 END IF;
967 --
968 ELSIF l_dimension_jp_type = 'DATE_EARNED' then
969 --
970 IF l_assact_id_for_earned is null THEN
971 l_balance := 0;
972 ELSE
973 --
974 OPEN last_date(l_assact_id_for_earned);
975 FETCH last_date INTO l_last_date;
976 l_last_date_earned := l_last_date.earned_date;
977 CLOSE last_date;
978 --
979 l_assignment_action_id := l_assact_id_for_earned;
980 l_next_start_date := hr_jprts.dimension_reset_last_date(p_dimension_name,l_last_date_earned) + 1;
981 --
982 /* -- p_effective_date <= session_date */
986 l_balance := balance( l_assignment_action_id,
983 if l_next_start_date <= p_effective_date then
984 l_balance := 0;
985 else
987 l_defined_balance_id);
988 end if;
989 END IF;
990 END IF;
991 else l_balance := null;
992 end if;
993 --
994 RETURN l_balance;
995 END calc_balance_date;
996 --
997 /* ------------------------------------------------------------------------------------
998 --
999 -- CALC_ASG_RUN_ACTION --
1000 --
1001 -- This is the function for calculating assignment runs in
1002 -- assignment action mode
1003 --
1004 -- ------------------------------------------------------------------------------------ */
1005 FUNCTION calc_asg_run_action(
1006 p_assignment_action_id IN NUMBER,
1007 p_balance_type_id IN NUMBER)
1008 RETURN NUMBER
1009 IS
1010 --
1011 l_assignment_action_id NUMBER;
1012 l_balance NUMBER;
1013 l_assignment_id NUMBER;
1014 --
1015 BEGIN
1016 --
1017 l_assignment_id := get_correct_type(p_assignment_action_id);
1018 IF l_assignment_id is null THEN
1019 --
1020 /* -- The assignment action is not a payroll or quickpay type, so return null */
1021 --
1022 l_balance := null;
1023 ELSE
1024 --
1025 l_balance := calc_asg_run(
1026 p_assignment_action_id => p_assignment_action_id,
1027 p_balance_type_id => p_balance_type_id,
1028 p_assignment_id => l_assignment_id);
1029 END IF;
1030 --
1031 RETURN l_balance;
1032 END calc_asg_run_action;
1033 --
1034 /* ------------------------------------------------------------------------------------
1035 --
1036 -- CALC_ASG_RUN_DATE --
1037 --
1038 -- This is the function for calculating assignment run in
1039 -- DATE MODE
1040 --
1041 -- ------------------------------------------------------------------------------------ */
1042 FUNCTION calc_asg_run_date(
1043 p_assignment_id IN NUMBER,
1044 p_balance_type_id IN NUMBER,
1045 p_effective_date IN DATE)
1046 RETURN NUMBER
1047 IS
1048 --
1049 l_assignment_action_id NUMBER;
1050 l_balance NUMBER;
1051 l_end_date DATE;
1052 l_defined_balance_id NUMBER;
1053 --
1054 cursor expired_time_period(c_assignment_action_id IN NUMBER)
1055 is
1056 SELECT /*+ ORDERED */
1057 ptp.end_date
1058 FROM pay_assignment_actions paa,
1059 pay_payroll_actions ppa,
1060 per_time_periods ptp
1061 WHERE paa.assignment_action_id = c_assignment_action_id
1062 AND ppa.payroll_action_id = paa.payroll_action_id
1063 AND ptp.time_period_id = ppa.time_period_id;
1064 --
1065 BEGIN
1066 --
1067 l_defined_balance_id := dimension_relevant(p_balance_type_id, hr_jprts.g_asg_run);
1068 /* -- check relevant dimension. if it is not so(defined_balance_id is null), return null */
1069 if l_defined_balance_id is not null then
1070 --
1071 l_assignment_action_id := get_latest_action_id(
1072 p_assignment_id,
1073 p_effective_date);
1074 IF l_assignment_action_id is null THEN
1075 l_balance := 0;
1076 ELSE
1077 OPEN expired_time_period(l_assignment_action_id);
1078 FETCH expired_time_period INTO l_end_date;
1079 CLOSE expired_time_period;
1080 --
1081 if l_end_date < p_effective_date then
1082 l_balance := 0;
1083 else
1084 l_balance := calc_asg_run(
1085 p_assignment_action_id => l_assignment_action_id,
1086 p_balance_type_id => p_balance_type_id,
1087 p_assignment_id => p_assignment_id);
1088 end if;
1089 END IF;
1090 else l_balance := null;
1091 end if;
1092 --
1093 RETURN l_balance;
1094 END calc_asg_run_date;
1095 --
1096 /* ------------------------------------------------------------------------------------
1097 --
1098 -- CALC_ASG_RUN --
1099 --
1100 -- calculate balances for Assignment Run
1101 --
1102 -- ------------------------------------------------------------------------------------ */
1103 /* -- Run
1104 -- the simplest dimension retrieves run values where the context
1105 -- is this assignment action and this balance feed. Balance is the
1106 -- specified input value. The related payroll action determines the
1107 -- date effectivity of the feeds */
1108 FUNCTION calc_asg_run(
1109 p_assignment_action_id IN NUMBER,
1110 p_balance_type_id IN NUMBER,
1111 p_assignment_id IN NUMBER)
1112 RETURN NUMBER
1113 IS
1114 --
1115 --
1116 l_balance NUMBER;
1117 l_defined_bal_id NUMBER;
1118 --
1119 BEGIN
1120 --
1121 /* --Do we need to work out nocopy a value for this dimension/balance combination.
1122 --Used dimension_name in dimension_relevant because of unique column */
1123 --
1124 l_defined_bal_id := dimension_relevant(p_balance_type_id, hr_jprts.g_asg_run);
1125 if l_defined_bal_id is not null then
1126 --
1127 /* -- Run balances will never have a value in pay_assignment_latest_balances
1131 -- performant (ie simple) route. */
1128 -- table, as they are only used for the duration of the payroll run.
1129 -- We therefore don't need to check the table, time can be saved by
1130 -- simply calling the route code, which is incidentally the most
1132 --
1133 l_balance := pay_balance_pkg.get_value(
1134 l_defined_bal_id,
1135 p_assignment_action_id);
1136 --
1137 else l_balance := null;
1138 end if;
1139 --
1140 RETURN l_balance;
1141 --
1142 END calc_asg_run;
1143 --
1144 /* ------------------------------------------------------------------------------------
1145 --
1146 -- CALC_ASG_PROC_PTD_ACTION
1147 --
1148 -- This is the function for calculating assignment processing
1149 -- period to date in assignment action mode
1150 --
1151 -- ------------------------------------------------------------------------------------ */
1152 FUNCTION calc_asg_proc_ptd_action(
1153 p_assignment_action_id IN NUMBER,
1154 p_balance_type_id IN NUMBER)
1155 RETURN NUMBER
1156 IS
1157 --
1158 l_assignment_action_id NUMBER;
1159 l_balance NUMBER;
1160 l_assignment_id NUMBER;
1161 --
1162 BEGIN
1163 --
1164 l_assignment_id := get_correct_type(p_assignment_action_id);
1165 IF l_assignment_id is null THEN
1166 --
1167 /* -- The assignment action is not a payroll or quickpay type, so return null */
1168 --
1169 l_balance := null;
1170 ELSE
1171 --
1172 l_balance := calc_asg_proc_ptd(
1173 p_assignment_action_id => p_assignment_action_id,
1174 p_balance_type_id => p_balance_type_id,
1175 p_assignment_id => l_assignment_id);
1176 END IF;
1177 --
1178 RETURN l_balance;
1179 END calc_asg_proc_ptd_action;
1180 --
1181 /* ------------------------------------------------------------------------------------
1182 --
1183 -- CALC_ASG_PROC_PTD_DATE
1184 --
1185 -- This is the function for calculating assignment processing
1186 -- period to date in date mode
1187 --
1188 -- ------------------------------------------------------------------------------------ */
1189 FUNCTION calc_asg_proc_ptd_date(
1190 p_assignment_id IN NUMBER,
1191 p_balance_type_id IN NUMBER,
1192 p_effective_date IN DATE)
1193 RETURN NUMBER
1194 IS
1195 --
1196 l_assignment_action_id NUMBER;
1197 l_balance NUMBER;
1198 l_end_date DATE;
1199 l_defined_balance_id NUMBER;
1200 --
1201 /* -- Has the processing time period expired */
1202 --
1203 cursor expired_time_period(c_assignment_action_id IN NUMBER)
1204 is
1205 SELECT /*+ ORDERED */
1206 ptp.end_date
1207 FROM pay_assignment_actions paa,
1208 pay_payroll_actions ppa,
1209 per_time_periods ptp
1210 WHERE paa.assignment_action_id = c_assignment_action_id
1211 AND ppa.payroll_action_id = paa.payroll_action_id
1212 AND ptp.time_period_id = ppa.time_period_id;
1213 --
1214 BEGIN
1215 --
1216 l_defined_balance_id := dimension_relevant(p_balance_type_id, hr_jprts.g_asg_proc_ptd);
1217 /* -- check relevant dimension. if it is not so(defined_balance_id is null), return null */
1218 --
1219 if l_defined_balance_id is not null then
1220 --
1221 l_assignment_action_id := get_latest_action_id( p_assignment_id,
1222 p_effective_date);
1223 IF l_assignment_action_id is null THEN
1224 l_balance := 0;
1225 ELSE
1226 OPEN expired_time_period(l_assignment_action_id);
1227 FETCH expired_time_period INTO l_end_date;
1228 CLOSE expired_time_period;
1229 --
1230 if l_end_date < p_effective_date then
1231 l_balance := 0;
1232 else
1233 l_balance := calc_asg_proc_ptd(
1234 p_assignment_action_id => l_assignment_action_id,
1235 p_balance_type_id => p_balance_type_id,
1236 p_assignment_id => p_assignment_id);
1237 end if;
1238 END IF;
1239 else l_balance := null;
1240 end if;
1241 --
1242 RETURN l_balance;
1243 END calc_asg_proc_ptd_date;
1244 --
1245 /* ------------------------------------------------------------------------------------
1246 --
1247 -- CALC_ASG_PROC_PTD --
1248 --
1249 -- calculate balances for Assignment process period to date
1250 --
1251 -- ------------------------------------------------------------------------------------ */
1252 /* -- This dimension is the total for an assignment within the processing
1253 -- period of his current payroll, OR if the assignment has transferred
1254 -- payroll within the current processing period, it is the total since
1255 -- he joined the current payroll.
1256 --
1257 -- This dimension should be used for the period dimension of balances
1258 -- which are reset to zero on transferring payroll. */
1259 --
1260 FUNCTION calc_asg_proc_ptd(
1261 p_assignment_action_id IN NUMBER,
1262 p_balance_type_id IN NUMBER,
1263 p_assignment_id IN NUMBER)
1264 --
1265 RETURN NUMBER
1266 IS
1267 --
1268 l_expired_balance NUMBER;
1272 l_action_eff_date DATE;
1269 l_assignment_action_id NUMBER;
1270 l_balance NUMBER;
1271 l_latest_value_exists VARCHAR2(2);
1273 l_end_date DATE;
1274 l_defined_bal_id NUMBER;
1275 --
1276 BEGIN
1277 --
1278 l_defined_bal_id := dimension_relevant(p_balance_type_id, hr_jprts.g_asg_proc_ptd);
1279 --
1280 if l_defined_bal_id is not null then
1281 l_balance := pay_balance_pkg.get_value(
1282 l_defined_bal_id,
1283 p_assignment_action_id);
1284 --
1285 else
1286 l_balance := null;
1287 end if;
1288 --
1289 RETURN l_balance;
1290 END calc_asg_proc_ptd;
1291 --
1292 /* ------------------------------------------------------------------------------------
1293 --
1294 -- CALC_ASG_MTD_JP_ACTION --
1295 --
1296 -- This is the function for calculating JP specific assignment processing
1297 -- month to date in assignment action mode
1298 --
1299 -- ------------------------------------------------------------------------------------ */
1300 FUNCTION calc_asg_mtd_jp_action(
1301 p_assignment_action_id IN NUMBER,
1302 p_balance_type_id IN NUMBER)
1303 -- p_dimension_name IN VARCHAR2)
1304 RETURN NUMBER
1305 IS
1306 --
1307 l_assignment_action_id NUMBER;
1308 l_balance NUMBER;
1309 l_assignment_id NUMBER;
1310 --
1311 BEGIN
1312 --
1313 l_assignment_id := get_correct_type(p_assignment_action_id);
1314 IF l_assignment_id is null THEN
1315 --
1316 /* -- The assignment action is not a payroll or quickpay type, so return null */
1317 --
1318 l_balance := null;
1319 ELSE
1320 --
1321 l_balance := calc_asg_mtd_jp(
1322 p_assignment_action_id => p_assignment_action_id,
1323 p_balance_type_id => p_balance_type_id,
1324 p_assignment_id => l_assignment_id);
1325 -- p_dimension_name => p_dimension_name);
1326 END IF;
1327 --
1328 RETURN l_balance;
1329 END calc_asg_mtd_jp_action;
1330 --
1331 /* ------------------------------------------------------------------------------------
1332 --
1333 -- CALC_ASG_MTD_JP_DATE --
1334 --
1335 -- This is the function for calculating JP specific assignment processing
1336 -- month to date in date mode
1337 --
1338 -- ------------------------------------------------------------------------------------ */
1339 --
1340 FUNCTION calc_asg_mtd_jp_date(
1341 p_assignment_id IN NUMBER,
1342 p_balance_type_id IN NUMBER,
1343 p_effective_date IN DATE)
1344 -- p_dimension_name IN VARCHAR2)
1345 RETURN NUMBER
1346 IS
1347 --
1348 l_assignment_action_id NUMBER;
1349 l_balance NUMBER;
1350 l_last_start_date DATE;
1351 l_defined_balance_id NUMBER;
1352 --
1353 cursor last_start_date(c_assignment_action_id IN NUMBER)
1354 is
1355 SELECT /*+ ORDERED */
1356 trunc(ppa.effective_date,'MM')
1357 FROM pay_assignment_actions paa,
1358 pay_payroll_actions ppa
1359 WHERE paa.assignment_action_id = c_assignment_action_id
1360 AND ppa.payroll_action_id = paa.payroll_action_id;
1361 --
1362 BEGIN
1363 --
1364 l_defined_balance_id := dimension_relevant( p_balance_type_id,
1365 hr_jprts.g_asg_mtd_jp);
1366 /* -- check relevant dimension. if it is not so(defined_balance_id is null), return null */
1367 --
1368 if l_defined_balance_id is not null then
1369 --
1370 l_assignment_action_id := get_latest_action_id(
1371 p_assignment_id,
1372 p_effective_date);
1373 IF l_assignment_action_id is null THEN
1374 l_balance := 0;
1375 ELSE
1376 OPEN last_start_date(l_assignment_action_id);
1377 FETCH last_start_date INTO l_last_start_date;
1378 CLOSE last_start_date;
1379 --
1380 if add_months(l_last_start_date,1) <= p_effective_date then
1381 l_balance := 0;
1382 else
1383 l_balance := calc_asg_mtd_jp(
1384 p_assignment_action_id => l_assignment_action_id,
1385 p_balance_type_id => p_balance_type_id,
1386 p_assignment_id => p_assignment_id);
1387 -- P_dimension_name => p_dimension_name);
1388 end if;
1389 END IF;
1390 else
1391 l_balance := null;
1392 end if;
1393 --
1394 RETURN l_balance;
1395 END calc_asg_mtd_jp_date;
1396 --
1397 /* ------------------------------------------------------------------------------------
1398 --
1399 -- CALC_ASG_MTD_JP --
1400 --
1401 -- Calculate balances for JP specific Assignment process month to date
1402 --
1403 -- ------------------------------------------------------------------------------------ */
1404 FUNCTION calc_asg_mtd_jp(
1405 p_assignment_action_id IN NUMBER,
1406 p_balance_type_id IN NUMBER,
1407 p_assignment_id IN NUMBER)
1408 -- p_dimension_name IN VARCHAR2)
1409 RETURN NUMBER
1410 IS
1411 --
1412 --
1413 l_expired_balance NUMBER;
1414 l_assignment_action_id NUMBER;
1415 l_balance NUMBER;
1419 l_defined_bal_id NUMBER;
1416 l_latest_value_exists VARCHAR2(2);
1417 l_action_eff_date DATE;
1418 l_end_date DATE;
1420 --
1421 BEGIN
1422 --
1423 l_defined_bal_id := dimension_relevant(p_balance_type_id,hr_jprts.g_asg_mtd_jp);
1424 --
1425 if l_defined_bal_id is not null then
1426 l_balance := pay_balance_pkg.get_value(
1427 l_defined_bal_id,
1428 p_assignment_action_id);
1429 --
1430 else
1431 l_balance := null;
1432 end if;
1433 --
1434 RETURN l_balance;
1435 END calc_asg_mtd_jp;
1436 --
1437 /* ------------------------------------------------------------------------------------
1438 --
1439 -- CALC_ASG_YTD_JP_ACTION --
1440 --
1441 -- This is the function for calculating JP specific assignment processing
1442 -- year to date in assignment action mode
1443 --
1444 -- ------------------------------------------------------------------------------------ */
1445 FUNCTION calc_asg_ytd_jp_action(
1446 p_assignment_action_id IN NUMBER,
1447 p_balance_type_id IN NUMBER)
1448 -- p_dimension_name IN VARCHAR2)
1449 RETURN NUMBER
1450 IS
1451 --
1452 l_assignment_action_id NUMBER;
1453 l_balance NUMBER;
1454 l_assignment_id NUMBER;
1455 --
1456 BEGIN
1457 --
1458 l_assignment_id := get_correct_type(p_assignment_action_id);
1459 IF l_assignment_id is null THEN
1460 --
1461 /* -- The assignment action is not a payroll or quickpay type, so return null */
1462 --
1463 l_balance := null;
1464 ELSE
1465 --
1466 l_balance := calc_asg_ytd_jp(
1467 p_assignment_action_id,
1468 p_balance_type_id,
1469 l_assignment_id);
1470 -- p_dimension_name);
1471 END IF;
1472 --
1473 RETURN l_balance;
1474 END calc_asg_ytd_jp_action;
1475 --
1476 /* ------------------------------------------------------------------------------------
1477 --
1478 -- CALC_ASG_YTD_JP_DATE --
1479 --
1480 -- This is the function for calculating JP specific assignment processing
1481 -- year to date in date mode
1482 --
1483 -- ------------------------------------------------------------------------------------ */
1484 FUNCTION calc_asg_ytd_jp_date(
1485 p_assignment_id IN NUMBER,
1486 p_balance_type_id IN NUMBER,
1487 p_effective_date IN DATE)
1488 -- p_dimension_name IN VARCHAR2)
1489 RETURN NUMBER
1490 IS
1491 --
1492 l_assignment_action_id NUMBER;
1493 l_balance NUMBER;
1494 l_last_start_date DATE;
1495 l_defined_balance_id NUMBER;
1496 --
1497 cursor last_start_date(c_assignment_action_id IN NUMBER)
1498 is
1499 SELECT /*+ ORDERED */
1500 trunc(ppa.effective_date,'YYYY')
1501 FROM pay_assignment_actions paa,
1502 pay_payroll_actions ppa
1503 WHERE paa.assignment_action_id = c_assignment_action_id
1504 AND ppa.payroll_action_id = paa.payroll_action_id;
1505 --
1506 BEGIN
1507 --
1508 l_defined_balance_id := dimension_relevant(p_balance_type_id, hr_jprts.g_ast_ytd_jp);
1509 /* -- check relevant dimension. if it is not so(defined_balance_id is null), return null */
1510 --
1511 if l_defined_balance_id is not null then
1512 --
1513 l_assignment_action_id := get_latest_action_id(p_assignment_id,
1514 p_effective_date);
1515 IF l_assignment_action_id is null THEN
1516 l_balance := 0;
1517 ELSE
1518 OPEN last_start_date(l_assignment_action_id);
1519 FETCH last_start_date INTO l_last_start_date;
1520 CLOSE last_start_date;
1521 --
1522 if add_months(l_last_start_date,12) <= p_effective_date then
1523 l_balance := 0;
1524 else
1525 l_balance := calc_asg_ytd_jp(
1526 l_assignment_action_id,
1527 p_balance_type_id,
1528 p_assignment_id);
1529 -- p_dimension_name);
1530 end if;
1531 END IF;
1532 else
1533 l_balance := null;
1534 end if;
1535 --
1536 RETURN l_balance;
1537 END calc_asg_ytd_jp_date;
1538 --
1539 /* ------------------------------------------------------------------------------------
1540 --
1541 -- CALC_ASG_YTD_JP --
1542 --
1543 -- Calculate balances for JP specific Assignment process year to date
1544 --
1545 -- ------------------------------------------------------------------------------------ */
1546 FUNCTION calc_asg_ytd_jp(
1547 p_assignment_action_id IN NUMBER,
1548 p_balance_type_id IN NUMBER,
1549 p_assignment_id IN NUMBER)
1550 -- p_dimension_name IN VARCHAR2)
1551 RETURN NUMBER
1552 IS
1553 --
1554 l_expired_balance NUMBER;
1555 l_assignment_action_id NUMBER;
1556 l_balance NUMBER;
1557 l_latest_value_exists VARCHAR2(2);
1558 l_action_eff_date DATE;
1559 l_end_date DATE;
1560 l_defined_bal_id NUMBER;
1561 --
1562 BEGIN
1563 --
1564 l_defined_bal_id := dimension_relevant(p_balance_type_id, hr_jprts.g_ast_ytd_jp);
1565 --
1569 p_assignment_action_id);
1566 if l_defined_bal_id is not null then
1567 l_balance := pay_balance_pkg.get_value(
1568 l_defined_bal_id,
1570 --
1571 else
1572 l_balance := null;
1573 end if;
1574 --
1575 RETURN l_balance;
1576 END calc_asg_ytd_jp;
1577 --
1578 /* ------------------------------------------------------------------------------------
1579 --
1580 -- CALC_ASG_FYTD_JP_ACTION --
1581 --
1582 -- This is the function for calculating JP specific assignment processing
1583 -- Financial year to date in assignment action mode
1584 --
1585 -- ------------------------------------------------------------------------------------ */
1586 FUNCTION calc_asg_fytd_jp_action(
1587 p_assignment_action_id IN NUMBER,
1588 p_balance_type_id IN NUMBER)
1589 -- p_dimension_name IN VARCHAR2)
1590 RETURN NUMBER
1591 IS
1592 --
1593 l_assignment_action_id NUMBER;
1594 l_balance NUMBER;
1595 l_assignment_id NUMBER;
1596 --
1597 BEGIN
1598 --
1599 l_assignment_id := get_correct_type(p_assignment_action_id);
1600 IF l_assignment_id is null THEN
1601 --
1602 /* -- The assignment action is not a payroll or quickpay type, so return null */
1603 --
1604 l_balance := null;
1605 ELSE
1606 --
1607 l_balance := calc_asg_fytd_jp(
1608 p_assignment_action_id => p_assignment_action_id,
1609 p_balance_type_id => p_balance_type_id,
1610 p_assignment_id => l_assignment_id);
1611 -- p_dimension_name => p_dimension_name);
1612 END IF;
1613 --
1614 RETURN l_balance;
1615 END calc_asg_fytd_jp_action;
1616 --
1617 /* ------------------------------------------------------------------------------------
1618 --
1619 -- CALC_ASG_FYTD_JP_DATE --
1620 --
1621 -- This is the function for calculating JP specific assignment processing
1622 -- Financial year to date in date mode
1623 --
1624 -- ------------------------------------------------------------------------------------ */
1625 FUNCTION calc_asg_fytd_jp_date(
1626 p_assignment_id IN NUMBER,
1627 p_balance_type_id IN NUMBER,
1628 p_effective_date IN DATE)
1629 -- p_dimension_name IN VARCHAR2)
1630 RETURN NUMBER
1631 IS
1632 --
1633 l_assignment_action_id NUMBER;
1634 l_balance NUMBER;
1635 l_last_start_date DATE;
1636 l_date_earned DATE;
1637 l_defined_balance_id NUMBER;
1638 --
1639 cursor last_start_date(c_assignment_action_id IN NUMBER)
1640 is
1641 SELECT /*+ ORDERED */
1642 add_months(nvl(FND_DATE.CANONICAL_TO_DATE(HROG.org_information11),trunc(PACT.date_earned,'YYYY')),
1643 floor(months_between(PACT.date_earned,nvl(FND_DATE.CANONICAL_TO_DATE(HROG.org_information11),
1644 trunc(PACT.date_earned,'YYYY')))/12)*12) last_start_date
1645 FROM pay_assignment_actions ASSACT,
1646 pay_payroll_actions PACT,
1647 hr_organization_information HROG
1648 WHERE ASSACT.assignment_action_id = c_assignment_action_id
1649 AND PACT.payroll_action_id = ASSACT.payroll_action_id
1650 AND HROG.organization_id = PACT.business_group_id
1651 AND HROG.org_information_context = 'Business Group Information';
1652 --
1653 cursor get_latest_id (c_assignment_id IN NUMBER,
1654 c_effective_date IN DATE)
1655 is
1656 SELECT /*+ ORDERED */
1657 TO_NUMBER(substr(max(lpad(ASSACT.action_sequence,15,'0')||
1658 ASSACT.assignment_action_id),16))
1659 FROM pay_assignment_actions ASSACT,
1660 pay_payroll_actions PACT
1661 WHERE ASSACT.assignment_id = c_assignment_id
1662 AND PACT.payroll_action_id = ASSACT.payroll_action_id
1663 AND PACT.date_earned <= c_effective_date
1664 AND PACT.action_type in ('R', 'Q', 'I', 'V', 'B');
1665 --
1666 BEGIN
1667 --
1668 l_defined_balance_id := dimension_relevant(p_balance_type_id, hr_jprts.g_asg_fytd_jp);
1669 /* -- check relevant dimension. if it is not so(defined_balance_id is null), return null */
1670 --
1671 if l_defined_balance_id is not null then
1672 --
1673 /* -- p_effective_date <= session_date */
1674 OPEN get_latest_id( p_assignment_id,
1675 p_effective_date);
1676 FETCH get_latest_id INTO l_assignment_action_id;
1677 CLOSE get_latest_id;
1678 --
1679 IF l_assignment_action_id is null THEN
1680 l_balance := 0;
1681 ELSE
1682 OPEN last_start_date(l_assignment_action_id);
1683 FETCH last_start_date INTO l_last_start_date;
1684 CLOSE last_start_date;
1685 --
1686 /* -- p_effective_date <= session_date */
1687 if add_months(l_last_start_date,12) <= p_effective_date then
1688 l_balance := 0;
1689 else
1690 l_balance := calc_asg_fytd_jp(
1691 p_assignment_action_id => l_assignment_action_id,
1692 p_balance_type_id => p_balance_type_id,
1693 p_assignment_id => p_assignment_id);
1694 -- p_dimension_name => p_dimension_name);
1695 end if;
1696 END IF;
1697 else
1698 l_balance := null;
1699 end if;
1700 --
1701 RETURN l_balance;
1702 END calc_asg_fytd_jp_date;
1703 --
1704 /* ------------------------------------------------------------------------------------
1705 --
1706 -- CALC_ASG_FYTD_JP --
1707 --
1708 -- Calculate balances for JP specific Assignment process financial year to date
1709 --
1710 -- ------------------------------------------------------------------------------------ */
1711 FUNCTION calc_asg_fytd_jp(
1712 p_assignment_action_id IN NUMBER,
1713 p_balance_type_id IN NUMBER,
1714 p_assignment_id IN NUMBER)
1715 -- p_dimension_name IN VARCHAR2)
1716 RETURN NUMBER
1717 IS
1718 --
1719 l_balance NUMBER;
1720 l_defined_bal_id NUMBER;
1721 --
1722 BEGIN
1723 --
1724 /* --Do we need to work out nocopy a value for this dimension/balance combination.
1725 --Used dimension_name in dimension_relevant because of unique column */
1726 --
1727 l_defined_bal_id := dimension_relevant(p_balance_type_id, hr_jprts.g_asg_fytd_jp);
1728 if l_defined_bal_id is not null then
1729 --
1730 /* -- This balances will never have a value in pay_assignment_latest_balances
1731 -- table, as they are only used for the duration of the payroll run.
1732 -- We therefore don't need to check the table, time can be saved by
1733 -- simply calling the route code, which is incidentally the most
1734 -- performant (ie simple) route. */
1735 --
1736 -- Remain hr_jprts.asg_fytd_jp function since better than
1737 -- using dimension_reset_date_userdef/calc_bal_date_earned
1738 --
1739 l_balance := hr_jprts.asg_fytd_jp(
1740 p_assignment_action_id,
1741 p_balance_type_id);
1742 --
1743 else
1744 l_balance := null;
1745 end if;
1746 --
1747 RETURN l_balance;
1748 END calc_asg_fytd_jp;
1749 --
1750 --
1751 /* ------------------------------------------------------------------------------------
1752 --
1753 -- CALC_ASG_FYTD2_JP_ACTION --
1754 --
1755 -- This is the function for calculating JP specific assignment processing
1756 -- Business year to date in assignment action mode
1757 --
1758 -- ------------------------------------------------------------------------------------ */
1759 FUNCTION calc_asg_fytd2_jp_action(
1760 p_assignment_action_id IN NUMBER,
1761 p_balance_type_id IN NUMBER)
1762 RETURN NUMBER
1763 IS
1764 --
1765 l_assignment_action_id NUMBER;
1766 l_balance NUMBER;
1767 l_assignment_id NUMBER;
1768 --
1769 BEGIN
1770 --
1771 l_assignment_id := get_correct_type(p_assignment_action_id);
1772 --
1773 IF l_assignment_id is null THEN
1774 --
1775 /* -- The assignment action is not a payroll or quickpay type, so return null */
1776 --
1777 l_balance := null;
1778 ELSE
1779 --
1780 l_balance := calc_asg_fytd2_jp(
1781 p_assignment_action_id => p_assignment_action_id,
1782 p_balance_type_id => p_balance_type_id,
1783 p_assignment_id => l_assignment_id);
1784 END IF;
1785 --
1786 RETURN l_balance;
1787 END calc_asg_fytd2_jp_action;
1788 --
1789 /* ------------------------------------------------------------------------------------
1790 --
1791 -- CALC_ASG_FYTD_JP2_DATE --
1792 --
1793 -- This is the function for calculating JP specific assignment processing
1794 -- Business year to date in date mode
1795 --
1796 -- ------------------------------------------------------------------------------------ */
1797 FUNCTION calc_asg_fytd2_jp_date(
1798 p_assignment_id IN NUMBER,
1799 p_balance_type_id IN NUMBER,
1800 p_effective_date IN DATE)
1801 RETURN NUMBER
1802 IS
1803 --
1804 l_assignment_action_id NUMBER;
1805 l_balance NUMBER;
1806 l_last_start_date DATE;
1807 l_effective_date DATE;
1808 l_defined_balance_id NUMBER;
1809 --
1810 cursor last_start_date(c_assignment_action_id IN NUMBER)
1811 is
1812 SELECT /*+ ORDERED */
1813 add_months(nvl(FND_DATE.CANONICAL_TO_DATE(HROG.org_information11),trunc(PACT.effective_date,'YYYY')),
1814 floor(months_between(PACT.effective_date,nvl(FND_DATE.CANONICAL_TO_DATE(HROG.org_information11),
1815 trunc(PACT.effective_date,'YYYY')))/12)*12) last_start_date
1816 FROM pay_assignment_actions ASSACT,
1817 pay_payroll_actions PACT,
1818 hr_organization_information HROG
1819 WHERE ASSACT.assignment_action_id = c_assignment_action_id
1820 AND PACT.payroll_action_id = ASSACT.payroll_action_id
1821 AND HROG.organization_id = PACT.business_group_id
1822 AND HROG.org_information_context = 'Business Group Information';
1823 --
1824 cursor get_latest_id (
1825 c_assignment_id IN NUMBER,
1826 c_effective_date IN DATE)
1827 is
1828 SELECT /*+ ORDERED */
1829 TO_NUMBER(substr(max(lpad(ASSACT.action_sequence,15,'0')||
1830 ASSACT.assignment_action_id),16))
1831 FROM pay_assignment_actions ASSACT,
1832 pay_payroll_actions PACT
1833 WHERE ASSACT.assignment_id = c_assignment_id
1834 AND PACT.payroll_action_id = ASSACT.payroll_action_id
1835 AND PACT.effective_date <= c_effective_date
1836 AND PACT.action_type in ('R', 'Q', 'I', 'V', 'B');
1837 --
1838 BEGIN
1839 --
1840 l_defined_balance_id := dimension_relevant(p_balance_type_id, hr_jprts.g_asg_fytd2_jp);
1841 /* -- check relevant dimension. if it is not so(defined_balance_id is null), return null */
1842 --
1843 if l_defined_balance_id is not null then
1844 --
1845 /* -- p_effective_date <= session_date */
1846 OPEN get_latest_id(
1847 p_assignment_id,
1848 p_effective_date);
1849 FETCH get_latest_id INTO l_assignment_action_id;
1850 CLOSE get_latest_id;
1851 --
1852 IF l_assignment_action_id is null THEN
1853 l_balance := 0;
1854 ELSE
1855 OPEN last_start_date(l_assignment_action_id);
1856 FETCH last_start_date INTO l_last_start_date;
1857 CLOSE last_start_date;
1858 --
1859 /* -- p_effective_date <= session_date */
1860 if add_months(l_last_start_date,12) <= p_effective_date then
1861 l_balance := 0;
1862 else
1863 l_balance := calc_asg_fytd2_jp(
1864 p_assignment_action_id => l_assignment_action_id,
1865 p_balance_type_id => p_balance_type_id,
1866 p_assignment_id => p_assignment_id);
1867 end if;
1868 END IF;
1869 else
1870 l_balance := null;
1871 end if;
1872 --
1873 RETURN l_balance;
1874 END calc_asg_fytd2_jp_date;
1875 --
1876 /* ------------------------------------------------------------------------------------
1877 --
1878 -- CALC_ASG_FYTD2_JP --
1879 --
1880 -- Calculate balances for JP specific Assignment process financial year to date
1881 --
1882 -- ------------------------------------------------------------------------------------ */
1883 FUNCTION calc_asg_fytd2_jp(
1884 p_assignment_action_id IN NUMBER,
1885 p_balance_type_id IN NUMBER,
1886 p_assignment_id IN NUMBER)
1887 RETURN NUMBER
1888 IS
1889 --
1890 l_balance NUMBER;
1891 l_defined_bal_id NUMBER;
1892 --
1893 BEGIN
1894 --
1895 l_defined_bal_id := dimension_relevant(p_balance_type_id, hr_jprts.g_asg_fytd2_jp);
1896 --
1897 if l_defined_bal_id is not null then
1898 --
1899 l_balance := pay_balance_pkg.get_value(
1900 l_defined_bal_id,
1901 p_assignment_action_id);
1902 --
1903 else
1904 l_balance := null;
1905 end if;
1906 --
1907 RETURN l_balance;
1908 END calc_asg_fytd2_jp;
1909 --
1910 /* ------------------------------------------------------------------------------------
1911 --
1912 -- CALC_ASG_APR2MAR_JP_ACTION
1913 --
1914 -- This is the function for calculating JP specific assignment process
1915 -- between Apr and Mar in assignment action mode
1916 --
1917 -- ------------------------------------------------------------------------------------ */
1918 function calc_asg_apr2mar_jp_action(
1919 p_assignment_action_id in number,
1920 p_balance_type_id in number)
1921 return number
1922 is
1923 --
1924 l_assignment_action_id number;
1925 l_balance number;
1926 l_assignment_id number;
1927 --
1928 begin
1929 --
1930 l_assignment_id := get_correct_type(p_assignment_action_id);
1931 --
1932 if l_assignment_id is null then
1933 --
1934 l_balance := null;
1935 --
1936 else
1937 --
1938 l_balance := calc_asg_apr2mar_jp(
1939 p_assignment_action_id => p_assignment_action_id,
1940 p_balance_type_id => p_balance_type_id,
1941 p_assignment_id => l_assignment_id);
1942 --
1943 end if;
1944 --
1945 return l_balance;
1946 end calc_asg_apr2mar_jp_action;
1947 --
1948 /* ------------------------------------------------------------------------------------
1949 --
1950 -- CALC_ASG_APR2MAR_JP_DATE
1951 --
1952 -- This is the function for calculating JP specific assignment process
1953 -- between Apr and Mar in date mode
1954 --
1955 -- ------------------------------------------------------------------------------------ */
1956 function calc_asg_apr2mar_jp_date(
1957 p_assignment_id in number,
1958 p_balance_type_id in number,
1959 p_effective_date in date)
1960 return number
1961 is
1962 --
1963 l_assignment_action_id number;
1964 l_balance number;
1965 l_last_start_date date;
1966 l_defined_balance_id number;
1967 --
1968 cursor last_start_date(c_assignment_action_id in number)
1969 is
1970 select /*+ ORDERED */
1971 add_months(trunc(add_months(ppa.effective_date,9),'YYYY'),-9)
1972 from pay_assignment_actions paa,
1973 pay_payroll_actions ppa
1974 where paa.assignment_action_id = c_assignment_action_id
1975 and ppa.payroll_action_id = paa.payroll_action_id;
1976 --
1977 begin
1978 --
1979 l_defined_balance_id := dimension_relevant(p_balance_type_id, pyjpexc.c_asg_aprtd);
1980 --
1981 if l_defined_balance_id is not null then
1982 --
1983 l_assignment_action_id := get_latest_action_id(
1984 p_assignment_id,
1985 p_effective_date);
1986 --
1987 if l_assignment_action_id is null then
1988 l_balance := 0;
1989 else
1990 --
1991 open last_start_date(l_assignment_action_id);
1992 fetch last_start_date into l_last_start_date;
1993 close last_start_date;
1994 --
1995 if add_months(l_last_start_date,12) <= p_effective_date then
1996 l_balance := 0;
1997 else
1998 --
1999 l_balance := calc_asg_apr2mar_jp(
2000 p_assignment_action_id => l_assignment_action_id,
2001 p_balance_type_id => p_balance_type_id,
2002 p_assignment_id => p_assignment_id);
2003 --
2004 end if;
2005 --
2006 end if;
2007 --
2008 else
2009 --
2010 l_balance := null;
2011 --
2012 end if;
2013 --
2014 return l_balance;
2015 end calc_asg_apr2mar_jp_date;
2016 --
2017 /* ------------------------------------------------------------------------------------
2018 ---
2019 --
2020 -- CALC_ASG_APR2MAR_JP --
2021 --
2022 -- Calculate balances for JP specific Assignment process between Apr and Mar
2023 --
2024 -- ------------------------------------------------------------------------------------ */
2025 function calc_asg_apr2mar_jp(
2026 p_assignment_action_id in number,
2027 p_balance_type_id in number,
2028 p_assignment_id in number)
2029 return number
2030 is
2031 --
2032 l_expired_balance number;
2033 l_assignment_action_id number;
2034 l_balance number;
2035 l_latest_value_exists varchar2(2);
2036 l_action_eff_date date;
2037 l_end_date date;
2038 l_defined_bal_id number;
2039 --
2040 begin
2041 --
2042 l_defined_bal_id := dimension_relevant(p_balance_type_id, pyjpexc.c_asg_aprtd);
2043 --
2044 if l_defined_bal_id is not null then
2045 --
2046 l_balance := pay_balance_pkg.get_value(
2047 l_defined_bal_id,
2048 p_assignment_action_id);
2049 --
2050 else
2051 l_balance := null;
2052 end if;
2053 --
2054 return l_balance;
2055 end calc_asg_apr2mar_jp;
2056 --
2057 /* ------------------------------------------------------------------------------------
2058 --
2059 -- CALC_ASG_AUG2JUL_JP_ACTION --
2060 --
2061 -- This is the function for calculating JP specific assignment process
2062 -- between Jan and Aug in assignment action mode
2063 --
2064 -- ------------------------------------------------------------------------------------ */
2065 FUNCTION calc_asg_aug2jul_jp_action(
2066 p_assignment_action_id IN NUMBER,
2067 p_balance_type_id IN NUMBER)
2068 -- p_dimension_name IN VARCHAR2)
2069 RETURN NUMBER
2070 IS
2071 --
2072 l_assignment_action_id NUMBER;
2073 l_balance NUMBER;
2074 l_assignment_id NUMBER;
2075 --
2076 BEGIN
2077 --
2078 l_assignment_id := get_correct_type(p_assignment_action_id);
2079 IF l_assignment_id is null THEN
2080 --
2081 /* -- The assignment action is not a payroll or quickpay type, so return null */
2082 --
2083 l_balance := null;
2084 ELSE
2085 --
2086 l_balance := calc_asg_aug2jul_jp(
2087 p_assignment_action_id => p_assignment_action_id,
2088 p_balance_type_id => p_balance_type_id,
2089 p_assignment_id => l_assignment_id);
2090 -- p_dimension_name => p_dimension_name);
2091 END IF;
2092 --
2093 RETURN l_balance;
2094 END calc_asg_aug2jul_jp_action;
2095 --
2096 /* ------------------------------------------------------------------------------------
2097 --
2098 -- CALC_ASG_AUG2JUL_JP_DATE --
2099 --
2100 -- This is the function for calculating JP specific assignment process
2101 -- between Jan and Aug in date mode
2102 --
2103 -- ------------------------------------------------------------------------------------ */
2104 FUNCTION calc_asg_aug2jul_jp_date(
2105 p_assignment_id IN NUMBER,
2106 p_balance_type_id IN NUMBER,
2107 p_effective_date IN DATE)
2108 -- p_dimension_name IN VARCHAR2)
2109 RETURN NUMBER
2110 IS
2111 --
2112 l_assignment_action_id NUMBER;
2113 l_balance NUMBER;
2114 l_last_start_date DATE;
2115 l_defined_balance_id NUMBER;
2116 --
2117 cursor last_start_date(c_assignment_action_id IN NUMBER)
2118 is
2119 SELECT /*+ ORDERED */
2120 add_months(trunc(add_months(ppa.effective_date,5),'YYYY'),-5)
2121 FROM pay_assignment_actions paa,
2122 pay_payroll_actions ppa
2123 WHERE paa.assignment_action_id = c_assignment_action_id
2124 AND ppa.payroll_action_id = paa.payroll_action_id;
2125 --
2126 BEGIN
2127 --
2128 l_defined_balance_id := dimension_relevant(p_balance_type_id, hr_jprts.g_asg_aug2jul_jp);
2129 /* -- check relevant dimension. if it is not so(defined_balance_id is null), return null */
2130 --
2131 if l_defined_balance_id is not null then
2132 --
2133 l_assignment_action_id := get_latest_action_id(
2134 p_assignment_id,
2138 ELSE
2135 p_effective_date);
2136 IF l_assignment_action_id is null THEN
2137 l_balance := 0;
2139 OPEN last_start_date(l_assignment_action_id);
2140 FETCH last_start_date INTO l_last_start_date;
2141 CLOSE last_start_date;
2142 --
2143 if add_months(l_last_start_date,12) <= p_effective_date then
2144 l_balance := 0;
2145 else
2146 l_balance := calc_asg_aug2jul_jp(
2147 p_assignment_action_id => l_assignment_action_id,
2148 p_balance_type_id => p_balance_type_id,
2149 p_assignment_id => p_assignment_id);
2150 -- p_dimension_name => p_dimension_name);
2151 end if;
2152 END IF;
2153 else
2154 l_balance := null;
2155 end if;
2156 --
2157 RETURN l_balance;
2158 END calc_asg_aug2jul_jp_date;
2159 --
2160 /* ------------------------------------------------------------------------------------
2161 ---
2162 --
2163 -- CALC_ASG_AUG2JUL_JP --
2164 --
2165 -- Calculate balances for JP specific Assignment process between Jan and Aug
2166 --
2167 -- ------------------------------------------------------------------------------------ */
2168 FUNCTION calc_asg_aug2jul_jp(
2169 p_assignment_action_id IN NUMBER,
2170 p_balance_type_id IN NUMBER,
2171 p_assignment_id IN NUMBER)
2172 -- p_dimension_name IN VARCHAR2)
2173 RETURN NUMBER
2174 IS
2175 --
2176 l_expired_balance NUMBER;
2177 l_assignment_action_id NUMBER;
2178 l_balance NUMBER;
2179 l_latest_value_exists VARCHAR2(2);
2180 l_action_eff_date DATE;
2181 l_end_date DATE;
2182 l_defined_bal_id NUMBER;
2183 --
2184 BEGIN
2185 --
2186 l_defined_bal_id := dimension_relevant(p_balance_type_id, hr_jprts.g_asg_aug2jul_jp);
2187 --
2188 if l_defined_bal_id is not null then
2189 l_balance := pay_balance_pkg.get_value(
2190 l_defined_bal_id,
2191 p_assignment_action_id);
2192 --
2193 else
2194 l_balance := null;
2195 end if;
2196 --
2197 RETURN l_balance;
2198 END calc_asg_aug2jul_jp;
2199 --
2200 /* ------------------------------------------------------------------------------------
2201 --
2202 -- CALC_ASG_JUL2JUN_JP_ACTION --
2203 --
2204 -- This is the function for calculating JP specific assignment process
2205 -- between Jan and Jul in assignment action mode
2206 --
2207 -- ------------------------------------------------------------------------------------ */
2208 FUNCTION calc_asg_jul2jun_jp_action(
2209 p_assignment_action_id IN NUMBER,
2210 p_balance_type_id IN NUMBER)
2211 RETURN NUMBER
2212 IS
2213 --
2214 l_assignment_action_id NUMBER;
2215 l_balance NUMBER;
2216 l_assignment_id NUMBER;
2217 --
2218 BEGIN
2219 --
2220 l_assignment_id := get_correct_type(p_assignment_action_id);
2221 IF l_assignment_id is null THEN
2222 --
2223 /* -- The assignment action is not a payroll or quickpay type, so return null */
2224 --
2225 l_balance := null;
2226 ELSE
2227 --
2228 l_balance := calc_asg_jul2jun_jp(
2229 p_assignment_action_id => p_assignment_action_id,
2230 p_balance_type_id => p_balance_type_id,
2231 p_assignment_id => l_assignment_id);
2232 END IF;
2233 --
2234 RETURN l_balance;
2235 END calc_asg_jul2jun_jp_action;
2236 --
2237 /* ------------------------------------------------------------------------------------
2238 --
2239 -- CALC_ASG_JUL2JUN_JP_DATE --
2240 --
2241 -- This is the function for calculating JP specific assignment process
2242 -- between Jan and Jul in date mode
2243 --
2244 -- ------------------------------------------------------------------------------------ */
2245 FUNCTION calc_asg_jul2jun_jp_date(
2246 p_assignment_id IN NUMBER,
2247 p_balance_type_id IN NUMBER,
2248 p_effective_date IN DATE)
2249 RETURN NUMBER
2250 IS
2251 --
2252 l_assignment_action_id NUMBER;
2253 l_balance NUMBER;
2254 l_last_start_date DATE;
2255 l_defined_balance_id NUMBER;
2256 --
2257 cursor last_start_date(c_assignment_action_id IN NUMBER)
2258 is
2259 SELECT /*+ ORDERED */
2260 add_months(trunc(add_months(ppa.effective_date,6),'YYYY'),-6)
2261 FROM pay_assignment_actions paa,
2262 pay_payroll_actions ppa
2263 WHERE paa.assignment_action_id = c_assignment_action_id
2264 AND paa.payroll_action_id = ppa.payroll_action_id;
2265 --
2266 BEGIN
2267 --
2268 l_defined_balance_id := dimension_relevant(p_balance_type_id, hr_jprts.g_asg_jul2jun_jp);
2269 /* -- check relevant dimension. if it is not so(defined_balance_id is null), return null */
2270 --
2271 if l_defined_balance_id is not null then
2272 --
2273 l_assignment_action_id := get_latest_action_id(
2274 p_assignment_id,
2275 p_effective_date);
2276 IF l_assignment_action_id is null THEN
2277 l_balance := 0;
2278 ELSE
2279 OPEN last_start_date(l_assignment_action_id);
2280 FETCH last_start_date INTO l_last_start_date;
2284 l_balance := 0;
2281 CLOSE last_start_date;
2282 --
2283 if add_months(l_last_start_date,12) <= p_effective_date then
2285 else
2286 l_balance := calc_asg_jul2jun_jp(
2287 p_assignment_action_id => l_assignment_action_id,
2288 p_balance_type_id => p_balance_type_id,
2289 p_assignment_id => p_assignment_id);
2290 end if;
2291 END IF;
2292 else
2293 l_balance := null;
2294 end if;
2295 --
2296 RETURN l_balance;
2297 END calc_asg_jul2jun_jp_date;
2298 --
2299 /* ------------------------------------------------------------------------------------
2300 ---
2301 --
2302 -- CALC_ASG_JUL2JUN_JP --
2303 --
2304 -- Calculate balances for JP specific Assignment process between Jan and Jul
2305 --
2306 -- ------------------------------------------------------------------------------------ */
2307 FUNCTION calc_asg_jul2jun_jp(
2308 p_assignment_action_id IN NUMBER,
2309 p_balance_type_id IN NUMBER,
2310 p_assignment_id IN NUMBER)
2311 RETURN NUMBER
2312 IS
2313 --
2314 l_expired_balance NUMBER;
2315 l_assignment_action_id NUMBER;
2316 l_balance NUMBER;
2317 l_latest_value_exists VARCHAR2(2);
2318 l_action_eff_date DATE;
2319 l_end_date DATE;
2320 l_defined_bal_id NUMBER;
2321 --
2322 BEGIN
2323 --
2324 l_defined_bal_id := dimension_relevant(p_balance_type_id, hr_jprts.g_asg_jul2jun_jp);
2325 --
2326 if l_defined_bal_id is not null then
2327 l_balance := pay_balance_pkg.get_value(
2328 l_defined_bal_id,
2329 p_assignment_action_id);
2330 --
2331 else
2332 l_balance := null;
2333 end if;
2334 --
2335 RETURN l_balance;
2336 END calc_asg_jul2jun_jp;
2337 --
2338 /* ------------------------------------------------------------------------------------
2339 ---
2340 --
2341 -- CALC_ASG_ITD_ACTION --
2342 --
2343 -- This is the function for calculating assignment
2344 -- Inception to date in assignment action mode
2345 --
2346 -- ------------------------------------------------------------------------------------ */
2347 FUNCTION calc_asg_itd_action(
2348 p_assignment_action_id IN NUMBER,
2349 p_balance_type_id IN NUMBER)
2350 RETURN NUMBER
2351 IS
2352 --
2353 l_assignment_action_id NUMBER;
2354 l_balance NUMBER;
2355 l_assignment_id NUMBER;
2356 l_effective_date DATE;
2357 --
2358 BEGIN
2359 --
2360 l_assignment_id := get_correct_type(p_assignment_action_id);
2361 IF l_assignment_id is null THEN
2362 --
2363 /* -- The assignment action is not a payroll or quickpay type, so return null */
2364 --
2365 l_balance := null;
2366 ELSE
2367 --
2368 l_balance := calc_asg_itd(
2369 p_assignment_action_id => p_assignment_action_id,
2370 p_balance_type_id => p_balance_type_id,
2371 p_assignment_id => l_assignment_id);
2372 END IF;
2373 --
2374 RETURN l_balance;
2375 end calc_asg_itd_action;
2376 --
2377 /* ------------------------------------------------------------------------------------
2378 ---
2379 --
2380 -- CALC_ASG_ITD_DATE --
2381 --
2382 -- This is the function for calculating assignment inception to
2383 -- date in DATE MODE
2384 --
2385 -- ------------------------------------------------------------------------------------ */
2386 FUNCTION calc_asg_itd_date(
2387 p_assignment_id IN NUMBER,
2388 p_balance_type_id IN NUMBER,
2389 p_effective_date IN DATE)
2390 RETURN NUMBER
2391 IS
2392 --
2393 l_assignment_action_id NUMBER;
2394 l_balance NUMBER;
2395 l_end_date DATE;
2396 l_defined_balance_id NUMBER;
2397 --
2398 BEGIN
2399 --
2400 l_defined_balance_id := dimension_relevant(p_balance_type_id, hr_jprts.g_asg_itd);
2401 /* -- check relevant dimension. if it is not so(defined_balance_id is null), return null */
2402 --
2403 if l_defined_balance_id is not null then
2404 --
2405 l_assignment_action_id := get_latest_action_id( p_assignment_id,
2406 p_effective_date);
2407 IF l_assignment_action_id is null THEN
2408 l_balance := 0;
2409 ELSE
2410 l_balance := calc_asg_itd(
2411 p_assignment_action_id => l_assignment_action_id,
2412 p_balance_type_id => p_balance_type_id,
2413 p_assignment_id => p_assignment_id);
2414 END IF;
2415 else
2416 l_balance := null;
2417 end if;
2418 --
2419 RETURN l_balance;
2420 end calc_asg_itd_date;
2421 --
2422 /* ------------------------------------------------------------------------------------
2423 ---
2424 --
2425 -- CALC_ASG_ITD --
2426 --
2427 -- calculate balances for Assignment Inception to Date
2428 --
2429 -- ------------------------------------------------------------------------------------ */
2433 p_assignment_action_id IN NUMBER,
2430 /* -- Sum of all run items since inception. */
2431 --
2432 FUNCTION calc_asg_itd(
2434 p_balance_type_id IN NUMBER,
2435 p_assignment_id IN NUMBER)
2436 -- p_effective_date IN DATE DEFAULT NULL) -- in for consistency
2437 RETURN NUMBER
2438 IS
2439 --
2440 l_balance NUMBER;
2441 l_latest_value_exists VARCHAR2(2);
2442 l_assignment_action_id NUMBER;
2443 l_action_eff_date DATE;
2444 l_defined_bal_id NUMBER;
2445 --
2446 BEGIN
2447 --
2448 l_defined_bal_id := dimension_relevant(p_balance_type_id, hr_jprts.g_asg_itd);
2449 --
2450 if l_defined_bal_id is not null then
2451 l_balance := pay_balance_pkg.get_value(
2452 l_defined_bal_id,
2453 p_assignment_action_id);
2454 --
2455 else
2456 l_balance := null;
2457 end if;
2458 --
2459 RETURN l_balance;
2460 --
2461 END calc_asg_itd;
2462 --
2463 /* ------------------------------------------------------------------------------------
2464 --
2465 -- CALC_RETRO_ACTION --
2466 --
2467 -- Actually, this function is not used so that hr_routes.retro_jp does not exist.
2468 -- This is the function for calculating retro pay process
2469 -- in assignment action mode.
2470 --
2471 -- ------------------------------------------------------------------------------------ */
2472 FUNCTION calc_retro_action(
2473 p_assignment_action_id IN NUMBER,
2474 p_balance_type_id IN NUMBER)
2475 RETURN NUMBER
2476 IS
2477 --
2478 l_assignment_action_id NUMBER;
2479 l_balance NUMBER;
2480 l_assignment_id NUMBER;
2481 l_effective_date DATE;
2482 --
2483 BEGIN
2484 --
2485 l_assignment_id := get_correct_type(p_assignment_action_id);
2486 IF l_assignment_id is null THEN
2487 --
2488 /* -- The assignment action is not a payroll or quickpay type, so return null */
2489 --
2490 l_balance := null;
2491 ELSE
2492 --
2493 l_balance := calc_retro(
2494 p_assignment_action_id => p_assignment_action_id,
2495 p_balance_type_id => p_balance_type_id,
2496 p_assignment_id => l_assignment_id);
2497 END IF;
2498 --
2499 RETURN l_balance;
2500 END calc_retro_action;
2501 --
2502 /* ------------------------------------------------------------------------------------
2503 --
2504 -- CALC_RETRO_DATE --
2505 --
2506 -- Actually, this function is not used so that hr_routes.retro_jp does not exist.
2507 -- This is the function for calculating retro pay process
2508 -- in date mode.
2509 --
2510 -- ------------------------------------------------------------------------------------ */
2511 FUNCTION calc_retro_date(
2512 p_assignment_id IN NUMBER,
2513 p_balance_type_id IN NUMBER,
2514 p_effective_date IN DATE)
2515 RETURN NUMBER
2516 IS
2517 --
2518 l_assignment_action_id NUMBER;
2519 l_balance NUMBER;
2520 l_end_date DATE;
2521 l_defined_balance_id NUMBER;
2522 --
2523 cursor expired_time_period(c_assignment_action_id IN NUMBER)
2524 is
2525 SELECT /*+ ORDERED */
2526 ptp.end_date
2527 FROM pay_assignment_actions paa,
2528 pay_payroll_actions ppa,
2529 per_time_periods ptp
2530 WHERE paa.assignment_action_id = c_assignment_action_id
2531 AND ppa.payroll_action_id = paa.payroll_action_id
2532 AND ptp.time_period_id = ppa.time_period_id;
2533 --
2534 BEGIN
2535 --
2536 l_defined_balance_id := dimension_relevant(p_balance_type_id, hr_jprts.g_retro);
2537 /* -- check relevant dimension. if it is not so(defined_balance_id is null), return null */
2538 --
2539 if l_defined_balance_id is not null then
2540 --
2541 l_assignment_action_id := get_latest_action_id(
2542 p_assignment_id,
2543 p_effective_date);
2544 IF l_assignment_action_id is null THEN
2545 l_balance := 0;
2546 ELSE
2547 OPEN expired_time_period(l_assignment_action_id);
2548 FETCH expired_time_period INTO l_end_date;
2549 CLOSE expired_time_period;
2550 --
2551 if l_end_date < p_effective_date then
2552 l_balance := 0;
2553 else
2554 l_balance := calc_retro(
2555 p_assignment_action_id => l_assignment_action_id,
2556 p_balance_type_id => p_balance_type_id,
2557 p_assignment_id => p_assignment_id);
2558 end if;
2559 END IF;
2560 else
2561 l_balance := null;
2562 end if;
2563 --
2564 RETURN l_balance;
2565 END calc_retro_date;
2566 --
2567 /* ------------------------------------------------------------------------------------
2568 --
2569 -- CALC_RETRO --
2570 --
2571 -- Actually, this function is not used so that hr_routes.retro_jp does not exist.
2572 -- calculate balances for retro pay process
2573 --
2574 -- ------------------------------------------------------------------------------------ */
2575 FUNCTION calc_retro(
2579 RETURN NUMBER
2576 p_assignment_action_id IN NUMBER,
2577 p_balance_type_id IN NUMBER,
2578 p_assignment_id IN NUMBER)
2580 IS
2581 --
2582 l_balance NUMBER;
2583 l_defined_bal_id NUMBER;
2584 --
2585 BEGIN
2586 --
2587 /* --Do we need to work out nocopy a value for this dimension/balance combination.
2588 --Used dimension_name in dimension_relevant because of unique column */
2589 --
2590 l_defined_bal_id := dimension_relevant(p_balance_type_id, hr_jprts.g_retro);
2591 if l_defined_bal_id is not null then
2592 --
2593 /* -- Run balances will never have a value in pay_assignment_latest_balances
2594 -- table, as they are only used for the duration of the payroll run.
2595 -- We therefore don't need to check the table, time can be saved by
2596 -- simply calling the route code, which is incidentally the most
2597 -- performant (ie simple) route. */
2598 --
2599 /* -- Actually, this function is not used so that hr_routes.retro_jp does not exist. */
2600 l_balance := hr_jprts.retro_jp(
2601 p_assignment_action_id,
2602 p_balance_type_id);
2603 --
2604 else
2605 l_balance := null;
2606 end if;
2607 --
2608 RETURN l_balance;
2609 END calc_retro;
2610 --
2611 /* ------------------------------------------------------------------------------------
2612 --
2613 -- CALC_PAYMENT_ACTION --
2614 --
2615 -- This is the function for calculating payment process
2616 -- in assignment action mode
2617 --
2618 -- ------------------------------------------------------------------------------------ */
2619 FUNCTION calc_payment_action(
2620 p_assignment_action_id IN NUMBER,
2621 p_balance_type_id IN NUMBER)
2622 RETURN NUMBER
2623 IS
2624 --
2625 l_assignment_action_id NUMBER;
2626 l_balance NUMBER;
2627 l_assignment_id NUMBER;
2628 l_effective_date DATE;
2629 --
2630 BEGIN
2631 --
2632 l_assignment_id := get_correct_type(p_assignment_action_id);
2633 IF l_assignment_id is null THEN
2634 --
2635 /* -- The assignment action is not a payroll or quickpay type, so return null */
2636 --
2637 l_balance := null;
2638 ELSE
2639 --
2640 l_balance := calc_payment(
2641 p_assignment_action_id => p_assignment_action_id,
2642 p_balance_type_id => p_balance_type_id,
2643 p_assignment_id => l_assignment_id);
2644 END IF;
2645 --
2646 RETURN l_balance;
2647 END calc_payment_action;
2648 --
2649 /* ------------------------------------------------------------------------------------
2650 --
2651 -- CALC_PAYMENT_DATE --
2652 --
2653 -- This is the function for calculating payment process
2654 -- in date mode
2655 --
2656 -- ------------------------------------------------------------------------------------ */
2657 FUNCTION calc_payment_date(
2658 p_assignment_id IN NUMBER,
2659 p_balance_type_id IN NUMBER,
2660 p_effective_date IN DATE)
2661 RETURN NUMBER
2662 IS
2663 --
2664 l_assignment_action_id NUMBER;
2665 l_balance NUMBER;
2666 l_end_date DATE;
2667 l_defined_balance_id NUMBER;
2668 --
2669 cursor expired_time_period(c_assignment_action_id IN NUMBER)
2670 is
2671 SELECT /*+ ORDERED */
2672 ptp.end_date
2673 FROM pay_assignment_actions paa,
2674 pay_payroll_actions ppa,
2675 per_time_periods ptp
2676 WHERE paa.assignment_action_id = c_assignment_action_id
2677 AND ppa.payroll_action_id = paa.payroll_action_id
2678 AND ptp.time_period_id = ppa.time_period_id;
2679 --
2680 BEGIN
2681 --
2682 l_defined_balance_id := dimension_relevant(p_balance_type_id, hr_jprts.g_payment);
2683 /* -- check relevant dimension. if it is not so(defined_balance_id is null), return null */
2684 --
2685 if l_defined_balance_id is not null then
2686 --
2687 l_assignment_action_id := get_latest_action_id(
2688 p_assignment_id,
2689 p_effective_date);
2690 IF l_assignment_action_id is null THEN
2691 l_balance := 0;
2692 ELSE
2693 OPEN expired_time_period(l_assignment_action_id);
2694 FETCH expired_time_period INTO l_end_date;
2695 CLOSE expired_time_period;
2696 --
2697 if l_end_date < p_effective_date then
2698 l_balance := 0;
2699 else
2700 l_balance := calc_payment(
2701 p_assignment_action_id => l_assignment_action_id,
2702 p_balance_type_id => p_balance_type_id,
2703 p_assignment_id => p_assignment_id);
2704 end if;
2705 END IF;
2706 else
2707 l_balance := null;
2708 end if;
2709 --
2710 RETURN l_balance;
2711 END calc_payment_date;
2712 --
2713 /* ------------------------------------------------------------------------------------
2714 --
2715 -- CALC_PAYMENT --
2716 --
2717 -- Calculate balances for payment process
2718 --
2719 -- ------------------------------------------------------------------------------------ */
2720 FUNCTION calc_payment(
2724 RETURN NUMBER
2721 p_assignment_action_id IN NUMBER,
2722 p_balance_type_id IN NUMBER,
2723 p_assignment_id IN NUMBER)
2725 IS
2726 --
2727 l_expired_balance NUMBER;
2728 l_balance NUMBER;
2729 l_latest_value_exists VARCHAR2(2);
2730 l_assignment_action_id NUMBER;
2731 l_action_eff_date DATE;
2732 l_end_date DATE;
2733 l_defined_bal_id NUMBER;
2734 --
2735 BEGIN
2736 --
2737 l_defined_bal_id := dimension_relevant(p_balance_type_id, hr_jprts.g_payment);
2738 --
2739 if l_defined_bal_id is not null then
2740 l_balance := pay_balance_pkg.get_value(
2741 l_defined_bal_id,
2742 p_assignment_action_id);
2743 --
2744 else
2745 l_balance := null;
2746 end if;
2747 --
2748 RETURN l_balance;
2749 END calc_payment;
2750 --
2751 /* ------------------------------------------------------------------------------------
2752 --
2753 -- CALC_ELEMENT_ITD_BAL
2754 --
2755 -- This is the function for calculating element itd balance
2756 --
2757 -- ------------------------------------------------------------------------------------ */
2758 FUNCTION calc_element_itd_bal(p_assignment_action_id IN NUMBER,
2759 p_balance_type_id IN NUMBER,
2760 p_source_id IN NUMBER)
2761 RETURN NUMBER IS
2762 --
2763 l_balance NUMBER;
2764 l_defined_bal_id NUMBER;
2765 --
2766 BEGIN
2767 --
2768 l_defined_bal_id := dimension_relevant(p_balance_type_id, hr_jprts.g_element_itd);
2769 if l_defined_bal_id is not null then
2770 l_balance := get_latest_element_bal(
2771 p_assignment_action_id,
2772 l_defined_bal_id,
2773 p_source_id);
2774 if l_balance is null then
2775 --
2776 -- Fix bug#3083339: Set context to identify element name in the element level
2777 -- dimension rows.
2778 --
2779 -- This set source_id will be used in pay_balance_pkg.get_value function.
2780 pay_balance_pkg.set_context(
2781 p_context_name => 'ORIGINAL_ENTRY_ID',
2782 p_context_value => p_source_id);
2783 --
2784 l_balance := pay_balance_pkg.get_value(
2785 l_defined_bal_id,
2786 p_assignment_action_id);
2787 end if;
2788 else
2789 l_balance := null;
2790 --
2791 end if;
2792 --
2793 RETURN l_balance;
2794 END calc_element_itd_bal;
2795 --
2796 /* ------------------------------------------------------------------------------------
2797 --
2798 -- CALC_ELEMENT_PTD_BAL
2799 --
2800 -- This is the function for calculating element ptd balance
2801 --
2802 -- ------------------------------------------------------------------------------------ */
2803 FUNCTION calc_element_ptd_bal(p_assignment_action_id IN NUMBER,
2804 p_balance_type_id IN NUMBER,
2805 p_source_id IN NUMBER)
2806 RETURN NUMBER IS
2807 --
2808 l_balance NUMBER;
2809 l_defined_bal_id NUMBER;
2810 --
2811 BEGIN
2812 --
2813 l_defined_bal_id := dimension_relevant(p_balance_type_id, hr_jprts.g_element_ptd);
2814 if l_defined_bal_id is not null then
2815 --
2816 l_balance := get_latest_element_bal(
2817 p_assignment_action_id,
2818 l_defined_bal_id,
2819 p_source_id);
2820 if l_balance is null then
2821 --
2822 -- This set source_id will be used in pay_balance_pkg.get_value function.
2823 pay_balance_pkg.set_context(
2824 p_context_name => 'ORIGINAL_ENTRY_ID',
2825 p_context_value => p_source_id);
2826 l_balance := pay_balance_pkg.get_value(
2827 l_defined_bal_id,
2828 p_assignment_action_id);
2829 --l_balance := hr_routes.element_ptd(
2830 -- p_assignment_action_id,
2831 -- p_balance_type_id,
2832 -- p_source_id);
2833 end if;
2834 else
2835 l_balance := null;
2836 --
2837 end if;
2838 --
2839 RETURN l_balance;
2840 END calc_element_ptd_bal;
2841 --
2842 /* ------------------------------------------------------------------------------------
2843 --
2844 -- CALC_ALL_BALANCES
2845 -- -- assignment action Mode -
2846 --
2847 -- This is the generic overloaded function for calculating all balances
2848 -- in assignment action mode. NB Element level balances cannot be called
2849 -- from here as they require further context.
2850 --
2851 -- ------------------------------------------------------------------------------------ */
2852 FUNCTION calc_all_balances(
2853 p_assignment_action_id IN NUMBER,
2854 p_defined_balance_id IN NUMBER)
2855 --
2856 RETURN NUMBER
2857 IS
2858 --
2859 l_balance NUMBER;
2860 l_balance_type_id NUMBER;
2861 l_dimension_name VARCHAR2(80);
2862 --
2863 cursor get_balance_type_id(c_defined_balance_id IN NUMBER)
2864 IS
2865 SELECT /*+ ORDERED */
2866 pdb.balance_type_id,
2867 pbd.dimension_name
2868 FROM pay_defined_balances pdb,
2869 pay_balance_dimensions pbd
2870 WHERE pdb.defined_balance_id = c_defined_balance_id
2871 AND pbd.balance_dimension_id = pdb.balance_dimension_id;
2872 --
2873 BEGIN
2874 --
2875 OPEN get_balance_type_id(p_defined_balance_id);
2876 FETCH get_balance_type_id INTO l_balance_type_id, l_dimension_name;
2877 CLOSE get_balance_type_id;
2878 --
2879 If l_dimension_name = hr_jprts.g_asg_run then
2880 l_balance := calc_asg_run_action(
2881 p_assignment_action_id,
2882 l_balance_type_id);
2883 ELSIF l_dimension_name = hr_jprts.g_asg_mtd_jp then
2884 l_balance := calc_asg_mtd_jp_action(
2885 p_assignment_action_id,
2886 l_balance_type_id);
2887 -- l_dimension_name);
2888 ELSIF l_dimension_name = hr_jprts.g_ast_ytd_jp then
2889 l_balance := calc_asg_ytd_jp_action(
2890 p_assignment_action_id,
2891 l_balance_type_id);
2892 -- l_dimension_name);
2893 --
2894 elsif l_dimension_name = pyjpexc.c_asg_aprtd then
2895 --
2896 l_balance := calc_asg_apr2mar_jp_action(
2897 p_assignment_action_id,
2898 l_balance_type_id);
2899 --
2900 ELSIF l_dimension_name = hr_jprts.g_asg_aug2jul_jp then
2901 l_balance := calc_asg_aug2jul_jp_action(
2902 p_assignment_action_id,
2903 l_balance_type_id);
2904 -- l_dimension_name);
2908 l_balance := calc_asg_jul2jun_jp_action(
2905 ELSIF l_dimension_name = hr_jprts.g_asg_jul2jun_jp then
2906 -- calc_asg_jul2jun_jp_action is necessary
2907 -- this should be used in pay_jp_bal_matrix_by_act_v
2909 p_assignment_action_id,
2910 l_balance_type_id);
2911 ELSIF l_dimension_name = hr_jprts.g_asg_proc_ptd then
2912 l_balance := calc_asg_proc_ptd_action(
2913 p_assignment_action_id,
2914 l_balance_type_id);
2915 ELSIF l_dimension_name = hr_jprts.g_asg_itd then
2916 l_balance := calc_asg_itd_action(p_assignment_action_id,
2917 l_balance_type_id);
2918 ELSIF l_dimension_name = hr_jprts.g_asg_fytd_jp then
2919 l_balance := calc_asg_fytd_jp_action(
2920 p_assignment_action_id,
2921 l_balance_type_id);
2922 -- l_dimension_name);
2923 ELSIF l_dimension_name = hr_jprts.g_asg_fytd2_jp then
2924 -- calc_asg_fytd2_jp_action might be necessary
2925 l_balance := calc_asg_fytd2_jp_action(
2926 p_assignment_action_id,
2927 l_balance_type_id);
2928 /* -- Actually, this function is not used so that hr_routes.retro_jp does not exist. */
2929 ELSIF l_dimension_name = hr_jprts.g_retro then
2930 l_balance := calc_retro_action(p_assignment_action_id,
2931 l_balance_type_id);
2932 ELSIF l_dimension_name = hr_jprts.g_payment then
2933 l_balance := calc_payment_action(
2934 p_assignment_action_id,
2935 l_balance_type_id);
2936 /* -- This function can not call the calculation of Element dimension.
2937 -- Because it needs source_id.
2938 -- ELSIF l_dimension_name = hr_jprts.g_element_itd then
2939 -- fnd_message.set_name('PAY','This dimension is invalid');
2940 -- fnd_message.raise_error;
2941 --l_balance := NULL;
2942 -- ELSIF l_dimension_name = hr_jprts.g_element_ptd then
2943 -- fnd_message.set_name('PAY','This dimension is invalid');
2944 -- fnd_message.raise_error;
2945 -- l_balance := NULL; */
2946 --ELSE the balance must be for a USER-REG level dimension
2947 ELSE
2948 l_balance := balance(p_assignment_action_id, p_defined_balance_id);
2949 End If;
2950 --
2951 RETURN l_balance;
2952 END calc_all_balances;
2953 --
2954 /* ------------------------------------------------------------------------------------
2955 --
2956 -- CALC_ALL_BALANCES
2957 -- - Date Mode -
2958 --
2959 -- This is the overloaded generic function for calculating all balances
2960 -- in Date Mode. NB Element level balances cannot be obtained from here as
2961 -- they require further context.
2962 --
2963 -- ------------------------------------------------------------------------------------ */
2964 FUNCTION calc_all_balances(
2965 p_effective_date IN DATE,
2966 p_assignment_id IN NUMBER,
2967 p_defined_balance_id IN NUMBER)
2968 --
2969 RETURN NUMBER
2970 IS
2971 --
2972 l_balance NUMBER;
2973 l_balance_type_id NUMBER;
2974 l_dimension_name VARCHAR2(80);
2975 l_assignment_action_id NUMBER;
2976 --
2977 cursor get_balance_type_id(c_defined_balance_id IN NUMBER)
2978 IS
2979 SELECT /*+ ORDERED */
2980 pdb.balance_type_id,
2981 pbd.dimension_name
2982 FROM pay_defined_balances pdb,
2983 pay_balance_dimensions pbd
2984 WHERE pdb.defined_balance_id = c_defined_balance_id
2985 AND pbd.balance_dimension_id = pdb.balance_dimension_id;
2986 --
2987 BEGIN
2988 --
2989 OPEN get_balance_type_id(p_defined_balance_id);
2990 FETCH get_balance_type_id INTO l_balance_type_id, l_dimension_name;
2991 CLOSE get_balance_type_id;
2992 --
2993 If l_dimension_name = hr_jprts.g_asg_run then
2994 l_balance := calc_asg_run_date(
2995 p_assignment_id,
2996 l_balance_type_id,
2997 p_effective_date);
2998 ELSIF l_dimension_name = hr_jprts.g_asg_mtd_jp then
2999 l_balance := calc_asg_mtd_jp_date(
3000 p_assignment_id,
3001 l_balance_type_id,
3002 p_effective_date);
3003 ELSIF l_dimension_name = hr_jprts.g_ast_ytd_jp then
3004 l_balance := calc_asg_ytd_jp_date(
3005 p_assignment_id,
3006 l_balance_type_id,
3007 p_effective_date);
3008 --
3009 elsif l_dimension_name = pyjpexc.c_asg_aprtd then
3010 --
3011 l_balance := calc_asg_apr2mar_jp_date(
3012 p_assignment_id,
3013 l_balance_type_id,
3014 p_effective_date);
3015 --
3016 ELSIF l_dimension_name = hr_jprts.g_asg_aug2jul_jp then
3017 l_balance := calc_asg_aug2jul_jp_date(
3018 p_assignment_id,
3019 l_balance_type_id,
3020 p_effective_date);
3021 ELSIF l_dimension_name = hr_jprts.g_asg_jul2jun_jp then
3022 -- calc_asg_jul2jun_jp_date is necessary
3023 -- this should be used in pay_jp_bal_matrix_by_date_v
3024 l_balance := calc_asg_jul2jun_jp_date(
3025 p_assignment_id,
3026 l_balance_type_id,
3027 p_effective_date);
3028 ELSIF l_dimension_name = hr_jprts.g_asg_proc_ptd then
3029 l_balance := calc_asg_proc_ptd_date(
3030 p_assignment_id,
3031 l_balance_type_id,
3032 p_effective_date);
3033 ELSIF l_dimension_name = hr_jprts.g_asg_itd then
3034 l_balance := calc_asg_itd_date(
3035 p_assignment_id,
3036 l_balance_type_id,
3037 p_effective_date);
3038 ELSIF l_dimension_name = hr_jprts.g_asg_fytd_jp then
3039 l_balance := calc_asg_fytd_jp_date(
3040 p_assignment_id,
3041 l_balance_type_id,
3042 p_effective_date);
3043 ELSIF l_dimension_name = hr_jprts.g_asg_fytd2_jp then
3044 -- calc_asg_fytd2_jp_action might be necessary
3045 l_balance := calc_asg_fytd2_jp_date(
3046 p_assignment_id,
3047 l_balance_type_id,
3048 p_effective_date);
3049 /* -- Actually, this function is not used so that hr_routes.retro_jp does not exist. */
3050 ELSIF l_dimension_name = hr_jprts.g_retro then
3051 l_balance := calc_retro_date(
3052 p_assignment_id,
3053 l_balance_type_id,
3054 p_effective_date);
3055 ELSIF l_dimension_name = hr_jprts.g_payment then
3056 l_balance := calc_payment_date(
3057 p_assignment_id,
3058 l_balance_type_id,
3059 p_effective_date);
3060 /* -- This function can not call the calculation of Element dimension.
3061 -- Because it needs source_id.
3062 -- ELSIF l_dimension_name = hr_jprts.g_element_itd then
3063 -- fnd_message.set_name('PAY','This dimension is invalid');
3064 -- fnd_message.raise_error;
3065 -- l_balance := NULL;
3066 -- ELSIF l_dimension_name = hr_jprts.g_element_ptd then
3067 -- fnd_message.set_name('PAY','This dimension is invalid');
3068 -- fnd_message.raise_error;
3069 -- l_balance := NULL; */
3070 ----
3071 -- This comment is no more effective because new function has been added
3072 -- for hr_jprts.g_asg_jul2jun_jp and hr_jprts.g_asg_fytd2_jp
3073 --
3074 ELSE
3075 --This will trap USER-REG level balances
3076 l_balance := calc_balance_date(
3077 p_assignment_id,
3078 l_balance_type_id,
3079 p_effective_date,
3080 l_dimension_name);
3081 END IF;
3082 --
3083 RETURN l_balance;
3084 --
3085 END calc_all_balances;
3086 --
3087 END hr_jpbal;