[Home] [Help]
PACKAGE BODY: APPS.AP_GENERATE_DISTRIBUTIONS_PKG
Source
1 PACKAGE BODY AP_GENERATE_DISTRIBUTIONS_PKG AS
2 /*$Header: apaidutb.pls 120.19 2010/12/22 08:19:04 asansari 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 l_allocation_rule_type ap_allocation_rules.rule_type%TYPE; --10052153
241 l_allocation_status ap_allocation_rules.status%TYPE; --10052153
242
243 BEGIN
244
245 l_curr_calling_sequence := 'AP_GENERATE_DISTRIBUTIONS_PKG.'||
246 'Generate_Dists_for_Invoice <- '||p_calling_sequence;
247
248 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
249 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_GENERATE_DISTRIBUTIONS_PKG.Generate_Dists_for_Invoice(+)');
250 END IF;
251
252 l_debug_info := 'Open G_Invoice_Lines Cursor';
253
254 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
255 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
256 END IF;
257
258 --If some reason the earlier call to generate distributions did not success
259 --due to an error in the etax pkgs, which raise the error in those pkgs itself
260 --, then we will not be able to close the cursor and exit.
261 IF (G_INVOICE_LINES_CURSOR%ISOPEN ) THEN
262 CLOSE G_Invoice_Lines_Cursor ;
263 END IF;
264
265 OPEN G_Invoice_Lines_Cursor(P_Invoice_Id, NULL);
266
267 FETCH G_Invoice_Lines_Cursor BULK COLLECT INTO t_inv_lines_table;
268
269 CLOSE G_Invoice_Lines_Cursor;
270
271 IF (t_inv_lines_table.count <> 0 ) THEN
272
273 IF (p_calling_mode = 'INVOICE HEADER') THEN
274
275 --Since the invoice is not 'Validated' yet, we will need to
276 --Calculate-Tax on the invoice before generating the candidate
277 --distributions for the taxable lines.
278
279 l_debug_info := 'Call etax api to Calculate-Tax';
280 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
281 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
282 END IF;
283
284
285 l_success := ap_etax_pkg.calling_etax(
286 p_invoice_id => p_invoice_id,
287 p_calling_mode => 'CALCULATE',
288 p_all_error_messages => 'N',
289 p_error_code => l_error_code,
290 p_calling_sequence => l_curr_calling_sequence);
291
292 --If Tax-Calculation Failed
293 IF NOT(l_success) THEN
294 l_debug_info := 'Call to EBTax api - Calculate Tax failed';
295 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
296 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
297 END IF;
298
299 P_Error_Code := 'AP_CANNOT_GEN_DIST_DUE_TAX';
300 P_Token1 := l_error_code;
301 IF (G_Invoice_Lines_Cursor%ISOPEN) THEN
302 Close G_Invoice_Lines_Cursor;
303 End If;
304
305 Return(FALSE);
306 END IF;
307
308 END IF; -- p_calling_mode = 'INVOICE HEADER'
309
310 /* Bug 5342316. reopening the cursor again, so that included_tax_amount
311 for lines reflected correctly. */
312
313 OPEN G_Invoice_Lines_Cursor(P_Invoice_Id, NULL);
314
315 FETCH G_Invoice_Lines_Cursor BULK COLLECT INTO t_inv_lines_table;
316
317 CLOSE G_Invoice_Lines_Cursor;
318
319
320 FOR i in t_inv_lines_table.first .. t_inv_lines_table.last LOOP
321
322 IF(t_inv_lines_table.exists(i)) THEN
323
324 --bugfix:5685469 added the below code to generate allocation rule
325 IF t_inv_lines_table(i).line_type_lookup_code in ('FREIGHT', 'MISCELLANEOUS') THEN
326 --Bug10052153: Added below code to check if PRORATION rule is defined.
327 --API Create_Proration_Rule is called if prorate_across_all_items flag
328 -- is Y or Fully Prorate rule is defined from Allocations window
329 BEGIN
330 SELECT rule_type, status
331 INTO l_allocation_rule_type, l_allocation_status
332 FROM ap_allocation_rules
333 WHERE invoice_id = p_invoice_id
334 AND chrg_invoice_line_number = t_inv_lines_table(i).line_number;
335 EXCEPTION
336 WHEN NO_DATA_FOUND THEN
337 l_allocation_rule_type := NULL;
338 l_allocation_status := NULL;
339 END;
340
341
342 ----------------------------------------------------------------
343 l_debug_info := 'Create Allocation Rule ';
344 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
345 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
346 END IF;
347 ----------------------------------------------------------------
348 --Bug10052153: Added OR condition
349 IF(t_inv_lines_table(i).prorate_across_all_items='Y'
350 OR (l_allocation_rule_type = 'PRORATION' AND l_allocation_status = 'PENDING')) then
351
352 l_success := AP_ALLOCATION_RULES_PKG.Create_Proration_Rule(
353 t_inv_lines_table(i).invoice_id,
354 t_inv_lines_table(i).line_number,
355 NULL,
356 'APAIDUTB',
357 l_error_code,
358 l_debug_info,
359 l_debug_context,
360 'Generate_Dists_For_Invoice');
361
362 END IF;
363 END IF;
364
365
366 IF (t_inv_lines_table(i).generate_dists = 'Y') THEN
367
368 IF t_inv_lines_table(i).line_type_lookup_code <> 'TAX' THEN
369
370 l_debug_info := 'Check if sufficient data is provided to generate candidate distributions';
371
372 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
373 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
374 END IF;
375
376 AP_Approval_Pkg.Check_Insufficient_Line_Data(
377 p_inv_line_rec => t_inv_lines_table(i),
378 p_system_user => l_system_user,
379 p_holds => l_holds,
380 p_holds_count => l_hold_count,
381 p_release_count => l_release_count,
382 p_insufficient_data_exist => l_insufficient_data_exist,
383 p_calling_mode => 'CANDIDATE_DISTRIBUTIONS',
384 p_calling_sequence => l_curr_calling_sequence );
385
386 IF ( NOT l_insufficient_data_exist ) THEN
387
388 l_debug_info := 'Generating Candidate Distributions
389 for Taxable Line';
390
391 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
392 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
393 END IF;
394
395 l_success := AP_Approval_Pkg.Execute_Dist_Generation_Check(
396 p_batch_id => p_batch_id,
397 p_invoice_date => p_invoice_date,
398 p_vendor_id => p_vendor_id,
399 p_invoice_currency => p_invoice_currency_code,
400 p_exchange_rate => p_exchange_rate,
401 p_exchange_rate_type => p_exchange_rate_type,
402 p_exchange_date => p_exchange_date,
403 p_inv_line_rec => t_inv_lines_table(i),
404 p_system_user => l_system_user,
405 p_holds => l_holds,
406 p_holds_count => l_hold_count,
407 p_release_count => l_release_count,
408 p_generate_permanent => 'N',
409 p_calling_mode => 'CANDIDATE_DISTRIBUTIONS',
410 p_error_code => l_error_code,
411 p_curr_calling_sequence => l_curr_calling_sequence);
412
413 IF NOT(l_success) THEN
414
415 l_debug_info := 'Could not Generate the Taxable Distributions';
416 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
417 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
418 END IF;
419
420 P_Error_Code := 'AP_CANNOT_GEN_TAXABLE_DISTS';
421 P_Token1 := t_inv_lines_table(i).line_number;
422
423 -- Bug 7936518: Start
424 -- P_Token2 := l_error_code;
425
426 If (substr(l_error_code,1,3) = 'GMS') then
427 P_Token2 := FND_MESSAGE.GET_STRING('GMS',l_error_code);
428 Else
429 P_Token2 := l_error_code;
430 End if;
431 -- Bug 7936518: End
432
433 IF(G_Invoice_Lines_Cursor%ISOPEN) THEN
434 Close G_Invoice_Lines_Cursor;
435 End if;
436 Return(FALSE);
437
438 END IF;
439
440 ELSE
441
442 P_Error_Code := 'AP_INSUFF_TAXABLE_DIST_INFO';
443 P_Token1 := t_inv_lines_table(i).line_number;
444
445 If(G_Invoice_Lines_Cursor%ISOPEN) THEN
446 Close G_Invoice_Lines_Cursor;
447 End if;
448
449 Return(FALSE);
450
451 END IF; -- end of sufficient data check
452 END IF; -- end of line_type_lookup_code check
453 END IF; -- end of generate_dists check
454
455 END IF; /* t_inv_lines_table(i).exists */
456
457 END LOOP;
458
459 l_debug_info := 'Generate Tax Distributions';
460 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
461 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
462 END IF;
463
464 l_success := ap_etax_pkg.calling_etax (
465 p_invoice_id => p_invoice_id,
466 p_calling_mode => 'DISTRIBUTE',
467 p_all_error_messages => 'N',
468 p_error_code => l_error_code,
469 p_calling_sequence => l_curr_calling_sequence);
470
471
472 IF (NOT l_success) THEN
473
474 l_debug_info := 'Call to EBTax api Determine Recovery failed';
475 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
476 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
477 END IF;
478
479 P_Error_Code := 'AP_CANNOT_DISTRIBUTE_TAX';
480 P_Token1 := l_error_code;
481 IF (G_Invoice_Lines_Cursor%ISOPEN) THEN
482 Close G_Invoice_Lines_Cursor;
483 End if;
484 Return(FALSE);
485 END IF;
486
487 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
488 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_GENERATE_DISTRIBUTIONS_PKG.Generate_Dists_for_Invoice(-)');
489 END IF;
490
491 Return(TRUE);
492
493 ELSE
494
495 l_debug_info := 'Standalone: Item/Freight/Misc Distributions have been generated';
496
497 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
498 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
499 END IF;
500
501 -- Bug 6802813 Start
502 OPEN c_tax_lines;
503 FETCH c_tax_lines
504 INTO l_tax_lines;
505 CLOSE c_tax_lines;
506
507 IF l_tax_lines IS NOT NULL THEN
508
509 -- bug 9246414: add start
510 l_debug_info := 'Call etax api to Calculate-Tax';
511 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
512 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
513 END IF;
514
515 l_success := ap_etax_pkg.calling_etax(
516 p_invoice_id => p_invoice_id,
517 p_calling_mode => 'CALCULATE',
518 p_all_error_messages => 'N',
519 p_error_code => l_error_code,
520 p_calling_sequence => l_curr_calling_sequence);
521
522 --If Tax-Calculation Failed
523 IF NOT(l_success) THEN
524 l_debug_info := 'Call to EBTax api - Calculate Tax failed';
525 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
526 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
527 END IF;
528
529 P_Error_Code := 'AP_CANNOT_GEN_DIST_DUE_TAX';
530 P_Token1 := l_error_code;
531 IF (G_Invoice_Lines_Cursor%ISOPEN) THEN
532 Close G_Invoice_Lines_Cursor;
533 End If;
534
535 Return(FALSE);
536 END IF;
537 -- bug 9246414: add end
538
539 l_debug_info := 'Standalone: Generate Tax Distributions';
540 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
541 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
542 END IF;
543
544 l_success := ap_etax_pkg.calling_etax (
545 p_invoice_id => p_invoice_id,
546 p_calling_mode => 'DISTRIBUTE',
547 p_all_error_messages => 'N',
548 p_error_code => l_error_code,
549 p_calling_sequence => l_curr_calling_sequence);
550
551
552 IF (NOT l_success) THEN
553
554 l_debug_info := 'Standalone: Call to EBTax api Determine Recovery failed';
555 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
556 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
557 END IF;
558
559 P_Error_Code := 'AP_CANNOT_DISTRIBUTE_TAX';
560 P_Token1 := l_error_code;
561 Return(FALSE);
562
563 END IF;
564
565 END IF;
566 -- Bug 6802813 End.
567
568 --Do nothing, just return as successful.
569 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
570 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_GENERATE_DISTRIBUTIONS_PKG.Generate_Dists_for_Invoice(-)');
571 END IF;
572
573 Return(TRUE);
574
575 END IF; /* t_inv_lines_table.count <> 0 */
576
577
578 EXCEPTION WHEN OTHERS THEN
579 IF ( SQLCODE = -54 ) THEN
580 FND_MESSAGE.SET_NAME('SQLAP', 'AP_INVOICE_UPDATED_REQUERY');
581 IF (G_INVOICE_LINES_CURSOR%ISOPEN ) THEN
582 CLOSE G_Invoice_Lines_Cursor ;
583 END IF;
584 ELSIF (SQLCODE <> -20001) THEN
585 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
586 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
587 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_curr_calling_sequence);
588 FND_MESSAGE.SET_TOKEN('PARAMETERS',
589 'Invoice Id = '|| to_char(p_invoice_id));
590 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
591
592 IF (G_INVOICE_LINES_CURSOR%ISOPEN ) THEN
593 CLOSE G_Invoice_Lines_Cursor ;
594 END IF;
595 END IF;
596
597 APP_EXCEPTION.RAISE_EXCEPTION;
598
599 END Generate_Dists_For_Invoice;
600
601
602
603 FUNCTION Generate_Dists_For_Line (
604 P_Invoice_Id IN NUMBER,
605 P_Invoice_Line_Number IN NUMBER,
606 P_Batch_Id IN NUMBER,
607 P_Invoice_Date IN DATE,
608 P_Vendor_Id IN NUMBER,
609 P_Invoice_Currency_Code IN VARCHAR2,
610 P_Exchange_Rate IN NUMBER,
611 P_Exchange_Rate_Type IN VARCHAR2,
612 P_Exchange_Date IN DATE,
613 P_Error_Code OUT NOCOPY VARCHAR2,
614 P_Token1 OUT NOCOPY VARCHAR2,
615 P_Token2 OUT NOCOPY VARCHAR2,
616 P_Calling_Sequence IN VARCHAR2) RETURN BOOLEAN IS
617
618 t_inv_lines_table AP_INVOICES_PKG.t_invoice_lines_table;
619 l_success BOOLEAN := TRUE;
620 l_error_code VARCHAR2(4000);
621 i NUMBER;
622 l_system_user NUMBER := 5;
623 l_holds AP_APPROVAL_PKG.HOLDSARRAY;
624 l_hold_count AP_APPROVAL_PKG.COUNTARRAY;
625 l_release_count AP_APPROVAL_PKG.COUNTARRAY;
626 l_insufficient_data_exist BOOLEAN := TRUE ;
627 l_included_tax_amount NUMBER;
628 l_api_name CONSTANT VARCHAR2(200) := 'Generate_Dists_for_Line';
629 l_debug_info VARCHAR2(2000);
630 l_debug_context VARCHAR2(2000);
631 l_curr_calling_sequence VARCHAR2(2000);
632
633 l_allocation_status ap_allocation_rules.status%TYPE; --10052153
634 l_allocation_rule_type ap_allocation_rules.rule_type%TYPE; --10052153
635
636 BEGIN
637
638 l_curr_calling_sequence := 'AP_GENERATE_DISTRIBUTIONS_PKG.'||
639 'Generate_Dists_for_Line <- '||p_calling_sequence;
640
641 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
642 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_GENERATE_DISTRIBUTIONS_PKG.Generate_Dists_for_Line(+)');
643 END IF;
644
645 /* Bug 5362316. Move the tax call before Opening Cursor */
646 l_debug_info := 'Call etax api to Calculate-Tax for just that invoice line';
647 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
648 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
649 END IF;
650 l_success := ap_etax_pkg.calling_etax(
651 p_invoice_id => p_invoice_id,
652 p_line_number => p_invoice_line_number,
653 p_calling_mode => 'CALCULATE',
654 p_all_error_messages => 'N',
655 p_error_code => l_error_code,
656 p_calling_sequence => l_curr_calling_sequence);
657
658 --Tax-Calculation Failed
659 IF NOT(l_success) THEN
660
661 l_debug_info := 'Call to EBTax api Calculate Tax failed';
662 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
663 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
664 END IF;
665
666 P_Error_Code := 'AP_CANNOT_GEN_DIST_DUE_TAX';
667 P_Token1 := l_error_code;
668
669 IF (G_Invoice_Lines_Cursor%ISOPEN) THEN
670 Close G_Invoice_Lines_Cursor;
671 END IF;
672 RETURN FALSE;
673
674 END IF;
675
676 --If some reason the earlier call to generate distributions did not success
677 --due to an error in the etax pkgs, which raise the error in those pkgs itself
678 --, then we will not be able to close the cursor and exit.
679 IF (G_INVOICE_LINES_CURSOR%ISOPEN ) THEN
680 CLOSE G_Invoice_Lines_Cursor ;
681 END IF;
682
683
684 l_debug_info := 'Open G_Invoice_Lines Cursor';
685 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
686 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
687 END IF;
688
689 OPEN G_Invoice_Lines_Cursor(P_Invoice_Id ,
690 P_Invoice_Line_Number);
691
692 FETCH G_Invoice_Lines_Cursor BULK COLLECT INTO t_inv_lines_table;
693
694 CLOSE G_Invoice_Lines_Cursor;
695
696 l_debug_info := 'Check if sufficient data is provided to generate candidate distributions';
697 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
698 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
699 END IF;
700
701 i := t_inv_lines_table.first;
702
703 IF (t_inv_lines_table.exists(i)) Then
704
705 IF t_inv_lines_table(i).line_type_lookup_code in ('FREIGHT', 'MISCELLANEOUS') THEN
706
707 --Bug10052153: Added below code to check if PRORATION rule is defined.
708 --API Create_Proration_Rule is called if prorate_across_all_items flag
709 -- is Y or Fully Prorate rule is defined from Allocations window
710 BEGIN
711 SELECT rule_type, status
712 INTO l_allocation_rule_type, l_allocation_status
713 FROM ap_allocation_rules
714 WHERE invoice_id = p_invoice_id
715 AND chrg_invoice_line_number = t_inv_lines_table(i).line_number;
716 EXCEPTION
717 WHEN NO_DATA_FOUND THEN
718 l_allocation_rule_type := NULL;
719 l_allocation_status := NULL;
720 END;
721
722 ----------------------------------------------------------------
723 l_debug_info := 'Create Allocation Rule ';
724 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
725 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
726 END IF;
727 ----------------------------------------------------------------
728
729 --bugfix:5685469 added the below code to generate allocation rule
730 --Bug10052153 : Added OR condition
731 IF (t_inv_lines_table(i).prorate_across_all_items='Y'
732 OR (l_allocation_rule_type = 'PRORATION' AND l_allocation_status='PENDING')) then
733
734 l_success := AP_ALLOCATION_RULES_PKG.Create_Proration_Rule(
735 t_inv_lines_table(i).invoice_id,
736 t_inv_lines_table(i).line_number,
737 NULL,
738 'APAIDUTB',
739 l_error_code,
740 l_debug_info,
741 l_debug_context,
742 'Generate_Dists_For_Invoice_Line');
743
744 END IF;
745 END IF;
746
747
748 IF (t_inv_lines_table(i).generate_dists = 'Y') THEN
749
750 AP_Approval_Pkg.Check_Insufficient_Line_Data(
751 p_inv_line_rec => t_inv_lines_table(i),
752 p_system_user => l_system_user,
753 p_holds => l_holds,
754 p_holds_count => l_hold_count,
755 p_release_count => l_release_count,
756 p_insufficient_data_exist => l_insufficient_data_exist,
757 p_calling_mode => 'CANDIDATE_DISTRIBUTIONS',
758 p_calling_sequence => l_curr_calling_sequence );
759
760 END IF;
761
762 END IF; /* t_inv_lines_table.exists(i) */
763
764
765 IF ( NOT l_insufficient_data_exist ) THEN
766
767 l_debug_info := 'Generating Candidate Distributions for Taxable Line';
768
769 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
770 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
771 END IF;
772
773 l_success := AP_Approval_Pkg.Execute_Dist_Generation_Check(
774 p_batch_id => p_batch_id,
775 p_invoice_date => p_invoice_date,
776 p_vendor_id => p_vendor_id,
777 p_invoice_currency => p_invoice_currency_code,
778 p_exchange_rate => p_exchange_rate,
779 p_exchange_rate_type => p_exchange_rate_type,
780 p_exchange_date => p_exchange_date,
781 p_inv_line_rec => t_inv_lines_table(i),
782 p_system_user => l_system_user,
783 p_holds => l_holds,
784 p_holds_count => l_hold_count,
785 p_release_count => l_release_count,
786 p_generate_permanent => 'N',
787 p_calling_mode => 'CANDIDATE_DISTRIBUTIONS',
788 p_error_code => l_error_code,
789 p_curr_calling_sequence => l_curr_calling_sequence);
790
791
792 IF NOT(l_success) THEN
793 l_debug_info := 'Cannot Generate Distributions for the Taxable Line';
794 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
795 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
796 END IF;
797
798 P_Error_Code := 'AP_CANNOT_GEN_TAXABLE_DISTS';
799 P_Token1 := p_invoice_line_number;
800
801 -- Bug 7936518: Start
802 -- P_Token2 := l_error_code;
803
804 If (substr(l_error_code,1,3) = 'GMS') then
805 P_Token2 := FND_MESSAGE.GET_STRING('GMS',l_error_code);
806 Else
807 P_Token2 := l_error_code;
808 End if;
809 -- Bug 7936518: End
810
811 If(G_Invoice_Lines_Cursor%ISOPEN) THEN
812 Close G_Invoice_Lines_Cursor;
813 End if;
814 return(FALSE);
815
816 END IF;
817
818 SELECT nvl(included_tax_amount,0)
819 INTO l_included_tax_amount
820 FROM ap_invoice_lines
821 WHERE invoice_id = p_invoice_id
822 AND line_number = p_invoice_line_number;
823
824 l_debug_info := 'l_included_tax_amount : '||l_included_tax_amount;
825 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
826 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
827 END IF;
828
829 IF (nvl(l_included_tax_amount,0) <> 0) THEN
830
831 l_debug_info := 'Call EBTax api to Determine Recovery for just that invoice line';
832 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
833 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
834 END IF;
835
836 l_success := ap_etax_pkg.calling_etax(
837 p_invoice_id => p_invoice_id,
838 p_line_number => p_invoice_line_number,
839 p_calling_mode => 'DISTRIBUTE',
840 p_all_error_messages => 'N',
841 p_error_code => l_error_code,
842 p_calling_sequence => l_curr_calling_sequence);
843
844 --Tax-Distribution Failed
845 IF NOT(l_success) THEN
846
847 l_debug_info := 'Call to EBTax api Determine Recovery failed';
848 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
849 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
850 END IF;
851
852 P_Error_Code := 'AP_CANNOT_GEN_DIST_DUE_TAX';
853 P_Token1 := l_error_code;
854
855 IF (G_Invoice_Lines_Cursor%ISOPEN) THEN
856 Close G_Invoice_Lines_Cursor;
857 END IF;
858 RETURN FALSE;
859
860 END IF;
861
862 END IF;
863
864 ELSE
865
866 l_debug_info := 'Insufficent info available to generate distributions for the taxable line';
867 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
868 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
869 END IF;
870
871 P_Error_Code := 'AP_INSUFF_TAXABLE_DIST_INFO';
872 P_Token1 := P_Invoice_Line_Number;
873 If (G_Invoice_Lines_Cursor%ISOPEN) Then
874 Close G_Invoice_Lines_Cursor;
875 End if;
876 Return(FALSE);
877
878 END IF; -- end of sufficient data check
879
880 RETURN(TRUE);
881
882 EXCEPTION WHEN OTHERS THEN
883 IF ( SQLCODE = -54 ) THEN
884 FND_MESSAGE.SET_NAME('SQLAP', 'AP_INVOICE_UPDATED_REQUERY');
885 IF (G_INVOICE_LINES_CURSOR%ISOPEN ) THEN
886 CLOSE G_Invoice_Lines_Cursor ;
887 END IF;
888 ELSIF (SQLCODE <> -20001) THEN
889 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
890 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
891 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_curr_calling_sequence);
892 FND_MESSAGE.SET_TOKEN('PARAMETERS',
893 'Invoice Id = '|| to_char(p_invoice_id)
894 ||'Invoice Line Number = '||to_char(p_invoice_line_number));
895 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
896 IF (G_Invoice_Lines_Cursor%ISOPEN ) THEN
897 CLOSE G_Invoice_Lines_Cursor ;
898 END IF;
899 END IF;
900
901 APP_EXCEPTION.RAISE_EXCEPTION;
902
903 END Generate_Dists_For_Line;
904
905 /*=============================================================================
906 | FUNCTION - generateDistsForInvoice()
907 |
908 | DESCRIPTION
909 | This API is a wrapper for generate_dists_for_invoice() for JDBC calls,
910 | used by OA based applications.
911 |
912 *============================================================================*/
913
914 FUNCTION generateDistsForInvoice (
915 P_Invoice_Id IN NUMBER,
916 P_Batch_Id IN NUMBER,
917 P_Invoice_Date IN DATE,
918 P_Vendor_Id IN NUMBER,
919 P_Invoice_Currency_Code IN VARCHAR2,
920 P_Exchange_Rate IN NUMBER,
921 P_Exchange_Rate_Type IN VARCHAR2,
922 P_Exchange_Date IN DATE,
923 P_Calling_Mode IN VARCHAR2,
924 P_Error_Code OUT NOCOPY VARCHAR2,
925 P_Token1 OUT NOCOPY VARCHAR2,
926 P_Token2 OUT NOCOPY VARCHAR2,
927 P_Calling_Sequence IN VARCHAR2) RETURN NUMBER IS
928
929 l_debug_info VARCHAR2(2000);
930 l_curr_calling_sequence VARCHAR2(2000);
931 l_api_name CONSTANT VARCHAR2(200) := 'generateDistsForInvoice';
932
933 BEGIN
934
935 l_curr_calling_sequence := 'AP_GENERATE_DISTRIBUTIONS_PKG.'||
936 'Generate_Dists_for_Invoice <- '||p_calling_sequence;
937
938 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
939 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_GENERATE_DISTRIBUTIONS_PKG.Generate_Dists_for_Invoice(+)');
940 END IF;
941
942 IF ( generate_dists_for_invoice (
943 P_Invoice_Id => p_invoice_id,
944 P_Batch_Id => p_batch_id,
945 P_Invoice_Date => p_invoice_date,
946 P_Vendor_Id => p_vendor_id,
947 P_Invoice_Currency_Code => p_invoice_currency_code,
948 P_Exchange_Rate => p_exchange_rate,
949 P_Exchange_Rate_Type => p_exchange_rate_type,
950 P_Exchange_Date => p_exchange_date,
951 P_Calling_Mode => p_calling_mode,
952 P_Error_Code => p_error_code,
953 P_Token1 => p_token1,
954 P_Token2 => p_token2,
955 P_Calling_Sequence => l_curr_calling_sequence) ) THEN
956 RETURN 0;
957 ELSE
958 RETURN 1;
959 END IF;
960
961
962 EXCEPTION WHEN OTHERS THEN
963 IF (SQLCODE <> -20001) THEN
964 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
965 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
966 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_curr_calling_sequence);
967 FND_MESSAGE.SET_TOKEN('PARAMETERS',
968 'Invoice Id = '|| to_char(p_invoice_id));
969 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
970
971 END IF;
972
973 APP_EXCEPTION.RAISE_EXCEPTION;
974
975 END generateDistsForInvoice;
976
977
978 END AP_GENERATE_DISTRIBUTIONS_PKG;