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