1 PACKAGE BODY PAY_P45_PKG2 AS
2 /* $Header: payp45p.pkb 120.2 2006/11/08 00:34:26 rmakhija noship $ */
3
4 /*
5 NAME
6 payp45p.pkb -- procedure P45 Report
7 --
8 DESCRIPTION
9 this procedure is used by PAYRPP45 report to retrieve the database
10 items and the balance items.
11 --
12 MODIFIED (DD-MON-YYYY)
13 btailor 11-JUL-1995 - Created.
14 ctucker 01-SEP-1995 - Return code for Tax basis instead of desc
15 on procedure get_database_items
16 smrobinson 11-APR-2001 - Added legislation code specifier to
17 balance_type_id select in defined_balance_id
18 function.
19 smrobins 27-FEB-2002 - Added get_uk_term_dates. Called by HREMEA
20 to default the last standard process and
21 final process dates.
22 smrobins 01-MAR-2002 - Change to get_uk_term_dates to only return
23 a value for final close, which has been
24 pushed out nocopy for end date of period
25 regular payment date resides in. Change for
26 Positive Offsets.
27 rmakhija 115.4 01-MAY-2002 - Changed context and database items for tax
28 details
29 rmakhija 115.5 05-JUL-2002 - Changed get_database_items procedure to
30 get statutory details from run result
31 values before fetching them from DBIs
32 rmakhija 115.6 08-JUL-2002 - Changed DBI names for Previous Pay and Tax
33 gbutler 115.7 27-JAN-2003 - nocopy and gscc fixes
34 amills 115.8 21-JUL-2003 - Agg PAYE changes.
35 amills 115.9 02-MAR-2004 - 3473274. changed get_database_items and
36 get_balance_items to handle NDFs.
37 amills 115.10 02-MAR-2004 - Added nocopy hints.
38 npershad 115.11 14-OCT-2005 - 4428406. Removed reference to redundant index
39 PAY_ASSIGNMENT_ACTIONS_N1 used in hints.
40 rmakhija 115.12 07-NOV-2006 - 5144323, replaced PER_TD_YTD dimension with
41 PER_TD_CPE_YTD
42 */
43
44 /* Constants */
45
46 -- DataBase Items
47 -- these are the database items used for the values displayed
48 --
49 G_TAX_REFNO_ITEM varchar2(30) := 'SCL_PAY_GB_TAX_REFERENCE';
50 G_TAX_CODE_ITEM varchar2(40) := 'PAYE_DETAILS_TAX_CODE_GB_ENTRY_VALUE';
51 G_TAX_BASIS_ITEM varchar2(40) := 'PAYE_DETAILS_TAX_BASIS_GB_ENTRY_VALUE';
52 G_TAX_PERIOD_ITEM varchar2(40) := 'PAY_STATUTORY_PERIOD_NUMBER';
53 G_PREV_PAY_DETAILS varchar2(40) := 'PAYE_DETAILS_PAY_PREVIOUS_GB_ENTRY_VALUE';
54 G_PREV_TAX_DETAILS varchar2(40) := 'PAYE_DETAILS_TAX_PREVIOUS_GB_ENTRY_VALUE';
55 --
56 -- Balance Items
57 --
58 -- the following are the database items used to retrieve the balances
59 -- for P45 report. Use PERson level for Aggregated PAYE details.
60 --
61 G_TAXABLE_PAY_BALANCE varchar2(30) := 'PAYE_ASG_TD_YTD';
62 G_GROSS_PAY_BALANCE varchar2(30) := 'TAXABLE_PAY_ASG_TD_YTD';
63 G_AGG_TAXABLE_PAY_BALANCE varchar2(30) := 'PAYE_PER_TD_CPE_YTD';
64 G_AGG_GROSS_PAY_BALANCE varchar2(30) := 'TAXABLE_PAY_PER_TD_CPE_YTD';
65 --
66 -- Balance Types
67 --
68 -- the following are the types associated with the above balances
69 --
70 g_gross_pay_type varchar2(30) := 'TAXABLE PAY';
71 g_taxable_pay_type varchar2(30) := 'PAYE';
72 --
73 -- Dimension suffixes
74 --
75 -- the following are the different balance dimension suffixes used by
76 -- the balance items
77 --
78 g_year_to_date varchar2(30) := '_ASG_YTD';
79 g_tax_district_ytd varchar2(30) := '_ASG_TD_YTD';
80 g_agg_tax_district_ytd varchar2(30) := '_PER_TD_CPE_YTD';
81 --
82 -------------------------------------------------------------------------
83 --
84 -- sets the context for which a database item is to be retrieved and returns
85 -- whether context has been set correctly
86 --
87 function set_database_context (p_database_item in varchar2,
88 p_payroll_action_id in number default null,
89 p_date_earned in varchar2 default null,
90 p_assignment_id in number default null)
91 return boolean is
92 --
93 begin
94 --
95 if p_database_item = G_TAX_PERIOD_ITEM then
96 --
97 if p_payroll_action_id is not null then
98 --
99 pay_balance_pkg.set_context ('payroll_action_id',
100 to_char(p_payroll_action_id));
101 --
102 hr_utility.trace('Set context for G_TAX_PERIOD_ITEM.');
103 return true;
104 --
105 else
106 --
107 return false;
108 --
109 end if;
110 --
111 elsif p_database_item = G_TAX_REFNO_ITEM then
112 if p_date_earned is not null and
113 p_assignment_id is not null then
114 --
115 pay_balance_pkg.set_context ('date_earned',
116 p_date_earned);
117 --
118 pay_balance_pkg.set_context ('assignment_id',
119 to_char(p_assignment_id));
120 --
121 hr_utility.trace('Set context for G_TAX_REFNO_ITEM.');
122 return true;
123 --
124 else
125 --
126 return false;
127 end if;
128 elsif p_database_item = G_TAX_CODE_ITEM then
129 --
130 if p_payroll_action_id is not null and
131 p_assignment_id is not null then
132 --
133 hr_utility.trace('Set context for G_TAX_CODE_ITEM, payroll_action_id='||to_char(p_payroll_action_id));
134 pay_balance_pkg.set_context ('payroll_action_id',
135 to_char(p_payroll_action_id));
136 --
137 hr_utility.trace('Set context for G_TAX_CODE_ITEM, assignment_id='||to_char(p_assignment_id));
138 pay_balance_pkg.set_context ('assignment_id',
139 to_char(p_assignment_id));
140 --
141 hr_utility.trace('Set context for G_TAX_CODE_ITEM.');
142 return true;
143 --
144 else
145 --
146 return false;
147 --
148 end if;
149 --
150 end if;
151 --
152 end;
153 --
154 -------------------------------------------------------------------------
155 procedure get_ele_entry_details(p_assignment_id in number,
156 p_effective_date in date,
157 p_tax_refno out nocopy varchar2,
158 p_tax_code out nocopy varchar2,
159 p_tax_basis out nocopy varchar2,
160 p_pay_previous out nocopy varchar2,
161 p_tax_previous out nocopy varchar2) is
162 --
163 l_paye_id number;
164 --
165 cursor csr_paye_id(c_effective_date in date) is
166 SELECT element_type_id
167 FROM pay_element_types_f
168 WHERE element_name = 'PAYE Details'
169 AND c_effective_date BETWEEN effective_start_date
170 AND effective_end_date;
171 --
172 CURSOR csr_tax_ref (c_assignment_id in number,
173 c_effective_date in date) is
174 select scl.segment1
175 from per_all_assignments_f paf,
176 pay_all_payrolls_f ppf,
177 hr_soft_coding_keyflex scl
178 where paf.assignment_id = c_assignment_id
179 and paf.payroll_id = ppf.payroll_id
180 and scl.soft_coding_keyflex_id = ppf.soft_coding_keyflex_id
181 and c_effective_date between
182 paf.effective_start_date and paf.effective_end_date
183 and c_effective_date between
184 ppf.effective_start_date and ppf.effective_end_date;
185 --
186 cursor csr_paye_details(c_assignment_id NUMBER,
187 c_effective_date DATE,
188 c_paye_id in number) IS
189 SELECT max(decode(iv.name,'Tax Code',screen_entry_value)) tax_code,
190 max(decode(iv.name,'Tax Basis',screen_entry_value)) tax_basis,
191 max(decode(iv.name,'Pay Previous',screen_entry_value))
192 pay_previous,
193 max(decode(iv.name,'Tax Previous',screen_entry_value))
194 tax_previous
195 FROM pay_element_entries_f e,
196 pay_element_entry_values_f v,
197 pay_input_values_f iv,
198 pay_element_links_f link
199 WHERE e.assignment_id = c_assignment_id
200 AND link.element_type_id = c_paye_id
201 AND e.element_link_id = link.element_link_id
202 AND e.element_entry_id = v.element_entry_id
203 AND iv.input_value_id = v.input_value_id
204 AND c_effective_date
205 BETWEEN link.effective_start_date AND link.effective_end_date
206 AND c_effective_date
207 BETWEEN e.effective_start_date AND e.effective_end_date
208 AND c_effective_date
209 BETWEEN iv.effective_start_date AND iv.effective_end_date
210 AND c_effective_date
211 BETWEEN v.effective_start_date AND v.effective_end_date;
212 --
213 BEGIN
214 hr_utility.set_location('get_ele_entry_details',10);
215 --
216 OPEN csr_paye_id(p_effective_date);
217 FETCH csr_paye_id into l_paye_id;
218 CLOSE csr_paye_id;
219 --
220 open csr_tax_ref(p_assignment_id,p_effective_date);
221 fetch csr_tax_ref into p_tax_refno;
222 close csr_tax_ref;
223 --
224 OPEN csr_paye_details(p_assignment_id,p_effective_date,l_paye_id);
225 FETCH csr_paye_details INTO p_tax_code,
226 p_tax_basis,
227 p_pay_previous,
228 p_tax_previous;
229 CLOSE csr_paye_details;
230 --
231 hr_utility.set_location('get_ele_entry_details',20);
232 --
233 EXCEPTION WHEN NO_DATA_FOUND THEN
234 p_tax_code := null;
235 p_tax_basis := null;
236 p_tax_refno := null;
237 p_pay_previous := null;
238 p_tax_previous := null;
239 --
240 END get_ele_entry_details;
241 ----------------------------------------------------------------------------
242 -- returns the value associated with a given database item assuming that the
243 -- correct context has already been set
244 --
245 function database_item (p_database_item in varchar2) return varchar2 is
246 --
247 -- constants for calls to database items
248 --
249 l_business_group_id number := null;
250 l_legislation_code varchar2(30) := 'GB';
251 --
252 begin
253 --
254 return pay_balance_pkg.run_db_item
255 (p_database_name => p_database_item,
256 p_bus_group_id => l_business_group_id,
257 p_legislation_code => l_legislation_code);
258 --
259 end;
260 --
261 -------------------------------------------------------------------------
262 --
263 -- retrieves the values to be displayed by calling database items
264 --
265 procedure get_database_items (p_assignment_id in number,
266 p_date_earned in varchar2,
267 p_payroll_action_id in number,
268 p_tax_period in out nocopy varchar2,
269 p_tax_refno in out nocopy varchar2,
270 p_tax_code in out nocopy varchar2,
271 p_tax_basis in out nocopy varchar2,
272 p_prev_pay_details in out nocopy varchar2,
273 p_prev_tax_details in out nocopy varchar2) is
274 --
275 l_tax_basis varchar2(30);
276 l_paye_element_id number;
277 l_tax_code_ipv_id number;
278 l_tax_basis_ipv_id number;
279 l_pay_previous_ipv_id number;
280 l_tax_previous_ipv_id number;
281 l_max_run_result_id number;
282 --
283 CURSOR csr_paye_element IS
284 SELECT element_type_id
285 FROM pay_element_types_f
286 WHERE element_name = 'PAYE';
287 --
288 CURSOR csr_input_value(p_ipv_name IN VARCHAR2) IS
289 SELECT input_value_id
290 FROM pay_input_values_f
291 WHERE element_type_id = l_paye_element_id
292 AND name = p_ipv_name;
293 --
294 CURSOR csr_result_value(p_ipv_id IN NUMBER) IS
295 SELECT result_value
296 FROM pay_run_result_values
297 WHERE run_result_id = l_max_run_result_id
298 AND input_value_id = p_ipv_id;
299 --
300 CURSOR csr_max_run_result IS
301 SELECT /*+ ORDERED INDEX (assact2 PAY_ASSIGNMENT_ACTIONS_N51,
302 pact PAY_PAYROLL_ACTIONS_PK,
303 r2 PAY_RUN_RESULTS_N50)
304 USE_NL(assact2, pact, r2) */
305 to_number(substr(max(lpad(assact2.action_sequence,15,'0')||r2.source_type||
306 r2.run_result_id),17))
307 FROM pay_assignment_actions assact2,
308 pay_payroll_actions pact,
309 pay_run_results r2
310 WHERE assact2.assignment_id = p_assignment_id
311 AND r2.element_type_id+0 = l_paye_element_id
312 AND r2.assignment_action_id = assact2.assignment_action_id
313 AND r2.status IN ('P', 'PA')
314 AND pact.payroll_action_id = assact2.payroll_action_id
315 AND pact.action_type IN ( 'Q','R','B','I')
316 AND assact2.action_status = 'C'
317 AND pact.effective_date <= to_date(p_date_earned,'YYYY/MM/DD')
318 AND NOT EXISTS(
319 SELECT '1'
320 FROM pay_action_interlocks pai,
321 pay_assignment_actions assact3,
322 pay_payroll_actions pact3
323 WHERE pai.locked_action_id = assact2.assignment_action_id
324 AND pai.locking_action_id = assact3.assignment_action_id
325 AND pact3.payroll_action_id = assact3.payroll_action_id
326 AND pact3.action_type = 'V'
327 AND assact3.action_status = 'C');
328 begin
329 --
330 hr_utility.set_location('pay_p45_pkg2.get_database_items',10);
331 --
332 -- Bug 3473274. Check that the payroll action is not null or -9999, which
333 -- may have been set if there are no payroll actions found for the
334 -- assignment (new starter). If so, use el entries.
335 IF p_payroll_action_id is null OR
336 p_payroll_action_id = -9999 THEN
337 --
338 hr_utility.trace('Payroll Action invalid, obtain El Entries');
339 get_ele_entry_details(p_assignment_id => p_assignment_id,
340 p_effective_date => to_date(p_date_earned,'YYYY/MM/DD'),
341 p_tax_refno => p_tax_refno,
342 p_tax_code => p_tax_code,
343 p_tax_basis => p_tax_basis,
344 p_pay_previous => p_prev_pay_details,
345 p_tax_previous => p_prev_tax_details);
346
347 --
348 hr_utility.trace('Tax Ref: '||p_tax_refno);
349 ELSE
350 hr_utility.set_location('pay_p45_pkg2.get_database_items',20);
351 -- There is a valid Payroll Action, continue selecting information.
352 -- Set context for Tax Period database item and retrieve it
353 --
354 if set_database_context (p_database_item => G_TAX_PERIOD_ITEM,
355 p_payroll_action_id => p_payroll_action_id) then
356 --
357 hr_utility.trace('Getting G_TAX_PERIOD_ITEM.');
358 p_tax_period := database_item (G_TAX_PERIOD_ITEM);
359 --
360 --
361 --
362 -- set context for the Tax Refno database item and retrieve it
363 if set_database_context (p_database_item => G_TAX_REFNO_ITEM,
364 p_date_earned => p_date_earned,
365 p_assignment_id => p_assignment_id) then
366 --
367 hr_utility.trace('Getting G_TAX_REFNO_ITEM.');
368 p_tax_refno := database_item (G_TAX_REFNO_ITEM);
369 --
370 -- Look for tax details in run results first and if not found then
371 -- call dbis
372 -- Get element id for PAYE element
373 OPEN csr_paye_element;
374 FETCH csr_paye_element INTO l_paye_element_id;
375 CLOSE csr_paye_element;
376 --
377 -- Get input_value_id for Tax Code input value
378 OPEN csr_input_value('Tax Code');
379 FETCH csr_input_value INTO l_tax_code_ipv_id;
380 CLOSE csr_input_value;
381 --
382 -- Get input_value_id for Tax Basis input value
383 OPEN csr_input_value('Tax Basis');
384 FETCH csr_input_value INTO l_tax_basis_ipv_id;
385 CLOSE csr_input_value;
386 --
387 -- Get input_value_id for Pay Previous input value
388 OPEN csr_input_value('Pay Previous');
389 FETCH csr_input_value INTO l_pay_previous_ipv_id;
390 CLOSE csr_input_value;
391 --
392 -- Get input_value_id for Tax Previous input value
393 OPEN csr_input_value('Tax Previous');
394 FETCH csr_input_value INTO l_tax_previous_ipv_id;
395 CLOSE csr_input_value;
396
397 -- Get tax code from run results of PAYE element
398 BEGIN
399 -- Get max run_result_id for PAYE element
400 OPEN csr_max_run_result;
401 FETCH csr_max_run_result INTO l_max_run_result_id;
402 -- if max run result found then get values from run result values
403 IF csr_max_run_result%FOUND THEN
404 OPEN csr_result_value(l_tax_code_ipv_id);
405 FETCH csr_result_value INTO p_tax_code;
406 CLOSE csr_result_value;
407 --
408 OPEN csr_result_value(l_tax_basis_ipv_id);
409 FETCH csr_result_value INTO p_tax_basis;
410 CLOSE csr_result_value;
411 --
412 OPEN csr_result_value(l_pay_previous_ipv_id);
413 FETCH csr_result_value INTO p_prev_pay_details;
414 CLOSE csr_result_value;
415 --
416 OPEN csr_result_value(l_tax_previous_ipv_id);
417 FETCH csr_result_value INTO p_prev_tax_details;
418 CLOSE csr_result_value;
419 --
420 ELSE
421 -- set context for tax code database item , which is also
422 -- used for the remaining items, and retrieve the remaining items
423 --
424 if set_database_context (p_database_item => G_TAX_CODE_ITEM,
425 p_payroll_action_id => p_payroll_action_id,
426 p_assignment_id => p_assignment_id) then
427 --
428 hr_utility.trace('Getting G_TAX_CODE_ITEM.');
429 p_tax_code := database_item (G_TAX_CODE_ITEM);
430 --
431 p_tax_basis := database_item (G_TAX_BASIS_ITEM);
432 --
433 -- Tax Basis is translated into its meaning
434 --
435 -- ctucker: NO!
436 --p_tax_basis := hr_general.decode_lookup ('GB_TAX_BASIS', l_tax_basis);
437 --
438 p_prev_pay_details := database_item (G_PREV_PAY_DETAILS);
439 --
440 p_prev_tax_details := database_item (G_PREV_TAX_DETAILS);
441 --
442 end if;
443 END IF;
444 END;
445 end if;
446 end if;
447 END IF; -- payroll action not found.
448 --
449 EXCEPTION WHEN NO_DATA_FOUND THEN
450 --
451 p_tax_period := null;
452 p_tax_refno := null;
453 p_tax_code := null;
454 p_tax_basis := null;
455 p_prev_pay_details := null;
456 p_prev_tax_details := null;
457 --
458 END get_database_items;
459 --
460 ------------------------------------------------------------------------------
461 --
462 -- returns the defined balance ID associated with a given balance database
463 -- item - the balance is defined in terms of its type and the balance
464 -- dimension
465 --
466 function defined_balance_id (p_balance_type in varchar2,
467 p_dimension_suffix in varchar2) return number is
468 --
469 cursor c_defined_balance is
470 select defined_balance_id
471 from pay_defined_balances
472 --
473 where balance_type_id = (select balance_type_id
474 from pay_balance_types
475 where upper(balance_name) = p_balance_type
476 and legislation_code = 'GB')
477 --
478 and balance_dimension_id = (select balance_dimension_id
479 from pay_balance_dimensions
480 where upper(database_item_suffix) =
481 p_dimension_suffix);
482 --
483 l_result number;
484 --
485 begin
486 --
487 open c_defined_balance;
488 fetch c_defined_balance into l_result;
489 close c_defined_balance;
490 --
491 return l_result;
492 end;
493 --
494 ------------------------------------------------------------------------------
495 --
496 -- returns the value associated with a given balance database item
497 -- this is derived by translating the balance name into its balance type
498 -- and dimension
499 -- using the type and dimesnion to derive the defined balance ID
500 -- using the defined balance ID to obtain the current value for the balance
501 -- for the given assignment action ID
502 --
503 function balance_item_value (p_balance_name in varchar2,
504 p_assignment_action_id in number) return number is
505 --
506 l_balance_type varchar2(30);
507 l_dimension_suffix varchar2(30);
508 l_defined_balance_id number;
509 --
510 begin
511 --
512 if p_balance_name = G_GROSS_PAY_BALANCE then
513 --
514 l_balance_type := g_gross_pay_type;
515 l_dimension_suffix := g_tax_district_ytd ;
516 --
517 elsif p_balance_name = G_TAXABLE_PAY_BALANCE then
518 --
519 l_balance_type := g_taxable_pay_type;
520 l_dimension_suffix := g_tax_district_ytd;
521 --
522 elsif p_balance_name = G_AGG_GROSS_PAY_BALANCE then
523 --
524 l_balance_type := g_gross_pay_type;
525 l_dimension_suffix := g_agg_tax_district_ytd ;
526 --
527 elsif p_balance_name = G_AGG_TAXABLE_PAY_BALANCE then
528 --
529 l_balance_type := g_taxable_pay_type;
530 l_dimension_suffix := g_agg_tax_district_ytd;
531 --
532 end if;
533 --
534 -- derive defined balance ID
535 --
536 l_defined_balance_id := defined_balance_id
537 (p_balance_type => l_balance_type,
538 p_dimension_suffix => l_dimension_suffix);
539 --
540 return pay_balance_pkg.get_value
541 (p_defined_balance_id => l_defined_balance_id,
542 p_assignment_action_id => p_assignment_action_id);
543 --
544 end;
545 --
546 -------------------------------------------------------------------------------
547 -- Retrieves the balance items.
548 --
549 PROCEDURE get_balance_items (p_assignment_action_id in number,
550 p_gross_pay in out nocopy number,
551 p_taxable_pay in out nocopy number,
552 p_agg_paye_flag in varchar2 default null) IS
553 --
554 l_ni_a_employee_value number;
555 l_ni_b_employee_value number;
556 l_ni_d_employee_value number;
557 l_ni_e_employee_value number;
558 --
559 BEGIN
560 --
561 hr_utility.set_location('pay_p45_pkg2.get_balance_items',10);
562 -- if the assignment action id is not specified then do nothing.
563 -- this may have been set to -9999 to denote no action found.
564 --
565 if p_assignment_action_id is null or
566 p_assignment_action_id = -9999 then
567 --
568 hr_utility.trace('Assignment Action invalid, return');
569 return;
570 --
571 end if;
572 --
573 if p_agg_paye_flag = 'Y' then
574 -- Use the Person Level Balance names
575 p_gross_pay := balance_item_value
576 (p_balance_name => G_AGG_GROSS_PAY_BALANCE,
577 p_assignment_action_id => p_assignment_action_id);
578 --
579 p_taxable_pay := balance_item_value
580 (p_balance_name => G_AGG_TAXABLE_PAY_BALANCE,
581 p_assignment_action_id => p_assignment_action_id);
582 else
583 -- Use the assignment level balance names
584 p_gross_pay := balance_item_value
585 (p_balance_name => G_GROSS_PAY_BALANCE,
586 p_assignment_action_id => p_assignment_action_id);
587 --
588 p_taxable_pay := balance_item_value
589 (p_balance_name => G_TAXABLE_PAY_BALANCE,
590 p_assignment_action_id => p_assignment_action_id);
591 end if;
592 --
593 --
594 END;
595 --
596 -- Default Last Standard Process Date and Final Process Date
597 -- to Regular Payment Date for Current Period on Termination
598 -- Form. Called from HREMEA library. (Positive Offset Enhancement)
599 --
600 PROCEDURE get_uk_term_dates(p_person_id in number,
601 p_period_of_service_id in number,
602 p_act_term_date in date,
603 p_reg_pay_end_date out nocopy date) IS
604 --
605 -- Deliberately getting the end date of the period that the
606 -- regular payment date resides in as opposed to the
607 -- end date of the period for regular payment dates
608 -- to push Fianl Process date out further.
609 -- Called by HREMEA library.
610 --
611 cursor get_reg_pay_date_period is
612 select ptp2.end_date regular_payment_end_date
613 from per_time_periods ptp1,
614 per_time_periods ptp2
615 where p_act_term_date between ptp1.start_date and ptp1.end_date
616 and ptp1.payroll_id IN (select pa.payroll_id
617 from per_assignments pa
618 where pa.period_of_Service_id = p_period_of_service_id
619 and pa.person_id = p_person_id)
620 and ptp1.regular_payment_date between ptp2.start_date and ptp2.end_date
621 and ptp2.payroll_id IN (select pa2.payroll_id
622 from per_assignments pa2
623 where pa2.period_of_service_id = p_period_of_Service_id
624 and pa2.person_id = p_person_id);
625 --
626 l_pay_dates get_reg_pay_date_period%ROWTYPE;
627 BEGIN
628 open get_reg_pay_date_period;
629 fetch get_reg_pay_date_period into l_pay_dates;
630 close get_reg_pay_date_period;
631 p_reg_pay_end_date := l_pay_dates.regular_payment_end_date;
632 END;
633 END PAY_P45_PKG2;