1 package body pay_bf_bal_upload as
2 /* $Header: pybfupld.pkb 120.0 2005/05/29 03:18 appldev noship $ */
3 /*
4 Copyright (c) Oracle Corporation 1995 All rights reserved
5 PRODUCT
6 Oracle*Payroll
7 NAME
8 pybfupld.pkb
9 DESCRIPTION
10 Provides support for the upload of balances based on BF 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.5 T.Habara 18-May-2005 Element ptd and itd support. Modified
20 is_supported and expiry_date.
21 115.4 T.Habara 10-May-2004 Added ASSIGNMENT GRE ST2 SN PERIOD TO
22 DATE to expiry_date and is_supported.
23 115.3 T.Habara 18-Sep-2003 Modified expiry_date and is_supported
24 to support extra dimensions.
25 Added p_source_id and p_source_text
26 params to include_adjustment.
27 115.2 A.Logue 07-Oct-1999 Change to_number(segment1) to
28 to_char(tax_unit_id) to avoid
29 to_number errors.
30 115.1 A.Logue 14-May-1999 Canoncial Date in org_information11.
31 40.8 J.Alloun 30-Jul-1996 Added error handling.
32 40.7 A.Wong 16-May-1996 uncomment exit command at the end.
33 40.6 N.Bristow 08-May-1996 Bug 359005. Tax Unit Id is now passed
34 to expiry_date and include_adjustment.
35 40.5 S Desai 27-Feb-1996 Bug 333439: Date format was 'DD-MON-YY'.
36 40.4 N.Bristow 13-Dec-1995 Fixed #328322. Expiry date not set
37 correctly for assignments created
38 in the upload year.
39 40.3 N.Bristow 03-Nov-1995 The cursors retrieving the date of an
40 itd adjustment were incorrect.
41 40.2 N.Bristow 23-Oct-1995 created.
42 */
43 --
47 END_OF_TIME constant date := to_date('31/12/4712','DD/MM/YYYY');
44 -- Date constants.
45 --
46 START_OF_TIME constant date := to_date('01/01/0001','DD/MM/YYYY');
48 --
49 -----------------------------------------------------------------------------
50 -- NAME
51 -- get_tax_unit
52 -- PURPOSE
53 -- Returns the legal company an assignment is associated with at
54 -- particular point in time.
55 -- ARGUMENTS
56 -- p_assignment_id - the assignment
57 -- p_effective_date - the date on which the information is required.
58 -- USES
59 -- NOTES
60 -----------------------------------------------------------------------------
61 --
62 function get_tax_unit
63 (
64 p_assignment_id number
65 ,p_effective_date date
66 ) return number is
67 --
68 -- Retrieves the legal company an assignment belongs to at a given date.
69 --
70 cursor csr_tax_unit
71 (
72 p_assignment_id number
73 ,p_effective_date date
74 ) is
75 select to_number(SCL.segment1) tax_unit_id
76 from per_assignments_f ASG
77 ,hr_soft_coding_keyflex SCL
78 where ASG.assignment_id = p_assignment_id
79 and SCL.soft_coding_keyflex_id = ASG.soft_coding_keyflex_id
80 and p_effective_date between ASG.effective_start_date
81 and ASG.effective_end_date;
82 --
83 -- Holds the tax unit an assignment belongs to.
84 --
85 l_tax_unit_id number;
86 --
87 begin
88 --
89 hr_utility.trace('Entering pay_bf_bal_upload.get_tax_unit');
90 --
91 -- Get the legal company the assignment belongs to.
92 --
93 open csr_tax_unit(p_assignment_id
94 ,p_effective_date);
95 fetch csr_tax_unit into l_tax_unit_id;
96 close csr_tax_unit;
97 --
98 -- Return the tax unit.
99 --
100 return (l_tax_unit_id);
101 --
102 hr_utility.trace('Exiting pay_bf_bal_upload.get_tax_unit');
103 --
104 end get_tax_unit;
105 --
106 -----------------------------------------------------------------------------
107 -- NAME
108 -- expiry_date
109 -- PURPOSE
110 -- Returns the expiry date of a given dimension relative to a date.
111 -- ARGUMENTS
112 -- p_upload_date - the date on which the balance should be correct.
113 -- p_dimension_name - the dimension being set.
114 -- p_assignment_id - the assignment involved.
115 -- p_original_entry_id - ORIGINAL_ENTRY_ID context.
116 -- USES
117 -- NOTES
118 -- This is used by pay_balance_upload.dim_expiry_date.
119 -- If the expiry date cannot be derived then it is set to the end of time
120 -- to indicate that a failure has occured. The process that uses the
121 -- expiry date knows this rulw and acts accordingly.
122 -----------------------------------------------------------------------------
123 --
124 function expiry_date
125 (
126 p_upload_date date
127 ,p_dimension_name varchar2
128 ,p_assignment_id number
129 ,p_tax_unit_id number
130 ,p_jurisdiction_code varchar2
131 ,p_original_entry_id number
132 ) return date is
133 --
134 -- Returns the start date of the fiscal year.
135 --
136 cursor csr_fiscal_year
137 (
138 p_assignment_id number
139 ,p_upload_date date
140 ) is
141 select nvl(trunc(p_upload_date -
142 to_char(fnd_date.canonical_to_date(HOI.org_information11),'DDD') +1,'Y')
143 - 1 + to_char(fnd_date.canonical_to_date(HOI.org_information11),'DDD'),
144 END_OF_TIME)
145 from per_assignments_f ASS
146 ,hr_organization_information HOI
147 where ASS.assignment_id = p_assignment_id
148 and p_upload_date between ASS.effective_start_date
149 and ASS.effective_end_date
150 and HOI.organization_id = ASS.business_group_id
151 and upper(HOI.org_information_context) = 'BUSINESS GROUP INFORMATION';
152 --
153 -- Returns the start date of the fiscal quarter.
154 --
155 cursor csr_fiscal_quarter
156 (
157 p_assignment_id number
158 ,p_upload_date date
159 ) is
160 select nvl(add_months(trunc(add_months(p_upload_date, -
161 to_char(fnd_date.canonical_to_date(HOI.org_information11),'MM') + 1) -
162 to_char(fnd_date.canonical_to_date(HOI.org_information11),'DD') + 1, 'Q'),
163 to_char(fnd_date.canonical_to_date(HOI.org_informatioN11),'MM') - 1) +
164 to_char(fnd_date.canonical_to_date(HOI.org_information11),'DD') - 1,
165 END_OF_TIME)
166 from per_assignments_f ASS
167 ,hr_organization_information HOI
168 where ASS.assignment_id = p_assignment_id
169 and p_upload_date between ASS.effective_start_date
170 and ASS.effective_end_date
171 and HOI.organization_id = ASS.business_group_id
172 and upper(HOI.org_information_context) = 'BUSINESS GROUP INFORMATION';
173 --
174 -- Returns the start date of the current period on the upload date.
175 --
176 cursor csr_period_start
177 (
178 p_assignment_id number
179 ,p_upload_date date
180 ) is
181 select nvl(PTP.start_date, END_OF_TIME)
185 and p_upload_date between ASS.effective_start_date
182 from per_time_periods PTP
183 ,per_assignments_f ASS
184 where ASS.assignment_id = p_assignment_id
186 and ASS.effective_end_date
187 and PTP.payroll_id = ASS.payroll_id
188 and p_upload_date between PTP.start_date
189 and PTP.end_date;
190 --
191 -- Returns the earliest assignment start date relative to a date where the
192 -- assignment belongs to a specific tax unit.
193 --
194 cursor csr_assignment_on_tax_unit
195 (
196 p_assignment_id number
197 ,p_upload_date date
198 ,p_expiry_date date
199 ,p_tax_unit_id number
200 ) is
201 select nvl(greatest(p_expiry_date, min(ASS.effective_start_date)),
202 END_OF_TIME)
203 from per_assignments_f ASS
204 ,hr_soft_coding_Keyflex SCL
205 where ASS.assignment_id = p_assignment_id
206 and ASS.effective_start_date <= p_upload_date
207 and ASS.effective_end_date >= p_expiry_date
208 and SCL.soft_coding_keyflex_id = ASS.soft_coding_keyflex_id
209 and SCL.segment1 = to_char(p_tax_unit_id);
210 --
211 -- Returns the earliest date on which the assignment exists.
212 --
213 cursor csr_ele_itd_start
214 (
215 p_assignment_id number
216 ,p_upload_date date
217 ) is
218 select nvl(min(ASG.effective_start_date), END_OF_TIME)
219 from per_assignments_f ASG
220 where ASG.assignment_id = p_assignment_id
221 and ASG.effective_start_date <= p_upload_date;
222 --
223 -- Returns the earliest date on which the assignment exists and the
224 -- assignment belongs to a specific legal company ie. matches the
225 -- TAX_UNIT_ID context.
226 --
227 cursor csr_ele_itd_tax_unit_start
228 (
229 p_assignment_id number
230 ,p_upload_date date
231 ,p_tax_unit_id number
232 ) is
233 select nvl(min(ASS.effective_start_date),
234 END_OF_TIME)
235 from per_assignments_f ASS
236 ,hr_soft_coding_keyflex SCL
237 where ASS.assignment_id = p_assignment_id
238 and SCL.soft_coding_keyflex_id = ASS.soft_coding_keyflex_id
239 and ASS.effective_start_date <= p_upload_date
240 and SCL.segment1 = to_char(p_tax_unit_id);
241 --
242 cursor csr_asg_start_date
243 (p_assignment_id number
244 ,p_upload_date date
245 ,p_expiry_date date
246 ) is
247 select nvl(greatest(min(ASS.effective_start_date), p_expiry_date),
248 END_OF_TIME)
249 from per_assignments_f ASS
250 where ASS.assignment_id = p_assignment_id
251 and ASS.effective_start_date <= p_upload_date
252 and ASS.effective_end_date >= p_expiry_date;
253 --
254 cursor csr_oee_start_date
255 (p_original_entry_id number
256 ,p_upload_date date
257 ) is
258 select min(pee.effective_start_date)
259 from pay_element_entries_f pee
260 where (pee.element_entry_id = p_original_entry_id
261 or pee.original_entry_id = p_original_entry_id)
262 and pee.assignment_id = p_assignment_id
263 and pee.entry_type = 'E'
264 and pee.effective_start_date <= p_upload_date;
265 --
266 -- Holds the start of the tax year for the upload date.
267 --
268 l_tax_yr_start_date date;
269 --
270 -- Holds the start of the tax quarter for the upload date.
271 --
272 l_tax_qtr_start_date date;
273 --
274 -- Holds the start of the fiscal year for the upload date.
275 --
276 l_fiscal_yr_start_date date;
277 --
278 -- Holds the start of the fiscal quarter for the upload date.
279 --
280 l_fiscal_qtr_start_date date;
281 --
282 -- Holds the start of the period for the upload date.
283 --
284 l_prd_start_date date;
285 --
286 -- Holds the earliest assignment start date relative to a date where the
287 -- assignment belongs to a specific tax unit.
288 --
289 l_closest_tax_unit_date date;
290 --
291 -- Holds the earliest date on which the element entry exists.
292 --
293 l_ele_itd_start_date date;
294 --
295 -- Holds the earliest date on which the element entry exists and the
296 -- assignment belongs to a specific legal company.
297 --
298 l_ele_itd_tax_unit_start_date date;
299 --
300 -- Holds the expiry date of the dimension.
301 --
302 l_expiry_date date;
303 --
304 -- Holds the start date of the original entry.
305 --
306 l_oee_start_date date;
307 --
308 --
309 l_tax_unit_id number;
310 l_bus_grp number;
311 begin
312 --
313 -- Get the tax unit.
314 --
315 l_tax_unit_id := p_tax_unit_id;
316 --
317 -- Calculate the expiry date for the specified dimension relative to the
318 -- upload date, taking into account any contexts where appropriate. Each of
319 -- the calculations also takes into account when the assignment is on a
323 -- Inception to date dimension.
320 -- payroll to ensure that a balance adjustment could be made at that point
321 -- if it were required.
322 --
324 --
325 if p_dimension_name in
326 ('ASSIGNMENT INCEPTION TO DATE', 'ELEMENT INCEPTION TO DATE') then
327 --
328 -- What is the earliest date on which the element entry exists ?
329 --
330 open csr_ele_itd_start(p_assignment_id
331 ,p_upload_date);
332 fetch csr_ele_itd_start into l_ele_itd_start_date;
333 close csr_ele_itd_start;
334 --
335 l_expiry_date := l_ele_itd_start_date;
336 --
337 -- Inception to date within a tax unit dimension.
338 --
339 elsif p_dimension_name =
340 'ASSIGNMENT WITHIN GOVERNMENT REPORTING ENTITY INCEPTION TO DATE' then
341 --
342 -- What is the earliest date on which the element entry exists and the
343 -- assignment belongs to a specific legal company ??
344 --
345 open csr_ele_itd_tax_unit_start(p_assignment_id
346 ,p_upload_date
347 ,l_tax_unit_id);
348 fetch csr_ele_itd_tax_unit_start into l_ele_itd_tax_unit_start_date;
349 close csr_ele_itd_tax_unit_start;
350 --
351 l_expiry_date := l_ele_itd_tax_unit_start_date;
352 --
353 -- Period to date dimensions.
354 --
355 elsif p_dimension_name in
356 ('ASSIGNMENT PERIOD TO DATE'
357 ,'ASSIGNMENT GRE ST2 SN PERIOD TO DATE'
358 ,'ASSIGNMENT SOURCE ID PERIOD TO DATE'
359 ,'ASSIGNMENT SOURCE TEXT PERIOD TO DATE'
360 ,'ASSIGNMENT IN JD PERIOD TO DATE'
361 ,'ASSIGNMENT WITHIN GOVERNMENT REPORTING ENTITY PERIOD TO DATE'
362 ,'ASSIGNMENT IN JD WITHIN GRE PERIOD TO DATE'
363 ,'ELEMENT PERIOD TO DATE'
364 ,'SUBJECT TO TAX FOR ASSIGNMENT WITHIN GRE PERIOD TO DATE') then
365 --
366 -- What is the current period start date ?
367 --
368 open csr_period_start(p_assignment_id
369 ,p_upload_date);
370 fetch csr_period_start into l_prd_start_date;
371 close csr_period_start;
372 --
373 -- Ensure that the expiry date is at a date where the assignment is to the
374 -- correct legal company ie. matches the TAX_UNIT_ID context specified.
375 --
376 if p_dimension_name in
377 ('ASSIGNMENT WITHIN GOVERNMENT REPORTING ENTITY PERIOD TO DATE'
378 ,'ASSIGNMENT IN JD WITHIN GRE PERIOD TO DATE'
379 ,'SUBJECT TO TAX FOR ASSIGNMENT WITHIN GRE PERIOD TO DATE') then
380 --
381 open csr_assignment_on_tax_unit(p_assignment_id
382 ,p_upload_date
383 -- ,l_expiry_date
384 ,l_prd_start_date
385 ,l_tax_unit_id);
386 fetch csr_assignment_on_tax_unit into l_closest_tax_unit_date;
387 close csr_assignment_on_tax_unit;
388 --
389 l_expiry_date := l_closest_tax_unit_date;
390 --
391 else
392 open csr_asg_start_date(p_assignment_id
393 ,p_upload_date
394 ,l_prd_start_date);
395 fetch csr_asg_start_date into l_expiry_date;
396 close csr_asg_start_date;
397 end if;
398 --
399 -- Quarter to date dimensions.
400 --
401 elsif p_dimension_name in
402 ('ASSIGNMENT QUARTER TO DATE'
403 ,'ASSIGNMENT IN JD QUARTER TO DATE'
404 ,'ASSIGNMENT WITHIN GOVERNMENT REPORTING ENTITY QUARTER TO DATE'
405 ,'ASSIGNMENT IN JD WITHIN GRE QUARTER TO DATE'
406 ,'SUBJECT TO TAX FOR ASSIGNMENT WITHIN GRE QUARTER TO DATE') then
407 --
408 -- What is the start date of the tax quarter ?
409 --
410 l_tax_qtr_start_date := trunc(p_upload_date, 'Q');
411 open csr_asg_start_date(p_assignment_id
412 ,p_upload_date
413 ,l_tax_qtr_start_date);
414 fetch csr_asg_start_date into l_tax_qtr_start_date;
415 close csr_asg_start_date;
416 --
417 -- Ensure that the expiry date is at a date where the assignment is to the
418 -- correct legal company ie. matches the TAX_UNIT_ID context specified.
419 --
420 if p_dimension_name in
421 ('ASSIGNMENT WITHIN GOVERNMENT REPORTING ENTITY QUARTER TO DATE'
422 ,'ASSIGNMENT IN JD WITHIN GRE QUARTER TO DATE'
423 ,'SUBJECT TO TAX FOR ASSIGNMENT WITHIN GRE QUARTER TO DATE') then
424 --
425 open csr_assignment_on_tax_unit(p_assignment_id
426 ,p_upload_date
427 -- ,l_expiry_date
428 ,l_tax_qtr_start_date
429 ,l_tax_unit_id);
430 fetch csr_assignment_on_tax_unit into l_closest_tax_unit_date;
431 close csr_assignment_on_tax_unit;
432 --
433 l_expiry_date := l_closest_tax_unit_date;
434 --
435 else
436 l_expiry_date := l_tax_qtr_start_date;
437 end if;
438 --
439 -- Year to date dimensions.
440 --
441 elsif p_dimension_name in
442 ('ASSIGNMENT YEAR TO DATE'
443 ,'ASSIGNMENT IN JD YEAR TO DATE'
444 ,'ASSIGNMENT WITHIN GOVERNMENT REPORTING ENTITY YEAR TO DATE'
445 ,'ASSIGNMENT IN JD WITHIN GRE YEAR TO DATE'
446 ,'SUBJECT TO TAX FOR ASSIGNMENT WITHIN GRE YEAR TO DATE') then
447 --
448 -- What is the start date of the tax year ?
449 --
450 l_tax_yr_start_date := trunc(p_upload_date, 'Y');
451 open csr_asg_start_date(p_assignment_id
455 close csr_asg_start_date;
452 ,p_upload_date
453 ,l_tax_yr_start_date);
454 fetch csr_asg_start_date into l_tax_yr_start_date;
456 --
457 -- Ensure that the expiry date is at a date where the assignment is to the
458 -- correct legal company ie. matches the TAX_UNIT_ID context specified.
459 --
460 if p_dimension_name in
461 ('ASSIGNMENT WITHIN GOVERNMENT REPORTING ENTITY YEAR TO DATE'
462 ,'ASSIGNMENT IN JD WITHIN GRE YEAR TO DATE'
463 ,'SUBJECT TO TAX FOR ASSIGNMENT WITHIN GRE YEAR TO DATE') then
464 --
465 open csr_assignment_on_tax_unit(p_assignment_id
466 ,p_upload_date
467 -- ,l_expiry_date
468 ,l_tax_yr_start_date
469 ,l_tax_unit_id);
470 fetch csr_assignment_on_tax_unit into l_closest_tax_unit_date;
471 close csr_assignment_on_tax_unit;
472 --
473 l_expiry_date := l_closest_tax_unit_date;
474 --
475 else
476 l_expiry_date := l_tax_yr_start_date;
477 end if;
478 --
479 -- Fiscal quarter to date dimensions.
480 --
481 elsif p_dimension_name in
482 ('ASSIGNMENT FISCAL QUARTER TO DATE'
483 ,'ASSIGNMENT IN JD FISCAL QUARTER TO DATE'
484 ,'ASSIGNMENT WITHIN GOVERNMENT REPORTING ENTITY FISCAL QUARTER TO DATE'
485 ,'ASSIGNMENT IN JD WITHIN GRE FISCAL QUARTER TO DATE') then
486 --
487 -- What is the start date of the fiscal quarter ?
488 --
489 open csr_fiscal_quarter(p_assignment_id
490 ,p_upload_date);
491 fetch csr_fiscal_quarter into l_fiscal_qtr_start_date;
492 close csr_fiscal_quarter;
493 --
494 -- Ensure that the expiry date is at a date where the assignment is to the
495 -- correct legal company ie. matches the TAX_UNIT_ID context specified.
496 --
497 if p_dimension_name in
498 ('ASSIGNMENT WITHIN GOVERNMENT REPORTING ENTITY FISCAL QUARTER TO DATE'
499 ,'ASSIGNMENT IN JD WITHIN GRE FISCAL QUARTER TO DATE') then
500 --
501 open csr_assignment_on_tax_unit(p_assignment_id
502 ,p_upload_date
503 ,l_fiscal_qtr_start_date
504 ,l_tax_unit_id);
505 fetch csr_assignment_on_tax_unit into l_closest_tax_unit_date;
506 close csr_assignment_on_tax_unit;
507 --
508 l_expiry_date := l_closest_tax_unit_date;
509 --
510 else
511 open csr_asg_start_date(p_assignment_id
512 ,p_upload_date
513 ,l_fiscal_qtr_start_date);
514 fetch csr_asg_start_date into l_expiry_date;
515 close csr_asg_start_date;
516 end if;
517 --
518 -- Fiscal year to date dimensions.
519 --
520 elsif p_dimension_name in
521 ('ASSIGNMENT FISCAL YEAR TO DATE'
522 ,'ASSIGNMENT IN JD FISCAL YEAR TO DATE'
523 ,'ASSIGNMENT WITHIN GOVERNMENT REPORTING ENTITY FISCAL YEAR TO DATE'
524 ,'ASSIGNMENT IN JD WITHIN GRE FISCAL YEAR TO DATE') then
525 --
526 -- What is the start date of the fiscal year ?
527 --
528 open csr_fiscal_year(p_assignment_id
529 ,p_upload_date);
530 fetch csr_fiscal_year into l_fiscal_yr_start_date;
531 close csr_fiscal_year;
532 --
533 -- Ensure that the expiry date is at a date where the assignment is to the
534 -- correct legal company ie. matches the TAX_UNIT_ID context specified.
535 --
536 if p_dimension_name in
537 ('ASSIGNMENT WITHIN GOVERNMENT REPORTING ENTITY FISCAL YEAR TO DATE'
538 ,'ASSIGNMENT IN JD WITHIN GRE FISCAL YEAR TO DATE') then
539 --
540 open csr_assignment_on_tax_unit(p_assignment_id
541 ,p_upload_date
542 ,l_fiscal_yr_start_date
543 ,l_tax_unit_id);
544 fetch csr_assignment_on_tax_unit into l_closest_tax_unit_date;
545 close csr_assignment_on_tax_unit;
546 --
547 l_expiry_date := l_closest_tax_unit_date;
548 --
549 else
550 open csr_asg_start_date(p_assignment_id
551 ,p_upload_date
552 ,l_fiscal_yr_start_date);
553 fetch csr_asg_start_date into l_expiry_date;
554 close csr_asg_start_date;
555 end if;
556 --
557 end if;
558 --
559 -- Original entry based dimension
560 --
561 if p_dimension_name in
562 ('ELEMENT PERIOD TO DATE', 'ELEMENT INCEPTION TO DATE') then
563 --
564 -- Retrieve the start date of the original entry.
565 --
566 open csr_oee_start_date(p_original_entry_id
567 ,p_upload_date);
568 fetch csr_oee_start_date into l_oee_start_date;
569 close csr_oee_start_date;
570 --
571 l_expiry_date := greatest(l_expiry_date, nvl(l_oee_start_date, END_OF_TIME));
572 --
573 end if;
574 --
575 -- return the date on which the dimension expires.
576 --
577 return (l_expiry_date);
578 --
579 end expiry_date;
580 --
581 -----------------------------------------------------------------------------
582 -- NAME
583 -- is_supported
584 -- PURPOSE
585 -- Checks if the dimension is supported by the upload process.
586 -- ARGUMENTS
590 -- Only a subset of the BF dimensions are supported and these have been
587 -- p_dimension_name - the balance dimension to be checked.
588 -- USES
589 -- NOTES
591 -- picked to allow effective migration to release 10.
592 -- This is used by pay_balance_upload.validate_dimension.
593 -----------------------------------------------------------------------------
594 --
595 function is_supported
596 (
597 p_dimension_name varchar2
598 ) return boolean is
599 begin
600 --
601 hr_utility.trace('Entering pay_bf_bal_upload.is_supported');
602 --
603 -- See if the dimension is supported.
604 --
605 if p_dimension_name in
606 ('ASSIGNMENT WITHIN GOVERNMENT REPORTING ENTITY INCEPTION TO DATE'
607 ,'ASSIGNMENT WITHIN GOVERNMENT REPORTING ENTITY PERIOD TO DATE'
608 ,'ASSIGNMENT WITHIN GOVERNMENT REPORTING ENTITY QUARTER TO DATE'
609 ,'ASSIGNMENT WITHIN GOVERNMENT REPORTING ENTITY YEAR TO DATE'
610 ,'ASSIGNMENT WITHIN GOVERNMENT REPORTING ENTITY FISCAL QUARTER TO DATE'
611 ,'ASSIGNMENT WITHIN GOVERNMENT REPORTING ENTITY FISCAL YEAR TO DATE'
612 ,'ASSIGNMENT INCEPTION TO DATE'
613 ,'ASSIGNMENT PERIOD TO DATE'
614 ,'ASSIGNMENT QUARTER TO DATE'
615 ,'ASSIGNMENT YEAR TO DATE'
616 ,'ASSIGNMENT FISCAL QUARTER TO DATE'
617 ,'ASSIGNMENT FISCAL YEAR TO DATE'
618 ,'ASSIGNMENT IN JD PERIOD TO DATE'
619 ,'ASSIGNMENT IN JD QUARTER TO DATE'
620 ,'ASSIGNMENT IN JD YEAR TO DATE'
621 ,'ASSIGNMENT IN JD FISCAL QUARTER TO DATE'
622 ,'ASSIGNMENT IN JD FISCAL YEAR TO DATE'
623 ,'ASSIGNMENT IN JD WITHIN GRE PERIOD TO DATE'
624 ,'ASSIGNMENT IN JD WITHIN GRE QUARTER TO DATE'
625 ,'ASSIGNMENT IN JD WITHIN GRE YEAR TO DATE'
626 ,'ASSIGNMENT IN JD WITHIN GRE FISCAL QUARTER TO DATE'
627 ,'ASSIGNMENT IN JD WITHIN GRE FISCAL YEAR TO DATE'
628 ,'SUBJECT TO TAX FOR ASSIGNMENT WITHIN GRE PERIOD TO DATE'
629 ,'SUBJECT TO TAX FOR ASSIGNMENT WITHIN GRE QUARTER TO DATE'
630 ,'ASSIGNMENT SOURCE ID PERIOD TO DATE'
631 ,'ASSIGNMENT SOURCE TEXT PERIOD TO DATE'
632 ,'ASSIGNMENT GRE ST2 SN PERIOD TO DATE'
633 ,'ELEMENT PERIOD TO DATE'
634 ,'ELEMENT INCEPTION TO DATE'
635 ,'SUBJECT TO TAX FOR ASSIGNMENT WITHIN GRE YEAR TO DATE') then
636 return (TRUE);
637 else
638 return (FALSE);
639 end if;
640 --
641 hr_utility.trace('Exiting pay_bf_bal_upload.is_supported');
642 --
643 end is_supported;
644 --
645 -----------------------------------------------------------------------------
646 -- NAME
647 -- include_adjustment
648 -- PURPOSE
649 -- Given a dimension, and relevant contexts and details of an existing
650 -- balanmce adjustment, it will find out if the balance adjustment effects
651 -- the dimension to be set. Both the dimension to be set and the adjustment
652 -- are for the same assignment and balance. The adjustment also lies between
653 -- the expiry date of the new balance and the date on which it is to set.
654 -- ARGUMENTS
655 -- p_balance_type_id - the balance to be set.
656 -- p_dimension_name - the balance dimension to be set.
657 -- p_tax_unit_id - TAX_UNIT_ID context.
658 -- p_jurisdiction_code - JURISDICTION_CODE context.
659 -- p_original_entry_id - ORIGINAL_ENTRY_ID context.
660 -- p_source_id - SOURCE_ID context.
661 -- p_source_text - SOURCE_TEXT context.
662 -- p_bal_adjustment_rec - details of an existing balance adjustment.
663 -- USES
664 -- NOTES
665 -- All the BF dimensions affect each other when they share the same context
666 -- values so there is no special support required for individual dimensions.
667 -- This is used by pay_balance_upload.get_current_value.
668 -----------------------------------------------------------------------------
669 --
670 function include_adjustment
671 (
672 p_balance_type_id number
673 ,p_dimension_name varchar2
674 ,p_jurisdiction_code varchar2
675 ,p_original_entry_id number
676 ,p_tax_unit_id number
677 ,p_assignment_id number
678 ,p_upload_date date
679 ,p_source_id number
680 ,p_source_text varchar2
681 ,p_bal_adjustment_rec pay_balance_upload.csr_balance_adjustment%rowtype
682 ) return boolean is
683 --
684 -- Does the balance adjustment effect the new balance dimension.
685 --
686 cursor csr_is_included
687 (
688 p_balance_type_id number
689 ,p_tax_unit_id number
690 ,p_jurisdiction_code varchar2
691 ,p_original_entry_id number
692 ,p_source_id number
693 ,p_source_text varchar2
694 ,p_bal_adj_tax_unit_id number
695 ,p_bal_adj_jurisdiction_code varchar2
696 ,p_bal_adj_original_entry_id number
697 ,p_bal_adj_source_id number
698 ,p_bal_adj_source_text varchar2
699 ) is
700 select BT.balance_type_id
701 from pay_balance_types BT
702 where BT.balance_type_id = p_balance_type_id
703 --
704 -- JURISDICTION_CODE context NB. if the jurisdiction code is
705 -- used then only those adjustments which are for the same
706 -- jurisdiction code can be included.
707 --
708 and ((p_jurisdiction_code is null) or
709 (p_jurisdiction_code is not null and
710 substr(p_bal_adj_jurisdiction_code, 1, BT.jurisdiction_level) =
711 substr(p_jurisdiction_code , 1, BT.jurisdiction_level)))
712 --
713 -- TAX_UNIT_ID context NB. if the tax unit is used then only those
717 nvl(p_bal_adj_tax_unit_id, -1)
714 -- adjustments which are for the same tax unit can be included.
715 --
716 and nvl(p_tax_unit_id, nvl(p_bal_adj_tax_unit_id, -1)) =
718 --
719 -- ORIGINAL_ENTRY_ID context NB. this context controls the expiry
720 -- date of the dimension in the same way as the QTD dimension. Any
721 -- existing balance adjustments that lie between the upload date
722 -- and the expiry date are all included. There is no special
723 -- criteria that has to be met.
724 --
725 -- SOURCE_ID and SOURCE_TEXT contexts.
726 and nvl(p_bal_adj_source_id, -1)
727 = nvl(p_source_id, nvl(p_bal_adj_source_id, -1))
728 and nvl(p_bal_adj_source_text, '~nvl~')
729 = nvl(p_source_text, nvl(p_bal_adj_source_text, '~nvl~'))
730 and 1 = 1;
731 --
732 -- The balance returned by the include check.
733 --
734 l_bal_type_id number;
735 --
736 l_tax_unit_id number;
737 --
738 begin
739 --
740 hr_utility.trace('Entering pay_bf_bal_upload.include_adjustment');
741 --
742 -- Get the tax unit.
743 --
744 l_tax_unit_id := p_tax_unit_id;
745 --
746 -- Does the balance adjustment effect the new balance ?
747 --
748 open csr_is_included(p_balance_type_id
749 ,l_tax_unit_id
750 ,p_jurisdiction_code
751 ,p_original_entry_id
752 ,p_source_id
753 ,p_source_text
754 ,p_bal_adjustment_rec.tax_unit_id
755 ,p_bal_adjustment_rec.jurisdiction_code
756 ,p_bal_adjustment_rec.original_entry_id
757 ,p_bal_adjustment_rec.source_id
758 ,p_bal_adjustment_rec.source_text);
759 fetch csr_is_included into l_bal_type_id;
760 close csr_is_included;
761 --
762 hr_utility.trace('Exiting pay_bf_bal_upload.include_adjustment');
763 --
764 -- Adjustment does contribute to the new balance.
765 --
766 if l_bal_type_id is not null then
767 return (TRUE);
768 --
769 -- Adjustment does not contribute to the new balance.
770 --
771 else
772 return (FALSE);
773 end if;
774 --
775 end include_adjustment;
776 --
777 -----------------------------------------------------------------------------
778 -- NAME
779 -- validate_batch_lines
780 -- PURPOSE
781 -- Applies BF specific validation to the batch.
782 -- ARGUMENTS
783 -- p_batch_id - the batch to be validate_batch_linesd.
784 -- USES
785 -- NOTES
786 -- This is used by pay_balance_upload.validate_batch_lines.
787 -----------------------------------------------------------------------------
788 --
789 procedure validate_batch_lines
790 (
791 p_batch_id number
792 ) is
793 begin
794 --
795 hr_utility.trace('Entering pay_bf_bal_upload.validate_batch_lines');
796 --
797 hr_utility.trace('Exiting pay_bf_bal_upload.validate_batch_lines');
798 --
799 end validate_batch_lines;
800 --
801 end pay_bf_bal_upload;
[Home] [Help]