1 PACKAGE BODY pay_ca_bal_upload AS
2 /* $Header: pycaupld.pkb 115.4 2003/03/28 01:43:42 pganguly ship $ */
3 /*
4 Copyright (c) Oracle Corporation 1995 All rights reserved
5 PRODUCT
6 Oracle*Payroll
7 NAME
8 pycaupld.pkb
9 DESCRIPTION
10 Stub File.
11 Provides support for the upload of balances based on CA dimensions.
12 EXTERNAL
13 get_tax_unit
14 get_source_id
15 expiry_date
16 include_adjustment
17 is_supported
18 validate_batch_lines
19 INTERNAL
20 MODIFIED (DD-MON-YYYY)
21 110.0 A.Logue 11-Jul-1997 created.
22 115.1 JARTHURT 05-JAN-2001 Updates to add required Canadian balance
23 dimensions and comply with new dynamic
24 SQL calls from pay_balance_upload.
25 115.2 JARTHURT 15-JAN-2001 Corrected balance dimension list and
26 month truncation.
27 115.3 JARTHURT 22-JAN-2001 Corrected type of jurisdiction_code
28 115.3 PGANGULY 27-MAR-2003 Added the following dimensions in the
29 is_supported, expiry_date functions:
30 Assignment within Reporting Unit Year to
31 Date/Month. Fixed Bug# 2859270. Added
32 dbdrv, set verify off for GSCC.
33 */
34 --
35 -- Date constants.
36 --
37 START_OF_TIME constant date := to_date('01/01/0001','DD/MM/YYYY');
38 END_OF_TIME constant date := to_date('31/12/4712','DD/MM/YYYY');
39 --
40 -----------------------------------------------------------------------------
41 -- NAME
42 -- expiry_date
43 -- PURPOSE
44 -- Returns the expiry date of a given dimension relative to a date.
45 -- ARGUMENTS
46 -- p_upload_date - the date on which the balance should be correct.
47 -- p_dimension_name - the dimension being set.
48 -- p_assignment_id - the assignment involved.
49 -- p_original_entry_id - ORIGINAL_ENTRY_ID context.
50 -- USES
51 -- NOTES
52 -- This is used by pay_balance_upload.dim_expiry_date.
53 -- If the expiry date cannot be derived then it is set to the end of time
54 -- to indicate that a failure has occured. The process that uses the
55 -- expiry date knows this rule and acts accordingly.
56 -----------------------------------------------------------------------------
57 --
58 function expiry_date
59 (
60 p_upload_date date
61 ,p_dimension_name varchar2
62 ,p_assignment_id number
63 ,p_original_entry_id number
64 ) return date is
65 --
66 -- Returns the start date of the current period on the upload date.
67 --
68 cursor csr_period_start
69 (
70 p_assignment_id number
71 ,p_upload_date date
72 ) is
73 select nvl(PTP.start_date, END_OF_TIME)
74 from per_time_periods PTP
75 ,per_assignments_f ASS
76 where ASS.assignment_id = p_assignment_id
77 and p_upload_date between ASS.effective_start_date
78 and ASS.effective_end_date
79 and PTP.payroll_id = ASS.payroll_id
80 and p_upload_date between PTP.start_date
81 and PTP.end_date;
82 --
83 -- Returns the earliest date on which the assignment exists.
84 --
85 cursor csr_ele_itd_start
86 (
87 p_assignment_id number
88 ,p_upload_date date
89 ) is
90 select nvl(min(ASG.effective_start_date), END_OF_TIME)
91 from per_all_assignments_f ASG
92 where ASG.assignment_id = p_assignment_id
93 and ASG.effective_start_date <= p_upload_date;
94 --
95 cursor csr_asg_start_date
96 (p_assignment_id number
97 ,p_upload_date date
98 ,p_expiry_date date
99 ) is
100 select nvl(greatest(min(ASS.effective_start_date), p_expiry_date),
101 END_OF_TIME)
102 from per_all_assignments_f ASS
103 where ASS.assignment_id = p_assignment_id
104 and ASS.effective_start_date <= p_upload_date
105 and ASS.effective_end_date >= p_expiry_date;
106 --
107 --
108 -- Holds the start of the tax year for the upload date.
109 --
110 l_tax_yr_start_date date;
111 --
112 -- Holds the start of the tax month for the upload date.
113 --
114 l_tax_month_start_date date;
115 --
116 -- Holds the earliest date on which the element entry exists.
117 --
118 l_ele_itd_start_date date;
119 --
120 -- Holds the expiry date of the dimension.
121 --
122 l_prd_start_date date;
123 l_expiry_date date;
124 --
125 begin
126 --
127 --
128 -- Calculate the expiry date for the specified dimension relative to the
129 -- upload date, taking into account any contexts where appropriate. Each of
130 -- the calculations also takes into account when the assignment is on a
131 -- payroll to ensure that a balance adjustment could be made at that point
132 -- if it were required.
133 --
134 -- Inception to date dimension.
135 --
136 if p_dimension_name in
137 ('ASSIGNMENT WITHIN GOVERNMENT REPORTING ENTITY INCEPTION TO DATE') then
138 --
139 -- What is the earliest date on which the element entry exists ?
140 --
141 open csr_ele_itd_start(p_assignment_id
142 ,p_upload_date);
143 fetch csr_ele_itd_start into l_expiry_date;
144 close csr_ele_itd_start;
145 --
146 -- Period to date dimensions.
147 --
148 elsif p_dimension_name in
149 ('ASSIGNMENT WITHIN GOVERNMENT REPORTING ENTITY PERIOD TO DATE'
150 ,'ASSIGNMENT IN JD WITHIN GRE PERIOD TO DATE') then
151 --
152 -- What is the current period start date ?
153 --
154 open csr_period_start(p_assignment_id
155 ,p_upload_date);
156 fetch csr_period_start into l_prd_start_date;
157 close csr_period_start;
158
159 open csr_asg_start_date(p_assignment_id
160 ,p_upload_date
161 ,l_prd_start_date);
162 fetch csr_asg_start_date into l_expiry_date;
163 close csr_asg_start_date;
164 --
165 -- Quarter to date dimensions.
166 --
167 elsif p_dimension_name in
168 ('ASSIGNMENT WITHIN GOVERNMENT REPORTING ENTITY MONTH'
169 ,'ASSIGNMENT IN JD WITHIN GRE MONTH'
170 ,'ASSIGNMENT WITHIN REPORTING UNIT MONTH' ) then
171 --
172 -- What is the start date of the tax month ?
173 --
174 l_tax_month_start_date := trunc(p_upload_date, 'MON');
175 open csr_asg_start_date(p_assignment_id
176 ,p_upload_date
177 ,l_tax_month_start_date);
178 fetch csr_asg_start_date into l_expiry_date;
179 close csr_asg_start_date;
180
181 --
182 -- Year to date dimensions.
183 --
184 elsif p_dimension_name in
185 ('ASSIGNMENT WITHIN GOVERNMENT REPORTING ENTITY YEAR TO DATE'
186 ,'ASSIGNMENT IN JD WITHIN GRE YEAR TO DATE'
187 ,'ASSIGNMENT WITHIN REPORTING UNIT YEAR TO DATE' ) then
188 --
189 -- What is the start date of the tax year ?
190 --
191 l_tax_yr_start_date := trunc(p_upload_date, 'Y');
192 open csr_asg_start_date(p_assignment_id
193 ,p_upload_date
194 ,l_tax_yr_start_date);
195 fetch csr_asg_start_date into l_expiry_date;
196 close csr_asg_start_date;
197 end if;
198
199 --
200 -- return the date on which the dimension expires.
201 --
202 return (l_expiry_date);
203 --
204 end expiry_date;
205 --
206 -----------------------------------------------------------------------------
207 -- NAME
208 -- is_supported
209 -- PURPOSE
210 -- Checks if the dimension is supported by the upload process.
211 -- ARGUMENTS
212 -- p_dimension_name - the balance dimension to be checked.
213 -- USES
214 -- NOTES
215 -- Only a subset of the CA dimensions are supported.
216 -- This is used by pay_balance_upload.validate_dimension.
217 -----------------------------------------------------------------------------
218 --
219 function is_supported
220 (
221 p_dimension_name varchar2
222 ) return number is
223 begin
224 --
225 hr_utility.trace('Entering pay_ca_bal_upload.is_supported stub');
226 --
227 -- See if the dimension is supported.
228 --
229 if p_dimension_name in
230 ('ASSIGNMENT WITHIN GOVERNMENT REPORTING ENTITY INCEPTION TO DATE'
231 ,'ASSIGNMENT WITHIN GOVERNMENT REPORTING ENTITY PERIOD TO DATE'
232 ,'ASSIGNMENT WITHIN GOVERNMENT REPORTING ENTITY YEAR TO DATE'
233 ,'ASSIGNMENT WITHIN GOVERNMENT REPORTING ENTITY MONTH'
234 ,'ASSIGNMENT IN JD WITHIN GRE MONTH'
235 ,'ASSIGNMENT IN JD WITHIN GRE YEAR TO DATE'
236 ,'ASSIGNMENT IN JD WITHIN GRE PERIOD TO DATE'
237 ,'ASSIGNMENT WITHIN REPORTING UNIT YEAR TO DATE'
238 ,'ASSIGNMENT WITHIN REPORTING UNIT MONTH' ) then
239 return (1); --(TRUE);
240 else
241 return (0); --(FALSE);
242 end if;
243 --
244 hr_utility.trace('Exiting pay_ca_bal_upload.is_supported stub');
245 --
246 end is_supported;
247 --
248 --
249 -----------------------------------------------------------------------------
250 -- NAME
251 -- include_adjustment
252 -- PURPOSE
253 -- Given a dimension, and relevant contexts and details of an existing
254 -- balanmce adjustment, it will find out if the balance adjustment effects
255 -- the dimension to be set. Both the dimension to be set and the adjustment
256 -- are for the same assignment and balance. The adjustment also lies between
257 -- the expiry date of the new balance and the date on which it is to set.
258 -- ARGUMENTS
259 -- p_balance_type_id - the balance to be set.
260 -- p_dimension_name - the balance dimension to be set.
261 -- p_original_entry_id - ORIGINAL_ENTRY_ID context.
262 -- p_upload_date
263 -- p_batch_line_id
264 -- p_test_batch_line_id
265 -- USES
266 -- NOTES
267 -- All the CA dimensions affect each other when they share the same context
268 -- values so there is no special support required for individual dimensions.
269 -- This is used by pay_balance_upload.get_current_value.
270 -----------------------------------------------------------------------------
271 --
272 function include_adjustment
273 (
274 p_balance_type_id number
275 ,p_dimension_name varchar2
276 ,p_original_entry_id number
277 ,p_upload_date date
278 ,p_batch_line_id number
279 ,p_test_batch_line_id number
280 ) return number is
281 --
282 -- Does the balance adjustment effect the new balance dimension.
283 --
284 cursor csr_is_included
285 (
286 p_balance_type_id number
287 ,p_tax_unit_id number
288 ,p_jurisdiction_code varchar
289 ,p_original_entry_id number
290 ,p_bal_adj_tax_unit_id number
291 ,p_bal_adj_jurisdiction_code varchar
292 ,p_bal_adj_original_entry_id number
293 ) is
294 select BT.balance_type_id
295 from pay_balance_types BT
296 where BT.balance_type_id = p_balance_type_id
297 --
298 -- JURISDICTION_CODE context NB. if the jurisdiction code is
299 -- used then only those adjustments which are for the same
300 -- jurisdiction code can be included.
301 --
302 and ((p_jurisdiction_code is null) or
303 (p_jurisdiction_code is not null and
304 substr(p_bal_adj_jurisdiction_code, 1, BT.jurisdiction_level) =
305 substr(p_jurisdiction_code , 1, BT.jurisdiction_level)))
306 --
307 -- TAX_UNIT_ID context NB. if the tax unit is used then only those
308 -- adjustments which are for the same tax unit can be included.
309 --
310 and nvl(p_tax_unit_id, nvl(p_bal_adj_tax_unit_id, -1)) =
311 nvl(p_bal_adj_tax_unit_id, -1)
312 --
313 -- ORIGINAL_ENTRY_ID context NB. this context controls the expiry
314 -- date of the dimension in the same way as the QTD dimension. Any
315 -- existing balance adjustments that lie between the upload date
316 -- and the expiry date are all included. There is no special
317 -- criteria that has to be met.
318 --
319 and 1 = 1;
320 --
321 -- Get the tax_unit_id from the original balance batch line
322 --
323 cursor csr_get_tax_unit
324 (
325 p_batch_line_id number
326 ) is
327 select htuv.tax_unit_id
328 from pay_balance_batch_lines pbbl
329 ,hr_tax_units_v htuv
330 where pbbl.batch_line_id = p_batch_line_id
331 and pbbl.tax_unit_id = htuv.tax_unit_id
332 and pbbl.tax_unit_id is not null
333 union all
334 select htuv.tax_unit_id
335 from pay_balance_batch_lines pbbl
336 ,hr_tax_units_v htuv
337 where pbbl.batch_line_id = p_batch_line_id
338 and upper(pbbl.gre_name) = upper(htuv.name)
339 and pbbl.tax_unit_id is null;
340 --
341 -- Get the jurisdiction code from the original balance batch line
342 --
343 cursor csr_get_jurisdiction_code
344 (
345 p_batch_line_id number
346 ) is
347 select prov.province_abbrev
348 from pay_balance_batch_lines pbbl
349 ,pay_ca_provinces_v prov
350 where pbbl.batch_line_id = p_batch_line_id
351 and pbbl.jurisdiction_code = prov.province_abbrev
352 and pbbl.jurisdiction_code is not null;
353 --
354 -- Get tax_unit_id, jurisdiction_code and original_entry_id for
355 -- previously tested adjustments
356 --
357 cursor csr_get_tested_adjustments
358 (
359 p_test_batch_line_id number
360 ) is
361 select tax_unit_id
362 ,jurisdiction_code
363 ,original_entry_id
364 from pay_temp_balance_adjustments
365 where batch_line_id = p_test_batch_line_id;
366 --
367 -- The balance returned by the include check.
368 --
369 l_bal_type_id number;
370 --
371 l_tax_unit_id number;
372 l_jurisdiction_code varchar2(2);
373 --
374 l_adj_tax_unit_id number;
375 l_adj_jurisdiction_code varchar2(2);
376 l_adj_orig_entry_id number;
377 --
378 begin
379 --
380 --
381 open csr_get_tax_unit(p_batch_line_id);
382 fetch csr_get_tax_unit into l_tax_unit_id;
383 close csr_get_tax_unit;
384 --
385 open csr_get_jurisdiction_code(p_batch_line_id);
386 fetch csr_get_jurisdiction_code into l_jurisdiction_code;
387 close csr_get_jurisdiction_code;
388 --
389 open csr_get_tested_adjustments(p_test_batch_line_id);
390 fetch csr_get_tested_adjustments into l_adj_tax_unit_id,
391 l_adj_jurisdiction_code,
392 l_adj_orig_entry_id;
393 close csr_get_tested_adjustments;
394 --
395 -- Does the balance adjustment effect the new balance ?
396 --
397 hr_utility.trace('balance_type_id = '||to_char(p_balance_type_id));
398 hr_utility.trace('tax_unit_id = '||to_char(l_tax_unit_id));
399 hr_utility.trace('jurisdiction_code = '||l_jurisdiction_code);
400 hr_utility.trace('original_entry_id = '||to_char(p_original_entry_id));
404 --
401 hr_utility.trace('BA tax_unit_id = '||to_char(l_adj_tax_unit_id));
402 hr_utility.trace('BA jurisdiction_code = '||l_adj_jurisdiction_code);
403 hr_utility.trace('BA original_entry_id = '||to_char(l_adj_orig_entry_id));
405 open csr_is_included(p_balance_type_id
406 ,l_tax_unit_id
407 ,l_jurisdiction_code
408 ,p_original_entry_id
409 ,l_adj_tax_unit_id
410 ,l_adj_jurisdiction_code
411 ,l_adj_orig_entry_id);
412 fetch csr_is_included into l_bal_type_id;
413 close csr_is_included;
414 --
415 hr_utility.trace('Exiting pay_ca_bal_upload.include_adjustment_test');
416 --
417 -- Adjustment does contribute to the new balance.
418 --
419 if l_bal_type_id is not null then
420 return (1); --TRUE
421 --
422 -- Adjustment does not contribute to the new balance.
423 --
424 else
425 return (0); --FALSE
426 end if;
427 --
428 end include_adjustment;
429 --
430 -----------------------------------------------------------------------------
431 -- NAME
432 -- validate_batch_lines
433 -- PURPOSE
434 -- Applies CA specific validation to the batch.
435 -- ARGUMENTS
436 -- p_batch_id - the batch to be validate_batch_linesd.
437 -- USES
438 -- NOTES
439 -- This is used by pay_balance_upload.validate_batch_lines.
440 -----------------------------------------------------------------------------
441 --
442 procedure validate_batch_lines
443 (
444 p_batch_id number
445 ) is
446 begin
447 --
448 hr_utility.trace('Entering pay_ca_bal_upload.validate_batch_lines stub');
449 --
450 hr_utility.trace('Exiting pay_ca_bal_upload.validate_batch_lines stub');
451 --
452 end validate_batch_lines;
453 --
454 end pay_ca_bal_upload;
[Home] [Help]