[Home] [Help]
PACKAGE BODY: APPS.AP_INVOICE_CORRECTIONS_PKG
Source
1 PACKAGE BODY AP_INVOICE_CORRECTIONS_PKG AS
2 /*$Header: apinvcob.pls 120.24.12020000.2 2012/07/13 11:21:03 mkmeda ship $*/
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AP_MATCHING_PKG';
5 G_MSG_UERROR CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR;
6 G_MSG_ERROR CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_ERROR;
7 G_MSG_SUCCESS CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_SUCCESS;
8 G_MSG_HIGH CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH;
9 G_MSG_MEDIUM CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM;
10 G_MSG_LOW CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW;
11 G_LINES_PER_FETCH CONSTANT NUMBER := 1000;
12
13 G_CURRENT_RUNTIME_LEVEL CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
14 G_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
15 G_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
16 G_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
17 G_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
18 G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
19 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
20 G_MODULE_NAME CONSTANT VARCHAR2(100) := 'AP.PLSQL.AP_INVOICE_CORRECTIONS_PKG.';
21
22 --LOCAL PROCEDURES
23 PROCEDURE Get_Info(X_Invoice_ID IN NUMBER,
24 X_Calling_Sequence IN VARCHAR2 );
25
26
27 Procedure Create_Invoice_Lines_Dists(
28 X_Invoice_Id IN NUMBER,
29 X_Corrected_Invoice_Id IN NUMBER,
30 X_Corrected_Line_Number IN NUMBER,
31 X_Line_Tab IN OUT NOCOPY LINE_TAB_TYPE,
32 X_Dist_Tab IN OUT NOCOPY DIST_TAB_TYPE,
33 --bugfix:4700522
34 X_Prorate_Dists_Flag IN VARCHAR2,
35 X_Correction_Quantity IN NUMBER,
36 X_Correction_Price IN NUMBER,
37 X_Total_Correction_Amount IN NUMBER,
38 X_Calling_Sequence IN VARCHAR2);
39
40
41 Procedure Get_Line_Proration_Info(X_Corrected_Invoice_Id IN NUMBER,
42 X_Correction_Amount IN NUMBER,
43 X_Line_Tab IN OUT NOCOPY LINE_TAB_TYPE,
44 X_Prorate_Lines_Flag IN VARCHAR2,
45 X_Calling_Sequence IN VARCHAR2);
46
47
48 Procedure Get_Dist_Proration_Info(X_Corrected_Invoice_Id IN NUMBER,
49 X_Corrected_Line_Number IN NUMBER,
50 X_Line_Amount IN NUMBER,
51 X_Line_Base_Amount IN NUMBER,
52 X_Included_Tax_Amount IN NUMBER, -- Bug 5597409
53 X_Dist_Tab IN OUT NOCOPY DIST_TAB_TYPE,
54 X_Prorate_Dists_Flag IN VARCHAR2,
55 X_Calling_Sequence IN VARCHAR2);
56
57 FUNCTION Get_Dist_Proration_Total(
58 X_Corrected_Invoice_id IN NUMBER,
59 X_Corrected_Line_Number IN NUMBER,
60 X_Current_Calling_Sequence IN VARCHAR2) RETURN NUMBER;
61
62
63 Procedure Insert_Invoice_Line (X_Invoice_Id IN NUMBER,
64 X_Invoice_Line_Number IN NUMBER,
65 X_Corrected_Invoice_Id IN NUMBER,
66 X_Corrected_Line_Number IN NUMBER,
67 X_Amount IN NUMBER,
68 X_Base_Amount IN NUMBER,
69 X_Rounding_Amt IN NUMBER,
70 X_Correction_Quantity IN NUMBER,
71 X_Correction_Price IN NUMBER,
72 X_Calling_Sequence IN VARCHAR2);
73
74
75 PROCEDURE Insert_Invoice_Distributions (
76 X_Invoice_ID IN NUMBER,
77 X_Invoice_Line_Number IN NUMBER,
78 X_Dist_Tab IN OUT NOCOPY Dist_Tab_Type,
79 X_Line_Amount IN NUMBER,
80 X_Calling_Sequence IN VARCHAR2);
81
82
83 --Global Variable Declaration
84 G_max_invoice_line_number ap_invoice_lines.line_number%TYPE := 0;
85 G_batch_id ap_batches.batch_id%TYPE;
86 G_Accounting_Date ap_invoice_lines.accounting_date%TYPE;
87 G_Period_Name gl_period_statuses.period_name%TYPE;
88 G_Set_of_Books_ID ap_system_parameters.set_of_books_id%TYPE;
89 G_Awt_Group_ID ap_awt_groups.group_id%TYPE;
90 G_Exchange_Rate ap_invoices.exchange_rate%TYPE;
91 G_Precision fnd_currencies.precision%TYPE;
92 G_Min_Acct_Unit fnd_currencies.minimum_accountable_unit%TYPE;
93 G_System_Allow_Awt_Flag ap_system_parameters.allow_awt_flag%TYPE;
94 G_Site_Allow_Awt_Flag po_vendor_sites.allow_awt_flag%TYPE;
95 G_Base_Currency_Code ap_system_parameters.base_currency_code%TYPE;
96 G_Invoice_Currency_Code ap_invoices.invoice_currency_code%TYPE;
97 G_Allow_PA_Override VARCHAR2(1);
98 G_Income_Tax_Region ap_system_parameters.income_tax_region%TYPE;
99 G_Approval_Workflow_Flag ap_system_parameters.approval_workflow_flag%TYPE;
100 -- Removed for bug 4277744
101 -- G_Ussgl_Transaction_Code ap_invoices.ussgl_transaction_code%TYPE;
102 G_Type_1099 po_vendors.type_1099%TYPE;
103 G_User_Id number;
104 G_Login_Id number;
105 G_Trx_Business_Category ap_invoices.trx_business_category%TYPE;
106 G_Org_Id ap_invoices_all.org_id%TYPE;
107
108
109 PROCEDURE Invoice_Correction(
110 X_Invoice_Id IN NUMBER,
111 X_Invoice_Line_Number IN NUMBER,
112 X_Corrected_Invoice_Id IN NUMBER,
113 X_Corrected_Line_Number IN NUMBER,
114 X_Prorate_Lines_Flag IN VARCHAR2,
115 X_Prorate_Dists_Flag IN VARCHAR2,
116 X_Correction_Quantity IN NUMBER,
117 X_Correction_Amount IN NUMBER,
118 X_Correction_Price IN NUMBER,
119 X_Line_Tab IN OUT NOCOPY LINE_TAB_TYPE,
120 X_Dist_Tab IN OUT NOCOPY DIST_TAB_TYPE,
121 X_Calling_Sequence IN VARCHAR2) IS
122
123
124 l_line_base_amount ap_invoice_lines.base_amount%TYPE;
125 l_debug_info VARCHAR2(100);
126 current_calling_sequence VARCHAR2(2000);
127 l_api_name VARCHAR2(30);
128 l_success BOOLEAN := TRUE;
129 l_error_code VARCHAR2(2000);
130 l_included_tax_amount NUMBER;
131 Tax_Exception Exception;
132
133 BEGIN
134
135 l_api_name := 'Invoice_Correction';
136
137 -- Update the calling sequence (for error message).
138 current_calling_sequence := 'AP_INVOICE_CORRECTIONS_PKG.Invoice_Correction<-'||X_calling_sequence;
139
140 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
141 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_INVOICE_CORRECTIONS_PKG.Invoice_Correction(+)');
142 END IF;
143
144 l_debug_info := 'Get Invoice and System Options information';
145 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
146 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
147 END IF;
148
149 get_info(X_Invoice_Id => X_invoice_id,
150 X_Calling_Sequence => current_calling_sequence);
151
152 --Create a invoice distributions for line level correction.
153 IF (x_invoice_line_number IS NOT NULL) THEN
154
155 -- Bug 5597409. Calling eTax
156 l_debug_info := 'Calculate Tax on the Invoice';
157
158 l_success := ap_etax_pkg.calling_etax
159 (p_invoice_id => x_invoice_id,
160 p_calling_mode => 'CALCULATE',
161 p_all_error_messages => 'N',
162 p_error_code => l_error_code,
163 p_calling_sequence => current_calling_sequence);
164
165 IF (NOT l_success) THEN
166 Raise Tax_Exception;
167 END IF;
168
169 l_debug_info := 'Call Get_Proration_Info to get
170 distribution proration info';
171
172 l_line_base_amount := ap_utilities_pkg.ap_round_currency(x_correction_amount*g_exchange_rate,
173 g_base_currency_code);
174
175 -- Bug 5597409
176 l_debug_info := 'Get Line Included Tax Amount';
177 Begin
178 Select nvl(included_tax_amount, 0)
179 Into l_included_tax_amount
180 From ap_invoice_lines_all
181 Where invoice_id = x_invoice_id
182 And line_number = x_invoice_line_number;
183 Exception
184 When Others Then
185 l_included_tax_amount := 0;
186 End ;
187
188 Get_Dist_Proration_Info(
189 X_Corrected_Invoice_Id => x_corrected_invoice_id,
190 X_Corrected_Line_Number => x_corrected_line_number,
191 X_Line_Amount => x_correction_amount,
192 X_Line_Base_Amount => l_line_base_amount,
193 X_Included_Tax_Amount => l_included_tax_amount, -- Bug 5597409
194 X_Dist_Tab => x_dist_tab,
195 X_Prorate_Dists_Flag => x_prorate_dists_flag,
196 X_Calling_Sequence => current_calling_sequence);
197
198
199 l_debug_info := 'Call Insert_Invoice_Dists to insert correcting
200 invoice distributions';
201 Insert_Invoice_Distributions(
202 X_Invoice_Id => x_invoice_id,
203 X_Invoice_Line_Number => x_invoice_line_number,
204 X_Dist_Tab => x_dist_tab,
205 X_Line_Amount => x_correction_amount,
206 X_Calling_Sequence => current_calling_sequence);
207
208 l_debug_info := 'Update the generate_dists to D after the distributions are created';
209
210 UPDATE ap_invoice_lines
211 SET generate_dists = 'D'
212 WHERE invoice_id = x_invoice_id
213 AND line_number = x_invoice_line_number;
214
215 ELSE
216
217 l_debug_info := 'Call Get_Line_Proration_Info to get
218 line proration info';
219
220 Get_Line_Proration_Info(
221 X_Corrected_Invoice_Id => x_corrected_invoice_id,
222 X_Correction_Amount => x_correction_amount,
223 X_Line_Tab => x_line_tab,
224 X_Prorate_Lines_Flag => x_prorate_lines_flag,
225 X_Calling_Sequence => current_calling_sequence);
226
227
228 l_debug_info := 'Call Create_Invoice_Lines to create
229 correcting lines and distributions';
230
231 Create_Invoice_Lines_Dists(
232 X_Invoice_Id => x_invoice_id,
233 X_Corrected_Invoice_Id => x_corrected_invoice_id,
234 X_Corrected_Line_Number => x_corrected_line_number,
235 X_Line_Tab => x_line_tab,
236 X_Dist_Tab => x_dist_tab,
237 X_Correction_Quantity => x_correction_quantity,
238 X_Correction_Price => x_correction_price,
239 X_Total_Correction_Amount => x_correction_amount,
240 X_Prorate_Dists_Flag => x_prorate_dists_flag,
241 X_Calling_Sequence => current_calling_sequence);
242
243 END IF;
244
245 --Clean up the PL/SQL tables.
246 x_line_tab.delete;
247 x_dist_tab.delete;
248
249 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
250 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_INVOICE_CORRECTIONS_PKG.Invoice_Correction(-)');
251 END IF;
252
253
254 EXCEPTION
255 WHEN OTHERS THEN
256 IF (SQLCODE <> -20001) THEN
257 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
258 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
259 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
260 FND_MESSAGE.SET_TOKEN('PARAMETERS',' Invoice Id = '||to_char(x_invoice_id)
261 ||', Invoice Line Number = '||to_char(x_invoice_line_number)
262 ||', Corrected Invoice Id = '||to_char(x_corrected_invoice_id)
263 ||', Corrected Line Number = '||to_char(x_corrected_line_number)
264 ||', Correction amount = '||to_char(x_correction_amount));
265 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
266 END IF;
267
268 --Clean up the PL/SQL tables.
269 x_line_tab.delete;
270 x_dist_tab.delete;
271
272 APP_EXCEPTION.RAISE_EXCEPTION;
273
274 END Invoice_Correction;
275
276
277 PROCEDURE Get_Info(X_Invoice_ID IN NUMBER,
278 X_Calling_Sequence IN VARCHAR2
279 ) IS
280
281 current_calling_sequence VARCHAR2(2000);
282 l_debug_info VARCHAR2(100);
283 l_api_name VARCHAR2(30);
284
285 BEGIN
286
287 l_api_name := 'Get_Info';
288
289 current_calling_sequence := 'Get_Info<-'||Current_Calling_Sequence;
290 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
291 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_INVOICE_CORRECTIONS_PKG.Get_Info(+)');
292 END IF;
293
294
295 --NOTE: Need to test out the logic for income_tax_region
296 -- Perf bug 5052493
297 -- Original SQL causing shared memory usage of 11 MB. Reduced SQL memory usage by going to base
298 -- tables
299 SELECT ai.gl_date,
300 ai.batch_id,
301 ai.set_of_books_id,
302 ai.awt_group_id,
303 ai.exchange_rate,
304 fc.precision,
305 fc.minimum_accountable_unit,
306 nvl(asp.allow_awt_flag,'N'),
307 nvl(pvs.allow_awt_flag,'N'),
308 asp.base_currency_code,
309 ai.invoice_currency_code,
310 decode(pv.type_1099,'','',
311 decode(combined_filing_flag,'N',NULL,
312 decode(asp.income_tax_region_flag,'Y',pvs.state,
313 asp.income_tax_region))),
314 pv.type_1099,
315 nvl(asp.approval_workflow_flag,'N'),
316 -- ai.ussgl_transaction_code , - Bug 4277744
317 ai.trx_business_category,
318 ai.org_id
319 INTO g_accounting_date,
320 g_batch_id,
321 g_set_of_books_id,
322 g_awt_group_id,
323 g_exchange_rate,
324 g_precision,
325 g_min_acct_unit,
326 g_system_allow_awt_flag,
327 g_site_allow_awt_flag,
328 g_base_currency_code,
329 g_invoice_currency_code,
330 g_income_tax_region,
331 g_type_1099,
332 g_approval_workflow_flag,
333 -- g_ussgl_transaction_code, - Bug 4277744
334 g_trx_business_category,
335 g_org_id
336 FROM ap_invoices_all ai ,
337 ap_system_parameters asp,
338 ap_suppliers pv,
339 ap_supplier_sites_all pvs,
340 fnd_currencies fc -- bug 5052493
341 WHERE ai.invoice_id = x_invoice_id
342 AND ai.vendor_site_id = pvs.vendor_site_id
343 AND pv.vendor_id = pvs.vendor_id
344 AND ai.org_id = asp.org_id
345 AND ai.set_of_books_id = asp.set_of_books_id
346 AND ai.invoice_currency_code = fc.currency_code (+);
347
348 SELECT nvl(max(ail.line_number),0)
349 INTO g_max_invoice_line_number
350 FROM ap_invoice_lines ail
351 WHERE ail.invoice_id = x_invoice_id;
352
353 l_debug_info := 'select period for accounting date';
354
355 --Get_current_gl_date will return NULL if the date passed to it doesn't fall in a
356 --open period.
357 g_period_name := AP_UTILITIES_PKG.get_current_gl_date(g_accounting_date,
358 g_org_id);
359
360 IF (g_period_name IS NULL) THEN
361
362 --Get gl_period and Date from a future period for the accounting date
363 ap_utilities_pkg.get_open_gl_date(p_date => g_accounting_date,
364 p_period_name => g_period_name,
365 p_gl_date => g_accounting_date);
366
367 IF (g_accounting_date IS NULL) THEN
368 fnd_message.set_name('SQLAP','AP_DISTS_NO_OPEN_FUT_PERIOD');
369 app_exception.raise_exception;
370 END IF;
371
372 END IF;
373
374 g_allow_pa_override := FND_PROFILE.VALUE('PA_ALLOW_FLEXBUILDER_OVERRIDES');
375
376 g_user_id := FND_PROFILE.VALUE('USER_ID');
377
378 g_login_id := FND_PROFILE.VALUE('LOGIN_ID');
379
380 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
381 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_INVOICE_CORRECTIONS_PKG.Get_Info(-)');
382 END IF;
383
384
385 EXCEPTION
386 WHEN OTHERS THEN
387 IF (SQLCODE <> -20001) THEN
388 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
389 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
390 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
391 FND_MESSAGE.SET_TOKEN('PARAMETERS','Invoice Id = '||TO_CHAR(X_Invoice_Id));
392 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
393 END IF;
394 app_exception.raise_exception;
395
396 END Get_Info;
397
398 FUNCTION Get_Line_Proration_Total(X_Corrected_Invoice_Id IN NUMBER,
399 X_Calling_Sequence IN VARCHAR2) RETURN NUMBER IS
400 l_line_total NUMBER;
401 l_debug_info VARCHAR2(1000);
402 current_calling_sequence VARCHAR2(2000);
403 BEGIN
404
405 l_debug_info := 'Get Line Proration Total of corrected invoice';
406 current_calling_sequence := 'Get_Line_Proration_Total <- '||x_calling_sequence;
407
408 SELECT sum(ail.amount)
409 INTO l_line_total
410 FROM ap_invoice_lines_all ail
411 WHERE ail.invoice_id = x_corrected_invoice_id
412 --Modified below condition for bug#10410133
413 --from " = 'ITEM' to in ('ITEM','FREIGHT','MISCELLANEOUS') "
414 -- Bug 5597409, restricting for Item line only
415 AND ail.line_type_lookup_code in ('ITEM','FREIGHT','MISCELLANEOUS')
416 --Added below conditions for bug#10410133
417 AND nvl(ail.discarded_flag,'N') = 'N'
418 AND NVL(match_type,'NOT_MATCHED') = 'NOT_MATCHED';
419
420 RETURN(l_line_total);
421
422 EXCEPTION WHEN OTHERS THEN
423 IF (SQLCODE <> -20001) THEN
424 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
425 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
426 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
427 FND_MESSAGE.SET_TOKEN('PARAMETERS','Corrected Invoice Id = '||TO_CHAR(X_Corrected_Invoice_Id));
428 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
429 END IF;
430 app_exception.raise_exception;
431
432 END;
433
434 Procedure Get_Line_Proration_Info(X_Corrected_Invoice_Id IN NUMBER,
435 X_Correction_Amount IN NUMBER,
436 X_Line_Tab IN OUT NOCOPY LINE_TAB_TYPE,
437 X_Prorate_Lines_Flag IN VARCHAR2,
438 X_Calling_Sequence IN VARCHAR2) IS
439
440 CURSOR Invoice_Lines_Cursor(p_line_proration_total NUMBER) IS
441 SELECT ail.line_number,
442 decode(g_min_acct_unit,'',
443 round (x_correction_amount * ail.amount/p_line_proration_total,
444 g_precision),
445 round (((x_correction_amount * ail.amount/p_line_proration_total)/
446 g_min_acct_unit) * g_min_acct_unit)
447 )
448 FROM ap_invoice_lines ail,
449 ap_invoices ai
450 WHERE ai.invoice_id = x_corrected_invoice_id
451 AND ail.invoice_id = ai.invoice_id
452 --Modified below condition for bug#10410133
453 --from " = 'ITEM' to in ('ITEM','FREIGHT','MISCELLANEOUS') "
454 -- Bug 5597409, restricting for Item line only
455 AND ail.line_type_lookup_code in ('ITEM','FREIGHT','MISCELLANEOUS')
456 --Introduced below condition for bug#9010485
457 AND nvl(ail.discarded_flag,'N') = 'N'
458 --Added condition for bug#10410133
459 AND NVL(match_type,'NOT_MATCHED') = 'NOT_MATCHED';
460
461
462 l_corrected_line_number ap_invoice_lines.line_number%TYPE;
463 l_amount ap_invoice_lines.amount%TYPE;
464 i NUMBER;
465 l_line_rounded_index ap_invoice_lines.line_number%TYPE;
466 l_max_line_amount ap_invoice_lines.amount%TYPE := 0;
467 l_sum_line_prorated_amount ap_invoice_lines.amount%TYPE := 0;
468 l_total_base_amount ap_invoice_lines.base_amount%TYPE := 0;
469 l_sum_line_base_amount ap_invoice_lines.base_amount%TYPE := 0;
470 l_base_amount ap_invoice_lines.base_amount%TYPE;
471 l_debug_info VARCHAR2(100);
472 l_line_proration_total NUMBER;
473 current_calling_sequence VARCHAR2(2000);
474 l_api_name VARCHAR2(30);
475
476
477 BEGIN
478
479 l_api_name := 'Get_Line_Proration_Info';
480
481 current_calling_sequence := 'Get_Line_Proration_Info<-'||x_calling_sequence;
482
483 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
484 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_INVOICE_CORRECTIONS_PKG.Get_Line_Proration_Info(+)');
485 END IF;
486
487
488 IF (x_prorate_lines_flag = 'Y') THEN
489
490 l_line_proration_total := Get_Line_Proration_Total(
491 X_Corrected_Invoice_Id,
492 current_calling_sequence);
493
494 l_debug_info := 'Open Invoice_Lines_Cursor';
495 OPEN Invoice_Lines_Cursor(l_line_proration_total);
496
497 LOOP
498
499 FETCH Invoice_Lines_Cursor INTO l_corrected_line_number,
500 l_amount;
501
502 EXIT WHEN Invoice_Lines_Cursor%NOTFOUND;
503
504 x_line_tab(l_corrected_line_number).corrected_line_number := l_corrected_line_number;
505 x_line_tab(l_corrected_line_number).line_amount := l_amount;
506
507 --Store the index of max of invoice line with largest amount
508 --for proration rounding and base_amount rounding
509 l_debug_info := 'Get rounding index';
510 --bug 6629136
511 IF (abs(l_amount)>= abs(l_max_line_amount)) THEN
512
513 l_line_rounded_index := l_corrected_line_number;
514 l_max_line_amount := x_line_tab(l_corrected_line_number).line_amount;
515
516 END IF;
517
518 l_sum_line_prorated_amount := l_sum_line_prorated_amount + l_amount;
519
520 END LOOP;
521
522 l_debug_info := 'Close Invoice Lines Cursor';
523 CLOSE Invoice_Lines_Cursor;
524
525 l_debug_info := 'Update the pl/sql table with the rounding amount';
526 IF (x_correction_amount <> l_sum_line_prorated_amount and l_line_rounded_index IS NOT NULL) THEN
527
528 x_line_tab(l_line_rounded_index).line_amount := x_line_tab(l_line_rounded_index).line_amount +
529 (x_correction_amount - l_sum_line_prorated_amount);
530 END IF;
531
532 END IF;
533
534
535 IF (g_exchange_rate IS NOT NULL) THEN
536
537 l_total_base_amount := ap_utilities_pkg.ap_round_currency(
538 x_correction_amount*g_exchange_rate,
539 g_base_currency_code);
540
541 FOR i in nvl(x_line_tab.first,0) .. nvl(x_line_tab.last,0) LOOP
542
543 IF (x_line_tab.exists(i)) THEN
544
545 l_base_amount := ap_utilities_pkg.ap_round_currency(
546 x_line_tab(i).line_amount*g_exchange_rate,
547 g_base_currency_code);
548 x_line_tab(i).base_amount := l_base_amount;
549
550 --Store the index of max of invoice line with largest amount
551 --for base_amount rounding
552 l_debug_info := 'Get the rounding index for base amount rounding';
553 IF (x_line_tab(i).line_amount >= l_max_line_amount) THEN
554 l_line_rounded_index := i;
555 l_max_line_amount := x_line_tab(i).line_amount;
556 END IF;
557
558 l_sum_line_base_amount := l_sum_line_base_amount + l_base_amount;
559 END IF;
560
561 END LOOP;
562
563 IF (l_total_base_amount <> l_sum_line_base_amount and l_line_rounded_index IS NOT NULL) THEN
564
565 x_line_tab(l_line_rounded_index).base_amount := x_line_tab(l_line_rounded_index).base_amount +
566 (l_total_base_amount - l_sum_line_base_amount);
567 x_line_tab(l_line_rounded_index).rounding_amt := l_total_base_amount - l_sum_line_base_amount;
568
569 END IF;
570
571 END IF; /* g_exchange_rate IS NOT NULL*/
572
573 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
574 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_INVOICE_CORRECTIONS_PKG.Get_Line_Proration_Info(-)');
575 END IF;
576
577
578 EXCEPTION
579 WHEN OTHERS THEN
580 IF (SQLCODE <> -20001) THEN
581 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
582 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
583 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
584 FND_MESSAGE.SET_TOKEN('PARAMETERS',
585 'Corrected Invoice Id = '||to_char(x_corrected_invoice_id)
586 ||', Correction Amount = '||to_char(x_correction_amount)
587 ||', Prorate Lines Flag = '||x_prorate_lines_flag);
588 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
589 END IF;
590
591 --Clean up the PL/SQL table
592 x_line_tab.delete;
593
594 app_exception.raise_exception;
595
596 END Get_Line_Proration_Info;
597
598
599
600 Procedure Get_Dist_Proration_Info(X_Corrected_Invoice_Id IN NUMBER,
601 X_Corrected_Line_Number IN NUMBER,
602 X_Line_Amount IN NUMBER,
603 X_Line_Base_Amount IN NUMBER,
604 X_Included_Tax_Amount IN NUMBER,
605 X_Dist_Tab IN OUT NOCOPY DIST_TAB_TYPE,
606 X_Prorate_Dists_Flag IN VARCHAR2,
607 X_Calling_Sequence IN VARCHAR2) IS
608
609 CURSOR Invoice_Dists_Cursor(p_proration_total IN NUMBER) IS
610 SELECT aid.invoice_distribution_id,
611 /* decode(g_min_acct_unit,'',
612 round ((x_line_amount + nvl(ail.included_tax_amount,0)) * aid.amount/p_proration_total,
613 g_precision),
614 round ((((x_line_amount + nvl(ail.included_tax_amount,0)) * aid.amount/p_proration_total)/
615 g_min_acct_unit) * g_min_acct_unit)
616 ) , */
617 decode(g_min_acct_unit,'',
618 round ((x_line_amount - x_included_tax_amount) * aid.amount/p_proration_total,
619 g_precision),
620 round ((((x_line_amount - x_included_tax_amount) * aid.amount/p_proration_total)/
621 g_min_acct_unit) * g_min_acct_unit)
622 ) ,
623
624 ap_invoice_distributions_s.nextval
625 FROM ap_invoice_lines ail,
626 ap_invoice_distributions aid
627 WHERE ail.invoice_id = x_corrected_invoice_id
628 AND ail.line_number = x_corrected_line_number
629 AND aid.invoice_id = ail.invoice_id
630 AND aid.invoice_line_number = ail.line_number
631 --Modified below condition for bug#10410133
632 --from " = 'ITEM' to in ('ITEM','FREIGHT','MISCELLANEOUS')"
633 -- Bug 5597409. Add the restriction for Item distribution only
634 AND aid.line_type_lookup_code in ('ITEM','FREIGHT','MISCELLANEOUS')
635 --AND aid.prepay_distribution_id IS NULL
636 --Introduced below condition for bug#9010485
637 AND nvl(aid.reversal_flag,'N') = 'N';
638
639
640 l_corrected_inv_dist_id ap_invoice_distributions.invoice_distribution_id%TYPE;
641 l_invoice_distribution_id ap_invoice_distributions.invoice_distribution_id%TYPE;
642 l_amount ap_invoice_distributions.amount%TYPE;
643 l_max_dist_amount ap_invoice_distributions.amount%TYPE := 0;
644 l_rounding_index ap_invoice_distributions.invoice_distribution_id%TYPE;
645 l_sum_prorated_amount ap_invoice_distributions.amount%TYPE := 0;
646 l_base_amount ap_invoice_distributions.base_amount%TYPE := 0;
647 l_sum_base_amount ap_invoice_distributions.base_amount%TYPE := 0;
648 i NUMBER;
649 l_debug_info VARCHAR2(100);
650 current_calling_sequence VARCHAR2(2000);
651 l_api_name VARCHAR2(30);
652 l_dist_proration_total NUMBER;
653
654 BEGIN
655
656 l_api_name := 'Get_Dist_Proration_Info';
657 current_calling_sequence := 'Get_Dist_Proration_Info<-'||x_calling_sequence;
658
659 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
660 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_INVOICE_CORRECTIONS_PKG.Get_Dist_Proration_Info(+)');
661 END IF;
662
663
664 IF (x_prorate_dists_flag = 'Y') THEN
665
666 l_debug_info := 'Get Distribution Proration total from corrected line';
667 l_dist_proration_total := Get_Dist_Proration_Total(
668 X_Corrected_Invoice_Id,
669 X_Corrected_Line_Number,
670 Current_Calling_Sequence);
671
672
673 l_debug_info := 'Open Invoice_Dists_Cursor(l_dist_proration_total)';
674 OPEN Invoice_Dists_Cursor(l_dist_proration_total);
675
676 l_debug_info := 'Populate distribution proration info';
677 LOOP
678
679 FETCH Invoice_Dists_Cursor INTO l_corrected_inv_dist_id,
680 l_amount,
681 l_invoice_distribution_id;
682
683
684 EXIT WHEN Invoice_Dists_Cursor%NOTFOUND;
685
686 x_dist_tab(l_invoice_distribution_id).corrected_inv_dist_id := l_corrected_inv_dist_id;
687 x_dist_tab(l_invoice_distribution_id).amount := l_amount;
688 x_dist_tab(l_invoice_distribution_id).invoice_distribution_id := l_invoice_distribution_id;
689
690 --Bug6720791
691 IF ((l_amount >= l_max_dist_amount) and (l_amount >= 0))
692 OR((l_amount < l_max_dist_amount) and (l_amount < 0 ) ) THEN
693 l_rounding_index := l_invoice_distribution_id;
694 l_max_dist_amount := l_amount;
695 END IF;
696
697 l_sum_prorated_amount := l_sum_prorated_amount + l_amount;
698
699 END LOOP;
700
701 CLOSE Invoice_Dists_Cursor;
702
703 l_debug_info := 'Perform Proration rounding';
704 IF (l_sum_prorated_amount <> x_line_amount and l_rounding_index IS NOT NULL) THEN
705
706 x_dist_tab(l_rounding_index).amount := x_dist_tab(l_rounding_index).amount +
707 (x_line_amount - l_sum_prorated_amount);
708
709 END IF;
710
711 END IF;
712
713 IF (g_exchange_rate IS NOT NULL) THEN
714
715 l_debug_info := 'Calculate Base_amount and Base_amount rounding';
716 FOR i in nvl(x_dist_tab.first,0) .. nvl(x_dist_tab.last,0) LOOP
717
718 IF (x_dist_tab.exists(i)) THEN
719
720 l_base_amount := ap_utilities_pkg.ap_round_currency(
721 x_dist_tab(i).amount*g_exchange_rate,
722 g_base_currency_code);
723
724 x_dist_tab(i).base_amount := l_base_amount ;
725
726 l_sum_base_amount := l_sum_base_amount + l_base_amount;
727
728 END IF;
729
730 END LOOP;
731
732 IF (x_line_base_amount <> l_sum_base_amount and l_rounding_index is not null) THEN
733
734 x_dist_tab(l_rounding_index).base_amount := x_dist_tab(l_rounding_index).base_amount +
735 (x_line_base_amount - l_sum_base_amount);
736 x_dist_tab(l_rounding_index).rounding_amt := x_line_base_amount - l_sum_base_amount;
737
738 END IF;
739
740 END IF; /*g_exchange_rate IS NOT NULL*/
741
742
743 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
744 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_INVOICE_CORRECTIONS_PKG.Get_Dist_Proration_Info(-)');
745 END IF;
746
747
748 EXCEPTION
749 WHEN OTHERS THEN
750 IF (SQLCODE <> -20001) THEN
751 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
752 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
753 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
754 FND_MESSAGE.SET_TOKEN('PARAMETERS',
755 'Corrected Invoice Id = '||to_char(x_corrected_invoice_id)
756 ||', Line Amount = '||to_char(x_line_amount)
757 ||', Prorate Distributions Flag = '||x_prorate_dists_flag);
758 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
759 END IF;
760
761 --Clean up the PL/SQL table
762 x_dist_tab.delete;
763
764 app_exception.raise_exception;
765
766 END Get_Dist_Proration_Info;
767
768
769 FUNCTION Get_Dist_Proration_Total(
770 X_Corrected_Invoice_id IN NUMBER,
771 X_Corrected_Line_Number IN NUMBER,
772 X_Current_Calling_Sequence IN VARCHAR2) RETURN NUMBER IS
773
774 l_dist_total NUMBER;
775 l_debug_info VARCHAR2(1000);
776 current_calling_sequence VARCHAR2(2000);
777 BEGIN
778
779 Current_Calling_Sequence := 'Get_Dist_Proration_Total <- '||x_current_calling_sequence;
780 l_debug_info := 'Get the total dist proration total';
781
782 SELECT sum(aid.amount)
783 INTO l_dist_total
784 FROM ap_invoice_distributions_all aid
785 WHERE aid.invoice_id = x_corrected_invoice_id
786 AND aid.invoice_line_number = x_corrected_line_number
787 --Modified below condition for bug#10410133
788 --from " = 'ITEM' to in ('ITEM','FREIGHT','MISCELLANEOUS')"
789 -- Bug 5597409 Add the restriction for Item distribution only
790 AND aid.line_type_lookup_code in ('ITEM','FREIGHT','MISCELLANEOUS')
791 -- AND aid.prepay_distribution_id IS NULL
792 --Introduced below condition for bug#10410133
793 AND nvl(aid.reversal_flag,'N') = 'N';
794
795 RETURN(l_dist_total);
796
797 EXCEPTION WHEN OTHERS THEN
798 IF (SQLCODE <> -20001) THEN
799 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
800 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
801 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
802 FND_MESSAGE.SET_TOKEN('PARAMETERS',
803 'Corrected Invoice Id = '||to_char(x_corrected_invoice_id)
804 ||',Corrected Line Number = '||to_char(x_corrected_line_number));
805 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
806 END IF;
807
808 app_exception.raise_exception;
809
810 END Get_Dist_Proration_Total;
811
812
813 Procedure Create_Invoice_Lines_Dists(
814 X_Invoice_Id IN NUMBER,
815 X_Corrected_Invoice_Id IN NUMBER,
816 X_Corrected_Line_Number IN NUMBER,
817 X_Line_Tab IN OUT NOCOPY LINE_TAB_TYPE,
818 X_Dist_Tab IN OUT NOCOPY DIST_TAB_TYPE,
819 --bugfix:4700522
820 X_Prorate_Dists_Flag IN VARCHAR2,
821 X_Correction_Quantity IN NUMBER,
822 X_Correction_Price IN NUMBER,
823 X_Total_Correction_Amount IN NUMBER,
824 X_Calling_Sequence IN VARCHAR2) IS
825
826 l_invoice_line_number ap_invoice_lines.line_number%TYPE;
827 l_sum_prorated_amount ap_invoice_lines.amount%TYPE;
828 l_max_line_amount ap_invoice_lines.amount%TYPE;
829 l_line_rounded_index ap_invoice_lines.line_number%TYPE;
830 l_line_rounding_amount ap_invoice_lines.amount%TYPE;
831 l_debug_info VARCHAR2(100);
832 current_calling_sequence VARCHAR2(2000);
833 l_api_name VARCHAR2(30);
834 l_line_base_amount NUMBER;
835 l_success BOOLEAN := TRUE;
836 l_error_code VARCHAR2(2000);
837 Tax_Exception Exception;
838
839
840 BEGIN
841
842 l_api_name := 'Create_Invoice_Lines_Dists';
843
844 current_calling_sequence := 'Create_Invoice_Lines_Dists<-'||x_calling_sequence;
845
846
847 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
848 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_INVOICE_CORRECTIONS_PKG.Create_Invoice_Lines_Dists(+)');
849 END IF;
850
851
852 l_invoice_line_number := g_max_invoice_line_number + 1;
853
854 l_debug_info := 'Insert Invoice Lines for this correction';
855
856 FOR i IN nvl(x_line_tab.first,0).. nvl(x_line_tab.last,0) LOOP
857
858 IF (x_line_tab.exists(i)) THEN
859
860 x_line_tab(i).line_number := l_invoice_line_number;
861
862 Insert_Invoice_Line(X_Invoice_Id => x_invoice_id,
863 X_Invoice_Line_Number => l_invoice_line_number,
864 X_Corrected_Invoice_Id => x_corrected_invoice_id,
865 X_Corrected_Line_Number => x_line_tab(i).corrected_line_number,
866 X_Amount => x_line_tab(i).line_amount,
867 X_Base_Amount => x_line_tab(i).base_amount,
868 X_Rounding_Amt => x_line_tab(i).rounding_amt,
869 X_Correction_Quantity => x_correction_quantity,
870 X_Correction_Price => x_correction_price,
871 X_Calling_Sequence => current_calling_sequence);
872
873 l_invoice_line_number := l_invoice_line_number + 1;
874
875 END IF;
876
877 END LOOP;
878
879 -- Bug 5597409. Calling eTax
880 l_debug_info := 'Calculate Tax on the Invoice';
881
882
883 l_success := ap_etax_pkg.calling_etax
884 (p_invoice_id => x_invoice_id,
885 p_calling_mode => 'CALCULATE',
886 p_all_error_messages => 'N',
887 p_error_code => l_error_code,
888 p_calling_sequence => current_calling_sequence);
889
890 IF (NOT l_success) THEN
891 Raise Tax_Exception;
892 END IF;
893
894 FOR i IN nvl(x_line_tab.first,0).. nvl(x_line_tab.last,0) LOOP
895
896 IF (x_line_tab.exists(i)) THEN
897
898 Begin
899 Select nvl(included_tax_amount, 0)
900 Into x_line_tab(i).included_tax_amount
901 From ap_invoice_lines_all
902 Where invoice_id = x_invoice_id
903 And line_number = x_line_tab(i).line_number;
904
905 Exception
906 When Others Then
907 x_line_tab(i).included_tax_amount := 0;
908 End ;
909
910 END IF;
911
912 END LOOP;
913
914 FOR i IN nvl(x_line_tab.first,0).. nvl(x_line_tab.last,0) LOOP
915
916 IF (x_line_tab.exists(i)) THEN
917
918 Get_Dist_Proration_Info(
919 X_Corrected_Invoice_Id => x_corrected_invoice_id,
920 X_Corrected_Line_Number =>x_line_tab(i).corrected_line_number,
921 X_Line_Amount => x_line_tab(i).line_amount,
922 X_Line_Base_Amount => x_line_tab(i).base_amount,
923 X_Included_Tax_Amount => x_line_tab(i).included_tax_amount,
924 X_Dist_Tab => x_dist_tab,
925 X_Prorate_Dists_Flag => x_prorate_dists_flag,
926 X_Calling_Sequence => current_calling_sequence);
927
928 Insert_Invoice_Distributions(
929 X_Invoice_ID => x_invoice_id,
930 X_Invoice_Line_Number => x_line_tab(i).line_number,
931 X_Dist_Tab => x_dist_tab,
932 X_Line_Amount => x_line_tab(i).line_amount,
933 X_Calling_Sequence => current_calling_sequence);
934
935 l_invoice_line_number := l_invoice_line_number + 1;
936
937 END IF;
938
939 END LOOP;
940
941 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
942 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_INVOICE_CORRECTIONS_PKG.Create_Invoice_Lines_Dists(-)');
943 END IF;
944
945
946
947 EXCEPTION WHEN OTHERS THEN
948 IF (SQLCODE <> -20001) THEN
949 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
950 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
951 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
952 FND_MESSAGE.SET_TOKEN('PARAMETERS','Invoice_Id = '||to_char(x_invoice_id)
953 ||', Corrected Invoice Id = '||to_char(x_corrected_invoice_id)
954 ||', Corrected line Number = '||to_char(x_corrected_line_number)
955 ||', Correction Quantity = '||to_char(x_correction_quantity)
956 ||', Correction Price = '||to_char(x_correction_price)
957 ||', Total Correction Amount = '||to_char(x_total_correction_amount));
958
959 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
960 END IF;
961 --Clean up the PL/SQL tables.
962 x_line_tab.delete;
963 x_dist_tab.delete;
964
965 APP_EXCEPTION.RAISE_EXCEPTION;
966
967 END Create_Invoice_Lines_Dists;
968
969
970
971 Procedure Insert_Invoice_Line (X_Invoice_Id IN NUMBER,
972 X_Invoice_Line_Number IN NUMBER,
973 X_Corrected_Invoice_Id IN NUMBER,
974 X_Corrected_Line_Number IN NUMBER,
975 X_Amount IN NUMBER,
976 X_Base_Amount IN NUMBER,
977 X_Rounding_Amt IN NUMBER,
978 X_Correction_Quantity IN NUMBER,
979 X_Correction_Price IN NUMBER,
980 X_Calling_Sequence IN VARCHAR2) IS
981
982 current_calling_sequence VARCHAR2(2000);
983 l_debug_info VARCHAR2(100);
984 l_api_name VARCHAR2(30);
985
986 BEGIN
987
988 l_api_name := 'Insert_Invoice_Line';
989
990 current_calling_sequence := 'Insert_Invoice_Line<-'||x_calling_sequence;
991
992 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
993 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_INVOICE_CORRECTIONS_PKG.Insert_Invoice_Lines(+)');
994 END IF;
995
996
997 l_debug_info := 'Inserting non-matched Item Line';
998
999 INSERT INTO AP_INVOICE_LINES_ALL (
1000 INVOICE_ID,
1001 LINE_NUMBER,
1002 LINE_TYPE_LOOKUP_CODE,
1003 REQUESTER_ID,
1004 DESCRIPTION,
1005 LINE_SOURCE,
1006 ORG_ID,
1007 INVENTORY_ITEM_ID,
1008 ITEM_DESCRIPTION,
1009 SERIAL_NUMBER,
1010 MANUFACTURER,
1011 MODEL_NUMBER,
1012 GENERATE_DISTS,
1013 MATCH_TYPE,
1014 DISTRIBUTION_SET_ID,
1015 ACCOUNT_SEGMENT,
1016 BALANCING_SEGMENT,
1017 COST_CENTER_SEGMENT,
1018 OVERLAY_DIST_CODE_CONCAT,
1019 DEFAULT_DIST_CCID,
1020 PRORATE_ACROSS_ALL_ITEMS,
1021 LINE_GROUP_NUMBER,
1022 ACCOUNTING_DATE,
1023 PERIOD_NAME,
1024 DEFERRED_ACCTG_FLAG,
1025 DEF_ACCTG_START_DATE,
1026 DEF_ACCTG_END_DATE,
1027 DEF_ACCTG_NUMBER_OF_PERIODS,
1028 DEF_ACCTG_PERIOD_TYPE,
1029 SET_OF_BOOKS_ID,
1030 AMOUNT,
1031 BASE_AMOUNT,
1032 ROUNDING_AMT,
1033 QUANTITY_INVOICED,
1034 UNIT_MEAS_LOOKUP_CODE,
1035 UNIT_PRICE,
1036 WFAPPROVAL_STATUS,
1037 -- USSGL_TRANSACTION_CODE, - Bug 4277744
1038 DISCARDED_FLAG,
1039 ORIGINAL_AMOUNT,
1040 ORIGINAL_BASE_AMOUNT,
1041 ORIGINAL_ROUNDING_AMT,
1042 CANCELLED_FLAG,
1043 INCOME_TAX_REGION,
1044 TYPE_1099,
1045 STAT_AMOUNT,
1046 PREPAY_INVOICE_ID,
1047 PREPAY_LINE_NUMBER,
1048 INVOICE_INCLUDES_PREPAY_FLAG,
1049 CORRECTED_INV_ID,
1050 CORRECTED_LINE_NUMBER,
1051 PO_HEADER_ID,
1052 PO_LINE_ID,
1053 PO_RELEASE_ID,
1054 PO_LINE_LOCATION_ID,
1055 PO_DISTRIBUTION_ID,
1056 RCV_TRANSACTION_ID,
1057 FINAL_MATCH_FLAG,
1058 ASSETS_TRACKING_FLAG,
1059 ASSET_BOOK_TYPE_CODE,
1060 ASSET_CATEGORY_ID,
1061 PROJECT_ID,
1062 TASK_ID,
1063 EXPENDITURE_TYPE,
1064 EXPENDITURE_ITEM_DATE,
1065 EXPENDITURE_ORGANIZATION_ID,
1066 PA_QUANTITY,
1067 PA_CC_AR_INVOICE_ID,
1068 PA_CC_AR_INVOICE_LINE_NUM,
1069 PA_CC_PROCESSED_CODE,
1070 AWARD_ID,
1071 AWT_GROUP_ID,
1072 REFERENCE_1,
1073 REFERENCE_2,
1074 RECEIPT_VERIFIED_FLAG,
1075 RECEIPT_REQUIRED_FLAG,
1076 RECEIPT_MISSING_FLAG,
1077 JUSTIFICATION,
1078 EXPENSE_GROUP,
1079 START_EXPENSE_DATE,
1080 END_EXPENSE_DATE,
1081 RECEIPT_CURRENCY_CODE,
1082 RECEIPT_CONVERSION_RATE,
1083 RECEIPT_CURRENCY_AMOUNT,
1084 DAILY_AMOUNT,
1085 WEB_PARAMETER_ID,
1086 ADJUSTMENT_REASON,
1087 MERCHANT_DOCUMENT_NUMBER,
1088 MERCHANT_NAME,
1089 MERCHANT_REFERENCE,
1090 MERCHANT_TAX_REG_NUMBER,
1091 MERCHANT_TAXPAYER_ID,
1092 COUNTRY_OF_SUPPLY,
1093 CREDIT_CARD_TRX_ID,
1094 COMPANY_PREPAID_INVOICE_ID,
1095 CC_REVERSAL_FLAG,
1096 ATTRIBUTE_CATEGORY,
1097 ATTRIBUTE1,
1098 ATTRIBUTE2,
1099 ATTRIBUTE3,
1100 ATTRIBUTE4,
1101 ATTRIBUTE5,
1102 ATTRIBUTE6,
1103 ATTRIBUTE7,
1104 ATTRIBUTE8,
1105 ATTRIBUTE9,
1106 ATTRIBUTE10,
1107 ATTRIBUTE11,
1108 ATTRIBUTE12,
1109 ATTRIBUTE13,
1110 ATTRIBUTE14,
1111 ATTRIBUTE15,
1112 /* GLOBAL_ATTRIBUTE_CATEGORY,
1113 GLOBAL_ATTRIBUTE1,
1114 GLOBAL_ATTRIBUTE2,
1115 GLOBAL_ATTRIBUTE3,
1116 GLOBAL_ATTRIBUTE4,
1117 GLOBAL_ATTRIBUTE5,
1118 GLOBAL_ATTRIBUTE6,
1119 GLOBAL_ATTRIBUTE7,
1120 GLOBAL_ATTRIBUTE8,
1121 GLOBAL_ATTRIBUTE9,
1122 GLOBAL_ATTRIBUTE10,
1123 GLOBAL_ATTRIBUTE11,
1124 GLOBAL_ATTRIBUTE12,
1125 GLOBAL_ATTRIBUTE13,
1126 GLOBAL_ATTRIBUTE14,
1127 GLOBAL_ATTRIBUTE15,
1128 GLOBAL_ATTRIBUTE16,
1129 GLOBAL_ATTRIBUTE17,
1130 GLOBAL_ATTRIBUTE18,
1131 GLOBAL_ATTRIBUTE19,
1132 GLOBAL_ATTRIBUTE20, */
1133 CREATION_DATE,
1134 CREATED_BY,
1135 LAST_UPDATED_BY,
1136 LAST_UPDATE_DATE,
1137 LAST_UPDATE_LOGIN,
1138 PROGRAM_APPLICATION_ID,
1139 PROGRAM_ID,
1140 PROGRAM_UPDATE_DATE,
1141 REQUEST_ID,
1142 --ETAX: Invwkb
1143 TAX_CLASSIFICATION_CODE, --Bug 8717668
1144 SHIP_TO_LOCATION_ID,
1145 PRIMARY_INTENDED_USE,
1146 PRODUCT_FISC_CLASSIFICATION,
1147 TRX_BUSINESS_CATEGORY,
1148 PRODUCT_TYPE,
1149 PRODUCT_CATEGORY,
1150 USER_DEFINED_FISC_CLASS,
1151 PURCHASING_CATEGORY_ID,
1152 PAY_AWT_GROUP_ID --Bug13793337
1153 )
1154 SELECT x_invoice_id, --invoice_id
1155 x_invoice_line_number, --line_number
1156 ail.line_type_lookup_code, --line_type_lookup_code
1157 ail.requester_id, --requester_id
1158 ail.description, --description
1159 'HEADER CORRECTION', --line_source
1160 ail.org_id, --org_id
1161 ail.inventory_item_id, --inventory_item_id
1162 ail.item_description, --item_description
1163 ail.serial_number, --serial_number
1164 ail.manufacturer, --manufacturer
1165 ail.model_number, --model_number
1166 'D', --generate_dists
1167 'LINE_CORRECTION', --match_type
1168 NULL, --distribution_set_id
1169 ail.account_segment, --account_segment
1170 ail.balancing_segment, --balancing_segment
1171 ail.cost_center_segment, --cost_center_segment
1172 ail.overlay_dist_code_concat, --overlay_dist_code_concat
1173 ail.default_dist_ccid, --default_dist_ccid
1174 'N', --prorate_across_all_items
1175 NULL, --line_group_number
1176 g_accounting_date, --accounting_date
1177 g_period_name, --period_name
1178 'N', --deferred_acctg_flag
1179 NULL, --def_acctg_start_date
1180 NULL, --def_acctg_end_date
1181 NULL, --def_acctg_number_of_periods
1182 NULL, --def_acctg_period_type
1183 g_set_of_books_id, --set_of_books_id
1184 x_amount, --amount
1185 x_base_amount, --base_amount
1186 x_rounding_amt, --rounding_amount
1187 x_correction_quantity, --quantity_invoiced
1188 decode(x_correction_quantity,'','',
1189 ail.unit_meas_lookup_code), --unit_meas_lookup_code
1190 x_correction_price, --unit_price
1191 decode(g_approval_workflow_flag,'Y',
1192 'REQUIRED','NOT REQUIRED'), --wf_approval_status
1193 -- Removed for bug 4277744
1194 -- g_ussgl_transaction_code, --ussgl_transaction_code
1195 'N', --discarded_flag
1196 NULL, --original_amount
1197 NULL, --original_base_amount
1198 NULL, --original_rounding_amt
1199 'N', --cancelled_flag
1200 g_income_tax_region, --income_tax_region
1201 g_type_1099, --type_1099
1202 NULL, --stat_amount
1203 NULL, --prepay_invoice_id
1204 NULL, --prepay_line_number
1205 NULL, --invoice_includes_prepay_flag
1206 x_corrected_invoice_id, --corrected_inv_id
1207 x_corrected_line_number, --corrected_line_number
1208 NULL, --po_header_id
1209 NULL, --po_line_id
1210 NULL, --po_release_id
1211 NULL, --po_line_location_id
1212 NULL, --po_distribution_id
1213 NULL, --rcv_transaction_id
1214 NULL, --final_match_flag
1215 ail.assets_tracking_flag, --assets_tracking_flag
1216 ail.asset_book_type_code, --asset_book_type_code
1217 ail.asset_category_id, --asset_category_id
1218 ail.project_id, --project_id
1219 ail.task_id, --task_id
1220 ail.expenditure_type, --expenditure_type
1221 ail.expenditure_item_date, --expenditure_item_date
1222 ail.expenditure_organization_id, --expenditure_organization_id
1223 x_correction_quantity, --pa_quantity
1224 NULL, --pa_cc_ar_invoice_id
1225 NULL, --pa_cc_ar_invoice_line_num
1226 NULL, --pa_cc_processed_code
1227 ail.award_id, --award_id
1228 g_awt_group_id, --awt_group_id
1229 ail.reference_1, --reference_1
1230 ail.reference_2, --reference_2
1231 ail.receipt_verified_flag, --receipt_verified_flag
1232 ail.receipt_required_flag, --receipt_required_flag
1233 ail.receipt_missing_flag, --receipt_missing_flag
1234 ail.justification, --justification
1235 ail.expense_group, --expense_group
1236 ail.start_expense_date, --start_expense_date
1237 ail.end_expense_date, --end_expense_date
1238 ail.receipt_currency_code, --receipt_currency_code
1239 ail.receipt_conversion_rate, --receipt_conversion_rate
1240 ail.receipt_currency_amount, --receipt_currency_amount
1241 ail.daily_amount, --daily_amount
1242 ail.web_parameter_id, --web_parameter_id
1243 ail.adjustment_reason, --adjustment_reason
1244 ail.merchant_document_number, --merchant_document_number
1245 ail.merchant_name, --merchant_name
1246 ail.merchant_reference, --merchant_reference
1247 ail.merchant_tax_reg_number, --merchant_tax_reg_number
1248 ail.merchant_taxpayer_id, --merchant_taxpayer_id
1249 ail.country_of_supply, --country_of_supply
1250 ail.credit_card_trx_id, --credit_card_trx_id
1251 ail.company_prepaid_invoice_id, --company_prepaid_invoice_id
1252 ail.cc_reversal_flag, --cc_reversal_flag
1253 ail.attribute_category, --attribute_category
1254 ail.attribute1, --attribute1
1255 ail.attribute2, --attribute2
1256 ail.attribute3, --attribute3
1257 ail.attribute4, --attribute4
1258 ail.attribute5, --attribute5
1259 ail.attribute6, --attribute6
1260 ail.attribute7, --attribute7
1261 ail.attribute8, --attribute8
1262 ail.attribute9, --attribute9
1263 ail.attribute10, --attribute10
1264 ail.attribute11, --attribute11
1265 ail.attribute12, --attribute12
1266 ail.attribute13, --attribute13
1267 ail.attribute14, --attribute14
1268 ail.attribute15, --attribute15
1269 /*OPEN ISSUE 1*/
1270 /* X_GLOBAL_ATTRIBUTE_CATEGORY, --global_attribute_category
1271 X_GLOBAL_ATTRIBUTE1, --global_attribute1
1272 X_GLOBAL_ATTRIBUTE2, --global_attribute2
1273 X_GLOBAL_ATTRIBUTE3, --global_attribute3
1274 X_GLOBAL_ATTRIBUTE4, --global_attribute4
1275 X_GLOBAL_ATTRIBUTE5, --global_attribute5
1276 X_GLOBAL_ATTRIBUTE6, --global_attribute6
1277 X_GLOBAL_ATTRIBUTE7, --global_attribute7
1278 X_GLOBAL_ATTRIBUTE8, --global_attribute8
1279 X_GLOBAL_ATTRIBUTE9, --global_attribute9
1280 X_GLOBAL_ATTRIBUTE10, --global_attribute10
1281 X_GLOBAL_ATTRIBUTE11, --global_attribute11
1282 X_GLOBAL_ATTRIBUTE12, --global_attribute12
1283 X_GLOBAL_ATTRIBUTE13, --global_attribute13
1284 X_GLOBAL_ATTRIBUTE14, --global_attribute14
1285 X_GLOBAL_ATTRIBUTE15, --global_attribute15
1286 X_GLOBAL_ATTRIBUTE16, --global_attribute16
1287 X_GLOBAL_ATTRIBUTE17, --global_attribute17
1288 X_GLOBAL_ATTRIBUTE18, --global_attribute18
1289 X_GLOBAL_ATTRIBUTE19, --global_attribute19
1290 X_GLOBAL_ATTRIBUTE20, */ --global_attribute20
1291 sysdate, --creation_date
1292 g_user_id, --created_by
1293 g_user_id, --last_update_by
1294 sysdate, --last_update_date
1295 g_login_id, --last_update_login
1296 NULL, --program_application_id
1297 NULL, --program_id
1298 NULL, --program_update_date
1299 NULL, --request_id
1300 --ETAX: Invwkb
1301 TAX_CLASSIFICATION_CODE, --Tax Classification Code Bug 8717668
1302 AIL.SHIP_TO_LOCATION_ID, --ship_to_location_id
1303 AIL.PRIMARY_INTENDED_USE, --primary_intended_use
1304 AIL.PRODUCT_FISC_CLASSIFICATION, --product_fisc_classification
1305 AIL.TRX_BUSINESS_CATEGORY, --trx_business_category --bug10085965
1306 AIL.PRODUCT_TYPE, --product_type
1307 AIL.PRODUCT_CATEGORY, --product_category
1308 AIL.USER_DEFINED_FISC_CLASS, --user_defined_fisc_class
1309 AIL.PURCHASING_CATEGORY_ID, --purchasing_category_id
1310 AIL.PAY_AWT_GROUP_ID --pay_awt_group_id --bug13793337
1311 FROM ap_invoices ai,
1312 ap_invoice_lines ail
1313 WHERE ai.invoice_id = ail.invoice_id
1314 AND ai.invoice_id = x_corrected_invoice_id
1315 AND ail.line_number = x_corrected_line_number;
1316
1317
1318 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1319 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_INVOICE_CORRECTIONS_PKG.Insert_Invoice_Lines(-)');
1320 END IF;
1321
1322
1323 EXCEPTION WHEN OTHERS THEN
1324 IF (SQLCODE <> -20001) THEN
1325 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1326 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1327 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
1328 FND_MESSAGE.SET_TOKEN('PARAMETERS','Invoice_Id = '||to_char(x_invoice_id)
1329 ||', Invoice Line Number = '||to_char(x_invoice_line_number)
1330 ||', Corrected Invoice Id = '||to_char(x_corrected_invoice_id)
1331 ||', Corrected line Number = '||to_char(x_corrected_line_number));
1332
1333 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
1334 END IF;
1335
1336 APP_EXCEPTION.RAISE_EXCEPTION;
1337
1338
1339 END Insert_Invoice_Line;
1340
1341
1342
1343 PROCEDURE Insert_Invoice_Distributions (X_Invoice_ID IN NUMBER,
1344 X_Invoice_Line_Number IN NUMBER,
1345 X_Dist_Tab IN OUT NOCOPY Dist_Tab_Type,
1346 X_Line_Amount IN NUMBER,
1347 X_Calling_Sequence IN VARCHAR2) IS
1348 l_distribution_line_number NUMBER := 1;
1349 l_rounding_amount NUMBER;
1350 l_sum_prorated_amount NUMBER := 0;
1351 l_rounded_index ap_invoice_distributions.invoice_distribution_id%type;
1352 i NUMBER;
1353 l_max_dist_amount NUMBER := 0;
1354 l_max_distribution_id ap_invoice_distributions.invoice_distribution_id%type;
1355 l_debug_info VARCHAR2(100);
1356 current_calling_sequence VARCHAR2(2000);
1357 l_api_name VARCHAR2(30);
1358
1359 BEGIN
1360
1361 l_api_name := 'Insert_Invoice_Distributions';
1362
1363 current_calling_sequence := 'Insert_Invoice_Distributions <-'||current_calling_sequence;
1364
1365 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1366 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_INVOICE_CORRECTIONS_PKG.Insert_Invoice_Distributions(+)');
1367 END IF;
1368
1369 l_debug_info := 'Insert Invoice Distributions';
1370
1371 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1372 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1373 END IF;
1374
1375 FOR i in nvl(X_Dist_tab.FIRST, 0) .. nvl(X_Dist_tab.LAST, 0) LOOP
1376
1377 IF (x_dist_tab.exists(i)) THEN
1378
1379
1380 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1381 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1382 END IF;
1383
1384 INSERT INTO ap_invoice_distributions(
1385 batch_id,
1386 invoice_id,
1387 invoice_line_number,
1388 invoice_distribution_id,
1389 distribution_line_number,
1390 line_type_lookup_code,
1391 description,
1392 dist_match_type,
1393 distribution_class,
1394 org_id,
1395 dist_code_combination_id,
1396 accounting_date,
1397 period_name,
1398 accrual_posted_flag,
1399 cash_posted_flag,
1400 amount_to_post,
1401 base_amount_to_post,
1402 posted_amount,
1403 posted_base_amount,
1404 je_batch_id,
1405 cash_je_batch_id,
1406 posted_flag,
1407 accounting_event_id,
1408 upgrade_posted_amt,
1409 upgrade_base_posted_amt,
1410 set_of_books_id,
1411 amount,
1412 base_amount,
1413 rounding_amt,
1414 match_status_flag,
1415 encumbered_flag,
1416 packet_id,
1417 -- Removed for bug 4277744
1418 -- ussgl_transaction_code,
1419 -- ussgl_trx_code_context,
1420 reversal_flag,
1421 parent_reversal_id,
1422 cancellation_flag,
1423 income_tax_region,
1424 type_1099,
1425 stat_amount,
1426 charge_applicable_to_dist_id,
1427 prepay_amount_remaining,
1428 prepay_distribution_id,
1429 parent_invoice_id,
1430 corrected_invoice_dist_id,
1431 corrected_quantity,
1432 other_invoice_id,
1433 po_distribution_id,
1434 rcv_transaction_id,
1435 unit_price,
1436 matched_uom_lookup_code,
1437 quantity_invoiced,
1438 final_match_flag,
1439 related_id,
1440 assets_addition_flag,
1441 assets_tracking_flag,
1442 asset_book_type_code,
1443 asset_category_id,
1444 project_id,
1445 task_id,
1446 expenditure_type,
1447 expenditure_item_date,
1448 expenditure_organization_id,
1449 pa_quantity,
1450 pa_addition_flag,
1451 pa_cc_ar_invoice_id,
1452 pa_cc_ar_invoice_line_num,
1453 pa_cc_processed_code,
1454 award_id,
1455 gms_burdenable_raw_cost,
1456 awt_flag,
1457 awt_group_id,
1458 awt_tax_rate_id,
1459 awt_gross_amount,
1460 awt_invoice_id,
1461 awt_origin_group_id,
1462 awt_invoice_payment_id,
1463 awt_withheld_amt,
1464 inventory_transfer_status,
1465 reference_1,
1466 reference_2,
1467 receipt_verified_flag,
1468 receipt_required_flag,
1469 receipt_missing_flag,
1470 justification,
1471 expense_group,
1472 start_expense_date,
1473 end_expense_date,
1474 receipt_currency_code,
1475 receipt_conversion_rate,
1476 receipt_currency_amount,
1477 daily_amount,
1478 web_parameter_id,
1479 adjustment_reason,
1480 merchant_document_number,
1481 merchant_name,
1482 merchant_reference,
1483 merchant_tax_reg_number,
1484 merchant_taxpayer_id,
1485 country_of_supply,
1486 credit_card_trx_id,
1487 company_prepaid_invoice_id,
1488 cc_reversal_flag,
1489 attribute_category,
1490 attribute1,
1491 attribute2,
1492 attribute3,
1493 attribute4,
1494 attribute5,
1495 attribute6,
1496 attribute7,
1497 attribute8,
1498 attribute9,
1499 attribute10,
1500 attribute11,
1501 attribute12,
1502 attribute13,
1503 attribute14,
1504 attribute15,
1505 /*global_attribute_category,
1506 global_attribute1,
1507 global_attribute2,
1508 global_attribute3,
1509 global_attribute4,
1510 global_attribute5,
1511 global_attribute6,
1512 global_attribute7,
1513 global_attribute8,
1514 global_attribute9,
1515 global_attribute10,
1516 global_attribute11,
1517 global_attribute12,
1518 global_attribute13,
1519 global_attribute14,
1520 global_attribute15,
1521 global_attribute16,
1522 global_attribute17,
1523 global_attribute18,
1524 global_attribute19,
1525 global_attribute20,*/
1526 created_by,
1527 creation_date,
1528 last_updated_by,
1529 last_update_date,
1530 last_update_login,
1531 program_application_id,
1532 program_id,
1533 program_update_date,
1534 request_id,
1535 --ETAX: Invwkb
1536 intended_use,
1537 --Freight and Special Charges
1538 rcv_charge_addition_flag,
1539 pay_awt_group_id --bug13793337
1540 )
1541 SELECT g_batch_id, --batch_id
1542 x_invoice_id, --invoice_id
1543 x_invoice_line_number, --invoice_line_number
1544 NVL(x_dist_tab(i).invoice_distribution_id,
1545 ap_invoice_distributions_s.nextval), --invoice_distribution_id
1546 l_distribution_line_number, --distribution_line_number
1547 aid.line_type_lookup_code, --line_type_lookup_code
1548 --Modified ail.item_description as description for bug#10200174
1549 ail.description, --description
1550 'DIST_CORRECTION', --dist_match_type
1551 'PERMANENT', --distribution_class
1552 ail.org_id, --org_id
1553 aid.dist_code_combination_id, --dist_code_combination_id
1554 ail.accounting_date, --accounting_date
1555 ail.period_name, --period_name
1556 'N', --accrual_posted_flag
1557 'N', --cash_posted_flag
1558 NULL, --amount_to_post
1559 NULL, --base_amount_to_post
1560 NULL, --posted_amount
1561 NULL, --posted_base_amount
1562 NULL, --je_batch_id
1563 NULL, --cash_je_batch_id
1564 'N', --posted_flag
1565 NULL, --accounting_event_id
1566 NULL, --upgrade_posted_amt
1567 NULL, --upgrade_base_posted_amt
1568 g_set_of_books_id, --set_of_books_id
1569 x_dist_tab(i).amount, --amount
1570 x_dist_tab(i).base_amount, --base_amount
1571 x_dist_tab(i).rounding_amt, --rounding_amount
1572 NULL, --match_status_flag
1573 'N', --encumbered_flag
1574 NULL, --packet_id
1575 -- Removed for bug 4277744
1576 -- ail.ussgl_transaction_code, --ussgl_transaction_code
1577 -- NULL, --ussgl_trx_code_context
1578 'N', --reversal_flag
1579 NULL, --parent_reversal_id
1580 'N', --cancellation_flag
1581 aid.income_tax_region, --income_tax_region
1582 aid.type_1099, --type_1099
1583 ap_utilities_pkg.ap_round_currency(
1584 (x_dist_tab(i).amount * aid.stat_amount)/aid.amount,
1585 'STAT'), --stat_amount
1586 NULL, --charge_applicable_to_dist_id
1587 NULL, --prepay_amount_remaining
1588 NULL, --prepay_distribution_id
1589 aid.invoice_id, --parent_invoice_id
1590 x_dist_tab(i).corrected_inv_dist_id, --corrected_invoice_dist_id
1591 NULL, --corrected_quantity
1592 NULL, --other_invoice_id
1593 NULL, --po_distribution_id
1594 NULL, --rcv_transaction_id
1595 NULL, --unit_price
1596 NULL, --matched_uom_lookup_code
1597 NULL, --quantity_invoiced
1598 NULL, --final_match_flag
1599 NULL, --related_id
1600 'U', --assets_addition_flag
1601 decode(gcc.account_type,'E',
1602 ail.assets_tracking_flag,
1603 'A','Y','N'), --assets_tracking_flag
1604 decode(decode(gcc.account_type,'E',
1605 ail.assets_tracking_flag,'A','Y','N'),
1606 'Y',ail.asset_book_type_code,NULL),--asset_book_type_code
1607 decode(decode(gcc.account_type,'E',
1608 ail.assets_tracking_flag,'A','Y','N'),
1609 'Y',ail.asset_category_id,NULL), --asset_category_id
1610 aid.project_id, --project_id
1611 aid.task_id, --task_id
1612 aid.expenditure_type, --expenditure_type
1613 aid.expenditure_item_date, --expenditure_item_date
1614 aid.expenditure_organization_id, --expenditure_organization_id
1615 x_dist_tab(i).amount * aid.pa_quantity/aid.amount, --pa_quantity
1616 decode(aid.project_id,NULL, 'E', 'N'), --pa_addition_flag
1617 NULL, --pa_cc_ar_invoice_id
1618 NULL, --pa_cc_ar_invoice_line_num
1619 NULL, --pa_cc_processed_code
1620 aid.award_id, --award_id
1621 NULL, --gms_burdenable_raw_cost
1622 NULL, --awt_flag
1623 decode(g_system_allow_awt_flag,'Y',
1624 decode(g_site_allow_awt_flag,'Y',ail.awt_group_id,NULL),
1625 NULL), --awt_group_id
1626 NULL, --awt_tax_rate_id
1627 NULL, --awt_gross_amount
1628 NULL, --awt_invoice_id
1629 NULL, --awt_origin_group_id
1630 NULL, --awt_invoice_payment_id
1631 NULL, --awt_withheld_amt
1632 'N', --inventory_transfer_status
1633 aid.reference_1, --reference_1
1634 aid.reference_2, --reference_2
1635 aid.receipt_verified_flag, --receipt_verified_flag
1636 aid.receipt_required_flag, --receipt_required_flag
1637 aid.receipt_missing_flag, --receipt_missing_flag
1638 aid.justification, --justification
1639 aid.expense_group, --expense_group
1640 aid.start_expense_date, --start_expense_date
1641 aid.end_expense_date, --end_expense_date
1642 aid.receipt_currency_code, --receipt_currency_code
1643 aid.receipt_conversion_rate, --receipt_conversion_rate
1644 aid.receipt_currency_amount, --receipt_currency_amount
1645 aid.daily_amount, --daily_amount
1646 aid.web_parameter_id, --web_parameter_id
1647 aid.adjustment_reason, --adjustment_reason
1648 aid.merchant_document_number, --merchant_document_number
1649 aid.merchant_name, --merchant_name
1650 aid.merchant_reference, --merchant_reference
1651 aid.merchant_tax_reg_number, --merchant_tax_reg_number
1652 aid.merchant_taxpayer_id, --merchant_taxpayer_id
1653 aid.country_of_supply, --country_of_supply
1654 aid.credit_card_trx_id, --credit_card_trx_id
1655 aid.company_prepaid_invoice_id, --company_prepaid_invoice_id
1656 aid.cc_reversal_flag, --cc_reversal_flag
1657 aid.attribute_category, --attribute_category
1658 aid.attribute1, --attribute1
1659 aid.attribute2, --attribute2
1660 aid.attribute3, --attribute3
1661 aid.attribute4, --attribute4
1662 aid.attribute5, --attribute5
1663 aid.attribute6, --attribute6
1664 aid.attribute7, --attribute7
1665 aid.attribute8, --attribute8
1666 aid.attribute9, --attribute9
1667 aid.attribute10, --attribute10
1668 aid.attribute11, --attribute11
1669 aid.attribute12, --attribute12
1670 aid.attribute13, --attribute13
1671 aid.attribute14, --attribute14
1672 aid.attribute15, --attribute15
1673 /* X_GLOBAL_ATTRIBUTE_CATEGORY,
1674 X_GLOBAL_ATTRIBUTE1,
1675 X_GLOBAL_ATTRIBUTE2,
1676 X_GLOBAL_ATTRIBUTE3,
1677 X_GLOBAL_ATTRIBUTE4,
1678 X_GLOBAL_ATTRIBUTE5,
1679 X_GLOBAL_ATTRIBUTE6,
1680 X_GLOBAL_ATTRIBUTE7,
1681 X_GLOBAL_ATTRIBUTE8,
1682 X_GLOBAL_ATTRIBUTE9,
1683 X_GLOBAL_ATTRIBUTE10,
1684 X_GLOBAL_ATTRIBUTE11,
1685 X_GLOBAL_ATTRIBUTE12,
1686 X_GLOBAL_ATTRIBUTE13,
1687 X_GLOBAL_ATTRIBUTE14,
1688 X_GLOBAL_ATTRIBUTE15,
1689 X_GLOBAL_ATTRIBUTE16,
1690 X_GLOBAL_ATTRIBUTE17,
1691 X_GLOBAL_ATTRIBUTE18,
1692 X_GLOBAL_ATTRIBUTE19,
1693 X_GLOBAL_ATTRIBUTE20, */
1694 ail.created_by, --created_by
1695 sysdate, --creation_date
1696 ail.last_updated_by, --last_updated_by
1697 sysdate, --last_update_date
1698 ail.last_update_login, --last_update_login
1699 NULL, --program_application_id
1700 NULL, --program_id
1701 NULL, --program_update_date
1702 NULL, --request_id
1703 --ETAX: Invwkb
1704 aid.intended_use, --intended_use
1705 'N',
1706 aid.pay_awt_group_id --pay_awt_group_id --bug13793337
1707 FROM ap_invoice_distributions aid,
1708 ap_invoice_lines ail,
1709 gl_code_combinations gcc
1710 WHERE ail.invoice_id = x_invoice_id
1711 AND ail.line_number = x_invoice_line_number
1712 AND aid.invoice_id = ail.corrected_inv_id
1713 AND aid.invoice_line_number = ail.corrected_line_number
1714 AND aid.invoice_distribution_id = x_dist_tab(i).corrected_inv_dist_id
1715 AND gcc.code_combination_id = aid.dist_code_combination_id;
1716
1717
1718 l_distribution_line_number := l_distribution_line_number + 1;
1719
1720 END IF; /*x_dist_tab.exists(i))*/
1721
1722 END LOOP;
1723
1724
1725 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1726 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_INVOICE_CORRECTIONS_PKG.Insert_Invoice_Distributions(-)');
1727 END IF;
1728
1729
1730 EXCEPTION
1731 WHEN OTHERS THEN
1732
1733 IF (SQLCODE <> -20001) THEN
1734 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1735 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1736 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
1737 FND_MESSAGE.SET_TOKEN('PARAMETERS','Batch_Id = '||TO_CHAR(g_Batch_Id)
1738 ||', Invoice_id = '||TO_CHAR(X_invoice_id)
1739 ||', Invoice Line Number = '||X_Invoice_Line_Number
1740 ||', Dist_num = '||l_distribution_line_number);
1741 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
1742 END IF;
1743 --Clean up the PL/SQL tables.
1744 X_DIST_TAB.DELETE;
1745
1746 APP_EXCEPTION.RAISE_EXCEPTION;
1747
1748 END Insert_Invoice_Distributions;
1749
1750
1751 END AP_INVOICE_CORRECTIONS_PKG;