

CREATE OR ALTER PROCEDURE [HM].[GetSaleDocumentXML] (
	@docId INT,
	@correctedDocId INT,
	@userId INT,
	@moduleCode NVARCHAR(255),
	@removeInvalidXMLCharacters BIT,
	@correction BIT,
	@foreignOrderWithAdvanceInvoiceExists BIT,
	@payments HM.Payments2 READONLY,
	@hasPayments BIT,
	@relatedDocuments AS HM.RelatedDocuments READONLY,
	@relatedElements HM.RelatedElements READONLY,
	@relatedToForeignOrder BIT, 
	@relatedForeignOrderBaseType SMALLINT,
	@relatedToInventoryDocument BIT, 
	@relatedToParTypeDocument BIT,
	@hasRelationParToFvs BIT,
	@NpRateId INT,
	@ZeroRateId INT
	)
AS
BEGIN
-- UWAGA! Analogiczne zmiany należy wykonać w procedurze HM.GetSaleDocumentXMLWithoutAdvanceInvoiceData

	DECLARE @resultXml XML = (
			SELECT dk.[ExchangeRatePIT],
				dk.[FiscalizationState],
				dk.ReceiptNumber,
				dk.UniqueCashBoxNumber,
				dk.[JPK_V7],
				dk.[JPK],
				dk.[SaleRegister],
				dk.[IsGroupCorrection],
				dk.[IsBonusCorrection],
				dk.[DigitalInvoice],
				dk.RelatedForeignOrderDocumentId,
				dk.RelatedForeignOrderVersionId,
				dk.AdvanceInvoiceAmount,
                0 AS ElementsOfSetDocumentStatus,
				dk.PaymentsSum,
				(
					ISNULL((
							SELECT *
							FROM HM.ft_Contractor(dk.khadid)
							FOR XML PATH(''),
								ELEMENTS XSINIL,
								TYPE
							), '')
					) AS ContractorData,
				(
					ISNULL((
							SELECT *
							FROM HM.ft_Contractor(dk.odadid)
							FOR XML PATH(''),
								ELEMENTS XSINIL,
								TYPE
							), '')
					) AS ReceiverData,
                (
					ISNULL((
							SELECT *
							FROM HM.ft_Contractor(ISNULL(dk.kh3adid, dk.khadid))
                            FOR XML PATH(''),
								ELEMENTS XSINIL,
								TYPE
							), '')
					) AS NonPayerContractorData,
				(
					ISNULL((
							SELECT dp.[Description],
								dp.[CorrectedDocumentId],
								dp.[CorrectedDocumentShortcut],
								dp.[CorrectedPositionId],
								dp.[Compensation],
								dp.[RelatedForeignOrderPositionId],
								dp.[RelatedForeignOrderPositionIdForSalePosition],
								dp.[RelatedForeignOrderIdForSalePosition],
								dp.[RelatedPositionIdForDropdown],
								dp.[IsDocumentPosition],
								dp.[IsCorrectedPosition],
								dp.[IsCorrectionPosition],
								dp.[IsNewCorrectPosition],
								dp.[IsGroupCorrection],
								dp.[IsCreatedFromForeignOrder],
								dp.[ServiceExport],
								dp.[VatUE],
								dp.[DomesticVatRateId],
								dp.[JPK_V7] AS [JPK_V7],
								dp.[cn] AS [CN],
								dp.[VatRegister],
								dp.[HasSettelmentDeliveryReservation],
								ISNULL((
										SELECT rz.[id] AS [Id],
											rz.[idpozrz] AS [PositionId],
											kh.id AS [InventoryId],
											kh.[kod] AS [InventoryName],
											CAST(ISNULL(rz.[iloscrz], 0) AS DECIMAL(24, 4)) AS [ReservedQuantity],
											CAST(ISNULL(rz.[iloscrz], 0) AS DECIMAL(24, 4)) AS [ReservedQuantityAuxiliary],
											CAST(ISNULL((
														SELECT SUM(zz.ilosc)
														FROM [HM].[ZZ] zz
														WHERE zz.typ = 96
															AND zz.baza1 = 152
															AND zz.id1 = rz.id
															AND zz.baza1 = zz.baza2
														), 0) AS DECIMAL(24, 4)) AS [RealizedReservedQuantity],
											ISNULL((
													SELECT rzo.id AS [ReservationId],
														ISNULL(IIF(rzo.subtyp & 0x01  = 0, 'true', 'false'), 'false') AS [IsDeliveryReservation],
														CAST(ISNULL((
																	SELECT SUM(Quantity)
																	FROM [HM].[dbo_ForeignOrderPositionSettlementsView]
																	WHERE OrderPositionId = zp.id
																		AND DocumentId != @docId
																	), 0) AS DECIMAL(24, 4)) AS [RealizedSettelmentReservedQuantity],
														CAST(ISNULL(pwo.ilosc, 0) AS DECIMAL(24, 4)) AS [Quantity],
														CAST(ISNULL((
																	SELECT zzz.ilosc
																	FROM HM.ZZ zzz
																	JOIN HM.PW pw ON zzz.id2 = pw.id
																	WHERE zzz.typ = 96 and zzz.baza1 = 36 and zzz.id1 = pwo.id and pw.idmg = rz.id
																	), 0) AS DECIMAL(24, 4)) AS [RealizationQuantity],
														CAST(ISNULL((
																SELECT zzz.ilosc + pwo.ilosc
																FROM HM.ZZ zzz
																JOIN HM.PW pw ON zzz.id2 = pw.id
																WHERE zzz.typ = 96 AND zzz.baza1 = 36 AND zzz.id1 = pwo.id and pw.idmg = rz.id
																), 0) AS DECIMAL(24, 4)) AS MaxRealizationQuantity,
														ISNULL(pwo.id, 0) AS [DeliverySettlementId],
														ISNULL(pwo.iddw, 0) AS DeliveryId,
														ISNULL(CAST(ISNULL(dw.iloscdosp, 0) AS DECIMAL(24, 4)), 0) AS [DeliveryMaxQuantity],
														ISNULL(zp.id, 0) AS [OrderId],
														ISNULL(rzo.bazadokrz, 0) AS [OrderBaseId],
														ISNULL(CAST(ISNULL(zp.ilosc, 0) AS DECIMAL(24, 4)), 0) AS [MaxQuantity]
													FROM HM.RZ rzo
													LEFT JOIN HM.PW pwo ON pwo.idmg = rzo.id
													LEFT JOIN HM.DW dw ON pwo.iddw = dw.id
													LEFT JOIN HM.ZZ zz ON zz.typ = 96 and zz.baza1 = 152 and zz.id1 = rzo.id
													LEFT JOIN HM.ZP zp ON rzo.idpozrz = zp.id
													LEFT JOIN HM.ZO zo ON zp.super = zo.id
													WHERE pwo.typ = 26
														AND zz.id2 = rz.id
															
													ORDER BY zo.datasp DESC, pwo.iddw
													FOR XML PATH('DeliverySettlement'),
														ELEMENTS XSINIL,
														TYPE
													), '') AS [DeliverySettlements]
										FROM [HM].[RZ] rz
										JOIN [HM].[KH] kh ON rz.[magazyn] = kh.[id]
										WHERE rz.[bazadokrz] = 16
											AND rz.[bazapozrz] = 18
											AND rz.[idpozrz] = dp.[id]
										FOR XML PATH('Reservation'),
											ELEMENTS XSINIL,
											TYPE
										), '') AS Reservations,
								ISNULL((
										SELECT pw.[id] AS [Id],
											CAST(ISNULL(pw.[ilosc], 0) AS DECIMAL(24, 4)) AS [ReservedQuantity],
											(CAST(ISNULL(dw.[iloscdosp], 0) AS DECIMAL(24, 4)) + CAST(ISNULL(pw.[ilosc], 0) AS DECIMAL(24, 4))) AS [AvailableQuantity],
											CAST(ISNULL(pw.[ilosc], 0) AS DECIMAL(24, 4)) AS [ReservedQuantityAuxiliary],
											CAST(ISNULL((
														SELECT SUM(zz.ilosc)
														FROM [HM].[ZZ] zz
														WHERE zz.typ = 96
															AND zz.baza1 = 36
															AND zz.id1 = pw.id
															AND zz.baza1 = zz.baza2
														), 0) AS DECIMAL(24, 4)) AS [RealizedReservedQuantity],
											(
												SELECT kod
												FROM hm.kh
												WHERE typ = 105
													AND id = dw.magazyn
												) AS [InventoryName],
											ISNULL((
													SELECT dw.id AS Id,
														CAST(dw.cena AS DECIMAL(24, 4)) AS Price,
														dw.[data] AS [Date],
														dw.kod AS [Name],
														dw.krajPochodzenia AS CountryId,
														dw.magazyn AS InventoryId
													FOR XML PATH('Delivery'),
														ELEMENTS XSINIL,
														TYPE
													), '')
										FROM [HM].[PW] pw
										JOIN [HM].[DW] dw ON dw.[id] = pw.[iddw]
                                                            AND dw.numer != -dw.magazyn --pomijamy rezerwacje ilościowe
										WHERE pw.[idmg] IN (
												SELECT id
												FROM hm.rz
												WHERE idpozrz = dp.[id]
													AND bazapozrz = 18
													AND subtyp & 0x01 = 0
												)
											AND pw.[typ] = 26
										FOR XML PATH('ReservationDelivery'),
											ELEMENTS XSINIL,
											TYPE
										), '') AS ReservationsDelivery,
								ISNULL((
										SELECT rel.ZpId
										FROM [HM].[AdvanceInvoiceSettlementForeignOrder] rel
										WHERE rel.DpId = ISNULL(dp.idpozkoryg, dp.id)
										FOR XML PATH('RelationZp'),
											ELEMENTS XSINIL,
											TYPE
										), '') AS RelationsZp,
								ISNULL((
										select * from hm.ft_AdvanceInvoiceSettlementPosition(dp.Id)
										order by DocumentDate, DkzId
										FOR XML PATH('AdvanceInvoiceSettlementPosition'),
											ELEMENTS XSINIL,
											TYPE
										), '') AS AdvanceInvoiceSettlementPositions,
								(select sum(SettlementAmount) from hm.ft_AdvanceInvoiceSettlementPosition(dp.Id)) as AdvanceInvoiceValueGross,

								-- powiązane pozycje zamówienia
								-- na razie zakomentowane ponieważ przy odczycie istniejącego dokumentu te informacje nie są wykorzystywane
								-- ISNULL((
								-- 		SELECT orderOP.owner_id OrderPositionId, CAST(ro.ilosc AS DECIMAL(24, 4)) Quantity
								-- 		FROM HM.OP saleOP
								-- 			JOIN HM.RO ON ro.typ = 0 AND ro.id2 = saleOP.id
								-- 			JOIN HM.OP orderOP ON orderOP.id = ro.id1  
								-- 		WHERE saleOP.subtyp = 1 AND saleOP.owner_base = 18 AND saleOP.owner_id = dp.id
								-- 		ORDER BY OrderPositionId
								-- 		FOR XML PATH('RelatedOrderPosition'),
								-- 			ELEMENTS XSINIL,
								-- 			TYPE
								-- 		), '') AS RelatedOrderPositions,

								-- pola własne
								(
									SELECT pcf.customFields
									FROM [HM].[ft_GetSaleDocumentPositionCustomFields](@docId, @correctedDocId) pcf
									WHERE pcf.positionId = dp.id
								),
								dp.[PriceOfSetCalculatedBasedOfElements],
								dp.[NoEditOfSet],
								dp.[VatRateId],
								dp.[PricePLN],
								dp.[PriceGross],
								dp.[PriceGrossPLN],
								dp.[PriceListPrice],
								dp.[PriceListPriceGross],
								dp.[OriginalPriceListPrice],
								dp.[OriginalPriceListPriceCurrency],
								dp.[OriginalBasePrice],
								dp.[OriginalBasePriceCurrency],
								dp.[BasePricePLN],
								dp.[ValueGross],
								dp.[ValueVat],
								dp.[ValuePLN],
								dp.[ValueGrossPLN],
								dp.[ValueVatPLN],
								dp.[PriceTypeId],
								dp.[OO],
								dp.[CodePKWIU],
								dp.[Discount],
								dp.[IsProductDiscountAllowed],
								dp.[ReservationStockCode],
								dp.[StockStatus],
								dp.[ServiceExpense],
								dp.[Id],
								dp.[NodeId],
								dp.[ParentId],
								dp.[UserOrdinalNumber],
								dp.[OrdinalNumber],
								dp.[DocumentId],
								dp.[OriginalDocumentId],
								dp.[BaseType],
								dp.[Guid],
								dp.[ProductId],
								dp.[ProductShortcut],
								dp.[IsElementOfSet],
								dp.[IsSet],
								dp.[UnitOfMeasurement],
								dp.[EvidenceUnitOfMeasurement],
								dp.[ConversionFactorUoM],
								dp.[Quantity],
								dp.[UnitQuantity],
								dp.[ReservationQuantity],
								dp.[EvidenceQuantity],
								dp.[ReservationType],
								dp.[AccountingPattern],
								dp.[Price],
								dp.[Value],
								dp.[PriceRounding],
								dp.[IsService],
                                dp.[IsMarginService],
								dp.[CompletedQuantity],
								dp.ForSettlement AS ForSettlement,
								dp.[AdvanceInvoiceSettlementValueGross],
								dp.[IsOssService],
								'false' AS IsVatModifiedManually,
                                dp.[TaxCategoryId],
                                dp.[ExchangeRate]
							FROM [HM].[ft_SalePositions](@docId, dk.flag, dk.subtyp, @correction, @removeInvalidXMLCharacters) dp
							WHERE dp.[subtyp] = 0
							-- pozycje sprzedaży
							ORDER BY dp.NodeId
							-- istotne w przypadku korekt do poprawnego wyliczania _Index
							FOR XML PATH('SalePosition'),
								ELEMENTS XSINIL,
								TYPE
							), '')
					) AS DocumentPositions,
				-- Stawki VAT
				(
					ISNULL((
							SELECT *
							FROM HM.ft_VatRates(16, @docId, @NpRateId, @ZeroRateId)
							ORDER BY (
									CASE 
										WHEN Code LIKE '[a-z]%'
											THEN 1
										ELSE 0
										END
									),
								Code DESC, Id ASC
							FOR XML PATH('VatRate'),
								ELEMENTS XSINIL,
								TYPE
							), '')
					) AS VatRates,
				-- Płatności
				(
					ISNULL((
							SELECT *
							FROM @payments
							FOR XML PATH('Payment'),
								ELEMENTS XSINIL,
								TYPE
							), '')
					) AS Payments,
				IIF(@hasPayments = 1, 'true', 'false') AS HasPayments,
				-- Dokumenty powiązane
				(
					ISNULL((
							SELECT *
							FROM HM.ft_RelatedDocuments(@relatedDocuments)
							FOR XML PATH('RelatedDocument'),
								ELEMENTS XSINIL,
								TYPE
							), '')
					) AS RelatedDocuments,
                --Opakowania zwrotne
                (
					ISNULL((
							SELECT
                                pak.[Id],
                                pak.[DKId],
                                pak.[PackageName],
                                pak.[RefundableDepositName],
                                pak.[PackageId],
                                pak.[Quantity],
                                pak.[Price],
                                pak.[Value]
							FROM [HM].[DocumentPackagesView] pak 
                            WHERE pak.DKId = @docId
                            ORDER BY pak.PackageName ASC
							FOR XML PATH('RefundableDeposit'),
								ELEMENTS XSINIL,
								TYPE
							), '')
					) AS RefundableDeposits,
				-- Inne powiązania
				(
					ISNULL((
							SELECT *
							FROM HM.ft_RelatedElements(@relatedElements)
							FOR XML PATH('RelatedElement'),
								ELEMENTS XSINIL,
								TYPE
							), '')
					) AS RelatedElements,
				ISNULL(dk.[statusKsef], 0) AS [StatusKsef],
				dk.[dataWystawieniaKsef] AS [DataWystawieniaKsef],
				ISNULL(dk.[numberKsef], '') AS [NumberKsef],
				dk.[IsGuidEarchFilled] AS [IsGuidEarchFilled],
				-- pola własne
				(
					SELECT [HM].[fn_GetSaleDocumentCustomFields](@docId)
				),
				dk.[Id],
				dk.[Guid],
				dk.[DocumentNumber],
				dk.[SeriesNo],
				dk.[Name],
				dk.[CorrectedDocumentId],
				dk.[IssueDate],
				dk.[OperationDate],
				dk.[IsCorrection],
				dk.[Description],
				dk.[RecipientPerson],
				dk.[BaseType],
				dk.[DocumentTypeShortcut],
				dk.[DocumentNumerationShortcut],
				dk.[IsIssued],
				dk.[Value],
				dk.[DocumentStatus],
				dk.[DocumentStatusDescription],
				dk.[HmImported],
				dk.[Archived],
				dk.[FkExported],
				dk.[IsCreatedFromForeignOrder],
				dk.[Corrected],
				dk.[Canceled],
				dk.[IsActive],
				dk.[MigrationStatus],
				dk.[ContractorId],
				dk.[DepartmentId],
				dk.[AccountingPattern],
				dk.[IssuerId],
				dk.[IssuerLogin],
				dk.[DepartmentInSeries],
				dk.[Period],
				dk.[RDFStatus],
				dk.[ReceiverId],
				dk.[ReceiverAddressName],
                dk.[IsNonPayerContractorDocument],
                dk.[NonPayerContractorId],
				dk.[NonPayerContractorAddressName],
				dk.[PayRegistryId],
				dk.[PaymentFormClientId],
				dk.[PaymentDeadline],
				dk.[ValueGross],
				dk.[ValueVat],
				dk.[ValuePLN],
				dk.[ValueGrossPLN],
				dk.[ValueVatPLN],
				dk.[Currency],
				dk.[ExchangeRate],
				dk.[PriceTypeId],
				dk.[Discount],
				dk.[SplitPayment],
				dk.[InRegisterDate],
				dk.[CorrectionCause],
				dk.[PriceKind],
				dk.[InventorySettlementStatus],
				dk.[PublishInventoryDocumentMode],
				dk.[CashSettled],
				dk.[RegisterMethod],
				dk.[ContractorLimitFlag],
				dk.[ContractorLimitValue],
				dk.[ContractorLimitCurrency],
				dk.[CanContractorNegotiatePrices],
				dk.[CalculateVatMethod],
				dk.[KindId],
				dk.[CatalogId],
				dk.[Marker],
				dk.[ReservationType],
				IIF ([HM].[fnIsCompatibleCurrencies](ZO.subtyp, ZO.waluta, dk.subtyp, dk.Currency) = 1,'true','false') AS CompatibleCurrencies,
                ISNULL(ZO.waluta, '') AS RelatedForeignOrderCurrency,
				IIF(dk.BaseType IN (45, 46, 114, 120, 126, 127) AND ZO.walNetto <> ZO.walBrutto, 'false', 'true') AS CompatibleVAT,
				dk.IsSettlementAdvances,
				dk.SettlementAdvancesNet,
				dk.SettlementAdvancesVat,
				dk.SettlementAdvancesGross,
                dk.IsOssProcedure,
                dk.OssDeliveryCountryId,
                dk.OssShippingCountryId,
                dk.[ManualSettlementStatus],
                dk.[TypeExternal],
                dk.[IdExternal],
                dk.[Id2External],
                dk.[ZeroVatRate],
				CAST(dk.AllToPay - dk.PaymentsSum  AS DECIMAL(24, 2)) AS ToPay,
				IIF(@relatedToForeignOrder = 1, 'true', 'false') AS RelatedToForeignOrder,
				@relatedForeignOrderBaseType AS RelatedForeignOrderBaseType,
				IIF(@relatedToInventoryDocument = 1, 'true', 'false')  AS RelatedToInventoryDocument,
				IIF(@foreignOrderWithAdvanceInvoiceExists = 1, 'true', 'false') AS ForeignOrderWithAdvanceInvoiceExists,
                IIF(@relatedToParTypeDocument = 1, 'true', 'false') AS CreatedFromReceipt,
                IIF(@hasRelationParToFvs = 1, 'true', 'false') as HasRelationParToFvs,
				'false' AS IsVatModifiedManually,
                dk.IsSmeProcedure
			FROM [HM].[ft_SaleDocument](@docId, @removeInvalidXMLCharacters) dk
				    LEFT OUTER JOIN HM.TR AS tdk ON ISNULL(dk.iddokkoryg, dk.id) = tdk.owner_id AND tdk.owner_base = 16
					OUTER APPLY (SELECT top 1 id1 FROM HM.PO WHERE id2 = tdk.id) PO
					LEFT OUTER JOIN HM.TR AS tzo ON PO.id1 = tzo.id AND tzo.owner_base = 45
				    LEFT OUTER JOIN HM.ZO ON tzo.owner_id = ZO.id
			FOR XML PATH(''), ELEMENTS XSINIL,
				ROOT('SaleDocument')
			)

	-- RI – lepiej wydajnościowo: wszystko dzieje się lokalnie w jednej procedurze
	EXEC [HM].[ReadSaleDocumentRIAsXML] @docId,
		@resultXml,
		@userId,
		@moduleCode

END

GO

CREATE OR ALTER PROCEDURE [HM].[GetSaleDocumentXMLWithoutAdvanceInvoiceData] (
	@docId INT,
	@correctedDocId INT,
	@userId INT,
	@moduleCode NVARCHAR(255),
	@removeInvalidXMLCharacters BIT,
	@correction BIT,
	@payments HM.Payments2 READONLY,
	@hasPayments BIT,
	@relatedDocuments AS HM.RelatedDocuments READONLY,
	@relatedElements HM.RelatedElements READONLY,
	@relatedToForeignOrder BIT, 
	@relatedForeignOrderBaseType SMALLINT,
	@relatedToInventoryDocument BIT,
	@relatedToParTypeDocument BIT,
	@hasRelationParToFvs BIT,
	@NpRateId INT,
	@ZeroRateId INT
	)
AS
BEGIN
-- UWAGA! Analogiczne zmiany należy wykonać w procedurze HM.GetSaleDocumentXML

	DECLARE @resultXml XML = (
			SELECT dk.[ExchangeRatePIT],
				dk.[FiscalizationState],
				dk.ReceiptNumber,
				dk.UniqueCashBoxNumber,
				dk.[JPK_V7],
				dk.[JPK],
				dk.[SaleRegister],
				dk.[IsGroupCorrection],
				dk.[IsBonusCorrection],
				dk.[DigitalInvoice],
				dk.RelatedForeignOrderDocumentId,
				dk.RelatedForeignOrderVersionId,
				dk.AdvanceInvoiceAmount,
                0 AS ElementsOfSetDocumentStatus,
				dk.PaymentsSum,
				(
					ISNULL((
							SELECT *
							FROM HM.ft_Contractor(dk.khadid)
							FOR XML PATH(''),
								ELEMENTS XSINIL,
								TYPE
							), '')
					) AS ContractorData,
				(
					ISNULL((
							SELECT *
							FROM HM.ft_Contractor(dk.odadid)
							FOR XML PATH(''),
								ELEMENTS XSINIL,
								TYPE
							), '')
					) AS ReceiverData,
                (
					ISNULL((
							SELECT *
							FROM HM.ft_Contractor(ISNULL(dk.kh3adid, dk.khadid))
                            FOR XML PATH(''),
								ELEMENTS XSINIL,
								TYPE
							), '')
					) AS NonPayerContractorData,
				(
					ISNULL((
							SELECT dp.[Description],
								dp.[CorrectedDocumentId],
								dp.[CorrectedDocumentShortcut],
								dp.[CorrectedPositionId],
								dp.[Compensation],
								0 AS [RelatedForeignOrderPositionId],
								dp.[RelatedForeignOrderPositionIdForSalePosition],
								dp.[RelatedForeignOrderIdForSalePosition],
								0 AS [RelatedPositionIdForDropdown],
								dp.[IsDocumentPosition],
								dp.[IsCorrectedPosition],
								dp.[IsCorrectionPosition],
								dp.[IsNewCorrectPosition],
								dp.[IsGroupCorrection],
								dp.[IsCreatedFromForeignOrder],
								dp.[ServiceExport],
								dp.[VatUE],
								dp.[DomesticVatRateId],
								dp.[JPK_V7] AS [JPK_V7],
								dp.[cn] AS [CN],
								dp.[VatRegister],
								dp.[HasSettelmentDeliveryReservation],
								ISNULL((
										SELECT rz.[id] AS [Id],
											rz.[idpozrz] AS [PositionId],
											kh.id AS [InventoryId],
											kh.[kod] AS [InventoryName],
											CAST(ISNULL(rz.[iloscrz], 0) AS DECIMAL(24, 4)) AS [ReservedQuantity],
											CAST(ISNULL(rz.[iloscrz], 0) AS DECIMAL(24, 4)) AS [ReservedQuantityAuxiliary],
											CAST(ISNULL((
														SELECT SUM(zz.ilosc)
														FROM [HM].[ZZ] zz
														WHERE zz.typ = 96
															AND zz.baza1 = 152
															AND zz.id1 = rz.id
															AND zz.baza1 = zz.baza2
														), 0) AS DECIMAL(24, 4)) AS [RealizedReservedQuantity],
											ISNULL((
													SELECT rzo.id AS [ReservationId],
														ISNULL(IIF(rzo.subtyp & 0x01  = 0, 'true', 'false'), 'false') AS [IsDeliveryReservation],
														CAST(ISNULL((
																	SELECT SUM(Quantity)
																	FROM [HM].[dbo_ForeignOrderPositionSettlementsView]
																	WHERE OrderPositionId = zp.id
																		AND DocumentId != @docId
																	), 0) AS DECIMAL(24, 4)) AS [RealizedSettelmentReservedQuantity],
														CAST(ISNULL(pwo.ilosc, 0) AS DECIMAL(24, 4)) AS [Quantity],
														CAST(ISNULL((
																	SELECT zzz.ilosc
																	FROM HM.ZZ zzz
																	JOIN HM.PW pw ON zzz.id2 = pw.id
																	WHERE zzz.typ = 96 and zzz.baza1 = 36 and zzz.id1 = pwo.id and pw.idmg = rz.id
																	), 0) AS DECIMAL(24, 4)) AS [RealizationQuantity],
														CAST(ISNULL((
																SELECT zzz.ilosc + pwo.ilosc
																FROM HM.ZZ zzz
																JOIN HM.PW pw ON zzz.id2 = pw.id
																WHERE zzz.typ = 96 AND zzz.baza1 = 36 AND zzz.id1 = pwo.id and pw.idmg = rz.id
																), 0) AS DECIMAL(24, 4)) AS MaxRealizationQuantity,
														ISNULL(pwo.id, 0) AS [DeliverySettlementId],
														ISNULL(pwo.iddw, 0) AS DeliveryId,
														ISNULL(CAST(ISNULL(dw.iloscdosp, 0) AS DECIMAL(24, 4)), 0) AS [DeliveryMaxQuantity],
														ISNULL(zp.id, 0) AS [OrderId],
														ISNULL(rzo.bazadokrz, 0) AS [OrderBaseId],
														ISNULL(CAST(ISNULL(zp.ilosc, 0) AS DECIMAL(24, 4)), 0) AS [MaxQuantity]
													FROM HM.RZ rzo
													LEFT JOIN HM.PW pwo ON pwo.idmg = rzo.id
													LEFT JOIN HM.DW dw ON pwo.iddw = dw.id
													LEFT JOIN HM.ZZ zz ON zz.typ = 96 and zz.baza1 = 152 and zz.id1 = rzo.id
													LEFT JOIN HM.ZP zp ON rzo.idpozrz = zp.id
													LEFT JOIN HM.ZO zo ON zp.super = zo.id
													WHERE pwo.typ = 26
														AND zz.id2 = rz.id
															
													ORDER BY zo.datasp DESC, pwo.iddw
													FOR XML PATH('DeliverySettlement'),
														ELEMENTS XSINIL,
														TYPE
													), '') AS [DeliverySettlements]
										FROM [HM].[RZ] rz
										JOIN [HM].[KH] kh ON rz.[magazyn] = kh.[id]
										WHERE rz.[bazadokrz] = 16
											AND rz.[bazapozrz] = 18
											AND rz.[idpozrz] = dp.[id]
										FOR XML PATH('Reservation'),
											ELEMENTS XSINIL,
											TYPE
										), '') AS Reservations,
								ISNULL((
										SELECT pw.[id] AS [Id],
											CAST(ISNULL(pw.[ilosc], 0) AS DECIMAL(24, 4)) AS [ReservedQuantity],
											(CAST(ISNULL(dw.[iloscdosp], 0) AS DECIMAL(24, 4)) + CAST(ISNULL(pw.[ilosc], 0) AS DECIMAL(24, 4))) AS [AvailableQuantity],
											CAST(ISNULL(pw.[ilosc], 0) AS DECIMAL(24, 4)) AS [ReservedQuantityAuxiliary],
											CAST(ISNULL((
														SELECT SUM(zz.ilosc)
														FROM [HM].[ZZ] zz
														WHERE zz.typ = 96
															AND zz.baza1 = 36
															AND zz.id1 = pw.id
															AND zz.baza1 = zz.baza2
														), 0) AS DECIMAL(24, 4)) AS [RealizedReservedQuantity],
											(
												SELECT kod
												FROM hm.kh
												WHERE typ = 105
													AND id = dw.magazyn
												) AS [InventoryName],
											ISNULL((
													SELECT dw.id AS Id,
														CAST(dw.cena AS DECIMAL(24, 4)) AS Price,
														dw.[data] AS [Date],
														dw.kod AS [Name],
														dw.krajPochodzenia AS CountryId,
														dw.magazyn AS InventoryId
													FOR XML PATH('Delivery'),
														ELEMENTS XSINIL,
														TYPE
													), '')
										FROM [HM].[PW] pw
										JOIN [HM].[DW] dw ON dw.[id] = pw.[iddw]
                                                            AND dw.numer != -dw.magazyn --pomijamy rezerwacje ilościowe
										WHERE pw.[idmg] IN (
												SELECT id
												FROM hm.rz
												WHERE idpozrz = dp.[id]
													AND bazapozrz = 18
													AND subtyp & 0x01 = 0
												)
											AND pw.[typ] = 26
										FOR XML PATH('ReservationDelivery'),
											ELEMENTS XSINIL,
											TYPE
										), '') AS ReservationsDelivery,
								'' AS RelationsZp,
								'' AS AdvanceInvoiceSettlementPositions,
								null AS AdvanceInvoiceValueGross,

								-- powiązane pozycje zamówienia
								-- na razie zakomentowane ponieważ przy odczycie istniejącego dokumentu te informacje nie są wykorzystywane
								-- ISNULL((
								-- 		SELECT orderOP.owner_id OrderPositionId, CAST(ro.ilosc AS DECIMAL(24, 4)) Quantity
								-- 		FROM HM.OP saleOP
								-- 			JOIN HM.RO ON ro.typ = 0 AND ro.id2 = saleOP.id
								-- 			JOIN HM.OP orderOP ON orderOP.id = ro.id1  
								-- 		WHERE saleOP.subtyp = 1 AND saleOP.owner_base = 18 AND saleOP.owner_id = dp.id
								-- 		ORDER BY OrderPositionId
								-- 		FOR XML PATH('RelatedOrderPosition'),
								-- 			ELEMENTS XSINIL,
								-- 			TYPE
								-- 		), '') AS RelatedOrderPositions,

								-- pola własne
								(
									SELECT pcf.customFields
									FROM [HM].[ft_GetSaleDocumentPositionCustomFields](@docId, @correctedDocId) pcf
									WHERE pcf.positionId = dp.id
								),
								dp.[PriceOfSetCalculatedBasedOfElements],
								dp.[NoEditOfSet],
								dp.[VatRateId],
								dp.[PricePLN],
								dp.[PriceGross],
								dp.[PriceGrossPLN],
								dp.[PriceListPrice],
								dp.[PriceListPriceGross],
								dp.[OriginalPriceListPrice],
								dp.[OriginalPriceListPriceCurrency],
								dp.[OriginalBasePrice],
								dp.[OriginalBasePriceCurrency],
								dp.[BasePricePLN],
								dp.[ValueGross],
								dp.[ValueVat],
								dp.[ValuePLN],
								dp.[ValueGrossPLN],
								dp.[ValueVatPLN],
								dp.[PriceTypeId],
								dp.[OO],
								dp.[CodePKWIU],
								dp.[Discount],
								dp.[IsProductDiscountAllowed],
								dp.[ReservationStockCode],
								dp.[StockStatus],
								dp.[ServiceExpense],
								dp.[Id],
								dp.[NodeId],
								dp.[ParentId],
								dp.[UserOrdinalNumber],
								dp.[OrdinalNumber],
								dp.[DocumentId],
								dp.[OriginalDocumentId],
								dp.[BaseType],
								dp.[Guid],
								dp.[ProductId],
								dp.[ProductShortcut],
								dp.[IsElementOfSet],
								dp.[IsSet],
								dp.[UnitOfMeasurement],
								dp.[EvidenceUnitOfMeasurement],
								dp.[ConversionFactorUoM],
								dp.[Quantity],
								dp.[UnitQuantity],
								dp.[ReservationQuantity],
								dp.[EvidenceQuantity],
								dp.[ReservationType],
								dp.[AccountingPattern],
								dp.[Price],
								dp.[Value],
								dp.[PriceRounding],
								dp.[IsService],
                                dp.[IsMarginService],
								dp.[CompletedQuantity],
								null AS [ForSettlement],
								CAST(0 AS DECIMAL(24,2)) AS [AdvanceInvoiceSettlementValueGross],
								dp.[IsOssService],
								'false' AS IsVatModifiedManually,
                                dp.[TaxCategoryId],
                                dp.[ExchangeRate]
							FROM [HM].[ft_SalePositionsWithoutAdvanceInvoiceData](@docId, dk.flag, dk.subtyp, @correction, @removeInvalidXMLCharacters) dp
							WHERE dp.[subtyp] = 0
							-- pozycje sprzedaży
							ORDER BY dp.NodeId
							-- istotne w przypadku korekt do poprawnego wyliczania _Index
							FOR XML PATH('SalePosition'),
								ELEMENTS XSINIL,
								TYPE
							), '')
					) AS DocumentPositions,
				-- Stawki VAT
				(
					ISNULL((
							SELECT *
							FROM HM.ft_VatRates(16, @docId, @NpRateId, @ZeroRateId)
							ORDER BY (
									CASE 
										WHEN Code LIKE '[a-z]%'
											THEN 1
										ELSE 0
										END
									),
								Code DESC, Id ASC
							FOR XML PATH('VatRate'),
								ELEMENTS XSINIL,
								TYPE
							), '')
					) AS VatRates,
				-- Płatności
				(
					ISNULL((
							SELECT *
							FROM @payments
							FOR XML PATH('Payment'),
								ELEMENTS XSINIL,
								TYPE
							), '')
					) AS Payments,
				IIF(@hasPayments = 1, 'true', 'false') AS HasPayments,
				-- Dokumenty powiązane
				(
					ISNULL((
							SELECT *
							FROM HM.ft_RelatedDocuments(@relatedDocuments)
							FOR XML PATH('RelatedDocument'),
								ELEMENTS XSINIL,
								TYPE
							), '')
					) AS RelatedDocuments,
                --Opakowania zwrotne
                (
					ISNULL((
							SELECT
                                pak.[Id],
                                pak.[DKId],
                                pak.[PackageName],
                                pak.[RefundableDepositName],
                                pak.[PackageId],
                                pak.[Quantity],
                                pak.[Price],
                                pak.[Value]
							FROM [HM].[DocumentPackagesView] pak 
                            WHERE pak.DKId = @docId
                            ORDER BY pak.PackageName ASC
							FOR XML PATH('RefundableDeposit'),
								ELEMENTS XSINIL,
								TYPE
							), '')
					) AS RefundableDeposits,
				-- Inne powiązania
				(
					ISNULL((
							SELECT *
							FROM HM.ft_RelatedElements(@relatedElements)
							FOR XML PATH('RelatedElement'),
								ELEMENTS XSINIL,
								TYPE
							), '')
					) AS RelatedElements,
				ISNULL(dk.[statusKsef], 0) AS [StatusKsef],
				dk.[dataWystawieniaKsef] AS [DataWystawieniaKsef],
				ISNULL(dk.[numberKsef], '') AS [NumberKsef],
				dk.[IsGuidEarchFilled] AS [IsGuidEarchFilled],
				-- pola własne
				(
					SELECT [HM].[fn_GetSaleDocumentCustomFields](@docId)
				),
				dk.[Id],
				dk.[Guid],
				dk.[DocumentNumber],
				dk.[SeriesNo],
				dk.[Name],
				dk.[CorrectedDocumentId],
				dk.[IssueDate],
				dk.[OperationDate],
				dk.[IsCorrection],
				dk.[Description],
				dk.[RecipientPerson],
				dk.[BaseType],
				dk.[DocumentTypeShortcut],
				dk.[DocumentNumerationShortcut],
				dk.[IsIssued],
				dk.[Value],
				dk.[DocumentStatus],
				dk.[DocumentStatusDescription],
				dk.[HmImported],
				dk.[Archived],
				dk.[FkExported],
				dk.[IsCreatedFromForeignOrder],
				dk.[Corrected],
				dk.[Canceled],
				dk.[IsActive],
				dk.[MigrationStatus],
				dk.[ContractorId],
				dk.[DepartmentId],
				dk.[AccountingPattern],
				dk.[IssuerId],
				dk.[IssuerLogin],
				dk.[DepartmentInSeries],
				dk.[Period],
				dk.[RDFStatus],
				dk.[ReceiverId],
				dk.[ReceiverAddressName],
                dk.[IsNonPayerContractorDocument],
                dk.[NonPayerContractorId],
				dk.[NonPayerContractorAddressName],
				dk.[PayRegistryId],
				dk.[PaymentFormClientId],
				dk.[PaymentDeadline],
				dk.[ValueGross],
				dk.[ValueVat],
				dk.[ValuePLN],
				dk.[ValueGrossPLN],
				dk.[ValueVatPLN],
				dk.[Currency],
				dk.[ExchangeRate],
				dk.[PriceTypeId],
				dk.[Discount],
				dk.[SplitPayment],
				dk.[InRegisterDate],
				dk.[CorrectionCause],
				dk.[PriceKind],
				dk.[InventorySettlementStatus],
				dk.[PublishInventoryDocumentMode],
				dk.[CashSettled],
				dk.[RegisterMethod],
				dk.[ContractorLimitFlag],
				dk.[ContractorLimitValue],
				dk.[ContractorLimitCurrency],
				dk.[CanContractorNegotiatePrices],
				dk.[CalculateVatMethod],
				dk.[KindId],
				dk.[CatalogId],
				dk.[Marker],
				dk.[ReservationType],
				IIF ([HM].[fnIsCompatibleCurrencies](ZO.subtyp, ZO.waluta, dk.subtyp, dk.Currency) = 1,'true','false') AS CompatibleCurrencies,
                ISNULL(ZO.waluta, '') AS RelatedForeignOrderCurrency,
				IIF(dk.BaseType IN (45, 46, 114, 120, 126, 127) AND ZO.walNetto <> ZO.walBrutto, 'false', 'true') AS CompatibleVAT,
				dk.IsSettlementAdvances,
				dk.SettlementAdvancesNet,
				dk.SettlementAdvancesVat,
				dk.SettlementAdvancesGross,
                dk.IsOssProcedure,
                dk.OssDeliveryCountryId,
                dk.OssShippingCountryId,
                dk.[ManualSettlementStatus],
                dk.[TypeExternal],
                dk.[IdExternal],
                dk.[Id2External],
                dk.[ZeroVatRate],
				CAST(dk.AllToPay - dk.PaymentsSum  AS DECIMAL(24, 2)) AS ToPay,
				IIF(@relatedToForeignOrder = 1, 'true', 'false') AS RelatedToForeignOrder,
				@relatedForeignOrderBaseType AS RelatedForeignOrderBaseType,
				IIF(@relatedToInventoryDocument = 1, 'true', 'false')  AS RelatedToInventoryDocument,
				'false' AS ForeignOrderWithAdvanceInvoiceExists,
                IIF(@relatedToParTypeDocument = 1, 'true', 'false')  AS CreatedFromReceipt,
                IIF(@hasRelationParToFvs = 1, 'true', 'false') as HasRelationParToFvs,
				'false' AS IsVatModifiedManually,
                dk.IsSmeProcedure
			FROM [HM].[ft_SaleDocument](@docId, @removeInvalidXMLCharacters) dk
				    LEFT OUTER JOIN HM.TR AS tdk ON ISNULL(dk.iddokkoryg, dk.id) = tdk.owner_id AND tdk.owner_base = 16
					OUTER APPLY (SELECT top 1 id1 FROM HM.PO WHERE id2 = tdk.id) PO
					LEFT OUTER JOIN HM.TR AS tzo ON PO.id1 = tzo.id AND tzo.owner_base = 45
				    LEFT OUTER JOIN HM.ZO ON tzo.owner_id = ZO.id
			FOR XML PATH(''), ELEMENTS XSINIL,
				ROOT('SaleDocument')
			)

	-- RI – lepiej wydajnościowo: wszystko dzieje się lokalnie w jednej procedurze
	EXEC [HM].[ReadSaleDocumentRIAsXML] @docId,
		@resultXml,
		@userId,
		@moduleCode

END

GO

-- Procedura umożliwiająca pobranie danych dokumentu w postaci XML
-- Uwaga: Gdy dodasz nowy parametr, użyj go również przy wywołaniu na końcu procedury (szukaj: EXEC [HM].[ReadSaleDocumentAsXML]), sugeruje się pozostawić parametr @removeInvalidXMLCharacters na końcu
CREATE
	OR

ALTER PROCEDURE [HM].[ReadInventoryDocumentAsXML] (
	@docId INT,
	@userId INT,
	@moduleCode NVARCHAR(255),
	@removeInvalidXMLCharacters BIT = 0
	)
AS
BEGIN
	BEGIN TRY
		-- Dokumenty powiazane
		DECLARE @relatedDocuments AS HM.RelatedDocuments

		INSERT @relatedDocuments
		SELECT [Id],
            [DocumentTypeId],
            [CharakterDocumentId],
            [DocumentCode],
            [DateW],
            [OldNew],
            [GroupDescription],
            [Buffer]
        FROM [HM].[ft_InventoryRelatedDocuments](@docId)

		-- Inne powiazania
		DECLARE @relatedElements AS HM.RelatedElements

		INSERT @relatedElements
		EXEC [HM].[GetRelatedElementsForDocument] @docId = @docId,
			@docBase = 33 -- magazyn

		DECLARE @deliveries AS TABLE (
			PwId INT,
			InventoryDocumentId INT,
			Price DECIMAL(24, 6),
			[Date] DATETIME,
			Shortcut NVARCHAR(100),
			DeliveryId INT,
			[Name] NVARCHAR(100),
			DeliveredQuantity DECIMAL(24, 4),
			DeliveredValue DECIMAL(24, 2),
			ReleasedQuantity DECIMAL(24, 4),
			CountryId INT,
			IsApprovedProductValue BIT,
			MzId INT INDEX deliveriesIdx02 NONCLUSTERED,
			[Guid] UNIQUEIDENTIFIER,
			[Number] INT,
            [InventoryId] INT,
            [ContractorId] INT,
			[ProductId] INT,
            [IsUniqueNumeration] BIT,
			SumOfPwQuantities DECIMAL(24, 4), --suma ilości z pw aktualnego i poprzednich
			TotalDeliveredQuantity DECIMAL(24, 4), --wartość z dw
			AvailableQuantity DECIMAL(24, 4)
			)

		INSERT INTO @deliveries
		SELECT pw.id AS PwId,
			dw.iddkpz AS InventoryDocumentId,
			CAST(dw.cena AS DECIMAL(24, 6)) AS Price,
			dw.[data] AS [Date],
			dw.kod AS Shortcut,
			dw.id AS DeliveryId,
			dw.kod AS [Name],
			CAST(ISNULL(-pw.ilosc, 0) AS DECIMAL(24, 4)) AS DeliveredQuantity,
			CAST(ISNULL(-pw.wartosc, 0) AS DECIMAL(24, 2)) AS DeliveredValue,
			-1 AS ReleasedQuantity,
			dw.krajPochodzenia AS CountryId,
			IIF(dw.flag & 0x1 = 1, 'true', 'false') AS IsApprovedProductValue,
			mz.id AS MzId,
			dw.[guid] AS [Guid],
			dw.numer AS [Number],
            dw.magazyn as [InventoryId],
            dw.idkh as [ContractorId],
			dw.idtw as [ProductId],
			IIF(dw.numer BETWEEN 0 AND 16777215, 'true', 'false') AS [IsUniqueNumeration],
			ISNULL((SELECT SUM(-pw2.ilosc) FROM HM.PW pw2 WHERE pw2.iddw = dw.id AND pw2.id < pw.id AND (pw2.flag & 0x10) <> 0), 0) AS SumOfPwQuantities,
			dw.iloscPz AS TotalDeliveredQuantity,
			dw.iloscdosp AS AvailableQuantity
		FROM HM.MZ mz
		JOIN HM.PW pw ON mz.id = pw.idmg
		JOIN HM.DW dw ON pw.iddw = dw.id
		WHERE mz.super = @docId
			AND (
				(
					mz.bufor = 1
					AND pw.typ = 39
					)
				OR (
					mz.bufor = 0
					AND pw.typ = 37
					)
				)

		--Sytuacja, gdy jest jakieś PW do wybranej dostawy oraz już towary z niej wydane. Wtedy wydajemy w kolejności tworzenia dokumentów
		UPDATE @deliveries
		SET ReleasedQuantity =
			(CASE WHEN (TotalDeliveredQuantity - SumOfPwQuantities - AvailableQuantity >= DeliveredQuantity) THEN DeliveredQuantity
				WHEN (TotalDeliveredQuantity - SumOfPwQuantities - AvailableQuantity < 0 ) THEN 0
				ELSE -- (TotalDeliveredQuantity - SumOfPwQuantities - AvailableQuantity < DeliveredQuantity) 
					(TotalDeliveredQuantity - SumOfPwQuantities - AvailableQuantity)
			END)

        DECLARE @releaseDeliverys AS TABLE (
            Id INT,
            Quantity DECIMAL(24, 4),
            Price DECIMAL(26, 6),
            dwId INT,
            dwAvailableQuantity DECIMAL(24, 4),
            dwPrice DECIMAL(24, 6),
            dwDate DATETIME,
            dwName VARCHAR(100),
            dwCountryId INT, 
            dwInventoryId INT,
            LP BIGINT INDEX releaseDeliverysIdx01 UNIQUE CLUSTERED,
            mzId INT INDEX releaseDeliverysIdx02 NONCLUSTERED
        )
        INSERT INTO @releaseDeliverys
        SELECT pw.id AS Id,
               CAST(pw.ilosc AS DECIMAL(24, 4)) AS Quantity,
               CAST(dw.cena AS DECIMAL(24, 6)) AS Price,
               dw.id AS dwId,
               (CAST(ISNULL(dw.iloscdosp, 0) AS DECIMAL(24, 4)) + CAST(ISNULL(pw.ilosc, 0) AS DECIMAL(24, 4))) AS dwAvailableQuantity,
               CAST(dw.cena AS DECIMAL(24, 6)) AS dwPrice,
               dw.[data] AS [dwDate],
               dw.kod AS [dwName],
               dw.krajPochodzenia AS dwCountryId,
               dw.magazyn AS dwInventoryId,
               ROW_NUMBER() OVER (ORDER BY dw.DATA, dw.id) * CASE WHEN ISNULL(tw.metoda, 0) = 0 THEN 1 ELSE -1 END LP,
               mz.id
            FROM HM.MZ mz 
            JOIN HM.PW pw on mz.id = pw.idmg
            INNER JOIN HM.DW dw ON dw.id = pw.iddw
            LEFT JOIN hm.tw on dw.idtw = tw.id
            WHERE mz.super = @docId
                AND pw.typ = 37

		DECLARE @purchaseCosts AS TABLE (
			MzId INT,
			PurchaseCost DECIMAL(24, 2)
			)

		INSERT INTO @purchaseCosts
		SELECT
			mz.id AS MzId,
			SUM(ISNULL(CAST((op2.wartosc - ISNULL(op2.wartosc_rozl, 0) + op1.wartosc_rozl) AS DECIMAL(35, 15)), IIF(HM.fnIsFlagSet(mz.flag, 0x100) = 1, NULL, 0))) AS PurchaseCost	
		FROM HM.MZ mz
		JOIN HM.OP op1 ON op1.owner_id = mz.id AND op1.owner_base = 37
		JOIN HM.RO ON ro.id1 = op1.id AND ro.flag & 0x1000 = 0 AND ro.flag & 0x100 = 0 --dla nieskorygowanych pozycji
		JOIN HM.OP op2 ON op2.id = ro.id2 AND op2.owner_base = 18
		WHERE mz.super = @docId
		GROUP BY mz.id

        DECLARE @TradePositionId TABLE (positionId INT, tradePositionId INT)    
        INSERT INTO @TradePositionId
        SELECT mz.id, OP2.owner_id
        FROM hm.mz mz
            JOIN hm.OP AS OP1 ON OP1.owner_id = mz.id AND op1.owner_base = 37
            JOIN hm.RO ON RO.id1 = OP1.id 
            JOIN hm.OP AS OP2 ON RO.id2 = OP2.id AND OP2.owner_base = 18
		WHERE mz.super = @docId
		UNION ALL
		SELECT mz.id, OP2.owner_id
        FROM hm.mz mz
            JOIN hm.OP AS OP1 ON OP1.owner_id = mz.id AND op1.owner_base = 37
            JOIN hm.RO ON RO.id2 = OP1.id
            JOIN hm.OP AS OP2 ON RO.id1 = OP2.id AND OP2.owner_base = 18
        WHERE mz.super = @docId;

        DECLARE @RelatedOrderPositions TABLE (positionId int, relatedOrderPositionId INT)
        INSERT INTO @RelatedOrderPositions
        SELECT mz.id, OP2.owner_id
        FROM hm.mz mz
            JOIN hm.OP AS OP1 ON OP1.owner_id = mz.id AND op1.owner_base = 37
            JOIN hm.RO ON RO.id1 = OP1.id 
            JOIN hm.OP AS OP2 ON RO.id2 = OP2.id AND OP2.owner_base = 30
        WHERE mz.super = @docId
        UNION ALL
        SELECT mz.id, OP2.owner_id
        FROM hm.mz mz
            JOIN hm.OP AS OP1 ON OP1.owner_id = mz.id AND op1.owner_base = 37
            JOIN hm.RO ON RO.id2 = OP1.id
            JOIN hm.OP AS OP2 ON RO.id1 = OP2.id AND OP2.owner_base = 30
        WHERE mz.super = @docId;

		DECLARE @resultXml XML = (
				SELECT MG.Deadline,
					MG.InventoryId,
					mg.IsRelease,
					(
						ISNULL((
								SELECT ad.[nazwaAdr]
								FROM HM.AD
								WHERE AD.id = mg.khadid
								), '')
						) AS AddressName,
					(
						ISNULL((
								SELECT *
								FROM HM.ft_Contractor(MG.khadid)
								FOR XML PATH(''),
									ELEMENTS XSINIL,
									TYPE
								), '')
						) AS ContractorData,
					mg.InventoryRegister,
					ISNULL((
							SELECT mz.CorrectedDocumentId,
								mz.CorrectedDocumentShortcut,
								mz.CorrectedPositionId,
								mz.Compensation,
								mz.IsDocumentPosition,
								mz.IsCorrectedPosition,
								mz.IsCorrectionPosition,
								mz.IsNewCorrectPosition,
								mz.[Description],
								ISNULL((
										SELECT rz.id AS Id,
											rz.idpozrz AS PositionId,
											kh.id AS InventoryId,
											kh.kod AS InventoryName,
											CAST(ISNULL(rz.iloscrz, 0) AS DECIMAL(24, 4)) AS ReservedQuantity,
											CAST(ISNULL(rz.iloscrz, 0) AS DECIMAL(24, 4)) AS ReservedQuantityAuxiliary,
											CAST(ISNULL((
														SELECT SUM(zz.ilosc)
														FROM HM.ZZ zz
														WHERE zz.typ = 96
															AND zz.baza1 = 152
															AND zz.id1 = rz.id
															AND zz.baza1 = zz.baza2
														), 0) AS DECIMAL(24, 4)) AS RealizedReservedQuantity,
											ISNULL((
													SELECT rzo.id AS ReservationId,
														ISNULL(IIF(rzo.subtyp & 0x01 = 0, 'true', 'false'), 'false') AS IsDeliveryReservation,
														CAST(ISNULL((
																		SELECT SUM(Quantity)
																		FROM [HM].[dbo_DocumentReservationSettlementsView]
																		WHERE SalePositionId = dp.id OR ForeignOrderPositionId = zp.id
																		), 0) AS DECIMAL(24, 4)) AS [RealizedSettelmentReservedQuantity],
														CAST(ISNULL(pwo.ilosc, 0) AS DECIMAL(24, 4)) AS Quantity,
														CAST(ISNULL((
																		SELECT zzz.ilosc
																		FROM HM.ZZ zzz
																		JOIN HM.PW pw ON zzz.id2 = pw.id
																		WHERE zzz.typ = 96 and zzz.baza1 = 36 and zzz.id1 = pwo.id and pw.idmg = rz.id
																		), 0) AS DECIMAL(24, 4)) AS [RealizationQuantity],
															CAST(ISNULL((
																	SELECT zzz.ilosc + pwo.ilosc
																	FROM HM.ZZ zzz
																	JOIN HM.PW pw ON zzz.id2 = pw.id
																	WHERE zzz.typ = 96 AND zzz.baza1 = 36 AND zzz.id1 = pwo.id and pw.idmg = rz.id
																	), 0) AS DECIMAL(24, 4)) AS MaxRealizationQuantity,
														ISNULL(pwo.id, 0) AS DeliverySettlementId,
														ISNULL(CAST(ISNULL(dw.iloscdosp, 0) AS DECIMAL(24, 4)), 0) AS DeliveryMaxQuantity,
														ISNULL(pwo.iddw, 0) AS DeliveryId,
														ISNULL(rzo.idpozrz, 0) AS [OrderId],
														ISNULL(rzo.bazadokrz, 0) AS [OrderBaseId],
														CAST(rzo.iloscrz + ISNULL(zz.ilosc, 0) AS DECIMAL(24, 4)) AS [MaxQuantity]
													FROM HM.RZ rzo
													LEFT JOIN HM.PW pwo ON pwo.typ = 26 AND pwo.idmg = rzo.id
													LEFT JOIN HM.DW dw ON pwo.iddw = dw.id
													LEFT JOIN HM.ZZ zz ON zz.typ = 96 AND zz.baza1 = 152 AND zz.id1 = rzo.id
													LEFT JOIN HM.ZP zp ON rzo.idpozrz = zp.id and bazapozrz = 30
													LEFT JOIN HM.DP dp ON rzo.idpozrz = dp.id and bazapozrz = 18
													WHERE zz.id2 = rz.id
													ORDER BY pwo.iddw
													FOR XML PATH('DeliverySettlement'),
														ELEMENTS XSINIL,
														TYPE
													), '') AS DeliverySettlements
										FROM HM.RZ rz
										JOIN HM.KH kh ON rz.magazyn = kh.id
										WHERE rz.bazadokrz = 33
											AND rz.bazapozrz = 37
											AND rz.idpozrz = mz.id
										FOR XML PATH('Reservation'),
											ELEMENTS XSINIL,
											TYPE
										), '') AS Reservations,
								ISNULL((
										SELECT pw.id AS Id,
                                            CAST(ISNULL(pw.wartosc, 0) AS DECIMAL(24, 2)) AS [ReservedValue],
											CAST(ISNULL(pw.ilosc, 0) AS DECIMAL(24, 4)) AS ReservedQuantity,
											(CAST(ISNULL(dw.iloscdosp, 0) AS DECIMAL(24, 4)) + CAST(ISNULL(pw.ilosc, 0) AS DECIMAL(24, 4))) AS AvailableQuantity,
											CAST(ISNULL(pw.ilosc, 0) AS DECIMAL(24, 4)) AS ReservedQuantityAuxiliary,
											CAST(ISNULL((
														SELECT SUM(zz.ilosc)
														FROM HM.ZZ zz
														WHERE zz.typ = 96
															AND zz.baza1 = 36
															AND zz.id1 = pw.id
															AND zz.baza1 = zz.baza2
														), 0) AS DECIMAL(24, 4)) AS RealizedReservedQuantity,
											(
												SELECT kod
												FROM hm.kh
												WHERE typ = 105
													AND id = dw.magazyn
												) AS InventoryName,
											ISNULL((
													SELECT dw.id AS Id,
														CAST(dw.cena AS DECIMAL(24, 6)) AS Price,
														dw.[data] AS [Date],
														dw.kod AS [Name],
														dw.krajPochodzenia AS CountryId,
														dw.magazyn AS InventoryId
													FOR XML PATH('Delivery'),
														ELEMENTS XSINIL,
														TYPE
													), '')
										FROM HM.PW pw
										INNER JOIN HM.DW dw ON dw.id = pw.iddw 
                                                            AND dw.numer != -dw.magazyn --pomijamy rezerwacje ilościowe
										WHERE pw.idmg IN (
												SELECT id
												FROM hm.rz
												WHERE idpozrz = mz.id
													AND bazapozrz = 37
													AND subtyp & 0x01 = 0
												)
											AND pw.typ = 26
										FOR XML PATH('ReservationDelivery'),
											ELEMENTS XSINIL,
											TYPE
										), '') AS ReservationsDelivery,
								ISNULL(IIF(mg.IsRelease = 'false', (
										SELECT deli.DeliveryId AS Id,
												deli.InventoryDocumentId AS InventoryDocumentId,
                                                deli.Price AS Price,
                                                deli.[Date] AS [Date],
                                                deli.Shortcut AS Shortcut,
                                                deli.[Name] AS [Name],
                                                deli.DeliveredQuantity AS DeliveredQuantity,
                                                deli.DeliveredValue AS DeliveredValue,
                                                deli.ReleasedQuantity AS ReleasedQuantity,
												deli.AvailableQuantity AS AvailableQuantity,
                                                deli.CountryId AS CountryId,
                                                deli.[Guid] AS [Guid],
                                                deli.InventoryId as [InventoryId],
                                                deli.ContractorId as [ContractorId],
                                                deli.ProductId as [ProductId],
												deli.IsUniqueNumeration as [IsUniqueNumeration],
                                                deli.Number [Number],
												-- pola własne
												(
													SELECT dcf.customFields
													FROM [HM].[ft_GetDeliveryPositionCustomFields](@docId) dcf
													WHERE dcf.positionId = deli.DeliveryId
												)
										FROM @deliveries deli
										WHERE deli.MzId = mz.id
										FOR XML PATH('Delivery'),
											ELEMENTS XSINIL,
											TYPE
										), ''), '') AS Deliveries,
								ISNULL((
										SELECT SUM(deli.ReleasedQuantity)
										FROM @deliveries deli
										WHERE deli.MzId = mz.id										
										), 0) AS ReleasedQuantity,
								ISNULL((SELECT [Id],
                                               mg.[data] [ReleaseDate],
                                               [Quantity],
                                               [Price],
                                               ISNULL((SELECT [dwId] [Id],
                                                            [dwAvailableQuantity] [AvailableQuantity],
                                                            [dwPrice] [Price],
                                                            [dwDate] [Date],
                                                            [dwName] [Name],
                                                            [dwCountryId] [CountryId],
                                                            [dwInventoryId] [InventoryId]
                                                FOR XML PATH('Delivery'),
                                                            ELEMENTS XSINIL,
                                                            TYPE
                                                        ), '')
                                            FROM @releaseDeliverys rd
                                            WHERE rd.mzId = mz.Id
                                            ORDER BY rd.lp ASC
                                            FOR XML PATH('ReleaseDelivery'),
                                                ELEMENTS XSINIL,
                                                TYPE
										), '') AS ReleasesDelivery,
								mz.ForeignCode,
								mz.IsNewDelivery,
								mz.IsSplitDelivery,
								--Do dopracowania przy wymaganiach z dzieleniem dostaw
								(
									IIF((SELECT TOP (1) IsApprovedProductValue
												FROM @deliveries
												WHERE MzId = mz.id) = 1, 'true', 'false')
									) AS IsApprovedProductValue,
								(
									CASE WHEN mz.IsSplitDelivery = 'true' THEN 'Podzielona dostawa'
									ELSE
										(
											SELECT TOP (1) [Name]
											FROM @deliveries
											WHERE MzId = mz.id
										)
									END
									) AS DeliveryName,
								(
									SELECT TOP (1) PwId
									FROM @deliveries
									WHERE MzId = mz.id
									) AS DeliveryIdPW,
								-- pola własne
								(
									SELECT pcf.customFields
									FROM [HM].[ft_GetInventoryDocumentPositionCustomFields](@docId, mg.[CorrectedDocumentId]) pcf
									WHERE pcf.positionId = mz.id
								),
								mz.[Id],
								mz.[NodeId],
								mz.[ParentId],
								mz.[UserOrdinalNumber],
								mz.[OrdinalNumber],
								mz.[DocumentId],
								mz.[OriginalDocumentId],
								mz.[BaseType],
								mz.[Guid],
								mz.[ProductId],
								mz.[ProductShortcut],
								mz.[IsElementOfSet],
								mz.[IsSet],
								mz.[UnitOfMeasurement],
								mz.[EvidenceUnitOfMeasurement],
								mz.[ConversionFactorUoM],
								mz.[Quantity],
								mz.[UnitQuantity],
								mz.[ReservationQuantity],
								mz.[EvidenceQuantity],
								mz.[ReservationType],
								mz.[AccountingPattern],
								mz.[Price],
								mz.[Value],
								mz.[PriceRounding],
								mz.[IsService],
								mz.[IsMarginService],
								mz.[CompletedQuantity],
								mz.[IsCreatedFromForeignOrder],
								mz.[NoEditOfSet],
								mz.[HasSettelmentDeliveryReservation],
								pc.[PurchaseCost],
								mz.[HasRelatedPriceChangeDocument],
                                ISNULL((SELECT DISTINCT TOP 1 rop.tradePositionId FROM @TradePositionId rop WHERE mz.id = rop.positionId), 0) [TradePositionId],
                                ISNULL((SELECT TOP 1 rop.relatedOrderPositionId FROM @RelatedOrderPositions rop WHERE mz.id = rop.positionId), 0) [RelatedOrderPositionId]
							FROM hm.ft_InventoryPositions(@docId, @removeInvalidXMLCharacters) mz
							LEFT JOIN @purchaseCosts pc ON pc.MzId = mz.Id
							ORDER BY mz.NodeId
							FOR XML PATH('InventoryPosition'),
								ELEMENTS XSINIL,
								TYPE
							), '') AS DocumentPositions,
					-- Dokumenty powiązane
					(
						ISNULL((
								SELECT *
								FROM HM.ft_RelatedDocuments(@relatedDocuments)
								FOR XML PATH('RelatedDocument'),
									ELEMENTS XSINIL,
									TYPE
								), '')
						) AS RelatedDocuments,
					-- Inne powiązania
					(
						ISNULL((
								SELECT *
								FROM HM.ft_RelatedElements(@relatedElements)
								FOR XML PATH('RelatedElement'),
									ELEMENTS XSINIL,
									TYPE
								), '')
						) AS RelatedElements,
					-- pola własne
					(
						SELECT [HM].[fn_GetInventoryDocumentCustomFields](@docId)
					),
					mg.[Id],
					mg.[Guid],
					mg.[DocumentNumber],
					mg.[SeriesNo],
					mg.[Name],
					mg.[CorrectedDocumentId],
					mg.[IssueDate],
					mg.[OperationDate],
					mg.[IsCorrection],
					mg.[Description],
					mg.[RecipientPerson],
					mg.[BaseType],
					mg.[DocumentTypeShortcut],
					mg.[DocumentNumerationShortcut],
					mg.[IsIssued],
					mg.[Value],
					mg.[DocumentStatus],
					mg.[DocumentStatusDescription],
					mg.[HmImported],
					mg.[Archived],
					mg.[FkExported],
					mg.[IsCreatedFromForeignOrder],
					IIF(EXISTS(SELECT TOP 1 id FROM @relatedDocuments rd WHERE rd.GroupDescription  = 'Dokumenty korygujące'), 'true', 'false') AS Corrected,
					mg.[Canceled],
					mg.[IsActive],
					mg.[MigrationStatus],
					mg.[ContractorId],
					mg.[DepartmentId],
					mg.[AccountingPattern],
					mg.[IssuerId],
					mg.[IssuerLogin],
					mg.[DepartmentInSeries],
					mg.[Period],
					mg.[KindId],
					mg.[CatalogId],
					mg.[Marker],
					mg.[ReservationType],
                    mg.[SettlementMarker],
                    mg.[CustomSettlement],
                    mg.[ManualSettlementStatus],
					IIF(EXISTS(SELECT TOP 1 id FROM @relatedDocuments rd WHERE rd.GroupDescription = 'Zamówienia'), 'true', 'false') AS RelatedToForeignOrder,
					IIF(EXISTS(SELECT TOP 1 id FROM @relatedDocuments rd WHERE rd.GroupDescription = 'Faktura wewnętrzna'), 'true', 'false') AS HasInternalInvoice,
					IIF(EXISTS(SELECT TOP 1 id FROM @relatedDocuments rd WHERE rd.GroupDescription in ('Dokumenty sprzedaży', 'Dokumenty zakupu')), 'true', 'false') AS HasRelatedTradeDocuments,
                    mg.[TypeExternal],
                    mg.[IdExternal],
                    mg.[Id2External]
				FROM hm.ft_InventoryDocument(@docId, @removeInvalidXMLCharacters) mg

				FOR XML PATH(''), ELEMENTS XSINIL,
					ROOT('InventoryDocument')
				)

		EXEC [HM].[ReadInventoryDocumentRIAsXML] @docId,
			@resultXml,
			@userId,
			@moduleCode
	END TRY

	BEGIN CATCH
		DECLARE @ErrorNumber INT = ERROR_NUMBER();

		IF (
				@removeInvalidXMLCharacters = 0
				AND (@ErrorNumber IN (9420, 6841))
				)
		BEGIN
			EXEC [HM].[ReadInventoryDocumentAsXML] @docId,
				@userId,
				@moduleCode,
				@removeInvalidXMLCharacters = 1;

			GOTO END_CATCH;
		END;

		THROW;

		END_CATCH:
	END CATCH
END

GO

CREATE OR ALTER PROCEDURE [HM].[UpdateDocumentReservationsFromXML] (@docHandle INT, 
															@tblDocumentPositions [HM].[DocumentReservationsPositionsType] READONLY,
															@DP [HM].[DocumentPositionsType] READONLY,
															@DeletedDocumentPositionsRZ [HM].[DocumentPositionsType] READONLY,
															@docId INT, 
															@docDate DATETIME,	
															@docKhId INT,		
															@correction BIT, 
															@anyOrderRelatedDocuments BIT,
															@tworzacy int
															)
AS
BEGIN
	DECLARE @dataName varchar(255) = '',
	@positionName varchar(255) = '',
	@docType INT,		
	@bazaDokRZ INT,		
	@bazaPozRZ INT

	SELECT TOP 1 @dataName = localname 
	FROM OPENXML(@docHandle, '/')

	IF (@dataName = 'SaleDocument')
	BEGIN
		SELECT @docType = 0,
		@bazaDokRZ = 16,
		@bazaPozRZ = 18,
		@positionName = 'SalePosition'
	END
	ELSE IF (@dataName = 'ForeignOrderDocument')
	BEGIN
		SELECT @docType = 1,
		@bazaDokRZ = 45,
		@bazaPozRZ = 30,
		@positionName = 'ForeignOrderPosition'
	END
	ELSE IF (@dataName = 'InventoryDocument')
	BEGIN
		SELECT @docType = 2,
		@bazaDokRZ = 33,
		@bazaPozRZ = 37,
		@positionName = 'InventoryPosition'
	END
	ELSE
	BEGIN
		DECLARE @msg varchar(2048) = CONCAT('Nieobslugiwany charakter dokumentu: ', @dataName);
		THROW 51000, @msg, 1;  
	END

	--Tabela tymczasowa odczytywania rezerwacji dokumentu
	DECLARE @tblPositionsReservations AS TABLE
	(
		[Id] INT,
		[PositionId] INT,
		[NodeId] INT,
		[AvailableQuantity] DECIMAL(24,4),
		[ReservedQuantity] DECIMAL(24,4),
		[ReservedQuantityAuxiliary] DECIMAL(24,4),
		[RealizedReservedQuantity] DECIMAL(24,4),
		[InventoryId] INT
	)

	-- Deserializacja danych rezerwacji z xml
	DECLARE @PositionsReservationsXPath varchar(500) = CONCAT('/', @dataName, '/DocumentPositions/', @positionName,'/Reservations/Reservation')

	INSERT INTO @tblPositionsReservations
	SELECT 
		0,
		[PositionId],
		[NodeId],
		[AvailableQuantity],
		[ReservedQuantity],
		[ReservedQuantityAuxiliary],
		[RealizedReservedQuantity],
		[InventoryId]
	FROM 
		OPENXML(@docHandle, @PositionsReservationsXPath)
		WITH
		(
		--[Id] INT 'Id',	--nie możemy polegać na id z xml-a ponieważ może się on zmienić w trakcie edycji rezerwacji (z nie zerowego stać się zerowy)
		[PositionId] INT 'PositionId',
		[NodeId] INT './../../NodeId',	--NodeId z pozycji jest zawsze aktualny
		[AvailableQuantity] DECIMAL(24,4) 'AvailableQuantity',
		[ReservedQuantity] DECIMAL(24,4) 'ReservedQuantity',
		[ReservedQuantityAuxiliary] DECIMAL(24,4) 'ReservedQuantityAuxiliary',
		[RealizedReservedQuantity] DECIMAL(24,4) 'RealizedReservedQuantity',
		[InventoryId] INT 'InventoryId',
		[ModificationStatus] VARCHAR(30) './../../@ModificationStatus'
		)
		WHERE [ModificationStatus] <> 'Deleted' OR [ModificationStatus] IS NULL  -- pomijamy pozycje oznaczone jako 'Deleted', warunek konieczny ponieważ NodeId dla pozycji usuniętych, powtarza się z nie usuniętymi
			AND	[NodeId] IN (SELECT NodeId FROM @tblDocumentPositions)	--wybieramy tylko te dla których aktualizowaliśmy pozycje

	--Zabezpieczenie przed przypadkową rezerwacją przy zapisie dokumentu rezerwowanego wydaniem
	UPDATE @tblPositionsReservations SET ReservedQuantity = IIF(ReservedQuantity - RealizedReservedQuantity < 0,0,ReservedQuantity - RealizedReservedQuantity)
	FROM @tblPositionsReservations
	WHERE RealizedReservedQuantity > 0

	--Aktualizacja Id pozycji (DP), z xmla przyjdą zera dla nowych pozycji
	UPDATE tblRZ SET PositionId = tblDP.Id
	FROM @tblDocumentPositions tblDP
		JOIN @tblPositionsReservations tblRZ ON tblDP.NodeId = tblRZ.NodeId
	WHERE tblRZ.PositionId = 0;

	--odtworzenie id-ów rezerwacji, które już istnieją (z xml-a przyszły same zera)
	UPDATE tblRZ SET tblRZ.Id = RZ.id
	FROM @tblPositionsReservations tblRZ
		JOIN @tblDocumentPositions tblDP ON tblDP.Id = tblRZ.PositionId
		JOIN HM.RZ ON RZ.idpozrz = tblRZ.PositionId AND RZ.magazyn = tblRZ.InventoryId AND RZ.bazapozrz = @bazaPozRZ AND RZ.idtw = tblDP.ProductId 
	WHERE tblDP.ReservationType = IIF(rz.subtyp & 0x01 = 0,2,1) --odtwarzamy jeśli nie został zmieniony typ rezerwacji (ilościowy/ze wskazaniem), w przeciwnym razie traktujemy jako nową

	--usuwamy rezerwacje z pozycji które mają zmieniony typ rezerwacji na 'brak' lub zostały wyzerowane
	DELETE tblRZ
	FROM @tblDocumentPositions tblDP 
		JOIN @tblPositionsReservations tblRZ ON tblRZ.PositionId = tblDP.Id
	WHERE tblDP.ReservationType = 0 OR 
		(tblRZ.ReservedQuantity = 0 AND ISNULL(tblRZ.RealizedReservedQuantity,0) = 0)
			and tblRZ.id not in (SELECT id2 FROM HM.ZZ WHERE typ=96 AND baza1 = 152) -- rezerwacje które realizują, pozostają pomimo zera 


	--Tabela tymczasowa do odczytania rezerwacji ze wskazaniem dostawy
	DECLARE @tblPositionsReservationsDelivery AS TABLE
	(
		[Id] INT,
		[PositionId] INT,
		[NodeId] INT,
		[AvailableQuantity] DECIMAL(24,4),
		[ReservedQuantity] DECIMAL(24,4),
		[ReservedQuantityAuxiliary] DECIMAL(24,4),
		[InventoryId] INT,
		[DeliveryId] INT,
		[Price] DECIMAL(35,15)
	)

	-- Deserializacja danych rezerwacji ze wskazaniem dostawy z xml
	DECLARE @PositionsReservationsDeliveryXPath varchar(500) = CONCAT('/', @dataName, '/DocumentPositions/', @positionName,'/ReservationsDelivery/ReservationDelivery')

	INSERT INTO @tblPositionsReservationsDelivery
	SELECT 
		xmlData.[Id],
		xmlData.[PositionId],
		xmlData.[NodeId],
		xmlData.[AvailableQuantity],
		xmlData.[ReservedQuantity],
		xmlData.[ReservedQuantityAuxiliary],
		xmlData.[InventoryId],
		xmlData.[DeliveryId],
		xmlData.[Price]
	FROM 
		OPENXML(@docHandle, @PositionsReservationsDeliveryXPath)
		WITH
		(
			[Id] INT 'Id',
			[PositionId] INT 'PositionId',
			[NodeId] INT './../../NodeId', 	--NodeId z pozycji jest zawsze aktualny
			[AvailableQuantity] DECIMAL(24,4) 'AvailableQuantity',
			[ReservedQuantity] DECIMAL(24,4) 'ReservedQuantity',
			[ReservedQuantityAuxiliary] DECIMAL(24,4) 'ReservedQuantityAuxiliary',
			[InventoryId] INT 'Delivery/InventoryId',
			[DeliveryId] INT 'Delivery/Id',
			[Price] DECIMAL(35,15) 'Delivery/Price',
			[ModificationStatus] VARCHAR(30) './../../@ModificationStatus'
		) xmlData
		JOIN @tblDocumentPositions tblPos ON tblPos.NodeId = xmlData.[NodeId]	--wybieramy tylko te dla których aktualizowaliśmy pozycje
		WHERE [ModificationStatus] <> 'Deleted' OR [ModificationStatus] IS NULL  -- pomijamy pozycje oznaczone jako 'Deleted', warunek konieczny ponieważ NodeId dla pozycji usuniętych, powtarza się z nie usuniętymi
			AND tblPos.ReservationType = 2	--pomijamy rozliczenia jeśli rezerwacja nie jest ze wskazaniem dostaw, potrafią przyjść takie z XML-a, przy zmianie typu rezewacji na ilościową

	--aktualizacja id pozycji (DP)
	UPDATE tblRZD SET PositionId = tblDP.Id
	FROM @tblDocumentPositions tblDP
	JOIN @tblPositionsReservationsDelivery tblRZD ON tblDP.NodeId = tblRZD.NodeId
	WHERE tblRZD.PositionId = 0;

	--odtworzenie id-ów PW, które już istnieją (z xml-a mogą przyjść nie prawdziwe)
	UPDATE tblRZD SET Id = pw.id
	FROM @tblPositionsReservationsDelivery tblRZD 
		JOIN @tblPositionsReservations tblRZ ON tblRZ.PositionId = tblRZD.PositionId AND tblRZ.InventoryId = tblRZD.InventoryId
		JOIN HM.PW pw ON pw.idmg = tblRZ.Id AND pw.iddw = tblRZD.DeliveryId AND pw.typ = 26

	--Tabela tymczasowa do odczytania realizowanych rezerwacji
	DECLARE @tblDeliverySettlements AS TABLE
	(
		[ReservationId] INT,				
		[DeliverySettlementId] INT,				
		[Quantity] DECIMAL(24,4),
		[RealizationQuantity] DECIMAL(24,4),
		[MaxQuantity] DECIMAL(24,4),
		[DeliveryMaxQuantity] DECIMAL(24,4),
		[MaxRealizationQuantity] DECIMAL(24,4),
		[NodeId] INT,
		[ParentId] INT,
		[DeliveryId] INT,
		[InventoryId] INT
	)

	-- Deserializacja danych realizowanych rezerwacji z xml
	DECLARE @DeliverySettlementsXPath varchar(500) = CONCAT('/', @dataName, '/DocumentPositions/', @positionName,'/Reservations/Reservation/DeliverySettlements/DeliverySettlement')

	INSERT INTO @tblDeliverySettlements
	SELECT 
		[ReservationId],				
		[DeliverySettlementId],				
		[Quantity],
		[RealizationQuantity],
		[MaxQuantity],
		[DeliveryMaxQuantity],
		[MaxRealizationQuantity],
		[NodeId],
		0,
		[DeliveryId],
		[InventoryId]
	FROM 
		OPENXML(@docHandle, @DeliverySettlementsXPath)
		WITH
		(
			[ReservationId] INT 'ReservationId',				
			[DeliverySettlementId] INT 'DeliverySettlementId',				
			[Quantity] DECIMAL(24,4) 'Quantity',
			[RealizationQuantity] DECIMAL(24,4) 'RealizationQuantity',
			[MaxQuantity] DECIMAL(24,4) 'MaxQuantity',
			[DeliveryMaxQuantity] DECIMAL(24,4) 'DeliveryMaxQuantity',
			[MaxRealizationQuantity] DECIMAL(24,4) 'MaxRealizationQuantity',
			[ModificationStatus] VARCHAR(30) './../../../../@ModificationStatus',
			[NodeId] INT './../../../../NodeId',
			[DeliveryId] INT 'DeliveryId',
			[InventoryId] INT './../../InventoryId'
		)
		WHERE [ModificationStatus] <> 'Deleted' OR [ModificationStatus] IS NULL  -- pomijamy pozycje oznaczone jako 'Deleted', warunek konieczny ponieważ NodeId dla pozycji usuniętych, powtarza się z nie usuniętymi
			AND [NodeId] IN (SELECT NodeId FROM @tblDocumentPositions)	--wybieramy tylko te dla których aktualizowaliśmy pozycje

	-- uzupełnienie danych dla realizacji rezerwacji ilościowej - z XML-a mogą przyjść nie wypełnione  
	UPDATE @tblDeliverySettlements SET DeliverySettlementId = pw.id, DeliveryId = pw.iddw 
	FROM @tblDeliverySettlements tds
	JOIN HM.PW pw ON pw.typ = 26 AND pw.idmg = tds.ReservationId
	JOIN @tblDocumentPositions tblDP ON tblDP.NodeId = tds.NodeId
	WHERE ISNULL(tds.DeliverySettlementId, 0) = 0 AND tblDP.ReservationType = 1

	--tabela z uzupełnieniem informacji o realizacjach ze wskazaniem dostaw (w celu obsłużenia powrotu wartości)
	DECLARE @tblDeliverySettlements2 AS TABLE
	(
		[SettlementLeft] INT,				
		[SettlementRight] INT,				
		[NewValueLeft] DECIMAL(24,2),
		[NewValueRight] DECIMAL(24,2),
		[OldQuantityLeft] DECIMAL(24,4)
	)

	INSERT INTO @tblDeliverySettlements2
	SELECT 
		pwL.id AS [SettlementLeft],				
		pwR.id AS [SettlementRight],				
		newValueLeft.v AS NewValueLeft,
		(pwL.wartosc + pwR.wartosc) - overValue.v - newValueLeft.v AS NewValueRight,
		pwL.ilosc AS OldQuantityLeft
	FROM @tblDeliverySettlements tds 
	JOIN HM.ZZ zz ON tds.DeliverySettlementId = zz.id1 and zz.typ = 96 and zz.baza1 = 36
	JOIN HM.PW pwL ON pwL.id = tds.DeliverySettlementId 
	JOIN HM.PW pwR ON pwR.id = zz.id2
	JOIN @tblPositionsReservations tpr ON pwR.idmg = tpr.Id AND pwR.typ = 26	--pomijamy PW z innych dokumentów które również aktualnie realizują tą samą rezerwację.
-- obliczenia
	CROSS APPLY( SELECT		--nadwyżka ilości (ponad realizację)
			IIF(pwL.ilosc + pwR.ilosc - tds.MaxRealizationQuantity > 0, pwL.ilosc + pwR.ilosc - tds.MaxRealizationQuantity, 0) AS q) overQuantity
	CROSS APPLY( SELECT		--nadwyżka wartości (ponad realizację)
			IIF(pwL.ilosc + pwR.ilosc <> 0, HM.ROUND2((pwL.wartosc + pwR.wartosc) / (pwL.ilosc + pwR.ilosc) * overQuantity.q), 0) AS v) overValue
	CROSS APPLY( SELECT
			IIF(pwL.ilosc + pwR.ilosc <> 0, HM.ROUND2((pwL.wartosc + pwR.wartosc) / (pwL.ilosc + pwR.ilosc) * tds.Quantity), 0) AS v) newValueLeft

	IF @correction = 0
	BEGIN
		--Aktualizacja typu rezerwacji na pozycji				
		IF (@docType = 0)
		BEGIN
			UPDATE [HM].[DP] SET
				typ = dp.typ | IIF(tblDP.[ReservationType] = 0,0,0x08) | IIF(tblDP.[ReservationType] = 1,IIF(tw.metoda = 0,0,0x04) | 0x02, 0)
				| IIF(tblDP.[ReservationType] = 2,0x20,0) --0x20 dotyczy jawnej zmiany przez użytkownika, zostawione tymczasowo dla wstecznej kompatybilności
			FROM [HM].[DP] dp
			JOIN [HM].[TW] tw ON dp.idtw = tw.id
			JOIN @tblDocumentPositions tblDP ON DP.[id] = tblDP.[Id]
			WHERE (dp.flag & 0x0200 <> 0) and dp.super = @docId
			--AND tblDP.MofificationStatus IN (@created, @updated)
		END 
		ELSE IF (@docType = 1)
		BEGIN
			UPDATE [HM].[ZP] SET
				typ = zp.typ | IIF(tblDP.[ReservationType] = 0,0,0x08) | IIF(tblDP.[ReservationType] = 1,IIF(tw.metoda = 0,0,0x04) | 0x02, 0)
				| IIF(tblDP.[ReservationType] = 2,0x20,0) --0x20 dotyczy jawnej zmiany przez użytkownika, zostawione tymczasowo dla wstecznej kompatybilności
			FROM [HM].[ZP] zp
			JOIN [HM].[TW] tw ON zp.idtw = tw.id
			JOIN @tblDocumentPositions tblDP ON ZP.[id] = tblDP.[Id]
			WHERE (zp.flag & 0x0200 <> 0) and zp.super = @docId
			--AND tblDP.MofificationStatus IN (@created, @updated)
		END
		ELSE IF (@docType = 2)
		BEGIN
			UPDATE HM.MZ SET
				typ = mz.typ | IIF(tblDP.ReservationType = 0,0,0x08) | IIF(tblDP.ReservationType = 1, IIF(tw.metoda = 0,0,0x04) | 0x02, 0)
				| IIF(tblDP.[ReservationType] = 2,0x20,0) --0x20 dotyczy jawnej zmiany przez użytkownika, zostawione tymczasowo dla wstecznej kompatybilności
			FROM HM.MZ mz
			JOIN HM.TW tw ON mz.idtw = tw.id
			JOIN @tblDocumentPositions tblDP ON MZ.id = tblDP.Id
			WHERE (mz.flag & 0x0200 <> 0) and mz.super = @docId
			--AND tblDP.MofificationStatus IN (@created, @updated)
		END
	END
    
	--aktualizacja kontrahenta na wszystkich rezerwacjach z dokumentu
	IF EXISTS(SELECT TOP 1 1 FROM HM.RZ rz WHERE rz.iddokrz = @docId AND rz.bazadokrz = @bazaDokRZ AND ISNULL(@docKhId, 0) != ISNULL(rz.idkh, 0))
	BEGIN --jeśli zmienił się kontrahent w jednej to zmień na wszystkich 
		UPDATE HM.RZ SET RZ.idkh = @docKhId WHERE RZ.iddokrz = @docId AND RZ.bazadokrz = @bazaDokRZ
	END

	--aktualizacja daty rezerwacji na wszystkich rezerwacjach z dokumentu
	IF EXISTS(SELECT TOP 1 1 FROM HM.RZ rz WHERE rz.iddokrz = @docId AND rz.bazadokrz = @bazaDokRZ AND @docDate != rz.data)
	BEGIN --jeśli zmieniła się data w jednej rezerwacji to zmień na wszystkich 
		UPDATE HM.RZ SET RZ.[data] = @docDate, RZ.termin = DATEADD(DAY, 7, @docDate) WHERE RZ.iddokrz = @docId AND RZ.bazadokrz = @bazaDokRZ

		UPDATE HM.PW SET [data] = @docDate 
			FROM HM.RZ rz
				JOIN HM.PW pw ON rz.id = pw.idmg AND pw.typ = 26 
			WHERE rz.iddokrz = @docId AND rz.bazadokrz = @bazaDokRZ
	END

	--indentyfikatory nowo dodanych rezerwacji
	DECLARE @newRzIds TABLE (Id INT, PositionId INT, InventoryId INT)

	-- Dodanie rekordów rezerwacji (RZ), dla pozycji i magazynów dla których ich nie ma
	INSERT INTO [HM].[RZ] (flag, subtyp, typ, idtw, idkh, magazyn, data, termin, iloscrz, iloscrl, wartosc, iddokrz, bazadokrz, idpozrz, bazapozrz, tworzacy) 
	OUTPUT inserted.id, inserted.idpozrz, inserted.magazyn INTO @newRzIds
	SELECT DISTINCT 0,IIF(tblDP.[ReservationType] = 2,0,1) | IIF(TW.metoda = 1, 0x02, 0),2,tblDP.ProductId,@docKhId,tblRZ.InventoryId, @docDate, DATEADD(DAY, 7, @docDate),0,0,0,@docId,@bazaDokRZ,tblDP.id,@bazaPozRZ,@tworzacy
	FROM @tblDocumentPositions tblDP
		JOIN @tblPositionsReservations tblRZ ON tblRZ.PositionId = tblDP.id
		JOIN [HM].[SM] SM ON SM.idtw = tblDP.ProductId AND SM.magazyn = tblRZ.InventoryId
		JOIN HM.TW ON TW.id = tblDP.ProductId 
		LEFT JOIN [HM].[OP] OP ON OP.owner_id = tblDP.id AND OP.owner_base = @bazaPozRZ
	WHERE HM.ROUND4(SM.stanHandl) >= 0 
		--AND (tblDP.id NOT IN (SELECT RR.idpozrz FROM [HM].[RZ] RR WHERE RR.iddokrz = @docId and RR.magazyn = tblRZ.InventoryId) 
		--`		OR tblDP.ProductId NOT IN (SELECT RR.idtw FROM [HM].[RZ] RR WHERE RR.iddokrz = @docId))
		AND tblRZ.Id = 0 AND tblRZ.ReservedQuantity > 0
		AND tblDP.[ReservationType] in (1,2) 		 
		--??? ilość dp rozliczenia większa od 0 AND (tblDP.iloscDP - ISNULL(OP.ilosc_rozl,0)) > 0
	--AND tblDP.MofificationStatus IN (@created, @updated)

	-- Aktualizacja Id nowo dodanych RZ w @tblPositionsReservations
	UPDATE tblRZ SET Id = newRzIds.Id
	FROM @tblPositionsReservations tblRZ
		JOIN @newRzIds newRzIds ON newRzIds.PositionId = tblRZ.PositionId AND newRzIds.InventoryId = tblRZ.InventoryId

	-- Aktualizacja ID rezerwacji na rozliczeniach rezerwacji w celu merga powiazań na ZZ
	UPDATE tds SET tds.ParentId = tpr.Id
	FROM @tblDeliverySettlements tds
	JOIN @tblPositionsReservations tpr on tds.NodeId = tpr.NodeId
				AND tds.InventoryId = tpr.InventoryId -- może być kilka rezerwacji pod jedną pozycją

	DECLARE @mergedZzIds TABLE (id1 INT, id2 INT)

	MERGE INTO HM.ZZ AS ZZ 
	USING (SELECT ReservationId,
				  ParentId,
				  SUM(RealizationQuantity) RealizationQuantity 
			FROM @tblDeliverySettlements
			GROUP BY ReservationId,
				     ParentId) AS TDS
	ON ZZ.TYP = 96 and zz.baza1 = 152 and zz.id1 = tds.ReservationId and zz.baza2 = 152 and zz.id2 = tds.ParentId
	WHEN NOT MATCHED BY TARGET THEN
		INSERT (flag, subtyp, typ, baza1, id1, baza2, id2, ilosc, kod1, kod2, cena, waluta, opis, baza3, ilosc2, magazyn)
		VALUES (0, 0, 96, 152, tds.ReservationId, 152, tds.ParentId, tds.RealizationQuantity, '', '', 0, '', '', 0, 0, 0)
	OUTPUT Inserted.id1, Inserted.id2 INTO @mergedZzIds;
			
--Rezerwacje przeznaczone do usunięcia
	DECLARE @rzToDel TABLE (Id INT, ProductId INT, InventoryId INT, ReservationType INT)

	--z usuniętych pozycji
	INSERT INTO @rzToDel
	SELECT delRZ.id, delRZ.IdTw, rz.magazyn, IIF(rz.subtyp & 0x01 = 0,2,1)
	FROM @DeletedDocumentPositionsRZ delRZ
		JOIN HM.RZ rz ON rz.id = delRz.Id

	--ze zmodyfikowanych pozycji np. zmiana towaru, magazynu
	INSERT INTO @rzToDel
	SELECT RZ.id, RZ.idtw, RZ.magazyn, IIF(rz.subtyp & 0x01 = 0,2,1)
	FROM @tblDocumentPositions tblDP 
		JOIN HM.RZ ON RZ.idpozrz = tblDP.Id AND RZ.bazapozrz = @bazaPozRZ 
	WHERE RZ.id NOT IN (SELECT Id FROM @tblPositionsReservations) 
	--AND tblDP.MofificationStatus IN (@created, @updated)

--pw przeznaczone do usunięcia
	DECLARE @pwToDel TABLE (Id INT)

--z usuwanych rezerwacji
	INSERT INTO @pwToDel
	SELECT pw.id FROM HM.PW WHERE typ = 26 AND idmg IN (SELECT DISTINCT Id FROM @rzToDel)

--z ilością 0
	INSERT INTO @pwToDel
	SELECT pw.id FROM HM.PW
	WHERE id IN (SELECT tblRZD.Id 
		 			FROM @tblPositionsReservationsDelivery tblRZD
					WHERE tblRZD.ReservedQuantity <= 0
						AND tblRZD.id NOT IN (SELECT id2 FROM HM.ZZ WHERE typ = 96 AND baza1 = 36) -- rekordów PW realizujących, nie usuwamy
				)

--pw których nie ma w xmlu w rozliczeniach dostaw
	INSERT INTO @pwToDel
	SELECT pw.id
	FROM @tblPositionsReservations tblRz
		JOIN HM.PW pw ON pw.idmg = tblRz.Id AND pw.typ = 26
		JOIN HM.DW dw ON dw.id = pw.iddw
	WHERE pw.id NOT IN (SELECT Id FROM @tblPositionsReservationsDelivery)
		AND dw.numer != -dw.magazyn	--pomijamy rekordy PW od rezerwacji ilościowej


	--stany które należy zaktualizować
	DECLARE @smToUpdate TABLE (ProductId INT, InventoryId INT, ReservationType INT)

	--z rezerwacji usuniętych
	INSERT INTO @smToUpdate 
	SELECT DISTINCT ProductId, InventoryId, ReservationType 
	FROM @rzToDel

	--z rezerwacji ze zmodyfikowanych pozycji
	INSERT INTO @smToUpdate 
	SELECT DISTINCT tblDP.ProductId, tblRz.InventoryId, tblDP.ReservationType 
	FROM @tblPositionsReservations tblRz
		JOIN @tblDocumentPositions tblDP ON tblDP.Id = tblRz.PositionId
	--WHERE tblDP.MofificationStatus IN (@created, @updated)

	-- z rezerwacji z różnym typem (ilościowa/ze wskazaniem) niż rezerwacja realizowana
	INSERT INTO @smToUpdate 
	SELECT DISTINCT tblDP.ProductId, tblRz.InventoryId, IIF(rzFrom.subtyp & 0x01 = 0,2,1) 
	FROM @tblPositionsReservations tblRz
		JOIN HM.ZZ zz ON zz.id2 = tblRz.Id AND zz.typ = 96 AND zz.baza1 = 152 AND zz.baza2 = 152
		JOIN HM.RZ rzFrom ON rzFrom.id = zz.id1
		JOIN @tblDocumentPositions tblDP ON tblDP.Id = tblRz.PositionId
	WHERE IIF(rzFrom.subtyp & 0x01 = 0,2,1) != tblDP.ReservationType

	
	-- dostawy wartościowe których stany trzeba będzie zauktualizować
	DECLARE @dwToUpdate TABLE (Id INT)

	--pochodzące z usuniętych rozliczeń (PW) rezerwacji
	INSERT INTO @dwToUpdate
	SELECT DISTINCT pw.iddw
	FROM @pwToDel pwToDel
		JOIN HM.PW pw ON pw.id = pwToDel.Id
		JOIN HM.DW dw ON dw.id = pw.iddw
	WHERE dw.numer != -dw.magazyn --pomijamy dostawy ilościowe

	-- ze zmodyfikowanych rezerwacji
	INSERT INTO @dwToUpdate
	SELECT DISTINCT tblRZD.DeliveryId
	FROM @tblPositionsReservationsDelivery tblRZD

	--Wyzerowanie rezerwacji do usunięcia
	UPDATE [HM].[RZ] SET 
		iloscrz = 0, 
		iloscrl = 0 
	WHERE id in (SELECT Id FROM @rzToDel)
		and (rz.iloscrz != 0 or rz.iloscrl != 0)
				

	--Dodanie brakujących dostaw rezerwacji ilościowej
	INSERT INTO [HM].[DW] (flag,subtyp,typ,kod,numer,idtw,data,ilosc,iloscdosp,cena,wartoscdosp,magazyn,iloscPz,stan,wartoscst,bufor)
	SELECT 0,0,0,'Rezerwacja ilościowa', -qReservations.InventoryId, qReservations.ProductId,'9999-01-01 00:00:00.000',0,0,0,0, qReservations.InventoryId,0,0,0,0
	FROM (
		SELECT tblDP.ProductId AS ProductId, tblRZ.InventoryId AS InventoryId
		FROM @tblDocumentPositions tblDP  
			JOIN @tblPositionsReservations tblRZ ON tblRZ.PositionId = tblDP.Id
		WHERE tblDP.[ReservationType] = 1
		GROUP BY tblDP.ProductId, tblRZ.InventoryId) AS qReservations  
	LEFT JOIN HM.DW ON DW.idtw = qReservations.ProductId and DW.magazyn = qReservations.InventoryId and DW.numer = -qReservations.InventoryId
	WHERE DW.id IS NULL --nie zjoinowały się a więc nie istnieją

	
	IF exists (select top 1 * from hm.zz zz join @rzToDel rzToDel on zz.id2 = rzToDel.Id and typ = 96 and baza1 = 152)
	BEGIN /* Obsługa rezerwacji powiązanych z zamówieniem przed usunięciem rezerwacji */

		UPDATE rzLeft SET rzLeft.iloscrz = rzLeft.iloscrz + zz.ilosc, rzLeft.wartosc = rzLeft.wartosc + rzRight.wartosc
		FROM HM.RZ rzLeft
		JOIN HM.ZZ zz ON rzLeft.id = zz.id1 and zz.typ = 96 and zz.baza1 = 152
		JOIN @rzToDel ddprz ON ddprz.Id = zz.id2
		JOIN HM.RZ rzRight ON rzRight.id = ddprz.Id
		WHERE rzLeft.iloscrz != rzLeft.iloscrz + zz.ilosc

		UPDATE pwLeft SET pwLeft.ilosc = pwLeft.ilosc + zz.ilosc, pwLeft.wartosc = pwLeft.wartosc + pwRight.wartosc
		FROM HM.PW pwRight
		JOIN @pwToDel pwToDel ON pwToDel.Id = pwRight.id
		JOIN HM.ZZ zz ON pwRight.id = zz.id2 and zz.typ = 96 and zz.baza1 = 36
		JOIN HM.PW pwLeft ON pwLeft.id = zz.id1

		--usunięcie powiązań ZZ z usuwanych rezerwacji
		DELETE HM.ZZ
		FROM HM.ZZ zz 
		JOIN @rzToDel tds ON tds.Id = zz.id2 and zz.typ = 96 and zz.baza1 = 152

		--usunięcie powiązań ZZ z usuwanych rekordów PW
		DELETE HM.ZZ
		FROM HM.ZZ zz 
		JOIN @pwToDel pwToDel ON zz.id2 = pwToDel.id and zz.typ = 96 and zz.baza1 = 36
	END

	--Usunięcie RZ i PW przewidzianych wcześniej do usunięca
	DELETE FROM HM.RZ WHERE id IN (SELECT DISTINCT id FROM @rzToDel)
	DELETE FROM HM.PW WHERE id IN (SELECT DISTINCT id FROM @pwToDel)

	--dodanie PW dla nowych rezerwacji ilościowych
	INSERT INTO [HM].[PW] (flag,subtyp,typ,idmg,iddw,idtw,ilosc,wartosc,ilosczreal,data)
	SELECT DISTINCT 1, 0, 26, tblRZ.id, dw.id, tblDP.ProductId, tblRZ.ReservedQuantity, 0, 0, @docDate 
		FROM @newRzIds newRz
			JOIN @tblPositionsReservations tblRZ ON tblRZ.Id = newRz.Id
			JOIN @tblDocumentPositions tblDP ON tblDP.Id = tblRZ.PositionId
			JOIN HM.DW dw ON dw.idtw = tblDP.ProductId AND dw.magazyn = tblRZ.InventoryId AND dw.numer = -dw.magazyn
		WHERE tblRZ.ReservedQuantity > 0 AND tblDP.ReservationType = 1

	--aktualizacja PW dla rezerwacji ilościowych
	UPDATE [HM].[PW] SET 
		ilosc = IIF(tblRZ.ReservedQuantity < pw.ilosc + sm.stanHandl + ISNULL(tds2.OldQuantityLeft, 0), 
				tblRZ.ReservedQuantity,
				pw.ilosc + sm.stanHandl + ISNULL(tds2.OldQuantityLeft, 0)), --rezerwacja tylko do dostępnego stanu w magazynie plus to co ew. jest w realizacji rezerwacji
		flag = 1
	FROM @tblPositionsReservations tblRZ
	JOIN [HM].[PW] PW ON PW.idmg = tblRZ.id AND PW.typ = 26
	JOIN @tblDocumentPositions tblDP ON tblDP.Id = tblRZ.PositionId
	JOIN HM.SM sm ON sm.idtw = tblDP.ProductId AND sm.magazyn = tblRZ.InventoryId
	LEFT JOIN @tblDeliverySettlements2 tds2 ON tds2.SettlementRight = pw.id 
	WHERE tblDP.[ReservationType] = 1


	DECLARE @newPwIds TABLE (Id INT, ReservationId INT, DeliveryId INT)
	--Dodanie PW dla rezerwacji ze wskazaniem dostaw
	INSERT INTO [HM].[PW] (flag, subtyp, typ, idmg, iddw, idtw, ilosc, wartosc, ilosczreal, data)
	OUTPUT inserted.id, inserted.idmg, inserted.iddw INTO @newPwIds
	SELECT DISTINCT IIF((dw.flag & 0x01 != 0), 0x04, 0), 0, 26, tblRZ.Id, tblRZD.DeliveryId, tblDP.ProductId, 0, 0, 0, @docDate 
	FROM @tblPositionsReservationsDelivery tblRZD
		JOIN @tblPositionsReservations tblRZ ON tblRZ.PositionId = tblRZD.PositionId AND tblRZ.InventoryId = tblRZD.InventoryId
		JOIN @tblDocumentPositions tblDP ON tblDP.Id = tblRZD.PositionId
		JOIN HM.DW dw ON dw.id = tblRZD.DeliveryId
		LEFT JOIN @tblDeliverySettlements tds ON tds.DeliveryId = tblRZD.DeliveryId AND tds.NodeId = tblRZD.NodeId --sprawdzenie czy PW realizuje inne PW
	WHERE tblRZD.Id = 0 AND (tblRZD.ReservedQuantity > 0 
									OR tds.DeliverySettlementId IS NOT NULL) --PW które realizuje dodajemy nawet jeśli jest z zerem

	--dodanie powiązań
	DECLARE @mergedPwIds TABLE (id1 INT, id2 INT)

	MERGE INTO HM.ZZ AS ZZ 
	USING (SELECT DISTINCT pw_dp.id id1, pw_mz.id id2, tds.RealizationQuantity ilosc
			FROM @tblDeliverySettlements tds 
			JOIN HM.RZ rz_mz on tds.ParentId = rz_mz.id
			JOIN HM.PW pw_mz ON pw_mz.typ = 26 AND pw_mz.idmg = rz_mz.id
			JOIN HM.DW dw_mz ON pw_mz.iddw = dw_mz.id
			JOIN HM.RZ rz_dp on tds.ReservationId = rz_dp.id
			JOIN HM.PW pw_dp ON pw_dp.typ = 26 AND pw_dp.idmg = rz_dp.id
			JOIN HM.DW dw_dp ON pw_dp.iddw = dw_dp.id
			WHERE dw_mz.id = dw_dp.id AND
				tds.DeliveryId = dw_mz.id) AS TDS
	ON ZZ.TYP = 96 and zz.baza1 = 36 and zz.id1 = tds.id1 and zz.baza2 = 36 and zz.id2 = tds.id2
	WHEN NOT MATCHED BY TARGET THEN
		INSERT (flag, subtyp, typ, baza1, id1, baza2, id2, ilosc, kod1, kod2, cena, waluta, opis, baza3, ilosc2, magazyn)
		VALUES (0, 0, 96, 36, tds.id1, 36, tds.id2, tds.ilosc, '', '', 0, '', '', 0, 0, 0)
	OUTPUT Inserted.id1, Inserted.id2 INTO @mergedPwIds;

	--aktualizacja nowych identyfikatorów w @tblPositionsReservationsDelivery
	UPDATE tblRZD SET id = newPW.Id
	FROM @tblPositionsReservationsDelivery tblRZD
		JOIN @tblPositionsReservations tblRZ ON tblRZ.PositionId = tblRZD.PositionId AND tblRZ.InventoryId = tblRZD.InventoryId
		JOIN @newPwIds newPW ON newPW.ReservationId = tblRZ.Id AND newPW.DeliveryId = tblRZD.DeliveryId
	WHERE tblRZD.Id = 0

	-- dodanie nowych identyfikatorów PW w @tblDeliverySettlements2
	INSERT INTO @tblDeliverySettlements2
	SELECT 
		mPwIDS.id1 AS [SettlementLeft],				
		mPwIDS.id2 AS [SettlementRight],				
		IIF(pwL.ilosc <> 0, HM.ROUND2(pwL.wartosc / pwL.ilosc  * tds.Quantity), 0) AS NewValueLeft,
		(pwL.wartosc) - IIF(pwL.ilosc <> 0, HM.ROUND2((pwL.wartosc) / (pwL.ilosc ) * tds.Quantity), 0)
			AS NewValueRight,
		pwL.ilosc AS OldQuantityLeft
	FROM @mergedPwIds mPwIDS 
	JOIN HM.PW pwL ON pwL.id = mPwIDS.id1
	JOIN @tblDeliverySettlements tds ON tds.DeliverySettlementId = mPwIDS.id1
	
	
	DECLARE @newQuantity DECIMAL(24, 4)

	--Aktualizacja ilości i wartości PW dla rezerwacji ze wskazaniem dostaw
	UPDATE pw SET 
		--rezerwacja tylko do dostępnego stanu w dostawie
		@newQuantity = IIF(tblRZD.ReservedQuantity - pw.ilosc < dw.iloscdosp + ISNULL(tds2.OldQuantityLeft, 0), 
							tblRZD.ReservedQuantity, 
							pw.ilosc + dw.iloscdosp + ISNULL(tds2.OldQuantityLeft, 0)),

		wartosc = CASE
				WHEN @newQuantity = pw.ilosc THEN wartosc	--skoro ilość bez zmian, wartość też bez zmian
				ELSE
					ISNULL(tds2.NewValueRight, 0) + 										-- wartość od ilości zrealizowanej
					IIF(pw.ilosc + dw.iloscdosp <> 0, HM.ROUND2((pw.wartosc + dw.wartoscdosp) / (pw.ilosc + dw.iloscdosp) 
								* (@newQuantity - ISNULL(tds.RealizationQuantity, 0))), 0)		-- wartość od ilości ponad realizację 
				END,

		ilosc = @newQuantity,
		--flag = 0, 
		iddw = tblRZD.DeliveryId
	FROM HM.PW pw 
		JOIN @tblPositionsReservationsDelivery tblRZD ON tblRZD.Id = pw.id
		JOIN HM.DW dw ON dw.id = tblRZD.DeliveryId
		LEFT JOIN @tblDeliverySettlements2 tds2 ON tds2.SettlementRight = pw.id
		LEFT JOIN @tblDeliverySettlements tds ON tds.DeliveryId = tblRZD.DeliveryId AND tds.NodeId = tblRZD.NodeId


	-- aktualizacja ilości i wartości w rezerwacji na podstawie sumy rozliczeń (PW) - mamy spójność
	UPDATE rz SET
		rz.iloscrz = sumPW.ilosc,
		rz.wartosc = sumPW.wartosc
	FROM HM.RZ rz
		JOIN (SELECT pw.idmg AS ReservationId, SUM(pw.ilosc) AS ilosc, SUM(pw.wartosc) AS wartosc 
				FROM HM.PW pw 
				JOIN @tblPositionsReservations tblRZ ON tblRZ.Id = pw.idmg AND pw.typ = 26
				GROUP BY pw.idmg ) sumPW ON sumPW.ReservationId = rz.id


	IF @correction = 0
	BEGIN
		--Aktualizacja typu pozycji			
		IF (@docType = 0)
		BEGIN
			UPDATE [HM].[DP] SET
                typ = dp.typ | IIF(dp.[typ] & 0x08 = 0,0,IIF(dp.ilosc > ISNULL(sumRZ.sumIloscrz,0),0x40,0))
            FROM [HM].[DP] dp
            JOIN @tblDocumentPositions tblDP ON tblDP.Id = dp.id
            LEFT JOIN (
                SELECT R.idpozrz, SUM(R.[iloscrz]) as sumIloscrz
                FROM HM.RZ R
                WHERE R.bazapozrz = @bazaPozRZ
                GROUP BY R.idpozrz
            ) sumRZ ON dp.id = sumRZ.idpozrz
            WHERE (dp.flag & 0x0200 <> 0)
		END
		ELSE IF (@docType = 1)
		BEGIN
			UPDATE [HM].[ZP] SET
				typ = zp.typ | IIF(zp.[typ] & 0x08 = 0,0,IIF(zp.ilosc > ISNULL(sumRZ.sumIloscrz,0),0x40,0))
			FROM [HM].[ZP] zp
			JOIN @tblDocumentPositions tblDP ON tblDP.Id = zp.id
            LEFT JOIN (
                SELECT R.idpozrz, SUM(R.[iloscrz]) as sumIloscrz
                FROM HM.RZ R
                WHERE R.bazapozrz = @bazaPozRZ
                GROUP BY R.idpozrz
            ) sumRZ ON zp.id = sumRZ.idpozrz
			WHERE (zp.flag & 0x0200 <> 0)					
		END
		ELSE IF (@docType = 2)
		BEGIN
			UPDATE [HM].[MZ] SET
				typ = mz.typ | IIF(mz.[typ] & 0x08 = 0,0,IIF(mz.ilosc > ISNULL(sumRZ.sumIloscrz,0),0x40,0))
			FROM [HM].[MZ] mz
			JOIN @tblDocumentPositions tblDP ON tblDP.Id = mz.id
            LEFT JOIN (
                SELECT R.idpozrz, SUM(R.[iloscrz]) as sumIloscrz
                FROM HM.RZ R
                WHERE R.bazapozrz = @bazaPozRZ
                GROUP BY R.idpozrz
            ) sumRZ ON mz.id = sumRZ.idpozrz
			WHERE (mz.flag & 0x0200 <> 0)					
		END
	END
												
	IF exists (select top 1 * from @tblDeliverySettlements)
	BEGIN /* Obsługa rezerwacji powiązanych z zamówieniem */	

		--powracająca ilość, aktualizacja ilości i wartości w PW
		UPDATE HM.PW SET 
				pw.ilosc = tds.Quantity, 
				pw.wartosc = tds2.NewValueLeft
		FROM HM.PW pw
		JOIN @tblDeliverySettlements tds ON pw.id = tds.DeliverySettlementId	
		JOIN @tblDeliverySettlements2 tds2 ON tds2.SettlementLeft = tds.DeliverySettlementId

		--powracająca ilość i wartość - aktualizacja rezerwacji na podstawie sum z PW
		UPDATE HM.RZ SET rz.iloscrz = sumPW.ilosc, rz.wartosc = sumPW.wartosc
		FROM ( SELECT DISTINCT tds1.ReservationId FROM @tblDeliverySettlements tds1 ) tds 
		JOIN HM.RZ rz ON rz.id = tds.ReservationId
		JOIN ( SELECT pw.typ, pw.idmg idrz, SUM(pw.ilosc) ilosc, SUM(pw.wartosc) wartosc 
				FROM HM.PW 
				GROUP BY pw.typ, pw.idmg) sumPW ON sumPW.typ = 26 AND sumPW.idrz = rz.id

		--Uzupełnienie ilości zrealizowanych dla dokumentu sprzedaży
		UPDATE @tblDeliverySettlements SET DeliveryMaxQuantity = DeliveryMaxQuantity * -1
		WHERE DeliveryMaxQuantity < 0

		--uzupełnienie ilości zrealizowanej (po stronie Right) w rekordach PW 
		UPDATE pwR SET pwR.ilosczreal = IIF(pwR.ilosc > tds.MaxRealizationQuantity, tds.MaxRealizationQuantity,pwR.ilosc)
		FROM @tblDeliverySettlements tds 
		JOIN @tblDeliverySettlements2 tds2 ON tds2.SettlementLeft = tds.DeliverySettlementId
		JOIN HM.PW pwR ON pwR.id = tds2.SettlementRight

		-- to samo w rezerwacjach (po stronie Right), na podstawie sumy z PW
		UPDATE HM.RZ SET rz.iloscrl = sumPW.ilosczreal
		FROM ( SELECT DISTINCT tds1.ParentId FROM @tblDeliverySettlements tds1) tds 
		JOIN HM.RZ ON rz.id = tds.ParentId
		JOIN ( SELECT pw.typ, pw.idmg idrz, SUM(pw.ilosczreal) ilosczreal 
				FROM HM.PW 
				GROUP BY pw.typ, pw.idmg) sumPW ON sumPW.typ = 26 AND sumPW.idrz = RZ.id

		--uzupełnienie poprawne powiązań pomiędzy realizowanymi rezerwacjami (ZZ)
		UPDATE zz SET zz.ilosc = ISNULL(gDS.RealizationQuantitySum, 0)
		FROM (SELECT ParentId, SUM(RealizationQuantity) AS RealizationQuantitySum FROM @tblDeliverySettlements GROUP BY ParentId) gDS 
		JOIN HM.ZZ zz ON zz.typ = 96 AND zz.baza2 = 152 AND zz.id2 = gDS.ParentId 

		UPDATE zz SET zz.ilosc = ISNULL(tds.RealizationQuantity,0)
		FROM @tblDeliverySettlements tds
		JOIN @tblDeliverySettlements2 tds2 ON tds2.SettlementLeft = tds.DeliverySettlementId
		JOIN HM.ZZ ON zz.typ = 96 AND zz.baza2 = 36 AND zz.id2 = tds2.SettlementRight
				
	END

	--Aktualizacja stanu handlowego w dostawach ilościowych (na podstawie sumy z rezerwacji ilościowych), celowo po akrualizacji rezerwacji powiązanych
	UPDATE DW SET DW.iloscdosp = -ISNULL(sumRZ.iloscrz, 0),
				  DW.typ = IIF(ISNULL(sumRZ.iloscrz, 0) > 0,1,0)
	FROM HM.DW DW
		JOIN (SELECT DISTINCT ProductId, InventoryId FROM @smToUpdate WHERE ReservationType = 1) smToUpdate 
				ON smToUpdate.InventoryId = DW.magazyn AND smToUpdate.ProductId = DW.idtw AND DW.numer = -DW.magazyn
		LEFT JOIN (SELECT SUM(HM.ROUND4(iloscrz)) AS iloscrz, idtw, magazyn 
				FROM HM.RZ 
				WHERE RZ.subtyp & 0x01 = 1 
				GROUP BY RZ.idtw, RZ.magazyn) sumRZ ON sumRZ.idtw = DW.idtw AND sumRZ.magazyn = DW.magazyn

	--tabela ze stanami w dostawach po aktualizacji tych stanów
	DECLARE @tblDeliveryStateAfterUpdate TABLE (DeliveryId INT, ProductId INT, InventoryId INT, DeliveryState DECIMAL(24, 4))

	--Aktualizacja stanu handlowego w dostawach wartościowych (na podstawie sumy z PW)
	UPDATE DW SET dw.iloscdosp = dw.stan - ISNULL(sumPW.ilosc, 0),
				  dw.wartoscdosp = dw.wartoscst - ISNULL(sumPW.wartosc, 0)
	OUTPUT inserted.id, inserted.idtw, inserted.magazyn, inserted.iloscdosp INTO @tblDeliveryStateAfterUpdate
	FROM HM.DW dw
		JOIN @dwToUpdate dwToUpdate ON dwToUpdate.Id = dw.id
		LEFT JOIN (SELECT pw.iddw, SUM(HM.ROUND4(pw.ilosc)) AS ilosc, SUM(HM.ROUND2(pw.wartosc)) AS wartosc 
					FROM HM.PW pw WHERE pw.typ = 26 GROUP BY pw.iddw) AS sumPW ON sumPW.iddw = dw.id

	--próba zarezerwowania ponad stan w dostawie, zgłaszamy wyjątek z komunikatem
	IF EXISTS(SELECT TOP 1 1 FROM @tblDeliveryStateAfterUpdate WHERE DeliveryState < 0)
	BEGIN
        DECLARE @msgDeliveryState VARCHAR(MAX)

		--składanie komunikatu

        --SQL2017 i wyższe (polecenie STRING_AGG)
		-- SELECT @msgDeliveryState =  
		-- 		STRING_AGG('Towar: ''' + tw.kod + '''' + CHAR(13) + 
		-- 					'Magazyn: ''' + mg.kod + '''' + CHAR(13) + 
		-- 					'Dostawa: ''' + dw.kod + '''' + CHAR(13) + 
		-- 					'Ilość powyżej stanu: ' + CONVERT(VARCHAR, -tblDeliveryState.DeliveryState), CHAR(13) + CHAR(13)) 
		-- FROM @tblDeliveryStateAfterUpdate tblDeliveryState
		-- 	JOIN HM.TW tw ON tw.id = tblDeliveryState.ProductId
		-- 	JOIN HM.KH mg ON mg.id = tblDeliveryState.InventoryId
		-- 	JOIN HM.DW dw ON dw.id = tblDeliveryState.DeliveryId
		-- WHERE tblDeliveryState.DeliveryState < 0;
        ---

        --SQL2016
		SET @msgDeliveryState =  
				(SELECT CHAR(13) + CHAR(13) + 'Towar: ''' + tw.kod + '''' + CHAR(13) + 
							'Magazyn: ''' + mg.kod + '''' + CHAR(13) + 
							'Dostawa: ''' + dw.kod + '''' + CHAR(13) + 
							'Ilość powyżej stanu: ' + CONVERT(VARCHAR, -tblDeliveryState.DeliveryState) 
		            FROM @tblDeliveryStateAfterUpdate tblDeliveryState
			            JOIN HM.TW tw ON tw.id = tblDeliveryState.ProductId
			            JOIN HM.KH mg ON mg.id = tblDeliveryState.InventoryId
			            JOIN HM.DW dw ON dw.id = tblDeliveryState.DeliveryId
		            WHERE tblDeliveryState.DeliveryState < 0
                    FOR XML PATH(''))

        SET @msgDeliveryState = REPLACE(@msgDeliveryState, '&#x0D;', CHAR(13))   --przywrócone entery po wygenerowaniu XML-a
        SET @msgDeliveryState = STUFF(@msgDeliveryState, 1, 2, '')              --usunięcie dwóch pierwszych enterów
        ---

		SET @msgDeliveryState = 'Operacja nie powiodła się.' +  CHAR(13) + 'Próba zarezerwowania powyżej stanu w dostawie:' + CHAR(13) + CHAR(13) + @msgDeliveryState;

		THROW 50000, @msgDeliveryState, 1; 
	END


	--tabela ze stanami w magazynach po aktualizacji
	DECLARE @tblInventoryStateAfterUpdate TABLE (StateId INT, ProductId INT, InventoryId INT, InventoryState DECIMAL(24, 4))

	--Aktualizacja stanu handlowego, na podstawie sumy rezerwacji
	UPDATE [HM].[SM] SET 
		stanHandl =  SM.stan - pom.Ilosc
	OUTPUT inserted.id, inserted.idtw, inserted.magazyn, inserted.stanHandl INTO @tblInventoryStateAfterUpdate
	FROM (
		SELECT SM.id, ISNULL(sumRZ.iloscrz, 0) AS ilosc
		FROM HM.SM SM
			JOIN (SELECT DISTINCT ProductId, InventoryId FROM @smToUpdate) smToUpdate 
				ON smToUpdate.InventoryId = SM.magazyn AND smToUpdate.ProductId = SM.idtw
			LEFT JOIN (SELECT SUM(HM.ROUND4(iloscrz)) AS iloscrz, idtw, magazyn 
				FROM HM.RZ 
				GROUP BY RZ.idtw, RZ.magazyn) sumRZ ON sumRZ.idtw = SM.idtw AND sumRZ.magazyn = SM.magazyn
		) pom
	WHERE sm.Id = pom.Id and sm.stanHandl <> (SM.stan - pom.Ilosc)

	--próba zarezerwowania ponad stan w magazynie, zgłaszamy wyjątek z komunikatem
	IF EXISTS(SELECT TOP 1 1 FROM @tblInventoryStateAfterUpdate WHERE InventoryState < 0)
	BEGIN
		DECLARE @msgInventoryState VARCHAR(MAX)

		--składanie komunikatu

		--SQL2017 i wyższe (polecenie STRING_AGG)
		---
		-- SELECT @msgInventoryState =  
		-- 		STRING_AGG('Towar: ''' + tw.kod + '''' + CHAR(13) + 
		-- 					'Magazyn: ''' + mg.kod + '''' + CHAR(13) + 
		-- 					'Ilość powyżej stanu: ' + CONVERT(VARCHAR, -tblState.InventoryState), CHAR(13) + CHAR(13)) 
		-- FROM @tblInventoryStateAfterUpdate tblState
		-- 	JOIN HM.TW tw ON tw.id = tblState.ProductId
		-- 	JOIN HM.KH mg ON mg.id = tblState.InventoryId
		-- WHERE tblState.InventoryState < 0;
		---

		--SQL2016
		---
		SET @msgInventoryState =
			(SELECT CHAR(13) + CHAR(13) + 'Towar: ''' + tw.kod + '''' + CHAR(13) + 
							'Magazyn: ''' + mg.kod + '''' + CHAR(13) + 
							'Ilość powyżej stanu: ' + CONVERT(VARCHAR, -tblState.InventoryState) 
				FROM @tblInventoryStateAfterUpdate tblState
					JOIN HM.TW tw ON tw.id = tblState.ProductId
					JOIN HM.KH mg ON mg.id = tblState.InventoryId
				WHERE tblState.InventoryState < 0
				FOR XML PATH(''))

        SET @msgInventoryState = REPLACE(@msgInventoryState, '&#x0D;', CHAR(13))   --przywrócone entery po wygenerowaniu XML-a
        SET @msgInventoryState = STUFF(@msgInventoryState, 1, 2, '')              --usunięcie dwóch pierwszych enterów
		---

		SET @msgInventoryState = 'Operacja nie powiodła się.' +  CHAR(13) + 'Próba zarezerwowania powyżej stanu w magazynie:' + CHAR(13) + CHAR(13) + @msgInventoryState;
		THROW 50000, @msgInventoryState, 1; 
	END

END

GO
