[Home] [Help]
PACKAGE BODY: APPS.JL_AR_AP_WITHHOLDING_PKG
Source
1 PACKAGE BODY JL_AR_AP_WITHHOLDING_PKG AS
2 /* $Header: jlarpwhb.pls 120.32.12010000.2 2008/09/15 05:27:35 vspuli ship $ */
3
4
5 /**************************************************************************
6 * Private Procedures Specification *
7 **************************************************************************/
8
9 -- Define Package Level Debug Variable and Assign the Profile
10 --DEBUG_Var varchar2(1) := NVL(FND_PROFILE.value('EXT_AWT_DEBUG_FLAG'), 'N');
11 DEBUG_Var varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
12
13 /**************************************************************************
14 * *
15 * Name : Do_AWT_Quick_Payment *
16 * Purpose : Withholding Tax Calculation for Quick Payments *
17 * Processing units to be executed: *
18 * 1. Create Temporary Distribution Lines *
19 * 2. Create AWT Distribution Lines *
20 * 3. Create AWT Invoices *
21 * *
22 **************************************************************************/
23 PROCEDURE Do_AWT_Quick_Payment
24 (P_Checkrun_Name IN Varchar2,
25 P_Checkrun_Id IN Number,
26 P_Check_Id IN Number,
27 P_AWT_Date IN Date,
28 P_Calling_Module IN Varchar2,
29 P_Calling_Sequence IN Varchar2,
30 P_AWT_Success OUT NOCOPY Varchar2,
31 P_Last_Updated_By IN Number Default null,
32 P_Last_Update_Login IN Number Default null,
33 P_Program_Application_Id IN Number Default null,
34 P_Program_Id IN Number Default null,
35 P_Request_Id IN Number Default null);
36
37
38
39
40 /**************************************************************************
41 * *
42 * Name : Do_AWT_Build_Payment_Batch *
43 * Purpose : Withholding Tax Calculation for Payment Batches *
44 * (AutoSelect/Build Payment Stage) *
45 * Processing units to be executed: *
46 * 1. Create Temporary Distribution Lines *
47 * *
48 **************************************************************************/
49 PROCEDURE Do_AWT_Build_Payment_Batch
50 (P_Checkrun_Name IN Varchar2,
51 p_Checkrun_id IN Number,
52 P_Calling_Module IN Varchar2,
53 P_Calling_Sequence IN Varchar2,
54 P_AWT_Success OUT NOCOPY Varchar2,
55 P_Last_Updated_By IN Number Default null,
56 P_Last_Update_Login IN Number Default null,
57 P_Program_Application_Id IN Number Default null,
58 P_Program_Id IN Number Default null,
59 P_Request_Id IN Number Default null);
60
61
62 /**************************************************************************
63 * *
64 * Name : Do_AWT_Confirm_Payment_Batch *
65 * Purpose : Withholding Tax Calculation for Payment Batches *
66 * (Confirm Payment Stage) *
67 * Processing units to be executed: *
68 * 2. Create AWT Distribution Lines *
69 * 3. Create AWT Invoices *
70 * *
71 **************************************************************************/
72 PROCEDURE Do_AWT_Confirm_Payment_Batch
73 (P_Checkrun_Name IN Varchar2,
74 p_Checkrun_id IN Number,
75 P_Calling_Module IN Varchar2,
76 P_Calling_Sequence IN Varchar2,
77 P_AWT_Success OUT NOCOPY Varchar2,
78 P_Last_Updated_By IN Number Default null,
79 P_Last_Update_Login IN Number Default null,
80 P_Program_Application_Id IN Number Default null,
81 P_Program_Id IN Number Default null,
82 P_Request_Id IN Number Default null);
83
84
85
86
87 /**************************************************************************
88 * *
89 * Name : Calculate_AWT_Amounts *
90 * Purpose : This procedure performs all the withholding calculations *
91 * and generates the temporary distribution lines. *
92 * It also updates buckets and credit letter amounts. *
93 * *
94 **************************************************************************/
95 PROCEDURE Calculate_AWT_Amounts
96 (P_Checkrun_Name IN Varchar2,
97 P_Checkrun_ID IN Number,
98 P_Check_Id IN Number,
99 P_Selected_Check_Id IN Number,
100 P_AWT_Date IN Date,
101 P_Calling_Module IN Varchar2,
102 P_Calling_Sequence IN Varchar2,
103 P_Total_Wh_Amount OUT NOCOPY Number,
104 P_AWT_Success OUT NOCOPY Varchar2,
105 P_Last_Updated_By IN Number Default null,
106 P_Last_Update_Login IN Number Default null,
107 P_Program_Application_Id IN Number Default null,
108 P_Program_Id IN Number Default null,
109 P_Request_Id IN Number Default null);
110
111
112
113
114 /**************************************************************************
115 * *
116 * Name : Initialize_Withholdings *
117 * Purpose : Obtains all the attributes for the current withholding *
118 * tax type and name. This procedure also initializes the *
119 * PL/SQL table to store the withholdings *
120 * *
121 **************************************************************************/
122 PROCEDURE Initialize_Withholdings
123 (P_Vendor_Id IN Number,
124 P_AWT_Type_Code IN Varchar2,
125 P_Tax_Id IN Number,
126 P_Calling_Sequence IN Varchar2,
127 P_Rec_AWT_Type OUT NOCOPY jl_zz_ap_awt_types%ROWTYPE,
128 P_Rec_AWT_Name OUT NOCOPY Jl_Zz_Ap_Withholding_Pkg.Rec_AWT_Code,
129 P_Rec_Suppl_AWT_Type OUT NOCOPY jl_zz_ap_supp_awt_types%ROWTYPE,
130 P_Rec_Suppl_AWT_Name OUT NOCOPY jl_zz_ap_sup_awt_cd%ROWTYPE,
131 P_Wh_Table IN OUT NOCOPY Jl_Zz_Ap_Withholding_Pkg.Tab_Withholding);
132
133
134
135
136 /**************************************************************************
137 * *
138 * Name : Process_Withholdings *
139 * Purpose : Process the information for the current withholding tax *
140 * type and name *
141 * *
142 **************************************************************************/
143 PROCEDURE Process_Withholdings
144 (P_Vendor_Id IN Number,
145 P_Rec_AWT_Type IN jl_zz_ap_awt_types%ROWTYPE,
146 P_Rec_Suppl_AWT_Type IN jl_zz_ap_supp_awt_types%ROWTYPE,
147 P_AWT_Date IN Date,
148 P_GL_Period_Name IN Varchar2,
149 P_Base_Currency_Code IN Varchar2,
150 P_Check_Id IN Number,
151 P_Selected_Check_Id IN Number,
152 P_Calling_Sequence IN Varchar2,
153 P_Tab_Withhold IN OUT NOCOPY Jl_Zz_Ap_Withholding_Pkg.Tab_Withholding,
154 P_Total_Wh_Amount IN OUT NOCOPY Number,
155 P_AWT_Success OUT NOCOPY Varchar2,
156 P_Last_Updated_By IN Number Default null,
157 P_Last_Update_Login IN Number Default null,
158 P_Program_Application_Id IN Number Default null,
159 P_Program_Id IN Number Default null,
160 P_Request_Id IN Number Default null,
161 P_Calling_Module IN Varchar2 Default null,
162 P_Checkrun_Name IN Varchar2 Default null,
163 P_Checkrun_ID IN Number Default null,
164 P_Payment_Num IN Number Default null);
165
166
167
168 /**************************************************************************
169 * *
170 * Name : Calculate_Taxable_Base_Amounts *
171 * Purpose : Calculates the taxable base amount for each invoice *
172 * distribution line included within the payment. The steps *
173 * to do this are: *
174 * 1. Prorates the payment amount for each distribution line *
175 * 2. Rounds the prorated amount *
176 * Taxable base amounts must be calculated all together in *
177 * order to avoid rounding mistakes (last amount will be *
178 * obtained by difference). *
179 * *
180 **************************************************************************/
181 PROCEDURE Calculate_Taxable_Base_Amounts
182 (P_Check_Id IN Number,
183 P_Selected_Check_Id IN Number,
184 P_Currency_Code IN Varchar2,
185 P_Tab_Inv_Amounts IN OUT NOCOPY Tab_Amounts,
186 P_Calling_Module IN Varchar2,
187 P_Calling_Sequence IN Varchar2);
188
189
190
191
192 /**************************************************************************
193 * *
194 * Name : Get_Taxable_Base_Amount *
195 * Purpose : Obtains the taxable base amount for a particular invoice *
196 * distribution line. *
197 * *
198 **************************************************************************/
199 FUNCTION Get_Taxable_Base_Amount
200 (P_Invoice_Id IN Number,
201 P_Distribution_Line_No IN Number,
202 P_Invoice_Payment_ID IN Number,
203 P_Invoice_Payment_Num IN Number,
204 P_Tax_Base_Amount_Basis IN Varchar2,
205 P_Tax_Inclusive_Flag IN Varchar2,
206 P_Tab_Inv_Amounts IN Tab_Amounts,
207 P_Calling_Module IN Varchar2,
208 P_Calling_Sequence IN Varchar2)
209 RETURN NUMBER;
210
211
212
213
214 /**************************************************************************
215 * *
216 * Name : Get_Credit_Letter_Amount *
217 * Purpose : Obtains the credit letter amount for a particular *
218 * supplier and withholding tax type *
219 * *
220 **************************************************************************/
221 FUNCTION Get_Credit_Letter_Amount
222 (P_Vendor_Id IN Number,
223 P_AWT_Type_Code IN Varchar2,
224 P_Calling_Sequence IN Varchar2)
225 RETURN NUMBER;
226
227
228
229
230 /**************************************************************************
231 * *
232 * Name : Update_Credit_Letter *
233 * Purpose : Updates the withheld amount for each tax name contained *
234 * into the PL/SQL table. The credit letters table is also *
235 * updated *
236 * *
237 **************************************************************************/
238 PROCEDURE Update_Credit_Letter
239 (P_Vendor_Id IN Number,
240 P_Rec_AWT_Type IN jl_zz_ap_awt_types%ROWTYPE,
241 P_AWT_Date IN Date,
242 P_Payment_Num IN Number,
243 P_Check_Id IN Number,
244 P_Selected_Check_Id IN Number,
245 P_Calling_Sequence IN Varchar2,
246 P_Tab_Withhold IN OUT NOCOPY Jl_Zz_Ap_Withholding_Pkg.Tab_Withholding,
247 P_Last_Updated_By IN Number Default null,
248 P_Last_Update_Login IN Number Default null,
249 P_Program_Application_Id IN Number Default null,
250 P_Program_Id IN Number Default null,
251 P_Request_Id IN Number Default null);
252
253
254
255
256 /**************************************************************************
257 * *
258 * Name : Insert_Credit_Letter_Amount *
259 * Purpose : Stores current information about credit letters into the *
260 * JL_AR_AP_SUP_AWT_CR_LTS table *
261 * *
262 **************************************************************************/
263 PROCEDURE Insert_Credit_Letter_Amount
264 (P_Vendor_Id IN Number,
265 P_AWT_Type_Code IN Varchar2,
266 P_Tax_Id IN Number,
267 P_AWT_Date IN Date,
268 P_Withheld_Amount IN Number,
269 P_Actual_Withheld_Amount IN Number,
270 P_Balance IN Number,
271 P_Status IN Varchar2,
272 P_Payment_Num IN Number,
273 P_Check_Id IN Number,
274 P_Selected_Check_Id IN Number,
275 P_Calling_Sequence IN Varchar2,
276 P_Last_Updated_By IN Number Default null,
277 P_Last_Update_Login IN Number Default null,
278 P_Program_Application_Id IN Number Default null,
279 P_Program_Id IN Number Default null,
280 P_Request_Id IN Number Default null);
281
282
283
284
285 /**************************************************************************
286 * *
287 * Name : Undo_Credit_Letter *
288 * Purpose : Reverse all the credit letter amounts for a particular *
289 * payment. One record will be created for each different *
290 * supplier and witholding tax type. *
291 * *
292 **************************************************************************/
293 PROCEDURE Undo_Credit_Letter
294 (P_Check_Id IN Number,
295 P_Selected_Check_Id IN Number,
296 P_AWT_Date IN Date,
297 P_Payment_Num IN Number,
298 P_Calling_Sequence IN Varchar2,
299 P_Last_Updated_By IN Number Default null,
300 P_Last_Update_Login IN Number Default null,
301 P_Program_Application_Id IN Number Default null,
302 P_Program_Id IN Number Default null,
303 P_Request_Id IN Number Default null);
304
305
306
307
308 /**************************************************************************
309 * *
310 * Name : Update_Quick_Payment *
311 * Purpose : Updates the payment amount by subtracting the withheld *
312 * amount. *
313 * *
314 **************************************************************************/
315 PROCEDURE Update_Quick_Payment
316 (P_Check_Id IN Number,
317 P_Calling_Sequence IN Varchar2);
318
319
320
321
322 /**************************************************************************
323 * *
324 * Name : Update_Payment_Batch *
325 * Purpose : Updates the amounts of the payment batch by subtracting *
326 * the withholding amount. *
327 * *
328 **************************************************************************/
329 PROCEDURE Update_Payment_Batch
330 (P_Checkrun_Name IN Varchar2,
331 p_checkrun_id IN Number,
332 P_Selected_Check_Id IN Number,
333 P_Calling_Sequence IN Varchar2);
334
335
336
337
338 /**************************************************************************
339 * *
340 * Name : Withholding_Already_Calculated *
341 * Purpose : Checks whether the withholding was already calculated for *
342 * a particular invoice. This is only applicable for those *
343 * 'Invoice Based' withholding taxes. *
344 * *
345 **************************************************************************/
346 FUNCTION Withholding_Already_Calculated
347 (P_Invoice_Id IN Number,
348 P_Tax_Name IN Varchar2,
349 P_Tax_Id IN Number,
350 P_Taxable_Base_Amount_Basis IN Varchar2,
351 P_Tab_Withhold IN Jl_Zz_Ap_Withholding_Pkg.Tab_Withholding,
352 P_Inv_Payment_Num IN Number,
353 P_Calling_Sequence IN Varchar2)
354 RETURN Boolean;
355
356
357
358
359 /**************************************************************************
360 * *
361 * Name : Total_Withholding_Amount *
362 * Purpose : Returns the total withheld amount for the withholding tax *
363 * type (sums up all the prorated amounts). *
364 * *
365 **************************************************************************/
366 FUNCTION Total_Withholding_Amount
367 (P_Tab_Withhold IN Jl_Zz_Ap_Withholding_Pkg.Tab_Withholding,
368 P_Calling_Sequence IN Varchar2)
369 RETURN Number;
370
371
372
373
374 /**************************************************************************
375 * *
376 * Name : Partial_Payment_Paid_In_Full *
377 * Purpose : Checks whether the payment amount is enough to cover the *
378 * withholding amount. *
379 * *
380 **************************************************************************/
381 FUNCTION Partial_Payment_Paid_In_Full
382 (P_Check_Id IN Number,
383 P_Selected_Check_Id IN Number,
384 P_Calling_Module IN Varchar2,
385 P_Total_Wh_Amount IN Number,
386 P_Calling_Sequence IN Varchar2,
387 P_Vendor_Name OUT NOCOPY Varchar2,
388 P_Vendor_Site_Code OUT NOCOPY Varchar2)
389 RETURN Boolean;
390
391
392
393
394 /**************************************************************************
395 * *
396 * Name : Confirm_Credit_Letters *
397 * Purpose : Updates the credit letters table in order to store the *
398 * the final check ID, when users confirm a payment batch. *
399 * This procedure is not called for Quick Payments because *
400 * the check ID is known from the begining. *
401 * *
402 **************************************************************************/
403 PROCEDURE Confirm_Credit_Letters
404 (P_Checkrun_Name IN Varchar2,
405 P_Checkrun_ID IN Number,
406 P_Calling_Sequence IN Varchar2);
407
408
409 /**************************************************************************
410 * *
411 * Name : Reject_Payment_Batch *
412 * Purpose : Sets the "Ok To Pay" flag for all the selected invoices *
413 * within the payment when the calculation routine is not *
414 * successful *
415 * *
416 **************************************************************************/
417 PROCEDURE Reject_Payment_Batch
418 (P_Selected_Check_Id IN Number,
419 P_AWT_Success IN Varchar2,
420 P_Calling_Sequence IN Varchar2);
421
422
423 /**************************************************************************
424 * Public Procedures *
425 **************************************************************************/
426
427 /**************************************************************************
428 * *
429 * Name : Jl_Ar_Ap_Do_Withholding *
430 * Purpose : This is the main Argentine withholding tax calculation *
431 * routine. This procedure can be divided into three *
432 * processing units (just like the core calculation routine) *
433 * 1. Create Temporary Distribution Lines *
434 * 2. Create AWT Distribution Lines *
435 * 3. Create AWT Invoices *
436 * *
437 **************************************************************************/
438 PROCEDURE Jl_Ar_Ap_Do_Withholding
439 (P_Invoice_Id IN Number,
440 P_Awt_Date IN Date,
441 P_Calling_Module IN Varchar2,
442 P_Amount IN Number,
443 P_Payment_Num IN Number Default null,
444 P_Checkrun_Name IN Varchar2 Default null,
445 p_Checkrun_id IN Number Default null,
446 P_Last_Updated_By IN Number,
447 P_Last_Update_Login IN Number,
448 P_Program_Application_Id IN Number Default null,
449 P_Program_Id IN Number Default null,
450 P_Request_Id IN Number Default null,
451 P_Awt_Success OUT NOCOPY Varchar2,
452 P_Invoice_Payment_Id IN Number Default null,
453 P_Check_Id IN Number Default null)
454 IS
455
456 l_debug_info Varchar2(300);
457 l_calling_sequence Varchar2(2000);
458
459 BEGIN
460
461 -------------------------------
462 -- Initializes debug variables
463 -------------------------------
464 l_calling_sequence := 'JL_AR_AP_WITHHOLDING_PKG' || '.' ||
465 'Jl_Ar_Ap_Do_Withholding';
466
467 -- Debug
468 IF (DEBUG_Var = 'Y') THEN
469 JL_ZZ_AP_EXT_AWT_UTIL.Debug ('Procedure - Jl_Ar_Ap_Do_Withholding');
470 END IF;
471 -- End Debug
472
473 -----------------------------------
474 -- Assumes successfully completion
475 -----------------------------------
476 P_AWT_Success := AWT_SUCCESS;
477
478
479 /********************************************************
480 * *
481 * Withholding Tax Calculation for Quick Payments *
482 * ---------------------------------------------------- *
483 * Processing units to be executed: *
484 * 1. Create Temporary Distribution Lines *
485 * 2. Create AWT Distribution Lines *
486 * 3. Create AWT Invoices *
487 * *
488 ********************************************************/
489 IF (P_Calling_Module = 'QUICKCHECK') THEN
490 l_debug_info := 'Calculating Withholding for Quick Payment';
491 -- Debug Information
492 IF (DEBUG_Var = 'Y') THEN
493 JL_ZZ_AP_EXT_AWT_UTIL.Debug (l_debug_info);
494 END IF;
495 -- End Debug
496 Do_AWT_Quick_Payment (P_Checkrun_Name,
497 P_Checkrun_ID,
498 P_Check_Id,
499 P_AWT_Date,
500 P_Calling_Module,
501 l_calling_sequence,
502 P_AWT_Success,
503 P_Last_Updated_By,
504 P_Last_Update_Login,
505 P_Program_Application_Id,
506 P_Program_Id,
507 P_Request_Id);
508
509 -- Debug Information
510 IF (DEBUG_Var = 'Y') THEN
511 JL_ZZ_AP_EXT_AWT_UTIL.Debug ('After Calculating Withholding for Quick Payment');
512 END IF;
513 -- End Debug
514 -------------------------------------------------------
515 -- If the calculation did not complete successfully,
516 -- sets the error message on the stack to be retrieved
517 -- on the client side
518 -------------------------------------------------------
519 IF (P_AWT_Success <> AWT_SUCCESS) THEN
520 Fnd_Message.Set_Name ('JL', 'JL_AR_AP_AWT_CALC_ERROR');
521 Fnd_Message.Set_Token ('ERROR_TEXT', P_AWT_Success);
522 END IF;
523
524
525 /********************************************************
526 * *
527 * Withholding Tax Calculation for Payment Batches *
528 * (AutoSelect/Build Payment Stage) *
529 * ---------------------------------------------------- *
530 * Processing units to be executed: *
531 * 1. Create Temporary Distribution Lines *
532 * *
533 ********************************************************/
534 ELSIF (P_Calling_Module = 'AUTOSELECT') THEN
535 l_debug_info := 'Calculating Withholding for Payment Batch (Build)';
536 -- Debug Information
537 IF (DEBUG_Var = 'Y') THEN
538 JL_ZZ_AP_EXT_AWT_UTIL.Debug (l_debug_info);
539 END IF;
540 -- End Debug
541 Do_AWT_Build_Payment_Batch
542 (P_Checkrun_Name,
543 p_Checkrun_id,
544 P_Calling_Module,
545 l_calling_sequence,
546 P_AWT_Success,
547 P_Last_Updated_By,
548 P_Last_Update_Login,
549 P_Program_Application_Id,
550 P_Program_Id,
551 P_Request_Id);
552
553 -- Debug Information
554 IF (DEBUG_Var = 'Y') THEN
555 JL_ZZ_AP_EXT_AWT_UTIL.Debug ('After Calculating Withholding for Payment Batch (Build)');
556 END IF;
557 -- End Debug
558
559 /********************************************************
560 * *
561 * Withholding Tax Calculation for Payment Batches *
562 * (Confirm Payment Stage) *
563 * ---------------------------------------------------- *
564 * Processing units to be executed: *
565 * 2. Create AWT Distribution Lines *
566 * 3. Create AWT Invoices *
567 * *
568 ********************************************************/
569 ELSIF (P_Calling_Module = 'CONFIRM') THEN
570 l_debug_info := 'Calculating Withholding for Payment Batch (Confirm)';
571 -- Debug Information
572 IF (DEBUG_Var = 'Y') THEN
573 JL_ZZ_AP_EXT_AWT_UTIL.Debug (l_debug_info);
574 END IF;
575 -- End Debug
576 Do_AWT_Confirm_Payment_Batch
577 (P_Checkrun_Name,
578 p_checkrun_id,
579 P_Calling_Module,
580 l_calling_sequence,
581 P_AWT_Success,
582 P_Last_Updated_By,
583 P_Last_Update_Login,
584 P_Program_Application_Id,
585 P_Program_Id,
586 P_Request_Id);
587 -- Debug Information
588 IF (DEBUG_Var = 'Y') THEN
589 JL_ZZ_AP_EXT_AWT_UTIL.Debug
590 ('P_AWT_Success: '||P_AWT_Success);
591 JL_ZZ_AP_EXT_AWT_UTIL.Debug
592 ('After Calculating Withholding for Payment Batch (Confirm)');
593 END IF;
594 -- End Debug
595 END IF;
596
597 EXCEPTION
598 WHEN others THEN
599 IF (SQLCODE <> -20001) THEN
600 -- Debug Information
601 IF (DEBUG_Var = 'Y') THEN
602 JL_ZZ_AP_EXT_AWT_UTIL.Debug ('EXCEPTION - Jl_Ar_Ap_Do_Withholding - Error:'||SQLERRM);
603 END IF;
604 -- End Debug
605
606 Fnd_Message.Set_Name ('JL', 'JL_ZZ_AP_DEBUG');
607 Fnd_Message.Set_Token('ERROR', SQLERRM);
608 Fnd_Message.Set_Token('CALLING_SEQUENCE', l_calling_sequence);
609 Fnd_Message.Set_Token('PARAMETERS',
610 ' Invoice Id= ' || to_char(P_Invoice_Id) ||
611 ', Awt Date= ' || to_char(P_Awt_Date,'YYYY/MM/DD') ||
612 ', Calling Module= ' || P_Calling_Module ||
613 ', Amount= ' || to_char(P_Amount) ||
614 ', Payment Num= ' || to_char(P_Payment_Num) ||
615 ', Checkrun Name= ' || P_Checkrun_Name ||
616 ', Invoice Payment Id= ' || to_char(P_Invoice_Payment_Id) ||
617 ', Check Id= ' || to_char(P_Check_Id));
618 Fnd_Message.Set_Token('DEBUG_INFO', l_debug_info);
619 END IF;
620
621 P_AWT_Success := AWT_ERROR;
622
623 END Jl_Ar_Ap_Do_Withholding;
624
625
626
627
628 /**************************************************************************
629 * *
630 * Name : Jl_Ar_Ap_Undo_Withholding *
631 * Purpose : Routine to reverse withholding taxes which were *
632 * calculated by the Argentine withholding tax calculation *
633 * routine (Jl_Ar_Ap_Do_Withholding). *
634 * Most of the withholding tax figures will be reversed by *
635 * the core procedures. This routine will only reverse *
636 * credit letter amounts and withholding certificates. *
637 * *
638 **************************************************************************/
639 PROCEDURE Jl_Ar_Ap_Undo_Withholding
640 (P_Parent_Id IN Number,
641 P_Calling_Module IN Varchar2,
642 P_Undo_Awt_Date IN Date,
643 P_Last_Updated_By IN Number,
644 P_Last_Update_Login IN Number,
645 P_Program_Application_Id IN Number Default null,
646 P_Program_Id IN Number Default null,
647 P_Request_Id IN Number Default null)
648 IS
649
650 ------------------------------
651 -- Local variables definition
652 ------------------------------
653 l_check_id Number;
654 l_payment_num Number;
655 l_selected_check_id Number;
656 l_invoice_payment_id Number;
657 l_invoice_id Number;
658 l_debug_info Varchar2(300);
659 l_calling_sequence Varchar2(2000);
660 l_payment_id number;
661
662 BEGIN
663 -------------------------------
664 -- Initializes debug variables
665 -------------------------------
666 l_calling_sequence := 'JL_AR_AP_WITHHOLDING_PKG' || '.' ||
667 'Jl_Ar_Ap_Undo_Withholding';
668 -- Debug Information
669 IF (DEBUG_Var = 'Y') THEN
670 JL_ZZ_AP_EXT_AWT_UTIL.Debug ('Procedure - Jl_Ar_Ap_Undo_Withholding');
671 END IF;
672 -- End Debug
673
674 -----------------------------------------------------
675 -- Obtains the information to reverse credit letters
676 -----------------------------------------------------
677 -- Bug 2722913 Modified the below query to refer
678 -- to invoice_payment_id instead of invoice_id.
679
680 -- In the confirm CR the payment ID is inserted then changed check id
681 SELECT apip.check_id check_id,
682 apip.payment_num payment_num,
683 apip.invoice_id invoice_id
684 INTO l_check_id,
685 l_payment_num,
686 l_invoice_id
687 FROM ap_invoice_payments apip
688 WHERE apip.invoice_payment_id = P_Parent_Id;
689
690 -- added to reverse the certificate.
691 select ac.payment_id
692 into l_payment_id
693 from ap_checks ac
694 where ac.check_id = l_check_id;
695
696
697
698 ----------------------------------
699 -- Reverses credit letter amounts
700 ----------------------------------
701 -- Debug Information
702 IF (DEBUG_Var = 'Y') THEN
703 JL_ZZ_AP_EXT_AWT_UTIL.Debug ('Undo Credit Letter');
704 END IF;
705 -- End Debug
706
707 Undo_Credit_Letter (l_check_id,
708 null, -- Selected Check Id
709 P_Undo_AWT_Date,
710 l_payment_num,
711 l_calling_sequence,
712 P_Last_Updated_By,
713 P_Last_Update_Login,
714 P_Program_Application_Id,
715 P_Program_Id,
716 P_Request_Id);
717
718 -- Debug Information
719 IF (DEBUG_Var = 'Y') THEN
720 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' After Undo Credit Letter');
721 END IF;
722 -- End Debug
723 ----------------------------------
724 -- Voids Withholding Certificates
725 ----------------------------------
726 -- Debug Information
727 IF (DEBUG_Var = 'Y') THEN
728 JL_ZZ_AP_EXT_AWT_UTIL.Debug ('Calling Jl_Ar_Ap_Void_Certificates');
729 END IF;
730 -- End Debug
731
732 Jl_Ar_Ap_Awt_Reports_Pkg.Jl_Ar_Ap_Void_Certificates (l_payment_id,
733 l_calling_sequence);
734 -- Debug Information
735 IF (DEBUG_Var = 'Y') THEN
736 JL_ZZ_AP_EXT_AWT_UTIL.Debug ('After Jl_Ar_Ap_Void_Certificates');
737 END IF;
738 -- End Debug
739 -----------------------------------------------
740 -- Reverse Exemption_Amount (Global Attribute5)
741 -----------------------------------------------
742
743 UPDATE ap_invoice_distributions
744 SET Global_Attribute5 = 0
745 WHERE invoice_id = l_invoice_id
746 and nvl(to_number(Global_Attribute5),0) > 0;
747
748 -- Debug Information
749 IF (DEBUG_Var = 'Y') THEN
750 JL_ZZ_AP_EXT_AWT_UTIL.Debug ('Reverse Exemption Complete');
751 END IF;
752 -- End Debug
753
754
755 EXCEPTION
756 When NO_DATA_FOUND THEN
757 -- Debug Information
758 IF (DEBUG_Var = 'Y') THEN
759 JL_ZZ_AP_EXT_AWT_UTIL.Debug ('EXCEPTION - Jl_Ar_Ap_Undo_Withholding - No data Found');
760 END IF;
761 WHEN others THEN
762 IF (SQLCODE <> -20001) THEN
763
764 -- Debug Information
765 IF (DEBUG_Var = 'Y') THEN
766 JL_ZZ_AP_EXT_AWT_UTIL.Debug ('EXCEPTION - Jl_Ar_Ap_Undo_Withholding - Error:'||SQLERRM);
767 END IF;
768 -- End Debug
769 Fnd_Message.Set_Name ('JL', 'JL_ZZ_AP_DEBUG');
770 Fnd_Message.Set_Token('ERROR', SQLERRM);
771 Fnd_Message.Set_Token('CALLING_SEQUENCE', l_calling_sequence);
772 Fnd_Message.Set_Token('PARAMETERS',
773 ' Parent_Id= ' || to_char(P_Parent_Id) ||
774 ', Calling_Module= ' || P_Calling_Module ||
775 ', Undo_Awt_Date= ' || to_char(P_Undo_Awt_Date,'YYYY/MM/DD'));
776 Fnd_Message.Set_Token('DEBUG_INFO', l_debug_info);
777 END IF;
778
779 App_Exception.Raise_Exception;
780
781 END Jl_Ar_Ap_Undo_Withholding;
782
783
784 /**************************************************************************
785 * *
786 * Name : Jl_Ar_Ap_Void_Selec_Cetif *
787 * Purpose : Routine to Void the Certificates corresponding to cancel *
788 * payments *
789 * Created for bug 2145634 *
790 * *
791 **************************************************************************/
792 /* Procedure removed due to Cancel of payments in new process has not generated
793 certificates.
794
795
796 PROCEDURE JL_AR_AP_VOID_SELEC_CERTIF(
797 p_checkrun_Name IN Varchar2,
798 p_selected_check_id IN Number,
799 P_Calling_Sequence IN Varchar2)
800 IS
801
802
803 -----------VARIABLES-----------
804 l_debug_info Varchar2(300);
805 l_awt_success Varchar2(2000) := 'SUCCESS';
806 l_calling_sequence Varchar2(2000);
807
808 l_check_number Number;
809 l_selected_check_id Number;
810 l_lookup_code Varchar2(300);
811
812
813 BEGIN
814
815 -------------------------------
816 -- Initializes debug variables
817 -------------------------------
818 l_calling_sequence := 'JL_AR_AP_WITHHOLDING_PKG' || '.' ||
819 'JL_AR_AP_VOID_SELEC_CERTIF<--' || P_Calling_Sequence;
820 -- Debug Information
821 IF (DEBUG_Var = 'Y') THEN
822 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' ');
823 JL_ZZ_AP_EXT_AWT_UTIL.Debug ('Procedure - JL_AR_AP_VOID_SELEC_CERTIF');
824 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Parameter: p_checkrun_name='||p_checkrun_Name);
825 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Parameter: p_selected_check_id='||to_char(p_selected_check_id));
826 END IF;
827 -- End Debug
828
829 UPDATE jl_ar_ap_awt_certif
830 set status = 'VOID'
831 where checkrun_name = p_checkrun_name
832 and check_number NOT IN (
833 SELECT apsi.check_number
834 FROM ap_selected_invoice_checks apsi
835 WHERE apsi.checkrun_name = P_Checkrun_Name
836 AND (apsi.status_lookup_code ='NEGOTIABLE' or apsi.status_lookup_code='ISSUED') );
837
838
839 EXCEPTION
840
841 WHEN NO_DATA_FOUND THEN
842 null;
843
844 WHEN others THEN
845 IF (SQLCODE <> -20001) THEN
846 -- Debug Information
847 IF (DEBUG_Var = 'Y') THEN
848 JL_ZZ_AP_EXT_AWT_UTIL.Debug ('EXCEPTION - JL_AR_AP_VOID_SELEC_CERTIF - Error:'||SQLERRM);
849 END IF;
850 -- End Debug
851 Fnd_Message.Set_Name ('JL', 'JL_ZZ_AP_DEBUG');
852 Fnd_Message.Set_Token('ERROR', SQLERRM);
853 Fnd_Message.Set_Token('CALLING_SEQUENCE', l_calling_sequence);
854 Fnd_Message.Set_Token('PARAMETERS',
855 ', Checkrun Name = ' || P_Checkrun_name);
856 Fnd_Message.Set_Token('DEBUG_INFO', l_debug_info);
857 END IF;
858
859
860 END JL_AR_AP_VOID_SELEC_CERTIF;
861
862 */
863
864 /**************************************************************************
865 * *
866 * Name : Jl_Ar_Ap_Undo_Temp_Withholding *
867 * Purpose : Routine to reverse temporary withholding taxes which were *
868 * calculated by the Argentine withholding tax calculation *
869 * routine (Jl_Ar_Ap_Do_Withholding). *
870 * Most of the withholding tax figures will be reversed by *
871 * the core procedures. This routine will only reverse *
872 * credit letter amounts. *
873 * *
874 **************************************************************************/
875 PROCEDURE Jl_Ar_Ap_Undo_Temp_Withholding
876 (P_Invoice_Id IN Number,
877 P_Payment_Num IN Number,
878 P_Checkrun_Name IN Varchar2,
879 p_Checkrun_id IN Number,
880 P_Undo_Awt_Date IN Date,
881 P_Calling_Module IN Varchar2,
882 P_Last_Updated_By IN Number,
883 P_Last_Update_Login IN Number,
884 P_Program_Application_Id IN Number Default null,
885 P_Program_Id IN Number Default null,
886 P_Request_Id IN Number Default null)
887 IS
888
889 -------------------------------
890 -- Local variables definition
891 -------------------------------
892 l_selected_check_id Number;
893 l_debug_info Varchar2(300);
894 l_calling_sequence Varchar2(2000);
895
896 ---------------------
897 -- Cursor definition
898 ---------------------
899 CURSOR c_selected_invoices (P_Invoice_Id IN Number,
900 P_Payment_Num IN Number,
901 P_Checkrun_Name IN Varchar2)
902 IS
903 SELECT Ihd.Payment_id selected_check_id
904 FROM IBY_Hook_Docs_in_PMT_T ihd
905 WHERE ihd.calling_app_doc_unique_ref2 = P_Invoice_Id
906 AND ihd.calling_app_doc_unique_ref3 = P_Payment_Num
907 AND ihd.calling_App_doc_unique_ref1 = P_Checkrun_ID
908 AND ihd.calling_app_id = 200 ;
909
910 BEGIN
911 -------------------------------
912 -- Initializes debug variables
913 -------------------------------
914 l_calling_sequence := 'JL_AR_AP_WITHHOLDING_PKG' || '.' ||
915 'Jl_Ar_Ap_Undo_Temp_Withholding';
916 -- Debug Information
917 IF (DEBUG_Var = 'Y') THEN
918 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' ');
919 JL_ZZ_AP_EXT_AWT_UTIL.Debug ('Procedure - Jl_Ar_Ap_Undo_Temp_Withholding');
920 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Parameter: P_Checkrun_Name = '||P_Checkrun_Name);
921 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Parameter: P_Invoice_Id = '||to_char(P_Invoice_Id));
922 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Parameter: P_Payment_Num = '||to_char(P_Payment_Num));
923 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Parameter: P_Calling_Module = '||P_Calling_Module);
924 END IF;
925 -- End Debug
926
927 ----------------------------------
928 -- Reverses credit letter amounts
929 ----------------------------------
930 OPEN c_selected_invoices (P_Invoice_Id, P_Payment_Num, P_Checkrun_Name);
931 LOOP
932 FETCH c_selected_invoices INTO l_selected_check_id;
933 EXIT WHEN c_selected_invoices%NOTFOUND;
934
935 Undo_Credit_Letter (null, -- Check Id
936 l_selected_check_id,
937 P_Undo_AWT_Date,
938 P_Payment_Num,
939 l_calling_sequence,
940 P_Last_Updated_By,
941 P_Last_Update_Login,
942 P_Program_Application_Id,
943 P_Program_Id,
944 P_Request_Id);
945
946
947 END LOOP;
948
949 CLOSE c_selected_invoices;
950
951 /* The Cancelation of Certificates has been moved as it is being in a different stage of
952 payment process
953
954 -- Bug 2145634 and Bug# 2319631
955 -- Void certificates when the Payment Batch is canceled, spoilded,
956 -- and skipped
957 -- Undo_temp_wh could be call also during confirm/cancel remainder
958 ----------------------------------
959 -- Voids Withholding Certificates
960 ----------------------------------
961 -- Debug Information
962 IF (DEBUG_Var = 'Y') THEN
963 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Calling JL_AR_AP_VOID_SELEC_CERTIF');
964 END IF;
965 -- End Debug
966
967
968 If (P_Calling_Module='CANCEL') or (P_Calling_Module = 'AUTOSELECT') then
969 JL_AR_AP_WITHHOLDING_PKG.JL_AR_AP_VOID_SELEC_CERTIF(p_checkrun_name,
970 l_selected_check_id,
971 l_calling_sequence);
972
973 -- Debug Information
974 IF (DEBUG_Var = 'Y') THEN
975 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' After Called JL_AR_AP_VOID_SELEC_CERTIF');
976 END IF;
977 -- End Debug
978 end if;
979 */
980
981
982 EXCEPTION
983 WHEN others THEN
984 IF (SQLCODE <> -20001) THEN
985 Fnd_Message.Set_Name ('JL', 'JL_ZZ_AP_DEBUG');
986 Fnd_Message.Set_Token('ERROR', SQLERRM);
987 Fnd_Message.Set_Token('CALLING_SEQUENCE', l_calling_sequence);
988 Fnd_Message.Set_Token('PARAMETERS',
989 ' Invoice Id= ' || to_char(P_Invoice_Id) ||
990 ', Payment Num= ' || to_char(P_Payment_Num) ||
991 ', Checkrun Name= ' || P_Checkrun_Name ||
992 ', Undo Awt Date= ' || to_char(P_Undo_Awt_Date,'YYYY/MM/DD') ||
993 ', Calling Module= ' || P_Calling_Module);
994 Fnd_Message.Set_Token('DEBUG_INFO', l_debug_info);
995 END IF;
996
997 App_Exception.Raise_Exception;
998
999 END Jl_Ar_Ap_Undo_Temp_Withholding;
1000
1001
1002 /**************************************************************************
1003 * *
1004 * Name : Jl_Ar_Ap_Gen_Certificates *
1005 * Purpose : Creates withholding certificates for a particular *
1006 * payment. *
1007 * *
1008 **************************************************************************/
1009 /* This function is removed as the new calling point is from IBY
1010 and the way to handle the call to generate certificates is changed
1011 FUNCTION Jl_Ar_Ap_Gen_Certificates
1012 (P_Checkrun_Name IN Varchar2,
1013 P_Errmsg OUT NOCOPY Varchar2)
1014 RETURN Boolean
1015 IS
1016 .....
1017
1018 END Jl_Ar_Ap_Gen_Certificates;
1019 */
1020
1021 PROCEDURE Jl_Ar_Ap_Certificates
1022 ( p_payment_instruction_ID IN NUMBER,
1023 p_calling_module IN VARCHAR2,
1024 p_api_version IN NUMBER,
1025 p_init_msg_list IN VARCHAR2 ,
1026 p_commit IN VARCHAR2,
1027 x_return_status OUT NOCOPY VARCHAR2,
1028 x_msg_count OUT NOCOPY NUMBER,
1029 x_msg_data OUT NOCOPY VARCHAR2)
1030 IS
1031 -------------------------------
1032 -- Local variables definition
1033 -------------------------------
1034 l_debug_info Varchar2(300);
1035 l_calling_sequence Varchar2(2000);
1036 v_errmsg Varchar2(2000);
1037 l_status Boolean;
1038
1039 cursor c_spoiled_pmt (p_pmt_instruction_id IN NUMBER) is
1040 select pmt.payment_id
1041 from iby_fd_payments_v pmt
1042 where pmt.payment_instruction_id = p_pmt_instruction_id
1043 and pmt.payment_status ='REMOVED_DOCUMENT_SPOILED' ;
1044
1045 cursor c_reprint_pmt (p_pmt_instruction_id IN NUMBER) is
1046 select pmt.payment_id
1047 from iby_fd_payments_v pmt
1048 where pmt.payment_instruction_id = p_pmt_instruction_id
1049 and pmt.payment_status ='READY_TO_REPRINT';
1050
1051
1052 BEGIN
1053 -------------------------------
1054 -- Initializes debug variables
1055 -------------------------------
1056 l_calling_sequence := 'JL_AR_AP_WITHHOLDING_PKG' || '.' ||
1057 'Jl_Ar_Ap_Gen_Certificates';
1058
1059 -- Debug Information
1060 IF (DEBUG_Var = 'Y') THEN
1061 JL_ZZ_AP_EXT_AWT_UTIL.Debug ('Jl_Ar_Ap_certificates');
1062 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Parameter: p_payment_instruction_id = '||p_payment_instruction_id);
1063 END IF;
1064 -- End Debug
1065
1066
1067 ------------------------------
1068 -- Generates the certificates
1069 ------------------------------
1070 l_debug_info := 'Generating Withholding Certificates';
1071 -- Debug Information
1072 IF (DEBUG_Var = 'Y') THEN
1073 JL_ZZ_AP_EXT_AWT_UTIL.Debug (l_debug_info);
1074 END IF;
1075 -- End Debug
1076
1077 IF p_CALLING_MODULE = 'GENERATE' THEN
1078 l_status := Jl_Ar_Ap_Awt_Reports_Pkg.Jl_Ar_Ap_Gen_Certificates( p_payment_instruction_id,
1079 p_calling_module,
1080 v_Errmsg);
1081 ELSIF p_CALLING_MODULE = 'REPRINT' THEN
1082 -- Cancel Previous Certificates
1083 FOR rec_reprint_pmt in c_reprint_pmt(p_payment_instruction_id) Loop
1084
1085 JL_AR_AP_AWT_REPORTS_PKG.jl_ar_ap_void_certificates(rec_reprint_pmt.payment_id,p_calling_module);
1086
1087 END LOOP;
1088 -- Generate new certificates
1089 l_status := Jl_Ar_Ap_Awt_Reports_Pkg.Jl_Ar_Ap_Gen_Certificates( p_payment_instruction_id,
1090 p_calling_module,
1091 v_Errmsg);
1092
1093
1094 ELSIF p_CALLING_MODULE = 'SPOILED' THEN
1095
1096 FOR rec_spoiled_pmt in c_spoiled_pmt(p_payment_instruction_id) Loop
1097
1098 JL_AR_AP_AWT_REPORTS_PKG.jl_ar_ap_void_certificates(rec_spoiled_pmt.payment_id,p_calling_module);
1099
1100 END LOOP;
1101 /* Commented this condition to get in synch with branchline fix*/
1102 /*
1103 -- Bug 6736363 - added new value as per IBY team's suggestion
1104 ELSIF p_CALLING_MODULE = 'CONFIRM' THEN
1105 l_status := Jl_Ar_Ap_Awt_Reports_Pkg.Jl_Ar_Ap_Gen_Certificates( p_payment_instruction_id,
1106 p_calling_module,
1107 v_Errmsg);
1108 */
1109 END IF;
1110
1111 -- Debug Information
1112 IF (DEBUG_Var = 'Y') THEN
1113 JL_ZZ_AP_EXT_AWT_UTIL.Debug ('After Generating Withholding Certificates');
1114 END IF;
1115 -- End Debug
1116 IF l_status then
1117 x_return_status := FND_API.G_RET_STS_SUCCESS;
1118 else
1119 x_return_status := fnd_api.g_ret_sts_error;
1120 END IF;
1121
1122 EXCEPTION
1123
1124 WHEN others THEN
1125 IF (SQLCODE <> -20001) THEN
1126 Fnd_Message.Set_Name ('JL', 'JL_ZZ_AP_DEBUG');
1127 Fnd_Message.Set_Token('ERROR', SQLERRM);
1128 Fnd_Message.Set_Token('CALLING_MODULE', p_calling_module);
1129 Fnd_Message.Set_Token('PARAMETERS',
1130 ' Payment Instruction ID ' || p_payment_instruction_id );
1131 Fnd_Message.Set_Token('DEBUG_INFO', l_debug_info);
1132 END IF;
1133 x_return_status := fnd_api.g_ret_sts_error;
1134 App_Exception.Raise_Exception;
1135
1136 END Jl_Ar_Ap_Certificates;
1137
1138 /**************************************************************************
1139 * Private Procedures *
1140 **************************************************************************/
1141
1142 /**************************************************************************
1143 * *
1144 * Name : Do_AWT_Quick_Payment *
1145 * Purpose : Withholding Tax Calculation for Quick Payments *
1146 * Processing units to be executed: *
1147 * 1. Create Temporary Distribution Lines *
1148 * 2. Create AWT Distribution Lines *
1149 * 3. Create AWT Invoices *
1150 * *
1151 **************************************************************************/
1152 PROCEDURE Do_AWT_Quick_Payment
1153 (P_Checkrun_Name IN Varchar2,
1154 P_Checkrun_ID IN Number,
1155 P_Check_Id IN Number,
1156 P_AWT_Date IN Date,
1157 P_Calling_Module IN Varchar2,
1158 P_Calling_Sequence IN Varchar2,
1159 P_AWT_Success OUT NOCOPY Varchar2,
1160 P_Last_Updated_By IN Number Default null,
1161 P_Last_Update_Login IN Number Default null,
1162 P_Program_Application_Id IN Number Default null,
1163 P_Program_Id IN Number Default null,
1164 P_Request_Id IN Number Default null)
1165 IS
1166
1167 ------------------------------
1168 -- Local variables definition
1169 ------------------------------
1170 l_create_distr Varchar2(25);
1171 l_create_invoices Varchar2(25);
1172 l_invoice_id Number;
1173 l_inv_curr_code Varchar2(50);
1174 l_payment_num Number;
1175 l_total_wh_amount Number := 0;
1176 -- l_payment_amount Number; Bug# 2807464
1177 l_vendor_name Varchar2(240);
1178 l_vendor_site_code Varchar2(15);
1179 l_debug_info Varchar2(300);
1180 l_calling_sequence Varchar2(2000);
1181 l_payment_type Varchar2(10);
1182
1183 -------------------------
1184 -- Exceptions definition
1185 -------------------------
1186 Not_Paid_In_Full Exception;
1187
1188 -------------------------------------
1189 -- Cursor to select all the invoices
1190 -- within the payment
1191 -------------------------------------
1192 CURSOR c_invoice_payment (P_Check_Id Number)
1193 IS
1194 SELECT apin.invoice_id invoice_id,
1195 apin.invoice_currency_code invoice_currency_code,
1196 apip.payment_num payment_num
1197 FROM ap_invoice_payments apip,
1198 ap_invoices apin
1199 WHERE apin.invoice_id = apip.invoice_id
1200 AND apip.check_id = P_Check_Id;
1201
1202
1203 BEGIN
1204 -------------------------------
1205 -- Initializes debug variables
1206 -------------------------------
1207 l_calling_sequence := 'JL_AR_AP_WITHHOLDING_PKG' || '.' ||
1208 'Do_AWT_Quick_Payment<--' || P_Calling_Sequence;
1209
1210 -- Debug Information
1211 IF (DEBUG_Var = 'Y') THEN
1212 JL_ZZ_AP_EXT_AWT_UTIL.Debug ('PROCEDURE - Do_AWT_Quick_Payment');
1213 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Parameter: P_Checkrun_Name = '||P_Checkrun_Name);
1214 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Parameter: P_Check_Id = '||P_Check_Id);
1215 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Parameter: P_Calling_Module = '||P_Calling_Module);
1216 END IF;
1217 -- End Debug
1218
1219 -----------------------------------
1220 -- Assumes successfully completion
1221 -----------------------------------
1222 P_AWT_Success := AWT_SUCCESS;
1223
1224 --------------------------------------------------------------
1225 -- Refund Payments Bug number 1468697.
1226 -- Withholdings are not calculated for payment type = Refund.
1227 --------------------------------------------------------------
1228 Select payment_type_flag
1229 into l_payment_type
1230 from ap_checks
1231 where check_id = P_Check_id;
1232
1233 -- Debug Information
1234 IF (DEBUG_Var = 'Y') THEN
1235 JL_ZZ_AP_EXT_AWT_UTIL.Debug ('Getting Payment Type Flag - '||l_payment_type);
1236 END IF;
1237 -- End Debug
1238
1239
1240 IF (l_payment_type = 'R') THEN
1241 return;
1242 END IF;
1243
1244 ----------------------------
1245 -- Gets Withholding Options
1246 ----------------------------
1247 -- Debug Information
1248 IF (DEBUG_Var = 'Y') THEN
1249 JL_ZZ_AP_EXT_AWT_UTIL.Debug ('==> Calling Get_Withholding_Options ');
1250 END IF;
1251 -- End Debug
1252
1253 Jl_Zz_Ap_Withholding_Pkg.Get_Withholding_Options (l_create_distr,
1254 l_create_invoices);
1255
1256 IF (l_create_distr <> 'PAYMENT') THEN
1257 -- Nothing to do
1258 RETURN;
1259 END IF;
1260
1261 -----------------------------------------
1262 -- Executes First Processing Unit
1263 -- Creates Temporary Distribution Lines
1264 -----------------------------------------
1265 SAVEPOINT Before_Calc_Withholding;
1266
1267 -- Debug Information
1268 IF (DEBUG_Var = 'Y') THEN
1269 JL_ZZ_AP_EXT_AWT_UTIL.Debug ('==> Calling Calculate_AWT_Amounts');
1270 END IF;
1271 -- End Debug
1272
1273 Calculate_AWT_Amounts (P_Checkrun_Name,
1274 P_Checkrun_ID,
1275 P_Check_Id,
1276 null,
1277 P_AWT_Date,
1278 P_Calling_Module,
1279 l_calling_sequence,
1280 l_total_wh_amount,
1281 P_AWT_Success,
1282 P_Last_Updated_By,
1283 P_Last_Update_Login,
1284 P_Program_Id,
1285 P_Request_Id);
1286
1287 -- Debug Information
1288 IF (DEBUG_Var = 'Y') THEN
1289 JL_ZZ_AP_EXT_AWT_UTIL.Debug ('After Called Calculate_AWT_Amounts');
1290 END IF;
1291 -- End Debug
1292 --------------------------------------------------------
1293 -- Checks whether the calculation finishes successfully
1294 --------------------------------------------------------
1295 IF (P_AWT_Success <> AWT_SUCCESS) THEN
1296 RETURN;
1297 END IF;
1298
1299 ---------------------------------------------------
1300 -- Checks whether the payment amount is enough to
1301 -- cover the withholding amount
1302 ---------------------------------------------------
1303 IF (NOT Partial_Payment_Paid_In_Full(P_Check_Id,
1304 null,
1305 P_Calling_Module,
1306 l_total_wh_amount,
1307 l_calling_sequence,
1308 l_vendor_name,
1309 l_vendor_site_code)) THEN
1310 -- l_payment_amount)) THEN Bug# 2807464
1311
1312 -- Debug Information
1313 IF (DEBUG_Var = 'Y') THEN
1314 JL_ZZ_AP_EXT_AWT_UTIL.Debug ('IF (NOT Partial_Payment_Paid_In_Full) - Function');
1315 END IF;
1316 -- End Debug
1317
1318 ROLLBACK TO Before_Calc_Withholding;
1319 RAISE Not_Paid_In_Full;
1320 END IF;
1321
1322 ----------------------------------------------
1323 -- Processing each invoice within the payment
1324 ----------------------------------------------
1325 OPEN c_invoice_payment(P_Check_Id);
1326
1327 -- Debug Information
1328 IF (DEBUG_Var = 'Y') THEN
1329 JL_ZZ_AP_EXT_AWT_UTIL.Debug ('Looping thru the cursor c_invoice_payment');
1330 END IF;
1331 -- End Debug
1332
1333 LOOP
1334 FETCH c_invoice_payment INTO l_invoice_id,
1335 l_inv_curr_code,
1336 l_payment_num;
1337 EXIT WHEN c_invoice_payment%NOTFOUND;
1338
1339 -- Debug Information
1340 IF (DEBUG_Var = 'Y') THEN
1341 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Fetched Values: l_invoice_id'||to_char(l_invoice_id));
1342 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Fetched Values: l_inv_curr_code'||l_inv_curr_code);
1343 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Fetched Values: l_payment_num'||to_char(l_payment_num));
1344 JL_ZZ_AP_EXT_AWT_UTIL.Debug ('==> Calling Ap_Withholding_Pkg.Create_AWT_Distributions');
1345 END IF;
1346 -- End Debug
1347
1348 -----------------------------------------
1349 -- Executes Second Processing Unit
1350 -- Creates AWT Distribution Lines
1351 -----------------------------------------
1352 Ap_Withholding_Pkg.Create_AWT_Distributions
1353 (l_invoice_id,
1354 P_Calling_Module,
1355 l_create_distr,
1356 l_payment_num,
1357 l_inv_curr_code,
1358 P_Last_Updated_By,
1359 P_Last_Update_Login,
1360 P_Program_Application_Id,
1361 P_Program_Id,
1362 P_Request_Id,
1363 l_calling_sequence);
1364
1365 -- Debug Information
1366 IF (DEBUG_Var = 'Y') THEN
1367 JL_ZZ_AP_EXT_AWT_UTIL.Debug ('After Called Ap_Withholding_Pkg.Create_AWT_Distributions');
1368 END IF;
1369 -- End Debug
1370
1371
1372 -----------------------------------------
1373 -- Executes Third Processing Unit
1374 -- Creates AWT Invoices
1375 -----------------------------------------
1376 IF (l_create_invoices = 'PAYMENT') THEN
1377
1378 -- Debug Information
1379 IF (DEBUG_Var = 'Y') THEN
1380 JL_ZZ_AP_EXT_AWT_UTIL.Debug ('==> Calling Ap_Withholding_Pkg.Create_AWT_Invoices');
1381 END IF;
1382 -- End Debug
1383
1384 Ap_Withholding_Pkg.Create_AWT_Invoices
1385 (l_invoice_id,
1386 P_AWT_Date,
1387 P_Last_Updated_By,
1388 P_Last_Update_Login,
1389 P_Program_Application_Id,
1390 P_Program_Id,
1391 P_Request_Id,
1392 l_calling_sequence,
1393 P_Calling_Module);
1394
1395 -- Debug Information
1396 IF (DEBUG_Var = 'Y') THEN
1397 JL_ZZ_AP_EXT_AWT_UTIL.Debug ('After Called Ap_Withholding_Pkg.Create_AWT_Invoices');
1398 END IF;
1399 -- End Debug
1400
1401
1402 END IF;
1403
1404 END LOOP;
1405
1406 -- Debug Information
1407 IF (DEBUG_Var = 'Y') THEN
1408 JL_ZZ_AP_EXT_AWT_UTIL.Debug ('Close c_invoice_payment');
1409 END IF;
1410 -- End Debug
1411
1412 CLOSE c_invoice_payment;
1413
1414 ---------------------------------------------
1415 -- Updates all the amounts associated to the
1416 -- Quick Payment
1417 ---------------------------------------------
1418 -- Debug Information
1419 IF (DEBUG_Var = 'Y') THEN
1420 JL_ZZ_AP_EXT_AWT_UTIL.Debug ('==> Calling Update_Quick_Payment');
1421 END IF;
1422 -- End Debug
1423
1424 Update_Quick_Payment (P_Check_Id,
1425 l_calling_sequence);
1426
1427 EXCEPTION
1428 WHEN Not_Paid_In_Full THEN
1429 Fnd_Message.Set_Name ('JL', 'JL_AR_AP_PARTIAL_PAY_ERROR');
1430 Fnd_Message.Set_Token('SUPPLIER', l_vendor_name);
1431 Fnd_Message.Set_Token('PAY_SITE', l_vendor_site_code);
1432 --Fnd_Message.Set_Token('PAY_AMOUNT', l_payment_amount); Bug# 2807464
1433 Fnd_Message.Set_Token('WH_AMOUNT', l_total_wh_amount);
1434 P_AWT_Success := Fnd_Message.Get;
1435
1436 WHEN others THEN
1437 IF (SQLCODE <> -20001) THEN
1438 Fnd_Message.Set_Name ('JL', 'JL_ZZ_AP_DEBUG');
1439 Fnd_Message.Set_Token('ERROR', SQLERRM);
1440 Fnd_Message.Set_Token('CALLING_SEQUENCE', l_calling_sequence);
1441 Fnd_Message.Set_Token('PARAMETERS',
1442 ' Checkrun Name= ' || P_Checkrun_Name ||
1443 ', Check Id= ' || to_char(P_Check_Id) ||
1444 ', AWT Date= ' || to_char(P_AWT_Date,'YYYY/MM/DD') ||
1445 ', Calling Module= ' || P_Calling_Module);
1446 Fnd_Message.Set_Token('DEBUG_INFO', l_debug_info);
1447 END IF;
1448
1449 App_Exception.Raise_Exception;
1450
1451 END Do_AWT_Quick_Payment;
1452
1453
1454
1455
1456 /**************************************************************************
1457 * *
1458 * Name : Do_AWT_Build_Payment_Batch *
1459 * Purpose : Withholding Tax Calculation for Payment Batches *
1460 * (AutoSelect/Build Payment Stage) *
1461 * Processing units to be executed: *
1462 * 1. Create Temporary Distribution Lines *
1463 * *
1464 **************************************************************************/
1465 PROCEDURE Do_AWT_Build_Payment_Batch
1466 (P_Checkrun_Name IN Varchar2,
1467 P_Checkrun_ID IN Number,
1468 P_Calling_Module IN Varchar2,
1469 P_Calling_Sequence IN Varchar2,
1470 P_AWT_Success OUT NOCOPY Varchar2,
1471 P_Last_Updated_By IN Number Default null,
1472 P_Last_Update_Login IN Number Default null,
1473 P_Program_Application_Id IN Number Default null,
1474 P_Program_Id IN Number Default null,
1475 P_Request_Id IN Number Default null)
1476 IS
1477 ------------------------------------------------
1478 -- Cursor to select all the checks ID included
1479 -- within the payment batch
1480 ------------------------------------------------
1481 CURSOR c_selected_checks (P_Checkrun_Name Varchar2)
1482 IS
1483 /* SELECT apsic.selected_check_id selected_check_id
1484 FROM ap_selected_invoice_checks apsic
1485 WHERE apsic.checkrun_name = P_Checkrun_Name;*/
1486 --RG
1487 SELECT ipmt.payment_id payment_id, ipmt.payment_date
1488 from IBY_HOOK_PAYMENTS_T ipmt
1489 where ipmt.call_app_pay_service_req_code = P_Checkrun_Name
1490 and ipmt.calling_app_id= 200;
1491
1492 ------------------------------
1493 -- Local variables definition
1494 -------------------------------
1495 rec_selected_checks c_selected_checks%ROWTYPE;
1496 l_awt_date Date;
1497 l_create_distr Varchar2(25);
1498 l_create_invoices Varchar2(25);
1499 l_total_wh_amount Number := 0;
1500 -- l_payment_amount Number; Bug# 2807464
1501 l_vendor_name Varchar2(240);
1502 l_vendor_site_code Varchar2(15);
1503 l_debug_info Varchar2(300);
1504 l_calling_sequence Varchar2(2000);
1505
1506 BEGIN
1507 -------------------------------
1508 -- Initializes debug variables
1509 -------------------------------
1510 l_calling_sequence := 'JL_AR_AP_WITHHOLDING_PKG' || '.' ||
1511 'Do_AWT_Build_Payment_Batch<--' || P_Calling_Sequence;
1512
1513 -- Debug Information
1514 IF (DEBUG_Var = 'Y') THEN
1515 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' ');
1516 JL_ZZ_AP_EXT_AWT_UTIL.Debug ('PROCEDURE - Do_AWT_Build_Payment_Batch');
1517 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Parameter: P_Checkrun_Name '||P_Checkrun_Name);
1518 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Parameter: P_Checkrun_ID '||to_number(P_Checkrun_ID));
1519 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Parameter: P_Calling_Module '||P_Calling_Module);
1520 END IF;
1521 -- End Debug
1522
1523 -----------------------------------
1524 -- Assumes successfully completion
1525 -----------------------------------
1526 P_AWT_Success := AWT_SUCCESS;
1527
1528 ----------------------------
1529 -- Gets Withholding Options
1530 ----------------------------
1531
1532 -- Debug Information
1533 IF (DEBUG_Var = 'Y') THEN
1534 JL_ZZ_AP_EXT_AWT_UTIL.Debug ('==> Calling Get_Withholding_Options ');
1535 END IF;
1536 -- End Debug
1537
1538 Jl_Zz_Ap_Withholding_Pkg.Get_Withholding_Options (l_create_distr,
1539 l_create_invoices);
1540
1541 -- Debug Information
1542 IF (DEBUG_Var = 'Y') THEN
1543 JL_ZZ_AP_EXT_AWT_UTIL.Debug ('Withh Options: create_dist and create_invoices: '||l_create_distr||', '||l_create_invoices);
1544 END IF;
1545 -- End Debug
1546
1547 IF (l_create_distr <> 'PAYMENT') THEN
1548 -- Nothing to do
1549 RETURN;
1550 END IF;
1551
1552 ------------------------
1553 -- Obtains the AWT Date
1554 ------------------------
1555 /* SELECT apisc.check_date
1556 INTO l_awt_date
1557 FROM ap_invoice_selection_criteria apisc
1558 WHERE apisc.checkrun_name = P_Checkrun_Name;
1559
1560 -- RG
1561 SELECT payment_date
1562 INTO l_awt_date
1563 FROM IBY_HOOK_PAYMENTS_T ipmt
1564 WHERE ipmt.call_app_pay_service_req_code = P_Checkrun_Name
1565 AND ipmt.calling_app_id=200;
1566 */
1567
1568 -------------------------------------------------------------
1569 -- Calculates withholding for each different payment within
1570 -- the payment batch
1571 -------------------------------------------------------------
1572 OPEN c_selected_checks (P_Checkrun_Name);
1573
1574 -- Debug Information
1575 IF (DEBUG_Var = 'Y') THEN
1576 JL_ZZ_AP_EXT_AWT_UTIL.Debug ('Looping thru c_selected_checks');
1577 END IF;
1578 -- End Debug
1579
1580 LOOP
1581 FETCH c_selected_checks INTO rec_selected_checks;
1582 EXIT WHEN c_selected_checks%NOTFOUND;
1583
1584 -----------------------------------------
1585 -- Executes First Processing Unit
1586 -- Creates Temporary Distribution Lines
1587 -----------------------------------------
1588 SAVEPOINT Before_Calc_Withholding;
1589
1590 -- Debug Information
1591 IF (DEBUG_Var = 'Y') THEN
1592 JL_ZZ_AP_EXT_AWT_UTIL.Debug ('==> Calling Calculate_AWT_Amounts');
1593 END IF;
1594 -- End Debug
1595
1596 Calculate_AWT_Amounts (P_Checkrun_Name,
1597 P_Checkrun_ID,
1598 null,
1599 rec_selected_checks.payment_id,
1600 rec_selected_checks.payment_date,
1601 P_Calling_Module,
1602 l_calling_sequence,
1603 l_total_wh_amount,
1604 P_AWT_Success,
1605 P_Last_Updated_By,
1606 P_Last_Update_Login,
1607 P_Program_Id,
1608 P_Request_Id);
1609
1610 -- Debug Information
1611 IF (DEBUG_Var = 'Y') THEN
1612 JL_ZZ_AP_EXT_AWT_UTIL.Debug ('After Called Calculate_AWT_Amounts');
1613 END IF;
1614 -- End Debug
1615
1616 --------------------------------------------------------
1617 -- Checks whether the calculation finishes successfully
1618 --------------------------------------------------------
1619 IF (P_AWT_Success <> AWT_SUCCESS) THEN
1620 -- Debug Information
1621 IF (DEBUG_Var = 'Y') THEN
1622 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' ==> Calling Reject_Payment_Batch - P_AWT_Success <> AWT_SUCCESS ');
1623 END IF;
1624 -- End Debug
1625
1626 Reject_Payment_Batch (rec_selected_checks.payment_id,
1627 P_AWT_Success,
1628 l_calling_sequence);
1629
1630 ---------------------------------------------------
1631 -- Checks whether the payment amount is enough to
1632 -- cover the withholding amount
1633 ---------------------------------------------------
1634 ELSIF (NOT Partial_Payment_Paid_In_Full(null,
1635 rec_selected_checks.payment_id,
1636 P_Calling_Module,
1637 l_total_wh_amount,
1638 l_calling_sequence,
1639 l_vendor_name,
1640 l_vendor_site_code)) THEN
1641 -- l_payment_amount)) Bug# 2807464
1642 -- AND l_payment_amount > 0 THEN --- Bug 2157401 Bug# 2807464
1643
1644 -- Debug Information
1645 IF (DEBUG_Var = 'Y') THEN
1646 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' ==> Calling - NOT Partial_Payment_Paid_In_Full');
1647 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' ROLLBACK TO Before_Calc_Withholding');
1648 END IF;
1649 -- End Debug
1650
1651 ROLLBACK TO Before_Calc_Withholding;
1652
1653 Fnd_Message.Set_Name ('JL', 'JL_AR_AP_PARTIAL_BATCH_ERROR');
1654 Fnd_Message.Set_Token('WH_AMOUNT', l_total_wh_amount);
1655 P_AWT_Success := Fnd_Message.Get;
1656
1657 -- Debug Information
1658 IF (DEBUG_Var = 'Y') THEN
1659 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' ==> Calling Reject_Payment_Batch - NOT Pay_in_Full');
1660 END IF;
1661 -- End Debug
1662
1663 Reject_Payment_Batch (rec_selected_checks.payment_id,
1664 P_AWT_Success,
1665 l_calling_sequence);
1666
1667 -----------------------------------------------
1668 -- Updates payment amounts with the calculated
1669 -- withholding amount
1670 -----------------------------------------------
1671 ELSE
1672
1673 -- Debug Information
1674 IF (DEBUG_Var = 'Y') THEN
1675 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' ==> Calling Update_Payment_Batch');
1676 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Parameter: P_Checkrun_Name = '||P_Checkrun_Name);
1677 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Parameter: rec_selected_checks.selected_check_id = '||
1678 to_char(rec_selected_checks.payment_id));
1679 END IF;
1680 -- End Debug
1681
1682 Update_Payment_Batch (P_Checkrun_Name,
1683 p_checkrun_id,
1684 rec_selected_checks.payment_id,
1685 l_calling_sequence);
1686
1687 END IF;
1688
1689 END LOOP;
1690
1691 -- Debug Information
1692 IF (DEBUG_Var = 'Y') THEN
1693 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Close Cursor c_selected_checks');
1694 END IF;
1695 -- End Debug
1696
1697 CLOSE c_selected_checks;
1698
1699 -- Debug Information
1700 IF (DEBUG_Var = 'Y') THEN
1701 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' P_AWT_Success ='||P_AWT_Success);
1702 END IF;
1703 -- End Debug
1704
1705 P_AWT_Success := AWT_SUCCESS;
1706
1707 EXCEPTION
1708 WHEN others THEN
1709 -- Debug Information
1710 IF (DEBUG_Var = 'Y') THEN
1711 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Error Do_AWT_Build_Payment_Batch= '||SQLERRM);
1712 END IF;
1713 -- End Debug
1714
1715 IF (SQLCODE <> -20001) THEN
1716 Fnd_Message.Set_Name ('JL', 'JL_ZZ_AP_DEBUG');
1717 Fnd_Message.Set_Token('ERROR', SQLERRM);
1718 Fnd_Message.Set_Token('CALLING_SEQUENCE', l_calling_sequence);
1719 Fnd_Message.Set_Token('PARAMETERS',
1720 ' Checkrun Name= ' || P_Checkrun_Name ||
1721 ', Calling Module= ' || P_Calling_Module);
1722 Fnd_Message.Set_Token('DEBUG_INFO', l_debug_info);
1723 END IF;
1724 P_AWT_Success := AWT_ERROR;
1725 App_Exception.Raise_Exception;
1726
1727 END Do_AWT_Build_Payment_Batch;
1728
1729
1730
1731 /**************************************************************************
1732 * *
1733 * Name : Do_AWT_Confirm_Payment_Batch *
1734 * Purpose : Withholding Tax Calculation for Payment Batches *
1735 * (Confirm Payment Stage) *
1736 * Processing units to be executed: *
1737 * 2. Create AWT Distribution Lines *
1738 * 3. Create AWT Invoices *
1739 * *
1740 **************************************************************************/
1741 PROCEDURE Do_AWT_Confirm_Payment_Batch
1742 (P_Checkrun_Name IN Varchar2,
1743 P_Checkrun_ID IN Number,
1744 P_Calling_Module IN Varchar2,
1745 P_Calling_Sequence IN Varchar2,
1746 P_AWT_Success OUT NOCOPY Varchar2,
1747 P_Last_Updated_By IN Number Default null,
1748 P_Last_Update_Login IN Number Default null,
1749 P_Program_Application_Id IN Number Default null,
1750 P_Program_Id IN Number Default null,
1751 P_Request_Id IN Number Default null)
1752 IS
1753 -------------------------------
1754 -- Local Variables Definition
1755 -------------------------------
1756 l_create_distr Varchar2(25);
1757 l_create_invoices Varchar2(25);
1758 -- l_awt_date Date;
1759 l_debug_info Varchar2(300);
1760 l_calling_sequence Varchar2(2000);
1761
1762 ------------------------------------
1763 -- Cursor to select all the invoices
1764 -- within the payment
1765 -------------------------------------
1766 /* CURSOR c_selected_invoices (P_Checkrun_Name IN Varchar2)
1767 IS
1768 SELECT apsi.invoice_id invoice_id,
1769 apsi.payment_num payment_num,
1770 apin.invoice_currency_code invoice_curr_code
1771 FROM ap_selected_invoices apsi,
1772 ap_selected_invoice_checks apsic,
1773 unique_ref2 invoice_id,
1774 docs.calling_app_doc_uniq
1775 ap_invoices apin
1776 WHERE apsic.checkrun_name = P_Checkrun_Name
1777 AND apsi.checkrun_name = P_Checkrun_Name
1778 AND (apsic.status_lookup_code = 'NEGOTIABLE'
1779 OR apsic.status_lookup_code = 'ISSUED')
1780 AND apsic.selected_check_id = apsi.pay_selected_check_id
1781 AND nvl(apsi.ok_to_pay_flag, 'Y') = 'Y'
1782 AND apin.invoice_id = apsi.invoice_id
1783 AND apsi.original_invoice_id IS NULL;
1784 */
1785
1786 -- R12 Changes uptake IBY
1787 CURSOR c_selected_invoices (p_checkrun_id IN NUMBER) IS
1788 SELECT docs.calling_app_doc_unique_ref2 invoice_id,
1789 docs.calling_app_doc_unique_ref3 payment_num,
1790 docs.document_currency_code invoice_curr_code,
1791 docs.payment_date,
1792 docs.org_id
1793 FROM IBY_FD_PAYMENTS_V ipmt,
1794 IBY_FD_DOCS_PAYABLE_V docs
1795 WHERE to_number(docs.calling_app_doc_unique_ref1) = p_checkrun_id
1796 AND ipmt.payment_id = docs.payment_id
1797 AND (ipmt.payment_status = 'NEGOTIABLE'
1798 OR ipmt.payment_status = 'ISSUED'
1799 OR ipmt.payment_status = 'FORMATTED'
1800 OR ipmt.payment_status = 'TRANSMITTED'
1801 OR ipmt.payment_status = 'ACKNOWLEDGED'
1802 OR ipmt.payment_status = 'BANK_VALIDATED'
1803 OR ipmt.payment_status = 'PAID')
1804 AND ipmt.payments_complete_flag ='Y'
1805 AND docs.calling_app_id= 200;
1806
1807 ----------------------
1808 -- Record Declaration
1809 ----------------------
1810 rec_sel_inv c_selected_invoices%ROWTYPE;
1811
1812 BEGIN
1813 -------------------------------
1814 -- Initializes debug variables
1815 -------------------------------
1816 l_calling_sequence := 'JL_AR_AP_WITHHOLDING_PKG' || '.' ||
1817 'Do_AWT_Confirm_Payment_Batch<--' ||
1818 P_Calling_Sequence;
1819
1820 -- Debug Information
1821 IF (DEBUG_Var = 'Y') THEN
1822 JL_ZZ_AP_EXT_AWT_UTIL.Debug ('PROCEDURE Do_AWT_Confirm_Payment_Batch'||
1823 'Parameter: P_Checkrun_Name = '||P_Checkrun_Name||
1824 'Parameter: P_Calling_Module = '||P_Calling_Module||
1825 'Parameter: P_Calling_Sequence = '||P_Calling_Sequence||
1826 'Parameter: P_Checkrun_ID = '||to_char(P_Checkrun_ID));
1827 END IF;
1828 -- End Debug
1829
1830 -----------------------------------
1831 -- Assumes successfully completion
1832 -----------------------------------
1833 P_AWT_Success := AWT_SUCCESS;
1834
1835 --------------------------------------------------
1836 -- Confirms each payment within the payment batch
1837 ---------------------------------------------------
1838 OPEN c_selected_invoices (P_Checkrun_ID);
1839
1840 -- Debug Information
1841 IF (DEBUG_Var = 'Y') THEN
1842 JL_ZZ_AP_EXT_AWT_UTIL.Debug ('Looping Thru c_selected_invoices');
1843 END IF;
1844 -- End Debug
1845
1846 LOOP
1847 FETCH c_selected_invoices INTO rec_sel_inv;
1848 EXIT WHEN c_selected_invoices%NOTFOUND;
1849
1850 -- Debug Information
1851 IF (DEBUG_Var = 'Y') THEN
1852 JL_ZZ_AP_EXT_AWT_UTIL.Debug
1853 (' Fetched Values: rec_sel_inv.invoice_id: '||to_char(rec_sel_inv.invoice_id));
1854 JL_ZZ_AP_EXT_AWT_UTIL.Debug
1855 (' Fetched Values: rec_sel_inv.payment_num: '||to_char(rec_sel_inv.payment_num));
1856 JL_ZZ_AP_EXT_AWT_UTIL.Debug
1857 (' Fetched Values: rec_sel_inv.invoice_curr_code: '||rec_sel_inv.invoice_curr_code);
1858 JL_ZZ_AP_EXT_AWT_UTIL.Debug
1859 (' Fetched Values: rec_sel_inv.payment_date: '||rec_sel_inv.payment_date);
1860 JL_ZZ_AP_EXT_AWT_UTIL.Debug
1861 (' Fetched Values: rec_sel_inv.org_id: '||to_char(rec_sel_inv.org_id));
1862 END IF;
1863 -- End Debug
1864
1865 ----------------------------
1866 -- Gets Withholding Options
1867 ----------------------------
1868
1869 -- Debug Information
1870 IF (DEBUG_Var = 'Y') THEN
1871 JL_ZZ_AP_EXT_AWT_UTIL.Debug ('==> Calling Get_Withholding_Options ');
1872 END IF;
1873 -- End Debug
1874
1875 --Jl_Zz_Ap_Withholding_Pkg.Get_Withholding_Options (l_create_distr,
1876 -- l_create_invoices);
1877 -- Bug 5442868
1878 SELECT nvl(create_awt_dists_type, 'NEVER'),
1879 nvl(create_awt_invoices_type, 'NEVER')
1880 INTO l_create_distr,
1881 l_create_invoices
1882 FROM ap_system_parameters_all
1883 WHERE org_id = rec_sel_inv.org_id;
1884
1885 -- Debug Information
1886 IF (DEBUG_Var = 'Y') THEN
1887 JL_ZZ_AP_EXT_AWT_UTIL.Debug
1888 ('Withholding Opt: l_crte_dst and l_crte_inv: '||l_create_distr||', '||l_create_invoices);
1889 END IF;
1890 -- End Debug
1891
1892 IF (l_create_distr <> 'PAYMENT') THEN
1893 -- Nothing to do
1894 RETURN;
1895 END IF;
1896
1897 ------------------------
1898 -- Obtains the AWT Date
1899 ------------------------
1900 /* SELECT apisc.check_date
1901 INTO l_awt_date
1902 FROM ap_invoice_selection_criteria apisc
1903 WHERE apisc.checkrun_name = P_Checkrun_Name;
1904 */
1905
1906 -----------------------------------------
1907 -- Creates AWT Distribution Lines
1908 -----------------------------------------
1909
1910 -- Debug Information
1911 IF (DEBUG_Var = 'Y') THEN
1912 JL_ZZ_AP_EXT_AWT_UTIL.Debug
1913 ('==> Calling Ap_Withholding_Pkg.Create_AWT_Distributions');
1914 END IF;
1915 -- End Debug
1916
1917 Ap_Withholding_Pkg.Create_AWT_Distributions
1918 (rec_sel_inv.invoice_id,
1919 P_Calling_Module,
1920 l_create_distr,
1921 rec_sel_inv.payment_num,
1922 rec_sel_inv.invoice_curr_code,
1923 P_Last_Updated_By,
1924 P_Last_Update_Login,
1925 P_Program_Application_Id,
1926 P_Program_Id,
1927 P_Request_Id,
1928 l_calling_sequence);
1929
1930 -- Debug Information
1931 IF (DEBUG_Var = 'Y') THEN
1932 JL_ZZ_AP_EXT_AWT_UTIL.Debug
1933 ('After Called Ap_Withholding_Pkg.Create_AWT_Distributions');
1934 END IF;
1935 -- End Debug
1936
1937 ------------------------
1938 -- Creates AWT Invoices
1939 ------------------------
1940 IF (l_create_invoices = 'PAYMENT') THEN
1941
1942 -- Debug Information
1943 IF (DEBUG_Var = 'Y') THEN
1944 JL_ZZ_AP_EXT_AWT_UTIL.Debug ('==> Calling Ap_Withholding_Pkg.Create_AWT_Invoices');
1945 END IF;
1946 -- End Debug
1947
1948 Ap_Withholding_Pkg.Create_AWT_Invoices
1949 (rec_sel_inv.invoice_id,
1950 rec_sel_inv.payment_date,
1951 P_Last_Updated_By,
1952 P_Last_Update_Login,
1953 P_Program_Application_Id,
1954 P_Program_Id,
1955 P_Request_Id,
1956 l_calling_sequence,
1957 P_Calling_Module);
1958 END IF;
1959
1960 END LOOP;
1961
1962 -- Debug Information
1963 IF (DEBUG_Var = 'Y') THEN
1964 JL_ZZ_AP_EXT_AWT_UTIL.Debug ('Close Cursor c_selected_invoices');
1965 END IF;
1966 -- End Debug
1967
1968 CLOSE c_selected_invoices;
1969
1970 ---------------------------
1971 -- Confirms credit letters
1972 ---------------------------
1973 -- Debug Information
1974 IF (DEBUG_Var = 'Y') THEN
1975 JL_ZZ_AP_EXT_AWT_UTIL.Debug ('==> Calling Confirm_Credit_Letters');
1976 END IF;
1977 -- End Debug
1978
1979 Confirm_Credit_Letters (P_Checkrun_Name,P_Checkrun_ID, l_calling_sequence);
1980
1981 -- Debug Information
1982 IF (DEBUG_Var = 'Y') THEN
1983 JL_ZZ_AP_EXT_AWT_UTIL.Debug ('PROCEDURE Do_AWT_Confirm_Payment_Batch (-)');
1984 END IF;
1985 -- End Debug
1986
1987 EXCEPTION
1988 WHEN others THEN
1989 -- Debug Information
1990 IF (DEBUG_Var = 'Y') THEN
1991 JL_ZZ_AP_EXT_AWT_UTIL.Debug ('Confirm ERROR: '||SQLERRM);
1992 END IF;
1993 -- end debug
1994
1995 IF (SQLCODE <> -20001) THEN
1996 Fnd_Message.Set_Name ('JL', 'JL_ZZ_AP_DEBUG');
1997 Fnd_Message.Set_Token('ERROR', SQLERRM);
1998 Fnd_Message.Set_Token('CALLING_SEQUENCE', l_calling_sequence);
1999 Fnd_Message.Set_Token('PARAMETERS',
2000 ' Checkrun Name= ' || P_Checkrun_Name ||
2001 ', Calling Module= ' || P_Calling_Module);
2002 Fnd_Message.Set_Token('DEBUG_INFO', l_debug_info);
2003 END IF;
2004
2005 App_Exception.Raise_Exception;
2006
2007 END Do_AWT_Confirm_Payment_Batch;
2008
2009
2010
2011 /**************************************************************************
2012 * *
2013 * Name : Calculate_AWT_Amounts *
2014 * Purpose : This procedure performs all the withholding calculations *
2015 * and generates the temporary distribution lines. *
2016 * It also updates buckets and credit letter amounts. *
2017 * *
2018 **************************************************************************/
2019 PROCEDURE Calculate_AWT_Amounts
2020 (P_Checkrun_Name IN Varchar2,
2021 P_Checkrun_ID IN Number,
2022 P_Check_Id IN Number,
2023 P_Selected_Check_Id IN Number,
2024 P_AWT_Date IN Date,
2025 P_Calling_Module IN Varchar2,
2026 P_Calling_Sequence IN Varchar2,
2027 P_Total_Wh_Amount OUT NOCOPY Number,
2028 P_AWT_Success OUT NOCOPY Varchar2,
2029 P_Last_Updated_By IN Number Default null,
2030 P_Last_Update_Login IN Number Default null,
2031 P_Program_Application_Id IN Number Default null,
2032 P_Program_Id IN Number Default null,
2033 P_Request_Id IN Number Default null)
2034 IS
2035 ------------------------
2036 -- Variables Definition
2037 ------------------------
2038 l_previous_awt_type_code Varchar2(30);
2039 l_previous_tax_id Number;
2040 l_previous_invoice_id Number;
2041 l_current_vendor_id Number;
2042 l_current_awt Number;
2043 l_initial_awt Number;
2044 l_tax_base_amt Number;
2045 l_gl_period_name Varchar2(100);
2046 l_base_currency_code Varchar2(15);
2047 l_not_found Boolean;
2048 l_total_wh_amount Number := 0;
2049 l_debug_info Varchar2(300);
2050 l_calling_sequence Varchar2(2000);
2051
2052 ------------------------------------------------------------
2053 -- Cursor to select all the withholding tax types and names
2054 -- associated to all the invoices within the Quick Payment.
2055 -- The cursor will be ordered by:
2056 -- - Withholding tax type, tax name, invoice ID
2057 -- (For those payment based withholding taxes)
2058 -- - Invoice ID, withholding tax type, tax name
2059 -- (For those invoice based withholding taxes)
2060 ------------------------------------------------------------
2061 CURSOR c_payment_withholdings (P_Check_Id Number)
2062 IS
2063 SELECT
2064 jlst.awt_type_code awt_type_code,
2065 jlsc.tax_id tax_id,
2066 apin.invoice_id invoice_id,
2067 apin.vendor_id vendor_id,
2068 apid.invoice_distribution_id invoice_distribution_id, -- Lines
2069 nvl(apin.invoice_amount, apin.base_amount) invoice_amount,
2070 nvl(apid.base_amount, apid.amount) line_amount,
2071 apip.amount payment_amount,
2072 apip.invoice_payment_id invoice_payment_id,
2073 apip.payment_num payment_num,
2074 jlty.taxable_base_amount_basis tax_base_amount_basis
2075 FROM
2076 jl_zz_ap_inv_dis_wh jlwh,
2077 ap_invoices apin,
2078 ap_invoice_distributions apid,
2079 ap_invoice_payments apip,
2080 jl_zz_ap_supp_awt_types jlst,
2081 jl_zz_ap_sup_awt_cd jlsc,
2082 jl_zz_ap_awt_types jlty
2083 WHERE
2084 apid.invoice_id = jlwh.invoice_id
2085 -- AND apid.distribution_line_number = jlwh.distribution_line_number - Lines 4382256
2086 AND apid.invoice_distribution_id = jlwh.invoice_distribution_id -- Lines
2087 AND apin.invoice_id = apid.invoice_id
2088 AND apin.invoice_id = apip.invoice_id
2089 AND jlwh.supp_awt_code_id = jlsc.supp_awt_code_id
2090 AND jlsc.supp_awt_type_id = jlst.supp_awt_type_id
2091 AND jlst.awt_type_code = jlty.awt_type_code
2092 AND apip.check_id = P_Check_Id
2093 -- added recently
2094 AND NVL(apid.REVERSAL_FLAG,'N') <> 'Y'
2095 ORDER BY
2096 to_number(decode(jlty.taxable_base_amount_basis, 'INVOICE',
2097 apin.invoice_id,
2098 DUMMY_INVOICE_ID)),
2099 jlst.awt_type_code,
2100 jlsc.tax_id,
2101 apin.invoice_id,
2102 apip.invoice_payment_id;
2103
2104 /* This would be the query needed if for quick payment we use IBY tables
2105 SELECT
2106 jlst.awt_type_code awt_type_code,
2107 jlsc.tax_id tax_id,
2108 apin.invoice_id invoice_id,
2109 apin.vendor_id vendor_id,
2110 apid.invoice_distribution_id invoice_distribution_id, -- Lines
2111 nvl(apin.invoice_amount, apin.base_amount) invoice_amount,
2112 nvl(apid.base_amount, apid.amount) line_amount,
2113 -- apsi.payment_amount payment_amount,
2114 docs.document_amount payment_amount,
2115 null invoice_payment_id,
2116 -- apsi.payment_num payment_num,
2117 to_number(docs.calling_app_doc_unique_ref3) payment_num,
2118 jlty.taxable_base_amount_basis tax_base_amount_basis
2119 FROM
2120 jl_zz_ap_inv_dis_wh jlwh,
2121 ap_invoices apin,
2122 ap_invoice_distributions apid,
2123 iby_hook_docs_in_pmt_t docs,
2124 jl_zz_ap_supp_awt_types jlst,
2125 jl_zz_ap_sup_awt_cd jlsc,
2126 jl_zz_ap_awt_types jlty
2127 WHERE docs.payment_id = P_Check_Id
2128 AND apid.invoice_id = jlwh.invoice_id
2129 -- AND apid.distribution_line_number = jlwh.distribution_line_number - Lines 4382256
2130 AND apid.invoice_distribution_id = jlwh.invoice_distribution_id -- Lines
2131 AND apin.invoice_id = apid.invoice_id
2132 AND apin.invoice_id = to_number(docs.calling_app_doc_unique_ref2)
2133 AND jlwh.supp_awt_code_id = jlsc.supp_awt_code_id
2134 AND jlsc.supp_awt_type_id = jlst.supp_awt_type_id
2135 AND jlst.awt_type_code = jlty.awt_type_code
2136 AND docs.dont_pay_flag = 'N'
2137 AND docs.calling_app_id =200
2138 ORDER BY
2139 to_number(decode(jlty.taxable_base_amount_basis, 'INVOICE',
2140 apin.invoice_id,
2141 DUMMY_INVOICE_ID)),
2142 jlst.awt_type_code,
2143 jlsc.tax_id,
2144 docs.calling_app_doc_unique_ref2,
2145 docs.calling_app_doc_unique_ref3;
2146 */
2147
2148
2149 ------------------------------------------------------------
2150 -- Cursor to select all the withholding tax types and names
2151 -- associated to all the invoices within the Payment Batch.
2152 -- The cursor will be ordered by:
2153 -- - Withholding tax type, tax name, invoice ID
2154 -- (For those payment based withholding taxes)
2155 -- - Invoice ID, withholding tax type, tax name
2156 -- (For those invoice based withholding taxes)
2157
2158 -- Change this cursor to Select Payments in the Payment ID
2159 ------------------------------------------------------------
2160 CURSOR c_payment_batch_withholdings (P_Selected_Check_Id Number)
2161 IS
2162 SELECT
2163 jlst.awt_type_code awt_type_code,
2164 jlsc.tax_id tax_id,
2165 apin.invoice_id invoice_id,
2166 apin.vendor_id vendor_id,
2167 apid.invoice_distribution_id invoice_distribution_id, -- Lines
2168 nvl(apin.invoice_amount, apin.base_amount) invoice_amount,
2169 nvl(apid.base_amount, apid.amount) line_amount,
2170 -- apsi.payment_amount payment_amount,
2171 docs.document_amount payment_amount,
2172 null invoice_payment_id,
2173 -- apsi.payment_num payment_num,
2174 to_number(docs.calling_app_doc_unique_ref3) payment_num,
2175 jlty.taxable_base_amount_basis tax_base_amount_basis
2176 FROM
2177 jl_zz_ap_inv_dis_wh jlwh,
2178 ap_invoices apin,
2179 ap_invoice_distributions apid,
2180 -- ap_selected_invoices apsi,
2181 iby_hook_docs_in_pmt_t docs,
2182 jl_zz_ap_supp_awt_types jlst,
2183 jl_zz_ap_sup_awt_cd jlsc,
2184 jl_zz_ap_awt_types jlty
2185 WHERE docs.payment_id = P_Selected_Check_Id
2186 AND apid.invoice_id = jlwh.invoice_id
2187 -- AND apid.distribution_line_number = jlwh.distribution_line_number - Lines 4382256
2188 AND apid.invoice_distribution_id = jlwh.invoice_distribution_id -- Lines
2189 AND apin.invoice_id = apid.invoice_id
2190 -- AND apin.invoice_id = apsi.invoice_id
2191 AND apin.invoice_id = to_number(docs.calling_app_doc_unique_ref2)
2192 AND jlwh.supp_awt_code_id = jlsc.supp_awt_code_id
2193 AND jlsc.supp_awt_type_id = jlst.supp_awt_type_id
2194 AND jlst.awt_type_code = jlty.awt_type_code
2195 AND docs.dont_pay_flag = 'N'
2196 AND docs.calling_app_id =200
2197 -- added recently
2198 AND NVL(apid.REVERSAL_FLAG,'N') <> 'Y'
2199 -- AND apsi.pay_selected_check_id = P_Selected_Check_Id
2200 -- AND nvl(apsi.ok_to_pay_flag, 'Y') = 'Y'
2201 -- AND apsi.original_invoice_id IS NULL
2202 ORDER BY
2203 to_number(decode(jlty.taxable_base_amount_basis, 'INVOICE',
2204 apin.invoice_id,
2205 DUMMY_INVOICE_ID)),
2206 jlst.awt_type_code,
2207 jlsc.tax_id,
2208 docs.calling_app_doc_unique_ref2,
2209 docs.calling_app_doc_unique_ref3;
2210
2211 ------------------------
2212 -- Records Declaration
2213 ------------------------
2214 rec_payment_wh Rec_Payment_Withholding;
2215 rec_awt_type jl_zz_ap_awt_types%ROWTYPE;
2216 rec_awt_name Jl_Zz_Ap_Withholding_Pkg.Rec_AWT_Code;
2217 rec_suppl_awt_type jl_zz_ap_supp_awt_types%ROWTYPE;
2218 rec_suppl_awt_name jl_zz_ap_sup_awt_cd%ROWTYPE;
2219
2220 ------------------------
2221 -- Tables Declaration
2222 ------------------------
2223 tab_payment_wh Jl_Zz_Ap_Withholding_Pkg.Tab_Withholding;
2224 tab_all_wh Jl_Zz_Ap_Withholding_Pkg.Tab_All_Withholding;
2225 tab_inv_amounts Tab_Amounts;
2226
2227 BEGIN
2228 -------------------------------
2229 -- Initializes debug variables
2230 -------------------------------
2231 l_calling_sequence := 'JL_AR_AP_WITHHOLDING_PKG' || '.' ||
2232 'Calculate_AWT_Amounts<--' || P_Calling_Sequence;
2233
2234 -- Debug Information
2235 IF (DEBUG_Var = 'Y') THEN
2236 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' ');
2237 JL_ZZ_AP_EXT_AWT_UTIL.Debug ('PROCEDURE Calculate_AWT_Amounts');
2238 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Parameters: P_Checkrun_Name : '||P_Checkrun_Name);
2239 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Parameters: P_Check_Id : '||to_char(P_Check_Id));
2240 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Parameters: P_Selected_Check_Id : '||to_char(P_Selected_Check_Id));
2241 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Parameters: P_AWT_Date : '||to_char(P_AWT_Date,'YYYY/MM/DD'));
2242 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Parameters: P_Calling_Module : '||P_Calling_Module);
2243 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' ');
2244 END IF;
2245 -- End Debug
2246
2247
2248 -----------------------------------
2249 -- Assumes successfully completion
2250 -----------------------------------
2251 P_AWT_Success := AWT_SUCCESS;
2252
2253 -------------------------------
2254 -- Initializes output argument
2255 -------------------------------
2256 P_Total_Wh_Amount := 0;
2257
2258 ---------------------------
2259 -- Gets generic parameters
2260 ---------------------------
2261 l_base_currency_code := Jl_Zz_Ap_Withholding_Pkg.Get_Base_Currency_Code;
2262 l_gl_period_name := Jl_Zz_Ap_Withholding_Pkg.Get_GL_Period_Name
2263 (P_AWT_Date);
2264
2265 -- Debug Information
2266 IF (DEBUG_Var = 'Y') THEN
2267 JL_ZZ_AP_EXT_AWT_UTIL.Debug ('Generic Parameters: l_base_currency_code: '||l_base_currency_code);
2268 JL_ZZ_AP_EXT_AWT_UTIL.Debug ('Generic Parameters: l_gl_period_name: '||l_gl_period_name);
2269 END IF;
2270 -- End Debug
2271
2272
2273 -------------------------------------------------------------
2274 -- Calculates the taxable base amount for each distribution
2275 -- line included within the payment
2276 -------------------------------------------------------------
2277
2278 -- Debug Information
2279 IF (DEBUG_Var = 'Y') THEN
2280 JL_ZZ_AP_EXT_AWT_UTIL.Debug ('==> Calling Calculate_Taxable_Base_Amounts');
2281 END IF;
2282 -- End Debug
2283
2284 Calculate_Taxable_Base_Amounts (P_Check_Id,
2285 P_Selected_Check_Id,
2286 l_base_currency_code,
2287 tab_inv_amounts,
2288 P_Calling_Module,
2289 l_calling_sequence);
2290
2291 -- Debug Information
2292 IF (DEBUG_Var = 'Y') THEN
2293 JL_ZZ_AP_EXT_AWT_UTIL.Debug('After Called Calculate_Taxable_Base_Amounts');
2294 END IF;
2295 -- End Debug
2296
2297 -------------------------------------------------------
2298 -- Defines a Save Point for the temporary calculations
2299 -------------------------------------------------------
2300 SAVEPOINT Before_Temporary_Calculations;
2301
2302 ----------------------------------------
2303 -- Opens the cursor to select all the
2304 -- withholdings to process
2305 ----------------------------------------
2306
2307 IF (P_Calling_Module = 'QUICKCHECK') THEN
2308 OPEN c_payment_withholdings (P_Check_Id);
2309
2310 -- Debug Information
2311 IF (DEBUG_Var = 'Y') THEN
2312 JL_ZZ_AP_EXT_AWT_UTIL.Debug ('Open Cursor c_payment_withholdings for Quick Payments');
2313 END IF;
2314 -- End Debug
2315
2316
2317 FETCH c_payment_withholdings INTO rec_payment_wh;
2318
2319 IF (c_payment_withholdings%NOTFOUND) THEN
2320
2321 -- Debug Information
2322 IF (DEBUG_Var = 'Y') THEN
2323 JL_ZZ_AP_EXT_AWT_UTIL.Debug ('No rows in the cursor c_payment_withholdings');
2324 END IF;
2325 -- End Debug
2326 RETURN;
2327 END IF;
2328
2329 ELSIF (P_Calling_Module = 'AUTOSELECT') THEN
2330 OPEN c_payment_batch_withholdings (P_Selected_Check_Id);
2331
2332 -- Debug Information
2333 IF (DEBUG_Var = 'Y') THEN
2334 JL_ZZ_AP_EXT_AWT_UTIL.Debug ('Open Cursor c_payment_batch_withholdings for AutoSelect');
2335 END IF;
2336 -- End Debug
2337
2338 FETCH c_payment_batch_withholdings INTO rec_payment_wh;
2339
2340 IF (c_payment_batch_withholdings%NOTFOUND) THEN
2341
2342 -- Debug Information
2343 IF (DEBUG_Var = 'Y') THEN
2344 JL_ZZ_AP_EXT_AWT_UTIL.Debug ('No rows in the cursor c_payment_batch_withholdings');
2345 END IF;
2346 -- End Debug
2347 RETURN;
2348 END IF;
2349 ELSE
2350 RETURN;
2351 END IF; -- End if (P_Calling_Module = 'QUICKCHECK')
2352
2353 -- Debug Information
2354 IF (DEBUG_Var = 'Y') THEN
2355 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Fetched Values: awt_type_code= '||rec_payment_wh.awt_type_code);
2356 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Fetched Values: tax_id= '||to_char(rec_payment_wh.tax_id));
2357 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Fetched Values: invoice_id= '||to_char(rec_payment_wh.invoice_id));
2358 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Fetched Values: vendor_id= '||to_char(rec_payment_wh.vendor_id));
2359 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Fetched Values: invoice_distribution_id = '||
2360 to_char(rec_payment_wh.invoice_distribution_id ));
2361 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Fetched Values: invoice_amount= '||to_char(rec_payment_wh.invoice_amount));
2362 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Fetched Values: line_amount= '||to_char(rec_payment_wh.line_amount));
2363 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Fetched Values: payment_amount= '||to_char(rec_payment_wh.payment_amount));
2364 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Fetched Values: invoice_payment_id= '||to_char(rec_payment_wh.invoice_payment_id));
2365 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Fetched Values: payment_num= '||to_char(rec_payment_wh.payment_num));
2366 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Fetched Values: tax_base_amount_basis= '||rec_payment_wh.tax_base_amount_basis);
2367 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' ');
2368 END IF;
2369 -- End Debug
2370
2371 ---------------------------------------
2372 -- Initialize auxiliary variables
2373 ---------------------------------------
2374 l_current_vendor_id := rec_payment_wh.vendor_id;
2375 l_previous_awt_type_code := rec_payment_wh.awt_type_code;
2376 l_previous_tax_id := rec_payment_wh.tax_id;
2377 l_previous_invoice_id := rec_payment_wh.invoice_id;
2378
2379 -------------------------------------------
2380 -- Obtains the all information associated
2381 -- to the withholding taxes and initialize
2382 -- the PL/SQL table to store them
2383 -------------------------------------------
2384
2385 -- Debug Information
2386 IF (DEBUG_Var = 'Y') THEN
2387 JL_ZZ_AP_EXT_AWT_UTIL.Debug ('==> Calling Initialize_Withholdings');
2388 END IF;
2389 -- End Debug
2390
2391 Initialize_Withholdings (rec_payment_wh.vendor_id,
2392 rec_payment_wh.awt_type_code,
2393 rec_payment_wh.tax_id,
2394 l_calling_sequence,
2395 rec_awt_type,
2396 rec_awt_name,
2397 rec_suppl_awt_type,
2398 rec_suppl_awt_name,
2399 tab_payment_wh);
2400
2401 -- Debug Information
2402 IF (DEBUG_Var = 'Y') THEN
2403 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' After Called Initialize_Withholdings');
2404 END IF;
2405 -- End Debug
2406
2407 l_current_awt := 0;
2408 l_initial_awt := 1;
2409
2410 --------------------------------------------
2411 -- Loop for each withholding tax type and
2412 -- tax name associated to the payment
2413 --------------------------------------------
2414 -- Debug Information
2415 IF (DEBUG_Var = 'Y') THEN
2416 JL_ZZ_AP_EXT_AWT_UTIL.Debug ('Loop for each withholding tax type and tax name associated to the payment');
2417 END IF;
2418 -- End Debug
2419
2420 LOOP
2421 ---------------------------------------
2422 -- Checks whether there are more taxes
2423 ---------------------------------------
2424 IF (P_Calling_Module = 'QUICKCHECK') THEN
2425 l_not_found := c_payment_withholdings%NOTFOUND;
2426 ELSIF (P_Calling_Module = 'AUTOSELECT') THEN
2427 l_not_found := c_payment_batch_withholdings%NOTFOUND;
2428 END IF;
2429
2430 IF (l_not_found) THEN
2431
2432 -----------------------------------------------------
2433 -- Process previous withholding tax name information
2434 -----------------------------------------------------
2435
2436 -- Debug Information
2437 IF (DEBUG_Var = 'Y') THEN
2438 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' ==> Calling Jl_Zz_Ap_Withholding_Pkg.Process_Withholding_Name when l_not_found');
2439 END IF;
2440 -- End Debug
2441
2442 Jl_Zz_Ap_Withholding_Pkg.Process_Withholding_Name
2443 (l_current_vendor_id,
2444 rec_awt_type,
2445 rec_awt_name,
2446 rec_suppl_awt_type,
2447 rec_suppl_awt_name,
2448 P_AWT_Date,
2449 tab_payment_wh,
2450 l_initial_awt,
2451 l_current_awt,
2452 tab_all_wh,
2453 P_AWT_Success);
2454
2455 -- Debug Information
2456 IF (DEBUG_Var = 'Y') THEN
2457 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' After Called Jl_Zz_Ap_Withholding_Pkg.Process_Withholding_Name when l_not_found');
2458 END IF;
2459 -- End Debug
2460
2461 IF (P_AWT_Success <> AWT_SUCCESS) THEN
2462 ROLLBACK TO Before_Temporary_Calculations;
2463 RETURN;
2464 END IF;
2465
2466 ------------------------------------------------------
2467 -- Process previous withholding tax type information.
2468 -- Prorates the withheld amount by invoice and
2469 -- inserts temporary distribution lines
2470 ------------------------------------------------------
2471 -- Debug Information
2472 IF (DEBUG_Var = 'Y') THEN
2473 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' ==> Calling Process_Withholdings when l_not_found');
2474 END IF;
2475 -- End Debug
2476
2477 Process_Withholdings (l_current_vendor_id,
2478 rec_awt_type,
2479 rec_suppl_awt_type,
2480 P_AWT_Date,
2481 l_gl_period_name,
2482 l_base_currency_code,
2483 P_Check_Id,
2484 P_Selected_Check_Id,
2485 l_calling_sequence,
2486 tab_payment_wh,
2487 l_total_wh_amount,
2488 P_AWT_Success,
2489 P_Last_Updated_By,
2490 P_Last_Update_Login,
2491 P_Program_Application_Id,
2492 P_Program_Id,
2493 P_Request_Id,
2494 P_Calling_Module,
2495 P_Checkrun_Name,
2496 P_Checkrun_ID,
2497 rec_payment_wh.payment_num);
2498
2499
2500 -- Debug Information
2501 IF (DEBUG_Var = 'Y') THEN
2502 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' After Called Process_Withholdings when l_not_found');
2503 END IF;
2504 -- End Debug
2505
2506 IF (P_AWT_Success <> AWT_SUCCESS) THEN
2507 ROLLBACK TO Before_Temporary_Calculations;
2508 RETURN;
2509 END IF;
2510
2511
2512 ---------------------------------------------------------
2513 -- Checks whether the withholding tax type has changed
2514 -- (or whether the invoice has changed for those invoice
2515 -- based withholding taxes)
2516 ---------------------------------------------------------
2517 ELSIF (rec_payment_wh.awt_type_code <> l_previous_awt_type_code OR
2518 (rec_payment_wh.awt_type_code = l_previous_awt_type_code AND
2519 rec_payment_wh.invoice_id <> l_previous_invoice_id AND
2520 rec_awt_type.taxable_base_amount_basis = 'INVOICE')) THEN
2521
2522 ------------------------------------------------
2523 -- Process previous withholding tax information
2524 ------------------------------------------------
2525
2526 -- Debug Information
2527 IF (DEBUG_Var = 'Y') THEN
2528 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' ==> Calling Jl_Zz_Ap_Withholding_Pkg.Process_Withholding_Name IN ');
2529 END IF;
2530 -- End Debug
2531
2532 Jl_Zz_Ap_Withholding_Pkg.Process_Withholding_Name
2533 (l_current_vendor_id,
2534 rec_awt_type,
2535 rec_awt_name,
2536 rec_suppl_awt_type,
2537 rec_suppl_awt_name,
2538 P_AWT_Date,
2539 tab_payment_wh,
2540 l_initial_awt,
2541 l_current_awt,
2542 tab_all_wh,
2543 P_AWT_Success);
2544
2545 -- Debug Information
2546 IF (DEBUG_Var = 'Y') THEN
2547 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' After Called Jl_Zz_Ap_Withholding_Pkg.Process_Withholding_Name IN ELSIF');
2548 END IF;
2549 -- End Debug
2550
2551 IF (P_AWT_Success <> AWT_SUCCESS) THEN
2552 ROLLBACK TO Before_Temporary_Calculations;
2553 RETURN;
2554 END IF;
2555
2556 ------------------------------------------------------
2557 -- Process previous withholding tax type information.
2558 -- Prorates the withheld amount by invoice and
2559 -- inserts temporary distribution lines
2560 ------------------------------------------------------
2561
2562 -- Debug Information
2563 IF (DEBUG_Var = 'Y') THEN
2564 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' ==> Calling Process_Withholdings IN ELSIF');
2565 END IF;
2566 -- End Debug
2567
2568 Process_Withholdings (l_current_vendor_id,
2569 rec_awt_type,
2570 rec_suppl_awt_type,
2571 P_AWT_Date,
2572 l_gl_period_name,
2573 l_base_currency_code,
2574 P_Check_Id,
2575 P_Selected_Check_Id,
2576 l_calling_sequence,
2577 tab_payment_wh,
2578 l_total_wh_amount,
2579 P_AWT_Success,
2580 P_Last_Updated_By,
2581 P_Last_Update_Login,
2582 P_Program_Application_Id,
2583 P_Program_Id,
2584 P_Request_Id,
2585 P_Calling_Module,
2586 P_Checkrun_Name,
2587 P_Checkrun_ID,
2588 rec_payment_wh.payment_num);
2589
2590 -- Debug Information
2591 IF (DEBUG_Var = 'Y') THEN
2592 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' After Called Process_Withholdings IN ELSIF');
2593 END IF;
2594 -- End Debug
2595
2596
2597 IF (P_AWT_Success <> AWT_SUCCESS) THEN
2598 ROLLBACK TO Before_Temporary_Calculations;
2599 RETURN;
2600 END IF;
2601
2602 -------------------------------------------
2603 -- Obtains the all information associated
2604 -- to the withholding taxes and initialize
2605 -- the PL/SQL table to store them
2606 -------------------------------------------
2607
2608 -- Debug Information
2609 IF (DEBUG_Var = 'Y') THEN
2610 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' ==> Calling Initialize_Withholdings IN ELSIF');
2611 END IF;
2612 -- End Debug
2613
2614 Initialize_Withholdings (rec_payment_wh.vendor_id,
2615 rec_payment_wh.awt_type_code,
2616 rec_payment_wh.tax_id,
2617 l_calling_sequence,
2618 rec_awt_type,
2619 rec_awt_name,
2620 rec_suppl_awt_type,
2621 rec_suppl_awt_name,
2622 tab_payment_wh);
2623
2624 -- Debug Information
2625 IF (DEBUG_Var = 'Y') THEN
2626 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' After Called Initialize_Withholdings IN ELSIF');
2627 END IF;
2628 -- End Debug
2629
2630 ----------------------------------
2631 -- Initialize auxiliary variables
2632 ----------------------------------
2633 l_current_awt := 0;
2634 l_initial_awt := 1;
2635 l_previous_awt_type_code := rec_payment_wh.awt_type_code;
2636 l_previous_tax_id := rec_payment_wh.tax_id;
2637 l_previous_invoice_id := rec_payment_wh.invoice_id;
2638
2639 -- Debug Information
2640 IF (DEBUG_Var = 'Y') THEN
2641 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Aux Variables: l_previous_awt_type_code = '||l_previous_awt_type_code);
2642 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Aux Variables: l_previous_tax_id = '||to_char(l_previous_tax_id));
2643 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Aux Variables: l_previous_invoice_id = '||to_char(l_previous_invoice_id));
2644 END IF;
2645 -- End Debug
2646
2647
2648 -------------------------------------------
2649 -- Checks whether the tax name has changed
2650 -------------------------------------------
2651 ELSIF (rec_payment_wh.tax_id <> l_previous_tax_id) THEN
2652
2653 ------------------------------------------------
2654 -- Process previous withholding tax information
2655 ------------------------------------------------
2656
2657 -- Debug Information
2658 IF (DEBUG_Var = 'Y') THEN
2659 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' ==> Calling Jl_Zz_Ap_Withholding_Pkg.Process_Withholding_Name when <> Tax Name');
2660 END IF;
2661 -- End Debug
2662 Jl_Zz_Ap_Withholding_Pkg.Process_Withholding_Name
2663 (l_current_vendor_id,
2664 rec_awt_type,
2665 rec_awt_name,
2666 rec_suppl_awt_type,
2667 rec_suppl_awt_name,
2668 P_AWT_Date,
2669 tab_payment_wh,
2670 l_initial_awt,
2671 l_current_awt,
2672 tab_all_wh,
2673 P_AWT_Success);
2674
2675 -- Debug Information
2676 IF (DEBUG_Var = 'Y') THEN
2677 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' After Called Jl_Zz_Ap_Withholding_Pkg.Process_Withholding_Name when Tax Name Changed');
2678 END IF;
2679 -- End Debug
2680
2681 IF (P_AWT_Success <> AWT_SUCCESS) THEN
2682 ROLLBACK TO Before_Temporary_Calculations;
2683 RETURN;
2684 END IF;
2685
2686 ---------------------------------------------
2687 -- Obtains the information associated to the
2688 -- new withholding tax
2689 ---------------------------------------------
2690
2691 -- Debug Information
2692 IF (DEBUG_Var = 'Y') THEN
2693 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' ==> Calling Jl_Zz_Ap_Withholding_Pkg.Initialize_Withholding_Name. Getting Tax Code Info');
2694 END IF;
2695 -- End Debug
2696
2697 Jl_Zz_Ap_Withholding_Pkg.Initialize_Withholding_Name
2698 (rec_payment_wh.awt_type_code,
2699 rec_payment_wh.tax_id,
2700 rec_payment_wh.vendor_id,
2701 rec_awt_name,
2702 rec_suppl_awt_name);
2703
2704 -----------------------------------
2705 -- Initializes auxiliary variables
2706 -----------------------------------
2707
2708 -- Debug Information
2709 IF (DEBUG_Var = 'Y') THEN
2710 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Initializes auxiliary variables: l_previous_tax_id = '||to_char(l_previous_tax_id));
2711 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Initializes auxiliary variables: l_initial_awt = '||to_char(l_initial_awt));
2712 END IF;
2713 -- End Debug
2714
2715 l_previous_tax_id := rec_payment_wh.tax_id;
2716 l_initial_awt := l_current_awt + 1;
2717
2718 END IF; -- End If IF (l_not_found)
2719
2720 ---------------------------------------
2721 -- Checks whether there are more taxes
2722 ---------------------------------------
2723
2724 -- Debug Information
2725 IF (DEBUG_Var = 'Y') THEN
2726 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Exit if there are NO more taxes');
2727 END IF;
2728 -- End Debug
2729
2730 EXIT WHEN l_not_found;
2731
2732 -------------------------------------------------------
2733 -- Checks whether withholding tax should be calculated
2734 -------------------------------------------------------
2735
2736 IF (NOT Withholding_Already_Calculated (
2737 rec_payment_wh.invoice_id,
2738 rec_awt_name.name,
2739 rec_awt_name.tax_id,
2740 rec_awt_type.taxable_base_amount_basis,
2741 tab_payment_wh,
2742 rec_payment_wh.payment_num,
2743 l_calling_sequence)) THEN
2744
2745 -- Debug Information
2746 IF (DEBUG_Var = 'Y') THEN
2747 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' IF(NOT Withholding_Already_Calculated - rec_awt_name.name: '||rec_awt_name.name);
2748 END IF;
2749 -- End Debug
2750
2751 -------------------------------------
2752 -- Obtains the taxable base amount
2753 -------------------------------------
2754
2755 l_tax_base_amt := Get_Taxable_Base_Amount
2756 (rec_payment_wh.invoice_id,
2757 rec_payment_wh.invoice_distribution_id , -- Lines
2758 rec_payment_wh.invoice_payment_id,
2759 rec_payment_wh.payment_num,
2760 rec_awt_type.taxable_base_amount_basis,
2761 rec_awt_name.Tax_Inclusive,
2762 tab_inv_amounts,
2763 P_Calling_Module,
2764 l_calling_sequence);
2765
2766 -- Debug Information
2767 IF (DEBUG_Var = 'Y') THEN
2768 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Got Taxable Base Amount for invoice_id = '||
2769 to_char(rec_payment_wh.invoice_id)||' = '||to_char(l_tax_base_amt));
2770 END IF;
2771 -- End Debug
2772
2773 --------------------------------------------------
2774 -- Stores the information of the current tax name
2775 -- into the PL/SQL table
2776 --------------------------------------------------
2777
2778 l_current_awt := l_current_awt + 1;
2779
2780 -- Debug Information
2781 IF (DEBUG_Var = 'Y') THEN
2782 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Increate l_current_awt = '||to_char(l_current_awt));
2783 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' ==>Calling Jl_Zz_Ap_Withholding_Pkg.Store_Tax_Name');
2784 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Invoice_id = '||to_char(rec_payment_wh.invoice_id)||' - '||
2785 'Tax_id = '||to_char(rec_payment_wh.tax_id)||' - '||
2786 'Tax Name = '||rec_awt_name.name);
2787 END IF;
2788 -- End Debug
2789
2790 Jl_Zz_Ap_Withholding_Pkg.Store_Tax_Name
2791 (tab_payment_wh,
2792 l_current_awt,
2793 rec_payment_wh.invoice_id,
2794 rec_payment_wh.invoice_distribution_id , -- Lines
2795 rec_payment_wh.awt_type_code,
2796 rec_payment_wh.tax_id,
2797 rec_awt_name.name,
2798 rec_awt_name.tax_code_combination_id,
2799 rec_awt_name.awt_period_type,
2800 rec_awt_type.jurisdiction_type,
2801 rec_payment_wh.line_amount,
2802 l_tax_base_amt,
2803 rec_payment_wh.invoice_payment_id,
2804 -- By Zmohiudd for bug 1849986
2805 rec_payment_wh.payment_num);
2806
2807 -- Debug Information
2808 IF (DEBUG_Var = 'Y') THEN
2809 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' After Called Jl_Zz_Ap_Withholding_Pkg.Store_Tax_Name');
2810 END IF;
2811 -- End Debug
2812
2813 END IF; -- NOT Withholding_Already_Calculated
2814
2815 ------------------------------------------------
2816 -- Fetches next withholding tax type / tax name
2817 ------------------------------------------------
2818 IF (P_Calling_Module = 'QUICKCHECK') THEN
2819 FETCH c_payment_withholdings INTO rec_payment_wh;
2820 ELSIF (P_Calling_Module = 'AUTOSELECT') THEN
2821 FETCH c_payment_batch_withholdings INTO rec_payment_wh;
2822 END IF;
2823
2824 -- Debug Information
2825 IF (DEBUG_Var = 'Y') THEN
2826 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Fetched Values: awt_type_code= '||rec_payment_wh.awt_type_code);
2827 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Fetched Values: tax_id= '||to_char(rec_payment_wh.tax_id));
2828 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Fetched Values: invoice_id= '||to_char(rec_payment_wh.invoice_id));
2829 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Fetched Values: vendor_id= '||to_char(rec_payment_wh.vendor_id));
2830 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Fetched Values: invoice_distribution_id = '||
2831 to_char(rec_payment_wh.invoice_distribution_id ));
2832 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Fetched Values: invoice_amount= '||to_char(rec_payment_wh.invoice_amount));
2833 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Fetched Values: line_amount= '||to_char(rec_payment_wh.line_amount));
2834 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Fetched Values: payment_amount= '||to_char(rec_payment_wh.payment_amount));
2835 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Fetched Values: invoice_payment_id= '||to_char(rec_payment_wh.invoice_payment_id));
2836 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Fetched Values: payment_num= '||to_char(rec_payment_wh.payment_num));
2837 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Fetched Values: tax_base_amount_basis= '||rec_payment_wh.tax_base_amount_basis);
2838 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' ');
2839 END IF;
2840 -- End Debug
2841
2842 END LOOP;
2843
2844 ---------------------------------
2845 -- Closes the withholding cursor
2846 ---------------------------------
2847 IF (P_Calling_Module = 'QUICKCHECK') THEN
2848
2849 -- Debug Information
2850 IF (DEBUG_Var = 'Y') THEN
2851 JL_ZZ_AP_EXT_AWT_UTIL.Debug ('Close Cursor c_payment_withholdings');
2852 END IF;
2853 -- End Debug
2854 CLOSE c_payment_withholdings;
2855
2856 ELSIF (P_Calling_Module = 'AUTOSELECT') THEN
2857
2858 -- Debug Information
2859 IF (DEBUG_Var = 'Y') THEN
2860 JL_ZZ_AP_EXT_AWT_UTIL.Debug ('Close Cursor c_payment_batch_withholdings');
2861 END IF;
2862 -- End Debug
2863 CLOSE c_payment_batch_withholdings;
2864
2865 END IF;
2866
2867 ------------------------
2868 -- Sets output argument
2869 ------------------------
2870
2871 -- Debug Information
2872 IF (DEBUG_Var = 'Y') THEN
2873 JL_ZZ_AP_EXT_AWT_UTIL.Debug ('P_Total_Wh_Amount: '||l_total_wh_amount);
2874 END IF;
2875 -- End Debug
2876
2877 P_Total_Wh_Amount := l_total_wh_amount;
2878
2879 -- Debug Information
2880 IF (DEBUG_Var = 'Y') THEN
2881 JL_ZZ_AP_EXT_AWT_UTIL.Print_Tax_Names(tab_payment_wh);
2882 JL_ZZ_AP_EXT_AWT_UTIL.Print_tab_all_wh(tab_all_wh);
2883 JL_ZZ_AP_EXT_AWT_UTIL.Debug ('End Procudure Calculate_AWT_Amounts ');
2884 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' ');
2885 END IF;
2886 -- End Debug
2887
2888 EXCEPTION
2889 WHEN others THEN
2890 IF (SQLCODE <> -20001) THEN
2891 Fnd_Message.Set_Name ('JL', 'JL_ZZ_AP_DEBUG');
2892 Fnd_Message.Set_Token('ERROR', SQLERRM);
2893 Fnd_Message.Set_Token('CALLING_SEQUENCE', l_calling_sequence);
2894 Fnd_Message.Set_Token('PARAMETERS',
2895 ' Checkrun Name= ' || P_Checkrun_Name ||
2896 ', Check Id= ' || to_char(P_Check_Id) ||
2897 ', Selected Check_Id= ' || to_char(P_Selected_Check_Id) ||
2898 ', AWT Date= ' || to_char(P_AWT_Date,'YYYY/MM/DD') ||
2899 ', Calling Module= ' || P_Calling_Module);
2900 Fnd_Message.Set_Token('DEBUG_INFO', l_debug_info);
2901 END IF;
2902 P_AWT_Success := AWT_ERROR;
2903 App_Exception.Raise_Exception;
2904
2905 END Calculate_AWT_Amounts;
2906
2907
2908 /**************************************************************************
2909 * *
2910 * Name : Initialize_Withholdings *
2911 * Purpose : Obtains all the attributes for the current withholding *
2912 * tax type and name. This procedure also initializes the *
2913 * PL/SQL table to store the withholdings *
2914 * *
2915 **************************************************************************/
2916 PROCEDURE Initialize_Withholdings
2917 (P_Vendor_Id IN Number,
2918 P_AWT_Type_Code IN Varchar2,
2919 P_Tax_Id IN Number,
2920 P_Calling_Sequence IN Varchar2,
2921 P_Rec_AWT_Type OUT NOCOPY jl_zz_ap_awt_types%ROWTYPE,
2922 P_Rec_AWT_Name OUT NOCOPY Jl_Zz_Ap_Withholding_Pkg.Rec_AWT_Code,
2923 P_Rec_Suppl_AWT_Type OUT NOCOPY jl_zz_ap_supp_awt_types%ROWTYPE,
2924 P_Rec_Suppl_AWT_Name OUT NOCOPY jl_zz_ap_sup_awt_cd%ROWTYPE,
2925 P_Wh_Table IN OUT NOCOPY Jl_Zz_Ap_Withholding_Pkg.Tab_Withholding)
2926 IS
2927
2928 l_debug_info Varchar2(300);
2929 l_calling_sequence Varchar2(2000);
2930
2931 BEGIN
2932 -------------------------------
2933 -- Initializes debug variables
2934 -------------------------------
2935 l_calling_sequence := 'JL_AR_AP_WITHHOLDING_PKG' || '.' ||
2936 'Initialize_Withholdings<--' || P_Calling_Sequence;
2937
2938 -----------------------------------------
2939 -- Initializes records and PL/SQL tables
2940 -----------------------------------------
2941 Jl_Zz_Ap_Withholding_Pkg.Initialize_Withholding_Type
2942 (P_AWT_Type_Code,
2943 P_Vendor_Id,
2944 P_Rec_AWT_Type,
2945 P_Rec_Suppl_AWT_Type);
2946
2947 Jl_Zz_Ap_Withholding_Pkg.Initialize_Withholding_Name
2948 (P_AWT_Type_Code,
2949 P_Tax_Id,
2950 P_Vendor_Id,
2951 P_Rec_AWT_Name,
2952 P_Rec_Suppl_AWT_Name);
2953
2954 Jl_Zz_Ap_Withholding_Pkg.Initialize_Withholding_Table
2955 (P_Wh_Table);
2956
2957 EXCEPTION
2958 WHEN others THEN
2959 IF (SQLCODE <> -20001) THEN
2960 Fnd_Message.Set_Name ('JL', 'JL_ZZ_AP_DEBUG');
2961 Fnd_Message.Set_Token('ERROR', SQLERRM);
2962 Fnd_Message.Set_Token('CALLING_SEQUENCE', l_calling_sequence);
2963 Fnd_Message.Set_Token('PARAMETERS',
2964 ' Vendor Id= ' || to_char(P_Vendor_Id) ||
2965 ', AWT Type Code= ' || P_AWT_Type_Code ||
2966 ', Tax Id= ' || to_char(P_Tax_Id));
2967 Fnd_Message.Set_Token('DEBUG_INFO', l_debug_info);
2968 END IF;
2969
2970 App_Exception.Raise_Exception;
2971
2972 END Initialize_Withholdings;
2973
2974
2975
2976
2977 /**************************************************************************
2978 * *
2979 * Name : Process_Withholdings *
2980 * Purpose : Process the information for the current withholding tax *
2981 * type and name *
2982 * *
2983 **************************************************************************/
2984 PROCEDURE Process_Withholdings
2985 (P_Vendor_Id IN Number,
2986 P_Rec_AWT_Type IN jl_zz_ap_awt_types%ROWTYPE,
2987 P_Rec_Suppl_AWT_Type IN jl_zz_ap_supp_awt_types%ROWTYPE,
2988 P_AWT_Date IN Date,
2989 P_GL_Period_Name IN Varchar2,
2990 P_Base_Currency_Code IN Varchar2,
2991 P_Check_Id IN Number,
2992 P_Selected_Check_Id IN Number,
2993 P_Calling_Sequence IN Varchar2,
2994 P_Tab_Withhold IN OUT NOCOPY Jl_Zz_Ap_Withholding_Pkg.Tab_Withholding,
2995 P_Total_Wh_Amount IN OUT NOCOPY Number,
2996 P_AWT_Success OUT NOCOPY Varchar2,
2997 P_Last_Updated_By IN Number Default null,
2998 P_Last_Update_Login IN Number Default null,
2999 P_Program_Application_Id IN Number Default null,
3000 P_Program_Id IN Number Default null,
3001 P_Request_Id IN Number Default null,
3002 P_Calling_Module IN Varchar2 Default null,
3003 P_Checkrun_Name IN Varchar2 Default null,
3004 P_Checkrun_ID IN Number Default null,
3005 P_Payment_Num IN Number Default null)
3006 IS
3007
3008 l_debug_info Varchar2(300);
3009 l_calling_sequence Varchar2(2000);
3010
3011 BEGIN
3012 -------------------------------
3013 -- Initializes debug variables
3014 -------------------------------
3015 l_calling_sequence := 'JL_AR_AP_WITHHOLDING_PKG' || '.' ||
3016 'Process_Withholdings<--' || P_Calling_Sequence;
3017
3018 -- Debug Information
3019 IF (DEBUG_Var = 'Y') THEN
3020 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' ');
3021 JL_ZZ_AP_EXT_AWT_UTIL.Debug ('PROCEDURE Process_Withholdings');
3022 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Parameters: P_Vendor_Id= '||to_char(P_Vendor_Id));
3023 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Parameters: P_Rec_AWT_Type.AWT_TYPE_CODE= '||P_Rec_AWT_Type.AWT_TYPE_CODE);
3024 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Parameters: P_Rec_Suppl_AWT_Type.AWT_TYPE_CODE= '||P_Rec_Suppl_AWT_Type.AWT_TYPE_CODE);
3025 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Parameters: P_AWT_Date= '||to_char(P_AWT_Date,'YYYY/MM/DD'));
3026 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Parameters: P_GL_Period_Name= '||P_GL_Period_Name);
3027 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Parameters: P_Base_Currency_Code= '||P_Base_Currency_Code);
3028 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Parameters: P_Selected_Check_Id= '||to_char(P_Selected_Check_Id));
3029 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Parameters: P_Checkrun_Id= '||to_char(P_Checkrun_Id));
3030 JL_ZZ_AP_EXT_AWT_UTIL.Print_Tax_Names(P_Tab_Withhold);
3031 END IF;
3032 -- End Debug
3033
3034
3035
3036 -----------------------------------
3037 -- Assumes successfully completion
3038 -----------------------------------
3039 P_AWT_Success := AWT_SUCCESS;
3040
3041 ------------------------------------------------------
3042 -- Checks whether there are elements within the table
3043 ------------------------------------------------------
3044 IF (P_Tab_Withhold.COUNT <= 0) THEN
3045 -- Nothing to do
3046 RETURN;
3047 END IF;
3048
3049 -----------------------------------------
3050 -- Defines a Save Point before inserting
3051 -----------------------------------------
3052 SAVEPOINT Before_Process_Withholding;
3053
3054 ------------------------------------------------
3055 -- Process previous withholding tax type
3056 ------------------------------------------------
3057
3058 -- Debug Information
3059 IF (DEBUG_Var = 'Y') THEN
3060 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' ==> Calling Jl_Zz_Ap_Withholding_Pkg.Process_Withholding_Type');
3061 END IF;
3062 -- End Debug
3063
3064 Jl_Zz_Ap_Withholding_Pkg.Process_Withholding_Type
3065 (P_Rec_AWT_Type,
3066 P_Rec_Suppl_AWT_Type,
3067 P_AWT_Date,
3068 P_Base_Currency_Code,
3069 P_Tab_Withhold);
3070
3071 -- Debug Information
3072 IF (DEBUG_Var = 'Y') THEN
3073 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' After Called Jl_Zz_Ap_Withholding_Pkg.Process_Withholding_Type');
3074 JL_ZZ_AP_EXT_AWT_UTIL.Print_Tax_Names(P_Tab_Withhold);
3075 END IF;
3076 -- End Debug
3077
3078
3079 --------------------------------------
3080 -- Updates Credit Letters Information
3081 --------------------------------------
3082
3083 -- Debug Information
3084 IF (DEBUG_Var = 'Y') THEN
3085 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' ==> Calling Update_Credit_Letter');
3086 END IF;
3087 -- End Debug
3088
3089 Update_Credit_Letter (P_Vendor_Id,
3090 P_Rec_AWT_Type,
3091 P_AWT_Date,
3092 P_Payment_Num,
3093 P_Check_Id,
3094 P_Selected_Check_Id,
3095 l_calling_sequence,
3096 P_Tab_Withhold,
3097 P_Last_Updated_By,
3098 P_Last_Update_Login,
3099 P_Program_Application_Id,
3100 P_Program_Id,
3101 P_Request_Id);
3102
3103 -- Debug Information
3104 IF (DEBUG_Var = 'Y') THEN
3105 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' After Called Update_Credit_Letter');
3106 END IF;
3107 -- End Debug
3108
3109 ------------------------------------------------
3110 -- Prorates withholding within the PL/SQL table
3111 -------------------------------------------------
3112
3113 -- Debug Information
3114 IF (DEBUG_Var = 'Y') THEN
3115 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' ==> Calling Jl_Zz_Ap_Withholding_Pkg.Prorate_Withholdings');
3116 JL_ZZ_AP_EXT_AWT_UTIL.Print_Tax_Names(P_Tab_Withhold);
3117 END IF;
3118 -- End Debug
3119
3120 Jl_Zz_Ap_Withholding_Pkg.Prorate_Withholdings (P_Tab_Withhold,
3121 P_Base_Currency_Code);
3122
3123 -- Debug Information
3124 IF (DEBUG_Var = 'Y') THEN
3125 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' After Called Jl_Zz_Ap_Withholding_Pkg.Prorate_Withholdings');
3126 JL_ZZ_AP_EXT_AWT_UTIL.Print_Tax_Names(P_Tab_Withhold);
3127 END IF;
3128 -- End Debug
3129
3130
3131 ----------------------------------------
3132 -- Insert Temporary Distributions Lines
3133 ----------------------------------------
3134 -- Debug Information
3135 IF (DEBUG_Var = 'Y') THEN
3136 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' ==> Calling Jl_Zz_Ap_Withholding_Pkg.Store_Into_Temporary_Table');
3137 END IF;
3138 -- End Debug
3139
3140 Jl_Zz_Ap_Withholding_Pkg.Store_Into_Temporary_Table
3141 (P_Tab_Withhold,
3142 P_Vendor_Id,
3143 P_AWT_Date,
3144 P_GL_Period_Name,
3145 P_Base_Currency_Code,
3146 FALSE, -- Revised Amount Flag
3147 TRUE, -- Prorated Amount Flag
3148 TRUE, -- Zero WH Applicable
3149 TRUE, -- Update Bucket
3150 P_AWT_Success,
3151 P_Last_Updated_By,
3152 P_Last_Update_Login,
3153 P_Program_Application_Id,
3154 P_Program_Id,
3155 P_Request_Id,
3156 P_Calling_Module,
3157 P_Checkrun_Name,
3158 P_Checkrun_ID,
3159 P_Payment_Num);
3160
3161 -- Debug Information
3162 IF (DEBUG_Var = 'Y') THEN
3163 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' After Called Jl_Zz_Ap_Withholding_Pkg.Store_Into_Temporary_Table');
3164 END IF;
3165 -- End Debug
3166
3167 IF (P_AWT_Success <> AWT_SUCCESS) THEN
3168 ROLLBACK TO Before_Process_Withholding;
3169 RETURN;
3170 END IF;
3171
3172 ----------------------------------------------
3173 -- Obtains total withheld amount for current
3174 -- withholding tax type
3175 ----------------------------------------------
3176 P_Total_Wh_Amount := nvl(P_Total_Wh_Amount, 0) +
3177 nvl(Total_Withholding_Amount(P_Tab_Withhold,
3178 l_calling_sequence), 0);
3179
3180
3181 -- Debug Information
3182 IF (DEBUG_Var = 'Y') THEN
3183 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Total withheld amount for current tax type: '||to_char(P_Total_Wh_Amount));
3184 JL_ZZ_AP_EXT_AWT_UTIL.Print_Tax_Names(P_Tab_Withhold);
3185 JL_ZZ_AP_EXT_AWT_UTIL.Debug ('End Process_Withholdings');
3186 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' ');
3187 END IF;
3188 -- End Debug
3189
3190 EXCEPTION
3191 WHEN others THEN
3192 IF (SQLCODE <> -20001) THEN
3193 Fnd_Message.Set_Name ('JL', 'JL_ZZ_AP_DEBUG');
3194 Fnd_Message.Set_Token('ERROR', SQLERRM);
3195 Fnd_Message.Set_Token('CALLING_SEQUENCE', l_calling_sequence);
3196 Fnd_Message.Set_Token('PARAMETERS',
3197 ' Vendor Id= ' || to_char(P_Vendor_Id) ||
3198 ', AWT Date= ' || to_char(P_AWT_Date,'YYYY/MM/DD') ||
3199 ', GL Period Name= ' || P_GL_Period_Name ||
3200 ', Base Currency Code= ' || P_Base_Currency_Code ||
3201 ', Check Id= ' || to_char(P_Check_Id) ||
3202 ', Selected Check_Id= ' || to_char(P_Selected_Check_Id) ||
3203 ', Calling Module= ' || P_Calling_Module ||
3204 ', Checkrun Name= ' || P_Checkrun_Name ||
3205 ', Payment Num= ' || to_char(P_Payment_Num));
3206 Fnd_Message.Set_Token('DEBUG_INFO', l_debug_info);
3207 END IF;
3208
3209 App_Exception.Raise_Exception;
3210
3211 END Process_Withholdings;
3212
3213
3214 /**************************************************************************
3215 * *
3216 * Name : Calculate_Taxable_Base_Amounts *
3217 * Purpose : Calculates the taxable base amount for each invoice *
3218 * distribution line included within the payment. The steps *
3219 * to do this are: *
3220 * 1. Prorates the payment amount for each distribution line *
3221 * 2. Rounds the prorated amount *
3222 * Taxable base amounts must be calculated all together in *
3223 * order to avoid rounding mistakes (last amount will be *
3224 * obtained by difference). *
3225 * *
3226 **************************************************************************/
3227 PROCEDURE Calculate_Taxable_Base_Amounts
3228 (P_Check_Id IN Number,
3229 P_Selected_Check_Id IN Number,
3230 P_Currency_Code IN Varchar2,
3231 P_Tab_Inv_Amounts IN OUT NOCOPY Tab_Amounts,
3232 P_Calling_Module IN Varchar2,
3233 P_Calling_Sequence IN Varchar2)
3234 IS
3235 ------------------------
3236 -- Variables definition
3237 ------------------------
3238 l_not_found Boolean := TRUE;
3239 l_invoice_id Number;
3240 l_dist_line_no Number;
3241 l_invoice_amount Number;
3242 l_invo_payment_id Number;
3243 l_previous_inv_pay_id Number;
3244 l_amount Number;
3245 l_tax_inclusive_amount Number;
3246 l_payment_amount Number;
3247 l_position Number;
3248 l_initial_position Number;
3249 l_cumulative_amount Number := 0;
3250 l_previous_invoice_id Number;
3251 l_previous_inv_pay_num Number;
3252 l_invo_payment_num Number;
3253 l_debug_info Varchar2(300);
3254 l_calling_sequence Varchar2(2000);
3255 rec_inv_amount Rec_Invoice_Amount;
3256
3257
3258 -------------------------------------------------------
3259 -- Cursor to select the invoices for the Quick Payment
3260 -------------------------------------------------------
3261 CURSOR c_invoice_amounts (P_Check_Id IN Number) IS
3262 SELECT apin.invoice_id invoice_id,
3263 apid.invoice_distribution_id invoice_distribution_id , -- Lines
3264 nvl(apid.base_amount, apid.amount) amount,
3265 nvl(apid.global_attribute4, 0) tax_inclusive_amount,
3266 nvl(apip.invoice_base_amount,apip.amount) payment_amount,
3267 apip.invoice_payment_id invo_payment_id
3268 FROM ap_invoices apin,
3269 ap_invoice_distributions apid,
3270 ap_invoice_payments apip
3271 WHERE apin.invoice_id = apid.invoice_id
3272 AND apin.invoice_id = apip.invoice_id
3273 AND apip.check_id = P_Check_Id
3274 AND apid.line_type_lookup_code <> 'AWT'
3275 -- added recently
3276 AND NVL(apid.REVERSAL_FLAG,'N') <> 'Y'
3277 ORDER BY apin.invoice_id,
3278 apip.invoice_payment_id,
3279 apid.invoice_distribution_id ; -- Lines
3280
3281 /* This would be the cursor to use if quick pmt uses IBY tables.
3282 SELECT apin.invoice_id invoice_id,
3283 apid.invoice_distribution_id invoice_distribution_id , -- Lines
3284 nvl(apid.base_amount, apid.amount) amount,
3285 nvl(apid.global_attribute4, 0) tax_inclusive_amount,
3286 -- apsi.payment_amount*nvl(apsi.invoice_exchange_rate,1) payment_amount,
3287 -- ,apsi.payment_num payment_num
3288 docs.document_amount* nvl(apsi.invoice_exchange_rate,1) payment_amount,
3289 docs.calling_app_doc_unique_ref3 payment_num
3290 FROM ap_invoices apin,
3291 ap_invoice_distributions apid,
3292 ap_selected_invoices apsi,
3293 iby_hook_docs_in_pmt_t docs
3294 WHERE apin.invoice_id = apid.invoice_id
3295 AND apin.invoice_id = apsi.invoice_id
3296 -- AND apsi.pay_selected_check_id = P_Selected_Check_Id
3297 and docs.payment_id = P_Check_Id
3298 and apsi.invoice_id = docs.calling_app_doc_unique_ref2
3299 -- AND apsi.original_invoice_id IS NULL
3300 AND docs.dont_pay_flag = 'N'
3301 AND apid.line_type_lookup_code <> 'AWT'
3302 and docs.calling_app_id = 200
3303 ORDER BY apin.invoice_id,
3304 docs.calling_app_doc_unique_ref3,
3305 apid.invoice_distribution_id ;
3306 */
3307
3308 ------------------------------------------------------
3309 -- Cursor to select the invoices for Payment Batches
3310 ------------------------------------------------------
3311 CURSOR c_batch_invoice_amounts (P_Selected_Check_Id IN Number) IS
3312 SELECT apin.invoice_id invoice_id,
3313 apid.invoice_distribution_id invoice_distribution_id , -- Lines
3314 nvl(apid.base_amount, apid.amount) amount,
3315 nvl(apid.global_attribute4, 0) tax_inclusive_amount,
3316 docs.document_amount* nvl(apsi.invoice_exchange_rate,1) payment_amount,
3317 docs.calling_app_doc_unique_ref3 payment_num
3318 FROM ap_invoices apin,
3319 ap_invoice_distributions apid,
3320 ap_selected_invoices apsi,
3321 iby_hook_docs_in_pmt_t docs
3322 WHERE apin.invoice_id = apid.invoice_id
3323 AND apin.invoice_id = apsi.invoice_id
3324 -- AND apsi.pay_selected_check_id = P_Selected_Check_Id
3325 and docs.payment_id = P_Selected_Check_Id
3326 and apsi.invoice_id = docs.calling_app_doc_unique_ref2
3327 -- AND apsi.original_invoice_id IS NULL
3328 AND docs.dont_pay_flag = 'N'
3329 AND apid.line_type_lookup_code <> 'AWT'
3330 AND docs.calling_app_id = 200
3331 -- added recently
3332 AND NVL(apid.REVERSAL_FLAG,'N') <> 'Y'
3333 ORDER BY apin.invoice_id,
3334 docs.calling_app_doc_unique_ref3,
3335 apid.invoice_distribution_id ; -- Lines
3336
3337 BEGIN
3338 -------------------------------
3339 -- Initializes debug variables
3340 -------------------------------
3341 l_calling_sequence := 'JL_AR_AP_WITHHOLDING_PKG' || '.' ||
3342 'Calculate_Taxable_Base_Amounts<--' ||
3343 P_Calling_Sequence;
3344
3345 -- Debug Information
3346 IF (DEBUG_Var = 'Y') THEN
3347 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' ');
3348 JL_ZZ_AP_EXT_AWT_UTIL.Debug ('PROCEDURE Calculate_Taxable_Base_Amounts');
3349 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Parameters: P_Check_Id= '||to_char(P_Check_Id));
3350 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Parameters: P_Selected_Check_Id= '||to_char(P_Selected_Check_Id));
3351 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Parameters: P_Currency_Code= '||P_Currency_Code);
3352 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Parameters: P_Calling_Module= '||P_Calling_Module);
3353 JL_ZZ_AP_EXT_AWT_UTIL.Print_tab_amounts(P_Tab_Inv_Amounts);
3354 END IF;
3355 -- End Debug
3356
3357 --------------------
3358 -- Open the cursor
3359 --------------------
3360 IF (P_Calling_Module = 'QUICKCHECK') THEN
3361 OPEN c_invoice_amounts (P_Check_Id);
3362
3363 -- Debug Information
3364 IF (DEBUG_Var = 'Y') THEN
3365 JL_ZZ_AP_EXT_AWT_UTIL.Debug ('Open Cursor c_invoice_amounts');
3366 END IF;
3367 -- End Debug
3368
3369 ELSIF (P_Calling_Module = 'AUTOSELECT') THEN
3370 OPEN c_batch_invoice_amounts (P_Selected_Check_Id);
3371
3372 -- Debug Information
3373 IF (DEBUG_Var = 'Y') THEN
3374 JL_ZZ_AP_EXT_AWT_UTIL.Debug ('Open Cursor c_batch_invoice_amounts');
3375 END IF;
3376 -- End Debug
3377
3378 END IF;
3379
3380 -----------------------------------
3381 -- Initializes auxiliary variables
3382 -----------------------------------
3383
3384 l_invoice_amount := 0;
3385 l_position := 1;
3386 l_initial_position := l_position;
3387 l_previous_invoice_id := null;
3388 l_previous_inv_pay_id := null;
3389 l_previous_inv_pay_num := null;
3390
3391 -- Debug Information
3392 IF (DEBUG_Var = 'Y') THEN
3393 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Auxiliary Variables: l_invoice_amount = '||to_char(l_invoice_amount));
3394 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Auxiliary Variables: l_position = '||to_char(l_position));
3395 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Auxiliary Variables: l_initial_position = '||to_char(l_initial_position));
3396 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Auxiliary Variables: l_previous_invoice_id = '||to_char(l_previous_invoice_id));
3397 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Auxiliary Variables: l_previous_inv_pay_id = '||to_char(l_previous_inv_pay_id));
3398 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' ');
3399 END IF;
3400 -- End Debug
3401
3402 LOOP
3403 IF (P_Calling_Module = 'QUICKCHECK') THEN
3404 FETCH c_invoice_amounts INTO l_invoice_id,
3405 l_dist_line_no,
3406 l_amount,
3407 l_tax_inclusive_amount,
3408 l_payment_amount,
3409 l_invo_payment_id;
3410 l_not_found := c_invoice_amounts%NOTFOUND;
3411
3412 ELSIF (P_Calling_Module = 'AUTOSELECT') THEN
3413 FETCH c_batch_invoice_amounts INTO l_invoice_id,
3414 l_dist_line_no,
3415 l_amount,
3416 l_tax_inclusive_amount,
3417 l_payment_amount,
3418 l_invo_payment_num;
3419 l_not_found := c_batch_invoice_amounts%NOTFOUND;
3420
3421 END IF;
3422 EXIT WHEN l_not_found;
3423
3424 -- Debug Information
3425 IF (DEBUG_Var = 'Y') THEN
3426 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' ');
3427 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' STARTING THE LOOP');
3428 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Fetched Values: Invoice_ID = '||to_char(l_invoice_id));
3429 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Fetched Values: l_dist_line_no = '||to_char(l_dist_line_no));
3430 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Fetched Values: l_amount = '||to_char(l_amount));
3431 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Fetched Values: l_tax_inclusive_amount = '||l_tax_inclusive_amount);
3432 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Fetched Values: l_payment_amount = '||to_char(l_payment_amount));
3433 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Fetched Values: l_invo_payment_id = '||to_char(l_invo_payment_id));
3434 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Fetched Values: l_invo_payment_num = '||to_char(l_invo_payment_num));
3435 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' ');
3436 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Auxiliary Variables: l_invoice_amount = '||to_char(l_invoice_amount));
3437 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Auxiliary Variables: l_position = '||to_char(l_position));
3438 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Auxiliary Variables: l_initial_position = '||to_char(l_initial_position));
3439 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Auxiliary Variables: l_previous_invoice_id = '||to_char(l_previous_invoice_id));
3440 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Auxiliary Variables: l_previous_inv_pay_id = '||to_char(l_previous_inv_pay_id));
3441 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Auxiliary Variables: l_previous_inv_pay_num = '||to_char(l_previous_inv_pay_num));
3442 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' ');
3443 END IF;
3444 -- End Debug
3445
3446 ---------------------------------
3447 -- Sets the total invoice amount
3448 -- Bug# 1743594
3449 ---------------------------------
3450 IF (P_Calling_Module = 'QUICKCHECK') THEN
3451 IF ((l_previous_invoice_id IS NOT NULL AND
3452 l_previous_invoice_id <> l_invoice_id)
3453 OR (l_previous_invoice_id IS NOT NULL AND
3454 l_previous_invoice_id = l_invoice_id AND
3455 l_invo_payment_id <> l_previous_inv_pay_id))
3456 THEN
3457
3458 FOR i IN l_initial_position .. (l_position - 1) LOOP
3459 P_Tab_Inv_Amounts(i).invoice_amount := l_invoice_amount;
3460 END LOOP;
3461
3462 -- Debug Information
3463 IF (DEBUG_Var = 'Y') THEN
3464 JL_ZZ_AP_EXT_AWT_UTIL.Debug(' QUICKCHECK and l_previous_invoice_id <> l_invoice_id');
3465 END IF;
3466 -- End Debug
3467
3468 l_invoice_amount := 0;
3469 l_initial_position := l_position;
3470 END IF;
3471 ELSIF (P_Calling_Module = 'AUTOSELECT') THEN
3472
3473 IF ((l_previous_invoice_id IS NOT NULL AND
3474 l_previous_invoice_id <> l_invoice_id)
3475 OR (l_previous_invoice_id IS NOT NULL AND
3476 l_previous_invoice_id = l_invoice_id AND
3477 l_invo_payment_num <> l_previous_inv_pay_num))
3478
3479 THEN
3480
3481 FOR i IN l_initial_position .. (l_position - 1) LOOP
3482 P_Tab_Inv_Amounts(i).invoice_amount := l_invoice_amount;
3483 END LOOP;
3484
3485 -- Debug Information
3486 IF (DEBUG_Var = 'Y') THEN
3487 JL_ZZ_AP_EXT_AWT_UTIL.Debug(' AUTOSELECT and l_previous_invoice_id <> l_invoice_id');
3488 END IF;
3489 -- End Debug
3490
3491
3492 l_invoice_amount := 0;
3493 l_initial_position := l_position;
3494 END IF;
3495 END IF;
3496
3497 ---------------------------------------------------
3498 -- Stores the invoice amount into the PL/SQL table
3499 ---------------------------------------------------
3500 rec_inv_amount.invoice_id := l_invoice_id;
3501 rec_inv_amount.invoice_distribution_id := l_dist_line_no; -- Lines
3502 rec_inv_amount.invoice_amount := null;
3503 rec_inv_amount.amount := l_amount;
3504 rec_inv_amount.tax_inclusive_amount := l_tax_inclusive_amount;
3505 rec_inv_amount.payment_amount := l_payment_amount;
3506 rec_inv_amount.taxable_base_amount := 0;
3507 rec_inv_amount.prorated_tax_incl_amt := 0;
3508 rec_inv_amount.invoice_payment_id := l_invo_payment_id;
3509 rec_inv_amount.invoice_payment_num := l_invo_payment_num;
3510 P_Tab_Inv_Amounts(l_position) := rec_inv_amount;
3511 l_position := l_position + 1;
3512
3513 l_invoice_amount := l_invoice_amount + l_amount;
3514 l_previous_invoice_id := l_invoice_id;
3515 l_previous_inv_pay_id := l_invo_payment_id;
3516 l_previous_inv_pay_num := l_invo_payment_num;
3517
3518 END LOOP;
3519
3520 -- Debug Information
3521 IF (DEBUG_Var = 'Y') THEN
3522 JL_ZZ_AP_EXT_AWT_UTIL.Debug('After lOOP');
3523 END IF;
3524 -- End Debug
3525
3526 ---------------------------------
3527 -- Sets the total invoice amount
3528 ---------------------------------
3529 FOR i IN l_initial_position .. P_Tab_Inv_Amounts.COUNT LOOP
3530 P_Tab_Inv_Amounts(i).invoice_amount := l_invoice_amount;
3531 END LOOP;
3532
3533 -- Debug Information
3534 IF (DEBUG_Var = 'Y') THEN
3535 JL_ZZ_AP_EXT_AWT_UTIL.Debug(' Sets the total invoice amount');
3536 JL_ZZ_AP_EXT_AWT_UTIL.Print_tab_amounts(P_Tab_Inv_Amounts);
3537 END IF;
3538 -- End Debug
3539
3540 ----------------
3541 -- Close cursor
3542 ----------------
3543 IF (P_Calling_Module = 'QUICKCHECK') THEN
3544
3545 -- Debug Information
3546 IF (DEBUG_Var = 'Y') THEN
3547 JL_ZZ_AP_EXT_AWT_UTIL.Debug('Close Cursor c_invoice_amounts');
3548 END IF;
3549 -- End Debug
3550 CLOSE c_invoice_amounts;
3551
3552 ELSIF (P_Calling_Module = 'AUTOSELECT') THEN
3553
3554 -- Debug Information
3555 IF (DEBUG_Var = 'Y') THEN
3556 JL_ZZ_AP_EXT_AWT_UTIL.Debug('Close Cursor c_invoice_amounts');
3557 END IF;
3558 -- End Debug
3559 CLOSE c_batch_invoice_amounts;
3560
3561 END IF;
3562
3563 ------------------------------------------------------
3564 -- Checks whether there are elements within the table
3565 ------------------------------------------------------
3566 IF (P_Tab_Inv_Amounts.COUNT <= 0) THEN
3567 -- Nothing to do
3568 RETURN;
3569 END IF;
3570
3571 -----------------------------------
3572 -- Initializes auxiliary variables
3573 -----------------------------------
3574 l_cumulative_amount := 0;
3575 l_previous_invoice_id := P_Tab_Inv_Amounts(1).invoice_id;
3576
3577 ---------------------------------------------------------------
3578 -- Calculates taxable base amounts by prorating payment amount
3579 -- for each different invoice
3580 ---------------------------------------------------------------
3581 FOR i IN 1 .. P_Tab_Inv_Amounts.COUNT LOOP
3582 /* Bug 2065366
3583 IF (l_previous_invoice_id <> P_Tab_Inv_Amounts(i).invoice_id) THEN
3584 P_Tab_Inv_Amounts(i-1).taxable_base_amount :=
3585 P_Tab_Inv_Amounts(i-1).payment_amount -
3586 l_cumulative_amount;
3587 l_cumulative_amount := 0;
3588 l_previous_invoice_id := P_Tab_Inv_Amounts(i).invoice_id;
3589
3590 -- Debug Information
3591 IF (DEBUG_Var = 'Y') THEN
3592 JL_ZZ_AP_EXT_AWT_UTIL.Debug(' TBA = tax base amts - l_cumulative_amount');
3593 JL_ZZ_AP_EXT_AWT_UTIL.Debug(' Invoice_id = '||to_char(P_Tab_Inv_Amounts(i-1).invoice_id)||' '||
3594 'Taxable Base Amount = '||to_char(P_Tab_Inv_Amounts(i-1).taxable_base_amount));
3595 END IF;
3596 -- End Debug
3597
3598 END IF;
3599 */
3600
3601 ----------------------------------
3602 -- Calculates taxable base amount
3603 ----------------------------------
3604 -- Bug 2477413
3605 -- Added the following IF condition to avoid
3606 -- division by zero, this happens incase of prepayments
3607 -- applied/unapplied to a invoice and is selected in Payment Batch.
3608
3609 IF P_Tab_Inv_Amounts(i).invoice_amount <> 0 THEN
3610 P_Tab_Inv_Amounts(i).taxable_base_amount :=
3611 P_Tab_Inv_Amounts(i).amount *
3612 P_Tab_Inv_Amounts(i).payment_amount /
3613 P_Tab_Inv_Amounts(i).invoice_amount;
3614
3615 -- Debug Information
3616 IF (DEBUG_Var = 'Y') THEN
3617 JL_ZZ_AP_EXT_AWT_UTIL.Debug(' Calculates tax base amts prorating payment amt for each different inv');
3618 JL_ZZ_AP_EXT_AWT_UTIL.Debug(' Invoice_id = '||to_char(P_Tab_Inv_Amounts(i).invoice_id)||' '||
3619 'Taxable Base Amount = '||to_char(P_Tab_Inv_Amounts(i).taxable_base_amount));
3620 END IF;
3621 -- End Debug
3622
3623 ELSE
3624 P_Tab_Inv_Amounts(i).taxable_base_amount := 0 ;
3625 END IF;
3626
3627 P_Tab_Inv_Amounts(i).taxable_base_amount :=
3628 Ap_Utilities_Pkg.Ap_Round_Currency (
3629 P_Tab_Inv_Amounts(i).taxable_base_amount,
3630 P_Currency_Code);
3631
3632
3633 --------------------------------------------
3634 -- Calculates prorated tax inclusive amount
3635 --------------------------------------------
3636 -- Bug 2477413
3637 -- Added the following IF condition to avoid
3638 -- division by zero, this happens incase of prepayments
3639 -- applied/unapplied to a invoice and is selected in Payment Batch.
3640
3641 IF P_Tab_Inv_Amounts(i).invoice_amount <> 0 THEN
3642 P_Tab_Inv_Amounts(i).prorated_tax_incl_amt :=
3643 P_Tab_Inv_Amounts(i).tax_inclusive_amount *
3644 P_Tab_Inv_Amounts(i).payment_amount /
3645 P_Tab_Inv_Amounts(i).invoice_amount;
3646 ELSE
3647 P_Tab_Inv_Amounts(i).prorated_tax_incl_amt := 0;
3648 END IF;
3649
3650 P_Tab_Inv_Amounts(i).prorated_tax_incl_amt :=
3651 Ap_Utilities_Pkg.Ap_Round_Currency (
3652 P_Tab_Inv_Amounts(i).prorated_tax_incl_amt,
3653 P_Currency_Code);
3654
3655 -- Debug Information
3656 IF (DEBUG_Var = 'Y') THEN
3657 JL_ZZ_AP_EXT_AWT_UTIL.Debug(' Calculates prorated tax inclusive amount');
3658 END IF;
3659 -- End Debug
3660
3661 IF (i > 1) THEN
3662 IF (P_Tab_Inv_Amounts(i-1).invoice_id =
3663 P_Tab_Inv_Amounts(i).invoice_id) THEN
3664 l_cumulative_amount := l_cumulative_amount +
3665 P_Tab_Inv_Amounts(i-1).taxable_base_amount;
3666
3667 -- Debug Information
3668 IF (DEBUG_Var = 'Y') THEN
3669 JL_ZZ_AP_EXT_AWT_UTIL.Debug(' Cumulative Amount = '||to_char(l_cumulative_amount));
3670 END IF;
3671 -- End Debug
3672
3673 END IF;
3674 END IF;
3675
3676 END LOOP;
3677
3678 -- Debug Information
3679 IF (DEBUG_Var = 'Y') THEN
3680 JL_ZZ_AP_EXT_AWT_UTIL.Debug(' END Loop thru Tax Invoice Amounts');
3681 JL_ZZ_AP_EXT_AWT_UTIL.Print_tab_amounts(P_Tab_Inv_Amounts);
3682 END IF;
3683 -- End Debug
3684
3685 /*
3686 -- Bug# 1743594
3687 IF (P_Calling_Module = 'AUTOSELECT') THEN
3688 -------------------------
3689 -- Processes last amount
3690 -------------------------
3691 P_Tab_Inv_Amounts(P_Tab_Inv_Amounts.COUNT).taxable_base_amount :=
3692 P_Tab_Inv_Amounts(P_Tab_Inv_Amounts.COUNT).payment_amount -
3693 l_cumulative_amount;
3694
3695 -- Debug Information
3696 IF (DEBUG_Var = 'Y') THEN
3697 JL_ZZ_AP_EXT_AWT_UTIL.Debug(' Last Row for AUTOSELECT - Payment Amount');
3698 END IF;
3699 -- End Debug
3700
3701 END IF;
3702 */
3703
3704 -- Debug Information
3705 IF (DEBUG_Var = 'Y') THEN
3706 JL_ZZ_AP_EXT_AWT_UTIL.Debug('END Procedure Calculate_Taxable_Base_Amounts');
3707 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' ');
3708 END IF;
3709 -- End Debug
3710
3711
3712 EXCEPTION
3713 WHEN others THEN
3714 IF (SQLCODE <> -20001) THEN
3715 Fnd_Message.Set_Name ('JL', 'JL_ZZ_AP_DEBUG');
3716 Fnd_Message.Set_Token('ERROR', SQLERRM);
3717 Fnd_Message.Set_Token('CALLING_SEQUENCE', l_calling_sequence);
3718 Fnd_Message.Set_Token('PARAMETERS',
3719 ' Check Id= ' || to_char(P_Check_Id) ||
3720 ', Selected Check Id= ' || to_char(P_Selected_Check_Id) ||
3721 ', Currency Code= ' || P_Currency_Code ||
3722 ', Calling Module= ' || P_Calling_Module);
3723 Fnd_Message.Set_Token('DEBUG_INFO', l_debug_info);
3724 END IF;
3725
3726 App_Exception.Raise_Exception;
3727
3728 END Calculate_Taxable_Base_Amounts;
3729
3730
3731 /**************************************************************************
3732 * *
3733 * Name : Get_Taxable_Base_Amount *
3734 * Purpose : Obtains the taxable base amount for a particular invoice *
3735 * distribution line. *
3736 * *
3737 **************************************************************************/
3738 FUNCTION Get_Taxable_Base_Amount
3739 (P_Invoice_Id IN Number,
3740 P_Distribution_Line_No IN Number,
3741 P_Invoice_Payment_ID IN Number,
3742 P_Invoice_Payment_Num IN Number,
3743 P_Tax_Base_Amount_Basis IN Varchar2,
3744 P_Tax_Inclusive_Flag IN Varchar2,
3745 P_Tab_Inv_Amounts IN Tab_Amounts,
3746 P_Calling_Module IN Varchar2,
3747 P_Calling_Sequence IN Varchar2)
3748 RETURN NUMBER
3749 IS
3750
3751 l_debug_info Varchar2(300);
3752 l_calling_sequence Varchar2(2000);
3753
3754 BEGIN
3755 -------------------------------
3756 -- Initializes debug variables
3757 -------------------------------
3758 l_calling_sequence := 'JL_AR_AP_WITHHOLDING_PKG' || '.' ||
3759 'Get_Taxable_Base_Amount<--' || P_Calling_Sequence;
3760
3761 -- Debug Information
3762 IF (DEBUG_Var = 'Y') THEN
3763 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' ');
3764 JL_ZZ_AP_EXT_AWT_UTIL.Debug ('FUNCTION Get_Taxable_Base_Amount');
3765 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Parameters: P_Invoice_Id= '||to_char(P_Invoice_Id));
3766 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Parameters: P_Distribution_Line_No= '||to_char(P_Distribution_Line_No));
3767 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Parameters: P_Invoice_Payment_ID= '||to_char(P_Invoice_Payment_ID));
3768 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Parameters: P_Tax_Base_Amount_Basis= '||P_Tax_Base_Amount_Basis);
3769 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Parameters: P_Tax_Inclusive_Flag= '||P_Tax_Inclusive_Flag);
3770 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Parameters: P_Calling_Module= '||P_Calling_Module);
3771 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' ');
3772 END IF;
3773 -- End Debug
3774
3775 -----------------------------------
3776 -- Obtains the taxable base amount
3777 -----------------------------------
3778 FOR i IN 1 .. P_Tab_Inv_Amounts.COUNT LOOP
3779
3780 IF (P_Calling_Module = 'QUICKCHECK') THEN
3781 EXIT WHEN (P_Tab_Inv_Amounts(i).invoice_id > P_Invoice_Id);
3782 /* Comment the next 4 following lines bug# 1743594
3783 OR
3784 (P_Tab_Inv_Amounts(i).invoice_id = P_Invoice_Id AND
3785 P_Tab_Inv_Amounts(i).invoice_distribution_id >
3786 P_Distribution_Line_No);
3787 */
3788
3789 -- Bug# 1743594. Add last condition.
3790 IF (P_Tab_Inv_Amounts(i).invoice_id = P_Invoice_Id AND
3791 P_Tab_Inv_Amounts(i).invoice_distribution_id =
3792 P_Distribution_Line_No AND -- Lines
3793 P_Tab_Inv_Amounts(i).invoice_payment_id = P_Invoice_Payment_ID) THEN
3794
3795 ---------------------------------------------------
3796 -- Returns taxable base amount for 'Invoice Based'
3797 -- withholding taxes
3798 ---------------------------------------------------
3799 IF (P_Tax_Base_Amount_Basis = 'INVOICE') THEN
3800 IF (nvl(P_Tax_Inclusive_Flag, 'N') = 'Y') THEN
3801
3802 -- Debug Information
3803 IF (DEBUG_Var = 'Y') THEN
3804 JL_ZZ_AP_EXT_AWT_UTIL.Debug ('FUNCTION Get_Taxable_Base_Amount for invoice '||
3805 to_char(P_Tab_Inv_Amounts(i).invoice_id));
3806 END IF;
3807 -- End Debug
3808
3809 RETURN P_Tab_Inv_Amounts(i).amount;
3810 ELSE
3811 RETURN P_Tab_Inv_Amounts(i).amount -
3812 P_Tab_Inv_Amounts(i).tax_inclusive_amount;
3813 END IF;
3814
3815 ---------------------------------------------------
3816 -- Returns taxable base amount for 'Payment Based'
3817 -- withholding taxes
3818 ---------------------------------------------------
3819 ELSIF (P_Tax_Base_Amount_Basis = 'PAYMENT') THEN
3820 IF (nvl(P_Tax_Inclusive_Flag, 'N') = 'Y') THEN
3821 RETURN P_Tab_Inv_Amounts(i).taxable_base_amount;
3822 ELSE
3823 RETURN P_Tab_Inv_Amounts(i).taxable_base_amount -
3824 P_Tab_Inv_Amounts(i).prorated_tax_incl_amt;
3825 END IF;
3826
3827 END IF;
3828
3829 END IF;
3830 -- Bug# 1743594
3831 ELSIF (P_Calling_Module = 'AUTOSELECT') THEN
3832 EXIT WHEN (P_Tab_Inv_Amounts(i).invoice_id > P_Invoice_Id);
3833 -- Bug 2065366 Comment next lines
3834 -- OR ((P_Tab_Inv_Amounts(i).invoice_id = P_Invoice_Id) AND
3835 -- (P_Tab_Inv_Amounts(i).invoice_distribution_id >
3836 -- P_Distribution_Line_No));
3837
3838 IF (P_Tab_Inv_Amounts(i).invoice_id = P_Invoice_Id AND
3839 P_Tab_Inv_Amounts(i).invoice_distribution_id = P_Distribution_Line_No AND -- Lines
3840 P_Tab_Inv_Amounts(i).invoice_payment_num = P_Invoice_Payment_Num)
3841 THEN
3842
3843 ---------------------------------------------------
3844 -- Returns taxable base amount for 'Invoice Based'
3845 -- withholding taxes
3846 ---------------------------------------------------
3847 IF (P_Tax_Base_Amount_Basis = 'INVOICE') THEN
3848 IF (nvl(P_Tax_Inclusive_Flag, 'N') = 'Y') THEN
3849 RETURN P_Tab_Inv_Amounts(i).amount;
3850 ELSE
3851 RETURN P_Tab_Inv_Amounts(i).amount -
3852 P_Tab_Inv_Amounts(i).tax_inclusive_amount;
3853 END IF;
3854
3855 ---------------------------------------------------
3856 -- Returns taxable base amount for 'Payment Based'
3857 -- withholding taxes
3858 ---------------------------------------------------
3859 ELSIF (P_Tax_Base_Amount_Basis = 'PAYMENT') THEN
3860 IF (nvl(P_Tax_Inclusive_Flag, 'N') = 'Y') THEN
3861 RETURN P_Tab_Inv_Amounts(i).taxable_base_amount;
3862 ELSE
3863 RETURN P_Tab_Inv_Amounts(i).taxable_base_amount -
3864 P_Tab_Inv_Amounts(i).prorated_tax_incl_amt;
3865 END IF;
3866
3867 END IF;
3868
3869 END IF;
3870
3871 END IF;
3872 END LOOP;
3873
3874 RETURN 0;
3875
3876 EXCEPTION
3877 WHEN others THEN
3878 IF (SQLCODE <> -20001) THEN
3879 Fnd_Message.Set_Name ('JL', 'JL_ZZ_AP_DEBUG');
3880 Fnd_Message.Set_Token('ERROR', SQLERRM);
3881 Fnd_Message.Set_Token('CALLING_SEQUENCE', l_calling_sequence);
3882 Fnd_Message.Set_Token('PARAMETERS',
3883 ' Invoice Id= ' || to_char(P_Invoice_Id) ||
3884 ', Distribution Line No= ' || to_char(P_Distribution_Line_No) ||
3885 ', Tax Base Amount Basis= ' || P_Tax_Base_Amount_Basis ||
3886 ', Tax Inclusive Flag= ' || P_Tax_Inclusive_Flag);
3887 Fnd_Message.Set_Token('DEBUG_INFO', l_debug_info);
3888 END IF;
3889
3890 App_Exception.Raise_Exception;
3891
3892 END Get_Taxable_Base_Amount;
3893
3894
3895
3896
3897 /**************************************************************************
3898 * *
3899 * Name : Get_Credit_Letter_Amount *
3900 * Purpose : Obtains the credit letter amount for a particular *
3901 * supplier and withholding tax type *
3902 * *
3903 **************************************************************************/
3904 FUNCTION Get_Credit_Letter_Amount
3905 (P_Vendor_Id IN Number,
3906 P_AWT_Type_Code IN Varchar2,
3907 P_Calling_Sequence IN Varchar2)
3908 RETURN NUMBER
3909 IS
3910
3911 l_seq_num Number;
3912 l_credit_letter_amount Number;
3913 l_debug_info Varchar2(300);
3914 l_calling_sequence Varchar2(2000);
3915
3916 BEGIN
3917 -------------------------------
3918 -- Initializes debug variables
3919 -------------------------------
3920 l_calling_sequence := 'JL_AR_AP_WITHHOLDING_PKG' || '.' ||
3921 'Get_Credit_Letter_Amount<--' || P_Calling_Sequence;
3922
3923 ------------------------------------------------------
3924 -- Obtains the credit letter amount for the supplier
3925 -- and the withholding tax type
3926 ------------------------------------------------------
3927 SELECT max(seq_num)
3928 INTO l_seq_num
3929 FROM jl_ar_ap_sup_awt_cr_lts
3930 WHERE po_vendor_id = P_Vendor_Id
3931 AND awt_type_code = P_AWT_Type_Code;
3932
3933 IF (l_seq_num IS NULL) THEN
3934 RETURN 0;
3935 END IF;
3936
3937 SELECT balance
3938 INTO l_credit_letter_amount
3939 FROM jl_ar_ap_sup_awt_cr_lts
3940 WHERE po_vendor_id = P_Vendor_Id
3941 AND awt_type_code = P_AWT_Type_Code
3942 AND seq_num = l_seq_num;
3943
3944 RETURN l_credit_letter_amount;
3945
3946 EXCEPTION
3947 WHEN no_data_found THEN
3948 RETURN 0;
3949
3950 WHEN others THEN
3951 IF (SQLCODE <> -20001) THEN
3952 Fnd_Message.Set_Name ('JL', 'JL_ZZ_AP_DEBUG');
3953 Fnd_Message.Set_Token('ERROR', SQLERRM);
3954 Fnd_Message.Set_Token('CALLING_SEQUENCE', l_calling_sequence);
3955 Fnd_Message.Set_Token('PARAMETERS',
3956 ' Vendor Id= ' || to_char(P_Vendor_Id) ||
3957 ', AWT Type Code= ' || P_AWT_Type_Code);
3958 Fnd_Message.Set_Token('DEBUG_INFO', l_debug_info);
3959 END IF;
3960
3961 App_Exception.Raise_Exception;
3962
3963 END Get_Credit_Letter_Amount;
3964
3965
3966
3967
3968 /**************************************************************************
3969 * *
3970 * Name : Update_Credit_Letter *
3971 * Purpose : Updates the withheld amount for each tax name contained *
3972 * into the PL/SQL table. The credit letters table is also *
3973 * updated *
3974 * *
3975 **************************************************************************/
3976 PROCEDURE Update_Credit_Letter
3977 (P_Vendor_Id IN Number,
3978 P_Rec_AWT_Type IN jl_zz_ap_awt_types%ROWTYPE,
3979 P_AWT_Date IN Date,
3980 P_Payment_Num IN Number,
3981 P_Check_Id IN Number,
3982 P_Selected_Check_Id IN Number,
3983 P_Calling_Sequence IN Varchar2,
3984 P_Tab_Withhold IN OUT NOCOPY Jl_Zz_Ap_Withholding_Pkg.Tab_Withholding,
3985 P_Last_Updated_By IN Number Default null,
3986 P_Last_Update_Login IN Number Default null,
3987 P_Program_Application_Id IN Number Default null,
3988 P_Program_Id IN Number Default null,
3989 P_Request_Id IN Number Default null)
3990 IS
3991
3992 l_credit_letter_amount Number;
3993 l_tax_id Number;
3994 l_initial_tax Number;
3995 l_withheld_amount Number := 0;
3996 l_orig_withheld_amount Number := 0;
3997 l_actual_withheld_amount Number := 0;
3998 l_debug_info Varchar2(300);
3999 l_calling_sequence Varchar2(2000);
4000
4001 BEGIN
4002 -------------------------------
4003 -- Initializes debug variables
4004 -------------------------------
4005 l_calling_sequence := 'JL_AR_AP_WITHHOLDING_PKG' || '.' ||
4006 'Update_Credit_Letter<--' || P_Calling_Sequence;
4007
4008 --------------------------------------------
4009 -- Checks whether there is at least one tax
4010 --------------------------------------------
4011 IF (P_Tab_Withhold.COUNT <= 0) THEN
4012 -- Nothing to do
4013 RETURN;
4014 END IF;
4015
4016 -----------------------------------------------------------
4017 -- Checks whether the current withholding tax type accepts
4018 -- credit letters
4019 -----------------------------------------------------------
4020 IF (nvl(P_Rec_AWT_Type.credit_letter_flag, 'N') <> 'Y') THEN
4021 -- Nothing to do
4022 RETURN;
4023 END IF;
4024
4025 -----------------------------------------------------------
4026 -- Checks whether the supplier has a credit letter for the
4027 -- current withholding tax type
4028 -----------------------------------------------------------
4029 l_credit_letter_amount := Get_Credit_Letter_Amount (P_Vendor_Id,
4030 P_Rec_AWT_Type.awt_type_code,
4031 l_calling_sequence);
4032
4033 IF (l_credit_letter_amount IS NULL OR
4034 l_credit_letter_amount <= 0) THEN
4035 -- Nothing to do
4036 RETURN;
4037 END IF;
4038
4039 -- Debug Information
4040 IF (DEBUG_Var = 'Y') THEN
4041 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Withholding Type Code = '||P_Rec_AWT_Type.awt_type_code);
4042 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Credit Letter Amount = '||to_char(l_credit_letter_amount));
4043 END IF;
4044 -- End Debug
4045
4046
4047 -----------------------------------
4048 -- Initializes auxiliary variables
4049 -----------------------------------
4050 l_tax_id := P_Tab_Withhold(1).tax_id;
4051 l_withheld_amount := P_Tab_Withhold(1).withheld_amount;
4052 l_orig_withheld_amount := l_withheld_amount;
4053 l_initial_tax := 1;
4054
4055 -------------------------
4056 -- Applies credit letter
4057 -------------------------
4058 FOR i IN 1 .. P_Tab_Withhold.COUNT LOOP
4059 EXIT WHEN l_credit_letter_amount <= 0;
4060
4061 IF (P_Tab_Withhold(i).tax_id <> l_tax_id) THEN
4062
4063 --------------------------------
4064 -- Updates credit letter amount
4065 --------------------------------
4066 IF (l_withheld_amount >= l_credit_letter_amount) THEN
4067 l_actual_withheld_amount := l_withheld_amount -
4068 l_credit_letter_amount;
4069 l_credit_letter_amount := 0;
4070 ELSE
4071 l_credit_letter_amount := l_credit_letter_amount -
4072 l_withheld_amount;
4073 l_actual_withheld_amount := 0;
4074 END IF;
4075
4076 ---------------------------
4077 -- Updates withheld amount
4078 ---------------------------
4079 FOR j IN l_initial_tax .. (i - 1) LOOP
4080 P_Tab_Withhold(j).withheld_amount := l_actual_withheld_amount;
4081 END LOOP;
4082
4083 -------------------------------
4084 -- Updates credit letter table
4085 -------------------------------
4086 Insert_Credit_Letter_Amount (P_Vendor_Id,
4087 P_Rec_AWT_Type.awt_type_code,
4088 l_tax_id,
4089 P_AWT_Date,
4090 l_orig_withheld_amount,
4091 l_actual_withheld_amount,
4092 l_credit_letter_amount,
4093 'AA',
4094 P_Payment_Num,
4095 P_Check_Id,
4096 P_Selected_Check_Id,
4097 l_calling_sequence,
4098 P_Last_Updated_By,
4099 P_Last_Update_Login,
4100 P_Program_Application_Id,
4101 P_Program_Id,
4102 P_Request_Id);
4103
4104 --------------------------------------
4105 -- Reinitializes auxiliary variables
4106 --------------------------------------
4107 l_withheld_amount := P_Tab_Withhold(i).withheld_amount;
4108 l_orig_withheld_amount := l_withheld_amount;
4109 l_initial_tax := i;
4110
4111 END IF;
4112 l_tax_id := P_Tab_Withhold(i).tax_id;
4113 END LOOP;
4114
4115
4116 IF (l_credit_letter_amount > 0) THEN
4117
4118 --------------------------------
4119 -- Updates credit letter amount
4120 --------------------------------
4121 IF (l_withheld_amount >= l_credit_letter_amount) THEN
4122 l_actual_withheld_amount := l_withheld_amount -
4123 l_credit_letter_amount;
4124 l_credit_letter_amount := 0;
4125 ELSE
4126 l_credit_letter_amount := l_credit_letter_amount -
4127 l_withheld_amount;
4128 l_actual_withheld_amount := 0;
4129 END IF;
4130
4131 ---------------------------
4132 -- Updates withheld amount
4133 ---------------------------
4134 FOR j IN l_initial_tax .. P_Tab_Withhold.COUNT LOOP
4135 P_Tab_Withhold(j).withheld_amount := l_actual_withheld_amount;
4136 END LOOP;
4137
4138 -------------------------------
4139 -- Updates credit letter table
4140 -------------------------------
4141 Insert_Credit_Letter_Amount (P_Vendor_Id,
4142 P_Rec_AWT_Type.awt_type_code,
4143 l_tax_id,
4144 P_AWT_Date,
4145 l_orig_withheld_amount,
4146 l_actual_withheld_amount,
4147 l_credit_letter_amount,
4148 'AA',
4149 P_Payment_Num,
4150 P_Check_Id,
4151 P_Selected_Check_Id,
4152 l_calling_sequence,
4153 P_Last_Updated_By,
4154 P_Last_Update_Login,
4155 P_Program_Application_Id,
4156 P_Program_Id,
4157 P_Request_Id);
4158 END IF;
4159
4160 EXCEPTION
4161 WHEN others THEN
4162 IF (SQLCODE <> -20001) THEN
4163 Fnd_Message.Set_Name ('JL', 'JL_ZZ_AP_DEBUG');
4164 Fnd_Message.Set_Token('ERROR', SQLERRM);
4165 Fnd_Message.Set_Token('CALLING_SEQUENCE', l_calling_sequence);
4166 Fnd_Message.Set_Token('PARAMETERS',
4167 ' Vendor_Id= ' || to_char(P_Vendor_Id) ||
4168 ', AWT_Date= ' || to_char(P_AWT_Date,'YYYY/MM/DD') ||
4169 ', Payment_Num= ' || to_char(P_Payment_Num) ||
4170 ', Check_Id= ' || to_char(P_Check_Id) ||
4171 ', Selected_Check_Id= ' || to_char(P_Selected_Check_Id));
4172 Fnd_Message.Set_Token('DEBUG_INFO', l_debug_info);
4173 END IF;
4174
4175 App_Exception.Raise_Exception;
4176
4177 END Update_Credit_Letter;
4178
4179
4180
4181
4182 /**************************************************************************
4183 * *
4184 * Name : Insert_Credit_Letter_Amount *
4185 * Purpose : Stores current information about credit letters into the *
4186 * JL_AR_AP_SUP_AWT_CR_LTS table *
4187 * *
4188 **************************************************************************/
4189 PROCEDURE Insert_Credit_Letter_Amount
4190 (P_Vendor_Id IN Number,
4191 P_AWT_Type_Code IN Varchar2,
4192 P_Tax_Id IN Number,
4193 P_AWT_Date IN Date,
4194 P_Withheld_Amount IN Number,
4195 P_Actual_Withheld_Amount IN Number,
4196 P_Balance IN Number,
4197 P_Status IN Varchar2,
4198 P_Payment_Num IN Number,
4199 P_Check_Id IN Number,
4200 P_Selected_Check_Id IN Number,
4201 P_Calling_Sequence IN Varchar2,
4202 P_Last_Updated_By IN Number Default null,
4203 P_Last_Update_Login IN Number Default null,
4204 P_Program_Application_Id IN Number Default null,
4205 P_Program_Id IN Number Default null,
4206 P_Request_Id IN Number Default null)
4207 IS
4208
4209 l_debug_info Varchar2(300);
4210 l_calling_sequence Varchar2(2000);
4211
4212 BEGIN
4213 -------------------------------
4214 -- Initializes debug variables
4215 -------------------------------
4216 l_calling_sequence := 'JL_AR_AP_WITHHOLDING_PKG' || '.' ||
4217 'Insert_Credit_Letter_Amount<--' ||
4218 P_Calling_Sequence;
4219
4220 -----------------------------------------------
4221 -- Inserts record into JL_AR_AP_SUP_AWT_CR_LTS
4222 -----------------------------------------------
4223 INSERT INTO jl_ar_ap_sup_awt_cr_lts
4224 (seq_num,
4225 po_vendor_id,
4226 awt_type_code,
4227 tax_id,
4228 trx_date,
4229 calc_wh_amnt,
4230 act_wheld_amnt,
4231 balance,
4232 check_id,
4233 selected_check_id,
4234 pay_number,
4235 created_by,
4236 creation_date,
4237 last_updated_by,
4238 last_update_date,
4239 last_update_login,
4240 program_application_id,
4241 program_id,
4242 request_id,
4243 status)
4244 VALUES
4245 (jl_ar_ap_sup_awt_cr_lts_s.nextval,
4246 P_Vendor_Id,
4247 P_AWT_Type_Code,
4248 P_Tax_Id,
4249 P_AWT_Date,
4250 P_Withheld_Amount,
4251 P_Actual_Withheld_Amount,
4252 P_Balance,
4253 P_Check_Id,
4254 P_Selected_Check_Id,
4255 P_Payment_Num,
4256 fnd_global.user_id,
4257 sysdate,
4258 P_Last_Updated_By,
4259 sysdate,
4260 P_Last_Update_Login,
4261 P_Program_Application_Id,
4262 P_Program_Id,
4263 P_Request_Id,
4264 P_Status);
4265
4266 EXCEPTION
4267 WHEN others THEN
4268 IF (SQLCODE <> -20001) THEN
4269 Fnd_Message.Set_Name ('JL', 'JL_ZZ_AP_DEBUG');
4270 Fnd_Message.Set_Token('ERROR', SQLERRM);
4271 Fnd_Message.Set_Token('CALLING_SEQUENCE', l_calling_sequence);
4272 Fnd_Message.Set_Token('PARAMETERS',
4273 ' Vendor Id= ' || to_char(P_Vendor_Id) ||
4274 ', AWT Type Code= ' || P_AWT_Type_Code ||
4275 ', Tax Id= ' || to_char(P_Tax_Id) ||
4276 ', AWT Date= ' || to_char(P_AWT_Date,'YYYY/MM/DD') ||
4277 ', Withheld Amount= ' || to_char(P_Withheld_Amount) ||
4278 ', Actual Withheld Amount= ' || to_char(P_Actual_Withheld_Amount) ||
4279 ', Balance= ' || to_char(P_Balance) ||
4280 ', Status= ' || P_Status ||
4281 ', Payment Num= ' || to_char(P_Payment_Num) ||
4282 ', Check Id= ' || to_char(P_Check_Id) ||
4283 ', Selected Check Id= ' || to_char(P_Selected_Check_Id));
4284 Fnd_Message.Set_Token('DEBUG_INFO', l_debug_info);
4285 END IF;
4286
4287 App_Exception.Raise_Exception;
4288
4289 END Insert_Credit_Letter_Amount;
4290
4291
4292
4293 /**************************************************************************
4294 * *
4295 * Name : Undo_Credit_Letter *
4296 * Purpose : Reverse all the credit letter amounts for a particular *
4297 * payment. One record will be created for each different *
4298 * supplier and witholding tax type. *
4299 * *
4300 **************************************************************************/
4301 PROCEDURE Undo_Credit_Letter
4302 (P_Check_Id IN Number,
4303 P_Selected_Check_Id IN Number,
4304 P_AWT_Date IN Date,
4305 P_Payment_Num IN Number,
4306 P_Calling_Sequence IN Varchar2,
4307 P_Last_Updated_By IN Number Default null,
4308 P_Last_Update_Login IN Number Default null,
4309 P_Program_Application_Id IN Number Default null,
4310 P_Program_Id IN Number Default null,
4311 P_Request_Id IN Number Default null)
4312
4313 IS
4314 ---------------------
4315 -- Types definition
4316 ---------------------
4317 TYPE Rec_Credit_Letter IS RECORD
4318 (
4319 vendor_id Number,
4320 awt_type_code Varchar2(30),
4321 amount_to_reverse Number
4322 );
4323
4324 TYPE Tab_Credit_Letter IS TABLE OF Rec_Credit_Letter
4325 INDEX BY BINARY_INTEGER;
4326
4327 ---------------------
4328 -- Cursor definition
4329 ---------------------
4330 CURSOR c_credit_letters (P_Check_Id IN Number,
4331 P_Selected_Check_Id IN Number) IS
4332 SELECT jlcl.po_vendor_id vendor_id,
4333 jlcl.awt_type_code awt_type_code,
4334 jlcl.calc_wh_amnt calc_wh_amnt,
4335 jlcl.act_wheld_amnt act_wheld_amnt
4336 FROM jl_ar_ap_sup_awt_cr_lts jlcl
4337 WHERE jlcl.status = 'AA'
4338 AND ((P_Check_Id IS NOT NULL AND
4339 jlcl.check_id = P_Check_Id) OR
4340 (P_Selected_Check_Id IS NOT NULL AND
4341 jlcl.selected_check_id = P_Selected_Check_Id))
4342 ORDER BY jlcl.po_vendor_id,
4343 jlcl.awt_type_code,
4344 jlcl.seq_num
4345 FOR UPDATE OF jlcl.status;
4346
4347 ------------------------
4348 -- Variables definition
4349 ------------------------
4350 rec_cr_letter Rec_Credit_Letter;
4351 tab_cr_letter Tab_Credit_Letter;
4352 rec_credit_letters c_credit_letters%ROWTYPE;
4353 l_position Number := 0;
4354 l_balance Number;
4355 l_debug_info Varchar2(300);
4356 l_calling_sequence Varchar2(2000);
4357
4358 BEGIN
4359 -------------------------------
4360 -- Initializes debug variables
4361 -------------------------------
4362 l_calling_sequence := 'JL_AR_AP_WITHHOLDING_PKG' || '.' ||
4363 'Undo_Credit_Letter<--' || P_Calling_Sequence;
4364
4365 ----------------------
4366 -- Initializes record
4367 ----------------------
4368 rec_cr_letter.vendor_id := null;
4369 rec_cr_letter.awt_type_code := null;
4370 rec_cr_letter.amount_to_reverse := 0;
4371
4372 ------------------------------------------
4373 -- Retrieves all the lines to be reversed
4374 ------------------------------------------
4375 OPEN c_credit_letters (P_Check_Id, P_Selected_Check_Id);
4376 LOOP
4377 FETCH c_credit_letters INTO rec_credit_letters;
4378 EXIT WHEN c_credit_letters%NOTFOUND;
4379
4380 IF ((rec_cr_letter.vendor_id IS NULL AND
4381 rec_cr_letter.awt_type_code IS NULL) OR
4382 (rec_cr_letter.vendor_id <> rec_credit_letters.vendor_id OR
4383 rec_cr_letter.awt_type_code <> rec_credit_letters.awt_type_code))
4384 THEN
4385 l_position := l_position + 1;
4386 rec_cr_letter.vendor_id := rec_credit_letters.vendor_id;
4387 rec_cr_letter.awt_type_code := rec_credit_letters.awt_type_code;
4388 rec_cr_letter.amount_to_reverse := 0;
4389 tab_cr_letter(l_position) := rec_cr_letter;
4390 END IF;
4391
4392 ----------------------------------------
4393 -- Calculates the amount to be reversed
4394 ----------------------------------------
4395 tab_cr_letter(l_position).amount_to_reverse :=
4396 tab_cr_letter(l_position).amount_to_reverse +
4397 nvl(rec_credit_letters.calc_wh_amnt, 0) -
4398 nvl(rec_credit_letters.act_wheld_amnt, 0);
4399
4400 ----------------------------------------------
4401 -- Changes the status of the reversed records
4402 ----------------------------------------------
4403 UPDATE jl_ar_ap_sup_awt_cr_lts
4404 SET status = 'AR'
4405 WHERE CURRENT OF c_credit_letters;
4406
4407 END LOOP;
4408 CLOSE c_credit_letters;
4409
4410
4411 ---------------------------------------------------------
4412 -- Inserts the records with the reversion information
4413 -- (one record for each different vendor and withholding
4414 -- tax type)
4415 ---------------------------------------------------------
4416 FOR i IN 1 .. tab_cr_letter.COUNT LOOP
4417
4418 --------------------------------------------------------
4419 -- Obtains current balance for the withholding tax type
4420 --------------------------------------------------------
4421 l_balance := Get_Credit_Letter_Amount(tab_cr_letter(i).vendor_id,
4422 tab_cr_letter(i).awt_type_code,
4423 l_calling_sequence);
4424
4425 -------------------------------------------------------
4426 -- Calculates new balance for the withholding tax type
4427 -------------------------------------------------------
4428 l_balance := nvl(l_balance, 0) +
4429 tab_cr_letter(i).amount_to_reverse;
4430
4431 -----------------------------------
4432 -- Inserts record with new balance
4433 -----------------------------------
4434 Insert_Credit_Letter_Amount(tab_cr_letter(i).vendor_id,
4435 tab_cr_letter(i).awt_type_code,
4436 null, -- Tax ID
4437 P_AWT_Date,
4438 null, -- Calc. Withheld Amount
4439 null, -- Actual Withheld Amount
4440 l_balance,
4441 'AR', -- Status
4442 P_Payment_Num,
4443 P_Check_Id,
4444 P_Selected_Check_Id,
4445 l_calling_sequence,
4446 P_Last_Updated_By,
4447 P_Last_Update_Login,
4448 P_Program_Application_Id,
4449 P_Program_Id,
4450 P_Request_Id);
4451 END LOOP;
4452
4453
4454 EXCEPTION
4455 WHEN others THEN
4456 IF (SQLCODE <> -20001) THEN
4457 Fnd_Message.Set_Name ('JL', 'JL_ZZ_AP_DEBUG');
4458 Fnd_Message.Set_Token('ERROR', SQLERRM);
4459 Fnd_Message.Set_Token('CALLING_SEQUENCE', l_calling_sequence);
4460 Fnd_Message.Set_Token('PARAMETERS',
4461 ' Check Id= ' || to_char(P_Check_Id) ||
4462 ', Selected Check Id= ' || to_char(P_Selected_Check_Id) ||
4463 ', AWT Date= ' || to_char(P_AWT_Date,'YYYY/MM/DD') ||
4464 ', Payment Num= ' || to_char(P_Payment_Num));
4465 Fnd_Message.Set_Token('DEBUG_INFO', l_debug_info);
4466 END IF;
4467
4468 App_Exception.Raise_Exception;
4469
4470 END Undo_Credit_Letter;
4471
4472
4473
4474 /**************************************************************************
4475 * *
4476 * Name : Update_Quick_Payment *
4477 * Purpose : Updates the payment amount by subtracting the withheld *
4478 * amount. *
4479 * *
4480 **************************************************************************/
4481 PROCEDURE Update_Quick_Payment
4482 (P_Check_Id IN Number,
4483 P_Calling_Sequence IN Varchar2)
4484 IS
4485
4486 ------------------------------
4487 -- Local variables definition
4488 ------------------------------
4489 l_invoice_payment_id Number;
4490 l_invoice_id Number;
4491 l_pay_exchange_rate Number;
4492 l_inv_exchange_rate Number;
4493 l_payment_cross_rate Number;
4494 l_payment_num Number;
4495 l_withhold_amount Number;
4496 l_amount Number;
4497 l_base_amount Number;
4498 l_total_wh_amount Number := 0;
4499 l_total_wh_base_amount Number := 0;
4500 l_debug_info Varchar2(300);
4501 l_calling_sequence Varchar2(2000);
4502 l_pay_amount Number;
4503 l_payment_base_amount Number;
4504 l_invoice_base_amount Number;
4505 -- Bug 2886571
4506 l_payment_currency_code Varchar2(15);
4507 -------------------------------------
4508 -- Cursor to select all the invoices
4509 -- within the payment
4510 -------------------------------------
4511 CURSOR c_invoice_payment (P_Check_Id Number)
4512 IS
4513 SELECT apip.invoice_payment_id invoice_payment_id,
4514 apip.invoice_id invoice_id,
4515 apip.exchange_rate pay_exchange_rate,
4516 apip.payment_num payment_num,
4517 apip.amount amount,
4518 apip.payment_base_amount payment_base_amount,
4519 apip.invoice_base_amount invoice_base_amount
4520 FROM ap_invoice_payments apip
4521 WHERE apip.check_id = P_Check_Id
4522 FOR UPDATE OF apip.amount,
4523 apip.payment_base_amount,
4524 apip.invoice_base_amount;
4525
4526 --------------------------------
4527 -- Cursor to select the payment
4528 --------------------------------
4529 CURSOR c_checks (P_Check_Id Number)
4530 IS
4531 SELECT apch.amount amount,
4532 apch.base_amount base_amount,
4533 apch.currency_code currency_code -- Bug 2886571
4534 FROM ap_checks apch
4535 WHERE apch.check_id = P_Check_Id
4536 FOR UPDATE OF apch.amount,
4537 apch.base_amount;
4538
4539 BEGIN
4540 -------------------------------
4541 -- Initializes debug variables
4542 -------------------------------
4543 l_calling_sequence := 'JL_AR_AP_WITHHOLDING_PKG' || '.' ||
4544 'Update_Quick_Payment<--' || P_Calling_Sequence;
4545
4546
4547 -- Debug Information
4548 IF (DEBUG_Var = 'Y') THEN
4549 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' ');
4550 JL_ZZ_AP_EXT_AWT_UTIL.Debug ('PROCEDURE Update_Quick_Payment');
4551 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Parameter P_Check_Id: '||to_char(P_Check_Id));
4552 END IF;
4553 -- End Debug
4554
4555
4556 --------------------------------------------
4557 -- Updates amounts for the invoice payments
4558 --------------------------------------------
4559
4560 -- Debug Information
4561 IF (DEBUG_Var = 'Y') THEN
4562 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' OPEN c_invoice_payment');
4563 END IF;
4564 -- End Debug
4565
4566 OPEN c_invoice_payment(P_Check_Id);
4567 LOOP
4568 FETCH c_invoice_payment INTO l_invoice_payment_id,
4569 l_invoice_id,
4570 l_pay_exchange_rate,
4571 l_payment_num,
4572 l_pay_amount,
4573 l_payment_base_amount,
4574 l_invoice_base_amount;
4575 EXIT WHEN c_invoice_payment%NOTFOUND;
4576
4577 -- Debug Information
4578 IF (DEBUG_Var = 'Y') THEN
4579 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Fetched Values: l_invoice_payment_id= '||to_char(l_invoice_payment_id));
4580 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Fetched Values: l_invoice_id= '||to_char(l_invoice_id));
4581 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Fetched Values: l_pay_exchange_rate= '||to_char(l_pay_exchange_rate));
4582 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Fetched Values: l_payment_num= '||to_char(l_payment_num));
4583 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Fetched Values: l_pay_amount= '||to_char(l_pay_amount));
4584 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Fetched Values: l_payment_base_amount= '||to_char(l_payment_base_amount));
4585 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Fetched Values: l_invoice_base_amount= '||to_char(l_invoice_base_amount));
4586 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' ');
4587 END IF;
4588 -- End Debug
4589
4590 -------------------------------------------
4591 -- Obtains withheld amount for the invoice
4592 -------------------------------------------
4593 SELECT nvl(sum(apid.amount), 0)
4594 INTO l_withhold_amount
4595 FROM ap_invoice_distributions apid
4596 WHERE apid.invoice_id = l_invoice_id
4597 AND apid.awt_invoice_payment_id = l_invoice_payment_id
4598 -- added recently
4599 AND NVL(apid.REVERSAL_FLAG,'N') <> 'Y';
4600
4601 -- Debug Information
4602 IF (DEBUG_Var = 'Y') THEN
4603 JL_ZZ_AP_EXT_AWT_UTIL.Debug ('Obtains withheld amount for the invoice : '||to_char(l_withhold_amount));
4604 END IF;
4605 -- End Debug
4606
4607 IF (l_withhold_amount <> 0) THEN
4608
4609 --------------------------------
4610 -- Obtains currency information
4611 --------------------------------
4612 SELECT apin.exchange_rate,
4613 apps.payment_cross_rate,
4614 apin.payment_currency_code
4615 INTO l_inv_exchange_rate,
4616 l_payment_cross_rate,
4617 l_payment_currency_code -- Bug 2886571
4618 FROM ap_invoices apin,
4619 ap_payment_schedules apps
4620 WHERE apin.invoice_id = l_invoice_id
4621 AND apps.invoice_id = l_invoice_id
4622 AND apps.payment_num = l_payment_num;
4623
4624 -- Debug Information
4625 IF (DEBUG_Var = 'Y') THEN
4626 JL_ZZ_AP_EXT_AWT_UTIL.Debug ('Obtains currency information: exch rate, pay cross rate, pay curr code'||
4627 to_char(l_inv_exchange_rate)||', '||to_char(l_payment_cross_rate)||
4628 ','||l_payment_currency_code);
4629 END IF;
4630 -- End Debug
4631
4632 -- Bug 2886571 Rounding the amounts.
4633 --------------------------------------------------------
4634 -- Updates the amount remaining of the payment schedule
4635 --------------------------------------------------------
4636 UPDATE ap_payment_schedules
4637 SET amount_remaining = ap_utilities_pkg.ap_round_currency(
4638 amount_remaining - (l_withhold_amount * nvl(l_payment_cross_rate, 1)),
4639 l_payment_currency_code),
4640 payment_status_flag = decode( ap_utilities_pkg.ap_round_currency(amount_remaining -
4641 (l_withhold_amount *
4642 nvl(l_payment_cross_rate, 1)),l_payment_currency_code),
4643 0, 'Y',
4644 amount_remaining,
4645 payment_status_flag, 'P')
4646 WHERE invoice_id = l_invoice_id
4647 AND payment_num = l_payment_num;
4648
4649 ------------------------------------------
4650 -- Updates the amount paid of the invoice
4651 -- amount_paid does not affect MRC
4652 ------------------------------------------
4653 UPDATE ap_invoices
4654 SET amount_paid = ap_utilities_pkg.ap_round_currency(
4655 nvl(amount_paid, 0) +
4656 (l_withhold_amount *
4657 nvl(l_payment_cross_rate, 1)),l_payment_currency_code),
4658 payment_status_flag = AP_INVOICES_UTILITY_PKG.get_payment_status(l_invoice_id)
4659 WHERE invoice_id = l_invoice_id;
4660
4661 --------------------------------------------------------------
4662 -- Updates the payment amount
4663 -- Calling the AP Table Handler to update ap_invoice_payments.
4664 -- Bug 1827398
4665 --------------------------------------------------------------
4666 l_pay_amount := ap_utilities_pkg.ap_round_currency(
4667 l_pay_amount + (l_withhold_amount *
4668 nvl(l_payment_cross_rate, 1)), l_payment_currency_code);
4669
4670 l_invoice_base_amount := ap_utilities_pkg.ap_round_currency(
4671 l_invoice_base_amount +
4672 (l_withhold_amount * nvl(l_inv_exchange_rate, 1)), l_payment_currency_code);
4673
4674 l_payment_base_amount := ap_utilities_pkg.ap_round_currency(
4675 l_payment_base_amount +
4676 (l_withhold_amount * nvl(l_payment_cross_rate, 1) *
4677 nvl(l_pay_exchange_rate, 1)),l_payment_currency_code);
4678
4679 AP_AIP_TABLE_HANDLER_PKG.Update_Amounts(
4680 l_invoice_payment_id
4681 ,l_pay_amount
4682 ,l_invoice_base_amount
4683 ,l_payment_base_amount
4684 ,l_calling_sequence);
4685
4686 --------------------------------------------
4687 -- Calculates total amounts for the payment
4688 --------------------------------------------
4689 l_total_wh_amount := l_total_wh_amount +
4690 (l_withhold_amount *
4691 nvl(l_payment_cross_rate, 1));
4692
4693 l_total_wh_base_amount := l_total_wh_base_amount +
4694 (l_withhold_amount *
4695 nvl(l_payment_cross_rate, 1) *
4696 nvl(l_pay_exchange_rate, 1));
4697 END IF;
4698
4699 END LOOP;
4700
4701 CLOSE c_invoice_payment;
4702
4703 -------------------------------------------------------------------
4704 -- Updates the payment amount for the check
4705 -- Calling the AP Table Handler to update ap_checks.
4706 -- Bug 1827398
4707 -------------------------------------------------------------------
4708 IF (l_total_wh_amount <> 0 OR l_total_wh_base_amount <> 0) THEN
4709 OPEN c_checks (P_Check_Id);
4710 FETCH c_checks INTO l_amount, l_base_amount, l_payment_currency_code;
4711 IF (NOT c_checks%NOTFOUND) THEN
4712
4713 l_amount := ap_utilities_pkg.ap_round_currency((l_amount + l_total_wh_amount),l_payment_currency_code);
4714 l_base_amount := ap_utilities_pkg.ap_round_currency((l_base_amount + l_total_wh_base_amount),
4715 l_payment_currency_code);
4716
4717 AP_AC_TABLE_HANDLER_PKG.Update_Amounts(
4718 P_check_id
4719 ,l_amount
4720 ,l_base_amount
4721 ,l_calling_sequence);
4722 END IF;
4723 CLOSE c_checks;
4724 END IF;
4725
4726 -- Debug Information
4727 IF (DEBUG_Var = 'Y') THEN
4728 JL_ZZ_AP_EXT_AWT_UTIL.Debug ('End Procedure Update_Quick_Payment');
4729 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' ');
4730 END IF;
4731 -- End Debug
4732
4733 EXCEPTION
4734 WHEN others THEN
4735 IF (SQLCODE <> -20001) THEN
4736 Fnd_Message.Set_Name ('JL', 'JL_ZZ_AP_DEBUG');
4737 Fnd_Message.Set_Token('ERROR', SQLERRM);
4738 Fnd_Message.Set_Token('CALLING_SEQUENCE', l_calling_sequence);
4739 Fnd_Message.Set_Token('PARAMETERS',
4740 ' Check Id= ' || to_char(P_Check_Id));
4741 Fnd_Message.Set_Token('DEBUG_INFO', l_debug_info);
4742 END IF;
4743
4744 App_Exception.Raise_Exception;
4745
4746 END Update_Quick_Payment;
4747
4748
4749 /**************************************************************************
4750 * *
4751 * Name : Update_Payment_Batch *
4752 * Purpose : Updates the amounts of the payment batch by subtracting *
4753 * the withholding amount. *
4754 * just update invoices in same payment check *
4755 **************************************************************************/
4756 PROCEDURE Update_Payment_Batch
4757 (P_Checkrun_Name IN Varchar2,
4758 P_Checkrun_ID IN Number,
4759 P_Selected_Check_Id IN Number,
4760 P_Calling_Sequence IN Varchar2)
4761 IS
4762 ----------------------
4763 -- Cursor definition
4764 ----------------------
4765 CURSOR c_selected_invoices (P_Selected_Check_Id IN Number) IS
4766
4767 /* RG update documents
4768 SELECT apsi.invoice_id invoice_id,
4769 apsi.payment_num payment_num,
4770 apsi.payment_amount payment_amount,
4771 nvl(apsi.invoice_exchange_rate, 1) invoice_exchange_rate,
4772 nvl(apsi.payment_cross_rate, 1) payment_cross_rate
4773 FROM ap_selected_invoices apsi
4774 WHERE apsi.pay_selected_check_id = P_Selected_Check_id
4775 AND nvl(apsi.ok_to_pay_flag, 'Y') = 'Y'
4776 AND apsi.original_invoice_id IS NULL
4777 FOR UPDATE;
4778 */
4779 SELECT docs.CALLING_APP_DOC_UNIQUE_REF2 invoice_id,
4780 docs.document_payable_id document_payable_id,
4781 docs.CALLING_APP_DOC_UNIQUE_REF3 payment_num,
4782 docs.document_amount payment_amount ,
4783 nvl(apsi.invoice_exchange_rate, 1) invoice_exchange_rate,
4784 nvl(apsi.payment_cross_rate, 1) payment_cross_rate
4785 FROM iby_hook_docs_in_pmt_t docs,
4786 ap_selected_invoices apsi
4787 WHERE docs.payment_id = P_Selected_Check_id
4788 AND docs.calling_app_id = 200
4789 AND apsi.invoice_id = docs.calling_app_doc_unique_ref2
4790 AND nvl(docs.dont_pay_flag,'N')='N';
4791
4792
4793 /* RG
4794 CURSOR c_selected_invoice_checks (P_Selected_Check_Id IN Number) IS
4795 SELECT apsic.check_amount check_amount,
4796 apsic.vendor_amount vendor_amount
4797 FROM ap_selected_invoice_checks apsic
4798 WHERE apsic.selected_check_id = P_Selected_Check_Id
4799 FOR UPDATE OF apsic.check_amount,
4800 apsic.vendor_amount;
4801 */
4802 -- Update Payments
4803 CURSOR c_selected_invoice_checks (P_Selected_Check_Id IN Number) IS
4804 SELECT ipmt.payment_amount payment_amount
4805 FROM iby_hook_payments_t ipmt
4806 WHERE ipmt.payment_id = P_Selected_Check_id
4807 AND ipmt.calling_app_id = 200
4808 FOR UPDATE OF ipmt.payment_amount;
4809
4810
4811 ------------------------
4812 -- Variables definition
4813 ------------------------
4814 rec_sel_inv c_selected_invoices%ROWTYPE;
4815 l_withholding_amount Number;
4816 l_check_amount Number;
4817 l_vendor_amount Number;
4818 l_total_wh_amount Number := 0;
4819 l_debug_info Varchar2(300);
4820 l_calling_sequence Varchar2(2000);
4821 l_count_inv Number;
4822
4823 -- Bug 2176607
4824 l_payment_currency_code Varchar2(15);
4825
4826 l_prop_payment_amount Number := 0;
4827
4828 BEGIN
4829 -------------------------------
4830 -- Initializes debug variables
4831 -------------------------------
4832 l_calling_sequence := 'JL_AR_AP_WITHHOLDING_PKG' || '.' ||
4833 'Update_Payment_Batch<--' || P_Calling_Sequence;
4834
4835 -- Debug Information
4836 IF (DEBUG_Var = 'Y') THEN
4837 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' ');
4838 JL_ZZ_AP_EXT_AWT_UTIL.Debug ('PROCEDURE Update_Payment_Batch');
4839 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Parameter P_Checkrun_Name: '||P_Checkrun_Name);
4840 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Parameter P_Selected_Check_Id: '||to_char(P_Selected_Check_Id));
4841 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' ');
4842 END IF;
4843 -- End Debug
4844
4845
4846 -----------------------------------------------------------------------------
4847 -- Bug Number: 1480825 -- Just update the invoices in the same payment check.
4848 -----------------------------------------------------------------------------
4849 SELECT count(*)
4850 INTO l_count_inv
4851 FROM iby_hook_docs_in_pmt_t docs,
4852 -- RG ap_selected_invoices apsi,
4853 ap_awt_temp_distributions awtd
4854 WHERE docs.payment_id = P_Selected_Check_Id
4855 AND nvl(docs.dont_pay_flag,'N') ='N'
4856 AND docs.calling_app_doc_unique_ref2 = awtd.invoice_id
4857 AND docs.calling_app_id=200 ;
4858
4859 -- apsi.pay_selected_check_id = P_Selected_Check_Id
4860 -- AND nvl(apsi.ok_to_pay_flag, 'Y') = 'Y'
4861 -- AND apsi.original_invoice_id IS NULL
4862
4863
4864 -- Debug Information
4865 IF (DEBUG_Var = 'Y') THEN
4866 JL_ZZ_AP_EXT_AWT_UTIL.Debug ('Just update the invoices in the same payment check: '||to_char(l_count_inv));
4867 END IF;
4868 -- End Debug
4869
4870 IF (l_count_inv = 0 ) Then
4871 return;
4872 END IF;
4873
4874 -- Bug2175168. Store the the Proposed Payment Amount so that this can be utilized to correct
4875 -- the check amount. Since the standard AP build only updates the AP_SELECTED_INVOICES while
4876 -- updating the check amount we need to consider this rather than the existing check amount.
4877 -- This would however be redundant when we Rebuild the batch.
4878
4879 SELECT SUM(docs.document_amount)
4880 INTO l_prop_payment_amount
4881 FROM iby_hook_docs_in_pmt_t docs
4882 -- ap_selected_invoices apsi
4883 WHERE docs.payment_id = P_Selected_Check_Id
4884 AND nvl(docs.dont_pay_flag,'N') ='N'
4885 AND docs.calling_app_id =200;
4886
4887 -- apsi.pay_selected_check_id = P_Selected_Check_id
4888 -- AND nvl(apsi.ok_to_pay_flag, 'Y') = 'Y'
4889 -- AND apsi.original_invoice_id IS NULL;
4890
4891 -- Debug Information
4892 IF (DEBUG_Var = 'Y') THEN
4893 JL_ZZ_AP_EXT_AWT_UTIL.Debug ('Proposed Payment Amount : '||to_char(l_prop_payment_amount));
4894 END IF;
4895 -- End Debug
4896
4897 --------------------------------------
4898 -- Updates payment amount information
4899 --------------------------------------
4900
4901 -- Debug Information
4902 IF (DEBUG_Var = 'Y') THEN
4903 JL_ZZ_AP_EXT_AWT_UTIL.Debug ('OPEN Cursor c_selected_invoices');
4904 END IF;
4905 -- End Debug
4906
4907 OPEN c_selected_invoices (P_Selected_Check_Id);
4908
4909 LOOP
4910 FETCH c_selected_invoices INTO rec_sel_inv;
4911 EXIT WHEN c_selected_invoices%NOTFOUND;
4912
4913 -- Debug Information
4914 IF (DEBUG_Var = 'Y') THEN
4915 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Fetched Values: Invoice_ID= '||to_char(rec_sel_inv.invoice_id));
4916 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Fetched Values: payment_num= '||to_char(rec_sel_inv.payment_num));
4917 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Fetched Values: payment_amount= '||to_char(rec_sel_inv.payment_amount));
4918 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Fetched Values: inv exch rate= '||to_char(rec_sel_inv.invoice_exchange_rate));
4919 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Fetched Values: pay cross rate= '||to_char(rec_sel_inv.payment_cross_rate));
4920 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' ');
4921 END IF;
4922 -- End Debug
4923
4924 ------------------------------------
4925 -- Bug 2176607
4926 -- Added the following SELECT to get
4927 -- the currency code for rounding.
4928 ------------------------------------
4929 /* RG
4930 SELECT payment_currency_code
4931 INTO l_payment_currency_code
4932 FROM ap_invoices_all
4933 WHERE invoice_id = rec_sel_inv.invoice_id;
4934 */
4935
4936 SELECT document_currency_code
4937 INTO l_payment_currency_code
4938 FROM IBY_HOOK_DOCS_IN_PMT_T
4939 WHERE payment_id = P_Selected_Check_Id
4940 AND document_payable_id = rec_sel_inv.document_payable_id;
4941 -----------------------------------------------------
4942 -- Calculates the withholding amount for the invoice
4943 -----------------------------------------------------
4944
4945 SELECT nvl(sum(withholding_amount), 0)
4946 INTO l_withholding_amount
4947 FROM ap_awt_temp_distributions
4948 WHERE checkrun_name = P_Checkrun_Name
4949 AND checkrun_id= p_checkrun_id
4950 AND invoice_id = rec_sel_inv.invoice_id
4951 AND payment_num = rec_sel_inv.payment_num;
4952
4953 ------------------------------------
4954 -- Converts to the payment currency
4955 ------------------------------------
4956 l_withholding_amount := l_withholding_amount /
4957 rec_sel_inv.invoice_exchange_rate *
4958 rec_sel_inv.payment_cross_rate;
4959
4960 l_withholding_amount := ap_utilities_pkg.ap_round_currency(l_withholding_amount,
4961 l_payment_currency_code);
4962
4963
4964
4965 -- Debug Information
4966 IF (DEBUG_Var = 'Y') THEN
4967 JL_ZZ_AP_EXT_AWT_UTIL.Debug ('Withheld amount for the invoice= '||to_char(l_withholding_amount));
4968 END IF;
4969 -- End Debug
4970
4971 -------------------------------------------------------
4972 -- Updates proposed payment
4973 -- Bug 2176607 Rounding the amounts; the withheld amount
4974 -- will be rounded by AP in create invoice distributions.
4975 -- using the local variable instead.
4976 -------------------------------------------------------
4977
4978 -- RG Update Documents in PMT table
4979 -- As discussed with Ryan , we will keep updating ap selected invoices
4980
4981 UPDATE ap_selected_invoices apsi
4982 SET apsi.proposed_payment_amount = ap_utilities_pkg.ap_round_currency(
4983 apsi.proposed_payment_amount - l_withholding_amount,
4984 l_payment_currency_code),
4985 apsi.payment_amount = ap_utilities_pkg.ap_round_currency(
4986 apsi.payment_amount - l_withholding_amount,
4987 l_payment_currency_code),
4988 apsi.amount_remaining = ap_utilities_pkg.ap_round_currency(
4989 apsi.amount_remaining - l_withholding_amount,
4990 l_payment_currency_code),
4991 apsi.withholding_amount = ap_utilities_pkg.ap_round_currency(
4992 l_withholding_amount, l_payment_currency_code)
4993 WHERE invoice_id = rec_sel_inv.invoice_id ;
4994 -- WHERE CURRENT OF c_selected_invoices;
4995
4996
4997 UPDATE iby_hook_docs_in_pmt_t docs
4998 SET docs.document_amount = ap_utilities_pkg.ap_round_currency(
4999 docs.document_amount - l_withholding_amount,
5000 l_payment_currency_code),
5001 docs.amount_withheld = ap_utilities_pkg.ap_round_currency(
5002 l_withholding_amount, l_payment_currency_code)
5003 WHERE document_payable_id = rec_sel_inv.document_payable_id;
5004
5005 l_total_wh_amount := l_total_wh_amount +
5006 l_withholding_amount;
5007
5008 END LOOP;
5009
5010 CLOSE c_selected_invoices;
5011
5012 -- Bug2175168. Using the Proposed payment amount instead of check amount. Since, the vendor_amount
5013 -- will always be -1 * total withholding amount ofor the selected check, used this to update the
5014 -- Vendor_Amount.
5015
5016 --------------------------------------------
5017 -- Update the amount for the selected check
5018 -- Bug 2176607 Rounding the amounts;
5019 --------------------------------------------
5020 -- RG Not Applicable the vendor amount
5021 -- Update Payments Hook table
5022
5023 OPEN c_selected_invoice_checks (P_Selected_Check_Id);
5024 FETCH c_selected_invoice_checks INTO l_check_amount;
5025
5026 IF (NOT c_selected_invoice_checks%NOTFOUND) THEN
5027
5028 /* RG
5029 UPDATE ap_selected_invoice_checks apsic
5030 SET apsic.check_amount = ap_utilities_pkg.ap_round_currency(
5031 NVL(l_prop_payment_amount, l_check_amount ) -
5032 l_total_wh_amount, apsic.currency_code),
5033 apsic.vendor_amount = ap_utilities_pkg.ap_round_currency(
5034 -1 * l_total_wh_amount, apsic.currency_code)
5035 WHERE CURRENT OF c_selected_invoice_checks;
5036 */
5037 UPDATE iby_hook_payments_t ipmt
5038 SET ipmt.payment_amount = ap_utilities_pkg.ap_round_currency(
5039 NVL(l_prop_payment_amount, l_check_amount ) -
5040 l_total_wh_amount, ipmt.payment_currency_code)
5041 WHERE CURRENT OF c_selected_invoice_checks;
5042
5043 END IF;
5044
5045 CLOSE c_selected_invoice_checks;
5046
5047 EXCEPTION
5048 WHEN others THEN
5049 IF (SQLCODE <> -20001) THEN
5050 Fnd_Message.Set_Name ('JL', 'JL_ZZ_AP_DEBUG');
5051 Fnd_Message.Set_Token('ERROR', SQLERRM);
5052 Fnd_Message.Set_Token('CALLING_SEQUENCE', l_calling_sequence);
5053 Fnd_Message.Set_Token('PARAMETERS',
5054 ' Checkrun Name= ' || P_Checkrun_Name ||
5055 ', Selected Check Id= ' || to_char(P_Selected_Check_Id));
5056 Fnd_Message.Set_Token('DEBUG_INFO', l_debug_info);
5057 END IF;
5058
5059 App_Exception.Raise_Exception;
5060
5061 END Update_Payment_Batch;
5062
5063
5064
5065
5066 /**************************************************************************
5067 * *
5068 * Name : Withholding_Already_Calculated *
5069 * Purpose : Checks whether the withholding was already calculated for *
5070 * a particular invoice. This is only applicable for those *
5071 * 'Invoice Based' withholding taxes. *
5072 * *
5073 **************************************************************************/
5074 FUNCTION Withholding_Already_Calculated
5075 (P_Invoice_Id IN Number,
5076 P_Tax_Name IN Varchar2,
5077 P_Tax_Id IN Number,
5078 P_Taxable_Base_Amount_Basis IN Varchar2,
5079 P_Tab_Withhold IN Jl_Zz_Ap_Withholding_Pkg.Tab_Withholding,
5080 P_Inv_Payment_Num IN Number,
5081 P_Calling_Sequence IN Varchar2)
5082 RETURN Boolean
5083 IS
5084
5085 l_count Number;
5086 l_withheld_amount Number;
5087 l_debug_info Varchar2(300);
5088 l_calling_sequence Varchar2(2000);
5089
5090 BEGIN
5091 -------------------------------
5092 -- Initializes debug variables
5093 -------------------------------
5094 l_calling_sequence := 'JL_AR_AP_WITHHOLDING_PKG' || '.' ||
5095 'Withholding_Already_Calculated<--' ||
5096 P_Calling_Sequence;
5097
5098 --------------------------------------------------------
5099 -- If the taxable base amount basis for the withholding
5100 -- is 'Payment', returns FALSE
5101 --------------------------------------------------------
5102 IF (nvl(P_Taxable_Base_Amount_Basis, 'PAYMENT') = 'PAYMENT') THEN
5103 RETURN FALSE;
5104
5105 ----------------------------------------------------------
5106 -- If the taxable base amount basis for the withholding
5107 -- is 'Invoice', we need to check whether the withholding
5108 -- was calculated previously for the invoice
5109 ----------------------------------------------------------
5110 ELSIF (P_Taxable_Base_Amount_Basis = 'INVOICE') THEN
5111
5112 -------------------------------------------
5113 -- Checks for PL*SQL Table
5114 -------------------------------------------
5115 FOR i IN 1 .. P_Tab_Withhold.COUNT LOOP
5116 IF (P_Tab_Withhold(i).invoice_id = P_Invoice_Id) AND
5117 (P_Tab_Withhold(i).tax_id = P_Tax_Id) AND
5118 (P_Tab_Withhold(i).payment_num <> P_Inv_Payment_Num) THEN
5119
5120 RETURN TRUE;
5121 END IF;
5122 END LOOP;
5123
5124 -------------------------------------------
5125 -- Checks for temporary distribution lines
5126 -------------------------------------------
5127 SELECT count('Withholding Already Calculated')
5128 INTO l_count
5129 FROM ap_awt_temp_distributions apatd
5130 WHERE apatd.invoice_id = P_Invoice_Id
5131 AND apatd.tax_name = P_Tax_Name;
5132
5133 IF (nvl(l_count, 0) > 0) THEN
5134 RETURN TRUE;
5135 END IF;
5136
5137 --------------------------------------
5138 -- Checks for real distribution lines
5139 --------------------------------------
5140 SELECT nvl(sum(apid.amount), 0)
5141 INTO l_withheld_amount
5142 FROM ap_invoice_distributions apid
5143 WHERE apid.invoice_id = P_Invoice_Id
5144 AND apid.line_type_lookup_code = 'AWT'
5145 AND apid.withholding_tax_code_id = P_Tax_Id
5146 -- added recently
5147 AND NVL(apid.REVERSAL_FLAG,'N') <> 'Y';
5148
5149 RETURN (l_withheld_amount <> 0);
5150 END IF;
5151
5152 RETURN FALSE;
5153
5154 EXCEPTION
5155 WHEN others THEN
5156 IF (SQLCODE <> -20001) THEN
5157 Fnd_Message.Set_Name ('JL', 'JL_ZZ_AP_DEBUG');
5158 Fnd_Message.Set_Token('ERROR', SQLERRM);
5159 Fnd_Message.Set_Token('CALLING_SEQUENCE', l_calling_sequence);
5160 Fnd_Message.Set_Token('PARAMETERS',
5161 ' Invoice Id= ' || to_char(P_Invoice_Id) ||
5162 ', Tax Name= ' || P_Tax_Name ||
5163 ', Taxable Base Amount Basis= ' || P_Taxable_Base_Amount_Basis);
5164 Fnd_Message.Set_Token('DEBUG_INFO', l_debug_info);
5165 END IF;
5166
5167 App_Exception.Raise_Exception;
5168
5169 END Withholding_Already_Calculated;
5170
5171
5172
5173
5174 /**************************************************************************
5175 * *
5176 * Name : Total_Withholding_Amount *
5177 * Purpose : Returns the total withheld amount for the withholding tax *
5178 * type (sums up all the prorated amounts). *
5179 * *
5180 **************************************************************************/
5181 FUNCTION Total_Withholding_Amount
5182 (P_Tab_Withhold IN Jl_Zz_Ap_Withholding_Pkg.Tab_Withholding,
5183 P_Calling_Sequence IN Varchar2)
5184 RETURN Number
5185 IS
5186
5187 l_withholding_amount Number := 0;
5188 l_debug_info Varchar2(300);
5189 l_calling_sequence Varchar2(2000);
5190
5191 BEGIN
5192 -------------------------------
5193 -- Initializes debug variables
5194 -------------------------------
5195 l_calling_sequence := 'JL_AR_AP_WITHHOLDING_PKG' || '.' ||
5196 'Total_Withholding_Amount<--' || P_Calling_Sequence;
5197
5198 -----------------------------------------------------------
5199 -- Sums up all the prorated amounts included into the table
5200 -----------------------------------------------------------
5201 FOR i IN 1 .. P_Tab_Withhold.COUNT LOOP
5202 l_withholding_amount := l_withholding_amount +
5203 nvl(P_Tab_Withhold(i).prorated_amount, 0);
5204 END LOOP;
5205 RETURN l_withholding_amount;
5206
5207 EXCEPTION
5208 WHEN others THEN
5209 IF (SQLCODE <> -20001) THEN
5210 Fnd_Message.Set_Name ('JL', 'JL_ZZ_AP_DEBUG');
5211 Fnd_Message.Set_Token('ERROR', SQLERRM);
5212 Fnd_Message.Set_Token('CALLING_SEQUENCE', l_calling_sequence);
5213 Fnd_Message.Set_Token('PARAMETERS', null);
5214 Fnd_Message.Set_Token('DEBUG_INFO', l_debug_info);
5215 END IF;
5216
5217 App_Exception.Raise_Exception;
5218
5219 END Total_Withholding_Amount;
5220
5221
5222
5223
5224 /**************************************************************************
5225 * *
5226 * Name : Partial_Payment_Paid_In_Full *
5227 * Purpose : Checks whether the payment amount is enough to cover the *
5228 * withholding amount. *
5229 * *
5230 **************************************************************************/
5231 FUNCTION Partial_Payment_Paid_In_Full
5232 (P_Check_Id IN Number,
5233 P_Selected_Check_Id IN Number,
5234 P_Calling_Module IN Varchar2,
5235 P_Total_Wh_Amount IN Number,
5236 P_Calling_Sequence IN Varchar2,
5237 P_Vendor_Name OUT NOCOPY Varchar2,
5238 P_Vendor_Site_Code OUT NOCOPY Varchar2)
5239 --P_Payment_Amount OUT NOCOPY Number) Bug# 2807464
5240 RETURN Boolean
5241 IS
5242
5243 l_payment_amount Number := 0;
5244 l_vendor_name Varchar2(240);
5245 l_vendor_site_code Varchar2(15);
5246 l_debug_info Varchar2(300);
5247 l_calling_sequence Varchar2(2000);
5248
5249 BEGIN
5250 -------------------------------
5251 -- Initializes debug variables
5252 -------------------------------
5253 l_calling_sequence := 'JL_AR_AP_WITHHOLDING_PKG' || '.' ||
5254 'Partial_Payment_Paid_In_Full<--' ||
5255 P_Calling_Sequence;
5256
5257 --------------------------------------------
5258 -- Obtains payment amount for Quick Payment
5259 --------------------------------------------
5260 IF (P_Calling_Module = 'QUICKCHECK') THEN
5261
5262 SELECT nvl(apchk.base_amount, apchk.amount),
5263 apchk.vendor_name,
5264 apchk.vendor_site_code
5265 INTO l_payment_amount,
5266 l_vendor_name,
5267 l_vendor_site_code
5268 FROM ap_checks apchk
5269 WHERE apchk.check_id = P_Check_Id;
5270
5271 --------------------------------------------
5272 -- Obtains payment amount for Payment Batch
5273 --------------------------------------------
5274 ELSIF (P_Calling_Module = 'AUTOSELECT') THEN
5275 SELECT nvl(sum(docs.document_amount/
5276 nvl(apsi.payment_cross_rate, 1) *
5277 nvl(apsi.invoice_exchange_rate, 1)), 0)
5278 INTO l_payment_amount
5279 FROM iby_hook_docs_in_pmt_t docs,
5280 ap_selected_invoices apsi
5281 WHERE docs.payment_id = P_Selected_Check_id
5282 AND apsi.invoice_id = docs.calling_app_doc_unique_ref2
5283 AND docs.dont_pay_flag = 'N'
5284 -- AND apsi.pay_selected_check_id = P_Selected_Check_id
5285 -- AND apsi.original_invoice_id IS NULL
5286 AND docs.calling_app_id=200;
5287
5288 /* RG SELECT vendor_name,
5289 vendor_site_code
5290 INTO l_vendor_name,
5291 l_vendor_site_code
5292 FROM ap_selected_invoice_checks
5293 WHERE selected_check_id = P_Selected_Check_id;
5294
5295
5296 SELECT asi.vendor_name,
5297 asi.vendor_site_code
5298 INTO l_vendor_name,
5299 l_vendor_site_code
5300 FROM IBY_HOOK_DOCS_IN_PMT_T docs,
5301 ap_selected_invoices_all asi
5302 WHERE docs.payment_id = P_Selected_Check_id
5303 AND docs.calling_app_doc_unique_ref2 = asi.invoice_id
5304 AND docs.calling_app_id=200;
5305 */
5306
5307 select a.vendor_name, b.vendor_site_code
5308 into l_vendor_name,
5309 l_vendor_site_code
5310 from ap_suppliers a, ap_supplier_sites_all b,
5311 iby_hook_payments_t c
5312 where c.PAYEE_PARTY_ID = a.party_id
5313 and c.SUPPLIER_SITE_ID = b.vendor_site_id
5314 and a.vendor_id = b.vendor_id
5315 and c.payment_id = P_Selected_Check_id;
5316
5317 END IF;
5318
5319 -------------------------
5320 -- Sets output arguments
5321 -------------------------
5322 P_Vendor_Name := l_vendor_name;
5323 P_Vendor_Site_Code := l_vendor_site_code;
5324 --P_Payment_Amount := l_payment_amount; Bug# 2807464
5325
5326 RETURN (l_payment_amount >= P_Total_Wh_Amount);
5327
5328 EXCEPTION
5329 WHEN others THEN
5330 IF (SQLCODE <> -20001) THEN
5331 Fnd_Message.Set_Name ('JL', 'JL_ZZ_AP_DEBUG');
5332 Fnd_Message.Set_Token('ERROR', SQLERRM);
5333 Fnd_Message.Set_Token('CALLING_SEQUENCE', l_calling_sequence);
5334 Fnd_Message.Set_Token('PARAMETERS',
5335 ' Check Id= ' || to_char(P_Check_Id) ||
5336 ', Selected Check_Id= ' || to_char(P_Selected_Check_Id) ||
5337 ', Calling Module= ' || P_Calling_Module ||
5338 ', Total Wh Amount= ' || to_char(P_Total_Wh_Amount));
5339 Fnd_Message.Set_Token('DEBUG_INFO', l_debug_info);
5340 END IF;
5341
5342 App_Exception.Raise_Exception;
5343
5344 END Partial_Payment_Paid_In_Full;
5345
5346
5347
5348 /**************************************************************************
5349 * *
5350 * Name : Confirm_Credit_Letters *
5351 * Purpose : Updates the credit letters table in order to store the *
5352 * the final check ID, when users confirm a payment batch. *
5353 * This procedure is not called for Quick Payments because *
5354 * the check ID is known from the begining. *
5355 * *
5356 **************************************************************************/
5357 PROCEDURE Confirm_Credit_Letters
5358 (P_Checkrun_Name IN Varchar2,
5359 p_checkrun_id IN Number,
5360 P_Calling_Sequence IN Varchar2)
5361 IS
5362
5363 ------------------------------
5364 -- Local variables definition
5365 ------------------------------
5366 l_check_id Number;
5367 l_selected_check_id Number;
5368 l_debug_info Varchar2(300);
5369 l_calling_sequence Varchar2(2000);
5370
5371 ------------------------------------------------------
5372 -- Cursor to select all the payments for a particular
5373 -- payment batch
5374 ------------------------------------------------------
5375 CURSOR c_selected_invoice_checks
5376 IS
5377 SELECT distinct(d.payment_id) check_id
5378 FROM iby_fd_payments_v p,iby_fd_docs_payable_v d
5379 WHERE to_number(d.calling_app_doc_unique_ref1) = p_checkrun_id
5380 AND p.payment_id = d.payment_id;
5381
5382 /*
5383 SELECT apsic.selected_check_id selected_check_id,
5384 apsic.check_id check_id
5385 FROM ap_selected_invoice_checks apsic
5386 WHERE checkrun_name = P_Checkrun_Name;
5387 */
5388
5389 BEGIN
5390 -------------------------------
5391 -- Initializes debug variables
5392 -------------------------------
5393 l_calling_sequence := 'JL_AR_AP_WITHHOLDING_PKG' || '.' ||
5394 'Confirm_Credit_Letters<--' ||
5395 P_Calling_Sequence;
5396
5397 ------------------------------------------------
5398 -- Updates credit letter table for each payment
5399 ------------------------------------------------
5400 OPEN c_selected_invoice_checks;
5401 LOOP
5402 FETCH c_selected_invoice_checks INTO l_check_id;
5403
5404 EXIT WHEN c_selected_invoice_checks%NOTFOUND;
5405
5406 ---------------------------------------------------
5407 -- Updates the credit letter information by
5408 -- replacing the selected check ID by the check ID
5409 ---------------------------------------------------
5410 IF (l_check_id IS NOT NULL) THEN
5411 UPDATE jl_ar_ap_sup_awt_cr_lts
5412 SET check_id = l_check_id,
5413 selected_check_id = null
5414 WHERE selected_check_id = l_check_id;
5415 END IF;
5416
5417 END LOOP;
5418
5419 CLOSE c_selected_invoice_checks;
5420
5421 EXCEPTION
5422 WHEN no_data_found THEN
5423 -- No credit letters available.
5424 null;
5425 WHEN others THEN
5426 -- Debug Information
5427 IF (DEBUG_Var = 'Y') THEN
5428 JL_ZZ_AP_EXT_AWT_UTIL.Debug ('Confirm Credit Letters: '||SQLERRM);
5429 END IF;
5430 -- end debug
5431
5432 IF (SQLCODE <> -20001) THEN
5433 Fnd_Message.Set_Name ('JL', 'JL_ZZ_AP_DEBUG');
5434 Fnd_Message.Set_Token('ERROR', SQLERRM);
5435 Fnd_Message.Set_Token('CALLING_SEQUENCE', l_calling_sequence);
5436 Fnd_Message.Set_Token('PARAMETERS',
5437 ' Checkrun Name= ' || P_Checkrun_Name);
5438 Fnd_Message.Set_Token('DEBUG_INFO', l_debug_info);
5439 END IF;
5440
5441 App_Exception.Raise_Exception;
5442
5443 END Confirm_Credit_Letters;
5444
5445
5446
5447 /**************************************************************************
5448 * *
5449 * Name : Reject_Payment_Batch *
5450 * Purpose : Sets the "Ok To Pay" flag for all the selected invoices *
5451 * within the payment when the calculation routine is not *
5452 * successful *
5453 * *
5454 * RG Sets the DONT_PAY_FLAG for all documents in payment *
5455 **************************************************************************/
5456 PROCEDURE Reject_Payment_Batch
5457 (P_Selected_Check_Id IN Number,
5458 P_AWT_Success IN Varchar2,
5459 P_Calling_Sequence IN Varchar2)
5460 IS
5461 ------------------------------
5462 -- Local variables definition
5463 ------------------------------
5464 l_ok_to_pay_flag Varchar2(10);
5465 l_dont_pay_reason_code Varchar2(25);
5466 l_dont_pay_description Varchar2(255);
5467 l_debug_info Varchar2(300);
5468 l_calling_sequence Varchar2(2000);
5469 l_invoice_id Number;
5470
5471 ----------------------
5472 -- Cursor definition
5473 ----------------------
5474 CURSOR c_selected_invoices (P_Selected_Check_Id IN Number) IS
5475 SELECT docs.dont_pay_flag dont_pay_flag ,
5476 docs.dont_pay_reason_code dont_pay_reason,
5477 docs.calling_app_doc_unique_ref2 invoice_id
5478 -- apsi.ok_to_pay_flag ok_to_pay_flag,
5479 -- apsi.dont_pay_reason_code dont_pay_reason_code,
5480 -- apsi.dont_pay_description dont_pay_description
5481 FROM iby_hook_docs_in_pmt_t docs
5482 -- ap_selected_invoices apsi
5483 WHERE docs.payment_id = P_Selected_Check_id
5484 AND docs.dont_pay_flag = 'N'
5485 AND docs.calling_app_id = 200
5486 -- apsi.pay_selected_check_id = P_Selected_Check_id
5487 -- AND nvl(apsi.ok_to_pay_flag, 'Y') = 'Y'
5488 -- AND apsi.original_invoice_id IS NULL
5489 FOR UPDATE OF docs.dont_pay_flag,
5490 docs.dont_pay_reason_code;
5491
5492 BEGIN
5493 -------------------------------
5494 -- Initializes debug variables
5495 -------------------------------
5496 l_calling_sequence := 'JL_AR_AP_WITHHOLDING_PKG' || '.' ||
5497 'Reject_Payment_Batch<--' ||
5498 P_Calling_Sequence;
5499
5500 -- Debug Information
5501 IF (DEBUG_Var = 'Y') THEN
5502 JL_ZZ_AP_EXT_AWT_UTIL.Debug ('PROCEDURE Reject_Payment_Batch');
5503 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Parameter P_Selected_Check_Id: '||to_char(P_Selected_Check_Id));
5504 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Parameter P_AWT_Success: '||P_AWT_Success);
5505 END IF;
5506 -- End Debug
5507
5508 -------------------------------------
5509 -- Updates all the selected invoices
5510 -------------------------------------
5511 OPEN c_selected_invoices (P_Selected_Check_Id);
5512 LOOP
5513 FETCH c_selected_invoices INTO l_ok_to_pay_flag,
5514 l_dont_pay_reason_code,
5515 l_invoice_id;
5516
5517 EXIT WHEN c_selected_invoices%NOTFOUND;
5518 UPDATE iby_hook_docs_in_pmt_t docs
5519 SET docs.dont_pay_flag = 'Y',
5520 docs.dont_pay_reason_code = AWT_ERROR
5521 WHERE CURRENT OF c_selected_invoices;
5522
5523 UPDATE ap_selected_invoices
5524 SET ok_to_pay_flag = 'N',
5525 dont_pay_reason_code = AWT_ERROR
5526 WHERE invoice_id = l_invoice_id;
5527
5528 END LOOP;
5529
5530 CLOSE c_selected_invoices;
5531
5532 -- RG Update also Payments Table with error
5533 UPDATE iby_hook_payments_t ipmt
5534 SET ipmt.dont_pay_flag = 'Y',
5535 ipmt.dont_pay_reason_code = AWT_ERROR
5536 WHERE ipmt.payment_id = P_Selected_Check_id;
5537
5538 EXCEPTION
5539 WHEN others THEN
5540 IF (SQLCODE <> -20001) THEN
5541 Fnd_Message.Set_Name ('JL', 'JL_ZZ_AP_DEBUG');
5542 Fnd_Message.Set_Token('ERROR', SQLERRM);
5543 Fnd_Message.Set_Token('CALLING_SEQUENCE', l_calling_sequence);
5544 Fnd_Message.Set_Token('PARAMETERS',
5545 ' Selected Check Id= ' || to_char(P_Selected_Check_Id) ||
5546 ', AWT Success= ' || P_AWT_Success);
5547 Fnd_Message.Set_Token('DEBUG_INFO', l_debug_info);
5548 END IF;
5549
5550 App_Exception.Raise_Exception;
5551
5552 END Reject_Payment_Batch;
5553
5554
5555 /**************************************************************************
5556 * *
5557 * Name : JL_CALL_DO_AWT *
5558 * Purpose : Bug# 1384294 The reason of this procedure is: *
5559 * One store procedure cannot be call from a form and *
5560 * at the same time from the library in a single apps *
5561 * session. *
5562 * *
5563 **************************************************************************/
5564 PROCEDURE JL_CALL_DO_AWT
5565 (P_Invoice_Id IN number
5566 ,P_Awt_Date IN date
5567 ,P_Calling_Module IN varchar2
5568 ,P_Amount IN number
5569 ,P_Payment_Num IN number
5570 default null
5571 ,P_Checkrun_Name IN varchar2
5572 default null
5573 ,P_Last_Updated_By IN number
5574 ,P_Last_Update_Login IN number
5575 ,P_Program_Application_Id IN number
5576 default null
5577 ,P_Program_Id IN number
5578 default null
5579 ,P_Request_Id IN number
5580 default null
5581 ,P_Awt_Success OUT NOCOPY varchar2
5582 ,P_Invoice_Payment_Id IN number
5583 default null
5584 ,P_Check_Id IN number
5585 )
5586 IS
5587 BEGIN
5588 -- Call to core procedure
5589 Ap_Withholding_Pkg.Ap_Do_Withholding (
5590 P_Invoice_Id
5591 ,P_AWT_Date
5592 ,P_Calling_Module
5593 ,P_Amount
5594 ,P_Payment_Num
5595 ,P_Checkrun_Name
5596 ,P_Last_Updated_By
5597 ,P_Last_Update_Login
5598 ,P_Program_Application_id
5599 ,P_Program_Id
5600 ,P_Request_Id
5601 ,P_Awt_Success
5602 ,P_Invoice_Payment_Id
5603 ,P_Check_Id
5604 );
5605 END JL_CALL_DO_AWT;
5606
5607 -- Bug 2722425 Added this new procedure for reissued checks
5608 -- to revert the updates to checks and invoice payments
5609 -- done by Core.
5610 /**************************************************************************
5611 * *
5612 * Name : Undo_Quick_Payment *
5613 * Purpose : Updates the payment amount by adding the withheld *
5614 * amount. *
5615 * *
5616 **************************************************************************/
5617 PROCEDURE Undo_Quick_Payment
5618 (P_Check_Id IN Number,
5619 P_Old_Check_Id IN Number,
5620 P_Calling_Sequence IN Varchar2)
5621 IS
5622
5623 ------------------------------
5624 -- Local variables definition
5625 ------------------------------
5626 l_invoice_payment_id Number;
5627 l_invoice_id Number;
5628 l_pay_exchange_rate Number;
5629 l_inv_exchange_rate Number;
5630 l_payment_cross_rate Number;
5631 l_payment_num Number;
5632 l_withhold_amount Number;
5633 l_amount Number;
5634 l_base_amount Number;
5635 l_total_wh_amount Number := 0;
5636 l_total_wh_base_amount Number := 0;
5637 l_debug_info Varchar2(300);
5638 l_calling_sequence Varchar2(2000);
5639 l_pay_amount Number;
5640 l_payment_base_amount Number;
5641 l_invoice_base_amount Number;
5642
5643 -------------------------------------
5644 -- Cursor to select all the invoices
5645 -- within the payment
5646 -------------------------------------
5647 CURSOR c_invoice_payment (P_Check_Id Number)
5648 IS
5649 SELECT apip.invoice_payment_id invoice_payment_id,
5650 apip.invoice_id invoice_id,
5651 apip.exchange_rate pay_exchange_rate,
5652 apip.payment_num payment_num,
5653 apip.amount amount,
5654 apip.payment_base_amount payment_base_amount,
5655 apip.invoice_base_amount invoice_base_amount
5656 FROM ap_invoice_payments apip
5657 WHERE apip.check_id = P_Check_Id
5658 FOR UPDATE OF apip.amount,
5659 apip.payment_base_amount,
5660 apip.invoice_base_amount;
5661
5662 --------------------------------
5663 -- Cursor to select the payment
5664 --------------------------------
5665 CURSOR c_checks (P_Check_Id Number)
5666 IS
5667 SELECT apch.amount amount,
5668 apch.base_amount base_amount
5669 FROM ap_checks apch
5670 WHERE apch.check_id = P_Check_Id
5671 FOR UPDATE OF apch.amount,
5672 apch.base_amount;
5673
5674
5675 -------------------------------------------------------------
5676 -- Cursor to get the withheld amount from the old check id
5677 ------------------------------------------------------------
5678
5679 CURSOR c_withheld_amount(P_Old_Check_Id Number,
5680 P_Invoice_Id Number)
5681 IS
5682 SELECT sum(aid.amount)
5683 FROM ap_invoice_distributions aid,
5684 ap_invoice_payments aip,
5685 ap_invoices ai
5686 WHERE aid.invoice_id = aip.invoice_id
5687 AND ai.invoice_id = aid.invoice_id
5688 AND aid.invoice_id = P_Invoice_Id
5689 AND aid.awt_invoice_payment_id = aip.invoice_payment_id
5690 AND aid.amount < 0
5691 AND aip.check_id = P_Old_Check_Id
5692 AND ai.invoice_type_lookup_code NOT IN ('CREDIT','DEBIT')
5693 -- added recently
5694 AND NVL(aid.REVERSAL_FLAG,'N') <> 'Y'
5695 UNION
5696 SELECT sum(aid.amount)
5697 FROM ap_invoice_distributions aid,
5698 ap_invoice_payments aip,
5699 ap_invoices ai
5700 WHERE aid.invoice_id = aip.invoice_id
5701 AND ai.invoice_id = aid.invoice_id
5702 AND aid.invoice_id = P_Invoice_Id
5703 AND aid.awt_invoice_payment_id = aip.invoice_payment_id
5704 AND aid.amount > 0
5705 AND aip.check_id = P_Old_Check_Id
5706 AND ai.invoice_type_lookup_code IN ('CREDIT','DEBIT')
5707 -- added recently
5708 AND NVL(aid.REVERSAL_FLAG,'N') <> 'Y'
5709 GROUP BY aid.invoice_id;
5710
5711
5712 BEGIN
5713 -------------------------------
5714 -- Initializes debug variables
5715 -------------------------------
5716 l_calling_sequence := 'JL_AR_AP_WITHHOLDING_PKG' || '.' ||
5717 'Undo_Quick_Payment<--' || P_Calling_Sequence;
5718
5719
5720 -- Debug Information
5721 IF (DEBUG_Var = 'Y') THEN
5722 -- JL_ZZ_AP_EXT_AWT_UTIL.Debug (' ');
5723 JL_ZZ_AP_EXT_AWT_UTIL.Debug ('PROCEDURE Undo_Quick_Payment');
5724 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Parameter P_Check_Id: '||to_char(P_Check_Id));
5725 END IF;
5726 -- End Debug
5727
5728 --------------------------------------------
5729 -- Updates amounts for the invoice payments
5730 --------------------------------------------
5731
5732 -- Debug Information
5733 IF (DEBUG_Var = 'Y') THEN
5734 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' OPEN c_invoice_payment');
5735 END IF;
5736 -- End Debug
5737
5738 OPEN c_invoice_payment(P_Check_Id);
5739 LOOP
5740 FETCH c_invoice_payment INTO l_invoice_payment_id,
5741 l_invoice_id,
5742 l_pay_exchange_rate,
5743 l_payment_num,
5744 l_pay_amount,
5745 l_payment_base_amount,
5746 l_invoice_base_amount;
5747 EXIT WHEN c_invoice_payment%NOTFOUND;
5748
5749 -- Debug Information
5750 IF (DEBUG_Var = 'Y') THEN
5751 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Fetched Values: l_invoice_payment_id= '||to_char(l_invoice_payment_id));
5752 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Fetched Values: l_invoice_id= '||to_char(l_invoice_id));
5753 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Fetched Values: l_pay_exchange_rate= '||to_char(l_pay_exchange_rate));
5754 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Fetched Values: l_payment_num= '||to_char(l_payment_num));
5755 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Fetched Values: l_pay_amount= '||to_char(l_pay_amount));
5756 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Fetched Values: l_payment_base_amount= '||to_char(l_payment_base_amount));
5757 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Fetched Values: l_invoice_base_amount= '||to_char(l_invoice_base_amount));
5758 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' ');
5759 END IF;
5760 -- End Debug
5761 -------------------------------------------
5762 -- Obtains withheld amount for the invoice
5763 -------------------------------------------
5764 OPEN c_withheld_amount (P_Old_Check_Id, l_invoice_id);
5765 LOOP
5766 FETCH c_withheld_amount INTO l_withhold_amount;
5767 EXIT WHEN c_withheld_amount%NOTFOUND;
5768
5769 -- Debug Information
5770 IF (DEBUG_Var = 'Y') THEN
5771 JL_ZZ_AP_EXT_AWT_UTIL.Debug ('Obtains withheld amount for the invoice : '||to_char(l_withhold_amount));
5772 END IF;
5773 -- End Debug
5774 IF (l_withhold_amount <> 0) THEN
5775
5776 --------------------------------
5777 -- Obtains currency information
5778 --------------------------------
5779 SELECT apin.exchange_rate,
5780 apps.payment_cross_rate
5781 INTO l_inv_exchange_rate,
5782 l_payment_cross_rate
5783 FROM ap_invoices apin,
5784 ap_payment_schedules apps
5785 WHERE apin.invoice_id = l_invoice_id
5786 AND apps.invoice_id = l_invoice_id
5787 AND apps.payment_num = l_payment_num;
5788
5789 -- Debug Information
5790 IF (DEBUG_Var = 'Y') THEN
5791 JL_ZZ_AP_EXT_AWT_UTIL.Debug ('Obtains currency information: exch rate, pay cross rate '||
5792 to_char(l_inv_exchange_rate)||', '||to_char(l_payment_cross_rate));
5793 END IF;
5794 -- End Debug
5795
5796 --------------------------------------------------------
5797 -- Updates the amount remaining of the payment schedule
5798 --------------------------------------------------------
5799 UPDATE ap_payment_schedules
5800 SET amount_remaining = amount_remaining +
5801 (l_withhold_amount *
5802 nvl(l_payment_cross_rate, 1)),
5803 payment_status_flag = decode(amount_remaining +
5804 (l_withhold_amount *
5805 nvl(l_payment_cross_rate, 1)),
5806 0, 'Y',
5807 amount_remaining,
5808 payment_status_flag, 'P')
5809 WHERE invoice_id = l_invoice_id
5810 AND payment_num = l_payment_num;
5811
5812 ------------------------------------------
5813 -- Updates the amount paid of the invoice
5814 -- amount_paid does not affect MRC
5815 ------------------------------------------
5816 UPDATE ap_invoices
5817 SET amount_paid = nvl(amount_paid, 0) -
5818 (l_withhold_amount *
5819 nvl(l_payment_cross_rate, 1)),
5820 payment_status_flag = AP_INVOICES_UTILITY_PKG.get_payment_status(l_invoice_id)
5821 WHERE invoice_id = l_invoice_id;
5822
5823 --------------------------------------------------------------
5824 -- Updates the payment amount
5825 -- Calling the AP Table Handler to update ap_invoice_payments.
5826 --------------------------------------------------------------
5827 l_pay_amount := l_pay_amount - (l_withhold_amount *
5828 nvl(l_payment_cross_rate, 1));
5829
5830 l_invoice_base_amount := l_invoice_base_amount -
5831 (l_withhold_amount * nvl(l_inv_exchange_rate, 1));
5832
5833 l_payment_base_amount := l_payment_base_amount -
5834 (l_withhold_amount * nvl(l_payment_cross_rate, 1) *
5835 nvl(l_pay_exchange_rate, 1));
5836
5837 IF (Debug_var = 'Y' ) Then
5838 JL_ZZ_AP_EXT_AWT_UTIL.Debug('Payment Amt =' || to_char(l_pay_amount) || 'Inv BaseAmt :'
5839 || to_char(l_withhold_amount));
5840 END IF;
5841
5842 AP_AIP_TABLE_HANDLER_PKG.Update_Amounts(
5843 l_invoice_payment_id
5844 ,l_pay_amount
5845 ,l_invoice_base_amount
5846 ,l_payment_base_amount
5847 ,l_calling_sequence);
5848
5849
5850 --------------------------------------------
5851 -- Calculates total amounts for the payment
5852 --------------------------------------------
5853 l_total_wh_amount := l_total_wh_amount +
5854 (l_withhold_amount *
5855 nvl(l_payment_cross_rate, 1));
5856
5857 l_total_wh_base_amount := l_total_wh_base_amount +
5858 (l_withhold_amount *
5859 nvl(l_payment_cross_rate, 1) *
5860 nvl(l_pay_exchange_rate, 1));
5861
5862 END IF;
5863 END LOOP; -- end of c_withheld_amount
5864 CLOSE c_withheld_amount;
5865 END LOOP;
5866 CLOSE c_invoice_payment;
5867
5868 -------------------------------------------------------------------
5869 -- Updates the payment amount for the check
5870 -- Calling the AP Table Handler to update ap_checks.
5871 -------------------------------------------------------------------
5872 IF (l_total_wh_amount <> 0 OR l_total_wh_base_amount <> 0) THEN
5873 OPEN c_checks (P_Check_Id);
5874 FETCH c_checks INTO l_amount, l_base_amount;
5875 IF (NOT c_checks%NOTFOUND) THEN
5876
5877 l_amount := l_amount - l_total_wh_amount;
5878 l_base_amount := l_base_amount - l_total_wh_base_amount;
5879
5880 AP_AC_TABLE_HANDLER_PKG.Update_Amounts(
5881 P_check_id
5882 ,l_amount
5883 ,l_base_amount
5884 ,l_calling_sequence);
5885
5886 END IF;
5887 CLOSE c_checks;
5888 END IF;
5889
5890 -- Debug Information
5891 IF (DEBUG_Var = 'Y') THEN
5892 JL_ZZ_AP_EXT_AWT_UTIL.Debug ('End Procedure Undo_Quick_Payment');
5893 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' ');
5894 END IF;
5895 -- End Debug
5896
5897 EXCEPTION
5898 WHEN others THEN
5899 IF (SQLCODE <> -20001) THEN
5900 Fnd_Message.Set_Name ('JL', 'JL_ZZ_AP_DEBUG');
5901 Fnd_Message.Set_Token('ERROR', SQLERRM);
5902 Fnd_Message.Set_Token('CALLING_SEQUENCE', l_calling_sequence);
5903 Fnd_Message.Set_Token('PARAMETERS',
5904 ' Check Id= ' || to_char(P_Check_Id));
5905 Fnd_Message.Set_Token('DEBUG_INFO', l_debug_info);
5906 END IF;
5907 App_Exception.Raise_Exception;
5908
5909 END Undo_Quick_Payment;
5910
5911 END JL_AR_AP_WITHHOLDING_PKG;