[Home] [Help]
PACKAGE BODY: APPS.AP_WITHHOLDING_PKG
Source
1 PACKAGE BODY AP_WITHHOLDING_PKG AS
2 /* $Header: apdoawtb.pls 120.87.12020000.12 2013/01/30 18:36:37 tjbhatt ship $ */
3
4 -- =====================================================================
5 -- P U B L I C O B J E C T S
6 -- =====================================================================
7
8 G_CURRENT_RUNTIME_LEVEL CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
9 G_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
10 G_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
11 G_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
12 G_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
13 G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
14 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
15 G_MODULE_NAME CONSTANT VARCHAR2(30) := 'AP.PLSQL.AP_WITHHOLDING_PKG.';
16
17 /*
18 bug13606808, Forward declaration for
19 private api Ap_Undo_Orphan_Distributions
20 */
21 PROCEDURE Ap_Undo_Orphan_Distributions (
22 P_Invoice_Id IN NUMBER,
23 P_Vendor_Id IN NUMBER DEFAULT NULL,
24 P_Payment_Num IN NUMBER,
25 P_Checkrun_Name IN VARCHAR2,
26 P_Undo_Awt_Date IN DATE,
27 P_Calling_Module IN VARCHAR2,
28 P_Last_Updated_By IN NUMBER,
29 P_Last_Update_Login IN NUMBER,
30 P_Program_Application_Id IN NUMBER DEFAULT NULL,
31 P_Program_Id IN NUMBER DEFAULT NULL,
32 P_Request_Id IN NUMBER DEFAULT NULL,
33 P_Awt_Success OUT NOCOPY VARCHAR2,
34 P_checkrun_id in number default null);
35
36 PROCEDURE Create_AWT_Distributions(
37 P_Invoice_Id IN NUMBER,
38 P_Calling_Module IN VARCHAR2,
39 P_Create_dists IN VARCHAR2,
40 P_Payment_Num IN NUMBER,
41 P_Currency_Code IN VARCHAR2,
42 P_Last_Updated_By IN NUMBER,
43 P_Last_Update_Login IN NUMBER,
44 P_Program_Application_Id IN NUMBER,
45 P_Program_Id IN NUMBER,
46 P_Request_Id IN NUMBER,
47 P_Calling_Sequence IN VARCHAR2,
48 P_Check_Id IN NUMBER DEFAULT NULL) -- 8590059
49 IS
50 withholding_total NUMBER := 0;
51 base_withholding_total NUMBER := 0;
52 l_invoice_distribution_id ap_invoice_distributions.invoice_distribution_id%TYPE;
53
54 --Bug 8266021 Changed Cursor to include two additional parameters GRP_ID and TAXID
55 --and included these two parameters in the where clause
56 CURSOR C_temp_dists (InvId IN NUMBER,GRP_ID IN NUMBER,TAXID IN NUMBER) IS
57 SELECT AATD.invoice_id
58 , AATD.payment_num
59 , AATD.group_id
60 , AATD.tax_name
61 , AATD.tax_code_combination_id
62 , AATD.gross_amount
63 , AATD.withholding_amount
64 , AATD.base_withholding_amount
65 , AATD.accounting_date
66 , AATD.period_name
67 , AATD.checkrun_name
68 , AATD.tax_rate_id
69 , AATD.invoice_payment_id
70 , TC.tax_id tax_code_id
71 , AATD.GLOBAL_ATTRIBUTE_CATEGORY
72 , AATD.GLOBAL_ATTRIBUTE1
73 , AATD.GLOBAL_ATTRIBUTE2
74 , AATD.GLOBAL_ATTRIBUTE3
75 , AATD.GLOBAL_ATTRIBUTE4
76 , AATD.GLOBAL_ATTRIBUTE5
77 , AATD.GLOBAL_ATTRIBUTE6
78 , AATD.GLOBAL_ATTRIBUTE7
79 , AATD.GLOBAL_ATTRIBUTE8
80 , AATD.GLOBAL_ATTRIBUTE9
81 , AATD.GLOBAL_ATTRIBUTE10
82 , AATD.GLOBAL_ATTRIBUTE11
83 , AATD.GLOBAL_ATTRIBUTE12
84 , AATD.GLOBAL_ATTRIBUTE13
85 , AATD.GLOBAL_ATTRIBUTE14
86 , AATD.GLOBAL_ATTRIBUTE15
87 , AATD.GLOBAL_ATTRIBUTE16
88 , AATD.GLOBAL_ATTRIBUTE17
89 , AATD.GLOBAL_ATTRIBUTE18
90 , AATD.GLOBAL_ATTRIBUTE19
91 , AATD.GLOBAL_ATTRIBUTE20
92 , AI.org_id
93 , AATD.awt_related_id
94 , aatd.checkrun_id
95 , TC.description --Bug5502917
96 FROM ap_awt_temp_distributions_all AATD,
97 ap_invoices_all AI,
98 ap_tax_codes_all TC,
99 ap_invoice_distributions_all AID --bug 7930936
100 WHERE AATD.invoice_id = InvId
101 AND AATD.group_id = GRP_ID
102 AND AATD.invoice_id = AI.invoice_id
103 AND TC.TAX_ID = TAXID
104 AND AATD.tax_name = TC.name(+)
105 AND TC.org_id = AI.org_id -- Bug5902006
106 AND TC.tax_type = 'AWT' -- Bug3665866
107 AND NVL(TC.enabled_flag,'Y') = 'Y'
108 AND ( P_Payment_Num IS NULL
109 OR AATD.payment_num = P_Payment_Num)
110 AND NVL(AI.invoice_date,SYSDATE) BETWEEN
111 NVL(TC.start_date,NVL(AI.invoice_date,SYSDATE)) AND
112 NVL(TC.inactive_date,NVL(AI.invoice_date,SYSDATE))
113 AND AATD.invoice_id = AID.invoice_id --bug 7930936
114 AND AATD.awt_related_id = AID.invoice_distribution_id --bug 7930936
115 AND AID.prepay_distribution_id is NULL --bug 7930936
116 ORDER BY AATD.tax_name,
117 AATD.tax_rate_id
118 FOR UPDATE of AATD.invoice_id;
119 rec_temp_dists c_temp_dists%ROWTYPE;
120
121 CURSOR c_invoice (InvId IN NUMBER) IS
122 SELECT AI.set_of_books_id
123 , AI.org_id --bug 8266021
124 , AI.accts_pay_code_combination_id
125 , AI.batch_id
126 , AI.description
127 , AI.invoice_amount
128 , NVL(AI.payment_cross_rate,1) payment_cross_rate
129 , AI.payment_currency_code
130 , AI.exchange_date
131 , NVL(AI.exchange_rate, 1) exchange_rate
132 , AI.exchange_rate_type
133 --, AI.ussgl_transaction_code - Bug 4277744
134 --, AI.ussgl_trx_code_context - Bug 4277744
135 , AI.vat_code
136 , NVL(PV.federal_reportable_flag, 'N') federal_reportable_flag
137 , AI.vendor_site_id vendor_site_id
138 , AI.amount_applicable_to_discount
139 FROM ap_invoices_all AI,
140 po_vendors PV
141 WHERE PV.vendor_id(+) = DECODE(AI.invoice_type_lookup_code,'PAYMENT REQUEST', NULL, AI.vendor_id) --bug8272564
142 AND AI.invoice_id = InvId
143 FOR UPDATE of AI.invoice_id;
144
145 rec_invoice c_invoice%ROWTYPE;
146
147 --Bug 8266021 added new cursor
148 CURSOR C_line_cursor (InvId IN NUMBER) IS
149 SELECT AATD.group_id
150 , AATD.invoice_payment_id
151 , TC.TAX_ID
152 , SUM(AATD.withholding_amount) AMOUNT
153 , SUM(AATD.base_withholding_amount) BASE_AMOUNT
154 , MIN(AATD.accounting_date) ACCOUNTING_DATE
155 FROM ap_awt_temp_distributions_all AATD,
156 ap_invoices_all AI,
157 ap_tax_codes_all TC,
158 ap_invoice_distributions_all AID --bug 7930936
159 WHERE AATD.invoice_id = InvId
160 AND AATD.invoice_id = AI.invoice_id
161 AND AATD.tax_name = TC.name(+)
162 AND TC.org_id = AI.org_id -- Bug5902006
163 AND TC.tax_type = 'AWT' -- Bug3665866
164 AND NVL(TC.enabled_flag,'Y') = 'Y'
165 AND ( P_Payment_Num IS NULL
166 OR AATD.payment_num = P_Payment_Num)
167 AND NVL(AI.invoice_date,SYSDATE) BETWEEN
168 NVL(TC.start_date,NVL(AI.invoice_date,SYSDATE)) AND
169 NVL(TC.inactive_date,NVL(AI.invoice_date,SYSDATE))
170 AND AATD.invoice_id = AID.invoice_id --bug 7930936
171 AND AATD.awt_related_id = AID.invoice_distribution_id --bug 7930936
172 AND AID.prepay_distribution_id is NULL --bug 7930936
173 GROUP BY AATD.group_id,AATD.invoice_payment_id,TC.tax_id;
174
175 rec_temp_lines C_line_cursor%ROWTYPE;
176
177 CURSOR C_Current_Line (InvId IN NUMBER)
178 IS
179 SELECT MAX(line_number) curr_inv_line_number
180 FROM ap_invoice_lines_all
181 WHERE (invoice_id = InvId);
182
183 curr_inv_line_number ap_invoice_lines_all.line_number%TYPE;
184 --bug 8266021
185 curr_inv_dist_line_number ap_invoice_distributions_all.distribution_line_number%TYPE;
186
187 --bug 7930936
188 CURSOR C_NONPREPAY_AWT (InvId IN NUMBER)
189 IS
190 SELECT AATD.*
191 FROM ap_awt_temp_distributions_all AATD,
192 ap_invoice_distributions_all AID
193 WHERE AATD.invoice_id = InvId
194 AND AATD.invoice_id = AID.invoice_id
195 AND AATD.awt_related_id = AID.invoice_distribution_id
196 AND AID.prepay_distribution_id is NULL;
197
198 rec_nonprepay_awt C_NONPREPAY_AWT%ROWTYPE;
199
200 /* bug 7930936 added the above cursor to include to select all non prepay awt distributions
201 against which the prepay awt amount should be prorated*/
202
203 l_prepay_awt_amount NUMBER; -- bug7930936
204 l_prepay_awt_base_amount NUMBER; -- bug7930936
205 l_non_prepay_awt_amount NUMBER; -- bug7930936
206 l_non_prepay_awt_base_amount NUMBER; -- bug7930936
207 l_pro_prepay_awt_amt NUMBER; -- bug7930936
208 l_pro_prepay_awt_base_amt NUMBER; -- bug7930936
209 l_sum_prorated_awt_amt NUMBER := 0; -- bug7930936
210 l_sum_prorated_awt_base_amt NUMBER := 0; -- bug7930936
211 l_awt_related_id NUMBER; -- bug7930936
212 l_tax_rate_id NUMBER; -- bug7930936
213 l_amt_diff NUMBER; -- bug7930936
214 l_base_amt_diff NUMBER; -- bug7930936
215
216 --bug 7930936
217
218 DBG_Loc VARCHAR2(30) := 'Create_AWT_distributions';
219
220 current_calling_sequence VARCHAR2(2000);
221 debug_info VARCHAR2(1000);
222 l_disc_amt_factor NUMBER;
223 l_disc_amt_divisor NUMBER; -- BUG 7000143
224 l_basecur ap_system_parameters.base_currency_code%TYPE;
225 l_enable_1099_on_awt_flag ap_system_parameters.enable_1099_on_awt_flag%TYPE;
226 l_type_1099 ap_invoice_distributions.type_1099%TYPE;
227 l_combined_filing_flag ap_system_parameters.combined_filing_flag%TYPE;
228 l_income_tax_region_asp ap_system_parameters.income_tax_region%TYPE;
229 l_income_tax_region_pvs ap_system_parameters.income_tax_region%TYPE;
230 l_income_tax_region_flag ap_system_parameters.income_tax_region_flag%TYPE;
231 l_income_tax_region ap_system_parameters.income_tax_region%TYPE;
232
233 l_period_name gl_period_statuses.period_name%TYPE; --added for bug 8266021
234
235 l_exchange_rate ap_checks_all.exchange_rate%type; -- added for bug 8590059
236
237 l_withhold_amount NUMBER; --8726501
238
239 -- bug8879522
240 l_sum_dists NUMBER;
241 l_sum_dists_base NUMBER;
242 l_line_amt NUMBER;
243 l_line_base_amt NUMBER;
244 l_round_amt NUMBER;
245 l_round_base_amt NUMBER;
246 l_dist_id_to_round NUMBER;
247
248 /* These variables use to calculate the discount amount if the exclude freight from disc option enabled */
249 l_sub_withhold_amt NUMBER := 0; -- 10183587
250 l_exclude_freight_from_disc VARCHAR2(2) := 'N'; -- 10183587
251
252
253 BEGIN
254 current_calling_sequence := 'AP_WITHHOLDING_PKG.Create_AWT_distributions<-' ||
255 P_Calling_Sequence;
256
257
258 debug_info := 'Get 1099 Info From ASP';
259 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
260 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
261 END IF;
262
263
264 SELECT NVL(enable_1099_on_awt_flag, 'N'),
265 combined_filing_flag,
266 income_tax_region_flag,
267 income_tax_region,
268 base_currency_code
269 INTO l_enable_1099_on_awt_flag,
270 l_combined_filing_flag,
271 l_income_tax_region_flag,
272 l_income_tax_region_asp,
273 l_basecur
274 FROM ap_system_parameters_all asp,
275 ap_invoices_all ai
276 WHERE ai.org_id = asp.org_id
277 and ai.invoice_id = p_invoice_id;
278
279 --bug 7930936
280 SELECT nvl(sum(AATD.withholding_amount),0),nvl(sum(AATD.base_withholding_amount),0)
281 INTO l_prepay_awt_amount,l_prepay_awt_base_amount
282 FROM ap_awt_temp_distributions_all AATD,
283 ap_invoice_distributions_all AID
284 WHERE AATD.invoice_id = P_Invoice_Id
285 AND AATD.invoice_id = AID.invoice_id
286 AND AATD.awt_related_id = AID.invoice_distribution_id
287 AND AID.prepay_distribution_id is not NULL;
288
289 /* bug 7930936 The above query will select the total prepay awt amount from
290 ap_awt_temp_distributions table and this will be prorated against other
291 non prepay awt distributions */
292
293 SELECT sum(AATD.withholding_amount),sum(AATD.base_withholding_amount)
294 INTO l_non_prepay_awt_amount,l_non_prepay_awt_base_amount
295 FROM ap_awt_temp_distributions_all AATD,
296 ap_invoice_distributions_all AID
297 WHERE AATD.invoice_id = P_Invoice_Id
298 AND AATD.invoice_id = AID.invoice_id
299 AND AATD.awt_related_id = AID.invoice_distribution_id
300 AND AID.prepay_distribution_id is NULL;
301
302 /* bug 7930936 The above query will select the total non prepay awt amount from
303 ap_awt_temp_distributions table and this will be used in the proration formula */
304
305 debug_info := 'l_prepay_awt_amount -- '||l_prepay_awt_amount;
306 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
307 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
308 END IF;
309 debug_info := 'l_non_prepay_awt_amount -- '||l_non_prepay_awt_amount;
310 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
311 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
312 END IF;
313
314 IF (l_prepay_awt_amount <> 0 AND l_prepay_awt_base_amount <> 0 AND
315 (l_prepay_awt_amount + l_non_prepay_awt_amount) > 0) THEN
316
317 /* bug 7930936 Enter into proration logic only if prepay awt amount exists
318 and the prepay awt amount is not more than standard invoice awt amount.
319 Here the awt amount sign would be opposite to what we see in ap_invoie_distributions_all table. */
320
321 debug_info := 'l_prepay_awt_amount + l_non_prepay_awt_amount -- '||(l_prepay_awt_amount+l_non_prepay_awt_amount);
322 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
323 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
324 END IF;
325
326 debug_info := 'OPEN CURSOR C_NONPREPAY_AWT';
327 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
328 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
329 END IF;
330
331 OPEN C_NONPREPAY_AWT(P_Invoice_Id);
332
333 <<FOR_EACH_NONPREPAY_AWT>>
334
335 LOOP
336
337 debug_info := 'Fetch CURSOR C_NONPREPAY_AWT';
338 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
339 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
340 END IF;
341 FETCH C_NONPREPAY_AWT INTO rec_nonprepay_awt;
342
343 EXIT WHEN C_NONPREPAY_AWT%NOTFOUND;
344
345 l_pro_prepay_awt_amt := (rec_nonprepay_awt.withholding_amount * l_prepay_awt_amount)
346 /l_non_prepay_awt_amount;
347
348 debug_info := 'l_pro_prepay_awt_amt -- '||l_pro_prepay_awt_amt;
349 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
350 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
351 END IF;
352
353 l_pro_prepay_awt_base_amt := (rec_nonprepay_awt.base_withholding_amount * l_prepay_awt_base_amount)
354 /l_non_prepay_awt_base_amount;
355
356 /* bug 7930936 Above is the proration formula */
357
358 debug_info := 'l_pro_prepay_awt_base_amt -- '||l_pro_prepay_awt_base_amt;
359 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
360 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
361 END IF;
362
363 l_sum_prorated_awt_amt := l_sum_prorated_awt_amt + l_pro_prepay_awt_amt;
364
365 debug_info := 'l_sum_prorated_awt_amt -- '||l_sum_prorated_awt_amt;
366 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
367 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
368 END IF;
369
370 l_sum_prorated_awt_base_amt := l_sum_prorated_awt_base_amt + l_pro_prepay_awt_base_amt;
371
372 debug_info := 'l_sum_prorated_awt_base_amt -- '||l_sum_prorated_awt_base_amt;
373 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
374 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
375 END IF;
376
377 update ap_awt_temp_distributions_all
378 set withholding_amount = withholding_amount + l_pro_prepay_awt_amt,
379 base_withholding_amount = base_withholding_amount + l_pro_prepay_awt_base_amt
380 where invoice_id = rec_nonprepay_awt.invoice_id
381 and awt_related_id = rec_nonprepay_awt.awt_related_id
382 and tax_rate_id = rec_nonprepay_awt.tax_rate_id;
383
384 l_tax_rate_id := rec_nonprepay_awt.tax_rate_id;
385 l_awt_related_id := rec_nonprepay_awt.awt_related_id;
386
387 END LOOP FOR_EACH_NONPREPAY_AWT;
388
389 /* bug 7930936 Added the below check to handle any rounding diff if created due to this
390 proration and adjust that rounding diff against the last non prepay awt distribution */
391
392 IF (l_sum_prorated_awt_amt <> l_prepay_awt_amount OR
393 l_sum_prorated_awt_base_amt <> l_prepay_awt_base_amount)
394 THEN
395 l_amt_diff := l_prepay_awt_amount-l_sum_prorated_awt_amt;
396 l_base_amt_diff := l_prepay_awt_base_amount-l_sum_prorated_awt_base_amt;
397 debug_info := 'l_amt_diff -- '||l_amt_diff;
398 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
399 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
400 END IF;
401 debug_info := 'l_base_amt_diff -- '||l_base_amt_diff;
402 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
403 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
404 END IF;
405
406 update ap_awt_temp_distributions_all
407 set withholding_amount = withholding_amount + l_amt_diff,
408 base_withholding_amount = base_withholding_amount + l_base_amt_diff
409 where invoice_id = P_INVOICE_ID
410 and awt_related_id = l_awt_related_id
411 and tax_rate_id = l_tax_rate_id;
412 END IF;
413
414 debug_info := 'CLSOE CURSOR C_NONPREPAY_AWT';
415 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
416 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
417 END IF;
418
419 CLOSE C_NONPREPAY_AWT;
420 END IF; --l_prepay_awt_amount<>0
421
422 --bug 7930936
423
424 debug_info := 'OPEN CURSOR C_Current_Line';
425 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
426 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
427 END IF;
428 OPEN C_Current_line (P_Invoice_Id);
429
430 debug_info := 'Fetch CURSOR c_current_line';
431 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
432 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
433 END IF;
434 FETCH C_Current_line INTO curr_inv_line_number;
435
436 debug_info := 'CLOSE CURSOR C_Current_Line';
437 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
438 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
439 END IF;
440 CLOSE C_Current_Line;
441
442 debug_info := 'OPEN CURSOR c_invoice';
443 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
444 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
445 END IF;
446 OPEN c_invoice (P_Invoice_Id);
447
448 debug_info := 'Fetch CURSOR c_invoice';
449 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
450 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
451 END IF;
452 FETCH c_invoice INTO rec_invoice;
453
454 debug_info := 'Check 1099 Info From Rec_Invoice';
455 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
456 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
457 END IF;
458 IF (l_enable_1099_on_awt_flag = 'Y') THEN
459 IF (rec_invoice.federal_reportable_flag = 'Y') THEN
460
461 l_type_1099 := 'MISC4';
462 IF (l_combined_filing_flag = 'Y') THEN
463 IF (l_income_tax_Region_flag = 'Y') THEN
464 BEGIN
465 SELECT SUBSTR(state, 1, 10)
466 INTO l_income_tax_region
467 FROM po_vendor_sites_all
468 WHERE vendor_site_id = rec_invoice.vendor_site_id
469 AND NVL(tax_reporting_site_flag, 'N') = 'Y';
470
471 EXCEPTION
472 WHEN NO_DATA_FOUND THEN
473 l_income_tax_region := NULL;
474 END;
475 ELSE
476 l_income_tax_region := l_income_tax_region_asp;
477 END IF;
478 ELSE
479 l_income_tax_region := NULL;
480 END IF;
481 ELSE
482 l_type_1099 := NULL;
483 END IF;
484 END IF;
485
486 -- bug 8266021 Opened line cursor
487 debug_info := 'OPEN CURSOR C_line_cursor';
488 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
489 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
490 END IF;
491 OPEN C_line_cursor (P_Invoice_Id);
492
493 <<FOR_EACH_TEMPORARY_LINE>>
494 LOOP
495 debug_info := 'Fetch CURSOR C_line_cursor';
496 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
497 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
498 END IF;
499 FETCH C_line_cursor INTO rec_temp_lines;
500
501 EXIT WHEN C_line_cursor%NOTFOUND;
502
503 -- Increment the Invoice Line Number
504 curr_inv_line_number := curr_inv_line_number + 1;
505
506 SELECT DISTINCT gps.Period_Name
507 INTO l_period_name
508 FROM gl_Period_Statuses gps,
509 ap_System_Parameters_All Asp
510 WHERE gps.Application_Id = 200
511 AND gps.Set_Of_Books_Id = Asp.Set_Of_Books_Id
512 AND Nvl(gps.Adjustment_Period_Flag,'N') = 'N'
513 AND rec_temp_lines.accounting_date BETWEEN Trunc(gps.Start_Date)
514 AND Trunc(gps.End_Date)
515 AND Nvl(Asp.Org_Id,- 99) = Nvl(rec_invoice.org_id,- 99)
516 AND gps.closing_Status in ('O', 'F');
517
518 debug_info := 'group_id'||rec_temp_lines.group_id;
519 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
520 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
521 END IF;
522
523 debug_info := 'P_Calling_Module -- '||P_Calling_Module;
524 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
525 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
526 END IF;
527
528 -- Now we have obtained all the required information AND we can
529 -- create lines
530
531 --pay_wht_project 8590059
532 IF (P_Calling_Module = 'QUICKCHECK') then
533
534 debug_info := 'Inside QUICKCHECK';
535 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
536 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
537 END IF;
538
539 SELECT exchange_rate
540 INTO l_exchange_rate
541 FROM ap_checks_all
542 WHERE check_id = P_Check_Id;
543 ELSIF (P_Calling_Module = 'CONFIRM') then
544
545 debug_info := 'Inside CONFIRM';
546 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
547 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
548 END IF;
549
550 SELECT DISTINCT payment_exchange_rate /* Bug 9666111 added distinct */
551 INTO l_exchange_rate
552 FROM ap_selected_invoices_all
553 WHERE invoice_id = P_Invoice_Id;
554 ELSE
555 debug_info := 'Inside VALIDATION';
556 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
557 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
558 END IF;
559
560 l_exchange_rate := rec_invoice.exchange_rate;
561 END IF;
562 --pay_wht_project 8590059
563
564 --bug 8726501
565 IF (P_Calling_Module = 'AUTOAPPROVAL') then
566 l_withhold_amount := -rec_temp_lines.amount/nvl(l_exchange_rate,1);
567 ELSE
568 l_withhold_amount := -rec_temp_lines.amount/(nvl(l_exchange_rate,1)*rec_invoice.payment_cross_rate);
569 END IF;
570 --bug 8726501
571
572 --Bug 8266021 insert in ap_invoice_lines is changed to insert single line per tax code/group id
573
574 debug_info := 'Insert INTO ap_invoice_lines_all';
575 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
576 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
577 END IF;
578
579 INSERT INTO AP_INVOICE_LINES_all (
580 invoice_id,
581 line_number,
582 line_type_lookup_code,
583 description,
584 line_source,
585 generate_dists,
586 match_type,
587 prorate_across_all_items,
588 accounting_date,
589 period_name,
590 deferred_acctg_flag,
591 set_of_books_id,
592 amount,
593 base_amount,
594 rounding_amt,
595 wfapproval_status,
596 -- ussgl_transaction_code, - Bug 4277744
597 discarded_flag,
598 cancelled_flag,
599 income_tax_region,
600 type_1099,
601 final_match_flag,
602 assets_tracking_flag,
603 awt_group_id,
604 creation_date,
605 created_by,
606 last_update_date,
607 last_updated_by,
608 last_update_login,
609 program_application_id,
610 program_id,
611 program_UPDATE_date,
612 request_id,
613 org_id, --7230158
614 pay_awt_group_id) --7230158
615 VALUES
616 ( P_Invoice_ID,
617 curr_inv_line_number,
618 'AWT',
619 rec_invoice.description,
620 'AUTO WITHHOLDING',
621 'D',
622 'NOT_MATCHED',
623 'N',
624 rec_temp_lines.accounting_date,
625 l_period_name,
626 'N',
627 rec_invoice.set_of_books_id,
628 ap_utilities_pkg.ap_round_currency(
629 l_withhold_amount, -- bug 8726501
630 p_currency_code), -- bug 8590059
631 ap_utilities_pkg.ap_round_currency(
632 -rec_temp_lines.base_amount,
633 l_basecur),
634 0,
635 'NOT REQUIRED', /*bug 4994642, was 'NOT_REQUIRED' */
636 -- rec_invoice.ussgl_transaction_code, - Bug 4277744
637 'N',
638 'N',
639 l_income_tax_region,
640 l_type_1099,
641 'N',
642 'N',
643 decode (rec_temp_lines.invoice_payment_id,NULL, rec_temp_lines.group_id,NULL), --7230158,
644 SYSDATE,
645 P_Last_Updated_By,
646 SYSDATE,
647 P_Last_Updated_By,
648 P_Last_Update_Login,
649 P_Program_Application_ID,
650 P_Program_ID,
651 SYSDATE,
652 P_request_ID,
653 rec_invoice.org_id, --7230158
654 decode (rec_temp_lines.invoice_payment_id,NULL,NULL,rec_temp_lines.group_id)); --7230158
655
656 -- bug8879522
657 l_line_amt := ap_utilities_pkg.ap_round_currency
658 (l_withhold_amount,
659 p_currency_code);
660 l_line_base_amt := ap_utilities_pkg.ap_round_currency
661 (-rec_temp_lines.base_amount,
662 l_basecur);
663 l_sum_dists := 0;
664 l_sum_dists_base := 0;
665
666 debug_info := ' After Initializing the line amounts and setting '||
667 ' dist running totals to 0 for line '||curr_inv_line_number||
668 ' l_line_amt :'||l_line_amt||' l_line_base_amt :'||l_line_base_amt;
669 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
670 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
671 END IF;
672
673
674
675 --Bug 8266021 now the distributions related to this line will be inserted
676 debug_info := 'OPEN CURSOR c_temp_dists';
677 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
678 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
679 END IF;
680 OPEN c_temp_dists (P_Invoice_Id ,rec_temp_lines.group_id,rec_temp_lines.tax_id);
681
682 <<FOR_EACH_TEMPORARY_DIST>>
683 curr_inv_dist_line_number := 0 ;
684
685 LOOP
686 debug_info := 'Fetch CURSOR c_temp_dists';
687 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
688 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
689 END IF;
690 FETCH c_temp_dists INTO rec_temp_dists;
691
692 EXIT WHEN c_temp_dists%NOTFOUND;
693
694 -- Increment the distribution Line Number
695 curr_inv_dist_line_number := curr_inv_dist_line_number + 1;
696
697 --bug 8726501
698 IF (P_Calling_Module = 'AUTOAPPROVAL') then
699 l_withhold_amount := -rec_temp_dists.withholding_amount/nvl(l_exchange_rate,1);
700 ELSE
701 l_withhold_amount := -rec_temp_dists.withholding_amount/(nvl(l_exchange_rate,1)*rec_invoice.payment_cross_rate);
702 END IF;
703 --bug 8726501
704
705 debug_info := 'dist_num'||curr_inv_dist_line_number;
706 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
707 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
708 END IF;
709
710 debug_info := 'Insert INTO ap_invoice_distributions';
711 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
712 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
713 END IF;
714
715 INSERT INTO ap_invoice_distributions_all (
716 accounting_date
717 ,accrual_posted_flag
718 ,assets_addition_flag
719 ,assets_tracking_flag
720 ,cash_posted_flag
721 ,distribution_line_number
722 ,dist_code_combination_id
723 ,invoice_id
724 ,invoice_line_number
725 ,last_updated_by
726 ,last_update_date
727 ,line_type_lookup_code
728 ,period_name
729 ,set_of_books_id
730 ,amount
731 ,base_amount
732 ,batch_id
733 ,created_by
734 ,creation_date
735 ,description
736 ,last_update_login
737 ,match_status_flag
738 ,posted_flag
739 ,program_application_id
740 ,program_id
741 ,program_UPDATE_date
742 ,request_id
743 ,withholding_tax_code_id /* Bug 5382525 */
744 ,encumbered_flag
745 ,pa_addition_flag
746 ,posted_amount
747 ,posted_base_amount
748 -- ,ussgl_transaction_code - Bug 4277744
749 -- ,ussgl_trx_code_context - Bug 4277744
750 ,awt_flag
751 ,awt_tax_rate_id
752 ,awt_gross_amount
753 ,awt_origin_group_id
754 ,awt_invoice_payment_id
755 ,invoice_distribution_id
756 ,GLOBAL_ATTRIBUTE_CATEGORY
757 ,GLOBAL_ATTRIBUTE1
758 ,GLOBAL_ATTRIBUTE2
759 ,GLOBAL_ATTRIBUTE3
760 ,GLOBAL_ATTRIBUTE4
761 ,GLOBAL_ATTRIBUTE5
762 ,GLOBAL_ATTRIBUTE6
763 ,GLOBAL_ATTRIBUTE7
764 ,GLOBAL_ATTRIBUTE8
765 ,GLOBAL_ATTRIBUTE9
766 ,GLOBAL_ATTRIBUTE10
767 ,GLOBAL_ATTRIBUTE11
768 ,GLOBAL_ATTRIBUTE12
769 ,GLOBAL_ATTRIBUTE13
770 ,GLOBAL_ATTRIBUTE14
771 ,GLOBAL_ATTRIBUTE15
772 ,GLOBAL_ATTRIBUTE16
773 ,GLOBAL_ATTRIBUTE17
774 ,GLOBAL_ATTRIBUTE18
775 ,GLOBAL_ATTRIBUTE19
776 ,GLOBAL_ATTRIBUTE20
777 ,type_1099
778 ,income_tax_region
779 ,org_id
780 ,awt_related_id
781 --Freight and Special Charges
782 ,rcv_charge_addition_flag
783 ,distribution_class -- bug 8620272
784 )
785 VALUES
786 (
787 rec_temp_dists.accounting_date
788 ,'N'
789 ,'N'
790 ,'N'
791 ,'N'
792 ,curr_inv_dist_line_number -- distribution_line_number
793 ,rec_temp_dists.tax_code_combination_id
794 ,P_Invoice_Id
795 ,curr_inv_line_number -- invoice_line_number
796 ,P_Last_Updated_By
797 ,SYSDATE
798 ,'AWT'
799 ,rec_temp_dists.period_name
800 ,rec_invoice.set_of_books_id
801 ,ap_utilities_pkg.ap_round_currency(
802 l_withhold_amount, -- bug 8726501
803 p_currency_code) -- bug 8590059
804 ,ap_utilities_pkg.ap_round_currency(-rec_temp_dists.base_withholding_amount,
805 l_basecur)
806 ,rec_invoice.batch_id
807 ,P_Last_Updated_By
808 ,SYSDATE
809 ,rec_temp_dists.description --Bug5502917 Replaced rec_invoice.description
810 ,P_Last_Update_Login
811 ,decode (P_Calling_Module, 'INVOICE ENTRY','N',
812 'INVOICE INQUIRY','N',
813 'A')
814 ,'N'
815 ,P_Program_Application_Id
816 ,P_Program_Id
817 ,decode (P_Program_Id,NULL,NULL,SYSDATE)
818 ,P_Request_Id
819 ,rec_temp_dists.tax_code_id
820 ,'T'
821 ,'E'
822 ,0
823 ,0
824 -- ,rec_invoice.ussgl_transaction_code - Bug 4277744
825 -- ,rec_invoice.ussgl_trx_code_context - Bug 4277744
826 ,decode (P_Calling_Module, 'AWT REPORT', 'P',
827 'A')
828 ,rec_temp_dists.tax_rate_id
829 ,ap_utilities_pkg.ap_round_currency(
830 rec_temp_dists.gross_amount/nvl(l_exchange_rate,1), --bug 8590059
831 P_currency_code)
832 ,rec_temp_dists.group_id
833 ,rec_temp_dists.invoice_payment_id
834 ,ap_invoice_distributions_s.nextval
835 ,rec_temp_dists.GLOBAL_ATTRIBUTE_CATEGORY
836 ,rec_temp_dists.GLOBAL_ATTRIBUTE1
837 ,rec_temp_dists.GLOBAL_ATTRIBUTE2
838 ,rec_temp_dists.GLOBAL_ATTRIBUTE3
839 ,rec_temp_dists.GLOBAL_ATTRIBUTE4
840 ,rec_temp_dists.GLOBAL_ATTRIBUTE5
841 ,rec_temp_dists.GLOBAL_ATTRIBUTE6
842 ,rec_temp_dists.GLOBAL_ATTRIBUTE7
843 ,rec_temp_dists.GLOBAL_ATTRIBUTE8
844 ,rec_temp_dists.GLOBAL_ATTRIBUTE9
845 ,rec_temp_dists.GLOBAL_ATTRIBUTE10
846 ,rec_temp_dists.GLOBAL_ATTRIBUTE11
847 ,rec_temp_dists.GLOBAL_ATTRIBUTE12
848 ,rec_temp_dists.GLOBAL_ATTRIBUTE13
849 ,rec_temp_dists.GLOBAL_ATTRIBUTE14
850 ,rec_temp_dists.GLOBAL_ATTRIBUTE15
851 ,rec_temp_dists.GLOBAL_ATTRIBUTE16
852 ,rec_temp_dists.GLOBAL_ATTRIBUTE17
853 ,rec_temp_dists.GLOBAL_ATTRIBUTE18
854 ,rec_temp_dists.GLOBAL_ATTRIBUTE19
855 ,rec_temp_dists.GLOBAL_ATTRIBUTE20
856 ,l_type_1099
857 ,l_income_tax_region
858 ,rec_temp_dists.org_id
859 ,rec_temp_dists.awt_related_id
860 ,'N'
861 ,'PERMANENT' -- distribution_class bug 8620272
862 );
863
864 -- bug8879522
865 debug_info := 'Adding the dist amount and dist base amount to running total';
866 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
867 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
868 END IF;
869
870 l_sum_dists := l_sum_dists +
871 ap_utilities_pkg.ap_round_currency
872 (l_withhold_amount,
873 p_currency_code);
874 l_sum_dists_base := l_sum_dists_base +
875 ap_utilities_pkg.ap_round_currency
876 (-rec_temp_dists.base_withholding_amount,
877 l_basecur);
878
879 debug_info := ' After processing awt_related_id '||rec_temp_dists.awt_related_id||
880 ' the totals are, '||
881 ' l_sum_dists : '||l_sum_dists||
882 ' l_sum_dists_base : '||l_sum_dists_base;
883 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
884 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
885 END IF;
886
887
888 --Bug 4539462 DBI logging
889 AP_DBI_PKG.Maintain_DBI_Summary
890 ( p_table_name => 'AP_INVOICE_DISTRIBUTIONS',
891 p_operation => 'I',
892 p_key_value1 => P_invoice_id,
893 p_key_value2 => l_Invoice_distribution_id,
894 p_calling_sequence => current_calling_sequence);
895
896
897 withholding_total := withholding_total +
898 ap_utilities_pkg.ap_round_currency(
899 rec_temp_dists.withholding_amount/
900 nvl(l_exchange_rate,1), --bug 8899204
901 p_currency_code); --bug 8590059
902 base_withholding_total := base_withholding_total +
903 rec_temp_dists.base_withholding_amount;
904
905
906 debug_info := 'withholding_total -- '||to_char(withholding_total);
907 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
908 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
909 END IF;
910
911
912 END LOOP For_Each_Temporary_dist;
913
914 debug_info := 'CLOSE CURSOR c_temp_dists';
915 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
916 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
917 END IF;
918 CLOSE c_temp_dists;
919
920 -- bug8879522
921 debug_info := 'Calculating the difference between the dist totals and line amount';
922 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
923 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
924 END IF;
925
926 l_round_amt := l_line_amt - l_sum_dists;
927 l_round_base_amt := l_line_base_amt - l_sum_dists_base;
928
929 debug_info := ' l_round_amt : '||to_char(l_round_amt)||
930 ' l_round_base_amt :'||to_char(l_round_base_amt);
931 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
932 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
933 END IF;
934
935 --bug 9258669
936
937 withholding_total := withholding_total - l_round_amt;
938 base_withholding_total := base_withholding_total - l_round_base_amt;
939
940 debug_info := ' withholding_total : '||to_char(withholding_total)||
941 ' base_withholding_total :'||to_char(base_withholding_total);
942 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
943 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
944 END IF;
945 --bug 9258669
946
947 BEGIN
948
949 SELECT max(aid.invoice_distribution_id)
950 INTO l_dist_id_to_round
951 FROM ap_invoice_distributions_all aid
952 WHERE aid.invoice_id = P_Invoice_Id
953 AND aid.line_type_lookup_code = 'AWT'
954 AND abs(aid.amount) =
955 (SELECT max(abs(aid1.amount))
956 FROM ap_invoice_distributions_all aid1
957 WHERE aid1.invoice_id = P_Invoice_Id
958 AND aid1.invoice_line_number = curr_inv_line_number
959 AND aid1.line_type_lookup_code = 'AWT');
960
961 EXCEPTION
962 WHEN OTHERS THEN
963 NULL;
964 END;
965
966 debug_info := 'Max dist_id to round off is :'||l_dist_id_to_round;
967 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
968 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
969 END IF;
970
971 UPDATE ap_invoice_distributions_all aid
972 SET aid.amount = aid.amount + l_round_amt,
973 aid.base_amount = aid.base_amount + l_round_base_amt
974 WHERE aid.invoice_id = P_Invoice_Id
975 AND aid.invoice_distribution_id = l_dist_id_to_round;
976
977
978 END LOOP For_Each_Temporary_line;
979
980 debug_info := 'CLOSE CURSOR c_temp_lines';
981 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
982 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
983 END IF;
984 CLOSE c_line_cursor;
985
986 -- delete temp withholding lines for thIS invoice
987
988 debug_info := 'Delete From ap_awt_temp_distributions';
989 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
990 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
991 END IF;
992
993 DELETE ap_awt_temp_distributions_all
994 WHERE invoice_id = p_invoice_id
995 AND (P_Payment_Num IS NULL OR payment_num = P_Payment_Num);
996
997
998 /* 10183587 - Start - this code added to exclude the AWT amount of freight dists
999 from discount (if we opted the option exclude the freight from disc) */
1000 BEGIN
1001 SELECT AI.Exclude_Freight_From_Discount
1002 INTO l_exclude_freight_from_disc
1003 FROM AP_Invoices_All AI
1004 WHERE AI.Invoice_ID = P_Invoice_Id;
1005
1006 debug_info := 'l_exclude_freight_from_disc ' || l_exclude_freight_from_disc;
1007 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1008 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
1009 END IF;
1010
1011 IF l_exclude_freight_from_disc = 'Y' THEN
1012
1013 SELECT NVL(SUM(AID_AWT.Amount),0)
1014 INTO l_sub_withhold_amt
1015 FROM AP_Invoice_Distributions_All AID_FRE,
1016 AP_Invoice_Distributions_All AID_AWT
1017 WHERE AID_FRE.Invoice_ID = P_Invoice_Id
1018 AND AID_FRE.Invoice_ID = AID_AWT.Invoice_ID
1019 AND AID_FRE.Line_Type_Lookup_Code = 'FREIGHT'
1020 AND AID_AWT.Line_Type_Lookup_code = 'AWT'
1021 AND AID_FRE.Invoice_Distribution_ID = AID_AWT.Awt_Related_ID;
1022
1023 debug_info := 'l_sub_withhold_amt ' || l_sub_withhold_amt;
1024 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1025 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
1026 END IF;
1027
1028 END IF;
1029 EXCEPTION
1030 WHEN OTHERS THEN
1031 debug_info := 'error occurred : '|| SQLERRM;
1032 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1033 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
1034 END IF;
1035 END;
1036 /* 10183587 - END */
1037
1038
1039
1040 <<Update_Payment_Schedules>>
1041 DECLARE
1042 --Bug7707630:Cursor c_payment_sched: Added decode for BOTH
1043 CURSOR c_payment_sched --bug6660355
1044 (Createdists IN VARCHAR2
1045 ,PaymNum IN NUMBER
1046 ,InvId IN NUMBER
1047 ) IS
1048 SELECT gross_amount
1049 , amount_remaining
1050 , NVL(inv_curr_gross_amount, gross_Amount) inv_curr_gross_amount
1051 FROM ap_payment_schedules_all
1052 WHERE (invoice_id = InvId)
1053 AND (payment_num = decode(Createdists
1054 ,'APPROVAL',payment_num, 'BOTH',
1055 decode(P_Calling_Module,'CONFIRM',PaymNum,'QUICKCHECK',PaymNum,payment_num)
1056 ,PaymNum
1057 ))
1058 FOR UPDATE of amount_remaining;
1059 rec_payment_sched c_payment_sched%ROWTYPE;
1060
1061 DBG_Loc VARCHAR2(30) := 'Update_Payment_Schedules';
1062
1063 BEGIN
1064 debug_info := 'OPEN CURSOR c_payment_sched';
1065 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1066 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
1067 END IF;
1068
1069 OPEN c_payment_sched(P_Create_dists
1070 ,P_Payment_Num
1071 ,P_Invoice_Id);
1072 --Bug7707630: Removed BOTH from the if condition
1073 IF (P_Create_dists in ('APPROVAL')) THEN
1074 -- When withholding at approval time, LOOP on all possible payments
1075 DECLARE
1076 inv_amount_before_withholding NUMBER := rec_invoice.invoice_amount;
1077 amount_to_subtract NUMBER;
1078 pay_curr_amount_to_subtract NUMBER;
1079 subtracting_cumulator NUMBER := 0;
1080 CURSOR c_how_many_payments (InvId IN NUMBER)
1081 IS
1082 SELECT count(*) payments
1083 FROM ap_payment_schedules_all
1084 WHERE invoice_id = InvId;
1085
1086 num_payments NUMBER;
1087 BEGIN
1088 debug_info := 'OPEN CURSOR c_how_many_payments';
1089 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1090 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
1091 END IF;
1092 OPEN c_how_many_payments (P_Invoice_Id);
1093
1094 debug_info := 'Fetch CURSOR c_how_many_payments';
1095 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1096 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
1097 END IF;
1098 FETCH c_how_many_payments INTO num_payments;
1099
1100 debug_info := 'CLOSE CURSOR c_how_many_payments';
1101 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1102 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
1103 END IF;
1104 CLOSE c_how_many_payments;
1105
1106 <<FOR_EACH_PAYMENT>>
1107
1108 FOR j IN 1..num_payments LOOP
1109
1110 debug_info := 'Fetch CURSOR c_payment_sched';
1111 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1112 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
1113 END IF;
1114 FETCH c_payment_sched INTO rec_payment_sched;
1115
1116 IF (inv_amount_before_withholding = 0) THEN
1117 amount_to_subtract := 0;
1118 l_disc_amt_factor := 0;
1119 ELSE
1120 amount_to_subtract := withholding_total *
1121 (rec_payment_sched.inv_curr_gross_amount /
1122 inv_amount_before_withholding
1123 );
1124 amount_to_subtract := Ap_Utilities_Pkg.Ap_Round_Currency
1125 (amount_to_subtract ,P_Currency_Code);
1126
1127 debug_info := 'amount_to_subtract ' || amount_to_subtract;
1128 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1129 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
1130 END IF;
1131
1132 -- BUG 7000143 Old Code.
1133 -- l_disc_amt_factor := withholding_total /
1134 -- NVL(rec_invoice.amount_applicable_to_discount,
1135 -- inv_amount_before_withholding);
1136
1137 -- BUG 7000143 New Code Start
1138 l_disc_amt_divisor := NVL(rec_invoice.amount_applicable_to_discount,
1139 inv_amount_before_withholding);
1140 if l_disc_amt_divisor = 0 then
1141 l_disc_amt_factor := 0;
1142 else
1143 /* 10183587 - exclude the freight withholding amount from total WH amt */
1144 l_disc_amt_factor := (withholding_total + l_sub_withhold_amt)
1145 /l_disc_amt_divisor; -- 10183587 /*bug13431819:Changed the sign of l_sub_withhold_amt to positive*/
1146 end if;
1147 -- BUG 7000143 End
1148 END IF;
1149
1150 debug_info := 'amount_to_subtract ' || amount_to_subtract;
1151 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1152 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
1153 END IF;
1154
1155 debug_info := 'l_disc_amt_divisor ' || l_disc_amt_divisor;
1156 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1157 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
1158 END IF;
1159
1160 debug_info := 'l_disc_amt_factor ' || l_disc_amt_factor;
1161 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1162 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
1163 END IF;
1164
1165 IF (j < num_payments) THEN
1166 subtracting_cumulator := subtracting_cumulator +
1167 amount_to_subtract;
1168 ELSE
1169 -- Get last amount to subtract FROM payments amounts by difference
1170 -- (this is due to rounding reasons):
1171 amount_to_subtract := withholding_total - subtracting_cumulator;
1172 END IF;
1173
1174 pay_curr_amount_to_subtract := ap_utilities_pkg.ap_round_currency(
1175 amount_to_subtract * rec_invoice.payment_cross_rate,
1176 rec_invoice.payment_currency_code);
1177
1178 debug_info := 'pay_curr_amount_to_subtract ' || pay_curr_amount_to_subtract;
1179 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1180 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
1181 END IF;
1182
1183 -- Update current payment schedule:
1184 debug_info := 'Update current payment schedule';
1185 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1186 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
1187 END IF;
1188
1189 UPDATE ap_payment_schedules_all
1190 SET amount_remaining = amount_remaining -
1191 pay_curr_amount_to_subtract,
1192 -- iyas: Following code IS in DLD but was not found originally in file:
1193 discount_amount_available = discount_amount_available -
1194 ap_utilities_pkg.ap_round_currency(
1195 discount_amount_available * l_disc_amt_factor,
1196 rec_invoice.payment_currency_code),
1197 second_disc_amt_available = second_disc_amt_available -
1198 ap_utilities_pkg.ap_round_currency(
1199 second_disc_amt_available * l_disc_amt_factor,
1200 rec_invoice.payment_currency_code) ,
1201 third_disc_amt_available = third_disc_amt_available -
1202 ap_utilities_pkg.ap_round_currency(
1203 third_disc_amt_available * l_disc_amt_factor,
1204 rec_invoice.payment_currency_code)
1205 WHERE CURRENT of c_payment_sched;
1206
1207 END LOOP For_Each_Payment;
1208 END;
1209 ELSIF (P_Calling_Module <> 'AWT REPORT') THEN
1210 -- otherwise subtract total withholding FROM current payment
1211 debug_info := 'Fetch CURSOR c_payment_sched';
1212 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1213 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
1214 END IF;
1215
1216
1217 FETCH c_payment_sched INTO rec_payment_sched;
1218 debug_info := 'Update current payment schedule';
1219 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1220 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
1221 END IF;
1222
1223 -- The withholding_total should be converted to payment
1224 -- currency before substracting it FROM the amount remaining.
1225
1226 UPDATE ap_payment_schedules_all
1227 SET amount_remaining = (amount_remaining -
1228 ap_utilities_pkg.ap_round_currency(
1229 withholding_total * rec_invoice.payment_cross_rate,
1230 rec_invoice.payment_currency_code))
1231 WHERE current of c_payment_sched;
1232
1233 END IF; -- whether withholding at approval time or not
1234
1235 debug_info := 'CLOSE CURSOR c_payment_sched';
1236 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1237 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
1238 END IF;
1239
1240 CLOSE c_payment_sched;
1241 END Update_Payment_Schedules;
1242
1243 <<UPDATE_INVOICE>>
1244 debug_info := 'Update ap_invoices';
1245 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1246 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
1247 END IF;
1248
1249 UPDATE ap_invoices_all
1250 SET awt_flag = DECODE(P_Create_dists, 'APPROVAL', 'Y','BOTH','Y', NULL), --Bug6660355
1251 amount_applicable_to_discount = decode (sign(invoice_amount),
1252 -1, amount_applicable_to_discount,
1253 amount_applicable_to_discount
1254 - withholding_total - l_sub_withhold_amt) -- 10183587
1255 WHERE CURRENT OF c_invoice;
1256
1257 debug_info := 'CLOSE CURSOR c_invoice';
1258 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1259 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
1260 END IF;
1261
1262 CLOSE c_invoice;
1263
1264 EXCEPTION
1265 WHEN OTHERS THEN
1266 IF (SQLCODE <> -20001) THEN
1267 FND_MESSAGE.set_NAME('SQLAP','AP_DEBUG');
1268 FND_MESSAGE.set_TOKEN('ERROR',SQLERRM);
1269 FND_MESSAGE.set_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
1270 FND_MESSAGE.set_TOKEN('PARAMETERS',
1271 ' Invoice Id = ' || to_char(P_Invoice_Id) ||
1272 ', Calling module = ' || P_Calling_Module ||
1273 ', Create dists = ' || P_Create_dists ||
1274 ', Payment Num = ' || to_char(P_Payment_Num) ||
1275 ', Currency code = ' || P_Currency_Code);
1276
1277 FND_MESSAGE.set_TOKEN('DEBUG_INFO',debug_info);
1278 END IF;
1279 APP_EXCEPTION.RAISE_EXCEPTION;
1280
1281 END Create_AWT_distributions;
1282
1283 PROCEDURE Create_AWT_Invoices(
1284 P_Invoice_Id IN NUMBER,
1285 P_Payment_Date IN DATE,
1286 P_Last_Updated_By IN NUMBER,
1287 P_Last_Update_Login IN NUMBER,
1288 P_Program_Application_Id IN NUMBER,
1289 P_Program_Id IN NUMBER,
1290 P_Request_Id IN NUMBER,
1291 P_Calling_Sequence IN VARCHAR2,
1292 P_Calling_Module IN VARCHAR2 DEFAULT NULL, --Bug6660355 -- bug 8266021
1293 P_Inv_Line_No IN NUMBER DEFAULT NULL,
1294 P_Dist_Line_No IN NUMBER DEFAULT NULL,
1295 P_New_Invoice_Id IN NUMBER DEFAULT NULL,
1296 P_create_dists IN VARCHAR2 DEFAULT NULL) --Bug7685907 bug8207324 bug8236169
1297 IS
1298 new_invoice_id ap_invoices.invoice_id%TYPE;
1299 tax_authority_id ap_tax_codes.awt_vendor_id%TYPE;
1300 tax_authority_site_id ap_tax_codes.awt_vendor_site_id%TYPE;
1301 base_currency ap_system_parameters.base_currency_code%TYPE;
1302 new_invoice_base_descr ap_invoices.description%TYPE;
1303 inv_terms_date DATE;
1304 ta_terms_id po_vendor_sites.terms_id%TYPE;
1305 ta_payment_priority po_vendor_sites.payment_priority%TYPE;
1306 ta_terms_date_basIS po_vendor_sites.terms_date_basIS%TYPE;
1307 ta_pay_group_lookup_code po_vendor_sites.pay_group_lookup_code%TYPE;
1308 ta_accts_pay_code_comb_id po_vendor_sites.accts_pay_code_combination_id%TYPE;
1309 ta_payment_currency_code po_vendor_sites.payment_currency_code%TYPE;
1310 c_payment_cross_rate ap_invoices.payment_cross_rate%TYPE;
1311 c_payment_cross_rate_type ap_invoices.payment_cross_rate_type%TYPE;
1312 l_invoice_distribution_id ap_invoice_distributions.invoice_distribution_id%TYPE;
1313 l_legal_entity_id ap_invoices_all.legal_entity_id%type;
1314
1315 l_set_of_books_id ap_invoices_all.set_of_books_id%type;
1316 l_batch_id ap_invoices_all.batch_id%type;
1317 l_org_id ap_invoices_all.org_id%type;
1318 l_period_name gl_period_statuses.period_name%type;
1319
1320 l_line_number ap_invoice_lines_all.line_number%type;
1321 l_dist_number ap_invoice_distributions_all.distribution_line_number%type;
1322
1323
1324
1325 l_IBY_PAYMENT_METHOD varchar2(80);
1326 l_PAYMENT_REASON varchar2(80);
1327 l_BANK_CHARGE_BEARER_DSP varchar2(80);
1328 l_DELIVERY_CHANNEL varchar2(80);
1329 l_SETTLEMENT_PRIORITY_DSP varchar2(80);
1330 l_bank_account_num varchar2(100);
1331 l_bank_account_name varchar2(80);
1332 l_bank_branch_name varchar2(360);
1333 l_bank_branch_num varchar2(30);
1334 l_bank_name varchar2(360);
1335 l_bank_number varchar2(30);
1336 l_PAYMENT_METHOD_CODE varchar2(30);
1337 l_PAYMENT_REASON_CODE varchar2(30);
1338 l_BANK_CHARGE_BEARER varchar2(30);
1339 l_DELIVERY_CHANNEL_CODE varchar2(30);
1340 l_SETTLEMENT_PRIORITY varchar2(30);
1341 l_PAY_ALONE varchar2(30);
1342 l_external_bank_account_id number;
1343 l_exclusive_payment_flag varchar2(1);
1344 l_party_id number;
1345 l_party_site_id number;
1346 l_payment_reason_comments varchar2(240); --4874927
1347
1348 --bug 7699166
1349 l_remit_party_id NUMBER;
1350 l_relationship_id NUMBER;
1351 l_invoice_date DATE;
1352 l_remit_to_supplier_name AP_SUPPLIERS.VENDOR_NAME%TYPE;
1353 l_remit_to_supplier_id AP_SUPPLIERS.VENDOR_ID%TYPE;
1354 l_remit_to_supplier_site AP_SUPPLIER_SITES.VENDOR_SITE_CODE%TYPE;
1355 l_remit_to_supplier_site_id AP_SUPPLIER_SITES.VENDOR_SITE_ID%TYPE;
1356 l_remit_to_party_site_id AP_SUPPLIER_SITES.PARTY_SITE_ID%TYPE; --7721149
1357 --bug 7699166
1358
1359 --
1360 --8266021 changed cursor --
1361 CURSOR c_awt_lines (InvId IN NUMBER,line_num in number) IS
1362 SELECT APID.accounting_date accounting_date
1363 , APID.invoice_line_number invoice_line_number
1364 , APID.distribution_line_number distribution_line_number
1365 , APID.set_of_books_id set_of_books_id
1366 , APID.dist_code_combination_id dist_code_combination_id
1367 , APID.period_name period_name
1368 , APID.withholding_tax_code_id tax_code_id /* Bug 5382525 */
1369 , APID.amount amount
1370 , APID.base_amount base_amount
1371 , APID.batch_id batch_id
1372 --, APID.ussgl_transaction_code ussgl_transaction_code - Bug 4277744
1373 --, APID.ussgl_trx_code_context ussgl_trx_code_context - Bug 4277744
1374 , APID.org_id
1375 , APID.awt_related_id awt_related_id -- bug 9913164
1376 FROM ap_invoice_distributions_all APID,
1377 ap_invoice_distributions_all APID1,
1378 ap_tax_codes_all ATC,
1379 ap_invoices_all AI
1380 WHERE (APID.invoice_id = InvId)
1381 AND (APID.invoice_line_number = NVL(P_Inv_Line_No,line_num))
1382 AND (APID.distribution_line_number = NVL(P_dist_Line_No,APID.distribution_line_number))
1383 AND (APID.line_type_lookup_code = 'AWT')
1384 AND APID.invoice_id = APID1.invoice_id
1385 AND APID.awt_related_id = APID1.invoice_distribution_id
1386 AND ((APID.awt_invoice_id IS NULL)
1387 OR (APID.awt_invoice_id = P_New_Invoice_Id))
1388 AND (NVL(APID.awt_flag , 'M' ) = 'A' )
1389 AND APID.invoice_id = AI.invoice_id
1390 AND APID.WITHHOLDING_TAX_CODE_ID = ATC.tax_id /* Bug 5382525 */
1391 AND APID.base_amount <> decode (NVL(ATC.suppress_zero_amount_flag,
1392 'N'), 'Y', 0 , APID.base_amount +1) /*Bug 14491356*/
1393 AND NVL(APID.reversal_flag, 'N') <> 'Y'
1394 AND
1395 (
1396 APID.AWT_ORIGIN_GROUP_ID = nvl(DECODE(P_calling_module,'AUTOAPPROVAL',APID1.awt_group_id,
1397 'CANCEL INVOICE',APID1.awt_group_id,'REVERSE DIST',APID1.awt_group_id,
1398 'CONFIRM',DECODE(P_create_dists,'APPROVAL',
1399 APID1.awt_group_id, APID1.pay_awt_group_id),
1400 'QUICKCHECK', DECODE(P_create_dists,'APPROVAL',
1401 APID1.awt_group_id,APID1.pay_awt_group_id), APID1.pay_awt_group_id),-1) --6660355 --9093973
1402 --Bug 7685907 Added Decode for Confirm and Quickcheck
1403 or
1404 APID.AWT_ORIGIN_GROUP_ID = - 1 --bug13999969
1405 )
1406 FOR UPDATE of APID.awt_invoice_id;
1407
1408
1409
1410 --8266021 added new cursor
1411 CURSOR c_awt_lines_rev (InvId IN NUMBER,line_num in number) IS
1412 SELECT APID.accounting_date accounting_date
1413 , APID.invoice_line_number invoice_line_number
1414 , APID.distribution_line_number distribution_line_number
1415 , APID.set_of_books_id set_of_books_id
1416 , APID.dist_code_combination_id dist_code_combination_id
1417 , APID.period_name period_name
1418 , APID.withholding_tax_code_id tax_code_id /* Bug 5382525 */
1419 , APID.amount amount
1420 , APID.base_amount base_amount
1421 , APID.batch_id batch_id
1422 --, APID.ussgl_transaction_code ussgl_transaction_code - Bug 4277744
1423 --, APID.ussgl_trx_code_context ussgl_trx_code_context - Bug 4277744
1424 , APID.org_id
1425 , APID.awt_related_id awt_related_id -- bug 9913164
1426 FROM ap_invoice_distributions_all APID,
1427 ap_invoice_distributions_all APID1,
1428 ap_tax_codes_all ATC,
1429 ap_invoices_all AI
1430 WHERE (APID.invoice_id = InvId)
1431 AND (APID.invoice_line_number = NVL(P_Inv_Line_No,line_num))
1432 AND (APID.distribution_line_number = NVL(P_dist_Line_No,APID.distribution_line_number))
1433 AND (APID.line_type_lookup_code = 'AWT')
1434 AND APID.invoice_id = APID1.invoice_id
1435 AND APID.awt_related_id = APID1.invoice_distribution_id
1436 AND ((APID.awt_invoice_id IS NULL)
1437 OR (APID.awt_invoice_id = P_New_Invoice_Id)
1438 )
1439 AND (NVL(APID.awt_flag , 'M' ) = 'A' )
1440 AND APID.invoice_id = AI.invoice_id
1441 AND APID.WITHHOLDING_TAX_CODE_ID = ATC.tax_id /* Bug 5382525 */
1442 AND APID.base_amount <> decode (NVL(ATC.suppress_zero_amount_flag,
1443 'N'), 'Y', 0 , APID.base_amount +1) /*Bug 14491356*/
1444 AND nvl(APID.parent_reversal_id,-99) <> -99
1445 AND NVL(APID.reversal_flag, 'N') = 'Y'
1446 AND
1447 (
1448 APID.AWT_ORIGIN_GROUP_ID = nvl(DECODE(P_calling_module,'AUTOAPPROVAL',APID1.awt_group_id,
1449 'CANCEL INVOICE',APID1.awt_group_id,'REVERSE DIST',APID1.awt_group_id,
1450 'CONFIRM',DECODE(P_create_dists,'APPROVAL',
1451 APID1.awt_group_id, APID1.pay_awt_group_id),
1452 'QUICKCHECK', DECODE(P_create_dists,'APPROVAL',
1453 APID1.awt_group_id,APID1.pay_awt_group_id), APID1.pay_awt_group_id),-1) --6660355 --9093973
1454 --Bug 7685907 Added Decode for Confirm and Quickcheck
1455 or
1456 APID.AWT_ORIGIN_GROUP_ID = - 1 --bug13999969
1457 )
1458 FOR UPDATE of APID.awt_invoice_id;
1459
1460 rec_awt_lines c_awt_lines%ROWTYPE;
1461
1462 -- bug8266021 added 2 new cursors
1463 CURSOR c_awt_invs (InvId IN NUMBER) IS
1464 SELECT min(APID.accounting_date) accounting_date
1465 , APID.withholding_tax_code_id tax_code_id
1466 , sum(-1 * NVL(APID.base_amount,APID.amount)) invoice_amount --bug 8597105
1467 , APID.invoice_line_number
1468 FROM ap_invoice_distributions_all APID,
1469 ap_invoice_distributions_all APID1,
1470 ap_tax_codes_all ATC,
1471 AP_INVOICES_ALL AI
1472 WHERE (APID.invoice_id = InvId)
1473 AND (APID.line_type_lookup_code = 'AWT')
1474 AND (NVL(APID.awt_flag , 'M' ) = 'A' )
1475 AND APID.WITHHOLDING_TAX_CODE_ID = ATC.tax_id
1476 AND APID.invoice_id = APID1.invoice_id
1477 --AND (APID.invoice_line_number = NVL(P_Inv_Line_No,APID.invoice_line_number))
1478 AND APID.awt_related_id = APID1.invoice_distribution_id
1479 AND APID.base_amount <> decode (NVL(ATC.suppress_zero_amount_flag,
1480 'N'), 'Y', 0 , APID.base_amount +1) /*Bug 14491356*/
1481 AND NVL(APID.reversal_flag, 'N') <> 'Y'
1482 AND APID.invoice_id = AI.invoice_id
1483 AND ((APID.awt_invoice_id IS NULL)
1484 OR (APID.awt_invoice_id = P_New_Invoice_Id)
1485 ) --bug 8659829
1486 AND
1487 (
1488 APID.AWT_ORIGIN_GROUP_ID = nvl(DECODE(P_calling_module,'AUTOAPPROVAL',APID1.awt_group_id,
1489 'CANCEL INVOICE',APID1.awt_group_id,'REVERSE DIST',APID1.awt_group_id,
1490 'CONFIRM',DECODE(P_create_dists,'APPROVAL',
1491 APID1.awt_group_id, APID1.pay_awt_group_id),
1492 'QUICKCHECK', DECODE(P_create_dists,'APPROVAL',
1493 APID1.awt_group_id,APID1.pay_awt_group_id), APID1.pay_awt_group_id),-1) --9093973
1494 or
1495 APID.AWT_ORIGIN_GROUP_ID = - 1 --bug13999969
1496 )
1497 GROUP By APID.withholding_tax_code_id
1498 ,APID.invoice_line_number;
1499
1500 CURSOR c_awt_invs_rev (InvId IN NUMBER) IS
1501 SELECT min(APID.accounting_date) accounting_date
1502 , APID.withholding_tax_code_id tax_code_id
1503 , sum(-1 * NVL(APID.base_amount,APID.amount)) invoice_amount --bug 8597105
1504 , APID.invoice_line_number
1505 FROM ap_invoice_distributions_all APID,
1506 ap_invoice_distributions_all APID1,
1507 ap_tax_codes_all ATC,
1508 AP_INVOICES_ALL AI
1509 WHERE (APID.invoice_id = InvId)
1510 AND (APID.line_type_lookup_code = 'AWT')
1511 AND (NVL(APID.awt_flag , 'M' ) = 'A' )
1512 AND APID.WITHHOLDING_TAX_CODE_ID = ATC.tax_id
1513 AND APID.invoice_id = APID1.invoice_id
1514 --AND (APID.invoice_line_number = NVL(P_Inv_Line_No,APID.invoice_line_number))
1515 AND APID.awt_related_id = APID1.invoice_distribution_id
1516 AND APID.base_amount <> decode (NVL(ATC.suppress_zero_amount_flag,
1517 'N'), 'Y', 0 , APID.base_amount +1) /*Bug 14491356*/
1518 AND NVL(APID.reversal_flag, 'N') = 'Y'
1519 AND nvl(APID.parent_reversal_id,-99) <> -99
1520 AND APID.invoice_id = AI.invoice_id
1521 AND ((APID.awt_invoice_id IS NULL)
1522 OR (APID.awt_invoice_id = P_New_Invoice_Id)
1523 ) --bug 8659829
1524 AND
1525 (
1526 APID.AWT_ORIGIN_GROUP_ID = nvl(DECODE(P_calling_module,'AUTOAPPROVAL',APID1.awt_group_id,
1527 'CANCEL INVOICE',APID1.awt_group_id,'REVERSE DIST',APID1.awt_group_id,
1528 'CONFIRM',DECODE(P_create_dists,'APPROVAL',
1529 APID1.awt_group_id, APID1.pay_awt_group_id),
1530 'QUICKCHECK', DECODE(P_create_dists,'APPROVAL',
1531 APID1.awt_group_id,APID1.pay_awt_group_id), APID1.pay_awt_group_id),-1) --9093973
1532 or
1533 APID.AWT_ORIGIN_GROUP_ID = - 1 --bug13999969
1534 )
1535 GROUP By APID.withholding_tax_code_id
1536 ,APID.invoice_line_number;
1537
1538 rec_awt_invs c_awt_invs%ROWTYPE;
1539
1540 --bug 8266021 added last 3 more values --
1541 CURSOR c_base_invoice_description (InvId IN NUMBER) IS
1542 SELECT substrb(
1543 substrb(Ap_Utilities_Pkg.Ap_Get_DISplayed_Field('NLS TRANSLATION' , 'AWT'),1,25)||
1544 ' - '||
1545 v.vendor_name||
1546 ' - '||
1547 i.invoice_num||
1548 ' /' --4940604
1549 ,1
1550 , 234
1551 ) description,
1552 i.legal_entity_id,
1553 i.set_of_books_id,
1554 i.batch_id,
1555 i.org_id
1556 FROM po_vendors v
1557 , ap_invoices_all i
1558 WHERE (v.vendor_id = i.vendor_id)
1559 AND (i.invoice_id = InvId);
1560
1561 DBG_Loc VARCHAR2(30) := 'Create_AWT_Invoices';
1562 current_calling_sequence VARCHAR2(2000);
1563 debug_info VARCHAR2(100);
1564 goods_received_date DATE;
1565 invoice_received_date DATE;
1566 l_dist_code_ccid NUMBER; -- bug9913164
1567 l_overlayed_awt_ccid NUMBER; -- bug9913164
1568 l_reason VARCHAR2(100); -- bug9913164
1569 l_automatic_offsets VARCHAR2(1); -- bug9913164
1570
1571 BEGIN
1572
1573 current_calling_sequence := 'AP_WITHHOLDING_PKG.Create_AWT_Invoices<-' ||
1574 P_Calling_Sequence;
1575
1576 -- Get base invoice description to insert in every new generated invoice
1577
1578 debug_info := 'OPEN CURSOR c_base_invoice_description';
1579 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1580 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
1581 END IF;
1582
1583 OPEN c_base_invoice_description (P_Invoice_Id);
1584
1585 debug_info := 'Fetch CURSOR c_base_invoice_description';
1586 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1587 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
1588 END IF;
1589 --bug 8266021 added 3 more variables
1590 FETCH c_base_invoice_description
1591 INTO new_invoice_base_descr,
1592 l_legal_entity_id,
1593 l_set_of_books_id,
1594 l_batch_id,
1595 l_org_id;
1596
1597 debug_info := 'CLOSE CURSOR c_base_invoice_description';
1598 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1599 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
1600 END IF;
1601
1602 CLOSE c_base_invoice_description;
1603
1604
1605 --Bug 8266021 inv cursor called based on calling module
1606 --This cursor will fetch info required to insert into
1607 --ap_invoices_all and ap_invoice_lines_all
1608 debug_info := 'OPEN CURSOR c_awt_invs';
1609
1610 IF (P_Calling_Module in ('CANCEL INVOICE','REVERSE DIST','VOID PAYMENT')) THEN
1611 OPEN c_awt_invs_rev (P_Invoice_Id);
1612 ELSE
1613 OPEN c_awt_invs (P_Invoice_Id);
1614 END IF;
1615
1616
1617
1618 <<FOR_EACH_NEGATIVE_LINE>>
1619 LOOP
1620 debug_info := 'Fetch CURSOR for invoices (c_awt_invs_rev or c_awt_invs )';
1621 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1622 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
1623 END IF;
1624
1625 debug_info := 'P_Calling_Module '|| P_Calling_Module;
1626 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1627 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
1628 END IF;
1629
1630
1631
1632 IF (P_Calling_Module in ('CANCEL INVOICE','REVERSE DIST','VOID PAYMENT')) THEN
1633 FETCH c_awt_invs_rev INTO rec_awt_invs;
1634 EXIT WHEN c_awt_invs_rev%NOTFOUND;
1635
1636 debug_info := 'c_awt_invs_rev rows chosen'||c_awt_invs_rev%ROWCOUNT;
1637 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1638 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
1639 END IF;
1640 ELSE
1641 FETCH c_awt_invs INTO rec_awt_invs;
1642 EXIT WHEN c_awt_invs%NOTFOUND;
1643
1644 debug_info := 'c_awt_invs rows chosen'||c_awt_invs%ROWCOUNT;
1645 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1646 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
1647 END IF;
1648 END IF;
1649
1650 -- Start: Create invoice payable to Tax Authority for thIS negative line:
1651 -- First get tax authority site AND new invoice_id from sequence:
1652
1653 <<TAX_AUTHORITY_INFO>>
1654 DECLARE
1655 CURSOR c_tax_authority (TaxId IN NUMBER)
1656 IS
1657 SELECT t.awt_vendor_id,
1658 t.awt_vendor_site_id,
1659 NVL(s.payment_currency_code, s.invoice_currency_code),
1660 NVL(P_New_Invoice_Id, ap_invoices_s.nextval),
1661 p.base_currency_code,
1662 s.terms_id,
1663 s.payment_priority,
1664 s.terms_date_basis,
1665 s.pay_group_lookup_code,
1666 s.accts_pay_code_combination_id,
1667 s.party_site_id,
1668 pv.party_id
1669 FROM ap_tax_codes_all t,
1670 ap_system_parameters_all p,
1671 po_vendor_sites_all s,
1672 po_vendors pv
1673 WHERE t.tax_id = TaxId
1674 AND pv.vendor_id = s.vendor_id /* Bug 4724120 */
1675 AND s.vendor_id = t.awt_vendor_id
1676 AND s.vendor_site_id = t.awt_vendor_site_id
1677 AND p.org_id = t.org_id;
1678 BEGIN
1679 debug_info := 'OPEN CURSOR c_tax_authority';
1680 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1681 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
1682 END IF;
1683
1684 --bug 8266021 changed cursor parameter
1685 OPEN c_tax_authority(rec_awt_invs.tax_code_id);
1686
1687 debug_info := 'Fetch CURSOR c_tax_authority';
1688 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1689 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
1690 END IF;
1691
1692 FETCH c_tax_authority
1693 INTO tax_authority_id,
1694 tax_authority_site_id,
1695 ta_payment_currency_code,
1696 new_invoice_id,
1697 base_currency,
1698 ta_terms_id,
1699 ta_payment_priority,
1700 ta_terms_date_basis,
1701 ta_pay_group_lookup_code,
1702 ta_accts_pay_code_comb_id,
1703 l_party_site_id,
1704 l_party_id;
1705
1706 IF c_tax_authority%NOTFOUND THEN
1707 NULL;
1708 END IF;
1709
1710 debug_info := 'CLOSE CURSOR c_tax_authority';
1711 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1712 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
1713 END IF;
1714
1715 CLOSE c_tax_authority;
1716 END Tax_Authority_Info;
1717
1718
1719 /**
1720 bug 7699166 -- The following call is made to set the remittance details
1721 related to Third Party Payments
1722 */
1723 --bug 8266021 changed cursor parameter
1724 l_invoice_date := NVL(P_Payment_Date,rec_awt_invs.accounting_date);
1725
1726 IBY_EXT_PAYEE_RELSHIPS_PKG.default_Ext_Payee_Relationship(
1727 p_party_id => l_party_id,
1728 p_supplier_site_id => tax_authority_site_id,
1729 p_date => l_invoice_date,
1730 x_remit_party_id => l_remit_party_id,
1731 x_remit_supplier_site_id => l_remit_to_supplier_site_id,
1732 x_relationship_id => l_relationship_id
1733 );
1734
1735 -- Added if else condition as part of bug 8345877
1736 IF (l_relationship_id <> -1) THEN
1737 select vendor_id, vendor_name into l_remit_to_supplier_id, l_remit_to_supplier_name
1738 from ap_suppliers where party_id = l_remit_party_id and rownum<2;
1739
1740 select party_site_id, vendor_site_code into l_remit_to_party_site_id,
1741 l_remit_to_supplier_site from ap_supplier_sites where vendor_site_id = l_remit_to_supplier_site_id
1742 and rownum<2;
1743 ELSE
1744 l_remit_party_id := null;
1745 l_remit_to_party_site_id := null;
1746 l_remit_to_supplier_id := null;
1747 l_remit_to_supplier_name := null;
1748 l_remit_to_supplier_site_id := null;
1749 l_remit_to_supplier_site := null;
1750 END IF;
1751 -- retrieving party_site_id also as part of bug 7721149
1752
1753 --bug 7699166
1754
1755
1756 --4610924, added this call to get payment attributes
1757 -- Added nvl conditions for p_payee_party_id, p_payee_party_site_id, p_supplier_site_id
1758 -- as part of bug 8345877
1759 ap_invoices_pkg.get_payment_attributes(
1760 p_le_id =>l_legal_entity_id,
1761 p_org_id =>l_org_id, --bug 10072576
1762 p_payee_party_id => nvl(l_remit_party_id, l_party_id), --bug 7721149, replacing l_party_id for Third Party Payments
1763 p_payee_party_site_id => nvl(l_remit_to_party_site_id, l_party_site_id), --bug 7721149, replacing l_party_site_id for Third Party Payments
1764 p_supplier_site_id => nvl(l_remit_to_supplier_site_id, tax_authority_site_id), -- bug 7721149 replacing tax_authority_site_id
1765 p_payment_currency =>ta_payment_currency_code,
1766 p_payment_amount =>rec_awt_invs.invoice_amount, --bug 8266021
1767 p_payment_function =>'PAYABLES_DISB',
1768 p_pay_proc_trxn_type_code =>'PAYABLES_DOC',
1769
1770 p_PAYMENT_METHOD_CODE => l_payment_method_code,
1771 p_PAYMENT_REASON_CODE => l_payment_reason_code,
1772 p_BANK_CHARGE_BEARER => l_bank_charge_bearer,
1773 p_DELIVERY_CHANNEL_CODE => l_delivery_channel_code,
1774 p_SETTLEMENT_PRIORITY => l_settlement_priority,
1775 p_PAY_ALONE => l_exclusive_payment_flag,
1776 p_external_bank_account_id => l_external_bank_account_id,
1777
1778 p_IBY_PAYMENT_METHOD => l_IBY_PAYMENT_METHOD,
1779 p_PAYMENT_REASON => l_PAYMENT_REASON,
1780 p_BANK_CHARGE_BEARER_DSP => l_BANK_CHARGE_BEARER_DSP,
1781 p_DELIVERY_CHANNEL => l_DELIVERY_CHANNEL,
1782 p_SETTLEMENT_PRIORITY_DSP => l_SETTLEMENT_PRIORITY_DSP,
1783 p_bank_account_num => l_bank_account_num,
1784 p_bank_account_name => l_bank_account_name,
1785 p_bank_branch_name => l_bank_branch_name,
1786 p_bank_branch_num => l_bank_branch_num,
1787 p_bank_name => l_bank_name,
1788 p_bank_number => l_bank_number,
1789 p_payment_reason_comments => l_payment_reason_comments); --4874927
1790
1791
1792 debug_info := 'Get Exchange Rate'||'pc: '||ta_payment_currency_code||
1793 ' bc: '||base_currency||' date: '||
1794 to_char(rec_awt_lines.accounting_date, 'DD-MON-YYYY');
1795 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1796 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
1797 END IF;
1798
1799 --bug 8266021 changed the parameters from rec_awt_lines to rec_awt_invs
1800 IF ( gl_currency_api.is_fixed_rate(ta_payment_currency_code,
1801 base_currency,
1802 rec_awt_invs.accounting_date) = 'Y' AND
1803 ta_payment_currency_code <> base_currency ) THEN
1804
1805 c_payment_cross_rate := gl_currency_api.get_rate(base_currency,
1806 ta_payment_currency_code,
1807 rec_awt_invs.accounting_date,
1808 'EMU FIXED');
1809 c_payment_cross_rate_type := 'EMU FIXED';
1810 ELSE
1811 c_payment_cross_rate := 1;
1812 ta_payment_currency_code := base_currency;
1813 c_payment_cross_rate_type := '';
1814 END IF;
1815
1816 IF ta_terms_date_basis IN ('Goods Received', 'Invoice Received') THEN
1817 SELECT invoice_received_date,
1818 goods_received_date
1819 INTO invoice_received_date,
1820 goods_received_date
1821 FROM ap_invoices_all
1822 WHERE invoice_id = P_Invoice_Id;
1823 END IF;
1824
1825 --added for bug 8266021 to fetch period
1826 --added automatic_offsets_flag for bug 9913164
1827 SELECT DISTINCT gps.Period_Name,
1828 automatic_offsets_flag
1829 INTO l_period_name,
1830 l_automatic_offsets
1831 FROM gl_Period_Statuses gps,
1832 ap_System_Parameters_All Asp
1833 WHERE gps.Application_Id = 200
1834 AND gps.Set_Of_Books_Id = Asp.Set_Of_Books_Id
1835 AND Nvl(gps.Adjustment_Period_Flag,'N') = 'N'
1836 AND rec_awt_invs.accounting_date BETWEEN Trunc(gps.Start_Date)
1837 AND Trunc(gps.End_Date)
1838 AND Nvl(Asp.Org_Id,- 99) = Nvl(l_org_id,- 99);
1839 -- AND gps.closing_Status in ('O', 'F');
1840 --bug 9304565 commented the above condition as part of this bug.
1841
1842 debug_info := 'Insert Into ap_invoices';
1843 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1844 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
1845 END IF;
1846
1847 INSERT INTO ap_invoices_all
1848 (invoice_id
1849 ,last_UPDATE_date
1850 ,last_UPDATEd_by
1851 ,vendor_id
1852 ,invoice_num
1853 ,set_of_books_id
1854 ,invoice_currency_code
1855 ,payment_currency_code
1856 ,payment_cross_rate
1857 ,invoice_amount
1858 ,pay_curr_invoice_amount
1859 ,payment_cross_rate_type
1860 ,payment_cross_rate_date
1861 ,vendor_site_id
1862 ,amount_paid
1863 ,discount_amount_taken
1864 ,invoice_date
1865 ,source
1866 ,invoice_type_lookup_code
1867 ,description
1868 ,batch_id
1869 ,amount_applicable_to_discount
1870 ,terms_id
1871 ,terms_date
1872 ,pay_group_lookup_code
1873 ,accts_pay_code_combination_id
1874 ,payment_status_flag
1875 ,creation_date
1876 ,created_by
1877 ,last_UPDATE_login
1878 ,doc_sequence_id
1879 ,doc_sequence_value
1880 ,doc_category_code
1881 ,posting_status
1882 -- ,ussgl_transaction_code - Bug 4277744
1883 -- ,ussgl_trx_code_context - Bug 4277744
1884 ,payment_amount_total
1885 ,gl_date
1886 ,approval_ready_flag
1887 ,wfapproval_status
1888 ,org_id
1889 ,legal_entity_id
1890 ,auto_tax_calc_flag -- BUG 3007085
1891 ,PAYMENT_METHOD_CODE
1892 ,PAYMENT_REASON_CODE
1893 ,BANK_CHARGE_BEARER
1894 ,DELIVERY_CHANNEL_CODE
1895 ,SETTLEMENT_PRIORITY
1896 ,exclusive_payment_flag
1897 ,external_bank_account_id
1898 ,party_id
1899 ,party_site_id
1900 ,payment_reason_comments
1901 --bug 7699166 changes for Third Party Payments
1902 ,remit_to_supplier_name
1903 ,remit_to_supplier_id
1904 ,remit_to_supplier_site
1905 ,remit_to_supplier_site_id
1906 ,relationship_id
1907 --bug 7699166
1908 )
1909 VALUES
1910 (new_invoice_id
1911 ,SYSDATE
1912 ,5
1913 ,tax_authority_id
1914 ,DECODE( p_calling_sequence, 'AP_WITHHOLDING_PKG.AP_Undo_Withholding',
1915 substrb(Ap_Utilities_Pkg.Ap_Get_DISplayed_Field('NLS TRANSLATION', 'AWT'),1,25)
1916 ||' - '||to_char(P_invoice_id)||' - ' || to_char(rec_awt_invs.invoice_line_number)
1917 || ' - ' || Ap_Utilities_Pkg.Ap_Get_DISplayed_Field('NLS TRANSLATION','CANCELLED'),
1918 substrb(Ap_Utilities_Pkg.Ap_Get_DISplayed_Field('NLS TRANSLATION', 'AWT'),1,25)||
1919 ' - '||to_char(P_invoice_id)||' - ' || to_char(rec_awt_invs.invoice_line_number)
1920 )
1921 ,l_set_of_books_id
1922 ,base_currency
1923 ,ta_payment_currency_code
1924 ,c_payment_cross_rate
1925 ,rec_awt_invs.invoice_amount
1926 ,gl_currency_api.convert_amount(
1927 base_currency,
1928 ta_payment_currency_code,
1929 rec_awt_invs.accounting_date,
1930 c_payment_cross_rate_type,
1931 rec_awt_invs.invoice_amount)
1932 ,c_payment_cross_rate_type
1933 ,rec_awt_invs.accounting_date
1934 ,tax_authority_site_id
1935 ,0
1936 ,0
1937 ,NVL(P_Payment_Date,rec_awt_invs.accounting_date)
1938 ,substrb(Ap_Utilities_Pkg.Ap_Get_DISplayed_Field('NLS TRANSLATION', 'AWT'),1,25)
1939 ,'AWT'
1940 ,new_invoice_base_descr
1941 ,l_batch_id
1942 ,decode(sign(rec_awt_invs.invoice_amount),
1943 -1, 0, rec_awt_invs.invoice_amount)
1944 ,ta_terms_id
1945 ,decode(ta_terms_date_basIS
1946 ,'Current', SYSDATE
1947 ,'Invoice', NVL(p_payment_date,
1948 rec_awt_invs.accounting_date)
1949 ,'Goods Received', NVL(goods_received_date,
1950 rec_awt_invs.accounting_date)
1951 ,'Invoice Received', NVL(invoice_received_date,
1952 rec_awt_invs.accounting_date)
1953 ,NULL)
1954 ,ta_pay_group_lookup_code
1955 ,ta_accts_pay_code_comb_id
1956 ,'N'
1957 ,SYSDATE
1958 ,5
1959 ,P_Last_Update_Login
1960 ,NULL
1961 ,NULL
1962 ,NULL
1963 ,'N'
1964 -- ,rec_awt_lines.ussgl_transaction_code - Bug 4277744
1965 -- ,rec_awt_lines.ussgl_trx_code_context - Bug 4277744
1966 ,NULL
1967 ,NVL(P_Payment_Date,rec_awt_invs.accounting_date)
1968 ,'Y'
1969 ,'NOT REQUIRED'
1970 ,l_org_id
1971 ,l_legal_entity_id
1972 ,'N' -- BUG 3007085
1973 ,nvl(l_payment_method_code,'CHECK')
1974 ,l_payment_reason_code
1975 ,l_bank_charge_bearer
1976 ,l_delivery_channel_code
1977 ,l_settlement_priority
1978 ,l_exclusive_payment_flag
1979 ,l_external_bank_account_id
1980 ,l_party_id
1981 ,l_party_site_id
1982 ,l_payment_reason_comments --4874927
1983 --bug 7699166 changes for Third Party Payments
1984 ,l_remit_to_supplier_name
1985 ,l_remit_to_supplier_id
1986 ,l_remit_to_supplier_site
1987 ,l_remit_to_supplier_site_id
1988 ,l_relationship_id
1989 --bug 7699166
1990 );
1991
1992 --Bug 4539462 DBI logging
1993 AP_DBI_PKG.Maintain_DBI_Summary
1994 (p_table_name => 'AP_INVOICES',
1995 p_operation => 'I',
1996 p_key_value1 => new_invoice_id,
1997 p_calling_sequence => current_calling_sequence);
1998
1999
2000 -- Insert Invoice Lines for each invoice inserted (bug 8266021)
2001
2002 debug_info := 'Insert INTO ap_invoice_lines_all';
2003 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2004 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
2005 END IF;
2006
2007 INSERT INTO AP_INVOICE_LINES_all (
2008 invoice_id,
2009 line_number,
2010 line_type_lookup_code,
2011 description,
2012 line_source,
2013 generate_dists,
2014 match_type,
2015 prorate_across_all_items,
2016 accounting_date,
2017 period_name,
2018 deferred_acctg_flag,
2019 set_of_books_id,
2020 amount,
2021 base_amount,
2022 rounding_amt,
2023 wfapproval_status,
2024 -- ussgl_transaction_code, - Bug 4277744
2025 discarded_flag,
2026 cancelled_flag,
2027 final_match_flag,
2028 assets_tracking_flag,
2029 creation_date,
2030 created_by,
2031 last_update_date,
2032 last_updated_by,
2033 last_update_login,
2034 program_application_id,
2035 program_id,
2036 program_update_date,
2037 request_id,
2038 org_id
2039 )
2040 VALUES
2041 (
2042 new_invoice_id,
2043 1,
2044 'ITEM'
2045 ,new_invoice_base_descr||to_char(rec_awt_invs.invoice_line_number),
2046 'AUTO INVOICE CREATION',
2047 'D',
2048 'NOT MATCHED',
2049 'N',
2050 NVL(P_Payment_Date,rec_awt_invs.accounting_date),
2051 NVL(ap_utilities_pkg.get_current_gl_date(P_Payment_Date, l_org_id),
2052 l_period_name),
2053 'N',
2054 l_set_of_books_id,
2055 rec_awt_invs.invoice_amount,
2056 null, -- bug 5190989
2057 0,
2058 'NOT REQUIRED',
2059 -- rec_awt_lines.ussgl_transaction_code, - Bug 4277744
2060 'N',
2061 'N',
2062 'N',
2063 'N',
2064 SYSDATE,
2065 P_Last_Updated_By,
2066 SYSDATE,
2067 P_Last_Updated_By,
2068 P_Last_Update_Login,
2069 P_Program_Application_ID,
2070 P_Program_ID,
2071 SYSDATE,
2072 P_request_ID,
2073 l_org_id);
2074
2075 --To be resolved by DBI forward porting project.
2076 /*
2077 AP_DBI_PKG.Maintain_DBI_Summary
2078 (p_table_name => 'AP_INVOICE_DISTRIBUTIONS',
2079 p_operation => 'I',
2080 p_key_value1 => new_invoice_id,
2081 p_key_value2 => l_Invoice_distribution_Id,
2082 p_calling_sequence => current_calling_sequence); */
2083
2084
2085 debug_info := 'invoice_id = '||P_Invoice_Id;
2086 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2087 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
2088 END IF;
2089
2090
2091 debug_info := 'invoiceline_number = '||rec_awt_invs.invoice_line_number;
2092 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2093 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
2094 END IF;
2095
2096
2097 IF (P_Calling_Module in ('CANCEL INVOICE','REVERSE DIST','VOID PAYMENT')) THEN
2098 OPEN c_awt_lines_rev (P_Invoice_Id,rec_awt_invs.invoice_line_number);
2099
2100 debug_info := 'c_awt_lines_rev chosen';
2101 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2102 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
2103 END IF;
2104
2105 debug_info := 'rows chosen'||c_awt_lines_rev%ROWCOUNT;
2106 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2107 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
2108 END IF;
2109 ELSE
2110 OPEN c_awt_lines (P_Invoice_Id,rec_awt_invs.invoice_line_number);
2111 debug_info := 'c_awt_lines chosen';
2112 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2113 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
2114 END IF;
2115
2116 debug_info := 'rows chosen'||c_awt_lines%ROWCOUNT;
2117 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2118 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
2119 END IF;
2120 END IF;
2121 l_dist_number := 0;
2122
2123 ---Bug 8266021 now the distributions are inserted for the Withholding invoice
2124 --Here also we will decide the cursor based on the calling module
2125 LOOP
2126 debug_info := 'Fetch CURSOR c_awt_lines';
2127 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2128 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
2129 END IF;
2130
2131
2132
2133 IF (P_Calling_Module in ('CANCEL INVOICE','REVERSE DIST','VOID PAYMENT')) THEN
2134 FETCH c_awt_lines_rev INTO rec_awt_lines;
2135 EXIT WHEN c_awt_lines_rev%NOTFOUND;
2136
2137
2138 ELSE
2139 FETCH c_awt_lines INTO rec_awt_lines;
2140 EXIT WHEN c_awt_lines%NOTFOUND;
2141
2142 END IF;
2143
2144 l_dist_number := l_dist_number + 1 ;
2145
2146 debug_info := 'Insert INTO ap_invoice_distributions';
2147 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2148 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
2149 END IF;
2150
2151 --bug 9913164 begins
2152 IF nvl(l_automatic_offsets,'N') = 'Y' THEN
2153 SELECT dist_code_combination_id
2154 INTO l_dist_code_ccid
2155 FROM ap_invoice_distributions_all
2156 WHERE invoice_distribution_id = rec_awt_lines.awt_related_id;
2157
2158 AP_UTILITIES_PKG.g_org_id := rec_awt_lines.org_id; /*Bug11720134 : Setting the org id global parameter*/
2159
2160 ap_utilities_pkg.build_offset_account(rec_awt_lines.dist_code_combination_id,
2161 l_dist_code_ccid,
2162 NVL(P_Payment_Date,rec_awt_lines.accounting_date),
2163 l_overlayed_awt_ccid,
2164 l_reason,
2165 P_calling_sequence);
2166
2167 debug_info := 'Awt invoice dist ccid -- '||rec_awt_lines.dist_code_combination_id;
2168 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2169 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
2170 END IF;
2171 debug_info := 'Std Invoice Item dist ccid -- '||l_dist_code_ccid;
2172 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2173 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
2174 END IF;
2175 debug_info := 'l_overlayed_awt_ccid -- '||l_overlayed_awt_ccid;
2176 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2177 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
2178 END IF;
2179 debug_info := 'l_reason -- '||l_reason;
2180 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2181 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
2182 END IF;
2183 END IF;
2184 --bug 9913164 end
2185
2186 SELECT ap_invoice_distributions_s.nextval
2187 INTO l_invoice_distribution_id
2188 FROM DUAL;
2189
2190
2191 INSERT INTO ap_invoice_distributions_all (
2192 accounting_date
2193 ,accrual_posted_flag
2194 ,assets_addition_flag
2195 ,assets_tracking_flag
2196 ,cash_posted_flag
2197 ,distribution_line_number
2198 ,dist_code_combination_id
2199 ,invoice_id
2200 ,invoice_line_number
2201 ,last_updated_by
2202 ,last_update_date
2203 ,line_type_lookup_code
2204 ,period_name
2205 ,set_of_books_id
2206 ,amount
2207 ,base_amount
2208 ,batch_id
2209 ,created_by
2210 ,creation_date
2211 ,description
2212 ,last_update_login
2213 ,match_status_flag
2214 ,posted_flag
2215 ,program_application_id
2216 ,program_id
2217 ,program_UPDATE_date
2218 ,request_id
2219 ,tax_code_id
2220 ,encumbered_flag
2221 ,pa_addition_flag
2222 ,posted_amount
2223 ,posted_base_amount
2224 ,awt_flag
2225 ,awt_tax_rate_id
2226 ,awt_gross_amount
2227 ,awt_origin_group_id
2228 ,awt_invoice_payment_id
2229 ,invoice_distribution_id
2230 ,GLOBAL_ATTRIBUTE_CATEGORY
2231 ,GLOBAL_ATTRIBUTE1
2232 ,GLOBAL_ATTRIBUTE2
2233 ,GLOBAL_ATTRIBUTE3
2234 ,GLOBAL_ATTRIBUTE4
2235 ,GLOBAL_ATTRIBUTE5
2236 ,GLOBAL_ATTRIBUTE6
2237 ,GLOBAL_ATTRIBUTE7
2238 ,GLOBAL_ATTRIBUTE8
2239 ,GLOBAL_ATTRIBUTE9
2240 ,GLOBAL_ATTRIBUTE10
2241 ,GLOBAL_ATTRIBUTE11
2242 ,GLOBAL_ATTRIBUTE12
2243 ,GLOBAL_ATTRIBUTE13
2244 ,GLOBAL_ATTRIBUTE14
2245 ,GLOBAL_ATTRIBUTE15
2246 ,GLOBAL_ATTRIBUTE16
2247 ,GLOBAL_ATTRIBUTE17
2248 ,GLOBAL_ATTRIBUTE18
2249 ,GLOBAL_ATTRIBUTE19
2250 ,GLOBAL_ATTRIBUTE20
2251 ,type_1099
2252 ,income_tax_region
2253 ,org_id
2254 ,awt_related_id
2255 --Freight and Special Charges
2256 ,rcv_charge_addition_flag
2257 ,distribution_class) --bug7719929
2258 VALUES
2259 (
2260 NVL(P_Payment_Date,rec_awt_lines.accounting_date)
2261 ,'N'
2262 ,'N'
2263 ,'N'
2264 ,'N'
2265 ,l_dist_number -- distribution_line_number
2266 /* ,rec_awt_lines.dist_code_combination_id */ --bug 9913164
2267 ,nvl(l_overlayed_awt_ccid,rec_awt_lines.dist_code_combination_id) --bug 9913164 --bug 10050107
2268 ,new_Invoice_Id
2269 ,1 -- invoice_line_number
2270 ,P_Last_Updated_By
2271 ,SYSDATE
2272 ,'ITEM'
2273 , NVL(ap_utilities_pkg.get_current_gl_date(P_Payment_Date, rec_awt_lines.org_id),
2274 rec_awt_lines.period_name)
2275 ,rec_awt_lines.set_of_books_id
2276 ,-NVL(rec_awt_lines.base_amount, rec_awt_lines.amount)
2277 ,NULL -- base amount bug 5190989
2278 ,NULL -- batch_id
2279 ,P_Last_Updated_By
2280 ,SYSDATE
2281 ,new_invoice_base_descr||to_char(rec_awt_lines.distribution_line_number)
2282 ,P_Last_Update_Login
2283 ,NULL -- match_status_flag
2284 ,'N' -- posted_flag
2285 ,P_Program_Application_Id
2286 ,P_Program_Id
2287 ,decode (P_Program_Id,NULL,NULL,SYSDATE)
2288 ,P_Request_Id
2289 ,NULL -- tax_code_id
2290 ,'T' -- encumbered_flag
2291 ,'E' -- pa_addition_flag
2292 ,0
2293 ,0
2294 ,NULL -- awt_flag
2295 ,NULL -- awt_tax_rate_id
2296 ,NULL -- awt_gross_amount
2297 ,NULL -- awt_origin_group_id
2298 ,NULL -- awt_invoice_payment_id
2299 ,l_invoice_distribution_id
2300 ,NULL -- Global Attribute Category
2301 ,NULL -- Global Attribute1
2302 ,NULL
2303 ,NULL
2304 ,NULL
2305 ,NULL -- Global Attribute5
2306 ,NULL
2307 ,NULL
2308 ,NULL
2309 ,NULL
2310 ,NULL -- Global Attribute10
2311 ,NULL
2312 ,NULL
2313 ,NULL
2314 ,NULL
2315 ,NULL -- Global Attribute15
2316 ,NULL
2317 ,NULL
2318 ,NULL
2319 ,NULL
2320 ,NULL -- Global Attribute20
2321 ,NULL -- type_1099
2322 ,NULL -- income_tax_region
2323 ,rec_awt_lines.org_id
2324 ,NULL -- awt_related_id
2325 ,'N'
2326 ,'PERMANENT'); -- bug 8304036: modify
2327
2328 AP_DBI_PKG.Maintain_DBI_Summary
2329 ( p_table_name => 'AP_INVOICE_DISTRIBUTIONS',
2330 p_operation => 'I',
2331 p_key_value1 => new_invoice_id,
2332 p_key_value2 => l_invoice_distribution_id,
2333 p_calling_sequence => current_calling_sequence);
2334
2335 --bug 8266021
2336 debug_info := 'Update ap_invoice_distributions';
2337 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2338 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
2339 END IF;
2340
2341 IF (P_Calling_Module in ('CANCEL INVOICE','REVERSE DIST','VOID PAYMENT')) THEN
2342 UPDATE ap_invoice_distributions_all
2343 SET awt_invoice_id = new_invoice_id
2344 WHERE current of c_awt_lines_rev;
2345 ELSE
2346 UPDATE ap_invoice_distributions_all
2347 SET awt_invoice_id = new_invoice_id
2348 WHERE current of c_awt_lines;
2349
2350 END IF;
2351
2352
2353 end loop;
2354
2355 IF (P_Calling_Module in ('CANCEL INVOICE','REVERSE DIST','VOID PAYMENT')) THEN
2356 close c_awt_lines_rev;
2357 ELSE
2358 close c_awt_lines;
2359
2360 END IF;
2361 -- Prepare Terms_Date argument for Payment Schedule Creation
2362 -- PL/SQL
2363
2364 IF (ta_terms_date_basIS = 'Current') THEN
2365 inv_terms_date := SYSDATE;
2366 ELSIF (ta_terms_date_basIS = 'Invoice') THEN
2367 inv_terms_date := NVL(p_payment_date, rec_awt_lines.accounting_date);
2368 ELSIF (ta_terms_date_basIS = 'Goods Received') THEN
2369 inv_terms_date := NVL(goods_received_date, rec_awt_lines.accounting_date);
2370 ELSIF (ta_terms_date_basIS = 'Invoice Received') THEN
2371 inv_terms_date := NVL(invoice_received_date,
2372 rec_awt_lines.accounting_date);
2373 ELSE
2374 inv_terms_date := NULL;
2375 END IF;
2376
2377 -- Create payment schedule for thIS new invoice:
2378
2379 Ap_Create_Pay_Scheds_Pkg.Ap_Create_From_Terms
2380 (new_invoice_id
2381 ,ta_terms_id
2382 ,P_Last_Updated_By
2383 ,P_Last_Updated_By
2384 ,ta_payment_priority
2385 ,l_batch_id --bug 8266021
2386 ,inv_terms_date
2387 ,rec_awt_invs.invoice_amount --bug 8266021
2388 ,gl_currency_api.convert_amount(
2389 base_currency,
2390 ta_payment_currency_code,
2391 rec_awt_invs.accounting_date, --bug 8266021
2392 c_payment_cross_rate_type,
2393 rec_awt_invs.invoice_amount) --bug 8266021
2394 ,c_payment_cross_rate
2395 ,NULL
2396 ,nvl(l_PAYMENT_METHOD_CODE,'CHECK')
2397 ,base_currency
2398 ,ta_payment_currency_code
2399 ,'ap_do_withholding');
2400
2401 -- End: Update original negative distribution with new invoice id:
2402
2403 /* commented in bug 8266021 ,this update has been moved up
2404 debug_info := 'Update ap_invoice_distributions';
2405 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2406 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
2407 END IF;
2408
2409 UPDATE ap_invoice_distributions_all
2410 SET awt_invoice_id = new_invoice_id
2411 WHERE current of c_awt_lines;
2412 */
2413
2414 END LOOP For_Each_Negative_Line;
2415
2416 --bug 8266021
2417 debug_info := 'CLOSE CURSOR c_awt_invs (or c_awt_invs_rev) ';
2418 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2419 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
2420 END IF;
2421 IF (P_Calling_Module in ('CANCEL INVOICE','REVERSE DIST','VOID PAYMENT')) THEN
2422 CLOSE c_awt_invs_rev;
2423 ELSE
2424 CLOSE c_awt_invs;
2425
2426 END IF;
2427
2428 EXCEPTION
2429 WHEN OTHERS THEN
2430 IF (SQLCODE <> -20001) THEN
2431 FND_MESSAGE.set_NAME('SQLAP','AP_DEBUG');
2432 FND_MESSAGE.set_TOKEN('ERROR',SQLERRM);
2433 FND_MESSAGE.set_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
2434 FND_MESSAGE.set_TOKEN('PARAMETERS',
2435 ' Invoice Id = ' || to_char(P_Invoice_Id) ||
2436 ', dist line no = ' || to_char(P_dist_Line_No) ||
2437 ', New Invoice Id = ' || to_char(P_New_Invoice_Id));
2438
2439 FND_MESSAGE.set_TOKEN('DEBUG_INFO',debug_info);
2440 END IF;
2441 APP_EXCEPTION.RAISE_EXCEPTION;
2442
2443 END Create_AWT_Invoices;
2444
2445
2446 PROCEDURE Ap_Do_Withholding (
2447 P_Invoice_Id IN NUMBER,
2448 P_Awt_Date IN DATE,
2449 P_Calling_Module IN VARCHAR2,
2450 P_Amount IN NUMBER,
2451 P_Payment_Num IN NUMBER DEFAULT NULL,
2452 P_Checkrun_Name IN VARCHAR2 DEFAULT NULL,
2453 P_Last_Updated_By IN NUMBER,
2454 P_Last_Update_Login IN NUMBER,
2455 P_Program_Application_Id IN NUMBER DEFAULT NULL,
2456 P_Program_Id IN NUMBER DEFAULT NULL,
2457 P_Request_Id IN NUMBER DEFAULT NULL,
2458 P_Awt_Success OUT NOCOPY VARCHAR2,
2459 P_Invoice_Payment_Id IN NUMBER DEFAULT NULL,
2460 P_Check_Id IN NUMBER DEFAULT NULL,
2461 p_checkrun_id in number default null)
2462 IS
2463 l_awt_flag ap_invoices.awt_flag%TYPE;
2464 l_inv_curr_code ap_invoices.invoice_currency_code%TYPE;
2465 l_tax_name ap_tax_codes.name%TYPE;
2466 l_payment_date DATE := p_awt_date;
2467 l_org_id number; --4742265
2468
2469 -- The variable "l_AWT_success" checks general WT calculations in the first
2470 -- processing unit (Create Temporary AWT distributions), causing a return
2471 -- error message in the following cases:
2472 -- o The invoice has one inactive group
2473 -- o One Tax in any group IS inactive
2474 -- o One Tax Account IS invalid
2475 -- o One Tax has no valid rate
2476
2477 l_AWT_success VARCHAR2(2000) := 'SUCCESS';
2478
2479 DBG_Loc VARCHAR2(30) := 'Ap_Do_Withholding';
2480 current_calling_sequence VARCHAR2(2000);
2481 debug_info VARCHAR2(100);
2482 --
2483 /*
2484
2485 << Beginning of Ap_Do_Withholding program documentation >>
2486
2487 ThIS IS the core PROCEDURE of the Automatic Withholding Tax feature. It
2488 can be invoked by five possible originating events:
2489
2490 - Invoice Autoapproval
2491 - AutoSELECT / Build Payments
2492 - Confirm Payment Batch
2493 - Invoice Entry / Inquiry
2494 - QuickCheck
2495
2496 Three dIFferent processing units ("Create Temporary AWT distributions",
2497 "Create AWT distributions" AND "Create AWT Invoices") are conditionally
2498 executed depENDing on the originating event triggering the Ap_Do_Withholding
2499 PROCEDURE, as represented in the following flow diagrams:
2500
2501 +=========================+
2502 | |
2503 | AutoApproval |
2504 | |
2505 +=========================+
2506 |
2507 |
2508 ^
2509 / \
2510 / \
2511 / \
2512 / create_ \ +------------------------------------+
2513 / dists = \_______| |
2514 \ APPROVAL / Yes | Create Temporary AWT distributions |
2515 \/BOTH / | |
2516 \ ? / +------------------+-----------------+
2517 \ / |
2518 \ / |
2519 v |
2520 No | |
2521 | +------------------+-----------------+
2522 | | |
2523 | | Create AWT distributions |
2524 | | |
2525 | +------------------+-----------------+
2526 | |
2527 +--------------------------------+
2528 |
2529 ^
2530 / \
2531 / \
2532 / \
2533 / create_ \ +------------------------------------+
2534 / invoices \_______| |
2535 \= APPROVAL / Yes | Create AWT Invoices |
2536 \ /BOTH / | |
2537 \ ? / +------------------+-----------------+
2538 \ / |
2539 \ / |
2540 v |
2541 No | |
2542 | |
2543 +--------------------------------+
2544 |
2545 +----+----+
2546 | DONE |
2547 +---------+
2548
2549 +===========================+
2550 | |
2551 | AutoSelect/Build Payments |
2552 | |
2553 +===========================+
2554 |
2555 |
2556 ^
2557 / \
2558 / \
2559 / \
2560 / create_ \ +------------------------------------+
2561 / dists = \_______| |
2562 \ PAYMENT / Yes | Create Temporary AWT distributions |
2563 \ /BOTH / | |
2564 \ ? / +------------------+-----------------+
2565 \ / |
2566 \ / |
2567 v |
2568 No | |
2569 +--------------------------------+
2570 |
2571 | +------------------------------------+
2572 | | |
2573 | | Create AWT distributions |
2574 | | |
2575 | +------------------------------------+
2576 |
2577 | +------------------------------------+
2578 | | |
2579 | | Create AWT Invoices |
2580 | | |
2581 | +------------------------------------+
2582 |
2583 +----+----+
2584 | DONE |
2585 +---------+
2586
2587
2588 +=========================+
2589 | |
2590 | Confirm Payment Batch |
2591 | |
2592 +=========================+
2593 |
2594 | +------------------------------------+
2595 | | |
2596 | | Create Temporary AWT distributions |
2597 | | |
2598 | +------------------------------------+
2599 ^
2600 / \
2601 / \
2602 / \
2603 / create_ \ +------------------------------------+
2604 / dists = \_______| |
2605 \ PAYMENT / Yes | Create AWT distributions |
2606 \ /BOTH / | |
2607 \ ? / +------------------+-----------------+
2608 \ / |
2609 \ / |
2610 v |
2611 No | |
2612 +--------------------------------+
2613 |
2614 ^
2615 / \
2616 / \
2617 / \
2618 / create_ \ +------------------------------------+
2619 / invoices \_______| |
2620 \ = PAYMENT / Yes | Create AWT Invoices |
2621 \ /BOTH / | |
2622 \ ? / +------------------+-----------------+
2623 \ / |
2624 \ / |
2625 v |
2626 No | |
2627 +--------------------------------+
2628 |
2629 +----+----+
2630 | DONE |
2631 +---------+
2632
2633
2634 +=========================+
2635 | |
2636 | Invoice Entry/Inquiry |
2637 | |
2638 +=========================+
2639 | +------------------------------------+
2640 |_____________| |
2641 | Create Temporary AWT distributions |
2642 | |
2643 +------------------+-----------------+
2644 |
2645 +--------------------------------+
2646 |
2647 | +------------------------------------+
2648 | | |
2649 | | Create AWT distributions |
2650 | | |
2651 | +------------------------------------+
2652 |
2653 | +------------------------------------+
2654 | | |
2655 | | Create AWT Invoices |
2656 | | |
2657 | +------------------------------------+
2658 +----+----+
2659 | DONE |
2660 +---------+
2661
2662
2663 +=========================+
2664 | |
2665 | QuickCheck |
2666 | |
2667 +=========================+
2668 |
2669 |
2670 ^
2671 / \
2672 / \
2673 / \
2674 / create_ \ +------------------------------------+
2675 / dists = \_______| |
2676 \ PAYMENT / Yes | Create Temporary AWT distributions |
2677 \ /BOTH / | |
2678 \ ? / +------------------+-----------------+
2679 \ / |
2680 \ / |
2681 v |
2682 No | |
2683 | +------------------+-----------------+
2684 | | |
2685 | | Create AWT distributions |
2686 | | |
2687 | +------------------+-----------------+
2688 | |
2689 +--------------------------------+
2690 |
2691 ^
2692 / \
2693 / \
2694 / \
2695 / create_ \ +------------------------------------+
2696 / invoices \_______| |
2697 \ = PAYMENT / Yes | Create AWT Invoices |
2698 \ /BOTH / | |
2699 \ ? / +------------------+-----------------+
2700 \ / |
2701 \ / |
2702 v |
2703 No | |
2704 +--------------------------------+
2705 |
2706 +----+----+
2707 | DONE |
2708 +---------+
2709
2710 << End of Ap_Do_Withholding program documentation >>
2711
2712 */
2713
2714 BEGIN
2715 current_calling_sequence := 'AP_WITHHOLDING_PKG.AP_Do_Withholding';
2716
2717 -- Execute the ExtENDed Withholding Calculation (IF active)
2718 IF (Ap_ExtENDed_Withholding_Pkg.Ap_ExtENDed_Withholding_Active) THEN
2719 Ap_ExtENDed_Withholding_Pkg.Ap_Do_ExtENDed_Withholding
2720 (P_Invoice_Id,
2721 P_Awt_Date,
2722 P_Calling_Module,
2723 P_Amount,
2724 P_Payment_Num,
2725 P_Checkrun_Name,
2726 P_Last_Updated_By,
2727 P_Last_Update_Login,
2728 P_Program_Application_Id,
2729 P_Program_Id,
2730 P_Request_Id,
2731 P_Awt_Success,
2732 P_Invoice_Payment_Id,
2733 P_Check_Id,
2734 p_checkrun_id);
2735 RETURN;
2736 END IF;
2737
2738 -- Read the AWT flag for the current invoice (i.e. whether AWT
2739 -- calculation has already been performed by AUTOAPPROVAL on thIS
2740 -- invoice):
2741
2742 -- Read setup information
2743 debug_info := 'Read Setup information';
2744 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2745 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
2746 END IF;
2747
2748 SELECT create_awt_dists_type,
2749 create_awt_invoices_type,
2750 NVL(ai.awt_flag, 'N') awt_flag,
2751 ai.invoice_currency_code,
2752 ai.org_id --4742265
2753 INTO l_create_dists,
2754 l_create_invoices,
2755 l_awt_flag,
2756 l_inv_curr_code,
2757 l_org_id --4742265
2758 FROM ap_system_parameters_all asp,
2759 ap_invoices_all ai
2760 WHERE ai.org_id = asp.org_id
2761 and ai.invoice_id = p_invoice_id;
2762
2763 --Bug6660355
2764 -- Starts Automatic Withholding Processing on the invoice
2765 IF (
2766 ( (l_create_dists in ('APPROVAL', 'BOTH'))
2767 AND
2768 (P_Calling_Module = 'AUTOAPPROVAL')
2769 AND
2770 (l_awt_flag <> 'Y'))
2771 OR
2772 ( (l_create_dists in ( 'PAYMENT','BOTH'))
2773 AND
2774 (P_Calling_Module in ('AUTOSELECT', 'QUICKCHECK') ))
2775 OR
2776 ( P_Calling_Module in ('INVOICE ENTRY', 'INVOICE INQUIRY', 'AWT REPORT'))
2777 ) THEN
2778
2779 savepoint BEFORE_TEMPORARY_CALCULATIONS;
2780
2781 debug_info := 'AP_CALC_Withholding_PKG.AP_Calculate_AWT_Amounts';
2782 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2783 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
2784 END IF;
2785
2786 -- Create Temporary AWT distributions:
2787 AP_CALC_Withholding_PKG.AP_Calculate_AWT_Amounts
2788 (P_Invoice_Id
2789 ,P_Awt_Date
2790 ,P_Calling_Module
2791 ,l_create_dists
2792 ,P_Amount
2793 ,P_Payment_Num
2794 ,P_Checkrun_Name
2795 ,P_Last_Updated_By
2796 ,P_Last_Update_Login
2797 ,P_Program_Application_Id
2798 ,P_Program_Id
2799 ,P_Request_Id
2800 ,l_AWT_success
2801 ,current_calling_sequence
2802 ,P_Invoice_Payment_Id
2803 ,p_checkrun_id
2804 ,l_org_id); --4742265
2805
2806 IF (l_AWT_success <> 'SUCCESS') THEN
2807 rollback to BEFORE_TEMPORARY_CALCULATIONS;
2808 END IF;
2809 END IF;
2810 --Bug6660355
2811 IF ( ( ( (l_create_dists in ('APPROVAL','BOTH'))
2812 AND
2813 (P_Calling_Module = 'AUTOAPPROVAL')
2814 AND
2815 (l_awt_flag <> 'Y'))
2816 OR
2817 ( (l_create_dists in ('PAYMENT','BOTH'))
2818 AND
2819 (P_Calling_Module in ('CONFIRM', 'QUICKCHECK')))
2820 OR
2821 (P_Calling_Module = 'AWT REPORT'))
2822 AND
2823 (l_AWT_success = 'SUCCESS'))
2824 THEN
2825
2826 debug_info := 'Create_AWT_Distributions';
2827 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2828 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
2829 END IF;
2830
2831 -- Create AWT distributions:
2832 Create_AWT_distributions
2833 (P_Invoice_Id
2834 ,P_Calling_Module
2835 ,l_create_dists
2836 ,P_Payment_Num
2837 ,l_inv_curr_code
2838 ,P_Last_Updated_By
2839 ,P_Last_Update_Login
2840 ,P_Program_Application_Id
2841 ,P_Program_Id
2842 ,P_Request_Id
2843 ,current_calling_sequence
2844 ,P_Check_Id); --bug 8590059
2845
2846 END IF;
2847 --Bug6660355
2848 IF ( ( ( (l_create_invoices in ('APPROVAL','BOTH'))
2849 AND
2850 (P_Calling_Module = 'AUTOAPPROVAL')
2851 AND
2852 (l_awt_flag <> 'Y'))
2853 OR
2854 ( (l_create_invoices in('PAYMENT','BOTH'))
2855 AND
2856 (P_Calling_Module in ('CONFIRM', 'QUICKCHECK'))
2857 ))
2858 AND
2859 (l_AWT_success = 'SUCCESS')) THEN
2860 -- Create AWT Invoices:
2861
2862 IF (P_Calling_Module NOT IN ('CONFIRM', 'QUICKCHECK')) THEN
2863 l_payment_date := NULL;
2864 END IF;
2865
2866 debug_info := 'Create_AWT_Invoices';
2867 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2868 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
2869 END IF;
2870
2871 -- Bug 8254604 Modified method call to populate all the input parameters.
2872 Create_AWT_Invoices(
2873 P_Invoice_Id => P_Invoice_Id,
2874 P_Payment_Date => l_payment_date,
2875 P_Last_Updated_By => P_Last_Updated_By,
2876 P_Last_Update_Login => P_Last_Update_Login,
2877 P_Program_Application_Id => P_Program_Application_Id,
2878 P_Program_Id => P_Program_Id,
2879 P_Request_Id => P_Request_Id,
2880 P_Calling_Sequence => current_calling_sequence,
2881 P_Calling_Module => p_calling_module, --Bug6660355
2882 P_Inv_Line_No => NULL,
2883 P_Dist_Line_No => NULL,
2884 P_New_Invoice_Id => NULL,
2885 P_create_dists => l_create_dists); --Bug7685907
2886 END IF;
2887
2888 -- Set general response for thIS Ap_Do_Withholding execution:
2889 P_Awt_Success := l_AWT_success;
2890
2891 EXCEPTION
2892 WHEN OTHERS THEN
2893 IF (SQLCODE <> -20001) THEN
2894 FND_MESSAGE.set_NAME('SQLAP','AP_DEBUG');
2895 FND_MESSAGE.set_TOKEN('ERROR',SQLERRM);
2896 FND_MESSAGE.set_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
2897 FND_MESSAGE.set_TOKEN('PARAMETERS',
2898 ' Invoice Id = ' || to_char(P_Invoice_Id) ||
2899 ', AWT Date = ' || to_char(P_Awt_Date) ||
2900 ', Calling module = ' || P_Calling_Module ||
2901 ', Amount = ' || to_char(P_Amount) ||
2902 ', Payment Num = ' || to_char(P_Payment_Num) ||
2903 ', Checkrun Name = ' || P_Checkrun_Name);
2904
2905 FND_MESSAGE.set_TOKEN('DEBUG_INFO',debug_info);
2906 END IF;
2907 APP_EXCEPTION.RAISE_EXCEPTION;
2908
2909 END Ap_Do_Withholding;
2910
2911
2912 PROCEDURE Ap_Withhold_AutoSelect (
2913 P_Checkrun_Name IN VARCHAR2,
2914 P_Last_Updated_By IN NUMBER,
2915 P_Last_Update_Login IN NUMBER,
2916 P_Program_Application_Id IN NUMBER,
2917 P_Program_Id IN NUMBER,
2918 P_Request_Id IN NUMBER,
2919 p_checkrun_id in number)
2920 IS
2921 DBG_Loc VARCHAR2(30) := 'Ap_Withhold_AutoSelect';
2922 current_calling_sequence VARCHAR2(2000);
2923 debug_info VARCHAR2(100);
2924
2925 /* Start - BUG 14364091 - Logging */
2926 l_api_name varchar2(30);
2927 /* End - BUG 14364091 - Logging */
2928
2929 BEGIN
2930 /* Start - BUG 14364091 - Logging */
2931 l_api_name := 'Ap_Undo_Temp_Withholding';
2932 /* End - BUG 14364091 - Logging */
2933 current_calling_sequence := 'AP_WITHHOLDING_PKG.AP_Withhold_AutoSelect';
2934
2935 debug_info := 'AP_WITHHOLDING_PKG.AP_Withhold_AutoSelect';
2936 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2937 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
2938 END IF;
2939
2940 -- Undo Withholding for all SELECTed invoices in thIS checkrun
2941
2942 DECLARE
2943 CURSOR c_all_sel_invs (l_checkrun_name IN VARCHAR2, l_checkrun_id in number)
2944 IS
2945 SELECT invoice_id
2946 , vendor_id
2947 , payment_num
2948 FROM ap_SELECTed_invoices_all ASI,
2949 ap_system_parameters_all asp
2950 WHERE checkrun_name = l_checkrun_name
2951 AND original_invoice_id IS NULL
2952 AND asp.org_id = asi.org_id
2953 and checkrun_id = l_checkrun_id
2954 --Bug6660355
2955 AND decode(nvl(ASP.allow_awt_flag, 'N'), 'Y',
2956 decode(ASP.create_awt_dists_type, 'PAYMENT',
2957 'Y','BOTH','Y',decode(ASP.create_awt_invoices_type, 'PAYMENT',
2958 'Y','BOTH','Y','N'),
2959 'N'),
2960 'N') = 'Y';
2961
2962
2963 rec_all_sel_invs c_all_sel_invs%ROWTYPE;
2964
2965 BEGIN
2966 debug_info := 'OPEN CURSOR for all SELECTed invoices';
2967 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2968 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
2969 END IF;
2970
2971 OPEN c_all_sel_invs (P_Checkrun_Name, p_checkrun_id);
2972
2973 LOOP
2974 debug_info := 'Fetch CURSOR for all SELECTed invoices';
2975 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2976 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
2977 END IF;
2978
2979 FETCH c_all_sel_invs INTO rec_all_sel_invs;
2980 EXIT WHEN c_all_sel_invs%NOTFOUND;
2981
2982 DECLARE
2983 undo_output VARCHAR2(2000);
2984 BEGIN
2985 Ap_Undo_Temp_Withholding
2986 (P_Invoice_Id => rec_all_sel_invs.invoice_id
2987 ,P_VENDor_Id => rec_all_sel_invs.vendor_id
2988 ,P_Payment_Num => rec_all_sel_invs.payment_num
2989 ,P_Checkrun_Name => P_Checkrun_Name
2990 ,P_Undo_Awt_Date => SYSDATE
2991 ,P_Calling_Module => 'AUTOSELECT'
2992 ,P_Last_Updated_By => P_Last_Updated_By
2993 ,P_Last_Update_Login => P_Last_Update_Login
2994 ,P_Program_Application_Id => P_Program_Application_Id
2995 ,P_Program_Id => P_Program_Id
2996 ,P_Request_Id => P_Request_Id
2997 ,P_Awt_Success => undo_output
2998 ,P_checkrun_id => p_checkrun_id );
2999 END;
3000 END LOOP;
3001
3002 debug_info := 'CLOSE CURSOR for all SELECTed invoices';
3003 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3004 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
3005 END IF;
3006
3007 CLOSE c_all_sel_invs;
3008
3009 END;
3010
3011 -- DO Withholding for all OK to pay SELECTed invoices in thIS checkrun
3012 -- that have No Manual AWT dists
3013
3014 UPDATE ap_SELECTed_invoices_all
3015 SET ok_to_pay_flag = 'Y',
3016 proposed_payment_amount = invoice_amount * payment_cross_rate,
3017 -- We cannot round the proposed_payment_amount here since we don't
3018 -- have payment_currency_code. We will round it later.
3019 dont_pay_reason_code = NULL,
3020 dont_pay_description = NULL
3021 WHERE checkrun_name = P_Checkrun_Name AND
3022 ok_to_pay_flag = 'N' AND
3023 checkrun_id = p_checkrun_id and
3024 dont_pay_reason_code = 'AWT ERROR';
3025
3026 -- Execute Core Withholding Calculation Routine
3027
3028 IF (NOT Ap_ExtENDed_Withholding_Pkg.Ap_ExtENDed_Withholding_Active) THEN
3029 DECLARE
3030 CURSOR c_ok_sel_invs (l_checkrun_name IN VARCHAR2, l_checkrun_id in number)
3031 IS
3032 SELECT ASI.invoice_id
3033 , ASI.payment_num
3034 , ASI.payment_amount
3035 , ASI.discount_amount
3036 , NVL(ASI.invoice_exchange_rate, 1) invoice_exchange_rate
3037 , NVL(ASI.payment_cross_rate,1) payment_cross_rate
3038 , AI.payment_currency_code
3039 , NVL(asp.awt_include_discount_amt, 'N') include_discount_amt
3040 , asp.base_currency_code
3041 , NVL(ASI.payment_exchange_rate,1) payment_exchange_rate --bug 8590059
3042 FROM ap_SELECTed_invoices_all ASI,
3043 ap_invoices_all AI,
3044 ap_system_parameters_all asp
3045 WHERE ASI.checkrun_name = l_checkrun_name
3046 AND asi.checkrun_id = l_checkrun_id
3047 AND AI.invoice_id = ASI.invoice_id
3048 AND AI.org_id = asp.org_id
3049 AND NVL(ASI.ok_to_pay_flag,'Y') IN ( 'Y','F')
3050 AND NOT EXISTS (SELECT 'Manual AWT dists exist'
3051 FROM ap_invoice_distributions AID
3052 WHERE AID.invoice_id = ASI.invoice_id
3053 AND AID.line_type_lookup_code = 'AWT'
3054 AND AID.awt_flag = 'M')
3055 AND ((ASP.create_awt_dists_type ='PAYMENT' --Bug6660355
3056 AND NOT EXISTS (SELECT 'Invoice already withheld by AutoApproval'
3057 FROM ap_invoices AI
3058 WHERE AI.invoice_id = ASI.invoice_id
3059 AND NVL(AI.awt_flag, 'N') = 'Y'))
3060 OR
3061 ASP.create_awt_dists_type ='BOTH')
3062
3063 AND EXISTS (SELECT 'At least one dist exists with AWT_GROUP_ID'
3064 FROM ap_invoice_distributions AID
3065 WHERE AID.invoice_id = ASI.invoice_id
3066 AND AID.pay_awt_group_id IS NOT NULL) --Bug8631142
3067 AND ASI.original_invoice_id IS NULL --Bug6660355
3068 AND decode(nvl(ASP.allow_awt_flag, 'N'), 'Y',
3069 decode(ASP.create_awt_dists_type, 'PAYMENT',
3070 'Y','BOTH','Y', decode(ASP.create_awt_invoices_type, 'PAYMENT',
3071 'Y','BOTH','Y','N'),
3072 'N'),
3073 'N') = 'Y'
3074 FOR UPDATE OF
3075 ASI.proposed_payment_amount
3076 , ASI.payment_amount
3077 , ASI.withholding_amount
3078 , ASI.ok_to_pay_flag
3079 , ASI.dont_pay_reason_code
3080 , ASI.dont_pay_description;
3081
3082 rec_ok_sel_invs c_ok_sel_invs%ROWTYPE;
3083
3084 l_awt_date DATE;
3085 l_withholding_amount NUMBER;
3086 l_subject_amount NUMBER;
3087 l_awt_success VARCHAR2(2000);
3088 l_invoice_amount NUMBER;
3089 l_amount_remaining NUMBER;
3090 l_total_amount NUMBER;
3091 l_count NUMBER;
3092 l_amountapplied NUMBER;
3093 l_update_indicator number:=0;
3094 l_total_awt_amount NUMBER;--6660355
3095 l_amount_payable NUMBER;
3096
3097 BEGIN
3098
3099 debug_info := 'Select check_date for thIS checkrun';
3100 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3101 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
3102 END IF;
3103
3104 SELECT AISC.check_date
3105 INTO l_awt_date
3106 FROM ap_inv_SELECTion_criteria_all AISC
3107 WHERE AISC.checkrun_name = P_Checkrun_Name
3108 and aisc.checkrun_id = p_checkrun_id;
3109
3110
3111 debug_info := 'OPEN CURSOR for all ok to pay invoices';
3112 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3113 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
3114 END IF;
3115
3116 OPEN c_ok_sel_invs (P_Checkrun_Name, p_checkrun_id);
3117
3118 LOOP
3119 debug_info := 'Fetch CURSOR for all ok to pay invoices';
3120 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3121 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
3122 END IF;
3123
3124 FETCH c_ok_sel_invs INTO rec_ok_sel_invs;
3125 EXIT WHEN c_ok_sel_invs%NOTFOUND;
3126
3127 if l_update_indicator = 0 then
3128 --if we are here the cursor got data, so we need to set the
3129 --batches rejection levels to request
3130
3131 -- Bug 7492768 We need to set the inv_awt_exists_flag which indicates if the
3132 -- check run contains invoice that has awt. If the flag is set we would
3133 -- pass the rejection_level_code as 'REQUEST' to IBY.
3134 -- We will not update the rejection levels directly so that we can retrieve
3135 -- the initial values for these if the user removes awt invoices during
3136 -- the review stage from the selected invoices.
3137 update ap_inv_selection_criteria_all
3138 set /*document_rejection_level_code = 'REQUEST',
3139 payment_rejection_level_code = 'REQUEST'*/
3140 inv_awt_exists_flag = 'Y'
3141 where checkrun_id = p_checkrun_id;
3142
3143 l_update_indicator := 1;
3144 end if;
3145
3146
3147 IF (rec_ok_sel_invs.include_discount_amt = 'Y') THEN
3148 l_subject_amount := rec_ok_sel_invs.payment_amount +
3149 rec_ok_sel_invs.discount_amount;
3150 ELSE
3151 l_subject_amount := rec_ok_sel_invs.payment_amount;
3152 END IF;
3153
3154 SELECT /*invoice_amount,*/ amount_remaining /*Bug 14530960*/
3155 INTO /*l_invoice_amount,*/ l_amount_remaining /*Bug 14530960*/
3156 FROM ap_selected_invoices_all
3157 WHERE invoice_id = rec_ok_sel_invs.invoice_id
3158 AND checkrun_name = p_checkrun_name
3159 and checkrun_id = p_checkrun_id
3160 AND payment_num = rec_ok_sel_invs.payment_num;
3161
3162 /*Start of Bug 14530960*/
3163 SELECT sum(nvl(base_amount,amount))
3164 INTO l_invoice_amount
3165 FROM ap_invoice_distributions
3166 WHERE invoice_id = rec_ok_sel_invs.invoice_id
3167 AND line_type_lookup_code <> 'AWT'; /*End of Bug 14530960*/
3168
3169 --Bug6660355
3170 /*SELECT sum(nvl(aid.base_amount,aid.amount)) --query is commented for Bug:14696775
3171 INTO l_total_awt_amount
3172 FROM ap_invoice_distributions aid,ap_invoices ai
3173 WHERE aid.invoice_id = ai.invoice_id
3174 AND aid.invoice_id =rec_ok_sel_invs.invoice_id
3175 AND aid.line_type_lookup_code in ('AWT')
3176 AND aid.awt_origin_group_id = ai.awt_group_id;*/
3177
3178 --query is added for Bug:14696775
3179 SELECT sum(nvl(aid.base_amount,aid.amount))
3180 INTO l_total_awt_amount
3181 FROM ap_invoice_distributions aid
3182 WHERE aid.invoice_id =rec_ok_sel_invs.invoice_id
3183 AND aid.line_type_lookup_code in ('AWT')
3184 AND aid.awt_invoice_payment_id is null;
3185
3186 /* Bug 12598502
3187 --Get the total amount of the invoices SELECTed in the batch.
3188 SELECT SUM(NVL(payment_amount,0)) +
3189 SUM((-1) * NVL(withholding_amount,0))
3190 INTO l_total_amount
3191 FROM ap_SELECTed_invoices_all
3192 WHERE checkrun_name = p_checkrun_name
3193 and checkrun_id = p_checkrun_id
3194 AND NVL(ok_to_pay_flag,'Y') in ( 'Y','F');
3195
3196 --Get the count of credit AND debit memos in the batch.
3197 Select COUNT(*)
3198 INTO l_count
3199 FROM ap_selected_invoices_all
3200 WHERE checkrun_name = p_checkrun_name
3201 and checkrun_id = p_checkrun_id
3202 AND NVL(ok_to_pay_flag,'Y') IN ( 'Y','F')
3203 AND invoice_amount < 0;
3204
3205 -- The following statements should be executed only for credit memos with
3206 -- amount remaining equals to payment amount AND total amount <> 0. Because IF
3207 -- total amount IS zero, withholding tax should be calculated for whole invoice
3208 -- amount. If amount remaining IS not equal to payment amount, withholding tax
3209 -- should be calculated for payment amount AND need not to go inside thIS LOOP.
3210
3211 IF l_invoice_amount < 0 AND l_amount_remaining = rec_ok_sel_invs.payment_amount
3212 AND l_total_amount <> 0 THEN
3213
3214 SELECT (-1) * (SUM(NVL(payment_amount,0) +
3215 NVL(ABS(withholding_amount),0)))
3216 INTO l_subject_amount
3217 FROM ap_selected_invoices_all
3218 WHERE payment_amount > 0
3219 AND NVL(ok_to_pay_flag,'Y') in ( 'Y','F')
3220 AND checkrun_name = p_checkrun_name
3221 and checkrun_id = p_checkrun_id;
3222
3223 -- If the batch contains more than one credit memo, get the applied amount AND
3224 -- subtract it FROM subject amount.
3225
3226 IF l_count > 1 THEN
3227 SELECT (-1) * (SUM(NVL(ABS(payment_amount),0) +
3228 NVL(withholding_amount,0)))
3229 INTO l_amountapplied
3230 FROM ap_selected_invoices_all
3231 WHERE NVL(withholding_amount,0) > 0
3232 AND NVL(ok_to_pay_flag,'Y') in ( 'Y','F')
3233 AND checkrun_name = p_checkrun_name
3234 and checkrun_id = p_checkrun_id;
3235
3236 IF ABS(l_amountapplied) > 0 THEN
3237 l_subject_amount := l_subject_amount - l_amountapplied;
3238 END IF;
3239 END IF;
3240
3241 -- If the subject amount IS greater than amount remaining, subject amount
3242 -- should be replaced with amount remaining.
3243
3244 IF ABS(l_subject_amount) > Abs(l_amount_remaining) THEN
3245 l_subject_amount := l_amount_remaining;
3246 END IF;
3247
3248 END IF;
3249 end bug 12598502 */
3250 /* Bug 4990575 removed the round currency function from below statement */
3251 /* l_subject_amount := ap_utilities_pkg.ap_round_currency(
3252 l_subject_amount /
3253 rec_ok_sel_invs.payment_cross_rate *
3254 rec_ok_sel_invs.invoice_exchange_rate,
3255 rec_ok_sel_invs.base_currency_code);*/
3256 l_subject_amount := l_subject_amount * rec_ok_sel_invs.payment_exchange_rate; -- bug 8590059
3257 l_amount_payable :=l_invoice_amount + nvl(l_total_awt_amount,0); --Bug8631142
3258
3259 --bug14176696, commented rounding for subject amount
3260 /*
3261 --Bug12376604: Added if else block for l_amount_payable = 0
3262 if (l_amount_payable <> 0) then
3263 l_subject_amount := ap_utilities_pkg.ap_round_currency((l_subject_amount * l_invoice_amount/l_amount_payable)
3264 ,rec_ok_sel_invs.payment_currency_code); --6660355
3265 else
3266 l_subject_amount := ap_utilities_pkg.ap_round_currency(l_subject_amount ,rec_ok_sel_invs.payment_currency_code);
3267 end if;
3268 --End of Bug12376604
3269 */
3270
3271 --bug14176696
3272 if (l_amount_payable <> 0) then
3273 l_subject_amount := (l_subject_amount * l_invoice_amount/l_amount_payable);
3274 end if;
3275
3276
3277 debug_info := 'AP_DO_WITHHOLDING';
3278 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3279 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
3280 END IF;
3281
3282 Ap_Do_Withholding
3283 (P_Invoice_Id => rec_ok_sel_invs.invoice_id
3284 ,P_Awt_Date => l_awt_date
3285 ,P_Calling_Module => 'AUTOSELECT'
3286 ,P_Amount => l_subject_amount
3287 ,P_Payment_Num => rec_ok_sel_invs.payment_num
3288 ,P_Checkrun_Name => P_Checkrun_Name
3289 ,P_Last_Updated_By => P_Last_Updated_By
3290 ,P_Last_Update_Login => P_Last_Update_Login
3291 ,P_Program_Application_Id => P_Program_Application_Id
3292 ,P_Program_Id => P_Program_Id
3293 ,P_Request_Id => P_Request_Id
3294 ,P_Awt_Success => l_awt_success
3295 ,P_checkrun_id => p_checkrun_id
3296 );
3297
3298 IF (l_awt_success = 'SUCCESS') THEN
3299
3300 debug_info := 'Select sum of withholding amount for thIS invoice';
3301 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3302 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
3303 END IF;
3304
3305 SELECT NVL(SUM(AATD.withholding_amount), 0)
3306 INTO l_withholding_amount
3307 FROM ap_awt_temp_distributions_all AATD
3308 WHERE AATD.checkrun_name = P_Checkrun_Name
3309 AND AATD.invoice_id = rec_ok_sel_invs.invoice_id
3310 AND AATD.payment_num = rec_ok_sel_invs.payment_num
3311 and aatd.checkrun_id = p_checkrun_id;
3312
3313 l_withholding_amount := ap_utilities_pkg.ap_round_currency(
3314 l_withholding_amount /
3315 rec_ok_sel_invs.payment_exchange_rate,
3316 -- * rec_ok_sel_invs.payment_cross_rate, -- bug 8590059
3317 rec_ok_sel_invs.payment_currency_code);
3318
3319 debug_info := 'Update proposed payment in ap_selected_invoices';
3320 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3321 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
3322 END IF;
3323
3324 UPDATE ap_selected_invoices_all ASI
3325 SET /*ASI.proposed_payment_amount =
3326 ap_utilities_pkg.ap_round_currency(
3327 ASI.proposed_payment_amount,rec_ok_sel_invs.payment_currency_code) -
3328 l_withholding_amount
3329 -- We round proposed_payment_amount here because we couldn't round it earlier.
3330 ,ASI.payment_amount =
3331 ASI.payment_amount - l_withholding_amount
3332 ,ASI.amount_remaining =
3333 ASI.amount_remaining - l_withholding_amount
3334 ,ASI.withholding_amount = l_withholding_amount */
3335 --Bug#8281225 Wrong Amount Remaining in Case of Inv Payment Through PPR
3336 ASI.proposed_payment_amount = ap_utilities_pkg.ap_round_currency(ASI.proposed_payment_amount,rec_ok_sel_invs.payment_currency_code)
3337 - nvl(l_withholding_amount, 0)
3338 ,ASI.payment_amount = ap_utilities_pkg.ap_round_currency(ASI.proposed_payment_amount,rec_ok_sel_invs.payment_currency_code)
3339 - nvl(l_withholding_amount, 0)
3340 ,ASI.withholding_amount = l_withholding_amount
3341 WHERE current of c_ok_sel_invs;
3342 ELSE
3343 debug_info := 'Update AWT error in ap_selected_invoices';
3344 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3345 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
3346 END IF;
3347
3348 UPDATE ap_SELECTed_invoices_all ASI
3349 SET ASI.ok_to_pay_flag = 'N',
3350 ASI.dont_pay_reason_code = 'AWT ERROR',
3351 ASI.dont_pay_description = substr(l_awt_success, 1, 255)
3352 WHERE current of c_ok_sel_invs;
3353 END IF;
3354 END LOOP;
3355
3356 debug_info := 'CLOSE CURSOR for all ok to pay invoices';
3357 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3358 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
3359 END IF;
3360
3361 CLOSE c_ok_sel_invs;
3362 END;
3363
3364 ELSE --extended awt is used, set the rejection levels for the batch
3365
3366 -- Bug 7492768 We need to set the inv_awt_exists_flag which indicates if the
3367 -- check run contains invoice that has awt. If the flag is set we would
3368 -- pass the rejection_level_code as 'REQUEST' to IBY.
3369 -- We will not update the rejection levels directly so that we can retrieve
3370 -- the initial values for these if the user removes awt invoices during
3371 -- the review stage from the selected invoices.
3372 update ap_inv_selection_criteria_all
3373 set /*document_rejection_level_code = 'REQUEST',
3374 payment_rejection_level_code = 'REQUEST'*/
3375 inv_awt_exists_flag = 'Y'
3376 where checkrun_id = p_checkrun_id;
3377
3378 END IF;
3379 EXCEPTION
3380 WHEN OTHERS THEN
3381
3382
3383
3384 IF (SQLCODE <> -20001) THEN
3385 FND_MESSAGE.set_NAME('SQLAP','AP_DEBUG');
3386 FND_MESSAGE.set_TOKEN('ERROR',SQLERRM);
3387 FND_MESSAGE.set_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
3388 FND_MESSAGE.set_TOKEN('PARAMETERS',
3389 ' Checkrun Name = ' || P_Checkrun_Name ||
3390 ', Program_Id = ' || to_char(P_Program_Id) ||
3391 ', Request_Id = ' || to_char(P_Request_Id));
3392
3393 FND_MESSAGE.set_TOKEN('DEBUG_INFO',debug_info);
3394 END IF;
3395
3396 /* Start - BUG 14364091 - Logging */
3397 FND_FILE.PUT_LINE(FND_FILE.LOG, 'ERROR Code : ' || SQLCODE);
3398 FND_FILE.PUT_LINE(FND_FILE.LOG, 'ERROR Msg : ' || SQLERRM);
3399 FND_FILE.PUT_LINE(FND_FILE.LOG, G_MODULE_NAME || '.' || l_api_name || ' - ' || debug_info ||' - '||systimestamp);
3400 /* End - BUG 14364091 - Logging */
3401
3402 APP_EXCEPTION.RAISE_EXCEPTION;
3403
3404 END Ap_Withhold_AutoSelect;
3405
3406 PROCEDURE Ap_Withhold_Confirm (
3407 P_Checkrun_Name IN VARCHAR2,
3408 P_Last_Updated_By IN NUMBER,
3409 P_Last_Update_Login IN NUMBER,
3410 P_Program_Application_Id IN NUMBER,
3411 P_Program_Id IN NUMBER,
3412 P_Request_Id IN NUMBER,
3413 p_checkrun_id in number,
3414 p_completed_pmts_group_id in number,
3415 p_org_id in number,
3416 p_check_date in date
3417 )
3418 IS
3419 -- DO Withholding for all OK to pay selected invoices in this checkrun
3420 CURSOR c_ok_sel_invs IS
3421 SELECT ASI.invoice_id,
3422 ASI.payment_num,
3423 p_check_date payment_date
3424 FROM ap_selected_invoices_all ASI,
3425 iby_fd_docs_payable_v ibydocs
3426 WHERE ASI.checkrun_name = p_checkrun_name
3427 AND ASI.original_invoice_id IS NULL
3428 and asi.checkrun_id = p_checkrun_id
3429 and ibydocs.calling_app_doc_unique_ref1 = to_char(asi.checkrun_id) /* Added to_char for bug#8462020 */
3430 AND ibydocs.calling_app_doc_unique_ref2 = to_char(asi.invoice_id) /* Added to_char for bug#8462020 */
3431 AND ibydocs.calling_app_doc_unique_ref3 = to_char(asi.payment_num) /* Added to_char for bug#8462020 */
3432 and ibydocs.completed_pmts_group_id = p_completed_pmts_group_id
3433 and ibydocs.org_id = p_org_id
3434 and ibydocs.calling_app_id = 200; /* Added calling_app_id condition for bug#8462020 */
3435
3436
3437 rec_ok_sel_invs c_ok_sel_invs%ROWTYPE;
3438 l_awt_success VARCHAR2(2000);
3439 DBG_Loc VARCHAR2(30) := 'Ap_Withhold_Confirm';
3440 current_calling_sequence VARCHAR2(2000);
3441 debug_info VARCHAR2(100);
3442 BEGIN
3443 current_calling_sequence := 'AP_WITHHOLDING_PKG.AP_Withhold_Confirm';
3444
3445 -- Execute Core Withholding Routine for each invoice within
3446 -- the payment batch
3447
3448 IF (NOT Ap_ExtENDed_Withholding_Pkg.Ap_ExtENDed_Withholding_Active) THEN
3449
3450 debug_info := 'OPEN CURSOR for all OK to pay invoices';
3451 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3452 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
3453 END IF;
3454
3455 OPEN c_ok_sel_invs ;
3456
3457 LOOP
3458 debug_info := 'Fetch CURSOR for all OK to pay invoices';
3459 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3460 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
3461 END IF;
3462
3463 FETCH c_ok_sel_invs INTO rec_ok_sel_invs;
3464 EXIT WHEN c_ok_sel_invs%NOTFOUND;
3465 Ap_Do_Withholding
3466 (P_Invoice_Id => rec_ok_sel_invs.invoice_id
3467 ,P_Awt_Date => rec_ok_sel_invs.payment_date
3468 ,P_Calling_Module => 'CONFIRM'
3469 ,P_Amount => NULL
3470 ,P_Payment_Num => rec_ok_sel_invs.payment_num
3471 ,P_Checkrun_Name => P_Checkrun_Name
3472 ,P_Last_Updated_By => P_Last_Updated_By
3473 ,P_Last_Update_Login => P_Last_Update_Login
3474 ,P_Program_Application_Id => P_Program_Application_Id
3475 ,P_Program_Id => P_Program_Id
3476 ,P_Request_Id => P_Request_Id
3477 ,P_Awt_Success => l_awt_success
3478 ,p_checkrun_id => p_checkrun_id
3479 );
3480 END LOOP;
3481
3482 debug_info := 'CLOSE CURSOR for all OK to pay invoices';
3483 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3484 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
3485 END IF;
3486
3487 CLOSE c_ok_sel_invs;
3488
3489 -- Execute ExtENDed Withholding Routine for the entire payment)
3490 --
3491 ELSE
3492 Ap_Do_Withholding
3493 (P_Invoice_Id => NULL
3494 ,P_Awt_Date => NULL
3495 ,P_Calling_Module => 'CONFIRM'
3496 ,P_Amount => NULL
3497 ,P_Payment_Num => NULL
3498 ,P_Checkrun_Name => P_Checkrun_Name
3499 ,P_Last_Updated_By => P_Last_Updated_By
3500 ,P_Last_Update_Login => P_Last_Update_Login
3501 ,P_Program_Application_Id => P_Program_Application_Id
3502 ,P_Program_Id => P_Program_Id
3503 ,P_Request_Id => P_Request_Id
3504 ,P_Awt_Success => l_awt_success
3505 ,p_checkrun_id => p_checkrun_id
3506 );
3507 END IF;
3508
3509 EXCEPTION
3510 WHEN OTHERS THEN
3511 IF (SQLCODE <> -20001) THEN
3512 FND_MESSAGE.set_NAME('SQLAP','AP_DEBUG');
3513 FND_MESSAGE.set_TOKEN('ERROR',SQLERRM);
3514 FND_MESSAGE.set_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
3515 FND_MESSAGE.set_TOKEN('PARAMETERS',
3516 ' Checkrun Name = ' || P_Checkrun_Name ||
3517 ', Program_Id = ' || to_char(P_Program_Id) ||
3518 ', Request_Id = ' || to_char(P_Request_Id));
3519 FND_MESSAGE.set_TOKEN('DEBUG_INFO',debug_info);
3520 END IF;
3521
3522 /* Start - BUG 14364091 - Logging */
3523 FND_FILE.PUT_LINE(FND_FILE.LOG, 'ERROR Code : ' || SQLCODE);
3524 FND_FILE.PUT_LINE(FND_FILE.LOG, 'ERROR Msg : ' || SQLERRM);
3525 FND_FILE.PUT_LINE(FND_FILE.LOG, debug_info||' - '||systimestamp);
3526 /* End - BUG 14364091 - Logging */
3527
3528 APP_EXCEPTION.RAISE_EXCEPTION;
3529
3530 END Ap_Withhold_Confirm;
3531
3532
3533 PROCEDURE Ap_Withhold_Cancel (
3534 P_Checkrun_Name IN VARCHAR2,
3535 P_Last_Updated_By IN NUMBER,
3536 P_Last_Update_Login IN NUMBER,
3537 P_Program_Application_Id IN NUMBER,
3538 P_Program_Id IN NUMBER,
3539 P_Request_Id IN NUMBER,
3540 p_checkrun_id in number,
3541 p_completed_pmts_group_id in number default null,
3542 p_org_id in number default null)
3543 IS
3544 -- UNDO Withholding for all selected invoices in thIS checkrun
3545 CURSOR c_all_sel_invs (l_checkrun_name IN VARCHAR2, l_checkrun_id in number)
3546 IS
3547 SELECT ASI.invoice_id
3548 , ASI.payment_num
3549 , AI.vendor_id
3550 FROM ap_SELECTed_invoices_all ASI
3551 , ap_invoices_all AI
3552 WHERE ASI.checkrun_name = l_checkrun_name
3553 AND AI.invoice_id = ASI.invoice_id
3554 and asi.checkrun_id = l_checkrun_id;
3555
3556 rec_all_sel_invs c_all_sel_invs%ROWTYPE;
3557
3558 CURSOR C_sel_invs is
3559 SELECT ASI.invoice_id
3560 , ASI.payment_num
3561 , AI.vendor_id
3562 FROM ap_SELECTed_invoices_all ASI
3563 , ap_invoices_all AI
3564 , iby_fd_docs_payable_v ibydocs
3565 WHERE ASI.checkrun_name = p_checkrun_name
3566 AND AI.invoice_id = ASI.invoice_id
3567 and asi.checkrun_id = p_checkrun_id
3568 and ibydocs.completed_pmts_group_id = p_completed_pmts_group_id
3569 and ibydocs.org_id = p_org_id
3570 and ibydocs.calling_app_doc_unique_ref1 = asi.checkrun_id
3571 AND ibydocs.calling_app_doc_unique_ref2 = asi.invoice_id
3572 AND ibydocs.calling_app_doc_unique_ref3 = asi.payment_num;
3573
3574
3575
3576 l_awt_success VARCHAR2(2000);
3577 DBG_Loc VARCHAR2(30) := 'Ap_Withhold_Cancel';
3578 current_calling_sequence VARCHAR2(2000);
3579 debug_info VARCHAR2(1000);
3580 BEGIN
3581 current_calling_sequence := 'AP_WITHHOLDING_PKG.AP_Withhold_Cancel';
3582 debug_info := 'Open Cursor for all selected invoices';
3583 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3584 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
3585 END IF;
3586
3587 if p_completed_pmts_group_id is null then
3588 OPEN c_all_sel_invs (P_Checkrun_Name, p_checkrun_id);
3589 else
3590 OPEN C_SEL_INVS;
3591 end if;
3592
3593
3594 LOOP
3595 debug_info := 'Fetch CURSOR for all SELECTed invoices -- invoice_id = '||to_char(rec_all_sel_invs.invoice_id);
3596 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3597 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
3598 END IF;
3599
3600
3601 if p_completed_pmts_group_id is null then
3602 FETCH c_all_sel_invs INTO rec_all_sel_invs;
3603 EXIT WHEN c_all_sel_invs%NOTFOUND;
3604 else
3605 fetch c_sel_invs into rec_all_sel_invs;
3606 exit when c_sel_invs%notfound;
3607 end if;
3608
3609
3610 Ap_Undo_Temp_Withholding
3611 (P_Invoice_Id => rec_all_sel_invs.invoice_id
3612 ,P_VENDor_Id => rec_all_sel_invs.vendor_id
3613 ,P_Payment_Num => rec_all_sel_invs.payment_num
3614 ,P_Checkrun_Name => P_Checkrun_Name
3615 ,P_Undo_Awt_Date => SYSDATE
3616 ,P_Calling_Module => 'CANCEL'
3617 ,P_Last_Updated_By => P_Last_Updated_By
3618 ,P_Last_Update_Login => P_Last_Update_Login
3619 ,P_Program_Application_Id => P_Program_Application_Id
3620 ,P_Program_Id => P_Program_Id
3621 ,P_Request_Id => P_Request_Id
3622 ,P_Awt_Success => l_awt_success
3623 ,P_checkrun_id => p_checkrun_id);
3624 END LOOP;
3625
3626 debug_info := 'CLOSE CURSOR for all SELECTed invoices';
3627 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3628 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
3629 END IF;
3630
3631 if p_completed_pmts_group_id is null then
3632 CLOSE c_all_sel_invs;
3633 else
3634 CLOSE c_sel_invs;
3635 end if;
3636
3637
3638 EXCEPTION
3639 WHEN OTHERS THEN
3640 IF (SQLCODE <> -20001) THEN
3641 FND_MESSAGE.set_NAME('SQLAP','AP_DEBUG');
3642 FND_MESSAGE.set_TOKEN('ERROR',SQLERRM);
3643 FND_MESSAGE.set_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
3644 FND_MESSAGE.set_TOKEN('PARAMETERS',
3645 ' Checkrun Name = ' || P_Checkrun_Name ||
3646 ', Program_Id = ' || to_char(P_Program_Id) ||
3647 ', Request_Id = ' || to_char(P_Request_Id));
3648 FND_MESSAGE.set_TOKEN('DEBUG_INFO',debug_info);
3649 END IF;
3650
3651 /* Start - BUG 14364091 - Logging */
3652 FND_FILE.PUT_LINE(FND_FILE.LOG, 'ERROR Code : ' || SQLCODE);
3653 FND_FILE.PUT_LINE(FND_FILE.LOG, 'ERROR Msg : ' || SQLERRM);
3654 FND_FILE.PUT_LINE(FND_FILE.LOG, debug_info||' - '||systimestamp);
3655 /* End - BUG 14364091 - Logging */
3656
3657 APP_EXCEPTION.RAISE_EXCEPTION;
3658
3659 END Ap_Withhold_Cancel;
3660
3661
3662 PROCEDURE Ap_Undo_Temp_Withholding (
3663 P_Invoice_Id IN NUMBER,
3664 P_Vendor_Id IN NUMBER DEFAULT NULL,
3665 P_Payment_Num IN NUMBER,
3666 P_Checkrun_Name IN VARCHAR2,
3667 P_Undo_Awt_Date IN DATE,
3668 P_Calling_Module IN VARCHAR2,
3669 P_Last_Updated_By IN NUMBER,
3670 P_Last_Update_Login IN NUMBER,
3671 P_Program_Application_Id IN NUMBER DEFAULT NULL,
3672 P_Program_Id IN NUMBER DEFAULT NULL,
3673 P_Request_Id IN NUMBER DEFAULT NULL,
3674 P_Awt_Success OUT NOCOPY VARCHAR2,
3675 P_checkrun_id in number default null)
3676 IS
3677 DBG_Loc VARCHAR2(30) := 'Ap_Undo_Temp_Withholding';
3678 current_calling_sequence VARCHAR2(2000);
3679 debug_info VARCHAR2(100);
3680 l_withholding_amount NUMBER;
3681 l_proposed_payment_amount NUMBER; --Added for Bug#8281225
3682 /* Start - BUG 14364091 - Logging */
3683 l_api_name varchar2(30);
3684 /* End - BUG 14364091 - Logging */
3685
3686 BEGIN
3687 /* Start - BUG 14364091 - Logging */
3688 l_api_name := 'Ap_Undo_Temp_Withholding';
3689 /* End - BUG 14364091 - Logging */
3690
3691 current_calling_sequence := 'AP_WITHHOLDING_PKG.AP_Undo_Temp_Withholding';
3692
3693 P_AWT_Success := 'SUCCESS';
3694
3695 --bug13606808
3696 IF (P_Calling_Module in ('AUTOSELECT', 'QUICKCHECK')) THEN
3697 Ap_Undo_Orphan_Distributions
3698 (P_Invoice_Id => P_Invoice_Id
3699 ,P_VENDor_Id => P_Vendor_Id
3700 ,P_Payment_Num => P_Payment_Num
3701 ,P_Checkrun_Name => P_Checkrun_Name
3702 ,P_Undo_Awt_Date => P_Undo_Awt_Date
3703 ,P_Calling_Module => P_Calling_Module
3704 ,P_Last_Updated_By => P_Last_Updated_By
3705 ,P_Last_Update_Login => P_Last_Update_Login
3706 ,P_Program_Application_Id => P_Program_Application_Id
3707 ,P_Program_Id => P_Program_Id
3708 ,P_Request_Id => P_Request_Id
3709 ,P_Awt_Success => P_Awt_Success
3710 ,P_checkrun_id => P_checkrun_id);
3711 END IF;
3712
3713 IF (P_Calling_Module in ('AUTOSELECT', 'CANCEL', 'PROJECTED')) THEN
3714 <<Undo_During_AutoSELECT>>
3715 DECLARE
3716 CURSOR c_temp (InvId IN NUMBER
3717 ,PaymNum IN NUMBER
3718 ,CheckrunName in VARCHAR2
3719 ,Calling_Module in VARCHAR2
3720 ,checkrun_id in number) IS
3721 SELECT AATD.invoice_id
3722 , AATD.payment_num
3723 , AATD.group_id
3724 , AATD.tax_name
3725 , AATD.tax_code_combination_id
3726 , AATD.gross_amount
3727 , AATD.withholding_amount
3728 , AATD.base_withholding_amount
3729 , AATD.accounting_date
3730 , AATD.period_name
3731 , AATD.checkrun_name
3732 , AATD.tax_rate_id
3733 , TC.tax_id tax_code_id
3734 , aatd.checkrun_id
3735 FROM ap_awt_temp_distributions_all AATD,
3736 ap_invoices_all AI,
3737 ap_tax_codes_all TC
3738 WHERE AATD.invoice_id = InvId
3739 AND AATD.invoice_id = AI.invoice_id
3740 AND TC.name(+) = AATD.tax_name
3741 AND TC.tax_type = 'AWT' -- BUG 3665866
3742 AND NVL(TC.enabled_flag,'Y') = 'Y'
3743 AND NVL(AI.invoice_date,SYSDATE) BETWEEN
3744 NVL(TC.start_date, NVL(AI.invoice_date,SYSDATE)) AND
3745 NVL(TC.inactive_date, NVL(AI.invoice_date,SYSDATE))
3746 AND (((AATD.checkrun_name = NVL(CheckrunName, AATD.checkrun_name))
3747 AND (AATD.payment_num = NVL(PaymNum, AATD.payment_num))
3748 and (aatd.checkrun_id = nvl(checkrun_id, aatd.checkrun_id)))
3749 OR
3750 (AATD.checkrun_name IS NULL
3751 AND AATD.payment_num IS NULL
3752 and aatd.checkrun_id is null
3753 AND calling_module = 'PROJECTED'))
3754 AND TC.org_id = AI.org_id -- Bug 8772252
3755 FOR UPDATE of aatd.withholding_amount, aatd.base_withholding_amount; --tapan, added two columns to prevent deadlock on TC;
3756
3757 rec_temp c_temp%ROWTYPE;
3758
3759 --Bug12594549
3760
3761 CURSOR c_temp_gross (InvId IN NUMBER
3762 ,PaymNum IN NUMBER
3763 ,CheckrunName in VARCHAR2
3764 ,Calling_Module in VARCHAR2
3765 ,checkrun_id in number) IS
3766 SELECT distinct AATD.invoice_id
3767 , AATD.payment_num
3768 , AATD.group_id
3769 , AATD.tax_name
3770 , AATD.gross_amount
3771 , TC.tax_id tax_code_id
3772 , aatd.checkrun_id
3773 , AATD.accounting_date
3774 FROM ap_awt_temp_distributions_all AATD,
3775 ap_invoices_all AI,
3776 ap_tax_codes_all TC
3777 WHERE AATD.invoice_id = InvId
3778 AND AATD.invoice_id = AI.invoice_id
3779 AND TC.name(+) = AATD.tax_name
3780 AND TC.tax_type = 'AWT'
3781 AND NVL(TC.enabled_flag,'Y') = 'Y'
3782 AND NVL(AI.invoice_date,SYSDATE) BETWEEN
3783 NVL(TC.start_date, NVL(AI.invoice_date,SYSDATE)) AND
3784 NVL(TC.inactive_date, NVL(AI.invoice_date,SYSDATE))
3785 AND (((AATD.checkrun_name = NVL(CheckrunName, AATD.checkrun_name))
3786 AND (AATD.payment_num = NVL(PaymNum, AATD.payment_num))
3787 and (aatd.checkrun_id = nvl(checkrun_id, aatd.checkrun_id)))
3788 OR
3789 (AATD.checkrun_name IS NULL
3790 AND AATD.payment_num IS NULL
3791 and aatd.checkrun_id is null
3792 AND calling_module = 'PROJECTED'))
3793 AND TC.org_id = AI.org_id ;
3794
3795
3796 rec_temp_gross c_temp_gross%ROWTYPE;
3797
3798 --Bug12594549
3799 FUNCTION Period_Limit_ExISt_For_Tax (
3800 TaxId IN NUMBER,
3801 P_Calling_Sequence in VARCHAR2)
3802 RETURN BOOLEAN
3803 IS
3804 ret BOOLEAN;
3805
3806 CURSOR c_get_limit IS
3807 SELECT 'Limit ExISts'
3808 FROM ap_tax_codes_all
3809 WHERE tax_id = TaxId
3810 AND awt_period_type IS not NULL;
3811
3812 dummy CHAR(12);
3813 current_calling_sequence VARCHAR2(2000);
3814 debug_info VARCHAR2(100);
3815 BEGIN
3816 current_calling_sequence := 'AP_WITHHOLDING_PKG.Period_Limit_ExISt_For_Tax<-' ||
3817 P_Calling_Sequence;
3818 debug_info := 'OPEN CURSOR c_get_limit';
3819 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3820 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
3821 END IF;
3822
3823 OPEN c_get_limit;
3824
3825 debug_info := 'Fetch CURSOR c_get_limit';
3826 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3827 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
3828 END IF;
3829
3830 FETCH c_get_limit INTO dummy;
3831
3832 ret := c_get_limit%FOUND;
3833 debug_info := 'CLOSE CURSOR c_get_limit';
3834 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3835 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
3836 END IF;
3837
3838 CLOSE c_get_limit;
3839
3840 RETURN(ret);
3841 EXCEPTION
3842 WHEN OTHERS THEN
3843 IF (SQLCODE <> -20001) THEN
3844 FND_MESSAGE.set_NAME('SQLAP','AP_DEBUG');
3845 FND_MESSAGE.set_TOKEN('ERROR',SQLERRM);
3846 FND_MESSAGE.set_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
3847 FND_MESSAGE.set_TOKEN('PARAMETERS',
3848 'Tax Code Id = ' || TaxId);
3849
3850 FND_MESSAGE.set_TOKEN('DEBUG_INFO',debug_info);
3851 END IF;
3852 APP_EXCEPTION.RAISE_EXCEPTION;
3853
3854 END Period_Limit_ExISt_For_Tax;
3855
3856 BEGIN
3857 debug_info := 'OPEN CURSOR for AWT temp distributions';
3858 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3859 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
3860 END IF;
3861
3862
3863 --Bug12594549
3864 OPEN c_temp_gross (P_Invoice_Id, P_Payment_Num, P_Checkrun_Name, P_Calling_Module, p_checkrun_id);
3865 LOOP
3866 -- Read one temporary distribution line:
3867 debug_info := 'Fetch CURSOR for AWT gross amount';
3868 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3869 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
3870 END IF;
3871
3872 FETCH c_temp_gross INTO rec_temp_gross;
3873 EXIT WHEN c_temp_gross%NOTFOUND;
3874
3875
3876
3877 IF (P_Calling_Module in ('AUTOSELECT', 'CANCEL') AND
3878 Period_Limit_ExISt_For_Tax(rec_temp_gross.tax_code_id
3879 ,current_calling_sequence)) THEN
3880 DECLARE
3881 CURSOR c_get_awt_period IS
3882 SELECT p.period_name
3883 FROM ap_other_periods P,
3884 ap_tax_codes_all C
3885 WHERE (rec_temp_gross.accounting_date BETWEEN
3886 p.start_date AND p.end_date)
3887 AND p.period_type = c.awt_period_type
3888 AND c.name = rec_temp_gross.tax_name
3889 AND p.module = 'AWT';
3890
3891 awt_period ap_other_periods.period_name%TYPE;
3892 BEGIN
3893 debug_info := 'OPEN CURSOR c_get_awt_period';
3894 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3895 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
3896 END IF;
3897
3898 OPEN c_get_awt_period;
3899
3900 debug_info := 'Fetch CURSOR c_get_awt_period';
3901 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3902 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
3903 END IF;
3904
3905 FETCH c_get_awt_period INTO awt_period;
3906
3907 debug_info := 'CLOSE CURSOR c_get_awt_period';
3908 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3909 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
3910 END IF;
3911
3912 CLOSE c_get_awt_period;
3913
3914
3915
3916 UPDATE ap_awt_buckets_all
3917 SET gross_amount_to_date = gross_amount_to_date -
3918 NVL(rec_temp_gross.gross_amount,0)
3919 , last_UPDATE_date = SYSDATE
3920 , last_UPDATEd_by = P_Last_Updated_By
3921 , last_UPDATE_login = P_Last_Update_Login
3922 , program_UPDATE_date = SYSDATE
3923 , program_application_id = P_Program_Application_Id
3924 , program_id = P_Program_Id
3925 , request_id = P_Request_Id
3926 WHERE period_name = awt_period
3927 AND tax_name = rec_temp_gross.tax_name
3928 AND vendor_id = P_vendor_Id;
3929
3930 end;
3931 END IF;
3932 END LOOP ;
3933
3934 debug_info := 'CLOSE CURSOR c_temp_gross';
3935 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3936 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
3937 END IF;
3938
3939 CLOSE c_temp_gross;
3940
3941
3942 --Bug12594549
3943
3944 OPEN c_temp (P_Invoice_Id, P_Payment_Num, P_Checkrun_Name, P_Calling_Module, p_checkrun_id);
3945 <<For_Each_Temporary_dist>>
3946 LOOP
3947 -- Read one temporary distribution line:
3948 debug_info := 'Fetch CURSOR for AWT temp distributions';
3949 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3950 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
3951 END IF;
3952
3953 FETCH c_temp INTO rec_temp;
3954 EXIT WHEN c_temp%NOTFOUND;
3955
3956 -- Decrease corresponding bucket unless called FROM PROJECTED:
3957 -- (PROJECTED doesn't affect buckets)
3958
3959 IF (P_Calling_Module in ('AUTOSELECT', 'CANCEL') AND
3960 Period_Limit_ExISt_For_Tax(rec_temp.tax_code_id
3961 ,current_calling_sequence)) THEN
3962 DECLARE
3963 CURSOR c_get_awt_period IS
3964 SELECT p.period_name
3965 FROM ap_other_periods P,
3966 ap_tax_codes_all C
3967 WHERE (rec_temp.accounting_date BETWEEN
3968 p.start_date AND p.end_date)
3969 AND p.period_type = c.awt_period_type
3970 AND c.name = rec_temp.tax_name
3971 AND p.module = 'AWT';
3972
3973 awt_period ap_other_periods.period_name%TYPE;
3974 BEGIN
3975 debug_info := 'OPEN CURSOR c_get_awt_period';
3976 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3977 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
3978 END IF;
3979
3980 OPEN c_get_awt_period;
3981
3982 debug_info := 'Fetch CURSOR c_get_awt_period';
3983 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3984 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
3985 END IF;
3986
3987 FETCH c_get_awt_period INTO awt_period;
3988
3989 debug_info := 'CLOSE CURSOR c_get_awt_period';
3990 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3991 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
3992 END IF;
3993
3994 CLOSE c_get_awt_period;
3995
3996 debug_info := 'Update ap_awt_buckets';
3997 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3998 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
3999 END IF;
4000
4001 --Bug12594549 commented gross amount update
4002 UPDATE ap_awt_buckets_all
4003 /* SET gross_amount_to_date = gross_amount_to_date -
4004 NVL(rec_temp.gross_amount,0) */
4005 SET withheld_amount_to_date = withheld_amount_to_date -
4006 NVL(rec_temp.withholding_amount,0)
4007 , last_UPDATE_date = SYSDATE
4008 , last_UPDATEd_by = P_Last_Updated_By
4009 , last_UPDATE_login = P_Last_Update_Login
4010 , program_UPDATE_date = SYSDATE
4011 , program_application_id = P_Program_Application_Id
4012 , program_id = P_Program_Id
4013 , request_id = P_Request_Id
4014 WHERE period_name = awt_period
4015 AND tax_name = rec_temp.tax_name
4016 AND vendor_id = P_vendor_Id;
4017 END;
4018 END IF;
4019
4020 END LOOP For_Each_Temporary_dist;
4021
4022 debug_info := 'CLOSE CURSOR c_temp';
4023 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4024 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
4025 END IF;
4026
4027 CLOSE c_temp;
4028
4029 /* Bug 12433018 Moved updates to ap_selected_invoices_all and the delete
4030 outside the loop */
4031 IF (P_Calling_Module = 'AUTOSELECT') THEN
4032 debug_info := 'Update ap selected invoices';
4033 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4034 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
4035 END IF;
4036
4037 /* Note the withholding_amount in ap_selected_invoices_all is already in the
4038 payment currency. See ap_withhold_autoslect where it is updated.
4039 ap_undo_temp_withholding is also called at the beginning of the
4040 ap_withhold_autoselect procedure so it also has to properly handle the case
4041 where no withholding has been calculated yet. */
4042 UPDATE ap_selected_invoices_all si
4043 SET proposed_payment_amount = nvl(proposed_payment_amount,0) + NVL(withholding_amount,0)
4044 , payment_amount = nvl(proposed_payment_amount,0) + NVL(withholding_amount,0)
4045 , withholding_amount = 0
4046 WHERE checkrun_name = p_checkrun_name
4047 AND invoice_id = p_invoice_id
4048 AND payment_num = p_payment_num
4049 and checkrun_id = p_checkrun_id
4050 and exists
4051 (SELECT 1
4052 FROM ap_awt_temp_distributions_all atd
4053 WHERE atd.invoice_id = si.invoice_id
4054 AND atd.payment_num = si.payment_num
4055 AND atd.checkrun_id = si.checkrun_id);
4056
4057 END IF; /* calling mode equals AUTOSELECT */
4058
4059
4060
4061
4062
4063
4064
4065
4066
4067
4068
4069
4070
4071
4072
4073
4074 -- Drop that temporary line:
4075 debug_info := 'Delete the AWT temp distribution';
4076 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4077 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
4078 END IF;
4079
4080 DELETE ap_awt_temp_distributions_all
4081 WHERE invoice_id = p_invoice_id
4082 AND ( ( (checkrun_name = NVL(p_checkrun_name, checkrun_name))
4083 AND (payment_num = NVL(p_payment_num, payment_num))
4084 and (checkrun_id = nvl(p_checkrun_id,checkrun_id)))
4085 OR
4086 ( checkrun_name IS NULL
4087 and checkrun_id is NULL
4088 AND payment_num IS NULL
4089 AND P_calling_module = 'PROJECTED'));
4090
4091
4092
4093
4094
4095
4096
4097
4098 END Undo_During_AutoSELECT;
4099 END IF;
4100
4101 -- Execute the ExtENDed Withholding Reversion (IF active)
4102 --
4103 IF (Ap_ExtENDed_Withholding_Pkg.Ap_ExtENDed_Withholding_Active) THEN
4104 Ap_ExtENDed_Withholding_Pkg.Ap_Undo_Temp_Ext_Withholding
4105 (P_Invoice_Id,
4106 P_VENDor_Id,
4107 P_Payment_Num,
4108 P_Checkrun_Name,
4109 P_Undo_Awt_Date,
4110 P_Calling_Module,
4111 P_Last_Updated_By,
4112 P_Last_Update_Login,
4113 P_Program_Application_Id,
4114 P_Program_Id,
4115 P_Request_Id,
4116 P_Awt_Success,
4117 p_checkrun_id);
4118 END IF;
4119
4120 EXCEPTION
4121 WHEN OTHERS THEN
4122 DECLARE
4123 error_text VARCHAR2(512) := substr(sqlerrm, 1, 512);
4124 BEGIN
4125 P_Awt_Success := error_text;
4126 IF (SQLCODE <> -20001) THEN
4127 FND_MESSAGE.set_NAME('SQLAP','AP_DEBUG');
4128 FND_MESSAGE.set_TOKEN('ERROR',SQLERRM);
4129 FND_MESSAGE.set_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
4130 FND_MESSAGE.set_TOKEN('PARAMETERS',
4131 ', Invoice_Id = ' || to_char(P_Invoice_Id) ||
4132 ', VENDor_Id = ' || to_char(P_VENDor_Id) ||
4133 ', Payment_Num = ' || to_char(P_Payment_Num) ||
4134 ', Checkrun_Name = ' || P_Checkrun_Name ||
4135 ' Undo_Awt_Date = ' || to_char(P_Undo_Awt_Date));
4136
4137 FND_MESSAGE.set_TOKEN('DEBUG_INFO',debug_info);
4138 END IF;
4139
4140 /* Start - BUG 14364091 - Logging */
4141 FND_FILE.PUT_LINE(FND_FILE.LOG, 'ERROR Code : ' || SQLCODE);
4142 FND_FILE.PUT_LINE(FND_FILE.LOG, 'ERROR Msg : ' || SQLERRM);
4143 FND_FILE.PUT_LINE(FND_FILE.LOG, G_MODULE_NAME || '.' || l_api_name || ' - ' || debug_info ||' - '||systimestamp);
4144 /* End - BUG 14364091 - Logging */
4145
4146 APP_EXCEPTION.RAISE_EXCEPTION;
4147 END;
4148 END Ap_Undo_Temp_Withholding;
4149
4150
4151 PROCEDURE Ap_Undo_Withholding (
4152 P_Parent_Id IN NUMBER,
4153 P_Calling_Module IN VARCHAR2,
4154 P_Awt_Date IN DATE,
4155 P_New_Invoice_Payment_Id IN NUMBER DEFAULT NULL,
4156 P_Last_Updated_By IN NUMBER,
4157 P_Last_Update_Login IN NUMBER,
4158 P_Program_Application_Id IN NUMBER DEFAULT NULL,
4159 P_Program_Id IN NUMBER DEFAULT NULL,
4160 P_Request_Id IN NUMBER DEFAULT NULL,
4161 P_Awt_Success OUT NOCOPY VARCHAR2,
4162 P_Inv_Line_No IN NUMBER DEFAULT NULL,
4163 P_dist_Line_No IN NUMBER DEFAULT NULL,
4164 P_New_Invoice_Id IN NUMBER DEFAULT NULL,
4165 P_New_dist_Line_No IN NUMBER DEFAULT NULL)
4166 IS
4167 /*
4168
4169 Copyright (c) 1995 by Oracle Corporation
4170
4171 NAME
4172 Ap_Undo_Withholding
4173 DESCRIPTION
4174 Reverses AWT distribution lines, buckets, tax authority invoices
4175 for a full invoice or for a payment depENDing upon the calling module
4176 NOTES
4177 ThIS PROCEDURE IS part of the AP_WITHHOLDING_PKG PL/SQL package
4178 HISTORY (YY/MM/DD)
4179 atassoni.it 95/07/14 Creation
4180 mhtaylor 95/08/21 Adapted for Adjust distributions
4181
4182 << Beginning of Undo_Awt_By_Invoice_Payment program documentation >>
4183
4184 Flow of thIS PROCEDURE:
4185
4186 *---------------------------*
4187 | BEGIN Ap_Undo_Withholding |
4188 *---------------------------*
4189 |
4190 v
4191 *---------------------------------------------------*
4192 | Get one AWT distribution line for current invoice | <------------------+
4193 | or invoice payment | |
4194 *---------------------------------------------------* |
4195 | |
4196 v |
4197 *------------------------------------------------------* |
4198 | Get line accounting DATE AND corresponding WT period | |
4199 *------------------------------------------------------* |
4200 | |
4201 v |
4202 *-----------------------------------* |
4203 | Reverse the AWT distribution line | |
4204 *-----------------------------------* |
4205 | |
4206 v |
4207 *--------------------------------------------* |
4208 | Adjust invoice amount AND payment schedule | |
4209 *--------------------------------------------* |
4210 | |
4211 *--------------------------------------------* |
4212 | Decrease corresponding bucket, IF exISting | |
4213 *--------------------------------------------* |
4214 | |
4215 +--> An invoice to a tax authority exISts? |
4216 ,'`. |
4217 *-----------------------------* Yes ,' `. |
4218 | Reverse that invoice: | <---------- <End Loop> |
4219 | ~~~~~~~~~~~~~~~~~~~~ | `. ,' |
4220 | - Reverse invoice line | `.,' |
4221 | - Reverse distribution line | No | |
4222 | - Reverse payment schedules | | |
4223 *-----------------------------* | |
4224 | | |
4225 +<--------------------------+ |
4226 | |
4227 v |
4228 ,'`. |
4229 ,' `. No |
4230 <End Loop> ---------------------------------------------+
4231 `. ,'
4232 `.,'
4233 Yes |
4234 v
4235 *-------------------------*
4236 | END Ap_Undo_Withholding |
4237 *-------------------------*
4238
4239
4240 << End of Ap_Undo_Withholding program documentation >>
4241
4242 */
4243
4244 -- PL/SQL Main Block Constants AND Variables:
4245
4246 awt_period ap_other_periods.period_name%TYPE;
4247 gl_period_name ap_invoice_distributions.period_name%TYPE;
4248 gl_awt_date DATE;
4249 DBG_Loc VARCHAR2(30) := 'Ap_Undo_Withholding';
4250 current_calling_sequence VARCHAR2(2000);
4251 debug_info VARCHAR2(100);
4252 l_org_id NUMBER; /* Bug 4759178, added org_id */
4253
4254 -- PL/SQL Main Block Exceptions:
4255
4256 INVALID_CALLING_MODULE exception;
4257 NOT_AN_OPEN_GL_PERIOD exception;
4258
4259 -- PL/SQL Main Block Tables:
4260
4261 -- PL/SQL Main Block CURSORs AND records:
4262
4263 CURSOR c_awt_dists_inv (ParentId IN NUMBER)
4264 IS
4265 SELECT AID.accounting_date
4266 , AID.accrual_posted_flag
4267 , AID.assets_addition_flag
4268 , AID.assets_tracking_flag
4269 , AID.cash_posted_flag
4270 , AID.invoice_line_number
4271 , AID.distribution_line_number
4272 , AID.dist_code_combination_id
4273 , AID.invoice_id
4274 , AID.last_UPDATEd_by
4275 , AID.last_UPDATE_date
4276 , AID.line_type_lookup_code
4277 , AID.period_name
4278 , AID.set_of_books_id
4279 , AID.accts_pay_code_combination_id
4280 , AID.amount
4281 , AID.base_amount
4282 , AID.base_invoice_price_variance
4283 , AID.batch_id
4284 , AID.created_by
4285 , AID.creation_date
4286 , AID.description
4287 , AID.exchange_rate_variance
4288 , AID.final_match_flag
4289 , AID.income_tax_region
4290 , AID.invoice_price_variance
4291 , AID.last_UPDATE_login
4292 , AID.match_status_flag
4293 , AID.posted_flag
4294 , AID.po_distribution_id
4295 , AID.program_application_id
4296 , AID.program_id
4297 , AID.program_UPDATE_date
4298 , AID.quantity_invoiced
4299 , AID.rate_var_code_combination_id
4300 , AID.request_id
4301 , AID.reversal_flag
4302 , AID.type_1099
4303 , AID.unit_price
4304 , AID.withholding_tax_code_id /* Bug 5382525 */
4305 , TC.name vat_code
4306 , AID.amount_encumbered
4307 , AID.base_amount_encumbered
4308 , AID.encumbered_flag
4309 , AID.price_adjustment_flag
4310 , AID.price_var_code_combination_id
4311 , AID.quantity_unencumbered
4312 , AID.stat_amount
4313 , AID.amount_to_post
4314 , AID.attribute1
4315 , AID.attribute10
4316 , AID.attribute11
4317 , AID.attribute12
4318 , AID.attribute13
4319 , AID.attribute14
4320 , AID.attribute15
4321 , AID.attribute2
4322 , AID.attribute3
4323 , AID.attribute4
4324 , AID.attribute5
4325 , AID.attribute6
4326 , AID.attribute7
4327 , AID.attribute8
4328 , AID.attribute9
4329 , AID.attribute_category
4330 , AID.base_amount_to_post
4331 , AID.cash_je_batch_id
4332 , AID.expenditure_item_date
4333 , AID.expenditure_organization_Id
4334 , AID.expenditure_type
4335 , AID.je_batch_id
4336 , AID.parent_invoice_id
4337 , AID.pa_addition_flag
4338 , AID.pa_quantity
4339 , AID.posted_amount
4340 , AID.posted_base_amount
4341 , AID.prepay_amount_remaining
4342 , AID.project_accounting_context
4343 , AID.project_id
4344 , AID.task_id
4345 --, AID.ussgl_transaction_code - Bug 4277744
4346 --, AID.ussgl_trx_code_context - Bug 4277744
4347 , AID.earliest_settlement_date
4348 , AID.req_distribution_id
4349 , AID.quantity_variance
4350 , AID.base_quantity_variance
4351 , AID.packet_id
4352 , AID.awt_flag
4353 , AID.awt_group_id
4354 , AID.awt_tax_rate_id
4355 , AID.awt_gross_amount
4356 , AID.awt_invoice_id
4357 , AID.awt_origin_group_id
4358 , AID.reference_1
4359 , AID.reference_2
4360 , AID.org_id
4361 , AID.other_invoice_id
4362 , AID.awt_invoice_payment_id
4363 , AID.invoice_distribution_id
4364 , AID.awt_related_id
4365 /* Start of fix for bug#8462050*/
4366 , AID.global_attribute_category
4367 , AID.global_attribute1
4368 , AID.global_attribute2
4369 , AID.global_attribute3
4370 , AID.global_attribute4
4371 , AID.global_attribute5
4372 , AID.global_attribute6
4373 , AID.global_attribute7
4374 , AID.global_attribute8
4375 , AID.global_attribute9
4376 , AID.global_attribute10
4377 , AID.global_attribute11
4378 , AID.global_attribute12
4379 , AID.global_attribute13
4380 , AID.global_attribute14
4381 , AID.global_attribute15
4382 , AID.global_attribute16
4383 , AID.global_attribute17
4384 , AID.global_attribute18
4385 , AID.global_attribute19
4386 , AID.global_attribute20
4387 /* End of fix for bug#8462050*/
4388 FROM ap_invoice_distributions AID,
4389 ap_tax_codes TC
4390 --,ap_invoices AI --Bug8547506
4391 WHERE AID.invoice_id = ParentId
4392 AND TC.tax_id (+) = AID.withholding_tax_code_id /* Bug 5382525 */
4393 --Bug8547506 Undoing changes done for bug6660355
4394 --AND AID.invoice_id = AI.invoice_id --6660355
4395 --AND AID.awt_origin_group_id = AI.awt_group_id
4396 AND AID.invoice_line_number = NVL(P_Inv_Line_No,
4397 AID.invoice_line_number)
4398 AND AID.distribution_line_number = NVL(P_dist_Line_No,
4399 AID.distribution_line_number)
4400 AND NVL(AID.reversal_flag, 'N') <> 'Y' -- bug 7606072
4401 AND NVL(AID.awt_flag, 'M') = 'A';
4402
4403 -- only auto-generated AWT lines are to be considered
4404
4405 CURSOR c_awt_dists_pay (ParentId IN NUMBER) IS
4406 SELECT AID.accounting_date
4407 , AID.accrual_posted_flag
4408 , AID.assets_addition_flag
4409 , AID.assets_tracking_flag
4410 , AID.cash_posted_flag
4411 , AID.invoice_line_number
4412 , AID.distribution_line_number
4413 , AID.dist_code_combination_id
4414 , AID.invoice_id
4415 , AID.last_UPDATEd_by
4416 , AID.last_UPDATE_date
4417 , AID.line_type_lookup_code
4418 , AID.period_name
4419 , AID.set_of_books_id
4420 , AID.accts_pay_code_combination_id
4421 , AID.amount
4422 , AID.base_amount
4423 , AID.base_invoice_price_variance
4424 , AID.batch_id
4425 , AID.created_by
4426 , AID.creation_date
4427 , AID.description
4428 , AID.exchange_rate_variance
4429 , AID.final_match_flag
4430 , AID.income_tax_region
4431 , AID.invoice_price_variance
4432 , AID.last_UPDATE_login
4433 , AID.match_status_flag
4434 , AID.posted_flag
4435 , AID.po_distribution_id
4436 , AID.program_application_id
4437 , AID.program_id
4438 , AID.program_UPDATE_date
4439 , AID.quantity_invoiced
4440 , AID.rate_var_code_combination_id
4441 , AID.request_id
4442 , AID.reversal_flag
4443 , AID.type_1099
4444 , AID.unit_price
4445 , AID.withholding_tax_code_id /* Bug 5382525 */
4446 , TC.name vat_code
4447 , AID.amount_encumbered
4448 , AID.base_amount_encumbered
4449 , AID.encumbered_flag
4450 , AID.price_adjustment_flag
4451 , AID.price_var_code_combination_id
4452 , AID.quantity_unencumbered
4453 , AID.stat_amount
4454 , AID.amount_to_post
4455 , AID.attribute1
4456 , AID.attribute10
4457 , AID.attribute11
4458 , AID.attribute12
4459 , AID.attribute13
4460 , AID.attribute14
4461 , AID.attribute15
4462 , AID.attribute2
4463 , AID.attribute3
4464 , AID.attribute4
4465 , AID.attribute5
4466 , AID.attribute6
4467 , AID.attribute7
4468 , AID.attribute8
4469 , AID.attribute9
4470 , AID.attribute_category
4471 , AID.base_amount_to_post
4472 , AID.cash_je_batch_id
4473 , AID.expenditure_item_date
4474 , AID.expenditure_organization_Id
4475 , AID.expenditure_type
4476 , AID.je_batch_id
4477 , AID.parent_invoice_id
4478 , AID.pa_addition_flag
4479 , AID.pa_quantity
4480 , AID.posted_amount
4481 , AID.posted_base_amount
4482 , AID.prepay_amount_remaining
4483 , AID.project_accounting_context
4484 , AID.project_id
4485 , AID.task_id
4486 --, AID.ussgl_transaction_code - Bug 4277744
4487 --, AID.ussgl_trx_code_context - Bug 4277744
4488 , AID.earliest_settlement_date
4489 , AID.req_distribution_id
4490 , AID.quantity_variance
4491 , AID.base_quantity_variance
4492 , AID.packet_id
4493 , AID.awt_flag
4494 , AID.awt_group_id
4495 , AID.awt_tax_rate_id
4496 , AID.awt_gross_amount
4497 , AID.awt_invoice_id
4498 , AID.awt_origin_group_id
4499 , AID.reference_1
4500 , AID.reference_2
4501 , AID.org_id
4502 , AID.other_invoice_id
4503 , AID.awt_invoice_payment_id
4504 , AID.invoice_distribution_id
4505 , awt_related_id
4506 /* Start of fix for bug#8462050*/
4507 , AID.global_attribute_category
4508 , AID.global_attribute1
4509 , AID.global_attribute2
4510 , AID.global_attribute3
4511 , AID.global_attribute4
4512 , AID.global_attribute5
4513 , AID.global_attribute6
4514 , AID.global_attribute7
4515 , AID.global_attribute8
4516 , AID.global_attribute9
4517 , AID.global_attribute10
4518 , AID.global_attribute11
4519 , AID.global_attribute12
4520 , AID.global_attribute13
4521 , AID.global_attribute14
4522 , AID.global_attribute15
4523 , AID.global_attribute16
4524 , AID.global_attribute17
4525 , AID.global_attribute18
4526 , AID.global_attribute19
4527 , AID.global_attribute20
4528 /* End of fix for bug#8462050*/
4529 FROM ap_invoice_distributions AID,
4530 ap_tax_codes TC
4531 WHERE AID.awt_invoice_payment_id = ParentId
4532 AND TC.tax_id(+) = AID.withholding_tax_code_id /* 5382525 */
4533 AND AID.invoice_line_number = NVL(P_Inv_Line_No,
4534 AID.invoice_line_number)
4535 AND AID.distribution_line_number = NVL(P_dist_Line_No,
4536 AID.distribution_line_number)
4537 AND NVL(AID.awt_flag, 'M') = 'A';
4538
4539 -- only auto-generated AWT lines are to be considered
4540
4541 rec_awt_dists c_awt_dists_pay%ROWTYPE;
4542
4543 --Bug12594549
4544 CURSOR c_awt_gross_dists_pay (ParentId IN NUMBER) IS
4545 SELECT DISTINCT
4546 AID.accounting_date
4547 , AID.invoice_line_number
4548 , AID.invoice_id
4549 , AID.period_name
4550 , AID.set_of_books_id
4551 , AID.withholding_tax_code_id /* Bug 5382525 */
4552 , TC.name vat_code
4553 , AID.awt_group_id
4554 , AID.awt_tax_rate_id
4555 , AID.awt_invoice_payment_id
4556 , AID.awt_gross_amount
4557
4558 /* End of fix for bug#8462050*/
4559 FROM ap_invoice_distributions AID,
4560 ap_tax_codes TC
4561 WHERE AID.awt_invoice_payment_id = ParentId
4562 AND TC.tax_id(+) = AID.withholding_tax_code_id /* 5382525 */
4563 AND NVL(AID.awt_flag, 'M') = 'A';
4564
4565 -- only auto-generated AWT lines are to be considered
4566 CURSOR c_awt_gross_dists_inv (ParentId IN NUMBER) IS
4567 SELECT DISTINCT
4568 AID.accounting_date
4569 , AID.invoice_line_number
4570 , AID.invoice_id
4571 , AID.period_name
4572 , AID.set_of_books_id
4573 , AID.withholding_tax_code_id /* Bug 5382525 */
4574 , TC.name vat_code
4575 , AID.awt_group_id
4576 , AID.awt_tax_rate_id
4577 , AID.awt_invoice_payment_id
4578 , AID.awt_gross_amount
4579 /* End of fix for bug#8462050*/
4580 FROM ap_invoice_distributions AID,
4581 ap_tax_codes TC
4582 --,ap_invoices AI --Bug8547506
4583 WHERE AID.invoice_id = ParentId
4584 AND TC.tax_id (+) = AID.withholding_tax_code_id
4585 AND AID.invoice_line_number = NVL(P_Inv_Line_No,
4586 AID.invoice_line_number)
4587 AND NVL(AID.reversal_flag, 'N') <> 'Y' -- bug 7606072
4588 AND NVL(AID.awt_flag, 'M') = 'A';
4589
4590 rec_awt_gross_dists c_awt_gross_dists_pay%ROWTYPE;
4591 --Bug12594549
4592
4593 l_invoice_exchange_rate ap_invoices.exchange_rate%type;
4594 l_func_currency_code ap_system_parameters.base_currency_code%TYPE;
4595 l_old_inv_line_num ap_invoice_lines_all.line_number%TYPE;
4596
4597 l_pay_awt_invs_count NUMBER; /* Bug 10353952 */
4598
4599 -- Ap_Undo_Withholding:
4600 -- PL/SQL Main Block PROCEDUREs AND functions:
4601
4602 -- _______
4603 -- | |
4604 -- | |
4605 -- | |
4606 -- _________| |_________
4607 -- \ /
4608 -- \ Ap_Undo_Withholding /
4609 -- \ /
4610 -- \ _____ /
4611 -- \ | | /
4612 -- \ | | /
4613 -- \___| |___/
4614 -- \ /
4615 -- \ BEGIN /
4616 -- \ /
4617 -- \ /
4618 -- \ /
4619 -- \ /
4620 -- v
4621
4622 BEGIN
4623 current_calling_sequence := 'AP_WITHHOLDING_PKG.AP_Undo_Withholding';
4624 P_Awt_Success := 'SUCCESS'; -- Assumes successfully completion
4625
4626 IF ( (P_Calling_Module NOT IN
4627 ('VOID PAYMENT', 'CANCEL INVOICE', 'REVERSE DIST'))
4628 OR
4629 (P_Calling_Module IS NULL)) THEN
4630 RAISE INVALID_CALLING_MODULE;
4631 END IF;
4632
4633 SAVEPOINT BEFORE_UNDO_WITHHOLDING;
4634 /* Bug 4759178, get org_id */
4635 debug_info := 'Select Org Id';
4636 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4637 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
4638 END IF;
4639
4640 IF (P_Calling_Module in ('CANCEL INVOICE','REVERSE DIST')) THEN
4641 SELECT AI.org_id
4642 INTO l_org_id
4643 FROM AP_INVOICES_ALL AI
4644 WHERE invoice_id = P_Parent_Id;
4645
4646 ELSIF (P_Calling_Module = 'VOID PAYMENT') THEN
4647 SELECT AIP.org_id
4648 INTO l_org_id
4649 FROM AP_INVOICE_PAYMENTS_ALL AIP
4650 WHERE AIP.invoice_payment_id = P_Parent_Id;
4651
4652 END IF;
4653
4654 debug_info := 'Select GL Period Name';
4655 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4656 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
4657 END IF;
4658
4659 BEGIN
4660 SELECT GPS.period_name,
4661 P_Awt_Date
4662 INTO gl_period_name,
4663 gl_awt_date
4664 FROM gl_period_statuses GPS,
4665 ap_system_parameters_all ASP
4666 WHERE GPS.application_id = 200
4667 AND GPS.set_of_books_id = ASP.set_of_books_id
4668 AND P_Awt_Date BETWEEN GPS.start_date AND GPS.END_date
4669 AND GPS.closing_status IN ('O', 'F')
4670 AND NVL(gps.ADJUSTMENT_PERIOD_FLAG,'N') = 'N'
4671 AND ASP.org_id = l_org_id; /* Bug 4759178, added org_id condition*/
4672
4673 EXCEPTION
4674 WHEN NO_DATA_FOUND THEN
4675 ap_utilities_pkg.get_open_gl_date(P_Awt_Date, gl_period_name, gl_awt_date);
4676 IF gl_awt_date IS NULL THEN
4677 RAISE NOT_AN_OPEN_GL_PERIOD;
4678 END IF;
4679 END;
4680
4681 --Bug12594549
4682
4683 <<Process_awt_gross_dists>>
4684 DECLARE
4685 DBG_Loc VARCHAR2(30) := 'Process_Withholding_gross';
4686 BEGIN
4687 debug_info := 'OPEN CURSOR c_awt_gross_dists';
4688 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4689 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
4690 END IF;
4691
4692 IF (P_Calling_Module in ('CANCEL INVOICE','REVERSE DIST')) THEN
4693 OPEN c_awt_gross_dists_inv (P_Parent_Id);
4694 NULL;
4695 ELSIF (P_Calling_Module = 'VOID PAYMENT') THEN
4696 OPEN c_awt_gross_dists_pay (P_Parent_Id);
4697 END IF;
4698
4699
4700 <<For_Each_awt_gross_Line>>
4701 LOOP
4702 debug_info := 'Fetch CURSOR c_get_awt_period';
4703 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4704 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
4705 END IF;
4706
4707 IF (P_Calling_Module in ('CANCEL INVOICE','REVERSE DIST')) THEN
4708 FETCH c_awt_gross_dists_inv INTO rec_awt_gross_dists;
4709 EXIT WHEN c_awt_gross_dists_inv%NOTFOUND;
4710 ELSIF (P_Calling_Module = 'VOID PAYMENT') THEN
4711 FETCH c_awt_gross_dists_pay INTO rec_awt_gross_dists;
4712 EXIT WHEN c_awt_gross_dists_pay%NOTFOUND;
4713 END IF;
4714 --
4715 <<Get_Withholding_Period>>
4716 DECLARE
4717 DBG_Loc VARCHAR2(30) := 'Get_Withholding_Period';
4718 msg VARCHAR2(240);
4719 CURSOR c_get_period (distDate IN DATE, TaxId IN NUMBER) IS
4720 SELECT period_name
4721 FROM ap_other_periods P,
4722 ap_tax_codes T
4723 WHERE t.tax_id = TaxId
4724 AND p.period_type = t.awt_period_type
4725 AND p.application_id = 200
4726 AND p.module = 'AWT'
4727 AND p.start_date <= TRUNC(distDate)
4728 AND p.end_date >= TRUNC(distDate);
4729 BEGIN
4730 debug_info := 'OPEN CURSOR c_get_period';
4731 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4732 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
4733 END IF;
4734
4735 OPEN c_get_period (rec_awt_gross_dists.accounting_date
4736 ,rec_awt_gross_dists.withholding_tax_code_id);
4737 debug_info := 'Fetch CURSOR c_get_period';
4738 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4739 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
4740 END IF;
4741
4742 FETCH c_get_period INTO awt_period;
4743
4744 IF c_get_period%FOUND THEN
4745 msg := 'AWT period '||awt_period||' found for tax id '||
4746 rec_awt_gross_dists.withholding_tax_code_id;
4747 ELSE
4748 msg := 'No AWT period found for tax id '||rec_awt_gross_dists.withholding_tax_code_id;
4749 END IF;
4750
4751 debug_info := 'CLOSE CURSOR c_get_period';
4752 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4753 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
4754 END IF;
4755
4756 CLOSE c_get_period;
4757
4758 END Get_Withholding_Period;
4759
4760
4761
4762 <<Get_Invoice_info>>
4763 DECLARE
4764
4765 CURSOR c_invoice (InvId IN NUMBER) IS
4766 SELECT vendor_id
4767 , set_of_books_id
4768 , exchange_date
4769 , exchange_rate
4770 FROM ap_invoices
4771 WHERE invoice_id = InvId
4772 FOR UPDATE;
4773 rec_invoice c_invoice%ROWTYPE;
4774
4775 BEGIN
4776
4777 OPEN c_invoice (rec_awt_gross_dists.invoice_id);
4778
4779 debug_info := 'Fetch CURSOR c_invoice';
4780 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4781 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
4782 END IF;
4783
4784 FETCH c_invoice INTO rec_invoice;
4785
4786 <<Update_Bucket>>
4787 DECLARE
4788 CURSOR c_awt_bucket (VendorId IN NUMBER,
4789 Period IN VARCHAR2,
4790 TaxCode IN VARCHAR2) IS
4791 SELECT gross_amount_to_date,
4792 withheld_amount_to_date
4793 FROM ap_awt_buckets
4794 WHERE vendor_id = VendorId
4795 AND period_name = Period
4796 AND tax_name = TaxCode
4797 FOR UPDATE;
4798
4799 gross_amt_to_date ap_awt_buckets.gross_amount_to_date%TYPE;
4800 withheld_amt_to_date ap_awt_buckets.withheld_amount_to_date%TYPE;
4801 r_w_amount_to_date NUMBER := NULL; --Added for Bug#12594549
4802 r_gross_amount_to_date NUMBER := NULL; --Added for Bug#12594549
4803 DBG_Loc VARCHAR2(30) := 'Update_Bucket';
4804 NOTHING_TO_DO exception;
4805 BEGIN
4806 IF awt_period IS NULL THEN
4807 raISe NOTHING_TO_DO;
4808 END IF;
4809
4810 debug_info := ' Fetching the functional currency AND exchange rate ' ;
4811 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4812 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
4813 END IF;
4814
4815 SELECT base_currency_code
4816 INTO l_func_currency_code
4817 FROM ap_system_parameters
4818 WHERE org_id = l_org_id;
4819
4820 IF (P_Calling_Module in ('CANCEL INVOICE','REVERSE DIST')) THEN
4821 l_invoice_exchange_rate := rec_invoice.exchange_rate;
4822 ELSIF (P_Calling_Module = 'VOID PAYMENT') THEN
4823
4824 SELECT ai.exchange_rate
4825 INTO l_invoice_exchange_rate
4826 FROM ap_invoices ai, ap_invoice_payments aip
4827 WHERE ai.invoice_id = aip.invoice_id
4828 AND aip.invoice_payment_id =rec_awt_gross_dists.awt_invoice_payment_id;
4829
4830 END IF;
4831
4832 debug_info := 'OPEN CURSOR c_awt_bucket';
4833 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4834 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
4835 END IF;
4836
4837 OPEN c_awt_bucket(rec_invoice.vendor_id
4838 ,awt_period
4839 ,rec_awt_gross_dists.vat_code
4840 );
4841 debug_info := 'Fetch CURSOR c_awt_bucket';
4842 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4843 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
4844 END IF;
4845
4846 FETCH c_awt_bucket INTO gross_amt_to_date, withheld_amt_to_date;
4847
4848 IF (c_awt_bucket%FOUND) THEN
4849 debug_info := 'Update the AWT bucket';
4850 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4851 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
4852 END IF;
4853
4854 UPDATE ap_awt_buckets
4855 SET gross_amount_to_date = (gross_amt_to_date -
4856 ap_utilities_pkg.ap_round_currency(
4857 rec_awt_gross_dists.awt_gross_amount*
4858 NVL(l_invoice_exchange_rate,1),
4859 l_func_currency_code ))
4860 WHERE CURRENT OF c_awt_bucket;
4861
4862
4863
4864
4865 ELSE
4866 NULL;
4867 END IF;
4868
4869 debug_info := 'CLOSE CURSOR c_awt_bucket';
4870 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4871 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
4872 END IF;
4873
4874 CLOSE c_awt_bucket;
4875
4876 EXCEPTION
4877 WHEN NOTHING_TO_DO THEN NULL;
4878 END Update_Bucket;
4879
4880 debug_info := 'CLOSE CURSOR c_invoice';
4881 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4882 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
4883 END IF;
4884
4885
4886 CLOSE c_invoice;
4887
4888 END Get_Invoice_info;
4889
4890 END LOOP For_Each_AWT_Line;
4891
4892 END Process_awt_gross_dists;
4893
4894 --Bug12594549
4895 <<Process_Withholding_dists>>
4896 DECLARE
4897 DBG_Loc VARCHAR2(30) := 'Process_Withholding_dists';
4898 BEGIN
4899 debug_info := 'OPEN CURSOR c_awt_dists';
4900 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4901 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
4902 END IF;
4903
4904 IF (P_Calling_Module in ('CANCEL INVOICE','REVERSE DIST')) THEN
4905 OPEN c_awt_dists_inv (P_Parent_Id);
4906 ELSIF (P_Calling_Module = 'VOID PAYMENT') THEN
4907 OPEN c_awt_dists_pay (P_Parent_Id);
4908 END IF;
4909
4910 <<For_Each_Withholding_Line>>
4911 LOOP
4912 debug_info := 'Fetch CURSOR c_get_awt_period';
4913 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4914 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
4915 END IF;
4916
4917 IF (P_Calling_Module in ('CANCEL INVOICE','REVERSE DIST')) THEN
4918 FETCH c_awt_dists_inv INTO rec_awt_dists;
4919 EXIT WHEN c_awt_dists_inv%NOTFOUND;
4920 ELSIF (P_Calling_Module = 'VOID PAYMENT') THEN
4921 FETCH c_awt_dists_pay INTO rec_awt_dists;
4922 EXIT WHEN c_awt_dists_pay%NOTFOUND;
4923 END IF;
4924 --
4925 <<Get_Withholding_Period>>
4926 DECLARE
4927 DBG_Loc VARCHAR2(30) := 'Get_Withholding_Period';
4928 msg VARCHAR2(240);
4929 CURSOR c_get_period (distDate IN DATE, TaxId IN NUMBER) IS
4930 SELECT period_name
4931 FROM ap_other_periods P,
4932 ap_tax_codes T
4933 WHERE t.tax_id = TaxId
4934 AND p.period_type = t.awt_period_type
4935 AND p.application_id = 200
4936 AND p.module = 'AWT'
4937 AND p.start_date <= TRUNC(distDate)
4938 AND p.end_date >= TRUNC(distDate);
4939 BEGIN
4940 debug_info := 'OPEN CURSOR c_get_period';
4941 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4942 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
4943 END IF;
4944
4945 OPEN c_get_period (rec_awt_dists.accounting_date
4946 ,rec_awt_dists.withholding_tax_code_id);
4947 debug_info := 'Fetch CURSOR c_get_period';
4948 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4949 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
4950 END IF;
4951
4952 FETCH c_get_period INTO awt_period;
4953
4954 IF c_get_period%FOUND THEN
4955 msg := 'AWT period '||awt_period||' found for tax id '||
4956 rec_awt_dists.withholding_tax_code_id;
4957 ELSE
4958 msg := 'No AWT period found for tax id '||rec_awt_dists.withholding_tax_code_id;
4959 END IF;
4960
4961 debug_info := 'CLOSE CURSOR c_get_period';
4962 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4963 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
4964 END IF;
4965
4966 CLOSE c_get_period;
4967
4968 END Get_Withholding_Period;
4969
4970 <<Reverse_Current_Line>>
4971 DECLARE
4972 DBG_Loc VARCHAR2(30) := 'Reverse_Current_Line';
4973
4974 CURSOR c_invoice (InvId IN NUMBER) IS
4975 SELECT vendor_id
4976 , set_of_books_id
4977 , accts_pay_code_combination_id
4978 , batch_id
4979 , description
4980 , invoice_amount
4981 , invoice_currency_code
4982 , exchange_date
4983 , exchange_rate
4984 , exchange_rate_type
4985 -- , ussgl_transaction_code - Bug 4277744
4986 -- , ussgl_trx_code_context - Bug 4277744
4987 , vat_code
4988 FROM ap_invoices
4989 WHERE invoice_id = InvId
4990 FOR UPDATE;
4991 rec_invoice c_invoice%ROWTYPE;
4992
4993 CURSOR c_curr_dist (InvId IN NUMBER,
4994 InvLineNum IN NUMBER) IS
4995 SELECT MAX(distribution_line_number)+1 curr_line_number
4996 FROM ap_invoice_distributions
4997 WHERE invoice_id = InvId
4998 AND invoice_line_number = InvLineNum;
4999
5000 curr_line_number ap_invoice_distributions.distribution_line_number%TYPE;
5001 l_invoice_distribution_id ap_invoice_distributions.invoice_distribution_id%TYPE;
5002
5003 BEGIN
5004 debug_info := 'OPEN CURSOR c_curr_dist';
5005 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5006 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
5007 END IF;
5008
5009 OPEN c_curr_dist (rec_awt_dists.invoice_id,
5010 rec_awt_dists.invoice_line_number);
5011
5012 debug_info := 'Fetch CURSOR c_curr_dist';
5013 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5014 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
5015 END IF;
5016
5017 FETCH c_curr_dist INTO curr_line_number;
5018
5019 debug_info := 'CLOSE CURSOR c_curr_dist';
5020 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5021 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
5022 END IF;
5023
5024 CLOSE c_curr_dist;
5025
5026 debug_info := 'OPEN CURSOR c_invoice';
5027 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5028 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
5029 END IF;
5030
5031 OPEN c_invoice (rec_awt_dists.invoice_id);
5032
5033 debug_info := 'Fetch CURSOR c_invoice';
5034 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5035 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
5036 END IF;
5037
5038 FETCH c_invoice INTO rec_invoice;
5039
5040 debug_info := 'Discard the Line';
5041 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5042 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
5043 END IF;
5044
5045 /* Bug 5202248. Added the Nvl */
5046 IF nvl(l_old_inv_line_num, 0) <> rec_awt_dists.invoice_line_number THEN
5047 UPDATE ap_invoice_lines_all
5048 SET discarded_flag = DECODE(p_calling_module,'CANCEL INVOICE','N','Y'),
5049 /* Bug 5299720. Comment out the following line */
5050 -- Cancelled_flag = DECODE(p_calling_module,'CANCEL INVOICE','Y','N'),
5051 Original_amount = amount,
5052 Original_base_amount = base_amount,
5053 Original_rounding_amt = rounding_amt,
5054 Amount = 0,
5055 Base_amount = 0,
5056 Rounding_amt = 0,
5057 Last_update_date = SYSDATE,
5058 Last_Updated_By = P_Last_Updated_By,
5059 Last_Update_Login = P_Last_Update_Login,
5060 Program_application_id = P_Program_application_id,
5061 Program_id = P_Program_id,
5062 Program_update_date = DECODE(p_program_id,NULL,NULL,SYSDATE),
5063 Request_id = P_Request_id
5064 WHERE invoice_id = rec_awt_dists.invoice_id
5065 AND line_number = rec_awt_dists.invoice_line_number;
5066
5067 l_old_inv_line_num := rec_awt_dists.invoice_line_number;
5068 END IF;
5069
5070 -- IF (P_Calling_module not in ('REVERSE DIST')) THEN
5071 -- From now there will be no difference between REVERSE DIST and CANCEL INVOICE
5072 -- except when REVERSE DIST IS passed match status flag of newly created
5073 -- awt lines will be N else it will be Y.
5074
5075 debug_info := 'Insert reverse AWT line INTO ap_invoice_distributions';
5076 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5077 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
5078 END IF;
5079
5080 INSERT INTO ap_invoice_distributions
5081 (
5082 accounting_date
5083 ,accrual_posted_flag
5084 ,assets_addition_flag
5085 ,assets_tracking_flag
5086 ,cash_posted_flag
5087 ,distribution_line_number
5088 ,invoice_line_number
5089 ,dist_code_combination_id
5090 ,invoice_id
5091 ,last_UPDATEd_by
5092 ,last_UPDATE_date
5093 ,line_type_lookup_code
5094 ,period_name
5095 ,set_of_books_id
5096 ,amount
5097 ,base_amount
5098 ,batch_id
5099 ,created_by
5100 ,creation_date
5101 ,description
5102 ,last_UPDATE_login
5103 ,match_status_flag
5104 ,posted_flag
5105 ,program_application_id
5106 ,program_id
5107 ,program_update_date
5108 ,request_id
5109 ,withholding_tax_code_id /* Bug 5382525 */
5110 ,encumbered_flag
5111 ,pa_addition_flag
5112 ,posted_amount
5113 ,posted_base_amount
5114 -- ,ussgl_transaction_code - Bug 4277744
5115 -- ,ussgl_trx_code_context - Bug 4277744
5116 ,awt_flag
5117 ,awt_tax_rate_id
5118 ,awt_gross_amount
5119 ,awt_origin_group_id
5120 ,awt_invoice_payment_id
5121 ,tax_code_override_flag
5122 ,tax_recovery_rate
5123 ,tax_recovery_override_flag
5124 ,tax_recoverable_flag
5125 ,invoice_distribution_id
5126 ,reversal_flag
5127 ,parent_reversal_id
5128 ,type_1099
5129 ,income_tax_region
5130 ,org_id
5131 ,awt_related_id
5132 --Freight and Special Charges
5133 ,rcv_charge_addition_flag
5134 /* Start of fix for bug#8462050*/
5135 ,global_attribute_category
5136 ,global_attribute1
5137 ,global_attribute2
5138 ,global_attribute3
5139 ,global_attribute4
5140 ,global_attribute5
5141 ,global_attribute6
5142 ,global_attribute7
5143 ,global_attribute8
5144 ,global_attribute9
5145 ,global_attribute10
5146 ,global_attribute11
5147 ,global_attribute12
5148 ,global_attribute13
5149 ,global_attribute14
5150 ,global_attribute15
5151 ,global_attribute16
5152 ,global_attribute17
5153 ,global_attribute18
5154 ,global_attribute19
5155 ,global_attribute20
5156 /* End of fix for bug#8462050*/
5157 ,cancellation_flag --bug 9781126
5158 )
5159 values
5160 (
5161 gl_awt_date
5162 ,'N'
5163 ,'N'
5164 ,'N'
5165 ,'N'
5166 ,curr_line_number /*bug 5202248. invoice_line_number was inserted before */
5167 ,rec_awt_dists.invoice_line_number
5168 ,rec_awt_dists.dISt_code_combination_id
5169 ,rec_awt_dists.invoice_id
5170 ,P_Last_Updated_By
5171 ,SYSDATE
5172 ,'AWT'
5173 ,gl_period_name
5174 ,rec_invoice.set_of_books_id
5175 ,-rec_awt_dists.amount
5176 ,-rec_awt_dists.base_amount
5177 ,rec_invoice.batch_id
5178 ,P_Last_Updated_By
5179 ,SYSDATE
5180 ,rec_awt_dists.description
5181 ,P_Last_Update_Login
5182 ,decode(p_calling_module,'REVERSE DIST','N','A') -- BUG 6720284
5183 ,'N'
5184 ,P_Program_Application_Id
5185 ,P_Program_Id
5186 ,decode (P_Program_Id,NULL,NULL,SYSDATE)
5187 ,P_Request_Id
5188 ,rec_awt_dists.withholding_tax_code_id
5189 ,'T'
5190 ,'E'
5191 ,0
5192 ,0
5193 -- ,rec_invoice.ussgl_transaction_code - Bug 4277744
5194 -- ,rec_invoice.ussgl_trx_code_context - Bug 4277744
5195 ,'A'
5196 ,rec_awt_dists.awt_tax_rate_id
5197 ,rec_awt_dists.awt_gross_amount * -1
5198 ,rec_awt_dists.awt_origin_group_id
5199 ,P_New_Invoice_Payment_Id
5200 ,'N'
5201 ,''
5202 ,'N'
5203 ,'N'
5204 ,ap_invoice_distributions_s.nextval
5205 ,'N'
5206 ,rec_awt_dists.invoice_distribution_id
5207 ,rec_awt_dists.type_1099
5208 ,rec_awt_dists.income_tax_region
5209 ,rec_awt_dists.org_id
5210 ,rec_awt_dists.awt_related_id
5211 ,'N'
5212 /* Start of fix for bug#8462050*/
5213 ,rec_awt_dists.global_attribute_category
5214 ,rec_awt_dists.global_attribute1
5215 ,rec_awt_dists.global_attribute2
5216 ,rec_awt_dists.global_attribute3
5217 ,rec_awt_dists.global_attribute4
5218 ,rec_awt_dists.global_attribute5
5219 ,rec_awt_dists.global_attribute6
5220 ,rec_awt_dists.global_attribute7
5221 ,rec_awt_dists.global_attribute8
5222 ,rec_awt_dists.global_attribute9
5223 ,rec_awt_dists.global_attribute10
5224 ,rec_awt_dists.global_attribute11
5225 ,rec_awt_dists.global_attribute12
5226 ,rec_awt_dists.global_attribute13
5227 ,rec_awt_dists.global_attribute14
5228 ,rec_awt_dists.global_attribute15
5229 ,rec_awt_dists.global_attribute16
5230 ,rec_awt_dists.global_attribute17
5231 ,rec_awt_dists.global_attribute18
5232 ,rec_awt_dists.global_attribute19
5233 ,rec_awt_dists.global_attribute20
5234 /* End of fix for bug#8462050*/
5235 ,DECODE(P_Calling_Module, 'CANCEL INVOICE',
5236 DECODE(rec_awt_dists.awt_invoice_payment_id, NULL, 'Y', NULL), NULL) --bug 9781126
5237 );
5238
5239 --Bug 4539462 DBI logging
5240 AP_DBI_PKG.Maintain_DBI_Summary
5241 ( p_table_name => 'AP_INVOICE_DISTRIBUTIONS',
5242 p_operation => 'I',
5243 p_key_value1 => rec_awt_dists.invoice_id,
5244 p_key_value2 => l_Invoice_distribution_ID,
5245 p_calling_sequence => current_calling_sequence);
5246
5247
5248 <<Update_Payment_Schedule>>
5249 DECLARE
5250
5251 reversed_withholding NUMBER := -rec_awt_dists.amount;
5252
5253 CURSOR c_payment_num (InvPaymId IN NUMBER) IS
5254 SELECT payment_num
5255 FROM ap_invoice_payments
5256 WHERE invoice_payment_id = InvPaymId;
5257
5258 paym_num ap_invoice_payments.payment_num%TYPE;
5259
5260 CURSOR c_payment_sched (PaymNum IN NUMBER, InvId IN NUMBER) IS
5261
5262 SELECT APS.gross_amount
5263 , NVL(APS.inv_curr_gross_amount, APS.gross_Amount) inv_curr_gross_amount
5264 , APS.amount_remaining
5265 , AI.payment_currency_code
5266 FROM ap_payment_schedules APS,
5267 ap_invoices AI
5268 WHERE AI.invoice_id = InvId
5269 AND AI.invoice_id = APS.invoice_id
5270 AND APS.payment_num = NVL(PaymNum, APS.payment_num) /* Bug 5300858 */
5271 FOR UPDATE of APS.gross_amount, APS.inv_curr_gross_amount, APS.amount_remaining;
5272
5273 rec_payment_sched c_payment_sched%ROWTYPE;
5274
5275 DBG_Loc VARCHAR2(30) := 'Update_Payment_Schedule';
5276
5277 NOTHING_TO_DO exception;
5278
5279 BEGIN
5280
5281 /* Bug 5300858 */
5282 IF (P_Calling_Module NOT IN ('REVERSE DIST', 'VOID PAYMENT')) THEN
5283 RAISE NOTHING_TO_DO;
5284 END IF;
5285
5286 /* Bug 5300858 */
5287 IF (P_Calling_Module = 'VOID PAYMENT') THEN
5288
5289 debug_info := 'OPEN CURSOR c_payment_num';
5290 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5291 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
5292 END IF;
5293
5294 OPEN c_payment_num(P_Parent_Id);
5295
5296 debug_info := 'Fetch CURSOR c_payment_num';
5297 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5298 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
5299 END IF;
5300
5301 FETCH c_payment_num INTO paym_num;
5302
5303 debug_info := 'CLOSE CURSOR c_payment_num';
5304 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5305 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
5306 END IF;
5307
5308 CLOSE c_payment_num;
5309
5310 END IF;
5311
5312 debug_info := 'OPEN CURSOR c_payment_sched';
5313 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5314 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
5315 END IF;
5316
5317 OPEN c_payment_sched(paym_num, rec_awt_dists.invoice_id);
5318
5319 debug_info := 'Fetch CURSOR c_payment_sched';
5320 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5321 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
5322 END IF;
5323
5324 FETCH c_payment_sched INTO rec_payment_sched;
5325
5326 IF (c_payment_sched%FOUND) THEN
5327 debug_info := 'Update the payment schedule';
5328 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5329 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
5330 END IF;
5331
5332 UPDATE ap_payment_schedules
5333 SET amount_remaining = (amount_remaining +
5334 ap_utilities_pkg.ap_round_currency(
5335 reversed_withholding *
5336 payment_cross_rate,
5337 rec_payment_sched.payment_currency_code))
5338 , payment_status_flag = decode(amount_remaining + -- Bug 8300099/4959558
5339 ap_utilities_pkg.ap_round_currency(reversed_withholding * payment_cross_rate,
5340 rec_payment_sched.payment_currency_code),gross_amount,'N','P')
5341 WHERE CURRENT of c_payment_sched;
5342
5343 -- Bug 8300099/7518063 : Added below update statement
5344 UPDATE ap_invoices
5345 SET payment_status_flag = AP_INVOICES_UTILITY_PKG.get_payment_status( rec_awt_dists.invoice_id )
5346 WHERE invoice_id = rec_awt_dists.invoice_id ;
5347
5348 ELSE
5349 NULL;
5350 END IF;
5351
5352 debug_info := 'CLOSE CURSOR c_payment_sched';
5353 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5354 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
5355 END IF;
5356
5357 CLOSE c_payment_sched;
5358
5359 EXCEPTION
5360 WHEN NOTHING_TO_DO THEN
5361 NULL;
5362
5363 END Update_Payment_Schedule;
5364
5365 <<Update_Bucket>>
5366 DECLARE
5367 CURSOR c_awt_bucket (VendorId IN NUMBER,
5368 Period IN VARCHAR2,
5369 TaxCode IN VARCHAR2) IS
5370 SELECT gross_amount_to_date,
5371 withheld_amount_to_date
5372 FROM ap_awt_buckets
5373 WHERE vendor_id = VendorId
5374 AND period_name = Period
5375 AND tax_name = TaxCode
5376 FOR UPDATE;
5377
5378 gross_amt_to_date ap_awt_buckets.gross_amount_to_date%TYPE;
5379 withheld_amt_to_date ap_awt_buckets.withheld_amount_to_date%TYPE;
5380
5381 DBG_Loc VARCHAR2(30) := 'Update_Bucket';
5382 NOTHING_TO_DO exception;
5383 BEGIN
5384 IF awt_period IS NULL THEN
5385 raISe NOTHING_TO_DO;
5386 END IF;
5387
5388 debug_info := ' Fetching the functional currency AND exchange rate ' ;
5389 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5390 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
5391 END IF;
5392
5393 SELECT base_currency_code
5394 INTO l_func_currency_code
5395 FROM ap_system_parameters
5396 WHERE org_id = l_org_id;
5397
5398 IF (P_Calling_Module in ('CANCEL INVOICE','REVERSE DIST')) THEN
5399 l_invoice_exchange_rate := rec_invoice.exchange_rate;
5400 ELSIF (P_Calling_Module = 'VOID PAYMENT') THEN
5401
5402 SELECT ai.exchange_rate
5403 INTO l_invoice_exchange_rate
5404 FROM ap_invoices ai, ap_invoice_payments aip
5405 WHERE ai.invoice_id = aip.invoice_id
5406 AND aip.invoice_payment_id = rec_awt_dists.awt_invoice_payment_id;
5407 END IF;
5408
5409 debug_info := 'OPEN CURSOR c_awt_bucket';
5410 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5411 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
5412 END IF;
5413
5414 OPEN c_awt_bucket(rec_invoice.vendor_id
5415 ,awt_period
5416 ,rec_awt_dists.vat_code
5417 );
5418 debug_info := 'Fetch CURSOR c_awt_bucket';
5419 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5420 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
5421 END IF;
5422
5423 FETCH c_awt_bucket INTO gross_amt_to_date, withheld_amt_to_date;
5424
5425 IF (c_awt_bucket%FOUND) THEN
5426 debug_info := 'Update the AWT bucket';
5427 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5428 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
5429 END IF;
5430
5431 --Bug12594549
5432 UPDATE ap_awt_buckets
5433 SET
5434 /* gross_amount_to_date = (gross_amt_to_date -
5435 ap_utilities_pkg.ap_round_currency(
5436 rec_awt_dists.awt_gross_amount*
5437 NVL(l_invoice_exchange_rate,1),
5438 l_func_currency_code )), */
5439 withheld_amount_to_date = (withheld_amt_to_date+
5440 ap_utilities_pkg.ap_round_currency(
5441 rec_awt_dists.amount*NVL(l_invoice_exchange_rate,1),
5442 l_func_currency_code ))
5443 WHERE CURRENT OF c_awt_bucket;
5444 ELSE
5445 NULL;
5446 END IF;
5447
5448 debug_info := 'CLOSE CURSOR c_awt_bucket';
5449 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5450 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
5451 END IF;
5452
5453 CLOSE c_awt_bucket;
5454
5455 EXCEPTION
5456 WHEN NOTHING_TO_DO THEN NULL;
5457 END Update_Bucket;
5458
5459 debug_info := 'CLOSE CURSOR c_invoice';
5460 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5461 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
5462 END IF;
5463
5464 CLOSE c_invoice;
5465
5466 -- Create/Reverse the invoice to the Tax Authority
5467 DECLARE
5468 CURSOR c_read_setup(p_org_id in NUMBER) --bug14404025
5469 IS
5470 SELECT create_awt_invoices_type,create_awt_dists_type --bug7685907
5471 FROM ap_system_parameters_all
5472 where org_id = p_org_id; --bug14404025
5473
5474 BEGIN
5475 debug_info := 'OPEN CURSOR c_read_setup';
5476 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5477 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
5478 END IF;
5479
5480 OPEN c_read_setup(l_org_id); --bug14404025
5481
5482 debug_info := 'Fetch CURSOR c_read_setup';
5483 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5484 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
5485 END IF;
5486
5487 FETCH c_read_setup INTO l_create_invoices,l_create_dists; --bug7685907
5488
5489 debug_info := 'CLOSE CURSOR c_read_setup';
5490 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5491 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
5492 END IF;
5493
5494 CLOSE c_read_setup;
5495 --Bug6660355
5496
5497 /* bug 8266021 the following call to create awt invoices
5498 is moved outside the loop
5499
5500 IF (l_create_invoices in('APPROVAL','BOTH')) THEN
5501 -- Bug 8254604
5502 Create_AWT_Invoices(
5503 P_Invoice_Id => rec_awt_dists.invoice_id,
5504 P_Payment_Date => NULL,
5505 P_Last_Updated_By => P_Last_Updated_By,
5506 P_Last_Update_Login => P_Last_Update_Login,
5507 P_Program_Application_Id => P_Program_Application_Id,
5508 P_Program_Id => P_Program_Id,
5509 P_Request_Id => P_Request_Id,
5510 P_Calling_Sequence => current_calling_sequence,
5511 P_Calling_Module => p_calling_module,
5512 P_Inv_Line_No => rec_awt_dists.invoice_line_number,
5513 P_Dist_Line_No => curr_line_number,
5514 P_New_Invoice_Id => P_New_Invoice_Id,
5515 P_create_dists => l_create_dists); --bug7685907
5516
5517 ELSIF (l_create_invoices in('PAYMENT','BOTH') AND
5518 rec_awt_dists.awt_invoice_id IS NOT NULL) THEN
5519 -- Bug 8254604
5520 Create_AWT_Invoices(
5521 P_Invoice_Id => rec_awt_dists.invoice_id,
5522 P_Payment_Date => NULL,
5523 P_Last_Updated_By => P_Last_Updated_By,
5524 P_Last_Update_Login => P_Last_Update_Login,
5525 P_Program_Application_Id => P_Program_Application_Id,
5526 P_Program_Id => P_Program_Id,
5527 P_Request_Id => P_Request_Id,
5528 P_Calling_Sequence => current_calling_sequence,
5529 P_Calling_Module => p_calling_module,
5530 P_Inv_Line_No => rec_awt_dists.invoice_line_number,
5531 P_Dist_Line_No => NVL(P_New_dist_Line_No, P_dist_Line_No),
5532 P_New_Invoice_Id => P_New_Invoice_Id,
5533 P_create_dists => l_create_dists); --bug7685907
5534
5535 END IF;
5536
5537 */
5538
5539 UPDATE ap_invoice_distributions
5540 SET reversal_flag='Y'
5541 WHERE invoice_distribution_id = rec_awt_dists.invoice_distribution_id
5542 OR parent_reversal_id=rec_awt_dists.invoice_distribution_id;
5543
5544 END;
5545 END Reverse_Current_Line;
5546 END LOOP For_Each_Withholding_Line;
5547
5548 debug_info := 'rec_awt_dists.awt_invoice_id '|| rec_awt_dists.awt_invoice_id;
5549 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5550 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
5551 END IF;
5552
5553 debug_info := 'rec_awt_dists.invoice_id '|| rec_awt_dists.invoice_id;
5554 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5555 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
5556 END IF;
5557
5558 debug_info := 'P_Parent_Id '|| P_Parent_Id;
5559 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5560 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
5561 END IF;
5562
5563
5564 debug_info := 'l_create_invoices '||l_create_invoices;
5565 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5566 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
5567 END IF;
5568
5569 /*Bug 10353952 begins*/
5570 SELECT count(*)
5571 INTO l_pay_awt_invs_count
5572 FROM ap_invoice_distributions_all
5573 WHERE invoice_id = DECODE(p_calling_module,'VOID PAYMENT',rec_awt_dists.invoice_id,
5574 P_Parent_Id)
5575 AND line_type_lookup_code = 'AWT'
5576 AND awt_flag = 'A'
5577 AND awt_invoice_id is not null;
5578 /*Bug 10353952 ends*/
5579
5580 IF (l_create_invoices in('APPROVAL','BOTH') and p_calling_module <> 'VOID PAYMENT') THEN
5581 -- Bug 8254604
5582 Create_AWT_Invoices(
5583 P_Invoice_Id => P_Parent_Id, --bug 8266021
5584 P_Payment_Date => NULL,
5585 P_Last_Updated_By => P_Last_Updated_By,
5586 P_Last_Update_Login => P_Last_Update_Login,
5587 P_Program_Application_Id => P_Program_Application_Id,
5588 P_Program_Id => P_Program_Id,
5589 P_Request_Id => P_Request_Id,
5590 P_Calling_Sequence => current_calling_sequence,
5591 P_Calling_Module => p_calling_module,
5592 P_Inv_Line_No => NULL,
5593 P_Dist_Line_No => NULL,
5594 P_New_Invoice_Id => P_New_Invoice_Id, --bug 8266021
5595 P_create_dists => l_create_dists); --bug7685907
5596
5597 ELSIF (l_create_invoices in('PAYMENT','BOTH')
5598 --AND rec_awt_dists.awt_invoice_id IS NOT NULL (commented in bug 8266021)
5599 and l_pay_awt_invs_count > 0) THEN /*bug 10353952*/
5600 -- Bug 8254604
5601 Create_AWT_Invoices(
5602 P_Invoice_Id => rec_awt_dists.invoice_id,
5603 P_Payment_Date => NULL,
5604 P_Last_Updated_By => P_Last_Updated_By,
5605 P_Last_Update_Login => P_Last_Update_Login,
5606 P_Program_Application_Id => P_Program_Application_Id,
5607 P_Program_Id => P_Program_Id,
5608 P_Request_Id => P_Request_Id,
5609 P_Calling_Sequence => current_calling_sequence,
5610 P_Calling_Module => p_calling_module,
5611 P_Inv_Line_No => NULL,
5612 P_Dist_Line_No => NULL,
5613 P_New_Invoice_Id => P_New_Invoice_Id,
5614 P_create_dists => l_create_dists); --bug7685907
5615
5616 END IF;
5617
5618 debug_info := 'CLOSE CURSOR c_awt_dists';
5619 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5620 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
5621 END IF;
5622
5623 IF (P_Calling_Module IN ('CANCEL INVOICE','REVERSE DIST')) THEN
5624 CLOSE c_awt_dists_inv;
5625
5626 UPDATE ap_invoice_distributions
5627 SET awt_withheld_amt = NULL
5628 WHERE invoice_id = P_parent_id
5629 AND NVL(awt_withheld_amt,0) <> 0;
5630
5631 ELSIF (P_Calling_Module = 'VOID PAYMENT') THEN
5632 CLOSE c_awt_dists_pay;
5633 END IF;
5634
5635 END Process_Withholding_dists;
5636
5637 -- Execute the ExtENDed Withholding Reversion (IF active)
5638
5639 IF (Ap_ExtENDed_Withholding_Pkg.Ap_ExtENDed_Withholding_Active) THEN
5640 Ap_ExtENDed_Withholding_Pkg.Ap_Undo_ExtENDed_Withholding
5641 (P_Parent_Id,
5642 P_Calling_Module,
5643 P_Awt_Date,
5644 P_New_Invoice_Payment_Id,
5645 P_Last_Updated_By,
5646 P_Last_Update_Login,
5647 P_Program_Application_Id,
5648 P_Program_Id,
5649 P_Request_Id,
5650 P_Awt_Success,
5651 P_dist_Line_No,
5652 P_New_Invoice_Id,
5653 P_New_dist_Line_No);
5654 END IF;
5655
5656
5657 EXCEPTION
5658 WHEN INVALID_CALLING_MODULE THEN
5659 P_Awt_Success := 'Error: Invalid Calling Module ['||P_Calling_Module||']';
5660
5661 WHEN NOT_AN_OPEN_GL_PERIOD THEN
5662 DECLARE
5663 error_text VARCHAR2(2000);
5664 BEGIN
5665 error_text := Ap_Utilities_Pkg.Ap_Get_DISplayed_Field('AWT ERROR',
5666 'GL PERIOD NOT OPEN');
5667 P_AWT_Success := error_text;
5668 END;
5669 --
5670 WHEN OTHERS THEN
5671 DECLARE
5672 error_text VARCHAR2(512) := substr(sqlerrm, 1, 512);
5673 BEGIN
5674 ROLLBACK TO BEFORE_UNDO_WITHHOLDING;
5675 --
5676 P_Awt_Success := error_text;
5677
5678 IF (SQLCODE <> -20001) THEN
5679 FND_MESSAGE.set_NAME('SQLAP','AP_DEBUG');
5680 FND_MESSAGE.set_TOKEN('ERROR',SQLERRM);
5681 FND_MESSAGE.set_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
5682 FND_MESSAGE.set_TOKEN('PARAMETERS',
5683 ' Parent Id = ' || to_char(P_Parent_Id) ||
5684 ', Calling_Module = ' || P_Calling_Module ||
5685 ', Awt_Date = ' || P_Awt_Date ||
5686 ', New_Invoice_Payment_Id = ' || to_char(P_New_Invoice_Payment_Id) ||
5687 ', dist_Line_No = ' || to_char(P_dist_Line_No) ||
5688 ', New_Invoice_Id = ' || to_char(P_New_Invoice_Id) ||
5689 ', New_dist_Line_No = ' || to_char(P_New_dist_Line_No));
5690
5691 FND_MESSAGE.set_TOKEN('DEBUG_INFO',debug_info);
5692 END IF;
5693 APP_EXCEPTION.RAISE_EXCEPTION;
5694 END;
5695 END Ap_Undo_Withholding;
5696
5697 /*bug13606808, Added the procedure Ap_Undo_Orphan_Distributions to delete the orphan AWT distributions
5698 from ap_awt_temp_distributions_all table and hanlde awt buckets that are effected by them
5699 */
5700 PROCEDURE Ap_Undo_Orphan_Distributions (
5701 P_Invoice_Id IN NUMBER,
5702 P_Vendor_Id IN NUMBER DEFAULT NULL,
5703 P_Payment_Num IN NUMBER,
5704 P_Checkrun_Name IN VARCHAR2,
5705 P_Undo_Awt_Date IN DATE,
5706 P_Calling_Module IN VARCHAR2,
5707 P_Last_Updated_By IN NUMBER,
5708 P_Last_Update_Login IN NUMBER,
5709 P_Program_Application_Id IN NUMBER DEFAULT NULL,
5710 P_Program_Id IN NUMBER DEFAULT NULL,
5711 P_Request_Id IN NUMBER DEFAULT NULL,
5712 P_Awt_Success OUT NOCOPY VARCHAR2,
5713 P_checkrun_id in NUMBER DEFAULT NULL)
5714 IS
5715 DBG_Loc VARCHAR2(30) := 'Ap_Undo_Temp_Withholding';
5716 current_calling_sequence VARCHAR2(2000);
5717 debug_info VARCHAR2(100);
5718
5719 --cursor c_orphan_awt_buckets_w to revert the withheld amount from buckets
5720 cursor c_orphan_awt_buckets_w (InvId IN NUMBER
5721 ,PaymNum IN NUMBER)
5722 is
5723 select ai.vendor_id, aatd.accounting_date, aatd.tax_name, aatd.withholding_amount
5724 from ap_awt_temp_distributions_all AATD,
5725 ap_invoices_all AI
5726 where aatd.invoice_id = InvId
5727 and AATD.invoice_id = AI.invoice_id
5728 AND ( PaymNum IS NULL
5729 OR AATD.payment_num = PaymNum)
5730 and P_Calling_Module <> 'AUTOAPPROVAL'
5731 AND (P_Calling_Module = 'QUICKCHECK'
5732 OR (P_Calling_Module = 'AUTOSELECT' and AATD.checkrun_id <> P_checkrun_id))
5733 and AATD.invoice_payment_id is null;
5734
5735 --cursor c_orphan_awt_buckets_g to revert the gross amount from buckets
5736 cursor c_orphan_awt_buckets_g (InvId IN NUMBER
5737 ,PaymNum IN NUMBER)
5738 is
5739 select ai.vendor_id, aatd.accounting_date, aatd.tax_name, aatd.gross_amount
5740 from ap_awt_temp_distributions_all AATD,
5741 ap_invoices_all AI
5742 where aatd.invoice_id = InvId
5743 and AATD.invoice_id = AI.invoice_id
5744 AND ( PaymNum IS NULL
5745 OR AATD.payment_num = PaymNum)
5746 and P_Calling_Module <> 'AUTOAPPROVAL'
5747 AND (P_Calling_Module = 'QUICKCHECK'
5748 OR (P_Calling_Module = 'AUTOSELECT' and AATD.checkrun_id <> P_checkrun_id))
5749 and aatd.awt_related_id =
5750 (
5751 select min(aatdx.awt_related_id) from ap_awt_temp_distributions_all aatdx
5752 where aatdx.invoice_id = InvId
5753 and aatdx.checkrun_name = aatd.checkrun_name
5754 and aatdx.payment_num = aatd.payment_num
5755 and aatdx.checkrun_id = aatd.checkrun_id
5756 and aatdx.group_id = aatd.group_id
5757 )
5758 and AATD.invoice_payment_id is null;
5759
5760 rec_orphan_awt_buckets_w c_orphan_awt_buckets_w%ROWTYPE;
5761 rec_orphan_awt_buckets_g c_orphan_awt_buckets_g%ROWTYPE;
5762 awt_period ap_other_periods.period_name%TYPE;
5763
5764 BEGIN
5765 current_calling_sequence := 'AP_WITHHOLDING_PKG.Ap_Undo_Orphan_Distributions';
5766
5767 P_AWT_Success := 'SUCCESS';
5768
5769 BEGIN
5770
5771 debug_info := 'DELETE orphan AWT temp distributions';
5772 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5773 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
5774 END IF;
5775
5776
5777 OPEN c_orphan_awt_buckets_w(P_Invoice_Id, P_Payment_Num);
5778 debug_info := 'OPEN CURSOR c_orphan_awt_buckets_w';
5779 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5780 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
5781 END IF;
5782
5783 LOOP
5784
5785 FETCH c_orphan_awt_buckets_w into rec_orphan_awt_buckets_w;
5786 EXIT WHEN c_orphan_awt_buckets_w%NOTFOUND;
5787
5788 DECLARE
5789 CURSOR c_get_awt_period IS
5790 SELECT p.period_name
5791 FROM ap_other_periods P,
5792 ap_tax_codes_all C
5793 WHERE (rec_orphan_awt_buckets_w.accounting_date BETWEEN
5794 p.start_date AND p.end_date)
5795 AND p.period_type = c.awt_period_type
5796 AND c.name = rec_orphan_awt_buckets_w.tax_name
5797 AND p.module = 'AWT';
5798
5799
5800 BEGIN
5801 debug_info := 'OPEN CURSOR c_get_awt_period';
5802 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5803 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
5804 END IF;
5805
5806 OPEN c_get_awt_period;
5807
5808 debug_info := 'Fetch CURSOR c_get_awt_period';
5809 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5810 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
5811 END IF;
5812
5813 FETCH c_get_awt_period INTO awt_period;
5814
5815 debug_info := 'CLOSE CURSOR c_get_awt_period';
5816 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5817 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
5818 END IF;
5819
5820 CLOSE c_get_awt_period;
5821
5822 END;
5823
5824 UPDATE ap_awt_buckets_all
5825 SET
5826 withheld_amount_to_date = withheld_amount_to_date -
5827 NVL(rec_orphan_awt_buckets_w.withholding_amount, 0),
5828 last_update_date = SYSDATE,
5829 last_updated_by = P_Last_Updated_By,
5830 last_update_login = P_Last_Update_Login,
5831 program_update_date = SYSDATE,
5832 program_application_id = P_Program_Application_Id,
5833 program_id = P_Program_Id,
5834 request_id = P_Request_Id
5835 WHERE vendor_id = rec_orphan_awt_buckets_w.vendor_id
5836 AND tax_name = rec_orphan_awt_buckets_w.tax_name
5837 AND period_name = awt_period;
5838
5839 END LOOP;
5840
5841 CLOSE c_orphan_awt_buckets_w;
5842
5843
5844 OPEN c_orphan_awt_buckets_g(P_Invoice_Id, P_Payment_Num);
5845 debug_info := 'OPEN CURSOR c_orphan_awt_buckets_g';
5846 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5847 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
5848 END IF;
5849
5850 LOOP
5851
5852 FETCH c_orphan_awt_buckets_g into rec_orphan_awt_buckets_g;
5853 EXIT WHEN c_orphan_awt_buckets_g%NOTFOUND;
5854
5855 DECLARE
5856 CURSOR c_get_awt_period IS
5857 SELECT p.period_name
5858 FROM ap_other_periods P,
5859 ap_tax_codes_all C
5860 WHERE (rec_orphan_awt_buckets_g.accounting_date BETWEEN
5861 p.start_date AND p.end_date)
5862 AND p.period_type = c.awt_period_type
5863 AND c.name = rec_orphan_awt_buckets_g.tax_name
5864 AND p.module = 'AWT';
5865
5866
5867 BEGIN
5868 debug_info := 'OPEN CURSOR c_get_awt_period';
5869 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5870 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
5871 END IF;
5872
5873 OPEN c_get_awt_period;
5874
5875 debug_info := 'Fetch CURSOR c_get_awt_period';
5876 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5877 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
5878 END IF;
5879
5880 FETCH c_get_awt_period INTO awt_period;
5881
5882 debug_info := 'CLOSE CURSOR c_get_awt_period';
5883 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5884 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
5885 END IF;
5886
5887 CLOSE c_get_awt_period;
5888
5889 END;
5890
5891 UPDATE ap_awt_buckets_all
5892 SET
5893 gross_amount_to_date = gross_amount_to_date -
5894 NVL(rec_orphan_awt_buckets_g.gross_amount, 0),
5895 last_update_date = SYSDATE,
5896 last_updated_by = P_Last_Updated_By,
5897 last_update_login = P_Last_Update_Login,
5898 program_update_date = SYSDATE,
5899 program_application_id = P_Program_Application_Id,
5900 program_id = P_Program_Id,
5901 request_id = P_Request_Id
5902 WHERE vendor_id = rec_orphan_awt_buckets_g.vendor_id
5903 AND tax_name = rec_orphan_awt_buckets_g.tax_name
5904 AND period_name = awt_period;
5905
5906 END LOOP;
5907
5908 CLOSE c_orphan_awt_buckets_g;
5909
5910 EXCEPTION
5911 WHEN OTHERS THEN
5912 debug_info := 'error occurred : '|| SQLERRM;
5913 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5914 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||DBG_Loc,debug_info);
5915 END IF;
5916 END;
5917
5918 DELETE
5919 FROM ap_awt_temp_distributions_all AATD
5920 where aatd.invoice_id = P_Invoice_Id
5921 AND (P_Payment_Num IS NULL
5922 OR AATD.payment_num = P_Payment_Num)
5923 AND P_Calling_Module <> 'AUTOAPPROVAL'
5924 AND (P_Calling_Module = 'QUICKCHECK'
5925 OR (P_Calling_Module = 'AUTOSELECT' and AATD.checkrun_id <> P_checkrun_id))
5926 AND AATD.invoice_payment_id is null;
5927
5928
5929
5930 EXCEPTION
5931 WHEN OTHERS THEN
5932 DECLARE
5933 error_text VARCHAR2(512) := substr(sqlerrm, 1, 512);
5934 BEGIN
5935 P_Awt_Success := error_text;
5936 IF (SQLCODE <> -20001) THEN
5937 FND_MESSAGE.set_NAME('SQLAP','AP_DEBUG');
5938 FND_MESSAGE.set_TOKEN('ERROR',SQLERRM);
5939 FND_MESSAGE.set_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
5940 FND_MESSAGE.set_TOKEN('PARAMETERS',
5941 ', Invoice_Id = ' || to_char(P_Invoice_Id) ||
5942 ', VENDor_Id = ' || to_char(P_VENDor_Id) ||
5943 ', Payment_Num = ' || to_char(P_Payment_Num) ||
5944 ', Checkrun_Name = ' || P_Checkrun_Name ||
5945 ' Undo_Awt_Date = ' || to_char(P_Undo_Awt_Date));
5946
5947 FND_MESSAGE.set_TOKEN('DEBUG_INFO',debug_info);
5948 END IF;
5949 APP_EXCEPTION.RAISE_EXCEPTION;
5950 END;
5951 END Ap_Undo_Orphan_Distributions;
5952
5953 END AP_WITHHOLDING_PKG;