1 package body pay_nz_bal_upload as
2 /* $Header: pynzbaup.pkb 120.3 2008/07/16 10:13:19 vamittal noship $ */
3 --
4 -- Change List
5 -- ----------
6 -- DATE Name Vers Bug No Description
7 -- -----------+---------------+--------+--------+------------------------------+
8 -- 23-Aug-1999 sclarke 110.0 Created
9 -- 24-Aug-1999 sclarke 110.1 Added support for asg_hol_ytd
10 -- and asg_4week dimensions
11 -- 14-Sep-1999 sclarke 110.2 Bug 993389
12 -- 12-Jun-2001 apunekar 110.3 Parameter p_test_batch_line_id to function include_adjustment
13 -- 24-Sep-2001 jlin 115.2 2011512 Should choose the greater date
14 -- between the earliest pay period's
15 -- start date for the current payroll
16 -- and the earliest EFFECTIVE_START_
17 -- EFFECTIVE_START_DATE for the
18 -- assignment for a variable called
19 -- l_asg_start_date
20 -- 19-Jun-2008 vamittal 115.3 7037181 Removed support for asg_hol_ytd dimension
21 -- 14-Jul-2008 vamittal 115.4 7037181 Comments are added.
22 -- -----------+---------------+--------+--------+------------------------------+
23 g_package constant varchar2(240) := 'pay_nz_bal_upload.';
24 --
25 -- date constants.
26 --
27 start_of_time constant date := to_date('01/01/0001','dd/mm/yyyy');
28 end_of_time constant date := to_date('31/12/4712','dd/mm/yyyy');
29 g_tax_start_dd_mm constant varchar2(11) := '01-04-';
30 --
31 -- Dimension Name constants
32 --
33 g_asg_td constant pay_balance_dimensions.dimension_name%type := upper('_asg_td');
34 g_asg_ptd constant pay_balance_dimensions.dimension_name%type := upper('_asg_ptd');
35 g_asg_ytd constant pay_balance_dimensions.dimension_name%type := upper('_asg_ytd');
36 g_asg_fy_qtd constant pay_balance_dimensions.dimension_name%type := upper('_asg_fy_qtd');
37 g_asg_fy_ytd constant pay_balance_dimensions.dimension_name%type := upper('_asg_fy_ytd');
38 /*Bug 7037181 variable for dimension asg_hol_ytd is removed */
39 g_asg_4week constant pay_balance_dimensions.dimension_name%type := upper('_asg_4week');
40 --
41 -----------------------------------------------------------------------------
42 -- name
43 -- expiry_date
44 -- purpose
45 -- returns the expiry date of a given dimension relative to a date.
46 -- arguments
47 -- p_upload_date - the date on which the balance should be correct.
48 -- p_dimension_name - the dimension being set.
49 -- p_assignment_id - the assignment involved.
50 -- p_original_entry_id - original_entry_id context.
51 -- uses
52 -- notes
53 -- this is used by pay_balance_upload.dim_expiry_date.
54 -- if the expiry date cannot be derived then it is set to the end of time
55 -- to indicate that a failure has occured. the process that uses the
56 -- expiry date knows this rule and acts accordingly.
57 -----------------------------------------------------------------------------
58 --
59 function expiry_date
60 ( p_upload_date date
61 , p_dimension_name varchar2
62 , p_assignment_id number
63 , p_original_entry_id number
64 )
65 return date is
66 --
67 -- returns the date of 4 weeks ago
68 --
69 cursor csr_asg_4week
70 ( p_assignment_id number
71 , p_upload_date date) is
72 select p_upload_date - 28
73 from per_assignments_f paf
74 , per_time_periods ptp
75 where paf.assignment_id = p_assignment_id
76 and ptp.payroll_id = paf.payroll_id
77 and p_upload_date between ptp.start_date and ptp.end_date
81 --
78 and p_upload_date between paf.effective_start_date and paf.effective_end_date;
79 --
80 -- returns the start date of the fiscal year.
82 cursor csr_fiscal_year
83 ( p_assignment_id number
84 , p_upload_date date) is
85 select hr_nz_routes.fiscal_span_start( p_upload_date, 1, paf.business_group_id )
86 from per_assignments_f paf
87 , per_time_periods ptp
88 where paf.assignment_id = p_assignment_id
89 and ptp.payroll_id = paf.payroll_id
90 and p_upload_date between ptp.start_date and ptp.end_date
91 and p_upload_date between paf.effective_start_date and paf.effective_end_date;
92 --
93 -- returns the start date of the fiscal quarter.
94 --
95 cursor csr_fiscal_quarter
96 ( p_assignment_id number
97 , p_upload_date date) is
98 select hr_nz_routes.fiscal_span_start( p_upload_date, 4, paf.business_group_id )
99 from per_assignments_f paf
100 , per_time_periods ptp
101 where paf.assignment_id = p_assignment_id
102 and ptp.payroll_id = paf.payroll_id
103 and p_upload_date between ptp.start_date and ptp.end_date
104 and p_upload_date between paf.effective_start_date and paf.effective_end_date;
105 --
106 -- returns the start date of the current period on the upload date.
107 --
108 cursor csr_period_start
109 ( p_assignment_id number
110 , p_upload_date date) is
111 select nvl(ptp.start_date, end_of_time)
112 from per_time_periods ptp
113 , per_assignments_f paf
114 where paf.assignment_id = p_assignment_id
115 and p_upload_date between paf.effective_start_date and paf.effective_end_date
116 and ptp.payroll_id = paf.payroll_id
117 and p_upload_date between ptp.start_date and ptp.end_date;
118 --
119 -- returns the earliest date on which the assignment exists.
120 --
121 cursor csr_asg_start
122 ( p_assignment_id number
123 , p_upload_date date) is
124 select nvl(greatest(min(ptp.start_date), min(paf.effective_start_date)),end_of_time)
125 , paf.business_group_id
126 from per_assignments_f paf
127 , per_time_periods ptp
128 where paf.assignment_id = p_assignment_id
129 and paf.payroll_id = ptp.payroll_id
130 and paf.effective_start_date <= p_upload_date
131 and ptp.start_date <= p_upload_date
132 group by paf.business_group_id;
133 --
134 --
135 --
136 cursor csr_asg_start_date
137 ( p_assignment_id number
138 , p_upload_date date
139 , p_expiry_date date) is
140 select nvl(greatest(min(paf.effective_start_date), p_expiry_date), end_of_time)
141 from per_assignments_f paf
142 , per_time_periods ptp
143 where paf.assignment_id = p_assignment_id
144 and ptp.payroll_id = paf.payroll_id
145 and p_upload_date between ptp.start_date and ptp.end_date
146 and paf.effective_start_date <= p_upload_date
147 and paf.effective_end_date >= p_expiry_date;
148 --
149 --
150 --
151 l_tax_yr_start_date date; -- start of the tax year using the upload_date.
152 l_tax_qtr_start_date date; -- start of the tax quarter using the upload_date.
153 l_fiscal_yr_start_date date; -- start of the fiscal year using the upload_date.
154 l_fiscal_qtr_start_date date; -- start of the fiscal quarter using the upload_date.
155 l_prd_start_date date; -- start of the period using the upload_date.
156 l_expiry_date date; -- expiry_date of the dimension.
157 l_asg_start_date date; -- earliest date on which the assignment exists.
158 l_asg_4week_start date; -- start of 4 weeks prior to upload date
159 l_start_date date;
160 l_anniversary_date date; -- start date of the last anniversary
161 l_business_group_id per_assignments_f.business_group_id%type;
162 --
163 begin
164 --
165 -- get the earliest effective date that the assignment can exist
166 -- expiry dates cannot be before this date, also get the business
167 -- group id for later use
168 --
169 open csr_asg_start(p_assignment_id, p_upload_date);
170 fetch csr_asg_start into l_asg_start_date, l_business_group_id;
171 if csr_asg_start%notfound then
172 close csr_asg_start;
173 raise no_data_found;
174 end if;
175 close csr_asg_start;
176 --
177 -- Calculate the expiry_date of the specified dimension relative to the
178 -- upload_date, taking account any contexts. each of
179 -- the calculations also takes into account when the assignment is on a
180 -- payroll to ensure that a balance adjustment could be made at that point
181 -- if it were required.
182 --
183 if p_dimension_name = g_asg_td then
184 --
185 l_expiry_date := l_asg_start_date;
186 --
187 /* Bug 7037181 code for dimension asg_hol_ytd is removed */
188 elsif p_dimension_name = g_asg_4week then
189 --
190 open csr_asg_4week(p_assignment_id, p_upload_date);
191 fetch csr_asg_4week into l_asg_4week_start;
192 if csr_asg_4week%notfound then
193 close csr_asg_4week;
194 raise no_data_found;
195 end if;
196 close csr_asg_4week;
197 l_expiry_date := greatest(l_asg_4week_start, l_asg_start_date);
198 --
199 elsif p_dimension_name = g_asg_ptd then
200 --
204 fetch csr_period_start into l_prd_start_date;
201 -- what's the current period start_date ?
202 --
203 open csr_period_start(p_assignment_id, p_upload_date);
205 if csr_period_start%notfound then
206 close csr_period_start;
207 raise no_data_found;
208 else
209 close csr_period_start;
210 open csr_asg_start_date(p_assignment_id, p_upload_date, l_prd_start_date);
211 fetch csr_asg_start_date into l_start_date;
212 if csr_asg_start_date%notfound then
213 close csr_asg_start_date;
214 raise no_data_found;
215 end if;
216 close csr_asg_start_date;
217 l_expiry_date := greatest(l_start_date, l_asg_start_date);
218 end if;
219 --
220 elsif p_dimension_name = g_asg_ytd then
221 --
222 -- what's the start_date of the tax year ?
223 --
224 l_tax_yr_start_date := hr_nz_routes.span_start(p_upload_date, 1, g_tax_start_dd_mm);
225 open csr_asg_start_date(p_assignment_id, p_upload_date, l_tax_yr_start_date);
226 fetch csr_asg_start_date into l_start_date;
227 if csr_asg_start_date%notfound then
228 close csr_asg_start_date;
229 raise no_data_found;
230 end if;
231 close csr_asg_start_date;
232 l_expiry_date := greatest(l_start_date, l_asg_start_date);
233 --
234 elsif p_dimension_name = g_asg_fy_qtd then
235 --
236 -- what's the start_date of the fiscal quarter ?
237 --
238 open csr_fiscal_quarter(p_assignment_id, p_upload_date);
239 fetch csr_fiscal_quarter into l_fiscal_qtr_start_date;
240 if csr_fiscal_quarter%notfound then
241 close csr_fiscal_quarter;
242 raise no_data_found;
243 else
244 close csr_fiscal_quarter;
245 open csr_asg_start_date(p_assignment_id, p_upload_date, l_fiscal_qtr_start_date);
246 fetch csr_asg_start_date into l_start_date;
247 if csr_asg_start_date%notfound then
248 close csr_asg_start_date;
249 raise no_data_found;
250 end if;
251 close csr_asg_start_date;
252 l_expiry_date := greatest(l_start_date, l_asg_start_date);
253 end if;
254 --
255 elsif p_dimension_name = g_asg_fy_ytd then
256 --
257 -- what's the start_date of the fiscal year ?
258 --
259 open csr_fiscal_year(p_assignment_id, p_upload_date);
260 fetch csr_fiscal_year into l_fiscal_yr_start_date;
261 if csr_fiscal_year%notfound then
262 close csr_fiscal_year;
263 raise no_data_found;
264 else
265 close csr_fiscal_year;
266 --
267 open csr_asg_start_date(p_assignment_id, p_upload_date, l_fiscal_yr_start_date);
268 fetch csr_asg_start_date into l_start_date;
269 if csr_asg_start_date%notfound then
270 close csr_asg_start_date;
271 raise no_data_found;
272 end if;
273 close csr_asg_start_date;
274 --
275 l_expiry_date := greatest(l_start_date, l_asg_start_date);
276 --
277 end if;
278 end if;
279 --
280 -- check null value, as the no_data_found exception won't be raised by
281 -- a pseudo-column null returned by the cursor.
282 --
283 if l_expiry_date is null then
284 raise no_data_found;
285 end if;
286 --
287 return l_expiry_date;
288 --
289 exception
290 when no_data_found then
291 l_expiry_date := end_of_time;
292 return l_expiry_date;
293 when others then
294 l_expiry_date := end_of_time;
295 return l_expiry_date;
296 --
297 end expiry_date;
298 --
299 -----------------------------------------------------------------------------
300 -- name
301 -- is_supported
302 -- purpose
303 -- checks if the dimension is supported by the upload process.
304 -- arguments
305 -- p_dimension_name - the balance dimension to be checked.
306 -- uses
307 -- notes
308 -- only a subset of the nz dimensions are supported
309 -- this is used by pay_balance_upload.validate_dimension.
310 -----------------------------------------------------------------------------
311 --
312 function is_supported ( p_dimension_name varchar2)
313 return number is
314 l_proc constant varchar2(72) := g_package||'is_supported';
315 begin
316 --
317 hr_utility.trace('Entering '||l_proc);
318 --
319 -- see if the dimension is supported.
320 --
321 /* Bug 7037181 dimension name for _asg_hol_ytd is removed */
322 if p_dimension_name in
323 ( g_asg_td
324 , g_asg_ptd
325 , g_asg_ytd
326 , g_asg_fy_qtd
327 , g_asg_fy_ytd
328 , g_asg_4week
329 ) then
330 return 1;
331 else
332 return 0;
333 end if;
334 --
335 hr_utility.trace('Exiting '||l_proc);
336 --
337 end is_supported;
338 --
339 -----------------------------------------------------------------------------
340 -- name
341 -- include_adjustment
342 -- purpose
343 -- given a dimension, and relevant contexts and details of an existing
344 -- balanmce adjustment, it will find out if the balance adjustment effects
345 -- the dimension to be set. both the dimension to be set and the adjustment
346 -- are for the same assignment and balance. the adjustment also lies between
347 -- the expiry date of the new balance and the date on which it is to set.
348 -- arguments
349 -- p_balance_type_id - the balance to be set.
350 -- p_dimension_name - the balance dimension to be set.
351 -- p_original_entry_id - original_entry_id context.
352 -- p_bal_adjustment_rec - details of an existing balance adjustment.
353 -- uses
354 -- notes
355 -- all the nz dimensions affect each other when they share the same context
356 -- values so there is no special support required for individual dimensions.
357 -- this is used by pay_balance_upload.get_current_value.
358 -----------------------------------------------------------------------------
359 --
360 function include_adjustment ( p_balance_type_id number
361 , p_dimension_name varchar2
362 , p_original_entry_id number
363 , p_upload_date date
364 , p_batch_line_id number
365 , p_test_batch_line_id number
366 ) return number is
367 --
368 l_bal_type_id number;
369 l_proc constant varchar2(72) := g_package||'include_adjustment';
370 --
371 begin
372 --
373 hr_utility.trace('Entering '||l_proc);
374 --
375 return 1;
376 --
377 hr_utility.trace('Exiting '||l_proc);
378 --
379 end include_adjustment;
380 --
381 -----------------------------------------------------------------------------
382 -- name
383 -- validate_batch_lines
384 -- purpose
385 -- applies bf specific validation to the batch.
386 -- arguments
387
388 -- p_batch_id - the batch to be validate_batch_lines.
389 -- uses
390 -- notes
391 -- this is used by pay_balance_upload.validate_batch_lines.
392 -----------------------------------------------------------------------------
393 --
394 procedure validate_batch_lines( p_batch_id number ) is
395 begin
396 --
397 hr_utility.trace('Entering '||g_package||'validate_batch_lines');
398 --
399 hr_utility.trace('Exiting '||g_package||'validate_batch_lines');
400 --
401 end validate_batch_lines;
402 --
403 end pay_nz_bal_upload;
[Home] [Help]