1 PACKAGE BODY pay_ip_bal_upload AS
2 /* $Header: pyipupld.pkb 120.3.12010000.1 2008/07/27 22:56:11 appldev ship $ */
3
4 START_OF_TIME constant date := to_date('01/01/0001','DD/MM/YYYY');
5 END_OF_TIME constant date := to_date('31/12/4712','DD/MM/YYYY');
6
7 --
8 -- Global variable type
9 --
10 type t_dimension_names_tab is table of
11 pay_balance_dimensions.dimension_name%type index by binary_integer;
12
13 type t_route_names_tab is table of
14 ff_routes.route_name%type index by binary_integer;
15
16 --
17 -- Cache for expiry date
18 --
19 type t_expiry_date_rec is record
20 (itd_start_date date -- ITD Start date.
21 ,assignment_id number
22 );
23
24 --
25 -- Global cache
26 --
27 g_legislation_code varchar2(30); -- legislation code
28 g_leg_supported boolean; -- legislation is supported?
29 g_dimension_names t_dimension_names_tab; -- dimension name cache
30 g_route_names t_route_names_tab; -- route name cache
31 g_expiry_date_rec t_expiry_date_rec;
32
33 -- -------------------------------------------------------------------------
34 -- initialize
35 -- -------------------------------------------------------------------------
36 -- Description: This procedure sets up dimension names cache for the
37 -- specified legislation.
38 -- -------------------------------------------------------------------------
39 procedure initialize(p_legislation_code in varchar2)
40 is
41 cursor csr_baldim
42 is
43 select
44 upper(pbd.dimension_name)
45 ,r.route_name
46 from
47 pay_balance_dimensions pbd
48 ,ff_routes r
49 where
50 pbd.legislation_code = p_legislation_code
51 and pbd.route_id = r.route_id
52 and r.route_name in
53 ('Global Assignment Inception To Date',
54 'Global Assignment Processing Period To Date',
55 'Global Assignment Calendar Month To Date',
56 'Global Assignment Calendar Quarter To Date',
57 'Global Assignment Calendar Year To Date',
58 'Global Assignment Tax Quarter To Date',
59 'Global Assignment Tax Year To Date',
60 'Global Assignment Fiscal Quarter To Date',
61 'Global Assignment Fiscal Year To Date',
62 'Global Element Entry Inception To Date',
63 'Global Element Entry Processing Period To Date',
64 'Global Element Entry Calendar Month To Date',
65 'Global Element Entry Calendar Quarter To Date',
66 'Global Element Entry Calendar Year To Date',
67 'Global Assignment Within Tax Unit Inception To Date',
68 'Global Assignment Within Tax Unit Processing Period To Date',
69 'Global Assignment Within Tax Unit Calendar Month To Date',
70 'Global Assignment Within Tax Unit Calendar Quarter To Date',
71 'Global Assignment Within Tax Unit Calendar Year To Date',
72 'Global Assignment Within Tax Unit Tax Quarter To Date',
73 'Global Assignment Within Tax Unit Tax Year To Date',
74 'Global Assignment Within Tax Unit Fiscal Quarter To Date',
75 'Global Assignment Within Tax Unit Fiscal Year To Date')
76 union
77 select
78 upper(pbd.dimension_name)
79 ,r.route_name
80 from
81 pay_balance_dimensions pbd
82 ,pay_dimension_routes pdr
83 ,ff_routes r
84 where
85 pbd.legislation_code = p_legislation_code
86 and pbd.balance_dimension_id = pdr.balance_dimension_id
87 and pdr.route_id = r.route_id
88 and pdr.route_type = 'RR'
89 and r.route_name in
90 ('Global Assignment Inception To Date',
91 'Global Assignment Processing Period To Date',
92 'Global Assignment Calendar Month To Date',
93 'Global Assignment Calendar Quarter To Date',
94 'Global Assignment Calendar Year To Date',
95 'Global Assignment Tax Quarter To Date',
96 'Global Assignment Tax Year To Date',
97 'Global Assignment Fiscal Quarter To Date',
98 'Global Assignment Fiscal Year To Date',
99 'Global Element Entry Inception To Date',
100 'Global Element Entry Processing Period To Date',
101 'Global Element Entry Calendar Month To Date',
102 'Global Element Entry Calendar Quarter To Date',
103 'Global Element Entry Calendar Year To Date',
104 'Global Assignment Within Tax Unit Inception To Date',
105 'Global Assignment Within Tax Unit Processing Period To Date',
106 'Global Assignment Within Tax Unit Calendar Month To Date',
107 'Global Assignment Within Tax Unit Calendar Quarter To Date',
108 'Global Assignment Within Tax Unit Calendar Year To Date',
109 'Global Assignment Within Tax Unit Tax Quarter To Date',
110 'Global Assignment Within Tax Unit Tax Year To Date',
111 'Global Assignment Within Tax Unit Fiscal Quarter To Date',
112 'Global Assignment Within Tax Unit Fiscal Year To Date')
113 ;
114 --
115 null_expiry_date_rec t_expiry_date_rec;
116
117 begin
118
119 --
120 -- set the legislation code
121 --
122 g_legislation_code := p_legislation_code;
123
124 --
125 -- Retrieve dimension names
126 --
127 open csr_baldim;
128 fetch csr_baldim bulk collect into g_dimension_names, g_route_names;
129 close csr_baldim;
130
131 g_leg_supported := (g_dimension_names.count > 0);
132
133 --
134 -- Reset the expiry date cache
135 --
136 g_expiry_date_rec := null_expiry_date_rec;
137
138 end initialize;
139 --
140 -- -------------------------------------------------------------------------
141 -- get_dim_route_name
142 -- -------------------------------------------------------------------------
143 -- Description: This function returns the route name for the specified
144 -- dimension name.
145 -- The name is derived from the supported route list.
146 -- -------------------------------------------------------------------------
147 function get_dim_route_name
148 (p_dimension_name in varchar2
149 ,p_legislation_code in varchar2
150 ) return varchar2
151 is
152 l_idx binary_integer;
153 begin
154 if (g_legislation_code = p_legislation_code) then
155 --
156 -- cache has been established, check if there are any
157 -- dimensions supported.
158 --
159 if not g_leg_supported then
160 return '';
161 end if;
162 elsif (p_legislation_code is not null) then
163 --
164 -- initialize the legislation cache.
165 --
166 initialize(p_legislation_code);
167 --
168 else
169 return '';
170 end if;
171
172 --
173 -- Check if the dimension name exists in the cache.
174 --
175 for l_idx in 1..g_dimension_names.count loop
176 if upper(g_dimension_names(l_idx)) = upper(p_dimension_name) then
177 --
178 return g_route_names(l_idx);
179 --
180 end if;
181 end loop;
182 --
183 -- dimension not found
184 --
185 return '';
186
187 end get_dim_route_name;
188 --
189 -- -------------------------------------------------------------------------
190 -- get_expiry_date_info
191 -- -------------------------------------------------------------------------
192 -- Description: This procedure returns the expiry date info.
193 --
194 -- -------------------------------------------------------------------------
195 procedure get_expiry_date_info
196 (p_assignment_id in number
197 ,p_upload_date in date
198 ,p_itd_start_date out nocopy date
199 )
200 is
201 --
202 -- Bug 5234566. Use of union all to ensure the start date returned is
203 -- on a time period.
204 --
205 cursor csr_itd_start_date
206 is
207 --
208 -- Minimum asg start date that is on time period.
209 --
210 -- Asg |----------------->
211 -- Prd |----->|----->|----->
212 --
213 select
214 min(asg.effective_start_date) start_date
215 from
216 per_all_assignments_f asg
217 ,per_time_periods ptp
218 where
219 asg.assignment_id = p_assignment_id
220 and ptp.payroll_id = asg.payroll_id
221 and asg.effective_start_date between ptp.start_date
222 and ptp.end_date
223 UNION ALL
224 --
225 -- Minimum period start date that is on the assignment.
226 --
227 -- Asg |----------------->
228 -- Prd |----->|----->|----->
229 --
230 select
231 min(ptp.start_date) start_date
232 from
233 per_all_assignments_f asg
234 ,per_time_periods ptp
235 where
236 asg.assignment_id = p_assignment_id
237 and ptp.payroll_id = asg.payroll_id
238 and ptp.start_date between asg.effective_start_date
239 and asg.effective_end_date
240 order by 1
241 ;
242
243 l_itd_start_date date;
244
245 begin
246 if p_assignment_id = g_expiry_date_rec.assignment_id then
247
248 --
249 -- The start date is before the upload date.
250 --
251 l_itd_start_date := g_expiry_date_rec.itd_start_date;
252
253 elsif p_assignment_id is not null then
254 --
255 -- Reset the expiry date info.
256 --
257 g_expiry_date_rec.assignment_id := p_assignment_id;
258
259 open csr_itd_start_date;
260 fetch csr_itd_start_date into l_itd_start_date;
261 close csr_itd_start_date;
262
263 l_itd_start_date := nvl(l_itd_start_date, END_OF_TIME);
264 g_expiry_date_rec.itd_start_date := l_itd_start_date;
265
266 end if;
267
268 --
269 -- Check to see if the start date is before the upload date.
270 --
271 if l_itd_start_date <= p_upload_date then
272 p_itd_start_date := l_itd_start_date;
273 else
274 p_itd_start_date := END_OF_TIME;
275 end if;
276
277 end get_expiry_date_info;
278 --
279 -- -------------------------------------------------------------------------
280 -- Function to check whether a particular dimension is supported by
281 -- International Payroll. Function returns TRUE if it is a International
282 -- Payroll supported dimension, otherwise returns FALSE.
283 -- -------------------------------------------------------------------------
284 FUNCTION international_payroll
285 (p_dimension_name IN VARCHAR2,
286 p_legislation_code IN VARCHAR2) RETURN BOOLEAN IS
287 l_idx binary_integer;
288 l_route_name ff_routes.route_name%type;
289 BEGIN
290 hr_utility.trace('Entering pay_ip_bal_upload.international_payroll');
291
292 --
293 -- Check to see if the dimension/route exists in the support list.
294 --
295 l_route_name := get_dim_route_name
296 (p_dimension_name => p_dimension_name
297 ,p_legislation_code => p_legislation_code
298 );
299
300 if l_route_name is not null then
301 return true;
302 else
303 return false;
304 end if;
305
306 hr_utility.trace('Exiting pay_ip_bal_upload.international_payroll');
307 END international_payroll;
308
309
310 -- -------------------------------------------------------------------------
311 -- Funtion to return expiry date for supported Routes.
312 -- -------------------------------------------------------------------------
313 FUNCTION expiry_date
314 (p_upload_date IN DATE,
315 p_dimension_name IN VARCHAR2,
316 p_assignment_id IN NUMBER,
317 p_original_entry_id IN NUMBER,
318 p_business_group_id IN NUMBER,
319 p_legislation_code IN VARCHAR2) RETURN DATE IS
320
321 /************
322 *
323 * Now this ITD expiry is checked in get_expiry_date_info.
324 *
325 CURSOR csr_expiry_date
326 (p_assignment_id NUMBER
327 ,p_upload_date DATE
328 ,p_expiry_date DATE) IS
329 SELECT nvl(GREATEST(MIN(ass.effective_start_date), MIN(ptp.start_date), p_expiry_date)
330 ,END_OF_TIME)
331 FROM per_assignments_f ass
332 ,per_time_periods ptp
333 WHERE ass.assignment_id = p_assignment_id
334 AND ass.effective_start_date <= p_upload_date
335 AND ass.effective_end_date >= p_expiry_date
336 AND ptp.payroll_id = ass.payroll_id
337 AND ptp.start_date BETWEEN ass.effective_start_date and p_upload_date;
338 ************/
339
340 --
341 -- period start date
342 --
343 CURSOR csr_start_of_date
344 (p_assignment_id NUMBER
345 ,p_upload_date DATE
346 ) IS
347 SELECT ptp.start_date
348 FROM per_all_assignments_f ass
349 ,per_time_periods ptp
350 WHERE ass.assignment_id = p_assignment_id
351 AND ass.effective_start_date <= p_upload_date
352 AND ass.effective_end_date >= p_upload_date
353 AND ptp.payroll_id = ass.payroll_id
354 AND p_upload_date BETWEEN ptp.start_date
355 AND ptp.end_date;
356
357 /************
358 *
359 * This is replaced by get_dim_route_name().
360 *
361 CURSOR csr_route_name
362 (p_legislation_code VARCHAR2,
363 p_dimension_name VARCHAR2) IS
364 SELECT route_name
365 FROM ff_routes
366 WHERE route_id =
367 (SELECT route_id
368 FROM PAY_BALANCE_DIMENSIONS
369 WHERE legislation_code= p_legislation_code
370 AND upper(dimension_name) = upper(p_dimension_name)
371 AND business_group_id IS NULL);
372 ************/
373 --
374 -- original entry start date
375 --
376 cursor csr_oe_start_date
377 is
378 select min(pee.effective_start_date)
379 from
380 pay_element_entries_f pee
381 where
382 pee.assignment_id = p_assignment_id
383 and pee.entry_type = 'E'
384 and ((pee.element_entry_id = p_original_entry_id
385 and pee.original_entry_id is null)
386 or (pee.original_entry_id = p_original_entry_id));
387
388 l_expiry_date DATE;
389 l_business_group_id PER_ALL_ASSIGNMENTS_F.BUSINESS_GROUP_ID%TYPE;
390 l_route_name FF_ROUTES.ROUTE_NAME%TYPE;
391 l_itd_start_date date;
392 l_oe_start_date date;
393 BEGIN
394
395 hr_utility.trace('Entering pay_ip_bal_upload.expiry_date');
396
397 l_route_name := get_dim_route_name
398 (p_dimension_name => p_dimension_name
399 ,p_legislation_code => p_legislation_code);
400
401 hr_utility.trace('Route='||l_route_name);
402 --
403 -- Get the ITD start date.
404 --
405 get_expiry_date_info
406 (p_assignment_id => p_assignment_id
407 ,p_upload_date => p_upload_date
408 ,p_itd_start_date => l_itd_start_date
409 );
410
411 --
412 hr_utility.trace('Asg Start Date='||l_itd_start_date);
413
414 --
415 -- Get the original entry start date.
416 --
417 if l_route_name in ('Global Element Entry Inception To Date'
418 ,'Global Element Entry Processing Period To Date'
419 ,'Global Element Entry Calendar Month To Date'
420 ,'Global Element Entry Calendar Quarter To Date'
421 ,'Global Element Entry Calendar Year To Date') then
422 --
423 open csr_oe_start_date;
424 fetch csr_oe_start_date into l_oe_start_date;
425 close csr_oe_start_date;
426
430 end if;
427 l_oe_start_date := nvl(l_oe_start_date, END_OF_TIME);
428 hr_utility.trace('OE Start Date='||l_oe_start_date);
429
431
432 IF l_route_name IN ( 'Global Assignment Processing Period To Date',
433 'Global Element Entry Processing Period To Date',
434 'Global Assignment Within Tax Unit Processing Period To Date') THEN
435
436 open csr_start_of_date(p_assignment_id, p_upload_date);
437 fetch csr_start_of_date into l_expiry_date;
438 close csr_start_of_date;
439 hr_utility.trace('Period Start Date=' || l_expiry_date);
440
441 ELSIF l_route_name IN ( 'Global Assignment Inception To Date',
442 'Global Element Entry Inception To Date',
443 'Global Assignment Within Tax Unit Inception To Date') THEN
444 l_expiry_date := l_itd_start_date;
445 hr_utility.trace('Asg Start Date=' || l_expiry_date);
446
447 ELSIF l_route_name IN ( 'Global Assignment Calendar Month To Date',
448 'Global Element Entry Calendar Month To Date',
449 'Global Assignment Within Tax Unit Calendar Month To Date') THEN
450 l_expiry_date := TRUNC(p_upload_date,'MM');
451 hr_utility.trace('Mth Start Date=' || l_expiry_date);
452
453 ELSIF l_route_name IN ( 'Global Assignment Calendar Quarter To Date',
454 'Global Element Entry Calendar Quarter To Date',
455 'Global Assignment Within Tax Unit Calendar Quarter To Date') THEN
456 l_expiry_date := TRUNC(p_upload_date,'Q');
457 hr_utility.trace('Qtr Start Date=' || l_expiry_date);
458
459 ELSIF l_route_name IN ( 'Global Assignment Calendar Year To Date',
460 'Global Element Entry Calendar Year To Date',
461 'Global Assignment Within Tax Unit Calendar Year To Date') THEN
462 l_expiry_date := TRUNC(p_upload_date,'Y');
463 hr_utility.trace('Year Start Date=' || l_expiry_date);
464
465 ELSIF l_route_name IN ( 'Global Assignment Tax Quarter To Date',
466 'Global Assignment Within Tax Unit Tax Quarter To Date') THEN
467 l_expiry_date := pay_ip_route_support.tax_quarter(p_business_group_id, p_upload_date);
468 hr_utility.trace('Tax Qtr Start Date=' || l_expiry_date);
469
470 ELSIF l_route_name IN ( 'Global Assignment Tax Year To Date',
471 'Global Assignment Within Tax Unit Tax Year To Date') THEN
472 l_expiry_date := pay_ip_route_support.tax_year(p_business_group_id, p_upload_date);
473 hr_utility.trace('Tax Year Start Date=' || l_expiry_date);
474
475 ELSIF l_route_name IN ( 'Global Assignment Fiscal Quarter To Date',
476 'Global Assignment Within Tax Unit Fiscal Quarter To Date') THEN
477 l_expiry_date := pay_ip_route_support.fiscal_quarter(p_business_group_id, p_upload_date);
478 hr_utility.trace('FQ Start Date=' || l_expiry_date);
479
480 ELSIF l_route_name IN ( 'Global Assignment Fiscal Year To Date',
481 'Global Assignment Within Tax Unit Fiscal Year To Date') THEN
482 l_expiry_date := pay_ip_route_support.fiscal_year(p_business_group_id, p_upload_date);
483 hr_utility.trace('FY Start Date=' || l_expiry_date);
484
485 ELSE
486 --
487 -- Dimension not supported.
488 --
489 l_expiry_date := END_OF_TIME;
490 hr_utility.trace('Dimension Not Supported. ' || p_dimension_name);
491
492 END IF;
493
494 l_expiry_date := nvl(greatest(l_itd_start_date
495 ,l_expiry_date
496 ,nvl(l_oe_start_date, l_expiry_date)
497 ), END_OF_TIME);
498
499 if (l_expiry_date <> END_OF_TIME) and (l_expiry_date > p_upload_date) then
500 hr_utility.trace('Expiry date is later than upload_date! expiry_date='||l_expiry_date);
501 --
502 l_expiry_date := END_OF_TIME;
503 end if;
504
505 hr_utility.trace('Exiting pay_ip_bal_upload.expiry_date');
506
507 RETURN l_expiry_date;
508
509 END expiry_date;
510
511 -- -------------------------------------------------------------------------
512 -- Function to check if adjustment is required for a particular Dimension.
513 -- p_test_batch_line_id identifies the adjustment that has already been processed
514 -- p_batch_line_id identifies the adjustment currently being processed.
515 -- -------------------------------------------------------------------------
516 FUNCTION include_adjustment
517 (
518 p_balance_type_id NUMBER
519 ,p_dimension_name VARCHAR2
520 ,p_original_entry_id NUMBER
521 ,p_upload_date DATE
522 ,p_batch_line_id NUMBER
523 ,p_test_batch_line_id NUMBER
524 ,p_legislation_code VARCHAR2
525 ) RETURN BOOLEAN IS
526
527 l_include_adj BOOLEAN := TRUE ;
528 l_orginal_entry_id NUMBER;
529 l_tax_unit_id NUMBER;
530
531 CURSOR csr_bal_adj (p_test_batch_line_id NUMBER, p_batch_line_id NUMBER) IS
532 SELECT tba.original_entry_id
533 FROM pay_temp_balance_adjustments tba,
534 pay_balance_batch_lines bbl
535 WHERE tba.batch_line_id = p_test_batch_line_id
536 AND bbl.batch_line_id = p_batch_line_id
537 AND nvl(tba.original_entry_id,0) = nvl(bbl.original_entry_id,0);
538
539 CURSOR csr_bal_adj_tu (p_test_batch_line_id NUMBER, p_batch_line_id NUMBER) IS
540 SELECT tba.tax_unit_id
541 FROM pay_temp_balance_adjustments tba,
542 pay_balance_batch_lines bbl
546
543 WHERE tba.batch_line_id = p_test_batch_line_id
544 AND bbl.batch_line_id = p_batch_line_id
545 AND nvl(tba.tax_unit_id,0) = nvl(bbl.tax_unit_id,0);
547 /********************
548 *
549 * This is replaced by get_dim_route_name().
550 *
551 CURSOR csr_route_name (l_legislation_code VARCHAR2, l_dimension_name VARCHAR2) IS
552 SELECT route_name
553 FROM ff_routes
554 WHERE route_id =
555 (SELECT route_id
556 FROM PAY_BALANCE_DIMENSIONS
557 WHERE legislation_code= l_legislation_code
558 AND upper(dimension_name) = upper(l_dimension_name)
559 AND business_group_id IS NULL);
560 ********************/
561
562 l_route_name FF_ROUTES.ROUTE_NAME%TYPE;
563 BEGIN
564
565 hr_utility.trace('Entering pay_ip_bal_upload.include_adjustment');
566
567 l_route_name := get_dim_route_name
568 (p_dimension_name => p_dimension_name
569 ,p_legislation_code => p_legislation_code);
570
571 IF l_route_name IN ('Global Assignment Inception To Date',
572 'Global Assignment Processing Period To Date',
573 'Global Assignment Calendar Month To Date',
574 'Global Assignment Calendar Quarter To Date',
575 'Global Assignment Calendar Year To Date',
576 'Global Assignment Tax Quarter To Date',
577 'Global Assignment Tax Year To Date',
578 'Global Assignment Fiscal Quarter To Date',
579 'Global Assignment Fiscal Year To Date') THEN
580 l_include_adj := TRUE;
581
582 ELSIF l_route_name IN ('Global Element Entry Inception To Date',
583 'Global Element Entry Processing Period To Date',
584 'Global Element Entry Calendar Month To Date',
585 'Global Element Entry Calendar Quarter To Date',
586 'Global Element Entry Calendar Year To Date') THEN
587
588 OPEN csr_bal_adj(p_test_batch_line_id => p_test_batch_line_id,
589 p_batch_line_id => p_batch_line_id);
590
591 FETCH csr_bal_adj INTO l_orginal_entry_id;
592
593 IF csr_bal_adj%NOTFOUND THEN
594 l_include_adj := FALSE ;
595 END IF;
596
597 CLOSE csr_bal_adj;
598 ELSIF l_route_name IN ('Global Assignment Within Tax Unit Inception To Date',
599 'Global Assignment Within Tax Unit Processing Period To Date',
600 'Global Assignment Within Tax Unit Calendar Month To Date',
601 'Global Assignment Within Tax Unit Calendar Quarter To Date',
602 'Global Assignment Within Tax Unit Calendar Year To Date',
603 'Global Assignment Within Tax Unit Tax Quarter To Date',
604 'Global Assignment Within Tax Unit Tax Year To Date',
605 'Global Assignment Within Tax Unit Fiscal Quarter To Date',
606 'Global Assignment Within Tax Unit Fiscal Year To Date') THEN
607
608 OPEN csr_bal_adj_tu(p_test_batch_line_id => p_test_batch_line_id,
609 p_batch_line_id => p_batch_line_id);
610
611 FETCH csr_bal_adj_tu INTO l_tax_unit_id;
612
613 IF csr_bal_adj_tu%NOTFOUND THEN
614 l_include_adj := FALSE ;
615 END IF;
616
617 CLOSE csr_bal_adj_tu;
618 ELSE
619 NULL;
620 END IF;
621
622 hr_utility.trace('Exiting pay_ip_bal_upload.include_adjustment');
623
624 RETURN l_include_adj;
625
626 END include_adjustment;
627 END pay_ip_bal_upload;
628
[Home] [Help]