TimePhase BAQ

[JohnnyAthers (John Atherley)]

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.

[JohnnyAthers (John Atherley)]

Looks like Kyle P has won the prize… I can stop hitting myself with the keyboard.

Time Phase Inquiry BAQ

We have been using this for daily reporting, but no warranty. Please validate the data. There are some cases where Time Phase calculates Sales Orders/Jobs before the PO Qty on Lines for Running Total. I argued with Epicor about this but, for the most part you should get the same results as Time Phase from epicor. Here is the RDL. You will have to define which part numbers you want to show in the parameter under default values, or modify it another way to make it produce the data you want. […

[kylepbsps (Kyle P)]

Glad you could use it. We’re all in this together! =)

[jkane (John Kane)]

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

[klincecum (Kevin Lincecum)]

@JohnnyAthers

I can stop hitting myself with the keyboard.

Are you sure? Did it help?