[Home] [Help]
PACKAGE: APPS.JL_ZZ_AP_WITHHOLDING_PKG
Source
1 PACKAGE JL_ZZ_AP_WITHHOLDING_PKG AUTHID CURRENT_USER AS
2 /* $Header: jlzzpwhs.pls 120.8.12010000.2 2009/01/06 11:39:30 nivnaray ship $ */
3
4
5
6 /**************************************************************************
7 * Constants Definition *
8 **************************************************************************/
9
10 AWT_SUCCESS CONSTANT Varchar2(10) := 'SUCCESS';
11 AWT_ERROR CONSTANT Varchar2(10) := 'AWT ERROR';
12
13
14
15 /**************************************************************************
16 * PL/SQL Records Definition *
17 **************************************************************************/
18
19
20 -------------------------------------------
21 -- Invoice/Payment Withholding Information
22 -------------------------------------------
23 TYPE Rec_Withholding IS RECORD
24 (
25 invoice_id Number,
26 invoice_distribution_id Number,
27 awt_type_code Varchar2(30),
28 jurisdiction_type Varchar2(30),
29 tax_id Number,
30 tax_name Varchar2(15),
31 tax_code_combination_id Number,
32 awt_period_type Varchar2(15),
33 rate_id Number,
34 line_amount Number,
35 taxable_base_amount Number,
36 revised_tax_base_amount Number,
37 withheld_amount Number,
38 prorated_amount Number,
39 invoice_payment_id Number,
40 applicable_flag Varchar2(1),
41 -- by zmohiudd for 1849986
42 payment_num Number,
43 exemption_amount Number);
44
45
46 -------------------------------
47 -- Amount Withheld Information
48 -------------------------------
49 TYPE Rec_All_Withholding IS RECORD
50 (
51 invoice_id Number,
52 invoice_distribution_id Number,
53 awt_type_code Varchar2(30),
54 tax_id Number,
55 jurisdiction_type Varchar2(30),
56 prorated_amount Number
57 );
58
59
60 -----------------------
61 -- Tax Name Attributes
62 -----------------------
63 TYPE Rec_AWT_Code IS RECORD
64 (
65 Tax_Id Number,
66 Name Varchar2(15),
67 Tax_Code_Combination_Id Number,
68 AWT_Period_Type Varchar2(15),
69 Foreign_Rate_Ind Varchar2(1),
70 Zone_Code Varchar2(30),
71 Item_Applic Varchar2(1),
72 Freight_Applic Varchar2(1),
73 Misc_Applic Varchar2(1),
74 Tax_Applic Varchar2(1),
75 Min_Tax_Base_Amt Number,
76 Min_Withheld_Amt Number,
77 Adj_Min_Base Varchar2(30),
78 Cumulative_Payment_Flag Varchar2(1),
79 Tax_Inclusive Varchar2(1),
80 Income_Tax_Rate Number,
81 First_Tax_Type Varchar2(30),
82 Second_Tax_Type Varchar2(30),
83 Municipal_Type Varchar2(1),
84 Reduction_Perc Number
85 );
86
87
88
89 -----------------------
90 -- Tax Rate Attributes
91 -----------------------
92 TYPE Rec_AWT_Rate IS RECORD
93 (
94 Tax_Rate_Id Number,
95 Tax_Rate Number,
96 Rate_Type Varchar2(25),
97 Amount_To_Subtract Number,
98 Amount_To_Add Number
99 );
100
101
102
103 /**************************************************************************
104 * PL/SQL Tables Definition *
105 **************************************************************************/
106
107
108 -------------------------------------
109 -- Invoice/Payment Withholding Table
110 -------------------------------------
111 TYPE Tab_Withholding IS TABLE OF Rec_Withholding
112 INDEX BY BINARY_INTEGER;
113
114
115 --------------------------
116 -- Amount Withheld Table
117 --------------------------
118 TYPE Tab_All_Withholding IS TABLE OF Rec_All_Withholding
119 INDEX BY BINARY_INTEGER;
120
121
122
123
124 /**************************************************************************
125 * Public Procedures *
126 **************************************************************************/
127
128
129
130 /**************************************************************************
131 * *
132 * Name : Get_Withholding_Options *
133 * Purpose : Obtains all the withholding setup options from AP_SYSTEM_ *
134 * PARAMETERS table *
135 * *
136 **************************************************************************/
137 PROCEDURE Get_Withholding_Options (P_Create_Distr OUT NOCOPY Varchar2,
138 P_Create_Invoices OUT NOCOPY Varchar2);
139
140
141
142
143 /**************************************************************************
144 * *
145 * Name : Get_GL_Period_Name *
146 * Purpose : Returns the period name for a particular date. *
147 * *
148 **************************************************************************/
149 FUNCTION Get_GL_Period_Name (P_AWT_Date IN Date)
150 RETURN VARCHAR2;
151
152
153
154
155 /**************************************************************************
156 * *
157 * Name : Get_Base_Currency_Code *
158 * Purpose : Returns the functional currency code (from AP_SYSTEM_ *
159 * PARAMETERS) *
160 * *
161 **************************************************************************/
162 FUNCTION Get_Base_Currency_Code RETURN VARCHAR2;
163
164
165
166
167 /**************************************************************************
168 * *
169 * Name : Initialize_Withholding_Table *
170 * Purpose : Initialize the PL/SQL table to store the withholding tax *
171 * names. *
172 * *
173 **************************************************************************/
174 PROCEDURE Initialize_Withholding_Table (P_Wh_Table IN OUT NOCOPY Tab_Withholding);
175
176
177
178
179 /**************************************************************************
180 * *
181 * Name : Initialize_Withholding_Type *
182 * Purpose : Obtains all the information associated to the current *
183 * withholding tax type and for a particular supplier: *
184 * 1. Minimum taxable base amount *
185 * 2. Minimum withheld amount *
186 * 3. Associated attributes (from JL_ZZ_AP_AWT_TYPES) *
187 * 4. Supplier exemptions *
188 * 5. Multilateral contribution *
189 * *
190 **************************************************************************/
191 PROCEDURE Initialize_Withholding_Type
192 (P_AWT_Type_Code IN Varchar2,
193 P_Vendor_Id IN Number,
194 P_Rec_AWT_Type OUT NOCOPY jl_zz_ap_awt_types%ROWTYPE,
195 P_Rec_Suppl_AWT_Type OUT NOCOPY jl_zz_ap_supp_awt_types%ROWTYPE);
196
197
198
199
200 /**************************************************************************
201 * *
202 * Name : Initialize_Withholding_Name *
203 * Purpose : Obtains all the information associated to the current *
204 * tax name and for a particular supplier: *
205 * 1. Minimum taxable base amount *
206 * 2. Minimum withheld amount *
207 * 3. Associated global attributes (from AP_TAX_CODES) *
208 * 4. Supplier exemptions *
209 * *
210 **************************************************************************/
211 PROCEDURE Initialize_Withholding_Name
212 (P_AWT_Type_Code IN Varchar2,
213 P_Tax_Id IN Number,
214 P_Vendor_Id IN Number,
215 P_AWT_Name OUT NOCOPY Rec_AWT_Code,
216 P_Rec_Suppl_AWT_Name OUT NOCOPY jl_zz_ap_sup_awt_cd%ROWTYPE,
217 P_CODE_ACCOUNTING_DATE IN DATE Default NULL); -- Argentina AWT ER 6624809
218
219
220
221
222 /**************************************************************************
223 * *
224 * Name : Get_Taxable_Base_Amount *
225 * Purpose : Obtains the taxable base amount for a particular tax name *
226 * This amount is calculated as follows: *
227 * * The distribution line amount for those invoice based *
228 * withholding taxes *
229 * * The proportional payment amount for those payment based *
230 * withholding taxes *
231 * *
232 **************************************************************************/
233 FUNCTION Get_Taxable_Base_Amount
234 (P_Invoice_Id IN Number,
235 P_Distr_Line_No IN Number,
236 P_Line_Amount IN Number,
237 P_Payment_Amount IN Number Default null,
238 P_Invoice_Amount IN Number,
239 P_Tax_Base_Amount_Basis IN Varchar2) RETURN NUMBER;
240
241
242
243
244 /**************************************************************************
245 * *
246 * Name : Store_Tax_Name *
247 * Purpose : Put the information regarding the current tax name of the *
248 * payment into the PL/SQL table *
249 * *
250 **************************************************************************/
251 PROCEDURE Store_Tax_Name
252 (P_Tab_Withhold IN OUT NOCOPY Tab_Withholding,
253 P_Current_AWT IN Number,
254 P_Invoice_Id IN Number,
255 P_Distr_Line_No IN Number,
256 P_AWT_Type_Code IN Varchar2,
257 P_Tax_Id IN Number,
258 P_Tax_Name IN Varchar2,
259 P_Tax_Code_Comb_Id IN Number,
260 P_AWT_Period_Type IN Varchar2,
261 P_Jurisdiction_Type IN Varchar2,
262 P_Line_Amount IN Number,
263 P_Taxable_Base_Amount IN Number,
264 P_Invoice_Payment_Id IN Number Default null,
265 -- by Zmohiudd for bug 1849986
266 P_Payment_Num IN Number Default null);
267
268
269
270 /**************************************************************************
271 * *
272 * Name : Process_Withholding_Name *
273 * Purpose : Process the information for each different tax name for *
274 * a particular withholding tax type. It means: *
275 * 1. Obtains cumulative figures (when applicable) *
276 * 2. Gets the tax rate (checking the effective dates and *
277 * taxable base amount) *
278 * 3. Performs the calculation to obtain the withheld amount *
279 * and applies all the validations that are applicable *
283 PROCEDURE Process_Withholding_Name
280 * at withholding tax name level. *
281 * *
282 **************************************************************************/
284 (P_Vendor_Id IN Number,
285 P_Rec_AWT_Type IN jl_zz_ap_awt_types%ROWTYPE,
286 P_Rec_AWT_Name IN Rec_AWT_CODE,
287 P_Rec_Suppl_AWT_Type IN jl_zz_ap_supp_awt_types%ROWTYPE,
288 P_Rec_Suppl_AWT_Name IN jl_zz_ap_sup_awt_cd%ROWTYPE,
289 P_AWT_Date IN Date,
290 P_Tab_Withhold IN OUT NOCOPY Tab_Withholding,
291 P_Tax_Name_From IN Number,
292 P_Tax_Name_To IN Number,
293 P_Tab_All_Withhold IN OUT NOCOPY Tab_All_Withholding,
294 P_AWT_Success OUT NOCOPY Varchar2);
295
296
297
298
299 /**************************************************************************
300 * *
301 * Name : Prorate_Withholdings *
302 * Purpose : Prorates all the withholdings included into the PL/SQL *
303 * table. *
304 * *
305 **************************************************************************/
306 PROCEDURE Prorate_Withholdings
307 (P_Tab_Withhold IN OUT NOCOPY Tab_Withholding,
308 P_Currency_Code IN Varchar2);
309
310
311
312
313 /**************************************************************************
314 * *
315 * Name : Store_Into_Temporary_Table *
319 **************************************************************************/
316 * Purpose : Transfers all the withholding taxes stored into the PL/SQL*
317 * table to the temporary table (AP_AWT_TEMP_DISTRIBUTIONS) *
318 * *
320 PROCEDURE Store_Into_Temporary_Table
321 (P_Tab_Withhold IN Tab_Withholding,
322 P_Vendor_Id IN Number,
323 P_AWT_Date IN Date,
324 P_GL_Period_Name IN Varchar2,
325 P_Base_Currency_Code IN Varchar2,
326 P_Revised_Amount_Flag IN Boolean,
327 P_Prorated_Amount_Flag IN Boolean,
328 P_Zero_WH_Applicable IN Boolean,
329 P_Handle_Bucket IN Boolean,
330 P_AWT_Success OUT NOCOPY Varchar2,
331 P_Last_Updated_By IN Number Default null,
332 P_Last_Update_Login IN Number Default null,
333 P_Program_Application_Id IN Number Default null,
334 P_Program_Id IN Number Default null,
335 P_Request_Id IN Number Default null,
336 P_Calling_Module IN Varchar2 Default null,
337 P_Checkrun_Name IN Varchar2 Default null,
338 P_Checkrun_id IN Number Default null,
339 P_Payment_Num IN Number Default null,
340 P_Global_Attr_Category IN Varchar2 Default null,
341 P_NIT_Number IN Varchar2 Default null);
342
343
344
345
346 /**************************************************************************
347 * *
348 * Name : Process_Withholding_Type *
349 * Purpose : Process the information for each different withholding *
350 * tax type included within the payment. *
351 * *
352 **************************************************************************/
353 PROCEDURE Process_Withholding_Type
354 (P_Rec_AWT_Type IN jl_zz_ap_awt_types%ROWTYPE,
355 P_Rec_Suppl_AWT_Type IN jl_zz_ap_supp_awt_types%ROWTYPE,
356 P_AWT_Date IN Date,
357 P_Currency_Code IN Varchar2,
358 P_Tab_Withhold IN OUT NOCOPY Tab_Withholding);
359
360
361
362
363 /**************************************************************************
364 * *
365 * Name : Store_Prorated_Withholdings *
366 * Purpose : Transfers the Prorated Withholding details, from one *
367 * PL/SQL table to another *
368 * *
369 **************************************************************************/
370 PROCEDURE Store_Prorated_Withholdings
371 (P_Tab_Withhold IN Tab_Withholding,
372 P_Tab_All_Withhold IN OUT NOCOPY Tab_All_Withholding);
373
374
375
376
377 /**************************************************************************
378 * *
379 * Name : Print_Tax_Names *
380 * Purpose : This procedure shows all the elements of the PL/SQL table *
381 * (just for debug purposes) *
382 * *
383 **************************************************************************/
384 PROCEDURE Print_Tax_Names (P_Tab_Payment_Wh IN Tab_Withholding);
385
386
387
388
389 /**************************************************************************
390 * *
391 * Name : Jl_Zz_Ap_Extended_Match *
392 * Purpose : Regional Extended Routine for Matching *
393 * *
394 **************************************************************************/
395 --
396 -- R12 KI
397 --
398 PROCEDURE Jl_Zz_Ap_Extended_Match
399 (P_Credit_Id IN Number,
400 P_Invoice_Id IN Number Default null, -- Bug 4559478
401 P_Inv_Line_Num IN Number Default null,
402 P_Distribution_id IN Number Default null,
403 P_Parent_Dist_ID IN Number Default null);
404
405 /**************************************************************************
406 * *
407 * Name : Jl_Zz_Ap_Ext_Insert_Dist *
408 * Purpose : Regional Extended Routine for Insertion *
409 * *
410 **************************************************************************/
411 PROCEDURE Jl_Zz_Ap_Ext_Insert_Dist
412 (P_Invoice_Id IN Number,
413 P_Invoice_Distribution_id IN Number, -- Add new Column
414 P_Distribution_Line_Number IN Number,
415 P_Line_Type IN Varchar2,
416 P_GL_Date IN Date,
417 P_Period_Name IN Varchar2,
418 P_Type_1099 IN Varchar2,
419 P_Income_Tax_Region IN Varchar2,
420 P_Amount IN Number,
421 P_Tax_Code_ID IN Number, -- Add new Column
422 P_Code_Combination_Id IN Number,
423 P_PA_Quantity IN Number,
424 P_Description IN Varchar2,
425 P_tax_recoverable_flag IN Varchar2, -- Add new Column
426 P_tax_recovery_rate IN Number, -- Add new Column
427 P_tax_code_override_flag IN Varchar2, -- Add new Column
428 P_tax_recovery_override_flag IN Varchar2, -- Add new Column
429 P_po_distribution_id IN Number, -- Add new Column
430 P_Attribute_Category IN Varchar2,
431 P_Attribute1 IN Varchar2,
432 P_Attribute2 IN Varchar2,
433 P_Attribute3 IN Varchar2,
434 P_Attribute4 IN Varchar2,
435 P_Attribute5 IN Varchar2,
436 P_Attribute6 IN Varchar2,
437 P_Attribute7 IN Varchar2,
438 P_Attribute8 IN Varchar2,
439 P_Attribute9 IN Varchar2,
440 P_Attribute10 IN Varchar2,
441 P_Attribute11 IN Varchar2,
442 P_Attribute12 IN Varchar2,
443 P_Attribute13 IN Varchar2,
444 P_Attribute14 IN Varchar2,
445 P_Attribute15 IN Varchar2,
446 P_Calling_Sequence IN Varchar2);
447
448 /*************************************************************************
449 * Name : Validate_Multiple_Bal_Seg *
450 * Purpose : Rountine to check whether there exists multiple balancing *
451 * segments within invoice distributions or tax code *
452 * *
453 **************************************************************************/
454
455 -- Fix for bug 1770433
456 FUNCTION Validate_Multiple_Bal_Seg
457 (P_Invoice_Id ap_invoices.invoice_id%TYPE
458 ) return Varchar2;
459
460 /*************************************************************************
461 * Name : Validate_Mult_BS_GateWay *
462 * Purpose : Rountine to check whether there exists multiple balancing *
463 * segments within invoice distributions or tax code *
464 * for Invoice Gateway *
465 * *
466 **************************************************************************/
467 -- Fix for bug 1770433
468 FUNCTION Validate_Mult_BS_GateWay
469 (P_Invoice_Id ap_invoices.invoice_id%TYPE
470 ) return Varchar2;
471
472 /*************************************************************************
473 * Name : Dynamic_Call_Get_BalSeg *
474 * Purpose : Encapsulate Dynamic Call to get_auto_offsets_segments *
475 * *
476 **************************************************************************/
477
478 FUNCTION Dynamic_Call_Get_BalSeg
479 (P_ccid IN Number ) return Varchar2;
480
481 END JL_ZZ_AP_WITHHOLDING_PKG;