1 package body pay_au_bal_upload as
2 -- $Header: pyaubaup.pkb 120.3 2006/08/21 08:39:03 priupadh noship $
3
4 -- Copyright (c) 1999 Oracle Corporation
5 -- All rights reserved
6
7 -- Date Author Bug/CR Num Notes
8 -- -----------+--------+----------+-----------------------------------------
9 -- 21 Aug 2006 priupadh 5471095 Modified expiry_date
10 -- 06 Sep 2005 ksingla Added dbdrv comments
11 -- 06 Sep 2005 ksingla Modified for Bug 4516174.
12 -- 17 Feb 2000 JTurner Completed development
13 -- 28-DEC-1999 sgoggin Genesis
14
15 g_package constant varchar2(240) := 'pay_au_bal_upload.';
16
17 -- date constants.
18
19 g_end_of_time constant date := to_date('31/12/4712','dd/mm/yyyy');
20 g_tax_year_start constant varchar2(6) := '01-07-';
21 g_fbt_year_start constant varchar2(6) := '01-04-';
22 g_cal_year_start constant varchar2(6) := '01-01-';
23
24
25
26 -----------------------------------------------------------------------------
27 -- name
28 -- expiry_date
29 -- purpose
30 -- returns the expiry date of a given dimension relative to a date.
31 -- arguments
32 -- p_upload_date - the date on which the balance should be correct.
33 -- p_dimension_name - the dimension being set.
34 -- p_assignment_id - the assignment involved.
35 -- p_original_entry_id - original_entry_id context.
36 -- uses
37 -- notes
38 -- this is used by pay_balance_upload.dim_expiry_date.
39 -- if the expiry date cannot be derived then it is set to the end of time
40 -- to indicate that a failure has occured. the process that uses the
41 -- expiry date knows this rule and acts accordingly.
42 -----------------------------------------------------------------------------
43
44 function expiry_date
45 (p_upload_date date
46 ,p_dimension_name varchar2
47 ,p_assignment_id number
48 ,p_original_entry_id number)
49 return date is
50
51 l_fn_name varchar2(61) := 'pay_au_bal_upload.expiry_date' ;
52 l_business_group_id per_assignments_f.business_group_id%type ;
53 l_asg_start_date per_assignments_f.effective_start_date%type ;
54 l_dimension_start_date date ;
55 l_expiry_date date ; -- expiry_date of the dimension.
56
57 e_bad_expiry_date exception ;
58
59 -- get assignment details
60
61 /* Bug 4516174- Modified for case when assignment is assigned to a payroll at a time
62 when it does not have associated time periods */
63
64 cursor c_assignment
65 (p_assignment_id number
66 ,p_effective_date date) is
67 select greatest (min(a.effective_start_date), min(PTP.start_date))
68 , min(a.business_group_id)
69 from per_assignments_f a
70 ,per_time_periods PTP
71 where a.assignment_id = p_assignment_id
72 and PTP.payroll_id = a.payroll_id
73 and PTP.start_date <= p_effective_date
74 and ptp.start_date between a.effective_start_date and p_effective_date
75 and p_effective_date between a.effective_start_date and a.effective_end_date
76 order by a.effective_start_date ;
77
78 -- get period details
79
80 cursor csr_period_start
81 (p_assignment_id number
82 ,p_upload_date date) is
83 select nvl(ptp.start_date, g_end_of_time)
84 from per_time_periods ptp
85 , per_assignments_f paf
86 where paf.assignment_id = p_assignment_id
87 and p_upload_date between paf.effective_start_date
88 and paf.effective_end_date
89 and ptp.payroll_id = paf.payroll_id
90 and p_upload_date between ptp.start_date
91 and ptp.end_date ;
92
93
94
95 begin
96
97 hr_utility.trace('In: ' || l_fn_name) ;
98 hr_utility.trace(' p_upload_date => ' || to_char(p_upload_date,'dd Mon yyyy')) ;
99 hr_utility.trace(' p_dimension_name => ' || p_dimension_name) ;
100 hr_utility.trace(' p_assignment_id => ' || to_char(p_assignment_id)) ;
101 hr_utility.trace(' p_original_entry_id => ' || to_char(p_original_entry_id)) ;
102
103 -- get assignment details
104 open c_assignment(p_assignment_id, p_upload_date);
105 fetch c_assignment
106 into l_asg_start_date
107 , l_business_group_id ;
108 if c_assignment%notfound
109 then
110 close c_assignment;
111 raise e_bad_expiry_date ;
112 end if;
113 close c_assignment;
114
115 -- Calculate the expiry_date of the specified dimension relative to the
116 -- upload_date, taking account any contexts. each of
117 -- the calculations also takes into account when the assignment is on a
118 -- payroll to ensure that a balance adjustment could be made at that point
119 -- if it were required.
120
121 /* bug 4516174 - Added LE level dimensions */
122 /*bug 5471095 In expiry_date changed '_ASG_CAL_LE_YTD' to '_ASG_LE_CAL_YTD' */
123
124 if (p_dimension_name = '_ASG_CAL_YTD') or (p_dimension_name = '_ASG_LE_CAL_YTD')then
125
126 -- get start of dimension
127 l_dimension_start_date := hr_au_routes.span_start(p_upload_date, 1, g_cal_year_start) ;
128 l_expiry_date := greatest(l_asg_start_date, l_dimension_start_date) ;
129 if l_expiry_date > p_upload_date
130 then
131 raise e_bad_expiry_date ;
132 end if ;
133
134 elsif (p_dimension_name = '_ASG_FBT_YTD') or (p_dimension_name = '_ASG_LE_FBT_YTD') then
135
136 -- get start of dimension
137 l_dimension_start_date := hr_au_routes.span_start(p_upload_date, 1, g_fbt_year_start) ;
138 l_expiry_date := greatest(l_asg_start_date, l_dimension_start_date) ;
139 if l_expiry_date > p_upload_date
140 then
141 raise e_bad_expiry_date ;
142 end if ;
143
144 elsif (p_dimension_name = '_ASG_FY_QTD')or (p_dimension_name = '_ASG_LE_FY_QTD') then
145
146 -- get start of dimension
147 l_dimension_start_date := hr_au_routes.fiscal_span_start(p_upload_date, 4, l_business_group_id) ;
148 l_expiry_date := greatest(l_asg_start_date, l_dimension_start_date) ;
149 if l_expiry_date > p_upload_date
150 then
151 raise e_bad_expiry_date ;
152 end if ;
153
154 elsif (p_dimension_name = '_ASG_FY_YTD') or (p_dimension_name = '_ASG_LE_FY_YTD')then
155
156 -- get start of dimension
157 l_dimension_start_date := hr_au_routes.fiscal_span_start(p_upload_date, 1, l_business_group_id) ;
158 l_expiry_date := greatest(l_asg_start_date, l_dimension_start_date) ;
159 if l_expiry_date > p_upload_date
160 then
161 raise e_bad_expiry_date ;
162 end if ;
163
164 elsif (p_dimension_name = '_ASG_MTD')or (p_dimension_name = '_ASG_LE_MTD') then
165
166 -- get start of dimension
167 l_dimension_start_date := hr_au_routes.span_start(p_upload_date, 12, g_cal_year_start) ;
168 l_expiry_date := greatest(l_asg_start_date, l_dimension_start_date) ;
169 if l_expiry_date > p_upload_date
170 then
171 raise e_bad_expiry_date ;
172 end if ;
173
174 elsif (p_dimension_name = '_ASG_PTD')or (p_dimension_name = '_ASG_LE_PTD') then
175
176 -- get start of dimension
177 open csr_period_start(p_assignment_id, p_upload_date);
178 fetch csr_period_start
179 into l_dimension_start_date;
180 if csr_period_start%notfound
181 then
182 close csr_period_start;
183 raise e_bad_expiry_date;
184 end if;
185 close csr_period_start;
186 l_expiry_date := greatest(l_asg_start_date, l_dimension_start_date) ;
187 if l_expiry_date > p_upload_date
188 then
189 raise e_bad_expiry_date ;
190 end if ;
191
192 elsif (p_dimension_name = '_ASG_QTD') or (p_dimension_name = '_ASG_LE_QTD') then
193
194 -- get start of dimension
195 l_dimension_start_date := hr_au_routes.span_start(p_upload_date, 4, g_tax_year_start) ;
196 l_expiry_date := greatest(l_asg_start_date, l_dimension_start_date) ;
197 if l_expiry_date > p_upload_date
198 then
199 raise e_bad_expiry_date ;
200 end if ;
201
202 elsif (p_dimension_name = '_ASG_TD') or (p_dimension_name = '_ASG_LE_TD') then
203
204 l_expiry_date := l_asg_start_date;
205 if l_expiry_date > p_upload_date
206 then
207 raise e_bad_expiry_date ;
208 end if ;
209
210 elsif ( p_dimension_name = '_ASG_YTD') or ( p_dimension_name = '_ASG_LE_YTD') then
211
212 -- get start of dimension
213 /* Bug 4516174 Modified the frequency from 4 to 1 */
214
215 l_dimension_start_date := hr_au_routes.span_start(p_upload_date, 1, g_tax_year_start) ;
216 l_expiry_date := greatest(l_asg_start_date, l_dimension_start_date) ;
217 if l_expiry_date > p_upload_date
218 then
219 raise e_bad_expiry_date ;
220 end if ;
221
222 end if;
223
224 -- check null value, as the no_data_found exception won't be raised by
225 -- a pseudo-column null returned by the cursor.
226 if l_expiry_date is null then
227 raise e_bad_expiry_date ;
228 end if;
229
230 hr_utility.trace(l_fn_name || ' return: ' || to_char(l_expiry_date,'dd Mon yyyy')) ;
231 hr_utility.trace('Out: ' || l_fn_name) ;
232 return l_expiry_date;
233
234 exception
235 -- when e_bad_expiry_date then
236 -- l_expiry_date := g_end_of_time;
237 -- return l_expiry_date;
238 when others then
239 l_expiry_date := g_end_of_time;
240 hr_utility.trace(l_fn_name || ' return: ' || to_char(l_expiry_date,'dd Mon yyyy')) ;
241 hr_utility.trace('Out: ' || l_fn_name) ;
242 return l_expiry_date;
243
244 end expiry_date;
245
246 -----------------------------------------------------------------------------
247 -- name
248 -- is_supported
249 -- purpose
250 -- checks if the dimension is supported by the upload process.
251 -- arguments
252 -- p_dimension_name - the balance dimension to be checked.
253 -- uses
254 -- notes
255 -- only a subset of the au dimensions are supported
256 -- this is used by pay_balance_upload.validate_dimension.
257 -----------------------------------------------------------------------------
258
259 function is_supported
260 (p_dimension_name varchar2)
261 return number is
262 l_proc constant varchar2(72) := g_package||'is_supported';
263 begin
264
265 hr_utility.trace('Entering '||l_proc);
266
267 -- see if the dimension is supported.
268
269 /* Bug 4516174 - Added support for LE level dimensions */
270
271 if p_dimension_name in ('_ASG_CAL_YTD'
272 ,'_ASG_FBT_YTD'
273 ,'_ASG_FY_QTD'
274 ,'_ASG_FY_YTD'
275 ,'_ASG_MTD'
276 ,'_ASG_PTD'
277 ,'_ASG_QTD'
278 ,'_ASG_TD'
279 ,'_ASG_YTD'
280 ,'_ASG_LE_CAL_YTD'
281 ,'_ASG_LE_FBT_YTD'
282 ,'_ASG_LE_FY_QTD'
283 ,'_ASG_LE_FY_YTD'
284 ,'_ASG_LE_MTD'
285 ,'_ASG_LE_PTD'
286 ,'_ASG_LE_QTD'
287 ,'_ASG_LE_TD'
288 ,'_ASG_LE_YTD'
289 )
290 then
291 hr_utility.trace('Exiting '||l_proc);
292 return 1;
293 else
294 hr_utility.trace('Exiting '||l_proc);
295 return 0;
296 end if;
297
298 end is_supported;
299
300 -----------------------------------------------------------------------------
301 -- name
302 -- include_adjustment
303 -- purpose
304 -- given a dimension, and relevant contexts and details of an existing
305 -- balanmce adjustment, it will find out if the balance adjustment effects
306 -- the dimension to be set. both the dimension to be set and the adjustment
307 -- are for the same assignment and balance. the adjustment also lies between
308 -- the expiry date of the new balance and the date on which it is to set.
309 -- arguments
310 -- p_balance_type_id - the balance to be set.
311 -- p_dimension_name - the balance dimension to be set.
312 -- p_original_entry_id - original_entry_id context.
313 -- p_bal_adjustment_rec - details of an existing balance adjustment.
314 -- uses
315 -- notes
316 -- all the au dimensions affect each other when they share the same context
317 -- values so there is no special support required for individual dimensions.
318 -- this is used by pay_balance_upload.get_current_value.
319 -----------------------------------------------------------------------------
320
321 /*Bug 4516174 Function Modified for checking valid adjustments */
322
323 FUNCTION include_adjustment
324 (
325 p_balance_type_id NUMBER
326 ,p_dimension_name VARCHAR2
327 ,p_original_entry_id NUMBER
328 ,p_upload_date DATE
329 ,p_batch_line_id NUMBER
330 ,p_test_batch_line_id NUMBER
331 )
332 RETURN NUMBER
333 IS
334
335 -- Does the balance adjustment effect the new balance dimension.
336 -- TAX_UNIT_ID context NB. if the tax unit is used then only those
337 -- adjustments which are for the same tax unit can be included.
338
339 CURSOR csr_is_included( p_balance_type_id NUMBER
340 , p_tax_unit_id NUMBER
341 , p_bal_adj_tax_unit_id NUMBER
342 ) IS
343 SELECT BT.balance_type_id
344 FROM pay_balance_types BT
345 WHERE BT.balance_type_id = p_balance_type_id
346 and ((p_tax_unit_id is null) or
347 (p_tax_unit_id is not null and p_tax_unit_id = p_bal_adj_tax_unit_id)) ;
348
349 l_bal_type_id pay_balance_types.balance_type_id%TYPE;
350
351 -- To get the tax_unit_id from pay_balance_batch_lines
352
353 CURSOR csr_get_tax_unit(p_batch_line_id NUMBER) IS
354 SELECT htuv.tax_unit_id
355 FROM pay_balance_batch_lines pbbl
356 ,hr_tax_units_v htuv
357 WHERE pbbl.batch_line_id = p_batch_line_id
358 AND pbbl.tax_unit_id = htuv.tax_unit_id
359 AND pbbl.tax_unit_id IS NOT NULL
360 UNION ALL
361 SELECT htuv.tax_unit_id
362 FROM pay_balance_batch_lines pbbl
363 ,hr_tax_units_v htuv
364 WHERE pbbl.batch_line_id = p_batch_line_id
365 AND upper(pbbl.gre_name) = UPPER(htuv.name)
366 AND pbbl.tax_unit_id IS NULL;
367
368
369 -- Get tax_unit_id for previously tested adjustments
370
371 CURSOR csr_get_tested_adjustments(p_test_batch_line_id NUMBER) IS
372 SELECT tax_unit_id
373 FROM pay_temp_balance_adjustments
374 WHERE batch_line_id = p_test_batch_line_id;
375
376 -- The balance returned by the include check.
377 l_orig_entry_id pay_balance_batch_lines.original_entry_id%TYPE;
378 l_adj_orig_entry_id pay_temp_balance_adjustments.original_entry_id%TYPE;
379 l_tax_unit_id pay_balance_batch_lines.tax_unit_id%TYPE;
380 l_adj_tax_unit_id pay_temp_balance_adjustments.tax_unit_id%TYPE;
381
382 BEGIN
383
384 OPEN csr_get_tax_unit(p_batch_line_id);
385 FETCH csr_get_tax_unit INTO l_tax_unit_id ;
386 CLOSE csr_get_tax_unit;
387
388
389 OPEN csr_get_tested_adjustments(p_test_batch_line_id);
390 FETCH csr_get_tested_adjustments
391 INTO l_adj_tax_unit_id ;
392 CLOSE csr_get_tested_adjustments;
393
394 -- Does the balance adjustment effect the new balance
395
396 hr_utility.trace('balance_type_id = '||TO_CHAR(p_balance_type_id));
397 hr_utility.trace('tax_unit_id = '||TO_CHAR(l_tax_unit_id));
398 hr_utility.trace('original_entry_id = '||TO_CHAR(p_original_entry_id));
399 hr_utility.trace('BA tax_unit_id = '||TO_CHAR(l_adj_tax_unit_id));
400 hr_utility.trace('BA original_entry_id = '||TO_CHAR(l_adj_orig_entry_id));
401
402
403 OPEN csr_is_included(p_balance_type_id
404 ,l_tax_unit_id
405 ,l_adj_tax_unit_id
406 );
407
408 FETCH csr_is_included
409 INTO l_bal_type_id;
410 CLOSE csr_is_included;
411
412 -- Adjustment does contribute to the new balance.
413
414 IF l_bal_type_id IS NOT NULL THEN
415 RETURN (1); --TRUE
416
417 -- Adjustment does not contribute to the new balance.
418 ELSE
419 RETURN (0); --FALSE
420 END IF;
421
422 EXCEPTION
423 WHEN OTHERS THEN
424 IF csr_is_included%ISOPEN THEN
425 CLOSE csr_is_included;
426 END IF;
427
428 IF csr_get_tax_unit%ISOPEN THEN
429 CLOSE csr_get_tax_unit;
430 END IF;
431
432 IF csr_get_tested_adjustments%ISOPEN THEN
433 CLOSE csr_get_tested_adjustments;
434 END IF;
435
436 RAISE;
437 END include_adjustment;
438
439 -----------------------------------------------------------------------------
440 -- name
441 -- validate_batch_lines
442 -- purpose
443 -- applies bf specific validation to the batch.
444 -- arguments
445 -- p_batch_id - the batch to be validate_batch_lines.
446 -- uses
447 -- notes
448 -- this is used by pay_balance_upload.validate_batch_lines.
449 -----------------------------------------------------------------------------
450
451 procedure validate_batch_lines( p_batch_id number ) is
452 begin
453
454 hr_utility.trace('Entering '||g_package||'validate_batch_lines');
455
456 hr_utility.trace('Exiting '||g_package||'validate_batch_lines');
457
458 end validate_batch_lines;
459
460 end pay_au_bal_upload;
[Home] [Help]