[Home] [Help]
PACKAGE BODY: APPS.AP_MATCHING_UTILS_PKG
Source
1 PACKAGE BODY AP_MATCHING_UTILS_PKG AS
2 /* $Header: apmtutlb.pls 120.49.12020000.5 2013/01/17 09:20:01 rnimmaka ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AP_MATCHING_UTILS_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_MATCHING_UTILS_PKG.';
21
22 Procedure Initialize (
23 P_invoice_id IN NUMBER,
24 P_quick_po_id IN NUMBER DEFAULT NULL, --5386827
25 P_invoice_num IN OUT NOCOPY VARCHAR2,
26 P_invoice_amount IN OUT NOCOPY NUMBER,
27 P_invoice_date IN OUT NOCOPY DATE,
28 P_vendor_id IN OUT NOCOPY NUMBER,
29 P_vendor_site_id IN OUT NOCOPY NUMBER,
30 P_vendor_name IN OUT NOCOPY VARCHAR2,
31 P_vendor_number IN OUT NOCOPY VARCHAR2,
32 P_vendor_site_code IN OUT NOCOPY VARCHAR2,
33 P_vat_registration_num IN OUT NOCOPY VARCHAR2,
34 P_inv_curr_code IN OUT NOCOPY VARCHAR2,
35 P_inv_type_lookup_code IN OUT NOCOPY VARCHAR2,
36 P_inv_description IN OUT NOCOPY VARCHAR2,
37 P_income_tax_region IN OUT NOCOPY VARCHAR2,
38 -- P_ussgl_transaction_code IN OUT NOCOPY VARCHAR2, - Bug 4277744
39 P_awt_group_id IN OUT NOCOPY NUMBER,
40 P_batch_id IN OUT NOCOPY NUMBER,
41 P_gl_date IN OUT NOCOPY DATE,
42 P_po_number IN OUT NOCOPY VARCHAR2, -- Bug 5386827
43 P_vendor_type_lookup_code IN OUT NOCOPY VARCHAR2,
44 P_item_structure_id IN OUT NOCOPY NUMBER,
45 P_payment_terms_id IN OUT NOCOPY NUMBER,
46 P_payment_terms_name IN OUT NOCOPY VARCHAR2,
47 P_period_name IN OUT NOCOPY VARCHAR2,
48 P_minimum_accountable_unit IN OUT NOCOPY NUMBER,
49 P_precision IN OUT NOCOPY NUMBER,
50 P_release_amount_net_of_tax IN OUT NOCOPY NUMBER)
51 IS
52 debug_info varchar2(100);
53 BEGIN
54 -- select all the out variables from the view ap_invoices_v
55
56 debug_info := 'select out variables from ap_invoices_v';
57 --bug 5056082 Replacing the view with base tables
58 SELECT ai.invoice_num,
59 ai.invoice_amount,
60 ai.invoice_date,
61 ai.vendor_id,
62 ai.vendor_site_id,
63 HP.PARTY_NAME VENDOR_NAME,
64 PV.SEGMENT1 VENDOR_NUMBER,
65 PVS.VENDOR_SITE_CODE VENDOR_SITE_CODE,
66 ai.invoice_currency_code,
67 ai.invoice_type_lookup_code,
68 ai.description,
69 DECODE(PV.TYPE_1099, '','', DECODE(ASP.COMBINED_FILING_FLAG, 'N', '',
70 DECODE(ASP.INCOME_TAX_REGION_FLAG, 'Y', DECODE(PVS.country, 'US',PVS.state, NULL), ASP.INCOME_TAX_REGION))) INCOME_TAX_REGION,
71 -- ai.ussgl_transaction_code, - bug 4277744
72 ai.awt_group_id,
73 ai.batch_id,
74 ai.gl_date,
75 ai.terms_id,
76 AT.NAME TERMS_NAME ,
77 AP_INVOICES_PKG.GET_PERIOD_NAME( AI.GL_DATE, NULL, AI.ORG_ID) PERIOD_NAME,
78 FC.MINIMUM_ACCOUNTABLE_UNIT,
79 FC.PRECISION PRECISION,
80 ai.release_amount_net_of_tax
81 INTO
82 P_invoice_num,
83 P_invoice_amount,
84 P_invoice_date,
85 P_vendor_id,
86 P_vendor_site_id,
87 P_vendor_name,
88 P_vendor_number,
89 P_vendor_site_code,
90 P_inv_curr_code,
91 P_inv_type_lookup_code,
92 P_inv_description,
93 P_income_tax_region,
94 -- P_ussgl_transaction_code, - Bug 4277744
95 P_awt_group_id,
96 P_batch_id,
97 P_gl_date,
98 P_payment_terms_id,
99 P_payment_terms_name,
100 P_period_name,
101 P_minimum_accountable_unit,
102 P_precision,
103 P_release_amount_net_of_tax
104 FROM
105 ap_invoices_all ai,
106 FND_CURRENCIES FC,
107 HZ_PARTIES HP,
108 ap_suppliers pv,
109 ap_supplier_sites_all pvs,
110 AP_TERMS AT,
111 AP_SYSTEM_PARAMETERS ASP,
112 FND_TERRITORIES_TL FND
113 WHERE ai.invoice_id = P_invoice_id
114 AND AI.TERMS_ID = AT.TERM_ID (+)
115 AND AI.VENDOR_SITE_ID = PVS.VENDOR_SITE_ID (+)
116 AND AI.INVOICE_CURRENCY_CODE = FC.CURRENCY_CODE (+)
117 AND AI.ORG_ID = ASP.ORG_ID
118 AND FND.territory_code(+) = AI.taxation_country
119 AND (AI.TAXATION_COUNTRY IS NULL OR FND.LANGUAGE = USERENV('LANG'))
120 AND AI.PARTY_ID = HP.PARTY_ID
121 AND HP.PARTY_ID = PV.PARTY_ID (+);
122
123 -- get vendor_type lookup_code from po_vendors
124 SELECT vendor_type_lookup_code ,
125 vat_registration_num
126 INTO P_vendor_type_lookup_code,
127 P_vat_registration_num
128 FROM po_vendors
129 WHERE vendor_id = P_vendor_id;
130
131 -- Bug 5386827 : fetch po_number
132 -- select the po_number if the quick_po_id is specified,
133 debug_info := 'select the po_number';
134 -- Changed this Query for CLM Document Numbering 9503239
135 If (P_quick_po_id is NOT NULL) Then
136 SELECT po_number
137 INTO P_po_number
138 FROM po_headers_ap_v
139 WHERE po_header_id = P_quick_po_id;
140 End if;
141
142
143 -- select item_structure id for Item category for the product
144 -- Purchasing
145 -- Get the structure id for Purchasing
146 SELECT mdsv.structure_id
147 INTO P_item_structure_id
148 FROM mtl_default_sets_view mdsv
149 WHERE mdsv.functional_area_id = 2;
150
151 EXCEPTION
152 WHEN OTHERS THEN
153 If (SQLCODE <> -20001) Then
154 fnd_message.set_name('SQLAP','AP_DEBUG');
155 fnd_message.set_token('ERROR',SQLERRM);
156 fnd_message.set_token('DEBUG_INFO',debug_info);
157 End if;
158 app_exception.raise_exception;
159
160 END Initialize;
161
162 Procedure Get_Num_PO_Dists (
163 P_line_location_id IN NUMBER,
164 P_num_po_dists IN OUT NOCOPY NUMBER,
165 P_po_distribution_id IN OUT NOCOPY NUMBER) IS
166
167 Begin
168
169 SELECT count(*)
170 INTO p_num_po_dists
171 FROM po_distributions
172 WHERE line_location_id = P_line_location_id;
173
174 If (p_num_po_dists = 1 ) Then
175 SELECT po_distribution_id
176 INTO p_po_distribution_id
177 FROM po_distributions
178 WHERE line_location_id = P_line_location_id;
179 Else
180 p_po_distribution_id := null;
181 End if;
182
183 End Get_Num_PO_Dists;
184
185 Procedure Get_Receipt_Quantities (
186 P_rcv_transaction_id IN NUMBER,
187 P_ordered_qty IN OUT NOCOPY NUMBER,
188 P_cancelled_qty IN OUT NOCOPY NUMBER,
189 P_received_qty IN OUT NOCOPY NUMBER,
190 P_corrected_qty IN OUT NOCOPY NUMBER,
191 P_delivered_qty IN OUT NOCOPY NUMBER,
192 P_transaction_qty IN OUT NOCOPY NUMBER,
193 P_billed_qty IN OUT NOCOPY NUMBER,
194 P_accepted_qty IN OUT NOCOPY NUMBER,
195 P_rejected_qty IN OUT NOCOPY NUMBER) IS
196
197 l_po_ordered_qty NUMBER;
198 l_po_received_qty NUMBER;
199 l_po_corrected_qty NUMBER;
200 l_po_delivered_qty NUMBER;
201 l_po_transaction_qty NUMBER;
202 l_po_billed_qty NUMBER;
203 l_po_accepted_qty NUMBER;
204 l_po_rejected_qty NUMBER;
205 l_po_cancelled_qty NUMBER;
206 Begin
207
208 -- Call the PO function which returns quantities in Po UOM and
209 -- Receipt UOM.
210
211 RCV_INVOICE_MATCHING_SV.Get_Quantities (
212 P_rcv_transaction_id,
213 l_po_ordered_qty,
214 l_po_cancelled_qty,
215 l_po_received_qty,
216 l_po_corrected_qty,
217 l_po_delivered_qty,
218 l_po_transaction_qty,
219 l_po_billed_qty,
220 l_po_accepted_qty,
221 l_po_rejected_qty,
222 P_ordered_qty,
223 P_cancelled_qty,
224 P_received_qty,
225 P_corrected_qty,
226 P_delivered_qty,
227 P_transaction_qty,
228 P_billed_qty,
229 P_accepted_qty,
230 P_rejected_qty );
231 End Get_Receipt_Quantities;
232
233 Procedure Get_Recpt_Dist_Qty_Billed (
234 P_rcv_transaction_id IN NUMBER,
235 P_po_distribution_id IN NUMBER,
236 P_billed_qty IN OUT NOCOPY NUMBER)
237 IS
238
239 Begin
240 -- Bug fix: 1712542 added the NVL in the WHERE clause
241 -- so that we get the quantity billed for the case when the
242 -- match option is PO, for which we do not stamp the
243 -- invoice distribution with rcv_transaction_id .
244
245 -- Bug 7532498 - Removed the NVL on rcv_transaction_id as in R12,
246 -- this code is used only for receipt matching and PO Match cases are
247 -- handled separately.
248
249 SELECT nvl(sum(DECODE( aid.dist_match_type,'PRICE_CORRECTION', 0,
250 'AMOUNT_CORRECTION', 0, 'ITEM_TO_SERVICE_PO', 0,'ITEM_TO_SERVICE_RECEIPT', 0,
251 NVL( aid.corrected_quantity,0 ) + NVL( aid.quantity_invoiced,0 ))),0) /*Bug13691308*/
252 /*nvl(sum(nvl(quantity_invoiced,0)),0)-nvl(sum(nvl(price_correct_qty,0)),0)*/ --6509492
253 INTO p_billed_qty
254 FROM ap_invoice_distributions AID
255 WHERE AID.rcv_transaction_id = P_rcv_transaction_id --bug 7532498
256 AND AID.po_distribution_id = P_po_distribution_id
257 --BUGFIX:5641346
258 AND line_type_lookup_code NOT IN ('RETAINAGE','PREPAY');
259
260 Exception
261 WHEN OTHERS THEN
262 app_exception.raise_exception;
263
264 End Get_Recpt_Dist_Qty_Billed;
265
266 /*-------------------------------------------------------------------------
267 This procedure will be called by PO whenever the receipt is adjusted
268 The input parameters refer to
269 p_parent_rcv_txn_id : the original 'RECEIVE' transaction,
270 p_adjusted_rcv_txn_id : the 'ADJUST' or 'RETURN' transaction
271 p_adjusted_date : the transaction_date on ADJUST or RETURN transaction
272 p_user_id : WHO column information from the form
273 p_login_id : WHO column information from the form
274 --------------------------------------------------------------------------*/
275 Procedure Insert_Adjusted_Receipt_IDs (
276 p_parent_rcv_txn_id IN NUMBER,
277 p_adjusted_rcv_txn_id IN NUMBER,
278 p_adjusted_date IN DATE,
279 p_user_id IN NUMBER,
280 p_login_id IN NUMBER) IS
281 Begin
282
283 -- find out if the receipt is matched -check quantity billed on the
284 -- receipt
285
286
287
288 -- Insert data into the table AP_MATCHED_RECT_ADJ_ALL
289
290 -- set all who column dates to sysdate and conc program related
291 -- columns to null
292
293 -- just entering the stub package right now.
294 null;
295 End Insert_Adjusted_receipt_Ids;
296
297 Function Get_Correction_Quantity (
298 p_invoice_id IN NUMBER,
299 p_line_number IN NUMBER)
300 Return Number IS
301
302 l_existing_corr_qty Number;
303
304 Begin
305
306 /*
307 * bug 7118571 - added ap_invoices_all to the query to consider non-cancelled invoices only
308 */
309 Select Nvl(Sum(ail.quantity_invoiced), 0)
310 Into l_existing_corr_qty
311 From ap_invoice_lines_all ail
312 ,ap_invoices_all ai
313 Where ail.corrected_inv_id = p_invoice_id
314 And ail.corrected_line_number = p_line_number
315 And ail.match_type = 'QTY_CORRECTION'
316 And ail.invoice_id = ai.invoice_id
317 And ai.cancelled_date is null;
318
319 Return l_existing_corr_qty;
320
321 End Get_Correction_Quantity;
322
323 Function Get_Correction_Unit_Price (
324 p_invoice_id IN NUMBER,
325 p_line_number IN NUMBER)
326 Return Number IS
327
328 l_corrected_unit_price Number;
329 l_correction_amount Number;
330 l_original_amount Number;
331 l_original_qty_invoiced Number;
332 l_existing_corr_qty Number;
333 l_corrected_original_amt Number; --7187973
334
335 Begin
336
337 /*
338 * bug 7118571 - added ap_invoices_all to the query to consider non-cancelled invoices only
339 */
340
341 --Commented below code and introduced new call to find l_correction_amount
342 /* Select Nvl(Sum(ail.unit_price * ail.quantity_invoiced), 0)
343 Into l_correction_amount
344 From ap_invoice_lines_all ail
345 ,ap_invoices_all ai
346 Where ail.corrected_inv_id = p_invoice_id
347 And ail.corrected_line_number = p_line_number
348 And ail.match_type = 'PRICE_CORRECTION'
349 And ai.invoice_id = ail.invoice_id
350 And ai.cancelled_date is null; */
351
352 --Uncommented the code for bug#12312362
353 -- Bug7187973 Starts
354 Select (NVL(ail.unit_price, 0) * NVL(ail.quantity_invoiced, 0)),
355 NVL( ail.quantity_invoiced,0)
356 Into l_original_amount, l_original_qty_invoiced
357 From ap_invoice_lines_all ail
358 Where ail.invoice_id = p_invoice_id
359 And ail.line_number = p_line_number;
360 -- Bug 7187973 Ends
361
362 l_existing_corr_qty := Get_Correction_Quantity(p_invoice_id, p_line_number);
363 --Introduced for bug#12312362
364 l_correction_amount := Get_Correction_Amount(p_invoice_id, p_line_number);
365
366 --Commented below code for bug#12312362
367 -- Bug 7187973 Starts
368 -- The original amount should be calculated with latest quantity.
369 -- Latest quantity here is sum of the orginal quantity Invoiced and
370 -- Corrected quantity.
371 /*
372 Select (NVL(ail.unit_price, 0) * NVL(ail.quantity_invoiced, 0)),
373 (NVL(ail.unit_price, 0) * (NVL(ail.quantity_invoiced, 0) + NVL(l_existing_corr_qty,0)) ),
374 NVL( ail.quantity_invoiced,0)
375 Into l_original_amount,
376 l_corrected_original_amt,
377 l_original_qty_invoiced
378 From ap_invoice_lines_all ail
379 Where ail.invoice_id = p_invoice_id
380 And ail.line_number = p_line_number; */
381
382 -- Bug 7187973 Ends
383
384
385
386 --Bug#12312362 replaced l_corrected_original_amt with l_original_amt
387 --Bug:4515876
388 -- bug7187973 l_original_amount is replaced with
389 -- l_corrected_original_amt
390 IF l_correction_amount <> 0 THEN
391 IF (l_original_qty_invoiced+l_existing_corr_qty) > 0 THEN
392 l_corrected_unit_price := (l_correction_amount + l_original_amount)/
393 (l_original_qty_invoiced + l_existing_corr_qty);
394 END IF;
395 ELSE
396 IF l_original_qty_invoiced > 0 THEN --8299022
397 l_corrected_unit_price := l_original_amount / l_original_qty_invoiced;
398 END IF;
399 END IF;
400 Return l_corrected_unit_price;
401
402 End Get_Correction_Unit_Price;
403
404 Function Get_Correction_Quantity_Dist (
405 p_invoice_dist_id IN NUMBER)
406 Return Number IS
407
408 l_existing_corr_qty Number;
409
410 Begin
411
412 Select Nvl(Sum(aid.corrected_quantity), 0)
413 Into l_existing_corr_qty
414 From ap_invoice_distributions_all aid,
415 ap_invoice_lines_all ail
416 Where aid.corrected_invoice_dist_id = p_invoice_dist_id
417 And ail.line_number = aid.invoice_line_number
418 And ail.match_type = 'QTY_CORRECTION'
419 And aid.invoice_id=ail.invoice_id; --bug 5015014
420
421 Return l_existing_corr_qty;
422
423 End Get_Correction_Quantity_Dist;
424
425 Function Get_Correction_Amount (
426 p_invoice_id IN NUMBER,
427 p_line_number IN NUMBER)
428 Return Number IS
429
430 l_existing_corr_amt Number;
431
432 Begin
433
434 /*
435 * bug 7118571 - added ap_invoices_all to the query to consider non-cancelled invoices only
436 */
437
438 Select Nvl(Sum(ail.amount), 0)
439 Into l_existing_corr_amt
440 From ap_invoice_lines_all ail
441 ,ap_invoices_all ai
442 Where ail.corrected_inv_id = p_invoice_id
443 And ail.corrected_line_number = p_line_number
444 And ail.match_type In ( 'QTY_CORRECTION', 'PRICE_CORRECTION',
445 'AMOUNT_CORRECTION')
446 And ai.invoice_id = ail.invoice_id
447 And ai.cancelled_date is null;
448
449 Return l_existing_corr_amt;
450
451 End Get_Correction_Amount;
452
453
454 Procedure Get_Num_Line_Dists (
455 P_invoice_id IN NUMBER,
456 P_invoice_line_number IN NUMBER,
457 P_num_line_dists IN OUT NOCOPY NUMBER,
458 P_inv_distribution_id IN OUT NOCOPY NUMBER) IS
459
460 Begin
461
462 -- Bug 5585744 , added the line_type_lookup_code condition
463 SELECT count(*)
464 INTO p_num_line_dists
465 FROM ap_invoice_distributions_all
466 WHERE invoice_id = P_invoice_id
467 AND invoice_line_number = P_invoice_line_number
468 AND line_type_lookup_code in ('ITEM', 'ACCRUAL')
469 AND prepay_distribution_id is NULL;
470
471 If (p_num_line_dists = 1 ) Then
472 SELECT invoice_distribution_id
473 INTO p_inv_distribution_id
474 FROM ap_invoice_distributions_all
475 WHERE invoice_id = P_invoice_id
476 AND invoice_line_number = P_invoice_line_number
477 AND line_type_lookup_code in ('ITEM', 'ACCRUAL')
478 AND prepay_distribution_id is NULL;
479
480 Else
481 p_inv_distribution_id := null;
482 End if;
483
484 End Get_Num_Line_Dists;
485
486
487 --Invoice Lines: Matching
488 /*----------------------------------------------------------------------
489 |This procedure when provided with a Invoice Line, based on the |
490 |information provided on the line will match the invoice line |
491 |appropriately to either PO or Receipt or perform Price/Quantity/Line |
492 |correction. |
493 | |
494 -----------------------------------------------------------------------*/
495 Procedure Match_Invoice_Line(
496 P_Invoice_Id IN NUMBER,
497 P_Invoice_Line_Number IN NUMBER,
498 P_Overbill_Flag IN VARCHAR2,
499 P_Calling_Sequence IN VARCHAR2) IS
500
501 CURSOR Invoice_Lines_Cur IS
502 SELECT *
503 FROM ap_invoice_lines
504 WHERE invoice_id = p_invoice_id
505 AND line_number = p_invoice_line_number;
506
507 l_invoice_line_rec ap_invoice_lines%ROWTYPE;
508 l_match_mode VARCHAR2(8);
509 l_index po_distributions_all.po_distribution_id%TYPE;
510 l_dist_ccid ap_invoice_distributions_all.dist_code_combination_id%TYPE;
511 l_corr_inv_dist_id ap_invoice_distributions_all.invoice_distribution_id%TYPE;
512 l_dist_tab ap_matching_pkg.dist_tab_type;
513 l_othr_chrg_tab ap_othr_chrg_match_pkg.othr_chrg_match_tabtype;
514 l_corr_dist_tab ap_matching_pkg.corr_dist_tab_type;
515 l_inv_line_tab ap_invoice_corrections_pkg.line_tab_type;
516 l_inv_dist_tab ap_invoice_corrections_pkg.dist_tab_type;
517 l_debug_info VARCHAR2(2000);
518 current_calling_sequence VARCHAR2(2000);
519 l_api_name VARCHAR2(50);
520
521 BEGIN
522
523 l_api_name := 'Match_Invoice_Line';
524 current_calling_sequence := 'AP_MATCHING_UTILS_PKG.Match_Invoice_Line <-'||p_calling_sequence;
525 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
526 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_MATCHING_UTILS_PKG.Match_Invoice_Line(+)');
527 END IF;
528
529 l_debug_info := 'Open Cursor Invoice_Lines_Cur';
530 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
531 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
532 END IF;
533
534 OPEN Invoice_Lines_Cur;
535 FETCH Invoice_Lines_Cur INTO l_invoice_line_rec;
536 CLOSE Invoice_Lines_Cur;
537
538
539 l_debug_info := 'Derive the Match_Mode for the matching';
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 IF (l_invoice_line_rec.match_type IN ('ITEM_TO_PO','ITEM_TO_RECEIPT',
545 'ITEM_TO_SERVICE_PO', 'ITEM_TO_SERVICE_RECEIPT',
546 'PRICE_CORRECTION','QTY_CORRECTION',
547 'AMOUNT_CORRECTION')) THEN
548 /* Amount Based Matching */
549 IF (SIGN(l_invoice_line_rec.amount) < 0) THEN
550 l_match_mode := 'CR-';
551 ELSE
552 l_match_mode := 'STD-';
553 END IF;
554
555 IF (l_invoice_line_rec.po_distribution_id IS NULL) THEN
556
557 l_match_mode := l_match_mode||'PS';
558
559 ELSE
560
561 l_match_mode := l_match_mode||'PD';
562
563 l_index := l_invoice_line_rec.po_distribution_id;
564
565 IF (l_invoice_line_rec.match_type IN ('ITEM_TO_PO','ITEM_TO_RECEIPT',
566 'ITEM_TO_SERVICE_PO', 'ITEM_TO_SERVICE_RECEIPT')) THEN
567 /* AmounT Based Matching */
568 l_dist_tab(l_index).po_distribution_id := l_invoice_line_rec.po_distribution_id;
569 l_dist_tab(l_index).amount := l_invoice_line_rec.amount;
570 l_dist_tab(l_index).quantity_invoiced := l_invoice_line_rec.quantity_invoiced;
571 l_dist_tab(l_index).unit_price := l_invoice_line_rec.unit_price;
572
573 --Bugfix:4699604
574 BEGIN
575 SELECT code_combination_id
576 INTO l_dist_tab(l_index).dist_ccid
577 FROM po_distributions_ap_v
578 WHERE po_distribution_id = l_invoice_line_rec.po_distribution_id;
579 EXCEPTION WHEN OTHERS THEN
580 NULL;
581 END;
582
583 ELSE /* match type IN ('PRICE_CORRECTION','QTY_CORRECTION', 'AMOUNT_CORRECTION') */
584
585 --bugfix:5641346
586 BEGIN
587
588 SELECT invoice_distribution_id, dist_code_combination_id
589 INTO l_corr_inv_dist_id, l_dist_ccid
590 FROM ap_invoice_distributions
591 WHERE invoice_id = l_invoice_line_rec.corrected_inv_id
592 AND invoice_line_number = l_invoice_line_rec.corrected_line_number
593 AND po_distribution_id = l_invoice_line_rec.po_distribution_id
594 AND line_type_lookup_code in ('ITEM', 'ACCRUAL') /*Bug15980592*/
595 AND prepay_distribution_id is NULL; /*Bug15980592*/
596
597
598 EXCEPTION WHEN OTHERS THEN
599 NULL;
600 END;
601
602 l_corr_dist_tab(l_index).po_distribution_id :=
603 l_invoice_line_rec.po_distribution_id;
604 l_corr_dist_tab(l_index).corrected_inv_dist_id := l_corr_inv_dist_id;
605 l_corr_dist_tab(l_index).amount := l_invoice_line_rec.amount;
606 l_corr_dist_tab(l_index).dist_ccid := l_dist_ccid;
607
608 /* Amount Based Matching */
609 IF l_invoice_line_rec.match_type <> 'AMOUNT_CORRECTION' THEN
610
611 l_corr_dist_tab(l_index).corrected_quantity :=
612 l_invoice_line_rec.quantity_invoiced;
613 l_corr_dist_tab(l_index).unit_price := l_invoice_line_rec.unit_price;
614
615 END IF;
616
617
618 END IF;
619
620 END IF;
621
622 END IF;
623
624
625 IF (l_invoice_line_rec.match_type = 'ITEM_TO_PO') THEN
626
627 l_debug_info := 'Calling AP_Matching_Pkg.Base_Credit_Po_Match';
628 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
629 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
630 END IF;
631
632 Ap_Matching_Pkg.Base_Credit_PO_Match(
633 X_match_mode => l_match_mode,
634 X_invoice_id => p_invoice_id,
635 X_invoice_line_number => p_invoice_line_number,
636 X_Po_Line_Location_id => l_invoice_line_rec.po_line_location_id,
637 X_Dist_Tab => l_dist_tab,
638 X_amount => l_invoice_line_rec.amount,
639 X_quantity => l_invoice_line_rec.quantity_invoiced,
640 X_unit_price => l_invoice_line_rec.unit_price,
641 X_uom_lookup_code => l_invoice_line_rec.unit_meas_lookup_code,
642 X_final_match_flag => l_invoice_line_rec.final_match_flag,
643 X_overbill_flag => p_overbill_flag,
644 X_retained_amount => l_invoice_line_rec.retained_amount,
645 X_freight_amount => NULL,
646 X_freight_description => NULL,
647 X_misc_amount => NULL,
648 X_misc_description => NULL,
649 X_calling_sequence => current_calling_sequence) ;
650
651
652 ELSIF (l_invoice_line_rec.match_type = 'ITEM_TO_RECEIPT') THEN
653
654 l_debug_info := 'Calling AP_Rect_Match_Pkg.Base_Credit_RCV_Match';
655 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
656 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
657 END IF;
658
659 --Bug 5524881 ISP Receipt Matching
660 IF l_invoice_line_rec.rcv_transaction_id IS NULL THEN
661 Match_To_Rcv_Shipment_Line(P_Invoice_Id => p_invoice_id,
662 P_Invoice_Line_Number => p_invoice_line_number,
663 P_Calling_Sequence => current_calling_sequence);
664 ELSE
665
666 Ap_Rect_Match_Pkg.Base_Credit_RCV_Match(
667 X_match_mode => l_match_mode,
668 X_invoice_id => p_invoice_id,
669 X_invoice_line_number => p_invoice_line_number,
670 X_Po_Line_Location_id => l_invoice_line_rec.po_line_location_id,
671 X_Rcv_Transaction_id => l_invoice_line_rec.rcv_transaction_id,
672 X_Dist_Tab => l_dist_tab,
673 X_amount => l_invoice_line_rec.amount,
674 X_quantity => l_invoice_line_rec.quantity_invoiced,
675 X_unit_price => l_invoice_line_rec.unit_price,
676 X_uom_lookup_code => l_invoice_line_rec.unit_meas_lookup_code,
677 X_freight_amount => NULL,
678 X_freight_description => NULL,
679 X_misc_amount => NULL,
680 X_misc_description => NULL,
681 X_retained_amount => l_invoice_line_rec.retained_amount,
682 X_calling_sequence => current_calling_sequence) ;
683 END IF;
684
685 ELSIF (l_invoice_line_rec.match_type = 'OTHER_TO_RECEIPT') THEN
686
687 l_debug_info := 'Calling AP_Othr_Chrg_Match_Pkg.Othr_Chrg_Match';
688 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
689 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
690 END IF;
691
692 l_othr_chrg_tab(1).rcv_txn_id := l_invoice_line_rec.rcv_transaction_id;
693 l_othr_chrg_tab(1).charge_amt := l_invoice_line_rec.amount;
694 l_othr_chrg_tab(1).base_amt := NULL;
695 l_othr_chrg_tab(1).rounding_amt := NULL;
696 l_othr_chrg_tab(1).rcv_qty := l_invoice_line_rec.quantity_invoiced;
697
698 Ap_Othr_Chrg_Match_Pkg.Othr_Chrg_Match(
699 X_invoice_id => p_invoice_id,
700 X_invoice_line_number => p_invoice_line_number,
701 X_line_type => l_invoice_line_rec.line_type_lookup_code,
702 X_prorate_flag => 'N',
703 X_account_id => l_invoice_line_rec.default_dist_ccid,
704 X_description => l_invoice_line_rec.description,
705 X_total_amount => l_invoice_line_rec.amount ,
706 X_othr_chrg_tab => l_othr_chrg_tab,
707 X_row_count => 1,
708 X_calling_sequence => current_calling_sequence);
709
710 /* Amount Based Matching */
711 ELSIF (l_invoice_line_rec.match_type = 'ITEM_TO_SERVICE_PO') THEN
712
713 l_debug_info := 'Calling AP_Po_Amt_Match_Pkg.AP_Amt_Match';
714 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
715 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
716 END IF;
717
718 Ap_Po_Amt_Match_Pkg.Ap_Amt_Match(
719 X_match_mode => l_match_mode,
720 X_invoice_id => p_invoice_id,
721 X_invoice_line_number => p_invoice_line_number,
722 X_Dist_Tab => l_dist_tab,
723 X_Po_Line_Location_id => l_invoice_line_rec.po_line_location_id,
724 X_amount => l_invoice_line_rec.amount,
725 X_quantity => l_invoice_line_rec.quantity_invoiced,
726 X_unit_price => l_invoice_line_rec.unit_price,
727 X_uom_lookup_code => l_invoice_line_rec.unit_meas_lookup_code,
728 X_final => l_invoice_line_rec.final_match_flag,
729 X_overbill => p_overbill_flag,
730 X_freight_amount => NULL,
731 X_freight_description => NULL,
732 X_misc_amount => NULL,
733 X_misc_description => NULL,
734 X_retained_amount => l_invoice_line_rec.retained_amount,
735 X_calling_sequence => current_calling_sequence) ;
736
737
738 /* Amount Based Matching */
739 ELSIF (l_invoice_line_rec.match_type = 'ITEM_TO_SERVICE_RECEIPT') THEN
740
741 l_debug_info := 'AP_Rct_Amt_Match_Pkg.AP_Amt_Match';
742 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
743 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
744 END IF;
745
746 --Bug 5524881 ISP Receipt Matching
747 IF l_invoice_line_rec.rcv_transaction_id IS NULL THEN
748 Match_To_Rcv_Shipment_Line(P_Invoice_Id => p_invoice_id,
749 P_Invoice_Line_Number => p_invoice_line_number,
750 P_Calling_Sequence => current_calling_sequence);
751 ELSE
752
753 Ap_Rct_Amt_Match_Pkg.Ap_Amt_Match(
754 X_match_mode => l_match_mode,
755 X_invoice_id => p_invoice_id,
756 X_invoice_line_number => p_invoice_line_number,
757 X_Dist_Tab => l_dist_tab,
758 X_Po_Line_Location_id => l_invoice_line_rec.po_line_location_id,
759 X_Rcv_Transaction_id => l_invoice_line_rec.rcv_transaction_id,
760 X_amount => l_invoice_line_rec.amount,
761 X_quantity => l_invoice_line_rec.quantity_invoiced,
762 X_unit_price => l_invoice_line_rec.unit_price,
763 X_uom_lookup_code => l_invoice_line_rec.unit_meas_lookup_code,
764 X_freight_amount => NULL,
765 X_freight_description => NULL,
766 X_misc_amount => NULL,
767 X_misc_description => NULL,
768 X_retained_amount => l_invoice_line_rec.retained_amount,
769 X_calling_sequence => current_calling_sequence) ;
770 END IF;
771
772 ELSIF (l_invoice_line_rec.match_type IN ('PRICE_CORRECTION','QTY_CORRECTION')) THEN
773
774 IF (l_invoice_line_rec.rcv_transaction_id IS NULL) THEN
775
776 l_debug_info := 'Calling AP_Matching_Pkg.Price_Quantity_Correct_Inv_PO';
777 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
778 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
779 END IF;
780
781 Ap_Matching_Pkg.Price_Quantity_Correct_Inv_PO(
782 X_Invoice_Id => p_invoice_id,
783 X_Invoice_Line_Number => p_invoice_line_number,
784 X_Corrected_Invoice_Id => l_invoice_line_rec.corrected_inv_id,
785 X_Corrected_Line_Number => l_invoice_line_rec.corrected_line_number,
786 X_Correction_Type => l_invoice_line_rec.match_type,
787 X_Correction_Quantity => l_invoice_line_rec.quantity_invoiced,
788 X_Correction_Amount => l_invoice_line_rec.amount,
789 X_Correction_Price => l_invoice_line_rec.unit_price,
790 X_Match_Mode => l_match_mode,
791 X_Po_Line_Location_Id => l_invoice_line_rec.po_line_location_id,
792 X_Corr_Dist_Tab => l_corr_dist_tab,
793 X_Final_Match_Flag => l_invoice_line_rec.final_match_flag,
794 X_Uom_Lookup_Code => l_invoice_line_rec.unit_meas_lookup_code,
795 X_Retained_Amount => l_invoice_line_rec.retained_amount,
796 X_Calling_Sequence => current_calling_sequence);
797
798 ELSE
799
800 l_debug_info := 'AP_Rect_Match_Pkg.Price_Quantity_Correct_Inv_RCV';
801 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
802 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
803 END IF;
804
805 Ap_Rect_Match_Pkg.Price_Quantity_Correct_Inv_RCV(
806 X_Invoice_Id => p_invoice_id,
807 X_Invoice_Line_Number => p_invoice_line_number,
808 X_Corrected_Invoice_Id => l_invoice_line_rec.corrected_inv_id,
809 X_Corrected_Line_Number => l_invoice_line_rec.corrected_line_number,
810 X_Correction_Type => l_invoice_line_rec.match_type,
811 X_Correction_Quantity => l_invoice_line_rec.quantity_invoiced,
812 X_Correction_Amount => l_invoice_line_rec.amount,
813 X_Correction_Price => l_invoice_line_rec.unit_price,
814 X_Match_Mode => l_match_mode,
815 X_Po_Line_Location_Id => l_invoice_line_rec.po_line_location_id,
816 X_Rcv_Transaction_Id => l_invoice_line_rec.rcv_transaction_id,
817 X_Corr_Dist_Tab => l_corr_dist_tab,
818 X_Uom_Lookup_Code => l_invoice_line_rec.unit_meas_lookup_code,
819 X_retained_amount => l_invoice_line_rec.retained_amount,
820 X_Calling_Sequence => current_calling_sequence);
821
822 END IF;
823
824 /* AmounT Based Matching */
825 ELSIF (l_invoice_line_rec.match_type = 'AMOUNT_CORRECTION') THEN
826
827 IF (l_invoice_line_rec.rcv_transaction_id IS NULL) THEN
828
829 l_debug_info := 'Calling AP_Po_Amt_Match_Pkg.Amount_Correct_Inv_Po';
830 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
831 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
832 END IF;
833
834 Ap_Po_Amt_Match_Pkg.Amount_Correct_Inv_PO(
835 X_Invoice_Id => p_invoice_id,
836 X_Invoice_Line_Number => p_invoice_line_number,
837 X_Corrected_Invoice_Id => l_invoice_line_rec.corrected_inv_id,
838 X_Corrected_Line_Number => l_invoice_line_rec.corrected_line_number,
839 X_Match_Mode => l_match_mode,
840 X_Correction_Amount => l_invoice_line_rec.amount,
841 X_Po_Line_Location_Id => l_invoice_line_rec.po_line_location_id,
842 X_Corr_Dist_Tab => l_corr_dist_tab,
843 X_Final_Match_Flag => l_invoice_line_rec.final_match_flag,
844 X_Uom_Lookup_Code => l_invoice_line_rec.unit_meas_lookup_code,
845 X_Retained_Amount => l_invoice_line_rec.retained_amount,
846 X_Calling_Sequence => current_calling_sequence);
847
848 ELSE
849
850 l_debug_info := 'Calling AP_Rct_Amt_Match_Pkg.Amount_Correct_Inv_Rcv';
851 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
852 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
853 END IF;
854
855 Ap_Rct_Amt_Match_Pkg.Amount_Correct_Inv_Rcv(
856 X_Invoice_Id => p_invoice_id,
857 X_Invoice_Line_Number => p_invoice_line_number,
858 X_Corrected_Invoice_Id => l_invoice_line_rec.corrected_inv_id,
859 X_Corrected_Line_Number => l_invoice_line_rec.corrected_line_number,
860 X_Match_Mode => l_match_mode,
861 X_Correction_Amount => l_invoice_line_rec.amount,
862 X_Po_Line_Location_Id => l_invoice_line_rec.po_line_location_id,
863 X_Rcv_Transaction_Id => l_invoice_line_rec.rcv_transaction_id,
864 X_Corr_Dist_Tab => l_corr_dist_tab,
865 X_Uom_Lookup_Code => l_invoice_line_rec.unit_meas_lookup_code,
866 X_retained_amount => l_invoice_line_rec.retained_amount,
867 X_Calling_Sequence => current_calling_sequence);
868
869 END IF;
870
871 ELSIF (l_invoice_line_rec.match_type = 'LINE_CORRECTION') THEN
872 l_debug_info := 'Calling AP_Invoice_Corrections_Pkg.Invoice_Correction';
873 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
874 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
875 END IF;
876
877 AP_INVOICE_CORRECTIONS_PKG.Invoice_Correction(
878 X_Invoice_Id => p_invoice_id,
879 X_Invoice_Line_Number => p_invoice_line_number,
880 X_Corrected_Invoice_Id => l_invoice_line_rec.corrected_inv_id,
881 X_Corrected_Line_Number => l_invoice_line_rec.corrected_line_number,
882 X_Prorate_Lines_Flag => 'N',
883 X_Prorate_Dists_Flag => 'Y',
884 X_Correction_Quantity => l_invoice_line_rec.quantity_invoiced,
885 X_Correction_Amount => l_invoice_line_rec.amount,
886 X_Correction_Price => l_invoice_line_rec.unit_price,
887 X_Line_Tab => l_inv_line_tab,
888 X_Dist_Tab => l_inv_dist_tab,
889 X_Calling_Sequence => current_calling_sequence);
890
891 END IF;
892
893 EXCEPTION WHEN OTHERS THEN
894 IF (SQLCODE <> -20001) THEN
895 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
896 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
897 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
898 FND_MESSAGE.SET_TOKEN('PARAMETERS',
899 'P_invoice_id = ' || P_invoice_id
900 ||'P_invoice_line_number = '||P_invoice_line_number);
901 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
902
903 If (Invoice_Lines_Cur%ISOPEN) Then
904 Close Invoice_Lines_Cur;
905 End if;
906 END IF;
907 App_Exception.Raise_Exception;
908
909 END Match_Invoice_Line;
910
911 --Added the following new procedure for Amount-Based Matching Project
912
913 PROCEDURE Get_Recpt_Dist_Amt_Billed (
914 p_rcv_transaction_id IN NUMBER,
915 p_po_distribution_id IN NUMBER,
916 p_billed_amt IN OUT NOCOPY NUMBER) IS
917
918 BEGIN
919 SELECT sum(amount)
920 INTO p_billed_amt
921 FROM ap_invoice_distributions AID
922 --Bug#10140824. Removed NVL condition with rcv_transaction_id
923 WHERE AID.rcv_transaction_id = P_rcv_transaction_id
924 AND AID.po_distribution_id = P_po_distribution_id
925 --Bugfix:5641346
926 AND AID.line_type_lookup_code NOT IN ('RETAINAGE','PREPAY');
927
928
929 EXCEPTION
930 WHEN OTHERS THEN
931 app_exception.raise_exception;
932
933 END Get_Recpt_Dist_Amt_Billed;
934
935 Function Get_Avail_Dist_Corr_Amount (
936 p_invoice_dist_id IN NUMBER)
937 Return Number IS
938
939 l_dist_amt Number;
940 l_existing_corr_amt Number;
941 l_avail_corr_amt Number;
942
943 Begin
944
945 Select amount
946 Into l_dist_amt
947 From ap_invoice_distributions_all
948 Where invoice_distribution_id = p_invoice_dist_id;
949
950 Select Nvl(Sum(aid.amount), 0)
951 Into l_existing_corr_amt
952 From ap_invoice_distributions_all aid,
953 ap_invoice_lines_all ail
954 Where aid.corrected_invoice_dist_id = p_invoice_dist_id
955 And ail.line_number = aid.invoice_line_number
956 And ail.match_type = 'LINE_CORRECTION'
957 And aid.invoice_id=ail.invoice_id; --bug5015014
958
959 l_avail_corr_amt := l_dist_amt - abs(l_existing_corr_amt);
960
961 Return l_avail_corr_amt;
962
963 End Get_Avail_Dist_Corr_Amount;
964
965 Function Get_Line_Assoc_Charge (
966 P_invoice_id IN NUMBER,
967 P_line_number IN NUMBER)
968 Return Number IS
969
970 l_total_amount NUMBER;
971
972 Begin
973
974 Select Nvl(Sum(aarl.amount), 0)
975 Into l_total_amount
976 From ap_allocation_rule_lines aarl
977 Where invoice_id = p_invoice_id
978 And to_invoice_line_number = p_line_number;
979
980 Return l_total_amount;
981
982 End Get_Line_Assoc_Charge;
983
984 Function Get_Avail_Line_Corr_Amount (
985 P_invoice_id IN NUMBER,
986 P_line_number IN NUMBER)
987 Return Number IS
988
989 l_line_amt Number;
990 l_existing_corr_amt Number;
991 l_avail_corr_amt Number;
992
993 Begin
994
995 --In below select discard/cancelled and line_type conditions are not
996 --checked as those were already filtered invoice line number LOV.
997
998 Select amount
999 Into l_line_amt
1000 From ap_invoice_lines_all
1001 Where invoice_id = p_invoice_id
1002 And line_number = p_line_number;
1003
1004 Select Nvl(Sum(ail.amount), 0)
1005 Into l_existing_corr_amt
1006 From ap_invoice_lines_all ail
1007 Where ail.corrected_inv_id = p_invoice_id
1008 And ail.corrected_line_number = p_line_number
1009 And ail.match_type = 'LINE_CORRECTION'
1010 --Added below conditions for bug#10410133
1011 And nvl(discarded_flag, 'N') <> 'Y'
1012 And nvl(cancelled_flag, 'N') <> 'Y';
1013
1014 l_avail_corr_amt := l_line_amt - abs(l_existing_corr_amt);
1015
1016 Return l_avail_corr_amt;
1017
1018 End Get_Avail_Line_Corr_Amount;
1019
1020 Function Get_Avail_Line_Corr_Qty (
1021 P_invoice_id IN NUMBER,
1022 P_line_number IN NUMBER)
1023 Return Number IS
1024
1025 l_line_qty Number;
1026 l_existing_corr_qty Number;
1027 l_avail_corr_qty Number;
1028
1029 Begin
1030
1031 Select nvl(quantity_invoiced,0)
1032 Into l_line_qty
1033 From ap_invoice_lines_all
1034 Where invoice_id = p_invoice_id
1035 And line_number = p_line_number;
1036
1037 Select Nvl(Sum(ail.quantity_invoiced), 0)
1038 Into l_existing_corr_qty
1039 From ap_invoice_lines_all ail
1040 Where ail.corrected_inv_id = p_invoice_id
1041 And ail.corrected_line_number = p_line_number
1042 And ail.match_type = 'LINE_CORRECTION';
1043
1044 l_avail_corr_qty := l_line_qty - abs(l_existing_corr_qty);
1045
1046 Return l_avail_corr_qty;
1047
1048 End Get_Avail_Line_Corr_Qty;
1049
1050 Function Get_Avail_Inv_Corr_Amount (
1051 P_invoice_id IN NUMBER)
1052 Return Number IS
1053
1054 l_invoice_amt Number;
1055 l_existing_corr_amt Number;
1056 l_avail_corr_amt Number;
1057
1058 Begin
1059
1060 Select Nvl(Sum(amount), 0)
1061 Into l_invoice_amt
1062 From ap_invoice_lines_all ail
1063 Where ail.invoice_id = p_invoice_id
1064 And NVL(ail.match_type,'NOT_MATCHED') = 'NOT_MATCHED'
1065 --Added below conditions for bug#10410133
1066 And nvl(ail.discarded_flag, 'N') <> 'Y'
1067 And nvl(ail.cancelled_flag, 'N') <> 'Y'
1068 And ail.line_type_lookup_code in ('ITEM','FREIGHT','MISCELLANEOUS')
1069 -- Added below conditions for bug#9689019
1070 AND ail.line_type_lookup_code not in ('AWT','TAX')
1071 AND (ail.line_type_lookup_code <> 'PREPAY'
1072 OR nvl(ail.invoice_includes_prepay_flag,'N') = 'Y')
1073 AND not exists (select 'matched dists'
1074 from ap_invoice_distributions_all aid
1075 where aid.invoice_id = ail.invoice_id
1076 and aid.invoice_line_number = ail.line_number
1077 and aid.po_distribution_id is not null);
1078
1079 Select Nvl(Sum(ail.amount), 0)
1080 Into l_existing_corr_amt
1081 From ap_invoice_lines_all ail
1082 Where ail.corrected_inv_id = p_invoice_id
1083 And ail.match_type = 'LINE_CORRECTION'
1084 --Added below conditions for bug#10410133
1085 And nvl(discarded_flag, 'N') <> 'Y'
1086 And nvl(cancelled_flag, 'N') <> 'Y';
1087
1088 l_avail_corr_amt := l_invoice_amt - abs(l_existing_corr_amt);
1089
1090 Return l_avail_corr_amt;
1091
1092 End Get_Avail_Inv_Corr_Amount;
1093
1094
1095 Procedure AP_Upgrade_Po_Shipment(P_Po_Line_Location_Id IN NUMBER,
1096 P_Calling_Sequence IN VARCHAR2) IS
1097
1098 l_total_shipment_qty_invoiced NUMBER;
1099 l_total_shipment_amt_invoiced NUMBER;
1100 l_total_shipment_qty_applied NUMBER;
1101 l_total_shipment_amt_applied NUMBER;
1102
1103 l_total_dist_qty_invoiced NUMBER;
1104 l_total_dist_amt_invoiced NUMBER;
1105 l_total_dist_qty_applied NUMBER;
1106 l_total_dist_amt_applied NUMBER;
1107 l_po_distribution_id NUMBER;
1108
1109 TYPE dist_record_type is RECORD
1110 (po_distribution_id PO_DISTRIBUTIONS.po_distribution_id%TYPE, --Index Column
1111 total_dist_qty_invoiced AP_INVOICE_DISTRIBUTIONS.quantity_invoiced%TYPE,
1112 total_dist_qty_applied AP_INVOICE_DISTRIBUTIONS.quantity_invoiced%TYPE,
1113 total_dist_amt_invoiced AP_INVOICE_DISTRIBUTIONS.amount%TYPE,
1114 total_dist_amt_applied AP_INVOICE_DISTRIBUTIONS.amount%TYPE,
1115 matching_basis PO_LINE_LOCATIONS.matching_basis%TYPE);
1116
1117 TYPE dist_tab_type IS TABLE OF dist_record_type INDEX BY BINARY_INTEGER;
1118
1119 l_dist_tab dist_tab_type;
1120 l_matching_basis po_line_locations_all.matching_basis%TYPE;
1121 l_debug_info VARCHAR2(2000);
1122 l_api_name VARCHAR2(50);
1123
1124
1125 CURSOR C_Po_Dists_Financed IS
1126 SELECT aid.po_distribution_id,
1127 decode(pll.matching_basis,'QUANTITY',sum(nvl(aid.quantity_invoiced,0))),
1128 decode(pll.matching_basis,'AMOUNT',sum(nvl(aid.amount,0))),
1129 pll.matching_basis
1130 FROM ap_invoice_distributions_v aid,
1131 ap_invoices ai,
1132 po_line_locations pll
1133 WHERE pll.line_location_id = P_Po_Line_Location_Id
1134 AND pll.shipment_type <> 'PREPAYMENT'
1135 AND aid.line_location_id =pll.line_location_id
1136 AND aid.invoice_id = ai.invoice_id
1137 AND ai.invoice_type_lookup_code = 'PREPAYMENT'
1138 GROUP BY aid.po_distribution_id, pll.matching_basis;
1139
1140 CURSOR C_Po_Dists_Recouped IS
1141 select aid.po_distribution_id,
1142 decode(pll.matching_basis,'QUANTITY',sum(nvl(aid.quantity_invoiced,0))),
1143 decode(pll.matching_basis,'AMOUNT',sum(nvl(aid.amount,0))),
1144 pll.matching_basis
1145 from ap_invoice_distributions aid,
1146 po_distributions_all pd,
1147 po_line_locations pll
1148 where pll.line_location_id = p_po_line_location_id
1149 and pll.shipment_type <> 'PREPAYMENT'
1150 and aid.po_distribution_id = pd.po_distribution_id
1151 and pd.line_location_id = pll.line_location_id
1152 and aid.line_type_lookup_code = 'PREPAY'
1153 group by aid.po_distribution_id,pll.matching_basis;
1154
1155 BEGIN
1156
1157 l_api_name := 'AP_Upgrade_Po_Shipment';
1158 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1159 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_MATCHING_UTILS_PKG.Ap_Upgrade_Po_Shipment(+)');
1160 END IF;
1161
1162
1163 l_debug_info := 'Get Total Quantity/Amount Financed for this shipment across all invoices';
1164 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1165 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1166 END IF;
1167
1168 OPEN C_Po_Dists_Financed;
1169
1170 LOOP
1171
1172 FETCH C_Po_Dists_Financed INTO l_po_distribution_id,
1173 l_total_dist_qty_invoiced,
1174 l_total_dist_amt_invoiced,
1175 l_matching_basis;
1176
1177 EXIT WHEN C_Po_Dists_Financed%NOTFOUND;
1178
1179 l_dist_tab(l_po_distribution_id).po_distribution_id := l_po_distribution_id;
1180 l_dist_tab(l_po_distribution_id).total_dist_qty_invoiced := l_total_dist_qty_invoiced;
1181 l_dist_tab(l_po_distribution_id).total_dist_amt_invoiced := l_total_dist_amt_invoiced;
1182
1183 END LOOP;
1184
1185 CLOSE C_Po_Dists_Financed;
1186
1187 l_debug_info := 'Get Total Quantity/Amount Recouped for this shipment across all invoices';
1188 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1189 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1190 END IF;
1191
1192 OPEN C_Po_Dists_Recouped;
1193
1194 LOOP
1195
1196 l_debug_info := 'Fetch C_Po_Dists_Recouped into local variables';
1197 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1198 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1199 END IF;
1200
1201 FETCH C_Po_Dists_Recouped INTO l_po_distribution_id,
1202 l_total_dist_qty_applied,
1203 l_total_dist_amt_applied,
1204 l_matching_basis;
1205
1206 EXIT WHEN C_Po_Dists_Recouped%NOTFOUND;
1207
1208 l_dist_tab(l_po_distribution_id).po_distribution_id := l_po_distribution_id;
1209 l_dist_tab(l_po_distribution_id).total_dist_qty_applied := l_total_dist_qty_applied;
1210 l_dist_tab(l_po_distribution_id).total_dist_amt_applied := l_total_dist_amt_applied;
1211
1212 END LOOP;
1213
1214 CLOSE C_Po_Dists_Financed;
1215
1216
1217 l_debug_info := 'Update Po_Distributions';
1218 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1219 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1220 END IF;
1221
1222 FOR i in nvl(l_dist_tab.first,0) .. nvl(l_dist_tab.last,0) LOOP
1223
1224 IF (l_dist_tab.exists(i)) THEN
1225
1226 IF (l_matching_basis = 'QUANTITY') THEN
1227
1228 UPDATE po_distributions pod
1229 SET quantity_financed = l_dist_tab(i).total_dist_qty_invoiced,
1230 quantity_recouped = l_dist_tab(i).total_dist_qty_applied,
1231 quantity_billed = nvl(quantity_billed,0) - (l_dist_tab(i).total_dist_qty_invoiced -
1232 l_dist_tab(i).total_dist_qty_applied)
1233 WHERE pod.po_distribution_id = l_dist_tab(i).po_distribution_id
1234 AND pod.quantity_financed IS NULL;
1235
1236 l_total_shipment_qty_invoiced := nvl(l_total_shipment_qty_invoiced,0) +
1237 nvl(l_dist_tab(i).total_dist_qty_invoiced,0);
1238 l_total_shipment_qty_applied := nvl(l_total_shipment_qty_applied,0) +
1239 nvl(l_dist_tab(i).total_dist_qty_applied,0);
1240
1241 ELSIF (l_matching_basis = 'AMOUNT') THEN
1242
1243 UPDATE po_distributions pod
1244 SET amount_financed = l_total_dist_amt_invoiced,
1245 amount_recouped = l_total_dist_amt_applied,
1246 amount_billed = nvl(amount_billed,0) - (l_dist_tab(i).total_dist_amt_invoiced -
1247 l_dist_tab(i).total_dist_amt_applied)
1248 WHERE pod.po_distribution_id = l_dist_tab(i).po_distribution_id
1249 AND pod.amount_financed IS NULL;
1250
1251 l_total_shipment_amt_invoiced := nvl(l_total_shipment_amt_invoiced,0) +
1252 nvl(l_dist_tab(i).total_dist_amt_invoiced,0);
1253 l_total_shipment_amt_applied := nvl(l_total_shipment_amt_applied,0) +
1254 nvl(l_dist_tab(i).total_dist_amt_applied,0);
1255
1256 END IF;
1257
1258 END IF;
1259
1260 END LOOP;
1261
1262 l_debug_info := 'Update Po_Shipments with the cumulative totals';
1263 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1264 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1265 END IF;
1266
1267 IF (l_matching_basis = 'QUANTITY') THEN
1268
1269 UPDATE po_line_locations
1270 SET quantity_financed = l_total_shipment_qty_invoiced,
1271 quantity_recouped = l_total_shipment_qty_applied,
1272 quantity_billed = nvl(quantity_billed,0) - (l_total_shipment_qty_invoiced - l_total_shipment_qty_applied)
1273 WHERE line_location_id = p_po_line_location_id
1274 AND quantity_financed IS NULL;
1275
1276 ELSIF (l_matching_basis = 'AMOUNT') THEN
1277
1278 UPDATE po_line_locations
1279 SET amount_financed = l_total_shipment_amt_invoiced,
1280 amount_recouped = l_total_shipment_amt_applied,
1281 amount_billed = nvl(amount_billed,0) - (l_total_shipment_amt_invoiced - l_total_shipment_amt_applied)
1282 WHERE line_location_id = p_po_line_location_id
1283 AND amount_financed IS NULL;
1284
1285 END IF;
1286
1287 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1288 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_MATCHING_UTILS_PKG.Ap_Upgrade_Po_Shipment(-)');
1289 END IF;
1290
1291 EXCEPTION WHEN OTHERS THEN
1292 NULL;
1293
1294 END AP_Upgrade_Po_Shipment;
1295
1296
1297
1298
1299 /*API to Automatically recoup Prepayment invoice lines which are matched
1300 to the same PO Line as the Item Line on the Standard invoice. */
1301 Function Ap_Recoup_Invoice_Line(P_Invoice_Id IN NUMBER,
1302 P_Invoice_Line_Number IN NUMBER,
1303 P_Amount_To_Recoup IN NUMBER,
1304 P_Po_Line_Id IN NUMBER,
1305 P_Vendor_Id IN NUMBER,
1306 P_Vendor_Site_Id IN NUMBER,
1307 P_Accounting_Date IN DATE,
1308 P_Period_Name IN VARCHAR2,
1309 P_User_Id IN NUMBER,
1310 P_Last_Update_Login IN NUMBER,
1311 P_Error_Message OUT NOCOPY VARCHAR2,
1312 P_Calling_Sequence IN VARCHAR2) RETURN BOOLEAN IS
1313
1314 CURSOR Prepayment_Invoice_Lines IS
1315 /* select matched prepayments */
1316 SELECT ai.invoice_id prepayment_invoice_id,
1317 ai.invoice_num prepayment_invoice_num,
1318 ail.line_number prepayment_line_number,
1319 decode(pll.payment_type,'ADVANCE',2,1) prepayment_order_number,
1320 AP_Prepay_Utils_Pkg.Get_Ln_Prep_Amt_Remain_Recoup(
1321 ai.invoice_id,ail.line_number) prepay_amount_remaining,
1322 max(aip.accounting_date) prepayment_payment_date
1323 FROM ap_invoices ai,
1324 ap_invoice_lines ail,
1325 po_line_locations pll,
1326 ap_invoice_payments aip
1327 WHERE ai.invoice_id = ail.invoice_id
1328 AND ail.po_line_id = p_po_line_id
1329 AND ai.invoice_type_lookup_code = 'PREPAYMENT'
1330 AND ail.line_type_lookup_code = 'ITEM'
1331 AND pll.po_line_id = p_po_line_id
1332 AND pll.payment_type IS NOT NULL
1333 AND AP_PREPAY_UTILS_PKG.Get_Ln_Prep_Amt_Remain_Recoup(ai.invoice_id,ail.line_number) > 0
1334 AND NVL(aip.reversal_flag,'N') <> 'Y' -- Added for bug 8340944
1335 AND aip.invoice_id = ai.invoice_id
1336 AND pll.line_location_id = ail.po_line_location_id
1337 --bugfix:4880825 removed '+1' from the NVL condition
1338 AND nvl(ai.earliest_settlement_date,SYSDATE) <= SYSDATE
1339 AND NVL(ail.discarded_flag,'N') <> 'Y'
1340 --Do we need to check this, since by the time cursor is fetched and the one-by-one
1341 --prepayment is applied, it could be the case that the prepayment_invoice which was
1342 --locked when selecting, could be actually unlocked by the time actual application happens.
1343 --So, just checking if the line if locked or not just before application should be sufficient?
1344 -- AND NVL(ail.line_selected_for_appl_flag,'N') <> 'Y'
1345 GROUP BY aip.invoice_id, ai.invoice_id,ai.invoice_num,
1346 ail.line_number,pll.payment_type,aip.accounting_date
1347 ORDER BY prepayment_payment_date,prepayment_order_number;
1348
1349
1350 CURSOR C_INVOICE_INFO (CV_Invoice_ID IN NUMBER) IS
1351 SELECT invoice_currency_code,
1352 exchange_rate,
1353 exchange_date,
1354 exchange_rate_type,
1355 payment_currency_code,
1356 payment_cross_rate_date,
1357 payment_cross_rate_type
1358 FROM AP_Invoices
1359 WHERE invoice_id = CV_Invoice_ID;
1360
1361 CURSOR C_Prepay_dists IS
1362 SELECT prepay_distribution_id,
1363 amount
1364 FROM ap_invoice_distributions
1365 WHERE invoice_id = p_invoice_id
1366 AND invoice_line_number = p_invoice_line_number
1367 AND line_type_lookup_code IN ('REC_TAX','NONREC_TAX')
1368 AND prepay_distribution_id IS NOT NULL;
1369
1370
1371
1372
1373
1374 l_max_amount_to_recoup NUMBER;
1375 l_amount_recouped NUMBER;
1376 l_amount_to_apply NUMBER;
1377 l_is_line_locked VARCHAR2(30);
1378 l_prepayment_invoice_id NUMBER;
1379 l_prepayment_invoice_num VARCHAR2(50);
1380 l_prepayment_line_number NUMBER;
1381 l_prepayment_amount_remaining NUMBER;
1382 l_prepayment_order_number NUMBER;
1383 l_prepayment_payment_date DATE;
1384 l_prepay_dist_info AP_PREPAY_PKG.PREPAY_DIST_TAB_TYPE;
1385 l_lock_result BOOLEAN;
1386 l_debug_info VARCHAR2(2000);
1387 l_curr_calling_sequence VARCHAR2(2000);
1388 l_api_name VARCHAR2(50);
1389 l_error_message VARCHAR2(4000);
1390 l_success BOOLEAN;
1391
1392 --bugfix:5496603
1393 l_recouped_tax_amt_in_pay_curr NUMBER;
1394 l_recouped_tax_amount NUMBER;
1395 l_inv_curr_code ap_invoices_all.invoice_currency_code%TYPE;
1396 l_inv_xrate ap_invoices_all.exchange_rate%TYPE;
1397 l_inv_xdate ap_invoices_all.exchange_date%TYPE;
1398 l_inv_xrate_type ap_invoices_all.exchange_rate_type%TYPE;
1399 l_inv_pay_curr_code ap_invoices_all.payment_currency_code%TYPE;
1400 l_inv_pay_cross_rate_date ap_invoices_all.payment_cross_rate_date%TYPE;
1401 l_inv_pay_cross_rate_type ap_invoices_all.payment_cross_rate_type%TYPE;
1402 TYPE PREPAY_DIST_ID_LIST IS TABLE OF AP_INVOICE_DISTRIBUTIONS_ALL.PREPAY_DISTRIBUTION_ID%TYPE INDEX BY PLS_INTEGER;
1403 TYPE RECOUP_AMOUNT_LIST IS TABLE OF AP_INVOICE_DISTRIBUTIONS_ALL.AMOUNT%TYPE INDEX BY PLS_INTEGER;
1404 l_prepay_dist_id_list prepay_dist_id_list;
1405 l_recoup_amount_list recoup_amount_list;
1406
1407 tax_exception EXCEPTION;
1408
1409 BEGIN
1410 l_max_amount_to_recoup := p_amount_to_recoup;
1411 l_amount_recouped := 0;
1412 l_is_line_locked := 'UNLOCKED';
1413
1414 l_api_name := 'AP_Recoup_Invoice_Line';
1415 l_curr_calling_sequence := 'AP_MATCHING_UTILS_PKG.AP_Recoup_Invoice_Line <-'||p_calling_sequence;
1416
1417 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1418 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_MATCHING_UTILS_PKG.Ap_Recoup_Invoice_Line(+)');
1419 END IF;
1420
1421
1422 OPEN C_INVOICE_INFO (P_INVOICE_ID);
1423
1424 FETCH C_INVOICE_INFO INTO
1425 l_inv_curr_code,
1426 l_inv_xrate,
1427 l_inv_xdate,
1428 l_inv_xrate_type,
1429 l_inv_pay_curr_code,
1430 l_inv_pay_cross_rate_date,
1431 l_inv_pay_cross_rate_type;
1432
1433 CLOSE C_INVOICE_INFO;
1434
1435
1436 l_debug_info := 'Open cursor Prepayment_Invoice_Lines';
1437 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1438 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1439 END IF;
1440
1441
1442 OPEN Prepayment_Invoice_Lines;
1443 LOOP
1444
1445 l_debug_info := 'Fetch into local variables';
1446 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1447 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1448 END IF;
1449
1450 FETCH Prepayment_Invoice_Lines INTO l_prepayment_invoice_id,
1451 l_prepayment_invoice_num,
1452 l_prepayment_line_number,
1453 l_prepayment_order_number,
1454 l_prepayment_amount_remaining,
1455 l_prepayment_payment_date;
1456
1457 EXIT WHEN (Prepayment_Invoice_Lines%NOTFOUND OR l_max_amount_to_recoup = 0);
1458
1459 IF (l_prepayment_amount_remaining >= l_max_amount_to_recoup) THEN
1460 l_amount_to_apply := l_max_amount_to_recoup;
1461 ELSE
1462 l_amount_to_apply := l_prepayment_amount_remaining;
1463 END IF;
1464
1465 l_max_amount_to_recoup := l_max_amount_to_recoup - l_amount_to_apply;
1466 l_amount_recouped := l_amount_recouped + l_amount_to_apply;
1467
1468
1469 l_debug_info := 'Check if the Prepayment Invoice - Item line is already locked';
1470 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1471 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1472 END IF;
1473
1474 l_is_line_locked := AP_PREPAY_UTILS_PKG.Is_Line_Locked (
1475 l_prepayment_invoice_id,
1476 l_prepayment_line_number,
1477 NULL);
1478
1479 IF l_is_line_locked = 'UNLOCKED' THEN
1480
1481 l_debug_info := 'Lock the Prepayment Invoice - Item line for this recoupment';
1482 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1483 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1484 END IF;
1485 l_lock_result := AP_PREPAY_UTILS_PKG.Lock_Line(
1486 l_prepayment_invoice_id,
1487 l_prepayment_line_number,
1488 NULL);
1489
1490 END IF;
1491
1492
1493 IF(l_lock_result) THEN
1494
1495 l_debug_info := 'Call AP_Prepay_Pkg.Apply_Prepay_Line';
1496 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1497 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1498 END IF;
1499 l_success :=
1500 AP_Prepay_Pkg.Apply_Prepay_Line(
1501 P_PREPAY_INVOICE_ID => l_prepayment_invoice_id,
1502 P_PREPAY_LINE_NUM => l_prepayment_line_number,
1503 P_PREPAY_DIST_INFO => l_prepay_dist_info,
1504 P_PRORATE_FLAG => 'Y',
1505 P_INVOICE_ID => p_invoice_id,
1506 P_INVOICE_LINE_NUMBER => p_invoice_line_number,
1507 P_APPLY_AMOUNT => l_amount_to_apply,
1508 P_GL_DATE => p_accounting_date,
1509 P_PERIOD_NAME => p_period_name,
1510 P_PREPAY_INCLUDED => 'N',
1511 P_USER_ID => p_user_id,
1512 P_LAST_UPDATE_LOGIN => p_last_update_login,
1513 P_CALLING_SEQUENCE => l_curr_calling_sequence,
1514 P_CALLING_MODE => 'RECOUPMENT',
1515 P_ERROR_MESSAGE => l_error_message);
1516
1517 IF NOT(l_success) THEN
1518 p_error_message := l_error_message;
1519 RETURN(l_success);
1520 END IF;
1521 END IF;
1522
1523 END LOOP;
1524
1525 CLOSE Prepayment_Invoice_Lines;
1526
1527 l_debug_info := 'Call to calculate tax';
1528 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1529 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1530 END IF;
1531
1532 IF NOT (ap_etax_pkg.calling_etax(
1533 p_invoice_id => p_invoice_id
1534 ,p_line_number => p_invoice_line_number
1535 ,p_calling_mode => 'RECOUPMENT'
1536 ,p_override_status => NULL
1537 ,p_line_number_to_delete => NULL
1538 ,p_Interface_Invoice_Id => NULL
1539 ,p_all_error_messages => 'N'
1540 ,p_error_code => p_error_message
1541 ,p_calling_sequence => l_curr_calling_sequence)) THEN
1542
1543 RAISE tax_exception;
1544
1545 END IF;
1546
1547
1548 --Update the prepayment invoice's tax distributions for prepay_amount_remaining
1549 --after recouped tax distributions have been created.
1550 --Bugfix:5609186 Starts here
1551 OPEN c_prepay_dists;
1552
1553 FETCH c_prepay_dists BULK COLLECT INTO l_prepay_dist_id_list,
1554 l_recoup_amount_list;
1555
1556 CLOSE c_prepay_dists;
1557
1558 FORALL i IN l_prepay_dist_id_list.first .. l_prepay_dist_id_list.last
1559 UPDATE ap_invoice_distributions
1560 SET prepay_amount_remaining = prepay_amount_remaining + l_recoup_amount_list(i)
1561 WHERE invoice_distribution_id = l_prepay_dist_id_list(i);
1562
1563 l_debug_info := 'Update payment schedules with the tax on recouped distributions';
1564 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1565 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1566 END IF;
1567
1568 SELECT sum(aid.amount)
1569 INTO l_recouped_tax_amount
1570 FROM ap_invoice_distributions aid,
1571 ap_invoice_distributions aid1,
1572 ap_invoice_lines ail
1573 WHERE aid.invoice_id = p_invoice_id
1574 AND aid.invoice_line_number = p_invoice_line_number
1575 AND ail.invoice_id = aid.invoice_id
1576 AND ail.line_number = aid.invoice_line_number
1577 AND aid.line_type_lookup_code in ('REC_TAX','NONREC_TAX','TIPV','TRV','TERV')
1578 AND aid.charge_applicable_to_dist_id = aid1.invoice_distribution_id
1579 AND aid1.invoice_id = aid.invoice_id
1580 AND aid1.invoice_line_number = aid.invoice_line_number
1581 AND aid1.line_type_lookup_code = 'PREPAY';
1582
1583 l_debug_info := 'Get Apply Amount in Payment Currency l_recouped_tax_amount is '||l_recouped_tax_amount;
1584 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1585 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1586 END IF;
1587
1588 IF (l_recouped_tax_amount IS NOT NULL ) THEN
1589 IF (l_inv_curr_code <> l_inv_pay_curr_code) THEN
1590
1591 l_recouped_tax_amt_in_pay_curr :=
1592 GL_Currency_API.Convert_Amount (
1593 l_inv_curr_code,
1594 l_inv_pay_curr_code,
1595 l_inv_pay_cross_rate_date,
1596 l_inv_pay_cross_rate_type,
1597 l_recouped_tax_amount);
1598
1599
1600 ELSE
1601 l_recouped_tax_amt_in_pay_curr := l_recouped_tax_amount;
1602 END IF;
1603
1604
1605 l_debug_info := 'Update Payment Schedules l_recouped_tax_amt_in_pay_curr is '||l_recouped_tax_amt_in_pay_curr;
1606 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1607 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1608 END IF;
1609
1610 l_success := AP_PREPAY_PKG.Update_Payment_Schedule(
1611 p_invoice_id,
1612 l_prepayment_invoice_id,
1613 l_prepayment_line_number,
1614 (-1)*l_recouped_tax_amt_in_pay_curr,
1615 'APPLICATION',
1616 l_inv_pay_curr_code,
1617 p_user_id,
1618 p_last_update_login,
1619 l_curr_calling_sequence,
1620 'RECOUPMENT',
1621 l_error_message);
1622
1623 END IF;
1624
1625 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1626 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_MATCHING_UTILS_PKG.Ap_Recoup_Invoice_Line(-)');
1627 END IF;
1628
1629 RETURN(TRUE);
1630
1631 EXCEPTION WHEN OTHERS THEN
1632 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1633 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1634 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',
1635 l_curr_calling_sequence);
1636 FND_MESSAGE.SET_TOKEN('PARAMETERS',
1637 'P_INVOICE_ID = '||P_INVOICE_ID
1638 ||', P_INVOICE_LINE_NUMBER = '||P_INVOICE_LINE_NUMBER
1639 ||', P_AMOUNT_TO_RECOUP = '||P_AMOUNT_TO_RECOUP
1640 ||', P_USER_ID = '||P_USER_ID
1641 ||', P_LAST_UPDATE_LOGIN = '||P_LAST_UPDATE_LOGIN);
1642
1643 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
1644
1645 APP_EXCEPTION.RAISE_EXCEPTION;
1646 END Ap_Recoup_Invoice_Line;
1647
1648
1649 FUNCTION Get_Inv_Line_Recouped_Amount(P_Invoice_Id IN NUMBER,
1650 P_Invoice_Line_Number IN NUMBER) RETURN NUMBER IS
1651 l_recouped_amount NUMBER;
1652 BEGIN
1653
1654 l_recouped_amount := 0;
1655 --Bug 6841613 : For performance reasons, Split the update into 2 different stmts
1656 --based on the value of parameter p_invoice_line_number.
1657 IF (p_invoice_line_number IS NOT NULL) THEN
1658
1659 SELECT sum(aid.amount)
1660 INTO l_recouped_amount
1661 FROM ap_invoice_distributions aid,
1662 ap_invoice_lines ail
1663 WHERE aid.invoice_id = p_invoice_id
1664 AND aid.invoice_line_number = p_invoice_line_number
1665 AND ail.invoice_id = aid.invoice_id
1666 AND ail.line_number = aid.invoice_line_number
1667 AND ail.line_type_lookup_code IN ('ITEM', 'RETAINAGE RELEASE')
1668 AND aid.parent_reversal_id is null -- Added for bug #8928639
1669 AND (aid.line_type_lookup_code = 'PREPAY'
1670 OR (aid.line_type_lookup_code IN ('REC_TAX','NONREC_TAX') and
1671 aid.prepay_distribution_id IS NOT NULL)
1672 OR (aid.line_type_lookup_code IN ('TIPV','TRV','TERV')
1673 and aid.related_id IN (SELECT invoice_distribution_id
1674 FROM ap_invoice_distributions aid1
1675 WHERE aid1.invoice_id = aid.invoice_id
1676 AND aid1.invoice_line_number = aid.invoice_line_number
1677 AND aid1.line_type_lookup_code IN ('REC_TAX','NONREC_TAX')
1678 AND aid1.prepay_distribution_id IS NOT NULL)
1679 )
1680 );
1681 ELSE
1682
1683 SELECT sum(aid.amount)
1684 INTO l_recouped_amount
1685 FROM ap_invoice_distributions aid,
1686 ap_invoice_lines ail
1687 WHERE aid.invoice_id = p_invoice_id
1688 AND ail.invoice_id = aid.invoice_id
1689 AND ail.line_number = aid.invoice_line_number
1690 AND ail.line_type_lookup_code IN ('ITEM', 'RETAINAGE RELEASE')
1691 AND aid.parent_reversal_id is null -- Added for bug #8928639
1692 AND (aid.line_type_lookup_code = 'PREPAY'
1693 OR (aid.line_type_lookup_code IN ('REC_TAX','NONREC_TAX') and
1694 aid.prepay_distribution_id IS NOT NULL)
1695 OR (aid.line_type_lookup_code IN ('TIPV','TRV','TERV')
1696 and aid.related_id IN (SELECT invoice_distribution_id
1697 FROM ap_invoice_distributions aid1
1698 WHERE aid1.invoice_id = aid.invoice_id
1699 AND aid1.invoice_line_number = aid.invoice_line_number
1700 AND aid1.line_type_lookup_code IN ('REC_TAX','NONREC_TAX')
1701 AND aid1.prepay_distribution_id IS NOT NULL)
1702 )
1703 );
1704
1705 END IF;
1706 RETURN(NVL(l_recouped_amount, 0));
1707
1708 EXCEPTION WHEN OTHERS THEN
1709 RETURN(l_recouped_amount);
1710 END Get_Inv_Line_Recouped_Amount;
1711
1712
1713 FUNCTION Get_Recoup_Amt_Per_Prepay_Line(P_Invoice_Id IN NUMBER,
1714 P_Invoice_Line_Number IN NUMBER,
1715 P_Prepay_Invoice_Id IN NUMBER,
1716 P_Prepay_Line_Number IN NUMBER) RETURN NUMBER IS
1717 l_recouped_amount NUMBER;
1718 BEGIN
1719
1720 l_recouped_amount := 0;
1721
1722 SELECT sum(aid.amount)
1723 INTO l_recouped_amount
1724 FROM ap_invoice_distributions aid
1725 WHERE aid.invoice_id = p_invoice_id
1726 AND aid.invoice_line_number = p_invoice_line_number
1727 AND aid.line_type_lookup_code = 'PREPAY'
1728 AND aid.prepay_distribution_id IN (SELECT aid1.invoice_distribution_id
1729 FROM ap_invoice_distributions aid1
1730 WHERE aid1.invoice_id = p_prepay_invoice_id
1731 AND aid1.invoice_line_number = p_prepay_line_number);
1732
1733 RETURN(l_recouped_amount);
1734
1735 EXCEPTION WHEN OTHERS THEN
1736 return (l_recouped_amount);
1737 END Get_Recoup_Amt_Per_Prepay_Line;
1738
1739
1740
1741 FUNCTION Get_Recoup_Tax_Amt_Per_Ppay_Ln(P_Invoice_Id IN NUMBER,
1742 P_Invoice_Line_Number IN NUMBER,
1743 P_Prepay_Invoice_Id IN NUMBER,
1744 P_Prepay_Line_Number IN NUMBER) RETURN NUMBER IS
1745 l_recouped_tax_amount NUMBER;
1746 BEGIN
1747
1748 l_recouped_tax_amount := 0;
1749
1750 SELECT sum(aid.amount)
1751 INTO l_recouped_tax_amount
1752 FROM ap_invoice_distributions aid
1753 WHERE aid.invoice_id = p_invoice_id
1754 AND aid.invoice_line_number = p_invoice_line_number
1755 AND
1756 ((aid.line_type_lookup_code IN ('REC_TAX','NONREC_TAX')
1757 and aid.prepay_distribution_id IN (SELECT aid1.invoice_distribution_id
1758 FROM ap_invoice_distributions aid1
1759 WHERE aid1.invoice_id = p_prepay_invoice_id
1760 AND aid1.invoice_line_number = p_prepay_line_number)
1761 ) OR
1762 (aid.line_type_lookup_code IN ('TIPV','TRV','TERV')
1763 and aid.related_id IN (SELECT invoice_distribution_id
1764 FROM ap_invoice_distributions aid2
1765 WHERE aid2.invoice_id = aid.invoice_id
1766 AND aid2.invoice_line_number = aid.invoice_line_number
1767 AND aid2.line_type_lookup_code IN ('REC_TAX','NONREC_TAX')
1768 AND aid2.prepay_distribution_id IN
1769 (SELECT aid4.invoice_distribution_id
1770 FROM ap_invoice_distributions aid4
1771 WHERE aid4.invoice_id = p_prepay_invoice_id
1772 AND aid4.invoice_line_number = p_prepay_line_number)
1773 )
1774 )
1775 );
1776
1777 RETURN(l_recouped_tax_amount);
1778
1779 EXCEPTION WHEN OTHERS THEN
1780 RETURN(l_recouped_tax_amount);
1781
1782 END Get_Recoup_Tax_Amt_Per_Ppay_Ln;
1783
1784
1785 Procedure Match_To_Rcv_Shipment_Line(P_Invoice_Id IN NUMBER,
1786 P_Invoice_Line_Number IN NUMBER,
1787 P_Calling_Sequence IN VARCHAR2) IS
1788
1789 CURSOR C_Rcv_Transactions (p_rcv_shipment_line_id IN NUMBER) IS
1790 SELECT rcv.transaction_id,
1791 pll.matching_basis,
1792 pll.line_location_id
1793 FROM rcv_transactions rcv,
1794 rcv_shipment_lines rsl,
1795 po_line_locations pll
1796 WHERE rcv.shipment_line_id = rsl.shipment_line_id
1797 AND rsl.shipment_line_id = p_rcv_shipment_line_id
1798 AND pll.line_location_id = rcv.po_line_location_id
1799 AND rcv.transaction_type IN ('RECEIVE','MATCH');
1800
1801 CURSOR C_Deliver_Transactions(p_rcv_transaction_id IN NUMBER) IS
1802 SELECT po_distribution_id
1803 FROM
1804 rcv_transactions
1805 WHERE
1806 transaction_type = 'DELIVER'
1807 START WITH transaction_id = p_rcv_transaction_id
1808 CONNECT BY parent_transaction_id = PRIOR transaction_id
1809 AND PRIOR transaction_type <> 'DELIVER';
1810
1811 l_dist_tab AP_MATCHING_PKG.DIST_TAB_TYPE;
1812
1813 l_rcv_shipment_line_id RCV_SHIPMENT_LINES.SHIPMENT_LINE_ID%TYPE;
1814 l_po_line_location_id PO_LINE_LOCATIONS.LINE_LOCATION_ID%TYPE;
1815 l_total_match_quantity NUMBER;
1816 l_total_match_amount NUMBER;
1817
1818 l_match_unit_price NUMBER;
1819 l_match_quantity NUMBER;
1820 l_match_amount NUMBER;
1821 l_rcv_transaction_id RCV_TRANSACTIONS.TRANSACTION_ID%TYPE;
1822 l_matching_basis PO_LINE_LOCATIONS.MATCHING_BASIS%TYPE;
1823 l_invoice_currency_code AP_INVOICES.INVOICE_CURRENCY_CODE%TYPE;
1824 l_invoice_type_lookup_code AP_INVOICES.INVOICE_TYPE_LOOKUP_CODE%TYPE;
1825 l_unit_meas_lookup_code AP_INVOICE_LINES.UNIT_MEAS_LOOKUP_CODE%TYPE;
1826 l_retained_amount AP_INVOICE_LINES.RETAINED_AMOUNT%TYPE;
1827 l_match_type AP_INVOICE_LINES.MATCH_TYPE%TYPE;
1828 l_po_distribution_id PO_DISTRIBUTIONS.PO_DISTRIBUTION_ID%TYPE;
1829 l_match_mode VARCHAR2(30);
1830
1831 l_ordered_po_qty NUMBER;
1832 l_cancelled_po_qty NUMBER;
1833 l_delivered_po_qty NUMBER;
1834 l_returned_po_qty NUMBER;
1835 l_corrected_po_qty NUMBER;
1836 l_ordered_txn_qty NUMBER;
1837 l_cancelled_txn_qty NUMBER;
1838 l_delivered_txn_qty NUMBER;
1839 l_returned_txn_qty NUMBER;
1840 l_corrected_txn_qty NUMBER;
1841 l_billed_txn_qty NUMBER;
1842
1843 l_ordered_qty NUMBER;
1844 l_cancelled_qty NUMBER;
1845 l_received_qty NUMBER;
1846 l_corrected_qty NUMBER;
1847 l_delivered_qty NUMBER;
1848 l_transaction_qty NUMBER;
1849 l_billed_qty NUMBER;
1850 l_accepted_qty NUMBER;
1851 l_rejected_qty NUMBER;
1852
1853 l_amount_delivered NUMBER;
1854 l_amount_corrected NUMBER;
1855 l_amount_ordered NUMBER;
1856 l_amount_cancelled NUMBER;
1857 l_amount_billed NUMBER;
1858 l_amount_received NUMBER;
1859 l_ret_status VARCHAR2(100);
1860 l_msg_count NUMBER;
1861 l_msg_data VARCHAR2(250);
1862
1863 l_debug_info VARCHAR2(1000);
1864 l_current_calling_sequence VARCHAR2(2000);
1865
1866 BEGIN
1867
1868 l_current_calling_sequence := 'AP_Matching_Utils_Pkg.Match_To_Rcv_Shipment_Line <-' ||p_calling_sequence;
1869
1870 l_debug_info := 'Get Invoice and Invoice Line info';
1871
1872 SELECT ail.rcv_shipment_line_id,
1873 ail.quantity_invoiced,
1874 ail.amount,
1875 ail.unit_price,
1876 ai.invoice_currency_code,
1877 ai.invoice_type_lookup_code,
1878 ail.unit_meas_lookup_code,
1879 ail.retained_amount,
1880 ail.match_type,
1881 ail.po_distribution_id
1882 INTO l_rcv_shipment_line_id,
1883 l_total_match_quantity,
1884 l_total_match_amount,
1885 l_match_unit_price,
1886 l_invoice_currency_code,
1887 l_invoice_type_lookup_code,
1888 l_unit_meas_lookup_code,
1889 l_retained_amount,
1890 l_match_type,
1891 l_po_distribution_id
1892 FROM ap_invoice_lines_all ail,
1893 ap_invoices ai
1894 WHERE ai.invoice_id = p_invoice_id
1895 AND ail.invoice_id = ai.invoice_id
1896 AND ail.line_number = p_invoice_line_number;
1897
1898 l_debug_info := 'Derive Match_Mode';
1899 IF (l_match_type IN ('ITEM_TO_RECEIPT','ITEM_TO_SERVICE_RECEIPT')) THEN
1900
1901 IF (SIGN(l_total_match_amount) < 0) THEN
1902 l_match_mode := 'CR-';
1903 ELSE
1904 l_match_mode := 'STD-';
1905 END IF;
1906
1907 IF (l_po_distribution_id IS NULL) THEN
1908 l_match_mode := l_match_mode||'PS';
1909 ELSE
1910 l_match_mode := l_match_mode||'PD';
1911 END IF;
1912
1913 END IF;
1914
1915
1916 IF (l_match_mode IN ('STD-PS','STD-PD')) THEN
1917 l_debug_info := 'Open C_Rcv_Transactions cursor';
1918 OPEN c_rcv_transactions(l_rcv_shipment_line_id);
1919
1920 LOOP
1921
1922 FETCH C_Rcv_Transactions INTO l_rcv_transaction_id,
1923 l_matching_basis,
1924 l_po_line_location_id;
1925 EXIT WHEN C_Rcv_Transactions%NOTFOUND OR l_total_match_amount = 0
1926 OR l_total_match_quantity = 0;
1927
1928
1929 OPEN C_Deliver_Transactions(l_rcv_transaction_id);
1930
1931 LOOP
1932
1933 FETCH C_Deliver_Transactions INTO l_po_distribution_id;
1934
1935 EXIT WHEN C_Deliver_Transactions%NOTFOUND OR l_total_match_quantity <= 0 OR l_total_match_amount <= 0;
1936
1937 IF (l_matching_basis = 'QUANTITY') THEN
1938
1939 RCV_INVOICE_MATCHING_SV.Get_Delivered_Quantity(
1940 rcv_transaction_id => l_rcv_transaction_id,
1941 p_distribution_id => l_po_distribution_id,
1942 ordered_po_qty => l_ordered_po_qty,
1943 cancelled_po_qty => l_cancelled_po_qty,
1944 delivered_po_qty => l_delivered_po_qty,
1945 returned_po_qty => l_returned_po_qty,
1946 corrected_po_qty => l_corrected_po_qty,
1947 ordered_txn_qty => l_ordered_txn_qty,
1948 cancelled_txn_qty => l_cancelled_txn_qty,
1949 delivered_txn_qty => l_delivered_txn_qty,
1950 returned_txn_qty => l_returned_txn_qty,
1951 corrected_txn_qty => l_corrected_txn_qty);
1952
1953 AP_MATCHING_UTILS_PKG.Get_Recpt_Dist_Qty_Billed (
1954 l_rcv_transaction_id,
1955 l_po_distribution_id,
1956 l_billed_txn_qty);
1957
1958 l_billed_txn_qty := nvl(l_billed_txn_qty,0);
1959
1960 l_delivered_txn_qty := nvl(l_delivered_txn_qty,0)
1961 + nvl(l_corrected_txn_qty,0)
1962 - nvl(l_returned_txn_qty,0);
1963
1964 l_ordered_txn_qty := nvl(l_ordered_txn_qty,0)
1965 - nvl(l_cancelled_txn_qty,0);
1966
1967 IF (l_total_match_quantity >= (l_delivered_txn_qty - l_billed_txn_qty)) THEN
1968
1969 l_match_quantity := l_delivered_txn_qty - l_billed_txn_qty;
1970
1971 ELSE
1972
1973 l_match_quantity := l_total_match_quantity;
1974
1975 END IF;
1976
1977 l_match_amount := ap_utilities_pkg.ap_round_currency(l_match_quantity*l_match_unit_price,
1978 l_invoice_currency_code);
1979
1980 l_total_match_quantity := l_total_match_quantity - l_match_quantity;
1981 l_total_match_amount := l_total_match_amount - l_match_amount;
1982
1983 l_debug_info := 'Call Receipt Matching Api';
1984 AP_RECT_MATCH_PKG.Base_Credit_Rcv_Match(X_Match_Mode => l_match_mode,
1985 X_Invoice_Id => p_invoice_id,
1986 X_Invoice_Line_Number => p_invoice_line_number,
1987 X_Po_Line_Location_Id => l_po_line_location_id,
1988 X_Rcv_Transaction_Id => l_rcv_transaction_id,
1989 X_Dist_Tab => l_dist_tab,
1990 X_Amount => l_match_amount,
1991 X_Quantity => l_match_quantity,
1992 X_Unit_Price => l_match_unit_price,
1993 X_Uom_Lookup_Code => l_unit_meas_lookup_code,
1994 X_freight_cost_factor_id => NULL,
1995 X_freight_amount => NULL,
1996 X_freight_description => NULL,
1997 X_misc_cost_factor_id => NULL,
1998 X_misc_amount => NULL,
1999 X_misc_description => NULL,
2000 X_retained_amount => l_retained_amount,
2001 X_Calling_Sequence => l_current_calling_sequence);
2002
2003
2004 ELSIF l_matching_basis = 'AMOUNT' THEN
2005
2006 RCV_INVOICE_MATCHING_SV.get_DeliverAmount(
2007 p_api_version => 1.0,
2008 p_init_msg_list => FND_API.G_TRUE,
2009 x_return_status => l_ret_status,
2010 x_msg_count => l_msg_count,
2011 x_msg_data => l_msg_data,
2012 p_receive_transaction_id => l_rcv_transaction_id,
2013 p_po_distribution_id => l_po_distribution_id,
2014 x_delivered_amt => l_amount_delivered,
2015 x_corrected_amt => l_amount_corrected);
2016
2017 AP_MATCHING_UTILS_PKG.Get_Recpt_Dist_Amt_Billed (
2018 l_rcv_transaction_id,
2019 l_po_distribution_id,
2020 l_amount_billed);
2021
2022 l_amount_billed := nvl(l_amount_billed,0);
2023
2024 l_amount_delivered := nvl(l_amount_delivered,0) + nvl(l_amount_corrected,0);
2025
2026 IF (l_total_match_amount >= (l_amount_delivered - l_amount_billed)) THEN
2027
2028 l_match_amount := l_amount_delivered - l_amount_billed;
2029 l_match_quantity := ROUND(((l_amount_delivered - l_amount_billed) / l_match_unit_price),15);
2030
2031 ELSE
2032
2033 l_match_amount := l_total_match_amount;
2034 l_match_quantity := ROUND((l_total_match_amount/l_match_unit_price),15);
2035
2036 END IF;
2037
2038 l_total_match_quantity := l_total_match_quantity - l_match_quantity;
2039 l_total_match_amount := l_total_match_amount - l_match_amount;
2040
2041 l_debug_info := 'Call Receipt Matching api for service orders';
2042
2043 AP_RCT_AMT_MATCH_PKG.AP_AMT_MATCH(
2044 X_match_mode => l_match_mode,
2045 X_invoice_id => p_invoice_id,
2046 X_invoice_line_number => p_invoice_line_number,
2047 X_dist_tab => l_dist_tab,
2048 X_po_line_location_id => l_po_line_location_id,
2049 X_rcv_transaction_id => l_rcv_transaction_id,
2050 X_amount => l_match_amount,
2051 X_quantity => l_match_quantity,
2052 X_unit_price => l_match_unit_price,
2053 X_uom_lookup_code => l_unit_meas_lookup_code,
2054 X_freight_cost_factor_id => NULL,
2055 X_freight_amount => NULL,
2056 X_freight_description => NULL,
2057 X_misc_cost_factor_id => NULL,
2058 X_misc_amount => NULL,
2059 X_misc_description => NULL,
2060 X_retained_amount => l_retained_amount,
2061 X_calling_sequence => l_current_calling_sequence);
2062
2063 END IF;
2064
2065 END LOOP;
2066
2067 CLOSE C_Deliver_Transactions;
2068
2069 END LOOP;
2070
2071 CLOSE C_Rcv_Transactions;
2072
2073 END IF; /* l_match_mode IN ...*/
2074
2075 --If match_quantity or amount is still not used up by the
2076 --deliver transactions above, then we prorate the remaining
2077 --quantity/amount across all the rcv_transactions based on O-B ??
2078
2079 IF ((l_match_mode IN ('CR-PS','CR-PD')) OR
2080 (l_matching_basis = 'QUANTITY' and l_total_match_quantity > 0 ) OR
2081 (l_matching_basis = 'AMOUNT' and l_total_match_amount > 0)) THEN
2082
2083 OPEN C_Rcv_Transactions(l_rcv_shipment_line_id);
2084
2085 LOOP
2086
2087 FETCH C_Rcv_Transactions INTO l_rcv_transaction_id,
2088 l_matching_basis,
2089 l_po_line_location_id;
2090
2091 EXIT WHEN (C_Rcv_Transactions%NOTFOUND or
2092 l_total_match_quantity = 0 OR l_total_match_amount = 0);
2093
2094 IF (l_matching_basis = 'QUANTITY') THEN
2095
2096 AP_MATCHING_UTILS_PKG.Get_receipt_Quantities(
2097 l_rcv_transaction_id,
2098 l_ordered_qty,
2099 l_cancelled_qty,
2100 l_received_qty,
2101 l_corrected_qty,
2102 l_delivered_qty,
2103 l_transaction_qty,
2104 l_billed_qty,
2105 l_accepted_qty,
2106 l_rejected_qty);
2107
2108 l_billed_qty := nvl(l_billed_qty,0);
2109 l_delivered_qty := nvl(l_delivered_qty,0);
2110 l_cancelled_qty := nvl(l_cancelled_qty,0);
2111 l_ordered_qty := nvl(l_ordered_qty,0);
2112
2113 IF (l_match_mode IN ('STD-PS','STD-PD')) THEN
2114
2115 IF ((l_ordered_qty - l_cancelled_qty - l_billed_qty) > 0) THEN
2116
2117 IF (l_total_match_quantity >= (l_ordered_qty - l_cancelled_qty - l_billed_qty)) THEN
2118 l_match_quantity := l_ordered_qty - l_cancelled_qty - l_billed_qty;
2119 l_match_amount := ap_utilities_pkg.ap_round_currency(l_match_quantity * l_match_unit_price,l_invoice_currency_code);
2120 ELSE
2121 l_match_quantity := l_total_match_quantity;
2122 l_match_amount := ap_utilities_pkg.ap_round_currency(l_match_quantity * l_match_unit_price,l_invoice_currency_code);
2123 END IF;
2124
2125 /* For overbill cases, for positive invoices we go off of ordered qty*/
2126 ELSE
2127
2128 IF (l_total_match_quantity >= l_ordered_qty - l_cancelled_qty) THEN
2129 l_match_quantity := l_ordered_qty - l_cancelled_qty;
2130 l_match_amount := ap_utilities_pkg.ap_round_currency(l_match_quantity * l_match_unit_price,l_invoice_currency_code);
2131 ELSE
2132 l_match_quantity := l_total_match_quantity;
2133 l_match_amount := ap_utilities_pkg.ap_round_currency(l_match_quantity * l_match_unit_price,l_invoice_currency_code);
2134 END IF;
2135
2136 END IF;
2137
2138 ELSE /*For Credit/Debit memos */
2139
2140 IF (l_total_match_quantity >= l_billed_qty) THEN
2141 l_match_quantity := -1*l_billed_qty;
2142 l_match_amount := ap_utilities_pkg.ap_round_currency(l_match_quantity * l_match_unit_price,l_invoice_currency_code);
2143 ELSE
2144 l_match_quantity := l_total_match_quantity;
2145 l_match_amount := ap_utilities_pkg.ap_round_currency(l_match_quantity * l_match_unit_price,l_invoice_currency_code);
2146 END IF;
2147
2148 END IF;
2149
2150 l_total_match_quantity := l_total_match_quantity - l_match_quantity;
2151 l_total_match_amount := l_total_match_amount - l_match_amount;
2152
2153 l_debug_info := 'Call Receipt Matching api';
2154
2155 AP_RECT_MATCH_PKG.Base_Credit_Rcv_Match(X_Match_Mode => l_match_mode,
2156 X_Invoice_Id => p_invoice_id,
2157 X_Invoice_Line_Number => p_invoice_line_number,
2158 X_Po_Line_Location_Id => l_po_line_location_id,
2159 X_Rcv_Transaction_Id => l_rcv_transaction_id,
2160 X_Dist_Tab => l_dist_tab,
2161 X_Amount => l_match_amount,
2162 X_Quantity => l_match_quantity,
2163 X_Unit_Price => l_match_unit_price,
2164 X_Uom_Lookup_Code => l_unit_meas_lookup_code,
2165 X_freight_cost_factor_id => NULL,
2166 X_freight_amount => NULL,
2167 X_freight_description => NULL,
2168 X_misc_cost_factor_id => NULL,
2169 X_misc_amount => NULL,
2170 X_misc_description => NULL,
2171 X_retained_amount => l_retained_amount,
2172 X_Calling_Sequence => l_current_calling_sequence);
2173
2174 ELSIF (l_matching_basis = 'AMOUNT') THEN
2175
2176 RCV_INVOICE_MATCHING_SV.Get_ReceiveAmount(
2177 p_api_version => 1.0,
2178 p_init_msg_list => FND_API.G_TRUE,
2179 x_return_status => l_ret_status,
2180 x_msg_count => l_msg_count,
2181 x_msg_data => l_msg_data,
2182 p_receive_transaction_id => l_rcv_transaction_id,
2183 x_billed_amt => l_amount_billed,
2184 x_received_amt => l_amount_received,
2185 x_delivered_amt => l_amount_delivered,
2186 x_corrected_amt => l_amount_corrected);
2187
2188 PO_AP_INVOICE_MATCH_GRP.Get_po_ship_amounts(
2189 p_api_version => 1.0,
2190 p_receive_transaction_id => l_rcv_transaction_id,
2191 x_ship_amt_ordered => l_amount_ordered,
2192 x_ship_amt_cancelled => l_amount_cancelled,
2193 x_ret_status => l_ret_status,
2194 x_msg_count => l_msg_count,
2195 x_msg_data => l_msg_data);
2196
2197 l_amount_billed := nvl(l_amount_billed,0);
2198 l_amount_delivered := nvl(l_amount_delivered,0);
2199 l_amount_cancelled := nvl(l_amount_cancelled,0);
2200
2201 IF (l_match_mode IN ('STD-PS','STD-PD')) THEN
2202
2203 IF (l_amount_ordered - l_amount_cancelled - l_amount_billed > 0) THEN
2204
2205 IF (l_total_match_amount >= l_amount_ordered - l_amount_cancelled - l_amount_billed) THEN
2206 l_match_amount := l_amount_ordered - l_amount_cancelled - l_amount_billed;
2207 ELSE
2208 l_match_amount := l_total_match_amount;
2209 END IF;
2210
2211 /* For the overbill cases */
2212 ELSE
2213
2214 IF (l_total_match_amount >= l_amount_ordered - l_amount_cancelled) THEN
2215 l_match_amount := l_amount_ordered - l_amount_cancelled;
2216 ELSE
2217 l_match_amount := l_total_match_amount;
2218 END IF;
2219
2220 END IF;
2221
2222 ELSE /*For Credit/Debit memos */
2223
2224 IF (l_total_match_amount >= l_amount_billed) THEN
2225 l_match_amount := -1*l_amount_billed;
2226 ELSE
2227 l_match_amount := l_total_match_amount;
2228 END IF;
2229
2230 END IF; /* l_match_mode IN 'STD-PS' ...*/
2231
2232 l_match_quantity := ROUND(l_match_amount/l_match_unit_price,15);
2233
2234 l_total_match_amount := l_total_match_amount - l_match_amount;
2235 l_total_match_quantity := l_total_match_quantity - l_match_quantity;
2236
2237 l_debug_info := 'Call Receipt Matching api for service orders';
2238 AP_RCT_AMT_MATCH_PKG.AP_AMT_MATCH(
2239 X_match_mode => l_match_mode,
2240 X_invoice_id => p_invoice_id,
2241 X_invoice_line_number => p_invoice_line_number,
2242 X_dist_tab => l_dist_tab,
2243 X_po_line_location_id => l_po_line_location_id,
2244 X_rcv_transaction_id => l_rcv_transaction_id,
2245 X_amount => l_match_amount,
2246 X_quantity => l_match_quantity,
2247 X_unit_price => l_match_unit_price,
2248 X_uom_lookup_code => l_unit_meas_lookup_code,
2249 X_freight_cost_factor_id => NULL,
2250 X_freight_amount => NULL,
2251 X_freight_description => NULL,
2252 X_misc_cost_factor_id => NULL,
2253 X_misc_amount => NULL,
2254 X_misc_description => NULL,
2255 X_retained_amount => l_retained_amount,
2256 X_calling_sequence => l_current_calling_sequence);
2257
2258 END IF; /* l_matching_basis */
2259
2260 END LOOP;
2261
2262 CLOSE C_Rcv_Transactions;
2263
2264 END IF; /* l_match_mode = 'STD-PS' OR l_total_match_quantity > 0 OR ... */
2265
2266 EXCEPTION WHEN OTHERS THEN
2267 IF (SQLCODE <> -20001) THEN
2268 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
2269 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
2270 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',l_current_calling_sequence);
2271 FND_MESSAGE.SET_TOKEN('PARAMETERS',
2272 ' X_Invoice_Id = '||TO_CHAR(P_Invoice_id)
2273 ||', X_Invoice_Line_Number = '||TO_CHAR(P_Invoice_Line_Number));
2274 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
2275 END IF;
2276 APP_EXCEPTION.RAISE_EXCEPTION;
2277
2278 END Match_To_Rcv_Shipment_Line;
2279
2280
2281
2282 --Bug 5524881 ISP Receipt Matching
2283 /*=============================================================================
2284 | FUNCTION - Get_rcv_ship_qty_amt
2285 |
2286 | DESCRIPTION
2287 | This API is used by the SupplierPortal in the PO Search Page to display
2288 | the quantity_recieved, quantity_billed, amount_recieved, amount_billed
2289 | etc for a Reciept Shipment Line
2290 | PARAMETERS
2291 | p_rcv_shipment_line_id Receipt Shipment Line Id,
2292 | p_matching_basis Qnantity or Amount
2293 | p_returned_item This parameter can take six different values
2294 | and the function returns value associated with
2295 | this parameter for a Receipt Ship Line.
2296 |
2297 | MODIFICATION HISTORY
2298 | DATE Author Action
2299 | 09/27/06 dgulraja Created
2300 | 09-Nov-09 sjetti Modified for bug 8881382 to prevent overbill
2301 | of receipt returns.
2302 |
2303 *============================================================================*/
2304 FUNCTION Get_rcv_ship_qty_amt(p_rcv_shipment_line_id IN NUMBER,
2305 p_matching_basis IN VARCHAR2,
2306 p_returned_item IN VARCHAR2)
2307 RETURN NUMBER IS
2308
2309 CURSOR C_Rcv_Transactions (p_rcv_shipment_line_id IN NUMBER) IS
2310 SELECT rcv.transaction_id,
2311 pll.matching_basis,
2312 pll.line_location_id
2313 FROM rcv_transactions rcv,
2314 rcv_shipment_lines rsl,
2315 po_line_locations pll
2316 WHERE rcv.shipment_line_id = rsl.shipment_line_id
2317 AND rsl.shipment_line_id = p_rcv_shipment_line_id
2318 AND pll.line_location_id = rcv.po_line_location_id
2319 AND rcv.transaction_type IN ('RECEIVE','MATCH');
2320
2321 l_po_line_location_id PO_LINE_LOCATIONS.LINE_LOCATION_ID%TYPE;
2322 l_rcv_transaction_id RCV_TRANSACTIONS.TRANSACTION_ID%TYPE;
2323 l_matching_basis PO_LINE_LOCATIONS.MATCHING_BASIS%TYPE;
2324 l_po_distribution_id PO_DISTRIBUTIONS.PO_DISTRIBUTION_ID%TYPE;
2325
2326 l_ordered_qty NUMBER;
2327 l_cancelled_qty NUMBER;
2328 l_received_qty NUMBER;
2329 l_corrected_qty NUMBER;
2330 l_delivered_qty NUMBER;
2331 l_transaction_qty NUMBER;
2332 l_billed_qty NUMBER;
2333 l_accepted_qty NUMBER;
2334 l_rejected_qty NUMBER;
2335
2336 l_amount_delivered NUMBER;
2337 l_amount_corrected NUMBER;
2338 l_amount_ordered NUMBER;
2339 l_amount_cancelled NUMBER;
2340 l_amount_billed NUMBER;
2341 l_amount_received NUMBER;
2342 l_ret_status VARCHAR2(100);
2343 l_msg_count NUMBER;
2344 l_msg_data VARCHAR2(250);
2345
2346 l_debug_info VARCHAR2(1000);
2347 l_current_calling_sequence VARCHAR2(2000);
2348
2349 l_total_ordered_qty NUMBER;
2350 l_total_cancelled_qty NUMBER;
2351 l_total_billed_qty NUMBER;
2352 l_total_delivered_qty NUMBER;
2353 l_total_amount_billed NUMBER;
2354 l_total_amount_cancelled NUMBER;
2355 l_total_amount_ordered NUMBER;
2356 l_total_received_qty NUMBER;
2357 l_total_received_Amount NUMBER;
2358
2359
2360 BEGIN
2361
2362 l_current_calling_sequence := 'AP_Matching_Utils_Pkg. Get_qty_amt <-' ;
2363 l_total_ordered_qty := 0;
2364 l_total_cancelled_qty := 0;
2365 l_total_billed_qty := 0;
2366 l_total_delivered_qty := 0;
2367 l_total_amount_billed := 0;
2368 l_total_amount_cancelled := 0;
2369 l_total_amount_ordered := 0;
2370 l_total_received_qty := 0;
2371 l_total_received_Amount := 0;
2372
2373 l_debug_info := 'Open C_Rcv_Transactions cursor';
2374 OPEN c_rcv_transactions(p_rcv_shipment_line_id);
2375 LOOP
2376 FETCH C_Rcv_Transactions
2377 INTO l_rcv_transaction_id,
2378 l_matching_basis,
2379 l_po_line_location_id;
2380 EXIT WHEN C_Rcv_Transactions%NOTFOUND;
2381
2382 IF (l_matching_basis = 'QUANTITY') THEN
2383
2384 AP_MATCHING_UTILS_PKG.Get_receipt_Quantities(
2385 l_rcv_transaction_id,
2386 l_ordered_qty,
2387 l_cancelled_qty,
2388 l_received_qty,
2389 l_corrected_qty,
2390 l_delivered_qty,
2391 l_transaction_qty,
2392 l_billed_qty,
2393 l_accepted_qty,
2394 l_rejected_qty);
2395
2396
2397 l_billed_qty := nvl(l_billed_qty,0);
2398 l_cancelled_qty := nvl(l_cancelled_qty,0);
2399 l_ordered_qty := nvl(l_ordered_qty,0);
2400 l_received_qty := nvl(l_received_qty,0);
2401 l_delivered_qty := nvl(l_delivered_qty,0); --Bug 8881382
2402
2403 ELSIF l_matching_basis = 'AMOUNT' THEN
2404
2405 RCV_INVOICE_MATCHING_SV.Get_ReceiveAmount(
2406 p_api_version => 1.0,
2407 p_init_msg_list => FND_API.G_TRUE,
2408 x_return_status => l_ret_status,
2409 x_msg_count => l_msg_count,
2410 x_msg_data => l_msg_data,
2411 p_receive_transaction_id => l_rcv_transaction_id,
2412 x_billed_amt => l_amount_billed,
2413 x_received_amt => l_amount_received,
2414 x_delivered_amt => l_amount_delivered,
2415 x_corrected_amt => l_amount_corrected);
2416
2417 PO_AP_INVOICE_MATCH_GRP.Get_po_ship_amounts(
2418 p_api_version => 1.0,
2419 p_receive_transaction_id => l_rcv_transaction_id,
2420 x_ship_amt_ordered => l_amount_ordered,
2421 x_ship_amt_cancelled => l_amount_cancelled,
2422 x_ret_status => l_ret_status,
2423 x_msg_count => l_msg_count,
2424 x_msg_data => l_msg_data);
2425
2426 l_amount_ordered := nvl(l_amount_ordered, 0);
2427 l_amount_billed := nvl(l_amount_billed,0);
2428 l_amount_cancelled := nvl(l_amount_cancelled,0);
2429 l_amount_received := nvl(l_amount_received, 0);
2430
2431 END IF;
2432
2433 l_total_ordered_qty := l_ordered_qty + l_total_ordered_qty;
2434 l_total_billed_qty := l_billed_qty + l_total_billed_qty;
2435 l_total_cancelled_qty := l_cancelled_qty + l_total_cancelled_qty;
2436
2437 l_total_amount_ordered := l_amount_ordered + l_total_amount_ordered;
2438 l_total_amount_billed := l_amount_billed + l_total_amount_billed;
2439 l_total_amount_cancelled := l_amount_cancelled + l_total_amount_cancelled;
2440
2441 l_total_received_qty := l_received_qty + l_total_received_qty;
2442 l_total_delivered_qty := l_delivered_qty + l_total_delivered_qty; --Bug 8881382
2443 l_total_received_Amount := l_amount_received + l_total_received_Amount;
2444 END LOOP;
2445 CLOSE C_Rcv_Transactions;
2446
2447 IF p_returned_item = 'QUANTITY_BILLED' THEN
2448 RETURN l_total_billed_qty;
2449 ELSIF p_returned_item = 'QUANTITY_RECEIVED' THEN
2450 RETURN l_total_delivered_qty; --Bug 8881382
2451 ELSIF p_returned_item = 'AMOUNT_BILLED' THEN
2452 RETURN l_total_amount_billed;
2453 ELSIF p_returned_item = 'AMOUNT_RECEIVED' THEN
2454 RETURN l_total_received_amount;
2455 ELSIF p_returned_item = 'QUANTITY_UNBILLED' THEN
2456 RETURN l_total_received_qty - l_total_cancelled_qty - l_total_billed_qty;
2457 ELSIF p_returned_item = 'AMOUNT_UNBILLED' THEN
2458 RETURN l_total_received_amount - l_total_amount_cancelled - l_total_amount_billed ;
2459 END IF;
2460
2461
2462 EXCEPTION WHEN OTHERS THEN
2463 IF (SQLCODE <> -20001) THEN
2464 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
2465 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
2466 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',l_current_calling_sequence);
2467 FND_MESSAGE.SET_TOKEN('PARAMETERS',
2468 ' p_rcv_shipment_line_id = '||TO_CHAR(p_rcv_shipment_line_id));
2469 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
2470 END IF;
2471 APP_EXCEPTION.RAISE_EXCEPTION;
2472
2473 END;
2474
2475 --Introduced below procedure for bug#10062826
2476
2477 Procedure Get_Num_Rect_Dists (
2478 P_rcv_transaction_id IN NUMBER,
2479 P_num_rect_po_dists OUT NOCOPY NUMBER) IS
2480
2481 Begin
2482
2483 SELECT count(*)
2484 INTO P_num_rect_po_dists
2485 FROM po_distributions_all pod,
2486 rcv_transactions rt
2487 WHERE rt.transaction_id =P_rcv_transaction_id
2488 and rt.po_line_location_id = pod.line_location_id
2489 and (rt.po_distribution_id is null
2490 or rt.po_distribution_id = pod.po_distribution_id);
2491
2492 End Get_Num_Rect_Dists;
2493
2494 --Introduced this procedure for bug#13505998
2495 FUNCTION GET_FULL_MTCH_AMT(P_po_line_location_id IN NUMBER,
2496 P_curr_code IN VARCHAR2)
2497 RETURN NUMBER IS
2498
2499 L_FULL_AMT NUMBER;
2500
2501 BEGIN
2502 SELECT
2503 ap_utilities_pkg.ap_round_currency(
2504 NVL(DECODE(MATCHING_BASIS,
2505 'QUANTITY',
2506 (QUANTITY - NVL(QUANTITY_CANCELLED,0))*PRICE_OVERRIDE,
2507 (AMOUNT - NVL(AMOUNT_CANCELLED,0))),0),
2508 P_curr_code)
2509 INTO L_FULL_AMT
2510 FROM PO_LINE_LOCATIONS_ALL
2511 WHERE LINE_LOCATION_ID = P_po_line_location_id;
2512
2513 RETURN L_FULL_AMT;
2514
2515 EXCEPTION
2516 WHEN OTHERS THEN
2517 RETURN NULL;
2518
2519 END GET_FULL_MTCH_AMT;
2520
2521 -- New API added for Bug 11843285.
2522 PROCEDURE Is_Proj_Exp_Item_Date_Valid
2523 (
2524 p_transaction_date IN DATE ,
2525 p_gl_date IN DATE ,
2526 p_line_location_id IN NUMBER ,
2527 p_date_valid OUT NOCOPY VARCHAR2 ,
2528 p_pa_message_name OUT NOCOPY VARCHAR2 ,
2529 p_token_value1 OUT NOCOPY VARCHAR2 ,
2530 p_token_value2 OUT NOCOPY VARCHAR2
2531 ) IS
2532
2533 CURSOR po_dists_cursor IS
2534 SELECT po_distribution_id, project_id
2535 FROM po_distributions
2536 WHERE line_location_id = p_line_location_id
2537 AND project_id IS NOT NULL ;
2538
2539 l_po_distribution_id po_distributions_all.po_distribution_id%TYPE ;
2540 l_project_id po_distributions_all.project_id%TYPE ;
2541 l_exp_item_date DATE ;
2542 l_is_date_valid VARCHAR2(1) := 'Y' ;
2543 l_pa_message_name FND_NEW_MESSAGES.MESSAGE_NAME%TYPE := NULL ;
2544 l_token_value1 FND_NEW_MESSAGES.MESSAGE_TEXT%TYPE; --Bug 14236129 Modified Bug 16089519
2545 l_token_value2 FND_NEW_MESSAGES.MESSAGE_TEXT%TYPE; --Bug 14236129 Modified Bug 16089519
2546 l_api_name VARCHAR2(50);
2547 l_debug_info VARCHAR2(2000);
2548 l_current_calling_sequence VARCHAR2(2000); -- Bug 14069839.
2549
2550 BEGIN
2551
2552 l_current_calling_sequence := 'AP_Matching_Utils_Pkg.Is_Proj_Exp_Item_Date_Valid <-' ; -- Bug 14069839.
2553
2554 l_api_name := 'Is_Proj_Exp_Item_Date_Valid';
2555 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
2556 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_MATCHING_UTILS_PKG.Validate_Po_Match_Exp_Item_Date');
2557 END IF;
2558
2559 l_debug_info := 'Open Cursor po_dists_cursor';
2560 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2561 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
2562 END IF;
2563
2564 OPEN po_dists_cursor ;
2565
2566 LOOP
2567
2568 FETCH po_dists_cursor INTO l_po_distribution_id,
2569 l_project_id;
2570
2571 EXIT WHEN po_dists_cursor%NOTFOUND;
2572
2573 l_debug_info := 'Validating EI date on PO distribution : ' || l_po_distribution_id;
2574 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2575 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
2576 END IF;
2577 --Bug 14236129
2578 PA_AP_INTEGRATION.Get_Po_Match_Si_Exp_Item_Date
2579 ( p_transaction_date,
2580 p_gl_date,
2581 null,
2582 sysdate,
2583 l_po_distribution_id,
2584 'PO-MATCH',
2585 l_exp_item_date,
2586 l_is_date_valid,
2587 l_pa_message_name,
2588 l_token_value1,
2589 l_token_value2) ;
2590
2591 l_debug_info := 'l_is_date_valid : ' || l_is_date_valid;
2592 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2593 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
2594 END IF;
2595
2596 IF l_is_date_valid = 'N' THEN
2597
2598 l_debug_info := 'EI date NOT valid for the PO dist. Returning N.';
2599 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2600 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
2601 END IF;
2602
2603 EXIT ;
2604 END IF ;
2605
2606 END LOOP ;
2607
2608 CLOSE po_dists_cursor ;
2609
2610 p_date_valid := l_is_date_valid ;
2611 p_pa_message_name := l_pa_message_name ;
2612 p_token_value1 := l_token_value1 ; --Bug 14236129
2613 p_token_value2 := l_token_value2 ; --Bug 14236129
2614
2615 -- Bug 14069839. Added modified exception block.
2616 EXCEPTION WHEN OTHERS THEN
2617 IF (SQLCODE <> -20001) THEN
2618 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
2619 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
2620 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',l_current_calling_sequence);
2621 FND_MESSAGE.SET_TOKEN('PARAMETERS',
2622 ' p_line_location_id = '||TO_CHAR(p_line_location_id) ||
2623 ' ,l_po_distribution_id = ' || l_po_distribution_id);
2624 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
2625 END IF;
2626 APP_EXCEPTION.RAISE_EXCEPTION;
2627
2628 END Is_Proj_Exp_Item_Date_Valid;
2629
2630 END AP_MATCHING_UTILS_PKG;
2631