BAQ Concatenate Inventory Bin information error

Epicor ERP 10
,
1

I have created a BAQ (mostly with the help of posts on this site…thanks all!) that will eventually be turned into our Job pick list (shows all job material with inventory that needs to be picked.

I’m trying to create a concatenate so the material line for a specific job material/requirements shows all inventory BINS for a specific part that has inventory.

The report is running, but instead of showing me the bins specific to the part in question every warehouse bin we have is showing (with the inventory amount). And if a specific job material has inventory in multiple locations the report is listing a line for every bin location (I don’t want multiple lines for fear that lead to picking errors).

Any ideas what I’m doing wrong?

select
[JobHead].[JobClosed] as [JobHead_JobClosed],
[JobHead].[JobNum] as [JobHead_JobNum],
[JobMtl].[IssuedComplete] as [JobMtl_IssuedComplete],
[JobMtl].[BackFlush] as [JobMtl_BackFlush],
[JobHead].[PartNum] as [JobHead_PartNum],
[JobHead].[PartDescription] as [JobHead_PartDescription],
[JobMtl].[AssemblySeq] as [JobMtl_AssemblySeq],
[JobMtl].[MtlSeq] as [JobMtl_MtlSeq],
[JobMtl].[PartNum] as [JobMtl_PartNum],
[JobMtl].[Description] as [JobMtl_Description],
[JobMtl].[RequiredQty] as [JobMtl_RequiredQty],
[JobMtl].[IssuedQty] as [JobMtl_IssuedQty],
[JobMtl].[IUM] as [JobMtl_IUM],
[Part].[ClassID] as [Part_ClassID],
(REPLACE(REPLACE(((select
(CAST(PartBin.BinNum AS VARCHAR)+’,’) as [Calculated_PART_BIN],
[PartBin].[OnhandQty] as [PartBin_OnhandQty]
from Erp.PartBin as PartBin
where PartBin.PartNum = PartBin.PartNum FOR XML PATH(’’))) , ‘</
Calculated_PART_BIN>’,’’),’<Calculated_PART_BIN>’,’’)) as [Calculated_CONCATENATE_PARTBIN]
from Erp.JobHead as JobHead
inner join Erp.JobMtl as JobMtl on
JobHead.Company = JobMtl.Company
and JobHead.JobNum = JobMtl.JobNum
and ( JobMtl.BackFlush = FALSE and JobMtl.IssuedComplete = FALSE )

inner join Erp.Part as Part on
JobMtl.Company = Part.Company
and JobMtl.PartNum = Part.PartNum
and ( Part.ClassID <> ‘0022’ and Part.ClassID <> ‘0009’ and Part.ClassID <> ‘0010’ and Part.ClassID <> ‘0006’ and Part.ClassID <> ‘0008’ )

inner join Erp.PartBin as PartBin1 on
Part.Company = PartBin1.Company
and Part.PartNum = PartBin1.PartNum
and ( PartBin1.OnhandQty > 0 )

where (JobHead.JobClosed = FALSE)
order by JobHead.JobNum, JobMtl.PartNum

2

I’m not very good at reading SQL , but do have the part bin table on the top level too? You’ll have to take that off, that’s where you are getting your duplicate rows.

3

Thanks! I did remove the extra part bin from my main query. But my concatenate column is still showing all inventory and bin locations for all parts in our inventory (not just inventory for the specific part required for the job. I’ve attached what I see when I hover over the field. Any ideas on how to resolve that? results.pdf (224.5 KB)

4

Can you show some screen shots of your subquery criteria? Also, something is messed up in your XML path the results shouldn’t show the <> bracketed items in there.

5

I think I have screenshots of just about everything. See attachment.

Job Pick List.pdf (272.9 KB)

6

There are a couple things I see that I think could be the problem.

first, your subquery criteria is saying that PartBin.PartNum = ParBin.PartNum. So it’s not filtering anything because the same field will always equal itself. It should be referencing part.partnum instead. It’s not in the menu to pick because it’s on a different level, you have to type it in.

image
image.png1022×182 7.27 KB

The second thing is your calculated fields on both queries need to be the same. It’s looping through and it can’t replace them right if it’s a different name. There might be a way to make it work with 2 different names, but I don’t know how to do that. I just make them the same and it works.

image
image.png816×438 15.7 KB

image

Last nitpicky thing is the nvarchar type for you calculated fields. I don’t know if it matters, but epicor treats x(1000) as max, so it will expand as necessary. (someone correct me if I am wrong on that one)

This is what you should end up with.

image
image.png1470×185 8.99 KB

Here is the BAQ I made to test it if you want it.
JobXML.baq (27.4 KB)

1 Like
7

I followed your advice and it’s working now exactly how I wanted! So many things to know when writing BAQ’s and I’m just starting out.

Thank you so much for your help!!!

1 Like
8

Guys, I am trying to accomplish the same thing.

I tried importing the .baq file, it will not load, I am using Epicor 10.2

do you have a copy of this baq I can read?

Many Thanks

9

The PDF that he posted has how he made the original BAQ and the post I posted is what I would change. You should be able to recreate it by using those two things.

Also, what version of SQL are you on? If you’re on 2019+ you can use the String_Agg() function to do this a lot easier.

10

I am having issues with the { }

Our server is 2019+

Could I email you direct a copy of my baq?

11

You can just upload it here.

12

Here it is, thanks.

13

stedertest.baq (25.9 KB)

14

You’re missing the whole subquery to concatenate the rows.

if your SQL version is 2019+ then try this as a calculated field

string_agg(PartBin.BinNum, ' ,');

and group by the other fields in the query.

image

image
image1019×252 11.6 KB

image
image1002×343 20.6 KB

15

Thanks for looking into this so quickly. I will look it over, get back with you next week with additional questions.