1 package body pay_us_bal_upload as
2 /* $Header: pyusupld.pkb 120.5 2006/09/25 13:44:56 alikhar noship $ */
3 /*
4 Copyright (c) Oracle Corporation 1995 All rights reserved
5 PRODUCT
6 Oracle*Payroll
7 NAME
8 pyusxpry.pkb
9 DESCRIPTION
10 Provides support for the upload of balances based on US dimensions.
11 EXTERNAL
12 expiry_date
13 get_tax_unit
14 include_adjustment
15 is_supported
16 validate_batch_lines
17 INTERNAL
18 MODIFIED (DD-MON-YYYY)
19 115.12 alikhar 25-Sep-2006 Bug 5181998: Modified function expiry
20 date to return dimension period start
21 date for dimensions with GRE context
22 during Purge Process.
23 115.11 rdhingra 22-Mar-2006 Bug 5042715: Modified cursor
24 c_td_start_date to remove FTS on
25 per_time_periods.
26 115.10 kvsankar 11-Aug-2005 Enabled the dimension '_ASG_GRE_TD_TDPTD'
27 for Balance Initialization.
28 Modified the procedure 'EXPIRY_DATE'
29 to return the start date of the Time
30 Definition period on which the Upload
31 is done.
32 115.8,9 SSattini 21-Jul-2004 Bug 4505420 - Modified the cursors
33 csr_asg_start_date,
34 csr_assignment_on_tax_unit,
35 csr_ele_itd_start and
36 csr_ele_itd_tax_unit_start in
37 expiry_date function, so that it
38 returns correct expiry_date value.
39 115.7 SSattini 16-Jul-2004 Added 'WHENEVER OSERROR' for GSCC
40 compliance.
41 115.6 SSattini 16-Jul-2004 Bug 3751001 - Modified the cursors
42 csr_asg_start_date,
43 csr_assignment_on_tax_unit,
44 csr_ele_itd_start and
45 csr_ele_itd_tax_unit_start in
46 expiry_date function, so that it
47 returns correct expiry_date value
48 when Assignment hire_date and balance
49 upload_date falls in the same pay period.
50 115.4 D.Saxby 10-Jan-2002 Bug 2144736 - further alterations to
51 expiry_date procedure to deal correctly
52 with a further case with assigment
53 assigned to payroll earlier than time
54 periods exist.
55 115.4 D.Saxby 17-Dec-2001 Bug 2153245, first release of purge.
56 Support appropriate LTD dimensions
57 and ensure can rollup assignments that
58 do not have payroll across their entire
59 lifetime.
60 Added dbdrv line.
61 115.3 A.Logue 07-Oct-1999 Change to_number(segment1) to
62 to_char(tax_unit_id) to avoid
63 to_number errors.
64 40.10 J.Alloun 30-Jul-1996 Added error handling.
65 40.9 N.Bristow 08-May-1996 Bug 359005. Now tax_unit_id is now
66 passed to expiry_date and
67 include_adjustment.
68 40.8 S Desai 27-Feb-1996 Bug 333439: Date format was 'DD-MON-YY'.
69 40.7 N.Bristow 14-Dec-1995 Expiry_date was not checking the
70 creation date of the assignment
71 for certain balances.
72 40.6 N.Bristow 03-Nov-1995 The cursors retrieving the date of an
73 itd adjustment were incorrect.
74 40.5 N.Bristow 02-Nov-1995 Statements that reference the
75 hr_tax_units_v view run very slow.
76 Changed to access base tables.
77 40.3 N.Bristow 25-Aug-1995 Now uses the element type for ITD
78 balances.
79 40.2 N.Bristow 06-Jul-1995 General bugs discovered when testing.
80 40.1 J.S.Hobbs 16-May-1995 created.
81 */
82 --
83 -- Date constants.
84 --
85 START_OF_TIME constant date := to_date('01/01/0001','DD/MM/YYYY');
86 END_OF_TIME constant date := to_date('31/12/4712','DD/MM/YYYY');
87 --
88 --
89 -- Global for current batch info
90 --
91 g_batch_info pay_balance_upload.t_batch_info_rec;
92 --
93 --
94 -----------------------------------------------------------------------------
95 -- NAME
96 -- get_tax_unit
97 -- PURPOSE
98 -- Returns the legal company an assignment is associated with at
99 -- particular point in time.
100 -- ARGUMENTS
101 -- p_assignment_id - the assignment
102 -- p_effective_date - the date on which the information is required.
103 -- USES
104 -- NOTES
105 -----------------------------------------------------------------------------
106 --
107 function get_tax_unit
108 (
109 p_assignment_id number
110 ,p_effective_date date
111 ) return number is
112 --
113 -- Retrieves the legal company an assignment belongs to at a given date.
114 --
115 cursor csr_tax_unit
116 (
117 p_assignment_id number
118 ,p_effective_date date
119 ) is
120 select fnd_number.canonical_to_number(SCL.segment1) tax_unit_id
121 from per_assignments_f ASG
122 ,hr_soft_coding_keyflex SCL
123 where ASG.assignment_id = p_assignment_id
124 and SCL.soft_coding_keyflex_id = ASG.soft_coding_keyflex_id
125 and p_effective_date between ASG.effective_start_date
126 and ASG.effective_end_date;
127 --
128 -- Holds the tax unit an assignment belongs to.
129 --
130 l_tax_unit_id number;
131 --
132 begin
133 --
134 hr_utility.trace('Entering pay_us_bal_upload.get_tax_unit');
135 --
136 -- Get the legal company the assignment belongs to.
137 --
138 open csr_tax_unit(p_assignment_id
139 ,p_effective_date);
140 fetch csr_tax_unit into l_tax_unit_id;
141 close csr_tax_unit;
142 --
143 -- Return the tax unit.
144 --
145 return (l_tax_unit_id);
146 --
147 hr_utility.trace('Exiting pay_us_bal_upload.get_tax_unit');
148 --
149 end get_tax_unit;
150 --
151 -----------------------------------------------------------------------------
152 -- NAME
153 -- expiry_date
154 -- PURPOSE
155 -- Returns the expiry date of a given dimension relative to a date.
156 -- ARGUMENTS
157 -- p_upload_date - the date on which the balance should be correct.
158 -- p_dimension_name - the dimension being set.
159 -- p_assignment_id - the assignment involved.
160 -- p_original_entry_id - ORIGINAL_ENTRY_ID context.
161 -- USES
162 -- NOTES
163 -- This is used by pay_balance_upload.dim_expiry_date.
164 -- If the expiry date cannot be derived then it is set to the end of time
165 -- to indicate that a failure has occured. The process that uses the
166 -- expiry date knows this rulw and acts accordingly.
167 -----------------------------------------------------------------------------
168 --
169 function expiry_date
170 (
171 p_upload_date date
172 ,p_dimension_name varchar2
173 ,p_assignment_id number
174 ,p_tax_unit_id number
175 ,p_jurisdiction_code varchar2
176 ,p_original_entry_id number
177 ) return date is
178 --
179 -- Returns the start date of the fiscal year.
180 --
181 cursor csr_fiscal_year
182 (
183 p_assignment_id number
184 ,p_upload_date date
185 ) is
186 select nvl(trunc(p_upload_date -
187 to_char(fnd_date.canonical_to_date(HOI.org_information11),'DDD') +1,'Y')
188 - 1 + to_char(fnd_date.canonical_to_date(HOI.org_information11),'DDD'),
189 END_OF_TIME)
190 from per_assignments_f ASS
191 ,hr_organization_information HOI
192 where ASS.assignment_id = p_assignment_id
193 and p_upload_date between ASS.effective_start_date
194 and ASS.effective_end_date
195 and HOI.organization_id = ASS.business_group_id
196 and upper(HOI.org_information_context) = 'BUSINESS GROUP INFORMATION';
197 --
198 -- Returns the start date of the fiscal quarter.
199 --
200 cursor csr_fiscal_quarter
201 (
202 p_assignment_id number
203 ,p_upload_date date
204 ) is
205 select nvl(add_months(trunc(add_months(p_upload_date, -
206 to_char(fnd_date.canonical_to_date(HOI.org_information11),'MM') + 1) -
207 to_char(fnd_date.canonical_to_date(HOI.org_information11),'DD') + 1, 'Q'),
208 to_char(fnd_date.canonical_to_date(HOI.org_informatioN11),'MM') - 1) +
209 to_char(fnd_date.canonical_to_date(HOI.org_information11),'DD') - 1,
210 END_OF_TIME)
211 from per_assignments_f ASS
212 ,hr_organization_information HOI
213 where ASS.assignment_id = p_assignment_id
214 and p_upload_date between ASS.effective_start_date
215 and ASS.effective_end_date
216 and HOI.organization_id = ASS.business_group_id
217 and upper(HOI.org_information_context) = 'BUSINESS GROUP INFORMATION';
218 --
219 -- Returns the start date of the current period on the upload date.
220 --
221 cursor csr_period_start
222 (
223 p_assignment_id number
224 ,p_upload_date date
225 ) is
226 select nvl(PTP.start_date, END_OF_TIME)
227 from per_time_periods PTP
228 ,per_assignments_f ASS
229 where ASS.assignment_id = p_assignment_id
230 and p_upload_date between ASS.effective_start_date
231 and ASS.effective_end_date
232 and PTP.payroll_id = ASS.payroll_id
233 and p_upload_date between PTP.start_date
234 and PTP.end_date;
235 --
236 -- Returns the earliest assignment start date relative to a date where the
237 -- assignment belongs to a specific tax unit.
238 --
239 cursor csr_assignment_on_tax_unit
240 (
241 p_assignment_id number
242 ,p_upload_date date
243 ,p_expiry_date date
244 ,p_tax_unit_id number
245 ) is
246 select nvl(greatest(p_expiry_date, min(ASS.effective_start_date),
247 min(PTP.start_date)),
248 END_OF_TIME)
249 from per_assignments_f ASS
250 ,hr_soft_coding_Keyflex SCL
251 ,per_time_periods PTP
252 where ASS.assignment_id = p_assignment_id
253 and ASS.effective_start_date <= p_upload_date
254 and ASS.effective_end_date >= p_expiry_date
255 and SCL.soft_coding_keyflex_id = ASS.soft_coding_keyflex_id
256 and SCL.segment1 = to_char(p_tax_unit_id)
257 and PTP.payroll_id = ASS.payroll_id
258 and PTP.start_date <= p_upload_date
259 and ASS.effective_end_date >= ptp.start_date;
260
261 /*commented out to fix bug#4505420, added above last condition
262 and ASS.effective_start_date between PTP.start_date and
263 p_upload_date; */
264
265 /*and PTP.start_date between
266 ASS.effective_start_date and p_upload_date; Bug#3751001 */
267 --
268 -- Returns the earliest date on which the assignment exists.
269 -- Must also have an active payroll and an existing time
270 -- period at this date.
271 -- If the time period doesn't exist, the initialization will
272 --
273 cursor csr_ele_itd_start
274 (
275 p_assignment_id number
276 ,p_upload_date date
277 ) is
278 select nvl(greatest (min(ASG.effective_start_date), min(PTP.start_date)),
279 END_OF_TIME)
280 from per_assignments_f ASG
281 ,per_time_periods PTP
282 where ASG.assignment_id = p_assignment_id
283 and ASG.effective_start_date <= p_upload_date
284 and PTP.payroll_id = ASG.payroll_id
285 and PTP.start_date <= p_upload_date
286 and ASG.effective_end_date >= ptp.start_date;
287
288 /*commented out to fix bug#4505420, added above last condition
289 and ASG.effective_start_date between PTP.start_date and
290 p_upload_date; */
291
292 /*and PTP.start_date between
293 ASG.effective_start_date and p_upload_date; Bug#3751001 */
294 --
295 -- Returns the earliest date on which the assignment exists and the
296 -- assignment belongs to a specific legal company ie. matches the
297 -- TAX_UNIT_ID context.
298 -- fail when it calls the balance adjustment code.
299 --
300 cursor csr_ele_itd_tax_unit_start
301 (
302 p_assignment_id number
303 ,p_upload_date date
304 ,p_tax_unit_id number
305 ) is
306 select nvl(greatest(min(ASS.effective_start_date), min(PTP.start_date)),
307 END_OF_TIME)
308 from per_assignments_f ASS
309 ,hr_soft_coding_keyflex SCL
310 ,per_time_periods PTP
311 where ASS.assignment_id = p_assignment_id
312 and SCL.soft_coding_keyflex_id = ASS.soft_coding_keyflex_id
313 and ASS.effective_start_date <= p_upload_date
314 and SCL.segment1 = to_char(p_tax_unit_id)
315 and PTP.payroll_id = ASS.payroll_id
316 and PTP.start_date <= p_upload_date
317 and ASS.effective_end_date >= ptp.start_date;
318
319 /*commented out to fix bug#4505420, added above last condition
320 and ASS.effective_start_date between PTP.start_date
321 and p_upload_date; */
322
323 /* and PTP.start_date between
324 ASS.effective_start_date and p_upload_date; Bug#3751001 */
325 --
326 cursor csr_asg_start_date
327 (p_assignment_id number
328 ,p_upload_date date
329 ,p_expiry_date date
330 ) is
331 select nvl(greatest(min(ASS.effective_start_date),
332 min(PTP.start_date), p_expiry_date),
333 END_OF_TIME)
334 from per_assignments_f ASS
335 ,per_time_periods PTP
336 where ASS.assignment_id = p_assignment_id
337 and ASS.effective_start_date <= p_upload_date
338 and ASS.effective_end_date >= p_expiry_date
339 and PTP.payroll_id = ASS.payroll_id
340 and PTP.start_date <= p_upload_date
341 and ASS.effective_end_date >= ptp.start_date;
342
346
343 /*commented out to fix bug#4505420, added above last condition
344 and ASS.effective_start_date between PTP.start_date and
345 p_upload_date; */
347 /* and PTP.start_date between
348 ASS.effective_start_date and p_upload_date; Bug#3751001 */
349
350 -- Cursor to get the Business Group ID
351 cursor csr_business_grp_id
352 (p_assignment_id number) is
353 select distinct
354 paf.business_group_id
355 from per_assignments_f paf
356 where paf.assignment_id = p_assignment_id;
357
358 -- Cursor to get the Time Definition Start Date
359 cursor c_td_start_date(p_time_definition_id number
360 ,p_upload_date date) is
361 select ptp.start_date
362 from per_time_periods ptp
363 where ptp.time_definition_id = p_time_definition_id
364 and p_upload_date between ptp.start_date
365 and ptp.end_date
366 and ptp.time_definition_id is not null
367 and ptp.payroll_id is null;
368
369
370 --
371 --
372 -- Holds the start of the tax year for the upload date.
373 --
374 l_tax_yr_start_date date;
375 --
376 -- Holds the start of the tax quarter for the upload date.
377 --
378 l_tax_qtr_start_date date;
379 --
380 -- Holds the start of the fiscal year for the upload date.
381 --
382 l_fiscal_yr_start_date date;
383 --
384 -- Holds the start of the fiscal quarter for the upload date.
385 --
386 l_fiscal_qtr_start_date date;
387 --
388 -- Holds the start of the period for the upload date.
389 --
390 l_prd_start_date date;
391 --
392 -- Holds the earliest assignment start date relative to a date where the
393 -- assignment belongs to a specific tax unit.
394 --
395 l_closest_tax_unit_date date;
396 --
397 -- Holds the earliest date on which the element entry exists.
398 --
399 l_ele_itd_start_date date;
400 --
401 -- Holds the earliest date on which the element entry exists and the
402 -- assignment belongs to a specific legal company.
403 --
404 l_ele_itd_tax_unit_start_date date;
405 --
406 -- Holds the expiry date of the dimension.
407 --
408 l_expiry_date date;
409 --
410 l_tax_unit_id number;
411
412 -- Holds the Business Group ID
413 l_business_group_id number;
414 l_time_definition_id number;
415
416 -- Holds the TIme Definition Start Date
417 l_td_start_date date;
418
419 begin
420 --
421 -- Get the tax unit.
422 --
423 l_tax_unit_id := p_tax_unit_id;
424 --
425 --
426 -- Get the current batch info
427 --
428 g_batch_info := pay_balance_upload.get_batch_info;
429 --
430 -- Calculate the expiry date for the specified dimension relative to the
431 -- upload date, taking into account any contexts where appropriate. Each of
432 -- the calculations also takes into account when the assignment is on a
433 -- payroll to ensure that a balance adjustment could be made at that point
434 -- if it were required.
435 --
436 -- Inception to date dimension.
437 --
438 if p_dimension_name in
439 ('ASSIGNMENT INCEPTION TO DATE', 'ASSIGNMENT LIFETIME TO DATE',
440 'ASSIGNMENT IN JD LIFETIME TO DATE') then
441 --
442 -- What is the earliest date on which the element entry exists ?
443 --
444 open csr_ele_itd_start(p_assignment_id
445 ,p_upload_date);
446 fetch csr_ele_itd_start into l_ele_itd_start_date;
447 close csr_ele_itd_start;
448 --
449 l_expiry_date := l_ele_itd_start_date;
450 --
451 -- Inception to date within a tax unit dimension.
452 --
453 elsif p_dimension_name in
454 ('ASSIGNMENT WITHIN GOVERNMENT REPORTING ENTITY INCEPTION TO DATE',
455 'ASSIGNMENT WITHIN GOVERNMENT REPORTING ENTITY LIFETIME TO DATE',
456 'ASSIGNMENT IN JD WITHIN GRE LIFETIME TO DATE',
457 'SUBJECT TO TAX FOR ASSIGNMENT WITHIN GRE LIFETIME TO DATE') then
458 --
459 -- What is the earliest date on which the element entry exists and the
460 -- assignment belongs to a specific legal company ??
461 --
462 open csr_ele_itd_tax_unit_start(p_assignment_id
463 ,p_upload_date
464 ,l_tax_unit_id);
465 fetch csr_ele_itd_tax_unit_start into l_ele_itd_tax_unit_start_date;
466 close csr_ele_itd_tax_unit_start;
467 --
468 -- For Purge process if expiry date is EOT then set the expiry date to start of assignment
469 --
470 if g_batch_info.purge_mode and l_ele_itd_tax_unit_start_date = END_OF_TIME then
471 open csr_ele_itd_start(p_assignment_id
472 ,p_upload_date);
473 fetch csr_ele_itd_start into l_ele_itd_tax_unit_start_date;
474 close csr_ele_itd_start;
475 end if;
476 --
477 --
478 l_expiry_date := l_ele_itd_tax_unit_start_date;
479 --
480 -- Period to date dimensions.
481 --
482 elsif p_dimension_name in
486 ,'ASSIGNMENT IN JD WITHIN GRE PERIOD TO DATE'
483 ('ASSIGNMENT PERIOD TO DATE'
484 ,'ASSIGNMENT IN JD PERIOD TO DATE'
485 ,'ASSIGNMENT WITHIN GOVERNMENT REPORTING ENTITY PERIOD TO DATE'
487 ,'SUBJECT TO TAX FOR ASSIGNMENT WITHIN GRE PERIOD TO DATE') then
488 --
489 -- What is the current period start date ?
490 --
491 open csr_period_start(p_assignment_id
492 ,p_upload_date);
493 fetch csr_period_start into l_prd_start_date;
494 close csr_period_start;
495 --
496 -- Ensure that the expiry date is at a date where the assignment is to the
497 -- correct legal company ie. matches the TAX_UNIT_ID context specified.
498 --
499 if p_dimension_name in
500 ('ASSIGNMENT WITHIN GOVERNMENT REPORTING ENTITY PERIOD TO DATE'
501 ,'ASSIGNMENT IN JD WITHIN GRE PERIOD TO DATE'
502 ,'SUBJECT TO TAX FOR ASSIGNMENT WITHIN GRE PERIOD TO DATE') then
503 --
504 open csr_assignment_on_tax_unit(p_assignment_id
505 ,p_upload_date
506 -- ,l_expiry_date
507 ,l_prd_start_date
508 ,l_tax_unit_id);
509 fetch csr_assignment_on_tax_unit into l_closest_tax_unit_date;
510 close csr_assignment_on_tax_unit;
511 --
512 l_expiry_date := l_closest_tax_unit_date;
513 --
514 --
515 -- For Purge process if expiry date is EOT then set the expiry date to start of dimension period
516 --
517 if g_batch_info.purge_mode and l_expiry_date = END_OF_TIME then
518 --
519 l_expiry_date := l_prd_start_date;
520 --
521 end if;
522
523 else
524 open csr_asg_start_date(p_assignment_id
525 ,p_upload_date
526 ,l_prd_start_date);
527 fetch csr_asg_start_date into l_expiry_date;
528 close csr_asg_start_date;
529 end if;
530 --
531 -- Quarter to date dimensions.
532 --
533 elsif p_dimension_name in
534 ('ASSIGNMENT QUARTER TO DATE'
535 ,'ASSIGNMENT IN JD QUARTER TO DATE'
536 ,'ASSIGNMENT WITHIN GOVERNMENT REPORTING ENTITY QUARTER TO DATE'
537 ,'ASSIGNMENT IN JD WITHIN GRE QUARTER TO DATE'
538 ,'SUBJECT TO TAX FOR ASSIGNMENT WITHIN GRE QUARTER TO DATE') then
539 --
540 -- What is the start date of the tax quarter ?
541 --
542 l_tax_qtr_start_date := trunc(p_upload_date, 'Q');
543 open csr_asg_start_date(p_assignment_id
544 ,p_upload_date
545 ,l_tax_qtr_start_date);
546 fetch csr_asg_start_date into l_tax_qtr_start_date;
547 close csr_asg_start_date;
548 --
549 -- Ensure that the expiry date is at a date where the assignment is to the
550 -- correct legal company ie. matches the TAX_UNIT_ID context specified.
551 --
552 if p_dimension_name in
553 ('ASSIGNMENT WITHIN GOVERNMENT REPORTING ENTITY QUARTER TO DATE'
554 ,'ASSIGNMENT IN JD WITHIN GRE QUARTER TO DATE'
555 ,'SUBJECT TO TAX FOR ASSIGNMENT WITHIN GRE QUARTER TO DATE') then
556 --
557 open csr_assignment_on_tax_unit(p_assignment_id
558 ,p_upload_date
559 -- ,l_expiry_date
560 ,l_tax_qtr_start_date
561 ,l_tax_unit_id);
562 fetch csr_assignment_on_tax_unit into l_closest_tax_unit_date;
563 close csr_assignment_on_tax_unit;
564 --
565 l_expiry_date := l_closest_tax_unit_date;
566 --
567 --
568 -- For Purge process if expiry date is EOT then set the expiry date to start of dimension period
569 --
570 if g_batch_info.purge_mode and l_expiry_date = END_OF_TIME then
571 --
572 l_expiry_date := l_tax_qtr_start_date;
573 --
574 end if;
575 --
576 else
577 l_expiry_date := l_tax_qtr_start_date;
578 end if;
579 --
580 -- Year to date dimensions.
581 --
582 elsif p_dimension_name in
583 ('ASSIGNMENT YEAR TO DATE'
584 ,'ASSIGNMENT IN JD YEAR TO DATE'
585 ,'ASSIGNMENT WITHIN GOVERNMENT REPORTING ENTITY YEAR TO DATE'
586 ,'ASSIGNMENT IN JD WITHIN GRE YEAR TO DATE'
587 ,'SUBJECT TO TAX FOR ASSIGNMENT WITHIN GRE YEAR TO DATE') then
588 --
589 -- What is the start date of the tax year ?
590 --
591 l_tax_yr_start_date := trunc(p_upload_date, 'Y');
592 open csr_asg_start_date(p_assignment_id
593 ,p_upload_date
594 ,l_tax_yr_start_date);
595 fetch csr_asg_start_date into l_tax_yr_start_date;
596 close csr_asg_start_date;
597 --
598 -- Ensure that the expiry date is at a date where the assignment is to the
599 -- correct legal company ie. matches the TAX_UNIT_ID context specified.
600 --
601 if p_dimension_name in
602 ('ASSIGNMENT WITHIN GOVERNMENT REPORTING ENTITY YEAR TO DATE'
603 ,'ASSIGNMENT IN JD WITHIN GRE YEAR TO DATE'
604 ,'SUBJECT TO TAX FOR ASSIGNMENT WITHIN GRE YEAR TO DATE') then
605 --
609 ,l_tax_yr_start_date
606 open csr_assignment_on_tax_unit(p_assignment_id
607 ,p_upload_date
608 -- ,l_expiry_date
610 ,l_tax_unit_id);
611 fetch csr_assignment_on_tax_unit into l_closest_tax_unit_date;
612 close csr_assignment_on_tax_unit;
613 --
614 l_expiry_date := l_closest_tax_unit_date;
615 --
616 --
617 -- For Purge process if expiry date is EOT then set the expiry date to start of dimension period
618 --
619 if g_batch_info.purge_mode and l_expiry_date = END_OF_TIME then
620 --
621 l_expiry_date := l_tax_yr_start_date;
622 --
623 end if;
624 --
625 else
626 l_expiry_date := l_tax_yr_start_date;
627 end if;
628 --
629 -- Fiscal quarter to date dimensions.
630 --
631 elsif p_dimension_name in
632 ('ASSIGNMENT FISCAL QUARTER TO DATE'
633 ,'ASSIGNMENT IN JD FISCAL QUARTER TO DATE'
634 ,'ASSIGNMENT WITHIN GOVERNMENT REPORTING ENTITY FISCAL QUARTER TO DATE'
635 ,'ASSIGNMENT IN JD WITHIN GRE FISCAL QUARTER TO DATE') then
636 --
637 -- What is the start date of the fiscal quarter ?
638 --
639 open csr_fiscal_quarter(p_assignment_id
640 ,p_upload_date);
641 fetch csr_fiscal_quarter into l_fiscal_qtr_start_date;
642 close csr_fiscal_quarter;
643 --
644 -- Ensure that the expiry date is at a date where the assignment is to the
645 -- correct legal company ie. matches the TAX_UNIT_ID context specified.
646 --
647 if p_dimension_name in
648 ('ASSIGNMENT WITHIN GOVERNMENT REPORTING ENTITY FISCAL QUARTER TO DATE'
649 ,'ASSIGNMENT IN JD WITHIN GRE FISCAL QUARTER TO DATE') then
650 --
651 open csr_assignment_on_tax_unit(p_assignment_id
652 ,p_upload_date
653 ,l_fiscal_qtr_start_date
654 ,l_tax_unit_id);
655 fetch csr_assignment_on_tax_unit into l_closest_tax_unit_date;
656 close csr_assignment_on_tax_unit;
657 --
658 l_expiry_date := l_closest_tax_unit_date;
659 --
660 --
661 -- For Purge process if expiry date is EOT then set the expiry date to start of dimension period
662 --
663 if g_batch_info.purge_mode and l_expiry_date = END_OF_TIME then
664 --
665 l_expiry_date := l_fiscal_qtr_start_date;
666 --
667 end if;
668 --
669 else
670 open csr_asg_start_date(p_assignment_id
671 ,p_upload_date
672 ,l_fiscal_qtr_start_date);
673 fetch csr_asg_start_date into l_expiry_date;
674 close csr_asg_start_date;
675 end if;
676 --
677 -- Fiscal year to date dimensions.
678 --
679 elsif p_dimension_name in
680 ('ASSIGNMENT FISCAL YEAR TO DATE'
681 ,'ASSIGNMENT IN JD FISCAL YEAR TO DATE'
682 ,'ASSIGNMENT WITHIN GOVERNMENT REPORTING ENTITY FISCAL YEAR TO DATE'
683 ,'ASSIGNMENT IN JD WITHIN GRE FISCAL YEAR TO DATE') then
684 --
685 -- What is the start date of the fiscal year ?
686 --
687 open csr_fiscal_year(p_assignment_id
688 ,p_upload_date);
689 fetch csr_fiscal_year into l_fiscal_yr_start_date;
690 close csr_fiscal_year;
691 --
692 -- Ensure that the expiry date is at a date where the assignment is to the
693 -- correct legal company ie. matches the TAX_UNIT_ID context specified.
694 --
695 if p_dimension_name in
696 ('ASSIGNMENT WITHIN GOVERNMENT REPORTING ENTITY FISCAL YEAR TO DATE'
697 ,'ASSIGNMENT IN JD WITHIN GRE FISCAL YEAR TO DATE') then
698 --
699 open csr_assignment_on_tax_unit(p_assignment_id
700 ,p_upload_date
701 ,l_fiscal_yr_start_date
702 ,l_tax_unit_id);
703 fetch csr_assignment_on_tax_unit into l_closest_tax_unit_date;
704 close csr_assignment_on_tax_unit;
705 --
706 l_expiry_date := l_closest_tax_unit_date;
707 --
708 --
709 -- For Purge process if expiry date is EOT then set the expiry date to start of dimension period
710 --
711 if g_batch_info.purge_mode and l_expiry_date = END_OF_TIME then
712 --
713 l_expiry_date := l_fiscal_yr_start_date;
714 --
715 end if;
716 --
717 else
718 open csr_asg_start_date(p_assignment_id
719 ,p_upload_date
720 ,l_fiscal_yr_start_date);
721 fetch csr_asg_start_date into l_expiry_date;
722 close csr_asg_start_date;
723 end if;
724 --
725 -- Time Definition Period To Date Dimension
726 elsif p_dimension_name in
727 ('ASSIGNMENT WITHIN GRE TIME DEFINITION PERIOD TO DATE') then
728
729 open csr_business_grp_id(p_assignment_id);
730 fetch csr_business_grp_id into l_business_group_id;
731 close csr_business_grp_id;
732
733 l_time_definition_id :=
734 pay_us_rules.get_time_def_for_entry_func(
738 ,p_business_group_id => l_business_group_id
735 p_element_entry_id => null
736 ,p_assignment_id => p_assignment_id
737 ,p_assignment_action_id => null
739 ,p_time_def_date => p_upload_date);
740
741 open c_td_start_date(l_time_definition_id
742 ,p_upload_date);
743 fetch c_td_start_date into l_td_start_date;
744 close c_td_start_date;
745
746 l_expiry_date := l_td_start_date;
747 end if;
748 --
749 -- return the date on which the dimension expires.
750 --
751 return (l_expiry_date);
752 --
753 end expiry_date;
754 --
755 -----------------------------------------------------------------------------
756 -- NAME
757 -- is_supported
758 -- PURPOSE
759 -- Checks if the dimension is supported by the upload process.
760 -- ARGUMENTS
761 -- p_dimension_name - the balance dimension to be checked.
762 -- USES
763 -- NOTES
764 -- Only a subset of the US dimensions are supported and these have been
765 -- picked to allow effective migration to release 10.
766 -- This is used by pay_balance_upload.validate_dimension.
767 -----------------------------------------------------------------------------
768 --
769 function is_supported
770 (
771 p_dimension_name varchar2
772 ) return boolean is
773 begin
774 --
775 hr_utility.trace('Entering pay_us_bal_upload.is_supported');
776 --
777 -- See if the dimension is supported.
778 --
779 if p_dimension_name in
780 ('ASSIGNMENT WITHIN GOVERNMENT REPORTING ENTITY INCEPTION TO DATE'
781 ,'ASSIGNMENT WITHIN GOVERNMENT REPORTING ENTITY LIFETIME TO DATE'
782 ,'ASSIGNMENT WITHIN GOVERNMENT REPORTING ENTITY PERIOD TO DATE'
783 ,'ASSIGNMENT WITHIN GOVERNMENT REPORTING ENTITY QUARTER TO DATE'
784 ,'ASSIGNMENT WITHIN GOVERNMENT REPORTING ENTITY YEAR TO DATE'
785 ,'ASSIGNMENT WITHIN GOVERNMENT REPORTING ENTITY FISCAL QUARTER TO DATE'
786 ,'ASSIGNMENT WITHIN GOVERNMENT REPORTING ENTITY FISCAL YEAR TO DATE'
787 ,'ASSIGNMENT INCEPTION TO DATE'
788 ,'ASSIGNMENT LIFETIME TO DATE'
789 ,'ASSIGNMENT PERIOD TO DATE'
790 ,'ASSIGNMENT QUARTER TO DATE'
791 ,'ASSIGNMENT YEAR TO DATE'
792 ,'ASSIGNMENT IN JD LIFETIME TO DATE'
793 ,'ASSIGNMENT FISCAL QUARTER TO DATE'
794 ,'ASSIGNMENT FISCAL YEAR TO DATE'
795 ,'ASSIGNMENT IN JD PERIOD TO DATE'
796 ,'ASSIGNMENT IN JD QUARTER TO DATE'
797 ,'ASSIGNMENT IN JD YEAR TO DATE'
798 ,'ASSIGNMENT IN JD FISCAL QUARTER TO DATE'
799 ,'ASSIGNMENT IN JD FISCAL YEAR TO DATE'
800 ,'ASSIGNMENT IN JD WITHIN GRE PERIOD TO DATE'
801 ,'ASSIGNMENT IN JD WITHIN GRE QUARTER TO DATE'
802 ,'ASSIGNMENT IN JD WITHIN GRE YEAR TO DATE'
803 ,'ASSIGNMENT IN JD WITHIN GRE FISCAL QUARTER TO DATE'
804 ,'ASSIGNMENT IN JD WITHIN GRE FISCAL YEAR TO DATE'
805 ,'ASSIGNMENT IN JD WITHIN GRE LIFETIME TO DATE'
806 ,'SUBJECT TO TAX FOR ASSIGNMENT WITHIN GRE PERIOD TO DATE'
807 ,'SUBJECT TO TAX FOR ASSIGNMENT WITHIN GRE QUARTER TO DATE'
808 ,'SUBJECT TO TAX FOR ASSIGNMENT WITHIN GRE YEAR TO DATE'
809 ,'SUBJECT TO TAX FOR ASSIGNMENT WITHIN GRE LIFETIME TO DATE'
810 ,'ASSIGNMENT WITHIN GRE TIME DEFINITION PERIOD TO DATE') then
811 return (TRUE);
812 else
813 return (FALSE);
814 end if;
815 --
816 hr_utility.trace('Exiting pay_us_bal_upload.is_supported');
817 --
818 end is_supported;
819 --
820 -----------------------------------------------------------------------------
821 -- NAME
822 -- include_adjustment
823 -- PURPOSE
824 -- Given a dimension, and relevant contexts and details of an existing
825 -- balanmce adjustment, it will find out if the balance adjustment effects
826 -- the dimension to be set. Both the dimension to be set and the adjustment
827 -- are for the same assignment and balance. The adjustment also lies between
828 -- the expiry date of the new balance and the date on which it is to set.
829 -- ARGUMENTS
830 -- p_balance_type_id - the balance to be set.
831 -- p_dimension_name - the balance dimension to be set.
832 -- p_tax_unit_id - TAX_UNIT_ID context.
833 -- p_jurisdiction_code - JURISDICTION_CODE context.
834 -- p_original_entry_id - ORIGINAL_ENTRY_ID context.
835 -- p_bal_adjustment_rec - details of an existing balance adjustment.
836 -- USES
837 -- NOTES
838 -- All the US dimensions affect each other when they share the same context
839 -- values so there is no special support required for individual dimensions.
840 -- This is used by pay_balance_upload.get_current_value.
841 -----------------------------------------------------------------------------
842 --
843 function include_adjustment
844 (
845 p_balance_type_id number
846 ,p_dimension_name varchar2
847 ,p_jurisdiction_code varchar2
848 ,p_original_entry_id number
849 ,p_tax_unit_id number
850 ,p_assignment_id number
851 ,p_upload_date date
852 ,p_bal_adjustment_rec pay_balance_upload.csr_balance_adjustment%rowtype
853 ) return boolean is
854 --
855 -- Does the balance adjustment effect the new balance dimension.
859 p_balance_type_id number
856 --
857 cursor csr_is_included
858 (
860 ,p_tax_unit_id number
861 ,p_jurisdiction_code varchar2
862 ,p_original_entry_id number
863 ,p_bal_adj_tax_unit_id number
864 ,p_bal_adj_jurisdiction_code varchar2
865 ,p_bal_adj_original_entry_id number
866 ) is
867 select BT.balance_type_id
868 from pay_balance_types BT
869 where BT.balance_type_id = p_balance_type_id
870 --
871 -- JURISDICTION_CODE context NB. if the jurisdiction code is
872 -- used then only those adjustments which are for the same
873 -- jurisdiction code can be included.
874 --
875 and ((p_jurisdiction_code is null) or
876 (p_jurisdiction_code is not null and
877 substr(p_bal_adj_jurisdiction_code, 1, BT.jurisdiction_level) =
878 substr(p_jurisdiction_code , 1, BT.jurisdiction_level)))
879 --
880 -- TAX_UNIT_ID context NB. if the tax unit is used then only those
881 -- adjustments which are for the same tax unit can be included.
882 --
883 and nvl(p_tax_unit_id, nvl(p_bal_adj_tax_unit_id, -1)) =
884 nvl(p_bal_adj_tax_unit_id, -1)
885 --
886 -- ORIGINAL_ENTRY_ID context NB. this context controls the expiry
887 -- date of the dimension in the same way as the QTD dimension. Any
888 -- existing balance adjustments that lie between the upload date
889 -- and the expiry date are all included. There is no special
890 -- criteria that has to be met.
891 --
892 and 1 = 1;
893 --
894 -- The balance returned by the include check.
895 --
896 l_bal_type_id number;
897 --
898 l_tax_unit_id number;
899 --
900 begin
901 --
902 hr_utility.trace('Entering pay_us_bal_upload.include_adjustment');
903 --
904 l_tax_unit_id := p_tax_unit_id;
905 --
906 -- Does the balance adjustment effect the new balance ?
907 --
908 open csr_is_included(p_balance_type_id
909 ,l_tax_unit_id
910 ,p_jurisdiction_code
911 ,p_original_entry_id
912 ,p_bal_adjustment_rec.tax_unit_id
913 ,p_bal_adjustment_rec.jurisdiction_code
914 ,p_bal_adjustment_rec.original_entry_id);
915 fetch csr_is_included into l_bal_type_id;
916 close csr_is_included;
917 --
918 hr_utility.trace('Exiting pay_us_bal_upload.include_adjustment');
919 --
920 -- Adjustment does contribute to the new balance.
921 --
922 if l_bal_type_id is not null then
923 return (TRUE);
924 --
925 -- Adjustment does not contribute to the new balance.
926 --
927 else
928 return (FALSE);
929 end if;
930 --
931 end include_adjustment;
932 --
933 -----------------------------------------------------------------------------
934 -- NAME
935 -- validate_batch_lines
936 -- PURPOSE
937 -- Applies US specific validation to the batch.
938 -- ARGUMENTS
939 -- p_batch_id - the batch to be validate_batch_linesd.
940 -- USES
941 -- NOTES
942 -- This is used by pay_balance_upload.validate_batch_lines.
943 -----------------------------------------------------------------------------
944 --
948 ) is
945 procedure validate_batch_lines
946 (
947 p_batch_id number
949 begin
950 --
951 hr_utility.trace('Entering pay_us_bal_upload.validate_batch_lines');
952 --
953 hr_utility.trace('Exiting pay_us_bal_upload.validate_batch_lines');
954 --
955 end validate_batch_lines;
956 --
957 end pay_us_bal_upload;
[Home] [Help]