1 package body pay_za_bal_upload as
2 /* $Header: pyzaupld.pkb 120.5 2006/10/11 11:14:37 rpahune noship $ */
3 --
4 -- Date constants.
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');
8
9 -----------------------------------------------------------------------------
10 -- NAME
11 -- expiry_date
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 start date of the first period of the tax year in which
35 -- the upload date falls.
36 cursor csr_tax_year_start
37 (
38 p_assignment_id number,
39 p_upload_date date
40 ) is
41 select
42 nvl(min(ptp2.start_date), END_OF_TIME)
43 from
44 per_time_periods ptp, per_time_periods ptp2, per_assignments_f ass
45 where
46 ass.assignment_id = p_assignment_id
47 and
48 p_upload_date between ass.effective_start_date and ass.effective_end_date
49 and
50 ptp.payroll_id = ass.payroll_id
51 and
52 ptp2.payroll_id = ptp.payroll_id
53 and
54 p_upload_date between ptp.start_date and ptp.end_date
55 and
56 ptp2.prd_information1 = ptp.prd_information1;
57
58 -- Returns the start date of the first period of the tax quarter in which
59 -- the upload date falls.
60 cursor csr_tax_quarter_start
61 (
62 p_assignment_id number,
63 p_upload_date date
64 ) is
65 select
66 nvl(min(ptp2.start_date), END_OF_TIME)
67 from
68 per_time_periods ptp, per_time_periods ptp2, per_assignments_f ass
69 where
70 ass.assignment_id = p_assignment_id
71 and
72 p_upload_date between ass.effective_start_date and ass.effective_end_date
73 and
74 ptp.payroll_id = ass.payroll_id
75 and
76 ptp2.payroll_id = ptp.payroll_id
77 and
78 p_upload_date between ptp.start_date and ptp.end_date
79 and
80 ptp2.prd_information1 = ptp.prd_information1
81 and
82 ptp2.prd_information2 = ptp.prd_information2;
83
84 -- Returns the start date of the first period of the Payroll Month in which
85 -- the upload date falls.
86 cursor csr_month_start
87 (
88 p_assignment_id number,
89 p_upload_date date
90 ) is
91 select
92 nvl(min(ptp2.start_date), END_OF_TIME)
93 from
94 per_time_periods ptp, per_time_periods ptp2, per_assignments_f ass
95 where
96 ass.assignment_id = p_assignment_id
97 and
98 p_upload_date between ass.effective_start_date and ass.effective_end_date
99 and
100 ptp.payroll_id = ass.payroll_id
101 and
102 ptp2.payroll_id = ptp.payroll_id
103 and
104 p_upload_date between ptp.start_date and ptp.end_date
105 and
106 ptp2.pay_advice_date = ptp.pay_advice_date;
107
108 -- Returns the start date of the first period of the calendar year in which
109 -- the upload date falls.
110 cursor csr_calendar_year_start
111 (
112 p_assignment_id number,
113 p_upload_date date
114 ) is
115 select
116 nvl(min(ptp2.start_date), END_OF_TIME)
117 from
118 per_time_periods ptp, per_time_periods ptp2, per_assignments_f ass
119 where
120 ass.assignment_id = p_assignment_id
121 and
122 p_upload_date between ass.effective_start_date and ass.effective_end_date
123 and
124 ptp.payroll_id = ass.payroll_id
125 and
126 ptp2.payroll_id = ptp.payroll_id
127 and
128 p_upload_date between ptp.start_date and ptp.end_date
129 and
130 ptp2.prd_information3 = ptp.prd_information3;
131
132 -- Returns the start date of the current period on the upload date.
133 cursor csr_period_start
134 (
135 p_assignment_id number,
136 p_upload_date date
137 ) is
138 select
139 nvl(ptp.start_date, END_OF_TIME)
140 from
141 per_time_periods ptp, per_assignments_f ass
142 where
143 ass.assignment_id = p_assignment_id
144 and
145 ptp.payroll_id = ass.payroll_id
146 and
147 p_upload_date between ass.effective_start_date and ass.effective_end_date
148 and
149 p_upload_date between ptp.start_date and ptp.end_date;
150
151 -- Returns the start date of the assignment.
152 cursor csr_asg_itd_start
153 (
154 p_assignment_id number,
155 p_upload_date date
156 ) is
157 select
158 nvl(min(ass.effective_start_date), END_OF_TIME)
159 from
160 per_assignments_f ass
161 where
162 ass.assignment_id = p_assignment_id
163 and
164 ass.payroll_id is not null
165 and
166 ass.effective_start_date <= p_upload_date;
167
168 -- This cursor takes the assignment, the expiry_date and the upload_date
169 -- and returns the next regular_payment_date after the expiry_date for
170 -- that particular payroll.
171 cursor csr_regular_payment
172 (
173 l_assignment_id number,
174 l_upload_date date,
175 l_expiry_date date
176 ) is
177 select
178 min(ptp.regular_payment_date)
179 from
180 per_time_periods ptp, per_assignments_f ass
181 where
182 ass.assignment_id = l_assignment_id
183 and
184 ptp.payroll_id = ass.payroll_id
185 and
186 l_upload_date between ass.effective_start_date and ass.effective_end_date
187 and
188 ptp.regular_payment_date between l_expiry_date and l_upload_date;
189
190 -- This cursor takes the assignment, the expiry_date and the upload_date
191 -- and returns the next regular_payment_date after the expiry_date for
192 -- that particular payroll.
193 cursor csr_regular_payment2
194 (
195 l_assignment_id number,
196 l_upload_date date,
197 l_expiry_date date
198 ) is
199 select
200 ptp.regular_payment_date
201 from
202 per_time_periods ptp, per_assignments_f ass
203 where
204 ass.assignment_id = l_assignment_id
205 and
206 ptp.payroll_id = ass.payroll_id
207 and
208 l_upload_date between ass.effective_start_date and ass.effective_end_date
209 and
210 l_expiry_date between start_date and end_date;
211
212 -- Generic start date variable.
213 l_start_date date;
214
215 -- Holds the assignment start date.
216 l_asg_itd_start_date date;
217
218 -- Holds the first regular payment date after the expiry date of the dimension.
219 l_regular_date date;
220
221 -- Holds the expiry date of the dimension.
222 l_expiry_date date;
223
224 -- Holds the business group of the dimension.
225 l_business_group_id number;
226
227 --------------------------------------------------------------------------------------
228 begin -- Expiry_Date - Main --
229 --------------------------------------------------------------------------------------
230
231 -- What is the start date of the assignment? All loading must come after this date.
232 open csr_asg_itd_start(p_assignment_id, p_upload_date);
233 fetch csr_asg_itd_start into l_asg_itd_start_date;
234 close csr_asg_itd_start;
235
236
237 -- Return the date on which the dimension expires.
238 if substr(p_dimension_name, 31, 4) = 'USER' then
239
240 -- User balance
241 select
242 business_group_id
243 into
244 l_business_group_id
245 from
246 per_assignments_f
247 where
248 assignment_id = p_assignment_id;
249
250 l_expiry_date := hr_gbbal.dimension_reset_date
251 (
252 p_dimension_name,
253 p_upload_date,
254 l_business_group_id
255 );
256
257 l_expiry_date := greatest(l_expiry_date, l_asg_itd_start_date);
258
259 elsif p_dimension_name in ('_ASG_CAL_PTD', '_ASG_TAX_PTD') then
260
261 -- Calculate expiry date for _ASG_CAL_PTD, _ASG_TAX_PTD dimension.
262 -- What is the current period start date?
263 open csr_period_start(p_assignment_id, p_upload_date);
264 fetch csr_period_start into l_start_date;
265 close csr_period_start;
266
267 l_expiry_date := greatest(l_start_date, l_asg_itd_start_date);
268
269 elsif p_dimension_name in ('_ASG_CAL_MTD', '_ASG_TAX_MTD') then
270
271 -- Calculate expiry date for _ASG_CAL_MTD, _ASG_TAX_MTD dimension.
272 -- What is the current payroll month start date?
273 open csr_month_start(p_assignment_id, p_upload_date);
274 fetch csr_month_start into l_start_date;
275 close csr_month_start;
276
277 l_expiry_date := greatest(l_start_date, l_asg_itd_start_date);
278
279 elsif p_dimension_name = '_ASG_TAX_QTD' then
280
281 -- Calculate expiry date for _ASG_TAX_QTD dimension.
282 -- What is the current tax quarter start date?
283 open csr_tax_quarter_start(p_assignment_id, p_upload_date);
284 fetch csr_tax_quarter_start into l_start_date;
285 close csr_tax_quarter_start;
286
287 l_expiry_date := greatest(l_start_date, l_asg_itd_start_date);
288
289 elsif p_dimension_name in ('_ASG_TAX_YTD'
290 ,'_ASG_CLRNO_TAX_YTD'
291 ,'_ASG_LMPSM_TAX_YTD') then
292
293 -- Calculate expiry date for _ASG_TAX_YTD dimension.
294 -- What is the current tax year start date?
295 open csr_tax_year_start(p_assignment_id, p_upload_date);
296 fetch csr_tax_year_start into l_start_date;
297 close csr_tax_year_start;
298
299 l_expiry_date := greatest(l_start_date, l_asg_itd_start_date);
300
301 elsif p_dimension_name = '_ASG_CAL_YTD' then
302
303 -- Calculate expiry date for _ASG_CAL_YTD dimension.
304 -- What is the current calendar year start date?
305 open csr_calendar_year_start(p_assignment_id, p_upload_date);
306 fetch csr_calendar_year_start into l_start_date;
307 close csr_calendar_year_start;
308
309 l_expiry_date := greatest(l_start_date, l_asg_itd_start_date);
310
311 elsif p_dimension_name = '_ASG_ITD' then
312
313 -- Calculate expiry date for _ASG_ITD dimension.
314 l_expiry_date := l_asg_itd_start_date;
315
316 end if;
317
318 return nvl(l_expiry_date,END_OF_TIME);
319
320 exception
321 when no_data_found then
322 l_expiry_date := END_OF_TIME;
323 return l_expiry_date;
324
325 end expiry_date;
326
327 -----------------------------------------------------------------------------
328 -- NAME
329 -- is_supported
330 -- PURPOSE
331 -- Checks if the dimension is supported by the upload process.
332 -- ARGUMENTS
333 -- p_dimension_name - the balance dimension to be checked.
334 -- USES
335 -- NOTES
336 -- Only a subset of the ZA dimensions are supported.
337 -- This is used by pay_balance_upload.validate_dimension.
338 -----------------------------------------------------------------------------
339 function is_supported
340 (
341 p_dimension_name varchar2
342 ) return number is
343 begin
344
345 hr_utility.trace('Entering pay_za_bal_upload.is_supported stub');
346 -- Commneted bug no 5594502
347 -- hr_utility.trace_on(null,'ZABal');
348 -- See if the dimension is supported.
349 if p_dimension_name in
350 (
351 '_ASG_TAX_YTD',
352 '_ASG_TAX_QTD',
353 '_ASG_TAX_PTD',
354 '_ASG_TAX_MTD',
355 '_ASG_CAL_YTD',
356 '_ASG_CAL_MTD',
357 '_ASG_ITD',
358 '_ASG_CLRNO_TAX_YTD',
359 '_ASG_LMPSM_TAX_YTD'
360 )
361 or
362 (
363 substr(p_dimension_name, 31, 4) = 'USER'
364 and
365 substr(p_dimension_name, 40, 3) = 'ASG'
366 )
367 then
368 return 1;
369 else
370 return 0;
371 end if;
372
373 hr_utility.trace('Exiting pay_za_bal_upload.is_supported stub');
374
375 end is_supported;
376
377 -----------------------------------------------------------------------------
378 -- NAME
379 -- include_adjustment
380 -- PURPOSE
381 -- Given a dimension, and relevant contexts and details of an existing
382 -- balance adjustment, it will find out if the balance adjustment effects
383 -- the dimension to be set. Both the dimension to be set and the adjustment
384 -- are for the same assignment and balance.
385 -- ARGUMENTS
386 -- p_balance_type_id - the balance to be set.
387 -- p_dimension_name - the balance dimension to be set.
388 -- p_original_entry_id - ORIGINAL_ENTRY_ID context.
389 -- p_bal_adjustment_rec - details of an existing balance adjustment.
390 -- p_test_batch_line_id -
391 -- USES
392 -- NOTES
393 -- This is used by pay_balance_upload.get_current_value.
394 -----------------------------------------------------------------------------
395 function include_adjustment
396 (
397 p_balance_type_id number,
398 p_dimension_name varchar2,
399 p_original_entry_id number,
400 p_upload_date date,
401 p_batch_line_id number,
402 p_test_batch_line_id number
403 ) return number is
404 begin
405
406 hr_utility.trace('Entering pay_za_bal_upload.include_adjustment stub');
407
408 hr_utility.trace('Exiting pay_za_bal_upload.include_adjustment stub');
409
410 return 1;
411
412 end include_adjustment;
413
414 -----------------------------------------------------------------------------
415 -- NAME
416 -- validate_batch_lines
417 -- PURPOSE
418 -- Applies ZA specific validation to the batch.
419 -- ARGUMENTS
420 -- p_batch_id - the batch to be validate_batch_linesd.
421 -- USES
422 -- NOTES
423 -- This is used by pay_balance_upload.validate_batch_lines.
424 -----------------------------------------------------------------------------
425 procedure validate_batch_lines
426 (
427 p_batch_id number
428 ) is
429 begin
430
431 hr_utility.trace('Entering pay_za_bal_upload.validate_batch_lines stub');
432
433 hr_utility.trace('Exiting pay_za_bal_upload.validate_batch_lines stub');
434
435 end validate_batch_lines;
436
437 end pay_za_bal_upload;
[Home] [Help]