BAQ Concatenate Inventory Bin information error

concatenate
job-pick-list

(Michelle Heineman) #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


(Brandon Anderson) #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.


(Michelle Heineman) #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)


(Brandon Anderson) #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.


(Michelle Heineman) #5

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

Job Pick List.pdf (272.9 KB)


(Brandon Anderson) #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.

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

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.

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


(Michelle Heineman) #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!!!