[Home] [Help]
PACKAGE: APPS.JL_ZZ_AP_WITHHOLDING_PKG
Source
1 PACKAGE JL_ZZ_AP_WITHHOLDING_PKG AS
2 /* $Header: jlzzpwhs.pls 120.8 2005/11/28 19:47:12 dbetanco 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
218
219
220
221 /**************************************************************************
222 * *
223 * Name : Get_Taxable_Base_Amount *
224 * Purpose : Obtains the taxable base amount for a particular tax name *
225 * This amount is calculated as follows: *
226 * * The distribution line amount for those invoice based *
227 * withholding taxes *
228 * * The proportional payment amount for those payment based *
229 * withholding taxes *
230 * *
231 **************************************************************************/
232 FUNCTION Get_Taxable_Base_Amount
233 (P_Invoice_Id IN Number,
234 P_Distr_Line_No IN Number,
235 P_Line_Amount IN Number,
236 P_Payment_Amount IN Number Default null,
237 P_Invoice_Amount IN Number,
238 P_Tax_Base_Amount_Basis IN Varchar2) RETURN NUMBER;
239
240
241
242
243 /**************************************************************************
244 * *
245 * Name : Store_Tax_Name *
246 * Purpose : Put the information regarding the current tax name of the *
247 * payment into the PL/SQL table *
248 * *
249 **************************************************************************/
250 PROCEDURE Store_Tax_Name
251 (P_Tab_Withhold IN OUT NOCOPY Tab_Withholding,
252 P_Current_AWT IN Number,
253 P_Invoice_Id IN Number,
254 P_Distr_Line_No IN Number,
255 P_AWT_Type_Code IN Varchar2,
256 P_Tax_Id IN Number,
257 P_Tax_Name IN Varchar2,
258 P_Tax_Code_Comb_Id IN Number,
259 P_AWT_Period_Type IN Varchar2,
260 P_Jurisdiction_Type IN Varchar2,
261 P_Line_Amount IN Number,
262 P_Taxable_Base_Amount IN Number,
263 P_Invoice_Payment_Id IN Number Default null,
264 -- by Zmohiudd for bug 1849986
265 P_Payment_Num IN Number Default null);
266
267
268
269 /**************************************************************************
270 * *
271 * Name : Process_Withholding_Name *
272 * Purpose : Process the information for each different tax name for *
273 * a particular withholding tax type. It means: *
274 * 1. Obtains cumulative figures (when applicable) *
275 * 2. Gets the tax rate (checking the effective dates and *
276 * taxable base amount) *
277 * 3. Performs the calculation to obtain the withheld amount *
278 * and applies all the validations that are applicable *
279 * at withholding tax name level. *
280 * *
281 **************************************************************************/
282 PROCEDURE Process_Withholding_Name
283 (P_Vendor_Id IN Number,
284 P_Rec_AWT_Type IN jl_zz_ap_awt_types%ROWTYPE,
285 P_Rec_AWT_Name IN Rec_AWT_CODE,
286 P_Rec_Suppl_AWT_Type IN jl_zz_ap_supp_awt_types%ROWTYPE,
287 P_Rec_Suppl_AWT_Name IN jl_zz_ap_sup_awt_cd%ROWTYPE,
288 P_AWT_Date IN Date,
289 P_Tab_Withhold IN OUT NOCOPY Tab_Withholding,
290 P_Tax_Name_From IN Number,
291 P_Tax_Name_To IN Number,
295
292 P_Tab_All_Withhold IN OUT NOCOPY Tab_All_Withholding,
293 P_AWT_Success OUT NOCOPY Varchar2);
294
296
297
298 /**************************************************************************
299 * *
300 * Name : Prorate_Withholdings *
301 * Purpose : Prorates all the withholdings included into the PL/SQL *
302 * table. *
303 * *
304 **************************************************************************/
305 PROCEDURE Prorate_Withholdings
306 (P_Tab_Withhold IN OUT NOCOPY Tab_Withholding,
307 P_Currency_Code IN Varchar2);
308
309
310
311
312 /**************************************************************************
313 * *
314 * Name : Store_Into_Temporary_Table *
315 * Purpose : Transfers all the withholding taxes stored into the PL/SQL*
316 * table to the temporary table (AP_AWT_TEMP_DISTRIBUTIONS) *
317 * *
318 **************************************************************************/
319 PROCEDURE Store_Into_Temporary_Table
320 (P_Tab_Withhold IN Tab_Withholding,
321 P_Vendor_Id IN Number,
322 P_AWT_Date IN Date,
323 P_GL_Period_Name IN Varchar2,
324 P_Base_Currency_Code IN Varchar2,
325 P_Revised_Amount_Flag IN Boolean,
326 P_Prorated_Amount_Flag IN Boolean,
327 P_Zero_WH_Applicable IN Boolean,
328 P_Handle_Bucket IN Boolean,
329 P_AWT_Success OUT NOCOPY Varchar2,
330 P_Last_Updated_By IN Number Default null,
331 P_Last_Update_Login IN Number Default null,
332 P_Program_Application_Id IN Number Default null,
333 P_Program_Id IN Number Default null,
334 P_Request_Id IN Number Default null,
335 P_Calling_Module IN Varchar2 Default null,
336 P_Checkrun_Name IN Varchar2 Default null,
337 P_Checkrun_id IN Number Default null,
338 P_Payment_Num IN Number Default null,
339 P_Global_Attr_Category IN Varchar2 Default null,
340 P_NIT_Number IN Varchar2 Default null);
341
342
343
344
345 /**************************************************************************
346 * *
347 * Name : Process_Withholding_Type *
348 * Purpose : Process the information for each different withholding *
349 * tax type included within the payment. *
350 * *
351 **************************************************************************/
352 PROCEDURE Process_Withholding_Type
353 (P_Rec_AWT_Type IN jl_zz_ap_awt_types%ROWTYPE,
354 P_Rec_Suppl_AWT_Type IN jl_zz_ap_supp_awt_types%ROWTYPE,
355 P_AWT_Date IN Date,
356 P_Currency_Code IN Varchar2,
357 P_Tab_Withhold IN OUT NOCOPY Tab_Withholding);
358
359
360
361
362 /**************************************************************************
363 * *
364 * Name : Store_Prorated_Withholdings *
365 * Purpose : Transfers the Prorated Withholding details, from one *
366 * PL/SQL table to another *
367 * *
368 **************************************************************************/
369 PROCEDURE Store_Prorated_Withholdings
370 (P_Tab_Withhold IN Tab_Withholding,
371 P_Tab_All_Withhold IN OUT NOCOPY Tab_All_Withholding);
372
373
374
375
376 /**************************************************************************
377 * *
378 * Name : Print_Tax_Names *
379 * Purpose : This procedure shows all the elements of the PL/SQL table *
380 * (just for debug purposes) *
381 * *
382 **************************************************************************/
383 PROCEDURE Print_Tax_Names (P_Tab_Payment_Wh IN Tab_Withholding);
384
385
386
387
388 /**************************************************************************
389 * *
390 * Name : Jl_Zz_Ap_Extended_Match *
391 * Purpose : Regional Extended Routine for Matching *
395 -- R12 KI
392 * *
393 **************************************************************************/
394 --
396 --
397 PROCEDURE Jl_Zz_Ap_Extended_Match
398 (P_Credit_Id IN Number,
399 P_Invoice_Id IN Number Default null, -- Bug 4559478
400 P_Inv_Line_Num IN Number Default null,
401 P_Distribution_id IN Number Default null,
402 P_Parent_Dist_ID IN Number Default null);
403
404 /**************************************************************************
405 * *
406 * Name : Jl_Zz_Ap_Ext_Insert_Dist *
407 * Purpose : Regional Extended Routine for Insertion *
408 * *
409 **************************************************************************/
410 PROCEDURE Jl_Zz_Ap_Ext_Insert_Dist
411 (P_Invoice_Id IN Number,
412 P_Invoice_Distribution_id IN Number, -- Add new Column
413 P_Distribution_Line_Number IN Number,
414 P_Line_Type IN Varchar2,
415 P_GL_Date IN Date,
416 P_Period_Name IN Varchar2,
417 P_Type_1099 IN Varchar2,
418 P_Income_Tax_Region IN Varchar2,
419 P_Amount IN Number,
420 P_Tax_Code_ID IN Number, -- Add new Column
421 P_Code_Combination_Id IN Number,
422 P_PA_Quantity IN Number,
423 P_Description IN Varchar2,
424 P_tax_recoverable_flag IN Varchar2, -- Add new Column
425 P_tax_recovery_rate IN Number, -- Add new Column
426 P_tax_code_override_flag IN Varchar2, -- Add new Column
427 P_tax_recovery_override_flag IN Varchar2, -- Add new Column
428 P_po_distribution_id IN Number, -- Add new Column
429 P_Attribute_Category IN Varchar2,
430 P_Attribute1 IN Varchar2,
431 P_Attribute2 IN Varchar2,
432 P_Attribute3 IN Varchar2,
433 P_Attribute4 IN Varchar2,
434 P_Attribute5 IN Varchar2,
435 P_Attribute6 IN Varchar2,
436 P_Attribute7 IN Varchar2,
437 P_Attribute8 IN Varchar2,
438 P_Attribute9 IN Varchar2,
439 P_Attribute10 IN Varchar2,
440 P_Attribute11 IN Varchar2,
441 P_Attribute12 IN Varchar2,
442 P_Attribute13 IN Varchar2,
443 P_Attribute14 IN Varchar2,
444 P_Attribute15 IN Varchar2,
445 P_Calling_Sequence IN Varchar2);
446
447 /*************************************************************************
448 * Name : Validate_Multiple_Bal_Seg *
449 * Purpose : Rountine to check whether there exists multiple balancing *
450 * segments within invoice distributions or tax code *
451 * *
452 **************************************************************************/
453
454 -- Fix for bug 1770433
455 FUNCTION Validate_Multiple_Bal_Seg
456 (P_Invoice_Id ap_invoices.invoice_id%TYPE
457 ) return Varchar2;
458
459 /*************************************************************************
460 * Name : Validate_Mult_BS_GateWay *
461 * Purpose : Rountine to check whether there exists multiple balancing *
462 * segments within invoice distributions or tax code *
463 * for Invoice Gateway *
464 * *
465 **************************************************************************/
466 -- Fix for bug 1770433
467 FUNCTION Validate_Mult_BS_GateWay
468 (P_Invoice_Id ap_invoices.invoice_id%TYPE
469 ) return Varchar2;
470
471 /*************************************************************************
472 * Name : Dynamic_Call_Get_BalSeg *
473 * Purpose : Encapsulate Dynamic Call to get_auto_offsets_segments *
474 * *
475 **************************************************************************/
476
477 FUNCTION Dynamic_Call_Get_BalSeg
478 (P_ccid IN Number ) return Varchar2;
479
480 END JL_ZZ_AP_WITHHOLDING_PKG;