How to link parent job and child job before being used to manipulate at a top level query

Epicor ERP 10
, ,
1

I have made this beginner BAQ to show all of the jobs linked to the order from our customers.
Now I want to extend it to also show the child jobs of order-linked parent jobs if the parent job is an Assembly ( denoted as S) but have yet to find an elegant way to show them.

I have tried union query but the top level will carry so much information that I don’t see the point in trying to match the number of columns of union query to the top level ( mostly performance and coherence concerns)

Is there a way I can merge the parent jobs and child job into one single column but information of the orders ( Order # and Customer name) follows the linked jobs while contents of prod. qty, description,… follows the actual job being displayed.

I have also tried to use a calculated field to deduce the actual job being displayed but wanted to know if i can consistently produce correct data if I don’t have the columns used in calculated on display ( for performance and coherence reports also)

image
image617×434 12.7 KB

select
[Mainframe].[Part_ClassID] as [Part_ClassID],
[Mainframe].[Customer_CustID] as [Customer_CustID],
[Mainframe].[OrderDtl_OrderLine] as [OrderDtl_OrderLine],
[Mainframe].[Customer_Name] as [Customer_Name],
[Mainframe].[OrderHed_OrderNum] as [OrderHed_OrderNum],
[Mainframe].[OrderHed_PONum] as [OrderHed_PONum],
[Mainframe].[OrderDtl_POLine] as [OrderDtl_POLine],
[Mainframe].[OrderDtl_PartNum] as [OrderDtl_PartNum],
[Mainframe].[OrderDtl_LineDesc] as [OrderDtl_LineDesc],
[Mainframe].[OrderDtl_OrderQty] as [OrderDtl_OrderQty],
[Mainframe].[JobHead_JobNum] as [JobHead_JobNum],
[Mainframe].[JobHead_ProdQty] as [JobHead_ProdQty],
[Mainframe].[OrderHed_OrderDate] as [OrderHed_OrderDate],
[Mainframe].[OrderDtl_RequestDate] as [OrderDtl_RequestDate],
[Mainframe].[OrderDtl_NeedByDate] as [OrderDtl_NeedByDate],
[Mainframe].[OrderDtl_OrderComment] as [OrderDtl_OrderComment]
from (select
[OrderRel].[Plant] as [OrderRel_Plant],
[Part].[ClassID] as [Part_ClassID],
[Customer].[CustID] as [Customer_CustID],
[OrderDtl].[OrderLine] as [OrderDtl_OrderLine],
[Customer].[Name] as [Customer_Name],
[OrderHed].[OrderNum] as [OrderHed_OrderNum],
[OrderHed].[PONum] as [OrderHed_PONum],
[OrderDtl].[POLine] as [OrderDtl_POLine],
[OrderDtl].[PartNum] as [OrderDtl_PartNum],
[OrderDtl].[LineDesc] as [OrderDtl_LineDesc],
[JobHead].[ProdQty] as [JobHead_ProdQty],
[JobHead].[JobNum] as [JobHead_JobNum],
[OrderHed].[OrderDate] as [OrderHed_OrderDate],
[OrderDtl].[RequestDate] as [OrderDtl_RequestDate],
[OrderDtl].[NeedByDate] as [OrderDtl_NeedByDate],
[OrderDtl].[OrderComment] as [OrderDtl_OrderComment],
[OrderDtl].[OpenLine] as [OrderDtl_OpenLine],
[OrderDtl].[OrderQty] as [OrderDtl_OrderQty],
[OrderDtl].[LastConfigDate] as [OrderDtl_LastConfigDate],
[OrderDtl].[LastConfigUserID] as [OrderDtl_LastConfigUserID]
from Erp.OrderHed as OrderHed
left outer join Erp.OrderDtl as OrderDtl on
OrderHed.Company = OrderDtl.Company
and OrderHed.OrderNum = OrderDtl.OrderNum
left outer join Erp.OrderRel as OrderRel on
OrderDtl.Company = OrderRel.Company
and OrderDtl.OrderNum = OrderRel.OrderNum
and OrderDtl.OrderLine = OrderRel.OrderLine
left outer join Erp.JobProd as JobProd on
JobProd.OrderRelNum = OrderRel.OrderRelNum
and JobProd.Company = OrderRel.Company
and JobProd.OrderNum = OrderRel.OrderNum
and JobProd.OrderLine = OrderRel.OrderLine
left outer join Erp.JobHead as JobHead on
JobProd.Company = JobHead.Company
and JobProd.JobNum = JobHead.JobNum
left outer join Erp.ProdGrup as ProdGrup on
ProdGrup.Company = OrderDtl.Company
and ProdGrup.ProdCode = OrderDtl.ProdCode
left outer join Erp.Part as Part on
OrderDtl.Company = Part.Company
and OrderDtl.PartNum = Part.PartNum
and ( Part.ClassID is not null and (Part.ClassID = ‘M’ or Part.ClassID = ‘P’ or Part.ClassID = ‘S’ ) )

left outer join Erp.Customer as Customer on
Customer.Company = OrderHed.Company
and Customer.CustNum = OrderHed.BTCustNum
where (OrderHed.OrderNum >= 3000 and not OrderHed.OrderNum = 10010)) as Mainframe
where (Mainframe.Part_ClassID is not null)
order by Mainframe.OrderHed_OrderNum Desc

2

Previous attempts for the parent-child job on display to union with order-linked jobs, but to no avail:

image
image200×412 8.43 KB

Displayed Fields:

ParentJobClass_ClassId
Parent_Job_JobNum
Parent_Job_PartNum
Parent_Job_PartDescription
Child_Job_JobNum
Child_Job_PartNum
Child_Job_PartDescription
Child_Job_ProdQty

3

What is the objective here as you only need to use something like coalesce to ensure no null values, but not sure what you are trying to achieve here. You can also use calculated columns with case statements possibly even concatenating columns into a single reference item like JobNums Order-Line-Rel or something. Hard to direct you without more details.

4

The objective is to have all jobs that are present to be linked/affiliated with an order line as a basis for dashboards later extension to include the Job Mtl status and manufacture operation.

Since the agreed upon base information in my work settings are OrderNum and JobNumber. I think it would be a great point to have it all displayed intuitively and later link to other tables according to the specific query/dashboard requirements.

Thank you for your prompt idea

5

This is present in the JobProd table already… Based on the requirements provided, all you need are the JobHead, JobProd, JobMtl and JobOper/JobOpDtl table(s). More details can be provided with additional tables if needed but these should satisfy the reuirements you provided.

Very welcome! :slight_smile:

6

also consider using the Multi-Level Pegging as an option. If you run multi-level pegging, you can then display all associated jobs to a demand. as the name implies, it will drill down from the top ALL the way to the bottom of the BOM. OR it will also look at the bottom level and “peg” all the way to the top demand.

2 Likes