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