1 package body pay_sg_bal_upload as
2 -- /* $Header: pysgupld.pkb 120.0 2005/05/29 08:50:31 appldev noship $ */
3 --
4 -- +======================================================================+
5 -- | Copyright (c) 1997 Oracle Corporation UK Ltd |
6 -- | Reading, Berkshire, England |
7 -- | All rights reserved. |
8 -- +======================================================================+
9 -- SQL Script File Name : pysgupld.pkb
10 -- Description : This script delivers balance upload support
11 -- functions for the Singapore localization (SG).
12 --
13 -- DELIVERS EXTERNAL functions
14 -- expiry_date
15 -- include_adjustment
16 -- is_supported
17 -- validate_batch_lines
18 --
19 -- Change List:
20 -- ------------
21 --
22 -- ======================================================================
23 -- Version Date Author Bug No. Description of Change
24 -- ------- ----------- -------- ------- -----------------------------
25 -- 115.0 30-JUN-2000 JBailie Initial Version - based on the
26 -- assumption that the pay_balance_upload
27 -- package pybalupl.pkb will explicitly
28 -- call the function
29 -- pay_sg_bal_upload.insert_adjustment
30 -- and pass a record of the previously
31 -- processed adjustments.
32 -- This package needs to be reviewed
33 -- if a different approach is adopted
34 -- 115.1 21-JUL-2000 JBailie Set ship state
35 -- 115.2 06-NOV-2000 jbailie Changed to use pybalupl.pkb 115.17
36 -- removed get_tax_unit
37 -- removed tax_unit_id from expiry_date
38 -- added p_batch_line_id and
39 -- p_test_batch_line_id to
40 -- include_adjustment
41 -- 115.3 20-NOV-2000 jbailie include_adjustment now returns number
42 --
43 -- ======================================================================
44 --
45 --
46 -- Date constants.
47 --
48 START_OF_TIME constant date := to_date('01/01/0001','DD/MM/YYYY');
49 END_OF_TIME constant date := to_date('31/12/4712','DD/MM/YYYY');
50 --
51 --
52 -----------------------------------------------------------------------------
53 -- NAME
54 -- expiry_date
55 -- PURPOSE
56 -- Returns the expiry date of a given dimension relative to a date.
57 -- ARGUMENTS
58 -- p_upload_date - the date on which the balance should be correct.
59 -- p_dimension_name - the dimension being set.
60 -- p_assignment_id - the assignment involved.
61 -- p_original_entry_id - ORIGINAL_ENTRY_ID context.
62 -- USES
63 -- NOTES
64 -- This is used by pay_balance_upload.dim_expiry_date.
65 -- If the expiry date cannot be derived then it is set to the end of time
66 -- to indicate that a failure has occured. The process that uses the
67 -- expiry date knows this rulw and acts accordingly.
68 -- 06-NOV-2000 removed tax_unit_id, as it is no longer required
69 -----------------------------------------------------------------------------
70 --
71 function expiry_date
72 (
73 p_upload_date date
74 ,p_dimension_name varchar2
75 ,p_assignment_id number
76 ,p_original_entry_id number
77 ) return date is
78 --
79 -- Returns the start date of the fiscal year.
80 --
81 cursor csr_fiscal_year
82 (
83 p_assignment_id number
84 ,p_upload_date date
85 ) is
86 select nvl(add_months(fnd_date.canonical_to_date(HOI.ORG_INFORMATION11),
87 12*(floor(months_between(p_upload_date,
88 fnd_date.canonical_to_date(HOI.ORG_INFORMATION11))/12))),
89 END_OF_TIME)
90 from per_assignments_f ASS
91 ,hr_organization_information HOI
92 where ASS.assignment_id = p_assignment_id
93 and p_upload_date between ASS.effective_start_date
94 and ASS.effective_end_date
95 and HOI.organization_id = ASS.business_group_id
96 and upper(HOI.org_information_context) = 'BUSINESS GROUP INFORMATION';
97 --
98 -- Returns the start date of the fiscal quarter.
99 --
100 cursor csr_fiscal_quarter
101 (
102 p_assignment_id number
103 ,p_upload_date date
104 ) is
105 select nvl(add_months(fnd_date.canonical_to_date(HOI.ORG_INFORMATION11),
106 3*(floor(months_between(p_upload_date,
107 fnd_date.canonical_to_date(HOI.ORG_INFORMATION11))/3))),
108 END_OF_TIME)
109 from per_assignments_f ASS
110 ,hr_organization_information HOI
111 where ASS.assignment_id = p_assignment_id
112 and p_upload_date between ASS.effective_start_date
113 and ASS.effective_end_date
114 and HOI.organization_id = ASS.business_group_id
115 and upper(HOI.org_information_context) = 'BUSINESS GROUP INFORMATION';
116 --
117 -- Returns the start date of the current period on the upload date.
118 --
119 cursor csr_period_start
120 (
121 p_assignment_id number
122 ,p_upload_date date
123 ) is
124 select nvl(PTP.start_date, END_OF_TIME)
125 from per_time_periods PTP
126 ,per_assignments_f ASS
127 where ASS.assignment_id = p_assignment_id
128 and p_upload_date between ASS.effective_start_date
129 and ASS.effective_end_date
130 and PTP.payroll_id = ASS.payroll_id
131 and p_upload_date between PTP.start_date
132 and PTP.end_date;
133 --
134 -- Returns the earliest date on which the assignment exists.
135 --
136 cursor csr_ele_ltd_start
137 (
138 p_assignment_id number
139 ,p_upload_date date
140 ) is
141 select nvl(min(ASG.effective_start_date), END_OF_TIME)
142 from per_assignments_f ASG
143 where ASG.assignment_id = p_assignment_id
144 and ASG.effective_start_date <= p_upload_date;
145 --
146 --
147 cursor csr_asg_start_date
148 (p_assignment_id number
149 ,p_upload_date date
150 ,p_expiry_date date
151 ) is
152 select nvl(greatest(min(ASS.effective_start_date), p_expiry_date),
153 END_OF_TIME)
154 from per_assignments_f ASS
155 where ASS.assignment_id = p_assignment_id
156 and ASS.effective_start_date <= p_upload_date
157 and ASS.effective_end_date >= p_expiry_date;
158 --
159 --
160 -- Holds the start of the month for the upload date.
161 --
162 l_month_start_date date;
163 --
164 -- Holds the start of the statutory year for the upload date.
165 --
166 l_tax_yr_start_date date;
167 --
168 -- Holds the start of the statutory quarter for the upload date.
169 --
170 l_tax_qtr_start_date date;
171 --
172 -- Holds the start of the fiscal year for the upload date.
173 --
174 l_fiscal_yr_start_date date;
175 --
176 -- Holds the start of the fiscal quarter for the upload date.
177 --
178 l_fiscal_qtr_start_date date;
179 --
180 -- Holds the start of the period for the upload date.
181 --
182 l_prd_start_date date;
183 --
184 -- Holds the earliest date on which the element entry exists.
185 --
186 l_ele_ltd_start_date date;
187 --
188 -- Holds the expiry date of the dimension.
189 --
190 l_expiry_date date;
191 --
192 begin
193 --
194 -- Calculate the expiry date for the specified dimension relative to the
195 -- upload date, taking into account any contexts where appropriate. Each of
196 -- the calculations also takes into account when the assignment is on a
197 -- payroll to ensure that a balance adjustment could be made at that point
198 -- if it were required.
199 --
200 -- Lifetime to date dimension.
201 --
202 if p_dimension_name in
203 ('_ASG_LTD'
204 ,'_ASG_LE_LTD') then
205 --
206 -- What is the earliest date on which the element entry exists ?
207 --
208 open csr_ele_ltd_start(p_assignment_id
209 ,p_upload_date);
210 fetch csr_ele_ltd_start into l_ele_ltd_start_date;
211 close csr_ele_ltd_start;
212 --
213 l_expiry_date := l_ele_ltd_start_date;
214 --
215 -- Inception to date within a tax unit dimension.
216 --
217 -- Period to date dimensions.
218 --
219 elsif p_dimension_name in
220 ('_ASG_PTD'
221 ,'_ASG_LE_PTD') then
222 --
223 -- What is the current period start date ?
224 --
225 open csr_period_start(p_assignment_id
226 ,p_upload_date);
227 fetch csr_period_start into l_prd_start_date;
228 close csr_period_start;
229 --
230 open csr_asg_start_date(p_assignment_id
231 ,p_upload_date
232 ,l_prd_start_date);
233 fetch csr_asg_start_date into l_expiry_date;
234 close csr_asg_start_date;
235 --
236 -- Month dimensions.
237 --
238 elsif p_dimension_name in
239 ('_ASG_MONTH'
240 ,'_ASG_LE_MONTH') then
241 --
242 -- What is the current month start ?
243 --
244 l_month_start_date := trunc(p_upload_date, 'MON');
245 open csr_asg_start_date(p_assignment_id
246 ,p_upload_date
247 ,l_month_start_date);
248 fetch csr_asg_start_date into l_month_start_date;
249 close csr_asg_start_date;
250 --
251 open csr_asg_start_date(p_assignment_id
252 ,p_upload_date
253 ,l_month_start_date);
254 fetch csr_asg_start_date into l_expiry_date;
255 close csr_asg_start_date;
256 --
257 -- Quarter to date dimensions.
258 --
259 elsif p_dimension_name in
260 ('_ASG_QTD'
261 ,'_ASG_LE_QTD') then
262 --
263 -- What is the start date of the tax quarter ?
264 --
265 l_tax_qtr_start_date := trunc(p_upload_date, 'Q');
266 open csr_asg_start_date(p_assignment_id
267 ,p_upload_date
268 ,l_tax_qtr_start_date);
269 fetch csr_asg_start_date into l_tax_qtr_start_date;
270 close csr_asg_start_date;
271 --
272 l_expiry_date := l_tax_qtr_start_date;
273 --
274 -- Year to date dimensions.
275 --
276 elsif p_dimension_name in
277 ('_ASG_YTD'
278 ,'_ASG_LE_YTD') then
279 --
280 -- What is the start date of the tax year ?
281 --
282 l_tax_yr_start_date := trunc(p_upload_date, 'Y');
283 open csr_asg_start_date(p_assignment_id
284 ,p_upload_date
285 ,l_tax_yr_start_date);
286 fetch csr_asg_start_date into l_tax_yr_start_date;
287 close csr_asg_start_date;
288 --
289 -- Ensure that the expiry date is at a date where the assignment is to the
290 -- correct legal company ie. matches the TAX_UNIT_ID context specified.
291 --
292 l_expiry_date := l_tax_yr_start_date;
293 --
294 -- Fiscal quarter to date dimensions.
295 --
296 elsif p_dimension_name in
297 ('_ASG_FQTD'
298 ,'_ASG_LE_FQTD') then
299 --
300 -- What is the start date of the fiscal quarter ?
301 --
302 open csr_fiscal_quarter(p_assignment_id
303 ,p_upload_date);
304 fetch csr_fiscal_quarter into l_fiscal_qtr_start_date;
305 close csr_fiscal_quarter;
306 --
307 open csr_asg_start_date(p_assignment_id
308 ,p_upload_date
309 ,l_fiscal_qtr_start_date);
310 fetch csr_asg_start_date into l_expiry_date;
311 close csr_asg_start_date;
312 --
313 -- Fiscal year to date dimensions.
314 --
315 elsif p_dimension_name in
316 ('_ASG_FYTD'
317 ,'_ASG_LE_FYTD') then
318 --
319 -- What is the start date of the fiscal year ?
320 --
321 open csr_fiscal_year(p_assignment_id
322 ,p_upload_date);
323 fetch csr_fiscal_year into l_fiscal_yr_start_date;
324 close csr_fiscal_year;
325 --
326 open csr_asg_start_date(p_assignment_id
327 ,p_upload_date
328 ,l_fiscal_yr_start_date);
329 fetch csr_asg_start_date into l_expiry_date;
330 close csr_asg_start_date;
331 --
332 end if;
333 --
334 -- return the date on which the dimension expires.
335 --
336 return (l_expiry_date);
337 --
338 end expiry_date;
339 --
340 -----------------------------------------------------------------------------
341 -- NAME
342 -- is_supported
343 -- PURPOSE
344 -- Checks if the dimension is supported by the upload process.
345 -- ARGUMENTS
346 -- p_dimension_name - the balance dimension to be checked.
347 -- USES
348 -- NOTES
349 -- Only a subset of the US dimensions are supported and these have been
350 -- picked to allow effective migration to release 10.
351 -- This is used by pay_balance_upload.validate_dimension.
352 -----------------------------------------------------------------------------
353 --
354 function is_supported
355 (
356 p_dimension_name varchar2
357 ) return number is
358 begin
359 --
360 hr_utility.trace('Entering pay_sg_bal_upload.is_supported');
361 --
362 -- See if the dimension is supported.
363 --
364 if p_dimension_name in
365 ('_ASG_LE_PTD'
366 ,'_ASG_LE_MONTH'
367 ,'_ASG_LE_QTD'
368 ,'_ASG_LE_YTD'
369 ,'_ASG_LE_FQTD'
370 ,'_ASG_LE_FYTD'
371 ,'_ASG_LE_LTD'
372 ,'_ASG_PTD'
373 ,'_ASG_MONTH'
374 ,'_ASG_QTD'
375 ,'_ASG_YTD'
376 ,'_ASG_FQTD'
377 ,'_ASG_FYTD'
378 ,'_ASG_LTD') then
379 return (1); -- denotes TRUE
380 else
381 return (0); -- denotes FALSE
382 end if;
383 --
384 hr_utility.trace('Exiting pay_sg_bal_upload.is_supported');
385 --
386 end is_supported;
387 --
388 -----------------------------------------------------------------------------
389 -- NAME
390 -- include_adjustment
391 -- PURPOSE
392 -- Given a dimension, and relevant contexts and details of an existing
393 -- balanmce adjustment, it will find out if the balance adjustment effects
394 -- the dimension to be set. Both the dimension to be set and the adjustment
395 -- are for the same assignment and balance. The adjustment also lies between
396 -- the expiry date of the new balance and the date on which it is to set.
397 -- ARGUMENTS
401 -- p_upload_date
398 -- p_balance_type_id - the balance to be set.
399 -- p_dimension_name - the balance dimension to be set.
400 -- p_original_entry_id - ORIGINAL_ENTRY_ID context.
402 -- p_batch_line_id
403 -- p_test_batch_line_id
404 -- USES
405 -- NOTES
406 -- All the US dimensions affect each other when they share the same context
407 -- values so there is no special support required for individual dimensions.
408 -- This is used by pay_balance_upload.get_current_value.
409 -----------------------------------------------------------------------------
410 --
411 function include_adjustment
412 (
413 p_balance_type_id number
414 ,p_dimension_name varchar2
415 ,p_original_entry_id number
416 ,p_upload_date date
417 ,p_batch_line_id number
418 ,p_test_batch_line_id number
419 ) return number is
420 --
421 -- Does the balance adjustment effect the new balance dimension.
422 --
423 cursor csr_is_included
424 (
425 p_balance_type_id number
426 ,p_tax_unit_id number
427 ,p_original_entry_id number
428 ,p_bal_adj_tax_unit_id number
429 ,p_bal_adj_original_entry_id number
430 ) is
431 select BT.balance_type_id
432 from pay_balance_types BT
433 where BT.balance_type_id = p_balance_type_id
434 --
435 -- TAX_UNIT_ID context NB. if the tax unit is used then only those
436 -- adjustments which are for the same tax unit can be included.
437 --
438 and nvl(p_tax_unit_id, nvl(p_bal_adj_tax_unit_id, -1)) =
439 nvl(p_bal_adj_tax_unit_id, -1)
440 --
441 -- ORIGINAL_ENTRY_ID context NB. this context controls the expiry
442 -- date of the dimension in the same way as the QTD dimension. Any
443 -- existing balance adjustments that lie between the upload date
444 -- and the expiry date are all included. There is no special
445 -- criteria that has to be met.
446 --
447 and 1 = 1;
448 --
449 -- Get the tax_unit_id from the original balance batch line
450 --
451 cursor csr_get_tax_unit
452 (
453 p_batch_line_id number
454 ) is
455 select htuv.tax_unit_id
456 from pay_balance_batch_lines pbbl
457 ,hr_tax_units_v htuv
458 where pbbl.batch_line_id = p_batch_line_id
459 and pbbl.tax_unit_id = htuv.tax_unit_id
460 and pbbl.tax_unit_id is not null
461 union all
462 select htuv.tax_unit_id
463 from pay_balance_batch_lines pbbl
464 ,hr_tax_units_v htuv
465 where pbbl.batch_line_id = p_batch_line_id
466 and upper(pbbl.gre_name) = upper(htuv.name)
467 and pbbl.tax_unit_id is null;
468 --
469 -- Get tax_unit_id and original_entry_id for previously tested adjustments
470 --
471 cursor csr_get_tested_adjustments
472 (
473 p_test_batch_line_id number
474 ) is
475 select tax_unit_id
476 ,original_entry_id
477 from pay_temp_balance_adjustments
478 where batch_line_id = p_test_batch_line_id;
479 --
480 -- The balance returned by the include check.
481 --
482 l_bal_type_id number;
483 --
484 l_tax_unit_id number;
485 --
486 l_adj_tax_unit_id number;
487 l_adj_orig_entry_id number;
488 --
489 begin
490 --
491 hr_utility.trace('Entering pay_sg_bal_upload.include_adjustment_test');
492 --
493 open csr_get_tax_unit(p_batch_line_id);
494 fetch csr_get_tax_unit into l_tax_unit_id;
495 close csr_get_tax_unit;
496 --
497 open csr_get_tested_adjustments(p_test_batch_line_id);
498 fetch csr_get_tested_adjustments into l_adj_tax_unit_id, l_adj_orig_entry_id;
499 close csr_get_tested_adjustments;
500 --
501 -- Does the balance adjustment effect the new balance ?
502 --
503 hr_utility.trace('balance_type_id = '||to_char(p_balance_type_id));
504 hr_utility.trace('tax_unit_id = '||to_char(l_tax_unit_id));
505 hr_utility.trace('original_entry_id = '||to_char(p_original_entry_id));
506 hr_utility.trace('BA tax_unit_id = '||to_char(l_adj_tax_unit_id));
507 hr_utility.trace('BA original_entry_id = '||to_char(l_adj_orig_entry_id));
508 --
509 open csr_is_included(p_balance_type_id
510 ,l_tax_unit_id
511 ,p_original_entry_id
512 ,l_adj_tax_unit_id
513 ,l_adj_orig_entry_id);
514 fetch csr_is_included into l_bal_type_id;
515 close csr_is_included;
516 --
517 hr_utility.trace('Exiting pay_sg_bal_upload.include_adjustment_test');
518 --
519 -- Adjustment does contribute to the new balance.
520 --
521 if l_bal_type_id is not null then
522 return (1); --TRUE
523 --
524 -- Adjustment does not contribute to the new balance.
525 --
526 else
527 return (0); --FALSE
528 end if;
529 --
530 end include_adjustment;
531 --
532 -----------------------------------------------------------------------------
533 -- NAME
534 -- validate_batch_lines
535 -- PURPOSE
536 -- Applies SG specific validation to the batch.
537 -- ARGUMENTS
538 -- p_batch_id - the batch to be validate_batch_linesd.
539 -- USES
540 -- NOTES
541 -- This is used by pay_balance_upload.validate_batch_lines.
542 -----------------------------------------------------------------------------
543 --
544 procedure validate_batch_lines
545 (
546 p_batch_id number
547 ) is
548 begin
549 --
550 hr_utility.trace('Entering pay_sg_bal_upload.validate_batch_lines');
551 --
552 hr_utility.trace('Exiting pay_sg_bal_upload.validate_batch_lines');
553 --
554 end validate_batch_lines;
555 --
556 end pay_sg_bal_upload;
[Home] [Help]