Finding the DMRHead record creation date

Epicor ERP 10
1

So a little background, first.

We have an on-prem instance of Epicor and my job (amongst others) is building/expending our own internal BI platform.

This includes a lot of delicate Epicor DB queries.

Recently, we got to thinking - it would be REALLY handy to be able to know all our exact inventory levels for any given point-in-time, and that made me think even more.

Couple weeks later and I came up with an algorithm that scans the PartTran table for each given part and “replays” all the actions - carefully tracking each part that was removed, and each part that was placed into inventory.

Surprisingly, it actually works… for the most part.

The only issue - the only parts that are “incorrect” when compared to current inventory levels, are parts that went into DMR and… well… they never came out.

In other words, I have a few dozen parts that were placed into DMR and - although Epicor removed them from inventory, there is no actual record of it in the PartTran history table.

What I look for is an entry in the PartTran table that has TranType of “DMR-REJ”. I then take the TranQty and subtract that from the bin.

But here is where things get hairy. You see, when a part is placed into inventory - there is an entry in the PartTran table. When a part is taken out for a sales order, or an inspection, or pretty much ANY other task… there is a corresponding entry created on-the-spot in the PartTran table.

But the instant that a DMR is created for a part - although Epicor REMOVES (de-incrementing) the part from the PartBin table accordingly, it DOES NOT create an entry in the PartTran table. In fact, it seems that a part may get DMR’d on a Monday - but no mention of this is in the PartTran table until the part is rejected.

There have also been a couple fringe cases where a DMR was cancelled and the part was placed back into inventory - which also throws off my logic.

It seems to me that any time the PartBin table is manipulated (parts removed or added), there should ALWAYS be an entry in PartTran to log the instant that part left “available inventory”.

So I dug deeper and realized: maybe I can query the DMRHead table and see when the DMR was created. Well, easier said than done. Even more ironically, the DMRHead table HAS NO CREATION timestamp! As it turns out, we have a handful of DMR’s that were started, but no action was actually taken against the DMR. (In other words, there is a DMRHead record, but nothing in DMRActn) Even though Epicor already took the parts out of inventory.

Another issue that I uncovered (and I realize this is largely user-error on behalf of the sales and warehouse guys that use Epicor) is this: sometimes 10 parts may get removed from inventory for DMR. All 10 are actually removed from the Bin and sent back for RMA… but only 5 are “Rejected”. This further skews my calculations.

Again, I understand a lot of this is the result of incorrect end-user-usage of the product. Nonetheless, if there is a reliable way I can query around this, that would be great.

Out of 60k+ parts, I have only ran into this issue on less than 200. Not too bad, but I would like to zero in on this a bit more.

So my questions are:

  1. How can I reliably query to see exactly when any given DMR was created? Date/time preferred. If only a date is available, I will take what I can get.
  2. Am I looking at the PartTran table wrongly? Currently, my algorithm says “INS-DMR” is “Adding a part to the specified bin”, and “DMR-REJ” is taking it back out. Seems there should be some other entry that I am missing? Seems Epicor has a hole in how it logs the data to PartTran? Seems to me, any time any QTY of parts is added or removed from PartBin, that exact amount should always be reflected in PartTran.
  3. TranDate seems to be Workstation date/time - correct? SysDate and SysTime is the date/time of the server - correct?
  4. The SysTime fields are seconds-since-midnight - correct? This is what I assumed from looking at the data.

If I could just reliably know each date/time every DMR is created, I can then join that data into my “PartTran replay logic” and - based on the DMRHead.TotDiscrepantQty value, I could use THAT as the “point-in-time” a DMR’d part was removed from the PartBin records.

Any help would be greatly valued!

2

This can’t be the case as parttran records are what drives Part in records. In fact, if your PartBin Qtys get out of whack, the rebuild process rebuilds the PartBin records based on parttran records.

3

Use the parttran history tracker to see what happens to the QOH (and what sign to use) for each trantype

4

Interesting…

So here is what seems to be happening - and please bear in mind: 99% of my Epicor experience is strictly from the back-end (writing reports and BI mining)…

So, when things are working correctly… I am assuming:

A part comes into the DMR bin (INS-DMR)
A part then is taken back out when I see a DMR-REJ

But there are a handful of DMR instances where there is NEVER a DMR-REJ entry in PartTran.

In almost every one of these instances, I can head to the DMRActn table (based on DMRNum referenced in the PartTran notes) and… nothing. DRMHead.OpenDMR is “0” -

The DMR was closed.
The part was de-incremented from PartBin.
No DMR-REJ was ever logged.
No records ever created in DMRActn (although, there IS a DMRHead record).

So… I assume this is a case of the PartBin table is “out of whack”?

5

Sorry for the previous brief answers. I was on my phone.

You’ll probably need to add a UD field and set it with a BPM

Use the Part Tran History Tracker to view a part with various tran types. Make sure you sort by TranNumber, else the running balance column won’t make sense.

TranDate and SysDate both use the servers time. SysDate is the real date when the transaction actually happened. TranDate is the date the user told the system to use. Like if you enter a receipt on 3/15, but with a receipt date of 3/14.

Correct. SysTime / 86400 gives you the fraction of the day (like how Excel stores time)

6

You can check if the PartBin is out of whack by running Rebuild PartBin from Part Trans. There is a Report only mode that will just report any findings, vs actually making changes.

We don’t have the Quality module and thus can’t do DMR’s. So I don’t have experience with exactly how partrans related to those work.

When you say, “DMR Bin” is that an actual Bin that is represented in the PartBin table? If it is a real Bin, is it marked as Non-nettable?

And just so you know, If a partbin’s OnhandQty field goes to zero, the record is deleted. So you can’t just assume that a part that currently has a zero QOH will have a partbin record to start your calculations from.

7

Just took a closer look through the Part Transaction History tracker (from Epicor UI) and may have noticed my error.

For each INS-DMR, the QOH never moves. I have been incrementing the “Bin of which the DMR part is placed into”, then expecting to find a matching DMR-REJ - which is not always the case.

I suppose when I am “replaying” the transactions, I should be ignoring the INS-DMR and DMR-REJ entries.

Again, my only goal here is the ability to show the stakeholders exact inventory levels for a given date/time.

8

If you search this site, you’ll find a list of the sign (-1, 0, or 1) to use as a multipler for each transaction type.

edit

see this post. It’s probably not all the trantypes…

9

I am digging in right now…

Would you believe I have been writing reports for 2 years and never once actually USED Epicor?

This is something that was just kind of lumped on my plate and I have done the best I could so far. I just found this site/forum today and was not even aware there were a community of people hacking on the product to be honest.

My entire mining program that I wrote was based strictly on some excel exports of the PartTran tool that someone else sent me - and my own digging around the DB.

Let me research the non-nettable thing and look into the signing documentation.

So far, you have answered the most crucial bit of info that was bothering me: YES, the PartTran table should be considered the final authority as to EXACTLY what went where, and how many of each.

Let me dig in for a few minutes and I will report back.

Thanks a million! I will let you know what I figured out.

10

An incredible useful tool a user on here built is the whole ERP schema in a Windows Help file.

Worth it’s weight in unobtanium!

11

SCORE!!!

Man, this is the motherlode. Seriously…

12

I think I just about got this figured out - refactoring my code now.

Quick questions:

I found this answer to “calculating running signed qty” as such:

(case  
    when PartTran.TranType IN ('STK-ASM', 'STK-CUS', 'STK-INS', 'STK-KIT', 'STK-FAM', 'STK-MTL', 'STK-PLT', 'STK-STK', 'STK-UKN', 'STK-DMR') then -1 
    when PartTran.TranType IN ('ADJ-CST', 'ADJ-QTY', 'AST-STK', 'DMR-STK', 'INS-STK', 'MFG-STK', 'PLT-STK', 'PUR-STK', 'STK-STK', 'SVG-STK')  then 1 
    else 0
end) * PartTran.TranQty

So now I have an additional column that I can indiscriminately add to a running total as I loop each record.

But I noticed the STK-STK was listed in TWO “WHERE IN” clauses… Should STK-STK be a negative or positive signing value?

13

Just to be clear, here is my entire query:

                SELECT
                part_tran.TranDate,
                part_tran.SysDate,
                part_tran.SysTime,
                part_tran.TranNum,
                part_tran.BinNum,
                part_tran.BinNum2,
                part_tran.TranType,
                part_tran.TranQty,
                part_tran.ActTranQty,
                part_tran.AfterQty,
                (case  
				    when part_tran.TranType IN ('STK-ASM', 'STK-CUS', 'STK-INS', 'STK-KIT', 'STK-FAM', 'STK-MTL', 'STK-PLT', 'STK-STK', 'STK-UKN', 'STK-DMR') then -1 
				    when part_tran.TranType IN ('ADJ-CST', 'ADJ-QTY', 'AST-STK', 'DMR-STK', 'INS-STK', 'MFG-STK', 'PLT-STK', 'PUR-STK', 'STK-STK', 'SVG-STK')  then 1 
				    else 0
				end) * part_tran.TranQty AS SignedTranQty,
				
                part_tran.TranReference,
                part_tran.MtlUnitCost,
                part_tran.EntryPerson,
                part_tran.PONum,
                part_tran.POLine,
                part_tran.PackNum,
                part_tran.PackLine,
                (SELECT TOP 1 rd.PackSlip FROM Erp.RcvDtl rd WHERE part_tran.PONum=rd.PONum AND part_tran.POLine=rd.POLine) as PackSlip,
                part_tran.OrderNum,
                part_tran.OrderLine,
                part_tran.OrderRelNum,
                part_tran.InvoiceNum,
                part_tran.InvoiceLine,
                part_tran.JobNum,
                part_tran.JobSeq,
                job_prod.OrderNum AS JobProd_OrderNum,
           		job_prod.OrderLine AS JobProd_OrderLine
            FROM
                Erp.PartTran part_tran
            LEFT JOIN
                Erp.Warehse warehouse ON part_tran.WareHouseCode = warehouse.WarehouseCode
            LEFT JOIN
                Erp.Plant plant ON part_tran.Plant = plant.Plant
            LEFT JOIN
            	Erp.JobProd job_prod ON part_tran.JobNum = job_prod.JobNum
            WHERE
                part_tran.PartNum = 'SomePartNumberHERE'
            ORDER BY
            	part_tran.TranNum ASC

I start a running QTY of “0”. For each iteration, I add whatever value is assigned SignedTranQty, so:

RunningQTY += SignedTranQty

This is the correct logic, yes?

Oh and… the Before and After QTY fields seem rather useless, no?

14

Updated query…

Turns out, years ago - when we were on a considerably older version of Epicor, the TranNum was NOT unique.

It incremented for each part for each day. So the older data from before 2008 cannot be sorted on TranNum. Instead, I must order like so:

part_tran.SysDate ASC,
part_tran.SysTime ASC,
part_tran.TranNum ASC

That guarantees correct order for some of the older parts.

Strange enough, the history tracker tool in the Epicor UI even shows the transactions out-of-order due to this issue. Likely an Epicor bug?

Sorting by all 3 in that order looks to show correct order for ALL parts I have checked thus far.

15

I swear I am going to bed now…

Adjusted the CASE for signing as follows:

                (case
				    when part_tran.TranType IN ('STK-ASM', 'STK-CUS', 'STK-INS', 'STK-KIT', 'STK-FAM', 'STK-MTL', 'STK-PLT', 'STK-UKN', 'STK-DMR') then -1
				    when part_tran.TranType IN ('ADJ-CST', 'ADJ-QTY', 'AST-STK', 'DMR-STK', 'INS-STK', 'MFG-STK', 'PLT-STK', 'PUR-STK', 'STK-STK', 'SVG-STK')  then 1
				    else 0
				end) * part_tran.TranQty AS SignedTranQty,

Seems to reconcile perfectly to current inventory levels so far.

Writing a test to loop through 10k parts and will compare the final running total against inventory levels in order to verify accuracy.

The really slick part of this that I am writing is: it not just provides point-in-time snapshots of inventory, but does so by-bin. I also have simplified the view via a nice HTML5 render that shows a “human readable” version of the transaction history. Very simplified. Will report back tomorrow sometime.

Thanks again!

16

First off, that duplication of the trantype STK-STK was probably a copy and paste error, and should only be in one of the two WHEN lists.

The way the Stock Status Report (which gives you the QOH for a specified date) works, is by starting with the QOH (qty’s in bins) and subtracting out the Part trans after the desired date. Typically, this will be faster as more often than not, you’re more interested in more recent dates.

So if there were 10 years of part trans, a part had 10 transactions per day, and yu wanted to know what the QOH was 1 month ago, you’d have over 36,000 records to apply your calculation on (to get the multiplier sign). Where as starting with todays QOH and working backwards it would just be 300 records.

And I really doubt the parttran number is not unique. Even if older versions did use a composite key of PartNum and TranNum, when that data was brought into E10, they would have been given new tran and unique numbers. Now maybe that process didn’t put all those new transactions in date order, but they would be for any specific partnum.

17

What I am doing for this business intelligence tool, is I have built a data warehouse.

I de-normalize a LOT of data for faster reports (much faster than trying to query the Epicor DB directly). Raw MSSQL queries and lot’s of crunching on the back-end, from there: I am placing the highly de-normalized data into a combination of MySQL and Elasticsearch. Bad data aside, my de-normalized data setup allows me to quickly provide a point-in-time inventory report (entire inventory, by-bin) in less than 2 seconds. That includes the rendering!

We have a total of 248k PartTran records, and yup… I process them all (initially). Most of my data mining is incremental and such is the case here - I will only be re-mining the PartTran records that have changed since my last sync.

Regarding the duplicate PartTran number: yes. Must have been before my time. But here is a sample:

SELECT COUNT(*) FROM PartTran;
Result: 248,040

SELECT COUNT(DISTINCT(TranNum)) FROM PartTran;
Result: 166,819

It looks like around up-until late 2009, the way Epicor (at least on our instance) stored the TranNum was:
Each time a new transaction for a given part happened, the “unique key” was:
(‘SysDate’,‘SysTime’,‘TranNum’).

The TranNum would start from 1, and increment by 1 each additional transaction… each day.

Furthermore, we have many instances where TranDate and SysDate are NOT the same. They are, in fact… YEARS apart. I have a couple hundred records where the difference between the two are more than 7 years apart. These are OLD PartTran records (dating back several years by themselves), but I asked around to some who were around the company when this would have happened and was told:

“We once had some workstations with incorrect dates set on the users computers, and Epicor also had several bugs that they had to fix at that time - even sent a specialist out to fix a few things on a handful of occasions”

So that is what led me to believe that the TranDate comes from the workstation. Regardless, the only reliable date and time fields I seem to have - at least for older data - are SysDate and SysTime.

Our company has been on Epicor since around 2005 if I recall correctly. At least that is how far back the PartTran log goes.

We are currently on Epicor version 10.5.500.13.

The only unique key on the PartTran table is SysRowID.

Bossman says we are due for a HUGE upgrade later this year.

18

I got the STK-STK thing working, too.

I ran the new logic on 10K parts dating back from 2005 and - except for a couple - all reconciled perfectly.

19

There are two major reasons for TranDate being different than the SysDate.

  1. The data entered did not match the date is was entered on. If I enter a PO Receipt on Monday - but back date it to last Friday (when the parts actually came in), the TranDate would be friday’s date, while the SysDate would be todays date.

  2. Fat fingers while entering dates. I’d bet that we have tran dates that are centuries apart from the system date (like enter 1/15/2221 when you meant 1/15/2021).