Hi all
I have a BAQ which I am using to compare all parts received into stock/dropshipped with their std costs and last costs and compare that with the supplier price list.
I got the query working in SSMS with execution time under 1 second, but in Epicor the BAQ times out by over 30 seconds. Why would that happen?
My query in SSMS
WITH LatestReceipt_AllParts AS (
SELECT
V.VendorID,
RD.PackSlip,
RD.ReceiptDate,
RD.PONum,
PH.OrderDate,
RD.POLine,
RD.PORelNum,
RD.PartNum,
RD.ReceivedTo,
RD.OurUnitCost,
RD.DocUnitCost,
RD.OurQty,
RD.RevisionNum,
PH.CurrencyCode,
ROW_NUMBER() OVER (
PARTITION BY RD.PartNum
ORDER BY RD.ReceiptDate DESC, RD.OurUnitCost DESC
) AS RowNum
FROM
erp.RcvDtl RD
INNER JOIN
erp.vendor V
ON V.vendornum = RD.vendornum
INNER JOIN
erp.POHeader PH
ON PH.PONum = RD.PONum
INNER JOIN
erp.PODetail PD
ON PD.PONum = RD.PONum
AND PD.POLine = RD.POLine
INNER JOIN
erp.PORel PR
ON PR.PONum = RD.PONum
AND PR.POLine = RD.POLine
AND PR.PORelNum = RD.PORelNum
WHERE (ReceivedTo = 'PUR-STK')
UNION ALL
SELECT
V.VendorID,
DSD.PackSlip,
DSD.ReceiptDate,
DSD.PONum,
PH.OrderDate,
DSD.POLine,
DSD.PORelNum,
DSD.PartNum,
'PUR-DRP',
DSD.OurUnitCost,
DSD.DocUnitCost,
DSD.OurQty,
DSD.RevisionNum,
PH.CurrencyCode,
ROW_NUMBER() OVER (
PARTITION BY DSD.PartNum
ORDER BY DSD.ReceiptDate DESC, DSD.OurUnitCost DESC
) AS RowNum
FROM
erp.DropShipDtl DSD
INNER JOIN
erp.vendor V
ON V.vendornum = DSD.vendornum
INNER JOIN
erp.POHeader PH
ON PH.PONum = DSD.PONum
), LatestPrice_AllParts AS (
SELECT
V.VendorID,VP.PartNum,VP.EffectiveDate,VP.BaseUnitPrice,VP.CurrencyCode,
ROW_NUMBER() OVER (
PARTITION BY VP.PartNum
ORDER BY VP.EffectiveDate DESC, VP.BaseUnitPrice DESC
) AS RowNum
FROM
erp.VendPart VP
INNER JOIN
erp.vendor V
ON V.vendornum = VP.vendornum
INNER JOIN erp.PartPlant PP
ON PP.PartNum = VP.PartNum
WHERE PP.VendorNum = VP.VendorNum
)
Select
PartPlant.Plant,
[PartCost].[PartNum] as [PartCost_PartNum],
[PartPlant].[SourceType] as [PartPlant_SourceType],
[PartRev].[RevisionNum] as [PartRev_RevisionNum],
[PartRev].[Approved] as [PartRev_Approved],
Vendor.[VendorID] as 'Preferred Vendor',
PartWhse.OnHandQty,
PartWhse.OnHandQty * (PartCost.StdLaborCost+ PartCost.StdBurdenCost+ PartCost.StdMaterialCost+ PartCost.StdSubContCost + PartCost.StdMtlBurCost) AS 'TotalStdCostSOH',
LatestPrice_AllParts.BaseUnitPrice,
LatestPrice_AllParts.EffectiveDate,
LatestPrice_AllParts.CurrencyCode AS 'PriceListCurrencyCode',
[PartCost].[StdLaborCost] as [PartCost_StdLaborCost],
[PartCost].[StdBurdenCost] as [PartCost_StdBurdenCost],
[PartCost].[StdMaterialCost] as [PartCost_StdMaterialCost],
[PartCost].[StdSubContCost] as [PartCost_StdSubContCost],
[PartCost].[StdMtlBurCost] as [PartCost_StdMtlBurCost],
(PartCost.StdLaborCost+ PartCost.StdBurdenCost+ PartCost.StdMaterialCost+ PartCost.StdSubContCost + PartCost.StdMtlBurCost) as [Calculated_TotalStdCost],
[PartCost].[LastLaborCost] as [PartCost_LastLaborCost],
[PartCost].[LastBurdenCost] as [PartCost_LastBurdenCost],
[PartCost].[LastMaterialCost] as [PartCost_LastMaterialCost],
[PartCost].[LastSubContCost] as [PartCost_LastSubContCost],
[PartCost].[LastMtlBurCost] as [PartCost_LastMtlBurCost],
(PartCost.LastLaborCost + PartCost.LastBurdenCost + PartCost.LastMaterialCost + PartCost.LastSubContCost + PartCost.LastMtlBurCost) as [Calculated_TotalLastCost],
LatestReceipt_AllParts.ReceiptDate,
LatestReceipt_AllParts.VendorID,
LatestReceipt_AllParts.CurrencyCode AS 'ReceiptCurrencyCode',
LatestReceipt_AllParts.OurUnitCost,
CASE
WHEN LatestPrice_AllParts.BaseUnitPrice = 0
THEN 0
ELSE
CASE
WHEN LatestPrice_AllParts.CurrencyCode <> LatestReceipt_AllParts.CurrencyCode THEN
CAST((LatestPrice_AllParts.BaseUnitPrice - LatestReceipt_AllParts.DocUnitCost)/LatestPrice_AllParts.BaseUnitPrice * 100 AS DECIMAL (14,2))
ELSE
0
END
END AS 'PriceListVariation%',
CAST((LatestReceipt_AllParts.OurUnitCost /LatestReceipt_AllParts.DocUnitCost) AS DECIMAL (14,2)) AS 'ReceiptExchangeRateValue',
LatestReceipt_AllParts.OurQty,
LatestReceipt_AllParts.PONum,
LatestReceipt_AllParts.POLine,
LatestReceipt_AllParts.PORelNum,
LatestReceipt_AllParts.ReceivedTo,
LatestReceipt_AllParts.OrderDate,
LatestReceipt_AllParts.RevisionNum
from erp.Part as Part
left join Erp.PartRev as PartRev on
Part.Company = PartRev.Company
and Part.PartNum = PartRev.PartNum
and Partrev.Approved <> 0
inner join Erp.PartCost as PartCost on
Part.PartNum = PartCost.PartNum
and Part.Company = PartCost.Company
inner join Erp.PartPlant as PartPlant on
PartPlant.Company = Part.Company
and PartPlant.PartNum = Part.PartNum
inner join erp.Vendor on
Vendor.VendorNum = PartPlant.VendorNum
inner join LatestReceipt_AllParts on
Part.PartNum = LatestReceipt_AllParts.PartNum
and LatestReceipt_AllParts.RowNum = 1
left join LatestPrice_AllParts on
Part.PartNum = LatestPrice_AllParts.PartNum
and 1 = LatestPrice_AllParts.RowNum
inner join erp.PartWhse on
PartWhse.PartNum = Part.PartNum
and PartWhse.WarehouseCode = '190'
where (Part.InActive = 0)
AND PartPlant.Plant = 'GLENDN'
order by PartCost_PartNum
My query in BAQ designer window
with [LatestReceipt_AllParts] as
(select
[Vendor1].[VendorID] as [Vendor1_VendorID],
(ROW_NUMBER() OVER (
PARTITION BY RcvDtl.PartNum
ORDER BY RcvDtl.ReceiptDate DESC, RcvDtl.OurUnitCost DESC
)) as [Calculated_RowNum],
[RcvDtl].[ReceiptDate] as [RcvDtl_ReceiptDate],
[RcvDtl].[PackSlip] as [RcvDtl_PackSlip],
[RcvDtl].[PartNum] as [RcvDtl_PartNum],
[RcvDtl].[OurUnitCost] as [RcvDtl_OurUnitCost],
[RcvDtl].[DocUnitCost] as [RcvDtl_DocUnitCost],
[RcvDtl].[OurQty] as [RcvDtl_OurQty],
[RcvDtl].[RevisionNum] as [RcvDtl_RevisionNum],
[RcvDtl].[PONum] as [RcvDtl_PONum],
[RcvDtl].[POLine] as [RcvDtl_POLine],
[RcvDtl].[PORelNum] as [RcvDtl_PORelNum],
[POHeader].[OrderDate] as [POHeader_OrderDate],
[RcvDtl].[ReceivedTo] as [RcvDtl_ReceivedTo],
[POHeader].[CurrencyCode] as [POHeader_CurrencyCode]
from Erp.Vendor as Vendor1
inner join Erp.RcvDtl as RcvDtl on
Vendor1.Company = RcvDtl.Company
and Vendor1.VendorNum = RcvDtl.VendorNum
inner join Erp.POHeader as POHeader on
RcvDtl.Company = POHeader.Company
and RcvDtl.PONum = POHeader.PONum
where (RcvDtl.ReceivedTo = 'PUR-STK' or RcvDtl.ReceivedTo = 'PUR-DRP')
union all
select
[Vendor3].[VendorID] as [Vendor3_VendorID],
(ROW_NUMBER() OVER (
PARTITION BY DropShipDtl.PartNum
ORDER BY DropShipDtl.ReceiptDate DESC, DropShipDtl.OurUnitCost DESC)) as [Calculated_RowNum],
[DropShipDtl].[ReceiptDate] as [DropShipDtl_ReceiptDate],
[DropShipDtl].[PackSlip] as [DropShipDtl_PackSlip],
[DropShipDtl].[PartNum] as [DropShipDtl_PartNum],
[DropShipDtl].[OurUnitCost] as [DropShipDtl_OurUnitCost],
[DropShipDtl].[DocUnitCost] as [DropShipDtl_DocUnitCost],
[DropShipDtl].[OurQty] as [DropShipDtl_OurQty],
[DropShipDtl].[RevisionNum] as [DropShipDtl_RevisionNum],
[DropShipDtl].[PONum] as [DropShipDtl_PONum],
[DropShipDtl].[POLine] as [DropShipDtl_POLine],
[DropShipDtl].[PORelNum] as [DropShipDtl_PORelNum],
[POHeader1].[OrderDate] as [POHeader1_OrderDate],
('PUR-DRP') as [Calculated_PURDRP],
[POHeader1].[CurrencyCode] as [POHeader1_CurrencyCode]
from Erp.DropShipDtl as DropShipDtl
inner join Erp.Vendor as Vendor3 on
DropShipDtl.Company = Vendor3.Company
and DropShipDtl.VendorNum = Vendor3.VendorNum
inner join Erp.POHeader as POHeader1 on
Vendor3.Company = POHeader1.Company
and Vendor3.VendorNum = POHeader1.VendorNum)
,[LatestPrice_AllParts] as
(select
[Vendor2].[VendorID] as [Vendor2_VendorID],
[VendPart].[PartNum] as [VendPart_PartNum],
[VendPart].[EffectiveDate] as [VendPart_EffectiveDate],
[VendPart].[BaseUnitPrice] as [VendPart_BaseUnitPrice],
[VendPart].[CurrencyCode] as [VendPart_CurrencyCode],
(ROW_NUMBER() OVER (
PARTITION BY VendPart.PartNum
ORDER BY VendPart.EffectiveDate DESC, VendPart.BaseUnitPrice DESC
)) as [Calculated_RowNum]
from Erp.Vendor as Vendor2
inner join Erp.VendPart as VendPart on
Vendor2.Company = VendPart.Company
and Vendor2.VendorNum = VendPart.VendorNum
inner join Erp.PartPlant as PartPlant1 on
VendPart.Company = PartPlant1.Company
and VendPart.PartNum = PartPlant1.PartNum
and VendPart.VendorNum = PartPlant1.VendorNum)
select
[PartCost].[PartNum] as [PartCost_PartNum],
[PartPlant].[SourceType] as [PartPlant_SourceType],
[PartRev].[RevisionNum] as [PartRev_RevisionNum],
[Vendor].[VendorID] as [Vendor_VendorID],
[PartCost].[StdLaborCost] as [PartCost_StdLaborCost],
[PartCost].[StdBurdenCost] as [PartCost_StdBurdenCost],
[PartCost].[StdMaterialCost] as [PartCost_StdMaterialCost],
[PartCost].[StdSubContCost] as [PartCost_StdSubContCost],
[PartCost].[StdMtlBurCost] as [PartCost_StdMtlBurCost],
(PartCost.StdLaborCost+ PartCost.StdBurdenCost+ PartCost.StdMaterialCost+ PartCost.StdSubContCost + PartCost.StdMtlBurCost) as [Calculated_TotalStdCost],
[PartWhse].[OnHandQty] as [PartWhse_OnHandQty],
((PartCost.StdLaborCost+ PartCost.StdBurdenCost+ PartCost.StdMaterialCost+ PartCost.StdSubContCost + PartCost.StdMtlBurCost) * PartWhse.OnHandQty) as [Calculated_TotalStdCostSOH],
[PartCost].[LastLaborCost] as [PartCost_LastLaborCost],
[PartCost].[LastBurdenCost] as [PartCost_LastBurdenCost],
[PartCost].[LastMaterialCost] as [PartCost_LastMaterialCost],
[PartCost].[LastSubContCost] as [PartCost_LastSubContCost],
[PartCost].[LastMtlBurCost] as [PartCost_LastMtlBurCost],
(PartCost.LastLaborCost + PartCost.LastBurdenCost + PartCost.LastMaterialCost + PartCost.LastSubContCost + PartCost.LastMtlBurCost) as [Calculated_TotalLastCost],
[LatestPrice_AllParts].[VendPart_BaseUnitPrice] as [VendPart_BaseUnitPrice],
[LatestPrice_AllParts].[VendPart_CurrencyCode] as [VendPart_CurrencyCode],
[LatestPrice_AllParts].[VendPart_EffectiveDate] as [VendPart_EffectiveDate],
[LatestReceipt_AllParts].[RcvDtl_ReceiptDate] as [RcvDtl_ReceiptDate],
[LatestReceipt_AllParts].[Vendor1_VendorID] as [Vendor1_VendorID],
[LatestReceipt_AllParts].[RcvDtl_PackSlip] as [RcvDtl_PackSlip],
[LatestReceipt_AllParts].[RcvDtl_OurQty] as [RcvDtl_OurQty],
[LatestReceipt_AllParts].[RcvDtl_OurUnitCost] as [RcvDtl_OurUnitCost],
(CAST((LatestReceipt_AllParts.RcvDtl_OurUnitCost /LatestReceipt_AllParts.RcvDtl_DocUnitCost) AS Decimal (14,2))) as [Calculated_ExchangeRateValue],
(CASE
WHEN LatestPrice_AllParts.VendPart_BaseUnitPrice = 0 THEN 0
ELSE
CASE
WHEN LatestPrice_AllParts.VendPart_CurrencyCode <> LatestReceipt_AllParts.POHeader_CurrencyCode THEN
CAST((LatestPrice_AllParts.VendPart_BaseUnitPrice - LatestReceipt_AllParts.RcvDtl_DocUnitCost)/LatestPrice_AllParts.VendPart_BaseUnitPrice * 100 AS DECIMAL (14,2))
ELSE 0
END
END) as [Calculated_PriceListVariation],
[LatestReceipt_AllParts].[RcvDtl_POLine] as [RcvDtl_POLine],
[LatestReceipt_AllParts].[RcvDtl_PONum] as [RcvDtl_PONum],
[LatestReceipt_AllParts].[RcvDtl_PORelNum] as [RcvDtl_PORelNum],
[LatestReceipt_AllParts].[POHeader_OrderDate] as [POHeader_OrderDate],
[LatestReceipt_AllParts].[RcvDtl_ReceivedTo] as [RcvDtl_ReceivedTo]
from Erp.PartCost as PartCost
inner join Erp.Part as Part on
Part.Company = PartCost.Company
and Part.PartNum = PartCost.PartNum
inner join Erp.PartPlant as PartPlant on
PartPlant.Company = Part.Company
and PartPlant.PartNum = Part.PartNum
inner join Erp.Vendor as Vendor on
PartPlant.Company = Vendor.Company
and PartPlant.VendorNum = Vendor.VendorNum
left outer join Erp.PartRev as PartRev on
Part.Company = PartRev.Company
and Part.PartNum = PartRev.PartNum
and ( PartRev.Approved <> 0 )
inner join LatestReceipt_AllParts as LatestReceipt_AllParts on
Part.PartNum = LatestReceipt_AllParts.RcvDtl_PartNum
and ( LatestReceipt_AllParts.Calculated_RowNum = 1 )
left outer join LatestPrice_AllParts as LatestPrice_AllParts on
Part.PartNum = LatestPrice_AllParts.VendPart_PartNum
and ( LatestPrice_AllParts.Calculated_RowNum = 1 )
inner join Erp.PartWhse as PartWhse on
Part.Company = PartWhse.Company
and Part.PartNum = PartWhse.PartNum
where (Part.InActive = 0 and PartPlant.Plant = 'GLENDN' and PartWhse.WarehouseCode = '190')