Adding DMR Number to Material Tag Report

e10

(Adam Nofsinger) #1

It seems that the Material Tags report is used for several different material tag types in Epicor, and the dataset is pretty limited and dynamically generated (not based on any actual table in Epicor). It seems impossible to link the limited data Epicor generates and passes to the RDD (MtlTags) to a DMR.

I opened a ticket with Epicor support but they basically referred me to professional services - understandable I guess.

Has anyone ever been able to successfully add the DMR Number to the INS-DRM Material Tags you can print out of Epicor?


(tyler fenwick) #3

Modify your MtlTag Dataset in the SSRS report based on the last field in the query below with the (select statement) and try it. I recently modified my MtlTag while working on an autoprint method to include the Material Queue Sequence id in relation to the NonConformance Tran ID. And the method below worked since where dealing with a temporary table being generated for the MtlTag dataset.

**As a note, I don’t like doing the method below unless I absolutely have to LOL

Blockquote ="
SELECT
T1.[AsmSeq]
,T1.[TagNum]
,T1.[BCAsmSeq]
,T1.[BCBinNum]
,T1.[BCJobNum]
,T1.[BCLotNum]
,T1.[BCOprSeq]
,T1.[BCPartNum]
,T1.[BCWhseCode]
,T1.[BinNum]
,T1.[ItemQty]
,T1.[JobNum]
,T1.[LaborNote]
,T1.[LotNum]
,T1.[NCComment]
,T1.[NonConfTranID]
,T1.[OpCode]
,T1.[OprSeq]
,T1.[PartNum]
,T1.[PartDesc]
,T1.[POLine]
,T1.[PONum]
,T1.[PORel]
,T1.[Reason]
,T1.[ReasonDesc]
,T1.[ResGrpID]
,T1.[TagFormat]
,T1.[TagTitle]
,T1.[UM]
,T1.[VendID]
,T1.[VendName]
,T1.[WhseCode]
,T1.[QtyNum]
,T1.[LegalNumber]
,T1.[JobSeqType]
,T1.[Revision]
,T1.[Company]
,T1.[MtlSeq]
,T1.[PurPoint]
,T1.[RefAsmSeq]
,T1.[RptUserID]
,T1.[UM_UOMSymbol]
,T1.[VendorNum]
, (SELECT ISNULL(DMRNUM,0000) FROM [EPICORTEST].[ERP].[DMRHEAD] (NOLOCK)
WHERE PONUM = CONVERT(INT,T1.PONUM)) as ‘’ FROM MtlTags_" + Parameters ! TableGuid.Value +
" T1 "


(Greg Celentano) #4

I’ve had problems with this too. Because I believe the material tag is used in a lot of places is what makes it difficult. I had to do a query similar to the one above.


(Brad Boes) #5

I have duplicated the report into a new style and added the needed DMR info so it can be done.

Not something I can share as it is the clients.

Brad


(Adam Nofsinger) #6

Brad,

Would it be acceptable to just share how you did the JOIN from what is in the default RDD to DMRHead (assuming DMRHead is where you get the DMR Number)?

Did you just add DMRHead table and then join based on PONum, or is it more complicated than that?


(Brad Boes) #7

Adam,

Here is a snapshot of the RDD.

MtlTag to NC, NC to DMR, DMR to DMR Action.

The part tables shown are for other unrelated things.

FYI

Brad