I wouldn’t start from scratch. Copy the query and delete one table at a time until it responds appropriately then start focusing in on that specifically.
4 Likes
You can write final SQL statement into the server log and go from there
Also QueryOldCompanySecurity now have different name, as they show better result some time.
Need to search for name, but something “alternate” company security…
So, interesting. I can isolate the subqueries by setting the “TopLevel” query to “InnerSubQuery” and then setting the subquery I want to “TopLevel”.
All of them work. I’m documenting them here in the hopes someone will spot something that’s known to not work, and because I’ll know where to find it later…
I rebuilt the TopLevel query with no fields and started adding them one at a time, and found one that fails. It’s a member of a subquery “PartList” though, and works fine in there. In “PartList”, it looks like this:
case
when TimePhase.Calculated_MinOnHandBeforeNextTO < 0.0 then
-1.0 * TimePhase.Calculated_MinOnHandBeforeNextTO
else 0.0
end
And “TimePhase” also runs fine in isolation. The field it’s providing looks like this in its native habitat:
min(DailyTimePhase.Calculated_OnHandAtDate)
So again, not rock surgery.
That one looks like this:
OnHand + SUM(Supply - Demand) OVER (PARTITION BY PartDtl1.Plant, PartDtl1.PartNum ORDER BY PartDtl1.Plant, PartDtl1.PartNum, PartDtl1.DueDate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
And “Supply” and “Demand” respectively:
SUM(
CASE
WHEN PartDtl1.RequirementFlag = 0 THEN PartDtl1.Quantity
ELSE 0
END
)
and
SUM(
CASE
WHEN PartDtl1.RequirementFlag = 1 THEN PartDtl1.Quantity
ELSE 0
END
)
I’m trying to figure out if any of the calculations we used are deprecated.
Ah yes I noticed another one in there, but didn’t try it in case it was touchy. Many thanks!
Sorry for my ignorance - how do you do that? I’m not getting anything in the server log until I kill the execution, and then just a “it’s dead, Jim” error, or something like that.
But sadly, still doesn’t cause a change.
2 Likes
oh wow, missed that - thanks!
So getting the information out of tracing was interesting, but not very enlightening. In the “display” section it seems to regurgitate exactly what you see in the designer, complete with “not a real query” warning. Further down it seems to break everything down into sections and formulas, but aside from replacing symbols with “>” and “<” I don’t see anything different or I just don’t know where to look.
I’m still hoping to find something concrete that’s different from 10.2 to 11.1, because this query has never failed before.
So my next step is to remove one table at a time, I suppose starting with tables providing the data for the field that’s failing.
Did you update SQL server version and/or compatibility level during thre upgrade?
Hello Olga, yes we did. The old version was SQL server version 13 / compatibility level 2014 and the new is version 15 compatibility level set to 2019.
try to setup old compat level for single query in SSMS and check if result differs
1 Like
I know you said it didn’t have any errors, but look out for this: A secured or licensed column is referenced by a table relation from - Kinetic 202X - Epicor User Help Forum (epiusers.help)
Good luck!
That’s interesting. However the query runs perfectly in SSMS already - <1sec
With the number of subqueries and grouping clauses it’s a challenge to figure out where to put the QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_100 statement, so I’m working my way up from the bottom, locating each “from” clause.
Some of the items below are changed for SSMS, EG Constants.Today became getdate()
/*
* Disclaimer!!!
* This is not a real query being executed, but a simplified version for general vision.
* Executing it with any other tool may produce a different result.
*/
select
[PartsList].[PartPlant_Company] as [PartPlant_Company],
[PartsList].[PartPlant_Plant] as [PartPlant_Plant],
[PartsList].[PartPlant_TransferPlant] as [PartPlant_TransferPlant],
[PartsList].[PartPlant_PartNum] as [PartPlant_PartNum],
[PartsList].[PartPlant_MinimumQty] as [PartPlant_MinimumQty],
[PartsList].[Calculated_FirmDemand] as [Calculated_FirmDemand],
[PartsList].[Calculated_TODemand] as [Calculated_TODemand],
[PartsList].[Calculated_OnHandQty] as [Calculated_OnHandQty],
[PartsList].[Calculated_OpenTOQty] as [Calculated_OpenTOQty],
(SUM(ISNULL(OpenPOs.Calculated_OpenQty,0))) as [Calculated_OpenPOQty],
[PartsList].[Calculated_ShippedTOQty] as [Calculated_ShippedTOQty],
(PartsList.Calculated_QtyToTransfer - SUM(ISNULL(OpenPOs.Calculated_OpenQty,0))) as [Calculated_QtyToTransfer01],
[PartsList].[Calculated_NextStdTOShipDate] as [Calculated_NextStdTOShipDate],
[PartsList].[Calculated_NextStdTORcvDate] as [Calculated_NextStdTORcvDate],
[PartsList].[Calculated_QtyToExpedite] as [Calculated_QtyToExpedite],
[PartsList].[Calculated_ExpeditedDate] as [Calculated_ExpeditedDate],
[PartsList].[Calculated_NextExpTORcvDate] as [Calculated_NextExpTORcvDate],
[PartsList].[PartRev_RevisionNum] as [PartRev_RevisionNum],
[PartsList].[Calculated_5] as [Calculated_5],
[PartsList].[Calculated_QtyToTransfer] as [Calculated_QtyToTransfer]
from (select
[PartPlant].[Company] as [PartPlant_Company],
[PartPlant].[Plant] as [PartPlant_Plant],
[PartPlant].[TransferPlant] as [PartPlant_TransferPlant],
[PartPlant].[PartNum] as [PartPlant_PartNum],
[PartPlant].[MinimumQty] as [PartPlant_MinimumQty],
(ISNULL(FirmDemand.Calculated_FirmDemand,0) + ISNULL(JobDemand.Calculated_JobDemandQty, 0)) as [Calculated_FirmDemand],
(ISNULL(FirmTODemand.Calculated_FirmTODemandQty, 0)) as [Calculated_TODemand],
(ISNULL(OnHand.Calculated_OnHandQty, 0)) as [Calculated_OnHandQty],
(ISNULL(OpenTOs.Calculated_OpenTOQty, 0)) as [Calculated_OpenTOQty],
(ISNULL(OpenTOs.Calculated_ShippedTOQty, 0)) as [Calculated_ShippedTOQty],
(PartPlant.MinimumQty + (ISNULL(FirmDemand.Calculated_FirmDemand,0) + ISNULL(JobDemand.Calculated_JobDemandQty, 0)) + (ISNULL(FirmTODemand.Calculated_FirmTODemandQty, 0)) - ((ISNULL(OnHand.Calculated_OnHandQty, 0)) + (ISNULL(OpenTOs.Calculated_OpenTOQty, 0)) + (ISNULL(OpenTOs.Calculated_ShippedTOQty, 0)) + (CASE
WHEN TimePhase.Calculated_MinOnHandBeforeNextTO < 0 THEN -1*TimePhase.Calculated_MinOnHandBeforeNextTO
ELSE 0
END))) as [Calculated_QtyToTransfer],
(5) as [Calculated_5],
(CASE
WHEN PartPlant.Plant = 'MTL' THEN
CASE
WHEN DATEPART(DW,DATEADD(DAY, ((5 - 5%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + 5%5 >= 7 THEN 5%5 + 2 ELSE 5%5 END, GETDATE())) IN (3, 6)
THEN DATEADD(DAY, ((5 - 5%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + 5%5 >= 7 THEN 5%5 + 2 ELSE 5%5 END, GETDATE())
WHEN DATEPART(DW,DATEADD(DAY, (((5+1) - (5+1)%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + (5+1)%5 >= 7 THEN (5+1)%5 + 2 ELSE (5+1)%5 END, GETDATE())) IN (3, 6)
THEN DATEADD(DAY, (((5+1) - (5+1)%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + (5+1)%5 >= 7 THEN (5+1)%5 + 2 ELSE (5+1)%5 END, GETDATE())
WHEN DATEPART(DW,DATEADD(DAY, (((5+2) - (5+2)%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + (5+2)%5 >= 7 THEN (5+2)%5 + 2 ELSE (5+2)%5 END, GETDATE())) IN (3, 6)
THEN DATEADD(DAY, (((5+2) - (5+2)%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + (5+2)%5 >= 7 THEN (5+2)%5 + 2 ELSE (5+2)%5 END, GETDATE())
END
WHEN PartPlant.Plant = 'ROC' THEN
CASE
WHEN DATEPART(DW,DATEADD(DAY, ((5 - 5%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + 5%5 >= 7 THEN 5%5 + 2 ELSE 5%5 END, GETDATE())) IN (2, 5)
THEN DATEADD(DAY, ((5 - 5%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + 5%5 >= 7 THEN 5%5 + 2 ELSE 5%5 END, GETDATE())
WHEN DATEPART(DW,DATEADD(DAY, (((5+1) - (5+1)%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + (5+1)%5 >= 7 THEN (5+1)%5 + 2 ELSE (5+1)%5 END, GETDATE())) IN (2, 5)
THEN DATEADD(DAY, (((5+1) - (5+1)%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + (5+1)%5 >= 7 THEN (5+1)%5 + 2 ELSE (5+1)%5 END, GETDATE())
WHEN DATEPART(DW,DATEADD(DAY, (((5+2) - (5+2)%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + (5+2)%5 >= 7 THEN (5+2)%5 + 2 ELSE (5+2)%5 END, GETDATE())) IN (2, 5)
THEN DATEADD(DAY, (((5+2) - (5+2)%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + (5+2)%5 >= 7 THEN (5+2)%5 + 2 ELSE (5+2)%5 END, GETDATE())
END
END) as [Calculated_NextStdTOShipDate],
(CASE
WHEN TimePhase.Calculated_MinOnHandBeforeNextTO < 0 THEN -1*TimePhase.Calculated_MinOnHandBeforeNextTO
ELSE 0
END) as [Calculated_QtyToExpedite],
(CASE
WHEN PartPlant.Plant = 'MTL' THEN
CASE
WHEN DATEPART(DW, DATEADD(DAY, 2, GETDATE())) IN (3, 6) THEN DATEADD(DAY, 2, GETDATE())
WHEN DATEPART(DW, DATEADD(DAY, 3, GETDATE())) IN (3, 6) THEN DATEADD(DAY, 3, GETDATE())
WHEN DATEPART(DW, DATEADD(DAY, 4, GETDATE())) IN (3, 6) THEN DATEADD(DAY, 4, GETDATE())
WHEN DATEPART(DW, DATEADD(DAY, 5, GETDATE())) IN (3, 6) THEN DATEADD(DAY, 5, GETDATE())
WHEN DATEPART(DW, DATEADD(DAY, 6, GETDATE())) IN (3, 6) THEN DATEADD(DAY, 6, GETDATE())
WHEN DATEPART(DW, DATEADD(DAY, 7, GETDATE())) IN (3, 6) THEN DATEADD(DAY, 7, GETDATE())
WHEN DATEPART(DW, DATEADD(DAY, 8, GETDATE())) IN (3, 6) THEN DATEADD(DAY, 8, GETDATE())
END
WHEN PartPlant.Plant = 'ROC' THEN
CASE
WHEN DATEPART(DW, DATEADD(DAY, 2, GETDATE())) IN (2, 5) THEN DATEADD(DAY, 2, GETDATE())
WHEN DATEPART(DW, DATEADD(DAY, 3, GETDATE())) IN (2, 5) THEN DATEADD(DAY, 3, GETDATE())
WHEN DATEPART(DW, DATEADD(DAY, 4, GETDATE())) IN (2, 5) THEN DATEADD(DAY, 4, GETDATE())
WHEN DATEPART(DW, DATEADD(DAY, 5, GETDATE())) IN (2, 5) THEN DATEADD(DAY, 5, GETDATE())
WHEN DATEPART(DW, DATEADD(DAY, 6, GETDATE())) IN (2, 5) THEN DATEADD(DAY, 6, GETDATE())
WHEN DATEPART(DW, DATEADD(DAY, 7, GETDATE())) IN (2, 5) THEN DATEADD(DAY, 7, GETDATE())
WHEN DATEPART(DW, DATEADD(DAY, 8, GETDATE())) IN (2, 5) THEN DATEADD(DAY, 8, GETDATE())
END
END) as [Calculated_ExpeditedDate],
(CASE
WHEN DATEPART(DW, DATEADD(DAY, 1,(CASE
WHEN PartPlant.Plant = 'MTL' THEN
CASE
WHEN DATEPART(DW,DATEADD(DAY, ((5 - 5%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + 5%5 >= 7 THEN 5%5 + 2 ELSE 5%5 END, GETDATE())) IN (3, 6)
THEN DATEADD(DAY, ((5 - 5%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + 5%5 >= 7 THEN 5%5 + 2 ELSE 5%5 END, GETDATE())
WHEN DATEPART(DW,DATEADD(DAY, (((5+1) - (5+1)%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + (5+1)%5 >= 7 THEN (5+1)%5 + 2 ELSE (5+1)%5 END, GETDATE())) IN (3, 6)
THEN DATEADD(DAY, (((5+1) - (5+1)%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + (5+1)%5 >= 7 THEN (5+1)%5 + 2 ELSE (5+1)%5 END, GETDATE())
WHEN DATEPART(DW,DATEADD(DAY, (((5+2) - (5+2)%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + (5+2)%5 >= 7 THEN (5+2)%5 + 2 ELSE (5+2)%5 END, GETDATE())) IN (3, 6)
THEN DATEADD(DAY, (((5+2) - (5+2)%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + (5+2)%5 >= 7 THEN (5+2)%5 + 2 ELSE (5+2)%5 END, GETDATE())
END
WHEN PartPlant.Plant = 'ROC' THEN
CASE
WHEN DATEPART(DW,DATEADD(DAY, ((5 - 5%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + 5%5 >= 7 THEN 5%5 + 2 ELSE 5%5 END, GETDATE())) IN (2, 5)
THEN DATEADD(DAY, ((5 - 5%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + 5%5 >= 7 THEN 5%5 + 2 ELSE 5%5 END, GETDATE())
WHEN DATEPART(DW,DATEADD(DAY, (((5+1) - (5+1)%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + (5+1)%5 >= 7 THEN (5+1)%5 + 2 ELSE (5+1)%5 END, GETDATE())) IN (2, 5)
THEN DATEADD(DAY, (((5+1) - (5+1)%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + (5+1)%5 >= 7 THEN (5+1)%5 + 2 ELSE (5+1)%5 END, GETDATE())
WHEN DATEPART(DW,DATEADD(DAY, (((5+2) - (5+2)%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + (5+2)%5 >= 7 THEN (5+2)%5 + 2 ELSE (5+2)%5 END, GETDATE())) IN (2, 5)
THEN DATEADD(DAY, (((5+2) - (5+2)%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + (5+2)%5 >= 7 THEN (5+2)%5 + 2 ELSE (5+2)%5 END, GETDATE())
END
END))) IN (1,7) THEN DATEADD(DAY, 3,(CASE
WHEN PartPlant.Plant = 'MTL' THEN
CASE
WHEN DATEPART(DW,DATEADD(DAY, ((5 - 5%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + 5%5 >= 7 THEN 5%5 + 2 ELSE 5%5 END, GETDATE())) IN (3, 6)
THEN DATEADD(DAY, ((5 - 5%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + 5%5 >= 7 THEN 5%5 + 2 ELSE 5%5 END, GETDATE())
WHEN DATEPART(DW,DATEADD(DAY, (((5+1) - (5+1)%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + (5+1)%5 >= 7 THEN (5+1)%5 + 2 ELSE (5+1)%5 END, GETDATE())) IN (3, 6)
THEN DATEADD(DAY, (((5+1) - (5+1)%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + (5+1)%5 >= 7 THEN (5+1)%5 + 2 ELSE (5+1)%5 END, GETDATE())
WHEN DATEPART(DW,DATEADD(DAY, (((5+2) - (5+2)%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + (5+2)%5 >= 7 THEN (5+2)%5 + 2 ELSE (5+2)%5 END, GETDATE())) IN (3, 6)
THEN DATEADD(DAY, (((5+2) - (5+2)%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + (5+2)%5 >= 7 THEN (5+2)%5 + 2 ELSE (5+2)%5 END, GETDATE())
END
WHEN PartPlant.Plant = 'ROC' THEN
CASE
WHEN DATEPART(DW,DATEADD(DAY, ((5 - 5%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + 5%5 >= 7 THEN 5%5 + 2 ELSE 5%5 END, GETDATE())) IN (2, 5)
THEN DATEADD(DAY, ((5 - 5%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + 5%5 >= 7 THEN 5%5 + 2 ELSE 5%5 END, GETDATE())
WHEN DATEPART(DW,DATEADD(DAY, (((5+1) - (5+1)%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + (5+1)%5 >= 7 THEN (5+1)%5 + 2 ELSE (5+1)%5 END, GETDATE())) IN (2, 5)
THEN DATEADD(DAY, (((5+1) - (5+1)%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + (5+1)%5 >= 7 THEN (5+1)%5 + 2 ELSE (5+1)%5 END, GETDATE())
WHEN DATEPART(DW,DATEADD(DAY, (((5+2) - (5+2)%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + (5+2)%5 >= 7 THEN (5+2)%5 + 2 ELSE (5+2)%5 END, GETDATE())) IN (2, 5)
THEN DATEADD(DAY, (((5+2) - (5+2)%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + (5+2)%5 >= 7 THEN (5+2)%5 + 2 ELSE (5+2)%5 END, GETDATE())
END
END))
ELSE DATEADD(DAY, 1,(CASE
WHEN PartPlant.Plant = 'MTL' THEN
CASE
WHEN DATEPART(DW,DATEADD(DAY, ((5 - 5%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + 5%5 >= 7 THEN 5%5 + 2 ELSE 5%5 END, GETDATE())) IN (3, 6)
THEN DATEADD(DAY, ((5 - 5%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + 5%5 >= 7 THEN 5%5 + 2 ELSE 5%5 END, GETDATE())
WHEN DATEPART(DW,DATEADD(DAY, (((5+1) - (5+1)%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + (5+1)%5 >= 7 THEN (5+1)%5 + 2 ELSE (5+1)%5 END, GETDATE())) IN (3, 6)
THEN DATEADD(DAY, (((5+1) - (5+1)%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + (5+1)%5 >= 7 THEN (5+1)%5 + 2 ELSE (5+1)%5 END, GETDATE())
WHEN DATEPART(DW,DATEADD(DAY, (((5+2) - (5+2)%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + (5+2)%5 >= 7 THEN (5+2)%5 + 2 ELSE (5+2)%5 END, GETDATE())) IN (3, 6)
THEN DATEADD(DAY, (((5+2) - (5+2)%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + (5+2)%5 >= 7 THEN (5+2)%5 + 2 ELSE (5+2)%5 END, GETDATE())
END
WHEN PartPlant.Plant = 'ROC' THEN
CASE
WHEN DATEPART(DW,DATEADD(DAY, ((5 - 5%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + 5%5 >= 7 THEN 5%5 + 2 ELSE 5%5 END, GETDATE())) IN (2, 5)
THEN DATEADD(DAY, ((5 - 5%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + 5%5 >= 7 THEN 5%5 + 2 ELSE 5%5 END, GETDATE())
WHEN DATEPART(DW,DATEADD(DAY, (((5+1) - (5+1)%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + (5+1)%5 >= 7 THEN (5+1)%5 + 2 ELSE (5+1)%5 END, GETDATE())) IN (2, 5)
THEN DATEADD(DAY, (((5+1) - (5+1)%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + (5+1)%5 >= 7 THEN (5+1)%5 + 2 ELSE (5+1)%5 END, GETDATE())
WHEN DATEPART(DW,DATEADD(DAY, (((5+2) - (5+2)%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + (5+2)%5 >= 7 THEN (5+2)%5 + 2 ELSE (5+2)%5 END, GETDATE())) IN (2, 5)
THEN DATEADD(DAY, (((5+2) - (5+2)%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + (5+2)%5 >= 7 THEN (5+2)%5 + 2 ELSE (5+2)%5 END, GETDATE())
END
END))
END) as [Calculated_NextStdTORcvDate],
(CASE
WHEN DATEPART(DW, DATEADD(DAY, 1,(CASE
WHEN PartPlant.Plant = 'MTL' THEN
CASE
WHEN DATEPART(DW, DATEADD(DAY, 2, GETDATE())) IN (3, 6) THEN DATEADD(DAY, 2, GETDATE())
WHEN DATEPART(DW, DATEADD(DAY, 3, GETDATE())) IN (3, 6) THEN DATEADD(DAY, 3, GETDATE())
WHEN DATEPART(DW, DATEADD(DAY, 4, GETDATE())) IN (3, 6) THEN DATEADD(DAY, 4, GETDATE())
WHEN DATEPART(DW, DATEADD(DAY, 5, GETDATE())) IN (3, 6) THEN DATEADD(DAY, 5, GETDATE())
WHEN DATEPART(DW, DATEADD(DAY, 6, GETDATE())) IN (3, 6) THEN DATEADD(DAY, 6, GETDATE())
WHEN DATEPART(DW, DATEADD(DAY, 7, GETDATE())) IN (3, 6) THEN DATEADD(DAY, 7, GETDATE())
WHEN DATEPART(DW, DATEADD(DAY, 8, GETDATE())) IN (3, 6) THEN DATEADD(DAY, 8, GETDATE())
END
WHEN PartPlant.Plant = 'ROC' THEN
CASE
WHEN DATEPART(DW, DATEADD(DAY, 2, GETDATE())) IN (2, 5) THEN DATEADD(DAY, 2, GETDATE())
WHEN DATEPART(DW, DATEADD(DAY, 3, GETDATE())) IN (2, 5) THEN DATEADD(DAY, 3, GETDATE())
WHEN DATEPART(DW, DATEADD(DAY, 4, GETDATE())) IN (2, 5) THEN DATEADD(DAY, 4, GETDATE())
WHEN DATEPART(DW, DATEADD(DAY, 5, GETDATE())) IN (2, 5) THEN DATEADD(DAY, 5, GETDATE())
WHEN DATEPART(DW, DATEADD(DAY, 6, GETDATE())) IN (2, 5) THEN DATEADD(DAY, 6, GETDATE())
WHEN DATEPART(DW, DATEADD(DAY, 7, GETDATE())) IN (2, 5) THEN DATEADD(DAY, 7, GETDATE())
WHEN DATEPART(DW, DATEADD(DAY, 8, GETDATE())) IN (2, 5) THEN DATEADD(DAY, 8, GETDATE())
END
END))) IN (1,7) THEN DATEADD(DAY, 3,(CASE
WHEN PartPlant.Plant = 'MTL' THEN
CASE
WHEN DATEPART(DW, DATEADD(DAY, 2, GETDATE())) IN (3, 6) THEN DATEADD(DAY, 2, GETDATE())
WHEN DATEPART(DW, DATEADD(DAY, 3, GETDATE())) IN (3, 6) THEN DATEADD(DAY, 3, GETDATE())
WHEN DATEPART(DW, DATEADD(DAY, 4, GETDATE())) IN (3, 6) THEN DATEADD(DAY, 4, GETDATE())
WHEN DATEPART(DW, DATEADD(DAY, 5, GETDATE())) IN (3, 6) THEN DATEADD(DAY, 5, GETDATE())
WHEN DATEPART(DW, DATEADD(DAY, 6, GETDATE())) IN (3, 6) THEN DATEADD(DAY, 6, GETDATE())
WHEN DATEPART(DW, DATEADD(DAY, 7, GETDATE())) IN (3, 6) THEN DATEADD(DAY, 7, GETDATE())
WHEN DATEPART(DW, DATEADD(DAY, 8, GETDATE())) IN (3, 6) THEN DATEADD(DAY, 8, GETDATE())
END
WHEN PartPlant.Plant = 'ROC' THEN
CASE
WHEN DATEPART(DW, DATEADD(DAY, 2, GETDATE())) IN (2, 5) THEN DATEADD(DAY, 2, GETDATE())
WHEN DATEPART(DW, DATEADD(DAY, 3, GETDATE())) IN (2, 5) THEN DATEADD(DAY, 3, GETDATE())
WHEN DATEPART(DW, DATEADD(DAY, 4, GETDATE())) IN (2, 5) THEN DATEADD(DAY, 4, GETDATE())
WHEN DATEPART(DW, DATEADD(DAY, 5, GETDATE())) IN (2, 5) THEN DATEADD(DAY, 5, GETDATE())
WHEN DATEPART(DW, DATEADD(DAY, 6, GETDATE())) IN (2, 5) THEN DATEADD(DAY, 6, GETDATE())
WHEN DATEPART(DW, DATEADD(DAY, 7, GETDATE())) IN (2, 5) THEN DATEADD(DAY, 7, GETDATE())
WHEN DATEPART(DW, DATEADD(DAY, 8, GETDATE())) IN (2, 5) THEN DATEADD(DAY, 8, GETDATE())
END
END))
ELSE DATEADD(DAY, 1,(CASE
WHEN PartPlant.Plant = 'MTL' THEN
CASE
WHEN DATEPART(DW, DATEADD(DAY, 2, GETDATE())) IN (3, 6) THEN DATEADD(DAY, 2, GETDATE())
WHEN DATEPART(DW, DATEADD(DAY, 3, GETDATE())) IN (3, 6) THEN DATEADD(DAY, 3, GETDATE())
WHEN DATEPART(DW, DATEADD(DAY, 4, GETDATE())) IN (3, 6) THEN DATEADD(DAY, 4, GETDATE())
WHEN DATEPART(DW, DATEADD(DAY, 5, GETDATE())) IN (3, 6) THEN DATEADD(DAY, 5, GETDATE())
WHEN DATEPART(DW, DATEADD(DAY, 6, GETDATE())) IN (3, 6) THEN DATEADD(DAY, 6, GETDATE())
WHEN DATEPART(DW, DATEADD(DAY, 7, GETDATE())) IN (3, 6) THEN DATEADD(DAY, 7, GETDATE())
WHEN DATEPART(DW, DATEADD(DAY, 8, GETDATE())) IN (3, 6) THEN DATEADD(DAY, 8, GETDATE())
END
WHEN PartPlant.Plant = 'ROC' THEN
CASE
WHEN DATEPART(DW, DATEADD(DAY, 2, GETDATE())) IN (2, 5) THEN DATEADD(DAY, 2, GETDATE())
WHEN DATEPART(DW, DATEADD(DAY, 3, GETDATE())) IN (2, 5) THEN DATEADD(DAY, 3, GETDATE())
WHEN DATEPART(DW, DATEADD(DAY, 4, GETDATE())) IN (2, 5) THEN DATEADD(DAY, 4, GETDATE())
WHEN DATEPART(DW, DATEADD(DAY, 5, GETDATE())) IN (2, 5) THEN DATEADD(DAY, 5, GETDATE())
WHEN DATEPART(DW, DATEADD(DAY, 6, GETDATE())) IN (2, 5) THEN DATEADD(DAY, 6, GETDATE())
WHEN DATEPART(DW, DATEADD(DAY, 7, GETDATE())) IN (2, 5) THEN DATEADD(DAY, 7, GETDATE())
WHEN DATEPART(DW, DATEADD(DAY, 8, GETDATE())) IN (2, 5) THEN DATEADD(DAY, 8, GETDATE())
END
END))
END) as [Calculated_NextExpTORcvDate],
[rev].[PartRev_RevisionNum] as [PartRev_RevisionNum]
from PostUpgrade.Erp.PartPlant as PartPlant
left outer join (select
[OrderRel].[Plant] as [OrderRel_Plant],
[OrderDtl].[PartNum] as [OrderDtl_PartNum],
(SUM(OrderRel.SellingReqQty)) as [Calculated_FirmDemand]
from PostUpgrade.Erp.OrderHed as OrderHed
inner join PostUpgrade.Erp.OrderDtl as OrderDtl on
OrderHed.Company = OrderDtl.Company
and OrderHed.OrderNum = OrderDtl.OrderNum
and ( OrderDtl.OpenLine = 1 )
inner join PostUpgrade.Erp.OrderRel as OrderRel on
OrderDtl.Company = OrderRel.Company
and OrderDtl.OrderNum = OrderRel.OrderNum
and OrderDtl.OrderLine = OrderRel.OrderLine
and ( OrderRel.OpenRelease = 1 )
where (OrderHed.OpenOrder = 1 and OrderHed.ReadyToFulfill = 1 and OrderHed.RequestDate <= DATEADD(DAY, 28, GETDATE()))
group by [OrderRel].[Plant],
[OrderDtl].[PartNum]) as FirmDemand on
PartPlant.Plant = FirmDemand.OrderRel_Plant
and PartPlant.PartNum = FirmDemand.OrderDtl_PartNum
left outer join (select
[TFOrdHed].[Plant] as [TFOrdHed_Plant],
[TFOrdHed].[ToPlant] as [TFOrdHed_ToPlant],
[TFOrdDtl].[PartNum] as [TFOrdDtl_PartNum],
(SUM(TFOrdDtl.SellingQty - TFOrdDtl.SellingShippedQty)) as [Calculated_OpenTOQty],
(SUM(TFOrdDtl.SellingShippedQty - TFOrdDtl.ReceivedQty)) as [Calculated_ShippedTOQty]
from PostUpgrade.Erp.TFOrdDtl as TFOrdDtl
inner join PostUpgrade.dbo.TFOrdHed as TFOrdHed on
TFOrdDtl.Company = TFOrdHed.Company
and TFOrdDtl.TFOrdNum = TFOrdHed.TFOrdNum
and ( TFOrdHed.OrderDate > DATEADD(DAY, -60, GETDATE()) )
left outer join PostUpgrade.Erp.OrderHed as OrderHed5 on
TFOrdHed.JTF4_S_O_c = OrderHed5.OrderNum
where (TFOrdDtl.ReceivedQty <> TFOrdDtl.SellingQty)
and (OrderHed5.RequestDate <= DATEADD(DAY, 28, GETDATE()) or OrderHed5.RequestDate is null)
group by [TFOrdHed].[Plant],
[TFOrdHed].[ToPlant],
[TFOrdDtl].[PartNum]) as OpenTOs on
PartPlant.Plant = OpenTOs.TFOrdHed_ToPlant
and PartPlant.PartNum = OpenTOs.TFOrdDtl_PartNum
left outer join (select
(LEFT(WhseBin.WarehouseCode, 3)) as [Calculated_Plant],
[PartBin].[PartNum] as [PartBin_PartNum],
(SUM(PartBin.OnhandQty)) as [Calculated_OnHandQty]
from PostUpgrade.Erp.PartBin as PartBin
inner join PostUpgrade.Erp.WhseBin as WhseBin on
PartBin.Company = WhseBin.Company
and PartBin.WarehouseCode = WhseBin.WarehouseCode
and PartBin.BinNum = WhseBin.BinNum
and ( WhseBin.NonNettable = 0 )
group by (LEFT(WhseBin.WarehouseCode, 3)),
[PartBin].[PartNum]) as OnHand on
PartPlant.Plant = OnHand.Calculated_Plant
and PartPlant.PartNum = OnHand.PartBin_PartNum
left outer join (select
[DailyTimePhase].[PartDtl1_Plant] as [PartDtl1_Plant],
[DailyTimePhase].[PartDtl1_PartNum] as [PartDtl1_PartNum],
(MIN(DailyTimePhase.Calculated_OnHandAtDate)) as [Calculated_MinOnHandBeforeNextTO]
from (select
[PartDtl1].[Plant] as [PartDtl1_Plant],
[PartDtl1].[PartNum] as [PartDtl1_PartNum],
[PartDtl1].[DueDate] as [PartDtl1_DueDate],
(ISNULL(OnHand2.Calculated_OnHandqty, 0)) as [Calculated_OnHand],
(SUM(
CASE
WHEN PartDtl1.RequirementFlag = 1 THEN PartDtl1.Quantity
ELSE 0
END
)) as [Calculated_Demand],
(SUM(
CASE
WHEN PartDtl1.RequirementFlag = 0 THEN PartDtl1.Quantity
ELSE 0
END
)) as [Calculated_Supply],
(5) as [Calculated_5],
(CASE
WHEN PartDtl1.Plant = 'MTL' THEN
CASE
WHEN DATEPART(DW,DATEADD(DAY, ((5 - 5%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + 5%5 >= 7 THEN 5%5 + 2 ELSE 5%5 END, GETDATE())) IN (3, 6)
THEN DATEADD(DAY, ((5 - 5%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + 5%5 >= 7 THEN 5%5 + 2 ELSE 5%5 END, GETDATE())
WHEN DATEPART(DW,DATEADD(DAY, (((5+1) - (5+1)%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + (5+1)%5 >= 7 THEN (5+1)%5 + 2 ELSE (5+1)%5 END, GETDATE())) IN (3, 6)
THEN DATEADD(DAY, (((5+1) - (5+1)%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + (5+1)%5 >= 7 THEN (5+1)%5 + 2 ELSE (5+1)%5 END, GETDATE())
WHEN DATEPART(DW,DATEADD(DAY, (((5+2) - (5+2)%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + (5+2)%5 >= 7 THEN (5+2)%5 + 2 ELSE (5+2)%5 END, GETDATE())) IN (3, 6)
THEN DATEADD(DAY, (((5+2) - (5+2)%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + (5+2)%5 >= 7 THEN (5+2)%5 + 2 ELSE (5+2)%5 END, GETDATE())
END
WHEN PartDtl1.Plant = 'ROC' THEN
CASE
WHEN DATEPART(DW,DATEADD(DAY, ((5 - 5%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + 5%5 >= 7 THEN 5%5 + 2 ELSE 5%5 END, GETDATE())) IN (2, 5)
THEN DATEADD(DAY, ((5 - 5%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + 5%5 >= 7 THEN 5%5 + 2 ELSE 5%5 END, GETDATE())
WHEN DATEPART(DW,DATEADD(DAY, (((5+1) - (5+1)%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + (5+1)%5 >= 7 THEN (5+1)%5 + 2 ELSE (5+1)%5 END, GETDATE())) IN (2, 5)
THEN DATEADD(DAY, (((5+1) - (5+1)%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + (5+1)%5 >= 7 THEN (5+1)%5 + 2 ELSE (5+1)%5 END, GETDATE())
WHEN DATEPART(DW,DATEADD(DAY, (((5+2) - (5+2)%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + (5+2)%5 >= 7 THEN (5+2)%5 + 2 ELSE (5+2)%5 END, GETDATE())) IN (2, 5)
THEN DATEADD(DAY, (((5+2) - (5+2)%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + (5+2)%5 >= 7 THEN (5+2)%5 + 2 ELSE (5+2)%5 END, GETDATE())
END
END) as [Calculated_NextStdTOShipDate],
(CASE
WHEN DATEPART(DW, DATEADD(DAY, 1, (CASE
WHEN PartDtl1.Plant = 'MTL' THEN
CASE
WHEN DATEPART(DW,DATEADD(DAY, ((5 - 5%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + 5%5 >= 7 THEN 5%5 + 2 ELSE 5%5 END, GETDATE())) IN (3, 6)
THEN DATEADD(DAY, ((5 - 5%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + 5%5 >= 7 THEN 5%5 + 2 ELSE 5%5 END, GETDATE())
WHEN DATEPART(DW,DATEADD(DAY, (((5+1) - (5+1)%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + (5+1)%5 >= 7 THEN (5+1)%5 + 2 ELSE (5+1)%5 END, GETDATE())) IN (3, 6)
THEN DATEADD(DAY, (((5+1) - (5+1)%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + (5+1)%5 >= 7 THEN (5+1)%5 + 2 ELSE (5+1)%5 END, GETDATE())
WHEN DATEPART(DW,DATEADD(DAY, (((5+2) - (5+2)%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + (5+2)%5 >= 7 THEN (5+2)%5 + 2 ELSE (5+2)%5 END, GETDATE())) IN (3, 6)
THEN DATEADD(DAY, (((5+2) - (5+2)%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + (5+2)%5 >= 7 THEN (5+2)%5 + 2 ELSE (5+2)%5 END, GETDATE())
END
WHEN PartDtl1.Plant = 'ROC' THEN
CASE
WHEN DATEPART(DW,DATEADD(DAY, ((5 - 5%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + 5%5 >= 7 THEN 5%5 + 2 ELSE 5%5 END, GETDATE())) IN (2, 5)
THEN DATEADD(DAY, ((5 - 5%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + 5%5 >= 7 THEN 5%5 + 2 ELSE 5%5 END, GETDATE())
WHEN DATEPART(DW,DATEADD(DAY, (((5+1) - (5+1)%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + (5+1)%5 >= 7 THEN (5+1)%5 + 2 ELSE (5+1)%5 END, GETDATE())) IN (2, 5)
THEN DATEADD(DAY, (((5+1) - (5+1)%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + (5+1)%5 >= 7 THEN (5+1)%5 + 2 ELSE (5+1)%5 END, GETDATE())
WHEN DATEPART(DW,DATEADD(DAY, (((5+2) - (5+2)%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + (5+2)%5 >= 7 THEN (5+2)%5 + 2 ELSE (5+2)%5 END, GETDATE())) IN (2, 5)
THEN DATEADD(DAY, (((5+2) - (5+2)%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + (5+2)%5 >= 7 THEN (5+2)%5 + 2 ELSE (5+2)%5 END, GETDATE())
END
END))) IN (1,7) THEN DATEADD(DAY, 3, (CASE
WHEN PartDtl1.Plant = 'MTL' THEN
CASE
WHEN DATEPART(DW,DATEADD(DAY, ((5 - 5%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + 5%5 >= 7 THEN 5%5 + 2 ELSE 5%5 END, GETDATE())) IN (3, 6)
THEN DATEADD(DAY, ((5 - 5%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + 5%5 >= 7 THEN 5%5 + 2 ELSE 5%5 END, GETDATE())
WHEN DATEPART(DW,DATEADD(DAY, (((5+1) - (5+1)%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + (5+1)%5 >= 7 THEN (5+1)%5 + 2 ELSE (5+1)%5 END, GETDATE())) IN (3, 6)
THEN DATEADD(DAY, (((5+1) - (5+1)%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + (5+1)%5 >= 7 THEN (5+1)%5 + 2 ELSE (5+1)%5 END, GETDATE())
WHEN DATEPART(DW,DATEADD(DAY, (((5+2) - (5+2)%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + (5+2)%5 >= 7 THEN (5+2)%5 + 2 ELSE (5+2)%5 END, GETDATE())) IN (3, 6)
THEN DATEADD(DAY, (((5+2) - (5+2)%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + (5+2)%5 >= 7 THEN (5+2)%5 + 2 ELSE (5+2)%5 END, GETDATE())
END
WHEN PartDtl1.Plant = 'ROC' THEN
CASE
WHEN DATEPART(DW,DATEADD(DAY, ((5 - 5%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + 5%5 >= 7 THEN 5%5 + 2 ELSE 5%5 END, GETDATE())) IN (2, 5)
THEN DATEADD(DAY, ((5 - 5%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + 5%5 >= 7 THEN 5%5 + 2 ELSE 5%5 END, GETDATE())
WHEN DATEPART(DW,DATEADD(DAY, (((5+1) - (5+1)%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + (5+1)%5 >= 7 THEN (5+1)%5 + 2 ELSE (5+1)%5 END, GETDATE())) IN (2, 5)
THEN DATEADD(DAY, (((5+1) - (5+1)%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + (5+1)%5 >= 7 THEN (5+1)%5 + 2 ELSE (5+1)%5 END, GETDATE())
WHEN DATEPART(DW,DATEADD(DAY, (((5+2) - (5+2)%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + (5+2)%5 >= 7 THEN (5+2)%5 + 2 ELSE (5+2)%5 END, GETDATE())) IN (2, 5)
THEN DATEADD(DAY, (((5+2) - (5+2)%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + (5+2)%5 >= 7 THEN (5+2)%5 + 2 ELSE (5+2)%5 END, GETDATE())
END
END))
ELSE DATEADD(DAY, 1, (CASE
WHEN PartDtl1.Plant = 'MTL' THEN
CASE
WHEN DATEPART(DW,DATEADD(DAY, ((5 - 5%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + 5%5 >= 7 THEN 5%5 + 2 ELSE 5%5 END, GETDATE())) IN (3, 6)
THEN DATEADD(DAY, ((5 - 5%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + 5%5 >= 7 THEN 5%5 + 2 ELSE 5%5 END, GETDATE())
WHEN DATEPART(DW,DATEADD(DAY, (((5+1) - (5+1)%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + (5+1)%5 >= 7 THEN (5+1)%5 + 2 ELSE (5+1)%5 END, GETDATE())) IN (3, 6)
THEN DATEADD(DAY, (((5+1) - (5+1)%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + (5+1)%5 >= 7 THEN (5+1)%5 + 2 ELSE (5+1)%5 END, GETDATE())
WHEN DATEPART(DW,DATEADD(DAY, (((5+2) - (5+2)%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + (5+2)%5 >= 7 THEN (5+2)%5 + 2 ELSE (5+2)%5 END, GETDATE())) IN (3, 6)
THEN DATEADD(DAY, (((5+2) - (5+2)%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + (5+2)%5 >= 7 THEN (5+2)%5 + 2 ELSE (5+2)%5 END, GETDATE())
END
WHEN PartDtl1.Plant = 'ROC' THEN
CASE
WHEN DATEPART(DW,DATEADD(DAY, ((5 - 5%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + 5%5 >= 7 THEN 5%5 + 2 ELSE 5%5 END, GETDATE())) IN (2, 5)
THEN DATEADD(DAY, ((5 - 5%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + 5%5 >= 7 THEN 5%5 + 2 ELSE 5%5 END, GETDATE())
WHEN DATEPART(DW,DATEADD(DAY, (((5+1) - (5+1)%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + (5+1)%5 >= 7 THEN (5+1)%5 + 2 ELSE (5+1)%5 END, GETDATE())) IN (2, 5)
THEN DATEADD(DAY, (((5+1) - (5+1)%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + (5+1)%5 >= 7 THEN (5+1)%5 + 2 ELSE (5+1)%5 END, GETDATE())
WHEN DATEPART(DW,DATEADD(DAY, (((5+2) - (5+2)%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + (5+2)%5 >= 7 THEN (5+2)%5 + 2 ELSE (5+2)%5 END, GETDATE())) IN (2, 5)
THEN DATEADD(DAY, (((5+2) - (5+2)%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + (5+2)%5 >= 7 THEN (5+2)%5 + 2 ELSE (5+2)%5 END, GETDATE())
END
END))
END) as [Calculated_NextStdTORcvDate],
((ISNULL(OnHand2.Calculated_OnHandqty, 0)) + SUM((SUM(
CASE
WHEN PartDtl1.RequirementFlag = 0 THEN PartDtl1.Quantity
ELSE 0
END
)) - (SUM(
CASE
WHEN PartDtl1.RequirementFlag = 1 THEN PartDtl1.Quantity
ELSE 0
END
))) OVER (PARTITION BY PartDtl1.Plant, PartDtl1.PartNum ORDER BY PartDtl1.Plant, PartDtl1.PartNum, PartDtl1.DueDate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)) as [Calculated_OnHandAtDate]
from PostUpgrade.Erp.PartDtl as PartDtl1
left outer join (select
(LEFT(WhseBin.WarehouseCode, 3)) as [Calculated_Plant],
[PartBin].[PartNum] as [PartBin_PartNum],
(SUM(PartBin.OnhandQty)) as [Calculated_OnHandQty]
from PostUpgrade.Erp.PartBin as PartBin
inner join PostUpgrade.Erp.WhseBin as WhseBin on
PartBin.Company = WhseBin.Company
and PartBin.WarehouseCode = WhseBin.WarehouseCode
and PartBin.BinNum = WhseBin.BinNum
and ( WhseBin.NonNettable = 0 )
group by (LEFT(WhseBin.WarehouseCode, 3)),
[PartBin].[PartNum]) as OnHand2 on
PartDtl1.PartNum = OnHand2.PartBin_PartNum
and PartDtl1.Plant = OnHand2.Calculated_Plant
left outer join PostUpgrade.Erp.OrderHed as OrderHed3 on
PartDtl1.Company = OrderHed3.Company
and PartDtl1.OrderNum = OrderHed3.OrderNum
left outer join PostUpgrade.Erp.OrderHed as OrderHed4 on
PartDtl1.Company = OrderHed4.Company
and LEFT(PartDtl1.JobNum, 6) = CAST(OrderHed4.OrderNum AS nvarchar(6))
inner join PostUpgrade.dbo.Part as Part3 on
PartDtl1.Company = Part3.Company
and PartDtl1.PartNum = Part3.PartNum
and ( Part3.PriceListItem_c = 1 )
inner join PostUpgrade.Erp.PartPlant as PartPlant2 on
PartDtl1.Company = PartPlant2.Company
and PartDtl1.PartNum = PartPlant2.PartNum
and PartDtl1.Plant = PartPlant2.Plant
and ( PartPlant2.SourceType = 'T' )
where (PartDtl1.SourceFile in ('JM', 'OR', 'PO', 'PT', 'TO') and ((PartDtl1.SourceFile = 'TO' and PartDtl1.TFOrdNum like 'TO%' ) or PartDtl1.SourceFile <> 'TO' ))
and (PartDtl1.DueDate <= (CASE
WHEN DATEPART(DW, DATEADD(DAY, 1, (CASE
WHEN PartDtl1.Plant = 'MTL' THEN
CASE
WHEN DATEPART(DW,DATEADD(DAY, ((5 - 5%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + 5%5 >= 7 THEN 5%5 + 2 ELSE 5%5 END, GETDATE())) IN (3, 6)
THEN DATEADD(DAY, ((5 - 5%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + 5%5 >= 7 THEN 5%5 + 2 ELSE 5%5 END, GETDATE())
WHEN DATEPART(DW,DATEADD(DAY, (((5+1) - (5+1)%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + (5+1)%5 >= 7 THEN (5+1)%5 + 2 ELSE (5+1)%5 END, GETDATE())) IN (3, 6)
THEN DATEADD(DAY, (((5+1) - (5+1)%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + (5+1)%5 >= 7 THEN (5+1)%5 + 2 ELSE (5+1)%5 END, GETDATE())
WHEN DATEPART(DW,DATEADD(DAY, (((5+2) - (5+2)%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + (5+2)%5 >= 7 THEN (5+2)%5 + 2 ELSE (5+2)%5 END, GETDATE())) IN (3, 6)
THEN DATEADD(DAY, (((5+2) - (5+2)%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + (5+2)%5 >= 7 THEN (5+2)%5 + 2 ELSE (5+2)%5 END, GETDATE())
END
WHEN PartDtl1.Plant = 'ROC' THEN
CASE
WHEN DATEPART(DW,DATEADD(DAY, ((5 - 5%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + 5%5 >= 7 THEN 5%5 + 2 ELSE 5%5 END, GETDATE())) IN (2, 5)
THEN DATEADD(DAY, ((5 - 5%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + 5%5 >= 7 THEN 5%5 + 2 ELSE 5%5 END, GETDATE())
WHEN DATEPART(DW,DATEADD(DAY, (((5+1) - (5+1)%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + (5+1)%5 >= 7 THEN (5+1)%5 + 2 ELSE (5+1)%5 END, GETDATE())) IN (2, 5)
THEN DATEADD(DAY, (((5+1) - (5+1)%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + (5+1)%5 >= 7 THEN (5+1)%5 + 2 ELSE (5+1)%5 END, GETDATE())
WHEN DATEPART(DW,DATEADD(DAY, (((5+2) - (5+2)%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + (5+2)%5 >= 7 THEN (5+2)%5 + 2 ELSE (5+2)%5 END, GETDATE())) IN (2, 5)
THEN DATEADD(DAY, (((5+2) - (5+2)%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + (5+2)%5 >= 7 THEN (5+2)%5 + 2 ELSE (5+2)%5 END, GETDATE())
END
END))) IN (1,7) THEN DATEADD(DAY, 3, (CASE
WHEN PartDtl1.Plant = 'MTL' THEN
CASE
WHEN DATEPART(DW,DATEADD(DAY, ((5 - 5%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + 5%5 >= 7 THEN 5%5 + 2 ELSE 5%5 END, GETDATE())) IN (3, 6)
THEN DATEADD(DAY, ((5 - 5%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + 5%5 >= 7 THEN 5%5 + 2 ELSE 5%5 END, GETDATE())
WHEN DATEPART(DW,DATEADD(DAY, (((5+1) - (5+1)%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + (5+1)%5 >= 7 THEN (5+1)%5 + 2 ELSE (5+1)%5 END, GETDATE())) IN (3, 6)
THEN DATEADD(DAY, (((5+1) - (5+1)%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + (5+1)%5 >= 7 THEN (5+1)%5 + 2 ELSE (5+1)%5 END, GETDATE())
WHEN DATEPART(DW,DATEADD(DAY, (((5+2) - (5+2)%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + (5+2)%5 >= 7 THEN (5+2)%5 + 2 ELSE (5+2)%5 END, GETDATE())) IN (3, 6)
THEN DATEADD(DAY, (((5+2) - (5+2)%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + (5+2)%5 >= 7 THEN (5+2)%5 + 2 ELSE (5+2)%5 END, GETDATE())
END
WHEN PartDtl1.Plant = 'ROC' THEN
CASE
WHEN DATEPART(DW,DATEADD(DAY, ((5 - 5%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + 5%5 >= 7 THEN 5%5 + 2 ELSE 5%5 END, GETDATE())) IN (2, 5)
THEN DATEADD(DAY, ((5 - 5%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + 5%5 >= 7 THEN 5%5 + 2 ELSE 5%5 END, GETDATE())
WHEN DATEPART(DW,DATEADD(DAY, (((5+1) - (5+1)%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + (5+1)%5 >= 7 THEN (5+1)%5 + 2 ELSE (5+1)%5 END, GETDATE())) IN (2, 5)
THEN DATEADD(DAY, (((5+1) - (5+1)%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + (5+1)%5 >= 7 THEN (5+1)%5 + 2 ELSE (5+1)%5 END, GETDATE())
WHEN DATEPART(DW,DATEADD(DAY, (((5+2) - (5+2)%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + (5+2)%5 >= 7 THEN (5+2)%5 + 2 ELSE (5+2)%5 END, GETDATE())) IN (2, 5)
THEN DATEADD(DAY, (((5+2) - (5+2)%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + (5+2)%5 >= 7 THEN (5+2)%5 + 2 ELSE (5+2)%5 END, GETDATE())
END
END))
ELSE DATEADD(DAY, 1, (CASE
WHEN PartDtl1.Plant = 'MTL' THEN
CASE
WHEN DATEPART(DW,DATEADD(DAY, ((5 - 5%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + 5%5 >= 7 THEN 5%5 + 2 ELSE 5%5 END, GETDATE())) IN (3, 6)
THEN DATEADD(DAY, ((5 - 5%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + 5%5 >= 7 THEN 5%5 + 2 ELSE 5%5 END, GETDATE())
WHEN DATEPART(DW,DATEADD(DAY, (((5+1) - (5+1)%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + (5+1)%5 >= 7 THEN (5+1)%5 + 2 ELSE (5+1)%5 END, GETDATE())) IN (3, 6)
THEN DATEADD(DAY, (((5+1) - (5+1)%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + (5+1)%5 >= 7 THEN (5+1)%5 + 2 ELSE (5+1)%5 END, GETDATE())
WHEN DATEPART(DW,DATEADD(DAY, (((5+2) - (5+2)%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + (5+2)%5 >= 7 THEN (5+2)%5 + 2 ELSE (5+2)%5 END, GETDATE())) IN (3, 6)
THEN DATEADD(DAY, (((5+2) - (5+2)%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + (5+2)%5 >= 7 THEN (5+2)%5 + 2 ELSE (5+2)%5 END, GETDATE())
END
WHEN PartDtl1.Plant = 'ROC' THEN
CASE
WHEN DATEPART(DW,DATEADD(DAY, ((5 - 5%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + 5%5 >= 7 THEN 5%5 + 2 ELSE 5%5 END, GETDATE())) IN (2, 5)
THEN DATEADD(DAY, ((5 - 5%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + 5%5 >= 7 THEN 5%5 + 2 ELSE 5%5 END, GETDATE())
WHEN DATEPART(DW,DATEADD(DAY, (((5+1) - (5+1)%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + (5+1)%5 >= 7 THEN (5+1)%5 + 2 ELSE (5+1)%5 END, GETDATE())) IN (2, 5)
THEN DATEADD(DAY, (((5+1) - (5+1)%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + (5+1)%5 >= 7 THEN (5+1)%5 + 2 ELSE (5+1)%5 END, GETDATE())
WHEN DATEPART(DW,DATEADD(DAY, (((5+2) - (5+2)%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + (5+2)%5 >= 7 THEN (5+2)%5 + 2 ELSE (5+2)%5 END, GETDATE())) IN (2, 5)
THEN DATEADD(DAY, (((5+2) - (5+2)%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + (5+2)%5 >= 7 THEN (5+2)%5 + 2 ELSE (5+2)%5 END, GETDATE())
END
END))
END) and (OrderHed3.ReadyToFulfill = 1 or OrderHed3.OrderNum is null) and (OrderHed4.ReadyToFulfill = 1 or OrderHed4.OrderNum is null))
group by [PartDtl1].[Plant],
[PartDtl1].[PartNum],
[PartDtl1].[DueDate],
(ISNULL(OnHand2.Calculated_OnHandqty, 0)),
(CASE
WHEN PartDtl1.Plant = 'MTL' THEN
CASE
WHEN DATEPART(DW,DATEADD(DAY, ((5 - 5%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + 5%5 >= 7 THEN 5%5 + 2 ELSE 5%5 END, GETDATE())) IN (3, 6)
THEN DATEADD(DAY, ((5 - 5%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + 5%5 >= 7 THEN 5%5 + 2 ELSE 5%5 END, GETDATE())
WHEN DATEPART(DW,DATEADD(DAY, (((5+1) - (5+1)%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + (5+1)%5 >= 7 THEN (5+1)%5 + 2 ELSE (5+1)%5 END, GETDATE())) IN (3, 6)
THEN DATEADD(DAY, (((5+1) - (5+1)%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + (5+1)%5 >= 7 THEN (5+1)%5 + 2 ELSE (5+1)%5 END, GETDATE())
WHEN DATEPART(DW,DATEADD(DAY, (((5+2) - (5+2)%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + (5+2)%5 >= 7 THEN (5+2)%5 + 2 ELSE (5+2)%5 END, GETDATE())) IN (3, 6)
THEN DATEADD(DAY, (((5+2) - (5+2)%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + (5+2)%5 >= 7 THEN (5+2)%5 + 2 ELSE (5+2)%5 END, GETDATE())
END
WHEN PartDtl1.Plant = 'ROC' THEN
CASE
WHEN DATEPART(DW,DATEADD(DAY, ((5 - 5%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + 5%5 >= 7 THEN 5%5 + 2 ELSE 5%5 END, GETDATE())) IN (2, 5)
THEN DATEADD(DAY, ((5 - 5%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + 5%5 >= 7 THEN 5%5 + 2 ELSE 5%5 END, GETDATE())
WHEN DATEPART(DW,DATEADD(DAY, (((5+1) - (5+1)%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + (5+1)%5 >= 7 THEN (5+1)%5 + 2 ELSE (5+1)%5 END, GETDATE())) IN (2, 5)
THEN DATEADD(DAY, (((5+1) - (5+1)%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + (5+1)%5 >= 7 THEN (5+1)%5 + 2 ELSE (5+1)%5 END, GETDATE())
WHEN DATEPART(DW,DATEADD(DAY, (((5+2) - (5+2)%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + (5+2)%5 >= 7 THEN (5+2)%5 + 2 ELSE (5+2)%5 END, GETDATE())) IN (2, 5)
THEN DATEADD(DAY, (((5+2) - (5+2)%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + (5+2)%5 >= 7 THEN (5+2)%5 + 2 ELSE (5+2)%5 END, GETDATE())
END
END),
(CASE
WHEN DATEPART(DW, DATEADD(DAY, 1, (CASE
WHEN PartDtl1.Plant = 'MTL' THEN
CASE
WHEN DATEPART(DW,DATEADD(DAY, ((5 - 5%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + 5%5 >= 7 THEN 5%5 + 2 ELSE 5%5 END, GETDATE())) IN (3, 6)
THEN DATEADD(DAY, ((5 - 5%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + 5%5 >= 7 THEN 5%5 + 2 ELSE 5%5 END, GETDATE())
WHEN DATEPART(DW,DATEADD(DAY, (((5+1) - (5+1)%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + (5+1)%5 >= 7 THEN (5+1)%5 + 2 ELSE (5+1)%5 END, GETDATE())) IN (3, 6)
THEN DATEADD(DAY, (((5+1) - (5+1)%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + (5+1)%5 >= 7 THEN (5+1)%5 + 2 ELSE (5+1)%5 END, GETDATE())
WHEN DATEPART(DW,DATEADD(DAY, (((5+2) - (5+2)%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + (5+2)%5 >= 7 THEN (5+2)%5 + 2 ELSE (5+2)%5 END, GETDATE())) IN (3, 6)
THEN DATEADD(DAY, (((5+2) - (5+2)%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + (5+2)%5 >= 7 THEN (5+2)%5 + 2 ELSE (5+2)%5 END, GETDATE())
END
WHEN PartDtl1.Plant = 'ROC' THEN
CASE
WHEN DATEPART(DW,DATEADD(DAY, ((5 - 5%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + 5%5 >= 7 THEN 5%5 + 2 ELSE 5%5 END, GETDATE())) IN (2, 5)
THEN DATEADD(DAY, ((5 - 5%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + 5%5 >= 7 THEN 5%5 + 2 ELSE 5%5 END, GETDATE())
WHEN DATEPART(DW,DATEADD(DAY, (((5+1) - (5+1)%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + (5+1)%5 >= 7 THEN (5+1)%5 + 2 ELSE (5+1)%5 END, GETDATE())) IN (2, 5)
THEN DATEADD(DAY, (((5+1) - (5+1)%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + (5+1)%5 >= 7 THEN (5+1)%5 + 2 ELSE (5+1)%5 END, GETDATE())
WHEN DATEPART(DW,DATEADD(DAY, (((5+2) - (5+2)%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + (5+2)%5 >= 7 THEN (5+2)%5 + 2 ELSE (5+2)%5 END, GETDATE())) IN (2, 5)
THEN DATEADD(DAY, (((5+2) - (5+2)%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + (5+2)%5 >= 7 THEN (5+2)%5 + 2 ELSE (5+2)%5 END, GETDATE())
END
END))) IN (1,7) THEN DATEADD(DAY, 3, (CASE
WHEN PartDtl1.Plant = 'MTL' THEN
CASE
WHEN DATEPART(DW,DATEADD(DAY, ((5 - 5%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + 5%5 >= 7 THEN 5%5 + 2 ELSE 5%5 END, GETDATE())) IN (3, 6)
THEN DATEADD(DAY, ((5 - 5%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + 5%5 >= 7 THEN 5%5 + 2 ELSE 5%5 END, GETDATE())
WHEN DATEPART(DW,DATEADD(DAY, (((5+1) - (5+1)%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + (5+1)%5 >= 7 THEN (5+1)%5 + 2 ELSE (5+1)%5 END, GETDATE())) IN (3, 6)
THEN DATEADD(DAY, (((5+1) - (5+1)%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + (5+1)%5 >= 7 THEN (5+1)%5 + 2 ELSE (5+1)%5 END, GETDATE())
WHEN DATEPART(DW,DATEADD(DAY, (((5+2) - (5+2)%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + (5+2)%5 >= 7 THEN (5+2)%5 + 2 ELSE (5+2)%5 END, GETDATE())) IN (3, 6)
THEN DATEADD(DAY, (((5+2) - (5+2)%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + (5+2)%5 >= 7 THEN (5+2)%5 + 2 ELSE (5+2)%5 END, GETDATE())
END
WHEN PartDtl1.Plant = 'ROC' THEN
CASE
WHEN DATEPART(DW,DATEADD(DAY, ((5 - 5%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + 5%5 >= 7 THEN 5%5 + 2 ELSE 5%5 END, GETDATE())) IN (2, 5)
THEN DATEADD(DAY, ((5 - 5%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + 5%5 >= 7 THEN 5%5 + 2 ELSE 5%5 END, GETDATE())
WHEN DATEPART(DW,DATEADD(DAY, (((5+1) - (5+1)%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + (5+1)%5 >= 7 THEN (5+1)%5 + 2 ELSE (5+1)%5 END, GETDATE())) IN (2, 5)
THEN DATEADD(DAY, (((5+1) - (5+1)%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + (5+1)%5 >= 7 THEN (5+1)%5 + 2 ELSE (5+1)%5 END, GETDATE())
WHEN DATEPART(DW,DATEADD(DAY, (((5+2) - (5+2)%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + (5+2)%5 >= 7 THEN (5+2)%5 + 2 ELSE (5+2)%5 END, GETDATE())) IN (2, 5)
THEN DATEADD(DAY, (((5+2) - (5+2)%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + (5+2)%5 >= 7 THEN (5+2)%5 + 2 ELSE (5+2)%5 END, GETDATE())
END
END))
ELSE DATEADD(DAY, 1, (CASE
WHEN PartDtl1.Plant = 'MTL' THEN
CASE
WHEN DATEPART(DW,DATEADD(DAY, ((5 - 5%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + 5%5 >= 7 THEN 5%5 + 2 ELSE 5%5 END, GETDATE())) IN (3, 6)
THEN DATEADD(DAY, ((5 - 5%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + 5%5 >= 7 THEN 5%5 + 2 ELSE 5%5 END, GETDATE())
WHEN DATEPART(DW,DATEADD(DAY, (((5+1) - (5+1)%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + (5+1)%5 >= 7 THEN (5+1)%5 + 2 ELSE (5+1)%5 END, GETDATE())) IN (3, 6)
THEN DATEADD(DAY, (((5+1) - (5+1)%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + (5+1)%5 >= 7 THEN (5+1)%5 + 2 ELSE (5+1)%5 END, GETDATE())
WHEN DATEPART(DW,DATEADD(DAY, (((5+2) - (5+2)%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + (5+2)%5 >= 7 THEN (5+2)%5 + 2 ELSE (5+2)%5 END, GETDATE())) IN (3, 6)
THEN DATEADD(DAY, (((5+2) - (5+2)%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + (5+2)%5 >= 7 THEN (5+2)%5 + 2 ELSE (5+2)%5 END, GETDATE())
END
WHEN PartDtl1.Plant = 'ROC' THEN
CASE
WHEN DATEPART(DW,DATEADD(DAY, ((5 - 5%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + 5%5 >= 7 THEN 5%5 + 2 ELSE 5%5 END, GETDATE())) IN (2, 5)
THEN DATEADD(DAY, ((5 - 5%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + 5%5 >= 7 THEN 5%5 + 2 ELSE 5%5 END, GETDATE())
WHEN DATEPART(DW,DATEADD(DAY, (((5+1) - (5+1)%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + (5+1)%5 >= 7 THEN (5+1)%5 + 2 ELSE (5+1)%5 END, GETDATE())) IN (2, 5)
THEN DATEADD(DAY, (((5+1) - (5+1)%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + (5+1)%5 >= 7 THEN (5+1)%5 + 2 ELSE (5+1)%5 END, GETDATE())
WHEN DATEPART(DW,DATEADD(DAY, (((5+2) - (5+2)%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + (5+2)%5 >= 7 THEN (5+2)%5 + 2 ELSE (5+2)%5 END, GETDATE())) IN (2, 5)
THEN DATEADD(DAY, (((5+2) - (5+2)%5)/5)*7 + CASE WHEN DATEPART(DW, GETDATE()) + (5+2)%5 >= 7 THEN (5+2)%5 + 2 ELSE (5+2)%5 END, GETDATE())
END
END))
END)) as DailyTimePhase
group by [DailyTimePhase].[PartDtl1_Plant],
[DailyTimePhase].[PartDtl1_PartNum]) as TimePhase on
PartPlant.Plant = TimePhase.PartDtl1_Plant
and PartPlant.PartNum = TimePhase.PartDtl1_PartNum
left outer join (select
[PartRev].[Plant] as [PartRev_Plant],
[PartRev].[PartNum] as [PartRev_PartNum],
[PartRev].[RevisionNum] as [PartRev_RevisionNum],
(ROW_NUMBER() OVER (PARTITION BY PartRev.Plant, PartRev.PartNum
ORDER BY PartRev.RevisionNum DESC)) as [Calculated_rowNum]
from PostUpgrade.Erp.PartRev as PartRev
inner join PostUpgrade.dbo.Part as Part on
PartRev.Company = Part.Company
and PartRev.PartNum = Part.PartNum
and ( Part.PriceListItem_c = 1 )
where (PartRev.Approved = 1 and PartRev.EffectiveDate <= GETDATE())) as rev on
PartPlant.TransferPlant = rev.PartRev_Plant
and PartPlant.PartNum = rev.PartRev_PartNum
and ( rev.Calculated_rowNum = 1 )
inner join PostUpgrade.dbo.Part as Part2 on
PartPlant.Company = Part2.Company
and PartPlant.PartNum = Part2.PartNum
and ( Part2.PriceListItem_c = 1 )
left outer join (select
[TFOrdHed1].[Plant] as [TFOrdHed1_Plant],
[TFOrdDtl2].[PartNum] as [TFOrdDtl2_PartNum],
(SUM(TFOrdDtl2.SellingQty - TFOrdDtl2.SellingShippedQty)) as [Calculated_FirmTODemandQty]
from PostUpgrade.Erp.TFOrdHed as TFOrdHed1
inner join PostUpgrade.Erp.TFOrdDtl as TFOrdDtl2 on
TFOrdHed1.Company = TFOrdDtl2.Company
and TFOrdHed1.TFOrdNum = TFOrdDtl2.TFOrdNum
and ( (TFOrdDtl2.SellingQty <> TFOrdDtl2.SellingShippedQty and TFOrdDtl2.OpenLine = 1 ) )
group by [TFOrdHed1].[Plant],
[TFOrdDtl2].[PartNum]) as FirmTODemand on
PartPlant.Plant = FirmTODemand.TFOrdHed1_Plant
and PartPlant.PartNum = FirmTODemand.TFOrdDtl2_PartNum
left outer join (select
[PartDtl].[Plant] as [PartDtl_Plant],
[PartDtl].[PartNum] as [PartDtl_PartNum],
(SUM(PartDtl.Quantity)) as [Calculated_JobDemandQty]
from PostUpgrade.Erp.PartDtl as PartDtl
inner join PostUpgrade.dbo.Part as Part4 on
PartDtl.Company = Part4.Company
and PartDtl.PartNum = Part4.PartNum
and ( Part4.PriceListItem_c = 1 )
where (PartDtl.SourceFile = 'JM' and PartDtl.JobFirm = 1)
group by [PartDtl].[Plant],
[PartDtl].[PartNum]) as JobDemand on
JobDemand.PartDtl_Plant = PartPlant.Plant
and JobDemand.PartDtl_PartNum = PartPlant.PartNum
where (PartPlant.SourceType = 'T' and PartPlant.Plant in ('MTL', 'ROC'))) as PartsList
left outer join (select
[PODetail].[Company] as [PODetail_Company],
[PORel].[Plant] as [PORel_Plant],
[PODetail].[PartNum] as [PODetail_PartNum],
(SUM(PORel.XRelQty - PORel.ArrivedQty)) as [Calculated_OpenQty],
[PORel].[DueDate] as [PORel_DueDate]
from PostUpgrade.Erp.POHeader as POHeader
inner join PostUpgrade.Erp.PODetail as PODetail on
POHeader.Company = PODetail.Company
and POHeader.PONum = PODetail.PONUM
and ( PODetail.OpenLine = 1 )
inner join PostUpgrade.Erp.PORel as PORel on
PODetail.Company = PORel.Company
and PODetail.PONUM = PORel.PONum
and PODetail.POLine = PORel.POLine
and ( PORel.OpenRelease = 1 )
inner join PostUpgrade.dbo.Part as Part1 on
PODetail.Company = Part1.Company
and PODetail.PartNum = Part1.PartNum
and ( Part1.PriceListItem_c = 1 )
where (POHeader.OpenOrder = 1)
and ((PORel.PromiseDt is not null or POHeader.Confirmed = 1 ))
group by [PODetail].[Company],
[PORel].[Plant],
[PODetail].[PartNum],
[PORel].[DueDate]) as OpenPOs on
PartsList.Calculated_NextStdTORcvDate >= OpenPOs.PORel_DueDate
and PartsList.PartPlant_Company = OpenPOs.PODetail_Company
and PartsList.PartPlant_Plant = OpenPOs.PORel_Plant
and PartsList.PartPlant_PartNum = OpenPOs.PODetail_PartNum
inner join PostUpgrade.Erp.PartPlant as PartPlant1 on
PartsList.PartPlant_Company = PartPlant1.Company
and PartsList.PartPlant_TransferPlant = PartPlant1.Plant
and PartsList.PartPlant_PartNum = PartPlant1.PartNum
and ( PartPlant1.SourceType = 'M' )
group by [PartsList].[PartPlant_Company],
[PartsList].[PartPlant_Plant],
[PartsList].[PartPlant_TransferPlant],
[PartsList].[PartPlant_PartNum],
[PartsList].[PartPlant_MinimumQty],
[PartsList].[Calculated_FirmDemand],
[PartsList].[Calculated_TODemand],
[PartsList].[Calculated_OnHandQty],
[PartsList].[Calculated_OpenTOQty],
[PartsList].[Calculated_ShippedTOQty],
[PartsList].[Calculated_NextStdTOShipDate],
[PartsList].[Calculated_NextStdTORcvDate],
[PartsList].[Calculated_QtyToExpedite],
[PartsList].[Calculated_ExpeditedDate],
[PartsList].[Calculated_NextExpTORcvDate],
[PartsList].[PartRev_RevisionNum],
[PartsList].[Calculated_5],
[PartsList].[Calculated_QtyToTransfer]
There is an execution setting in BAQ designer called Option. you put it there if you run from BAQ designer
1 Like
OK cool, I see that. testing…
OK, very cool but no, no change. In SSMS it’s one second, in BAQ it’s
Severity: Error, Text: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
The wait operation timed out.
Query returned 0 row(s).
Query has no more records to return.
Query execution total time: 300102.8307 ms.
in SSMS you run real query you took from log?
There’s been talk on here from @timshuwy I think, about using an external BAQ as a last resort, pointing back at the main DB to bypass the safeties in BAQ; is there an option argument to just do that in BAQ?
With the timeout, I can’t even generate an execution plan, but maybe that way I could at least do that.
Sorry, no - In the trace log I could only see 2 things:
(1) the display tag which was exactly what was shown in BAQ designer, and
(2) many other tags, each containing a formula or statement that was only a part of the whole.
I didn’t see an actual complete query
it sounds like you are looking into client trace log.
Sql statements are dumped in the server log.
1 Like