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