CREATE OR ALTER FUNCTION [HM].[ft_PurchasePositions] (
	@docId INT,
	@removeInvalidXMLCharacters BIT = 0
	)
RETURNS TABLE
AS
RETURN (
		WITH 
			NpRateId([Value]) AS (
				SELECT TOP (1) Id
				FROM HM.XT
				WHERE super = 10000
					AND subtyp = 7
					AND flag & 0x200 <> 0
				),
			ZeroRateId([Value]) AS (
				SELECT TOP (1) Id
				FROM HM.XT
				WHERE super = 10000
					AND subtyp = 3
				)			
		SELECT 
            -- pola techniczne - poza strukturą
			DP.subtyp,
			DP.kod																						            AS [ProductShortcut], -- pole niezdefiniowane ale przekazywane (wykorzystywane w podglądzie/REPX, ale także odczytywane w Update)

            -- pola BasePosition
            DP.id                                                                                                   AS [Id],
			dptr.ordinalnumber                                                                                      AS [OrdinalNumber],
			dptr.nodeid                                                                                             AS [NodeId],
			dptr.parentid                                                                                           AS [ParentId],
			dptr.userordinalnumber                                                                                  AS [UserOrdinalNumber],
			DP.super                                                                                                AS [DocumentId],
			DP.superkoryg                                                                                           AS [OriginalDocumentId],
            NULL                                                                                                    AS [CorrectedDocumentId],
			''                                                                                                      AS [CorrectedDocumentShortcut],
			DP.idpozkoryg																							AS [CorrectedPositionId],
			DP.kompensata                                                                                           AS [Compensation],
			IIF(DP.super = @docId, 'true', 'false')																    AS [IsDocumentPosition],
			IIF(DP.super = @docId
				AND DP.super != DP.superkoryg
				AND DP.idpozkoryg IS NOT NULL
				AND DP.kompensata = 1, 'true', 'false')															    AS [IsCorrectedPosition],
			IIF(DP.super = @docId
				AND DP.super != DP.superkoryg
				AND DP.idpozkoryg IS NOT NULL
				AND DP.kompensata = 0, 'true', 'false')															    AS [IsCorrectionPosition],
			IIF(DP.super = @docId
				AND DP.super != DP.superkoryg
				AND DP.idpozkoryg IS NULL
				AND DP.super = @docId, 'true', 'false')															    AS [IsNewCorrectPosition],
			LOWER(DP.guid)                                                                                          AS [Guid],
			DP.idtw                                                                                                 AS [ProductId],
		    IIF(@removeInvalidXMLCharacters = 0, DP.opis, HM.RemoveInvalidXMLCharacters(DP.opis))                   AS [Description],
			DP.jmwp                                                                                                 AS [UnitOfMeasurement],
			DP.jm                                                                                                   AS [EvidenceUnitOfMeasurement],
			IIF(HM.fnIsFlagSet(DP.flag, 0x1000) = 1, 'true', 'false')                                               AS [IsSet],
			IIF(HM.fnIsFlagSet(DP.flag, 0x4000) = 1, 'true', 'false')                                               AS [IsElementOfSet],
			CAST(ISNULL((
						SELECT ilosc_rozl
						FROM HM.OP
						WHERE owner_id = DP.id
							AND owner_base = 18
						), 0) AS DECIMAL(24, 4))                                                                    AS [CompletedQuantity],
			-IIF(DP.kompensata = 1, - 1, 1) * CAST(DP.iloscwp AS DECIMAL(24, 4))                                    AS [Quantity],
			CAST(DP.iloscjedn AS DECIMAL(35, 15))                                                                   AS [UnitQuantity],
			-IIF(DP.kompensata = 1, - 1, 1) * CAST(DP.ilosc AS DECIMAL(24, 4))                                      AS [EvidenceQuantity],
			CAST(IIF(DP.iloscwp = 0 OR DP.ilosc = 0, ISNULL((SELECT TOP(1) Multiplier 
                FROM HM.ProductsUnitsOfMeasure 
                WHERE TwId = TW.id and Uom = DP.jmwp),1), ROUND(DP.ilosc / DP.iloscwp, 4)) AS DECIMAL(24, 4))       AS [ConversionFactorUoM],
            HM.GetNetPrice(DP.cenawal, TW.zaokrag, DP.grupacen, DP.wartstvat)                                       AS [Price],
			ISNULL(TW.zaokrag, 5)                                                                                   AS [PriceRounding],
			-IIF(DP.kompensata = 1, - 1, 1) * CAST(DP.walNetto AS DECIMAL(24, 2))                                   AS [Value],
			ISNULL(IIF(TW.subtyp = 1, 'true', 'false'), 'false')                                                    AS [IsService],
            ISNULL(IIF(TW.subtypext = 4, 'true', 'false'), 'false')										            AS [IsMarginService],
			DP.schemat                                                                                              AS [AccountingPattern],
			DK.subtyp                                                                                               AS [BaseType],

            -- pola TradeBasePosition
			IIF(DK.subtyp = 46, IIF(DP.stvat = NpRateId.Value, NpRateId.Value, ZeroRateId.Value), DP.stvat)         AS [VatRateId],
            HM.GetNetPrice(DP.cena, TW.zaokrag, DP.grupacen, DP.wartstvat)                                          AS [PricePLN],
            HM.GetGrossPrice(DP.cenawal, TW.zaokrag, DP.grupacen, DP.wartstvat)                                     AS [PriceGross],
            HM.GetGrossPrice(DP.cena, TW.zaokrag, DP.grupacen, DP.wartstvat)                                        AS [PriceGrossPLN],
			-IIF(DP.kompensata = 1, - 1, 1) * CAST((DP.walBrutto - DP.walNetto) AS DECIMAL(24, 2))                  AS [ValueVat],
			-IIF(DP.kompensata = 1, - 1, 1) * CAST(DP.walBrutto AS DECIMAL(24, 2))                                  AS [ValueGross],
			-IIF(DP.kompensata = 1, - 1, 1) * CAST(DP.wartNetto AS DECIMAL(24, 2))                                  AS [ValuePLN],
			-IIF(DP.kompensata = 1, - 1, 1) * CAST(DP.wartVat AS DECIMAL(24, 2))                                    AS [ValueVatPLN],
			-IIF(DP.kompensata = 1, - 1, 1) * CAST(DP.wartNetto + DP.wartVat AS DECIMAL(24, 2))                     AS [ValueGrossPLN],
            CASE ISNULL(CASE WHEN OP.ilosc_rozl = 0 THEN 0 
                             WHEN HM.ROUND4(OP.ilosc - OP.ilosc_rozl) = 0 THEN 2 
                             ELSE 1 END, 0)
                WHEN 0 THEN 'Do przyjęcia' WHEN 1 THEN 'Przyjęte częściowo' ELSE 'Przyjęte' END                     AS [StockStatus],
			DP.rejestrVAT                                                                                           AS [VatRegister],
			IIF(DP.odwrotneObc = 1, 'true', 'false')                                                                AS [OO],
			IIF(((DP.flag & 0x0400) = 0x0400), 'true', 'false')                                                     AS [IsVatModifiedManually],

            -- pola PurchaseBasePosition

            -- pola PurchasePosition
			UZ.kodobcy                                                                                              AS [ForeignCode],
			-CAST(DP.cenabaztow AS DECIMAL(24,2))                                                                   AS [CostPLN],
			IIF(DP.vat50 = 1, 'true', 'false')                                                                      AS [Vat50],
			DP.rejestrVAT2                                                                                          AS [DueRegister],
            DP.katPodatkowa																					        AS [TaxCategoryId]

		FROM HM.FnDocumentPositionsTree(@docId) dptr
		    CROSS JOIN NpRateId
		    CROSS JOIN ZeroRateId
		    JOIN HM.DP DP ON dptr.Id = DP.id
		    JOIN HM.DK DK ON DK.id = DP.super
		    LEFT OUTER JOIN HM.TW ON TW.id = DP.idtw
		    LEFT OUTER JOIN HM.OP OP ON OP.owner_id = DP.id AND owner_base = 18
		    OUTER APPLY
		        (SELECT TOP(1) * 
                    FROM HM.UZ UZ 
                    WHERE UZ.idkh = DK.khid
			            AND UZ.iduz = DP.idtw 
                        AND UZ.typ = 32
		        ) AS UZ 
		)
