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,

Urgent, Late Breaking News... Integration Manager and Macros Failing after several hundred records? Microsoft's New Navigation the Culprit!

I am working hard to maintain my sense of humor about this post!

Many months ago, July 2015, according to my One Note notes, I worked with a client who was having difficulty running an Integration using Integration Manager.

The problem was a failure after several hundred records were processed.

I found this very useful post by Mariano Gomez, which helped me resolve it.

Mariano Gomez's Useful Post on RPC Errors

Fast forward to December, 2015 and this same issue reared it's ugly head again! This time, I hit a wall. We were under pressure, and needed long-tested integrations to run, in order to hit a year-end cut-over deadline. 

So, I logged a Support Incident with Microsoft and went to Plan B. In this case, we used the eConnect Adapter to get past this problem... This meant redesigning and testing a bunch of integrations at a moment's notice; time consuming, fraught with peril and expensive.

Fast forward a few weeks, and I was endeavoring to get some basic macros working for the same customer, and whamo! The same behavior with a different tool set!

Here is where things get interesting. Within a few minutes of reviewing my notes, I realized the problem. We'd applied the Year-End update (Payroll Client), and the update had re-set the navigation option to the Action Pane (Pain) from the classic Menu Bar option in User Preference!!!!

The Action Pane, since GP 2013 has been known for this behavior! 

If you are struggling with Integrations or Macros, and you have been breaking them down into bite sized pieces for processing, STOP! Change the User Preferences for the Windows Command Display option to Menu Bar (old reliable), and get back to work.

I have asked Microsoft to Fast Publish this one, escalate it and make sure it is in the Knowledgebase and on the tip of the tongue of front line technical support. The best I can do is post my experience.

I hope this helps you get where you are going.