Serial Sale BAQ

Epicor ERP 10
, ,
1

Hi Hive mind

Is there a report built in to Epicor that can show me what serial numbers have been sold in a month, this is a move from the sales team to track by serial number. If there is not a report can someone point be in the right direction for making a BAQ to return this data.

Thank you so much for any help

Cheers
D

2

To find that you might have to go to shipments for the month and then find the serial number on those.

Ship Head joined to Ship Dtl will get you all the shipments. Then you can join OrderHed to ShipDtl.OrderNum and company of course, and then from there you can join Customer to ShipHead.Shiptocustnum or ShipHead.Custnum.

1 Like
3

I think you’ll also need ShipDtl > SNTran to get the actual serial number. You can join that by OrderNum/Line/Rel or by PackNum/Line

2 Likes
4

thanks, I have never used SN, so glad someone could pop in.

1 Like
5

Thank you this gives me a great start amazing people as always.

Cheers
D

6

Looks like SNTran is a table that will be your friend here, disregard my comments.

1 Like
7

So as will all things the goal posts change and the request for invoice number and invoice date was added which I have done. My only problem is that my serial numbers are duplicated and im at a lost how to not make that happen, here is what i have done so far and it give me the info i need.

/*
 * Disclaimer!!!
 * This is not a real query being executed, but a simplified version for general vision.
 * Executing it with any other tool may produce a different result.
 */select 
	[ShipDtl].[PartNum] as [ShipDtl_PartNum],
	[SNTran].[SerialNumber] as [SNTran_SerialNumber],
	[InvcHead].[InvoiceNum] as [InvcHead_InvoiceNum],
	[InvcHead].[InvoiceDate] as [InvcHead_InvoiceDate],
	[SNTran].[SNStatus] as [SNTran_SNStatus]
from Erp.ShipHead as ShipHead
inner join Erp.ShipDtl as ShipDtl on 
	ShipHead.Company = ShipDtl.Company
	and ShipHead.PackNum = ShipDtl.PackNum
inner join Erp.OrderHed as OrderHed on 
	ShipDtl.Company = OrderHed.Company
	and ShipDtl.OrderNum = OrderHed.OrderNum
inner join Erp.InvcHead as InvcHead on 
	OrderHed.Company = InvcHead.Company
	and OrderHed.OrderNum = InvcHead.OrderNum
	and ( InvcHead.InvoiceDate >= '01/01/2025'  )

right outer join Erp.SNTran as SNTran on 
	SNTran.Company = ShipDtl.Company
	and SNTran.OrderNum = ShipDtl.OrderNum
	and SNTran.PackNum = ShipDtl.PackNum
	and ( SNTran.TranDate >= '01/01/2025'  )

inner join Erp.ShipTo as ShipTo on 
	ShipHead.Company = ShipTo.Company
	and ShipHead.CustNum = ShipTo.CustNum
	and ShipHead.ShipToNum = ShipTo.ShipToNum

Still learning this side of Epicor so aplogise if the above is wrong or it can be done better.

8

My fault, I lead you down an overly complicated path. Looking at your query you can use the SerialNo table instead of SNTran.
Join ShipDtl to SerialNo on Company, PackNum, and PackLine. This will probably fix your problem. SNTran will have a bunch of stuff you don’t care about.

To answer your question.

  1. Are just the serial numbers duplicated but the other columns are different?
    If this is the case you’ll have to filter each table to only return exactly what you’re looking for. An example would be if you only wanted to see SN that were shipped you would put a filter on the SNTran table where SNStatus = “Shipped”

  2. All columns in multiple rows are duplicated.
    If there are entire duplicate rows then that means that one or more of your tables are returning two or more rows, but there are column(s) in those tables that have different values in each row and you don’t have those columns visible.
    A good way to debug this is to save your query, then one table at a time, add the entire table to your display fields (Highlight the table name and click the arrow and it will move the whole table). Run the query and just scroll over until you find the columns that return different rows. Once you find them, make a note of the columns and refresh the query without saving so it will remove all of the fields (otherwise you have to manually remove each one which is a pain). Then you can add just the ones that were different and work on filtering that table to return only what you want.

The right outer join on you SNTran seems odd.
The logic below applies if you’re using either SNTran or the SerialNo tables.

  • The right outer join itself is going to return THE ENTIRE SNTran table. I’m not sure this is what you’re going for.
  • I wouldn’t join ShipDtl to SNTran (or SerialNo) on PackNum and OrderNum. I would change that to join on Company, PackNum, and PackLine OR Company, OrderNum, OrderLine, and OrderRel.
  • If you only want parts that have shipped AND have a Serial Number make it an Inner Join
  • If you want all parts that have shipped regardless if they have a serial number then make it a Left Inner Join
  • If you want ALL Serial Numbers that haven’t shipped yet then make it a Right Inner Join. Just be ready to heavily filter your SNTran Table for this because it’s going to give you a lot of junk. If you go this route I might look into using one or multiple sub queries where the SNTran table is filtered exactly the way you want it.
2 Likes
9

If you’re using SNTran you may want to filter to just the STK-CUS or other “final” shipping transactions. Otherwise you may end up pulling in STK-PCK or other “picking” transactions.

1 Like
10

Thank you for all the support i switched out SNtran for Serial No and did some trouble shooting about the double results and now i have a fully working BAQ. Made many notes.

Thank you again
D

1 Like