


CREATE VIEW [dbo].[ApiProdOperationsAllView] AS

select
case when ( ope.ProdDocStateId =0 OR ope.ProdDocStateId =50) then ope.ProdDocStateId 
else case when ( ISNULL(opeSum.OperatorId,-1) =-1) then 10
else case when EXISTS( select * from dbo.ApiUnfinishedProdActivities where OperationId = ope.ID) then 20
else case when EXISTS  ( select * from dbo.ApiProdActivities where OperationId = ope.ID and StateId=50) then 50
else 30
end end  end end StateId ,

ISNULL(opeSum.OperatorId,0) OperatorId ,ISNULL(opeSum.SumQuantity, cast (0 as decimal)) SumQuantity ,ISNULL(opeSum.TS, CURRENT_TIMESTAMP) TS ,
ope.* 

from dbo.[ApiProdOperationsAllBaseView] ope
LEFT OUTER JOIN 
( select OperationId, SUM(Quantity) SumQuantity, MAX(TS) TS, MAX(OperatorId) OperatorId  from dbo.ApiProdActivities GROUP BY OperationId) 
opeSum ON opeSum.OperationId = ope.Id
GO


