This might help. I did it in SQL and not Epicor, but it should be the same.
with Order_CTE (Company, PartNum, OrderLine)
as
(
select Company, PartNum, OrderLine
from dbo.OrderDtl
where Company = 'X' and OrderNum = @OrderNum and OpenLine = 1
group by Company, PartNum, OrderLine
),
BOM_CTE (Company, PartNum, [Level], ChildPart)
as
(
select o.Company, o.PartNum as PartNum, 0 as [Level], p.MtlPartNum as ChildPart
from Order_CTE as o
inner join dbo.PartMtl as p
on o.Company = p.Company and o.PartNum = p.PartNum
union all
select b.Company, b.PartNum, [Level] + 1, b.MtlPartNum
from dbo.PartMtl as b
inner join BOM_CTE as q
on b.PartNum = q.ChildPart
where b.Company = 'X'
),
BOM_Group_CTE (Company, PartNum)
as
(
select Company, PartNum
from BOM_CTE
group by Company, PartNum
union
select Company, ChildPart
from BOM_CTE
group by Company, ChildPart
),
Inventory_CTE (Company, PartNum, OnHandQty)
as
(
select a.Company, a.PartNum, Sum(OnHandQty) as OnHandQty
from BOM_Group_CTE as a
inner join dbo.PartWhse as c
on a.Company = c.Company and a.PartNum = c.PartNum
group by a. Company, a.PartNum
),
Part_CTE (Company, PartNum, PartDescription, TypeCode, ClassID, IUM)
as
(
select a.Company, a.PartNum, d.PartDescription, d.TypeCode, d.ClassID, d.IUM
from BOM_Group_CTE as a
inner join dbo.Part as d
on a.Company = d.Company and a.PartNum = d.PartNum
),
PartPlant_CTE (Company, PartNum, LeadTime, MinimumQty, MaximumQty, SafetyQty, MinOrderQty)
as
(
select a.Company, a.PartNum, MAX(LeadTime) as LeadTime, MIN(MinimumQty) as MinimumQty, MAX(MaximumQty) as MaximumQty,
MAX(SafetyQty) as SafetyQty, MIN(MinOrderQty) as MinOrderQty
from BOM_Group_CTE as a
inner join dbo.PartPlant as e
on a.Company = e.Company and a.PartNum = e.PartNum
group by a.Company, a.PartNum
),
Demand_CTE (Company, PartNum, [Source], DueDate, Quantity, IUM, AssemblySeq, JobSeq)
as
(
select a.Company, a.PartNum,
'SO: ' + convert(varchar, OrderRelNum) + '/' + convert(varchar, OrderLine) + '/' + convert(varchar, OrderRelNum) as [Source],
f.DueDate, Quantity, f.IUM, f.AssemblySeq, f.JobSeq
from BOM_Group_CTE as a
inner join dbo.PartDtl as f
on a.Company = f.Company and a.PartNum = f.PartNum
where RequirementFlag = 1 and SourceFile = 'SO' and [Type] <> 'sub'
),
Demand_Job_CTE (Company, PartNum, [Source], DueDate, Quantity, IUM, [Status], AssemblySeq, JobSeq)
as
(
select a.Company, a.PartNum, 'Job: ' + f.JobNum as [Source],
f.DueDate, Quantity, f.IUM,
(case
when g.JobReleased = 1 then 'Released'
when g.JobEngineered = 1 then 'Engineered'
when g.JobFirm = 1 then 'Firm'
else 'Un-Firm'
end) as [Status], f.AssemblySeq, f.JobSeq
from BOM_Group_CTE as a
inner join dbo.PartDtl as f
on a.Company = f.Company and a.PartNum = f.PartNum
inner join dbo.JobHead as g
on f.Company = g.Company and f.JobNum = g.JobNum
where RequirementFlag = 1 and SourceFile = 'JM' and [Type] <> 'sub'
),
Supply_CTE (Company, PartNum, [Source], DueDate, Quantity, IUM, AssemblySeq, JobSeq)
as
(
select a.Company, a.partNum,
'PO: ' + convert(varchar, PONum) + '/' + convert(varchar, POLine) + '/' + convert(varchar, PORelNum) as [Source],
f.DueDate, Quantity, f.IUM, f.AssemblySeq, f.JobSeq
from BOM_Group_CTE as a
inner join dbo.PartDtl as f
on a.Company = f.Company and a.PartNum = f.PartNum
where RequirementFlag = 0 and SourceFile = 'PO' and [Type] <> 'sub'
),
Supply_Job_CTE (Company, PartNum, [Source], DueDate, Quantity, IUM, [Status], AssemblySeq, JobSeq)
as
(
select a.Company, a.partNum, 'Job: ' + f.JobNum as [Source],
f.DueDate, Quantity, f.IUM,
(case
when g.JobReleased = 1 then 'Released'
when g.JobEngineered = 1 then 'Engineered'
when g.JobFirm = 1 then 'Firm'
else 'Un-Firm'
end) as [Status], f.AssemblySeq, f.JobSeq
from BOM_Group_CTE as a
inner join dbo.PartDtl as f
on a.Company = f.Company and a.PartNum = f.PartNum
inner join dbo.JobHead as g
on f.Company = g.Company and f.JobNum = g.JobNum
where RequirementFlag = 0 and SourceFile = 'JH' and [Type] <> 'sub'
),
Results_CTE (PartNum, LeadTime, [Source], DueDate, PendingReceipt, PendingRequirement, [Status], PartDesc, TypeCode, ClassID, MinimumQty, MaximumQty, SafetyQty, MinimumOrderQty, IUM, AssemblySeq, JobSeq)
as
(
select i.PartNum, k.LeadTime, 'On-Hand Quantity' as [Source], 1/1/1900 as DueDate, i.OnHandQty as PendingReceipt, 0 as PendingRequirement, '' as [Status], j.PartDescription as PartDesc, j.TypeCode as TypeCode, j.ClassID as ClassID, k.MinimumQty as MinimumQty, k.MaximumQty as MaximumQty, k.SafetyQty as SafetyQty, k.MinOrderQty as MinOrderQty, j.IUM as IUM, '' as AssemblySeq, '' as JobSeq
from Inventory_CTE as i
inner join Part_CTE as j
on i.Company = j.Company and i.PartNum = j.PartNum
inner join PartPlant_CTE as k
on i.Company = k.Company and i.PartNum = k.PartNum
union
select m.PartNum, k.LeadTime, m.[Source] as [Source], m.DueDate as DueDate, 0 as PendingReceipt, m.Quantity as PendingRequirement, '' as [Status], j.PartDescription as PartDesc, j.TypeCode as TypeCode, j.ClassID as ClassID, k.MinimumQty as MinimumQty, k.MaximumQty as MaximumQty, k.SafetyQty as SafetyQty, k.MinOrderQty as MinOrderQty, m.IUM as IUM, m.AssemblySeq as AssemblySeq, m.JobSeq as JobSeq
from Demand_CTE as m
inner join Part_CTE as j
on m.Company = j.Company and m.PartNum = j.PartNum
inner join PartPlant_CTE as k
on m.Company = k.Company and m.PartNum = k.PartNum
union
select s.PartNum, k.LeadTime, s.[Source] as [Source], s.DueDate as DueDate, 0 as PendingReceipt, s.Quantity as PendingRequirement, s.[Status] as [Status], j.PartDescription as PartDesc, j.TypeCode as TypeCode, j.ClassID as ClassID, k.MinimumQty as MinimumQty, k.MaximumQty as MaximumQty, k.SafetyQty as SafetyQty, k.MinOrderQty as MinOrderQty, s.IUM as IUM, s.AssemblySeq as AssemblySeq, s.JobSeq as JobSeq
from Demand_Job_CTE as s
inner join Part_CTE as j
on s.Company = j.Company and s.PartNum = j.PartNum
inner join PartPlant_CTE as k
on s.Company = k.Company and s.PartNum = k.PartNum
union
select n.PartNum, k.LeadTime, n.[Source] as [Source], n.DueDate as DueDate, n.Quantity as PendingReceipt, 0 as PendingRequirement, '' as [Status], j.PartDescription as PartDesc, j.TypeCode as TypeCode, j.ClassID as ClassID, k.MinimumQty as MinimumQty, k.MaximumQty as MaximumQty, k.SafetyQty as SafetyQty, k.MinOrderQty as MinOrderQty, n.IUM as IUM, n.AssemblySeq as AssemblySeq, n.JobSeq as JobSeq
from Supply_CTE as n
inner join Part_CTE as j
on n.Company = j.Company and n.PartNum = j.PartNum
inner join PartPlant_CTE as k
on n.Company = k.Company and n.PartNum = k.PartNum
union
select r.PartNum, k.LeadTime, r.[Source] as [Source], r.DueDate as DueDate, r.Quantity as PendingReceipt, 0 as PendingRequirement, r.[Status] as [Status], j.PartDescription as PartDesc, j.TypeCode as TypeCode, j.ClassID as ClassID, k.MinimumQty as MinimumQty, k.MaximumQty as MaximumQty, k.SafetyQty as SafetyQty, k.MinOrderQty as MinOrderQty, r.IUM as IUM, r.AssemblySeq as AssemblySeq,r.JobSeq as JobSeq
from Supply_Job_CTE as r
inner join Part_CTE as j
on r.Company = j.Company and r.PartNum = j.PartNum
inner join PartPlant_CTE as k
on r.Company = k.Company and r.PartNum = k.PartNum
)
Select *
From Results_CTE
Order By PartNum, DueDate
END