Skip to content

SQL View – Inventory Receipts

March 29, 2010

The following view returns the results from IV10200 inventory receipts table.  This will show you every time you have received an item and at what costs. 

This view is also a modification of the spvInventoryReceipts view from Salespad (www.salespad.net).

You can name the view whatever you want by change the [tspvInventoryReceipts] below.  Also the USE [TSP] tells the script to only execute on my database named TSP.  You will need to modify the [TSP] to be whatever your database name is.

USE [TSP]
GO
/****** Object:  View [dbo].[tspvInventoryReceipts]    Script Date: 03/29/2010 17:29:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE view [dbo].[tspvInventoryReceipts]
AS
select
    [Item_Number]=itemnmbr,
    [Location]=trxloctn,
    [Date_Received]=daterecd,
    [Qty_Received]=qtyrecvd,
    [Qty_Sold]=qtysold,
    [Receipt_Number]=rcptnmbr,
    [Vendor_ID]=vendorid,
    [PO_Number]=pordnmbr,
    [Unit_Cost]=unitcost,
    [Extended_Cost]=((qtyrecvd-qtysold)*unitcost)
from iv10200 a

If you want to view your FIFO/LIFO inventory costs, then add the following where clause to the end of the above query:

FIFO

WHERE (Qty_Received-Qty_Sold)>1
ORDER BY Item_Number, Date_Received

LIFO

WHERE (Qty_Received-Qty_Sold)>1
ORDER BY Item_Number, Date_Received DESC

The results will be ordered by your FIFO or LIFO quantities depending on the clause you choose.

Update (4/6/2010):  It has been brought to my attention that my FIFO/LIFO clauses were exactly the same.  I have added a DESC to the LIFO clause ORDER BY statement to order the results of the received by the newest first (LIFO).  Thanks David!

About these ads

From → Dynamics GP

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: