[Home] [Help]
PACKAGE BODY: APPS.JL_CO_AP_WITHHOLDING_PKG
Source
1 PACKAGE BODY JL_CO_AP_WITHHOLDING_PKG AS
2 /* $Header: jlcopwhb.pls 120.12.12010000.2 2009/01/29 13:42:24 gkumares ship $ */
3
4 /**************************************************************************
5 * Private Procedure Specification *
6 **************************************************************************/
7
8 /**************************************************************************
9 * *
10 * Name : Jl_Co_Ap_Calculate_AWT_Amounts *
11 * Purpose : This procedure performs all the withholding calculations *
12 * and generates the temporary distribution lines. *
13 * *
14 **************************************************************************/
15 PROCEDURE Jl_Co_Ap_Calculate_AWT_Amounts
16 (P_Invoice_Id IN Number,
17 P_AWT_Date IN Date,
18 P_Calling_Module IN Varchar2,
19 P_Create_Dists IN Varchar2,
20 P_Amount IN Number,
21 P_Last_Updated_By IN Number Default Null,
22 P_Last_Update_Login IN Number Default Null,
23 P_Program_Application_Id IN Number Default Null,
24 P_Program_Id IN Number Default Null,
25 P_Request_Id IN Number Default Null,
26 P_AWT_Success IN OUT NOCOPY Varchar2,
27 P_Calling_Sequence IN Varchar2
28 );
29
30 /**************************************************************************
31 * *
32 * Name : User_Defined_Formula_Exists *
33 * Purpose : This function returns TRUE, if there is atleast one *
34 * type within this NIT, with the user defined formula *
35 * flag set to 'Y'. Otherwise, it returns FALSE *
36 * *
37 **************************************************************************/
38
39 FUNCTION User_Defined_Formula_Exists
40 (P_Invoice_Id IN Number,
41 P_NIT IN Varchar2) RETURN BOOLEAN;
42
43
44 /**************************************************************************
45 * *
46 * Name : Initialize_Withholdings *
47 * Purpose : Obtains all the attributes for the current withholding *
48 * tax type and tax name. This procedure also initializes a *
49 * PL/SQL table to store the withholdings
50 * *
51 **************************************************************************/
52 PROCEDURE Initialize_Withholdings
53 (P_Vendor_Id IN Number,
54 P_AWT_Type_Code IN Varchar2,
55 P_Tax_Id IN Number,
56 P_Rec_AWT_Type OUT NOCOPY jl_zz_ap_awt_types%ROWTYPE,
57 P_Rec_AWT_Name OUT NOCOPY Jl_Zz_Ap_Withholding_Pkg.Rec_AWT_Code,
58 P_Rec_Suppl_AWT_Type OUT NOCOPY jl_zz_ap_supp_awt_types%ROWTYPE,
59 P_Rec_Suppl_AWT_Name OUT NOCOPY jl_zz_ap_sup_awt_cd%ROWTYPE,
60 P_Wh_Table IN OUT NOCOPY Jl_Zz_Ap_Withholding_Pkg.Tab_Withholding);
61
62
63
64 /**************************************************************************
65 * *
66 * Name : Process_Withholdings *
67 * Purpose : Process the information for the current withholding tax *
68 * type and name *
69 * *
70 **************************************************************************/
71 PROCEDURE Process_Withholdings
72 (P_Vendor_Id IN Number,
73 P_Rec_AWT_Type IN jl_zz_ap_awt_types%ROWTYPE,
74 P_Rec_Suppl_AWT_Type IN jl_zz_ap_supp_awt_types%ROWTYPE,
75 P_AWT_Date IN Date,
76 P_GL_Period_Name IN Varchar2,
77 P_Base_Currency_Code IN Varchar2,
78 P_User_Defd_Formula IN Boolean,
79 P_NIT_Number IN Varchar2 Default null,
80 P_Tab_Withhold IN OUT NOCOPY Jl_Zz_Ap_Withholding_Pkg.Tab_Withholding,
81 P_Tab_All_Withhold IN OUT NOCOPY Jl_Zz_Ap_Withholding_Pkg.Tab_All_Withholding,
82 P_AWT_Success OUT NOCOPY Varchar2,
83 P_Last_Updated_By IN Number Default null,
84 P_Last_Update_Login IN Number Default null,
85 P_Program_Application_Id IN Number Default null,
86 P_Program_Id IN Number Default null,
87 P_Request_Id IN Number Default null,
88 P_Calling_Module IN Varchar2 Default null
89 );
90
91
92 /**************************************************************************
93 * Public Procedures *
94 **************************************************************************/
95
96
97 /**************************************************************************
98 * *
99 * Name : Jl_Co_Ap_Do_Withholding *
100 * Purpose : This is the main procedure for the Colombian Automatic *
101 * Withholding Tax calculation. Three different processing *
102 * units are executed from this main routine. *
103 * *
104 **************************************************************************/
105
106 PROCEDURE Jl_Co_Ap_Do_Withholding
107 (P_Invoice_Id IN Number,
108 P_AWT_Date IN Date,
109 P_Calling_Module IN Varchar2,
110 P_Amount IN Number,
111 P_Payment_Num IN Number Default Null,
112 P_Last_Updated_By IN Number,
113 P_Last_Update_Login IN Number,
114 P_Program_Application_Id IN Number Default Null,
115 P_Program_Id IN Number Default Null,
116 P_Request_Id IN Number Default Null,
117 P_AWT_Success OUT NOCOPY Varchar2
118 )
119 IS
120 ------------------------
121 -- Variables Definition
122 ------------------------
123 l_awt_flag ap_invoices.awt_flag%TYPE;
124 l_inv_curr_code ap_invoices.invoice_currency_code%TYPE;
125 l_invoice_type_lookup_code ap_invoices.invoice_type_lookup_code%TYPE;
126 l_AWT_success Varchar2(2000) := 'SUCCESS';
127 l_create_dists ap_system_parameters.create_awt_dists_type%TYPE;
128 l_create_invoices ap_system_parameters.create_awt_invoices_type%TYPE;
129 current_calling_sequence Varchar2(2000);
130 debug_info Varchar2(100);
131
132
133 BEGIN
134
135 current_calling_sequence := 'JL_CO_AP_WITHHOLDING_PKG.Jl_Co_Ap_Do_Withholding';
136
137 -----------------------------------------------------------------------
138 -- IF calling module is different from AUTOAPPROVAL should not execute
139 -- anything for Colombia Bug# 2279293
140 -----------------------------------------------------------------------
141 IF (P_Calling_Module <> 'AUTOAPPROVAL') THEN
142 -- Bug 3008030 Initialize OUT parameter P_AWT_Success.
143 P_AWT_Success := l_AWT_success;
144 Return;
145 END IF;
146
147 ------------------------------------------------------------------------
148 -- Read the AWT flag for the current invoice to check whether AWT
149 -- calculation has already been performed by AUTOAPPROVAL on this
150 -- invoice
151 ------------------------------------------------------------------------
152 debug_info := 'Read the AWT flag for the current invoice';
153
154 SELECT
155 nvl(awt_flag,'N') awt_flag,
156 invoice_currency_code,
157 invoice_type_lookup_code
158 INTO
159 l_awt_flag,
160 l_inv_curr_code,
161 l_invoice_type_lookup_code
162 FROM
163 ap_invoices
164 WHERE
165 invoice_id = P_Invoice_Id;
166
167 ---------------------------
168 -- Check Invoice Type
169 ---------------------------
170 IF (l_invoice_type_lookup_code = 'AWT') THEN
171 P_AWT_Success := l_AWT_success;
172 Return;
173 END IF;
174
175 ---------------------------
176 -- Read setup information
177 ---------------------------
178 debug_info := 'Read setup information';
179
180 SELECT
181 nvl(create_awt_dists_type, 'NEVER'),
182 nvl(create_awt_invoices_type, 'NEVER')
183 INTO
184 l_create_dists,
185 l_create_invoices
186 FROM
187 ap_system_parameters;
188
189 ------------------------------------------------------------------------
190 -- Checks whether the withholding taxes are calculated at invoice
191 -- approval time
192 ------------------------------------------------------------------------
193 IF (l_create_dists <> 'APPROVAL' ) THEN
194 RETURN;
195 END IF;
196
197 -----------------------------------------------------------
198 -- Withholding Tax Calculation for "Invoice AutoApproval"
199 -----------------------------------------------------------
200 IF (
201 (P_Calling_Module = 'AUTOAPPROVAL') AND
202 (l_awt_flag <> 'Y')
203 )
204 THEN
205
206 SAVEPOINT Before_Temporary_Calculations;
207
208 --------------------------------------
209 -- Create Temporary AWT Distributions
210 --------------------------------------
211 Jl_Co_Ap_Calculate_AWT_Amounts
212 (P_Invoice_Id,
213 P_AWT_Date,
214 P_Calling_Module,
215 l_create_dists,
216 P_Amount,
217 P_Last_Updated_By,
218 P_Last_Update_Login,
219 P_Program_Application_Id,
220 P_Program_Id,
221 P_Request_Id,
222 l_AWT_success,
223 current_calling_sequence
224 );
225
226 IF (l_AWT_success <> 'SUCCESS') THEN
227
228 ROLLBACK TO Before_Temporary_Calculations;
229 ELSE
230 ----------------------------
231 -- Create AWT Distributions
232 ----------------------------
233 Ap_Withholding_Pkg.Create_AWT_Distributions
234 (P_Invoice_Id,
235 P_Calling_Module,
236 l_create_dists,
237 P_Payment_Num,
238 l_inv_curr_code,
239 P_Last_Updated_By,
240 P_Last_Update_Login,
241 P_Program_Application_Id,
242 P_Program_Id,
243 P_Request_Id,
244 current_calling_sequence
245 );
246
247 END IF;
248
249 IF (
250 (l_create_invoices = 'APPROVAL')
251 AND (l_AWT_success = 'SUCCESS')
252 )
253 THEN
254
255 -----------------------
256 -- Create AWT Invoices
257 -----------------------
258 Ap_Withholding_Pkg.Create_AWT_Invoices
259 (P_Invoice_Id,
260 P_AWT_Date,
261 P_Last_Updated_By,
262 P_Last_Update_Login,
263 P_Program_Application_Id,
264 P_Program_Id,
265 P_Request_Id,
266 current_calling_sequence,
267 P_Calling_Module
268 );
269 END IF;
270
271 P_AWT_Success := l_AWT_success;
272 -- Bug 3404210
273 -- When the invoice is validated for the second time after creating
274 -- a manual hold, the system generates AWT hold. This is because the invoice
275 -- to which withholding is already applied, AWT_FLAG is set to 'Y'
276 -- So it will not enter the above IF condition and SUCCESS status is not
277 -- returned to the called program. Added the below ELSE part.
278 ELSE
279 P_AWT_Success := l_AWT_success;
280 END IF;
281
282 EXCEPTION
283 WHEN others THEN
284 IF (SQLCODE <> -20001) THEN
285 FND_MESSAGE.SET_NAME('JL','JL_ZZ_AP_DEBUG');
286 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
287 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
288 FND_MESSAGE.SET_TOKEN('PARAMETERS',
289 ' Invoice Id = ' || to_char(P_Invoice_Id) ||
290 ', AWT Date = ' || to_char(P_Awt_Date,'YYYY/MM/DD') ||
291 ', Calling module = ' || P_Calling_Module ||
292 ', Amount = ' || to_char(P_Amount));
293
294 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
295 END IF;
296 APP_EXCEPTION.RAISE_EXCEPTION;
297
298 END Jl_Co_Ap_Do_Withholding ;
299
300
301 /**************************************************************************
302 * Private Procedure *
303 **************************************************************************/
304
305 /**************************************************************************
306 * *
307 * Name : Jl_Co_Ap_Calculate_AWT_Amounts *
308 * Purpose : This procedure performs all the withholding calculations *
309 * and generates the temporary distribution lines. *
310 * *
311 **************************************************************************/
312
313 PROCEDURE Jl_Co_Ap_Calculate_AWT_Amounts
314 (P_Invoice_Id IN Number,
315 P_AWT_Date IN Date,
316 P_Calling_Module IN Varchar2,
317 P_Create_Dists IN Varchar2,
318 P_Amount IN Number,
319 P_Last_Updated_By IN Number Default Null,
320 P_Last_Update_Login IN Number Default Null,
321 P_Program_Application_Id IN Number Default Null,
322 P_Program_Id IN Number Default Null,
323 P_Request_Id IN Number Default Null,
324 P_AWT_Success IN OUT NOCOPY Varchar2,
325 P_Calling_Sequence IN Varchar2
326 )
327 IS
328
329 -------------------------------
330 -- Local Variables Definition
331 -------------------------------
332 l_previous_awt_type_code Varchar2(30);
333 l_previous_tax_id Number;
334 l_current_vendor_id Number;
335 l_current_awt Number;
336 l_initial_awt Number;
337 l_tax_base_amt Number;
338 l_nit po_vendors.segment1%TYPE;
339 l_user_defd_formula_exists Boolean := FALSE;
340 l_gl_period_name ap_invoice_distributions.period_name%TYPE;
341 l_base_currency_code Varchar2(15);
342 debug_info Varchar2(100);
343 current_calling_sequence Varchar2(2000);
344
345 ---------------------------------------------------------
346 -- Cursor to select all distinct NIT within the invoice
347 ---------------------------------------------------------
348 CURSOR c_nit(Inv_Id Number)
349 IS
350 SELECT
351 distinct nvl(substr(apid.global_attribute2,1,30),pove.segment1) nit
352 FROM
353 ap_invoices apin,
354 ap_invoice_distributions apid,
355 po_vendors pove
356 WHERE
357 apid.invoice_id = apin.invoice_id
358 AND pove.vendor_id = apin.vendor_id
359 AND apin.invoice_id = Inv_Id;
360
361 --------------------------------------------------------------------------
362 -- Cursor to select all the withholding tax types and names with same NIT
363 -- and associated to the invoice
364 --------------------------------------------------------------------------
365 CURSOR c_withholdings(Inv_Id Number,Nit Varchar2)
366 IS
367 SELECT
368 jlst.awt_type_code awt_type_code,
369 jlsc.tax_id tax_id,
370 apin.invoice_id invoice_id,
371 pove2.vendor_id vendor_id,
372 apid.invoice_distribution_id invoice_distribution_id,
373 nvl(apin.base_amount, apin.invoice_amount) invoice_amount,
377 ap_invoices apin,
374 nvl(apid.base_amount, apid.amount) line_amount
375 FROM
376 jl_zz_ap_inv_dis_wh jlwh,
378 ap_invoice_distributions apid,
379 jl_zz_ap_supp_awt_types jlst,
380 jl_zz_ap_sup_awt_cd jlsc,
381 jl_zz_ap_awt_types jlat,
382 po_vendors pove,
383 po_vendors pove2
384 WHERE
385 apid.invoice_id = jlwh.invoice_id
386 AND apid.invoice_distribution_id = jlwh.invoice_distribution_id
387 AND apin.invoice_id = apid.invoice_id
388 AND pove.vendor_id = apin.vendor_id
389 AND pove2.segment1 = nvl(apid.global_attribute2,pove.segment1)
390 AND jlwh.supp_awt_code_id = jlsc.supp_awt_code_id
391 AND jlsc.supp_awt_type_id = jlst.supp_awt_type_id
392 AND jlat.awt_type_code = jlst.awt_type_code
393 AND jlwh.invoice_id = Inv_Id
394 AND nvl(apid.global_attribute2,pove.segment1)= Nit
395 AND NVL(apid.reversal_flag, 'N') <> 'Y' -- bug 7693731 Colombia AWT reverse
396 ORDER BY
397 jlat.user_defined_formula_flag,
398 jlst.awt_type_code,
399 jlsc.tax_id;
400
401
402
403 ------------------------
404 -- Records Declaration
405 ------------------------
406 rec_withholding c_withholdings%ROWTYPE;
407 rec_awt_type jl_zz_ap_awt_types%ROWTYPE;
408 rec_awt_name Jl_Zz_Ap_Withholding_Pkg.Rec_AWT_Code;
409 rec_suppl_awt_type jl_zz_ap_supp_awt_types%ROWTYPE;
410 rec_suppl_awt_name jl_zz_ap_sup_awt_cd%ROWTYPE;
411
412 -------------------------
413 -- Table Declaration
414 -------------------------
415 tab_withholdings Jl_Zz_Ap_Withholding_Pkg.Tab_Withholding;
416 tab_invoice_wh Jl_Zz_Ap_Withholding_Pkg.Tab_All_Withholding;
417
418 BEGIN
419
420 current_calling_sequence := 'JL_CO_AP_WITHHOLDING_PKG.Jl_Co_Ap_Calculate_AWT_Amounts';
421
422 ------------------------------------------
423 -- Opens the cursor to select all the NIT
424 ------------------------------------------
425 debug_info := 'Open Cursor to select all NIT within the invoice';
426 OPEN c_nit(P_Invoice_Id);
427 debug_info := 'Fetch cursor for each NIT selected';
428 FETCH c_nit INTO l_nit;
429 IF (c_nit%NOTFOUND) THEN
430 RETURN;
431 END IF;
432
433 ------------------------------
434 -- Loop for each NIT obtained
435 ------------------------------
436 LOOP
437
438 ---------------------------------------------------------------
439 -- Initializes a PL/SQL table to store all withholding details
440 ---------------------------------------------------------------
441 IF (tab_invoice_wh IS NOT NULL) THEN
442 tab_invoice_wh.DELETE;
443 END IF;
444
445 ---------------------------------------------------------------------
446 -- Checks whether there exist atleast one type with the user defined
447 -- formula set to 'Y'
448 ---------------------------------------------------------------------
449 debug_info := 'Call a function to check for user defined formula';
450 l_user_defd_formula_exists := User_Defined_Formula_Exists
451 (P_Invoice_Id,
452 l_nit);
453 ---------------------------------------------------------------
454 -- Opens the cursor to select all the withholdings to process
455 ---------------------------------------------------------------
456 debug_info := 'Open cursor for all the withholdings with same NIT';
457 OPEN c_withholdings(P_Invoice_Id,l_nit);
458 debug_info := 'Fetch cursor for each withholding';
459 FETCH c_withholdings INTO rec_withholding;
460 IF (c_withholdings%FOUND) THEN
461
462 ---------------------------
463 -- Gets generic parameters
464 ---------------------------
465 l_base_currency_code := Jl_Zz_Ap_Withholding_Pkg.Get_Base_Currency_Code;
466 l_gl_period_name := Jl_Zz_Ap_Withholding_Pkg.Get_GL_Period_Name
467 (P_AWT_Date);
468
469 ----------------------------------
470 -- Initialize auxillary variables
471 ----------------------------------
472 l_current_vendor_id := rec_withholding.vendor_id;
473 l_previous_awt_type_code := rec_withholding.awt_type_code;
474 l_previous_tax_id := rec_withholding.tax_id;
475
476 -------------------------------------------------------------
477 -- Obtains all the information associated to the withholding
478 -- taxes and initializes a PL/SQL table to store them
479 -------------------------------------------------------------
480 Initialize_Withholdings (rec_withholding.vendor_id,
481 rec_withholding.awt_type_code,
482 rec_withholding.tax_id,
483 rec_awt_type,
484 rec_awt_name,
485 rec_suppl_awt_type,
486 rec_suppl_awt_name,
487 tab_withholdings);
488
489
490 l_current_awt := 0;
491 l_initial_awt := 1;
492
493 ---------------------------------------------------------------
494 -- Loop for each withholding tax type within the invoice with
495 -- same NIT
496 ---------------------------------------------------------------
497 LOOP
498
499 ---------------------------------------
500 -- Checks whether there are more taxes
501 ---------------------------------------
502 IF (c_withholdings%NOTFOUND) THEN
503 ------------------------------------------------
504 -- Process the withholding tax name information
508 rec_awt_type,
505 ------------------------------------------------
506 Jl_Zz_Ap_Withholding_Pkg.Process_Withholding_Name
507 (l_current_vendor_id,
509 rec_awt_name,
510 rec_suppl_awt_type,
511 rec_suppl_awt_name,
512 P_AWT_Date,
513 tab_withholdings,
514 l_initial_awt,
515 l_current_awt,
516 tab_invoice_wh,
517 P_AWT_Success);
518 IF (P_AWT_Success <> 'SUCCESS') THEN
519 CLOSE c_withholdings;
520 CLOSE c_nit;
521 RETURN;
522 END IF;
523
524 -----------------------------------------------------
525 -- Process previous withholding tax type information.
526 -- Prorates the withheld amounts, if applicable and
527 -- Inserts temporary distribution lines
528 -----------------------------------------------------
529 Process_Withholdings (l_current_vendor_id,
530 rec_awt_type,
531 rec_suppl_awt_type,
532 P_AWT_Date,
533 l_gl_period_name,
534 l_base_currency_code,
535 l_user_defd_formula_exists,
536 l_nit,
537 tab_withholdings,
538 tab_invoice_wh,
539 P_AWT_Success,
540 P_Last_Updated_By,
541 P_Last_Update_Login,
542 P_Program_Application_Id,
543 P_Program_Id,
544 P_Request_Id,
545 P_Calling_Module
546 );
547
548 -------------------------------------------------------
549 -- Check whether the withholding tax type has changed
550 -------------------------------------------------------
551 ELSIF (rec_withholding.awt_type_code<>l_previous_awt_type_code) THEN
552
553 -------------------------------------------------
554 -- Process previous withholding tax information
555 -------------------------------------------------
556 Jl_Zz_Ap_Withholding_Pkg.Process_Withholding_Name
557 (l_current_vendor_id,
558 rec_awt_type,
559 rec_awt_name,
560 rec_suppl_awt_type,
561 rec_suppl_awt_name,
562 P_AWT_Date,
563 tab_withholdings,
564 l_initial_awt,
565 l_current_awt,
566 tab_invoice_wh,
567 P_AWT_Success);
568
569 IF (P_AWT_Success <> 'SUCCESS') THEN
570 CLOSE c_withholdings;
571 CLOSE c_nit;
572 RETURN;
573 END IF;
574
575 -----------------------------------------------------
576 -- Process previous withholding tax type information.
577 -- Prorates the withheld amounts, if applicable and
578 -- Inserts temporary distribution lines
579 -----------------------------------------------------
580 Process_Withholdings (l_current_vendor_id,
581 rec_awt_type,
582 rec_suppl_awt_type,
583 P_AWT_Date,
584 l_gl_period_name,
585 l_base_currency_code,
586 l_user_defd_formula_exists,
587 l_nit,
588 tab_withholdings,
589 tab_invoice_wh,
590 P_AWT_Success,
591 P_Last_Updated_By,
592 P_Last_Update_Login,
593 P_Program_Application_Id,
594 P_Program_Id,
595 P_Request_Id,
596 P_Calling_Module
597 );
598
599 -------------------------------------------------------------
600 -- Obtains all the information associated to the wittholding
601 -- taxes and initializes a PL/SQL table to store them
602 -------------------------------------------------------------
603 Initialize_Withholdings (rec_withholding.vendor_id,
604 rec_withholding.awt_type_code,
605 rec_withholding.tax_id,
606 rec_awt_type,
607 rec_awt_name,
608 rec_suppl_awt_type,
609 rec_suppl_awt_name,
610 tab_withholdings);
611
612 --------------------------------------
613 -- Re-initializes auxillary variables
614 --------------------------------------
615
616 l_current_awt := 0;
617 l_initial_awt := 1;
618 l_previous_awt_type_code := rec_withholding.awt_type_code;
619 l_previous_tax_id := rec_withholding.tax_id;
620
621 -------------------------------------------
622 -- Checks whether the tax name has changed
623 -------------------------------------------
624 ELSIF (rec_withholding.tax_id <> l_previous_tax_id) THEN
625
626
627 Jl_Zz_Ap_Withholding_Pkg.Print_Tax_Names (tab_withholdings);
631 ------------------------------------------------
628
629 ------------------------------------------------
630 -- Process previous withholding tax information
632
633 Jl_Zz_Ap_Withholding_Pkg.Process_Withholding_Name
634 (l_current_vendor_id,
635 rec_awt_type,
636 rec_awt_name,
637 rec_suppl_awt_type,
638 rec_suppl_awt_name,
639 P_AWT_Date,
640 tab_withholdings,
641 l_initial_awt,
642 l_current_awt,
643 tab_invoice_wh,
644 P_AWT_Success);
645
646 IF (P_AWT_Success <> 'SUCCESS') THEN
647 CLOSE c_withholdings;
648 CLOSE c_nit;
649 RETURN;
650 END IF;
651 -------------------------------------------------
652 -- Obtains all the information associated to the
653 -- withholding tax name
654 -------------------------------------------------
655
656 Jl_Zz_Ap_Withholding_Pkg.Initialize_Withholding_Name
657 (rec_withholding.awt_type_code,
658 rec_withholding.tax_id,
659 rec_withholding.vendor_id,
660 rec_awt_name,
661 rec_suppl_awt_name);
662
663 -------------------------------------------
664 -- Re-initializes the auxillary variables
665 -------------------------------------------
666 l_previous_tax_id := rec_withholding.tax_id;
667 l_initial_awt := l_current_awt + 1;
668
669 END IF;
670
671 --------------------------------------------
672 -- Checks whether there are some more taxes
673 --------------------------------------------
674 EXIT WHEN c_withholdings%NOTFOUND;
675
676 -----------------------------------
677 -- Obtains the taxable base amount
678 -----------------------------------
679 l_tax_base_amt := Jl_Zz_Ap_Withholding_Pkg.Get_Taxable_Base_Amount
680 (rec_withholding.invoice_id,
681 rec_withholding.invoice_distribution_id,
682 rec_withholding.line_amount,
683 Null,
684 rec_withholding.invoice_amount,
685 rec_awt_type.taxable_base_amount_basis);
686
687 -----------------------------------------------------------
688 -- Stores the information of the current tax name into the
689 -- PL/SQL table
690 -----------------------------------------------------------
691 l_current_awt := l_current_awt + 1;
692 Jl_Zz_Ap_Withholding_Pkg.Store_Tax_Name
693 (tab_withholdings,
694 l_current_awt,
695 rec_withholding.invoice_id,
696 rec_withholding.invoice_distribution_id,
697 rec_withholding.awt_type_code,
698 rec_withholding.tax_id,
699 rec_awt_name.name,
700 rec_awt_name.tax_code_combination_id,
701 rec_awt_name.awt_period_type,
702 rec_awt_type.jurisdiction_type,
703 rec_withholding.line_amount,
704 l_tax_base_amt);
705
706 ------------------------------------
707 -- Fetches next tax type / tax name
708 ------------------------------------
709 FETCH c_withholdings into rec_withholding;
710
711 END LOOP; -- withholding by NIT
712
713 END IF; --c_withholdings is notfound no more withholdings for the nit
714
715 ---------------------------------
716 -- Closes the withholding cursor
717 ---------------------------------
718 debug_info := 'Close cursor for all withholdings with same NIT';
719 CLOSE c_withholdings;
720
721 --------------------
722 -- Fetches next NIT
723 --------------------
724 FETCH c_nit into l_nit;
725 EXIT WHEN c_nit%NOTFOUND;
726
727 END LOOP;
728
729 -------------------------
730 -- Closes the NIT cursor
731 -------------------------
732 debug_info := 'Close cursor for all selected NIT';
733 CLOSE c_nit;
734
735 EXCEPTION
736 WHEN others THEN
737 DECLARE
738 error_text Varchar2(512) := substr(sqlerrm, 1, 512);
739 BEGIN
740 P_AWT_Success := error_text;
741
742 IF (SQLCODE <> -20001) THEN
743 FND_MESSAGE.SET_NAME('JL','JL_ZZ_AP_DEBUG');
744 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
745 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
746 FND_MESSAGE.SET_TOKEN('PARAMETERS',
747 ' Invoice Id = ' || to_char(P_Invoice_Id) ||
748 ', Awt Date = ' || to_char(P_Awt_Date,'YYYY/MM/DD') ||
749 ', Calling Module = ' || P_Calling_Module ||
750 ', Create Dists = ' || P_Create_Dists ||
751 ', Amount = ' || to_char(P_Amount));
752
753 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
754 END IF;
755 APP_EXCEPTION.RAISE_EXCEPTION;
756 END;
757
758 END Jl_Co_Ap_Calculate_AWT_Amounts;
759
760
761 /**************************************************************************
762 * *
763 * Name : User_Defined_Formula_Exists *
764 * Purpose : This function returns TRUE, if there is atleast one *
768 **************************************************************************/
765 * type within this NIT, with the user defined formula *
766 * flag set to 'Y'. Otherwise, it returns FALSE *
767 * *
769
770 FUNCTION User_Defined_Formula_Exists
771 (P_Invoice_Id IN Number,
772 P_NIT IN Varchar2) RETURN BOOLEAN
773 IS
774 -------------------------------
775 -- Local Variables Definition
776 -------------------------------
777 l_user_defined_formula_flag jl_zz_ap_awt_types.user_defined_formula_flag%TYPE;
778 debug_info Varchar2(100);
779 current_calling_sequence Varchar2(2000);
780
781 --------------------------------------------------------------------------
782 -- Cursor to select the user defined formula flag of the withholding
783 -- types with same NIT and associated to the invoice
784 --------------------------------------------------------------------------
785 CURSOR c_user_defined_formula_flag(Inv_Id Number,Nit Varchar2)
786 IS
787 SELECT
788 jlat.user_defined_formula_flag user_defined_formula_flag
789 FROM
790 jl_zz_ap_inv_dis_wh jlwh,
791 ap_invoices apin,
792 ap_invoice_distributions apid,
793 jl_zz_ap_supp_awt_types jlst,
794 jl_zz_ap_sup_awt_cd jlsc,
795 jl_zz_ap_awt_types jlat,
796 po_vendors pove
797 WHERE
798 apid.invoice_id = jlwh.invoice_id
799 AND apid.invoice_distribution_id = jlwh.invoice_distribution_id
800 AND apin.invoice_id = apid.invoice_id
801 AND pove.vendor_id = apin.vendor_id
802 AND jlwh.supp_awt_code_id = jlsc.supp_awt_code_id
803 AND jlsc.supp_awt_type_id = jlst.supp_awt_type_id
804 AND jlat.awt_type_code = jlst.awt_type_code
805 AND jlwh.invoice_id = Inv_Id
806 AND nvl(apid.global_attribute2,pove.segment1)= Nit
807 AND nvl(jlat.user_defined_formula_flag,'N') = 'Y';
808
809
810 BEGIN
811
812 current_calling_sequence := 'JL_CO_AP_WITHHOLDING_PKG.User_Defined_Formula_Exists';
813
814 -------------------------------------------------------------------------
815 -- Opens the cursor to select all the user defined formula flag
816 -------------------------------------------------------------------------
817 debug_info := 'Open cursor to get user defined formula flag';
818 OPEN c_user_defined_formula_flag(P_Invoice_Id, P_NIT);
819 debug_info := 'Fetch from cursor to get user defined formula flag';
820 FETCH c_user_defined_formula_flag INTO l_user_defined_formula_flag;
821
822 --------------------------------------------------
823 -- Checks whether the cursor has fetched any row
824 -- Returns FALSE, if there are no rows fetched.
825 -- Otherwise, returns TRUE
826 --------------------------------------------------
827 IF (c_user_defined_formula_flag%NOTFOUND) THEN
828 CLOSE c_user_defined_formula_flag;
829 RETURN FALSE;
830 ELSE
831 CLOSE c_user_defined_formula_flag;
832 RETURN TRUE;
833
834 END IF;
835
836 EXCEPTION
837 WHEN others THEN
838 IF (SQLCODE <> -20001) THEN
839 FND_MESSAGE.SET_NAME('JL','JL_ZZ_AP_DEBUG');
840 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
841 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
842 FND_MESSAGE.SET_TOKEN('PARAMETERS',
843 ' Invoice Id = ' || to_char(P_Invoice_Id) ||
844 ', NIT = ' || P_NIT );
845
846 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
847 END IF;
848 APP_EXCEPTION.RAISE_EXCEPTION;
849
850 END User_Defined_Formula_Exists;
851
852 /**************************************************************************
853 * *
854 * Name : Initialize_Withholdings *
855 * Purpose : Obtains all the attributes for the current withholding *
856 * tax type and name. This procedure also initializes the *
857 * PL/SQL table to store the withholdings *
858 * *
859 **************************************************************************/
860 PROCEDURE Initialize_Withholdings
861 (P_Vendor_Id IN Number,
862 P_AWT_Type_Code IN Varchar2,
863 P_Tax_Id IN Number,
864 P_Rec_AWT_Type OUT NOCOPY jl_zz_ap_awt_types%ROWTYPE,
865 P_Rec_AWT_Name OUT NOCOPY Jl_Zz_Ap_Withholding_Pkg.Rec_AWT_Code,
866 P_Rec_Suppl_AWT_Type OUT NOCOPY jl_zz_ap_supp_awt_types%ROWTYPE,
867 P_Rec_Suppl_AWT_Name OUT NOCOPY jl_zz_ap_sup_awt_cd%ROWTYPE,
868 P_Wh_Table IN OUT NOCOPY Jl_Zz_Ap_Withholding_Pkg.Tab_Withholding)
869 IS
870 BEGIN
871
872 Jl_Zz_Ap_Withholding_Pkg.Initialize_Withholding_Type
873 (P_AWT_Type_Code,
874 P_Vendor_Id,
875 P_Rec_AWT_Type,
876 P_Rec_Suppl_AWT_Type);
877
878 Jl_Zz_Ap_Withholding_Pkg.Initialize_Withholding_Name
879 (P_AWT_Type_Code,
880 P_Tax_Id,
881 P_Vendor_Id,
882 P_Rec_AWT_Name,
883 P_Rec_Suppl_AWT_Name);
884
885 Jl_Zz_Ap_Withholding_Pkg.Initialize_Withholding_Table
886 (P_Wh_Table);
887
888 END Initialize_Withholdings;
889
890
891 /**************************************************************************
892 * *
893 * Name : Process_Withholdings *
894 * Purpose : Process the information for the current withholding tax *
895 * type and name *
896 * *
897 **************************************************************************/
898 PROCEDURE Process_Withholdings
899 (P_Vendor_Id IN Number,
900 P_Rec_AWT_Type IN jl_zz_ap_awt_types%ROWTYPE,
901 P_Rec_Suppl_AWT_Type IN jl_zz_ap_supp_awt_types%ROWTYPE,
902 P_AWT_Date IN Date,
903 P_GL_Period_Name IN Varchar2,
904 P_Base_Currency_Code IN Varchar2,
905 P_User_Defd_Formula IN Boolean,
906 P_NIT_Number IN Varchar2 Default null,
907 P_Tab_Withhold IN OUT NOCOPY Jl_Zz_Ap_Withholding_Pkg.Tab_Withholding,
908 P_Tab_All_Withhold IN OUT NOCOPY Jl_Zz_Ap_Withholding_Pkg.Tab_All_Withholding,
909 P_AWT_Success OUT NOCOPY Varchar2,
910 P_Last_Updated_By IN Number Default null,
911 P_Last_Update_Login IN Number Default null,
912 P_Program_Application_Id IN Number Default null,
913 P_Program_Id IN Number Default null,
914 P_Request_Id IN Number Default null,
915 P_Calling_Module IN Varchar2 Default null
916 )
917
918 IS
919 l_revised_amount_flag Boolean := FALSE;
920
921 BEGIN
922 ------------------------------------------------
923 -- Process previous withholding tax type
924 ------------------------------------------------
925 Jl_Zz_Ap_Withholding_Pkg.Process_Withholding_Type
926 (P_Rec_AWT_Type,
927 P_Rec_Suppl_AWT_Type,
928 P_AWT_Date,
929 P_Base_Currency_Code,
930 P_Tab_Withhold);
931
932 -------------------------------------------------
933 -- Prorates the withheld amounts, if applicable
934 -------------------------------------------------
935 IF ((P_user_defd_formula) AND
936 (nvl(P_Rec_AWT_Type.user_defined_formula_flag,'N') = 'N')) THEN
937 Jl_Zz_Ap_Withholding_Pkg.Prorate_Withholdings(P_Tab_Withhold,
938 P_Base_Currency_Code);
939 Jl_Zz_Ap_Withholding_Pkg.Store_Prorated_Withholdings
940 (P_Tab_Withhold,
941 P_Tab_All_Withhold);
942 END IF;
943
944 ---------------------------------------------------------
945 -- Determines whether revised taxable base amount should
946 -- be considered while storing into temporary table
947 ---------------------------------------------------------
948
949 IF (nvl(P_Rec_AWT_Type.user_defined_formula_flag,'N') = 'Y') THEN
950 l_revised_amount_flag := TRUE;
951 ELSE
952 l_revised_amount_flag := FALSE;
953 END IF;
954
955 ----------------------------------------
956 -- Insert Temporary Distributions Lines
957 ----------------------------------------
958 Jl_Zz_Ap_Withholding_Pkg.Store_Into_Temporary_Table
959 (P_Tab_Withhold,
960 P_Vendor_Id,
961 P_AWT_Date,
962 P_GL_Period_Name,
963 P_Base_Currency_Code,
964 l_revised_amount_flag,
965 FALSE, -- Prorated Amount Flag
966 FALSE, -- Zero WH Applicable
967 FALSE, -- Handle Bucket
968 P_AWT_Success,
969 P_Last_Updated_By,
970 P_Last_Update_Login,
971 P_Program_Application_Id,
972 P_Program_Id,
973 P_Request_Id,
974 P_Calling_Module,
975 null, -- checkrun name
976 null, -- checkrun id
977 null, -- payment number
978 'JL.CO.APXINWKB.DISTRIBUTIONS',
979 P_NIT_Number
980 );
981 IF (P_AWT_Success <> 'SUCCESS') THEN
982 RETURN;
983 END IF;
984
985 Jl_Zz_Ap_Withholding_Pkg.Print_Tax_Names (P_Tab_Withhold);
986
987 END Process_Withholdings;
988
989 END JL_CO_AP_WITHHOLDING_PKG;