Dynamics F/SCM Customer Experience (CRM) Dynamics GP Dynamics Business Central (NAV) Dynamics SL Power Platform Fabric Copilot

[Power Hely]

Good morning, all:

We are new to GP, having just made the transition in January from Syspro.

I have an ask from one of my Warehouse Managers.  We have an outbuilding at one site for overflow storage that has no connectivity whatsoever (an old barn). So stock takes have to be done manually on paper and then input after the fact. In Syspro, we had an off-the-shelf bin content report by Item Number, Lot Number, and Quantity.  Is there such a report/view/smartlist/etc. for GP off-the-shelf? If not, can someone point me at the right tables or views I can use to build something like this?

My thanks in advance for any help you can provide.

——————————
J. Power Hely
Hely & Weber
Santa Paula, CA and DFW Airport, TX
——————————

[Windi Epperson]

Hey John,
By off-the-shelf do you mean that the inventory items/quantities are not in GP at all?  If the items are in GP, do you have a Location for the barn or are the items just mixed in with the other stock?

My train of thought is if the items are in GP and in their own site, then you can use the native Stock Count functionality to print your reports.  You would just build a Stock Count for the Barn location and when you print the stock count report, check the box to include serial/lot numbers.

If they aren’t in their own location, then there are a couple of other fields in GP that you can use to restrict/select items for stock counts and maybe you could use one of those fields to build a stock count schedule for just the Barn items.

Hope that gives you a starting point.
Thanks
Windi

——————————
Windi Epperson
President/GP Senior Consultant
Advanced Integrators, Inc.
Norman OK
405-946-1774 Ext 102
——————————
——————————————-

[Power Hely]

Hi Windi. The barn is not its own site, but is its own bin in a site (imaginatively named site CA, bin BARN).  We normally do stock takes through Accellos COLLECT, but that is handheld-based and we have no connectivity in the barn, hence my need for another option. I’ll test to see if I can print a hardcopy report with lot numbers as part of a GP stock take.  Thanks!

——————————
John Hely
Hely & Weber
Santa Paula CA
——————————
——————————————-

[Sandra Dodge]

We moved the stock status reports to SSRS and print them Bin specific for hard counts and also for Auditor reporting.  I’ll see if I can find the exact query if you need it.

Sandra

——————————
Sandra Dodge
Iridium
Gilbert AZ
——————————
——————————————-

[John Arnold]

Hi John,

I think this query might give you a good start.  Paper always works, but you may considered using the query to drive an excel refreshable report.  Put it on a laptop that has network access, refresh the Excel data then take it to the barn and record the count directly in Excel.  Just a thought 🙂

SELECT   RTRIM(IV00112.ITEMNMBR) AS ItemNumber,
                RTRIM(IV00101.ITEMDESC) AS Description,
                IV00112.LOCNCODE AS Location,
                IV00112.BIN AS Bin,
                CASE IV00112.QTYTYPE
                                WHEN 1 THEN ‘On Hand’
                                WHEN 2 THEN ‘Returned’
                                WHEN 3 THEN ‘In Use’
                                WHEN 4 THEN ‘In Service’
                                WHEN 5 then ‘Damaged’
                END AS InvType,
                IV00112.QUANTITY AS Qty,
                ATYALLOC AS Allocated
FROM     IV00112
JOIN        IV00101 ON IV00101.ITEMNMBR = IV00112.ITEMNMBR

Good Luck and if you have any questions, just give me a yell!

——————————
John Arnold John_P_Arnold@hotmail.com
US Digital
Vancouver WA

——————————
——————————————-

[David Hayford]

We also use Collect and Lot tracking in GP
Created a number of SQL Views for your kind of questions and have ways to print out the bar coded information so you can use the handheld guns to enter the paper information back to GP.

Would love to discuss directly anytime.

SQL code for site, bin, item, lot with quantities:

SELECT TOP (100) PERCENT T1.ITEMNMBR,

T2.BIN,

T1.LOCNCODE,

T1.LOTNUMBR,

T1.QTYRECVD,

T1.QTYSOLD,

T1.ATYALLOC AS LotAlloc,

T3.ITEMDESC,

T3.ITMCLSCD,

T2.QUANTITY AS BinQTY,

T2.ATYALLOC AS BinAlloc

FROM dbo.IV00300 AS T1

LEFT OUTER JOIN dbo.IV00112 AS T2 ON T1.ITEMNMBR = T2.ITEMNMBR

AND T1.LOCNCODE = T2.LOCNCODE

AND T1.BIN = T2.BIN

LEFT OUTER JOIN dbo.IV00101 AS T3 ON T1.ITEMNMBR = T3.ITEMNMBR

ORDER BY T1.LOCNCODE,

T1.BIN,

T3.ITEMNMBR

——————————
David Hayford
Owner
Greenfield Pro Services, LLC
Alpharetta GA
——————————
——————————————-