4 -- Date constants.
1 PACKAGE BODY pay_cn_bal_upload AS
2 /* $Header: pycnupld.pkb 120.1 2005/12/19 21:36:05 snekkala noship $ */
3
5 START_OF_TIME CONSTANT DATE := TO_DATE('01/01/0001','DD/MM/YYYY');
6 END_OF_TIME CONSTANT DATE := TO_DATE('31/12/4712','DD/MM/YYYY');
7
8 --------------------------------------------------------------------------
9 -- --
10 -- Name : EXPIRY_DATE --
11 -- Type : FUNCTION --
12 -- Access : Public --
13 -- Description : Function to return the expiry date for the specified--
14 -- balance dimension --
15 -- --
16 -- Parameters : --
17 -- IN : p_upload_date DATE --
18 -- p_dimension_name VARCHAR2 --
19 -- p_assignment_id NUMBER --
20 -- p_original_entry_id NUMBER --
21 -- OUT : N/A --
22 -- RETURN : Date --
23 -- --
24 -- Change History : --
25 --------------------------------------------------------------------------
26 -- Rev# Date Userid Description --
27 --------------------------------------------------------------------------
28 -- 1.0 18-Mar-03 saikrish Created this function --
29 -- 1.1 19-Jun-03 bramajey Removed dimensions which are not --
30 -- supported by CN Legislation --
31 -- 1.2 08-Jul-03 bramajey Modified SELECT part of csr_tax_year --
32 -- cursor --
33 --------------------------------------------------------------------------
34 FUNCTION expiry_date ( p_upload_date DATE
35 , p_dimension_name VARCHAR2
36 , p_assignment_id NUMBER
37 , p_original_entry_id NUMBER
38 )
39 RETURN DATE
40 IS
41
42 -- Returns the start date of the fiscal year.
43 CURSOR csr_fiscal_year ( p_assignment_id NUMBER
44 , p_upload_date DATE
45 ) IS
46 SELECT NVL(ADD_MONTHS(fnd_date.canonical_to_date(HOI.ORG_INFORMATION11)
47 ,12*(floor(months_between(p_upload_date
48 ,fnd_date.canonical_to_date(HOI.ORG_INFORMATION11))/12)))
49 ,END_OF_TIME)
50 FROM per_assignments_f ASS
51 ,hr_organization_information HOI
52 WHERE ASS.assignment_id = p_assignment_id
53 AND p_upload_date BETWEEN ASS.effective_start_date
54 AND ASS.effective_end_date
55 AND HOI.organization_id = ASS.business_group_id
59 -- Change for bug 3041205 starts
56 AND UPPER(HOI.org_information_context) = 'BUSINESS GROUP INFORMATION';
57
58 -- Returns the start date of the tax year.
60
61 CURSOR csr_tax_year( p_assignment_id NUMBER
62 , p_upload_date DATE
63 ) IS
64 SELECT TRUNC(p_upload_date,'Y')
65 FROM per_assignments_f ASS
66 WHERE ASS.assignment_id = p_assignment_id
67 AND p_upload_date BETWEEN ASS.effective_start_date AND ASS.effective_end_date;
68
69 -- Change for bug 3041205 ends
70
71 -- Returns the start date of the fiscal quarter.
72 CURSOR csr_fiscal_quarter( p_assignment_id NUMBER
73 , p_upload_date DATE
74 ) IS
75 SELECT NVL(ADD_MONTHS(fnd_date.canonical_to_date(HOI.ORG_INFORMATION11)
76 , 3*(FLOOR(MONTHS_BETWEEN(p_upload_date
77 , fnd_date.canonical_to_date(HOI.ORG_INFORMATION11))/3)))
78 , END_OF_TIME)
79 FROM per_assignments_f ASS
80 ,hr_organization_information HOI
81 WHERE ASS.assignment_id = p_assignment_id
82 AND p_upload_date BETWEEN ASS.effective_start_date 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 current period on the upload date.
87 CURSOR csr_period_start( p_assignment_id NUMBER
88 , p_upload_date DATE
89 ) IS
90 SELECT NVL(PTP.start_date, END_OF_TIME)
91 FROM per_time_periods PTP
92 ,per_assignments_f ASS
93 WHERE ASS.assignment_id = p_assignment_id
94 AND p_upload_date BETWEEN ASS.effective_start_date AND ASS.effective_end_date
95 AND PTP.payroll_id = ASS.payroll_id
96 AND p_upload_date BETWEEN PTP.start_date AND PTP.end_date;
97
98 -- Returns the earliest date on which the assignment exists.
99 CURSOR csr_ele_ltd_start( p_assignment_id NUMBER
100 , p_upload_date DATE
101 ) IS
102 SELECT NVL(MIN(ASG.effective_start_date), END_OF_TIME)
103 FROM per_assignments_f ASG
104 WHERE ASG.assignment_id = p_assignment_id
105 AND ASG.effective_start_date <= p_upload_date;
106
107 CURSOR csr_asg_start_date( p_assignment_id NUMBER
108 , p_upload_date DATE
109 , p_expiry_date DATE
110 ) IS
111 SELECT NVL(GREATEST(MIN(ASS.effective_start_date), p_expiry_date),END_OF_TIME)
112 FROM per_assignments_f ASS
113 WHERE ASS.assignment_id = p_assignment_id
114 AND ASS.effective_start_date <= p_upload_date
115 AND ASS.effective_end_date >= p_expiry_date;
116
117 -- Holds the start of the month for the upload date.
118 l_month_start_date DATE;
119
120 -- Holds the start of the calendar year for the upload date.
121 l_cal_yr_start_date DATE;
122
123 -- Holds the start of the statutory year for the upload date.
124 l_tax_yr_start_date DATE;
125
126 -- Holds the start of the statutory quarter for the upload date.
127 l_tax_qtr_start_date DATE;
128
129 -- Holds the start of the fiscal year for the upload date.
130 l_fiscal_yr_start_date DATE;
131
132 -- Holds the start of the fiscal quarter for the upload date.
133 l_fiscal_qtr_start_date DATE;
134
135 -- Holds the start of the period for the upload date.
136 l_prd_start_date DATE;
137
138 -- Holds the earliest date on which the element entry exists.
139 l_ele_ltd_start_date DATE;
140
141 -- Holds the expiry date of the dimension.
142 l_expiry_date DATE;
143
144 BEGIN
145
146 -- Calculate the expiry date for the specified dimension relative to the
147 -- upload date, taking into account any contexts where appropriate. Each of
148 -- the calculations also takes into account when the assignment is on a
149 -- payroll to ensure that a balance adjustment could be made at that point
150 -- if it were required.
151 --
152 -- Lifetime to date dimension.
153
154 IF p_dimension_name IN ('_ASG_LTD','_ASG_ER_LTD','_ASG_ER_JUR_LTD') THEN
155 --
156 -- What is the earliest date on which the element entry exists ?
157 --
158 OPEN csr_ele_ltd_start( p_assignment_id
159 , p_upload_date
160 );
161 FETCH csr_ele_ltd_start
162 INTO l_ele_ltd_start_date;
163 CLOSE csr_ele_ltd_start;
164 --
165 l_expiry_date := l_ele_ltd_start_date;
166 --
167 -- Inception to date within a tax unit dimension.
168 --
169 -- Period to date dimensions.
170 --
171 ELSIF p_dimension_name IN
172 ('_ASG_PTD'
173 ,'_ASG_ER_PTD'
174 ,'_ASG_ER_JUR_PTD') THEN
175 --
176 -- What is the current period start date ?
177 --
178 OPEN csr_period_start( p_assignment_id
179 , p_upload_date
180 );
181 FETCH csr_period_start
182 INTO l_prd_start_date;
183 CLOSE csr_period_start;
184 --
185 OPEN csr_asg_start_date( p_assignment_id
186 , p_upload_date
187 , l_prd_start_date
188 );
189 FETCH csr_asg_start_date
190 INTO l_expiry_date;
194 --
191 CLOSE csr_asg_start_date;
192 --
193 -- Month dimensions.
195 ELSIF p_dimension_name IN
196 ('_ASG_MTD'
197 ,'_ASG_ER_MTD'
198 ,'_ASG_ER_JUR_MTD') THEN
199 --
200 -- What is the current month start ?
201 --
202 l_month_start_date := TRUNC(p_upload_date, 'MON');
203 OPEN csr_asg_start_date ( p_assignment_id
204 , p_upload_date
205 , l_month_start_date
206 );
207 FETCH csr_asg_start_date
208 INTO l_month_start_date;
209 CLOSE csr_asg_start_date;
210
211 OPEN csr_asg_start_date( p_assignment_id
212 , p_upload_date
213 , l_month_start_date
214 );
215 FETCH csr_asg_start_date
216 INTO l_expiry_date;
217 CLOSE csr_asg_start_date;
218 --
219 -- Quarter to date dimensions.
220 --
221 ELSIF p_dimension_name IN
222 ('_ASG_QTD'
223 ,'_ASG_ER_QTD'
224 ,'_ASG_ER_JUR_QTD') THEN
225 --
226 -- What is the start date of the tax quarter ?
227 --
228 l_tax_qtr_start_date := TRUNC(p_upload_date, 'Q');
229 OPEN csr_asg_start_date( p_assignment_id
230 , p_upload_date
231 , l_tax_qtr_start_date
232 );
233 FETCH csr_asg_start_date
234 INTO l_tax_qtr_start_date;
235 CLOSE csr_asg_start_date;
236 --
237 l_expiry_date := l_tax_qtr_start_date;
238 --
239 -- Year to date dimensions.
240 --
241 ELSIF p_dimension_name IN
242 ('_ASG_YTD'
243 ,'_ASG_ER_YTD'
244 ,'_ASG_ER_JUR_YTD') THEN
245 --
246 -- What is the start date of the tax year ?
247 --
248 OPEN csr_tax_year(p_assignment_id
249 ,p_upload_date);
250 FETCH csr_tax_year
251 INTO l_tax_yr_start_date;
252 CLOSE csr_tax_year;
253 --
254 OPEN csr_asg_start_date( p_assignment_id
255 , p_upload_date
256 , l_tax_yr_start_date
257 );
258 FETCH csr_asg_start_date
259 INTO l_tax_yr_start_date;
260 CLOSE csr_asg_start_date;
261 --
262 -- Ensure that the expiry date is at a date where the assignment is to the
263 -- correct legal company ie. matches the TAX_UNIT_ID context specified.
264 --
265 l_expiry_date := l_tax_yr_start_date;
266 --
267 -- Fiscal quarter to date dimensions.
268 --
269 ELSIF p_dimension_name IN
270 ('_ASG_FY_QTD'
271 ,'_ASG_ER_FY_QTD') THEN
272
273 -- What is the start date of the fiscal quarter ?
274 OPEN csr_fiscal_quarter( p_assignment_id
275 , p_upload_date
276 );
277 FETCH csr_fiscal_quarter
278 INTO l_fiscal_qtr_start_date;
279 CLOSE csr_fiscal_quarter;
280
281 OPEN csr_asg_start_date( p_assignment_id
282 , p_upload_date
283 , l_fiscal_qtr_start_date
284 );
285 FETCH csr_asg_start_date
286 INTO l_expiry_date;
287 CLOSE csr_asg_start_date;
288 --
289 -- Fiscal year to date dimensions.
290 --
291 ELSIF p_dimension_name IN
292 ('_ASG_FY_YTD'
293 ,'_ASG_ER_FY_YTD') THEN
294
295 -- What is the start date of the fiscal year ?
296 OPEN csr_fiscal_year( p_assignment_id
297 , p_upload_date
298 );
299 FETCH csr_fiscal_year
300 INTO l_fiscal_yr_start_date;
301 CLOSE csr_fiscal_year;
302
303 OPEN csr_asg_start_date( p_assignment_id
304 , p_upload_date
305 , l_fiscal_yr_start_date
306 );
307 FETCH csr_asg_start_date
308 INTO l_expiry_date;
309 CLOSE csr_asg_start_date;
310
311 END IF;
312 --
313 -- return the date on which the dimension expires.
314 --
315 RETURN (l_expiry_date);
316 --
317 EXCEPTION
318 WHEN OTHERS THEN
319 IF csr_fiscal_year%ISOPEN THEN
320 CLOSE csr_fiscal_year;
321 END IF;
322 IF csr_tax_year%ISOPEN THEN
323 CLOSE csr_tax_year;
324 END IF;
325 IF csr_fiscal_quarter%ISOPEN THEN
326 CLOSE csr_fiscal_quarter;
327 END IF;
328 IF csr_period_start%ISOPEN THEN
329 CLOSE csr_period_start;
330 END IF;
331 IF csr_ele_ltd_start%ISOPEN THEN
332 CLOSE csr_ele_ltd_start;
333 END IF;
334 IF csr_asg_start_date%ISOPEN THEN
335 CLOSE csr_asg_start_date;
336 END IF;
337
338 RAISE;
339 END expiry_date;
340
341 --------------------------------------------------------------------------
342 -- --
343 -- Name : IS_SUPPORTED --
344 -- Type : FUNCTION --
345 -- Access : Public --
346 -- Description : Function to check if the specified dimension is --
347 -- supported for China Localization --
351 -- OUT : N/A --
348 -- --
349 -- Parameters : --
350 -- IN : p_dimension_name VARCHAR2 --
352 -- RETURN : Number --
353 -- --
354 -- Change History : --
355 --------------------------------------------------------------------------
356 -- Rev# Date Userid Description --
357 --------------------------------------------------------------------------
358 -- 1.0 18-Mar-03 saikrish Created this function --
359 -- 1.1 18-Jun-03 bramajey Introduced cursor to check whether --
360 -- dimension is a valid dimension for CN --
361 --------------------------------------------------------------------------
362 FUNCTION is_supported ( p_dimension_name VARCHAR2)
363 RETURN NUMBER
364 IS
365 CURSOR csr_dimension (p_dimension_name VARCHAR2)
366 IS
367 --
368 SELECT balance_dimension_id
369 FROM pay_balance_dimensions
370 WHERE dimension_name = p_dimension_name
371 AND legislation_code = 'CN'
372 AND dimension_name NOT IN ( '_ASG_RUN'
373 , '_ASG_ER_RUN'
374 , '_ASG_ER_JUR_RUN'
375 , '_ASG_PMTH'
376 , '_ASG_P12MTH'
377 , '_PAYMENTS');
378 --
379 l_dimension_id NUMBER;
380 --
381 BEGIN
382 --
383 hr_utility.trace('Entering pay_cn_bal_upload.is_supported');
384 --
385 -- See if the dimension is supported.
386 --
387 OPEN csr_dimension (p_dimension_name);
388 FETCH csr_dimension INTO l_dimension_id;
389
390 IF csr_dimension%NOTFOUND THEN
391 CLOSE csr_dimension;
392 RETURN (0); -- denotes FALSE
393 ELSE
394 CLOSE csr_dimension;
395 RETURN (1); -- denotes TRUE
396 END IF;
397 --
398 hr_utility.trace('Exiting pay_cn_bal_upload.is_supported');
399 --
400 EXCEPTION
401 WHEN others THEN
402 CLOSE csr_dimension;
403 RAISE;
404 END is_supported;
405
406 --------------------------------------------------------------------------
407 -- --
408 -- Name : INCLUDE_ADJUSTMENT --
409 -- Type : FUNCTION --
410 -- Access : Public --
411 -- Description : Function to perform balance adjustment --
412 -- --
413 -- Parameters : --
414 -- IN : p_balance_type_id NUMBER --
415 -- p_dimension_name VARCHAR2 --
416 -- p_original_entry_id NUMBER --
417 -- p_upload_date DATE --
418 -- p_batch_line_id NUMBER --
419 -- p_test_batch_line_id NUMBER --
420 -- OUT : N/A --
421 -- RETURN : Number --
422 -- --
423 -- Change History : --
424 --------------------------------------------------------------------------
425 -- Rev# Date Userid Description --
426 --------------------------------------------------------------------------
427 -- 1.0 18-Mar-03 saikrish Created this function --
428 -- 1.1 30-Nov-05 snekkala Modified cursor csr_get_tax_unit --
429 --------------------------------------------------------------------------
430 FUNCTION include_adjustment ( p_balance_type_id NUMBER
431 , p_dimension_name VARCHAR2
432 , p_original_entry_id NUMBER
433 , p_upload_date DATE
434 , p_batch_line_id NUMBER
435 , p_test_batch_line_id NUMBER
436 )
437 RETURN NUMBER
438 IS
439 -- Does the balance adjustment effect the new balance dimension.
440 CURSOR csr_is_included( p_balance_type_id NUMBER
441 , p_tax_unit_id NUMBER
442 , p_original_entry_id NUMBER
443 , p_bal_adj_tax_unit_id NUMBER
444 , p_bal_adj_original_entry_id NUMBER
445 ) IS
446 SELECT BT.balance_type_id
447 FROM pay_balance_types BT
448 WHERE BT.balance_type_id = p_balance_type_id
449 --
450 -- TAX_UNIT_ID context NB. if the tax unit is used then only those
451 -- adjustments which are for the same tax unit can be included.
452 --
453 AND NVL(p_tax_unit_id, NVL(p_bal_adj_tax_unit_id, -1)) =
454 NVL(p_bal_adj_tax_unit_id, -1)
455 --
456 -- ORIGINAL_ENTRY_ID context NB. this context controls the expiry
457 -- date of the dimension in the same way as the QTD dimension. Any
461 --
458 -- existing balance adjustments that lie between the upload date
459 -- and the expiry date are all included. There is no special
460 -- criteria that has to be met.
462 AND 1 = 1;
463
464 l_bal_type_id pay_balance_types.balance_type_id%TYPE;
465
466 -- Get the tax_unit_id from the original balance batch line
467 CURSOR csr_get_tax_unit(p_batch_line_id NUMBER)
468 IS
469 SELECT pbbl.tax_unit_id
470 FROM pay_balance_batch_lines pbbl
471 , hr_all_organization_units_tl otl
472 , hr_organization_information hoi
473 WHERE pbbl.batch_line_id = p_batch_line_id
474 AND pbbl.tax_unit_id = otl.organization_id
475 AND pbbl.tax_unit_id IS NOT NULL
476 AND otl.organization_id = hoi.organization_id
477 AND hoi.org_information1 = 'HR_LEGAL'
478 AND hoi.org_information2 = 'Y'
479 AND otl.language = userenv('LANG')
480 UNION ALL
481 SELECT pbbl.tax_unit_id
482 FROM pay_balance_batch_lines pbbl
483 , hr_all_organization_units_tl otl
484 , hr_organization_information hoi
485 WHERE pbbl.batch_line_id = p_batch_line_id
486 AND upper(pbbl.gre_name) = UPPER(otl.name)
487 AND pbbl.tax_unit_id IS NULL
488 AND otl.organization_id = hoi.organization_id
489 AND hoi.org_information1 = 'HR_LEGAL'
490 AND hoi.org_information2 = 'Y'
491 AND otl.language = userenv('LANG');
492
493 -- Get tax_unit_id and original_entry_id for previously tested adjustments
494 CURSOR csr_get_tested_adjustments(p_test_batch_line_id NUMBER) IS
495 SELECT tax_unit_id
496 ,original_entry_id
497 FROM pay_temp_balance_adjustments
498 WHERE batch_line_id = p_test_batch_line_id;
499
500 -- The balance returned by the include check.
501 l_tax_unit_id hr_tax_units_v.tax_unit_id%TYPE;
502 l_adj_tax_unit_id pay_temp_balance_adjustments.tax_unit_id%TYPE;
503 l_adj_orig_entry_id pay_temp_balance_adjustments.original_entry_id%TYPE;
504
505 BEGIN
506 hr_utility.trace('Entering pay_cn_bal_upload.include_adjustment_test');
507
508 OPEN csr_get_tax_unit(p_batch_line_id);
509 FETCH csr_get_tax_unit
510 INTO l_tax_unit_id;
511 CLOSE csr_get_tax_unit;
512
513 OPEN csr_get_tested_adjustments(p_test_batch_line_id);
514 FETCH csr_get_tested_adjustments
515 INTO l_adj_tax_unit_id
516 , l_adj_orig_entry_id;
517 CLOSE csr_get_tested_adjustments;
518
519 -- Does the balance adjustment effect the new balance ?
520
521 hr_utility.trace('balance_type_id = '||TO_CHAR(p_balance_type_id));
522 hr_utility.trace('tax_unit_id = '||TO_CHAR(l_tax_unit_id));
523 hr_utility.trace('original_entry_id = '||TO_CHAR(p_original_entry_id));
524 hr_utility.trace('BA tax_unit_id = '||TO_CHAR(l_adj_tax_unit_id));
525 hr_utility.trace('BA original_entry_id = '||TO_CHAR(l_adj_orig_entry_id));
526
527 OPEN csr_is_included(p_balance_type_id
528 ,l_tax_unit_id
529 ,p_original_entry_id
530 ,l_adj_tax_unit_id
531 ,l_adj_orig_entry_id
532 );
533 FETCH csr_is_included
534 INTO l_bal_type_id;
535 CLOSE csr_is_included;
536
537 hr_utility.trace('Exiting pay_cn_bal_upload.include_adjustment_test');
538
539 -- Adjustment does contribute to the new balance.
540
541 IF l_bal_type_id IS NOT NULL THEN
542 RETURN (1); --TRUE
543
544 -- Adjustment does not contribute to the new balance.
545 ELSE
546 RETURN (0); --FALSE
547
548 END IF;
549
550 EXCEPTION
551 WHEN OTHERS THEN
552 IF csr_is_included%ISOPEN THEN
553 CLOSE csr_is_included;
554 END IF;
555 IF csr_get_tax_unit%ISOPEN THEN
556 CLOSE csr_get_tax_unit;
557 END IF;
558 IF csr_get_tested_adjustments%ISOPEN THEN
559 CLOSE csr_get_tested_adjustments;
560 END IF;
561 RAISE;
562 END include_adjustment;
563
564 --------------------------------------------------------------------------
565 -- --
566 -- Name : VALIDATE_BATCH_LINES --
567 -- Type : PROCEDURE --
568 -- Access : Public --
569 -- Description : Function to perform user-defined validation --
570 -- --
571 -- Parameters : --
572 -- IN : p_batch_id NUMBER --
573 -- OUT : N/A --
574 -- --
575 -- Change History : --
576 --------------------------------------------------------------------------
577 -- Rev# Date Userid Description --
578 --------------------------------------------------------------------------
579 -- 1.0 18-Mar-03 saikrish Created this function --
580 --------------------------------------------------------------------------
581 PROCEDURE validate_batch_lines (p_batch_id NUMBER)
582 IS
583
584 BEGIN
585 hr_utility.trace('Entering pay_cn_bal_upload.validate_batch_lines');
586
587 hr_utility.trace('Exiting pay_cn_bal_upload.validate_batch_lines');
588
589 END validate_batch_lines;
590
591 END pay_cn_bal_upload;
[Home] [Help]