USE [test]
GO

/****** Object:  View [MF].[ProductionInventoryItem]    Script Date: 02.06.2021 12:12:13 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


ALTER VIEW [MF].[ProductionInventoryItem]
AS



SELECT Tow.[ItemCode]
, REPLACE(REPLACE(REPLACE(REPLACE(Tow.ItemName, CHAR(160), ''), CHAR(9), ''), CHAR(13), ''), CHAR(10), '') as [ItemName]
, Tow.[UoM]
, Tow.[Price]
, Tow.[Barcode]
, Tow.[SupplierID]
, Tow.[ItemMin]
, Tow.[ItemMax]
, Tow.[Active]
, Tow.[HMID]
, Tow.[Traceability]
, Tow.[Weight]
, Tow.[Volume]
, Tow.[Width]
, Tow.[Length]
, Tow.[Height]
, CONVERT(DECIMAL(12) ,Tow.[DeliveryDays]) AS [DeliveryDays]
, CAST(Tow.[EnableSerialNumbers] AS bit) AS [EnableSerialNumbers]
, Tow.[EnableLotNumbers]
, Tow.[SequencePattern]
, Tow.[DefaultReceipt]
, Tow.[QTYPrecision]
, Tow.[ItemGroupID]
, Wym.*
, Tow.OptimisticLockField
, Tow.metoda AS IsLifoMethod
FROM (SELECT 
TW.id AS HMID
, TW.kod AS ItemCode
, TW.metoda
, CAST(TW.nazwa as varchar(max)) AS ItemName
, ISNULL(TW.jm, '') AS UoM
, CONVERT(numeric(28, 5), ISNULL(C.cena, 0)) AS Price
, TW.kodpaskowy AS Barcode
, '' AS SupplierID
, TW.stanmin AS ItemMin
, TW.stanmax AS ItemMax
, CAST(TW.aktywny AS bit) AS Active
, MF.ProductionItemAttributes.[Weight]
, MF.ProductionItemAttributes.[Volume]
, MF.ProductionItemAttributes.[Width]
, MF.ProductionItemAttributes.[Length]
, MF.ProductionItemAttributes.[Height]
, MF.ProductionItemAttributes.DeliveryDays
, MF.ProductionItemAttributes.EnableSerialNumbers
, MF.ProductionItemAttributes.Traceability
, MF.ProductionItemAttributes.EnableLotNumbers
, MF.ProductionItemAttributes.SequencePattern
, MF.ProductionItemAttributes.DefaultReceipt
, MF.ProductionItemAttributes.QTYPrecision
, MF.ProductionItemAttributes.ItemGroupID
, ISNULL(MF.ProductionItemAttributes.OptimisticLockField, 0) + ISNULL(DATEPART(YEAR, TW.modifiedDate) + DATEPART(MONTH, TW.modifiedDate) + DATEPART(DAY, TW.modifiedDate) + DATEPART(SECOND, TW.modifiedDate), 0) AS OptimisticLockField
FROM HM.TW AS TW INNER JOIN
HM.CN AS C ON C.idpm = TW.id AND C.typ = 6 AND C.aktywna = 1 INNER JOIN
MF.ProductionItemAttributes ON TW.id = MF.ProductionItemAttributes.HMID AND TW.typ = 0 AND TW.subtyp = 0 AND C.dataend IS NULL AND TW.idlongname IS NULL

UNION

SELECT 
TW.id AS HMID
, TW.kod AS ItemCode
, TW.metoda
, REPLACE(CAST(TX.opis as varchar(max)) COLLATE Polish_BIN,nchar(0x00),'') AS ItemName
, ISNULL(TW.jm, '') AS UoM
, CONVERT(numeric(28, 5), ISNULL(C.cena, 0)) AS Price
, TW.kodpaskowy AS Barcode
, '' AS SupplierID
, TW.stanmin AS ItemMin
, TW.stanmax AS ItemMax
, CAST(TW.aktywny AS bit) AS Active
, ProductionItemAttributes_1.[Weight]
, ProductionItemAttributes_1.[Volume]
, ProductionItemAttributes_1.[Width]
, ProductionItemAttributes_1.[Length]
, ProductionItemAttributes_1.[Height]
, ProductionItemAttributes_1.DeliveryDays
, ProductionItemAttributes_1.EnableSerialNumbers
, ProductionItemAttributes_1.Traceability
, ProductionItemAttributes_1.EnableLotNumbers
, ProductionItemAttributes_1.SequencePattern
, ProductionItemAttributes_1.DefaultReceipt
, ProductionItemAttributes_1.QTYPrecision
, ProductionItemAttributes_1.ItemGroupID
, ISNULL(ProductionItemAttributes_1.OptimisticLockField, 0) + ISNULL(DATEPART(YEAR, TW.modifiedDate) + DATEPART(MONTH, TW.modifiedDate) + DATEPART(DAY, TW.modifiedDate) + DATEPART(SECOND, TW.modifiedDate), 0) AS OptimisticLockField
FROM HM.TW AS TW INNER JOIN
HM.CN AS C ON C.idpm = TW.id AND C.typ = 6 AND C.aktywna = 1 INNER JOIN
MF.ProductionItemAttributes AS ProductionItemAttributes_1 ON TW.id = ProductionItemAttributes_1.HMID INNER JOIN
HM.TX AS TX ON TX.id = TW.idlongname AND TW.typ = 0 AND TW.subtyp = 0 AND C.dataend IS NULL AND TW.idlongname IS NOT NULL) AS Tow 


--LEFT OUTER JOIN (SELECT * FROM SSCommon.STElements WHERE ElementKindId = 19) e ON (e.Shortcut = ItemCode)
--LEFT OUTER JOIN SSCommon.ProductClassification Wym ON (Wym.ElementId = e.ElementId)

LEFT OUTER JOIN
 (SELECT * FROM SSCommon.STElements WHERE ElementKindId = 19) e ON (e.Shortcut = ItemCode)
LEFT OUTER JOIN SSCommon.ProductClassification Wym ON (Wym.ElementId = e.ElementId)




GO