4 -- Date constants.
1 package body pay_nl_bal_upload as
2 /* $Header: pynlupld.pkb 115.3 2003/09/15 23:13:56 karajago noship $ */
3 --
5 --
6 START_OF_TIME constant date := to_date('01/01/0001','DD/MM/YYYY');
7 END_OF_TIME constant date := to_date('31/12/4712','DD/MM/YYYY');
11 -- expiry_date
8
9 -----------------------------------------------------------------------------
10 -- NAME
12 -- PURPOSE
13 -- Returns the expiry date of a given dimension relative to a date.
14 -- ARGUMENTS
15 -- p_upload_date - the date on which the balance should be correct.
16 -- p_dimension_name - the dimension being set.
17 -- p_assignment_id - the assignment involved.
18 -- p_original_entry_id - ORIGINAL_ENTRY_ID context.
19 -- USES
20 -- NOTES
21 -- This is used by pay_balance_upload.dim_expiry_date.
22 -- If the expiry date cannot be derived then it is set to the end of time
23 -- to indicate that a failure has occured. The process that uses the
24 -- expiry date knows this rule and acts accordingly.
25 -----------------------------------------------------------------------------
26 function expiry_date
27 (
28 p_upload_date date,
29 p_dimension_name varchar2,
30 p_assignment_id number,
31 p_original_entry_id number
32 ) return date is
33
34 -- Returns the legislative start date
35 cursor csr_tax_start_date is
36 select rule_mode
37 from pay_legislation_rules
38 where legislation_code='NL'
39 and rule_type = 'L';
40
41 -- Holds the legislative start date
42 l_leg_start pay_legislation_rules.rule_mode%TYPE;
43
44 -- Returns the start date of the first period of the tax year in which
45 -- the upload date falls.
46
47 -- 1) to_char(to_date(p_upload_date,'DD/MM/YYYY'),'YYYY')
48 -- retuned incorrect output depening on the date setting
49 -- of the env. to_date(p_upload_date,'DD/MM/YYYY') is not needed as
50 -- p_upload_date is a date!
51 -- 2) if the upload it being done on say 01-jan-2003, it would return null
52 -- record as below condition will not be fulfilled
53 -- ptp2.start_date between
54 -- to_date(l_leg_start||to_char(to_date(
55 -- p_upload_date,'DD/MM/YYYY'),'YYYY'),'DD/MM/YYYY')
56 -- and ptp.end_date;
57 -- But due to 1, condition 2 did not happen and it returned the
58 -- first period of the payroll which is incorrect.
59 -- if 1 is fixed , then condtion 2 would occur.
60
61
62 -- Returns the start date of the current period on the upload date.
63 cursor csr_period_start
64 (
65 p_assignment_id number,
66 p_upload_date date
67 ) is
68 select
69 nvl(ptp.start_date, END_OF_TIME)
70 from
71 per_time_periods ptp, per_assignments_f ass
72 where
73 ass.assignment_id = p_assignment_id
74 and
75 ptp.payroll_id = ass.payroll_id
76 and
77 p_upload_date between ass.effective_start_date and ass.effective_end_date
78 and
79 p_upload_date between ptp.start_date and ptp.end_date;
80
81 -- Returns the start date of the assignment.
82 cursor csr_asg_itd_start
83 (
84 p_assignment_id number,
85 p_upload_date date
86 ) is
87 select
88 nvl(min(ass.effective_start_date), END_OF_TIME)
89 from
90 per_assignments_f ass
91 where
92 ass.assignment_id = p_assignment_id
93 and
94 ass.payroll_id is not null
95 and
96 ass.effective_start_date <= p_upload_date;
97
98 -- Generic start date variable.
99 l_start_date date;
100
101 -- Holds the assignment start date.
102 l_asg_itd_start_date date;
103
104 --Holds the LQTD start date.
105 l_lqtd_start_date date;
106
107 --Holds month start date
108 l_month_start_date date;
109
110 -- Holds the first regular payment date after the expiry date of the dimension.
111 l_regular_date date;
112
113 -- Holds the expiry date of the dimension.
114 l_expiry_date date;
115
116 -- Holds the business group of the dimension.
117 l_business_group_id number;
118
119 -- Holds the start date of the quarter.
120 l_qtr_start_date date;
121
122 -- Holds theearliest date an element entry
123 l_ele_itd_start_date date;
124
125 --Used for _ASG_LQTD expiry date calculation
126 begin_date date;
127 end_date date;
128
129 cursor csr_payroll_start_date (
130 p_assignment_id number,
131 p_upload_date date
132 ) is
133 select
134 nvl(ppf.effective_start_date, END_OF_TIME)
135 from
136 per_all_assignments_f ass,
137 pay_all_payrolls_f ppf
138 where
139 ass.assignment_id = p_assignment_id
140 and p_upload_date between
141 nvl(ass.effective_start_date,p_upload_date) and
142 nvl(ass.effective_end_date,p_upload_date)
143 and ppf.payroll_id = ass.payroll_id
144 and p_upload_date between
145 nvl(ppf.effective_start_date,p_upload_date) and
146 nvl(ppf.effective_end_date,p_upload_date);
147
148 --
149 -- Returns the earliest date on which the element entry exists.
150 --
151 cursor csr_ele_itd_start
152 (
153 p_assignment_id number
154 ,p_upload_date date
155 ,p_original_entry_id number
156 ) is
157 select nvl(min(EE.effective_start_date), END_OF_TIME)
161 EE.original_entry_id = p_original_entry_id)
158 from pay_element_entries_f EE
159 where EE.assignment_id = p_assignment_id
160 and (EE.element_entry_id = p_original_entry_id or
162 and EE.effective_start_date <= p_upload_date;
163 --
164
165 --Holds the tax year start date for the upload process
166 l_tax_year date;
167 --------------------------------------------------------------------------------------
168 begin -- Expiry_Date - Main --
169 --------------------------------------------------------------------------------------
170 -- What is the start date of the assignment? All loading must come after this date.
171
172 open csr_asg_itd_start(p_assignment_id, p_upload_date);
173 fetch csr_asg_itd_start into l_asg_itd_start_date;
174 close csr_asg_itd_start;
175
176
177 -- Return the date on which the dimension expires.
178 if substr(p_dimension_name, 31, 4) = 'USER' then
179
180 -- User balance
181 select
182 business_group_id
183 into
184 l_business_group_id
185 from
186 per_assignments_f
187 where
188 assignment_id = p_assignment_id;
189 l_expiry_date := hr_gbbal.dimension_reset_date
190 (
191 p_dimension_name,
192 p_upload_date,
193 l_business_group_id
194 );
195 l_expiry_date := greatest(l_expiry_date, l_asg_itd_start_date);
196
197 elsif p_dimension_name in ('ASSIGNMENT PERIOD TO DATE', 'ASSIGNMENT SI TYPE PERIOD TO DATE','ASSIGNMENT RUN','ASSIGNMENT SI TYPE RUN') then
198
199 -- Calculate expiry date for _ASG_PTD, _ASG_PRSI_PTD dimension.
200 -- What is the current period start date?
201 open csr_period_start(p_assignment_id, p_upload_date);
202 fetch csr_period_start into l_start_date;
203 close csr_period_start;
204 l_expiry_date := greatest(l_start_date, l_asg_itd_start_date);
205
206 elsif p_dimension_name in ('ASSIGNMENT YEAR TO DATE', 'ASSIGNMENT SI TYPE YEAR TO DATE') then
207
208 open csr_tax_start_date;
209 fetch csr_tax_start_date into l_leg_start;
210 close csr_tax_start_date;
211
212 -- Calculate expiry date for _ASG_YTD and _ASG_PRSI_YTD dimension.
213 -- What is the current tax year start date?
214 -- calculate the the payroll start date
215 open csr_payroll_start_date(p_assignment_id, p_upload_date);
216 fetch csr_payroll_start_date into l_start_date;
217 close csr_payroll_start_date;
218 -- calculate the tax year start date for the upload process
219 l_tax_year := to_date(l_leg_start || to_char(p_upload_date,'YYYY'),'DD/MM/YYYY');
220
221 -- calculate the expiry date
222 l_expiry_date := greatest(l_start_date, l_asg_itd_start_date,
223 l_tax_year );
224
225 elsif p_dimension_name in ('ASSIGNMENT SI TYPE MONTH', 'ASSIGNMENT MONTH') then
226 -- calculate the the payroll start date
227 open csr_payroll_start_date(p_assignment_id, p_upload_date);
228 fetch csr_payroll_start_date into l_start_date;
229 close csr_payroll_start_date;
230 l_month_start_date := trunc(p_upload_date,'MM') ;
231 l_expiry_date := greatest(l_month_start_date,l_start_date,l_asg_itd_start_date);
232
233 elsif p_dimension_name in ('ASSIGNMENT QUARTER TO DATE','ASSIGNMENT SI TYPE QUARTER TO DATE') then
234 -- calculate the qtr start date
235 l_qtr_start_date := trunc(p_upload_date,'Q');
236
237 -- calculate the the payroll start date
238 open csr_payroll_start_date(p_assignment_id, p_upload_date);
239 fetch csr_payroll_start_date into l_start_date;
240 close csr_payroll_start_date;
241
242 -- calculate the expiry date
243 l_expiry_date := greatest(l_start_date, l_asg_itd_start_date,
244 l_qtr_start_date );
245
246 elsif p_dimension_name in ('ASSIGNMENT LUNAR QUARTER TO DATE') then
247 -- calculate the the payroll start date
248 open csr_payroll_start_date(p_assignment_id, p_upload_date);
249 fetch csr_payroll_start_date into l_start_date;
250 close csr_payroll_start_date;
251
252 -- calculate the tax year satrt date
253 open csr_tax_start_date;
254 fetch csr_tax_start_date into l_leg_start;
255 close csr_tax_start_date;
256 -- calculate the tax year start date for the upload process
257
258 l_tax_year := to_date(l_leg_start ||
259 to_char(p_upload_date,'YYYY'),'DD/MM/YYYY');
260 -- Derive Lunar Quarter Start Date
261 SELECT l_tax_year - to_char(l_tax_year,'D') + 2
262 + (84*(decode(trunc((to_number((to_char(p_upload_date,'IW')))-1)/12),4,3,trunc((to_number((to_char(p_upload_date,'IW')))-1)/12))))
263 INTO l_lqtd_start_date
264 FROM dual;
265
266 l_expiry_date :=greatest(l_lqtd_start_date,l_asg_itd_start_date,l_start_date);
267
268 elsif p_dimension_name in ('_PAYMENTS','ASSIGNMENT INCEPTION TO DATE') then
269 -- Calculate expiry date for _PAYMENTS and '_ASG_ITD' dimensions.
270 l_expiry_date := l_asg_itd_start_date;
271
272 elsif p_dimension_name in ('_ELEMENT_ITD') then
273 --
274 -- Calculate expiry date for _ELEMENT_ITD dimensions.
275 --
276 open csr_ele_itd_start(p_assignment_id
280 close csr_ele_itd_start;
277 ,p_upload_date
278 ,p_original_entry_id);
279 fetch csr_ele_itd_start into l_ele_itd_start_date;
281 -- Set the expiry date.
282 l_expiry_date := greatest(l_ele_itd_start_date,l_asg_itd_start_date);
283 --
284 end if;
285
286 -- HR_UTILITY.TRACE_OFF;
287 return nvl(l_expiry_date,END_OF_TIME);
288
289 exception
290 when no_data_found then
291 l_expiry_date := END_OF_TIME;
292 return l_expiry_date;
293
294 end expiry_date;
295
296 -----------------------------------------------------------------------------
297 -- NAME
298 -- is_supported
299 -- PURPOSE
300 -- Checks if the dimension is supported by the upload process.
301 -- ARGUMENTS
302 -- p_dimension_name - the balance dimension to be checked.
303 -- USES
304 -- NOTES
305 -- Only a subset of the NL dimensions are supported.
306 -- This is used by pay_balance_upload.validate_dimension.
307 -----------------------------------------------------------------------------
308 function is_supported
309 (
310 p_dimension_name varchar2
311 ) return number is
312
313 p_dimension_name_temp varchar2(100);
314 begin
315
316
317 hr_utility.trace('Entering pay_nl_bal_upload.is_supported stub');
318
319 -- See if the dimension is supported.
320
321
322 if p_dimension_name in
323 (
324 'ASSIGNMENT YEAR TO DATE','ASSIGNMENT SI TYPE YEAR TO DATE',
325 'ASSIGNMENT PERIOD TO DATE','ASSIGNMENT SI TYPE PERIOD TO DATE',
326 'ASSIGNMENT QUARTER TO DATE','ASSIGNMENT SI TYPE QUARTER TO DATE','ASSIGNMENT LUNAR QUARTER TO DATE',
327 'ASSIGNMENT INCEPTION TO DATE','ASSIGNMENT MONTH','ASSIGNMENT SI TYPE MONTH'
328 )
329 or
330 (
331 substr(p_dimension_name, 31, 4) = 'USER'
332 and
333 substr(p_dimension_name, 40, 3) = 'ASG'
334 )
335
336 then
337
338 return 1;
339 else
340 return 0;
341 end if;
342
343 hr_utility.trace('Exiting pay_nl_bal_upload.is_supported stub');
344
345 end is_supported;
346
347 -----------------------------------------------------------------------------
348 -- NAME
349 -- include_adjustment
350 -- PURPOSE
351 -- Given a dimension, and relevant contexts and details of an existing
352 -- balance adjustment, it will find out if the balance adjustment effects
353 -- the dimension to be set. Both the dimension to be set and the adjustment
354 -- are for the same assignment and balance.
355 -- ARGUMENTS
356 -- p_balance_type_id - the balance to be set.
357 -- p_dimension_name - the balance dimension to be set.
358 -- p_original_entry_id - ORIGINAL_ENTRY_ID context.
359 -- p_bal_adjustment_rec - details of an existing balance adjustment.
360 -- p_test_batch_line_id -
361 -- USES
362 -- NOTES
363 -- This is used by pay_balance_upload.get_current_value.
364 -----------------------------------------------------------------------------
365 function include_adjustment
366 (
367 p_balance_type_id number,
368 p_dimension_name varchar2,
369 p_original_entry_id number,
370 p_upload_date date,
371 p_batch_line_id number,
372 p_test_batch_line_id number
373 ) return number is
374
375
376 CURSOR csr_bal_adj(p_test_batch_line_id NUMBER, p_batch_line_id NUMBER) IS
377 SELECT tba.source_text,tba.balance_type_id
378 FROM pay_temp_balance_adjustments tba,
379 pay_balance_batch_lines bbl
380 WHERE tba.batch_line_id = p_test_batch_line_id
381 AND bbl.batch_line_id = p_batch_line_id;
382
383 l_source_text1 varchar2(10);
384 l_return Number := 0;--True
385 v_cur_bal_adj csr_bal_adj%rowtype;
386 begin
387 hr_utility.trace('Entering pay_nl_bal_upload.include_adjustment stub');
388
389 --Select source text of the current batch line
390 select source_text into l_source_text1 from pay_balance_batch_lines where batch_line_id=p_batch_line_id;
391
392 --For context balances
393 if l_source_text1 is not null then
394 open csr_bal_adj(p_test_batch_line_id,p_batch_line_id);
395 FETCH csr_bal_adj INTO v_cur_bal_adj;
396
397 --Two different dimensions of the same balance and same context, hence adjustment needs to be done
398 if v_cur_bal_adj.source_text=l_source_text1 and v_cur_bal_adj.balance_type_id=p_balance_type_id then
399 l_return := 1;
400 end if;
401
402 --If no other dimension of the same balance has been processed before
403 IF csr_bal_adj%NOTFOUND THEN
404 l_return := 0; -- false
405 END IF;
406 CLOSE csr_bal_adj;
407
408
409 --For non context balances , adjustment should be done
410 else
411 l_return := 1;
412 end if;
413
414
415
416
417
418 hr_utility.trace('Exiting pay_nl_bal_upload.include_adjustment stub');
419
420 return l_return;
421
422
423 end include_adjustment;
424
425 -----------------------------------------------------------------------------
426 -- NAME
427 -- validate_batch_lines
428 -- PURPOSE
429 -- Applies NL specific validation to the batch.
430 -- ARGUMENTS
431 -- p_batch_id - the batch to be validate_batch_linesd.
432 -- USES
433 -- NOTES
434 -- This is used by pay_balance_upload.validate_batch_lines.
435 -----------------------------------------------------------------------------
436 procedure validate_batch_lines
437 (
438 p_batch_id number
439 ) is
440 begin
441
442 hr_utility.trace('Entering pay_nl_bal_upload.validate_batch_lines stub');
443
444 hr_utility.trace('Exiting pay_nl_bal_upload.validate_batch_lines stub');
445
446 end validate_batch_lines;
447
448 end pay_nl_bal_upload;
[Home] [Help]