USE [KoltonNew1808]
GO

/****** Object:  View [dbo].[ApiWmsBaseLinesView]    Script Date: 19.08.2022 15:43:27 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO






 ALTER VIEW [dbo].[ApiWmsBaseLinesView] as
 
 --d.Korekta,  pn.IloscValue,
 select 
 --d.Korekta,  pn.IloscValue,
 p.Lp Lp, 0 [SerialNumberId],
 p.ID LineId, p.Dokument ObjectId, 10 ObjectType,
 p.Towar ItemId, 
t.Kod ItemCode, t.Nazwa ItemName, 
 case WHEN (d.Korekta=0) THEN p.IloscMagazynuValue   
     else  ISNUll(pn.IloscMagazynuValue,0) - p.IloscMagazynuValue   
   end Quantity
, p.IloscMagazynuSymbol UM , 

 case WHEN (d.Korekta=0) THEN p.IloscValue   
     else  ISNUll(pn.IloscValue,0) - p.IloscValue   
   end  [Quantity2], 
   p.IloscSymbol UM2, 
   
	   case when (p.IloscMagazynuValue <>0) then
   cast( (p.IloscValue/p.IloscMagazynuValue)  as decimal(16,4))
   else cast(0 as decimal(16,4)) end
   [UMRatio],
t.EAN Barcode,

-- partie albo tak że numer partii jest jako 
--ISNULL(NrPartii.Data,'') ItemFeatures, 
ISNULL(NrPartii.Data,'') ItemFeatures, 

--'' FeatureType,
--ISNULL(FeatureType.Data,'') as FeatureType,
case when (tow.WyborPartiiOpcjeOpcje = 4) then 't1' else '' end FeatureType,

-- jeśli chcemy żeby użytkownik potwierdził tą cechę dajemy 1 
-- 0 [FeaturesRequired],
case when (ISNULL( NrPartii.Data,'x')<>'x') then 1 else 0 end FeaturesRequired, -- czyli jeśli jest partia to ją poptwierdź


-- dowyboru czy cchemy id pozycj traktować  jako id czy numer partii
case when (tow.WyborPartiiOpcjeOpcje = 4) then RIGHT('00000000000'+CAST(ISNULL(dost.Id, p.ID) AS VARCHAR(11)),11)  else '' end StockBarcode,
--ISNULL(NrPartii.Data,'') StockBarcode,



'' [Description]
  from dbo.PozycjeDokHan p  JOIN dbo.Towary t ON p.Towar = t.ID and t.Typ <>2
  JOIN dbo.DokHandlowe d ON d.ID = p.Dokument
  JOIN dbo.Towary tow ON tow.ID = p.Towar
  LEFT OUTER JOIN dbo.Features AS FeatureType ON FeatureType.ParentType = 'Towary' AND FeatureType.Name = 'WMSFeatureType' AND FeatureType.Parent = t.ID
  LEFT OUTER JOIN dbo.PozRelHandlowej pr ON PodrzednaIdent = p.Ident and PodrzednyDok = p.Dokument and d.Korekta=1
LEFT OUTER JOIN dbo.PozycjeDokHan pn ON pn.Ident = NadrzednaIdent and NadrzednyDok = pn.Dokument
LEFT OUTER JOIN dbo.PozycjeDokHan dost ON p.DostawaPierwotna = dost.ID

LEFT OUTER JOIN dbo.Features NrPartii ON NrPartii.Parent = ISNULL(dost.Id, p.ID) and NrPartii.Name = 'NrPartii' and NrPartii.Lp =0 -- jeśli nie ma
  where
  ((d.Korekta =1 and (p.IloscMagazynuValue   -ISNUll(pn.IloscMagazynuValue,0)) <>0 ) OR d.Korekta=0)
  --and p.Dokument = 3441
--select * from dbo.PozRelHandlowej

--select * from dbo.DokHandlowe where Id = 3441

-- PWP przyjęcia 

/*
UNION ALL
SELECT  
1 Lp, 0 SerialNumberId, Id LineId, Id [ObjectId],504 [ObjectType]
      , [ProductId] [ItemId] ,[ProductCode] [ItemCode],[ProductName] [ItemName]
      , [DeliveredToTheWarehouse] [Quantity] , '' [UM] , [DeliveredToTheWarehouse] [Quantity2], '' [UM2]
      , cast ( 0 as decimal) [UMRatio] , '' [Barcode] , '' [ItemFeatures], '' [FeatureType] 
	  ,0 [FeaturesRequired] ,'' StockBarcode
	  ,ISNULL(Description,'') [Description] 
  FROM [dbo].[ApiProdActivities] where  ErpId <>0 and  [DeliveredToTheWarehouse] <>0 and [Confirmed] =1  
  */
  UNION ALL
select 0 Lp, ISNULL(sn.Id,0) SerialNumberId  , /* -  ISNULL(ObjectElementId,0) */ TechnLineId [LineId], [ObjectId]
,pl.[ObjectType]
,pl.[ItemId] ,pl.[ItemCode] ,pl.[ItemName]  ,[Quantity] ,ISNULL(UM,'szt' ) UM,
[Quantity] Quantity2  ,ISNULL(UM,'szt' ) UM2 
     , cast ( 0 as decimal) [UMRatio] 
	   , ISNULL(sn.SN, '') [Barcode] 
	   , ISNULL(sn.SN, ISNULL(Features,''))   [ItemFeatures], 'SN' [FeatureType] 
	  ,case when ( ISNULL(sn.Id,0)<>0) then 2 else  0 end [FeaturesRequired] 
	  ,ISNULL(sn.SN, '') StockBarcode
	  ,'' [Description] 
	  from dbo.ApiProdLines pl
	  LEFT OUTER JOIN dbo.ApiSerialNumbers sn  ON sn.ObjectType = pl.ObjectType and sn.ElementId = pl.ObjectId


  -- Wydania do operacji
  /* -- komentuje bo już jest to obsłużone inaczej 
UNION ALL 
	select 0 Lp ,0 SerialNumberId ,Id [LineId] 	    ,Id [ObjectId]  ,501 [ObjectType]  ,[ItemId] ,[ItemCode] ,[ItemName] 
	
      ,case when ( Quantity- RealizedQuantity <= Stock) then Quantity- RealizedQuantity else Stock end [Quantity] 
	  ,[UM] 
	  , case when ( Quantity- RealizedQuantity <= Stock) then Quantity- RealizedQuantity else Stock end Quantity2  ,UM UM2 
	   , cast ( 0 as decimal) [UMRatio] 
	   , '' [Barcode] 
	   , '' [ItemFeatures], '' [FeatureType] 
	  ,0 [FeaturesRequired] ,'' StockBarcode
	  ,'' [Description] 

	  from [dbo].[ApiProdTechnLinesView] where TechnLineTYpe = 0
	  */
GO


