
CREATE PROCEDURE [dbo].[ApiWmsGetTemplate]

	@DocumentId AS INT,
	@CustId as int,
	@DepartamentId as int,
	@DefId as int
AS 
BEGIN

IF (@DepartamentId <>0)
BEGIN
select 
p.ID [Id], p.ID [ErpId],p.[Lp] ,d.ID [DocumentId],0 [Direction]
      ,p.Towar [ItemId] ,t.Kod [ItemCode], t.Nazwa [ItemName], 
	  cast ( p.IloscMagazynuValue as decimal(16,4)) [OrgQuantity], cast(0 as decimal) [Quantity],   case when (p.IloscMagazynuValue <>0) then
   cast( (p.IloscValue/p.IloscMagazynuValue)  as decimal(16,4))
   else cast(0 as decimal(16,4)) end
   [UMRatio]
	  , p.IloscMagazynuSymbol [UM],  cast(0 as decimal) [Quantity2], p.IloscSymbol [UM2],
	  t.EAN [Barcode],0 [FeaturesRequired],  '' [ItemFeatures],'' StockBarcode,  0 [SerialNumberId],
	  0 [LocationId], ISNULL(stype.Data,'') [LocationAdress], 0 [OrgLocationId], ISNULL(stype.Data,'') [OrgLocationAdress],
	  CURRENT_TIMESTAMP [TS], '' [Description], cast(0 as decimal) [Stock], d.Magazyn [RepositoryId],
	  d.NumerPelnyZapisany [DocumentNumber], '' [FeatureType], 0 [OrgLineId]	   
from dbo.DokHandlowe d
JOIN dbo.Features f ON ParentType='DokHandlowe' and Name='Gabinet' and Parent = d.ID and d.Definicja = 109
JOIN dbo.PozycjeDokHan p ON d.Id = p.Dokument
JOIN dbo.Towary t ON p.Towar  = t.ID
LEFT OUTER JOIN dbo.Features stype ON stype.Parent = d.Id and stype.ParentType='DokHandlowe' and stype.Name='SpisTyp'
where f.DataKey =  @DepartamentId
END
ELSE
BEGIN

select 0 lp, 0 ErpId, l.ItemId Id, l.ItemId, l.ItemCode, l.ItemName, l.UM,0  [DocumentId],0 [Direction],
 cast ( SUM(l.OrgQuantity) as decimal(16,4)) [OrgQuantity], cast(0 as decimal) [Quantity], Max(l.umRatio) [UMRatio]
 ,'' Barcode
  ,cast(0 as decimal) [Quantity2] , UM [UM2], l.FeaturesRequired [FeaturesRequired],  l.StockBarcode StockBarcode, l.ItemFeatures [ItemFeatures], 0 [SerialNumberId],
	  0 [LocationId], LocationAdress [LocationAdress], 0 [OrgLocationId], LocationAdress [OrgLocationAdress],
	  CURRENT_TIMESTAMP [TS], '' [Description], cast(0 as decimal) [Stock], 0 [RepositoryId],
	  '' [DocumentNumber], '' [FeatureType], 0 [OrgLineId]	
from
dbo.ApiWmsDocLines l JOIN 
dbo.ApiWmsDocuments d 
ON d.Id = l.DocumentId and
d.DefId = 1 and d.Direction=-1 and d.DepartamentId<>0 and d.StateId<1


GROUP BY l.ItemId, l.ItemCode, l.ItemName, l.UM, l.LocationAdress, l.[FeaturesRequired], l.Barcode, l.ItemFeatures, l.StockBarcode

END
	
END


