Skip to content

SQL Scripts–Vendor Invoice Pricing Discrepancy

September 14, 2011

The following SQL Script will show you all Payables invoices that have been entered into your system where the price your vendor is charging does not match the price of your purchase order.

We use this, in conjunction with Task Centre, to alert us when a vendor has charged us more than what our purchase order was submitted for.  Very valuable when you have a payables clerk processing 100’s of invoices a day (it is easy for them to make a mistake).

This script has options for total amount of discrepancy.  This allows you to “tweak” the script to not show line items that are off a couple of pennies (or whatever tolerance you wish). 

We actually use $5, so that is set as default in the script.  To change, replace the value in line 2 (SET @dollar_amount=5), to whatever dollar value you want.  DO NOT INCLUDE THE DECIMALS.

The script using the following views:

Purchase Order  (tspvPurchaseOrder)

**SQL Script***

DECLARE @dollar_amount INT
SET @dollar_amount = 5

select  [Invoice_Date] = c.receiptdate,
[PO_Number] = a.po_number,
[POP_Type] = case when c.poptype = ’1′ then ‘Shipment’
when c.poptype = ’2′ then ‘ Invoice’
when c.poptype = ’3′ then ‘Shipment/Invoice’
else ‘N/A’
end,
[PO_Date] = po_date,
[Vendor_ID] = vendor_id,
[Vendor_Name] = vendor_name,
[Item_Number] = d.itemnmbr,
[Item_Desc] = d.itemdesc,
[PO_Qty] = d.qtyorder,
[Inv Unit Cost] = b.unitcost,
[PO Unit Cost] = d.unitcost,
[Created_By] = c.user2ent
from    tspvpurchaseorder a
join pop30310 b on a.po_number = b.ponumber
join pop30300 c on b.poprctnm = c.poprctnm
join pop10110 d on a.po_number = d.ponumber
AND b.itemnmbr = d.itemnmbr
AND b.itemdesc = d.itemdesc
where   ( ( b.unitcost – d.unitcost ) > @dollar_amount )
order by po_number desc

About these ads

From → Dynamics GP, SQL, Views

4 Comments
  1. Aboody permalink

    Hi Ron

    I tried to execute this script but i got a message saying that
    Could not find stored procedure ‘tspvpurchaseorder’.
    Any help .

    Thanks

    Abbody

    • Ron permalink

      You need to also have the tspvpurchaseorder view already created.

      I include the view requirements at the top of all my scripts, but I missed this one somehow. Thanks for pointing it out. I will update the script with a link to tspvpurchaseorder but in the mean time you can find it here:

      http://rldu.wordpress.com/2010/03/11/dynamics-gp-purchase-order-view/

      Sorry for the inconvenience and thanks for letting me know you had the problem.

      Ron

Trackbacks & Pingbacks

  1. SQL Scripts–Vendor Invoice Pricing Discrepancy « Real Life Dynamics User - DynamicAccounting.net - GP Technical Blogs - Microsoft Dynamics Community
  2. Everything Dynamics GP #32 - About Dynamics, Development and Life - GP Technical Blogs - Microsoft Dynamics Community

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 28 other followers

%d bloggers like this: