Job Get Details - invoke method to get material list

(pcunningham) #1

I’m trying to find a method I can invoke to provide my users with a material list. What I’m looking to provide is the equivalent to the Job Material list which is generated using “get details” for a particular job. However, in my case, I’m not doing this based on a job. It’s more of a preview of the material list that would be on a job.

I’m seeking a method where I pass: 1) Part Num 2) Part Rev 3) Effective Date - and it returns a material list (equal to what would be found in the job mtl list). Currently, we provide our users in the shop with a preview of the engineering BOM but this is clouded with all the sub-assemblies and phantom details.

I’m playing around with BL tester but the closest thing I can find is Job Entry / Get Details, however in my case I do not have a job number. Any help or direction would be appreciated!


(Monty Wilson) #2

Dear Peter,
Have you considered a BAQ report or dashboard based on PartMtl?


(James McKinnon) #3

with pm as (select x.Company, x.PartNum, x.RevisionNum, QtyPer, pr.Approved,
FixedQty, MtlPartNum as ChildPartNum
from erp.PartMtl x
inner join erp.PartRev pr on pr.Company = x.Company
and pr.PartNum = x.PartNum
and pr.RevisionNum = x.RevisionNum
and pr.Approved = 1
and pr.EffectiveDate <= GetDate()
and pr.EffectiveDate = (select max(effectiveDate)
from erp.PartRev pr2
where pr2.Company = pr.Company
and pr2.PartNum = pr.PartNum
and pr2.Approved = 1
and pr2.EffectiveDate <= GetDate()) ),
bm as (select 1 as BOMLevel,
root.PartNum as TopLevelPartNum, root.,
cast(root.QtyPer as decimal(30,10)) as PartQty
from pm as root
where not exists (select null
from Erp.PartMtl b
where b.MtlPartNum = root.PartNum)
union all
select BOMLevel + 1 as BOMLevel,
bm.TopLevelPartNum, node.
cast(node.QtyPer * bm.PartQty as decimal(30,10)) as PartQty
from pm as node inner join bm on bm.Company = node.Company
and bm.ChildPartNum = node.PartNum)
select *
from bm
where toplevelpartnum=@partnum

We use lots of sub assemblies and also need to see the nested bom and found this to be too complicated for a baq - try the above sql query as the basis of your bill of material - we only ever have one approved revision of a saleable part so this bring back the nested bom which we present in an SSRS report accessible from Epicor.

You can customise to show and summarise the purchased parts only.

(pcunningham) #4

James - thanks very much for the info. This info helped confirm for me that I wont find a method in Epicor to accomplish this. After sharing your reply with our IT manager he developed a query in our environment using similar methodology as you. Tested out, just applying some finishing touches.

Thanks again - much appreciated!

(pcunningham) #5

Hi Monty - thanks for the reply! We ended up writing SQL script to accomplish the task utilizing PartMtl. I was originally hoping that Epicor provided a method for this but looks like not.

(James McKinnon) #6

You are welcome