[Home] [Help]
PACKAGE BODY: APPS.AP_GENERATE_DISTRIBUTIONS_PKG
Source
1 PACKAGE BODY AP_GENERATE_DISTRIBUTIONS_PKG AS
2 /*$Header: apaidutb.pls 120.16 2008/06/09 08:33:37 mayyalas ship $ */
3
4 --Bugfix:3859755, added the below FND_LOG related variables, in order
5 --to enable LOGGING for this package.
6 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AP_GENERATE_DISTRIBUTIONS_PKG';
7 G_MSG_UERROR CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR;
8 G_MSG_ERROR CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_ERROR;
9 G_MSG_SUCCESS CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_SUCCESS;
10 G_MSG_HIGH CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH;
11 G_MSG_MEDIUM CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM;
12 G_MSG_LOW CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW;
13 G_LINES_PER_FETCH CONSTANT NUMBER := 1000;
14
15 G_CURRENT_RUNTIME_LEVEL CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
16 G_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
17 G_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
18 G_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
19 G_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
20 G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
21 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
22 G_MODULE_NAME CONSTANT VARCHAR2(100) := 'AP.PLSQL.AP_GENERATE_DISTRIBUTIONS_PKG.';
23
24 CURSOR G_INVOICE_LINES_CURSOR(P_Invoice_ID Number,
25 P_Invoice_Line_Number NUMBER) IS
26 SELECT INVOICE_ID,
27 LINE_NUMBER,
28 LINE_TYPE_LOOKUP_CODE,
29 REQUESTER_ID,
30 DESCRIPTION,
31 LINE_SOURCE,
32 ORG_ID,
33 LINE_GROUP_NUMBER,
34 INVENTORY_ITEM_ID,
35 ITEM_DESCRIPTION,
36 SERIAL_NUMBER,
37 MANUFACTURER,
38 MODEL_NUMBER,
39 WARRANTY_NUMBER,
40 GENERATE_DISTS,
41 MATCH_TYPE,
42 DISTRIBUTION_SET_ID,
43 ACCOUNT_SEGMENT,
44 BALANCING_SEGMENT,
45 COST_CENTER_SEGMENT,
46 OVERLAY_DIST_CODE_CONCAT,
47 DEFAULT_DIST_CCID,
48 PRORATE_ACROSS_ALL_ITEMS,
49 ACCOUNTING_DATE,
50 PERIOD_NAME ,
51 DEFERRED_ACCTG_FLAG ,
52 DEF_ACCTG_START_DATE ,
53 DEF_ACCTG_END_DATE,
54 DEF_ACCTG_NUMBER_OF_PERIODS,
55 DEF_ACCTG_PERIOD_TYPE ,
56 SET_OF_BOOKS_ID,
57 AMOUNT,
58 BASE_AMOUNT,
59 ROUNDING_AMT,
60 QUANTITY_INVOICED,
61 UNIT_MEAS_LOOKUP_CODE ,
62 UNIT_PRICE,
63 WFAPPROVAL_STATUS,
64 DISCARDED_FLAG,
65 ORIGINAL_AMOUNT,
66 ORIGINAL_BASE_AMOUNT ,
67 ORIGINAL_ROUNDING_AMT ,
68 CANCELLED_FLAG ,
69 INCOME_TAX_REGION,
70 TYPE_1099 ,
71 STAT_AMOUNT ,
72 PREPAY_INVOICE_ID ,
73 PREPAY_LINE_NUMBER ,
74 INVOICE_INCLUDES_PREPAY_FLAG ,
75 CORRECTED_INV_ID ,
76 CORRECTED_LINE_NUMBER ,
77 PO_HEADER_ID,
78 PO_LINE_ID ,
79 PO_RELEASE_ID ,
80 PO_LINE_LOCATION_ID ,
81 PO_DISTRIBUTION_ID,
82 RCV_TRANSACTION_ID,
83 FINAL_MATCH_FLAG,
84 ASSETS_TRACKING_FLAG ,
85 ASSET_BOOK_TYPE_CODE ,
86 ASSET_CATEGORY_ID ,
87 PROJECT_ID ,
88 TASK_ID ,
89 EXPENDITURE_TYPE ,
90 EXPENDITURE_ITEM_DATE ,
91 EXPENDITURE_ORGANIZATION_ID ,
92 PA_QUANTITY,
93 PA_CC_AR_INVOICE_ID ,
94 PA_CC_AR_INVOICE_LINE_NUM ,
95 PA_CC_PROCESSED_CODE ,
96 AWARD_ID,
97 AWT_GROUP_ID ,
98 REFERENCE_1 ,
99 REFERENCE_2 ,
100 RECEIPT_VERIFIED_FLAG ,
101 RECEIPT_REQUIRED_FLAG ,
102 RECEIPT_MISSING_FLAG ,
103 JUSTIFICATION ,
104 EXPENSE_GROUP ,
105 START_EXPENSE_DATE ,
106 END_EXPENSE_DATE ,
107 RECEIPT_CURRENCY_CODE ,
108 RECEIPT_CONVERSION_RATE,
109 RECEIPT_CURRENCY_AMOUNT ,
110 DAILY_AMOUNT ,
111 WEB_PARAMETER_ID ,
112 ADJUSTMENT_REASON ,
113 MERCHANT_DOCUMENT_NUMBER ,
114 MERCHANT_NAME ,
115 MERCHANT_REFERENCE ,
116 MERCHANT_TAX_REG_NUMBER,
117 MERCHANT_TAXPAYER_ID ,
118 COUNTRY_OF_SUPPLY,
119 CREDIT_CARD_TRX_ID ,
120 COMPANY_PREPAID_INVOICE_ID,
121 CC_REVERSAL_FLAG ,
122 CREATION_DATE ,
123 CREATED_BY,
124 LAST_UPDATED_BY ,
125 LAST_UPDATE_DATE ,
126 LAST_UPDATE_LOGIN ,
127 PROGRAM_APPLICATION_ID ,
128 PROGRAM_ID ,
129 PROGRAM_UPDATE_DATE,
130 REQUEST_ID ,
131 ATTRIBUTE_CATEGORY,
132 ATTRIBUTE1,
133 ATTRIBUTE2,
134 ATTRIBUTE3,
135 ATTRIBUTE4,
136 ATTRIBUTE5,
137 ATTRIBUTE6,
138 ATTRIBUTE7,
139 ATTRIBUTE8,
140 ATTRIBUTE9,
141 ATTRIBUTE10,
142 ATTRIBUTE11,
143 ATTRIBUTE12,
144 ATTRIBUTE13,
145 ATTRIBUTE14,
146 ATTRIBUTE15,
147 GLOBAL_ATTRIBUTE_CATEGORY,
148 GLOBAL_ATTRIBUTE1,
149 GLOBAL_ATTRIBUTE2,
150 GLOBAL_ATTRIBUTE3,
151 GLOBAL_ATTRIBUTE4,
152 GLOBAL_ATTRIBUTE5,
153 GLOBAL_ATTRIBUTE6,
154 GLOBAL_ATTRIBUTE7,
155 GLOBAL_ATTRIBUTE8,
156 GLOBAL_ATTRIBUTE9,
157 GLOBAL_ATTRIBUTE10,
158 GLOBAL_ATTRIBUTE11,
159 GLOBAL_ATTRIBUTE12,
160 GLOBAL_ATTRIBUTE13,
161 GLOBAL_ATTRIBUTE14,
162 GLOBAL_ATTRIBUTE15,
163 GLOBAL_ATTRIBUTE16,
164 GLOBAL_ATTRIBUTE17,
165 GLOBAL_ATTRIBUTE18,
166 GLOBAL_ATTRIBUTE19,
167 GLOBAL_ATTRIBUTE20,
168 --ETAX: Invwkb
169 INCLUDED_TAX_AMOUNT,
170 PRIMARY_INTENDED_USE,
171 --Bugfix:4673607
172 APPLICATION_ID,
173 PRODUCT_TABLE,
174 REFERENCE_KEY1,
175 REFERENCE_KEY2,
176 REFERENCE_KEY3,
177 REFERENCE_KEY4,
178 REFERENCE_KEY5,
179 --bugfix:4674194
180 SHIP_TO_LOCATION_ID,
181 --bug7022001
182 PAY_AWT_GROUP_ID
183 FROM AP_INVOICE_LINES AIL
184 WHERE AIL.INVOICE_ID = P_INVOICE_ID
185 AND AIL.LINE_NUMBER = NVL(P_INVOICE_LINE_NUMBER,AIL.LINE_NUMBER)
186 AND LINE_TYPE_LOOKUP_CODE IN ('ITEM','FREIGHT','MISCELLANEOUS')
187 AND NVL(AIL.GENERATE_DISTS,'N') = 'Y'
188 ORDER BY DECODE(AIL.LINE_TYPE_LOOKUP_CODE,'ITEM',1,2), LINE_NUMBER;
189
190
191 FUNCTION Generate_Dists_For_Invoice (
192 P_Invoice_Id IN NUMBER,
193 P_Batch_Id IN NUMBER,
194 P_Invoice_Date IN DATE,
195 P_Vendor_Id IN NUMBER,
196 P_Invoice_Currency_Code IN VARCHAR2,
197 P_Exchange_Rate IN NUMBER,
198 P_Exchange_Rate_Type IN VARCHAR2,
199 P_Exchange_Date IN DATE,
200 P_Calling_Mode IN VARCHAR2,
201 P_Error_Code OUT NOCOPY VARCHAR2,
202 P_Token1 OUT NOCOPY VARCHAR2,
203 P_Token2 OUT NOCOPY VARCHAR2,
204 P_Calling_Sequence IN VARCHAR2)
205 RETURN BOOLEAN IS
206
207
208 --Bug 6802813: Added new cursor to generate tax distributions
209 -- while navigating from 'All Distributions' button.
210 CURSOR c_tax_lines IS
211 SELECT 'Tax Lines not fully distributed'
212 FROM AP_INVOICE_LINES_ALL AIL
213 WHERE AIL.INVOICE_ID = P_INVOICE_ID
214 AND AIL.LINE_TYPE_LOOKUP_CODE = 'TAX'
215 AND AIL.amount <>
216 (SELECT nvl(sum(amount),0)
217 FROM AP_INVOICE_DISTRIBUTIONS_ALL AID
218 WHERE AID.INVOICE_ID = AIL.INVOICE_ID
219 AND AID.INVOICE_LINE_NUMBER = AIL.LINE_NUMBER);
220
221 l_tax_lines VARCHAR2(200);
222
223 t_inv_lines_table AP_INVOICES_PKG.t_invoice_lines_table;
224 l_success BOOLEAN := TRUE;
225 l_error_code VARCHAR2(4000);
226 i NUMBER;
227 l_system_user NUMBER := 5;
228 l_holds AP_APPROVAL_PKG.HOLDSARRAY;
229 l_hold_count AP_APPROVAL_PKG.COUNTARRAY;
230 l_release_count AP_APPROVAL_PKG.COUNTARRAY;
231 l_insufficient_data_exist BOOLEAN := TRUE;
232 l_debug_info VARCHAR2(2000);
233 l_curr_calling_sequence VARCHAR2(2000);
234 l_debug_context VARCHAR2(2000);
235
236 l_invoice_num VARCHAR2(50);
237 l_line_number NUMBER;
238 l_api_name CONSTANT VARCHAR2(200) := 'Generate_Dists_For_Invoice';
239
240 BEGIN
241
242 l_curr_calling_sequence := 'AP_GENERATE_DISTRIBUTIONS_PKG.'||
243 'Generate_Dists_for_Invoice <- '||p_calling_sequence;
244
245 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
246 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_GENERATE_DISTRIBUTIONS_PKG.Generate_Dists_for_Invoice(+)');
247 END IF;
248
249 l_debug_info := 'Open G_Invoice_Lines Cursor';
250
251 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
252 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
253 END IF;
254
255 --If some reason the earlier call to generate distributions did not success
256 --due to an error in the etax pkgs, which raise the error in those pkgs itself
257 --, then we will not be able to close the cursor and exit.
258 IF (G_INVOICE_LINES_CURSOR%ISOPEN ) THEN
259 CLOSE G_Invoice_Lines_Cursor ;
260 END IF;
261
262 OPEN G_Invoice_Lines_Cursor(P_Invoice_Id, NULL);
263
264 FETCH G_Invoice_Lines_Cursor BULK COLLECT INTO t_inv_lines_table;
265
266 CLOSE G_Invoice_Lines_Cursor;
267
268 IF (t_inv_lines_table.count <> 0 ) THEN
269
270 IF (p_calling_mode = 'INVOICE HEADER') THEN
271
272 --Since the invoice is not 'Validated' yet, we will need to
273 --Calculate-Tax on the invoice before generating the candidate
274 --distributions for the taxable lines.
275
276 l_debug_info := 'Call etax api to Calculate-Tax';
277 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
278 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
279 END IF;
280
281
282 l_success := ap_etax_pkg.calling_etax(
283 p_invoice_id => p_invoice_id,
284 p_calling_mode => 'CALCULATE',
285 p_all_error_messages => 'N',
286 p_error_code => l_error_code,
287 p_calling_sequence => l_curr_calling_sequence);
288
289 --If Tax-Calculation Failed
290 IF NOT(l_success) THEN
291 l_debug_info := 'Call to EBTax api - Calculate Tax failed';
292 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
293 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
294 END IF;
295
296 P_Error_Code := 'AP_CANNOT_GEN_DIST_DUE_TAX';
297 P_Token1 := l_error_code;
298 IF (G_Invoice_Lines_Cursor%ISOPEN) THEN
299 Close G_Invoice_Lines_Cursor;
300 End If;
301
302 Return(FALSE);
303 END IF;
304
305 END IF; -- p_calling_mode = 'INVOICE HEADER'
306
307 /* Bug 5342316. reopening the cursor again, so that included_tax_amount
308 for lines reflected correctly. */
309
310 OPEN G_Invoice_Lines_Cursor(P_Invoice_Id, NULL);
311
312 FETCH G_Invoice_Lines_Cursor BULK COLLECT INTO t_inv_lines_table;
313
314 CLOSE G_Invoice_Lines_Cursor;
315
316
317 FOR i in t_inv_lines_table.first .. t_inv_lines_table.last LOOP
318
319 IF(t_inv_lines_table.exists(i)) THEN
320
321 --bugfix:5685469 added the below code to generate allocation rule
322 IF t_inv_lines_table(i).line_type_lookup_code in
323 ('FREIGHT', 'MISCELLANEOUS') THEN
324
325 ----------------------------------------------------------------
326 l_debug_info := 'Create Allocation Rule ';
327 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
328 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
329 END IF;
330 ----------------------------------------------------------------
331
332 IF (t_inv_lines_table(i).prorate_across_all_items='Y') then
333
334 l_success := AP_ALLOCATION_RULES_PKG.Create_Proration_Rule(
335 t_inv_lines_table(i).invoice_id,
336 t_inv_lines_table(i).line_number,
337 NULL,
338 'APAIDUTB',
339 l_error_code,
340 l_debug_info,
341 l_debug_context,
342 'Generate_Dists_For_Invoice');
343
344 END IF;
345 END IF;
346
347
348 IF (t_inv_lines_table(i).generate_dists = 'Y') THEN
349
350 IF t_inv_lines_table(i).line_type_lookup_code <> 'TAX' THEN
351
352 l_debug_info := 'Check if sufficient data is provided to generate candidate distributions';
353
354 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
355 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
356 END IF;
357
358 AP_Approval_Pkg.Check_Insufficient_Line_Data(
359 p_inv_line_rec => t_inv_lines_table(i),
360 p_system_user => l_system_user,
361 p_holds => l_holds,
362 p_holds_count => l_hold_count,
363 p_release_count => l_release_count,
364 p_insufficient_data_exist => l_insufficient_data_exist,
365 p_calling_mode => 'CANDIDATE_DISTRIBUTIONS',
366 p_calling_sequence => l_curr_calling_sequence );
367
368 IF ( NOT l_insufficient_data_exist ) THEN
369
370 l_debug_info := 'Generating Candidate Distributions
371 for Taxable Line';
372
373 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
374 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
375 END IF;
376
377 l_success := AP_Approval_Pkg.Execute_Dist_Generation_Check(
378 p_batch_id => p_batch_id,
379 p_invoice_date => p_invoice_date,
380 p_vendor_id => p_vendor_id,
381 p_invoice_currency => p_invoice_currency_code,
382 p_exchange_rate => p_exchange_rate,
383 p_exchange_rate_type => p_exchange_rate_type,
384 p_exchange_date => p_exchange_date,
385 p_inv_line_rec => t_inv_lines_table(i),
386 p_system_user => l_system_user,
387 p_holds => l_holds,
388 p_holds_count => l_hold_count,
389 p_release_count => l_release_count,
390 p_generate_permanent => 'N',
391 p_calling_mode => 'CANDIDATE_DISTRIBUTIONS',
392 p_error_code => l_error_code,
393 p_curr_calling_sequence => l_curr_calling_sequence);
394
395 IF NOT(l_success) THEN
396
397 l_debug_info := 'Could not Generate the Taxable Distributions';
398 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
399 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
400 END IF;
401
402 P_Error_Code := 'AP_CANNOT_GEN_TAXABLE_DISTS';
403 P_Token1 := t_inv_lines_table(i).line_number;
404 P_Token2 := l_error_code;
405
406 IF(G_Invoice_Lines_Cursor%ISOPEN) THEN
407 Close G_Invoice_Lines_Cursor;
408 End if;
409 Return(FALSE);
410
411 END IF;
412
413 ELSE
414
415 P_Error_Code := 'AP_INSUFF_TAXABLE_DIST_INFO';
416 P_Token1 := t_inv_lines_table(i).line_number;
417
418 If(G_Invoice_Lines_Cursor%ISOPEN) THEN
419 Close G_Invoice_Lines_Cursor;
420 End if;
421
422 Return(FALSE);
423
424 END IF; -- end of sufficient data check
425 END IF; -- end of line_type_lookup_code check
426 END IF; -- end of generate_dists check
427
428 END IF; /* t_inv_lines_table(i).exists */
429
430 END LOOP;
431
432 l_debug_info := 'Generate Tax Distributions';
433 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
434 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
435 END IF;
436
437 l_success := ap_etax_pkg.calling_etax (
438 p_invoice_id => p_invoice_id,
439 p_calling_mode => 'DISTRIBUTE',
440 p_all_error_messages => 'N',
441 p_error_code => l_error_code,
442 p_calling_sequence => l_curr_calling_sequence);
443
444
445 IF (NOT l_success) THEN
446
447 l_debug_info := 'Call to EBTax api Determine Recovery failed';
448 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
449 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
450 END IF;
451
452 P_Error_Code := 'AP_CANNOT_DISTRIBUTE_TAX';
453 P_Token1 := l_error_code;
454 IF (G_Invoice_Lines_Cursor%ISOPEN) THEN
455 Close G_Invoice_Lines_Cursor;
456 End if;
457 Return(FALSE);
458 END IF;
459
460 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
461 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_GENERATE_DISTRIBUTIONS_PKG.Generate_Dists_for_Invoice(-)');
462 END IF;
463
464 Return(TRUE);
465
466 ELSE
467
468 l_debug_info := 'Standalone: Item/Freight/Misc Distributions have been generated';
469
470 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
471 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
472 END IF;
473
474 -- Bug 6802813 Start
475 OPEN c_tax_lines;
476 FETCH c_tax_lines
477 INTO l_tax_lines;
478 CLOSE c_tax_lines;
479
480 IF l_tax_lines IS NOT NULL THEN
481
482 l_debug_info := 'Standalone: Generate Tax Distributions';
483 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
484 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
485 END IF;
486
487 l_success := ap_etax_pkg.calling_etax (
488 p_invoice_id => p_invoice_id,
489 p_calling_mode => 'DISTRIBUTE',
490 p_all_error_messages => 'N',
491 p_error_code => l_error_code,
492 p_calling_sequence => l_curr_calling_sequence);
493
494
495 IF (NOT l_success) THEN
496
497 l_debug_info := 'Standalone: Call to EBTax api Determine Recovery failed';
498 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
499 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
500 END IF;
501
502 P_Error_Code := 'AP_CANNOT_DISTRIBUTE_TAX';
503 P_Token1 := l_error_code;
504 Return(FALSE);
505
506 END IF;
507
508 END IF;
509 -- Bug 6802813 End.
510
511 --Do nothing, just return as successful.
512 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
513 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_GENERATE_DISTRIBUTIONS_PKG.Generate_Dists_for_Invoice(-)');
514 END IF;
515
516 Return(TRUE);
517
518 END IF; /* t_inv_lines_table.count <> 0 */
519
520
521 EXCEPTION WHEN OTHERS THEN
522 IF ( SQLCODE = -54 ) THEN
523 FND_MESSAGE.SET_NAME('SQLAP', 'AP_INVOICE_UPDATED_REQUERY');
524 IF (G_INVOICE_LINES_CURSOR%ISOPEN ) THEN
525 CLOSE G_Invoice_Lines_Cursor ;
526 END IF;
527 ELSIF (SQLCODE <> -20001) THEN
528 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
529 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
530 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_curr_calling_sequence);
531 FND_MESSAGE.SET_TOKEN('PARAMETERS',
532 'Invoice Id = '|| to_char(p_invoice_id));
533 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
534
535 IF (G_INVOICE_LINES_CURSOR%ISOPEN ) THEN
536 CLOSE G_Invoice_Lines_Cursor ;
537 END IF;
538 END IF;
539
540 APP_EXCEPTION.RAISE_EXCEPTION;
541
542 END Generate_Dists_For_Invoice;
543
544
545
546 FUNCTION Generate_Dists_For_Line (
547 P_Invoice_Id IN NUMBER,
548 P_Invoice_Line_Number IN NUMBER,
549 P_Batch_Id IN NUMBER,
550 P_Invoice_Date IN DATE,
551 P_Vendor_Id IN NUMBER,
552 P_Invoice_Currency_Code IN VARCHAR2,
553 P_Exchange_Rate IN NUMBER,
554 P_Exchange_Rate_Type IN VARCHAR2,
555 P_Exchange_Date IN DATE,
556 P_Error_Code OUT NOCOPY VARCHAR2,
557 P_Token1 OUT NOCOPY VARCHAR2,
558 P_Token2 OUT NOCOPY VARCHAR2,
559 P_Calling_Sequence IN VARCHAR2) RETURN BOOLEAN IS
560
561 t_inv_lines_table AP_INVOICES_PKG.t_invoice_lines_table;
562 l_success BOOLEAN := TRUE;
563 l_error_code VARCHAR2(4000);
564 i NUMBER;
565 l_system_user NUMBER := 5;
566 l_holds AP_APPROVAL_PKG.HOLDSARRAY;
567 l_hold_count AP_APPROVAL_PKG.COUNTARRAY;
568 l_release_count AP_APPROVAL_PKG.COUNTARRAY;
569 l_insufficient_data_exist BOOLEAN := TRUE ;
570 l_included_tax_amount NUMBER;
571 l_api_name CONSTANT VARCHAR2(200) := 'Generate_Dists_for_Line';
572 l_debug_info VARCHAR2(2000);
573 l_debug_context VARCHAR2(2000);
574 l_curr_calling_sequence VARCHAR2(2000);
575
576 BEGIN
577
578 l_curr_calling_sequence := 'AP_GENERATE_DISTRIBUTIONS_PKG.'||
579 'Generate_Dists_for_Line <- '||p_calling_sequence;
580
581 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
582 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_GENERATE_DISTRIBUTIONS_PKG.Generate_Dists_for_Line(+)');
583 END IF;
584
585 /* Bug 5362316. Move the tax call before Opening Cursor */
586 l_debug_info := 'Call etax api to Calculate-Tax for just that invoice line';
587 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
588 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
589 END IF;
590 l_success := ap_etax_pkg.calling_etax(
591 p_invoice_id => p_invoice_id,
592 p_line_number => p_invoice_line_number,
593 p_calling_mode => 'CALCULATE',
594 p_all_error_messages => 'N',
595 p_error_code => l_error_code,
596 p_calling_sequence => l_curr_calling_sequence);
597
598 --Tax-Calculation Failed
599 IF NOT(l_success) THEN
600
601 l_debug_info := 'Call to EBTax api Calculate Tax failed';
602 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
603 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
604 END IF;
605
606 P_Error_Code := 'AP_CANNOT_GEN_DIST_DUE_TAX';
607 P_Token1 := l_error_code;
608
609 IF (G_Invoice_Lines_Cursor%ISOPEN) THEN
610 Close G_Invoice_Lines_Cursor;
611 END IF;
612 RETURN FALSE;
613
614 END IF;
615
616 --If some reason the earlier call to generate distributions did not success
617 --due to an error in the etax pkgs, which raise the error in those pkgs itself
618 --, then we will not be able to close the cursor and exit.
619 IF (G_INVOICE_LINES_CURSOR%ISOPEN ) THEN
620 CLOSE G_Invoice_Lines_Cursor ;
621 END IF;
622
623
624 l_debug_info := 'Open G_Invoice_Lines Cursor';
625 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
626 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
627 END IF;
628
629 OPEN G_Invoice_Lines_Cursor(P_Invoice_Id ,
630 P_Invoice_Line_Number);
631
632 FETCH G_Invoice_Lines_Cursor BULK COLLECT INTO t_inv_lines_table;
633
634 CLOSE G_Invoice_Lines_Cursor;
635
636 l_debug_info := 'Check if sufficient data is provided to generate candidate distributions';
637 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
638 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
639 END IF;
640
641 i := t_inv_lines_table.first;
642
643 IF (t_inv_lines_table.exists(i)) Then
644
645 IF t_inv_lines_table(i).line_type_lookup_code in
646 ('FREIGHT', 'MISCELLANEOUS') THEN
647
648 ----------------------------------------------------------------
649 l_debug_info := 'Create Allocation Rule ';
650 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
651 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
652 END IF;
653 ----------------------------------------------------------------
654
655 --bugfix:5685469 added the below code to generate allocation rule
656 IF (t_inv_lines_table(i).prorate_across_all_items='Y') then
657
658 l_success := AP_ALLOCATION_RULES_PKG.Create_Proration_Rule(
659 t_inv_lines_table(i).invoice_id,
660 t_inv_lines_table(i).line_number,
661 NULL,
662 'APAIDUTB',
663 l_error_code,
664 l_debug_info,
665 l_debug_context,
666 'Generate_Dists_For_Invoice_Line');
667
668 END IF;
669 END IF;
670
671
672 IF (t_inv_lines_table(i).generate_dists = 'Y') THEN
673
674 AP_Approval_Pkg.Check_Insufficient_Line_Data(
675 p_inv_line_rec => t_inv_lines_table(i),
676 p_system_user => l_system_user,
677 p_holds => l_holds,
678 p_holds_count => l_hold_count,
679 p_release_count => l_release_count,
680 p_insufficient_data_exist => l_insufficient_data_exist,
681 p_calling_mode => 'CANDIDATE_DISTRIBUTIONS',
682 p_calling_sequence => l_curr_calling_sequence );
683
684 END IF;
685
686 END IF; /* t_inv_lines_table.exists(i) */
687
688
689 IF ( NOT l_insufficient_data_exist ) THEN
690
691 l_debug_info := 'Generating Candidate Distributions for Taxable Line';
692
693 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
694 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
695 END IF;
696
697 l_success := AP_Approval_Pkg.Execute_Dist_Generation_Check(
698 p_batch_id => p_batch_id,
699 p_invoice_date => p_invoice_date,
700 p_vendor_id => p_vendor_id,
701 p_invoice_currency => p_invoice_currency_code,
702 p_exchange_rate => p_exchange_rate,
703 p_exchange_rate_type => p_exchange_rate_type,
704 p_exchange_date => p_exchange_date,
705 p_inv_line_rec => t_inv_lines_table(i),
706 p_system_user => l_system_user,
707 p_holds => l_holds,
708 p_holds_count => l_hold_count,
709 p_release_count => l_release_count,
710 p_generate_permanent => 'N',
711 p_calling_mode => 'CANDIDATE_DISTRIBUTIONS',
712 p_error_code => l_error_code,
713 p_curr_calling_sequence => l_curr_calling_sequence);
714
715
716 IF NOT(l_success) THEN
717 l_debug_info := 'Cannot Generate Distributions for the Taxable Line';
718 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
719 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
720 END IF;
721
722 P_Error_Code := 'AP_CANNOT_GEN_TAXABLE_DISTS';
723 P_Token1 := p_invoice_line_number;
724 P_Token2 := l_error_code;
725
726 If(G_Invoice_Lines_Cursor%ISOPEN) THEN
727 Close G_Invoice_Lines_Cursor;
728 End if;
729 return(FALSE);
730
731 END IF;
732
733 SELECT nvl(included_tax_amount,0)
734 INTO l_included_tax_amount
735 FROM ap_invoice_lines
736 WHERE invoice_id = p_invoice_id
737 AND line_number = p_invoice_line_number;
738
739 l_debug_info := 'l_included_tax_amount : '||l_included_tax_amount;
740 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
741 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
742 END IF;
743
744 IF (nvl(l_included_tax_amount,0) <> 0) THEN
745
746 l_debug_info := 'Call EBTax api to Determine Recovery for just that invoice line';
747 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
748 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
749 END IF;
750
751 l_success := ap_etax_pkg.calling_etax(
752 p_invoice_id => p_invoice_id,
753 p_line_number => p_invoice_line_number,
754 p_calling_mode => 'DISTRIBUTE',
755 p_all_error_messages => 'N',
756 p_error_code => l_error_code,
757 p_calling_sequence => l_curr_calling_sequence);
758
759 --Tax-Distribution Failed
760 IF NOT(l_success) THEN
761
762 l_debug_info := 'Call to EBTax api Determine Recovery failed';
763 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
764 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
765 END IF;
766
767 P_Error_Code := 'AP_CANNOT_GEN_DIST_DUE_TAX';
768 P_Token1 := l_error_code;
769
770 IF (G_Invoice_Lines_Cursor%ISOPEN) THEN
771 Close G_Invoice_Lines_Cursor;
772 END IF;
773 RETURN FALSE;
774
775 END IF;
776
777 END IF;
778
779 ELSE
780
781 l_debug_info := 'Insufficent info available to generate distributions for the taxable line';
782 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
783 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
784 END IF;
785
786 P_Error_Code := 'AP_INSUFF_TAXABLE_DIST_INFO';
787 P_Token1 := P_Invoice_Line_Number;
788 If (G_Invoice_Lines_Cursor%ISOPEN) Then
789 Close G_Invoice_Lines_Cursor;
790 End if;
791 Return(FALSE);
792
793 END IF; -- end of sufficient data check
794
795 RETURN(TRUE);
796
797 EXCEPTION WHEN OTHERS THEN
798 IF ( SQLCODE = -54 ) THEN
799 FND_MESSAGE.SET_NAME('SQLAP', 'AP_INVOICE_UPDATED_REQUERY');
800 IF (G_INVOICE_LINES_CURSOR%ISOPEN ) THEN
801 CLOSE G_Invoice_Lines_Cursor ;
802 END IF;
803 ELSIF (SQLCODE <> -20001) THEN
804 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
805 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
806 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_curr_calling_sequence);
807 FND_MESSAGE.SET_TOKEN('PARAMETERS',
808 'Invoice Id = '|| to_char(p_invoice_id)
809 ||'Invoice Line Number = '||to_char(p_invoice_line_number));
810 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
811 IF (G_Invoice_Lines_Cursor%ISOPEN ) THEN
812 CLOSE G_Invoice_Lines_Cursor ;
813 END IF;
814 END IF;
815
816 APP_EXCEPTION.RAISE_EXCEPTION;
817
818 END Generate_Dists_For_Line;
819
820 /*=============================================================================
821 | FUNCTION - generateDistsForInvoice()
822 |
823 | DESCRIPTION
824 | This API is a wrapper for generate_dists_for_invoice() for JDBC calls,
825 | used by OA based applications.
826 |
827 *============================================================================*/
828
829 FUNCTION generateDistsForInvoice (
830 P_Invoice_Id IN NUMBER,
831 P_Batch_Id IN NUMBER,
832 P_Invoice_Date IN DATE,
833 P_Vendor_Id IN NUMBER,
834 P_Invoice_Currency_Code IN VARCHAR2,
835 P_Exchange_Rate IN NUMBER,
836 P_Exchange_Rate_Type IN VARCHAR2,
837 P_Exchange_Date IN DATE,
838 P_Calling_Mode IN VARCHAR2,
839 P_Error_Code OUT NOCOPY VARCHAR2,
840 P_Token1 OUT NOCOPY VARCHAR2,
841 P_Token2 OUT NOCOPY VARCHAR2,
842 P_Calling_Sequence IN VARCHAR2) RETURN NUMBER IS
843
844 l_debug_info VARCHAR2(2000);
845 l_curr_calling_sequence VARCHAR2(2000);
846 l_api_name CONSTANT VARCHAR2(200) := 'generateDistsForInvoice';
847
848 BEGIN
849
850 l_curr_calling_sequence := 'AP_GENERATE_DISTRIBUTIONS_PKG.'||
851 'Generate_Dists_for_Invoice <- '||p_calling_sequence;
852
853 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
854 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_GENERATE_DISTRIBUTIONS_PKG.Generate_Dists_for_Invoice(+)');
855 END IF;
856
857 IF ( generate_dists_for_invoice (
858 P_Invoice_Id => p_invoice_id,
859 P_Batch_Id => p_batch_id,
860 P_Invoice_Date => p_invoice_date,
861 P_Vendor_Id => p_vendor_id,
862 P_Invoice_Currency_Code => p_invoice_currency_code,
863 P_Exchange_Rate => p_exchange_rate,
864 P_Exchange_Rate_Type => p_exchange_rate_type,
865 P_Exchange_Date => p_exchange_date,
866 P_Calling_Mode => p_calling_mode,
867 P_Error_Code => p_error_code,
868 P_Token1 => p_token1,
869 P_Token2 => p_token2,
870 P_Calling_Sequence => l_curr_calling_sequence) ) THEN
871 RETURN 0;
872 ELSE
873 RETURN 1;
874 END IF;
875
876
877 EXCEPTION WHEN OTHERS THEN
878 IF (SQLCODE <> -20001) THEN
879 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
880 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
881 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_curr_calling_sequence);
882 FND_MESSAGE.SET_TOKEN('PARAMETERS',
883 'Invoice Id = '|| to_char(p_invoice_id));
884 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
885
886 END IF;
887
888 APP_EXCEPTION.RAISE_EXCEPTION;
889
890 END generateDistsForInvoice;
891
892
893 END AP_GENERATE_DISTRIBUTIONS_PKG;