EXEC(N'CREATE OR ALTER VIEW HM.SalePositionsDocumentsCardIndexView
AS 
SELECT
	poz.id   
	 ,nag.id as Super  
	 ,nag.kod AS Code  
	 ,nag.datasp AS SellDate  
	 ,ISNULL(nag.data,GETDATE()) AS IssueDate  
	 ,poz.lp  
	 ,nag.typ_dk AS DocType  
	 ,nag.nazwa AS DocKind  
	 ,sums.Quantity  
	 ,sums.InputQuantity  
	 ,case isnull(poz.idpozkoryg,-1) WHEN -1 then Case When tw.subtypext = 1 then poz.iloscwp When tw.subtypext = 4 then poz.iloscwp Else ISNULL(oper.ilosc_rozl,0) END   
	 else 0 end AS QuantitySettledInWarehouse  
	 ,poz.jm  
	 ,poz.jmwp  
	 ,poz.kod AS ProductCode  
	,CASE WHEN tw.subtypext = 0 THEN ''artykuł''
		  WHEN tw.subtypext = 1 THEN ''usługa''
		  WHEN tw.subtypext = 3 THEN ''komplet''
		  WHEN tw.subtypext = 4 THEN ''usługa marża''
          WHEN tw.subtypext = 5 THEN ''opakowanie''
		  ELSE ''''
		END AS ProductType
	 ,poz.opis AS ProductDescription  
	 ,poz.JPK_V7 AS GTU  
	 ,sums.NetValue  
	 ,sums.GrossValue  
	 --,poz.wartVat+poz.wartNetto AS GrossValue  
	 ,xt.kod AS RateVat  
	,CASE WHEN nag.bufor = 0 THEN ''wystawione'' ELSE ''w buforze'' END AS Buffer
	,nag.aktywny AS IsActive
	,CAST(CASE WHEN nag.ok = 0 THEN 0 ELSE 1 END AS BIT) AS SettledInCash
    ,CAST(CASE WHEN ISNULL(trans.status,32) < 32 AND nag.rr < 2 THEN ''Nie'' ELSE ''Tak'' END AS VARCHAR(3)) AS SettledInWarehouse
	,ISNULL(ctr.kod, '''') AS KhCode
	,ISNULL(AD.nazwa,'''') AS KhName 
	,ISNULL(AD.NIP, '''') AS NIP
	,kh.kod AS Department
	,poz.cena AS PriceJm
	,Case When poz.grupacen = 0 Then ''Brutto'' When poz.grupacen = 1 Then ''Netto'' End AS PriceType
	,poz.cenaBaza AS PriceList 
	 ,sums.PriceNettoPLN  
	 ,sums.PriceVat  
	 ,sums.PriceBruttoPLN  
	 ,IIF(poz.cenaBazaPl = 0,'''',(1 -(poz.cena/poz.cenaBazaPl)) * 100) AS Discount   
	 ,nag.waluta AS Currency  
	 ,sp.*
FROM HM.DP poz
JOIN HM.DK nag ON poz.super = nag.id 
JOIN HM.XT xt ON xt.id = poz.stvat 
JOIN HM.AppUserDepartments UsrRights ON UsrRights.DepartmentId = nag.magazyn
JOIN HM.DocumentTypePermission DocPermission ON DocPermission.UserId=UsrRights.UserId AND DocPermission.DocumentTypeCode = nag.typ_dk
JOIN SSCommon.STGukView guk on ISNULL(guk.ContractorId,0)= ISNULL(nag.khid,0) 
CROSS APPLY
(
	SELECT
		SUM(pozSum.ilosc)  AS Quantity,
		SUM(pozSum.iloscwp)  AS InputQuantity,
		SUM(pozSum.walNetto) AS NetValue,
		SUM(pozSum.walBrutto)  AS GrossValue,
		SUM(pozSum.wartNetto)  AS PriceNettoPLN,
		SUM(pozSum.wartNetto + pozSum.wartVat) AS PriceBruttoPLN,
		SUM(pozSum.wartVat) AS PriceVat
	FROM HM.DP pozSum
	WHERE pozSum.super = nag.id AND pozSum.lp = poz.lp AND pozSum.superkoryg = poz.superkoryg
)sums
LEFT JOIN hm.GetCodeSettledInWarehouseForSalesPositionCardIndexView codesettled ON poz.id=codesettled.id  
LEFT JOIN hm.op oper ON oper.owner_id=poz.id AND oper.owner_base=18  
LEFT JOIN HM.TR trans ON (trans.owner_id = CASE WHEN nag.iddokkoryg IS NULL THEN nag.id ELSE nag.iddokkoryg END) AND TRans.owner_base=16 AND TRans.typ =1  
LEFT JOIN HM.AD ON AD.id = nag.khadid  
LEFT JOIN SSCommon.vKh ctr ON ctr.id = nag.khid  
LEFT JOIN HM.KH ON kh.id = nag.magazyn  
LEFT JOIN HM.TW ON tw.id = poz.idtw and tw.subtypext <> 3  
LEFT JOIN SSCommon.HMF_SalesInvoicePositionClassification sp ON sp.ElementId = poz.id  
WHERE poz.kompensata=0 AND nag.typ=0  
')

GO