[Home] [Help]
PACKAGE BODY: APPS.PAY_AC_ACTION_ARCH
Source
1 PACKAGE BODY PAY_AC_ACTION_ARCH AS
2 /* $Header: pyacxfrp.pkb 120.26.12010000.11 2008/09/16 12:53:47 sudedas ship $ */
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, US, 94065. *
17 * *
18 ******************************************************************
19
20 Name : pay_ac_action_arch
21
22 Change List
23 -----------
24 Date Name Vers Bug No Description
25 ----------- ---------- ------ ------- --------------------------
26 16-SEP-2008 sudedas 115.103 7348767 Modified get_xfr_elements,
27 7348838 get_missing_xfr_info to
28 populate action_info24
29 11-SEP-2008 asgugupt 115.102 7197824 Changed get_run_results and
30 get_run_results_rate cursors
31 in Proc Archive_addnl_elements
32 23-JUN-2008 sudedas 115.101 7197824 Changed get_current_element
33 ,archive_retro_element
34 ,archive_addnl_element
35 for Work at Home Condition
36 02-JUN-2008 sapalani 115.100 7120430 Used fnd_number.canonical_to_number
37 in procedure populate_summary.
38 Removed trace_off at the end of
39 procedure populate_hours_x_rate.
40 14-APR-2008 asgugupt 115.99 6950970 Modified get_current_elements
41 29-FEB-2008 sudedas 115.98 6663135 Changed other similar cursors
42 20-FEB-2008 sudedas 115.97 6831411 Kept Code for Canada intact
43 before US California OT Enh
44 Changed Cursors in get_current
45 _elements
46 23-DEC-2007 sudedas 115.96 6702864 Reverted Back Changes of 115.95
47 Changed get_current_elements,
48 Archive_retro_element
49 populate_elements Changed.
50 Changes on Top of 115.94
51 20-DEC-2007 sudedas 115.95 Changed get_current_elements,
52 Archive_retro_element,
53 Archive_addnl_elements
54 03-DEC-2007 tclewis 115.94 6663135 Removed the code processing cursor
55 retro_parent_check_flag and use check_retro
56 instead as its identical code.
57 22-SEP-2007 sausingh 115.93 5635335 Cahnged to archive ) value in case the YTD
58 value is null .
59 22-SEP-2007 Ahanda 115.92 5635335 Made changes in the to get the the
60 orignating date when offset date was
61 mentioned.
62 15-sep-2007 sausingh 115.91 5635335 Added nvl condition
63 13-sep-2007 sausingh 115.90 5635335 Added nvl condition while archiving ytd and
64 current amount in case of earnings and
65 deduction ( withelds)
66 5-Sep-2007 sausingh 115.88 6392875 Archiving rate through balance call
67 in populate_elements
68 03-Aug-2007 sausingh 115.87 5635335 Changes Archive_addnl_elements to calculate
69 ytd values from balance call
70 30-Aug-2007 sudedas 115.86 Changes Incorporated for Issues
71 found by Rick on Aug 24, 2007
72 23-Aug-2007 sudedas 115.82 Closing Cursors as per requirement.
73 21-AUG-2007 sausingh 115.81 Added action information24 to archive display name for deductions
74 17-Aug-2007 sausingh 115.80 5635335 Added two procedures Archive_retro_element
75 and Archive_addnl_elements to archive retro
76 elements in separate rows depending upon the
77 element_entry_id
78 30-Jul-2007 sausingh 115.79 5635335 Added cursors to archive Rate*Multiple
79 in a new segment Action_information22
80 06-Jun-2007 sausingh 115.78 5635335 Changed get_current_elements
81 to archive Original Date Earned.
82 15-NOV-2006 ahanda 115.77 Changed sql statement to
83 use base table instead secure
84 views.
85 27-OCT-2006 ahanda 115.76 5582224 Checking PL/SQL table count > 0
86 before starting loop.
87 12-OCT-2006 ppanda 115.75 5599167 Cursor c_check_baladj changed by
88 adding hint leading(PPA)
89 index(PPA,PAY_PAYROLL_ACTIONS_N51)
90 index(PAA,PAY_ASSIGNMENT_ACTIONS_N51)
91 Cursor c_prev_elements modified by
92 adding hint
93 ORDERED use_nl(PAA, PPA, PPF)
94 19-SEP-2006 sodhingr 115.74 5549032 Added ORDERED hint to c_prev_elements
95 11-JUL-2006 ppanda 115.73 Changed cursor c_prev_ytd_action_elements
96 for fixing R12 performance bug 5042715
97 13-APR-2006 ahanda 115.72 Changed populate_hours_x_rate
98 to use amount returned by
99 pay_hours_by_rate_v
100 08-Mar-2006 vpandya 115.71 Changed populate_hours_x_rate
101 procedure to fix retro issue
102 for Canada.
103 14-OCT-2005 ahanda 115.70 Changed the prev_ytd .. cursors
104 to not do a trunc on year but
105 pass it as a parameter.
106 06-OCT-2005 ahanda 115.69 4552807 Added process_baladj_elements
107 28-JUL-2005 ahanda 115.68 4507782 Changed cursor
108 c_multi_asg_prev_information
109 29-DEC-2004 ahanda 115.67 4069477 Changed procedure populate_elements
110 to remove special logic for
111 Non Payroll Payments
112 06-OCT-2004 ahanda 115.66 3940380 Added parameter p_xfr_action_id
113 to get_last_xfr_info and check
114 in cursor.
115 30-JUL-2004 ssattini 115.65 3498653 Added p_action_type parameter
116 to get_current_elements and
117 populate_elements procedures,
118 also added logic to archive
119 reversals and balance adjustments
120 in populate_elements procedure.
121 28-JUL-2004 vpandya 115.64 3780256 Added ORDERED hint to
122 c_prev_ytd_action_elem_rbr cursor.
123 Changed cursor c_last_xfr_elements
124 in get_xfr_element procedure to
125 get jurisdiction_code from previous
126 archived value.
127 19-JUL-2004 ahanda 115.63 3770899 Changed c_prev_ytd_action_elements
128 and c_prev_ytd_action_elem_rbr
129 to pick up elements processed from
130 1st and the passed date.
131 16-JUL-2004 ahanda 115.62 3767301 Added rpad and ltrim for state code
132 as JD in run balances might just
133 have a space.
134 16-JUL-2004 ahanda 115.61 3767301 Changed the run balance cursor
135 to do a substr on jurisdiction code
136 to ensure correct distinct JDs are
137 fetched. The table has JD values
138 like 05, 05-, 05-000-, 05-000-0.
139 20-MAY-2004 rsethupa 115.60 3639249 procedure process_additional_elements
140 set the balance context 'TAX_UNIT_ID'
141 to p_tax_unit_id in the beginning.
142 10-MAY-2004 ahanda 115.59 3567107 Changed get_xfr_elements procedure
143 to check if element is still valid
144 before archiving.
145 03-MAY-2004 kvsankar 115.58 3585754 Added a new cursor
146 'c_prev_ytd_action_elem_rbr'
147 which uses run balances to
148 retrieve the elements. This
149 cursor has to be executed instead
150 of 'c_prev_ytd_action_elements'
151 if Balance Initialization elements
152 are to be archived.
153 26-APR-2004 rsethupa 115.57 3559626 Removed code at the end of the
154 file that was used to initialize
155 the global variable
156 gv_correspondence_language of the
157 package pyempxfrp.pkb to get the
158 Accrual Information based on
159 Correspondance language.
160 16-APR-2004 rsethupa 115.56 3311866 US SS Payslip currency Format Enh.
161 Changed code to archive currency
162 in canonical format for the action
163 info categories 'AC EARNINGS',
164 'AC DEDUCTIONS', 'AC SUMMARY YTD'
165 and 'AC SUMMARY CURRENT'.
166 29-JAN-2004 rsethupa 115.55 3370112 11.5.10 Performance Changes
167 Modified cursor c_cur_action_elements
168 by removing the 'and exists' clause
169 28-JAN-2004 rsethupa 115.54 3370112 11.5.10 Performance Changes
170 14-JAN-2003 RMONGE 115.53 3360805 Remove hr. from pay_action_information
171 25-NOV-2003 vpandya 115.52 3280589 Changed get_xfr_elements:
172 modified cursor c_last_per_xfr_run.
173 07-NOV-2003 vpandya 115.51 3225286 Changed c_prev_ytd_action_elements
174 cursor and added condition for
175 Bal Adj (B) for action_type.
176 06-NOV-2003 vpandya 115.50 3239376 Changed get_xfr_elements:
177 Retreving action_information12
178 (ytd_hours) and initializing
179 variable ln_ytd_hours.
180 04-NOV-2003 vpandya 115.49 3228457 Changed c_last_per_xfr_run cursor:
181 Remove extra table
182 pay_action_information.
183 20-OCT-2003 vpandya 115.48 3119792 Changed process_additional_elements:
184 calling populate_summary to archive
185 summary for YTD.
186 04-OCT-2003 ahanda 115.47 3107166 Added date joins when getting
187 data from pay_element_types_f
188 10-Sep-2003 ekim 115.46 3119792 1) Added procedure
189 2880047 - process_additional_elements
190 2) Moved c_prev_ytd_action_elements
191 to be global.
192 3) Added following in
193 get_last_xfr_info procedure.
194 Cursor:
195 - c_multi_asg_prev_information
196 - c_multi_asg_prev_nonsepchk
197 Parameter:
198 - p_sepchk_flag
199 26-JUN-2003 vpandya 115.45 2950628 Changed populate_summary to archive
200 labels for CURRENT and YTD based on
201 correspondence language of an
202 employee. Also added cursor
203 c_arch_labels.
204 19-JUN-2003 ahanda 115.44 3018135 Changed populate_summary to populate
205 values for Alien/Expat Earnings.
206 19-JUN-2003 ahanda 115.43 3016946 Changed cursor to do an nvl
207 reporting_name and element_name.
208 11-Apr-2003 vpandya 115.42 Changed get_xfr_elements:
209 Removed Multi GRE cond. which was
210 with Multi Asg and SepChk cond.
211 25-Mar-2003 vpandya 115.41 Changed populate_hours_x_rate:
212 Taken out 'Exit' from GRE loop
213 and put it at common place so that
214 it works for GRE and Tax Group.
215 17-Mar-2003 ekim 115.40 Added index hint in
216 c_last_payment_info cursor.
217 14-Mar-2003 ekim 115.39 2851780 Added c_last_per_xfr_run in
218 get_xfr_elements.
219 07-Mar-2003 vpandya 115.38 2834674 Changed populate_hours_x_rate:
220 Divided hours_by_rate cursor into
221 c_run_aa_id and c_hbr cursor.
222 24-Feb-2003 vpandya 115.37 Changed get_current_elements:
223 added cursor c_ytd_action_seq and
224 changed cursor c_cur_action_elements
225 to get sep check elements.
226 Changed get_xfr_elements:
227 archive all elements of previous
228 xfr run when gv_multi_gre_payment
229 is 'N'.
230 06-Feb-2003 ekim 115.36 2315822 changed get_xfr_elements:
231 Added logic to get YTD for
232 the elements in the previous run
233 for the given assignment when
234 Multi-Asg is 'Y' and SEPCHK = 'Y'
235 06-FEB-2003 vpandya 115.35 2657464 Changed to get translated name of
236 an element. Changed all cursors
237 wherever reporting name is taken
238 from pay_element_types_f, now it is
239 taking from pay_element_types_f_tl.
243 of pay_emp_action_arch from all
240 Also changed populate_hours_x_rate.
241 02-DEC-2002 ahanda 115.34 Changed package to fix GSCC warning
242 19-NOV-2002 vpandya 115.33 Calling set_error_message function
244 exceptions to get error message
245 Remote Procedure Calls(RPC or Sub
246 program)
247 13-NOV-2002 ahanda 115.32 2667749 Changed get_missing_xfr_info
248 to set the JD for Tax Deduction
249 and insert value only if non Zero
250 01-NOV-2002 ahanda 115.31 Changed error handling.
251 25-OCT-2002 ahanda 115.30 - Changed code to set up
252 hours_bal_id
253 only for earnings and
254 2503094 - Resetting the category in
255 get_missing_xfr_info.
256 15-OCT-2002 tmehra 115.29 Added code to archive PQP
257 (Alien) Earnings.
258 09-SEP-2002 ahanda 115.26 2558228 Modified code to only set the
259 Jurisdiction for Tax Deduction.
260 06-SEP-2002 ahanda 115.25 Added stmts for GSCC warnings.
261 27-JUL-2002 ahanda 115.24 Added code to get the primary
262 balance if it is null. This will
263 happen only to existing US
264 customers for Tax Deduction.
265 12-JUL-2002 ahanda 115.23 Setting JD Balance only for US
266 10-JUL-2002 vpandya 115.22 2455729 Modified populate_elements,
267 put condition like don't assign
268 hours to pl/sql table if ytd and
269 payment amounts are zero.
270 17-JUN-2002 ahanda 115.21 2365908 Changed package to populate tax
271 deductions if location has changed.
272 13-JUN-2002 vpandya 115.20 Added populate_hours_x_rate proc.
273 to populate Hours by Rate(HBR)
274 element.
275 Changed check_hours_by_rate to
276 check whether HBR element exists in
277 PL/SQL table. Setting context for
278 'Tax Group' if reporting level is
279 'TAXGRP'(Canadian Req.)
280 15-MAY-2002 ahanda 115.19 2339387 Changed get_xfr_elements to reset
281 the variable for category.
282 Added procedures
283 - get_last_xfr_info
284 - get_last_pymt_info
285 07-MAY-2002 vpandya 115.18 Modified populate_summanry,
286 Added 'Taxable Benefits' in it for
287 AC SUMMARY CURRENT, AC CURRENT YTD
288 24-APR-2002 ahanda 115.17 Changed get_current_elements for
289 performance.
290 08-APR-2002 ahanda 115.16 Changed
291 - get_missing_xfr_info
292 - get_current_elements
293 - first_time_process
294 to pass NULL for hours if the
295 classification is of type Dedutions
296 18-MAR-2002 ahanda 115.15 2264358 Changed cursor
297 c_prev_ytd_action_elements
298 Fixed archiving for Bal Adj for
299 which Pre Pay flag is checked.
300 22-JAN-2002 ahanda 115.14 Moved get_multi_assignment_flag
301 to global package (pyempxfr.pkb)
302 26-JAN-2002 ahanda 115.13 Added dbdrv commands.
303 22-JAN-2002 ahanda 115.12 Changed package to take care
304 of Multi Assignment Processing.
305 01-NOV-2001 asasthan 115.10 2034976
306 30-OCT-2001 asasthan 115.9 YTD Hours BUg
307 26-OCT-2001 asasthan 115.8 Fix for Bug 2080689
308 03-OCT-2001 asasthan 115.7 Fix for Bug 2028415
309 03-OCT-2001 asasthan 115.6 Fix for Bug 2028415
310 02-OCT-2001 vpandya 115.5 canada Changes
311 21-SEP-2001 asasthan 115.4 Removed check for 'Fees' from
312 get_current_elements etc.
313 31-AUG-2001 asasthan 115.3 Modified populate_delta_earnings
314 29-AUG-2001 asasthan 115.2 Modified ytd balance calls.
315 17-JUL-2001 vpandya 115.1 Added 'Taxable Benefits'
316 classification and 'Hours by Rate'
317 for CA.
318 25-JUL-2001 asasthan 115.0 Created.
319
323 ** Package Local Variables
320 *******************************************************************/
321
322 /******************************************************************
324 ******************************************************************/
325 gv_package VARCHAR2(100) := 'pay_ac_action_arch';
326
327 gv_dim_asg_tg_ytd VARCHAR2(100) := '_ASG_TG_YTD';
328 gv_dim_asg_gre_ytd VARCHAR2(100) := '_ASG_GRE_YTD';
329 gv_dim_asg_jd_gre_ytd VARCHAR2(100) := '_ASG_JD_GRE_YTD';
330 gv_ytd_amount number(20,2) := 0;
331 gv_ytd_hour number(20,2) := 0;
332
333 cursor c_element_info(cp_element_type_id in number
334 ,cp_effective_date in date) is
335 select pet.element_information10 primary_balance,
336 pet.element_information12 hours_balance
337 from pay_element_types_f pet
338 where pet.element_type_id = cp_element_type_id
339 and cp_effective_date between pet.effective_start_date
340 and pet.effective_end_date;
341
342 cursor c_prev_ytd_action_elements(cp_assignment_id in number
343 ,cp_curr_eff_date in date
344 ,cp_start_eff_date in date
345 ,cp_action_type1 in varchar2
346 ,cp_action_type2 in varchar2
347 ,cp_action_type3 in varchar2
348 ) is
349 select /*+ ORDERED use_nl(PAA,PPA,PPF)
350 INDEX (paa PAY_ASSIGNMENT_ACTIONS_N51)
351 INDEX(ppa PAY_PAYROLL_ACTIONS_PK)
352 INDEX(prr PAY_RUN_RESULTS_N50)
353 INDEX(pcc PAY_ELEMENT_CLASSIFICATION_UK2) */
354 distinct
355 pec.classification_name,
356 pet.processing_priority,
357 nvl(decode(pec.classification_name,
358 'Tax Deductions', petl.reporting_name || ' Withheld',
359 petl.reporting_name),pet.element_name) reporting_name,
360 --pet.element_name,
361 decode(pec.classification_name,
362 'Tax Deductions', null,
363 prr.element_type_id) element_type_id,
364 --prr.element_type_id,
365 nvl(decode(pec.classification_name,
366 'Tax Deductions', prr.jurisdiction_code,
367 'Earnings',prr.jurisdiction_code), '00-000-0000'),
368 pet.element_information10,
369 pet.element_information12
370 from pay_assignment_actions paa,
371 pay_payroll_actions ppa,
372 pay_run_results prr,
373 pay_element_types_f pet,
374 pay_element_classifications pec,
375 pay_element_types_f_tl petl
376 where prr.assignment_action_id = paa.assignment_action_id
377 and paa.assignment_id = cp_assignment_id
378 and ppa.payroll_action_id = paa.payroll_action_id
379 and ppa.action_type in (cp_action_type1, cp_action_type2, cp_action_type3)
380 and ppa.effective_date >= cp_start_eff_date
381 and ppa.effective_date <= cp_curr_eff_date
382 and pet.element_type_id = prr.element_type_id
383 and pet.element_information10 is not null
384 and ppa.effective_date between pet.effective_start_date
385 and pet.effective_end_date
386 and petl.element_type_id = pet.element_type_id
387 and petl.language = gv_person_lang
388 and pec.classification_id = pet.classification_id
389 and pec.business_group_id is NULL
390 and pec.legislation_code = 'US'
391 and pec.classification_name in ('Earnings',
392 'Alien/Expat Earnings',
393 'Supplemental Earnings',
394 'Imputed Earnings',
395 'Taxable Benefits',
396 'Pre-Tax Deductions',
397 'Involuntary Deductions',
398 'Voluntary Deductions',
399 'Non-payroll Payments',
400 'Tax Deductions')
401 and pet.element_name not like '%Calculator'
402 and pet.element_name not like '%Special Inputs'
403 and pet.element_name not like '%Special Features'
404 and pet.element_name not like '%Special Features 2'
405 and pet.element_name not like '%Verifier'
406 and pet.element_name not like '%Priority'
407 order by 1, 3, 4;
408 --pec.classification_name, reporting_name, pet.element_name;
409
410 -- Bug 3585754
411 cursor c_prev_ytd_action_elem_rbr(cp_assignment_id in number
412 ,cp_curr_eff_date in date
413 ,cp_start_eff_date in date
414 ) is
415 select /*+ ORDERED INDEX(PRB PAY_RUN_BALANCES_N1
416 ,PDB PAY_DEFINED_BALANCES_PK
417 ,PBT PAY_BALANCE_TYPES_PK,
418 ,PET PAY_ELEMENT_TYPES_F_PK
419 ,PEC PAY_ELEMENT_CLASSIFICATION_PK
420 ,PETL PAY_ELEMENT_TYPES_F_TL_PK)
424 nvl(decode(pec.classification_name,
421 USE_NL(PRB, PDB, PBT, PET, PEC, PETL) */
422 distinct pec.classification_name,
423 pet.processing_priority,
425 'Tax Deductions', petl.reporting_name || ' Withheld',
426 petl.reporting_name), pet.element_name) reporting_name,
427 decode(pec.classification_name, 'Tax Deductions', null,
428 pet.element_type_id) element_type_id,
429 nvl(decode(pec.classification_name,
430 'Tax Deductions',
431 decode(pec.legislation_code,
432 'CA', substr(jurisdiction_code,1,2),
433 decode(to_char(length(replace(jurisdiction_code, '-'))),
434 '7', jurisdiction_code,
435 rpad(nvl(substr(rtrim(ltrim(jurisdiction_code)),1,2),'0')
436 ,2,'0') || '-'||
437 rpad(nvl(substr(rtrim(ltrim(jurisdiction_code)),4,3),'0')
438 ,3,'0') ||'-' ||
439 rpad(nvl(substr(rtrim(ltrim(jurisdiction_code)),8,4),'0')
440 ,4,'0')))), '00-000-0000') jurisdiction_code,
441 pet.element_information10,
442 pet.element_information12
443 from pay_run_balances prb
444 ,pay_defined_balances pdb
445 ,pay_balance_types pbt
446 ,pay_element_types_f pet
447 ,pay_element_classifications pec
448 ,pay_element_types_f_tl petl
449 where prb.effective_date >= cp_start_eff_date
450 and prb.effective_date <= cp_curr_eff_date
451 and prb.assignment_id = cp_assignment_id
452 and pet.element_information10 is not null
453 and pet.element_information10 = pbt.balance_type_id
454 and pbt.balance_type_id = pdb.balance_type_id
455 and pdb.defined_balance_id = prb.defined_balance_id
456 and prb.effective_date between pet.effective_start_date and pet.
457 effective_end_date
458 and petl.element_type_id = pet.element_type_id
459 and petl.language = gv_person_lang
460 and pec.classification_id = pet.classification_id
461 and pec.classification_name in ('Earnings',
462 'Alien/Expat Earnings',
463 'Supplemental Earnings',
464 'Imputed Earnings',
465 'Taxable Benefits',
466 'Pre-Tax Deductions',
467 'Involuntary Deductions',
468 'Voluntary Deductions',
469 'Non-payroll Payments',
470 'Tax Deductions')
471 and pet.element_name not like '%Calculator'
472 and pet.element_name not like '%Special Inputs'
473 and pet.element_name not like '%Special Features'
474 and pet.element_name not like '%Special Features 2'
475 and pet.element_name not like '%Verifier'
476 and pet.element_name not like '%Priority'
477 order by 1, 3, 4;
478
479 /******************************************************************
480 Name : initialization_process
481 Purpose : The procedure initializes the PL/SQL table -
482 pay_ac_action_arch.lrr_act_tab
483 Arguments :
484 Notes :
485 ******************************************************************/
486 PROCEDURE initialization_process
487 IS
488 lv_procedure_name VARCHAR2(100) := '.initialization_process';
489
490 lv_error_message VARCHAR2(200);
491 ln_step NUMBER;
492 i NUMBER := 0; -- used for label counter
493
494 cursor c_arch_labels is
495 select language, lookup_code, meaning
496 from fnd_lookup_values
497 where lookup_type = 'CA_CHEQUE_LABELS'
498 and lookup_code in ('CURRENT', 'YTD');
499
500
501 BEGIN
502 hr_utility.set_location(gv_package || lv_procedure_name, 10);
503 ln_step := 1;
504
505 if pay_ac_action_arch.lrr_act_tab.count > 0 then
506 for i in pay_ac_action_arch.lrr_act_tab.first ..
507 pay_ac_action_arch.lrr_act_tab.last loop
508 pay_ac_action_arch.lrr_act_tab(i).action_context_id := null;
509 pay_ac_action_arch.lrr_act_tab(i).action_context_type := null;
510 pay_ac_action_arch.lrr_act_tab(i).action_info_category := null;
511 pay_ac_action_arch.lrr_act_tab(i).jurisdiction_code := null;
512 pay_ac_action_arch.lrr_act_tab(i).act_info1 := null;
513 pay_ac_action_arch.lrr_act_tab(i).act_info2 := null;
514 pay_ac_action_arch.lrr_act_tab(i).act_info3 := null;
515 pay_ac_action_arch.lrr_act_tab(i).act_info4 := null;
516 pay_ac_action_arch.lrr_act_tab(i).act_info5 := null;
517 pay_ac_action_arch.lrr_act_tab(i).act_info6 := null;
518 pay_ac_action_arch.lrr_act_tab(i).act_info7 := null;
519 pay_ac_action_arch.lrr_act_tab(i).act_info8 := null;
520 pay_ac_action_arch.lrr_act_tab(i).act_info9 := null;
521 pay_ac_action_arch.lrr_act_tab(i).act_info10 := null;
522 pay_ac_action_arch.lrr_act_tab(i).act_info11 := null;
523 pay_ac_action_arch.lrr_act_tab(i).act_info12 := null;
527 pay_ac_action_arch.lrr_act_tab(i).act_info16 := null;
524 pay_ac_action_arch.lrr_act_tab(i).act_info13 := null;
525 pay_ac_action_arch.lrr_act_tab(i).act_info14 := null;
526 pay_ac_action_arch.lrr_act_tab(i).act_info15 := null;
528 pay_ac_action_arch.lrr_act_tab(i).act_info17 := null;
529 pay_ac_action_arch.lrr_act_tab(i).act_info18 := null;
530 pay_ac_action_arch.lrr_act_tab(i).act_info19 := null;
531 pay_ac_action_arch.lrr_act_tab(i).act_info20 := null;
532 pay_ac_action_arch.lrr_act_tab(i).act_info21 := null;
533 pay_ac_action_arch.lrr_act_tab(i).act_info22 := null;
534 pay_ac_action_arch.lrr_act_tab(i).act_info23 := null;
535 pay_ac_action_arch.lrr_act_tab(i).act_info24 := null;
536 pay_ac_action_arch.lrr_act_tab(i).act_info25 := null;
537 pay_ac_action_arch.lrr_act_tab(i).act_info26 := null;
538 pay_ac_action_arch.lrr_act_tab(i).act_info27 := null;
539 pay_ac_action_arch.lrr_act_tab(i).act_info28 := null;
540 pay_ac_action_arch.lrr_act_tab(i).act_info29 := null;
541 pay_ac_action_arch.lrr_act_tab(i).act_info30 := null;
542 end loop;
543 end if;
544
545 ln_step := 5;
546 pay_ac_action_arch.lrr_act_tab.delete;
547 pay_ac_action_arch.emp_state_jd.delete;
548 pay_ac_action_arch.emp_city_jd.delete;
549 pay_ac_action_arch.emp_county_jd.delete;
550 pay_ac_action_arch.emp_school_jd.delete;
551 pay_ac_action_arch.emp_elements_tab.delete;
552 pay_ac_action_arch.lrr_act_tab.delete;
553
554 if gv_reporting_level = 'TAXGRP' then
555 gv_ytd_balance_dimension := gv_dim_asg_tg_ytd;
556 else
557 gv_ytd_balance_dimension := gv_dim_asg_gre_ytd;
558 end if;
559
560 if pay_ac_action_arch.ltr_summary_labels.count = 0 then
561
562 i := 0;
563
564 for lbl in c_arch_labels loop
565
566 pay_ac_action_arch.ltr_summary_labels(i).language := lbl.language;
567 pay_ac_action_arch.ltr_summary_labels(i).lookup_code := lbl.lookup_code;
568 pay_ac_action_arch.ltr_summary_labels(i).meaning := lbl.meaning;
569
570 hr_utility.trace(pay_ac_action_arch.ltr_summary_labels(i).language);
571 hr_utility.trace(pay_ac_action_arch.ltr_summary_labels(i).lookup_code);
572 hr_utility.trace(pay_ac_action_arch.ltr_summary_labels(i).meaning);
573
574 i := i + 1;
575
576 end loop;
577
578 end if;
579
580 hr_utility.trace('pay_ac_action_arch.lrr_act_tab.count = ' ||
581 pay_ac_action_arch.lrr_act_tab.count);
582 hr_utility.set_location(gv_package || lv_procedure_name, 50);
583 ln_step := 10;
584
585 EXCEPTION
586 when others then
587 lv_error_message := 'Error at step ' || ln_step || ' in ' ||
588 gv_package || lv_procedure_name;
589
590 hr_utility.trace(lv_error_message || '-' || sqlerrm);
591
592 lv_error_message :=
593 pay_emp_action_arch.set_error_message(lv_error_message);
594
595 hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
596 hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
597 hr_utility.raise_error;
598
599 END initialization_process;
600
601
602 /******************************************************************
603 Name : get_last_xfr_info
604 Purpose : This returns the date and action_id of the last
605 External Process Archive run.
606 Arguments :
607 Notes :
608 ******************************************************************/
609 PROCEDURE get_last_xfr_info(p_assignment_id in number
610 ,p_curr_effective_date in date
611 ,p_action_info_category in varchar2
612 ,p_xfr_action_id in number
613 ,p_sepchk_flag in varchar2
614 ,p_last_xfr_eff_date out nocopy date
615 ,p_last_xfr_action_id out nocopy number
616 )
617 IS
618
619 cursor c_prev_run_information(cp_assignment_id in number
620 ,cp_action_info_category in varchar2
621 ,cp_xfr_action_id in number
622 ,cp_effective_date in date) is
623 select pai.effective_date,
624 pai.action_context_id
625 from pay_action_information pai
626 where pai.action_context_type = 'AAP'
627 and pai.assignment_id = cp_assignment_id
628 and pai.action_information_category = cp_action_info_category
629 and pai.action_context_id <> cp_xfr_action_id
630 and pai.effective_date <= cp_effective_date
631 order by pai.effective_date desc
632 ,pai.action_context_id desc;
633
634 cursor c_multi_asg_prev_information(
635 cp_assignment_id in number
636 ,cp_action_info_category in varchar2
637 ,cp_xfr_action_id in number
638 ,cp_effective_date in date) is
642 ,per_all_assignments_f paf
639 select pai.effective_date,
640 pai.action_context_id
641 from per_all_assignments_f paf2
643 ,pay_action_information pai
644 where paf2.assignment_id = cp_assignment_id
645 and paf.person_id = paf2.person_id
646 and pai.assignment_id = paf.assignment_id
647 and pai.action_context_type = 'AAP'
648 and pai.action_information_category = cp_action_info_category
649 and pai.effective_date <= cp_effective_date
650 and pai.effective_date >= trunc(cp_effective_date, 'Y')
651 and pai.action_context_id <> cp_xfr_action_id
652 order by pai.effective_date desc
653 ,pai.action_context_id desc;
654
655 ld_last_xfr_eff_date DATE;
656 ln_last_xfr_action_id NUMBER;
657 lv_procedure_name VARCHAR2(100) := '.get_last_xfr_info';
658
659 lv_error_message VARCHAR2(200);
660 ln_step NUMBER;
661
662 BEGIN
663 hr_utility.set_location(gv_package || lv_procedure_name, 10);
664 ln_step := 1;
665 if pay_emp_action_arch.gv_multi_payroll_pymt = 'Y' then
666 open c_multi_asg_prev_information(p_assignment_id,
667 p_action_info_category,
668 p_xfr_action_id,
669 p_curr_effective_date);
670 fetch c_multi_asg_prev_information into ld_last_xfr_eff_date
671 ,ln_last_xfr_action_id;
672 if c_multi_asg_prev_information%notfound then
673 hr_utility.trace('This process has not been run earlier');
674 end if;
675 close c_multi_asg_prev_information;
676
677 else
678
679 open c_prev_run_information(p_assignment_id,
680 p_action_info_category,
681 p_xfr_action_id,
682 p_curr_effective_date);
683 fetch c_prev_run_information into ld_last_xfr_eff_date
684 ,ln_last_xfr_action_id;
685 if c_prev_run_information%notfound then
686 hr_utility.trace('This process has not been run earlier');
687 end if;
688 close c_prev_run_information ;
689 end if;
690
691 ln_step := 5;
692 if ld_last_xfr_eff_date is not null then
693 if trunc(ld_last_xfr_eff_date,'Y') < trunc(p_curr_effective_date,'Y')
694 then
695 ld_last_xfr_eff_date := null;
696 ln_last_xfr_action_id := null;
697 end if;
698 end if;
699 hr_utility.trace('ld_last_xfr_eff_date '||to_char(ld_last_xfr_eff_date));
700 hr_utility.trace('ln_last_xfr_action_id '|| ln_last_xfr_action_id);
701
702 p_last_xfr_eff_date := ld_last_xfr_eff_date;
703 p_last_xfr_action_id := ln_last_xfr_action_id;
704
705 hr_utility.set_location(gv_package || lv_procedure_name, 50);
706 ln_step := 10;
707
708 EXCEPTION
709 when others then
710 lv_error_message := 'Error at step ' || ln_step || ' in ' ||
711 gv_package || lv_procedure_name;
712
713 hr_utility.trace(lv_error_message || '-' || sqlerrm);
714
715 lv_error_message :=
716 pay_emp_action_arch.set_error_message(lv_error_message);
717
718 hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
719 hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
720 hr_utility.raise_error;
721
722 END get_last_xfr_info;
723
724
725 /******************************************************************
726 Name : get_last_pymt_info
727 Purpose :
728 Arguments :
729 Notes :
730 ******************************************************************/
731 PROCEDURE get_last_pymt_info(p_assignment_id in number
732 ,p_curr_pymt_eff_date in date
733 ,p_last_pymt_eff_date out nocopy date
734 ,p_last_pymt_action_id out nocopy number
735 )
736 IS
737 cursor c_last_payment_info(cp_assignment_id in number
738 ,cp_curr_pymt_eff_date in date) is
739
740 select ppa.effective_date, paa.assignment_action_id
741 from pay_payroll_actions ppa,
742 pay_assignment_actions paa
743 where paa.assignment_id = p_assignment_id
744 and ppa.payroll_action_id = paa.payroll_action_id
745 and ppa.action_type in ('R','Q')
746 and ppa.effective_date < p_curr_pymt_eff_date
747 and ppa.effective_date in
748 ( select /*+ index(ppa1, pay_payroll_Actions_pk) */
749 max(ppa1.effective_date)
750 from pay_payroll_actions ppa1,
751 pay_assignment_actions paa1
752 where ppa1.payroll_action_id = paa1.payroll_action_id
753 and ppa1.action_type in ('R','Q')
754 and paa1.assignment_id = p_assignment_id
755 and ppa1.effective_date < p_curr_pymt_eff_date);
756
757 ld_last_pymt_eff_date DATE;
758 ln_last_pymt_action_id NUMBER;
759 lv_procedure_name VARCHAR2(100) := '.get_last_pymt_info';
760 lv_error_message VARCHAR2(200);
764 hr_utility.set_location(gv_package || lv_procedure_name, 10);
761 ln_step NUMBER;
762
763 BEGIN
765 ln_step := 1;
766 open c_last_payment_info(p_assignment_id,p_curr_pymt_eff_date);
767 fetch c_last_payment_info into ld_last_pymt_eff_date,
768 ln_last_pymt_action_id ;
769 close c_last_payment_info ;
770
771 ln_step := 5;
772 p_last_pymt_eff_date := ld_last_pymt_eff_date;
773 p_last_pymt_action_id := ln_last_pymt_action_id;
774
775 hr_utility.set_location(gv_package || lv_procedure_name, 100);
776 ln_step := 10;
777
778 EXCEPTION
779 when others then
780 lv_error_message := 'Error at step ' || ln_step || ' in ' ||
781 gv_package || lv_procedure_name;
782
783 hr_utility.trace(lv_error_message || '-' || sqlerrm);
784
785 lv_error_message :=
786 pay_emp_action_arch.set_error_message(lv_error_message);
787
788 hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
789 hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
790 hr_utility.raise_error;
791
792 END get_last_pymt_info;
793
794
795 /******************************************************************
796 Name : check_hours_by_rate
797 Purpose : The procedure checks whether element has already been
798 archived or not (Canadian Requirement).
799 Arguments :
800 Notes :
801 ******************************************************************/
802 PROCEDURE check_hours_by_rate(
803 p_xfr_action_id in number
804 ,p_puv_assignment_action_id in number
805 ,p_element_classification_name in varchar2
806 ,p_reporting_name in varchar2
807 ,p_element_type_id in number
808 ,p_primary_balance_id in number
809 ,p_processing_priority in number
810 ,p_tax_unit_id in number
811 ,p_pymt_eff_date in date
812 ,p_ytd_balcall_aaid in number
813 ,p_ytd_defined_balance_id in number
814 ,p_ytd_hours_balance_id in number
815 ,p_rate_exists out nocopy varchar2
816 )
817
818 IS
819 lv_procedure_name VARCHAR2(100) := '.check_hours_by_rate';
820 lv_error_message VARCHAR2(200);
821 ln_step NUMBER;
822
823 BEGIN
824 ln_step := 1;
825 p_rate_exists := 'N';
826
827 hr_utility.set_location(gv_package || lv_procedure_name, 10);
828 if pay_ac_action_arch.lrr_act_tab.count > 0 then
829 for i in pay_ac_action_arch.lrr_act_tab.first..
830 pay_ac_action_arch.lrr_act_tab.last
831 loop
832 if ( ( pay_ac_action_arch.lrr_act_tab(i).action_context_id =
833 p_xfr_action_id ) and
834 ( pay_ac_action_arch.lrr_act_tab(i).act_info2 =
835 p_element_type_id ) )
836 then
837 p_rate_exists := 'Y';
838 exit;
839 end if;
840 end loop;
841 end if;
842 hr_utility.trace('p_rate_exists = ' || p_rate_exists);
843
844 hr_utility.set_location(gv_package || lv_procedure_name, 20);
845 ln_step := 10;
846
847 EXCEPTION
848 when others then
849 lv_error_message := 'Error at step ' || ln_step || ' in ' ||
850 gv_package || lv_procedure_name;
851
852 hr_utility.trace(lv_error_message || '-' || sqlerrm);
853
854 lv_error_message :=
855 pay_emp_action_arch.set_error_message(lv_error_message);
856
857 hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
858 hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
859 hr_utility.raise_error;
860
861 END check_hours_by_rate;
862
863
864 /******************************************************************
865 Name : populate_elements
866 Purpose :
867 Arguments :
868 Notes :
869 ******************************************************************/
870 PROCEDURE populate_elements(p_xfr_action_id in number
871 ,p_pymt_assignment_action_id in number
872 ,p_pymt_eff_date in date
873 ,p_element_type_id in number
874 ,p_primary_balance_id in number
875 ,p_hours_balance_id in number
876 ,p_processing_priority in number
877 ,p_element_classification_name in varchar2
878 ,p_reporting_name in varchar2
879 ,p_tax_unit_id in number
880 ,p_ytd_balcall_aaid in number
881 ,p_pymt_balcall_aaid in number
882 ,p_jurisdiction_code in varchar2
883 default null
884 ,p_legislation_code in varchar2
888 default null
885 ,p_sepchk_flag in varchar2
886 ,p_sepchk_run_type_id in number
887 ,p_action_type in varchar2
889 ,p_original_date_earned in varchar2
890 default null
891 ,p_effective_start_date in varchar2
892 default null
893 ,p_effective_end_date in varchar2
894 default null
895 ,p_category in varchar2
896 default null
897
898 ,p_el_jurisdiction_code in varchar2
899 default null
900 ,p_final_rate in number
901 default null
902 ,p_ytd_flag in varchar2
903 )
904 IS
905
906 cursor c_non_sep_check(cp_pymt_assignment_action_id in number
907 ,cp_sepchk_run_type_id in number) is
908 select paa.assignment_action_id
909 from pay_action_interlocks pai,
910 pay_assignment_actions paa,
911 pay_payroll_actions ppa
912 where pai.locking_action_id = cp_pymt_assignment_action_id
913 and paa.assignment_action_id = pai.locked_action_id
914 and paa.payroll_action_id = ppa.payroll_action_id
915 and ppa.action_type in ('Q','R')
916 and ((nvl(paa.run_type_id, ppa.run_type_id) is null and
917 source_action_id is null) or
918 (nvl(paa.run_type_id, ppa.run_type_id) is not null and
919 source_action_id is not null and
920 paa.run_type_id <> cp_sepchk_run_type_id));
921
922
923 ln_current_hours NUMBER(15,2);
924 ln_payments_amount NUMBER(15,2);
925 ln_ytd_hours NUMBER(15,2);
926 ln_ytd_amount NUMBER(17,2);
927
928 ln_pymt_defined_balance_id NUMBER;
929 ln_pymt_hours_balance_id NUMBER;
930 ln_ytd_defined_balance_id NUMBER;
931 ln_ytd_hours_balance_id NUMBER;
932
933 lv_rate_exists VARCHAR2(1) := 'N';
934 ln_nonpayroll_balcall_aaid NUMBER;
935
936 ln_index NUMBER ;
937 lv_action_category VARCHAR2(50) := 'AC DEDUCTIONS';
938 lv_procedure_name VARCHAR2(100):= '.populate_elements';
939 lv_error_message VARCHAR2(200);
940
941 ln_step NUMBER;
942
943 BEGIN
944 ln_step := 1;
945 hr_utility.set_location(gv_package || lv_procedure_name, 10);
946 hr_utility.trace('p_pymt_assignment_action_id '
947 ||to_char(p_pymt_assignment_action_id));
948 hr_utility.trace('p_pymt_eff_date ='||to_char(p_pymt_eff_date));
949 hr_utility.trace('p_element_type_id ='||to_char(p_element_type_id));
950 hr_utility.trace('p_primary_balance_id ='||to_char(p_primary_balance_id));
951 hr_utility.trace('p_processing_priority='||to_char(p_processing_priority));
952 hr_utility.trace('p_reporting_name ='||p_reporting_name);
953 hr_utility.trace('p_ytd_balcall_aaid ='||to_char(p_ytd_balcall_aaid));
954 hr_utility.trace('p_pymt_balcall_aaid ='||to_char(p_pymt_balcall_aaid));
955 hr_utility.trace('p_legislation_code ='||p_legislation_code);
956 hr_utility.trace('p_hours_balance_id ='||to_char(p_hours_balance_id));
957
958 if pay_emp_action_arch.gv_multi_leg_rule is null then
959 pay_emp_action_arch.gv_multi_leg_rule
960 := pay_emp_action_arch.get_multi_legislative_rule(
961 p_legislation_code);
962 end if;
963
964 ln_step := 2;
965 if p_jurisdiction_code <> '00-000-0000' then
966 pay_balance_pkg.set_context('JURISDICTION_CODE', p_jurisdiction_code);
967 gv_ytd_balance_dimension := gv_dim_asg_jd_gre_ytd;
968 else
969 pay_balance_pkg.set_context('JURISDICTION_CODE', p_jurisdiction_code);
970 if gv_reporting_level = 'TAXGRP' then
971 gv_ytd_balance_dimension := gv_dim_asg_tg_ytd;
972 else
973 gv_ytd_balance_dimension := gv_dim_asg_gre_ytd;
974 end if;
975 end if;
976
977
978 ln_step := 3;
979 /*********************************************************
980 ** Get the defined balance_id for YTD call as it will be
981 ** same for all classification types.
982 *********************************************************/
983 ln_ytd_defined_balance_id
984 := pay_emp_action_arch.get_defined_balance_id(
985 p_primary_balance_id,
986 gv_ytd_balance_dimension,
987 p_legislation_code);
988
989 hr_utility.trace('ln_ytd_defined_balance_id = ' ||
990 ln_ytd_defined_balance_id);
991
992 ln_step := 4;
993 if p_hours_balance_id is not null then
997 p_hours_balance_id,
994 hr_utility.set_location(gv_package || lv_procedure_name, 20);
995 ln_ytd_hours_balance_id
996 := pay_emp_action_arch.get_defined_balance_id(
998 gv_ytd_balance_dimension,
999 p_legislation_code);
1000
1001 hr_utility.trace('ln_ytd_hours_balance_id = ' ||
1002 ln_ytd_hours_balance_id);
1003
1004 end if;
1005
1006 ln_step := 5;
1007 if p_legislation_code <> 'US' then
1008 hr_utility.set_location(gv_package || lv_procedure_name, 30);
1009 ln_step := 6;
1010 check_hours_by_rate(
1011 p_xfr_action_id => p_xfr_action_id
1012 ,p_puv_assignment_action_id => p_pymt_assignment_action_id
1013 ,p_element_classification_name => p_element_classification_name
1014 ,p_reporting_name => p_reporting_name
1015 ,p_element_type_id => p_element_type_id
1016 ,p_primary_balance_id => p_primary_balance_id
1017 ,p_processing_priority => p_processing_priority
1018 ,p_tax_unit_id => p_tax_unit_id
1019 ,p_pymt_eff_date => p_pymt_eff_date
1020 ,p_ytd_balcall_aaid => p_ytd_balcall_aaid
1021 ,p_ytd_defined_balance_id => ln_ytd_defined_balance_id
1022 ,p_ytd_hours_balance_id => ln_ytd_hours_balance_id
1023 ,p_rate_exists => lv_rate_exists
1024 );
1025 end if;
1026
1027 hr_utility.trace('lv_rate_exists = ' || lv_rate_exists);
1028
1029 if lv_rate_exists = 'N' then
1030 ln_step := 7;
1031 hr_utility.set_location(gv_package || lv_procedure_name, 40);
1032 if ln_ytd_defined_balance_id is not null then
1033 ln_ytd_amount := nvl(pay_balance_pkg.get_value(
1034 ln_ytd_defined_balance_id,
1035 p_ytd_balcall_aaid),0);
1036 end if;
1037
1038 if p_hours_balance_id is not null then
1039 hr_utility.set_location(gv_package || lv_procedure_name, 50);
1040 if ln_ytd_hours_balance_id is not null then
1041 ln_ytd_hours := nvl(pay_balance_pkg.get_value(
1042 ln_ytd_hours_balance_id,
1043 p_ytd_balcall_aaid),0);
1044 hr_utility.set_location(gv_package || lv_procedure_name, 60);
1045 end if;
1046 end if; --Hours
1047
1048 ln_step := 8;
1049 if p_pymt_balcall_aaid is not null then
1050 ln_step := 10;
1051 /* Added dimension _ASG_GRE_RUN for reversals and Balance
1052 Adjustments for Canada. Bug#3498653 */
1053 if p_action_type in ('B','V') then
1054 ln_pymt_defined_balance_id
1055 := pay_emp_action_arch.get_defined_balance_id(
1056 p_primary_balance_id,
1057 '_ASG_GRE_RUN',
1058 p_legislation_code);
1059 else
1060 if pay_emp_action_arch.gv_multi_leg_rule = 'Y' then
1061 ln_pymt_defined_balance_id
1062 := pay_emp_action_arch.get_defined_balance_id(
1063 p_primary_balance_id,
1064 '_ASG_PAYMENTS',
1065 p_legislation_code);
1066 else
1067 ln_pymt_defined_balance_id
1068 := pay_emp_action_arch.get_defined_balance_id(
1069 p_primary_balance_id,
1070 '_PAYMENTS',
1071 p_legislation_code);
1072 end if;
1073 end if; -- p_action_type in ('B','V')
1074 /* end of addition for Reversals and bal adjustments */
1075 hr_utility.trace('ln_pymt_defined_balance_id ' ||
1076 ln_pymt_defined_balance_id);
1077
1078 if ln_pymt_defined_balance_id is not null then
1079 ln_payments_amount := nvl(pay_balance_pkg.get_value(
1080 ln_pymt_defined_balance_id,
1081 p_pymt_balcall_aaid),0);
1082 hr_utility.trace('ln_payments_amount = ' ||ln_payments_amount);
1083 end if;
1084
1085 if p_hours_balance_id is not null then
1086 /* Added dimension _ASG_GRE_RUN for reversals and Balance
1087 Adjustments for Canada. Bug#3498653 */
1088 if p_action_type in ('B','V') then
1089 ln_pymt_hours_balance_id
1090 := pay_emp_action_arch.get_defined_balance_id(
1091 p_hours_balance_id
1092 ,'_ASG_GRE_RUN'
1093 ,p_legislation_code);
1094 else
1095 if pay_emp_action_arch.gv_multi_leg_rule = 'Y' then
1096 ln_pymt_hours_balance_id
1100 ,p_legislation_code);
1097 := pay_emp_action_arch.get_defined_balance_id(
1098 p_hours_balance_id
1099 ,'_ASG_PAYMENTS'
1101 else
1102 ln_pymt_hours_balance_id
1103 := pay_emp_action_arch.get_defined_balance_id(
1104 p_hours_balance_id
1105 ,'_PAYMENTS'
1106 ,p_legislation_code);
1107 end if;
1108 end if; -- p_action_type in ('B','V')
1109 /* end of addition for reversals and bal adjustments */
1110 hr_utility.trace('ln_pymt_hours_balance_id ' ||
1111 ln_pymt_hours_balance_id);
1112
1113 if ln_pymt_hours_balance_id is not null then
1114 ln_current_hours := nvl(pay_balance_pkg.get_value(
1115 ln_pymt_hours_balance_id,
1116 p_pymt_balcall_aaid),0);
1117 end if;
1118 hr_utility.set_location(gv_package || lv_procedure_name, 120);
1119 end if; --Hours
1120 end if; -- p_pymt_balcall_aaid is not null
1121
1122 ln_step := 15;
1123 if nvl(ln_ytd_amount, 0) <> 0 or nvl(ln_payments_amount, 0) <> 0 then
1124 ln_index := pay_ac_action_arch.lrr_act_tab.count;
1125 if p_element_classification_name in ('Earnings',
1126 'Supplemental Earnings',
1127 'Taxable Benefits',
1128 'Imputed Earnings',
1129 'Non-payroll Payments',
1130 'Alien/Expat Earnings') then
1131 hr_utility.set_location(gv_package || lv_procedure_name, 125);
1132 lv_action_category := 'AC EARNINGS';
1133 /* bug 6702864 We are not subtracting the Retro amount from the base element so added the if condition */
1134 /* pay_ac_action_arch.lrr_act_tab(ln_index).act_info11
1135 := fnd_number.number_to_canonical(ln_current_hours);
1136 */
1137 IF p_ytd_flag = 'N' then
1138 pay_ac_action_arch.lrr_act_tab(ln_index).act_info11
1139 := fnd_number.number_to_canonical(ln_current_hours);
1140 ELSE
1141 pay_ac_action_arch.lrr_act_tab(ln_index).act_info11
1142 := fnd_number.number_to_canonical((ln_current_hours) - gv_ytd_hour);
1143 END IF;
1144
1145 IF ln_current_hours <> 0 AND ln_payments_amount <> 0 THEN
1146 pay_ac_action_arch.lrr_act_tab(ln_index).act_info22
1147 := ln_payments_amount/ln_current_hours;/*Bug 3311866*/
1148
1149 ELSE
1150 pay_ac_action_arch.lrr_act_tab(ln_index).act_info22 := null;
1151 END IF;
1152
1153 IF p_ytd_flag = 'N' then
1154 pay_ac_action_arch.lrr_act_tab(ln_index).act_info12
1155 := fnd_number.number_to_canonical(ln_ytd_hours);
1156 ELSE
1157 pay_ac_action_arch.lrr_act_tab(ln_index).act_info12
1158 := fnd_number.number_to_canonical((ln_ytd_hours) - gv_ytd_hour);
1159 END IF;
1160 end if;
1161
1162 hr_utility.set_location(gv_package || lv_procedure_name, 130);
1163 /* Insert this into the plsql table if Current or YTD
1167 pay_ac_action_arch.lrr_act_tab(ln_index).jurisdiction_code
1164 amount is not Zero */
1165 pay_ac_action_arch.lrr_act_tab(ln_index).action_info_category
1166 := lv_action_category;
1168 := nvl(p_jurisdiction_code, '00-000-0000');
1169 pay_ac_action_arch.lrr_act_tab(ln_index).action_context_id
1170 := p_xfr_action_id;
1171 pay_ac_action_arch.lrr_act_tab(ln_index).act_info1
1172 := p_element_classification_name;
1173 pay_ac_action_arch.lrr_act_tab(ln_index).act_info2
1174 := p_element_type_id;
1175 pay_ac_action_arch.lrr_act_tab(ln_index).act_info6
1176 := p_primary_balance_id;
1177 pay_ac_action_arch.lrr_act_tab(ln_index).act_info7
1178 := p_processing_priority;
1179 /* bug 6702864 We are not subtracting the Retro amount from the base element so added the if condition */
1180 /* pay_ac_action_arch.lrr_act_tab(ln_index).act_info8
1181 := fnd_number.number_to_canonical(nvl(ln_payments_amount,0));
1182 */
1183
1184 IF p_ytd_flag = 'N' then
1185 pay_ac_action_arch.lrr_act_tab(ln_index).act_info8
1186 := fnd_number.number_to_canonical(nvl(ln_payments_amount,0));
1187 ELSE
1188 pay_ac_action_arch.lrr_act_tab(ln_index).act_info8
1189 := fnd_number.number_to_canonical(nvl(ln_payments_amount,0) - gv_ytd_amount);
1190 END IF;
1191
1192 hr_utility.trace('ln_amount := '||fnd_number.number_to_canonical(nvl(ln_payments_amount,0)));
1193
1194 IF p_ytd_flag = 'N' then
1195 pay_ac_action_arch.lrr_act_tab(ln_index).act_info9
1196 := fnd_number.number_to_canonical(nvl(ln_ytd_amount,0));
1197 ELSE
1198 pay_ac_action_arch.lrr_act_tab(ln_index).act_info9
1199 := fnd_number.number_to_canonical((ln_ytd_amount) - gv_ytd_amount);
1200 END IF;
1201 pay_ac_action_arch.lrr_act_tab(ln_index).act_info10
1202 := p_reporting_name;
1203 IF lv_action_category = 'AC DEDUCTIONS' THEN
1204 pay_ac_action_arch.lrr_act_tab(ln_index).act_info24
1205 := p_reporting_name;
1206 END IF;
1207 pay_ac_action_arch.lrr_act_tab(ln_index).act_info17
1208 := p_original_date_earned;
1209 hr_utility.trace('p_original_date_earned :=' || p_original_date_earned );
1210 pay_ac_action_arch.lrr_act_tab(ln_index).act_info18
1211 := p_effective_start_date;
1212 hr_utility.trace('p_effective_start_date := ' || p_effective_start_date );
1213 pay_ac_action_arch.lrr_act_tab(ln_index).act_info19
1214 := p_effective_end_date ;
1215 hr_utility.trace('p_effective_end_date:= ' || p_effective_end_date );
1216 pay_ac_action_arch.lrr_act_tab(ln_index).act_info20
1217 := p_category;
1218 hr_utility.trace('p_category ' || p_category );
1219 pay_ac_action_arch.lrr_act_tab(ln_index).act_info21
1220 := p_el_jurisdiction_code;
1221
1222 end if;
1223
1224 end if; -- lv_rate_exists = 'N'
1225
1226
1227
1228 hr_utility.set_location(gv_package || lv_procedure_name, 150);
1229 ln_step := 20;
1230
1231 EXCEPTION
1232 when others then
1233 hr_utility.set_location(gv_package || lv_procedure_name, 200);
1234 lv_error_message := 'Error at step ' || ln_step ||
1235 ' in ' || gv_package || lv_procedure_name;
1236
1237 hr_utility.trace(lv_error_message || '-' || sqlerrm);
1238
1239 lv_error_message :=
1240 pay_emp_action_arch.set_error_message(lv_error_message);
1241
1242 hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
1243 hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
1244 hr_utility.raise_error;
1245
1246 END populate_elements;
1247
1248 /******************************************************************
1249 Name : populate_hours_x_rate
1250 Purpose : The procedure gets all 'Hours by Rate' elements which
1251 have been processed in a given pre-payment.
1252 This also gets current and YTD amount,
1253 stores the values in a PL/SQL table.
1254 Arguments :
1255 Notes :
1256 ******************************************************************/
1257 PROCEDURE populate_hours_x_rate(p_xfr_action_id in number
1258 ,p_curr_pymt_action_id in number
1259 ,p_curr_pymt_eff_date in date
1260 ,p_assignment_id in number
1261 ,p_tax_unit_id in number
1262 ,p_sepchk_run_type_id in number
1263 ,p_sepchk_flag in varchar2
1264 ,p_pymt_balcall_aaid in number
1265 ,p_ytd_balcall_aaid in number
1266 ,p_legislation_code in varchar2
1267 )
1268
1269 IS
1270
1271 cursor c_run_aa_id(cp_pymt_action_id in number
1272 ,cp_assignment_id in number ) is
1273 select paa.assignment_action_id
1277 where pai.locking_action_id = cp_pymt_action_id
1274 ,paa.run_type_id
1275 from pay_assignment_actions paa,
1276 pay_action_interlocks pai
1278 and paa.assignment_action_id = pai.locked_action_id
1279 and paa.assignment_id = cp_assignment_id
1280 and paa.run_type_id is not null
1281 and not exists ( select 1
1282 from pay_run_types_f prt
1283 where prt.legislation_code = 'CA'
1284 and prt.run_type_id = paa.run_type_id
1285 and prt.run_method = 'C' );
1286
1287 cursor c_hbr(cp_assignment_action_id in number) is
1288 select hours.element_type_id,
1289 hours.element_name,
1290 hours.processing_priority,
1291 hours.rate,
1292 hours.multiple,
1293 hours.hours,
1294 hours.amount,
1295 hours.assignment_action_id
1296 from pay_hours_by_rate_v hours
1297 where hours.assignment_action_id = cp_assignment_action_id
1298 and legislation_code in ('US', 'CA') -- Bug 3370112
1299 and hours.element_type_id >= 0 -- Bug 3370112
1300 order by hours.processing_priority,hours.element_type_id;
1301
1302 cursor c_reporting_name(cp_element_type_id in number
1303 ,cp_language in varchar2) is
1304 select nvl(reporting_name, element_name)
1305 from pay_element_types_f_tl
1306 where element_type_id = cp_element_type_id
1307 and language = cp_language;
1308
1309 cursor c_classification(cp_element_type_id in number ) is
1310 select pec.classification_name,
1311 pet.element_information10 primary_balance_id,
1312 pet.element_information12 hours_balance_id
1313 from pay_element_types_f pet,
1314 pay_element_classifications pec
1315 where pet.element_type_id = cp_element_type_id
1316 and p_curr_pymt_eff_date between pet.effective_start_date
1317 and pet.effective_end_date
1318 and pec.classification_id = pet.classification_id;
1319
1320 cursor c_retro(cp_run_action_id in number
1321 ,cp_element_type_id in number) is
1322 select pepd.element_entry_id,
1323 sum(decode(piv.name, 'Pay Value', prrv.result_value)),
1324 sum(decode(piv.name, 'Hours', prrv.result_value)),
1325 nvl(sum(decode(piv.name, 'Multiple', prrv.result_value)),1),
1326 sum(decode(piv.name, 'Rate', prrv.result_value))
1327 from pay_run_results prr,
1328 pay_run_result_values prrv,
1329 pay_input_values_f piv,
1330 pay_entry_process_details pepd
1331 where piv.input_value_id = prrv.input_value_id
1332 and prr.element_type_id = cp_element_type_id
1333 and prr.run_result_id = prrv.run_result_id
1334 and prr.assignment_action_id = cp_run_action_id
1335 and prr.source_type = 'E'
1336 and pepd.element_entry_id = prr.source_id
1337 and pepd.source_asg_action_id is not null
1338 and result_value is not null
1339 group by pepd.element_entry_id;
1340
1341
1342 ln_element_type_id NUMBER;
1343 lv_element_name VARCHAR2(150);
1344 ln_processing_priority NUMBER;
1345
1346 ln_rate NUMBER;
1347 ln_multiple NUMBER;
1348 ln_hours NUMBER;
1349 ln_amount NUMBER;
1350
1351 lv_reporting_name VARCHAR2(150);
1352 lv_classification_name VARCHAR2(150);
1353 ln_primary_balance_id NUMBER;
1354 ln_hours_balance_id NUMBER;
1355
1356 ln_payments_amount NUMBER(15,2);
1357 ln_ytd_hours NUMBER(15,2) := 0;
1358 ln_ytd_amount NUMBER(15,2) := 0;
1359
1360 ln_tot_pymt_amt NUMBER(15,2);
1361 ln_pymt_def_bal_id NUMBER;
1362 ln_pymt_bal_amt NUMBER(15,2);
1363
1364 ln_index NUMBER ;
1365
1366 prev_element_type_id NUMBER := -1;
1367 prev_run_asg_act_id NUMBER := -1;
1368
1369 ln_gre_ytd_defined_bal_id NUMBER;
1370 ln_tg_ytd_defined_bal_id NUMBER;
1371 ln_hours_ytd_defined_bal_id NUMBER;
1372 lv_procedure_name VARCHAR2(100) := '.populate_hours_x_rate';
1373 lv_error_message VARCHAR2(200);
1374 ln_step NUMBER;
1375
1376 ln_assignment_action_id NUMBER;
1377 ln_run_type_id NUMBER;
1378
1379 ln_retro_rate NUMBER(15,5);
1380 ln_retro_multiple NUMBER(15,5);
1381 ln_retro_hours NUMBER(15,5);
1382 ln_retro_payvalue NUMBER(15,5);
1383 ln_retro_element_entry NUMBER;
1384
1385 i NUMBER := 0;
1386
1387 hbr pay_ac_action_arch.hbr_table;
1388
1389 BEGIN
1390
1391
1392 ln_step := 1;
1393 hr_utility.set_location(gv_package || lv_procedure_name, 10);
1394 hr_utility.trace('HBR p_curr_pymt_action_id : ' || p_curr_pymt_action_id);
1395 hr_utility.trace('HBR p_assignment_id : ' || p_assignment_id);
1396
1397 hbr.delete;
1398 ln_tot_pymt_amt := 0;
1399
1400 open c_run_aa_id(p_curr_pymt_action_id, p_assignment_id);
1401 loop
1402 fetch c_run_aa_id into ln_assignment_action_id
1403 ,ln_run_type_id;
1407 ln_assignment_action_id);
1404 exit when c_run_aa_id%notfound;
1405
1406 hr_utility.trace('HBR ln_assignment_action_id : ' ||
1408
1409 ln_step := 2;
1410
1411 open c_hbr(ln_assignment_action_id);
1412 loop
1413 fetch c_hbr into hbr(i);
1414
1415 exit when c_hbr%notfound;
1416
1417 i := i + 1;
1418 end loop;
1419 close c_hbr;
1420
1421 end loop;
1422 close c_run_aa_id;
1423
1424 if hbr.count > 0 then
1425 hr_utility.trace(' I came in first if ');
1426
1427 for j in hbr.first..hbr.last + 1
1428 loop
1429
1430 if ( j <> i ) then
1431 hr_utility.trace(' It came here one');
1432 ln_element_type_id := hbr(j).element_type_id;
1433 lv_element_name := hbr(j).element_name;
1434 ln_processing_priority := hbr(j).processing_priority;
1435 ln_rate := hbr(j).rate;
1436 ln_multiple := hbr(j).multiple;
1437 ln_hours := hbr(j).hours;
1438 ln_amount := hbr(j).amount;
1439 hr_utility.trace('element_type_id'||hbr(j).element_type_id);
1440 hr_utility.trace('element_name'||hbr(j).element_name);
1441 hr_utility.trace('hbr(j).rate'||hbr(j).rate);
1442 end if;
1443
1444 ln_step := 3;
1445
1446 if ( ( ln_element_type_id <> prev_element_type_id and
1447 prev_element_type_id <> -1 ) or
1448 ( j = i )
1449 ) then
1450
1451 hr_utility.trace('I came here two');
1452
1453 ln_step := 5;
1454 if gv_reporting_level = 'TAXGRP' then
1455 ln_tg_ytd_defined_bal_id
1456 := pay_emp_action_arch.get_defined_balance_id
1457 (ln_primary_balance_id,
1458 gv_dim_asg_tg_ytd,
1459 p_legislation_code);
1460 ln_hours_ytd_defined_bal_id
1461 := pay_emp_action_arch.get_defined_balance_id
1462 (ln_hours_balance_id,
1463 gv_dim_asg_tg_ytd,
1464 p_legislation_code);
1465 if ln_tg_ytd_defined_bal_id is not null then
1466 ln_ytd_amount := nvl(pay_balance_pkg.get_value(
1467 ln_tg_ytd_defined_bal_id,
1468 p_ytd_balcall_aaid),0);
1469 end if;
1470 if ln_hours_ytd_defined_bal_id is not null then
1471 ln_ytd_hours := nvl(pay_balance_pkg.get_value(
1472 ln_hours_ytd_defined_bal_id,
1473 p_ytd_balcall_aaid),0);
1474 end if;
1475 else
1476 ln_step := 10;
1477 ln_gre_ytd_defined_bal_id
1478 := pay_emp_action_arch.get_defined_balance_id
1479 (ln_primary_balance_id,
1480 gv_dim_asg_gre_ytd,
1481 p_legislation_code);
1482 ln_hours_ytd_defined_bal_id
1483 := pay_emp_action_arch.get_defined_balance_id
1484 (ln_hours_balance_id,
1485 gv_dim_asg_gre_ytd,
1486 p_legislation_code);
1487 if ln_gre_ytd_defined_bal_id is not null then
1488 ln_ytd_amount := nvl(pay_balance_pkg.get_value(
1489 ln_gre_ytd_defined_bal_id,
1490 p_ytd_balcall_aaid),0);
1491 end if;
1492 if ln_hours_ytd_defined_bal_id is not null then
1493 ln_ytd_hours := nvl(pay_balance_pkg.get_value(
1494 ln_hours_ytd_defined_bal_id,
1495 p_ytd_balcall_aaid),0);
1496 end if;
1497 end if;
1498
1499 hr_utility.trace('ytd balance = ' || ln_ytd_amount);
1500 hr_utility.trace('ytd hours = ' || ln_ytd_hours);
1501 pay_ac_action_arch.lrr_act_tab(ln_index).act_info9
1502 := ln_ytd_amount;
1503 pay_ac_action_arch.lrr_act_tab(ln_index).act_info12
1504 := ln_ytd_hours;
1505
1506 ln_ytd_amount := 0;
1507 ln_ytd_hours := 0;
1508
1509 ln_pymt_def_bal_id
1510 := pay_emp_action_arch.get_defined_balance_id
1511 (ln_primary_balance_id,
1512 '_ASG_PAYMENTS',
1513 p_legislation_code);
1514
1515 ln_pymt_bal_amt := nvl(pay_balance_pkg.get_value(
1516 ln_pymt_def_bal_id,
1520 hr_utility.trace('ln_tot_pymt_amt : '||ln_tot_pymt_amt);
1517 p_pymt_balcall_aaid),0);
1518
1519 hr_utility.trace('ln_pymt_bal_amt : '||ln_pymt_bal_amt);
1521 hr_utility.trace('prev_element_type_id: '||prev_element_type_id);
1522 hr_utility.trace('prev_run_asg_act_id : '||prev_run_asg_act_id);
1523
1524 IF ( ln_tot_pymt_amt <> ln_pymt_bal_amt ) THEN
1525
1526 hr_utility.trace('i came here third');
1527
1528 OPEN c_retro(prev_run_asg_act_id, prev_element_type_id);
1529 LOOP
1530 hr_utility.set_location(gv_package || lv_procedure_name,55);
1531 fetch c_retro into ln_retro_element_entry
1532 ,ln_retro_payvalue
1533 ,ln_retro_hours
1534 ,ln_retro_multiple
1535 ,ln_retro_rate;
1536 if c_retro%notfound then
1537 exit;
1538 end if;
1539
1540 hr_utility.trace('HBR Retro Values');
1541 hr_utility.trace('Pay Value='|| ln_retro_payvalue);
1542 hr_utility.trace('Hours ='|| ln_retro_hours);
1543 hr_utility.trace('Rate ='|| ln_retro_rate);
1544 hr_utility.trace('Multiple ='|| ln_retro_multiple);
1545
1546 IF nvl(ln_retro_multiple,0) = 0 THEN
1547 ln_retro_multiple := 1;
1548 END IF;
1549
1550 ln_index := pay_ac_action_arch.lrr_act_tab.count;
1551 hr_utility.trace('ln_index = ' || ln_index);
1552
1553
1554 ln_step := 20;
1555 pay_ac_action_arch.lrr_act_tab(ln_index).action_info_category
1556 := 'AC EARNINGS';
1557
1558 pay_ac_action_arch.lrr_act_tab(ln_index).jurisdiction_code
1559 := '00-000-0000';
1560 pay_ac_action_arch.lrr_act_tab(ln_index).act_info1
1561 := lv_classification_name;
1562 hr_utility.trace('action_info_category' || lv_classification_name);
1563 pay_ac_action_arch.lrr_act_tab(ln_index).act_info2
1564 := prev_element_type_id;
1565 hr_utility.trace('act_info2' || prev_element_type_id);
1566 pay_ac_action_arch.lrr_act_tab(ln_index).act_info6
1567 := ln_primary_balance_id;
1568 hr_utility.trace('act_info6' || ln_primary_balance_id);
1569 pay_ac_action_arch.lrr_act_tab(ln_index).act_info7
1570 := ln_processing_priority;
1571 hr_utility.trace('act_info7' || ln_processing_priority);
1572 pay_ac_action_arch.lrr_act_tab(ln_index).act_info8
1573 := fnd_number.number_to_canonical(ln_retro_payvalue);
1574 hr_utility.trace('act_info8' || fnd_number.number_to_canonical(ln_retro_payvalue));
1575 pay_ac_action_arch.lrr_act_tab(ln_index).act_info9
1576 := 0;
1577 pay_ac_action_arch.lrr_act_tab(ln_index).act_info10
1578 := lv_reporting_name;
1579 hr_utility.trace('act_info10' || lv_reporting_name);
1580 pay_ac_action_arch.lrr_act_tab(ln_index).act_info11
1581 := fnd_number.number_to_canonical(ln_retro_hours);
1582 hr_utility.trace('act_info11' || fnd_number.number_to_canonical(ln_retro_hours));
1583 pay_ac_action_arch.lrr_act_tab(ln_index).action_context_id
1584 := p_xfr_action_id;
1585 hr_utility.trace('action_context_id' || p_xfr_action_id);
1586
1587 pay_ac_action_arch.lrr_act_tab(ln_index).act_info13
1588 := fnd_number.number_to_canonical(ln_retro_rate * ln_retro_multiple);
1589 hr_utility.trace('act_info13' || fnd_number.number_to_canonical(ln_retro_rate * ln_retro_multiple));
1590 END LOOP;
1591 CLOSE c_retro;
1592 hr_utility.set_location(gv_package || lv_procedure_name, 77);
1593
1594 END IF;
1595
1596 ln_tot_pymt_amt := 0;
1597 ln_pymt_bal_amt := 0;
1598
1599 if ( j = i ) then
1600 exit;
1601 end if;
1602 end if;
1603
1604 hr_utility.trace('lv_element_name = ' || lv_element_name);
1605 hr_utility.trace('ln_rate = ' || ln_rate);
1606 hr_utility.trace('ln_amount = ' || ln_amount);
1607 hr_utility.trace('ln_multiple = ' || ln_multiple);
1608 hr_utility.trace('ln_hours = ' || ln_hours);
1609
1610 lv_reporting_name := lv_element_name;
1611
1612 ln_step := 15;
1613
1614 open c_reporting_name(ln_element_type_id,
1615 gv_person_lang);
1616 fetch c_reporting_name into lv_reporting_name;
1617 if ( c_reporting_name%notfound ) then
1618 lv_reporting_name := lv_element_name;
1619 end if;
1620 close c_reporting_name;
1621
1622 open c_classification(ln_element_type_id);
1623 fetch c_classification into lv_classification_name
1627
1624 ,ln_primary_balance_id
1625 ,ln_hours_balance_id;
1626 close c_classification;
1628 ln_payments_amount := ln_amount;
1629 ln_tot_pymt_amt := ln_tot_pymt_amt + ln_payments_amount;
1630
1631 /*Insert this into the plsql table */
1632
1633 hr_utility.set_location(gv_package || lv_procedure_name, 40);
1634 ln_index := pay_ac_action_arch.lrr_act_tab.count;
1635 hr_utility.trace('ln_index = ' || ln_index);
1636
1637
1638 ln_step := 20;
1639 pay_ac_action_arch.lrr_act_tab(ln_index).action_info_category
1640 := 'AC EARNINGS';
1641 pay_ac_action_arch.lrr_act_tab(ln_index).jurisdiction_code
1642 := '00-000-0000';
1643 pay_ac_action_arch.lrr_act_tab(ln_index).act_info1
1644 := lv_classification_name;
1645 hr_utility.trace('action_info_category2' || lv_classification_name);
1646 pay_ac_action_arch.lrr_act_tab(ln_index).act_info2
1647 := ln_element_type_id;
1648 hr_utility.trace('act_info22' || prev_element_type_id);
1649 pay_ac_action_arch.lrr_act_tab(ln_index).act_info6
1650 := ln_primary_balance_id;
1651 hr_utility.trace('act_info62' || ln_primary_balance_id);
1652 pay_ac_action_arch.lrr_act_tab(ln_index).act_info7
1653 := ln_processing_priority;
1654 hr_utility.trace('act_info72' || ln_processing_priority);
1655 pay_ac_action_arch.lrr_act_tab(ln_index).act_info8
1656 := fnd_number.number_to_canonical(ln_payments_amount);
1657 hr_utility.trace('act_info82' || fnd_number.number_to_canonical(ln_retro_payvalue)); /* Bug 3311866*/
1658 pay_ac_action_arch.lrr_act_tab(ln_index).act_info9
1659 := fnd_number.number_to_canonical(ln_ytd_amount);
1660
1661 pay_ac_action_arch.lrr_act_tab(ln_index).act_info10
1662 := lv_reporting_name;
1663 hr_utility.trace('act_info102' || lv_reporting_name);
1664 pay_ac_action_arch.lrr_act_tab(ln_index).act_info11
1665 := fnd_number.number_to_canonical(ln_hours);
1666 hr_utility.trace('act_info112' || fnd_number.number_to_canonical(ln_retro_hours));
1667 pay_ac_action_arch.lrr_act_tab(ln_index).action_context_id
1668 := p_xfr_action_id;
1669 -- pay_ac_action_arch.lrr_act_tab(ln_index).act_info12
1670 -- := fnd_number.number_to_canonical(ln_ytd_hours);
1671 pay_ac_action_arch.lrr_act_tab(ln_index).act_info13
1672 := fnd_number.number_to_canonical(ln_rate * nvl(ln_multiple,1));
1673 hr_utility.trace('act_info13' || fnd_number.number_to_canonical(ln_retro_rate * ln_retro_multiple));
1674 prev_element_type_id := ln_element_type_id;
1675 prev_run_asg_act_id := hbr(j).run_asg_act_id;
1676 end loop;
1677 end if;
1678
1679 hr_utility.set_location(gv_package || lv_procedure_name, 40);
1680 ln_step := 25;
1681 --hr_utility.trace_off;
1682
1683 EXCEPTION
1684 when others then
1685 lv_error_message := 'Error at step ' || ln_step ||
1686 ' in ' || gv_package || lv_procedure_name;
1687
1688 hr_utility.trace(lv_error_message || '-' || sqlerrm);
1689
1690 lv_error_message :=
1691 pay_emp_action_arch.set_error_message(lv_error_message);
1692
1693 hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
1694 hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
1695 hr_utility.raise_error;
1696
1697 END populate_hours_x_rate;
1698
1699
1700 /******************************************************************
1701 Name : get_current_elements
1702 Purpose : The procedure gets all the elements which have
1703 been processed in a given pre-payment.
1704 It also calls the populate_elements procedure
1705 which calls the Current and YTD balances and
1706 stores the values in a PL/SQL table.
1707 Arguments :
1708 Notes :
1709 ******************************************************************/
1710 PROCEDURE get_current_elements(p_xfr_action_id in number
1711 ,p_curr_pymt_action_id in number
1712 ,p_curr_pymt_eff_date in date
1713 ,p_assignment_id in number
1714 ,p_tax_unit_id in number
1715 ,p_sepchk_run_type_id in number
1716 ,p_sepchk_flag in varchar2
1717 ,p_pymt_balcall_aaid in number
1718 ,p_ytd_balcall_aaid in number
1719 ,p_legislation_code in varchar2
1720 ,p_action_type in varchar2 default null
1721 )
1722
1723 IS
1724
1725
1726 CURSOR get_run_action_id(cp_pre_as_action_id in number,
1727 cp_assignment_id in number
1728 ) IS
1729 SELECT paa.assignment_action_id
1730 FROM pay_action_interlocks pai, pay_assignment_actions paa,
1731 pay_payroll_actions ppa
1732 WHERE pai.locking_action_id =cp_pre_as_action_id
1736 AND paa.payroll_action_id = ppa.payroll_action_id;
1733 AND pai.locked_action_id = paa.assignment_action_id
1734 AND paa.assignment_id = cp_assignment_id
1735 AND paa.source_action_id IS NOT NULL
1737
1738 Cursor get_element_entry_id( cp_run_action_id in number ,
1739 cp_assignment_id in number ,
1740 cp_element_type_id in number ) IS
1741 SELECT distinct peef.element_entry_id
1742 FROM pay_element_entries_f peef,
1743 pay_assignment_actions paa,
1744 pay_payroll_actions ppa,
1745 per_time_periods ptp
1746 WHERE paa.assignment_action_id = cp_run_action_id
1747 AND ppa.payroll_action_id = paa.payroll_action_id
1748 AND ptp.payroll_id = ppa.payroll_id
1749 AND ppa.date_earned BETWEEN ptp.start_date AND ptp.end_date
1750 AND peef.assignment_id = cp_assignment_id
1751 AND peef.element_type_id = cp_element_type_id
1752
1753 /* Commenting as Ele Entry Eff Start / End Date may not match the following
1754 AND peef.effective_start_date BETWEEN ptp.start_date AND ptp.end_date
1755 AND peef.effective_end_date BETWEEN ptp.start_date AND ptp.end_date
1756 End of Comment */
1757
1758 AND NVL(ppa.date_earned, ppa.effective_date) BETWEEN peef.effective_start_date AND peef.effective_end_date ;
1759
1760 Cursor check_retro( cp_run_action_id in number ,
1761 cp_assignment_id in number ,
1762 cp_element_type_id in number ) IS
1763 SELECT distinct 'Y'
1764 FROM pay_element_entries_f peef,
1765 pay_assignment_actions paa,
1766 pay_payroll_actions ppa,
1767 per_time_periods ptp
1768 WHERE paa.assignment_action_id = cp_run_action_id
1769 AND ppa.payroll_action_id = paa.payroll_action_id
1770 AND ptp.payroll_id = ppa.payroll_id
1771 AND ppa.date_earned BETWEEN ptp.start_date AND ptp.end_date
1772 AND peef.assignment_id = cp_assignment_id
1773 AND peef.element_type_id = cp_element_type_id
1774 AND peef.creator_type IN ('R', 'EE', 'RR', 'NR', 'PR') -- Changed 25.08.2007
1775
1776
1777 /* Commenting as Ele Entry Eff Start / End Date may not match the following
1778 AND peef.effective_start_date BETWEEN ptp.start_date AND ptp.end_date
1779 AND peef.effective_end_date BETWEEN ptp.start_date AND ptp.end_date
1780 End of Comment*/
1781
1782 AND NVL(ppa.date_earned, ppa.effective_date) BETWEEN peef.effective_start_date AND peef.effective_end_date ;
1783
1784 CURSOR retro_parent_check_flag ( cp_run_action_id in number ,
1785 cp_assignment_id in number ,
1786 cp_element_type_id in number ) IS
1787 SELECT DISTINCT 'Y'
1788 FROM pay_element_entries_f peef,
1789 pay_assignment_actions paa,
1790 pay_payroll_actions ppa,
1791 per_time_periods ptp
1792 WHERE paa.assignment_action_id = cp_run_action_id
1793 AND ppa.payroll_action_id = paa.payroll_action_id
1794 AND ptp.payroll_id = ppa.payroll_id
1795 AND ppa.date_earned BETWEEN ptp.start_date AND ptp.end_date
1796 AND peef.assignment_id = cp_assignment_id
1797 AND peef.element_type_id = cp_element_type_id
1798 AND peef.creator_type NOT IN ('R', 'EE', 'RR', 'NR', 'PR') -- Changed on 25.08.2007
1799
1800 /* Commenting as Ele Entry Eff Start / End Date may not match the following
1801 AND peef.effective_start_date BETWEEN ptp.start_date AND ptp.end_date
1802 AND peef.effective_end_date BETWEEN ptp.start_date AND ptp.end_date
1803 End of Comment */
1804
1805 AND NVL(ppa.date_earned, ppa.effective_date) BETWEEN peef.effective_start_date AND peef.effective_end_date ;
1806
1807
1808 CURSOR archive_non_retro_elements ( cp_original_date_paid in varchar2,
1809 cp_element_entry_id in number,
1810 cp_run_assignment_action_id in number ) IS
1811
1812 select fnd_date.date_to_canonical(ptp.start_date),
1813 fnd_date.date_to_canonical(ptp.end_date),
1814 hr_general.decode_lookup
1815 (DECODE (UPPER (ec.classification_name),
1816 'EARNINGS', 'US_EARNINGS',
1817 'SUPPLEMENTAL EARNINGS', 'US_SUPPLEMENTAL_EARNINGS',
1818 'IMPUTED EARNINGS', 'US_IMPUTED_EARNINGS',
1819 'NON-PAYROLL PAYMENTS', 'US_PAYMENT',
1820 'ALIEN/EXPAT EARNINGS', 'PER_US_INCOME_TYPES',
1821 NULL
1822 ),
1823 et.element_information1
1824 ) CATEGORY
1825 from pay_assignment_actions paa,
1826 pay_payroll_actions ppa,
1827 per_time_periods ptp,
1828 pay_element_entries_f peef,
1829 pay_element_classifications ec,
1830 pay_element_types et
1831 where paa.assignment_action_id = cp_run_assignment_action_id
1832 and paa.payroll_action_id = ppa.payroll_action_id
1833 and ptp.payroll_id = ppa.payroll_id
1834 and cp_original_date_paid between ptp.start_date AND ptp.end_date
1835 and peef.element_entry_id = cp_element_entry_id
1839 CURSOR get_application_column_name IS
1836 and et.element_type_id = peef.element_type_id
1837 and et.classification_id = ec.classification_id;
1838
1840
1841 SELECT application_column_name
1842 FROM FND_DESCR_FLEX_COL_USAGE_VL
1843 WHERE end_user_column_name = 'Originating Pay Period'
1844 AND upper(descriptive_flexfield_name) = upper('PAY_ELEMENT_ENTRIES')
1845 AND upper(descriptive_flex_context_code) = 'US EARNINGS';
1846
1847
1848 CURSOR get_num_addnl_elements ( cp_run_action_id IN NUMBER,
1849 cp_assignment_id IN NUMBER,
1850 cp_element_type_id IN NUMBER) IS
1851
1852 SELECT COUNT (*)
1853 FROM pay_element_entries_f peef,
1854 pay_assignment_actions paa,
1855 pay_payroll_actions ppa,
1856 per_time_periods ptp
1857 WHERE paa.assignment_action_id = cp_run_action_id
1858 AND ppa.payroll_action_id = paa.payroll_action_id
1859 AND ptp.payroll_id = ppa.payroll_id
1860 AND ppa.date_earned BETWEEN ptp.start_date AND ptp.end_date
1861 AND peef.assignment_id = cp_assignment_id
1862 AND peef.element_type_id = cp_element_type_id
1863 AND peef.creator_type NOT IN ('R', 'EE', 'RR', 'NR', 'PR')
1864
1865 /* Commenting as Ele Entry Eff Start / End Date may not match the following
1866 AND peef.effective_start_date BETWEEN ptp.start_date AND ptp.end_date
1867 AND peef.effective_end_date BETWEEN ptp.start_date AND ptp.end_date
1868 End of Comment */
1869
1870 AND NVL(ppa.date_earned, ppa.effective_date) BETWEEN peef.effective_start_date AND peef.effective_end_date ;
1871
1872 -- Added For Work At Home Condition
1873
1874 CURSOR c_cur_get_wrkathome(cp_assignment_id IN NUMBER) IS
1875 SELECT NVL(paf.work_at_home, 'N')
1876 ,ppf.person_id
1877 ,ppf.business_group_id
1878 FROM per_assignments_f paf
1879 ,per_all_people_f ppf
1880 WHERE paf.assignment_id = cp_assignment_id
1881 AND paf.person_id = ppf.person_id;
1882
1883 CURSOR c_cur_home_state_jd(cp_person_id IN NUMBER
1884 ,cp_bg_id IN NUMBER) IS
1885 SELECT pus.state_code || '-000-0000'
1886 FROM per_addresses pa
1887 ,pay_us_states pus
1888 WHERE pa.person_id = cp_person_id
1889 AND pa.primary_flag = 'Y'
1890 AND p_curr_pymt_eff_date between pa.date_from AND NVL(pa.date_to, hr_general.END_OF_TIME)
1891 AND pa.business_group_id = cp_bg_id
1892 AND pa.region_2 = pus.state_abbrev
1893 AND pa.style = p_legislation_code;
1894
1895 cursor c_cur_sp_action_elements(cp_pymt_action_id in number
1896 ,cp_assignment_id in number
1897 ,cp_sepchk_run_type in number
1898 ,cp_sepchk_flag in varchar2
1899 ) is
1900 select distinct prr.element_type_id,
1901 pec.classification_name,
1902 nvl(petl.reporting_name, petl.element_name),
1903 pet.element_information10,
1904 pet.element_information12,
1905 pet.processing_priority
1906 from pay_assignment_actions paa,
1907 pay_payroll_actions ppa,
1908 pay_run_results prr,
1909 pay_element_types_f pet,
1910 pay_element_classifications pec,
1911 pay_element_types_f_tl petl
1912 where paa.assignment_id = cp_assignment_id
1913 and prr.assignment_action_id = paa.assignment_action_id
1914 and cp_sepchk_flag = 'Y'
1915 and paa.assignment_action_id = cp_pymt_action_id
1916 and nvl(paa.run_type_id, cp_sepchk_run_type) = cp_sepchk_run_type
1917 and ppa.payroll_action_id = paa.payroll_action_id
1918 and pet.element_type_id = prr.element_type_id
1919 and pet.element_information10 is not null
1920 and ppa.effective_date between pet.effective_start_date
1921 and pet.effective_end_date
1922 and petl.element_type_id = pet.element_type_id
1923 and petl.language = gv_person_lang
1924 and pec.classification_id = pet.classification_id
1925 and pec.classification_name in ('Earnings',
1926 'Alien/Expat Earnings',
1927 'Supplemental Earnings',
1928 'Imputed Earnings',
1929 'Taxable Benefits',
1930 'Pre-Tax Deductions',
1931 'Involuntary Deductions',
1932 'Voluntary Deductions',
1933 'Non-payroll Payments'
1934 )
1935 and pet.element_name not like '%Calculator'
1936 and pet.element_name not like '%Special Inputs'
1937 and pet.element_name not like '%Special Features'
1938 and pet.element_name not like '%Special Features 2'
1939 and pet.element_name not like '%Verifier'
1940 and pet.element_name not like '%Priority'
1941 order by pec.classification_name;
1942
1943 cursor c_cur_action_elements(cp_pymt_action_id in number
1944 ,cp_assignment_id in number
1945 ,cp_sepchk_run_type in number
1949 select distinct pet.element_type_id,
1946 ,cp_sepchk_flag in varchar2
1947 ,cp_ytd_act_sequence in number
1948 ) is
1950 pec.classification_name,
1951 nvl(petl.reporting_name, petl.element_name),
1952 pet.element_information10,
1953 pet.element_information12,
1954 pet.processing_priority
1955 from pay_action_interlocks pai,
1956 pay_assignment_actions paa,
1957 pay_payroll_actions ppa,
1958 pay_all_payrolls_f ppf,
1959 pay_run_results prr,
1960 pay_element_types_f pet,
1961 pay_element_classifications pec,
1962 pay_element_types_f_tl petl
1963 where paa.assignment_id = cp_assignment_id
1964 and prr.assignment_action_id = paa.assignment_action_id
1965 and cp_sepchk_flag = 'N'
1966 and pai.locking_action_id = cp_pymt_action_id
1967 and paa.assignment_action_id = pai.locked_action_id
1968 and paa.action_sequence <= cp_ytd_act_sequence
1969 and ppa.payroll_action_id = paa.payroll_action_id
1970 and pet.element_type_id = prr.element_type_id
1971 and pet.element_information10 is not null
1972 and ppa.effective_date between pet.effective_start_date
1973 and pet.effective_end_date
1974 and ppa.payroll_id = ppf.payroll_id -- Bug 3370112
1975 and ppf.payroll_id >= 0
1976 and ppa.effective_date between ppf.effective_start_date
1977 and ppf.effective_end_date
1978 and petl.element_type_id = pet.element_type_id
1979 and petl.language = gv_person_lang
1980 and pec.classification_id = pet.classification_id
1981 and pec.classification_name in ('Earnings',
1982 'Alien/Expat Earnings',
1983 'Supplemental Earnings',
1984 'Imputed Earnings',
1985 'Taxable Benefits',
1986 'Pre-Tax Deductions',
1987 'Involuntary Deductions',
1988 'Voluntary Deductions',
1989 'Non-payroll Payments'
1990 )
1991 and pet.element_name not like '%Calculator'
1992 and pet.element_name not like '%Special Inputs'
1993 and pet.element_name not like '%Special Features'
1994 and pet.element_name not like '%Special Features 2'
1995 and pet.element_name not like '%Verifier'
1996 and pet.element_name not like '%Priority'
1997 order by pec.classification_name;
1998
1999 cursor c_ytd_action_seq(cp_asg_act_id in number) is
2000 select paa.action_sequence
2001 from pay_assignment_actions paa
2002 where paa.assignment_action_id = cp_asg_act_id;
2003 --Bug 6950970 starts here
2004 CURSOR get_payroll_date_earned(cp_run_action_id IN NUMBER) IS
2005 SELECT
2006 TO_CHAR(TRUNC(fnd_date.canonical_to_date(fnd_date.date_to_canonical(ppa.date_earned))),'DD-MON-YYYY')
2007 FROM pay_assignment_actions paa,
2008 pay_payroll_actions ppa
2009 WHERE paa.assignment_action_id = cp_run_action_id
2010 AND ppa.payroll_action_id = paa.payroll_action_id;
2011 l_date_earned VARCHAR2(100);
2012 --Bug 6950970 ends here
2013 ln_element_type_id NUMBER;
2014 lv_element_classification_name VARCHAR2(80);
2015 lv_reporting_name VARCHAR2(80);
2016 ln_primary_balance_id NUMBER;
2017 ln_hours_balance_id NUMBER;
2018 ln_processing_priority NUMBER;
2019 ln_ytd_action_sequence NUMBER;
2020
2021 ln_element_index NUMBER ;
2022 lv_procedure_name VARCHAR2(100) := '.get_current_elements';
2023 lv_error_message VARCHAR2(200);
2024 ln_step NUMBER;
2025 lv_original_date_earned VARCHAR2(100);
2026 lv_effective_start_date VARCHAR2(100);
2027 lv_effective_end_date VARCHAR2(100);
2028 lv_category VARCHAR2(100);
2029 ln_run_assignment_action_id NUMBER;
2030 ln_element_entry_id NUMBER;
2031 lv_original_date_paid VARCHAR2(100);
2032 lv_application_column_name VARCHAR2(100);
2033 lv_sqlstr varchar2(300);
2034 ld_original_date_paid date;
2035 ln_flag number;
2036 lv_jurisdiction_flag varchar2(20);
2037 ln_rate number := null ;
2038 ln_final_rate number := null ;
2039 lv_retro_flag varchar2(100) :='N';
2040 ln_multiple number ;
2041 ln_addnl_ele_num number ;
2042 lv_retro_parent_flag varchar2(10) := 'N';
2043 lv_sqlstr1 varchar2(2000);
2044 lv_curr_pymt_eff_date VARCHAR2(100);
2045 -- Added For Work At Home Condition
2046 lv_wrk_at_home per_assignments_f.work_at_home%TYPE;
2047 ln_person_id per_people_f.person_id%TYPE;
2048 ln_bg_id per_people_f.business_group_id%TYPE;
2049
2050 BEGIN
2051 ln_flag := 0;
2052 ln_step := 1;
2053 hr_utility.set_location(gv_package || lv_procedure_name, 10);
2054 hr_utility.trace('p_xfr_action_id = ' || p_xfr_action_id);
2055 hr_utility.trace('p_assignment_id ' || p_assignment_id);
2056 hr_utility.trace('p_tax_unit_id ' || p_tax_unit_id);
2057 hr_utility.trace('p_sepchk_flag ' || p_sepchk_flag);
2058 hr_utility.trace('p_legislation_code '|| p_legislation_code);
2059 hr_utility.trace('p_curr_pymt_action_id '
2060 ||to_char(p_curr_pymt_action_id ));
2061 hr_utility.trace('p_ytd_balcall_aaid ' || p_ytd_balcall_aaid);
2062 hr_utility.trace('p_pymt_balcall_aaid ' ||p_pymt_balcall_aaid);
2063 hr_utility.trace('p_sepchk_run_type_id '|| p_sepchk_run_type_id);
2064 hr_utility.trace('p_curr_pymt_eff_date '||TO_CHAR(p_curr_pymt_eff_date,'DD-MON-YYYY'));
2065
2066 hr_utility.set_location(gv_package || lv_procedure_name, 20);
2067
2068 if p_legislation_code <> 'US' then
2069 ln_step := 5;
2070 populate_hours_x_rate(p_xfr_action_id => p_xfr_action_id
2071 ,p_curr_pymt_action_id => p_curr_pymt_action_id
2072 ,p_curr_pymt_eff_date => p_curr_pymt_eff_date
2073 ,p_assignment_id => p_assignment_id
2074 ,p_tax_unit_id => p_tax_unit_id
2075 ,p_sepchk_run_type_id => p_sepchk_run_type_id
2076 ,p_sepchk_flag => p_sepchk_flag
2077 ,p_pymt_balcall_aaid => p_pymt_balcall_aaid
2078 ,p_ytd_balcall_aaid => p_ytd_balcall_aaid
2079 ,p_legislation_code => p_legislation_code);
2080 end if;
2081
2082 ln_step := 6;
2083 open c_ytd_action_seq(p_ytd_balcall_aaid);
2084 fetch c_ytd_action_seq into ln_ytd_action_sequence;
2085 close c_ytd_action_seq;
2086
2087 ln_step := 10;
2088 if p_sepchk_flag = 'Y' then
2089 open c_cur_sp_action_elements(p_curr_pymt_action_id ,
2090 p_assignment_id,
2091 p_sepchk_run_type_id,
2092 p_sepchk_flag);
2093
2094 elsif p_sepchk_flag = 'N' then
2095 open c_cur_action_elements(p_curr_pymt_action_id ,
2096 p_assignment_id,
2097 p_sepchk_run_type_id,
2098 p_sepchk_flag,
2099 ln_ytd_action_sequence);
2100 end if;
2101
2102 loop
2103 if p_sepchk_flag = 'Y' then
2104 fetch c_cur_sp_action_elements into
2105 ln_element_type_id,
2106 lv_element_classification_name,
2107 lv_reporting_name,
2108 ln_primary_balance_id,
2109 ln_hours_balance_id,
2110 ln_processing_priority;
2111 if c_cur_sp_action_elements%notfound then
2112 hr_utility.set_location(gv_package || lv_procedure_name, 30);
2113 exit;
2114 end if;
2115
2116 elsif p_sepchk_flag = 'N' then
2117 fetch c_cur_action_elements into
2118 ln_element_type_id,
2119 lv_element_classification_name,
2120 lv_reporting_name,
2121 ln_primary_balance_id,
2122 ln_hours_balance_id,
2123 ln_processing_priority;
2124 --- here one thing can be added
2125 if c_cur_action_elements%notfound then
2126 hr_utility.set_location(gv_package || lv_procedure_name, 35);
2127 exit;
2128 end if;
2129
2130 end if;
2131 -- loop with the first coursor (if not found then exit )
2132 -- if the parameters from second cursor not null then exit loop else move in loop completly
2133
2134 hr_utility.trace('Element_type_id in get_current_elements = ' || ln_element_type_id);
2135
2136 if p_legislation_code <> 'US' then
2137 lv_retro_flag := 'N' ;
2138 lv_retro_parent_flag := 'N';
2139 gv_ytd_amount := 0;
2143 lv_effective_end_date := NULL;
2140 gv_ytd_hour := 0;
2141 lv_original_date_earned := NULL;
2142 lv_effective_start_date := NULL;
2144 lv_category := NULL;
2145 lv_jurisdiction_flag := NULL;
2146 lv_original_date_paid:= NULL;
2147 end if;
2148
2149 IF p_legislation_code = 'US' THEN
2150
2151 open get_run_action_id( p_curr_pymt_action_id ,
2152 p_assignment_id);
2153 LOOP -- For Each Run Assignment Action ID
2154
2155 FETCH get_run_action_id INTO ln_run_assignment_action_id ;
2156 IF get_run_action_id%notfound THEN
2157 close get_run_action_id ;
2158 exit;
2159 END IF;
2160 ln_step := 99;
2161 --
2162 -- Following to Check IF Additional element Entry DFF Configured for the Client
2163 -- This would be configured in case Client Does NOT use Retropay Functionality
2164
2165 ln_step := 100;
2166
2167 ln_flag := 1;
2168
2169 OPEN get_application_column_name ;
2170 FETCH get_application_column_name INTO lv_application_column_name;
2171 CLOSE get_application_column_name;
2172
2173 IF lv_application_column_name IS NULL THEN
2174 ln_flag :=1;
2175 ELSE
2176 ln_flag :=0; -- Addl Ele DFF Info Configured
2177 END IF;
2178
2179 hr_utility.trace('Step 100: ln_flag before entering into Ele Entry LOOP : '||ln_flag);
2180
2181 -- Following Code May Need revise
2182 -- Here we are NOT Looping Through the Ele Entries
2183 -- But Getting ele Entry ID so that we Can Check if Addl Ele Entry Configured
2184 --
2185 -- Check if there is any Ele Entry that is NOT created by Retro
2186
2187 OPEN get_element_entry_id (ln_run_assignment_action_id,
2188 p_assignment_id ,
2189 ln_element_type_id);
2190
2191
2192 FETCH get_element_entry_id INTO ln_element_entry_id;
2193 IF get_element_entry_id%found THEN
2194 CLOSE get_element_entry_id;
2195 hr_utility.trace('Ele Entry Found. ln_element_entry_id := '||ln_element_entry_id);
2196
2197 IF ln_flag = 0 then -- Addl Ele DFF Info Configured
2198 hr_utility.trace('Addl Ele DFF Info Configured.');
2199
2200 SELECT TO_CHAR(TRUNC(fnd_date.canonical_to_date(fnd_date.date_to_canonical(p_curr_pymt_eff_date))),'DD-MON-YYYY')
2201 INTO lv_curr_pymt_eff_date
2202 FROM DUAL;
2203 --bug no 6950970 starts here
2204 OPEN get_payroll_date_earned (ln_run_assignment_action_id);
2205 FETCH get_payroll_date_earned INTO l_date_earned;
2206 CLOSE get_payroll_date_earned;
2207 --bug no 6950970 ends here
2208 hr_utility.trace('lv_curr_pymt_eff_date := '|| lv_curr_pymt_eff_date);
2209 hr_utility.trace('l_date_earned := '|| l_date_earned);
2210 lv_sqlstr := 'select nvl(' || lv_application_column_name ||
2211 ',''AAA'') from pay_element_entries_f where element_entry_id = ' || ln_element_entry_id
2212 ||' AND '
2213 ||' TO_DATE('''
2214 --bug no 6950970 starts here
2215 -- || lv_curr_pymt_eff_date
2216 || l_date_earned
2217 --bug no 6950970 ends here
2218 ||''', ''DD-MON-YYYY'') '
2219 ||' BETWEEN effective_start_date AND effective_end_date ';
2220
2221 hr_utility.trace('Query := '|| lv_sqlstr);
2222 --bug no 6950970 starts here
2223 begin
2224 --bug no 6950970 ends here
2225 EXECUTE IMMEDIATE lv_sqlstr INTO lv_original_date_paid ;
2226 lv_original_date_earned := lv_original_date_paid ;
2227
2228 hr_utility.trace('lv_original_date_earned := '||lv_original_date_earned);
2229
2230 -- Possibility of Malformed SQL (Added Spaces between)
2231
2232 lv_sqlstr1 := 'select count(peef.' || lv_application_column_name
2233 ||') FROM pay_element_entries_f peef, pay_assignment_actions paa, pay_payroll_actions ppa,per_time_periods ptp WHERE paa.assignment_action_id = '
2234 || ln_run_assignment_action_id
2235 || ' AND ppa.payroll_action_id = paa.payroll_action_id AND ptp.payroll_id = ppa.payroll_id AND ppa.date_earned BETWEEN ptp.start_date AND ptp.end_date AND peef.assignment_id = '
2236 || p_assignment_id
2237 ||' AND peef.element_type_id = '
2238 || ln_element_type_id
2239 || ' AND NVL(ppa.date_earned, ppa.effective_date) BETWEEN peef.effective_start_date AND peef.effective_end_date AND peef.'
2240 || lv_application_column_name
2241 || ' is not null ' ;
2242
2243 EXECUTE IMMEDIATE lv_sqlstr1 into ln_addnl_ele_num;
2244 hr_utility.trace('ln_addnl_ele_num' || ln_addnl_ele_num);
2245 hr_utility.trace('p_curr_pymt_eff_date '|| p_curr_pymt_eff_date);
2246 --bug no 6950970 starts here
2247 exception
2248 when no_data_found then
2249 ln_addnl_ele_num:=0;
2250 end;
2251 --bug no 6950970 ends here
2252 END IF; -- Addl Ele DFF Info Configured
2253 -- Code to be Revised Again
2254
2258
2255 -- IF there is Element Entry for which Addl Ele Entry DFF Configured
2256 -- AND Originating Date Earned Field Populated
2257 --
2259
2260 IF (( lv_original_date_paid <> 'AAA' and ln_flag =0) OR (ln_addnl_ele_num > 0))THEN
2261
2262 hr_utility.trace('(( lv_original_date_paid <> AAA and ln_flag =0) OR (ln_addnl_ele_num > 0)');
2263
2264 IF ln_addnl_ele_num > 0 THEN
2265 hr_utility.trace('ln_addnl_ele_num > 0');
2266 Archive_addnl_elements(
2267 p_application_column_name => lv_application_column_name
2268 ,p_xfr_action_id => p_xfr_action_id
2269 ,p_assignment_id => p_assignment_id
2270 ,p_pymt_assignment_action_id => p_curr_pymt_action_id
2271 ,p_pymt_eff_date => p_curr_pymt_eff_date
2272 ,p_element_type_id => ln_element_type_id
2273 ,p_primary_balance_id => ln_primary_balance_id
2274 ,p_hours_balance_id => ln_hours_balance_id
2275 ,p_processing_priority => ln_processing_priority
2276 ,p_element_classification_name => lv_element_classification_name
2277 ,p_reporting_name => lv_reporting_name
2278 ,p_tax_unit_id => p_tax_unit_id
2279 ,p_ytd_balcall_aaid => p_ytd_balcall_aaid
2280 ,p_pymt_balcall_aaid => p_pymt_balcall_aaid
2281 ,p_legislation_code => p_legislation_code
2282 ,p_sepchk_flag => p_sepchk_flag
2283 ,p_sepchk_run_type_id => p_sepchk_run_type_id
2284 ,p_action_type => p_action_type
2285 ,p_run_assignment_action_id => ln_run_assignment_action_id
2286 ,p_multiple => ln_multiple
2287 ,p_rate => ln_final_rate);
2288 lv_retro_flag := 'Y' ;
2289 -- As Base + Addl Ele DFF Config Non-Retro Entry Both Handled above
2290 lv_retro_parent_flag := 'N';
2291 ELSE -- May Need to be Revised
2292
2293 hr_utility.trace('ln_addnl_ele_num <= 0');
2294 open archive_non_retro_elements( ld_original_date_paid,
2295 ln_element_entry_id,
2296 ln_run_assignment_action_id ) ;
2297 fetch archive_non_retro_elements
2298 into lv_effective_start_date,
2299 lv_effective_end_date,
2300 lv_category;
2301 close archive_non_retro_elements ;
2302 close get_run_action_id;
2303
2304 -- Added For Work At Home Condition
2305 OPEN c_cur_get_wrkathome(p_assignment_id);
2306 FETCH c_cur_get_wrkathome INTO lv_wrk_at_home
2307 ,ln_person_id
2308 ,ln_bg_id;
2309 CLOSE c_cur_get_wrkathome;
2310
2311 IF lv_wrk_at_home = 'Y' THEN
2312 OPEN c_cur_home_state_jd(ln_person_id
2313 ,ln_bg_id);
2314 FETCH c_cur_home_state_jd INTO lv_jurisdiction_flag;
2315 CLOSE c_cur_home_state_jd;
2316 ELSE
2317 select nvl((select peevf.screen_entry_value jurisdiction_code
2318 from pay_input_values_f pivf,
2319 pay_element_entry_values_f peevf
2320 where pivf.element_type_id = ln_element_type_id
2321 AND pivf.NAME = 'Jurisdiction'
2322 AND peevf.element_entry_id = ln_element_entry_id
2323 AND pivf.input_value_id = peevf.input_value_id),(SELECT distinct pus.state_code
2324 || '-'
2325 || puc.county_code
2326 || '-'
2327 || punc.city_code jurisdiction_code
2328 FROM per_all_assignments_f peaf,
2329 hr_locations_all hla,
2330 pay_us_states pus,
2331 pay_us_counties puc,
2332 pay_us_city_names punc,
2333 pay_assignment_actions paa,
2334 pay_payroll_actions ppa
2335 WHERE peaf.assignment_id = p_assignment_id
2336 AND paa.assignment_action_id = ln_run_assignment_action_id
2337 AND peaf.location_id = hla.location_id
2338 AND hla.region_2 = pus.state_abbrev
2339 AND pus.state_code = puc.state_code
2340 AND hla.region_1 = puc.county_name
2344 AND ppa.payroll_action_id = paa.payroll_action_id
2341 AND hla.town_or_city = punc.city_name
2342 AND pus.state_code = punc.state_code
2343 AND puc.county_code = punc.county_code
2345 AND ppa.effective_date between peaf.effective_start_date and peaf.effective_end_date
2346 ))
2347 into lv_jurisdiction_flag
2348 from dual;
2349 END IF; -- Work At Home 'N'
2350
2351 END IF; -- ln_addnl_ele_num > 0
2352 EXIT;
2353 END IF; -- lv_original_date_paid <> 'AAA' and ln_flag =0
2354
2355 hr_utility.trace('Before Checking Retro.');
2356
2357 -- Start Handling Retro Cases
2358 -- Checking IF Retro
2359
2360 OPEN check_retro ( ln_run_assignment_action_id,
2361 p_assignment_id,
2362 ln_element_type_id) ;
2363 FETCH check_retro into lv_retro_flag ;
2364 IF check_retro%FOUND THEN
2365 --
2366 -- In Case of Retro Checking IF it is Base + Retro Case
2367 --
2368 OPEN retro_parent_check_flag(ln_run_assignment_action_id,
2369 p_assignment_id,
2370 ln_element_type_id) ;
2371 FETCH retro_parent_check_flag INTO lv_retro_parent_flag;
2372 CLOSE retro_parent_check_flag ;
2373 IF lv_retro_parent_flag IS NULL THEN
2374 lv_retro_parent_flag := 'N';
2375 END IF;
2376 --
2377 -- Archiving ONLY Retro Entries NOT Retrp Base
2378 --
2379 Archive_retro_element( p_xfr_action_id => p_xfr_action_id
2380 ,p_assignment_id => p_assignment_id
2381 ,p_pymt_assignment_action_id => p_curr_pymt_action_id
2382 ,p_pymt_eff_date => p_curr_pymt_eff_date
2383 ,p_element_type_id => ln_element_type_id
2384 ,p_primary_balance_id => ln_primary_balance_id
2385 ,p_hours_balance_id => ln_hours_balance_id
2386 ,p_processing_priority => ln_processing_priority
2387 ,p_element_classification_name => lv_element_classification_name
2388 ,p_reporting_name => lv_reporting_name
2389 ,p_tax_unit_id => p_tax_unit_id
2390 ,p_ytd_balcall_aaid => p_ytd_balcall_aaid
2391 ,p_pymt_balcall_aaid => p_pymt_balcall_aaid
2392 ,p_legislation_code => p_legislation_code
2393 ,p_sepchk_flag => p_sepchk_flag
2394 ,p_sepchk_run_type_id => p_sepchk_run_type_id
2395 ,p_action_type => p_action_type
2396 ,p_run_assignment_action_id => ln_run_assignment_action_id
2397 ,p_multiple => ln_multiple
2398 ,p_rate => ln_final_rate
2399 ,p_retro_base => lv_retro_parent_flag);
2400 END IF; -- check_retro%FOUND
2401
2402 CLOSE check_retro; -- Added
2403 ELSE
2404 IF check_retro%ISOPEN THEN
2405 CLOSE check_retro;
2406 END IF;
2407 IF get_element_entry_id%ISOPEN THEN
2408 CLOSE get_element_entry_id;
2409 END IF;
2410 END IF; -- get_element_entry_id%found
2411
2412 END LOOP; -- For Each Run Assignment Action ID
2413
2414 IF get_run_action_id%ISOPEN THEN
2415 CLOSE get_run_action_id;
2416 END IF;
2417 END IF; -- Legislation US
2418
2419 hr_utility.set_location(gv_package || lv_procedure_name, 40);
2420 hr_utility.trace('Ele type id = ' || ln_element_type_id);
2421 hr_utility.trace('Primary Bal id = '|| ln_primary_balance_id);
2422 hr_utility.trace('Ele Class = ' || lv_element_classification_name);
2423
2424 if lv_element_classification_name like '% Deductions' then
2425 ln_hours_balance_id := null;
2426 end if;
2427 ln_step := 15;
2428 ln_element_index := pay_ac_action_arch.emp_elements_tab.count;
2429 pay_ac_action_arch.emp_elements_tab(ln_element_index).element_type_id
2430 := ln_element_type_id;
2431 pay_ac_action_arch.emp_elements_tab(ln_element_index).element_classfn
2432 := lv_element_classification_name;
2433 pay_ac_action_arch.emp_elements_tab(ln_element_index).element_primary_balance_id
2434 := ln_primary_balance_id;
2435 pay_ac_action_arch.emp_elements_tab(ln_element_index).element_processing_priority
2436 := ln_processing_priority;
2437 pay_ac_action_arch.emp_elements_tab(ln_element_index).element_reporting_name
2441 pay_ac_action_arch.emp_elements_tab(ln_element_index).jurisdiction_code
2438 := lv_reporting_name;
2439 pay_ac_action_arch.emp_elements_tab(ln_element_index).element_hours_balance_id
2440 := ln_hours_balance_id;
2442 := '00-000-0000';
2443
2444 hr_utility.set_location(gv_package || lv_procedure_name, 50);
2445 ln_step := 20;
2446 IF ((lv_retro_flag = 'N' ) OR (lv_retro_parent_flag = 'Y')) THEN
2447 IF lv_original_date_earned = 'AAA' THEN
2448 lv_original_date_earned := null;
2449 END IF;
2450 populate_elements(p_xfr_action_id => p_xfr_action_id
2451 ,p_pymt_assignment_action_id => p_curr_pymt_action_id
2452 ,p_pymt_eff_date => p_curr_pymt_eff_date
2453 ,p_element_type_id => ln_element_type_id
2454 ,p_primary_balance_id => ln_primary_balance_id
2455 ,p_hours_balance_id => ln_hours_balance_id
2456 ,p_processing_priority => ln_processing_priority
2457 ,p_element_classification_name
2458 => lv_element_classification_name
2459 ,p_reporting_name => lv_reporting_name
2460 ,p_tax_unit_id => p_tax_unit_id
2461 ,p_pymt_balcall_aaid => p_pymt_balcall_aaid
2462 ,p_ytd_balcall_aaid => p_ytd_balcall_aaid
2463 ,p_legislation_code => p_legislation_code
2464 ,p_sepchk_flag => p_sepchk_flag
2465 ,p_sepchk_run_type_id => p_sepchk_run_type_id
2466 ,p_action_type => p_action_type
2467 ,p_original_date_earned => lv_original_date_earned
2468 ,p_effective_start_date => lv_effective_start_date
2469 ,p_effective_end_date => lv_effective_end_date
2470 ,p_category => lv_category
2471 ,p_el_jurisdiction_code => lv_jurisdiction_flag
2472 ,p_final_rate => ln_final_rate
2473 ,p_ytd_flag => lv_retro_parent_flag
2474 );
2475
2476 lv_original_date_earned := NULL;
2477 lv_effective_start_date := NULL;
2478 lv_effective_end_date := NULL;
2479 lv_category := NULL;
2480 lv_jurisdiction_flag := NULL;
2481 lv_original_date_paid:= NULL;
2482
2483 END IF;
2484
2485 lv_retro_flag := 'N' ;
2486 lv_retro_parent_flag := 'N';
2487 gv_ytd_amount := 0;
2488 gv_ytd_hour := 0;
2489
2490 end loop; -- End Loop of c_cur_action_elements OR c_cur_sp_action_elements
2491
2492 if p_sepchk_flag = 'Y' then
2493 close c_cur_sp_action_elements;
2494 elsif p_sepchk_flag = 'N' then
2495 close c_cur_action_elements;
2496 end if;
2497 hr_utility.set_location(gv_package || lv_procedure_name, 60);
2498 ln_step := 25;
2499
2500 EXCEPTION
2501 when others then
2502 lv_error_message := 'Error at step ' || ln_step ||
2503 ' in ' || gv_package || lv_procedure_name;
2504
2505 hr_utility.trace(lv_error_message || '-' || sqlerrm);
2506
2507 lv_error_message :=
2508 pay_emp_action_arch.set_error_message(lv_error_message);
2509
2510 hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
2511 hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
2512 hr_utility.raise_error;
2513
2514 END get_current_elements;
2515
2516 /******************************************************************
2517 Name : get_xfr_elements
2518 Purpose : Check the elements archived in the previous record with
2519 the given assignment and if the element is not archived
2520 in this current run, get YTD for the element found.
2521 Arguments : p_xfr_action_id => Current xfr action id
2522 p_last_xfr_action_id => Previous xfr action id retrieved
2523 from get_last_xfr_info procedure
2524 p_ytd_balcall_aaid => aaid for YTD balance call.
2525 p_pymt_eff_date => Current pymt eff date.
2526 p_legislation_code => Legislation code.
2527 p_sepchk_flag => Separate Check flag.
2528 p_assignment_id => Current assignment id that is being
2529 processed.
2530 Notes : If multi assignment is enabled and is a sepchk, then check
2531 the last xfr run for the given person not assignment.
2532 ******************************************************************/
2533 PROCEDURE get_xfr_elements(p_xfr_action_id in number
2534 ,p_last_xfr_action_id in number
2535 ,p_ytd_balcall_aaid in number
2536 ,p_pymt_eff_date in date
2537 ,p_legislation_code in varchar2
2538 ,p_sepchk_flag in varchar2
2539 ,p_assignment_id in number
2540 )
2541
2542 IS
2546 action_information1 classification_name,
2543 cursor c_last_xfr_elements(cp_xfr_action_id in number
2544 ,cp_legislation_code in varchar2) is
2545 select assignment_id, action_information_category,
2547 action_information2 element_type_id,
2548 decode(cp_legislation_code,
2549 'CA', jurisdiction_code,
2550 'US', decode(jurisdiction_code, NULL, NULL,
2551 decode(to_char(length(replace(jurisdiction_code,'-')))
2552 ,'7', jurisdiction_code,
2553 rpad(nvl(substr(rtrim(ltrim(jurisdiction_code))
2554 ,1,2),'0'),2,'0') || '-'||
2555 rpad(nvl(substr(rtrim(ltrim(jurisdiction_code))
2556 ,4,3),'0'),3,'0') ||'-' ||
2557 rpad(nvl(substr(rtrim(ltrim(jurisdiction_code))
2558 ,8,4),'0'),4,'0')))) jurisdiction_code,
2559 action_information6 primary_balance_id,
2560 action_information7 processing_priority,
2561 action_information9 ytd_amount,
2562 action_information10 reporting_name,
2563 effective_date effective_date,
2564 action_information12 ytd_hours
2565 from pay_action_information
2566 where action_information_category in ('AC EARNINGS', 'AC DEDUCTIONS')
2567 and action_context_id = cp_xfr_action_id;
2568
2569
2570 cursor c_get_balance (cp_balance_name in varchar2
2571 ,cp_legislation_code in varchar2) is
2572 select balance_type_id
2573 from pay_balance_types
2574 where legislation_code = cp_legislation_code
2575 and balance_name = cp_balance_name;
2576
2577 ln_element_type_id NUMBER;
2578 lv_element_classfication_name VARCHAR2(80);
2579 lv_jurisdiction_code VARCHAR2(80);
2580 ln_primary_balance_id NUMBER;
2581 ln_processing_priority NUMBER;
2582 lv_reporting_name VARCHAR2(150);
2583 ld_effective_date DATE;
2584 ln_hours_balance_id NUMBER;
2585
2586 ln_t_primary_balance_id NUMBER;
2587 lv_t_reporting_name VARCHAR2(150);
2588
2589 ln_ele_primary_balance_id NUMBER;
2590 ln_ele_hours_balance_id NUMBER;
2591
2592 ln_ytd_defined_balance_id NUMBER;
2593 ln_ytd_hours_balance_id NUMBER;
2594 ln_payments_amount NUMBER;
2595 ln_ytd_hours NUMBER;
2596 ln_ytd_amount NUMBER;
2597
2598 ln_index NUMBER := 0;
2599 lv_element_archived VARCHAR2(1) := 'N';
2600 lv_action_info_category VARCHAR2(30) := 'AC DEDUCTIONS';
2601 lv_procedure_name VARCHAR2(100) := '.get_xfr_elements';
2602 lv_error_message VARCHAR2(200);
2603 ln_step NUMBER;
2604 ln_assignment_id NUMBER;
2605
2606 BEGIN
2607 ln_step:= 1;
2608 hr_utility.set_location(gv_package || lv_procedure_name, 10);
2609 hr_utility.trace('p_xfr_action_id = '||p_xfr_action_id);
2610 hr_utility.trace('p_last_xfr_action_id = '|| p_last_xfr_action_id );
2611 hr_utility.trace('p_assignment_id = '|| p_assignment_id );
2612 hr_utility.trace('gv_multi_payroll_pymt = '||
2613 pay_emp_action_arch.gv_multi_payroll_pymt);
2614 hr_utility.trace('p_sepchk_flag = '||p_sepchk_flag);
2615
2616 open c_last_xfr_elements(p_last_xfr_action_id, p_legislation_code);
2617 loop
2618 fetch c_last_xfr_elements into ln_assignment_id,
2619 lv_action_info_category,
2620 lv_element_classfication_name,
2621 ln_element_type_id,
2622 lv_jurisdiction_code,
2623 ln_primary_balance_id,
2624 ln_processing_priority,
2625 ln_ytd_amount,
2626 lv_reporting_name,
2627 ld_effective_date,
2628 ln_ytd_hours;
2629
2630 hr_utility.set_location(gv_package || lv_procedure_name, 20);
2631 if c_last_xfr_elements%notfound then
2632 hr_utility.set_location(gv_package || lv_procedure_name, 30);
2633 exit;
2634 end if;
2635
2636 ln_step := 5;
2637 if ln_primary_balance_id is null then
2638 if lv_reporting_name = 'SDI Withheld' then
2639 lv_t_reporting_name := 'SDI EE Withheld';
2640 elsif lv_reporting_name = 'SUI Withheld' then
2641 lv_t_reporting_name := 'SUI EE Withheld';
2642 else
2643 lv_t_reporting_name := lv_reporting_name;
2644 end if;
2645
2646 open c_get_balance(lv_t_reporting_name, p_legislation_code);
2647 fetch c_get_balance into ln_t_primary_balance_id;
2648 close c_get_balance;
2649 ln_primary_balance_id := ln_t_primary_balance_id;
2650 end if;
2651
2652 hr_utility.trace('Element type id =' || ln_element_type_id);
2653 hr_utility.trace('Reporting Name =' || lv_reporting_name);
2654 hr_utility.trace('JD Code =' || lv_jurisdiction_code);
2655 hr_utility.trace('Ele Class =' || lv_element_classfication_name);
2656
2657 ln_step := 6;
2658
2662 if pay_emp_action_arch.gv_multi_payroll_pymt = 'Y' and
2659 hr_utility.trace('p_assignment_id (current) = '||p_assignment_id);
2660 hr_utility.trace('ln_assignment_id (prev) = '||ln_assignment_id);
2661
2663 p_sepchk_flag = 'Y' and
2664 ln_assignment_id <> p_assignment_id then
2665
2666 hr_utility.trace('action_info_category = ' ||lv_action_info_category);
2667 hr_utility.trace('ln_element_type_id = ' ||ln_element_type_id);
2668 hr_utility.trace('ln_primary_balance_id = '||ln_primary_balance_id);
2669 hr_utility.trace('ln_ytd_amount = ' ||ln_ytd_amount);
2670
2671 ln_index := pay_ac_action_arch.lrr_act_tab.count;
2672
2673 pay_ac_action_arch.lrr_act_tab(ln_index).action_info_category
2674 := lv_action_info_category;
2675 pay_ac_action_arch.lrr_act_tab(ln_index).jurisdiction_code
2676 := lv_jurisdiction_code;
2677 pay_ac_action_arch.lrr_act_tab(ln_index).action_context_id
2678 := p_xfr_action_id;
2679 pay_ac_action_arch.lrr_act_tab(ln_index).assignment_id
2680 := ln_assignment_id;
2681 pay_ac_action_arch.lrr_act_tab(ln_index).act_info1
2682 := lv_element_classfication_name;
2683 pay_ac_action_arch.lrr_act_tab(ln_index).act_info2
2684 := ln_element_type_id;
2685 pay_ac_action_arch.lrr_act_tab(ln_index).act_info6
2686 := ln_primary_balance_id;
2687 pay_ac_action_arch.lrr_act_tab(ln_index).act_info7
2688 := ln_processing_priority;
2689 pay_ac_action_arch.lrr_act_tab(ln_index).act_info9
2690 := fnd_number.number_to_canonical(nvl(ln_ytd_amount,0));
2691 pay_ac_action_arch.lrr_act_tab(ln_index).act_info10
2692 := lv_reporting_name;
2693 if lv_action_info_category = 'AC EARNINGS' then
2694 pay_ac_action_arch.lrr_act_tab(ln_index).act_info12
2695 := fnd_number.number_to_canonical(ln_ytd_hours);
2696 end if;
2697
2698 -- Added for Bug# 7348767, Bug# 7348838
2699 if lv_action_info_category = 'AC DEDUCTIONS' THEN
2700 pay_ac_action_arch.lrr_act_tab(ln_index).act_info24 := lv_reporting_name;
2701 end if;
2702 end if;
2703
2704 if ln_assignment_id = p_assignment_id then
2705 if pay_ac_action_arch.emp_elements_tab.count > 0 then
2706 for i in pay_ac_action_arch.emp_elements_tab.first..
2707 pay_ac_action_arch.emp_elements_tab.last LOOP
2708 if pay_ac_action_arch.emp_elements_tab(i).element_primary_balance_id
2709 = ln_primary_balance_id and
2710 pay_ac_action_arch.emp_elements_tab(i).jurisdiction_code
2711 = lv_jurisdiction_code then
2712 lv_element_archived := 'Y';
2713 exit;
2714 end if;
2715 end loop;
2716 end if;
2717
2718 ln_step := 10;
2719 if lv_element_archived = 'N' then
2720 hr_utility.set_location(gv_package || lv_procedure_name, 50);
2721 /**************************************************************
2722 ** Bug 3567107: Check to see if the element is still effective
2723 ** the primary balance is there before archiving
2724 ** the value when picking elements which have
2725 ** already been archived.
2726 ** Note: This will take care of the issue when clients migrate
2727 ** to a new element and only want one entry to be archived
2728 ** and show up in checks, payslip and depsoit advice
2729 **************************************************************/
2730 if lv_element_classfication_name <> 'Tax Deductions' then
2731 open c_element_info(ln_element_type_id, ld_effective_date);
2732 fetch c_element_info into ln_ele_primary_balance_id,
2733 ln_ele_hours_balance_id;
2734 if c_element_info%notfound or
2735 ln_ele_primary_balance_id is null then
2736 lv_element_archived := 'Y';
2737 end if;
2738
2739 close c_element_info;
2740
2741 if lv_element_classfication_name not like '% Deductions' then
2742 ln_hours_balance_id := ln_ele_hours_balance_id;
2743 end if;
2744 end if;
2745 end if;
2746
2747 if lv_element_archived = 'N' then
2748 /* populate the extra element table */
2749 ln_index := pay_ac_action_arch.emp_elements_tab.count;
2750 pay_ac_action_arch.emp_elements_tab(ln_index).element_type_id
2751 := ln_element_type_id;
2752 pay_ac_action_arch.emp_elements_tab(ln_index).element_classfn
2753 := lv_element_classfication_name;
2754 pay_ac_action_arch.emp_elements_tab(ln_index).jurisdiction_code
2755 := lv_jurisdiction_code;
2756 pay_ac_action_arch.emp_elements_tab(ln_index).element_primary_balance_id
2757 := ln_primary_balance_id;
2758 pay_ac_action_arch.emp_elements_tab(ln_index).element_processing_priority
2759 := ln_processing_priority;
2760 pay_ac_action_arch.emp_elements_tab(ln_index).element_reporting_name
2764
2761 := lv_reporting_name;
2762 pay_ac_action_arch.emp_elements_tab(ln_index).element_hours_balance_id
2763 := ln_hours_balance_id;
2765 if lv_jurisdiction_code <> '00-000-0000' then
2766 pay_balance_pkg.set_context('JURISDICTION_CODE', lv_jurisdiction_code);
2767 gv_ytd_balance_dimension := gv_dim_asg_jd_gre_ytd;
2768 else
2769 pay_balance_pkg.set_context('JURISDICTION_CODE', lv_jurisdiction_code);
2770 if gv_reporting_level = 'TAXGRP' then
2771 gv_ytd_balance_dimension := gv_dim_asg_tg_ytd;
2772 else
2773 gv_ytd_balance_dimension := gv_dim_asg_gre_ytd;
2774 end if;
2775 end if;
2776
2777 ln_step := 15;
2778 ln_ytd_defined_balance_id
2779 := pay_emp_action_arch.get_defined_balance_id
2780 (ln_primary_balance_id,
2781 gv_ytd_balance_dimension,
2782 p_legislation_code);
2783 hr_utility.set_location(gv_package || lv_procedure_name, 60);
2784 if ln_ytd_defined_balance_id is not null then
2785 ln_ytd_amount := nvl(pay_balance_pkg.get_value(
2786 ln_ytd_defined_balance_id,
2787 p_ytd_balcall_aaid),0);
2788 end if;
2789 hr_utility.set_location(gv_package || lv_procedure_name, 70);
2790 if ln_hours_balance_id is not null then
2791 ln_ytd_hours_balance_id
2792 := pay_emp_action_arch.get_defined_balance_id
2793 (ln_hours_balance_id,
2794 gv_ytd_balance_dimension,
2795 p_legislation_code);
2796 hr_utility.set_location(gv_package || lv_procedure_name, 80);
2797 if ln_ytd_hours_balance_id is not null then
2798 ln_ytd_hours := nvl(pay_balance_pkg.get_value(
2799 ln_ytd_hours_balance_id,
2800 p_ytd_balcall_aaid),0);
2801 hr_utility.set_location(gv_package || lv_procedure_name, 90);
2802 end if;
2803 end if;
2804
2805 hr_utility.trace('ln_ytd_amount = '||ln_ytd_amount);
2806 hr_utility.trace('ln_ytd_hours = '||ln_ytd_hours);
2807
2808 if (( nvl(ln_ytd_amount, 0) + nvl(ln_payments_amount, 0) <> 0 ) or
2809 ( pay_ac_action_arch.gv_multi_gre_payment = 'N' ) ) then
2810
2811 hr_utility.set_location(gv_package || lv_procedure_name, 100);
2812 ln_index := pay_ac_action_arch.lrr_act_tab.count;
2813 hr_utility.trace('ln_index = ' || ln_index);
2814 ln_step := 20;
2815 if lv_element_classfication_name in ('Earnings',
2816 'Alien/Expat Earnings',
2817 'Supplemental Earnings',
2818 'Taxable Benefits',
2819 'Imputed Earnings',
2820 'Non-payroll Payments') then
2821 lv_action_info_category := 'AC EARNINGS';
2822 pay_ac_action_arch.lrr_act_tab(ln_index).act_info12
2823 := fnd_number.number_to_canonical(ln_ytd_hours);
2824 end if;
2825
2826 pay_ac_action_arch.lrr_act_tab(ln_index).action_info_category
2827 := lv_action_info_category;
2828 pay_ac_action_arch.lrr_act_tab(ln_index).jurisdiction_code
2829 := lv_jurisdiction_code;
2830 pay_ac_action_arch.lrr_act_tab(ln_index).action_context_id
2831 := p_xfr_action_id;
2832 pay_ac_action_arch.lrr_act_tab(ln_index).act_info1
2833 := lv_element_classfication_name;
2834 pay_ac_action_arch.lrr_act_tab(ln_index).act_info2
2835 := ln_element_type_id;
2836 pay_ac_action_arch.lrr_act_tab(ln_index).act_info6
2837 := ln_primary_balance_id;
2838 pay_ac_action_arch.lrr_act_tab(ln_index).act_info7
2839 := ln_processing_priority;
2840 pay_ac_action_arch.lrr_act_tab(ln_index).act_info8
2841 := fnd_number.number_to_canonical(nvl(ln_payments_amount,0));
2842 pay_ac_action_arch.lrr_act_tab(ln_index).act_info9
2843 := fnd_number.number_to_canonical(ln_ytd_amount);
2844 pay_ac_action_arch.lrr_act_tab(ln_index).act_info10
2845 := lv_reporting_name;
2846 -- Added for Bug# 7348767, Bug# 7348838
2847 if lv_action_info_category = 'AC DEDUCTIONS' THEN
2848 pay_ac_action_arch.lrr_act_tab(ln_index).act_info24 := lv_reporting_name;
2849 end if;
2850
2851 end if;
2852 end if;
2853
2854 lv_element_archived := 'N';
2855 lv_action_info_category := 'AC DEDUCTIONS';
2856 lv_element_classfication_name := null;
2857 ln_element_type_id := null;
2861 lv_reporting_name := null;
2858 lv_jurisdiction_code := null;
2859 ln_primary_balance_id := null;
2860 ln_processing_priority := null;
2862 ln_hours_balance_id := null;
2863 ln_ytd_amount := null;
2864 ln_ytd_hours := null;
2865
2866 end if;
2867 end loop;
2868 close c_last_xfr_elements;
2869
2870 hr_utility.set_location(gv_package || lv_procedure_name, 50);
2871 ln_step := 25;
2872
2873
2874
2875 EXCEPTION
2876 when others then
2877 lv_error_message := 'Error at step ' || ln_step ||
2878 ' in ' || gv_package || lv_procedure_name;
2879
2880 hr_utility.trace(lv_error_message || '-' || sqlerrm);
2881
2882 lv_error_message :=
2883 pay_emp_action_arch.set_error_message(lv_error_message);
2884
2885 hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
2886 hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
2887 hr_utility.raise_error;
2888
2889 END get_xfr_elements;
2890
2891
2892
2893 /******************************************************************
2894 Name : get_missing_xfr_info
2895 Purpose : The procedure gets the elements which have been
2896 processed for a given Payment Action. This procedure
2897 is only called if the archiver has not been run for
2898 all pre-payment actions.
2899 Arguments :
2900 Notes :
2901 ******************************************************************/
2902 PROCEDURE get_missing_xfr_info(p_xfr_action_id in number
2903 ,p_tax_unit_id in number
2904 ,p_assignment_id in number
2905 ,p_last_pymt_action_id in number
2906 ,p_last_pymt_eff_date in date
2907 ,p_last_xfr_eff_date in date
2908 ,p_ytd_balcall_aaid in number
2909 ,p_pymt_eff_date in date
2910 ,p_legislation_code in varchar2
2911 )
2912
2913 IS
2914
2915 cursor c_prev_elements(cp_assignment_id in number
2916 ,cp_pymt_eff_date in date
2917 ,cp_last_xfr_eff_date in date) is
2918 SELECT /*+ ORDERED use_nl(PAA,PPA,PPF) */
2919 DISTINCT
2920 pec.classification_name,
2921 pet.processing_priority,
2922 decode(pec.classification_name,
2923 'Tax Deductions',
2924 nvl(petl.reporting_name, petl.element_name) || ' Withheld',
2925 nvl(petl.reporting_name, petl.element_name)) reporting_name,
2926 decode(pec.classification_name,
2927 'Tax Deductions', null,
2928 prr.element_type_id) element_type_id,
2929 nvl(decode(pec.classification_name,
2930 'Tax Deductions', prr.jurisdiction_code), '00-000-0000'),
2931 pet.element_information10,
2932 pet.element_information12
2933 from PAY_ASSIGNMENT_ACTIONS PAA,
2934 PAY_PAYROLL_ACTIONS PPA,
2935 PAY_PAYROLLS_F PPF,
2936 PAY_RUN_RESULTS PRR,
2937 PAY_ELEMENT_TYPES_F PET ,
2938 PAY_ELEMENT_CLASSIFICATIONS PEC,
2939 PAY_ELEMENT_TYPES_F_TL PETL
2940 /*changing the order for bug 5549032
2941 pay_run_results prr,
2942 pay_element_types_f pet ,
2943 pay_element_classifications pec,
2944 pay_assignment_actions paa,
2945 pay_payroll_actions ppa,
2946 pay_element_types_f_tl petl,
2947 pay_all_payrolls_f ppf */ -- Bug 3370112
2948 where ppa.action_type in ('R', 'Q', 'B')
2949 and ppa.effective_date > cp_last_xfr_eff_date
2950 and ppa.effective_date <= cp_pymt_eff_date
2951 and ppa.payroll_id = ppf.payroll_id
2952 and ppf.payroll_id >= 0
2953 and ppa.effective_date between ppf.effective_start_date
2954 and ppf.effective_end_date
2955 and paa.payroll_action_id = ppa.payroll_action_id
2956 and paa.assignment_id = cp_assignment_id
2957 and paa.assignment_action_id = prr.assignment_action_id
2958 and pet.element_type_id = prr.element_type_id
2959 and pet.element_information10 is not null
2960 and ppa.effective_date between pet.effective_start_date
2961 and pet.effective_end_date
2962 and petl.element_type_id = pet.element_type_id
2963 and petl.language = gv_person_lang
2964 and pec.classification_id = pet.classification_id
2965 and pec.classification_name in ('Earnings',
2966 'Alien/Expat Earnings',
2967 'Supplemental Earnings',
2971 'Involuntary Deductions',
2968 'Imputed Earnings',
2969 'Taxable Benefits',
2970 'Pre-Tax Deductions',
2972 'Voluntary Deductions',
2973 'Non-payroll Payments',
2974 'Tax Deductions'
2975 )
2976 and pet.element_name not like '%Calculator'
2977 and pet.element_name not like '%Special Inputs'
2978 and pet.element_name not like '%Special Features'
2979 and pet.element_name not like '%Special Features 2'
2980 and pet.element_name not like '%Verifier'
2981 and pet.element_name not like '%Priority'
2982 order by 1, 3, 4;
2983
2984 lv_element_classfication_name VARCHAR2(80);
2985 ln_primary_balance_id NUMBER;
2986 ln_processing_priority NUMBER;
2987 lv_reporting_name VARCHAR2(80);
2988 ln_element_type_id NUMBER;
2989 lv_jurisdiction_code VARCHAR2(80);
2990 ln_hours_balance_id NUMBER;
2991
2992 ln_ytd_hours_balance_id NUMBER;
2993 ln_ytd_defined_balance_id NUMBER;
2994 ln_payments_amount NUMBER;
2995 ln_ytd_hours NUMBER;
2996 ln_ytd_amount NUMBER(17,2);
2997 lv_action_info_category VARCHAR2(30) := 'AC DEDUCTIONS';
2998
2999 ln_index NUMBER ;
3000 lv_element_archived VARCHAR2(1) := 'N';
3001 lv_procedure_name VARCHAR2(100) := '.get_missing_xfr_info';
3002 lv_error_message VARCHAR2(200);
3003 ln_step NUMBER;
3004
3005 BEGIN
3006 ln_step := 1;
3007 hr_utility.set_location(gv_package || lv_procedure_name, 10);
3008 hr_utility.trace('p_xfr_action_id = '|| p_xfr_action_id);
3009 hr_utility.trace('p_tax_unit_id = '|| p_tax_unit_id);
3010 hr_utility.trace('p_last_pymt_action_id = '|| p_last_pymt_action_id );
3011 hr_utility.trace('p_last_pymt_eff_date = '|| p_last_pymt_eff_date);
3012 hr_utility.trace('p_last_xfr_eff_date = '|| p_last_xfr_eff_date);
3013 hr_utility.trace('p_pymt_eff_date = '|| p_pymt_eff_date);
3014
3015 open c_prev_elements(p_assignment_id,
3016 p_pymt_eff_date,
3017 p_last_xfr_eff_date);
3018 loop
3019 fetch c_prev_elements into lv_element_classfication_name,
3020 ln_processing_priority,
3021 lv_reporting_name,
3022 ln_element_type_id,
3023 lv_jurisdiction_code,
3024 ln_primary_balance_id,
3025 ln_hours_balance_id;
3026 if c_prev_elements%notfound then
3027 hr_utility.set_location(gv_package || lv_procedure_name, 20);
3028 exit;
3029 end if;
3030 hr_utility.set_location(gv_package || lv_procedure_name, 30);
3031
3032 if lv_element_classfication_name like '% Deductions' then
3033 ln_hours_balance_id := null;
3034 end if;
3035
3036 ln_step := 5;
3037 if pay_ac_action_arch.emp_elements_tab.count > 0 then
3038 for i in pay_ac_action_arch.emp_elements_tab.first..
3039 pay_ac_action_arch.emp_elements_tab.last LOOP
3040 if pay_ac_action_arch.emp_elements_tab(i).element_primary_balance_id
3041 = ln_primary_balance_id and
3042 pay_ac_action_arch.emp_elements_tab(i).jurisdiction_code
3043 = lv_jurisdiction_code then
3044 lv_element_archived := 'Y';
3045 exit;
3046 end if;
3047 end loop;
3048 end if;
3049
3050 if lv_element_archived = 'N' then
3051 /* populate the extra element table */
3052 ln_step := 10;
3053 ln_index := pay_ac_action_arch.emp_elements_tab.count;
3054 pay_ac_action_arch.emp_elements_tab(ln_index).element_type_id
3055 := ln_element_type_id;
3056 pay_ac_action_arch.emp_elements_tab(ln_index).element_classfn
3057 := lv_element_classfication_name;
3058 pay_ac_action_arch.emp_elements_tab(ln_index).element_primary_balance_id
3059 := ln_primary_balance_id;
3060 pay_ac_action_arch.emp_elements_tab(ln_index).element_processing_priority
3061 := ln_processing_priority;
3062 pay_ac_action_arch.emp_elements_tab(ln_index).element_reporting_name
3063 := lv_reporting_name;
3064 pay_ac_action_arch.emp_elements_tab(ln_index).element_hours_balance_id
3065 := ln_hours_balance_id;
3066 pay_ac_action_arch.emp_elements_tab(ln_index).jurisdiction_code
3067 := lv_jurisdiction_code;
3068
3069 if lv_jurisdiction_code <> '00-000-0000' then
3070 pay_balance_pkg.set_context('JURISDICTION_CODE', lv_jurisdiction_code);
3071 gv_ytd_balance_dimension := gv_dim_asg_jd_gre_ytd;
3072 else
3073 pay_balance_pkg.set_context('JURISDICTION_CODE', lv_jurisdiction_code);
3077 gv_ytd_balance_dimension := gv_dim_asg_gre_ytd;
3074 if gv_reporting_level = 'TAXGRP' then
3075 gv_ytd_balance_dimension := gv_dim_asg_tg_ytd;
3076 else
3078 end if;
3079 end if;
3080
3081 ln_step := 15;
3082 ln_ytd_defined_balance_id :=
3083 pay_emp_action_arch.get_defined_balance_id
3084 (ln_primary_balance_id,
3085 gv_ytd_balance_dimension,
3086 p_legislation_code);
3087 hr_utility.set_location(gv_package || lv_procedure_name, 60);
3088 if ln_ytd_defined_balance_id is not null then
3089 ln_ytd_amount := nvl(pay_balance_pkg.get_value(
3090 ln_ytd_defined_balance_id,
3091 p_ytd_balcall_aaid),0);
3092 hr_utility.set_location(gv_package || lv_procedure_name, 70);
3093 end if;
3094 if ln_hours_balance_id is not null then
3095 ln_ytd_hours_balance_id :=
3096 pay_emp_action_arch.get_defined_balance_id
3097 (ln_hours_balance_id,
3098 gv_ytd_balance_dimension,
3099 p_legislation_code);
3100 hr_utility.set_location(gv_package || lv_procedure_name, 80);
3101 if ln_ytd_hours_balance_id is not null then
3102 ln_ytd_hours := nvl(pay_balance_pkg.get_value(
3103 ln_ytd_hours_balance_id,
3104 p_ytd_balcall_aaid),0);
3105 hr_utility.set_location(gv_package || lv_procedure_name, 90);
3106 end if;
3107 end if;
3108
3109 hr_utility.set_location(gv_package || lv_procedure_name, 100);
3110 if nvl(ln_ytd_amount, 0) <> 0 or nvl(ln_payments_amount, 0) <> 0 then
3111 ln_index := pay_ac_action_arch.lrr_act_tab.count;
3112 hr_utility.trace('ln_index = ' || ln_index);
3113 if lv_element_classfication_name in ('Earnings',
3114 'Alien/Expat Earnings',
3115 'Supplemental Earnings',
3116 'Taxable Benefits',
3117 'Imputed Earnings',
3118 'Non-payroll Payments') then
3119 lv_action_info_category := 'AC EARNINGS';
3120 pay_ac_action_arch.lrr_act_tab(ln_index).act_info12
3121 := fnd_number.number_to_canonical(ln_ytd_hours); /* Bug 3311866*/
3122 end if;
3123
3124 ln_step := 20;
3125 pay_ac_action_arch.lrr_act_tab(ln_index).action_info_category
3126 := lv_action_info_category;
3127 pay_ac_action_arch.lrr_act_tab(ln_index).jurisdiction_code
3128 := lv_jurisdiction_code;
3129 pay_ac_action_arch.lrr_act_tab(ln_index).action_context_id
3130 := p_xfr_action_id ;
3131 pay_ac_action_arch.lrr_act_tab(ln_index).act_info1
3132 := lv_element_classfication_name;
3133 pay_ac_action_arch.lrr_act_tab(ln_index).act_info2
3134 := ln_element_type_id;
3135 pay_ac_action_arch.lrr_act_tab(ln_index).act_info6
3136 := ln_primary_balance_id;
3137 pay_ac_action_arch.lrr_act_tab(ln_index).act_info7
3138 := ln_processing_priority;
3139 pay_ac_action_arch.lrr_act_tab(ln_index).act_info8
3140 := fnd_number.number_to_canonical(nvl(ln_payments_amount,0));
3141 pay_ac_action_arch.lrr_act_tab(ln_index).act_info9
3142 := fnd_number.number_to_canonical(nvl(ln_ytd_amount,0));
3143 pay_ac_action_arch.lrr_act_tab(ln_index).act_info10
3144 := lv_reporting_name;
3145 -- Added for Bug# 7348767, Bug# 7348838
3146 if lv_action_info_category = 'AC DEDUCTIONS' THEN
3147 pay_ac_action_arch.lrr_act_tab(ln_index).act_info24 := lv_reporting_name;
3148 end if;
3149 end if;
3150 end if;
3151 lv_element_archived := 'N';
3152 lv_action_info_category := 'AC DEDUCTIONS';
3153 lv_element_classfication_name := null;
3154 ln_element_type_id := null;
3155 lv_jurisdiction_code := null;
3156 ln_primary_balance_id := null;
3157 ln_processing_priority := null;
3158 lv_reporting_name := null;
3159 ln_hours_balance_id := null;
3160 end loop;
3161 close c_prev_elements;
3162 hr_utility.set_location(gv_package || lv_procedure_name, 150);
3163
3164 ln_step := 30;
3165
3166
3167 EXCEPTION
3168 when others then
3169
3170 lv_error_message := 'Error at step ' || ln_step ||
3171 ' in ' || gv_package || lv_procedure_name;
3172
3173 hr_utility.trace(lv_error_message || '-' || sqlerrm);
3174
3175 lv_error_message :=
3176 pay_emp_action_arch.set_error_message(lv_error_message);
3177
3178 hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
3179 hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
3180 hr_utility.raise_error;
3181
3185 FUNCTION check_run_balance_status(p_assignment_id in number
3182 END get_missing_xfr_info;
3183
3184
3186 ,p_curr_pymt_eff_date in date
3187 ,p_legislation_code in varchar2
3188 )
3189 RETURN VARCHAR2
3190 IS
3191
3192 lv_business_grp_id NUMBER;
3193 lv_rb_status VARCHAR2(1);
3194
3195 cursor c_business_grp_id is
3196 select distinct business_group_id
3197 from per_all_assignments_f
3198 where assignment_id = p_assignment_id;
3199
3200 BEGIN
3201
3202 -- Populating the PL/SQL table run_bal_stat_tab with the validity status
3203 -- of various attributes. If already populated, we use that to check the
3204 -- validity
3205 if run_bal_stat.COUNT > 0 then
3206 for i in run_bal_stat.first .. run_bal_stat.last loop
3207 if run_bal_stat(i).valid_status = 'N' then
3208 lv_rb_status := 'N';
3209 exit;
3210 end if;
3211 end loop;
3212 else
3213 open c_business_grp_id;
3214 fetch c_business_grp_id into lv_business_grp_id;
3215 close c_business_grp_id;
3216 if p_legislation_code = 'US' then
3217 run_bal_stat(1).attribute_name := 'PAY_US_EARNINGS_AMTS';
3218 run_bal_stat(2).attribute_name := 'PAY_US_PRE_TAX_DEDUCTIONS';
3219 run_bal_stat(3).attribute_name := 'PAY_US_AFTER_TAX_DEDUCTIONS';
3220 run_bal_stat(4).attribute_name := 'PAY_US_TAX_DEDUCTIONS';
3221 else
3222 run_bal_stat(1).attribute_name := 'PAY_CA_EARNINGS';
3223 run_bal_stat(2).attribute_name := 'PAY_CA_DEDUCTIONS';
3224 end if;
3225
3226 for i in run_bal_stat.first .. run_bal_stat.last loop
3227 run_bal_stat(i).valid_status := pay_us_payroll_utils.check_balance_status(
3228 p_curr_pymt_eff_date,
3229 lv_business_grp_id,
3230 run_bal_stat(i).attribute_name,
3231 p_legislation_code);
3232 if (lv_rb_status is NULL and run_bal_stat(i).valid_status = 'N') then
3233 lv_rb_status := 'N';
3234 end if;
3235 end loop;
3236 end if;
3237
3238 if lv_rb_status is NULL then
3239 lv_rb_status := 'Y';
3240 end if;
3241
3242 return (lv_rb_status);
3243
3244 END check_run_balance_status;
3245
3246
3247 PROCEDURE get_prev_ytd_elements(p_assignment_id in number
3248 ,p_xfr_action_id in number
3249 ,p_curr_pymt_action_id in number
3250 ,p_curr_pymt_eff_date in date
3251 ,p_start_eff_date in date
3252 ,p_tax_unit_id in number
3253 ,p_ytd_balcall_aaid in number
3254 ,p_sepchk_flag in varchar2
3255 ,p_sepchk_run_type_id in number
3256 ,p_legislation_code in varchar2
3257 ,p_action_type1 in varchar2
3258 ,p_action_type2 in varchar2
3259 ,p_action_type3 in varchar2
3260 )
3261 IS
3262
3263 lv_element_classification_name VARCHAR2(80);
3264 ln_processing_priority NUMBER;
3265 lv_reporting_name VARCHAR2(80);
3266 ln_element_type_id NUMBER;
3267 lv_jurisdiction_code VARCHAR2(80);
3268 ln_primary_balance_id NUMBER;
3269 ln_hours_balance_id NUMBER;
3270
3271 ln_element_index NUMBER ;
3272 lv_element_archived VARCHAR2(1);
3273 lv_procedure_name VARCHAR2(100);
3274 lv_error_message VARCHAR2(200);
3275 ln_step NUMBER;
3276 lv_run_bal_status VARCHAR2(1);
3277
3278 BEGIN
3279 ln_step := 1;
3280 lv_run_bal_status := NULL;
3281 lv_element_archived := 'N';
3282 lv_procedure_name := '.get_prev_ytd_elements';
3283
3284 hr_utility.set_location(gv_package || lv_procedure_name, 10);
3285 hr_utility.trace('p_xfr_action_id' || p_xfr_action_id);
3286 hr_utility.trace('p_assignment_id '|| p_assignment_id);
3287 hr_utility.trace('p_tax_unit_id ' || p_tax_unit_id);
3288 hr_utility.trace('p_sepchk_flag ' || p_sepchk_flag);
3289 hr_utility.trace('p_curr_pymt_eff_date '|| p_curr_pymt_eff_date);
3290 hr_utility.trace('p_start_eff_date '|| p_start_eff_date);
3291 hr_utility.trace('p_legislation_code ' || p_legislation_code);
3292 hr_utility.trace('p_sepchk_run_type_id '|| p_sepchk_run_type_id);
3293 hr_utility.trace('p_ytd_balcall_aaid ' || p_ytd_balcall_aaid);
3294 hr_utility.trace('p_curr_pymt_action_id '
3295 ||to_char(p_curr_pymt_action_id ));
3296
3297
3298 lv_run_bal_status := check_run_balance_status(
3299 p_assignment_id => p_assignment_id
3300 ,p_curr_pymt_eff_date => p_curr_pymt_eff_date
3301 ,p_legislation_code => p_legislation_code);
3302
3306 p_start_eff_date);
3303 if lv_run_bal_status = 'Y' then
3304 open c_prev_ytd_action_elem_rbr(p_assignment_id,
3305 p_curr_pymt_eff_date,
3307 else
3308 open c_prev_ytd_action_elements(p_assignment_id
3309 ,p_curr_pymt_eff_date
3310 ,p_start_eff_date
3311 ,p_action_type1
3312 ,p_action_type2
3313 ,p_action_type3);
3314 end if;
3315
3316 loop
3317 if lv_run_bal_status = 'Y' then
3318 fetch c_prev_ytd_action_elem_rbr into
3319 lv_element_classification_name,
3320 ln_processing_priority,
3321 lv_reporting_name,
3322 --lv_element_name,
3323 ln_element_type_id,
3324 lv_jurisdiction_code,
3325 ln_primary_balance_id,
3326 ln_hours_balance_id;
3327 if c_prev_ytd_action_elem_rbr%notfound then
3328 hr_utility.set_location(gv_package || lv_procedure_name, 40);
3329 exit;
3330 end if;
3331 else
3332 fetch c_prev_ytd_action_elements into
3333 lv_element_classification_name,
3334 ln_processing_priority,
3335 lv_reporting_name,
3336 --lv_element_name,
3337 ln_element_type_id,
3338 lv_jurisdiction_code,
3339 ln_primary_balance_id,
3340 ln_hours_balance_id;
3341 if c_prev_ytd_action_elements%notfound then
3342 hr_utility.set_location(gv_package || lv_procedure_name, 45);
3343 exit;
3344 end if;
3345 end if;
3346
3347 hr_utility.set_location(gv_package || lv_procedure_name, 50);
3348 hr_utility.trace('Ele type id = ' || ln_element_type_id);
3349 hr_utility.trace('Reporting Name = '|| lv_reporting_name);
3350 hr_utility.trace('Primary Bal id = '|| ln_primary_balance_id);
3351 hr_utility.trace('JD Code = ' || lv_jurisdiction_code);
3352 hr_utility.trace('Ele Class = ' || lv_element_classification_name);
3353
3354 if lv_element_classification_name like '% Deductions' then
3355 ln_step := 10;
3356 ln_hours_balance_id := null;
3357 end if;
3358
3359 /**********************************************************
3360 ** check whether the element has already been archived
3361 ** when archiving the Current Action. If it has been archived
3362 ** skip the element
3363 **********************************************************/
3364 ln_step := 15;
3365 if pay_ac_action_arch.emp_elements_tab.count > 0 then
3366 for i in pay_ac_action_arch.emp_elements_tab.first ..
3367 pay_ac_action_arch.emp_elements_tab.last loop
3368
3369 if pay_ac_action_arch.emp_elements_tab(i).element_primary_balance_id
3370 = ln_primary_balance_id and
3371 pay_ac_action_arch.emp_elements_tab(i).jurisdiction_code
3372 = lv_jurisdiction_code then
3373
3374 hr_utility.set_location(gv_package || lv_procedure_name, 65);
3375 lv_element_archived := 'Y';
3376 exit;
3377 end if;
3378 end loop;
3379 end if;
3380
3381 if lv_element_archived = 'N' then
3382 ln_step := 20;
3383 hr_utility.set_location(gv_package || lv_procedure_name, 70);
3384 ln_element_index := pay_ac_action_arch.emp_elements_tab.count;
3385 pay_ac_action_arch.emp_elements_tab(ln_element_index).element_type_id
3386 := ln_element_type_id;
3387 pay_ac_action_arch.emp_elements_tab(ln_element_index).element_classfn
3388 := lv_element_classification_name;
3389 pay_ac_action_arch.emp_elements_tab(ln_element_index).element_reporting_name
3390 := lv_reporting_name;
3391 pay_ac_action_arch.emp_elements_tab(ln_element_index).element_primary_balance_id
3392 := ln_primary_balance_id;
3393 pay_ac_action_arch.emp_elements_tab(ln_element_index).element_processing_priority
3394 := ln_processing_priority;
3395 pay_ac_action_arch.emp_elements_tab(ln_element_index).element_hours_balance_id
3396 := ln_hours_balance_id;
3397 pay_ac_action_arch.emp_elements_tab(ln_element_index).jurisdiction_code
3398 := lv_jurisdiction_code;
3399
3400 /*****************************************************************
3401 ** The Payment Assignemnt Action is not passed to this procedure
3402 ** as we do not want to call the Payment Balance.
3403 *****************************************************************/
3404 hr_utility.set_location(gv_package || lv_procedure_name, 80);
3405
3406 ln_step := 25;
3407 populate_elements(p_xfr_action_id => p_xfr_action_id
3408 ,p_pymt_assignment_action_id => p_curr_pymt_action_id
3412 ,p_hours_balance_id => ln_hours_balance_id
3409 ,p_pymt_eff_date => p_curr_pymt_eff_date
3410 ,p_element_type_id => ln_element_type_id
3411 ,p_primary_balance_id => ln_primary_balance_id
3413 ,p_processing_priority => ln_processing_priority
3414 ,p_element_classification_name
3415 => lv_element_classification_name
3416 ,p_reporting_name => lv_reporting_name
3417 ,p_tax_unit_id => p_tax_unit_id
3418 ,p_pymt_balcall_aaid => null
3419 ,p_ytd_balcall_aaid => p_ytd_balcall_aaid
3420 ,p_jurisdiction_code => lv_jurisdiction_code
3421 ,p_legislation_code => p_legislation_code
3422 ,p_sepchk_flag => p_sepchk_flag
3423 ,p_sepchk_run_type_id => p_sepchk_run_type_id
3424 ,p_original_date_earned => null
3425 ,p_effective_start_date => null
3426 ,p_effective_end_date => null
3427 ,p_final_rate => null
3428 ,p_ytd_flag => 'N'
3429 );
3430 end if;
3431 lv_element_archived := 'N'; -- Initilializing the variable back
3432 -- to N for the next element
3433 lv_element_classification_name := null;
3434 ln_element_type_id := null;
3435 lv_jurisdiction_code := null;
3436 ln_primary_balance_id := null;
3437 ln_processing_priority := null;
3438 lv_reporting_name := null;
3439 ln_hours_balance_id := null;
3440 end loop;
3441
3442 -- Bug 3585754
3443 if lv_run_bal_status = 'Y' then
3444 close c_prev_ytd_action_elem_rbr;
3445 else
3446 close c_prev_ytd_action_elements;
3447 end if;
3448
3449 hr_utility.set_location(gv_package || lv_procedure_name, 90);
3450
3451
3452 ln_step := 30;
3453 if pay_ac_action_arch.lrr_act_tab.count > 0 then
3454 for i in pay_ac_action_arch.lrr_act_tab.first ..
3455 pay_ac_action_arch.lrr_act_tab.last loop
3456
3457 hr_utility.trace('after populate_elements ftp' ||
3458 ' action_context_id is ' ||
3459 to_char(pay_ac_action_arch.lrr_act_tab(i).action_context_id));
3460 hr_utility.trace('action_info_category ' ||
3461 pay_ac_action_arch.lrr_act_tab(i).action_info_category);
3462 hr_utility.trace('act_info1 is ' ||
3463 pay_ac_action_arch.lrr_act_tab(i).act_info1);
3464 hr_utility.trace('act_info10 ' ||
3465 pay_ac_action_arch.lrr_act_tab(i).act_info10);
3466 hr_utility.trace('act_info3 ' ||
3467 pay_ac_action_arch.lrr_act_tab(i).act_info3);
3468 hr_utility.trace('act_info4 ' ||
3469 pay_ac_action_arch.lrr_act_tab(i).act_info4);
3470 hr_utility.trace('act_info5 ' ||
3471 pay_ac_action_arch.lrr_act_tab(i).act_info5);
3472 hr_utility.trace('act_info6 ' ||
3473 pay_ac_action_arch.lrr_act_tab(i).act_info6);
3474 hr_utility.trace('act_info7 ' ||
3475 pay_ac_action_arch.lrr_act_tab(i).act_info7);
3476 hr_utility.trace('act_info8 ' ||
3477 pay_ac_action_arch.lrr_act_tab(i).act_info8);
3478
3479 end loop;
3480 end if;
3481
3482 hr_utility.set_location(gv_package || lv_procedure_name, 110);
3483
3484 ln_step := 35;
3485 if pay_ac_action_arch.emp_elements_tab.count > 0 then
3486 for j in pay_ac_action_arch.emp_elements_tab.first ..
3487 pay_ac_action_arch.emp_elements_tab.last loop
3488
3489 hr_utility.trace('EMP_ELEMENTS_TAB.element_type ' ||
3490 to_char(pay_ac_action_arch.emp_elements_tab(j).element_type_id));
3491 end loop;
3492 end if;
3493
3494 hr_utility.set_location(gv_package || lv_procedure_name, 200);
3495
3496 EXCEPTION
3497 when others then
3498
3499 lv_error_message := 'Error at step ' || ln_step ||
3500 ' in ' || gv_package || lv_procedure_name;
3501
3502 hr_utility.trace(lv_error_message || '-' || sqlerrm);
3503
3504 lv_error_message :=
3505 pay_emp_action_arch.set_error_message(lv_error_message);
3506
3507 hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
3508 hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
3509 hr_utility.raise_error;
3510
3511 END get_prev_ytd_elements;
3512
3513
3514 /******************************************************************
3515 Name : first_time_process
3516 Purpose : This procedure is called only if the archiver is run
3517 for the first time for an assignment. It gets all the
3518 elements which have been processed within a given
3519 calendar year till current payment date i.e. the
3523 ******************************************************************/
3520 end date of the Archiver run.
3521 Arguments :
3522 Notes :
3524 PROCEDURE first_time_process(p_assignment_id in number
3525 ,p_xfr_action_id in number
3526 ,p_curr_pymt_action_id in number
3527 ,p_curr_pymt_eff_date in date
3528 ,p_curr_eff_date in date
3529 ,p_tax_unit_id in number
3530 ,p_sepchk_run_type_id in number
3531 ,p_ytd_balcall_aaid in number
3532 ,p_pymt_balcall_aaid in number
3533 ,p_sepchk_flag in varchar2
3534 ,p_legislation_code in varchar2
3535 )
3536
3537 IS
3538
3539 lv_procedure_name VARCHAR2(100);
3540 lv_error_message VARCHAR2(200);
3541 ln_step NUMBER;
3542
3543 BEGIN
3544 ln_step := 1;
3545 lv_procedure_name := '.first_time_process';
3546
3547 hr_utility.set_location(gv_package || lv_procedure_name, 10);
3548 hr_utility.trace('p_xfr_action_id' || p_xfr_action_id);
3549 hr_utility.trace('p_assignment_id '|| p_assignment_id);
3550 hr_utility.trace('p_curr_eff_date '|| p_curr_eff_date);
3551 hr_utility.trace('p_tax_unit_id ' || p_tax_unit_id);
3552 hr_utility.trace('p_sepchk_flag ' || p_sepchk_flag);
3553 hr_utility.trace('p_legislation_code ' || p_legislation_code);
3554 hr_utility.trace('p_sepchk_run_type_id '|| p_sepchk_run_type_id);
3555 hr_utility.trace('p_ytd_balcall_aaid ' || p_ytd_balcall_aaid);
3556 hr_utility.trace('p_pymt_balcall_aaid ' || p_pymt_balcall_aaid);
3557 hr_utility.trace('p_curr_pymt_action_id '
3558 ||to_char(p_curr_pymt_action_id ));
3559
3560 hr_utility.set_location(gv_package || lv_procedure_name, 20);
3561 ln_step := 10;
3562 get_current_elements(p_xfr_action_id => p_xfr_action_id
3563 ,p_curr_pymt_action_id => p_curr_pymt_action_id
3564 ,p_curr_pymt_eff_date => p_curr_pymt_eff_date
3565 ,p_assignment_id => p_assignment_id
3566 ,p_tax_unit_id => p_tax_unit_id
3567 ,p_sepchk_run_type_id => p_sepchk_run_type_id
3568 ,p_sepchk_flag => p_sepchk_flag
3569 ,p_pymt_balcall_aaid => p_pymt_balcall_aaid
3570 ,p_ytd_balcall_aaid => p_ytd_balcall_aaid
3571 ,p_legislation_code => p_legislation_code);
3572 hr_utility.set_location(gv_package || lv_procedure_name, 30);
3573
3574 ln_step := 20;
3575 get_prev_ytd_elements(p_assignment_id => p_assignment_id
3576 ,p_xfr_action_id => p_xfr_action_id
3577 ,p_curr_pymt_action_id => p_curr_pymt_action_id
3578 ,p_curr_pymt_eff_date => p_curr_pymt_eff_date
3579 ,p_start_eff_date => trunc(p_curr_pymt_eff_date, 'Y')
3580 ,p_tax_unit_id => p_tax_unit_id
3581 ,p_ytd_balcall_aaid => p_ytd_balcall_aaid
3582 ,p_sepchk_flag => p_sepchk_flag
3583 ,p_sepchk_run_type_id => p_sepchk_run_type_id
3584 ,p_legislation_code => p_legislation_code
3585 ,p_action_type1 => 'R'
3586 ,p_action_type2 => 'Q'
3587 ,p_action_type3 => 'B');
3588
3589 ln_step := 30;
3590 hr_utility.set_location(gv_package || lv_procedure_name, 200);
3591
3592 EXCEPTION
3593 when others then
3594
3595 lv_error_message := 'Error at step ' || ln_step ||
3596 ' in ' || gv_package || lv_procedure_name;
3597
3598 hr_utility.trace(lv_error_message || '-' || sqlerrm);
3599
3600 lv_error_message :=
3601 pay_emp_action_arch.set_error_message(lv_error_message);
3602
3603 hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
3604 hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
3605 hr_utility.raise_error;
3606
3607 END first_time_process;
3608
3609
3610 /******************************************************************
3611 Name : populate_summary
3612 Purpose : This procedure add the values for different
3613 classifications and inserts two rows for CURRENT and
3614 YTD Summary.
3615 Arguments :
3616 Notes :
3617 ******************************************************************/
3618 PROCEDURE populate_summary(p_xfr_action_id in number)
3619 IS
3620 lv_earnings VARCHAR2(80):= 0;
3621 lv_supplemental_earnings VARCHAR2(80):= 0;
3622 lv_imputed_Earnings VARCHAR2(80):= 0;
3623 lv_non_payroll_payments VARCHAR2(80):= 0;
3624 lv_pre_tax_deductions VARCHAR2(80):= 0;
3625 lv_involuntary_deductions VARCHAR2(80):= 0;
3626 lv_voluntary_deductions VARCHAR2(80):= 0;
3627 lv_tax_deductions VARCHAR2(80):= 0;
3631 lv_ytd_earnings VARCHAR2(80):= 0;
3628 lv_taxable_benefits VARCHAR2(80):= 0;
3629 lv_alien_expat_earnings VARCHAR2(80):= 0;
3630
3632 lv_ytd_supplemental_earnings VARCHAR2(80):= 0;
3633 lv_ytd_imputed_Earnings VARCHAR2(80):= 0;
3634 lv_ytd_non_payroll_payments VARCHAR2(80):= 0;
3635 lv_ytd_pre_tax_deductions VARCHAR2(80):= 0;
3636 lv_ytd_involuntary_deductions VARCHAR2(80):= 0;
3637 lv_ytd_voluntary_deductions VARCHAR2(80):= 0;
3638 lv_ytd_tax_deductions VARCHAR2(80):= 0;
3639 lv_ytd_taxable_benefits VARCHAR2(80):= 0;
3640 lv_ytd_alien_expat_earnings VARCHAR2(80):= 0;
3641
3642 ln_index NUMBER;
3643 lv_procedure_name VARCHAR2(100) := '.populate_summary';
3644 lv_error_message VARCHAR2(200);
3645 ln_step NUMBER;
3646
3647 lv_current_label VARCHAR2(100);
3648 lv_ytd_label VARCHAR2(100);
3649 j NUMBER := 0;
3650
3651 BEGIN
3652 ln_step := 1;
3653 hr_utility.set_location(gv_package || lv_procedure_name, 10);
3654 if pay_ac_action_arch.lrr_act_tab.count > 0 then
3655 hr_utility.set_location(gv_package || lv_procedure_name, 20);
3656
3657 ln_step := 2;
3658 for i in pay_ac_action_arch.lrr_act_tab.first ..
3659 pay_ac_action_arch.lrr_act_tab.last loop
3660
3661 if pay_ac_action_arch.lrr_act_tab(i).action_context_id
3662 = p_xfr_action_id then
3663 if pay_ac_action_arch.lrr_act_tab(i).action_info_category
3664 = 'AC EARNINGS' then
3665 if pay_ac_action_arch.lrr_act_tab(i).act_info1
3666 = 'Earnings' then
3667 hr_utility.set_location(gv_package || lv_procedure_name, 30);
3668 ln_step := 3;
3669 lv_earnings
3670 := lv_earnings +
3671 nvl(fnd_number.canonical_to_number(pay_ac_action_arch.lrr_act_tab(i).act_info8),0);
3672 lv_ytd_earnings
3673 := lv_ytd_earnings +
3674 nvl(fnd_number.canonical_to_number(pay_ac_action_arch.lrr_act_tab(i).act_info9),0);
3675 elsif pay_ac_action_arch.lrr_act_tab(i).act_info1
3676 = 'Supplemental Earnings' then
3677 hr_utility.set_location(gv_package || lv_procedure_name, 40);
3678 ln_step := 4;
3679 lv_supplemental_earnings
3680 := lv_supplemental_earnings +
3681 nvl(fnd_number.canonical_to_number(pay_ac_action_arch.lrr_act_tab(i).act_info8),0);
3682 lv_ytd_supplemental_earnings
3683 := lv_ytd_supplemental_earnings +
3684 nvl(fnd_number.canonical_to_number(pay_ac_action_arch.lrr_act_tab(i).act_info9),0);
3685 elsif pay_ac_action_arch.lrr_act_tab(i).act_info1
3686 = 'Imputed Earnings' then
3687 hr_utility.set_location(gv_package || lv_procedure_name, 50);
3688 ln_step := 5;
3689 lv_imputed_earnings
3690 := lv_imputed_earnings +
3691 nvl(fnd_number.canonical_to_number(pay_ac_action_arch.lrr_act_tab(i).act_info8),0);
3692 lv_ytd_imputed_earnings
3693 := lv_ytd_imputed_earnings +
3694 nvl(fnd_number.canonical_to_number(pay_ac_action_arch.lrr_act_tab(i).act_info9),0);
3695 elsif pay_ac_action_arch.lrr_act_tab(i).act_info1
3696 = 'Non-payroll Payments' then
3697 hr_utility.set_location(gv_package || lv_procedure_name, 50);
3698 ln_step := 6;
3699 lv_non_payroll_payments
3700 := lv_non_payroll_payments +
3701 nvl(fnd_number.canonical_to_number(pay_ac_action_arch.lrr_act_tab(i).act_info8),0);
3702 lv_ytd_non_payroll_payments
3703 := lv_ytd_non_payroll_payments +
3704 nvl(fnd_number.canonical_to_number(pay_ac_action_arch.lrr_act_tab(i).act_info9),0);
3705 elsif pay_ac_action_arch.lrr_act_tab(i).act_info1
3706 = 'Taxable Benefits' then
3707 hr_utility.set_location(gv_package || lv_procedure_name, 55);
3708 ln_step := 7;
3709 lv_taxable_benefits
3710 := lv_taxable_benefits +
3711 nvl(fnd_number.canonical_to_number(pay_ac_action_arch.lrr_act_tab(i).act_info8),0);
3712 lv_ytd_taxable_benefits
3713 := lv_ytd_taxable_benefits +
3714 nvl(fnd_number.canonical_to_number(pay_ac_action_arch.lrr_act_tab(i).act_info9),0);
3715 elsif pay_ac_action_arch.lrr_act_tab(i).act_info1
3716 = 'Alien/Expat Earnings' then
3717 hr_utility.set_location(gv_package || lv_procedure_name, 56);
3718 ln_step := 8;
3719 lv_alien_expat_earnings
3720 := lv_alien_expat_earnings +
3721 nvl(fnd_number.canonical_to_number(pay_ac_action_arch.lrr_act_tab(i).act_info8),0);
3725 end if;
3722 lv_ytd_alien_expat_earnings
3723 := lv_ytd_alien_expat_earnings +
3724 nvl(fnd_number.canonical_to_number(pay_ac_action_arch.lrr_act_tab(i).act_info9),0);
3726
3727 elsif pay_ac_action_arch.lrr_act_tab(i).action_info_category
3728 = 'AC DEDUCTIONS' then
3729 if pay_ac_action_arch.lrr_act_tab(i).act_info1
3730 = 'Pre-Tax Deductions' then
3731 hr_utility.set_location(gv_package || lv_procedure_name, 60);
3732 ln_step := 15;
3733 lv_pre_tax_deductions
3734 := lv_pre_tax_deductions +
3735 nvl(fnd_number.canonical_to_number(pay_ac_action_arch.lrr_act_tab(i).act_info8),0);
3736 lv_ytd_pre_tax_deductions
3737 := lv_ytd_pre_tax_deductions +
3738 nvl(fnd_number.canonical_to_number(pay_ac_action_arch.lrr_act_tab(i).act_info9),0);
3739 elsif pay_ac_action_arch.lrr_act_tab(i).act_info1
3740 = 'Involuntary Deductions' then
3741 hr_utility.set_location(gv_package || lv_procedure_name, 70);
3742 ln_step := 16;
3743 lv_involuntary_deductions
3744 := lv_involuntary_deductions +
3745 nvl(fnd_number.canonical_to_number(pay_ac_action_arch.lrr_act_tab(i).act_info8),0);
3746 lv_ytd_involuntary_deductions
3747 := lv_ytd_involuntary_deductions +
3748 nvl(fnd_number.canonical_to_number(pay_ac_action_arch.lrr_act_tab(i).act_info9),0);
3749 elsif pay_ac_action_arch.lrr_act_tab(i).act_info1
3750 = 'Voluntary Deductions' then
3751 hr_utility.set_location(gv_package || lv_procedure_name, 80);
3752 ln_step := 17;
3753 lv_voluntary_deductions
3754 := lv_voluntary_deductions +
3755 nvl(fnd_number.canonical_to_number(pay_ac_action_arch.lrr_act_tab(i).act_info8),0);
3756 lv_ytd_voluntary_deductions
3757 := lv_ytd_voluntary_deductions +
3758 nvl(fnd_number.canonical_to_number(pay_ac_action_arch.lrr_act_tab(i).act_info9),0);
3759 elsif pay_ac_action_arch.lrr_act_tab(i).act_info1
3760 = 'Tax Deductions' then
3761 hr_utility.set_location(gv_package || lv_procedure_name, 90);
3762 ln_step := 18;
3763 lv_tax_deductions
3764 := lv_tax_deductions +
3765 nvl(fnd_number.canonical_to_number(pay_ac_action_arch.lrr_act_tab(i).act_info8),0);
3766 lv_ytd_tax_deductions
3767 := lv_ytd_tax_deductions +
3768 nvl(fnd_number.canonical_to_number(pay_ac_action_arch.lrr_act_tab(i).act_info9),0);
3769 end if;
3770 end if;
3771 end if;
3772 end loop;
3773 end if;
3774
3775 hr_utility.set_location(gv_package || lv_procedure_name, 95);
3776 ln_step := 24;
3777 j := 0;
3778 if pay_ac_action_arch.ltr_summary_labels.count > 0 then
3779 for j in pay_ac_action_arch.ltr_summary_labels.first..
3780 pay_ac_action_arch.ltr_summary_labels.last loop
3781 if pay_ac_action_arch.ltr_summary_labels(j).language
3782 = pay_ac_action_arch.gv_person_lang and
3783 pay_ac_action_arch.ltr_summary_labels(j).lookup_code = 'CURRENT' then
3784 lv_current_label := pay_ac_action_arch.ltr_summary_labels(j).meaning;
3785 end if;
3786
3787 if pay_ac_action_arch.ltr_summary_labels(j).language
3788 = pay_ac_action_arch.gv_person_lang and
3789 pay_ac_action_arch.ltr_summary_labels(j).lookup_code = 'YTD' then
3790 lv_ytd_label := pay_ac_action_arch.ltr_summary_labels(j).meaning;
3791 end if;
3792 end loop;
3793 end if;
3794
3795 hr_utility.set_location(gv_package || lv_procedure_name, 100);
3796 /* Insert one row for CURRENT and one for YTD */
3797 if pay_ac_action_arch.lrr_act_tab.count > 0 then
3798 ln_step := 25;
3799 -- CURRENT
3800 ln_index := pay_ac_action_arch.lrr_act_tab.count;
3801 hr_utility.trace('ln_index = ' || ln_index);
3802 pay_ac_action_arch.lrr_act_tab(ln_index).action_info_category
3803 := 'AC SUMMARY CURRENT';
3804 pay_ac_action_arch.lrr_act_tab(ln_index).jurisdiction_code
3805 := '00-000-0000';
3806 pay_ac_action_arch.lrr_act_tab(ln_index).act_info4
3807 := fnd_number.number_to_canonical(lv_earnings); /*Bug 3311866*/
3808 pay_ac_action_arch.lrr_act_tab(ln_index).act_info5
3809 := fnd_number.number_to_canonical(lv_supplemental_earnings) ;
3810 pay_ac_action_arch.lrr_act_tab(ln_index).act_info6
3811 := fnd_number.number_to_canonical(lv_imputed_earnings);
3812 pay_ac_action_arch.lrr_act_tab(ln_index).act_info7
3813 := fnd_number.number_to_canonical(lv_pre_tax_deductions) ;
3814 pay_ac_action_arch.lrr_act_tab(ln_index).act_info8
3815 := fnd_number.number_to_canonical(lv_involuntary_deductions);
3819 := fnd_number.number_to_canonical(lv_tax_deductions) ;
3816 pay_ac_action_arch.lrr_act_tab(ln_index).act_info9
3817 := fnd_number.number_to_canonical(lv_voluntary_deductions) ;
3818 pay_ac_action_arch.lrr_act_tab(ln_index).act_info10
3820 pay_ac_action_arch.lrr_act_tab(ln_index).act_info11
3821 := fnd_number.number_to_canonical(lv_taxable_benefits);
3822 pay_ac_action_arch.lrr_act_tab(ln_index).act_info12
3823 := fnd_number.number_to_canonical(lv_alien_expat_earnings);
3824 pay_ac_action_arch.lrr_act_tab(ln_index).act_info13
3825 := fnd_number.number_to_canonical(lv_non_payroll_payments);
3826 pay_ac_action_arch.lrr_act_tab(ln_index).act_info14
3827 := lv_current_label;
3828
3829 hr_utility.set_location(gv_package || lv_procedure_name, 120);
3830 -- YTD
3831 ln_index := pay_ac_action_arch.lrr_act_tab.count;
3832 hr_utility.trace('ln_index = ' || ln_index);
3833 pay_ac_action_arch.lrr_act_tab(ln_index).action_info_category
3834 := 'AC SUMMARY YTD';
3835 pay_ac_action_arch.lrr_act_tab(ln_index).jurisdiction_code
3836 := '00-000-0000';
3837 pay_ac_action_arch.lrr_act_tab(ln_index).act_info4
3838 := fnd_number.number_to_canonical(lv_ytd_earnings); /*Bug 3311866*/
3839 pay_ac_action_arch.lrr_act_tab(ln_index).act_info5
3840 := fnd_number.number_to_canonical(lv_ytd_supplemental_earnings) ;
3841 pay_ac_action_arch.lrr_act_tab(ln_index).act_info6
3842 := fnd_number.number_to_canonical(lv_ytd_imputed_earnings);
3843 pay_ac_action_arch.lrr_act_tab(ln_index).act_info7
3844 := fnd_number.number_to_canonical(lv_ytd_pre_tax_deductions) ;
3845 pay_ac_action_arch.lrr_act_tab(ln_index).act_info8
3846 := fnd_number.number_to_canonical(lv_ytd_involuntary_deductions);
3847 pay_ac_action_arch.lrr_act_tab(ln_index).act_info9
3848 := fnd_number.number_to_canonical(lv_ytd_voluntary_deductions) ;
3849 pay_ac_action_arch.lrr_act_tab(ln_index).act_info10
3850 := fnd_number.number_to_canonical(lv_ytd_tax_deductions) ;
3851 pay_ac_action_arch.lrr_act_tab(ln_index).act_info11
3852 := fnd_number.number_to_canonical(lv_ytd_taxable_benefits);
3853 pay_ac_action_arch.lrr_act_tab(ln_index).act_info12
3854 := fnd_number.number_to_canonical(lv_ytd_alien_expat_earnings);
3855 pay_ac_action_arch.lrr_act_tab(ln_index).act_info13
3856 := fnd_number.number_to_canonical(lv_ytd_non_payroll_payments);
3857 pay_ac_action_arch.lrr_act_tab(ln_index).act_info14
3858 := lv_ytd_label;
3859 end if;
3860
3861 hr_utility.set_location(gv_package || lv_procedure_name, 200);
3862 ln_step := 15;
3863
3864 EXCEPTION
3865 when others then
3866
3867 lv_error_message := 'Error at step ' || ln_step ||
3868 ' in ' || gv_package || lv_procedure_name;
3869
3870 hr_utility.trace(lv_error_message || '-' || sqlerrm);
3871
3872 lv_error_message :=
3873 pay_emp_action_arch.set_error_message(lv_error_message);
3874
3875 hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
3876 hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
3877 hr_utility.raise_error;
3878
3879 END populate_summary;
3880
3881 /******************************************************************
3882 Name : process_additional_elements
3883 Purpose : Retrieve the elements processed in the given assignment
3884 and insert YTD balance to pl/sql table.
3885 Arguments : p_assignment_id => Terminated Assignment Id
3886 p_assignment_action_id => Max assignment action id
3887 of given assignment
3888 p_curr_eff_date => Current effective date
3889 p_xfr_action_id => Current XFR action id.
3890 Notes : This process is used to retrieve elements processed
3891 in terminated assignments which is not picked up by
3892 the archiver.
3893 ******************************************************************/
3894 PROCEDURE process_additional_elements(p_assignment_id in number
3895 ,p_assignment_action_id in number
3896 ,p_curr_eff_date in date
3897 ,p_xfr_action_id in number
3898 ,p_legislation_code in varchar2
3899 ,p_tax_unit_id in number)
3900 IS
3901
3902 lv_procedure_name VARCHAR2(50) := '.process_additional_elements';
3903 lv_element_classification_name VARCHAR2(80);
3904 ln_processing_priority NUMBER;
3905 lv_reporting_name VARCHAR2(80);
3906 ln_element_type_id NUMBER;
3907 lv_jurisdiction_code VARCHAR2(80);
3908 ln_primary_balance_id NUMBER;
3909 ln_hours_balance_id NUMBER;
3910 ln_element_index NUMBER;
3911 lv_action_category VARCHAR2(50) := 'AC DEDUCTIONS';
3912 ln_ytd_defined_balance_id NUMBER;
3913 ln_ytd_amount NUMBER(15,2) := 0;
3914 ln_ytd_hours_balance_id NUMBER;
3915 ln_ytd_hours NUMBER(15,2);
3916 ln_current_hours NUMBER(15,2) := 0;
3917 ln_payments_amount NUMBER(15,2) := 0;
3921 ln_step NUMBER;
3918 ln_index NUMBER;
3919 ln_check_count number;
3920 ln_check_count2 number;
3922 lv_error_message VARCHAR2(200);
3923
3924 BEGIN
3925 hr_utility.set_location(gv_package || lv_procedure_name, 10);
3926
3927 ln_step := 10;
3928 pay_balance_pkg.set_context('TAX_UNIT_ID',p_tax_unit_id); -- Bug 3639249
3929 OPEN c_prev_ytd_action_elements(p_assignment_id
3930 ,p_curr_eff_date
3931 ,trunc(p_curr_eff_date, 'Y')
3932 ,'R', 'Q', 'B');
3933 LOOP
3934 FETCH c_prev_ytd_action_elements into lv_element_classification_name,
3935 ln_processing_priority,
3936 lv_reporting_name,
3937 ln_element_type_id,
3938 lv_jurisdiction_code,
3939 ln_primary_balance_id,
3940 ln_hours_balance_id;
3941 IF c_prev_ytd_action_elements%NOTFOUND then
3942 hr_utility.set_location(gv_package || lv_procedure_name, 15);
3943 exit;
3944 END IF;
3945
3946 ln_step := 20;
3947 hr_utility.set_location(gv_package || lv_procedure_name, 20);
3948 hr_utility.trace('================= Fetched Element ==================');
3949 hr_utility.trace('ele classification = '||lv_element_classification_name);
3950 hr_utility.trace('ele type id = ' || ln_element_type_id);
3951 hr_utility.trace('reporting name = ' || lv_reporting_name);
3952 hr_utility.trace('primary balance id = ' || ln_primary_balance_id);
3953 hr_utility.trace('hours balance id = ' || ln_hours_balance_id);
3954
3955 if lv_jurisdiction_code <> '00-000-0000' then
3956 pay_balance_pkg.set_context('JURISDICTION_CODE', lv_jurisdiction_code);
3957 gv_ytd_balance_dimension := gv_dim_asg_jd_gre_ytd;
3958 else
3959 pay_balance_pkg.set_context('JURISDICTION_CODE', lv_jurisdiction_code);
3960 if gv_reporting_level = 'TAXGRP' then
3961 gv_ytd_balance_dimension := gv_dim_asg_tg_ytd;
3962 else
3963 gv_ytd_balance_dimension := gv_dim_asg_gre_ytd;
3964 end if;
3965 end if;
3966
3967 if lv_element_classification_name like '% Deductions' then
3968 ln_hours_balance_id := null;
3969 end if;
3970
3971 if ln_hours_balance_id is not null then
3972 ln_step := 30;
3973 hr_utility.set_location(gv_package || lv_procedure_name, 22);
3974 ln_ytd_hours_balance_id
3975 := pay_emp_action_arch.get_defined_balance_id(
3976 ln_hours_balance_id,
3977 gv_ytd_balance_dimension,
3978 p_legislation_code);
3979 hr_utility.trace('ln_ytd_hours_balance_id = '||
3980 ln_ytd_hours_balance_id);
3981 hr_utility.set_location(gv_package || lv_procedure_name, 24);
3982
3983 ln_step := 40;
3984 if ln_ytd_hours_balance_id is not null then
3985 ln_ytd_hours := nvl(pay_balance_pkg.get_value(
3986 ln_ytd_hours_balance_id,
3987 p_assignment_action_id),0);
3988 hr_utility.trace('ln_ytd_hours = '||ln_ytd_hours);
3989 hr_utility.set_location(gv_package || lv_procedure_name, 26);
3990 end if;
3991 end if; --Hours
3992
3993 ln_step := 50;
3994 ln_ytd_defined_balance_id
3995 := pay_emp_action_arch.get_defined_balance_id
3996 (ln_primary_balance_id,
3997 gv_ytd_balance_dimension,
3998 p_legislation_code);
3999 hr_utility.trace('ln_ytd_defined_balance_id = '||
4000 ln_ytd_defined_balance_id);
4001 hr_utility.set_location(gv_package || lv_procedure_name, 30);
4002 if ln_ytd_defined_balance_id is not null then
4003 ln_step := 60;
4004 ln_ytd_amount := nvl(pay_balance_pkg.get_value(
4005 ln_ytd_defined_balance_id,
4006 p_assignment_action_id),0);
4007 hr_utility.trace('ln_ytd_amount = '||ln_ytd_amount);
4008 end if;
4009 hr_utility.set_location(gv_package || lv_procedure_name, 40);
4010
4011
4012 if nvl(ln_ytd_amount, 0) <> 0 then
4013 ln_step := 70;
4014 ln_element_index := pay_ac_action_arch.emp_elements_tab.count;
4015
4016 hr_utility.trace('ln_element_index = '||ln_element_index);
4017
4018 pay_ac_action_arch.emp_elements_tab(ln_element_index).element_type_id
4019 := ln_element_type_id;
4020 pay_ac_action_arch.emp_elements_tab(ln_element_index).element_classfn
4021 := lv_element_classification_name;
4022 pay_ac_action_arch.emp_elements_tab(ln_element_index).element_reporting_name
4023 := lv_reporting_name;
4024 pay_ac_action_arch.emp_elements_tab(ln_element_index).element_primary_balance_id
4025 := ln_primary_balance_id;
4029 := ln_hours_balance_id;
4026 pay_ac_action_arch.emp_elements_tab(ln_element_index).element_processing_priority
4027 := ln_processing_priority;
4028 pay_ac_action_arch.emp_elements_tab(ln_element_index).element_hours_balance_id
4030 pay_ac_action_arch.emp_elements_tab(ln_element_index).jurisdiction_code
4031 := lv_jurisdiction_code;
4032
4033
4034 ln_index := pay_ac_action_arch.lrr_act_tab.count;
4035 hr_utility.trace('ln_index = '||ln_index);
4036 if lv_element_classification_name in ('Earnings',
4037 'Supplemental Earnings',
4038 'Taxable Benefits',
4039 'Imputed Earnings',
4040 'Non-payroll Payments',
4041 'Alien/Expat Earnings') then
4042 hr_utility.set_location(gv_package || lv_procedure_name, 50);
4043 lv_action_category := 'AC EARNINGS';
4044 hr_utility.trace('ln_current_hours = '||ln_current_hours);
4045 hr_utility.trace('ln_ytd_hours = '||ln_ytd_hours);
4046 ln_step := 80;
4047 pay_ac_action_arch.lrr_act_tab(ln_index).act_info11
4048 := fnd_number.number_to_canonical(ln_current_hours); /*Bug 3311866*/
4049 pay_ac_action_arch.lrr_act_tab(ln_index).act_info12
4050 := fnd_number.number_to_canonical(ln_ytd_hours);
4051 else
4052 lv_action_category := 'AC DEDUCTIONS';
4053 end if;
4054 hr_utility.set_location(gv_package || lv_procedure_name, 60);
4055 hr_utility.trace('lv_action_category = '||lv_action_category);
4056 hr_utility.trace('ln_ytd_amount = '||ln_ytd_amount);
4057 hr_utility.trace('lv_reporting_name = '||lv_reporting_name);
4058 hr_utility.trace('p_xfr_action_id = '||p_xfr_action_id);
4059 ln_step := 90;
4060
4061 pay_ac_action_arch.lrr_act_tab(ln_index).action_info_category
4062 := lv_action_category;
4063 pay_ac_action_arch.lrr_act_tab(ln_index).jurisdiction_code
4064 := nvl(lv_jurisdiction_code, '00-000-0000');
4065 pay_ac_action_arch.lrr_act_tab(ln_index).action_context_id
4066 := p_xfr_action_id;
4067 pay_ac_action_arch.lrr_act_tab(ln_index).act_info1
4068 := lv_element_classification_name;
4069 pay_ac_action_arch.lrr_act_tab(ln_index).act_info2
4070 := ln_element_type_id;
4071 pay_ac_action_arch.lrr_act_tab(ln_index).act_info6
4072 := ln_primary_balance_id;
4073 pay_ac_action_arch.lrr_act_tab(ln_index).act_info7
4074 := ln_processing_priority;
4075 pay_ac_action_arch.lrr_act_tab(ln_index).act_info8
4076 := fnd_number.number_to_canonical(ln_payments_amount); /*Bug 3311866*/
4077 pay_ac_action_arch.lrr_act_tab(ln_index).act_info9
4078 := fnd_number.number_to_canonical(ln_ytd_amount);
4079 pay_ac_action_arch.lrr_act_tab(ln_index).act_info10
4080 := lv_reporting_name;
4081
4082 end if;
4083 hr_utility.set_location(gv_package || lv_procedure_name, 100);
4084
4085 END LOOP;
4086 CLOSE c_prev_ytd_action_elements;
4087
4088 ln_step := 110;
4089 hr_utility.trace('------------Looping to see pl/sql table --------');
4090 ln_check_count := pay_ac_action_arch.emp_elements_tab.count;
4091 ln_check_count2 := pay_ac_action_arch.lrr_act_tab.count;
4092
4093 hr_utility.trace('ln_check_count = '||ln_check_count);
4094 hr_utility.trace('ln_check_count2 = '||ln_check_count2);
4095 hr_utility.trace('============= End of Processing '||p_assignment_id||
4096 '=============');
4097 hr_utility.set_location(gv_package || lv_procedure_name,150);
4098
4099 EXCEPTION
4100 when others then
4101
4102 lv_error_message := 'Error at step ' || ln_step ||
4103 ' in ' || gv_package || lv_procedure_name;
4104
4105 hr_utility.trace(lv_error_message || '-' || sqlerrm);
4106
4107 lv_error_message :=
4108 pay_emp_action_arch.set_error_message(lv_error_message);
4109
4110 hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
4111 hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
4112 hr_utility.raise_error;
4113
4114 END process_additional_elements;
4115
4116 /******************************************************************
4117 Name : process_balance_adjustment_elements
4118 Purpose : Retrieve the elements processed in the given assignment
4119 and insert YTD balance to pl/sql table.
4120 Arguments : p_assignment_id => Assignment Id
4121 Notes : This process is used to retrieve elements processed
4122 in balance adjustment but have never been processed in
4123 payroll run.
4124 ******************************************************************/
4125 PROCEDURE process_baladj_elements(
4126 p_assignment_id in number
4127 ,p_xfr_action_id in number
4128 ,p_last_xfr_action_id in number
4129 ,p_curr_pymt_action_id in number
4133 ,p_sepchk_run_type_id in number
4130 ,p_curr_pymt_eff_date in date
4131 ,p_ytd_balcall_aaid in number
4132 ,p_sepchk_flag in varchar2
4134 ,p_payroll_id in number
4135 ,p_consolidation_set_id in number
4136 ,p_legislation_code in varchar2
4137 ,p_tax_unit_id in number)
4138 IS
4139 cursor c_check_baladj(cp_assignment_id in number
4140 ,cp_xfr_action_id in number
4141 ,cp_tax_unit_id in number
4142 ,cp_payroll_id in number
4143 ,cp_consolidation_set_id in number
4144 ,cp_curr_eff_date in date) is
4145 select /*+ leading(PPA) index(PPA, PAY_PAYROLL_ACTIONS_N51)
4146 index(PAA, PAY_ASSIGNMENT_ACTIONS_N51) */
4147 min(ppa.effective_date)
4148 from pay_payroll_actions ppa
4149 ,pay_assignment_actions paa
4150 where ppa.action_type = 'B'
4151 and paa.payroll_action_id = ppa.payroll_action_id
4152 and paa.action_status = 'C'
4153 and paa.assignment_action_id > cp_xfr_action_id
4154 and paa.assignment_id = cp_assignment_id
4155 and paa.tax_unit_id = cp_tax_unit_id
4156 and ppa.effective_date >= trunc(cp_curr_eff_date, 'Y')
4157 and ppa.effective_date <= cp_curr_eff_date
4158 and ppa.payroll_id = cp_payroll_id
4159 and ppa.consolidation_set_id = cp_consolidation_set_id;
4160
4161 ld_baladj_date DATE;
4162 ln_step NUMBER;
4163 lv_error_message VARCHAR2(200);
4164 lv_procedure_name VARCHAR2(50);
4165
4166 BEGIN
4167 ln_step := 1;
4168 lv_procedure_name := '.process_baladj_elements';
4169 hr_utility.set_location(gv_package || lv_procedure_name, 10);
4170
4171 open c_check_baladj(p_assignment_id
4172 ,p_last_xfr_action_id
4173 ,p_tax_unit_id
4174 ,p_payroll_id
4175 ,p_consolidation_set_id
4176 ,p_curr_pymt_eff_date);
4177 fetch c_check_baladj into ld_baladj_date;
4178 hr_utility.set_location(gv_package || lv_procedure_name, 20);
4179 ln_step := 10;
4180 if c_check_baladj%found then
4181 -- There is atleast one balance adjustment done since the last archive
4182 -- run, so, need to find out the element and process it
4183 hr_utility.set_location(gv_package || lv_procedure_name, 30);
4184 get_prev_ytd_elements(p_assignment_id => p_assignment_id
4185 ,p_xfr_action_id => p_xfr_action_id
4186 ,p_curr_pymt_action_id => p_curr_pymt_action_id
4187 ,p_curr_pymt_eff_date => p_curr_pymt_eff_date
4188 ,p_start_eff_date => ld_baladj_date
4189 ,p_tax_unit_id => p_tax_unit_id
4190 ,p_ytd_balcall_aaid => p_ytd_balcall_aaid
4191 ,p_sepchk_flag => p_sepchk_flag
4192 ,p_sepchk_run_type_id => p_sepchk_run_type_id
4193 ,p_legislation_code => p_legislation_code
4194 ,p_action_type1 => 'B'
4195 ,p_action_type2 => ''
4196 ,p_action_type3 => '');
4197 end if;
4198 close c_check_baladj;
4199 hr_utility.set_location(gv_package || lv_procedure_name, 50);
4200 ln_step := 20;
4201
4202 EXCEPTION
4203 when others then
4204
4205 lv_error_message := 'Error at step ' || ln_step ||
4206 ' in ' || gv_package || lv_procedure_name;
4207
4208 hr_utility.trace(lv_error_message || '-' || sqlerrm);
4209
4210 lv_error_message :=
4211 pay_emp_action_arch.set_error_message(lv_error_message);
4212
4213 hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
4214 hr_utility.set_message_token('FORMULA_TEXT', lv_error_message);
4215 hr_utility.raise_error;
4216
4217 END process_baladj_elements;
4218
4219 Procedure Archive_retro_element (
4220 p_xfr_action_id in number
4221 ,p_assignment_id in number
4222 ,p_pymt_assignment_action_id in number
4223 ,p_pymt_eff_date in date
4224 ,p_element_type_id in number
4225 ,p_primary_balance_id in number
4226 ,p_hours_balance_id in number
4227 ,p_processing_priority in number
4228 ,p_element_classification_name in varchar2
4229 ,p_reporting_name in varchar2
4230 ,p_tax_unit_id in number
4231 ,p_ytd_balcall_aaid in number
4235 ,p_sepchk_run_type_id in number
4232 ,p_pymt_balcall_aaid in number
4233 ,p_legislation_code in varchar2
4234 ,p_sepchk_flag in varchar2
4236 ,p_action_type in varchar2
4237 ,p_run_assignment_action_id in number
4238 ,p_multiple in number
4239 ,p_rate in number
4240 ,p_retro_base IN VARCHAR2 DEFAULT 'N'
4241 )
4242 IS
4243 /*
4244 TYPE retro_rec_typ IS RECORD(original_dt_earned DATE
4245 ,original_st_dt DATE
4246 ,original_end_dt DATE
4247 ,category VARCHAR2(100)
4248 ,jurisdiction VARCHAR2(20)
4249 ,hours NUMBER
4250 ,ytd_hrs NUMBER
4251 ,amount NUMBER
4252 ,ytd_amt NUMBER
4253 );
4254 TYPE retro_tab_typ IS TABLE OF retro_rec_typ INDEX BY BINARY_INTEGER;
4255 */
4256 CURSOR archive_retro_elements ( cp_element_entry_id in number ,
4257 cp_run_assignment_action_id in number ) IS
4258
4259 select fnd_date.date_to_canonical(pay_paywsmee_pkg.get_original_date_earned(cp_element_entry_id)) ,
4260 fnd_date.date_to_canonical(ptp.start_date),
4261 fnd_date.date_to_canonical(ptp.end_date),
4262 hr_general.decode_lookup
4263 (DECODE (UPPER (ec.classification_name),
4264 'EARNINGS', 'US_EARNINGS',
4265 'SUPPLEMENTAL EARNINGS', 'US_SUPPLEMENTAL_EARNINGS',
4266 'IMPUTED EARNINGS', 'US_IMPUTED_EARNINGS',
4267 'NON-PAYROLL PAYMENTS', 'US_PAYMENT',
4268 'ALIEN/EXPAT EARNINGS', 'PER_US_INCOME_TYPES',
4269 NULL
4270 ),
4271 et.element_information1
4272 ) CATEGORY
4273 from pay_element_entries_f peef,
4274 per_time_periods ptp,
4275 pay_payroll_actions ppa,
4276 pay_assignment_actions paa,
4277 pay_element_types_f et,
4278 pay_element_classifications ec
4279 where peef.element_entry_id = cp_element_entry_id
4280 AND peef.creator_type IN ('EE', 'NR', 'PR', 'R', 'RR')
4281 AND et.element_type_id = peef.element_type_id
4282 AND et.classification_id = ec.classification_id
4283 AND paa.assignment_action_id = cp_run_assignment_action_id
4284 AND ppa.payroll_action_id = paa.payroll_action_id
4285 AND ptp.payroll_id = ppa.payroll_id
4286 AND pay_paywsmee_pkg.get_original_date_earned(cp_element_entry_id)
4287 BETWEEN ptp.start_date
4288 AND ptp.end_date ;
4289
4290 CURSOR get_element_entry_id(cp_run_action_id in number ,
4291 cp_assignment_id in number ,
4292 cp_element_type_id in number ) IS
4293 SELECT peef.element_entry_id,
4294 peef.creator_type,
4295 peef.source_start_date
4296 FROM pay_element_entries_f peef,
4297 pay_assignment_actions paa,
4298 pay_payroll_actions ppa,
4299 per_time_periods ptp
4300 WHERE paa.assignment_action_id = cp_run_action_id
4301 AND ppa.payroll_action_id = paa.payroll_action_id
4302 AND ptp.payroll_id = ppa.payroll_id
4303 AND ppa.date_earned BETWEEN ptp.start_date AND ptp.end_date
4304 AND peef.assignment_id = cp_assignment_id
4305 AND peef.creator_id is NOT NULL
4306 AND peef.element_type_id = cp_element_type_id
4307
4308 /* Commenting as Ele Entry Eff Start / End Date may not match the following
4309 AND peef.effective_start_date BETWEEN ptp.start_date AND ptp.end_date
4310 AND peef.effective_end_date BETWEEN ptp.start_date AND ptp.end_date
4311 End of Comment */
4312
4313 AND NVL(ppa.date_earned, ppa.effective_date) BETWEEN peef.effective_start_date AND peef.effective_end_date
4314
4315 ORDER BY 3;
4316
4317
4318 CURSOR get_run_results ( cp_element_entry_id in number ) IS
4319 SELECT to_number(prrv.result_value), pivf.NAME
4320 FROM pay_run_results prr,
4321 pay_run_result_values prrv,
4322 pay_input_values_f pivf
4323 WHERE prr.element_entry_id = cp_element_entry_id
4324 AND prrv.run_result_id = prr.run_result_id
4325 AND prrv.input_value_id = pivf.input_value_id
4326 AND pivf.NAME IN ('Pay Value', 'Hours')
4327 ORDER BY 2 ;
4328
4329 -- Introducing This Cussor in case Hours and Pay Values Both zero
4330 --
4331 CURSOR get_run_results_rate( cp_element_entry_id in number ) IS
4332 SELECT to_number(prrv.result_value)
4333 FROM pay_run_results prr,
4334 pay_run_result_values prrv,
4335 pay_input_values_f pivf
4336 WHERE prr.element_entry_id = cp_element_entry_id
4337 AND prrv.run_result_id = prr.run_result_id
4338 AND prrv.input_value_id = pivf.input_value_id
4339 AND pivf.NAME IN ('Rate');
4340
4344 SELECT NVL(paf.work_at_home, 'N')
4341 -- Added For Work At Home Condition
4342
4343 CURSOR c_cur_get_wrkathome(cp_assignment_id IN NUMBER) IS
4345 ,ppf.person_id
4346 ,ppf.business_group_id
4347 FROM per_assignments_f paf
4348 ,per_all_people_f ppf
4349 WHERE paf.assignment_id = cp_assignment_id
4350 AND paf.person_id = ppf.person_id;
4351
4352 CURSOR c_cur_home_state_jd(cp_person_id IN NUMBER
4353 ,cp_bg_id IN NUMBER) IS
4354 SELECT pus.state_code || '-000-0000'
4355 FROM per_addresses pa
4356 ,pay_us_states pus
4357 WHERE pa.person_id = cp_person_id
4358 AND pa.primary_flag = 'Y'
4359 AND p_pymt_eff_date between pa.date_from AND NVL(pa.date_to, hr_general.END_OF_TIME)
4360 AND pa.business_group_id = cp_bg_id
4361 AND pa.region_2 = pus.state_abbrev
4362 AND pa.style = p_legislation_code;
4363
4364 --retro_tab retro_tab_typ;
4365 --retro_refined_tab retro_tab_typ;
4366 --cnt NUMBER;
4367 --k NUMBER;
4368 --k_match_cnt NUMBER;
4369 lv_original_date_earned varchar2(100);
4370 lv_effective_start_date varchar2(100);
4371 lv_effective_end_date varchar2(100);
4372 lv_category varchar2(100);
4373 lv_el_jurisdiction_code varchar2(100);
4374 ln_final_rate number;
4375 ln_element_entry_id number;
4376 lv_creator_type varchar2(100);
4377 lv_jurisdiction_flag varchar2(20);
4378 ln_element_index number;
4379 ln_multiple number;
4380 ln_rate number(10,2);
4381 ln_current_hours NUMBER(15,5);
4382 ln_payments_amount NUMBER(15,5);
4383 ln_ytd_hours NUMBER(15,5) := 0;
4384 ln_ytd_amount NUMBER(17,5) := 0;
4385
4386 ln_pymt_defined_balance_id NUMBER;
4387 ln_pymt_hours_balance_id NUMBER;
4388 ln_ytd_defined_balance_id NUMBER;
4389 ln_ytd_hours_balance_id NUMBER;
4390
4391 lv_rate_exists VARCHAR2(1) := 'N';
4392 ln_nonpayroll_balcall_aaid NUMBER;
4393
4394 ln_index NUMBER ;
4395 lv_procedure_name VARCHAR2(100):= '.Archive_retro_element';
4396 lv_error_message VARCHAR2(200);
4397
4398 ln_step NUMBER;
4399 ln_final_ytd_value NUMBER(15,5);
4400 ld_source_start_date DATE;
4401 lv_action_category varchar2(100);
4402 lv_pay_value_name varchar2(100);
4403 ln_pay_value number (15,5);
4404 ln_hours number(15,5) ;
4405 ln_amount number(15,5);
4406 -- Added For Work At Home Condition
4407 lv_wrk_at_home per_assignments_f.work_at_home%TYPE;
4408 ln_person_id per_people_f.person_id%TYPE;
4409 ln_bg_id per_people_f.business_group_id%TYPE;
4410
4411 BEGIN
4412
4413 hr_utility.trace('Entering in package Archive_retro_element');
4414 hr_utility.trace('Run_assifnment_action_id = ' || p_run_assignment_action_id) ;
4415 OPEN get_element_entry_id ( p_run_assignment_action_id,
4416 p_assignment_id,
4417 p_element_type_id);
4418 ln_step := 50;
4419
4420 ln_ytd_hours := 0;
4421 ln_ytd_amount := 0;
4422 ln_hours := 0;
4423 ln_amount := 0;
4424 -- cnt := 0;
4425 LOOP -- For Each Ele Entry created by Retro
4426
4427 FETCH get_element_entry_id INTO ln_element_entry_id,
4428 lv_creator_type,
4429 ld_source_start_date;
4430
4431 IF get_element_entry_id%NOTFOUND THEN
4432 close get_element_entry_id;
4433 EXIT;
4434 END IF;
4435 hr_utility.trace('Step 50 : ln_element_entry_id := '||ln_element_entry_id);
4436 hr_utility.trace('Step 50 : lv_creator_type := '||lv_creator_type);
4437 hr_utility.trace('Step 50 : ld_source_start_date := '||ld_source_start_date);
4438
4439 OPEN get_run_results ( ln_element_entry_id );
4440 LOOP
4441 ln_step := 49;
4442 FETCH get_run_results INTO ln_pay_value ,
4443 lv_pay_value_name;
4444 IF get_run_results%FOUND THEN
4445 IF lv_pay_value_name = 'Hours' THEN
4446 ln_ytd_hours := ln_ytd_hours + nvl(ln_pay_value,0) ;
4447 ln_hours := nvl(ln_pay_value,0);
4448 hr_utility.trace('ln_hours := '||ln_hours);
4449 hr_utility.trace('ln_ytd_hours is '|| ln_ytd_hours );
4450
4451 END IF ;
4452
4453 IF lv_pay_value_name = 'Pay Value' THEN
4454 ln_ytd_amount := ln_ytd_amount + nvl(ln_pay_value,0) ;
4455 ln_amount := nvl(ln_pay_value,0) ;
4456 hr_utility.trace('ln_amount := '||ln_amount);
4457 hr_utility.trace('ln_ytd_amount is '|| ln_ytd_amount );
4458 END IF;
4459 ELSE
4460 EXIT;
4461 END IF;
4462 END LOOP; -- Run Results
4463
4464 ln_step := 48;
4465
4466 IF get_run_results%ISOPEN THEN
4467 CLOSE get_run_results ;
4468 END IF;
4469
4470 IF ln_hours = 0 THEN
4474 CLOSE get_run_results_rate;
4471 IF ln_amount = 0 THEN
4472 OPEN get_run_results_rate(ln_element_entry_id);
4473 FETCH get_run_results_rate INTO ln_rate;
4475 ELSE
4476 ln_rate := NULL;
4477 END IF;
4478 ELSE
4479 ln_rate := ln_amount/ln_hours;
4480 END IF;
4481
4482 hr_utility.trace('Before Opening Cursor archive_retro_elements');
4483 OPEN archive_retro_elements ( ln_element_entry_id ,
4484 p_run_assignment_action_id );
4485 FETCH archive_retro_elements INTO lv_original_date_earned
4486 ,lv_effective_start_date
4487 ,lv_effective_end_date
4488 ,lv_category;
4489 CLOSE archive_retro_elements ;
4490 hr_utility.trace('After Closing Cursor archive_retro_elements');
4491 hr_utility.trace('lv_original_date_earned := '||lv_original_date_earned);
4492 hr_utility.trace('lv_effective_start_date := '||lv_effective_start_date);
4493 hr_utility.trace('lv_effective_end_date := '||lv_effective_end_date);
4494 hr_utility.trace('lv_category := '||lv_category);
4495
4496 -- Added For Work At Home Condition
4497 OPEN c_cur_get_wrkathome(p_assignment_id);
4498 FETCH c_cur_get_wrkathome INTO lv_wrk_at_home
4499 ,ln_person_id
4500 ,ln_bg_id;
4501 CLOSE c_cur_get_wrkathome;
4502
4503 IF lv_wrk_at_home = 'Y' THEN
4504 OPEN c_cur_home_state_jd(ln_person_id
4505 ,ln_bg_id);
4506 FETCH c_cur_home_state_jd INTO lv_jurisdiction_flag;
4507 CLOSE c_cur_home_state_jd;
4508 ELSE
4509
4510 SELECT nvl((select peevf.screen_entry_value jurisdiction_code
4511 from pay_input_values_f pivf,
4512 pay_element_entry_values_f peevf
4513 where pivf.element_type_id = p_element_type_id
4514 AND pivf.NAME = 'Jurisdiction'
4515 AND peevf.element_entry_id = ln_element_entry_id
4516 AND pivf.input_value_id = peevf.input_value_id),(SELECT distinct pus.state_code
4517 || '-'
4518 || puc.county_code
4519 || '-'
4520 || punc.city_code jurisdiction_code
4521 FROM per_all_assignments_f peaf,
4522 hr_locations_all hla,
4523 pay_us_states pus,
4524 pay_us_counties puc,
4525 pay_us_city_names punc,
4526 pay_assignment_actions paa,
4527 pay_payroll_actions ppa
4528 WHERE peaf.assignment_id = p_assignment_id
4529 AND paa.assignment_action_id = p_run_assignment_action_id
4530 AND peaf.location_id = hla.location_id
4531 AND hla.region_2 = pus.state_abbrev
4532 AND pus.state_code = puc.state_code
4533 AND hla.region_1 = puc.county_name
4534 AND hla.town_or_city = punc.city_name
4535 AND pus.state_code = punc.state_code
4536 AND puc.county_code = punc.county_code
4537 AND ppa.payroll_action_id = paa.payroll_action_id
4538 AND ppa.effective_date between peaf.effective_start_date and peaf.effective_end_date
4539 ))
4540 into lv_jurisdiction_flag
4541 from dual;
4542 END IF; -- Work At Home 'N'
4543
4544 -- populating temporary plsql table
4545 --
4546 hr_utility.trace('lv_jurisdiction_flag := '||lv_jurisdiction_flag);
4547 -- Comment Starts From Here
4548 -- Comment Till Here
4549
4550 lv_action_category := 'AC DEDUCTIONS';
4551
4552 ln_step := 15;
4553 ln_index := pay_ac_action_arch.lrr_act_tab.count;
4554
4555 if p_element_classification_name in ('Earnings',
4556 'Supplemental Earnings',
4557 'Taxable Benefits',
4558 'Imputed Earnings',
4559 'Non-payroll Payments',
4560 'Alien/Expat Earnings') then
4561
4562 lv_action_category := 'AC EARNINGS';
4563
4564 pay_ac_action_arch.lrr_act_tab(ln_index).act_info11
4565 := fnd_number.number_to_canonical(ln_hours);
4566
4567
4568 -- YTD Hours
4569 pay_ac_action_arch.lrr_act_tab(ln_index).act_info22
4570 := ln_rate;
4571
4572 pay_ac_action_arch.lrr_act_tab(ln_index).act_info12
4573 := fnd_number.number_to_canonical(0);
4574
4575 end if; -- Classification Earnings
4576
4577 hr_utility.set_location(gv_package || lv_procedure_name, 130);
4578
4579 /* Insert this into the plsql table if Current or YTD
4580 amount is not Zero */
4581
4582 ln_step :=21;
4583 pay_ac_action_arch.lrr_act_tab(ln_index).action_info_category
4584 := lv_action_category;
4585 ln_step :=22;
4586 pay_ac_action_arch.lrr_act_tab(ln_index).jurisdiction_code
4587 := '00-000-0000' ;
4588 ln_step :=23;
4589 pay_ac_action_arch.lrr_act_tab(ln_index).action_context_id
4590 := p_xfr_action_id;
4594 ln_step :=25;
4591 ln_step :=24;
4592 pay_ac_action_arch.lrr_act_tab(ln_index).act_info1
4593 := p_element_classification_name;
4595 pay_ac_action_arch.lrr_act_tab(ln_index).act_info2
4596 := p_element_type_id;
4597 pay_ac_action_arch.lrr_act_tab(ln_index).act_info6
4598 := p_primary_balance_id;
4599 pay_ac_action_arch.lrr_act_tab(ln_index).act_info7
4600 := p_processing_priority;
4601 pay_ac_action_arch.lrr_act_tab(ln_index).act_info8
4602 := fnd_number.number_to_canonical(nvl(ln_amount,0));
4603
4604 hr_utility.trace('ln_amount := '||fnd_number.number_to_canonical(nvl(ln_amount,0)));
4605
4606 pay_ac_action_arch.lrr_act_tab(ln_index).act_info10
4607 := p_reporting_name;
4608 IF lv_action_category = 'AC DEDUCTIONS' THEN
4609 pay_ac_action_arch.lrr_act_tab(ln_index).act_info24
4610 := p_reporting_name;
4611 END IF;
4612 pay_ac_action_arch.lrr_act_tab(ln_index).act_info17
4613 := lv_original_date_earned;
4614 hr_utility.trace('lv_original_date_earned :=' || lv_original_date_earned );
4615 pay_ac_action_arch.lrr_act_tab(ln_index).act_info18
4616 := lv_effective_start_date;
4617 hr_utility.trace('lv_effective_start_date := ' || lv_effective_start_date );
4618 pay_ac_action_arch.lrr_act_tab(ln_index).act_info19
4619 := lv_effective_end_date ;
4620 hr_utility.trace('lv_effective_end_date:= ' || lv_effective_end_date );
4621 pay_ac_action_arch.lrr_act_tab(ln_index).act_info20
4622 := lv_category;
4623 hr_utility.trace('lv_category ' || lv_category );
4624 pay_ac_action_arch.lrr_act_tab(ln_index).act_info21
4625 := lv_jurisdiction_flag;
4626 pay_ac_action_arch.lrr_act_tab(ln_index).act_info9
4627 := fnd_number.number_to_canonical(0);
4628
4629 hr_utility.set_location(gv_package || lv_procedure_name, 150);
4630
4631 ln_step := 20;
4632 end loop;
4633 --
4634 -- If For this Element ONLY Retro Entries Exist
4635 -- OR It is Retro + Base Case
4636
4637 IF p_retro_base = 'N' THEN
4638
4639 /* Code added for doing balance call for YTD
4640 This is a Case where Element DOES NOT have Base Entry
4641 BUT ONLY Retro Entries */
4642
4643 if pay_emp_action_arch.gv_multi_leg_rule is null then
4644 pay_emp_action_arch.gv_multi_leg_rule
4645 := pay_emp_action_arch.get_multi_legislative_rule(
4646 p_legislation_code);
4647 end if;
4648
4649 pay_balance_pkg.set_context('JURISDICTION_CODE', NULL);
4650
4651 if gv_reporting_level = 'TAXGRP' then
4652 gv_ytd_balance_dimension := gv_dim_asg_tg_ytd;
4653 else
4654 gv_ytd_balance_dimension := gv_dim_asg_gre_ytd;
4655 end if;
4656
4657
4658 ln_ytd_defined_balance_id
4659 := pay_emp_action_arch.get_defined_balance_id(
4660 p_primary_balance_id,
4661 gv_ytd_balance_dimension,
4662 p_legislation_code);
4663
4664 hr_utility.trace('ln_ytd_defined_balance_id = ' ||
4665 ln_ytd_defined_balance_id);
4666
4667 if ln_ytd_defined_balance_id is not null then
4668 ln_ytd_amount := nvl(pay_balance_pkg.get_value(
4669 ln_ytd_defined_balance_id,
4670 p_ytd_balcall_aaid),0);
4671 end if;
4672 if p_hours_balance_id is not null then
4673 hr_utility.set_location(gv_package || lv_procedure_name, 20);
4674 ln_ytd_hours_balance_id
4675 := pay_emp_action_arch.get_defined_balance_id(
4676 p_hours_balance_id,
4677 gv_ytd_balance_dimension,
4678 p_legislation_code);
4679
4680 hr_utility.trace('ln_ytd_hours_balance_id = ' ||
4681 ln_ytd_hours_balance_id);
4682
4683 if ln_ytd_hours_balance_id is not null then
4684 ln_ytd_hours := nvl(pay_balance_pkg.get_value(
4685 ln_ytd_hours_balance_id,
4686 p_ytd_balcall_aaid),0);
4687 hr_utility.set_location(gv_package || lv_procedure_name, 60);
4688 end if;
4689 end if;
4690
4691 pay_ac_action_arch.lrr_act_tab(ln_index).act_info9
4692 := fnd_number.number_to_canonical(nvl(ln_ytd_amount,0));
4693 hr_utility.trace('ln_ytd_amount' || ln_ytd_amount);
4694
4695 if pay_ac_action_arch.lrr_act_tab(ln_index).action_info_category
4696 = 'AC EARNINGS' then
4697
4698 pay_ac_action_arch.lrr_act_tab(ln_index).act_info12
4699 := fnd_number.number_to_canonical(ln_ytd_hours);
4700 hr_utility.trace('ln_ytd_hours' || ln_ytd_hours);
4701
4702 end if;
4703 -- End Addition
4704 ELSE
4705 -- Global Variable Setting Needed Here
4706 -- That Can be Subtracted during Base Population
4707 --
4708 pay_ac_action_arch.lrr_act_tab(ln_index).act_info9
4712
4709 := fnd_number.number_to_canonical(nvl(ln_ytd_amount,0));
4710 hr_utility.trace('ln_ytd_amount' || ln_ytd_amount);
4711 gv_ytd_amount := ln_ytd_amount ;
4713 pay_ac_action_arch.lrr_act_tab(ln_index).act_info12
4714 := fnd_number.number_to_canonical(ln_ytd_hours);
4715 gv_ytd_hour := ln_ytd_hours ;
4716
4717 hr_utility.trace('ln_ytd_hours' || ln_ytd_hours);
4718
4719 END IF; -- p_retro_base 'Y'
4720
4721 EXCEPTION
4722 when others then
4723 hr_utility.trace(' Error In archive_retro_elements procedure');
4724 hr_utility.trace('error occured at step ' || ln_step );
4725
4726 END Archive_retro_element;
4727
4728 Procedure Archive_addnl_elements (p_application_column_name in varchar2
4729 ,p_xfr_action_id in number
4730 ,p_assignment_id in number
4731 ,p_pymt_assignment_action_id in number
4732 ,p_pymt_eff_date in date
4733 ,p_element_type_id in number
4734 ,p_primary_balance_id in number
4735 ,p_hours_balance_id in number
4736 ,p_processing_priority in number
4737 ,p_element_classification_name in varchar2
4738 ,p_reporting_name in varchar2
4739 ,p_tax_unit_id in number
4740 ,p_ytd_balcall_aaid in number
4741 ,p_pymt_balcall_aaid in number
4742 ,p_legislation_code in varchar2
4743 ,p_sepchk_flag in varchar2
4744 ,p_sepchk_run_type_id in number
4745 ,p_action_type in varchar2
4746 ,p_run_assignment_action_id in number
4747 ,p_multiple in number
4748 ,p_rate in number
4749 )
4750 IS
4751 CURSOR archive_non_retro_elements ( cp_original_date_paid in varchar2,
4752 cp_element_entry_id in number,
4753 cp_run_assignment_action_id in number ) IS
4754
4755 select fnd_date.date_to_canonical(ptp.start_date),
4756 fnd_date.date_to_canonical(ptp.end_date),
4757 hr_general.decode_lookup
4758 (DECODE (UPPER (ec.classification_name),
4759 'EARNINGS', 'US_EARNINGS',
4760 'SUPPLEMENTAL EARNINGS', 'US_SUPPLEMENTAL_EARNINGS',
4761 'IMPUTED EARNINGS', 'US_IMPUTED_EARNINGS',
4762 'NON-PAYROLL PAYMENTS', 'US_PAYMENT',
4763 'ALIEN/EXPAT EARNINGS', 'PER_US_INCOME_TYPES',
4764 NULL
4765 ),
4766 et.element_information1
4767 ) CATEGORY
4768 from pay_assignment_actions paa,
4769 pay_payroll_actions ppa,
4770 per_time_periods ptp,
4771 pay_element_entries_f peef,
4772 pay_element_classifications ec,
4773 pay_element_types et
4774 where paa.assignment_action_id = cp_run_assignment_action_id
4775 and paa.payroll_action_id = ppa.payroll_action_id
4776 and ptp.payroll_id = ppa.payroll_id
4777 and nvl(cp_original_date_paid,ptp.start_date) between ptp.start_date AND ptp.end_date
4778 and peef.element_entry_id = cp_element_entry_id
4779 and et.element_type_id = peef.element_type_id
4780 and et.classification_id = ec.classification_id;
4781
4782 Cursor get_element_entry_id( cp_run_action_id in number ,
4783 cp_assignment_id in number ,
4784 cp_element_type_id in number ) IS
4785 select peef.element_entry_id,
4786 peef.creator_type,
4787 peef.source_start_date
4788 FROM pay_element_entries_f peef,
4789 pay_assignment_actions paa,
4790 pay_payroll_actions ppa,
4791 per_time_periods ptp
4792 WHERE paa.assignment_action_id = cp_run_action_id
4793 AND ppa.payroll_action_id = paa.payroll_action_id
4794 AND ptp.payroll_id = ppa.payroll_id
4795 AND ppa.date_earned BETWEEN ptp.start_date AND ptp.end_date
4796 AND peef.assignment_id = cp_assignment_id
4797 AND peef.element_type_id = cp_element_type_id
4798
4799 /* Commenting as Ele Entry Eff Start / End Date may not match the following
4800 AND peef.effective_start_date BETWEEN ptp.start_date AND ptp.end_date
4801 AND peef.effective_end_date BETWEEN ptp.start_date AND ptp.end_date
4802 End of Comment */
4803
4804 AND NVL(ppa.date_earned, ppa.effective_date) BETWEEN peef.effective_start_date AND peef.effective_end_date
4805
4806 --ORDER BY 3;
4807 ORDER BY nvl(peef.attribute_category,'Z'), peef.element_entry_id ;
4808
4809 --bug 7373188
4810 --CURSOR get_run_results ( cp_element_entry_id in number ) IS
4811 CURSOR get_run_results ( cp_run_action_id in number ,cp_element_entry_id in number ) IS
4812 --bug 7373188
4813 SELECT to_number(prrv.result_value), pivf.NAME
4817 WHERE prr.element_entry_id = cp_element_entry_id
4814 FROM pay_run_results prr,
4815 pay_run_result_values prrv,
4816 pay_input_values_f pivf
4818 --bug 7373188
4819 and prr.assignment_action_id = cp_run_action_id
4820 --bug 7373188
4821 AND prrv.run_result_id = prr.run_result_id
4822 AND prrv.input_value_id = pivf.input_value_id
4823 AND pivf.NAME IN ('Pay Value', 'Hours')
4824 ORDER BY 2 ;
4825 -- Introducing This Cussor in case Hours and Pay Values Both zero
4826 --
4827 --bug 7373188
4828 --CURSOR get_run_results_rate( cp_element_entry_id in number ) IS
4829 CURSOR get_run_results_rate( cp_run_action_id in number , cp_element_entry_id in number ) IS
4830 --bug 7373188
4831 SELECT to_number(prrv.result_value)
4832 FROM pay_run_results prr,
4833 pay_run_result_values prrv,
4834 pay_input_values_f pivf
4835 WHERE prr.element_entry_id = cp_element_entry_id
4836 --bug 7373188
4837 and prr.assignment_action_id = cp_run_action_id
4838 --bug 7373188
4839 AND prrv.run_result_id = prr.run_result_id
4840 AND prrv.input_value_id = pivf.input_value_id
4841 AND pivf.NAME IN ('Rate');
4842
4843 -- Added For Work At Home Condition
4844
4845 CURSOR c_cur_get_wrkathome(cp_assignment_id IN NUMBER) IS
4846 SELECT NVL(paf.work_at_home, 'N')
4847 ,ppf.person_id
4848 ,ppf.business_group_id
4849 FROM per_assignments_f paf
4850 ,per_all_people_f ppf
4851 WHERE paf.assignment_id = cp_assignment_id
4852 AND paf.person_id = ppf.person_id;
4853
4854 CURSOR c_cur_home_state_jd(cp_person_id IN NUMBER
4855 ,cp_bg_id IN NUMBER) IS
4856 SELECT pus.state_code || '-000-0000'
4857 FROM per_addresses pa
4858 ,pay_us_states pus
4859 WHERE pa.person_id = cp_person_id
4860 AND pa.primary_flag = 'Y'
4861 AND p_pymt_eff_date between pa.date_from AND NVL(pa.date_to, hr_general.END_OF_TIME)
4862 AND pa.business_group_id = cp_bg_id
4863 AND pa.region_2 = pus.state_abbrev
4864 AND pa.style = p_legislation_code;
4865
4866 lv_original_date_earned varchar2(100);
4867 lv_effective_start_date varchar2(100);
4868 lv_effective_end_date varchar2(100);
4869 lv_category varchar2(100);
4870 lv_el_jurisdiction_code varchar2(100);
4871 ln_final_rate number;
4872 ln_element_entry_id number;
4873 lv_creator_type varchar2(100);
4874 lv_jurisdiction_flag varchar2(20);
4875 ln_element_index number;
4876 ln_multiple number;
4877 ln_rate number(10,2);
4878 ln_current_hours NUMBER(15,5);
4879 ln_payments_amount NUMBER(15,5);
4880 ln_ytd_hours NUMBER(15,5) := 0;
4881 ln_ytd_amount NUMBER(17,5) := 0;
4882
4883 ln_pymt_defined_balance_id NUMBER;
4884 ln_pymt_hours_balance_id NUMBER;
4885 ln_ytd_defined_balance_id NUMBER;
4886 ln_ytd_hours_balance_id NUMBER;
4887
4888 lv_rate_exists VARCHAR2(1) := 'N';
4889 ln_nonpayroll_balcall_aaid NUMBER;
4890
4891 ln_index NUMBER ;
4892 lv_procedure_name VARCHAR2(100):= '.Archive_addnl_elements';
4893 lv_error_message VARCHAR2(200);
4894
4895 ln_step NUMBER;
4896 ln_final_ytd_value NUMBER(15,5);
4897 ld_source_start_date DATE;
4898 lv_action_category varchar2(100);
4899 lv_pay_value_name varchar2(100);
4900 ln_pay_value number (15,5);
4901 ln_hours number(15,5) ;
4902 ln_amount number(15,5);
4903 ld_original_date_paid date;
4904 count_j number := null;
4905 result number := 0;
4906 lv_sqlstr varchar2(500);
4907 lv_check_date varchar2(100);
4908 ld_check_date date;
4909 lv_sqlstr1 varchar2(2500);
4910 lv_temp_AAA varchar2(100) :='BBB';
4911 lv_sqlstr2 varchar2(2500);
4912 lv_sqlstr3 varchar2(2500);
4913 lv_sqlstr4 varchar2(2500);
4914 lv_sqlstr_final varchar2(2500);
4915
4916 -- Added For Work At Home Condition
4917 lv_wrk_at_home per_assignments_f.work_at_home%TYPE;
4918 ln_person_id per_people_f.person_id%TYPE;
4919 ln_bg_id per_people_f.business_group_id%TYPE;
4920
4921 BEGIN
4922
4923 hr_utility.trace('Entering in package Archive_addnl_elements');
4924 hr_utility.trace('Run_assifnment_action_id = ' || p_run_assignment_action_id) ;
4925 hr_utility.trace('Element Type Id in Non retro Archiver ' || p_element_type_id);
4926 OPEN get_element_entry_id ( p_run_assignment_action_id,
4927 p_assignment_id,
4928 p_element_type_id);
4929 ln_step := 50;
4930
4931 ln_ytd_hours := 0;
4932 ln_amount := 0;
4933
4934 lv_sqlstr1 := 'select max(nvl(peef.' || p_application_column_name ||', ptp.start_date)) FROM pay_element_entries_f peef, pay_assignment_actions paa, pay_payroll_actions ppa,per_time_periods ptp WHERE paa.assignment_action_id =' ;
4935 lv_sqlstr2 := p_run_assignment_action_id ;
4936 lv_sqlstr3 :='AND ppa.payroll_action_id = paa.payroll_action_id AND ptp.payroll_id = ppa.payroll_id AND ppa.date_earned BETWEEN ptp.start_date AND ptp.end_date AND peef.assignment_id =' || p_assignment_id ||'AND peef.element_type_id =' ;
4940 execute immediate lv_sqlstr_final into lv_check_date ;
4937 lv_sqlstr4 := p_element_type_id ||' AND NVL(ppa.date_earned, ppa.effective_date) BETWEEN peef.effective_start_date AND peef.effective_end_date AND peef.' || p_application_column_name || ' is not null ' ;
4938 lv_sqlstr_final := lv_sqlstr1 || lv_sqlstr2 || lv_sqlstr3 || lv_sqlstr4 ;
4939
4941 ln_step :=51;
4942 hr_utility.trace('lv_check_date == ' || lv_check_date);
4943 LOOP
4944
4945 FETCH get_element_entry_id INTO ln_element_entry_id,
4946 lv_creator_type,
4947 ld_source_start_date;
4948 ln_step :=52;
4949 IF get_element_entry_id%NOTFOUND THEN
4950 close get_element_entry_id;
4951 ln_step :=53;
4952 EXIT;
4953 END IF;
4954 ln_step :=53;
4955 lv_sqlstr := 'select nvl(' || p_application_column_name ||
4956 ',''AAA'') from pay_element_entries_f where element_entry_id = ' || ln_element_entry_id;
4957 execute immediate lv_sqlstr
4958 into lv_original_date_earned ;
4959
4960 IF lv_original_date_earned = 'AAA' THEN
4961 lv_original_date_earned := fnd_date.date_to_canonical( p_pymt_eff_date);
4965
4962 lv_temp_AAA:= 'AAA' ;
4963 END IF;
4964 lv_original_date_earned := nvl(lv_original_date_earned ,p_pymt_eff_date);
4966
4967 hr_utility.trace('lv_original_date_earned in step 53 is' || lv_original_date_earned) ;
4968
4969 ld_original_date_paid := fnd_date.canonical_to_date(lv_original_date_earned);
4970
4971 ln_step :=54;
4972 ln_hours := 0;
4973 ln_amount := 0;
4974 --bug 7373188
4975 -- OPEN get_run_results ( ln_element_entry_id );
4976 OPEN get_run_results ( p_run_assignment_action_id,ln_element_entry_id );
4977 --bug 7373188
4978 LOOP
4979 ln_step := 49;
4980 FETCH get_run_results INTO ln_pay_value ,
4981 lv_pay_value_name;
4982 IF get_run_results%FOUND THEN
4983 IF lv_pay_value_name = 'Hours' THEN
4984 ln_hours := ln_hours + ln_pay_value;
4985 hr_utility.trace('ln_ytd_hours is '|| ln_ytd_hours );
4986 END IF ;
4987
4988 IF lv_pay_value_name = 'Pay Value' THEN
4989 ln_amount := ln_amount + ln_pay_value ;
4990 hr_utility.trace('ln_ytd_amount is '|| ln_ytd_amount );
4991 END IF;
4992 ELSE
4993 EXIT;
4994 END IF;
4995 END LOOP;
4996
4997 ln_step := 48;
4998 IF get_run_results%ISOPEN then
4999 CLOSE get_run_results ;
5000 END IF;
5001
5002 IF ln_hours = 0 THEN
5003 IF ln_amount = 0 THEN
5004 --bug 7373188
5005 -- OPEN get_run_results_rate(ln_element_entry_id);
5006 OPEN get_run_results_rate(p_run_assignment_action_id,ln_element_entry_id);
5007 --bug 7373188
5008 FETCH get_run_results_rate INTO ln_rate;
5009 CLOSE get_run_results_rate;
5010 ELSE
5011 ln_rate := NULL;
5012 END IF;
5013 ELSE
5014 ln_rate := ln_amount/ln_hours;
5015 END IF;
5016
5017 hr_utility.trace('ld_original_date_paid := '||ld_original_date_paid);
5018 hr_utility.trace('ln_element_entry_id := '||ln_element_entry_id);
5019 hr_utility.trace('p_run_assignment_action_id := '||p_run_assignment_action_id);
5020
5021 OPEN archive_non_retro_elements ( ld_original_date_paid,
5022 ln_element_entry_id ,
5023 p_run_assignment_action_id );
5024 FETCH archive_non_retro_elements INTO lv_effective_start_date
5025 ,lv_effective_end_date
5026 ,lv_category;
5027 CLOSE archive_non_retro_elements ;
5028 hr_utility.trace('lv_effective_start_date := '||lv_effective_start_date);
5029 hr_utility.trace('lv_effective_end_date := '||lv_effective_end_date);
5030 hr_utility.trace('lv_category := '||lv_category);
5031
5032 -- Added For Work At Home Condition
5033 OPEN c_cur_get_wrkathome(p_assignment_id);
5034 FETCH c_cur_get_wrkathome INTO lv_wrk_at_home
5035 ,ln_person_id
5036 ,ln_bg_id;
5037 CLOSE c_cur_get_wrkathome;
5038 IF lv_wrk_at_home = 'Y' THEN
5039 OPEN c_cur_home_state_jd(ln_person_id
5040 ,ln_bg_id);
5041 FETCH c_cur_home_state_jd INTO lv_jurisdiction_flag;
5042 CLOSE c_cur_home_state_jd;
5043 ELSE
5044
5045 SELECT nvl((select peevf.screen_entry_value jurisdiction_code
5046 from pay_input_values_f pivf,
5047 pay_element_entry_values_f peevf
5048 where pivf.element_type_id = p_element_type_id
5049 AND pivf.NAME = 'Jurisdiction'
5050 AND peevf.element_entry_id = ln_element_entry_id
5051 AND pivf.input_value_id = peevf.input_value_id),(SELECT distinct pus.state_code
5052 || '-'
5053 || puc.county_code
5054 || '-'
5055 || punc.city_code jurisdiction_code
5056 FROM per_all_assignments_f peaf,
5057 hr_locations_all hla,
5058 pay_us_states pus,
5059 pay_us_counties puc,
5060 pay_us_city_names punc,
5061 pay_assignment_actions paa,
5062 pay_payroll_actions ppa
5063 WHERE peaf.assignment_id = p_assignment_id
5064 AND paa.assignment_action_id = p_run_assignment_action_id
5065 AND peaf.location_id = hla.location_id
5066 AND hla.region_2 = pus.state_abbrev
5067 AND pus.state_code = puc.state_code
5068 AND hla.region_1 = puc.county_name
5069 AND hla.town_or_city = punc.city_name
5070 AND pus.state_code = punc.state_code
5071 AND puc.county_code = punc.county_code
5072 AND ppa.payroll_action_id = paa.payroll_action_id
5073 AND ppa.effective_date between peaf.effective_start_date and peaf.effective_end_date
5074 ))
5075 into lv_jurisdiction_flag
5076 from dual;
5077 END IF; -- Work at Home 'N'
5078
5079 hr_utility.trace('lv_jurisdiction_flag := '||lv_jurisdiction_flag);
5080 lv_action_category := 'AC EARNINGS';
5081 ln_step := 15;
5082 ln_index := pay_ac_action_arch.lrr_act_tab.count;
5083
5084 pay_ac_action_arch.lrr_act_tab(ln_index).act_info11
5085 := fnd_number.number_to_canonical(ln_hours);
5086 hr_utility.trace('pay_ac_action_arch.lrr_act_tab(ln_index).act_info11' || pay_ac_action_arch.lrr_act_tab(ln_index).act_info11 );/*Bug 3311866*/
5087 hr_utility.set_location(gv_package || lv_procedure_name, 130);
5091 pay_ac_action_arch.lrr_act_tab(ln_index).action_info_category
5088 /* Insert this into the plsql table if Current or YTD
5089 amount is not Zero */
5090 ln_step :=21;
5092 := lv_action_category;
5093 ln_step :=22;
5094 pay_ac_action_arch.lrr_act_tab(ln_index).jurisdiction_code
5095 := '00-000-0000' ;
5096 ln_step :=23;
5097 pay_ac_action_arch.lrr_act_tab(ln_index).action_context_id
5098 := p_xfr_action_id;
5099 ln_step :=24;
5100 pay_ac_action_arch.lrr_act_tab(ln_index).act_info1
5101 := p_element_classification_name;
5102 ln_step :=25;
5103 pay_ac_action_arch.lrr_act_tab(ln_index).act_info2
5104 := p_element_type_id;
5105 pay_ac_action_arch.lrr_act_tab(ln_index).act_info6
5106 := p_primary_balance_id;
5107 pay_ac_action_arch.lrr_act_tab(ln_index).act_info7
5108 := p_processing_priority;
5109 pay_ac_action_arch.lrr_act_tab(ln_index).act_info8
5110 := fnd_number.number_to_canonical(nvl(ln_amount,0));
5111 pay_ac_action_arch.lrr_act_tab(ln_index).act_info9
5112 := fnd_number.number_to_canonical(0);
5113
5114 hr_utility.trace('ln_amount := '||fnd_number.number_to_canonical(nvl(ln_amount,0)));
5115 pay_ac_action_arch.lrr_act_tab(ln_index).act_info10
5116 := p_reporting_name;
5117
5118 IF lv_temp_AAA <> 'AAA' THEN
5119 pay_ac_action_arch.lrr_act_tab(ln_index).act_info17
5120 := lv_original_date_earned;
5121 hr_utility.trace('lv_original_date_earned :=' || lv_original_date_earned );
5122
5123 IF lv_check_date = nvl(lv_original_date_earned,p_pymt_eff_date) THEN
5124 count_j := ln_index;
5125 END IF;
5126 pay_ac_action_arch.lrr_act_tab(ln_index).act_info18
5127 := lv_effective_start_date;
5128 hr_utility.trace('lv_effective_start_date := ' || lv_effective_start_date );
5129 pay_ac_action_arch.lrr_act_tab(ln_index).act_info19
5130 := lv_effective_end_date ;
5131 hr_utility.trace('lv_effective_end_date:= ' || lv_effective_end_date );
5132 pay_ac_action_arch.lrr_act_tab(ln_index).act_info20
5133 := lv_category;
5134 hr_utility.trace('lv_category ' || lv_category );
5135 pay_ac_action_arch.lrr_act_tab(ln_index).act_info21
5136 := lv_jurisdiction_flag;
5137 END IF;
5138
5139 pay_ac_action_arch.lrr_act_tab(ln_index).act_info22
5140 := ln_rate;
5141 /*
5142 IF pay_ac_action_arch.lrr_act_tab(ln_index).act_info22 IS NULL THEN
5143 pay_ac_action_arch.lrr_act_tab(ln_index).act_info22 := 'N/A';
5144 END IF;
5145 */
5146
5147 hr_utility.set_location(gv_package || lv_procedure_name, 150);
5148 ln_step := 20;
5149 lv_temp_AAA := 'BBB' ;
5150 end loop;
5151
5152 /* Code added for doing balance call for YTD */
5153 if pay_emp_action_arch.gv_multi_leg_rule is null then
5154 pay_emp_action_arch.gv_multi_leg_rule
5155 := pay_emp_action_arch.get_multi_legislative_rule(
5156 p_legislation_code);
5157 end if;
5158
5159 pay_balance_pkg.set_context('JURISDICTION_CODE', null);
5160 if gv_reporting_level = 'TAXGRP' then
5161 gv_ytd_balance_dimension := gv_dim_asg_tg_ytd;
5162 else
5163 gv_ytd_balance_dimension := gv_dim_asg_gre_ytd;
5164 end if;
5165
5166 /*********************************************************
5167 ** Get the defined balance_id for YTD call as it will be
5168 ** same for all classification types.
5169 *********************************************************/
5170 ln_ytd_defined_balance_id
5171 := pay_emp_action_arch.get_defined_balance_id(
5172 p_primary_balance_id,
5173 gv_ytd_balance_dimension,
5174 p_legislation_code);
5175
5176 hr_utility.trace('ln_ytd_defined_balance_id = ' ||
5177 ln_ytd_defined_balance_id);
5178
5179 ln_step := 4;
5180 if p_hours_balance_id is not null then
5181 hr_utility.set_location(gv_package || lv_procedure_name, 20);
5182 ln_ytd_hours_balance_id
5183 := pay_emp_action_arch.get_defined_balance_id(
5184 p_hours_balance_id,
5185 gv_ytd_balance_dimension,
5186 p_legislation_code);
5187
5188 hr_utility.trace('ln_ytd_hours_balance_id = ' ||
5189 ln_ytd_hours_balance_id);
5190
5191 end if;
5192
5193 ln_step := 5;
5194 hr_utility.set_location(gv_package || lv_procedure_name, 40);
5195 if ln_ytd_defined_balance_id is not null then
5196 ln_ytd_amount := nvl(pay_balance_pkg.get_value(
5197 ln_ytd_defined_balance_id,
5198 p_ytd_balcall_aaid),0);
5199 end if;
5200
5201 if p_hours_balance_id is not null then
5202 hr_utility.set_location(gv_package || lv_procedure_name, 50);
5203 if ln_ytd_hours_balance_id is not null then
5204 ln_ytd_hours := nvl(pay_balance_pkg.get_value(
5205 ln_ytd_hours_balance_id,
5209 end if; --Hours
5206 p_ytd_balcall_aaid),0);
5207 hr_utility.set_location(gv_package || lv_procedure_name, 60);
5208 end if;
5210
5211 ln_step := 8;
5212 if p_pymt_balcall_aaid is not null then
5213 ln_step := 10;
5214 /* Added dimension _ASG_GRE_RUN for reversals and Balance
5215 Adjustments for Canada. Bug#3498653 */
5216 if p_action_type in ('B','V') then
5217 ln_pymt_defined_balance_id
5218 := pay_emp_action_arch.get_defined_balance_id(
5219 p_primary_balance_id,
5220 '_ASG_GRE_RUN',
5221 p_legislation_code);
5222 else
5223 if pay_emp_action_arch.gv_multi_leg_rule = 'Y' then
5224 ln_pymt_defined_balance_id
5225 := pay_emp_action_arch.get_defined_balance_id(
5226 p_primary_balance_id,
5227 '_ASG_PAYMENTS',
5228 p_legislation_code);
5229 else
5230 ln_pymt_defined_balance_id
5231 := pay_emp_action_arch.get_defined_balance_id(
5232 p_primary_balance_id,
5233 '_PAYMENTS',
5234 p_legislation_code);
5235 end if;
5236 end if; -- p_action_type in ('B','V')
5237 /* end of addition for Reversals and bal adjustments */
5238 hr_utility.trace('ln_pymt_defined_balance_id ' ||
5239 ln_pymt_defined_balance_id);
5240
5241 if ln_pymt_defined_balance_id is not null then
5242 ln_payments_amount := nvl(pay_balance_pkg.get_value(
5243 ln_pymt_defined_balance_id,
5244 p_pymt_balcall_aaid),0);
5245 hr_utility.trace('ln_payments_amount = ' ||ln_payments_amount);
5246 end if;
5247
5248 if p_hours_balance_id is not null then
5249 /* Added dimension _ASG_GRE_RUN for reversals and Balance
5250 Adjustments for Canada. Bug#3498653 */
5251 if p_action_type in ('B','V') then
5252 ln_pymt_hours_balance_id
5253 := pay_emp_action_arch.get_defined_balance_id(
5254 p_hours_balance_id
5255 ,'_ASG_GRE_RUN'
5256 ,p_legislation_code);
5257 else
5258 if pay_emp_action_arch.gv_multi_leg_rule = 'Y' then
5259 ln_pymt_hours_balance_id
5260 := pay_emp_action_arch.get_defined_balance_id(
5261 p_hours_balance_id
5262 ,'_ASG_PAYMENTS'
5263 ,p_legislation_code);
5264 else
5265 ln_pymt_hours_balance_id
5266 := pay_emp_action_arch.get_defined_balance_id(
5267 p_hours_balance_id
5268 ,'_PAYMENTS'
5269 ,p_legislation_code);
5270 end if;
5271 end if; -- p_action_type in ('B','V')
5272 /* end of addition for reversals and bal adjustments */
5273 hr_utility.trace('ln_pymt_hours_balance_id ' ||
5274 ln_pymt_hours_balance_id);
5275
5276 hr_utility.set_location(gv_package || lv_procedure_name, 120);
5277 end if; --Hours
5278 end if; -- p_pymt_balcall_aaid is not null
5279
5280 ln_step := 15;
5281
5282 pay_ac_action_arch.lrr_act_tab(ln_index).act_info9
5283 := fnd_number.number_to_canonical(ln_ytd_amount);
5284 hr_utility.trace('ln_ytd_amount' || nvl(ln_ytd_amount,0));
5285
5286 pay_ac_action_arch.lrr_act_tab(ln_index).act_info12
5287 := fnd_number.number_to_canonical(ln_ytd_hours);
5288 hr_utility.trace('ln_ytd_hours' || ln_ytd_hours);
5289
5290 /* Following later to be re-valuated IF worth doing wrt Cost
5291
5292 IF count_j is null THEN
5293 pay_ac_action_arch.lrr_act_tab(ln_index).act_info9
5294 := fnd_number.number_to_canonical(nvl(ln_ytd_amount,0));
5295 hr_utility.trace('ln_ytd_amount' || ln_ytd_amount);
5296 pay_ac_action_arch.lrr_act_tab(ln_index).act_info12
5297 := fnd_number.number_to_canonical(ln_ytd_hours);
5298 hr_utility.trace('ln_ytd_hours' || ln_ytd_hours);
5299 END IF;
5300 */
5301
5302 EXCEPTION
5303 when others then
5304 hr_utility.trace(' Error In archive_addnl_elements procedure');
5305 hr_utility.trace('error occured at step ' || ln_step );
5306
5307 END Archive_addnl_elements;
5308
5309 END pay_ac_action_arch;