[Home] [Help]
PACKAGE BODY: APPS.AP_CALC_WITHHOLDING_PKG
Source
1 PACKAGE BODY AP_CALC_WITHHOLDING_PKG AS
2 /* $Header: apclawtb.pls 120.20.12010000.4 2008/09/09 10:10:44 njakkula ship $ */
3
4 -- ====================================================================
5 -- P R I V A T E - G L O B A L V A R I A B L E S
6 -- ====================================================================
7 -- BUG 7232736 : shifted the org_id declaration to the pachage spec
8 -- g_org_id NUMBER(15); /* Bug3700128. MOAC Project */
9
10 -- =====================================================================
11 -- P R I V A T E O B J E C T S
12 -- =====================================================================
13
14 FUNCTION Do_AWT_Cut_Off(
15 P_Awt_Date IN DATE,
16 P_Amount_Subject IN NUMBER,
17 P_Amount_Withheld IN NUMBER,
18 P_Vendor_Id IN NUMBER,
19 P_Tax_Name IN VARCHAR2,
20 P_Awt_Period_Name IN VARCHAR2,
21 P_Period_Limit IN NUMBER,
22 P_Calling_Sequence IN VARCHAR2)
23 RETURN NUMBER
24 IS
25 /*
26
27 NAME: Do_AWT_Cut_Off
28 FUNCTION: To cut off amounts to be withheld WHERE appropriate
29 HISTORY: atassoni.IT modIFied 04/25/95
30 (bucket manipulation extracted FROM this
31 code to make up the new PROCEDURE
32 H ANDLE_BUCKET; as a consequence, the
33 Manual hypothesis IS no more needed).
34 mhtaylor.UK created 04/17/95
35 NOTES:
36
37 Withholding DATE IS passed in to determine which period we are in.
38 Amount subject AND amount withheld are passed in to carry out the
39 actual calculation with. Vendor id AND tax name allow us to find
40 the appropriate bucket records. The NUMBER passed out IS the amount
41 to be withheld. If no cut off has been applied this will be the
42 same as the amount withheld passed in. */
43
44 l_awt_period_type ap_tax_codes.awt_period_type%TYPE;
45 l_awt_period_name ap_other_periods.period_name%TYPE
46 := P_Awt_Period_Name;
47 l_awt_period_limit ap_tax_codes.awt_period_limit%TYPE
48 := P_Period_Limit;
49 l_withheld_amount_to_date ap_awt_buckets.withheld_amount_to_date%TYPE;
50 l_amount_withheld NUMBER;
51
52 CURSOR c_get_bucket (PerName IN VARCHAR2)
53 IS
54 SELECT bk.withheld_amount_to_date
55 FROM ap_awt_buckets bk
56 WHERE bk.period_name = PerName
57 AND bk.tax_name = P_Tax_Name
58 AND bk.vendor_id = P_Vendor_Id;
59
60 DBG_Loc VARCHAR2(30) := 'Do_Awt_Cut_Off';
61 current_calling_sequence VARCHAR2(2000);
62 debug_info VARCHAR2(100);
63
64 BEGIN
65
66 current_calling_sequence := 'AP_CALC_WITHHOLDING_PKG.Do_AWT_Cut_Off<-' ||
67 P_Calling_Sequence;
68 IF ( (P_Amount_Withheld IS NULL)
69 OR
70 (P_Amount_Withheld = 0)) THEN
71 RETURN(0); -- Immediately RETURN ZERO IF withholding amount to
72 -- cut off IS NULLOR zero
73 END IF;
74
75 IF (l_awt_period_type = '' OR l_awt_period_limit = '') THEN
76 -- No period exists: do nothing AND RETURN the same amount withheld
77 RETURN (P_Amount_Withheld);
78 ELSE
79 -- Find the appropriate bucket record IF one exists.
80 debug_info := 'Open CURSOR c_get_bucket';
81
82 OPEN c_get_bucket(l_awt_period_name);
83 debug_info := 'Fetch CURSOR c_get_bucket';
84
85 FETCH c_get_bucket
86 INTO l_withheld_amount_to_date;
87
88 IF c_get_bucket%FOUND THEN
89
90 -- Does the bucket amount withheld so far WHEN added to the
91 -- package amount withheld before period limits have been
92 -- applied break the period limit ?
93 IF (P_Amount_Withheld + l_withheld_amount_to_date
94 > l_awt_period_limit) THEN
95 -- It does. Calculate actual amount to withhold
96 l_amount_withheld := l_awt_period_limit -
97 l_withheld_amount_to_date;
98 ELSE
99 -- It doesn't. Amount to withhold leaved unchanged.
100 l_amount_withheld := NVL(P_Amount_Withheld, 0);
101 END IF;
102 ELSE
103 -- Just checking that the amount withheld doesn't immediately
104 -- break the limit.
105 IF (P_Amount_Withheld > l_awt_period_limit) THEN
106 -- It does. Calculate actual amount to withhold.
107 l_amount_withheld := l_awt_period_limit;
108 ELSE
109 -- It doesn't. Amount to withhold leaved unchanged.
110 l_amount_withheld := NVL(P_Amount_Withheld, 0);
111 END IF;
112 END IF;
113
114 debug_info := 'Close CURSOR c_get_bucket';
115 CLOSE c_get_bucket;
116
117 -- End: Return the amount to be withheld after cut off
118 RETURN (l_amount_withheld);
119 END IF;
120
121 EXCEPTION
122 WHEN OTHERS THEN
123 IF (SQLCODE <> -20001) THEN
124 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
125 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
126 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
127 FND_MESSAGE.SET_TOKEN('PARAMETERS',
128 ' P_Awt_Date = ' || to_char(P_Awt_Date) ||
129 ', P_Amount_Subject = ' || to_char(P_Amount_Subject) ||
130 ', P_Amount_Withheld = ' || to_char(P_Amount_Withheld) ||
131 ', P_Vendor_Id = ' || to_char(P_Vendor_Id) ||
132 ', P_Tax_Name = ' || P_Tax_Name ||
133 ', P_Awt_Period_Name = ' || P_Awt_Period_Name ||
134 ', P_Period_limit = ' || to_char(P_Period_limit));
135
136 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
137 END IF;
138 APP_EXCEPTION.RAISE_EXCEPTION;
139 END Do_AWT_Cut_Off;
140
141 -- =====================================================================
142 -- P U B L I C O B J E C T S
143 -- =====================================================================
144
145
146 PROCEDURE Handle_Bucket (
147 P_Awt_Period_Name IN VARCHAR2,
148 P_Amount_Subject IN NUMBER,
149 P_Amount_Withheld IN NUMBER,
150 P_Vendor_Id IN NUMBER,
151 P_Tax_Name IN VARCHAR2,
152 P_Calling_Module IN VARCHAR2,
153 P_Last_Updated_By IN NUMBER,
154 P_Last_Update_Login IN NUMBER,
155 P_Program_Application_Id IN NUMBER,
156 P_Program_Id IN NUMBER,
157 P_Request_Id IN NUMBER,
158 P_Calling_Sequence IN VARCHAR2)
159 IS
160 DBG_Loc VARCHAR2(30) := 'Handle_Bucket';
161 dummy CHAR(1);
162 current_calling_sequence VARCHAR2(2000);
163 debug_info VARCHAR2(100);
164
165 CURSOR c_get_bucket IS
166 SELECT 'Y'
167 FROM ap_awt_buckets bk
168 WHERE bk.period_name = P_Awt_Period_Name
169 AND bk.tax_name = P_Tax_Name
170 AND bk.vendor_id = P_Vendor_Id
171 FOR UPDATE;
172
173 DO_NOT_UPDATE EXCEPTION;
174
175 BEGIN
176 current_calling_sequence := 'AP_CALC_WITHHOLDING_PKG.Handle_Bucket<-' ||
177 P_calling_sequence;
178
179 -- Check if UPDATE is allowed
180
181 IF (P_Calling_Module = 'INVOICE INQUIRY') THEN
182 RAISE DO_NOT_UPDATE;
183 END IF;
184
185 -- Find the appropriate bucket record IF one exists.
186 -- Ap_Logging_Pkg.Ap_Begin_Block (DBG_Loc);
187
188 debug_info := 'Open CURSOR to get buckets';
189 OPEN c_get_bucket;
190
191 debug_info := 'Fetch FROM CURSOR to get buckets';
192 FETCH c_get_bucket INTO dummy;
193
194 IF c_get_bucket%FOUND THEN
195
196 -- Update existing bucket
197 debug_info := 'Update existing bucket';
198
199 UPDATE ap_awt_buckets
200 SET gross_amount_to_date = gross_amount_to_date +
201 NVL(P_Amount_Subject, 0),
202 withheld_amount_to_date = withheld_amount_to_date +
203 NVL(P_Amount_Withheld, 0),
204 last_update_date = SYSDATE,
205 last_updated_by = P_Last_Updated_By,
206 last_update_login = P_Last_Update_Login,
207 program_update_date = SYSDATE,
208 program_application_id = P_Program_Application_Id,
209 program_id = P_Program_Id,
210 request_id = P_Request_Id
211 WHERE CURRENT OF c_get_bucket;
212 ELSE
213 -- Create new bucket
214 debug_info := 'Create new bucket';
215
216 INSERT INTO ap_awt_buckets
217 (period_name
218 ,tax_name
219 ,vendor_id
220 ,withheld_amount_to_date
221 ,gross_amount_to_date
222 ,last_update_date
223 ,last_updated_by
224 ,last_update_login
225 ,creation_date
226 ,created_by
227 ,program_update_date
228 ,program_application_id
229 ,program_id
230 ,request_id
231 ,org_id /* Bug 3700128. MOAC Project */
232 )
233 VALUES (P_Awt_Period_Name
234 ,P_Tax_Name
235 ,P_Vendor_Id
236 ,NVL(P_Amount_Withheld, 0)
237 ,NVL(P_Amount_Subject, 0)
238 ,SYSDATE
239 ,P_Last_Updated_By
240 ,P_Last_Update_Login
241 ,SYSDATE
242 ,P_Last_Updated_By
243 ,SYSDATE
244 ,P_Program_Application_Id
245 ,P_Program_Id
246 ,P_Request_Id
247 ,g_org_id); /* Bug 3700128. MOAC Project */
248 END IF;
249
250 debug_info := 'Close CURSOR to get buckets';
251 CLOSE c_get_bucket;
252
253 EXCEPTION
254 WHEN DO_NOT_UPDATE THEN
255 NULL;
256 WHEN OTHERS THEN
257 IF (SQLCODE <> -20001) THEN
258 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
259 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
260 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
261 FND_MESSAGE.SET_TOKEN('PARAMETERS',
262 ' P_Awt_Period_Name = ' || P_Awt_Period_Name ||
263 ', P_Amount_Subject = ' || to_char(P_Amount_Subject) ||
264 ', P_Amount_Withheld = ' || to_char(P_Amount_Withheld) ||
265 ', P_Vendor_Id = ' || to_char(P_Vendor_Id) ||
266 ', P_Tax_Name = ' || P_Tax_Name ||
267 ', P_Calling_Module = ' || P_Calling_Module);
268 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
269 END IF;
270 APP_EXCEPTION.RAISE_EXCEPTION;
271
272 END Handle_Bucket;
273
274
275 PROCEDURE Insert_Temp_Distribution(
276 InvoiceId IN NUMBER,
277 SuppId IN NUMBER,
278 PaymentNum IN NUMBER,
279 GroupId IN NUMBER,
280 TaxName IN VARCHAR2,
281 CodeCombinationId IN NUMBER,
282 GrossAmount IN NUMBER,
283 WithheldAmount IN NUMBER,
284 AwtDate IN DATE,
285 GLPeriodName IN VARCHAR2,
286 AwtPeriodType IN VARCHAR2,
287 AwtPeriodName IN VARCHAR2,
288 -- P_Awt_Related_Id IN NUMBER DEFAULT NULL, --Bug 6168793
289 CheckrunName IN VARCHAR2,
290 WithheldRateId IN NUMBER,
291 ExchangeRate IN NUMBER,
292 CurrCode IN VARCHAR2,
293 BaseCurrCode IN VARCHAR2,
294 auto_offset_segs IN VARCHAR2,
295 P_Calling_Sequence IN VARCHAR2,
296 HandleBucket IN VARCHAR2 DEFAULT 'N',
297 LastUpdatedBy IN NUMBER DEFAULT NULL,
298 LastUpdateLogin IN NUMBER DEFAULT NULL,
299 ProgramApplicationId IN NUMBER DEFAULT NULL,
300 ProgramId IN NUMBER DEFAULT NULL,
301 RequestId IN NUMBER DEFAULT NULL,
302 CallingModule IN VARCHAR2 DEFAULT NULL,
303 P_Invoice_Payment_Id IN NUMBER DEFAULT NULL,
304 invoice_exchange_rate IN NUMBER DEFAULT NULL,
305 GLOBAL_ATTRIBUTE_CATEGORY IN VARCHAR2 DEFAULT NULL,
306 GLOBAL_ATTRIBUTE1 IN VARCHAR2 DEFAULT NULL,
307 GLOBAL_ATTRIBUTE2 IN VARCHAR2 DEFAULT NULL,
308 GLOBAL_ATTRIBUTE3 IN VARCHAR2 DEFAULT NULL,
309 GLOBAL_ATTRIBUTE4 IN VARCHAR2 DEFAULT NULL,
310 GLOBAL_ATTRIBUTE5 IN VARCHAR2 DEFAULT NULL,
311 GLOBAL_ATTRIBUTE6 IN VARCHAR2 DEFAULT NULL,
312 GLOBAL_ATTRIBUTE7 IN VARCHAR2 DEFAULT NULL,
313 GLOBAL_ATTRIBUTE8 IN VARCHAR2 DEFAULT NULL,
314 GLOBAL_ATTRIBUTE9 IN VARCHAR2 DEFAULT NULL,
315 GLOBAL_ATTRIBUTE10 IN VARCHAR2 DEFAULT NULL,
316 GLOBAL_ATTRIBUTE11 IN VARCHAR2 DEFAULT NULL,
317 GLOBAL_ATTRIBUTE12 IN VARCHAR2 DEFAULT NULL,
318 GLOBAL_ATTRIBUTE13 IN VARCHAR2 DEFAULT NULL,
319 GLOBAL_ATTRIBUTE14 IN VARCHAR2 DEFAULT NULL,
320 GLOBAL_ATTRIBUTE15 IN VARCHAR2 DEFAULT NULL,
321 GLOBAL_ATTRIBUTE16 IN VARCHAR2 DEFAULT NULL,
322 GLOBAL_ATTRIBUTE17 IN VARCHAR2 DEFAULT NULL,
323 GLOBAL_ATTRIBUTE18 IN VARCHAR2 DEFAULT NULL,
324 GLOBAL_ATTRIBUTE19 IN VARCHAR2 DEFAULT NULL,
325 GLOBAL_ATTRIBUTE20 IN VARCHAR2 DEFAULT NULL,
326 p_checkrun_id in number default null,
327 P_Awt_Related_Id IN NUMBER DEFAULT NULL --bug6524425
328 )
329 IS
330 base_WT_amount NUMBER;
331 Withheld_Amt NUMBER;
332 DBG_Loc VARCHAR2(30) := 'Insert_Temp_Distribution';
333
334 current_calling_sequence VARCHAR2(2000);
335 debug_info VARCHAR2(100);
336 l_reason VARCHAR2(100);
337 l_proration_divisor NUMBER;
338 l_proration_base_divisor NUMBER;
339 l_awt_related_id NUMBER(15);
340
341 CURSOR c_prorate_awt_lines (
342 P_invoice_id IN NUMBER,
343 proration_divisor IN NUMBER,
344 P_awt_group_id IN NUMBER,
345 P_exchange_rate IN NUMBER,
346 P_base_curr_code IN VARCHAR2,
347 P_tax_name IN VARCHAR2,
348 withheld_amt IN NUMBER,
349 Proration_base_divisor IN NUMBER)
350 IS
351 SELECT (aid.amount * Withheld_amt / proration_divisor) prorated_awt_amt,
352 (NVL(aid.base_amount,amount) * Withheld_amt/ proration_base_divisor) prorated_base_awt_amt,
353 invoice_distribution_id
354 FROM ap_invoice_distributions AID
355 WHERE aid.invoice_id = p_invoice_id
356 --Bug 7217385 modified the below line
357 --AND aid.line_type_lookup_code NOT IN ('AWT','PREPAY')
358 AND aid.line_type_lookup_code <> 'AWT'
359 --Bug6660355
360 AND (( aid.awt_group_id IS NOT NULL
361 AND aid.awt_group_id = p_awt_group_id)
362 OR
363 ( aid.pay_awt_group_id IS NOT NULL
364 AND aid.pay_awt_group_id = p_awt_group_id));
365
366
370 l_total_base_pro_withheld_amt NUMBER := 0;
367 rec_prorate_awt_lines c_prorate_awt_lines%ROWTYPE;
368
369 l_total_pro_withheld_amt NUMBER := 0;
371 l_prorated_withheld_amt NUMBER;
372 l_prorated_base_withheld_amt NUMBER;
373 l_round_withheld_amt NUMBER;
374 l_round_base_withheld_amt NUMBER;
375
376 BEGIN
377
378 current_calling_sequence := 'AP_CALC_WITHHOLDING_PKG.<-Insert_Temp_Distribution' ||
379 P_Calling_Sequence;
380
381
382 /* Bug 4743558. We need to populate the g_org_id in this procedure since this
383 procedure is called by JG directly and in such cases the g_org_id is not
384 populated causing security policy violation error */
385
386 IF g_org_id IS NULL THEN
387
388 SELECT org_id
389 INTO g_org_id
390 FROM AP_Invoices
391 WHERE Invoice_ID = InvoiceId;
392
393 END IF;
394
395
396 /* bug3589682 we always pass withheld amt in base currency code only
397 Values passed to CurrCode and BaseCurrCode is always base currency code
398 convertion is not required. Hence delete the if condition below */
399 /* Bug 4721994 commented the below code as rounding should be done after prorating the awt amount*/
400 -- base_WT_amount := Ap_Utilities_Pkg.ap_round_currency(WithheldAmount,BaseCurrCode); -- R11: Xcurr
401
402 base_WT_amount := WithheldAmount; --Bug 4721994
403
404
405 Withheld_Amt := WithheldAmount;
406
407 IF Ap_Extended_Withholding_Pkg.Ap_Extended_Withholding_Active THEN
408
409 AP_CUSTOM_WITHHOLDING_PKG.Ap_Special_Withheld_Amt (
410 Withheld_Amt,
411 base_WT_amount,
412 CurrCode,
413 BaseCurrCode,
414 Invoice_exchange_rate,
415 TaxName,
416 P_Calling_Sequence );
417 END IF;
418
419 IF (NOT Ap_ExtENDed_Withholding_Pkg.Ap_ExtENDed_Withholding_Active) THEN
420
421 SELECT SUM(NVL(AID.amount,0)),
422 SUM(NVL(NVL(AID.base_amount,aid.amount),0))
423 INTO l_proration_divisor,
424 l_proration_base_divisor
425 FROM ap_invoice_distributions AID
426 WHERE aid.invoice_id = Invoiceid
427 --Bug 7217385 modified the below line
428 --AND aid.line_type_lookup_code NOT IN ('AWT','PREPAY')
429 AND aid.line_type_lookup_code <> 'AWT'
430 --Bug6660355
431 AND (( aid.awt_group_id IS NOT NULL
432 AND aid.awt_group_id = groupid)
433 OR
434 (aid.pay_awt_group_id IS NOT NULL
435 AND aid.pay_awt_group_id = groupid));
436
437 OPEN c_prorate_awt_lines(InvoiceId,
438 l_proration_divisor,
439 groupid,
440 ExchangeRate,
441 BaseCurrCode,
442 TaxName,
443 Withheld_amt,
444 l_proration_base_divisor);
445
446 LOOP
447 FETCH c_prorate_awt_lines INTO rec_prorate_awt_lines ;
448
449 EXIT WHEN c_prorate_awt_lines%NOTFOUND;
450
451 l_prorated_withheld_amt := rec_prorate_awt_lines.prorated_awt_amt; --bug 3589682
452
453 l_prorated_base_withheld_amt := ap_utilities_pkg.ap_round_currency(
454 rec_prorate_awt_lines.prorated_base_awt_amt,BaseCurrCode);
455
456 l_total_pro_withheld_amt := l_total_pro_withheld_amt +
457 l_prorated_withheld_amt;
458 l_total_base_pro_withheld_amt := l_total_base_pro_withheld_amt +
459 l_prorated_base_withheld_amt;
460
461 l_awt_related_id := rec_prorate_awt_lines.invoice_distribution_id;
462
463 AP_CUSTOM_WITHHOLDING_PKG.Ap_Special_Withheld_Amt (
464 l_prorated_withheld_amt,
465 l_prorated_base_withheld_amt,
466 CurrCode,
467 BaseCurrCode,
468 Invoice_exchange_rate,
469 TaxName,
470 P_Calling_Sequence);
471
472 debug_info := 'Insert INTO ap_awt_temp_distributions';
473
474 INSERT INTO ap_awt_temp_distributions_all
475 (invoice_id
476 ,payment_num
477 ,group_id
478 ,tax_name
479 ,tax_code_combination_id
480 ,gross_amount
481 ,withholding_amount
482 ,base_withholding_amount
483 ,accounting_date
484 ,period_name
485 ,checkrun_name
486 ,tax_rate_id
487 ,invoice_payment_id
488 ,awt_related_id
489 ,GLOBAL_ATTRIBUTE_CATEGORY
490 ,GLOBAL_ATTRIBUTE1
491 ,GLOBAL_ATTRIBUTE2
492 ,GLOBAL_ATTRIBUTE3
493 ,GLOBAL_ATTRIBUTE4
494 ,GLOBAL_ATTRIBUTE5
495 ,GLOBAL_ATTRIBUTE6
496 ,GLOBAL_ATTRIBUTE7
497 ,GLOBAL_ATTRIBUTE8
498 ,GLOBAL_ATTRIBUTE9
499 ,GLOBAL_ATTRIBUTE10
500 ,GLOBAL_ATTRIBUTE11
504 ,GLOBAL_ATTRIBUTE15
501 ,GLOBAL_ATTRIBUTE12
502 ,GLOBAL_ATTRIBUTE13
503 ,GLOBAL_ATTRIBUTE14
505 ,GLOBAL_ATTRIBUTE16
506 ,GLOBAL_ATTRIBUTE17
507 ,GLOBAL_ATTRIBUTE18
508 ,GLOBAL_ATTRIBUTE19
509 ,GLOBAL_ATTRIBUTE20
510 ,ORG_ID /* bug 3700128. MOAC Project */
511 ,checkrun_id)
512 VALUES
513 (InvoiceId
514 ,PaymentNum
515 ,GroupId
516 ,TaxName
517 ,CodeCombinationId
518 ,GrossAmount
519 ,l_prorated_withheld_amt
520 ,l_prorated_base_withheld_amt
521 ,AwtDate
522 ,GLPeriodName
523 ,CheckrunName
524 ,WithheldRateId
525 ,P_Invoice_Payment_Id
526 ,l_awt_related_id
527 ,GLOBAL_ATTRIBUTE_CATEGORY
528 ,GLOBAL_ATTRIBUTE1
529 ,GLOBAL_ATTRIBUTE2
530 ,GLOBAL_ATTRIBUTE3
531 ,GLOBAL_ATTRIBUTE4
532 ,GLOBAL_ATTRIBUTE5
533 ,GLOBAL_ATTRIBUTE6
534 ,GLOBAL_ATTRIBUTE7
535 ,GLOBAL_ATTRIBUTE8
536 ,GLOBAL_ATTRIBUTE9
537 ,GLOBAL_ATTRIBUTE10
538 ,GLOBAL_ATTRIBUTE11
539 ,GLOBAL_ATTRIBUTE12
540 ,GLOBAL_ATTRIBUTE13
541 ,GLOBAL_ATTRIBUTE14
542 ,GLOBAL_ATTRIBUTE15
543 ,GLOBAL_ATTRIBUTE16
544 ,GLOBAL_ATTRIBUTE17
545 ,GLOBAL_ATTRIBUTE18
546 ,GLOBAL_ATTRIBUTE19
547 ,GLOBAL_ATTRIBUTE20
548 ,g_org_id /* Bug 3700128. MOAC Project */
549 ,p_checkrun_id);
550
551 END LOOP;
552
553 CLOSE c_prorate_awt_lines;
554
555 -- We need to update the last AWT_TEMP_DISTRIBUTION with any
556 -- rounding difference.
557
558 l_round_withheld_amt := withheld_amt -
559 l_total_pro_withheld_amt;
560
561 l_round_base_withheld_amt := base_WT_amount -
562 l_total_base_pro_withheld_amt;
563
564 IF NVL(l_round_withheld_amt,0) <> 0 OR
565 NVL(l_round_base_withheld_amt,0) <> 0 THEN
566
567 UPDATE ap_awt_temp_distributions_all
568 SET withholding_amount = (withholding_amount +
569 l_round_withheld_amt),
570 base_withholding_amount = (base_withholding_amount +
571 l_round_base_withheld_amt)
572 WHERE invoice_id = InvoiceId
573 AND payment_num = PaymentNum
574 AND group_id = GroupId
575 AND tax_name = TaxName
576 AND tax_code_combination_id = CodeCombinationId
577 AND gross_amount = GrossAmount
578 AND withholding_amount = l_prorated_withheld_amt
579 AND base_withholding_amount = l_prorated_base_withheld_amt
580 AND accounting_date = AwtDate
581 AND period_name = GLPeriodName
582 AND checkrun_name = CheckrunName
583 AND tax_rate_id = WithheldRateId
584 AND invoice_payment_id = P_Invoice_Payment_Id
585 AND checkrun_id = p_checkrun_id
586 AND awt_related_id = l_awt_related_id;
587
588 END IF;
589
590 --bugfix:4716059
591 ELSE
592 /* Bug 4721994 Prorating not done here. so rounding the base_wt_amount*/
593 base_WT_amount := Ap_Utilities_Pkg.ap_round_currency(WithheldAmount,BaseCurrCode);
594
595
596 debug_info := 'Insert into ap_awt_temp_distributions';
597 insert into ap_awt_temp_distributions_all
598 (invoice_id
599 ,payment_num
600 ,group_id
601 ,tax_name
602 ,tax_code_combination_id
603 ,gross_amount
604 ,withholding_amount
605 ,base_withholding_amount
606 ,accounting_date
607 ,period_name
608 ,checkrun_name
609 ,tax_rate_id
610 ,invoice_payment_id
611 ,awt_related_id --Added Bug 6168793
612 ,GLOBAL_ATTRIBUTE_CATEGORY
613 ,GLOBAL_ATTRIBUTE1
614 ,GLOBAL_ATTRIBUTE2
615 ,GLOBAL_ATTRIBUTE3
616 ,GLOBAL_ATTRIBUTE4
617 ,GLOBAL_ATTRIBUTE5
618 ,GLOBAL_ATTRIBUTE6
619 ,GLOBAL_ATTRIBUTE7
620 ,GLOBAL_ATTRIBUTE8
621 ,GLOBAL_ATTRIBUTE9
622 ,GLOBAL_ATTRIBUTE10
623 ,GLOBAL_ATTRIBUTE11
624 ,GLOBAL_ATTRIBUTE12
625 ,GLOBAL_ATTRIBUTE13
626 ,GLOBAL_ATTRIBUTE14
627 ,GLOBAL_ATTRIBUTE15
628 ,GLOBAL_ATTRIBUTE16
629 ,GLOBAL_ATTRIBUTE17
630 ,GLOBAL_ATTRIBUTE18
631 ,GLOBAL_ATTRIBUTE19
632 ,GLOBAL_ATTRIBUTE20
633 ,ORG_ID
634 ,CHECKRUN_ID
635 )
636 values
637 (InvoiceId
638 ,PaymentNum
639 ,GroupId
640 ,TaxName
644 ,base_WT_amount
641 ,CodeCombinationId
642 ,GrossAmount
643 ,Withheld_Amt
645 ,AwtDate
646 ,GLPeriodName
647 ,CheckrunName
648 ,WithheldRateId
649 ,P_Invoice_Payment_Id
650 ,P_Awt_Related_Id --Added Bug 6168793
651 ,GLOBAL_ATTRIBUTE_CATEGORY
652 ,GLOBAL_ATTRIBUTE1
653 ,GLOBAL_ATTRIBUTE2
654 ,GLOBAL_ATTRIBUTE3
655 ,GLOBAL_ATTRIBUTE4
656 ,GLOBAL_ATTRIBUTE5
657 ,GLOBAL_ATTRIBUTE6
658 ,GLOBAL_ATTRIBUTE7
659 ,GLOBAL_ATTRIBUTE8
660 ,GLOBAL_ATTRIBUTE9
661 ,GLOBAL_ATTRIBUTE10
662 ,GLOBAL_ATTRIBUTE11
663 ,GLOBAL_ATTRIBUTE12
664 ,GLOBAL_ATTRIBUTE13
665 ,GLOBAL_ATTRIBUTE14
666 ,GLOBAL_ATTRIBUTE15
667 ,GLOBAL_ATTRIBUTE16
668 ,GLOBAL_ATTRIBUTE17
669 ,GLOBAL_ATTRIBUTE18
670 ,GLOBAL_ATTRIBUTE19
671 ,GLOBAL_ATTRIBUTE20
672 ,G_ORG_ID
673 ,P_CHECKRUN_ID --4759533
674 );
675
676
677 END IF;
678
679 IF (HandleBucket = 'Y' AND
680 AwtPeriodType IS NOT NULL) THEN
681
682 Handle_Bucket (
683 AwtPeriodName,
684 GrossAmount,
685 WithheldAmount,
686 SuppId,
687 TaxName,
688 CallingModule,
689 LastUpdatedBy,
690 LastUpdateLogin,
691 ProgramApplicationId,
692 ProgramId,
693 RequestId,
694 current_calling_sequence);
695 END IF;
696
697 EXCEPTION
698 WHEN OTHERS THEN
699 IF (SQLCODE <> -20001) THEN
700 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
701 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
702 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
703 FND_MESSAGE.SET_TOKEN('PARAMETERS',
704 ' Invoice Id = ' || to_char(InvoiceId) ||
705 ', Supplier Id = ' || to_char(SuppId) ||
706 ', Payment Num = ' || to_char(PaymentNum) ||
707 ', Group Id = ' || to_char(GroupId) ||
708 ', Tax Name = ' || TaxName ||
709 ', CodeCombinationId = ' || to_char(CodeCombinationId));
710
711 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
712 END IF;
713 APP_EXCEPTION.RAISE_EXCEPTION;
714
715 END Insert_Temp_Distribution;
716
717
718 PROCEDURE AP_Calculate_AWT_Amounts (
719 P_Invoice_Id IN NUMBER,
720 P_Awt_Date IN DATE,
721 P_Calling_Module IN VARCHAR2,
722 P_Create_Dists IN VARCHAR2,
723 P_Amount IN NUMBER,
724 P_Payment_Num IN NUMBER,
725 P_Checkrun_Name IN VARCHAR2,
726 P_Last_Updated_By IN NUMBER,
727 P_Last_Update_Login IN NUMBER,
728 P_Program_Application_Id IN NUMBER,
729 P_Program_Id IN NUMBER,
730 P_Request_Id IN NUMBER,
731 P_AWT_Success IN OUT NOCOPY VARCHAR2,
732 P_Calling_Sequence IN VARCHAR2,
733 P_Invoice_Payment_Id IN NUMBER DEFAULT NULL,
734 P_checkrun_id in number default null,
735 p_org_id in number default null) --4742265
736 IS
737 /*
738
739 Copyright (c) 1995 by Oracle Corporation
740
741 NAME
742 AP_Calculate_AWT_Amounts - First Unit of general Ap_Do_Withholding
743 DESCRIPTION
744 Calculate WT amounts that will be stored in temporary distributions
745 NOTES
746 This PROCEDURE IS part of the AP_AWT PL/SQL package
747 HISTORY (YY/MM/DD)
748 atassoni.it 95/04/26 Code refinements
749 mhtaylor.uk atassoni.it 95/04/21 First executable version
750 atassoni.it 95/04/12 Creation
751
752 << Beginning of AP_Calculate_AWT_Amounts program documentation >>
753
754 Flow chart of this PROCEDURE:
755
756 *--------------------------------*
757 | BEGIN AP_Calculate_AWT_Amounts |
758 *--------------------------------*
759 |
760 v
761 *----------------------------------------------------------------------------*
762 | Set up withholding environment: |
763 | - Get INVOICE basic information and supplier identification |
764 | - Get GROUP AMOUNTS and set number of GROUPS found |
765 | - Calculate TOTAL INVOICE AMOUNT from distribution lines |
766 | - Adjust figures for possible discount on invoice |
767 | - Recalculate the amounts subject to WT in proportion to the payment |
768 | - Set starting group FOR WT calculation, excluding the exempt if existent |
769 *----------------------------------------------------------------------------*
770 |
774 | |
771 | *---------------------------------*
772 +->+-> | Loop for each withholding Group |
773 | *---------------------------------*
775 | v
776 | *-------------------------------------------------------*
777 | | Reset amount subject for tax, rank and rank cumulator |
778 | *-------------------------------------------------------*
779 | |
780 | | *----------------------------*
781 | +----->+-> | Loop for each tax in group |
782 | | *----------------------------*
783 | | |
784 | | v
785 | | *----------------------------------------*
786 | | | Check if tax has valid characteristics |
787 | | *----------------------------------------*
788 | | |
789 | | v
790 | | *------------------------------------------------------------*
791 | | | (Re-)Calculate rank, cumulator and amount subject FOR tax |
792 | | *------------------------------------------------------------*
793 | | |
794 | | v
795 | | *--------------------------------------*
796 | | | Check for CUSTOM withholding figures | ===> goto next tax
797 | | *--------------------------------------* when found
798 | | |
799 | | v
800 | | *-------------------------------------------------*
801 | | | Get withholding figures FOR EXCEPTION AND apply |
802 | | | Cut Off (VALUES could be NULL) |
803 | | *-------------------------------------------------*
804 | | |
805 | | v
806 | | *-----------------------------------------*
807 | | +--| Get withholding figures FOR CERTIFICATE |
808 | | | | (VALUES could be NULL) |
809 | | | *-----------------------------------------*
810 | | |
811 | | +--> Withholding Tax Rate FOR Certificate IS not NULL?
812 | |
813 | | ,'`. *--------------------------------------------*
814 | | +-- < IF > ------> | Calculate proper WT amount FOR CERTIFICATE |
815 | | | `.,' Yes | applying Cut Off |
816 | | | No *--------------------------------------------*
817 | | v |
818 | | *----------------------------* | *----------------------------*
819 | | | Set to NULL the WT | +--> | Confirm WT Rate AND Amount |
820 | | | amount FOR CERTIFICATE | | (the greater between |
821 | | *----------------------------* | CertIFicate AND Exception) |
822 | | | *----------------------------*
823 | | v |
824 | | *----------------------------* |
825 | | | SAVEPOINT | |
826 | | | ~~~~~~~~~ | |
827 | | | BEFORE the AMOUNT RANGES | |
828 | | | have been processed | |
829 | | *----------------------------* |
830 | | | |
831 | | v |
832 | | *-----------------------------* |
833 | | | Get withholding figures FOR | |
834 | | | AMOUNT RANGES, manipulating | |
835 | | | the database IF necessary | v
836 | | *-----------------------------* *-------------------------------*
837 | | | | Insert Temporary Distribution |
838 | | | | AND Update Bucket |
839 | | +--> Were RANGES applicable? *-------------------------------*
840 | | |
841 | | ,'`. |
842 | | +--- < IF > -----------+ |
843 | | | No `.,' Yes | |
844 | | | v |
845 | | v *------------------------------------* |
846 | | *------------------* | Single amounts withheld on RANGES | |
847 | | | Calculate amount | | have already been INSERTed as tem- | |
848 | | | withheld in the | | porary distributions, with bucket, | |
849 | | | normal case, | | while getting withholding figures | |
850 | | | round AND apply | | FOR ranges (two steps above) | |
851 | | | CUT OFF | *------------------------------------* |
852 | | *------------------* | |
853 | | | +--> WT Amount withheld FOR Ranges |
854 | | v IS less THEN |
855 | | *------------------* WT Amount withheld FOR Exception? |
856 | | | Confirm WT Rate | |
857 | | | AND Amount (the | ,'`. *---------------------* |
858 | | | greater between | < IF > ---> | Undo changes due to | |
859 | | | Normal AND Ex- | `.,' Yes | ranges (rollback to | |
863 | | | | v |
860 | | | ception) | | | the savepoint) | |
861 | | *------------------* | No *---------------------* |
862 | | | | | |
864 | | v | *---------------------* |
865 | | *------------------* | | Insert Temporary | |
866 | | | Insert Temporary | | | Distribution with | |
867 | | | Distribution AND | | | EXCEPTION data THEN | |
868 | | | Update Bucket | | | Update Bucket | |
869 | | *------------------* | *---------------------* |
870 | | | | | |
871 | | | | v |
872 | | | +----------->+ |
873 | | | | |
874 | | +<--------------------------------------+<--------------------+
875 | | |
876 | | +--> Is there another Tax in this Group?
877 | |
878 | | ,'`.
879 | | Yes ,' `.
880 | +------- <End Loop>
881 | `. ,'
882 | `.,'
883 | No |
884 | +--> Is there another Withholding Group FOR this invoice?
885 |
886 | ,'`.
887 | Yes ,' `.
888 +--------------- <End Loop>
889 `. ,'
890 `.,'
891 No |
892 v
893 *------------------------------*
894 | END AP_Calculate_AWT_Amounts |
895 *------------------------------*
896
897
898 << End of AP_Calculate_AWT_Amounts program documentation >>
899
900 */
901
902 -- PL/SQL Main Block Constants AND Variables:
903
904 currency_code ap_invoices.invoice_currency_code%TYPE;
905 payment_currency_code ap_invoices.payment_currency_code%TYPE;
906 payment_cross_rate ap_invoices.payment_cross_rate%TYPE;
907 FUNCTIONal_currency ap_system_parameters.base_currency_code%TYPE;
908 invoice_exchange_rate ap_invoices.exchange_rate%TYPE;
909 invoice_number ap_invoices.invoice_num%TYPE;
910 supplier_id ap_invoices.vendor_id%TYPE;
911 supplier_site_id ap_invoices.vendor_site_id%TYPE;
912 NUMBER_of_awt_groups integer := 0;
913 gl_period_name ap_invoice_distributions.period_name%TYPE;
914 gl_awt_date DATE;
915
916 -- Invalid Situations Variables
917 invalid_group ap_awt_groups.group_id%TYPE;
918 invalid_tax ap_tax_codes.name%TYPE;
919
920 -- PL/SQL debugging/logging Objects:
921
922 DBG_Loc VARCHAR2(30) := 'AP_Calculate_AWT_Amounts';
923
924 -- see also PROCEDURE 'Log' below
925 current_calling_sequence VARCHAR2(2000);
926 debug_info VARCHAR2(100);
927
928 -- PL/SQL Main Block Exceptions:
929
930 NOT_AN_OPEN_GL_PERIOD EXCEPTION;
931 ONE_TAX_MISSING_PERIOD EXCEPTION;
932 ONE_INVALID_GROUP EXCEPTION;
933 ONE_INVALID_TAX EXCEPTION;
934 ONE_INVALID_TAX_ACCOUNT EXCEPTION;
935 NO_VALID_TAX_RATES EXCEPTION;
936 INVALID_RANGE_DATES EXCEPTION;
937 INVALID_RANGE EXCEPTION;
938 ALL_GROUPS_ZERO EXCEPTION;
939 INV_CURR_MUST_BE_BASE EXCEPTION;
940
941 -- PL/SQL Main Block Tables:
942
943 TYPE Group_Id_TabTyp IS
944 TABLE OF ap_awt_groups.group_id%TYPE
945 INDEX BY binary_integer;
946 tab_group_id Group_Id_TabTyp;
947
948 TYPE Amount_By_Group_TabTyp IS
949 TABLE OF NUMBER
950 INDEX BY BINARY_INTEGER;
951 tab_amount_by_group Amount_By_Group_TabTyp;
952
953 TYPE Vat_By_Group_TabTyp IS
954 TABLE OF NUMBER
955 INDEX BY BINARY_INTEGER;
956 tab_vat_by_group Vat_By_Group_TabTyp;
957
958 -- PL/SQL Main Block CURSORs AND records:
959 -- Not Defined
960
961 -- AP_Calculate_AWT_Amounts:
962 -- PL/SQL Main Block PROCEDUREs AND FUNCTIONs:
963
964 FUNCTION Proportional_Amount (
965 Amount IN NUMBER,
966 Numerator IN NUMBER,
967 Denominator IN NUMBER,
968 CurrCode IN VARCHAR2,
969 P_Calling_Sequence IN VARCHAR2)
970 RETURN NUMBER
971 IS
972 proportional_value NUMBER;
973 current_calling_sequence VARCHAR2(2000);
974 debug_info VARCHAR2(100);
975 BEGIN
976 current_calling_sequence := 'AP_CALC_WITHHOLDING_PKG.Proportional_Amount<-' ||
977 P_Calling_Sequence;
978
979 proportional_value := Amount; -- Bug7043937
980
981 if Denominator <> 0 then -- Bug7043937
982
983 proportional_value := (Amount * (Numerator / Denominator));
984
985 end if;
986
987 -- proportional_value := Ap_Utilities_Pkg.Ap_Round_Currency
988 -- (Amount * (Numerator / Denominator) ,CurrCode);
989 RETURN(proportional_value);
993 GroupId IN NUMBER,
990 END Proportional_Amount;
991
992 FUNCTION Get_Group_Name (
994 P_Calling_Sequence IN VARCHAR2)
995 RETURN VARCHAR2
996 IS
997 CURSOR c_group_name IS
998 SELECT name
999 FROM ap_awt_groups
1000 WHERE (group_id = GroupId);
1001
1002 group_name ap_awt_groups.name%TYPE;
1003 current_calling_sequence VARCHAR2(2000);
1004 debug_info VARCHAR2(100);
1005 BEGIN
1006 current_calling_sequence := 'AP_CALC_WITHHOLDING_PKG.Get_Group_Name<-' ||
1007 P_Calling_Sequence;
1008 debug_info := 'Open CURSOR c_group_name';
1009 OPEN c_group_name;
1010
1011 debug_info := 'Fetch CURSOR c_group_name';
1012 FETCH c_group_name INTO group_name;
1013 debug_info := 'Close CURSOR c_group_name';
1014
1015 CLOSE c_group_name;
1016 RETURN(group_name);
1017 EXCEPTION
1018 WHEN OTHERS THEN
1019 IF (SQLCODE <> -20001) THEN
1020 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1021 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1022 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
1023 FND_MESSAGE.SET_TOKEN('PARAMETERS',
1024 'Group_Id = ' || to_char(GroupId));
1025
1026 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
1027 END IF;
1028 APP_EXCEPTION.RAISE_EXCEPTION;
1029 END Get_Group_Name;
1030
1031 FUNCTION GL_Account_INVALID (
1032 CodeCombinationId IN NUMBER,
1033 AccountingDate IN DATE,
1034 P_Calling_Sequence IN VARCHAR2)
1035 RETURN BOOLEAN
1036 IS
1037 CURSOR c_test_acct_id IS
1038 SELECT detail_posting_allowed_flag,
1039 start_date_active,
1040 end_date_active,
1041 template_id,
1042 enabled_flag,
1043 summary_flag
1044 FROM gl_code_combinations
1045 WHERE CodeCombinationId = code_combination_id;
1046
1047 rec_test_acct_id c_test_acct_id%ROWTYPE;
1048 acct_invalid BOOLEAN := FALSE;
1049 current_calling_sequence VARCHAR2(2000);
1050 debug_info VARCHAR2(100);
1051 BEGIN
1052 current_calling_sequence := 'AP_CALC_WITHHOLDING_PKG.GL_Account_INVALID<-' ||
1053 P_Calling_Sequence;
1054
1055 debug_info := 'Open CURSOR c_test_acct_id';
1056 OPEN c_test_acct_id;
1057
1058 debug_info := 'Fetch CURSOR c_test_acct_id';
1059 FETCH c_test_acct_id INTO rec_test_acct_id;
1060
1061 IF (
1062 (c_test_acct_id%NOTFOUND)
1063 OR
1064 (rec_test_acct_id.detail_posting_allowed_flag = 'N')
1065 OR
1066 (rec_test_acct_id.start_date_active > AccountingDate)
1067 OR
1068 (rec_test_acct_id.end_date_active <= AccountingDate)
1069 OR
1070 (rec_test_acct_id.template_id IS not NULL)
1071 OR
1072 (rec_test_acct_id.enabled_flag <> 'Y')
1073 OR
1074 (rec_test_acct_id.summary_flag <> 'N')
1075 ) THEN
1076 acct_invalid := TRUE;
1077 END IF;
1078
1079 debug_info := 'Close CURSOR c_test_acct_id';
1080 CLOSE c_test_acct_id;
1081 RETURN(acct_invalid);
1082
1083 EXCEPTION
1084 WHEN OTHERS THEN
1085 IF (SQLCODE <> -20001) THEN
1086 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1087 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1088 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
1089 FND_MESSAGE.SET_TOKEN('PARAMETERS',
1090 ' Code Combination Id = ' || to_char(CodeCombinationId) ||
1091 ', Accounting Date = ' || to_char(AccountingDate));
1092
1093 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
1094 END IF;
1095 APP_EXCEPTION.RAISE_EXCEPTION;
1096 END GL_Account_INVALID;
1097
1098 PROCEDURE Get_Withholding_On_Exception (
1099 InvNum IN VARCHAR2,
1100 TaxName IN VARCHAR2,
1101 AmtSubject IN NUMBER,
1102 CurrCode IN VARCHAR2,
1103 OpDate IN DATE,
1104 SuppId IN NUMBER,
1105 SuppSiteId IN NUMBER,
1106 Rate OUT NOCOPY NUMBER,
1107 RateId OUT NOCOPY NUMBER,
1108 Amount OUT NOCOPY NUMBER,
1109 ExceptionRateNOTFOUND OUT NOCOPY BOOLEAN,
1110 P_Calling_Sequence IN VARCHAR2)
1111 IS
1112 amt NUMBER;
1113 rt ap_awt_tax_rates.tax_rate%TYPE;
1114 CURSOR c_exception_rate IS
1115 SELECT tax_rate,
1116 tax_rate_id
1117 FROM ap_awt_tax_rates
1118 WHERE invoice_num = InvNum
1119 AND vendor_id = SuppId
1120 AND vendor_site_id = SuppSiteId
1121 AND tax_name = TaxName
1122 AND OpDate BETWEEN NVL(start_date, OpDate - 1)
1123 AND NVL(end_date, OpDate + 1)
1124 AND rate_type = 'EXCEPTION';
1125
1126 DBG_Loc VARCHAR2(30) := 'Get_Withholding_On_Exception';
1130 BEGIN
1127 current_calling_sequence VARCHAR2(2000);
1128 debug_info VARCHAR2(100);
1129
1131 current_calling_sequence := 'AP_CALC_WITHHOLDING_PKG.Get_Withholding_On_Exception<-' ||
1132 P_Calling_Sequence;
1133
1134 debug_info := 'Open CURSOR c_EXCEPTION_rate';
1135 OPEN c_EXCEPTION_rate;
1136
1137 debug_info := 'Fetch CURSOR c_EXCEPTION_rate';
1138 FETCH c_EXCEPTION_rate INTO rt, RateId;
1139
1140 ExceptionRateNOTFOUND := c_EXCEPTION_rate%NOTFOUND;
1141 IF c_EXCEPTION_rate%NOTFOUND THEN
1142 Rate := NULL;
1143 RateId := NULL;
1144 Amount := NULL;
1145 ELSE
1146 Rate := rt;
1147 amt := AmtSubject * (rt / 100);
1148 Amount := amt;
1149 END IF;
1150
1151 debug_info := 'Close CURSOR c_EXCEPTION_rate';
1152 CLOSE c_EXCEPTION_rate;
1153
1154 EXCEPTION
1155 WHEN OTHERS THEN
1156 IF (SQLCODE <> -20001) THEN
1157 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1158 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1159 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
1160 FND_MESSAGE.SET_TOKEN('PARAMETERS',
1161 ' Invoice Num = ' || InvNum ||
1162 ', Tax Name = ' || TaxName ||
1163 ', Amt Subject = ' || to_char(AmtSubject) ||
1164 ', Curr Code = ' || CurrCode ||
1165 ', Op Date = ' || to_char(OpDate) ||
1166 ', Supp Id = ' || to_char(SuppId) ||
1167 ', Supp Site Id = ' || to_char(SuppSiteId));
1168
1169 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
1170 END IF;
1171 APP_EXCEPTION.RAISE_EXCEPTION;
1172
1173 END Get_Withholding_On_Exception;
1174
1175 PROCEDURE Get_Normal_Withholding(
1176 TaxName IN VARCHAR2,
1177 AmtSubject IN NUMBER,
1178 CurrCode IN VARCHAR2,
1179 OpDate IN DATE,
1180 Rate OUT NOCOPY NUMBER,
1181 RateId OUT NOCOPY NUMBER,
1182 Amount OUT NOCOPY NUMBER,
1183 NormalRateNOTFOUND OUT NOCOPY BOOLEAN,
1184 P_Calling_Sequence IN VARCHAR2)
1185 IS
1186 amt NUMBER;
1187 rt ap_awt_tax_rates.tax_rate%TYPE;
1188
1189 CURSOR c_normal_rate
1190 IS
1191 SELECT tax_rate,
1192 tax_rate_id
1193 FROM ap_awt_tax_rates
1194 WHERE tax_name = TaxName
1195 AND OpDate BETWEEN NVL(start_date, OpDate - 1)
1196 AND NVL(end_date, OpDate + 1)
1197 AND rate_type = 'STANDARD';
1198
1199 DBG_Loc VARCHAR2(30) := 'Get_Normal_Withholding';
1200 current_calling_sequence VARCHAR2(2000);
1201 debug_info VARCHAR2(100);
1202
1203 BEGIN
1204 current_calling_sequence := 'AP_CALC_WITHHOLDING_PKG.Get_Normal_Withholding<-' ||
1205 P_Calling_Sequence;
1206
1207 debug_info := 'Open CURSOR c_normal_rate';
1208 OPEN c_normal_rate;
1209
1210 debug_info := 'Fetch CURSOR c_normal_rate';
1211 FETCH c_normal_rate INTO rt, RateId;
1212
1213 NormalRateNOTFOUND := c_normal_rate%NOTFOUND;
1214
1215 IF c_normal_rate%NOTFOUND THEN
1216 Rate := NULL;
1217 RateId := NULL;
1218 Amount := NULL;
1219 ELSE
1220 Rate := rt;
1221 amt := AmtSubject * (rt / 100);
1222
1223 -- amt := Ap_Utilities_Pkg.Ap_Round_Currency(amt, CurrCode);
1224 Amount := amt;
1225
1226 END IF;
1227 debug_info := 'Close CURSOR c_normal_rate';
1228 CLOSE c_normal_rate;
1229
1230 EXCEPTION
1231 WHEN OTHERS THEN
1232 IF (SQLCODE <> -20001) THEN
1233 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1234 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1235 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
1236 FND_MESSAGE.SET_TOKEN('PARAMETERS',
1237 ' Tax Name = ' || TaxName ||
1238 ', Amount Subject = ' || to_char(AmtSubject) ||
1239 ', Currency Code = ' || CurrCode ||
1240 ', Op Date = ' || to_char(OpDate));
1241
1242 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
1243 END IF;
1244 APP_EXCEPTION.RAISE_EXCEPTION;
1245 END Get_Normal_Withholding;
1246
1247 PROCEDURE Get_Withholding_On_Certificate (
1248 TaxName IN VARCHAR2,
1249 AmtSubject IN NUMBER,
1250 CurrCode IN VARCHAR2,
1251 OpDate IN DATE,
1252 SuppId IN NUMBER,
1253 SuppSiteId IN NUMBER,
1254 Rate OUT NOCOPY NUMBER,
1255 RateId OUT NOCOPY NUMBER,
1256 Amount OUT NOCOPY NUMBER,
1257 CertRateNOTFOUND OUT NOCOPY BOOLEAN,
1258 P_Calling_Sequence IN VARCHAR2)
1259 IS
1260 amt NUMBER;
1261 rt ap_awt_tax_rates.tax_rate%TYPE;
1262 CURSOR c_certificate_rate
1263 IS
1267 WHERE tax_name = TaxName
1264 SELECT tax_rate,
1265 tax_rate_id
1266 FROM ap_awt_tax_rates
1268 AND vendor_id = SuppId
1269 AND vendor_site_id = SuppSiteId
1270 AND OpDate BETWEEN NVL(start_date, OpDate - 1)
1271 AND NVL(end_date, OpDate + 1)
1272 AND rate_type = 'CERTIFICATE'
1273 ORDER BY priority ASC;
1274
1275 DBG_Loc VARCHAR2(30) := 'Get_Withholding_On_Certificate';
1276 current_calling_sequence VARCHAR2(2000);
1277 debug_info VARCHAR2(100);
1278
1279 BEGIN
1280 current_calling_sequence := 'AP_CALC_WITHHOLDING_PKG.Get_Withholding_On_Certificate<-' ||
1281 P_Calling_Sequence;
1282
1283 debug_info := 'Open CURSOR c_certificate_rate';
1284 OPEN c_certificate_rate;
1285
1286 debug_info := 'Fetch CURSOR c_certificate_rate';
1287 FETCH c_certificate_rate INTO rt, RateId;
1288
1289 CertRateNOTFOUND := c_certificate_rate%NOTFOUND;
1290
1291 IF c_certificate_rate%NOTFOUND THEN
1292 Rate := NULL;
1293 RateId := NULL;
1294 Amount := NULL;
1295 ELSE
1296 Rate := rt;
1297 amt := AmtSubject * (rt / 100);
1298 Amount := amt;
1299 END IF;
1300
1301 debug_info := 'Close CURSOR c_certificate_rate';
1302 CLOSE c_certificate_rate;
1303
1304 EXCEPTION
1305 WHEN OTHERS THEN
1306 IF (SQLCODE <> -20001) THEN
1307 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1308 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1309 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
1310 FND_MESSAGE.SET_TOKEN('PARAMETERS',
1311 ' Tax Name = ' || TaxName ||
1312 ', Amount Subject = ' || to_char(AmtSubject) ||
1313 ', Currency Code = ' || CurrCode ||
1314 ', Op Date = ' || to_char(OpDate) ||
1315 ', Supplier Id = ' || to_char(SuppId) ||
1316 ', Supp Site Id = ' || to_char(SuppSiteId) );
1317
1318 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
1319 END IF;
1320 APP_EXCEPTION.RAISE_EXCEPTION;
1321
1322 END Get_Withholding_On_Certificate;
1323
1324
1325 PROCEDURE Get_Withholding_On_Ranges (
1326 GroupId IN NUMBER,
1327 TaxName IN VARCHAR2,
1328 CodeCombinationId IN NUMBER,
1329 GLPeriodName IN VARCHAR2,
1330 AwtPeriodName IN VARCHAR2,
1331 AwtPeriodType IN VARCHAR2,
1332 PeriodLimit IN NUMBER,
1333 InvoiceId IN NUMBER,
1334 PaymentNum IN NUMBER,
1335 AmtSubject IN NUMBER,
1336 ExchangeRate IN NUMBER,
1337 CurrCode IN VARCHAR2,
1338 BaseCurrCode IN VARCHAR2,
1339 OpDate IN DATE,
1340 SuppId IN NUMBER,
1341 SuppSiteId IN NUMBER,
1342 AmountBasis IN VARCHAR2,
1343 PeriodBasis IN VARCHAR2,
1344 CheckrunName IN VARCHAR2,
1345 LastUpdatedBy IN NUMBER,
1346 LastUpdateLogin IN NUMBER,
1347 ProgramApplicationId IN NUMBER,
1348 ProgramId IN NUMBER,
1349 RequestId IN NUMBER,
1350 CallingModule IN VARCHAR2,
1351 RangesWTAmount OUT NOCOPY NUMBER,
1352 RangesNumber OUT NOCOPY NUMBER,
1353 RangesRateNOTFOUND OUT NOCOPY BOOLEAN,
1354 RangesINVALID OUT NOCOPY BOOLEAN,
1355 RangesDatesINVALID OUT NOCOPY BOOLEAN,
1356 P_Calling_Sequence IN VARCHAR2,
1357 P_Invoice_Payment_Id IN NUMBER DEFAULT NULL,
1358 auto_offset_segs IN VARCHAR2,
1359 cert_withholding_rate IN NUMBER,
1360 cert_withholding_rate_id IN NUMBER,
1361 p_checkrun_id in number default null)
1362 IS
1363 -- This PROCEDURE also INSERTs temporary distribution lines due
1364 -- to the ranges AND triggers the corresponding bucket INSERT OR
1365 -- UPDATE.
1366
1367 DBG_Loc VARCHAR2(30) := 'Get_Withholding_On_Ranges';
1368 current_calling_sequence VARCHAR2(2000);
1369 debug_info VARCHAR2(100);
1370 amt NUMBER := 0;
1371 nr NUMBER := 0;
1372 DO_HANDLE_BUCKET CONSTANT CHAR(1) := 'Y';
1373
1374 TYPE Start_Amount_TabTyp IS
1375 TABLE OF ap_awt_tax_rates.start_amount%TYPE
1376 INDEX BY BINARY_INTEGER;
1377 tab_start_amount Start_Amount_TabTyp;
1378
1379 TYPE End_Amount_TabTyp IS
1380 TABLE OF ap_awt_tax_rates.end_amount%TYPE
1381 INDEX BY BINARY_INTEGER;
1382 tab_end_amount End_Amount_TabTyp;
1383
1384 TYPE Tax_Rate_TabTyp IS
1385 TABLE OF ap_awt_tax_rates.tax_rate%TYPE
1386 INDEX BY BINARY_INTEGER;
1387 tab_tax_rate Tax_Rate_TabTyp;
1388
1389 TYPE Tax_Rate_Id_TabTyp IS
1390 TABLE OF ap_awt_tax_rates.tax_rate_id%TYPE
1394 CURSOR c_amount_ranges (
1391 INDEX BY BINARY_INTEGER;
1392 tab_tax_rate_id Tax_Rate_Id_TabTyp;
1393
1395 TaxCode IN VARCHAR2,
1396 OpDate IN DATE)
1397 IS
1398 SELECT tax_rate
1399 , tax_rate_id
1400 , start_amount
1401 , end_amount
1402 , NVL(start_date, OpDate - 1) start_date
1403 , NVL(end_date, OpDate + 1) end_date
1404 FROM ap_awt_tax_rates
1405 WHERE tax_name = TaxCode
1406 AND rate_type = 'STANDARD'
1407 AND OpDate BETWEEN NVL(start_date, OpDate - 1)
1408 AND NVL(end_date, OpDate + 1)
1409 ORDER BY start_amount asc;
1410
1411 rec_amount_ranges c_amount_ranges%ROWTYPE;
1412
1413 -- Start assuming DATEs in ranges AND the ranges are valid
1414 invalid_range_dates BOOLEAN := FALSE;
1415 invalid_ranges BOOLEAN := FALSE;
1416
1417 NO_RANGES EXCEPTION;
1418 WRONG_CURRENCY EXCEPTION;
1419
1420 BEGIN
1421 current_calling_sequence := 'AP_CALC_WITHHOLDING_PKG.Get_Withholding_On_Ranges<-' ||
1422 P_Calling_Sequence;
1423
1424 debug_info := 'Open CURSOR c_amount_ranges';
1425 OPEN c_amount_ranges (TaxName, OpDate);
1426
1427 <<Count_Ranges>>
1428 DECLARE
1429 i binary_integer := 1;
1430 DateFrom DATE;
1431 DateTo DATE;
1432 DBG_Loc VARCHAR2(30) := 'Count_Ranges';
1433 BEGIN
1434 LOOP
1435 debug_info := 'Fetch CURSOR c_amount_ranges';
1436 FETCH c_amount_ranges INTO rec_amount_ranges;
1437 EXIT WHEN c_amount_ranges%NOTFOUND OR
1438 c_amount_ranges%NOTFOUND IS NULL;
1439 IF (i = 1) THEN
1440 DateFrom := rec_amount_ranges.start_date;
1441 DateTo := rec_amount_ranges.end_date;
1442 IF (rec_amount_ranges.start_amount <> 0) THEN
1443 invalid_ranges := TRUE; -- First range must start from zero.
1444 END IF;
1445 ELSIF (
1446 (DateFrom <> rec_amount_ranges.start_date)
1447 OR
1448 (DateTo <> rec_amount_ranges.end_date)
1449 ) THEN
1450 invalid_range_dates := TRUE; -- Selected ranges MUST have identical
1451 -- effectivity DATEs.
1452 ELSIF
1453 (rec_amount_ranges.start_amount <> tab_end_amount(i-1)) THEN
1454 invalid_ranges := TRUE;
1455 END IF;
1456
1457 IF (NVL(cert_withholding_rate,0) <> 0) AND
1458 (rec_amount_ranges.tax_rate > cert_withholding_rate) THEN
1459
1460 tab_tax_rate(i) := cert_withholding_rate;
1461 tab_tax_rate_id(i) := cert_withholding_rate_id;
1462 tab_start_amount(i) := rec_amount_ranges.start_amount;
1463 tab_end_amount(i) := rec_amount_ranges.end_amount;
1464
1465 ELSE
1466
1467 tab_tax_rate(i) := rec_amount_ranges.tax_rate;
1468 tab_tax_rate_id(i) := rec_amount_ranges.tax_rate_id;
1469 tab_start_amount(i) := rec_amount_ranges.start_amount;
1470 tab_end_amount(i) := rec_amount_ranges.end_amount;
1471
1472 END IF;
1473 nr := c_amount_ranges%ROWCOUNT;
1474 i := nr + 1;
1475 END LOOP;
1476
1477 IF nr>0 THEN
1478 rollback to BEFORE_CERTIFICATE;
1479 END IF;
1480
1481 IF tab_end_amount(nr) IS not NULL THEN
1482 invalid_ranges := TRUE;
1483 END IF;
1484
1485 EXCEPTION
1486 WHEN NO_DATA_FOUND THEN
1487 invalid_ranges := TRUE;
1488
1489 END Count_Ranges;
1490
1491 debug_info := 'Close CURSOR c_amount_ranges';
1492 CLOSE c_amount_ranges;
1493
1494 RangesRateNOTFOUND := FALSE;
1495 RangesINVALID := invalid_ranges;
1496 RangesDatesINVALID := invalid_range_dates;
1497 IF (
1498 (invalid_ranges)
1499 OR
1500 (invalid_range_dates)
1501 ) THEN
1502 RAISE NO_RANGES;
1503 END IF;
1504
1505 IF (currency_code <> FUNCTIONal_currency) THEN
1506 RAISE WRONG_CURRENCY;
1507 END IF;
1508
1509 -- Check IF amount basis IS WITHHELD
1510 IF (AmountBasis = 'WITHHELD') THEN
1511 -- Example:
1512 --
1513 -- The following ranges expressed in WT:
1514 --
1515 -- Got at
1516 -- WT Ranges Rate
1517 -- --------- ------
1518 -- 0 - 100 10%
1519 -- 100 - 200 20%
1520 -- 200 - 500 25%
1521 -- 500 + 30%
1522 --
1523 -- will be transformed in the equivalent:
1524 --
1525 -- Subjected
1526 -- Amt.Ranges Rate
1527 -- ----------- -----
1528 -- 0 - 1000 10%
1529 -- 1000 - 1500 20%
1530 -- 1500 - 2700 25%
1531 -- 2700 + 30%
1532 --
1533 -- The next upper limit IS calculated by adding the result of the
1534 -- ratio (wt_range_width / rate) to the lower range limit. The
1535 -- next lower range limit IS substituted by the previous upper
1536 -- range limit.
1537 --
1541 range_width NUMBER;
1538 <<Amount_Basis_Is_Withheld>>
1539 DECLARE
1540 DBG_Loc VARCHAR2(30) := 'Amount_Basis_Is_Withheld';
1542 new_start_amount NUMBER := 0;
1543 new_end_amount NUMBER;
1544 BEGIN
1545 FOR j in 1..nr LOOP
1546 range_width := tab_end_amount(j) -
1547 tab_start_amount(j);
1548 tab_start_amount(j) := new_start_amount;
1549 new_end_amount := tab_start_amount(j) +
1550 (range_width * 100 /
1551 tab_tax_rate(j));
1552 tab_end_amount(j) := Ap_Utilities_Pkg.Ap_Round_Currency
1553 (new_end_amount ,CurrCode);
1554 new_start_amount := tab_end_amount(j);
1555
1556 END LOOP;
1557 END Amount_Basis_Is_Withheld;
1558 END IF; -- Whether the amount basis was WITHHELD
1559
1560 -- Check IF period basis IS PERIOD
1561 IF (PeriodBasis = 'PERIOD') THEN
1562
1563 -- If the range basis IS PERIOD, each amount range limit IS to
1564 -- be decreased by the amount already withheld. This could cause
1565 -- vacuum ranges that will be skipped.
1566
1567 <<Period_Basis_Is_Period>>
1568 DECLARE
1569 CURSOR c_gross_amount_to_date IS
1570 SELECT gross_amount_to_date
1571 FROM ap_awt_buckets
1572 WHERE (period_name = AwtPeriodName)
1573 AND (tax_name = TaxName)
1574 AND (vendor_id = SuppId);
1575 gross_amount_to_date ap_awt_buckets.gross_amount_to_date%TYPE;
1576 new_number_of_ranges NUMBER := 0;
1577 i binary_integer := 1;
1578 DBG_Loc VARCHAR2(30) := 'Period_Basis_Is_Period';
1579 BEGIN
1580 debug_info := 'Open CURSOR c_gross_amount_to_date';
1581 OPEN c_gross_amount_to_date;
1582
1583 debug_info := 'Fetch CURSOR c_gross_amount_to_date';
1584 FETCH c_gross_amount_to_date INTO gross_amount_to_date;
1585
1586 IF c_gross_amount_to_date%NOTFOUND THEN
1587 gross_amount_to_date := 0;
1588 END IF;
1589
1590 debug_info := 'Close CURSOR c_gross_amount_to_date';
1591 CLOSE c_gross_amount_to_date;
1592
1593 FOR j in 1..nr LOOP
1594 tab_start_amount(i) := tab_start_amount(j) -
1595 gross_amount_to_date;
1596 IF (tab_start_amount(i) < 0) THEN
1597 tab_start_amount(i) := 0;
1598 END IF;
1599 tab_end_amount(i) := tab_end_amount(j) -
1600 gross_amount_to_date;
1601 IF (tab_end_amount(i) < 0) THEN
1602 tab_end_amount(i) := 0;
1603 END IF;
1604 tab_tax_rate(i) := tab_tax_rate(j);
1605 tab_tax_rate_id(i) := tab_tax_rate_id(j);
1606 IF (
1607 (tab_end_amount(i) > 0)
1608 OR
1609 (tab_end_amount(i) IS NULL)
1610 ) THEN
1611
1612 new_number_of_ranges := i;
1613 i := i + 1;
1614 END IF;
1615 END LOOP;
1616 nr := new_number_of_ranges;
1617 END Period_Basis_Is_Period;
1618 END IF; -- Whether the period basis was PERIOD
1619
1620 -- Loop on the ranges
1621 -- ==================
1622
1623 <<Processing_Ranges>>
1624 DECLARE
1625 amount_subject_for_ranges NUMBER;
1626 range_width NUMBER;
1627 current_amount_to_withhold NUMBER;
1628 current_amount_withheld NUMBER;
1629 DBG_Loc VARCHAR2(30) := 'Processing_Ranges';
1630 BEGIN
1631
1632 amount_subject_for_ranges := Abs(AmtSubject);
1633 <<FOR_EACH_RANGE>>
1634 FOR k in 1..nr LOOP
1635 range_width := tab_end_amount(k) -
1636 tab_start_amount(k);
1637 IF (amount_subject_for_ranges > range_width) THEN
1638 current_amount_to_withhold := range_width;
1639 amount_subject_for_ranges := amount_subject_for_ranges -
1640 range_width;
1641 ELSE
1642 current_amount_to_withhold := amount_subject_for_ranges;
1643 amount_subject_for_ranges := 0;
1644 END IF;
1645 current_amount_withheld := (current_amount_to_withhold / 100) *
1646 tab_tax_rate(k);
1647 current_amount_withheld := Ap_Utilities_Pkg.Ap_Round_Currency
1648 (current_amount_withheld ,CurrCode);
1649
1650 -- Apply Cut Off to this amount:
1651 current_amount_withheld := Do_AWT_Cut_Off (
1652 OpDate,
1653 current_amount_to_withhold,
1654 current_amount_withheld,
1655 SuppId,
1656 TaxName,
1657 AwtPeriodName,
1658 PeriodLimit,
1659 current_calling_sequence);
1660
1661 IF (Amtsubject < 0) THEN
1662 amt := amt - current_amount_withheld;
1663 ELSE
1664 amt := amt + current_amount_withheld;
1665 END IF;
1666
1667 IF (Amtsubject < 0) THEN
1671 -- End of code fix for the bug 5236191 by suchhabr
1668 current_amount_withheld := (-1) * current_amount_withheld;
1669 -- Start of code fix for the bug 5236191 by suchhabr
1670 current_amount_to_withhold := (-1) * current_amount_to_withhold;
1672 END IF;
1673
1674
1675 -- Insert the temporary distribution line for this amount withheld:
1676 IF (current_amount_withheld <> 0OR tab_tax_rate(k) = 0) THEN
1677
1678 Insert_Temp_Distribution (
1679 InvoiceId,
1680 SuppId,
1681 PaymentNum,
1682 GroupId,
1683 TaxName,
1684 CodeCombinationId,
1685 current_amount_to_withhold,
1686 current_amount_withheld,
1687 OpDate,
1688 GLPeriodName,
1689 AwtPeriodType,
1690 AwtPeriodName,
1691 CheckrunName,
1692 tab_tax_rate_id(k),
1693 ExchangeRate,
1694 CurrCode,
1695 BaseCurrCode,
1696 NULL,
1697 current_calling_sequence,
1698 DO_HANDLE_BUCKET,
1699 LastUpdatedBy,
1700 LastUpdateLogin,
1701 ProgramApplicationId,
1702 ProgramId,
1703 RequestId,
1704 CallingModule,
1705 P_Invoice_Payment_Id,
1706 p_checkrun_id => p_checkrun_id);
1707 END IF;
1708 END LOOP FOR_EACH_RANGE;
1709
1710 -- Set the OUT arguments:
1711 RangesNumber := nr;
1712 RangesWTAmount := amt;
1713
1714 END Processing_Ranges;
1715 EXCEPTION
1716 WHEN NO_RANGES THEN
1717 -- Simply RETURN RangesNumber AND RangesWTAmount both SET to ZERO
1718 RangesNumber := 0;
1719 RangesWTAmount := 0;
1720 -- Return boolean notfound test SET to TRUE, too:
1721 RangesRateNOTFOUND := TRUE;
1722
1723 WHEN WRONG_CURRENCY THEN
1724 RangesNumber := nr;
1725 RangesWTAmount := 0;
1726
1727 WHEN OTHERS THEN
1728 IF (SQLCODE <> -20001) THEN
1729 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1730 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1731 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
1732 FND_MESSAGE.SET_TOKEN('PARAMETERS',
1733 ' GroupId = ' || to_char(GroupId) ||
1734 ', Tax Name = ' || TaxName ||
1735 ', CodeCombinationId = ' || to_char(CodeCombinationId) ||
1736 ', GLPeriodName = ' || GLPeriodName ||
1737 ', AwtPeriodName = ' || AwtPeriodName ||
1738 ', AwtPeriodType = ' || AwtPeriodType ||
1739 ', PeriodLimit = ' || to_char(PeriodLimit) ||
1740 ', InvoiceId = ' || to_char(InvoiceId) ||
1741 ', PaymentNum = ' || to_char(PaymentNum) ||
1742 ', AmtSubject = ' || to_char(AmtSubject) ||
1743 ', ExchangeRate = ' || to_char(ExchangeRate));
1744
1745 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
1746 END IF;
1747 APP_EXCEPTION.RAISE_EXCEPTION;
1748
1749 END Get_Withholding_On_Ranges;
1750
1751 -- _______
1752 -- | |
1753 -- | |
1754 -- | |
1755 -- _________| |_________
1756 -- \ /
1757 -- \ Calculate_AWT_Amounts /
1758 -- \ /
1759 -- \ _____ /
1760 -- \ | | /
1761 -- \ | | /
1762 -- \___| |___/
1763 -- \ /
1764 -- \ BEGIN /
1765 -- \ /
1766 -- \ /
1767 -- \ /
1768 -- \ /
1769 -- v
1770
1771 BEGIN
1772
1773 current_calling_sequence := 'AP_CALC_WITHHOLDING_PKG.AP_Calculate_AWT_Amounts<-' ||
1774 P_calling_sequence;
1775
1776 -- SET UP WITHHOLDING ENVIRONMENT:
1777
1778 <<Getting_Basic_Info>>
1779 DECLARE
1780 DBG_Loc VARCHAR2(30) := 'Getting_Basic_Info';
1781 BEGIN
1782
1783 BEGIN
1784 -- Change this SQL to use appropriate PL/SQL FUNCTIONs WHEN available
1785 -- in the utilities package!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
1786 debug_info := 'Set up Withholding Environment';
1787
1788 SELECT GPS.period_name,
1789 P_Awt_Date
1790 INTO gl_period_name,
1791 gl_awt_date
1792 FROM gl_period_statuses GPS,
1793 ap_system_parameters ASP
1794 WHERE GPS.application_id = 200
1795 AND GPS.set_of_books_id = ASP.set_of_books_id
1796 AND P_Awt_Date BETWEEN GPS.start_date
1797 AND GPS.end_date
1798 AND (
1799 (GPS.closing_status IN ('O', 'F'))
1800 OR
1804 AND ASP.ORG_ID = nvl(P_ORG_ID, asp.org_id); --4742265
1801 (P_Calling_Module IN ('INVOICE INQUIRY','AWT REPORT'))
1802 )
1803 AND NVL(GPS.ADJUSTMENT_PERIOD_FLAG, 'N') = 'N'
1805 EXCEPTION
1806 WHEN NO_DATA_FOUND THEN
1807 ap_utilities_pkg.get_OPEN_gl_date(P_Awt_Date, gl_period_name, gl_awt_date);
1808
1809 IF gl_awt_date IS NULL THEN
1810 RAISE NOT_AN_OPEN_GL_PERIOD;
1811 END IF;
1812 END;
1813
1814 -- Get INVOICE basic information AND SUPPLIER identIFication:
1815
1816 debug_info := 'Get INVOICE basic information AND SUPPLIER identIFication';
1817 SELECT invoice_currency_code,
1818 payment_currency_code,
1819 payment_cross_rate,
1820 exchange_rate,
1821 invoice_num,
1822 vendor_id,
1823 vendor_site_id,
1824 org_id /* Bug 3700128. MOAC Project */
1825 INTO currency_code,
1826 payment_currency_code,
1827 payment_cross_rate,
1828 invoice_exchange_rate,
1829 invoice_number,
1830 supplier_id,
1831 supplier_site_id,
1832 g_org_id /* Bug 3700128. MOAC Project */
1833 FROM ap_invoices
1834 WHERE invoice_id = P_Invoice_Id;
1835
1836 -- Get base currency code:
1837
1838 debug_info := 'Get base currency code';
1839 SELECT base_currency_code
1840 INTO functional_currency
1841 FROM ap_system_parameters
1842 WHERE org_id = nvl(p_org_id,org_id); --4742265
1843
1844 END Getting_Basic_Info;
1845
1846 -- Get GROUP AMOUNTS AND SET NUMBER of GROUPS found:
1847 --Bug6660355
1848 <<Get_Groups>>
1849 DECLARE
1850 CURSOR c_group_amounts (InvId IN NUMBER, WTDate IN DATE)
1851 IS
1852 SELECT D.group_id
1853 ,DECODE (SIGN(WTDate - G.inactive_date),
1854 0, 'Y',
1855 1, 'Y', 'N') non_valid_group,
1856 SUM(D.amount * NVL(invoice_exchange_rate,1)) group_amount,
1857 SUM(DECODE (D.line_type_lookup_code,
1858 'TAX', NVL(D.base_amount, D.amount) ,0)) vat_amount
1859 FROM (select DECODE(AIP.create_awt_dists_type,'BOTH',decode(p_calling_module,'AUTOAPPROVAL',
1860 AID.awt_group_id,AID.pay_awt_group_id),
1861 'PAYMENT',AID.pay_awt_group_id,AID.awt_group_id) group_id,
1862 AID.amount,AID.base_amount,AID.line_type_lookup_code
1863 from ap_invoice_distributions_all AID,ap_system_parameters_all AIP
1864 where AID.invoice_id = InvId
1865 AND AID.org_id = AIP.org_id ) D,
1866 ap_awt_groups G
1867 where D.group_id = G.group_id(+)
1868 AND D.line_type_lookup_code <> 'AWT'
1869 GROUP BY D.group_id,
1870 DECODE ( SIGN(WTDate - G.inactive_date),
1871 0, 'Y',
1872 1, 'Y','N')
1873
1874 HAVING SUM(D.amount) <> 0
1875 ORDER BY DECODE(D.group_id, NULL, 0, 1);
1876
1877 rec_group_amounts c_group_amounts%ROWTYPE;
1878
1879 DBG_Loc VARCHAR2(30) := 'Get_Groups';
1880 i binary_integer := 0;
1881 total_invoice_amount ap_invoices.invoice_amount%TYPE;
1882 total_vat_amount NUMBER := 0;
1883 gross_exempt_amount NUMBER := 0;
1884 gross_amount_on_all_awt_groups NUMBER := 0;
1885 gross_amount_allbutlast_group NUMBER := 0;
1886 one_invalid_group_exists boolean := FALSE;
1887
1888 BEGIN
1889 debug_info := 'Open CURSOR to get group amounts';
1890 OPEN c_group_amounts(P_Invoice_Id, gl_awt_date);
1891
1892 LOOP
1893 debug_info := 'Fetch CURSOR to get group amounts';
1894
1895 FETCH c_group_amounts INTO rec_group_amounts;
1896 EXIT WHEN c_group_amounts%NOTFOUND;
1897
1898 IF (rec_group_amounts.non_valid_group = 'Y') THEN
1899 one_invalid_group_exists := TRUE;
1900 invalid_group := rec_group_amounts.group_id;
1901 END IF;
1902
1903 EXIT WHEN one_invalid_group_exists;
1904 i := c_group_amounts%ROWCOUNT;
1905 tab_group_id(i) := rec_group_amounts.group_id;
1906 tab_amount_by_group(i) := rec_group_amounts.group_amount;
1907 tab_vat_by_group(i) := rec_group_amounts.vat_amount;
1908 total_vat_amount := total_vat_amount +
1909 rec_group_amounts.vat_amount;
1910 IF (rec_group_amounts.group_id IS NULL) THEN
1911 gross_exempt_amount := rec_group_amounts.group_amount;
1912 ELSE
1913 gross_amount_on_all_awt_groups := gross_amount_on_all_awt_groups +
1914 rec_group_amounts.group_amount;
1915 END IF;
1916 END LOOP;
1917
1918 debug_info := 'Close CURSOR to get group amounts';
1919 CLOSE c_group_amounts;
1920
1921 IF (i = 0) THEN
1922 RAISE ALL_GROUPS_ZERO;
1923 END IF;
1924
1925 IF one_invalid_group_exists THEN
1926 RAISE ONE_INVALID_GROUP;
1927 END IF;
1928
1929 NUMBER_of_awt_groups := i;
1930
1934
1931 -- To be sure, the total invoice amount IS calculated rather than
1932 -- SELECTed FROM the database:
1933 -- Calculate TOTAL INVOICE AMOUNT FROM distribution lines:
1935 total_invoice_amount := gross_amount_on_all_awt_groups +
1936 gross_exempt_amount;
1937
1938 -- Adjust figures FOR possible discount on invoice
1939
1940 IF (P_Create_Dists in('PAYMENT','BOTH')) THEN --Bug6660355
1941 <<Check_For_Discount>>
1942 DECLARE
1943 CURSOR c_total_discount_on_invoice
1944 (InvId IN NUMBER,
1945 PaymNum IN NUMBER) IS
1946 SELECT SUM
1947 (NVL(S.discount_amount_available, 0)
1948 +NVL(S.second_disc_amt_available, 0)
1949 +NVL(S.third_disc_amt_available, 0)) discount,
1950 P.disc_is_inv_less_tax_flag
1951 FROM ap_payment_schedules S,
1952 ap_system_parameters P
1953 WHERE S.invoice_id = InvId
1954 /* Next condition is to make ok the projected withholding screen */
1955 AND S.payment_num = NVL(PaymNum, S.payment_num)
1956 AND P.awt_include_discount_amt = 'N'
1957 AND P.org_id = p_org_id
1958 AND P.org_id = s.org_id --4742265
1959 --bug5052436 modifies the above predicate
1960 GROUP BY P.disc_is_inv_less_tax_flag;
1961
1962 discount_amount NUMBER;
1963 tax_excluded char(1);
1964 DBG_Loc VARCHAR2(30) := 'Check_For_Discount';
1965 NO_DISCOUNT EXCEPTION;
1966
1967 BEGIN
1968 debug_info := 'Open CURSOR to check FOR discounts';
1969 OPEN c_total_discount_on_invoice(P_Invoice_Id, P_Payment_Num);
1970
1971 debug_info := 'Fetch CURSOR to check FOR discounts';
1972 FETCH c_total_discount_on_invoice INTO discount_amount, tax_excluded;
1973
1974 IF c_total_discount_on_invoice%NOTFOUND THEN
1975 discount_amount := 0;
1976 END IF;
1977
1978 debug_info := 'Close CURSOR to check FOR discounts';
1979 CLOSE c_total_discount_on_invoice;
1980
1981 IF (discount_amount = 0) THEN
1982 RAISE NO_DISCOUNT;
1983 END IF;
1984
1985 discount_amount := discount_amount / payment_cross_rate
1986 * NVL(invoice_exchange_rate,1);
1987
1988 -- A discount must be taken on this invoice.
1989 -- Thus EACH actual amount by group in the PL/SQL tables needs to be
1990 -- adjusted taking consideration of this discount, according to the
1991 -- following FORmula:
1992 --
1993 -- (OldAmount - GV)
1994 -- NewAmount = OldAmount - WholeDiscount * --------------------
1995 -- (InvoiceAmount - TV)
1996 -- WHERE:
1997 --
1998 -- GV = Group Vat IF (tax_excluded = 'Y'), ELSE ZERO
1999 -- TV = Total Vat IF (tax_excluded = 'Y'), ELSE ZERO
2000 --
2001 -- The amounts by group have come FROM the c_group_amounts CURSOR
2002 -- as figures including tax amounts. If there IS a discount, this
2003 -- applies to each group decreasing only the part on which the
2004 -- discount calculation was extENDed during the payment schedule
2005 -- routines. This IS the meaning of the above FORmula.
2006
2007 <<Loop_On_Group_Amounts>>
2008
2009 FOR j in 1..number_of_awt_groups LOOP
2010 -- This LOOP MUST include alse the withholding exempt group
2011 DECLARE
2012 gv NUMBER := tab_vat_by_group(j);
2013 tv NUMBER := total_vat_amount;
2014 BEGIN
2015 IF (tax_excluded <> 'Y') THEN
2016 gv := 0; tv := 0;
2017 END IF;
2018 IF ((total_invoice_amount - total_vat_amount) > 0) THEN
2019 tab_amount_by_group(j) := (tab_amount_by_group(j)
2020 - ((discount_amount*(tab_amount_by_group(j)
2021 -tab_vat_by_group(j))/(total_invoice_amount-total_vat_amount))));
2022 END IF; -- avoid division by zero
2023 END;
2024 END LOOP Loop_On_Group_Amounts;
2025
2026 -- Finally adjust invoice level data:
2027
2028 total_invoice_amount := total_invoice_amount - discount_amount;
2029
2030 IF (tab_group_id(1) IS NULL) THEN
2031 gross_exempt_amount := tab_amount_by_group(1);
2032 ELSE
2033 gross_exempt_amount := 0;
2034 END IF;
2035 gross_amount_on_all_awt_groups := total_invoice_amount -
2036 gross_exempt_amount;
2037 EXCEPTION
2038 WHEN NO_DISCOUNT THEN
2039 NULL;
2040 END Check_For_Discount;
2041
2042 -- Bug: 2958713 Commented out the end if and added after splitting the payment.
2043 -- END IF; -- Discount was checked only FOR withholding at PAYMENT time
2044
2045 -- Amounts applicable to each AWT group must now be recalculated on
2046 -- the basis of the current payment amount (which IS in the P_Amount
2047 -- parameter). For rounding purposes, the last group amount will be
2048 -- derived as a dIFference.
2049 --
2050 -- Consider the following example:
2051 --
2055 -- Group 2 --> 50
2052 -- Invoice amount....: 300
2053 -- Group Distribution: Null Group --> 150 (WT exempt)
2054 -- Group 1 --> 100
2056 -- Actual payment....: 75.25
2057 --
2058 -- Since only a part of the invoice amount IS subject to WT, the
2059 -- amount to withhold IS 75.25 * (150 / 300) = 75.25 / 2 = 37.625 =
2060 -- = (rounded) 37.63.
2061 -- The payment amount subject to WT FOR group 1 IS:
2062 -- 75.25 * (100 / 300) = 25.0833333... = (rounded) 25.08.
2063 -- The amount FOR group 2 should be 75.25 * (50 / 300) = 12.5416666... =
2064 -- = (rounded) 12.54; but, since 25.08 (WT group 1) + 12.54 (WT group 2)
2065 -- = 37.62 AND NOT 37.63, the last term IS calculated by subtracting FROM
2066 -- the total amount subject to WT the SUM of the preceding calculated
2067 -- amounts: 37.63 - 25.08 = 12.55.
2068
2069 -- Recalculate the amounts subject to WT in proportion to the payment:
2070
2071 <<Gross_Amounts_By_Payment>>
2072 DECLARE
2073 payment_amount NUMBER := P_Amount;
2074 DBG_Loc VARCHAR2(30) := 'Gross_Amounts_By_Payment';
2075 log_text VARCHAR2(2000);
2076 BEGIN
2077 log_text := 'Prorating ';
2078 IF (
2079 (P_Payment_Num IS NULL)
2080 OR
2081 (P_Calling_Module in ('INVOICE INQUIRY', 'AWT REPORT'))
2082 ) THEN
2083 payment_amount := total_invoice_amount;
2084 log_text := log_text||'invoice ';
2085 ELSE
2086 log_text := log_text||'payment ';
2087 END IF;
2088 log_text := log_text||'amount ['||
2089 ltrim(
2090 to_char(payment_amount
2091 , '999,999,999,999,999,999.99'
2092 )
2093 )||'] through ';
2094 IF (tab_group_id(1) IS NULL) THEN
2095 log_text := log_text||to_char(number_of_awt_groups-1);
2096 ELSE
2097 log_text := log_text||to_char(number_of_awt_groups);
2098 END IF;
2099 log_text := log_text||' AWT groups';
2100
2101 gross_amount_on_all_awt_groups :=
2102 Proportional_Amount(
2103 payment_amount,
2104 gross_amount_on_all_awt_groups,
2105 total_invoice_amount,
2106 functional_currency,
2107 current_calling_sequence);
2108
2109 FOR j in 1..number_of_awt_groups - 1 LOOP
2110 IF tab_group_id(j) IS not NULL THEN
2111 tab_amount_by_group(j) :=
2112 Proportional_Amount(
2113 payment_amount,
2114 tab_amount_by_group(j),
2115 total_invoice_amount,
2116 functional_currency,
2117 current_calling_sequence);
2118
2119 gross_amount_allbutlast_group := gross_amount_allbutlast_group +
2120 tab_amount_by_group(j);
2121 END IF;
2122 END LOOP;
2123
2124 -- Get the last group amount by dIFference:
2125
2126 tab_amount_by_group(number_of_awt_groups) :=
2127 gross_amount_on_all_awt_groups - gross_amount_allbutlast_group;
2128
2129 END Gross_Amounts_By_Payment;
2130 end if; -- For bug 2958713
2131
2132 END Get_Groups;
2133
2134 <<Process_Withholding>>
2135 DECLARE
2136 current_amount_subject_for_tax NUMBER;
2137 current_rank_tax_cumulator NUMBER;
2138 current_rank integer;
2139 starting_group integer(1);
2140 EXCEPTION_withholding_rate ap_awt_tax_rates.tax_rate%TYPE;
2141 EXCEPTION_withholding_rate_id ap_awt_tax_rates.tax_rate_id%TYPE;
2142 EXCEPTION_withholding_amount NUMBER;
2143 NUMBER_of_ranges NUMBER;
2144 ranges_withholding_amount NUMBER;
2145 normal_withholding_amount NUMBER;
2146 normal_withholding_rate NUMBER;
2147 normal_withholding_rate_id NUMBER;
2148 cert_withholding_amount NUMBER;
2149 cert_withholding_rate NUMBER;
2150 cert_withholding_rate_id NUMBER;
2151 withheld_amount NUMBER;
2152 withheld_rate_id NUMBER;
2153 one_tax_missing_period_exists boolean := FALSE;
2154 one_invalid_tax_exists boolean := FALSE;
2155 one_invalid_gl_acct_exists boolean := FALSE;
2156 no_valid_rates_exist boolean := FALSE;
2157 incorrect_ranges boolean := FALSE;
2158 incorrect_range_dates boolean := FALSE;
2159 must_be_base_currency boolean := FALSE;
2160 custom_rate_NOTFOUND boolean := TRUE;
2161 EXCEPTION_rate_NOTFOUND boolean := TRUE;
2162 certIFicate_rate_NOTFOUND boolean := TRUE;
2163 normal_rate_NOTFOUND boolean := TRUE;
2164 ranges_rate_NOTFOUND boolean := TRUE;
2165
2166 DBG_Loc VARCHAR2(30) := 'Process_Withholding';
2167 DO_HANDLE_BUCKET CONSTANT CHAR(1) := 'Y';
2168
2169 CURSOR c_group_taxes (
2170 GrpId IN NUMBER,
2171 WTDate IN DATE)
2172 IS
2173 SELECT AAGT.rank
2174 , AAGT.tax_name
2175 , ATC.range_amount_basis
2176 , ATC.range_period_basis
2177 , AOP.period_name
2178 , ATC.awt_period_type
2179 , ATC.awt_period_limit
2183 , ap_tax_codes ATC
2180 , ATC.inactive_date
2181 , ATC.tax_code_combination_id
2182 FROM ap_awt_group_taxes AAGT
2184 , ap_other_periods AOP
2185 WHERE (AAGT.group_id = GrpId)
2186 AND (AAGT.tax_name = ATC.name)
2187 AND (ATC.tax_type = 'AWT') -- BUG 3665866
2188 AND (AOP.application_id (+) = 200)
2189 AND (AOP.module (+) = 'AWT')
2190 AND (AOP.period_type (+) = ATC.awt_period_type)
2191 AND (AOP.start_date (+) <= TRUNC(gl_awt_date))
2192 AND (AOP.end_date (+) >= TRUNC(gl_awt_date))
2193 ORDER BY rank ASC, ATC.name;
2194 rec_group_taxes c_group_taxes%ROWTYPE;
2195
2196 BEGIN
2197 -- Set starting group FOR WT Calculation, excluding the exempt
2198 -- IF existent:
2199
2200 IF tab_group_id(1) IS NULL THEN
2201 starting_group := 2;
2202 ELSE
2203 starting_group := 1;
2204 END IF;
2205
2206 <<For_Each_Withholding_Group>>
2207 FOR g in starting_group..number_of_awt_groups LOOP
2208
2209 -- Reset amount subject FOR tax, rank AND rank cumulator:
2210
2211 current_amount_subject_for_tax := tab_amount_by_group(g);
2212 current_rank_tax_cumulator := 0;
2213 withheld_amount := 0;
2214
2215 <<Get_Starting_Rank>>
2216
2217 DECLARE
2218 CURSOR c_init_rank
2219 IS
2220 SELECT MIN(rank)
2221 FROM ap_awt_group_taxes
2222 WHERE group_id = tab_group_id(g);
2223 BEGIN
2224
2225 debug_info := 'Open CURSOR to get starting rank ';
2226 OPEN c_init_rank;
2227
2228 debug_info := 'Fetch CURSOR to get starting rank ';
2229 FETCH c_init_rank INTO current_rank;
2230
2231 debug_info := 'Close CURSOR to get starting rank ';
2232 CLOSE c_init_rank;
2233 END Get_Starting_Rank;
2234
2235 debug_info := 'Open CURSOR c_group_taxes';
2236 OPEN c_group_taxes(tab_group_id(g),
2237 gl_awt_date);
2238
2239 <<For_Each_Tax_In_Group>>
2240 LOOP
2241 <<Consider_One_Withholding_Tax>>
2242 DECLARE
2243 DBG_Loc VARCHAR2(30) := 'Consider_One_Withholding_Tax';
2244 BEGIN
2245
2246 debug_info := 'Fetch CURSOR c_roup_taxes';
2247 FETCH c_group_taxes INTO rec_group_taxes;
2248
2249 EXIT WHEN c_group_taxes%NOTFOUND;
2250
2251 -- Check for TAX INACTIVE:
2252
2253 IF (gl_awt_date >= rec_group_taxes.inactive_date) THEN
2254 one_invalid_tax_exists := TRUE;
2255 invalid_group := tab_group_id(g);
2256 invalid_tax := rec_group_taxes.tax_name;
2257 END IF;
2258 EXIT WHEN one_invalid_tax_exists;
2259
2260 -- Check FOR INVALID GL ACCOUNT:
2261
2262 IF GL_Account_INVALID (
2263 rec_group_taxes.tax_code_combination_id,
2264 gl_awt_date,
2265 current_calling_sequence) THEN
2266 one_invalid_gl_acct_exists := TRUE;
2267 invalid_group := tab_group_id(g);
2268 invalid_tax := rec_group_taxes.tax_name;
2269 END IF;
2270 EXIT WHEN one_invalid_gl_acct_exists;
2271
2272 -- Check FOR INVALID PERIOD:
2273
2274 IF (
2275 (rec_group_taxes.awt_period_type IS not NULL)
2276 AND
2277 (rec_group_taxes.period_name IS NULL)
2278 ) THEN
2279 one_tax_missing_period_exists := TRUE;
2280 invalid_group := tab_group_id(g);
2281 invalid_tax := rec_group_taxes.tax_name;
2282 END IF;
2283 EXIT WHEN one_tax_missing_period_exists;
2284
2285 -- Check FOR invoice CURRENCY against amount ranges/limits:
2286
2287 IF (
2288 (rec_group_taxes.awt_period_limit IS not NULL)
2289 AND
2290 (currency_code <> FUNCTIONal_currency)
2291 ) THEN
2292 must_be_base_currency := TRUE;
2293 invalid_group := tab_group_id(g);
2294 invalid_tax := rec_group_taxes.tax_name;
2295 END IF;
2296 EXIT WHEN must_be_base_currency;
2297
2298 -- One of following will turn to FALSE IF at least one valid
2299 -- Withholding Tax Rate row exist in AP_AWT_TAX_RATES table
2300 -- FOR current tax in current group:
2301 custom_rate_NOTFOUND := TRUE;
2302 EXCEPTION_rate_NOTFOUND := TRUE;
2303 certIFicate_rate_NOTFOUND := TRUE;
2304 normal_rate_NOTFOUND := TRUE;
2305 ranges_rate_NOTFOUND := TRUE;
2306
2307 -- (Re-)Calculate rank, cumulator AND amount subject FOR tax
2308
2309 current_rank_tax_cumulator := current_rank_tax_cumulator +
2310 withheld_amount;
2311 IF (rec_group_taxes.rank <> current_rank) THEN
2312 current_amount_subject_for_tax :=
2313 current_amount_subject_for_tax - current_rank_tax_cumulator;
2314 current_rank_tax_cumulator := 0;
2318
2315 END IF;
2316 current_rank := rec_group_taxes.rank;
2317 withheld_amount := 0;
2319 -- HOOK FOR custom withholding routines:
2320 -- Try to get a rate_id FROM the withholding custom package: IF
2321 -- successful, confirm that rate, apply cut off, INSERT the
2322 -- corresponding temporary withholding line, AND go to terminate
2323 -- the current tax processing, looking FOR the next tax.
2324
2325 <<Custom_Withholding_Hook>>
2326 DECLARE
2327 DBG_Loc VARCHAR2(30) := 'Custom_Withholding_Hook';
2328 custom_awt_tax_rate_id ap_awt_tax_rates.tax_rate_id%TYPE;
2329 INVALID_TAX_RATE_ID EXCEPTION;
2330 BEGIN
2331
2332 custom_awt_tax_rate_id :=
2333 Ap_Custom_Withholding_Pkg.Ap_Special_Rate (
2334 rec_group_taxes.tax_name,
2335 P_Invoice_Id,
2336 P_Payment_Num,
2337 gl_awt_date,
2338 current_amount_subject_for_tax);
2339 IF (custom_awt_tax_rate_id IS not NULL) THEN
2340
2341 custom_rate_NOTFOUND := FALSE;
2342
2343 <<Store_Custom_Withholding>>
2344 DECLARE
2345 CURSOR c_custom_rate (TaxRateId IN NUMBER)
2346 IS
2347 SELECT tax_rate
2348 FROM ap_awt_tax_rates
2349 WHERE tax_rate_id = TaxRateId;
2350
2351 custom_wt_amount NUMBER;
2352 custom_wt_rate ap_awt_tax_rates.tax_rate%TYPE;
2353 custom_rate_notfound boolean;
2354 BEGIN
2355 debug_info := 'Open CURSOR FOR custom rate';
2356 OPEN c_custom_rate(custom_awt_tax_rate_id);
2357
2358 debug_info := 'Fetch CURSOR FOR custom rate';
2359 FETCH c_custom_rate INTO custom_wt_rate;
2360
2361 custom_rate_notfound := c_custom_rate%NOTFOUND;
2362
2363 debug_info := 'Close CURSOR FOR custom rate';
2364 CLOSE c_custom_rate;
2365
2366 IF (
2367 (custom_rate_notfound)
2368 OR
2369 (custom_wt_rate IS NULL)
2370 ) THEN
2371 RAISE INVALID_TAX_RATE_ID;
2372 ELSE
2373 custom_wt_amount := current_amount_subject_for_tax *
2374 (custom_wt_rate / 100);
2375 custom_wt_amount := Ap_Utilities_Pkg.Ap_Round_Currency
2376 (custom_wt_amount
2377 ,currency_code);
2378 END IF;
2379 -- Apply cut off:
2380 custom_wt_amount := Do_AWT_Cut_Off
2381 (gl_awt_date,
2382 current_amount_subject_for_tax,
2383 custom_wt_amount,
2384 supplier_id,
2385 rec_group_taxes.tax_name,
2386 rec_group_taxes.period_name,
2387 rec_group_taxes.awt_period_limit,
2388 current_calling_sequence);
2389 -- Insert this custom information:
2390 Insert_Temp_Distribution (P_Invoice_Id,
2391 supplier_id,
2392 P_Payment_Num,
2393 tab_group_id(g),
2394 rec_group_taxes.tax_name,
2395 rec_group_taxes.tax_code_combination_id,
2396 current_amount_subject_for_tax,
2397 custom_wt_amount,
2398 gl_awt_date,
2399 gl_period_name,
2400 rec_group_taxes.awt_period_type,
2401 rec_group_taxes.period_name,
2402 P_Checkrun_Name,
2403 custom_awt_tax_rate_id,
2404 invoice_exchange_rate,
2405 FUNCTIONal_currency,
2406 FUNCTIONal_currency,
2407 NULL,
2408 current_calling_sequence,
2409 DO_HANDLE_BUCKET,
2410 P_Last_Updated_By,
2411 P_Last_Update_Login,
2412 P_Program_Application_Id,
2413 P_Program_Id,
2414 P_Request_Id,
2415 P_Calling_Module,
2416 P_Invoice_Payment_Id,
2417 p_checkrun_id => p_checkrun_id);
2418 END Store_Custom_Withholding;
2419
2420 -- Skip anyORdinary withholding processing:
2421 goto End_Processing_Current_Tax;
2422
2426 custom_rate_NOTFOUND := TRUE;
2423 END IF;
2424 EXCEPTION
2425 WHEN INVALID_TAX_RATE_ID THEN
2427 END Custom_Withholding_Hook;
2428
2429 -- CUSTOM rate unexistent. Continue with core AWT calculations:
2430 -- Get withholding figures FOR EXCEPTION AND apply Cut Off (VALUES
2431 -- could be NULL):
2432 Get_Withholding_On_Exception (invoice_number,
2433 rec_group_taxes.tax_name,
2434 current_amount_subject_for_tax,
2435 functional_currency,
2436 gl_awt_date,
2437 supplier_id,
2438 supplier_site_id,
2439 exception_withholding_rate,
2440 exception_withholding_rate_id,
2441 exception_withholding_amount,
2442 exception_rate_notfound,
2443 current_calling_sequence);
2444 EXCEPTION_withholding_amount := Do_AWT_Cut_Off(
2445 gl_awt_date,
2446 current_amount_subject_for_tax,
2447 exception_withholding_amount,
2448 supplier_id,
2449 rec_group_taxes.tax_name,
2450 rec_group_taxes.period_name,
2451 rec_group_taxes.awt_period_limit,
2452 current_calling_sequence) ;
2453
2454 -- Get withholding figures FOR CERTIFICATE (VALUES could be NULL):
2455 Get_Withholding_On_CertIFicate(rec_group_taxes.tax_name,
2456 current_amount_subject_for_tax,
2457 functional_currency,
2458 gl_awt_date,
2459 supplier_id,
2460 supplier_site_id,
2461 cert_withholding_rate,
2462 cert_withholding_rate_id,
2463 cert_withholding_amount,
2464 certificate_rate_notfound,
2465 current_calling_sequence);
2466 Savepoint BEFORE_CERTIFICATE;
2467
2468 -- Withholding Tax Rate FOR CertIFicate IS not NULL?
2469
2470 IF (cert_withholding_rate IS not NULL) THEN
2471 -- CertIFicate EXISTS
2472 -- Calculate proper WT amount FOR CERTIFICATE applying Cut Off:
2473
2474 cert_withholding_amount := Do_AWT_Cut_Off
2475 (gl_awt_date,
2476 current_amount_subject_for_tax,
2477 cert_withholding_amount,
2478 supplier_id,
2479 rec_group_taxes.tax_name,
2480 rec_group_taxes.period_name,
2481 rec_group_taxes.awt_period_limit,
2482 current_calling_sequence);
2483
2484 -- Confirm WT Rate AND Amount
2485
2486 IF (EXCEPTION_withholding_rate IS NULL) THEN
2487 withheld_amount := cert_withholding_amount;
2488 withheld_rate_id := cert_withholding_rate_id;
2489 ELSE
2490 withheld_amount := EXCEPTION_withholding_amount;
2491 withheld_rate_id := EXCEPTION_withholding_rate_id;
2492 END IF;
2493
2494 Insert_Temp_Distribution(P_Invoice_Id,
2495 supplier_id,
2496 P_Payment_Num,
2497 tab_group_id(g),
2498 rec_group_taxes.tax_name,
2499 rec_group_taxes.tax_code_combination_id,
2500 current_amount_subject_for_tax,
2501 withheld_amount,
2502 gl_awt_date,
2503 gl_period_name,
2504 rec_group_taxes.awt_period_type,
2505 rec_group_taxes.period_name,
2506 P_Checkrun_Name,
2507 withheld_rate_id,
2508 invoice_exchange_rate,
2509 functional_currency,
2510 functional_currency,
2511 NULL,
2512 current_calling_sequence,
2513 DO_HANDLE_BUCKET,
2514 P_Last_Updated_By,
2515 P_Last_Update_Login,
2516 P_Program_Application_Id,
2517 P_Program_Id,
2518 P_Request_Id,
2519 P_Calling_Module,
2520 P_Invoice_Payment_Id,
2524 ELSE -- cert_withholding_rate is null Bug 6894755
2521 p_checkrun_id => p_checkrun_id);
2522
2523 -- END IF;
2525
2526 -- CertIFicate NOT exists - Set Amount to Null:
2527 cert_withholding_amount := NULL;
2528
2529 -- SAVEPOINT:
2530 -- situation BEFORE the amount ranges have been processed
2531
2532 -- Get withholding figures FOR RANGES eventually INSERTing
2533 -- temporary distributions AND updating bucket:
2534
2535 Get_Withholding_On_Ranges (
2536 tab_group_id(g),
2537 rec_group_taxes.tax_name,
2538 rec_group_taxes.tax_code_combination_id,
2539 gl_period_name,
2540 rec_group_taxes.period_name,
2541 rec_group_taxes.awt_period_type,
2542 rec_group_taxes.awt_period_limit,
2543 P_Invoice_Id,
2544 P_Payment_Num,
2545 current_amount_subject_for_tax,
2546 invoice_exchange_rate,
2547 functional_currency,
2548 functional_currency,
2549 gl_awt_date,
2550 supplier_id,
2551 supplier_site_id,
2552 rec_group_taxes.range_amount_basis,
2553 rec_group_taxes.range_period_basis,
2554 P_Checkrun_Name,
2555 P_Last_Updated_By,
2556 P_Last_Update_Login,
2557 P_Program_Application_Id,
2558 P_Program_Id,
2559 P_Request_Id,
2560 P_Calling_Module,
2561 ranges_withholding_amount,
2562 number_of_ranges,
2563 ranges_rate_notfound,
2564 incorrect_ranges,
2565 incorrect_range_dates,
2566 current_calling_sequence,
2567 P_Invoice_Payment_Id,
2568 NULL,
2569 cert_withholding_rate ,
2570 cert_withholding_rate_id,
2571 p_checkrun_id);
2572
2573 IF incorrect_range_dates OR incorrect_ranges THEN
2574 invalid_group := tab_group_id(g);
2575 invalid_tax := rec_group_taxes.tax_name;
2576 EXIT; -- Stop processing taxes/group/invoice: error
2577 END IF;
2578
2579 IF (
2580 (currency_code <> FUNCTIONal_currency)
2581 AND
2582 (number_of_ranges > 1)
2583 ) THEN
2584 must_be_base_currency := TRUE;
2585 invalid_group := tab_group_id(g);
2586 invalid_tax := rec_group_taxes.tax_name;
2587 EXIT;
2588 END IF;
2589
2590 -- Were RANGES applicable?
2591
2592 IF (number_of_ranges > 0
2593 AND
2594 currency_code = FUNCTIONal_currency) THEN
2595 -- Ranges were APPLICABLE ...
2596
2597 IF (EXCEPTION_withholding_rate IS not NULL) THEN
2598
2599 -- If EXCEPTION exists THEN always take the EXCEPTION even
2600 -- IF it's less than range.
2601 -- Made changes FOR Bug # 429166.
2602
2603 -- ... but the EXCEPTION IS stronger. Undo anything FOR
2604 -- the ranges, AND INSERT FOR EXCEPTION updating the
2605 -- corresponding bucket:
2606
2607 ROLLBACK TO BEFORE_CERTIFICATE ;
2608
2609 Insert_Temp_Distribution (
2610 P_Invoice_Id,
2611 supplier_id,
2612 P_Payment_Num,
2613 tab_group_id(g),
2614 rec_group_taxes.tax_name,
2615 rec_group_taxes.tax_code_combination_id,
2616 current_amount_subject_for_tax,
2617 EXCEPTION_withholding_amount,
2618 gl_awt_date,
2619 gl_period_name,
2620 rec_group_taxes.awt_period_type,
2621 rec_group_taxes.period_name,
2622 P_Checkrun_Name,
2623 EXCEPTION_withholding_rate_id,
2624 invoice_exchange_rate,
2625 FUNCTIONal_currency,
2626 FUNCTIONal_currency,
2627 NULL,
2628 current_calling_sequence,
2629 DO_HANDLE_BUCKET,
2630 P_Last_Updated_By,
2631 P_Last_Update_Login,
2632 P_Program_Application_Id,
2633 P_Program_Id,
2634 P_Request_Id,
2635 P_Calling_Module,
2636 P_Invoice_Payment_Id,
2637 p_checkrun_id => p_checkrun_id);
2638
2639 withheld_amount := EXCEPTION_withholding_amount;
2640 ELSE
2641 withheld_amount := ranges_withholding_amount;
2642 END IF;
2643 ELSE
2644 -- Ranges were NOT applicable
2645 -- Get withholding figures FOR the normal case:
2646
2647 Get_Normal_Withholding (
2648 rec_group_taxes.tax_name,
2649 current_amount_subject_for_tax,
2653 normal_withholding_rate_id,
2650 functional_currency,
2651 gl_awt_date,
2652 normal_withholding_rate,
2654 normal_withholding_amount,
2655 normal_rate_notfound,
2656 current_calling_sequence);
2657
2658 normal_withholding_amount := Do_AWT_Cut_Off(
2659 gl_awt_date,
2660 current_amount_subject_for_tax,
2661 normal_withholding_amount,
2662 supplier_id,
2663 rec_group_taxes.tax_name,
2664 rec_group_taxes.period_name,
2665 rec_group_taxes.awt_period_limit,
2666 current_calling_sequence);
2667
2668 -- Confirm WT Rate AND Amount
2669 -- If there IS an EXCEPTION THEN always take EXCEPTION rate
2670 -- even IF it's less than normal.
2671
2672 IF (EXCEPTION_withholding_rate IS NULL) THEN
2673 withheld_amount := normal_withholding_amount;
2674 withheld_rate_id := normal_withholding_rate_id;
2675 ELSE
2676 withheld_amount := EXCEPTION_withholding_amount;
2677 withheld_rate_id := EXCEPTION_withholding_rate_id;
2678 END IF;
2679
2680 Insert_Temp_Distribution (
2681 P_Invoice_Id,
2682 supplier_id,
2683 P_Payment_Num,
2684 tab_group_id(g),
2685 rec_group_taxes.tax_name,
2686 rec_group_taxes.tax_code_combination_id,
2687 current_amount_subject_for_tax,
2688 withheld_amount,
2689 gl_awt_date,
2690 gl_period_name,
2691 rec_group_taxes.awt_period_type,
2692 rec_group_taxes.period_name,
2693 P_Checkrun_Name,
2694 withheld_rate_id,
2695 invoice_exchange_rate,
2696 functional_currency,
2697 functional_currency,
2698 NULL,
2699 current_calling_sequence,
2700 DO_HANDLE_BUCKET,
2701 P_Last_Updated_By,
2702 P_Last_Update_Login,
2703 P_Program_Application_Id,
2704 P_Program_Id,
2705 P_Request_Id,
2706 P_Calling_Module,
2707 P_Invoice_Payment_Id,
2708 p_checkrun_id => p_checkrun_id);
2709
2710 END IF; -- Whether Ranges were applicable OR not
2711 END IF; -- cert_withholding_rateis not null Bug 6894755
2712
2713 <<End_Processing_Current_Tax>>
2714
2715 IF (
2716 custom_rate_NOTFOUND
2717 AND
2718 EXCEPTION_rate_NOTFOUND
2719 AND
2720 certIFicate_rate_NOTFOUND
2721 AND
2722 normal_rate_NOTFOUND
2723 AND
2724 ranges_rate_NOTFOUND
2725 ) THEN
2726 no_valid_rates_exist := TRUE;
2727 invalid_group := tab_group_id(g);
2728 invalid_tax := rec_group_taxes.tax_name;
2729 END IF;
2730 EXIT WHEN no_valid_rates_exist;
2731
2732 END Consider_One_Withholding_Tax;
2733 END LOOP For_Each_Tax_In_Group;
2734
2735 debug_info := 'Close CURSOR c_group_taxes';
2736 CLOSE c_group_taxes;
2737
2738 IF incorrect_ranges THEN
2739 RAISE INVALID_RANGE;
2740 END IF;
2741
2742 IF one_invalid_tax_exists THEN
2743 RAISE ONE_INVALID_TAX;
2744 END IF;
2745
2746 IF one_invalid_gl_acct_exists THEN
2747 RAISE ONE_INVALID_TAX_ACCOUNT;
2748 END IF;
2749
2750 IF one_tax_missing_period_exists THEN
2751 RAISE ONE_TAX_MISSING_PERIOD;
2752 END IF;
2753
2754 IF no_valid_rates_exist THEN
2755 RAISE NO_VALID_TAX_RATES;
2756 END IF;
2757
2758 IF incorrect_range_dates THEN
2759 RAISE INVALID_RANGE_DATES;
2760 END IF;
2761
2762 IF must_be_base_currency THEN
2763 RAISE INV_CURR_MUST_BE_BASE;
2764 END IF;
2765
2766 END LOOP For_Each_Withholding_Group;
2767 END Process_Withholding;
2768
2769 EXCEPTION
2770 WHEN NOT_AN_OPEN_GL_PERIOD THEN
2771 DECLARE
2772 error_text VARCHAR2(2000);
2773 BEGIN
2774 error_text := Ap_Utilities_Pkg.Ap_Get_Displayed_Field('AWT ERROR',
2775 'GL PERIOD NOT OPEN');
2776 P_AWT_Success := error_text;
2777 END;
2778
2779 WHEN INVALID_RANGE THEN
2780 DECLARE
2781 invalid_group_name ap_awt_groups.name%TYPE;
2782 error_text VARCHAR2(2000);
2783 BEGIN
2784
2785 invalid_group_name := Get_Group_Name(invalid_group
2786 ,current_calling_sequence);
2790 Ap_Utilities_Pkg.Ap_Get_Displayed_Field('NLS TRANSLATION'
2787 error_text := Ap_Utilities_Pkg.Ap_Get_Displayed_Field('AWT ERROR'
2788 ,'AWT TAX RANGE INVALID')||
2789 ' - '||
2791 ,'TAX')||
2792 ' '||invalid_tax||' '||
2793 Ap_Utilities_Pkg.Ap_Get_Displayed_Field('NLS TRANSLATION'
2794 ,'GROUP')||
2795 ' '||invalid_group_name;
2796 P_AWT_Success := error_text;
2797 END;
2798
2799 WHEN ONE_INVALID_GROUP THEN
2800 DECLARE
2801 invalid_group_name ap_awt_groups.name%TYPE;
2802 error_text VARCHAR2(2000);
2803 BEGIN
2804 invalid_group_name := Get_Group_Name(invalid_group
2805 ,current_calling_sequence);
2806 error_text := Ap_Utilities_Pkg.Ap_Get_Displayed_Field('AWT ERROR',
2807 'AWT GROUP INACTIVE')||
2808 ' - '||invalid_group_name;
2809 P_AWT_Success := error_text;
2810 END;
2811
2812 WHEN ONE_INVALID_TAX THEN
2813 DECLARE
2814 invalid_group_name ap_awt_groups.name%TYPE;
2815 error_text VARCHAR2(2000);
2816 BEGIN
2817 invalid_group_name := Get_Group_Name(invalid_group
2818 ,current_calling_sequence);
2819 error_text := Ap_Utilities_Pkg.Ap_Get_Displayed_Field('AWT ERROR'
2820 ,'AWT TAX INACTIVE')||
2821 ' - '||
2822 Ap_Utilities_Pkg.Ap_Get_Displayed_Field('NLS TRANSLATION'
2823 ,'TAX')||
2824 ' '||invalid_tax||' '||
2825 Ap_Utilities_Pkg.Ap_Get_Displayed_Field('NLS TRANSLATION'
2826 ,'GROUP')||
2827 ' '||invalid_group_name;
2828 P_AWT_Success := error_text;
2829 END;
2830
2831 WHEN ONE_INVALID_TAX_ACCOUNT THEN
2832 DECLARE
2833 invalid_group_name ap_awt_groups.name%TYPE;
2834 error_text VARCHAR2(2000);
2835 BEGIN
2836 invalid_group_name := Get_Group_Name(invalid_group
2837 ,current_calling_sequence);
2838 error_text := Ap_Utilities_Pkg.Ap_Get_Displayed_Field('AWT ERROR'
2839 ,'AWT TAX ACCOUNT INVALID')||
2840 ' - '||
2841 Ap_Utilities_Pkg.Ap_Get_Displayed_Field('NLS TRANSLATION'
2842 ,'TAX')||
2843 ' '||invalid_tax||' '||
2844 Ap_Utilities_Pkg.Ap_Get_Displayed_Field('NLS TRANSLATION'
2845 ,'GROUP')||
2846 ' '||invalid_group_name;
2847 P_AWT_Success := error_text;
2848 END;
2849
2850 WHEN ONE_TAX_MISSING_PERIOD THEN
2851 DECLARE
2852 invalid_group_name ap_awt_groups.name%TYPE;
2853 error_text VARCHAR2(2000);
2854 BEGIN
2855 invalid_group_name := Get_Group_Name(invalid_group
2856 ,current_calling_sequence);
2857 error_text := Ap_Utilities_Pkg.Ap_Get_Displayed_Field('AWT ERROR'
2858 ,'NO AWT PERIOD')||
2859 ' - '||
2860 Ap_Utilities_Pkg.Ap_Get_Displayed_Field('NLS TRANSLATION'
2861 ,'TAX')||
2862 ' '||invalid_tax||' '||
2863 Ap_Utilities_Pkg.Ap_Get_Displayed_Field('NLS TRANSLATION'
2864 ,'GROUP')||
2865 ' '||invalid_group_name;
2866 P_AWT_Success := error_text;
2867 END;
2868
2869 WHEN NO_VALID_TAX_RATES THEN
2870 DECLARE
2871 invalid_group_name ap_awt_groups.name%TYPE;
2872 error_text VARCHAR2(2000);
2873 BEGIN
2874 invalid_group_name := Get_Group_Name(invalid_group
2875 ,current_calling_sequence);
2876 error_text := Ap_Utilities_Pkg.Ap_Get_Displayed_Field('AWT ERROR'
2877 ,'NO AWT RATE')||
2878 ' - '||
2879 Ap_Utilities_Pkg.Ap_Get_Displayed_Field('NLS TRANSLATION'
2880 ,'TAX')||
2881 ' '||invalid_tax||' '||
2882 Ap_Utilities_Pkg.Ap_Get_Displayed_Field('NLS TRANSLATION'
2883 ,'GROUP')||
2884 ' '||invalid_group_name;
2885 P_AWT_Success := error_text;
2886 END;
2887
2888 WHEN INVALID_RANGE_DATES THEN
2889 DECLARE
2890 invalid_group_name ap_awt_groups.name%TYPE;
2891 error_text VARCHAR2(2000);
2892 BEGIN
2893 invalid_group_name := Get_Group_Name(invalid_group
2894 ,current_calling_sequence);
2895 error_text := Ap_Utilities_Pkg.Ap_Get_Displayed_Field('AWT ERROR'
2896 ,'INVALID RANGE DATES')||
2897 ' - '||
2898 Ap_Utilities_Pkg.Ap_Get_Displayed_Field('NLS TRANSLATION'
2899 ,'TAX')||
2900 ' '||invalid_tax||' '||
2901 Ap_Utilities_Pkg.Ap_Get_Displayed_Field('NLS TRANSLATION'
2902 ,'GROUP')||
2903 ' '||invalid_group_name;
2904 P_AWT_Success := error_text;
2905 END;
2906
2907 WHEN INV_CURR_MUST_BE_BASE THEN
2908 DECLARE
2909 invalid_group_name ap_awt_groups.name%TYPE;
2910 error_text VARCHAR2(2000);
2911 BEGIN
2912 invalid_group_name := Get_Group_Name(invalid_group
2913 ,current_calling_sequence);
2914 error_text := Ap_Utilities_Pkg.Ap_Get_Displayed_Field('AWT ERROR'
2915 ,'INV CURR MUST BE BASE')||
2916 ' - '||
2917 Ap_Utilities_Pkg.Ap_Get_Displayed_Field('NLS TRANSLATION'
2918 ,'TAX')||
2919 ' '||invalid_tax||' '||
2920 Ap_Utilities_Pkg.Ap_Get_Displayed_Field('NLS TRANSLATION'
2921 ,'GROUP')||
2922 ' '||invalid_group_name;
2923 P_AWT_Success := error_text;
2924 END;
2925
2926 WHEN ALL_GROUPS_ZERO THEN
2927 NULL;
2928
2929 WHEN OTHERS THEN
2930 DECLARE
2931 error_text VARCHAR2(512) := SUBSTR(SQLERRM, 1, 512);
2932 BEGIN
2933 P_AWT_Success := error_text;
2934
2935 IF (SQLCODE <> -20001) THEN
2936 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
2937 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
2938 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
2939 FND_MESSAGE.SET_TOKEN('PARAMETERS',
2940 ' Invoice Id = ' || to_char(P_Invoice_Id) ||
2941 ', Awt Date = ' || to_char(P_Awt_Date) ||
2942 ', Calling Module = ' || P_Calling_Module ||
2943 ', Create Dists = ' || P_Create_Dists ||
2944 ', Amount = ' || to_char(P_Amount) ||
2945 ', Payment Num = ' || to_char(P_Payment_Num) ||
2946 ', Checkrun Name = ' || P_Checkrun_Name);
2947
2948 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
2949 END IF;
2950 APP_EXCEPTION.RAISE_EXCEPTION;
2951 END;
2952
2953 END AP_Calculate_AWT_Amounts;
2954
2955
2956 END AP_CALC_WITHHOLDING_PKG;