[Home] [Help]
PACKAGE BODY: APPS.PAY_IN_BAL_UPLOAD
Source
1 PACKAGE BODY pay_in_bal_upload AS
2 /* $Header: pyinupld.pkb 120.3.12020000.2 2012/11/26 09:05:33 anchhetr ship $ */
3
4 -- Date Constants
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 g_package CONSTANT VARCHAR2(100) := 'pay_in_bal_upload.';
8 g_debug BOOLEAN ;
9
10 --------------------------------------------------------------------------
11 -- --
12 -- Name : EXPIRY_DATE --
13 -- Type : FUNCTION --
14 -- Access : Public --
15 -- Description : Function to return the expiry date for the specified--
16 -- balance dimension --
17 -- --
18 -- Parameters : --
19 -- IN : p_upload_date DATE --
20 -- p_dimension_name VARCHAR2 --
21 -- p_assignment_id NUMBER --
22 -- p_original_entry_id NUMBER --
23 -- OUT : N/A --
24 -- RETURN : Date --
25 -- --
26 -- Change History : --
27 --------------------------------------------------------------------------
28 -- Rev# Date Userid Description --
29 --------------------------------------------------------------------------
30 -- 1.0 16-JUL-2004 lnagaraj Created this function --
31 --------------------------------------------------------------------------
32
33
34 FUNCTION expiry_date
35 (p_upload_date IN DATE,
36 p_dimension_name IN VARCHAR2,
37 p_assignment_id IN NUMBER,
38 p_original_entry_id IN NUMBER)
39 RETURN DATE IS
40
41 -- Returns the start date of the current period on the upload date.
42 CURSOR csr_period_start( p_assignment_id NUMBER
43 , p_upload_date DATE
44 ) IS
45 SELECT NVL(PTP.start_date, END_OF_TIME)
46 FROM per_time_periods PTP
47 ,per_assignments_f ASS
48 WHERE ASS.assignment_id = p_assignment_id
49 AND p_upload_date BETWEEN ASS.effective_start_date AND ASS.effective_end_date
50 AND PTP.payroll_id = ASS.payroll_id
51 AND p_upload_date BETWEEN PTP.start_date AND PTP.end_date;
52
53 CURSOR csr_asg_start_date( p_assignment_id NUMBER
54 , p_upload_date DATE
55 , p_expiry_date DATE
56 ) IS
57 SELECT NVL(GREATEST(MIN(ASS.effective_start_date), p_expiry_date),END_OF_TIME)
58 FROM per_assignments_f ASS
59 ,per_time_periods PTP ----bug 15844819
60 WHERE ASS.assignment_id = p_assignment_id
64 AND ASS.effective_start_date <= p_upload_date
61 AND ASS.assignment_type in ('E','B','C') ----bug 15844819
62 AND PTP.payroll_id = ASS.payroll_id
63 AND ASS.effective_start_date between PTP.start_date and PTP.end_date
65 AND ASS.effective_end_date >= p_expiry_date;
66
67
68
69
70 -- Holds the start of the month for the upload date.
71 l_month_start_date DATE;
72
73 -- Holds the start of the calendar year for the upload date.
74 l_cal_yr_start_date DATE;
75
76 -- Holds the start of the statutory year for the upload date.
77 l_tax_yr_start_date DATE;
78
79 -- Holds the start of the statutory/calendar quarter for the upload date.
80 l_tax_qtr_start_date DATE;
81
82 -- Holds the start of the mar-feb year for the upload date.
83 l_prov_yr_start_date DATE;
84
85 -- Holds the start of the period for the upload date.
86 l_prd_start_date DATE;
87
88 -- Holds the start date of the half tax year
89 l_half_yr_start_date DATE;
90
91 -- Holds the expiry date of the dimension.
92 l_expiry_date DATE;
93
94 -- Holds the start date of the half calender year
95 l_c_half_yr_start_date DATE;
96
97 l_year NUMBER(4);
98 l_month NUMBER(2);
99 l_start_dd_mm VARCHAR2(6);
100 l_half_start1_dd_mm VARCHAR2(6);
101 l_half_start2_dd_mm VARCHAR2(6);
102 BEGIN
103
104
105
106
107 -- Calculate the expiry date for the specified dimension relative to the
108 -- upload date, taking into account any contexts where appropriate. Each of
109 -- the calculations also takes into account when the assignment is on a
110 -- payroll to ensure that a balance adjustment could be made at that point
111 -- if it were required.
112
113
114 IF p_dimension_name IN ('_ASG_PTD'
115 ,'_ASG_ORG_PTD'
116 ,'_ASG_STATE_PTD'
117 ,'_ASG_LE_PTD'
118 ,'_ASG_COMP_PTD'
119 ,'_ASG_LE_COMP_PTD')
120 THEN
121 --
122 -- What is the Current Period Start Date?
123 --
124 OPEN csr_period_start( p_assignment_id
125 , p_upload_date
126 );
127 FETCH csr_period_start
128 INTO l_prd_start_date;
129 CLOSE csr_period_start;
130 --
131
132 OPEN csr_asg_start_date( p_assignment_id
133 , p_upload_date
134 , l_prd_start_date
135 );
136 FETCH csr_asg_start_date
137 INTO l_expiry_date;
138 CLOSE csr_asg_start_date;
139
140
141 ELSIF p_dimension_name IN ('_ASG_MTD'
142 ,'_ASG_ORG_MTD'
143 ,'_ASG_STATE_MTD'
144 ,'_ASG_LE_MTD'
145 ,'_ASG_COMP_MTD'
146 ,'_ASG_LE_COMP_MTD')
147 THEN
148 l_month_start_date := TRUNC(p_upload_date, 'MON');
149
150 OPEN csr_asg_start_date ( p_assignment_id
151 , p_upload_date
152 , l_month_start_date
153 );
154 FETCH csr_asg_start_date
155 INTO l_month_start_date;
156 CLOSE csr_asg_start_date;
157 l_expiry_date := l_month_start_date;
158 ELSIF p_dimension_name IN ('_ASG_QTD'
159 ,'_ASG_ORG_QTD'
160 ,'_ASG_STATE_QTD'
161 ,'_ASG_LE_QTD'
162 ,'_ASG_COMP_QTD'
163 ,'_ASG_LE_COMP_QTD')
164 THEN
165 l_tax_qtr_start_date := TRUNC(p_upload_date, 'Q');
166 OPEN csr_asg_start_date( p_assignment_id
167 , p_upload_date
168 , l_tax_qtr_start_date
169 );
170 FETCH csr_asg_start_date
171 INTO l_tax_qtr_start_date;
172 CLOSE csr_asg_start_date;
173 l_expiry_date := l_tax_qtr_start_date;
174 ELSIF p_dimension_name IN ('_ASG_YTD'
175 ,'_ASG_ORG_YTD'
176 ,'_ASG_STATE_YTD'
177 ,'_ASG_LE_YTD'
178 ,'_ASG_COMP_YTD'
179 ,'_ASG_LE_COMP_YTD')
180 THEN --Bugfix 3796385
181 l_year := to_number(to_char(p_upload_date,'yyyy'));
182 l_start_dd_mm := '01-04-';
183 IF p_upload_date>=to_date(l_start_dd_mm||to_char(l_year),'dd-mm-yyyy') THEN
184 l_tax_yr_start_date :=to_date(l_start_dd_mm||to_char(l_year),'dd-mm-yyyy');
185 ELSE
186 l_tax_yr_start_date := to_date(l_start_dd_mm||to_char(l_year -1),'dd-mm-yyyy');
187 END IF;
188
189
190 --
191 OPEN csr_asg_start_date( p_assignment_id
192 , p_upload_date
193 , l_tax_yr_start_date
194 );
195 FETCH csr_asg_start_date
196 INTO l_tax_yr_start_date;
197 CLOSE csr_asg_start_date;
198
199 l_expiry_date := l_tax_yr_start_date;
200
201 ELSIF p_dimension_name IN ('_ASG_CYTD','_ASG_ORG_CYTD','_ASG_STATE_CYTD') THEN
202 l_cal_yr_start_date := TRUNC(p_upload_date,'Y');
203 --
204 OPEN csr_asg_start_date( p_assignment_id
205 , p_upload_date
206 , l_cal_yr_start_date
207 );
208 FETCH csr_asg_start_date
209 INTO l_cal_yr_start_date;
210 CLOSE csr_asg_start_date;
211
215 ,'_ASG_LE_MAR_FEB_YTD')
212 l_expiry_date := l_cal_yr_start_date;
213 ELSIF p_dimension_name IN ('_ASG_MAR_FEB_YTD'
214 ,'_ASG_ORG_MAR_FEB_YTD'
216 THEN
217 l_year := to_number(to_char(p_upload_date,'yyyy'));
218 l_start_dd_mm := '01-03-';
219 IF p_upload_date>=to_date(l_start_dd_mm||to_char(l_year),'dd-mm-yyyy') THEN
220 l_prov_yr_start_date :=to_date(l_start_dd_mm||to_char(l_year),'dd-mm-yyyy');
221 ELSE
222 l_prov_yr_start_date := to_date(l_start_dd_mm||to_char(l_year -1),'dd-mm-yyyy');
223 END IF;
224
225 --
226 OPEN csr_asg_start_date( p_assignment_id
227 , p_upload_date
228 , l_prov_yr_start_date
229 );
230 FETCH csr_asg_start_date
231 INTO l_prov_yr_start_date;
232 CLOSE csr_asg_start_date;
233
234 l_expiry_date := l_prov_yr_start_date;
235
236 ELSIF p_dimension_name IN('_ASG_HYTD'
237 ,'_ASG_ORG_HYTD'
238 ,'_ASG_STATE_HYTD'
239 ,'_ASG_LE_HYTD'
240 ,'_ASG_COMP_HYTD'
241 ,'_ASG_LE_COMP_HYTD')
242 THEN
243 l_year := to_number(to_char(p_upload_date,'yyyy'));
244 l_month :=to_number(to_char(p_upload_date,'mm'));
245 l_half_start1_dd_mm := '01-04-';
246 l_half_start2_dd_mm := '01-10-';
247 IF l_month between 4 and 9 THEN
248 l_half_yr_start_date :=to_date(l_half_start1_dd_mm||to_char(l_year),'dd-mm-yyyy');
249 ELSIF l_month between 10 and 12 THEN
250 l_half_yr_start_date := to_date(l_half_start2_dd_mm||to_char(l_year),'dd-mm-yyyy');
251 ELSE
252 l_half_yr_start_date := to_date(l_half_start2_dd_mm||to_char(l_year -1),'dd-mm-yyyy');
253 END IF;
254
255 OPEN csr_asg_start_date( p_assignment_id
256 , p_upload_date
257 , l_half_yr_start_date
258 );
259 FETCH csr_asg_start_date
260 INTO l_half_yr_start_date;
261 CLOSE csr_asg_start_date;
262
263 l_expiry_date := l_half_yr_start_date;
264 ELSIF p_dimension_name IN ('_ASG_LTD','_ASG_SRC_LTD','_ASG_COMP_LTD') THEN
265 OPEN csr_asg_start_date ( p_assignment_id
266 , p_upload_date
267 , to_date('01-01-0001','dd-mm-yyyy')
268 );
269 FETCH csr_asg_start_date
270 INTO l_month_start_date;
271 CLOSE csr_asg_start_date;
272
273 l_expiry_date := l_month_start_date;
274 ELSIF p_dimension_name IN('_ASG_ORG_CHYTD'
275 ,'_ASG_STATE_CHYTD'
276 ,'_ASG_CHYTD'
277 )
278 THEN
279 l_year := to_number(to_char(p_upload_date,'yyyy'));
280 l_month :=to_number(to_char(p_upload_date,'mm'));
281 l_half_start1_dd_mm := '01-01-';
282 l_half_start2_dd_mm := '01-07-';
283 IF l_month between 1 and 6 THEN
284 l_c_half_yr_start_date :=to_date(l_half_start1_dd_mm||to_char(l_year),'dd-mm-yyyy');
285 ELSE
286 l_c_half_yr_start_date := to_date(l_half_start2_dd_mm||to_char(l_year),'dd-mm-yyyy');
287 END IF;
288
289 OPEN csr_asg_start_date( p_assignment_id
290 , p_upload_date
291 , l_c_half_yr_start_date
292 );
293 FETCH csr_asg_start_date
294 INTO l_c_half_yr_start_date;
295 CLOSE csr_asg_start_date;
296
297 l_expiry_date := l_c_half_yr_start_date;
298 END IF;
299
300 RETURN l_expiry_date;
301
302 EXCEPTION
303 WHEN OTHERS THEN
304 IF csr_period_start%ISOPEN THEN
305 CLOSE csr_period_start;
306 END IF;
307 IF csr_asg_start_date%ISOPEN THEN
308 CLOSE csr_asg_start_date;
309 END IF;
310 RAISE;
311
312 END expiry_date;
313
314
315
316 --------------------------------------------------------------------------
317 -- --
318 -- Name : IS_SUPPORTED --
319 -- Type : FUNCTION --
320 -- Access : Public --
321 -- Description : Function to check if the specified dimension is --
322 -- supported for India Localization --
323 -- --
324 -- Parameters : --
325 -- IN : p_dimension_name VARCHAR2 --
326 -- OUT : N/A --
327 -- RETURN : Number --
328 -- --
329 -- Change History : --
330 --------------------------------------------------------------------------
331 -- Rev# Date Userid Description --
332 --------------------------------------------------------------------------
333 -- 1.0 16-JUL-2004 lnagaraj Created this function --
334
335 --------------------------------------------------------------------------
336 FUNCTION is_supported ( p_dimension_name VARCHAR2)
337 RETURN NUMBER
338 IS
339 CURSOR csr_dimension (p_dimension_name VARCHAR2)
340 IS
341 --
345 AND legislation_code = 'IN'
342 SELECT balance_dimension_id
343 FROM pay_balance_dimensions
344 WHERE dimension_name = p_dimension_name
346 AND dimension_name NOT IN ( '_ASG_RUN'
347 , '_ASG_ORG_RUN'
348 , '_ASG_STATE_RUN'
349 , '_PAYMENTS'
350 , '_ASG_LE_RUN'
351 , '_ASG_COMP_RUN'
352 , '_ASG_LE_COMP_RUN'
353 , '_ASG_SRC_RUN'
354 ,'_ASG_COMP_LTD'
355 ,'_ASG_LE_FY_PMTH'
356 ,'_ASG_PMTH'
357 ,'_ASG_P10MTH'
358 );
359 --
360 l_dimension_id NUMBER;
361 --
362 l_procedure VARCHAR2(250);
363 l_message VARCHAR2(250);
364
365 BEGIN
366 g_debug := hr_utility.debug_enabled;
367 l_procedure := g_package ||'is_supported';
368 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
369 IF (g_debug)
370 THEN
371 pay_in_utils.trace('**************************************************','********************');
372 pay_in_utils.trace('p_dimension_name :',p_dimension_name);
373 pay_in_utils.trace('**************************************************','********************');
374 END IF;
375
376 --
377 -- hr_utility.trace('Entering pay_in_bal_upload.is_supported');
378 --
379 -- See if the dimension is supported.
380 --
381 OPEN csr_dimension (p_dimension_name);
382 FETCH csr_dimension INTO l_dimension_id;
383
384 IF (g_debug)
385 THEN
386 pay_in_utils.trace('**************************************************','********************');
387 pay_in_utils.trace('l_dimension_id :',l_dimension_id);
388 pay_in_utils.trace('**************************************************','********************');
389 END IF;
390
391 IF csr_dimension%NOTFOUND THEN
392 CLOSE csr_dimension;
393 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 20);
394 RETURN (0); -- denotes FALSE
395 ELSE
396 CLOSE csr_dimension;
397 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 30);
398 RETURN (1); -- denotes TRUE
399 END IF;
400 --
401 -- hr_utility.trace('Exiting pay_in_bal_upload.is_supported');
402 --
403 EXCEPTION
404 WHEN others THEN
405 CLOSE csr_dimension;
406 l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR', 'FUNCTION:'||l_procedure, 'SQLERRMC:'||sqlerrm);
407 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 50);
408 pay_in_utils.trace(l_message,l_procedure);
409 pay_in_utils.trace('**************************************************','********************');
410 RAISE;
411 END is_supported;
412
413
414 -- Function to check if adjustment is required for a particular Dimension.
415 -- p_test_batch_line_id identifies the adjustment that has already been processed
416 -- p_batch_line_id identifies the adjustment currently being processed.
417 --------------------------------------------------------------------------
418 -- --
419 -- Name : INCLUDE_ADJUSTMENT --
420 -- Type : FUNCTION --
421 -- Access : Public --
422 -- Description : Function to perform balance adjustment --
423 -- --
424 -- Parameters : --
425 -- IN : p_balance_type_id NUMBER --
426 -- p_dimension_name VARCHAR2 --
427 -- p_original_entry_id NUMBER --
428 -- p_upload_date DATE --
429 -- p_batch_line_id NUMBER --
430 -- p_test_batch_line_id NUMBER --
431 -- OUT : N/A --
432 -- RETURN : Number --
433 -- --
434 -- Change History : --
435 --------------------------------------------------------------------------
436 -- Rev# Date Userid Description --
437 --------------------------------------------------------------------------
438 -- 1.0 16-JUL-2004 lnagaraj Created this function --
439 -- 1.1 21-Oct-2005 lnagaraj Modified csr_get_details --
440 --------------------------------------------------------------------------
441
442 FUNCTION include_adjustment
443 (
444 p_balance_type_id NUMBER
445 ,p_dimension_name VARCHAR2
446 ,p_original_entry_id NUMBER
447 ,p_upload_date DATE
448 ,p_batch_line_id NUMBER
449 ,p_test_batch_line_id NUMBER
450 )
451 RETURN NUMBER
452 IS
453
454 -- Does the balance adjustment effect the new balance dimension.
455 CURSOR csr_is_included( p_balance_type_id NUMBER
456 , p_source_id NUMBER
457 , p_original_entry_id NUMBER
458 , p_tax_unit_id NUMBER
459 , p_jurisdiction_code VARCHAR2
463 , p_bal_adj_jurisdiction_code VARCHAR
460 , p_source_text VARCHAR2
461 , p_source_text2 VARCHAR2
462 , p_bal_adj_tax_unit_id NUMBER
464 , p_bal_adj_source_id NUMBER
465 , p_bal_adj_original_entry_id NUMBER
466 , p_bal_adj_source_text VARCHAR2
467 , p_bal_adj_source_text2 VARCHAR2
468 ) IS
469 SELECT BT.balance_type_id
470 FROM pay_balance_types BT
471 WHERE BT.balance_type_id = p_balance_type_id
472 and ((p_source_id is null) or
473 (p_source_id is not null and p_source_id = p_bal_adj_source_id))
474 and ((p_tax_unit_id is null) or
475 (p_tax_unit_id is not null and p_tax_unit_id = p_bal_adj_tax_unit_id))
476 and ((p_jurisdiction_code is null) or
477 (p_jurisdiction_code is not null and p_jurisdiction_code = p_bal_adj_jurisdiction_code))
478 and ((p_source_text is null) or
479 (p_source_text is not null and p_source_text = p_bal_adj_source_text))
480 and ((p_source_text2 is null) or
481 (p_source_text2 is not null and p_source_text2 = p_bal_adj_source_text2))
482 AND 1 = 1;
483
484 l_bal_type_id pay_balance_types.balance_type_id%TYPE;
485
486 -- Get the jurisdiction code from the original balance batch line
487 --
488 cursor csr_get_details(p_batch_line_id number)
489 is
490 select pbbl.source_id
491 ,pbbl.original_entry_id
492 ,pbbl.tax_unit_id
493 ,pbbl.jurisdiction_code
494 ,pbbl.source_text
495 ,pbbl.source_text2
496 from pay_balance_batch_lines pbbl
497 where pbbl.batch_line_id = p_batch_line_id;
498
499
500 -- Get jurisdiction code and original_entry_id for previously tested adjustments
501 CURSOR csr_get_tested_adjustments(p_test_batch_line_id NUMBER) IS
502 SELECT source_id
503 ,original_entry_id
504 ,tax_unit_id
505 ,jurisdiction_code
506 ,source_text
507 ,source_text2
508 FROM pay_temp_balance_adjustments
509 WHERE batch_line_id = p_test_batch_line_id;
510
511 -- The balance returned by the include check.
512 l_orig_entry_id pay_balance_batch_lines.original_entry_id%TYPE;
513 l_adj_orig_entry_id pay_temp_balance_adjustments.original_entry_id%TYPE;
514 l_source_id pay_balance_batch_lines.source_id%TYPE;
515 l_adj_source_id pay_temp_balance_adjustments.source_id%TYPE;
516 l_tax_unit_id pay_balance_batch_lines.tax_unit_id%TYPE;
517 l_adj_tax_unit_id pay_temp_balance_adjustments.tax_unit_id%TYPE;
518 l_source_text pay_balance_batch_lines.source_text%TYPE;
519 l_adj_source_text pay_temp_balance_adjustments.source_text%TYPE;
520 l_source_text2 pay_balance_batch_lines.source_text2%TYPE;
521 l_adj_source_text2 pay_temp_balance_adjustments.source_text2%TYPE;
522 l_jur_code pay_balance_batch_lines.jurisdiction_code%TYPE;
523 l_adj_jur_code pay_temp_balance_adjustments.jurisdiction_code%TYPE;
524 l_procedure VARCHAR2(250);
525 l_message VARCHAR2(250);
526
527 BEGIN
528 g_debug := hr_utility.debug_enabled;
529 l_procedure := g_package ||'include_adjustment';
530 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
531 IF (g_debug)
532 THEN
533 pay_in_utils.trace('**************************************************','********************');
534 pay_in_utils.trace('p_balance_type_id :',p_balance_type_id );
535 pay_in_utils.trace('p_dimension_name :',p_dimension_name );
536 pay_in_utils.trace('p_original_entry_id :',p_original_entry_id );
537 pay_in_utils.trace('p_upload_date :',p_upload_date );
538 pay_in_utils.trace('p_batch_line_id :',p_batch_line_id );
539 pay_in_utils.trace('p_test_batch_line_id :',p_test_batch_line_id);
540 pay_in_utils.trace('**************************************************','********************');
541 END IF;
542
543
544 OPEN csr_get_details(p_batch_line_id);
545 FETCH csr_get_details INTO l_source_id,
546 l_orig_entry_id,
547 l_tax_unit_id,
548 l_jur_code,
549 l_source_text,
550 l_source_text2;
551 CLOSE csr_get_details;
552 --
553
554 IF (g_debug)
555 THEN
556 pay_in_utils.trace('**************************************************','********************');
557 pay_in_utils.trace('l_source_id :',l_source_id );
558 pay_in_utils.trace('l_orig_entry_id :',l_orig_entry_id);
559 pay_in_utils.trace('l_tax_unit_id :',l_tax_unit_id );
560 pay_in_utils.trace('l_jur_code :',l_jur_code );
561 pay_in_utils.trace('l_source_text :',l_source_text );
562 pay_in_utils.trace('l_source_text2 :',l_source_text2 );
563 pay_in_utils.trace('**************************************************','********************');
564 END IF;
565
566 OPEN csr_get_tested_adjustments(p_test_batch_line_id);
567 FETCH csr_get_tested_adjustments
568 INTO l_adj_source_id,
569 l_adj_orig_entry_id,
570 l_adj_tax_unit_id,
571 l_adj_jur_code,
572 l_adj_source_text,
573 l_adj_source_text2;
574 CLOSE csr_get_tested_adjustments;
575
576 IF (g_debug)
577 THEN
578 pay_in_utils.trace('**************************************************','********************');
579 pay_in_utils.trace('l_adj_source_id :',l_adj_source_id );
580 pay_in_utils.trace('l_adj_orig_entry_id:',l_adj_orig_entry_id);
581 pay_in_utils.trace('l_adj_tax_unit_id :',l_adj_tax_unit_id );
582 pay_in_utils.trace('l_adj_jur_code :',l_adj_jur_code );
583 pay_in_utils.trace('l_adj_source_text :',l_adj_source_text );
584 pay_in_utils.trace('l_adj_source_text2 :',l_adj_source_text2 );
585 pay_in_utils.trace('**************************************************','********************');
586 END IF;
587 -- Does the balance adjustment effect the new balance ?
588
589 --hr_utility.trace('balance_type_id = '||TO_CHAR(p_balance_type_id));
590
591 --hr_utility.trace('jurisdiction_id = '||TO_CHAR(l_jurisdiction_code));
592 --hr_utility.trace('original_entry_id = '||TO_CHAR(p_original_entry_id));
593
594
595 -- hr_utility.trace('BA jurisdiction_id = '||TO_CHAR(l_adj_jurisdiction_code));
596 --hr_utility.trace('BA original_entry_id = '||TO_CHAR(l_adj_orig_entry_id));
597
598 OPEN csr_is_included(p_balance_type_id
599 ,l_source_id
600 ,p_original_entry_id
601 ,l_tax_unit_id
602 ,l_jur_code
603 ,l_source_text
604 ,l_source_text2
605 ,l_adj_tax_unit_id
606 ,l_adj_jur_code
607 ,l_adj_source_id
608 ,l_adj_orig_entry_id
609 ,l_adj_source_text
610 ,l_adj_source_text2
611 );
612 FETCH csr_is_included INTO l_bal_type_id;
613 CLOSE csr_is_included;
614
615 IF (g_debug)
616 THEN
617 pay_in_utils.trace('**************************************************','********************');
618 pay_in_utils.trace('l_bal_type_id :',l_bal_type_id);
619 pay_in_utils.trace('**************************************************','********************');
620 END IF;
621
622 --hr_utility.trace('Exiting pay_in_bal_upload.include_adjustment_test');
623
624 -- Adjustment does contribute to the new balance.
625
626 IF l_bal_type_id IS NOT NULL THEN
627 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 20);
628 RETURN (1); --TRUE
629
630 -- Adjustment does not contribute to the new balance.
631 ELSE
632 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 30);
633 RETURN (0); --FALSE
634
635 END IF;
636
637 EXCEPTION
638 WHEN OTHERS THEN
639 IF csr_is_included%ISOPEN THEN
640 CLOSE csr_is_included;
641 END IF;
642
643 IF csr_get_details%ISOPEN THEN
644 CLOSE csr_get_details;
645 END IF;
646
647 IF csr_get_tested_adjustments%ISOPEN THEN
648 CLOSE csr_get_tested_adjustments;
649 END IF;
650
651 l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR', 'FUNCTION:'||l_procedure, 'SQLERRMC:'||sqlerrm);
652 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 50);
653 pay_in_utils.trace(l_message,l_procedure);
654 pay_in_utils.trace('**************************************************','********************');
655 RAISE;
656 END include_adjustment;
657 --------------------------------------------------------------------------
658 -- --
659 -- Name : VALIDATE_BATCH_LINES --
660 -- Type : PROCEDURE --
661 -- Access : Public --
662 -- Description : Function to perform user-defined validation --
663 -- --
664 -- Parameters : --
665 -- IN : p_batch_id NUMBER --
666 -- OUT : N/A --
667 -- --
668 -- Change History : --
669 --------------------------------------------------------------------------
670 -- Rev# Date Userid Description --
671 --------------------------------------------------------------------------
672 -- 1.0 16-JUL-2004 lnagaraj Created this function --
673 --------------------------------------------------------------------------
674 PROCEDURE validate_batch_lines (p_batch_id NUMBER)
675 IS
676
677 BEGIN
678 NULL;
679 END validate_batch_lines;
680
681 END pay_in_bal_upload;
682