Job # & Serial Number for RMA

Hello~!

I am trying to create an auto print label that gives me the Job number and Serial Number of a part when someone receives the line in the RMA process.,

The first thing I am doing is just seeing if I can get the query correct, so I am experimenting with SSRS without auto print. I have a modified RMAForm RDD that I am working with.,

Two issues:

  1. I am having trouble bringing in the Serial Number of the part.

(Neither Definition Only or Output work)

  1. I want to connect the RMA to the Job. I have an Order set up that is tied to the job with the RMA number in the line. But I am having trouble figuring out what table/field to bring in and attach. And if I am not successfully bringing in SerialNo, then I doubt I will be successful here either. There is a JobNum in RMADtl but it’s blank – when I try to add the Job Number to the line, it gives me a “Invalid Job Material Data” error when I try to save it.

My end goal is to have a label printed out with Job Number and Serial Number on it when someone receives the RMA part.

Here is the query:

="SELECT T1.RptLanguageID,CAST( T1.BTCustNum as nvarchar ) as BTCustNum,T1.Company,CAST( T1.CustNum as nvarchar ) as CustNum, T1.RMADate,CAST( T1.RMANum as nvarchar ) as RMANum,CAST( T1.ShipToCustNum as nvarchar ) as ShipToCustNum,T1.Calc_ReturnPhoneNum,T1.Calc_ReturnFaxNum,T1.Customer_FaxNum,T1.Customer_EMailAddress,T1.Calc_CustomerAddress,T1.Calc_ReturnAddress,T1.Customer_BTName,T1.Customer_CustID,T1.Customer_Name, T2.Company as RMADtl_Company,CAST( T2.ConNum as nvarchar ) as RMADtl_ConNum,CAST( T2.CustNum as nvarchar ) as RMADtl_CustNum,T2.Note,CAST( T2.OrderRelNum as nvarchar ) as OrderRelNum,T2.PartNum,CAST( T2.RefInvoiceLine as nvarchar ) as RefInvoiceLine,CAST( T2.RefInvoiceNum as nvarchar ) as RefInvoiceNum,T2.ReturnQty,T2.ReturnQtyUOM,CAST( T2.RMALine as nvarchar ) as RMALine,CAST( T2.RMANum as nvarchar ) as RMADtl_RMANum,CAST( T2.ShipToCustNum as nvarchar ) as RMADtl_ShipToCustNum,T2.Calc_SerialNo,T2.ShipToNum,CAST( T2.Calc_SerialNoFlag as nvarchar ) as Calc_SerialNoFlag,T2.OrderNum_CurrencyCode,T2.RevisionNum,T2.PartNum_PartDescription,CAST( T2.PartNum_TrackDimension as nvarchar ) as PartNum_TrackDimension,CAST( T2.PartNum_TrackLots as nvarchar ) as PartNum_TrackLots,CAST( T2.PartNum_TrackSerialNum as nvarchar ) as PartNum_TrackSerialNum,T2.Reason_Description,T2.Calc_LineDesc, T2.Calc_ReasonDescription, T2.Calc_LegalNumber,T2.Calc_AttributeSetShortDescription, T3.RMANum as SerialNo_RMANum,T3.PartNum as SerialNo_PartNum,T3.PackLine as SerialNo_PackLine,T3.Company as SerialNo_Company,T3.SerialNumber as SerialNo_SerialNumber
          FROM RMAHead_" + Parameters!TableGuid.Value + " T1
          LEFT OUTER JOIN RMADtl_" + Parameters!TableGuid.Value + " T2
          ON T1.RMANum = T2.RMANum
          LEFT OUTER JOIN SerialNo_" + Parameters!TableGuid.Value + " T3
          ON T2.PartNum = T3.PartNum
          AND T2.Company = T3.Company"

Thank you.

I have struggled with modifying RDD’s, and in these scenarios I have opted to use a BAQ report. I find it much easier to get the fields I need in a BAQ.

We are on an earlier version than Kinetic, and I have heard Epicor is moving away from BAQ reports, so I’m not sure if they are still available or future-proof. But something to consider.

EDIT: BAQ Reports aren’t going anywhere, sorry for the confusion.

wut

1 Like

Don’t quote me on that, but I thought @Mark_Wonsil said something along those lines in a post at some point. :man_shrugging:

Maybe he can confirm or deny. Maybe I dreamt it

1 Like

It was a rumo(u)r.

I believe the classic designer was going away and the Kinetic one was very different.

1 Like

Thanks Mark! Sorry for causing any confusion

1 Like

How have you set up a BAQ for an auto print BPM? I’ve only ever managed to make one with an RDD.

I think one portion of my issue is I am misunderstanding RMAs and Jobs. Because when I made a BAQ to test, even though the RMA was linked in the sales order, it didn’t show up in the BAQ under JobNum.

Like here:

image

You are able to select a BAQ “Report” for auto print. A BAQ basically serves as the RDD for a BAQ report, and I prefer making BAQ’s to modifying RDD’s was what I was getting at.

Is the sales order in question “make direct” (shipped from the job) or shipped from inventory? You can check on the order release tab, whether the “make direct” checkbox is checked or not

Thank you.

I believe they ship from inventory.

Sorry, I double checked, the release tab has Make Direct checked off.

Interesting. I wouldn’t expect you to see any jobs on that job tab unless it was checked “Make Direct”.

If it’s shipped from inventory, that’s most likely why there isn’t a job number on the RMA.

But since you are seeing a job on that releases tab, you should be able to pull that field in to a BAQ to use (assuming it’s the right job)

That worked, thank you!

image

1 Like

Good deal! I may have misunderstood your “checked off” comment, it does seem like that sales order is make direct. So I’m not sure why the RMA isn’t linked directly to the job. Could have been caused when the RMA was created, I’m not extremely familiar with that module.

But looks like you are getting the fields you need anyway, good deal!