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