[Home] [Help]
PACKAGE BODY: APPS.AP_EXTENDED_WITHHOLDING_PKG
Source
1 PACKAGE BODY AP_EXTENDED_WITHHOLDING_PKG AS
2 /* $Header: apexawtb.pls 120.7 2005/10/26 22:43:22 dbetanco noship $ */
3
4
5 /**************************************************************************
6 * *
7 * Name : Ap_Do_Extended_Withholding *
8 * Purpose : This is a dummy procedure to Encapsulate the calls for *
9 * Regional Extended Package *
10 * (JG_EXTENDED_WITHHOLDING_PKG). *
11 * *
12 **************************************************************************/
13 PROCEDURE Ap_Do_Extended_Withholding
14 (P_Invoice_Id IN Number,
15 P_Awt_Date IN Date,
16 P_Calling_Module IN Varchar2,
17 P_Amount IN Number,
18 P_Payment_Num IN Number Default null,
19 P_Checkrun_Name IN Varchar2 Default null,
20 P_Last_Updated_By IN Number,
21 P_Last_Update_Login IN Number,
22 P_Program_Application_Id IN Number Default null,
23 P_Program_Id IN Number Default null,
24 P_Request_Id IN Number Default null,
25 P_Awt_Success OUT NOCOPY Varchar2,
26 P_Invoice_Payment_Id IN Number Default null,
27 P_Check_Id IN Number Default null,
28 p_checkrun_id in number default null)
29
30 IS
31
32 l_return_value NUMBER := 0;
33
34 BEGIN
35 --------------------------------
36 -- Initializes output arguments
37 --------------------------------
38 P_Awt_Success := 'AWT Error';
39
40 l_return_value := JG_EXTENDED_WITHHOLDING_PKG.Jg_Do_Extended_Withholding
41 (
42 P_Invoice_Id
43 ,P_Awt_Date
44 ,P_Calling_module
45 ,P_Amount
46 ,P_Payment_Num
47 ,P_Checkrun_Name
48 ,p_checkrun_id
49 ,P_Last_Updated_By
50 ,P_Last_Update_Login
51 ,P_Program_Application_Id
52 ,P_Program_Id
53 ,P_Request_Id
54 ,P_Invoice_Payment_Id
55 ,P_Check_Id);
56
57 IF (l_return_value = TRUE_VALUE) THEN
58 P_Awt_Success := 'SUCCESS';
59 END IF;
60
61 END Ap_Do_Extended_Withholding;
62
63 /**************************************************************************
64 * *
65 * Name : Ap_Undo_Extended_Withholding *
66 * Purpose : This is a dummy procedure to Encapsulate the calls for *
67 * Regional Extended Package *
68 * (JG_EXTENDED_WITHHOLDING_PKG). *
69 * *
70 **************************************************************************/
71 PROCEDURE Ap_Undo_Extended_Withholding
72 (P_Parent_Id IN Number,
73 P_Calling_Module IN Varchar2,
74 P_Awt_Date IN Date,
75 P_New_Invoice_Payment_Id IN Number Default null,
76 P_Last_Updated_By IN Number,
77 P_Last_Update_Login IN Number,
78 P_Program_Application_Id IN Number Default null,
79 P_Program_Id IN Number Default null,
80 P_Request_Id IN Number Default null,
81 P_Awt_Success OUT NOCOPY Varchar2 ,
82 P_Dist_Line_No IN Number Default null,
83 P_New_Invoice_Id IN Number Default null,
84 P_New_Dist_Line_No IN Number Default null)
85
86 IS
87 l_return_value NUMBER := 0;
88
89 BEGIN
90 --------------------------------
91 -- Initializes output arguments
92 --------------------------------
93 P_Awt_Success := 'AWT Error';
94
95 l_return_value := JG_EXTENDED_WITHHOLDING_PKG.Jg_Undo_Extended_Withholding
96 (
97 P_Parent_Id
98 ,P_Calling_Module
99 ,P_Awt_Date
100 ,P_New_Invoice_Payment_Id
101 ,P_Last_Updated_By
102 ,P_Last_Update_Login
103 ,P_Program_Application_Id
104 ,P_Program_Id
105 ,P_Request_Id
106 ,P_Dist_Line_No
107 ,P_New_Invoice_Id
108 ,P_New_Dist_Line_No);
109
110 IF (l_return_value = TRUE_VALUE) THEN
111 P_Awt_Success := 'SUCCESS';
112 END IF;
113 END Ap_Undo_Extended_Withholding;
114
115 /**************************************************************************
116 * *
117 * Name : Ap_Undo_Temp_Ext_Withholding *
118 * Purpose : This is a dummy procedure to Encapsulate the calls for *
119 * Regional Extended Package *
120 * (JG_EXTENDED_WITHHOLDING_PKG). *
121 * *
122 **************************************************************************/
123 PROCEDURE Ap_Undo_Temp_Ext_Withholding
124 (P_Invoice_Id IN Number,
125 P_Vendor_Id IN Number Default null,
126 P_Payment_Num IN Number,
127 P_Checkrun_Name IN Varchar2,
128 P_Undo_Awt_Date IN Date,
129 P_Calling_Module IN Varchar2,
130 P_Last_Updated_By IN Number,
131 P_Last_Update_Login IN Number,
132 P_Program_Application_Id IN Number Default null,
133 P_Program_Id IN Number Default null,
134 P_Request_Id IN Number Default null,
135 P_Awt_Success OUT NOCOPY Varchar2,
136 P_checkrun_id in number default null)
137 IS
138 l_return_value NUMBER := 0;
139
140 BEGIN
141 --------------------------------
142 -- Initializes output arguments
143 --------------------------------
144 P_Awt_Success := 'AWT Error';
145
146 l_return_value := JG_EXTENDED_WITHHOLDING_PKG.Jg_Undo_Temp_Ext_Withholding
147 (
148 P_Invoice_Id
149 ,P_Vendor_Id
150 ,P_Payment_Num
151 ,P_Checkrun_Name
152 ,P_checkrun_id
153 ,P_Undo_Awt_Date
154 ,P_Calling_Module
155 ,P_Last_Updated_By
156 ,P_Last_Update_Login
157 ,P_Program_Application_Id
158 ,P_Program_Id
159 ,P_Request_Id);
160
161 IF (l_return_value = TRUE_VALUE) THEN
162 P_Awt_Success := 'SUCCESS';
163 END IF;
164
165 END Ap_Undo_Temp_Ext_Withholding;
166
167 /**************************************************************************
168 * *
169 * Name : Ap_Ext_Withholding_Default *
170 * Purpose : This is a dummy procedure to Encapsulate the calls for *
171 * Regional Extended Package *
172 * (JG_EXTENDED_WITHHOLDING_PKG). *
173 * *
174 **************************************************************************/
175 PROCEDURE Ap_Ext_Withholding_Default
176 (P_Invoice_Id IN Number,
177 P_Inv_Line_Num IN Number, --Bug 4554163
178 P_Inv_Dist_Id IN ap_invoice_distributions_all.invoice_distribution_id%TYPE,
179 P_Calling_Module IN Varchar2, --Bug 4554163
180 P_Parent_Dist_ID IN Number,
181 P_Awt_Success OUT NOCOPY Varchar2)
182
183 IS
184
185 l_return_value NUMBER := 0;
186 l_country_code Varchar2(10);
187 BEGIN
188 ------------------------
189 -- Get the Country Code
190 ------------------------
191 -- Bug# 1683944
192 -- Verify country code to avoid not necessary
193 -- calls.
194 -------------------------------
195
196 fnd_profile.get('JGZZ_COUNTRY_CODE', l_country_code);
197 IF (P_Calling_Module <> 'IMPORT') and ((l_country_code = 'AR') OR (l_country_code = 'CO')) THEN
198 --------------------------------
199 -- Initializes output arguments
200 --------------------------------
201 P_Awt_Success := 'AWT Error';
202
203 l_return_value := JG_EXTENDED_WITHHOLDING_PKG.Jg_Ext_Withholding_Default
204 (P_Invoice_Id,
205 P_Inv_Line_Num, -- Bug 4554163
206 P_Inv_Dist_Id,
207 P_Calling_Module,
208 P_Parent_Dist_ID);
209
210 IF (l_return_value = TRUE_VALUE) THEN
211 P_Awt_Success := 'SUCCESS';
212 END IF;
213 ELSIF (P_Calling_Module = 'IMPORT') and (l_country_code = 'BR') Then
214 --------------------------------
215 -- Initializes output arguments
216 --------------------------------
217 P_Awt_Success := 'AWT Error';
218
219 l_return_value := JG_EXTENDED_WITHHOLDING_PKG.Jg_Ext_Withholding_Default
220 (P_Invoice_Id,
221 P_Inv_Line_Num, -- Bug 4554163
222 P_Inv_Dist_Id,
223 P_Calling_Module,
224 P_Parent_Dist_ID);
225
226 IF (l_return_value = TRUE_VALUE) THEN
227 P_Awt_Success := 'SUCCESS';
228 END IF;
229 END IF;
230 END Ap_Ext_Withholding_Default;
231
232 /**************************************************************************
233 * *
234 * Name : Ap_Extended_Match *
235 * Purpose : This is a dummy procedure to Encapsulate the calls for *
236 * Regional Extended Package *
237 * (JG_EXTENDED_WITHHOLDING_PKG). *
238 * *
239 **************************************************************************/
240 PROCEDURE Ap_Extended_Match
244 P_Distribution_id IN Number Default null,
241 (P_Credit_Id IN Number,
242 P_Invoice_Id IN Number Default null,
243 P_Inv_Line_Num IN Number Default null,
245 P_Parent_Dist_ID IN Number Default null)
246 IS
247 BEGIN
248
249 JG_EXTENDED_WITHHOLDING_PKG.Jg_Extended_Match
250 ( P_Credit_Id
251 ,P_Invoice_Id
252 ,P_Inv_Line_Num --Bug 4554163
253 ,P_Distribution_id
254 ,P_Parent_Dist_ID);
255
256 END Ap_Extended_Match;
257
258 /**************************************************************************
259 * *
260 * Name : Ap_Extended_Insert_Dist *
261 * Purpose : This is a dummy procedure which will verify whether the *
262 * Regional Extended Package is installed (JG_EXTENDED_ *
263 * WITHHOLDING_PKG). If installed, then the routine to *
264 * insert distribution lines will be executed dynamically. *
265 * *
266 **************************************************************************/
267 PROCEDURE Ap_Extended_Insert_Dist
268 (P_Invoice_Id IN Number,
269 P_Invoice_Distribution_id IN Number, -- Add new Column
270 P_Distribution_Line_Number IN Number,
271 P_Line_Type IN Varchar2,
272 P_GL_Date IN Date,
273 P_Period_Name IN Varchar2,
274 P_Type_1099 IN Varchar2,
275 P_Income_Tax_Region IN Varchar2,
276 P_Amount IN Number,
277 P_Tax_Code_ID IN Number, -- Add new Column
278 P_Code_Combination_Id IN Number,
279 P_PA_Quantity IN Number,
280 P_Description IN Varchar2,
281 P_tax_recoverable_flag IN Varchar2, -- Add new Column
282 P_tax_recovery_rate IN Number, -- Add new Column
283 P_tax_code_override_flag IN Varchar2, -- Add new Column
284 P_tax_recovery_override_flag IN Varchar2, -- Add new Column
285 P_po_distribution_id IN Number, -- Add new Column
286 P_Attribute_Category IN Varchar2,
287 P_Attribute1 IN Varchar2,
288 P_Attribute2 IN Varchar2,
289 P_Attribute3 IN Varchar2,
290 P_Attribute4 IN Varchar2,
291 P_Attribute5 IN Varchar2,
292 P_Attribute6 IN Varchar2,
293 P_Attribute7 IN Varchar2,
294 P_Attribute8 IN Varchar2,
295 P_Attribute9 IN Varchar2,
296 P_Attribute10 IN Varchar2,
297 P_Attribute11 IN Varchar2,
298 P_Attribute12 IN Varchar2,
299 P_Attribute13 IN Varchar2,
300 P_Attribute14 IN Varchar2,
301 P_Attribute15 IN Varchar2,
302 P_Calling_Sequence IN Varchar2)
303 IS
304 l_Calling_Sequence VARCHAR2(150);
305
306 BEGIN
307 JG_EXTENDED_WITHHOLDING_PKG.Jg_Extended_Insert_Dist
308 (P_Invoice_Id
309 ,P_Invoice_Distribution_id
310 ,P_Distribution_Line_Number
311 ,P_Line_Type
312 ,P_GL_Date
313 ,P_Period_Name
314 ,P_Type_1099
315 ,P_Income_Tax_Region
316 ,P_Amount
317 ,P_Tax_Code_ID
318 ,P_Code_Combination_Id
319 ,P_PA_Quantity
320 ,P_Description
321 ,P_Tax_Recoverable_Flag
322 ,P_Tax_recovery_rate
323 ,P_Tax_code_override_flag
324 ,P_Tax_recovery_override_flag
325 ,P_Po_distribution_id
326 ,P_Attribute_Category
327 ,P_Attribute1
328 ,P_Attribute2
329 ,P_Attribute3
330 ,P_Attribute4
331 ,P_Attribute5
332 ,P_Attribute6
333 ,P_Attribute7
334 ,P_Attribute8
335 ,P_Attribute9
336 ,P_Attribute10
337 ,P_Attribute11
338 ,P_Attribute12
339 ,P_Attribute13
340 ,P_Attribute14
341 ,P_Attribute15
342 ,P_Calling_Sequence);
343
344 END Ap_Extended_Insert_Dist;
345
346 /**************************************************************************
347 * *
348 * Name : Ap_Extended_Withholding_Active *
349 * Purpose : This function checks whether the extended withholding *
350 * calculation routine will be called, depending on the *
351 * value of the "JG: Extended AWT Calculation" profile *
352 * option. The values returned by this function can be: *
353 * - TRUE: If the "JG: Extended AWT Calculation" profile *
354 * option exists and is set to 'Yes'. *
355 * - FALSE: If the "JG: Extended AWT Calculation" profile *
359 **************************************************************************/
356 * option does not exist, or is set to 'No', or *
357 * is null. *
358 * *
360 FUNCTION Ap_Extended_Withholding_Active RETURN Boolean
361 IS
362 BEGIN
363 IF (Fnd_Profile.Defined('JGZZ_EXTENDED_AWT_CALC')) THEN
364 IF (nvl(Fnd_Profile.Value('JGZZ_EXTENDED_AWT_CALC'), 'N') = 'Y') THEN
365
366 RETURN TRUE;
367
368 END IF;
369 END IF;
370 RETURN FALSE;
371
372 END Ap_Extended_Withholding_Active;
373
374 /**************************************************************************
375 * *
376 * Name : Ap_Extended_Withholding_Option *
377 * Purpose : This function returns the value of the "JG: Extended AWT *
378 * Calculation" profile option. They can be: *
379 * - 'Y': If the "JG: Extended AWT Calculation" profile *
380 * option exists and is set to 'Yes'. *
381 * - 'N': If the "JG: Extended AWT Calculation" profile *
382 * option does not exist, or is set to 'No', or *
383 * is null. *
384 * *
385 **************************************************************************/
386 FUNCTION Ap_Extended_Withholding_Option RETURN Varchar2
387 IS
388 BEGIN
389
390 IF (Ap_Extended_Withholding_Active) THEN
391 RETURN 'Y';
392 ELSE
393 RETURN 'N';
394 END IF;
395
396 END Ap_Extended_Withholding_Option;
397
398 /**************************************************************************
399 * *
400 * Name : Ap_Ext_Withholding_Prepay *
401 * Purpose : Extended Routine for carry over the withholdings from *
402 * Prepayment invoice Item line to Invoice Prepay Line. *
403 * *
404 **************************************************************************/
405 PROCEDURE Ap_Ext_Withholding_Prepay
406 (P_prepay_dist_id IN Number,
407 P_invoice_id IN Number,
408 P_inv_dist_id IN Number, --Bug 4554163
409 P_user_id IN Number,
410 P_last_update_login IN Number,
411 P_calling_sequence IN Varchar2)
412 IS
413
414 l_return_value NUMBER := 0;
415 l_calling_sequence VARCHAR2(1000);
416
417 BEGIN
418 --------------------------------
419 -- Initializes output arguments
420 --------------------------------
421 l_calling_sequence := p_calling_sequence||'AP_EXT_WITHHOLDING_PREPAY';
422
423 l_return_value := JG_EXTENDED_WITHHOLDING_PKG.Jg_Ext_Withholding_Prepay
424 (
425 P_prepay_dist_id
426 ,P_invoice_id
427 ,P_inv_dist_id --Bug 4554163
428 ,P_user_id
429 ,P_last_update_login
430 ,l_calling_sequence);
431 END Ap_Ext_Withholding_Prepay;
432
433 /**************************************************************************
434 * *
435 * Name : Check_With_Dis *
436 * Purpose : Extended Routine to validate whether a Withholding Tax *
437 * Code has been calculated or not for a given invoice *
438 * distribution line. *
439 * This routine is for Long-Term Offset vs Ext AWT *
440 * The solution has not been developed yet. *
441 * *
442 **************************************************************************/
443
444 FUNCTION Check_With_Dis
445 (P_Invoice_Distribution_Id ap_invoice_distributions.invoice_distribution_id%TYPE
446 ,P_Tax_Name ap_tax_codes.name%TYPE
447 ,P_Global_Attribute2 ap_invoice_distributions.global_attribute2%TYPE
448 ,P_Global_Attribute3 ap_invoice_distributions.global_attribute3%TYPE
449 ) return Varchar2
450 IS
451 -------------------------------------------------------------------------
452 -- Select 'EXIST' from jl_zz_ap_inv_dis_wh
453 -------------------------------------------------------------------------
454 CURSOR Invoice_Dis_Withholdings IS
455 SELECT 'EXIST' Awt
456 FROM ap_invoice_distributions apid
457 ,jl_zz_ap_inv_dis_wh jid
458 ,jl_zz_ap_sup_awt_cd jsw
459 ,ap_tax_codes apc
460 WHERE apid.invoice_distribution_id = P_Invoice_Distribution_Id
461 AND apid.invoice_id = jid.invoice_id
462 AND apid.distribution_line_number= jid.distribution_line_number
463 AND jid.supp_awt_code_id = jsw.supp_awt_code_id
464 AND apc.tax_id = jsw.tax_id
465 AND apc.name = P_Tax_Name
466 AND nvl(apid.global_attribute2,-1) = nvl(P_Global_Attribute2,-1);
467
468 find_awt Varchar2(10):= 'N';
469
470 BEGIN
471 IF (Ap_Extended_Withholding_Pkg.Ap_Extended_Withholding_Active) THEN
472 FOR db_reg IN Invoice_Dis_Withholdings LOOP
473 IF db_reg.awt = 'EXIST' THEN
474 find_awt := 'Y';
475 return(find_awt);
476 END IF;
477 END LOOP;
478 END IF;
479 return(find_awt);
480 EXCEPTION
481 WHEN OTHERS THEN
482 return(find_awt);
483 END Check_With_Dis;
484
485 END AP_EXTENDED_WITHHOLDING_PKG;