[Home] [Help]
PACKAGE BODY: APPS.AP_WITHHOLDING_PKG
Source
1 PACKAGE BODY AP_WITHHOLDING_PKG AS
2 /* $Header: apdoawtb.pls 120.36.12010000.12 2009/02/20 07:14:53 udhenuko ship $ */
3
4 -- =====================================================================
5 -- P U B L I C O B J E C T S
6 -- =====================================================================
7
8 PROCEDURE Create_AWT_Distributions(
9 P_Invoice_Id IN NUMBER,
10 P_Calling_Module IN VARCHAR2,
11 P_Create_dists IN VARCHAR2,
12 P_Payment_Num IN NUMBER,
13 P_Currency_Code IN VARCHAR2,
14 P_Last_Updated_By IN NUMBER,
15 P_Last_Update_Login IN NUMBER,
16 P_Program_Application_Id IN NUMBER,
17 P_Program_Id IN NUMBER,
18 P_Request_Id IN NUMBER,
19 P_Calling_Sequence IN VARCHAR2)
20 IS
21 withholding_total NUMBER := 0;
22 base_withholding_total NUMBER := 0;
23 l_invoice_distribution_id ap_invoice_distributions.invoice_distribution_id%TYPE;
24
25 CURSOR C_temp_dists (InvId IN NUMBER) IS
26 SELECT AATD.invoice_id
27 , AATD.payment_num
28 , AATD.group_id
29 , AATD.tax_name
30 , AATD.tax_code_combination_id
31 , AATD.gross_amount
32 , AATD.withholding_amount
33 , AATD.base_withholding_amount
34 , AATD.accounting_date
35 , AATD.period_name
36 , AATD.checkrun_name
37 , AATD.tax_rate_id
38 , AATD.invoice_payment_id
39 , TC.tax_id tax_code_id
40 , AATD.GLOBAL_ATTRIBUTE_CATEGORY
41 , AATD.GLOBAL_ATTRIBUTE1
42 , AATD.GLOBAL_ATTRIBUTE2
43 , AATD.GLOBAL_ATTRIBUTE3
44 , AATD.GLOBAL_ATTRIBUTE4
45 , AATD.GLOBAL_ATTRIBUTE5
46 , AATD.GLOBAL_ATTRIBUTE6
47 , AATD.GLOBAL_ATTRIBUTE7
48 , AATD.GLOBAL_ATTRIBUTE8
49 , AATD.GLOBAL_ATTRIBUTE9
50 , AATD.GLOBAL_ATTRIBUTE10
51 , AATD.GLOBAL_ATTRIBUTE11
52 , AATD.GLOBAL_ATTRIBUTE12
53 , AATD.GLOBAL_ATTRIBUTE13
54 , AATD.GLOBAL_ATTRIBUTE14
55 , AATD.GLOBAL_ATTRIBUTE15
56 , AATD.GLOBAL_ATTRIBUTE16
57 , AATD.GLOBAL_ATTRIBUTE17
58 , AATD.GLOBAL_ATTRIBUTE18
59 , AATD.GLOBAL_ATTRIBUTE19
60 , AATD.GLOBAL_ATTRIBUTE20
61 , AI.org_id
62 , AATD.awt_related_id
63 , aatd.checkrun_id
64 , TC.description --Bug5502917
65 FROM ap_awt_temp_distributions_all AATD,
66 ap_invoices_all AI,
67 ap_tax_codes_all TC
68 WHERE AATD.invoice_id = InvId
69 AND AATD.invoice_id = AI.invoice_id
70 AND AATD.tax_name = TC.name(+)
71 AND TC.org_id = AI.org_id -- Bug5902006
72 AND TC.tax_type = 'AWT' -- Bug3665866
73 AND NVL(TC.enabled_flag,'Y') = 'Y'
74 AND ( P_Payment_Num IS NULL
75 OR AATD.payment_num = P_Payment_Num)
76 AND NVL(AI.invoice_date,SYSDATE) BETWEEN
77 NVL(TC.start_date,NVL(AI.invoice_date,SYSDATE)) AND
78 NVL(TC.inactive_date,NVL(AI.invoice_date,SYSDATE))
79 ORDER BY AATD.group_id,
80 AATD.tax_name,
81 AATD.tax_rate_id
82 FOR UPDATE of AATD.invoice_id;
83
84 rec_temp_dists c_temp_dists%ROWTYPE;
85
86 CURSOR c_invoice (InvId IN NUMBER) IS
87 SELECT AI.set_of_books_id
88 , AI.accts_pay_code_combination_id
89 , AI.batch_id
90 , AI.description
91 , AI.invoice_amount
92 , NVL(AI.payment_cross_rate,1) payment_cross_rate
93 , AI.payment_currency_code
94 , AI.exchange_date
95 , NVL(AI.exchange_rate, 1) exchange_rate
96 , AI.exchange_rate_type
97 --, AI.ussgl_transaction_code - Bug 4277744
98 --, AI.ussgl_trx_code_context - Bug 4277744
99 , AI.vat_code
100 , NVL(PV.federal_reportable_flag, 'N') federal_reportable_flag
101 , AI.vendor_site_id vendor_site_id
102 , AI.amount_applicable_to_discount
103 FROM ap_invoices_all AI,
104 po_vendors PV
105 WHERE PV.vendor_id = AI.vendor_id
106 AND AI.invoice_id = InvId
107 FOR UPDATE of AI.invoice_id;
108
109 rec_invoice c_invoice%ROWTYPE;
110
111 CURSOR C_Current_Line (InvId IN NUMBER)
112 IS
113 SELECT MAX(line_number) curr_inv_line_number
114 FROM ap_invoice_lines_all
115 WHERE (invoice_id = InvId);
116
117 curr_inv_line_number ap_invoice_lines_all.line_number%TYPE;
118
119 DBG_Loc VARCHAR2(30) := 'Create_AWT_distributions';
120
121 current_calling_sequence VARCHAR2(2000);
122 debug_info VARCHAR2(100);
123 l_disc_amt_factor NUMBER;
124 l_disc_amt_divisor NUMBER; -- BUG 7000143
125 l_basecur ap_system_parameters.base_currency_code%TYPE;
126 l_enable_1099_on_awt_flag ap_system_parameters.enable_1099_on_awt_flag%TYPE;
127 l_type_1099 ap_invoice_distributions.type_1099%TYPE;
128 l_combined_filing_flag ap_system_parameters.combined_filing_flag%TYPE;
129 l_income_tax_region_asp ap_system_parameters.income_tax_region%TYPE;
130 l_income_tax_region_pvs ap_system_parameters.income_tax_region%TYPE;
131 l_income_tax_region_flag ap_system_parameters.income_tax_region_flag%TYPE;
132 l_income_tax_region ap_system_parameters.income_tax_region%TYPE;
133
134 BEGIN
135 current_calling_sequence := 'AP_WITHHOLDING_PKG.Create_AWT_distributions<-' ||
136 P_Calling_Sequence;
137
138
139 debug_info := 'Get 1099 Info From ASP';
140
141
142 SELECT NVL(enable_1099_on_awt_flag, 'N'),
143 combined_filing_flag,
144 income_tax_region_flag,
145 income_tax_region,
146 base_currency_code
147 INTO l_enable_1099_on_awt_flag,
148 l_combined_filing_flag,
149 l_income_tax_region_flag,
150 l_income_tax_region_asp,
151 l_basecur
152 FROM ap_system_parameters_all asp,
153 ap_invoices_all ai
154 WHERE ai.org_id = asp.org_id
155 and ai.invoice_id = p_invoice_id;
156
157 debug_info := 'OPEN CURSOR C_Current_Line';
158 OPEN C_Current_line (P_Invoice_Id);
159
160 debug_info := 'Fetch CURSOR c_current_line';
161 FETCH C_Current_line INTO curr_inv_line_number;
162
163 debug_info := 'CLOSE CURSOR C_Current_Line';
164 CLOSE C_Current_Line;
165
166 debug_info := 'OPEN CURSOR c_invoice';
167 OPEN c_invoice (P_Invoice_Id);
168
169 debug_info := 'Fetch CURSOR c_invoice';
170 FETCH c_invoice INTO rec_invoice;
171
172 debug_info := 'Check 1099 Info From Rec_Invoice';
173 IF (l_enable_1099_on_awt_flag = 'Y') THEN
174 IF (rec_invoice.federal_reportable_flag = 'Y') THEN
175
176 l_type_1099 := 'MISC4';
177 IF (l_combined_filing_flag = 'Y') THEN
178 IF (l_income_tax_Region_flag = 'Y') THEN
179 BEGIN
180 SELECT SUBSTR(state, 1, 10)
181 INTO l_income_tax_region
182 FROM po_vendor_sites_all
183 WHERE vendor_site_id = rec_invoice.vendor_site_id
184 AND NVL(tax_reporting_site_flag, 'N') = 'Y';
185
186 EXCEPTION
187 WHEN NO_DATA_FOUND THEN
188 l_income_tax_region := NULL;
189 END;
190 ELSE
191 l_income_tax_region := l_income_tax_region_asp;
192 END IF;
193 ELSE
194 l_income_tax_region := NULL;
195 END IF;
196 ELSE
197 l_type_1099 := NULL;
198 END IF;
199 END IF;
200
201 debug_info := 'OPEN CURSOR c_temp_dists';
202 OPEN c_temp_dists (P_Invoice_Id);
203
204 <<FOR_EACH_TEMPORARY_DIST>>
205 LOOP
206 debug_info := 'Fetch CURSOR c_temp_dists';
207 FETCH c_temp_dists INTO rec_temp_dists;
208
209 EXIT WHEN c_temp_dists%NOTFOUND;
210
211 -- Increment the Invoice Line Number
212 curr_inv_line_number := curr_inv_line_number + 1;
213
214 -- Now we have obtained all the required information AND we can
215 -- create lines AND distributions.
216
217 debug_info := 'Insert INTO ap_invoice_lines_all';
218
219 INSERT INTO AP_INVOICE_LINES_all (
220 invoice_id,
221 line_number,
222 line_type_lookup_code,
223 description,
224 line_source,
225 generate_dists,
226 match_type,
227 prorate_across_all_items,
228 accounting_date,
229 period_name,
230 deferred_acctg_flag,
231 set_of_books_id,
232 amount,
233 base_amount,
234 rounding_amt,
235 wfapproval_status,
236 -- ussgl_transaction_code, - Bug 4277744
237 discarded_flag,
238 cancelled_flag,
239 income_tax_region,
240 type_1099,
241 final_match_flag,
242 assets_tracking_flag,
243 awt_group_id,
244 GLOBAL_ATTRIBUTE_CATEGORY,
245 GLOBAL_ATTRIBUTE1,
246 GLOBAL_ATTRIBUTE2,
247 GLOBAL_ATTRIBUTE3,
248 GLOBAL_ATTRIBUTE4,
249 GLOBAL_ATTRIBUTE5,
250 GLOBAL_ATTRIBUTE6,
251 GLOBAL_ATTRIBUTE7,
252 GLOBAL_ATTRIBUTE8,
253 GLOBAL_ATTRIBUTE9,
254 GLOBAL_ATTRIBUTE10,
255 GLOBAL_ATTRIBUTE11,
256 GLOBAL_ATTRIBUTE12,
257 GLOBAL_ATTRIBUTE13,
258 GLOBAL_ATTRIBUTE14,
259 GLOBAL_ATTRIBUTE15,
260 GLOBAL_ATTRIBUTE16,
261 GLOBAL_ATTRIBUTE17,
262 GLOBAL_ATTRIBUTE18,
263 GLOBAL_ATTRIBUTE19,
264 GLOBAL_ATTRIBUTE20,
265 creation_date,
266 created_by,
267 last_update_date,
268 last_updated_by,
269 last_update_login,
270 program_application_id,
271 program_id,
272 program_UPDATE_date,
273 request_id,
274 org_id, --7230158
275 pay_awt_group_id) --7230158
276 VALUES
277 ( P_Invoice_ID,
278 curr_inv_line_number,
279 'AWT',
280 rec_invoice.description,
281 'AUTO WITHHOLDING',
282 'D',
283 'NOT_MATCHED',
284 'N',
285 rec_temp_dists.accounting_date,
286 rec_temp_dists.period_name,
287 'N',
288 rec_invoice.set_of_books_id,
289 ap_utilities_pkg.ap_round_currency(
290 -rec_temp_dists.withholding_amount/rec_invoice.exchange_rate,
291 p_currency_code),
292 ap_utilities_pkg.ap_round_currency(
293 -rec_temp_dists.base_withholding_amount,
294 l_basecur),
295 0,
296 'NOT REQUIRED', /*bug 4994642, was 'NOT_REQUIRED' */
297 -- rec_invoice.ussgl_transaction_code, - Bug 4277744
298 'N',
299 'N',
300 l_income_tax_region,
301 l_type_1099,
302 'N',
303 'N',
304 decode (rec_temp_dists.invoice_payment_id,NULL, rec_temp_dists.group_id,NULL), --7230158
305 rec_temp_dists.GLOBAL_ATTRIBUTE_CATEGORY,
306 rec_temp_dists.GLOBAL_ATTRIBUTE1,
307 rec_temp_dists.GLOBAL_ATTRIBUTE2,
308 rec_temp_dists.GLOBAL_ATTRIBUTE3,
309 rec_temp_dists.GLOBAL_ATTRIBUTE4,
310 rec_temp_dists.GLOBAL_ATTRIBUTE5,
311 rec_temp_dists.GLOBAL_ATTRIBUTE6,
312 rec_temp_dists.GLOBAL_ATTRIBUTE7,
313 rec_temp_dists.GLOBAL_ATTRIBUTE8,
314 rec_temp_dists.GLOBAL_ATTRIBUTE9,
315 rec_temp_dists.GLOBAL_ATTRIBUTE10,
316 rec_temp_dists.GLOBAL_ATTRIBUTE11,
317 rec_temp_dists.GLOBAL_ATTRIBUTE12,
318 rec_temp_dists.GLOBAL_ATTRIBUTE13,
319 rec_temp_dists.GLOBAL_ATTRIBUTE14,
320 rec_temp_dists.GLOBAL_ATTRIBUTE15,
321 rec_temp_dists.GLOBAL_ATTRIBUTE16,
322 rec_temp_dists.GLOBAL_ATTRIBUTE17,
323 rec_temp_dists.GLOBAL_ATTRIBUTE18,
324 rec_temp_dists.GLOBAL_ATTRIBUTE19,
325 rec_temp_dists.GLOBAL_ATTRIBUTE20,
326 SYSDATE,
327 P_Last_Updated_By,
328 SYSDATE,
329 P_Last_Updated_By,
330 P_Last_Update_Login,
331 P_Program_Application_ID,
332 P_Program_ID,
333 SYSDATE,
334 P_request_ID,
335 rec_temp_dists.org_id, --7230158
336 decode (rec_temp_dists.invoice_payment_id,NULL,NULL,rec_temp_dists.group_id)); --7230158
337 debug_info := 'Insert INTO ap_invoice_distributions';
338
339 INSERT INTO ap_invoice_distributions_all (
340 accounting_date
341 ,accrual_posted_flag
342 ,assets_addition_flag
343 ,assets_tracking_flag
344 ,cash_posted_flag
345 ,distribution_line_number
346 ,dist_code_combination_id
347 ,invoice_id
348 ,invoice_line_number
349 ,last_updated_by
350 ,last_update_date
351 ,line_type_lookup_code
352 ,period_name
353 ,set_of_books_id
354 ,amount
355 ,base_amount
356 ,batch_id
357 ,created_by
358 ,creation_date
359 ,description
360 ,last_update_login
361 ,match_status_flag
362 ,posted_flag
363 ,program_application_id
364 ,program_id
365 ,program_UPDATE_date
366 ,request_id
367 ,withholding_tax_code_id /* Bug 5382525 */
368 ,encumbered_flag
369 ,pa_addition_flag
370 ,posted_amount
371 ,posted_base_amount
372 -- ,ussgl_transaction_code - Bug 4277744
373 -- ,ussgl_trx_code_context - Bug 4277744
374 ,awt_flag
375 ,awt_tax_rate_id
376 ,awt_gross_amount
377 ,awt_origin_group_id
378 ,awt_invoice_payment_id
379 ,invoice_distribution_id
380 ,GLOBAL_ATTRIBUTE_CATEGORY
381 ,GLOBAL_ATTRIBUTE1
382 ,GLOBAL_ATTRIBUTE2
383 ,GLOBAL_ATTRIBUTE3
384 ,GLOBAL_ATTRIBUTE4
385 ,GLOBAL_ATTRIBUTE5
386 ,GLOBAL_ATTRIBUTE6
387 ,GLOBAL_ATTRIBUTE7
388 ,GLOBAL_ATTRIBUTE8
389 ,GLOBAL_ATTRIBUTE9
390 ,GLOBAL_ATTRIBUTE10
391 ,GLOBAL_ATTRIBUTE11
392 ,GLOBAL_ATTRIBUTE12
393 ,GLOBAL_ATTRIBUTE13
394 ,GLOBAL_ATTRIBUTE14
395 ,GLOBAL_ATTRIBUTE15
396 ,GLOBAL_ATTRIBUTE16
397 ,GLOBAL_ATTRIBUTE17
398 ,GLOBAL_ATTRIBUTE18
399 ,GLOBAL_ATTRIBUTE19
400 ,GLOBAL_ATTRIBUTE20
401 ,type_1099
402 ,income_tax_region
403 ,org_id
404 ,awt_related_id
405 --Freight and Special Charges
406 ,rcv_charge_addition_flag
407 --,distribution_class --bug6749513 Removed for bug7719929
408 )
409 VALUES
410 (
411 rec_temp_dists.accounting_date
412 ,'N'
413 ,'N'
414 ,'N'
415 ,'N'
416 ,1 -- distribution_line_number
417 ,rec_temp_dists.tax_code_combination_id
418 ,P_Invoice_Id
419 ,curr_inv_line_number -- invoice_line_number
420 ,P_Last_Updated_By
421 ,SYSDATE
422 ,'AWT'
423 ,rec_temp_dists.period_name
424 ,rec_invoice.set_of_books_id
425 ,ap_utilities_pkg.ap_round_currency(
426 -rec_temp_dists.withholding_amount/rec_invoice.exchange_rate,
427 p_currency_code)
428 ,ap_utilities_pkg.ap_round_currency(-rec_temp_dists.base_withholding_amount,
429 l_basecur)
430 ,rec_invoice.batch_id
431 ,P_Last_Updated_By
432 ,SYSDATE
433 ,rec_temp_dists.description --Bug5502917 Replaced rec_invoice.description
434 ,P_Last_Update_Login
435 ,decode (P_Calling_Module, 'INVOICE ENTRY','N',
436 'INVOICE INQUIRY','N',
437 'A')
438 ,'N'
439 ,P_Program_Application_Id
440 ,P_Program_Id
441 ,decode (P_Program_Id,NULL,NULL,SYSDATE)
442 ,P_Request_Id
443 ,rec_temp_dists.tax_code_id
444 ,'T'
445 ,'E'
446 ,0
447 ,0
448 -- ,rec_invoice.ussgl_transaction_code - Bug 4277744
449 -- ,rec_invoice.ussgl_trx_code_context - Bug 4277744
450 ,decode (P_Calling_Module, 'AWT REPORT', 'P',
451 'A')
452 ,rec_temp_dists.tax_rate_id
453 ,ap_utilities_pkg.ap_round_currency(
454 rec_temp_dists.gross_amount/rec_invoice.exchange_rate,
455 P_currency_code)
456 ,rec_temp_dists.group_id
457 ,rec_temp_dists.invoice_payment_id
458 ,ap_invoice_distributions_s.nextval
459 ,rec_temp_dists.GLOBAL_ATTRIBUTE_CATEGORY
460 ,rec_temp_dists.GLOBAL_ATTRIBUTE1
461 ,rec_temp_dists.GLOBAL_ATTRIBUTE2
462 ,rec_temp_dists.GLOBAL_ATTRIBUTE3
463 ,rec_temp_dists.GLOBAL_ATTRIBUTE4
464 ,rec_temp_dists.GLOBAL_ATTRIBUTE5
465 ,rec_temp_dists.GLOBAL_ATTRIBUTE6
466 ,rec_temp_dists.GLOBAL_ATTRIBUTE7
467 ,rec_temp_dists.GLOBAL_ATTRIBUTE8
468 ,rec_temp_dists.GLOBAL_ATTRIBUTE9
469 ,rec_temp_dists.GLOBAL_ATTRIBUTE10
470 ,rec_temp_dists.GLOBAL_ATTRIBUTE11
471 ,rec_temp_dists.GLOBAL_ATTRIBUTE12
472 ,rec_temp_dists.GLOBAL_ATTRIBUTE13
473 ,rec_temp_dists.GLOBAL_ATTRIBUTE14
474 ,rec_temp_dists.GLOBAL_ATTRIBUTE15
475 ,rec_temp_dists.GLOBAL_ATTRIBUTE16
476 ,rec_temp_dists.GLOBAL_ATTRIBUTE17
477 ,rec_temp_dists.GLOBAL_ATTRIBUTE18
478 ,rec_temp_dists.GLOBAL_ATTRIBUTE19
479 ,rec_temp_dists.GLOBAL_ATTRIBUTE20
480 ,l_type_1099
481 ,l_income_tax_region
482 ,rec_temp_dists.org_id
483 ,rec_temp_dists.awt_related_id
484 ,'N'
485 --,'CANDIDATE' --bug6749513 Removed for bug7719929
486 );
487
488 --Bug 4539462 DBI logging
489 AP_DBI_PKG.Maintain_DBI_Summary
490 ( p_table_name => 'AP_INVOICE_DISTRIBUTIONS',
491 p_operation => 'I',
492 p_key_value1 => P_invoice_id,
493 p_key_value2 => l_Invoice_distribution_id,
494 p_calling_sequence => current_calling_sequence);
495
496
497 withholding_total := withholding_total +
498 ap_utilities_pkg.ap_round_currency(
499 rec_temp_dists.withholding_amount/
500 rec_invoice.exchange_rate,
501 p_currency_code);
502 base_withholding_total := base_withholding_total +
503 rec_temp_dists.base_withholding_amount;
504
505 END LOOP For_Each_Temporary_dist;
506
507 debug_info := 'CLOSE CURSOR c_temp_dists';
508 CLOSE c_temp_dists;
509
510 -- delete temp withholding lines for thIS invoice
511
512 debug_info := 'Delete From ap_awt_temp_distributions';
513
514 DELETE ap_awt_temp_distributions_all
515 WHERE invoice_id = p_invoice_id
516 AND (P_Payment_Num IS NULL OR payment_num = P_Payment_Num);
517
518 <<Update_Payment_Schedules>>
519 DECLARE
520 CURSOR c_payment_sched --bug6660355
521 (Createdists IN VARCHAR2
522 ,PaymNum IN NUMBER
523 ,InvId IN NUMBER
524 ) IS
525 SELECT gross_amount
526 , amount_remaining
527 , NVL(inv_curr_gross_amount, gross_Amount) inv_curr_gross_amount
528 FROM ap_payment_schedules_all
529 WHERE (invoice_id = InvId)
530 AND (payment_num = decode(Createdists
531 ,'APPROVAL',payment_num, 'BOTH',payment_num
532 ,PaymNum
533 ))
534 FOR UPDATE of amount_remaining;
535 rec_payment_sched c_payment_sched%ROWTYPE;
536
537 DBG_Loc VARCHAR2(30) := 'Update_Payment_Schedules';
538
539 BEGIN
540 debug_info := 'OPEN CURSOR c_payment_sched';
541 OPEN c_payment_sched(P_Create_dists
542 ,P_Payment_Num
543 ,P_Invoice_Id);
544
545 IF (P_Create_dists in ('APPROVAL','BOTH')) THEN
546 -- When withholding at approval time, LOOP on all possible payments
547 DECLARE
548 inv_amount_before_withholding NUMBER := rec_invoice.invoice_amount;
549 amount_to_subtract NUMBER;
550 pay_curr_amount_to_subtract NUMBER;
551 subtracting_cumulator NUMBER := 0;
552 CURSOR c_how_many_payments (InvId IN NUMBER)
553 IS
554 SELECT count(*) payments
555 FROM ap_payment_schedules_all
556 WHERE invoice_id = InvId;
557
558 num_payments NUMBER;
559 BEGIN
560 debug_info := 'OPEN CURSOR c_how_many_payments';
561 OPEN c_how_many_payments (P_Invoice_Id);
562
563 debug_info := 'Fetch CURSOR c_how_many_payments';
564 FETCH c_how_many_payments INTO num_payments;
565
566 debug_info := 'CLOSE CURSOR c_how_many_payments';
567 CLOSE c_how_many_payments;
568
569 <<FOR_EACH_PAYMENT>>
570
571 FOR j IN 1..num_payments LOOP
572
573 debug_info := 'Fetch CURSOR c_payment_sched';
574 FETCH c_payment_sched INTO rec_payment_sched;
575
576 IF (inv_amount_before_withholding = 0) THEN
577 amount_to_subtract := 0;
578 l_disc_amt_factor := 0;
579 ELSE
580 amount_to_subtract := withholding_total *
581 (rec_payment_sched.inv_curr_gross_amount /
582 inv_amount_before_withholding
583 );
584 amount_to_subtract := Ap_Utilities_Pkg.Ap_Round_Currency
585 (amount_to_subtract ,P_Currency_Code);
586
587
588 -- BUG 7000143 Old Code.
589 -- l_disc_amt_factor := withholding_total /
590 -- NVL(rec_invoice.amount_applicable_to_discount,
591 -- inv_amount_before_withholding);
592
593 -- BUG 7000143 New Code Start
594 l_disc_amt_divisor := NVL(rec_invoice.amount_applicable_to_discount,
595 inv_amount_before_withholding);
596 if l_disc_amt_divisor = 0 then
597 l_disc_amt_factor := 0;
598 else
599 l_disc_amt_factor := withholding_total /l_disc_amt_divisor;
600 end if;
601 -- BUG 7000143 End
602 END IF;
603
604 IF (j < num_payments) THEN
605 subtracting_cumulator := subtracting_cumulator +
606 amount_to_subtract;
607 ELSE
608 -- Get last amount to subtract FROM payments amounts by difference
609 -- (this is due to rounding reasons):
610 amount_to_subtract := withholding_total - subtracting_cumulator;
611 END IF;
612
613 pay_curr_amount_to_subtract := ap_utilities_pkg.ap_round_currency(
614 amount_to_subtract * rec_invoice.payment_cross_rate,
615 rec_invoice.payment_currency_code);
616
617 -- Update current payment schedule:
618 debug_info := 'Update current payment schedule';
619
620 UPDATE ap_payment_schedules_all
621 SET amount_remaining = amount_remaining -
622 pay_curr_amount_to_subtract,
623 -- iyas: Following code IS in DLD but was not found originally in file:
624 discount_amount_available = discount_amount_available -
625 ap_utilities_pkg.ap_round_currency(
626 discount_amount_available * l_disc_amt_factor,
627 rec_invoice.payment_currency_code),
628 second_disc_amt_available = second_disc_amt_available -
629 ap_utilities_pkg.ap_round_currency(
630 second_disc_amt_available * l_disc_amt_factor,
631 rec_invoice.payment_currency_code) ,
632 third_disc_amt_available = third_disc_amt_available -
633 ap_utilities_pkg.ap_round_currency(
634 third_disc_amt_available * l_disc_amt_factor,
635 rec_invoice.payment_currency_code)
636 WHERE CURRENT of c_payment_sched;
637
638 END LOOP For_Each_Payment;
639 END;
640 ELSIF (P_Calling_Module <> 'AWT REPORT') THEN
641 -- otherwise subtract total withholding FROM current payment
642 debug_info := 'Fetch CURSOR c_payment_sched';
643
644 FETCH c_payment_sched INTO rec_payment_sched;
645 debug_info := 'Update current payment schedule';
646
647 -- The withholding_total should be converted to payment
648 -- currency before substracting it FROM the amount remaining.
649
650 UPDATE ap_payment_schedules_all
651 SET amount_remaining = (amount_remaining -
652 ap_utilities_pkg.ap_round_currency(
653 withholding_total * rec_invoice.payment_cross_rate,
654 rec_invoice.payment_currency_code))
655 WHERE current of c_payment_sched;
656
657 END IF; -- whether withholding at approval time or not
658
659 debug_info := 'CLOSE CURSOR c_payment_sched';
660 CLOSE c_payment_sched;
661 END Update_Payment_Schedules;
662
663 <<UPDATE_INVOICE>>
664 debug_info := 'Update ap_invoices';
665 UPDATE ap_invoices_all
666 SET awt_flag = DECODE(P_Create_dists, 'APPROVAL', 'Y','BOTH','Y', NULL), --Bug6660355
667 amount_applicable_to_discount = decode (sign(invoice_amount),
668 -1, amount_applicable_to_discount,
669 amount_applicable_to_discount
670 - withholding_total)
671
672 WHERE CURRENT OF c_invoice;
673
674 debug_info := 'CLOSE CURSOR c_invoice';
675 CLOSE c_invoice;
676
677 EXCEPTION
678 WHEN OTHERS THEN
679 IF (SQLCODE <> -20001) THEN
680 FND_MESSAGE.set_NAME('SQLAP','AP_DEBUG');
681 FND_MESSAGE.set_TOKEN('ERROR',SQLERRM);
682 FND_MESSAGE.set_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
683 FND_MESSAGE.set_TOKEN('PARAMETERS',
684 ' Invoice Id = ' || to_char(P_Invoice_Id) ||
685 ', Calling module = ' || P_Calling_Module ||
686 ', Create dists = ' || P_Create_dists ||
687 ', Payment Num = ' || to_char(P_Payment_Num) ||
688 ', Currency code = ' || P_Currency_Code);
689
690 FND_MESSAGE.set_TOKEN('DEBUG_INFO',debug_info);
691 END IF;
692 APP_EXCEPTION.RAISE_EXCEPTION;
693
694 END Create_AWT_distributions;
695
696 PROCEDURE Create_AWT_Invoices(
697 P_Invoice_Id IN NUMBER,
698 P_Payment_Date IN DATE,
699 P_Last_Updated_By IN NUMBER,
700 P_Last_Update_Login IN NUMBER,
701 P_Program_Application_Id IN NUMBER,
702 P_Program_Id IN NUMBER,
703 P_Request_Id IN NUMBER,
704 P_Calling_Sequence IN VARCHAR2,
705 P_Calling_Module IN VARCHAR2, --Bug6660355
706 P_Inv_Line_No IN NUMBER DEFAULT NULL,
707 P_Dist_Line_No IN NUMBER DEFAULT NULL,
708 P_New_Invoice_Id IN NUMBER DEFAULT NULL,
709 P_create_dists IN VARCHAR2 DEFAULT NULL) --Bug7685907 bug8207324 bug8236169
710 IS
711 new_invoice_id ap_invoices.invoice_id%TYPE;
712 tax_authority_id ap_tax_codes.awt_vendor_id%TYPE;
713 tax_authority_site_id ap_tax_codes.awt_vendor_site_id%TYPE;
714 base_currency ap_system_parameters.base_currency_code%TYPE;
715 new_invoice_base_descr ap_invoices.description%TYPE;
716 inv_terms_date DATE;
717 ta_terms_id po_vendor_sites.terms_id%TYPE;
718 ta_payment_priority po_vendor_sites.payment_priority%TYPE;
719 ta_terms_date_basIS po_vendor_sites.terms_date_basIS%TYPE;
720 ta_pay_group_lookup_code po_vendor_sites.pay_group_lookup_code%TYPE;
721 ta_accts_pay_code_comb_id po_vendor_sites.accts_pay_code_combination_id%TYPE;
722 ta_payment_currency_code po_vendor_sites.payment_currency_code%TYPE;
723 c_payment_cross_rate ap_invoices.payment_cross_rate%TYPE;
724 c_payment_cross_rate_type ap_invoices.payment_cross_rate_type%TYPE;
725 l_invoice_distribution_id ap_invoice_distributions.invoice_distribution_id%TYPE;
726 l_legal_entity_id ap_invoices_all.legal_entity_id%type;
727
728
729 l_IBY_PAYMENT_METHOD varchar2(80);
730 l_PAYMENT_REASON varchar2(80);
731 l_BANK_CHARGE_BEARER_DSP varchar2(80);
732 l_DELIVERY_CHANNEL varchar2(80);
733 l_SETTLEMENT_PRIORITY_DSP varchar2(80);
734 l_bank_account_num varchar2(100);
735 l_bank_account_name varchar2(80);
736 l_bank_branch_name varchar2(360);
737 l_bank_branch_num varchar2(30);
738 l_bank_name varchar2(360);
739 l_bank_number varchar2(30);
740 l_PAYMENT_METHOD_CODE varchar2(30);
741 l_PAYMENT_REASON_CODE varchar2(30);
742 l_BANK_CHARGE_BEARER varchar2(30);
743 l_DELIVERY_CHANNEL_CODE varchar2(30);
744 l_SETTLEMENT_PRIORITY varchar2(30);
745 l_PAY_ALONE varchar2(30);
746 l_external_bank_account_id number;
747 l_exclusive_payment_flag varchar2(1);
748 l_party_id number;
749 l_party_site_id number;
750 l_payment_reason_comments varchar2(240); --4874927
751
752 --bug 7699166
753 l_remit_party_id NUMBER;
754 l_relationship_id NUMBER;
755 l_invoice_date DATE;
756 l_remit_to_supplier_name AP_SUPPLIERS.VENDOR_NAME%TYPE;
757 l_remit_to_supplier_id AP_SUPPLIERS.VENDOR_ID%TYPE;
758 l_remit_to_supplier_site AP_SUPPLIER_SITES.VENDOR_SITE_CODE%TYPE;
759 l_remit_to_supplier_site_id AP_SUPPLIER_SITES.VENDOR_SITE_ID%TYPE;
760 l_remit_to_party_site_id AP_SUPPLIER_SITES.PARTY_SITE_ID%TYPE; --7721149
761 --bug 7699166
762
763 --
764 CURSOR c_awt_lines (InvId IN NUMBER) IS
765 SELECT APID.accounting_date accounting_date
766 , APID.invoice_line_number invoice_line_number
767 , APID.distribution_line_number distribution_line_number
768 , APID.set_of_books_id set_of_books_id
769 , APID.dist_code_combination_id dist_code_combination_id
770 , APID.period_name period_name
771 , APID.withholding_tax_code_id tax_code_id /* Bug 5382525 */
772 , APID.amount amount
773 , APID.base_amount base_amount
774 , APID.batch_id batch_id
775 --, APID.ussgl_transaction_code ussgl_transaction_code - Bug 4277744
776 --, APID.ussgl_trx_code_context ussgl_trx_code_context - Bug 4277744
777 , APID.org_id
778 FROM ap_invoice_distributions_all APID,
779 ap_tax_codes_all ATC,
780 ap_invoices_all AI
781 WHERE (APID.invoice_id = InvId)
782 AND (APID.invoice_line_number = NVL(P_Inv_Line_No,invoice_line_number))
783 AND (APID.distribution_line_number = NVL(P_dist_Line_No,distribution_line_number))
784 AND (APID.line_type_lookup_code = 'AWT')
785 AND ((APID.awt_invoice_id IS NULL)
786 OR (APID.awt_invoice_id = P_New_Invoice_Id))
787 AND (NVL(APID.awt_flag , 'M' ) = 'A' )
788 AND APID.invoice_id = AI.invoice_id
789 AND APID.WITHHOLDING_TAX_CODE_ID = ATC.tax_id /* Bug 5382525 */
790 AND APID.amount <> decode (NVL(ATC.suppress_zero_amount_flag,
791 'N'), 'Y', 0 , APID.amount +1)
792 AND NVL(APID.reversal_flag, 'N') <> 'Y'
793 AND APID.AWT_ORIGIN_GROUP_ID = DECODE(P_calling_module,'AUTOAPPROVAL',AI.awt_group_id,
794 'CANCEL INVOICE',AI.awt_group_id,'CONFIRM',DECODE(P_create_dists,'APPROVAL',
795 AI.awt_group_id, AI.pay_awt_group_id),
796 'QUICKCHECK', DECODE(P_create_dists,'APPROVAL',
797 AI.awt_group_id,AI.pay_awt_group_id), AI.pay_awt_group_id) --6660355
798 --Bug 7685907 Added Decode for Confirm and Quickcheck
799 FOR UPDATE of awt_invoice_id;
800
801 rec_awt_lines c_awt_lines%ROWTYPE;
802 --
803 CURSOR c_base_invoice_description (InvId IN NUMBER) IS
804 SELECT substrb(
805 substrb(Ap_Utilities_Pkg.Ap_Get_DISplayed_Field('NLS TRANSLATION' , 'AWT'),1,25)||
806 ' - '||
807 v.vendor_name||
808 ' - '||
809 i.invoice_num||
810 ' /' --4940604
811 ,1
812 , 234
813 ) description,
814 i.legal_entity_id
815 FROM po_vendors v
816 , ap_invoices_all i
817 WHERE (v.vendor_id = i.vendor_id)
818 AND (i.invoice_id = InvId);
819
820 DBG_Loc VARCHAR2(30) := 'Create_AWT_Invoices';
821 current_calling_sequence VARCHAR2(2000);
822 debug_info VARCHAR2(100);
823 goods_received_date DATE;
824 invoice_received_date DATE;
825
826 BEGIN
827
828 current_calling_sequence := 'AP_WITHHOLDING_PKG.Create_AWT_Invoices<-' ||
829 P_Calling_Sequence;
830
831 -- Get base invoice description to insert in every new generated invoice
832
833 debug_info := 'OPEN CURSOR c_base_invoice_description';
834 OPEN c_base_invoice_description (P_Invoice_Id);
835
836 debug_info := 'Fetch CURSOR c_base_invoice_description';
837 FETCH c_base_invoice_description INTO new_invoice_base_descr,l_legal_entity_id;
838
839 debug_info := 'CLOSE CURSOR c_base_invoice_description';
840 CLOSE c_base_invoice_description;
841
842 debug_info := 'OPEN CURSOR c_awt_lines';
843 OPEN c_awt_lines (P_Invoice_Id);
844
845 <<FOR_EACH_NEGATIVE_LINE>>
846 LOOP
847 debug_info := 'Fetch CURSOR c_awt_lines';
848 FETCH c_awt_lines INTO rec_awt_lines;
849 EXIT WHEN c_awt_lines%NOTFOUND;
850
851 -- Start: Create invoice payable to Tax Authority for thIS negative line:
852 -- First get tax authority site AND new invoice_id from sequence:
853
854 <<TAX_AUTHORITY_INFO>>
855 DECLARE
856 CURSOR c_tax_authority (TaxId IN NUMBER)
857 IS
858 SELECT t.awt_vendor_id,
859 t.awt_vendor_site_id,
860 NVL(s.payment_currency_code, s.invoice_currency_code),
861 NVL(P_New_Invoice_Id, ap_invoices_s.nextval),
862 p.base_currency_code,
863 s.terms_id,
864 s.payment_priority,
865 s.terms_date_basis,
866 s.pay_group_lookup_code,
867 s.accts_pay_code_combination_id,
868 s.party_site_id,
869 pv.party_id
870 FROM ap_tax_codes_all t,
871 ap_system_parameters_all p,
872 po_vendor_sites_all s,
873 po_vendors pv
874 WHERE t.tax_id = TaxId
875 AND pv.vendor_id = s.vendor_id /* Bug 4724120 */
876 AND s.vendor_id = t.awt_vendor_id
877 AND s.vendor_site_id = t.awt_vendor_site_id
878 AND p.org_id = t.org_id;
879 BEGIN
880 debug_info := 'OPEN CURSOR c_tax_authority';
881 OPEN c_tax_authority(rec_awt_lines.tax_code_id);
882
883 debug_info := 'Fetch CURSOR c_tax_authority';
884 FETCH c_tax_authority
885 INTO tax_authority_id,
886 tax_authority_site_id,
887 ta_payment_currency_code,
888 new_invoice_id,
889 base_currency,
890 ta_terms_id,
891 ta_payment_priority,
892 ta_terms_date_basis,
893 ta_pay_group_lookup_code,
894 ta_accts_pay_code_comb_id,
895 l_party_site_id,
896 l_party_id;
897
898 IF c_tax_authority%NOTFOUND THEN
899 NULL;
900 END IF;
901
902 debug_info := 'CLOSE CURSOR c_tax_authority';
903 CLOSE c_tax_authority;
904 END Tax_Authority_Info;
905
906
907 /**
908 bug 7699166 -- The following call is made to set the remittance details
909 related to Third Party Payments
910 */
911 l_invoice_date := NVL(P_Payment_Date,rec_awt_lines.accounting_date);
912
913 IBY_EXT_PAYEE_RELSHIPS_PKG.default_Ext_Payee_Relationship(
914 p_party_id => l_party_id,
915 p_supplier_site_id => tax_authority_site_id,
916 p_date => l_invoice_date,
917 x_remit_party_id => l_remit_party_id,
918 x_remit_supplier_site_id => l_remit_to_supplier_site_id,
919 x_relationship_id => l_relationship_id
920 );
921
922
923 select vendor_id, vendor_name into l_remit_to_supplier_id, l_remit_to_supplier_name
924 from ap_suppliers where party_id = l_remit_party_id and rownum<2;
925
926 select party_site_id, vendor_site_code into l_remit_to_party_site_id,
927 l_remit_to_supplier_site from ap_supplier_sites where vendor_site_id = l_remit_to_supplier_site_id
928 and rownum<2;
929 -- retrieving party_site_id also as part of bug 7721149
930
931 --bug 7699166
932
933
934 --4610924, added this call to get payment attributes
935 ap_invoices_pkg.get_payment_attributes(
936 p_le_id =>l_legal_entity_id,
937 p_org_id =>rec_awt_lines.org_id,
938 p_payee_party_id => l_remit_party_id, --bug 7721149, replacing l_party_id for Third Party Payments
939 p_payee_party_site_id =>l_remit_to_party_site_id, --bug 7721149, replacing l_party_site_id for Third Party Payments
940 p_supplier_site_id =>l_remit_to_supplier_site_id, -- bug 7721149 replacing tax_authority_site_id
941 p_payment_currency =>ta_payment_currency_code,
942 p_payment_amount =>-NVL(rec_awt_lines.base_amount, rec_awt_lines.amount),
943 p_payment_function =>'PAYABLES_DISB',
944 p_pay_proc_trxn_type_code =>'PAYABLES_DOC',
945
946 p_PAYMENT_METHOD_CODE => l_payment_method_code,
947 p_PAYMENT_REASON_CODE => l_payment_reason_code,
948 p_BANK_CHARGE_BEARER => l_bank_charge_bearer,
949 p_DELIVERY_CHANNEL_CODE => l_delivery_channel_code,
950 p_SETTLEMENT_PRIORITY => l_settlement_priority,
951 p_PAY_ALONE => l_exclusive_payment_flag,
952 p_external_bank_account_id => l_external_bank_account_id,
953
954 p_IBY_PAYMENT_METHOD => l_IBY_PAYMENT_METHOD,
955 p_PAYMENT_REASON => l_PAYMENT_REASON,
956 p_BANK_CHARGE_BEARER_DSP => l_BANK_CHARGE_BEARER_DSP,
957 p_DELIVERY_CHANNEL => l_DELIVERY_CHANNEL,
958 p_SETTLEMENT_PRIORITY_DSP => l_SETTLEMENT_PRIORITY_DSP,
959 p_bank_account_num => l_bank_account_num,
960 p_bank_account_name => l_bank_account_name,
961 p_bank_branch_name => l_bank_branch_name,
962 p_bank_branch_num => l_bank_branch_num,
963 p_bank_name => l_bank_name,
964 p_bank_number => l_bank_number,
965 p_payment_reason_comments => l_payment_reason_comments); --4874927
966
967
968 debug_info := 'Get Exchange Rate'||'pc: '||ta_payment_currency_code||
969 ' bc: '||base_currency||' date: '||
970 to_char(rec_awt_lines.accounting_date, 'DD-MON-YYYY');
971 IF ( gl_currency_api.is_fixed_rate(ta_payment_currency_code,
972 base_currency,
973 rec_awt_lines.accounting_date) = 'Y' AND
974 ta_payment_currency_code <> base_currency ) THEN
975
976 c_payment_cross_rate := gl_currency_api.get_rate(base_currency,
977 ta_payment_currency_code,
978 rec_awt_lines.accounting_date,
979 'EMU FIXED');
980 c_payment_cross_rate_type := 'EMU FIXED';
981 ELSE
982 c_payment_cross_rate := 1;
983 ta_payment_currency_code := base_currency;
984 c_payment_cross_rate_type := '';
985 END IF;
986
987 IF ta_terms_date_basis IN ('Goods Received', 'Invoice Received') THEN
988 SELECT invoice_received_date,
989 goods_received_date
990 INTO invoice_received_date,
991 goods_received_date
992 FROM ap_invoices_all
993 WHERE invoice_id = P_Invoice_Id;
994 END IF;
995
996 debug_info := 'Insert Into ap_invoices';
997
998 INSERT INTO ap_invoices_all
999 (invoice_id
1000 ,last_UPDATE_date
1001 ,last_UPDATEd_by
1002 ,vendor_id
1003 ,invoice_num
1004 ,set_of_books_id
1005 ,invoice_currency_code
1006 ,payment_currency_code
1007 ,payment_cross_rate
1008 ,invoice_amount
1009 ,pay_curr_invoice_amount
1010 ,payment_cross_rate_type
1011 ,payment_cross_rate_date
1012 ,vendor_site_id
1013 ,amount_paid
1014 ,discount_amount_taken
1015 ,invoice_date
1016 ,source
1017 ,invoice_type_lookup_code
1018 ,description
1019 ,batch_id
1020 ,amount_applicable_to_discount
1021 ,terms_id
1022 ,terms_date
1023 ,pay_group_lookup_code
1024 ,accts_pay_code_combination_id
1025 ,payment_status_flag
1026 ,creation_date
1027 ,created_by
1028 ,last_UPDATE_login
1029 ,doc_sequence_id
1030 ,doc_sequence_value
1031 ,doc_category_code
1032 ,posting_status
1033 -- ,ussgl_transaction_code - Bug 4277744
1034 -- ,ussgl_trx_code_context - Bug 4277744
1035 ,payment_amount_total
1036 ,gl_date
1037 ,approval_ready_flag
1038 ,wfapproval_status
1039 ,org_id
1040 ,legal_entity_id
1041 ,auto_tax_calc_flag -- BUG 3007085
1042 ,PAYMENT_METHOD_CODE
1043 ,PAYMENT_REASON_CODE
1044 ,BANK_CHARGE_BEARER
1045 ,DELIVERY_CHANNEL_CODE
1046 ,SETTLEMENT_PRIORITY
1047 ,exclusive_payment_flag
1048 ,external_bank_account_id
1049 ,party_id
1050 ,party_site_id
1051 ,payment_reason_comments
1052 --bug 7699166 changes for Third Party Payments
1053 ,remit_to_supplier_name
1054 ,remit_to_supplier_id
1055 ,remit_to_supplier_site
1056 ,remit_to_supplier_site_id
1057 ,relationship_id
1058 --bug 7699166
1059 )
1060 VALUES
1061 (new_invoice_id
1062 ,SYSDATE
1063 ,5
1064 ,tax_authority_id
1065 ,DECODE( p_calling_sequence, 'AP_WITHHOLDING_PKG.AP_Undo_Withholding',
1066 substrb(Ap_Utilities_Pkg.Ap_Get_DISplayed_Field('NLS TRANSLATION', 'AWT'),1,25)
1067 ||' - '||to_char(P_invoice_id)||' - ' || to_char(rec_awt_lines.invoice_line_number)
1068 ||' - '|| to_char(rec_awt_lines.distribution_line_number)
1069 || ' - ' || Ap_Utilities_Pkg.Ap_Get_DISplayed_Field('NLS TRANSLATION','CANCELLED'),
1070 substrb(Ap_Utilities_Pkg.Ap_Get_DISplayed_Field('NLS TRANSLATION', 'AWT'),1,25)||
1071 ' - '||to_char(P_invoice_id)||' - ' || to_char(rec_awt_lines.invoice_line_number)
1072 ||' - '||to_char(rec_awt_lines.distribution_line_number)
1073 )
1074 ,rec_awt_lines.set_of_books_id
1075 ,base_currency
1076 ,ta_payment_currency_code
1077 ,c_payment_cross_rate
1078 ,-NVL(rec_awt_lines.base_amount, rec_awt_lines.amount)
1079 ,gl_currency_api.convert_amount(
1080 base_currency,
1081 ta_payment_currency_code,
1082 rec_awt_lines.accounting_date,
1083 c_payment_cross_rate_type,
1084 -NVL(rec_awt_lines.base_amount, rec_awt_lines.amount))
1085 ,c_payment_cross_rate_type
1086 ,rec_awt_lines.accounting_date
1087 ,tax_authority_site_id
1088 ,0
1089 ,0
1090 ,NVL(P_Payment_Date,rec_awt_lines.accounting_date)
1091 ,substrb(Ap_Utilities_Pkg.Ap_Get_DISplayed_Field('NLS TRANSLATION', 'AWT'),1,25)
1092 ,'AWT' -- It was: decode(sign(rec_awt_lines.amount),1,'CREDIT','STANDARD')
1093 ,new_invoice_base_descr
1094 ,rec_awt_lines.batch_id
1095 ,decode(sign(-NVL(rec_awt_lines.base_amount, rec_awt_lines.amount)),
1096 -1, 0, -NVL(rec_awt_lines.base_amount, rec_awt_lines.amount))
1097 ,ta_terms_id
1098 ,decode(ta_terms_date_basIS
1099 ,'Current', SYSDATE
1100 ,'Invoice', NVL(p_payment_date,
1101 rec_awt_lines.accounting_date)
1102 ,'Goods Received', NVL(goods_received_date,
1103 rec_awt_lines.accounting_date)
1104 ,'Invoice Received', NVL(invoice_received_date,
1105 rec_awt_lines.accounting_date)
1106 ,NULL)
1107 ,ta_pay_group_lookup_code
1108 ,ta_accts_pay_code_comb_id
1109 ,'N'
1110 ,SYSDATE
1111 ,5
1112 ,P_Last_Update_Login
1113 ,NULL
1114 ,NULL
1115 ,NULL
1116 ,'N'
1117 -- ,rec_awt_lines.ussgl_transaction_code - Bug 4277744
1118 -- ,rec_awt_lines.ussgl_trx_code_context - Bug 4277744
1119 ,NULL
1120 ,NVL(P_Payment_Date,rec_awt_lines.accounting_date)
1121 ,'Y'
1122 ,'NOT REQUIRED'
1123 ,rec_awt_lines.org_id
1124 ,l_legal_entity_id
1125 ,'N' -- BUG 3007085
1126 ,nvl(l_payment_method_code,'CHECK')
1127 ,l_payment_reason_code
1128 ,l_bank_charge_bearer
1129 ,l_delivery_channel_code
1130 ,l_settlement_priority
1131 ,l_exclusive_payment_flag
1132 ,l_external_bank_account_id
1133 ,l_party_id
1134 ,l_party_site_id
1135 ,l_payment_reason_comments --4874927
1136 --bug 7699166 changes for Third Party Payments
1137 ,l_remit_to_supplier_name
1138 ,l_remit_to_supplier_id
1139 ,l_remit_to_supplier_site
1140 ,l_remit_to_supplier_site_id
1141 ,l_relationship_id
1142 --bug 7699166
1143 );
1144
1145
1146 --Bug 4539462 DBI logging
1147 AP_DBI_PKG.Maintain_DBI_Summary
1148 (p_table_name => 'AP_INVOICES',
1149 p_operation => 'I',
1150 p_key_value1 => new_invoice_id,
1151 p_calling_sequence => current_calling_sequence);
1152
1153
1154 -- Insert Invoice Lines for each distribution Fetched
1155
1156 debug_info := 'Insert INTO ap_invoice_lines_all';
1157
1158 INSERT INTO AP_INVOICE_LINES_all (
1159 invoice_id,
1160 line_number,
1161 line_type_lookup_code,
1162 description,
1163 line_source,
1164 generate_dists,
1165 match_type,
1166 prorate_across_all_items,
1167 accounting_date,
1168 period_name,
1169 deferred_acctg_flag,
1170 set_of_books_id,
1171 amount,
1172 base_amount,
1173 rounding_amt,
1174 wfapproval_status,
1175 -- ussgl_transaction_code, - Bug 4277744
1176 discarded_flag,
1177 cancelled_flag,
1178 final_match_flag,
1179 assets_tracking_flag,
1180 creation_date,
1181 created_by,
1182 last_update_date,
1183 last_updated_by,
1184 last_update_login,
1185 program_application_id,
1186 program_id,
1187 program_update_date,
1188 request_id,
1189 org_id
1190 )
1191 VALUES
1192 (
1193 new_invoice_id,
1194 1,
1195 'ITEM' ,
1196 new_invoice_base_descr||to_char(rec_awt_lines.invoice_line_number),
1197 'AUTO INVOICE CREATION',
1198 'D',
1199 'NOT MATCHED',
1200 'N',
1201 NVL(P_Payment_Date,rec_awt_lines.accounting_date),
1202 NVL(ap_utilities_pkg.get_current_gl_date(P_Payment_Date, rec_awt_lines.org_id),
1203 rec_awt_lines.period_name),
1204 'N',
1205 rec_awt_lines.set_of_books_id,
1206 -NVL(rec_awt_lines.base_amount, rec_awt_lines.amount),
1207 null, -- bug 5190989
1208 0,
1209 'NOT REQUIRED',
1210 -- rec_awt_lines.ussgl_transaction_code, - Bug 4277744
1211 'N',
1212 'N',
1213 'N',
1214 'N',
1215 SYSDATE,
1216 P_Last_Updated_By,
1217 SYSDATE,
1218 P_Last_Updated_By,
1219 P_Last_Update_Login,
1220 P_Program_Application_ID,
1221 P_Program_ID,
1222 SYSDATE,
1223 P_request_ID,
1224 rec_awt_lines.org_id);
1225
1226 --To be resolved by DBI forward porting project.
1227 /*
1228 AP_DBI_PKG.Maintain_DBI_Summary
1229 (p_table_name => 'AP_INVOICE_DISTRIBUTIONS',
1230 p_operation => 'I',
1231 p_key_value1 => new_invoice_id,
1232 p_key_value2 => l_Invoice_distribution_Id,
1233 p_calling_sequence => current_calling_sequence); */
1234
1235 debug_info := 'Insert INTO ap_invoice_distributions';
1236
1237 SELECT ap_invoice_distributions_s.nextval
1238 INTO l_invoice_distribution_id
1239 FROM DUAL;
1240
1241
1242 INSERT INTO ap_invoice_distributions_all (
1243 accounting_date
1244 ,accrual_posted_flag
1245 ,assets_addition_flag
1246 ,assets_tracking_flag
1247 ,cash_posted_flag
1248 ,distribution_line_number
1249 ,dist_code_combination_id
1250 ,invoice_id
1251 ,invoice_line_number
1252 ,last_updated_by
1253 ,last_update_date
1254 ,line_type_lookup_code
1255 ,period_name
1256 ,set_of_books_id
1257 ,amount
1258 ,base_amount
1259 ,batch_id
1260 ,created_by
1261 ,creation_date
1262 ,description
1263 ,last_update_login
1264 ,match_status_flag
1265 ,posted_flag
1266 ,program_application_id
1267 ,program_id
1268 ,program_UPDATE_date
1269 ,request_id
1270 ,tax_code_id
1271 ,encumbered_flag
1272 ,pa_addition_flag
1273 ,posted_amount
1274 ,posted_base_amount
1275 ,awt_flag
1276 ,awt_tax_rate_id
1277 ,awt_gross_amount
1278 ,awt_origin_group_id
1279 ,awt_invoice_payment_id
1280 ,invoice_distribution_id
1281 ,GLOBAL_ATTRIBUTE_CATEGORY
1282 ,GLOBAL_ATTRIBUTE1
1283 ,GLOBAL_ATTRIBUTE2
1284 ,GLOBAL_ATTRIBUTE3
1285 ,GLOBAL_ATTRIBUTE4
1286 ,GLOBAL_ATTRIBUTE5
1287 ,GLOBAL_ATTRIBUTE6
1288 ,GLOBAL_ATTRIBUTE7
1289 ,GLOBAL_ATTRIBUTE8
1290 ,GLOBAL_ATTRIBUTE9
1291 ,GLOBAL_ATTRIBUTE10
1292 ,GLOBAL_ATTRIBUTE11
1293 ,GLOBAL_ATTRIBUTE12
1294 ,GLOBAL_ATTRIBUTE13
1295 ,GLOBAL_ATTRIBUTE14
1296 ,GLOBAL_ATTRIBUTE15
1297 ,GLOBAL_ATTRIBUTE16
1298 ,GLOBAL_ATTRIBUTE17
1299 ,GLOBAL_ATTRIBUTE18
1300 ,GLOBAL_ATTRIBUTE19
1301 ,GLOBAL_ATTRIBUTE20
1302 ,type_1099
1303 ,income_tax_region
1304 ,org_id
1305 ,awt_related_id
1306 --Freight and Special Charges
1307 ,rcv_charge_addition_flag
1308 ,distribution_class) --bug7719929
1309 VALUES
1310 (
1311 NVL(P_Payment_Date,rec_awt_lines.accounting_date)
1312 ,'N'
1313 ,'N'
1314 ,'N'
1315 ,'N'
1316 ,1 -- distribution_line_number
1317 ,rec_awt_lines.dist_code_combination_id
1318 ,new_Invoice_Id
1319 ,1 -- invoice_line_number
1320 ,P_Last_Updated_By
1321 ,SYSDATE
1322 ,'ITEM'
1323 , NVL(ap_utilities_pkg.get_current_gl_date(P_Payment_Date, rec_awt_lines.org_id),
1324 rec_awt_lines.period_name)
1325 ,rec_awt_lines.set_of_books_id
1326 ,-NVL(rec_awt_lines.base_amount, rec_awt_lines.amount)
1327 ,NULL -- base amount bug 5190989
1328 ,NULL -- batch_id
1329 ,P_Last_Updated_By
1330 ,SYSDATE
1331 ,new_invoice_base_descr||to_char(rec_awt_lines.distribution_line_number)
1332 ,P_Last_Update_Login
1333 ,NULL -- match_status_flag
1334 ,'N' -- posted_flag
1335 ,P_Program_Application_Id
1336 ,P_Program_Id
1337 ,decode (P_Program_Id,NULL,NULL,SYSDATE)
1338 ,P_Request_Id
1339 ,NULL -- tax_code_id
1340 ,'T' -- encumbered_flag
1341 ,'E' -- pa_addition_flag
1342 ,0
1343 ,0
1344 ,NULL -- awt_flag
1345 ,NULL -- awt_tax_rate_id
1346 ,NULL -- awt_gross_amount
1347 ,NULL -- awt_origin_group_id
1348 ,NULL -- awt_invoice_payment_id
1349 ,l_invoice_distribution_id
1350 ,NULL -- Global Attribute Category
1351 ,NULL -- Global Attribute1
1352 ,NULL
1353 ,NULL
1354 ,NULL
1355 ,NULL -- Global Attribute5
1356 ,NULL
1357 ,NULL
1358 ,NULL
1359 ,NULL
1360 ,NULL -- Global Attribute10
1361 ,NULL
1362 ,NULL
1363 ,NULL
1364 ,NULL
1365 ,NULL -- Global Attribute15
1366 ,NULL
1367 ,NULL
1368 ,NULL
1369 ,NULL
1370 ,NULL -- Global Attribute20
1371 ,NULL -- type_1099
1372 ,NULL -- income_tax_region
1373 ,rec_awt_lines.org_id
1374 ,NULL -- awt_related_id
1375 ,'N'
1376 ,'CANDIDATE'); --bug7719929
1377
1378 AP_DBI_PKG.Maintain_DBI_Summary
1379 ( p_table_name => 'AP_INVOICE_DISTRIBUTIONS',
1380 p_operation => 'I',
1381 p_key_value1 => new_invoice_id,
1382 p_key_value2 => l_invoice_distribution_id,
1383 p_calling_sequence => current_calling_sequence);
1384
1385 -- Prepare Terms_Date argument for Payment Schedule Creation
1386 -- PL/SQL
1387
1388 IF (ta_terms_date_basIS = 'Current') THEN
1389 inv_terms_date := SYSDATE;
1390 ELSIF (ta_terms_date_basIS = 'Invoice') THEN
1391 inv_terms_date := NVL(p_payment_date, rec_awt_lines.accounting_date);
1392 ELSIF (ta_terms_date_basIS = 'Goods Received') THEN
1393 inv_terms_date := NVL(goods_received_date, rec_awt_lines.accounting_date);
1394 ELSIF (ta_terms_date_basIS = 'Invoice Received') THEN
1395 inv_terms_date := NVL(invoice_received_date,
1396 rec_awt_lines.accounting_date);
1397 ELSE
1398 inv_terms_date := NULL;
1399 END IF;
1400
1401 -- Create payment schedule for thIS new invoice:
1402
1403 Ap_Create_Pay_Scheds_Pkg.Ap_Create_From_Terms
1404 (new_invoice_id
1405 ,ta_terms_id
1406 ,P_Last_Updated_By
1407 ,P_Last_Updated_By
1408 ,ta_payment_priority
1409 ,rec_awt_lines.batch_id
1410 ,inv_terms_date
1411 ,-NVL(rec_awt_lines.base_amount
1412 ,rec_awt_lines.amount
1413 )
1414 ,gl_currency_api.convert_amount(
1415 base_currency,
1416 ta_payment_currency_code,
1417 rec_awt_lines.accounting_date,
1418 c_payment_cross_rate_type,
1419 -NVL(rec_awt_lines.base_amount, rec_awt_lines.amount))
1420 ,c_payment_cross_rate
1421 ,NULL
1422 ,nvl(l_PAYMENT_METHOD_CODE,'CHECK')
1423 ,base_currency
1424 ,ta_payment_currency_code
1425 ,'ap_do_withholding');
1426
1427 -- End: Update original negative distribution with new invoice id:
1428
1429 debug_info := 'Update ap_invoice_distributions';
1430
1431 UPDATE ap_invoice_distributions_all
1432 SET awt_invoice_id = new_invoice_id
1433 WHERE current of c_awt_lines;
1434
1435 END LOOP For_Each_Negative_Line;
1436
1437 debug_info := 'CLOSE CURSOR c_awt_lines';
1438 CLOSE c_awt_lines;
1439
1440 EXCEPTION
1441 WHEN OTHERS THEN
1442 IF (SQLCODE <> -20001) THEN
1443 FND_MESSAGE.set_NAME('SQLAP','AP_DEBUG');
1444 FND_MESSAGE.set_TOKEN('ERROR',SQLERRM);
1445 FND_MESSAGE.set_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
1446 FND_MESSAGE.set_TOKEN('PARAMETERS',
1447 ' Invoice Id = ' || to_char(P_Invoice_Id) ||
1448 ', dist line no = ' || to_char(P_dist_Line_No) ||
1449 ', New Invoice Id = ' || to_char(P_New_Invoice_Id));
1450
1451 FND_MESSAGE.set_TOKEN('DEBUG_INFO',debug_info);
1452 END IF;
1453 APP_EXCEPTION.RAISE_EXCEPTION;
1454
1455 END Create_AWT_Invoices;
1456
1457
1458 PROCEDURE Ap_Do_Withholding (
1459 P_Invoice_Id IN NUMBER,
1460 P_Awt_Date IN DATE,
1461 P_Calling_Module IN VARCHAR2,
1462 P_Amount IN NUMBER,
1463 P_Payment_Num IN NUMBER DEFAULT NULL,
1464 P_Checkrun_Name IN VARCHAR2 DEFAULT NULL,
1465 P_Last_Updated_By IN NUMBER,
1466 P_Last_Update_Login IN NUMBER,
1467 P_Program_Application_Id IN NUMBER DEFAULT NULL,
1468 P_Program_Id IN NUMBER DEFAULT NULL,
1469 P_Request_Id IN NUMBER DEFAULT NULL,
1470 P_Awt_Success OUT NOCOPY VARCHAR2,
1471 P_Invoice_Payment_Id IN NUMBER DEFAULT NULL,
1472 P_Check_Id IN NUMBER DEFAULT NULL,
1473 p_checkrun_id in number default null)
1474 IS
1475 l_awt_flag ap_invoices.awt_flag%TYPE;
1476 l_inv_curr_code ap_invoices.invoice_currency_code%TYPE;
1477 l_tax_name ap_tax_codes.name%TYPE;
1478 l_payment_date DATE := p_awt_date;
1479 l_org_id number; --4742265
1480
1481 -- The variable "l_AWT_success" checks general WT calculations in the first
1482 -- processing unit (Create Temporary AWT distributions), causing a return
1483 -- error message in the following cases:
1484 -- o The invoice has one inactive group
1485 -- o One Tax in any group IS inactive
1486 -- o One Tax Account IS invalid
1487 -- o One Tax has no valid rate
1488
1489 l_AWT_success VARCHAR2(2000) := 'SUCCESS';
1490
1491 DBG_Loc VARCHAR2(30) := 'Ap_Do_Withholding';
1492 current_calling_sequence VARCHAR2(2000);
1493 debug_info VARCHAR2(100);
1494 --
1495 /*
1496
1497 << Beginning of Ap_Do_Withholding program documentation >>
1498
1499 ThIS IS the core PROCEDURE of the Automatic Withholding Tax feature. It
1500 can be invoked by five possible originating events:
1501
1502 - Invoice Autoapproval
1503 - AutoSELECT / Build Payments
1504 - Confirm Payment Batch
1505 - Invoice Entry / Inquiry
1506 - QuickCheck
1507
1508 Three dIFferent processing units ("Create Temporary AWT distributions",
1509 "Create AWT distributions" AND "Create AWT Invoices") are conditionally
1510 executed depENDing on the originating event triggering the Ap_Do_Withholding
1511 PROCEDURE, as represented in the following flow diagrams:
1512
1513 +=========================+
1514 | |
1515 | AutoApproval |
1516 | |
1517 +=========================+
1518 |
1519 |
1520 ^
1521 / \
1522 / \
1523 / \
1524 / create_ \ +------------------------------------+
1525 / dists = \_______| |
1526 \ APPROVAL / Yes | Create Temporary AWT distributions |
1527 \/BOTH / | |
1528 \ ? / +------------------+-----------------+
1529 \ / |
1530 \ / |
1531 v |
1532 No | |
1533 | +------------------+-----------------+
1534 | | |
1535 | | Create AWT distributions |
1536 | | |
1537 | +------------------+-----------------+
1538 | |
1539 +--------------------------------+
1540 |
1541 ^
1542 / \
1543 / \
1544 / \
1545 / create_ \ +------------------------------------+
1546 / invoices \_______| |
1547 \= APPROVAL / Yes | Create AWT Invoices |
1548 \ /BOTH / | |
1549 \ ? / +------------------+-----------------+
1550 \ / |
1551 \ / |
1552 v |
1553 No | |
1554 | |
1555 +--------------------------------+
1556 |
1557 +----+----+
1558 | DONE |
1559 +---------+
1560
1561 +===========================+
1562 | |
1563 | AutoSelect/Build Payments |
1564 | |
1565 +===========================+
1566 |
1567 |
1568 ^
1569 / \
1570 / \
1571 / \
1572 / create_ \ +------------------------------------+
1573 / dists = \_______| |
1574 \ PAYMENT / Yes | Create Temporary AWT distributions |
1575 \ /BOTH / | |
1576 \ ? / +------------------+-----------------+
1577 \ / |
1578 \ / |
1579 v |
1580 No | |
1581 +--------------------------------+
1582 |
1583 | +------------------------------------+
1584 | | |
1585 | | Create AWT distributions |
1586 | | |
1587 | +------------------------------------+
1588 |
1589 | +------------------------------------+
1590 | | |
1591 | | Create AWT Invoices |
1592 | | |
1593 | +------------------------------------+
1594 |
1595 +----+----+
1596 | DONE |
1597 +---------+
1598
1599
1600 +=========================+
1601 | |
1602 | Confirm Payment Batch |
1603 | |
1604 +=========================+
1605 |
1606 | +------------------------------------+
1607 | | |
1608 | | Create Temporary AWT distributions |
1609 | | |
1610 | +------------------------------------+
1611 ^
1612 / \
1613 / \
1614 / \
1615 / create_ \ +------------------------------------+
1616 / dists = \_______| |
1617 \ PAYMENT / Yes | Create AWT distributions |
1618 \ /BOTH / | |
1619 \ ? / +------------------+-----------------+
1620 \ / |
1621 \ / |
1622 v |
1623 No | |
1624 +--------------------------------+
1625 |
1626 ^
1627 / \
1628 / \
1629 / \
1630 / create_ \ +------------------------------------+
1631 / invoices \_______| |
1632 \ = PAYMENT / Yes | Create AWT Invoices |
1633 \ /BOTH / | |
1634 \ ? / +------------------+-----------------+
1635 \ / |
1636 \ / |
1637 v |
1638 No | |
1639 +--------------------------------+
1640 |
1641 +----+----+
1642 | DONE |
1643 +---------+
1644
1645
1646 +=========================+
1647 | |
1648 | Invoice Entry/Inquiry |
1649 | |
1650 +=========================+
1651 | +------------------------------------+
1652 |_____________| |
1653 | Create Temporary AWT distributions |
1654 | |
1655 +------------------+-----------------+
1656 |
1657 +--------------------------------+
1658 |
1659 | +------------------------------------+
1660 | | |
1661 | | Create AWT distributions |
1662 | | |
1663 | +------------------------------------+
1664 |
1665 | +------------------------------------+
1666 | | |
1667 | | Create AWT Invoices |
1668 | | |
1669 | +------------------------------------+
1670 +----+----+
1671 | DONE |
1672 +---------+
1673
1674
1675 +=========================+
1676 | |
1677 | QuickCheck |
1678 | |
1679 +=========================+
1680 |
1681 |
1682 ^
1683 / \
1684 / \
1685 / \
1686 / create_ \ +------------------------------------+
1687 / dists = \_______| |
1688 \ PAYMENT / Yes | Create Temporary AWT distributions |
1689 \ /BOTH / | |
1690 \ ? / +------------------+-----------------+
1691 \ / |
1692 \ / |
1693 v |
1694 No | |
1695 | +------------------+-----------------+
1696 | | |
1697 | | Create AWT distributions |
1698 | | |
1699 | +------------------+-----------------+
1700 | |
1701 +--------------------------------+
1702 |
1703 ^
1704 / \
1705 / \
1706 / \
1707 / create_ \ +------------------------------------+
1708 / invoices \_______| |
1709 \ = PAYMENT / Yes | Create AWT Invoices |
1710 \ /BOTH / | |
1711 \ ? / +------------------+-----------------+
1712 \ / |
1713 \ / |
1714 v |
1715 No | |
1716 +--------------------------------+
1717 |
1718 +----+----+
1719 | DONE |
1720 +---------+
1721
1722 << End of Ap_Do_Withholding program documentation >>
1723
1724 */
1725
1726 BEGIN
1727 current_calling_sequence := 'AP_WITHHOLDING_PKG.AP_Do_Withholding';
1728
1729 -- Execute the ExtENDed Withholding Calculation (IF active)
1730 IF (Ap_ExtENDed_Withholding_Pkg.Ap_ExtENDed_Withholding_Active) THEN
1731 Ap_ExtENDed_Withholding_Pkg.Ap_Do_ExtENDed_Withholding
1732 (P_Invoice_Id,
1733 P_Awt_Date,
1734 P_Calling_Module,
1735 P_Amount,
1736 P_Payment_Num,
1737 P_Checkrun_Name,
1738 P_Last_Updated_By,
1739 P_Last_Update_Login,
1740 P_Program_Application_Id,
1741 P_Program_Id,
1742 P_Request_Id,
1743 P_Awt_Success,
1744 P_Invoice_Payment_Id,
1745 P_Check_Id,
1746 p_checkrun_id);
1747 RETURN;
1748 END IF;
1749
1750 -- Read the AWT flag for the current invoice (i.e. whether AWT
1751 -- calculation has already been performed by AUTOAPPROVAL on thIS
1752 -- invoice):
1753
1754 -- Read setup information
1755 debug_info := 'Read Setup information';
1756 SELECT create_awt_dists_type,
1757 create_awt_invoices_type,
1758 NVL(ai.awt_flag, 'N') awt_flag,
1759 ai.invoice_currency_code,
1760 ai.org_id --4742265
1761 INTO l_create_dists,
1762 l_create_invoices,
1763 l_awt_flag,
1764 l_inv_curr_code,
1765 l_org_id --4742265
1766 FROM ap_system_parameters_all asp,
1767 ap_invoices_all ai
1768 WHERE ai.org_id = asp.org_id
1769 and ai.invoice_id = p_invoice_id;
1770
1771 --Bug6660355
1772 -- Starts Automatic Withholding Processing on the invoice
1773 IF (
1774 ( (l_create_dists in ('APPROVAL', 'BOTH'))
1775 AND
1776 (P_Calling_Module = 'AUTOAPPROVAL')
1777 AND
1778 (l_awt_flag <> 'Y'))
1779 OR
1780 ( (l_create_dists in ( 'PAYMENT','BOTH'))
1781 AND
1782 (P_Calling_Module in ('AUTOSELECT', 'QUICKCHECK') ))
1783 OR
1784 ( P_Calling_Module in ('INVOICE ENTRY', 'INVOICE INQUIRY', 'AWT REPORT'))
1785 ) THEN
1786
1787 savepoint BEFORE_TEMPORARY_CALCULATIONS;
1788
1789 -- Create Temporary AWT distributions:
1790 AP_CALC_Withholding_PKG.AP_Calculate_AWT_Amounts
1791 (P_Invoice_Id
1792 ,P_Awt_Date
1793 ,P_Calling_Module
1794 ,l_create_dists
1795 ,P_Amount
1796 ,P_Payment_Num
1797 ,P_Checkrun_Name
1798 ,P_Last_Updated_By
1799 ,P_Last_Update_Login
1800 ,P_Program_Application_Id
1801 ,P_Program_Id
1802 ,P_Request_Id
1803 ,l_AWT_success
1804 ,current_calling_sequence
1805 ,P_Invoice_Payment_Id
1806 ,p_checkrun_id
1807 ,l_org_id); --4742265
1808
1809 IF (l_AWT_success <> 'SUCCESS') THEN
1810 rollback to BEFORE_TEMPORARY_CALCULATIONS;
1811 END IF;
1812 END IF;
1813 --Bug6660355
1814 IF ( ( ( (l_create_dists in ('APPROVAL','BOTH'))
1815 AND
1816 (P_Calling_Module = 'AUTOAPPROVAL')
1817 AND
1818 (l_awt_flag <> 'Y'))
1819 OR
1820 ( (l_create_dists in ('PAYMENT','BOTH'))
1821 AND
1822 (P_Calling_Module in ('CONFIRM', 'QUICKCHECK')))
1823 OR
1824 (P_Calling_Module = 'AWT REPORT'))
1825 AND
1826 (l_AWT_success = 'SUCCESS'))
1827 THEN
1828 -- Create AWT distributions:
1829 Create_AWT_distributions
1830 (P_Invoice_Id
1831 ,P_Calling_Module
1832 ,l_create_dists
1833 ,P_Payment_Num
1834 ,l_inv_curr_code
1835 ,P_Last_Updated_By
1836 ,P_Last_Update_Login
1837 ,P_Program_Application_Id
1838 ,P_Program_Id
1839 ,P_Request_Id
1840 ,current_calling_sequence);
1841
1842 END IF;
1843 --Bug6660355
1844 IF ( ( ( (l_create_invoices in ('APPROVAL','BOTH'))
1845 AND
1846 (P_Calling_Module = 'AUTOAPPROVAL')
1847 AND
1848 (l_awt_flag <> 'Y'))
1849 OR
1850 ( (l_create_invoices in('PAYMENT','BOTH'))
1851 AND
1852 (P_Calling_Module in ('CONFIRM', 'QUICKCHECK'))
1853 ))
1854 AND
1855 (l_AWT_success = 'SUCCESS')) THEN
1856 -- Create AWT Invoices:
1857
1858 IF (P_Calling_Module NOT IN ('CONFIRM', 'QUICKCHECK')) THEN
1859 l_payment_date := NULL;
1860 END IF;
1861 -- Bug 8254604 Modified method call to populate all the input parameters.
1862 Create_AWT_Invoices(
1863 P_Invoice_Id => P_Invoice_Id,
1864 P_Payment_Date => l_payment_date,
1865 P_Last_Updated_By => P_Last_Updated_By,
1866 P_Last_Update_Login => P_Last_Update_Login,
1867 P_Program_Application_Id => P_Program_Application_Id,
1868 P_Program_Id => P_Program_Id,
1869 P_Request_Id => P_Request_Id,
1870 P_Calling_Sequence => current_calling_sequence,
1871 P_Calling_Module => p_calling_module, --Bug6660355
1872 P_Inv_Line_No => NULL,
1873 P_Dist_Line_No => NULL,
1874 P_New_Invoice_Id => NULL,
1875 P_create_dists => l_create_dists); --Bug7685907
1876 END IF;
1877
1878 -- Set general response for thIS Ap_Do_Withholding execution:
1879 P_Awt_Success := l_AWT_success;
1880
1881 EXCEPTION
1882 WHEN OTHERS THEN
1883 IF (SQLCODE <> -20001) THEN
1884 FND_MESSAGE.set_NAME('SQLAP','AP_DEBUG');
1885 FND_MESSAGE.set_TOKEN('ERROR',SQLERRM);
1886 FND_MESSAGE.set_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
1887 FND_MESSAGE.set_TOKEN('PARAMETERS',
1888 ' Invoice Id = ' || to_char(P_Invoice_Id) ||
1889 ', AWT Date = ' || to_char(P_Awt_Date) ||
1890 ', Calling module = ' || P_Calling_Module ||
1891 ', Amount = ' || to_char(P_Amount) ||
1892 ', Payment Num = ' || to_char(P_Payment_Num) ||
1893 ', Checkrun Name = ' || P_Checkrun_Name);
1894
1895 FND_MESSAGE.set_TOKEN('DEBUG_INFO',debug_info);
1896 END IF;
1897 APP_EXCEPTION.RAISE_EXCEPTION;
1898
1899 END Ap_Do_Withholding;
1900
1901
1902 PROCEDURE Ap_Withhold_AutoSelect (
1903 P_Checkrun_Name IN VARCHAR2,
1904 P_Last_Updated_By IN NUMBER,
1905 P_Last_Update_Login IN NUMBER,
1906 P_Program_Application_Id IN NUMBER,
1907 P_Program_Id IN NUMBER,
1908 P_Request_Id IN NUMBER,
1909 p_checkrun_id in number)
1910 IS
1911 DBG_Loc VARCHAR2(30) := 'Ap_Withhold_AutoSelect';
1912 current_calling_sequence VARCHAR2(2000);
1913 debug_info VARCHAR2(100);
1914 BEGIN
1915 current_calling_sequence := 'AP_WITHHOLDING_PKG.AP_Withhold_AutoSelect';
1916
1917 -- Undo Withholding for all SELECTed invoices in thIS checkrun
1918
1919 DECLARE
1920 CURSOR c_all_sel_invs (l_checkrun_name IN VARCHAR2, l_checkrun_id in number)
1921 IS
1922 SELECT invoice_id
1923 , vendor_id
1924 , payment_num
1925 FROM ap_SELECTed_invoices_all ASI,
1926 ap_system_parameters_all asp
1927 WHERE checkrun_name = l_checkrun_name
1928 AND original_invoice_id IS NULL
1929 AND asp.org_id = asi.org_id
1930 and checkrun_id = l_checkrun_id
1931 --Bug6660355
1932 AND decode(nvl(ASP.allow_awt_flag, 'N'), 'Y',
1933 decode(ASP.create_awt_dists_type, 'PAYMENT',
1934 'Y','BOTH','Y',decode(ASP.create_awt_invoices_type, 'PAYMENT',
1935 'Y','BOTH','Y','N'),
1936 'N'),
1937 'N') = 'Y';
1938
1939
1940 rec_all_sel_invs c_all_sel_invs%ROWTYPE;
1941
1942 BEGIN
1943 debug_info := 'OPEN CURSOR for all SELECTed invoices';
1944 OPEN c_all_sel_invs (P_Checkrun_Name, p_checkrun_id);
1945
1946 LOOP
1947 debug_info := 'Fetch CURSOR for all SELECTed invoices';
1948 FETCH c_all_sel_invs INTO rec_all_sel_invs;
1949 EXIT WHEN c_all_sel_invs%NOTFOUND;
1950
1951 DECLARE
1952 undo_output VARCHAR2(2000);
1953 BEGIN
1954 Ap_Undo_Temp_Withholding
1955 (P_Invoice_Id => rec_all_sel_invs.invoice_id
1956 ,P_VENDor_Id => rec_all_sel_invs.vendor_id
1957 ,P_Payment_Num => rec_all_sel_invs.payment_num
1958 ,P_Checkrun_Name => P_Checkrun_Name
1959 ,P_Undo_Awt_Date => SYSDATE
1960 ,P_Calling_Module => 'AUTOSELECT'
1961 ,P_Last_Updated_By => P_Last_Updated_By
1962 ,P_Last_Update_Login => P_Last_Update_Login
1963 ,P_Program_Application_Id => P_Program_Application_Id
1964 ,P_Program_Id => P_Program_Id
1965 ,P_Request_Id => P_Request_Id
1966 ,P_Awt_Success => undo_output
1967 ,P_checkrun_id => p_checkrun_id );
1968 END;
1969 END LOOP;
1970
1971 debug_info := 'CLOSE CURSOR for all SELECTed invoices';
1972 CLOSE c_all_sel_invs;
1973
1974 END;
1975
1976 -- DO Withholding for all OK to pay SELECTed invoices in thIS checkrun
1977 -- that have No Manual AWT dists
1978
1979 UPDATE ap_SELECTed_invoices_all
1980 SET ok_to_pay_flag = 'Y',
1981 proposed_payment_amount = invoice_amount * payment_cross_rate,
1982 -- We cannot round the proposed_payment_amount here since we don't
1983 -- have payment_currency_code. We will round it later.
1984 dont_pay_reason_code = NULL,
1985 dont_pay_description = NULL
1986 WHERE checkrun_name = P_Checkrun_Name AND
1987 ok_to_pay_flag = 'N' AND
1988 checkrun_id = p_checkrun_id and
1989 dont_pay_reason_code = 'AWT ERROR';
1990
1991 -- Execute Core Withholding Calculation Routine
1992
1993 IF (NOT Ap_ExtENDed_Withholding_Pkg.Ap_ExtENDed_Withholding_Active) THEN
1994 DECLARE
1995 CURSOR c_ok_sel_invs (l_checkrun_name IN VARCHAR2, l_checkrun_id in number)
1996 IS
1997 SELECT ASI.invoice_id
1998 , ASI.payment_num
1999 , ASI.payment_amount
2000 , ASI.discount_amount
2001 , NVL(ASI.invoice_exchange_rate, 1) invoice_exchange_rate
2002 , NVL(ASI.payment_cross_rate,1) payment_cross_rate
2003 , AI.payment_currency_code
2004 , NVL(asp.awt_include_discount_amt, 'N') include_discount_amt
2005 , asp.base_currency_code
2006 FROM ap_SELECTed_invoices_all ASI,
2007 ap_invoices_all AI,
2008 ap_system_parameters_all asp
2009 WHERE ASI.checkrun_name = l_checkrun_name
2010 AND asi.checkrun_id = l_checkrun_id
2011 AND AI.invoice_id = ASI.invoice_id
2012 AND AI.org_id = asp.org_id
2013 AND NVL(ASI.ok_to_pay_flag,'Y') IN ( 'Y','F')
2014 AND NOT EXISTS (SELECT 'Manual AWT dists exist'
2015 FROM ap_invoice_distributions AID
2016 WHERE AID.invoice_id = ASI.invoice_id
2017 AND AID.line_type_lookup_code = 'AWT'
2018 AND AID.awt_flag = 'M')
2019 AND ((ASP.create_awt_dists_type ='PAYMENT' --Bug6660355
2020 AND NOT EXISTS (SELECT 'Invoice already withheld by AutoApproval'
2021 FROM ap_invoices AI
2022 WHERE AI.invoice_id = ASI.invoice_id
2023 AND NVL(AI.awt_flag, 'N') = 'Y'))
2024 OR
2025 ASP.create_awt_dists_type ='BOTH')
2026
2027 AND EXISTS (SELECT 'At least one dist exists with AWT_GROUP_ID'
2028 FROM ap_invoice_distributions AID
2029 WHERE AID.invoice_id = ASI.invoice_id
2030 AND AID.awt_group_id IS NOT NULL)
2031 AND ASI.original_invoice_id IS NULL --Bug6660355
2032 AND decode(nvl(ASP.allow_awt_flag, 'N'), 'Y',
2033 decode(ASP.create_awt_dists_type, 'PAYMENT',
2034 'Y','BOTH','Y', decode(ASP.create_awt_invoices_type, 'PAYMENT',
2035 'Y','BOTH','Y','N'),
2036 'N'),
2037 'N') = 'Y'
2038 FOR UPDATE OF
2039 ASI.proposed_payment_amount
2040 , ASI.payment_amount
2041 , ASI.withholding_amount
2042 , ASI.ok_to_pay_flag
2043 , ASI.dont_pay_reason_code
2044 , ASI.dont_pay_description;
2045
2046 rec_ok_sel_invs c_ok_sel_invs%ROWTYPE;
2047
2048 l_awt_date DATE;
2049 l_withholding_amount NUMBER;
2050 l_subject_amount NUMBER;
2051 l_awt_success VARCHAR2(2000);
2052 l_invoice_amount NUMBER;
2053 l_amount_remaining NUMBER;
2054 l_total_amount NUMBER;
2055 l_count NUMBER;
2056 l_amountapplied NUMBER;
2057 l_update_indicator number:=0;
2058 l_total_awt_amount NUMBER;--6660355
2059 l_amount_payable NUMBER;
2060
2061 BEGIN
2062
2063 debug_info := 'Select check_date for thIS checkrun';
2064 SELECT AISC.check_date
2065 INTO l_awt_date
2066 FROM ap_inv_SELECTion_criteria_all AISC
2067 WHERE AISC.checkrun_name = P_Checkrun_Name
2068 and aisc.checkrun_id = p_checkrun_id;
2069
2070
2071 debug_info := 'OPEN CURSOR for all ok to pay invoices';
2072 OPEN c_ok_sel_invs (P_Checkrun_Name, p_checkrun_id);
2073
2074 LOOP
2075 debug_info := 'Fetch CURSOR for all ok to pay invoices';
2076 FETCH c_ok_sel_invs INTO rec_ok_sel_invs;
2077 EXIT WHEN c_ok_sel_invs%NOTFOUND;
2078
2079 if l_update_indicator = 0 then
2080 --if we are here the cursor got data, so we need to set the
2081 --batches rejection levels to request
2082
2083 -- Bug 7492768 We need to set the inv_awt_exists_flag which indicates if the
2084 -- check run contains invoice that has awt. If the flag is set we would
2085 -- pass the rejection_level_code as 'REQUEST' to IBY.
2086 -- We will not update the rejection levels directly so that we can retrieve
2087 -- the initial values for these if the user removes awt invoices during
2088 -- the review stage from the selected invoices.
2089 update ap_inv_selection_criteria_all
2090 set /*document_rejection_level_code = 'REQUEST',
2091 payment_rejection_level_code = 'REQUEST'*/
2092 inv_awt_exists_flag = 'Y'
2093 where checkrun_id = p_checkrun_id;
2094
2095 l_update_indicator := 1;
2096 end if;
2097
2098
2099 IF (rec_ok_sel_invs.include_discount_amt = 'Y') THEN
2100 l_subject_amount := rec_ok_sel_invs.payment_amount +
2101 rec_ok_sel_invs.discount_amount;
2102 ELSE
2103 l_subject_amount := rec_ok_sel_invs.payment_amount;
2104 END IF;
2105
2106 SELECT invoice_amount, amount_remaining
2107 INTO l_invoice_amount, l_amount_remaining
2108 FROM ap_selected_invoices_all
2109 WHERE invoice_id = rec_ok_sel_invs.invoice_id
2110 AND checkrun_name = p_checkrun_name
2111 and checkrun_id = p_checkrun_id
2112 AND payment_num = rec_ok_sel_invs.payment_num;
2113 --Bug6660355
2114 SELECT sum(nvl(aid.base_amount,aid.amount))
2115 INTO l_total_awt_amount
2116 FROM ap_invoice_distributions aid,ap_invoices ai
2117 WHERE aid.invoice_id = ai.invoice_id
2118 AND aid.invoice_id =rec_ok_sel_invs.invoice_id
2119 AND aid.line_type_lookup_code in ('AWT')
2120 AND aid.awt_origin_group_id = ai.awt_group_id;
2121 --Get the total amount of the invoices SELECTed in the batch.
2122
2123 --Get the total amount of the invoices SELECTed in the batch.
2124
2125 SELECT SUM(NVL(payment_amount,0)) +
2126 SUM((-1) * NVL(withholding_amount,0))
2127 INTO l_total_amount
2128 FROM ap_SELECTed_invoices_all
2129 WHERE checkrun_name = p_checkrun_name
2130 and checkrun_id = p_checkrun_id
2131 AND NVL(ok_to_pay_flag,'Y') in ( 'Y','F');
2132
2133 --Get the count of credit AND debit memos in the batch.
2134 Select COUNT(*)
2135 INTO l_count
2136 FROM ap_selected_invoices_all
2137 WHERE checkrun_name = p_checkrun_name
2138 and checkrun_id = p_checkrun_id
2139 AND NVL(ok_to_pay_flag,'Y') IN ( 'Y','F')
2140 AND invoice_amount < 0;
2141
2142 -- The following statements should be executed only for credit memos with
2143 -- amount remaining equals to payment amount AND total amount <> 0. Because IF
2144 -- total amount IS zero, withholding tax should be calculated for whole invoice
2145 -- amount. If amount remaining IS not equal to payment amount, withholding tax
2146 -- should be calculated for payment amount AND need not to go inside thIS LOOP.
2147
2148 IF l_invoice_amount < 0 AND l_amount_remaining = rec_ok_sel_invs.payment_amount
2149 AND l_total_amount <> 0 THEN
2150
2151 SELECT (-1) * (SUM(NVL(payment_amount,0) +
2152 NVL(ABS(withholding_amount),0)))
2153 INTO l_subject_amount
2154 FROM ap_selected_invoices_all
2155 WHERE payment_amount > 0
2156 AND NVL(ok_to_pay_flag,'Y') in ( 'Y','F')
2157 AND checkrun_name = p_checkrun_name
2158 and checkrun_id = p_checkrun_id;
2159
2160 -- If the batch contains more than one credit memo, get the applied amount AND
2161 -- subtract it FROM subject amount.
2162
2163 IF l_count > 1 THEN
2164 SELECT (-1) * (SUM(NVL(ABS(payment_amount),0) +
2165 NVL(withholding_amount,0)))
2166 INTO l_amountapplied
2167 FROM ap_selected_invoices_all
2168 WHERE NVL(withholding_amount,0) > 0
2169 AND NVL(ok_to_pay_flag,'Y') in ( 'Y','F')
2170 AND checkrun_name = p_checkrun_name
2171 and checkrun_id = p_checkrun_id;
2172
2173 IF ABS(l_amountapplied) > 0 THEN
2174 l_subject_amount := l_subject_amount - l_amountapplied;
2175 END IF;
2176 END IF;
2177
2178 -- If the subject amount IS greater than amount remaining, subject amount
2179 -- should be replaced with amount remaining.
2180
2181 IF ABS(l_subject_amount) > Abs(l_amount_remaining) THEN
2182 l_subject_amount := l_amount_remaining;
2183 END IF;
2184
2185 END IF;
2186 /* Bug 4990575 removed the round currency function from below statement */
2187 /* l_subject_amount := ap_utilities_pkg.ap_round_currency(
2188 l_subject_amount /
2189 rec_ok_sel_invs.payment_cross_rate *
2190 rec_ok_sel_invs.invoice_exchange_rate,
2191 rec_ok_sel_invs.base_currency_code);*/
2192 l_subject_amount := l_subject_amount / rec_ok_sel_invs.payment_cross_rate
2193 * rec_ok_sel_invs.invoice_exchange_rate;
2194 l_amount_payable :=l_invoice_amount + l_total_awt_amount;
2195 l_subject_amount := ap_utilities_pkg.ap_round_currency((l_subject_amount * l_invoice_amount/l_amount_payable)
2196 ,rec_ok_sel_invs.payment_currency_code); --6660355
2197
2198
2199 Ap_Do_Withholding
2200 (P_Invoice_Id => rec_ok_sel_invs.invoice_id
2201 ,P_Awt_Date => l_awt_date
2202 ,P_Calling_Module => 'AUTOSELECT'
2203 ,P_Amount => l_subject_amount
2204 ,P_Payment_Num => rec_ok_sel_invs.payment_num
2205 ,P_Checkrun_Name => P_Checkrun_Name
2206 ,P_Last_Updated_By => P_Last_Updated_By
2207 ,P_Last_Update_Login => P_Last_Update_Login
2208 ,P_Program_Application_Id => P_Program_Application_Id
2209 ,P_Program_Id => P_Program_Id
2210 ,P_Request_Id => P_Request_Id
2211 ,P_Awt_Success => l_awt_success
2212 ,P_checkrun_id => p_checkrun_id
2213 );
2214
2215 IF (l_awt_success = 'SUCCESS') THEN
2216
2217 debug_info := 'Select sum of withholding amount for thIS invoice';
2218 SELECT NVL(SUM(AATD.withholding_amount), 0)
2219 INTO l_withholding_amount
2220 FROM ap_awt_temp_distributions_all AATD
2221 WHERE AATD.checkrun_name = P_Checkrun_Name
2222 AND AATD.invoice_id = rec_ok_sel_invs.invoice_id
2223 AND AATD.payment_num = rec_ok_sel_invs.payment_num
2224 and aatd.checkrun_id = p_checkrun_id;
2225
2226 l_withholding_amount := ap_utilities_pkg.ap_round_currency(
2227 l_withholding_amount /
2228 rec_ok_sel_invs.invoice_exchange_rate *
2229 rec_ok_sel_invs.payment_cross_rate,
2230 rec_ok_sel_invs.payment_currency_code);
2231
2232 debug_info := 'Update proposed payment in ap_selected_invoices';
2233
2234 UPDATE ap_selected_invoices_all ASI
2235 SET ASI.proposed_payment_amount =
2236 ap_utilities_pkg.ap_round_currency(
2237 ASI.proposed_payment_amount,rec_ok_sel_invs.payment_currency_code) -
2238 l_withholding_amount
2239 -- We round proposed_payment_amount here because we couldn't round it earlier.
2240 ,ASI.payment_amount =
2241 ASI.payment_amount - l_withholding_amount
2242 ,ASI.amount_remaining =
2243 ASI.amount_remaining - l_withholding_amount
2244 ,ASI.withholding_amount = l_withholding_amount
2245 WHERE current of c_ok_sel_invs;
2246 ELSE
2247 debug_info := 'Update AWT error in ap_selected_invoices';
2248
2249 UPDATE ap_SELECTed_invoices_all ASI
2250 SET ASI.ok_to_pay_flag = 'N',
2251 ASI.dont_pay_reason_code = 'AWT ERROR',
2252 ASI.dont_pay_description = substr(l_awt_success, 1, 255)
2253 WHERE current of c_ok_sel_invs;
2254 END IF;
2255 END LOOP;
2256
2257 debug_info := 'CLOSE CURSOR for all ok to pay invoices';
2258 CLOSE c_ok_sel_invs;
2259 END;
2260
2261 ELSE --extended awt is used, set the rejection levels for the batch
2262
2263 -- Bug 7492768 We need to set the inv_awt_exists_flag which indicates if the
2264 -- check run contains invoice that has awt. If the flag is set we would
2265 -- pass the rejection_level_code as 'REQUEST' to IBY.
2266 -- We will not update the rejection levels directly so that we can retrieve
2267 -- the initial values for these if the user removes awt invoices during
2268 -- the review stage from the selected invoices.
2269 update ap_inv_selection_criteria_all
2270 set /*document_rejection_level_code = 'REQUEST',
2271 payment_rejection_level_code = 'REQUEST'*/
2272 inv_awt_exists_flag = 'Y'
2273 where checkrun_id = p_checkrun_id;
2274
2275 END IF;
2276 EXCEPTION
2277 WHEN OTHERS THEN
2278 IF (SQLCODE <> -20001) THEN
2279 FND_MESSAGE.set_NAME('SQLAP','AP_DEBUG');
2280 FND_MESSAGE.set_TOKEN('ERROR',SQLERRM);
2281 FND_MESSAGE.set_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
2282 FND_MESSAGE.set_TOKEN('PARAMETERS',
2283 ' Checkrun Name = ' || P_Checkrun_Name ||
2284 ', Program_Id = ' || to_char(P_Program_Id) ||
2285 ', Request_Id = ' || to_char(P_Request_Id));
2286
2287 FND_MESSAGE.set_TOKEN('DEBUG_INFO',debug_info);
2288 END IF;
2289 APP_EXCEPTION.RAISE_EXCEPTION;
2290
2291 END Ap_Withhold_AutoSelect;
2292
2293 PROCEDURE Ap_Withhold_Confirm (
2294 P_Checkrun_Name IN VARCHAR2,
2295 P_Last_Updated_By IN NUMBER,
2296 P_Last_Update_Login IN NUMBER,
2297 P_Program_Application_Id IN NUMBER,
2298 P_Program_Id IN NUMBER,
2299 P_Request_Id IN NUMBER,
2300 p_checkrun_id in number,
2301 p_completed_pmts_group_id in number,
2302 p_org_id in number,
2303 p_check_date in date
2304 )
2305 IS
2306 -- DO Withholding for all OK to pay selected invoices in this checkrun
2307 CURSOR c_ok_sel_invs IS
2308 SELECT ASI.invoice_id,
2309 ASI.payment_num,
2310 p_check_date payment_date
2311 FROM ap_selected_invoices_all ASI,
2312 iby_fd_docs_payable_v ibydocs
2313 WHERE ASI.checkrun_name = p_checkrun_name
2314 AND ASI.original_invoice_id IS NULL
2315 and asi.checkrun_id = p_checkrun_id
2316 and ibydocs.calling_app_doc_unique_ref1 = asi.checkrun_id
2317 AND ibydocs.calling_app_doc_unique_ref2 = asi.invoice_id
2318 AND ibydocs.calling_app_doc_unique_ref3 = asi.payment_num
2319 and ibydocs.completed_pmts_group_id = p_completed_pmts_group_id
2320 and ibydocs.org_id = p_org_id;
2321
2322
2323 rec_ok_sel_invs c_ok_sel_invs%ROWTYPE;
2324 l_awt_success VARCHAR2(2000);
2325 DBG_Loc VARCHAR2(30) := 'Ap_Withhold_Confirm';
2326 current_calling_sequence VARCHAR2(2000);
2327 debug_info VARCHAR2(100);
2328 BEGIN
2329 current_calling_sequence := 'AP_WITHHOLDING_PKG.AP_Withhold_Confirm';
2330
2331 -- Execute Core Withholding Routine for each invoice within
2332 -- the payment batch
2333
2334 IF (NOT Ap_ExtENDed_Withholding_Pkg.Ap_ExtENDed_Withholding_Active) THEN
2335
2336 debug_info := 'OPEN CURSOR for all OK to pay invoices';
2337 OPEN c_ok_sel_invs ;
2338
2339 LOOP
2340 debug_info := 'Fetch CURSOR for all OK to pay invoices';
2341
2342 FETCH c_ok_sel_invs INTO rec_ok_sel_invs;
2343 EXIT WHEN c_ok_sel_invs%NOTFOUND;
2344 Ap_Do_Withholding
2345 (P_Invoice_Id => rec_ok_sel_invs.invoice_id
2346 ,P_Awt_Date => rec_ok_sel_invs.payment_date
2347 ,P_Calling_Module => 'CONFIRM'
2348 ,P_Amount => NULL
2349 ,P_Payment_Num => rec_ok_sel_invs.payment_num
2350 ,P_Checkrun_Name => P_Checkrun_Name
2351 ,P_Last_Updated_By => P_Last_Updated_By
2352 ,P_Last_Update_Login => P_Last_Update_Login
2353 ,P_Program_Application_Id => P_Program_Application_Id
2354 ,P_Program_Id => P_Program_Id
2355 ,P_Request_Id => P_Request_Id
2356 ,P_Awt_Success => l_awt_success
2357 ,p_checkrun_id => p_checkrun_id
2358 );
2359 END LOOP;
2360
2361 debug_info := 'CLOSE CURSOR for all OK to pay invoices';
2362 CLOSE c_ok_sel_invs;
2363
2364 -- Execute ExtENDed Withholding Routine for the entire payment)
2365 --
2366 ELSE
2367 Ap_Do_Withholding
2368 (P_Invoice_Id => NULL
2369 ,P_Awt_Date => NULL
2370 ,P_Calling_Module => 'CONFIRM'
2371 ,P_Amount => NULL
2372 ,P_Payment_Num => NULL
2373 ,P_Checkrun_Name => P_Checkrun_Name
2374 ,P_Last_Updated_By => P_Last_Updated_By
2375 ,P_Last_Update_Login => P_Last_Update_Login
2376 ,P_Program_Application_Id => P_Program_Application_Id
2377 ,P_Program_Id => P_Program_Id
2378 ,P_Request_Id => P_Request_Id
2379 ,P_Awt_Success => l_awt_success
2380 ,p_checkrun_id => p_checkrun_id
2381 );
2382 END IF;
2383
2384 EXCEPTION
2385 WHEN OTHERS THEN
2386 IF (SQLCODE <> -20001) THEN
2387 FND_MESSAGE.set_NAME('SQLAP','AP_DEBUG');
2388 FND_MESSAGE.set_TOKEN('ERROR',SQLERRM);
2389 FND_MESSAGE.set_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
2390 FND_MESSAGE.set_TOKEN('PARAMETERS',
2391 ' Checkrun Name = ' || P_Checkrun_Name ||
2392 ', Program_Id = ' || to_char(P_Program_Id) ||
2393 ', Request_Id = ' || to_char(P_Request_Id));
2394 FND_MESSAGE.set_TOKEN('DEBUG_INFO',debug_info);
2395 END IF;
2396 APP_EXCEPTION.RAISE_EXCEPTION;
2397
2398 END Ap_Withhold_Confirm;
2399
2400
2401 PROCEDURE Ap_Withhold_Cancel (
2402 P_Checkrun_Name IN VARCHAR2,
2403 P_Last_Updated_By IN NUMBER,
2404 P_Last_Update_Login IN NUMBER,
2405 P_Program_Application_Id IN NUMBER,
2406 P_Program_Id IN NUMBER,
2407 P_Request_Id IN NUMBER,
2408 p_checkrun_id in number,
2409 p_completed_pmts_group_id in number default null,
2410 p_org_id in number default null)
2411 IS
2412 -- UNDO Withholding for all selected invoices in thIS checkrun
2413 CURSOR c_all_sel_invs (l_checkrun_name IN VARCHAR2, l_checkrun_id in number)
2414 IS
2415 SELECT ASI.invoice_id
2416 , ASI.payment_num
2417 , AI.vendor_id
2418 FROM ap_SELECTed_invoices_all ASI
2419 , ap_invoices_all AI
2420 WHERE ASI.checkrun_name = l_checkrun_name
2421 AND AI.invoice_id = ASI.invoice_id
2422 and asi.checkrun_id = l_checkrun_id;
2423
2424 rec_all_sel_invs c_all_sel_invs%ROWTYPE;
2425
2426 CURSOR C_sel_invs is
2427 SELECT ASI.invoice_id
2428 , ASI.payment_num
2429 , AI.vendor_id
2430 FROM ap_SELECTed_invoices_all ASI
2431 , ap_invoices_all AI
2432 , iby_fd_docs_payable_v ibydocs
2433 WHERE ASI.checkrun_name = p_checkrun_name
2434 AND AI.invoice_id = ASI.invoice_id
2435 and asi.checkrun_id = p_checkrun_id
2436 and ibydocs.completed_pmts_group_id = p_completed_pmts_group_id
2437 and ibydocs.org_id = p_org_id
2438 and ibydocs.calling_app_doc_unique_ref1 = asi.checkrun_id
2439 AND ibydocs.calling_app_doc_unique_ref2 = asi.invoice_id
2440 AND ibydocs.calling_app_doc_unique_ref3 = asi.payment_num;
2441
2442
2443
2444 l_awt_success VARCHAR2(2000);
2445 DBG_Loc VARCHAR2(30) := 'Ap_Withhold_Cancel';
2446 current_calling_sequence VARCHAR2(2000);
2447 debug_info VARCHAR2(100);
2448 BEGIN
2449 current_calling_sequence := 'AP_WITHHOLDING_PKG.AP_Withhold_Cancel';
2450 debug_info := 'Open Cursor for all selected invoices';
2451
2452 if p_completed_pmts_group_id is null then
2453 OPEN c_all_sel_invs (P_Checkrun_Name, p_checkrun_id);
2454 else
2455 OPEN C_SEL_INVS;
2456 end if;
2457
2458
2459 LOOP
2460 debug_info := 'Fetch CURSOR for all SELECTed invoices';
2461
2462
2463 if p_completed_pmts_group_id is null then
2464 FETCH c_all_sel_invs INTO rec_all_sel_invs;
2465 EXIT WHEN c_all_sel_invs%NOTFOUND;
2466 else
2467 fetch c_sel_invs into rec_all_sel_invs;
2468 exit when c_sel_invs%notfound;
2469 end if;
2470
2471
2472 Ap_Undo_Temp_Withholding
2473 (P_Invoice_Id => rec_all_sel_invs.invoice_id
2474 ,P_VENDor_Id => rec_all_sel_invs.vendor_id
2475 ,P_Payment_Num => rec_all_sel_invs.payment_num
2476 ,P_Checkrun_Name => P_Checkrun_Name
2477 ,P_Undo_Awt_Date => SYSDATE
2478 ,P_Calling_Module => 'CANCEL'
2479 ,P_Last_Updated_By => P_Last_Updated_By
2480 ,P_Last_Update_Login => P_Last_Update_Login
2481 ,P_Program_Application_Id => P_Program_Application_Id
2482 ,P_Program_Id => P_Program_Id
2483 ,P_Request_Id => P_Request_Id
2484 ,P_Awt_Success => l_awt_success
2485 ,P_checkrun_id => p_checkrun_id);
2486 END LOOP;
2487
2488 debug_info := 'CLOSE CURSOR for all SELECTed invoices';
2489 if p_completed_pmts_group_id is null then
2490 CLOSE c_all_sel_invs;
2491 else
2492 CLOSE c_sel_invs;
2493 end if;
2494
2495
2496 EXCEPTION
2497 WHEN OTHERS THEN
2498 IF (SQLCODE <> -20001) THEN
2499 FND_MESSAGE.set_NAME('SQLAP','AP_DEBUG');
2500 FND_MESSAGE.set_TOKEN('ERROR',SQLERRM);
2501 FND_MESSAGE.set_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
2502 FND_MESSAGE.set_TOKEN('PARAMETERS',
2503 ' Checkrun Name = ' || P_Checkrun_Name ||
2504 ', Program_Id = ' || to_char(P_Program_Id) ||
2505 ', Request_Id = ' || to_char(P_Request_Id));
2506 FND_MESSAGE.set_TOKEN('DEBUG_INFO',debug_info);
2507 END IF;
2508 APP_EXCEPTION.RAISE_EXCEPTION;
2509
2510 END Ap_Withhold_Cancel;
2511
2512
2513 PROCEDURE Ap_Undo_Temp_Withholding (
2514 P_Invoice_Id IN NUMBER,
2515 P_Vendor_Id IN NUMBER DEFAULT NULL,
2516 P_Payment_Num IN NUMBER,
2517 P_Checkrun_Name IN VARCHAR2,
2518 P_Undo_Awt_Date IN DATE,
2519 P_Calling_Module IN VARCHAR2,
2520 P_Last_Updated_By IN NUMBER,
2521 P_Last_Update_Login IN NUMBER,
2522 P_Program_Application_Id IN NUMBER DEFAULT NULL,
2523 P_Program_Id IN NUMBER DEFAULT NULL,
2524 P_Request_Id IN NUMBER DEFAULT NULL,
2525 P_Awt_Success OUT NOCOPY VARCHAR2,
2526 P_checkrun_id in number default null)
2527 IS
2528 DBG_Loc VARCHAR2(30) := 'Ap_Undo_Temp_Withholding';
2529 current_calling_sequence VARCHAR2(2000);
2530 debug_info VARCHAR2(100);
2531 l_withholding_amount NUMBER;
2532
2533 BEGIN
2534 current_calling_sequence := 'AP_WITHHOLDING_PKG.AP_Undo_Temp_Withholding';
2535
2536 P_AWT_Success := 'SUCCESS';
2537
2538 IF (P_Calling_Module in ('AUTOSELECT', 'CANCEL', 'PROJECTED')) THEN
2539 <<Undo_During_AutoSELECT>>
2540 DECLARE
2541 CURSOR c_temp (InvId IN NUMBER
2542 ,PaymNum IN NUMBER
2543 ,CheckrunName in VARCHAR2
2544 ,Calling_Module in VARCHAR2
2545 ,checkrun_id in number) IS
2546 SELECT AATD.invoice_id
2547 , AATD.payment_num
2548 , AATD.group_id
2549 , AATD.tax_name
2550 , AATD.tax_code_combination_id
2551 , AATD.gross_amount
2552 , AATD.withholding_amount
2553 , AATD.base_withholding_amount
2554 , AATD.accounting_date
2555 , AATD.period_name
2556 , AATD.checkrun_name
2557 , AATD.tax_rate_id
2558 , TC.tax_id tax_code_id
2559 , aatd.checkrun_id
2560 FROM ap_awt_temp_distributions_all AATD,
2561 ap_invoices_all AI,
2562 ap_tax_codes_all TC
2563 WHERE AATD.invoice_id = InvId
2564 AND AATD.invoice_id = AI.invoice_id
2565 AND TC.name(+) = AATD.tax_name
2566 AND TC.tax_type = 'AWT' -- BUG 3665866
2567 AND NVL(TC.enabled_flag,'Y') = 'Y'
2568 AND NVL(AI.invoice_date,SYSDATE) BETWEEN
2569 NVL(TC.start_date, NVL(AI.invoice_date,SYSDATE)) AND
2570 NVL(TC.inactive_date, NVL(AI.invoice_date,SYSDATE))
2571 AND (((AATD.checkrun_name = NVL(CheckrunName, AATD.checkrun_name))
2572 AND (AATD.payment_num = NVL(PaymNum, AATD.payment_num))
2573 and (aatd.checkrun_id = nvl(checkrun_id, aatd.checkrun_id)))
2574 OR
2575 (AATD.checkrun_name IS NULL
2576 AND AATD.payment_num IS NULL
2577 and aatd.checkrun_id is null
2578 AND calling_module = 'PROJECTED'))
2579 FOR UPDATE;
2580 rec_temp c_temp%ROWTYPE;
2581
2582 FUNCTION Period_Limit_ExISt_For_Tax (
2583 TaxId IN NUMBER,
2584 P_Calling_Sequence in VARCHAR2)
2585 RETURN BOOLEAN
2586 IS
2587 ret BOOLEAN;
2588
2589 CURSOR c_get_limit IS
2590 SELECT 'Limit ExISts'
2591 FROM ap_tax_codes_all
2592 WHERE tax_id = TaxId
2593 AND awt_period_type IS not NULL;
2594
2595 dummy CHAR(12);
2596 current_calling_sequence VARCHAR2(2000);
2597 debug_info VARCHAR2(100);
2598 BEGIN
2599 current_calling_sequence := 'AP_WITHHOLDING_PKG.Period_Limit_ExISt_For_Tax<-' ||
2600 P_Calling_Sequence;
2601 debug_info := 'OPEN CURSOR c_get_limit';
2602 OPEN c_get_limit;
2603
2604 debug_info := 'Fetch CURSOR c_get_limit';
2605 FETCH c_get_limit INTO dummy;
2606
2607 ret := c_get_limit%FOUND;
2608 debug_info := 'CLOSE CURSOR c_get_limit';
2609 CLOSE c_get_limit;
2610
2611 RETURN(ret);
2612 EXCEPTION
2613 WHEN OTHERS THEN
2614 IF (SQLCODE <> -20001) THEN
2615 FND_MESSAGE.set_NAME('SQLAP','AP_DEBUG');
2616 FND_MESSAGE.set_TOKEN('ERROR',SQLERRM);
2617 FND_MESSAGE.set_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
2618 FND_MESSAGE.set_TOKEN('PARAMETERS',
2619 'Tax Code Id = ' || TaxId);
2620
2621 FND_MESSAGE.set_TOKEN('DEBUG_INFO',debug_info);
2622 END IF;
2623 APP_EXCEPTION.RAISE_EXCEPTION;
2624
2625 END Period_Limit_ExISt_For_Tax;
2626
2627 BEGIN
2628 debug_info := 'OPEN CURSOR for AWT temp distributions';
2629 OPEN c_temp (P_Invoice_Id, P_Payment_Num, P_Checkrun_Name, P_Calling_Module, p_checkrun_id);
2630 <<For_Each_Temporary_dist>>
2631 LOOP
2632 -- Read one temporary distribution line:
2633 debug_info := 'Fetch CURSOR for AWT temp distributions';
2634 FETCH c_temp INTO rec_temp;
2635 EXIT WHEN c_temp%NOTFOUND;
2636
2637 -- Decrease corresponding bucket unless called FROM PROJECTED:
2638 -- (PROJECTED doesn't affect buckets)
2639
2640 IF (P_Calling_Module in ('AUTOSELECT', 'CANCEL') AND
2641 Period_Limit_ExISt_For_Tax(rec_temp.tax_code_id
2642 ,current_calling_sequence)) THEN
2643 DECLARE
2644 CURSOR c_get_awt_period IS
2645 SELECT p.period_name
2646 FROM ap_other_periods P,
2647 ap_tax_codes_all C
2648 WHERE (rec_temp.accounting_date BETWEEN
2649 p.start_date AND p.end_date)
2650 AND p.period_type = c.awt_period_type
2651 AND c.name = rec_temp.tax_name
2652 AND p.module = 'AWT';
2653
2654 awt_period ap_other_periods.period_name%TYPE;
2655 BEGIN
2656 debug_info := 'OPEN CURSOR c_get_awt_period';
2657 OPEN c_get_awt_period;
2658
2659 debug_info := 'Fetch CURSOR c_get_awt_period';
2660 FETCH c_get_awt_period INTO awt_period;
2661
2662 debug_info := 'CLOSE CURSOR c_get_awt_period';
2663 CLOSE c_get_awt_period;
2664
2665 debug_info := 'Update ap_awt_buckets';
2666 UPDATE ap_awt_buckets_all
2667 SET gross_amount_to_date = gross_amount_to_date -
2668 NVL(rec_temp.gross_amount,0)
2669 , withheld_amount_to_date = withheld_amount_to_date -
2670 NVL(rec_temp.withholding_amount,0)
2671 , last_UPDATE_date = SYSDATE
2672 , last_UPDATEd_by = P_Last_Updated_By
2673 , last_UPDATE_login = P_Last_Update_Login
2674 , program_UPDATE_date = SYSDATE
2675 , program_application_id = P_Program_Application_Id
2676 , program_id = P_Program_Id
2677 , request_id = P_Request_Id
2678 WHERE period_name = awt_period
2679 AND tax_name = rec_temp.tax_name
2680 AND vendor_id = P_vendor_Id;
2681 END;
2682 END IF;
2683
2684 -- Update ap_selected_invoices IF P_Calling_Modules
2685 -- is AUTOSELECT
2686
2687 IF (P_Calling_Module = 'AUTOSELECT') THEN
2688 DECLARE
2689
2690 CURSOR c_curr_code (l_checkrun_name IN VARCHAR2,
2691 l_invoice_id IN NUMBER,
2692 l_payment_num IN NUMBER,
2693 l_checkrun_id in number) IS
2694 SELECT ASI.payment_currency_code,
2695 ASI.invoice_exchange_rate,
2696 ASI.payment_cross_rate
2697 FROM ap_SELECTed_invoices_all ASI
2698 WHERE ASI.checkrun_name = l_checkrun_name
2699 AND ASI.invoice_id = l_invoice_id
2700 AND ASI.payment_num = l_payment_num
2701 and asi.checkrun_id = l_checkrun_id;
2702
2703 curr_code c_curr_code%ROWTYPE;
2704 BEGIN
2705
2706 debug_info := 'OPEN CURSOR c_curr_code';
2707 OPEN c_curr_code (rec_temp.checkrun_name,
2708 rec_temp.invoice_id,
2709 rec_temp.payment_num,
2710 rec_temp.checkrun_id);
2711
2712 debug_info := 'Fetch CURSOR c_curr_code';
2713 FETCH c_curr_code INTO curr_code;
2714
2715 debug_info := 'CLOSE CURSOR c_curr_code';
2716 CLOSE c_curr_code;
2717
2718 l_withholding_amount := ap_utilities_pkg.ap_round_currency(
2719 (rec_temp.withholding_amount/
2720 curr_code.invoice_exchange_rate) *
2721 curr_code.payment_cross_rate,
2722 curr_code.payment_currency_code);
2723 END ;
2724
2725 debug_info := 'Update ap SELECTed invoices';
2726 UPDATE ap_SELECTed_invoices_all
2727 SET payment_amount = payment_amount +
2728 NVL(l_withholding_amount,0),
2729 proposed_payment_amount = proposed_payment_amount +
2730 NVL(l_withholding_amount,0),
2731 amount_remaining = amount_remaining +
2732 NVL(l_withholding_amount,0),
2733 withholding_amount = 0
2734 WHERE checkrun_name = rec_temp.checkrun_name
2735 AND invoice_id = rec_temp.invoice_id
2736 AND payment_num = rec_temp.payment_num
2737 and checkrun_id = rec_temp.checkrun_id;
2738
2739 END IF;
2740 -- Drop that temporary line:
2741 debug_info := 'Delete the AWT temp distribution';
2742
2743 DELETE ap_awt_temp_distributions_all
2744 WHERE invoice_id = rec_temp.invoice_id
2745 AND group_id = rec_temp.group_id
2746 AND tax_name = rec_temp.tax_name
2747 AND ( ( (checkrun_name = NVL(rec_temp.checkrun_name, checkrun_name))
2748 AND (payment_num = NVL(rec_temp.payment_num, payment_num))
2749 and (checkrun_id = nvl(rec_temp.checkrun_id,checkrun_id)))
2750 OR
2751 ( checkrun_name IS NULL
2752 and checkrun_id is null
2753 AND payment_num IS NULL
2754 AND P_calling_module = 'PROJECTED'));
2755 END LOOP For_Each_Temporary_dist;
2756
2757 debug_info := 'CLOSE CURSOR c_temp';
2758 CLOSE c_temp;
2759 END Undo_During_AutoSELECT;
2760 END IF;
2761
2762 -- Execute the ExtENDed Withholding Reversion (IF active)
2763 --
2764 IF (Ap_ExtENDed_Withholding_Pkg.Ap_ExtENDed_Withholding_Active) THEN
2765 Ap_ExtENDed_Withholding_Pkg.Ap_Undo_Temp_Ext_Withholding
2766 (P_Invoice_Id,
2767 P_VENDor_Id,
2768 P_Payment_Num,
2769 P_Checkrun_Name,
2770 P_Undo_Awt_Date,
2771 P_Calling_Module,
2772 P_Last_Updated_By,
2773 P_Last_Update_Login,
2774 P_Program_Application_Id,
2775 P_Program_Id,
2776 P_Request_Id,
2777 P_Awt_Success,
2778 p_checkrun_id);
2779 END IF;
2780
2781 EXCEPTION
2782 WHEN OTHERS THEN
2783 DECLARE
2784 error_text VARCHAR2(512) := substr(sqlerrm, 1, 512);
2785 BEGIN
2786 P_Awt_Success := error_text;
2787 IF (SQLCODE <> -20001) THEN
2788 FND_MESSAGE.set_NAME('SQLAP','AP_DEBUG');
2789 FND_MESSAGE.set_TOKEN('ERROR',SQLERRM);
2790 FND_MESSAGE.set_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
2791 FND_MESSAGE.set_TOKEN('PARAMETERS',
2792 ', Invoice_Id = ' || to_char(P_Invoice_Id) ||
2793 ', VENDor_Id = ' || to_char(P_VENDor_Id) ||
2794 ', Payment_Num = ' || to_char(P_Payment_Num) ||
2795 ', Checkrun_Name = ' || P_Checkrun_Name ||
2796 ' Undo_Awt_Date = ' || to_char(P_Undo_Awt_Date));
2797
2798 FND_MESSAGE.set_TOKEN('DEBUG_INFO',debug_info);
2799 END IF;
2800 APP_EXCEPTION.RAISE_EXCEPTION;
2801 END;
2802 END Ap_Undo_Temp_Withholding;
2803
2804
2805 PROCEDURE Ap_Undo_Withholding (
2806 P_Parent_Id IN NUMBER,
2807 P_Calling_Module IN VARCHAR2,
2808 P_Awt_Date IN DATE,
2809 P_New_Invoice_Payment_Id IN NUMBER DEFAULT NULL,
2810 P_Last_Updated_By IN NUMBER,
2811 P_Last_Update_Login IN NUMBER,
2812 P_Program_Application_Id IN NUMBER DEFAULT NULL,
2813 P_Program_Id IN NUMBER DEFAULT NULL,
2814 P_Request_Id IN NUMBER DEFAULT NULL,
2815 P_Awt_Success OUT NOCOPY VARCHAR2,
2816 P_Inv_Line_No IN NUMBER DEFAULT NULL,
2817 P_dist_Line_No IN NUMBER DEFAULT NULL,
2818 P_New_Invoice_Id IN NUMBER DEFAULT NULL,
2819 P_New_dist_Line_No IN NUMBER DEFAULT NULL)
2820 IS
2821 /*
2822
2823 Copyright (c) 1995 by Oracle Corporation
2824
2825 NAME
2826 Ap_Undo_Withholding
2827 DESCRIPTION
2828 Reverses AWT distribution lines, buckets, tax authority invoices
2829 for a full invoice or for a payment depENDing upon the calling module
2830 NOTES
2831 ThIS PROCEDURE IS part of the AP_WITHHOLDING_PKG PL/SQL package
2832 HISTORY (YY/MM/DD)
2833 atassoni.it 95/07/14 Creation
2834 mhtaylor 95/08/21 Adapted for Adjust distributions
2835
2836 << Beginning of Undo_Awt_By_Invoice_Payment program documentation >>
2837
2838 Flow of thIS PROCEDURE:
2839
2840 *---------------------------*
2841 | BEGIN Ap_Undo_Withholding |
2842 *---------------------------*
2843 |
2844 v
2845 *---------------------------------------------------*
2846 | Get one AWT distribution line for current invoice | <------------------+
2847 | or invoice payment | |
2848 *---------------------------------------------------* |
2849 | |
2850 v |
2851 *------------------------------------------------------* |
2852 | Get line accounting DATE AND corresponding WT period | |
2853 *------------------------------------------------------* |
2854 | |
2855 v |
2856 *-----------------------------------* |
2857 | Reverse the AWT distribution line | |
2858 *-----------------------------------* |
2859 | |
2860 v |
2861 *--------------------------------------------* |
2862 | Adjust invoice amount AND payment schedule | |
2863 *--------------------------------------------* |
2864 | |
2865 *--------------------------------------------* |
2866 | Decrease corresponding bucket, IF exISting | |
2867 *--------------------------------------------* |
2868 | |
2869 +--> An invoice to a tax authority exISts? |
2870 ,'`. |
2871 *-----------------------------* Yes ,' `. |
2872 | Reverse that invoice: | <---------- <End Loop> |
2873 | ~~~~~~~~~~~~~~~~~~~~ | `. ,' |
2874 | - Reverse invoice line | `.,' |
2875 | - Reverse distribution line | No | |
2876 | - Reverse payment schedules | | |
2877 *-----------------------------* | |
2878 | | |
2879 +<--------------------------+ |
2880 | |
2881 v |
2882 ,'`. |
2883 ,' `. No |
2884 <End Loop> ---------------------------------------------+
2885 `. ,'
2886 `.,'
2887 Yes |
2888 v
2889 *-------------------------*
2890 | END Ap_Undo_Withholding |
2891 *-------------------------*
2892
2893
2894 << End of Ap_Undo_Withholding program documentation >>
2895
2896 */
2897
2898 -- PL/SQL Main Block Constants AND Variables:
2899
2900 awt_period ap_other_periods.period_name%TYPE;
2901 gl_period_name ap_invoice_distributions.period_name%TYPE;
2902 gl_awt_date DATE;
2903 DBG_Loc VARCHAR2(30) := 'Ap_Undo_Withholding';
2904 current_calling_sequence VARCHAR2(2000);
2905 debug_info VARCHAR2(100);
2906 l_org_id NUMBER; /* Bug 4759178, added org_id */
2907
2908 -- PL/SQL Main Block Exceptions:
2909
2910 INVALID_CALLING_MODULE exception;
2911 NOT_AN_OPEN_GL_PERIOD exception;
2912
2913 -- PL/SQL Main Block Tables:
2914
2915 -- PL/SQL Main Block CURSORs AND records:
2916
2917 CURSOR c_awt_dists_inv (ParentId IN NUMBER)
2918 IS
2919 SELECT AID.accounting_date
2920 , AID.accrual_posted_flag
2921 , AID.assets_addition_flag
2922 , AID.assets_tracking_flag
2923 , AID.cash_posted_flag
2924 , AID.invoice_line_number
2925 , AID.distribution_line_number
2926 , AID.dist_code_combination_id
2927 , AID.invoice_id
2928 , AID.last_UPDATEd_by
2929 , AID.last_UPDATE_date
2930 , AID.line_type_lookup_code
2931 , AID.period_name
2932 , AID.set_of_books_id
2933 , AID.accts_pay_code_combination_id
2934 , AID.amount
2935 , AID.base_amount
2936 , AID.base_invoice_price_variance
2937 , AID.batch_id
2938 , AID.created_by
2939 , AID.creation_date
2940 , AID.description
2941 , AID.exchange_rate_variance
2942 , AID.final_match_flag
2943 , AID.income_tax_region
2944 , AID.invoice_price_variance
2945 , AID.last_UPDATE_login
2946 , AID.match_status_flag
2947 , AID.posted_flag
2948 , AID.po_distribution_id
2949 , AID.program_application_id
2950 , AID.program_id
2951 , AID.program_UPDATE_date
2952 , AID.quantity_invoiced
2953 , AID.rate_var_code_combination_id
2954 , AID.request_id
2955 , AID.reversal_flag
2956 , AID.type_1099
2957 , AID.unit_price
2958 , AID.withholding_tax_code_id /* Bug 5382525 */
2959 , TC.name vat_code
2960 , AID.amount_encumbered
2961 , AID.base_amount_encumbered
2962 , AID.encumbered_flag
2963 , AID.price_adjustment_flag
2964 , AID.price_var_code_combination_id
2965 , AID.quantity_unencumbered
2966 , AID.stat_amount
2967 , AID.amount_to_post
2968 , AID.attribute1
2969 , AID.attribute10
2970 , AID.attribute11
2971 , AID.attribute12
2972 , AID.attribute13
2973 , AID.attribute14
2974 , AID.attribute15
2975 , AID.attribute2
2976 , AID.attribute3
2977 , AID.attribute4
2978 , AID.attribute5
2979 , AID.attribute6
2980 , AID.attribute7
2981 , AID.attribute8
2982 , AID.attribute9
2983 , AID.attribute_category
2984 , AID.base_amount_to_post
2985 , AID.cash_je_batch_id
2986 , AID.expenditure_item_date
2987 , AID.expenditure_organization_Id
2988 , AID.expenditure_type
2989 , AID.je_batch_id
2990 , AID.parent_invoice_id
2991 , AID.pa_addition_flag
2992 , AID.pa_quantity
2993 , AID.posted_amount
2994 , AID.posted_base_amount
2995 , AID.prepay_amount_remaining
2996 , AID.project_accounting_context
2997 , AID.project_id
2998 , AID.task_id
2999 --, AID.ussgl_transaction_code - Bug 4277744
3000 --, AID.ussgl_trx_code_context - Bug 4277744
3001 , AID.earliest_settlement_date
3002 , AID.req_distribution_id
3003 , AID.quantity_variance
3004 , AID.base_quantity_variance
3005 , AID.packet_id
3006 , AID.awt_flag
3007 , AID.awt_group_id
3008 , AID.awt_tax_rate_id
3009 , AID.awt_gross_amount
3010 , AID.awt_invoice_id
3011 , AID.awt_origin_group_id
3012 , AID.reference_1
3013 , AID.reference_2
3014 , AID.org_id
3015 , AID.other_invoice_id
3016 , AID.awt_invoice_payment_id
3017 , AID.invoice_distribution_id
3018 , AID.awt_related_id
3019 FROM ap_invoice_distributions AID,
3020 ap_tax_codes TC,
3021 ap_invoices AI
3022 WHERE AID.invoice_id = ParentId
3023 AND TC.tax_id (+) = AID.withholding_tax_code_id /* Bug 5382525 */
3024 AND AID.invoice_id = AI.invoice_id --6660355
3025 AND AID.awt_origin_group_id = AI.awt_group_id
3026 AND AID.invoice_line_number = NVL(P_Inv_Line_No,
3027 AID.invoice_line_number)
3028 AND AID.distribution_line_number = NVL(P_dist_Line_No,
3029 AID.distribution_line_number)
3030 AND NVL(AID.reversal_flag, 'N') <> 'Y' -- bug 7606072
3031 AND NVL(AID.awt_flag, 'M') = 'A';
3032
3033 -- only auto-generated AWT lines are to be considered
3034
3035 CURSOR c_awt_dists_pay (ParentId IN NUMBER) IS
3036 SELECT AID.accounting_date
3037 , AID.accrual_posted_flag
3038 , AID.assets_addition_flag
3039 , AID.assets_tracking_flag
3040 , AID.cash_posted_flag
3041 , AID.invoice_line_number
3042 , AID.distribution_line_number
3043 , AID.dist_code_combination_id
3044 , AID.invoice_id
3045 , AID.last_UPDATEd_by
3046 , AID.last_UPDATE_date
3047 , AID.line_type_lookup_code
3048 , AID.period_name
3049 , AID.set_of_books_id
3050 , AID.accts_pay_code_combination_id
3051 , AID.amount
3052 , AID.base_amount
3053 , AID.base_invoice_price_variance
3054 , AID.batch_id
3055 , AID.created_by
3056 , AID.creation_date
3057 , AID.description
3058 , AID.exchange_rate_variance
3059 , AID.final_match_flag
3060 , AID.income_tax_region
3061 , AID.invoice_price_variance
3062 , AID.last_UPDATE_login
3063 , AID.match_status_flag
3064 , AID.posted_flag
3065 , AID.po_distribution_id
3066 , AID.program_application_id
3067 , AID.program_id
3068 , AID.program_UPDATE_date
3069 , AID.quantity_invoiced
3070 , AID.rate_var_code_combination_id
3071 , AID.request_id
3072 , AID.reversal_flag
3073 , AID.type_1099
3074 , AID.unit_price
3075 , AID.withholding_tax_code_id /* Bug 5382525 */
3076 , TC.name vat_code
3077 , AID.amount_encumbered
3078 , AID.base_amount_encumbered
3079 , AID.encumbered_flag
3080 , AID.price_adjustment_flag
3081 , AID.price_var_code_combination_id
3082 , AID.quantity_unencumbered
3083 , AID.stat_amount
3084 , AID.amount_to_post
3085 , AID.attribute1
3086 , AID.attribute10
3087 , AID.attribute11
3088 , AID.attribute12
3089 , AID.attribute13
3090 , AID.attribute14
3091 , AID.attribute15
3092 , AID.attribute2
3093 , AID.attribute3
3094 , AID.attribute4
3095 , AID.attribute5
3096 , AID.attribute6
3097 , AID.attribute7
3098 , AID.attribute8
3099 , AID.attribute9
3100 , AID.attribute_category
3101 , AID.base_amount_to_post
3102 , AID.cash_je_batch_id
3103 , AID.expenditure_item_date
3104 , AID.expenditure_organization_Id
3105 , AID.expenditure_type
3106 , AID.je_batch_id
3107 , AID.parent_invoice_id
3108 , AID.pa_addition_flag
3109 , AID.pa_quantity
3110 , AID.posted_amount
3111 , AID.posted_base_amount
3112 , AID.prepay_amount_remaining
3113 , AID.project_accounting_context
3114 , AID.project_id
3115 , AID.task_id
3116 --, AID.ussgl_transaction_code - Bug 4277744
3117 --, AID.ussgl_trx_code_context - Bug 4277744
3118 , AID.earliest_settlement_date
3119 , AID.req_distribution_id
3120 , AID.quantity_variance
3121 , AID.base_quantity_variance
3122 , AID.packet_id
3123 , AID.awt_flag
3124 , AID.awt_group_id
3125 , AID.awt_tax_rate_id
3126 , AID.awt_gross_amount
3127 , AID.awt_invoice_id
3128 , AID.awt_origin_group_id
3129 , AID.reference_1
3130 , AID.reference_2
3131 , AID.org_id
3132 , AID.other_invoice_id
3133 , AID.awt_invoice_payment_id
3134 , AID.invoice_distribution_id
3135 , awt_related_id
3136 FROM ap_invoice_distributions AID,
3137 ap_tax_codes TC
3138 WHERE AID.awt_invoice_payment_id = ParentId
3139 AND TC.tax_id(+) = AID.withholding_tax_code_id /* 5382525 */
3140 AND AID.invoice_line_number = NVL(P_Inv_Line_No,
3141 AID.invoice_line_number)
3142 AND AID.distribution_line_number = NVL(P_dist_Line_No,
3143 AID.distribution_line_number)
3144 AND NVL(AID.awt_flag, 'M') = 'A';
3145
3146 -- only auto-generated AWT lines are to be considered
3147
3148 rec_awt_dists c_awt_dists_pay%ROWTYPE;
3149
3150 l_invoice_exchange_rate ap_invoices.exchange_rate%type;
3151 l_func_currency_code ap_system_parameters.base_currency_code%TYPE;
3152 l_old_inv_line_num ap_invoice_lines_all.line_number%TYPE;
3153
3154 -- Ap_Undo_Withholding:
3155 -- PL/SQL Main Block PROCEDUREs AND functions:
3156
3157 -- _______
3158 -- | |
3159 -- | |
3160 -- | |
3161 -- _________| |_________
3162 -- \ /
3163 -- \ Ap_Undo_Withholding /
3164 -- \ /
3165 -- \ _____ /
3166 -- \ | | /
3167 -- \ | | /
3168 -- \___| |___/
3169 -- \ /
3170 -- \ BEGIN /
3171 -- \ /
3172 -- \ /
3173 -- \ /
3174 -- \ /
3175 -- v
3176
3177 BEGIN
3178 current_calling_sequence := 'AP_WITHHOLDING_PKG.AP_Undo_Withholding';
3179 P_Awt_Success := 'SUCCESS'; -- Assumes successfully completion
3180
3181 IF ( (P_Calling_Module NOT IN
3182 ('VOID PAYMENT', 'CANCEL INVOICE', 'REVERSE DIST'))
3183 OR
3184 (P_Calling_Module IS NULL)) THEN
3185 RAISE INVALID_CALLING_MODULE;
3186 END IF;
3187
3188 SAVEPOINT BEFORE_UNDO_WITHHOLDING;
3189 /* Bug 4759178, get org_id */
3190 debug_info := 'Select Org Id';
3191 IF (P_Calling_Module in ('CANCEL INVOICE','REVERSE DIST')) THEN
3192 SELECT AI.org_id
3193 INTO l_org_id
3194 FROM AP_INVOICES_ALL AI
3195 WHERE invoice_id = P_Parent_Id;
3196
3197 ELSIF (P_Calling_Module = 'VOID PAYMENT') THEN
3198 SELECT AIP.org_id
3199 INTO l_org_id
3200 FROM AP_INVOICE_PAYMENTS_ALL AIP
3201 WHERE AIP.invoice_payment_id = P_Parent_Id;
3202
3203 END IF;
3204
3205 debug_info := 'Select GL Period Name';
3206 BEGIN
3207 SELECT GPS.period_name,
3208 P_Awt_Date
3209 INTO gl_period_name,
3210 gl_awt_date
3211 FROM gl_period_statuses GPS,
3212 ap_system_parameters_all ASP
3213 WHERE GPS.application_id = 200
3214 AND GPS.set_of_books_id = ASP.set_of_books_id
3215 AND P_Awt_Date BETWEEN GPS.start_date AND GPS.END_date
3216 AND GPS.closing_status IN ('O', 'F')
3217 AND NVL(gps.ADJUSTMENT_PERIOD_FLAG,'N') = 'N'
3218 AND ASP.org_id = l_org_id; /* Bug 4759178, added org_id condition*/
3219
3220 EXCEPTION
3221 WHEN NO_DATA_FOUND THEN
3222 ap_utilities_pkg.get_open_gl_date(P_Awt_Date, gl_period_name, gl_awt_date);
3223 IF gl_awt_date IS NULL THEN
3224 RAISE NOT_AN_OPEN_GL_PERIOD;
3225 END IF;
3226 END;
3227
3228 <<Process_Withholding_dists>>
3229 DECLARE
3230 DBG_Loc VARCHAR2(30) := 'Process_Withholding_dists';
3231 BEGIN
3232 debug_info := 'OPEN CURSOR c_awt_dists';
3233 IF (P_Calling_Module in ('CANCEL INVOICE','REVERSE DIST')) THEN
3234 OPEN c_awt_dists_inv (P_Parent_Id);
3235 ELSIF (P_Calling_Module = 'VOID PAYMENT') THEN
3236 OPEN c_awt_dists_pay (P_Parent_Id);
3237 END IF;
3238
3239 <<For_Each_Withholding_Line>>
3240 LOOP
3241 debug_info := 'Fetch CURSOR c_get_awt_period';
3242 IF (P_Calling_Module in ('CANCEL INVOICE','REVERSE DIST')) THEN
3243 FETCH c_awt_dists_inv INTO rec_awt_dists;
3244 EXIT WHEN c_awt_dists_inv%NOTFOUND;
3245 ELSIF (P_Calling_Module = 'VOID PAYMENT') THEN
3246 FETCH c_awt_dists_pay INTO rec_awt_dists;
3247 EXIT WHEN c_awt_dists_pay%NOTFOUND;
3248 END IF;
3249 --
3250 <<Get_Withholding_Period>>
3251 DECLARE
3252 DBG_Loc VARCHAR2(30) := 'Get_Withholding_Period';
3253 msg VARCHAR2(240);
3254 CURSOR c_get_period (distDate IN DATE, TaxId IN NUMBER) IS
3255 SELECT period_name
3256 FROM ap_other_periods P,
3257 ap_tax_codes T
3258 WHERE t.tax_id = TaxId
3259 AND p.period_type = t.awt_period_type
3260 AND p.application_id = 200
3261 AND p.module = 'AWT'
3262 AND p.start_date <= TRUNC(distDate)
3263 AND p.end_date >= TRUNC(distDate);
3264 BEGIN
3265 debug_info := 'OPEN CURSOR c_get_period';
3266 OPEN c_get_period (rec_awt_dists.accounting_date
3267 ,rec_awt_dists.withholding_tax_code_id);
3268 debug_info := 'Fetch CURSOR c_get_period';
3269 FETCH c_get_period INTO awt_period;
3270
3271 IF c_get_period%FOUND THEN
3272 msg := 'AWT period '||awt_period||' found for tax id '||
3273 rec_awt_dists.withholding_tax_code_id;
3274 ELSE
3275 msg := 'No AWT period found for tax id '||rec_awt_dists.withholding_tax_code_id;
3276 END IF;
3277
3278 debug_info := 'CLOSE CURSOR c_get_period';
3279 CLOSE c_get_period;
3280
3281 END Get_Withholding_Period;
3282
3283 <<Reverse_Current_Line>>
3284 DECLARE
3285 DBG_Loc VARCHAR2(30) := 'Reverse_Current_Line';
3286
3287 CURSOR c_invoice (InvId IN NUMBER) IS
3288 SELECT vendor_id
3289 , set_of_books_id
3290 , accts_pay_code_combination_id
3291 , batch_id
3292 , description
3293 , invoice_amount
3294 , invoice_currency_code
3295 , exchange_date
3296 , exchange_rate
3297 , exchange_rate_type
3298 -- , ussgl_transaction_code - Bug 4277744
3299 -- , ussgl_trx_code_context - Bug 4277744
3300 , vat_code
3301 FROM ap_invoices
3302 WHERE invoice_id = InvId
3303 FOR UPDATE;
3304 rec_invoice c_invoice%ROWTYPE;
3305
3306 CURSOR c_curr_dist (InvId IN NUMBER,
3307 InvLineNum IN NUMBER) IS
3308 SELECT MAX(distribution_line_number)+1 curr_line_number
3309 FROM ap_invoice_distributions
3310 WHERE invoice_id = InvId
3311 AND invoice_line_number = InvLineNum;
3312
3313 curr_line_number ap_invoice_distributions.distribution_line_number%TYPE;
3314 l_invoice_distribution_id ap_invoice_distributions.invoice_distribution_id%TYPE;
3315
3316 BEGIN
3317 debug_info := 'OPEN CURSOR c_curr_dist';
3318 OPEN c_curr_dist (rec_awt_dists.invoice_id,
3319 rec_awt_dists.invoice_line_number);
3320
3321 debug_info := 'Fetch CURSOR c_curr_dist';
3322 FETCH c_curr_dist INTO curr_line_number;
3323
3324 debug_info := 'CLOSE CURSOR c_curr_dist';
3325 CLOSE c_curr_dist;
3326
3327 debug_info := 'OPEN CURSOR c_invoice';
3328 OPEN c_invoice (rec_awt_dists.invoice_id);
3329
3330 debug_info := 'Fetch CURSOR c_invoice';
3331 FETCH c_invoice INTO rec_invoice;
3332
3333 debug_info := 'Discard the Line';
3334 /* Bug 5202248. Added the Nvl */
3335 IF nvl(l_old_inv_line_num, 0) <> rec_awt_dists.invoice_line_number THEN
3336 UPDATE ap_invoice_lines_all
3337 SET discarded_flag = DECODE(p_calling_module,'CANCEL INVOICE','N','Y'),
3338 /* Bug 5299720. Comment out the following line */
3339 -- Cancelled_flag = DECODE(p_calling_module,'CANCEL INVOICE','Y','N'),
3340 Original_amount = amount,
3341 Original_base_amount = base_amount,
3342 Original_rounding_amt = rounding_amt,
3343 Amount = 0,
3344 Base_amount = 0,
3345 Rounding_amt = 0,
3346 Last_update_date = SYSDATE,
3347 Last_Updated_By = P_Last_Updated_By,
3348 Last_Update_Login = P_Last_Update_Login,
3349 Program_application_id = P_Program_application_id,
3350 Program_id = P_Program_id,
3351 Program_update_date = DECODE(p_program_id,NULL,NULL,SYSDATE),
3352 Request_id = P_Request_id
3353 WHERE invoice_id = rec_awt_dists.invoice_id
3354 AND line_number = rec_awt_dists.invoice_line_number;
3355
3356 l_old_inv_line_num := rec_awt_dists.invoice_line_number;
3357 END IF;
3358
3359 -- IF (P_Calling_module not in ('REVERSE DIST')) THEN
3360 -- From now there will be no difference between REVERSE DIST and CANCEL INVOICE
3361 -- except when REVERSE DIST IS passed match status flag of newly created
3362 -- awt lines will be N else it will be Y.
3363
3364 debug_info := 'Insert reverse AWT line INTO ap_invoice_distributions';
3365
3366 INSERT INTO ap_invoice_distributions
3367 (
3368 accounting_date
3369 ,accrual_posted_flag
3370 ,assets_addition_flag
3371 ,assets_tracking_flag
3372 ,cash_posted_flag
3373 ,distribution_line_number
3374 ,invoice_line_number
3375 ,dist_code_combination_id
3376 ,invoice_id
3377 ,last_UPDATEd_by
3378 ,last_UPDATE_date
3379 ,line_type_lookup_code
3380 ,period_name
3381 ,set_of_books_id
3382 ,amount
3383 ,base_amount
3384 ,batch_id
3385 ,created_by
3386 ,creation_date
3387 ,description
3388 ,last_UPDATE_login
3389 ,match_status_flag
3390 ,posted_flag
3391 ,program_application_id
3392 ,program_id
3393 ,program_update_date
3394 ,request_id
3395 ,withholding_tax_code_id /* Bug 5382525 */
3396 ,encumbered_flag
3397 ,pa_addition_flag
3398 ,posted_amount
3399 ,posted_base_amount
3400 -- ,ussgl_transaction_code - Bug 4277744
3401 -- ,ussgl_trx_code_context - Bug 4277744
3402 ,awt_flag
3403 ,awt_tax_rate_id
3404 ,awt_gross_amount
3405 ,awt_origin_group_id
3406 ,awt_invoice_payment_id
3407 ,tax_code_override_flag
3408 ,tax_recovery_rate
3409 ,tax_recovery_override_flag
3410 ,tax_recoverable_flag
3411 ,invoice_distribution_id
3412 ,reversal_flag
3413 ,parent_reversal_id
3414 ,type_1099
3415 ,income_tax_region
3416 ,org_id
3417 ,awt_related_id
3418 --Freight and Special Charges
3419 ,rcv_charge_addition_flag
3420 )
3421 values
3422 (
3423 gl_awt_date
3424 ,'N'
3425 ,'N'
3426 ,'N'
3427 ,'N'
3428 ,curr_line_number /*bug 5202248. invoice_line_number was inserted before */
3429 ,rec_awt_dists.invoice_line_number
3430 ,rec_awt_dists.dISt_code_combination_id
3431 ,rec_awt_dists.invoice_id
3432 ,P_Last_Updated_By
3433 ,SYSDATE
3434 ,'AWT'
3435 ,gl_period_name
3436 ,rec_invoice.set_of_books_id
3437 ,-rec_awt_dists.amount
3438 ,-rec_awt_dists.base_amount
3439 ,rec_invoice.batch_id
3440 ,P_Last_Updated_By
3441 ,SYSDATE
3442 ,rec_awt_dists.description
3443 ,P_Last_Update_Login
3444 ,decode(p_calling_module,'REVERSE DIST','N','A') -- BUG 6720284
3445 ,'N'
3446 ,P_Program_Application_Id
3447 ,P_Program_Id
3448 ,decode (P_Program_Id,NULL,NULL,SYSDATE)
3449 ,P_Request_Id
3450 ,rec_awt_dists.withholding_tax_code_id
3451 ,'T'
3452 ,'E'
3453 ,0
3454 ,0
3455 -- ,rec_invoice.ussgl_transaction_code - Bug 4277744
3456 -- ,rec_invoice.ussgl_trx_code_context - Bug 4277744
3457 ,'A'
3458 ,rec_awt_dists.awt_tax_rate_id
3459 ,rec_awt_dists.awt_gross_amount * -1
3460 ,rec_awt_dists.awt_origin_group_id
3461 ,P_New_Invoice_Payment_Id
3462 ,'N'
3463 ,''
3464 ,'N'
3465 ,'N'
3466 ,ap_invoice_distributions_s.nextval
3467 ,'N'
3468 ,rec_awt_dists.invoice_distribution_id
3469 ,rec_awt_dists.type_1099
3470 ,rec_awt_dists.income_tax_region
3471 ,rec_awt_dists.org_id
3472 ,rec_awt_dists.awt_related_id
3473 ,'N'
3474 );
3475
3476 --Bug 4539462 DBI logging
3477 AP_DBI_PKG.Maintain_DBI_Summary
3478 ( p_table_name => 'AP_INVOICE_DISTRIBUTIONS',
3479 p_operation => 'I',
3480 p_key_value1 => rec_awt_dists.invoice_id,
3481 p_key_value2 => l_Invoice_distribution_ID,
3482 p_calling_sequence => current_calling_sequence);
3483
3484
3485 <<Update_Payment_Schedule>>
3486 DECLARE
3487
3488 reversed_withholding NUMBER := -rec_awt_dists.amount;
3489
3490 CURSOR c_payment_num (InvPaymId IN NUMBER) IS
3491 SELECT payment_num
3492 FROM ap_invoice_payments
3493 WHERE invoice_payment_id = InvPaymId;
3494
3495 paym_num ap_invoice_payments.payment_num%TYPE;
3496
3497 CURSOR c_payment_sched (PaymNum IN NUMBER, InvId IN NUMBER) IS
3498
3499 SELECT APS.gross_amount
3500 , NVL(APS.inv_curr_gross_amount, APS.gross_Amount) inv_curr_gross_amount
3501 , APS.amount_remaining
3502 , AI.payment_currency_code
3503 FROM ap_payment_schedules APS,
3504 ap_invoices AI
3505 WHERE AI.invoice_id = InvId
3506 AND AI.invoice_id = APS.invoice_id
3507 AND APS.payment_num = NVL(PaymNum, APS.payment_num) /* Bug 5300858 */
3508 FOR UPDATE of APS.gross_amount, APS.inv_curr_gross_amount, APS.amount_remaining;
3509
3510 rec_payment_sched c_payment_sched%ROWTYPE;
3511
3512 DBG_Loc VARCHAR2(30) := 'Update_Payment_Schedule';
3513
3514 NOTHING_TO_DO exception;
3515
3516 BEGIN
3517
3518 /* Bug 5300858 */
3519 IF (P_Calling_Module NOT IN ('REVERSE DIST', 'VOID PAYMENT')) THEN
3520 RAISE NOTHING_TO_DO;
3521 END IF;
3522
3523 /* Bug 5300858 */
3524 IF (P_Calling_Module = 'VOID PAYMENT') THEN
3525
3526 debug_info := 'OPEN CURSOR c_payment_num';
3527 OPEN c_payment_num(P_Parent_Id);
3528
3529 debug_info := 'Fetch CURSOR c_payment_num';
3530 FETCH c_payment_num INTO paym_num;
3531
3532 debug_info := 'CLOSE CURSOR c_payment_num';
3533 CLOSE c_payment_num;
3534
3535 END IF;
3536
3537 debug_info := 'OPEN CURSOR c_payment_sched';
3538 OPEN c_payment_sched(paym_num, rec_awt_dists.invoice_id);
3539
3540 debug_info := 'Fetch CURSOR c_payment_sched';
3541 FETCH c_payment_sched INTO rec_payment_sched;
3542
3543 IF (c_payment_sched%FOUND) THEN
3544 debug_info := 'Update the payment schedule';
3545
3546 UPDATE ap_payment_schedules
3547 SET amount_remaining = (amount_remaining +
3548 ap_utilities_pkg.ap_round_currency(
3549 reversed_withholding *
3550 payment_cross_rate,
3551 rec_payment_sched.payment_currency_code))
3552 WHERE CURRENT of c_payment_sched;
3553 ELSE
3554 NULL;
3555 END IF;
3556
3557 debug_info := 'CLOSE CURSOR c_payment_sched';
3558 CLOSE c_payment_sched;
3559
3560 EXCEPTION
3561 WHEN NOTHING_TO_DO THEN
3562 NULL;
3563
3564 END Update_Payment_Schedule;
3565
3566 <<Update_Bucket>>
3567 DECLARE
3568 CURSOR c_awt_bucket (VendorId IN NUMBER,
3569 Period IN VARCHAR2,
3570 TaxCode IN VARCHAR2) IS
3571 SELECT gross_amount_to_date,
3572 withheld_amount_to_date
3573 FROM ap_awt_buckets
3574 WHERE vendor_id = VendorId
3575 AND period_name = Period
3576 AND tax_name = TaxCode
3577 FOR UPDATE;
3578
3579 gross_amt_to_date ap_awt_buckets.gross_amount_to_date%TYPE;
3580 withheld_amt_to_date ap_awt_buckets.withheld_amount_to_date%TYPE;
3581
3582 DBG_Loc VARCHAR2(30) := 'Update_Bucket';
3583 NOTHING_TO_DO exception;
3584 BEGIN
3585 IF awt_period IS NULL THEN
3586 raISe NOTHING_TO_DO;
3587 END IF;
3588
3589 debug_info := ' Fetching the functional currency AND exchange rate ' ;
3590
3591 SELECT base_currency_code
3592 INTO l_func_currency_code
3593 FROM ap_system_parameters;
3594
3595 IF (P_Calling_Module in ('CANCEL INVOICE','REVERSE DIST')) THEN
3596 l_invoice_exchange_rate := rec_invoice.exchange_rate;
3597 ELSIF (P_Calling_Module = 'VOID PAYMENT') THEN
3598
3599 SELECT ai.exchange_rate
3600 INTO l_invoice_exchange_rate
3601 FROM ap_invoices ai, ap_invoice_payments aip
3602 WHERE ai.invoice_id = aip.invoice_id
3603 AND aip.invoice_payment_id = rec_awt_dists.awt_invoice_payment_id;
3604 END IF;
3605
3606 debug_info := 'OPEN CURSOR c_awt_bucket';
3607 OPEN c_awt_bucket(rec_invoice.vendor_id
3608 ,awt_period
3609 ,rec_awt_dists.vat_code
3610 );
3611 debug_info := 'Fetch CURSOR c_awt_bucket';
3612 FETCH c_awt_bucket INTO gross_amt_to_date, withheld_amt_to_date;
3613
3614 IF (c_awt_bucket%FOUND) THEN
3615 debug_info := 'Update the AWT bucket';
3616
3617 UPDATE ap_awt_buckets
3618 SET gross_amount_to_date = (gross_amt_to_date -
3619 ap_utilities_pkg.ap_round_currency(
3620 rec_awt_dists.awt_gross_amount*
3621 NVL(l_invoice_exchange_rate,1),
3622 l_func_currency_code )),
3623 withheld_amount_to_date = (withheld_amt_to_date+
3624 ap_utilities_pkg.ap_round_currency(
3625 rec_awt_dists.amount*NVL(l_invoice_exchange_rate,1),
3626 l_func_currency_code ))
3627 WHERE CURRENT OF c_awt_bucket;
3628 ELSE
3629 NULL;
3630 END IF;
3631
3632 debug_info := 'CLOSE CURSOR c_awt_bucket';
3633 CLOSE c_awt_bucket;
3634
3635 EXCEPTION
3636 WHEN NOTHING_TO_DO THEN NULL;
3637 END Update_Bucket;
3638
3639 debug_info := 'CLOSE CURSOR c_invoice';
3640 CLOSE c_invoice;
3641
3642 -- Create/Reverse the invoice to the Tax Authority
3643 DECLARE
3644 CURSOR c_read_setup
3645 IS
3646 SELECT create_awt_invoices_type,create_awt_dists_type --bug7685907
3647 FROM ap_system_parameters;
3648 BEGIN
3649 debug_info := 'OPEN CURSOR c_read_setup';
3650 OPEN c_read_setup;
3651
3652 debug_info := 'Fetch CURSOR c_read_setup';
3653 FETCH c_read_setup INTO l_create_invoices,l_create_dists; --bug7685907
3654
3655 debug_info := 'CLOSE CURSOR c_read_setup';
3656 CLOSE c_read_setup;
3657 --Bug6660355
3658 IF (l_create_invoices in('APPROVAL','BOTH')) THEN
3659 -- Bug 8254604
3660 Create_AWT_Invoices(
3661 P_Invoice_Id => rec_awt_dists.invoice_id,
3662 P_Payment_Date => NULL,
3663 P_Last_Updated_By => P_Last_Updated_By,
3664 P_Last_Update_Login => P_Last_Update_Login,
3665 P_Program_Application_Id => P_Program_Application_Id,
3666 P_Program_Id => P_Program_Id,
3667 P_Request_Id => P_Request_Id,
3668 P_Calling_Sequence => current_calling_sequence,
3669 P_Calling_Module => p_calling_module,
3670 P_Inv_Line_No => rec_awt_dists.invoice_line_number,
3671 P_Dist_Line_No => curr_line_number,
3672 P_New_Invoice_Id => P_New_Invoice_Id,
3673 P_create_dists => l_create_dists); --bug7685907
3674
3675 ELSIF (l_create_invoices in('PAYMENT','BOTH') AND
3676 rec_awt_dists.awt_invoice_id IS NOT NULL) THEN
3677 -- Bug 8254604
3678 Create_AWT_Invoices(
3679 P_Invoice_Id => rec_awt_dists.invoice_id,
3680 P_Payment_Date => NULL,
3681 P_Last_Updated_By => P_Last_Updated_By,
3682 P_Last_Update_Login => P_Last_Update_Login,
3683 P_Program_Application_Id => P_Program_Application_Id,
3684 P_Program_Id => P_Program_Id,
3685 P_Request_Id => P_Request_Id,
3686 P_Calling_Sequence => current_calling_sequence,
3687 P_Calling_Module => p_calling_module,
3688 P_Inv_Line_No => rec_awt_dists.invoice_line_number,
3689 P_Dist_Line_No => NVL(P_New_dist_Line_No, P_dist_Line_No),
3690 P_New_Invoice_Id => P_New_Invoice_Id,
3691 P_create_dists => l_create_dists); --bug7685907
3692
3693 END IF;
3694
3695 UPDATE ap_invoice_distributions
3696 SET reversal_flag='Y'
3697 WHERE invoice_distribution_id = rec_awt_dists.invoice_distribution_id
3698 OR parent_reversal_id=rec_awt_dists.invoice_distribution_id;
3699
3700 END;
3701 END Reverse_Current_Line;
3702 END LOOP For_Each_Withholding_Line;
3703
3704 debug_info := 'CLOSE CURSOR c_awt_dists';
3705
3706 IF (P_Calling_Module IN ('CANCEL INVOICE','REVERSE DIST')) THEN
3707 CLOSE c_awt_dists_inv;
3708
3709 UPDATE ap_invoice_distributions
3710 SET awt_withheld_amt = NULL
3711 WHERE invoice_id = P_parent_id
3712 AND NVL(awt_withheld_amt,0) <> 0;
3713
3714 ELSIF (P_Calling_Module = 'VOID PAYMENT') THEN
3715 CLOSE c_awt_dists_pay;
3716 END IF;
3717
3718 END Process_Withholding_dists;
3719
3720 -- Execute the ExtENDed Withholding Reversion (IF active)
3721
3722 IF (Ap_ExtENDed_Withholding_Pkg.Ap_ExtENDed_Withholding_Active) THEN
3723 Ap_ExtENDed_Withholding_Pkg.Ap_Undo_ExtENDed_Withholding
3724 (P_Parent_Id,
3725 P_Calling_Module,
3726 P_Awt_Date,
3727 P_New_Invoice_Payment_Id,
3728 P_Last_Updated_By,
3729 P_Last_Update_Login,
3730 P_Program_Application_Id,
3731 P_Program_Id,
3732 P_Request_Id,
3733 P_Awt_Success,
3734 P_dist_Line_No,
3735 P_New_Invoice_Id,
3736 P_New_dist_Line_No);
3737 END IF;
3738
3739
3740 EXCEPTION
3741 WHEN INVALID_CALLING_MODULE THEN
3742 P_Awt_Success := 'Error: Invalid Calling Module ['||P_Calling_Module||']';
3743
3744 WHEN NOT_AN_OPEN_GL_PERIOD THEN
3745 DECLARE
3746 error_text VARCHAR2(2000);
3747 BEGIN
3748 error_text := Ap_Utilities_Pkg.Ap_Get_DISplayed_Field('AWT ERROR',
3749 'GL PERIOD NOT OPEN');
3750 P_AWT_Success := error_text;
3751 END;
3752 --
3753 WHEN OTHERS THEN
3754 DECLARE
3755 error_text VARCHAR2(512) := substr(sqlerrm, 1, 512);
3756 BEGIN
3757 ROLLBACK TO BEFORE_UNDO_WITHHOLDING;
3758 --
3759 P_Awt_Success := error_text;
3760
3761 IF (SQLCODE <> -20001) THEN
3762 FND_MESSAGE.set_NAME('SQLAP','AP_DEBUG');
3763 FND_MESSAGE.set_TOKEN('ERROR',SQLERRM);
3764 FND_MESSAGE.set_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
3765 FND_MESSAGE.set_TOKEN('PARAMETERS',
3766 ' Parent Id = ' || to_char(P_Parent_Id) ||
3767 ', Calling_Module = ' || P_Calling_Module ||
3768 ', Awt_Date = ' || P_Awt_Date ||
3769 ', New_Invoice_Payment_Id = ' || to_char(P_New_Invoice_Payment_Id) ||
3770 ', dist_Line_No = ' || to_char(P_dist_Line_No) ||
3771 ', New_Invoice_Id = ' || to_char(P_New_Invoice_Id) ||
3772 ', New_dist_Line_No = ' || to_char(P_New_dist_Line_No));
3773
3774 FND_MESSAGE.set_TOKEN('DEBUG_INFO',debug_info);
3775 END IF;
3776 APP_EXCEPTION.RAISE_EXCEPTION;
3777 END;
3778 END Ap_Undo_Withholding;
3779
3780 END AP_WITHHOLDING_PKG;