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