Creating an on time delivery report BAQ?

[Ryan]

Good morning everyone,

New to the forum and about 3 months in on Epicor with…some training

I am trying to create an on time delivery BAQ that references the PO’s, line and release delivery/promised date with the date the physical goods have been received but keep on running into issues.
It seems that while there is a disconnect with the RcvHead that links to RcvDtl - which contains the receipt date and POHeader that links to PODetail which contains the line/release delivery date/promised date.

The moment I try and link the two tree’s (Rcv and PO) it breaks my report. However when run independently I get two halves of the full information required.

Any help on the subject would be grand

PS I also hear there is a report out the box for Epicor that gives you an on time order report but I’m quite dubious…

[mcfreedombaby (James McKinnon)]

I did this in SSRS as it was proving too tricky for a baq - below is the underlying query - run in SQL and see if it gives you what you want as a starting point - you can then add filters, group by etc - if you want I can send you the ssrs report and instructions on how to hook it up to Epicor

with receipts as (select ponum as pnumber,POLine as pline, max(ReceiptDate) as lastrecpt from [E10live].[Erp].[RcvDtl] group by ponum,poline)

SELECT povendor.pov
,povendor.name
,povendor.vendorid
,b.PartNum
,b.linedesc
,b.ClassID
,povendor.OrderDate
,a.[OpenRelease]
,a.[VoidRelease]
,a.[PONum]
,a.[POLine]
,a.[PORelNum]
,a.[DueDate]
,a.[XRelQty]
,a.[PurchasingFactor]
,a.[RelQty]
,a.[ReceivedQty]
,b.UnitCost
,case when b.CostPerCode=‘E’ then
(a.[RelQty]b.UnitCost)
when b.CostPerCode=‘C’ then
(a.[RelQty](b.UnitCost/100))
when b.CostPerCode=‘M’ then
(a.[RelQty]*(b.UnitCost/1000))
end as releasevalue
,a.[ReqChgDate]
,case when a.[ReqChgDate] is null then
0
else
1
end as pochanged
,a.[ReqChgQty]
,a.[TranType]
,a.[PreviousDueDate]
,a.[BaseQty]
,a.[BaseUOM]
,a.[PORelOpen]
,receipts.lastrecpt
,case when lastrecpt>a.[DueDate] then
0
when lastrecpt is null then
0
else
1
end as ontime

FROM [E10live].[Erp].[PORel] as a
inner join [E10live].[Erp].[PODetail] as b on
b.PONUM=a.ponum
and a.POLine=b.poline

inner join (select
c.ponum
,c.OrderDate
,c.VendorNum as pov
,d.VendorID
,d.Name
from e10live.erp.POHeader as c
inner join e10live.erp.Vendor d on
d.VendorNum=c.VendorNum) as povendor on
a.[PONum]=povendor.[PONum]

left join receipts on
a.ponum=receipts.pnumber and a.poline=pline

[Ryan]

Good morning James

Thank you so much for your response, it is very much appreciated. I’m finding it a bit mad that Epicor doesn’t support an on-time delivery report…or at least have the ability for me to create one! However its reassured me that I am not crazy by having a tough time working one out.

I’ve fed your response back to some of my IT team and we shall see where this leads with regards to us using Crystal

Thanks again
Ryan

[LarsonSolutions (Bruce Larson)]

THere is a shop vision dashboard that will show you on-time delivery from suppliers (also shipments to customers) it is located under Executive Analysis -->ShopVision.
Most of the Shop vision reports are Executive queries that require a process to be run to store the data in a cube. To run the process go to Executive Analysis–>BAQ–>General operations–>Schedule processes and select the process to run.
You should consider setting to run at least on a weekly schedule.

I did find that I needed to modify the query for the dashboard to calculate a percent on-time versus just a quantity of receipts on-time.

If you want to modify the system Dashboard, make a copy of it.
Second to update the query to put in percentages, open that one an make a copy of it.
Return to the dashboard to replace the system dashboard with your fixed query.
Also, somewhere along the revisions, Epicor broke this dashboard by changing how the dimension dashboard was calculating. Check the publish and subscribe links
Here’s the finished product.

[Ryan]

Hi Bruce,

Thank you for the above, I’m having trouble with the supplier performance dimension details being filled out?I’ve run the schedule and it has updated the Dimension ID to match yours above but sadly no performance items?

Any advice would be grand as at the moment I am doing a lot of manual excel work to get said information…

thank you
ryan

[Bruce (Bruce Crile)]

We have been using the below BAQ to track supplier performance for several years. I have also included a shot from the Query Builder for some clarity.

select
[PODetail].[PONUM] as [PODetail_PONUM],
[PODetail].[POLine] as [PODetail_POLine],
[PORel].[PORelNum] as [PORel_PORelNum],
[POHeader].[BuyerID] as [POHeader_BuyerID],
[POHeader].[OrderDate] as [POHeader_OrderDate],
[PODetail].[ClassID] as [PODetail_ClassID],
[PODetail].[PartNum] as [PODetail_PartNum],
[PODetail].[LineDesc] as [PODetail_LineDesc],
[Vendor].[Name] as [Vendor_Name],
[Vendor].[VendorID] as [Vendor_VendorID],
[Vendor].[VendorNum] as [Vendor_VendorNum],
[RcvDtl].[PackSlip] as [RcvDtl_PackSlip],
[PORel].[PromiseDt] as [PORel_PromiseDt],
[PORel].[DueDate] as [PORel_DueDate],
[RcvDtl].[ReceiptDate] as [RcvDtl_ReceiptDate],
((case when ((PORel.DueDate) is null) and ((PORel.PromiseDt) is null) then 1 else 0 end)) as [Calculated_UnknownRecpt],
((case when ((PORel.DueDate) is null) then 0 else (case when RcvDtl.ReceiptDate <= dateadd(day, -7, PORel.DueDate) then 1 else 0 end) end)) as [Calculated_EarlyDue],
((case when ((PORel.PromiseDt) is null) then 0 else (case when RcvDtl.ReceiptDate <= dateadd(day, -7, PORel.PromiseDt) then 1 else 0 end) end)) as [Calculated_EarlyProm],
((case when ((PORel.DueDate) is null) then 0 else
(case when RcvDtl.ReceiptDate <= PORel.DueDate then 1 else 0 end) end)) as [Calculated_OnTimeDue],
((case when ((PORel.PromiseDt) is null) then 0 else
(case when RcvDtl.ReceiptDate <= PORel.PromiseDt then 1 else 0 end) end)) as [Calculated_OnTimeProm],
((case when OnTimeProm + OnTimeDue > 0 then 1 else 0 end)) as [Calculated_OnTime],
[PORel].[XRelQty] as [PORel_XRelQty],
[RcvDtl].[OurQty] as [RcvDtl_OurQty],
((case when RcvDtl.OurQty < PORel.XRelQty Or RcvDtl.OurQty > (PORel.XRelQty*1.05) then 0 else 1 end)) as [Calculated_QtyDiscrp],
(1) as [Calculated_RecptCount],
(Erp.FiscalPeriod(@CurrentCompany, RcvDtl.ReceiptDate)) as [Calculated_RecptMon],
(Erp.FiscalYear(@CurrentCompany, RcvDtl.ReceiptDate)) as [Calculated_RecptYr],
(dateadd(day, -7, PORel.DueDate)) as [Calculated_EarlyDate]
from Erp.PODetail as PODetail
inner join Erp.POHeader as POHeader on
PODetail.Company = POHeader.Company
and PODetail.PONum = POHeader.PONUM
inner join Erp.Vendor as Vendor on
POHeader.Company = Vendor.Company
and POHeader.VendorNum = Vendor.VendorNum
inner join Erp.PORel as PORel on
PODetail.Company = PORel.Company
and PODetail.PONum = PORel.PONUM
and PODetail.POLine = PORel.POLine
and ( PORel.OpenRelease = 0 )

inner join Erp.RcvDtl as RcvDtl on
PORel.Company = RcvDtl.Company
and PORel.PONum = RcvDtl.PONum
and PORel.POLine = RcvDtl.POLine
and PORel.PORelNum = RcvDtl.PORelNum
and ( RcvDtl.ReceivedComplete = 1 and RcvDtl.ReceiptDate >= ‘1/5/2016’ )
order by PODetail.PONUM, PODetail.POLine, PORel.PORelNum

[Ryan]

Hi Bruce,

Thank you so much on the below, it appears to be what I am after.
I have a however (of course) a few questions:

Your additional fields “((case…” where are these input? via the calculated fields editor? if so can you please elaborate as this is something I have not had any training in and could quite work out what actions you were trying to explain.

and although the report is working, what do you mean by order by “PODetail.PONUM, PODetail.POLine, PORel.PORelNum”?

Thank you again for the help, the figures match the report I was working on in excel so i am very sure this works

Ryan

[ogaray10 (Omar Garay)]

Hi,

Would you be able to send me the BAQ, we have just implemented E10 and want to track this supplier metric as part of our AS9100D program?

Thank you,

Omar Garay