1 PACKAGE BODY pay_us_soe_balances_pkg AS
2 /* $Header: pyussoeb.pkb 120.9 2008/04/03 17:09:21 sneelapa noship $ */
3 /*
4 ******************************************************************
5 * *
6 * Copyright (C) 1993 Oracle Corporation. *
7 * All rights reserved. *
8 * *
9 * This material has been provided pursuant to an agreement *
10 * containing restrictions on its use. The material is also *
11 * protected by copyright law. No part of this material may *
12 * be copied or distributed, transmitted or transcribed, in *
13 * any form or by any means, electronic, mechanical, magnetic, *
14 * manual, or otherwise, or disclosed to third parties without *
15 * the express written permission of Oracle Corporation, *
16 * 500 Oracle Parkway, Redwood City, CA, 94065. *
17 * *
18 ******************************************************************
19
20 Name : pay_us_soe_balances_pkg
21
22 Description : The package has all the common packages used in
23 US Payroll.
24
25 Change List
26 -----------
27 Date Name Vers Bug No Description
28 ----------- ---------- ------ ------- ------------------------------------
29 07-NOV-2003 kaverma 115.0 Created.
30 07-NOV-2003 kaverma 115.1 2816363 Corrected populate_earn_balance
31 when run balances are not valid
32 10-NOV-2003 kaverma 115.2 3138331 updated code to consider the termination
33 of assignments for multiple assignments
34 12-NOV-2003 kaverma 115.3 3250653 Corrected populate_local_balance to fetch
35 school dst correcly. Also modified
36 populate_actions_ids.
37 17-NOV-2003 kaverma 115.4 3257504 corrected cursor c_get_max_action_id
38 21-NOV-2003 kaverma 115.5 3270646 Added exists check at places where plsql
39 table is accessed.
40 03-DEC-2003 kaverma 115.6 3275404 Modified populate_actions_ids and
41 populate_earn_bal. Removed
42 get_phbr_plsql_table,get_earn_plsql_table
43 and get_dedn_plsql_table. Moved the logic
44 to corres. plsql populate procedures and
45 passing the plsql table as out parameter.
46 06-JAN-2004 tclewis 115.7 2845480 Modified populate_state_balance.
47 added code to reverse the sign for
48 state EIC balances. Added code to
49 return STEIC balance as the fed
50 procedure does.
51 04-FEB-2004 ardsouza 115.9 3412605 Replaced table PAY_US_CITY_SCHOOL_DSTS
52 by the view PAY_US_SCHOOL_DSTS to handle
53 county level school districts of Kentucky.
54 26-FEB-2004 sdahiya 115.10 3464757 Modified cursor c_get_max_action_id
55 to use nvl(date_earned, effective_date)
56 instead of effective_date. Created a
57 branched version (115.6.11510.3) of
58 this file too.
59 31-MAY-2004 kaverma 115.11 3620872 Modified populate_dedn_balances to use
60 dedution run balance/run result view
61 10-JUN-2004 kaverma 115.12 3620872 Added Rule hint to earnings and deduction
62 queries for quick customer performance fix.
63 22-JUN-2004 kaverma 115.13 3620872 Changed the logic to fetch balances for
64 earnings and deductions.
65 06-SEP-2005 rmonge 115.14 3837653 Added a order by clause to the following
66 cursors in the
67 c_get_pay_rb_elements,
68 c_get_pre_earn_run_rb,
69 c_get_pre_earn_ytd_rb,
70 c_get_more_earn_elements
71 The order by clause matches the
72 Q_Earnigns order by clause
73 in order to retrieve the elements by
74 Earnings by reporting_name,
75 classification and processing_priority
76 24-NOV-2005 kvsankar 115.15 4004796 Modified the cursors
77 c_get_more_earn_elements
78 and c_get_more_dedn_elements
79 to correct the Date
80 Effective Joins present in them.
81 02-DEC-2004 ahanda 115.16 4004796 Changed Earnings and Deductions query to
82 not use view if Balances are not valid
83 instrad check run results.
84 This will ensure that indirect element
85 will show up if balances are not valid.
86 21-FEB-2005 sackumar 115.17 3334690 Remove a condition in populate earn balance when
87 balance status is invalid (<>'Y') which restrict to repeat the code
88 so current values is not sumed up in case of
89 Map enabled Multi Assignment
90
91 13-JAN-2006 rmonge 4883110 Changed the order by clause for the Q_Earnings again
92 to fix problem with customer not able to see all
93 Earning Elements displayed or printed when the
94 number of earning Elements is more than 8.
95 The new order by will display any earning elements
96 first regardless of their priority.
97 23-MAR-2006 saurgupt 115.20 4966938 Changed the cursor c_get_pay_assignment_dtl. Add ppa.effective_date
98 in the select statement.
99 Write the new queries for cursors c_get_earn_elements and
100 c_get_dedn_elements. Removed the table pay_element_entries_f.
101 Now pay_assignment_actions and pay_payroll_actions are used.
102 Also, now date_paid is used in place of date_earned. This resolves
103 the boundary issue if date earned and date paid are in different
104 years.
105 16-MAY-2006 sodhingr 115.20 5228817 changed cursor c_get_dedn_elements and c_get_earn_elements
106 to refer to ppa.effective_Date
107 20-JUN-2006 sjawid 115.22 5210560 Added a condition to the c_get_assignments
108 to avoid the overstated values in prepay soe
109 when person with person type both employee and applicant.
110 20-JUN-2006 sjawid 115.22 4743188 Changed Order by clause for the c_get_dedn_elements
111 in order to sort with element name.
112 Modified the logic in populate_earn_bal and populate_dedn_balance
113 in order to load p_earn_tab and p_dedn_tab tables in sorting order
114 when balances are invalid.
115 31-JUL-2006 saurgupt 115.23 5332346 Modified the procedure populate_actions_ids. Add p_balance_status to
116 check balance status. Removed cursor c_get_all_aaid_for_mast as it
117 does not work for 11.0 data. Instead added two new cursors,
118 c_get_all_aaid_for_mast_rb and c_get_all_aaid_for_mast_rr.
119 Modified cursor c_get_dedn_elements and c_get_earn_elements. Removed
120 the condition and paa1.source_action_id is not null.
121 02-AUG-2006 saurgupt 115.24 5332346 Reverse the changes done in populate_actions_ids. Only modified the
122 cursor c_get_all_aaid_for_mast by removing pay_run_types and instead
123 added pay_payroll_actions.
124 06-FEB-2007 kvsankar 115.25 5865549 Initialized the value of l_asg_action_id
125 to NULL before using it.
126 03-MAR-2008 sneelapa 115.26 6636807 Modified Procedure populate_action_ids
127 CURSOR c_get_max_action_id
128 Added 'V' in WHERE Condition for
129 pay_payroll_actions.action_type column
130 *****************************************************************************/
131
132 l_package VARCHAR2(30) := 'pay_us_soe_balances_pkg.';
133
134
135 /*****************************************************************************
136 Name : populate_actions_ids
137 Purpose : This procedure populates a PL/SQLTable with the maximum action id
138 and the run action ids by prepayment.
139 Note : The procedure will be called by the SOE in case of prepayment.
140 The Run values are the values of the locked run actions and
141 the YTD value is the value of the corresponding element given
142 by the maximum run action ID.
143 IF Multiple Assignments is checked for Payroll
144 Get maximum run actions for all multiple assignments
145 Else
146 Get maximum run action for the assignment in for SOE is viewed
147 *****************************************************************************/
148 PROCEDURE populate_actions_ids(p_master_action_id in number,
149 p_assignment_id in number,
150 p_period_end_date in date,
151 p_asg_multi_flag in varchar2,
152 p_period_start_date in date)
153 IS
154
155 CURSOR c_get_all_aaid_for_mast(cp_run_action_id number)
156 IS
157 select assact.assignment_action_id,
158 assact.assignment_id
159 from pay_action_interlocks intlk
160 ,pay_assignment_actions assact
161 ,pay_payroll_actions ppa
162 where intlk.locking_action_id = cp_run_action_id
163 and intlk.locked_action_id = assact.assignment_action_id
164 and assact.payroll_action_id = ppa.payroll_action_id
165 and ((ppa.run_type_id is null and assact.source_action_id is null) or
166 (ppa.run_type_id is not null and assact.source_action_id is not null))
167 and not exists
168 (select null
169 from pay_payroll_actions rpact
170 , pay_assignment_actions rassact
171 , pay_action_interlocks rintlk
172 where assact.assignment_action_id = rintlk.locked_action_id
173 and rintlk.locking_action_id = rassact.assignment_action_id
174 and rpact.payroll_action_id = rassact.payroll_action_id
175 and rpact.action_type = 'V')
176 order by assact.assignment_action_id;
177 /*
178 select assact.assignment_action_id,
179 assact.assignment_id,
180 prt.shortname,
181 prt.run_type_id
182 from pay_action_interlocks intlk
183 ,pay_assignment_actions assact
184 ,pay_run_types_f prt
185 where intlk.locking_action_id = cp_run_action_id
186 and intlk.locked_action_id = assact.assignment_action_id
187 and assact.source_action_id is not null
188 and assact.run_type_id(+) = prt.run_type_id
189 and not exists
190 (select null
191 from pay_payroll_actions rpact
192 , pay_assignment_actions rassact
193 , pay_action_interlocks rintlk
194 where assact.assignment_action_id = rintlk.locked_action_id
195 and rintlk.locking_action_id = rassact.assignment_action_id
196 and rpact.payroll_action_id = rassact.payroll_action_id
197 and rpact.action_type = 'V')
198 order by assact.assignment_action_id;
199 */
200
201 -- Cursor to get all the assignments for the person effective in
202 -- the current year for which SOE is being viewed
203 CURSOR c_get_assignments (c_assignment_id number,c_period_end_date date)
204 IS
205 select distinct paa1.assignment_id
206 from per_assignments_f paa,
207 per_assignments_f paa1,
208 per_people_f ppa
209 where paa.assignment_id = c_assignment_id
210 and paa.person_id = ppa.person_id
211 and paa1.person_id = ppa.person_id
212 and paa1.ASSIGNMENT_TYPE <>'A' -- bug5210560
213 and paa1.effective_end_date >= to_date('01/01/'||to_char(c_period_end_date,'YYYY'),'DD/MM/YYYY')
214 order by paa1.assignment_id;
215
216 -- Cursor to get the maximum Run Action for the Assignment till the current
217 -- pay period end date
218 CURSOR c_get_max_action_id (c_assignment_id number,
219 c_period_end_date date,
220 c_period_start_date date)
221 IS
222 select assact.assignment_action_id -- Bug 3257504
223 from pay_assignment_actions assact,
224 pay_payroll_actions pac
225 where assact.assignment_id = c_assignment_id
226 and assact.payroll_action_id = pac.payroll_action_id
227 and pac.action_type in ('R','Q','B','I','V')
228 -- 'V' action_type is added by sneelapa for bug 6636807
229 and nvl(pac.date_earned, pac.effective_date) <= c_period_end_date -- Bug 3464757
230 and nvl(pac.date_earned, pac.effective_date) >= trunc(c_period_start_date,'Y') -- Bug 3275404, 3464757
231 and not exists
232 (select null
233 from pay_payroll_actions rpact
234 , pay_assignment_actions rassact
235 , pay_action_interlocks rintlk
236 where assact.assignment_action_id = rintlk.locked_action_id
237 and rintlk.locking_action_id = rassact.assignment_action_id
238 and rpact.payroll_action_id = rassact.payroll_action_id
239 and rpact.action_type = 'V')
240 order by assact.assignment_action_id desc;
241
242
243 i number := 0;
244 j number := 0;
245 l_assignment_id number;
246 l_asg_action_id number;
247 c_aaid number;
248 c_asg_id number;
249 c_run_type pay_run_types_f.shortname%type;
250 c_run_type_id number;
251 l_aaid number;
252 l_procedure varchar2(20) ;
253
254 BEGIN
255 l_procedure := 'populate_actions_ids';
256 hr_utility.set_location(l_package||l_procedure, 10);
257
258 -- delete the plsql tables that stores max run action ids and
259 -- locked run action ids by prepayment
260 master_actions_tab.delete;
261 run_actions_tab.delete;
262
263 hr_utility.set_location(l_package||l_procedure, 20);
264
265 -- Check if the Payroll is Multiple Assignments checked or not
266 -- If yes we have to display the Person level balance for YTD field
267 -- If no we will display the assignment level YTD values
268 IF p_asg_multi_flag = 'Y' THEN
269 -- New Logic to get YTD Values
270 OPEN c_get_assignments (p_assignment_id , p_period_end_date);
271
272 LOOP
273 FETCH c_get_assignments INTO l_assignment_id;
274 EXIT WHEN c_get_assignments%NOTFOUND;
275
276 -- Bug 5865549
277 -- Set l_asg_action_id before using it
278 l_asg_action_id := NULL;
279 OPEN c_get_max_action_id(l_assignment_id , p_period_end_date,p_period_start_date);
280 FETCH c_get_max_action_id INTO l_asg_action_id;
281 CLOSE c_get_max_action_id ;
282 IF l_asg_action_id IS NOT NULL THEN
283 master_actions_tab(j).aaid:=l_asg_action_id;
284 j := j + 1;
285 END IF;
286 END LOOP;
287
288 CLOSE c_get_assignments;
289 ELSE
290 OPEN c_get_max_action_id(p_assignment_id , p_period_end_date, p_period_start_date);
291 FETCH c_get_max_action_id INTO master_actions_tab(j).aaid;
292 CLOSE c_get_max_action_id ;
293
294 END IF; -- p_asg_multi_flag = 'Y'
295 hr_utility.set_location(l_package||l_procedure, 30);
296
297 -- get all locked child actions
298 hr_utility.set_location(l_package||l_procedure, 40);
299
300 OPEN c_get_all_aaid_for_mast(p_master_action_id);
301 LOOP
302 FETCH c_get_all_aaid_for_mast
303 INTO c_aaid, c_asg_id;
304 --INTO c_aaid, c_asg_id, c_run_type, c_run_type_id; -- Bug 5332346
305 EXIT WHEN c_get_all_aaid_for_mast%NOTFOUND;
306 i := i + 1;
307 run_actions_tab(i).asg_id := c_asg_id;
308 run_actions_tab(i).aaid := c_aaid;
309 --run_actions_tab(i).run_type := c_run_type; -- Bug 5332346
310 --run_actions_tab(i).run_type_id := c_run_type_id; -- Bug 5332346
311 END LOOP;
312 CLOSE c_get_all_aaid_for_mast;
313
314 EXCEPTION
315 WHEN others THEN
316 hr_utility.set_location(l_package||l_procedure,50);
317 raise_application_error(-20101, 'Error in '|| l_package||l_procedure);
318 raise;
319
320 END populate_actions_ids;
321
322
323
324 /******************************************************************************
325 * Name : get_defined_bal
326 * Purpose : This function is used to get the defined balance ids based on
327 * balance type id and balance dimension id.
328 ******************************************************************************/
329 FUNCTION get_defined_bal (p_bal_id in number
330 ,p_dim_id in number
331 )
332 RETURN number IS
333 v_defbal_id number;
334 l_function varchar2(16);
335 BEGIN
336 l_function :='get_defined_bal';
337 hr_utility.set_location(l_package||l_function, 10);
338
339 SELECT defined_balance_id
340 INTO v_defbal_id
341 FROM pay_defined_balances pdb
342 WHERE pdb.balance_type_id = p_bal_id
343 AND pdb.balance_dimension_id = p_dim_id
344 AND nvl(pdb.legislation_code,'US') = 'US';
345
346 hr_utility.set_location(l_package||l_function, 20);
347
348 RETURN v_defbal_id;
349
350 EXCEPTION WHEN NO_DATA_FOUND THEN
351 hr_utility.set_location(l_package||l_function, 30);
352 RETURN -1;
353
354 END;
355
356
357
358 /*****************************************************************************
359 Name : populate_earn_bal
360 Purpose : This procedure populates a PL/SQL table with all the earnings elements
361 for SOE form.
362 NOTE : plsql tables p_earn_tab will be passed to the
363 SOE form for display
364
365 Bug 3275404 : Added p_earn_tab as out parameter and removed use of l_earn_info
366 Also removed get_position_id procedure. The rounding issue (Bug 2816363)
367 will be fixed by adding the difference to one of the same reporting name
368 entries in the Rate Details Block in the Form.
369
370 Bug 3837653 : Added an order by clause to all cursor in the procedure that retrieve
371 rmonge the earnings elements. The new order by clause will
372 order the elements according to Reporting_name ,Classification,
373 and Processing Priority.
374 Bug 4883110. Changed the order by clause again to fix problem with customer
375 not able to see all the Earning Elements due to processing
376 priority. The new change allow the Earnings Elements to
377 be printed first regardless of the priority.
378 *****************************************************************************/
379 PROCEDURE populate_earn_bal(p_assignment_action_id in number,
380 p_balance_status in varchar2,
381 p_action_type in varchar2,
382 p_earn_tab out nocopy earn)
383 IS
384
385 -- Cursor to fetch the earnings balances when all earnings balances are valid
386 CURSOR c_get_pay_rb_elements (c_run_assact_id number)
387 IS
388 select ytd_val
389 ,reporting_name_alt
390 ,run_val
391 ,hours_run_val
392 ,element_type_id
393 from pay_us_earnings_amounts_rbr_v
394 where assignment_action_id = c_run_assact_id
395 order by decode(reporting_name_alt, 'Regular Pay', 0,
396 'Regular Salary',0,
397 'Regular Wages',0,
398 'Time Entry Wages',1),
399 decode(classification_name,
400 'Earnings',1,
401 'Alien/Expat Earnings',2,
402 'Supplemental Earnings', 3,
403 'Inputed Earnings',4,
404 'Tax Credit',5,
405 'Non-payroll Payments',6),
406 processing_priority;
407
408 -- Cursor to get the run earnings amounts when the balances are valid
409 CURSOR c_get_pre_earn_run_rb(cp_run_action_id number)
410 IS
411 select reporting_name_alt
412 ,run_val
413 ,hours_run_val
414 ,element_type_id
415 from pay_us_earnings_amounts_rbr_v pt
416 where pt.assignment_action_id = cp_run_action_id
417 order by decode( reporting_name_alt, 'Regular Pay', 0,
418 'Regular Salary',0,
419 'Regular Wages',0,
420 'Time Entry Wages',1),
421 decode(classification_name,
422 'Earnings',1,
423 'Alien/Expat Earnings',2,
424 'Supplemental Earnings', 3,
425 'Inputed Earnings',4,
426 'Tax Credit',5,
427 'Non-payroll Payments',6),
428 processing_priority;
429
430 -- Cursor to get the ytd earnings amounts when the balances are valid
431 CURSOR c_get_pre_earn_ytd_rb(cp_master_action_id number) IS
432 select ytd_val
433 ,pt.reporting_name_alt
434 ,element_type_id
435 from pay_us_earnings_amounts_rbr_v pt
436 where pt.assignment_action_id = cp_master_action_id
437 order by decode( reporting_name_alt, 'Regular Pay', 0,
438 'Regular Salary',0,
439 'Regular Wages',0,
440 'Time Entry Wages',1),
441 decode(classification_name,
442 'Earnings',1,
443 'Alien/Expat Earnings',2,
444 'Supplemental Earnings', 3,
445 'Inputed Earnings',4,
446 'Tax Credit',5,
447 'Non-payroll Payments',6),
448 processing_priority;
449
450 -- Cursor to get balance dimension for run and ytd
451 CURSOR c_get_dimension_ids(cp_database_item_suffix varchar2) IS
452 select balance_dimension_id
453 from pay_balance_dimensions
454 where legislation_code = 'US'
455 and database_item_suffix = cp_database_item_suffix;
456
457 CURSOR c_get_pay_assignment_dtl(cp_assignment_action_id number) IS
458 select paa.assignment_id,
459 ppa.date_earned,
460 ppa.effective_date
461 from pay_assignment_actions paa,
462 pay_payroll_actions ppa
463 where paa.assignment_action_id = cp_assignment_action_id
464 and paa.payroll_action_id = ppa.payroll_action_id;
465
466 -- Cursor to get elements processed from the element entries.
467 -- rmonge Added a order by clause to make sure the earnings are retrieved in
468 -- order by Processing priority and type of EArnings.
469 -- Bug 4004796.Modified the Date effective joins with
470 -- pay_element_entries_f and pay_element_types_f
471
472 -- Bug 4966938
473 CURSOR c_get_earn_elements(cp_date_paid date,
474 cp_assignment_action_id number) IS
475 select distinct pet.element_type_id,
476 nvl(pet.reporting_name, pet.element_name),
477 pet.element_information10,
478 pet.element_information12,
479 pet.business_group_id,
480 pec.classification_name,
481 pet.processing_priority
482 from pay_assignment_actions paa ,
483 pay_assignment_actions paa1 ,
484 pay_payroll_actions ppa ,
485 pay_run_results prr ,
486 pay_element_types_f pet ,
487 pay_element_classifications pec
488 where paa.assignment_action_id = cp_assignment_action_id
489 and paa1.assignment_id = paa.assignment_id
490 -- and paa1.source_action_id is not null --for bug 5332346
491 and ppa.payroll_action_id = paa1.payroll_action_id
492 and ppa.effective_date between trunc(cp_date_paid,'Y') and cp_date_paid
493 and prr.assignment_action_id = paa1.assignment_action_id
494 and prr.source_type in ( 'E', 'I' )
495 and pet.element_type_id >= 0
496 and pet.element_information10 is not null
497 and nvl(ppa.date_earned,ppa.effective_date) between pet.effective_start_date and pet.effective_end_date
498 and prr.element_type_id + 0 = pet.element_type_id
499 and pec.classification_name in ('Earnings',
500 'Alien/Expat Earnings',
501 'Non-payroll Payments',
502 'Imputed Earnings',
503 'Supplemental Earnings')
504 and pet.classification_id = pec.classification_id
505 order by decode(nvl(pet.reporting_name, pet.element_name),
506 'Regular Pay', 0,
507 'Regular Salary',0,
508 'Regular Wages',0,
509 'Time Entry Wages',1),
510 decode(pec.classification_name,
511 'Earnings',1,
512 'Alien/Expat Earnings',2,
513 'Supplemental Earnings', 3,
514 'Inputed Earnings',4,
515 'Tax Credit',5,
516 'Non-payroll Payments',6),
517 pet.processing_priority;
518
519 /*
520 CURSOR c_get_earn_elements(cp_date_earned date,
521 cp_assignment_id number) IS
522 select /*+ ORDERED distinct
523 pet.element_type_id,
524 nvl(pet.reporting_name, pet.element_name),
525 pet.element_information10,
526 pet.element_information12,
527 pet.business_group_id,
528 pec.classification_name,
529 pet.processing_priority
530 from pay_element_entries_f pee,
531 pay_run_results prr,
532 pay_element_types_f pet,
533 pay_element_classifications pec
534 where pee.assignment_id = cp_assignment_id
535 --and pee.effective_end_date >= trunc(cp_date_earned, 'Y')
536 and pee.effective_start_date <= cp_date_earned
537 and prr.source_id = pee.element_entry_id
538 and prr.source_type in ( 'E', 'I' )
539 and pec.classification_name in ('Earnings',
540 'Alien/Expat Earnings',
541 'Non-payroll Payments',
542 'Imputed Earnings',
543 'Supplemental Earnings')
544 and pet.classification_id = pec.classification_id
545 and pet.element_information10 is not null
546 and pet.effective_start_date =
547 (select max(pet1.effective_start_date)
548 from pay_element_types_f pet1
549 where pet1.element_type_id = pet.element_type_id
550 and pet1.effective_start_date <= cp_date_earned)
551 and prr.element_type_id + 0 = pet.element_type_id
552 order by decode(nvl(pet.reporting_name, pet.element_name),
553 'Regular Pay', 0,
554 'Regular Salary',0,
555 'Regular Wages',0,
556 'Time Entry Wages',1),
557 decode(pec.classification_name,
558 'Earnings',1,
559 'Alien/Expat Earnings',2,
560 'Supplemental Earnings', 3,
561 'Inputed Earnings',4,
562 'Tax Credit',5,
563 'Non-payroll Payments',6),
564 pet.processing_priority;
565 */
566
567
568 l_rep_name pay_us_earnings_amounts_v.reporting_name_alt%type;
569 l_run_val number;
570 l_ytd_val number;
571 l_hours number;
572
573 l_found1 number:=0;
574 l_found boolean;
575 l_pos number;
576 l_procedure varchar2(21);
577
578 l_element_type_id pay_element_types_f.element_type_id%type;
579 l_element_reporting_name pay_element_types_f.reporting_name%type;
580 l_element_information10 pay_element_types_f.element_information10%type;
581 l_element_information12 pay_element_types_f.element_information12%type;
582 l_assignment_id pay_assignment_actions.assignment_id%type;
583 l_assignment_action_id pay_assignment_actions.assignment_action_id%type;
584 l_tax_unit_id pay_assignment_actions.tax_unit_id%type;
585 l_date_earned pay_payroll_actions.date_earned%type;
586 l_date_paid pay_payroll_actions.effective_date%type;
587 l_business_group_id pay_element_types_f.business_group_id%type;
588 l_classification_name pay_element_classifications.classification_name%type;
589 l_processing_priority pay_element_types_f.processing_priority%type;
590
591
592 -- Procedure to get the position of the reporting name in the plsql table
593 -- If the element exists it will return the position otherwise will return
594 -- new index where new element will be stored. Needed to group the earnings
595 -- based on the reporting name
596 PROCEDURE get_position_name (
597 p_rep_name in pay_us_earnings_amounts_rbr_v.reporting_name_alt%type,
598 p_found out nocopy boolean,
599 p_index out nocopy number)
600 IS
601 st_cnt number;
602 ed_cnt number;
603 p_cnt number;
604 BEGIN
605 p_found := FALSE;
606 p_index := 0;
607 p_cnt := p_earn_tab.COUNT;
608
609 IF p_cnt = 0 THEN
610 p_found := FALSE;
611 p_index := 0;
612 return;
613 ELSE
614 st_cnt := p_earn_tab.FIRST;
615 ed_cnt := p_earn_tab.LAST;
616
617 for i in st_cnt.. ed_cnt LOOP
618 IF p_earn_tab.exists(i) THEN
619 IF p_rep_name = p_earn_tab(i).rep_name THEN
620 p_index := i;
621 p_found := TRUE;
622 return;
623 END IF;
624 END IF;
625 END LOOP;
626 END IF;
627 END get_position_name;
628
629 BEGIN
630 --hr_utility.trace_on(null,'SOE');
631 l_procedure := 'populate_earn_bal';
632 hr_utility.set_location(l_package||l_procedure,10);
633
634 IF g_run_dimension_id is null THEN
635 OPEN c_get_dimension_ids('_ASG_GRE_RUN');
636 FETCH c_get_dimension_ids into g_run_dimension_id;
637 CLOSE c_get_dimension_ids;
638
639 OPEN c_get_dimension_ids('_ASG_GRE_YTD');
640 FETCH c_get_dimension_ids into g_ytd_dimension_id;
641 CLOSE c_get_dimension_ids;
642 END IF;
643 hr_utility.trace('Run Dimension : ' || g_run_dimension_id ||
644 'YTD Dimension : ' || g_ytd_dimension_id);
645
646 -- delete earnings table
647 p_earn_tab.delete;
648 earnings_elements_tab.delete;
649 hr_utility.set_location(l_package||l_procedure,20);
650
651 IF p_action_type in ('P','U') THEN
652
653 IF p_balance_status = 'Y' THEN
654 IF run_actions_tab.count > 0 THEN
655
656 hr_utility.set_location(l_package||l_procedure,30);
657 -- set the session variable as we are only getting the
658 -- RUN Balance
659 pay_us_balance_view_pkg.set_session_var('RUN','TRUE');
660 pay_us_balance_view_pkg.set_session_var('QTD','FALSE');
661 pay_us_balance_view_pkg.set_session_var('MTD','FALSE');
662 pay_us_balance_view_pkg.set_session_var('PYDATE','FALSE');
663 pay_us_balance_view_pkg.set_session_var('YTD','FALSE');
664
665 -- For all the run actions locked by prepayment , get all the
666 -- earnings elements and corresponding run value
667 FOR i IN run_actions_tab.FIRST .. run_actions_tab.LAST LOOP
668 IF run_actions_tab.exists(i) THEN
669 OPEN c_get_pre_earn_run_rb(run_actions_tab(i).aaid);
670 LOOP
671 FETCH c_get_pre_earn_run_rb INTO
672 l_rep_name --:EARNINGS.ELE_NAME
673 ,l_run_val --:EARNINGS.EARN_AMT
674 ,l_hours --:EARNINGS.EARN_HRS;
675 ,l_element_type_id;
676 EXIT WHEN c_get_pre_earn_run_rb%NOTFOUND;
677
678 -- Populate Earnings Elements Table
679 earnings_elements_tab(l_element_type_id).element_reporting_name
680 := l_rep_name ;
681 earnings_elements_tab(l_element_type_id).element_information10
682 := null;
683 earnings_elements_tab(l_element_type_id).element_information12
684 := null;
685 earnings_elements_tab(l_element_type_id).business_group_id
686 := null;
687 earnings_elements_tab(l_element_type_id).classification_name
688 := null;
689 --
690 -- See if element already exists in plsql table
691 get_position_name(l_rep_name,l_found, l_pos);
692 hr_utility.set_location(l_package||l_procedure,40);
693 IF l_found = FALSE THEN
694 l_pos := p_earn_tab.COUNT + 1;
695 p_earn_tab(l_pos).rep_name := l_rep_name;
696 p_earn_tab(l_pos).hour_val := l_hours;
697 p_earn_tab(l_pos).cur_val := l_run_val;
698 p_earn_tab(l_pos).ytd_val :=0;
699 ELSE
700 p_earn_tab(l_pos).hour_val
701 := p_earn_tab(l_pos).hour_val + l_hours;
702 p_earn_tab(l_pos).cur_val
703 := p_earn_tab(l_pos).cur_val + l_run_val;
704 p_earn_tab(l_pos).ytd_val:=0;
705 END IF;
706 END LOOP;
707 CLOSE c_get_pre_earn_run_rb;
708 hr_utility.set_location(l_package||l_procedure,50);
709 END IF;
710 END LOOP;
711 hr_utility.set_location(l_package||l_procedure,60);
712 END IF;
713
714 -- Get YTD values for master action
715 IF p_earn_tab.COUNT > 0 THEN
716
717 hr_utility.set_location(l_package||l_procedure,70);
718
719 -- Get the YTD value for the maximum run action stored in the
720 -- master_actions_tab plsql table
721 IF master_actions_tab.count > 0 then
722
723 hr_utility.set_location(l_package||l_procedure,90);
724
725 pay_us_balance_view_pkg.set_session_var('RUN','FALSE');
726 pay_us_balance_view_pkg.set_session_var('QTD','FALSE');
727 pay_us_balance_view_pkg.set_session_var('MTD','FALSE');
728 pay_us_balance_view_pkg.set_session_var('PYDATE','FALSE');
729 pay_us_balance_view_pkg.set_session_var('YTD','TRUE');
730
731 FOR i IN master_actions_tab.FIRST .. master_actions_tab.LAST LOOP
732 IF master_actions_tab.exists(i) THEN
733 OPEN c_get_pre_earn_ytd_rb(master_actions_tab(i).aaid);
734 LOOP
735 FETCH c_get_pre_earn_ytd_rb INTO
736 l_ytd_val,l_rep_name,l_element_type_id;
737 EXIT WHEN c_get_pre_earn_ytd_rb%NOTFOUND;
738 hr_utility.set_location(l_package||l_procedure,91);
739
740 -- Populate Earnings after check
741 IF earnings_elements_tab.count > 0 THEN
742 IF earnings_elements_tab.exists(l_element_type_id) THEN
743 hr_utility.trace('Element already exists in PLSQL table');
744 ELSE
745 earnings_elements_tab(l_element_type_id).element_reporting_name
746 := l_rep_name ;
747 earnings_elements_tab(l_element_type_id).element_information10
748 := null;
749 earnings_elements_tab(l_element_type_id).element_information12
750 := null;
751 earnings_elements_tab(l_element_type_id).business_group_id
752 := null;
753 earnings_elements_tab(l_element_type_id).classification_name
754 := null;
755 END IF;
756 hr_utility.set_location(l_package||l_procedure,92);
757 ELSE
758 earnings_elements_tab(l_element_type_id).element_reporting_name
759 := l_rep_name;
760 earnings_elements_tab(l_element_type_id).element_information10
761 := null;
762 earnings_elements_tab(l_element_type_id).element_information12
763 := null;
764 earnings_elements_tab(l_element_type_id).business_group_id
765 := null;
766 earnings_elements_tab(l_element_type_id).classification_name
767 := null;
768 END IF;
769 hr_utility.set_location(l_package||l_procedure,93);
770
771 -- get the position of the element in the plsql table
772 get_position_name(l_rep_name,l_found, l_pos);
773 IF l_found = TRUE THEN
774 -- Add the value if element already exists
775 p_earn_tab(l_pos).ytd_val :=p_earn_tab(l_pos).ytd_val+l_ytd_val;
776 ELSE
777 -- Create new index and store ytd value with run values as 0
778 l_pos := p_earn_tab.count+1;
779 p_earn_tab(l_pos).rep_name :=l_rep_name;
780 p_earn_tab(l_pos).cur_val :=0;
781 p_earn_tab(l_pos).hour_val :=0;
782 p_earn_tab(l_pos).ytd_val :=l_ytd_val;
783 END IF;
784 END LOOP;
785 hr_utility.set_location(l_package||l_procedure,94);
786 CLOSE c_get_pre_earn_ytd_rb;
787 END IF;
788 END LOOP;
789
790 hr_utility.set_location(l_package||l_procedure,100);
791 END IF; -- master_actions_tab.count > 0
792 END IF;
793 hr_utility.set_location(l_package||l_procedure,110);
794 end if;
795
796 IF p_balance_status <> 'Y' THEN
797
798 hr_utility.set_location(l_package||l_procedure,120);
799
800 IF run_actions_tab.COUNT >0 THEN
801 pay_us_balance_view_pkg.set_session_var('RUN','TRUE');
802 pay_us_balance_view_pkg.set_session_var('QTD','FALSE');
803 pay_us_balance_view_pkg.set_session_var('MTD','FALSE');
804 pay_us_balance_view_pkg.set_session_var('PYDATE','FALSE');
805 pay_us_balance_view_pkg.set_session_var('YTD','FALSE');
806
807 -- For all the run actions locked by prepayment , get all the
808 -- earnings elements and corresponding run value
809 FOR i IN run_actions_tab.FIRST .. run_actions_tab.LAST LOOP
810 IF run_actions_tab.exists(i) THEN
811 -- 4966938
812 OPEN c_get_pay_assignment_dtl(run_actions_tab(i).aaid);
813 FETCH c_get_pay_assignment_dtl INTO l_assignment_id, l_date_earned, l_date_paid;
814 CLOSE c_get_pay_assignment_dtl;
815
816 hr_utility.set_location(l_package||l_procedure,210);
817 hr_utility.trace('Run Action ID : ' || run_actions_tab(i).aaid);
818 hr_utility.set_location(l_package||l_procedure,220);
819 -- 4966938
820 OPEN c_get_earn_elements(l_date_paid,run_actions_tab(i).aaid);
821 LOOP
822 FETCH c_get_earn_elements
823 INTO l_element_type_id
824 ,l_element_reporting_name
825 ,l_element_information10
826 ,l_element_information12
827 ,l_business_group_id
828 ,l_classification_name
829 ,l_processing_priority;
830 EXIT WHEN c_get_earn_elements%NOTFOUND;
831
832 --Remove a check from here for Bug 3334690
833
834 earnings_elements_tab(l_element_type_id).element_reporting_name
835 := l_element_reporting_name ;
836 earnings_elements_tab(l_element_type_id).element_information10
837 := l_element_information10;
838 earnings_elements_tab(l_element_type_id).element_information12
839 := l_element_information12;
840 earnings_elements_tab(l_element_type_id).business_group_id
841 := l_business_group_id;
842 earnings_elements_tab(l_element_type_id).classification_name
843 := l_classification_name;
844
845 hr_utility.set_location(l_package||l_procedure,221);
846 IF l_classification_name = 'Non-payroll Payments' THEN
847 l_rep_name := l_element_reporting_name;
848 l_hours := null;
849 l_run_val := pay_balance_pkg.get_value
850 (get_defined_bal(l_element_information10,
851 g_run_dimension_id),
852 run_actions_tab(i).aaid);
853 ELSE
854 hr_utility.set_location(l_package||l_procedure,222);
855 l_rep_name := l_element_reporting_name;
856 hr_utility.set_location(l_package||l_procedure ,223);
857
858 IF l_element_information12 is not null THEN
859 l_hours := pay_balance_pkg.get_value
860 (get_defined_bal(
861 to_number(l_element_information12),
862 g_run_dimension_id),
863 run_actions_tab(i).aaid);
864 ELSE
865 l_hours := null;
866 END IF;
867
868 hr_utility.set_location(l_package||l_procedure,224);
869 l_run_val := pay_balance_pkg.get_value
870 (get_defined_bal(
871 to_number(l_element_information10),
872 g_run_dimension_id),
873 run_actions_tab(i).aaid);
874 hr_utility.set_location(l_package||l_procedure,225);
875 END IF;
876 hr_utility.trace('Hours Val : ' || l_hours);
877 hr_utility.trace('Run Val : ' || l_run_val);
878
879 get_position_name(l_rep_name,l_found, l_pos);
880 IF l_found = FALSE THEN
881 l_pos := p_earn_tab.COUNT + 1;
882 p_earn_tab(l_pos).rep_name := l_rep_name;
883 p_earn_tab(l_pos).hour_val := l_hours;
884 p_earn_tab(l_pos).cur_val := l_run_val;
885 p_earn_tab(l_pos).ytd_val := 0;
886 ELSE
887 p_earn_tab(l_pos).hour_val
888 := p_earn_tab(l_pos).hour_val + l_hours;
889 p_earn_tab(l_pos).cur_val
890 := p_earn_tab(l_pos).cur_val + l_run_val;
891 p_earn_tab(l_pos).ytd_val :=0;
892 END IF;
893
894 END LOOP;
895 CLOSE c_get_earn_elements;
896 END IF;
897 END LOOP;
898 END IF;
899
900 hr_utility.set_location(l_package||l_procedure,226);
901 IF earnings_elements_tab.COUNT > 0 THEN
902
903 IF master_actions_tab.COUNT>0 THEN
904 pay_us_balance_view_pkg.set_session_var('RUN','FALSE');
905 pay_us_balance_view_pkg.set_session_var('QTD','FALSE');
906 pay_us_balance_view_pkg.set_session_var('MTD','FALSE');
907 pay_us_balance_view_pkg.set_session_var('PYDATE','FALSE');
908 pay_us_balance_view_pkg.set_session_var('YTD','TRUE');
909
910 FOR i IN master_actions_tab.FIRST .. master_actions_tab.LAST LOOP
911 IF master_actions_tab.exists(i) THEN
912
913 hr_utility.trace('Master Action : ' || master_actions_tab(i).aaid);
914 hr_utility.set_location(l_package||l_procedure,230);
915
916 FOR j IN earnings_elements_tab.first..earnings_elements_tab.last LOOP
917 IF earnings_elements_tab.exists(j) and
918 earnings_elements_tab(j).element_information10 is not null THEN
919 hr_utility.set_location(l_package||l_procedure,240);
920
921 l_rep_name := earnings_elements_tab(j).element_reporting_name;
922 l_hours := null;
923 l_ytd_val := pay_balance_pkg.get_value
924 (get_defined_bal(
925 to_number(earnings_elements_tab(j).element_information10),
926 g_ytd_dimension_id),
927 master_actions_tab(i).aaid);
928 hr_utility.set_location(l_package||l_procedure,254);
929
930 get_position_name(l_rep_name,l_found, l_pos);
931 IF l_found = TRUE THEN
932 p_earn_tab(l_pos).ytd_val := p_earn_tab(l_pos).ytd_val + l_ytd_val;
933 ELSE
934 l_pos := p_earn_tab.count+1 ;
935 p_earn_tab(l_pos).rep_name := l_rep_name;
936 p_earn_tab(l_pos).cur_val := 0;
937 p_earn_tab(l_pos).hour_val := 0;
938 p_earn_tab(l_pos).ytd_val := l_ytd_val;
939 END IF;
940 END IF;
941 END LOOP;
942 END IF;
943 END LOOP;
944 hr_utility.set_location(l_package||l_procedure,150);
945 END IF;
946 END IF;
947 END IF;
948
949 ELSE -- SOE for Run is viewed
950
951 IF p_balance_status = 'Y' THEN
952
953 hr_utility.set_location(l_package||l_procedure,160);
954 pay_us_balance_view_pkg.set_session_var('RUN','TRUE');
955 pay_us_balance_view_pkg.set_session_var('QTD','FALSE');
956 pay_us_balance_view_pkg.set_session_var('MTD','FALSE');
957 pay_us_balance_view_pkg.set_session_var('PYDATE','FALSE');
958 pay_us_balance_view_pkg.set_session_var('YTD','TRUE');
959 earnings_elements_tab.delete;
960 p_earn_tab.delete;
961
962 OPEN c_get_pay_rb_elements(p_assignment_action_id);
963 LOOP
964 FETCH c_get_pay_rb_elements INTO l_ytd_val
965 ,l_rep_name
966 ,l_run_val
967 ,l_hours
968 ,l_element_type_id;
969 EXIT WHEN c_get_pay_rb_elements%NOTFOUND;
970
971 earnings_elements_tab(l_element_type_id).element_reporting_name := l_rep_name ;
972 earnings_elements_tab(l_element_type_id).element_information10 := null;
973 earnings_elements_tab(l_element_type_id).element_information12 := null;
974 earnings_elements_tab(l_element_type_id).business_group_id := null;
975 earnings_elements_tab(l_element_type_id).classification_name := null;
976
977 get_position_name(l_rep_name,l_found, l_pos);
978
979 hr_utility.set_location(l_package||l_procedure,40);
980 IF l_found = FALSE THEN
981 l_pos := p_earn_tab.COUNT + 1;
982 p_earn_tab(l_pos).rep_name := l_rep_name;
983 p_earn_tab(l_pos).hour_val := l_hours;
984 p_earn_tab(l_pos).cur_val := l_run_val;
985 p_earn_tab(l_pos).ytd_val := l_ytd_val;
986 ELSE
987 p_earn_tab(l_pos).hour_val := p_earn_tab(l_pos).hour_val + l_hours;
988 p_earn_tab(l_pos).cur_val := p_earn_tab(l_pos).cur_val + l_run_val;
989 p_earn_tab(l_pos).ytd_val := p_earn_tab(l_pos).ytd_val + l_ytd_val;
990 END IF;
991 END LOOP;
992 CLOSE c_get_pay_rb_elements;
993 hr_utility.set_location(l_package||l_procedure,170);
994 END IF;
995
996 IF p_balance_status <> 'Y' THEN
997 hr_utility.set_location(l_package||l_procedure,200);
998
999 OPEN c_get_pay_action_details(p_assignment_action_id);
1000 FETCH c_get_pay_action_details INTO l_assignment_id
1001 ,l_assignment_action_id
1002 ,l_date_earned
1003 ,l_tax_unit_id
1004 ,l_date_paid;
1005 CLOSE c_get_pay_action_details;
1006 hr_utility.set_location(l_package||l_procedure,210);
1007
1008 OPEN c_get_earn_elements(l_date_paid,l_assignment_action_id); -- Saurabh
1009 LOOP
1010 FETCH c_get_earn_elements INTO l_element_type_id
1011 ,l_element_reporting_name
1012 ,l_element_information10
1013 ,l_element_information12
1014 ,l_business_group_id
1015 ,l_classification_name
1016 ,l_processing_priority;
1017 hr_utility.trace(' SG l_element_type_id : ' || l_element_type_id );
1018 hr_utility.trace(' SG l_element_reporting_name : ' || l_element_reporting_name );
1019 hr_utility.trace(' SG l_element_information10 : ' || l_element_information10 );
1020 hr_utility.trace(' SG l_element_information12 : ' || l_element_information12 );
1021 hr_utility.trace(' SG l_business_group_id : ' || l_business_group_id );
1022 hr_utility.trace(' SG l_classification_name : ' || l_classification_name );
1023 hr_utility.trace(' SG l_processing_priority : ' || l_processing_priority );
1024 EXIT WHEN c_get_earn_elements%NOTFOUND;
1025 hr_utility.set_location(l_package||l_procedure,220);
1026 IF earnings_elements_tab.count > 0 THEN
1027
1028 hr_utility.set_location(l_package||l_procedure,230);
1029 IF earnings_elements_tab.exists(l_element_type_id) THEN
1030 hr_utility.trace('The element already exists in PLSQL table');
1031 ELSE
1032 earnings_elements_tab(l_element_type_id).element_reporting_name
1033 := l_element_reporting_name ;
1034 earnings_elements_tab(l_element_type_id).element_information10
1035 := l_element_information10;
1036 earnings_elements_tab(l_element_type_id).element_information12
1037 := l_element_information12;
1038 earnings_elements_tab(l_element_type_id).business_group_id
1039 := l_business_group_id;
1040 earnings_elements_tab(l_element_type_id).classification_name
1041 := l_classification_name;
1042 END IF;
1043 ELSE
1044 earnings_elements_tab(l_element_type_id).element_reporting_name
1045 := l_element_reporting_name ;
1046 earnings_elements_tab(l_element_type_id).element_information10
1047 := l_element_information10;
1048 earnings_elements_tab(l_element_type_id).element_information12
1049 := l_element_information12;
1050 earnings_elements_tab(l_element_type_id).business_group_id
1051 := l_business_group_id;
1052 earnings_elements_tab(l_element_type_id).classification_name
1053 := l_classification_name;
1054 END IF;
1055 --bugno 4743188
1056 IF earnings_elements_tab(l_element_type_id).classification_name
1057 = 'Non-payroll Payments' THEN
1058 hr_utility.set_location(l_package||l_procedure,240);
1059
1060 l_rep_name := earnings_elements_tab(l_element_type_id).element_reporting_name;
1061 l_hours := null;
1062 l_run_val := pay_balance_pkg.get_value
1063 (get_defined_bal(earnings_elements_tab(l_element_type_id).element_information10,
1064 g_run_dimension_id),
1065 p_assignment_action_id);
1066
1067 l_ytd_val := pay_balance_pkg.get_value
1068 (get_defined_bal(earnings_elements_tab(l_element_type_id).element_information10,
1069 g_ytd_dimension_id),
1070 p_assignment_action_id);
1071 ELSE
1072 hr_utility.set_location(l_package||l_procedure,250);
1073 l_rep_name := earnings_elements_tab(l_element_type_id).element_reporting_name;
1074 hr_utility.set_location(l_package||l_procedure ,251);
1075
1076 IF earnings_elements_tab(l_element_type_id).element_information12 is not null THEN
1077 hr_utility.trace('Info12 : ' ||
1078 earnings_elements_tab(l_element_type_id).element_information12 ||
1079 'g_run_dimension_id : ' || g_run_dimension_id);
1080 l_hours := pay_balance_pkg.get_value
1081 (get_defined_bal(to_number(earnings_elements_tab(l_element_type_id).element_information12),
1082 g_run_dimension_id),
1083 p_assignment_action_id);
1084 ELSE
1085 l_hours := null;
1086 END IF;
1087
1088 hr_utility.set_location(l_package||l_procedure,252);
1089 hr_utility.trace('Info10 : ' ||
1090 earnings_elements_tab(l_element_type_id).element_information10 ||
1091 'g_run_dimension_id : ' || g_run_dimension_id);
1092
1093 l_run_val := pay_balance_pkg.get_value
1094 (get_defined_bal(to_number(earnings_elements_tab(l_element_type_id).element_information10),
1095 g_run_dimension_id),
1096 p_assignment_action_id);
1097
1098 hr_utility.set_location(l_package||l_procedure,254);
1099 l_ytd_val := pay_balance_pkg.get_value
1100 (get_defined_bal(to_number(earnings_elements_tab(l_element_type_id).element_information10),
1101 g_ytd_dimension_id),
1102 p_assignment_action_id);
1103 hr_utility.set_location(l_package||l_procedure,256);
1104 END IF;
1105
1106 get_position_name(l_rep_name,l_found, l_pos);
1107
1108 IF l_found = TRUE THEN
1109 hr_utility.set_location(l_package||l_procedure,260);
1110 p_earn_tab(l_pos).ytd_val := p_earn_tab(l_pos).ytd_val + l_ytd_val;
1111 p_earn_tab(l_pos).cur_val := p_earn_tab(l_pos).cur_val + l_run_val;
1112 p_earn_tab(l_pos).hour_val := p_earn_tab(l_pos).hour_val + l_hours;
1113 ELSE
1114 hr_utility.set_location(l_package||l_procedure,261);
1115 l_pos := p_earn_tab.count + 1;
1116 p_earn_tab(l_pos).rep_name := l_rep_name;
1117 p_earn_tab(l_pos).ytd_val := l_ytd_val;
1118 p_earn_tab(l_pos).cur_val := l_run_val;
1119 p_earn_tab(l_pos).hour_val := l_hours;
1120 END IF;
1121 ---bug 4743188
1122
1123
1124 END LOOP;
1125 CLOSE c_get_earn_elements;
1126 hr_utility.set_location(l_package||l_procedure,270);
1127 /* --comments start bug 4743188
1128 IF earnings_elements_tab.count > 0 THEN
1129 FOR i IN earnings_elements_tab.first..earnings_elements_tab.last LOOP
1130 IF earnings_elements_tab.exists(i) and
1131 earnings_elements_tab(i).element_information10 is not null THEN
1132
1133 IF earnings_elements_tab(i).classification_name
1134 = 'Non-payroll Payments' THEN
1135 l_rep_name := earnings_elements_tab(i).element_reporting_name;
1136 l_hours := null;
1137 l_run_val := pay_balance_pkg.get_value
1138 (get_defined_bal(earnings_elements_tab(i).element_information10,
1139 g_run_dimension_id),
1140 p_assignment_action_id);
1141
1142 l_ytd_val := pay_balance_pkg.get_value
1143 (get_defined_bal(earnings_elements_tab(i).element_information10,
1144 g_ytd_dimension_id),
1145 p_assignment_action_id);
1146 ELSE
1147 hr_utility.set_location(l_package||l_procedure,250);
1148 l_rep_name := earnings_elements_tab(i).element_reporting_name;
1149 hr_utility.set_location(l_package||l_procedure ,251);
1150
1151 IF earnings_elements_tab(i).element_information12 is not null THEN
1152 hr_utility.trace('Info12 : ' ||
1153 earnings_elements_tab(i).element_information12 ||
1154 'g_run_dimension_id : ' || g_run_dimension_id);
1155 l_hours := pay_balance_pkg.get_value
1156 (get_defined_bal(to_number(earnings_elements_tab(i).element_information12),
1157 g_run_dimension_id),
1158 p_assignment_action_id);
1159 ELSE
1160 l_hours := null;
1161 END IF;
1162
1163 hr_utility.set_location(l_package||l_procedure,252);
1164 hr_utility.trace('Info10 : ' ||
1165 earnings_elements_tab(i).element_information10 ||
1166 'g_run_dimension_id : ' || g_run_dimension_id);
1167
1168 l_run_val := pay_balance_pkg.get_value
1169 (get_defined_bal(to_number(earnings_elements_tab(i).element_information10),
1170 g_run_dimension_id),
1171 p_assignment_action_id);
1172
1173 hr_utility.set_location(l_package||l_procedure,254);
1174 l_ytd_val := pay_balance_pkg.get_value
1175 (get_defined_bal(to_number(earnings_elements_tab(i).element_information10),
1176 g_ytd_dimension_id),
1177 p_assignment_action_id);
1178 hr_utility.set_location(l_package||l_procedure,256);
1179 END IF;
1180
1181 get_position_name(l_rep_name,l_found, l_pos);
1182 hr_utility.set_location(l_package||l_procedure,260);
1183 IF l_found = TRUE THEN
1184 p_earn_tab(l_pos).ytd_val := p_earn_tab(l_pos).ytd_val + l_ytd_val;
1185 p_earn_tab(l_pos).cur_val := p_earn_tab(l_pos).cur_val + l_run_val;
1186 p_earn_tab(l_pos).hour_val := p_earn_tab(l_pos).hour_val + l_hours;
1187 ELSE
1188 l_pos := p_earn_tab.count + 1;
1189 p_earn_tab(l_pos).rep_name := l_rep_name;
1190 p_earn_tab(l_pos).ytd_val := l_ytd_val;
1191 p_earn_tab(l_pos).cur_val := l_run_val;
1192 p_earn_tab(l_pos).hour_val := l_hours;
1193 END IF;
1194 END IF;
1195 END LOOP;
1196 END IF; */-- comments end
1197 END IF;
1198 hr_utility.set_location(l_package||l_procedure,248);
1199 END IF;--run/prepayment check
1200
1201 EXCEPTION
1202 WHEN others THEN
1203 hr_utility.set_location(l_package||l_procedure,290);
1204 raise_application_error(-20101, 'Error in ' || l_package||l_procedure|| ' - ' || sqlerrm);
1205 raise;
1206 END populate_earn_bal;
1207
1208
1209
1210 /*****************************************************************************
1211 Name : populate_fed_balance
1212 Purpose : This procedure populates a PL/SQL table with all the federal deduction
1213 elements for SOE form.
1214
1215 *****************************************************************************/
1216 PROCEDURE populate_fed_balance(p_assignment_action_id in number,
1217 p_balance_status in varchar2,
1218 p_action_type in varchar2,
1219 p_eic_curr_val out nocopy number,
1220 p_eic_ytd_val out nocopy number,
1221 p_dedn_tab out nocopy dedn)
1222 IS
1223 -- Declare Local Variables
1224 l_count number;
1225 l_run_amount number;
1226 l_curr_amount number;
1227 l_ytd_amount number;
1228 l_tax_type pay_us_fed_taxes_v.tax_type_code%TYPE;
1229
1230 start_cnt number;
1231 end_cnt number;
1232 i number :=0;
1233 j number :=0;
1234 k number :=0;
1235 l_found boolean;
1236 l_pos number;
1237
1238 l_rep_name pay_us_fed_taxes_v.user_reporting_name%TYPE;
1239 l_run_val number;
1240 l_ytd_val number;
1241 l_procedure varchar2(20) ;
1242
1243 /***
1244 ** Start Federal Balances Cursors when balances are not valid for eBRA **
1245 ***/
1246
1247 -- added cursor to get federal balances from run results
1248 CURSOR get_valid_taxes_fed_rr(l_assignment_action_id number)
1249 IS
1250 SELECT user_reporting_name,
1251 run_val,
1252 ytd_val,
1253 tax_type_code
1254 FROM pay_us_fed_taxes_v
1255 WHERE ee_or_er_code = 'EE'
1256 AND balance_category_code in ('WITHHELD','ADVANCED')
1257 AND assignment_action_id = l_assignment_action_id
1258 ORDER BY user_reporting_name;
1259
1260 -- Cursor to get Run Values
1261 CURSOR c_get_pre_fed_run_rr(cp_run_action_id NUMBER)
1262 IS
1263 select pt.user_reporting_name
1264 ,sum(pt.run_val)
1265 ,pt.tax_type_code
1266 from pay_us_fed_taxes_v pt
1267 where pt.ee_or_er_code = 'EE'
1268 and pt.balance_category_code in ('WITHHELD','ADVANCED')
1269 and pt.assignment_action_id = cp_run_action_id
1270 group by pt.user_reporting_name,tax_type_code
1271 order by user_reporting_name;
1272
1273
1274 -- Cursor to get YTD Value
1275 CURSOR c_get_pre_fed_ytd_rr(cp_master_action_id NUMBER)
1276 IS
1277 select sum(pt.ytd_val) ,
1278 pt.user_reporting_name,
1279 tax_type_code
1280 from pay_us_fed_taxes_v pt
1281 where pt.ee_or_er_code = 'EE'
1282 and pt.balance_category_code in ('WITHHELD','ADVANCED')
1283 and pt.assignment_action_id = cp_master_action_id
1284 group by pt.user_reporting_name,tax_type_code ;
1285
1286 /***
1287 ***End Federal Balances Cursors when balances are not valid for eBRA***
1288 ***/
1289
1290 /***
1291 ***Start Federal Balances Cursors when balances are valid for eBRA***
1292 ***/
1293
1294 -- Cursor to get federal balances from run balances
1295 CURSOR get_valid_taxes_fed_rb(l_assignment_action_id number)
1296 IS
1297 SELECT user_reporting_name,
1298 run_val,
1299 ytd_val,
1300 tax_type_code
1301 FROM pay_us_fed_taxes_rbr_v
1302 WHERE ee_or_er_code = 'EE'
1303 AND balance_category_code in ('WITHHELD','ADVANCED')
1304 AND assignment_action_id = l_assignment_action_id
1305 order by user_reporting_name;
1306
1307 -- Cursor to get Run Values
1308 CURSOR c_get_pre_fed_run_rb(cp_run_action_id NUMBER)
1309 IS
1310 select pt.user_reporting_name
1311 , sum(pt.run_val)
1312 ,pt.tax_type_code
1313 from pay_us_fed_taxes_rbr_v pt
1314 where pt.ee_or_er_code = 'EE'
1315 and pt.balance_category_code in ('WITHHELD','ADVANCED')
1316 and pt.assignment_action_id = cp_run_action_id
1317 group by pt.user_reporting_name,tax_type_code
1318 order by user_reporting_name;
1319
1320 -- Cursor to get YTD Value
1321 CURSOR c_get_pre_fed_ytd_rb(cp_master_action_id NUMBER)
1322 IS
1323 select sum(pt.ytd_val) run_val,
1324 pt.user_reporting_name,
1325 tax_type_code
1326 from pay_us_fed_taxes_rbr_v pt
1327 where pt.ee_or_er_code = 'EE'
1328 and pt.balance_category_code in ('WITHHELD','ADVANCED')
1329 and pt.assignment_action_id = cp_master_action_id
1330 group by pt.user_reporting_name,tax_type_code
1331 order by user_reporting_name;
1332
1333 /***
1334 ***Start Federal Balances Cursors when balances are valid for eBRA***
1335 ***/
1336
1337 l_master_action_id number;
1338
1339 -- Procedure to get the position of the federal deductions in the plsql table
1340 -- If the element exists it will return the position otherwise will return
1341 -- new index where new element will be stored.
1342 PROCEDURE get_position_fed (p_rep_name in pay_us_fed_taxes_v.user_reporting_name%TYPE ,
1343 p_tax_type_code in pay_us_fed_taxes_v.tax_type_code%type,
1344 p_found out nocopy boolean,
1345 p_index out nocopy number)
1346 IS
1347 st_cnt number;
1348 ed_cnt number;
1349 p_cnt number;
1350
1351 BEGIN
1352 p_found := FALSE;
1353 p_index := 0;
1354
1355 p_cnt := fed_tab.COUNT;
1356
1357 if p_cnt = 0 then
1358 p_found := FALSE;
1359 p_index := 0;
1360 return;
1361 else
1362 st_cnt := fed_tab.FIRST;
1363 ed_cnt := fed_tab.LAST;
1364 FOR i IN st_cnt.. ed_cnt LOOP
1365 IF fed_tab.exists(i) THEN
1366 IF p_rep_name = fed_tab(i).rep_name
1367 and p_tax_type_code=fed_tab(i).tax_type then
1368 p_index := i;
1369 p_found := TRUE;
1370 return;
1371 END IF;
1372 END IF;
1373 END LOOP;
1374 end if;
1375
1376 END; /* get_position_fed */
1377
1378 BEGIN
1379
1380 -- Start the Code : Need to Consider the Secondary Assignments Also--
1381 -- The code change is part of the eBRA Enhancement of SOE Form-----
1382 -- Check Balance Status
1383 l_procedure := 'populate_fed_balance';
1384
1385 hr_utility.set_location(l_package||l_procedure,10);
1386 -- delete the federal and deduction plsql tables
1387 fed_tab.delete;
1388
1389 hr_utility.set_location(l_package||l_procedure,20);
1390 IF p_action_type in ('P','U') THEN
1391
1392 hr_utility.set_location(l_package||l_procedure,30);
1393 IF p_balance_status = 'Y' THEN
1394
1395 IF run_actions_tab.COUNT>0 THEN
1396 start_cnt := run_actions_tab.FIRST;
1397 end_cnt := run_actions_tab.LAST;
1398 j := 0;
1399 pay_us_balance_view_pkg.set_session_var('RUN','TRUE');
1400 pay_us_balance_view_pkg.set_session_var('QTD','FALSE');
1401 pay_us_balance_view_pkg.set_session_var('MTD','FALSE');
1402 pay_us_balance_view_pkg.set_session_var('PYDATE','FALSE');
1403 pay_us_balance_view_pkg.set_session_var('YTD','FALSE');
1404
1405 hr_utility.set_location(l_package||l_procedure,40);
1406 FOR i IN start_cnt..end_cnt LOOP
1407 IF run_actions_tab.exists(i) THEN
1408 OPEN c_get_pre_fed_run_rb(run_actions_tab(i).aaid);
1409 LOOP
1410 FETCH c_get_pre_fed_run_rb
1411 INTO l_rep_name
1412 ,l_run_val
1413 ,l_tax_type;
1414 EXIT WHEN c_get_pre_fed_run_rb%NOTFOUND;
1415
1416 hr_utility.set_location(l_package||l_procedure,50);
1417 get_position_fed(l_rep_name,l_tax_type,l_found, l_pos);
1418
1419 hr_utility.set_location(l_package||l_procedure,60);
1420 IF l_found = FALSE THEN
1421 j := fed_tab.COUNT + 1;
1422 fed_tab(j).rep_name := l_rep_name;
1423 fed_tab(j).tax_type := l_tax_type;
1424 fed_tab(j).cur_val := l_run_val;
1425 fed_tab(j).ytd_val :=0;
1426 ELSE
1427 fed_tab(l_pos).cur_val := fed_tab(l_pos).cur_val + l_run_val;
1428 fed_tab(l_pos).ytd_val := 0;
1429 END IF;
1430 hr_utility.set_location(l_package||l_procedure,70);
1431 END LOOP;
1432 CLOSE c_get_pre_fed_run_rb;
1433 END IF;
1434 hr_utility.set_location(l_package||l_procedure,80);
1435 END LOOP;
1436 END IF;
1437 hr_utility.set_location(l_package||l_procedure,90);
1438 IF fed_tab.COUNT > 0 THEN
1439
1440 IF master_actions_tab.COUNT>0 THEN
1441 start_cnt:=master_actions_tab.FIRST;
1442 end_cnt:=master_actions_tab.LAST;
1443
1444 pay_us_balance_view_pkg.set_session_var('RUN','FALSE');
1445 pay_us_balance_view_pkg.set_session_var('QTD','FALSE');
1446 pay_us_balance_view_pkg.set_session_var('MTD','FALSE');
1447 pay_us_balance_view_pkg.set_session_var('PYDATE','FALSE');
1448 pay_us_balance_view_pkg.set_session_var('YTD','TRUE');
1449
1450 hr_utility.set_location(l_package||l_procedure,100);
1451 FOR i IN start_cnt..end_cnt LOOP
1452 IF master_actions_tab.exists(i) THEN
1453 OPEN c_get_pre_fed_ytd_rb(master_actions_tab(i).aaid);
1454 LOOP
1455 FETCH c_get_pre_fed_ytd_rb into l_ytd_val,l_rep_name,l_tax_type;
1456 EXIT WHEN c_get_pre_fed_ytd_rb%NOTFOUND;
1457 hr_utility.set_location(l_package||l_procedure,110);
1458 get_position_fed(l_rep_name,l_tax_type,l_found, l_pos);
1459
1460 IF l_found = TRUE THEN
1461 fed_tab(l_pos).ytd_val := fed_tab(l_pos).ytd_val + l_ytd_val;
1462 ELSE
1463 k := fed_tab.count+1;
1464 fed_tab(k).rep_name :=l_rep_name;
1465 fed_tab(k).cur_val :=0;
1466 fed_tab(k).ytd_val :=l_ytd_val;
1467 END IF;
1468 hr_utility.set_location(l_package||l_procedure,120);
1469 END LOOP;
1470 CLOSE c_get_pre_fed_ytd_rb;
1471 END IF;
1472
1473 END LOOP;
1474 hr_utility.set_location(l_package||l_procedure,120);
1475 END IF;
1476 END IF;
1477
1478 ELSE
1479
1480 hr_utility.set_location(l_package||l_procedure,130);
1481 IF run_actions_tab.count>0 THEN
1482 start_cnt := run_actions_tab.FIRST;
1483 end_cnt := run_actions_tab.LAST;
1484
1485 pay_us_balance_view_pkg.set_session_var('RUN','TRUE');
1486 pay_us_balance_view_pkg.set_session_var('QTD','FALSE');
1487 pay_us_balance_view_pkg.set_session_var('MTD','FALSE');
1488 pay_us_balance_view_pkg.set_session_var('PYDATE','FALSE');
1489 pay_us_balance_view_pkg.set_session_var('YTD','FALSE');
1490
1491 FOR i IN start_cnt..end_cnt LOOP
1492 OPEN c_get_pre_fed_run_rr(run_actions_tab(i).aaid);
1493
1494 LOOP
1495 FETCH c_get_pre_fed_run_rr
1496 INTO l_rep_name
1497 ,l_run_val
1498 ,l_tax_type;
1499 EXIT WHEN c_get_pre_fed_run_rr%NOTFOUND;
1500
1501 hr_utility.set_location(l_package||l_procedure,140);
1502 get_position_fed(l_rep_name,l_tax_type,l_found, l_pos);
1503
1504 IF l_found = FALSE THEN
1505 j := fed_tab.COUNT + 1;
1506 fed_tab(j).rep_name := l_rep_name;
1507 fed_tab(j).tax_type := l_tax_type;
1508 fed_tab(j).cur_val := l_run_val;
1509 fed_tab(j).ytd_val :=0;
1510 ELSE
1511 fed_tab(l_pos).cur_val := fed_tab(l_pos).cur_val + l_run_val;
1512 fed_tab(l_pos).ytd_val := 0;
1513 END IF;
1514 hr_utility.set_location(l_package||l_procedure,150);
1515 END LOOP;
1516
1517 CLOSE c_get_pre_fed_run_rr;
1518 END LOOP;
1519 END IF;
1520
1521 hr_utility.set_location(l_package||l_procedure,160);
1522 IF fed_tab.COUNT > 0 THEN
1523 IF master_actions_tab.COUNT>0 THEN
1524
1525 start_cnt:= master_actions_tab.FIRST;
1526 end_cnt:=master_actions_tab.LAST;
1527
1528 hr_utility.set_location(l_package||l_procedure,170);
1529 pay_us_balance_view_pkg.set_session_var('RUN','FALSE');
1530 pay_us_balance_view_pkg.set_session_var('QTD','FALSE');
1531 pay_us_balance_view_pkg.set_session_var('MTD','FALSE');
1532 pay_us_balance_view_pkg.set_session_var('PYDATE','FALSE');
1533 pay_us_balance_view_pkg.set_session_var('YTD','TRUE');
1534
1535 FOR i IN start_cnt..end_cnt LOOP
1536 IF master_actions_tab.exists(i) THEN
1537 OPEN c_get_pre_fed_ytd_rr(master_actions_tab(i).aaid);
1538
1539 LOOP
1540 FETCH c_get_pre_fed_ytd_rr into l_ytd_val,l_rep_name,l_tax_type;
1541 EXIT WHEN c_get_pre_fed_ytd_rr%NOTFOUND;
1542
1543 hr_utility.set_location(l_package||l_procedure,180);
1544 get_position_fed(l_rep_name,l_tax_type,l_found, l_pos);
1545
1546 IF l_found = TRUE THEN
1547 fed_tab(l_pos).ytd_val := fed_tab(l_pos).ytd_val + l_ytd_val;
1548 ELSE
1549 k := fed_tab.count+1;
1550 fed_tab(k).rep_name :=l_rep_name;
1551 fed_tab(k).cur_val :=0;
1552 fed_tab(k).ytd_val :=l_ytd_val;
1553 END IF;
1554
1555 END LOOP;
1556 hr_utility.set_location(l_package||l_procedure,190);
1557 CLOSE c_get_pre_fed_ytd_rr;
1558 END IF;
1559 END LOOP;
1560 END IF;
1561 hr_utility.set_location(l_package||l_procedure,200);
1562 END IF;
1563 END IF;
1564
1565 ELSE
1566
1567 hr_utility.set_location(l_package||l_procedure,210);
1568 pay_us_balance_view_pkg.set_session_var('YTD','TRUE');
1569 pay_us_balance_view_pkg.set_session_var('RUN','TRUE');
1570 pay_us_balance_view_pkg.set_session_var('QTD','FALSE');
1571 pay_us_balance_view_pkg.set_session_var('MTD','FALSE');
1572 pay_us_balance_view_pkg.set_session_var('PYDATE','FALSE');
1573
1574 hr_utility.set_location(l_package||l_procedure,220);
1575 -------------Run Federal Taxes Start-------------------------------
1576 i := 0;
1577 IF p_balance_status = 'Y' THEN
1578 hr_utility.set_location(l_package||l_procedure,230);
1579 OPEN get_valid_taxes_fed_rb(p_assignment_action_id) ;
1580 LOOP
1581 FETCH get_valid_taxes_fed_rb
1582 INTO fed_tab(i).rep_name,
1583 fed_tab(i).cur_val,
1584 fed_tab(i).ytd_val,
1585 fed_tab(i).tax_type;
1586
1587 EXIT WHEN get_valid_taxes_fed_rb%NOTFOUND;
1588 i := i+1;
1589 END LOOP;
1590 hr_utility.set_location(l_package||l_procedure,240);
1591 CLOSE get_valid_taxes_fed_rb;
1592
1593 ELSE
1594 hr_utility.set_location(l_package||l_procedure,250);
1595 OPEN get_valid_taxes_fed_rr(p_assignment_action_id) ;
1596 LOOP
1597
1598 FETCH get_valid_taxes_fed_rr
1599 INTO fed_tab(i).rep_name,
1600 fed_tab(i).cur_val,
1601 fed_tab(i).ytd_val,
1602 fed_tab(i).tax_type;
1603
1604 EXIT WHEN get_valid_taxes_fed_rr%NOTFOUND;
1605 i := i +1 ;
1606 END LOOP;
1607 hr_utility.set_location(l_package||l_procedure,260);
1608 CLOSE get_valid_taxes_fed_rr;
1609 END IF;
1610 END IF;
1611
1612 -- Populate the values in dedn plsql table for SOE Form
1613 hr_utility.set_location(l_package||l_procedure,270);
1614 IF fed_tab.count > 0 THEN
1615
1616 start_cnt := fed_tab.FIRST;
1617 end_cnt := fed_tab.LAST;
1618
1619 hr_utility.set_location(l_package||l_procedure,280);
1620 FOR i IN start_cnt..end_cnt LOOP
1621 IF fed_tab.exists(i) THEN
1622 fed_tab(i).rep_name := REPLACE(fed_tab(i).rep_name, 'EE ', '');
1623
1624 IF fed_tab(i).tax_type = 'EIC' THEN
1625 fed_tab(i).cur_val := -1 * fed_tab(i).cur_val;
1626 fed_tab(i).ytd_val := -1 * fed_tab(i).ytd_val;
1627
1628 p_eic_curr_val := fed_tab(i).cur_val;
1629 -- Bug 1786497
1630 p_eic_ytd_val := fed_tab(i).ytd_val;
1631
1632 END IF;
1633 hr_utility.set_location(l_package||l_procedure,290);
1634 p_dedn_tab(i).rep_name := fed_tab(i).rep_name;
1635 p_dedn_tab(i).cur_val := fed_tab(i).cur_val;
1636 p_dedn_tab(i).ytd_val := fed_tab(i).ytd_val ;
1637 END IF;
1638 hr_utility.set_location(l_package||l_procedure,300);
1639 END LOOP;
1640 END IF;
1641
1642 EXCEPTION
1643 WHEN others THEN
1644 hr_utility.set_location(l_package||l_procedure,310);
1645 raise_application_error(-20101, 'Error in ' ||l_package||l_procedure || ' - ' || sqlerrm);
1646 END populate_fed_balance;
1647
1648
1649
1650
1651 /*****************************************************************************
1652 Name : populate_state_balance
1653 Purpose : This procedure populates a PL/SQL table with all the state deductions
1654 elements for SOE form.
1655 *****************************************************************************/
1656 PROCEDURE populate_state_balance(p_assignment_action_id in number,
1657 p_balance_status in varchar2,
1658 p_action_type in varchar2,
1659 p_steic_curr_val out nocopy number,
1660 p_steic_ytd_val out nocopy number,
1661 p_dedn_tab out nocopy dedn)
1662 IS
1663
1664 -- Declare Local Variables
1665 l_juris_code pay_us_state_taxes_v.jurisdiction_code%type;
1666
1667 l_count number;
1668 l_run_amount number;
1669 l_curr_amount number;
1670 l_ytd_amount number;
1671 l_tax_type pay_us_state_taxes_v.tax_type_code%type;
1672
1673 start_cnt number;
1674 end_cnt number;
1675 i number := 0;
1676 j number := 0;
1677 k number := 0;
1678 l_found boolean;
1679 l_pos number;
1680
1681 l_ytd_value number;
1682 l_master_action_id number;
1683 l_state_abbrev pay_us_state_taxes_v.state_abbrev%type;
1684
1685 l_rep_name pay_us_state_taxes_v.user_reporting_name%TYPE;
1686 l_run_val number;
1687 l_ytd_val number;
1688 l_procedure varchar2(22);
1689
1690 /***
1691 ***Start State Balances Cursors when balances are not valid for eBRA***
1692 ***/
1693
1694 -- Cursor to get state balances from run results
1695 CURSOR get_valid_taxes_state_rr(l_assignment_action_id number)
1696 IS
1697 select state_abbrev,
1698 user_reporting_name,
1699 run_val,
1700 tax_type_code,
1701 jurisdiction_code,
1702 ytd_val
1703 from pay_us_state_taxes_v
1704 where ee_or_er_code = 'EE'
1705 and assignment_action_id = l_assignment_action_id
1706 order by user_reporting_name;
1707
1708 -- Cursor to get Run Values
1709 CURSOR c_get_pre_state_run_rr(cp_run_action_id NUMBER)
1710 IS
1711 select state_abbrev,
1712 user_reporting_name,
1713 sum(run_val),
1714 tax_type_code
1715 from pay_us_state_taxes_v pt
1716 where pt.ee_or_er_code = 'EE'
1717 and pt.assignment_action_id = cp_run_action_id
1718 group by user_reporting_name, state_abbrev,tax_type_code
1719 order by user_reporting_name;
1720
1721 -- Cursor to get YTD Value
1722 CURSOR c_get_pre_state_ytd_rr(cp_master_action_id NUMBER)
1723 IS
1724 select sum(pt.ytd_val),
1725 user_reporting_name,
1726 tax_type_code,
1727 state_abbrev
1728 from pay_us_state_taxes_v pt
1729 where pt.ee_or_er_code = 'EE'
1730 and pt.assignment_action_id = cp_master_action_id
1731 group by user_reporting_name, state_abbrev,tax_type_code
1732 order by user_reporting_name;
1733
1734 /***
1735 ***End State Balances Cursors when balances are not valid for eBRA***
1736 ***/
1737
1738
1739 /***
1740 ***Start State Balances Cursors when balances are valid for eBRA***
1741 ***/
1742
1743 -- Cursor to get state balances from run balances
1744 CURSOR get_valid_taxes_state_rb(l_assignment_action_id number)
1745 IS
1746 select state_abbrev,
1747 user_reporting_name,
1748 run_val,
1749 tax_type_code,
1750 jurisdiction_code,
1751 ytd_val
1752 from pay_us_state_taxes_rbr_v
1753 where ee_or_er_code = 'EE'
1754 and assignment_action_id = l_assignment_action_id
1755 order by user_reporting_name;
1756
1757 --Cursor to get Run Values
1758 CURSOR c_get_pre_state_run_rb(cp_run_action_id NUMBER)
1759 IS
1760 select state_abbrev,
1761 user_reporting_name,
1762 sum(run_val),
1763 tax_type_code
1764 from pay_us_state_taxes_rbr_v pt
1765 where pt.ee_or_er_code = 'EE'
1766 and pt.assignment_action_id = cp_run_action_id
1767 group by user_reporting_name, state_abbrev,tax_type_code
1768 order by user_reporting_name;
1769
1770 -- Cursor to get YTD Value
1771 CURSOR c_get_pre_state_ytd_rb(cp_master_action_id NUMBER)
1772 IS
1773 select sum(pt.ytd_val)
1774 ,user_reporting_name
1775 ,tax_type_code
1776 ,state_abbrev
1777 from pay_us_state_taxes_rbr_v pt
1778 where pt.ee_or_er_code = 'EE'
1779 and pt.assignment_action_id = cp_master_action_id
1780 group by user_reporting_name, state_abbrev,tax_type_code
1781 order by user_reporting_name;
1782
1783 /***
1784 ***Start State Balances Cursors when balances are valid for eBRA***
1785 ***/
1786
1787 -- Procedure to get the position of the state deductions in the plsql table
1788 -- If the element exists it will return the position otherwise will return
1789 -- new index where new element will be stored. The reporting name with the
1790 -- same state name are grouped to get the final deduction value.
1791 PROCEDURE get_position_state (p_rep_name in pay_us_state_taxes_v.user_reporting_name%TYPE ,
1792 p_tax_type_code in pay_us_state_taxes_v.user_reporting_name%TYPE ,
1793 p_state_abbrev in pay_us_state_taxes_v.state_abbrev%type,
1794 p_found out nocopy boolean,
1795 p_index out nocopy number)
1796 IS
1797
1798 st_cnt number;
1799 ed_cnt number;
1800 p_cnt number;
1801
1802 BEGIN
1803 p_found := FALSE;
1804 p_index := 0;
1805
1806 p_cnt := state_tab.COUNT;
1807
1808 IF p_cnt = 0 THEN
1809
1810 p_found := FALSE;
1811 p_index := 0;
1812 return;
1813
1814 ELSE
1815 st_cnt := state_tab.FIRST;
1816 ed_cnt := state_tab.LAST;
1817 FOR i in st_cnt.. ed_cnt LOOP
1818 IF state_tab.exists(i) THEN
1819 IF p_rep_name = state_tab(i).rep_name
1820 and p_tax_type_code=state_tab(i).tax_type
1821 and p_state_abbrev=state_tab(i).state_abbrev THEN
1822
1823 p_index := i;
1824 p_found := TRUE;
1825 return;
1826
1827 END IF;
1828 END IF;
1829 END LOOP;
1830 END IF;
1831
1832 END; /* get_position_state */
1833
1834 BEGIN
1835
1836 -- Start the Code : Need to Consider the Secondary Assignments Also--
1837 -- The code change is part of the eBRA Enhancement of SOE Form-----
1838 -- Check Balance Status
1839 l_procedure := 'populate_state_balance';
1840 hr_utility.set_location(l_package||l_procedure,10);
1841 -- Delete the state table
1842 state_tab.delete;
1843
1844 IF p_action_type = 'P' OR p_action_type = 'U' THEN
1845
1846 --------State----------------
1847 hr_utility.set_location(l_package||l_procedure,20);
1848 IF p_balance_status = 'Y' THEN
1849 IF run_actions_tab.COUNT>0 THEN
1850
1851 start_cnt := run_actions_tab.FIRST;
1852 end_cnt := run_actions_tab.LAST;
1853
1854 pay_us_balance_view_pkg.set_session_var('RUN','TRUE');
1855 pay_us_balance_view_pkg.set_session_var('QTD','FALSE');
1856 pay_us_balance_view_pkg.set_session_var('MTD','FALSE');
1857 pay_us_balance_view_pkg.set_session_var('PYDATE','FALSE');
1858 pay_us_balance_view_pkg.set_session_var('YTD','FALSE');
1859
1860 hr_utility.set_location(l_package||l_procedure,30);
1861
1862 FOR i IN start_cnt..end_cnt LOOP
1863 IF run_actions_tab.exists(i) THEN
1864 OPEN c_get_pre_state_run_rb(run_actions_tab(i).aaid);
1865 LOOP
1866 FETCH c_get_pre_state_run_rb
1867 INTO l_state_abbrev,
1868 l_rep_name,
1869 l_run_val,
1870 l_tax_type;
1871 EXIT WHEN c_get_pre_state_run_rb%NOTFOUND;
1872
1873 hr_utility.set_location(l_package||l_procedure,40);
1874 get_position_state(l_rep_name,l_tax_type,l_state_abbrev,l_found, l_pos);
1875
1876 IF l_found = FALSE THEN
1877 j := state_tab.COUNT + 1;
1878 state_tab(j).rep_name := l_rep_name;
1879 state_tab(j).tax_type := l_tax_type;
1880 state_tab(j).state_abbrev := l_state_abbrev;
1881 state_tab(j).cur_val := l_run_val;
1882 state_tab(j).ytd_val :=0;
1883 ELSE
1884 state_tab(l_pos).cur_val := state_tab(l_pos).cur_val + l_run_val;
1885 state_tab(l_pos).ytd_val := 0;
1886 END IF;
1887 END LOOP;
1888 hr_utility.set_location(l_package||l_procedure,50);
1889 CLOSE c_get_pre_state_run_rb;
1890 END IF;
1891 END LOOP;
1892 END IF;
1893 hr_utility.set_location(l_package||l_procedure,60);
1894
1895 IF state_tab.COUNT > 0 THEN
1896 IF master_actions_tab.COUNT>0 THEN
1897 start_cnt:=master_actions_tab.FIRST;
1898 end_cnt:=master_actions_tab.LAST;
1899
1900 pay_us_balance_view_pkg.set_session_var('RUN','FALSE');
1901 pay_us_balance_view_pkg.set_session_var('QTD','FALSE');
1902 pay_us_balance_view_pkg.set_session_var('MTD','FALSE');
1903 pay_us_balance_view_pkg.set_session_var('PYDATE','FALSE');
1904 pay_us_balance_view_pkg.set_session_var('YTD','TRUE');
1905
1906 hr_utility.set_location(l_package||l_procedure,70);
1907 FOR i IN start_cnt..end_cnt LOOP
1908 IF master_actions_tab.exists(i) THEN
1909 OPEN c_get_pre_state_ytd_rb(master_actions_tab(i).aaid);
1910 LOOP
1911 FETCH c_get_pre_state_ytd_rb into l_ytd_val,l_rep_name,l_tax_type,l_state_abbrev;
1912 EXIT WHEN c_get_pre_state_ytd_rb%NOTFOUND;
1913
1914 hr_utility.set_location(l_package||l_procedure,80);
1915 get_position_state(l_rep_name,l_tax_type,l_state_abbrev,l_found, l_pos);
1916
1917 IF l_found = TRUE THEN
1918 state_tab(l_pos).ytd_val := state_tab(l_pos).ytd_val + l_ytd_val;
1919 ELSE
1920 k := state_tab.count+1;
1921 state_tab(k).rep_name :=l_rep_name;
1922 state_tab(k).cur_val :=0;
1923 state_tab(k).state_abbrev := l_state_abbrev;
1924 state_tab(k).tax_type := l_tax_type;
1925 state_tab(k).ytd_val :=l_ytd_val;
1926 END IF;
1927 END LOOP;
1928 CLOSE c_get_pre_state_ytd_rb;
1929 END IF;
1930 hr_utility.set_location(l_package||l_procedure,90);
1931 END LOOP;
1932 END IF;
1933 END IF;
1934
1935
1936 ELSE -- Status Not Valid
1937 hr_utility.set_location(l_package||l_procedure,100);
1938 IF run_actions_tab.COUNT>0 THEN
1939 start_cnt := run_actions_tab.FIRST;
1940 end_cnt := run_actions_tab.LAST;
1941
1942 pay_us_balance_view_pkg.set_session_var('RUN','TRUE');
1943 pay_us_balance_view_pkg.set_session_var('QTD','FALSE');
1944 pay_us_balance_view_pkg.set_session_var('MTD','FALSE');
1945 pay_us_balance_view_pkg.set_session_var('PYDATE','FALSE');
1946 pay_us_balance_view_pkg.set_session_var('YTD','FALSE');
1947
1948 FOR i IN start_cnt..end_cnt LOOP
1949 IF run_actions_tab.exists(i) THEN
1950 OPEN c_get_pre_state_run_rr(run_actions_tab(i).aaid);
1951 LOOP
1952 FETCH c_get_pre_state_run_rr
1953 INTO l_state_abbrev,
1954 l_rep_name,
1955 l_run_val,
1956 l_tax_type;
1957 EXIT WHEN c_get_pre_state_run_rr%NOTFOUND;
1958
1959 hr_utility.set_location(l_package||l_procedure,110);
1960 get_position_state(l_rep_name,l_tax_type,l_state_abbrev,l_found, l_pos);
1961
1962 IF l_found = FALSE THEN
1963 j := state_tab.COUNT + 1;
1964 state_tab(j).rep_name := l_rep_name;
1965 state_tab(j).tax_type := l_tax_type;
1966 state_tab(j).state_abbrev := l_state_abbrev;
1967 state_tab(j).cur_val := l_run_val;
1968 state_tab(j).ytd_val :=0;
1969 ELSE
1970 state_tab(l_pos).cur_val := state_tab(l_pos).cur_val + l_run_val;
1971 state_tab(l_pos).ytd_val := 0;
1972 END IF;
1973 END LOOP;
1974 hr_utility.set_location(l_package||l_procedure,120);
1975 CLOSE c_get_pre_state_run_rr;
1976 END IF;
1977 END LOOP;
1978 END IF;
1979 hr_utility.set_location(l_package||l_procedure,130);
1980
1981 IF state_tab.COUNT > 0 THEN
1982 hr_utility.set_location(l_package||l_procedure,140);
1983 IF master_actions_tab.COUNT>0 THEN
1984 start_cnt:=master_actions_tab.FIRST;
1985 end_cnt:=master_actions_tab.LAST;
1986
1987 pay_us_balance_view_pkg.set_session_var('RUN','FALSE');
1988 pay_us_balance_view_pkg.set_session_var('QTD','FALSE');
1989 pay_us_balance_view_pkg.set_session_var('MTD','FALSE');
1990 pay_us_balance_view_pkg.set_session_var('PYDATE','FALSE');
1991 pay_us_balance_view_pkg.set_session_var('YTD','TRUE');
1992
1993 FOR i IN start_cnt..end_cnt LOOP
1994 IF master_actions_tab.exists(i) THEN
1995 OPEN c_get_pre_state_ytd_rr(master_actions_tab(i).aaid);
1996 LOOP
1997 FETCH c_get_pre_state_ytd_rr
1998 INTO l_ytd_val,
1999 l_rep_name,
2000 l_tax_type,
2001 l_state_abbrev;
2002 EXIT WHEN c_get_pre_state_ytd_rr%NOTFOUND;
2003
2004 hr_utility.set_location(l_package||l_procedure,150);
2005 get_position_state(l_rep_name,l_tax_type,l_state_abbrev,l_found, l_pos);
2006
2007 IF l_found = TRUE THEN
2008 state_tab(l_pos).ytd_val := state_tab(l_pos).ytd_val + l_ytd_val;
2009 ELSE
2010 k := state_tab.count+1;
2011 state_tab(k).rep_name :=l_rep_name;
2012 state_tab(k).cur_val :=0;
2013 state_tab(k).state_abbrev := l_state_abbrev;
2014 state_tab(k).tax_type := l_tax_type;
2015 state_tab(k).ytd_val :=l_ytd_val;
2016 END IF;
2017 END LOOP;
2018 CLOSE c_get_pre_state_ytd_rr;
2019 END IF;
2020 END LOOP;
2021 END IF;
2022
2023 END IF;
2024
2025 hr_utility.set_location(l_package||l_procedure,150);
2026 -- END IF;
2027
2028 END IF;
2029
2030 ELSE -- SOE for Run Action
2031
2032 hr_utility.set_location(l_package||l_procedure,160);
2033 pay_us_balance_view_pkg.set_session_var('YTD','TRUE');
2034 pay_us_balance_view_pkg.set_session_var('RUN','TRUE');
2035 pay_us_balance_view_pkg.set_session_var('QTD','FALSE');
2036 pay_us_balance_view_pkg.set_session_var('MTD','FALSE');
2037 pay_us_balance_view_pkg.set_session_var('PYDATE','FALSE');
2038
2039 -------------Start State Run--------------------------------------
2040 --State taxes
2041 --Use run Balances
2042 i := 0;
2043 hr_utility.set_location(l_package||l_procedure,170);
2044 IF p_balance_status = 'Y' THEN
2045 hr_utility.set_location(l_package||l_procedure,180);
2046 OPEN get_valid_taxes_state_rb(p_assignment_action_id);
2047 LOOP
2048 FETCH get_valid_taxes_state_rb
2049 INTO state_tab(i).state_abbrev
2050 , state_tab(i).rep_name
2051 , state_tab(i).cur_val
2052 , state_tab(i).tax_type
2053 , state_tab(i).juris_code
2054 , state_tab(i).ytd_val;
2055 EXIT WHEN get_valid_taxes_state_rb%NOTFOUND;
2056
2057 i := i + 1;
2058 END LOOP;
2059 hr_utility.set_location(l_package||l_procedure,190);
2060 CLOSE get_valid_taxes_state_rb;
2061
2062 --Use Run result
2063 ELSE
2064 hr_utility.set_location(l_package||l_procedure,200);
2065 OPEN get_valid_taxes_state_rr(p_assignment_action_id);
2066 LOOP
2067 FETCH get_valid_taxes_state_rr
2068 INTO state_tab(i).state_abbrev
2069 , state_tab(i).rep_name
2070 , state_tab(i).cur_val
2071 , state_tab(i).tax_type
2072 , state_tab(i).juris_code
2073 , state_tab(i).ytd_val;
2074 EXIT WHEN get_valid_taxes_state_rr%NOTFOUND;
2075 i := i + 1;
2076 END LOOP;
2077 CLOSE get_valid_taxes_state_rr;
2078 hr_utility.set_location(l_package||l_procedure,210);
2079 END IF;
2080 END IF;
2081 hr_utility.set_location(l_package||l_procedure,220);
2082
2083 -- Populate State Table of values for SOE form
2084 IF state_tab.count > 0 THEN
2085 start_cnt := state_tab.FIRST;
2086 end_cnt := state_tab.LAST;
2087
2088 FOR i IN start_cnt..end_cnt LOOP
2089 IF state_tab.exists(i) THEN
2090 hr_utility.set_location(l_package||l_procedure,230);
2091 state_tab(i).rep_name := REPLACE(state_tab(i).rep_name, 'EE ','');
2092 state_tab(i).rep_name := state_tab(i).rep_name||' ('||state_tab(i).state_abbrev||')';
2093
2094 IF state_tab(i).tax_type = 'STEIC' THEN
2095
2096 state_tab(i).cur_val := -1 * state_tab(i).cur_val;
2097 state_tab(i).ytd_val := -1 * state_tab(i).ytd_val;
2098
2099 p_steic_curr_val := state_tab(i).cur_val;
2100 p_steic_ytd_val := state_tab(i).ytd_val;
2101
2102 END IF;
2103
2104 p_dedn_tab(i).rep_name := state_tab(i).rep_name;
2105 p_dedn_tab(i).cur_val := state_tab(i).cur_val;
2106 p_dedn_tab(i).ytd_val := state_tab(i).ytd_val ;
2107 END IF;
2108 END LOOP;
2109 END IF;
2110 hr_utility.set_location(l_package||l_procedure,230);
2111
2112 EXCEPTION
2113 WHEN others THEN
2114 hr_utility.set_location(l_package||l_procedure,240);
2115 raise_application_error(-20101, 'Error in '|| l_package||l_procedure || ' - ' || sqlerrm);
2116
2117 END populate_state_balance;
2118
2119
2120
2121 /*****************************************************************************
2122 Name : populate_local_balance
2123 Purpose : This procedure populates a PL/SQL table with all the local deductions
2124 elements for SOE form.
2125 *****************************************************************************/
2126
2127 PROCEDURE populate_local_balance(p_assignment_action_id in number,
2128 p_balance_status in varchar2,
2129 p_action_type in varchar2,
2130 p_dedn_tab out nocopy dedn)
2131 IS
2132
2133 l_county_state_code varchar2(2);
2134 l_county_code pay_us_counties.county_code%type;
2135 l_county_name pay_us_counties.county_name%type;
2136
2137 l_school_code pay_us_school_dsts.school_dst_code%type;
2138 l_school_name pay_us_school_dsts.school_dst_name%type;
2139 l_school_jd pay_us_local_taxes_v.jurisdiction_code%type;
2140 l_juris_code pay_us_state_taxes_v.jurisdiction_code%type;
2141
2142 l_count number;
2143 l_run_amount number;
2144 l_curr_amount number;
2145 l_ytd_amount number;
2146 l_tax_type pay_us_local_taxes_v.tax_type_code%type;
2147
2148 start_cnt number;
2149 end_cnt number;
2150 i number :=0;
2151 j number :=0;
2152 k number :=0;
2153 l_found boolean;
2154 l_pos number;
2155
2156 l_rep_name pay_us_local_taxes_v.user_reporting_name%TYPE;
2157 l_city_name pay_us_local_taxes_v.city_name%TYPE;
2158 l_run_val number;
2159 l_ytd_val number;
2160
2161 l_ytd_value number;
2162 l_master_action_id number;
2163 l_procedure varchar2(22) ;
2164
2165 /***
2166 ***Start Local Balances Cursors when balances are not valid for eBRA***
2167 ***/
2168 -- Cursor to get local balances from run results
2169 CURSOR get_valid_taxes_local_rr(l_assignment_action_id number)
2170 IS
2171 select city_name ,
2172 jurisdiction_code,
2173 tax_type_code,
2174 user_reporting_name,
2175 run_val,
2176 ytd_val
2177 from pay_us_local_taxes_v
2178 where ee_or_er_code = 'EE'
2179 and assignment_action_id = l_assignment_action_id
2180 order by user_reporting_name;
2181
2182
2183 --Cursor to get Run Values
2184 CURSOR c_get_pre_local_run_rr(cp_run_action_id number)
2185 IS
2186 select city_name,
2187 jurisdiction_code,
2188 tax_type_code,
2189 user_reporting_name,
2190 sum(run_val)
2191 from pay_us_local_taxes_v pt
2192 where pt.ee_or_er_code = 'EE'
2193 and pt.assignment_action_id = cp_run_action_id
2194 group by user_reporting_name, city_name,jurisdiction_code, tax_type_code
2195 order by user_reporting_name;
2196
2197 -- Cursor to get YTD Value
2198 CURSOR c_get_pre_local_ytd_rr(cp_master_action_id number)
2199 IS
2200 select city_name,
2201 sum(pt.ytd_val) ,
2202 jurisdiction_code,
2203 tax_type_code,
2204 user_reporting_name
2205 from pay_us_local_taxes_v pt
2206 where pt.ee_or_er_code = 'EE'
2207 and pt.assignment_action_id = cp_master_action_id
2208 group by user_reporting_name, city_name,jurisdiction_code, tax_type_code
2209 order by user_reporting_name;
2210
2211 /***
2212 ***End Local Balances Cursors when balances are not valid for eBRA***
2213 ***/
2214
2215
2216 /***
2217 ***Start Local Balances Cursors when balances are valid for eBRA***
2218 ***/
2219
2220 -- Cursor to get local balances from run balances
2221 CURSOR get_valid_taxes_local_rb(l_assignment_action_id number)
2222 IS
2223 select city_name ,
2224 jurisdiction_code,
2225 tax_type_code,
2226 user_reporting_name,
2227 run_val,
2228 ytd_val
2229 FROM pay_us_local_taxes_rbr_v
2230 WHERE ee_or_er_code = 'EE'
2231 AND assignment_action_id = l_assignment_action_id
2232 order by user_reporting_name;
2233
2234 --Cursor to get Run Values
2235 CURSOR c_get_pre_local_run_rb(cp_run_action_id NUMBER)
2236 IS
2237 select city_name,
2238 jurisdiction_code,
2239 tax_type_code,
2240 user_reporting_name,
2241 sum(run_val)
2242 from pay_us_local_taxes_rbr_v pt
2243 where pt.ee_or_er_code = 'EE'
2244 and pt.assignment_action_id = cp_run_action_id
2245 group by user_reporting_name, city_name,jurisdiction_code, tax_type_code
2246 order by user_reporting_name;
2247
2248 -- Cursor to get YTD Value
2249 CURSOR c_get_pre_local_ytd_rb(cp_master_action_id NUMBER)
2250 IS
2251 select city_name ,
2252 sum(pt.ytd_val),
2253 jurisdiction_code,
2254 tax_type_code,
2255 user_reporting_name
2256 from pay_us_local_taxes_rbr_v pt
2257 where pt.ee_or_er_code = 'EE'
2258 and pt.assignment_action_id = cp_master_action_id
2259 group by user_reporting_name, city_name,jurisdiction_code, tax_type_code
2260 order by user_reporting_name;
2261
2262 /***
2263 ***End Local Balances Cursors when balances are not valid for eBRA***
2264 ***/
2265
2266 -- Procedure to get the position of the local deductions in the plsql table
2267 -- If the element exists it will return the position otherwise will return
2268 -- new index where new element will be stored. The reporting name with the
2269 -- same city , jurisdiction or tax_type are grouped for SOE
2270 PROCEDURE get_position_local(p_rep_name in pay_us_local_taxes_v.user_reporting_name%TYPE ,
2271 p_tax_type_code in pay_us_local_taxes_v.tax_type_code%TYPE ,
2272 p_jurisdiction_code in pay_us_local_taxes_v.jurisdiction_code%type,
2273 p_city_name in pay_us_local_taxes_v.city_name%type,
2274 p_found out nocopy boolean,
2275 p_index out nocopy number)
2276 IS
2277
2278 st_cnt number;
2279 ed_cnt number;
2280 p_cnt number;
2281
2282 BEGIN
2283 p_found := FALSE;
2284 p_index := 0;
2285
2286 p_cnt := local_tab.COUNT;
2287
2288 IF p_cnt = 0 THEN
2289
2290 p_found := FALSE;
2291 p_index := 0;
2292 return;
2293
2294 ELSE
2295 st_cnt := local_tab.FIRST;
2296 ed_cnt := local_tab.LAST;
2297 FOR i in st_cnt.. ed_cnt LOOP
2298 IF local_tab.exists(i) THEN
2299 IF p_rep_name = local_tab(i).rep_name
2300 and p_tax_type_code=local_tab(i).tax_type
2301 and p_jurisdiction_code=local_tab(i).juris_code
2302 and p_city_name =local_tab(i).city_name
2303
2304 THEN
2305 p_index := i;
2306 p_found := TRUE;
2307 return;
2308
2309 END IF;
2310 END IF;
2311 END LOOP;
2312 END IF;
2313
2314 END; /* get_position_local */
2315
2316
2317 BEGIN
2318 l_procedure := 'populate_local_balance';
2319
2320 hr_utility.set_location(l_package||l_procedure,10);
2321 -- delete local tables
2322 local_tab.delete;
2323
2324 hr_utility.set_location(l_package||l_procedure,20);
2325 IF p_action_type = 'P' OR p_action_type = 'U' THEN
2326
2327 hr_utility.set_location(l_package||l_procedure,30);
2328 IF p_balance_status = 'Y' THEN
2329
2330 IF run_actions_tab.COUNT>0 THEN
2331 start_cnt := run_actions_tab.FIRST;
2332 end_cnt := run_actions_tab.LAST;
2333
2334 pay_us_balance_view_pkg.set_session_var('RUN','TRUE');
2335 pay_us_balance_view_pkg.set_session_var('QTD','FALSE');
2336 pay_us_balance_view_pkg.set_session_var('MTD','FALSE');
2337 pay_us_balance_view_pkg.set_session_var('PYDATE','FALSE');
2338 pay_us_balance_view_pkg.set_session_var('YTD','FALSE');
2339
2340 FOR i IN start_cnt..end_cnt LOOP
2341 IF run_actions_tab.exists(i) THEN
2342 OPEN c_get_pre_local_run_rb(run_actions_tab(i).aaid);
2343 LOOP
2344 FETCH c_get_pre_local_run_rb
2345 INTO l_city_name
2346 ,l_juris_code
2347 ,l_tax_type
2348 ,l_rep_name
2349 ,l_run_val;
2350 EXIT WHEN c_get_pre_local_run_rb%NOTFOUND;
2351
2352 hr_utility.set_location(l_package||l_procedure,40);
2353 get_position_local(l_rep_name,l_tax_type,l_juris_code,l_city_name,l_found, l_pos);
2354
2355 IF l_found = FALSE THEN
2356 j := local_tab.COUNT + 1;
2357 local_tab(j).rep_name := l_rep_name;
2358 local_tab(j).tax_type := l_tax_type;
2359 local_tab(j).juris_code := l_juris_code;
2360 local_tab(j).cur_val := l_run_val;
2361 local_tab(j).ytd_val := 0;
2362 local_tab(j).city_name := l_city_name;
2363 ELSE
2364 local_tab(l_pos).cur_val := local_tab(l_pos).cur_val + l_run_val;
2365 local_tab(l_pos).ytd_val := 0;
2366 END IF;
2367 END LOOP;
2368 hr_utility.set_location(l_package||l_procedure,50);
2369 CLOSE c_get_pre_local_run_rb;
2370 END IF;
2371 END LOOP;
2372 END IF;
2373
2374 hr_utility.set_location(l_package||l_procedure,60);
2375 IF local_tab.COUNT > 0 THEN
2376 IF master_actions_tab.COUNT>0 THEN
2377 start_cnt:=master_actions_tab.FIRST;
2378 end_cnt:=master_actions_tab.LAST;
2379
2380 pay_us_balance_view_pkg.set_session_var('RUN','FALSE');
2381 pay_us_balance_view_pkg.set_session_var('QTD','FALSE');
2382 pay_us_balance_view_pkg.set_session_var('MTD','FALSE');
2383 pay_us_balance_view_pkg.set_session_var('PYDATE','FALSE');
2384 pay_us_balance_view_pkg.set_session_var('YTD','TRUE');
2385
2386 hr_utility.set_location(l_package||l_procedure,60);
2387 FOR i IN start_cnt..end_cnt LOOP
2388 IF master_actions_tab.exists(i) THEN
2389 OPEN c_get_pre_local_ytd_rb(master_actions_tab(i).aaid);
2390 LOOP
2391 FETCH c_get_pre_local_ytd_rb into l_city_name,l_ytd_val,l_juris_code,l_tax_type,l_rep_name;
2392 EXIT WHEN c_get_pre_local_ytd_rb%NOTFOUND;
2393
2394 hr_utility.set_location(l_package||l_procedure,70);
2395 get_position_local(l_rep_name,l_tax_type,l_juris_code,l_city_name,l_found, l_pos);
2396
2397 IF l_found = TRUE THEN
2398 local_tab(l_pos).ytd_val := local_tab(l_pos).ytd_val + l_ytd_val;
2399 ELSE
2400 k := local_tab.count+1;
2401 local_tab(k).rep_name :=l_rep_name;
2402 local_tab(k).cur_val :=0;
2403 local_tab(k).juris_code := l_juris_code;
2404 local_tab(k).tax_type := l_tax_type;
2405 local_tab(k).ytd_val :=l_ytd_val;
2406 local_tab(k).city_name := l_city_name;
2407 END IF;
2408 END LOOP;
2409 hr_utility.set_location(l_package||l_procedure,80);
2410 CLOSE c_get_pre_local_ytd_rb;
2411 END IF;
2412 END LOOP;
2413 END IF;
2414 END IF;
2415
2416 ELSE -- Invalid Local Balances for eBRA
2417
2418 hr_utility.set_location(l_package||l_procedure,90);
2419 IF run_actions_tab.COUNT>0 THEN
2420 start_cnt := run_actions_tab.FIRST;
2421 end_cnt := run_actions_tab.LAST;
2422
2423 pay_us_balance_view_pkg.set_session_var('RUN','TRUE');
2424 pay_us_balance_view_pkg.set_session_var('QTD','FALSE');
2425 pay_us_balance_view_pkg.set_session_var('MTD','FALSE');
2426 pay_us_balance_view_pkg.set_session_var('PYDATE','FALSE');
2427 pay_us_balance_view_pkg.set_session_var('YTD','FALSE');
2428
2429 FOR i IN start_cnt..end_cnt LOOP
2430 IF run_actions_tab.exists(i)THEN
2431 OPEN c_get_pre_local_run_rr(run_actions_tab(i).aaid);
2432 LOOP
2433 FETCH c_get_pre_local_run_rr
2434 INTO l_city_name
2435 ,l_juris_code
2436 ,l_tax_type
2437 ,l_rep_name
2438 ,l_run_val;
2439 EXIT WHEN c_get_pre_local_run_rr%NOTFOUND;
2440
2441 hr_utility.set_location(l_package||l_procedure,100);
2442 get_position_local(l_rep_name,l_tax_type,l_juris_code,l_city_name,l_found, l_pos);
2443
2444 IF l_found = FALSE THEN
2445 j := local_tab.COUNT + 1;
2446 local_tab(j).rep_name := l_rep_name;
2447 local_tab(j).tax_type := l_tax_type;
2448 local_tab(j).juris_code := l_juris_code;
2449 local_tab(j).cur_val := l_run_val;
2450 local_tab(j).ytd_val :=0;
2451 local_tab(j).city_name :=l_city_name; -- Bug 3138331
2452 ELSE
2453 local_tab(l_pos).cur_val := local_tab(l_pos).cur_val + l_run_val;
2454 local_tab(l_pos).ytd_val := 0;
2455 END IF;
2456 END LOOP;
2457 CLOSE c_get_pre_local_run_rr;
2458 END IF;
2459 END LOOP;
2460 END IF;
2461
2462 hr_utility.set_location(l_package||l_procedure,110);
2463 IF local_tab.COUNT > 0 THEN
2464 IF master_actions_tab.COUNT>0 THEN
2465 start_cnt := master_actions_tab.FIRST;
2466 end_cnt := master_actions_tab.LAST;
2467
2468 pay_us_balance_view_pkg.set_session_var('RUN','FALSE');
2469 pay_us_balance_view_pkg.set_session_var('QTD','FALSE');
2470 pay_us_balance_view_pkg.set_session_var('MTD','FALSE');
2471 pay_us_balance_view_pkg.set_session_var('PYDATE','FALSE');
2472 pay_us_balance_view_pkg.set_session_var('YTD','TRUE');
2473
2474 hr_utility.set_location(l_package||l_procedure,120);
2475 FOR i IN start_cnt..end_cnt LOOP
2476 IF master_actions_tab.exists(i) THEN
2477 OPEN c_get_pre_local_ytd_rr(master_actions_tab(i).aaid);
2478 LOOP
2479 FETCH c_get_pre_local_ytd_rr into l_city_name,l_ytd_val,l_juris_code,l_tax_type,l_rep_name;
2480 EXIT WHEN c_get_pre_local_ytd_rr%NOTFOUND;
2481
2482 hr_utility.set_location(l_package||l_procedure,130);
2483 get_position_local(l_rep_name,l_tax_type,l_juris_code,l_city_name,l_found, l_pos);
2484
2485 IF l_found = TRUE THEN
2486 local_tab(l_pos).ytd_val := local_tab(l_pos).ytd_val + l_ytd_val;
2487 ELSE
2488 k := local_tab.count+1;
2489 local_tab(k).rep_name :=l_rep_name;
2490 local_tab(k).cur_val :=0;
2491 local_tab(k).juris_code := l_juris_code;
2492 local_tab(k).tax_type := l_tax_type;
2493 local_tab(k).ytd_val :=l_ytd_val;
2494 local_tab(k).city_name := l_city_name;
2495 END IF;
2496 END LOOP;
2497 CLOSE c_get_pre_local_ytd_rr;
2498 END IF;
2499 END LOOP;
2500 END IF;
2501
2502 hr_utility.set_location(l_package||l_procedure,140);
2503 END IF;
2504 END IF;
2505
2506 ELSE -- SOE for Run is viewed
2507
2508 hr_utility.set_location(l_package||l_procedure,150);
2509 pay_us_balance_view_pkg.set_session_var('YTD','TRUE');
2510 pay_us_balance_view_pkg.set_session_var('RUN','TRUE');
2511 pay_us_balance_view_pkg.set_session_var('QTD','FALSE');
2512 pay_us_balance_view_pkg.set_session_var('MTD','FALSE');
2513 pay_us_balance_view_pkg.set_session_var('PYDATE','FALSE');
2514
2515 i := 0;
2516
2517 ------- Start Local Taxes-------------------
2518 IF p_balance_status ='Y' THEN
2519
2520 hr_utility.set_location(l_package||l_procedure,160);
2521 OPEN get_valid_taxes_local_rb(p_assignment_action_id);
2522 LOOP
2523 FETCH get_valid_taxes_local_rb
2524 INTO local_tab(i).city_name,
2525 local_tab(i).juris_code,
2526 local_tab(i).tax_type,
2527 local_tab(i).rep_name,
2528 local_tab(i).cur_val,
2529 local_tab(i).ytd_val;
2530
2531 EXIT WHEN get_valid_taxes_local_rb%NOTFOUND;
2532 i := i + 1;
2533
2534 END LOOP;
2535 CLOSE get_valid_taxes_local_rb;
2536 ELSE
2537 hr_utility.set_location(l_package||l_procedure,170);
2538
2539 OPEN get_valid_taxes_local_rr(p_assignment_action_id);
2540 LOOP
2541 FETCH get_valid_taxes_local_rr
2542 INTO local_tab(i).city_name,
2543 local_tab(i).juris_code,
2544 local_tab(i).tax_type,
2545 local_tab(i).rep_name,
2546 local_tab(i).cur_val,
2547 local_tab(i).ytd_val;
2548
2549 EXIT WHEN get_valid_taxes_local_rr%NOTFOUND;
2550 i := i + 1;
2551
2552 END LOOP;
2553 hr_utility.set_location(l_package||l_procedure,180);
2554 CLOSE get_valid_taxes_local_rr;
2555 END IF;
2556 hr_utility.set_location(l_package||l_procedure,190);
2557 END IF;
2558
2559 hr_utility.set_location(l_package||l_procedure,200);
2560 -- Populate local deduction table for SOE
2561 l_county_code := '000';
2562 l_county_state_code :='00';
2563
2564 IF local_tab.count > 0 THEN
2565 hr_utility.set_location(l_package||l_procedure,210);
2566 start_cnt := local_tab.FIRST;
2567 end_cnt := local_tab.LAST;
2568
2569 FOR i IN start_cnt..end_cnt LOOP
2570 IF local_tab.exists(i) THEN
2571 IF local_tab(i).tax_type = 'CITY' OR local_tab(i).tax_type = 'HT' THEN
2572
2573 hr_utility.set_location(l_package||l_procedure,220);
2574 local_tab(i).rep_name := REPLACE(local_tab(i).rep_name, 'EE ', '');
2575 local_tab(i).rep_name := local_tab(i).rep_name||' ('||local_tab(i).city_name||')';
2576
2577 ELSIF local_tab(i).tax_type = 'COUNTY' THEN
2578 hr_utility.set_location(l_package||l_procedure,230);
2579 select county_name into l_county_name
2580 from pay_us_counties
2581 where county_code = substr(local_tab(i).juris_code,4,3)
2582 and state_code = substr(local_tab(i).juris_code,1,2);
2583
2584 local_tab(i).rep_name := REPLACE(local_tab(i).rep_name, 'EE ', '');
2585 local_tab(i).rep_name := local_tab(i).rep_name||' ('||l_county_name||')';
2586
2587 -- Bug 3250653
2588 ELSIF local_tab(i).tax_type = 'SCHOOL' THEN
2589 hr_utility.set_location(l_package||l_procedure,240);
2590 select distinct school_dst_name into l_school_name
2591 from pay_us_school_dsts --Bug 3412605
2592 where school_dst_code = substr(local_tab(i).juris_code,4,5)
2593 and state_code = substr(local_tab(i).juris_code,1,2);
2594
2595 l_school_code :=substr(local_tab(i).juris_code,4,5);
2596 l_school_jd := substr(local_tab(i).juris_code,1,2)||'-'||l_school_code;
2597
2598 local_tab(i).rep_name := REPLACE(local_tab(i).rep_name, 'EE ','');
2599 local_tab(i).rep_name := local_tab(i).rep_name||' ('||l_school_name||'-'||l_school_code||')';
2600 END IF;
2601 hr_utility.set_location(l_package||l_procedure,240);
2602
2603 hr_utility.set_location(l_package||l_procedure,250);
2604 p_dedn_tab(i).rep_name := local_tab(i).rep_name;
2605 p_dedn_tab(i).cur_val := local_tab(i).cur_val;
2606 p_dedn_tab(i).ytd_val := local_tab(i).ytd_val ;
2607 END IF;
2608 END LOOP;
2609 END IF; -- Prepayment or Quick Pay Prepayment
2610 hr_utility.set_location(l_package||l_procedure,260);
2611
2612 EXCEPTION
2613 WHEN others THEN
2614 hr_utility.set_location(l_package||l_procedure,270);
2615 raise_application_error(-20101, 'Error in '||l_package||l_procedure || ' - ' || sqlerrm);
2616
2617 END populate_local_balance;
2618
2619
2620
2621 /***************************************************************************
2622 Name : populate_dedn_balance
2623 Purpose : This procedure populates the plsql table with the Pre-Tax and
2624 and After Tax Deduction elements for SOE form.
2625 ***************************************************************************/
2626 PROCEDURE populate_dedn_balance(p_assignment_action_id in number,
2627 p_pre_balance_status in varchar2,
2628 p_aft_balance_status in varchar2,
2629 p_action_type in varchar2,
2630 p_dedn_tab out nocopy dedn)
2631 IS
2632
2633 -- Cursor to get tax deduction elements using run balances
2634 CURSOR c_get_dedn_elements_rb(c_run_assact_id number) IS
2635 select ytd_val,
2636 reporting_name_alt,
2637 run_val,
2638 element_type_id
2639 from pay_us_deductions_rbr_v
2640 where assignment_action_id = c_run_assact_id
2641 order by reporting_name_alt;
2642
2643 -- Cursor to get run values of tax deductions when balances are valid
2644 CURSOR c_get_dedn_run_rb(cp_run_action_id number) IS
2645 select reporting_name_alt,
2646 run_val,
2647 element_type_id
2648 from pay_us_deductions_rbr_v pt
2649 where pt.assignment_action_id = cp_run_action_id
2650 order by reporting_name_alt;
2651
2652 -- Cursor to get ytd values of tax deductions when balances are valid for master action
2653 CURSOR c_get_dedn_ytd_rb(cp_master_action_id number) IS
2654 select ytd_val,
2655 reporting_name_alt,
2656 element_type_id
2657 from pay_us_deductions_rbr_v pt
2658 where pt.assignment_action_id = cp_master_action_id;
2659
2660
2661 -- Cursor to other deduction elements from element entries
2662 -- Bug 4966938
2663 CURSOR c_get_dedn_elements(cp_date_paid date,
2664 cp_assignment_action_id number) IS
2665 select distinct
2666 pet.element_type_id,
2667 nvl(pet.reporting_name, pet.element_name),
2668 pet.element_information10,
2669 pet.business_group_id,
2670 pet.processing_priority
2671 from pay_assignment_actions paa ,
2672 pay_assignment_actions paa1 ,
2673 pay_payroll_actions ppa ,
2674 pay_run_results prr ,
2675 pay_element_types_f pet ,
2676 pay_element_classifications pec
2677 where paa.assignment_action_id = cp_assignment_action_id
2678 and paa1.assignment_id = paa.assignment_id
2679 -- and paa1.source_action_id is not null --for bug 5332346
2680 and ppa.payroll_action_id = paa1.payroll_action_id
2681 and ppa.effective_date between trunc(cp_date_paid,'Y') and cp_date_paid
2682 and prr.assignment_action_id = paa1.assignment_action_id
2683 and prr.source_type in ( 'E', 'I' )
2684 and pet.element_type_id >= 0
2685 and pet.element_information10 is not null
2686 and nvl(ppa.date_earned,ppa.effective_date) between pet.effective_start_date and pet.effective_end_date
2687 and prr.element_type_id + 0 = pet.element_type_id
2688 and pec.classification_name IN ('Pre-Tax Deductions',
2689 'Voluntary Deductions',
2690 'Involuntary Deductions')
2691 and pet.classification_id = pec.classification_id
2692 order by pet.processing_priority,nvl(pet.reporting_name, pet.element_name); --bug4743188
2693
2694 /*
2695 CURSOR c_get_dedn_elements(cp_date_earned date,
2696 cp_assignment_id number) IS
2697 select /*+ ORDERED distinct
2698 pet.element_type_id,
2699 nvl(pet.reporting_name, pet.element_name),
2700 pet.element_information10,
2701 pet.business_group_id,
2702 pet.processing_priority
2703 from pay_element_entries_f pee,
2704 pay_run_results prr,
2705 pay_element_types_f pet,
2706 pay_element_classifications pec
2707 where pee.assignment_id = cp_assignment_id
2708 and pee.effective_end_date >= trunc(cp_date_earned, 'Y')
2709 and pee.effective_start_date <= cp_date_earned
2710 and prr.source_id = pee.element_entry_id
2711 and prr.source_type in ( 'E', 'I' )
2712 and pec.classification_name IN ('Pre-Tax Deductions',
2713 'Voluntary Deductions',
2714 'Involuntary Deductions')
2715 and pet.classification_id = pec.classification_id
2716 and pet.element_information10 is not null
2717 and pet.effective_start_date =
2718 (select max(pet1.effective_start_date)
2719 from pay_element_types_f pet1
2720 where pet1.element_type_id = pet.element_type_id
2721 and pet1.effective_start_date <= cp_date_earned)
2722 and prr.element_type_id + 0 = pet.element_type_id
2723 order by pet.processing_priority;
2724 */
2725 l_found1 number :=0;
2726
2727 l_rep_name pay_us_deductions_v.reporting_name_alt%type ;
2728 l_run_val number;
2729 l_ytd_val number;
2730 l_found boolean;
2731 l_pos number;
2732 l_procedure varchar2(21) ;
2733
2734
2735 l_element_type_id pay_element_types_f.element_type_id%type;
2736 l_element_reporting_name pay_element_types_f.reporting_name%type;
2737 l_element_information10 pay_element_types_f.element_information10%type;
2738 l_assignment_id pay_assignment_actions.assignment_id%type;
2739 l_assignment_action_id pay_assignment_actions.assignment_action_id%type;
2740 l_tax_unit_id pay_assignment_actions.tax_unit_id%type;
2741 l_date_earned pay_payroll_actions.date_earned%type;
2742 l_date_paid pay_payroll_actions.effective_date%type;
2743 l_business_group_id pay_element_types_f.business_group_id%type;
2744 l_processing_priority pay_element_types_f.processing_priority%type;
2745
2746
2747 -- Procedure to get the position of the deductions in the plsql
2748 -- table. If the element exists it will return the position otherwise will return
2749 -- new index where new element will be stored. The elements with the same
2750 -- reporting name will be grouped for SOE
2751 PROCEDURE get_position(p_rep_name in pay_us_deductions_v.reporting_name_alt%type,
2752 p_found out nocopy boolean,
2753 p_index out nocopy number,
2754 p_dedn_tab in dedn)
2755 IS
2756
2757 st_cnt number;
2758 ed_cnt number;
2759 p_cnt number;
2760
2761 BEGIN
2762 p_found := FALSE;
2763 p_index := 0;
2764
2765 p_cnt := p_dedn_tab.COUNT;
2766
2767 IF p_cnt = 0 THEN
2768
2769 p_found := FALSE;
2770 p_index := 0;
2771 return;
2772
2773 ELSE
2774 st_cnt := p_dedn_tab.FIRST;
2775 ed_cnt := p_dedn_tab.LAST;
2776 FOR i in st_cnt.. ed_cnt LOOP
2777 IF p_dedn_tab.exists(i) THEN
2778 IF p_rep_name = p_dedn_tab(i).rep_name THEN
2779
2780 p_index := i;
2781 p_found := TRUE;
2782 return;
2783
2784 END IF;
2785 END IF;
2786 END LOOP;
2787 END IF;
2788 END; /* get_position */
2789
2790 BEGIN
2791 l_procedure := 'populate_dedn_balance';
2792 --hr_utility.trace_on(null,'SOE');
2793 hr_utility.set_location(l_package||l_procedure,10);
2794 deduction_elements_tab.delete;
2795 p_dedn_tab.delete;
2796
2797 -- SOE for Prepayment/Quick pay prepayment is viewed
2798 IF p_action_type in ('P', 'U') THEN
2799 hr_utility.set_location(l_package||l_procedure,20);
2800
2801 IF p_pre_balance_status = 'Y' and p_aft_balance_status = 'Y' THEN
2802
2803 IF run_actions_tab.COUNT >0 THEN
2804 pay_us_balance_view_pkg.set_session_var('RUN','TRUE');
2805 pay_us_balance_view_pkg.set_session_var('YTD','FALSE');
2806 pay_us_balance_view_pkg.set_session_var('QTD','FALSE');
2807 pay_us_balance_view_pkg.set_session_var('MTD','FALSE');
2808 pay_us_balance_view_pkg.set_session_var('PYDATE','FALSE');
2809
2810 hr_utility.set_location(l_package||l_procedure,30);
2811 FOR i IN run_actions_tab.FIRST .. run_actions_tab.LAST LOOP
2812 IF run_actions_tab.exists(i) THEN
2813 OPEN c_get_dedn_run_rb(run_actions_tab(i).aaid);
2814 LOOP
2815 FETCH c_get_dedn_run_rb INTO l_rep_name
2816 ,l_run_val
2817 ,l_element_type_id;
2818 EXIT WHEN c_get_dedn_run_rb%NOTFOUND;
2819
2820 -- Populate Deductions Elements Table
2821 deduction_elements_tab(l_element_type_id).element_reporting_name
2822 := l_rep_name ;
2823 deduction_elements_tab(l_element_type_id).element_information10
2824 := null;
2825
2826 hr_utility.set_location(l_package||l_procedure,40);
2827 get_position(l_rep_name,l_found, l_pos,p_dedn_tab);
2828
2829 IF l_found = FALSE THEN
2830 l_pos := p_dedn_tab.COUNT + 1;
2831 p_dedn_tab(l_pos).rep_name := l_rep_name;
2832 p_dedn_tab(l_pos).cur_val := l_run_val;
2833 p_dedn_tab(l_pos).ytd_val := 0;
2834 ELSE
2835 p_dedn_tab(l_pos).cur_val := p_dedn_tab(l_pos).cur_val + l_run_val;
2836 p_dedn_tab(l_pos).ytd_val := 0;
2837 END IF;
2838 END LOOP;
2839 CLOSE c_get_dedn_run_rb;
2840 END IF;
2841 END LOOP;
2842 END IF;
2843
2844 hr_utility.set_location(l_package||l_procedure,50);
2845 IF p_dedn_tab.COUNT > 0 THEN
2846 hr_utility.set_location(l_package||l_procedure,60);
2847 IF master_Actions_tab.COUNT>0 THEN
2848
2849 pay_us_balance_view_pkg.set_session_var('RUN','FALSE');
2850 pay_us_balance_view_pkg.set_session_var('YTD','TRUE');
2851 pay_us_balance_view_pkg.set_session_var('QTD','FALSE');
2852 pay_us_balance_view_pkg.set_session_var('MTD','FALSE');
2853 pay_us_balance_view_pkg.set_session_var('PYDATE','FALSE');
2854
2855 FOR i IN master_actions_tab.FIRST .. master_actions_tab.LAST LOOP
2856 IF master_actions_tab.exists(i) THEN
2857 OPEN c_get_dedn_ytd_rb(master_actions_tab(i).aaid);
2858 LOOP
2859 FETCH c_get_dedn_ytd_rb INTO l_ytd_val,
2860 l_rep_name,
2861 l_element_type_id;
2862 EXIT WHEN c_get_dedn_ytd_rb%NOTFOUND;
2863
2864 -- Populate Deductions after check
2865 IF deduction_elements_tab.count > 0 THEN
2866
2867 IF deduction_elements_tab.exists(l_element_type_id) THEN
2868 hr_utility.trace('The element already exists in PLSQL table');
2869 ELSE
2870 deduction_elements_tab(l_element_type_id).element_reporting_name := l_rep_name ;
2871 deduction_elements_tab(l_element_type_id).element_information10 := null;
2872 END IF;
2873 ELSE
2874 deduction_elements_tab(l_element_type_id).element_reporting_name := l_rep_name;
2875 deduction_elements_tab(l_element_type_id).element_information10 := null;
2876 END IF;
2877 hr_utility.set_location(l_package||l_procedure,70);
2878 get_position(l_rep_name,l_found, l_pos, p_dedn_tab);
2879
2880 IF l_found = TRUE THEN
2881 p_dedn_tab(l_pos).ytd_val := p_dedn_tab(l_pos).ytd_val + l_ytd_val;
2882 ELSE
2883 -- Create new index and store ytd value with run values as 0
2884 l_pos := p_dedn_tab.count+1;
2885 p_dedn_tab(l_pos).rep_name :=l_rep_name;
2886 p_dedn_tab(l_pos).cur_val :=0;
2887 p_dedn_tab(l_pos).ytd_val :=l_ytd_val;
2888 END IF;
2889 END LOOP;
2890 CLOSE c_get_dedn_ytd_rb;
2891 END IF;
2892 END LOOP;
2893 END IF;
2894 END IF;
2895 END IF;
2896
2897 IF p_pre_balance_status <> 'Y' or p_aft_balance_status <> 'Y' THEN
2898
2899 hr_utility.set_location(l_package||l_procedure,80);
2900
2901 IF run_actions_tab.COUNT>0 THEN
2902 pay_us_balance_view_pkg.set_session_var('RUN','TRUE');
2903 pay_us_balance_view_pkg.set_session_var('YTD','FALSE');
2904 pay_us_balance_view_pkg.set_session_var('QTD','FALSE');
2905 pay_us_balance_view_pkg.set_session_var('MTD','FALSE');
2906 pay_us_balance_view_pkg.set_session_var('PYDATE','FALSE');
2907
2908 FOR i IN run_actions_tab.FIRST .. run_actions_tab.LAST LOOP
2909 IF run_actions_tab.exists(i) THEN
2910
2911 hr_utility.set_location(l_package||l_procedure,90);
2912 OPEN c_get_pay_action_details(run_actions_tab(i).aaid);
2913 -- 4966938
2914 FETCH c_get_pay_action_details INTO l_assignment_id
2915 ,l_assignment_action_id
2916 ,l_date_earned
2917 ,l_tax_unit_id
2918 ,l_date_paid;
2919 CLOSE c_get_pay_action_details;
2920 hr_utility.set_location(l_package||l_procedure,210);
2921
2922 hr_utility.trace('Run Action ID : ' || run_actions_tab(i).aaid);
2923
2924 hr_utility.set_location(l_package||l_procedure,220);
2925 -- 4966938
2926 OPEN c_get_dedn_elements(l_date_paid,l_assignment_action_id);
2927 LOOP
2928 FETCH c_get_dedn_elements INTO l_element_type_id
2929 ,l_element_reporting_name
2930 ,l_element_information10
2931 ,l_business_group_id
2932 ,l_processing_priority;
2933 EXIT WHEN c_get_dedn_elements%NOTFOUND;
2934
2935 IF deduction_elements_tab.count > 0 THEN
2936 FOR i in deduction_elements_tab.first ..
2937 deduction_elements_tab.last LOOP
2938 IF deduction_elements_tab.exists(l_element_type_id) THEN
2939 l_found1 := 1;
2940 hr_utility.trace('Element already fetched from Run Bal');
2941 EXIT;
2942 ELSE
2943 l_found1 := 0;
2944 END IF;
2945 END LOOP;
2946 END IF;
2947
2948 IF l_found1 = 0 THEN
2949 deduction_elements_tab(l_element_type_id).element_reporting_name
2950 := l_element_reporting_name ;
2951 deduction_elements_tab(l_element_type_id).element_information10
2952 := l_element_information10;
2953
2954 l_rep_name := l_element_reporting_name;
2955 hr_utility.set_location(l_package||l_procedure ,221);
2956
2957 l_run_val := pay_balance_pkg.get_value
2958 (get_defined_bal(to_number(l_element_information10),
2959 g_run_dimension_id),
2960 run_actions_tab(i).aaid);
2961 hr_utility.set_location(l_package||l_procedure,222);
2962 hr_utility.trace('Run Val : ' || l_run_val);
2963
2964 get_position(l_rep_name,l_found, l_pos,p_dedn_tab);
2965 IF l_found = FALSE THEN
2966 l_pos := p_dedn_tab.COUNT + 1;
2967 p_dedn_tab(l_pos).rep_name := l_rep_name;
2968 p_dedn_tab(l_pos).cur_val := l_run_val;
2969 p_dedn_tab(l_pos).ytd_val := 0;
2970 ELSE
2971 p_dedn_tab(l_pos).cur_val := p_dedn_tab(l_pos).cur_val + l_run_val;
2972 p_dedn_tab(l_pos).ytd_val :=0;
2973 END IF;
2974 END IF;
2975 END LOOP;
2976 CLOSE c_get_dedn_elements;
2977 END IF;
2978 END LOOP;
2979 END IF;
2980
2981 hr_utility.set_location(l_package||l_procedure,223);
2982 IF deduction_elements_tab.COUNT > 0 THEN
2983
2984 IF master_actions_tab.COUNT>0 THEN
2985
2986 pay_us_balance_view_pkg.set_session_var('RUN','FALSE');
2987 pay_us_balance_view_pkg.set_session_var('YTD','TRUE');
2988 pay_us_balance_view_pkg.set_session_var('QTD','FALSE');
2989 pay_us_balance_view_pkg.set_session_var('MTD','FALSE');
2990 pay_us_balance_view_pkg.set_session_var('PYDATE','FALSE');
2991
2992 FOR i IN master_actions_tab.FIRST .. master_actions_tab.LAST LOOP
2993 IF master_actions_tab.exists(i) THEN
2994 hr_utility.trace('Master Action : ' || master_actions_tab(i).aaid);
2995 hr_utility.set_location(l_package||l_procedure,230);
2996 FOR j IN deduction_elements_tab.first ..
2997 deduction_elements_tab.last LOOP
2998 IF deduction_elements_tab.exists(j) and
2999 deduction_elements_tab(j).element_information10 is not null THEN
3000 hr_utility.set_location(l_package||l_procedure,240);
3001 l_rep_name := deduction_elements_tab(j).element_reporting_name;
3002 l_ytd_val := pay_balance_pkg.get_value
3003 (get_defined_bal(to_number(deduction_elements_tab(j).element_information10),
3004 g_ytd_dimension_id),
3005 master_actions_tab(i).aaid);
3006 hr_utility.set_location(l_package||l_procedure,254);
3007 get_position(l_rep_name,l_found, l_pos,p_dedn_tab);
3008 IF l_found = TRUE THEN
3009 p_dedn_tab(l_pos).ytd_val
3010 := p_dedn_tab(l_pos).ytd_val + l_ytd_val;
3011 ELSE
3012 l_pos := p_dedn_tab.count+1;
3013 p_dedn_tab(l_pos).rep_name :=l_rep_name;
3014 p_dedn_tab(l_pos).cur_val :=0;
3015 p_dedn_tab(l_pos).ytd_val :=l_ytd_val;
3016 END IF;
3017 END IF;
3018 END LOOP;
3019 END IF;
3020 END LOOP;
3021 hr_utility.set_location(l_package||l_procedure,150);
3022 END IF;
3023 END IF;
3024 END IF;
3025 hr_utility.set_location(l_package||l_procedure,140);
3026
3027 ELSE -- SOE for run actions is viewed
3028
3029 IF p_pre_balance_status = 'Y' and p_aft_balance_status = 'Y' THEN
3030 hr_utility.set_location(l_package||l_procedure,150);
3031 pay_us_balance_view_pkg.set_session_var('YTD','TRUE');
3032 pay_us_balance_view_pkg.set_session_var('RUN','TRUE');
3033 pay_us_balance_view_pkg.set_session_var('QTD','FALSE');
3034 pay_us_balance_view_pkg.set_session_var('MTD','FALSE');
3035 pay_us_balance_view_pkg.set_session_var('PYDATE','FALSE');
3036 deduction_elements_tab.delete;
3037 p_dedn_tab.delete;
3038 hr_utility.set_location(l_package||l_procedure,160);
3039 OPEN c_get_dedn_elements_rb(p_assignment_action_id);
3040 LOOP
3041 FETCH c_get_dedn_elements_rb INTO l_ytd_val,
3042 l_rep_name,
3043 l_run_val,
3044 l_element_type_id;
3045 EXIT WHEN c_get_dedn_elements_rb%NOTFOUND;
3046
3047 deduction_elements_tab(l_element_type_id).element_reporting_name := l_rep_name ;
3048 deduction_elements_tab(l_element_type_id).element_information10 := null;
3049
3050 get_position(l_rep_name,l_found, l_pos, p_dedn_tab);
3051 hr_utility.set_location(l_package||l_procedure,40);
3052 IF l_found = FALSE THEN
3053 l_pos := p_dedn_tab.COUNT + 1;
3054 p_dedn_tab(l_pos).rep_name := l_rep_name;
3055 p_dedn_tab(l_pos).cur_val := l_run_val;
3056 p_dedn_tab(l_pos).ytd_val := l_ytd_val;
3057 ELSE
3058 p_dedn_tab(l_pos).cur_val := p_dedn_tab(l_pos).cur_val + l_run_val;
3059 p_dedn_tab(l_pos).ytd_val := p_dedn_tab(l_pos).ytd_val + l_ytd_val;
3060 END IF;
3061 END LOOP;
3062 CLOSE c_get_dedn_elements_rb;
3063 END IF;
3064
3065 hr_utility.set_location(l_package||l_procedure,170);
3066
3067 IF p_pre_balance_status <> 'Y' or p_aft_balance_status <> 'Y' THEN
3068
3069 OPEN c_get_pay_action_details(p_assignment_action_id);
3070 FETCH c_get_pay_action_details INTO l_assignment_id
3071 ,l_assignment_action_id
3072 ,l_date_earned
3073 ,l_tax_unit_id
3074 ,l_date_paid;
3075 CLOSE c_get_pay_action_details;
3076 -- 4966938
3077 OPEN c_get_dedn_elements(l_date_paid,l_assignment_action_id);
3078 LOOP
3079 FETCH c_get_dedn_elements INTO l_element_type_id
3080 ,l_element_reporting_name
3081 ,l_element_information10
3082 ,l_business_group_id
3083 ,l_processing_priority;
3084 EXIT WHEN c_get_dedn_elements%NOTFOUND;
3085
3086 IF deduction_elements_tab.count > 0 THEN
3087 IF deduction_elements_tab.exists(l_element_type_id) THEN
3088 hr_utility.trace('The element already exists in PLSQL table');
3089 ELSE
3090 deduction_elements_tab(l_element_type_id).element_reporting_name
3091 := l_element_reporting_name ;
3092 deduction_elements_tab(l_element_type_id).element_information10
3093 := l_element_information10;
3094 deduction_elements_tab(l_element_type_id).business_group_id
3095 := l_business_group_id;
3096 END IF;
3097 ELSE
3098 deduction_elements_tab(l_element_type_id).element_reporting_name
3099 := l_element_reporting_name ;
3100 deduction_elements_tab(l_element_type_id).element_information10
3101 := l_element_information10;
3102 deduction_elements_tab(l_element_type_id).business_group_id
3103 := l_business_group_id;
3104 END IF;
3105 -- bug 4743188
3106
3107 l_rep_name := deduction_elements_tab(l_element_type_id).element_reporting_name;
3108 l_run_val := pay_balance_pkg.get_value
3109 (get_defined_bal(deduction_elements_tab(l_element_type_id).element_information10,
3110 g_run_dimension_id),
3111 p_assignment_action_id);
3112
3113 l_ytd_val := pay_balance_pkg.get_value
3114 (get_defined_bal(deduction_elements_tab(l_element_type_id).element_information10,
3115 g_ytd_dimension_id),
3116 p_assignment_action_id);
3117
3118 get_position(l_rep_name,l_found, l_pos, p_dedn_tab);
3119 IF l_found = TRUE THEN
3120 p_dedn_tab(l_pos).ytd_val := p_dedn_tab(l_pos).ytd_val + l_ytd_val;
3121 p_dedn_tab(l_pos).cur_val := p_dedn_tab(l_pos).cur_val + l_run_val;
3122 ELSE
3123 l_pos := p_dedn_tab.count + 1;
3124 p_dedn_tab(l_pos).rep_name := l_rep_name;
3125 p_dedn_tab(l_pos).ytd_val := l_ytd_val;
3126 p_dedn_tab(l_pos).cur_val := l_run_val;
3127 END IF;
3128 --end
3129
3130 END LOOP;
3131 CLOSE c_get_dedn_elements;
3132 --bug 4743188
3133 /* IF deduction_elements_tab.count > 0 THEN
3134 FOR i IN deduction_elements_tab.first..deduction_elements_tab.last LOOP
3135 IF deduction_elements_tab.exists(i) and
3136 deduction_elements_tab(i).element_information10 is not null THEN
3137 l_rep_name := deduction_elements_tab(i).element_reporting_name;
3138 l_run_val := pay_balance_pkg.get_value
3139 (get_defined_bal(deduction_elements_tab(i).element_information10,
3140 g_run_dimension_id),
3141 p_assignment_action_id);
3142
3143 l_ytd_val := pay_balance_pkg.get_value
3144 (get_defined_bal(deduction_elements_tab(i).element_information10,
3145 g_ytd_dimension_id),
3146 p_assignment_action_id);
3147
3148 get_position(l_rep_name,l_found, l_pos, p_dedn_tab);
3149 IF l_found = TRUE THEN
3150 p_dedn_tab(l_pos).ytd_val := p_dedn_tab(l_pos).ytd_val + l_ytd_val;
3151 p_dedn_tab(l_pos).cur_val := p_dedn_tab(l_pos).cur_val + l_run_val;
3152 ELSE
3153 l_pos := p_dedn_tab.count + 1;
3154 p_dedn_tab(l_pos).rep_name := l_rep_name;
3155 p_dedn_tab(l_pos).ytd_val := l_ytd_val;
3156 p_dedn_tab(l_pos).cur_val := l_run_val;
3157 END IF;
3158 END IF;
3159 END LOOP;
3160 END IF; */--comments end
3161 END IF;
3162 END IF;
3163
3164 EXCEPTION
3165 WHEN others THEN
3166 hr_utility.set_location(l_package||l_procedure,180);
3167 raise_application_error(-20101, 'Error in '||l_package||l_procedure || ' - ' || sqlerrm);
3168
3169 END populate_dedn_balance;
3170
3171
3172
3173 /*****************************************************************************
3174 Name : get_max_actions_table
3175 Purpose : This procedure returns the plsql table of all max actions to the
3176 SOE form. We will store the max actions in sorted order so that
3177 we can take advantage to use the last stored value as the max
3178 action for Summary Block Values
3179 *****************************************************************************/
3180 PROCEDURE get_max_actions_table(p_max_actions_tab out nocopy master_aaid_tab)
3181 IS
3182 cnt_start number;
3183 cnt_end number;
3184 i number;
3185 l_temp number;
3186 l_procedure varchar2(21) ;
3187 BEGIN
3188 l_procedure := 'get_max_actions_table';
3189 hr_utility.set_location(l_package||l_procedure,10);
3190 IF master_actions_tab.count >0 THEN
3191 cnt_start := master_actions_tab.first;
3192 cnt_end := master_actions_tab.last;
3193
3194 hr_utility.set_location(l_package||l_procedure,20);
3195
3196 -- Sort the table in Ascending Order
3197 FOR i in cnt_start..(cnt_end-1) LOOP
3198 IF master_actions_tab.exists(i) THEN
3199 FOR j in i+1..cnt_end LOOP
3200 IF master_actions_tab.exists(j) THEN
3201 IF master_actions_tab(i).aaid > master_actions_tab(j).aaid THEN
3202 l_temp := master_actions_tab(i).aaid;
3203 master_actions_tab(i).aaid := master_actions_tab(j).aaid;
3204 master_actions_tab(j).aaid := l_temp;
3205 END IF;
3206 END IF;
3207 END LOOP;
3208 END IF;
3209 END LOOP;
3210 hr_utility.set_location(l_package||l_procedure,30);
3211
3212 -- Assign the sorted max actions table to the table to be used
3213 -- by SOE Form
3214
3215 FOR i in cnt_start..cnt_end
3216 LOOP
3217 IF master_actions_tab.exists(i) THEN
3218 p_max_actions_tab(i).aaid := master_actions_tab(i).aaid;
3219 END IF;
3220 END LOOP;
3221 END IF;
3222 hr_utility.set_location(l_package||l_procedure,40);
3223
3224 EXCEPTION
3225 WHEN others THEN
3226 hr_utility.set_location(l_package||l_procedure,50);
3227 raise_application_error(-20101, 'Error in '||l_package||l_procedure || ' - ' || sqlerrm);
3228 END;
3229 END pay_us_soe_balances_pkg;
[Home] [Help]