1 PACKAGE BODY JL_ZZ_AP_AWT_DEFAULT_PKG AS
2 /* $Header: jlzzpwdb.pls 120.23.12010000.3 2008/08/04 12:52:20 vgadde ship $ */
3 /*
4
5 Copyright (c) 1995 by Oracle Corporation
6
7 NAME
8 JL_ZZ_AP_AWT_DEFAULT_PKG - PL/SQL Package Body Validate the Global Attributes
9 for Import Process.
10 DESCRIPTION
11 This package validate the Global Attributes in the import process.
12 NOTES
13 This package body must be created under Global Applications Development.
14 HISTORY (DD/MM/YY)
15 dbetanco 09/11/98 Creation
16 dbetanco 11/12/98 Update
17 dbetanco 19/01/98 Update Include the Del_Wh_Def Proc.
18
19 */
20
21 -- =====================================================================
22 -- P R I V A T E O B J E C T S
23 -- =====================================================================
24 --
25 -- Insert_AWT_Default insert in jl_zz_ap_inv_dis_wh_all the withholdings after the validation.
26 --
27 -- Define Package Level Debug Variable and Assign the Profile
28 DEBUG_Var varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
29 G_CURRENT_RUNTIME_LEVEL NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
30 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
31 G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
32 G_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
33 G_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
34 G_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
35 G_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
36 --
37 -- R12 KI
38 --
39
40 PROCEDURE Insert_AWT_Default(
41 P_Invoice_Id IN ap_invoices_all.invoice_id%TYPE,
42 P_Inv_Dist_Id IN ap_invoice_distributions_all.invoice_distribution_id%TYPE,
43 P_Supp_Awt_Code_Id IN jl_zz_ap_sup_awt_cd.supp_awt_code_id%TYPE,
44 p_calling_sequence IN VARCHAR2,
45 P_Org_Id IN jl_zz_ap_sup_awt_cd.org_id%TYPE) IS
46
47 Seq_Inv_Dis_Awt_Id NUMBER;
48 l_debug_loc VARCHAR2(30) := ' Insert_AWT_Default ';
49 l_curr_calling_sequence VARCHAR2(2000);
50 l_debug_info VARCHAR2(100);
51 -- WHO Columns
52 v_last_update_by NUMBER;
53 v_last_update_login NUMBER;
54
55 BEGIN
56 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
57 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Insert_AWT_Default','Start PROCEDURE Insert_AWT_Default');
58 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Insert_AWT_Default','Parameters are :');
59 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Insert_AWT_Default',' P_Invoice_Id='||P_Invoice_Id);
60 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Insert_AWT_Default',' P_Inv_Dist_Id='||P_Inv_Dist_Id);
61 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Insert_AWT_Default',' P_Supp_Awt_Code_Id='||P_Supp_Awt_Code_Id);
62 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Insert_AWT_Default',' p_calling_sequence='||p_calling_sequence);
63 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Insert_AWT_Default',' P_Org_Id='||P_Org_Id);
64 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Insert_AWT_Default',' P_Invoice_Id='||P_Invoice_Id);
65 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Insert_AWT_Default',' Seq_Inv_Dis_Awt_Id='||Seq_Inv_Dis_Awt_Id);
66 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Insert_AWT_Default',' l_debug_loc='||l_debug_loc);
67 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Insert_AWT_Default',' l_curr_calling_sequence='||l_curr_calling_sequence);
68 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Insert_AWT_Default',' l_debug_info='||l_debug_info);
69 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Insert_AWT_Default',' v_last_update_by='||v_last_update_by);
70 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Insert_AWT_Default',' v_last_update_login='||v_last_update_login);
71 END IF;
72 -------------------------- DEBUG INFORMATION ------------------------------
73 l_curr_calling_sequence := 'jg_globe_flex_val.'||l_debug_loc||'<-'||p_calling_sequence;
74
75 l_debug_info := 'Insert rejection information to ap_interface_rejections';
76 ---------------------------------------------------------------------------
77
78 -- Get the information of WHO Columns from FND_GLOBAL
79 v_last_update_by := FND_GLOBAL.User_ID;
80 v_last_update_login := FND_GLOBAL.Login_Id;
81
82 -- Select next value from the sequence.
83 SELECT jl_zz_ap_inv_dis_wh_s.nextval
84 INTO Seq_Inv_Dis_Awt_Id
85 FROM dual;
86
87 --
88 -- Insert into JL_ZZ_AP_INV_DIS_WH_ALL
89 --
90 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
91 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Insert_AWT_Default','Inserting this record to JL_ZZ_AP_INV_DIS_WH_ALL with inv_distrib_awt_id='||Seq_Inv_Dis_Awt_Id);
92 END IF;
93
94 INSERT INTO jl_zz_ap_inv_dis_wh (
95 inv_distrib_awt_id
96 ,invoice_id
97 -- Bug 4559472
98 ,distribution_line_number
99 ,invoice_distribution_id
100 ,supp_awt_code_id
101 ,created_by
102 ,creation_date
103 ,last_updated_by
104 ,last_update_date
105 ,last_update_login
106 ,org_id -- Add org_id for MOAC
107 )
108 VALUES (
109 Seq_Inv_Dis_Awt_Id
110 ,P_Invoice_Id
111 -- Bug 4559472
112 --,P_Dis_Line_Number
113 -- Populate distribution_line_number with -99 for R12 records
114 -- as it is NOT NULL column in jl_zz_ap_inv_dis_wh_all
115 ,-99
116 , P_Inv_Dist_Id
117 ,P_Supp_Awt_Code_Id
118 ,v_last_update_by
119 ,sysdate
120 ,v_last_update_by
121 ,sysdate
122 ,v_last_update_login
123 ,P_Org_Id -- Add org_id for MOAC
124 );
125 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
126 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Insert_AWT_Default','End PROCEDURE Insert_AWT_Default');
127 END IF;
128
129 EXCEPTION
130 WHEN OTHERS then
131 IF (G_LEVEL_EXCEPTION >= G_CURRENT_RUNTIME_LEVEL ) THEN
132 FND_LOG.STRING(G_LEVEL_EXCEPTION, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Insert_AWT_Default','Exception Occured in PROCEDURE Insert_AWT_Default');
133 END IF;
134 IF (SQLCODE <> -20001) THEN
135 FND_MESSAGE.SET_NAME('SQLAP', 'AP_DEBUG');
136 FND_MESSAGE.SET_TOKEN('ERROR', 'SQLERRM');
137 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_curr_calling_sequence);
138 FND_MESSAGE.SET_TOKEN('PARAMETERS',
139 ' P_Invoice_ID = '||to_char(P_Invoice_ID)
140 -- Bug 4559472 ||', P_Dis_Line_Number = '||to_char(P_Dis_Line_Number )
141 ||', P_Inv_Dist_Id = '||to_char(P_Inv_Dist_Id)
142 ||', P_Supp_Awt_Code_Id = '||to_char(P_Supp_Awt_Code_Id)
143 ||', Last Updated By = '||to_char(v_last_update_by)
144 ||', Last Update Date = '||to_char(v_last_update_login));
145
146 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
147 END IF;
148 APP_EXCEPTION.RAISE_EXCEPTION;
149 END;
150
151
152
153 /*-------------------------------------------------------------------------
154 Ver_Territorial_Flag return true if the province is territorial.
155 ---------------------------------------------------------------------------*/
156
157 FUNCTION Ver_Territorial_Flag
158 (P_Province_Code jl_ar_ap_provinces.province_code%TYPE
159 ) return boolean is
160 -------------------------------------------------------------------------
161 -- Select the flag for province Territory
162 -------------------------------------------------------------------------
163 CURSOR Province_Territory IS
164 SELECT territorial_flag
165 FROM jl_ar_ap_provinces
166 WHERE province_code = P_Province_Code;
167
168 v_territory boolean;
169 v_province_terr jl_ar_ap_provinces.territorial_flag%TYPE;
170
171 BEGIN
172 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
173 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Ver_Territorial_Flag','Start FUNCTION Ver_Territorial_Flag');
174 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Ver_Territorial_Flag','Parameters are :');
175 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Ver_Territorial_Flag',' P_Province_Code='||P_Province_Code);
176 END IF;
177 v_territory := FALSE;
178 OPEN Province_Territory;
179 LOOP
180 FETCH Province_Territory
181 INTO v_province_terr;
182 EXIT when Province_Territory%NOTFOUND;
183 IF v_province_terr = 'Y' THEN
184 v_territory := TRUE;
185 END IF;
186 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
187 IF v_territory THEN
188 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Ver_Territorial_Flag','FUNCTION Ver_Territorial_Flag returns TRUE');
189 ELSE
190 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Ver_Territorial_Flag','FUNCTION Ver_Territorial_Flag returns FALSE');
191 END IF;
192 END IF;
193 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
194 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Ver_Territorial_Flag','End FUNCTION Ver_Territorial_Flag');
195 END IF;
196 return (v_territory);
197 END LOOP;
198 CLOSE Province_Territory;
199 EXCEPTION
200 WHEN OTHERS THEN
201 IF (G_LEVEL_EXCEPTION >= G_CURRENT_RUNTIME_LEVEL ) THEN
202 FND_LOG.STRING(G_LEVEL_EXCEPTION, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Ver_Territorial_Flag','Exception in FUNCTION Ver_Territorial_Flag');
203 NULL;
204 END IF;
205 END Ver_Territorial_Flag;
206
207 /*----------------------------------------------------------------------------------------------
208 The Following function return the vendor_id for the Distribution Line.
209 If tax_payerid is not null find the vendor_id for this tax_payerid Else
210 return the vendor id from the invoice.
211 ------------------------------------------------------------------------------------------------*/
212 FUNCTION Get_Vendor_Id ( P_Tax_Payer_Id ap_invoice_distributions_all.global_attribute2%TYPE
213 , P_Invoice_Id ap_invoices_all.invoice_id%TYPE
214 )
215 return number is
216 --------------------------------------------------------------------
217 -- Get the information from po_vendors when tax_payerid is not null
218 --------------------------------------------------------------------
219 CURSOR TaxPayerID_Po_Ven IS
220 SELECT Vendor_Id
221 FROM po_vendors
222 WHERE segment1 = P_Tax_Payer_Id; -- R12 KI : Need to uptake PTP?
223
224 --------------------------------------------------------------------
225 -- Get the information from ap_invoices_all when tax_payerid is null.
226 --------------------------------------------------------------------
227 CURSOR Invoice_Vendor IS
228 SELECT Vendor_Id
229 FROM ap_invoices
230 WHERE invoice_id = P_Invoice_Id;
231
232 v_vendor_id po_vendors.vendor_id%TYPE;
233
234 BEGIN
235 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
236 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Get_Vendor_Id','Start FUNCTION Get_Vendor_Id');
237 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Get_Vendor_Id','Parameters are :');
238 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Get_Vendor_Id',' P_Tax_Payer_Id='||P_Tax_Payer_Id);
239 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Get_Vendor_Id',' P_Invoice_Id='||P_Invoice_Id);
240 END IF;
241 IF P_Tax_Payer_Id IS NOT NULL THEN
242 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
243 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Get_Vendor_Id','Inside IF P_Tax_Payer_Id IS NOT NULL THEN');
244 END IF;
245 OPEN TaxPayerID_Po_Ven;
246 LOOP
247 FETCH TaxPayerID_Po_Ven
248 INTO v_vendor_id;
249 EXIT when TaxPayerID_Po_Ven%NOTFOUND;
250 END LOOP;
251 CLOSE TaxPayerID_Po_Ven;
252 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
253 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Get_Vendor_Id','FUNCTION Get_Vendor_Id returns v_vendor_id='||v_vendor_id);
254 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Get_Vendor_Id','End FUNCTION Get_Vendor_Id');
255 END IF;
256 return (v_vendor_id);
257 ELSE
258 OPEN Invoice_Vendor;
259 LOOP
260 FETCH Invoice_Vendor
261 INTO v_vendor_id;
262 EXIT when Invoice_Vendor%NOTFOUND;
263 END LOOP;
264 CLOSE Invoice_Vendor;
265 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
266 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Get_Vendor_Id','FUNCTION Get_Vendor_Id returns v_vendor_id='||v_vendor_id);
267 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Get_Vendor_Id','End FUNCTION Get_Vendor_Id');
268 END IF;
269 return (v_vendor_id);
270 END IF;
271 EXCEPTION
272 WHEN OTHERS THEN
273 IF (G_LEVEL_EXCEPTION >= G_CURRENT_RUNTIME_LEVEL ) THEN
274 FND_LOG.STRING(G_LEVEL_EXCEPTION, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Get_Vendor_Id','Exception in FUNCTION Get_Vendor_Id');
275 END IF;
276 END;
277
278 /*----------------------------------------------------------------------------------------------
279 The Following function verify if the company is agent for a withholding type.
280 The function receive the Supplier Withholding Type as parameter.
281 ------------------------------------------------------------------------------------------------*/
282
283 FUNCTION Company_Agent (P_Awt_Type_Code jl_zz_ap_awt_types.awt_type_code%TYPE,
284 P_Invoice_Id ap_invoices_all.invoice_id%TYPE)
285 return boolean is
286
287 Cursor Company_Awt_Types (PC_Legal_Entity_ID xle_entity_profiles.legal_entity_id%TYPE)
288 IS
289 SELECT awt_type_code
290 FROM jl_zz_ap_comp_awt_types
291 WHERE legal_entity_id = PC_Legal_Entity_ID
295 Cursor legal_entity IS
292 --location_id = PC_Location_ID
293 AND wh_agent_flag = 'Y';
294
296 SELECT legal_entity_id
297 FROM ap_invoices
298 WHERE invoice_id = P_Invoice_ID;
299
300 find_type boolean;
301 -- v_location hr_locations_all.location_id%TYPE;
302 l_legal_entity_id xle_entity_profiles.legal_entity_id%TYPE;
303
304 BEGIN
305 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
306 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Company_Agent','Start FUNCTION Company_Agent');
307 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Company_Agent','Parameters are :');
308 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Company_Agent',' P_Awt_Type_Code='||P_Awt_Type_Code);
309 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Company_Agent',' P_Invoice_Id='||P_Invoice_Id);
310 END IF;
311 find_type := FALSE;
312 -----------------------------------------------------------
313 -- Get the Company information from the Legal Entity
314 ----------------------------------------------------------
315 -- v_location := jg_zz_company_info.get_location_id; -- LE
316 SELECT legal_entity_id
317 INTO l_legal_entity_id
318 FROM ap_invoices
319 WHERE invoice_id = P_Invoice_ID;
320 ----------------------------------------------------------
321 -- Loop verify the withholding type.
322 ----------------------------------------------------------
323 FOR db_reg IN Company_Awt_Types (l_legal_entity_id) LOOP
324 IF db_reg.awt_type_code = P_Awt_Type_Code THEN
325 find_type := TRUE;
326 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
327 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Company_Agent','FUNCTION Company_Agent returns TRUE');
328 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Company_Agent','End FUNCTION Company_Agent');
329 END IF;
330 return(find_type);
331 END IF;
332 END LOOP;
333 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
334 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Company_Agent','FUNCTION Company_Agent returns FALSE');
335 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Company_Agent','End FUNCTION Company_Agent');
336 END IF;
337 return(find_type);
338 EXCEPTION
339 WHEN OTHERS THEN
340 IF (G_LEVEL_EXCEPTION >= G_CURRENT_RUNTIME_LEVEL ) THEN
341 FND_LOG.STRING(G_LEVEL_EXCEPTION, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Company_Agent','Exception in FUNCTION Company_Agent');
342 NULL;
343 END IF;
344 END;
345
346 /*------------------------------------------------------
347 The Following function is to find out whether the Tax Name is applicable to the Line_type
348 TAX-ID and Line Type are passed as a parameter for this function.
349 -----------------------------------------------------*/
350
351 FUNCTION Validate_Line_Type
352 (v_dist_type varchar2
353 ,v_tax_id ap_tax_codes_all.tax_id%type)
354 return boolean is
355 v_item_type ap_tax_codes_all.global_attribute8%type;
356 v_freight_type ap_tax_codes_all.global_attribute9%type;
357 v_misc_type ap_tax_codes_all.global_attribute10%type;
358 v_tax_type ap_tax_codes_all.global_attribute11%type;
359 find_type boolean;
360
361 CURSOR cur_validate_line_type is
362 SELECT global_attribute8, -- Type ITEM
363 global_attribute9, -- Type FREIGHT
364 global_attribute10,-- Type MISCELLANEOUS
365 global_attribute11 -- Type TAX
366 FROM ap_tax_codes
367 WHERE tax_id =v_tax_id;
368
369 BEGIN
370 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
371 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Validate_Line_Type','Start FUNCTION Validate_Line_Type');
372 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Validate_Line_Type','Parameters are :');
373 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Validate_Line_Type',' v_dist_type='||v_dist_type);
374 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Validate_Line_Type',' v_tax_id='||v_tax_id);
375 END IF;
376 find_type :=false;
377 OPEN cur_validate_line_type ;
378 LOOP
379 FETCH cur_validate_line_type INTO
380 v_item_type,
381 v_freight_type,
382 v_misc_type,
383 v_tax_type;
384 EXIT WHEN cur_validate_line_type%NOTFOUND;
385 --bug 6232172 - v_dist_type = 'ACCRUAL' is added
386 IF (v_dist_type = 'ITEM'
387 OR v_dist_type = 'ACCRUAL'
388 OR v_dist_type = 'IPV'
389 OR v_dist_type = 'ERV') AND (v_item_type='Y') THEN
390 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
391 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Validate_Line_Type','FUNCTION Validate_Line_Type returns TRUE');
392 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Validate_Line_Type','End FUNCTION Validate_Line_Type');
393 END IF;
394 find_type :=true ; return(find_type);
395 ELSIF (v_dist_type = 'FREIGHT') AND (v_freight_type ='Y') THEN
396 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
400 find_type :=true ; return(find_type);
397 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Validate_Line_Type','FUNCTION Validate_Line_Type returns TRUE');
398 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Validate_Line_Type','End FUNCTION Validate_Line_Type');
399 END IF;
401 ELSIF (v_dist_type = 'MISCELLANEOUS') AND (v_misc_type ='Y') THEN
402 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
403 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Validate_Line_Type','FUNCTION Validate_Line_Type returns TRUE');
404 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Validate_Line_Type','End FUNCTION Validate_Line_Type');
405 END IF;
406 find_type :=true ; return(find_type);
407 ELSIF (v_dist_type = 'TAX'
408 OR v_dist_type = 'NONREC_TAX'
409 OR v_dist_type = 'TRV'
410 OR v_dist_type = 'TIPV'
411 OR v_dist_type = 'TERV') AND (v_tax_type ='Y') THEN
412 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
413 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Validate_Line_Type','FUNCTION Validate_Line_Type returns TRUE');
414 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Validate_Line_Type','End FUNCTION Validate_Line_Type');
415 END IF;
416 find_type :=true ; return(find_type);
417 END IF;
418 END LOOP;
419 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
420 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Validate_Line_Type','FUNCTION Validate_Line_Type returns FALSE');
421 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Validate_Line_Type','End FUNCTION Validate_Line_Type');
422 END IF;
423 return(find_type);
424 CLOSE cur_validate_line_type;
425 EXCEPTION
426 WHEN OTHERS THEN
427 IF (G_LEVEL_EXCEPTION >= G_CURRENT_RUNTIME_LEVEL ) THEN
428 FND_LOG.STRING(G_LEVEL_EXCEPTION, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Validate_Line_Type','Exception in FUNCTION Validate_Line_Type');
429 NULL;
430 END IF;
431 END;
432
433 /*-----------------------------------------------------------------
434 The Procedure Province_zone_city is used to select region_1,region_2,town_or_city from
435 hr_locations for a ship_to_location_id.
436 -------------------------------------------------------------------*/
437 PROCEDURE Province_Zone_City
438 (p_ship_to_location_id hr_locations_all.location_id%TYPE
439 ,v_hr_zone out NOCOPY hr_locations_all.region_1%TYPE
440 ,v_hr_province out NOCOPY hr_locations_all.region_2%TYPE
441 ,v_city_code out NOCOPY hr_locations_all.town_or_city%TYPE) is
442
443 CURSOR cur_province_zone_city IS
444 SELECT region_1, region_2, town_or_city
445 FROM hr_locations_all
446 WHERE location_id = p_ship_to_location_id;
447 BEGIN
448 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
449 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Province_Zone_City','Start PROCEDURE Province_Zone_City');
450 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Province_Zone_City','Parameters are :');
451 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Province_Zone_City',' p_ship_to_location_id='||p_ship_to_location_id);
452 END IF;
453 OPEN cur_province_zone_city ;
454 LOOP
455 FETCH cur_province_zone_city
456 INTO v_hr_zone , v_hr_province, v_city_code;
457 EXIT when cur_province_zone_city%NOTFOUND;
458 END LOOP;
459 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
460 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Province_Zone_City','Out Parameters are :');
461 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Province_Zone_City',' v_hr_zone='||v_hr_zone);
462 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Province_Zone_City',' v_hr_province='||v_hr_province);
463 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Province_Zone_City',' v_city_code='||v_city_code);
464 END IF;
465 CLOSE cur_province_zone_city ;
466 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
467 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Province_Zone_City','End PROCEDURE Province_Zone_City');
468 END IF;
469 EXCEPTION
470 WHEN OTHERS THEN
471 IF (G_LEVEL_EXCEPTION >= G_CURRENT_RUNTIME_LEVEL ) THEN
472 FND_LOG.STRING(G_LEVEL_EXCEPTION, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Province_Zone_City','Exception in PROCEDURE Province_Zone_City');
473 NULL;
474 END IF;
475 END;
476
477 /*-----------------------------------------------------------------
478 The Procedure Del_Wh_Def Delete the records in JL_ZZ_AP_INV_DIS_WH
479 for the Invoice_ID Parameter and the Dis_Lin_Number.
480 -------------------------------------------------------------------*/
481 --
482 -- R12 KI
483 --
484 PROCEDURE Del_Wh_Def
485 (
486 p_inv_dist_id ap_invoice_distributions_all.invoice_distribution_id%TYPE
487 ) IS
488 Begin
489 /*
490 DELETE JL_ZZ_AP_INV_DIS_WH
491 WHERE invoice_id = P_Invoice_Id
492 AND invoice_distribution_id = P_Dis_Lin_Num;
493 */
494 -- Bug 4559472
495 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
496 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Del_Wh_Def','Start PROCEDURE Del_Wh_Def');
497 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Del_Wh_Def','Parameters are :');
498 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Del_Wh_Def',' p_inv_dist_id='||p_inv_dist_id);
499 END IF;
500 DELETE jl_zz_ap_inv_dis_wh
501 WHERE invoice_distribution_id = p_inv_dist_id;
502 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
503 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Del_Wh_Def','End PROCEDURE Del_Wh_Def');
504 END IF;
505 EXCEPTION
506 WHEN OTHERS THEN
507 IF (G_LEVEL_EXCEPTION >= G_CURRENT_RUNTIME_LEVEL ) THEN
508 FND_LOG.STRING(G_LEVEL_EXCEPTION, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Del_Wh_Def','Exception in PROCEDURE Del_Wh_Def');
509 NULL;
510 END IF;
511 End;
512
513
514 -- =====================================================================
515 -- P U B L I C O B J E C T S
516 -- =====================================================================
517 --
518 --
519
520 --
521 -- R12 KI
522 --
523 PROCEDURE Supp_Wh_Def_Line
524 ( p_invoice_id NUMBER,
525 p_inv_dist_id NUMBER,
526 p_tax_payer_id NUMBER,
527 p_ship_to_loc VARCHAR2,
528 p_line_type VARCHAR2,
529 p_vendor_id NUMBER
530 ) IS
531 ---------------------------------------------------------------------
532 -- Cursor Supplier Withholding Types.
533 ---------------------------------------------------------------------
534 CURSOR Supp_Wh_Types(C_Vendor_Id jl_zz_ap_supp_awt_types.vendor_id%TYPE) Is
535 SELECT swt.supp_awt_type_id ,
536 swt.awt_type_code,
537 swc.supp_awt_code_id,
538 swc.org_id, -- Add Org_ID for MOAC
539 tca.tax_id,
540 tca.global_attribute7, -- Zone
541 awt.jurisdiction_type,
542 awt.province_code,
543 awt.city_code
544 FROM jl_zz_ap_supp_awt_types swt,
545 jl_zz_ap_sup_awt_cd swc,
546 ap_tax_codes_ALL tca, -- Add _ALL for MOAC
547 jl_zz_ap_awt_types awt
548 WHERE swt.vendor_id = C_vendor_id -- Select only for this Supplier
549 AND swt.wh_subject_flag = 'Y' -- Supp subject to the withholding tax type
550 AND swc.supp_awt_type_id = swt.supp_awt_type_id -- Join
551 AND swc.tax_id = tca.tax_id -- Join
552 AND (tca.inactive_date > sysdate -- Verify Tax Name Inactive Date
553 OR tca.inactive_date IS NULL)
554 AND swc.primary_tax_flag = 'Y' -- Verify the Primary Withholding Tax
555 AND awt.awt_type_code = swt.awt_type_code -- Join
556 ;
557
558 v_provincial_code jl_ar_ap_provinces.province_code%TYPE;
559
560 v_hr_zone hr_locations_all.region_1%TYPE;
561 v_hr_province hr_locations_all.region_2%TYPE;
562 v_hr_city hr_locations_all.town_or_city%TYPE;
563 v_hr_city_name hz_geographies.geography_name%TYPE; --Added Bug 6147511
564 pc_vendor_id number;
565 p_calling_sequence varchar2(2000):= 'Supp_Wh_Def_Line';
566
567
568 Begin
569
570 ------------------------------------------------------------------------------------------
571 -- Delete the lines in JL_ZZ_AP_INV_DIS.
572 ------------------------------------------------------------------------------------------
573 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
574 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Supp_Wh_Def_Line','Start PROCEDURE Supp_Wh_Def_Line');
575 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Supp_Wh_Def_Line','Parameters are :');
576 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Supp_Wh_Def_Line',' p_invoice_id='||p_invoice_id);
577 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Supp_Wh_Def_Line',' p_inv_dist_id='||p_inv_dist_id);
578 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Supp_Wh_Def_Line',' p_tax_payer_id='||p_tax_payer_id);
579 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Supp_Wh_Def_Line',' p_ship_to_loc='||p_ship_to_loc);
580 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Supp_Wh_Def_Line',' p_line_type='||p_line_type);
581 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Supp_Wh_Def_Line',' p_vendor_id='||p_vendor_id);
582 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Supp_Wh_Def_Line','Calling Del_Wh_Def');
583 END IF;
584 Del_Wh_Def(
585 /*
586 P_Invoice_Id
587 ,P_Dis_Lin_Num
588 */
589 -- Bug 4559472
590 p_inv_dist_id);
591 ------------------------------------------------------------------------------------------
592 -- Get the Vendor_Id from the Vendor_Num (Taxpayer_ID) or Invoice
593 ------------------------------------------------------------------------------------------
594 IF P_Vendor_Id IS NULL Then
595 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
596 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Supp_Wh_Def_Line','Inside IF P_Vendor_Id IS NULL Then');
597 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Supp_Wh_Def_Line','Calling Get_Vendor_Id');
598 END IF;
599 pc_vendor_id := Get_Vendor_Id (/*
600 v_tax_payer_id,
601 p_invoice_id
602 */
603 p_tax_payer_id,
604 p_invoice_id);
605 Else
606 pc_vendor_id := p_vendor_id;
607 End IF;
608 ------------------------------------------------------------------------------------------
609 -- Loop for each Supplier Withholding Type
610 -----------------------------------------------------------------------------------------
611 FOR db_reg IN Supp_Wh_Types(pc_vendor_id) LOOP
612 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
613 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Supp_Wh_Def_Line','Inside db_reg IN Supp_Wh_Types(pc_vendor_id) for pc_vendor_id='||pc_vendor_id);
614 END IF;
615 ---------------------------------------------------------------------------------
616 -- The cursor verify the Supplier Withholding Applicability
617 -- Each Supp Withholding Type in the Cursor needs to be check.
618 -- Company Agent says if the company have to withhold by this Withholding Type.
622 p_invoice_id)) THEN
619 ---------------------------------------------------------------------------------
620 IF ( Company_Agent(db_reg.awt_type_code,
621 -- Added p_invoice_id for R12 LE changes
623 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
624 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Supp_Wh_Def_Line','Inside IF ( Company_Agent(db_reg.awt_type_code ..');
625 END IF;
626 ----------------------------------------------------------------------------
627 -- Validate the withholding type is according to distribution line.
628 ----------------------------------------------------------------------------
629 IF Validate_Line_Type(
630 /*
631 v_line_type,
632 db_reg.tax_id
633 */
634 p_line_type,
635 db_reg.tax_id) THEN
636 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
637 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Supp_Wh_Def_Line','Inside IF Validate_Line_Type( ..');
638 END IF;
639 -----------------------------------------------------------------------
640 -- Get the information from Zone, Province and City
641 -----------------------------------------------------------------------
642 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
643 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Supp_Wh_Def_Line','Calling Province_Zone_City');
644 END IF;
645 Province_Zone_City
646 (
647 /*
648 v_ship_to_loc -- IN
649 */
650 p_ship_to_loc -- IN
651 ,v_hr_zone -- OUT NOCOPY
652 ,v_hr_province -- OUT NOCOPY
653 ,v_hr_city
654 ); -- OUT NOCOPY
655 -----------------------------------------------------------------------
656 -- Validate the Jurisdiction
657 -----------------------------------------------------------------------
658 IF ( db_reg.jurisdiction_type = 'PROVINCIAL') THEN
659 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
660 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Supp_Wh_Def_Line','Inside IF ( db_reg.jurisdiction_type = PROVINCIAL)');
661 END IF;
662 --------------------------------------------------------------------
663 -- Verify if the Withholding Tax for the Province is TERRITORY
664 --------------------------------------------------------------------
665 IF Ver_Territorial_Flag (db_reg.province_code) THEN
666 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
667 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Supp_Wh_Def_Line','Inside IF Ver_Territorial_Flag (db_reg.province_code)');
668 END IF;
669 -----------------------------------------------------------------
670 -- Validate if the Ship to Location from Inv Dis Line is in the province.
671 -----------------------------------------------------------------
672 IF db_reg.province_code = v_hr_province THEN
673 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
674 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Supp_Wh_Def_Line','Inside IF db_reg.province_code = v_hr_province THEN');
675 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Supp_Wh_Def_Line','Calling Insert_AWT_Default - 1');
676 END IF;
677 Insert_AWT_Default
678 (P_Invoice_Id
679 -- Bug 4559472
680 -- ,P_Dis_Lin_Num
681 , p_inv_dist_id
682 , db_reg. supp_awt_code_id
683 , p_calling_sequence
684 , db_reg.org_id ); -- Add org_Id for MOAC
685
686 END IF;
687 ELSE -- v_territorial_flag = 'N' is Country Wide
688 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
689 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Supp_Wh_Def_Line','Inside ELSE Ver_Territorial_Flag (db_reg.province_code)');
690 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Supp_Wh_Def_Line','Calling Insert_AWT_Default - 2');
691 END IF;
692 Insert_AWT_Default
693 (P_Invoice_Id
694 -- Bug 4559472
695 -- ,P_Dis_Lin_Num
696 , p_inv_dist_id
697 , db_reg. supp_awt_code_id
698 , p_calling_sequence
699 , db_reg.org_id ); -- Add org_Id for MOAC
700
701 END IF; -- PROVINCE Class
702 ELSIF db_reg.jurisdiction_type = 'ZONAL' THEN
703 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
704 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Supp_Wh_Def_Line','Inside ELSIF db_reg.jurisdiction_type = ZONAL THEN');
708 ---------------------------------------------------------------
705 END IF;
706 ---------------------------------------------------------------
707 -- The name of the zone is taken from AP_TAX_CODES Global Att 7
709 IF db_reg.global_attribute7 = v_hr_zone THEN
710 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
711 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Supp_Wh_Def_Line','Inside IF db_reg.global_attribute7 = v_hr_zone THEN');
712 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Supp_Wh_Def_Line','Calling Insert_AWT_Default - 3');
713 END IF;
714 Insert_AWT_Default
715 (P_Invoice_Id
716 -- Bug 4559472
717 -- ,P_Dis_Lin_Num
718 , p_inv_dist_id
719 , db_reg. supp_awt_code_id
720 , p_calling_sequence
721 , db_reg.org_id ); -- Add org_Id for MOAC
722
723 END IF; --Tax_Zone
724
725 ELSIF db_reg.jurisdiction_type = 'MUNICIPAL' THEN
726 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
727 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Supp_Wh_Def_Line','Inside ELSIF db_reg.jurisdiction_type = MUNICIPAL THEN');
728 END IF;
729 ---------------------------------------------------------------
730 -- Compare the Withholding Type City with the city in the line
731 ---------------------------------------------------------------
732 --Bug no: 6147511. Added this query to get the city name from geographies table
733 select geography_name
734 into v_hr_city_name
735 from hz_geographies
736 where geography_code= db_reg.city_code
737 and geography_type='CITY';
738 --Bug no: 6147511, Previous condition : IF db_reg.city_code = v_hr_city THEN
739 IF v_hr_city_name = v_hr_city THEN
740 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
741 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Supp_Wh_Def_Line','Calling Insert_AWT_Default - 4');
742 END IF;
743 Insert_AWT_Default
744 (P_Invoice_Id
745 -- Bug 4559472
746 -- ,P_Dis_Lin_Num
747 , p_inv_dist_id
748 , db_reg. supp_awt_code_id
749 , p_calling_sequence
750 , db_reg.org_id ); -- Add org_Id for MOAC
751
752 END IF;
753
754 ELSE -- db_reg.jurisdiction_type = 'FEDERAL'
755 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
756 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Supp_Wh_Def_Line','Inside ELSE FOR db_reg.jurisdiction_type = FEDERAL');
757 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Supp_Wh_Def_Line','Calling Insert_AWT_Default - 5');
758 END IF;
759 Insert_AWT_Default
760 (P_Invoice_Id
761 -- Bug 4559472
762 -- ,P_Dis_Lin_Num
763 , p_inv_dist_id
764 , db_reg. supp_awt_code_id
765 , p_calling_sequence
766 , db_reg.org_id ); -- Add org_Id for MOAC
767
768 END IF;--jurisdiction type
769 END IF;--validate line_type
770 END IF;--withholding applicability
771 END LOOP; -- Loop for each Supplier Withholding Type
772 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
773 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Supp_Wh_Def_Line','End PROCEDURE Supp_Wh_Def_Line');
774 END IF;
775 EXCEPTION
776 WHEN OTHERS THEN
777 IF (G_LEVEL_EXCEPTION >= G_CURRENT_RUNTIME_LEVEL ) THEN
778 FND_LOG.STRING(G_LEVEL_EXCEPTION, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Supp_Wh_Def_Line','Exception in PROCEDURE Supp_Wh_Def_Line');
779 NULL;
780 END IF;
781 END Supp_Wh_Def_Line;
782
783 /*----------------------------------------------------------------------------------------------
784 Supp_Wh_Def receive as parameters the P_Invoice_Id and P_Dis_Lin_Num
785 If P_Dis_Lin_Num IS NULL this procedure process all the lines in the invoice
786 Else Only process the line that receive as parameter.
787 ------------------------------------------------------------------------------------------------*/
788
789 --
790 -- R12 KI
791 --
792
793 PROCEDURE Supp_Wh_Def
794 ( P_Invoice_Id ap_invoices_all.invoice_id%TYPE
795 , P_Inv_Line_Num ap_invoice_lines_all.line_number%TYPE
796 , P_Inv_Dist_Id ap_invoice_distributions_all.invoice_distribution_id%TYPE
797 , P_Calling_Module VARCHAR2
798 , P_Parent_Dist_ID IN Number Default null
799 ) IS
800
801 --
802 -- R12 KI Changes : 4559472
803 --
804 CURSOR Invoice_Distrib IS
805 SELECT invoice_distribution_id
806 FROM ap_invoice_distributions
807 WHERE invoice_id = P_Invoice_ID
811 CURSOR c_default_wh_dist (p_related_dist_id number) IS
808 AND invoice_line_number = P_Inv_Line_Num;
809
810 -- Added Cursor for bug 6869263
812 SELECT Supp_Awt_Code_Id,
813 org_id
814 FROM jl_zz_ap_inv_dis_wh
815 WHERE invoice_id = p_invoice_id
816 AND invoice_distribution_id = p_related_dist_id;
817
818 -- The following variables are used to get the information from the invoice
819 -- ditribution lines.
820 v_tax_payer_id ap_invoice_distributions_all.global_attribute2%TYPE;
821 v_ship_to_loc ap_invoice_distributions_all.global_attribute3%TYPE;
822 v_line_type ap_invoice_distributions_all.line_type_lookup_code %TYPE;
823 v_last_update_login number := FND_GLOBAL.Login_Id;
824 v_last_update_by number := FND_GLOBAL.User_ID;
825 DistWithholdings Number := 0;
826 -- Variable added for bug 6869263
827 v_related_dist_id ap_invoice_distributions_all.related_id%TYPE;
828
829 Begin
830 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
831 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Supp_Wh_Def','Start PROCEDURE Supp_Wh_Def');
832 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Supp_Wh_Def','Parameters are :');
833 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Supp_Wh_Def',' P_Invoice_Id='||P_Invoice_Id);
834 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Supp_Wh_Def',' P_Inv_Line_Num='||P_Inv_Line_Num);
835 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Supp_Wh_Def',' P_Inv_Dist_Id='||P_Inv_Dist_Id);
836 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Supp_Wh_Def',' P_Calling_Module='||P_Calling_Module);
837 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Supp_Wh_Def',' P_Parent_Dist_ID='||P_Parent_Dist_ID);
838 END IF;
839
840 /*
841 ** R12 KI Changes : Pseudo Logic
842 **
843 **
844 **
845
846
847 -- ***** CALLER *****
848 -- JLZZPIDW for redefault witholding applicability
849 IF p_inv_dist_id IS NOT NULL THEN
850
851
852 -- ***** CALLER *****
853 -- Validation Processes
854 -- APXINWKB (JL.pld) for ship to location change
855 ELSE p_inv_dist_id IS NULL THEN
856
857 -- ***** CALLER *****
858 -- Validate Process in UI
859 IF p_calling_module = 'NO_OVERRIDE' THEN
860 -- Check if extended withholding distribution line exists for this invoice line
861
862 -- ***** CALLER *****
863 -- Validate Process in Invoice Import
864 -- APXINWKB (JL.pld) for ship to location change
865 ELSE
866 -- For each distribution for an invoice line loop
867
868 -- Get ship to location : LINE
869 -- Get taxpayer id : DIST
870 -- Get distribution line type : DIST
871 -- Call supp_wh_def_line
872 -- p_invoice_id
873 -- p_dis_lin_num -> p_dist_line_id
874 -- p_tax_payer_id
875 -- p_ship_to_loc
876 -- p_line_type
877 -- p_vendor_id
878
879 -- End loop
880 END IF;
881
882 END IF;
883
884 **
885 **
886 ** End of Pseudo logic
887 **
888 **
889 */
890 ---------------------------------------------------------------------
891 -- Checking calling point
892 ---------------------------------------------------------------------
893 --Bug no : 6159617 -Removed the IF condition. The P_Calling_Module was
894 -- called with null values and the changes made to the witholdings
895 -- were overridden by default values on validation.
896 -- IF P_Calling_Module = 'NO_OVERRIDE' THEN
897 --Bug no : 6395850 - Added the following code for handling Redefault withholding button.
898 IF p_calling_module = 'JLZZPIDW' THEN
899 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
900 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Supp_Wh_Def','IF p_calling_module = JLZZPIDW THEN');
901 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Supp_Wh_Def','Redefaulting the withholdings for distibution id '||P_Inv_Dist_Id);
902 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Supp_Wh_Def','Calling Del_Wh_Def');
903 END IF;
904 Del_Wh_Def(P_Inv_Dist_Id);
905 END IF;
906 --Bug no : 6395850 - The existing withholdings are erased and then redefaulted.
907 ------------------------------------------------------------------
908 -- Checking if the distribution line has withholdings
909 ------------------------------------------------------------------
910 --Bug no : 6215810 - Changed the query
911 -- Select 1
912 SELECT COUNT(*)
913 INTO DistWithholdings
914 From jl_zz_ap_inv_dis_wh
915 Where invoice_id = P_Invoice_ID
916 And invoice_distribution_id = P_Inv_Dist_Id;
917 -- And rownum = 1;
918 --Bug no : 6215810 -Commented the above line
919 -- Debug
920 IF (DEBUG_Var = 'Y') THEN
921 JL_ZZ_AP_EXT_AWT_UTIL.Debug ('AWT Defaulting Exist No Override - Returning');
922 END IF;
923 -- End Debug
924 --Bug no : 6159617 - Changed the IF Condition
925 -- IF DistWithholdings = 1 Then
926 IF DistWithholdings <> 0 THEN
927 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
931 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Supp_Wh_Def','End PROCEDURE Supp_Wh_Def - 1');
928 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Supp_Wh_Def','Inside IF DistWithholdings <> 0 THEN');
929 END IF;
930 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
932 END IF;
933 Return;
934 End IF;
935 -- End IF;
936 --Bug no : 6159617 -Removed the IF condition.
937 ---------------------------------------------------------------------
938 -- Cheking if parent distribution id is not null
939 --------------------------------------------------------------------
940 IF P_Parent_Dist_ID IS NOT NULL Then
941 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
942 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Supp_Wh_Def','Inside IF P_Parent_Dist_ID IS NOT NULL Then');
943 END IF;
944 ----------------------------------------------------------
945 -- Copy the tax names from the parent distribution line
946 ----------------------------------------------------------
947 -- Debug
948 IF (DEBUG_Var = 'Y') THEN
949 JL_ZZ_AP_EXT_AWT_UTIL.Debug ('AWT Defaulting from Parent Dist ID');
950 END IF;
951 -- End Debug
952 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
953 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Supp_Wh_Def','INSERT INTO jl_zz_ap_inv_dis_wh (...');
954 END IF;
955 INSERT INTO jl_zz_ap_inv_dis_wh (
956 inv_distrib_awt_id
957 ,invoice_id
958 -- Bug 4559478
959 ,invoice_distribution_id
960 ,distribution_line_number
961 ,supp_awt_code_id
962 ,created_by
963 ,creation_date
964 ,last_updated_by
965 ,last_update_date
966 ,last_update_login
967 )
968 SELECT
969 jl_zz_ap_inv_dis_wh_s.nextval
970 ,P_Invoice_Id
971 ,P_Inv_Dist_Id
972 -- Bug 4559478 : -99 for distribution_line_number
973 ,-99
974 ,jlid.Supp_Awt_Code_Id
975 ,v_last_update_by
976 ,sysdate
977 ,v_last_update_by
978 ,sysdate
979 ,v_last_update_login
980 FROM
981 jl_zz_ap_inv_dis_wh jlid
982 WHERE jlid.invoice_distribution_id = P_Parent_Dist_ID
983 AND jlid.invoice_id = P_Invoice_Id;
984 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
985 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Supp_Wh_Def','End PROCEDURE Supp_Wh_Def - 2');
986 END IF;
987 RETURN;
988 END IF;
989 ----------------------------------------------------------------------
990 -- Validate if the parameter P_Inv_Dis_Id IS NULL
991 ----------------------------------------------------------------------
992 IF p_inv_dist_id IS NOT NULL THEN
993 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
994 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Supp_Wh_Def','Inside IF p_inv_dist_id IS NOT NULL THEN');
995 END IF;
996 -------------------------------------------------------------------
997 -- Information Invoice Distributions
998 -------------------------------------------------------------------
999 -- Bug 4559472
1000 -- Revert changes for R12 - Bug 4674638
1001 -- Debug
1002 IF (DEBUG_Var = 'Y') THEN
1003 JL_ZZ_AP_EXT_AWT_UTIL.Debug ('AWT Defaulting From Param Inv Dist ID');
1004 END IF;
1005 -- End Debug
1006
1007 SELECT apid.global_attribute2 -- Taxpayer Id for Colombia
1008 ,apid.global_attribute3 -- Ship to Location Argentina/Colombia
1009 -- , apil.ship_to_location_id -- Ship to Location Argentina/Colombia
1010 , apid.line_type_lookup_code -- Line Type
1011 INTO v_tax_payer_id,
1012 v_ship_to_loc,
1013 v_line_type
1014 FROM AP_Invoice_Distributions apid,
1015 AP_Invoice_Lines apil
1016 WHERE apid.invoice_id = p_invoice_id
1017 AND apid.invoice_distribution_id = p_inv_dist_id
1018 AND apid.invoice_line_number = apil.line_number
1019 AND apid.invoice_id = apil.invoice_id;
1020 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1021 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Supp_Wh_Def','Calling Supp_Wh_Def_Line - 1');
1022 END IF;
1023 Supp_Wh_Def_Line( P_Invoice_Id
1024 , P_Inv_Dist_id
1025 , v_tax_payer_id
1026 , v_ship_to_loc
1027 , v_line_type
1028 , null
1029 );
1030
1031 ELSE
1032 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1033 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Supp_Wh_Def','Inside ELSE p_inv_dist_id IS NOT NULL THEN');
1034 END IF;
1038 -- Debug
1035 -------------------------------------------------------------
1036 -- Loop for each Invoice Distribution Line.
1037 -------------------------------------------------------------
1039 IF (DEBUG_Var = 'Y') THEN
1040 JL_ZZ_AP_EXT_AWT_UTIL.Debug ('AWT Defaulting for distributions');
1041 END IF;
1042 -- End Debug
1043 FOR db_reg IN Invoice_Distrib LOOP
1044 -------------------------------------------------------------------
1045 -- Information Invoice Distribution Lines.
1046 -------------------------------------------------------------------
1047 SELECT apid.global_attribute2 -- Taxpayer Id for Colombia
1048 ,apid.global_attribute3 -- Ship to Location Argentina/Colombia
1049 -- , apil.ship_to_location_id -- Ship to Location Argentina/Colombia
1050 , apid.line_type_lookup_code -- Line Type
1051 INTO v_tax_payer_id,
1052 v_ship_to_loc,
1053 v_line_type
1054 FROM AP_Invoice_Distributions apid,
1055 AP_Invoice_Lines apil
1056 WHERE apid.invoice_id = P_Invoice_Id
1057 AND apid.invoice_distribution_id = db_reg.invoice_distribution_id
1058 AND apid.invoice_line_number = apil.line_number
1059 AND apid.invoice_id = apil.invoice_id;
1060 --bug 6346106 changes - The following code is added
1061 SELECT COUNT(*)
1062 INTO DistWithholdings
1063 FROM jl_zz_ap_inv_dis_wh
1064 WHERE invoice_id = P_Invoice_ID
1065 AND invoice_distribution_id = db_reg.invoice_distribution_id;
1066 IF (DEBUG_Var = 'Y') THEN
1067 JL_ZZ_AP_EXT_AWT_UTIL.Debug ('AWT Defaulting Exist No Override - Returning');
1068 END IF;
1069
1070 IF DistWithholdings <> 0 THEN
1071 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1072 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Supp_Wh_Def','Inside IF DistWithholdings <> 0 THEN');
1073 END IF;
1074 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1075 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Supp_Wh_Def','End PROCEDURE Supp_Wh_Def - 3');
1076 END IF;
1077 --Return;
1078 --End IF;
1079 ELSE
1080 -- Code added for bug 6869263
1081 IF upper(p_calling_module) = upper('AP_APPROVAL_PKG.Approval<-APXINWKB') and DistWithholdings = 0 AND
1082 v_line_type NOT IN ('IPV','ERV','TRV','TIPV','TERV') THEN
1083
1084 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1085 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Supp_Wh_Def','Inside call to ap_approval_pkg and Distwith =0 and line type not in variance');
1086 END IF;
1087
1088 ELSIF upper(p_calling_module) = upper('AP_APPROVAL_PKG.Approval<-APXINWKB') and DistWithholdings = 0 AND
1089 v_line_type IN ('IPV','ERV','TRV','TIPV','TERV') THEN
1090
1091 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1092 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Supp_Wh_Def','Inside call to ap_approval_pkg and Distwith =0 and line type in variance');
1093 END IF;
1094
1095 SELECT related_id
1096 INTO v_related_dist_id
1097 FROM ap_invoice_distributions
1098 WHERE invoice_id = P_Invoice_Id
1099 AND invoice_distribution_id = db_reg.invoice_distribution_id;
1100
1101 FOR l_def_wh_dist IN c_default_wh_dist(v_related_dist_id) LOOP
1102
1103 INSERT INTO jl_zz_ap_inv_dis_wh (
1104 inv_distrib_awt_id
1105 ,invoice_id
1106 ,distribution_line_number
1107 ,invoice_distribution_id
1108 ,supp_awt_code_id
1109 ,created_by
1110 ,creation_date
1111 ,last_updated_by
1112 ,last_update_date
1113 ,last_update_login
1114 ,org_id -- Add org_id for MOAC
1115 )
1116 VALUES (
1117 jl_zz_ap_inv_dis_wh_s.nextval
1118 ,P_Invoice_Id
1119 ,-99
1120 , db_reg.invoice_distribution_id
1121 ,l_def_wh_dist.Supp_Awt_Code_Id
1122 ,v_last_update_by
1123 ,sysdate
1124 ,v_last_update_by
1125 ,sysdate
1126 ,v_last_update_login
1127 ,l_def_wh_dist.Org_Id
1128 );
1129
1130 END LOOP;
1131
1132 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1133 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Supp_Wh_Def','Inside call to ap_approval_pkg and Distwith =0 and line type in variance, inserted record into jl_zz_ap_inv_dis_wh table ');
1134 END IF;
1135
1136 ELSE
1137 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1138 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Supp_Wh_Def','Calling Supp_Wh_Def_Line - 2');
1139 END IF;
1143 , db_reg.invoice_distribution_id
1140 --bug 6346106 changes end
1141 Supp_Wh_Def_Line(
1142 P_Invoice_Id
1144 , v_tax_payer_id
1145 , v_ship_to_loc
1146 , v_line_type
1147 , null
1148 );
1149 END IF; -- upper(p_calling_module)
1150 END IF; -- DistWithholings <> 0
1151 END LOOP; -- Invoice Distribution Line
1152 END IF; -- P_Dis_Lin_Num IS NULL
1153 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1154 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Supp_Wh_Def','End PROCEDURE Supp_Wh_Def - 4');
1155 END IF;
1156 EXCEPTION
1157 WHEN OTHERS THEN
1158 IF (G_LEVEL_EXCEPTION >= G_CURRENT_RUNTIME_LEVEL ) THEN
1159 FND_LOG.STRING(G_LEVEL_EXCEPTION, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Supp_Wh_Def','Exception in PROCEDURE Supp_Wh_Def');
1160 NULL;
1161 END IF;
1162 END Supp_Wh_Def;
1163
1164 /*----------------------------------------------------------------------------------------------
1165 Carry_Withholdings_Prepay copy the withholdings from Prepayment Invoice
1166 Item Line to Standard Invoice PREPAY line.
1167 ------------------------------------------------------------------------------------------------*/
1168 --
1169 -- R12 KI
1170 --
1171
1172 PROCEDURE Carry_Withholdings_Prepay
1173 (P_prepay_dist_id Number
1174 ,P_Invoice_Id Number
1175 ,P_inv_dist_id Number
1176 ,P_User_Id Number
1177 ,P_last_update_login Number
1178 ,P_Calling_Sequence Varchar2
1179 ) IS
1180 l_prepay_id Number;
1181
1182 l_prepay_dist_line_num Number;
1183
1184 l_calling_sequence Varchar2(2000);
1185 Begin
1186 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1187 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Carry_Withholdings_Prepay','Start PROCEDURE Carry_Withholdings_Prepay');
1188 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Carry_Withholdings_Prepay','Parameters are :');
1189 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Carry_Withholdings_Prepay',' P_prepay_dist_id='||P_prepay_dist_id);
1190 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Carry_Withholdings_Prepay',' P_Invoice_Id='||P_Invoice_Id);
1191 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Carry_Withholdings_Prepay',' P_inv_dist_id='||P_inv_dist_id);
1192 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Carry_Withholdings_Prepay',' P_User_Id='||P_User_Id);
1193 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Carry_Withholdings_Prepay',' P_last_update_login='||P_last_update_login);
1194 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Carry_Withholdings_Prepay',' P_Calling_Sequence='||P_Calling_Sequence);
1195 END IF;
1196 -----------------------------------
1197 -- Value for p_calling_sequence
1198 -----------------------------------
1199 l_calling_sequence := P_calling_sequence||'Carry_Withholdings_Prepay';
1200
1201 ------------------------------------------------------------------------
1202 -- Get invoice_id and invoice_distribution_id from p_prepay_dist_id (PK)
1203 ------------------------------------------------------------------------
1204 /*
1205 ** Bug 4559474
1206 **
1207 ** Commented out the query as invoice_id and invoice_distribution_id
1208 ** for payment_distribution_id is passed from AP through
1209 ** input parameters.
1210 **
1211 SELECT invoice_id,
1212 invoice_distribution_id
1213 INTO l_prepay_id,
1214 l_prepay_dist_line_num
1215 FROM ap_invoice_distributions
1216 WHERE invoice_distribution_id = P_prepay_dist_id;
1217 */
1218
1219 -----------------------------------------------------------------------
1220 -- Copy the withholdings to the new PREPAY line.
1221 -- Insert Withholdings in the table.
1222 ----------------------------------------------------------------------
1223 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1224 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Carry_Withholdings_Prepay','INSERT INTO jl_zz_ap_inv_dis_wh ...');
1225 END IF;
1226 INSERT INTO jl_zz_ap_inv_dis_wh
1227 (INV_DISTRIB_AWT_ID,
1228 INVOICE_ID,
1229 distribution_line_number, -- Bug 4559474
1230 invoice_distribution_id, -- Bug 4559474
1231 SUPP_AWT_CODE_ID,
1232 CREATED_BY,
1233 CREATION_DATE,
1234 LAST_UPDATED_BY,
1235 LAST_UPDATE_DATE,
1236 LAST_UPDATE_LOGIN,
1237 ORG_ID,
1238 ATTRIBUTE_CATEGORY,
1239 ATTRIBUTE1,
1240 ATTRIBUTE2,
1241 ATTRIBUTE3,
1242 ATTRIBUTE4,
1243 ATTRIBUTE5,
1244 ATTRIBUTE6,
1245 ATTRIBUTE7,
1246 ATTRIBUTE8,
1247 ATTRIBUTE9,
1248 ATTRIBUTE10,
1249 ATTRIBUTE11,
1250 ATTRIBUTE12,
1251 ATTRIBUTE13,
1252 ATTRIBUTE14,
1253 ATTRIBUTE15)
1254 SELECT
1255 JL_ZZ_AP_INV_DIS_WH_S.nextval,
1256 P_Invoice_Id,
1257 -99, -- Bug 4559474
1258 p_inv_dist_id, -- Bug 4559474
1259 idw.supp_awt_code_id,
1260 P_user_id,
1261 SYSDATE,
1262 DECODE(P_last_update_login,-999,P_user_id,P_last_update_login),
1263 SYSDATE,
1264 DECODE(P_last_update_login,-999,P_user_id,P_last_update_login),
1265 idw.ORG_ID,
1266 idw.ATTRIBUTE_CATEGORY,
1267 idw.ATTRIBUTE1,
1268 idw.ATTRIBUTE2,
1269 idw.ATTRIBUTE3,
1270 idw.ATTRIBUTE4,
1271 idw.ATTRIBUTE5,
1272 idw.ATTRIBUTE6,
1273 idw.ATTRIBUTE7,
1274 idw.ATTRIBUTE8,
1275 idw.ATTRIBUTE9,
1276 idw.ATTRIBUTE10,
1277 idw.ATTRIBUTE11,
1278 idw.ATTRIBUTE12,
1279 idw.ATTRIBUTE13,
1280 idw.ATTRIBUTE14,
1281 idw.ATTRIBUTE15
1282 FROM jl_zz_ap_inv_dis_wh idw
1283 WHERE idw.invoice_distribution_id = p_prepay_dist_id; -- Bug 4559474
1284 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1285 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Carry_Withholdings_Prepay','End PROCEDURE Carry_Withholdings_Prepay');
1286 END IF;
1287 EXCEPTION
1288 WHEN OTHERS THEN
1289 IF (G_LEVEL_EXCEPTION >= G_CURRENT_RUNTIME_LEVEL ) THEN
1290 FND_LOG.STRING(G_LEVEL_EXCEPTION, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Carry_Withholdings_Prepay','Exception in PROCEDURE Carry_Withholdings_Prepay');
1291 NULL;
1292 END IF;
1293 END Carry_Withholdings_Prepay;
1294 /*----------------------------------------------------------------------------------------------
1295 Supp_Wh_ReDef receive as parameters the P_Invoice_Id and P_Vendor_ID
1296 Bug 3609925
1297 ------------------------------------------------------------------------------------------------*/
1298 PROCEDURE Supp_Wh_ReDefault
1299 ( P_Invoice_Id ap_invoices_all.invoice_id%TYPE
1300 , P_Vendor_ID po_vendors.vendor_id%TYPE
1301 ) IS
1302
1303 CURSOR Invoice_Distrib IS
1304 SELECT invoice_distribution_id
1305 FROM ap_invoice_distributions
1306 WHERE invoice_id = P_Invoice_ID;
1307 -- The following variables are used to get the information from the invoice
1308 -- ditribution lines.
1309 v_tax_payer_id ap_invoice_distributions_all.global_attribute2%TYPE;
1310 v_ship_to_loc ap_invoice_distributions_all.global_attribute3%TYPE;
1311 v_line_type ap_invoice_distributions_all. line_type_lookup_code%TYPE;
1312
1313 Begin
1314 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1315 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Supp_Wh_ReDefault','Start PROCEDURE Supp_Wh_ReDefault');
1316 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Supp_Wh_ReDefault','Parameters are :');
1317 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Supp_Wh_ReDefault',' P_Invoice_Id='||P_Invoice_Id);
1318 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Supp_Wh_ReDefault',' P_Vendor_ID='||P_Vendor_ID);
1319 END IF;
1320 -------------------------------------------------------------
1321 -- Loop for each Invoice Distribution Line.
1322 -------------------------------------------------------------
1323 FOR db_reg IN Invoice_Distrib LOOP
1324 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1325 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Supp_Wh_ReDefault','Inside FOR db_reg IN Invoice_Distrib LOOP');
1326 END IF;
1327 -------------------------------------------------------------------
1328 -- Information Invoice Distribution Lines.
1329 -------------------------------------------------------------------
1330 -- Revert changes for R12 - Bug 4674638
1331 SELECT apid.global_attribute2 -- Taxpayer Id for Colombia
1332 ,apid.global_attribute3 -- Ship to Location Argentina/Colombia
1333 ,apid.line_type_lookup_code -- Line Type
1334 INTO v_tax_payer_id,
1335 v_ship_to_loc,
1336 v_line_type
1337 FROM AP_Invoice_Distributions apid,
1338 AP_Invoice_Lines apil
1339 WHERE apid.invoice_id = P_Invoice_Id
1340 AND apid.invoice_distribution_id = db_reg.invoice_distribution_id
1341 AND apil.line_number = apid.invoice_line_number
1342 AND apid.invoice_id = apil.invoice_id;
1343 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1344 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Supp_Wh_ReDefault','Calling Supp_Wh_Def_Line');
1345 END IF;
1346 Supp_Wh_Def_Line(P_Invoice_Id
1347 ,db_reg.invoice_distribution_id
1348 ,v_tax_payer_id
1349 ,v_ship_to_loc
1350 ,v_line_type
1351 ,p_vendor_id
1352 );
1353
1354 END LOOP; -- Invoice Distribution Line
1355 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1356 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Supp_Wh_ReDefault','End PROCEDURE Supp_Wh_ReDefault');
1357 END IF;
1358 EXCEPTION
1359 WHEN OTHERS THEN
1360 IF (G_LEVEL_EXCEPTION >= G_CURRENT_RUNTIME_LEVEL ) THEN
1361 FND_LOG.STRING(G_LEVEL_EXCEPTION, 'JL.plsql.JL_ZZ_AP_AWT_DEFAULT_PKG.Supp_Wh_ReDefault','Exception in PROCEDURE Supp_Wh_ReDefault');
1362 NULL;
1363 END IF;
1364 END Supp_Wh_ReDefault;
1365
1366 END JL_ZZ_AP_AWT_DEFAULT_PKG; -- Package
[Home] [Help]