IF NOT EXISTS (
  SELECT * 
  FROM   sys.columns 
  WHERE  object_id = OBJECT_ID(N'[HM].[DK]') 
         AND name = 'typeExternal'
)
ALTER TABLE HM.DK 
	ADD typeExternal INT DEFAULT ((0)) NOT NULL

GO

IF NOT EXISTS (
  SELECT * 
  FROM   sys.columns 
  WHERE  object_id = OBJECT_ID(N'[HM].[ZO]') 
         AND name = 'typeExternal'
)
ALTER TABLE HM.ZO 
	ADD typeExternal INT DEFAULT ((0)) NOT NULL

GO

IF NOT EXISTS (
  SELECT * 
  FROM   sys.columns 
  WHERE  object_id = OBJECT_ID(N'[HM].[DK]') 
         AND name = 'idExternal'
)
ALTER TABLE HM.DK 
    ADD idExternal INT NULL

GO

IF NOT EXISTS (
  SELECT * 
  FROM   sys.columns 
  WHERE  object_id = OBJECT_ID(N'[HM].[ZO]') 
         AND name = 'idExternal'
)
ALTER TABLE HM.ZO 
    ADD idExternal INT NULL

GO

IF NOT EXISTS (
  SELECT * 
  FROM   sys.columns 
  WHERE  object_id = OBJECT_ID(N'[HM].[DK]') 
         AND name = 'id2External'
)
ALTER TABLE HM.DK 
    ADD id2External VARCHAR(32) NULL

GO

IF NOT EXISTS (
  SELECT * 
  FROM   sys.columns 
  WHERE  object_id = OBJECT_ID(N'[HM].[ZO]') 
         AND name = 'id2External'
)
ALTER TABLE HM.ZO 
    ADD id2External VARCHAR(32) NULL

GO

CREATE OR ALTER FUNCTION HM.ft_TradeBaseDocument (@documentBase INT, @documentId INT)
RETURNS TABLE AS RETURN
(
	-- dokument sprzedazy i zakupu
	SELECT	       
		   DK.statusRDF                                                                    AS RDFStatus,
	       DK.odid                                                                         AS ReceiverId,
	       (ISNULL((SELECT AD.nazwaAdr FROM HM.AD WHERE AD.id = DK.odadid),''))            AS ReceiverAddressName,
	       CASE WHEN DK.plattyp IS NULL OR DK.plattyp = 0 THEN -1 ELSE DK.plattyp END      AS PayRegistryId,
	       ISNULL(DK.formaplatn, 0)                                                        AS PaymentFormClientId,
	       DK.plattermin                                                                   AS PaymentDeadline,
	       CAST(DK.walBrutto AS DECIMAL(24,2))                                             AS ValueGross,
	       CAST((DK.walBrutto - DK.walNetto) AS DECIMAL(24,2))                             AS ValueVat,
	       CAST(DK.netto AS DECIMAL(24,2))                                                 AS ValuePLN,
	       CAST((DK.netto + DK.vat) AS DECIMAL(24,2))                                      AS ValueGrossPLN,
		   CAST(DK.grupacen + 1 AS SMALLINT)                                               AS PriceKind,
	       CAST(DK.vat AS DECIMAL(24,2))                                                   AS ValueVatPLN,
	       DK.waluta                                                                       AS Currency,
	       CAST(DK.kurs AS DECIMAL(24,8))												   AS ExchangeRate,
	       IIF(DK.splitPayment = 1,'true','false')                                         AS SplitPayment,
	       DK.datarej                                                                      AS InRegisterDate,
		   1                                                                               AS InventorySettlementStatus,
		   CAST(CASE WHEN DK.exp_fk & 0x10 <> 0 THEN 2
	                 WHEN DK.exp_fk & 0x08 <> 0 THEN 1
	                 ELSE                              0 END AS SMALLINT)                  AS PublishInventoryDocumentMode,
	       DK.ok                                                                           AS CashSettled,
		   CAST(IIF(DK.flag & 128 <> 0, 2, 1) AS SMALLINT )                                AS RegisterMethod,
		   CAST(IIF(DK.flag & 0x8000 <> 0, 1, 0) AS SMALLINT)                              AS CalculateVatMethod,
		   CAST(ISNULL(PN.kwotarozl, 0) AS DECIMAL(24, 2))								   AS PaymentsSum,
		   CAST(ISNULL(PN.kwota, DK.walBrutto) AS DECIMAL(24, 2))						   AS AllToPay,
           DK.typeExternal                                                                 AS TypeExternal,
           DK.idExternal                                                                   AS IdExternal,
           DK.id2External                                                                  AS Id2External
	  FROM HM.DK
	       LEFT OUTER JOIN HM.TR ON TR.typ = 1
	                                AND TR.owner_base = @documentBase
	                                AND TR.owner_id = IIF(DK.iddokkoryg IS NULL, DK.id, DK.iddokkoryg)
	       LEFT OUTER JOIN SSCommon.STContractors ON STContractors.Id = DK.khid
		   LEFT OUTER JOIN HM.PN ON PN.dkid = @documentId
	 WHERE @documentBase IN (16, 34) AND DK.id = @documentId

	UNION ALL

	-- zamowienie obce
	SELECT 
	       ZO.statusRDF                                                                    AS RDFStatus,
	       ZO.odid                                                                         AS ReceiverId,
	       (ISNULL((SELECT AD.nazwaAdr FROM HM.AD WHERE AD.id = ZO.odadid),''))            AS ReceiverAddressName,
	       CASE WHEN ZO.plattyp IS NULL OR ZO.plattyp = 0 THEN -1 ELSE ZO.plattyp END      AS PayRegistryId,
	       ISNULL(ZO.formaplatn, 0)                                                        AS PaymentFormClientId,
	       ZO.plattermin                                                                   AS PaymentDeadline,	       
	       CAST(ZO.walBrutto AS DECIMAL(24,2))                                             AS ValueGross,
	       CAST((ZO.walBrutto - ZO.walNetto) AS DECIMAL(24,2))                             AS ValueVat,
	       CAST(ZO.netto AS DECIMAL(24,2))                                                 AS ValuePLN,
	       CAST((ZO.netto + ZO.vat) AS DECIMAL(24,2))                                      AS ValueGrossPLN,
		   CAST(ZO.grupacen + 1 AS SMALLINT)                                               AS PriceKind,
	       CAST(ZO.vat AS DECIMAL(24,2))                                                   AS ValueVatPLN,
	       ZO.waluta                                                                       AS Currency,
	       CAST(ZO.kurs AS DECIMAL(24,8))												   AS ExchangeRate,
	       IIF(ZO.splitPayment = 1,'true','false')                                         AS SplitPayment,
	       ZO.datarej                                                                      AS InRegisterDate,
		   1                                                                               AS InventorySettlementStatus,
		    CAST(CASE WHEN ZO.exp_fk & 0x10 <> 0 THEN 2
	                 WHEN ZO.exp_fk & 0x08 <> 0 THEN 1
	                 ELSE                              0 END AS SMALLINT)                  AS PublishInventoryDocumentMode,
	       ZO.ok                                                                           AS CashSettled,
		   CAST(IIF(ZO.flag & 128 <> 0, 2, 1) AS SMALLINT )                                AS RegisterMethod,
		   CAST(IIF(ZO.flag & 0x8000 <> 0, 1, 0) AS SMALLINT)                              AS CalculateVatMethod,
		   0																			   AS PaymentsSum,
		   CAST(ZO.walBrutto AS DECIMAL(24, 2))											   AS AllToPay,
           ZO.typeExternal                                                                 AS TypeExternal,
           ZO.idExternal                                                                   AS IdExternal,
           ZO.id2External                                                                  AS Id2External
	  FROM HM.ZO
	       LEFT OUTER JOIN HM.TR ON TR.typ = 0 
	                                AND TR.owner_base = 45 
	                                AND TR.owner_id = ZO.id
	       LEFT OUTER JOIN SSCommon.STContractors ON STContractors.Id = ZO.khid
	 WHERE @documentBase = 45 AND ZO.id = @documentId

	UNION ALL

	-- Oferty
	SELECT 
	       CONVERT(SMALLINT, 0)                                                            AS RDFStatus,
	       ReceiverId,
	       (ISNULL((SELECT AD.nazwaAdr FROM HM.AD WHERE AD.id = ofr.ReceiverAddrId),''))   AS ReceiverAddressName,
	       PayRegistryId,
	       PaymentFormClientId,
	       NULL                                                                            AS PaymentDeadline,	       
	       ValueGross,
	       CAST((ValueGross - Value) AS DECIMAL(24,4))                                     AS ValueVat,
	       ValuePLN,
	       ValueGrossPLN,
		   PriceKind,
	       ValueVatPLN,
	       Currency,
	       ExchangeRate,
	       'false'                                                                         AS SplitPayment,
	       NULL                                                                            AS InRegisterDate,
		   0                                                                               AS InventorySettlementStatus,
		   CONVERT(SMALLINT, 0)                                                            AS PublishInventoryDocumentMode,
	       CONVERT(SMALLINT, 0)                                                            AS CashSettled,
		   CONVERT(SMALLINT, 0)                                                            AS RegisterMethod,
		   CONVERT(SMALLINT, 0)                                                            AS CalculateVatMethod,
		   0																			   AS PaymentsSum,
		   0                                    										   AS AllToPay,
           0                                                                               AS TypeExternal,
           NULL                                                                            AS IdExternal,
           NULL                                                                            AS Id2External
	  FROM HM.OfferDocuments ofr
	  WHERE @documentBase = 667 AND ofr.Id = @documentId
)

GO

CREATE OR ALTER FUNCTION [HM].[ft_ForeignOrderDocument] (@foreignOrderId INT, @removeInvalidXMLCharacters BIT)
RETURNS TABLE
AS
RETURN (
		WITH AdvanceInvoiceExists AS (
				SELECT COALESCE((
							SELECT TOP 1 1
							FROM HM.ZO zo
							INNER JOIN HM.ZZ zz ON zz.id1 = zo.id
							WHERE zo.id = @foreignOrderId
								AND zz.baza1 = 45
								AND zz.baza2 = 16
								AND zz.typ = 157
							), 0) [VALUE]
				)
		SELECT ZO.rr AS ManualSettlement,
			ZO.khadid,
			ZO.odadid,
			'true' AS [IsDocumentPosition],
			bd.[Id],
			bd.[Guid],
			bd.[DocumentNumber],
			bd.[SeriesNo],
			bd.[Name],
			bd.[CorrectedDocumentId],
			bd.[IssueDate],
			bd.[OperationDate],
			bd.[IsCorrection],
			bd.[Description],
			bd.[RecipientPerson],
			bd.[BaseType],
			bd.[DocumentTypeShortcut],
			bd.[DocumentNumerationShortcut],
			bd.[IsIssued],
			bd.[Value],
			bd.[DocumentStatus],
			bd.[DocumentStatusDescription],
			bd.[HmImported],
			bd.[Archived],
			bd.[FkExported],
			bd.[IsCreatedFromForeignOrder],
			bd.[Corrected],
			bd.[Canceled],
			bd.[IsActive],
			bd.[MigrationStatus],
			bd.[ContractorId],
			bd.[DepartmentId],
			bd.[AccountingPattern],
			bd.[IssuerId],
			bd.[IssuerLogin],
			bd.[DepartmentInSeries],
			bd.[Period],
            bd.[ManualSettlementStatus],
			sbd.[PriceTypeId],
			sbd.[Discount],
			sbd.[CorrectionCause],
			sbd.[ContractorLimitFlag],
			sbd.[ContractorLimitValue],
			sbd.[ContractorLimitCurrency],
			tbd.[PriceKind],
			tbd.[RDFStatus],
			tbd.[ReceiverId],
			tbd.[ReceiverAddressName],
			tbd.[PayRegistryId],
			tbd.[PaymentFormClientId],
			tbd.[PaymentDeadline],
			tbd.[ValueGross],
			tbd.[ValueVat],
			tbd.[ValuePLN],
			tbd.[ValueGrossPLN],
			tbd.[ValueVatPLN],
			tbd.[Currency],
			tbd.[ExchangeRate],
			tbd.[SplitPayment],
			tbd.[InRegisterDate],
			tbd.[InventorySettlementStatus],
			tbd.[PublishInventoryDocumentMode],
			tbd.[CashSettled],
			tbd.[RegisterMethod],
			tbd.[CalculateVatMethod],
			CASE WHEN AdvanceInvoiceExists.[VALUE] = 1 THEN 'true' ELSE 'false' END AS UseAdvanceInvoiceIsReadOnly,
			CASE WHEN (CASE WHEN AdvanceInvoiceExists.[VALUE] = 1 THEN 1 ELSE advanceInvoiceDeclared END) = 1 THEN 'true' ELSE 'false' END AS UseAdvanceInvoice,
			CASE WHEN typ = 2 THEN 'true' ELSE 'false' END AS IsForeignOrderVersion,
			CASE WHEN EXISTS (
						SELECT TOP 1 1
						FROM HM.ForeignOrdersVersions v
						WHERE v.ZoId = @foreignOrderId
						) THEN 'true' ELSE 'false' END AS ForeignOrderVersionExists,
			[adata].[KindId],
			[adata].[CatalogId],
			[adata].[Marker],
			[adata].[ReservationType],
            tbd.TypeExternal,
            tbd.IdExternal,
            tbd.Id2External
		FROM HM.ZO zo
		CROSS JOIN AdvanceInvoiceExists
		CROSS APPLY HM.ft_BaseDocument(45, @foreignOrderId, @removeInvalidXMLCharacters) bd
		CROSS APPLY HM.ft_SaleBaseDocument(45, @foreignOrderId) sbd
		CROSS APPLY HM.ft_TradeBaseDocument(45, @foreignOrderId) tbd
		CROSS APPLY HM.ft_AdditionalData(45, @foreignOrderId) adata
		WHERE ZO.TYP IN (1, 2)
			AND ZO.id = @foreignOrderId
		);

GO

CREATE OR ALTER FUNCTION [HM].[ft_SaleDocument] (@docId INT, @removeInvalidXMLCharacters BIT)
RETURNS TABLE
AS
RETURN
    WITH NpRateId AS
        (SELECT TOP 1
                Id
            FROM HM.XT
            WHERE super = 10000 AND subtyp = 7 AND flag & 0x200 <> 0),
            ZeroRateId AS 
        (SELECT TOP 1
                Id
            FROM HM.XT
            WHERE super = 10000 AND subtyp = 3)
    SELECT 
        CAST(dk.[kursDoch] AS DECIMAL(24, 8)) AS [ExchangeRatePIT],
        CAST(CASE 
                -- NIEZBEDNE sprawdzenie dla dokumentow w buforze -- dokument fiskalny jest oznaczany dopiero w poczatkowej fazie wystawiania 
                WHEN dk.[bufor] <> 0
                    THEN ISNULL(dokType.IsFiscal, 0)	--niefiskalny (0) /do fiskalizacji (1)
                WHEN dk.[exp_fk] & 0x04 <> 0
                    THEN 2	--zafiskalizowany
                WHEN dk.[exp_fk] & 0x02 <> 0 AND ISNULL(dokType.IsFiscal, 0) <> 0	--dla wystawionego sprawdzamy flagę w dokumencie oraz znacznik w typie dokumentu
                    THEN 1	--do fiskalizacji
                ELSE 0		--niefiskalny
                END AS SMALLINT) AS [FiscalizationState],
        IIF(dk.paragon > 0, CONVERT(VARCHAR(40), dk.paragon), '') AS ReceiptNumber,
        fiscalDeviceNo AS UniqueCashBoxNumber,
        dk.[jpk_v7] AS [JPK_V7],
        dk.[jpk] AS [JPK],
        dk.[rejestrVat] AS [SaleRegister],
        IIF(dk.flag & 0x20 <> 0, 'true', 'false') AS [IsGroupCorrection],
        dk.[eFaktura] AS [DigitalInvoice],
        HM.fnGetRelatedForeignOrderDocumentId(IIF(CASE 
                    WHEN dk.iddokkoryg IS NOT NULL
                        OR dk.flag & 0x20 = 0x20
                        THEN 1
                    ELSE 0
                    END = 1, dk.iddokkoryg, dk.id), dk.subtyp) AS RelatedForeignOrderDocumentId,
        HM.fnGetRelatedForeignOrderVersionId(dk.id) AS RelatedForeignOrderVersionId,
        CAST(ISNULL(parametersAdvanceInvoice.[Amount], 0) AS DECIMAL(24, 2)) AS AdvanceInvoiceAmount,
        dk.khadid,
        dk.odadid,
        dk.kursDoch,
        dk.exp_fk,
        dk.flag,
        dk.subtyp,
        dk.statusKsef,
        dk.dataWystawieniaKsef,
        dk.numberKsef,
        IIF(dk.guidEarch IS NOT NULL, 'true', 'false') AS IsGuidEarchFilled,
        dk.iddokkoryg,
        [bd].[Id],
        [bd].[Guid],
        [bd].[DocumentNumber],
        [bd].[SeriesNo],
        [bd].[Name],
        [bd].[CorrectedDocumentId],
        [bd].[IssueDate],
        [bd].[OperationDate],
        [bd].[IsCorrection],
        [bd].[Description],
        [bd].[RecipientPerson],
        [bd].[BaseType],
        [bd].[DocumentTypeShortcut],
        [bd].[DocumentNumerationShortcut],
        [bd].[IsIssued],
        [bd].[Value],
        [bd].[DocumentStatus],
        [bd].[DocumentStatusDescription],
        [bd].[HmImported],
        [bd].[Archived],
        [bd].[FkExported],
        [bd].[IsCreatedFromForeignOrder],
        [bd].[Corrected],
        [bd].[Canceled],
        [bd].[IsActive],
        [bd].[MigrationStatus],
        [bd].[ContractorId],
        [bd].[DepartmentId],
        [bd].[AccountingPattern],
        [bd].[IssuerId],
        [bd].[IssuerLogin],
        [bd].[DepartmentInSeries],
        [bd].[Period],
        [bd].[ManualSettlementStatus],
        [sbd].[PriceTypeId],
        [sbd].[Discount],
        [sbd].[CorrectionCause],
        [sbd].[ContractorLimitFlag],
        [sbd].[ContractorLimitValue],
        [sbd].[ContractorLimitCurrency],
        [sbd].[CanContractorNegotiatePrices],
        [tbd].[RDFStatus],
        [tbd].[ReceiverId],
        [tbd].[ReceiverAddressName],
        [tbd].[PayRegistryId],
        [tbd].[PaymentFormClientId],
        [tbd].[PaymentDeadline],
        [tbd].[ValueGross],
        [tbd].[ValueVat],
        [tbd].[ValuePLN],
        [tbd].[ValueGrossPLN],
        [tbd].[ValueVatPLN],
        [tbd].[Currency],
        [tbd].[ExchangeRate],
        [tbd].[SplitPayment],
        [tbd].[InRegisterDate],
        [tbd].[InventorySettlementStatus],
        [tbd].[PublishInventoryDocumentMode],
        [tbd].[CashSettled],
        [tbd].[RegisterMethod],
        [tbd].[CalculateVatMethod],
        [tbd].[PriceKind],
        [tbd].[PaymentsSum],
        [adata].[KindId],
        [adata].[CatalogId],
        [adata].[Marker],
        [adata].[ReservationType],
        IIF(SettlementOfAdvances.Count > 0, 'true', 'false') as IsSettlementAdvances,
        -SettlementOfAdvances.Value SettlementAdvancesNet,
        -SettlementOfAdvances.ValueVat SettlementAdvancesVat,
        -SettlementOfAdvances.ValueGross SettlementAdvancesGross,
        IIF(dk.procedura_OSS = 1, 'true', 'false') [IsOssProcedure],
        dk.krajDostawy_OSS [OssDeliveryCountryId],
        dk.krajWysylki_OSS [OssShippingCountryId],
        tbd.AllToPay,
        tbd.TypeExternal,
        tbd.IdExternal,
        tbd.Id2External
    FROM [HM].[DK] AS dk
    CROSS JOIN NpRateId  
    CROSS JOIN ZeroRateId
    CROSS APPLY HM.ft_BaseDocument(16, @docId, @removeInvalidXMLCharacters) bd
    CROSS APPLY HM.ft_TradeBaseDocument(16, @docId) tbd
    CROSS APPLY HM.ft_SaleBaseDocument(16, @docId) sbd
    CROSS APPLY HM.ft_AdditionalData(16, @docId) adata
    OUTER APPLY (
        SELECT ROUND(SUM(Value), 2) as Value, ROUND(SUM(ValueGross), 2) as ValueGross, ROUND(SUM(ValueVat), 2) as ValueVat, Count(1) as Count
        FROM HM.ft_VatRates(16, @docId, NpRateId.id, ZeroRateId.id) WHERE Id < 0
        ) SettlementOfAdvances
    LEFT JOIN [HM].[ParametersAdvanceInvoice] parametersAdvanceInvoice ON dk.[id] = parametersAdvanceInvoice.[DkId]
    OUTER APPLY (
        SELECT [TD].[flag] & 0x01 AS IsFiscal
        FROM [HM].[TD]
        WHERE [TD].[kod] = dk.[typ_dk]) AS dokType
    WHERE dk.[typ] = 0
        AND dk.[id] = @docId

GO

EXEC(N'CREATE OR ALTER VIEW [HM].[ForeignOrdersCardIndexView]
AS
SELECT ZO.id,
       [Common].[GetCardIndexMarkSymbol](CAST(ZO.znacznik AS INT)) AS Tag,
       ZO.kod AS Code,
       ZO.nazwa AS Name,
       ZO.seria AS Sequence,
       ZO.typ_dk AS DocumentType,
       ZO.data AS Date,
       ZO.netto + ZO.vat AS Brutto,
       ZO.walBrutto AS BruttoAmount,
       ZO.waluta AS Currency,
       ZO.opis AS Description,
       ZO.plattermin AS DueDate,
       CASE WHEN ZO.bufor = 1 THEN ''W buforze'' ELSE ''Wystawione'' END AS [Buffer],
	   IIF(ZO.anulowany <> 0, ''Anulowany'', (
	   CASE WHEN ZO.rr = 0 THEN
		   CASE ISNULL(TR.status, 32)
			   WHEN 4 THEN ''Do realizacji''
			   WHEN 8 THEN ''Realizacja częściowa''
			   WHEN 16 THEN ''Realizacja przygotowana''
			   WHEN 32 THEN ''Zrealizowano''
		   END
		ELSE
		   CASE ZO.ok
			   WHEN 0 THEN ''Do realizacji''
			   ELSE ''Zrealizowano''
		   END		
	   END)) AS SettlementState,
       ZO.aktywny AS IsActive,
       CASE WHEN ZO.statusRDF = 0 THEN ''Brak dokumentu''
            WHEN ZO.statusRDF = 1 THEN ''Dokument bez PDF''
            ELSE ''Dokument z PDF'' END AS DocumentStatusInRepository,
       CASE ZO.statusMIG WHEN 0 THEN ''Symfonia ERP''
				         WHEN 1 THEN ''Zmigrowany''
				         WHEN 2 THEN ''Błędny z Symfonii 2.0'' END AS StatusMig,
       ZO.netto AS Netto,
	   ZO.Vat AS ValueVat,
       ZO.walNetto AS NettoAmount,
	   dir.path AS CatalogPath,
       Kind.kod AS KindName,
       UsrRights.UserId AS UserId,
	   ISNULL((SELECT KH.kod FROM hm.kh WHERE KH.id = ZO.magazyn), '''') AS Department,
       ISNULL(SSCommon.STUsers.LoginName, '''') AS CreatorCode,
       ISNULL(SSCommon.STUsers.Id, 0) AS osobaId,
       ISNULL(SSCommon.vKh.kod, '''') AS ContractorCode,
       ISNULL(AD.nazwa, '''') AS ContractorName, 
       ISNULL(VKh1.kod, '''') AS ReceiverCode, 
       ISNULL(AD1.nazwa, '''') AS ReceiverName,
	   ISNULL(AD1.nazwaAdr,'''') AS ShortcutAddressOfDelivery,
	   ISNULL(AD1.miejscowosc,'''') AS PlaceOfDelivery
		,ISNULL(AD1.kodpocz,'''') AS PostCodeDelivery
		,ISNULL(AD1.ulica,'''') + '' ''+ AD1.dom + ''/'' + AD1.lokal AS AddressOfDelivery
		,ISNULL(krajedos.kod,'''') AS CountryDelivery
		,ISNULL(AD.nip,'''') AS KHNip
        ,CAST(
			(CASE 
				WHEN EXISTS(select 1
					FROM HM.ZZ tmp_zz
					inner join HM.DK tmp_dk on tmp_dk.id = tmp_zz.id2 and tmp_zz.baza2 = 16
					where tmp_zz.typ = 157 and tmp_zz.baza1 = 45 and tmp_zz.id1 = zo.id
				) THEN 1 ELSE HM.ZO.advanceInvoiceDeclared END
			) as bit
		) as UseAdvanceInvoice
        ,CASE
	    WHEN ZO.typeExternal <> 0  THEN ''B2B''
        WHEN ZO.typeExternal = 0  THEN ''HM''
	    END AS DocumentSource
       ,SOC.*
FROM HM.ZO
JOIN HM.AppUserDepartments UsrRights ON UsrRights.DepartmentId=ZO.magazyn
JOIN HM.DocumentTypePermission DocPermission ON DocPermission.UserId=UsrRights.UserId AND DocPermission.DocumentTypeCode = zo.typ_dk
LEFT JOIN HM.ZO AS Kind ON HM.ZO.rodzaj = Kind.id
LEFT JOIN SSCommon.HMF_SalesOrderClassification SOC ON SOC.ElementId = ZO.id
LEFT JOIN HM.TR ON (TR.owner_id = CASE WHEN ZO.iddokkoryg IS NULL THEN ZO.id ELSE ZO.iddokkoryg END) AND TR.owner_base = 45 AND TR.typ = 0
LEFT JOIN SSCommon.STUsers ON ZO.wystawil = SSCommon.STUsers.Id 
LEFT JOIN SSCommon.vKh ON SSCommon.vKh.id = ZO.khid
LEFT JOIN HM.AD ON AD.id = ZO.khadid
LEFT JOIN SSCommon.vKh AS VKh1 ON VKh1.id = ZO.odid
LEFT JOIN HM.AD AS AD1 ON AD1.id = ZO.odadid  
LEFT JOIN hm.vKraje krajedos ON AD1.idKraju=krajedos.id
LEFT JOIN hm.Directories dir ON dir.id=ZO.katalog
INNER JOIN SSCommon.STGukView guk on  ISNULL(guk.ContractorId,0)= ISNULL(zo.khid,0)
Where ZO.typ = 1 
')

EXEC(N'CREATE OR ALTER  VIEW [HM].[ForeignOrdersCardIndexWithoutTrView]
AS
SELECT ZO.id,
       [Common].[GetCardIndexMarkSymbol](CAST(ZO.znacznik AS INT)) AS Tag,
       ZO.kod AS Code,
       ZO.nazwa AS Name,
       ZO.seria AS Sequence,
       ZO.typ_dk AS DocumentType,
       ZO.data AS Date,
       ZO.netto + ZO.vat AS Brutto,
       ZO.walBrutto AS BruttoAmount,
       ZO.waluta AS Currency,
       ZO.opis AS Description,
       ZO.plattermin AS DueDate,
       CASE WHEN ZO.bufor = 1 THEN ''W buforze'' ELSE ''Wystawione'' END AS [Buffer],
       ZO.aktywny AS IsActive,
       CASE WHEN ZO.statusRDF = 0 THEN ''Brak dokumentu''
            WHEN ZO.statusRDF = 1 THEN ''Dokument bez PDF''
            ELSE ''Dokument z PDF'' END AS DocumentStatusInRepository,
       CASE ZO.statusMIG WHEN 0 THEN ''Symfonia ERP''
				         WHEN 1 THEN ''Zmigrowany''
				         WHEN 2 THEN ''Błędny z Symfonii 2.0'' END AS StatusMig,
       ZO.netto AS Netto,
	   ZO.Vat AS ValueVat,
       ZO.walNetto AS NettoAmount,
	   dir.path AS CatalogPath,
       Kind.kod AS KindName,
       UsrRights.UserId AS UserId,
	   ISNULL((SELECT KH.kod FROM hm.kh WHERE KH.id = ZO.magazyn), '''') AS Department,
       ISNULL(SSCommon.STUsers.LoginName, '''') AS CreatorCode,
       ISNULL(SSCommon.STUsers.Id, 0) AS osobaId,
       ISNULL(SSCommon.vKh.kod, '''') AS ContractorCode,
       ISNULL(AD.nazwa, '''') AS ContractorName, 
       ISNULL(VKh1.kod, '''') AS ReceiverCode, 
       ISNULL(AD1.nazwa, '''') AS ReceiverName,
	   ISNULL(AD1.nazwaAdr,'''') AS ShortcutAddressOfDelivery,
        ISNULL(AD1.miejscowosc,'''') AS PlaceOfDelivery
		,ISNULL(AD1.kodpocz,'''') AS PostCodeDelivery
		,ISNULL(AD1.ulica,'''') + '' ''+ AD1.dom + ''/'' + AD1.lokal AS AddressOfDelivery
		,ISNULL(krajedos.kod,'''') AS CountryDelivery
		,ISNULL(AD.nip,'''') AS KHNip
        ,CAST(
			(CASE 
				WHEN EXISTS(select 1
					FROM HM.ZZ tmp_zz
					inner join HM.DK tmp_dk on tmp_dk.id = tmp_zz.id2 and tmp_zz.baza2 = 16
					where tmp_zz.typ = 157 and tmp_zz.baza1 = 45 and tmp_zz.id1 = zo.id
				) THEN 1 ELSE HM.ZO.advanceInvoiceDeclared END
			) as bit
		) as UseAdvanceInvoice
        ,CASE
	    WHEN ZO.typeExternal <> 0  THEN ''B2B''
        WHEN ZO.typeExternal = 0  THEN ''HM''
	    END AS DocumentSource
       ,SOC.*
FROM HM.ZO
JOIN HM.AppUserDepartments UsrRights ON UsrRights.DepartmentId=ZO.magazyn
JOIN HM.DocumentTypePermission DocPermission ON DocPermission.UserId=UsrRights.UserId AND DocPermission.DocumentTypeCode = zo.typ_dk
LEFT JOIN HM.ZO AS Kind ON HM.ZO.rodzaj = Kind.id
LEFT JOIN SSCommon.HMF_SalesOrderClassification SOC ON SOC.ElementId = ZO.id
LEFT JOIN SSCommon.STUsers ON ZO.wystawil = SSCommon.STUsers.Id 
LEFT JOIN SSCommon.vKh ON SSCommon.vKh.id = ZO.khid
LEFT JOIN HM.AD ON AD.id = ZO.khadid
LEFT JOIN SSCommon.vKh AS VKh1 ON VKh1.id = ZO.odid
LEFT JOIN HM.AD AS AD1 ON AD1.id = ZO.odadid 
LEFT JOIN hm.vKraje krajedos ON AD1.idKraju=krajedos.id
LEFT JOIN hm.Directories dir ON dir.id=ZO.katalog
INNER JOIN SSCommon.STGukView guk on  ISNULL(guk.ContractorId,0)= ISNULL(zo.khid,0)
Where ZO.typ = 1
')
EXEC(N'CREATE OR ALTER VIEW [HM].[ForeignOrdersCardIndexViewWithoutGUK]
AS
SELECT ZO.id,
       [Common].[GetCardIndexMarkSymbol](CAST(ZO.znacznik AS INT)) AS Tag,
       ZO.kod AS Code,
       ZO.nazwa AS Name,
       ZO.seria AS Sequence,
       ZO.typ_dk AS DocumentType,
       ZO.data AS Date,
       ZO.netto + ZO.vat AS Brutto,
       ZO.walBrutto AS BruttoAmount,
       ZO.waluta AS Currency,
       ZO.opis AS Description,
       ZO.plattermin AS DueDate,
       CASE WHEN ZO.bufor = 1 THEN ''W buforze'' ELSE ''Wystawione'' END AS [Buffer],
	   IIF(ZO.anulowany <> 0, ''Anulowany'', (
	   CASE WHEN ZO.rr = 0 THEN
		   CASE ISNULL(TR.status, 32)
			   WHEN 4 THEN ''Do realizacji''
			   WHEN 8 THEN ''Realizacja częściowa''
			   WHEN 16 THEN ''Realizacja przygotowana''
			   WHEN 32 THEN ''Zrealizowano''
		   END
		ELSE
		   CASE ZO.ok
			   WHEN 0 THEN ''Do realizacji''
			   ELSE ''Zrealizowano''
		   END		
	   END)) AS SettlementState,
       ZO.aktywny AS IsActive,
       CASE WHEN ZO.statusRDF = 0 THEN ''Brak dokumentu''
            WHEN ZO.statusRDF = 1 THEN ''Dokument bez PDF''
            ELSE ''Dokument z PDF'' END AS DocumentStatusInRepository,
       CASE ZO.statusMIG WHEN 0 THEN ''Symfonia ERP''
				         WHEN 1 THEN ''Zmigrowany''
				         WHEN 2 THEN ''Błędny z Symfonii 2.0'' END AS StatusMig,
       ZO.netto AS Netto,
	   ZO.Vat AS ValueVat,
       ZO.walNetto AS NettoAmount,
	   dir.path AS CatalogPath,
       Kind.kod AS KindName,
       UsrRights.UserId AS UserId,
	   ISNULL((SELECT KH.kod FROM hm.kh WHERE KH.id = ZO.magazyn), '''') AS Department,
       ISNULL(SSCommon.STUsers.LoginName, '''') AS CreatorCode,
       ISNULL(SSCommon.STUsers.Id, 0) AS osobaId,
       ISNULL(SSCommon.vKh.kod, '''') AS ContractorCode,
       ISNULL(AD.nazwa, '''') AS ContractorName, 
       ISNULL(VKh1.kod, '''') AS ReceiverCode, 
       ISNULL(AD1.nazwa, '''') AS ReceiverName,
	   ISNULL(AD1.nazwaAdr,'''') AS ShortcutAddressOfDelivery,
	   ISNULL(AD1.miejscowosc,'''') AS PlaceOfDelivery
		,ISNULL(AD1.kodpocz,'''') AS PostCodeDelivery
		,ISNULL(AD1.ulica,'''') + '' ''+ AD1.dom + ''/'' + AD1.lokal AS AddressOfDelivery
		,ISNULL(krajedos.kod,'''') AS CountryDelivery
		,ISNULL(AD.nip,'''') AS KHNip
        ,CAST(
			(CASE 
				WHEN EXISTS(select 1
					FROM HM.ZZ tmp_zz
					inner join HM.DK tmp_dk on tmp_dk.id = tmp_zz.id2 and tmp_zz.baza2 = 16
					where tmp_zz.typ = 157 and tmp_zz.baza1 = 45 and tmp_zz.id1 = zo.id
				) THEN 1 ELSE HM.ZO.advanceInvoiceDeclared END
			) as bit
		) as UseAdvanceInvoice
        ,CASE
	    WHEN ZO.typeExternal <> 0  THEN ''B2B''
        WHEN ZO.typeExternal = 0  THEN ''HM''
	    END AS DocumentSource
       ,SOC.*
FROM HM.ZO
JOIN HM.AppUserDepartments UsrRights ON UsrRights.DepartmentId=ZO.magazyn
JOIN HM.DocumentTypePermission DocPermission ON DocPermission.UserId=UsrRights.UserId AND DocPermission.DocumentTypeCode = zo.typ_dk
LEFT JOIN HM.ZO AS Kind ON HM.ZO.rodzaj = Kind.id
LEFT JOIN SSCommon.HMF_SalesOrderClassification SOC ON SOC.ElementId = ZO.id
LEFT JOIN HM.TR ON (TR.owner_id = CASE WHEN ZO.iddokkoryg IS NULL THEN ZO.id ELSE ZO.iddokkoryg END) AND TR.owner_base = 45 AND TR.typ = 0
LEFT JOIN SSCommon.STUsers ON ZO.wystawil = SSCommon.STUsers.Id 
LEFT JOIN SSCommon.vKh ON SSCommon.vKh.id = ZO.khid
LEFT JOIN HM.AD ON AD.id = ZO.khadid
LEFT JOIN SSCommon.vKh AS VKh1 ON VKh1.id = ZO.odid
LEFT JOIN HM.AD AS AD1 ON AD1.id = ZO.odadid  
LEFT JOIN hm.vKraje krajedos ON AD1.idKraju=krajedos.id
LEFT JOIN hm.Directories dir ON dir.id=ZO.katalog
Where ZO.typ = 1 
')

GO

EXEC(N'CREATE OR ALTER VIEW [HM].[SaleDocumentsCardIndexView] 
AS 
SELECT
	DK.id,
	DK.guid AS DKGuid,
	dir.path AS CatalogPath,
	DK.aktywny AS IsActive,
	DK.typ AS Type,
	DK.kod AS Code,
	[Common].[GetCardIndexMarkSymbol](CAST(DK.znacznik AS INT)) AS Tag,
	CASE DK.statusFK WHEN 0 THEN '''' 
				WHEN 1 THEN ''w trakcie eksportu''
				WHEN 2 THEN ''eksport wysłany''
				WHEN 3 THEN ''eksport odebrany'' END AS SageConnectStatus,
	DK.nazwa AS NameDoc,
	DK.seria AS SeriesDoc,
	DK.typ_dk AS TypeDoc,
	DK.datasp AS DateSale, 
    Dk.dataZest1 AS DateIssue,
	DK.khid AS KhID,
	DK.magazyn AS Store,
	DK.opis AS Description,
	DK.wystawil AS IssuePersonID,
	DK.odebrane AS Customer,
	DK.khadid AS CustomerAdrressID,
	DK.exp_fk AS ExportFK,
	DK.statusFK AS StstusFK,
	CASE ISNULL(DK.statusKsef, 0) 
					  WHEN 0 THEN '''' 
					  WHEN 10 THEN ''Oczekuje na wysyłkę do Symfonia KSeF''
					  WHEN 20 THEN ''Błąd wysyłki do Symfonia KSeF''
					  WHEN 25 THEN ''W kolejce do wysyłki z Symfonia KSeF do KSeF''
					  WHEN 30 THEN ''Przesłano do Symfonia KSeF''
					  WHEN 31 THEN ''Usuwanie z Symfonia KSeF''
		              WHEN 40 THEN ''Błąd wysyłki z Symfonia KSeF do KSeF''
					  WHEN 50 THEN ''Wysłano do KSeF - trwa walidacja''
					  WHEN 60 THEN ''KSeF - faktura zatwierdzona''
					  WHEN 70 THEN ''KSeF - faktura odrzucona''
					  WHEN 80 THEN ''Wyeksportowano do pliku KSeF''
					  WHEN 90 THEN ''Zaimportowano z pliku KSeF'' END AS StatusKsef,
	DK.dataWystawieniaKsef as DataWystawieniaKsef,
	IsNull(DK.numberKsef, '''') as NumberKsef,
	DK.flag AS Flag,
	CASE WHEN (DK.flag&8192 & 8192) = 0 THEN '''' ELSE ''K'' END AS Correction,
	CASE WHEN DK.ok = 0 OR DK.bufor = 1  THEN ''nierozliczony''
			ELSE ''rozliczony'' END AS StatusPayoff,	
	DK.subtyp AS SubType,
	DK.schemat AS Scheme,
	CASE DK.statusRDF WHEN 0 THEN ''brak dokumentu'' 
					  WHEN 1 THEN ''dokument bez PDF''
					  WHEN 2 THEN ''dokument z PDF'' END AS DocRepository,
	DK.data AS DateDoc,
	DK.plattermin AS DatePayment,
	CAST(DK.netto AS DECIMAL(18,2)) AS Netto,
	CAST(DK.vat AS DECIMAL(18,2)) AS VAT,
	CAST(DK.VAT AS DECIMAL(18,2)) AS ValueVat,
	CAST(DK.walNetto AS DECIMAL(18,2)) AS ValueNetto,
	CAST((DK.netto+DK.vat) AS DECIMAL(18,2)) AS Value,
	CAST(DK.walBrutto AS DECIMAL(18,2)) AS ValueGross,
	DK.jpk AS JPK,
	DK.jpk_V7 AS JPK_V7,
	DK.waluta AS Currency,
	CASE WHEN DK.bufor = 0 THEN   
			''wystawione''             
         ELSE
            ''w buforze''
	END AS Buffer,
	CAST(CASE WHEN DK.anulowany = 0 THEN 0 ELSE 1 END AS BIT) AS Canceled,
	CAST(CASE WHEN DK.ok = 0 THEN 0 ELSE 1 END AS BIT) AS SettledInCash,
    CAST(CASE WHEN ISNULL(trans.status,32) < 32 AND DK.rr < 2 THEN ''Nie'' ELSE ''Tak'' END AS VARCHAR(3)) AS SettledInWarehouse,
	DK.odid AS CustomerID,
	DK.rozlmg AS DeliveryWarehouse,
	DK.eFaktura AS eInvoice,
	InvoiceKind = CASE
		   WHEN  DK.statusRDF = 0 THEN ''brak faktury''
		   WHEN  DK.eFaktura = 1 THEN ''e-Faktura''
		   WHEN  DK.eFaktura = 0 THEN ''faktura papierowa''
		   ELSE '''' END,
    DK.rodzaj AS KindId,
    Kinds.kod AS KindName,	
	CASE DK.exp_fk%2 WHEN 1 THEN ''wyeksportowany'' ELSE ''niewyeksportowany'' END AS Export,
	CASE ((DK.exp_fk/2)/2%2) WHEN 1 THEN ''F'' ELSE '''' END AS Fiscalization,
	CASE DK.statusMIG WHEN 0 THEN ''Symfonia ERP'' 
				      WHEN 1 THEN ''Zmigrowany''
				      WHEN 2 THEN ''Błędny z Symfonii 2.0'' END AS StatusMig,
	CASE DK.statusWindykacji	
		WHEN ''SEND'' THEN ''Zlecono do windykacji'' 
		WHEN ''REJECTED'' THEN ''Sprawa odrzucona'' 
		WHEN ''NO_SUCCESS'' THEN ''Nie odzyskano pieniędzy'' 
		WHEN ''PAID'' THEN ''Odzyskano pieniądze'' 
		WHEN ''IN_PROGRESS'' THEN ''Windykacja w toku''
		ELSE ''''
	END AS StatusWindykacji
,ISNULL(trans.status,0) AS trStatus
,ISNULL(trans.flag,0) AS trFlag
,ISNULL((SELECT KH.kod FROM hm.kh WHERE KH.id = DK.magazyn),'''') AS DepartmentCode
,ISNULL(SU.LoginName,'''') AS PersonKod
,ISNULL(UsrRights.UserId,'''') AS UserId
,ISNULL(Skh.kod,'''') AS khKod 
,ISNULL(ad.nip,'''') AS KHNip
,ISNULL(skod.kod,'''') AS khOd 
,ISNULL(AD.nazwa,'''') AS khName 
,dk.splitPayment
,DK.datarej AS InRegisterDate
,periodicSaleReports.code AS PeriodicSaleReportCode
,periodicSaleReports.Id AS PeriodicSaleReportId
,ISNULL(ADDost.nazwaAdr,'''') AS ShortcutAddressOfDelivery
,ISNULL(ADDost.miejscowosc,'''') AS PlaceOfDelivery
,ISNULL(ADDost.kodpocz,'''') AS PostCodeDelivery
,ISNULL(ADDost.ulica,'''') + '' ''+ ADDost.dom + ''/'' + ADDost.lokal AS AddressOfDelivery
,ISNULL(krajedos.kod,'''') AS CountryDelivery
,CASE
	WHEN DK.typeExternal <> 0  THEN ''B2B''
    WHEN DK.typeExternal = 0  THEN ''HM''
	END AS DocumentSource
,SC.*
FROM HM.dk AS dk
JOIN HM.AppUserDepartments UsrRights ON UsrRights.DepartmentId = dk.magazyn
JOIN HM.DocumentTypePermission DocPermission ON DocPermission.UserId=UsrRights.UserId AND DocPermission.DocumentTypeCode = dk.typ_dk
LEFT JOIN SScommon.HMF_SalesInvoiceClassification SC ON SC.ElementId = DK.id
LEFT JOIN SSCommon.STUsers SU ON DK.wystawil = SU.id	
LEFT JOIN SSCommon.vKh SKh ON dk.khid = Skh.id
LEFT JOIN SSCommon.vKh Skod ON dk.odid = Skod.id
LEFT JOIN HM.SalesKinds AS Kinds ON DK.rodzaj = Kinds.KindId
LEFT JOIN HM.AD ON AD.id = DK.khadid 	
LEFT JOIN hm.ad ADDost ON ADDost.id = dk.odadid
LEFT JOIN hm.vKraje krajedos ON ADDost.idKraju=krajedos.id
LEFT JOIN HM.TR trans ON (trans.owner_id = CASE WHEN DK.iddokkoryg IS NULL THEN DK.id ELSE DK.iddokkoryg END) AND TRans.owner_base=16 AND TRans.typ=1
LEFT JOIN HM.Directories dir ON dir.id=dk.katalog
LEFT JOIN hm.PeriodicSaleReports periodicSaleReports ON periodicSaleReports.Id = dk.IdPeriodicSaleReport
INNER JOIN SSCommon.STGukView guk on  ISNULL(guk.ContractorId,0)= ISNULL(dk.khid,0)
WHERE DK.typ=0
')

EXEC(N'CREATE OR ALTER VIEW [HM].[SaleDocumentsCardIndexWithoutTrView] 
AS 
SELECT
	DK.id,
	DK.guid AS DKGuid,
	dir.path AS CatalogPath,
	DK.aktywny AS IsActive,
	DK.typ AS Type,
	DK.kod AS Code,
	[Common].[GetCardIndexMarkSymbol](CAST(DK.znacznik AS INT)) AS Tag,
	CASE DK.statusFK WHEN 0 THEN '''' 
				WHEN 1 THEN ''w trakcie eksportu''
				WHEN 2 THEN ''eksport wysłany''
				WHEN 3 THEN ''eksport odebrany'' END AS SageConnectStatus,
	DK.nazwa AS NameDoc,
	DK.seria AS SeriesDoc,
	DK.typ_dk AS TypeDoc,
	DK.datasp AS DateSale, 
    Dk.dataZest1 AS DateIssue,
	DK.khid AS KhID,
	DK.magazyn AS Store,
	DK.opis AS Description,
	DK.wystawil AS IssuePersonID,
	DK.odebrane AS Customer,
	DK.khadid AS CustomerAdrressID,
	DK.exp_fk AS ExportFK,
	DK.statusFK AS StstusFK,
	CASE ISNULL(DK.statusKsef, 0)  
				WHEN 0 THEN '''' 
				WHEN 10 THEN ''Oczekuje na wysyłkę do Symfonia KSeF''
				WHEN 20 THEN ''Błąd wysyłki do Symfonia KSeF''
				WHEN 25 THEN ''W kolejce do wysyłki z Symfonia KSeF do KSeF''
				WHEN 30 THEN ''Przesłano do Symfonia KSeF''
				WHEN 31 THEN ''Usuwanie z Symfonia KSeF''
		        WHEN 40 THEN ''Błąd wysyłki z Symfonia KSeF do KSeF''
				WHEN 50 THEN ''Wysłano do KSeF - trwa walidacja''
				WHEN 60 THEN ''KSeF - faktura zatwierdzona''
				WHEN 70 THEN ''KSeF - faktura odrzucona''
				WHEN 80 THEN ''Wyeksportowano do pliku KSeF''
				WHEN 90 THEN ''Zaimportowano z pliku KSeF'' END AS StatusKsef,
	DK.dataWystawieniaKsef as DataWystawieniaKsef,
	IsNull(DK.numberKsef, '''') as NumberKsef,
	DK.flag AS Flag,
	CASE WHEN (DK.flag&8192 & 8192) = 0 THEN '''' ELSE ''K'' END AS Correction,
	CASE WHEN DK.ok = 0 OR DK.bufor = 1  THEN ''nierozliczony''
			   ELSE ''rozliczony'' END AS StatusPayoff,	
	DK.subtyp AS SubType,
	DK.schemat AS Scheme,
	CASE DK.statusRDF WHEN 0 THEN ''brak dokumentu''
					  WHEN 1 THEN ''dokument bez PDF''
					  WHEN 2 THEN ''dokument z PDF'' END AS DocRepository,
	DK.data AS DateDoc,
	DK.plattermin AS DatePayment,
	CAST(DK.netto AS DECIMAL(18,2)) AS Netto,
	CAST(DK.vat AS DECIMAL(18,2)) AS VAT,
	CAST(DK.VAT AS DECIMAL(18,2)) AS ValueVat,
	CAST(DK.walNetto AS DECIMAL(18,2)) AS ValueNetto,
	CAST((DK.netto+DK.vat) AS DECIMAL(18,2)) AS Value,
	CAST(DK.walBrutto AS DECIMAL(18,2)) AS ValueGross,
	DK.jpk AS JPK,
	DK.JPK_V7 AS JPK_V7,
	DK.waluta AS Currency,
	CASE WHEN DK.bufor = 0 THEN   
			''wystawione''             
         ELSE
            ''w buforze''
	END AS Buffer,
	CAST(CASE WHEN DK.anulowany = 0 THEN 0 ELSE 1 END AS BIT) AS Canceled,
	CAST(CASE WHEN DK.ok = 0 THEN 0 ELSE 1 END AS BIT) AS SettledInCash,
	DK.odid AS CustomerID,
	DK.rozlmg AS DeliveryWarehouse,
	DK.eFaktura AS eInvoice,
	InvoiceKind = CASE
		   WHEN  DK.statusRDF = 0 THEN ''brak faktury''
		   WHEN  DK.eFaktura = 1 THEN ''e-Faktura''
		   WHEN  DK.eFaktura = 0 THEN ''faktura papierowa''
		   ELSE '''' END,
    DK.rodzaj AS KindId,
    Kinds.kod AS KindName,	
	CASE DK.exp_fk%2 WHEN 1 THEN ''wyeksportowany'' ELSE ''niewyeksportowany'' END AS Export,
	CASE ((DK.exp_fk/2)/2%2) WHEN 1 THEN ''F'' ELSE '''' END AS Fiscalization,
	CASE DK.statusMIG WHEN 0 THEN ''Symfonia ERP'' 
				      WHEN 1 THEN ''Zmigrowany''
				      WHEN 2 THEN ''Błędny z Symfonii 2.0'' END AS StatusMig,
	CASE DK.statusWindykacji	
		WHEN ''SEND'' THEN ''Zlecono do windykacji'' 
		WHEN ''REJECTED'' THEN ''Sprawa odrzucona'' 
		WHEN ''NO_SUCCESS'' THEN ''Nie odzyskano pieniędzy'' 
		WHEN ''PAID'' THEN ''Odzyskano pieniądze'' 
		WHEN ''IN_PROGRESS'' THEN ''Windykacja w toku''
		ELSE ''''
	END AS StatusWindykacji
,ISNULL((SELECT KH.kod FROM hm.kh WHERE KH.id = DK.magazyn),'''') AS DepartmentCode
,ISNULL(SU.LoginName,'''') AS PersonKod
,ISNULL(UsrRights.UserId,'''') AS UserId
,ISNULL(Skh.kod,'''') AS khKod 
,ISNULL(ad.nip,'''') AS KHNip
,ISNULL(skod.kod,'''') AS khOd 
,ISNULL(AD.nazwa,'''') AS khName
,dk.splitPayment
,DK.datarej AS InRegisterDate
,periodicSaleReports.code AS PeriodicSaleReportCode
,ISNULL(ADDost.nazwaAdr,'''') AS ShortcutAddressOfDelivery
,ISNULL(ADDost.miejscowosc,'''') AS PlaceOfDelivery
,ISNULL(ADDost.kodpocz,'''') AS PostCodeDelivery
,ISNULL(ADDost.ulica,'''') + '' ''+ ADDost.dom + ''/'' + ADDost.lokal AS AddressOfDelivery
,ISNULL(krajedos.kod,'''') AS CountryDelivery
,CASE
	WHEN DK.typeExternal <> 0  THEN ''B2B''
    WHEN DK.typeExternal = 0  THEN ''HM''
	END AS DocumentSource
,SC.*
FROM HM.dk AS dk
JOIN HM.AppUserDepartments UsrRights ON UsrRights.DepartmentId = dk.magazyn
JOIN HM.DocumentTypePermission DocPermission ON DocPermission.UserId=UsrRights.UserId AND DocPermission.DocumentTypeCode = dk.typ_dk
LEFT JOIN SScommon.HMF_SalesInvoiceClassification SC ON SC.ElementId = DK.id
LEFT JOIN SSCommon.STUsers SU ON DK.wystawil = SU.id	
LEFT JOIN SSCommon.vKh SKh ON dk.khid = Skh.id
LEFT JOIN SSCommon.vKh Skod ON dk.odid = Skod.id
LEFT JOIN HM.SalesKinds AS Kinds ON DK.rodzaj = Kinds.KindId
LEFT JOIN HM.AD ON AD.id = DK.khadid 	
LEFT JOIN hm.ad ADDost ON ADDost.id = dk.odadid
LEFT JOIN hm.vKraje krajedos ON ADDost.idKraju=krajedos.id
LEFT JOIN HM.Directories dir ON dir.id=dk.katalog
LEFT JOIN hm.PeriodicSaleReports periodicSaleReports ON periodicSaleReports.Id = dk.IdPeriodicSaleReport
INNER JOIN SSCommon.STGukView guk on  ISNULL(guk.ContractorId,0)= ISNULL(dk.khid,0)
WHERE DK.typ=0
')

EXEC(N'CREATE OR ALTER VIEW [HM].[SaleDocumentsCardIndexViewWithoutGUK] 
AS 
SELECT
	DK.id,
	DK.guid AS DKGuid,
	dir.path AS CatalogPath,
	DK.aktywny AS IsActive,
	DK.typ AS Type,
	DK.kod AS Code,
	[Common].[GetCardIndexMarkSymbol](CAST(DK.znacznik AS INT)) AS Tag,
	CASE DK.statusFK WHEN 0 THEN '''' 
				WHEN 1 THEN ''w trakcie eksportu''
				WHEN 2 THEN ''eksport wysłany''
				WHEN 3 THEN ''eksport odebrany'' END AS SageConnectStatus,
	DK.nazwa AS NameDoc,
	DK.seria AS SeriesDoc,
	DK.typ_dk AS TypeDoc,
	DK.datasp AS DateSale, 
    Dk.dataZest1 AS DateIssue,
	DK.khid AS KhID,
	DK.magazyn AS Store,
	DK.opis AS Description,
	DK.wystawil AS IssuePersonID,
	DK.odebrane AS Customer,
	DK.khadid AS CustomerAdrressID,
	DK.exp_fk AS ExportFK,
	DK.statusFK AS StstusFK,
	CASE ISNULL(DK.statusKsef, 0)  
				      WHEN 0 THEN '''' 
					  WHEN 10 THEN ''Oczekuje na wysyłkę do Symfonia KSeF''
					  WHEN 20 THEN ''Błąd wysyłki do Symfonia KSeF''
					  WHEN 25 THEN ''W kolejce do wysyłki z Symfonia KSeF do KSeF''
					  WHEN 30 THEN ''Przesłano do Symfonia KSeF''
					  WHEN 31 THEN ''Usuwanie z Symfonia KSeF''
		              WHEN 40 THEN ''Błąd wysyłki z Symfonia KSeF do KSeF''
					  WHEN 50 THEN ''Wysłano do KSeF - trwa walidacja''
					  WHEN 60 THEN ''KSeF - faktura zatwierdzona''
					  WHEN 70 THEN ''KSeF - faktura odrzucona''
					  WHEN 80 THEN ''Wyeksportowano do pliku KSeF''
					  WHEN 90 THEN ''Zaimportowano z pliku KSeF'' END AS StatusKsef,
	DK.dataWystawieniaKsef as DataWystawieniaKsef,
	IsNull(DK.numberKsef, '''') as NumberKsef,
	DK.flag AS Flag,
	CASE WHEN (DK.flag&8192 & 8192) = 0 THEN '''' ELSE ''K'' END AS Correction,
	CASE WHEN DK.ok = 0 OR DK.bufor = 1  THEN ''nierozliczony''
			ELSE ''rozliczony'' END AS StatusPayoff,	
	DK.subtyp AS SubType,
	DK.schemat AS Scheme,
	CASE DK.statusRDF WHEN 0 THEN ''brak dokumentu'' 
					  WHEN 1 THEN ''dokument bez PDF''
					  WHEN 2 THEN ''dokument z PDF'' END AS DocRepository,
	DK.data AS DateDoc,
	DK.plattermin AS DatePayment,
	CAST(DK.netto AS DECIMAL(18,2)) AS Netto,
	CAST(DK.vat AS DECIMAL(18,2)) AS VAT,
	CAST(DK.VAT AS DECIMAL(18,2)) AS ValueVat,
	CAST(DK.walNetto AS DECIMAL(18,2)) AS ValueNetto,
	CAST((DK.netto+DK.vat) AS DECIMAL(18,2)) AS Value,
	CAST(DK.walBrutto AS DECIMAL(18,2)) AS ValueGross,
	DK.jpk AS JPK,
	DK.jpk_V7 AS JPK_V7,
	DK.waluta AS Currency,
	CASE WHEN DK.bufor = 0 THEN   
			''wystawione''             
         ELSE
            ''w buforze''
	END AS Buffer,
	CAST(CASE WHEN DK.anulowany = 0 THEN 0 ELSE 1 END AS BIT) AS Canceled,
	CAST(CASE WHEN DK.ok = 0 THEN 0 ELSE 1 END AS BIT) AS SettledInCash,
    CAST(CASE WHEN ISNULL(trans.status,32) < 32 AND DK.rr < 2 THEN ''Nie'' ELSE ''Tak'' END AS VARCHAR(3)) AS SettledInWarehouse,
	DK.odid AS CustomerID,
	DK.rozlmg AS DeliveryWarehouse,
	DK.eFaktura AS eInvoice,
	InvoiceKind = CASE
		   WHEN  DK.statusRDF = 0 THEN ''brak faktury''
		   WHEN  DK.eFaktura = 1 THEN ''e-Faktura''
		   WHEN  DK.eFaktura = 0 THEN ''faktura papierowa''
		   ELSE '''' END,
    DK.rodzaj AS KindId,
    Kinds.kod AS KindName,	
	CASE DK.exp_fk%2 WHEN 1 THEN ''wyeksportowany'' ELSE ''niewyeksportowany'' END AS Export,
	CASE ((DK.exp_fk/2)/2%2) WHEN 1 THEN ''F'' ELSE '''' END AS Fiscalization,
	CASE DK.statusMIG WHEN 0 THEN ''Symfonia ERP'' 
				      WHEN 1 THEN ''Zmigrowany''
				      WHEN 2 THEN ''Błędny z Symfonii 2.0'' END AS StatusMig,
	CASE DK.statusWindykacji	
		WHEN ''SEND'' THEN ''Zlecono do windykacji'' 
		WHEN ''REJECTED'' THEN ''Sprawa odrzucona'' 
		WHEN ''NO_SUCCESS'' THEN ''Nie odzyskano pieniędzy'' 
		WHEN ''PAID'' THEN ''Odzyskano pieniądze'' 
		WHEN ''IN_PROGRESS'' THEN ''Windykacja w toku''
		ELSE ''''
	END AS StatusWindykacji
,ISNULL(trans.status,0) AS trStatus
,ISNULL(trans.flag,0) AS trFlag
,ISNULL((SELECT KH.kod FROM hm.kh WHERE KH.id = DK.magazyn),'''') AS DepartmentCode
,ISNULL(SU.LoginName,'''') AS PersonKod
,ISNULL(UsrRights.UserId,'''') AS UserId
,ISNULL(Skh.kod,'''') AS khKod 
,ISNULL(ad.nip,'''') AS KHNip
,ISNULL(skod.kod,'''') AS khOd 
,ISNULL(AD.nazwa,'''') AS khName 
,dk.splitPayment
,DK.datarej AS InRegisterDate
,periodicSaleReports.code AS PeriodicSaleReportCode
,periodicSaleReports.Id AS PeriodicSaleReportId
,ISNULL(ADDost.nazwaAdr,'''') AS ShortcutAddressOfDelivery
,ISNULL(ADDost.miejscowosc,'''') AS PlaceOfDelivery
,ISNULL(ADDost.kodpocz,'''') AS PostCodeDelivery
,ISNULL(ADDost.ulica,'''') + '' ''+ ADDost.dom + ''/'' + ADDost.lokal AS AddressOfDelivery
,ISNULL(krajedos.kod,'''') AS CountryDelivery
,CASE
	WHEN DK.typeExternal <> 0  THEN ''B2B''
    WHEN DK.typeExternal = 0  THEN ''HM''
	END AS DocumentSource
,SC.*
FROM HM.dk AS dk
JOIN HM.AppUserDepartments UsrRights ON UsrRights.DepartmentId = dk.magazyn
JOIN HM.DocumentTypePermission DocPermission ON DocPermission.UserId=UsrRights.UserId AND DocPermission.DocumentTypeCode = dk.typ_dk
LEFT JOIN SScommon.HMF_SalesInvoiceClassification SC ON SC.ElementId = DK.id
LEFT JOIN SSCommon.STUsers SU ON DK.wystawil = SU.id	
LEFT JOIN SSCommon.vKh SKh ON dk.khid = Skh.id
LEFT JOIN SSCommon.vKh Skod ON dk.odid = Skod.id
LEFT JOIN HM.SalesKinds AS Kinds ON DK.rodzaj = Kinds.KindId
LEFT JOIN HM.AD ON AD.id = DK.khadid 	
LEFT JOIN hm.ad ADDost ON ADDost.id = dk.odadid
LEFT JOIN hm.vKraje krajedos ON ADDost.idKraju=krajedos.id
LEFT JOIN HM.TR trans ON (trans.owner_id = CASE WHEN DK.iddokkoryg IS NULL THEN DK.id ELSE DK.iddokkoryg END) AND TRans.owner_base=16 AND TRans.typ=1
LEFT JOIN HM.Directories dir ON dir.id=dk.katalog
LEFT JOIN hm.PeriodicSaleReports periodicSaleReports ON periodicSaleReports.Id = dk.IdPeriodicSaleReport
WHERE DK.typ=0
')

GO
-- Inicjalizacja danych nowego zamowienia obcego w postaci XML
CREATE OR ALTER PROCEDURE HM.NewForeignOrderDocumentAsXML (@catalogId INT, @kindId INT, @documentTypeId INT, @departmentId INT, @userId INT, @docData XML)
AS
BEGIN
	SET NOCOUNT ON
	SET XACT_ABORT ON

	BEGIN TRY

		IF 0 = ISNULL(@catalogId, 0)
			SET @catalogId = 2700

		IF 0 = ISNULL(@kindId, 0)
			SET @kindId = 3700

		IF 0 = ISNULL(@documentTypeId, 0)
			SET @documentTypeId = 48

		DECLARE @changes TABLE
		(
			DocumentTypeShortcut         NVARCHAR(40) COLLATE database_default,
			Name                         NVARCHAR(100) COLLATE database_default,
			DocumentNumerationShortcut   NVARCHAR(40) COLLATE database_default,
			DocumentNumber               NVARCHAR(40) COLLATE database_default,
			SeriesNo                     INT,
			CatalogId                    INT,
			KindId                       INT,
			DepartmentId                 INT,
			IssueDate                    DATE,
			OperationDate                DATE,
			CalculateVatMethod           SMALLINT,
			Marker                       SMALLINT,
			PublishInventoryDocumentMode SMALLINT,
			ReservationType              SMALLINT,
			PriceTypeId                  INT,
			PriceKind                    SMALLINT,
			DepartmentInSeries           INT,
			Period                       SMALLINT,
			RegisterMethod               SMALLINT,
			UseAdvanceInvoice            BIT,
            TypeExternal                 INT,
            IdExternal                   INT,
            Id2External                  VARCHAR(32)
		)

		DECLARE @currentDate DATE
		SET @currentDate = CONVERT(DATE, GETDATE())

		-- identycznie jak w klasycznym oknie
		DECLARE @parameterPriceKind SMALLINT
		SELECT @parameterPriceKind = cena FROM HM.ZZ WHERE id2 = 1100
		IF @parameterPriceKind IS NULL
			SET @parameterPriceKind = 2
		ELSE
			IF @parameterPriceKind = 255
				SET @parameterPriceKind = 1
			ELSE
				SET @parameterPriceKind = @parameterPriceKind % 2 + 1

		INSERT INTO @changes
		SELECT docType.kod                                                       AS DocumentTypeShortcut,
			   docType.nazwa                                                     AS Name,
			   IIF(kind.DocumentTypeShortcut = docType.kod AND LEN(kind.NumerationShortcut) > 0,
		           kind.NumerationShortcut,                                                         -- typ dokumentu z rodzaju to seria rowniez z rodzaju
		           IIF((SELECT COUNT(TD.id) FROM HM.TD INNER JOIN HM.SD ON SD.idTypDok = TD.id WHERE SD.aktywny = 1 AND SD.idTypDok = @documentTypeId) = 1,
		               (SELECT TOP 1 kod FROM HM.SD WHERE @documentTypeId = idTypDok AND aktywny = 1 ORDER BY SD.id),
		               ''))                                                      AS DocumentNumerationShortcut,
		       ''                                                                AS DocumentNumber,
		       0                                                                 AS SeriesNo,
		       @catalogId                                                        AS CatalogId,
		       @kindId                                                           AS KindId,
		       IIF(userAcc.UserId IS NOT NULL, kind.DepartmentId, @departmentId) AS DepartmentId,   -- dzial handlowy z rodzaju jesli uzytkownik ma do niego prawo
		       @currentDate                                                      AS IssueDate,
		       @currentDate                                                      AS OperationDate,
		       ISNULL((SELECT CASE WHEN NumericValue = 1 THEN 0 ELSE 1 END FROM HM.WorkParameters WHERE Id = 1050), 0)
		                                                                         AS CalculateVatMethod,
		       kind.Tag                                                          AS Marker,
		       kind.PublishInventoryDocumentMode                                 AS PublishInventoryDocumentMode,
		       kind.ReservationType                                              AS ReservationType,
		       kind.PriceTypeId                                                  AS PriceTypeId,
		       IIF(kind.PriceKind <> 0, kind.PriceKind, @parameterPriceKind)   AS PriceKind,
		       0                                                                 AS DepartmentInSeries,
			   0                                                                 AS Period,
			   HM.GetRegisterMethod(docType.charakter, GETDATE())				 AS RegisterMethod,
			   IIF(docType.flag & 0x040 = 0x40, 1, 0)                            AS UseAdvanceInvoice,
               0                                                                 AS TypeExternal,
               null                                                              AS IdExternal,
               null                                                              AS Id2External 
		  FROM HM.ForeginOrderDocumentsKindsView AS kind
	           INNER JOIN HM.TD AS docType ON docType.id = @documentTypeId
			   LEFT OUTER JOIN HM.SK AS accPaterns ON accPaterns.id = docType.idSchematKs AND accPaterns.super IS NOT NULL
			   LEFT OUTER JOIN HM.UserDepartmentView AS userAcc ON userAcc.DepartmentId = kind.DepartmentId AND userAcc.UserId = @userId
		 WHERE kind.Id = @kindId

		-- Nowy numer dokumentu
		DECLARE
			@id_dok                 INT,
			@DocumentNumerationCode VARCHAR(10),
			@genContractorId        INT,
			@currency               VARCHAR(3),
			@buffer                 BIT,
			@date                   DATE,
			@tableId                SMALLINT,
			@code                   VARCHAR(40),
			@numberInSeries         INT,
			@period                 INT,
			@departmentInSeries     INT

		SELECT @id_dok = 0,
		       @DocumentNumerationCode = DocumentNumerationShortcut,
		       @genContractorId = 0,
		       @DepartmentId = DepartmentId,
		       @Currency = '',
		       @buffer = 1,
			   @date = IssueDate,
			   @TableId = 45,
			   @numberInSeries = 0
		  FROM @changes

		-- numer dokumentu generowany tylko gdy aktywna jedna seria
		IF (SELECT COUNT(id) FROM HM.SD WHERE idTypDok = @documentTypeId AND aktywny = 1) = 1
		BEGIN
			EXEC HM.GenerateDocumentNumber
				@id_dok,
				@DocumentTypeId,
				@DocumentNumerationCode,
				@userId,
				@genContractorId,
				@departmentId,
				@currency,
				@buffer,
				@date,
				@tableId,
				@code OUT,
				@numberInSeries OUT,
				@period OUT,
				@departmentInSeries OUT

            IF ISNULL(@code, '@0') != '@0'
            BEGIN
                DECLARE @shortcutMessage NVARCHAR(200) = CONCAT('Zamówienie obce o numerze "', @code, '" istnieje w bazie danych.')
			    IF EXISTS(SELECT * FROM [HM].[ZO] zo WHERE zo.[typ] = 1 and zo.[kod] = @code and zo.bufor = @buffer)
		            THROW 50000, @shortcutMessage, 1;
            END

			UPDATE @changes SET
				DocumentNumber = ISNULL(IIF(@code = '@0', NULL, @code), ''),
				SeriesNo = @numberInSeries,
				Period = @period,
				DepartmentInSeries = @departmentInSeries
		END

		DECLARE @x TABLE (value xml)
		INSERT INTO @x VALUES (@docData)

		IF 1 = (SELECT COUNT(DocumentTypeShortcut) FROM @changes)
		BEGIN
			UPDATE @x SET value.modify('delete /ForeignOrderDocument/DocumentTypeShortcut')
			UPDATE @x SET value.modify('insert <DocumentTypeShortcut>{sql:column("DocumentTypeShortcut")}</DocumentTypeShortcut> as first into (/ForeignOrderDocument)[1]') FROM @changes

			UPDATE @x SET value.modify('delete /ForeignOrderDocument/Name')
			UPDATE @x SET value.modify('insert <Name>{sql:column("Name")}</Name> as first into (/ForeignOrderDocument)[1]') FROM @changes

			UPDATE @x SET value.modify('delete /ForeignOrderDocument/DocumentNumerationShortcut')
			UPDATE @x SET value.modify('insert <DocumentNumerationShortcut>{sql:column("DocumentNumerationShortcut")}</DocumentNumerationShortcut> as first into (/ForeignOrderDocument)[1]') FROM @changes	

			UPDATE @x SET value.modify('delete /ForeignOrderDocument/DocumentNumber')
			UPDATE @x SET value.modify('insert <DocumentNumber>{sql:column("DocumentNumber")}</DocumentNumber> as first into (/ForeignOrderDocument)[1]') FROM @changes

			UPDATE @x SET value.modify('delete /ForeignOrderDocument/SeriesNo')
			UPDATE @x SET value.modify('insert <SeriesNo>{sql:column("SeriesNo")}</SeriesNo> as first into (/ForeignOrderDocument)[1]') FROM @changes

			UPDATE @x SET value.modify('delete /ForeignOrderDocument/CatalogId')
			UPDATE @x SET value.modify('insert <CatalogId>{sql:column("CatalogId")}</CatalogId> as first into (/ForeignOrderDocument)[1]') FROM @changes

			UPDATE @x SET value.modify('delete /ForeignOrderDocument/KindId')
			UPDATE @x SET value.modify('insert <KindId>{sql:column("KindId")}</KindId> as first into (/ForeignOrderDocument)[1]') FROM @changes

			UPDATE @x SET value.modify('delete /ForeignOrderDocument/DepartmentId')
			UPDATE @x SET value.modify('insert <DepartmentId>{sql:column("DepartmentId")}</DepartmentId> as first into (/ForeignOrderDocument)[1]') FROM @changes

			UPDATE @x SET value.modify('delete /ForeignOrderDocument/IssueDate')
			UPDATE @x SET value.modify('insert <IssueDate>{sql:column("IssueDate")}</IssueDate> as first into (/ForeignOrderDocument)[1]') FROM @changes

			UPDATE @x SET value.modify('delete /ForeignOrderDocument/OperationDate')
			UPDATE @x SET value.modify('insert <OperationDate>{sql:column("OperationDate")}</OperationDate> as first into (/ForeignOrderDocument)[1]') FROM @changes

			UPDATE @x SET value.modify('delete /ForeignOrderDocument/CalculateVatMethod')
			UPDATE @x SET value.modify('insert <CalculateVatMethod>{sql:column("CalculateVatMethod")}</CalculateVatMethod> as first into (/ForeignOrderDocument)[1]') FROM @changes

			UPDATE @x SET value.modify('delete /ForeignOrderDocument/Marker')
			UPDATE @x SET value.modify('insert <Marker>{sql:column("Marker")}</Marker> as first into (/ForeignOrderDocument)[1]') FROM @changes

			UPDATE @x SET value.modify('delete /ForeignOrderDocument/PublishInventoryDocumentMode')
			UPDATE @x SET value.modify('insert <PublishInventoryDocumentMode>{sql:column("PublishInventoryDocumentMode")}</PublishInventoryDocumentMode> as first into (/ForeignOrderDocument)[1]') FROM @changes

			UPDATE @x SET value.modify('delete /ForeignOrderDocument/ReservationType')
			UPDATE @x SET value.modify('insert <ReservationType>{sql:column("ReservationType")}</ReservationType> as first into (/ForeignOrderDocument)[1]') FROM @changes

			UPDATE @x SET value.modify('delete /ForeignOrderDocument/PriceTypeId')
			UPDATE @x SET value.modify('insert <PriceTypeId>{sql:column("PriceTypeId")}</PriceTypeId> as first into (/ForeignOrderDocument)[1]') FROM @changes

			UPDATE @x SET value.modify('delete /ForeignOrderDocument/PriceKind')
			UPDATE @x SET value.modify('insert <PriceKind>{sql:column("PriceKind")}</PriceKind> as first into (/ForeignOrderDocument)[1]') FROM @changes

			UPDATE @x SET value.modify('delete /ForeignOrderDocument/DepartmentInSeries')
			UPDATE @x SET value.modify('insert <DepartmentInSeries>{sql:column("DepartmentInSeries")}</DepartmentInSeries> as first into (/ForeignOrderDocument)[1]') FROM @changes

			UPDATE @x SET value.modify('delete /ForeignOrderDocument/Period')
			UPDATE @x SET value.modify('insert <Period>{sql:column("Period")}</Period> as first into (/ForeignOrderDocument)[1]') FROM @changes
			UPDATE @x SET value.modify('delete /ForeignOrderDocument/RegisterMethod')
			UPDATE @x SET value.modify('insert <RegisterMethod>{sql:column("RegisterMethod")}</RegisterMethod> as first into (/ForeignOrderDocument)[1]') FROM @changes
			UPDATE @x SET value.modify('delete /ForeignOrderDocument/UseAdvanceInvoice')
			UPDATE @x SET value.modify('insert <UseAdvanceInvoice>{sql:column("UseAdvanceInvoice")}</UseAdvanceInvoice> as first into (/ForeignOrderDocument)[1]') FROM @changes

            UPDATE @x SET value.modify('delete /ForeignOrderDocument/TypeExternal')
			UPDATE @x SET value.modify('insert <TypeExternal>{sql:column("TypeExternal")}</TypeExternal> as first into (/ForeignOrderDocument)[1]') FROM @changes

            UPDATE @x SET value.modify('delete /ForeignOrderDocument/IdExternal')
			UPDATE @x SET value.modify('insert <IdExternal>{sql:column("IdExternal")}</IdExternal> as first into (/ForeignOrderDocument)[1]') FROM @changes WHERE IdExternal IS NOT NULL;

            UPDATE @x SET value.modify('delete /ForeignOrderDocument/Id2External')
			UPDATE @x SET value.modify('insert <Id2External>{sql:column("Id2External")}</Id2External> as first into (/ForeignOrderDocument)[1]') FROM @changes WHERE Id2External IS NOT NULL;
		END

		SELECT value.query('/ForeignOrderDocument')
		FROM @x FOR XML PATH('')

	END TRY
	BEGIN CATCH
		THROW
	END CATCH
END

GO

-- Procedura umożliwiająca pobranie danych dokumentu w postaci XML
CREATE OR ALTER PROCEDURE [HM].[NewSaleDocumentAsXML] (@catalogId INT, @kindId INT, @documentTypeId INT, @departmentId INT, @userId INT, @docData XML)
AS
BEGIN
	SET NOCOUNT ON; -- Stops the message that shows the count of the number of rows affected by a Transact-SQL statement or stored procedure from being returned as part of the result set.
	SET XACT_ABORT ON; -- Specifies whether SQL Server automatically rolls back the current transaction when a Transact-SQL statement raises a run-time error.
	
BEGIN TRY

		IF	0 = ISNULL(@catalogId, 0)
			SET @catalogId = 2300;

		IF	0 = ISNULL(@kindId, 0)
			SET @kindId = 3300;
		
		IF	0 = ISNULL(@documentTypeId, 0)
			SET @documentTypeId = 16 --ustawienie domyślnego typu na FVS, bo aktualnie brak możliwości edycji

		DECLARE @changes TABLE (
			[DocumentTypeShortcut] nvarchar(40) COLLATE database_default,
			[Name] nvarchar(100) COLLATE database_default,
			[DocumentNumerationShortcut] nvarchar(40) COLLATE database_default,
			[DocumentNumber] nvarchar(40) COLLATE database_default,
			[SeriesNo] INT,
			[CatalogId] INT,
			[KindId] INT,
			[DepartmentId] INT,
			[IssueDate] DATE,
			[OperationDate] DATE,
			[SaleRegister] INT,
			[RegisterMethod] SMALLINT,
			[InRegisterDate] DATE,
			[CalculateVatMethod] SMALLINT,
			[JPK_V7] VARCHAR(256) COLLATE database_default,
			[JPK] SMALLINT,
			[CashSettled] SMALLINT,
			[Marker] SMALLINT,
			[PublishInventoryDocumentMode] SMALLINT,
			[ReservationType] SMALLINT,
			[PriceTypeId] INT,
			[PriceKind] SMALLINT,
			[AccountingPattern] nvarchar(40) COLLATE database_default,
			[DepartmentInSeries] INTEGER,
			[Period] SMALLINT,
			[FiscalizationState] SMALLINT,
            [IsOssProcedure] BIT,
            [OssShippingCountryId] INT,
            [TypeExternal] INT,
            [IdExternal] INT,
            [Id2External] VARCHAR(32)
 			);		
			
		INSERT INTO @changes
		SELECT
			docType.[kod] AS [DocumentTypeShortcut],
			docType.[nazwa] AS [Name],
			IIF(kind.DocumentTypeShortcut = docType.kod AND LEN(kind.NumerationShortcut) > 0,  --jeśli typ dokumentu z rodzaju to seria również z rodzaju
				kind.NumerationShortcut,
				IIF((select count(*) from [HM].[TD] td
						inner join [HM].[SD] sd on sd.[idTypDok] = td.[id]
						where sd.[aktywny] = 1 and sd.[idTypDok] = @documentTypeId) = 1
				,(SELECT TOP(1) [kod] FROM [HM].[SD] WHERE @documentTypeId = [idTypDok] AND [aktywny] = 1 ORDER BY SD.id)
				,(SELECT ''))
			) AS [DocumentNumerationShortcut],
			NEWID() AS [DocumentNumber],
			0 as SeriesNo,
			@catalogId AS CatalogId,
			@kindId AS KindId,
			IIF(userAcc.UserId IS NOT NULL, kind.DepartmentId, @departmentId) AS DepartmentId, --dział handlowy z rodzaju o ile użytkownik ma do niego prawo, jeśli nie to bieżący
			CONVERT(DATE, GETDATE()) AS IssueDate,
			CONVERT(DATE, IIF(kind.NoOperationDate = 1, NULL, GETDATE())) AS OperationDate,
			docType.[idRejestr] AS [SaleRegister],
			HM.GetRegisterMethod(docType.charakter, GETDATE()) AS [RegisterMethod],
			CONVERT(DATE, IIF(kind.NoOperationDate = 1, NULL, HM.GetInRegisterDate(0, @documentTypeId, CONVERT(DATE, GETDATE()), CONVERT(DATE, GETDATE()), default))) AS [InRegisterDate],
			ISNULL((SELECT CASE WHEN [NumericValue] = 1 THEN 0 ELSE 1 END FROM [HM].[WorkParameters] WHERE [Id] = 1050), 0) AS [CalculateVatMethod],
			docType.[jpk_v7] AS [JPK_V7],
			docType.[jpkRodzaj] AS [JPK],
			0,
			kind.Tag AS Marker,
			kind.PublishInventoryDocumentMode AS PublishInventoryDocumentMode,
			IIF(docType.charakter = 124, 2, kind.ReservationType) AS ReservationType, --dla marży ze wskazaniem dostaw
			kind.PriceTypeId AS PriceTypeId,
			IIF(docType.charakter <> 79, kind.PriceKind, 2) AS PriceKind, --dla rachunku netto
			IIF(LEN(kind.AccountingPattern) <> 0, kind.AccountingPattern, ISNULL(accPaterns.kod, '')) AS AccountingPattern,	--schemat księgowania z rodzaju, jeśli nie wypełniony to z typu dokumentu
			0, 0,
			CONVERT(SMALLINT, docType.[flag] & 0x01) AS [FiscalizationState],
            docType.procedura_OSS AS [IsOssProcedure],
            IIF(docType.procedura_OSS = 1, (SELECT TOP 1 ID FROM [HM].[vKraje] vk WHERE vk.Kod = 'PL'), NULL) [OssShippingCountryId],
            0                                                                 AS TypeExternal,
            null                                                              AS IdExternal,
            null                                                              AS Id2External 
		FROM HM.SaleDocumentsKindsView kind
			JOIN HM.TD docType ON docType.id = @documentTypeId
			LEFT OUTER JOIN HM.SK accPaterns ON accPaterns.id = docType.idSchematKs AND accPaterns.super IS NOT NULL
			LEFT OUTER JOIN HM.UserDepartmentView userAcc ON userAcc.DepartmentId = kind.DepartmentId AND userAcc.UserId = @userId
		WHERE kind.Id = @kindId 

		-- Nowy numer dokumentu
		DECLARE 
			@id_dok INT,
			@DocumentNumerationCode VARCHAR(10),
			@genContractorId INT,
			@currency VARCHAR(3),
			@buffer BIT,
			@date DATE,
			@tableId SMALLINT,
			@code VARCHAR(40),
			@numberInSeries INT,
			@period INT,
			@departmentInSeries INT

		SELECT 
			@id_dok = 0,
			@DocumentNumerationCode = DocumentNumerationShortcut,
			@genContractorId = 0,
			@DepartmentId = DepartmentId,
			@Currency = '',
			@buffer = 1,
			@Date = IssueDate,
			@TableId = 16,
			@numberInSeries = 0
		FROM @changes

		-- tutaj weryfikacja czy jest 1 seria aktywna czy więcej (jeżeli więcej to nie powinien być generowany numer dokumentu dla nowwego dokumentu sprzedaży)
		IF (SELECT COUNT(*) FROM [HM].[SD] WHERE [idTypDok] = @documentTypeId and [aktywny] = 1) = 1
		BEGIN
			EXEC [HM].[GenerateDocumentNumber]    
				@id_dok,
				@DocumentTypeId,
				@DocumentNumerationCode,
				@userId,
				@genContractorId,
				@departmentId,
				@currency,
				@buffer,
				@date,
				@tableId,    
				@code OUT,
				@numberInSeries OUT,
				@period OUT,
				@departmentInSeries OUT
        
            IF ISNULL(@code, '@0') != '@0'
            BEGIN
                DECLARE @shortcutMessage NVARCHAR(200) = CONCAT('Dokument sprzedaży o numerze "', @code, '" istnieje w bazie danych.')
                IF EXISTS(SELECT TOP 1 1 FROM [HM].[DK] dk WHERE dk.[typ] = 0 and dk.[kod] = @code and dk.bufor = @buffer)
                    THROW 50000, @shortcutMessage, 1;
            END
			
            UPDATE @changes set
				DocumentNumber = ISNULL(IIF(@code = '@0', NULL, @code), NEWID()),
				SeriesNo = @numberInSeries,
				Period = @period,
				DepartmentInSeries = @departmentInSeries
		END

		DECLARE @x TABLE ([value] xml);
		INSERT INTO @x VALUES (@docData);
		
		IF 1 = (SELECT COUNT(*) FROM @changes)
		BEGIN
			UPDATE @x SET value.modify('delete /SaleDocument/DocumentTypeShortcut');
			UPDATE @x SET value.modify('insert <DocumentTypeShortcut>{sql:column("DocumentTypeShortcut")}</DocumentTypeShortcut> as first into (/SaleDocument)[1]') FROM @changes;		
			
			UPDATE @x SET value.modify('delete /SaleDocument/Name');
			UPDATE @x SET value.modify('insert <Name>{sql:column("Name")}</Name> as first into (/SaleDocument)[1]') FROM @changes;		
			
			UPDATE @x SET value.modify('delete /SaleDocument/DocumentNumerationShortcut');
			UPDATE @x SET value.modify('insert <DocumentNumerationShortcut>{sql:column("DocumentNumerationShortcut")}</DocumentNumerationShortcut> as first into (/SaleDocument)[1]') FROM @changes;	
			
			UPDATE @x SET value.modify('delete /SaleDocument/DocumentNumber');
			UPDATE @x SET value.modify('insert <DocumentNumber>{sql:column("DocumentNumber")}</DocumentNumber> as first into (/SaleDocument)[1]') FROM @changes;	

			UPDATE @x SET value.modify('delete /SaleDocument/CatalogId');
			UPDATE @x SET value.modify('insert <CatalogId>{sql:column("CatalogId")}</CatalogId> as first into (/SaleDocument)[1]') FROM @changes;	

			UPDATE @x SET value.modify('delete /SaleDocument/KindId');
			UPDATE @x SET value.modify('insert <KindId>{sql:column("KindId")}</KindId> as first into (/SaleDocument)[1]') FROM @changes;	

			UPDATE @x SET value.modify('delete /SaleDocument/DepartmentId');
			UPDATE @x SET value.modify('insert <DepartmentId>{sql:column("DepartmentId")}</DepartmentId> as first into (/SaleDocument)[1]') FROM @changes;	

			UPDATE @x SET value.modify('delete /SaleDocument/IssueDate');
			UPDATE @x SET value.modify('insert <IssueDate>{sql:column("IssueDate")}</IssueDate> as first into (/SaleDocument)[1]') FROM @changes;	

			UPDATE @x SET value.modify('delete /SaleDocument/OperationDate');
			UPDATE @x SET value.modify('insert <OperationDate>{sql:column("OperationDate")}</OperationDate> as first into (/SaleDocument)[1]') FROM @changes;	

			UPDATE @x SET value.modify('delete /SaleDocument/SaleRegister');
			UPDATE @x SET value.modify('insert <SaleRegister>{sql:column("SaleRegister")}</SaleRegister> as first into (/SaleDocument)[1]') FROM @changes;
			
			UPDATE @x SET value.modify('delete /SaleDocument/RegisterMethod');
			UPDATE @x SET value.modify('insert <RegisterMethod>{sql:column("RegisterMethod")}</RegisterMethod> as first into (/SaleDocument)[1]') FROM @changes;	

			UPDATE @x SET value.modify('delete /SaleDocument/InRegisterDate');
			UPDATE @x SET value.modify('insert <InRegisterDate>{sql:column("InRegisterDate")}</InRegisterDate> as first into (/SaleDocument)[1]') FROM @changes;	

			UPDATE @x SET value.modify('delete /SaleDocument/CalculateVatMethod');
			UPDATE @x SET value.modify('insert <CalculateVatMethod>{sql:column("CalculateVatMethod")}</CalculateVatMethod> as first into (/SaleDocument)[1]') FROM @changes;
			
			UPDATE @x SET value.modify('delete /SaleDocument/JPK_V7');
			UPDATE @x SET value.modify('insert <JPK_V7>{sql:column("JPK_V7")}</JPK_V7> as first into (/SaleDocument)[1]') FROM @changes;

			UPDATE @x SET value.modify('delete /SaleDocument/JPK');
			UPDATE @x SET value.modify('insert <JPK>{sql:column("JPK")}</JPK> as first into (/SaleDocument)[1]') FROM @changes;

			UPDATE @x SET value.modify('delete /SaleDocument/CashSettled');
			UPDATE @x SET value.modify('insert <CashSettled>{sql:column("CashSettled")}</CashSettled> as first into (/SaleDocument)[1]') FROM @changes;

            UPDATE @x SET value.modify('delete /SaleDocument/IsOssProcedure');
			UPDATE @x SET value.modify('insert <IsOssProcedure>{sql:column("IsOssProcedure")}</IsOssProcedure> as first into (/SaleDocument)[1]') FROM @changes;

            UPDATE @x SET value.modify('delete /SaleDocument/OssShippingCountryId');
			UPDATE @x SET value.modify('insert <OssShippingCountryId>{sql:column("OssShippingCountryId")}</OssShippingCountryId> as first into (/SaleDocument)[1]') FROM @changes;

			UPDATE @x SET value.modify('insert text{sql:column("Marker")} into (/SaleDocument/Marker)[1]') FROM @changes;
			--jak pojawi się w metadata UPDATE @x SET value.modify('insert text{sql:column("PublishInventoryDocumentMode")} into (/SaleDocument/PublishInventoryDocumentMode)[1]') FROM @changes;

			UPDATE @x SET value.modify('insert text{sql:column("ReservationType")} into (/SaleDocument/ReservationType)[1]') FROM @changes;
			UPDATE @x SET value.modify('insert text{sql:column("PriceTypeId")} into (/SaleDocument/PriceTypeId)[1]') FROM @changes;
			UPDATE @x SET value.modify('insert text{sql:column("PriceKind")} into (/SaleDocument/PriceKind)[1]') FROM @changes;
			UPDATE @x SET value.modify('insert text{sql:column("AccountingPattern")} into (/SaleDocument/AccountingPattern)[1]') FROM @changes;

			UPDATE @x SET value.modify('delete /SaleDocument/SeriesNo');
			UPDATE @x SET value.modify('insert <SeriesNo>{sql:column("SeriesNo")}</SeriesNo> as first into (/SaleDocument)[1]') FROM @changes;	

			UPDATE @x SET value.modify('delete /SaleDocument/Period');
			UPDATE @x SET value.modify('insert <Period>{sql:column("Period")}</Period> as first into (/SaleDocument)[1]') FROM @changes;

			UPDATE @x SET value.modify('delete /SaleDocument/DepartmentInSeries');
			UPDATE @x SET value.modify('insert <DepartmentInSeries>{sql:column("DepartmentInSeries")}</DepartmentInSeries> as first into (/SaleDocument)[1]') FROM @changes;

			UPDATE @x SET value.modify('delete /SaleDocument/FiscalizationState');
			UPDATE @x SET value.modify('insert <FiscalizationState>{sql:column("FiscalizationState")}</FiscalizationState> as first into (/SaleDocument)[1]') FROM @changes;

			UPDATE @x SET value.modify('delete /SaleDocument/PublishInventoryDocumentMode');
			UPDATE @x SET value.modify('insert <PublishInventoryDocumentMode>{sql:column("PublishInventoryDocumentMode")}</PublishInventoryDocumentMode> as first into (/SaleDocument)[1]') FROM @changes;

            UPDATE @x SET value.modify('delete /SaleDocument/TypeExternal');
			UPDATE @x SET value.modify('insert <TypeExternal>{sql:column("TypeExternal")}</TypeExternal> as first into (/SaleDocument)[1]') FROM @changes;

            UPDATE @x SET value.modify('delete /SaleDocument/IdExternal');
			UPDATE @x SET value.modify('insert <IdExternal>{sql:column("IdExternal")}</IdExternal> as first into (/SaleDocument)[1]') FROM @changes WHERE IdExternal IS NOT NULL;

            UPDATE @x SET value.modify('delete /SaleDocument/Id2External');
			UPDATE @x SET value.modify('insert <Id2External>{sql:column("Id2External")}</Id2External> as first into (/SaleDocument)[1]') FROM @changes WHERE Id2External IS NOT NULL;
		END
		
		SELECT [value].query('/SaleDocument')
		from @x FOR XML PATH('');

	END TRY
	BEGIN CATCH
		THROW;
	END CATCH
END

GO

CREATE
	OR

ALTER PROCEDURE [HM].[ReadForeignOrderDocumentAsXML] (
	@foreignOrderId INT,
	@userId INT,
	@moduleCode NVARCHAR(255),
	@removeInvalidXMLCharacters BIT = 0
	)
AS
BEGIN
	BEGIN TRY
		-- Pola wlasne naglowka dokumentu
		CREATE TABLE #customFields (xmlData XML)

		EXECUTE HM.CustomFieldsDocumentPrepareTemporaryDataBeforeRead @foreignOrderId,
			@moduleCode,
			'ZO',
			'zamówienia obce'

		-- Pola wlasne pozycji dokumentu
		CREATE TABLE #positionsCustomFields (
			positionId INT,
			customFields XML
			)

		EXECUTE HM.CustomFieldsPositionsPrepareTemporaryDataBeforeRead @foreignOrderId,
			@moduleCode,
			'ZP',
			'pozycje zamówień obcych' -- Dokumenty powiazane

		DECLARE @relatedDocuments AS HM.RelatedDocuments

		INSERT @relatedDocuments
        EXEC HM.GetRelatedDocumentsToZMO @ID_zam = @foreignOrderId -- Obecnie nie można wykorzystać funkcji ponieważ niezbędne jest odwołanie do schemy CM które powoduje błąd przy tworzeniu funkcji.
		
		
		DECLARE @relatedToInventoryDocument VARCHAR(5) = IIF(EXISTS(SELECT TOP 1 id FROM @relatedDocuments rd WHERE rd.GroupDescription = 'Dokumenty magazynowe'), 'true', 'false')

		-- Inne powiazania
		DECLARE @relatedElements AS HM.RelatedElements

		INSERT @relatedElements
		EXEC HM.GetRelatedElementsForDocument @docId = @foreignOrderId,
			@docBase = 45

		DECLARE @NpRateId INT = (
				SELECT TOP (1) Id
				FROM HM.XT
				WHERE super = 10000
					AND subtyp = 7
					AND flag & 0x200 <> 0
				)
		DECLARE @ZeroRateId INT = (
				SELECT TOP (1) Id
				FROM HM.XT
				WHERE super = 10000
					AND subtyp = 3
				)
		-- czy istnieje zaliczka do zamówienia (TODO: do weryfikacji jak będzię można wystawiać zaliczkę do zamówienia)
		DECLARE @AdvanceInvoiceExists BIT = HM.AdvanceInvoiceExists(@foreignOrderId)
		-- czy istnieje dokument sprzedazy do zamówienia
		DECLARE @SaleDocumentExists BIT = ISNULL((
				SELECT TOP 1 1
				FROM HM.TR AS tzo
				INNER JOIN HM.PO ON tzo.id = PO.id1
				INNER JOIN HM.TR AS tdk ON PO.id2 = tdk.id AND tdk.owner_base = 16
				WHERE tzo.owner_id = @foreignOrderId AND tzo.owner_base = 45
				), 0)
		-- Czy istnieje wersja do zamówienia
		DECLARE @ForeignOrderVersionExists BIT = (
				SELECT TOP 1 1
				FROM HM.ForeignOrdersVersions v
				WHERE v.ZoId = @foreignOrderId
				)
		DECLARE @resultXml XML = (
				SELECT ZO.ManualSettlement,
					ISNULL((
							SELECT *
							FROM HM.ft_Contractor(ZO.khadid)
							FOR XML PATH(''),
								ELEMENTS XSINIL,
								TYPE
							), '') AS ContractorData,
					ISNULL((
							SELECT *
							FROM HM.ft_Contractor(ZO.odadid)
							FOR XML PATH(''),
								ELEMENTS XSINIL,
								TYPE
							), '') AS ReceiverData,
					ISNULL((
							SELECT 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(Quantity)
														FROM [HM].[dbo_ForeignOrderPositionSettlementsView]
														WHERE OrderPositionId = zp.id
														), 0) AS DECIMAL(24, 4)) AS RealizedReservedQuantity
										FROM HM.RZ
										INNER JOIN HM.KH ON RZ.magazyn = KH.id
										WHERE RZ.bazadokrz = 45
											AND RZ.bazapozrz = 30
											AND RZ.idpozrz = ZP.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) + 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(ilosc)
														FROM HM.ZZ
														WHERE typ = 96
															AND baza1 = 36
															AND id1 = PW.id
															AND baza1 = 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
										INNER JOIN HM.DW ON DW.id = PW.iddw
										WHERE PW.idmg IN (
												SELECT id
												FROM hm.rz
												WHERE idpozrz = ZP.id
													AND bazapozrz = 30
													AND subtyp & 0x01 = 0
												)
											AND PW.typ = 26
										FOR XML PATH('ReservationDelivery'),
											ELEMENTS XSINIL,
											TYPE
										), '') AS ReservationsDelivery,
								zp.IsDocumentPosition,
								zp.Description,
								-- pola własne
								(
									SELECT pcf.customFields
									FROM #positionsCustomFields pcf
									WHERE pcf.positionId = ZP.id
									),
								zp.[PriceOfSetCalculatedBasedOfElements],
								zp.[NoEditOfSet],
								zp.[VatRateId],
								zp.[PricePLN],
								zp.[PriceGross],
								zp.[PriceGrossPLN],
								zp.[PriceListPrice],
								zp.[PriceListPriceGross],
								zp.[OriginalPriceListPrice],
								zp.[OriginalPriceListPriceCurrency],
								zp.[OriginalBasePrice],
								zp.[OriginalBasePriceCurrency],
								zp.[BasePricePLN],
								zp.[ValueGross],
								zp.[ValueVat],
								zp.[ValuePLN],
								zp.[ValueGrossPLN],
								zp.[ValueVatPLN],
								zp.[PriceTypeId],
								zp.[OO],
								zp.[CodePKWIU],
								zp.[Discount],
								zp.[ReservationStockCode],
								zp.[StockStatus],
								zp.[ServiceExpense],
								zp.[Id],
								zp.[NodeId],
								zp.[ParentId],
								zp.[UserOrdinalNumber],
								zp.[OrdinalNumber],
								zp.[DocumentId],
								zp.[OriginalDocumentId],
								zp.[BaseType],
								zp.[Guid],
								zp.[ProductId],
								zp.[ProductShortcut],
								zp.[IsElementOfSet],
								zp.[IsSet],
								zp.[UnitOfMeasurement],
								zp.[EvidenceUnitOfMeasurement],
								zp.[ConversionFactorUoM],
								zp.[Quantity],
								zp.[UnitQuantity],
								zp.[ReservationQuantity],
								zp.[EvidenceQuantity],
								zp.[ReservationType],
								zp.[AccountingPattern],
								zp.[Price],
								zp.[Value],
								zp.[PriceRounding],
								zp.[IsService],
								zp.[CompletedQuantity],
								zp.ForSettlement,
								zp.ForSettlementPLN,
								zp.UsedInAdvanceInvoice,
								zp.AdvanceInvoiceValueGross,
								'' as [AdvanceInvoiceDivisionPositions],
								'false' AS IsVatModifiedManually
							FROM HM.ft_ForeignOrderPositions(@foreignOrderId, @removeInvalidXMLCharacters) zp
							ORDER BY ZP.lp
							FOR XML PATH('ForeignOrderPosition'),
								ELEMENTS XSINIL,
								TYPE
							), '') AS DocumentPositions,
					-- Stawki VAT
					ISNULL((
							SELECT [Id],
								[Code],
								[Value],
								[ValuePLN],
								[ValueGross],
								[ValueGrossPLN],
								[ValueVat],
								[ValueVatPLN]
							FROM HM.ft_VatRates(45, @foreignOrderId, @NpRateId, @ZeroRateId)
							ORDER BY (CASE WHEN Code LIKE '[a-z]%' THEN 1 ELSE 0 END),
								Code DESC
							FOR XML PATH('VatRate'),
								ELEMENTS XSINIL,
								TYPE
							), '') AS VatRates, -- Dokumenty powiązane
					ISNULL((
							SELECT [Id],
                                [DocumentTypeId],
								[DocumentType],
								[Number],
								[Date],
								[Person],
								[CharacterId],
								[InBuffer]
							FROM HM.ft_RelatedDocuments(@relatedDocuments)
							FOR XML PATH('RelatedDocument'),
								ELEMENTS XSINIL,
								TYPE
							), '') AS RelatedDocuments, -- Inne powiązania
					ISNULL((
							SELECT [Id],
								[TypeName],
								[Type],
								[Details],
								[InBuffer]
							FROM HM.ft_RelatedElements(@relatedElements)
							FOR XML PATH('RelatedElement'),
								ELEMENTS XSINIL,
								TYPE
							), '') AS RelatedElements, -- pola własne
					(
						SELECT *
						FROM #customFields
						),
					zo.[Id],
					zo.[Guid],
					zo.[DocumentNumber],
					zo.[SeriesNo],
					zo.[Name],
					zo.[CorrectedDocumentId],
					zo.[IssueDate],
					zo.[OperationDate],
					zo.[IsCorrection],
					zo.[Description],
					zo.[RecipientPerson],
					zo.[BaseType],
					zo.[DocumentTypeShortcut],
					zo.[DocumentNumerationShortcut],
					zo.[IsIssued],
					zo.[Value],
					zo.[DocumentStatus],
					zo.[DocumentStatusDescription],
					zo.[HmImported],
					zo.[Archived],
					zo.[FkExported],
					zo.[IsCreatedFromForeignOrder],
					zo.[Corrected],
					zo.[Canceled],
					zo.[IsActive],
					zo.[MigrationStatus],
					zo.[ContractorId],
					zo.[DepartmentId],
					zo.[AccountingPattern],
					zo.[IssuerId],
					zo.[IssuerLogin],
					zo.[DepartmentInSeries],
					zo.[Period],
					zo.[RDFStatus],
					zo.[ReceiverId],
					zo.[ReceiverAddressName],
					zo.[PayRegistryId],
					zo.[PaymentFormClientId],
					zo.[PaymentDeadline],
					zo.[ValueGross],
					zo.[ValueVat],
					zo.[ValuePLN],
					zo.[ValueGrossPLN],
					zo.[ValueVatPLN],
					zo.[Currency],
					zo.[ExchangeRate],
					zo.[PriceTypeId],
					zo.[Discount],
					zo.[SplitPayment],
					zo.[InRegisterDate],
					zo.[CorrectionCause],
					zo.[PriceKind],
					zo.[InventorySettlementStatus],
					zo.[PublishInventoryDocumentMode],
					zo.[CashSettled],
					zo.[RegisterMethod],
					zo.[CalculateVatMethod],
					CASE WHEN [HM].[fnMayCreateSaleDocument](ZO.id) = 1 THEN 'true' ELSE 'false' END AS MayCreateSaleDocument,
					CASE WHEN [HM].[fnMayCreateInventoryDocument](ZO.id) = 1 THEN 'true' ELSE 'false' END AS MayCreateInventoryDocument,
					CASE WHEN [HM].[fnMayCreateAdvanceInvoice](ZO.id) = 1 THEN 'true' ELSE 'false' END AS MayCreateAdvanceInvoice,
					zo.UseAdvanceInvoiceIsReadOnly,
					zo.UseAdvanceInvoice,
					zo.IsForeignOrderVersion,
					zo.ForeignOrderVersionExists,
					zo.[KindId],
					zo.[CatalogId],
					zo.[Marker],
					zo.[ReservationType],
					IIF(@AdvanceInvoiceExists = 1, 'true', 'false') AS AdvanceInvoiceExists,
					IIF(@SaleDocumentExists = 1, 'true', 'false') AS SaleDocumentExists,
					@relatedToInventoryDocument AS RelatedToInventoryDocument,
					'false' AS IsVatModifiedManually,
                    zo.[ManualSettlementStatus],
                    0 AS ToPay,
					zo.TypeExternal,
                    zo.IdExternal,
                    zo.Id2External
				FROM HM.ft_ForeignOrderDocument(@foreignOrderId, @removeInvalidXMLCharacters) zo
				FOR XML PATH(''), ELEMENTS XSINIL,
					ROOT('ForeignOrderDocument')
				)

		IF (OBJECT_ID('tempdb..#customFieldsDefinitions') IS NOT NULL)
			DROP TABLE #customFieldsDefinitions

		IF (OBJECT_ID('tempdb..#customFields') IS NOT NULL)
			DROP TABLE #customFields

		IF (OBJECT_ID('tempdb..#positionsCustomFields') IS NOT NULL)
			DROP TABLE #positionsCustomFields

		IF (OBJECT_ID('tempdb..#positionsCustomFieldsDefinitions') IS NOT NULL)
			DROP TABLE #positionsCustomFieldsDefinitions

		EXEC HM.ReadForeignOrderDocumentRIAsXML @foreignOrderId,
			@resultXml,
			@userId,
			@moduleCode
	END TRY

	BEGIN CATCH
		DECLARE @ErrorNumber INT = ERROR_NUMBER();

		IF (
				@removeInvalidXMLCharacters = 0
				AND (@ErrorNumber IN (9420, 6841))
				)
		BEGIN
			EXEC HM.ReadForeignOrderDocumentAsXML @foreignOrderId,
				@userId,
				@moduleCode,
				@removeInvalidXMLCharacters = 1;

			GOTO END_CATCH;
		END;

		THROW;

		END_CATCH:
	END CATCH
END

GO

-- Uwaga: Gdy dodasz nowy parametr, użuj go równiez przy wywołaniu na końcu procedury (szukaj: EXEC [HM].[ReadSaleDocumentAsXML]), sugeruje się pozostawić parametr @removeInvalidXMLCharacters na końcu
CREATE
	OR

ALTER PROCEDURE [HM].[ReadSaleDocumentAsXML] (
	@docId INT,
	@userId INT,
	@moduleCode NVARCHAR(255),
	@removeInvalidXMLCharacters BIT = 0
	)
AS
BEGIN
	BEGIN TRY
		DECLARE @correction BIT = 0 -- korekta zwykla lub zbiorcza
		DECLARE @correctedDocId INT

		SELECT 
			@correction = IIF(iddokkoryg IS NOT NULL
				OR flag & 0x20 = 0x20, 1, 0),
			@correctedDocId = iddokkoryg
		FROM HM.DK
		WHERE id = @docId

		-- Pola wlasne naglowka dokumentu
		CREATE TABLE #customFields (xmlData XML)

		EXECUTE HM.CustomFieldsDocumentPrepareTemporaryDataBeforeRead @docId,
			@moduleCode,
			'DK',
			'dokumenty sprzedaży'

		-- Pola wlasne pozycji dokumentu
		CREATE TABLE #positionsCustomFields (
			positionId INT,
			customFields XML
			)

		EXECUTE HM.CustomFieldsPositionsPrepareTemporaryDataBeforeRead @docId,
			@moduleCode,
			'DP',
			'pozycje dokumentów sprzedaży',
			@correctedDocId,
			'DK'

		-- Płatności
		DECLARE @payments AS HM.Payments

		INSERT @payments
		SELECT *
		FROM [HM].[ft_Payments](@docId)

		DECLARE @hasPayments VARCHAR(5)

		IF EXISTS (SELECT 1 FROM @payments)
			SET @hasPayments = 'true';
		ELSE
			SET @hasPayments = 'false';

		-- Dokumenty powiazane
		DECLARE @relatedDocuments AS HM.RelatedDocuments

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

		DECLARE @relatedToForeignOrder VARCHAR(5) = IIF(EXISTS(SELECT TOP 1 id FROM @relatedDocuments rd WHERE rd.GroupDescription = 'Zamówienia'), 'true', 'false')
		DECLARE @relatedForeignOrderBaseType SMALLINT = ISNULL((SELECT TOP 1 subtyp FROM @relatedDocuments rd INNER JOIN HM.ZO ON rd.id = ZO.id WHERE rd.GroupDescription = 'Zamówienia'), 0)
		DECLARE @relatedToInventoryDocument VARCHAR(5) = IIF(EXISTS(SELECT TOP 1 id FROM @relatedDocuments rd WHERE rd.GroupDescription = 'Dokumenty magazynowe'), 'true', 'false')
        DECLARE @relatedToParTypeDocument VARCHAR(5) = IIF(EXISTS(SELECT TOP 1 id FROM @relatedDocuments rd WHERE rd.CharakterDocumentId = 50), 'true', 'false') -- powiązany do PAR
        DECLARE @hasRelationParToFvs VARCHAR(5) = IIF(EXISTS(SELECT TOP 1 id FROM HM.ZZ zz WHERE zz.typ = 150 AND zz.id1 = @docId), 'true', 'false') -- ma powiązany dokument FVS w buforze

		--czy istnieje zaliczka do powiązanych zamówień
		DECLARE @foreignOrderWithAdvanceInvoiceExists BIT
		SELECT @foreignOrderWithAdvanceInvoiceExists = HM.ForeignOrderWithAdvanceInvoiceExists(@docId)

		-- Inne powiazania
		DECLARE @relatedElements AS HM.RelatedElements

		INSERT @relatedElements
		EXEC [HM].[GetRelatedElementsForDocument] @docId = @docId,
			@docBase = 16 -- sprzedaż

		DECLARE @NpRateId INT = (
				SELECT TOP (1) Id
				FROM HM.XT
				WHERE super = 10000
					AND subtyp = 7
					AND flag & 0x200 <> 0
				)
		DECLARE @ZeroRateId INT = (
				SELECT TOP (1) Id
				FROM HM.XT
				WHERE super = 10000
					AND subtyp = 3
				)
		DECLARE @resultXml XML = (
				SELECT dk.[ExchangeRatePIT],
					dk.[FiscalizationState],
					dk.ReceiptNumber,
					dk.UniqueCashBoxNumber,
					dk.[JPK_V7],
					dk.[JPK],
					dk.[SaleRegister],
					dk.[IsGroupCorrection],
					dk.[DigitalInvoice],
					dk.RelatedForeignOrderDocumentId,
					dk.RelatedForeignOrderVersionId,
					dk.AdvanceInvoiceAmount,
					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 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]
											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,
									-- pola własne
									(
										SELECT pcf.customFields
										FROM #positionsCustomFields 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.[CompletedQuantity],
									dp.ForSettlement AS ForSettlement,
									dp.[AdvanceInvoiceSettlementValueGross],
									dp.[IsOssService],
									'false' AS IsVatModifiedManually
								FROM [HM].[ft_SalePositions](@docId, 16, dk.flag, dk.subtyp, @removeInvalidXMLCharacters, @correction) 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,
					@hasPayments 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].[DocumentPackages] pak 
                                WHERE pak.DKId = @docId
								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 *
						FROM #customFields
						),
					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.[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],
					CAST(dk.AllToPay - dk.PaymentsSum  AS DECIMAL(24, 2)) AS ToPay,
					@relatedToForeignOrder AS RelatedToForeignOrder,
					@relatedForeignOrderBaseType AS RelatedForeignOrderBaseType,
					@relatedToInventoryDocument AS RelatedToInventoryDocument,
					IIF (ISNULL(@foreignOrderWithAdvanceInvoiceExists, 0) = 1, 'true', 'false') AS ForeignOrderWithAdvanceInvoiceExists,
                    @relatedToParTypeDocument AS CreatedFromReceipt,
                    @hasRelationParToFvs as HasRelationParToFvs,
					'false' AS IsVatModifiedManually
				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')
				)

		IF (OBJECT_ID('tempdb..#customFieldsDefinitions') IS NOT NULL)
			DROP TABLE #customFieldsDefinitions

		IF (OBJECT_ID('tempdb..#customFields') IS NOT NULL)
			DROP TABLE #customFields

		IF (OBJECT_ID('tempdb..#positionsCustomFields') IS NOT NULL)
			DROP TABLE #positionsCustomFields

		IF (OBJECT_ID('tempdb..#positionsCustomFieldsDefinitions') IS NOT NULL)
			DROP TABLE #positionsCustomFieldsDefinitions

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

	BEGIN CATCH
		DECLARE @ErrorNumber INT = ERROR_NUMBER();

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

			GOTO END_CATCH;
		END;

		THROW;

		END_CATCH:
	END CATCH
END

GO

CREATE OR ALTER PROCEDURE [HM].[stdhm_getrec_dk] @id INT
AS
SELECT id,
       flag,
       aktywny,
       subtyp,
       typ,
       znacznik,
       rodzaj,
       katalog,
       info,
       wystawil,
       kod,
       seria,
       serianr,
       okres,
       seriadzial,
       nazwa,
       data,
       datasp,
       datawplywu,
       opis,
       khid,
       khadid,
       odid,
       odadid,
       ok,
       rr,
       wplaty,
       rabat,
       plattyp,
       plattermin,
       netto,
       vat,
       odebrane,
       typ_dk,
       grupacen,
       rejestrVat,
       rejestrVat2,
       iddokkoryg,
       wartoscSp,
       exp_fk,
       waluta,
       kurs,
       zyskdod,
       paragon,
       kod_obcy,
       magazyn,
       rozlmg,
       formaplatn,
       schemat,
       datarej,
       datarej2,
       bufor,
       super,
       anulowany,
       createdBy,
       createdDate,
       modifiedBy,
       modifiedDate,
       walNetto,
       walBrutto,
       wartPrzychod,
       kursDoch,
       eFaktura,
       statusRDF,
       guid,
       data_obcy,
       koszt,
       kosztAproksymowany,
       statusFK,
       typceny,
       jpk,
	   importKasa,
	   splitPayment,
	   forceSP,
	   checkWL,
	   accountId,
       jpk_v7,
	   importKasaZbiorczy,
	   IdPeriodicSaleReport,
	   fiscalDeviceNo,
       zeroVatRate,
       statusKsef,
       dataWystawieniaKsef,
       guidEarch,
       numberKsef,
       procedura_OSS,
       krajDostawy_OSS,
       krajWysylki_OSS,
       idSOD,
       sodSource,
       typeExternal,
       idExternal,
       id2External,
       statusMig
FROM hm.DK
WHERE id = @id
ORDER BY id ASC;

GO

CREATE OR ALTER PROCEDURE HM.stdhm_getrec_zo
@id int
AS
SELECT id,flag,aktywny,subtyp,typ,znacznik,rodzaj,katalog,info,wystawil,kod,seria,serianr,okres,seriadzial,nazwa,data,datasp,datawplywu,opis,khid,khadid,odid,odadid,ok,rr,wplaty,rabat,plattyp,plattermin,netto,vat,odebrane,typ_dk,grupacen,iddokkoryg,wartoscSp,exp_fk,waluta,kurs,zyskdod,paragon,kod_obcy,magazyn,rozlmg,formaplatn,schemat,datarej,bufor,super,anulowany,createdBy,createdDate,modifiedBy,modifiedDate,walNetto, walBrutto,statusRDF, guid,typceny,splitPayment, zeroVatRate, advanceInvoiceDeclared,typeExternal,
       idExternal,id2External,statusMig 
FROM hm.ZO
WHERE id = @id
ORDER BY id ASC

GO

CREATE OR ALTER PROCEDURE HM.stdhm_insert_dk
@id int out, @flag smallint, @aktywny smallint, @subtyp smallint, @typ smallint,
@znacznik smallint, @rodzaj int, @katalog int, @info smallint, @wystawil int,
@kod varchar(41), @seria varchar(6), @serianr int, @okres smallint, @seriadzial int,
@nazwa varchar(41), @data datetime, @datasp datetime, @datawplywu datetime, @opis varchar(81),
@khid int, @khadid int, @odid int, @odadid int,
@ok smallint, @rr smallint, @wplaty float, @rabat float, @plattyp int, @plattermin datetime,
@netto float, @vat float, @odebrane varchar(41), @typ_dk varchar(4), @grupacen smallint,
@rejestrVat int, @rejestrVat2 int, @iddokkoryg int, @wartoscSp float, @exp_fk smallint,
@waluta varchar(4), @kurs float, @zyskdod float, @paragon int, @kod_obcy varchar(256),
@magazyn int, @rozlmg smallint, @formaplatn int, @schemat varchar(5), @datarej datetime, @datarej2 datetime,
@bufor smallint, @super int, @anulowany smallint, @createdBy int, @createdDate datetime, @modifiedBy int,
@modifiedDate datetime, @walNetto float, @walBrutto float, @wartPrzychod float, @kursDoch float,
@eFaktura smallint, @statusRDF smallint, @guid varchar(38), @data_obcy datetime, @koszt float, @kosztAproksymowany float, 
@statusFK smallint, @typceny smallint, @jpk smallint, @importKasa bit, @splitPayment bit, @forceSP bit, @checkWL bit, @accountId int,
@jpk_v7 varchar(256), @importKasaZbiorczy bit, @IdPeriodicSaleReport int, @fiscalDeviceNo varchar(40), @zeroVatRate bit, 
@statusKsef int, @dataWystawieniaKsef datetime, @guidEarch varchar(38), @numberKsef varchar(38),
@procedura_OSS bit, @krajDostawy_OSS int, @krajWysylki_OSS int, @idSOD varchar(38), @sodSource smallint, @typeExternal int, @idExternal int, @id2External varchar(32)
AS
-- INSERT bez GUID'a !!!!!
-- wykonywany kilkukrotnie np. dla importu korekty generuje
-- VIOLATION of UNIQUE KEY constraint 'UC_DK_GUID
DECLARE @nrDokumentu varchar(40)
IF ('' = ISNULL(@kod,''))
	SET @nrDokumentu = CONVERT(varchar(40), NEWID())
ELSE
	SET @nrDokumentu = @kod
SET @createdDate = GETDATE()
IF @bufor = 0 BEGIN
	SET @modifiedDate = @createdDate
	SET @modifiedBy = @createdBy
END
DECLARE @dataZest1 datetime
SELECT @dataZest1 = CASE WHEN @subtyp IN (134, 135, 150, 151) THEN @datasp ELSE @data END
IF @id=0 BEGIN
	INSERT INTO DK (flag, aktywny, subtyp, typ, znacznik,
		rodzaj, katalog, info, wystawil, kod,
		seria, serianr, okres, seriadzial, nazwa,
		data, datasp, datawplywu, dataZest1, opis,
		khid, khadid, odid, odadid, ok, rr,
		wplaty, rabat, plattyp, plattermin, netto,
		vat, odebrane, typ_dk, grupacen, rejestrVat, rejestrVat2,
		iddokkoryg, wartoscSp, exp_fk, waluta,
		kurs, zyskdod, paragon, kod_obcy, magazyn,
		rozlmg, formaplatn, schemat, datarej, datarej2, bufor,
		super, anulowany, createdBy, createdDate,
		walNetto, walBrutto, wartPrzychod, kursDoch, eFaktura, statusRDF, data_obcy, koszt, kosztAproksymowany, 
		statusFK, typceny, jpk, importKasa, splitPayment, forceSP, checkWL, accountId, jpk_v7, importKasaZbiorczy, IdPeriodicSaleReport, fiscalDeviceNo, zeroVatRate,
		statusKsef, dataWystawieniaKsef, guidEarch, numberKsef,
		procedura_OSS, krajDostawy_OSS, krajWysylki_OSS, idSOD, sodSource, typeExternal, idExternal, id2External)
	VALUES (@flag, @aktywny, @subtyp, @typ, @znacznik,
		@rodzaj, @katalog, @info, @wystawil, @nrDokumentu,
		@seria, @serianr, @okres, @seriadzial, @nazwa,
		@data, @datasp, @datawplywu, @dataZest1, @opis,
		@khid, @khadid, @odid, @odadid, @ok, @rr,
		@wplaty, @rabat, @plattyp, @plattermin, @netto,
		@vat, @odebrane, @typ_dk, @grupacen, @rejestrVat, @rejestrVat2,
		@iddokkoryg, @wartoscSp, @exp_fk, @waluta,
		@kurs, @zyskdod, @paragon, @kod_obcy, @magazyn,
		@rozlmg, @formaplatn, @schemat, @datarej, @datarej2, @bufor,
		@super, @anulowany, @createdBy, @createdDate,
		@walNetto, @walBrutto, @wartPrzychod, @kursDoch, @eFaktura, @statusRDF, @data_obcy, @koszt, @kosztAproksymowany,
		@statusFK, @typceny, @jpk, @importKasa, @splitPayment, @forceSP, @checkWL, @accountId, @jpk_v7, @importKasaZbiorczy, @IdPeriodicSaleReport, @fiscalDeviceNo, @zeroVatRate,
		@statusKsef, @dataWystawieniaKsef, @guidEarch, @numberKsef,
		@procedura_OSS, @krajDostawy_OSS, @krajWysylki_OSS, @idSOD, @sodSource, @typeExternal, @idExternal, @id2External)
	SET @id = SCOPE_IDENTITY()
	IF @typ = 110
	BEGIN
		INSERT INTO SalesKinds (KindId, kod)
		VALUES (@id, @kod)
	END
	IF (@iddokkoryg <> 0 AND ((@typ = 0) OR (@typ = 202))) BEGIN
	    IF (@bufor = 0)
	    BEGIN

		    UPDATE DK SET flag = 8192 | flag WHERE (id = @iddokkoryg) OR ((iddokkoryg = @iddokkoryg) AND (id <> @id))
		    INSERT INTO LOCAL_LOG (baseName, rec_id, operation, term) SELECT 'DK', id, 3, @@SPID FROM DK WHERE (id = @iddokkoryg) OR ((iddokkoryg = @iddokkoryg) AND (id <> @id))
		END
		ELSE
		BEGIN


		    DECLARE @idTransOrig INT
		    SELECT @idTransOrig = id FROM TR WHERE typ = 1 AND owner_base = CASE @typ WHEN 0 THEN 16 ELSE 34 END AND owner_id = @iddokkoryg
		    UPDATE TR SET flag = (16384 | flag) WHERE id = @idTransOrig
		    EXEC hm_TR_RecalcStatus @idTransOrig
		END
	END
END ELSE BEGIN
	RAISERROR( '***IDENTITY_INSERT*** Próba dodania rekordu o identyfikatorze %ld do tabeli DK!', 16, 1, @id )
END
IF ('' = ISNULL(@kod,''))
	UPDATE DK SET kod = '@' + CONVERT(varchar(40), id) FROM DK WHERE id = @id
IF @bufor = 0 AND @typ in (0, 202) BEGIN
	INSERT INTO HW (iddk, typdk) values (@id,16)
END
IF ((0 = @bufor) AND (0 = (0x0100 & @flag)))
BEGIN
	EXEC hm_DP_PrzeliczZyskKoszt @id, 0
END

EXEC hm.IsDocumentAsPeriodicSaleReport @DKid = @id -- int

return @id

GO

CREATE OR ALTER PROCEDURE HM.stdhm_insert_zo
@id int out, @flag smallint, @aktywny smallint, @subtyp smallint, @typ smallint,
@znacznik smallint, @rodzaj int, @katalog int, @info smallint, @wystawil int,
@kod varchar(41), @seria varchar(6), @serianr int, @okres smallint, @seriadzial int,
@nazwa varchar(41), @data datetime, @datasp datetime, @datawplywu datetime, @opis varchar(81),
@khid int, @khadid int, @odid int, @odadid int,
@ok smallint, @rr smallint, @wplaty float, @rabat float, @plattyp int, @plattermin datetime,
@netto float, @vat float, @odebrane varchar(41), @typ_dk varchar(4), @grupacen smallint,
@iddokkoryg int, @wartoscSp float, @exp_fk smallint, @waluta varchar(4),
@kurs float, @zyskdod float, @paragon int, @kod_obcy varchar(41), @magazyn int,
@rozlmg smallint, @formaplatn int, @schemat varchar(5), @datarej datetime, @bufor smallint,
@super int, @anulowany smallint, @createdBy int, @createdDate datetime, @modifiedBy int, @modifiedDate datetime, @walNetto float, @walBrutto float,
@statusRDF smallint, @guid varchar(38), @typceny smallint , @splitPayment bit, @zeroVatRate bit, @advanceInvoiceDeclared bit, @typeExternal int, @idExternal int, @id2External varchar(32)
AS
DECLARE @nrDokumentu varchar(40)
IF ('' = ISNULL(@kod,''))
	SET @nrDokumentu = CONVERT(varchar(40), NEWID())
ELSE
	SET @nrDokumentu = @kod
SET @createdDate = GETDATE()
IF @id=0 BEGIN
	INSERT INTO ZO (flag, aktywny, subtyp, typ, znacznik,
		rodzaj, katalog, info, wystawil, kod,
		seria, serianr, okres, seriadzial, nazwa,
		data, datasp, datawplywu, opis, khid,
		khadid, odid, odadid, ok, rr,
		wplaty, rabat, plattyp, plattermin, netto,
		vat, odebrane, typ_dk, grupacen, iddokkoryg,
		wartoscSp, exp_fk, waluta, kurs,
		zyskdod, paragon, kod_obcy, magazyn, rozlmg,
		formaplatn, schemat, datarej, bufor, super, anulowany,
		createdBy, createdDate, walNetto, walBrutto, statusRDF, typceny,[splitPayment], zeroVatRate, advanceInvoiceDeclared, typeExternal, idExternal, id2External)
	VALUES (@flag, @aktywny, @subtyp, @typ, @znacznik,
		@rodzaj, @katalog, @info, @wystawil, @nrDokumentu,
		@seria, @serianr, @okres, @seriadzial, @nazwa,
		@data, @datasp, @datawplywu, @opis, @khid,
		@khadid, @odid, @odadid, @ok, @rr,
		@wplaty, @rabat, @plattyp, @plattermin, @netto,
		@vat, @odebrane, @typ_dk, @grupacen, @iddokkoryg,
		@wartoscSp, @exp_fk, @waluta, @kurs,
		@zyskdod, @paragon, @kod_obcy, @magazyn, @rozlmg,
		@formaplatn, @schemat, @datarej, @bufor, @super, @anulowany,
		@createdBy, @createdDate, @walNetto, @walBrutto, @statusRDF, @typceny,@splitPayment, @zeroVatRate, @advanceInvoiceDeclared, @typeExternal, @idExternal, @id2External)
	SET @id = SCOPE_IDENTITY()
	IF @typ = 110
	BEGIN
		INSERT INTO ForeignOrdersKinds (KindId, kod)
		VALUES (@id, @kod)
	END
	IF @typ = 111
	BEGIN
		INSERT INTO OwnOrdersKinds (KindId, kod)
		VALUES (@id, @kod)
	END
	IF (@iddokkoryg <> 0 AND ((@typ = 1) OR (@typ = 203))) BEGIN
	    IF (@bufor = 0)
	    BEGIN

		    UPDATE ZO SET flag = 8192 | flag WHERE (id = @iddokkoryg) OR ((iddokkoryg = @iddokkoryg) AND (id <> @id))
		    INSERT INTO LOCAL_LOG (baseName, rec_id, operation, term) SELECT 'ZO', id, 3, @@SPID FROM ZO WHERE (id = @iddokkoryg) OR ((iddokkoryg = @iddokkoryg) AND (id <> @id))
		END
		ELSE
		BEGIN


		    DECLARE @idTransOrig INT
		    SELECT @idTransOrig = id FROM TR WHERE typ = 0 AND owner_base = CASE @typ WHEN 1 THEN 45 ELSE 46 END AND owner_id = @iddokkoryg
		    UPDATE TR SET flag = (16384 | flag) WHERE id = @idTransOrig
		    EXEC hm_TR_RecalcStatus @idTransOrig
		END
	END
END ELSE BEGIN
	RAISERROR( '***IDENTITY_INSERT*** Próba dodania rekordu o identyfikatorze %ld do tabeli ZO!', 16, 1, @id )
END
IF ('' = ISNULL(@kod,''))
	UPDATE ZO SET kod = '@' + CONVERT(varchar(40), id) FROM ZO WHERE id = @id
IF @bufor = 0 AND @typ in (1, 203) BEGIN
	INSERT INTO HW (iddk, typdk) values (@id,45)
END
return @id


GO

CREATE OR ALTER PROCEDURE HM.stdhm_update_dk
@id int, @flag smallint, @aktywny smallint, @subtyp smallint, @typ smallint,
@znacznik smallint, @rodzaj int, @katalog int, @info smallint, @wystawil int,
@kod varchar(41), @seria varchar(6), @serianr int, @okres smallint, @seriadzial int,
@nazwa varchar(41), @data datetime, @datasp datetime, @datawplywu datetime, @opis varchar(81),
@khid int, @khadid int, @odid int, @odadid int,
@ok smallint, @rr smallint, @wplaty float, @rabat float, @plattyp int, @plattermin datetime,
@netto float, @vat float, @odebrane varchar(41), @typ_dk varchar(4), @grupacen smallint,
@rejestrVat int, @rejestrVat2 int, @iddokkoryg int, @wartoscSp float, @exp_fk smallint,
@waluta varchar(4), @kurs float, @zyskdod float, @paragon int, @kod_obcy varchar(256),
@magazyn int, @rozlmg smallint, @formaplatn int, @schemat varchar(5), @datarej datetime, @datarej2 datetime,
@bufor smallint, @super int, @anulowany smallint, @createdBy int, @createdDate datetime, @modifiedBy int,
@modifiedDate datetime, @walNetto float, @walBrutto float, @wartPrzychod float, @kursDoch float,
@eFaktura smallint, @statusRDF smallint, @guid varchar(38), @data_obcy datetime, @koszt float, @kosztAproksymowany float, 
@statusFK smallint, @typceny smallint, @jpk smallint, @importKasa bit, @splitPayment bit, @forceSP bit, @checkWL bit, @accountId int,
@jpk_v7 varchar(256), @importKasaZbiorczy bit, @IdPeriodicSaleReport int, @fiscalDeviceNo varchar(40), @zeroVatRate bit,
@statusKsef int, @dataWystawieniaKsef datetime, @guidEarch varchar(38), @numberKsef varchar(38),
@procedura_OSS bit, @krajDostawy_OSS int, @krajWysylki_OSS int, @idSOD varchar(38), @sodSource smallint, @typeExternal int, @idExternal int, @id2External varchar(32)
AS
    IF(@bufor = 1 AND (SELECT bufor FROM HM.DK WHERE id = @id) = 0)
    BEGIN   --błąd!, nie można cofnąć wystawionego dokumentu do bufora
		DECLARE @msg varchar(2048) = CONCAT('Nie można cofnąć wystawionego dokumentu do bufora: ', @kod);
		THROW 51010, @msg, 1;  
    END

	DECLARE @oldDataSp DATETIME	
	DECLARE @oldDat DATETIME
	DECLARE @oldROId INT

	SELECT @oldDat = datasp FROM DK WHERE @id = id
	SELECT @oldROId = idPeriodicSaleReport FROM DK WHERE @id = id

	SELECT @guid = REPLACE( REPLACE( @guid, '{', '' ), '}', '' ) 
	IF @guid NOT LIKE(	REPLICATE( '[0-9A-Fa-f]', 8 ) + '-' + 
						REPLICATE( '[0-9A-Fa-f]', 4 ) + '-' + 
						REPLICATE( '[0-9A-Fa-f]', 4 ) + '-' + 
						REPLICATE( '[0-9A-Fa-f]', 4 ) + '-' + 
						REPLICATE( '[0-9A-Fa-f]', 12 )
					 )
	BEGIN
		-- Ignore invalid guid
		SELECT @guid = guid FROM DK WHERE id = @id
	END

	SELECT @guidEarch = REPLACE( REPLACE( @guidEarch, '{', '' ), '}', '' ) 
	IF @guidEarch NOT LIKE(	REPLICATE( '[0-9A-Fa-f]', 8 ) + '-' + 
						REPLICATE( '[0-9A-Fa-f]', 4 ) + '-' + 
						REPLICATE( '[0-9A-Fa-f]', 4 ) + '-' + 
						REPLICATE( '[0-9A-Fa-f]', 4 ) + '-' + 
						REPLICATE( '[0-9A-Fa-f]', 12 )
					 )
	BEGIN
		-- Ignore invalid guid
		SELECT @guidEarch = guidEarch FROM DK WHERE id = @id
	END

	IF (@subtyp IN (134, 135, 150, 151))
	BEGIN
		SELECT @oldDataSp = datasp FROM DK WHERE @id = id
	END	

	DECLARE @dataZest1 datetime
	SELECT @dataZest1 = CASE WHEN @subtyp IN (134, 135, 150, 151) THEN @datasp ELSE @data END

	SET @modifiedDate = GETDATE()
	UPDATE DK
	SET flag = @flag, aktywny = @aktywny, subtyp = @subtyp, typ = @typ, znacznik = @znacznik,
	rodzaj = @rodzaj, katalog = @katalog, info = @info, wystawil = @wystawil, kod = @kod,
	seria = @seria, serianr = @serianr, okres = @okres, seriadzial = @seriadzial, nazwa = @nazwa,
	data = @data, datasp = @datasp, datawplywu = @datawplywu, dataZest1 = @dataZest1, opis = @opis,
	khid = @khid, khadid = @khadid, odid = @odid, odadid = @odadid, ok = @ok, rr = @rr,
	wplaty = @wplaty, rabat = @rabat, plattyp = @plattyp, plattermin = @plattermin, netto = @netto,
	vat = @vat, odebrane = @odebrane, typ_dk = @typ_dk, grupacen = @grupacen, rejestrVat = @rejestrVat,
	rejestrVat2 = @rejestrVat2, iddokkoryg = @iddokkoryg, wartoscSp = @wartoscSp,
	exp_fk = @exp_fk, waluta = @waluta, kurs = @kurs, zyskdod = @zyskdod, paragon = @paragon,
	kod_obcy = @kod_obcy, magazyn = @magazyn, rozlmg = @rozlmg, formaplatn = @formaplatn, schemat = @schemat,
	datarej = @datarej, datarej2 = @datarej2, bufor = @bufor, super = @super, anulowany = @anulowany,
	modifiedBy = @modifiedBy, modifiedDate = @modifiedDate,
	walNetto = @walNetto, walBrutto = @walBrutto, wartPrzychod = @wartPrzychod, kursDoch = @kursDoch,
	eFaktura = @eFaktura, statusRDF = @statusRDF, guid = @guid, data_obcy = @data_obcy, koszt = @koszt, 
	kosztAproksymowany = @kosztAproksymowany, statusFK = @statusFK, typceny = @typceny, jpk = @jpk, importKasa = @importKasa, 
	splitPayment = @splitPayment, forceSP = @forceSP, checkWL = @checkWL, accountId = @accountId,
	jpk_v7 = @jpk_v7, importKasaZbiorczy = @importKasaZbiorczy, IdPeriodicSaleReport = @IdPeriodicSaleReport, fiscalDeviceNo = @fiscalDeviceNo, zeroVatRate = @zeroVatRate,
	statusKsef = @statusKsef, dataWystawieniaKsef = @dataWystawieniaKsef, guidEarch = @guidEarch, numberKsef = @numberKsef,
	procedura_OSS = @procedura_OSS, krajDostawy_OSS = @krajDostawy_OSS, krajWysylki_OSS = @krajWysylki_OSS, idSOD = @idSOD, sodSource = @sodSource, typeExternal = @typeExternal, idExternal = @idExternal, id2External = @id2External
	WHERE @id = id
	IF ((0 = @bufor) AND (0 = (0x0100 & @flag)))
	BEGIN
		EXEC hm_DP_PrzeliczZyskKoszt @id, 0
	END
	IF @bufor = 0 AND @typ IN (0, 202) AND NOT EXISTS (SELECT * FROM HW WHERE iddk = @id and typdk = 16) BEGIN
		INSERT INTO HW (iddk, typdk) values (@id,16)
	END
	IF ((0 = @bufor) AND (@subtyp IN (135, 136, 137, 151, 152, 153)))
	BEGIN
		DECLARE @idKontraktu INT
		EXEC @idKontraktu = hm_ZZ_GetIdKontraktu @id, 16
		EXEC hm_DK_SetKontraktRozl @idKontraktu
	END

	IF ((0 = @bufor) AND (ISNULL(@datasp, '9990-12-31') <> ISNULL(@oldDataSp, '9990-12-31')))

	BEGIN
		IF (@subtyp IN (134, 150))
		BEGIN
			UPDATE DK
			SET datasp = @datasp, dataZest1 = @datasp
			WHERE iddokkoryg = @id
			INSERT INTO LOCAL_LOG (baseName, rec_id, operation, term) SELECT 'DK', DK.id, 3, @@SPID FROM DK WHERE idDokKoryg = @id
		END
		IF (@subtyp IN (135, 151))
		BEGIN
			UPDATE DK
			SET datasp = @datasp, dataZest1 = @datasp
			WHERE iddokkoryg = @iddokkoryg AND id > @id
			INSERT INTO LOCAL_LOG (baseName, rec_id, operation, term) SELECT 'DK', DK.id, 3, @@SPID FROM DK WHERE iddokkoryg = @iddokkoryg AND id > @id
		END
	END
	IF @typ = 110
	BEGIN
		UPDATE SalesKinds SET kod = @kod WHERE KindId = @id
	END

	--updateRO
	DECLARE @oldDkRo VARCHAR(30)
	
	IF EXISTS (SELECT Code FROM HM.PeriodicSaleReports WHERE id = (SELECT IdPeriodicSaleReport FROM HM.DK WHERE id = @id))
		SELECT @oldDkRo = Code FROM HM.PeriodicSaleReports WHERE id = (SELECT IdPeriodicSaleReport FROM HM.DK WHERE id = @id)
	ELSE 
		SET @oldDkRo = 'brak'

	DECLARE @newDkRo VARCHAR(30)
	exec HM.getRoCode @id, @datasp, @newDkRo OUTPUT

	IF(@newDkRo <> 'brak') -- czy dokument bedzie ro
	BEGIN
		IF(@oldDkRo = 'brak') 
		BEGIN
			IF ((@exp_fk & 0x01) = 0) EXEC hm.IsDocumentAsPeriodicSaleReport @DKid = @id
		END
		ELSE
		BEGIN
			IF(@oldDkRo <> @newDkRo)
			BEGIN
				
				UPDATE HM.DK SET IdPeriodicSaleReport = NULL WHERE id  = @id
				IF ((SELECT COUNT(id) FROM hm.DK WHERE IdPeriodicSaleReport = @IdPeriodicSaleReport) = 0)
				BEGIN
					DELETE FROM hm.PeriodicSaleReports WHERE Id = @IdPeriodicSaleReport
					INSERT INTO LOCAL_LOG (baseName, rec_id,operation,term) VALUES ('PeriodicSaleReports', @IdPeriodicSaleReport, 4, @@SPID)
				END
				ELSE
					EXEC hm.RecalculatePeriodicSaleReport NULL, @IdPeriodicSaleReport

				EXEC hm.IsDocumentAsPeriodicSaleReport @DKid = @id
			END
			ELSE
				EXEC hm.IsDocumentAsPeriodicSaleReport @DKid = @id
		END		
	END
	ELSE
	BEGIN 		
		UPDATE HM.DK SET IdPeriodicSaleReport = NULL WHERE id  = @id
		IF ((SELECT COUNT(id) FROM hm.DK WHERE IdPeriodicSaleReport = @IdPeriodicSaleReport) = 0)
		BEGIN
			DELETE FROM hm.PeriodicSaleReports WHERE Id = @IdPeriodicSaleReport
			INSERT INTO LOCAL_LOG (baseName, rec_id,operation,term) VALUES ('PeriodicSaleReports', @IdPeriodicSaleReport, 4, @@SPID)
		END
		ELSE
			EXEC hm.RecalculatePeriodicSaleReport NULL, @IdPeriodicSaleReport
	END

GO

CREATE OR ALTER PROCEDURE HM.stdhm_update_zo
@id int, @flag smallint, @aktywny smallint, @subtyp smallint, @typ smallint,
@znacznik smallint, @rodzaj int, @katalog int, @info smallint, @wystawil int,
@kod varchar(41), @seria varchar(6), @serianr int, @okres smallint, @seriadzial int,
@nazwa varchar(41), @data datetime, @datasp datetime, @datawplywu datetime, @opis varchar(81),
@khid int, @khadid int, @odid int, @odadid int,
@ok smallint, @rr smallint, @wplaty float, @rabat float, @plattyp int, @plattermin datetime,
@netto float, @vat float, @odebrane varchar(41), @typ_dk varchar(4), @grupacen smallint,
@iddokkoryg int, @wartoscSp float, @exp_fk smallint, @waluta varchar(4),
@kurs float, @zyskdod float, @paragon int, @kod_obcy varchar(41), @magazyn int,
@rozlmg smallint, @formaplatn int, @schemat varchar(5), @datarej datetime, @bufor smallint,
@super int, @anulowany smallint, @createdBy int, @createdDate datetime, @modifiedBy int, @modifiedDate datetime, @walNetto float, @walBrutto float,
@statusRDF smallint, @guid varchar(38), @typceny SMALLINT, @splitPay bit, @zeroVatRate bit, @advanceInvoiceDeclared bit, @typeExternal int, @idExternal int, @id2External varchar(32)
AS
	
SELECT @guid = REPLACE( REPLACE( @guid, '{', '' ), '}', '' ) 
IF @guid NOT LIKE(	REPLICATE( '[0-9A-Fa-f]', 8 ) + '-' + 
					REPLICATE( '[0-9A-Fa-f]', 4 ) + '-' + 
					REPLICATE( '[0-9A-Fa-f]', 4 ) + '-' + 
					REPLICATE( '[0-9A-Fa-f]', 4 ) + '-' + 
					REPLICATE( '[0-9A-Fa-f]', 12 )
				 )
BEGIN
	-- Ignore invalid guid
	SELECT @guid = guid FROM ZO WHERE id = @id
END
	
SET @modifiedDate = GETDATE()
UPDATE ZO
SET flag = @flag, aktywny = @aktywny, subtyp = @subtyp, typ = @typ, znacznik = @znacznik,
rodzaj = @rodzaj, katalog = @katalog, info = @info, wystawil = @wystawil, kod = @kod,
seria = @seria, serianr = @serianr, okres = @okres, seriadzial = @seriadzial, nazwa = @nazwa,
data = @data, datasp = @datasp, datawplywu=@datawplywu, opis = @opis, khid = @khid,
khadid = @khadid, odid = @odid, odadid = @odadid, ok = @ok, rr = @rr,
wplaty = @wplaty, rabat = @rabat, plattyp = @plattyp, plattermin = @plattermin, netto = @netto,
vat = @vat, odebrane = @odebrane, typ_dk = @typ_dk, grupacen = @grupacen, iddokkoryg = @iddokkoryg,
wartoscSp = @wartoscSp, exp_fk = @exp_fk, waluta = @waluta, kurs = @kurs,
zyskdod = @zyskdod, paragon = @paragon, kod_obcy = @kod_obcy, magazyn = @magazyn, rozlmg = @rozlmg,
formaplatn = @formaplatn, schemat = @schemat, datarej = @datarej, bufor = @bufor, super = @super, anulowany = @anulowany,
modifiedBy = @modifiedBy, modifiedDate = @modifiedDate, walNetto = @walNetto, walBrutto = @walBrutto,
statusRDF = @statusRDF, guid = @guid, typceny = @typceny, splitPayment=@splitPay, zeroVatRate=@zeroVatRate, advanceInvoiceDeclared = @advanceInvoiceDeclared, typeExternal = @typeExternal, idExternal = @idExternal, id2External = @id2External
WHERE @id = id
IF @bufor = 0 AND @typ in (1, 203) AND NOT EXISTS (SELECT * FROM HW WHERE iddk = @id and typdk = 45) BEGIN
	INSERT INTO HW (iddk, typdk) values (@id,45)
END
IF @typ = 110
	BEGIN
		UPDATE ForeignOrdersKinds SET kod = @kod WHERE KindId = @id
	END 
IF @typ = 111
	BEGIN
		UPDATE OwnOrdersKinds SET kod = @kod WHERE KindId = @id
	END 

GO

CREATE OR ALTER PROCEDURE [HM].[UpdateForeignOrderDocumentFromXML] (@docData XML, @userId int, @moduleCode nvarchar(255))
AS
BEGIN
	SET NOCOUNT ON; -- Stops the message that shows the count of the number of rows affected by a Transact-SQL statement or stored procedure from being returned as part of the result set.
	SET XACT_ABORT ON; -- Specifies whether SQL Server automatically rolls back the current transaction when a Transact-SQL statement raises a run-time error.
	
	BEGIN TRY
		BEGIN TRANSACTION;
			DECLARE @allDataWithChangeStatus BIT = 0 -- wartość 1 oznacza że korzystamy z ModoficationStatus z XML

			-- Tabela tymczasowa na dane dokumentu sprzedażowego
			declare @tblForeignOrder table
			(
				[Id] INT,
				[Guid] UNIQUEIDENTIFIER,
				[DocumentNumber] VARCHAR(40) COLLATE database_default,
				[Name] VARCHAR(40) COLLATE database_default,
				[IssueDate] DATETIME,
				[OperationDate] DATETIME,
				[Description] VARCHAR(80) COLLATE database_default,
				[CorrectionCause] VARCHAR(80) COLLATE database_default,
				[RecipientPerson] VARCHAR(40) COLLATE database_default,
				[BaseType] SMALLINT,
				[DocumentTypeShortcut] VARCHAR(4) COLLATE database_default,
				[DocumentNumerationShortcut] VARCHAR(5) COLLATE database_default,
				[SeriesNo] INT,
				[KindId] INT,
				[CatalogId] INT,
				[Marker] SMALLINT,
				[IsIssued] BIT,
				[DocumentStatus] SMALLINT,
				[DocumentStatusDescription] VARCHAR(40) COLLATE database_default,
				[HmImported] BIT,
				[Archived] BIT,
				[FkExported] BIT,
				[IsCreatedFromForeignOrder] BIT,
				[Canceled] SMALLINT,
				[IsActive] BIT,
				[MigrationStatus] SMALLINT,
				[ContractorId] INT,
				[AccountingPattern] VARCHAR(4) COLLATE database_default,
				[IssuerId] INT,
				[ReceiverId] INT,
				[DepartmentId] INT,
				[PayRegistryId] INT,
				[PaymentFormClientId] INT,
				[PaymentDeadline] DATETIME,
				[Value] DECIMAL(24,2),
				[ValueVat] DECIMAL(24,2),
				[ValueGross] DECIMAL(24,2),
				[ValuePLN] DECIMAL(24,2),
				[ValueVatPLN] DECIMAL(24,2),
				[ValueGrossPLN] DECIMAL(24,2),
				[PriceKind] SMALLINT,
				[Currency] VARCHAR(3) COLLATE database_default,	
				[ExchangeRate] DECIMAL(24,8),
				[CalculateVatMethod] SMALLINT,
				[InventorySettlementStatus] INT,
				[PriceTypeId] INT,
				[Discount] DECIMAL(24,2),
				[ReservationType] SMALLINT,
				[Receipt] INT,
				[SplitPayment] BIT,
				[InRegisterDate] DATETIME,
				[SaleRegister] INT,
				[RegisterMethod] SMALLINT,
				[CashSettled] SMALLINT,
				[DepartmentInSeries] INTEGER,
				[Period] SMALLINT,
				[CorrectedDocumentId] INTEGER,
				[UseAdvanceInvoice] BIT,
				[UseAdvanceInvoiceIsReadOnly] BIT,
                [TypeExternal] INT,
                [IdExternal] VARCHAR(11) COLLATE database_default,
                [Id2External] VARCHAR(32) COLLATE database_default
			);
			-- Tabela tymczasowa na dane pozycji dokumentu sprzedażowego
			declare @tblDocumentPositions table
			(
				[Id] INT,
				[Compensation] SMALLINT,
				[NodeId] INT,
				[ParentId] INT,
				[OrdinalNumber] SMALLINT,
				[Guid] UNIQUEIDENTIFIER,
				[ProductId] INT,
				[ProductShortcut] VARCHAR(40) COLLATE database_default,
				[Description] VARCHAR(max) COLLATE database_default,
				[UnitOfMeasurement] VARCHAR(8) COLLATE database_default,
				[EvidenceUnitOfMeasurement] VARCHAR(8) COLLATE database_default,
				[Quantity] DECIMAL(24,4),
				[UnitQuantity] DECIMAL(35,15),
				[EvidenceQuantity] DECIMAL(24,4),
				[ReservationType] SMALLINT,
				[AccountingPattern] VARCHAR(4) COLLATE database_default,
				[VatRateId] INT,
				[Price] DECIMAL(35,15),
				[PriceGross] DECIMAL(35,15),
				[Value] DECIMAL(24,2),
				[ValueVat] DECIMAL(24,2),
				[ValueGross] DECIMAL(24,2),
				[ValuePLN] DECIMAL(24,2),
				[ValueVatPLN] DECIMAL(24,2),
				[ValueGrossPLN] DECIMAL(24,2),
				[PriceTypeId] SMALLINT,
				[PriceListPrice] DECIMAL(35,15),
				[PriceListPriceGross] DECIMAL(35,15),
				[CodePKWIU] VARCHAR(20) COLLATE database_default,
				[VatRegister] INT,
				CorrectedPositionId INT,
				[IsNewCorrectPosition] BIT,
				[IsSet] BIT,
				[IsElementOfSet] BIT,
				[NoEditOfSet] BIT,
				[PriceOfSetCalculatedBasedOfElements] BIT,
				[VatUE] BIT,
				[ServiceExport] BIT,
				[DomesticVatRateId] INT,
				[OriginalPriceListPrice] DECIMAL(35,15),
				[OriginalPriceListPriceCurrency] VARCHAR(3) COLLATE database_default,
				[OriginalBasePrice] DECIMAL(35,15),
				[OriginalBasePriceCurrency] VARCHAR(3) COLLATE database_default,
				[BasePricePLN] DECIMAL(35,15),
				[OO] BIT,
				[ServiceExpense] DECIMAL(35,15),
				[IsService] BIT,
				[ModificationStatus] SMALLINT
			)
			
			-- Przygotowanie xml
			DECLARE @handle INT, @prepareXmlStatus INT
			EXEC @prepareXmlStatus = sp_xml_preparedocument @handle OUTPUT, @docData
			
			-- Deserializacja danych dokumentu sprzedażowego z xml
			INSERT INTO @tblForeignOrder
			SELECT 
					[Id],
					CASE WHEN [Guid] IS NULL OR [Guid] = '' OR [Guid] = '00000000-0000-0000-0000-000000000000' THEN NEWID() ELSE [GUID] END,
					ISNULL(IIF([DocumentNumber] = '', NULL, [DocumentNumber]), NEWID()),
					[Name],
					HM.CastToDateOrNull([IssueDate]),
					HM.CastToDateOrNull([OperationDate]),
					[Description],
					[CorrectionCause],
					[RecipientPerson],
					[BaseType],
					[DocumentTypeShortcut],
					[DocumentNumerationShortcut],
					[SeriesNo],
					[KindId],
					[CatalogId],
					[Marker],
					[IsIssued],
					[DocumentStatus],
					[DocumentStatusDescription],
					[HmImported],
					[Archived],
					[FkExported],
					[IsCreatedFromForeignOrder],
					[Canceled],
					[IsActive],
					[MigrationStatus],
					[ContractorId],
					IsNull([AccountingPattern], ''),
					[IssuerId],
					[ReceiverId],
					[DepartmentId],
					IIF([PayRegistryId] = -1, NULL, [PayRegistryId]),
					[PaymentFormClientId],
					HM.CastToDateOrNull([PaymentDeadline]),
					[Value],
					[ValueVat],
					[ValueGross],
					[ValuePLN],
					[ValueVatPLN],
					[ValueGrossPLN],
					[PriceKind],
					[Currency],	
					--domyślnie ustawiamy 1 - wsteczna kompatybilność
					CASE WHEN 0 = ISNUMERIC([ExchangeRate]) THEN 1 ELSE CAST([ExchangeRate] AS DECIMAL(24,8)) END,
					[CalculateVatMethod],
					[InventorySettlementStatus],
					[PriceTypeId],
					[Discount],
					[ReservationType],
					[Receipt],
					[SplitPayment],
					HM.CastToDateOrNull([InRegisterDate]),
					[SaleRegister],
					[RegisterMethod],
					[CashSettled],
					[DepartmentInSeries],
					[Period],
					IIF([CorrectedDocumentId] = 0, NULL, [CorrectedDocumentId]),
					UseAdvanceInvoice,
					UseAdvanceInvoiceIsReadOnly,
                    [TypeExternal],
                    CASE WHEN [IdExternal] = '' THEN NULL ELSE CAST([IdExternal] AS INT) END,
                    CASE WHEN [Id2External] = '' THEN NULL ELSE [Id2External] END
			FROM 
				OPENXML(@handle, '/ForeignOrderDocument')
				WITH
				(
					[Id] INT 'Id',
					[Guid] NVARCHAR(36) 'Guid',
					[DocumentNumber] VARCHAR(40) 'DocumentNumber',
					[Name] VARCHAR(40) 'Name',
					[IssueDate] NVARCHAR(100) 'IssueDate',
					[OperationDate] NVARCHAR(100) 'OperationDate',
					[Description] VARCHAR(80) 'Description',
					[CorrectionCause] VARCHAR(80) 'CorrectionCause',
					[RecipientPerson] VARCHAR(40) 'RecipientPerson',
					[BaseType] SMALLINT 'BaseType',
					[DocumentTypeShortcut] VARCHAR(4) 'DocumentTypeShortcut',
					[DocumentNumerationShortcut] VARCHAR(5) 'DocumentNumerationShortcut',
					[SeriesNo] INT 'SeriesNo',
					[KindId] INT 'KindId',
					[CatalogId] INT 'CatalogId',
					[Marker] SMALLINT 'Marker',
					[IsIssued] BIT 'IsIssued',
					[DocumentStatus] SMALLINT 'DocumentStatus',
					[DocumentStatusDescription] VARCHAR(40) 'DocumentStatusDescription',
					[HmImported] BIT 'HmImported',
					[Archived] BIT 'Archived',
					[FkExported] BIT 'FkExported',
					[IsCreatedFromForeignOrder] BIT 'IsCreatedFromForeignOrder',
					[Canceled] BIT 'Canceled',
					[IsActive] BIT 'IsActive',
					[MigrationStatus] SMALLINT 'MigrationStatus',
					[ContractorId] INT 'ContractorId',
					[AccountingPattern] VARCHAR(4) 'AccountingPattern',
					[IssuerId] INT 'IssuerId',
					[ReceiverId] INT 'ReceiverId',
					[DepartmentId] INT 'DepartmentId',
					[PayRegistryId] INT 'PayRegistryId',
					[PaymentFormClientId] INT 'PaymentFormClientId',
					[PaymentDeadline] NVARCHAR(100) 'PaymentDeadline',
					[Value] DECIMAL(24,2) 'Value',
					[ValueVat] DECIMAL(24,2) 'ValueVat',
					[ValueGross] DECIMAL(24,2) 'ValueGross',
					[ValuePLN] DECIMAL(24,2) 'ValuePLN',
					[ValueVatPLN] DECIMAL(24,2) 'ValueVatPLN',
					[ValueGrossPLN] DECIMAL(24,2) 'ValueGrossPLN',
					[PriceKind] SMALLINT 'PriceKind',
					[Currency] VARCHAR(3) 'Currency',	
					[ExchangeRate] NVARCHAR(20) 'ExchangeRate',
					[CalculateVatMethod] SMALLINT 'CalculateVatMethod',
					[InventorySettlementStatus] INT 'InventorySettlementStatus',
					[PriceTypeId] INT 'PriceTypeId',
					[Discount] DECIMAL(24,2) 'Discount',
					[ReservationType] SMALLINT 'ReservationType',				
					[Receipt] INT 'Receipt',
					[SplitPayment] BIT 'SplitPayment',
					[InRegisterDate] NVARCHAR(100) 'InRegisterDate',
					[SaleRegister] INT 'SaleRegister',
					[RegisterMethod] SMALLINT 'RegisterMethod',
					[CashSettled] SMALLINT 'CashSettled',
					[DepartmentInSeries] INTEGER 'DepartmentInSeries',
					[Period] SMALLINT 'Period',
					[CorrectedDocumentId] INT 'CorrectedDocumentId',
					UseAdvanceInvoice BIT 'UseAdvanceInvoice',
					UseAdvanceInvoiceIsReadOnly BIT 'UseAdvanceInvoiceIsReadOnly',
                    [TypeExternal] INT 'TypeExternal',
                    [IdExternal] INT 'IdExternal',
                    [Id2External] VARCHAR(32) 'Id2External'
				)	

			-- Pobranie danych z główki dokumentu
			DECLARE @docId INT,
					@docNumber VARCHAR(40),
					@department INT,
					@issueDate DATETIME,
					@saleRegister INT,
					@foreignOrder BIT,
					@docIdCorrected INT,
					@isIssued BIT

			SET @docIdCorrected = (SELECT iddokkoryg from HM.ZO WHERE id = @docId);

			SELECT 
				@docId = [Id], 
				@docNumber = [DocumentNumber], 
				@department = [DepartmentId], 
				@issueDate = [IssueDate], 
				@saleRegister = [SaleRegister], 
				@foreignOrder = [IsCreatedFromForeignOrder],
				@isIssued = IsIssued
			FROM @tblForeignOrder

			IF @isIssued = 1
			BEGIN				
				UPDATE zo SET
					zo.advanceInvoiceDeclared = CASE WHEN tblSD.UseAdvanceInvoiceIsReadOnly = 1 THEN zo.advanceInvoiceDeclared ELSE tblSD.UseAdvanceInvoice END
				FROM [HM].[ZO] zo
				INNER JOIN @tblForeignOrder tblSD on tblSD.Id = zo.id
				WHERE zo.advanceInvoiceDeclared != CASE WHEN tblSD.UseAdvanceInvoiceIsReadOnly = 1 THEN zo.advanceInvoiceDeclared ELSE tblSD.UseAdvanceInvoice END

				GOTO CLEAN_AND_FINISH;
			END

			DECLARE @correction BIT
			SELECT @correction = COUNT(Id) FROM @tblForeignOrder WHERE ISNULL(CorrectedDocumentId, 0) > 0
				
			-- dokument i pozycje w cenie netto / brutto
			DECLARE @IsNet SMALLINT
			SELECT @IsNet = CASE WHEN [PriceKind] = 2 THEN 1 ELSE 0 END FROM @tblForeignOrder

			DECLARE @ExchangeRate FLOAT
			SELECT @ExchangeRate = [ExchangeRate] FROM @tblForeignOrder

			DECLARE @created SMALLINT = 1, @updated SMALLINT = 2, @deleted SMALLINT = 3

			-- Deserializacja danych pozycji dokumentu sprzedażowego z xml
			INSERT INTO @tblDocumentPositions
			SELECT 
					[Id],		
					IsNull([Compensation], 0),
					[NodeId],
					[ParentId],
					[OrdinalNumber],
					CASE WHEN [Guid] IS NULL OR [Guid] = '' OR [Guid] = '00000000-0000-0000-0000-000000000000' THEN NEWID() ELSE [GUID] END,
					[ProductId],
					[ProductShortcut],
					[Description],
					[UnitOfMeasurement],
					[EvidenceUnitOfMeasurement],
					CASE WHEN 0 = ISNUMERIC([Quantity]) THEN 0.0000 ELSE CAST([Quantity] AS DECIMAL(24, 4)) END AS [Quantity],
					CASE WHEN 0 = ISNUMERIC([UnitQuantity]) THEN 0.0000 ELSE CAST([UnitQuantity] AS DECIMAL(35, 15)) END AS [UnitQuantity],
					CASE WHEN 0 = ISNUMERIC([EvidenceQuantity]) THEN 0.0000 ELSE CAST([EvidenceQuantity] AS DECIMAL(24, 4)) END AS [EvidenceQuantity],
					[ReservationType],
					IsNull([AccountingPattern], ''),
					[VatRateId],
					CASE WHEN 0 = ISNUMERIC([Price]) THEN 0.0000 ELSE CAST([Price] AS DECIMAL(35,15)) END AS [Price],
					CASE WHEN 0 = ISNUMERIC([PriceGross]) THEN 0.0000 ELSE CAST([PriceGross] AS DECIMAL(35,15)) END AS [PriceGross],
					CASE WHEN 0 = ISNUMERIC([Value]) THEN 0.00 ELSE CAST([Value] AS DECIMAL(24, 2)) END AS [Value],
					CASE WHEN 0 = ISNUMERIC([ValueVat]) THEN 0.00 ELSE CAST([ValueVat] AS DECIMAL(24, 2)) END AS [ValueVat],
					CASE WHEN 0 = ISNUMERIC([ValueGross]) THEN 0.00 ELSE CAST([ValueGross] AS DECIMAL(24, 2)) END AS [ValueGross],
					CASE WHEN 0 = ISNUMERIC([ValuePLN]) THEN 0.00 ELSE CAST([ValuePLN] AS DECIMAL(24, 2)) END AS [ValuePLN],
					CASE WHEN 0 = ISNUMERIC([ValueVatPLN]) THEN 0.00 ELSE CAST([ValueVatPLN] AS DECIMAL(24, 2)) END AS [ValueVatPLN],
					CASE WHEN 0 = ISNUMERIC([ValueGrossPLN]) THEN 0.00 ELSE CAST([ValueGrossPLN] AS DECIMAL(24, 2)) END AS [ValueGrossPLN],
					[PriceTypeId],
					CASE WHEN 0 = ISNUMERIC([PriceListPrice]) THEN 0.0000 ELSE CAST([PriceListPrice] AS DECIMAL(35,15)) END AS [PriceListPrice],
					CASE WHEN 0 = ISNUMERIC([PriceListPriceGross]) THEN 0.0000 ELSE CAST([PriceListPriceGross] AS DECIMAL(35,15)) END AS [PriceListPriceGross],
					[CodePKWIU],
					[VatRegister],
					IIF(CorrectedPositionId = 0, NULL, CorrectedPositionId),
					[IsNewCorrectPosition],
					[IsSet],
					[IsElementofSet],
					[NoEditOfSet],
					[PriceOfSetCalculatedBasedOfElements],
					[VatUE],
					[ServiceExport],
					[DomesticVatRateId],
					CASE WHEN 0 = ISNUMERIC([OriginalPriceListPrice]) THEN 0.0000 ELSE CAST([OriginalPriceListPrice] AS DECIMAL(35,15)) END AS [OriginalPriceListPrice],
					[OriginalPriceListPriceCurrency],
					CASE WHEN 0 = ISNUMERIC([OriginalBasePrice]) THEN 0.0000 ELSE CAST([OriginalBasePrice] AS DECIMAL(35,15)) END AS [OriginalBasePrice],
					[OriginalBasePriceCurrency],
					CASE WHEN 0 = ISNUMERIC([BasePricePLN]) THEN 0.0000 ELSE CAST([BasePricePLN] AS DECIMAL(35,15)) END AS [BasePricePLN],
					[OO],
					CASE WHEN 0 = ISNUMERIC([ServiceExpense]) THEN 0.0000 ELSE CAST([ServiceExpense] AS DECIMAL(35,15)) END AS [ServiceExpense],
					[IsService],
					CASE @allDataWithChangeStatus
						WHEN 1 THEN
							CASE [ModificationStatus] 
								WHEN 'Created' THEN @created 
								WHEN 'Updated' THEN @updated
								WHEN 'Deleted' THEN @deleted
								ELSE @updated	--brak atrybutu (NULL) na razie traktujemy jako zmodyfikowane
							END
						WHEN 0 THEN
							IIF(Id = 0, @created, @updated)
					END
			FROM 
				OPENXML(@handle, '/ForeignOrderDocument/DocumentPositions/ForeignOrderPosition')
				WITH
				(
					[Id] INT 'Id',	
					[Compensation] SMALLINT 'Compensation',
					[NodeId] INT 'NodeId',
					[ParentId] INT 'ParentId',
					[OrdinalNumber] SMALLINT 'OrdinalNumber',
					[Guid] NVARCHAR(36) 'Guid',
					[ProductId] INT 'ProductId',
					[ProductShortcut] VARCHAR(40) 'ProductShortcut',
					[Description] VARCHAR(max) 'Description',
					[UnitOfMeasurement] VARCHAR(8) 'UnitOfMeasurement',
					[EvidenceUnitOfMeasurement] VARCHAR(8) 'EvidenceUnitOfMeasurement',
					[Quantity] VARCHAR(24) 'Quantity',
					[UnitQuantity] VARCHAR(36) 'UnitQuantity',
					[EvidenceQuantity] VARCHAR(24) 'EvidenceQuantity',
					[ReservationType] SMALLINT 'ReservationType',
					[AccountingPattern] VARCHAR(4) 'AccountingPattern',
					[VatRateId] INT 'VatRateId',
					[Price] VARCHAR(36) 'Price',
					[PriceGross] VARCHAR(36) 'PriceGross',
					[Value] VARCHAR(24) 'Value',
					[ValueVat] VARCHAR(24) 'ValueVat',
					[ValueGross] VARCHAR(24) 'ValueGross',
					[ValuePLN] VARCHAR(24) 'ValuePLN',
					[ValueVatPLN] VARCHAR(24) 'ValueVatPLN',
					[ValueGrossPLN] VARCHAR(24) 'ValueGrossPLN',
					[PriceTypeId] SMALLINT 'PriceTypeId',
					[PriceListPrice] VARCHAR(36) 'PriceListPrice',
					[PriceListPriceGross] VARCHAR(36) 'PriceListPriceGross',
					[CodePKWIU] VARCHAR(40) 'CodePKWIU',
					[VatRegister] INT 'VatRegister',
					CorrectedPositionId INT 'CorrectedPositionId',
					[IsNewCorrectPosition] BIT 'IsNewCorrectPosition',
					[IsSet] BIT 'IsSet',
					[IsElementOfSet] BIT 'IsElementOfSet',
					[NoEditOfSet] BIT 'NoEditOfSet',
					[PriceOfSetCalculatedBasedOfElements] BIT 'PriceOfSetCalculatedBasedOfElements',
					[VatUE] BIT 'VatUE',
					[ServiceExport] BIT 'ServiceExport',
					[DomesticVatRateId] INT 'DomesticVatRateId',
					[OriginalPriceListPrice] VARCHAR(36) 'OriginalPriceListPrice',
					[OriginalPriceListPriceCurrency] VARCHAR(3) 'OriginalPriceListPriceCurrency',
					[OriginalBasePrice] VARCHAR(35) 'OriginalBasePrice',
					[OriginalBasePriceCurrency] VARCHAR(3) 'OriginalBasePriceCurrency',
					[BasePricePLN] VARCHAR(35) 'BasePricePLN',
					[OO] BIT 'OO',
					[ServiceExpense] VARCHAR(24) 'ServiceExpense',
					[IsService] BIT 'IsService',
					[ModificationStatus] VARCHAR(30) '@ModificationStatus'
				)		
				WHERE @allDataWithChangeStatus = 1	--na razie bierzemy wszystkie pozycje, docelowo te bez statusu powinny być pomijane ( AND [ModificationStatus] IS NOT NULL)
					OR (@allDataWithChangeStatus = 0 AND ([ModificationStatus] <> 'Deleted' OR [ModificationStatus] IS NULL))  -- w trybie pełnym pomijamy pozycje oznaczone jako 'Deleted'

			IF @correction <> 0
			BEGIN
				-- XML korekty zawiera wszystkie pozycje, takze niekorygowane ktorych dalej nie uwzgledniamy
				DELETE
					FROM @tblDocumentPositions
					WHERE Compensation = 0 AND ISNULL(CorrectedPositionId, 0) = 0 AND IsNewCorrectPosition <> 'true'

				UPDATE @tblDocumentPositions
					SET EvidenceQuantity = -EvidenceQuantity, ValuePLN = -ValuePLN, ValueVatPLN = -ValueVatPLN, Quantity = -Quantity,
						Value = -Value, ValueGross = -ValueGross
					WHERE Compensation <> 0
			END

			-- Pozycje usunięte przechowywane w oddzielnej zmiennej tabelarycznej
			DECLARE @tblDeletedPositions TABLE (Id INT)

			IF @allDataWithChangeStatus = 1
			BEGIN
				INSERT INTO @tblDeletedPositions(Id)
				SELECT Id FROM @tblDocumentPositions WHERE ModificationStatus = @deleted

				DELETE @tblDocumentPositions WHERE ModificationStatus = @deleted
			END
			ELSE
			BEGIN
				INSERT INTO @tblDeletedPositions(Id)
						SELECT ZP.id
						FROM HM.ZP
						WHERE ZP.super = @docId AND ZP.id NOT IN (SELECT id FROM @tblDocumentPositions)
			END


-----------------------------------------------------------------------------------------------------------------------------------------
--część wspólna z dokumentem sprzedaży
				--odczyt parametru pracy
				DECLARE @IsShownOnSaleStatementWorkingParameter BIT = ISNULL((
										SELECT IIF(cena = 1, 1, 0) 
										FROM [HM].ZZ 
										WHERE typ = 32 AND baza1 = 20 AND id1 = 1 AND baza2 = 6 AND id2 = 1184), 1)

				DECLARE @CalculateVatMethod INT = (SELECT [CalculateVatMethod] FROM @tblForeignOrder)

				-- dane dla tabeli pozycji, zgodne z formatem DP
				DECLARE @valuesForDP TABLE (NodeId INT, flag SMALLINT, typ SMALLINT, cenabaztow FLOAT, walcenybaz VARCHAR(3) COLLATE database_default, zyskdod FLOAT, wartstvat SMALLINT, wartTowaru FLOAT)

				INSERT INTO @valuesForDP
				SELECT	position.NodeId, 

						CONVERT(SMALLINT, 
							IIF(@CalculateVatMethod = 1, CAST(0x8000 AS SMALLINT), 0) |		-- sumacyjny vat
							IIF(product.[subtyp] = 0, 0x0200, 0) |			-- pozycja towarowa
							IIF(position.[IsElementOfSet] = 1, 0x4000, 0) |	-- elementu kompletu
							IIF(position.[IsSet] = 1, 0x1000, 0) |			-- nagłówek kompletu
							IIF(position.[NoEditOfSet] = 1, 0x0800, 0)		-- nagłówek lub element kompletu nieedycyjnego
						) AS [flag],

						IIF(position.VatUE = 1,0x0200,0) | IIF(position.ServiceExport = 1,0x0100,0) |
						CASE --pierszy bit (0x01) mówiący o tym czy pozycja wchodzi na zestawienia czy nie, zależy to od ustawienia w definicji kompletu lub od parametru pracy
							WHEN position.IsSet = 1 THEN IIF(product.Id IS NOT NULL, [HM].fnIsFlagSet(product.flag, 0x2000), @IsShownOnSaleStatementWorkingParameter)
							WHEN position.IsElementOfSet = 1 THEN IIF(prodSetForElement.Id IS NOT NULL, ~[HM].fnIsFlagSet(prodSetForElement.flag, 0x2000), ~@IsShownOnSaleStatementWorkingParameter)
							ELSE 0 
						END	AS [typ],

						CASE 
							WHEN position.IsSet = 1 AND position.PriceOfSetCalculatedBasedOfElements = 1 THEN 0
							ELSE CAST(position.[OriginalBasePrice] AS FLOAT)
						END AS [cenabaztow],

						CASE 
							WHEN position.IsSet = 1 AND position.PriceOfSetCalculatedBasedOfElements = 1 THEN ''
							ELSE position.[OriginalBasePriceCurrency]
						END AS [walcenybaz],

						CASE 
							WHEN position.IsSet = 1 AND position.PriceOfSetCalculatedBasedOfElements = 1 THEN 0
							ELSE CAST(position.[BasePricePLN] AS FLOAT)
						END AS [zyskdod],

						CAST(ISNULL(vatRate.wartosc, 0) * 10000 AS SMALLINT) AS [wartstvat],

						IIF(position.[IsService] = 1 OR ISNULL(product.[id],0) = 0, HM.ROUND2(position.[EvidenceQuantity] * position.[BasePricePLN]), 0) AS [wartTowaru]

				FROM @tblDocumentPositions AS position
					LEFT JOIN @tblDocumentPositions AS posSetForElement ON position.IsElementOfSet = 1 AND posSetForElement.IsSet = 1 AND position.ParentId = posSetForElement.NodeId
					LEFT JOIN [HM].[TW] AS product ON product.Id = position.ProductId
					LEFT JOIN [HM].[TW] AS prodSetForElement ON prodSetForElement.Id = posSetForElement.ProductId
					LEFT JOIN [HM].[XT] AS vatRate ON vatRate.id = position.[VatRateId]
				WHERE position.ModificationStatus IN (@created, @updated)
-----------------------------------------------------------------------------------------------------------------------------------------

			-- Dane kontrahenta i odbiorcy
			DECLARE @ContractorId INT, 
					@ContractorAddressId INT = 0, 
					@ReceiverId INT,	
					@ReceiverAddressId INT = 0;

			-- Jeśli 0 to incydentalny - ustawiamy NULL
			SELECT @ContractorId = CASE WHEN [ContractorId] = 0 THEN NULL ELSE [ContractorId] END,
				   @ReceiverId = CASE WHEN [ReceiverId] = 0 THEN NULL ELSE [ReceiverId] END
			FROM @tblForeignOrder

			-- Aktualizacja na NULL jeśli były 0
			UPDATE @tblForeignOrder
			SET [ContractorId] = @ContractorId, [ReceiverId] = @ReceiverId
			
			-- Zapis danych adresowych kontrahenta i odbiorcy
			EXEC [HM].[UpdateDocumentContractorAdressFromXML] @handle, @ContractorId, @ReceiverId, @ContractorAddressId OUT, @ReceiverAddressId OUT

			DECLARE @defaultVatRateId INT,
					@defaultVatRateValue FLOAT,
					@flag SMALLINT;

			-- czy dla typu dokumentu ma byc podpowiadana zerowa stawka VAT w pozycjach ?
			DECLARE @zeroVatRate BIT = 0;
			SELECT @zeroVatRate = zeroVatRate
			  FROM HM.TD
			       INNER JOIN @tblForeignOrder AS ZO ON TD.kod = ZO.DocumentTypeShortcut

			IF @zeroVatRate = 1
				BEGIN
					-- domyslnie zerowa stawka VAT
					SELECT TOP 1
					       @defaultVatRateId = id,
					       @defaultVatRateValue = wartosc
					  FROM HM.XT
					 WHERE typ = 20 AND kod LIKE '_0_' AND wartosc = 0
					ORDER BY kod
				END
			ELSE
				BEGIN
					-- domyslnie najwyzsza aktywna stawka VAT
					SELECT TOP 1
							@defaultVatRateId = id,
							@defaultVatRateValue = wartosc
					FROM HM.XT
					where typ = 20 and flag&128 > 0 -- stawka vat, aktywna 
					order by wartosc desc
				END

						-- Numer dokumentu
			--
			-- Jeśli brak dokumentu w bazie lub właśnie wystawiamy dokument przy automatycznym generowaniu numeracji to zawsze generuj serie i numer
			IF ISNULL(@docId, 0) = 0
				or 
				(
					exists(select 1 from hm.WorkParameters where id = 1070 and NumericValue = 1)
					and exists(select top 1 1 from @tblForeignOrder t inner join hm.ZO zo on zo.Id = t.Id and zo.bufor = 1 where t.IsIssued = 1)
				)
			BEGIN
				DECLARE 
					@DocumentTypeId INT,
					@DocumentNumerationCode VARCHAR(10),
					@genContractorId INT,
					@departmentId INT,
					@currency VARCHAR(3),
					@buffer BIT,
					@date DATE,
					@tableId SMALLINT,
					@code VARCHAR(40),
					@numberInSeries INT,
					@period INT,
					@departmentInSeries INT

				SELECT 
					@DocumentTypeId = (select ID from hm.td where kod = DocumentTypeShortcut),
					@DocumentNumerationCode = DocumentNumerationShortcut,
					@UserId = @userId,
					@genContractorId = ContractorId,
					@DepartmentId = DepartmentId,
					@Currency = ISNULL(Currency, ''),
					@buffer = IIF(IsIssued = 1, 0, 1),
					@Date = IssueDate,
					@TableId = 45,
					@numberInSeries = 0
				FROM @tblForeignOrder

				EXEC [HM].[GenerateDocumentNumber]    
					@docId,
					@DocumentTypeId,
					@DocumentNumerationCode,
					@userId,
					@genContractorId,
					@departmentId,
					@currency,
					@buffer,
					@date,
					@tableId,    
					@code OUT,
					@numberInSeries OUT,
					@period OUT,
					@departmentInSeries OUT

				UPDATE @tblForeignOrder set
					DocumentNumber = ISNULL(IIF(@code = '@0', NULL, @code), NEWID()),
					SeriesNo = @numberInSeries,
					Period = @period,
					DepartmentInSeries = @departmentInSeries
					
				SET @docNumber = ISNULL(IIF(@code = '@0', NULL, @code), NEWID());

			end;

			DECLARE @shortcutMessage NVARCHAR(200) = CONCAT('Zamówienie obce o numerze "', @docNumber, '" istnieje w bazie danych.')
			IF EXISTS(SELECT * FROM [HM].[ZO] zo WHERE zo.[typ] = 1 and zo.[kod] = @docNumber and zo.bufor = @buffer and Id <> @docId)
					THROW 50000, @shortcutMessage, 1;	
			-- Koniec Numeru dokumentu

			-- Wyciągnięcie informacji czy jest to nowy dokument sprzedażowy czy istniejący
			IF (@docId IS NULL OR @docId = 0)
			BEGIN
				-- Zapis nowego dokumentu
				INSERT INTO [HM].[ZO]
				(
					[guid],
					[kod],
					[nazwa],
					[data],
					[datasp],
					[opis],
					[odebrane],
					[subtyp],
					[typ_dk],
					[seria],
					[serianr],
					[rodzaj],
					[katalog],
					[znacznik],
					[bufor],					
					[anulowany],
					[aktywny],
					[statusMig],
					[schemat],
					[wystawil],
					[khid],
					[odid],
					[khadid],
					[odadid],
					[magazyn],
					[plattyp],
					[formaplatn],
					[plattermin],
					[walNetto],
					[walBrutto],
					[netto],
					[vat],
					[grupacen],
					[waluta],
					[kurs],		
					[typceny],
					[paragon],
					[rabat],
					[flag],
					[typ],
					[info],
					[seriadzial],					
					[okres],					
					[ok],
					[rr],
					[wplaty],
					[wartoscSP],
					[exp_fk],
					[zyskdod],
					[kod_obcy],
					[rozlmg],
					[statusRdf],  
					[splitPayment],
					[datarej],
					[createdBy],
					[createdDate],
					[modifiedBy],
					[modifiedDate],
					advanceInvoiceDeclared,
                    [typeExternal],
                    [idExternal],
                    [id2External]
				)
				SELECT					
					[Guid] AS [guid],
					[DocumentNumber] AS [kod],
					[Name] AS [nazwa],
					[IssueDate] AS [data],
					[OperationDate] AS [datasp],
					IIF([CorrectedDocumentId] IS NOT NULL, [CorrectionCause], [Description]) AS [opis], --dla korekty, jej przyczyna
					[RecipientPerson] AS [odebrane],
					(SELECT td.charakter FROM [HM].[TD] td WHERE td.[kod] = [DocumentTypeShortcut]) AS [subtyp],
					[DocumentTypeShortcut] AS [typ_dk],
					[DocumentNumerationShortcut] AS [seria],
					[SeriesNo] as serianr,
					[KindId] AS [rodzaj],
					[CatalogId] AS [katalog],
					[Marker] AS [znacznik],
					1^(CAST([IsIssued] as bit)) AS [bufor],
					[Canceled] AS [anulowany],
					1 AS [aktywny],
					[MigrationStatus] AS [statusMig],
					[AccountingPattern] AS [schemat],
					[IssuerId] AS [wystawil],
					[ContractorId] AS [khid],
					[ReceiverId] AS [odid],
					@ContractorAddressId AS [khadid],
					@ReceiverAddressId AS [odadid],
					[DepartmentId] AS [magazyn],
					[PayRegistryId] AS [plattyp],
					[PaymentFormClientId] AS [formaplatn],
					[PaymentDeadline] AS [plattermin],
					[Value] AS [walNetto],
					[ValueGross] AS [walBrutto],
					[ValuePLN] AS [netto],
					[ValueVatPLN] AS [vat],
					@IsNet AS [grupacen],
					[Currency] AS [waluta],
					[ExchangeRate] AS [kurs],			
					[PriceTypeId] AS [typceny],
					0 AS [paragon], -- Receipt
					[Discount] AS [rabat],	
					(hm.SetFlag(0, 0x8000, CalculateVatMethod) | hm.SetFlag(0, 0x80, RegisterMethod - 1) | IIF(IsCreatedFromForeignOrder = 'true', 0x1000, 0)) AS [flag],
					1 AS [typ],
					0 AS [info],
					[DepartmentInSeries] AS [seriadzial],
					[Period] AS [okres],
					[CashSettled] AS [ok],
					0 AS [rr],
					0 AS [wplaty],
					0 AS [wartoscSP],
					0 AS [exp_fk],
					0 AS [zyskdod],
					'' AS [kod_obcy],
					[ReservationType] AS [rozlmg],
					0 AS [statusRdf],     
					SplitPayment AS [splitPayment],
					InRegisterDate AS [datarej],
					[IssuerId] AS [createdBy],
					GETDATE() AS [createdDate],
					[IssuerId] AS [modifiedBy],
					GETDATE() AS [modifiedDate],
					CASE WHEN UseAdvanceInvoiceIsReadOnly = 1 THEN 0 ELSE UseAdvanceInvoice END,
                    [TypeExternal],
                    [IdExternal],
                    [Id2External]
				FROM @tblForeignOrder

				DECLARE @isBufor SMALLINT;
				DECLARE @docType SMALLINT;

				-- Pobranie id nowo utworzonego dokumentu
				SET @docId = SCOPE_IDENTITY()
				SET @docIdCorrected = (SELECT iddokkoryg from HM.ZO WHERE id = @docId);

				SELECT @isBufor = bufor,
					   @docType = subtyp 
				FROM HM.ZO 
				WHERE id = @docId

				-- Zapis pozycji nowego dokumentu
				INSERT INTO [HM].[ZP]
				(
					[lp],
					[idkh],
					[idtw],
					[kod],
					[opis],
					[jmwp],
					[jm],
					[iloscwp],
					[iloscjedn],
					[ilosc],
					[schemat],
					[stvat],
					[cena],
					[walNetto],						
					[walBrutto],
					[wartNetto],
					[wartVat],
					[grupacen],
					[cenaBazaPl],
					[cenaBaza],
					[super],
					[superkoryg],
					[subtyp],
					[flag],
					[typ],
					[opisDod],
					[waluta],
					[wartTowaru],
					[cenabaztow],
					[walcenybaz],
					[wartstvat],
					[zyskdod],
					[cenawal],
					[bufor],
					[kompensata],
					[typceny],
					[magazyn],
					[data],
					idpozkoryg
				)
				SELECT
					CASE WHEN @correction = 0 THEN tblDP.NodeId
						 ELSE                      (SELECT ZP.lp FROM HM.ZP WHERE ZP.id = tblDP.CorrectedPositionId)
					END AS lp,
					@ContractorId AS [idkh],
					IIF([ProductId] = 0, NULL, [ProductId]) AS [idtw],
					(
						CASE
							WHEN ([ProductId] = 0) THEN ''
							ELSE (Select [kod] from HM.TW where [id] = [ProductId])
						END
					) AS [kod],
					[Description] AS [opis],
					[UnitOfMeasurement] AS [jmwp],
					[EvidenceUnitOfMeasurement] AS [jm],
					[Quantity] AS [iloscwp],
					[UnitQuantity] AS [iloscjedn],
					[EvidenceQuantity] AS [ilosc],
					[AccountingPattern] AS [schemat],
					IIF(@docType = 46,[DomesticVatRateId],ISNULL(NULLIF([VatRateId], ''), @defaultVatRateId)) AS [stvat], -- jeśli dok eksportowy
					CASE WHEN @IsNet = 1 THEN [Price] ELSE [PriceGross] END * @ExchangeRate AS [cena], --celowo bez zaokrąglenia
					[Value] AS [walNetto],						
					[ValueGross] AS [walBrutto],
					[ValuePLN] AS [wartNetto],
					[ValueVatPLN] AS [wartVat],
					@IsNet AS [grupacen],
					CASE WHEN @IsNet = 1 THEN [PriceListPrice] ELSE [PriceListPriceGross] END * @ExchangeRate AS [cenaBazaPl], --celowo bez zaokrąglenia
					[OriginalPriceListPrice] AS [cenaBaza],
					@docId AS [super],
					ISNULL(@docIdCorrected, @docId) AS [superkoryg],
					1 AS [subtyp],
					valuesForDP.flag AS [flag],
					valuesForDP.typ AS [typ],
					[CodePKWIU] AS [opisdod],
					[OriginalPriceListPriceCurrency] AS [waluta],
					valuesForDP.wartTowaru AS [wartTowaru],
					valuesForDP.cenabaztow AS [cenabaztow],
					valuesForDP.walcenybaz AS [walcenybaz],
					valuesForDP.wartstvat AS [wartstvat],
					valuesForDP.zyskdod AS [zyskdod],
					CASE WHEN @IsNet = 1 THEN [Price] ELSE [PriceGross] END AS [cenawal],
					@isBufor AS [bufor],
					Compensation AS [kompensata],
					[PriceTypeId] AS [typceny],
					@department AS [magazyn],
					@issueDate as [data],
					CorrectedPositionId AS idpozkoryg
				FROM @tblDocumentPositions AS tblDP
				JOIN @valuesForDP valuesForDP ON valuesForDP.NodeId = tblDP.NodeId
				ORDER BY tblDP.NodeId;	--istotna jest kolejność dodanych pozycji

				-- Zapis transakcji nowego dokumentu
				INSERT INTO HM.TR
				(
					[owner_id],
					[kod],
					[id_kto],
					[dzial],
					[id_komu],
					[datarozl],
					[data],
					[termin],
					[flag],
					[typ],
					[subtyp],
					[owner_base],
					[ok],
					[nrozl_oper],
					[wartosc],
					[wartosc_rozl],
					[nrozlilosc],
					[status]
				)
				SELECT 
					@docId AS [owner_id],
					zo.[kod] AS [kod],
					ISNULL([ReceiverId], [ContractorId]) AS [id_kto],		--logika z formatki klasycznej
					[DepartmentId] AS [dzial],
					NULL AS [id_komu],
					NULL AS [datarozl],
					[IssueDate] AS [data],
					[PaymentDeadline] AS [termin],
					16896 AS [flag],	-- flaga transakcji zamówienia, zgodnie z zamówieniem
					0 AS [typ],			-- transakcja zamowieniowa
					1 AS [subtyp],		-- zobowiązanie \ sprzedaż \ zamówienie obce
					45 AS [owner_base],	-- zamówienie
					1 AS [ok],			-- transakcja rozliczona (bo wartosc = 0)
					0 AS [nrozl_oper],
					0 AS [wartosc],
					0 AS [wartosc_rozl],
					0 AS [nrozlilosc],
					4 AS [status]		-- świeżo wystawiony dokument
				FROM @tblForeignOrder
				JOIN [HM].[ZO] zo ON zo.[id] = @docId;
			
			END
			ELSE
			BEGIN
				-- Aktualizacja dokumentu
				UPDATE [HM].[ZO]
				SET zo.[kod] = tblSD.[DocumentNumber],
					zo.[nazwa] = tblSD.[Name],
					zo.[data] = tblSD.[IssueDate],
					zo.[datasp] = tblSD.[OperationDate],
					zo.[opis] = IIF(tblSD.[CorrectedDocumentId] IS NOT NULL, tblSD.[CorrectionCause], tblSD.[Description]), -- dla korekty przyczyna korekty
					zo.[odebrane] = tblSD.[RecipientPerson],
					zo.[subtyp] = tblSD.[BaseType],
					zo.[typ_dk] = tblSD.[DocumentTypeShortcut],
					zo.[seria] = tblSD.[DocumentNumerationShortcut],
					zo.[serianr] = tblSD.[SeriesNo],
					zo.[rodzaj] = tblSD.[KindId],
					zo.[katalog] = tblSD.[CatalogId],
					zo.[znacznik] = tblSD.[Marker],
					zo.[bufor] = 1^(CAST(tblSD.[IsIssued] as bit)),
					zo.[anulowany] = tblSD.[Canceled],
					zo.[aktywny] = tblSD.[IsActive],
					zo.[statusMig] = tblSD.[MigrationStatus],
					zo.[schemat] = tblSD.[AccountingPattern],
					zo.[wystawil] = tblSD.[IssuerId],
					zo.[khid] = tblSD.[ContractorId],
					zo.[odid] = tblSD.[ReceiverId],
					zo.[khadid] = @ContractorAddressId,
					zo.[odadid] = @ReceiverAddressId,
					zo.[magazyn] = tblSD.[DepartmentId],
					zo.[plattyp] = IIF(tblSD.[PayRegistryId] = -1, NULL, tblSD.[PayRegistryId]),
					zo.[formaplatn] = tblSD.[PaymentFormClientId],
					zo.[plattermin] = tblSD.[PaymentDeadline],
					zo.[walNetto] = tblSD.[Value],
					zo.[walBrutto] = tblSD.[ValueGross],
					zo.[netto] = tblSD.[ValuePLN],
					zo.[vat] = tblSD.[ValueVatPLN],
					zo.[grupacen] = @IsNet,
					zo.[waluta] = tblSD.[Currency],
					zo.[kurs] = tblSD.[ExchangeRate],				
					zo.[typceny] = tblSD.[PriceTypeId],
					zo.[rabat] = tblSD.[Discount],
					zo.[paragon] = 0, -- Receipt
					zo.[flag] = hm.SetFlag(0, 0x8000, CalculateVatMethod) | hm.SetFlag(0, 0x80, RegisterMethod - 1) | IIF(IsCreatedFromForeignOrder = 'true', 0x1000, 0),
					zo.[typ] = 1,
					zo.[info] = 0,
					zo.[seriadzial] = IsNull(tblSD.DepartmentInSeries, 0),
					zo.[okres] = IsNull(tblSD.Period, 0),
					zo.[ok] = tblSD.[CashSettled],
					zo.[rr] = 0,
					zo.[wplaty] = 0,
					zo.[wartoscSP] = 0,
					zo.[exp_fk] = 0, --( zo.[exp_fk] & ~6 ) | 2 * [FiscalizationState] | 8 * [PublishInventoryDocumentMode],
					zo.[zyskdod] = 0,
					zo.[kod_obcy] = '',
					zo.[rozlmg] = tblSD.[ReservationType],
					zo.[statusRdf] = 0,   
					zo.[splitPayment] = tblSD.[SplitPayment],
					zo.[datarej] = tblSD.[InRegisterDate],
					zo.advanceInvoiceDeclared = CASE WHEN tblSD.UseAdvanceInvoiceIsReadOnly = 1 THEN zo.advanceInvoiceDeclared ELSE tblSD.UseAdvanceInvoice END,
                    zo.[typeExternal] = tblSD.[TypeExternal],
                    zo.[idExternal] = tblSD.[IdExternal],
                    zo.[id2External] = tblSD.[Id2External]
				FROM [HM].[ZO] zo, @tblForeignOrder tblSD
				WHERE zo.[id] = @docId			
				
				SELECT @isBufor = bufor,
					   @docType = subtyp 
					   FROM HM.ZO WHERE id = @docId;

				--Usunięcie powiązań operacji
				DELETE ro 
					FROM [HM].[RO] ro
					JOIN [HM].[OP] op ON op.id = ro.id1
					JOIN @tblDeletedPositions delPositions ON delPositions.id = op.owner_id AND op.owner_base = 30

				-- Usunięcie skasowanych operacji
				DELETE op
					FROM [HM].[OP] op
					JOIN @tblDeletedPositions delPositions ON delPositions.id = op.owner_id AND op.owner_base = 30

				-- Na potrzeby rezerwacji
				DECLARE @DeletedSaleDocumentPositionsRZ [HM].[DocumentPositionsType]
				INSERT INTO @DeletedSaleDocumentPositionsRZ
				SELECT rz.id,rz.idtw, rz.iddokrz,rz.iloscrz 
						FROM [HM].[RZ] rz
						JOIN @tblDeletedPositions delPositions ON rz.bazapozrz = 30 and rz.idpozrz = delPositions.id

				-- Usunięcie skasowanych pozycji
				DELETE FROM [HM].[ZP]
				WHERE [super] = @docId
				AND [id] IN (SELECT [Id] FROM @tblDeletedPositions)				

				--Zapis nowych pozycji
				DECLARE @newPositionIds TABLE (Id INT, Guid UNIQUEIDENTIFIER)
				INSERT INTO [HM].[ZP]
				(
					[guid],
					[lp],
					[idkh],
					[idtw],
					[kod],
					[opis],
					[jmwp],
					[jm],
					[iloscwp],
					[iloscjedn],
					[ilosc],
					[schemat],
					[stvat],
					[cena],
					[walNetto],						
					[walBrutto],
					[wartNetto],
					[wartVat],
					[grupacen],
					[cenaBazaPl],
					[cenaBaza],
					[super],
					[superkoryg],
					[subtyp],
					[flag],
					[typ],
					[opisdod],
					[waluta],
					[wartTowaru],
					[cenabaztow],
					[walcenybaz],
					[wartstvat],
					[zyskdod],
					[cenawal],
					[bufor],
					[kompensata],
					[typceny],
					[magazyn],
					[data],
					[idpozkoryg],
					[odwrotneObc]
				)
				OUTPUT INSERTED.id, INSERTED.guid INTO @newPositionIds
				SELECT
					[Guid] AS [guid],
					CASE WHEN @correction = 0 OR [IsNewCorrectPosition] = 'true' THEN tblDP.NodeId
						 ELSE                      (SELECT ZP.lp FROM HM.ZP WHERE ZP.id = tblDP.CorrectedPositionId)
					END AS lp,
					@ContractorId AS [idkh],
					IIF([ProductId] = 0, NULL, [ProductId]) AS [idtw],
					(
						CASE
							WHEN ([ProductId] = 0) THEN ''
							ELSE (Select [kod] from HM.TW where [id] = [ProductId])
						END
					) AS [kod],
					[Description] AS [opis],
					[UnitOfMeasurement] AS [jmwp],
					[EvidenceUnitOfMeasurement] AS [jm],
					[Quantity] AS [iloscwp],
					[UnitQuantity] AS [iloscjedn],
					[EvidenceQuantity] AS [ilosc],
					[AccountingPattern] AS [schemat],
					IIF(@docType = 46,[DomesticVatRateId],ISNULL(NULLIF([VatRateId], ''), @defaultVatRateId)) AS [stvat],
					CASE WHEN @IsNet = 1 THEN [Price] ELSE [PriceGross] END * @ExchangeRate AS [cena], --celowo bez zaokrąglenia
					[Value] AS [walNetto],						
					[ValueGross] AS [walBrutto],
					[ValuePLN] AS [wartNetto],
					[ValueVatPLN] AS [wartVat],
					@IsNet AS [grupacen],
					CASE WHEN @IsNet = 1 THEN [PriceListPrice] ELSE [PriceListPriceGross] END * @ExchangeRate AS [cenaBazaPl], --celowo bez zaokrąglenia
					[OriginalPriceListPrice] AS [cenaBaza],
					@docId AS [super],
					ISNULL(@docIdCorrected, @docId) AS [superkoryg],
					1 AS [subtyp],		-- pozycja zamówienia obcego			
					valuesForDP.flag AS [flag],
					valuesForDP.typ AS [typ],
					[CodePKWIU] AS [opisdod],
					[OriginalPriceListPriceCurrency] AS [waluta],
					valuesForDP.wartTowaru AS [wartTowaru],
					valuesForDP.cenabaztow AS [cenabaztow],
					valuesForDP.walcenybaz AS [walcenybaz],
					valuesForDP.wartstvat AS [wartstvat],
					valuesForDP.zyskdod AS [zyskdod],
					CASE WHEN @IsNet = 1 THEN [Price] ELSE [PriceGross] END AS [cenawal],
					@isBufor AS [bufor],
					Compensation AS [kompensata],
					[PriceTypeId] AS [typceny],
					@department AS [magazyn],
					@issueDate as [data],
					[CorrectedPositionId] AS [idpozkoryg],
					[OO] AS [odwrotneObc]
				FROM @tblDocumentPositions AS tblDP
				JOIN @valuesForDP valuesForDP ON valuesForDP.NodeId = tblDP.NodeId
				WHERE tblDP.ModificationStatus = @created
				ORDER BY tblDP.NodeId	--istotna jest kolejność dodanych pozycji

				-- Aktualizacja Id w @tblDocumentPositions
				UPDATE tblPosition SET Id = newIds.Id
				FROM @tblDocumentPositions tblPosition
				JOIN @newPositionIds newIds ON newIds.guid = tblPosition.guid
				WHERE tblPosition.ModificationStatus = @created

				-- Zapis nowych operacji
				INSERT INTO [HM].[OP]
				(
					[flag],
					[subtyp],
					[owner_id],
					[owner_base],
					[kod],
					[id_co],
					[id_transakcji],
					[ok],
					[ilosc],
					[ilosc_rozl],
					[wartosc],
					[wartosc_rozl],
					[datarozl]
				)
				SELECT
					0 AS [flag],
					1 AS [subtyp],
					zp.[id] AS [owner_id],
					30 AS [owner_base],
					zp.[kod] AS [kod],
					zp.[idtw] AS [id_co],
					TR.id AS [id_transakcji],
					0 AS [ok],
					Abs(zp.ilosc) AS [ilosc],
					0 AS [ilosc_rozl],
					0 AS [wartosc],
					0 AS [wartosc_rozl],
					NULL AS [datarozl]
				FROM @tblDocumentPositions tblDP
				JOIN [HM].[ZP] zp ON zp.id = tblDP.Id
				JOIN HM.TR ON TR.typ = 0 AND TR.owner_base = 45 AND TR.owner_id = zp.super
				LEFT OUTER JOIN HM.TW ON ZP.idtw = TW.id
				WHERE tblDP.ModificationStatus = @created

				-- Aktualizacja pozycji
				UPDATE [HM].[ZP]
				SET zp.lp = CASE WHEN @correction = 0 OR [IsNewCorrectPosition] = 'true' THEN tblDP.NodeId
								 ELSE                      (SELECT ZP.lp FROM HM.ZP WHERE ZP.id = tblDP.CorrectedPositionId)
							END,
					zp.[idkh] = @ContractorId,
					zp.[idtw] = IIF(tblDP.[ProductId] = 0, NULL, tblDP.[ProductId]),
					zp.[kod] = (
						CASE
							WHEN (tblDP.[ProductId] = 0) THEN ''
							ELSE (Select [kod] from HM.TW where [id] = tblDP.[ProductId])
						END
					),	
					zp.[opis] = tblDP.[Description],
					zp.[jmwp] = tblDP.[UnitOfMeasurement],
					zp.[jm] = tblDP.[EvidenceUnitOfMeasurement],
					zp.[iloscwp] = tblDP.[Quantity],
					zp.[iloscjedn] = tblDP.[UnitQuantity],
					zp.[ilosc] = tblDP.[EvidenceQuantity],					
					zp.[schemat] = tblDP.[AccountingPattern],
					zp.[stvat] = IIF(@docType = 46, tblDP.[DomesticVatRateId], tblDP.[VatRateId]),
					zp.[cena] = CASE WHEN @IsNet = 1 THEN tblDP.[Price] ELSE tblDP.[PriceGross] END * @ExchangeRate, --celowo bez zaokrąglenia
					zp.[walNetto] = tblDP.[Value],						
					zp.[walBrutto] = tblDP.[ValueGross],
					zp.[wartNetto] = tblDP.[ValuePLN],
					zp.[wartVat] = tblDP.[ValueVatPLN],
					zp.[grupacen] = @IsNet,
					zp.[cenaBazaPl] = CASE WHEN @IsNet = 1 THEN tblDP.[PriceListPrice] ELSE tblDP.[PriceListPriceGross] END * @ExchangeRate, --celowo bez zaokrąglenia
					zp.[cenaBaza] = tblDP.[OriginalPriceListPrice],
					zp.[subtyp] = 1, -- pozycja zamówienia obcego
					zp.[flag] = valuesForDP.flag,
					zp.[typ] = valuesForDP.typ,
					zp.[opisdod] = tblDP.[CodePKWIU],
					zp.[waluta] = tblDP.[OriginalPriceListPriceCurrency],
					zp.[wartTowaru] = valuesForDP.wartTowaru,
					zp.[cenabaztow] = valuesForDP.cenabaztow,
					zp.[walcenybaz] = valuesForDP.walcenybaz,
					zp.[wartstvat] = valuesForDP.wartstvat,
					zp.[zyskdod] = valuesForDP.zyskdod,
					zp.[cenawal] = CASE WHEN @IsNet = 1 THEN tblDP.[Price] ELSE tblDP.[PriceGross] END,
					zp.[bufor] = @isBufor,
					zp.[kompensata] = tblDP.[Compensation],
					zp.[typceny] = tblDP.[PriceTypeId],
					zp.[magazyn] = @department,
					zp.[data] = @issueDate,
					zp.[idpozkoryg] = tblDP.[CorrectedPositionId],
					zp.[odwrotneObc] = tblDP.[OO]
				FROM [HM].[ZP] zp
				JOIN @tblDocumentPositions tblDP ON zp.[id] = tblDP.[Id]
				JOIN @valuesForDP valuesForDP ON valuesForDP.NodeId = tblDP.NodeId
				WHERE zp.[super] = @docId AND tblDP.ModificationStatus = @updated
				
				-- Aktualizacja operacji
				UPDATE [HM].[OP]
				SET
					[flag] = CASE WHEN (zp.flag & 0x1000 = 0x1000) THEN 0x2200 -- operacja ZM dla pozycji niebiorąca udziału w rozliczeniach
									ELSE IIF(op.flag & 0x400 = 0x400, 0x400, 0) | IIF(ISNULL(tw.[subtypext], 1) = 1, 512, 0)
									END,
					[subtyp] = 1,
					[owner_id] = zp.[id],
					[kod] = zp.[kod],
					[id_co] = zp.[idtw],
					[ok] = CASE WHEN (zp.[flag] & 0x1000 = 0x1000) THEN 1
								-- W przypadku zamówień uwzględniamy pozycje z towarami niekartotekowymi
								ELSE IIF((zp.[ilosc] = [ilosc_rozl] AND [ilosc_rozl] <> 0) /* OR zp.[idtw] IS NULL */, 1, 0) END,
					[ilosc] = Abs(zp.[ilosc]),
					[wartosc] = IIF(zp.flag & 0x1000 = 0x1000, 0, zp.walBrutto),
					[datarozl] = CASE WHEN (zp.[flag] & 0x1000 = 0x1000) THEN CONVERT(date, GETDATE()) 
									ELSE IIF(zp.[idtw] IS NULL, CONVERT(date, GETDATE()), NULL)
									END
				FROM @tblDocumentPositions tblPositions 				
				JOIN [HM].[ZP] zp ON tblPositions.Id = zp.id
				JOIN [HM].[OP] op ON op.[owner_id] = zp.[id] AND op.[owner_base] = 30
				LEFT OUTER JOIN [HM].[TW] tw ON zp.idtw = tw.id
				WHERE zp.super = @docId AND tblPositions.ModificationStatus IN (@created, @updated)	--czy powinno być też @created? - dotychczas tak było (modyfikowane także dodane powyżej)

				-- Tymczasowe tabele pozycji na potrzeby zapisu rezerwacji
				DECLARE @tblDRP [HM].[DocumentReservationsPositionsType]

				INSERT INTO @tblDRP 
				SELECT tblDP.Id, tblDP.NodeId, tblDP.ProductId, tblDP.ReservationType
				FROM @tblDocumentPositions tblDP

				DECLARE @tblDP [HM].[DocumentPositionsType]

				INSERT INTO @tblDP
				SELECT ZP.[Id], ZP.[idtw], ZP.[super], ZP.[ilosc]
				FROM [HM].[ZP] ZP
				WHERE ZP.[super] = @docId

				-- Zapis rezerwacji
				EXEC [HM].[UpdateDocumentReservationsFromXML] @handle, @tblDRP, @tblDP, @DeletedSaleDocumentPositionsRZ, @docId, @IssueDate, @ContractorId, @correction, 0, @userId

			END			

			-- Aktualizacja transakcji
			-- Powiązane transakcje wydań
			DECLARE @incompletedOperations INT,
					@transactionId int,
					@incompletedTransactions INT,
					@status SMALLINT,
					@bufor bit,
					@anyTransactions INT,
					@wartoscTransakcji FLOAT;

			DECLARE @linkedTransactions TABLE(id int);

			SET @incompletedOperations = (
				SELECT COUNT(1)
				FROM [HM].[OP] op
				JOIN [HM].[ZP] zp ON op.owner_id = zp.id AND op.owner_base = 30 
				WHERE (zp.super = ISNULL(@docIdCorrected, @docId) OR zp.super = @docId) AND ok = 0);

			SET @transactionId = (SELECT id from HM.TR where owner_id = ISNULL(@docIdCorrected, @docId) AND owner_base = 45); -- do zamówienia obcego
				
			-- Powiązane transakcje
			INSERT INTO @linkedTransactions
			SELECT id2 from hm.po 
			WHERE id1 = @transactionId;

			SET @incompletedTransactions = (SELECT COUNT(1) 
											FROM HM.TR tr
											JOIN @linkedTransactions transactions ON transactions.id = tr.id
											where tr.owner_base = 33 AND tr.status <> 32); -- dok mag, oprucz dokumentów wystawionych

			SET @bufor = (SELECT bufor FROM [HM].[ZO] WHERE id = @docId);
				
			IF @correction = 0
			BEGIN
				SET @status = CASE 
							WHEN @incompletedTransactions = 0 AND @bufor = 1 THEN 4
							WHEN @incompletedOperations <> 0 THEN 8
							WHEN @incompletedTransactions <> 0 THEN 16
							ELSE 32
							END;
			END ELSE BEGIN
				SET @anyTransactions = (SELECT COUNT(1) 
											FROM HM.TR tr
											JOIN @linkedTransactions transactions ON transactions.id = tr.id
											where tr.owner_base = 33);-- dok mag

				SET @status = CASE 
							WHEN @incompletedTransactions = 0 AND @anyTransactions = 0 THEN 4
							WHEN @incompletedOperations <> 0 THEN 8
							WHEN @incompletedTransactions <> 0 OR @bufor = 1 THEN 16
							ELSE 32
							END;
			END;

			-- Wartość transakcji
			SELECT @wartoscTransakcji = ISNULL(SUM(
				CASE 
					WHEN ((32768 & OP.flag) = 32768) THEN 0 
					WHEN ((OP.flag & 6144) = 6144) THEN -OP.wartosc 
					WHEN ((2048 & OP.flag) = 2048) THEN -OP.wartosc 
					ELSE OP.wartosc 
				END),0) 
			FROM [HM].[TR] TR
			JOIN HM.OP ON OP.id_transakcji = TR.id 
			WHERE TR.owner_id = ISNULL(@docIdCorrected, @docId) and TR.typ = 0 AND TR.owner_base = 45

			UPDATE [HM].[TR]
			SET
				id_kto = ISNULL(zo.odid, zo.khid),		--logika z formatki klasycznej
				kod = zo.kod,
				wartosc = @wartoscTransakcji,
				nrozl_oper = @incompletedOperations,
				nrozlilosc = @incompletedOperations,
				status = @status,
				ok = IIF(@incompletedOperations = 0, 1, 0),
				data = zo.data,
				termin = zo.plattermin,
				datarozl = IIF(@incompletedOperations = 0, CONVERT(date, GETDATE()), NULL)
			FROM [HM].[TR] tr 
			JOIN [HM].[ZO] zo ON zo.id = tr.owner_id and zo.id = ISNULL(@docIdCorrected, @docId)
			WHERE tr.owner_base = 45

			-- Koniec aktualizacji transakcji

			-- Aktualizacja pól własnych dokumentu
			exec HM.UpdateCustomFieldsFromXML
				@docData = @docData,
				@moduleCode = @moduleCode, 
				@docId = @docId,
				@docType = 1

			CLEAN_AND_FINISH:

			EXEC sp_xml_removedocument @handle;

			EXEC [HM].[UpdateForeignOrderDocumentRIFromXML] @docId, @docData , @userId , @moduleCode

			COMMIT TRANSACTION;

			EXEC [HM].[ReadForeignOrderDocumentAsXML] @docId, @userId, @moduleCode
	END TRY
	BEGIN CATCH
		IF @@TRANCOUNT > 0
		BEGIN
			ROLLBACK TRANSACTION;

			THROW;
		END
	END CATCH
END

GO

CREATE OR ALTER PROCEDURE [HM].[UpdateSaleDocumentFromXML] (@docData XML, @userId int, @moduleCode nvarchar(255))
AS
BEGIN
	SET NOCOUNT ON; -- Stops the message that shows the count of the number of rows affected by a Transact-SQL statement or stored procedure from being returned as part of the result set.
	SET XACT_ABORT ON; -- Specifies whether SQL Server automatically rolls back the current transaction when a Transact-SQL statement raises a run-time error.
	
	BEGIN TRY
		BEGIN TRANSACTION;
			DECLARE @allDataWithChangeStatus BIT = 0 -- wartość 1 oznacza że korzystamy z ModoficationStatus z XML

			-- Tabela tymczasowa na dane dokumentu sprzedażowego
			DECLARE @tblSaleDocument TABLE
			(
				[Id] INT,
				[Guid] UNIQUEIDENTIFIER,
				[DocumentNumber] VARCHAR(40) COLLATE database_default,
				[Name] VARCHAR(40) COLLATE database_default,
				[IssueDate] DATETIME,
				[OperationDate] DATETIME,
				[Description] VARCHAR(80) COLLATE database_default,
				[CorrectionCause] VARCHAR(80) COLLATE database_default,
				[RecipientPerson] VARCHAR(40) COLLATE database_default,
				[BaseType] SMALLINT,
				[DocumentTypeShortcut] VARCHAR(4) COLLATE database_default,
				[DocumentNumerationShortcut] VARCHAR(5) COLLATE database_default,
				[SeriesNo] INT,
				[KindId] INT,
				[CatalogId] INT,
				[Marker] SMALLINT,
				[IsIssued] BIT,
				[DocumentStatus] SMALLINT,
				[DocumentStatusDescription] VARCHAR(40) COLLATE database_default,
				[HmImported] BIT,
				[Archived] BIT,
				[FkExported] BIT,
				[IsCreatedFromForeignOrder] BIT,
				[Canceled] SMALLINT,
				[IsActive] BIT,
				[MigrationStatus] SMALLINT,
				[ContractorId] INT,
				[AccountingPattern] VARCHAR(4) COLLATE database_default,
				[IssuerId] INT,
				[ReceiverId] INT,
				[DepartmentId] INT,
				[PayRegistryId] INT,
				[PaymentFormClientId] INT,
				[PaymentDeadline] DATETIME,
				[Value] DECIMAL(24,2),
				[ValueVat] DECIMAL(24,2),
				[ValueGross] DECIMAL(24,2),
				[ValuePLN] DECIMAL(24,2),
				[ValueVatPLN] DECIMAL(24,2),
				[ValueGrossPLN] DECIMAL(24,2),
				[PriceKind] SMALLINT,
				[Currency] VARCHAR(3) COLLATE database_default,	
				[ExchangeRate] DECIMAL(24,8),
				[ExchangeRatePIT] DECIMAL(24,8),
				[CalculateVatMethod] SMALLINT,
				[InventorySettlementStatus] INT,
				[PriceTypeId] INT,
				[Discount] DECIMAL(24,2),
				[ReservationType] SMALLINT,
				[FiscalizationState] SMALLINT,
				[PublishInventoryDocumentMode] SMALLINT,
				ReceiptNumber VARCHAR(40) COLLATE database_default,
				UniqueCashBoxNumber VARCHAR(40) COLLATE database_default,
				[JPK_V7] VARCHAR(256) COLLATE database_default,
				[JPK] SMALLINT,
				[SplitPayment] BIT,
				[InRegisterDate] DATETIME,
				[SaleRegister] INT,
				[RegisterMethod] SMALLINT,
				[CashSettled] SMALLINT,
				[DepartmentInSeries] INTEGER,
				[Period] SMALLINT,
				[CorrectedDocumentId] INTEGER,
				[IsGroupCorrection] BIT,
				[RelatedForeignOrderDocumentId] INT,
                [IsOssProcedure] BIT,
                [OssShippingCountryId] INT,
                [OssDeliveryCountryId] INT,
                [TypeExternal] INT,
                [IdExternal] VARCHAR(11) COLLATE database_default,
                [Id2External] VARCHAR(32) COLLATE database_default
			)
			-- Tabela tymczasowa na dane pozycji dokumentu sprzedażowego
			DECLARE @tblDocumentPositions TABLE
			(
				[Id] INT,
				[Compensation] SMALLINT,
				[NodeId] INT,
				[ParentId] INT,
				[OrdinalNumber] SMALLINT,
				[Guid] UNIQUEIDENTIFIER,
				[ProductId] INT,
				[ProductShortcut] VARCHAR(40) COLLATE database_default,
				[Description] VARCHAR(max) COLLATE database_default,
				[UnitOfMeasurement] VARCHAR(8) COLLATE database_default,
				[EvidenceUnitOfMeasurement] VARCHAR(8) COLLATE database_default,
				[Quantity] DECIMAL(24,4),
				[UnitQuantity] DECIMAL(35,15),
				[EvidenceQuantity] DECIMAL(24,4),
				[ReservationType] SMALLINT,
				[AccountingPattern] VARCHAR(4) COLLATE database_default,
				[VatRateId] INT,
				[Price] DECIMAL(35,15),
				[PriceGross] DECIMAL(35,15),
				[Value] DECIMAL(24,2),
				[ValueVat] DECIMAL(24,2),
				[ValueGross] DECIMAL(24,2),
				[ValuePLN] DECIMAL(24,2),
				[ValueVatPLN] DECIMAL(24,2),
				[ValueGrossPLN] DECIMAL(24,2),
				[PriceTypeId] SMALLINT,
				[PriceListPrice] DECIMAL(35,15),
				[PriceListPriceGross] DECIMAL(35,15),
				[JPK_V7] VARCHAR(10) COLLATE database_default,
				[CN] VARCHAR(10) COLLATE database_default,
				[CodePKWIU] VARCHAR(20) COLLATE database_default,
				[VatRegister] INT,
				[CorrectedPositionId] INT,
				[IsNewCorrectPosition] BIT,
				[IsSet] BIT,
				[IsElementOfSet] BIT,
				[NoEditOfSet] BIT,
				[PriceOfSetCalculatedBasedOfElements] BIT,
				[VatUE] BIT,
				[ServiceExport] BIT,
				[DomesticVatRateId] INT,
				[OriginalPriceListPrice] DECIMAL(35,15),
				[OriginalPriceListPriceCurrency] VARCHAR(3) COLLATE database_default,
				[OriginalBasePrice] DECIMAL(35,15),
				[OriginalBasePriceCurrency] VARCHAR(3) COLLATE database_default,
				[BasePricePLN] DECIMAL(35,15),
				[CorrectedDocumentId] INT,
				[OO] BIT,
				[ServiceExpense] DECIMAL(35,15),
				[IsService] BIT,
				[ModificationStatus] SMALLINT,
				[IsOssService] BIT
			)

			DECLARE  @tblRelatedDocuments TABLE
			(
				[Id] INT,
				[DocumentType] NVARCHAR(100) COLLATE database_default,
				[Number] NVARCHAR(100) COLLATE database_default,
				[CharacterId] INT,
				[InBuffer] BIT
			)

            DECLARE  @tblRefundableDeposits TABLE
			(
				[Id] INT,
				[DKId] INT,
				[PackageName] VARCHAR (100) COLLATE database_default,
				[RefundableDepositName] VARCHAR (100) COLLATE database_default,
                [PackageId] INT,
				[Quantity] DECIMAL(24,6),
				[Price] DECIMAL(24,2),
				[Value] DECIMAL(24,2)
			)

			-- Przygotowanie xml
			DECLARE @handle INT, @prepareXmlStatus INT
			EXEC @prepareXmlStatus = sp_xml_preparedocument @handle OUTPUT, @docData
			

            -- Deserializacja danych dokumentu sprzedażowego z xml
			INSERT INTO @tblSaleDocument
			SELECT 
					[Id],
					CASE WHEN [Guid] IS NULL OR [Guid] = '' OR [Guid] = '00000000-0000-0000-0000-000000000000' THEN NEWID() ELSE [GUID] END,
					[DocumentNumber],
					[Name],
					HM.CastToDateOrNull([IssueDate]),
					HM.CastToDateOrNull([OperationDate]),
					[Description],
					[CorrectionCause],
					[RecipientPerson],
					[BaseType],
					[DocumentTypeShortcut],
					[DocumentNumerationShortcut],
					[SeriesNo],
					[KindId],
					[CatalogId],
					[Marker],
					[IsIssued],
					[DocumentStatus],
					[DocumentStatusDescription],
					[HmImported],
					[Archived],
					[FkExported],
					[IsCreatedFromForeignOrder],
					[Canceled],
					[IsActive],
					[MigrationStatus],
					[ContractorId],
					[AccountingPattern],
					[IssuerId],
					[ReceiverId],
					[DepartmentId],
					IIF([PayRegistryId] = -1, NULL, [PayRegistryId]),
					[PaymentFormClientId],
					HM.CastToDateOrNull([PaymentDeadline]),
					[Value],
					[ValueVat],
					[ValueGross],
					[ValuePLN],
					[ValueVatPLN],
					[ValueGrossPLN],
					[PriceKind],
					[Currency],	
					--domyślnie ustawiamy 1 - wsteczna kompatybilność
					CASE WHEN 0 = ISNUMERIC([ExchangeRate]) THEN 1 ELSE CAST([ExchangeRate] AS DECIMAL(24,8)) END,
					CASE WHEN 0 = ISNUMERIC([ExchangeRatePIT]) THEN 1 ELSE CAST([ExchangeRatePIT] AS DECIMAL(24,8)) END,
					[CalculateVatMethod],
					[InventorySettlementStatus],
					[PriceTypeId],
					[Discount],
					[ReservationType],
					[FiscalizationState],
					[PublishInventoryDocumentMode],
					ReceiptNumber,
					UniqueCashBoxNumber,
					[JPK_V7],
					[JPK],
					[SplitPayment],
					HM.CastToDateOrNull([InRegisterDate]),
					[SaleRegister],
					[RegisterMethod],
					[CashSettled],
					[DepartmentInSeries],
					[Period],
					IIF([CorrectedDocumentId] = 0, NULL, [CorrectedDocumentId]),
					[IsGroupCorrection],
					[RelatedForeignOrderDocumentId],
                    [IsOssProcedure],
                    [OssShippingCountryId],
                    [OssDeliveryCountryId],
                    [TypeExternal],
                    CASE WHEN [IdExternal] = '' THEN NULL ELSE CAST([IdExternal] AS INT) END,
                    CASE WHEN [Id2External] = '' THEN NULL ELSE [Id2External] END
			FROM 
				OPENXML(@handle, '/SaleDocument')
				WITH
				(
					[Id] INT 'Id',
					[Guid] NVARCHAR(36) 'Guid',
					[DocumentNumber] VARCHAR(40) 'DocumentNumber',
					[Name] VARCHAR(40) 'Name',
					[IssueDate] NVARCHAR(100) 'IssueDate',
					[OperationDate] NVARCHAR(100) 'OperationDate',
					[Description] VARCHAR(80) 'Description',
					[CorrectionCause] VARCHAR(80) 'CorrectionCause',
					[RecipientPerson] VARCHAR(40) 'RecipientPerson',
					[BaseType] SMALLINT 'BaseType',
					[DocumentTypeShortcut] VARCHAR(4) 'DocumentTypeShortcut',
					[DocumentNumerationShortcut] VARCHAR(5) 'DocumentNumerationShortcut',
					[SeriesNo] INT 'SeriesNo',
					[KindId] INT 'KindId',
					[CatalogId] INT 'CatalogId',
					[Marker] SMALLINT 'Marker',
					[IsIssued] BIT 'IsIssued',
					[DocumentStatus] SMALLINT 'DocumentStatus',
					[DocumentStatusDescription] VARCHAR(40) 'DocumentStatusDescription',
					[HmImported] BIT 'HmImported',
					[Archived] BIT 'Archived',
					[FkExported] BIT 'FkExported',
					[IsCreatedFromForeignOrder] BIT 'IsCreatedFromForeignOrder',
					[Canceled] BIT 'Canceled',
					[IsActive] BIT 'IsActive',
					[MigrationStatus] SMALLINT 'MigrationStatus',
					[ContractorId] INT 'ContractorId',
					[AccountingPattern] VARCHAR(4) 'AccountingPattern',
					[IssuerId] INT 'IssuerId',
					[ReceiverId] INT 'ReceiverId',
					[DepartmentId] INT 'DepartmentId',
					[PayRegistryId] INT 'PayRegistryId',
					[PaymentFormClientId] INT 'PaymentFormClientId',
					[PaymentDeadline] NVARCHAR(100) 'PaymentDeadline',
					[Value] DECIMAL(24,2) 'Value',
					[ValueVat] DECIMAL(24,2) 'ValueVat',
					[ValueGross] DECIMAL(24,2) 'ValueGross',
					[ValuePLN] DECIMAL(24,2) 'ValuePLN',
					[ValueVatPLN] DECIMAL(24,2) 'ValueVatPLN',
					[ValueGrossPLN] DECIMAL(24,2) 'ValueGrossPLN',
					[PriceKind] SMALLINT 'PriceKind',
					[Currency] VARCHAR(3) 'Currency',	
					[ExchangeRate] NVARCHAR(20) 'ExchangeRate',
					[ExchangeRatePIT] NVARCHAR(20) 'ExchangeRatePIT',
					[CalculateVatMethod] SMALLINT 'CalculateVatMethod',
					[InventorySettlementStatus] INT 'InventorySettlementStatus',
					[PriceTypeId] INT 'PriceTypeId',
					[Discount] DECIMAL(24,2) 'Discount',
					[ReservationType] SMALLINT 'ReservationType',
					[FiscalizationState] SMALLINT 'FiscalizationState',
					[PublishInventoryDocumentMode] SMALLINT 'PublishInventoryDocumentMode',					
					ReceiptNumber VARCHAR(40) 'ReceiptNumber',
					UniqueCashBoxNumber VARCHAR(40) 'UniqueCashBoxNumber',
					[JPK_V7] VARCHAR(256) 'JPK_V7',
					[JPK] SMALLINT 'JPK',
					[SplitPayment] BIT 'SplitPayment',
					[InRegisterDate] NVARCHAR(100) 'InRegisterDate',
					[SaleRegister] INT 'SaleRegister',
					[RegisterMethod] SMALLINT 'RegisterMethod',
					[CashSettled] SMALLINT 'CashSettled',
					[DepartmentInSeries] INTEGER 'DepartmentInSeries',
					[Period] SMALLINT 'Period',
					[CorrectedDocumentId] INT 'CorrectedDocumentId',
					[IsGroupCorrection] BIT 'IsGroupCorrection',
					[RelatedForeignOrderDocumentId] INT 'RelatedForeignOrderDocumentId',
                    [IsOssProcedure] BIT 'IsOssProcedure',
                    [OssShippingCountryId] INT 'OssShippingCountryId',
                    [OssDeliveryCountryId] INT 'OssDeliveryCountryId',
                    [TypeExternal] INT 'TypeExternal',
                    [IdExternal] INT 'IdExternal',
                    [Id2External] VARCHAR(32) 'Id2External'
				)	


			-- Pobranie identyfikatora i numeru dokumentu sprzedażowego
			DECLARE @docId INT,
					@docNumber VARCHAR(40),
					@department INT,
					@issueDate DATETIME,
					@saleRegister INT,
					@foreignOrder BIT,
					@isIssued BIT,
					@isBufor SMALLINT

			SELECT 
				@docId = [Id], 
				@docNumber = [DocumentNumber], 
				@department = [DepartmentId], 
				@issueDate = [IssueDate], 
				@saleRegister = [SaleRegister], 
				@foreignOrder = [IsCreatedFromForeignOrder], 
				@isIssued = [IsIssued]
			FROM @tblSaleDocument

			IF @isIssued = 1
			BEGIN	--nie robimy nic 
				GOTO CLEAN_AND_FINISH;
			END

			IF IsNull(@docId, 0) != 0
			BEGIN
				SELECT @isBufor = bufor FROM HM.DK WHERE id = @docId
				IF @isBufor = 0
				BEGIN	--nie robimy nic 
					GOTO CLEAN_AND_FINISH;
				END
			END


			DECLARE @CorrectedDocumentId int = ISNULL((SELECT CorrectedDocumentId FROM @tblSaleDocument), 0);

			DECLARE @correction BIT
			SELECT @correction = COUNT(Id) FROM @tblSaleDocument WHERE ISNULL(CorrectedDocumentId, 0) > 0 OR IsGroupCorrection = 1
			
			-- dokument i pozycje w cenie netto / brutto
			DECLARE @IsNet SMALLINT
			SELECT @IsNet = CASE WHEN [PriceKind] = 2 THEN 1 ELSE 0 END FROM @tblSaleDocument

			DECLARE @ExchangeRate FLOAT
			SELECT @ExchangeRate = [ExchangeRate] FROM @tblSaleDocument

			DECLARE @created SMALLINT = 1, @updated SMALLINT = 2, @deleted SMALLINT = 3

			-- Deserializacja danych pozycji dokumentu sprzedażowego z xml
			INSERT INTO @tblDocumentPositions
			SELECT 
					[Id],		
					[Compensation],
					[NodeId],
					[ParentId],
					[OrdinalNumber],
					CASE WHEN [Guid] IS NULL OR [Guid] = '' OR [Guid] = '00000000-0000-0000-0000-000000000000' THEN NEWID() ELSE [GUID] END,
					[ProductId],
					[ProductShortcut],
					[Description],
					[UnitOfMeasurement],
					[EvidenceUnitOfMeasurement],
					CASE WHEN 0 = ISNUMERIC([Quantity]) THEN 0.0000 ELSE CAST([Quantity] AS DECIMAL(24, 4)) END AS [Quantity],
					CASE WHEN 0 = ISNUMERIC([UnitQuantity]) THEN 0.0000 ELSE CAST([UnitQuantity] AS DECIMAL(35, 15)) END AS [UnitQuantity],
					CASE WHEN 0 = ISNUMERIC([EvidenceQuantity]) THEN 0.0000 ELSE CAST([EvidenceQuantity] AS DECIMAL(24, 4)) END AS [EvidenceQuantity],
					[ReservationType],
					[AccountingPattern],
					[VatRateId],
					CASE WHEN 0 = ISNUMERIC([Price]) THEN 0.0000 ELSE CAST([Price] AS DECIMAL(35,15)) END AS [Price],
					CASE WHEN 0 = ISNUMERIC([PriceGross]) THEN 0.0000 ELSE CAST([PriceGross] AS DECIMAL(35,15)) END AS [PriceGross],
					CASE WHEN 0 = ISNUMERIC([Value]) THEN 0.00 ELSE CAST([Value] AS DECIMAL(24, 2)) END AS [Value],
					CASE WHEN 0 = ISNUMERIC([ValueVat]) THEN 0.00 ELSE CAST([ValueVat] AS DECIMAL(24, 2)) END AS [ValueVat],
					CASE WHEN 0 = ISNUMERIC([ValueGross]) THEN 0.00 ELSE CAST([ValueGross] AS DECIMAL(24, 2)) END AS [ValueGross],
					CASE WHEN 0 = ISNUMERIC([ValuePLN]) THEN 0.00 ELSE CAST([ValuePLN] AS DECIMAL(24, 2)) END AS [ValuePLN],
					CASE WHEN 0 = ISNUMERIC([ValueVatPLN]) THEN 0.00 ELSE CAST([ValueVatPLN] AS DECIMAL(24, 2)) END AS [ValueVatPLN],
					CASE WHEN 0 = ISNUMERIC([ValueGrossPLN]) THEN 0.00 ELSE CAST([ValueGrossPLN] AS DECIMAL(24, 2)) END AS [ValueGrossPLN],
					[PriceTypeId],
					CASE WHEN 0 = ISNUMERIC([PriceListPrice]) THEN 0.0000 ELSE CAST([PriceListPrice] AS DECIMAL(35,15)) END AS [PriceListPrice],
					CASE WHEN 0 = ISNUMERIC([PriceListPriceGross]) THEN 0.0000 ELSE CAST([PriceListPriceGross] AS DECIMAL(35,15)) END AS [PriceListPriceGross],
					[JPK_V7],
					[CN],
					[CodePKWIU],
					[VatRegister],
					IIF(CorrectedPositionId = 0, NULL, CorrectedPositionId),
					[IsNewCorrectPosition],
					[IsSet],
					[IsElementofSet],
					[NoEditOfSet],
					[PriceOfSetCalculatedBasedOfElements],
					[VatUE],
				    [ServiceExport],
					[DomesticVatRateId],
					CASE WHEN 0 = ISNUMERIC([OriginalPriceListPrice]) THEN 0.0000 ELSE CAST([OriginalPriceListPrice] AS DECIMAL(35,15)) END AS [OriginalPriceListPrice],
					[OriginalPriceListPriceCurrency],
					CASE WHEN 0 = ISNUMERIC([OriginalBasePrice]) THEN 0.0000 ELSE CAST([OriginalBasePrice] AS DECIMAL(35,15)) END AS [OriginalBasePrice],
					[OriginalBasePriceCurrency],
					CASE WHEN 0 = ISNUMERIC([BasePricePLN]) THEN 0.0000 ELSE CAST([BasePricePLN] AS DECIMAL(35,15)) END AS [BasePricePLN],
					[CorrectedDocumentId],
					[OO],
					CASE WHEN 0 = ISNUMERIC([ServiceExpense]) THEN 0.0000 ELSE CAST([ServiceExpense] AS DECIMAL(35,15)) END AS [ServiceExpense],
					[IsService],
					CASE @allDataWithChangeStatus
						WHEN 1 THEN
							CASE [ModificationStatus] 
								WHEN 'Created' THEN @created 
								WHEN 'Updated' THEN @updated
								WHEN 'Deleted' THEN @deleted
								ELSE @updated	--brak atrybutu (NULL) na razie traktujemy jako zmodyfikowane
							END
						WHEN 0 THEN
							IIF(Id = 0, @created, @updated)
					END,
					[IsOssService]
			FROM 
				OPENXML(@handle, '/SaleDocument/DocumentPositions/SalePosition')
				WITH
				(
					[Id] INT 'Id',	
					[Compensation] SMALLINT 'Compensation',
					[NodeId] INT 'NodeId',
					[ParentId] INT 'ParentId',
					[OrdinalNumber] SMALLINT 'OrdinalNumber',
					[Guid] NVARCHAR(36) 'Guid',
					[ProductId] INT 'ProductId',
					[ProductShortcut] VARCHAR(40) 'ProductShortcut',
					[Description] VARCHAR(max) 'Description',
					[UnitOfMeasurement] VARCHAR(8) 'UnitOfMeasurement',
					[EvidenceUnitOfMeasurement] VARCHAR(8) 'EvidenceUnitOfMeasurement',
					[Quantity] VARCHAR(24) 'Quantity',
					[UnitQuantity] VARCHAR(36) 'UnitQuantity',
					[EvidenceQuantity] VARCHAR(24) 'EvidenceQuantity',
					[ReservationType] SMALLINT 'ReservationType',
					[AccountingPattern] VARCHAR(4) 'AccountingPattern',
					[VatRateId] INT 'VatRateId',
					[Price] VARCHAR(36) 'Price',
					[PriceGross] VARCHAR(36) 'PriceGross',
					[Value] VARCHAR(24) 'Value',
					[ValueVat] VARCHAR(24) 'ValueVat',
					[ValueGross] VARCHAR(24) 'ValueGross',
					[ValuePLN] VARCHAR(24) 'ValuePLN',
					[ValueVatPLN] VARCHAR(24) 'ValueVatPLN',
					[ValueGrossPLN] VARCHAR(24) 'ValueGrossPLN',
					[PriceTypeId] SMALLINT 'PriceTypeId',
					[PriceListPrice] VARCHAR(36) 'PriceListPrice',
					[PriceListPriceGross] VARCHAR(36) 'PriceListPriceGross',
					[JPK_V7] VARCHAR(10) 'JPK_V7',
					[CN] VARCHAR(10) 'CN',
					[CodePKWIU] VARCHAR(40) 'CodePKWIU',
					[VatRegister] INT 'VatRegister',
					CorrectedPositionId INT 'CorrectedPositionId',
					[IsNewCorrectPosition] BIT 'IsNewCorrectPosition',
					[IsSet] BIT 'IsSet',
					[IsElementOfSet] BIT 'IsElementOfSet',
					[NoEditOfSet] BIT 'NoEditOfSet',
					[PriceOfSetCalculatedBasedOfElements] BIT 'PriceOfSetCalculatedBasedOfElements',
					[VatUE] BIT 'VatUE',
					[ServiceExport] BIT 'ServiceExport',
					[DomesticVatRateId] INT 'DomesticVatRateId',
					[OriginalPriceListPrice] VARCHAR(36) 'OriginalPriceListPrice',
					[OriginalPriceListPriceCurrency] VARCHAR(3) 'OriginalPriceListPriceCurrency',
					[OriginalBasePrice] VARCHAR(35) 'OriginalBasePrice',
					[OriginalBasePriceCurrency] VARCHAR(3) 'OriginalBasePriceCurrency',
					[BasePricePLN] VARCHAR(35) 'BasePricePLN',
					[CorrectedDocumentId] INT 'CorrectedDocumentId',
					[OO] BIT 'OO',
					[ServiceExpense] VARCHAR(24) 'ServiceExpense',
					[IsService] BIT 'IsService',
					[ModificationStatus] VARCHAR(30) '@ModificationStatus',
					[IsOssService] BIT 'IsOssService'
				)		
				WHERE (@allDataWithChangeStatus = 1 AND [ModificationStatus] IS NOT NULL)
					OR (@allDataWithChangeStatus = 0 AND ([ModificationStatus] <> 'Deleted' OR [ModificationStatus] IS NULL))  -- w trybie pełnym pomijamy pozycje oznaczone jako 'Deleted'

			IF @correction <> 0
			BEGIN
				-- XML korekty zawiera wszystkie pozycje, takze niekorygowane ktorych dalej nie uwzgledniamy
				DELETE
					FROM @tblDocumentPositions
					WHERE Compensation = 0 AND ISNULL(CorrectedPositionId, 0) = 0 AND IsNewCorrectPosition <> 'true'

				UPDATE @tblDocumentPositions
					SET EvidenceQuantity = -EvidenceQuantity, ValuePLN = -ValuePLN, ValueVatPLN = -ValueVatPLN, Quantity = -Quantity,
						Value = -Value, ValueGross = -ValueGross
					WHERE Compensation <> 0
			END

			-- Pozycje usunięte przechowywane w oddzielnej zmiennej tabelarycznej
			DECLARE @tblDeletedPositions TABLE (Id INT)

			IF @allDataWithChangeStatus = 1
			BEGIN
				INSERT INTO @tblDeletedPositions([Id])
				SELECT Id FROM @tblDocumentPositions WHERE ModificationStatus = @deleted

				DELETE @tblDocumentPositions WHERE ModificationStatus = @deleted
			END
			ELSE
			BEGIN
				INSERT INTO @tblDeletedPositions(Id)
						SELECT DP.id
						FROM HM.DP
						WHERE DP.super = @docId AND DP.id NOT IN (SELECT id FROM @tblDocumentPositions)
			END

			--odczyt parametru pracy
			DECLARE @IsShownOnSaleStatementWorkingParameter BIT = ISNULL((
									SELECT IIF(cena = 1, 1, 0) 
									FROM [HM].ZZ 
									WHERE typ = 32 AND baza1 = 20 AND id1 = 1 AND baza2 = 6 AND id2 = 1184), 1)

			DECLARE @CalculateVatMethod INT = (SELECT [CalculateVatMethod] FROM @tblSaleDocument)

			-- dane dla tabeli pozycji, zgodne z formatem DP
			DECLARE @valuesForDP TABLE (NodeId INT, flag SMALLINT, typ SMALLINT, cenabaztow FLOAT, walcenybaz VARCHAR(3) COLLATE database_default, zyskdod FLOAT, wartstvat SMALLINT, 
				koszt FLOAT, iloscRLKS FLOAT, wartTowaru FLOAT)

			INSERT INTO @valuesForDP
			SELECT	position.NodeId, 

					CONVERT(SMALLINT, 
						IIF(@CalculateVatMethod = 1, CAST(0x8000 AS SMALLINT), 0) |		-- sumacyjny vat
						IIF(product.[subtyp] = 0, 0x0200, 0) |			-- pozycja towarowa
						IIF(position.[IsElementOfSet] = 1, 0x4000, 0) |	-- elementu kompletu
						IIF(position.[IsSet] = 1, 0x1000, 0) |			-- nagłówek kompletu
						IIF(position.[NoEditOfSet] = 1, 0x0800, 0)		-- nagłówek lub element kompletu nieedycyjnego
					) AS [flag],

					IIF(position.VatUE = 1,0x0200,0) | IIF(position.ServiceExport = 1,0x0100,0) |
					CASE --pierszy bit (0x01) mówiący o tym czy pozycja wchodzi na zestawienia czy nie, zależy to od ustawienia w definicji kompletu lub od parametru pracy
						WHEN position.IsSet = 1 THEN IIF(product.Id IS NOT NULL, [HM].fnIsFlagSet(product.flag, 0x2000), @IsShownOnSaleStatementWorkingParameter)
						WHEN position.IsElementOfSet = 1 THEN IIF(prodSetForElement.Id IS NOT NULL, ~[HM].fnIsFlagSet(prodSetForElement.flag, 0x2000), ~@IsShownOnSaleStatementWorkingParameter)
						ELSE 0 
					END	AS [typ],

					CASE 
						WHEN position.IsSet = 1 AND position.PriceOfSetCalculatedBasedOfElements = 1 THEN 0
						ELSE CAST(position.[OriginalBasePrice] AS FLOAT)
					END AS [cenabaztow],

					CASE 
						WHEN position.IsSet = 1 AND position.PriceOfSetCalculatedBasedOfElements = 1 THEN ''
						ELSE position.[OriginalBasePriceCurrency]
					END AS [walcenybaz],

					CASE 
						WHEN position.IsSet = 1 AND position.PriceOfSetCalculatedBasedOfElements = 1 THEN 0
						ELSE CAST(position.[BasePricePLN] AS FLOAT)
					END AS [zyskdod],

					CAST(ISNULL(vatRate.wartosc, 0) * 10000 AS SMALLINT) AS [wartstvat],

					CAST(position.[ServiceExpense] AS FLOAT) AS [koszt],
					CAST(IIF(position.[IsService] = 1 OR ISNULL(product.[id],0) = 0,position.[EvidenceQuantity],0) AS FLOAT) AS [iloscRLKS],

					IIF(position.[IsService] = 1 OR ISNULL(product.[id],0) = 0, HM.ROUND2(position.[EvidenceQuantity] * position.[BasePricePLN]), 0) AS [wartTowaru]

			FROM @tblDocumentPositions AS position
				LEFT JOIN @tblDocumentPositions AS posSetForElement ON position.IsElementOfSet = 1 AND posSetForElement.IsSet = 1 AND position.ParentId = posSetForElement.NodeId
				LEFT JOIN [HM].[TW] AS product ON product.Id = position.ProductId
				LEFT JOIN [HM].[TW] AS prodSetForElement ON prodSetForElement.Id = posSetForElement.ProductId
				LEFT JOIN [HM].[XT] AS vatRate ON vatRate.id = position.[VatRateId]
			WHERE position.ModificationStatus IN (@created, @updated)


			-- Deserializacja danych dokumentów powiązannych z xml
			INSERT INTO @tblRelatedDocuments
			SELECT 
				[Id],
				[DocumentType],
				[Number],
				[CharacterId],
				[InBuffer]
			FROM 
				OPENXML(@handle, '/SaleDocument/RelatedDocuments/RelatedDocument')
				WITH
				(
					[Id] INT 'Id',
					[DocumentType] NVARCHAR(100) 'DocumentType',
					[Number] NVARCHAR(100) 'Number',
					[CharacterId] INT 'CharacterId',
					[InBuffer] BIT 'InBuffer'
				)          

			DECLARE @ContractorId INT, 
					@ContractorAddressId INT = 0, 
					@ReceiverId INT,	
					@ReceiverAddressId INT = 0;

            -- Deserializacja danych Opakowania zwrotne
			INSERT INTO @tblRefundableDeposits
			SELECT 
				[Id],
                [DKId],
				[PackageName],
				[RefundableDepositName],
                [PackageId],
				[Quantity],
				[Price],
				[Value]
			FROM 
				OPENXML(@handle, '/SaleDocument/RefundableDeposits/RefundableDeposit')
				WITH
				(
					[Id] INT 'Id',
					[DKId] INT 'DKId',
					[PackageName] NVARCHAR(100) 'PackageName',
					[RefundableDepositName] NVARCHAR(100) 'RefundableDepositName',
                    [PackageId] INT 'PackageId',
					[Quantity] DECIMAL(24,6) 'Quantity',
					[Price] DECIMAL(24,2) 'Price',
					[Value] DECIMAL(24,2) 'Value'
				)

			-- Jeśli 0 to incydentalny - ustawiamy NULL
			SELECT @ContractorId = CASE WHEN [ContractorId] = 0 THEN NULL ELSE [ContractorId] END,
				   @ReceiverId = CASE WHEN [ReceiverId] = 0 THEN NULL ELSE [ReceiverId] END
			FROM @tblSaleDocument;
			-- Aktualizacja na NULL jeśli były 0
			UPDATE @tblSaleDocument
			SET [ContractorId] = @ContractorId, [ReceiverId] = @ReceiverId
			

			-- Zapis danych adresowych kontrahenta i odbiorcy
			EXEC [HM].[UpdateDocumentContractorAdressFromXML] @handle, @ContractorId, @ReceiverId, @ContractorAddressId OUT, @ReceiverAddressId OUT

            

			DECLARE @correctedDocsIds TABLE (Id INT)
			INSERT INTO @correctedDocsIds SELECT Id FROM HM.[GetDocumentsIdsForGroupCorrection](@docId)
			IF ((SELECT iddokkoryg FROM HM.DK WHERE id = @docId) IS NOT NULL)
				INSERT INTO @correctedDocsIds SELECT iddokkoryg FROM HM.DK WHERE id = @docId

			DECLARE @correctedOrDocId TABLE (Id INT) --jeśli jest jakiś dokument korygowany, to jego id, jeśli nie to docId
			IF NOT EXISTS (SELECT * FROM @correctedDocsIds)
			BEGIN
				INSERT INTO @correctedOrDocId SELECT @docId
			END
			ELSE
			BEGIN
				INSERT INTO @correctedOrDocId SELECT * FROM @correctedDocsIds
			END

			DECLARE @defaultVatRateId INT,
					@defaultVatRateValue FLOAT,
					@flag SMALLINT;
			SELECT TOP 1
					@defaultVatRateId = id,
					@defaultVatRateValue = wartosc
			FROM HM.XT
			WHERE wartosc = 0.23;

            DECLARE @NpRateId INT = (
				SELECT TOP (1) Id
				FROM HM.XT
				WHERE super = 10000
					AND subtyp = 7
					AND flag & 0x200 <> 0
				)
			
			-- Numer dokumentu
			--
			-- Jeśli brak dokumentu w bazie lub właśnie wystawiamy dokument przy automatycznym generowaniu numeracji to zawsze generuj serie i numer
			IF ISNULL(@docId, 0) = 0
				or 
				(
					exists(select 1 from hm.WorkParameters where id = 1070 and NumericValue = 1)
					and exists(select top 1 1 from @tblSaleDocument t inner join hm.DK dk on dk.Id = t.Id and dk.bufor = 1 where t.IsIssued = 1)
				)
			BEGIN
				DECLARE 
					@DocumentTypeId INT,
					@DocumentNumerationCode VARCHAR(10),
					@genContractorId INT,
					@departmentId INT,
					@currency VARCHAR(3),
					@buffer BIT,
					@date DATE,
					@tableId SMALLINT,
					@code VARCHAR(40),
					@numberInSeries INT,
					@period INT,
					@departmentInSeries INT

				SELECT 
					@DocumentTypeId = (select ID from hm.td where kod = DocumentTypeShortcut),
					@DocumentNumerationCode = DocumentNumerationShortcut,
					@UserId = @userId,
					@genContractorId = ContractorId,
					@DepartmentId = DepartmentId,
					@Currency = ISNULL(Currency, ''),
					@buffer = IIF(IsIssued = 1, 0, 1),
					@Date = IssueDate,
					@TableId = 16,
					@numberInSeries = 0
				FROM @tblSaleDocument

				EXEC [HM].[GenerateDocumentNumber]    
					@docId,
					@DocumentTypeId,
					@DocumentNumerationCode,
					@userId,
					@genContractorId,
					@departmentId,
					@currency,
					@buffer,
					@date,
					@tableId,    
					@code OUT,
					@numberInSeries OUT,
					@period OUT,
					@departmentInSeries OUT

				UPDATE @tblSaleDocument set
					DocumentNumber = ISNULL(IIF(@code = '@0', NULL, @code), NEWID()),
					SeriesNo = @numberInSeries,
					Period = @period,
					DepartmentInSeries = @departmentInSeries

				SET @docNumber = ISNULL(IIF(@code = '@0', NULL, @code), NEWID());

			end;

			DECLARE @shortcutMessage NVARCHAR(200) = CONCAT('Dokument sprzedaży o numerze "', @docNumber, '" istnieje w bazie danych.')
			IF EXISTS(SELECT * FROM [HM].[DK] dk WHERE dk.[typ] = 0 and dk.[kod] = @docNumber and dk.bufor = @buffer and Id <> @docId)
					THROW 50000, @shortcutMessage, 1;
			
			--Dokument z odwrotnym obciążeniem, jeśli jakaś pozycja ma zaznaczony ten znacznik
			DECLARE @oO BIT;
			IF EXISTS(SELECT * FROM @tblDocumentPositions WHERE OO = 1) 
					OR EXISTS(SELECT id FROM HM.DP WHERE super = @docId AND odwrotneObc > 0 
														AND (id NOT IN (SELECT Id FROM @tblDocumentPositions))	--szukamy w pozycjach nie zmodyfikowanych ktorych może nie być w @tblDocumentPositions
														AND (id NOT IN (SELECT Id FROM @tblDeletedPositions)))	--pomijamy właśnie usuwane pozycje
				SET @oO = 1
			ELSE
				SET @oO = 0

			-- Koniec Numeru dokumentu

			-- Zapis główki dokumentu
			IF (IsNull(@docId, 0) = 0)
			BEGIN
				INSERT INTO [HM].[DK]
				(
					[guid],
					[kod],
					[nazwa],
					[data],
					[datasp],
					[opis],
					[odebrane],
					[subtyp],
					[typ_dk],
					[seria],
					[serianr],
					[rodzaj],
					[katalog],
					[znacznik],
					[bufor],					
					[anulowany],
					[aktywny],
					[statusMig],
					[schemat],
					[wystawil],
					[khid],
					[odid],
					[khadid],
					[odadid],
					[magazyn],
					[plattyp],
					[formaplatn],
					[plattermin],
					[walNetto],
					[walBrutto],
					[netto],
					[vat],
					[grupacen],
					[waluta],
					[kurs],
					[kursDoch],				
					[typceny],
					paragon,
					fiscalDeviceNo,
					[rabat],
					[jpk_v7],
					[jpk],
					[flag],
					[typ],
					[info],
					[seriadzial],					
					[okres],					
					[ok],
					[rr],
					[wplaty],
					[wartoscSP],
					[exp_fk],
					[zyskdod],
					[kod_obcy],
					[rozlmg],
					[efaktura],
					[statusRdf],          
					[data_obcy],
					[koszt],
					[kosztaproksymowany],
					[statusfk],
					[splitPayment],
					[datarej],
					[rejestrVat],
					[dataZest1],
					[createdBy],
					[createdDate],
					[modifiedBy],
					[modifiedDate],
					[wartPrzychod],
                    [procedura_OSS],
                    [krajWysylki_OSS],
                    [krajDostawy_OSS],
                    [typeExternal],
                    [idExternal],
                    [id2External]
				)
				SELECT					
					[Guid] AS [guid],
					[DocumentNumber] AS [kod],
					[Name] AS [nazwa],
					[IssueDate] AS [data],
					[OperationDate] AS [datasp],
					IIF(@correction > 0, [CorrectionCause], [Description]) AS [opis], --dla korekty, jej przyczyna
					[RecipientPerson] AS [odebrane],
					(SELECT td.charakter FROM [HM].[TD] td WHERE td.[kod] = [DocumentTypeShortcut]) AS [subtyp],
					[DocumentTypeShortcut] AS [typ_dk],
					[DocumentNumerationShortcut] AS [seria],
					[SeriesNo] as serianr,
					[KindId] AS [rodzaj],
					[CatalogId] AS [katalog],
					[Marker] AS [znacznik],
					1^(CAST([IsIssued] as bit)) AS [bufor],
					[Canceled] AS [anulowany],
					1 AS [aktywny],
					[MigrationStatus] AS [statusMig],
					[AccountingPattern] AS [schemat],
					[IssuerId] AS [wystawil],
					[ContractorId] AS [khid],
					[ReceiverId] AS [odid],
					@ContractorAddressId AS [khadid],
					@ReceiverAddressId AS [odadid],
					[DepartmentId] AS [magazyn],
					[PayRegistryId] AS [plattyp],
					[PaymentFormClientId] AS [formaplatn],
					[PaymentDeadline] AS [plattermin],
					[Value] AS [walNetto],
					[ValueGross] AS [walBrutto],
					[ValuePLN] AS [netto],
					[ValueVatPLN] AS [vat],
					@IsNet AS [grupacen],
					[Currency] AS [waluta],
					[ExchangeRate] AS [kurs],
					[ExchangeRatePIT] AS [kursDoch],				
					[PriceTypeId] AS [typceny],
					CONVERT(INTEGER, ReceiptNumber) AS paragon,
					UniqueCashBoxNumber AS fiscalDeviceNo,
					[Discount] AS [rabat],					
					[JPK_V7] AS [jpk_v7],
					[JPK],
					(hm.SetFlag(0, 0x8000, CalculateVatMethod) | hm.SetFlag(0, 0x80, RegisterMethod - 1) | IIF(IsCreatedFromForeignOrder = 'true', 0x1000, 0) | IIF(IsGroupCorrection = 'true', 0x20,0)) AS [flag],
					0 AS [typ],
					0 AS [info],
					[DepartmentInSeries] AS [seriadzial],
					[Period] AS [okres],
					[CashSettled] AS [ok],
					0 AS [rr],
					0 AS [wplaty],
					0 AS [wartoscSP],
					2 * [FiscalizationState] | 8 * [PublishInventoryDocumentMode] | 64 * @oO AS [exp_fk],
					0 AS [zyskdod],
					'' AS [kod_obcy],
					[ReservationType] AS [rozlmg],
					0 AS [efaktura],
					0 AS [statusRdf],           
					NULL AS [data_obcy],
					0 AS [koszt],
					0 AS [kosztaproksymowany],
					0 AS [statusfk],
					SplitPayment AS [splitPayment],
					InRegisterDate AS [datarej],
					SaleRegister AS [rejestrVat],
					CONVERT(DATE, GETDATE()) AS [dataZest1],
					[IssuerId] AS [createdBy],
					GETDATE() AS [createdDate],
					[IssuerId] AS [modifiedBy],
					GETDATE() AS [modifiedDate],
					[ValuePLN] AS [wartPrzychod],
                    [IsOssProcedure],
                    [OssShippingCountryId],
                    [OssDeliveryCountryId],
                    [TypeExternal],
                    [IdExternal],
                    [Id2External]
				FROM @tblSaleDocument

				-- Pobranie id nowo utworzonego dokumentu
				SET @docId = SCOPE_IDENTITY()

				-- Zapis transakcji nowego dokumentu
				INSERT INTO HM.TR
				(
					[owner_id],
					[kod],
					[id_kto],
					[dzial],
					[id_komu],
					[datarozl],
					[data],
					[termin],
					[flag],
					[typ],
					[subtyp],
					[owner_base],
					[ok],
					[nrozl_oper],
					[wartosc],
					[wartosc_rozl],
					[nrozlilosc],
					[status]
				)
				SELECT 
					@docId AS [owner_id],
					dk.[kod] AS [kod],
					[ReceiverId] AS [id_kto],
					[DepartmentId] AS [dzial],
					NULL AS [id_komu],
					NULL AS [datarozl],
					[IssueDate] AS [data],
					DATEADD(day, 7, [IssueDate]) AS [termin],
					16896 AS [flag],
					1 AS [typ],
					1 AS [subtyp],
					16 AS [owner_base],
					0 AS [ok],
					0 AS [nrozl_oper],
					0 AS [wartosc],
					0 AS [wartosc_rozl],
					0 AS [nrozlilosc],
					4 AS [status]
				FROM @tblSaleDocument
				JOIN [HM].[DK] dk ON dk.[id] = @docId;

			END ELSE BEGIN

				UPDATE [HM].[DK]
				SET dk.[kod] = tblSD.[DocumentNumber],
					dk.[nazwa] = tblSD.[Name],
					dk.[data] = tblSD.[IssueDate],
					dk.[datasp] = tblSD.[OperationDate],
					dk.[opis] = IIF(@correction > 0, tblSD.[CorrectionCause], tblSD.[Description]), -- dla korekty przyczyna korekty
					dk.[odebrane] = tblSD.[RecipientPerson],
					dk.[subtyp] = tblSD.[BaseType],
					dk.[typ_dk] = tblSD.[DocumentTypeShortcut],
					dk.[seria] = tblSD.[DocumentNumerationShortcut],
					dk.[serianr] = tblSD.[SeriesNo],
					dk.[rodzaj] = tblSD.[KindId],
					dk.[katalog] = tblSD.[CatalogId],
					dk.[znacznik] = tblSD.[Marker],
					dk.[bufor] = 1^(CAST(tblSD.[IsIssued] as bit)),
					dk.[anulowany] = tblSD.[Canceled],
					dk.[aktywny] = tblSD.[IsActive],
					dk.[statusMig] = tblSD.[MigrationStatus],
					dk.[schemat] = tblSD.[AccountingPattern],
					dk.[wystawil] = tblSD.[IssuerId],
					dk.[khid] = tblSD.[ContractorId],
					dk.[odid] = tblSD.[ReceiverId],
					dk.[khadid] = @ContractorAddressId,
					dk.[odadid] = @ReceiverAddressId,
					dk.[magazyn] = tblSD.[DepartmentId],
					dk.[plattyp] = IIF(tblSD.[PayRegistryId] = -1, NULL, tblSD.[PayRegistryId]),
					dk.[formaplatn] = tblSD.[PaymentFormClientId],
					dk.[plattermin] = tblSD.[PaymentDeadline],
					dk.[walNetto] = tblSD.[Value],
					dk.[walBrutto] = tblSD.[ValueGross],
					dk.[netto] = tblSD.[ValuePLN],
					dk.[vat] = tblSD.[ValueVatPLN],
					dk.[grupacen] = @IsNet,
					dk.[waluta] = tblSD.[Currency],
					dk.[kurs] = tblSD.[ExchangeRate],
					dk.[kursDoch] = tblSD.[ExchangeRatePIT],				
					dk.[typceny] = tblSD.[PriceTypeId],
					dk.[rabat] = tblSD.[Discount],
					dk.paragon = CONVERT(INTEGER, tblSD.ReceiptNumber),
					dk.fiscalDeviceNo = tblSD.UniqueCashBoxNumber,
					dk.[jpk_v7] = tblSD.[JPK_V7],
					dk.[jpk] = tblSD.[JPK],
					dk.[flag] = hm.SetFlag(0, 0x8000, CalculateVatMethod) | hm.SetFlag(0, 0x80, RegisterMethod - 1) | IIF(IsCreatedFromForeignOrder = 'true', 0x1000, 0) | IIF(IsGroupCorrection = 'true', 0x20,0),
					dk.[typ] = 0,
					dk.[info] = 0,
					dk.[seriadzial] = IsNull(tblSD.DepartmentInSeries, 0),
					dk.[okres] = IsNull(tblSD.Period, 0),
					dk.[ok] = tblSD.[CashSettled],
					dk.[rr] = 0,
					dk.[wplaty] = 0,
					dk.[exp_fk] = ( dk.[exp_fk] & ~90 ) | 2 * [FiscalizationState] | 8 * [PublishInventoryDocumentMode] -- PublishInventoryDocumentMode wpływa na dwie flagi 8 i 16, a przyjmuje wartości 0, 1 i 2
														| 64 * @oO,
					dk.[zyskdod] = 0,
					dk.[kod_obcy] = '',
					dk.[rozlmg] = tblSD.[ReservationType],
					dk.[efaktura] = 0,
					dk.[statusRdf] = 0,          
					dk.[data_obcy] = NULL,
					dk.[koszt] = 0,
					dk.[kosztaproksymowany] = 0,
					dk.[statusfk] = 0,
					dk.[splitPayment] = tblSD.[SplitPayment],
					dk.[datarej] = tblSD.[InRegisterDate],
					dk.[rejestrVat] = tblSD.[SaleRegister],
					dk.[wartPrzychod] = tblSD.[ValuePLN],
                    dk.[procedura_OSS] = tblSD.[IsOssProcedure],
                    dk.[krajDostawy_OSS] = tblSD.[OssDeliveryCountryId],
                    dk.[krajWysylki_OSS] = tblSD.[OssShippingCountryId],
                    dk.[typeExternal] = tblSD.[TypeExternal],
                    dk.[idExternal] = tblSD.[IdExternal],
                    dk.[id2External] = tblSD.[Id2External]
				FROM [HM].[DK] dk, @tblSaleDocument tblSD
				WHERE dk.[id] = @docId		
			END
			-- Koniec: Zapis główki dokumentu

				DECLARE @docType SMALLINT;
				
				SELECT @isBufor = bufor,
					   @docType = subtyp 
					   FROM HM.DK WHERE id = @docId;

				-- Tabela przechowująca id operacji pozycji zamówienia powiązane z operacjami pozycji dokumentu, 
				-- po usunięciu pozycji z dokumentu sprzedaży
				DECLARE @zmoOperationsToDeletedPositions TABLE(id int);

				-- Zapamiętanie id operacji zamówienia powiązanych z usuwanymi pozycjami
				INSERT INTO @zmoOperationsToDeletedPositions
				SELECT ro.id1
				FROM @tblDeletedPositions delPositions
					JOIN HM.OP op_dp ON op_dp.owner_id = delPositions.id AND op_dp.owner_base = 18
					JOIN HM.RO ON ro.id2 = op_dp.id

				--Usunięcie powiązań operacji
				DELETE ro 
					FROM [HM].[RO] ro
					JOIN [HM].[OP] op ON op.id = ro.id2
					JOIN @tblDeletedPositions delPositions ON delPositions.id = op.owner_id AND op.owner_base = 18

				-- Usunięcie skasowanych operacji
				DELETE op
					FROM [HM].[OP] op
					JOIN @tblDeletedPositions delPositions ON delPositions.id = op.owner_id AND op.owner_base = 18
				
				-- Na potrzeby rezerwacji
				DECLARE @DeletedSaleDocumentPositionsRZ [HM].[DocumentPositionsType]
				INSERT INTO @DeletedSaleDocumentPositionsRZ
				SELECT rz.id,rz.idtw, rz.iddokrz,rz.iloscrz 
						FROM [HM].[RZ] rz
						JOIN @tblDeletedPositions delPositions ON rz.bazapozrz = 18 and rz.idpozrz = delPositions.id

				-- Usunięcie skasowanych pozycji
				DELETE FROM [HM].[DP]
				WHERE [super] = @docId
				AND [id] IN (SELECT [Id] FROM @tblDeletedPositions)

				--Zapis nowych pozycji
				DECLARE @newPositionIds TABLE (Id INT, Guid UNIQUEIDENTIFIER)
				INSERT INTO [HM].[DP]
				(
					[guid],
					[lp],
					[idkh],
					[idtw],
					[kod],
					[opis],
					[jmwp],
					[jm],
					[iloscwp],
					[iloscjedn],
					[ilosc],
					[schemat],
					[stvat],
					[cena],
					[walNetto],						
					[walBrutto],
					[wartNetto],
					[wartVat],
					[grupacen],
					[cenaBazaPl],
					[cenaBaza],
					[JPK_V7],
					[super],
					[superkoryg],
					[subtyp],
					[flag],
					[typ],
					[opisdod],
					[waluta],
					[wartTowaru],
					[cenabaztow],
					[walcenybaz],
					[wartstvat],
					[zyskdod],
					[cenawal],
					[bufor],
					[kompensata],
					[typceny],
					[kosztmarzy],
					[cn],
					[rejestrVAT],
					[magazyn],
					[data],
					[wartPrzychod],
					idpozkoryg,
					[odwrotneObc],
					[koszt],
					[iloscRLKS],
					[usluga_oss]
				)
				OUTPUT INSERTED.id, INSERTED.guid INTO @newPositionIds
				SELECT
					[Guid] AS [guid],
					CASE WHEN [IsNewCorrectPosition] = 'true' THEN tblDP.OrdinalNumber
					     WHEN @correction = 0                 THEN tblDP.NodeId
					     ELSE                      (SELECT DP.lp FROM HM.DP WHERE DP.id = tblDP.CorrectedPositionId)
					END AS lp,
					@ContractorId AS [idkh],
					IIF([ProductId] = 0, NULL, [ProductId]) AS [idtw],
					(
						CASE
							WHEN ([ProductId] = 0) THEN ''
							ELSE (Select [kod] from HM.TW where [id] = [ProductId])
						END
					) AS [kod],
					[Description] AS [opis],
					[UnitOfMeasurement] AS [jmwp],
					[EvidenceUnitOfMeasurement] AS [jm],
					[Quantity] AS [iloscwp],
					[UnitQuantity] AS [iloscjedn],
					[EvidenceQuantity] AS [ilosc],
					[AccountingPattern] AS [schemat],
					IIF(@docType = 46 AND [VatRateId] <> @NpRateId,[DomesticVatRateId],ISNULL(NULLIF([VatRateId], ''), @defaultVatRateId)) AS [stvat],
					CASE WHEN @IsNet = 1 THEN [Price] ELSE [PriceGross] END * @ExchangeRate AS [cena], --celowo bez zaokrąglenia
					[Value] AS [walNetto],						
					[ValueGross] AS [walBrutto],
					[ValuePLN] AS [wartNetto],
					[ValueVatPLN] AS [wartVat],
					@IsNet AS [grupacen],
					CASE WHEN @IsNet = 1 THEN [PriceListPrice] ELSE [PriceListPriceGross] END * @ExchangeRate AS [cenaBazaPl], --celowo bez zaokrąglenia
					[OriginalPriceListPrice] AS [cenaBaza],
					[JPK_V7] AS [JPK_V7],
					@docId AS [super],
					IIF([CorrectedDocumentId] = 0, @docId, [CorrectedDocumentId]) AS [superkoryg],
					0 AS [subtyp],					
					valuesForDP.flag AS [flag],
					valuesForDP.typ AS [typ],
					[CodePKWIU] AS [opisdod],
					[OriginalPriceListPriceCurrency] AS [waluta],
					valuesForDP.wartTowaru AS [wartTowaru],
					valuesForDP.cenabaztow AS [cenabaztow],
					valuesForDP.walcenybaz AS [walcenybaz],
					valuesForDP.wartstvat AS [wartstvat],
					valuesForDP.zyskdod AS [zyskdod],
					CASE WHEN @IsNet = 1 THEN [Price] ELSE [PriceGross] END AS [cenawal],
					@isBufor AS [bufor],
					Compensation AS [kompensata],
					[PriceTypeId] AS [typceny],
					0 AS [kosztmarzy],
					[CN] as [cn],
					IIF([VatRegister] = 0, NULL, [VatRegister]) AS [rejestrVAT],
					@department AS [magazyn],
					@issueDate as [data],
					[ValuePLN] AS [wartPrzychod],
					CorrectedPositionId AS idpozkoryg,
					[OO] AS odwrotneObc,
					valuesForDP.koszt AS koszt,
					valuesForDP.iloscRLKS AS iloscRLKS,
					tblDP.[IsOssService] AS [usluga_oss]
				FROM @tblDocumentPositions AS tblDP
				JOIN @valuesForDP valuesForDP ON valuesForDP.NodeId = tblDP.NodeId
				WHERE tblDP.ModificationStatus = @created
				ORDER BY tblDP.NodeId;	--istotna jest kolejność dodanych pozycji

				-- Aktualizacja Id w @tblDocumentPositions
				UPDATE tblPosition SET Id = newIds.Id
				FROM @tblDocumentPositions tblPosition
				JOIN @newPositionIds newIds ON newIds.guid = tblPosition.guid
				WHERE tblPosition.ModificationStatus = @created

				DECLARE @positionsTable AS HM.DocumentTablePositionsType
				INSERT INTO @positionsTable (Id, NodeId)
				SELECT
					Id,
					NodeId
				FROM @tblDocumentPositions

				-- Aktualizacja rozliczeń zaliczek
				EXEC HM.UpdateAdvanceInvoiceSettlementPositions @docData, @positionsTable;
				
				INSERT INTO [HM].[OP]
				(
					[flag],
					[subtyp],
					[owner_id],
					[owner_base],
					[kod],
					[id_co],
					[id_transakcji],
					[ok],
					[ilosc],
					[ilosc_rozl],
					[wartosc],
					[wartosc_rozl],
					[datarozl]
				)
				SELECT
					IIF(ISNULL(TW.subtypext, 1) = 1, 0x200, 0) |
					CASE WHEN dp.[super] != dp.[superkoryg] and dp.[idpozkoryg] is not null and dp.[kompensata] = 1 THEN 0x1800
						 WHEN dp.[super] != dp.[superkoryg] and dp.[idpozkoryg] is not null and dp.[kompensata] = 0 THEN 0x1000
						 ELSE 0 END AS [flag],
					1 AS [subtyp],
					dp.[id] AS [owner_id],
					18 AS [owner_base],
					dp.[kod] AS [kod],
					dp.[idtw] AS [id_co],
					TR.id AS [id_transakcji],
					IIF(tblDP.CorrectedPositionId is NULL, 0, 1) AS [ok],
					Abs(dp.ilosc) AS [ilosc],
					IIF(tblDP.CorrectedPositionId is NULL, 0, Abs(dp.ilosc)) AS [ilosc_rozl],
					Abs(dp.ilosc) * dp.cenabaztow AS [wartosc],
					IIF(tblDP.CorrectedPositionId is NULL, 0, Abs(dp.ilosc) * dp.cenabaztow) AS [wartosc_rozl],
					IIF(tblDP.CorrectedPositionId is NULL, NULL, CONVERT(date, GETDATE())) AS [datarozl]
				FROM @tblDocumentPositions tblDP
				JOIN [HM].[DP] dp ON dp.id = tblDP.Id
				JOIN HM.TR ON TR.typ = 1 AND TR.owner_base = 16 AND TR.owner_id = dp.[superkoryg]
				LEFT OUTER JOIN HM.TW ON DP.idtw = TW.id
				WHERE tblDP.ModificationStatus = @created 

				-- Nowe rozliczenia w HM.RO dla korekt
				IF @correction <> 0
				BEGIN
					INSERT INTO [HM].[RO]
					(
						[flag],
						[typ],
						[id1],
						[id2],
						[ilosc],
						[wartosc],
						[data]
					)
					SELECT
						op.[flag] AS [flag],
						1 AS [typ],
						op2.[id] AS [id1],
						op.[id] AS [id2],
						CASE WHEN op.[flag] & 0x1800 = 0x1800 THEN op.[ilosc]
							 WHEN op.[flag] & 0x1000 = 0x1000 THEN -op.[ilosc]
							 ELSE 0 
							 END AS [ilosc],
						CASE WHEN op.[flag] & 0x1800 = 0x1800 THEN op.[wartosc]
							 WHEN op.[flag] & 0x1000 = 0x1000 THEN -op.[wartosc] 
							 ELSE 0 
							 END AS [wartosc],
						CONVERT(date, GETDATE()) AS [data]
					FROM @tblDocumentPositions tblDP
					JOIN [HM].[DP] dp ON dp.id = tblDP.Id
					JOIN [HM].[OP] op ON op.[owner_id] = dp.[id] AND op.owner_base = 18
					LEFT JOIN [HM].[DP] dp2 ON dp2.[id] = dp.[idpozkoryg]
					JOIN [HM].[OP] op2 ON op2.[owner_id] = (ISNULL(dp2.[idpozkoryg], dp.[idpozkoryg])) AND op2.owner_base = 18
					WHERE tblDP.ModificationStatus = @created
				END

				-- Aktualizacja pozycji 
				UPDATE [HM].[DP]
				SET dp.lp = CASE WHEN IsNewCorrectPosition = 'true' THEN tblDP.OrdinalNumber
				                 WHEN @correction = 0               THEN tblDP.NodeId
				                 ELSE                      (SELECT DP.lp FROM HM.DP WHERE DP.id = tblDP.CorrectedPositionId)
							END,
				    dp.[idkh] = @ContractorId,
					dp.[idtw] = IIF(tblDP.[ProductId] = 0, NULL, tblDP.[ProductId]),
					dp.[kod] = (
						CASE
							WHEN (tblDP.[ProductId] = 0) THEN ''
							ELSE (Select [kod] from HM.TW where [id] = tblDP.[ProductId])
						END
					),	
					dp.[opis] = tblDP.[Description],
					dp.[jmwp] = tblDP.[UnitOfMeasurement],
					dp.[jm] = tblDP.[EvidenceUnitOfMeasurement],
					dp.[iloscwp] = tblDP.[Quantity],
					dp.[iloscjedn] = tblDP.[UnitQuantity],
					dp.[ilosc] = tblDP.[EvidenceQuantity],					
					dp.[schemat] = tblDP.[AccountingPattern],
					dp.[stvat] = IIF(@docType = 46 AND [VatRateId] <> @NpRateId,tblDP.[DomesticVatRateId],tblDP.[VatRateId]),
					dp.[cena] = CASE WHEN @IsNet = 1 THEN tblDP.[Price] ELSE tblDP.[PriceGross] END * @ExchangeRate, --celowo bez zaokrąglenia
					dp.[walNetto] = tblDP.[Value],						
					dp.[walBrutto] = tblDP.[ValueGross],
					dp.[wartNetto] = tblDP.[ValuePLN],
					dp.[wartVat] = tblDP.[ValueVatPLN],
					dp.[grupacen] = @IsNet,
					dp.[cenaBazaPl] = CASE WHEN @IsNet = 1 THEN tblDP.[PriceListPrice] ELSE tblDP.[PriceListPriceGross] END * @ExchangeRate, --celowo bez zaokrąglenia
					dp.[cenaBaza] = tblDP.[OriginalPriceListPrice],
					dp.[JPK_V7] = tblDP.[JPK_V7],
					dp.[subtyp] = 0,
					dp.[flag] = valuesForDP.[flag],
					dp.[typ] = valuesForDP.[typ],
					dp.[opisdod] = tblDP.[CodePKWIU],
					dp.[waluta] = tblDP.[OriginalPriceListPriceCurrency],
					dp.[wartTowaru] = valuesForDP.wartTowaru,
					dp.[cenabaztow] = valuesForDP.cenabaztow,
					dp.[walcenybaz] = valuesForDP.walcenybaz,
					dp.[wartstvat] = valuesForDP.wartstvat,
					dp.[zyskdod] = valuesForDP.zyskdod,
					dp.[cenawal] = CASE WHEN @IsNet = 1 THEN tblDP.[Price] ELSE tblDP.[PriceGross] END,
					dp.[bufor] = @isBufor,
					dp.[kompensata] = tblDP.[Compensation],
					dp.[typceny] = tblDP.[PriceTypeId],
					dp.[kosztmarzy] = 0,
					dp.[cn] = tblDP.[CN],
					dp.[rejestrVAT] = IIF(tblDP.[VatRegister] = 0, NULL, tblDP.[VatRegister]),
					dp.[magazyn] = @department,
					dp.[data] = @issueDate,
					dp.[wartPrzychod] = tblDP.[ValuePLN],
					dp.idpozkoryg = tblDP.CorrectedPositionId,
					dp.odwrotneObc = tblDP.OO,
					dp.[koszt] = valuesForDP.koszt,
					dp.[iloscRLKS] = valuesForDP.iloscRLKS,
					dp.[usluga_oss] = tblDP.[IsOssService]
				FROM [HM].[DP] dp
				JOIN @tblDocumentPositions tblDP ON dp.[id] = tblDP.[Id]
				JOIN @valuesForDP valuesForDP ON valuesForDP.NodeId = tblDP.NodeId
				WHERE dp.[super] = @docId AND tblDP.ModificationStatus = @updated

				IF (@departmentId = 0 OR @departmentId IS NULL)
				BEGIN
					SELECT @departmentId = magazyn FROM [HM].[DK] WHERE id = @docId
				END				

				-- Aktualizacja operacji
				UPDATE [HM].[OP]
				SET
					[flag] = IIF(ISNULL(tw.subtypext, 1) = 1, 0x200, 0) | IIF(op.flag & 0x400 = 0x400, 0x400, 0) |
							 CASE WHEN (dp.[flag] & 0x1000 = 0x1000) THEN CASE WHEN (dp.[idpozkoryg] IS NOT NULL AND dp.kompensata = 0) THEN -28160
																			   WHEN (dp.[idpozkoryg] IS NOT NULL AND dp.kompensata = 1) THEN -26112
																			   ELSE -32256 END
								  WHEN (dp.[super] != dp.[superkoryg] and dp.[idpozkoryg] is not null and dp.[kompensata] = 1) THEN 0x1800
								  WHEN (dp.[super] != dp.[superkoryg] and dp.[idpozkoryg] is not null and dp.[kompensata] = 0) THEN 0x1000
								  ELSE 0 END,
					[subtyp] = 1,
					[owner_id] = dp.[id],
					[kod] = dp.[kod],
					[id_co] = dp.[idtw],
					[ok] = CASE WHEN dp.[idpozkoryg] is not null THEN 1 
								WHEN (dp.[flag] & 0x1000 = 0x1000) THEN 1 -- nagłówek kompletu
								WHEN (dp.[ilosc] = [ilosc_rozl] AND [ilosc_rozl] <> 0) THEN 1
								WHEN dp.[idtw] IS NULL THEN 1 -- pozycja niekartotekowa
								WHEN (dp.[flag] & 0x0200 = 0) THEN 1 -- pozycja niezwiązana z magazynem
								ELSE 0 END,
					[ilosc] = Abs(dp.[ilosc]),
					[ilosc_rozl] = CASE WHEN dp.[idpozkoryg] is not null THEN Abs(dp.[ilosc]) ELSE [ilosc_rozl] END,
					[wartosc] = IIF(dp.[flag] & 0x1000 = 0x1000 AND dp.[idpozkoryg] IS NOT NULL, 0, Abs(dp.[ilosc]) * dp.[cenabaztow]),
					[wartosc_rozl] = CASE WHEN dp.[flag] & 0x1000 = 0x1000 AND dp.[idpozkoryg] IS NOT NULL THEN 0
										  WHEN dp.[idpozkoryg] is not null THEN Abs(dp.[ilosc]) * dp.[cenabaztow] 
										  ELSE [wartosc_rozl] END,
					[datarozl] = CASE WHEN dp.[idpozkoryg] is not null THEN CONVERT(date, GETDATE())
										ELSE 
											CASE WHEN (dp.[flag] & 0x1000 = 0x1000) THEN CONVERT(date, GETDATE()) ELSE IIF(dp.[idtw] IS NULL, CONVERT(date, GETDATE()), NULL ) END
										END
				FROM [HM].[OP] op
				JOIN [HM].[DP] dp ON op.[owner_id] = dp.[id] AND op.[owner_base] = 18
				JOIN @tblDocumentPositions tblPositions ON tblPositions.Id = dp.id
				LEFT OUTER JOIN [HM].[TW] tw ON DP.idtw = tw.id
				WHERE dp.super = @docId AND tblPositions.ModificationStatus IN (@created, @updated)	--czy powinno być też @created? - dotychczas tak było (modyfikowane także dodane powyżej)

				-- Aktualizacja rozliczeń w HM.RO dla korekt
				IF @correction <> 0
				BEGIN
					UPDATE [HM].[RO]
					SET
						[ilosc] = CASE WHEN dp.kompensata = 1 THEN op.[ilosc]
									   WHEN dp.kompensata = 0 THEN -op.[ilosc]
									   ELSE 0 
									   END,
						[wartosc] = CASE WHEN dp.kompensata = 1 THEN op.[wartosc]
										 WHEN dp.kompensata = 0 THEN -op.[wartosc] 
										 ELSE 0 
										 END,
						[data] = CONVERT(date, GETDATE())
					FROM [HM].[RO] ro
					JOIN [HM].[OP] op ON op.[id] = ro.[id2]
					JOIN [HM].[DP] dp ON op.[owner_id] = dp.[id] AND op.[owner_base] = 18
					JOIN @tblDocumentPositions tblPositions ON tblPositions.Id = dp.id
					WHERE dp.[super] = @docId AND (ro.[flag] & 0x1800 = 0x1800 OR ro.[flag] & 0x1000 = 0x1000)
						AND tblPositions.ModificationStatus IN (@created, @updated)
										
					DECLARE @corectedPositionsRoots TABLE (rootId INT);
                    INSERT INTO @corectedPositionsRoots
                    SELECT DISTINCT hm.fnGetCorrectedPositionRoot(dp.id) rootId 
                    FROM @correctedOrDocId docId
                        JOIN hm.tr on docId.Id = tr.owner_id and tr.owner_base = 16
                        JOIN hm.op ON op.id_transakcji = tr.id 
                        JOIN hm.dp on op.owner_id = dp.id AND op.owner_base = 18
                
                    DECLARE @sum_ro TABLE(id1 int, quantitySum float, valueSum float);

					INSERT INTO @sum_ro
					SELECT id1, SUM(ilosc), SUM(wartosc)
					FROM [HM].[RO]
                        JOIN (SELECT op.[id]
                                FROM @corectedPositionsRoots cpr
                                JOIN [HM].[DP] dp on cpr.rootId = dp.id
                                JOIN [HM].[OP] op ON op.[owner_id] = ISNULL(dp.[idpozkoryg], dp.id) AND op.[owner_base] = 18) OP ON op.id = ro.id1
					GROUP BY id1

					UPDATE [HM].[OP]
					SET
						[ilosc_rozl] = sum_ro.[quantitySum],
						[wartosc_rozl] = sum_ro.[valueSum],
						[ok] = CASE WHEN op.[flag] = -32256 OR op.[flag] = 512 THEN 1 
									ELSE IIF(sum_ro.[quantitySum] = op.[ilosc], 1, 0)									
									END
					FROM [HM].[OP] op
					JOIN @sum_ro sum_ro ON sum_ro.[id1] = op.[id]

				END
				
				DECLARE @incompletedOperationsOwners TABLE (Id INT);
				IF EXISTS (SELECT TOP(1) * FROM @correctedOrDocId) AND @correction = 1
				BEGIN
					INSERT INTO @incompletedOperationsOwners
						SELECT dp.superkoryg
						FROM @corectedPositionsRoots cpr
                        JOIN [HM].[OP] op ON op.owner_id = cpr.rootId AND op.owner_base = 18
						JOIN [HM].[DP] dp ON dp.id = cpr.rootId
						WHERE op.ok = 0
				END
				ELSE
				BEGIN
					INSERT INTO @incompletedOperationsOwners
						SELECT dp.super
						FROM [HM].[OP] op
						JOIN [HM].[DP] dp ON op.owner_id = dp.id AND op.owner_base = 18 
						WHERE dp.super = @docId AND ok = 0
				END

				--Powiązane transakcje wydań
				DECLARE @transactionIds TABLE (id INT);
				INSERT INTO @transactionIds SELECT id from HM.TR where owner_id IN (SELECT * FROM @correctedOrDocId) AND owner_base = 16
				
				DECLARE @linkedTransactions TABLE(id int);
				
				INSERT INTO @linkedTransactions
				SELECT id2 FROM hm.po 
				WHERE id1 IN (SELECT * FROM @transactionIds);
				
				DECLARE @incompletedTransactions INT;
				SET @incompletedTransactions = (SELECT COUNT(1) 
												FROM HM.TR tr
												JOIN @linkedTransactions transactions ON transactions.id = tr.id
												where tr.owner_base = 33 AND tr.status <> 32);

				
				DECLARE @status SMALLINT;
				DECLARE @bufor bit;
				SET @bufor = (SELECT bufor FROM [HM].[DK] dk WHERE dk.id = @docId);
				
				IF @correction = 0
				BEGIN
					SET @status = CASE 
								WHEN @incompletedTransactions = 0 AND @bufor = 1 THEN 4
								WHEN (SELECT COUNT(Id) FROM @incompletedOperationsOwners) <> 0 THEN 8
								WHEN @incompletedTransactions <> 0 THEN 16
								ELSE 32
								END;
				END
				ELSE
				BEGIN

					DECLARE @anyTransactions INT;
					SET @anyTransactions = (SELECT COUNT(1) 
												FROM HM.TR tr
												JOIN @linkedTransactions transactions ON transactions.id = tr.id
												where tr.owner_base = 33);

					SET @status = CASE 
								WHEN @incompletedTransactions = 0 AND @anyTransactions = 0 THEN 4
								WHEN (SELECT COUNT(Id) FROM @incompletedOperationsOwners) <> 0 THEN 8
								WHEN @incompletedTransactions <> 0 OR @bufor = 1 THEN 16
								ELSE 32
								END;
				END;

				DECLARE @wartoscTransakcji FLOAT;
				SELECT @wartoscTransakcji = ISNULL(SUM(CASE WHEN ((32768 & OP.flag) = 32768) THEN 0 WHEN ((OP.flag & 6144) = 6144) THEN -OP.wartosc WHEN ((2048 & OP.flag) = 2048) THEN -OP.wartosc ELSE OP.wartosc END),0) FROM [HM].[TR] TR
				JOIN HM.OP ON OP.id_transakcji = TR.id 
				WHERE TR.owner_id IN (SELECT * FROM @correctedOrDocId) and TR.typ = 1 AND TR.owner_base = 16

				-- Update danych w tabeli transakcji na podstawie operacji pozycji
				-- W zaliczkach nie dokonujemy zmian w tabeli transakcji
				UPDATE [HM].[TR]
				SET
					id_kto = dk.odid,
					kod = dk.kod,
					wartosc = @wartoscTransakcji,
					nrozl_oper = (SELECT COUNT(Id) FROM @incompletedOperationsOwners WHERE Id = tr.owner_id),
					nrozlilosc = (SELECT COUNT(Id) FROM @incompletedOperationsOwners WHERE Id = tr.owner_id),
					status = @status,
					ok = IIF((SELECT COUNT(Id) FROM @incompletedOperationsOwners WHERE Id = tr.owner_id) = 0, 1, 0),
					datarozl = IIF((SELECT COUNT(Id) FROM @incompletedOperationsOwners WHERE Id = tr.owner_id) = 0, CONVERT(date, GETDATE()), NULL),
					data = dk.data,
					termin = DATEADD(day, 7, dk.data)
				FROM [HM].[TR] tr 
				JOIN [HM].[DK] dk ON dk.id = tr.owner_id AND tr.owner_base = 16
				WHERE DK.id IN (SELECT * FROM @correctedOrDocId)
				AND DK.subtyp not in (157, 158, 159, 160)
				
				-- Tymczasowe tabele pozycji na potrzeby zapisu rezerwacji
				DECLARE @tblDRP [HM].[DocumentReservationsPositionsType]

				INSERT INTO @tblDRP 
				SELECT tblDP.Id, tblDP.NodeId, tblDP.ProductId, tblDP.ReservationType
				FROM @tblDocumentPositions tblDP

				DECLARE @tblDP [HM].[DocumentPositionsType]

				INSERT INTO @tblDP
				SELECT DP.[Id], DP.[idtw], DP.[super], DP.[ilosc]
				FROM [HM].[DP] DP
				WHERE DP.[super] = @docId

				DECLARE @anyOrderRelatedDocuments BIT = ISNULL((SELECT TOP(1) 1 FROM @tblRelatedDocuments WHERE DocumentType = 'Zamówienia'), 0)

				-- Zapis rezerwacji
				EXEC [HM].[UpdateDocumentReservationsFromXML] @handle, @tblDRP, @tblDP, @DeletedSaleDocumentPositionsRZ, @docId, @IssueDate, @ContractorId, @correction, @anyOrderRelatedDocuments, @userId
											
				--aktualizacja statusu rozliczenia dokumentu gdy dokument jest już rozliczony lub częściowo rozliczony			
				UPDATE [HM].[DK] SET DK.rozlmg = case WHEN ((32 & DK.rozlmg) = 32) THEN ~32 & DK.rozlmg ELSE 32 | DK.rozlmg END 
				FROM HM.DK WHERE DK.id in 
				(SELECT DK.id 
					FROM  [HM].[DK] DK
					JOIN [HM].[TR] TR ON (TR.owner_id = DK.id OR TR.owner_id IN (SELECT * FROM @correctedDocsIds)) AND TR.owner_base = 16 AND TR.typ = 1 
					JOIN  [HM].[DK] DKO ON DKO.id = TR.owner_id
					WHERE (((TR.ok = 0 AND TR.nrozl_oper <> 0 AND ((32 & DK.rozlmg) = 32)) OR (TR.ok <> 0 AND TR.nrozl_oper = 0 AND ((32 & DK.rozlmg) <> 32))) AND (0x4000 & DKO.flag) = 0 AND DK.bufor = 0 AND DK.typ = 0) 
					AND DK.anulowany = 0 and DK.id IN (SELECT * FROM @correctedOrDocId))

				IF @correction <> 0
				BEGIN
					UPDATE [HM].[DK] 
						SET [rozlmg] = CASE WHEN tr.[status] = 32 OR tr.[status] = 16 THEN dk.[rozlmg] | 32
											 ELSE dk.[rozlmg]
											 END
					FROM [HM].[DK] dk
					JOIN [HM].[TR] tr ON tr.[owner_id] IN (SELECT * FROM @correctedDocsIds)
					WHERE dk.[id] = @docId
				END

				--naprawa wartości oraz wartości rozliczonej dokumentu gdy dokument jest już rozliczony lub częściowo rozliczony
				CREATE TABLE [#tempRozl] (idTR INT, wartosc FLOAT, wartosc_rozl FLOAT)

				INSERT #tempRozl
				SELECT TR.id as idTR, SUM(CASE WHEN ((32768 & OP.flag) = 32768) THEN 0 WHEN ((2048 & OP.flag) = 2048) THEN -OP.wartosc ELSE OP.wartosc END) as wartGood, SUM(CASE WHEN ((32768 & OP.flag) = 32768) THEN 0 WHEN ((2048 & OP.flag) = 2048) THEN -OP.wartosc_rozl ELSE OP.wartosc_rozl END) as wartRozlGood  
				FROM HM.TR TR
				JOIN HM.OP ON OP.id_transakcji = TR.id 
				WHERE TR.typ = '1' and (TR.owner_id = @docId and TR.owner_base = 16) 
				GROUP BY TR.id, TR.kod HAVING (ABS(ABS(SUM(CASE WHEN ((32768 & OP.flag) = 32768) THEN 0 WHEN ((2048 & OP.flag) = 2048) THEN -OP.wartosc_rozl ELSE OP.wartosc_rozl END)) - ABS(MIN(TR.wartosc_rozl))) > 0.000001) OR (ABS(ABS(SUM(CASE WHEN ((32768 & OP.flag) = 32768) THEN 0 WHEN ((2048 & OP.flag) = 2048) THEN -OP.wartosc ELSE OP.wartosc END)) - ABS(MIN(TR.wartosc))) > 0.000001)

				UPDATE HM.TR SET TR.wartosc = tmpRozl.wartosc, TR.wartosc_rozl = tmpRozl.wartosc_rozl FROM HM.TR JOIN #tempRozl tmpRozl ON tmpRozl.idTR = TR.id

				IF (OBJECT_ID('tempdb..[#tempRozl]') IS NOT NULL) DROP TABLE [#tempRozl]	
			
			IF(@foreignOrder = 'true')
			BEGIN
				
				-- Tabela pomocnicza powiązania operacji dokumentu i zamówienia
				DECLARE @temp_ro TABLE(id int, id_zp int, id_dp int, ilosc_ro float, ilosc_dp float, ilosc_zp float, 
										ilosc_rozl_zp float, ilosc_dp_other float, ilosc_ro_new float, ilosc_sum float, date_zp DATE);

				INSERT INTO @temp_ro
				SELECT ro.id, ro.id1, ro.id2, ro.ilosc, op_dp.ilosc, op_zp.ilosc, op_zp.ilosc_rozl, 0, 0, 0, zo.data
				FROM HM.RO ro
				JOIN HM.OP op_dp ON ro.id2 = op_dp.id
				JOIN HM.OP op_zp ON ro.id1 = op_zp.id
				JOIN @tblDocumentPositions AS position ON position.id = op_dp.owner_id AND op_dp.owner_base = 18
				JOIN HM.ZP zp ON zp.id = op_zp.owner_id AND op_zp.owner_base = 30
				JOIN HM.ZO zo ON zo.id = zp.super
				WHERE op_dp.flag & 0x400 = 0x400 AND position.ModificationStatus IN (@created, @updated);

				-- Tabela pomocnicza sumy ilosci z innych powiazanych dokumentow, bez edytowanego dokumentu
				DECLARE @temp_ro_SUM TABLE(id int, ilosc_SUM float);
				
				INSERT INTO @temp_ro_SUM
				SELECT temp_ro.id, SUM(ro.ilosc) AS ilosc_SUM 
				FROM HM.RO ro
				JOIN @temp_ro temp_ro ON temp_ro.id_zp = ro.id1
				WHERE temp_ro.id != ro.id 
				GROUP BY temp_ro.id, temp_ro.ilosc_zp;

				-- Dopisanie do tabeli pomocniczej sumy ilosci na innych powiązanych dokumentach
				UPDATE @temp_ro
				SET ilosc_dp_other = temp_ro_SUM.ilosc_SUM
				from @temp_ro ro
				JOIN @temp_ro_SUM temp_ro_SUM ON temp_ro_SUM.id = ro.id

				-- Przeliczenie ilości do aktualizacji kolumny HM.RO
				DECLARE @id int;
				DECLARE @id_dp_prev int;
				DECLARE @id_dp int;
				DECLARE @ilosc_dp float;
				DECLARE @ilosc_zp float;
				DECLARE @ilosc_dp_other float;
				DECLARE @ilosc_ro_new float;
				DECLARE @calculated_ilosc_ro float;
				DECLARE @ilosc_left float; -- pozostała ilość do przypisania (dla pozycji scalonych z wielu zamówień)
 
				DECLARE temp_cursor CURSOR FOR
				SELECT id, id_dp, ilosc_dp, ilosc_zp, ilosc_dp_other
				FROM @temp_ro
				ORDER BY id_dp, date_zp
 
				OPEN temp_cursor;
				FETCH NEXT FROM temp_cursor INTO @id, @id_dp, @ilosc_dp, @ilosc_zp, @ilosc_dp_other;
 
				WHILE @@FETCH_STATUS = 0
				BEGIN

					IF(@id_dp_prev = @id_dp)
					BEGIN
						
						SET @calculated_ilosc_ro = IIF(@ilosc_left > @ilosc_zp - @ilosc_dp_other, @ilosc_zp - @ilosc_dp_other, @ilosc_left);
						SET @ilosc_left = @ilosc_left - @calculated_ilosc_ro;

					END
					ELSE
					BEGIN
						SET @calculated_ilosc_ro = IIF(@ilosc_dp > @ilosc_zp - @ilosc_dp_other, @ilosc_zp - @ilosc_dp_other, @ilosc_dp);
						SET @ilosc_left = @ilosc_dp - @calculated_ilosc_ro;
					END

					UPDATE @temp_ro
					SET ilosc_ro_new = @calculated_ilosc_ro, ilosc_sum = @calculated_ilosc_ro + ilosc_dp_other
					WHERE id = @id;

					SET @id_dp_prev = @id_dp;

					FETCH NEXT FROM temp_cursor INTO @id, @id_dp, @ilosc_dp, @ilosc_zp, @ilosc_dp_other;
				END
 
				CLOSE temp_cursor;
				DEALLOCATE temp_cursor;

				-- Aktualizacja ilosci w tabeli powiązań operacji
				UPDATE HM.RO
				SET ilosc = temp_ro.ilosc_ro_new
				FROM HM.RO ro
				JOIN @temp_ro temp_ro ON ro.id = temp_ro.id

				-- Aktualizacja operacji zamówień
				UPDATE HM.OP SET
				ilosc_rozl = temp_ro.ilosc_SUM,
				ok = IIF(op.ilosc = temp_ro.ilosc_SUM OR op.flag & 0x2200 = 0x2200, 1, 0),
				datarozl = IIF(op.ilosc = temp_ro.ilosc_SUM OR op.flag & 0x2200 = 0x2200, CAST( GETDATE() AS Date ), NULL),
				wartosc_rozl = HM.ROUND2((wartosc / ilosc) * temp_ro.ilosc_ro_new)
				FROM HM.OP op
				JOIN @temp_ro temp_ro ON temp_ro.id_zp = op.id;

				-- Aktualizacja operacji zamówieia po skasowaniu pozycji
				-- Tabela pomocnicza sumy wszystkich ilosci z powiazanych dokumentow
				DECLARE @deleted_dp_sum TABLE (id int, ilosc_SUM float);
				INSERT INTO @deleted_dp_sum
				SELECT deleted_operations.id, SUM(ro.ilosc) AS ilosc_SUMA 
				FROM HM.RO ro
				JOIN @zmoOperationsToDeletedPositions deleted_operations ON deleted_operations.id = ro.id1
				GROUP BY deleted_operations.id;

				-- Aktualizacja operacji zamówienia po skasowaniu pozycji
				UPDATE HM.OP SET
				ilosc_rozl = deleted_dp_sum.ilosc_SUM,
				ok = IIF(op.ilosc=deleted_dp_sum.ilosc_SUM, 1, 0),
				datarozl = IIF(op.ilosc=deleted_dp_sum.ilosc_SUM, CAST( GETDATE() AS Date ), NULL),
				wartosc_rozl = (wartosc / ilosc) * deleted_dp_sum.ilosc_SUM
				FROM HM.OP op
				JOIN @deleted_dp_sum deleted_dp_sum ON deleted_dp_sum.id = op.id;

				-- Aktualizacja wartosci w tabeli powiązań operacji
				UPDATE HM.RO SET
				ro.wartosc = op.wartosc_rozl
				FROM HM.RO ro
				JOIN HM.OP op ON ro.id1 = op.id
				JOIN @temp_ro temp_ro on temp_ro.id = ro.id

				-- Id transakcji zamówienia powiązana z dokumentem
				DECLARE @tr_zm TABLE(id int);
				INSERT INTO @tr_zm SELECT id1 from hm.tr tr JOIN hm.po po ON po.id2 = tr.id
				WHERE tr.owner_id = @docId AND tr.owner_base = 16;
				
				-- Aktualizacja operacji zamówienia po usunięciu operacji w dokumencie
				UPDATE HM.OP SET
				op.ok = 0,
				op.ilosc_rozl = 0,
				op.wartosc_rozl = 0
				FROM HM.OP op
				JOIN @zmoOperationsToDeletedPositions zmoPosition ON zmoPosition.id = op.id AND op.owner_base = 30
				LEFT JOIN HM.RO ON ro.id1 = op.id
				JOIN @tr_zm tr_zm ON op.id_transakcji = tr_zm.id
				WHERE ro.id IS NULL;

				-- Suma wartosci rozliczonych operacji zamowienia
				DECLARE @completedValue TABLE (id int, wartosc_rozl float);
				INSERT INTO @completedValue
				SELECT op.id_transakcji, SUM(op.wartosc_rozl) 
				FROM HM.OP op 
				JOIN @tr_zm tr_zm ON op.id_transakcji = tr_zm.id 
				GROUP BY op.id_transakcji;

				-- Ilość nierozliczonych operacji zamówienia
				DECLARE @incompletedOperations_zm TABLE (id int, nrozl int);
				INSERT INTO @incompletedOperations_zm
				SELECT op.id_transakcji, COUNT(*) 
				FROM HM.OP op 
				JOIN @tr_zm tr_zm ON op.id_transakcji = tr_zm.id 
				where ok = 0 
				GROUP BY op.id_transakcji;

				-- Aktualizacja transakcji zamówienia
				UPDATE HM.TR SET
				nrozl_oper = ISNULL(ico.nrozl, 0),
				nrozlilosc = ISNULL(ico.nrozl, 0),
				ok = IIF(ISNULL(ico.nrozl, 0) = 0, 1, 0),
				status = IIF(ISNULL(ico.nrozl, 0) = 0, 16, 8),
				datarozl = IIF(ISNULL(ico.nrozl, 0) = 0, CAST( GETDATE() AS Date ), NULL),
				wartosc_rozl = cv.wartosc_rozl
				FROM HM.TR tr 
				JOIN @tr_zm tr_zm ON tr.id = tr_zm.id
				JOIN @completedValue cv ON cv.id = tr_zm.id
				LEFT JOIN @incompletedOperations_zm ico ON ico.id = tr_zm.id
							   
				-- Aktualizacja stanu zamówienia w tabeli ZO			   
				UPDATE HM.ZO SET
				ok = IIF(ISNULL(ico.nrozl, 0) = 0, 1, 0),
				rozlmg = CASE WHEN (rozlmg & 0x20) = 0 THEN (207 & rozlmg) | (CASE WHEN TR.status = 16 THEN 0x20 ELSE 0 END) ELSE rozlmg END
				FROM HM.ZO ZO
				JOIN HM.TR TR ON TR.owner_id = ZO.id AND TR.owner_base = 45
				JOIN @tr_zm tr_zm ON tr_zm.id = TR.id
				LEFT JOIN @incompletedOperations_zm ico ON ico.id = tr_zm.id

			END

			-- Zaliczki
			DECLARE @baseType SMALLINT
			DECLARE @foreignOrderDocumentId INT
			DECLARE @valueGrossPLN DECIMAL(24,2)

			SELECT 
				@baseType = BaseType, 
				@foreignOrderDocumentId = RelatedForeignOrderDocumentId ,
				@valueGrossPLN = ValueGrossPLN
			  FROM @tblSaleDocument
			
			IF @baseType IN ( 157, 158, 159, 160 ) -- zaliczka (w walucie i korekty)
			BEGIN
					-- Wiązanie nagłówka zamówienia z nagłówkiem zaliczki
					-- Aktualizacja flagi: czy końcowa
					IF (NOT EXISTS(SELECT typ FROM HM.ZZ WHERE typ = 157 AND baza2 = 16 AND id2 = @docId AND id1 = @foreignOrderDocumentId AND baza1 = 45))
					BEGIN
						INSERT INTO HM.ZZ (flag, subtyp, typ, baza1, id1, baza2, id2, kod1, kod2, [data],
							termin, ilosc, cena, waluta, opis, id3, baza3, ilosc2, magazyn, iddkreal, id3pos)
						VALUES (0, @baseType, 157, 45, @foreignOrderDocumentId, 16, @docId, '', @docNumber, @issueDate,
								NULL, 0, @valueGrossPLN, '', '', NULL, 0, 0, 0, IIF(@correction = 1, @CorrectedDocumentId, NULL), NULL)					
					END
					ELSE
					BEGIN
						UPDATE HM.ZZ
						SET flag = flag & ~1,
							[data] = @issueDate, cena = @valueGrossPLN, kod2 = @docNumber
						WHERE subtyp = @baseType AND typ = 157 AND baza1 = 45 AND baza2 = 16 AND id2 = @docId
					END

					-- Aktualizacja połączeń pozycji zaliczki z pozycjami zamówienia
					DECLARE @RelationsZp table(DpId INT, NodeId INT, ZpId INT, IsNewCorrectPosition BIT);

					INSERT INTO @RelationsZp(DpId, ZpId, NodeId, IsNewCorrectPosition)
					SELECT
						DpId = pos.Id,
						ZpId = T.C.value('(.)/ZpId[1]', 'int'),
						NodeId = T.C.value('(.)/../../NodeId[1]', 'int'),
						IsNewCorrectPosition = T.C.value('(.)/../../IsNewCorrectPosition[1]', 'bit')
					FROM @docData.nodes('/SaleDocument/DocumentPositions/SalePosition/RelationsZp/*') AS T(C)
					JOIN @tblDocumentPositions pos ON pos.NodeId = T.C.value('(.)/../../NodeId[1]', 'int')
					WHERE pos.ModificationStatus IN (@created, @updated) AND ISNULL(T.C.value('(.)/../../@ModificationStatus[1]','varchar(30)'), '') <> 'Deleted'

					--pomijamy pozycje korekt
					DELETE FROM @RelationsZp
					WHERE @correction <> 0 AND IsNewCorrectPosition = 0

					-- Usunięcie wszystkich relacji między pozycjami zaliczki i pozycjami zamówienia
					-- Uniknięcie duplikowania relacji
					DELETE FROM HM.AdvanceInvoiceSettlementForeignOrder 
					WHERE DpId in (SELECT r.DpId from @RelationsZp r)

					-- Dodanie relacji do bazy danych
					insert into HM.AdvanceInvoiceSettlementForeignOrder(DpId, ZpId)
					select r.DpId, r.ZpId
					from @RelationsZp r
					-- Koniec: Aktualizacja połączeń ...

					-- Operacje pozycji zaliczki
					UPDATE HM.OP SET
					flag = CASE WHEN (dp.[idpozkoryg] IS NOT NULL AND dp.kompensata = 0) THEN 0x3000
								WHEN (dp.[idpozkoryg] IS NOT NULL AND dp.kompensata = 1) THEN 0x3800
								ELSE 0x2000 END,
					ok = 1,
					datarozl = CONVERT(date, GETDATE()),
					ilosc = 1,
					ilosc_rozl = 0,
					wartosc = 0,
					wartosc_rozl = 0
					FROM @tblDocumentPositions pos
					JOIN HM.OP op ON pos.Id = op.owner_id AND op.owner_base = 18
					JOIN HM.DP dp ON dp.id = pos.Id
					WHERE pos.ModificationStatus IN (@created, @updated)
					-- Koniec: Operacje pozycji zaliczki

					-- Powiązania operacji zaliczki i korekty zaliczki
					UPDATE HM.RO SET
					ilosc = 0					
					FROM @tblDocumentPositions pos
					JOIN HM.OP op ON op.owner_id = pos.Id AND op.owner_base = 18
					JOIN HM.RO ro ON ro.id2 = op.id
					WHERE pos.ModificationStatus IN (@created, @updated)
					-- Koniec: Powiązania operacji zaliczki i korekty zaliczki
					
					-- Aktualizacja transakcji zaliczki
					-- Oznaczenie flagi flagTRtwNFZ (0x0100) dla transakcji zaliczki w buforze
					UPDATE HM.TR SET
					flag = TR.flag | 0x100,
					nrozlilosc = 0,
					nrozl_oper = 0,
					status = IIF(DK.bufor = 0, 32, 4)
					FROM HM.TR tr
					JOIN HM.DK dk ON dk.id = tr.owner_id
					WHERE tr.owner_id IN (SELECT * FROM @correctedOrDocId) AND tr.owner_base = 16
					-- Koniec: Aktualizacja transakcji zaliczki

					-- Aktualizacja statusu rozliczenia
					-- Zaliczka w buforze: rozliczenie rozlmg = 32. Zaliczka wystawiona rozliczenie rozlmg = 0.
					UPDATE HM.DK SET 
						dk.rozlmg = 32 | dk.rozlmg
					FROM HM.DK dk
					WHERE dk.id = @docId AND dk.rozlmg != 32 | dk.rozlmg;
					-- Koniec: Aktualizacja statusu rozliczenia
			END

			-- Aktualizacja pól własnych dokumentu
			exec HM.UpdateCustomFieldsFromXML
				@docData = @docData,
				@moduleCode = @moduleCode, 
				@docId = @docId,
				@docType = 0

            --Aktualizacja opakowań
            MERGE [HM].[DocumentPackages] AS T
            USING(
                SELECT 
                    tmp.[DKId], 
                    tmp.[PackageName], 
                    tmp.[RefundableDepositName], 
                    tmp.[PackageId],
                    tmp.[Quantity], 
                    tmp.[Price], 
                    tmp.[Value]
                FROM @tblRefundableDeposits tmp
            ) AS S ON T.[DKId] = S.[DKId] 
                AND T.[PackageName] = S.[PackageName] 
                AND T.[RefundableDepositName] = S.[RefundableDepositName]
                AND T.[Price] = S.[Price]
            WHEN NOT MATCHED BY TARGET THEN 
                INSERT([DKId], [PackageName], [PackageId], [RefundableDepositName], [Quantity], [Price], [Value])
                VALUES(S.[DKId], S.[PackageName], S.[PackageId], S.[RefundableDepositName], S.[Quantity], S.[Price], S.[Value])
            WHEN NOT MATCHED BY SOURCE AND T.[DKId] = @docId 
            THEN 
                DELETE
            WHEN MATCHED THEN 
                UPDATE SET 
                    T.[PackageName] = S.[PackageName],
                    T.[PackageId] = S.[PackageId],
                    T.[RefundableDepositName] = S.[RefundableDepositName],
                    T.[Price] = S.[Price],
                    T.[Quantity] = S.[Quantity], 
                    T.[Value] = S.[Value];

			-- odswiezamy powiazane zamowienia
			-- m.in. przyciski do tworzenia dokumentow realizujacych : 'Dokument sprzedazy' i 'Dokument wydania'
			INSERT LOG_BASE (baseName, rec_id, operation, term)
			SELECT 'ZO', TRzo.owner_id, 3, @@SPID
			  FROM HM.TR AS TRdk
			       INNER JOIN HM.PO ON TRdk.id = PO.id2
				   INNER JOIN HM.TR AS TRzo ON PO.id1 = TRzo.id
			 WHERE TRdk.owner_base = 16 AND TRdk.owner_id = @docId
			       AND TRzo.owner_base = 45
			
			CLEAN_AND_FINISH:

			EXEC sp_xml_removedocument @handle;

			EXEC [HM].[UpdateSaleDocumentRIFromXML] @docId, @docData , @userId , @moduleCode

			COMMIT TRANSACTION;
		
			EXEC [HM].[ReadSaleDocumentAsXML] @docId, @userId, @moduleCode
	END TRY
	BEGIN CATCH
		IF @@TRANCOUNT > 0
		BEGIN
			ROLLBACK TRANSACTION;

			THROW;
		END
	END CATCH
END

GO

CREATE OR ALTER VIEW [HM].[OrderDocuments]
AS
SELECT document.[id] as Id
      ,document.[flag] as Flag
      ,document.[aktywny] as IsActive
      ,document.[subtyp] as SubType
      ,document.[znacznik] as Tag
      ,document.[rodzaj] as Kind
      ,document.[katalog] as Catalog
      ,document.[info] as Info
      ,document.[kod] as DocumentNumber
      ,document.[seria] as NumerationShortcut
      ,document.[serianr] as NumberInNumeration
      ,document.[okres] as Period
      ,document.[seriadzial] as NumerationDepartmentId
      ,document.[nazwa] as Name
      ,document.[data] as IssueDate
      ,document.[datasp] as OperationDate
      ,document.[datawplywu] as ReceiveDate
      ,document.[opis] as Description
      ,document.[khid] as BuyerId
      ,document.[khadid] as BuyerAddress
      ,document.[odid] as RecipientId
      ,document.[odadid] as DeliveryAddress
      ,document.[ok] as IsSettled
      ,document.[rr] as SettlementKind
      ,document.[wplaty] as PaidUpAmount
      ,document.[rabat] as Discount
      ,document.[plattyp] as PaymentType
      ,document.[plattermin] as MaturityDate
      ,document.[netto] as Netto
      ,document.[vat] as Vat
      ,document.[odebrane] as RecipientName
      ,document.[typ_dk] as DocumentSubtypeShortcut
      ,CAST(document.[grupacen]+1 as smallint) as PriceKind
	  ,CAST(document.[typceny] as smallint) as PricesGroup
      ,document.[iddokkoryg] as CorrectedDocumentId
      ,document.[wartoscSp] as DocumentAmount
      ,document.[exp_fk] as FkFlag
      ,document.[waluta] as Currency
      ,document.[kurs] as CurrencyRate
      ,document.[zyskdod] as DocumentProfit
      ,document.[paragon] as Receipt
      ,document.[kod_obcy] as ForeignDocumentNumber
      ,document.[magazyn] as DepartmentId
      ,document.[rozlmg] as StoreFlag
      ,document.[formaplatn] as PaymentMethod
      ,document.[schemat] as AccountingPattern
      ,document.[datarej] as InRegisterDate
      ,document.[bufor] as IsNotIssued
      ,document.[super] as Super
      ,document.[anulowany] as Canceled
      ,document.[wystawil] as IssuerId
      ,document.[createdBy] as CreatedBy
      ,document.[createdDate] as CreationDate
      ,document.[modifiedBy] as ModifiedBy
      ,document.[modifiedDate] as ModificationDate
      ,document.[walNetto] as NettoInCurrency
      ,document.[walBrutto] as GrossInCurrency
      ,document.[statusRDF] as RdfState
      ,document.[guid] as Guid
	 ,document.[typ_dk] as DocumentCode
     ,document.[typeExternal] as TypeExternal
     ,document.[idExternal] as IdExternal
     ,document.[id2External] as Id2External
 	 ,ISNULL(trans.[status], 32) AS DocumentStatus
	 ,CASE ISNULL(trans.status, 32)
	  WHEN 8 THEN 'Realizacja częściowa'
	  WHEN 16 THEN 'Realizacja przygotowana'
	  WHEN 32 THEN 'Zrealizowano'
	  WHEN 4 THEN
	  CASE trans.OK 
		WHEN 0 THEN 'Do realizacji'
		WHEN 1 THEN 'Zrealizowano'
		ELSE 'Zrealizowano'
		END
	  ELSE 'Do realizacji'
	  END AS DocumentStatusDescription
	  ,document.[splitPayment] AS SplitPayment
  FROM [HM].[ZO] AS document
  LEFT OUTER JOIN [hm].[tr] AS trans ON trans.owner_id=DOCUMENT.id AND (trans.owner_base=45 OR trans.owner_base=46) -- ³¹czymy transakcje do zamówieñ (ZMO i Zam w³asne)
  --LEFT OUTER JOIN ze wzglêdu na typ "Kontrakt zaliczkowy korekta (archiwalny)", który nie ma transakcji

  GO

  
CREATE OR ALTER VIEW [HM].[SaleDocuments]
AS
SELECT 
	d.[id]				as Id
	,d.[guid]			as Guid

	,d.[aktywny]		as IsActive

	,d.[subtyp]			as BaseType				-- charakter dokumentu
	,d.[typ_dk]			as DocumentTypeShortcut -- typ dokumentu

	--numeracja
	,d.[seria]			as NumerationShortcut
	,d.[serianr]		as NumberInNumeration
	,d.[okres]			as Period
	,d.[seriadzial]		as NumerationDepartmentId
	--numeracja

	,d.[rodzaj]			as KindId
	,d.[katalog]		as CatalogId
	,d.[znacznik]		as Tag

	--,document.[info] as Info				nie u¿ywane
	,d.[kod]			as DocumentNumber
	,d.[nazwa]			as Name
	,d.[opis]			as Description
	,d.[odebrane]		as RecipientName

	,d.[magazyn]		as DepartmentId		-- dzia³

	,d.[data]			as IssueDate
	,d.[datasp]			as OperationDate
	,d.[datarej]		as InRegisterDate
	--,document.[datawplywu] as ReceiveDate	nie u¿ywane

	,d.[khid]			as BuyerId
	,d.[khadid]			as BuyerAddressId
	,d.[odid]			as RecipientId
	,d.[odadid]			as DeliveryAddressId

	,CAST(d.[ok] as bit)as IsSettled		-- rozliczony w znaczeniu zap³acony, mo¿e IsPaid ???
	-- nie wiem czy to potrzebne ,d.[rr]				as SettlementKind	-- rozliczony magazynowo rêcznie 0/1/2
	--,document.[wplaty] as PaidUpAmount		nie u¿ywane
	,CAST(IIF(d.[rozlmg] & 0x20 <> 0, 1, 0) as bit)
						as IsStoreSettled	-- rozliczony w znaczeniu magazynowym (0/1)

	,CAST((d.[rozlmg] & 0x3) % 3 as smallint)
						as ReservationType	-- rezerwacja: brak/iloœciowa/ze wskazaniem dostaw

	,CAST(d.[rabat]	as decimal(24,2))
						as Discount

	,d.[plattyp]		as RegisterPaymentId
	,d.[formaplatn]		as PaymentMethodId
	,d.[plattermin]		as MaturityDate

	,CAST(d.[netto] as decimal(24,2))		
						as Netto

	,CAST(d.[vat]	as decimal(24,2))		
						as Vat

	,CAST(d.[walNetto] as decimal(24,2))	
						as NettoInCurrency

	,CAST(d.[walBrutto] as decimal(24,2))	
						as GrossInCurrency

	,CAST(d.[grupacen] + 1 as smallint) 
						as PriceKind		-- netto/brutto

	,d.[typceny]		as PriceTypeId		-- typ ceny

	,d.[iddokkoryg]		as CorrectedDocumentId
	--??,d.[wartoscSp] as DocumentAmount
	,CAST(d.[exp_fk] & 0x01	as bit)
						as FkExported		-- wyeksportowany do FK-a lub do pliku

	,CAST(IIF(d.[exp_fk] & 0x20 <> 0, 1, 0) as bit)
						as HmImported		-- zaimportowany z oddzia³u
	,CAST(CASE
		WHEN d.[exp_fk] & 0x04 <> 0 THEN 2
		WHEN d.[exp_fk] & 0x02 <> 0 THEN 1
		ELSE 0
	END as smallint)	as FiscalizationState		-- nie fiskalny/do fiskalizacji/zafiskalizowany 0/1/2
	,d.[paragon]		as Receipt

	,d.[waluta]			as Currency
	,CAST(d.[kurs] as decimal(24,8))
						as CurrencyRateVAT		-- kurs dla VAT
	,CAST(d.[kursDoch] as decimal(24,8))
						as CurrencyRateCIT		-- kurs dla CIT/PIT
	--,document.[zyskdod] as DocumentProfit	nie uzywane

	--,document.[kod_obcy] as ForeignDocumentNumber nie uzywane

	,d.[schemat]		as AccountingPattern

	,d.[wystawil]		as IssuerId

	,d.[createdBy]		as CreatedBy
	,d.[createdDate]	as CreationDate
	,d.[modifiedBy]		as ModifiedBy
	,d.[modifiedDate]	as ModificationDate

	,d.[statusRDF]		as RdfState
	--,d.[statusFK]			as FkState	wspó³praca z SD
	,d.[eFaktura]		as eInvoiceState

	,CAST(d.[bufor]	as bit)	
						as IsNotIssued			-- nie wystawiony
	--,document.[super] as Super				nie uzywane
	,d.[anulowany]		as Canceled
 
	,CAST(IIF(d.[flag] & 0x8000 <> 0, 1, 0)	as smallint)
						as CalculateVatMethod			-- 0 - iloczynowy, 1 - sumacyjny

    ,CAST((pn.[kwota] - pn.[kwotarozl]) AS decimal(24,8)) AS AmountToPay
    ,IsNull(trans.[status], 4) AS DocumentStatus
	 ,CASE trans.status
	  WHEN 8 THEN 'Wydanie częściowe'
	  WHEN 16 THEN 'Wydanie przygotowane'
	  WHEN 32 THEN 'Towar wydano'
	  WHEN 4 THEN 'Nie wydano'
	  ELSE 'Nie wydano'
	  END AS DocumentStatusDescription
	, CAST( CASE WHEN zz.id IS NULL THEN 0 ELSE 1 END AS BIT ) AS FromFiscalReceipt -- czy faktura do paragonu fiskalnego
	, d.splitPayment	as SplitPayment
	, CAST(IIF(d.[flag] & 0x4000 <> 0, 1, 0) as bit)
						as IsArchived		-- zaimportowany z archiwum
    ,d.[typeExternal] as TypeExternal
    ,d.[idExternal] as IdExternal
    ,d.[id2External] as Id2External
  FROM [HM].[DK] AS d
  LEFT JOIN [HM].[PN] AS pn		ON pn.dkid = d.id
  LEFT JOIN [hm].[tr] AS trans	ON trans.typ = 1 AND trans.owner_base = 16 AND trans.owner_id = d.id	-- ³¹czymy transakcje do sprzeda¿y
  LEFT JOIN [HM].[ZZ] AS zz		ON zz.typ = 150 AND zz.baza2 = 16 AND zz.id2 = d.id                     -- czy faktura do paragonu fiskalnego
  where d.typ = 0;

GO