1 package body pay_uk_bal_upload as
2 /* $Header: pyukupld.pkb 120.1 2005/07/11 06:17:26 npershad noship $ */
3 /*
4 Copyright (c) Oracle Corporation 1995 All rights reserved
5 PRODUCT
6 Oracle*Payroll
7 NAME
8 pyukupld.pkb
9 DESCRIPTION
10 Provides support for the upload of balances based on UK dimensions.
11 EXTERNAL
12 expiry_date
13 include_adjustment
14 is_supported
15 validate_batch_lines
16 INTERNAL
17 MODIFIED (DD-MON-YYYY)
18 40.0 J.S.Hobbs 16-May-1995 created.
19 40.2 A.Snell 03-Oct-1995 added director logic
20 40.3 N.Bristow 06-Oct-1995 ITD dimensions not supported
21 for balance upload.
22 40.5 N.Bristow 17-Oct-1995 Changes to support ITD balances.
23 40.6 N.Bristow 19-Oct-1995 Uncomment exit.
24 40.7 A.Snell 28-Feb-1996 Bug 345309 mid year starters
25 40.8 J.Alloun 30-JUL-1996 Added error handling.
26 40.9 C.Barbieri 13-AUG-1996 Added ASG_TD_ITD dimension.
27 40.10 C.Barbieri 28-Oct-1996 Changed User Balance Name
28 Convenction.
29 40.10 C.Barbieri 28-Oct-1996 Changed User Balance naming.
30 110.1 A.Mills 03-Nov-1997 568639 Corrected the way that
31 function expiry_date handles nulls
32 from csr_regular_payment cursor.
33 115.2 A.Mills 04-Apr-2001 PQP Addition of new dimension,
34 11i only, 2 yr expiry.
35 115.3 SKutteti 10-Apr-2001 Added code to take care of two
36 new dimensions : ASG_TD_ODD_TWO_YTD
37 and ASG_TD_EVEN_TWO_YTD
38 115.4 skutteti 11-Apr-2001 Fixed typo for the above changes
39 115.5 AMills 16-Oct-2001 2048418 Forward port of 665503.
40 115.6 AMills 25-Jun-2003 Added dbdrv commands.
41 115.7 AMills 15-Sep-2003 3140420 Changed expiry_date function
42 to ensure expiry in current
43 Tax Year for ytd section.
44 115.9 S.Rai 15-Nov-2003 3246437 Added code to support dimensions
45 _PER_TD_EVEN_TWO_YTD ,
46 _PER_TD_ODD_TWO_YTD and _PER_TD_YTD
47 115.10 A.Mills 05-Feb-2004 3418267 Changed expiry_date to not use
48 csr_regular_payment, changed
49 csr_proc_start_year.
50 115.11 A.Mills 27-May-2004 3655649 Changed return date for ASG_ITD
51 dimension to ensure there is a
52 valid time period.
53 115.12 A.Mills 04-Jun-2004 Changed csr_asg_itd_start to be
54 valid as long as time period start
55 is before the upload date, not
56 necessarily after asg start date.
57 115.13 npershad 10-jul-2005 4452262 Added code to support dimension
58 '_ELEMENT_CO_REF_ITD'.
59 */
60 --
61 -- Date constants.
62 --
63 START_OF_TIME constant date := to_date('01/01/0001','DD/MM/YYYY');
64 END_OF_TIME constant date := to_date('31/12/4712','DD/MM/YYYY');
65 --
66 -----------------------------------------------------------------------------
67 -- NAME
68 -- expiry_date
69 -- PURPOSE
70 -- Returns the expiry date of a given dimension relative to a date.
71 -- ARGUMENTS
72 -- p_upload_date - the date on which the balance should be correct.
73 -- p_dimension_name - the dimension being set.
74 -- p_assignment_id - the assignment involved.
75 -- p_original_entry_id - ORIGINAL_ENTRY_ID context.
76 -- USES
77 -- NOTES
78 -- This is used by pay_balance_upload.dim_expiry_date.
79 -- If the expiry date cannot be derived then it is set to the end of time
80 -- to indicate that a failure has occured. The process that uses the
81 -- expiry date knows this rule and acts accordingly.
82 -----------------------------------------------------------------------------
83 --
84 function expiry_date
85 (
86 p_upload_date date
87 ,p_dimension_name varchar2
88 ,p_assignment_id number
89 ,p_original_entry_id number
90 ) return date is
91 --
92 -- Returns the date on which the assignment transferred payroll prior to
93 -- the upload date NB. the payroll is the one the assignment is assigned to
94 -- on the upload date.
95 --
96 cursor csr_transfer_payroll
97 (
98 p_assignment_id number
99 ,p_upload_date date
100 ) is
101 select nvl(max(ASS.effective_start_date), START_OF_TIME)
102 from per_assignments_f ASS
103 ,per_assignments_f ASS2
104 where ASS.assignment_id = p_assignment_id
105 and ASS.effective_start_date <= p_upload_date
106 and ASS2.assignment_id = ASS.assignment_id
107 and ASS2.effective_end_date = (ASS.effective_start_date - 1)
108 and ASS2.payroll_id <> ASS.payroll_id;
109 --
110 -- Returns the earliest regular payment date for the payroll that lies
111 -- within the current tax year NB. the payroll is the one the assignment is
112 -- assigned to on the upload date.
113 --
114 cursor csr_proc_year_start
115 (
116 p_assignment_id number
117 ,p_upload_date date
118 ,p_stat_yr_start_date date
119 ) is
120 select nvl(min(PTP.regular_payment_date), END_OF_TIME)
121 from per_time_periods PTP
122 ,per_assignments_f ASS
123 where ASS.assignment_id = p_assignment_id
124 and p_upload_date between ASS.effective_start_date
125 and ASS.effective_end_date
126 and PTP.payroll_id = ASS.payroll_id
127 and PTP.regular_payment_date >= p_stat_yr_start_date;
128 --
129 -- Returns the start date of the current period on the upload date.
130 --
131 cursor csr_period_start
132 (
133 p_assignment_id number
134 ,p_upload_date date
135 ) is
136 select nvl(PTP.start_date, END_OF_TIME)
137 from per_time_periods PTP
138 ,per_assignments_f ASS
139 where ASS.assignment_id = p_assignment_id
140 and p_upload_date between ASS.effective_start_date
141 and ASS.effective_end_date
142 and PTP.payroll_id = ASS.payroll_id
143 and p_upload_date between PTP.start_date
144 and PTP.end_date;
145 --
146 -- Returns the Earliest date that can be used for uploading
147 -- for the assignment, therefore ensures that a time period
148 -- exists, and uses the greatest of the assignment start and
149 -- time period start. Used for ITD date, and as a minimum
150 -- for other dimensions.
151 --
152 cursor csr_asg_itd_start
153 (
154 p_assignment_id number
155 ,p_upload_date date
156 ) is
157 select nvl(greatest(min(ASS.effective_start_date),
158 min(PTP.start_date)), END_OF_TIME)
159 from per_assignments_f ASS
160 ,per_time_periods PTP
161 where ASS.assignment_id = p_assignment_id
162 and ASS.effective_start_date <= p_upload_date
163 and PTP.start_date <= p_upload_date
164 and PTP.payroll_id = ASS.payroll_id;
165 --
166 -- Returns the earliest date on which the element entry exists.
167 --
168 cursor csr_ele_itd_start
169 (
170 p_assignment_id number
171 ,p_upload_date date
172 ,p_original_entry_id number
173 ) is
174 select nvl(min(EE.effective_start_date), END_OF_TIME)
175 from pay_element_entries_f EE
176 where EE.assignment_id = p_assignment_id
177 and (EE.element_entry_id = p_original_entry_id or
178 EE.original_entry_id = p_original_entry_id)
179 and EE.effective_start_date <= p_upload_date;
180 --
181 -- Returns the date the employee became a director
182 -- if not a director(in the current year) then return END_OF_TIME
183 -- the date returned may be in the financial year or before it
184 cursor csr_appointment_as_director
185 (
186 p_assignment_id number
187 ,p_upload_date date
188 ,p_stat_yr_start_date date
189 ) is
190 select nvl(min(p.effective_start_date) ,END_OF_TIME)
191 from per_people_f p,
192 per_assignments_f ASS
193 where p.per_information2 = 'Y'
194 and ASS.assignment_id = p_assignment_id
195 and p_upload_date between
196 ASS.effective_start_date and ASS.effective_end_date
197 and ASS.person_id = P.person_id
198 and P.effective_start_date <= p_upload_date
199 and p.effective_end_date >= p_stat_yr_start_date ;
200 --
201 l_stat_yr_start_date date; -- The start of the tax year.
202 l_stat_prev_yr_start_date date; -- The start of the previous tax year.
203 l_transfer_payroll_date date; -- The date the assignment transferred
204 -- onto the current payroll.
205 l_stat_yr_proc_date date; -- earliest regular payment date for the
206 -- current payroll within the tax year.
207 l_period_start_date date; -- start date of the upload date period.
208 l_asg_itd_start_date date; -- The assignment start date.
209 l_ele_itd_start_date date; -- The earliest date an element entry exists.
210 l_director_start_date date; -- The date the director was appointed
211 l_date date; -- Temp date for Start of Tax Year.
212 l_regular_date date; -- Regular payment date after the expiry
213 l_expiry_date date; -- The expiry date of the dimension.
214 l_business_group_id number; -- The business_group of the dimension.
215 --
216 begin
217 --
218 -- Calculate the start of the tax year relative to the upload date. First
219 -- calculate the 6th April of the year the upload date falls in and then
220 -- see which side of this date the upload date falls. If it is on or after
221 -- the date then this is the current tax year start date, if it is before
222 -- the date then the current tax year start date is the 6th april of the
223 -- previous year.
224 -- PQP Addition, Do similar calculation for 2 year expiries, but
225 -- minus off another 1 year in relation to the YTD.
226 --
227 hr_utility.trace('Assignment ID: '||to_char(p_assignment_id));
228 hr_utility.trace('Dimension name: '||p_dimension_name);
229 --
230 l_date := to_date('06/04/' || to_char(p_upload_date,'YYYY'),'DD/MM/YYYY');
231 --
232 if p_upload_date >= l_date then
233 l_stat_yr_start_date := l_date;
234 elsif p_upload_date < l_date then
235 l_stat_yr_start_date := add_months(l_date,-12);
236 end if;
237 --
238 -- Calculate the expiry date for the specified dimension relative to the
239 -- upload date, taking into account any contexts where appropriate. Each of
240 -- the calculations also takes into account when the assignment is on a
241 -- payroll to ensure that a balance adjustment could be made at that point
242 -- if it were required.
243 --
244 -- What is the start date of the assignment ? All loading must come
245 -- after this date
246 --
247 open csr_asg_itd_start(p_assignment_id, p_upload_date);
248 fetch csr_asg_itd_start into l_asg_itd_start_date;
249 close csr_asg_itd_start;
250
251 if substr(p_dimension_name,31,4) = 'USER' then
252 -- User Balance
253 --
254 -- 665503 - Ensure single bgid returned.
255 -- Must select distinct rather than use effective
256 -- start and end date to ascertain singular
257 -- business group id.
258 --
259 SELECT DISTINCT business_group_id
260 INTO l_business_group_id
261 FROM per_assignments_f
262 WHERE assignment_id = p_assignment_id;
263
264 l_expiry_date := hr_gbbal.dimension_reset_date(
265 p_dimension_name,
266 p_upload_date,
267 l_business_group_id);
268 l_expiry_date := greatest(l_expiry_date, l_asg_itd_start_date);
269 --
270 -- added odd and even by skutteti
271 -- added _PER_TD_YTD and _PER_TD_EVEN_TWO_YTD and _PER_TD_ODD_TWO_YTD by saurai for bug fix 3246437
272 --
273 elsif p_dimension_name in ('_ASG_PROC_YTD', '_ASG_YTD',
274 '_ASG_TD_YTD', '_ASG_TD_EVEN_TWO_YTD',
275 '_ASG_TD_ODD_TWO_YTD','_PER_TD_YTD','_PER_TD_EVEN_TWO_YTD',
276 '_PER_TD_ODD_TWO_YTD') then
277
278 -- When did the assignment transfer onto the current payroll ?
279 --
280 open csr_transfer_payroll(p_assignment_id
281 ,p_upload_date);
282 fetch csr_transfer_payroll into l_transfer_payroll_date;
283 close csr_transfer_payroll;
284 --
285 -- added by skutteti
286 -- added by saurai,dimension _PER_TD_EVEN_TWO_YTD for bug fix 3246437
287 if p_dimension_name IN( '_ASG_TD_EVEN_TWO_YTD','_PER_TD_EVEN_TWO_YTD')then
288 if mod(to_number(to_char(l_stat_yr_start_date,'yyyy')),2) = 1 then
289 l_stat_yr_start_date := l_stat_yr_start_date;
290 else
291 l_stat_yr_start_date := add_months(l_stat_yr_start_date, -12);
292 end if;
293 -- added by saurai,dimension _PER_TD_ODD_TWO_YTD for bug fix 3246437
294 elsif p_dimension_name IN ('_ASG_TD_ODD_TWO_YTD','_PER_TD_ODD_TWO_YTD') then
295 if mod(to_number(to_char(l_stat_yr_start_date,'yyyy')),2) = 1 then
296 l_stat_yr_start_date := add_months(l_stat_yr_start_date, -12);
297 else
298 l_stat_yr_start_date := l_stat_yr_start_date;
299 end if;
300 end if;
301 --
302 -- What is the earliest regular payment date for the current payroll
303 -- within the current tax year ?
304 --
305 open csr_proc_year_start(p_assignment_id
306 ,p_upload_date
307 ,l_stat_yr_start_date);
308 fetch csr_proc_year_start into l_stat_yr_proc_date;
309 close csr_proc_year_start;
310 --
311 hr_utility.trace('proc yr start: '||to_char(l_stat_yr_proc_date));
312 --
313 -- The expiry date must lie within the processing tax year for the
314 -- current payroll and at a time when the assignment belongs to the
315 -- current payroll.
316 --
317 l_expiry_date := greatest(l_transfer_payroll_date, l_stat_yr_proc_date
318 ,l_asg_itd_start_date, l_stat_yr_start_date);
319 --
320 -- Calculate expiry date for _ASG_STAT_YTD dimension.
321 --
322 elsif p_dimension_name = '_ASG_STAT_YTD' then
323 l_expiry_date := greatest(l_stat_yr_start_date,l_asg_itd_start_date);
324 --
325 -- Calculate expiry date for _ASG_PROC_PTD dimension.
326 --
327 elsif p_dimension_name = '_ASG_PROC_PTD' then
328 --
329 -- What is the current period start date ?
330 --
331 open csr_period_start(p_assignment_id
332 ,p_upload_date);
333 fetch csr_period_start into l_period_start_date;
334 close csr_period_start;
335 --
336 hr_utility.trace('Period start: '||to_char(l_period_start_date));
337 -- Set the expiry date. This is the later of the period start date,
338 -- the assignment start date or the Start of tax year, incase the period
339 -- begins before the tax year end (e.g. 01-30 Apr).
340 --
341 l_expiry_date := greatest(l_stat_yr_start_date,l_period_start_date,
345 --
342 l_asg_itd_start_date);
343 --
344 -- Calculate expiry date for _ASG_ITD dimension.
346 elsif p_dimension_name in ('_ASG_ITD','_ASG_TD_ITD') then
347 --
348 -- Use the greater of the assignments start date or the tfr to
349 -- payroll date, as cannot do adjustments if current payroll did
350 -- not exist at start of assignment and asg transferred.
351 --
352 open csr_transfer_payroll(p_assignment_id
353 ,p_upload_date);
354 fetch csr_transfer_payroll into l_transfer_payroll_date;
355 close csr_transfer_payroll;
356
357 l_expiry_date := greatest(l_transfer_payroll_date,l_asg_itd_start_date);
358
359 elsif p_dimension_name in ('_ELEMENT_ITD', '_ELEMENT_CO_REF_ITD') then
360 --
361 -- What is the earliest date the element entry exists ?
362 --
363 open csr_ele_itd_start(p_assignment_id
364 ,p_upload_date
365 ,p_original_entry_id);
366 fetch csr_ele_itd_start into l_ele_itd_start_date;
367 close csr_ele_itd_start;
368 --
369 -- Set the expiry date.
370 --
371 l_expiry_date := greatest(l_ele_itd_start_date,l_asg_itd_start_date);
372 --
373 elsif p_dimension_name = '_PER_TD_DIR_YTD' then
374 --
375 -- When did the assignment transfer onto the current payroll ?
376 --
377 open csr_transfer_payroll(p_assignment_id
378 ,p_upload_date);
379 fetch csr_transfer_payroll into l_transfer_payroll_date;
380 close csr_transfer_payroll;
381 --
382 -- What is the earliest regular payment date for the current payroll
383 -- within the current tax year ?
384 --
385 open csr_proc_year_start(p_assignment_id
386 ,p_upload_date
387 ,l_stat_yr_start_date);
388 fetch csr_proc_year_start into l_stat_yr_proc_date;
389 close csr_proc_year_start;
390 --
391 hr_utility.trace('proc yr start: '||to_char(l_stat_yr_proc_date));
392 -- What is the edate of appointment as a director
393 --
394 open csr_appointment_as_director(p_assignment_id
395 ,p_upload_date
396 ,l_stat_yr_start_date);
397 fetch csr_appointment_as_director into l_director_start_date;
398 close csr_appointment_as_director;
399 --
400 -- The expiry date must lie within the processing tax year for the
401 -- current payroll and at a time when the assignment belongs to the
402 -- current payroll and since the appointment as director.
403 --
404 l_expiry_date := greatest(l_transfer_payroll_date, l_stat_yr_proc_date,
405 l_director_start_date,l_asg_itd_start_date);
406 --
407 end if;
408 --
409 -- Return the date on which the dimension expires. If this has not been
410 -- set due to a cursor above not finding the correct info, set this to
411 -- End Of Time. The core process will then fail this upload.
412 --
413 hr_utility.trace('Returned date: '||to_char(l_expiry_date));
414 --
415 IF l_expiry_date is null then
416 --
417 l_expiry_date := END_OF_TIME;
418 --
419 END IF;
420 --
421 return (l_expiry_date);
422 --
423 end expiry_date;
424 --
425 -----------------------------------------------------------------------------
426 -- NAME
427 -- is_supported
428 -- PURPOSE
429 -- Checks if the dimension is supported by the upload process.
430 -- ARGUMENTS
431 -- p_dimension_name - the balance dimension to be checked.
432 -- USES
433 -- NOTES
434 -- Only a subset of the UK dimensions are supported and these have been
435 -- picked to allow effective migration to release 10.
436 -- This is used by pay_balance_upload.validate_dimension.
437 -----------------------------------------------------------------------------
438 --
439 function is_supported
440 (
441 p_dimension_name varchar2
442 ) return boolean is
443 begin
444 --
445 hr_utility.trace('Entering pay_uk_bal_upload.is_supported');
446 --
447 -- See if the dimension is supported.
448 --
449 if p_dimension_name in
450 ('_ASG_PROC_YTD'
451 ,'_ASG_YTD'
452 ,'_ASG_TD_YTD'
453 ,'_ASG_STAT_YTD'
454 ,'_PER_TD_DIR_YTD'
455 ,'_ASG_PROC_PTD'
456 ,'_ASG_ITD'
457 ,'_ASG_TD_ITD'
458 ,'_ELEMENT_ITD'
459 -- added by skutteti
460 ,'_ASG_TD_EVEN_TWO_YTD'
461 ,'_ASG_TD_ODD_TWO_YTD'
462 -- added by saurai for bug fix 3246437
463 ,'_PER_TD_EVEN_TWO_YTD'
464 ,'_PER_TD_ODD_TWO_YTD'
465 ,'_PER_TD_YTD'
466 ,'_ELEMENT_CO_REF_ITD'
467 )
468 OR
469 (
470 substr(p_dimension_name,31,4) = 'USER'
471 AND
475 return (TRUE);
472 substr(p_dimension_name,40,3) = 'ASG'
473 )
474 then
476 else
477 return (FALSE);
478 end if;
479 --
480 hr_utility.trace('Exiting pay_uk_bal_upload.is_supported');
481 --
482 end is_supported;
483 --
484 -----------------------------------------------------------------------------
485 -- NAME
486 -- include_adjustment
487 -- PURPOSE
488 -- Given a dimension, and relevant contexts and details of an existing
489 -- balanmce adjustment, it will find out if the balance adjustment effects
493 -- p_balance_type_id - the balance to be set.
490 -- the dimension to be set. Both the dimension to be set and the adjustment
491 -- are for the same assignment and balance.
492 -- ARGUMENTS
494 -- p_dimension_name - the balance dimension to be set.
495 -- p_original_entry_id - ORIGINAL_ENTRY_ID context.
496 -- p_bal_adjustment_rec - details of an existing balance adjustment.
497 -- USES
498 -- NOTES
499 -- This is used by pay_balance_upload.get_current_value.
500 -----------------------------------------------------------------------------
501 --
502 function include_adjustment
503 (
504 p_balance_type_id number
505 ,p_dimension_name varchar2
506 ,p_original_entry_id number
507 ,p_bal_adjustment_rec pay_balance_upload.csr_balance_adjustment%rowtype
508 ) return boolean is
509 --
510 ret_val boolean;
511 begin
512 --
513 hr_utility.trace('Entering pay_uk_bal_upload.include_adjustment');
514 --
515 if (p_original_entry_id = p_bal_adjustment_rec.original_entry_id) or
516 (p_original_entry_id is null
517 and p_bal_adjustment_rec.original_entry_id is null) then
518 ret_val := TRUE;
519 else
520 ret_val := FALSE;
521 end if;
522 hr_utility.trace('Exiting pay_uk_bal_upload.include_adjustment');
523 --
524 return (ret_val);
525 --
526 end include_adjustment;
527 --
528 -----------------------------------------------------------------------------
529 -- NAME
530 -- validate_batch_lines
531 -- PURPOSE
532 -- Applies UK specific validation to the batch.
533 -- ARGUMENTS
534 -- p_batch_id - the batch to be validate_batch_linesd.
535 -- USES
536 -- NOTES
537 -- This is used by pay_balance_upload.validate_batch_lines.
538 -----------------------------------------------------------------------------
539 --
540 procedure validate_batch_lines
541 (
542 p_batch_id number
543 ) is
544 begin
545 --
546 hr_utility.trace('Entering pay_uk_bal_upload.validate_batch_lines');
547 --
548 hr_utility.trace('Exiting pay_uk_bal_upload.validate_batch_lines');
549 --
550 end validate_batch_lines;
551 --
552 end pay_uk_bal_upload;
[Home] [Help]