[Home] [Help]
PACKAGE BODY: APPS.PAY_NZ_SOE_PKG
Source
1 package body pay_nz_soe_pkg as
2 /* $Header: pynzsoe.pkb 120.0.12000000.4 2007/06/04 11:54:56 dduvvuri noship $ */
3
4 /*
5 **
6 ** Copyright (C) 1999 Oracle Corporation
7 ** All Rights Reserved
8 **
9 ** NZ HRMS statement of earnings package
10 **
11 ** Change List
12 ** ===========
13 **
14 ** Date Author Reference Description
15 ** -----------+--------+---------+-------------
16 ** 10 MAY 2000 JMHATRE N/A Creation
17 ** 04 AUG 2000 NDOMA N/A Added two procedures which is required
18 ** for NZ SOE window(get_details and
19 ** get_asg_latest_pay).
20 ** 21 AUG 2000 NDOMA N/A Added new procedure(final_balance_totals)
21 ** Which is used to get the cumulative run
22 ** balances if the prepayments is run for
23 ** selected run or prepayments.
24 **
25 ** 11 JUN 2001 SHOSKATT Bug : 1817816
26 ** get_tax_code function changed to retrieve Special
27 ** Tax Code along with Tax Code. Also the function
28 ** changed to check for date conditions
29 ** 10 JAN 2002 SRRAJAGO 2177800 Included Action_type 'C' for Costing process
30 ** 10 OCT 2002 PUCHIL 2595888 Changed the type of variable l_action_type
31 ** from varchar2(1) to pay_payroll_actions.action_type%type
32 ** 19 NOV 2002 SRRAJAGO 2636739 Removed the action_type 'C' only from cursor asg_latest_pay of the procedure get_asg_latest_pay
33 ** 03 DEC 2002 SRRAJAGO 2689221 Included 'nocopy' option for the 'out' and 'in out' parameters of all the procedures.
34 ** 17 NOV 2003 PUCHIL 3257888 Added language check to cursors c_get_work_address and c_get_home_address.
35 ** 08 APR 2004 PUCHIL 3453503 Added logic to support Advanced Retropay.
36 ** 04 JUN 2007 DDUVVURI 6083911 Removed the condition "legislation_code is null" in the cursor in procedure run_and_ytd_balances
37 */
38
39 --
40 -- get_tax_code
41 --
42
43 function get_tax_code (p_run_assignment_action_id number) return varchar2 is
44
45 l_tax_code pay_run_result_values.result_value%type;
46 l_special_tax_code pay_run_result_values.result_value%type;
47
48 --
49 -- Fetch Special Tax Code along with Tax Code (Bug No : 1817816)
50 --
51 cursor c_tax_code (p_assignment_action_id number) is
52 select rrv.result_value,rrv1.result_value
53 from pay_element_types_f et
54 , pay_input_values_f iv
55 , pay_run_result_values rrv
56 , pay_run_results rr
57 , pay_input_values_f iv1
58 , pay_run_result_values rrv1
59 , pay_payroll_actions ppa
60 , pay_assignment_actions paa
61 where et.element_name = 'PAYE Information'
62 and et.legislation_code = 'NZ'
63 and iv.element_type_id = et.element_type_id
64 and iv.name = 'Tax Code'
65 and rr.element_type_id = et.element_type_id
66 and rr.assignment_action_id = p_assignment_action_id
67 and rrv.run_result_id = rr.run_result_id
68 and rrv.input_value_id = iv.input_value_id
69 and iv1.element_type_id = et.element_type_id
70 and iv1.name = 'Special Tax Code'
71 and rrv1.run_result_id = rr.run_result_id
72 and rrv1.input_value_id = iv1.input_value_id
73 and ppa.payroll_action_id = paa.payroll_action_id
74 and paa.assignment_action_id = rr.assignment_action_id
75 and ppa.effective_date between et.effective_start_date and et.effective_end_date
76 and ppa.effective_date between iv.effective_start_date and iv.effective_end_date
77 and ppa.effective_date between iv1.effective_start_date and iv1.effective_end_date;
78
79 begin
80
81 hr_utility.set_location('pay_nz_soe_pkg.get_tax_code', 10) ;
82
83 open c_tax_code (p_run_assignment_action_id) ;
84 fetch c_tax_code into l_tax_code,l_special_tax_code ;
85 if c_tax_code%notfound
86 then
87 l_tax_code := null ;
88 l_special_tax_code := null ;
89 end if ;
90 --
91 -- If record is found and Special tax Code is Yes, then return Tax Code as STC (Bug No 1817816)
92 --
93 if (c_tax_code%found and l_special_tax_code = 'Y') then
94 l_tax_code := 'STC' ;
95 end if;
96 close c_tax_code ;
97
98 hr_utility.set_location('pay_nz_soe_pkg.get_tax_code', 20) ;
99
100 return l_tax_code ;
101
102 exception
103 when others then
104 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL') ;
105 hr_utility.set_message_token('PROCEDURE', 'pay_nz_soe_pkg.get_tax_code') ;
106 hr_utility.set_message_token('STEP','body') ;
107 hr_utility.raise_error ;
108
109 end get_tax_code ;
110
111 --
112 -- get_home_address
113 --
114 Procedure get_home_address(p_person_id IN NUMBER,
115 p_addr_line1 OUT NOCOPY VARCHAR2,
116 p_addr_line2 OUT NOCOPY VARCHAR2,
117 p_addr_line3 OUT NOCOPY VARCHAR2,
118 p_town_city OUT NOCOPY VARCHAR2,
119 p_postal_code OUT NOCOPY VARCHAR2,
120 p_country_name OUT NOCOPY VARCHAR2) IS
121
122 Cursor c_get_home_address (cp_person_id NUMBER) is
123 select substr(pad.address_line1,1,27),
124 substr(pad.address_line2,1,27),
125 substr(pad.address_line3,1,27),
126 substr(pad.town_or_city,1,27),
127 pad.postal_code,
128 substr(ftt.territory_short_name,1,27)
129 from per_addresses pad,
130 fnd_territories_tl ftt
131 where pad.country = ftt.territory_code
132 and ftt.language = USERENV('LANG') -- Bug 3257888
133 and pad.person_id = cp_person_id
134 and sysdate between nvl(pad.date_from, sysdate) and nvl(pad.date_to, sysdate);
135
136 Begin
137 hr_utility.set_location('pay_nz_soe_pkg.get_home_address', 10) ;
138 open c_get_home_address(p_person_id);
139 fetch c_get_home_address into p_addr_line1,
140 p_addr_line2,
141 p_addr_line3,
142 p_town_city,
143 p_postal_code,
144 p_country_name;
145 close c_get_home_address;
146 hr_utility.set_location('pay_nz_soe_pkg.get_home_address', 20) ;
147
148 Exception
149 when others then
150 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL') ;
151 hr_utility.set_message_token('PROCEDURE', 'pay_nz_soe_pkg.get_home_address') ;
152 hr_utility.set_message_token('STEP','body') ;
153 hr_utility.raise_error ;
154
155 End;
156
157 --
158 -- get_work_address
159 --
160
161 Procedure get_work_address(p_location_id IN NUMBER,
162 p_addr_line1 OUT NOCOPY VARCHAR2,
163 p_addr_line2 OUT NOCOPY VARCHAR2,
164 p_addr_line3 OUT NOCOPY VARCHAR2,
165 p_town_city OUT NOCOPY VARCHAR2,
166 p_postal_code OUT NOCOPY VARCHAR2,
167 p_country_name OUT NOCOPY VARCHAR2) IS
168
169 Cursor c_get_work_address(cp_location_id NUMBER) is
170 select substr(hrl.address_line_1,1,27),
171 substr(hrl.address_line_2,1,27),
172 substr(hrl.address_line_3,1,27),
173 substr(hrl.town_or_city,1,27),
174 hrl.postal_code,
175 substr(ftt.territory_short_name,1,27)
176 from hr_locations hrl,
177 fnd_territories_tl ftt
178 where hrl.country = ftt.territory_code
179 and ftt.language = USERENV('LANG') -- Bug 3257888
180 and hrl.location_id = cp_location_id;
181
182 Begin
183 hr_utility.set_location('pay_nz_soe_pkg.get_work_address', 10) ;
184 open c_get_work_address(p_location_id);
185 fetch c_get_work_address into p_addr_line1,
186 p_addr_line2,
187 p_addr_line3,
188 p_town_city,
189 p_postal_code,
190 p_country_name;
191 close c_get_work_address;
192 hr_utility.set_location('pay_nz_soe_pkg.get_work_address', 20) ;
193
194 Exception
195 when others then
196 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL') ;
197 hr_utility.set_message_token('PROCEDURE', 'pay_nz_soe_pkg.get_work_address') ;
198 hr_utility.set_message_token('STEP','body') ;
199 hr_utility.raise_error ;
200
201 End;
202
203 --
204 -- get_salary
205 --
206
207 function GET_SALARY (
208 --
209 p_pay_basis_id number,
210 p_assignment_id number,
211 p_effective_date date) return varchar2 is
212 --
213 -- clone of hr_general.get_salary but fetcH At a given date
214 -- This cursor gets the screen_entry_value from pay_element_entry_values_f.
215 -- This is the salary amount
216 -- obtained when the pay basis isn't null. The pay basis and assignment_id
217 -- are passed in by the view. A check is made on the effective date of
218 -- pay_element_entry_values_f and pay_element_entries_f as they're datetracked.
219 --
220 cursor csr_lookup is
221 select eev.screen_entry_value
222 from pay_element_entry_values_f eev,
223 per_pay_bases ppb,
224 pay_element_entries_f pe
225 where ppb.pay_basis_id +0 = p_pay_basis_id
226 and pe.assignment_id = p_assignment_id
227 and eev.input_value_id = ppb.input_value_id
228 and eev.element_entry_id = pe.element_entry_id
229
230 and eev.input_value_id = ppb.input_value_id
231 and eev.element_entry_id = pe.element_entry_id
232 and p_effECtive_date between
233 eev.effective_start_date and eev.effective_end_date
234 and p_EFfective_date between
235 pe.effective_start_date and pe.effective_end_date;
236 --
237 v_meaning varchar2(60);
238 begin
239 --
240 -- Only open the cursor if the parameter may retrieve anything
241 -- In practice, p_assignment_id is always going to be non null;
242 -- p_pay_basis_id may be null, though. If it is, don't bother trying
243 -- to fetch a salary.
244 --
245 -- If we do have a pay basis, try and get a salary. There may not be one,
246 -- in which case no problem: just return null.
247 --
248 if p_pay_basis_id is not null and p_assignment_id is not null then
249 open csr_lookup;
250 fetch csr_lookup into v_meaning;
251 close csr_lookup;
252
253 end if;
254 --
255 -- Return the salary value, if this does not exist, return a null value.
256 --
257 return v_meaning;
258 end get_salary;
259
260 ------------------------------------------------------------------------
261 -- Returns the Currency Code for the Business Group.
262 ------------------------------------------------------------------------
263 function business_currency_code
264 (p_business_group_id in hr_organization_units.business_group_id%type)
265 return fnd_currencies.currency_code%type is
266
267 v_currency_code fnd_currencies.currency_code%type;
268
269 cursor currency_code
270 (c_business_group_id hr_organization_units.business_group_id%type) is
271 select fcu.currency_code
272 from hr_organization_information hoi,
273 hr_organization_units hou,
274 fnd_currencies fcu
275 where hou.business_group_id = c_business_group_id
276 and hou.organization_id = hoi.organization_id
277 and hoi.org_information_context = 'Business Group Information'
278 and fcu.issuing_territory_code = hoi.org_information9;
279
280 begin
281 open currency_code (p_business_group_id);
282 fetch currency_code into v_currency_code;
283 close currency_code;
284
285 return v_currency_code;
286 end business_currency_code;
287
288 ------------------------------------------------------------------------
289 -- Procedure to merely pass all the balance results back in one hit,
290 -- rather than 6 separate calls.
291 ------------------------------------------------------------------------
292 procedure balance_totals
293 (p_assignment_id in per_all_assignments_f.assignment_id%type,
294 p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
295 p_effective_date in date,
296 p_gross_this_pay out nocopy number,
297 p_other_deductions_this_pay out nocopy number,
298 p_tax_deductions_this_pay out nocopy number,
299 p_gross_ytd out nocopy number,
300 p_other_deductions_ytd out nocopy number,
301 p_tax_deductions_ytd out nocopy number,
302 p_non_tax_allow_this_pay out nocopy number,
303 p_non_tax_allow_ytd out nocopy number,
304 p_pre_tax_deductions_this_pay out nocopy number,
305 p_pre_tax_deductions_ytd out nocopy number)
306 is
307 v_Extra_Emolument_Tax_Ear_run number;
308 v_Extra_Emolument_Tax_Ear_ytd number;
309 v_Ordinary_Tax_Ear_run number;
310 v_Ordinary_Tax_Ear_ytd number;
311 v_Retro_Ordinary_Tax_Ear_run number;
312 v_Retro_Ordinary_Tax_Ear_ytd number;
313 v_Retiring_Redund_Tax_Ear_run number;
314 v_Retiring_Redund_Tax_Ear_ytd number;
315 v_Withholding_Payments_run number;
316 v_Withholding_Payments_ytd number;
317 v_pre_tax_deductions_run number;
318 v_pre_tax_deductions_ytd number;
319 v_voluntary_deductions_run number;
320 v_voluntary_deductions_ytd number;
321 v_leg_order_deductions_run number;
322 v_leg_order_deductions_ytd number;
323 v_tax_deductions_run number;
324 v_tax_deductions_ytd number;
325 v_retro_tax_deductions_run number;
326 v_retro_tax_deductions_ytd number;
327 v_Non_Tax_Reimbursements_run number;
328 v_Non_Tax_Reimbursements_ytd number;
329
330
331 begin
332 run_and_ytd_balances (p_assignment_id => p_assignment_id,
333 p_assignment_action_id => p_assignment_action_id,
334 p_effective_date => p_effective_date,
335 p_balance_name => 'Extra Emolument Taxable Earnings',
336 p_run_balance => v_Extra_Emolument_Tax_Ear_run,
337 p_ytd_balance => v_Extra_Emolument_Tax_Ear_ytd);
338
339 run_and_ytd_balances (p_assignment_id => p_assignment_id,
340 p_assignment_action_id => p_assignment_action_id,
341 p_effective_date => p_effective_date,
342 p_balance_name => 'Ordinary Taxable Earnings',
343 p_run_balance => v_Ordinary_Tax_Ear_run,
344 p_ytd_balance => v_Ordinary_Tax_Ear_ytd);
345
346 -- Bug 3453503 - Added to support Advanced Retropay
347 run_and_ytd_balances (p_assignment_id => p_assignment_id,
348 p_assignment_action_id => p_assignment_action_id,
349 p_effective_date => p_effective_date,
350 p_balance_name => 'Retro Ordinary Taxable Earnings',
351 p_run_balance => v_retro_Ordinary_Tax_Ear_run,
352 p_ytd_balance => v_retro_Ordinary_Tax_Ear_ytd);
353
354 run_and_ytd_balances (p_assignment_id => p_assignment_id,
355 p_assignment_action_id => p_assignment_action_id,
356 p_effective_date => p_effective_date,
357 p_balance_name => 'Retiring and Redundancy Taxable Earnings',
358 p_run_balance => v_Retiring_Redund_Tax_Ear_run,
362 p_assignment_action_id => p_assignment_action_id,
359 p_ytd_balance => v_Retiring_Redund_Tax_Ear_ytd);
360
361 run_and_ytd_balances (p_assignment_id => p_assignment_id,
363 p_effective_date => p_effective_date,
364 p_balance_name => 'Withholding Payments',
365 p_run_balance => v_Withholding_Payments_run,
366 p_ytd_balance => v_Withholding_Payments_ytd);
367
368 run_and_ytd_balances (p_assignment_id => p_assignment_id,
369 p_assignment_action_id => p_assignment_action_id,
370 p_effective_date => p_effective_date,
371 p_balance_name => 'Pre Tax Deductions',
372 p_run_balance => v_pre_tax_deductions_run,
373 p_ytd_balance => v_pre_tax_deductions_ytd);
374
375 run_and_ytd_balances (p_assignment_id => p_assignment_id,
376 p_assignment_action_id => p_assignment_action_id,
377 p_effective_date => p_effective_date,
378 p_balance_name => 'Voluntary Deductions',
379 p_run_balance => v_voluntary_deductions_run,
380 p_ytd_balance => v_voluntary_deductions_ytd);
381 run_and_ytd_balances (p_assignment_id => p_assignment_id,
382 p_assignment_action_id => p_assignment_action_id,
383 p_effective_date => p_effective_date,
384 p_balance_name => 'Tax Deductions',
385 p_run_balance => v_tax_deductions_run,
386 p_ytd_balance => v_tax_deductions_ytd);
387
388 -- Bug 3453503 - Added to support Advanced Retropay
389 run_and_ytd_balances (p_assignment_id => p_assignment_id,
390 p_assignment_action_id => p_assignment_action_id,
391 p_effective_date => p_effective_date,
392 p_balance_name => 'Retro Tax Deductions',
393 p_run_balance => v_retro_tax_deductions_run,
394 p_ytd_balance => v_retro_tax_deductions_ytd);
395
396 run_and_ytd_balances (p_assignment_id => p_assignment_id,
397 p_assignment_action_id => p_assignment_action_id,
398 p_effective_date => p_effective_date,
399 p_balance_name => 'Legislative Order Deductions',
400 p_run_balance => v_leg_order_deductions_run,
401 p_ytd_balance => v_leg_order_deductions_ytd);
402
403 run_and_ytd_balances (p_assignment_id => p_assignment_id,
404 p_assignment_action_id => p_assignment_action_id,
405 p_effective_date => p_effective_date,
406 p_balance_name => 'Non Taxable Reimbursements',
407 p_run_balance => v_Non_Tax_Reimbursements_run,
408 p_ytd_balance => v_Non_Tax_Reimbursements_ytd);
409
410 p_gross_this_pay := v_Extra_Emolument_Tax_Ear_run +
411 v_Ordinary_Tax_Ear_run +
412 v_Retro_Ordinary_Tax_Ear_run + -- Bug 3453503
413 v_Retiring_Redund_Tax_Ear_run +
414 v_Withholding_Payments_run ;
415
416
417 p_gross_ytd := v_Withholding_Payments_ytd +
418 v_Extra_Emolument_Tax_Ear_ytd +
419 v_Retiring_Redund_Tax_Ear_ytd +
420 v_Retro_Ordinary_Tax_Ear_ytd + -- Bug 3453503
421 v_Ordinary_Tax_Ear_ytd;
422
423 p_non_tax_allow_this_pay := v_Non_Tax_Reimbursements_run;
424
425 p_non_tax_allow_ytd := v_Non_Tax_Reimbursements_ytd;
426
427 p_other_deductions_this_pay := v_pre_tax_deductions_run +
428 v_voluntary_deductions_run +
429 v_leg_order_deductions_run ;
430
431 p_other_deductions_ytd := v_leg_order_deductions_ytd +
432 v_pre_tax_deductions_ytd +
433 v_voluntary_deductions_ytd ;
434
435 p_tax_deductions_this_pay := v_tax_deductions_run +
436 v_retro_tax_deductions_run; -- Bug 3453503
437
438 p_tax_deductions_ytd := v_tax_deductions_ytd +
439 v_retro_tax_deductions_ytd; --Bug 3453503
440
441 p_pre_tax_deductions_this_pay := v_pre_tax_deductions_run;
442
443 p_pre_tax_deductions_ytd := v_pre_tax_deductions_ytd;
444
445 end balance_totals;
446 ------------------------------------------------------------------------
447
448 ------------------------------------------------------------------------
449 -- Sums the Balances for This Pay and YTD, according to the parameters.
450 ------------------------------------------------------------------------
451 procedure run_and_ytd_balances
452 (p_assignment_id in per_all_assignments_f.assignment_id%type,
453 p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
457 p_ytd_balance out nocopy number)
454 p_effective_date in date,
455 p_balance_name in pay_balance_types.balance_name%type,
456 p_run_balance out nocopy number,
458 is
459
460 cursor run_and_ytd_value
461 (c_assignment_id pay_assignment_actions.assignment_id%type,
462 c_assignment_action_id pay_assignment_actions.assignment_action_id%type,
463 c_effective_date date,
464 c_balance_name pay_balance_types.balance_name%type) is
465 select nvl(hr_nzbal.calc_asg_run(c_assignment_action_id,
466 balance_type_id,
467 c_effective_date,
468 c_assignment_id),0),
469 nvl(hr_nzbal.calc_asg_ytd(c_assignment_action_id,
470 balance_type_id,
471 c_effective_date,
472 c_assignment_id),0)
473 from pay_balance_types
474 where balance_name = c_balance_name
475 -- Modified the condition for bug 6083911
476 and legislation_code = 'NZ';
477
478 begin
479 open run_and_ytd_value (p_assignment_id,
480 p_assignment_action_id,
481 p_effective_date,
482 p_balance_name);
483 fetch run_and_ytd_value into p_run_balance,
484 p_ytd_balance;
485 close run_and_ytd_value;
486 end run_and_ytd_balances;
487
488 procedure get_asg_latest_pay(p_session_date in date,
489 p_payroll_exists in out nocopy varchar2,
490 p_assignment_action_id in out nocopy number,
491 p_run_assignment_action_id in out nocopy number,
492 p_assignment_id in number,
493 p_payroll_id out nocopy number,
494 p_payroll_action_id in out nocopy number,
495 p_date_earned in out nocopy varchar2,
496 p_time_period_id out nocopy number,
497 p_period_name out nocopy varchar2,
498 p_pay_advice_date out nocopy date,
499 p_pay_advice_message out nocopy varchar2)
500 is
501
502 -- get the latest prepayments action for this individual and get the
503 -- details of the last run that that action locked
504 cursor asg_latest_pay is
505 select
506 rppa.date_earned,
507 rpaa.payroll_action_id,
508 rpaa.assignment_action_id,
509 paa.assignment_action_id,
510 ptp.time_period_id,
511 ptp.period_name,
512 rppa.payroll_id,
513 nvl(rppa.pay_advice_date,ptp.pay_advice_date),
514 rppa.pay_advice_message
515 from pay_assignment_actions paa,
516 pay_payroll_actions ppa,
517 pay_assignment_actions rpaa,
518 per_time_periods ptp,
519 pay_payroll_actions rppa
520 where paa.payroll_action_id = ppa.payroll_action_id
521 and rppa.payroll_action_id = rpaa.payroll_action_id
522 and rppa.time_period_id = ptp.time_period_id
523 and paa.assignment_action_id =
524 (select to_number(substr(max(to_char(pa.effective_date,'J')||lpad(aa.assignment_action_id,15,'0')),8))
525 from pay_payroll_actions pa,
526 pay_assignment_actions aa
527 where pa.action_type in ('U','P')
528 and aa.action_status = 'C'
529 and pa.payroll_action_id = aa.payroll_action_id
530 and aa.assignment_id = p_assignment_id
531 and pa.effective_date <= p_session_date)
532 and ppa.action_type in ('P', 'U')
533 and rpaa.assignment_id = p_assignment_id
534 and rpaa.action_sequence =
535 (select max(aa.action_sequence)
536 from pay_assignment_actions aa,
537 pay_action_interlocks loc
538 where loc.locked_action_id = aa.assignment_action_id
539 and loc.locking_action_id = paa.assignment_action_id);
540 begin
541 open asg_latest_pay;
542 fetch asg_latest_pay into p_date_earned,
543 p_payroll_action_id,
544 p_run_assignment_action_id,
545 p_assignment_action_id,
546 p_time_period_id,
547 p_period_name,
548 p_payroll_id,
549 p_pay_advice_date,
550 p_pay_advice_message;
551 if asg_latest_pay%FOUND then
552 p_payroll_exists := 'TRUE';
553 end if;
554 close asg_latest_pay;
555 end get_asg_latest_pay;
556
557 ------------------------------------------------------------------
558 procedure get_details (p_assignment_action_id in out nocopy number,
559 p_run_assignment_action_id in out nocopy number,
560 p_assignment_id in out nocopy number,
561 p_payroll_id out nocopy number,
562 p_payroll_action_id in out nocopy number,
563 p_date_earned in out nocopy date,
564 p_time_period_id out nocopy number,
565 p_period_name out nocopy varchar2,
566 p_pay_advice_date out nocopy date,
567 p_pay_advice_message out nocopy varchar2) is
571 --locked by the prepayment
568
569 -- if the assignment action is a run then return the run details
570 -- if the assignment action is a prepayment return the latest run
572
573 cursor get_action_type is
574 -- find type of action this is
575 select pact.action_type , assact.assignment_id
576 from pay_assignment_actions assact,
577 pay_payroll_actions pact
578 where assact.assignment_action_id = p_assignment_action_id
579 and pact.payroll_action_id =
580 assact.payroll_action_id
581 ;
582 cursor get_run is
583 -- for prepayment action find the latest interlocked run
584 select assact.assignment_action_id
585 from pay_assignment_actions assact,
586 pay_action_interlocks loc
587 where loc.locking_action_id = p_assignment_action_id
588 and assact.assignment_action_id = loc.locked_action_id
589 order by assact.action_sequence desc ;
590
591 cursor get_prepay is
592 -- for run action check if its been prepaid
593 select assact.assignment_action_id
594 from pay_assignment_actions assact,
595 pay_payroll_actions pact,
596 pay_action_interlocks loc
597 where loc.locked_action_id = p_assignment_action_id
598 and assact.assignment_action_id = loc.locking_action_id
599 and pact.payroll_action_id = assact.payroll_action_id
600 and pact.action_type in ('P','U','C') -- Bug No : 2177800
601 -- prepayments only
602 order by assact.action_sequence desc
603 ;
604 cursor get_run_details is
605 -- now find the date earned and payroll action of the run action
606 select pact.payroll_id,
607 pact.payroll_action_id,
608 pact.date_earned,
609 pact.time_period_id,
610 ptp.period_name,
611 nvl(pact.pay_advice_date,ptp.pay_advice_date),
612 pay_advice_message
613 from pay_assignment_actions assact,
614 pay_payroll_actions pact,
615 per_time_periods ptp
616 where assact.assignment_action_id = p_run_assignment_action_id
617 and pact.payroll_action_id = assact.payroll_action_id
618 and pact.time_period_id = ptp.time_period_id ;
619 --
620 -- Bug 2595888: changed the datatype from varchar2(1) to pay_payroll_actions.action_type%type
621 l_action_type pay_payroll_actions.action_type%type;
622 --
623 begin
624 --
625 open get_action_type;
626 fetch get_action_type into l_action_type, p_assignment_id;
627 close get_action_type;
628 --
629 if l_action_type in ('P', 'U','C') then -- Bug No : 2177800
630 open get_run;
631 fetch get_run into p_run_assignment_action_id;
632 close get_run;
633 -- if its a run action it may or may not have been prepaid
634 else
635 p_run_assignment_action_id := p_assignment_action_id;
636 begin
637 open get_prepay;
638 fetch get_prepay into p_assignment_action_id;
639 if get_prepay%NOTFOUND then
640 p_assignment_action_id := p_run_assignment_action_id;
641 end if;
642 close get_prepay;
643 end;
644 end if;
645 -- fetch payroll details
646 open get_run_details;
647 fetch get_run_details into p_payroll_id,
648 p_payroll_action_id,
649 p_date_earned,
650 p_time_period_id,
651 p_period_name,
652 p_pay_advice_date,
653 p_pay_advice_message;
654 close get_run_details;
655 end get_details;
656
657 procedure final_balance_totals
658 (p_assignment_id in per_all_assignments_f.assignment_id%type,
659 p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
660 p_effective_date in date,
661 p_gross_this_pay out nocopy number,
662 p_other_deductions_this_pay out nocopy number,
663 p_tax_deductions_this_pay out nocopy number,
664 p_gross_ytd out nocopy number,
665 p_other_deductions_ytd out nocopy number,
666 p_tax_deductions_ytd out nocopy number,
667 p_non_tax_allow_this_pay out nocopy number,
668 p_non_tax_allow_ytd out nocopy number,
669 p_pre_tax_deductions_this_pay out nocopy number,
670 p_pre_tax_deductions_ytd out nocopy number)
671 is
672
673 cursor run_ids is
674 select LOCKED_ACTION_ID
675 from pay_action_interlocks
676 where LOCKING_ACTION_ID = p_assignment_action_id
677 order by locked_action_id asc;
678 pre_pay number;
679 cur_run_id number;
680 l_ASSIGNMENT_ID number ;
681 l_RUN_ASSIGNMENT_ACTION_ID number ;
682 l_DATE_EARNED date ;
683 l_GROSS_INCOME_TP number;
684 l_DED_TP number;
685 l_TAX_DED_TP number;
686 l_GROSS_INCOME_YTD number;
687 l_DED_YTD number;
691 l_PRE_TAX_DED_TP number;
688 l_TAX_DED_YTD number;
689 l_NON_TAX_TP number;
690 l_NON_TAX_YTD number;
692 l_PRE_TAX_DED_YTD number;
693 begin
694 pre_pay := 1;
695 open run_ids;
696 loop
697 fetch run_ids into cur_run_id;
698 exit when run_ids%NOTFOUND;
699 l_RUN_ASSIGNMENT_ACTION_ID := cur_run_id;
700 pre_pay:= 0;
701 pay_nz_soe_pkg.balance_totals(
702 p_assignment_id ,
703 l_RUN_ASSIGNMENT_ACTION_ID ,
704 p_effective_date ,
705 l_GROSS_INCOME_TP,
706 l_DED_TP,
707 l_TAX_DED_TP,
708 l_GROSS_INCOME_YTD,
709 l_DED_YTD,
710 l_TAX_DED_YTD,
711 l_NON_TAX_TP,
712 l_NON_TAX_YTD,
713 l_PRE_TAX_DED_TP,
714 l_PRE_TAX_DED_YTD);
715
716 p_gross_this_pay := NVL(p_gross_this_pay,0) + l_GROSS_INCOME_TP;
717 p_other_deductions_this_pay := NVL(p_other_deductions_this_pay,0) + l_DED_TP;
718 p_tax_deductions_this_pay := NVL(p_tax_deductions_this_pay,0) + l_TAX_DED_TP;
719 p_non_tax_allow_this_pay := NVL(p_non_tax_allow_this_pay,0) + l_NON_TAX_TP;
720 p_pre_tax_deductions_this_pay := NVL(p_pre_tax_deductions_this_pay,0) +
721 l_PRE_TAX_DED_TP;
722 end loop;
723 p_gross_ytd := l_GROSS_INCOME_YTD;
724 p_other_deductions_ytd := l_DED_YTD;
725 p_tax_deductions_ytd := l_TAX_DED_YTD;
726 p_non_tax_allow_ytd := l_NON_TAX_YTD;
727 p_pre_tax_deductions_ytd := l_PRE_TAX_DED_YTD;
728 close run_ids;
729 if Pre_pay <> 0 then
730 pay_nz_soe_pkg.balance_totals(
731 p_assignment_id ,
732 p_assignment_action_id ,
733 p_effective_date ,
734 p_gross_this_pay ,
735 p_other_deductions_this_pay ,
736 p_tax_deductions_this_pay ,
737 p_gross_ytd ,
738 p_other_deductions_ytd ,
739 p_tax_deductions_ytd ,
740 p_non_tax_allow_this_pay ,
741 p_non_tax_allow_ytd ,
742 p_pre_tax_deductions_this_pay ,
743 p_pre_tax_deductions_ytd );
744 end if;
745 end final_balance_totals;
746
747 END pay_nz_soe_pkg ;