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