1 package body pay_ie_bal_upload as
2 /* $Header: pyieupld.pkb 120.1 2006/06/09 09:31:45 vikgupta noship $ */
3 /*
4 Copyright (c) Oracle Corporation 1995 All rights reserved
5 PRODUCT
6 Oracle*Payroll
7 NAME
8 pyieupld.pkb
9 DESCRIPTION
13 include_adjustment
10 Provides support for the upload of balances based on IE dimensions.
11 EXTERNAL
12 expiry_date
14 is_supported
15 validate_batch_lines
16 INTERNAL
17 MODIFIED (DD-MON-YYYY)
18 115.0 vnatari 31-Jan-2002 created.
19 115.1 vmkhande 11-mar-2003 fixed bug 2836853.
20 115.2 vmkhande 16-apr-2003 Added support for
21 ASG_QTD
22 115.3 viviswan 02-may-2003 2933807 Added support for
23 _ELEMENT_ITD
24 115.4 vmkhande 01-sep-2003 Added logic to
25 include_adjustment
26 115.5 vmkhande 27-JAN-2004 Added support for
27 _ELEMENT_YTD
28 115.6 vikgupta 31-MAY-2006 fixed bug 5258159
29 */
30 --
31 -- Date constants.
32 --
33 START_OF_TIME constant date := to_date('01/01/0001','DD/MM/YYYY');
34 END_OF_TIME constant date := to_date('31/12/4712','DD/MM/YYYY');
35 -----------------------------------------------------------------------------
36 -- NAME
37 -- expiry_date
38 -- PURPOSE
39 -- Returns the expiry date of a given dimension relative to a date.
40 -- ARGUMENTS
41 -- p_upload_date - the date on which the balance should be correct.
42 -- p_dimension_name - the dimension being set.
43 -- p_assignment_id - the assignment involved.
44 -- p_original_entry_id - ORIGINAL_ENTRY_ID context.
45 -- USES
46 -- NOTES
47 -- This is used by pay_balance_upload.dim_expiry_date.
48 -- If the expiry date cannot be derived then it is set to the end of time
49 -- to indicate that a failure has occured. The process that uses the
50 -- expiry date knows this rule and acts accordingly.
51 -----------------------------------------------------------------------------
52 function expiry_date
53 (
54 p_upload_date date,
55 p_dimension_name varchar2,
56 p_assignment_id number,
57 p_original_entry_id number
58 ) return date is
59 -- Returns the legislative start date
60 cursor csr_tax_start_date is
61 select rule_mode
62 from pay_legislation_rules
63 where legislation_code='IE'
64 and rule_type = 'L';
65 -- Holds the legislative start date
66 l_leg_start pay_legislation_rules.rule_mode%TYPE;
67 -- Returns the start date of the first period of the tax year in which
68 -- the upload date falls.
69 -- 2836853.
70 -- csr_tax_year_start is incorrect .
71 -- 1) to_char(to_date(p_upload_date,'DD/MM/YYYY'),'YYYY')
72 -- retuned incorrect output depening on the date setting
73 -- of the env. to_date(p_upload_date,'DD/MM/YYYY') is not needed as
74 -- p_upload_date is a date!
75 -- 2) if the upload it being done on say 01-jan-2003, it would return null
76 -- record as below condition will not be
77 -- fulfilled
78 -- ptp2.start_date between
79 -- to_date(l_leg_start||to_char(to_date(
80 -- p_upload_date,'DD/MM/YYYY'),'YYYY'),'DD/MM/YYYY')
81 -- and ptp.end_date;
82 -- But due to 1, condition 2 did not happen and it returned the
83 -- first period of the payroll which is incorrect.
84 -- if 1 is fixed , then condtion 2 would occur.
85 -- the above errors resulted in payroll_action_id's being
86 -- created with incorrect effective date This is now changed such that the
87 -- expiry date is the greatest of the tax year start date 01/01/YYYY
88 -- the payroll_start_date, and the assignment start date.
89 /*
90 cursor csr_tax_year_start
91 (
92 p_assignment_id number,
93 p_upload_date date
94 ) is
95 select
96 nvl(min(ptp2.start_date), END_OF_TIME)
97 from
98 per_time_periods ptp,per_time_periods ptp2,per_assignments_f ass
99 where
100 ass.assignment_id = p_assignment_id
101 and
102 p_upload_date between ass.effective_start_date and ass.effective_end_date
103 and
104 ptp.payroll_id = ass.payroll_id
105 and
106 ptp2.payroll_id = ptp.payroll_id
107 and
108 p_upload_date between ptp.start_date and ptp.end_date
109 and
110 ptp2.start_date between to_date(l_leg_start||to_char(to_date(p_upload_date,'DD/MM/YYYY'),'YYYY'),'DD/MM/YYYY')
111 and ptp.end_date;
112 */
113 -- Returns the start date of the current period on the upload date.
114 cursor csr_period_start
115 (
116 p_assignment_id number,
117 p_upload_date date
118 ) is
119 select
120 nvl(ptp.start_date, END_OF_TIME)
121 from
122 per_time_periods ptp, per_assignments_f ass
123 where
124 ass.assignment_id = p_assignment_id
125 and
126 ptp.payroll_id = ass.payroll_id
127 and
128 p_upload_date between ass.effective_start_date and ass.effective_end_date
129 and
130 p_upload_date between ptp.start_date and ptp.end_date;
131 -- Returns the start date of the assignment.
132 cursor csr_asg_itd_start
133 (
134 p_assignment_id number,
135 p_upload_date date
136 ) is
137 select
138 nvl(min(ass.effective_start_date), END_OF_TIME)
139 from
140 per_assignments_f ass
141 where
142 ass.assignment_id = p_assignment_id
143 and
144 ass.payroll_id is not null
145 and
146 ass.effective_start_date <= p_upload_date;
147 -- This cursor takes the assignment, the expiry_date and the upload_date
151 /*
148 -- and returns the next regular_payment_date after the expiry_date for
149 -- that particular payroll.
150 -- unnecessary code!
152 cursor csr_regular_payment
153 (
154 l_assignment_id number,
155 l_upload_date date,
156 l_expiry_date date
157 ) is
158 select
159 min(ptp.regular_payment_date)
160 from
161 per_time_periods ptp, per_assignments_f ass
162 where
163 ass.assignment_id = l_assignment_id
164 and
165 ptp.payroll_id = ass.payroll_id
166 and
167 l_upload_date between ass.effective_start_date and ass.effective_end_date
168 and
169 ptp.regular_payment_date between l_expiry_date and l_upload_date;
170 -- This cursor takes the assignment, the expiry_date and the upload_date
171 -- and returns the next regular_payment_date after the expiry_date for
172 -- that particular payroll.
173 cursor csr_regular_payment2
174 (
175 l_assignment_id number,
176 l_upload_date date,
177 l_expiry_date date
178 ) is
179 select
180 ptp.regular_payment_date
181 from
182 per_time_periods ptp, per_assignments_f ass
183 where
184 ass.assignment_id = l_assignment_id
185 and
186 ptp.payroll_id = ass.payroll_id
187 and
188 l_upload_date between ass.effective_start_date and ass.effective_end_date
189 and
190 l_expiry_date between start_date and end_date;
191 */
192 -- Generic start date variable.
193 l_start_date date;
194 -- Holds the assignment start date.
195 l_asg_itd_start_date date;
196 -- Holds the first regular payment date after the expiry date of the dimension.
197 l_regular_date date;
198 -- Holds the expiry date of the dimension.
199 l_expiry_date date;
200 -- Holds the business group of the dimension.
201 l_business_group_id number;
202 -- Holds the start date of the quarter.
203 l_qtr_start_date date;
204 -- Holds theearliest date an element entry
205 l_ele_itd_start_date date;
206 cursor csr_payroll_start_date (
207 p_assignment_id number,
208 p_upload_date date
209 ) is
210 select
211 nvl(ppf.effective_start_date, END_OF_TIME)
212 from
213 per_all_assignments_f ass,
214 pay_all_payrolls_f ppf
215 where
216 ass.assignment_id = p_assignment_id
217 and p_upload_date between
218 nvl(ass.effective_start_date,p_upload_date) and
219 nvl(ass.effective_end_date,p_upload_date)
220 and ppf.payroll_id = ass.payroll_id
221 and p_upload_date between
222 nvl(ppf.effective_start_date,p_upload_date) and
223 nvl(ppf.effective_end_date,p_upload_date);
224 --
225 -- Bug 2933807 - Added _ELEMENT_ITD Dimension Support
226 -- Returns the earliest date on which the element entry exists.
227 --
228 cursor csr_ele_itd_start
229 (
230 p_assignment_id number
231 ,p_upload_date date
232 ,p_original_entry_id number
233 ) is
234 select nvl(min(EE.effective_start_date), END_OF_TIME)
235 from pay_element_entries_f EE
236 where EE.assignment_id = p_assignment_id
237 and (EE.element_entry_id = p_original_entry_id or
238 EE.original_entry_id = p_original_entry_id)
239 and EE.effective_start_date <= p_upload_date;
240 --
241 l_tax_year date;
242 --------------------------------------------------------------------------------------
243 begin -- Expiry_Date - Main --
244 --------------------------------------------------------------------------------------
245 -- What is the start date of the assignment? All loading must come after this date.
246 -- HR_UTILITY.trace_on(null,'BIK');
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 hr_utility.trace('l_asg_itd_start_date' || to_char(l_asg_itd_start_date,'DD-MON-YYYY'));
251 -- Return the date on which the dimension expires.
252 if substr(p_dimension_name, 31, 4) = 'USER' then
253 -- User balance
254 select
255 distinct business_group_id -- for bug 5258159 added distinct clause.
256 into
257 l_business_group_id
258 from
259 per_assignments_f
260 where
261 assignment_id = p_assignment_id;
262 l_expiry_date := hr_gbbal.dimension_reset_date
263 (
264 p_dimension_name,
265 p_upload_date,
266 l_business_group_id
267 );
268 l_expiry_date := greatest(l_expiry_date, l_asg_itd_start_date);
269 elsif p_dimension_name in ('_ASG_PTD', '_ASG_PRSI_PTD') then
270 -- Calculate expiry date for _ASG_PTD, _ASG_PRSI_PTD dimension.
271 -- What is the current period start date?
272 open csr_period_start(p_assignment_id, p_upload_date);
273 fetch csr_period_start into l_start_date;
274 close csr_period_start;
275 l_expiry_date := greatest(l_start_date, l_asg_itd_start_date);
276 elsif p_dimension_name in ('_ASG_YTD', '_ASG_PRSI_YTD') then
277 open csr_tax_start_date;
278 fetch csr_tax_start_date into l_leg_start;
279 close csr_tax_start_date;
280 -- Calculate expiry date for _ASG_YTD and _ASG_PRSI_YTD dimension.
284 fetch csr_tax_year_start into l_start_date;
281 -- What is the current tax year start date?
282 /*
283 open csr_tax_year_start(p_assignment_id, p_upload_date);
285 close csr_tax_year_start;
286 */
287 -- calculate the the payroll start date
288 open csr_payroll_start_date(p_assignment_id, p_upload_date);
289 fetch csr_payroll_start_date into l_start_date;
290 close csr_payroll_start_date;
291 hr_utility.trace('l_start_date' || to_char(l_start_date,'DD-MON-YYYY'));
292 -- calculate the tac year start date for the upload process
293 l_tax_year := to_date(l_leg_start || to_char(p_upload_date,'YYYY'),'DD/MM/YYYY');
294 hr_utility.trace('l_tax_year' || to_char(l_tax_year,'DD-MON-YYYY'));
295 -- calculate the expiry date
296 l_expiry_date := greatest(l_start_date, l_asg_itd_start_date,
297 l_tax_year );
298 hr_utility.trace('l_expiry_date ' || to_char(l_expiry_date,'DD-MON-YYYY'));
299 elsif p_dimension_name in ('_ASG_QTD') then
300 -- calculate the qtr start date
301 l_qtr_start_date := trunc(p_upload_date,'Q');
302 -- calculate the the payroll start date
303 open csr_payroll_start_date(p_assignment_id, p_upload_date);
304 fetch csr_payroll_start_date into l_start_date;
305 close csr_payroll_start_date;
306 hr_utility.trace('l_start_date' || to_char(l_start_date,'DD-MON-YYYY'));
307 -- calculate the expiry date
308 l_expiry_date := greatest(l_start_date, l_asg_itd_start_date,
309 l_qtr_start_date );
310 hr_utility.trace('l_expiry_date ' || to_char(l_expiry_date,'DD-MON-YYYY'));
311 elsif p_dimension_name in ('_PAYMENTS','_ASG_ITD') then
312 -- Calculate expiry date for _PAYMENTS and '_ASG_ITD' dimensions.
313 l_expiry_date := l_asg_itd_start_date;
314 elsif p_dimension_name in ('_ELEMENT_ITD','_ELEMENT_YTD') then
315 --
316 -- Bug 2933807 - Added _ELEMENT_ITD Dimension Support
317 -- Calculate expiry date for _ELEMENT_ITD dimensions.
318 --
319 open csr_ele_itd_start(p_assignment_id
320 ,p_upload_date
321 ,p_original_entry_id);
322 fetch csr_ele_itd_start into l_ele_itd_start_date;
323 close csr_ele_itd_start;
324 -- Set the expiry date.
325 open csr_period_start(p_assignment_id, p_upload_date);
326 fetch csr_period_start into l_start_date;
327 close csr_period_start;
328
329 l_expiry_date := greatest(l_ele_itd_start_date,l_asg_itd_start_date,l_start_date);
330 HR_UTILITY.trace('l_expiry_date ' || to_char(l_expiry_date,'dd-mon-yyyy'));
331 --
332 end if;
333 -- HR_UTILITY.TRACE_OFF;
334 return nvl(l_expiry_date,END_OF_TIME);
335 exception
336 when no_data_found then
337 l_expiry_date := END_OF_TIME;
338 return l_expiry_date;
339 end expiry_date;
340 -----------------------------------------------------------------------------
341 -- NAME
342 -- is_supported
343 -- PURPOSE
344 -- Checks if the dimension is supported by the upload process.
345 -- ARGUMENTS
346 -- p_dimension_name - the balance dimension to be checked.
347 -- USES
348 -- NOTES
349 -- Only a subset of the IE dimensions are supported.
350 -- This is used by pay_balance_upload.validate_dimension.
351 -----------------------------------------------------------------------------
352 function is_supported
353 (
354 p_dimension_name varchar2
355 ) return number is
356 begin
357 -- hr_utility.trace_on(null,'BIK');
358 hr_utility.trace('Entering pay_ie_bal_upload.is_supported stub');
359 -- Bug 2933807 - Added _ELEMENT_ITD Dimension
360 -- See if the dimension is supported.
361 if p_dimension_name in
362 (
363 '_ASG_YTD',
364 '_ASG_PTD',
365 '_ASG_PRSI_YTD',
366 '_ASG_PRSI_PTD',
367 '_PAYMENTS',
368 '_ELEMENT_ITD',
369 '_ASG_ITD',
370 '_ASG_QTD',
371 '_ELEMENT_YTD'
372 )
373 or
374 (
375 substr(p_dimension_name, 31, 4) = 'USER'
376 and
377 substr(p_dimension_name, 40, 3) = 'ASG'
378 )
379 then
380 return 1;
381 else
382 return 0;
383 end if;
384 hr_utility.trace('Exiting pay_ie_bal_upload.is_supported stub');
385 end is_supported;
386 -----------------------------------------------------------------------------
387 -- NAME
388 -- include_adjustment
389 -- PURPOSE
390 -- Given a dimension, and relevant contexts and details of an existing
391 -- balance adjustment, it will find out if the balance adjustment effects
392 -- the dimension to be set. Both the dimension to be set and the adjustment
393 -- are for the same assignment and balance.
394 -- ARGUMENTS
395 -- p_balance_type_id - the balance to be set.
396 -- p_dimension_name - the balance dimension to be set.
397 -- p_original_entry_id - ORIGINAL_ENTRY_ID context.
398 -- p_bal_adjustment_rec - details of an existing balance adjustment.
399 -- p_test_batch_line_id -
400 -- USES
401 -- NOTES
402 -- This is used by pay_balance_upload.get_current_value.
403 -----------------------------------------------------------------------------
404 function include_adjustment
405 (
406 p_balance_type_id number,
407 p_dimension_name varchar2,
411 p_test_batch_line_id number
408 p_original_entry_id number,
409 p_upload_date date,
410 p_batch_line_id number,
412 ) return number is
413 CURSOR csr_bal_adj_source_text (p_test_batch_line_id NUMBER, p_batch_line_id NUMBER) IS
414 SELECT tba.SOURCE_TEXT
415 FROM pay_temp_balance_adjustments tba,
416 pay_balance_batch_lines bbl
417 WHERE tba.batch_line_id = p_test_batch_line_id
418 AND bbl.batch_line_id = p_batch_line_id
419 AND tba.SOURCE_TEXT like nvl(bbl.SOURCE_TEXT,'%');
420 -- Note above: included the like condiditon as for PRSI balances
421 -- ASG_YTd dim source text will be null! as a result
422 -- it could mean that bal adj does not happen, which would be incorrect
423 -- we should let the bal adj happen for ASG_YTD and ASG_PTD
424
425 CURSOR csr_bal_adj_orig_entry_id (p_test_batch_line_id NUMBER, p_batch_line_id NUMBER) IS
426 SELECT tba.original_entry_id
427 FROM pay_temp_balance_adjustments tba,
428 pay_balance_batch_lines bbl
429 WHERE tba.batch_line_id = p_test_batch_line_id
430 AND bbl.batch_line_id = p_batch_line_id
431 AND tba.original_entry_id = bbl.original_entry_id;
432
433 l_source_text varchar2(10);
434 l_return Number := 1;--True
435 l_original_entry_id Number;
436 Begin
437 hr_utility.trace('Entering pay_ie_bal_upload.include_adjustment stub');
438 Open csr_bal_adj_source_text(p_test_batch_line_id,p_batch_line_id);
439 FETCH csr_bal_adj_source_text INTO l_source_text;
440 IF csr_bal_adj_source_text%NOTFOUND THEN
441 l_return := 0; -- false
442 END IF;
443 CLOSE csr_bal_adj_source_text;
444 -- the below will ensure that bal adjustment is done if the
445 -- original entry_id is same.
446 If p_dimension_name in ('_ELEMENT_YTD','_ELEMENT_ITD')
447 Then
448 Open csr_bal_adj_orig_entry_id(p_test_batch_line_id,p_batch_line_id);
449 Fetch csr_bal_adj_orig_entry_id into l_original_entry_id;
450 If csr_bal_adj_orig_entry_id%NOTFOUND then
451 l_return := 0; -- false
452 End If;
453 Close csr_bal_adj_orig_entry_id;
454 End if;
455 hr_utility.trace('Exiting pay_ie_bal_upload.include_adjustment l_return:' ||l_return );
456 Return l_return;
457 End include_adjustment;
458 -----------------------------------------------------------------------------
459 -- NAME
460 -- validate_batch_lines
461 -- PURPOSE
462 -- Applies IE specific validation to the batch.
463 -- ARGUMENTS
464 -- p_batch_id - the batch to be validate_batch_linesd.
465 -- USES
466 -- NOTES
467 -- This is used by pay_balance_upload.validate_batch_lines.
468 -----------------------------------------------------------------------------
469 procedure validate_batch_lines
470 (
471 p_batch_id number
472 ) is
473 begin
474 hr_utility.trace('Entering pay_ie_bal_upload.validate_batch_lines stub');
475 hr_utility.trace('Exiting pay_ie_bal_upload.validate_batch_lines stub');
476 end validate_batch_lines;
477 end pay_ie_bal_upload;
[Home] [Help]