[Home] [Help]
PACKAGE BODY: APPS.PAY_BAL_ADJUST
Source
1 package body pay_bal_adjust as
2 /* $Header: pybaladj.pkb 120.5.12010000.2 2008/10/01 06:12:11 ankagarw ship $ */
3 /*
4 NOTES
5 o The first implementation of the batch balance adjustment does
6 not make use of the (yet to be designed) batch adjutment tables.
7 Therefore, this process either runs or fails in one commit, as
8 there is no place to store the information about the adjustments
9 to allow re-runs. Therefore, any error causes an immediate
10 exit from the entire process, all work having been lost.
11 o The upcoming proper batch version may even use the datapump
12 tables, but this is not decided yet.
13 */
14
15 /*---------------------------------------------------------------------------*/
16 /*-------------------------- constant definitions ---------------------------*/
17 /*---------------------------------------------------------------------------*/
18 g_number constant number:= hr_api.g_number;
19
20 /*---------------------------------------------------------------------------*/
21 /*------------------------ balance adjustment types -------------------------*/
22 /*---------------------------------------------------------------------------*/
23 type info_r is record
24 (
25 batchid number,
26 effdate date,
27 busgrpid number,
28 legcode varchar2(30),
29 asgid number,
30 assactid number,
31 payid number,
32 runtypeid number,
33 batch_mode varchar2(30),
34 tax_unit_id number,
35 purge_mode boolean,
36 action_type varchar2(30)
37
38 );
39
40 /*---------------------------------------------------------------------------*/
41 /*----------------------- balance adjustment globals ------------------------*/
42 /*---------------------------------------------------------------------------*/
43 g_info info_r;
44 g_curr_chunk_no number := 1;
45 g_no_asg_act number := 1;
46
47 /*---------------------------------------------------------------------------*/
48 /*--------------------- local functions and procedures ----------------------*/
49 /*---------------------------------------------------------------------------*/
50
51 /*
52 * This is a cover for a reset to the
53 * global information variable. Called to reset after the
54 * batch has been processed.
55 */
56 procedure purge_batch_info is
57 begin
58 g_info.batchid := null;
59 g_info.effdate := null;
60 g_info.busgrpid := null;
61 g_info.legcode := null;
62 g_info.asgid := null;
63 g_info.assactid := null;
64 g_info.payid := null;
65 g_info.runtypeid := null;
66 g_info.tax_unit_id := null;
67 g_info.purge_mode := null;
68 g_info.action_type := null;
69 end purge_batch_info;
70
71 /*
72 * Insert an assignment action for a particular assignment
73 * to be processed by the balance adjustment. Note that there
74 * is one assignment action per assignment/balance adjustment.
75 * Therefore, there can be more than one assignment action per
76 * assignnment attached to the payroll action.
77 */
78 function insert_assact
79 (
80 p_info in out nocopy info_r,
81 p_element_entry_id in number,
82 run_type_id in number default null
83 ) return number is
84 l_assactid number;
85 l_payid number;
86 l_tax_unit_id number:= p_info.tax_unit_id;
87 l_chunk_size number;
88 l_found boolean;
89 begin
90
91
92 pay_core_utils.get_action_parameter('CHUNK_SIZE',l_chunk_size,l_found);
93
94 if (l_found=FALSE)
95 then
96 l_chunk_size := 20;
97 end if;
98 --
99 -- Identify the tax unit
100 --
101 if l_tax_unit_id is null then
102
103 l_tax_unit_id := hr_dynsql.get_tax_unit
104 (p_assignment_id => p_info.asgid
105 ,p_effective_date => p_info.effdate
106 );
107 end if;
108
109 -- Look for an existing assignment action.
110 select act.assignment_action_id
111 into l_assactid
112 from pay_assignment_actions act
113 where act.payroll_action_id = p_info.batchid
114 and nvl(act.tax_unit_id, g_number) = nvl(l_tax_unit_id, g_number)
115 and act.assignment_id = p_info.asgid;
116
117 hr_utility.trace('(existing) l_assactid : ' || l_assactid);
118
119 return(l_assactid);
120
121 exception when no_data_found then
122 -- Need to create new assignment action.
123 -- Also trashes the latest balances.
124 hrassact.inassact_main
125 (pactid => p_info.batchid
126 ,asgid => p_info.asgid
127 ,p_ass_action_seq => null
128 ,p_serial_number => null
129 ,p_pre_payment_id => null
130 ,p_element_entry => p_element_entry_id
131 ,p_asg_lock => TRUE
132 ,taxunt => l_tax_unit_id
133 ,p_purge_mode => p_info.purge_mode
134 ,p_run_type_id => run_type_id
135 );
136
137 -- Get the action id of that created.
138 hr_utility.set_location('insert_assact', 20);
139 select act.assignment_action_id
140 into l_assactid
141 from pay_assignment_actions act
142 where act.payroll_action_id = p_info.batchid
143 and act.assignment_id = p_info.asgid
144 and nvl(act.tax_unit_id, g_number) = nvl(l_tax_unit_id, g_number);
145
146 hr_utility.trace('(new) l_assactid : ' || l_assactid);
147
148 update pay_assignment_actions
149 set chunk_number = g_curr_chunk_no
150 where assignment_action_id= l_assactid;
151
152 g_no_asg_act := g_no_asg_act + 1;
153 if g_no_asg_act > l_chunk_size
154 then
155 g_curr_chunk_no := g_curr_chunk_no +1;
156 g_no_asg_act :=1;
157 end if;
158
159
160 return(l_assactid);
161
162 end insert_assact;
163
164 /*
165 * Get some information about the batch getting processed.
166 * Also performs some validation on that batch.
167 */
168 function get_batch_info
169 (
170 p_batch_id in number
171 ) return info_r is
172 l_business_group_id number;
173 l_legislation_code varchar2(30);
174 l_payroll_id number;
175 l_effective_date date;
176 l_mode varchar2(30);
177 l_info info_r;
178 l_action_type varchar2(30);
179 begin
180 if(p_batch_id <> g_info.batchid or g_info.batchid is null) then
181 -- Re-set the assignment information.
182 g_info.asgid := null;
183 g_info.runtypeid := null;
184 g_info.tax_unit_id := null;
185 g_info.purge_mode := null;
186
187 -- Get information for the batch.
188 -- We perform a basic check here that we are not
189 -- effectively processing a batch we shouldn't be.
190 select pac.business_group_id,
191 pac.effective_date,
192 pac.payroll_id,
193 pbg.legislation_code,
194 pac.batch_process_mode,
195 pac.action_type
196 into l_business_group_id,
197 l_effective_date,
198 l_payroll_id,
199 l_legislation_code,
200 l_mode,
201 l_action_type
202 from pay_payroll_actions pac,
203 per_business_groups pbg
204 where pac.payroll_action_id = p_batch_id
205 and pac.action_status <> 'C'
206 and pbg.business_group_id = pac.business_group_id;
207
208 -- Everything ok - store information in global record.
209 g_info.batchid := p_batch_id;
210 g_info.busgrpid := l_business_group_id;
211 g_info.legcode := l_legislation_code;
212 g_info.effdate := l_effective_date;
213 g_info.payid := l_payroll_id;
214 g_info.batch_mode := l_mode;
215 g_info.action_type:= l_action_type;
216
217 -- Output information the first time round.
218 hr_utility.trace('batchid : ' || g_info.batchid);
219 hr_utility.trace('effdate : ' || fnd_date.date_to_canonical(l_info.effdate));
220 hr_utility.trace('busgrpid : ' || g_info.busgrpid);
221 hr_utility.trace('asgid : ' || g_info.asgid);
222 hr_utility.trace('mode : ' || g_info.batch_mode);
223 hr_utility.trace('acttype : ' || g_info.action_type);
224
225 end if;
226
227 l_info := g_info;
228
229 return(l_info);
230
231 end get_batch_info;
232
233 /*
234 * Function returns record holding relevant
235 * information about the assignment.
236 * Returns the information in the general
237 * information record.
238 *
239 * Note that the current implementation of
240 * this function uses the existing hrassact
241 * inassact procedure. This avoids the need
242 * to clone the logic for retrospective
243 * adjustments. However, this means that
244 * it needs to be passed the element_entry_id
245 * (which is used in latest balance trashing)
246 * and therefore has to be called after the
247 * element entry creation.
248 */
249 function get_asg_info
250 (
251 p_info in info_r,
252 p_assignment_id in number,
253 p_element_entry_id in number,
254 run_type_id in number default null,
255 p_tax_unit_id in number default null,
256 p_purge_mode in boolean default false
257 ) return info_r is
258 l_info info_r;
259 l_assactid number;
260 begin
261
262 -- If anything relevant has changed, we need to
263 -- (re)derive the information.
264 if(g_info.asgid is null
265 or g_info.asgid <> p_assignment_id
266 or nvl(g_info.runtypeid, g_number) <> nvl(run_type_id, g_number)
267 or nvl(g_info.tax_unit_id, g_number) <> nvl(p_tax_unit_id, g_number)
268 ) then
269
270 hr_utility.trace('batchid : ' || p_info.batchid);
271
272 g_info.asgid := p_assignment_id;
273 g_info.runtypeid := run_type_id;
274 g_info.tax_unit_id := p_tax_unit_id;
275 g_info.purge_mode := p_purge_mode;
276
277 -- May need to insert an assignment action.
278 g_info.assactid := insert_assact(g_info, p_element_entry_id,run_type_id);
279
280 -- Output information first time round.
281 hr_utility.trace('assignment_id : ' || p_assignment_id);
282 hr_utility.trace('business_group_id : ' || g_info.busgrpid);
283 hr_utility.trace('assignment_action_id : ' || g_info.assactid);
284 hr_utility.trace('run_type_id : ' || g_info.runtypeid);
285
286 end if;
287
288 -- Return the information.
289 l_info := g_info;
290
291 return(l_info);
292
293 end get_asg_info;
294
295 /*---------------------------------------------------------------------------*/
296 /*------------------ global functions and procedures ------------------------*/
297 /*---------------------------------------------------------------------------*/
298
299 /*
300 * Initialises the batch balance adjustment run.
301 * Must be called before the adjust_balance procedures.
302 * Currently, this inserts a payroll action against which
303 * the Balance Adjustments will be processed.
304 */
305 function init_batch
306 (
307 p_batch_name in varchar2 default null,
308 p_effective_date in date,
309 p_consolidation_set_id in number,
310 p_payroll_id in number,
311 p_action_type in varchar2 default 'B', -- for balance adjustment.
312 p_batch_mode in varchar2 default 'STANDARD',
313 p_prepay_flag in varchar2 default 'Y'
314 ) return number is
315 l_proc varchar2(72) := 'pay_bal_adjust.init_batch';
316 l_business_group_id number;
317 l_payroll_action_id number;
318 l_exists number;
319 l_time_period_id number;
320
321 cursor csr_time_period
322 is
323 select
324 ptp.time_period_id
325 from
326 per_time_periods ptp
327 where
328 ptp.payroll_id = p_payroll_id
329 and p_effective_date between ptp.start_date
330 and ptp.end_date;
331
332 begin
333 hr_utility.set_location('Entering: '||l_proc, 5);
334
335 -- Get the business group from consolidation set
336 -- and validate at same time.
337 -- Also check that payroll_id passed in is valid.
338 select con.business_group_id,
339 pay_payroll_actions_s.nextval
340 into l_business_group_id,
341 l_payroll_action_id
342 from pay_consolidation_sets con,
343 pay_all_payrolls_f prl
344 where con.consolidation_set_id = p_consolidation_set_id
345 and prl.payroll_id = p_payroll_id
346 and p_effective_date between
347 prl.effective_start_date and prl.effective_end_date
348 ;
349 --
350 -- Obtain the time period
351 --
352 open csr_time_period;
353 fetch csr_time_period into l_time_period_id;
354 close csr_time_period;
355
356 -- We can now insert the payroll action.
357 insert into pay_payroll_actions (
358 payroll_action_id,
359 action_type,
360 business_group_id,
361 consolidation_set_id,
362 payroll_id,
363 action_population_status,
364 action_status,
365 effective_date,
366 date_earned,
367 action_sequence,
368 legislative_parameters,
369 future_process_mode,
370 batch_process_mode,
371 object_version_number,
372 time_period_id,
373 creation_date)
374 values (l_payroll_action_id,
375 p_action_type,
376 l_business_group_id,
377 p_consolidation_set_id,
378 p_payroll_id,
379 'P',
380 'U',
381 p_effective_date,
382 p_effective_date,
383 pay_payroll_actions_s.nextval,
384 p_batch_name,
385 p_prepay_flag,
386 p_batch_mode,
387 1,
388 l_time_period_id,
389 sysdate);
390
391 hr_utility.trace('batch_id : ' || l_payroll_action_id);
392
393 -- Crude validation of modes.
394 if(p_batch_mode not in ('STANDARD', 'NO_COMMIT')) then
395 ff_utils.assert(false, 'init_batch:1');
396 end if;
397
398 hr_utility.set_location('Leaving: '||l_proc, 100);
399 --
400 return(l_payroll_action_id);
401
402 end init_batch;
403
404
405 procedure set_lat_balances
406 (
407 p_assignment_id in number default null,
408 p_original_entry_id in number default null,
409 p_element_entry_id in number default null,
410 p_effdate in date default null,
411 p_busgrpid in number default null,
412 p_legcode in varchar2 default null,
413 p_assactid in number default null,
414 p_action_type in varchar2 default 'B',
415 p_run_result_id in number
416 )
417 is
418 udca hrassact.context_details;
419 tax_unit number;
420 begin
421
422 /*
423 * 3482270.
424 * This update is no longer necessary, hence commented out.
425 *
426 -- Update the inserted run result appropriately.
427 update pay_run_results prr
428 set prr.assignment_action_id = p_assactid
429 ,prr.source_id = nvl(p_original_entry_id, prr.source_id)
430 ,prr.status = 'P'
431 where prr.source_id = p_element_entry_id
432 and prr.source_type = 'E';
433 */
434
435 select tax_unit_id
436 into tax_unit
437 from pay_assignment_actions
438 where assignment_action_id = p_assactid;
439 --
440 hrassact.set_action_context (p_assactid,
441 p_run_result_id,
442 p_element_entry_id,
443 tax_unit,
444 p_assignment_id,
445 p_busgrpid,
446 p_legcode,
447 p_original_entry_id,
448 udca
449 );
450
451 if p_action_type = 'B' then
452
453 -- Make call to maintain latest balances.
454 hrassact.maintain_lat_bal (
455 assactid => p_assactid,
456 rrid => p_run_result_id,
457 eentryid => p_element_entry_id,
458 effdate => p_effdate,
459 udca => udca);
460 end if;
461
462
463 end;
464
465 /*
466 * Balance Adjustment.
467 * For calling information, please see the header.
468 */
469 procedure adjust_balance
470 (
471 p_batch_id in number,
472 p_assignment_id in number,
473 p_element_link_id in number,
474
475 --
476 -- Element Entry Values Table
477 --
478 p_num_entry_values IN number,
479 p_input_value_id_tbl IN hr_entry.number_table,
480 p_entry_value_tbl IN hr_entry.varchar2_table,
481
482 -- Costing information.
483 p_balance_adj_cost_flag in varchar2 default null,
484 p_cost_allocation_keyflex_id in number default null,
485 p_attribute_category in varchar2 default null,
486 p_attribute1 in varchar2 default null,
487 p_attribute2 in varchar2 default null,
488 p_attribute3 in varchar2 default null,
489 p_attribute4 in varchar2 default null,
490 p_attribute5 in varchar2 default null,
491 p_attribute6 in varchar2 default null,
492 p_attribute7 in varchar2 default null,
493 p_attribute8 in varchar2 default null,
494 p_attribute9 in varchar2 default null,
495 p_attribute10 in varchar2 default null,
496 p_attribute11 in varchar2 default null,
497 p_attribute12 in varchar2 default null,
498 p_attribute13 in varchar2 default null,
499 p_attribute14 in varchar2 default null,
500 p_attribute15 in varchar2 default null,
501 p_attribute16 in varchar2 default null,
502 p_attribute17 in varchar2 default null,
503 p_attribute18 in varchar2 default null,
504 p_attribute19 in varchar2 default null,
505 p_attribute20 in varchar2 default null,
506 p_run_type_id in number default null,
507 p_original_entry_id in number default null,
508 p_tax_unit_id in number default null,
509 p_purge_mode in boolean default false
510 ) is
511 l_info info_r;
512 l_consetid number;
513
514 -- Returns from the API.
515 l_create_warning boolean;
516
517 --
518 -- Declare cursors and local variables
519 --
520 l_run_result_id number;
521 l_jc_name varchar2(30);
522 l_rr_sparse boolean;
523 l_rr_sparse_jc boolean;
524 l_rule_mode varchar2(30);
525 l_status varchar2(30);
526 l_found boolean;
527 l_element_entry_id pay_element_entries_f.element_entry_id%TYPE;
528 l_object_version_number pay_element_entries_f.object_version_number%TYPE;
529 l_effective_start_date pay_element_entries_f.effective_start_date%TYPE;
530 l_effective_end_date pay_element_entries_f.effective_end_date%TYPE;
531 l_process_in_run_flag pay_element_types_f.process_in_run_flag%TYPE;
532 l_closed_for_entry_flag pay_element_types_f.closed_for_entry_flag%TYPE;
533 l_period_status per_time_periods.status%TYPE;
534 l_date_on_which_time_served_ok date;
535 l_date_on_which_old_enough date;
536 l_dummy varchar2(1);
537
538 l_proc varchar2(72) := 'pay_bal_adjust.adjust_balance';
539 l_element_name pay_element_types_f.element_name%TYPE;
540 l_legislation_code pay_element_types_f.legislation_code%TYPE;
541
542 -- bug 659393, added variables for storing all dates pased in and truncate them
543 l_effective_date date;
544 --
545 -- Bugfix 2665492
546 -- l_costable_type needed to hold the costable_type of the element link
547 --
548 l_costable_type pay_element_links_f.costable_type%TYPE;
549 --
550 CURSOR c_output_variables IS
551 SELECT ee.object_version_number
552 FROM pay_element_entries_f ee
553 WHERE l_element_entry_id = ee.element_entry_id
554 -- bug 675794, added date condition to select correct row
555 AND l_effective_date BETWEEN ee.effective_start_date
556 AND ee.effective_end_date;
557
558 CURSOR c_assignment_details IS
559 SELECT ptp.status
560 FROM per_time_periods ptp,
561 per_all_assignments_f pas
562 WHERE pas.assignment_id = p_assignment_id
563 AND pas.payroll_id = ptp.payroll_id
564 AND l_effective_date BETWEEN ptp.start_date
565 AND ptp.end_date
566 AND l_effective_date BETWEEN pas.effective_start_date
567 AND pas.effective_end_date;
568
569 CURSOR c_entry_exists IS
570 SELECT 'X'
571 FROM pay_element_entries_f ee,
572 pay_element_types_f et,
573 pay_element_links_f el
574 WHERE el.element_link_id = ee.element_link_id
575 AND el.element_link_id = p_element_link_id
576 AND el.element_type_id = et.element_type_id
577 AND ee.assignment_id = p_assignment_id
578 AND l_effective_date BETWEEN ee.effective_start_date
579 AND ee.effective_end_date
580 AND l_effective_date BETWEEN el.effective_start_date
581 AND el.effective_end_date
582 AND l_effective_date BETWEEN et.effective_start_date
583 AND et.effective_end_date
584 AND et.multiple_entries_allowed_flag = 'N'
585 AND ee.entry_type = 'E';
586
587 CURSOR c_element_info IS
588 SELECT et.closed_for_entry_flag,
589 et.process_in_run_flag,
590 et.element_name,
591 et.legislation_code,
592 --
593 -- Bugfix 2665492
594 -- Retrieve the element_link costable_type
595 --
596 el.costable_type
597 FROM pay_element_types_f et,
598 pay_element_links_f el
599 WHERE el.element_link_id = p_element_link_id
600 AND el.element_type_id = et.element_type_id
601 AND l_effective_date BETWEEN el.effective_start_date
602 AND el.effective_end_date
603 AND l_effective_date BETWEEN et.effective_start_date
604 AND et.effective_end_date;
605
606
607 BEGIN
608
609 --
610 -- Issue a savepoint
611 --
612 savepoint adjust_balance;
613
614 -- Validate the batch we are passing in, fetching
615 -- the information if necessary.
616 l_info := get_batch_info(p_batch_id);
617 l_effective_date := trunc(l_info.effdate);
618 l_effective_start_date := l_effective_date;
619
620 hr_utility.set_location(l_proc, 70);
621
622 -- can't justify why this is used yet
623 /*
624 OPEN c_entry_exists;
625 FETCH c_entry_exists
626 INTO l_dummy;
627 IF c_entry_exists%FOUND THEN
628 CLOSE c_entry_exists;
629 hr_utility.set_location(l_proc, 80);
630 hr_utility.set_message(801,'HR_7455_PLK_ELE_ENTRY_EXISTS');
631 hr_utility.raise_error;
632 END IF;
633 CLOSE c_entry_exists;
634 */
635 hr_utility.set_location(l_proc, 90);
636 OPEN c_element_info;
637 FETCH c_element_info
638 INTO l_closed_for_entry_flag,
639 l_process_in_run_flag,
640 l_element_name,
641 l_legislation_code,
642 --
643 -- Bugfix 2665492
644 -- Fetch the element_link costable_type
645 --
646 l_costable_type;
647
648 IF c_element_info%NOTFOUND THEN
649 CLOSE c_element_info;
653 END IF;
650 hr_utility.set_location(l_proc, 95);
651 hr_utility.set_message(801,'HR_6132_ELE_ENTRY_LINK_MISSING');
652 hr_utility.raise_error;
654 CLOSE c_element_info;
655 --
656 hr_utility.set_location(l_proc, 100);
657 OPEN c_assignment_details;
658 FETCH c_assignment_details
659 INTO l_period_status;
660 --
661 -- bug 685930, commented this out as it is done by the api, and for non-recurring entries only.
662 --
663 CLOSE c_assignment_details;
664
665 IF l_closed_for_entry_flag = 'Y' THEN
666
667 hr_utility.set_location(l_proc, 110);
668 hr_utility.set_message(801,'HR_6064_ELE_ENTRY_CLOSED_ELE');
669 hr_utility.raise_error;
670
671 -- Error will not be raised for VERTEX, Workers Compensation element with
672 -- Legislation code as US. Bug No 506819
673
674 ELSIF (l_period_status = 'C' AND l_process_in_run_flag = 'Y'
675 AND l_element_name not in ('US_TAX_VERTEX','VERTEX','Workers Compensation')
676 AND l_legislation_code <> 'US') THEN
677
678 hr_utility.set_location(l_proc, 120);
679 hr_utility.set_message(801,'HR_6074_ELE_ENTRY_CLOSE_PERIOD');
680 hr_utility.raise_error;
681
682 --
683 -- Bugfix 2665492
684 -- Ensure that element_link is costable if cost_allocation_keyflex_id
685 -- is not null
686 --
687 ELSIF l_costable_type = 'N' and p_cost_allocation_keyflex_id IS NOT NULL THEN
688 --
689 hr_utility.set_location(l_proc,130);
690 hr_utility.set_message(801,'HR_7453_PLK_NON_COSTABLE_ELE');
691 hr_utility.set_warning;
692 --
693 END IF;
694
695 hr_entry.return_qualifying_conditions (p_assignment_id,
696 p_element_link_id,
697 l_effective_date,
698 l_date_on_which_time_served_ok,
699 l_date_on_which_old_enough );
700
701 IF l_effective_date < l_date_on_which_time_served_ok THEN
702 hr_utility.set_message(801, 'HR_ELE_ENTRY_QUAL_LOS');
703 hr_utility.set_warning;
704 ELSIF l_effective_date < l_date_on_which_old_enough THEN
705 hr_utility.set_message(801, 'HR_ELE_ENTRY_QUAL_AGE');
706 hr_utility.set_warning;
707 END IF;
708
709
710 hr_utility.set_location(l_proc, 350);
711 hr_entry_api.insert_element_entry
712 (
713 p_effective_start_date => l_effective_start_date,
714 p_effective_end_date => l_effective_end_date,
715 p_element_entry_id => l_element_entry_id,
716 p_original_entry_id => p_original_entry_id,
717 p_assignment_id => p_assignment_id,
718 p_element_link_id => p_element_link_id,
719 -- create all elements as type 'F' with NULL creator
720 p_creator_type => 'B',
721 p_entry_type => 'B',
722 p_creator_id => l_info.assactid,
723 p_cost_allocation_keyflex_id => p_cost_allocation_keyflex_id,
724 p_attribute_category => p_attribute_category,
725 p_attribute1 => p_attribute1,
726 p_attribute2 => p_attribute2,
727 p_attribute3 => p_attribute3,
728 p_attribute4 => p_attribute4,
729 p_attribute5 => p_attribute5,
730 p_attribute6 => p_attribute6,
731 p_attribute7 => p_attribute7,
732 p_attribute8 => p_attribute8,
733 p_attribute9 => p_attribute9,
734 p_attribute10 => p_attribute10,
735 p_attribute11 => p_attribute11,
736 p_attribute12 => p_attribute12,
737 p_attribute13 => p_attribute13,
738 p_attribute14 => p_attribute14,
739 p_attribute15 => p_attribute15,
740 p_attribute16 => p_attribute16,
741 p_attribute17 => p_attribute17,
742 p_attribute18 => p_attribute18,
743 p_attribute19 => p_attribute19,
744 p_attribute20 => p_attribute20,
745 p_num_entry_values => p_num_entry_values,
746 p_input_value_id_tbl => p_input_value_id_tbl,
747 p_entry_value_tbl => p_entry_value_tbl);
748
749
750 -- calc jur code name
751 pay_core_utils.get_leg_context_iv_name
752 ('JURISDICTION_CODE',
753 l_info.legcode,
754 l_jc_name,
755 l_found);
756 if (l_found = FALSE) then
757 l_jc_name := 'Jurisdiction';
758 end if;
759
760 -- set rr sparse leg_rule
761 pay_core_utils.get_legislation_rule('RR_SPARSE',
762 l_info.legcode,
763 l_rule_mode,
764 l_found
765 );
766 if (l_found = FALSE) then
767 l_rule_mode := 'N';
768 end if;
769
770 if upper(l_rule_mode)='Y'
771 then
772 -- Confirm Enabling Upgrade has been made by customer
773 pay_core_utils.get_upgrade_status(l_info.busgrpid,
774 'ENABLE_RR_SPARSE',
775 l_status);
776
777 if upper(l_status)='N'
781 end if;
778 then
779 l_rule_mode := 'N';
780 end if;
782
783 if upper(l_rule_mode)='Y'
784 then
785 l_rr_sparse:=TRUE;
786 else
787 l_rr_sparse :=FALSE;
788 end if;
789 --
790 pay_core_utils.get_upgrade_status(l_info.busgrpid,
791 'RR_SPARSE_JC',
792 l_status);
793 --
794 if upper(l_status)='Y'
795 then
796 l_rr_sparse_jc :=TRUE;
797 else
798 l_rr_sparse_jc :=FALSE;
799 end if;
800 --
801 IF hr_utility.check_warning THEN
802 l_create_warning := TRUE;
803 hr_utility.clear_warning;
804 END IF;
805 --
806 -- Set all output arguments
807 --
808 OPEN c_output_variables;
809 FETCH c_output_variables
810 INTO l_object_version_number;
811 CLOSE c_output_variables;
812
813 -- Get information related to the assignment.
814 -- See comments for this function.
815 l_info := get_asg_info(l_info
816 ,p_assignment_id
817 ,l_element_entry_id
818 ,p_run_type_id
819 ,p_tax_unit_id
820 ,p_purge_mode
821 );
822 --
823 -- create run result
824 pay_run_result_pkg.create_run_result(
825 p_element_entry_id => l_element_entry_id,
826 p_session_date => l_effective_date,
827 p_business_group_id => l_info.busgrpid,
828 p_jc_name => l_jc_name,
829 p_rr_sparse => l_rr_sparse,
830 p_rr_sparse_jc => l_rr_sparse_jc,
831 p_asg_action_id => l_info.assactid,
832 p_run_result_id => l_run_result_id);
833
834
835 -- Set the creator information. Doing this here because
836 -- the entry api doesn't allow us to on insert (which seems odd)
837 -- and don't see the point of going all out to use the
838 -- update API. May re-address when do 'proper' batch version.
839 -- Also, balance_adj_cost_flag is not supported yet by API.
840 update pay_element_entries_f pee
841 set pee.creator_id = l_info.assactid,
842 pee.creator_type = 'B',
843 pee.balance_adj_cost_flag = p_balance_adj_cost_flag
844 where pee.element_entry_id = l_element_entry_id
845 and l_info.effdate between
846 pee.effective_start_date and pee.effective_end_date;
847
848 set_lat_balances( p_assignment_id ,
849 p_original_entry_id,
850 l_element_entry_id,
851 l_info.effdate,
852 l_info.busgrpid,
853 l_info.legcode,
854 l_info.assactid,
855 l_info.action_type,
856 l_run_result_id
857 );
858
859 --
860 -- Create asg run balances based on the run result.
861 --
862 pay_balance_pkg.create_rr_asg_balances
863 (p_run_result_id => l_run_result_id
864 );
865
866 exception
867 when others then
868 rollback to adjust_balance;
869 --
870 -- Clear the global cache
871 --
872 purge_batch_info;
873
874 -- Output some final information about the error.
875 hr_utility.trace('** exception information **');
876 hr_utility.trace('l_info.batchid : ' || l_info.batchid);
877 hr_utility.trace('l_info.assactid : ' || l_info.assactid);
878 hr_utility.trace('l_info.payid : ' || l_info.payid);
879 hr_utility.trace('l_effective_start_date : ' || l_effective_start_date);
880 hr_utility.trace('l_effective_end_date : ' || l_effective_end_date);
881 hr_utility.trace('l_element_entry_id : ' || l_element_entry_id);
882 hr_utility.trace('l_object_version_number : ' || l_object_version_number);
883
884 raise;
885 end adjust_balance;
886 procedure adjust_balance
887 (
888 p_batch_id in number,
889 p_assignment_id in number,
890 p_element_link_id in number,
891 p_input_value_id1 in number default null,
892 p_input_value_id2 in number default null,
893 p_input_value_id3 in number default null,
894 p_input_value_id4 in number default null,
895 p_input_value_id5 in number default null,
896 p_input_value_id6 in number default null,
897 p_input_value_id7 in number default null,
898 p_input_value_id8 in number default null,
899 p_input_value_id9 in number default null,
900 p_input_value_id10 in number default null,
901 p_input_value_id11 in number default null,
902 p_input_value_id12 in number default null,
903 p_input_value_id13 in number default null,
904 p_input_value_id14 in number default null,
905 p_input_value_id15 in number default null,
906 p_entry_value1 in varchar2 default null,
907 p_entry_value2 in varchar2 default null,
911 p_entry_value6 in varchar2 default null,
908 p_entry_value3 in varchar2 default null,
909 p_entry_value4 in varchar2 default null,
910 p_entry_value5 in varchar2 default null,
912 p_entry_value7 in varchar2 default null,
913 p_entry_value8 in varchar2 default null,
914 p_entry_value9 in varchar2 default null,
915 p_entry_value10 in varchar2 default null,
916 p_entry_value11 in varchar2 default null,
917 p_entry_value12 in varchar2 default null,
918 p_entry_value13 in varchar2 default null,
919 p_entry_value14 in varchar2 default null,
920 p_entry_value15 in varchar2 default null,
921
922 -- Costing information.
923 p_balance_adj_cost_flag in varchar2 default null,
924 p_cost_allocation_keyflex_id in number default null,
925 p_attribute_category in varchar2 default null,
926 p_attribute1 in varchar2 default null,
927 p_attribute2 in varchar2 default null,
928 p_attribute3 in varchar2 default null,
929 p_attribute4 in varchar2 default null,
930 p_attribute5 in varchar2 default null,
931 p_attribute6 in varchar2 default null,
932 p_attribute7 in varchar2 default null,
933 p_attribute8 in varchar2 default null,
934 p_attribute9 in varchar2 default null,
935 p_attribute10 in varchar2 default null,
936 p_attribute11 in varchar2 default null,
937 p_attribute12 in varchar2 default null,
938 p_attribute13 in varchar2 default null,
939 p_attribute14 in varchar2 default null,
940 p_attribute15 in varchar2 default null,
941 p_attribute16 in varchar2 default null,
942 p_attribute17 in varchar2 default null,
943 p_attribute18 in varchar2 default null,
944 p_attribute19 in varchar2 default null,
945 p_attribute20 in varchar2 default null,
946 p_run_type_id in number default null,
947 p_original_entry_id in number default null,
948 p_tax_unit_id in number default null,
949 p_purge_mode in boolean default false
950 ) is
951 l_info info_r;
952 l_consetid number;
953
954 -- Returns from the API.
955 l_effective_start_date date;
956 l_effective_end_date date;
957 l_element_entry_id number;
958 l_object_version_number number;
959 l_create_warning boolean;
960
961 l_dummy number;
962
963 l_run_result_id number;
964 l_jc_name varchar2(30);
965 l_rr_sparse boolean;
966 l_rr_sparse_jc boolean;
967 l_rule_mode varchar2(30);
968 l_status varchar2(30);
969 l_found boolean;
970 begin
971
972 --
973 -- Issue a savepoint
974 --
975 savepoint adjust_balance;
976
977 -- Validate the batch we are passing in, fetching
978 -- the information if necessary.
979 l_info := get_batch_info(p_batch_id);
980
981 -- Create the element entry.
982 py_element_entry_api.create_element_entry (
983 p_effective_date => l_info.effdate,
984 p_business_group_id => l_info.busgrpid,
985 p_original_entry_id => p_original_entry_id,
986 p_assignment_id => p_assignment_id,
987 p_element_link_id => p_element_link_id,
988 p_entry_type => 'B', -- Balance Adjustment entry.
989 p_creator_type => 'B',
990 p_input_value_id1 => p_input_value_id1,
991 p_input_value_id2 => p_input_value_id2,
992 p_input_value_id3 => p_input_value_id3,
993 p_input_value_id4 => p_input_value_id4,
994 p_input_value_id5 => p_input_value_id5,
995 p_input_value_id6 => p_input_value_id6,
996 p_input_value_id7 => p_input_value_id7,
997 p_input_value_id8 => p_input_value_id8,
998 p_input_value_id9 => p_input_value_id9,
999 p_input_value_id10 => p_input_value_id10,
1000 p_input_value_id11 => p_input_value_id11,
1001 p_input_value_id12 => p_input_value_id12,
1002 p_input_value_id13 => p_input_value_id13,
1003 p_input_value_id14 => p_input_value_id14,
1004 p_input_value_id15 => p_input_value_id15,
1005 p_entry_value1 => p_entry_value1,
1006 p_entry_value2 => p_entry_value2,
1007 p_entry_value3 => p_entry_value3,
1008 p_entry_value4 => p_entry_value4,
1009 p_entry_value5 => p_entry_value5,
1010 p_entry_value6 => p_entry_value6,
1011 p_entry_value7 => p_entry_value7,
1012 p_entry_value8 => p_entry_value8,
1013 p_entry_value9 => p_entry_value9,
1014 p_entry_value10 => p_entry_value10,
1015 p_entry_value11 => p_entry_value11,
1016 p_entry_value12 => p_entry_value12,
1017 p_entry_value13 => p_entry_value13,
1018 p_entry_value14 => p_entry_value14,
1022 p_cost_allocation_keyflex_id => p_cost_allocation_keyflex_id,
1019 p_entry_value15 => p_entry_value15,
1020
1021 -- Costing information.
1023 p_attribute_category => p_attribute_category,
1024 p_attribute1 => p_attribute1,
1025 p_attribute2 => p_attribute2,
1026 p_attribute3 => p_attribute3,
1027 p_attribute4 => p_attribute4,
1028 p_attribute5 => p_attribute5,
1029 p_attribute6 => p_attribute6,
1030 p_attribute7 => p_attribute7,
1031 p_attribute8 => p_attribute8,
1032 p_attribute9 => p_attribute9,
1033 p_attribute10 => p_attribute10,
1034 p_attribute11 => p_attribute11,
1035 p_attribute12 => p_attribute12,
1036 p_attribute13 => p_attribute13,
1037 p_attribute14 => p_attribute14,
1038 p_attribute15 => p_attribute15,
1039 p_attribute16 => p_attribute16,
1040 p_attribute17 => p_attribute17,
1041 p_attribute18 => p_attribute18,
1042 p_attribute19 => p_attribute19,
1043 p_attribute20 => p_attribute20,
1044 p_effective_start_date => l_effective_start_date,
1045 p_effective_end_date => l_effective_end_date,
1046 p_element_entry_id => l_element_entry_id,
1047 p_object_version_number => l_object_version_number,
1048 p_create_warning => l_create_warning);
1049
1050 -- calc jur code name
1051 pay_core_utils.get_leg_context_iv_name
1052 ('JURISDICTION_CODE',
1053 l_info.legcode,
1054 l_jc_name,
1055 l_found);
1056 if (l_found = FALSE) then
1057 l_jc_name := 'Jurisdiction';
1058 end if;
1059
1060
1061 -- set rr sparse leg_rule
1062 pay_core_utils.get_legislation_rule('RR_SPARSE',
1063 l_info.legcode,
1064 l_rule_mode,
1065 l_found
1066 );
1067 if (l_found = FALSE) then
1068 l_rule_mode := 'N';
1069 end if;
1070
1071 if upper(l_rule_mode)='Y'
1072 then
1073 -- Confirm Enabling Upgrade has been made by customer
1074 pay_core_utils.get_upgrade_status(l_info.busgrpid,
1075 'ENABLE_RR_SPARSE',
1076 l_status);
1077
1078 if upper(l_status)='N'
1079 then
1080 l_rule_mode := 'N';
1081 end if;
1082 end if;
1083
1084 if upper(l_rule_mode)='Y'
1085 then
1086 l_rr_sparse:=TRUE;
1087 else
1088 l_rr_sparse :=FALSE;
1089 end if;
1090 --
1091 pay_core_utils.get_upgrade_status(l_info.busgrpid,
1092 'RR_SPARSE_JC',
1093 l_status);
1094 --
1095 if upper(l_status)='Y'
1096 then
1097 l_rr_sparse_jc :=TRUE;
1098 else
1099 l_rr_sparse_jc :=FALSE;
1100 end if;
1101 --
1102 -- Get information related to the assignment.
1103 -- See comments for this function.
1104 l_info := get_asg_info(l_info
1105 ,p_assignment_id
1106 ,l_element_entry_id
1107 ,p_run_type_id
1108 ,p_tax_unit_id
1109 ,p_purge_mode
1110 );
1111 --
1112 -- create run result
1113 pay_run_result_pkg.create_run_result(
1114 p_element_entry_id => l_element_entry_id,
1115 p_session_date => l_info.effdate,
1116 p_business_group_id => l_info.busgrpid,
1117 p_jc_name => l_jc_name,
1118 p_rr_sparse => l_rr_sparse,
1119 p_rr_sparse_jc => l_rr_sparse_jc,
1120 p_asg_action_id => l_info.assactid,
1121 p_run_result_id => l_run_result_id);
1122 --
1123
1124 -- Set the creator information. Doing this here because
1125 -- the entry api doesn't allow us to on insert (which seems odd)
1126 -- and don't see the point of going all out to use the
1127 -- update API. May re-address when do 'proper' batch version.
1128 -- Also, balance_adj_cost_flag is not supported yet by API.
1129 update pay_element_entries_f pee
1130 set pee.creator_id = l_info.assactid,
1131 pee.creator_type = 'B',
1132 pee.balance_adj_cost_flag = p_balance_adj_cost_flag
1133 where pee.element_entry_id = l_element_entry_id
1134 and l_info.effdate between
1135 pee.effective_start_date and pee.effective_end_date;
1136
1137 set_lat_balances( p_assignment_id ,
1138 p_original_entry_id,
1139 l_element_entry_id,
1140 l_info.effdate,
1141 l_info.busgrpid,
1142 l_info.legcode,
1143 l_info.assactid,
1147
1144 l_info.action_type,
1145 l_run_result_id
1146 );
1148 --
1149 -- Create asg run balances based on the run result.
1150 --
1151 pay_balance_pkg.create_rr_asg_balances
1152 (p_run_result_id => l_run_result_id
1153 );
1154
1155 exception
1156 when others then
1157 rollback to adjust_balance;
1158 --
1159 -- Clear the global cache
1160 --
1161 purge_batch_info;
1162
1163 -- Output some final information about the error.
1164 hr_utility.trace('** exception information **');
1165 hr_utility.trace('l_info.batchid : ' || l_info.batchid);
1166 hr_utility.trace('l_info.effdate : ' || l_info.effdate);
1167 hr_utility.trace('l_info.assactid : ' || l_info.assactid);
1168 hr_utility.trace('l_info.payid : ' || l_info.payid);
1169 hr_utility.trace('l_info.batch_mode : ' || l_info.batch_mode);
1170 hr_utility.trace('l_effective_start_date : ' || l_effective_start_date);
1171 hr_utility.trace('l_effective_end_date : ' || l_effective_end_date);
1172 hr_utility.trace('l_element_entry_id : ' || l_element_entry_id);
1173 hr_utility.trace('l_object_version_number : ' || l_object_version_number);
1174
1175 raise;
1176
1177 end adjust_balance;
1178
1179 /*
1180 * Currently has the job of updating the payroll action table
1181 * to indicate that the balance adjustments have been processed.
1182 * Also performs commit.
1183 * Will eventually be the code that actually processes the
1184 * balance adjustments.
1185 */
1186 procedure process_batch
1187 (
1188 p_batch_id in number
1189 ) is
1190 --
1191 cursor get_rb_asg_actions(p_pact_id number)
1192 is
1193 select assignment_action_id
1194 from pay_assignment_actions
1195 where payroll_action_id = p_pact_id;
1196 --
1197 l_info info_r;
1198 l_timperid number;
1199 l_processed number;
1200 l_mode varchar2(30);
1201 l_proc varchar2(80):= ' pay_bal_adjust.process_batch';
1202 begin
1203 --
1204 -- Issue a savepoint
1205 --
1206 savepoint process_batch;
1207
1208 -- See if batch can be processed.
1209 l_info := get_batch_info(p_batch_id);
1210
1211 hr_utility.trace('batchid : ' || l_info.batchid);
1212 hr_utility.trace('effdate : ' || fnd_date.date_to_canonical(l_info.effdate));
1213 hr_utility.trace('busgrpid : ' || l_info.busgrpid);
1214 hr_utility.trace('mode : ' || l_info.batch_mode);
1215
1216 --
1217 -- Need to add balance adjustment support for pay_run_balances. 1st for
1218 -- assignment run balances then for group run balances.
1219 -- NOTE: While the only commit unit in balance adjustments is done in this
1220 -- procedure we can do both asg and group balances here. If a commit unit is
1221 -- ever put earlier in the bal adj process, the support for asg leve run
1222 -- balances may need moving to the balance adjust procedure(s).
1223 --
1224 -- Bug 3354765.
1225 -- The creation of asg run balances has been moved to be processed
1226 -- in adjust_balance.
1227
1228 -- for each_row in get_rb_asg_actions(l_info.batchid) loop
1229 -- pay_balance_pkg.create_all_asg_balances(each_row.assignment_action_id);
1230 -- end loop;
1231
1232 pay_balance_pkg.create_all_group_balances(l_info.batchid);
1233
1234 -- Obtain the time_period_id for this batch.
1235 select ptp.time_period_id
1236 into l_timperid
1237 from per_time_periods ptp
1238 where ptp.payroll_id = l_info.payid
1239 and l_info.effdate between
1240 ptp.start_date and ptp.end_date;
1241
1242 -- Stamp payroll action with the time_period_id and
1243 -- payroll_id whilst setting the statuses to success.
1244 update pay_payroll_actions pac
1245 set pac.action_status = 'C',
1246 pac.action_population_status = 'C',
1247 pac.time_period_id = l_timperid
1248 where pac.payroll_action_id = p_batch_id;
1249
1250 -- One item of information.
1251 select count(*)
1252 into l_processed
1253 from pay_assignment_actions act
1254 where act.payroll_action_id = l_info.batchid
1255 and act.action_status = 'C';
1256
1257 hr_utility.trace('Asgs processed : ' || l_processed);
1258
1259 purge_batch_info; -- any global information shut down.
1260 --
1261 -- Only commit if 'STANDARD' processing mode.
1262 if(l_info.batch_mode = 'STANDARD') then
1263 hr_utility.trace('STANDARD mode : COMMIT');
1264 commit;
1265 end if;
1266
1267 exception
1268 when others then
1269 --
1270 hr_utility.set_location(l_proc, 30);
1271 --
1272 rollback to process_batch;
1273 purge_batch_info;
1274 raise;
1275 end process_batch;
1276
1277 procedure rerun_batch
1278 (
1279 p_batch_id in number,
1280 p_busgrpid in number,
1281 p_effdate in date,
1282 p_legcode in varchar2,
1283 p_assignment_action_id in number
1284 ) is
1285 l_original_entry_id number;
1286 l_element_entry_id number;
1287 l_element_link_id number;
1288 l_assignment_id number;
1289 l_input_value_id_tbl hr_entry.number_table;
1293 l_jc_name varchar2(30);
1290 l_entry_value_tbl hr_entry.varchar2_table;
1291
1292 l_run_result_id number;
1294 l_rr_sparse boolean;
1295 l_rr_sparse_jc boolean;
1296 l_rule_mode varchar2(30);
1297 l_status varchar2(30);
1298 l_found boolean;
1299 begin
1300
1301 select assignment_id
1302 into l_assignment_id
1303 from pay_assignment_actions
1304 where assignment_action_id=p_assignment_action_id;
1305
1306 select ee.element_entry_id,
1307 ee.element_link_id,
1308 ee.original_entry_id
1309 into l_element_entry_id,
1310 l_element_link_id,
1311 l_original_entry_id
1312 from pay_element_entries_f ee
1313 where ee.creator_id = p_assignment_action_id
1314 and ee.assignment_id = l_assignment_id
1315 and ee.creator_type='B'
1316 and ee.entry_type = 'B'
1317 and p_effdate between ee.effective_start_date and ee.effective_end_date;
1318
1319
1320
1321
1322 -- calc jur code name
1323 pay_core_utils.get_leg_context_iv_name
1324 ('JURISDICTION_CODE',
1325 p_legcode,
1326 l_jc_name,
1327 l_found);
1328 if (l_found = FALSE) then
1329 l_jc_name := 'Jurisdiction';
1330 end if;
1331
1332
1333 -- set rr sparse leg_rule
1334 pay_core_utils.get_legislation_rule('RR_SPARSE',
1335 p_legcode,
1336 l_rule_mode,
1337 l_found
1338 );
1339 if (l_found = FALSE) then
1340 l_rule_mode := 'N';
1341 end if;
1342
1343 if upper(l_rule_mode)='Y'
1344 then
1345 -- Confirm Enabling Upgrade has been made by customer
1346 pay_core_utils.get_upgrade_status(p_busgrpid,
1347 'ENABLE_RR_SPARSE',
1348 l_status);
1349
1350 if upper(l_status)='N'
1351 then
1352 l_rule_mode := 'N';
1353 end if;
1354 end if;
1355
1356 if upper(l_rule_mode)='Y'
1357 then
1358 l_rr_sparse:=TRUE;
1359 else
1360 l_rr_sparse :=FALSE;
1361 end if;
1362 --
1363 pay_core_utils.get_upgrade_status(p_busgrpid,
1364 'RR_SPARSE_JC',
1365 l_status);
1366 --
1367 if upper(l_status)='Y'
1368 then
1369 l_rr_sparse_jc :=TRUE;
1370 else
1371 l_rr_sparse_jc :=FALSE;
1372 end if;
1373 --
1374 -- create run result
1375 pay_run_result_pkg.create_run_result(
1376 p_element_entry_id => l_element_entry_id,
1377 p_session_date => p_effdate,
1378 p_business_group_id => p_busgrpid,
1379 p_jc_name => l_jc_name,
1380 p_rr_sparse => l_rr_sparse,
1381 p_rr_sparse_jc => l_rr_sparse_jc,
1382 p_asg_action_id => p_assignment_action_id,
1383 p_run_result_id => l_run_result_id);
1384
1385 set_lat_balances ( l_assignment_id ,
1386 l_original_entry_id,
1387 l_element_entry_id,
1388 p_effdate,
1389 p_busgrpid,
1390 p_legcode,
1391 p_assignment_action_id,
1392 'B',
1393 l_run_result_id
1394 );
1395
1396 pay_balance_pkg.create_all_asg_balances(p_assignment_action_id);
1397
1398 end rerun_batch;
1399
1400 procedure create_ee
1401 (
1402 p_ele_type in number,
1403 p_busgrpid in number,
1404 p_effdate in date,
1405 p_legcode in varchar2,
1406 p_assignment_action_id in number,
1407 p_assignment_id in number,
1408 p_entry_id in out nocopy number,
1409 p_balcostflg in varchar2,
1410 p_costkflx_id in number
1411 )
1412 is
1413
1414
1415 l_start_date date;
1416 l_end_date date;
1417 l_link_id number;
1418 l_found boolean;
1419
1420 begin
1421
1422 -- see if entry already exists (i.e a rerun )
1423
1424 select element_entry_id
1425 into p_entry_id
1426 from pay_element_entries_f
1427 where assignment_id=p_assignment_id
1428 and entry_type='B'
1429 and creator_id=p_assignment_action_id
1430 and p_effdate between effective_start_date and effective_end_date;
1431
1432
1433 exception
1434 when others then
1435 -- get info needed to create ee
1436 l_link_id := hr_entry_api.get_link(p_assignment_id,p_ele_type,p_effdate);
1437
1438 /*
1439 select tp.start_date,tp.end_date
1440 into l_start_date,l_end_date
1441 from per_time_periods tp,per_all_assignments_f asg
1442 where asg.assignment_id = p_assignment_id
1443 and asg.payroll_id=tp.payroll_id
1444 and p_effdate between tp.start_date and tp.end_date
1445 and p_effdate between asg.effective_start_date and asg.effective_end_date;
1446 */
1447
1448
1449 l_start_date:=p_effdate;
1450 -- create ee
1451
1452 hr_entry_api.insert_element_entry
1453 (
1454 p_effective_start_date => l_start_date,
1458 p_element_link_id => l_link_id,
1455 p_effective_end_date => l_end_date,
1456 p_element_entry_id => p_entry_id,
1457 p_assignment_id => p_assignment_id,
1459 p_creator_type => 'B',
1460 p_entry_type => 'B',
1461 p_cost_allocation_keyflex_id =>p_costkflx_id,
1462 p_creator_id => p_assignment_action_id);
1463
1464 update pay_element_entries_f
1465 set balance_adj_cost_flag = p_balcostflg
1466 where element_entry_id = p_entry_id;
1467
1468 end create_ee;
1469
1470
1471 procedure get_context(p_ele_type in number,
1472 p_assignment_id in number,
1473 p_assignment_action_id in number,
1474 p_effdate in date,
1475 p_bus_grp in number,
1476 p_entry_id in number,
1477 p_inp_name varchar2,
1478 p_context_value out nocopy varchar2)
1479 is
1480 begin
1481
1482 if (p_inp_name = 'PAYROLL_ID') then
1483 select payroll_id
1484 into p_context_value
1485 from per_all_assignments_f
1486 where assignment_id=p_assignment_id
1487 and p_effdate between effective_start_date and effective_end_date;
1488 elsif (p_inp_name = 'ASSIGNMENT_ID') then
1489 p_context_value := p_assignment_id;
1490 elsif (p_inp_name = 'ASSIGNMENT_ACTION_ID') then
1491 p_context_value := p_assignment_action_id;
1492 elsif (p_inp_name = 'TAX_UNIT_ID') then
1493 select tax_unit_id
1494 into p_context_value
1495 from pay_assignment_actions
1496 where assignment_action_id = p_assignment_action_id;
1497 elsif (p_inp_name = 'ELEMENT_ENTRY_ID') then
1498 p_context_value := p_entry_id;
1499 elsif (p_inp_name = 'ELEMENT_TYPE_ID') then
1500 p_context_value := p_ele_type;
1501 elsif (p_inp_name = 'BUSINESS_GROUP_ID') then
1502 p_context_value := p_bus_grp;
1503 elsif (p_inp_name = 'PAYROLL_ACTION_ID') then
1504 select payroll_action_id
1505 into p_context_value
1506 from pay_assignment_actions
1507 where assignment_action_id =p_assignment_action_id;
1508 elsif (p_inp_name = 'DATE_EARNED') then
1509 p_context_value := fnd_date.date_to_canonical(p_effdate);
1510 end if;
1511
1512 end get_context;
1513
1514 procedure run_formula( p_ele_type in number,
1515 p_busgrpid in number,
1516 p_effdate in date,
1517 p_legcode in varchar2,
1518 p_assignment_action_id in number,
1519 p_assignment_id in number,
1520 p_entry_id in number,
1521 p_run_result_id in number)
1522 is
1523 cursor form_outputs (p_ele_type number,p_effdate date,
1524 p_formula_id number,p_out_name varchar2)
1525 is
1526 select input_value_id,frr.result_rule_type,frr.severity_level
1527 from pay_formula_result_rules_f frr,
1528 pay_status_processing_rules_f spr
1529 where spr.element_type_id= p_ele_type
1530 and spr.formula_id = p_formula_id
1531 and spr.status_processing_rule_id = frr.status_processing_rule_id
1532 and upper(frr.result_name) = upper(p_out_name)
1533 and p_effdate between spr.effective_start_date and spr.effective_end_date
1534 and p_effdate between frr.effective_start_date and frr.effective_end_date;
1535
1536 v_inputs ff_exec.inputs_t;
1537 v_outputs ff_exec.outputs_t;
1538 l_formula_id number;
1539 l_formula_name varchar2(80);
1540 inp_name varchar2(240);
1541 out_name varchar2(240);
1542 out_value varchar2(255);
1543 inp_value varchar2(240);
1544 l_jc_name varchar2(30);
1545 l_rr_sparse boolean;
1546 l_rr_sparse_jc boolean;
1547 l_rule_mode varchar2(30);
1548 l_status varchar2(30);
1549 l_found boolean;
1550
1551
1552
1553 begin
1554
1555 -- calc jur code name
1556 pay_core_utils.get_leg_context_iv_name
1557 ('JURISDICTION_CODE',
1558 p_legcode,
1559 l_jc_name,
1560 l_found);
1561 if (l_found = FALSE) then
1562 l_jc_name := 'Jurisdiction';
1563 end if;
1564
1565 -- set rr sparse leg_rule
1566 pay_core_utils.get_legislation_rule('RR_SPARSE',
1567 p_legcode,
1568 l_rule_mode,
1569 l_found
1570 );
1571 if (l_found = FALSE) then
1572 l_rule_mode := 'N';
1573 end if;
1574
1575 if upper(l_rule_mode)='Y'
1576 then
1577 -- Confirm Enabling Upgrade has been made by customer
1578 pay_core_utils.get_upgrade_status(p_busgrpid,
1579 'ENABLE_RR_SPARSE',
1580 l_status);
1581
1582 if upper(l_status)='N'
1583 then
1584 l_rule_mode := 'N';
1585 end if;
1586 end if;
1587
1588 if upper(l_rule_mode)='Y'
1589 then
1590 l_rr_sparse:=TRUE;
1591 else
1592 l_rr_sparse :=FALSE;
1593 end if;
1594 --
1595 pay_core_utils.get_upgrade_status(p_busgrpid,
1596 'RR_SPARSE_JC',
1597 l_status);
1598 --
1599 if upper(l_status)='Y'
1600 then
1604 end if;
1601 l_rr_sparse_jc :=TRUE;
1602 else
1603 l_rr_sparse_jc :=FALSE;
1605 --
1606 -- get formula id
1607 select formula_id
1608 into l_formula_id
1609 from pay_status_processing_rules_f
1610 where element_type_id =p_ele_type
1611 and processing_rule='B'
1612 and p_effdate between effective_start_date and effective_end_date;
1613
1614 hr_utility.trace('l_formula_id : ' || l_formula_id);
1615
1616 -- init formula
1617 ff_exec.init_formula(l_formula_id, p_effdate, v_inputs, v_outputs);
1618
1619 -- set inputs
1620 if(v_inputs.count >= 1) then
1621 --
1622 -- Set up the inputs and contexts to formula.
1623 for i in v_inputs.first..v_inputs.last loop
1624 inp_name:=v_inputs(i).name;
1625 -- see if equivalent entry value
1626 hr_utility.trace('input name : ' || inp_name);
1627
1628 begin
1629 select ee.screen_entry_value
1630 into inp_value
1631 from pay_element_entry_values_f ee,
1632 pay_input_values_f iv
1633 where ee.element_entry_id=p_entry_id
1634 and ee.input_value_id=iv.input_value_id
1635 and upper(iv.name)=upper(inp_name)
1636 and p_effdate between iv.effective_start_date and iv.effective_end_date
1637 and p_effdate between ee.effective_start_date and ee.effective_end_date;
1638
1639 v_inputs(i).value:=inp_value;
1640 hr_utility.trace('input value : ' || inp_value);
1641
1642 exception
1643 when no_data_found then
1644 -- see if context
1645 get_context(p_ele_type, p_assignment_id,
1646 p_assignment_action_id, p_effdate, p_busgrpid ,
1647 p_entry_id,inp_name,v_inputs(i).value);
1648 end;
1649
1650 end loop;
1651 end if;
1652
1653
1654 -- run formula
1655 ff_exec.run_formula(v_inputs, v_outputs);
1656
1657
1658 -- process results
1659 for i in v_outputs.first..v_outputs.last loop
1660
1661 out_name:=v_outputs(i).name;
1662 out_value:=v_outputs(i).value;
1663
1664 hr_utility.trace('output name : ' || out_name);
1665 hr_utility.trace('output value : ' || out_value);
1666
1667 for outputs in form_outputs(p_ele_type,p_effdate,l_formula_id,out_name)
1668 loop
1669 hr_utility.trace('input_value_id: ' || to_char(outputs.input_value_id));
1670 hr_utility.trace('result_rule_type: ' || outputs.result_rule_type);
1671 hr_utility.trace('severity level: ' || outputs.severity_level);
1672
1673
1674 if (outputs.result_rule_type='M' and out_value is not NULL)
1675 then
1676 --
1677 /* Message Result */
1678 --
1679 pay_core_utils.push_message(801,NULL,out_value,outputs.severity_level);
1680 if (outputs.severity_level='F')
1681 then
1682 -- error asgnment
1683 seLect formula_name
1684 into l_formula_name
1685 from ff_formulas_f
1686 where formula_id = l_formula_id
1687 and p_effdate between effective_start_date and effective_end_date;
1688
1689 hr_utility.set_message(801, 'HR_51120_HRPROC_ERR_OCC_ON_FML');
1690 hr_utility.set_message_token('FMLANAME',l_formula_name);
1691 hr_utility.raise_error;
1692 end if;
1693 --
1694 elsif (outputs.result_rule_type = 'D' and out_value is not null ) then
1695 --
1696 /* Direct Result */
1697 pay_run_result_pkg.maintain_rr_value(
1698 p_run_result_id => p_run_result_id,
1699 p_session_date => p_effdate,
1700 p_input_value_id => outputs.input_value_id,
1701 p_value => out_value,
1702 p_formula_result_flag => 'Y',
1703 p_jc_name => l_jc_name,
1704 p_rr_sparse => l_rr_sparse,
1705 p_rr_sparse_jc => l_rr_sparse_jc,
1706 p_mode => 'DIRECT'
1707 );
1708 --
1709 elsif (outputs.result_rule_type = 'I' and out_value is not null ) then
1710 --
1711 /* Indirect Result */
1712 --
1713 declare
1714 l_ind_ele_type_id pay_input_values_f.element_type_id%type;
1715 l_rr_id pay_run_results.run_result_id%type;
1716 begin
1717 --
1718 select element_type_id
1719 into l_ind_ele_type_id
1720 from pay_input_values_f
1721 where input_value_id = outputs.input_value_id
1722 and p_effdate between effective_start_date
1723 and effective_end_date;
1724 --
1725 begin
1726 --
1727 select prr.run_result_id
1728 into l_rr_id
1729 from pay_run_results prr
1730 where element_type_id = l_ind_ele_type_id
1731 and source_id = p_entry_id
1732 and source_type = 'I'
1733 and assignment_action_id = p_assignment_action_id;
1734 --
1735 pay_run_result_pkg.maintain_rr_value(
1736 p_run_result_id => l_rr_id,
1737 p_session_date => p_effdate,
1741 p_jc_name => l_jc_name,
1738 p_input_value_id => outputs.input_value_id,
1739 p_value => out_value,
1740 p_formula_result_flag => 'Y',
1742 p_rr_sparse => l_rr_sparse,
1743 p_rr_sparse_jc => l_rr_sparse_jc,
1744 p_mode => 'INDIRECT'
1745 );
1746 --
1747 exception
1748 when no_data_found then
1749 --
1750 pay_run_result_pkg.create_indirect_rr(
1751 p_element_type_id => l_ind_ele_type_id,
1752 p_run_result_id => p_run_result_id,
1753 p_session_date => p_effdate,
1754 p_business_group_id => p_busgrpid,
1755 p_jc_name => l_jc_name,
1756 p_rr_sparse => l_rr_sparse,
1757 p_rr_sparse_jc => l_rr_sparse_jc,
1758 p_asg_action_id => p_assignment_action_id,
1759 p_ind_run_result_id => l_rr_id
1760 );
1761 --
1762 pay_run_result_pkg.maintain_rr_value(
1763 p_run_result_id => l_rr_id,
1764 p_session_date => p_effdate,
1765 p_input_value_id => outputs.input_value_id,
1766 p_value => out_value,
1767 p_formula_result_flag => 'Y',
1768 p_jc_name => l_jc_name,
1769 p_rr_sparse => l_rr_sparse,
1770 p_rr_sparse_jc => l_rr_sparse_jc,
1771 p_mode => 'INDIRECT'
1772 );
1773 --
1774 end;
1775 end;
1776 --
1777 end if;
1778 end loop;
1779 end loop;
1780
1781 end run_formula;
1782
1783 procedure process_bal_adj
1784 (
1785 p_ele_type in number,
1786 p_busgrpid in number,
1787 p_effdate in date,
1788 p_legcode in varchar2,
1789 p_assignment_action_id in number,
1790 p_assignment_id in number,
1791 p_balcostflg in varchar2,
1792 p_costkflx_id in number
1793 )
1794 is
1795 l_entry_id number;
1796 l_jc_name varchar2(30);
1797 l_rr_sparse boolean;
1798 l_rr_sparse_jc boolean;
1799 l_rule_mode varchar2(30);
1800 l_status varchar2(30);
1801 l_found boolean;
1802 l_run_result_id pay_run_results.run_result_id%type;
1803 --
1804 -- cursor to retrieve the generated run results
1805 --
1806 cursor csr_run_results(p_assact_id number
1807 ,p_eentry_id number
1808 )
1809 is
1810 select rr.run_result_id
1811 from pay_run_results rr
1812 where
1813 rr.assignment_action_id = p_assact_id
1814 and rr.source_id = p_eentry_id
1815 and rr.source_type in ('E', 'I')
1816 ;
1817
1818 begin
1819
1820
1821 -- create ele_entry
1822 create_ee(p_ele_type,p_busgrpid,p_effdate,p_legcode,p_assignment_action_id,p_assignment_id,l_entry_id,p_balcostflg,p_costkflx_id);
1823 hr_utility.trace('l_entry_id : ' || l_entry_id);
1824
1825 -- calc jur code name
1826 pay_core_utils.get_leg_context_iv_name
1827 ('JURISDICTION_CODE',
1828 p_legcode,
1829 l_jc_name,
1830 l_found);
1831 if (l_found = FALSE) then
1832 l_jc_name := 'Jurisdiction';
1833 end if;
1834
1835
1836 -- set rr sparse leg_rule
1837 pay_core_utils.get_legislation_rule('RR_SPARSE',
1838 p_legcode,
1839 l_rule_mode,
1840 l_found
1841 );
1842 if (l_found = FALSE) then
1843 l_rule_mode := 'N';
1844 end if;
1845
1846 if upper(l_rule_mode)='Y'
1847 then
1848 -- Confirm Enabling Upgrade has been made by customer
1849 pay_core_utils.get_upgrade_status(p_busgrpid,
1850 'ENABLE_RR_SPARSE',
1851 l_status);
1852
1853 if upper(l_status)='N'
1854 then
1855 l_rule_mode := 'N';
1856 end if;
1857 end if;
1858
1859 if upper(l_rule_mode)='Y'
1860 then
1861 l_rr_sparse:=TRUE;
1862 else
1863 l_rr_sparse :=FALSE;
1864 end if;
1865 --
1866 pay_core_utils.get_upgrade_status(p_busgrpid,
1867 'RR_SPARSE_JC',
1868 l_status);
1869 --
1870 if upper(l_status)='Y'
1871 then
1872 l_rr_sparse_jc :=TRUE;
1873 else
1874 l_rr_sparse_jc :=FALSE;
1875 end if;
1876 --
1877 -- create run result
1878 pay_run_result_pkg.create_run_result(
1879 p_element_entry_id => l_entry_id,
1880 p_session_date => p_effdate,
1881 p_business_group_id => p_busgrpid,
1882 p_jc_name => l_jc_name,
1883 p_rr_sparse => l_rr_sparse,
1884 p_rr_sparse_jc => l_rr_sparse_jc,
1885 p_asg_action_id => p_assignment_action_id,
1886 p_run_result_id => l_run_result_id);
1887 --
1888 --run formula
1889 run_formula(p_ele_type,
1890 p_busgrpid,
1891 p_effdate,
1892 p_legcode,
1893 p_assignment_action_id,
1894 p_assignment_id,
1895 l_entry_id,
1896 l_run_result_id);
1897
1898 -- balnces
1899 for rr_rec in csr_run_results
1900 (p_assignment_action_id
1901 ,l_entry_id)
1902 loop
1903 set_lat_balances
1904 (p_assignment_id => p_assignment_id
1905 ,p_original_entry_id => null
1906 ,p_element_entry_id => l_entry_id
1907 ,p_effdate => p_effdate
1908 ,p_busgrpid => p_busgrpid
1909 ,p_legcode => p_legcode
1910 ,p_assactid => p_assignment_action_id
1911 ,p_action_type => 'B'
1912 ,p_run_result_id => rr_rec.run_result_id
1913 );
1914 end loop;
1915
1916 --
1917 -- Bug 3211015. Added the call to create_all_asg_balances instead of
1918 -- calling it in set_lat_balances.
1919 --
1920 pay_balance_pkg.create_all_asg_balances(p_assignment_action_id);
1921
1922 end process_bal_adj;
1923
1924 end pay_bal_adjust;