Friday, November 18, 2016

Lot Availability Query

Recently I have been working with a client who has implemented Inventory Bill of Materials to Assemble materials having lot tracked components. It is important to fulfill orders with a single lot. So, quickly viewing component lot availability was a must.

While there is a Lot Inquiry window (Navigation: Inquiry >> Inventory >> Lots) the flexibility and information available in this window pales in comparison to what is possible using the following view coupled with SmartList Builder.


Lot Number Inquiry

CREATE View [dbo].[_Lots]
as
select LOTS.[ITEMNMBR] AS Item_Number,
LOTS.[LOCNCODE] AS Location_Code,
LOTS.[DATERECD] AS Date_Received,
LOTS.[LOTNUMBR] AS Lot_Number,
LOTS.[QTYRECVD] AS QTY_Received,
LOTS.[QTYSOLD] AS QTY_Sold,
LOTS.[ATYALLOC] AS QTY_Allocated,
cast(LOTS.UNITCOST as money) Unit_Cost,
LOTS.[VNDRNMBR] AS Vendor_Number,
isnull(VEND.VENDNAME,'') as Vendor_Name,
case when LOTS.LTNUMSLD = 1 then 'Yes' ELSE 'No' end AS 'Lot Number Sold',
case when LOTS.QTYTYPE = 1 then 'On Hand'
      when lots.QTYTYPE = 2 then 'Returned'
      when lots.QTYTYPE = 3 then 'In Use'
      when lots.QTYTYPE = 4 then 'In Service'
      when lots.QTYTYPE = 5 then 'Damaged'
      else 'ERROR'
      end
      AS QTY_Type,
LOTS.[BIN] AS Bin,
LOTS.[MFGDATE] AS Manufacture_Date,
LOTS.[EXPNDATE] AS Expiration_Date,
ITEMS.[ITEMDESC] AS Item_Description,
ITEMS.[ITMCLSCD] AS Item_Class_Code,
ITEMS.[LOTTYPE] AS Lot_Type,
(LOTS.[QTYRECVD]-LOTS.[QTYSOLD]) AS QTY_ON_HAND
from IV00300 as LOTS
inner join IV00101 as items on LOTS.ITEMNMBR = ITEMS.ITEMNMBR
left join PM00200 as VEND on LOTS.VNDRNMBR = VEND.VENDORID


GO

Grant Select on _Lots to DYNGRP

CASE Statement for BOMCAT_I in Dynamics GP Manufacturing BOM Revision Table BM010415

I have amassed a number of useful CASE statements, which can be incorporated into SQL queries run against Dynamics GP. When Querying the BOMCAT_I Field in Dynamics GP Manufacturing BOM Revision Table [BM010415].[BOMCAT_I] table: use the following case statement to return text versions of the BOM Categories in Dynamics GP:

select
CASE BM010415.BOMCAT_I
      WHEN 1 THEN 'MFG'
      WHEN 2 THEN 'ENG'
      WHEN 3 THEN 'ARCH'
      WHEN 4 THEN 'CONFIG'
      WHEN 5 THEN 'SUPER'
      ELSE 'ERROR'
END BOMCAT_I_TEXT,