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