All Jobs and All orders in one BAQ

Kinetic ERP
1

We have been using an Excel spreadsheet for scheduling for years that has all of our open orders and all of our jobs. The issue I run into trying to create a BAQ is not all jobs have orders (some are for stock), and not all orders have jobs (some get pulled from stock). I thought about linking them with the part table however we also make “on the fly” parts… Here is my mess of a BAQ

select
[Part].[PartNum] as [Part_PartNum],
[ORDERS].[OrderHed_OrderNum] as [OrderHed_OrderNum],
[ORDERS].[OrderHed_PONum] as [OrderHed_PONum],
[ORDERS].[OrderRel_OrderLine] as [OrderRel_OrderLine],
[ORDERS].[OrderRel_OrderRelNum] as [OrderRel_OrderRelNum],
[JOBS].[JobHead_JobNum] as [JobHead_JobNum],
[JOBS].[Calculated_Operations] as [Calculated_Operations],
[ORDERS].[OrderHed_RequestDate] as [OrderHed_RequestDate],
[ORDERS].[OrderRel_NeedByDate] as [OrderRel_NeedByDate],
[ORDERS].[OrderRel_SellingReqQty] as [OrderRel_SellingReqQty]
from (select
[JobHead].[JobNum] as [JobHead_JobNum],
[JobHead].[PartNum] as [JobHead_PartNum],
(STRING_AGG(JobOper.OpCode,‘,’) WITHIN GROUP (ORDER BY JobOper.OprSeq)) as [Calculated_Operations],
[JobHead].[Company] as [JobHead_Company]
from Erp.JobHead as JobHead
inner join Erp.JobOper as JobOper on
JobHead.Company = JobOper.Company
and JobHead.JobNum = JobOper.JobNum
inner join Erp.JobProd as JobProd on
JobHead.Company = JobProd.Company
and JobHead.JobNum = JobProd.JobNum
where (JobHead.JobComplete = 0)
group by [JobHead].[JobNum],
[JobHead].[PartNum],
[JobHead].[Company]) as JOBS
left outer join Erp.Part as Part on
Part.Company = JOBS.JobHead_Company
and Part.PartNum = JOBS.JobHead_PartNum
right outer join (select
[OrderHed].[OrderNum] as [OrderHed_OrderNum],
[OrderRel].[OrderLine] as [OrderRel_OrderLine],
[OrderRel].[OrderRelNum] as [OrderRel_OrderRelNum],
[OrderHed].[PONum] as [OrderHed_PONum],
[OrderDtl].[PartNum] as [OrderDtl_PartNum],
[OrderHed].[RequestDate] as [OrderHed_RequestDate],
[OrderRel].[NeedByDate] as [OrderRel_NeedByDate],
[OrderRel].[SellingReqQty] as [OrderRel_SellingReqQty],
[OrderHed].[Company] as [OrderHed_Company]
from Erp.OrderHed as OrderHed
inner join Erp.OrderDtl as OrderDtl on
OrderHed.Company = OrderDtl.Company
and OrderHed.OrderNum = OrderDtl.OrderNum
inner join Erp.OrderRel as OrderRel on
OrderDtl.Company = OrderRel.Company
and OrderDtl.OrderNum = OrderRel.OrderNum
and OrderDtl.OrderLine = OrderRel.OrderLine
where (OrderHed.OpenOrder = 1)) as ORDERS on
Part.Company = ORDERS.OrderHed_Company
and Part.PartNum = ORDERS.OrderDtl_PartNum

This isn’t working properly only showing the first job number it finds open with the part number.

image

This is what I am trying to achieve. Our current Excel spreadsheet.

image
image1059×753 39.9 KB

Any ideas?

1 Like
2

Just set up your BAQ for all records from both matching tables this will include orders without jobs and jobs without orders

1 Like
3

Ever try Multi-Level Pegging? It’s a process; you can run it as part of MRP or as its own process.

I’ve had to resort to it for similar reasons.

For me it runs at night (currently takes an hour for me) and then I have a BAQ based on the results. Four our business, it looks like so.

image

I’m being a little vague. If you want to know more I’ll ramble on…

2 Likes
4

I’m looking over the spreadsheet pic, and it looks all you need is a dump of the PartDtl table. (PartDtl is the Time Phase table; it stores all supply and demand.)

Then throw in the OH qty and such.

2 Likes