<?xml version="1.0"?>
<root>
  <CreateTmpTaxPayersTables message="ö‘þ¢øõ ©¤¥ð ñø¢› ¢‘›þ">

    <Table TableName ="__TmpTaxPayers_Main__">
      CREATE TABLE [dbo].[__TmpTaxPayers_Main__](
      [Taxid] [nvarchar](22) NOT NULL ,
      [indatim] [nvarchar](13) NOT NULL,
      [indati2m] [nvarchar](13) NULL,
      [Inty] [smallint] NOT NULL,
      [inno] [nvarchar](10) ,
      [irtaxid] [nvarchar](22) ,
      [inp] [smallint] NOT NULL,
      [ins] [smallint] NOT NULL,
      [tins] [nvarchar](14) NOT NULL ,
      [tob] [smallint] NULL,
      [bid] [nvarchar](12),
      [tinb] [nvarchar](14),
      [sbc] [nvarchar](10),
      [bpc] [nvarchar](10),
      [bbc] [nvarchar](10),
      [bpn] [nvarchar](10),
      [scln] [nvarchar](14),
      [scc] [nvarchar](5),
      [crn] [nvarchar](12),
      [tprdis] [nvarchar](22) NOT NULL,
      [tdis] [nvarchar](19) NOT NULL,
      [tadis] [nvarchar](22) NOT NULL,
      [tvam] [nvarchar](22) NOT NULL,
      [todam] [nvarchar](22) NOT NULL,
      [tbill] [nvarchar](22) NOT NULL,
      [setm] [smallint] NOT NULL,
      [Cap] [nvarchar](22) NULL,
      [insp] [nvarchar](22) NULL,
      [tvop] [nvarchar](22) NULL,
      [tax17] [nvarchar](22) NOT NULL,
      [cdcn] [nvarchar](14) ,
      [cdcd] [int] ,
      [tonw] [nvarchar](25),
      [torv] [nvarchar](18) ,
      [tocv] [nvarchar](19) ,
      [FactorNo] [int] NOT NULL,
      [SPFId] [INT] NOT NULL,
      [FPId] [INT] NOT NULL )
    </Table>
    <Table TableName ="__TmpTaxPayers_Body__">
      CREATE TABLE [dbo].[__TmpTaxPayers_Body__](
      [sstid] [nvarchar](13) NOT NULL ,
      [sstt] [nvarchar](400),
      [am] [nvarchar](20) NOT NULL,
      [mu] [nvarchar](8),
      [nw] [nvarchar](20),
      [fee] [nvarchar](27) NOT NULL,
      [cfee] [nvarchar](20),
      [cut] [nvarchar](3),
      [exr] [nvarchar](22),
      [ssrv] [nvarchar](22),
      [sscv] [nvarchar](20),
      [prdis] [nvarchar](22) NOT NULL,
      [dis] [nvarchar](19) NOT NULL,
      [adis] [nvarchar](22) NOT NULL,
      [vra] [nvarchar](6) NOT NULL,
      [vam] [nvarchar](22) NOT NULL,
      [odt] [nvarchar](255),
      [odr] [nvarchar](6),
      [odam] [nvarchar](22),
      [olt] [nvarchar](255),
      [olr] [nvarchar](6),
      [olam] [nvarchar](22),
      [consfee] [nvarchar](22),
      [bros] [nvarchar](22),
      [tcpbs] [nvarchar](22),
      [bsrn] [nvarchar](12),
      [tsstam] [nvarchar](22),
      [vop] [nvarchar](18),
      [FactorNo] [int] NOT NULL,
      [MerchandiseId] [INT] NOT NULL,
      [SPFId] [INT] NOT NULL,
      [Id] 	[INT] NOT NULL,
      [FPId] [INT] NOT NULL
      )
    </Table>

  </CreateTmpTaxPayersTables>
  <InsertDataQuery>
    <ActionQuery Name="MainQuery">
      insert into __TmpTaxPayers_Main__
      SELECT
      '' as Taxid,
      CASE
      WHEN SPF.Cancelled &lt;&gt; 1 THEN
      case
      WHEN (select top 1 OpType from __SPFactorOp__ where SPId=SPF.Id and FPId=SPF.FPId order by id desc)=2 THEN
      cast(DATEDIFF(SECOND,'1970-01-01 00:00:00',CONVERT (datetime, CONVERT(VARCHAR(12),CONVERT(date,(select top 1 OpDate from __SPFactorOp__ where SPId=SPF.Id and FPId=SPF.FPId order by id desc)))+' '+CONVERT(VARCHAR(12),CONVERT(time,DATEADD(minute,-270,(select top 1 OpTime from __SPFactorOp__ where SPId=SPF.Id and FPId=SPF.FPId order by id desc)))), 120)) as varchar(13))+'000'
      ELSE
      cast(DATEDIFF(SECOND,'1970-01-01 00:00:00',CONVERT (datetime, CONVERT(VARCHAR(12),CONVERT(date,SPDate))+' '+CONVERT(VARCHAR(12),CONVERT(time,DATEADD(minute,-270,ETime))), 120)) as varchar(13))+'000'
      END
      ELSE
      cast(DATEDIFF(SECOND,'1970-01-01 00:00:00',CONVERT (datetime, CONVERT(VARCHAR(12),CONVERT(date,(SELECT CreateDate FROM __FactorVerifyApprove__ WHERE
      CancelId&lt;&gt;0 AND SPId =SPF.Id AND FPID=@pFPId)))+' '+CONVERT(VARCHAR(12),DATEADD(minute,-270,CONVERT(time,(SELECT CreateDate FROM __FactorVerifyApprove__ WHERE
      CancelId&lt;&gt;0 AND SPId =SPF.Id AND FPID=@pFPId)))), 120)) as varchar(13))+'000'
      END
      as indatim ,

      CASE
      WHEN SPF.Cancelled &lt;&gt; 1 THEN
      case
      WHEN (select top 1 OpType from __SPFactorOp__ where SPId=SPF.Id and FPId=SPF.FPId order by id desc)=2 THEN
      cast(DATEDIFF(SECOND,'1970-01-01 00:00:00',CONVERT (datetime, CONVERT(VARCHAR(12),CONVERT(date,(select top 1 OpDate from __SPFactorOp__ where SPId=SPF.Id and FPId=SPF.FPId order by id desc)))+' '+CONVERT(VARCHAR(12),CONVERT(time,DATEADD(minute,-270,(select top 1 OpTime from __SPFactorOp__ where SPId=SPF.Id and FPId=SPF.FPId order by id desc)))), 120)) as varchar(13))+'000'
      ELSE
      cast(DATEDIFF(SECOND,'1970-01-01 00:00:00',CONVERT (datetime, CONVERT(VARCHAR(12),CONVERT(date,SPDate))+' '+CONVERT(VARCHAR(12),CONVERT(time,DATEADD(minute,-270,ETime))), 120)) as varchar(13))+'000'
      END
      ELSE
      cast(DATEDIFF(SECOND,'1970-01-01 00:00:00',CONVERT (datetime, CONVERT(VARCHAR(12),CONVERT(date,(SELECT CreateDate FROM __FactorVerifyApprove__ WHERE
      CancelId&lt;&gt;0 AND SPId =SPF.Id AND FPID=@pFPId)))+' '+CONVERT(VARCHAR(12),DATEADD(minute,-270,CONVERT(time,(SELECT CreateDate FROM __FactorVerifyApprove__ WHERE
      CancelId&lt;&gt;0 AND SPId =SPF.Id AND FPID=@pFPId)))), 120)) as varchar(13))+'000'
      END
      as indati2m,
      [dbo].[__GetTaxPayersRepIntyFunc__] (SPF.Id,SPF.FPId) as Inty,
      SPF.Id as inno,
      case
      when (select top 1 OpType from __SPFactorOp__ where SPId=SPF.Id and FPId=SPF.FPId order by id desc)=2 and Cancelled = 0 then  --(select top 1 TaxId from __TaxPayersRepOps__ where SPId=SPF.Id AND FPID = @pFPId AND Status = 'SUCCESS' AND Ins not in (2) ORDER BY Id DESC)
      (
      select top 1 tax.TaxId from
      (Select ROW_NUMBER() over(order by id) as rowNo1, * from __SPFactorOp__ Where SPId = spf.id and FPId = spf.fpid and OpType = 2) op
      inner join
      (select ROW_NUMBER() over(order by id) as rowNo2, * from __TaxPayersRepOps__ Where SPId = spf.id and FPId = spf.fpid and Status = 'SUCCESS' ) tax
      on (op.SPId = tax.SPId and op.FPId = tax.FPId and rowNo1 = rowNo2)
      order by op.Id DESC
      )
      else
      case CONCAT( FactorType,Cancelled )
      when 30 then	(
      COALESCE((select top 1 TaxId from __TaxPayersRepOps__ tpr inner join __SPRetArticleLink__ spal
      on (tpr.SPId = spal.SPId and tpr.FPId = spal.FPId)
      inner join __spfactor__ tspf on (spal.retid=tspf.id and spal.retfpid=tspf.fpid)
      where spal.SPId = (select distinct spid from __SPRetArticleLink__ where RetId = SPF.id) and RetId != SPF.id
      and tpr.status='SUCCESS' and spf.Cancelled &lt;&gt; 1
      ORDER BY spal.RetId DESC),
      (
      select top 1 TaxId from __TaxPayersRepOps__ tpr inner join __SPRetArticleLink__ spal
      on (tpr.SPId = spal.SPId and tpr.FPId = spal.FPId)
      where spal.RetId = SPF.id
      order by RetId desc
      )
      )
      )
      when 32 then	(
      COALESCE((select top 1 TaxId from __TaxPayersRepOps__ tpr inner join __SPRetArticleLink__ spal
      on (tpr.SPId = spal.RetId and tpr.FPId = spal.RetFPId)
      inner join __spfactor__ tspf on (spal.retid=tspf.id and spal.retfpid=tspf.fpid)
      where spal.SPId = (select distinct spid from __SPRetArticleLink__ where RetId = SPF.id) and RetId != SPF.id
      and tpr.status='SUCCESS' and spf.Cancelled &lt;&gt; 1
      ORDER BY spal.RetId DESC),
      (
      select top 1 TaxId from __TaxPayersRepOps__ tpr inner join __SPRetArticleLink__ spal
      on (tpr.SPId = spal.SPId and tpr.FPId = spal.FPId)
      where spal.RetId = SPF.id
      order by RetId desc
      )
      )
      )
      when 31 then  (select top 1 TaxId from __TaxPayersRepOps__ where SPId = SPF.Id AND FPID=@pFPId AND Status='SUCCESS' AND Ins not in (3) ORDER BY Id DESC) -- ابطال برگشت از فروش
      when 11 then  (select top 1 TaxId from __TaxPayersRepOps__ where SPId = SPF.Id AND FPID=@pFPId AND Status='SUCCESS' AND Ins not in (3) ORDER BY Id DESC) --
      when 51 then  (select top 1 TaxId from __TaxPayersRepOps__ where SPId = SPF.Id AND FPID=@pFPId AND Status='SUCCESS' AND Ins not in (3) ORDER BY Id DESC) --
      else ''
      end
      end as irtaxid,
      CASE [dbo].[__GetTaxPayersRepIntyFunc__] (SPF.Id,SPF.FPId) WHEN 2 THEN 1 ELSE [dbo].[__GetTaxPayersRepINPFunc__] (SPF.Id,SPF.FPId) END as inp,
      CASE
      WHEN (select top 1 OpType from __SPFactorOp__ where SPId=SPF.Id and FPId=SPF.FPId order by id)=2 and SPF.Cancelled = 1 		THEN 3
      WHEN (select top 1 OpType from __SPFactorOp__ where SPId=SPF.Id and FPId=SPF.FPId order by id)=2 						        THEN 2
      WHEN SPF.Cancelled = 1			                                                					        				THEN 3
      WHEN SPF.FactorType in (1,5)		                                                					        				THEN 1
      WHEN SPF.FactorType = 3					                                        					        				THEN 4
      END as ins,
      (select RegisterCode from __CompanyInfo__ WHERE Id=1) as tins,
      [dbo].[__getTOBFunc__](SPF.Id,SPF.FPId) as  tob,
      (select Substring(RegisterCode,0,12) from __CustomerTaxInfo__ where  FPId=@pFPId AND  FPId=SPF.FPId and CustId=SPF.CustomerId) as bid,
      (select RegisterCode+ValidationTaxCode from __CustomerTaxInfo__ where  FPId=@pFPId AND  FPId=SPF.FPId and CustId=SPF.CustomerId) as tinb,
      (SELECT SUBSTRING(BranchCode,1,4) FROM __CompanyInfo__ where Id = 1) as sbc,
      (select PostalCode from __Customer__ where  FPId=@pFPId AND  FPId=SPF.FPId and Id=SPF.CustomerId) as bpc,
      CASE
      WHEN [dbo].[__getTOBFunc__](SPF.Id,SPF.FPId) is not null THEN (select Substring(BranchCode,1,4) FROM __CustomerTaxInfo__ where  FPId=@pFPId AND  FPId=SPF.FPId and CustId=SPF.CustomerId)
      else ''
      END  as bbc,
      '' as bpn,
      '' as scln,
      Ltrim(STR((select CustomsImportCode from __SPQImportExport__ SPQIE where SPQIE.SPId=SPF.Id AND SPQIE.FPId=SPF.FPId),5,0)) as scc,
      (select UniqueSellerContract from __SPQImportExport__ SPQIE where SPQIE.SPId=SPF.Id AND SPQIE.FPId=SPF.FPId) as crn,
      Ltrim(STR(dbo.SPPCFLOOR(COALESCE((select sum(SPA.Amount*SPA.UnitPrice) from __SPArticle__ SPA where SPA.SPId=SPF.Id AND SPA.FPId=SPF.FPId),0)),18,0)) as tprdis,
      Ltrim(STR(dbo.SPPCFLOOR(COALESCE(SPF.Discount,0)),18,0)) as tdis,
      Ltrim(STR(dbo.SPPCFLOOR(COALESCE((select sum(SPA.Amount*SPA.UnitPrice) from __SPArticle__ SPA where SPA.SPId=SPF.Id AND SPA.FPId=SPF.FPId) - SPF.Discount,0)),18,0)) as tadis,
      Ltrim(STR(dbo.SPPCFLOOR(COALESCE(cast((select sum(TaxAmount) from __SPTax__ where SPID = SPF.Id AND FPID = SPF.FPId) as decimal(21,2) ),0)),18,0)) as tvam,
      0 as todam,
      Ltrim(STR(dbo.SPPCFLOOR(COALESCE(SPF.total,0)),18,0)) as tbill,
      COALESCE((SELECT TOP 1  SPTaxType from __SPTaxExtraInfo__ WHERE SPId=SPF.Id AND FPId=SPF.FPId),-1) AS  setm,

      CASE (SELECT TOP 1  SPTaxType from __SPTaxExtraInfo__ WHERE  FPId=@pFPId AND  SPId=SPF.Id AND FPId=SPF.FPId)
      WHEN 1 THEN Ltrim(STR(COALESCE(SPF.total,0) -
      (dbo.SPPCFLOOR(COALESCE(cast((select sum(TaxAmount) from __SPTax__ where SPID = SPF.Id AND FPID = SPF.FPId) as decimal(21,0) ),0))) -- tvam
      ,18,0))
      WHEN 2 THEN '0'
      WHEN 3 THEN
      Ltrim(STR(dbo.SPPCFLOOR(COALESCE((select sum(VAmount) from __SPTasvie__ where FPId=@pFPId AND SPId=SPF.Id AND FPId=SPF.FPId ),0))
      ,18,0))
      END AS Cap,

      CASE (SELECT TOP 1  SPTaxType from __SPTaxExtraInfo__ WHERE FPId=@pFPId AND  SPId=SPF.Id AND FPId=SPF.FPId)
      WHEN 1 THEN '0'
      WHEN 2 THEN Ltrim(STR(
      dbo.SPPCFLOOR(COALESCE(SPF.total,0)) -
      dbo.SPPCFLOOR(COALESCE(cast((select sum(TaxAmount) from __SPTax__ where SPID = SPF.Id AND FPID = SPF.FPId) as decimal(21,2) ),0))
      ,18,0))
      WHEN 3 THEN  convert(varchar(22),Ltrim(STR(
      dbo.SPPCFLOOR(COALESCE(SPF.Total,0)) -
      dbo.SPPCFLOOR(COALESCE(cast((select sum(TaxAmount) from __SPTax__ where SPID = SPF.Id AND FPID = SPF.FPId) as decimal(21,2) ),0))-
      dbo.SPPCFLOOR(COALESCE((select sum(VAmount) from __SPTasvie__ where SPId=SPF.Id
      AND FPId=SPF.FPId ),0))
      ,18,0)))
      end as insp,
      null as tvop,
      0 as tax17,
      (select KotajNo from __SPQImportExport__ SPQIE where SPQIE.SPId=SPF.Id AND SPQIE.FPId=SPF.FPId) as cdcn,
      (select (DATEDIFF(DAY,'1970-01-01 00:00:00',CONVERT (datetime, CONVERT(VARCHAR(12),CONVERT(date,KotajDate))))) from __SPQImportExport__ SPQIE where SPQIE.SPId=SPF.Id AND SPQIE.FPId=SPF.FPId) as cdcd,
      Ltrim(STR(COALESCE((select KotajWeight from __SPQImportExport__ SPQIE where SPQIE.SPId=SPF.Id AND SPQIE.FPId=SPF.FPId),0),16,8))  as tonw,
      '' as torv,
      '' as tocv,
      SPF.FactorNo,
      SPF.id as SPFId,
      SPF.FPId
      FROM __SPFactor__  SPF where Id != 0
      AND FactorType NOT IN (0,2,4) AND SPF.FPId = @pFPId
      AND (select count(*) from __SPArticle__ spa where spf.id=spa.SPId and spf.FPId=spa.FPId)>0
      AND SPF.SPDate &gt; = '@pStart' AND SPF.SPDate &lt; = '@pEnd'
      AND SPF.TEnable &lt;&gt; -1
    </ActionQuery>

    <ActionQuery Name="BodyQuery">
      insert into __TmpTaxPayers_Body__
      select
      (select TaxCode from __Merchandise__ ME where ME.Id=SPA.MerchandiseId AND ME.FPId=SPF.FPId AND ME.FPId=@pFPId) as sstid,
      (select LEFT(Name,400) from __Merchandise__ where FPId=@pFPId AND   Id=SPA.MerchandiseId AND FPId=SPA.FPId)
      as sstt,
      Ltrim(STR(COALESCE([dbo].[__GetTaxPayersRepAmountFunc__] (SPF.Id,SPA.Id,SPF.FPId,NoChange,Amount),0),22,8)) as am,
      (SELECT Code from __TaxUnit__ where Id = (SELECT TaxUnitId FROM __Unit__ where Id=SPA.UnitId and FPId=SPA.FPId) ) as mu,
      Ltrim(STR((SELECT WeightKotaj FROM __SPArticleTaxExInfo__ SPATEI where SPATEI.SPId=SPF.Id AND SPATEI.FPId=SPF.FPId AND SPATEI.SPAId=SPA.Id),20,8)) as nw,
      Ltrim(STR(ROUND(COALESCE(SPA.UnitPrice,0),9,1),27,8)) as fee,
      CASE
      when 	[dbo].[__GetTaxPayersRepIntyFunc__] (SPF.Id,SPF.FPId) = 1 AND [dbo].[__GetTaxPayersRepINPFunc__] (SPF.Id,SPF.FPId)=2 then  LTRIM(STR(ROUND( cast(Ltrim(STR(ROUND(COALESCE(SPA.UnitPrice,0),9,1),27,8)) as decimal(26,8))/ cast( Ltrim(STR(dbo.SPPCFLOOR(COALESCE(SPF.CurrencyVal,0)),18,0)) as decimal(18,0)),4,1) ,20,4)) --Ltrim(STR(ROUND(COALESCE([dbo].[__GetTaxPayersRepAmountFunc__] (SPF.Id,SPA.Id,SPF.FPId,NoChange,Amount),0),4,1),20,4))
      when  	[dbo].[__GetTaxPayersRepINPFunc__] (SPF.Id,SPF.FPId) &lt;&gt; 2 and SPF.CurrencyId &lt;&gt; 0 then Ltrim(STR(ROUND(COALESCE(SPA.UnitPrice/SPF.CurrencyVal,0),4,1),20,4))
      else null
      END as cfee,
      CASE
      WHEN [dbo].[__GetTaxPayersRepINPFunc__] (SPF.Id,SPF.FPId) in (1,2,7) THEN
      (Select TOP 1 SUBSTRING(Name,1,3) From __TmpSPQCurrency__ T INNER JOIN  __SPQRelatedCurrency__ SC ON
      SC.TmpSPQCurrencyId=T.Id AND SC.CurrencyId=SPF.CurrencyId
      )
      ELSE'IRR'
      END as cut,
      case
      when [dbo].[__GetTaxPayersRepINPFunc__] (SPF.Id,SPF.FPId) = 7 AND [dbo].[__GetTaxPayersRepIntyFunc__] (SPF.Id,SPF.FPId) = 1 then Ltrim(STR(dbo.SPPCFLOOR(COALESCE((select arzPriceparvane from __SPArticleTaxExInfo__ SPATEI where SPATEI.SPId=SPF.Id AND SPATEI.FPId=SPF.FPId AND SPATEI.SPAId=SPA.Id),1)),18,0))
      when [dbo].[__GetTaxPayersRepINPFunc__] (SPF.Id,SPF.FPId) in(1,2) AND [dbo].[__GetTaxPayersRepIntyFunc__] (SPF.Id,SPF.FPId) = 1 AND SPF.CurrencyId &lt;&gt; 0  then Ltrim(STR(dbo.SPPCFLOOR(COALESCE(SPF.CurrencyVal,0)),18,0))
      end as exr,
      case
      when [dbo].[__GetTaxPayersRepINPFunc__](SPF.Id,SPF.FPId) = 7 AND [dbo].[__GetTaxPayersRepIntyFunc__] (SPF.Id,SPF.FPId) = 1 then Ltrim(STR( dbo.SPPCFLOOR((COALESCE(SPA.UnitPrice,0) * COALESCE([dbo].[__GetTaxPayersRepAmountFunc__] (SPF.Id,SPA.Id,SPF.FPId,NoChange,Amount),0))) ,18))
      end as ssrv,
      case
      when [dbo].[__GetTaxPayersRepINPFunc__](SPF.Id,SPF.FPId) = 7 AND [dbo].[__GetTaxPayersRepIntyFunc__] (SPF.Id,SPF.FPId) = 1 AND (select KotajCurrencyAmount from __SPArticleTaxExInfo__ SPATEI where SPATEI.SPId=SPF.Id AND SPATEI.FPId=SPF.FPId AND SPATEI.SPAId=SPA.Id) > 0 then cast( (select KotajCurrencyAmount from __SPArticleTaxExInfo__ SPATEI where SPATEI.SPId=SPF.Id AND SPATEI.FPId=SPF.FPId AND SPATEI.SPAId=SPA.Id) as decimal(19,4))
      when [dbo].[__GetTaxPayersRepINPFunc__](SPF.Id,SPF.FPId) = 7 AND [dbo].[__GetTaxPayersRepIntyFunc__] (SPF.Id,SPF.FPId) = 1 then 0
      end as sscv,
      Ltrim(STR(
      CAST(dbo.SPPCFLOOR(COALESCE(
      CAST( [dbo].[__GetTaxPayersRepAmountFunc__] (SPF.Id,SPA.Id,SPF.FPId,NoChange,Amount) as  decimal(26,8))
      * CAST(SPA.UnitPrice as  decimal(26,8))
      ,0) ) as  decimal(26,8)) ,18,0)) as prdis,
      Ltrim(STR(dbo.SPPCFLOOR(COALESCE(SPA.Remainder ,0)),18)) as dis,
      case
      when [dbo].[__GetTaxPayersRepINPFunc__] (SPF.Id,SPF.FPId) = 7 then Ltrim(STR(dbo.SPPCFLOOR(COALESCE([dbo].[__GetTaxPayersRepAmountFunc__] (SPF.Id,SPA.Id,SPF.FPId,NoChange,Amount) * SPA.UnitPrice,0)),18,0))
      when [dbo].[__GetTaxPayersRepINPFunc__] (SPF.Id,SPF.FPId) = 2 then Ltrim(STR(dbo.SPPCFLOOR(COALESCE([dbo].[__GetTaxPayersRepAmountFunc__] (SPF.Id,SPA.Id,SPF.FPId,NoChange,Amount) * SPA.UnitPrice + 0 - (SPA.Remainder) ,0)),18,0))
      else Ltrim(STR(
      CAST(
      dbo.SPPCFLOOR(
      COALESCE(
      ( CAST([dbo].[__GetTaxPayersRepAmountFunc__] (SPF.Id,SPA.Id,SPF.FPId,NoChange,Amount) as  decimal(26,8))
      * CAST(SPA.UnitPrice as  decimal(26,8))) - cast(SPA.Remainder as decimal(18,0)),0))  as  decimal(26,8)
      ) ,18,0))
      end as adis,
      CASE HasGuaranty WHEN 1 THEN '0' ELSE
      case
      when (select TaxPercent+AvarezPercent from __SPVariableMerchTax__ SPV where SPV.SPId = SPA.SPId AND SPV.SPAId = SPA.Id AND SPV.FPId = SPA.FPId) is not null THEN Ltrim(STR(ROUND(COALESCE((select TaxPercent+AvarezPercent from __SPVariableMerchTax__ SPV where SPV.SPId = SPA.SPId AND SPV.SPAId = SPA.Id AND SPV.FPId = SPA.FPId),0),2,1),6,2))
      when (select TaxPercent+AvarezPercent from __MerchTax__ where MerchId = SPA.MerchandiseId AND FPId = SPA.FPId and EffectDate =(select max(EffectDate) from __MerchTax__ mt where mt.FPId=spa.FPId and mt.MerchId=spa.MerchandiseId and EffectDate &lt;= spf.SPDate) ) is not null THEN Ltrim(STR(ROUND(COALESCE((select TaxPercent+AvarezPercent from __MerchTax__ where MerchId = SPA.MerchandiseId AND FPId = SPA.FPId and EffectDate =(select max(EffectDate) from __MerchTax__ mt where mt.FPId=spa.FPId and mt.MerchId=spa.MerchandiseId and EffectDate  &lt;= spf.SPDate)),0),2,1),6,2))
      else '0'
      END
      end as vra ,
      CASE HasGuaranty WHEN 1 THEN '0' ELSE
      case
      when [dbo].[__GetTaxPayersRepINPFunc__] (SPF.Id,SPF.FPId) = 7 then Ltrim(STR(dbo.SPPCFLOOR(COALESCE( ([dbo].[__GetTaxPayersRepAmountFunc__] (SPF.Id,SPA.Id,SPF.FPId,NoChange,Amount) * SPA.UnitPrice) *
      -- vra +
      (CASE HasGuaranty WHEN 1 THEN 0 ELSE
      case
      when (select TaxPercent+AvarezPercent from __SPVariableMerchTax__ SPV where SPV.SPId = SPA.SPId AND SPV.SPAId = SPA.Id AND SPV.FPId = SPA.FPId) is not null THEN (ROUND(COALESCE((select TaxPercent+AvarezPercent from __SPVariableMerchTax__ SPV where SPV.SPId = SPA.SPId AND SPV.SPAId = SPA.Id AND SPV.FPId = SPA.FPId),0),2,1))
      when (select TaxPercent+AvarezPercent from __MerchTax__ where MerchId = SPA.MerchandiseId AND FPId = SPA.FPId and EffectDate =(select max(EffectDate) from __MerchTax__ mt where mt.FPId=spa.FPId and mt.MerchId=spa.MerchandiseId and EffectDate &lt;= spf.SPDate) ) is not null THEN (ROUND(COALESCE((select TaxPercent+AvarezPercent from __MerchTax__ where MerchId = SPA.MerchandiseId AND FPId = SPA.FPId and EffectDate =(select max(EffectDate) from __MerchTax__ mt where mt.FPId=spa.FPId and mt.MerchId=spa.MerchandiseId and EffectDate  &lt;= spf.SPDate)),0),2,1))
      else 0
      end
      END)/100
      -- vra -
      ,0)),18))
      when [dbo].[__GetTaxPayersRepINPFunc__] (SPF.Id,SPF.FPId) = 2 then Ltrim(STR(dbo.SPPCFLOOR(COALESCE( ([dbo].[__GetTaxPayersRepAmountFunc__] (SPF.Id,SPA.Id,SPF.FPId,NoChange,Amount) * SPA.UnitPrice + 0 - (SPA.Remainder)) *
      -- vra +
      (CASE HasGuaranty WHEN 1 THEN 0 ELSE
      case
      when (select TaxPercent+AvarezPercent from __SPVariableMerchTax__ SPV where SPV.SPId = SPA.SPId AND SPV.SPAId = SPA.Id AND SPV.FPId = SPA.FPId) is not null THEN (ROUND(COALESCE((select TaxPercent+AvarezPercent from __SPVariableMerchTax__ SPV where SPV.SPId = SPA.SPId AND SPV.SPAId = SPA.Id AND SPV.FPId = SPA.FPId),0),2,1))
      when (select TaxPercent+AvarezPercent from __MerchTax__ where MerchId = SPA.MerchandiseId AND FPId = SPA.FPId and EffectDate =(select max(EffectDate) from __MerchTax__ mt where mt.FPId=spa.FPId and mt.MerchId=spa.MerchandiseId and EffectDate &lt;= spf.SPDate) ) is not null THEN (ROUND(COALESCE((select TaxPercent+AvarezPercent from __MerchTax__ where MerchId = SPA.MerchandiseId AND FPId = SPA.FPId and EffectDate =(select max(EffectDate) from __MerchTax__ mt where mt.FPId=spa.FPId and mt.MerchId=spa.MerchandiseId and EffectDate  &lt;= spf.SPDate)),0),2,1))
      else 0
      end
      END)/100
      -- vra -
      ,0)),18))
      else
      case
      COALESCE((select taxpercent from __SPVariableMerchTax__ VTax WHERE FPId=@pFPId AND VTax.SPId=SPA.SPId AND VTax.SPAId=SPA.Id),-1)
      WHEN -1 THEN
      Ltrim(STR(dbo.SPPCFLOOR(COALESCE( dbo.SPPCFLOOR( CAST( [dbo].[__GetTaxPayersRepAmountFunc__] (SPF.Id,SPA.Id,SPF.FPId,NoChange,Amount) as  decimal(26,8)) * CAST(SPA.UnitPrice as  decimal(26,8)) - (SPA.Remainder)) * (select cast((TaxPercent+AvarezPercent)/100 as decimal(6,2)) from __MerchTax__ where __MerchTax__.FPId=@pFPId AND MerchId = SPA.MerchandiseId and FPId =SPA.FPId and EffectDate =(select max(EffectDate) from __MerchTax__ mt where mt.FPId=SPA.FPId AND mt.FPId=spa.FPId and mt.MerchId=spa.MerchandiseId and EffectDate &lt;= spf.SPDate)) ,0)),18))
      ELSE
      Ltrim(STR(dbo.SPPCFLOOR(COALESCE( dbo.SPPCFLOOR(cast(([dbo].[__GetTaxPayersRepAmountFunc__] (SPF.Id,SPA.Id,SPF.FPId,NoChange,Amount) * SPA.UnitPrice)as decimal(18,0)) - (SPA.Remainder)) * (select (TaxPercent+AvarezPercent)/100 from __SPVariableMerchTax__ VTax where   FPId=@pFPId AND VTax.SPId=SPA.SPId AND VTax.SPAId=SPA.Id ) ,0)),18))
      END
      END
      end as vam,

      RIGHT('',255) as odt ,
      0 as odr ,
      0 as odam ,
      RIGHT('',255) as olt ,
      '' as olr ,
      '' as olam ,
      '' as consfee,
      '' as bros,
      '' as tcpbs ,
      right('',12) as bsrn,
      case
      when [dbo].[__GetTaxPayersRepINPFunc__] (SPF.Id,SPF.FPId) = 7 then Ltrim(STR(dbo.SPPCFLOOR(COALESCE(( ([dbo].[__GetTaxPayersRepAmountFunc__] (SPF.Id,SPA.Id,SPF.FPId,NoChange,Amount) * SPA.UnitPrice + 0 - (SPA.Remainder)) + ([dbo].[__GetTaxPayersRepAmountFunc__] (SPF.Id,SPA.Id,SPF.FPId,NoChange,Amount) * SPA.UnitPrice + 0 - (SPA.Remainder)) *
      CASE HasGuaranty WHEN 1 THEN '0' ELSE
      CASE
      COALESCE((SELECT taxpercent from __SPVariableMerchTax__ VTax WHERE FPId=@pFPId AND VTax.SPId=SPA.SPId AND VTax.SPAId=SPA.Id),-1)
      WHEN -1 THEN
      COALESCE((select (TaxPercent+AvarezPercent)/100 from __MerchTax__ where MerchId = SPA.MerchandiseId and FPId =SPA.FPId and EffectDate =(select max(EffectDate) from __MerchTax__ mt where mt.FPId=spa.FPId and mt.MerchId=spa.MerchandiseId and EffectDate &lt;= spf.SPDate)),0)
      ELSE
      COALESCE((select (TaxPercent+AvarezPercent)/100 from __SPVariableMerchTax__ VTax where   FPId=@pFPId AND VTax.SPId=SPA.SPId AND VTax.SPAId=SPA.Id ),0)
      END
      END
      ) ,0)),18))
      --------------------
      when [dbo].[__GetTaxPayersRepINPFunc__] (SPF.Id,SPF.FPId) = 2 then Ltrim(STR(dbo.SPPCFLOOR(COALESCE( dbo.SPPCFLOOR( ([dbo].[__GetTaxPayersRepAmountFunc__] (SPF.Id,SPA.Id,SPF.FPId,NoChange,Amount) * SPA.UnitPrice + 0 - (SPA.Remainder)) ) + dbo.SPPCFLOOR ( ([dbo].[__GetTaxPayersRepAmountFunc__] (SPF.Id,SPA.Id,SPF.FPId,NoChange,Amount) * SPA.UnitPrice + 0 - (SPA.Remainder)) ) *
      CASE HasGuaranty WHEN 1 THEN '0' ELSE
      CASE
      COALESCE((SELECT taxpercent from __SPVariableMerchTax__ VTax WHERE FPId=@pFPId AND VTax.SPId=SPA.SPId AND VTax.SPAId=SPA.Id),-1)
      WHEN -1 THEN
      COALESCE((select (TaxPercent+AvarezPercent)/100 from __MerchTax__ where MerchId = SPA.MerchandiseId and FPId =SPA.FPId and EffectDate =(select max(EffectDate) from __MerchTax__ mt where mt.FPId=spa.FPId and mt.MerchId=spa.MerchandiseId and EffectDate &lt;= spf.SPDate)),0)
      ELSE  COALESCE((select (TaxPercent+AvarezPercent)/100 from __SPVariableMerchTax__ VTax where   FPId=@pFPId AND VTax.SPId=SPA.SPId AND VTax.SPAId=SPA.Id ),0) END
      END
      ,0)),18))
      -------------------------
      ELSE
      Ltrim(STR((COALESCE(
      dbo.SPPCFLOOR(
      CAST(
      (CAST( [dbo].[__GetTaxPayersRepAmountFunc__] (SPF.Id,SPA.Id,SPF.FPId,NoChange,Amount) as  decimal(26,8)) * CAST(SPA.UnitPrice as  decimal(26,8)) )
      - cast(SPA.Remainder as decimal(18,0)) as  decimal(26,8))
      )  +
      dbo.SPPCFLOOR(  (
      dbo.SPPCFLOOR(CAST( [dbo].[__GetTaxPayersRepAmountFunc__] (SPF.Id,SPA.Id,SPF.FPId,NoChange,Amount) as  decimal(26,8)) * CAST(SPA.UnitPrice as  decimal(26,8)))
      - cast(SPA.Remainder as decimal(18,0))
      ) *
      CASE HasGuaranty WHEN 1 THEN '0' ELSE
      CASE
      COALESCE((SELECT taxpercent from __SPVariableMerchTax__ VTax WHERE FPId=@pFPId AND VTax.SPId=SPA.SPId AND VTax.SPAId=SPA.Id),-1)
      WHEN -1 THEN
      COALESCE((select (TaxPercent+AvarezPercent)/100 from __MerchTax__ where MerchId = SPA.MerchandiseId and FPId =SPA.FPId and EffectDate =(select max(EffectDate) from __MerchTax__ mt where mt.FPId=spa.FPId and mt.MerchId=spa.MerchandiseId and EffectDate &lt;= spf.SPDate)),0)
      ELSE
      COALESCE((select (TaxPercent+AvarezPercent)/100 from __SPVariableMerchTax__ VTax where   FPId=@pFPId AND VTax.SPId=SPA.SPId AND VTax.SPAId=SPA.Id ),0)
      END
      END
      ) ,0)),18))
      end as tsstam,
      '' as vop,
      SPF.FactorNo,
      SPA.MerchandiseId,
      SPId as SPFId,
      SPA.Id,
      SPA.FPId

      from  [dbo].[__TaxPayersFactorArticleFunc__]() SPA left join __SPFactor__ SPF on SPA.FPId=SPA.FPId and SPA.SPId=SPF.Id where SPId=SPF.Id AND SPA.FPId=@pFPId
      AND (select count(*) from __SPArticle__ spa where spf.id=spa.SPId and SPF.FPId=spa.FPId)>0
      AND SPF.SPDate &gt; = '@pStart' AND SPF.SPDate &lt; = '@pEnd' AND
      ( FactorType in (1,5) OR
      (	FactorType in (3)
      AND COALESCE(Amount,0) > 0
      )
      )
      AND SPF.TEnable &lt;&gt; -1
    </ActionQuery>

  </InsertDataQuery>

  <SelectDataQuery>
    <ActionQuery TableName ="__TmpTaxPayers_Main__">
      SELECT
      FactorNo,
      Taxid,
      indatim ,
      indati2m,
      Inty,
      inno,
      irtaxid,
      inp,
      ins,
      tins,
      tob,
      bid,
      tinb,
      sbc,
      bpc,
      bbc,
      bpn,
      scln,
      scc,
      crn,
      LTRIM(STR((select SUM(cast(prdis as decimal(18,0))) from __TmpTaxPayers_Body__ TPB where TPB.FPId=TPM.FPId and TPB.SPFId=TPM.SPFId),18)) as tprdis,
      LTRIM(STR((select SUM(cast(dis as decimal(18,0))) from __TmpTaxPayers_Body__ TPB where TPB.FPId=TPM.FPId and TPB.SPFId=TPM.SPFId),18)) as tdis,
      LTRIM(STR((select SUM(cast(adis as decimal(18,0))) from __TmpTaxPayers_Body__ TPB where TPB.FPId=TPM.FPId and TPB.SPFId=TPM.SPFId),18)) as tadis,
      LTRIM(STR((select SUM(cast(vam as decimal(18,0))) from __TmpTaxPayers_Body__ TPB where TPB.FPId=TPM.FPId and TPB.SPFId=TPM.SPFId),18)) as tvam,
      LTRIM(STR((select SUM(cast(odam as float)) from __TmpTaxPayers_Body__ TPB where TPB.FPId=TPM.FPId and TPB.SPFId=TPM.SPFId),18)) as todam,
      LTRIM(STR((select SUM(cast(tsstam as decimal(18,0))) from __TmpTaxPayers_Body__ TPB where TPB.FPId=TPM.FPId and TPB.SPFId=TPM.SPFId),18)) as tbill,
      setm,

      CASE setm
      WHEN 1 THEN LTRIM(STR((select SUM(cast(tsstam as decimal(18,0)))-SUM(cast(vam as decimal(18,0)))
      from __TmpTaxPayers_Body__ TPB where TPB.FPId=TPM.FPId and TPB.SPFId=TPM.SPFId),18))
      WHEN 2 THEN '0'
      WHEN 3 THEN
      Ltrim(STR(dbo.SPPCFLOOR(COALESCE((select sum(VAmount) from __SPTasvie__ where SPId=TPM.SPFId AND FPId=TPM.FPId ),0))
      ,18,0))
      END
      as Cap,
      CASE setm
      WHEN 1 THEN '0'
      WHEN 2 THEN LTRIM(STR((select SUM(cast(adis as decimal(18,0))) from __TmpTaxPayers_Body__ TPB where TPB.FPId=TPM.FPId and TPB.SPFId=TPM.SPFId),18))
      WHEN 3 THEN  LTRIM(STR(
      (select SUM(cast(tsstam as decimal(18,0)))-SUM(cast(vam as decimal(18,0)))
      from __TmpTaxPayers_Body__ TPB where TPB.FPId=TPM.FPId and TPB.SPFId=TPM.SPFId)
      - dbo.SPPCFLOOR( (COALESCE((select sum(VAmount) from __SPTasvie__ where SPId=TPM.SPFId AND FPId=TPM.FPId ),0)) )
      ,18))
      END
      as insp,
      LTRIM(STR(
      (select SUM(cast( ( (vam * (CASE WHEN temp.setm in (1,3) THEN cap else 0 END)) / case tadis when 0 then 1 else tadis end ) as decimal(18,0))) from __TmpTaxPayers_Body__ TPB
      join (select id ,SUM(cast(tsstam as decimal(18,0)))-SUM(cast(vam as decimal(18,0))) as cap , SUM(cast( adis as decimal(18,0))) as tadis , TPM.setm as setm
      from __TmpTaxPayers_Body__ t group by id) temp on  temp.Id=TPB.Id
      where TPB.FPId=TPM.FPId and TPB.SPFId=TPM.SPFId),18))
      as tvop ,
      tax17,
      cdcn,
      cdcd,
      LTRIM(STR((select SUM(cast(nw as decimal(24,8))) from __TmpTaxPayers_Body__ TPB where TPB.FPId=TPM.FPId and TPB.SPFId=TPM.SPFId),25,8)) tonw,
      LTRIM(STR((select SUM(cast(ssrv as decimal(18,0))) from __TmpTaxPayers_Body__ TPB where TPB.FPId=TPM.FPId and TPB.SPFId=TPM.SPFId),18)) as torv,
      LTRIM(STR((select SUM(cast(sscv as decimal(18,4))) from __TmpTaxPayers_Body__ TPB where TPB.FPId=TPM.FPId and TPB.SPFId=TPM.SPFId),19,4)) as tocv,
      FPId,
      SPFId
      FROM __TmpTaxPayers_Main__ TPM
    </ActionQuery>
    <ActionQuery TableName ="__TmpTaxPayers_Body__">
      select
      RIGHT('0000000000000'+sstid,13) as sstid,
      sstt,
      LTRIM(STR(am,22,8)) as am,
      CASE mu WHEN '0' THEN '' ELSE mu END as mu,
      LTRIM(STR(nw,25,8)) as nw,
      LTRIM(STR(fee,26,8)) as fee,
      cfee,
      cut,
      LTRIM(STR(exr,18)) as exr,
      LTRIM(STR(ssrv,18)) as ssrv,
      LTRIM(STR(sscv,20,4)) as sscv,
      LTRIM(STR(prdis,18)) as prdis,
      LTRIM(STR(dis,18)) as dis,
      LTRIM(STR(adis,18)) as adis,
      vra ,
      LTRIM(STR(vam,18)) as vam ,
      odt ,
      LTRIM(STR(odr,6,2)) as odr ,
      LTRIM(STR(odam,18)) as odam ,
      olt ,
      '' as olr ,
      '' as olam ,
      consfee,
      bros,
      tcpbs ,
      '' as bsrn,
      LTRIM(STR(tsstam,18)) as tsstam,
      LTRIM(STR(
      (
      vam *
      (
      CASE
      WHEN ( select setm from __TmpTaxPayers_Main__ TPM where TPB.FPId=TPM.FPId and TPB.SPFId=TPM.SPFId ) in (1,3)
      THEN (select SUM(cast(tsstam as decimal(18,0)))-SUM(cast(vam as decimal(18,0))) from __TmpTaxPayers_Body__ TPB2 where TPB.FPId=TPB2.FPId and TPB.SPFId=TPB2.SPFId)
      else 0
      END
      )
      ) /
      case ( select SUM(cast( adis as decimal(18,0) ) ) from  __TmpTaxPayers_Body__ where TPB.FPId=FPId and TPB.SPFId=SPFId)
      when 0 then 1
      else ( select SUM(cast( adis as decimal(18,0) ) ) from  __TmpTaxPayers_Body__ where TPB.FPId=FPId and TPB.SPFId=SPFId)
      end
      ,18))
      as vop,

      MerchandiseId,
      SPFId,
      Id,
      FPId
      from __TmpTaxPayers_Body__ TPB
    </ActionQuery>

  </SelectDataQuery>
  <OutPutColumnsOrder>
    <ColumnsOrder TableName ="__TmpTaxPayers_Main__">
      FactorNo,
      Taxid,
      indatim,
      indati2m,
      Inty,
      inno,
      irtaxid,
      inp,
      ins,
      tins,
      tob,
      bid,
      tinb,
      sbc,
      bpc,
      bbc,
      bpn,
      scln,
      scc,
      crn,
      tprdis,
      tdis,
      tadis,
      tvam,
      todam,
      tbill,
      setm,
      Cap,
      insp,
      tvop ,
      tax17,
      cdcn,
      cdcd,
      tonw,
      torv,
      tocv,
      FPId,
      SPFId,
    </ColumnsOrder>
    <ColumnsOrder TableName ="__TmpTaxPayers_Body__">
      sstid,
      sstt,
      am,
      mu,
      nw,
      fee,
      cfee,
      cut,
      exr,
      ssrv,
      sscv,
      prdis,
      dis,
      adis,
      vra ,
      vam ,
      odt ,
      odr ,
      odam ,
      olt ,
      olr ,
      olam ,
      consfee,
      bros,
      tcpbs,
      bsrn,
      tsstam,
      vop,
      MerchandiseId,
      SPFId,
      Id,
      FPId
    </ColumnsOrder>

  </OutPutColumnsOrder>
  <OutPutColumnsType>
    <ColumnsOrder TableName ="__TmpTaxPayers_Main__">
      int,
      nvarchar,
      float,
      float,
      smallint,
      nvarchar,
      nvarchar,
      smallint,
      smallint,
      nvarchar,
      smallint,
      nvarchar,
      nvarchar,
      nvarchar,
      nvarchar,
      nvarchar,
      nvarchar,
      nvarchar,
      nvarchar,
      nvarchar,
      float,
      float,
      float,
      float,
      float,
      float,
      smallint,
      float,
      float,
      float,
      float,
      nvarchar,
      int,
      float,
      float,
      float,
      int,
      int
    </ColumnsOrder>
    <ColumnsOrder TableName ="__TmpTaxPayers_Body__">
      nvarchar,
      nvarchar,
      float,
      nvarchar,
      float,
      float,
      float,
      nvarchar,
      float,
      float,
      float,
      float,
      float,
      float,
      float,
      float,
      nvarchar,
      float,
      float,
      nvarchar,
      float,
      float,
      float,
      float,
      float,
      nvarchar,
      float,
      float,
      int,
      int,
      int,
      int
    </ColumnsOrder>

  </OutPutColumnsType>
</root>
<!--Version: 1.2.2-->
<!-- when changing the order of columns for header , two fields: SPFId and FPId , and three fields: SPFId and Id and FPId in the body should be placed at the end -->
<!--V 1.2.1 : export fields created-->
<!--V 1.2.2 : config ssrv, sscv, torv and tocv -->
<!--V 1.2.3 : config cfee -->
<!--V 1.2.4 : get exr , sscv and nw from __SPArticleTaxExInfo__ -->
<!--V 1.2.5 : limit fee decimal digits to 7 number -->
<!--V 1.2.6 : use CAST instead of FLOOR in vam calculation -->
<!--V 1.2.7 : use floor in tsstam when inp = 2 -->
<!--V 1.3.0 : Use __SPFactorOp__ for corrective factor and change indatim - irtaxid - ins  -->
<!--V 1.4.0 :  -->
<!--V 1.4.1 : Use function to calculate Amount-->
<!--V 1.5.0 : Chage insert __TmpTaxPayers_Body__ for factor type = 3 -->
<!--V 1.6.0 : use dbo.sppcfloor and change fee decimal count and use cast for vra in vam -->
<!--v 1.7.0 : Edit indatim1 and indatim2 and irtaxid-->
<!--v 1.8.0 : irtaxid for factor type 3 -->
<!--v 1.8.1 : debug decimal part of numbers in adis - tsstam -->
<!--v 1.8.2 : debug exr and cut when inp=1 -->