I am attempting to create a rather complex report that will key off of the Item Lot Master table. IV00300
The problem that I have is that once the lot number for a specific item has been fully consumed (Sold) the record is removed from the IV00300 table.
Is there a History table that it get written to? Is so, I sure cannot find it.
Thanks,
Andy
*This post is locked for comments
Hello! Did you ever make any progress with this? I too am trying to get historical lot data related to sales, and while I can at least find the data I am looking for, I cannot get it out of SQL or CR. Any scripts you can share would be greatly appreciated, as it may trigger a piece I am missing?
Thanks in advance!
> Is there a History table that it get written to?
No, there is not (at least not up through GP2013!). My company relies on information from that same table for reports, so we added a Trigger to the IV00300 table to immediately copy the Deleted record into a custom Historical table.
Below is the SQL code to do so (keep in mind that if you should upgrade GP or rebuild GP's core tables, you will have to add this trigger again):
Share
Report
All responses (
Answers (
ALTER TRIGGER [dbo].[ArchiveDeletedIV00300Record]
ON [dbo].[IV00300]
AFTER DELETE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
INSERT INTO [RFALP].[dbo].[tblIV00300History]
([ITEMNMBR],[LOCNCODE],[DATERECD],[DTSEQNUM],[LOTNUMBR],[QTYRECVD],[QTYSOLD],[ATYALLOC],[UNITCOST],
[RCTSEQNM],[VNDRNMBR],[LTNUMSLD],[QTYTYPE],[BIN],[MFGDATE],[EXPNDATE], HISTORYDATE)
SELECT [ITEMNMBR],[LOCNCODE],[DATERECD],[DTSEQNUM],[LOTNUMBR],[QTYRECVD],[QTYSOLD],[ATYALLOC],[UNITCOST],
[RCTSEQNM],[VNDRNMBR],[LTNUMSLD],[QTYTYPE],[BIN],[MFGDATE],[EXPNDATE], getdate() from deleted
END
The Serial/Lot Tracking tables in SOP, POP, Bill of Materials, Inventory, Field Service store just about the same amount of information as the Serial/Lot Master in IV00300. Basically the majority (if not all) of the information from the Serial/Lot line in IV00300 is copied into the Serial/Lot tables for the transactions.
SOP -- SOP10201
POP -- POP30330
Bill of Materials -- BM30400
Field Service (RMAs) -- SVC05255 (open) SVC35255 (history)
Field Service (RTVs) -- SVC05602 (open) SVC35602 (history)
I hope this helps.
-Joe
Richard,
Unfortunately you are not exactly correct. The IV30400 table only records the history on Inventory transactions when Lot/serial numbers are used. Sales transactions are not reflected in this table. Also is does not contain the same information as the IV00300 table.
What I am needing is a historical table that has the same data as the IV00300. Unlike every other aspect of GP, it does not exist that I can find.
Andy
Of course there is. It is the Item Serial and Lot Number History table or IV30400. This information can be found in our Information Flow and Posting book, you should get a copy. It helps you find tables for reports!
Are you coming to Convergence in Atlanta? If so our books will be on display at booth 1646 and in the Convergence Store! If not, check out our web site below
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,280
Super User 2024 Season 2
#2
Martin Dráb
230,235
Most Valuable Professional
#3
nmaenpaa
101,156