I’m currently working up a BAQ where one of the requirements is to track when DMRs are open and closed (primarily for a “DMR Aging” report [i.e. - “how many days open”]).
I see that there is no “open date” or “closed date” fields within the [Erp.DMRHead] table (and records are written to this table precisely when a DMR is “initiated”). The [Erp.DMRActn] table is merely a tracking of records that illustrate what actions are taken on a specific DMR; and those actions have nothing to do with “opening”, or “closing” a DMR. The [Erp.PartTran] table provides some clues that I might be able to leverage (such as the [Erp.PartTran.TranDate] and a [Erp.PartTran.TranType] of “INS-DMR” to perhaps indicate when a DMR has been opened), but I am not confident that I would be referencing this data accurately.
Might anyone have a good strategy for analyzing/defining when a DMR has been opened/closed for use in DMR aging reporting? What data points should be used?
Hi, yes parttran for DMR open date, probably part tran for closing. But dmrs can be manually closed.
Probably best to bpm/data directive on closing to a field.
Hi Bill,
you are right TranDate or SysDate is your DMR open date for TranType INS-DMR, and as @enbw mentioned you need to create a UD field on DMRHead to save the date when closing DMR method triggered, i have already done this for the same purposes, and created a BAQ/Dashboard to get all needed information to built the KPI metrics/Reports for our Quality Department
Thanks for the clarification on DMR open/close status. I like the idea of creating a BPM to populate a UD field on [DMRHead] with the close date. That seems a lot easier than trying to piece together a BAQ that checks when all [PartTran.TranQty] values equal the [DMRHead.TotDiscrepantQty] value to determine if a DMR should be considered closed.
I’ll definitely look into building that up and giving it a good test. I’ll try to follow-up here with my results in a few days.
Thanks very much for being incredibly generous, Al. I was just about to reply to the thread to ask you what XML tag in the trace log would indicate that the DMR has changed from open to closed. There seems to be a lot of interesting information about the part transaction under “Erp.BO.DMRHistoryDataset” and “Erp.TableSets.DMRHistoryTableset”, but nothing is specifically jumping out at me (could merely be poor eyesight).
From your BPM example, it looks like I didn’t set up the logging correctly (or didn’t think the matter through properly), perhaps - as I see it’s a simple monitoring of the changed value in [DMRHead.OpenDMR]. Makes perfect sense.
Curious though… why the additional step to check the field a second time for a value change from “false” to “true”? Might that be in case a data entry person discovered a mistake in closing the DMR and decided to delete his/her previous effort to close it - thereby, reversing the status of the DMR back to “open”?
you more than welcome mate, i do not mind sharing my work at all, i have learned a lot from all of you on this Forum guys, so lot of thanks to you as well,
with regards to second step is to cover the case when user reopen the DMR as Close date will be irrelevant on this case, so the BPM will clear it and update the relevant one when DMR get closed again
Perfect, Al. Thorough logic there - much appreciated.
I’m working now on creating the UD field on the [DMRHead] table. I’ll let you know how things progress for me. Thanks again for the helping hand and sharing some detailed information.
it is worth to mention that obviously, this solution won’t work for historical data, i.e. Null value in Closed Date for all DMR records that have been closed prior to enable this BPM, so you may need to cater for this in your BAQ if you intend to calculate the difference in time between the two dates within it.
I wanted to thank you again for your example and provide a bit of an update for you.
Using the information you had offered concerning BPMs on the DMRHead table, I was able to recreate that in my environment and it was rather easy to implement. I’m sure Epicor has specific reason for not adapting those important data points directly in the DMRHead table (I can’t guess why open/close dates would not be important enough to track), but having them readily accessible has given me exactly what I need to provide insight to the Quality team on simple things like “Open DMR” reporting.
you more than welcome mate, glad to hear that, it is you who created this smart, professional, efficient, and well presented report, analysing and using ideas is what counts.
