Issue: Invoice unit price -> (Purchase Order (PO) unit price x % tolerance) but invoice is not going on price hold. As a result the supplier has been overpaid as the invoice passed the approval process.
Invoice Price Tolerance = 5%
PO price = .70 PO qty = 1
INV price = 19.20 INV qty = 1
The current functionality in Accounts Payable is not clearly documented. The current documentation states that PRICE HOLD will be place on invoice if:
INVOICE UNIT PRICE > (PO UNIT PRICE x (1 + % tolerance))
UNIT_PRICE in AP_INVOICE_DISTRIBUTIONS > PRICE_OVERRIDE in PO_LINE_LOCATIONS (PRICE Hold)
Weighted average price of all distributions on the matched invoice and all price corrections related to the invoice is more than
[purchase order unit price (1 plus% tolerance)]
Unit Price - P.O./Invoice. Unit price of the item from the purchase order line/invoice. Payables compares the unit prices for a purchase order and matched invoice and applies a Price hold to an invoice distribution if the invoice unit price exceeds the purchase order unit price by more than the tolerance level you allow.
Price. The average price of all matched invoices exceeds purchase order price.
System uses average unit price in ap invoices and po unit price to calculate price variance.
Variance = (Inv amt/qty) - (price tolerance * unit price)
If unit price is with lots of decimals. For these cases users can define tolerance with some minimal positive percentage(0.5 to 1).
So system does not place price hold with this tolerance.
Responsibility: Accounts payables super user
Navigation: Setup - Invoices - ToleranceInvoice Price Tolerance = 5%
PO price = .70 PO qty = 1
INV price = 19.20 INV qty = 1
The current functionality in Accounts Payable is not clearly documented. The current documentation states that PRICE HOLD will be place on invoice if:
INVOICE UNIT PRICE > (PO UNIT PRICE x (1 + % tolerance))
UNIT_PRICE in AP_INVOICE_DISTRIBUTIONS > PRICE_OVERRIDE in PO_LINE_LOCATIONS (PRICE Hold)
Weighted average price of all distributions on the matched invoice and all price corrections related to the invoice is more than
[purchase order unit price (1 plus% tolerance)]
Unit Price - P.O./Invoice. Unit price of the item from the purchase order line/invoice. Payables compares the unit prices for a purchase order and matched invoice and applies a Price hold to an invoice distribution if the invoice unit price exceeds the purchase order unit price by more than the tolerance level you allow.
Price. The average price of all matched invoices exceeds purchase order price.
System uses average unit price in ap invoices and po unit price to calculate price variance.
Variance = (Inv amt/qty) - (price tolerance * unit price)
If unit price is with lots of decimals. For these cases users can define tolerance with some minimal positive percentage(0.5 to 1).
So system does not place price hold with this tolerance.
Responsibility: Accounts payables super user
Check the PO matching zone what is price%
a) select * from PO_headers_all
where segment1 in (301977,272062); ----- PO#
b) select * from po_lines_all
where po_header_id in (1149476,1184290); ---- get the po_header_id from the above script.
c) Select SUM(NVL(PD.quantity_billed,0)), (SUM(NVL(PD.amount_billed,0)))
FROM po_distributions_all PD
WHERE PD.line_location_id in (2704791);
d) Select sum(D.quantity_invoiced), sum(nvl(D.amount,0))
FROM ap_invoice_distributions_all D,
po_distributions_all PD
WHERE D.po_distribution_id+0 = PD.po_distribution_id
AND PD.line_location_id in (3129997);
e) Select SUM(NVL(PD.quantity_billed,0)), (SUM(NVL(PD.amount_billed,0)))
FROM po_distributions_all PD
WHERE PD.line_location_id in (3129997);
PO#301977 – unit price is 247.5
AUP= 495/95 = 5.21053
The PO price is being compared to the AVERAGE price of ALL THE INVOICES MATCHED to the PO. So if the average price of all the invoices is greater than the PO price and the tolerance, then the
PRICE hold will come on, even if the current invoice is equal to or less than the PO price.
In both the above cases the AUP is greater than the PO price and the tolerance.
Run the APLISTH script which will provide more details , once you run the aplisth script you can make a note of the columns like invoice_id, Line_Location_ID so that you can run tbe below script to check further.
SELECT AID.invoice_id, PD.line_location_id SHIP_ID,
DECODE(SUM(nvl(quantity_invoiced,0)),
0, 0,
null,0,
(nvl(sum(decode(nvl(unit_price,0)*nvl(quantity_invoiced,0),0,amount,
nvl(unit_price,0)*nvl(quantity_invoiced,0)
)
),0) /
sum(nvl(quantity_invoiced, 0)))) avg_price
FROM ap_invoice_distributions_all AID,
po_distributions_all PD
WHERE ( AID.parent_invoice_id =
OR AID.invoice_id =
AND AID.po_distribution_id = PD.po_distribution_id
AND PD.line_location_id =
AND AID.line_type_lookup_code = 'ITEM'
GROUP BY AID.invoice_id, PD.line_location_id;
References:
A documentation Bug 1149642 has been logged to update the current documentation to be clear and concise. In addition, an enhancement Bug 1149668 has been been approved by development to have a setup option so users can have the price hold be per invoice or for all invoices for a PO.
Source: support.oracle.com, AP User guide, etrm guide.
