[Home] [Help]
PACKAGE BODY: APPS.JL_ZZ_AP_WITHHOLDING_PKG
Source
1 PACKAGE BODY JL_ZZ_AP_WITHHOLDING_PKG AS
2 /* $Header: jlzzpwhb.pls 120.22.12010000.2 2008/08/04 12:52:33 vgadde ship $ */
3
4
5
6 /**************************************************************************
7 * Private Procedure Specification *
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 : Get_Period_Name *
16 * Purpose : Returns the name of the AWT period for a particular tax *
17 * name and period type *
18 * *
19 **************************************************************************/
20 FUNCTION Get_Period_Name
21 (P_Tax_Name IN Varchar2,
22 P_Period_Type IN Varchar2,
23 P_AWT_Date IN Date,
24 P_Calling_Sequence IN Varchar2,
25 P_AWT_Success OUT NOCOPY Varchar2)
26 RETURN Varchar2;
27
28
29
30
31 /**************************************************************************
32 * *
33 * Name : Get_Cumulative_Figures *
34 * Purpose : Obtains the cumulative gross amount to date and the *
35 * cumulative withheld amount to date for a particular *
36 * supplier, tax name and period. *
37 * *
38 **************************************************************************/
39 PROCEDURE Get_Cumulative_Figures
40 (P_Vendor_Id IN Number,
41 P_Tax_Name IN Varchar2,
42 P_AWT_Period_Type IN Varchar2,
43 P_AWT_Date IN Date,
44 P_Calling_Sequence IN Varchar2,
45 P_Gross_Amount_To_Date OUT NOCOPY Number,
46 P_Withheld_Amount_To_Date OUT NOCOPY Number,
47 P_AWT_Success OUT NOCOPY Varchar2);
48
49
50
51
52 /**************************************************************************
53 * *
54 * Procedure : Get_Tax_Rate *
55 * Description: Obtains the tax rate for the current tax name and for the *
56 * calculated taxable base amount. *
57 * *
58 **************************************************************************/
59 PROCEDURE Get_Tax_Rate
60 (P_Tax_Name IN Varchar2,
61 P_Date IN Date,
62 P_Taxable_Base_Amount IN Number,
63 P_Calling_Sequence IN Varchar2,
64 P_Rec_AWT_Rate OUT NOCOPY Rec_AWT_Rate,
65 P_AWT_Success OUT NOCOPY Varchar2);
66
67
68
69
70 /**************************************************************************
71 * *
72 * Name : Update_Withheld_Amount *
73 * Purpose : Prorates the withheld amount for each tax name included *
74 * into the PL/SQL table. These values will also be rounded. *
75 * *
76 **************************************************************************/
77 PROCEDURE Update_Withheld_Amount
78 (P_Original_Withheld_Amt IN Number,
79 P_Updated_Withheld_Amt IN Number,
80 P_Currency_Code IN Varchar2,
81 P_Calling_Sequence IN Varchar2,
82 P_Tab_Withhold IN OUT NOCOPY Tab_Withholding);
83
84
85
86
87 /**************************************************************************
88 * *
89 * Name : Get_Revised_Tax_Base_Amount *
90 * Purpose : 1 Retrieves the taxable base amount from the PL/SQL table *
91 * 2 Applies all the validations like income tax rate, *
92 * reduction percentage etc., and generates a revised *
93 * taxable base amount. *
94 * 3 Updates the PL/SQL table to store the revised amount *
95 * *
96 **************************************************************************/
97 FUNCTION Get_Revised_Tax_Base_Amount
98 (P_Rec_AWT_Name IN Rec_AWT_CODE,
99 P_Tab_Withhold IN OUT NOCOPY Tab_Withholding,
100 P_Tax_Name_From IN Number,
101 P_Tax_Name_To IN Number,
102 P_Taxable_Base_Amount IN Number,
103 P_Tab_All_Withhold IN Tab_All_Withholding,
104 P_Calling_Sequence IN Varchar2)
105 RETURN NUMBER;
106
107
108
109
110 /**************************************************************************
111 * *
112 * Name : Bool_To_Char *
113 * Purpose : Converts the Boolean value received as a parameter to a *
114 * Varchar2 character string. This function is only used *
115 * for debug purposes. *
116 * *
117 **************************************************************************/
118 FUNCTION Bool_To_Char (P_Bool_Value IN Boolean)
119 RETURN Varchar2;
120
121 /**************************************************************************
122 * *
123 * Name : Get_Cumulative_Supp_Exemp *
124 * Purpose : Obtains the cumulative supplier's exemption amount *
125 * to date for a particular period *
126 * *
127 **************************************************************************/
128 FUNCTION Get_Cumulative_Supp_Exemp
129 (P_Vendor_Id IN Number,
130 P_Tax_Name IN Varchar2,
131 P_AWT_Period_Type IN Varchar2,
132 P_AWT_Date IN Date,
133 P_Calling_Sequence IN Varchar2)
134 RETURN NUMBER;
135
136 /**************************************************************************
137 * Public Procedures *
138 **************************************************************************/
139
140
141
142 /**************************************************************************
143 * *
144 * Name : Get_Withholding_Options *
145 * Purpose : Obtains all the withholding setup options from AP_SYSTEM_ *
146 * PARAMETERS table *
147 * *
148 **************************************************************************/
149 PROCEDURE Get_Withholding_Options (P_Create_Distr OUT NOCOPY Varchar2,
150 P_Create_Invoices OUT NOCOPY Varchar2)
151 IS
152
153 ------------------------------
154 -- Local variables definition
155 ------------------------------
156 l_create_distr Varchar2(25);
157 l_create_invoices Varchar2(25);
158 l_debug_info Varchar2(300);
159 l_calling_sequence Varchar2(2000);
160
161 BEGIN
162 -------------------------------
163 -- Initializes debug variables
164 -------------------------------
165 l_calling_sequence := 'JL_ZZ_AP_WITHHOLDING_PKG' || '.' ||
166 'Get_Withholding_Options';
167
168 ----------------------------
169 -- Obtains Payables Options
170 ----------------------------
171 SELECT nvl(create_awt_dists_type, 'NEVER'),
172 nvl(create_awt_invoices_type, 'NEVER')
173 INTO l_create_distr,
174 l_create_invoices
175 FROM ap_system_parameters;
176
177 P_Create_Distr := l_create_distr;
178 P_Create_Invoices := l_create_invoices;
179
180 EXCEPTION
181 WHEN others THEN
182 IF (SQLCODE <> -20001) THEN
183 Fnd_Message.Set_Name ('JL', 'JL_ZZ_AP_DEBUG');
184 Fnd_Message.Set_Token('ERROR', SQLERRM);
185 Fnd_Message.Set_Token('CALLING_SEQUENCE', l_calling_sequence);
186 Fnd_Message.Set_Token('PARAMETERS', 'NO INPUT ARGUMENTS');
187 Fnd_Message.Set_Token('DEBUG_INFO', l_debug_info);
188 END IF;
189
190 App_Exception.Raise_Exception;
191
192 END Get_Withholding_Options;
193
194
195
196
197 /**************************************************************************
198 * *
199 * Name : Get_GL_Period_Name *
200 * Purpose : Returns the period name for a particular date. *
201 * *
202 **************************************************************************/
203 FUNCTION Get_GL_Period_Name (P_AWT_Date IN Date)
204 RETURN VARCHAR2
205 IS
206 ------------------------------
207 -- Local variables definition
208 ------------------------------
209 l_gl_period_name Varchar2(15);
210 l_debug_info Varchar2(300);
211 l_calling_sequence Varchar2(2000);
212
213 BEGIN
214 -------------------------------
215 -- Initializes debug variables
216 -------------------------------
217 l_calling_sequence := 'JL_ZZ_AP_WITHHOLDING_PKG' || '.' ||
218 'Get_GL_Period_Name';
219
220 ----------------------------------
221 -- Obtains the name of the period
222 ----------------------------------
223 SELECT gps.period_name
224 INTO l_gl_period_name
225 FROM gl_period_statuses gps,
226 ap_system_parameters asp
227 WHERE gps.application_id = 200
228 AND gps.set_of_books_id = asp.set_of_books_id
229 AND P_AWT_Date BETWEEN gps.start_date AND gps.end_date
230 AND gps.closing_status IN ('O', 'F');
231
232 RETURN l_gl_period_name;
233
234 EXCEPTION
235 WHEN others THEN
236 IF (SQLCODE <> -20001) THEN
237 Fnd_Message.Set_Name ('JL', 'JL_ZZ_AP_DEBUG');
238 Fnd_Message.Set_Token('ERROR', SQLERRM);
239 Fnd_Message.Set_Token('CALLING_SEQUENCE', l_calling_sequence);
240 Fnd_Message.Set_Token('PARAMETERS',
241 ' AWT Date= ' || to_char(P_AWT_Date,'YYYY/MM/DD'));
242 Fnd_Message.Set_Token('DEBUG_INFO', l_debug_info);
243 END IF;
244
245 App_Exception.Raise_Exception;
246
247 END Get_GL_Period_Name;
248
249
250
251
252 /**************************************************************************
253 * *
254 * Name : Get_Base_Currency_Code *
255 * Purpose : Returns the functional currency code (from AP_SYSTEM_ *
256 * PARAMETERS) *
257 * *
258 **************************************************************************/
259 FUNCTION Get_Base_Currency_Code RETURN VARCHAR2
260 IS
261
262 ------------------------------
263 -- Local variables definition
264 ------------------------------
265 l_base_currency_code Varchar2(15);
266 l_debug_info Varchar2(300);
267 l_calling_sequence Varchar2(2000);
268
269 BEGIN
270 -------------------------------
271 -- Initializes debug variables
272 -------------------------------
273 l_calling_sequence := 'JL_ZZ_AP_WITHHOLDING_PKG' || '.' ||
274 'Get_Base_Currency_Code';
275
276 ------------------------------------
277 -- Obtains functional currency code
278 ------------------------------------
279 SELECT base_currency_code
280 INTO l_base_currency_code
281 FROM ap_system_parameters;
282
283 RETURN l_base_currency_code;
284
285 EXCEPTION
286 WHEN others THEN
287 IF (SQLCODE <> -20001) THEN
288 Fnd_Message.Set_Name ('JL', 'JL_ZZ_AP_DEBUG');
289 Fnd_Message.Set_Token('ERROR', SQLERRM);
290 Fnd_Message.Set_Token('CALLING_SEQUENCE', l_calling_sequence);
291 Fnd_Message.Set_Token('PARAMETERS', 'NO INPUT ARGUMENTS');
292 Fnd_Message.Set_Token('DEBUG_INFO', l_debug_info);
293 END IF;
294
295 App_Exception.Raise_Exception;
296
297 END Get_Base_Currency_Code;
298
299
300
301
302 /**************************************************************************
303 * *
304 * Name : Initialize_Withholding_Table *
305 * Purpose : Initialize the PL/SQL table to store the withholding tax *
306 * names. *
307 * *
308 **************************************************************************/
309 PROCEDURE Initialize_Withholding_Table (P_Wh_Table IN OUT NOCOPY Tab_Withholding)
310 IS
311 ------------------------------
312 -- Local variables definition
313 ------------------------------
314 l_debug_info Varchar2(300);
315 l_calling_sequence Varchar2(2000);
316
317 BEGIN
318 -------------------------------
319 -- Initializes debug variables
320 -------------------------------
321 l_calling_sequence := 'JL_ZZ_AP_WITHHOLDING_PKG' || '.' ||
322 'Initialize_Withholding_Table';
323
324 -----------------------------------
325 -- Initializing withholding table
326 -----------------------------------
327 IF (P_Wh_Table IS NOT NULL) THEN
328 P_Wh_Table.DELETE;
329 END IF;
330
331 EXCEPTION
332 WHEN others THEN
333 IF (SQLCODE <> -20001) THEN
334 Fnd_Message.Set_Name ('JL', 'JL_ZZ_AP_DEBUG');
335 Fnd_Message.Set_Token('ERROR', SQLERRM);
336 Fnd_Message.Set_Token('CALLING_SEQUENCE', l_calling_sequence);
337 Fnd_Message.Set_Token('PARAMETERS', null);
338 Fnd_Message.Set_Token('DEBUG_INFO', l_debug_info);
339 END IF;
340
341 App_Exception.Raise_Exception;
342
343 END Initialize_Withholding_Table;
344
345
346
347
348 /**************************************************************************
349 * *
350 * Name : Initialize_Withholding_Type *
351 * Purpose : Obtains all the information associated to the current *
352 * withholding tax type and for a particular supplier: *
353 * 1. Minimum taxable base amount *
354 * 2. Minimum withheld amount *
355 * 3. Associated attributes (from JL_ZZ_AP_AWT_TYPES) *
356 * 4. Supplier exemptions *
357 * 5. Multilateral contribution *
358 * *
359 **************************************************************************/
360 PROCEDURE Initialize_Withholding_Type
361 (P_AWT_Type_Code IN Varchar2,
362 P_Vendor_Id IN Number,
363 P_Rec_AWT_Type OUT NOCOPY jl_zz_ap_awt_types%ROWTYPE,
364 P_Rec_Suppl_AWT_Type OUT NOCOPY jl_zz_ap_supp_awt_types%ROWTYPE)
365 IS
366 ------------------------------
367 -- Local variables definition
368 ------------------------------
369 l_debug_info Varchar2(300);
370 l_calling_sequence Varchar2(2000);
371
372 BEGIN
373 -------------------------------
374 -- Initializes debug variables
375 -------------------------------
376 l_calling_sequence := 'JL_ZZ_AP_WITHHOLDING_PKG' || '.' ||
377 'Initialize_Withholding_Type';
378
379 -----------------------------------------------------------
380 -- Obtains all the attributes for the withholding tax type
381 -----------------------------------------------------------
382 l_debug_info := 'Obtains withholding tax type attributes';
383 SELECT *
384 INTO P_Rec_AWT_Type
385 FROM jl_zz_ap_awt_types
386 WHERE awt_type_code = P_AWT_Type_Code;
387
388 -----------------------------------------------------------
389 -- Obtains all the attributes for the withholding tax type
390 -- and for the supplier
391 -----------------------------------------------------------
392 l_debug_info := 'Obtains withholding tax type attributes for the supplier';
393 SELECT *
394 INTO P_Rec_Suppl_AWT_Type
395 FROM jl_zz_ap_supp_awt_types
396 WHERE awt_type_code = P_AWT_Type_Code
397 AND vendor_id = P_Vendor_Id;
398
399 EXCEPTION
400 WHEN others THEN
401 IF (SQLCODE <> -20001) THEN
402 Fnd_Message.Set_Name ('JL', 'JL_ZZ_AP_DEBUG');
403 Fnd_Message.Set_Token('ERROR', SQLERRM);
404 Fnd_Message.Set_Token('CALLING_SEQUENCE', l_calling_sequence);
405 Fnd_Message.Set_Token('PARAMETERS',
406 ' AWT Type Code= ' || P_AWT_Type_Code ||
407 ', Vendor Id= ' || to_char(P_Vendor_Id));
408 Fnd_Message.Set_Token('DEBUG_INFO', l_debug_info);
409 END IF;
410
411 App_Exception.Raise_Exception;
412
413 END Initialize_Withholding_Type;
414
415
416
417
418 /**************************************************************************
419 * *
420 * Name : Initialize_Withholding_Name *
421 * Purpose : Obtains all the information associated to the current *
422 * tax name and for a particular supplier: *
423 * 1. Minimum taxable base amount *
424 * 2. Minimum withheld amount *
425 * 3. Associated global attributes (from AP_TAX_CODES) *
426 * 4. Supplier exemptions *
427 * *
428 **************************************************************************/
429 PROCEDURE Initialize_Withholding_Name
430 (P_AWT_Type_Code IN Varchar2,
431 P_Tax_Id IN Number,
432 P_Vendor_Id IN Number,
433 P_AWT_Name OUT NOCOPY Rec_AWT_Code,
434 P_Rec_Suppl_AWT_Name OUT NOCOPY jl_zz_ap_sup_awt_cd%ROWTYPE)
435 IS
436 ------------------------------
437 -- Local variables definition
438 ------------------------------
439 l_glattr6 Varchar2(150);
440 l_glattr7 Varchar2(150);
441 l_glattr8 Varchar2(150);
442 l_glattr9 Varchar2(150);
443 l_glattr10 Varchar2(150);
444 l_glattr11 Varchar2(150);
445 l_glattr12 Varchar2(150);
446 l_glattr13 Varchar2(150);
447 l_glattr14 Varchar2(150);
448 l_glattr15 Varchar2(150);
449 l_glattr16 Varchar2(150);
450 l_glattr17 Varchar2(150);
451 l_glattr18 Varchar2(150);
452 l_attr1_type_error Boolean := FALSE;
453 l_attr2_type_error Boolean := FALSE;
454 l_attr3_type_error Boolean := FALSE;
455 l_debug_info Varchar2(300);
456 l_calling_sequence Varchar2(2000);
457 Tax_Name_Attributes_Error Exception;
458
459 BEGIN
460 -------------------------------
461 -- Initializes debug variables
462 -------------------------------
463 l_calling_sequence := 'JL_ZZ_AP_WITHHOLDING_PKG' || '.' ||
464 'Initialize_Withholding_Name';
465
466 -----------------------------------------------------------
467 -- Obtains all the attributes for the tax name
468 -----------------------------------------------------------
469 l_debug_info := 'Obtain all tax name attributes';
470 SELECT tax_id,
471 name,
472 tax_code_combination_id,
473 awt_period_type,
474 global_attribute6,
475 global_attribute7,
476 global_attribute8,
477 global_attribute9,
478 global_attribute10,
479 global_attribute11,
480 global_attribute12,
481 global_attribute13,
482 global_attribute14,
483 global_attribute15,
484 global_attribute16,
485 global_attribute17,
486 global_attribute18
487 INTO P_AWT_Name.Tax_Id,
488 P_AWT_Name.Name,
489 P_AWT_Name.Tax_Code_Combination_Id,
490 P_AWT_Name.AWT_Period_Type,
491 l_glattr6,
492 l_glattr7,
493 l_glattr8,
494 l_glattr9,
495 l_glattr10,
496 l_glattr11,
497 l_glattr12,
498 l_glattr13,
499 l_glattr14,
500 l_glattr15,
501 l_glattr16,
502 l_glattr17,
503 l_glattr18
504 FROM ap_tax_codes
505 WHERE tax_id = P_Tax_Id;
506
507 --------------------------
508 -- Sets common attributes
509 --------------------------
510 BEGIN
511 P_AWT_Name.Foreign_Rate_Ind := substr(l_glattr6, 1, 1);
512 P_AWT_Name.Zone_Code := substr(l_glattr7, 1, 30);
513 P_AWT_Name.Item_Applic := substr(l_glattr8, 1, 1);
514 P_AWT_Name.Freight_Applic := substr(l_glattr9, 1, 1);
515 P_AWT_Name.Misc_Applic := substr(l_glattr10, 1, 1);
516 P_AWT_Name.Tax_Applic := substr(l_glattr11, 1, 1);
517 P_AWT_Name.Min_Tax_Base_Amt := fnd_number.canonical_to_number(l_glattr12);
518 P_AWT_Name.Min_Withheld_Amt := fnd_number.canonical_to_number(l_glattr13);
519 EXCEPTION
520 WHEN others THEN
521 l_attr1_type_error := TRUE;
522 END;
523
524 ---------------------------------
525 -- Sets attributes for Argentina
526 ---------------------------------
527 BEGIN
528 P_AWT_Name.Adj_Min_Base := substr(l_glattr14, 1, 30);
529 P_AWT_Name.Cumulative_Payment_Flag := substr(l_glattr15, 1, 1);
530 P_AWT_Name.Tax_Inclusive := substr(l_glattr16, 1, 1);
531 EXCEPTION
532 WHEN others THEN
533 l_attr2_type_error := TRUE;
534 END;
535
536 ---------------------------------
537 -- Sets attributes for Colombia
538 ---------------------------------
539 BEGIN
540 P_AWT_Name.Income_Tax_Rate := fnd_number.canonical_to_number(l_glattr14);
541 P_AWT_Name.First_Tax_Type := substr(l_glattr15, 1, 30);
542 P_AWT_Name.Second_Tax_Type := substr(l_glattr16, 1, 30);
543 P_AWT_Name.Municipal_Type := substr(l_glattr17, 1, 1);
544 P_AWT_Name.Reduction_Perc := fnd_number.canonical_to_number(l_glattr18);
545 EXCEPTION
546 WHEN others THEN
547 l_attr3_type_error := TRUE;
548 END;
549
550 -------------------------------------------------
551 -- Checks for any possible type conversion error
552 -------------------------------------------------
553 IF (l_attr1_type_error OR (l_attr2_type_error AND l_attr3_type_error)) THEN
554 l_debug_info := 'Obtain tax name attributes';
555 RAISE Tax_Name_Attributes_Error;
556 END IF;
557
558 -----------------------------------------------------------
559 -- Obtains all the attributes for the tax name and for
560 -- the supplier
561 -----------------------------------------------------------
562 l_debug_info := 'Obtain tax name attributes for the supplier';
563 SELECT *
564 INTO P_Rec_Suppl_AWT_Name
565 FROM jl_zz_ap_sup_awt_cd jlsc
566 WHERE jlsc.tax_id = P_Tax_Id
567 AND jlsc.supp_awt_type_id =
568 (SELECT jlst.supp_awt_type_id
569 FROM jl_zz_ap_supp_awt_types jlst
570 WHERE jlst.awt_type_code = P_AWT_Type_Code
571 AND jlst.vendor_id = P_Vendor_Id);
572
573 EXCEPTION
574 WHEN others THEN
575 IF (SQLCODE <> -20001) THEN
576 Fnd_Message.Set_Name ('JL', 'JL_ZZ_AP_DEBUG');
577 Fnd_Message.Set_Token('ERROR', SQLERRM);
578 Fnd_Message.Set_Token('CALLING_SEQUENCE', l_calling_sequence);
579 Fnd_Message.Set_Token('PARAMETERS',
580 ' AWT Type Code= ' || P_AWT_Type_Code ||
581 ', Tax Id= ' || to_char(P_Tax_Id) ||
582 ', Vendor Id= ' || to_char(P_Vendor_Id));
583 Fnd_Message.Set_Token('DEBUG_INFO', l_debug_info);
584 END IF;
585
586 App_Exception.Raise_Exception;
587
588 END Initialize_Withholding_Name;
589
590
591
592
593 /**************************************************************************
594 * *
595 * Name : Get_Taxable_Base_Amount *
596 * Purpose : Obtains the taxable base amount for a particular tax name *
597 * This amount is calculated as follows: *
598 * * The distribution line amount for those invoice based *
599 * withholding taxes *
600 * * The proportional payment amount for those payment based *
601 * withholding taxes *
602 * *
603 **************************************************************************/
604 FUNCTION Get_Taxable_Base_Amount
605 (P_Invoice_Id IN Number,
606 P_Distr_Line_No IN Number,
607 P_Line_Amount IN Number,
608 P_Payment_Amount IN Number Default null,
609 P_Invoice_Amount IN Number,
610 P_Tax_Base_Amount_Basis IN Varchar2) RETURN NUMBER
611 IS
612 ------------------------------
613 -- Local variables definition
614 ------------------------------
615 l_debug_info Varchar2(300);
616 l_calling_sequence Varchar2(2000);
617
618 BEGIN
619 -------------------------------
620 -- Initializes debug variables
621 -------------------------------
622 l_calling_sequence := 'JL_ZZ_AP_WITHHOLDING_PKG' || '.' ||
623 'Get_Taxable_Base_Amount';
624
625 ----------------------------------------------------------------
626 -- This procedure is no longer used for Argentina. It will only
627 -- be called from the Colombian withholding calculation routine.
628 -- Argentine calculation will call a private procedure to obtain
629 -- taxable base amount.
630 ----------------------------------------------------------------
631 IF (P_Tax_Base_Amount_Basis = 'INVOICE') THEN
632 RETURN P_Line_Amount;
633
634 ELSIF (P_Tax_Base_Amount_Basis = 'PAYMENT') THEN
635 RETURN P_Line_Amount * P_Payment_Amount / P_Invoice_Amount;
636
637 END IF;
638
639 RETURN 0;
640
641 EXCEPTION
642 WHEN others THEN
643 IF (SQLCODE <> -20001) THEN
644 Fnd_Message.Set_Name ('JL', 'JL_ZZ_AP_DEBUG');
645 Fnd_Message.Set_Token('ERROR', SQLERRM);
646 Fnd_Message.Set_Token('CALLING_SEQUENCE', l_calling_sequence);
647 Fnd_Message.Set_Token('PARAMETERS',
648 ' Invoice Id= ' || to_char(P_Invoice_Id) ||
649 ', Distr Line No= ' || to_char(P_Distr_Line_No) ||
650 ', Line Amount= ' || to_char(P_Line_Amount) ||
651 ', Payment Amount= ' || to_char(P_Payment_Amount) ||
652 ', Invoice Amount= ' || to_char(P_Invoice_Amount) ||
653 ', Tax Base Amount Basis= ' || P_Tax_Base_Amount_Basis);
654 Fnd_Message.Set_Token('DEBUG_INFO', l_debug_info);
655 END IF;
656
657 App_Exception.Raise_Exception;
658
659 END Get_Taxable_Base_Amount;
660
661
662
663
664 /**************************************************************************
665 * *
666 * Name : Store_Tax_Name *
667 * Purpose : Put the information regarding the current tax name of the *
668 * payment into the PL/SQL table *
669 * *
670 **************************************************************************/
671 PROCEDURE Store_Tax_Name
672 (P_Tab_Withhold IN OUT NOCOPY Tab_Withholding,
673 P_Current_AWT IN Number,
674 P_Invoice_Id IN Number,
675 P_Distr_Line_No IN Number,
676 P_AWT_Type_Code IN Varchar2,
677 P_Tax_Id IN Number,
678 P_Tax_Name IN Varchar2,
679 P_Tax_Code_Comb_Id IN Number,
680 P_AWT_Period_Type IN Varchar2,
681 P_Jurisdiction_Type IN Varchar2,
682 P_Line_Amount IN Number,
683 P_Taxable_Base_Amount IN Number,
684 P_Invoice_Payment_Id IN Number Default null,
685 -- By zmohiudd for bug 1849986 for handling null
686 P_Payment_Num IN Number Default null)
687 IS
688 ------------------------------
689 -- Local variables definition
690 ------------------------------
691 l_rec Rec_Withholding;
692 l_debug_info Varchar2(300);
693 l_calling_sequence Varchar2(2000);
694
695 BEGIN
696 -------------------------------
697 -- Initializes debug variables
698 -------------------------------
699 l_calling_sequence := 'JL_ZZ_AP_WITHHOLDING_PKG' || '.' ||
700 'Store_Tax_Name';
701
702 ----------------------------------------
703 -- Stores the information into the table
704 ----------------------------------------
705 l_rec.invoice_id := P_Invoice_Id;
706 l_rec.invoice_distribution_id := P_Distr_Line_No;
707 l_rec.awt_type_code := P_AWT_Type_Code;
708 l_rec.jurisdiction_type := P_Jurisdiction_Type;
709 l_rec.tax_id := P_Tax_Id;
710 l_rec.tax_name := P_Tax_Name;
711 l_rec.tax_code_combination_id := P_Tax_Code_Comb_id;
712 l_rec.awt_period_type := P_AWT_Period_Type;
713 l_rec.rate_id := null;
714 l_rec.line_amount := P_Line_Amount;
715 l_rec.taxable_base_amount := P_Taxable_Base_Amount;
716 l_rec.revised_tax_base_amount := 0;
717 l_rec.withheld_amount := 0;
718 l_rec.prorated_amount := 0;
719 l_rec.invoice_payment_id := P_Invoice_Payment_Id;
720 l_rec.payment_num := P_Payment_Num; -- By Zmohiudd for bug 1849986
721 l_rec.applicable_flag := 'Y';
722 l_rec.exemption_amount := 0;
723
724 P_Tab_Withhold(P_Current_AWT) := l_rec;
725
726 EXCEPTION
727 WHEN others THEN
728 IF (SQLCODE <> -20001) THEN
729 Fnd_Message.Set_Name ('JL', 'JL_ZZ_AP_DEBUG');
730 Fnd_Message.Set_Token('ERROR', SQLERRM);
731 Fnd_Message.Set_Token('CALLING_SEQUENCE', l_calling_sequence);
732 Fnd_Message.Set_Token('PARAMETERS',
733 ' Current AWT= ' || to_char(P_Current_AWT) ||
734 ', Invoice Id= ' || to_char(P_Invoice_Id) ||
735 ', Distr Line No= ' || to_char(P_Distr_Line_No) ||
736 ', AWT Type Code= ' || P_AWT_Type_Code ||
737 ', Tax Id= ' || to_char(P_Tax_Id) ||
738 ', Tax Name= ' || P_Tax_Name ||
739 ', Tax Code Comb Id= ' || to_char(P_Tax_Code_Comb_Id) ||
740 ', AWT Period Type= ' || P_AWT_Period_Type ||
741 ', Jurisdiction Type= ' || P_Jurisdiction_Type ||
742 ', Line Amount= ' || to_char(P_Line_Amount) ||
743 ', Taxable Base Amount= ' || to_char(P_Taxable_Base_Amount) ||
744 ', Invoice Payment Id= ' || to_char(P_Invoice_Payment_Id));
745 Fnd_Message.Set_Token('DEBUG_INFO', l_debug_info);
746 END IF;
747
748 App_Exception.Raise_Exception;
749
750 END Store_Tax_Name;
751
752
753
754
755 /**************************************************************************
756 * *
757 * Name : Process_Withholding_Name *
758 * Purpose : Process the information for each different tax name for *
759 * a particular withholding tax type. It means: *
760 * 1. Obtains cumulative figures (when applicable) *
761 * 2. Gets the tax rate (checking the effective dates and *
762 * taxable base amount) *
763 * 3. Performs the calculation to obtain the withheld amount *
764 * and applies all the validations that are applicable *
765 * at withholding tax name level. *
766 * *
767 **************************************************************************/
768 PROCEDURE Process_Withholding_Name
769 (P_Vendor_Id IN Number,
770 P_Rec_AWT_Type IN jl_zz_ap_awt_types%ROWTYPE,
771 P_Rec_AWT_Name IN Rec_AWT_CODE,
772 P_Rec_Suppl_AWT_Type IN jl_zz_ap_supp_awt_types%ROWTYPE,
773 P_Rec_Suppl_AWT_Name IN jl_zz_ap_sup_awt_cd%ROWTYPE,
774 P_AWT_Date IN Date,
775 P_Tab_Withhold IN OUT NOCOPY Tab_Withholding,
776 P_Tax_Name_From IN Number,
777 P_Tax_Name_To IN Number,
778 P_Tab_All_Withhold IN OUT NOCOPY Tab_All_Withholding,
779 P_AWT_Success OUT NOCOPY Varchar2)
780 IS
781
782 ------------------------------
783 -- Local variables definition
784 ------------------------------
785 l_withholding_is_required Boolean := TRUE;
786 l_cumulative_gross_amount Number := 0;
787 l_cumulative_withheld_amount Number := 0;
788 l_taxable_base_amount Number := 0;
789 l_subject_amount Number := 0;
790 l_withheld_amount Number := 0;
791 l_debug_info Varchar2(300);
792 l_calling_sequence Varchar2(2000);
793 rec_tax_rate Rec_AWT_Rate;
794 l_cum_exemption_amt Number := 0;
795 l_exemption_amount Number := 0;
796 l_tem_withheld_amount Number := 0;
797
798 BEGIN
799
800 -------------------------------
801 -- Initializes debug variables
802 -------------------------------
803 l_calling_sequence := 'JL_ZZ_AP_WITHHOLDING_PKG' || '.' ||
804 'Process_Withholding_Name';
805
806 -- Debug Information
807 IF (DEBUG_Var = 'Y') THEN
808 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' ');
809 JL_ZZ_AP_EXT_AWT_UTIL.Debug ('PROCEDURE Process_Withholding_Name');
810 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Param: P_Vendor_Id: '||to_char(P_Vendor_Id));
811 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Param: Tax Name: '||P_Rec_AWT_Name.Name);
812 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Param: Zone_Code: '||P_Rec_AWT_Name.Zone_Code);
813 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Param: Min_Tax_Base_Amt: '||to_char(P_Rec_AWT_Name.Min_Tax_Base_Amt));
814 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Param: Min_Withheld_Amt: '||to_char(P_Rec_AWT_Name.Min_Withheld_Amt));
815 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Param: P_Tax_Name_From: '||to_char(P_Tax_Name_From));
816 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Param: P_Tax_Name_To: '||to_char(P_Tax_Name_To));
817 END IF;
818 -- End Debug
819
820 -----------------------------------
821 -- Assumes successfully completion
822 -----------------------------------
823 P_AWT_Success := AWT_SUCCESS;
824
825 ---------------------------------------
826 -- Obtains the cumulative gross amount
827 -- and the cumulative withheld amount
828 ---------------------------------------
829 IF (nvl(P_Rec_AWT_Type.Cumulative_Payment_Flag, 'N') = 'Y' AND
830 nvl(P_Rec_AWT_Name.Cumulative_Payment_Flag, 'N') = 'Y') THEN
831
832 Get_Cumulative_Figures(P_Vendor_Id,
833 P_Rec_AWT_Name.Name,
834 P_Rec_AWT_Name.AWT_Period_Type,
835 P_AWT_Date,
836 l_calling_sequence,
837 l_cumulative_gross_amount,
838 l_cumulative_withheld_amount,
839 P_AWT_Success);
840
841 -- Debug Information
842 IF (DEBUG_Var = 'Y') THEN
843 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Cumulative_Figs P_Rec_AWT_Name.Name = '||P_Rec_AWT_Name.Name);
844 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Cumulative_Figs P_Rec_AWT_Name.AWT_Period_Type = '||P_Rec_AWT_Name.AWT_Period_Type);
845 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Param: l_cumulative_gross_amount: '||to_char(l_cumulative_gross_amount));
846 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Param: l_cumulative_withheld_amount: '||to_char(l_cumulative_withheld_amount));
847 END IF;
848 -- End Debug
849
850 l_cum_exemption_amt := Get_Cumulative_Supp_Exemp
851 (P_Vendor_Id,
852 P_Rec_AWT_Name.Name,
853 P_Rec_AWT_Name.AWT_Period_Type,
854 P_AWT_Date,
855 l_calling_sequence);
856
857 l_cumulative_withheld_amount := l_cumulative_withheld_amount + l_cum_exemption_amt;
858
859 -- Debug Information
860 IF (DEBUG_Var = 'Y') THEN
861 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Cumulative_Figs After Get Exemption Amount');
862 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Param: l_cumulative_withheld_amount: '||to_char(l_cumulative_withheld_amount));
863 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Param: l_cum_exemption_amt: '||to_char(l_cum_exemption_amt));
864 END IF;
865 -- End Debug
866
867 IF (P_AWT_Success <> AWT_SUCCESS) THEN
868 RETURN;
869 END IF;
870
871 END IF;
872
873
874 -------------------------------------------------------
875 -- Calculates the taxable base amount by summing up
876 -- all the base amounts included into the PL/SQL table
877 -------------------------------------------------------
878 FOR i IN P_Tax_Name_From .. P_Tax_Name_To LOOP
879 l_taxable_base_amount := l_taxable_base_amount +
880 P_Tab_Withhold(i).taxable_base_amount;
881 END LOOP;
882
883 -- Debug Information
884 IF (DEBUG_Var = 'Y') THEN
885 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Taxable base amount: l_taxable_base_amount = '||to_char(l_taxable_base_amount));
886 END IF;
887 -- End Debug
888
889 ------------------------------------------------
890 -- Calculates the amount subject to withholding
891 ------------------------------------------------
892
893 l_subject_amount := l_taxable_base_amount + l_cumulative_gross_amount;
894
895 -- Debug Information
896 IF (DEBUG_Var = 'Y') THEN
897 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Amount subject to withh: l_subject_amount = '||to_char(l_subject_amount));
898 END IF;
899 -- End Debug
900
901 ---------------------------------------------------------------------
902 -- Obtains the revised amount subject to withholding, if applicable.
903 -- This procedure is invoked only for Remittance Tax Type.
904 ---------------------------------------------------------------------
905 IF (nvl(P_Rec_AWT_Type.user_defined_formula_flag,'N') = 'Y') THEN
906
907 l_subject_amount := Get_Revised_Tax_Base_Amount
908 (P_Rec_AWT_Name,
909 P_Tab_Withhold,
910 P_Tax_Name_From,
911 P_Tax_Name_To,
912 l_subject_amount,
913 P_Tab_All_Withhold,
914 l_calling_sequence);
915 END IF;
916
917 -------------------------------------
918 -- Applies multilateral contribution
919 -------------------------------------
920 IF (nvl(P_Rec_AWT_Type.multilat_contrib_flag, 'N') = 'Y') THEN
921 IF (nvl(P_Rec_Suppl_AWT_Type.multilat_start_date, P_AWT_Date) <=
922 P_AWT_Date AND
923 nvl(P_Rec_Suppl_AWT_Type.multilat_end_date, P_AWT_Date) >=
924 P_AWT_Date AND
925 P_Rec_Suppl_AWT_Type.multilateral_rate IS NOT NULL) THEN
926 l_subject_amount := l_subject_amount *
927 P_Rec_Suppl_AWT_Type.multilateral_rate / 100;
928
929 -- Debug Information
930 IF (DEBUG_Var = 'Y') THEN
931 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Amount subject to withh for multilateral contribution ');
932 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Amount subject to withh MC: l_subject_amount = '||to_char(l_subject_amount));
933 END IF;
934 -- End Debug
935
936 END IF;
937 END IF;
938
939 ------------------------------------------
940 -- Checks the minimum taxable base amount
941 ------------------------------------------
942 IF (nvl(P_Rec_AWT_Type.min_tax_amount_level, 'N/A') = 'CATEGORY') THEN
943
944 -------------------------------------------------
945 -- Compares with the minimum taxable base amount
946 -------------------------------------------------
947 IF (ABS(l_subject_amount) < P_Rec_AWT_Name.Min_Tax_Base_Amt) THEN
948
949 -- Debug Information
950 IF (DEBUG_Var = 'Y') THEN
951 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' l_subject_amount < P_Rec_AWT_Name.Min_Tax_Base_Amt');
952 END IF;
953 -- End Debug
954
955 l_withholding_is_required := FALSE;
956
957 -------------------------------------------------------
958 -- Obtains the tax rate and its attributes. This rate
959 -- will only be used to be able to insert distribution
960 -- lines with zero withheld amount. The obtained tax
961 -- rate will not be used by the calculation.
962 -------------------------------------------------------
963 Get_Tax_Rate (P_Rec_AWT_Name.Name,
964 P_AWT_Date,
965 P_Rec_AWT_Name.Min_Tax_Base_Amt,
966 l_calling_sequence,
967 rec_tax_rate,
968 P_AWT_Success);
969
970 IF (P_AWT_Success <> AWT_SUCCESS) THEN
971 RETURN;
972 END IF;
973
974 --------------------------------------------
975 -- Subtract the minimum taxable base amount
976 --------------------------------------------
977 ELSIF (nvl(P_Rec_AWT_Name.Adj_Min_Base, 'X') = 'S') THEN
978 l_subject_amount := l_subject_amount -
979 P_Rec_AWT_Name.Min_Tax_Base_Amt;
980
981 -- Debug Information
982 IF (DEBUG_Var = 'Y') THEN
983 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Subtract the minimum taxable base amount');
984 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' l_subject_amount = '||to_char(l_subject_amount));
985 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' P_Rec_AWT_Name.Min_Tax_Base_Amt = '||to_char(P_Rec_AWT_Name.Min_Tax_Base_Amt));
986 END IF;
987 -- End Debug
988
989 END IF;
990
991 END IF;
992
993
994 IF (l_withholding_is_required) THEN
995
996 -------------------------------------------
997 -- Obtains the tax rate and its attributes
998 -- which will be used by the calculation
999 -------------------------------------------
1000
1001 Get_Tax_Rate (P_Rec_AWT_Name.Name,
1002 P_AWT_Date,
1003 l_subject_amount,
1004 l_calling_sequence,
1005 rec_tax_rate,
1006 P_AWT_Success);
1007
1008 -- Debug Information
1009 IF (DEBUG_Var = 'Y') THEN
1010 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Rate Information: Tax_Rate_Id = '||to_char(rec_tax_rate.Tax_Rate_Id));
1011 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Rate Information: Tax_Rate = '||to_char(rec_tax_rate.Tax_Rate));
1012 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Rate Information: Rate_Type = '||rec_tax_rate.Rate_Type);
1013 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Rate Information: Amount_To_Subtract = '||to_char(rec_tax_rate.Amount_To_Subtract));
1014 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Rate Information: Amount_To_Add = '||to_char(rec_tax_rate.Amount_To_Add));
1015 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' ');
1016 END IF;
1017 -- End Debug
1018
1019 IF (P_AWT_Success <> AWT_SUCCESS) THEN
1020 RETURN;
1021 END IF;
1022
1023 ----------------------------------
1024 -- Calculates the withheld amount
1025 ----------------------------------
1026 l_withheld_amount := (l_subject_amount -
1027 nvl(rec_tax_rate.amount_to_subtract, 0)) *
1028 rec_tax_rate.Tax_Rate / 100 +
1029 nvl(rec_tax_rate.amount_to_add, 0);
1030
1031 -- Debug Information
1032 IF (DEBUG_Var = 'Y') THEN
1033 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Withheld amount: '||to_char(l_withheld_amount));
1034 END IF;
1035 -- End Debug
1036
1037 -- Added the changes for the bug 2211795 by zmohiudd..
1038 -----------------------------------------------------------
1039 -- Adjusts the withheld amount by subtracting the withheld
1040 -- amount of the period (only when cumulative payments are
1041 -- applicable)
1042 -----------------------------------------------------------
1043
1044 l_withheld_amount := l_withheld_amount - l_cumulative_withheld_amount;
1045
1046 -- Debug Information
1047 IF (DEBUG_Var = 'Y') THEN
1048 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' l_withheld_amount := l_withheld_amount - l_cumulative_withheld_amount');
1049 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Withheld amount: '||to_char(l_withheld_amount));
1050 END IF;
1051 -- End Debug
1052
1053 -------------------------------------------------
1054 -- Applies supplier exemptions at tax name level
1055 -------------------------------------------------
1056 IF (nvl(P_Rec_AWT_Type.supplier_exempt_level, 'N/A') = 'CATEGORY') THEN
1057 IF (nvl(P_Rec_Suppl_AWT_Name.exemption_start_date, P_AWT_Date) <=
1058 P_AWT_Date AND
1059 nvl(P_Rec_Suppl_AWT_Name.exemption_end_date, P_AWT_Date) >=
1060 P_AWT_Date AND
1061 P_Rec_Suppl_AWT_Name.exemption_rate IS NOT NULL) THEN
1062
1063 l_tem_withheld_amount := l_withheld_amount * (1 -
1064 (P_Rec_Suppl_AWT_Name.exemption_rate / 100));
1065 l_exemption_amount := l_withheld_amount - l_tem_withheld_amount;
1066 l_withheld_amount := l_tem_withheld_amount;
1067
1068 -- Debug Information
1069 IF (DEBUG_Var = 'Y') THEN
1070 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Applies Supplier Exemptions at Tax NAME Level');
1071 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Exemption Start Date = '||to_char(P_Rec_Suppl_AWT_Name.exemption_start_date));
1072 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Exemption End Date = '||to_char(P_Rec_Suppl_AWT_Name.exemption_end_date));
1073 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Exemption Rate = '||to_char(P_Rec_Suppl_AWT_Name.exemption_rate));
1074 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Withheld Amount: '||to_char(l_withheld_amount));
1075 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Exemption Amount: '||to_char(l_exemption_amount));
1076 END IF;
1077 -- End Debug
1078
1079 END IF;
1080 END IF;
1081
1082 -------------------------------------------------
1083 -- Applies supplier exemptions at tax type level
1084 -------------------------------------------------
1085 IF (nvl(P_Rec_AWT_Type.supplier_exempt_level, 'N/A') = 'TYPE') THEN
1086 IF (nvl(P_Rec_Suppl_AWT_Type.exemption_start_date, P_AWT_Date) <=
1087 P_AWT_Date AND
1088 nvl(P_Rec_Suppl_AWT_Type.exemption_end_date, P_AWT_Date) >=
1089 P_AWT_Date AND
1090 P_Rec_Suppl_AWT_Type.exemption_rate IS NOT NULL) THEN
1091
1092 l_tem_withheld_amount := l_withheld_amount * (1 -
1093 (P_Rec_Suppl_AWT_Type.exemption_rate / 100));
1094 l_exemption_amount := l_withheld_amount - l_tem_withheld_amount;
1095 l_withheld_amount := l_tem_withheld_amount;
1096
1097 -- Debug Information
1098 IF (DEBUG_Var = 'Y') THEN
1099 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Applies supplier exemptions at tax TYPE level');
1100 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Exemption Start Date = '||to_char(P_Rec_Suppl_AWT_Type.exemption_start_date));
1101 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Exemption End Date = '||to_char(P_Rec_Suppl_AWT_Type.exemption_end_date));
1102 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Exemption Rate = '||to_char(P_Rec_Suppl_AWT_Type.exemption_rate));
1103 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Withheld Amount: '||to_char(l_withheld_amount));
1104 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Exemption Amount: '||to_char(l_exemption_amount));
1105 END IF;
1106 -- End Debug
1107
1108 END IF;
1109 END IF;
1110
1111 --This part of the code is commented and moved above for bug2211795 by zmohiudd.
1112 /*
1113 -----------------------------------------------------------
1114 -- Adjusts the withheld amount by subtracting the withheld
1115 -- amount of the period (only when cumulative payments are
1116 -- applicable)
1117 -----------------------------------------------------------
1118
1119 l_withheld_amount := l_withheld_amount - l_cumulative_withheld_amount;
1120 */
1121
1122 --------------------------------------
1123 -- Checks the minimum withheld amount
1124 --------------------------------------
1125 IF (nvl(P_Rec_AWT_Type.min_wh_amount_level, 'N/A') = 'CATEGORY') THEN
1126
1127 ---------------------------------------------
1128 -- Compares with the minimum withheld amount
1129 ---------------------------------------------
1130 IF (ABS(l_withheld_amount) < P_Rec_AWT_Name.Min_Withheld_Amt) THEN
1131
1132 -- Debug Information
1133 IF (DEBUG_Var = 'Y') THEN
1134 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Withheld Amount is less that P_Rec_AWT_Name.Min_Withheld_Amt');
1135 END IF;
1136 -- End Debug
1137
1138 l_withholding_is_required := FALSE;
1139
1140 END IF;
1141
1142 END IF;
1143
1144 END IF;
1145
1146
1147 -------------------------------------------------------
1148 -- Updates the amounts contained into the PL/SQL table
1149 -- in order to store the withheld amount (and the used
1150 -- tax rate)
1151 -------------------------------------------------------
1152 FOR i IN P_Tax_Name_From .. P_Tax_Name_To LOOP
1153 P_Tab_Withhold(i).rate_id := rec_tax_rate.Tax_Rate_Id;
1154 IF (l_withholding_is_required) THEN
1155 P_Tab_Withhold(i).withheld_amount := l_withheld_amount;
1156 P_Tab_Withhold(i).applicable_flag := 'Y';
1157 P_Tab_Withhold(i).exemption_amount := l_exemption_amount;
1158 ELSE
1159 P_Tab_Withhold(i).withheld_amount := 0;
1160 P_Tab_Withhold(i).applicable_flag := 'N';
1161 END IF;
1162 END LOOP;
1163
1164 EXCEPTION
1165 WHEN others THEN
1166 IF (SQLCODE <> -20001) THEN
1167 Fnd_Message.Set_Name ('JL', 'JL_ZZ_AP_DEBUG');
1168 Fnd_Message.Set_Token('ERROR', SQLERRM);
1169 Fnd_Message.Set_Token('CALLING_SEQUENCE', l_calling_sequence);
1170 Fnd_Message.Set_Token('PARAMETERS',
1171 ' Vendor Id= ' || to_char(P_Vendor_Id) ||
1172 ', AWT Date= ' || to_char(P_AWT_Date,'YYYY/MM/DD') ||
1173 ', Tax Name From= ' || to_char(P_Tax_Name_From) ||
1174 ', Tax Name To= ' || to_char(P_Tax_Name_To));
1175 Fnd_Message.Set_Token('DEBUG_INFO', l_debug_info);
1176 END IF;
1177
1178 App_Exception.Raise_Exception;
1179
1180 END Process_Withholding_Name;
1181
1182
1183
1184
1185 /**************************************************************************
1186 * *
1187 * Name : Prorate_Withholdings *
1188 * Purpose : Prorates all the withholdings included into the PL/SQL *
1189 * table. *
1190 * *
1191 **************************************************************************/
1192 PROCEDURE Prorate_Withholdings
1193 (P_Tab_Withhold IN OUT NOCOPY Tab_Withholding,
1194 P_Currency_Code IN Varchar2)
1195 IS
1196
1197 ------------------------------
1198 -- Local variables definition
1199 ------------------------------
1200 l_previous_tax_id Number := null;
1201 l_taxable_base_amount Number := 0;
1202 l_initial_tax_name Number := 1;
1203 l_cumulative_wh_amount Number := 0;
1204 l_debug_info Varchar2(300);
1205 l_calling_sequence Varchar2(2000);
1206
1207 BEGIN
1208 -------------------------------
1209 -- Initializes debug variables
1210 -------------------------------
1211 l_calling_sequence := 'JL_ZZ_AP_WITHHOLDING_PKG' || '.' ||
1212 'Prorate_Withholdings';
1213
1214 --------------------------------------------
1215 -- Checks whether there is at least one tax
1216 --------------------------------------------
1217 IF (P_Tab_Withhold.COUNT <= 0) THEN
1218 -- Nothing to do
1219 RETURN;
1220 END IF;
1221
1222 ---------------------------------------------
1223 -- Prorates the withheld amounts by tax name
1224 ---------------------------------------------
1225 l_previous_tax_id := P_Tab_Withhold(1).tax_id;
1226 FOR i IN 1 .. P_Tab_Withhold.COUNT LOOP
1227
1228 IF (P_Tab_Withhold(i).tax_id <> l_previous_tax_id) THEN
1229
1230 ---------------------------------------------
1231 -- Prorates amounts for the current tax name
1232 ---------------------------------------------
1233 l_cumulative_wh_amount := 0;
1234 FOR j IN l_initial_tax_name .. (i - 1) LOOP
1235 IF (l_taxable_base_amount = 0) THEN
1236 P_Tab_Withhold(j).prorated_amount := 0;
1237 ELSE
1238 -------------------------------------------
1239 -- Prorates amount except for the last one
1240 -------------------------------------------
1241 IF (j < (i-1)) THEN
1242 P_Tab_Withhold(j).prorated_amount :=
1243 P_Tab_Withhold(j).taxable_base_amount *
1244 P_Tab_Withhold(j).withheld_amount /
1245 l_taxable_base_amount;
1246
1247 P_Tab_Withhold(j).prorated_amount :=
1248 Ap_Utilities_Pkg.Ap_Round_Currency
1249 (P_Tab_Withhold(j).prorated_amount,
1250 P_Currency_Code);
1251
1252 l_cumulative_wh_amount := l_cumulative_wh_amount +
1253 P_Tab_Withhold(j).prorated_amount;
1254
1255 -----------------------------------------------
1256 -- Calculates prorated amount for the last one
1257 -----------------------------------------------
1258 ELSE
1259 P_Tab_Withhold(j).prorated_amount :=
1260 P_Tab_Withhold(j).withheld_amount -
1261 l_cumulative_wh_amount;
1262 END IF;
1263
1264 END IF;
1265 END LOOP;
1266
1267 ------------------------------------
1268 -- Initializes auxiliary variables
1269 ------------------------------------
1270 l_previous_tax_id := P_Tab_Withhold(i).tax_id;
1271 l_taxable_base_amount := 0;
1272 l_initial_tax_name := i;
1273
1274 END IF;
1275
1276 --------------------------------------------
1277 -- Calculates total taxable base amount by
1278 -- tax name
1279 --------------------------------------------
1280 l_taxable_base_amount := l_taxable_base_amount +
1281 P_Tab_Withhold(i).taxable_base_amount;
1282 END LOOP;
1283
1284 --------------------------
1285 -- Prorates last tax name
1286 --------------------------
1287 l_cumulative_wh_amount := 0;
1288 FOR j IN l_initial_tax_name .. P_Tab_Withhold.COUNT LOOP
1289 IF (l_taxable_base_amount = 0) THEN
1290 P_Tab_Withhold(j).prorated_amount := 0;
1291 ELSE
1292 -------------------------------------------
1293 -- Prorates amount except for the last one
1294 -------------------------------------------
1295 IF (j < P_Tab_Withhold.COUNT) THEN
1296 P_Tab_Withhold(j).prorated_amount :=
1297 P_Tab_Withhold(j).taxable_base_amount *
1298 P_Tab_Withhold(j).withheld_amount /
1299 l_taxable_base_amount;
1300
1301 P_Tab_Withhold(j).prorated_amount :=
1302 Ap_Utilities_Pkg.Ap_Round_Currency
1303 (P_Tab_Withhold(j).prorated_amount,
1304 P_Currency_Code);
1305
1306 l_cumulative_wh_amount := l_cumulative_wh_amount +
1307 P_Tab_Withhold(j).prorated_amount;
1308
1309 -----------------------------------------------
1310 -- Calculates prorated amount for the last one
1311 -----------------------------------------------
1312 ELSE
1313 P_Tab_Withhold(j).prorated_amount :=
1314 P_Tab_Withhold(j).withheld_amount -
1315 l_cumulative_wh_amount;
1316 END IF;
1317
1318 END IF;
1319 END LOOP;
1320
1321 EXCEPTION
1322 WHEN others THEN
1323 IF (SQLCODE <> -20001) THEN
1324 Fnd_Message.Set_Name ('JL', 'JL_ZZ_AP_DEBUG');
1325 Fnd_Message.Set_Token('ERROR', SQLERRM);
1326 Fnd_Message.Set_Token('CALLING_SEQUENCE', l_calling_sequence);
1327 Fnd_Message.Set_Token('PARAMETERS',
1328 ' Currency Code= ' || P_Currency_Code);
1329 Fnd_Message.Set_Token('DEBUG_INFO', l_debug_info);
1330 END IF;
1331
1332 App_Exception.Raise_Exception;
1333
1334 END Prorate_Withholdings;
1335
1336
1337
1338
1339 /**************************************************************************
1340 * *
1341 * Name : Store_Into_Temporary_Table *
1342 * Purpose : Transfers all the withholding taxes stored into the PL/SQL*
1343 * table to the temporary table (AP_AWT_TEMP_DISTRIBUTIONS) *
1344 * *
1345 **************************************************************************/
1346 PROCEDURE Store_Into_Temporary_Table
1347 (P_Tab_Withhold IN Tab_Withholding,
1348 P_Vendor_Id IN Number,
1349 P_AWT_Date IN Date,
1350 P_GL_Period_Name IN Varchar2,
1351 P_Base_Currency_Code IN Varchar2,
1352 P_Revised_Amount_Flag IN Boolean,
1353 P_Prorated_Amount_Flag IN Boolean,
1354 P_Zero_WH_Applicable IN Boolean,
1355 P_Handle_Bucket IN Boolean,
1356 P_AWT_Success OUT NOCOPY Varchar2,
1357 P_Last_Updated_By IN Number Default null,
1358 P_Last_Update_Login IN Number Default null,
1359 P_Program_Application_Id IN Number Default null,
1360 P_Program_Id IN Number Default null,
1361 P_Request_Id IN Number Default null,
1362 P_Calling_Module IN Varchar2 Default null,
1363 P_Checkrun_Name IN Varchar2 Default null,
1364 P_Checkrun_id IN Number Default null,
1365 P_Payment_Num IN Number Default null,
1366 P_Global_Attr_Category IN Varchar2 Default null,
1367 P_NIT_Number IN Varchar2 Default null)
1368 IS
1369
1370 ------------------------------
1371 -- Local variables definition
1372 ------------------------------
1373 l_invoice_id Number := null;
1374 l_tax_id Number := null;
1375 l_tax_name Varchar2(15);
1376 l_tax_code_comb_id Number;
1377 l_awt_period_type Varchar2(15);
1378 l_awt_period_name Varchar2(15);
1379 l_tax_rate_id Number;
1380 l_gross_amount Number := 0;
1381 l_withheld_amount Number := 0;
1382 l_applicable_flag Varchar2(10);
1383 l_invoice_payment_id Number;
1384 l_handle_bucket Varchar2(10);
1385 l_debug_info Varchar2(300);
1386 l_calling_sequence Varchar2(1000);
1387 l_temerr Varchar2(100);
1388 -- By zmohiudd for 1849986
1389 l_payment_num Number;
1390 l_exemption_amount Number;
1391 l_awt_related_id Number; -- Bug 6347255
1392 BEGIN
1393 -------------------------------
1394 -- Initializes debug variables
1395 -------------------------------
1396 l_calling_sequence := 'JL_ZZ_AP_WITHHOLDING_PKG' || '.' ||
1397 'Store_Into_Temporary_Table';
1398
1399 -- Debug Information
1400 IF (DEBUG_Var = 'Y') THEN
1401 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' ');
1402 JL_ZZ_AP_EXT_AWT_UTIL.Debug ('PROCEDURE Store_Into_Temporary_Table');
1403 JL_ZZ_AP_EXT_AWT_UTIL.Print_Tax_Names(P_Tab_Withhold);
1404 END IF;
1405 -- End Debug
1406
1407 -----------------------------------
1408 -- Assumes successfully completion
1409 -----------------------------------
1410 P_AWT_Success := AWT_SUCCESS;
1411
1412 ----------------------------------
1413 -- Defines the initial Save Point
1414 ----------------------------------
1415 SAVEPOINT Before_Inserting_Lines;
1416
1417 --------------------------------------------
1418 -- Checks whether there is at least one tax
1419 --------------------------------------------
1420 IF (P_Tab_Withhold.COUNT <= 0) THEN
1421 -- Nothing to do
1422 RETURN;
1423 END IF;
1424
1425 ---------------------------
1426 -- Sets handle bucket flag
1427 ---------------------------
1428 IF (P_Handle_Bucket) THEN
1429 l_handle_bucket := 'Y';
1430 ELSE
1431 l_handle_bucket := 'N';
1432 END IF;
1433
1434 -----------------------------------
1435 -- Initializes auxiliary variables
1436 -----------------------------------
1437 l_invoice_id := P_Tab_Withhold(1).invoice_id;
1438 l_tax_id := P_Tab_Withhold(1).tax_id;
1439 l_tax_name := P_Tab_Withhold(1).tax_name;
1440 l_tax_code_comb_id := P_Tab_Withhold(1).tax_code_combination_id;
1441 l_awt_period_type := P_Tab_Withhold(1).awt_period_type;
1442 l_awt_related_id := P_Tab_Withhold(1).invoice_distribution_id; -- Bug 6347255
1443 l_tax_rate_id := P_Tab_Withhold(1).rate_id;
1444 l_invoice_payment_id := P_Tab_Withhold(1).invoice_payment_id;
1445 l_applicable_flag := P_Tab_Withhold(1).applicable_flag;
1446 l_exemption_amount := P_Tab_Withhold(1).exemption_amount;
1447 l_awt_period_name := Get_Period_Name(l_tax_name,
1448 l_awt_period_type,
1449 P_AWT_Date,
1450 l_calling_sequence,
1451 P_AWT_Success);
1452
1453 -- By zmohiudd for Bug1849986
1454 l_payment_num := P_Tab_Withhold(1).payment_num;
1455
1456 IF (P_AWT_Success <> AWT_SUCCESS) THEN
1457 RETURN;
1458 END IF;
1459
1460
1461 ----------------------------
1462 -- Initializes gross amount
1463 ----------------------------
1464 IF (P_Revised_Amount_Flag) THEN
1465 l_gross_amount := P_Tab_Withhold(1).revised_tax_base_amount;
1466
1467 -- Debug Information
1468 IF (DEBUG_Var = 'Y') THEN
1469 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' ');
1470 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Initializes Gross Amount if P_Revised_Amount_Flag First Record');
1471 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Gross Amount = '||to_char(l_gross_amount));
1472 END IF;
1473 -- End Debug
1474
1475 ELSE
1476 l_gross_amount := 0;
1477
1478 -- Debug Information
1479 IF (DEBUG_Var = 'Y') THEN
1480 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Initializes Gross Amount to Zero First Record ');
1481 END IF;
1482 -- End Debug
1483
1484 END IF;
1485
1486 -------------------------------
1487 -- Initializes withheld amount
1488 -------------------------------
1489 IF (P_Prorated_Amount_Flag) THEN
1490 l_withheld_amount := 0;
1491 ELSE
1492 l_withheld_amount := P_Tab_Withhold(1).withheld_amount;
1493 END IF;
1494
1495 ------------------------------------------------------------
1496 -- Inserts temporary distribution lines by invoice/tax name
1497 ------------------------------------------------------------
1498 FOR i IN 1 .. P_Tab_Withhold.COUNT LOOP
1499 IF (P_Tab_Withhold(i).invoice_id <> l_invoice_id OR
1500 P_Tab_Withhold(i).tax_id <> l_tax_id) THEN
1501
1502 -----------------------------------------------------
1503 -- Inserts temporary distribution line for
1504 -- current invoice/tax name
1505 -- Changed from dynamic to static call. Bug# 2107329
1506 -----------------------------------------------------
1507 IF (P_Zero_WH_Applicable OR l_applicable_flag = 'Y') THEN
1508
1509 -- Bug 5257162
1510 IF l_withheld_amount = 0 and l_exemption_amount > 0 THEN
1511 l_exemption_amount:= 0;
1512 END IF;
1513
1514 Ap_Calc_Withholding_Pkg.Insert_Temp_Distribution
1515 (l_invoice_id,
1516 P_Vendor_Id,
1517 -- By zmohiudd Bug1849986 changed P_Payment_Num to l_Payment_Num
1518 nvl(l_Payment_Num,P_PAYMENT_NUM),
1519 -1, -- Group ID
1520 l_tax_name,
1521 l_tax_code_comb_id,
1522 l_gross_amount,
1523 l_withheld_amount,
1524 P_AWT_Date,
1525 P_GL_Period_Name,
1526 l_awt_period_type,
1527 l_awt_period_name,
1528 -- l_awt_related_id, Commented for bug 6885098
1529 P_Checkrun_Name,
1530 l_tax_rate_id,
1531 null,
1532 P_Base_Currency_Code,
1533 P_Base_Currency_Code,
1534 null, -- Offset
1535 l_calling_sequence,
1536 l_handle_bucket,
1537 P_Last_Updated_By,
1538 P_Last_Update_Login,
1539 P_Program_Application_Id,
1540 P_Program_Id,
1541 P_Request_Id,
1542 P_Calling_Module,
1543 l_invoice_payment_id,
1544 null, -- Invoice exchange rate
1545 P_Global_Attr_Category, -- Global attribute category
1546 null, -- Global attribute1
1547 P_NIT_Number, -- Global Attribute2
1548 null, -- Global Attribute3
1549 null, -- Global Attribute4
1550 l_exemption_amount, -- Global Attribute5
1551 P_checkrun_id => p_checkrun_id,
1552 P_awt_related_id => l_awt_related_id); --Added for 6885098
1553
1554
1555 END IF; -- P_Zero_WH_Applicable
1556
1557 -----------------------------------
1558 -- Reinitializes auxiliary variables
1559 -----------------------------------
1560 l_invoice_id := P_Tab_Withhold(i).invoice_id;
1561 l_tax_id := P_Tab_Withhold(i).tax_id;
1562 l_tax_name := P_Tab_Withhold(i).tax_name;
1563 l_tax_code_comb_id := P_Tab_Withhold(i).tax_code_combination_id;
1564 l_awt_period_type := P_Tab_Withhold(i).awt_period_type;
1565 l_tax_rate_id := P_Tab_Withhold(i).rate_id;
1566 l_invoice_payment_id := P_Tab_Withhold(i).invoice_payment_id;
1567 l_applicable_flag := P_Tab_Withhold(i).applicable_flag;
1568 l_exemption_amount := P_Tab_Withhold(i).exemption_amount;
1569 l_awt_related_id := P_Tab_Withhold(i).invoice_distribution_id; --Bug 6347225
1570 l_awt_period_name := Get_Period_Name(l_tax_name,
1571 l_awt_period_type,
1572 P_AWT_Date,
1573 l_calling_sequence,
1574 P_AWT_Success);
1575 -- By zmohiudd for Bug1849986
1576 l_payment_num := P_Tab_Withhold(i).payment_num;
1577 IF (P_AWT_Success <> AWT_SUCCESS) THEN
1578 ROLLBACK TO Before_Inserting_Lines;
1579 RETURN;
1580 END IF;
1581
1582 ----------------------------
1583 -- Initializes gross amount
1584 ----------------------------
1585 IF (P_Revised_Amount_Flag) THEN
1586 l_gross_amount := P_Tab_Withhold(i).revised_tax_base_amount;
1587
1588 -- Debug Information
1589 IF (DEBUG_Var = 'Y') THEN
1590 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' ');
1591 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Initializes Gross Amount if P_Revised_Amount_Flag ');
1592 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Gross Amount = '||to_char(l_gross_amount));
1593 END IF;
1594 -- End Debug
1595 ELSE
1596 l_gross_amount := 0;
1597
1598 -- Debug Information
1599 IF (DEBUG_Var = 'Y') THEN
1600 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Initializes Gross Amount to Zero First Record ');
1601 END IF;
1602 -- End Debug
1603 END IF;
1604
1605 -------------------------------
1606 -- Initializes withheld amount
1607 -------------------------------
1608 IF (P_Prorated_Amount_Flag) THEN
1609 l_withheld_amount := 0;
1610 ELSE
1611 l_withheld_amount := P_Tab_Withhold(i).withheld_amount;
1612 END IF;
1613 END IF;
1614
1615 ---------------------------
1616 -- Calculates gross amount
1617 ---------------------------
1618 IF (NOT P_Revised_Amount_Flag) THEN
1619 l_gross_amount := l_gross_amount +
1620 P_Tab_Withhold(i).taxable_base_amount;
1621
1622 -- Debug Information
1623 IF (DEBUG_Var = 'Y') THEN
1624 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' ');
1625 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Calculates gross amount if NOT P_Revised_Amount_Flag');
1626 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Gross Amount = '||to_char(l_gross_amount));
1627 END IF;
1628 -- End Debug
1629
1630 END IF;
1631
1632 ------------------------------
1633 -- Calculates withheld amount
1634 ------------------------------
1635 IF (P_Prorated_Amount_Flag) THEN
1636 l_withheld_amount := l_withheld_amount +
1637 P_Tab_Withhold(i).prorated_amount;
1638 END IF;
1639
1640 END LOOP;
1641
1642 ------------------------------------------------------
1643 -- Inserts temporary distribution line for
1644 -- last invoice/tax name
1645 -- Changed from dynamic to static call. Bug# 2107329
1646 ------------------------------------------------------
1647 IF (P_Zero_WH_Applicable OR l_applicable_flag = 'Y') THEN
1648
1649 -- Bug 5257162
1650 IF l_withheld_amount = 0 and l_exemption_amount > 0 THEN
1651 l_exemption_amount:= 0;
1652 END IF;
1653
1654 Ap_Calc_Withholding_Pkg.Insert_Temp_Distribution
1655 (l_invoice_id,
1656 P_Vendor_Id,
1657 -- By zmohiudd Bug1849986 changed P_Payment_Num to l_Payment_Num
1658 nvl(l_Payment_Num,P_PAYMENT_NUM),
1659 -1, -- Group ID
1660 l_tax_name,
1661 l_tax_code_comb_id,
1662 l_gross_amount,
1663 l_withheld_amount,
1664 P_AWT_Date,
1665 P_GL_Period_Name,
1666 l_awt_period_type,
1667 l_awt_period_name,
1668 -- l_awt_related_id,Commented for bug 6885098
1669 P_Checkrun_Name,
1670 l_tax_rate_id,
1671 null,
1672 P_Base_Currency_Code,
1673 P_Base_Currency_Code,
1674 null, -- offset
1675 l_calling_sequence,
1676 l_handle_bucket,
1677 P_Last_Updated_By,
1678 P_Last_Update_Login,
1679 P_Program_Application_Id,
1680 P_Program_Id,
1681 P_Request_Id,
1682 P_Calling_Module,
1683 l_invoice_payment_id,
1684 null, -- Invoice exchange rate
1685 P_Global_Attr_Category, -- Global attribute category
1686 null, -- Global attribute1
1687 P_NIT_Number, -- Global Attribute2
1688 null, -- Global Attribute3
1689 null, -- Global Attribute4
1690 l_exemption_amount, -- Global Attribute5
1691 P_checkrun_id => p_checkrun_id,
1692 P_awt_related_id => l_awt_related_id); --Added the code for bug 6885098
1693
1694 END IF;
1695
1696 EXCEPTION
1697 WHEN others THEN
1698 IF (SQLCODE <> -20001) THEN
1699 Fnd_Message.Set_Name ('JL', 'JL_ZZ_AP_DEBUG');
1700 Fnd_Message.Set_Token('ERROR', SQLERRM);
1701 Fnd_Message.Set_Token('CALLING_SEQUENCE', l_calling_sequence);
1702 Fnd_Message.Set_Token('PARAMETERS',
1703 ' Vendor Id= ' || to_char(P_Vendor_Id) ||
1704 ', AWT Date= ' || to_char(P_AWT_Date,'YYYY/MM/DD') ||
1705 ', GL Period Name= ' || P_GL_Period_Name ||
1706 ', Base Currency Code= ' || P_Base_Currency_Code ||
1707 ', Revised Amount Flag= ' || Bool_To_Char(P_Revised_Amount_Flag) ||
1708 ', Prorated Amount Flag= '|| Bool_To_Char(P_Prorated_Amount_Flag) ||
1709 ', Zero WH Applicable= ' || Bool_To_Char(P_Zero_WH_Applicable) ||
1710 ', Handle Bucket= ' || Bool_To_Char(P_Handle_Bucket) ||
1711 ', Calling Module= ' || P_Calling_Module ||
1712 ', Checkrun Name= ' || P_Checkrun_Name ||
1713 ', Payment Num= ' || to_char(P_Payment_Num) ||
1714 ', Global Attr Category= '|| P_Global_Attr_Category ||
1715 ', NIT Number= ' || P_NIT_Number);
1716 Fnd_Message.Set_Token('DEBUG_INFO', l_debug_info);
1717 END IF;
1718
1719 App_Exception.Raise_Exception;
1720
1721 END Store_Into_Temporary_Table;
1722
1723
1724
1725
1726 /**************************************************************************
1727 * *
1728 * Name : Process_Withholding_Type *
1729 * Purpose : Process the information for each different withholding *
1730 * tax type included within the payment. *
1731 * *
1732 **************************************************************************/
1733 PROCEDURE Process_Withholding_Type
1734 (P_Rec_AWT_Type IN jl_zz_ap_awt_types%ROWTYPE,
1735 P_Rec_Suppl_AWT_Type IN jl_zz_ap_supp_awt_types%ROWTYPE,
1736 P_AWT_Date IN Date,
1737 P_Currency_Code IN Varchar2,
1738 P_Tab_Withhold IN OUT NOCOPY Tab_Withholding)
1739 IS
1740
1741 ------------------------------
1742 -- Local variables definition
1743 ------------------------------
1744 l_previous_wh_amount Number;
1745 l_withheld_amount Number := 0;
1746 l_previous_tax_id Number := null;
1747 l_withholding_is_required Boolean := TRUE;
1748 l_debug_info Varchar2(300);
1749 l_calling_sequence Varchar2(2000);
1750
1751 BEGIN
1752 -------------------------------
1753 -- Initializes debug variables
1754 -------------------------------
1755 l_calling_sequence := 'JL_ZZ_AP_WITHHOLDING_PKG' || '.' ||
1756 'Process_Withholding_Type';
1757
1758 -------------------------------------------
1759 -- Calculates the withheld amount for the
1760 -- withholding tax type
1761 -------------------------------------------
1762 FOR i IN 1 .. P_Tab_Withhold.COUNT LOOP
1763 IF (l_previous_tax_id IS NULL OR
1764 P_Tab_Withhold(i).tax_id <> l_previous_tax_id) THEN
1765 l_withheld_amount := l_withheld_amount +
1766 P_Tab_Withhold(i).withheld_amount;
1767 l_previous_tax_id := P_Tab_Withhold(i).tax_id;
1768 END IF;
1769 END LOOP;
1770
1771 ---------------------------------
1772 -- Store current withheld amount
1773 ---------------------------------
1774 l_previous_wh_amount := l_withheld_amount;
1775
1776 --------------------------------------
1777 -- Checks the minimum withheld amount
1778 --------------------------------------
1779 IF (nvl(P_Rec_AWT_Type.min_wh_amount_level, 'N/A') = 'TYPE') THEN
1780 IF (ABS(l_withheld_amount) < P_Rec_AWT_Type.min_wh_amount) THEN
1781 l_withholding_is_required := FALSE;
1782 END IF;
1783 END IF;
1784
1785 ----------------------------------------------------
1786 -- Updates the tax name information stored into the
1787 -- PL/SQL table
1788 ----------------------------------------------------
1789 IF (NOT l_withholding_is_required) THEN
1790 FOR i IN 1 .. P_Tab_Withhold.COUNT LOOP
1791 P_Tab_Withhold(i).withheld_amount := 0;
1792 P_Tab_Withhold(i).applicable_flag := 'N';
1793 END LOOP;
1794 ELSE
1795 Update_Withheld_Amount (l_previous_wh_amount,
1796 l_withheld_amount,
1797 P_Currency_Code,
1798 l_calling_sequence,
1799 P_Tab_Withhold);
1800 END IF;
1801
1802 EXCEPTION
1803 WHEN others THEN
1804 IF (SQLCODE <> -20001) THEN
1805 Fnd_Message.Set_Name ('JL', 'JL_ZZ_AP_DEBUG');
1806 Fnd_Message.Set_Token('ERROR', SQLERRM);
1807 Fnd_Message.Set_Token('CALLING_SEQUENCE', l_calling_sequence);
1808 Fnd_Message.Set_Token('PARAMETERS',
1809 ' AWT Date= ' || to_char(P_AWT_Date,'YYYY/MM/DD') ||
1810 ', Currency Code= ' || P_Currency_Code);
1811 Fnd_Message.Set_Token('DEBUG_INFO', l_debug_info);
1812 END IF;
1813
1814 App_Exception.Raise_Exception;
1815
1816 END Process_Withholding_Type;
1817
1818
1819
1820
1821 /**************************************************************************
1822 * *
1823 * Name : Store_Prorated_Withholdings *
1824 * Purpose : Transfers the Prorated Withholding details, from one *
1825 * PL/SQL table to another *
1826 * *
1827 **************************************************************************/
1828 PROCEDURE Store_Prorated_Withholdings
1829 (P_Tab_Withhold IN Tab_Withholding,
1830 P_Tab_All_Withhold IN OUT NOCOPY Tab_All_Withholding)
1831 IS
1832
1833 ------------------------------
1834 -- Local variables definition
1835 ------------------------------
1836 l_last_rec_number Number:=0;
1837 pos Number;
1838 tab Tab_Withholding := P_Tab_Withhold;
1839 l_debug_info Varchar2(300);
1840 l_calling_sequence Varchar2(2000);
1841
1842 BEGIN
1843
1844 -------------------------------
1845 -- Initializes debug variables
1846 -------------------------------
1847 l_calling_sequence := 'JL_ZZ_AP_WITHHOLDING_PKG' || '.' ||
1848 'Store_Prorated_Withholdings';
1849
1850 ------------------------------------------------------
1851 -- Obtains the last record number of the PL/SQL table
1852 ------------------------------------------------------
1853 l_last_rec_number := P_Tab_All_Withhold.COUNT;
1854 pos := l_last_rec_number;
1855
1856 -----------------------------------------
1857 -- Stores the information into the table
1858 -----------------------------------------
1859 FOR i IN 1..tab.COUNT LOOP
1860 pos := pos + 1;
1861 P_Tab_All_Withhold(pos).invoice_id :=
1862 tab(i).invoice_id;
1863 P_Tab_All_Withhold(pos).invoice_distribution_id :=
1864 tab(i).invoice_distribution_id;
1865 P_Tab_All_Withhold(pos).awt_type_code :=
1866 tab(i).awt_type_code;
1867 P_Tab_All_Withhold(pos).tax_id :=
1868 tab(i).tax_id;
1869 P_Tab_All_Withhold(pos).jurisdiction_type :=
1870 tab(i).jurisdiction_type;
1871 P_Tab_All_Withhold(pos).prorated_amount :=
1872 tab(i).prorated_amount;
1873 END LOOP;
1874
1875 EXCEPTION
1876 WHEN others THEN
1877 IF (SQLCODE <> -20001) THEN
1878 Fnd_Message.Set_Name ('JL', 'JL_ZZ_AP_DEBUG');
1879 Fnd_Message.Set_Token('ERROR', SQLERRM);
1880 Fnd_Message.Set_Token('CALLING_SEQUENCE', l_calling_sequence);
1881 Fnd_Message.Set_Token('PARAMETERS', null);
1882 Fnd_Message.Set_Token('DEBUG_INFO', l_debug_info);
1883 END IF;
1884
1885 App_Exception.Raise_Exception;
1886
1887 END Store_Prorated_Withholdings;
1888
1889
1890
1891
1892 /**************************************************************************
1893 * *
1894 * Name : Print_Tax_Names *
1895 * Purpose : This procedure shows all the elements of the PL/SQL table *
1896 * (just for debug purposes) *
1897 * *
1898 **************************************************************************/
1899
1900 PROCEDURE Print_Tax_Names (P_Tab_Payment_Wh IN Tab_Withholding)
1901 IS
1902 tab Tab_Withholding := P_Tab_Payment_Wh;
1903 pos Number;
1904
1905
1906 BEGIN
1907 NULL;
1908 END Print_Tax_Names;
1909
1910
1911
1912
1913 /**************************************************************************
1914 * *
1915 * Name : Jl_Zz_Ap_Extended_Match *
1916 * Purpose : Regional Extended Routine for Matching *
1917 * *
1918 **************************************************************************/
1919 --
1920 -- Bug 4559478 : R12 KI
1921 --
1922 PROCEDURE Jl_Zz_Ap_Extended_Match
1923 (P_Credit_Id IN Number,
1924 P_Invoice_Id IN Number Default null,
1925 -- Bug 4559478
1926 P_Inv_Line_Num IN Number Default null,
1927 P_Distribution_id IN Number Default null,
1928 P_Parent_Dist_ID IN Number Default null)
1929 IS
1930
1931 ------------------------------
1932 -- Local variables definition
1933 ------------------------------
1934 l_parent_dist_num Varchar2(100);
1935
1936 -- Bug 4559478
1937 -- l_dist_line_num ap_invoice_distributions.invoice_distribution_id%TYPE;
1938 l_inv_dist_id ap_invoice_distributions.invoice_distribution_id%TYPE;
1939
1940 l_po_distribution_id ap_invoice_distributions.po_distribution_id%TYPE;
1941 l_ship_to_location_id po_line_locations.ship_to_location_id%TYPE;
1942 l_debug_info Varchar2(300);
1943 l_calling_sequence Varchar2(2000);
1944 v_country_code Varchar2(100);
1945
1946 l_ou_id Number;
1947
1948 ---------------
1949 -- WHO Columns
1950 ---------------
1951 v_last_update_by NUMBER;
1952 v_last_update_login NUMBER;
1953
1954 --------------------------------------------------------
1955 -- Cursor to select all distribution lines for which the
1956 -- tax names has to be associated
1957 ---------------------------------------------------------
1958 --
1959 -- Bug 4559478
1960 --
1961 /*
1962 CURSOR c_distributions(P_Credit_Id Number,
1963 P_Inv_Line_Num Number)
1964 IS
1965 SELECT apid.invoice_distribution_id,
1966 apid.po_distribution_id,
1967 apid.global_attribute20 -- What is gdf20?
1968 FROM ap_invoice_distributions apid
1969 WHERE apid.invoice_id = P_Credit_Id
1970 AND apid.invoice_line_number = P_Inv_Line_Num;
1971 */
1972
1973 BEGIN
1974 -------------------------------
1975 -- Initializes debug variables
1976 -------------------------------
1977 l_calling_sequence := 'JL_ZZ_AP_WITHHOLDING_PKG' || '.' ||
1978 'Jl_Zz_Ap_Extended_Match';
1979
1980 -------------------------------------------------------
1981 -- Get the information of WHO Columns from FND_GLOBAL
1982 -------------------------------------------------------
1983 v_last_update_by := FND_GLOBAL.User_ID;
1984 v_last_update_login := FND_GLOBAL.Login_Id;
1985
1986 --------------------------------------------------------------------
1987 -- If distribution lines are created by matching against an invoice
1988 --------------------------------------------------------------------
1989 l_debug_info := 'Distribution lines are created by matching ' ||
1990 'against an invoice';
1991
1992
1993 /* No need to loop if all parameters are passed.
1994
1995 -- Bug 4559478
1996 -- Passing P_Inv_Line_Num in stead of P_Start_Dist_Line_Num
1997 OPEN c_distributions(nvl(P_Credit_Id,P_Invoice_Id),
1998 P_Inv_Line_Num);
1999
2000 ----------------------------------------
2001 -- Loop for each distribution obtained
2002 ----------------------------------------
2003 LOOP
2004
2005 FETCH c_distributions INTO l_inv_dist_id,
2006 l_po_distribution_id,
2007 l_parent_dist_num; -- ap_dist.gdf20
2008 EXIT WHEN c_distributions%NOTFOUND;
2009 */
2010 ---------------------------------------------------------------
2011 -- Creates lines in JL_ZZ_AP_INV_DIS_WH_ALL table for the
2012 -- distribution lines created in ap_invoice_distributions
2013 ---------------------------------------------------------------
2014 IF (P_Parent_Dist_ID IS NOT NULL) THEN
2015
2016 ----------------------------------------------------------
2017 -- Copies the tax names from the parent distribution line
2018 ----------------------------------------------------------
2019 INSERT INTO jl_zz_ap_inv_dis_wh (
2020 inv_distrib_awt_id
2021 ,invoice_id
2022 -- Bug 4559478
2023 ,invoice_distribution_id
2024 ,distribution_line_number
2025 ,supp_awt_code_id
2026 ,created_by
2027 ,creation_date
2028 ,last_updated_by
2029 ,last_update_date
2030 ,last_update_login
2031 )
2032 SELECT
2033 jl_zz_ap_inv_dis_wh_s.nextval
2034 ,P_Credit_Id
2035 ,P_distribution_id
2036 -- Bug 4559478 : -99 for distribution_line_number
2037 ,-99
2038 ,jlid.Supp_Awt_Code_Id
2039 ,v_last_update_by
2040 ,sysdate
2041 ,v_last_update_by
2042 ,sysdate
2043 ,v_last_update_login
2044 FROM
2045 jl_zz_ap_inv_dis_wh jlid
2046 WHERE jlid.invoice_distribution_id = P_Parent_Dist_ID
2047 AND jlid.invoice_id = P_Invoice_Id;
2048
2049
2050 ELSE
2051 /*
2052 ----------------------------------------------------------
2053 -- Obtains the ship to location for the distribution line
2054 ----------------------------------------------------------
2055 SELECT poll.ship_to_location_id
2056 INTO l_ship_to_location_id
2057 FROM po_line_locations poll
2058 WHERE line_location_id = (SELECT line_location_id
2059 FROM po_distributions
2060 WHERE po_distribution_id = l_po_distribution_id);
2061 */
2062
2063 ----------------------------------------------------------------
2064 -- Get the country code to update the global attribute category
2065 ----------------------------------------------------------------
2066 --FND_PROFILE.GET('ORG_ID',l_ou_id);
2067 --R12: Commented to overcome build errors. These changes still pending to be
2068 --properly implemented.
2069 v_country_code := JG_ZZ_SHARED_PKG.GET_COUNTRY(l_ou_id);
2070
2071 -----------------------------------------------------------
2072 -- Updates the distribution line to hold ship to location
2073 -- for defaulting the tax names
2074 -----------------------------------------------------------
2075 UPDATE ap_invoice_distributions
2076 SET
2077 -- global_attribute3 = l_ship_to_location_id,
2078 global_attribute_category = decode(v_country_code,'AR','JL.AR.APXINWKB.DISTRIBUTIONS',
2079 'CO','JL.CO.APXINWKB.DISTRIBUTIONS','')
2080 where invoice_id = nvl(P_Credit_Id,P_Invoice_Id) -- Bug 2906487, Added an nvl clause.
2081 and invoice_distribution_id = P_distribution_id;
2082
2083 ---------------------------------------------------------------
2084 -- Defaults the tax names for the distributions created.
2085 ---------------------------------------------------------------
2086 --
2087 -- Bug 4559478
2088 --
2089 Jl_Zz_Ap_Awt_Default_Pkg.Supp_Wh_Def(
2090 P_Invoice_Id,
2091 P_Inv_Line_Num,
2092 P_Distribution_id,
2093 null,
2094 null -- check if we need should pass parent id
2095 );
2096
2097 END IF;
2098
2099 -- END LOOP;
2100
2101 -- CLOSE c_distributions;
2102
2103 EXCEPTION
2104 WHEN others THEN
2105 IF (SQLCODE <> -20001) THEN
2106 Fnd_Message.Set_Name ('JL', 'JL_ZZ_AP_DEBUG');
2107 Fnd_Message.Set_Token('ERROR', SQLERRM);
2108 Fnd_Message.Set_Token('CALLING_SEQUENCE', l_calling_sequence);
2109 Fnd_Message.Set_Token('PARAMETERS',
2110 ' Credit Id = ' || to_char(P_Credit_Id) ||
2111 ', Invoice_Id = ' || to_char(P_Invoice_Id) ||
2112 -- Bug 4559478
2113 -- ', Start Dist Line Num = ' || to_char(P_Start_Dist_Line_Num));
2114 ', Inv Line Num = ' || to_char(P_Inv_Line_Num));
2115 Fnd_Message.Set_Token('DEBUG_INFO', l_debug_info);
2116 END IF;
2117
2118 App_Exception.Raise_Exception;
2119
2120 END Jl_Zz_Ap_Extended_Match;
2121
2122
2123
2124 /**************************************************************************
2125 * *
2126 * Name : Jl_Zz_Ap_Ext_Insert_Dist *
2127 * Purpose : Regional Extended Routine for Insertion *
2128 * *
2129 **************************************************************************/
2130 PROCEDURE Jl_Zz_Ap_Ext_Insert_Dist
2131 (P_Invoice_Id IN Number,
2132 P_Invoice_Distribution_id IN Number, -- Add new Column
2133 P_Distribution_Line_Number IN Number,
2134 P_Line_Type IN Varchar2,
2135 P_GL_Date IN Date,
2136 P_Period_Name IN Varchar2,
2137 P_Type_1099 IN Varchar2,
2138 P_Income_Tax_Region IN Varchar2,
2139 P_Amount IN Number,
2140 P_Tax_Code_ID IN Number, -- Add new Column
2141 P_Code_Combination_Id IN Number,
2142 P_PA_Quantity IN Number,
2143 P_Description IN Varchar2,
2144 P_tax_recoverable_flag IN Varchar2, -- Add new Column
2145 P_tax_recovery_rate IN Number, -- Add new Column
2146 P_tax_code_override_flag IN Varchar2, -- Add new Column
2147 P_tax_recovery_override_flag IN Varchar2, -- Add new Column
2148 P_po_distribution_id IN Number, -- Add new Column
2149 P_Attribute_Category IN Varchar2,
2150 P_Attribute1 IN Varchar2,
2151 P_Attribute2 IN Varchar2,
2152 P_Attribute3 IN Varchar2,
2153 P_Attribute4 IN Varchar2,
2154 P_Attribute5 IN Varchar2,
2155 P_Attribute6 IN Varchar2,
2156 P_Attribute7 IN Varchar2,
2157 P_Attribute8 IN Varchar2,
2158 P_Attribute9 IN Varchar2,
2159 P_Attribute10 IN Varchar2,
2160 P_Attribute11 IN Varchar2,
2161 P_Attribute12 IN Varchar2,
2162 P_Attribute13 IN Varchar2,
2163 P_Attribute14 IN Varchar2,
2164 P_Attribute15 IN Varchar2,
2165 P_Calling_Sequence IN Varchar2)
2166 IS
2167 BEGIN
2168 ----------------------------------------------------------
2169 -- Stubbed OUT JL will not longer insert in AP Dist Table
2170 -- R12
2171 ----------------------------------------------------------
2172 NULL;
2173
2174 EXCEPTION
2175 WHEN OTHERS THEN
2176 IF (SQLCODE <> -20001) THEN
2177 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
2178 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
2179 END IF;
2180 APP_EXCEPTION.RAISE_EXCEPTION;
2181
2182 END Jl_Zz_Ap_Ext_Insert_Dist;
2183
2184
2185
2186
2187 /**************************************************************************
2188 * Private Procedure *
2189 **************************************************************************/
2190
2191
2192 /**************************************************************************
2193 * *
2194 * Name : Get_Period_Name *
2195 * Purpose : Returns the name of the AWT period for a particular tax *
2196 * name and period type *
2197 * *
2198 **************************************************************************/
2199 FUNCTION Get_Period_Name
2200 (P_Tax_Name IN Varchar2,
2201 P_Period_Type IN Varchar2,
2202 P_AWT_Date IN Date,
2203 P_Calling_Sequence IN Varchar2,
2204 P_AWT_Success OUT NOCOPY Varchar2)
2205 RETURN Varchar2
2206 IS
2207
2208 ------------------------------
2209 -- Local variables definition
2210 ------------------------------
2211 l_period_name Varchar2(15);
2212 l_debug_info Varchar2(300);
2213 l_calling_sequence Varchar2(2000);
2214
2215 BEGIN
2216 -------------------------------
2217 -- Initializes debug variables
2218 -------------------------------
2219 l_calling_sequence := 'JL_ZZ_AP_WITHHOLDING_PKG' || '.' ||
2220 'Get_Period_Name<--' || P_Calling_Sequence;
2221
2222 -----------------------------------
2223 -- Assumes successfully completion
2224 -----------------------------------
2225 P_AWT_Success := AWT_SUCCESS;
2226
2227 --------------------------------------
2228 -- Obtains the name of the AWT period
2229 --------------------------------------
2230 IF (P_Period_Type IS NULL) THEN
2231 RETURN null;
2232 ELSE
2233 SELECT period_name
2234 INTO l_period_name
2235 FROM ap_other_periods
2236 WHERE application_id = 200
2237 AND module = 'AWT'
2238 AND period_type = P_Period_Type
2239 AND start_date <= trunc(P_AWT_Date)
2240 AND end_date >= trunc(P_AWT_Date);
2241
2242 RETURN l_period_name;
2243 END IF;
2244
2245 EXCEPTION
2246 WHEN no_data_found THEN
2247 Fnd_Message.Set_Name ('JL', 'JL_AR_AP_AWT_PERIOD_ERROR');
2248 Fnd_Message.Set_Token ('TAX_NAME', P_Tax_Name);
2249 Fnd_Message.Set_Token ('PERIOD_TYPE', P_Period_Type);
2250 Fnd_Message.Set_Token ('AWT_DATE', P_AWT_Date);
2251 P_AWT_Success := Fnd_Message.Get;
2252 RETURN null;
2253
2254 WHEN others THEN
2255 IF (SQLCODE <> -20001) THEN
2256 Fnd_Message.Set_Name ('JL', 'JL_ZZ_AP_DEBUG');
2257 Fnd_Message.Set_Token('ERROR', SQLERRM);
2258 Fnd_Message.Set_Token('CALLING_SEQUENCE', l_calling_sequence);
2259 Fnd_Message.Set_Token('PARAMETERS',
2260 ' Tax Name= ' || P_Tax_Name ||
2261 ', Period Type= ' || P_Period_Type ||
2262 ', AWT Date= ' || to_char(P_AWT_Date,'YYYY/MM/DD'));
2263 Fnd_Message.Set_Token('DEBUG_INFO', l_debug_info);
2264 END IF;
2265
2266 App_Exception.Raise_Exception;
2267
2268 END Get_Period_Name;
2269
2270
2271
2272
2273 /**************************************************************************
2274 * *
2275 * Name : Get_Cumulative_Figures *
2276 * Purpose : Obtains the cumulative gross amount to date and the *
2277 * cumulative withheld amount to date for a particular *
2278 * supplier, tax name and period. *
2279 * *
2280 **************************************************************************/
2281 PROCEDURE Get_Cumulative_Figures
2282 (P_Vendor_Id IN Number,
2283 P_Tax_Name IN Varchar2,
2284 P_AWT_Period_Type IN Varchar2,
2285 P_AWT_Date IN Date,
2286 P_Calling_Sequence IN Varchar2,
2287 P_Gross_Amount_To_Date OUT NOCOPY Number,
2288 P_Withheld_Amount_To_Date OUT NOCOPY Number,
2289 P_AWT_Success OUT NOCOPY Varchar2)
2290 IS
2291
2292 ------------------------------
2293 -- Local variables definition
2294 ------------------------------
2295 l_period_name Varchar2(15);
2296 l_gross_amount_to_date Number;
2297 l_withheld_amount_to_date Number;
2298 l_debug_info Varchar2(300);
2299 l_calling_sequence Varchar2(2000);
2300
2301 BEGIN
2302 -------------------------------
2303 -- Initializes debug variables
2304 -------------------------------
2305 l_calling_sequence := 'JL_ZZ_AP_WITHHOLDING_PKG' || '.' ||
2306 'Get_Cumulative_Figures<--' || P_Calling_Sequence;
2307
2308 --------------------------------------
2309 -- Obtains the name of the awt period
2310 --------------------------------------
2311 l_period_name := Get_Period_Name(P_Tax_Name,
2312 P_AWT_Period_Type,
2313 P_AWT_Date,
2314 l_calling_sequence,
2315 P_AWT_Success);
2316
2317 IF (P_AWT_Success <> AWT_SUCCESS) THEN
2318 RETURN;
2319 END IF;
2320
2321 ------------------------------
2322 -- Obtains cumulative figures
2323 ------------------------------
2324 SELECT gross_amount_to_date,
2325 withheld_amount_to_date
2326 INTO l_gross_amount_to_date,
2327 l_withheld_amount_to_date
2328 FROM ap_awt_buckets
2329 WHERE period_name = l_period_name
2330 AND tax_name = P_Tax_Name
2331 AND vendor_id = P_Vendor_Id;
2332
2333 --------------------------
2334 -- Sets output parameters
2335 --------------------------
2336 P_Gross_Amount_To_Date := l_gross_amount_to_date;
2337 P_Withheld_Amount_To_Date := l_withheld_amount_to_date;
2338
2339 EXCEPTION
2340 WHEN no_data_found THEN
2341 P_Gross_Amount_To_Date := 0;
2342 P_Withheld_Amount_To_Date := 0;
2343
2344 WHEN others THEN
2345 IF (SQLCODE <> -20001) THEN
2346 Fnd_Message.Set_Name ('JL', 'JL_ZZ_AP_DEBUG');
2347 Fnd_Message.Set_Token('ERROR', SQLERRM);
2348 Fnd_Message.Set_Token('CALLING_SEQUENCE', l_calling_sequence);
2349 Fnd_Message.Set_Token('PARAMETERS',
2350 ' Vendor Id= ' || to_char(P_Vendor_Id) ||
2351 ', Tax Name= ' || P_Tax_Name ||
2352 ', AWT Period Type= ' || P_AWT_Period_Type ||
2353 ', AWT Date= ' || to_char(P_AWT_Date,'YYYY/MM/DD'));
2354 Fnd_Message.Set_Token('DEBUG_INFO', l_debug_info);
2355 END IF;
2356
2357 App_Exception.Raise_Exception;
2358
2359 END Get_Cumulative_Figures;
2360
2361
2362
2363
2364 /**************************************************************************
2365 * *
2366 * Procedure : Get_Tax_Rate *
2367 * Description: Obtains the tax rate for the current tax name and for the *
2368 * calculated taxable base amount. *
2369 * *
2370 **************************************************************************/
2371 PROCEDURE Get_Tax_Rate
2372 (P_Tax_Name IN Varchar2,
2373 P_Date IN Date,
2374 P_Taxable_Base_Amount IN Number,
2375 P_Calling_Sequence IN Varchar2,
2376 P_Rec_AWT_Rate OUT NOCOPY Rec_AWT_Rate,
2377 P_AWT_Success OUT NOCOPY Varchar2)
2378 IS
2379 ------------------------------
2380 -- Local variables definition
2381 ------------------------------
2382 l_tax_rate_found Boolean := FALSE;
2383 l_debug_info Varchar2(300);
2384 l_calling_sequence Varchar2(2000);
2385
2386 ----------------------
2387 -- Cursor Definition
2388 ----------------------
2389 CURSOR c_tax_rates (P_Tax_Name IN Varchar2,
2390 P_Date IN Date) IS
2391 SELECT tax_rate,
2392 tax_rate_id,
2393 rate_type,
2394 start_amount,
2395 end_amount,
2396 global_attribute1,
2397 global_attribute2
2398 FROM ap_awt_tax_rates
2399 WHERE tax_name = P_Tax_Name
2400 AND rate_type = 'STANDARD'
2401 AND P_Date BETWEEN nvl(start_date, P_Date - 1)
2402 AND nvl(end_date, P_Date + 1)
2403 ORDER BY start_amount asc;
2404
2405 ---------------------
2406 -- Record Definition
2407 ---------------------
2408 rec_tax_rates c_tax_rates%ROWTYPE;
2409
2410 BEGIN
2411 -------------------------------
2412 -- Initializes debug variables
2413 -------------------------------
2414 l_calling_sequence := 'JL_ZZ_AP_WITHHOLDING_PKG' || '.' ||
2415 'Get_Tax_Rate<--' || P_Calling_Sequence;
2416
2417 -----------------------------------
2418 -- Assumes successfully completion
2419 -----------------------------------
2420 P_AWT_Success := AWT_SUCCESS;
2421
2422 -----------------------------------------
2423 -- Opens the cursor to get all the rates
2424 -- for the tax name
2425 -----------------------------------------
2426 OPEN c_tax_rates (P_Tax_Name, P_Date);
2427 LOOP
2428 FETCH c_tax_rates INTO rec_tax_rates;
2429 EXIT WHEN c_tax_rates%NOTFOUND
2430 OR c_tax_rates%NOTFOUND IS NULL
2431 OR l_tax_rate_found;
2432
2433 ----------------------------------
2434 -- Checks the taxable base amount
2435 ----------------------------------
2436 IF (ABS(P_Taxable_Base_Amount) >= nvl(rec_tax_rates.start_amount,
2437 ABS(P_Taxable_Base_Amount)) AND
2438 ABS(P_Taxable_Base_Amount) <= nvl(rec_tax_rates.end_amount,
2439 ABS(P_Taxable_Base_Amount))) THEN
2440 P_Rec_AWT_Rate.Tax_Rate_Id := rec_tax_rates.tax_rate_id;
2441 P_Rec_AWT_Rate.Tax_Rate := rec_tax_rates.tax_rate;
2442 P_Rec_AWT_Rate.Rate_Type := rec_tax_rates.rate_type;
2443 P_Rec_AWT_Rate.Amount_To_Subtract := rec_tax_rates.global_attribute1;
2444 P_Rec_AWT_Rate.Amount_To_Add := rec_tax_rates.global_attribute2;
2445 l_tax_rate_found := TRUE;
2446 END IF;
2447 END LOOP;
2448
2449 CLOSE c_tax_rates;
2450
2451 IF (NOT l_tax_rate_found) THEN
2452 Fnd_Message.Set_Name ('JL', 'JL_ZZ_AP_TAX_RATE_NOT_FOUND');
2453 Fnd_Message.Set_Token ('TAX_NAME', P_Tax_Name);
2454 Fnd_Message.Set_Token ('AWT_DATE', P_Date);
2455 Fnd_Message.Set_Token ('BASE_AMOUNT', P_Taxable_Base_Amount);
2456 P_AWT_Success := Fnd_Message.Get;
2457 RETURN;
2458 END IF;
2459
2460 EXCEPTION
2461 WHEN others THEN
2462 IF (SQLCODE <> -20001) THEN
2463 Fnd_Message.Set_Name ('JL', 'JL_ZZ_AP_DEBUG');
2464 Fnd_Message.Set_Token('ERROR', SQLERRM);
2465 Fnd_Message.Set_Token('CALLING_SEQUENCE', l_calling_sequence);
2466 Fnd_Message.Set_Token('PARAMETERS',
2467 ' Tax Name= ' || P_Tax_Name ||
2468 ', Date= ' || to_char(P_Date) ||
2469 ', Taxable Base Amount= ' || to_char(P_Taxable_Base_Amount));
2470 Fnd_Message.Set_Token('DEBUG_INFO', l_debug_info);
2471 END IF;
2472
2473 App_Exception.Raise_Exception;
2474
2475 END Get_Tax_Rate;
2476
2477
2478
2479
2480 /**************************************************************************
2481 * *
2482 * Name : Update_Withheld_Amount *
2483 * Purpose : Prorates the withheld amount for each tax name included *
2484 * into the PL/SQL table. These values will also be rounded. *
2485 * *
2486 **************************************************************************/
2487 PROCEDURE Update_Withheld_Amount
2488 (P_Original_Withheld_Amt IN Number,
2489 P_Updated_Withheld_Amt IN Number,
2490 P_Currency_Code IN Varchar2,
2491 P_Calling_Sequence IN Varchar2,
2492 P_Tab_Withhold IN OUT NOCOPY Tab_Withholding)
2493 IS
2494
2495 ------------------------------
2496 -- Local variables definition
2497 ------------------------------
2498 l_withheld_amount Number := 0;
2499 l_cumulative_amount Number := 0;
2500 l_previous_tax_id Number := null;
2501 l_updated_withheld_amt Number;
2502 l_debug_info Varchar2(300);
2503 l_calling_sequence Varchar2(2000);
2504
2505 BEGIN
2506 -------------------------------
2507 -- Initializes debug variables
2508 -------------------------------
2509 l_calling_sequence := 'JL_ZZ_AP_WITHHOLDING_PKG' || '.' ||
2510 'Update_Withheld_Amount<--' || P_Calling_Sequence;
2511
2512 ---------------------------------------------------
2513 -- Checks whether the original withheld amount is
2514 -- different from zero
2515 ---------------------------------------------------
2516 IF (P_Original_Withheld_Amt = 0) THEN
2517 RETURN;
2518 END IF;
2519
2520 --------------------------------------
2521 -- Rounds the updated withheld amount
2522 --------------------------------------
2523 l_updated_withheld_amt := Ap_Utilities_Pkg.Ap_Round_Currency
2524 (P_Updated_Withheld_Amt, P_Currency_Code);
2525
2526 -----------------------------------------------------------
2527 -- Updates the withheld amount for each different tax name
2528 -----------------------------------------------------------
2529 FOR i IN 1 .. P_Tab_Withhold.COUNT LOOP
2530
2531 IF (l_previous_tax_id IS NULL OR
2532 P_Tab_Withhold(i).tax_id <> l_previous_tax_id) THEN
2533
2534 ----------------------------------------------------
2535 -- Calculates the withheld amount for each tax name
2536 -- except for the last one
2537 ----------------------------------------------------
2538 IF (P_Tab_Withhold(i).tax_id <>
2539 P_Tab_Withhold(P_Tab_Withhold.COUNT).tax_id) THEN
2540
2541 l_withheld_amount := P_Tab_Withhold(i).withheld_amount *
2542 l_updated_withheld_amt /
2543 P_Original_Withheld_Amt;
2544 l_withheld_amount := Ap_Utilities_Pkg.Ap_Round_Currency
2545 (l_withheld_amount, P_Currency_Code);
2546 l_cumulative_amount := l_cumulative_amount + l_withheld_amount;
2547
2548 --------------------------------------------------------
2549 -- Calculates the withheld amount for the last tax name
2550 --------------------------------------------------------
2551 ELSE
2552 l_withheld_amount := l_updated_withheld_amt -
2553 l_cumulative_amount;
2554 END IF;
2555
2556 l_previous_tax_id := P_Tab_Withhold(i).tax_id;
2557
2558 END IF;
2559
2560 ---------------------------------------------------
2561 -- Updates the withheld amount in the PL/SQL table
2562 ---------------------------------------------------
2563 P_Tab_Withhold(i).withheld_amount := l_withheld_amount;
2564
2565 END LOOP;
2566
2567 EXCEPTION
2568 WHEN others THEN
2569 IF (SQLCODE <> -20001) THEN
2570 Fnd_Message.Set_Name ('JL', 'JL_ZZ_AP_DEBUG');
2571 Fnd_Message.Set_Token('ERROR', SQLERRM);
2572 Fnd_Message.Set_Token('CALLING_SEQUENCE', l_calling_sequence);
2573 Fnd_Message.Set_Token('PARAMETERS',
2574 ' Original Withheld Amt= ' || to_char(P_Original_Withheld_Amt) ||
2575 ', Updated Withheld Amt= ' || to_char(P_Updated_Withheld_Amt) ||
2576 ', Currency Code= ' || P_Currency_Code);
2577 Fnd_Message.Set_Token('DEBUG_INFO', l_debug_info);
2578 END IF;
2579
2580 App_Exception.Raise_Exception;
2581
2582 END Update_Withheld_Amount;
2583
2584
2585
2586
2587 /**************************************************************************
2588 * *
2589 * Name : Get_Revised_Tax_Base_Amount *
2590 * Purpose : 1 Retrieves the taxable base amount from the PL/SQL table *
2591 * 2 Applies all the validations like income tax rate, *
2592 * reduction percentage etc., and generates a revised *
2593 * taxable base amount. *
2594 * 3 Updates the PL/SQL table to store the revised amount *
2595 * *
2596 **************************************************************************/
2597 FUNCTION Get_Revised_Tax_Base_Amount
2598 (P_Rec_AWT_Name IN Rec_AWT_CODE,
2599 P_Tab_Withhold IN OUT NOCOPY Tab_Withholding,
2600 P_Tax_Name_From IN Number,
2601 P_Tax_Name_To IN Number,
2602 P_Taxable_Base_Amount IN Number,
2603 P_Tab_All_Withhold IN Tab_All_Withholding,
2604 P_Calling_Sequence IN Varchar2)
2605 RETURN NUMBER
2606 IS
2607 ------------------------------
2608 -- Local Variables Definition
2609 ------------------------------
2610 tab Tab_All_Withholding := P_Tab_All_Withhold;
2611 ctr Number;
2612 pos Number;
2613 l_revised_tax_base_amt Number := 0;
2614 l_debug_info Varchar2(300);
2615 l_calling_sequence Varchar2(2000);
2616
2617 BEGIN
2618 -------------------------------
2619 -- Initializes debug variables
2620 -------------------------------
2621 l_calling_sequence := 'JL_ZZ_AP_WITHHOLDING_PKG' || '.' ||
2622 'Get_Revised_Tax_Base_Amount<--' || P_Calling_Sequence;
2623
2624 ----------------------------------------------------------------
2625 -- Reduces the taxable base amount by income tax rate percentage
2626 ----------------------------------------------------------------
2627 l_revised_tax_base_amt := P_Taxable_Base_Amount -
2628 (P_Taxable_Base_Amount *
2629 nvl(P_Rec_AWT_Name.Income_Tax_Rate/100,1));
2630
2631 ----------------------------------------------------------------
2632 -- Applies all the validations that are applicable to the tax
2633 -- name, on the revised taxable base amount
2634 ----------------------------------------------------------------
2635
2636 FOR pos IN P_Tax_Name_From..P_Tax_Name_To LOOP
2637
2638 FOR ctr IN 1..tab.COUNT LOOP
2639
2640 IF (P_Tab_All_Withhold(ctr).invoice_distribution_id =
2641 P_Tab_Withhold(pos).invoice_distribution_id) THEN
2642
2643 IF ((P_Rec_AWT_Name.First_Tax_Type IS NOT NULL) AND
2644 (P_Tab_All_Withhold(ctr).awt_type_code =
2645 P_Rec_AWT_Name.First_Tax_Type)) THEN
2646
2647 l_revised_tax_base_amt := l_revised_tax_base_amt -
2648 nvl(P_Tab_All_Withhold(ctr).prorated_amount,0);
2649
2650 ELSIF ((P_Rec_AWT_Name.Second_Tax_Type IS NOT NULL) AND
2651 (P_Tab_All_Withhold(ctr).awt_type_code =
2652 P_Rec_AWT_Name.Second_Tax_Type)) THEN
2653
2654 l_revised_tax_base_amt := l_revised_tax_base_amt -
2655 nvl(P_Tab_All_Withhold(ctr).prorated_amount,0);
2656
2657 ELSIF ((P_Rec_AWT_Name.Municipal_Type = 'Y') AND
2658 (UPPER(P_Tab_All_Withhold(ctr).jurisdiction_type) =
2659 'MUNICIPAL')) THEN
2660
2661 l_revised_tax_base_amt := l_revised_tax_base_amt -
2662 nvl(P_Tab_All_Withhold(ctr).prorated_amount,0);
2663
2664 END IF;
2665
2666 END IF;
2667
2668 END LOOP;
2669
2670 END LOOP;
2671
2672 ----------------------------------------------------------------
2673 -- Multiplies the revised taxable base amount by the reduction
2674 -- percentage
2675 ----------------------------------------------------------------
2676 IF (P_Rec_AWT_Name.Reduction_Perc = 0) THEN
2677 l_revised_tax_base_amt := l_revised_tax_base_amt * 1;
2678 ELSE
2679 l_revised_tax_base_amt := l_revised_tax_base_amt *
2680 (nvl(P_Rec_AWT_Name.Reduction_Perc/100,1));
2681 END IF;
2682
2683
2684 ----------------------------------------------------------------
2685 -- Updates the amount contained in the PL/SQL table inorder to
2686 -- store the revised taxable base amount
2687 ----------------------------------------------------------------
2688 FOR pos IN P_Tax_Name_From..P_Tax_Name_To LOOP
2689
2690 P_Tab_Withhold(pos).revised_tax_base_amount := l_revised_tax_base_amt;
2691
2692 END LOOP;
2693
2694 RETURN l_revised_tax_base_amt;
2695
2696 EXCEPTION
2697 WHEN others THEN
2698 IF (SQLCODE <> -20001) THEN
2699 Fnd_Message.Set_Name ('JL', 'JL_ZZ_AP_DEBUG');
2700 Fnd_Message.Set_Token('ERROR', SQLERRM);
2701 Fnd_Message.Set_Token('CALLING_SEQUENCE', l_calling_sequence);
2702 Fnd_Message.Set_Token('PARAMETERS',
2703 ' Tax Name From= ' || to_char(P_Tax_Name_From) ||
2704 ', Tax Name To= ' || to_char(P_Tax_Name_To) ||
2705 ', Taxable Base Amount= ' || to_char(P_Taxable_Base_Amount));
2706 Fnd_Message.Set_Token('DEBUG_INFO', l_debug_info);
2707 END IF;
2708
2709 App_Exception.Raise_Exception;
2710
2711 END Get_Revised_Tax_Base_Amount;
2712
2713
2714
2715
2716 /**************************************************************************
2717 * *
2718 * Name : Bool_To_Char *
2719 * Purpose : Converts the Boolean value received as a parameter to a *
2720 * Varchar2 character string. This function is only used *
2721 * for debug purposes. *
2722 * *
2723 **************************************************************************/
2724 FUNCTION Bool_To_Char (P_Bool_Value IN Boolean) RETURN Varchar2
2725 IS
2726 BEGIN
2727 IF (P_Bool_Value IS NULL) THEN
2728 RETURN null;
2729 ELSIF (P_Bool_Value) THEN
2730 RETURN 'Yes';
2731 ELSE
2732 RETURN 'No';
2733 END IF;
2734 END Bool_To_Char;
2735
2736
2737 /*************************************************************************
2738 * Name : Validate_Multiple_Bal_Seg *
2739 * Purpose : Rountine to check whether there exists multiple balancing *
2740 * segments within invoice distributions or tax code *
2741 * *
2742 **************************************************************************/
2743
2744 FUNCTION Validate_Multiple_Bal_Seg
2745 (P_Invoice_Id ap_invoices.invoice_id%TYPE
2746 ) return Varchar2
2747 IS
2748
2749 t_bal_seg varchar2(200);
2750 Curr_Bal varchar2(200);
2751 Pre_Bal varchar2(200);
2752 l_liability_post_lookup_code AP_SYSTEM_PARAMETERS.liability_post_lookup_code%TYPE;
2753 counter number :=1 ;
2754
2755
2756 ----------------------------------------------------------------------
2757 -- Cursor to get CCID from AP_Invoice_Distributions_ID
2758 ----------------------------------------------------------------------
2759 CURSOR Bal_Seg IS
2760 SELECT dist_code_combination_id
2761 FROM ap_invoice_distributions
2762 WHERE invoice_id = P_Invoice_id;
2763
2764 ----------------------------------------------------------------------
2765 -- Cursor to get the distinct tax codes for the given invoice
2766 ----------------------------------------------------------------------
2767 CURSOR tax_code IS
2768 SELECT distinct atc.name, atc.tax_code_combination_id
2769 FROM jl_zz_ap_inv_dis_wh jid,
2770 jl_zz_ap_sup_awt_cd jsw,
2771 ap_tax_codes atc
2772 WHERE jid.invoice_id = P_Invoice_Id
2773 AND jsw.supp_awt_code_id = jid.supp_awt_code_id
2774 AND atc.tax_id = jsw.tax_id;
2775
2776 -------------------------------------------------------------------------
2777 -- Validate for multiple balancing segments in distribution lines.
2778 -------------------------------------------------------------------------
2779 BEGIN
2780 ----------------------------------------------------------------------------------------
2781 -- Get Set of Books and Auto-offsets Option info
2782 ----------------------------------------------------------------------------------------
2783
2784 SELECT nvl(liability_post_lookup_code, 'NONE')
2785 INTO l_liability_post_lookup_code
2786 FROM ap_system_parameters;
2787
2788 IF (l_Liability_Post_Lookup_Code = 'BALANCING_SEGMENT') AND
2789 (Ap_Extended_Withholding_Pkg.Ap_Extended_Withholding_Active) THEN
2790
2791 For my_reg IN Bal_Seg LOOP
2792
2793 Curr_BaL := Dynamic_Call_Get_BalSeg(my_reg.dist_code_combination_id);
2794
2795 IF counter > 1 THEN
2796 IF Curr_Bal <> Pre_Bal Then
2797 Return('Error');
2798 END IF;
2799 ELSE
2800 Pre_Bal := Curr_Bal;
2801 End IF;
2802 counter := counter + 1;
2803 End Loop;
2804
2805 ------------------------------------------------------------------------
2806 -- Check for mulitiple balancing segments in the applicable tax codes
2807 ------------------------------------------------------------------------
2808 FOR cur_rec IN tax_code LOOP
2809
2810 t_bal_seg := Dynamic_Call_Get_BalSeg(cur_rec.tax_code_combination_id);
2811
2812 ------------------------------------------------------------------
2813 -- Check if the balancing segment for the tax code is different
2814 ------------------------------------------------------------------
2815 IF t_bal_seg <> Curr_Bal THEN
2816 return('Error');
2817 END IF;
2818 END LOOP;
2819 -- Return Success
2820 return('Success');
2821 END IF; -- Balancing Segement
2822 -- No Balancing Segment
2823 return('Success');
2824 END Validate_Multiple_Bal_Seg;
2825
2826 /*************************************************************************
2827 * Name : Validate_Mult_BS_GateWay *
2828 * Purpose : Rountine to check whether there exists multiple balancing *
2829 * segments within invoice distributions or tax code *
2830 * for Invoice Gateway *
2831 * *
2832 **************************************************************************/
2833
2834 FUNCTION Validate_Mult_BS_GateWay
2835 (P_Invoice_Id ap_invoices.invoice_id%TYPE
2836 ) return Varchar2
2837 IS
2838
2839 t_bal_seg varchar2(200);
2840 l_liability_post_lookup_code AP_SYSTEM_PARAMETERS.liability_post_lookup_code%TYPE;
2841 Curr_Bal varchar2(200);
2842 Pre_Bal varchar2(200);
2843 counter number :=1 ;
2844
2845 ----------------------------------------------------------------------
2846 -- Cursor to get CCID from AP_Invoice_Distributions_ID
2847 ----------------------------------------------------------------------
2848 CURSOR Bal_Seg IS
2849 SELECT dist_code_combination_id
2850 FROM ap_invoice_lines_interface
2851 WHERE invoice_id = P_Invoice_id;
2852
2853 ----------------------------------------------------------------------
2854 -- Cursor to get the distinct tax codes for the given invoice
2855 ----------------------------------------------------------------------
2856 CURSOR tax_code IS
2857 SELECT distinct atc.name, atc.tax_code_combination_id
2858 FROM jl_zz_ap_sup_awt_cd jsw,
2859 jl_zz_ap_supp_awt_types jst,
2860 ap_tax_codes atc,
2861 ap_invoices_interface aii
2862 WHERE aii.invoice_id = P_Invoice_id
2863 AND jst.vendor_id = aii.vendor_id
2864 AND jst.supp_awt_type_id = jsw.supp_awt_type_id
2865 AND atc.tax_id = jsw.tax_id
2866 AND jsw.primary_tax_flag = 'Y';
2867
2868 -------------------------------------------------------------------------
2869 -- Validate for multiple balancing segments in distribution lines.
2870 -------------------------------------------------------------------------
2871 BEGIN
2872 ----------------------------------------------------------------------------------------
2873 -- Get Set of Books and Auto-offsets Option info
2874 ----------------------------------------------------------------------------------------
2875
2876 SELECT nvl(liability_post_lookup_code, 'NONE')
2877 INTO l_liability_post_lookup_code
2878 FROM ap_system_parameters;
2879
2880
2881 IF (l_Liability_Post_Lookup_Code = 'BALANCING_SEGMENT') AND
2882 (Ap_Extended_Withholding_Pkg.Ap_Extended_Withholding_Active) THEN
2883
2884 For my_reg IN Bal_Seg LOOP
2885 Curr_BaL := Dynamic_Call_Get_BalSeg(my_reg.dist_code_combination_id);
2886 IF counter > 1 THEN
2887 IF Curr_Bal <> Pre_Bal Then
2888 Return('Error');
2889 END IF;
2890 ELSE
2891 Pre_Bal := Curr_Bal;
2892 End IF;
2893 counter := counter + 1;
2894 End Loop;
2895
2896 ------------------------------------------------------------------------
2897 -- Check for mulitiple balancing segments in the applicable tax codes
2898 ------------------------------------------------------------------------
2899 FOR cur_rec in tax_code LOOP
2900
2901 t_bal_seg := Dynamic_Call_Get_BalSeg(cur_rec.tax_code_combination_id);
2902
2903 ------------------------------------------------------------------
2904 -- Check if the balancing segment for the tax code is different
2905 ------------------------------------------------------------------
2906 IF t_bal_seg <> Curr_Bal THEN
2907 return('Error');
2908 END IF;
2909 END LOOP;
2910 return('Success');
2911 END IF; -- Balancing Segement
2912
2913 -- No Balancing Segment
2914 return('Success');
2915
2916 END Validate_Mult_BS_GateWay;
2917
2918 /*************************************************************************
2919 * Name : Dynamic_Call_Get_BalSeg *
2920 * Purpose : Encapsulate Dynamic Call to get_auto_offsets_segments *
2921 * *
2922 **************************************************************************/
2923
2924 FUNCTION Dynamic_Call_Get_BalSeg
2925 (P_ccid IN Number ) return Varchar2
2926 IS
2927 Curr_Bal Varchar2(200):= null;
2928 l_cursor NUMBER;
2929 l_sqlstmt VARCHAR2(1000);
2930 l_ignore NUMBER;
2931
2932 Begin
2933 ------------------------------------------
2934 -- Dynamic Call
2935 ------------------------------------------
2936 -- Create the SQL statement
2937 l_cursor := dbms_sql.open_cursor;
2938 l_sqlstmt := 'BEGIN :Curr_BaL := ' ||
2939 'ap_utilities_pkg.get_auto_offsets_segments (:l_code_combination_id); END;';
2940
2941 -- Parse the SQL statement
2942 dbms_sql.parse (l_cursor, l_sqlstmt, dbms_sql.native);
2943
2944 -- Define the variables
2945 dbms_sql.bind_variable (l_cursor, 'Curr_BaL', Curr_BaL,200);
2946 dbms_sql.bind_variable (l_cursor, 'l_code_combination_id', P_ccid);
2947
2948 -- Execute the SQL statement
2949 l_ignore := dbms_sql.execute (l_cursor);
2950
2951 -- Get the return value (success)
2952 dbms_sql.variable_value (l_cursor, 'Curr_BaL', Curr_BaL);
2953
2954 -- Close the cursor
2955 dbms_sql.close_cursor (l_cursor);
2956
2957 -- Function Return Values
2958 return (Curr_Bal);
2959
2960 EXCEPTION
2961 WHEN others THEN
2962 IF (dbms_sql.is_open(l_cursor)) THEN
2963 dbms_sql.close_cursor(l_cursor);
2964 END IF;
2965 return (Curr_Bal);
2966 END Dynamic_Call_Get_BalSeg;
2967
2968 /**************************************************************************
2969 * *
2970 * Name : Get_Cumulative_Supp_Exemp *
2971 * Purpose : Obtains the cumulative supplier's exemption amount *
2972 * to date for a particular period *
2973 * *
2974 **************************************************************************/
2975 FUNCTION Get_Cumulative_Supp_Exemp
2976 (P_Vendor_Id IN Number,
2977 P_Tax_Name IN Varchar2,
2978 P_AWT_Period_Type IN Varchar2,
2979 P_AWT_Date IN Date,
2980 P_Calling_Sequence IN Varchar2)
2981 RETURN NUMBER IS
2982
2983 ------------------------------
2984 -- Local variables definition
2985 ------------------------------
2986 l_period_name Varchar2(15);
2987 l_exemption_amount Number := 0;
2988 l_start_date Date;
2989 l_end_date Date;
2990 l_tax_id Number;
2991 l_debug_info Varchar2(300);
2992 l_calling_sequence Varchar2(2000);
2993 P_AWT_Success Varchar2(10);
2994
2995 BEGIN
2996 -------------------------------
2997 -- Initializes debug variables
2998 -------------------------------
2999 l_calling_sequence := 'JL_ZZ_AP_WITHHOLDING_PKG' || '.' ||
3000 'Get_Cumulative_Supp_Exemp<--' || P_Calling_Sequence;
3001
3002 -- Debug Information
3003 IF (DEBUG_Var = 'Y') THEN
3004 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' ');
3005 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Function Get_Cumulative_Supp_Exemp');
3006 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Param: P_Vendor_Id: '||to_char(P_Vendor_Id));
3007 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Param: Tax Name: '||P_Tax_Name);
3008 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Param: P_AWT_Period_Type: '||P_AWT_Period_Type);
3009 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Param: P_AWT_Date: '||to_char(P_AWT_Date,'YYYY/MM/DD'));
3010 END IF;
3011 -- End Debug
3012
3013 --------------------------------------
3014 -- Obtains the name of the awt period
3015 --------------------------------------
3016 P_AWT_Success := AWT_SUCCESS;
3017
3018 l_period_name := Get_Period_Name(P_Tax_Name,
3019 P_AWT_Period_Type,
3020 P_AWT_Date,
3021 l_calling_sequence,
3022 P_AWT_Success);
3023
3024
3025 -- Debug Information
3026 IF (DEBUG_Var = 'Y') THEN
3027 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Period Name '||l_period_name);
3028 END IF;
3029 -- End Debug
3030
3031 -------------------------------------------------
3032 -- Obtains start and end date for a given period
3033 -------------------------------------------------
3034 SELECT start_date, end_date
3035 INTO l_start_date, l_end_date
3036 FROM ap_other_periods
3037 WHERE application_id = 200
3038 AND module = 'AWT'
3039 AND period_type = P_AWT_Period_Type
3040 AND period_name = l_period_name;
3041
3042 -- Debug Information
3043 IF (DEBUG_Var = 'Y') THEN
3044 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Start and End Dates for Withh Period '||to_char(l_start_date)||' '||
3045 to_char(l_end_date));
3046 END IF;
3047 -- End Debug
3048
3049 -------------------------------------------------
3050 -- Obtains start and end date for a given period
3051 -------------------------------------------------
3052 SELECT Tax_Id
3053 INTO l_tax_id
3054 FROM ap_tax_codes
3055 WHERE name = P_Tax_Name;
3056
3057 -- Debug Information
3058 IF (DEBUG_Var = 'Y') THEN
3059 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Tax ID : '||to_char(l_tax_id));
3060 END IF;
3061 -- End Debug
3062
3063 --------------------------------------------------------------------------
3064 -- Obtains cumulative supplier exemption amount to day for a given period
3065 --------------------------------------------------------------------------
3066 SELECT NVL(sum(to_number(aid.global_attribute5)),0)
3067 INTO l_exemption_amount
3068 FROM ap_invoices ai,
3069 ap_invoice_distributions aid
3070 WHERE ai.vendor_id = P_Vendor_Id
3071 AND ai.invoice_id = aid.invoice_id
3072 AND trunc(aid.accounting_date) >= l_start_date
3073 AND trunc(aid.accounting_date) <= l_end_date
3074 AND aid.line_type_lookup_code = 'AWT'
3075 AND aid.withholding_tax_code_id = l_tax_id;
3076
3077 --------------------------
3078 -- Sets output parameters
3079 --------------------------
3080 -- Debug Information
3081 IF (DEBUG_Var = 'Y') THEN
3082 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' Return Cumulative Exemption Amount = '||to_char(l_exemption_amount));
3083 JL_ZZ_AP_EXT_AWT_UTIL.Debug (' ');
3084 END IF;
3085 -- End Debug
3086
3087 Return(l_exemption_amount);
3088
3089 EXCEPTION
3090 WHEN no_data_found THEN
3091 Return(0);
3092
3093 WHEN others THEN
3094 IF (SQLCODE <> -20001) THEN
3095 Fnd_Message.Set_Name ('JL', 'JL_ZZ_AP_DEBUG');
3096 Fnd_Message.Set_Token('ERROR', SQLERRM);
3097 Fnd_Message.Set_Token('CALLING_SEQUENCE', l_calling_sequence);
3098 Fnd_Message.Set_Token('PARAMETERS',
3099 ' Vendor Id= ' || to_char(P_Vendor_Id) ||
3100 ', Tax Name= ' || P_Tax_Name ||
3101 ', AWT Period Type= ' || P_AWT_Period_Type ||
3102 ', AWT Date= ' || to_char(P_AWT_Date,'YYYY/MM/DD'));
3103 Fnd_Message.Set_Token('DEBUG_INFO', l_debug_info);
3104 END IF;
3105
3106 App_Exception.Raise_Exception;
3107 END Get_Cumulative_Supp_Exemp;
3108
3109 END JL_ZZ_AP_WITHHOLDING_PKG;