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