USE [CRM0100_SMARTTECH]
GO

/****** Object:  StoredProcedure [accounting].[warehouse_exportToFinancialSystem]    Script Date: 11.03.2019 20:49:53 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



ALTER PROCEDURE [accounting].[warehouse_exportToFinancialSystem]
	@documentID uniqueidentifier, 
	@username varchar(50),
	@setStatus bit = 0,
	@test int =0  --1 oznacza testowe wywolanie
AS
BEGIN

 /*
 exec [accounting].[warehouse_exportToFinancialSystem] 
 @documentID='bc342899-05dc-e811-941d-d89d6719e0ca', @username='m.kotynia',@setStatus =0,@test=1
 
update document.document  set financialSystemExport = null where   financialSystemExport is not null
select * from document.document where financialSystemExport is not null


 */


	SET NOCOUNT ON;

    declare @currentFinancialSystemExportDate datetime,
            @canBeExported int,
            @documentfullNumber nvarchar(300),
            @currencyCode varchar(50),
            @currencyExchangeRate decimal(18,4),
            @currencyExchangeDate smalldatetime,
            @locationCode  varchar(400) ,
            @documentType varchar(400),
            @c int,
            @request_xml     XML,
            @request_xml_ans XML,
            @correcteDocumentId uniqueidentifier,
            @USER_REW       NVARCHAR(50)


      
                SELECT @USER_REW =
                                        CASE WHEN @username ='k.musialowska'   then 'KM'
                                             WHEN @username ='m.dzierzanowska' then 'M'
                                        ELSE 'B'
                                        END
	select @currentFinancialSystemExportDate = d.financialSystemExport,
           @documentfullNumber = d.documentFullNR,
           @locationCode    =l.locationCode,
           @documentType = d.documentType

	from document.document d
    left join wms.location l ON d.warehouseID=l.locationID
     where d.documentID = @documentID
     
     SELECT @c=
     CASE WHEN   @documentType IN (SELECT DISTINCT documentType from document.documentType 
     where componentName='documentIn'
     AND documentType not in
     ('ZW','ZWD','ZWR')
     )    then 1 else 0 end    
       
 
     SELECT    @currencyCode=currency,
               @currencyExchangeRate=currencyExchangeRate,
               @currencyExchangeDate=currencyExchangeDate 
     FROM   document.document 
     WHERE  documentID= @documentID


   DECLARE 
        @PW_organisationUnit varchar(250)   /*WYJĄTEK DLA PW WYDZIAŁ W PRZYPDAKU DEKRETU MA BYĆ POBIERANY Z PIERESZEJ POZYCJI DOKUMENTU KTÓRA MA USTALONY WYDZIAŁ*/

    SELECT @PW_organisationUnit= X.accounting
    FROM(
        SELECT   TOP 1
                cd_X.accounting
        FROM document.document D
        INNER JOIN document.documentLine DL ON D.documentID=DL.documentID
        INNER JOIN wms.commisionLine CL ON CL.commisionLineID=DL.commisionLineID
        INNER JOIN wms.commision C ON C.commisionID=CL.commisionID
        INNER JOIN common.dictionary cd_X ON  cd_X.dictionaryKey=c.organisationUnitID and cd_X.dictionaryName='organisationUnit' and cd_X.status>-1
        WHERE 
            DL.status>-1
        AND C.organisationUnitID IS NOT NULL
        AND D.documentID=@documentID
        AND D.documentType='PW'
        ORDER BY DL.documentOrdinalNumber
        )X
	
    /*dla korekt dokumentów magazynowych */
        declare 
                @correctedDocumentID        uniqueidentifier,
                @correctedInvoiceCategory   varchar(100)

        SELECT  @correctedDocumentID =documentID
        FROM(
        SELECT  top 1 correct_dl.documentID
		        FROM document.documentline x
                inner join document.document d ON d.documentID = x.documentID 
                LEFT JOIN document.documentLine correct_dl ON x.sourceDocumentLineID = correct_dl.documentLineID AND x.isCorrectiveLine=1 and correct_dl.status>-1
		        left  join document.invoiceline il ON isnull(x.invoiceLineId,correct_dl.invoiceLineId)=il.invoiceLineID --and il.status >-1
        WHERE d.documentID=@documentID
        and x.status>-1
        )x
        SELECT @correctedInvoiceCategory=invoiceCategory
        from (
        SELECT top 1 i.invoiceCategory from document.documentLine x
		        left  join document.invoiceline il ON x.invoiceLineId=il.invoiceLineID and il.status >-1
		        left  join document.invoice i on i.invoiceID = il.invoiceid
        where documentID=@correctedDocumentID
            and x.status>-1
                AND i.invoiceCategory  is NOT null
        )x
declare @invoice_category varchar(200)


SELECT TOP 1 @invoice_category = invoiceCategory from 
(
SELECT  distinct
        d.documentID,d.documentFullNR,
        COALESCE( i.invoiceFullNR,i2.invoiceFullNR,c_d.invoiceNR) invoiceFullNR,
        x.invoiceCategory                
FROM document.document d
INNER JOIN document.documentType dt ON d.documentType = dt.documentType
LEFT JOIN document.documentLine dl ON d.documentID = dl.documentID
LEFT JOIN document.invoiceLine il ON dl.invoiceLineId = il.invoiceLineID
LEFT JOIN document.invoice i on i.invoiceID=il.invoiceID
LEFT JOIN document.documentLine c_dl on c_dl.documentLineID =  dl.sourceDocumentLineID and dl.isCorrectiveLine=1
left JOIN document.document c_d on c_d.documentID=c_dl.documentID
LEFT JOIN document.invoiceLine il2 ON c_dl.invoiceLineId = il2.invoiceLineID
LEFT JOIN document.invoice i2 on i2.invoiceID=il2.invoiceID
LEFT JOIN wms.location l on l.locationID=d.warehouseID
LEFT JOIN document.invoice x ON x.invoiceFullNR=COALESCE( i.invoiceFullNR,i2.invoiceFullNR,c_d.invoiceNR)
WHERE replace(dt.DocumentType,'KO_','') = 'WZ'  
	and (dl.[status] > -1 or dl.[status] is null)
	and (il.[status] > -1 or il.[status] is null) 
	and d.[status] =1          
    and d.documentID = @documentID
)x
/*dla kolejnych korekt wz pobieranie typu faktury*/
declare 
        @initialCorrectedDocumentId uniqueidentifier
select  
        @initialCorrectedDocumentId = initialCorrectedDocumentId
from document.document where documentID=@documentID
IF @invoice_category IS NULL AND @correctedInvoiceCategory is null and @documentType in ('KO_WZ')
BEGIN

SELECT TOP 1 @invoice_category = invoiceCategory from 
(
   SELECT  distinct
        d.documentID,d.documentFullNR,
        COALESCE( i.invoiceFullNR,i2.invoiceFullNR,c_d.invoiceNR) invoiceFullNR,
        x.invoiceCategory                
FROM document.document d
INNER JOIN document.documentType dt ON d.documentType = dt.documentType
LEFT JOIN document.documentLine dl ON d.documentID = dl.documentID
LEFT JOIN document.invoiceLine il ON dl.invoiceLineId = il.invoiceLineID
LEFT JOIN document.invoice i on i.invoiceID=il.invoiceID
LEFT JOIN document.documentLine c_dl on c_dl.documentLineID =  dl.sourceDocumentLineID and dl.isCorrectiveLine=1
left JOIN document.document c_d on c_d.documentID=c_dl.documentID
LEFT JOIN document.invoiceLine il2 ON c_dl.invoiceLineId = il2.invoiceLineID
LEFT JOIN document.invoice i2 on i2.invoiceID=il2.invoiceID
LEFT JOIN wms.location l on l.locationID=d.warehouseID
LEFT JOIN document.invoice x ON x.invoiceFullNR=COALESCE( i.invoiceFullNR,i2.invoiceFullNR,c_d.invoiceNR)
WHERE replace(dt.DocumentType,'KO_','') = 'WZ'  
	and (dl.[status] > -1 or dl.[status] is null)
	and (il.[status] > -1 or il.[status] is null) 
	and d.[status] = 1          
    and d.documentID = @initialCorrectedDocumentId
)h
END 
            select   @canBeExported= 
                    COUNT(*)
            from 
                        (
 			                        SELECT  
				                        COALESCE(
						                        cA.wn_account_mask,                           
						                        isnull(cast([dbo].[fn_accounting_warehouse](
							                        dc_WN.accountMASK ,		--szablon konta ksiegowego
							                        x.itemid,			    --itemcode (przekazujemy itemid)
							                        f.countrycode,		    --countrycode
                                                CASE WHEN D.DOCUMENTTYPE='PW' THEN @PW_organisationUnit
                                                ELSE
					                            isnull(cd.accounting ,cd_X.accounting) 
                                                END ,                            --@organisationUnit	 (tutaj mozna by przekazc od razu jaki kod ksiegowy ale chodzi o spojnosc),
							                        f.firmNR                -- id firmy (podmieniany numer dla spójności)
                                                    ,d.documentType
                                                    ,@locationCode
                                                    ,c.commisionGroupID
													,x.documentLineID
							                        ) as varchar(max)),'-'))  as wn, 
				                        COALESCE(
						                        cA.ma_account_mask,
						                        isnull(cast([dbo].[fn_accounting_warehouse](
							                        dc_MA.accountMASK ,		    --szablon konta ksiegowego
							                        x.itemid,			        --itemcode (przekazujemy itemid)
							                        f.countrycode,		        --countrycode
                                                CASE WHEN D.DOCUMENTTYPE='PW' THEN @PW_organisationUnit
                                                ELSE
					                            isnull(cd.accounting ,cd_X.accounting) 
                                                END ,                            --@organisationUnit	 (tutaj mozna by przekazc od razu jaki kod ksiegowy ale chodzi o spojnosc),
							                        f.firmNR                    --id firmy (podmieniany numer dla spójności)
                                                    ,d.documentType
                                                    ,@locationCode
                                                    ,c.commisionGroupID
													,x.documentLineID
							                        ) as varchar(max)),'!-'))  as ma
		                        FROM document.documentline x
                                inner join document.document d ON d.documentID = x.documentID 
		                        left  join wms.itemClass ic on x.itemid = ic.itemid and ic.mainGroup = 1
                                left  join common.dictionary itemsubcategory on ic.itemSubCategoryID = itemsubcategory.dictionaryKey and itemsubcategory.dictionaryName = 'itemsubcategory'
                                left  join document.documentType dt ON dt.documentType=d.documentType and dt.componentName in ('documentIn','documentOut')
		                        left  join wms.location l  on l.locationID  = d.warehouseID
		                        left  join wms.location lt on lt.locationID = d.warehouseIDB
		                     --   left  join document.invoiceline il ON x.invoiceLineId=il.invoiceLineID and il.status >-1
		                        left  join document.invoice i on  i.invoiceFullNR = d.invoiceNR --i.invoiceID = il.invoiceid
		                        left  join common.dictionary cd ON cd.dictionaryKey=d.organisationUnitID and cd.dictionaryName='organisationUnit' and cd.status>-1
		                        left  join wms.commisionLine cl ON cl.commisionLineID=x.commisionLineID and cl.status>-1
		                        left  join wms.commision     c  ON c.commisionID = cl.commisionID and c.status>-1
		                        left  join common.dictionary cd_X ON cd_X.dictionaryKey=c.organisationUnitID and cd_X.dictionaryName='organisationUnit' and cd_X.status>-1
		                        left  join firm.firm f on d.firmID = f.firmid
                        /*polaczenie z szablonem ksiegowania  DLA STRONY MA!!!!!*/
	                        left join accounting.warehouseconfiguration dc_MA   
	                        on  dc_MA.documentType = case when d.documentType='KO_RWP' then 'RW-P' 
                                                               when d.documentType='ZW' then 'RW-P'
                                                               when d.documentType='KO_WZK' then '*WZK'
                                                                else  REPLACE(d.documentType,'KO_','')   end
	                        and dc_MA.warehouse1 = l.locationCode and dc_MA.status>-1
	                        and isnull(dc_MA.warehouse2,'') = isnull(lt.locationCode,'')
                            and   (
                                          isnull(dc_MA.invoiceCategory,'') =  isnull(REPLACE(isnull(@invoice_category,@correctedInvoiceCategory),'K',''),'')
                                     or   d.documentType not in ('KO_WZ','WZ')--'*PZK',    
                                  )
	                        and   (
                                        isnull(dc_MA.commisionGroup,'')  =  isnull(c.commisionGroupID,'')
                                    or   d.documentType not in ('RW-P','KO_RWP','ZW')    
                                    )
                            AND dc_MA.WN_MA='MA'/*!!!!!*/

                        /*polaczenie z szablonem ksiegowania  DLA STRONY WN!!!!!*/
	                        left join accounting.warehouseconfiguration dc_WN    
	                        on  dc_WN.documentType =  case when d.documentType='KO_RWP' then 'RW-P' 
                                                           when d.documentType='ZW' then 'RW-P'
                                                           when d.documentType='KO_WZK' then '*WZK'
                                                            else  REPLACE(d.documentType,'KO_','')   end and dc_WN.status>-1
	                        and dc_WN.warehouse1 = l.locationCode
	                        and isnull(dc_WN.warehouse2,'') = isnull(lt.locationCode,'')
	                        and   (
                                        isnull(dc_WN.invoiceCategory,'')  =  isnull(REPLACE(isnull(@invoice_category,@correctedInvoiceCategory),'K',''),'')
                                    or   d.documentType not in ('KO_WZ','WZ') -- '*PZK',  
                                    )	
                            and   (
                                        isnull(dc_WN.commisionGroup,'')  =  isnull(c.commisionGroupID,'')
                                    or   d.documentType not in ('RW-P','KO_RWP','ZW')    
                                    )
                            AND dc_WN.WN_MA='WN'/*!!!!!*/        
                        /*polaczenie z szablonem konta wpisanym ręcznie!!!*/
                            left join accounting.customeAccounting cA ON 
                                                       (
                                                                (
                                                                        cA.remoteID = x.documentLineID 
                                                                        AND ( d.documentType = 'WZ' OR  DT.baseDocumentType ='RWP' OR d.documentType = 'RW')
                                                                )
                                                          OR
                                                        (
                                                             cA.remoteID = isnull(isnull(x.initialSourceDocumentLineId,x.sourceDocumentLineID),x.documentLineID) 
                                                             and (d.documentType like 'KO%' 
                                                                    OR 
                                                                  d.documentType IN ('ZW','ZWD','ZWR') 
                                                                 )
                                                        )
                                                       or 
                                                        (
                                                            cA.remoteID = x.documentLineID
                                                            AND NOT EXISTS (SELECT 1 FROM accounting.customeAccounting XV WHERE XV.remoteID=isnull(x.initialSourceDocumentLineId,x.sourceDocumentLineID))

                                                        )  
                                                         ) and cA.remoteSource = 'documentline'        
	                        where x.status>-1
	                        and (d.documentID = @documentID)
	                        and  d.status=1
	                        and  x.status>-1
	                        and dt.baseDocumentType NOT in ('WMM') 
                        )  x
                                    where x.ma  like '%!%'
                                    or    x.wn  like '%!%'
       
        declare @mess nvarchar(3000)='#error.accounting.wrongAccountMask '+@documentfullNumber
            if @canBeExported>0 
                BEGIN
			        RAISERROR(@mess, 16, 1)
			        return;
                END
 /* ZABEZBIECZENIE PRZED WYSŁANIEM KOREKTY PRZED ZAKSIĘGOWANIEM DOKUMENTU ŹRÓDŁOWEGO */

 IF @documentType like 'KO%' OR @documentType IN ('ZW','ZWD','ZWR') 
 BEGIN
        select @correcteDocumentId = d.correctedDocumentId
        from   document.document d
        where  d.documentID = @documentID

        IF EXISTS (
                        SELECT 1 
                        FROM   document.document d
                        inner join 
                                    (
                                        SELECT  dl.documentID,
                                                SUM(ISNULL(dl.sysitemValue, 0))summ
                                        from document.documentLine dl 
                                        where   dl.status>-1
                                        and     dl.documentid = @correcteDocumentId
                                        group by  dl.documentID
                                        having SUM(ISNULL(dl.sysitemValue, 0))<>0
                                    )x on x.documentID =d.documentID
                        where  d.documentID = @correcteDocumentId
                        and    d.financialSystemExport is null
                        and    d.status=1
                  )
        BEGIN
               set @mess='#error.accounting.sourceDocumentUnaccounted '+@documentfullNumber
			        RAISERROR(@mess, 16, 1)
			        return;                
        END
 END;
 ----oznaczenie tylko ze jest do wyslania
 if (@setStatus =0 and 1= 0 )
 Begin	
	update document.document
	set financialSystemExport = GETUTCDATE()
	where documentid = @documentID 
 End

 --Eksportujemy tylko jesli nei bylo juz wczesniej exportowane
 --Status 2 oznacza wyeksportowane do systemu FK
 if ((@setStatus =1  and @canBeExported=0 ) or @test =1)
 Begin
  
 SELECT @request_xml =
 (
 select 
         NEWID()               'requestId',
        'CommercialDocument'   'method',
         (
          select   ISNULL(@USER_REW ,'B')               'username',
                    'WarehouseDocument'     'submethod',
                   d.documentID             'foreignId',
                   '2033'                   'type',
                   0                        'typeAbbreviation'  ,
                   1                        'documentSequentialNumber',
                   'WP'                     'documentSeries',
                   NULL                     'supplierDocumentNumber',
                   Year(d.documentDate    ) 'documentYear',
                   d.documentFullNR+'/'+l.locationCode          'documentNumber',
                   NULL                      'correctedNumber',
                   NULL                      'correctedDate',
                   d.documentDate            'datee',
                   isnull(f.firmNR,'-1')     'contractorId'   ,
 ----------------------------------contractor----------------------------------------------------------------------- 
 ----------------------------------contractor----------------------------------------------------------------
 ----------------------------------contractor---------------------------------------------------------------------- 
                   f.firmNR                 'contractor/codeContractor',
                   f.taxCode                'contractor/taxId',
                   f.firmName               'contractor/name',
                   f.firmNameShort          'contractor/shortName',
                   d.documentDate           'contractor/dateCreate',
                   16                       'contractor/type',
                   upper(f.countryCode)     'contractor/address/country',
                   f.postCode               'contractor/address/postCode',
                   f.city                   'contractor/address/city',
                   f.countryState           'contractor/address/region',
                   f.flatNumber             'contractor/address/premise',
                   f.houseNumber            'contractor/address/building',
                   f.streetName             'contractor/address/street',
                   1  'contractor/accountingEntries/accountingEntrie/@skmId',
                   case when isnull(upper(f.countryCode),'PL') ='PL' then '200' else '211' end  'contractor/accountingEntries/accountingEntrie',
 ------------------------------------------------------------------------------------------------------------------- 
 -------------------------------------------------------------------------------------------------------------------
 -------------------------------------------------------------------------------------------------------------------               
                   dl.sysItemValue                                           'gross',
                   dl.itemValue                                              'grossInCurrency',
                   isnull(nullif(d.currency,''),'PLN')                                  'currency',
                   isnull(d.currencyExchangeRate,1)                          'exchangeRate',
                   case when isnull(d.currencyExchangeRate,1)<>1 then 2 end  'exchangeRateType',
               --    case when isnull(d.currencyExchangeDate ,1)<>1 then 2 end 'date',
                   d.documentDate                                            'operationDate',
                   d.documentDate                                            'periodDate',
                   d.documentDate                                            'incomeDate',
                   d.documentDate                                            'registrationDate',
                   d.documentMemo                                            'description',
                   N'MAGAZYN'                                                'accountingRule',
                   'MAG'                                                     'vatRegister',
                    NULL                                                     'sendingCountry', 
 ------------------------------------------------------------------------------------------------------------------- 
 -------------------------------------------------------------------------------------------------------------------
 -------------------------------------------------------------------------------------------------------------------               
(

SELECT  
           ROW_NUMBER() OVER (partition by itemsubcategory ORDER BY SUM(KWOTA_WN)  desc)	'order',
           SUM(KWOTA_WN)                                                            'grossDebet',
		   SUM(KWOTA_WN_WALUTA)                                                     'grossInCurrencyDebet',
           case when isnull(SUM(KWOTA_WN) ,0) <> 0 THEN SYMBOL_KONTA ELSE '' end    'syntheticAccountDebet',
           ''                                                                       'analyticAccountDebet',
           SUM(KWOTA_MA)                                                            'grossCredit',
           SUM(KWOTA_MA_WALUTA)                                                     'grossInCurrencyCredit',
            case when isnull(SUM(KWOTA_MA) ,0) <> 0 THEN SYMBOL_KONTA ELSE '' end   'syntheticAccountCredit',       
           ''                                                                       'analyticAccountCredit',
           upper(KOD_WALUTY)                                                        'currency',
           ISNULL(WARTOSC_KURSU,1)                                                  'exchangeRate',
           case when ISNULL(WARTOSC_KURSU,1) <>1 then 2 end                         'exchangeRateType',
           SYMBOL_DOKUMENTU                                                                      'description'
FROM 
(    
SELECT   
                D.documentFullNR+'/'+isnull(@locationCode,'')	                SYMBOL_DOKUMENTU, 
			    upper(isnull(nullif(d.currency,''),'PLN') )                     KOD_WALUTY ,
				CASE WHEN D.currencyExchangeRate IS NULL THEN NULL ELSE 'R' end RODZAJ_KURSU,
				D.currencyExchangeRate			                                WARTOSC_KURSU,
				COALESCE(
                       CASE WHEN ca.s='WN'  THEN  cA.customMaskmask
                            WHEN ca.s='MA' THEN   cA.customMaskmask
                            END,                          
						isnull(cast([dbo].[fn_accounting_warehouse](
                        REPLACE(REPLACE(REPLACE(dc_MA_WN.accountMASK, CHAR(13) + CHAR(10), ''), CHAR(13), ''), CHAR(10), ''), 	--szablon konta ksiegowego
							x.itemid,			    --itemcode (przekazujemy itemid)
							f.countrycode,		    --countrycode
                        CASE WHEN D.DOCUMENTTYPE='PW' THEN @PW_organisationUnit
                        ELSE
					    isnull(cd.accounting ,cd_X.accounting) 
                        END ,                            --@organisationUnit	 (tutaj mozna by przekazc od razu jaki kod ksiegowy ale chodzi o spojnosc),
							f.firmNR                     -- id firmy (podmieniany numer dla spójności)
                            ,d.documentType
                            ,@locationCode
                            ,c.commisionGroupID
							,x.documentLineID
							) as varchar(max)),'-'))  as                        SYMBOL_KONTA,
                CASE WHEN isnull(dc_MA_WN.WN_MA,ca.s)='WN' THEN x.sysItemValue * CASE when d.documentType = 'ZW' then (-1) else 1 end
                ELSE null END                                                      KWOTA_WN,
				CASE WHEN isnull(dc_MA_WN.WN_MA,ca.s)='MA' THEN x.sysItemValue * CASE when d.documentType = 'ZW' then (-1) else 1 end 
                ELSE null END                                                      KWOTA_MA,

				x.ItemValue * CASE when d.documentType = 'ZW' then (-1) else 1 end KWOTA_WN_WALUTA,
				x.ItemValue * CASE when d.documentType = 'ZW' then (-1) else 1 end KWOTA_MA_WALUTA,
            
            CASE when d.documentType in ('WZ','KO_WZ')  THEN itemsubcategory.dictionaryValue
                 when d.documentType in ('RW-P','ZW','KO_RWP') and dc_MA_WN.accountMASK like'%itemcode%'  THEN itemsubcategory.dictionaryValue
                 when d.documentType in ('RW-P','ZW','KO_RWP') THEN isnull(c.commisionGroupID,'')
            else '' END itemsubcategory

		FROM document.documentline x
        inner join document.document d ON d.documentID = x.documentID 
		left  join wms.itemClass ic on x.itemid = ic.itemid and ic.mainGroup = 1
        left  join common.dictionary itemsubcategory on ic.itemSubCategoryID = itemsubcategory.dictionaryKey and itemsubcategory.dictionaryName = 'itemsubcategory'
        left  join document.documentType dt ON dt.documentType=d.documentType and dt.componentName in ('documentIn','documentOut')
		left  join wms.location l  on l.locationID  = d.warehouseID
		left  join wms.location lt on lt.locationID = d.warehouseIDB
		left  join document.invoice i on i.invoiceFullNR = d.invoiceNR --i.invoiceID = il.invoiceid
		left  join common.dictionary cd ON cd.dictionaryKey=d.organisationUnitID and cd.dictionaryName='organisationUnit' and cd.status>-1
		left  join wms.commisionLine cl ON cl.commisionLineID=x.commisionLineID and cl.status>-1
		left  join wms.commision     c  ON c.commisionID = cl.commisionID and c.status>-1
		left  join common.dictionary cd_X ON cd_X.dictionaryKey=c.organisationUnitID and cd_X.dictionaryName='organisationUnit' and cd_X.status>-1
        LEFT  JOIN document.documentline dl_x on dl_x.documentLineID=x.sourceDocumentLineID 
		left  join wms.commisionLine cl2 ON cl2.commisionLineID=dl_x.commisionLineID 
		left  join wms.commision     c2  ON c2.commisionID = cl2.commisionID	
    	left  join firm.firm f on d.firmID = f.firmid
           LEFT join 
                   (
                        SELECT 'wn' s, 
                                wn_account_mask     customMaskmask,
                                x.remoteID
                        from    accounting.customeAccounting x
                        where   x.remoteSource = 'documentline'
                      --  and     wn_account_mask is not null 
                        UNION ALL
                        SELECT 'ma' s,
                                ma_account_mask     customMaskmask,
                                x.remoteID
                        from    accounting.customeAccounting x
                        where   x.remoteSource = 'documentline'
                      --  and     ma_account_mask is not null 
                   )cA ON (
                                                        
                                                                (
                                                                        cA.remoteID = x.documentLineID 
                                                                        AND ( d.documentType = 'WZ' OR  DT.baseDocumentType ='RWP' OR d.documentType = 'RW')
                                                                )
                                                          OR                                                        
                                                        (
                                                             cA.remoteID = isnull(isnull(x.initialSourceDocumentLineId,x.sourceDocumentLineID),x.documentLineID) 
                                                             and (d.documentType like 'KO%' 
                                                                    OR 
                                                                  d.documentType IN ('ZW','ZWD','ZWR') 
                                                                 )
                                                        )
                                                        or 
                                                        (
                                                            cA.remoteID = x.documentLineID
                                                            AND NOT EXISTS (SELECT 1 FROM accounting.customeAccounting XV WHERE XV.remoteID=isnull(x.initialSourceDocumentLineId,x.sourceDocumentLineID))
                                                        )
                                                     )
/*polaczenie z szablonem ksiegowania  DLA STRONY MA!!!!!*/
	left join accounting.warehouseconfiguration dc_MA_WN   
	on  (
        dc_MA_WN.documentType =  case when d.documentType='KO_RWP' then 'RW-P' 
                                      when d.documentType='ZW' then 'RW-P'
                                      when d.documentType='KO_WZK' then '*WZK'
                                      else  REPLACE(d.documentType,'KO_','')  
                                 end and dc_MA_WN.status>-1
	and  dc_MA_WN.warehouse1 = l.locationCode
	and isnull(dc_MA_WN.warehouse2,'') = isnull(lt.locationCode,'')
 
    and   (
            (
                  isnull(dc_MA_WN.invoiceCategory,'') =  isnull(REPLACE(isnull(@invoice_category,@correctedInvoiceCategory),'K',''),'')
             or   d.documentType not in ('KO_WZ','WZ') --  '*PZK' ,
            )

          )

	and   (
                isnull(dc_MA_WN.commisionGroup,'')  = COALESCE(c.commisionGroupID,c2.commisionGroupID,'')
             or   d.documentType not in ('RW-P','KO_RWP','ZW')    
          )
)         
and       (
                          dc_MA_WN.WN_MA =  cA.s 
                          or  
                          not exists(select 1 from accounting.customeAccounting y 
                          where
                                                    (
                                                        (

                                                                (
                                                                        y.remoteid = x.documentLineID 
                                                                        AND ( d.documentType = 'WZ' OR  DT.baseDocumentType ='RWP' OR d.documentType = 'RW')
                                                                )
                                                          OR
                                                             y.remoteid = isnull(isnull(x.initialSourceDocumentLineId,x.sourceDocumentLineID),x.documentLineID) 
                                                             and (d.documentType like 'KO%' 
                                                                    OR 
                                                                  d.documentType IN ('ZW','ZWD','ZWR') 
                                                                 )
                                                        )
                                                        or y.remoteid = x.documentLineID
                                                     )                          
                          
                          
                          )
    ) 
		where x.status>-1
		and (d.documentID = @documentID)
		and  d.status=1
		and  x.status>-1
		and dt.baseDocumentType NOT in ('WMM') /*dokumenty wewnętrzne*/

 )X
 GROUP BY 
KOD_WALUTY	,
WARTOSC_KURSU	,
itemsubcategory	,
SYMBOL_KONTA	,
SYMBOL_DOKUMENTU
FOR XML PATH('entry'),TYPE,ELEMENTS
) entries
 
          from document.document d
          left join wms.location l on l.locationID=d.warehouseID
          left join 
                    (
                        select x.documentID,
                               sum(x.itemValue*CASE when dx.documentType = 'ZW' then (-1) else 1 end) itemValue,
                               sum(x.sysItemValue*CASE when dx.documentType = 'ZW' then (-1) else 1 end) sysItemValue
                        from   [document].[documentLineList]() x
                        inner join document.document dx on dx.documentID=x.documentId 
                        group by x.documentID,dx.documentType                       
                    )dl on dl.documentId=d.documentID
          left join firm.firm f on f.firmID = d.firmID
           where d.documentID=@documentID 
          for XML PATH(''),type,ELEMENTS
         )document
  for XML PATH('request'),TYPE , ELEMENTS
 )
  
  if (@test <>1)
    Begin
		EXEC [SERVICE].[dbo].[fractusImportDocumentCommercial] @request_xml,@request_xml_ans output
		select @request_xml_ans
		declare 
				@errorCode int,
				@errorMess varchar(max)
		select
				@errorCode =  (con.query('codeErr').value('.', 'int')),
				@errorMess = (con.query('message').value('.', 'varchar(4000)'))
		FROM  @request_xml_ans.nodes('/response/document/status') AS C ( con )


		if @errorCode <> 1
			BEGIN
						RAISERROR(@errorMess, 16, 1)
						return;
			END
		ELSE
			BEGIN
						update document.document
						set financialSystemExport = GETUTCDATE()
						where documentID = @documentID
			END
		end
	else
	Begin
		select @request_xml
	End
  
end


END
 /*
     exec [accounting].[warehouse_exportToFinancialSystem] 
     @documentID='B6D3FF50-523D-4FA6-A140-0C86FEE71A8F', 
     @username='m.kotynia',
     @setStatus =0
 */

 

 
GO


