[Home] [Help]
PACKAGE BODY: APPS.AP_INVOICE_CORRECTIONS_PKG
Source
1 PACKAGE BODY AP_INVOICE_CORRECTIONS_PKG AS
2 /*$Header: apinvcob.pls 120.18 2007/12/21 08:07:25 ssuhasar 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
358 g_org_id);
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,
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 AND ail.line_type_lookup_code = 'ITEM'; -- Bug 5597409
413
414 RETURN(l_line_total);
415
416 EXCEPTION WHEN OTHERS THEN
417 IF (SQLCODE <> -20001) THEN
418 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
419 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
420 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
421 FND_MESSAGE.SET_TOKEN('PARAMETERS','Corrected Invoice Id = '||TO_CHAR(X_Corrected_Invoice_Id));
422 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
423 END IF;
424 app_exception.raise_exception;
425
426 END;
427
428 Procedure Get_Line_Proration_Info(X_Corrected_Invoice_Id IN NUMBER,
429 X_Correction_Amount IN NUMBER,
430 X_Line_Tab IN OUT NOCOPY LINE_TAB_TYPE,
431 X_Prorate_Lines_Flag IN VARCHAR2,
432 X_Calling_Sequence IN VARCHAR2) IS
433
434 CURSOR Invoice_Lines_Cursor(p_line_proration_total NUMBER) IS
435 SELECT ail.line_number,
436 decode(g_min_acct_unit,'',
437 round (x_correction_amount * ail.amount/p_line_proration_total,
438 g_precision),
439 round (((x_correction_amount * ail.amount/p_line_proration_total)/
440 g_min_acct_unit) * g_min_acct_unit)
441 )
442 FROM ap_invoice_lines ail,
443 ap_invoices ai
444 WHERE ai.invoice_id = x_corrected_invoice_id
445 AND ail.invoice_id = ai.invoice_id
446 AND ail.line_type_lookup_code = 'ITEM'; -- Bug 5597409, restricting for Item line only
447
448 l_corrected_line_number ap_invoice_lines.line_number%TYPE;
449 l_amount ap_invoice_lines.amount%TYPE;
450 i NUMBER;
451 l_line_rounded_index ap_invoice_lines.line_number%TYPE;
452 l_max_line_amount ap_invoice_lines.amount%TYPE := 0;
453 l_sum_line_prorated_amount ap_invoice_lines.amount%TYPE := 0;
454 l_total_base_amount ap_invoice_lines.base_amount%TYPE := 0;
455 l_sum_line_base_amount ap_invoice_lines.base_amount%TYPE := 0;
456 l_base_amount ap_invoice_lines.base_amount%TYPE;
457 l_debug_info VARCHAR2(100);
458 l_line_proration_total NUMBER;
459 current_calling_sequence VARCHAR2(2000);
460 l_api_name VARCHAR2(30);
461
462
463 BEGIN
464
465 l_api_name := 'Get_Line_Proration_Info';
466
467 current_calling_sequence := 'Get_Line_Proration_Info<-'||x_calling_sequence;
468
469 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
470 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_INVOICE_CORRECTIONS_PKG.Get_Line_Proration_Info(+)');
471 END IF;
472
473
474 IF (x_prorate_lines_flag = 'Y') THEN
475
476 l_line_proration_total := Get_Line_Proration_Total(
477 X_Corrected_Invoice_Id,
478 current_calling_sequence);
479
480 l_debug_info := 'Open Invoice_Lines_Cursor';
481 OPEN Invoice_Lines_Cursor(l_line_proration_total);
482
483 LOOP
484
485 FETCH Invoice_Lines_Cursor INTO l_corrected_line_number,
486 l_amount;
487
488 EXIT WHEN Invoice_Lines_Cursor%NOTFOUND;
489
493 --Store the index of max of invoice line with largest amount
490 x_line_tab(l_corrected_line_number).corrected_line_number := l_corrected_line_number;
491 x_line_tab(l_corrected_line_number).line_amount := l_amount;
492
494 --for proration rounding and base_amount rounding
495 l_debug_info := 'Get rounding index';
496 --bug 6629136
497 IF (abs(l_amount)>= abs(l_max_line_amount)) THEN
498
499 l_line_rounded_index := l_corrected_line_number;
500 l_max_line_amount := x_line_tab(l_corrected_line_number).line_amount;
501
502 END IF;
503
504 l_sum_line_prorated_amount := l_sum_line_prorated_amount + l_amount;
505
506 END LOOP;
507
508 l_debug_info := 'Close Invoice Lines Cursor';
509 CLOSE Invoice_Lines_Cursor;
510
511 l_debug_info := 'Update the pl/sql table with the rounding amount';
512 IF (x_correction_amount <> l_sum_line_prorated_amount and l_line_rounded_index IS NOT NULL) THEN
513
514 x_line_tab(l_line_rounded_index).line_amount := x_line_tab(l_line_rounded_index).line_amount +
515 (x_correction_amount - l_sum_line_prorated_amount);
516 END IF;
517
518 END IF;
519
520
521 IF (g_exchange_rate IS NOT NULL) THEN
522
523 l_total_base_amount := ap_utilities_pkg.ap_round_currency(
524 x_correction_amount*g_exchange_rate,
525 g_base_currency_code);
526
527 FOR i in nvl(x_line_tab.first,0) .. nvl(x_line_tab.last,0) LOOP
528
529 IF (x_line_tab.exists(i)) THEN
530
531 l_base_amount := ap_utilities_pkg.ap_round_currency(
532 x_line_tab(i).line_amount*g_exchange_rate,
533 g_base_currency_code);
534 x_line_tab(i).base_amount := l_base_amount;
535
536 --Store the index of max of invoice line with largest amount
537 --for base_amount rounding
538 l_debug_info := 'Get the rounding index for base amount rounding';
539 IF (x_line_tab(i).line_amount >= l_max_line_amount) THEN
540 l_line_rounded_index := i;
541 l_max_line_amount := x_line_tab(i).line_amount;
542 END IF;
543
544 l_sum_line_base_amount := l_sum_line_base_amount + l_base_amount;
545 END IF;
546
547 END LOOP;
548
549 IF (l_total_base_amount <> l_sum_line_base_amount and l_line_rounded_index IS NOT NULL) THEN
550
551 x_line_tab(l_line_rounded_index).base_amount := x_line_tab(l_line_rounded_index).base_amount +
552 (l_total_base_amount - l_sum_line_base_amount);
553 x_line_tab(l_line_rounded_index).rounding_amt := l_total_base_amount - l_sum_line_base_amount;
554
555 END IF;
556
557 END IF; /* g_exchange_rate IS NOT NULL*/
558
559 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
560 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_INVOICE_CORRECTIONS_PKG.Get_Line_Proration_Info(-)');
561 END IF;
562
563
564 EXCEPTION
565 WHEN OTHERS THEN
566 IF (SQLCODE <> -20001) THEN
567 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
568 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
569 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
570 FND_MESSAGE.SET_TOKEN('PARAMETERS',
571 'Corrected Invoice Id = '||to_char(x_corrected_invoice_id)
572 ||', Correction Amount = '||to_char(x_correction_amount)
573 ||', Prorate Lines Flag = '||x_prorate_lines_flag);
574 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
575 END IF;
576
577 --Clean up the PL/SQL table
578 x_line_tab.delete;
579
580 app_exception.raise_exception;
581
582 END Get_Line_Proration_Info;
583
584
585
586 Procedure Get_Dist_Proration_Info(X_Corrected_Invoice_Id IN NUMBER,
587 X_Corrected_Line_Number IN NUMBER,
588 X_Line_Amount IN NUMBER,
589 X_Line_Base_Amount IN NUMBER,
590 X_Included_Tax_Amount IN NUMBER,
591 X_Dist_Tab IN OUT NOCOPY DIST_TAB_TYPE,
592 X_Prorate_Dists_Flag IN VARCHAR2,
593 X_Calling_Sequence IN VARCHAR2) IS
594
595 CURSOR Invoice_Dists_Cursor(p_proration_total IN NUMBER) IS
596 SELECT aid.invoice_distribution_id,
597 /* decode(g_min_acct_unit,'',
598 round ((x_line_amount + nvl(ail.included_tax_amount,0)) * aid.amount/p_proration_total,
599 g_precision),
600 round ((((x_line_amount + nvl(ail.included_tax_amount,0)) * aid.amount/p_proration_total)/
601 g_min_acct_unit) * g_min_acct_unit)
602 ) , */
603 decode(g_min_acct_unit,'',
604 round ((x_line_amount - x_included_tax_amount) * aid.amount/p_proration_total,
605 g_precision),
606 round ((((x_line_amount - x_included_tax_amount) * aid.amount/p_proration_total)/
607 g_min_acct_unit) * g_min_acct_unit)
608 ) ,
609
610 ap_invoice_distributions_s.nextval
611 FROM ap_invoice_lines ail,
612 ap_invoice_distributions aid
616 AND aid.invoice_line_number = ail.line_number
613 WHERE ail.invoice_id = x_corrected_invoice_id
614 AND ail.line_number = x_corrected_line_number
615 AND aid.invoice_id = ail.invoice_id
617 -- Bug 5597409. Add the restriction for Item distribution only
618 AND aid.line_type_lookup_code = 'ITEM'
619 AND aid.prepay_distribution_id IS NULL;
620
621 l_corrected_inv_dist_id ap_invoice_distributions.invoice_distribution_id%TYPE;
622 l_invoice_distribution_id ap_invoice_distributions.invoice_distribution_id%TYPE;
623 l_amount ap_invoice_distributions.amount%TYPE;
624 l_max_dist_amount ap_invoice_distributions.amount%TYPE := 0;
625 l_rounding_index ap_invoice_distributions.invoice_distribution_id%TYPE;
626 l_sum_prorated_amount ap_invoice_distributions.amount%TYPE := 0;
627 l_base_amount ap_invoice_distributions.base_amount%TYPE := 0;
628 l_sum_base_amount ap_invoice_distributions.base_amount%TYPE := 0;
629 i NUMBER;
630 l_debug_info VARCHAR2(100);
631 current_calling_sequence VARCHAR2(2000);
632 l_api_name VARCHAR2(30);
633 l_dist_proration_total NUMBER;
634
635 BEGIN
636
637 l_api_name := 'Get_Dist_Proration_Info';
638 current_calling_sequence := 'Get_Dist_Proration_Info<-'||x_calling_sequence;
639
640 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
641 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_INVOICE_CORRECTIONS_PKG.Get_Dist_Proration_Info(+)');
642 END IF;
643
644
645 IF (x_prorate_dists_flag = 'Y') THEN
646
647 l_debug_info := 'Get Distribution Proration total from corrected line';
648 l_dist_proration_total := Get_Dist_Proration_Total(
649 X_Corrected_Invoice_Id,
650 X_Corrected_Line_Number,
651 Current_Calling_Sequence);
652
653 l_debug_info := 'Open Invoice_Dists_Cursor(l_dist_proration_total)';
654 OPEN Invoice_Dists_Cursor(l_dist_proration_total);
655
656 l_debug_info := 'Populate distribution proration info';
657 LOOP
658
659 FETCH Invoice_Dists_Cursor INTO l_corrected_inv_dist_id,
660 l_amount,
661 l_invoice_distribution_id;
662
663
664 EXIT WHEN Invoice_Dists_Cursor%NOTFOUND;
665
666 x_dist_tab(l_invoice_distribution_id).corrected_inv_dist_id := l_corrected_inv_dist_id;
667 x_dist_tab(l_invoice_distribution_id).amount := l_amount;
668 x_dist_tab(l_invoice_distribution_id).invoice_distribution_id := l_invoice_distribution_id;
669
670 IF (l_amount >= l_max_dist_amount ) THEN
671 l_rounding_index := l_invoice_distribution_id;
672 l_max_dist_amount := l_amount;
673 END IF;
674
675 l_sum_prorated_amount := l_sum_prorated_amount + l_amount;
676
677 END LOOP;
678
679 CLOSE Invoice_Dists_Cursor;
680
681 l_debug_info := 'Perform Proration rounding';
682 IF (l_sum_prorated_amount <> x_line_amount and l_rounding_index IS NOT NULL) THEN
683
684 x_dist_tab(l_rounding_index).amount := x_dist_tab(l_rounding_index).amount +
685 (x_line_amount - l_sum_prorated_amount);
686
687 END IF;
688
689 END IF;
690
691 IF (g_exchange_rate IS NOT NULL) THEN
692
693 l_debug_info := 'Calculate Base_amount and Base_amount rounding';
694 FOR i in nvl(x_dist_tab.first,0) .. nvl(x_dist_tab.last,0) LOOP
695
696 IF (x_dist_tab.exists(i)) THEN
697
698 l_base_amount := ap_utilities_pkg.ap_round_currency(
699 x_dist_tab(i).amount*g_exchange_rate,
700 g_base_currency_code);
701
702 x_dist_tab(i).base_amount := l_base_amount ;
703
704 l_sum_base_amount := l_sum_base_amount + l_base_amount;
705
706 END IF;
707
708 END LOOP;
709
710 IF (x_line_base_amount <> l_sum_base_amount and l_rounding_index is not null) THEN
711
712 x_dist_tab(l_rounding_index).base_amount := x_dist_tab(l_rounding_index).base_amount +
713 (x_line_base_amount - l_sum_base_amount);
714 x_dist_tab(l_rounding_index).rounding_amt := x_line_base_amount - l_sum_base_amount;
715
716 END IF;
717
718 END IF; /*g_exchange_rate IS NOT NULL*/
719
720
721 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
722 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_INVOICE_CORRECTIONS_PKG.Get_Dist_Proration_Info(-)');
723 END IF;
724
725
726 EXCEPTION
727 WHEN OTHERS THEN
728 IF (SQLCODE <> -20001) THEN
729 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
730 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
731 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
732 FND_MESSAGE.SET_TOKEN('PARAMETERS',
733 'Corrected Invoice Id = '||to_char(x_corrected_invoice_id)
734 ||', Line Amount = '||to_char(x_line_amount)
735 ||', Prorate Distributions Flag = '||x_prorate_dists_flag);
736 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
737 END IF;
738
739 --Clean up the PL/SQL table
740 x_dist_tab.delete;
744 END Get_Dist_Proration_Info;
741
742 app_exception.raise_exception;
743
745
746
747 FUNCTION Get_Dist_Proration_Total(
748 X_Corrected_Invoice_id IN NUMBER,
749 X_Corrected_Line_Number IN NUMBER,
750 X_Current_Calling_Sequence IN VARCHAR2) RETURN NUMBER IS
751
752 l_dist_total NUMBER;
753 l_debug_info VARCHAR2(1000);
754 current_calling_sequence VARCHAR2(2000);
755 BEGIN
756
757 Current_Calling_Sequence := 'Get_Dist_Proration_Total <- '||x_current_calling_sequence;
758 l_debug_info := 'Get the total dist proration total';
759
760 SELECT sum(aid.amount)
761 INTO l_dist_total
762 FROM ap_invoice_distributions_all aid
763 WHERE aid.invoice_id = x_corrected_invoice_id
764 AND aid.invoice_line_number = x_corrected_line_number
765 AND aid.line_type_lookup_code = 'ITEM' -- Bug 5597409. Restrict to 'ITEM' type only
766 AND aid.prepay_distribution_id IS NULL;
767
768 RETURN(l_dist_total);
769
770 EXCEPTION WHEN OTHERS THEN
771 IF (SQLCODE <> -20001) THEN
772 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
773 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
774 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
775 FND_MESSAGE.SET_TOKEN('PARAMETERS',
776 'Corrected Invoice Id = '||to_char(x_corrected_invoice_id)
777 ||',Corrected Line Number = '||to_char(x_corrected_line_number));
778 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
779 END IF;
780
781 app_exception.raise_exception;
782
783 END Get_Dist_Proration_Total;
784
785
786 Procedure Create_Invoice_Lines_Dists(
787 X_Invoice_Id IN NUMBER,
788 X_Corrected_Invoice_Id IN NUMBER,
789 X_Corrected_Line_Number IN NUMBER,
790 X_Line_Tab IN OUT NOCOPY LINE_TAB_TYPE,
791 X_Dist_Tab IN OUT NOCOPY DIST_TAB_TYPE,
792 --bugfix:4700522
793 X_Prorate_Dists_Flag IN VARCHAR2,
794 X_Correction_Quantity IN NUMBER,
795 X_Correction_Price IN NUMBER,
796 X_Total_Correction_Amount IN NUMBER,
797 X_Calling_Sequence IN VARCHAR2) IS
798
799 l_invoice_line_number ap_invoice_lines.line_number%TYPE;
800 l_sum_prorated_amount ap_invoice_lines.amount%TYPE;
801 l_max_line_amount ap_invoice_lines.amount%TYPE;
802 l_line_rounded_index ap_invoice_lines.line_number%TYPE;
803 l_line_rounding_amount ap_invoice_lines.amount%TYPE;
804 l_debug_info VARCHAR2(100);
805 current_calling_sequence VARCHAR2(2000);
806 l_api_name VARCHAR2(30);
807 l_line_base_amount NUMBER;
808 l_success BOOLEAN := TRUE;
809 l_error_code VARCHAR2(2000);
810 Tax_Exception Exception;
811
812
813 BEGIN
814
815 l_api_name := 'Create_Invoice_Lines_Dists';
816
817 current_calling_sequence := 'Create_Invoice_Lines_Dists<-'||x_calling_sequence;
818
819
820 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
821 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_INVOICE_CORRECTIONS_PKG.Create_Invoice_Lines_Dists(+)');
822 END IF;
823
824
825 l_invoice_line_number := g_max_invoice_line_number + 1;
826
827 l_debug_info := 'Insert Invoice Lines for this correction';
828
829 FOR i IN nvl(x_line_tab.first,0).. nvl(x_line_tab.last,0) LOOP
830
831 IF (x_line_tab.exists(i)) THEN
832
833 x_line_tab(i).line_number := l_invoice_line_number;
834
835 Insert_Invoice_Line(X_Invoice_Id => x_invoice_id,
836 X_Invoice_Line_Number => l_invoice_line_number,
837 X_Corrected_Invoice_Id => x_corrected_invoice_id,
838 X_Corrected_Line_Number => x_line_tab(i).corrected_line_number,
839 X_Amount => x_line_tab(i).line_amount,
840 X_Base_Amount => x_line_tab(i).base_amount,
841 X_Rounding_Amt => x_line_tab(i).rounding_amt,
842 X_Correction_Quantity => x_correction_quantity,
843 X_Correction_Price => x_correction_price,
844 X_Calling_Sequence => current_calling_sequence);
845
846 l_invoice_line_number := l_invoice_line_number + 1;
847
848 END IF;
849
850 END LOOP;
851
852 -- Bug 5597409. Calling eTax
853 l_debug_info := 'Calculate Tax on the Invoice';
854
855
856 l_success := ap_etax_pkg.calling_etax
857 (p_invoice_id => x_invoice_id,
858 p_calling_mode => 'CALCULATE',
859 p_all_error_messages => 'N',
860 p_error_code => l_error_code,
861 p_calling_sequence => current_calling_sequence);
862
863 IF (NOT l_success) THEN
864 Raise Tax_Exception;
865 END IF;
866
867 FOR i IN nvl(x_line_tab.first,0).. nvl(x_line_tab.last,0) LOOP
868
869 IF (x_line_tab.exists(i)) THEN
870
871 Begin
872 Select nvl(included_tax_amount, 0)
873 Into x_line_tab(i).included_tax_amount
874 From ap_invoice_lines_all
875 Where invoice_id = x_invoice_id
876 And line_number = x_line_tab(i).line_number;
877
878 Exception
879 When Others Then
883 END IF;
880 x_line_tab(i).included_tax_amount := 0;
881 End ;
882
884
885 END LOOP;
886
887 FOR i IN nvl(x_line_tab.first,0).. nvl(x_line_tab.last,0) LOOP
888
889 IF (x_line_tab.exists(i)) THEN
890
891 Get_Dist_Proration_Info(
892 X_Corrected_Invoice_Id => x_corrected_invoice_id,
893 X_Corrected_Line_Number =>x_line_tab(i).corrected_line_number,
894 X_Line_Amount => x_line_tab(i).line_amount,
895 X_Line_Base_Amount => x_line_tab(i).base_amount,
896 X_Included_Tax_Amount => x_line_tab(i).included_tax_amount,
897 X_Dist_Tab => x_dist_tab,
898 X_Prorate_Dists_Flag => x_prorate_dists_flag,
899 X_Calling_Sequence => current_calling_sequence);
900
901 Insert_Invoice_Distributions(
902 X_Invoice_ID => x_invoice_id,
903 X_Invoice_Line_Number => x_line_tab(i).line_number,
904 X_Dist_Tab => x_dist_tab,
905 X_Line_Amount => x_line_tab(i).line_amount,
906 X_Calling_Sequence => current_calling_sequence);
907
908 l_invoice_line_number := l_invoice_line_number + 1;
909
910 END IF;
911
912 END LOOP;
913
914 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
915 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_INVOICE_CORRECTIONS_PKG.Create_Invoice_Lines_Dists(-)');
916 END IF;
917
918
919
920 EXCEPTION WHEN OTHERS THEN
921 IF (SQLCODE <> -20001) THEN
922 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
923 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
924 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
925 FND_MESSAGE.SET_TOKEN('PARAMETERS','Invoice_Id = '||to_char(x_invoice_id)
926 ||', Corrected Invoice Id = '||to_char(x_corrected_invoice_id)
927 ||', Corrected line Number = '||to_char(x_corrected_line_number)
928 ||', Correction Quantity = '||to_char(x_correction_quantity)
929 ||', Correction Price = '||to_char(x_correction_price)
930 ||', Total Correction Amount = '||to_char(x_total_correction_amount));
931
932 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
933 END IF;
934 --Clean up the PL/SQL tables.
935 x_line_tab.delete;
936 x_dist_tab.delete;
937
938 APP_EXCEPTION.RAISE_EXCEPTION;
939
940 END Create_Invoice_Lines_Dists;
941
942
943
944 Procedure Insert_Invoice_Line (X_Invoice_Id IN NUMBER,
945 X_Invoice_Line_Number IN NUMBER,
946 X_Corrected_Invoice_Id IN NUMBER,
947 X_Corrected_Line_Number IN NUMBER,
948 X_Amount IN NUMBER,
949 X_Base_Amount IN NUMBER,
950 X_Rounding_Amt IN NUMBER,
951 X_Correction_Quantity IN NUMBER,
952 X_Correction_Price IN NUMBER,
953 X_Calling_Sequence IN VARCHAR2) IS
954
955 current_calling_sequence VARCHAR2(2000);
956 l_debug_info VARCHAR2(100);
957 l_api_name VARCHAR2(30);
958
959 BEGIN
960
961 l_api_name := 'Insert_Invoice_Line';
962
963 current_calling_sequence := 'Insert_Invoice_Line<-'||x_calling_sequence;
964
965 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
966 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_INVOICE_CORRECTIONS_PKG.Insert_Invoice_Lines(+)');
967 END IF;
968
969
970 l_debug_info := 'Inserting non-matched Item Line';
971
972 INSERT INTO AP_INVOICE_LINES_ALL (
973 INVOICE_ID,
974 LINE_NUMBER,
975 LINE_TYPE_LOOKUP_CODE,
976 REQUESTER_ID,
977 DESCRIPTION,
978 LINE_SOURCE,
979 ORG_ID,
980 INVENTORY_ITEM_ID,
981 ITEM_DESCRIPTION,
982 SERIAL_NUMBER,
983 MANUFACTURER,
984 MODEL_NUMBER,
985 GENERATE_DISTS,
986 MATCH_TYPE,
987 DISTRIBUTION_SET_ID,
988 ACCOUNT_SEGMENT,
989 BALANCING_SEGMENT,
990 COST_CENTER_SEGMENT,
991 OVERLAY_DIST_CODE_CONCAT,
992 DEFAULT_DIST_CCID,
993 PRORATE_ACROSS_ALL_ITEMS,
994 LINE_GROUP_NUMBER,
995 ACCOUNTING_DATE,
996 PERIOD_NAME,
997 DEFERRED_ACCTG_FLAG,
998 DEF_ACCTG_START_DATE,
999 DEF_ACCTG_END_DATE,
1000 DEF_ACCTG_NUMBER_OF_PERIODS,
1001 DEF_ACCTG_PERIOD_TYPE,
1002 SET_OF_BOOKS_ID,
1003 AMOUNT,
1004 BASE_AMOUNT,
1005 ROUNDING_AMT,
1006 QUANTITY_INVOICED,
1007 UNIT_MEAS_LOOKUP_CODE,
1008 UNIT_PRICE,
1009 WFAPPROVAL_STATUS,
1010 -- USSGL_TRANSACTION_CODE, - Bug 4277744
1011 DISCARDED_FLAG,
1012 ORIGINAL_AMOUNT,
1013 ORIGINAL_BASE_AMOUNT,
1014 ORIGINAL_ROUNDING_AMT,
1015 CANCELLED_FLAG,
1016 INCOME_TAX_REGION,
1017 TYPE_1099,
1018 STAT_AMOUNT,
1019 PREPAY_INVOICE_ID,
1020 PREPAY_LINE_NUMBER,
1021 INVOICE_INCLUDES_PREPAY_FLAG,
1022 CORRECTED_INV_ID,
1023 CORRECTED_LINE_NUMBER,
1024 PO_HEADER_ID,
1025 PO_LINE_ID,
1026 PO_RELEASE_ID,
1027 PO_LINE_LOCATION_ID,
1031 ASSETS_TRACKING_FLAG,
1028 PO_DISTRIBUTION_ID,
1029 RCV_TRANSACTION_ID,
1030 FINAL_MATCH_FLAG,
1032 ASSET_BOOK_TYPE_CODE,
1033 ASSET_CATEGORY_ID,
1034 PROJECT_ID,
1035 TASK_ID,
1036 EXPENDITURE_TYPE,
1037 EXPENDITURE_ITEM_DATE,
1038 EXPENDITURE_ORGANIZATION_ID,
1039 PA_QUANTITY,
1040 PA_CC_AR_INVOICE_ID,
1041 PA_CC_AR_INVOICE_LINE_NUM,
1042 PA_CC_PROCESSED_CODE,
1043 AWARD_ID,
1044 AWT_GROUP_ID,
1045 REFERENCE_1,
1046 REFERENCE_2,
1047 RECEIPT_VERIFIED_FLAG,
1048 RECEIPT_REQUIRED_FLAG,
1049 RECEIPT_MISSING_FLAG,
1050 JUSTIFICATION,
1051 EXPENSE_GROUP,
1052 START_EXPENSE_DATE,
1053 END_EXPENSE_DATE,
1054 RECEIPT_CURRENCY_CODE,
1055 RECEIPT_CONVERSION_RATE,
1056 RECEIPT_CURRENCY_AMOUNT,
1057 DAILY_AMOUNT,
1058 WEB_PARAMETER_ID,
1059 ADJUSTMENT_REASON,
1060 MERCHANT_DOCUMENT_NUMBER,
1061 MERCHANT_NAME,
1062 MERCHANT_REFERENCE,
1063 MERCHANT_TAX_REG_NUMBER,
1064 MERCHANT_TAXPAYER_ID,
1065 COUNTRY_OF_SUPPLY,
1066 CREDIT_CARD_TRX_ID,
1067 COMPANY_PREPAID_INVOICE_ID,
1068 CC_REVERSAL_FLAG,
1069 ATTRIBUTE_CATEGORY,
1070 ATTRIBUTE1,
1071 ATTRIBUTE2,
1072 ATTRIBUTE3,
1073 ATTRIBUTE4,
1074 ATTRIBUTE5,
1075 ATTRIBUTE6,
1076 ATTRIBUTE7,
1077 ATTRIBUTE8,
1078 ATTRIBUTE9,
1079 ATTRIBUTE10,
1080 ATTRIBUTE11,
1081 ATTRIBUTE12,
1082 ATTRIBUTE13,
1083 ATTRIBUTE14,
1084 ATTRIBUTE15,
1085 /* GLOBAL_ATTRIBUTE_CATEGORY,
1086 GLOBAL_ATTRIBUTE1,
1087 GLOBAL_ATTRIBUTE2,
1088 GLOBAL_ATTRIBUTE3,
1089 GLOBAL_ATTRIBUTE4,
1090 GLOBAL_ATTRIBUTE5,
1091 GLOBAL_ATTRIBUTE6,
1092 GLOBAL_ATTRIBUTE7,
1093 GLOBAL_ATTRIBUTE8,
1094 GLOBAL_ATTRIBUTE9,
1095 GLOBAL_ATTRIBUTE10,
1096 GLOBAL_ATTRIBUTE11,
1097 GLOBAL_ATTRIBUTE12,
1098 GLOBAL_ATTRIBUTE13,
1099 GLOBAL_ATTRIBUTE14,
1100 GLOBAL_ATTRIBUTE15,
1101 GLOBAL_ATTRIBUTE16,
1102 GLOBAL_ATTRIBUTE17,
1103 GLOBAL_ATTRIBUTE18,
1104 GLOBAL_ATTRIBUTE19,
1105 GLOBAL_ATTRIBUTE20, */
1106 CREATION_DATE,
1107 CREATED_BY,
1108 LAST_UPDATED_BY,
1109 LAST_UPDATE_DATE,
1110 LAST_UPDATE_LOGIN,
1111 PROGRAM_APPLICATION_ID,
1112 PROGRAM_ID,
1113 PROGRAM_UPDATE_DATE,
1114 REQUEST_ID,
1115 --ETAX: Invwkb
1116 SHIP_TO_LOCATION_ID,
1117 PRIMARY_INTENDED_USE,
1118 PRODUCT_FISC_CLASSIFICATION,
1119 TRX_BUSINESS_CATEGORY,
1120 PRODUCT_TYPE,
1121 PRODUCT_CATEGORY,
1122 USER_DEFINED_FISC_CLASS,
1123 PURCHASING_CATEGORY_ID)
1124 SELECT x_invoice_id, --invoice_id
1125 x_invoice_line_number, --line_number
1126 ail.line_type_lookup_code, --line_type_lookup_code
1127 ail.requester_id, --requester_id
1128 ail.description, --description
1129 'HEADER CORRECTION', --line_source
1130 ail.org_id, --org_id
1131 ail.inventory_item_id, --inventory_item_id
1132 ail.item_description, --item_description
1133 ail.serial_number, --serial_number
1134 ail.manufacturer, --manufacturer
1135 ail.model_number, --model_number
1136 'D', --generate_dists
1137 'LINE_CORRECTION', --match_type
1138 NULL, --distribution_set_id
1139 ail.account_segment, --account_segment
1140 ail.balancing_segment, --balancing_segment
1141 ail.cost_center_segment, --cost_center_segment
1142 ail.overlay_dist_code_concat, --overlay_dist_code_concat
1143 ail.default_dist_ccid, --default_dist_ccid
1144 'N', --prorate_across_all_items
1145 NULL, --line_group_number
1146 g_accounting_date, --accounting_date
1147 g_period_name, --period_name
1148 'N', --deferred_acctg_flag
1149 NULL, --def_acctg_start_date
1150 NULL, --def_acctg_end_date
1151 NULL, --def_acctg_number_of_periods
1152 NULL, --def_acctg_period_type
1153 g_set_of_books_id, --set_of_books_id
1154 x_amount, --amount
1155 x_base_amount, --base_amount
1156 x_rounding_amt, --rounding_amount
1157 x_correction_quantity, --quantity_invoiced
1158 decode(x_correction_quantity,'','',
1159 ail.unit_meas_lookup_code), --unit_meas_lookup_code
1160 x_correction_price, --unit_price
1161 decode(g_approval_workflow_flag,'Y',
1162 'REQUIRED','NOT REQUIRED'), --wf_approval_status
1163 -- Removed for bug 4277744
1164 -- g_ussgl_transaction_code, --ussgl_transaction_code
1165 'N', --discarded_flag
1166 NULL, --original_amount
1170 g_income_tax_region, --income_tax_region
1167 NULL, --original_base_amount
1168 NULL, --original_rounding_amt
1169 'N', --cancelled_flag
1171 g_type_1099, --type_1099
1172 NULL, --stat_amount
1173 NULL, --prepay_invoice_id
1174 NULL, --prepay_line_number
1175 NULL, --invoice_includes_prepay_flag
1176 x_corrected_invoice_id, --corrected_inv_id
1177 x_corrected_line_number, --corrected_line_number
1178 NULL, --po_header_id
1179 NULL, --po_line_id
1180 NULL, --po_release_id
1181 NULL, --po_line_location_id
1182 NULL, --po_distribution_id
1183 NULL, --rcv_transaction_id
1184 NULL, --final_match_flag
1185 ail.assets_tracking_flag, --assets_tracking_flag
1186 ail.asset_book_type_code, --asset_book_type_code
1187 ail.asset_category_id, --asset_category_id
1188 ail.project_id, --project_id
1189 ail.task_id, --task_id
1190 ail.expenditure_type, --expenditure_type
1191 ail.expenditure_item_date, --expenditure_item_date
1192 ail.expenditure_organization_id, --expenditure_organization_id
1193 x_correction_quantity, --pa_quantity
1194 NULL, --pa_cc_ar_invoice_id
1195 NULL, --pa_cc_ar_invoice_line_num
1196 NULL, --pa_cc_processed_code
1197 ail.award_id, --award_id
1198 g_awt_group_id, --awt_group_id
1199 ail.reference_1, --reference_1
1200 ail.reference_2, --reference_2
1201 ail.receipt_verified_flag, --receipt_verified_flag
1202 ail.receipt_required_flag, --receipt_required_flag
1203 ail.receipt_missing_flag, --receipt_missing_flag
1204 ail.justification, --justification
1205 ail.expense_group, --expense_group
1206 ail.start_expense_date, --start_expense_date
1207 ail.end_expense_date, --end_expense_date
1208 ail.receipt_currency_code, --receipt_currency_code
1209 ail.receipt_conversion_rate, --receipt_conversion_rate
1210 ail.receipt_currency_amount, --receipt_currency_amount
1211 ail.daily_amount, --daily_amount
1212 ail.web_parameter_id, --web_parameter_id
1213 ail.adjustment_reason, --adjustment_reason
1214 ail.merchant_document_number, --merchant_document_number
1215 ail.merchant_name, --merchant_name
1216 ail.merchant_reference, --merchant_reference
1217 ail.merchant_tax_reg_number, --merchant_tax_reg_number
1218 ail.merchant_taxpayer_id, --merchant_taxpayer_id
1219 ail.country_of_supply, --country_of_supply
1220 ail.credit_card_trx_id, --credit_card_trx_id
1221 ail.company_prepaid_invoice_id, --company_prepaid_invoice_id
1222 ail.cc_reversal_flag, --cc_reversal_flag
1223 ail.attribute_category, --attribute_category
1224 ail.attribute1, --attribute1
1225 ail.attribute2, --attribute2
1226 ail.attribute3, --attribute3
1227 ail.attribute4, --attribute4
1228 ail.attribute5, --attribute5
1229 ail.attribute6, --attribute6
1230 ail.attribute7, --attribute7
1231 ail.attribute8, --attribute8
1232 ail.attribute9, --attribute9
1233 ail.attribute10, --attribute10
1234 ail.attribute11, --attribute11
1235 ail.attribute12, --attribute12
1236 ail.attribute13, --attribute13
1237 ail.attribute14, --attribute14
1238 ail.attribute15, --attribute15
1239 /*OPEN ISSUE 1*/
1240 /* X_GLOBAL_ATTRIBUTE_CATEGORY, --global_attribute_category
1241 X_GLOBAL_ATTRIBUTE1, --global_attribute1
1242 X_GLOBAL_ATTRIBUTE2, --global_attribute2
1243 X_GLOBAL_ATTRIBUTE3, --global_attribute3
1244 X_GLOBAL_ATTRIBUTE4, --global_attribute4
1245 X_GLOBAL_ATTRIBUTE5, --global_attribute5
1246 X_GLOBAL_ATTRIBUTE6, --global_attribute6
1247 X_GLOBAL_ATTRIBUTE7, --global_attribute7
1248 X_GLOBAL_ATTRIBUTE8, --global_attribute8
1249 X_GLOBAL_ATTRIBUTE9, --global_attribute9
1250 X_GLOBAL_ATTRIBUTE10, --global_attribute10
1251 X_GLOBAL_ATTRIBUTE11, --global_attribute11
1252 X_GLOBAL_ATTRIBUTE12, --global_attribute12
1253 X_GLOBAL_ATTRIBUTE13, --global_attribute13
1254 X_GLOBAL_ATTRIBUTE14, --global_attribute14
1255 X_GLOBAL_ATTRIBUTE15, --global_attribute15
1256 X_GLOBAL_ATTRIBUTE16, --global_attribute16
1257 X_GLOBAL_ATTRIBUTE17, --global_attribute17
1258 X_GLOBAL_ATTRIBUTE18, --global_attribute18
1259 X_GLOBAL_ATTRIBUTE19, --global_attribute19
1260 X_GLOBAL_ATTRIBUTE20, */ --global_attribute20
1261 sysdate, --creation_date
1262 g_user_id, --created_by
1263 g_user_id, --last_update_by
1264 sysdate, --last_update_date
1265 g_login_id, --last_update_login
1266 NULL, --program_application_id
1267 NULL, --program_id
1271 AIL.SHIP_TO_LOCATION_ID, --ship_to_location_id
1268 NULL, --program_update_date
1269 NULL, --request_id
1270 --ETAX: Invwkb
1272 AIL.PRIMARY_INTENDED_USE, --primary_intended_use
1273 AIL.PRODUCT_FISC_CLASSIFICATION, --product_fisc_classification
1274 G_TRX_BUSINESS_CATEGORY, --trx_business_category
1275 AIL.PRODUCT_TYPE, --product_type
1276 AIL.PRODUCT_CATEGORY, --product_category
1277 AIL.USER_DEFINED_FISC_CLASS, --user_defined_fisc_class
1278 AIL.PURCHASING_CATEGORY_ID --purchasing_category_id
1279 FROM ap_invoices ai,
1280 ap_invoice_lines ail
1281 WHERE ai.invoice_id = ail.invoice_id
1282 AND ai.invoice_id = x_corrected_invoice_id
1283 AND ail.line_number = x_corrected_line_number;
1284
1285
1286 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1287 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_INVOICE_CORRECTIONS_PKG.Insert_Invoice_Lines(-)');
1288 END IF;
1289
1290
1291 EXCEPTION WHEN OTHERS THEN
1292 IF (SQLCODE <> -20001) THEN
1293 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1294 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1295 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
1296 FND_MESSAGE.SET_TOKEN('PARAMETERS','Invoice_Id = '||to_char(x_invoice_id)
1297 ||', Invoice Line Number = '||to_char(x_invoice_line_number)
1298 ||', Corrected Invoice Id = '||to_char(x_corrected_invoice_id)
1299 ||', Corrected line Number = '||to_char(x_corrected_line_number));
1300
1301 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
1302 END IF;
1303
1304 APP_EXCEPTION.RAISE_EXCEPTION;
1305
1306
1307 END Insert_Invoice_Line;
1308
1309
1310
1311 PROCEDURE Insert_Invoice_Distributions (X_Invoice_ID IN NUMBER,
1312 X_Invoice_Line_Number IN NUMBER,
1313 X_Dist_Tab IN OUT NOCOPY Dist_Tab_Type,
1314 X_Line_Amount IN NUMBER,
1315 X_Calling_Sequence IN VARCHAR2) IS
1316 l_distribution_line_number NUMBER := 1;
1317 l_rounding_amount NUMBER;
1318 l_sum_prorated_amount NUMBER := 0;
1319 l_rounded_index ap_invoice_distributions.invoice_distribution_id%type;
1320 i NUMBER;
1321 l_max_dist_amount NUMBER := 0;
1322 l_max_distribution_id ap_invoice_distributions.invoice_distribution_id%type;
1323 l_debug_info VARCHAR2(100);
1324 current_calling_sequence VARCHAR2(2000);
1325 l_api_name VARCHAR2(30);
1326
1327 BEGIN
1328
1329 l_api_name := 'Insert_Invoice_Distributions';
1330
1331 current_calling_sequence := 'Insert_Invoice_Distributions <-'||current_calling_sequence;
1332
1333 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1334 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_INVOICE_CORRECTIONS_PKG.Insert_Invoice_Distributions(+)');
1335 END IF;
1336
1337 l_debug_info := 'Insert Invoice Distributions';
1338
1339 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1340 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1341 END IF;
1342
1343 FOR i in nvl(X_Dist_tab.FIRST, 0) .. nvl(X_Dist_tab.LAST, 0) LOOP
1344
1345 IF (x_dist_tab.exists(i)) THEN
1346
1347
1348 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1349 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1350 END IF;
1351
1352 INSERT INTO ap_invoice_distributions(
1353 batch_id,
1354 invoice_id,
1355 invoice_line_number,
1356 invoice_distribution_id,
1357 distribution_line_number,
1358 line_type_lookup_code,
1359 description,
1360 dist_match_type,
1361 distribution_class,
1362 org_id,
1363 dist_code_combination_id,
1364 accounting_date,
1365 period_name,
1366 accrual_posted_flag,
1367 cash_posted_flag,
1368 amount_to_post,
1369 base_amount_to_post,
1370 posted_amount,
1371 posted_base_amount,
1372 je_batch_id,
1373 cash_je_batch_id,
1374 posted_flag,
1375 accounting_event_id,
1376 upgrade_posted_amt,
1377 upgrade_base_posted_amt,
1378 set_of_books_id,
1379 amount,
1380 base_amount,
1381 rounding_amt,
1382 match_status_flag,
1383 encumbered_flag,
1384 packet_id,
1385 -- Removed for bug 4277744
1386 -- ussgl_transaction_code,
1387 -- ussgl_trx_code_context,
1388 reversal_flag,
1389 parent_reversal_id,
1390 cancellation_flag,
1391 income_tax_region,
1392 type_1099,
1393 stat_amount,
1394 charge_applicable_to_dist_id,
1395 prepay_amount_remaining,
1396 prepay_distribution_id,
1397 parent_invoice_id,
1398 corrected_invoice_dist_id,
1402 rcv_transaction_id,
1399 corrected_quantity,
1400 other_invoice_id,
1401 po_distribution_id,
1403 unit_price,
1404 matched_uom_lookup_code,
1405 quantity_invoiced,
1406 final_match_flag,
1407 related_id,
1408 assets_addition_flag,
1409 assets_tracking_flag,
1410 asset_book_type_code,
1411 asset_category_id,
1412 project_id,
1413 task_id,
1414 expenditure_type,
1415 expenditure_item_date,
1416 expenditure_organization_id,
1417 pa_quantity,
1418 pa_addition_flag,
1419 pa_cc_ar_invoice_id,
1420 pa_cc_ar_invoice_line_num,
1421 pa_cc_processed_code,
1422 award_id,
1423 gms_burdenable_raw_cost,
1424 awt_flag,
1425 awt_group_id,
1426 awt_tax_rate_id,
1427 awt_gross_amount,
1428 awt_invoice_id,
1429 awt_origin_group_id,
1430 awt_invoice_payment_id,
1431 awt_withheld_amt,
1432 inventory_transfer_status,
1433 reference_1,
1434 reference_2,
1435 receipt_verified_flag,
1436 receipt_required_flag,
1437 receipt_missing_flag,
1438 justification,
1439 expense_group,
1440 start_expense_date,
1441 end_expense_date,
1442 receipt_currency_code,
1443 receipt_conversion_rate,
1444 receipt_currency_amount,
1445 daily_amount,
1446 web_parameter_id,
1447 adjustment_reason,
1448 merchant_document_number,
1449 merchant_name,
1450 merchant_reference,
1451 merchant_tax_reg_number,
1452 merchant_taxpayer_id,
1453 country_of_supply,
1454 credit_card_trx_id,
1455 company_prepaid_invoice_id,
1456 cc_reversal_flag,
1457 attribute_category,
1458 attribute1,
1459 attribute2,
1460 attribute3,
1461 attribute4,
1462 attribute5,
1463 attribute6,
1464 attribute7,
1465 attribute8,
1466 attribute9,
1467 attribute10,
1468 attribute11,
1469 attribute12,
1470 attribute13,
1471 attribute14,
1472 attribute15,
1473 /*global_attribute_category,
1474 global_attribute1,
1475 global_attribute2,
1476 global_attribute3,
1477 global_attribute4,
1478 global_attribute5,
1479 global_attribute6,
1480 global_attribute7,
1481 global_attribute8,
1482 global_attribute9,
1483 global_attribute10,
1484 global_attribute11,
1485 global_attribute12,
1486 global_attribute13,
1487 global_attribute14,
1488 global_attribute15,
1489 global_attribute16,
1490 global_attribute17,
1491 global_attribute18,
1492 global_attribute19,
1493 global_attribute20,*/
1494 created_by,
1495 creation_date,
1496 last_updated_by,
1497 last_update_date,
1498 last_update_login,
1499 program_application_id,
1500 program_id,
1501 program_update_date,
1502 request_id,
1503 --ETAX: Invwkb
1504 intended_use,
1505 --Freight and Special Charges
1506 rcv_charge_addition_flag)
1507 SELECT g_batch_id, --batch_id
1508 x_invoice_id, --invoice_id
1509 x_invoice_line_number, --invoice_line_number
1510 NVL(x_dist_tab(i).invoice_distribution_id,
1511 ap_invoice_distributions_s.nextval), --invoice_distribution_id
1512 l_distribution_line_number, --distribution_line_number
1513 aid.line_type_lookup_code, --line_type_lookup_code
1514 ail.item_description, --description
1515 'DIST_CORRECTION', --dist_match_type
1516 'PERMANENT', --distribution_class
1517 ail.org_id, --org_id
1518 aid.dist_code_combination_id, --dist_code_combination_id
1519 ail.accounting_date, --accounting_date
1520 ail.period_name, --period_name
1521 'N', --accrual_posted_flag
1522 'N', --cash_posted_flag
1523 NULL, --amount_to_post
1524 NULL, --base_amount_to_post
1525 NULL, --posted_amount
1526 NULL, --posted_base_amount
1527 NULL, --je_batch_id
1531 NULL, --upgrade_posted_amt
1528 NULL, --cash_je_batch_id
1529 'N', --posted_flag
1530 NULL, --accounting_event_id
1532 NULL, --upgrade_base_posted_amt
1533 g_set_of_books_id, --set_of_books_id
1534 x_dist_tab(i).amount, --amount
1535 x_dist_tab(i).base_amount, --base_amount
1536 x_dist_tab(i).rounding_amt, --rounding_amount
1537 NULL, --match_status_flag
1538 'N', --encumbered_flag
1539 NULL, --packet_id
1540 -- Removed for bug 4277744
1541 -- ail.ussgl_transaction_code, --ussgl_transaction_code
1542 -- NULL, --ussgl_trx_code_context
1543 'N', --reversal_flag
1544 NULL, --parent_reversal_id
1545 'N', --cancellation_flag
1546 aid.income_tax_region, --income_tax_region
1547 aid.type_1099, --type_1099
1548 ap_utilities_pkg.ap_round_currency(
1549 (x_dist_tab(i).amount * aid.stat_amount)/aid.amount,
1550 'STAT'), --stat_amount
1551 NULL, --charge_applicable_to_dist_id
1552 NULL, --prepay_amount_remaining
1553 NULL, --prepay_distribution_id
1554 aid.invoice_id, --parent_invoice_id
1555 x_dist_tab(i).corrected_inv_dist_id, --corrected_invoice_dist_id
1556 NULL, --corrected_quantity
1557 NULL, --other_invoice_id
1558 NULL, --po_distribution_id
1559 NULL, --rcv_transaction_id
1560 NULL, --unit_price
1561 NULL, --matched_uom_lookup_code
1562 NULL, --quantity_invoiced
1563 NULL, --final_match_flag
1564 NULL, --related_id
1565 'U', --assets_addition_flag
1566 decode(gcc.account_type,'E',
1567 ail.assets_tracking_flag,
1568 'A','Y','N'), --assets_tracking_flag
1569 decode(decode(gcc.account_type,'E',
1570 ail.assets_tracking_flag,'A','Y','N'),
1571 'Y',ail.asset_book_type_code,NULL),--asset_book_type_code
1572 decode(decode(gcc.account_type,'E',
1573 ail.assets_tracking_flag,'A','Y','N'),
1574 'Y',ail.asset_category_id,NULL), --asset_category_id
1575 aid.project_id, --project_id
1576 aid.task_id, --task_id
1577 aid.expenditure_type, --expenditure_type
1578 aid.expenditure_item_date, --expenditure_item_date
1579 aid.expenditure_organization_id, --expenditure_organization_id
1580 x_dist_tab(i).amount * aid.pa_quantity/aid.amount, --pa_quantity
1581 decode(aid.project_id,NULL, 'E', 'N'), --pa_addition_flag
1582 NULL, --pa_cc_ar_invoice_id
1583 NULL, --pa_cc_ar_invoice_line_num
1584 NULL, --pa_cc_processed_code
1585 aid.award_id, --award_id
1586 NULL, --gms_burdenable_raw_cost
1587 NULL, --awt_flag
1588 decode(g_system_allow_awt_flag,'Y',
1589 decode(g_site_allow_awt_flag,'Y',ail.awt_group_id,NULL),
1590 NULL), --awt_group_id
1591 NULL, --awt_tax_rate_id
1592 NULL, --awt_gross_amount
1593 NULL, --awt_invoice_id
1594 NULL, --awt_origin_group_id
1595 NULL, --awt_invoice_payment_id
1596 NULL, --awt_withheld_amt
1597 'N', --inventory_transfer_status
1598 aid.reference_1, --reference_1
1599 aid.reference_2, --reference_2
1600 aid.receipt_verified_flag, --receipt_verified_flag
1601 aid.receipt_required_flag, --receipt_required_flag
1602 aid.receipt_missing_flag, --receipt_missing_flag
1603 aid.justification, --justification
1604 aid.expense_group, --expense_group
1605 aid.start_expense_date, --start_expense_date
1606 aid.end_expense_date, --end_expense_date
1607 aid.receipt_currency_code, --receipt_currency_code
1608 aid.receipt_conversion_rate, --receipt_conversion_rate
1609 aid.receipt_currency_amount, --receipt_currency_amount
1610 aid.daily_amount, --daily_amount
1611 aid.web_parameter_id, --web_parameter_id
1612 aid.adjustment_reason, --adjustment_reason
1613 aid.merchant_document_number, --merchant_document_number
1614 aid.merchant_name, --merchant_name
1615 aid.merchant_reference, --merchant_reference
1616 aid.merchant_tax_reg_number, --merchant_tax_reg_number
1617 aid.merchant_taxpayer_id, --merchant_taxpayer_id
1618 aid.country_of_supply, --country_of_supply
1619 aid.credit_card_trx_id, --credit_card_trx_id
1620 aid.company_prepaid_invoice_id, --company_prepaid_invoice_id
1621 aid.cc_reversal_flag, --cc_reversal_flag
1622 aid.attribute_category, --attribute_category
1623 aid.attribute1, --attribute1
1624 aid.attribute2, --attribute2
1625 aid.attribute3, --attribute3
1626 aid.attribute4, --attribute4
1627 aid.attribute5, --attribute5
1628 aid.attribute6, --attribute6
1629 aid.attribute7, --attribute7
1630 aid.attribute8, --attribute8
1631 aid.attribute9, --attribute9
1632 aid.attribute10, --attribute10
1633 aid.attribute11, --attribute11
1634 aid.attribute12, --attribute12
1635 aid.attribute13, --attribute13
1636 aid.attribute14, --attribute14
1637 aid.attribute15, --attribute15
1638 /* X_GLOBAL_ATTRIBUTE_CATEGORY,
1639 X_GLOBAL_ATTRIBUTE1,
1643 X_GLOBAL_ATTRIBUTE5,
1640 X_GLOBAL_ATTRIBUTE2,
1641 X_GLOBAL_ATTRIBUTE3,
1642 X_GLOBAL_ATTRIBUTE4,
1644 X_GLOBAL_ATTRIBUTE6,
1645 X_GLOBAL_ATTRIBUTE7,
1646 X_GLOBAL_ATTRIBUTE8,
1647 X_GLOBAL_ATTRIBUTE9,
1648 X_GLOBAL_ATTRIBUTE10,
1649 X_GLOBAL_ATTRIBUTE11,
1650 X_GLOBAL_ATTRIBUTE12,
1651 X_GLOBAL_ATTRIBUTE13,
1652 X_GLOBAL_ATTRIBUTE14,
1653 X_GLOBAL_ATTRIBUTE15,
1654 X_GLOBAL_ATTRIBUTE16,
1655 X_GLOBAL_ATTRIBUTE17,
1656 X_GLOBAL_ATTRIBUTE18,
1657 X_GLOBAL_ATTRIBUTE19,
1658 X_GLOBAL_ATTRIBUTE20, */
1659 ail.created_by, --created_by
1660 sysdate, --creation_date
1661 ail.last_updated_by, --last_updated_by
1662 sysdate, --last_update_date
1663 ail.last_update_login, --last_update_login
1664 NULL, --program_application_id
1665 NULL, --program_id
1666 NULL, --program_update_date
1667 NULL, --request_id
1668 --ETAX: Invwkb
1669 aid.intended_use, --intended_use
1670 'N'
1671 FROM ap_invoice_distributions aid,
1672 ap_invoice_lines ail,
1673 gl_code_combinations gcc
1674 WHERE ail.invoice_id = x_invoice_id
1675 AND ail.line_number = x_invoice_line_number
1676 AND aid.invoice_id = ail.corrected_inv_id
1677 AND aid.invoice_line_number = ail.corrected_line_number
1678 AND aid.invoice_distribution_id = x_dist_tab(i).corrected_inv_dist_id
1679 AND gcc.code_combination_id = aid.dist_code_combination_id;
1680
1681
1682 l_distribution_line_number := l_distribution_line_number + 1;
1683
1684 END IF; /*x_dist_tab.exists(i))*/
1685
1686 END LOOP;
1687
1688
1689 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1690 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_INVOICE_CORRECTIONS_PKG.Insert_Invoice_Distributions(-)');
1691 END IF;
1692
1693
1694 EXCEPTION
1695 WHEN OTHERS THEN
1696
1697 IF (SQLCODE <> -20001) THEN
1698 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1699 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1700 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
1701 FND_MESSAGE.SET_TOKEN('PARAMETERS','Batch_Id = '||TO_CHAR(g_Batch_Id)
1702 ||', Invoice_id = '||TO_CHAR(X_invoice_id)
1703 ||', Invoice Line Number = '||X_Invoice_Line_Number
1704 ||', Dist_num = '||l_distribution_line_number);
1705 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
1706 END IF;
1707 --Clean up the PL/SQL tables.
1708 X_DIST_TAB.DELETE;
1709
1710 APP_EXCEPTION.RAISE_EXCEPTION;
1711
1712 END Insert_Invoice_Distributions;
1713
1714
1715 END AP_INVOICE_CORRECTIONS_PKG;