Before I begin shivering in a cold sweat in the corner banging a keyboard against my head like a crazy fool… Has anyone had any success writing an EXACT VERSION of TIME PHASE as a BAQ? I know it includes PartDtl, PartSug and data from PartBin but getting an VERTBATIM match of the data is nasty when I try and use Over/Partition/OrderBy for the Balance column. Anyone able to share the BAQ would save my sanity and earn a potential beer at the next Epicor meet up.
Looks like Kyle P has won the prize… I can stop hitting myself with the keyboard.
Glad you could use it. We’re all in this together! =)
1 Like
I did it as a SQL Query once if it helps anyone.
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
1 Like
Are you sure? Did it help?