5,080
We continue to have issues with lot numbers not being available ...or expiring?
When I run reconcile ...they unallocate the lots...but when I go to adjust I get the lot is unaavailable.
Can I just delete these in the SQL??
*This post is locked for comments
Thank you for your assistance with this Tim!!
Yes I believe the problem is with the Manufacturing module also...
the allocate\unallocate scenario did happen a fair amount when we first started using the system as some of our boms were wrong.
now we just edit the pick lists and these problems have mostly disappeared.
Tami,
To elaborate. Something is not happening correctly. To see allocation on the Lot table (IV00300) and not in Quantities Master (IV00102) is not normal. IV00102 should be allocated first (soft allocation) or at the same time as IV00300 (hard allocation). Do you have ISV products or are you integrating IV or MOP transactions directly? If this were in my own environment, I would create a trigger on IV00300 for INSERT and UPDATE to log to a table when/who changes are taking place. I have SQL code to do this if you want. If my second script returns nothing, you would be (99.9%) safe to remove the allocation via SQL. But, I think you want to know what the root cause is rather than spending the rest of your days fixing. I have similar things happening here very occasionally. Most often caused by Manufacturing module when users Allocate, Reverse Allocate, Allocate, Reverse Allocate, several times. But, the cause is a Pending Manufacturing transaction quantity in a table somewhere and can be seen and fixed.
Tim
Tami,
You're right. Not "normal."
This is the SQL from a KB:
mbs2.microsoft.com/.../kbdisplay.aspx
Substitute your Item number for A below:
declare @ITEMNMBR char(30)
select @ITEMNMBR = 'A'
print 'Allocated Documents in Sales Order Processing'
print '==============================================================='
print ''
if exists (select * from sysobjects where name ='SOP10200')
Begin
print 'Allocated Orders in Sales Order Processing'
select SOPNUMBE,ITEMNMBR,ITEMDESC,ATYALLOC from SOP10200 where ITEMNMBR = @ITEMNMBR and ATYALLOC > 0 and SOPTYPE = 2
end
if exists (select * from sysobjects where name ='SOP10200')
Begin
print 'Allocated Invoices in Sales Order Processing'
select SOPNUMBE,ITEMNMBR,ITEMDESC,ATYALLOC from SOP10200 where ITEMNMBR = @ITEMNMBR and ATYALLOC > 0 and SOPTYPE = 3
end
if exists (select * from sysobjects where name ='SOP10200')
Begin
print 'Allocated Fulfillment Orders in Sales Order Processing'
select SOPNUMBE,ITEMNMBR,ITEMDESC,ATYALLOC from SOP10200 where ITEMNMBR = @ITEMNMBR and ATYALLOC > 0 and SOPTYPE = 6
end
Print ''
print 'Allocated Returns in Purchase Order Processing'
print '==============================================================='
print ''
if exists (select * from sysobjects where name ='POP10500 and POP10310')
Begin
select A.POPRCTNM,A.ITEMNMBR, QTYRESERVED,* from POP10500 A join POP10310 B
on A.POPRCTNM=B.POPRCTNM AND A.RCPTLNNM=B.RCPTLNNM AND A.ITEMNMBR=B.ITEMNMBR
where A.ITEMNMBR = @ITEMNMBR and A.QTYRESERVED > 0
end
select A.POPRCTNM,A.ITEMNMBR, QTYRESERVED,* from POP10500 A join POP10310 B
on B.POPRCTNM=B.POPRCTNM AND A.RCPTLNNM=B.RCPTLNNM AND A.ITEMNMBR=B.ITEMNMBR
where A.ITEMNMBR = @ITEMNMBR and A.QTYRESERVED > 0
Print ''
print 'Allocated documents in Inventory'
print '==============================================================='
print ''
if exists (select * from sysobjects where name ='IV10001')
Begin
select IVDOCNBR,ITEMNMBR,TRXQTY from IV10001 where ITEMNMBR = @ITEMNMBR and TRXQTY < 0
select IVDOCNBR,ITEMNMBR,TRXQTY from IV10001 where ITEMNMBR = @ITEMNMBR and TRXQTY > 0 and IVDOCTYP = 3
end
print ''
print 'Allocated Invoices in Invoicing'
print '==============================================================='
print ''
if exists (select * from sysobjects where name ='IVC10101')
Begin
select INVCNMBR,ITEMNMBR,QUANTITY from IVC10101 where ITEMNMBR = @ITEMNMBR and DOCTYPE = 1
end
print ''
print 'Allocated Assembly documents in Bill of Materials'
print '==============================================================='
print ''
if exists (select * from sysobjects where name ='BM10300')
Begin
select TRX_ID,ITEMNMBR,ATYALLOC from BM10300 where Component_ID <> 0 and ITEMNMBR = @ITEMNMBR and ATYALLOC > 0
end
Print ''
print 'Allocated Orders in Service Call Management'
print '==============================================================='
print ''
if exists (select * from sysobjects where name ='SVC00203')
Begin
select CALLNBR,ITEMNMBR,ATYALLOC,LOCNCODE from SVC00203 where ITEMNMBR=@ITEMNMBR and LINITMTYP='P' AND ATYALLOC <> 0
end
select CALLNBR,ITEMNMBR,ATYALLOC,LOCNCODE from SVC00203 where ITEMNMBR=@ITEMNMBR and LINITMTYP='P' AND ATYALLOC <> 0
print 'Allocated Service Call Transfers'
print '==============================================================='
print ''
if exists (select * from sysobjects where name ='SVC00701')
Begin
select ORDDOCID,ITEMNMBR,TRNSFQTY from SVC00701 where ITEMNMBR=@ITEMNMBR and TRNSFQTY <> 0
end
print 'Allocated Service Call Transfers (serial)'
print '==============================================================='
print ''
if exists (select * from sysobjects where name ='SVC00702')
Begin
select ORDDOCID,ITEMNMBR,SERLTQTY from SVC00702 where ITEMNMBR=@ITEMNMBR and SERLTQTY <> 0
end
print 'Allocated RTV lines'
print '==============================================================='
print ''
if exists (select * from sysobjects where name ='SVC05601')
Begin
select RTV_Number,ITEMNMBR, QUANTITY, * from SVC05601 where
ITEMNMBR=@ITEMNMBR and RTV_Status=2 and CUSTOWN=0 and Transfer_Reference=''
end
select RTV_Number,ITEMNMBR, QUANTITY, * from SVC05601 where
ITEMNMBR=@ITEMNMBR and RTV_Status=2 and CUSTOWN=0 and Transfer_Reference=''
print 'Allocated Documents in Depot Management'
print '==============================================================='
print ''
if exists (select * from sysobjects where name ='SVC06100')
Begin
select WORKORDNUM,IBITEMNUM,QUANTITY,LOCNCODE from SVC06100 where IBITEMNUM=@ITEMNMBR AND WORECTYPE = 2 AND QUANTITY <> 0
End
if exists (select * from sysobjects where name ='SVC06101')
Begin
select WORKORDNUM,ITEMNMBR,ATYALLOC,LOCNCODE from SVC06101 where ITEMNMBR=@ITEMNMBR AND WORECTYPE = 2 and ATYALLOC <> 0
end
if exists (select * from sysobjects where name ='SVC06120')
Begin
select WORKORDNUM,ITEMNMBR,SERLTQTY from SVC06120 where ITEMNMBR=@ITEMNMBR AND WORECTYPE = 2 AND SERLTQTY <> 0
End
print ''
print 'Allocated Documents in Project Accounting'
print '==============================================================='
print ''
if exists (select * from sysobjects where name ='PA10901')
Begin
select PAIV_Document_No,ITEMNMBR,PABase_Qty,LOCNCODE from PA10901 where ITEMNMBR=@ITEMNMBR and
PABase_Qty <> 0 and PAIV_Transfer_Type = 1
end
print ''
print 'This is the (general) allocated or pending issue quantity in the MOP1400 for this Manufacturing Order'
print '==============================================================='
print ''
if exists (select * from sysobjects where name ='MOP1400')
Begin
select MANUFACTUREORDER_I, ITEMNMBR, ATYALLOC from MOP1400 where ATYALLOC > 0 and ITEMNMBR = @ITEMNMBR
end
print 'This is the (bin) allocated quantity in the MOP1900 for this Manufacturing Order'
print '==============================================================='
print ''
if exists (select * from sysobjects where name ='MOP1900')
Begin
select MANUFACTUREORDER_I, ITEMNMBR, ATYALLOC, LOCNCODE, BIN from MOP1900 where ATYALLOC > 0 and ITEMNMBR = @ITEMNMBR
end
print 'This is the (lot) allocated or pending issue quantity in MOP1020 for this Manufacturing Order'
print '==============================================================='
print ''
if exists (select * from sysobjects where name ='MOP1020')
Begin
select MANUFACTUREORDER_I, ITEMNMBR, SERLTNUM, FROM_SITE_I, DOCNUMBR from MOP1020 where ITEMNMBR =@ITEMNMBR
end
print 'Pending transactions that can hold allocations'
print '==============================================================='
print ''
if exists (select * from sysobjects where name ='MOP1200 and MOP1020 and MOP1025 and MOP1026')
Begin
select * from MOP1020 where DOCNUMBR in (select PICKNUMBER from MOP1200 where POSTED=1)
select * from MOP1025 where MOPDOCNUM in (select PICKNUMBER from MOP1200 where POSTED=1)
select * from MOP1026 where MOPDOCNUM in (select PICKNUMBER from MOP1200 where POSTED=1)
end
ITEMNMBR LOCNCODE QTYONHND ATYALLOC
------------------------------- ----------- --------------------------------------- ---------------------------------------
273013 3.00000 0.00000
273013 EN99-DN 0.00000 0.00000
273013 FG15 0.00000 0.00000
273013 M100 3.00000 0.00000
273013 PR002 0.00000 0.00000
273013 PR022 0.00000 0.00000
273013 SC1 0.00000 0.00000
(7 row(s) affected)
ITEMNMBR LOTNUMBR QTYONHND ATYALLOC
------------------------------- --------------------- --------------------------------------- ---------------------------------------
273013 20140515 2.00000 2.00000
273013 20141217 1.00000 1.00000
(2 row(s) affected)
ITEMNMBR LOCNCODE BIN QUANTITY ATYALLOC
------------------------------- ----------- --------------- --------------------------------------- ---------------------------------------
(0 row(s) affected)
ITEMNMBR TRXLOCTN QTYONHND QTYRESERVED
------------------------------- ----------- --------------------------------------- ---------------------------------------
273013 M100 2.00000 0.00000
273013 M100 1.00000 0.00000
(2 row(s) affected)
Tami,
For one of these items (substitute your number for <TAMIS ITEM>) could you run this SQL and reply with results?
DECLARE @ITEMNMBR CHAR(31)
SELECT @ITEMNMBR='<TAMIS ITEM>'
SELECT ITEMNMBR,LOCNCODE,QTYONHND,ATYALLOC FROM IV00102 WHERE ITEMNMBR=@ITEMNMBR
SELECT ITEMNMBR,LOTNUMBR,(QTYRECVD-QTYSOLD) QTYONHND,ATYALLOC FROM IV00300 WHERE ITEMNMBR=@ITEMNMBR AND (QTYRECVD-QTYSOLD)>0
SELECT ITEMNMBR,LOCNCODE,BIN,QUANTITY,ATYALLOC FROM IV00112 WHERE ITEMNMBR=@ITEMNMBR
SELECT ITEMNMBR,TRXLOCTN,(QTYRECVD-QTYSOLD) QTYONHND,QTYRESERVED FROM IV10200 WHERE ITEMNMBR=@ITEMNMBR AND RCPTSOLD=0 ORDER BY RCTSEQNM
Thanks
Tim
We do not use expiry date...the QTYs show available in the Inventory Quantities/ sites look up...when I use Inventory adjustment ..GP tells me they are unavailable??
Inventory Control is set to use expired lots in adjustments and transfers and other transactions. Auto assign lots is based on receipt date
Hi Tami,
Have you checked the Inventory control setup ?
Microsoft Dynamics GP menu >> Setup >> Inventory >> Inventory Control
This is taken from the Inventory / BoM user guide :
...
Manual and automatic serial and lot number assignment in Bill of Materials
For stock components, the serial and lot numbers are assigned automatically when the items are allocated—that is, when the status of the assembly transaction is changed to Released. Serial numbers are assigned based on the valuation method and stock quantities of each component. Lot numbers are assigned automatically by receipt date or by expiration date, depending on how you selected to automatically assign lot numbers in the Inventory Control Setup window.
If lot numbers are assigned automatically by expiration date, lot numbers with expiration dates that haven’t expired are used. Lot numbers without an expiration date aren’t assigned.
...
Really....so on the inventory inquiry screen the item shows 3 available...then when we go to issue them, allocate or adjust them out...the lots are unavailable...in the manufacturing when using the component transaction window....or MO receipt ...it says lots have expired??
I have run the reconcile on the whole inventory...
It is not specific to one item.
#1
André Arnaud de Cal...
294,157
Super User 2025 Season 1
#2
Martin Dráb
232,930
Most Valuable Professional
#3
nmaenpaa
101,158
Moderator
Share
Report
All responses (
Answers (
