1 PACKAGE BODY pay_us_employee_balances AS
2 /* $Header: pyusempb.pkb 120.2 2006/08/24 11:29:09 kvsankar noship $ */
3
4 /******************************************************************************
5 ******************************************************************
6 * *
7 * Copyright (C) 1993 Oracle Corporation. *
8 * All rights reserved. *
9 * *
10 * This material has been provided pursuant to an agreement *
11 * containing restrictions on its use. The material is also *
12 * protected by copyright law. No part of this material may *
13 * be copied or distributed, transmitted or transcribed, in *
14 * any form or by any means, electronic, mechanical, magnetic, *
15 * manual, or otherwise, or disclosed to third parties without *
16 * the express written permission of Oracle Corporation, *
17 * 500 Oracle Parkway, Redwood City, CA, 94065. *
18 * *
19 ******************************************************************
20
21 Name : pay_us_employee_balances
22
23 Description : The package is used by the Employee Balances form
24 and it is used to fetch the earnings and non-tax
25 deduction balances.
26
27 Change List
28 -----------
29 Date Name Vers Bug No Description
30 ----------- ---------- ------ ------- -----------------------------------
31 26-Dec-2003 kaverma 115.0 3311781 Created.
32 26-Mar-2004 kvsankar 115.1 3311781 Modified the code to fetch
33 3300433 balance values for only those
34 elements selected by the user
35 in query mode
36 Modified the function get_bal
37 to consider balance adjustments
38 done after actual termination date.
39 Modified the cursors
40 'c_get_element_runs' ,
41 'csr_element_assact_info',
42 'csr_element_assact_runs',
43 'c_element_assact_balances',
44 'c_element_asg_balances'
45 to retrieve ROWID.
46 06-Apr-2004 kvsankar 115.2 3541052 Modified the date passed for
47 getting the value of the defined
48 balances
49 12-Apr-2004 kvsankar 115.3 3311781 Corrected GSCC warnings
50 15-Apr-2004 kvsankar 115.4 3311781 Removed the cursor
51 'c_element_assact_balances' since
52 Balances for 'Balance
53 Initialization' cannot be seen
54 using assignment action mode. Also
55 made changes to cursors
56 'csr_element_assact_info',
57 'csr_element_assact_runs',
58 'c_element_asg_balances' to
59 include the changes for 'PER'
60 level balances.
61 19-Apr-2004 kvsankar 115.5 3369361 Added the condition
62 pac.tax_unit_id := p_tax_unit_id
63 in cursor c_action_type
64 11-May-2004 kvsankar 115.6 3300433 Changed the select query written
65 for Bug Fix 3300433 to return
66 final process date instead of
67 last standard process date
68 17-Mar-2005 meshah 115.7 4039299 changed cursor
69 csr_element_assact_runs and changed
70 the exists clause.
71 13-Jan-2006 rpasumar 115.8 4915420 Changed per_assignments_f to
72 per_all_assignments_f to
73 improve performance.
74 23-Aug-2006 kvsankar 115.9 5460886 Added a new cursor
75 csr_element_assact_info_dedn for
76 the following classifications
77 * Pre-Tax Deductions
78 * Involuntary Deductions
79 * Voluntary Deduction
80 ******************************************************************************/
81
82 l_package VARCHAR2(30);
83
84 /******************************************************************************
85 Name : populate_element_info
86 Purpose : This procedure fetches the elements for which the balances are to
87 be retrieved.It then finds out the corresponding balance values and
88 stores them in a PL/SQL table.This PL/SQL table is passed to the
89 form as an OUT parameter.
90 ******************************************************************************/
91 PROCEDURE populate_element_info( p_assignment_id in number,
92 p_assignment_action_id in number,
93 p_classification_id in pay_element_classifications.classification_id%TYPE,
94 p_classification_name in pay_element_classifications.classification_name%TYPE,
95 p_session_date in pay_element_types_f.effective_start_date%TYPE,
96 p_action_date in pay_element_types_f.effective_start_date%TYPE,
97 p_pay_start_date in pay_element_types_f.effective_start_date%TYPE,
98 p_tax_unit_id in number,
99 p_per_month in number,
100 p_per_qtd in number,
101 p_per_ytd in number,
102 p_asg_ptd in number,
103 p_asg_month in number,
104 p_asg_qtd in number,
105 p_asg_ytd in number,
106 p_asg_itd in number,
107 p_legislation_code in pay_element_types_f.legislation_code%TYPE,
108 p_business_group_id in pay_element_types_f.business_group_id%TYPE,
109 p_balance_level in varchar2,
110 p_earn_data out nocopy earn_tbl,
111 p_dedn_data out nocopy dedn_tbl,
112 p_element_type_id in out nocopy number,
113 p_flag out nocopy varchar2,
114 p_balance_status in varchar2
115 )
116 IS
117
118 /*
119 * Cursor to find out which processes have been completed.
120 * Most interested in Quick pays or Runs.
121 */
122
123 CURSOR c_action_type
124 IS
125 select pay.action_type
126 from pay_assignment_actions pac
127 ,pay_payroll_actions pay
128 where pay.payroll_action_id = pac.payroll_action_id
129 and pac.assignment_id = p_assignment_id
130 and pac.action_status = 'C'
131 and pac.tax_unit_id = p_tax_unit_id
132 and exists (select 'x'
133 from pay_run_results prr
134 where prr.assignment_action_id = pac.assignment_action_id )
135 order by decode(pay.action_type,'Q','1','R','1','I','2','3');
136 -- note: Also check run_results as might have a payment where there
137 -- was no pay_value. In this case we'll want to see Initialised balance.
138
139
140
141 l_attribute_name pay_bal_attribute_definitions.attribute_name%type;
142 l_last_process_date DATE;
143 l_date DATE;
144 l_ytd_date DATE;
145 l_qtd_date DATE ;
146 l_temp_assignment_id per_assignments_f.assignment_id%TYPE;
147 l_dim_month varchar2(20);
148 l_dim_qtd varchar2(20);
149 l_dim_ytd varchar2(20);
150
151
152
153 /******************************************************************************
154 * Cursor to get element_information in case complete quickpay or run process
155 * has been carried out don't include any initial upload elements.
156 * This cursor is needed in assignment and date mode.
157 ******************************************************************************/
158
159 CURSOR c_get_element_runs
160 IS
161 select distinct pet.rowid
162 ,pet.element_name
163 ,pet.element_type_id
164 ,pet.classification_id
165 ,pet.element_information10
166 ,pet.element_information11
167 ,pet.element_information12
168 ,pet.element_information14
169 from pay_element_types_f pet
170 , pay_element_types_f pet2
171 , pay_element_entries_f ee
172 WHERE pet2.classification_id = p_classification_id
173 AND pet2.element_information10 is not null
174 AND ee.effective_end_date >= p_pay_start_date
175 AND ee.effective_start_date <= nvl(p_action_date , p_session_date )
176 AND ee.effective_start_date between pet2.effective_start_date and pet2.effective_end_date
177 AND pet2.element_type_id = pet.element_type_id
178 AND PET.effective_start_date = (select max(pet1.effective_start_date)
179 from pay_element_types_f pet1
180 where pet1.element_type_id = pet.element_type_id
181 and pet1.effective_start_date <= p_session_date )
182 AND pet.element_name not like 'VERTEX%'
183 AND ee.assignment_id = p_assignment_id
184 AND EXISTS
185 (select prr.element_type_id
186 from pay_run_results prr
187 where prr.source_id = ee.element_entry_id
188 and prr.source_type in ( 'E' , 'I' )
189 and prr.element_type_id + 0 = pet.element_type_id
190 )
191 order by 2;
192
193
194
195 /******************************************************************************
196 * Cursor to get balance values incase balances are valid and mode is
197 * assignment action mode for ASG/PER level balances.
198 ******************************************************************************/
199
200 CURSOR csr_element_assact_info
201 IS
202 select /*+ index (pet pay_element_types_f_fk1) */ distinct pet.rowid
203 ,pet.element_name
204 ,pet.element_type_id
205 ,pet.classification_id
206 ,pet.element_information10
207 ,pet.element_information11
208 ,pet.element_information12
209 ,pet.element_information14
210 ,decode (p_balance_level,
211 'ASG',(PAY_US_TAXBAL_VIEW_PKG.US_NAMED_BALANCE_VM (UPPER(pbT.balance_name) ,
212 'ASG_GRE_PTD' ,
213 p_assignment_action_id ,
214 NULL,
215 NULL,
216 p_tax_unit_id,
217 p_business_group_id ,
218 NULL)),
219 'PER',NULL) PTD_VAL
220 ,PAY_US_TAXBAL_VIEW_PKG.US_NAMED_BALANCE_VM (UPPER(pBT.balance_name) ,
221 l_dim_month ,
222 p_assignment_action_id ,
223 NULL,
224 NULL ,
225 p_tax_unit_id,
226 p_business_group_id ,
227 NULL) MONTH_VAL
228 ,PAY_US_TAXBAL_VIEW_PKG.US_NAMED_BALANCE_VM (UPPER(pBT.balance_name) ,
229 l_dim_qtd ,
230 p_assignment_action_id ,
231 NULL,
232 NULL ,
233 p_tax_unit_id,
234 p_business_group_id ,
235 NULL) QTD_VAL
236 ,PAY_US_TAXBAL_VIEW_PKG.US_NAMED_BALANCE_VM (UPPER(pBT.balance_name) ,
237 l_dim_ytd ,
238 p_assignment_action_id ,--global.assignment_action_id
239 NULL,
240 NULL ,
241 p_tax_unit_id,--control.tax_unit_id
242 p_business_group_id ,--ctlglobals.bg_id
243 NULL) YTD_VAL
244 from pay_element_types_f pet
245 , pay_element_classifications pec
246 , pay_defined_balances pdb
247 , pay_bal_attribute_definitions pbad
248 , pay_balance_attributes pba
249 , pay_balance_types pbt
250 , pay_assignment_actions paa
251 , pay_payroll_actions ppa
252 WHERE pbad.attribute_name = l_attribute_name
253 AND pbad.business_group_id is null
254 AND pbad.legislation_code = 'US'
255 AND pba.attribute_id = pbad.attribute_id
256 AND pdb.defined_balance_id = pba.defined_balance_id
257 AND pdb.balance_type_id =pbt.balance_type_id
258 AND pec.classification_id = p_classification_id
259 AND pec.classification_id = pet.classification_id
260 and pec.legislation_code = 'US'
261 AND pet.element_information10 is not null
262 AND nvl(ppa.date_earned,ppa.effective_date) between pet.effective_start_date
263 and pet.effective_end_date
264 AND paa.assignment_action_id =p_assignment_action_id
265 AND paa.payroll_action_id =ppa.payroll_action_id
266 AND pet.element_name not like 'VERTEX%'
267 and pet.element_information10= pDB.balance_type_id
268 AND EXISTS (select prb.balance_value
269 from pay_run_balances prb,
270 pay_defined_balances pdb
271 where prb.defined_balance_id = pdb.defined_balance_id
272 and prb.assignment_id = paa.assignment_id
273 and pdb.balance_type_id = pet.element_information10
274 and rownum < 2)
275 order by 2;
276
277 /******************************************************************************
281 * * Pre-Tax Deductions
278 * Cursor to get balance values incase balances are valid and mode is
279 * assignment action mode for ASG/PER level balances.
280 * This cursor will be called for the following classifications
282 * * Involuntary Deductions
283 * * Voluntary Deductions
284 ******************************************************************************/
285
286 CURSOR csr_element_assact_info_dedn
287 IS
288 select /*+ index (pet pay_element_types_f_fk1) */ distinct pet.rowid
289 ,pet.element_name
290 ,pet.element_type_id
291 ,pet.classification_id
292 ,pet.element_information10
293 ,pet.element_information11
294 ,pet.element_information12
295 ,pet.element_information14
296 ,decode (p_balance_level,
297 'ASG',(PAY_US_TAXBAL_VIEW_PKG.US_NAMED_BALANCE_VM (UPPER(pbT.balance_name) ,
298 'ASG_GRE_PTD' ,
299 p_assignment_action_id ,
300 NULL,
301 NULL,
302 p_tax_unit_id,
303 p_business_group_id ,
304 NULL)),
305 'PER',NULL) PTD_VAL
306 ,PAY_US_TAXBAL_VIEW_PKG.US_NAMED_BALANCE_VM (UPPER(pBT.balance_name) ,
307 l_dim_month ,
308 p_assignment_action_id ,
309 NULL,
310 NULL ,
311 p_tax_unit_id,
312 p_business_group_id ,
313 NULL) MONTH_VAL
314 ,PAY_US_TAXBAL_VIEW_PKG.US_NAMED_BALANCE_VM (UPPER(pBT.balance_name) ,
315 l_dim_qtd ,
316 p_assignment_action_id ,
317 NULL,
318 NULL ,
319 p_tax_unit_id,
320 p_business_group_id ,
321 NULL) QTD_VAL
322 ,PAY_US_TAXBAL_VIEW_PKG.US_NAMED_BALANCE_VM (UPPER(pBT.balance_name) ,
323 l_dim_ytd ,
324 p_assignment_action_id ,--global.assignment_action_id
325 NULL,
326 NULL ,
327 p_tax_unit_id,--control.tax_unit_id
328 p_business_group_id ,--ctlglobals.bg_id
329 NULL) YTD_VAL
330 from pay_element_types_f pet
331 , pay_element_classifications pec
332 , pay_defined_balances pdb
333 , pay_bal_attribute_definitions pbad
334 , pay_balance_attributes pba
335 , pay_balance_types pbt
336 , pay_assignment_actions paa
337 , pay_payroll_actions ppa
338 WHERE pbad.attribute_name = l_attribute_name
339 AND pbad.business_group_id is null
340 AND pbad.legislation_code = 'US'
341 AND pba.attribute_id = pbad.attribute_id
342 AND pdb.defined_balance_id = pba.defined_balance_id
343 AND pdb.balance_type_id =pbt.balance_type_id
344 AND pec.classification_id = p_classification_id
345 AND pec.classification_id = pet.classification_id
346 and pec.legislation_code = 'US'
347 AND pet.element_information10 is not null
348 AND nvl(ppa.date_earned,ppa.effective_date) between pet.effective_start_date
349 and pet.effective_end_date
350 AND paa.assignment_action_id =p_assignment_action_id
351 AND paa.payroll_action_id =ppa.payroll_action_id
352 AND pet.element_name not like 'VERTEX%'
353 and pet.element_information10= pDB.balance_type_id
354 AND EXISTS (select prb.balance_value
355 from pay_run_balances prb,
356 pay_defined_balances pdb
357 where prb.defined_balance_id = pdb.defined_balance_id
358 and prb.assignment_id = paa.assignment_id
359 and pdb.balance_type_id in (pet.element_information10
360 ,pet.element_information11
361 ,pet.element_information12
362 ,pet.element_information14)
363 and rownum < 2)
364 order by 2;
365
366
367 /*****************************************************************************
368 * Cursor to get balance values incase balances are not valid and mode is
369 * assignment action mode for ASG/PER level balances
370 *****************************************************************************/
371
372 CURSOR csr_element_assact_runs
373 IS
374 select distinct pet.rowid
375 ,pet.element_name
376 ,pet.element_type_id
377 ,pet.classification_id
378 ,pet.element_information10
379 ,pet.element_information11
380 ,pet.element_information12
381 ,pet.element_information14
382 ,decode (p_balance_level,
383 'ASG',(PAY_US_TAXBAL_VIEW_PKG.US_NAMED_BALANCE_VM (UPPER(pbT.balance_name) ,
384 'ASG_GRE_PTD' ,
385 p_assignment_action_id ,
386 NULL,
387 NULL,
388 p_tax_unit_id,
389 p_business_group_id ,
390 NULL)),
391 'PER', NULL) PTD_VAL
392 ,PAY_US_TAXBAL_VIEW_PKG.US_NAMED_BALANCE_VM (UPPER(pBT.balance_name) ,
393 l_dim_month ,
394 p_assignment_action_id ,
395 NULL,
396 NULL ,
397 p_tax_unit_id,
398 p_business_group_id ,
399 NULL) MONTH_VAL
403 NULL,
400 ,PAY_US_TAXBAL_VIEW_PKG.US_NAMED_BALANCE_VM (UPPER(pBT.balance_name) ,
401 l_dim_qtd,
402 p_assignment_action_id ,
404 NULL ,
405 p_tax_unit_id,
406 p_business_group_id ,
407 NULL) QTD_VAL
408 ,PAY_US_TAXBAL_VIEW_PKG.US_NAMED_BALANCE_VM (UPPER(pBT.balance_name) ,
409 l_dim_ytd,
410 p_assignment_action_id ,--global.assignment_action_id
411 NULL,
412 NULL ,
413 p_tax_unit_id,--control.tax_unit_id
414 p_business_group_id ,--ctlglobals.bg_id
415 NULL) YTD_VAL
416 from pay_element_types_f pet
417 , pay_payroll_actions ppa
418 , pay_assignment_actions paa
419 , pay_balance_types pbt
420 WHERE pet.classification_id = p_classification_id
421 AND pet.element_information10 is not null
422 AND PAA.ASSIGNMENT_ACTION_ID =p_assignment_action_id
423 and paa.payroll_action_id =ppa.payroll_action_id
424 AND nvl(ppa.date_earned,ppa.effective_date) between pet.effective_start_date
425 and pet.effective_end_date
426 AND pet.element_name not like 'VERTEX%'
427 AND paa.assignment_id = p_assignment_id
428 AND pet.element_information10 =pbt.balance_type_id
429 AND EXISTS (SELECT 'x'
430 FROM pay_payroll_actions pact,
431 pay_assignment_actions asg,
432 pay_run_results rr
433 where rr.element_type_id + 0 = pet.element_type_id
434 and rr.assignment_action_id = asg.assignment_action_id
435 and asg.assignment_id = paa.assignment_id
436 and asg.tax_unit_id = paa.tax_unit_id
437 and asg.payroll_action_id = pact.payroll_action_id
438 and pact.effective_date between trunc(ppa.effective_date,'YEAR')
439 and ppa.effective_date
440 and rr.source_type in ( 'E' , 'I' )
441 )
442 order by 2;
443 /*
444 AND EXISTS (select prr.element_type_id
445 from pay_run_results prr
446 where prr.assignment_action_id = paa.assignment_action_id
447 and prr.source_type in ( 'E' , 'I' )
448 and prr.element_type_id + 0 = pet.element_type_id
449 )
450 */
451
452
453 /******************************************************************************
454 * Cursor to get element information in case balance uploads are completed and
455 * mode is assignment mode for ASG/PER level balances
456 ******************************************************************************/
457 CURSOR c_element_asg_balances
458 IS
459 select distinct pet2.rowid
460 ,pet2. element_name
461 ,pet2.element_type_id
462 ,pet2.classification_id
463 ,pet2.element_information10
464 ,pet2.element_information11
465 ,pet2.element_information12
466 ,pet2.element_information14
467 ,decode (p_balance_level,
468 'ASG', (PAY_US_TAXBAL_VIEW_PKG.US_NAMED_BALANCE_VM (UPPER(pbT.balance_name) ,
469 'ASG_GRE_PTD' ,
470 NULL,
471 l_temp_assignment_id,
472 l_date,
473 p_tax_unit_id,
474 p_business_group_id ,
475 NULL)),
476 'PER', NULL) PTD_VAL
477 ,PAY_US_TAXBAL_VIEW_PKG.US_NAMED_BALANCE_VM (UPPER(pBT.balance_name) ,
478 l_dim_month,
479 NULL ,
480 l_temp_assignment_id,
481 l_date ,
482 p_tax_unit_id,
483 p_business_group_id ,
484 NULL) MONTH_VAL
485 ,PAY_US_TAXBAL_VIEW_PKG.US_NAMED_BALANCE_VM (UPPER(pBT.balance_name) ,
486 l_dim_qtd,
487 NULL ,
488 l_temp_assignment_id,
489 nvl(l_qtd_date,l_date ),
490 p_tax_unit_id,
491 p_business_group_id ,
492 NULL) QTD_VAL
493 ,PAY_US_TAXBAL_VIEW_PKG.US_NAMED_BALANCE_VM (UPPER(pBT.balance_name) ,
494 l_dim_ytd,
495 NULL ,--global.assignment_action_id
496 l_temp_assignment_id,
497 nvl(l_ytd_date,l_date) ,
498 p_tax_unit_id,--control.tax_unit_id
499 p_business_group_id ,--ctlglobals.bg_id
500 NULL) YTD_VAL
501 from pay_element_classifications ec
502 ,pay_element_types_f et
503 ,pay_element_links_f el
504 ,pay_element_entries_f ee
505 ,pay_element_entry_values_f eev
506 ,pay_balance_feeds_f pbf
507 ,pay_element_types_f pet2
508 ,pay_element_classifications pec
509 ,pay_input_values_f piv
510 ,pay_balance_types pbt
511 where ec.classification_name = 'Balance Initialization'
512 and ec.legislation_code is null
513 and ee.assignment_id = p_assignment_id
517 and ee.element_entry_id = eev.element_entry_id
514 and ee.element_link_id = el.element_link_id
515 and el.element_type_id = et.element_type_id
516 and et.classification_id = ec.classification_id
518 and eev.input_value_id = pbf.input_value_id
519 and piv.input_value_id = pbf.input_value_id
520 and et.element_type_id = piv.element_type_id
521 and nvl(p_action_date ,p_session_date) between pbf.effective_start_date
522 and pbf.effective_end_date
523 and pbf.balance_type_id = pet2.element_information10
524 and pbt.balance_type_id = pet2.element_information10
525 and pet2.element_information10 is not null
526 and pet2.classification_id = pec.classification_id
527 and pec.classification_name = p_classification_name
528 and pec.legislation_code = 'US'
529 and nvl(p_action_date, p_session_date ) between pet2.effective_start_date
530 and pet2.effective_end_date
531 and eev.screen_entry_value is not null
532 order by 2;
533
534
535 st_cnt number;
536 end_cnt number;
537 i number;
538 j number;
539 l_value number;
540 l_type pay_payroll_actions.action_type%TYPE;
541 l_procedure VARCHAR2(22) ;
542 value pay_balance_types.balance_name%type ;
543 p_dedn_data_temp p_dedn_data_temp_tbl;
544
545
546 /******************************************************************************
547 * Name : get_balance_name
548 * Purpose : This function is used to get the balance names based on the
549 * balance type id passed.
550 ******************************************************************************/
551 FUNCTION get_balance_name(l_balance_type_id in number )
552 RETURN varchar2 IS
553 BEGIN
554 SELECT balance_name INTO value
555 FROM pay_balance_types
556 WHERE balance_type_id =l_balance_type_id;
557 RETURN value;
558
559 EXCEPTION WHEN NO_DATA_FOUND THEN
560 RETURN -1;
561 END;
562
563 /******************************************************************************
564 * Name : get_defined_bal
565 * Purpose : This function is used to get the defined balance ids based on
566 * balance type id and balance dimension id.
567 ******************************************************************************/
568 FUNCTION get_defined_bal (p_bal_id in number
569 ,p_dim_id in number)
570 RETURN number IS
571 v_defbal_id number;
572 l_function varchar2(16);
573 BEGIN
574 l_function :='get_defined_bal';
575 hr_utility.set_location(l_package||l_function, 10);
576
577 SELECT defined_balance_id
578 INTO v_defbal_id
579 FROM pay_defined_balances
580 WHERE balance_type_id = p_bal_id
581 AND balance_dimension_id = p_dim_id
582 AND nvl(business_group_id,p_business_group_id) = p_business_group_id
583 AND nvl(legislation_code,p_legislation_code) = p_legislation_code;
584
585 hr_utility.set_location(l_package||l_function, 20);
586
587 RETURN v_defbal_id;
588
589 EXCEPTION WHEN NO_DATA_FOUND THEN
590 hr_utility.set_location(l_package||l_function, 30);
591 RETURN -1;
592
593 END;
594
595
596
597 /******************************************************************************
598 * Name : get_bal
599 * Purpose : This function is used to get balance values based on defined
600 * balance ids
601 ******************************************************************************/
602
603 FUNCTION get_bal (l_defbal_id in number
604 ,l_bal_type_id in number)
605 RETURN NUMBER IS
606 l_last_process_date DATE;
607 l_date DATE;
608 l_ytd_id number(9);
609 l_qtd_id number(9);
610 l_temp_assignment_id per_assignments_f.assignment_id%TYPE;
611 l_function varchar2(9);
612
613 BEGIN
614 l_function :='get_bal';
615 hr_utility.set_location(l_package||l_function, 10);
616 IF (p_assignment_action_id = -1 ) THEN
617 IF p_balance_level='PER' THEN
618 l_ytd_id := get_defined_bal(l_bal_type_id,p_per_ytd);
619 l_qtd_id := get_defined_bal(l_bal_type_id,p_per_qtd);
620
621 hr_utility.set_location(l_package||l_function, 20);
622
623 BEGIN
624
625 -- check to see if p_assignment_id exist as of l_date
626 select paf.assignment_id
627 into l_temp_assignment_id
628 from per_assignments_f paf,
629 hr_soft_coding_keyflex hsk
630 where paf.assignment_id = p_assignment_id
631 and paf.soft_coding_keyflex_id = hsk.soft_coding_keyflex_id
632 and p_session_date between paf.effective_start_date
633 and paf.effective_end_date
634 and hsk.segment1 = to_char(p_tax_unit_id);
635
636 l_temp_assignment_id := to_number(p_assignment_id);
637
638 hr_utility.set_location(l_package||l_function, 30);
639
640 EXCEPTION
641 WHEN NO_DATA_FOUND THEN
642
643 -- Attempt to find any assignment id for the person as of l_date
644
648 select paf2.assignment_id
645 BEGIN
646 hr_utility.set_location(l_package||l_function, 40);
647
649 into l_temp_assignment_id
650 from per_assignments_f paf1,
651 per_assignments_f paf2,
652 hr_soft_coding_keyflex hsk
653 where paf1.assignment_id = p_assignment_id
654 and paf2.person_id = paf1.person_id
655 and paf2.soft_coding_keyflex_id = hsk.soft_coding_keyflex_id
656 and p_session_date between paf2.effective_start_date
657 and paf2.effective_end_date
658 and hsk.segment1 = to_char(p_tax_unit_id)
659 and rownum=1;
660
661 hr_utility.set_location(l_package||l_function, 50);
662 EXCEPTION
663 WHEN NO_DATA_FOUND THEN
664
665 BEGIN
666 -- Find an assignment id for the person with an end date < l_date
667 -- and greater than trunc(p_session_date,y).
668 hr_utility.set_location(l_package||l_function, 60);
669
670 -- 4915420
671
672 select paf2.assignment_id
673 into l_temp_assignment_id
674 from per_all_assignments_f paf1,
675 per_all_assignments_f paf2,
676 hr_soft_coding_keyflex hsk
677 where paf1.assignment_id = p_assignment_id
678 and paf2.person_id = paf1.person_id
679 and paf2.soft_coding_keyflex_id = hsk.soft_coding_keyflex_id
680 and hsk.segment1 = to_char(p_tax_unit_id)
681 and paf2.effective_end_date < p_session_date
682 and paf2.effective_end_date >= trunc(p_session_date,'YYYY')
683 and paf2.effective_end_date =
684 (select MAX(paf3.effective_end_date)
685 from per_all_assignments_f paf3
686 where paf3.person_id = paf1.person_id
687 and paf3.effective_end_date < p_session_date
688 )
689 and rownum=1;
690
691 /* select paf2.assignment_id
692 into l_temp_assignment_id
693 from per_assignments_f paf1,
694 per_assignments_f paf2,
695 hr_soft_coding_keyflex hsk
696 where paf1.assignment_id = p_assignment_id
697 and paf2.person_id = paf1.person_id
698 and paf2.soft_coding_keyflex_id = hsk.soft_coding_keyflex_id
699 and hsk.segment1 = to_char(p_tax_unit_id)
700 and paf2.effective_end_date < p_session_date
701 and paf2.effective_end_date >= trunc(p_session_date,'YYYY')
702 and paf2.effective_end_date =
703 (select MAX(paf3.effective_end_date)
704 from per_assignments_f paf3
705 where paf3.person_id = paf1.person_id
706 and paf3.effective_end_date < p_session_date
707 )
708 and rownum=1; */
709
710
711 hr_utility.set_location(l_package||l_function, 70);
712
713 EXCEPTION
714 WHEN NO_DATA_FOUND THEN
715 hr_utility.set_location(l_package||l_function, 80);
716 NULL;
717 END;
718 END;
719 END;
720 ELSE -- Assignment level balances are required
721
722 hr_utility.set_location(l_package||l_function, 90);
723
724 l_temp_assignment_id := p_assignment_id;
725 l_ytd_id := get_defined_bal(l_bal_type_id,p_asg_ytd);
726 l_qtd_id := get_defined_bal(l_bal_type_id,p_asg_qtd);
727
728 END IF; -- Person/asg level balances
729
730 hr_utility.set_location(l_package||l_function, 100);
731
732 l_date := payvwele.get_fpd_or_atd(p_assignment_id => l_temp_assignment_id,
733 p_session_date => p_session_date);
734 -- Bugfix 3300433 start
735
736 IF l_date IS NOT NULL THEN
737 SELECT pps.final_process_date
738 INTO l_last_process_date
739 FROM per_periods_of_service pps
740 WHERE date_start <= p_session_date
741 AND pps.period_of_service_id = (
742 SELECT DISTINCT(period_of_service_id)
743 FROM per_all_assignments_f
744 WHERE assignment_id = l_temp_assignment_id
745 AND assignment_type = 'E'
746 );
747
748 -- Set the year end date as the final process date if not specified
749 IF l_last_process_date is NULL THEN
750 SELECT trunc(add_months(l_date,12),'Y')-1
751 INTO l_last_process_date
752 FROM dual;
753 END IF;
754
755 IF l_date>nvl(l_last_process_date,l_date) THEN
756 l_last_process_date:=l_date;
757 END IF;
758
759 IF p_session_date<l_last_process_date THEN
763
760 l_last_process_date:=p_session_date;
761 END IF;
762 END IF; -- Bugfix 3300433 end
764 hr_utility.set_location(l_package||l_function,110);
765
766 IF l_date IS NULL THEN
767 l_date := p_session_date; -- current emp
768 ELSIF l_date >= p_session_date THEN
769 l_date := p_session_date; -- current emp
770 ELSIF l_date < trunc(p_session_date, 'YEAR') THEN
771 -- terminated before this year, so, no balances for this year
772 l_date := p_session_date;
773 ELSIF l_date < trunc(p_session_date, 'MONTH') THEN
774 -- terminated this year but before this month
775 IF l_date >= trunc(p_session_date, 'Q') THEN
776 -- terminated this quarter
777 -- show QTD and YTD balances
778 IF l_ytd_id = l_defbal_id OR l_qtd_id = l_defbal_id THEN
779 l_date:=l_last_process_date; -- Bugfix 3300433;
780 ELSE
781 l_date := p_session_date;
782 END IF;
783 ELSE
784 -- only show YTD balance
785 IF l_ytd_id = l_defbal_id THEN
786 l_date:=l_last_process_date; -- Bugfix 3300433;
787 ELSE
788 l_date := p_session_date;
789 END IF;
790 END IF;
791 ELSE
792 -- terminated this year and this month
793 -- show all balances
794 l_date := p_session_date; -- Bugfix 3300433 and 3541052
795 END IF;
796 hr_utility.set_location(l_package||l_function,120);
797 -- set TAX_UNIT_ID context
798 pay_balance_pkg.set_context ('TAX_UNIT_ID', TO_CHAR(p_tax_unit_id));
799
800 l_value := pay_balance_pkg.get_value_lock( p_defined_balance_id => l_defbal_id,
801 p_assignment_id => l_temp_assignment_id,
802 p_virtual_date => l_date,
803 p_asg_lock => 'N' );
804 hr_utility.set_location(l_package||l_function, 130);
805
806 END IF;-- End assignment action check
807
808 hr_utility.set_location(l_package||l_function, 150);
809
810 RETURN l_value;
811
812 EXCEPTION
813 WHEN NO_DATA_FOUND THEN
814 hr_utility.set_location(l_package||l_function, 160);
815 RETURN NULL;
816 END; --End of function get_bal
817
818
819 /******************************************************************************
820 * Name : get_dedn_info
821 * Purpose : This procedure is used to copy element information
822 * in earning tables to deduction tables.
823 ******************************************************************************/
824 PROCEDURE get_dedn_info(p_earn_data IN earn_tbl,
825 p_dedn_data OUT NOCOPY dedn_tbl) IS
826 st_cnt number;
827 end_cnt number;
828 i number;
829 l_procedure VARCHAR2(15);
830 BEGIN
831 l_procedure :='get_dedn_info';
832 hr_utility.set_location(l_package||l_procedure, 10);
833
834 IF p_earn_data.COUNT>0 THEN
835 st_cnt:=p_earn_data.FIRST;
836 end_cnt:=p_earn_data.LAST;
837 FOR i IN st_cnt ..end_cnt
838 LOOP
839 IF p_earn_data.exists(i) THEN
840 p_dedn_data(i).row_id :=p_earn_data(i).row_id;
841 p_dedn_data(i).element_name :=p_earn_data(i).element_name;
842 p_dedn_data(i).element_type_id :=p_earn_data(i).element_type_id;
843 p_dedn_data(i).classification_id :=p_earn_data(i).classification_id ;
844 p_dedn_data(i).element_information10:=p_earn_data(i).element_information10;
845 p_dedn_data(i).element_information11:=p_earn_data(i).element_information11;
846 p_dedn_data(i).element_information12:=p_earn_data(i).element_information12;
847 p_dedn_data(i).element_information14:=p_earn_data(i).element_information14;
848 p_dedn_data(i).ptd :=p_earn_data(i).ptd;
849 p_dedn_data(i).month :=p_earn_data(i).month;
850 p_dedn_data(i).qtd :=p_earn_data(i).qtd;
851 p_dedn_data(i).ytd :=p_earn_data(i).ytd;
852 END IF;
853 END LOOP;
854 END IF;
855 hr_utility.set_location(l_package||l_procedure, 20);
856 END;
857
858
859 /******************************************************************************
860 * Name : get_asg_date
861 * Purpose : This procedure is used to get the correct assignment and date
862 * that have to be passed in the call to the package
863 * PAY_US_TAXBAL_VIEW_PKG
864 ******************************************************************************/
865 PROCEDURE get_asg_date IS
866 l_procedure varchar2(14);
867 BEGIN
868 l_ytd_date :=NULL;
869 l_qtd_date :=NULL;
870 l_procedure:='get_asg_date';
871 hr_utility.set_location(l_package||l_procedure, 10);
872
873 IF (p_assignment_action_id = -1 ) THEN
874 IF p_balance_level='PER' THEN
875
876 hr_utility.set_location(l_package||l_procedure, 20);
877 BEGIN
878 -- check to see if p_assignment_id exist as of l_date
879 select paf.assignment_id
880 into l_temp_assignment_id
881 from per_assignments_f paf,
885 and p_session_date between paf.effective_start_date
882 hr_soft_coding_keyflex hsk
883 where paf.assignment_id = p_assignment_id
884 and paf.soft_coding_keyflex_id = hsk.soft_coding_keyflex_id
886 and paf.effective_end_date
887 and hsk.segment1 = to_char(p_tax_unit_id);
888
889 l_temp_assignment_id := to_number(p_assignment_id);
890
891 hr_utility.set_location(l_package||l_procedure, 30);
892
893 EXCEPTION
894 WHEN NO_DATA_FOUND THEN
895 -- Attempt to find any assignment id for the person as of l_date
896 BEGIN
897 hr_utility.set_location(l_package||l_procedure, 40);
898
899
900 select paf2.assignment_id
901 into l_temp_assignment_id
902 from per_assignments_f paf1,
903 per_assignments_f paf2,
904 hr_soft_coding_keyflex hsk
905 where paf1.assignment_id = p_assignment_id
906 and paf2.person_id = paf1.person_id
907 and paf2.soft_coding_keyflex_id = hsk.soft_coding_keyflex_id
908 and p_session_date between paf2.effective_start_date
909 and paf2.effective_end_date
910 and hsk.segment1 = to_char(p_tax_unit_id)
911 and rownum=1;
912
913 hr_utility.set_location(l_package||l_procedure, 50);
914 EXCEPTION
915 WHEN NO_DATA_FOUND THEN
916 BEGIN
917 -- Find an assignment id for the person with an end date < l_date
918 -- and greater than trunc(p_session_date,y).
919 hr_utility.set_location(l_package||l_procedure, 60);
920
921 -- 4915420
922
923 select paf2.assignment_id
924 into l_temp_assignment_id
925 from per_all_assignments_f paf1,
926 per_all_assignments_f paf2,
927 hr_soft_coding_keyflex hsk
928 where paf1.assignment_id = p_assignment_id
929 and paf2.person_id = paf1.person_id
930 and paf2.soft_coding_keyflex_id = hsk.soft_coding_keyflex_id
931 and hsk.segment1 = to_char(p_tax_unit_id)
932 and paf2.effective_end_date < p_session_date
933 and paf2.effective_end_date >= trunc(p_session_date,'YYYY')
934 and paf2.effective_end_date =
935 (select MAX(paf3.effective_end_date)
936 from per_all_assignments_f paf3
937 where paf3.person_id = paf1.person_id
938 and paf3.effective_end_date < p_session_date
939 )
940 and rownum=1;
941
942 /* select paf2.assignment_id
943 into l_temp_assignment_id
944 from per_assignments_f paf1,
945 per_assignments_f paf2,
946 hr_soft_coding_keyflex hsk
947 where paf1.assignment_id = p_assignment_id
948 and paf2.person_id = paf1.person_id
949 and paf2.soft_coding_keyflex_id = hsk.soft_coding_keyflex_id
950 and hsk.segment1 = to_char(p_tax_unit_id)
951 and paf2.effective_end_date < p_session_date
952 and paf2.effective_end_date >= trunc(p_session_date,'YYYY')
953 and paf2.effective_end_date =
954 (select MAX(paf3.effective_end_date)
955 from per_assignments_f paf3
956 where paf3.person_id = paf1.person_id
957 and paf3.effective_end_date < p_session_date
958 )
959 and rownum=1; */
960
961 hr_utility.set_location(l_package||l_procedure, 70);
962
963 EXCEPTION
964 WHEN NO_DATA_FOUND THEN
965 hr_utility.set_location(l_package||l_procedure, 80);
966 NULL;
967 END;
968 END;
969 END;
970 ELSE -- Assignment level balances are required
971
972 hr_utility.set_location(l_package||l_procedure, 90);
973 l_temp_assignment_id := p_assignment_id;
974 END IF; -- Person/asg level balances
975
976 hr_utility.set_location(l_package||l_procedure, 100);
977
978 l_date := payvwele.get_fpd_or_atd(p_assignment_id => l_temp_assignment_id,
979 p_session_date => p_session_date);
980 -- Bugfix 3300433 start
981
982 IF l_date IS NOT NULL THEN
983 SELECT pps.final_process_date
984 into l_last_process_date
985 FROM per_periods_of_service pps
986 WHERE date_start <= p_session_date
987 AND pps.period_of_service_id =
988 ( SELECT DISTINCT(period_of_service_id)
989 FROM per_all_assignments_f
993 -- Set the year end date as the final process date if not specified
990 WHERE assignment_id = l_temp_assignment_id
991 AND assignment_type = 'E');
992
994 IF l_last_process_date is NULL THEN
995 SELECT trunc(add_months(l_date,12),'Y')-1
996 INTO l_last_process_date
997 FROM dual;
998 END IF;
999
1000 IF l_date>nvl(l_last_process_date,l_date) THEN
1001 l_last_process_date:=l_date;
1002 END IF;
1003
1004 IF p_session_date<l_last_process_date THEN
1005 l_last_process_date:=p_session_date;
1006 END IF;
1007 END IF; -- Bugfix 3300433 end
1008
1009 hr_utility.set_location(l_package||l_procedure,110);
1010
1011 IF l_date IS NULL THEN
1012 l_date := p_session_date; -- current emp
1013 ELSIF l_date >= p_session_date THEN
1014 l_date := p_session_date; -- current emp
1015 ELSIF l_date < trunc(p_session_date, 'YEAR') THEN
1016 -- terminated before this year, so, no balances for this year
1017 l_date := p_session_date;
1018 ELSIF l_date < trunc(p_session_date, 'MONTH') THEN
1019 -- terminated this year but before this month
1020 IF l_date >= trunc(p_session_date, 'Q') THEN
1021 -- terminated this quarter
1022 -- show QTD and YTD balances
1023 l_ytd_date:=l_last_process_date;
1024 l_qtd_date:=l_last_process_date;-- Bugfix 3300433;
1025 l_date := p_session_date;
1026 ELSE
1027 -- only show YTD balance
1028 l_ytd_date:=l_last_process_date;-- Bugfix 3300433;
1029 l_date := p_session_date;
1030 END IF;
1031 ELSE
1032 -- terminated this year and this month
1033 -- show all balances
1034 l_date := p_session_date; -- Bugfix 3300433 and 3541052
1035 END IF;
1036
1037 hr_utility.set_location(l_package||l_procedure,120);
1038 hr_utility.set_location(l_package||l_procedure, 130);
1039
1040 END IF;-- End assignment action check
1041
1042 hr_utility.set_location(l_package||l_procedure, 150);
1043
1044 END; --End of procedure get_asg_date
1045
1046
1047 BEGIN -- populate_element_info
1048
1049 l_package := 'pay_us_employee_balances.';
1050 l_procedure :='populate_element_info';
1051 -- hr_utility.trace_on(null,'EMPB');
1052 hr_utility.set_location(l_package||l_procedure, 10);
1053 p_flag:='N';
1054
1055 IF p_balance_level='ASG' THEN
1056 l_dim_month := 'ASG_GRE_MONTH';
1057 l_dim_qtd := 'ASG_GRE_QTD';
1058 l_dim_ytd := 'ASG_GRE_YTD';
1059 ELSE
1060 l_dim_month := 'PER_GRE_MONTH';
1061 l_dim_qtd := 'PER_GRE_QTD';
1062 l_dim_ytd := 'PER_GRE_YTD';
1063 END IF;
1064
1065 open c_action_type;
1066 fetch c_action_type INTO l_type;
1067 close c_action_type;
1068
1069 IF l_type ='Q' or l_type = 'R' THEN
1070 IF p_assignment_action_id <> -1 THEN -- Assignment_action_mode
1071 IF(p_balance_status ='Y' ) THEN
1072 I:=0;
1073 IF UPPER(p_classification_name) = 'ALIEN/EXPAT EARNINGS' THEN
1074 l_attribute_name := 'PAY_US_ALIEN_EXPAT_EARNINGS';
1075 ELSIF UPPER(p_classification_name) = 'EARNINGS' THEN
1076 l_attribute_name := 'PAY_US_EARNINGS';
1077 ELSIF UPPER(p_classification_name) = 'SUPPLEMENTAL EARNINGS' THEN
1078 l_attribute_name := 'PAY_US_SUPPLEMENTAL_EARNINGS';
1079 ELSIF UPPER(p_classification_name) = 'IMPUTED EARNINGS' THEN
1080 l_attribute_name := 'PAY_US_IMPUTED_EARNINGS';
1081 ELSIF UPPER(p_classification_name) = 'EMPLOYER LIABILITIES' THEN
1082 l_attribute_name := 'PAY_US_EMPLOYER_LIABILITY';
1083 ELSIF UPPER(p_classification_name) = 'NON-PAYROLL PAYMENTS' THEN
1084 l_attribute_name := 'PAY_US_NON_PAYROLL_PAYMENTS';
1085 ELSIF UPPER(p_classification_name) = 'PRE-TAX DEDUCTIONS' THEN
1086 l_attribute_name := 'PAY_US_PRE_TAX_DEDUCTIONS';
1087 ELSIF UPPER(p_classification_name) = 'INVOLUNTARY DEDUCTIONS' THEN
1088 l_attribute_name := 'PAY_US_INVOLUNTARY_DEDUCTIONS';
1089 ELSIF UPPER(p_classification_name) = 'VOLUNTARY DEDUCTIONS' THEN
1090 l_attribute_name := 'PAY_US_VOLUNTARY_DEDUCTIONS';
1091 END IF;
1092
1093 hr_utility.set_location('Balances Valid ASG/PER level balances', 40);
1094 -- Use the cursor csr_element_assact_info_dedn for Deductions
1095 -- as it checks for existence of Run balances for
1096 -- Arrears, Accrued, Towards Bond balance apart from
1097 -- the base balance
1098 if (UPPER(p_classification_name) = 'PRE-TAX DEDUCTIONS'
1099 or UPPER(p_classification_name) = 'INVOLUNTARY DEDUCTIONS'
1100 or UPPER(p_classification_name) = 'VOLUNTARY DEDUCTIONS') then
1101 OPEN csr_element_assact_info_dedn;
1102 LOOP
1103 FETCH csr_element_assact_info_dedn INTO
1104 p_earn_data(i).row_id
1105 ,p_earn_data(i).element_name
1109 ,p_earn_data(i).element_information11
1106 ,p_earn_data(i).element_type_id
1107 ,p_earn_data(i).classification_id
1108 ,p_earn_data(i).element_information10
1110 ,p_earn_data(i).element_information12
1111 ,p_earn_data(i).element_information14
1112 ,p_earn_data(i).ptd
1113 ,p_earn_data(i).month
1114 ,p_earn_data(i).qtd
1115 ,p_earn_data(i).ytd;
1116 EXIT WHEN csr_element_assact_info_dedn%NOTFOUND;
1117 i:=i+1;
1118 END LOOP;
1119 CLOSE csr_element_assact_info_dedn;
1120 else
1121 OPEN csr_element_assact_info;
1122 LOOP
1123 FETCH csr_element_assact_info INTO
1124 p_earn_data(i).row_id
1125 ,p_earn_data(i).element_name
1126 ,p_earn_data(i).element_type_id
1127 ,p_earn_data(i).classification_id
1128 ,p_earn_data(i).element_information10
1129 ,p_earn_data(i).element_information11
1130 ,p_earn_data(i).element_information12
1131 ,p_earn_data(i).element_information14
1132 ,p_earn_data(i).ptd
1133 ,p_earn_data(i).month
1134 ,p_earn_data(i).qtd
1135 ,p_earn_data(i).ytd;
1136 EXIT WHEN csr_element_assact_info%NOTFOUND;
1137 i:=i+1;
1138 END LOOP;
1139 CLOSE csr_element_assact_info;
1140
1141 end if; -- if (UPPER(p_classification_name) = 'PRE-TAX DEDUCTIONS'
1142 ELSE -- Balances are invalid
1143 i:=0;
1144 hr_utility.set_location(l_package||l_procedure, 20);
1145 hr_utility.set_location('Balances Invalid ASG/PER level balances', 60); -- delete
1146 OPEN csr_element_assact_runs;
1147 LOOP
1148 FETCH csr_element_assact_runs
1149 INTO p_earn_data(i).row_id
1150 ,p_earn_data(i).element_name
1151 ,p_earn_data(i).element_type_id
1152 ,p_earn_data(i).classification_id
1153 ,p_earn_data(i).element_information10
1154 ,p_earn_data(i).element_information11
1155 ,p_earn_data(i).element_information12
1156 ,p_earn_data(i).element_information14
1157 ,p_earn_data(i).ptd
1158 ,p_earn_data(i).month
1159 ,p_earn_data(i).qtd
1160 ,p_earn_data(i).ytd;
1161 EXIT WHEN csr_element_assact_runs%NOTFOUND;
1162 i:=i+1;
1163 END LOOP;
1164 CLOSE csr_element_assact_runs;
1165 END IF; --Balance Validity check
1166
1167 IF UPPER(p_classification_name) IN ('INVOLUNTARY DEDUCTIONS' ,
1168 'VOLUNTARY DEDUCTIONS',
1169 'PRE-TAX DEDUCTIONS') THEN
1170 get_dedn_info(p_earn_data,p_dedn_data);
1171 p_earn_data.delete();
1172 I:=P_DEDN_DATA.COUNT;
1173
1174 FOR I IN 0 .. P_DEDN_DATA.COUNt-1
1175 LOOP
1176 p_dedn_data_temp(i).accrued :=get_balance_name(p_dedn_data(i).element_information11);
1177 p_dedn_data_temp(i).arrears :=get_balance_name(p_dedn_data(i).element_information12);
1178 p_dedn_data_temp(i).tobond :=get_balance_name(p_dedn_data(i).element_information14);
1179 END LOOP;
1180
1181 FOR I IN 0 .. P_DEDN_DATA.COUNt-1
1182 LOOP
1183 p_dedn_data(i).accrued := PAY_US_TAXBAL_VIEW_PKG.US_NAMED_BALANCE_VM
1184 (UPPER(p_dedn_data_temp(i).accrued) ,
1185 'ASG_GRE_ITD' ,
1186 p_assignment_action_id ,
1187 NULL,
1188 NULL ,
1189 p_tax_unit_id,
1190 p_business_group_id ,
1191 NULL) ;
1192 p_dedn_data(i).arrears := PAY_US_TAXBAL_VIEW_PKG.US_NAMED_BALANCE_VM
1193 (UPPER(p_dedn_data_temp(i).arrears) ,
1194 'ASG_GRE_ITD' ,
1195 p_assignment_action_id ,
1196 NULL,
1197 NULL ,
1198 p_tax_unit_id,
1199 p_business_group_id ,
1200 NULL) ;
1201 p_dedn_data(i).tobond := PAY_US_TAXBAL_VIEW_PKG.US_NAMED_BALANCE_VM
1202 (UPPER(p_dedn_data_temp(i).tobond) ,
1203 'ASG_GRE_ITD' ,
1204 p_assignment_action_id ,
1208 p_business_group_id ,
1205 NULL,
1206 NULL ,
1207 p_tax_unit_id,
1209 NULL) ;
1210 END LOOP;
1211 p_dedn_data_temp.delete();
1212 END IF;
1213
1214 ELSE --Assignment Mode
1215 i:=0;
1216 OPEN c_get_element_runs;
1217 LOOP
1218 FETCH c_get_element_runs
1219 INTO p_earn_data(i).row_id
1220 ,p_earn_data(i).element_name
1221 ,p_earn_data(i).element_type_id
1222 ,p_earn_data(i).classification_id
1223 ,p_earn_data(i).element_information10
1224 ,p_earn_data(i).element_information11
1225 ,p_earn_data(i).element_information12
1226 ,p_earn_data(i).element_information14;
1227 EXIT WHEN c_get_element_runs %NOTFOUND;
1228 i:=i+1;
1229 END LOOP;
1230 CLOSE c_get_element_runs;
1231 IF p_element_type_id IS NOT NULL and p_earn_data.COUNT>0 THEN
1232 st_cnt := p_earn_data.first;
1233 end_cnt := p_earn_data.last;
1234
1235 hr_utility.set_location(l_package||l_procedure, 60);
1236
1237 FOR i IN st_cnt..end_cnt LOOP
1238 IF p_earn_data.exists(i) THEN
1239 IF p_element_type_id=p_earn_data(i).element_type_id THEN
1240 p_flag :='Y';
1241 j:=i;
1242 p_element_type_id :=j;
1243 exit;
1244 END IF;
1245 END IF;
1246 END LOOP;
1247
1248 hr_utility.set_location(l_package||l_procedure, 70);
1249
1250 END IF;
1251
1252 -- Fetch the balance for the classification and populate the corresponding
1253 -- PLSQL table to be used by form
1254 IF UPPER(p_classification_name) IN ('ALIEN/EXPAT EARNINGS',
1255 'EARNINGS',
1256 'SUPPLEMENTAL EARNINGS',
1257 'IMPUTED EARNINGS',
1258 'EMPLOYER LIABILITIES',
1259 'NON-PAYROLL PAYMENTS') THEN
1260 hr_utility.set_location(l_package||l_procedure, 80);
1261
1262 IF p_flag='Y' THEN
1263 IF p_balance_level='ASG' THEN
1264 p_earn_data(j).ptd :=get_defined_bal(p_earn_data(j).element_information10,p_asg_ptd);
1265 p_earn_data(j).month:=get_defined_bal(p_earn_data(j).element_information10,p_asg_month);
1266 p_earn_data(j).qtd :=get_defined_bal(p_earn_data(j).element_information10,p_asg_qtd);
1267 p_earn_data(j).ytd :=get_defined_bal(p_earn_data(j).element_information10,p_asg_ytd);
1268 ELSE
1269 p_earn_data(j).month:=get_defined_bal(p_earn_data(j).element_information10,p_per_month);
1270 p_earn_data(j).qtd :=get_defined_bal(p_earn_data(j).element_information10,p_per_qtd);
1271 p_earn_data(j).ytd :=get_defined_bal(p_earn_data(j).element_information10,p_per_ytd);
1272 END IF;
1273
1274 hr_utility.set_location(l_package||l_procedure, 90);
1275
1276 IF p_balance_level='ASG' THEN
1277 p_earn_data(j).ptd :=get_bal(p_earn_data(j).ptd,p_earn_data(j).element_information10);
1278 END IF;
1279 p_earn_data(j).month:=get_bal(p_earn_data(j).month,p_earn_data(j).element_information10);
1280 p_earn_data(j).qtd :=get_bal(p_earn_data(j).qtd,p_earn_data(j).element_information10);
1281 p_earn_data(j).ytd :=get_bal(p_earn_data(j).ytd,p_earn_data(j).element_information10);
1282
1283 hr_utility.set_location(l_package||l_procedure, 100);
1284 END IF; -- End of p_flag
1285
1286 IF p_element_type_id IS NULL THEN
1287 -- start of fetching balance values for earnings when
1288 -- element type id is not passed
1289 -- get DEFINED BALANCE IDS for asg/person for different time dimensions
1290 hr_utility.set_location(l_package||l_procedure, 110);
1291
1292 IF p_earn_data.COUNT>0 THEN
1293
1294 hr_utility.set_location(l_package||l_procedure, 120);
1295
1296 st_cnt := p_earn_data.first;
1297 end_cnt := p_earn_data.last;
1298
1299 FOR i IN st_cnt..end_cnt LOOP
1300 IF p_earn_data.exists(i) THEN
1301 IF p_balance_level='ASG' THEN
1302 p_earn_data(i).ptd :=get_defined_bal(p_earn_data(i).element_information10,p_asg_ptd);
1303 p_earn_data(i).month:=get_defined_bal(p_earn_data(i).element_information10,p_asg_month);
1304 p_earn_data(i).qtd :=get_defined_bal(p_earn_data(i).element_information10,p_asg_qtd);
1305 p_earn_data(i).ytd :=get_defined_bal(p_earn_data(i).element_information10,p_asg_ytd);
1306 ELSE
1307 p_earn_data(i).month:=get_defined_bal(p_earn_data(i).element_information10,p_per_month);
1311 END IF;
1308 p_earn_data(i).qtd :=get_defined_bal(p_earn_data(i).element_information10,p_per_qtd);
1309 p_earn_data(i).ytd :=get_defined_bal(p_earn_data(i).element_information10,p_per_ytd);
1310 END IF;
1312 END LOOP;
1313 hr_utility.set_location(l_package||l_procedure, 130);
1314 END IF;
1315
1316 -- end of fetching defined balance ids
1317
1318 hr_utility.set_location(l_package||l_procedure, 140);
1319
1320 -- get BALANCE values stored for asg/person for different time dimensions
1321
1322 IF p_earn_data.COUNT>0 THEN
1323 st_cnt :=p_earn_data.first;
1324 end_cnt :=p_earn_data.last;
1325
1326 hr_utility.set_location(l_package||l_procedure, 150);
1327
1328 FOR i IN st_cnt..end_cnt LOOP
1329 IF p_earn_data.exists(i) THEN
1330 IF p_balance_level='ASG' THEN
1331 p_earn_data(i).ptd :=get_bal(p_earn_data(i).ptd,p_earn_data(i).element_information10);
1332 END IF;
1333 p_earn_data(i).month:=get_bal(p_earn_data(i).month,p_earn_data(i).element_information10);
1334 p_earn_data(i).qtd :=get_bal(p_earn_data(i).qtd,p_earn_data(i).element_information10);
1335 p_earn_data(i).ytd :=get_bal(p_earn_data(i).ytd,p_earn_data(i).element_information10);
1336 END IF;
1337 END LOOP;
1338
1339 hr_utility.set_location(l_package||l_procedure, 160);
1340
1341 END IF;
1342 -- End of fetching balance values for earnings.
1343 hr_utility.set_location(l_package||l_procedure, 170);
1344 END IF;
1345 -- End of fetching all balance values for earnings
1346 -- when element_type_id is not passed
1347
1348 ELSE -- get the values for deduction elements
1349
1350 hr_utility.set_location(l_package||l_procedure, 180);
1351
1352 -- copy element information in the earning table to the deductions table
1353 get_dedn_info(p_earn_data,p_dedn_data);
1354 p_earn_data.delete;
1355
1356 IF p_flag='Y' THEN
1357 IF p_balance_level='ASG' THEN
1358 p_dedn_data(j).ptd := get_defined_bal(p_dedn_data(j).element_information10,p_asg_ptd);
1359 p_dedn_data(j).month := get_defined_bal(p_dedn_data(j).element_information10,p_asg_month);
1360 p_dedn_data(j).qtd := get_defined_bal(p_dedn_data(j).element_information10,p_asg_qtd);
1361 p_dedn_data(j).ytd := get_defined_bal(p_dedn_data(j).element_information10,p_asg_ytd);
1362 ELSE
1363 p_dedn_data(j).month := get_defined_bal(p_dedn_data(j).element_information10,p_per_month);
1364 p_dedn_data(j).qtd := get_defined_bal(p_dedn_data(j).element_information10,p_per_qtd);
1365 p_dedn_data(j).ytd := get_defined_bal(p_dedn_data(j).element_information10,p_per_ytd);
1366 END IF;
1367 p_dedn_data(j).accrued := get_defined_bal(p_dedn_data(j).element_information11,p_asg_itd);
1368 p_dedn_data(j).arrears := get_defined_bal(p_dedn_data(j).element_information12,p_asg_itd);
1369 p_dedn_data(j).tobond := get_defined_bal(p_dedn_data(j).element_information14,p_asg_itd);
1370
1371 hr_utility.set_location(l_package||l_procedure, 190);
1372
1373 IF p_balance_level='ASG' THEN
1374 p_dedn_data(j).ptd :=get_bal(p_dedn_data(j).ptd,p_dedn_data(j).element_information10);
1375 END IF;
1376 p_dedn_data(j).month :=get_bal(p_dedn_data(j).month,p_dedn_data(j).element_information10);
1377 p_dedn_data(j).qtd :=get_bal(p_dedn_data(j).qtd,p_dedn_data(j).element_information10);
1378 p_dedn_data(j).ytd :=get_bal(p_dedn_data(j).ytd,p_dedn_data(j).element_information10 );
1379 p_dedn_data(j).accrued :=get_bal(p_dedn_data(j).accrued,p_dedn_data(j).element_information11);
1380 p_dedn_data(j).arrears :=get_bal(p_dedn_data(j).arrears,p_dedn_data(j).element_information12);
1381 p_dedn_data(j).tobond :=get_bal(p_dedn_data(j).tobond,p_dedn_data(j).element_information14);
1382
1383 hr_utility.set_location(l_package||l_procedure, 200);
1384 END IF;--End of fetching balance values when p_flag is 'Y'
1385
1386 --start of code when element_type_id is not passed for deductions.
1387 --So all balance values are to be retrieved
1388 IF p_element_type_id IS NULL THEN
1389 -- fetch defined balance ids
1390 IF p_dedn_data.COUNT>0 THEN
1391 st_cnt :=p_dedn_data.first;
1392 end_cnt :=p_dedn_data.last;
1393
1394 hr_utility.set_location(l_package||l_procedure, 210);
1395
1396 FOR i IN st_cnt..end_cnt LOOP
1397 IF p_dedn_data.exists(i) THEN
1398 IF p_balance_level='ASG' THEN
1399 p_dedn_data(i).ptd := get_defined_bal(p_dedn_data(i).element_information10,p_asg_ptd);
1400 p_dedn_data(i).month := get_defined_bal(p_dedn_data(i).element_information10,p_asg_month);
1404 p_dedn_data(i).month := get_defined_bal(p_dedn_data(i).element_information10,p_per_month);
1401 p_dedn_data(i).qtd := get_defined_bal(p_dedn_data(i).element_information10,p_asg_qtd);
1402 p_dedn_data(i).ytd := get_defined_bal(p_dedn_data(i).element_information10,p_asg_ytd);
1403 ELSE
1405 p_dedn_data(i).qtd := get_defined_bal(p_dedn_data(i).element_information10,p_per_qtd);
1406 p_dedn_data(i).ytd := get_defined_bal(p_dedn_data(i).element_information10,p_per_ytd);
1407 END IF;
1408 p_dedn_data(i).accrued := get_defined_bal(p_dedn_data(i).element_information11,p_asg_itd);
1409 p_dedn_data(i).arrears := get_defined_bal(p_dedn_data(i).element_information12,p_asg_itd);
1410 p_dedn_data(i).tobond := get_defined_bal(p_dedn_data(i).element_information14,p_asg_itd);
1411 END IF;
1412 END LOOP;
1413 hr_utility.set_location(l_package||l_procedure, 220);
1414
1415 END IF;
1416
1417 hr_utility.set_location(l_package||l_procedure, 230);
1418
1419 -- get the balance values.
1420 IF p_dedn_data.COUNT>0 THEN
1421 st_cnt :=p_dedn_data.first;
1422 end_cnt :=p_dedn_data.last;
1423
1424 hr_utility.set_location(l_package||l_procedure, 240);
1425
1426 FOR i IN st_cnt..end_cnt LOOP
1427
1428 IF p_dedn_data.exists(i) THEN
1429 IF p_balance_level='ASG' THEN
1430 p_dedn_data(i).ptd :=get_bal(p_dedn_data(i).ptd,p_dedn_data(i).element_information10);
1431 END IF;
1432 p_dedn_data(i).month :=get_bal(p_dedn_data(i).month,p_dedn_data(i).element_information10);
1433 p_dedn_data(i).qtd :=get_bal(p_dedn_data(i).qtd,p_dedn_data(i).element_information10);
1434 p_dedn_data(i).ytd :=get_bal(p_dedn_data(i).ytd,p_dedn_data(i).element_information10 );
1435 p_dedn_data(i).accrued :=get_bal(p_dedn_data(i).accrued,p_dedn_data(i).element_information11);
1436 p_dedn_data(i).arrears :=get_bal(p_dedn_data(i).arrears,p_dedn_data(i).element_information12);
1437 p_dedn_data(i).tobond :=get_bal(p_dedn_data(i).tobond,p_dedn_data(i).element_information14);
1438 END IF;
1439
1440 END LOOP;
1441 hr_utility.set_location(l_package||l_procedure, 250);
1442
1443 END IF; -- End of fetching balance values
1444
1445 hr_utility.set_location(l_package||l_procedure, 260);
1446
1447 END IF; -- End of fetching balances when p_element_type_id is null;
1448 hr_utility.set_location(l_package||l_procedure, 270);
1449 END IF; --end earnings/deduction elements
1450
1451 END IF; --End Assignmentaction/assignment mode
1452
1453 hr_utility.set_location(l_package||l_procedure, 30);
1454
1455 ELSIF l_type='I' THEN
1456
1457 hr_utility.set_location(l_package||l_procedure, 40);
1458
1459 I:=0;
1460 get_asg_date; -- Get the correct assignment id and date
1461 hr_utility.set_location('Balance Initialization ASG/PER level balances', 300);
1462 OPEN c_element_asg_balances;
1463 LOOP
1464 FETCH c_element_asg_balances INTO
1465 p_earn_data(i).row_id
1466 ,p_earn_data(i).element_name
1467 ,p_earn_data(i).element_type_id
1468 ,p_earn_data(i).classification_id
1469 ,p_earn_data(i).element_information10
1470 ,p_earn_data(i).element_information11
1471 ,p_earn_data(i).element_information12
1472 ,p_earn_data(i).element_information14
1473 ,p_earn_data(i).ptd
1474 ,p_earn_data(i).month
1475 ,p_earn_data(i).qtd
1476 ,p_earn_data(i).ytd;
1477 EXIT WHEN c_element_asg_balances%NOTFOUND;
1478 i:=i+1;
1479 END LOOP;
1480 CLOSE c_element_asg_balances;
1481
1482 IF UPPER(p_classification_name) IN ('INVOLUNTARY DEDUCTIONS',
1483 'VOLUNTARY DEDUCTIONS',
1484 'PRE-TAX DEDUCTIONS') THEN
1485 get_dedn_info(p_earn_data,p_dedn_data);
1486 p_earn_data.delete();
1487 I:=P_DEDN_DATA.COUNT;
1488 FOR I IN 0 .. P_DEDN_DATA.COUNt-1 LOOP
1489 p_dedn_data_temp(i).accrued :=get_balance_name(p_dedn_data(i).element_information11);
1490 p_dedn_data_temp(i).arrears :=get_balance_name(p_dedn_data(i).element_information12);
1491 p_dedn_data_temp(i).tobond :=get_balance_name(p_dedn_data(i).element_information14);
1492 END LOOP;
1493
1494 FOR I IN 0 .. P_DEDN_DATA.COUNt-1 LOOP
1495 p_dedn_data(i).accrued := PAY_US_TAXBAL_VIEW_PKG.US_NAMED_BALANCE_VM
1496 (UPPER(p_dedn_data_temp(i).accrued) ,
1497 'ASG_GRE_ITD' ,
1498 NULL ,
1499 l_temp_assignment_id,
1500 l_date ,
1504 p_dedn_data(i).arrears := PAY_US_TAXBAL_VIEW_PKG.US_NAMED_BALANCE_VM
1501 p_tax_unit_id,
1502 p_business_group_id ,
1503 NULL) ;
1505 (UPPER(p_dedn_data_temp(i).arrears) ,
1506 'ASG_GRE_ITD' ,
1507 NULL ,
1508 l_temp_assignment_id,
1509 l_date ,
1510 p_tax_unit_id,
1511 p_business_group_id ,
1512 NULL) ;
1513 p_dedn_data(i).tobond := PAY_US_TAXBAL_VIEW_PKG.US_NAMED_BALANCE_VM
1514 (UPPER(p_dedn_data_temp(i).tobond) ,
1515 'ASG_GRE_ITD' ,
1516 NULL ,
1517 l_temp_assignment_id,
1518 l_date ,
1519 p_tax_unit_id,
1520 p_business_group_id ,
1521 NULL) ;
1522 END LOOP;
1523 p_dedn_data_temp.delete();
1524 END IF;
1525 hr_utility.set_location(l_package||l_procedure, 50);
1526 END IF; -- l_type check
1527
1528 IF p_element_type_id IS NOT NULL and p_assignment_action_id <> -1 THEN
1529 IF UPPER(p_classification_name) IN ('INVOLUNTARY DEDUCTIONS' ,
1530 'VOLUNTARY DEDUCTIONS',
1531 'PRE-TAX DEDUCTIONS') THEN
1532 IF p_dedn_data.count>0 THEN
1533 st_cnt := p_dedn_data.first;
1534 end_cnt := p_dedn_data.last;
1535
1536 hr_utility.set_location(l_package||l_procedure, 60);
1537
1538 FOR i IN st_cnt..end_cnt LOOP
1539 IF p_dedn_data.exists(i) THEN
1540 IF p_element_type_id=p_dedn_data(i).element_type_id THEN
1541 p_flag :='Y';
1542 j:=i;
1543 p_element_type_id :=j;
1544 exit;
1545 END IF;
1546 END IF;
1547 END LOOP;
1548 END IF;
1549 ELSE -- Earnings
1550 IF p_earn_data.count>0 THEN
1551 st_cnt := p_earn_data.first;
1552 end_cnt := p_earn_data.last;
1553
1554 hr_utility.set_location(l_package||l_procedure, 60);
1555
1556 FOR i IN st_cnt..end_cnt LOOP
1557 IF p_earn_data.exists(i) THEN
1558 IF p_element_type_id=p_earn_data(i).element_type_id THEN
1559 p_flag :='Y';
1560 j:=i;
1561 p_element_type_id :=j;
1562 exit;
1563 END IF;
1564 END IF;
1565 END LOOP;
1566 END IF;
1567 hr_utility.set_location(l_package||l_procedure, 70);
1568 END IF; --Earnings/Deduction
1569 END IF; -- ELEMENT_TYPE_ID is not null
1570 -- Fetch the balance for the classification and populate the corresponding
1571 -- PLSQL table to be used by form
1572
1573 EXCEPTION
1574 WHEN others THEN
1575 hr_utility.set_location(l_package||l_procedure, 280);
1576 raise_application_error(-20101, 'Error in '||l_package||l_procedure || ' - ' || sqlerrm);
1577 END;
1578 END pay_us_employee_balances;
1579
[Home] [Help]