Lot Tracking from finished good to Raw Material

Kinetic ERP
1

I am having a hard time creating a BAQ to would loop through the Top level job and pull all of the lot numbers used on the job. This would also include any parts that have a make to stock job and lots as well. I have a part that has a part that starts with raw mtl 123 and lot abc that get issued to a job to make part 456 and that get lot def when completed. then that part 456 gets issued to another job to create part 789 and lot ghi. once completed it gets issued to the final assembly as part 789 with lot ghi.

So what i am looking for is a baq to show that process. Is this even possible? If someone has an example of this or some ideas that would be helpfull

thanks
Devin

1 Like
2

You’d be wanting to do a CTE baq for that. There is an example of this in the help for BOMs if I recall, if not there are heaps of information here for baqs with a hierarchy, do I have one to share? sadly no.

1 Like
3

So here is how i ended up doing this can anyone review this for me and provide some feedback good or bad on it. Thanks.

API-GETMtr.baq (281.7 KB)

with [BaseJobQuery] as 
(select 
	[JobMtl].[Company] as [JobMtl_Company],
	[JobHead1].[JobNum] as [JobHead1_JobNum],
	[JobHead1].[PartNum] as [JobHead1_PartNum],
	[JobMtl].[PartNum] as [JobMtl_PartNum],
	[PartTran].[LotNum] as [PartTran_LotNum],
	[PartTran].[TranDate] as [PartTran_TranDate],
	(0) as [Calculated_heiarchiy]
from Erp.JobMtl as JobMtl
inner join Erp.Part as Part on 
	JobMtl.Company = Part.Company
	and JobMtl.PartNum = Part.PartNum
	and ( Part.TrackLots = True  )

left outer join Erp.PartTran as PartTran on 
	JobMtl.Company = PartTran.Company
	and JobMtl.JobNum = PartTran.JobNum
	and JobMtl.PartNum = PartTran.PartNum
left outer join Erp.JobHead as JobHead1 on 
	JobMtl.Company = JobHead1.Company
	and JobMtl.JobNum = JobHead1.JobNum
where (JobMtl.JobNum = 'F1105216')
union all
select 
	[BaseJobQuery].[JobMtl_Company] as [JobMtl_Company],
	[JobMtl1].[JobNum] as [JobMtl1_JobNum],
	[PartTran1].[PartNum] as [PartTran1_PartNum],
	[JobMtl1].[PartNum] as [JobMtl1_PartNum],
	[PartTran2].[LotNum] as [PartTran2_LotNum],
	[PartTran2].[TranDate] as [PartTran2_TranDate],
	(BaseJobQuery.Calculated_heiarchiy + 1) as [Calculated_Hei]
from  BaseJobQuery  as BaseJobQuery
inner join Erp.PartTran as PartTran1 on 
	BaseJobQuery.JobMtl_Company = PartTran1.Company
	and BaseJobQuery.PartTran_LotNum = PartTran1.LotNum
	and BaseJobQuery.JobMtl_PartNum = PartTran1.PartNum
	and ( PartTran1.TranType = 'MFG-STK'  )

inner join Erp.JobMtl as JobMtl1 on 
	PartTran1.Company = JobMtl1.Company
	and PartTran1.JobNum = JobMtl1.JobNum
inner join Erp.PartTran as PartTran2 on 
	JobMtl1.Company = PartTran2.Company
	and JobMtl1.PartNum = PartTran2.PartNum
	and JobMtl1.JobNum = PartTran2.JobNum)

select 
	[SubQuery7].[JobMtl_Company] as [JobMtl_Company],
	(Max(SubQuery7.JobHead1_JobNum)) as [Calculated_MaxJobNum],
	[SubQuery7].[JobHead1_PartNum] as [JobHead1_PartNum],
	[SubQuery7].[JobMtl_PartNum] as [JobMtl_PartNum],
	[SubQuery7].[PartTran_LotNum] as [PartTran_LotNum],
	[SubQuery7].[Calculated_heiarchiy] as [Calculated_heiarchiy]
from  (select 
	[BaseJobQuery1].[JobMtl_Company] as [JobMtl_Company],
	[BaseJobQuery1].[JobHead1_JobNum] as [JobHead1_JobNum],
	[BaseJobQuery1].[JobHead1_PartNum] as [JobHead1_PartNum],
	[BaseJobQuery1].[JobMtl_PartNum] as [JobMtl_PartNum],
	[BaseJobQuery1].[PartTran_LotNum] as [PartTran_LotNum],
	[BaseJobQuery1].[PartTran_TranDate] as [PartTran_TranDate],
	[BaseJobQuery1].[Calculated_heiarchiy] as [Calculated_heiarchiy]
from  BaseJobQuery  as BaseJobQuery1)  as SubQuery7
group by [SubQuery7].[JobMtl_Company],
	[SubQuery7].[JobHead1_PartNum],
	[SubQuery7].[JobMtl_PartNum],
	[SubQuery7].[PartTran_LotNum],
	[SubQuery7].[Calculated_heiarchiy]
order by SubQuery7.Calculated_heiarchiy
1 Like