SQL View – Inventory Receipts

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