USE [p543_v2.2]
GO

/****** Object:  StoredProcedure [dbo].[MakeAkt10]    Script Date: 19.04.2019 13:20:00 ******/
DROP PROCEDURE [dbo].[MakeAkt10]
GO

/****** Object:  StoredProcedure [dbo].[MAKE_REP_XML]    Script Date: 19.04.2019 13:20:01 ******/
DROP PROCEDURE [dbo].[MAKE_REP_XML]
GO

/****** Object:  StoredProcedure [dbo].[FIX_REPORT_XML]    Script Date: 19.04.2019 13:20:01 ******/
DROP PROCEDURE [dbo].[FIX_REPORT_XML]
GO

/****** Object:  StoredProcedure [dbo].[FIX_REPORT]    Script Date: 19.04.2019 13:20:01 ******/
DROP PROCEDURE [dbo].[FIX_REPORT]
GO

/****** Object:  StoredProcedure [dbo].[FIX_REPORT]    Script Date: 19.04.2019 13:20:01 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



CREATE PROCEDURE [dbo].[FIX_REPORT]
WITH EXEC AS CALLER
AS
DECLARE @NO_DOG varchar(255)
DECLARE @igk varchar(255)
DECLARE @ddoc date
DECLARE @dend date
declare @dotch  dateTime
declare @dotch_calc  dateTime

/*Объявляем курсор*/
DECLARE @CURSOR CURSOR
/*Заполняем курсор*/
SET @CURSOR  = CURSOR SCROLL
FOR
SELECT
  DIM_IGK.DATE_DOG
 ,DIM_IGK.DATE_END
 ,DIM_IGK.IGK
 ,DIM_IGK.NO_DOG
FROM
  DIM_IGK
WHERE
  IGK <> '-'
/*Открываем курсор*/
OPEN @CURSOR
/*Выбираем первую строку*/
FETCH NEXT FROM @CURSOR INTO @ddoc, @dend, @igk, @NO_DOG

set @dotch = (dateadd(mm, Datediff(mm,0,dateadd(mm,1,getdate())),0))
set @dotch = dateadd(mm,-1, @dotch)
set @dotch_calc = dateadd(dd,-1, @dotch)

DELETE FROM dbo.tQData WHERE dOtch = @dotch_calc

/*Выполняем в цикле перебор строк*/
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO dbo.tQData
(NRow, dOtch, IGK,  doVal_1, doVal_2, doVal_3, doVal_4, doVal_5, doVal_6, doVal_7, doVal_8, doVal_9) 
SELECT
  rep_shbl.no_str
 ,@dotch_calc
 ,@NO_DOG
 ,round(isNull(B.Fcol1,0),2)
 ,round(isNull(B.Fcol2,0),2)
 ,round(isNull(B.Fcol3,0),2)
 ,round(isNull(B.Fcol4,0),2)
 ,round(isNull(B.Fcol5,0),2)
 ,round(isNull(B.Fcol6,0),2)
 ,round(isNull(B.Fcol7,0),2)
 ,round(isNull(B.Fcol8,0),2)
 ,round(isNull(B.Fcol9,0),2)
FROM
    rep_shbl
  LEFT OUTER JOIN
    (SELECT
       row543
      ,SUM(fCol1) AS Fcol1
      ,SUM(fCol2) AS Fcol2
      ,SUM(fCol3) AS Fcol3
      ,SUM(fCol4) AS Fcol4
      ,SUM(fCol5) AS Fcol5
      ,SUM(fCol6) AS Fcol6
      ,SUM(fCol7) AS Fcol7
      ,SUM(fCol8) AS Fcol8
      ,SUM(fCol9) AS Fcol9
      ,MAX(DATOB) AS datob
      ,NO_DOG
     FROM
       (SELECT
          НомерДокумента AS Документ
         ,DATOB
         ,F$SCHETO AS CDEBS
         ,CDEBSS
         ,F$SCHETK AS Ccreds
         ,CcredsS
         ,NO_DOG
         ,CSHPZ
         ,CSTZATR
         ,sum0 AS fCol1
         ,0 AS fCol2
         ,CASE
            WHEN line IN (28, 36) THEN sum3
            WHEN line = 2 THEN ISNULL(
                                 SUMK
                                ,0) -
                               ISNULL(
                                 SUMD1
                                ,0) -
                               ISNULL(
                                 SUMD3
                                ,0)
            WHEN line IN (3, 4) THEN ISNULL(
                                       SUMK4
                                      ,0) -
                                     ISNULL(
                                       SUMD1
                                      ,0) -
                                     ISNULL(
                                       SUMD3
                                      ,0)
            WHEN line = 5 THEN ISNULL(
                                 SUMK4
                                ,0) -
                               ISNULL(
                                 SUMD1
                                ,0) -
                               ISNULL(
                                 SUMD2
                                ,0) -
                               ISNULL(
                                 SUMD3
                                ,0)
            WHEN line IN (8, 9, 10) THEN ISNULL(
                                           SUMD1
                                          ,0) +
                                         ISNULL(
                                           SUMD2
                                          ,0) +
                                         ISNULL(
                                           SUMD3
                                          ,0) -
                                         ISNULL(
                                           SUMK4
                                          ,0) -
                                         ISNULL(
                                           SUMK5
                                          ,0) -
                                         ISNULL(
                                           SUMK6
                                          ,0)
            WHEN line IN (12, 13, 14, 15, 16, 17) THEN ISNULL(
                                                         SUMD1
                                                        ,0) +
                                                       ISNULL(
                                                         SUMD2
                                                        ,0) +
                                                       ISNULL(
                                                         SUMD3
                                                        ,0) -
                                                       ISNULL(
                                                         SUMK4
                                                        ,0) -
                                                       ISNULL(
                                                         SUMK5
                                                        ,0) -
                                                       ISNULL(
                                                         SUMK6
                                                        ,0)
            WHEN line IN (19, 20, 21, 22, 23, 24, 25, 26) THEN ISNULL(
                                                                 SUMD1
                                                                ,0) +
                                                               ISNULL(
                                                                 SUMD2
                                                                ,0) +
                                                               ISNULL(
                                                                 SUMD3
                                                                ,0) -
                                                               ISNULL(
                                                                 SUMK4
                                                                ,0) -
                                                               ISNULL(
                                                                 SUMK5
                                                                ,0) -
                                                               ISNULL(
                                                                 SUMK6
                                                                ,0)
            WHEN line = 27 THEN ISNULL(
                                  SUMD1
                                 ,0) +
                                ISNULL(
                                  SUMD2
                                 ,0) +
                                ISNULL(
                                  SUMD3
                                 ,0) -
                                ISNULL(
                                  SUMK4
                                 ,0) -
                                ISNULL(
                                  SUMK5
                                 ,0) -
                                ISNULL(
                                  SUMK6
                                 ,0)
            WHEN line IN (29, 30, 31, 32, 33) THEN ISNULL(
                                                     SUMD1
                                                    ,0) +
                                                   ISNULL(
                                                     SUMD2
                                                    ,0) +
                                                   ISNULL(
                                                     SUMD3
                                                    ,0)
            ELSE 0
          END
            AS fCol3
         ,CASE
            WHEN CDEBSS = '0_62.01' AND
                 CCREDSS = '0_90.01' THEN NULL
            ELSE SUMD1
          END
            AS fCol4
         ,CASE
            WHEN CDEBSS = '0_62.01' AND
                 CCREDSS = '0_90.01' THEN NULL
            ELSE SUMD2
          END
            AS fCol5
         ,CASE
            WHEN line = 36 THEN NULL
            WHEN CDEBSS = '0_62.01' AND
                 CCREDSS = '0_90.01' THEN NULL
            ELSE SUMD3
          END
            AS fCol6
         ,CASE WHEN line = 2 THEN SUMK ELSE SUMK4 END AS fCol7
         ,CASE
            WHEN CDEBSS = '0_62.01' AND
                 CCREDSS = '0_90.01' THEN NULL
            WHEN CDEBSS = '0_51.02' AND
                 CCREDSS = '0_51.01' AND
                 ШПЗ_КОР = '-' THEN NULL
            ELSE SUMK5
          END
            AS fCol8
         ,CASE WHEN line = 2 THEN 0 ELSE SUMK6 END AS fCol9
         ,SUMD
         ,SUMK
         ,line AS row543
        FROM
          FACT_OBOROT1C
        WHERE
          (CASE
             WHEN (F$SCHETO LIKE '51%' AND
                   F$SCHETK LIKE '51' AND
                   NO_DOG = '-') THEN NO_DOG_KORR
             ELSE NO_DOG
           END = @no_dog) AND
          (DATOB <= @dotch)
        UNION ALL
        SELECT
          НомерДокумента AS Документ
         ,DATOB
         ,F$SCHETO AS CDEBS
         ,CDEBSS
         ,F$SCHETK AS Ccreds
         ,CcredsS
         ,NO_DOG
         ,CSHPZ
         ,CSTZATR
         ,sum0 AS fCol1
         ,0 AS fCol2
         ,CASE
            WHEN line IN (2, 3, 4) THEN ISNULL(
                                          SUMK
                                         ,0) -
                                        ISNULL(
                                          SUMD1
                                         ,0) -
                                        ISNULL(
                                          SUMD3
                                         ,0)
            WHEN line = 5 THEN ISNULL(
                                 SUMK4
                                ,0) -
                               ISNULL(
                                 SUMD1
                                ,0) -
                               ISNULL(
                                 SUMD2
                                ,0) -
                               ISNULL(
                                 SUMD3
                                ,0)
            ELSE 0
          END
            AS fCol3
         ,NULL AS fCol4
         ,NULL AS fCol5
         ,NULL AS fCol6
         ,NULL AS fCol7
         ,NULL AS fCol8
         ,NULL AS fCol9
         ,SUMD
         ,SUMK
         ,1 AS row543
        FROM
          FACT_OBOROT1C AS FACT_OBOROT1C_1
        WHERE
          (CASE
             WHEN (F$SCHETO LIKE '51%' AND
                   F$SCHETK LIKE '51' AND
                   NO_DOG = '-') THEN NO_DOG_KORR
             ELSE NO_DOG
           END = @no_dog) AND
          (DATOB <= @dotch) AND
          (line IN (2, 3, 4, 5))
        UNION ALL
        SELECT
          НомерДокумента AS Документ
         ,DATOB
         ,F$SCHETO AS CDEBS
         ,CDEBSS
         ,F$SCHETK AS Ccreds
         ,CcredsS
         ,NO_DOG
         ,CASE
            WHEN (CDEBSS = '0_51.01' AND
                  CcredsS = '0_51.02') THEN ШПЗ_КОР
            ELSE ШПЗ
          END
            AS ШПЗ
         ,CSTZATR
         ,NULL AS fCol1
         ,0 AS fCol2
         ,CASE
            WHEN line IN
                   (8
                   ,9
                   ,10
                   ,12
                   ,13
                   ,14
                   ,15
                   ,16
                   ,17
                   ,19
                   ,20
                   ,21
                   ,22
                   ,23
                   ,24
                   ,25
                   ,26
                   ,27
                   ,29
                   ,30
                   ,31
                   ,32
                   ,33)
            THEN
              ISNULL(
                SUMD1
               ,0) +
              ISNULL(
                SUMD2
               ,0) +
              ISNULL(
                SUMD3
               ,0) -
              ISNULL(
                SUMK4
               ,0) -
              ISNULL(
                SUMK5
               ,0) -
              ISNULL(
                SUMK6
               ,0)
            WHEN line = 36
            THEN
              isnull(
                sum3
               ,0)
            ELSE
              0
          END
            AS fCol3
         ,NULL AS fCol4
         ,NULL AS fCol5
         ,NULL AS fCol6
         ,NULL AS fCol7
         ,NULL AS fCol8
         ,NULL AS fCol9
         ,SUMD
         ,SUMK
         ,6 AS row543
        FROM
          FACT_OBOROT1C AS FACT_OBOROT2_7
        WHERE
          (CASE
             WHEN (F$SCHETO LIKE '51%' AND
                   F$SCHETK LIKE '51' AND
                   NO_DOG = '-') THEN NO_DOG_KORR
             ELSE NO_DOG
           END = @no_dog) AND
          (DATOB <= @dotch) AND
          (line IN
             (8
             ,9
             ,10
             ,12
             ,13
             ,14
             ,15
             ,16
             ,17
             ,19
             ,20
             ,21
             ,22
             ,23
             ,24
             ,25
             ,26
             ,27
             ,29
             ,30
             ,31
             ,32
             ,33
             ,36))
        UNION ALL
        SELECT
          НомерДокумента AS Документ
         ,DATOB
         ,F$SCHETO AS CDEBS
         ,CDEBSS
         ,F$SCHETK AS Ccreds
         ,CcredsS
         ,NO_DOG
         ,CASE
            WHEN (CDEBSS = '0_51.01' AND
                  CcredsS = '0_51.02') THEN ШПЗ_КОР
            ELSE ШПЗ
          END
            AS ШПЗ
         ,CSTZATR
         ,NULL AS fCol1
         ,0 AS fCol2
         ,CASE
            WHEN CDEBSS = '0_51.01' AND
                 CCREDSS = '0_51.02' THEN SUMD
            WHEN CDEBSS = '0_62.01' AND
                 CCREDSS = '0_90.01' THEN SUMD
            WHEN line IN (2, 3, 4) THEN ISNULL(
                                          SUMK4
                                         ,0) -
                                        ISNULL(
                                          SUMD1
                                         ,0) -
                                        ISNULL(
                                          SUMD3
                                         ,0)
            WHEN line = 5 THEN ISNULL(
                                 SUMK4
                                ,0) -
                               ISNULL(
                                 SUMD1
                                ,0) -
                               ISNULL(
                                 SUMD2
                                ,0) -
                               ISNULL(
                                 SUMD3
                                ,0)
            WHEN line IN (8, 9, 10) THEN ISNULL(
                                           SUMD1
                                          ,0) +
                                         ISNULL(
                                           SUMD2
                                          ,0) +
                                         ISNULL(
                                           SUMD3
                                          ,0) -
                                         ISNULL(
                                           SUMK4
                                          ,0) -
                                         ISNULL(
                                           SUMK5
                                          ,0) -
                                         ISNULL(
                                           SUMK6
                                          ,0)
            WHEN line IN (12, 13, 14, 15, 16, 17) THEN ISNULL(
                                                         SUMD1
                                                        ,0) +
                                                       ISNULL(
                                                         SUMD2
                                                        ,0) +
                                                       ISNULL(
                                                         SUMD3
                                                        ,0) -
                                                       ISNULL(
                                                         SUMK4
                                                        ,0) -
                                                       ISNULL(
                                                         SUMK5
                                                        ,0) -
                                                       ISNULL(
                                                         SUMK6
                                                        ,0)
            WHEN line IN (19, 20, 21, 22, 23, 24, 25, 26) THEN ISNULL(
                                                                 SUMD1
                                                                ,0) +
                                                               ISNULL(
                                                                 SUMD2
                                                                ,0) +
                                                               ISNULL(
                                                                 SUMD3
                                                                ,0) -
                                                               ISNULL(
                                                                 SUMK4
                                                                ,0) -
                                                               ISNULL(
                                                                 SUMK5
                                                                ,0) -
                                                               ISNULL(
                                                                 SUMK6
                                                                ,0)
            WHEN line = 27 THEN ISNULL(
                                  SUMD1
                                 ,0) +
                                ISNULL(
                                  SUMD2
                                 ,0) +
                                ISNULL(
                                  SUMD3
                                 ,0) -
                                ISNULL(
                                  SUMK4
                                 ,0) -
                                ISNULL(
                                  SUMK5
                                 ,0) -
                                ISNULL(
                                  SUMK6
                                 ,0)
            WHEN line IN (29, 30, 31, 32, 33) THEN ISNULL(
                                                     SUMD1
                                                    ,0) +
                                                   ISNULL(
                                                     SUMD2
                                                    ,0) +
                                                   ISNULL(
                                                     SUMD3
                                                    ,0)
            ELSE NULL
          END
            AS fCol3
         ,CASE
            WHEN CDEBSS = '0_62.01' AND
                 CCREDSS = '0_90.01' THEN NULL
            ELSE SUMD1
          END
            AS fCol4
         ,CASE
            WHEN CDEBSS = '0_62.01' AND
                 CCREDSS = '0_90.01' THEN NULL
            ELSE SUMD2
          END
            AS fCol5
         ,CASE
            WHEN line = 36 THEN NULL
            WHEN CDEBSS = '0_62.01' AND
                 CCREDSS = '0_90.01' THEN NULL
            ELSE SUMD3
          END
            AS fCol6
         ,CASE
            WHEN CDEBSS = '0_19.03' AND
                 CCREDSS = '0_68.02' THEN NULL
            WHEN CDEBSS = '0_62.01' AND
                 CCREDSS = '0_90.01' THEN NULL
            WHEN CDEBSS = '0_51.02' AND
                 CCREDSS = '0_51.01' AND
                 ШПЗ_КОР = '-' THEN NULL
            ELSE SUMK4
          END
            AS fCol7
         ,CASE
            WHEN CDEBSS = '0_62.01' AND
                 CCREDSS = '0_90.01' THEN NULL
            WHEN CDEBSS = '0_51.02' AND
                 CCREDSS = '0_51.01' AND
                 ШПЗ_КОР = '-' THEN NULL
            ELSE SUMK5
          END
            AS fCol8
         ,CASE
            WHEN CDEBSS = '0_19.03' AND
                 CCREDSS = '0_68.02' THEN SUMK4
            WHEN CDEBSS = '0_62.01' AND
                 CCREDSS = '0_90.01' THEN NULL
            WHEN CDEBSS = '0_51.02' AND
                 CCREDSS = '0_51.01' AND
                 ШПЗ_КОР = '-' THEN NULL
            ELSE SUMK6
          END
            AS fCol9
         ,SUMD
         ,SUMK
         ,7 AS row543
        FROM
          FACT_OBOROT1C AS FACT_OBOROT2_6
        WHERE
          (CASE
             WHEN (F$SCHETO LIKE '51%' AND
                   F$SCHETK LIKE '51' AND
                   NO_DOG = '-') THEN NO_DOG_KORR
             ELSE NO_DOG
           END = @no_dog) AND
          (DATOB <= @dotch) AND
          (line IN (8, 9, 10))
        UNION ALL
        SELECT
          НомерДокумента AS Документ
         ,DATOB
         ,F$SCHETO AS CDEBS
         ,CDEBSS
         ,F$SCHETK AS Ccreds
         ,CcredsS
         ,NO_DOG
         ,CASE
            WHEN (CDEBSS = '0_51.01' AND
                  CcredsS = '0_51.02') THEN ШПЗ_КОР
            ELSE ШПЗ
          END
            AS ШПЗ
         ,CSTZATR
         ,CASE WHEN CDEBSS LIKE '0_888%' THEN SUMD END AS fCol1
         ,0 AS fCol2
         ,CASE
            WHEN CDEBSS = '0_51.01' AND
                 CCREDSS = '0_51.02' THEN SUMD
            WHEN CDEBSS = '0_62.01' AND
                 CCREDSS = '0_90.01' THEN SUMD
            WHEN LINE IN (2, 3, 4) THEN ISNULL(
                                          SUMK4
                                         ,0) -
                                        ISNULL(
                                          SUMD1
                                         ,0) -
                                        ISNULL(
                                          SUMD3
                                         ,0)
            WHEN line = 5 THEN ISNULL(
                                 SUMK4
                                ,0) -
                               ISNULL(
                                 SUMD1
                                ,0) -
                               ISNULL(
                                 SUMD2
                                ,0) -
                               ISNULL(
                                 SUMD3
                                ,0)
            WHEN line IN (8, 9, 10) THEN ISNULL(
                                           SUMD1
                                          ,0) +
                                         ISNULL(
                                           SUMD2
                                          ,0) +
                                         ISNULL(
                                           SUMD3
                                          ,0) -
                                         ISNULL(
                                           SUMK4
                                          ,0) -
                                         ISNULL(
                                           SUMK5
                                          ,0) -
                                         ISNULL(
                                           SUMK6
                                          ,0)
            WHEN line IN (12, 13, 14, 15, 16, 17) THEN ISNULL(
                                                         SUMD1
                                                        ,0) +
                                                       ISNULL(
                                                         SUMD2
                                                        ,0) +
                                                       ISNULL(
                                                         SUMD3
                                                        ,0) -
                                                       ISNULL(
                                                         SUMK4
                                                        ,0) -
                                                       ISNULL(
                                                         SUMK5
                                                        ,0) -
                                                       ISNULL(
                                                         SUMK6
                                                        ,0)
            WHEN line IN (19, 20, 21, 22, 23, 24, 25, 26) THEN ISNULL(
                                                                 SUMD1
                                                                ,0) +
                                                               ISNULL(
                                                                 SUMD2
                                                                ,0) +
                                                               ISNULL(
                                                                 SUMD3
                                                                ,0) -
                                                               ISNULL(
                                                                 SUMK4
                                                                ,0) -
                                                               ISNULL(
                                                                 SUMK5
                                                                ,0) -
                                                               ISNULL(
                                                                 SUMK6
                                                                ,0)
            WHEN line = 27 THEN ISNULL(
                                  SUMD1
                                 ,0) +
                                ISNULL(
                                  SUMD2
                                 ,0) +
                                ISNULL(
                                  SUMD3
                                 ,0) -
                                ISNULL(
                                  SUMK4
                                 ,0) -
                                ISNULL(
                                  SUMK5
                                 ,0) -
                                ISNULL(
                                  SUMK6
                                 ,0)
            WHEN line IN (29, 30, 31, 32, 33) THEN ISNULL(
                                                     SUMD1
                                                    ,0) +
                                                   ISNULL(
                                                     SUMD2
                                                    ,0) +
                                                   ISNULL(
                                                     SUMD3
                                                    ,0)
            ELSE NULL
          END
            AS fCol3
         ,CASE
            WHEN CDEBSS = '0_62.01' AND
                 CCREDSS = '0_90.01' THEN NULL
            ELSE SUMD1
          END
            AS fCol4
         ,CASE
            WHEN CDEBSS = '0_19.03' AND
                 CCREDSS = '0_68.02' THEN NULL
            WHEN CDEBSS = '0_62.01' AND
                 CCREDSS = '0_90.01' THEN NULL
            ELSE SUMD2
          END
            AS fCol5
         ,CASE
            WHEN line = 36 THEN NULL
            WHEN CDEBSS = '0_62.01' AND
                 CCREDSS = '0_90.01' THEN NULL
            ELSE SUMD3
          END
            AS fCol6
         ,CASE
            WHEN CDEBSS = '0_19.03' AND
                 CCREDSS = '0_68.02' THEN NULL
            WHEN CDEBSS = '0_62.01' AND
                 CCREDSS = '0_90.01' THEN NULL
            WHEN CDEBSS = '0_51.02' AND
                 CCREDSS = '0_51.01' AND
                 ШПЗ_КОР = '-' THEN NULL
            ELSE SUMK4
          END
            AS fCol7
         ,CASE
            WHEN CDEBSS = '0_62.01' AND
                 CCREDSS = '0_90.01' THEN NULL
            WHEN CDEBSS = '0_51.02' AND
                 CCREDSS = '0_51.01' AND
                 ШПЗ_КОР = '-' THEN NULL
            ELSE SUMK5
          END
            AS fCol8
         ,CASE
            WHEN CDEBSS = '0_19.03' AND
                 CCREDSS = '0_68.02' THEN SUMK4
            WHEN CDEBSS = '0_62.01' AND
                 CCREDSS = '0_90.01' THEN NULL
            WHEN CDEBSS = '0_51.02' AND
                 CCREDSS = '0_51.01' AND
                 ШПЗ_КОР = '-' THEN NULL
            ELSE SUMK6
          END
            AS fCol9
         ,SUMD
         ,SUMK
         ,11 AS row543
        FROM
          FACT_OBOROT1C AS FACT_OBOROT2_5
        WHERE
          (CASE
             WHEN (F$SCHETO LIKE '51%' AND
                   F$SCHETK LIKE '51' AND
                   NO_DOG = '-') THEN NO_DOG_KORR
             ELSE NO_DOG
           END = @no_dog) AND
          (DATOB <= @dotch) AND
          (line IN (12, 13, 14, 15, 16, 17))
        UNION ALL
        SELECT
          НомерДокумента AS Документ
         ,DATOB
         ,F$SCHETO AS CDEBS
         ,CDEBSS
         ,F$SCHETK AS Ccreds
         ,CcredsS
         ,NO_DOG
         ,CASE
            WHEN (CDEBSS = '0_51.01' AND
                  CcredsS = '0_51.02') THEN ШПЗ_КОР
            ELSE ШПЗ
          END
            AS ШПЗ
         ,CSTZATR
         ,CASE WHEN CDEBSS LIKE '0_888%' THEN SUMD END AS fCol1
         ,0 AS fCol2
         ,CASE
            WHEN CDEBSS = '0_51.01' AND
                 CCREDSS = '0_51.02' THEN SUMD
            WHEN CDEBSS = '0_62.01' AND
                 CCREDSS = '0_90.01' THEN SUMD
            WHEN line IN (2, 3, 4) THEN ISNULL(
                                          SUMK4
                                         ,0) -
                                        ISNULL(
                                          SUMD1
                                         ,0) -
                                        ISNULL(
                                          SUMD3
                                         ,0)
            WHEN line = 5 THEN ISNULL(
                                 SUMK4
                                ,0) -
                               ISNULL(
                                 SUMD1
                                ,0) -
                               ISNULL(
                                 SUMD2
                                ,0) -
                               ISNULL(
                                 SUMD3
                                ,0)
            WHEN line IN (8, 9, 10) THEN ISNULL(
                                           SUMD1
                                          ,0) +
                                         ISNULL(
                                           SUMD2
                                          ,0) +
                                         ISNULL(
                                           SUMD3
                                          ,0) -
                                         ISNULL(
                                           SUMK4
                                          ,0) -
                                         ISNULL(
                                           SUMK5
                                          ,0) -
                                         ISNULL(
                                           SUMK6
                                          ,0)
            WHEN line IN (12, 13, 14, 15, 16, 17) THEN ISNULL(
                                                         SUMD1
                                                        ,0) +
                                                       ISNULL(
                                                         SUMD2
                                                        ,0) +
                                                       ISNULL(
                                                         SUMD3
                                                        ,0) -
                                                       ISNULL(
                                                         SUMK4
                                                        ,0) -
                                                       ISNULL(
                                                         SUMK5
                                                        ,0) -
                                                       ISNULL(
                                                         SUMK6
                                                        ,0)
            WHEN line IN (19, 20, 21, 22, 23, 24, 25, 26) THEN ISNULL(
                                                                 SUMD1
                                                                ,0) +
                                                               ISNULL(
                                                                 SUMD2
                                                                ,0) +
                                                               ISNULL(
                                                                 SUMD3
                                                                ,0) -
                                                               ISNULL(
                                                                 SUMK4
                                                                ,0) -
                                                               ISNULL(
                                                                 SUMK5
                                                                ,0) -
                                                               ISNULL(
                                                                 SUMK6
                                                                ,0)
            WHEN line = 27 THEN ISNULL(
                                  SUMD1
                                 ,0) +
                                ISNULL(
                                  SUMD2
                                 ,0) +
                                ISNULL(
                                  SUMD3
                                 ,0) -
                                ISNULL(
                                  SUMK4
                                 ,0) -
                                ISNULL(
                                  SUMK5
                                 ,0) -
                                ISNULL(
                                  SUMK6
                                 ,0)
            WHEN line IN (29, 30, 31, 32, 33) THEN ISNULL(
                                                     SUMD1
                                                    ,0) +
                                                   ISNULL(
                                                     SUMD2
                                                    ,0) +
                                                   ISNULL(
                                                     SUMD3
                                                    ,0)
            ELSE NULL
          END
            AS fCol3
         ,CASE
            WHEN CDEBSS = '0_62.01' AND
                 CCREDSS = '0_90.01' THEN NULL
            ELSE SUMD1
          END
            AS fCol4
         ,CASE
            WHEN CDEBSS = '0_62.01' AND
                 CCREDSS = '0_90.01' THEN NULL
            ELSE SUMD2
          END
            AS fCol5
         ,CASE
            WHEN line = 36 THEN NULL
            WHEN CDEBSS = '0_62.01' AND
                 CCREDSS = '0_90.01' THEN NULL
            ELSE SUMD3
          END
            AS fCol6
         ,CASE
            WHEN CDEBSS = '0_19.03' AND
                 CCREDSS = '0_68.02' THEN NULL
            WHEN CDEBSS = '0_62.01' AND
                 CCREDSS = '0_90.01' THEN NULL
            WHEN CDEBSS = '0_51.02' AND
                 CCREDSS = '0_51.01' AND
                 ШПЗ_КОР = '-' THEN NULL
            ELSE SUMK4
          END
            AS fCol7
         ,CASE
            WHEN CDEBSS = '0_62.01' AND
                 CCREDSS = '0_90.01' THEN NULL
            WHEN CDEBSS = '0_51.02' AND
                 CCREDSS = '0_51.01' AND
                 ШПЗ_КОР = '-' THEN NULL
            ELSE SUMK5
          END
            AS fCol8
         ,CASE
            WHEN CDEBSS = '0_19.03' AND
                 CCREDSS = '0_68.02' THEN SUMK4
            WHEN CDEBSS = '0_62.01' AND
                 CCREDSS = '0_90.01' THEN NULL
            WHEN CDEBSS = '0_51.02' AND
                 CCREDSS = '0_51.01' AND
                 ШПЗ_КОР = '-' THEN NULL
            ELSE SUMK6
          END
            AS fCol9
         ,SUMD
         ,SUMK
         ,18 AS row543
        FROM
          FACT_OBOROT1C AS FACT_OBOROT2_3
        WHERE
          (CASE
             WHEN (F$SCHETO LIKE '51%' AND
                   F$SCHETK LIKE '51' AND
                   NO_DOG = '-') THEN NO_DOG_KORR
             ELSE NO_DOG
           END = @no_dog) AND
          (DATOB <= @dotch) AND
          (line IN (19, 20, 21, 22, 23, 24, 25, 26))
        UNION ALL
        SELECT
          НомерДокумента AS Документ
         ,DATOB
         ,F$SCHETO AS CDEBS
         ,CDEBSS
         ,F$SCHETK AS Ccreds
         ,CcredsS
         ,NO_DOG
         ,CASE
            WHEN (CDEBSS = '0_51.01' AND
                  CcredsS = '0_51.02') THEN ШПЗ_КОР
            ELSE ШПЗ
          END
            AS ШПЗ
         ,CSTZATR
         ,CASE WHEN CDEBSS LIKE '0_888%' THEN SUMD END AS fCol1
         ,0 AS fCol2
         ,0 AS fCol3
         ,0 AS fCol4
         ,0 AS fCol5
         ,0 AS fCol6
         ,0 AS fCol7
         ,0 AS fCol8
         ,0 AS fCol9
         ,SUMD
         ,SUMK
         ,29 AS row543
        FROM
          FACT_OBOROT1C AS FACT_OBOROT2_2
        WHERE
          (CASE
             WHEN (F$SCHETO LIKE '51%' AND
                   F$SCHETK LIKE '51' AND
                   NO_DOG = '-') THEN NO_DOG_KORR
             ELSE NO_DOG
           END = @no_dog) AND
          (DATOB <= @dotch) AND
          (line IN (19, 20, 21, 22, 23))
        UNION ALL
        SELECT
          НомерДокумента AS Документ
         ,DATOB
         ,F$SCHETO AS CDEBS
         ,CDEBSS
         ,F$SCHETK AS Ccreds
         ,CcredsS
         ,NO_DOG
         ,CASE
            WHEN (CDEBSS = '0_51.01' AND
                  CcredsS = '0_51.02') THEN ШПЗ_КОР
            ELSE ШПЗ
          END
            AS ШПЗ
         ,CSTZATR
         ,0 AS fCol1
         ,0 AS fCol2
         ,CASE
            WHEN line IN (5, 10, 12, 14, 17, 19, 21, 27) THEN ISNULL(
                                                                SUMD2
                                                               ,0)
            ELSE 0
          END +
          CASE
            WHEN line IN
                   (2
                   ,3
                   ,4
                   ,5
                   ,8
                   ,10
                   ,12
                   ,14
                   ,16
                   ,19
                   ,20
                   ,21
                   ,22
                   ,23
                   ,27
                   ,29
                   ,30
                   ,33)
            THEN
              ISNULL(
                SUMD3
               ,0)
            ELSE
              0
          END -
          CASE
            WHEN line IN (8, 12, 14, 17, 19, 21, 27) THEN ISNULL(
                                                            SUMK5
                                                           ,0)
            ELSE 0
          END -
          CASE
            WHEN line IN (8, 10, 12, 13, 14, 15, 17, 19, 21, 27) THEN ISNULL(
                                                                        SUMK6
                                                                       ,0)
            ELSE 0
          END
            AS fCol3
         ,0 AS fCol4
         ,CASE
            WHEN line IN (5, 10, 12, 14, 17, 19, 21, 27) THEN ISNULL(
                                                                SUMD2
                                                               ,0)
            ELSE 0
          END
            AS fCol5
         ,CASE
            WHEN line IN
                   (2
                   ,3
                   ,4
                   ,5
                   ,8
                   ,10
                   ,12
                   ,14
                   ,16
                   ,19
                   ,20
                   ,21
                   ,22
                   ,23
                   ,27
                   ,29
                   ,30
                   ,33)
            THEN
              ISNULL(
                SUMD3
               ,0)
            ELSE
              0
          END
            AS fCol6
         ,0 AS fCol7
         ,CASE
            WHEN line IN (8, 12, 14, 17, 19, 21, 27) THEN ISNULL(
                                                            SUMK5
                                                           ,0)
            ELSE 0
          END
            AS fCol8
         ,CASE
            WHEN line IN (8, 10, 12, 13, 14, 15, 17, 19, 21, 27) THEN ISNULL(
                                                                        SUMK6
                                                                       ,0)
            ELSE 0
          END
            AS fCol9
         ,SUMD
         ,SUMK
         ,35 AS row543
        FROM
          FACT_OBOROT1C AS FACT_OBOROT2_1
        WHERE
          (CASE
             WHEN (F$SCHETO LIKE '51%' AND
                   F$SCHETK LIKE '51' AND
                   NO_DOG = '-') THEN NO_DOG_KORR
             ELSE NO_DOG
           END = @no_dog) AND
          (DATOB <= @dotch) AND
          (line IN
             (2
             ,3
             ,4
             ,5
             ,7
             ,8
             ,10
             ,12
             ,13
             ,14
             ,15
             ,16
             ,17
             ,19
             ,20
             ,21
             ,22
             ,23
             ,27
             ,29
             ,30
             ,33))
        UNION ALL
        SELECT
          НомерДокумента AS Документ
         ,DATOB
         ,F$SCHETO AS CDEBS
         ,CDEBSS
         ,F$SCHETK AS Ccreds
         ,CcredsS
         ,NO_DOG
         ,CASE
            WHEN (CDEBSS = '0_51.01' AND
                  CcredsS = '0_51.02') THEN ШПЗ_КОР
            ELSE ШПЗ
          END
            AS ШПЗ
         ,CSTZATR
         ,0 AS fCol1
         ,0 AS fCol2
         ,CASE
            WHEN line = 28 THEN SUM3
            WHEN line IN (29, 30, 31, 32, 33) THEN -1 * SUMD
            ELSE NULL
          END
            AS fCol3
         ,0 AS fCol4
         ,0 AS fCol5
         ,0 AS fCol6
         ,0 AS fCol7
         ,0 AS fCol8
         ,0 AS fCol9
         ,SUMD
         ,SUMK
         ,34 AS row543
        FROM
          FACT_OBOROT1C AS FACT_OBOROT2_2
        WHERE
          (CASE
             WHEN (F$SCHETO LIKE '51%' AND
                   F$SCHETK LIKE '51' AND
                   NO_DOG = '-') THEN NO_DOG_KORR
             ELSE NO_DOG
           END = @no_dog) AND
          (DATOB <= @dotch) AND
          (line IN (28, 29, 30, 31, 32, 33))) AS A
     GROUP BY
       row543
      ,NO_DOG) AS B
  ON rep_shbl.id = B.row543
WHERE
  (rep_shbl.id <> 999)
ORDER BY
  rep_shbl.no_str
FETCH NEXT FROM @CURSOR INTO @ddoc, @dend, @igk, @NO_DOG
END

set @dotch = (dateadd(mm, Datediff(mm,0,dateadd(mm,1,getdate())),0))
set @dotch = dateadd(mm,-1, @dotch)
set @dotch_calc = dateadd(dd,-1, @dotch)

/*Выполняем в цикле перебор строк*/
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO dbo.tQData
(NRow, dOtch, IGK,  doVal_1, doVal_2, doVal_3, doVal_4, doVal_5, doVal_6, doVal_7, doVal_8, doVal_9) 
SELECT
  rep_shbl.no_str
 ,@dotch_calc
 ,@NO_DOG
 ,round(isNull(B.Fcol1,0),2)
 ,round(isNull(B.Fcol2,0),2)
 ,round(isNull(B.Fcol3,0),2)
 ,round(isNull(B.Fcol4,0),2)
 ,round(isNull(B.Fcol5,0),2)
 ,round(isNull(B.Fcol6,0),2)
 ,round(isNull(B.Fcol7,0),2)
 ,round(isNull(B.Fcol8,0),2)
 ,round(isNull(B.Fcol9,0),2)
FROM
    rep_shbl
  LEFT OUTER JOIN
    (SELECT
       row543
      ,SUM(fCol1) AS Fcol1
      ,SUM(fCol2) AS Fcol2
      ,SUM(fCol3) AS Fcol3
      ,SUM(fCol4) AS Fcol4
      ,SUM(fCol5) AS Fcol5
      ,SUM(fCol6) AS Fcol6
      ,SUM(fCol7) AS Fcol7
      ,SUM(fCol8) AS Fcol8
      ,SUM(fCol9) AS Fcol9
      ,MAX(DATOB) AS datob
      ,NO_DOG
     FROM
       (SELECT
          НомерДокумента AS Документ
         ,DATOB
         ,F$SCHETO AS CDEBS
         ,CDEBSS
         ,F$SCHETK AS Ccreds
         ,CcredsS
         ,NO_DOG
         ,CSHPZ
         ,CSTZATR
         ,sum0 AS fCol1
         ,0 AS fCol2
         ,CASE
            WHEN line IN (28, 36) THEN sum3
            WHEN line = 2 THEN ISNULL(
                                 SUMK
                                ,0) -
                               ISNULL(
                                 SUMD1
                                ,0) -
                               ISNULL(
                                 SUMD3
                                ,0)
            WHEN line IN (3, 4) THEN ISNULL(
                                       SUMK4
                                      ,0) -
                                     ISNULL(
                                       SUMD1
                                      ,0) -
                                     ISNULL(
                                       SUMD3
                                      ,0)
            WHEN line = 5 THEN ISNULL(
                                 SUMK4
                                ,0) -
                               ISNULL(
                                 SUMD1
                                ,0) -
                               ISNULL(
                                 SUMD2
                                ,0) -
                               ISNULL(
                                 SUMD3
                                ,0)
            WHEN line IN (8, 9, 10) THEN ISNULL(
                                           SUMD1
                                          ,0) +
                                         ISNULL(
                                           SUMD2
                                          ,0) +
                                         ISNULL(
                                           SUMD3
                                          ,0) -
                                         ISNULL(
                                           SUMK4
                                          ,0) -
                                         ISNULL(
                                           SUMK5
                                          ,0) -
                                         ISNULL(
                                           SUMK6
                                          ,0)
            WHEN line IN (12, 13, 14, 15, 16, 17) THEN ISNULL(
                                                         SUMD1
                                                        ,0) +
                                                       ISNULL(
                                                         SUMD2
                                                        ,0) +
                                                       ISNULL(
                                                         SUMD3
                                                        ,0) -
                                                       ISNULL(
                                                         SUMK4
                                                        ,0) -
                                                       ISNULL(
                                                         SUMK5
                                                        ,0) -
                                                       ISNULL(
                                                         SUMK6
                                                        ,0)
            WHEN line IN (19, 20, 21, 22, 23, 24, 25, 26) THEN ISNULL(
                                                                 SUMD1
                                                                ,0) +
                                                               ISNULL(
                                                                 SUMD2
                                                                ,0) +
                                                               ISNULL(
                                                                 SUMD3
                                                                ,0) -
                                                               ISNULL(
                                                                 SUMK4
                                                                ,0) -
                                                               ISNULL(
                                                                 SUMK5
                                                                ,0) -
                                                               ISNULL(
                                                                 SUMK6
                                                                ,0)
            WHEN line = 27 THEN ISNULL(
                                  SUMD1
                                 ,0) +
                                ISNULL(
                                  SUMD2
                                 ,0) +
                                ISNULL(
                                  SUMD3
                                 ,0) -
                                ISNULL(
                                  SUMK4
                                 ,0) -
                                ISNULL(
                                  SUMK5
                                 ,0) -
                                ISNULL(
                                  SUMK6
                                 ,0)
            WHEN line IN (29, 30, 31, 32, 33) THEN ISNULL(
                                                     SUMD1
                                                    ,0) +
                                                   ISNULL(
                                                     SUMD2
                                                    ,0) +
                                                   ISNULL(
                                                     SUMD3
                                                    ,0)
            ELSE 0
          END
            AS fCol3
         ,CASE
            WHEN CDEBSS = '0_62.01' AND
                 CCREDSS = '0_90.01' THEN NULL
            ELSE SUMD1
          END
            AS fCol4
         ,CASE
            WHEN CDEBSS = '0_62.01' AND
                 CCREDSS = '0_90.01' THEN NULL
            ELSE SUMD2
          END
            AS fCol5
         ,CASE
            WHEN line = 36 THEN NULL
            WHEN CDEBSS = '0_62.01' AND
                 CCREDSS = '0_90.01' THEN NULL
            ELSE SUMD3
          END
            AS fCol6
         ,CASE WHEN line = 2 THEN SUMK ELSE SUMK4 END AS fCol7
         ,CASE
            WHEN CDEBSS = '0_62.01' AND
                 CCREDSS = '0_90.01' THEN NULL
            WHEN CDEBSS = '0_51.02' AND
                 CCREDSS = '0_51.01' AND
                 ШПЗ_КОР = '-' THEN NULL
            ELSE SUMK5
          END
            AS fCol8
         ,CASE WHEN line = 2 THEN 0 ELSE SUMK6 END AS fCol9
         ,SUMD
         ,SUMK
         ,line AS row543
        FROM
          FACT_OBOROT1C
        WHERE
          (CASE
             WHEN (F$SCHETO LIKE '51%' AND
                   F$SCHETK LIKE '51' AND
                   NO_DOG = '-') THEN NO_DOG_KORR
             ELSE NO_DOG
           END = @no_dog) AND
          (DATOB <= @dotch)
        UNION ALL
        SELECT
          НомерДокумента AS Документ
         ,DATOB
         ,F$SCHETO AS CDEBS
         ,CDEBSS
         ,F$SCHETK AS Ccreds
         ,CcredsS
         ,NO_DOG
         ,CSHPZ
         ,CSTZATR
         ,sum0 AS fCol1
         ,0 AS fCol2
         ,CASE
            WHEN line IN (2, 3, 4) THEN ISNULL(
                                          SUMK
                                         ,0) -
                                        ISNULL(
                                          SUMD1
                                         ,0) -
                                        ISNULL(
                                          SUMD3
                                         ,0)
            WHEN line = 5 THEN ISNULL(
                                 SUMK4
                                ,0) -
                               ISNULL(
                                 SUMD1
                                ,0) -
                               ISNULL(
                                 SUMD2
                                ,0) -
                               ISNULL(
                                 SUMD3
                                ,0)
            ELSE 0
          END
            AS fCol3
         ,NULL AS fCol4
         ,NULL AS fCol5
         ,NULL AS fCol6
         ,NULL AS fCol7
         ,NULL AS fCol8
         ,NULL AS fCol9
         ,SUMD
         ,SUMK
         ,1 AS row543
        FROM
          FACT_OBOROT1C AS FACT_OBOROT1C_1
        WHERE
          (CASE
             WHEN (F$SCHETO LIKE '51%' AND
                   F$SCHETK LIKE '51' AND
                   NO_DOG = '-') THEN NO_DOG_KORR
             ELSE NO_DOG
           END = @no_dog) AND
          (DATOB <= @dotch) AND
          (line IN (2, 3, 4, 5))
        UNION ALL
        SELECT
          НомерДокумента AS Документ
         ,DATOB
         ,F$SCHETO AS CDEBS
         ,CDEBSS
         ,F$SCHETK AS Ccreds
         ,CcredsS
         ,NO_DOG
         ,CASE
            WHEN (CDEBSS = '0_51.01' AND
                  CcredsS = '0_51.02') THEN ШПЗ_КОР
            ELSE ШПЗ
          END
            AS ШПЗ
         ,CSTZATR
         ,NULL AS fCol1
         ,0 AS fCol2
         ,CASE
            WHEN line IN
                   (8
                   ,9
                   ,10
                   ,12
                   ,13
                   ,14
                   ,15
                   ,16
                   ,17
                   ,19
                   ,20
                   ,21
                   ,22
                   ,23
                   ,24
                   ,25
                   ,26
                   ,27
                   ,29
                   ,30
                   ,31
                   ,32
                   ,33)
            THEN
              ISNULL(
                SUMD1
               ,0) +
              ISNULL(
                SUMD2
               ,0) +
              ISNULL(
                SUMD3
               ,0) -
              ISNULL(
                SUMK4
               ,0) -
              ISNULL(
                SUMK5
               ,0) -
              ISNULL(
                SUMK6
               ,0)
            WHEN line = 36
            THEN
              isnull(
                sum3
               ,0)
            ELSE
              0
          END
            AS fCol3
         ,NULL AS fCol4
         ,NULL AS fCol5
         ,NULL AS fCol6
         ,NULL AS fCol7
         ,NULL AS fCol8
         ,NULL AS fCol9
         ,SUMD
         ,SUMK
         ,6 AS row543
        FROM
          FACT_OBOROT1C AS FACT_OBOROT2_7
        WHERE
          (CASE
             WHEN (F$SCHETO LIKE '51%' AND
                   F$SCHETK LIKE '51' AND
                   NO_DOG = '-') THEN NO_DOG_KORR
             ELSE NO_DOG
           END = @no_dog) AND
          (DATOB <= @dotch) AND
          (line IN
             (8
             ,9
             ,10
             ,12
             ,13
             ,14
             ,15
             ,16
             ,17
             ,19
             ,20
             ,21
             ,22
             ,23
             ,24
             ,25
             ,26
             ,27
             ,29
             ,30
             ,31
             ,32
             ,33
             ,36))
        UNION ALL
        SELECT
          НомерДокумента AS Документ
         ,DATOB
         ,F$SCHETO AS CDEBS
         ,CDEBSS
         ,F$SCHETK AS Ccreds
         ,CcredsS
         ,NO_DOG
         ,CASE
            WHEN (CDEBSS = '0_51.01' AND
                  CcredsS = '0_51.02') THEN ШПЗ_КОР
            ELSE ШПЗ
          END
            AS ШПЗ
         ,CSTZATR
         ,NULL AS fCol1
         ,0 AS fCol2
         ,CASE
            WHEN CDEBSS = '0_51.01' AND
                 CCREDSS = '0_51.02' THEN SUMD
            WHEN CDEBSS = '0_62.01' AND
                 CCREDSS = '0_90.01' THEN SUMD
            WHEN line IN (2, 3, 4) THEN ISNULL(
                                          SUMK4
                                         ,0) -
                                        ISNULL(
                                          SUMD1
                                         ,0) -
                                        ISNULL(
                                          SUMD3
                                         ,0)
            WHEN line = 5 THEN ISNULL(
                                 SUMK4
                                ,0) -
                               ISNULL(
                                 SUMD1
                                ,0) -
                               ISNULL(
                                 SUMD2
                                ,0) -
                               ISNULL(
                                 SUMD3
                                ,0)
            WHEN line IN (8, 9, 10) THEN ISNULL(
                                           SUMD1
                                          ,0) +
                                         ISNULL(
                                           SUMD2
                                          ,0) +
                                         ISNULL(
                                           SUMD3
                                          ,0) -
                                         ISNULL(
                                           SUMK4
                                          ,0) -
                                         ISNULL(
                                           SUMK5
                                          ,0) -
                                         ISNULL(
                                           SUMK6
                                          ,0)
            WHEN line IN (12, 13, 14, 15, 16, 17) THEN ISNULL(
                                                         SUMD1
                                                        ,0) +
                                                       ISNULL(
                                                         SUMD2
                                                        ,0) +
                                                       ISNULL(
                                                         SUMD3
                                                        ,0) -
                                                       ISNULL(
                                                         SUMK4
                                                        ,0) -
                                                       ISNULL(
                                                         SUMK5
                                                        ,0) -
                                                       ISNULL(
                                                         SUMK6
                                                        ,0)
            WHEN line IN (19, 20, 21, 22, 23, 24, 25, 26) THEN ISNULL(
                                                                 SUMD1
                                                                ,0) +
                                                               ISNULL(
                                                                 SUMD2
                                                                ,0) +
                                                               ISNULL(
                                                                 SUMD3
                                                                ,0) -
                                                               ISNULL(
                                                                 SUMK4
                                                                ,0) -
                                                               ISNULL(
                                                                 SUMK5
                                                                ,0) -
                                                               ISNULL(
                                                                 SUMK6
                                                                ,0)
            WHEN line = 27 THEN ISNULL(
                                  SUMD1
                                 ,0) +
                                ISNULL(
                                  SUMD2
                                 ,0) +
                                ISNULL(
                                  SUMD3
                                 ,0) -
                                ISNULL(
                                  SUMK4
                                 ,0) -
                                ISNULL(
                                  SUMK5
                                 ,0) -
                                ISNULL(
                                  SUMK6
                                 ,0)
            WHEN line IN (29, 30, 31, 32, 33) THEN ISNULL(
                                                     SUMD1
                                                    ,0) +
                                                   ISNULL(
                                                     SUMD2
                                                    ,0) +
                                                   ISNULL(
                                                     SUMD3
                                                    ,0)
            ELSE NULL
          END
            AS fCol3
         ,CASE
            WHEN CDEBSS = '0_62.01' AND
                 CCREDSS = '0_90.01' THEN NULL
            ELSE SUMD1
          END
            AS fCol4
         ,CASE
            WHEN CDEBSS = '0_62.01' AND
                 CCREDSS = '0_90.01' THEN NULL
            ELSE SUMD2
          END
            AS fCol5
         ,CASE
            WHEN line = 36 THEN NULL
            WHEN CDEBSS = '0_62.01' AND
                 CCREDSS = '0_90.01' THEN NULL
            ELSE SUMD3
          END
            AS fCol6
         ,CASE
            WHEN CDEBSS = '0_19.03' AND
                 CCREDSS = '0_68.02' THEN NULL
            WHEN CDEBSS = '0_62.01' AND
                 CCREDSS = '0_90.01' THEN NULL
            WHEN CDEBSS = '0_51.02' AND
                 CCREDSS = '0_51.01' AND
                 ШПЗ_КОР = '-' THEN NULL
            ELSE SUMK4
          END
            AS fCol7
         ,CASE
            WHEN CDEBSS = '0_62.01' AND
                 CCREDSS = '0_90.01' THEN NULL
            WHEN CDEBSS = '0_51.02' AND
                 CCREDSS = '0_51.01' AND
                 ШПЗ_КОР = '-' THEN NULL
            ELSE SUMK5
          END
            AS fCol8
         ,CASE
            WHEN CDEBSS = '0_19.03' AND
                 CCREDSS = '0_68.02' THEN SUMK4
            WHEN CDEBSS = '0_62.01' AND
                 CCREDSS = '0_90.01' THEN NULL
            WHEN CDEBSS = '0_51.02' AND
                 CCREDSS = '0_51.01' AND
                 ШПЗ_КОР = '-' THEN NULL
            ELSE SUMK6
          END
            AS fCol9
         ,SUMD
         ,SUMK
         ,7 AS row543
        FROM
          FACT_OBOROT1C AS FACT_OBOROT2_6
        WHERE
          (CASE
             WHEN (F$SCHETO LIKE '51%' AND
                   F$SCHETK LIKE '51' AND
                   NO_DOG = '-') THEN NO_DOG_KORR
             ELSE NO_DOG
           END = @no_dog) AND
          (DATOB <= @dotch) AND
          (line IN (8, 9, 10))
        UNION ALL
        SELECT
          НомерДокумента AS Документ
         ,DATOB
         ,F$SCHETO AS CDEBS
         ,CDEBSS
         ,F$SCHETK AS Ccreds
         ,CcredsS
         ,NO_DOG
         ,CASE
            WHEN (CDEBSS = '0_51.01' AND
                  CcredsS = '0_51.02') THEN ШПЗ_КОР
            ELSE ШПЗ
          END
            AS ШПЗ
         ,CSTZATR
         ,CASE WHEN CDEBSS LIKE '0_888%' THEN SUMD END AS fCol1
         ,0 AS fCol2
         ,CASE
            WHEN CDEBSS = '0_51.01' AND
                 CCREDSS = '0_51.02' THEN SUMD
            WHEN CDEBSS = '0_62.01' AND
                 CCREDSS = '0_90.01' THEN SUMD
            WHEN LINE IN (2, 3, 4) THEN ISNULL(
                                          SUMK4
                                         ,0) -
                                        ISNULL(
                                          SUMD1
                                         ,0) -
                                        ISNULL(
                                          SUMD3
                                         ,0)
            WHEN line = 5 THEN ISNULL(
                                 SUMK4
                                ,0) -
                               ISNULL(
                                 SUMD1
                                ,0) -
                               ISNULL(
                                 SUMD2
                                ,0) -
                               ISNULL(
                                 SUMD3
                                ,0)
            WHEN line IN (8, 9, 10) THEN ISNULL(
                                           SUMD1
                                          ,0) +
                                         ISNULL(
                                           SUMD2
                                          ,0) +
                                         ISNULL(
                                           SUMD3
                                          ,0) -
                                         ISNULL(
                                           SUMK4
                                          ,0) -
                                         ISNULL(
                                           SUMK5
                                          ,0) -
                                         ISNULL(
                                           SUMK6
                                          ,0)
            WHEN line IN (12, 13, 14, 15, 16, 17) THEN ISNULL(
                                                         SUMD1
                                                        ,0) +
                                                       ISNULL(
                                                         SUMD2
                                                        ,0) +
                                                       ISNULL(
                                                         SUMD3
                                                        ,0) -
                                                       ISNULL(
                                                         SUMK4
                                                        ,0) -
                                                       ISNULL(
                                                         SUMK5
                                                        ,0) -
                                                       ISNULL(
                                                         SUMK6
                                                        ,0)
            WHEN line IN (19, 20, 21, 22, 23, 24, 25, 26) THEN ISNULL(
                                                                 SUMD1
                                                                ,0) +
                                                               ISNULL(
                                                                 SUMD2
                                                                ,0) +
                                                               ISNULL(
                                                                 SUMD3
                                                                ,0) -
                                                               ISNULL(
                                                                 SUMK4
                                                                ,0) -
                                                               ISNULL(
                                                                 SUMK5
                                                                ,0) -
                                                               ISNULL(
                                                                 SUMK6
                                                                ,0)
            WHEN line = 27 THEN ISNULL(
                                  SUMD1
                                 ,0) +
                                ISNULL(
                                  SUMD2
                                 ,0) +
                                ISNULL(
                                  SUMD3
                                 ,0) -
                                ISNULL(
                                  SUMK4
                                 ,0) -
                                ISNULL(
                                  SUMK5
                                 ,0) -
                                ISNULL(
                                  SUMK6
                                 ,0)
            WHEN line IN (29, 30, 31, 32, 33) THEN ISNULL(
                                                     SUMD1
                                                    ,0) +
                                                   ISNULL(
                                                     SUMD2
                                                    ,0) +
                                                   ISNULL(
                                                     SUMD3
                                                    ,0)
            ELSE NULL
          END
            AS fCol3
         ,CASE
            WHEN CDEBSS = '0_62.01' AND
                 CCREDSS = '0_90.01' THEN NULL
            ELSE SUMD1
          END
            AS fCol4
         ,CASE
            WHEN CDEBSS = '0_19.03' AND
                 CCREDSS = '0_68.02' THEN NULL
            WHEN CDEBSS = '0_62.01' AND
                 CCREDSS = '0_90.01' THEN NULL
            ELSE SUMD2
          END
            AS fCol5
         ,CASE
            WHEN line = 36 THEN NULL
            WHEN CDEBSS = '0_62.01' AND
                 CCREDSS = '0_90.01' THEN NULL
            ELSE SUMD3
          END
            AS fCol6
         ,CASE
            WHEN CDEBSS = '0_19.03' AND
                 CCREDSS = '0_68.02' THEN NULL
            WHEN CDEBSS = '0_62.01' AND
                 CCREDSS = '0_90.01' THEN NULL
            WHEN CDEBSS = '0_51.02' AND
                 CCREDSS = '0_51.01' AND
                 ШПЗ_КОР = '-' THEN NULL
            ELSE SUMK4
          END
            AS fCol7
         ,CASE
            WHEN CDEBSS = '0_62.01' AND
                 CCREDSS = '0_90.01' THEN NULL
            WHEN CDEBSS = '0_51.02' AND
                 CCREDSS = '0_51.01' AND
                 ШПЗ_КОР = '-' THEN NULL
            ELSE SUMK5
          END
            AS fCol8
         ,CASE
            WHEN CDEBSS = '0_19.03' AND
                 CCREDSS = '0_68.02' THEN SUMK4
            WHEN CDEBSS = '0_62.01' AND
                 CCREDSS = '0_90.01' THEN NULL
            WHEN CDEBSS = '0_51.02' AND
                 CCREDSS = '0_51.01' AND
                 ШПЗ_КОР = '-' THEN NULL
            ELSE SUMK6
          END
            AS fCol9
         ,SUMD
         ,SUMK
         ,11 AS row543
        FROM
          FACT_OBOROT1C AS FACT_OBOROT2_5
        WHERE
          (CASE
             WHEN (F$SCHETO LIKE '51%' AND
                   F$SCHETK LIKE '51' AND
                   NO_DOG = '-') THEN NO_DOG_KORR
             ELSE NO_DOG
           END = @no_dog) AND
          (DATOB <= @dotch) AND
          (line IN (12, 13, 14, 15, 16, 17))
        UNION ALL
        SELECT
          НомерДокумента AS Документ
         ,DATOB
         ,F$SCHETO AS CDEBS
         ,CDEBSS
         ,F$SCHETK AS Ccreds
         ,CcredsS
         ,NO_DOG
         ,CASE
            WHEN (CDEBSS = '0_51.01' AND
                  CcredsS = '0_51.02') THEN ШПЗ_КОР
            ELSE ШПЗ
          END
            AS ШПЗ
         ,CSTZATR
         ,CASE WHEN CDEBSS LIKE '0_888%' THEN SUMD END AS fCol1
         ,0 AS fCol2
         ,CASE
            WHEN CDEBSS = '0_51.01' AND
                 CCREDSS = '0_51.02' THEN SUMD
            WHEN CDEBSS = '0_62.01' AND
                 CCREDSS = '0_90.01' THEN SUMD
            WHEN line IN (2, 3, 4) THEN ISNULL(
                                          SUMK4
                                         ,0) -
                                        ISNULL(
                                          SUMD1
                                         ,0) -
                                        ISNULL(
                                          SUMD3
                                         ,0)
            WHEN line = 5 THEN ISNULL(
                                 SUMK4
                                ,0) -
                               ISNULL(
                                 SUMD1
                                ,0) -
                               ISNULL(
                                 SUMD2
                                ,0) -
                               ISNULL(
                                 SUMD3
                                ,0)
            WHEN line IN (8, 9, 10) THEN ISNULL(
                                           SUMD1
                                          ,0) +
                                         ISNULL(
                                           SUMD2
                                          ,0) +
                                         ISNULL(
                                           SUMD3
                                          ,0) -
                                         ISNULL(
                                           SUMK4
                                          ,0) -
                                         ISNULL(
                                           SUMK5
                                          ,0) -
                                         ISNULL(
                                           SUMK6
                                          ,0)
            WHEN line IN (12, 13, 14, 15, 16, 17) THEN ISNULL(
                                                         SUMD1
                                                        ,0) +
                                                       ISNULL(
                                                         SUMD2
                                                        ,0) +
                                                       ISNULL(
                                                         SUMD3
                                                        ,0) -
                                                       ISNULL(
                                                         SUMK4
                                                        ,0) -
                                                       ISNULL(
                                                         SUMK5
                                                        ,0) -
                                                       ISNULL(
                                                         SUMK6
                                                        ,0)
            WHEN line IN (19, 20, 21, 22, 23, 24, 25, 26) THEN ISNULL(
                                                                 SUMD1
                                                                ,0) +
                                                               ISNULL(
                                                                 SUMD2
                                                                ,0) +
                                                               ISNULL(
                                                                 SUMD3
                                                                ,0) -
                                                               ISNULL(
                                                                 SUMK4
                                                                ,0) -
                                                               ISNULL(
                                                                 SUMK5
                                                                ,0) -
                                                               ISNULL(
                                                                 SUMK6
                                                                ,0)
            WHEN line = 27 THEN ISNULL(
                                  SUMD1
                                 ,0) +
                                ISNULL(
                                  SUMD2
                                 ,0) +
                                ISNULL(
                                  SUMD3
                                 ,0) -
                                ISNULL(
                                  SUMK4
                                 ,0) -
                                ISNULL(
                                  SUMK5
                                 ,0) -
                                ISNULL(
                                  SUMK6
                                 ,0)
            WHEN line IN (29, 30, 31, 32, 33) THEN ISNULL(
                                                     SUMD1
                                                    ,0) +
                                                   ISNULL(
                                                     SUMD2
                                                    ,0) +
                                                   ISNULL(
                                                     SUMD3
                                                    ,0)
            ELSE NULL
          END
            AS fCol3
         ,CASE
            WHEN CDEBSS = '0_62.01' AND
                 CCREDSS = '0_90.01' THEN NULL
            ELSE SUMD1
          END
            AS fCol4
         ,CASE
            WHEN CDEBSS = '0_62.01' AND
                 CCREDSS = '0_90.01' THEN NULL
            ELSE SUMD2
          END
            AS fCol5
         ,CASE
            WHEN line = 36 THEN NULL
            WHEN CDEBSS = '0_62.01' AND
                 CCREDSS = '0_90.01' THEN NULL
            ELSE SUMD3
          END
            AS fCol6
         ,CASE
            WHEN CDEBSS = '0_19.03' AND
                 CCREDSS = '0_68.02' THEN NULL
            WHEN CDEBSS = '0_62.01' AND
                 CCREDSS = '0_90.01' THEN NULL
            WHEN CDEBSS = '0_51.02' AND
                 CCREDSS = '0_51.01' AND
                 ШПЗ_КОР = '-' THEN NULL
            ELSE SUMK4
          END
            AS fCol7
         ,CASE
            WHEN CDEBSS = '0_62.01' AND
                 CCREDSS = '0_90.01' THEN NULL
            WHEN CDEBSS = '0_51.02' AND
                 CCREDSS = '0_51.01' AND
                 ШПЗ_КОР = '-' THEN NULL
            ELSE SUMK5
          END
            AS fCol8
         ,CASE
            WHEN CDEBSS = '0_19.03' AND
                 CCREDSS = '0_68.02' THEN SUMK4
            WHEN CDEBSS = '0_62.01' AND
                 CCREDSS = '0_90.01' THEN NULL
            WHEN CDEBSS = '0_51.02' AND
                 CCREDSS = '0_51.01' AND
                 ШПЗ_КОР = '-' THEN NULL
            ELSE SUMK6
          END
            AS fCol9
         ,SUMD
         ,SUMK
         ,18 AS row543
        FROM
          FACT_OBOROT1C AS FACT_OBOROT2_3
        WHERE
          (CASE
             WHEN (F$SCHETO LIKE '51%' AND
                   F$SCHETK LIKE '51' AND
                   NO_DOG = '-') THEN NO_DOG_KORR
             ELSE NO_DOG
           END = @no_dog) AND
          (DATOB <= @dotch) AND
          (line IN (19, 20, 21, 22, 23, 24, 25, 26))
        UNION ALL
        SELECT
          НомерДокумента AS Документ
         ,DATOB
         ,F$SCHETO AS CDEBS
         ,CDEBSS
         ,F$SCHETK AS Ccreds
         ,CcredsS
         ,NO_DOG
         ,CASE
            WHEN (CDEBSS = '0_51.01' AND
                  CcredsS = '0_51.02') THEN ШПЗ_КОР
            ELSE ШПЗ
          END
            AS ШПЗ
         ,CSTZATR
         ,CASE WHEN CDEBSS LIKE '0_888%' THEN SUMD END AS fCol1
         ,0 AS fCol2
         ,0 AS fCol3
         ,0 AS fCol4
         ,0 AS fCol5
         ,0 AS fCol6
         ,0 AS fCol7
         ,0 AS fCol8
         ,0 AS fCol9
         ,SUMD
         ,SUMK
         ,29 AS row543
        FROM
          FACT_OBOROT1C AS FACT_OBOROT2_2
        WHERE
          (CASE
             WHEN (F$SCHETO LIKE '51%' AND
                   F$SCHETK LIKE '51' AND
                   NO_DOG = '-') THEN NO_DOG_KORR
             ELSE NO_DOG
           END = @no_dog) AND
          (DATOB <= @dotch) AND
          (line IN (19, 20, 21, 22, 23))
        UNION ALL
        SELECT
          НомерДокумента AS Документ
         ,DATOB
         ,F$SCHETO AS CDEBS
         ,CDEBSS
         ,F$SCHETK AS Ccreds
         ,CcredsS
         ,NO_DOG
         ,CASE
            WHEN (CDEBSS = '0_51.01' AND
                  CcredsS = '0_51.02') THEN ШПЗ_КОР
            ELSE ШПЗ
          END
            AS ШПЗ
         ,CSTZATR
         ,0 AS fCol1
         ,0 AS fCol2
         ,CASE
            WHEN line IN (5, 10, 12, 14, 17, 19, 21, 27) THEN ISNULL(
                                                                SUMD2
                                                               ,0)
            ELSE 0
          END +
          CASE
            WHEN line IN
                   (2
                   ,3
                   ,4
                   ,5
                   ,8
                   ,10
                   ,12
                   ,14
                   ,16
                   ,19
                   ,20
                   ,21
                   ,22
                   ,23
                   ,27
                   ,29
                   ,30
                   ,33)
            THEN
              ISNULL(
                SUMD3
               ,0)
            ELSE
              0
          END -
          CASE
            WHEN line IN (8, 12, 14, 17, 19, 21, 27) THEN ISNULL(
                                                            SUMK5
                                                           ,0)
            ELSE 0
          END -
          CASE
            WHEN line IN (8, 10, 12, 13, 14, 15, 17, 19, 21, 27) THEN ISNULL(
                                                                        SUMK6
                                                                       ,0)
            ELSE 0
          END
            AS fCol3
         ,0 AS fCol4
         ,CASE
            WHEN line IN (5, 10, 12, 14, 17, 19, 21, 27) THEN ISNULL(
                                                                SUMD2
                                                               ,0)
            ELSE 0
          END
            AS fCol5
         ,CASE
            WHEN line IN
                   (2
                   ,3
                   ,4
                   ,5
                   ,8
                   ,10
                   ,12
                   ,14
                   ,16
                   ,19
                   ,20
                   ,21
                   ,22
                   ,23
                   ,27
                   ,29
                   ,30
                   ,33)
            THEN
              ISNULL(
                SUMD3
               ,0)
            ELSE
              0
          END
            AS fCol6
         ,0 AS fCol7
         ,CASE
            WHEN line IN (8, 12, 14, 17, 19, 21, 27) THEN ISNULL(
                                                            SUMK5
                                                           ,0)
            ELSE 0
          END
            AS fCol8
         ,CASE
            WHEN line IN (8, 10, 12, 13, 14, 15, 17, 19, 21, 27) THEN ISNULL(
                                                                        SUMK6
                                                                       ,0)
            ELSE 0
          END
            AS fCol9
         ,SUMD
         ,SUMK
         ,35 AS row543
        FROM
          FACT_OBOROT1C AS FACT_OBOROT2_1
        WHERE
          (CASE
             WHEN (F$SCHETO LIKE '51%' AND
                   F$SCHETK LIKE '51' AND
                   NO_DOG = '-') THEN NO_DOG_KORR
             ELSE NO_DOG
           END = @no_dog) AND
          (DATOB <= @dotch) AND
          (line IN
             (2
             ,3
             ,4
             ,5
             ,7
             ,8
             ,10
             ,12
             ,13
             ,14
             ,15
             ,16
             ,17
             ,19
             ,20
             ,21
             ,22
             ,23
             ,27
             ,29
             ,30
             ,33))
        UNION ALL
        SELECT
          НомерДокумента AS Документ
         ,DATOB
         ,F$SCHETO AS CDEBS
         ,CDEBSS
         ,F$SCHETK AS Ccreds
         ,CcredsS
         ,NO_DOG
         ,CASE
            WHEN (CDEBSS = '0_51.01' AND
                  CcredsS = '0_51.02') THEN ШПЗ_КОР
            ELSE ШПЗ
          END
            AS ШПЗ
         ,CSTZATR
         ,0 AS fCol1
         ,0 AS fCol2
         ,CASE
            WHEN line = 28 THEN SUM3
            WHEN line IN (29, 30, 31, 32, 33) THEN -1 * SUMD
            ELSE NULL
          END
            AS fCol3
         ,0 AS fCol4
         ,0 AS fCol5
         ,0 AS fCol6
         ,0 AS fCol7
         ,0 AS fCol8
         ,0 AS fCol9
         ,SUMD
         ,SUMK
         ,34 AS row543
        FROM
          FACT_OBOROT1C AS FACT_OBOROT2_2
        WHERE
          (CASE
             WHEN (F$SCHETO LIKE '51%' AND
                   F$SCHETK LIKE '51' AND
                   NO_DOG = '-') THEN NO_DOG_KORR
             ELSE NO_DOG
           END = @no_dog) AND
          (DATOB <= @dotch) AND
          (line IN (28, 29, 30, 31, 32, 33))) AS A
     GROUP BY
       row543
      ,NO_DOG) AS B
  ON rep_shbl.id = B.row543
WHERE
  (rep_shbl.id <> 999)
ORDER BY
  rep_shbl.no_str
  
CLOSE @CURSOR


END;

update  tQData
set doVal_2 = round (CASE WHEN doVal_1 <> 0 then doVal_3 / doVal_1* 100 else 0 end, 2) 
update tQData set doVal_9 = 0 where tQData.NRow = '1.4.'

GO

/****** Object:  StoredProcedure [dbo].[FIX_REPORT_XML]    Script Date: 19.04.2019 13:20:01 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[FIX_REPORT_XML]
WITH EXEC AS CALLER
AS
declare @inn nvarchar(30) = '3831281799' -- ИНН собственной организации
declare @kpp nvarchar(30) = '380228001' -- КПП собственной организации
declare @orgname nvarchar(30) = 'АО РОГА И КОПЫТА' -- Наименование  собственной организации
declare @dateOtchS date
declare @dateOtchF datetime
declare @XMLResult nvarchar(MAX)
declare @conv varchar(1000)='c:\emart_ru\convert_xml.cmd'

select @inn=di.UNN, @kpp=di.KODPLATNDS, @orgname=di.MYORG FROM DIM_IGK di where len(DI.IGK) = 25

set @dateOtchs = (dateadd(dd,-1, (dateadd(mm, Datediff(mm,0,getdate()),0))))
set @dateOtchf = dateadd(ms,-datepart(ms, SYSDATETIMEOFFSET()), SYSDATETIMEOFFSET())
-- dateadd(ms,-datepart(ms, GETUTCDATE()), GETUTCDATE())


select @XMLResult = 
'<?xml version="1.0" encoding="utf-8"?>' +
CAST(
(
select(
select
/*****************************************/
(
select cast( doVal_1 as numeric(18,2)) 'ЦелевойОбъемФинансирования' ,cast( doVal_2 as numeric(18,2)) 'ПроцентВыполнения' ,cast( doVal_3 as numeric(18,2)) 'СальдоОпераций' 
,(
select cast( doVal_1 as numeric(18,2)) 'ЦенаКонтракта' ,cast( doVal_2 as numeric(18,2)) 'ПроцентВыполнения' ,cast( doVal_3 as numeric(18,2)) 'СальдоОпераций' ,cast( doVal_4 as numeric(18,2)) 'ВозвращеноЗаказчику' ,cast( doVal_6 as numeric(18,2)) 'ВозвращеноЗаказчикуСобственныеСредства' ,cast( doVal_7 as numeric(18,2)) 'ПолученоОтЗаказчика' 
from tQData
where tQData.IGK = DIM_IGK.NO_DOG and tQData.dOtch=@dateOtchS and tQData.NRow='1.1.'
FOR XML RAW ('ДенежныеСредстваЗаказчика'), type
)
,(
select cast( doVal_1 as numeric(18,2)) 'ПлановыйОбъемКредитования' ,cast( doVal_2 as numeric(18,2)) 'ПроцентВыполнения' ,cast( doVal_3 as numeric(18,2)) 'СальдоОпераций' ,cast( doVal_4 as numeric(18,2)) 'ПогашеноТелаКредита' ,cast( doVal_6 as numeric(18,2)) 'ПогашеноТелаКредитаСобственныеСредства' ,cast( doVal_7 as numeric(18,2)) 'ПривлеченоКредитов' 
from tQData
where tQData.IGK = DIM_IGK.NO_DOG and tQData.dOtch=@dateOtchS and tQData.NRow='1.2.'
FOR XML RAW ('БанковскиеКредиты'), type
)
,(
select cast( doVal_3 as numeric(18,2)) 'СальдоОпераций' ,cast( doVal_4 as numeric(18,2)) 'ПогашеноПроцентов' ,cast( doVal_6 as numeric(18,2)) 'ПогашеноПроцентовСобственныеСредства' ,cast( doVal_7 as numeric(18,2)) 'НачисленоПроцентов' 
from tQData
where tQData.IGK = DIM_IGK.NO_DOG and tQData.dOtch=@dateOtchS and tQData.NRow='1.3.'
FOR XML RAW ('ЗадолженностьПоПроцентамКредитов'), type
)
,(
select cast( doVal_3 as numeric(18,2)) 'СальдоОпераций' ,cast( doVal_4 as numeric(18,2)) 'ОплаченоПоставщикам' ,cast( doVal_5 as numeric(18,2)) 'ОплаченоПоставщикамСредстваДругихКонтрактов' ,cast( doVal_6 as numeric(18,2)) 'ОплаченоПоставщикамСобственныеСредства' ,cast( doVal_7 as numeric(18,2)) 'СуммарнаяЗадолженность' 
from tQData
where tQData.IGK = DIM_IGK.NO_DOG and tQData.dOtch=@dateOtchS and tQData.NRow='1.4.'
FOR XML RAW ('ЗадолженностьПоставщикам'), type
)
from tQData
where tQData.IGK = DIM_IGK.NO_DOG and tQData.dOtch=@dateOtchS and tQData.NRow='1.'
FOR XML RAW ('ГруппаФинансированиеКонтракта'), type

)
/*****************************************/
,
(
select cast( doVal_3 as numeric(18,2)) 'СальдоОпераций' 
,(
select cast( doVal_3 as numeric(18,2)) 'СальдоОпераций' ,cast( doVal_4 as numeric(18,2)) 'ДенежныеАктивы' ,cast( doVal_5 as numeric(18,2)) 'ДенежныеАктивыСредстваДругихКонтрактов' ,cast( doVal_6 as numeric(18,2)) 'ДенежныеАктивыСобственныеСредства' ,cast( doVal_7 as numeric(18,2)) 'ИспользованиеРесурсов', cast( doVal_8 as numeric(18,2)) 'ИспользованиеРесурсовДругиеКонтракты', cast( doVal_9 as numeric(18,2)) 'ИспользованиеРесурсовСобственныеСредства' 
,(
select cast( doVal_3 as numeric(18,2)) 'СальдоОпераций' ,cast( doVal_4 as numeric(18,2)) 'ЗачисленоИсполнениеКонтракта' ,cast( doVal_6 as numeric(18,2)) 'ЗачисленоИное' ,cast( doVal_7 as numeric(18,2)) 'СписаноИсполнениеКонтракта' ,cast( doVal_8 as numeric(18,2)) 'СписаноДругиеКонтракты' ,cast( doVal_9 as numeric(18,2)) 'СписаноРасходыОрганизации'
from tQData
where tQData.IGK = DIM_IGK.NO_DOG and tQData.dOtch=@dateOtchS and tQData.NRow='2.1.1.'
FOR XML RAW ('ДенежныеСредстваОтдельныйСчет'), type
)
,(
select cast( doVal_3 as numeric(18,2)) 'СальдоОпераций' ,cast( doVal_4 as numeric(18,2)) 'ПеречисленоНаДепозит' ,cast( doVal_7 as numeric(18,2)) 'ВозвращеноСДепозита' 
from tQData
where tQData.IGK = DIM_IGK.NO_DOG and tQData.dOtch=@dateOtchS and tQData.NRow='2.1.2.'
FOR XML RAW ('БанковскиеДепозиты'), type
)
,(
select cast( doVal_3 as numeric(18,2)) 'СальдоОпераций' ,cast( doVal_4 as numeric(18,2)) 'АвансыИсполнениеКонтракта' ,cast( doVal_5 as numeric(18,2)) 'АвансыСредстваДругихКонтрактов' ,cast( doVal_6 as numeric(18,2)) 'АвансыСобственныеСредства' ,cast( doVal_7 as numeric(18,2)) 'ЗачтеноАвансов' ,cast( doVal_9 as numeric(18,2)) 'СписаноЗадолженностиКооперации'
from tQData
where tQData.IGK = DIM_IGK.NO_DOG and tQData.dOtch=@dateOtchS and tQData.NRow='2.1.3.'
FOR XML RAW ('АвансыВыданные'), type
)
from tQData
where tQData.IGK = DIM_IGK.NO_DOG and tQData.dOtch=@dateOtchS and tQData.NRow='2.1.'
FOR XML RAW ('ГруппаДенежныеСредства'), type
)
,(
select cast( doVal_3 as numeric(18,2)) 'СальдоОпераций' ,cast( doVal_4 as numeric(18,2)) 'СформированоЗапасов' ,cast( doVal_5 as numeric(18,2)) 'СформированоЗапасовСредстваДругихКонтрактов' ,cast( doVal_6 as numeric(18,2)) 'СформированоЗапасовСобственныеСредства' ,cast( doVal_7 as numeric(18,2)) 'ИспользованоЗапасов' ,cast( doVal_8 as numeric(18,2)) 'ИспользованоЗапасовНаДругиеКонтракты' ,cast( doVal_9 as numeric(18,2)) 'ИспользованоЗапасовНуждыОрганизации'
,(
select cast( doVal_3 as numeric(18,2)) 'СальдоОпераций' ,cast( doVal_4 as numeric(18,2)) 'ПоступилоМатериалов' ,cast( doVal_5 as numeric(18,2)) 'ПоступилоМатериаловСредстваДругихКонтрактов' ,cast( doVal_6 as numeric(18,2)) 'ПоступилоМатериаловСобственныеСредства' ,cast( doVal_7 as numeric(18,2)) 'ИспользованоМатериалов' ,cast( doVal_8 as numeric(18,2)) 'ИспользованоМатериаловНаДругиеКонтракты' ,cast( doVal_9 as numeric(18,2)) 'ИспользованоМатериаловНуждыОрганизации'
from tQData
where tQData.IGK = DIM_IGK.NO_DOG and tQData.dOtch=@dateOtchS and tQData.NRow='2.2.1.'
FOR XML RAW ('МатериалыНаСкладах'), type
)
,(
select cast( doVal_3 as numeric(18,2)) 'СальдоОпераций' ,cast( doVal_4 as numeric(18,2)) 'Выделено' ,cast( doVal_7 as numeric(18,2)) 'ВключеноВСтоимостьЗапасов' ,cast( doVal_9 as numeric(18,2)) 'ПринятоКВычету' 
from tQData
where tQData.IGK = DIM_IGK.NO_DOG and tQData.dOtch=@dateOtchS and tQData.NRow='2.2.2.'
FOR XML RAW ('НДСПоПриобретеннымЦенностям'), type
)
,(
select cast( doVal_3 as numeric(18,2)) 'СальдоОпераций' ,cast( doVal_4 as numeric(18,2)) 'ПоступилоПолуфабрикатов' ,cast( doVal_5 as numeric(18,2)) 'ПоступилоПолуфабрикатовСредстваДругихКонтрактов' ,cast( doVal_6 as numeric(18,2)) 'ПоступилоПолуфабрикатовСобственныеСредства' ,cast( doVal_7 as numeric(18,2)) 'ИспользованоПолуфабрикатов' ,cast( doVal_8 as numeric(18,2)) 'ИспользованоПолуфабрикатовНаДругиеКонтракты' ,cast( doVal_9 as numeric(18,2)) 'ИспользованоПолуфабрикатовНуждыОрганизации'
from tQData
where tQData.IGK = DIM_IGK.NO_DOG and tQData.dOtch=@dateOtchS and tQData.NRow='2.2.3.'
FOR XML RAW ('ПолуфабрикатыНаСкладах'), type
)
,(
select cast( doVal_3 as numeric(18,2)) 'СальдоОпераций' ,cast( doVal_4 as numeric(18,2)) 'ПереданоСтороннемуИсполнителю' ,cast( doVal_7 as numeric(18,2)) 'ПринятоИзПереработки' ,cast( doVal_9 as numeric(18,2)) 'ПринятоИзПереработкиНуждыОрганизации'
from tQData
where tQData.IGK = DIM_IGK.NO_DOG and tQData.dOtch=@dateOtchS and tQData.NRow='2.2.4.'
FOR XML RAW ('МатериалыПереданныеВПереработку'), type
)
,(
select cast( doVal_3 as numeric(18,2)) 'СальдоОпераций' ,cast( doVal_4 as numeric(18,2)) 'НачисленоРБП' ,cast( doVal_7 as numeric(18,2)) 'СписаноРБП' 
from tQData
where tQData.IGK = DIM_IGK.NO_DOG and tQData.dOtch=@dateOtchS and tQData.NRow='2.2.5.'
FOR XML RAW ('РасходыБудущихПериодов'), type
)
,(
select cast( doVal_3 as numeric(18,2)) 'СальдоОпераций' ,cast( doVal_4 as numeric(18,2)) 'ПоступилоСредствПроизводства' ,cast( doVal_5 as numeric(18,2)) 'ПоступилоСредствПроизводстваСредстваДругихКонтрактов' ,cast( doVal_6 as numeric(18,2)) 'ПоступилоСредствПроизводстваСобственныеСредства' ,cast( doVal_7 as numeric(18,2)) 'ВыбылоСредствПроизводства' ,cast( doVal_8 as numeric(18,2)) 'ВыбылоСредствПроизводстваНаДругиеКонтракты' ,cast( doVal_9 as numeric(18,2)) 'ВыбылоСредствПроизводстваНуждыОрганизации'
from tQData
where tQData.IGK = DIM_IGK.NO_DOG and tQData.dOtch=@dateOtchS and tQData.NRow='2.2.6.'
FOR XML RAW ('СредстваПроизводства'), type
)
from tQData
where tQData.IGK = DIM_IGK.NO_DOG and tQData.dOtch=@dateOtchS and tQData.NRow='2.2.'
FOR XML RAW ('ГруппаЗапасы'), type
)
,(
select cast( doVal_3 as numeric(18,2)) 'СальдоОпераций' ,cast( doVal_4 as numeric(18,2)) 'ПроизводственныеЗатраты' ,cast( doVal_5 as numeric(18,2)) 'ПроизводственныеЗатратыДругихКонтрактов' ,cast( doVal_6 as numeric(18,2)) 'ПроизводственныеЗатратыСобственные' ,cast( doVal_7 as numeric(18,2)) 'Выпуск' ,cast( doVal_8 as numeric(18,2)) 'ВыпускНаДругиеКонтракты',cast( doVal_9 as numeric(18,2)) 'ВыпускНуждыОрганизации'
,(
select cast( doVal_1 as numeric(18,2)) 'ЦелевойПоказатель' ,cast( doVal_2 as numeric(18,2)) 'ПроцентВыполнения' ,cast( doVal_3 as numeric(18,2)) 'СальдоОпераций' ,cast( doVal_4 as numeric(18,2)) 'СписаноНаЗатраты' ,cast( doVal_5 as numeric(18,2)) 'СписаноЗатратДругихКонтрактов' ,cast( doVal_6 as numeric(18,2)) 'СписаноСобственныхЗатрат' ,cast( doVal_7 as numeric(18,2)) 'ИсключеноИзЗатрат' ,cast( doVal_8 as numeric(18,2)) 'ОтнесеноНаДругиеКонтракты',cast( doVal_9 as numeric(18,2)) 'ОтнесеноНаСобственныеЗатраты' 
from tQData
where tQData.IGK = DIM_IGK.NO_DOG and tQData.dOtch=@dateOtchS and tQData.NRow='2.3.1.'
FOR XML RAW ('МатериальныеЗатраты'), type
)
,(
select cast( doVal_1 as numeric(18,2)) 'ЦелевойПоказатель' ,cast( doVal_2 as numeric(18,2)) 'ПроцентВыполнения' ,cast( doVal_3 as numeric(18,2)) 'СальдоОпераций' ,cast( doVal_6 as numeric(18,2)) 'ЗарплатаИсполнителей' 
from tQData
where tQData.IGK = DIM_IGK.NO_DOG and tQData.dOtch=@dateOtchS and tQData.NRow='2.3.2.'
FOR XML RAW ('ЗатратыФОТ'), type
)
,(
select cast( doVal_1 as numeric(18,2)) 'ЦелевойПоказатель' ,cast( doVal_2 as numeric(18,2)) 'ПроцентВыполнения' ,cast( doVal_3 as numeric(18,2)) 'СальдоОпераций' ,cast( doVal_4 as numeric(18,2)) 'СписаноНаЗатраты' ,cast( doVal_5 as numeric(18,2)) 'СписаноЗатратДругихКонтрактов' ,cast( doVal_6 as numeric(18,2)) 'СписаноСобственныхЗатрат' ,cast( doVal_7 as numeric(18,2)) 'ИсключеноИзЗатрат' ,cast( doVal_8 as numeric(18,2)) 'ОтнесеноНаДругиеКонтракты' ,cast( doVal_9 as numeric(18,2)) 'ОтнесеноНаСобственныеЗатраты' 
from tQData
where tQData.IGK = DIM_IGK.NO_DOG and tQData.dOtch=@dateOtchS and tQData.NRow='2.3.3.'
FOR XML RAW ('ПрочиеПроизводственныеЗатраты'), type
)
,(
select cast( doVal_1 as numeric(18,2)) 'ЦелевойПоказатель' ,cast( doVal_2 as numeric(18,2)) 'ПроцентВыполнения' ,cast( doVal_3 as numeric(18,2)) 'СальдоОпераций' ,cast( doVal_6 as numeric(18,2)) 'РазмерЗатрат' 
from tQData
where tQData.IGK = DIM_IGK.NO_DOG and tQData.dOtch=@dateOtchS and tQData.NRow='2.3.4.'
FOR XML RAW ('ОбщепроизводственныеЗатраты'), type
)
,(
select cast( doVal_1 as numeric(18,2)) 'ЦелевойПоказатель' ,cast( doVal_2 as numeric(18,2)) 'ПроцентВыполнения' ,cast( doVal_3 as numeric(18,2)) 'СальдоОпераций' ,cast( doVal_6 as numeric(18,2)) 'РазмерЗатрат' 
from tQData
where tQData.IGK = DIM_IGK.NO_DOG and tQData.dOtch=@dateOtchS and tQData.NRow='2.3.5.'
FOR XML RAW ('ОбщехозяйственныеЗатраты'), type
)
,(
select cast( doVal_3 as numeric(18,2)) 'СальдоОпераций' ,cast( doVal_4 as numeric(18,2)) 'СписаноНаЗатраты' 
from tQData
where tQData.IGK = DIM_IGK.NO_DOG and tQData.dOtch=@dateOtchS and tQData.NRow='2.3.6.'
FOR XML RAW ('ПолуфабрикатыВнутренниеРаботы'), type
)
,(
select cast( doVal_3 as numeric(18,2)) 'СальдоОпераций' ,cast( doVal_7 as numeric(18,2)) 'Выпущено' 
from tQData
where tQData.IGK = DIM_IGK.NO_DOG and tQData.dOtch=@dateOtchS and tQData.NRow='2.3.7.'
FOR XML RAW ('ВыпускПолуфабрикатовВнутреннихРабот'), type
)
,(
select cast( doVal_3 as numeric(18,2)) 'СальдоОпераций' ,cast( doVal_7 as numeric(18,2)) 'Выпущено' 
from tQData
where tQData.IGK = DIM_IGK.NO_DOG and tQData.dOtch=@dateOtchS and tQData.NRow='2.3.8.'
FOR XML RAW ('ВыпускПродукции'), type
)
from tQData
where tQData.IGK = DIM_IGK.NO_DOG and tQData.dOtch=@dateOtchS and tQData.NRow='2.3.'
FOR XML RAW ('ГруппаПроизводство'), type
)
,(
select cast( doVal_3 as numeric(18,2)) 'СальдоОпераций' ,cast( doVal_4 as numeric(18,2)) 'Выпущено' ,cast( doVal_5 as numeric(18,2)) 'ИспользованоСДругихКонтрактов' ,cast( doVal_6 as numeric(18,2)) 'ИспользованоСобственной' ,cast( doVal_7 as numeric(18,2)) 'Отгружено' ,cast( doVal_8 as numeric(18,2)) 'ОтгруженоНаДругиеКонтракты' ,cast( doVal_9 as numeric(18,2)) 'ОтгруженоНаНуждыОрганизации' 
from tQData
where tQData.IGK = DIM_IGK.NO_DOG and tQData.dOtch=@dateOtchS and tQData.NRow='2.4.'
FOR XML RAW ('ГотоваяПродукция'), type
)
from tQData
where tQData.IGK = DIM_IGK.NO_DOG and tQData.dOtch=@dateOtchS and tQData.NRow='2.'
FOR XML RAW ('ГруппаРаспределениеРесурсовКонтракта'), type

)
/*****************************************/
,
(
select cast( doVal_1 as numeric(18,2)) 'ЦелевойПоказатель' ,cast( doVal_2 as numeric(18,2)) 'ПроцентВыполнения' ,cast( doVal_3 as numeric(18,2)) 'СальдоОпераций' 
,(
select cast( doVal_1 as numeric(18,2)) 'ЦелевойПоказатель' ,cast( doVal_2 as numeric(18,2)) 'ПроцентВыполнения' ,cast( doVal_3 as numeric(18,2)) 'СальдоОпераций' ,cast( doVal_4 as numeric(18,2)) 'СебестоимостьКонтракт' ,cast( doVal_6 as numeric(18,2)) 'СебестоимостьНеКонтракт' 
from tQData
where tQData.IGK = DIM_IGK.NO_DOG and tQData.dOtch=@dateOtchS and tQData.NRow='3.1.'
FOR XML RAW ('СебестоимостьПродаж'), type
)
,(
select cast( doVal_1 as numeric(18,2)) 'ЦелевойПоказатель' ,cast( doVal_2 as numeric(18,2)) 'ПроцентВыполнения' ,cast( doVal_3 as numeric(18,2)) 'СальдоОпераций' ,cast( doVal_6 as numeric(18,2)) 'РазмерЗатрат' 
from tQData
where tQData.IGK = DIM_IGK.NO_DOG and tQData.dOtch=@dateOtchS and tQData.NRow='3.2.'
FOR XML RAW ('АУР'), type
)
,(
select cast( doVal_1 as numeric(18,2)) 'ЦелевойПоказатель' ,cast( doVal_2 as numeric(18,2)) 'ПроцентВыполнения' ,cast( doVal_3 as numeric(18,2)) 'СальдоОпераций' ,cast( doVal_4 as numeric(18,2)) 'РазмерЗатрат' 
from tQData
where tQData.IGK = DIM_IGK.NO_DOG and tQData.dOtch=@dateOtchS and tQData.NRow='3.3.'
FOR XML RAW ('КоммерческиеРасходы'), type
)
,(
select cast( doVal_1 as numeric(18,2)) 'ЦелевойПоказатель' ,cast( doVal_2 as numeric(18,2)) 'ПроцентВыполнения' ,cast( doVal_3 as numeric(18,2)) 'СальдоОпераций' ,cast( doVal_4 as numeric(18,2)) 'РазмерЗатрат' 
from tQData
where tQData.IGK = DIM_IGK.NO_DOG and tQData.dOtch=@dateOtchS and tQData.NRow='3.4.'
FOR XML RAW ('ПроцентыПоБанковскимКредитам'), type
)
,(
select cast( doVal_3 as numeric(18,2)) 'СальдоОпераций', cast(doVal_6 as numeric(18,2)) 'СуммаНДС'
from tQData
where tQData.IGK = DIM_IGK.NO_DOG and tQData.dOtch=@dateOtchS and tQData.NRow='3.5.'
FOR XML RAW ('НДСПродажи'), type
)
,(
select cast( doVal_1 as numeric(18,2)) 'ЦелевойПоказатель' ,cast( doVal_2 as numeric(18,2)) 'ПроцентВыполнения' ,cast( doVal_3 as numeric(18,2)) 'СальдоОпераций' 
from tQData
where tQData.IGK = DIM_IGK.NO_DOG and tQData.dOtch=@dateOtchS and tQData.NRow='3.6.'
FOR XML RAW ('Прибыль'), type
)
from tQData
where tQData.IGK = DIM_IGK.NO_DOG and tQData.dOtch=@dateOtchS and tQData.NRow='3.'
FOR XML RAW ('ГруппаОтгрузкаПродукцииВыполнениеРабот'), type

)
/*****************************************/
,
(
select cast( doVal_3 as numeric(18,2)) 'СальдоОпераций' ,cast( doVal_5 as numeric(18,2)) 'ПривлеченоСредствДругихКонтрактов' ,cast( doVal_6 as numeric(18,2)) 'ПривлеченоСобственныхСредств' ,cast( doVal_8 as numeric(18,2)) 'ИспользованоНаДругиеКонтракты',cast( doVal_9 as numeric(18,2)) 'ИспользованоНаСобственныеНужды' 
from tQData
where tQData.IGK = DIM_IGK.NO_DOG and tQData.dOtch=@dateOtchS and tQData.NRow='4.'
FOR XML RAW ('ПеренаправлениеПривлечение'), type

)
/*****************************************/
,
(
select cast( doVal_1 as numeric(18,2)) 'ЦелевойПоказатель' ,cast( doVal_2 as numeric(18,2)) 'ПроцентВыполнения' ,cast( doVal_3 as numeric(18,2)) 'СальдоОпераций' 
from tQData
where tQData.IGK = DIM_IGK.NO_DOG and tQData.dOtch=@dateOtchS and tQData.NRow='5.'
FOR XML RAW ('СписаноСредств'), type

)
, null xmlns
,DIM_IGK.IGK ИГК
,dim_igk.N_SCHET НомерОтдельногоСчета
,@dateOtchS ДатаСоставленияОтчета
,DIM_IGK.NO_DOG НомерКонтракта
,CONVERT (date,dim_igk.DATE_DOG) ДатаЗаключенияКонтракта
,CONVERT (date,dim_igk.DATE_END) ПлановаяДатаИсполнения
from dim_igk
WHERE len(dbo.DIM_IGK.IGK) = 25
FOR XML RAW ('Контракт'), TYPE
)
,'EMART. Раздельный учет' ГенераторОтчета
,@inn ИННОрганизации
,@kpp КППОрганизации
,@orgname НаименованиеОрганизации
, format(getdate(),'yyyy-MM-ddTHH:mm:ss+05:00')  ДатаФормирования
,'http://mil.ru/discreteAccounting' xmlns
FOR XML RAW ('ДанныеРаздельногоУчета'), TYPE
)
as nvarchar(MAX))

SET @XMLResult = REPLACE(@XMLResult, 'xmlns="" ', '')

execute spWriteStringToFile @XMLResult, 'C:\EMART_RU','message_in.xml'

exec master..xp_cmdshell @conv

GO

/****** Object:  StoredProcedure [dbo].[MAKE_REP_XML]    Script Date: 19.04.2019 13:20:01 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



CREATE PROCEDURE [dbo].[MAKE_REP_XML]
(@list_no_dog varchar(MAX), @dateOtchS date)
WITH EXEC AS CALLER
AS
-- Разбираем строку с разделителями 
declare @delimeter varchar(1) = '|'
declare @PARS_STR varchar(max) = @list_no_dog
declare @inn nvarchar(30) = '3831281799' -- ИНН собственной организации
declare @kpp nvarchar(30) = '380228001' -- КПП собственной организации
declare @orgname nvarchar(30) = 'АО РОГА И КОПЫТА' -- Наименование  собственной организации
-- declare @dateOtchS date
declare @dateOtchF datetimeoffset= SYSDATETIMEOFFSET ()
declare @XMLResult nvarchar(MAX)
declare @conv varchar(1000)='c:\emart_ru\convert_xml.cmd'

UPDATE DIM_IGK SET ISMARKED = 0

declare @pos int = charindex(@delimeter,@PARS_STR)
declare @id nvarchar(255)
while  (@pos != 0)
begin
  set @id = SUBSTRING(@PARS_STR, 1, @pos-1)
  UPDATE DIM_IGK SET ISMARKED = 1 WHERE DIM_IGK.NO_DOG = @id

  set @PARS_STR = SUBSTRING(@PARS_STR, @pos+1, LEN(@PARS_STR))
  set @pos = CHARINDEX(@delimeter,@PARS_STR)
end
  UPDATE DIM_IGK SET ISMARKED = 1 WHERE DIM_IGK.NO_DOG = @PARS_STR

select @inn=di.UNN, @kpp=di.KODPLATNDS, @orgname=di.MYORG FROM DIM_IGK di where len(DI.ISMARKED) = 1

set @dateOtchS = (dateadd(dd,-1, (dateadd(mm, Datediff(mm,0,getdate()),0))))
set @dateOtchf = dateadd(ms,-datepart(ms, @dateOtchf), @dateOtchf)


select @XMLResult = 
'<?xml version="1.0" encoding="utf-8"?>' +
CAST(
(
select(
select
/*****************************************/
(
select cast( doVal_1 as numeric(18,2)) 'ЦелевойОбъемФинансирования' ,cast( doVal_2 as numeric(18,2)) 'ПроцентВыполнения' ,cast( doVal_3 as numeric(18,2)) 'СальдоОпераций' 
,(
select cast( doVal_1 as numeric(18,2)) 'ЦенаКонтракта' ,cast( doVal_2 as numeric(18,2)) 'ПроцентВыполнения' ,cast( doVal_3 as numeric(18,2)) 'СальдоОпераций' ,cast( doVal_4 as numeric(18,2)) 'ВозвращеноЗаказчику' ,cast( doVal_6 as numeric(18,2)) 'ВозвращеноЗаказчикуСобственныеСредства' ,cast( doVal_7 as numeric(18,2)) 'ПолученоОтЗаказчика' 
from tQData
where tQData.IGK = DIM_IGK.NO_DOG and tQData.dOtch=@dateOtchS and tQData.NRow='1.1.'
FOR XML RAW ('ДенежныеСредстваЗаказчика'), type
)
,(
select cast( doVal_1 as numeric(18,2)) 'ПлановыйОбъемКредитования' ,cast( doVal_2 as numeric(18,2)) 'ПроцентВыполнения' ,cast( doVal_3 as numeric(18,2)) 'СальдоОпераций' ,cast( doVal_4 as numeric(18,2)) 'ПогашеноТелаКредита' ,cast( doVal_6 as numeric(18,2)) 'ПогашеноТелаКредитаСобственныеСредства' ,cast( doVal_7 as numeric(18,2)) 'ПривлеченоКредитов' 
from tQData
where tQData.IGK = DIM_IGK.NO_DOG and tQData.dOtch=@dateOtchS and tQData.NRow='1.2.'
FOR XML RAW ('БанковскиеКредиты'), type
)
,(
select cast( doVal_3 as numeric(18,2)) 'СальдоОпераций' ,cast( doVal_4 as numeric(18,2)) 'ПогашеноПроцентов' ,cast( doVal_6 as numeric(18,2)) 'ПогашеноПроцентовСобственныеСредства' ,cast( doVal_7 as numeric(18,2)) 'НачисленоПроцентов' 
from tQData
where tQData.IGK = DIM_IGK.NO_DOG and tQData.dOtch=@dateOtchS and tQData.NRow='1.3.'
FOR XML RAW ('ЗадолженностьПоПроцентамКредитов'), type
)
,(
select cast( doVal_3 as numeric(18,2)) 'СальдоОпераций' ,cast( doVal_4 as numeric(18,2)) 'ОплаченоПоставщикам' ,cast( doVal_5 as numeric(18,2)) 'ОплаченоПоставщикамСредстваДругихКонтрактов' ,cast( doVal_6 as numeric(18,2)) 'ОплаченоПоставщикамСобственныеСредства' ,cast( doVal_7 as numeric(18,2)) 'СуммарнаяЗадолженность' 
from tQData
where tQData.IGK = DIM_IGK.NO_DOG and tQData.dOtch=@dateOtchS and tQData.NRow='1.4.'
FOR XML RAW ('ЗадолженностьПоставщикам'), type
)
from tQData
where tQData.IGK = DIM_IGK.NO_DOG and tQData.dOtch=@dateOtchS and tQData.NRow='1.'
FOR XML RAW ('ГруппаФинансированиеКонтракта'), type

)
/*****************************************/
,
(
select cast( doVal_3 as numeric(18,2)) 'СальдоОпераций' 
,(
select cast( doVal_3 as numeric(18,2)) 'СальдоОпераций' ,cast( doVal_4 as numeric(18,2)) 'ДенежныеАктивы' ,cast( doVal_5 as numeric(18,2)) 'ДенежныеАктивыСредстваДругихКонтрактов' ,cast( doVal_6 as numeric(18,2)) 'ДенежныеАктивыСобственныеСредства' ,cast( doVal_7 as numeric(18,2)) 'ИспользованиеРесурсов', cast( doVal_8 as numeric(18,2)) 'ИспользованиеРесурсовДругиеКонтракты', cast( doVal_9 as numeric(18,2)) 'ИспользованиеРесурсовСобственныеСредства' 
,(
select cast( doVal_3 as numeric(18,2)) 'СальдоОпераций' ,cast( doVal_4 as numeric(18,2)) 'ЗачисленоИсполнениеКонтракта' ,cast( doVal_6 as numeric(18,2)) 'ЗачисленоИное' ,cast( doVal_7 as numeric(18,2)) 'СписаноИсполнениеКонтракта' ,cast( doVal_8 as numeric(18,2)) 'СписаноДругиеКонтракты' ,cast( doVal_9 as numeric(18,2)) 'СписаноРасходыОрганизации'
from tQData
where tQData.IGK = DIM_IGK.NO_DOG and tQData.dOtch=@dateOtchS and tQData.NRow='2.1.1.'
FOR XML RAW ('ДенежныеСредстваОтдельныйСчет'), type
)
,(
select cast( doVal_3 as numeric(18,2)) 'СальдоОпераций' ,cast( doVal_4 as numeric(18,2)) 'ПеречисленоНаДепозит' ,cast( doVal_7 as numeric(18,2)) 'ВозвращеноСДепозита' 
from tQData
where tQData.IGK = DIM_IGK.NO_DOG and tQData.dOtch=@dateOtchS and tQData.NRow='2.1.2.'
FOR XML RAW ('БанковскиеДепозиты'), type
)
,(
select cast( doVal_3 as numeric(18,2)) 'СальдоОпераций' ,cast( doVal_4 as numeric(18,2)) 'АвансыИсполнениеКонтракта' ,cast( doVal_5 as numeric(18,2)) 'АвансыСредстваДругихКонтрактов' ,cast( doVal_6 as numeric(18,2)) 'АвансыСобственныеСредства' ,cast( doVal_7 as numeric(18,2)) 'ЗачтеноАвансов' ,cast( doVal_9 as numeric(18,2)) 'СписаноЗадолженностиКооперации'
from tQData
where tQData.IGK = DIM_IGK.NO_DOG and tQData.dOtch=@dateOtchS and tQData.NRow='2.1.3.'
FOR XML RAW ('АвансыВыданные'), type
)
from tQData
where tQData.IGK = DIM_IGK.NO_DOG and tQData.dOtch=@dateOtchS and tQData.NRow='2.1.'
FOR XML RAW ('ГруппаДенежныеСредства'), type
)
,(
select cast( doVal_3 as numeric(18,2)) 'СальдоОпераций' ,cast( doVal_4 as numeric(18,2)) 'СформированоЗапасов' ,cast( doVal_5 as numeric(18,2)) 'СформированоЗапасовСредстваДругихКонтрактов' ,cast( doVal_6 as numeric(18,2)) 'СформированоЗапасовСобственныеСредства' ,cast( doVal_7 as numeric(18,2)) 'ИспользованоЗапасов' ,cast( doVal_8 as numeric(18,2)) 'ИспользованоЗапасовНаДругиеКонтракты' ,cast( doVal_9 as numeric(18,2)) 'ИспользованоЗапасовНуждыОрганизации'
,(
select cast( doVal_3 as numeric(18,2)) 'СальдоОпераций' ,cast( doVal_4 as numeric(18,2)) 'ПоступилоМатериалов' ,cast( doVal_5 as numeric(18,2)) 'ПоступилоМатериаловСредстваДругихКонтрактов' ,cast( doVal_6 as numeric(18,2)) 'ПоступилоМатериаловСобственныеСредства' ,cast( doVal_7 as numeric(18,2)) 'ИспользованоМатериалов' ,cast( doVal_8 as numeric(18,2)) 'ИспользованоМатериаловНаДругиеКонтракты' ,cast( doVal_9 as numeric(18,2)) 'ИспользованоМатериаловНуждыОрганизации'
from tQData
where tQData.IGK = DIM_IGK.NO_DOG and tQData.dOtch=@dateOtchS and tQData.NRow='2.2.1.'
FOR XML RAW ('МатериалыНаСкладах'), type
)
,(
select cast( doVal_3 as numeric(18,2)) 'СальдоОпераций' ,cast( doVal_4 as numeric(18,2)) 'Выделено' ,cast( doVal_7 as numeric(18,2)) 'ВключеноВСтоимостьЗапасов' ,cast( doVal_9 as numeric(18,2)) 'ПринятоКВычету' 
from tQData
where tQData.IGK = DIM_IGK.NO_DOG and tQData.dOtch=@dateOtchS and tQData.NRow='2.2.2.'
FOR XML RAW ('НДСПоПриобретеннымЦенностям'), type
)
,(
select cast( doVal_3 as numeric(18,2)) 'СальдоОпераций' ,cast( doVal_4 as numeric(18,2)) 'ПоступилоПолуфабрикатов' ,cast( doVal_5 as numeric(18,2)) 'ПоступилоПолуфабрикатовСредстваДругихКонтрактов' ,cast( doVal_6 as numeric(18,2)) 'ПоступилоПолуфабрикатовСобственныеСредства' ,cast( doVal_7 as numeric(18,2)) 'ИспользованоПолуфабрикатов' ,cast( doVal_8 as numeric(18,2)) 'ИспользованоПолуфабрикатовНаДругиеКонтракты' ,cast( doVal_9 as numeric(18,2)) 'ИспользованоПолуфабрикатовНуждыОрганизации'
from tQData
where tQData.IGK = DIM_IGK.NO_DOG and tQData.dOtch=@dateOtchS and tQData.NRow='2.2.3.'
FOR XML RAW ('ПолуфабрикатыНаСкладах'), type
)
,(
select cast( doVal_3 as numeric(18,2)) 'СальдоОпераций' ,cast( doVal_4 as numeric(18,2)) 'ПереданоСтороннемуИсполнителю' ,cast( doVal_7 as numeric(18,2)) 'ПринятоИзПереработки' ,cast( doVal_9 as numeric(18,2)) 'ПринятоИзПереработкиНуждыОрганизации'
from tQData
where tQData.IGK = DIM_IGK.NO_DOG and tQData.dOtch=@dateOtchS and tQData.NRow='2.2.4.'
FOR XML RAW ('МатериалыПереданныеВПереработку'), type
)
,(
select cast( doVal_3 as numeric(18,2)) 'СальдоОпераций' ,cast( doVal_4 as numeric(18,2)) 'НачисленоРБП' ,cast( doVal_7 as numeric(18,2)) 'СписаноРБП' 
from tQData
where tQData.IGK = DIM_IGK.NO_DOG and tQData.dOtch=@dateOtchS and tQData.NRow='2.2.5.'
FOR XML RAW ('РасходыБудущихПериодов'), type
)
,(
select cast( doVal_3 as numeric(18,2)) 'СальдоОпераций' ,cast( doVal_4 as numeric(18,2)) 'ПоступилоСредствПроизводства' ,cast( doVal_5 as numeric(18,2)) 'ПоступилоСредствПроизводстваСредстваДругихКонтрактов' ,cast( doVal_6 as numeric(18,2)) 'ПоступилоСредствПроизводстваСобственныеСредства' ,cast( doVal_7 as numeric(18,2)) 'ВыбылоСредствПроизводства' ,cast( doVal_8 as numeric(18,2)) 'ВыбылоСредствПроизводстваНаДругиеКонтракты' ,cast( doVal_9 as numeric(18,2)) 'ВыбылоСредствПроизводстваНуждыОрганизации'
from tQData
where tQData.IGK = DIM_IGK.NO_DOG and tQData.dOtch=@dateOtchS and tQData.NRow='2.2.6.'
FOR XML RAW ('СредстваПроизводства'), type
)
from tQData
where tQData.IGK = DIM_IGK.NO_DOG and tQData.dOtch=@dateOtchS and tQData.NRow='2.2.'
FOR XML RAW ('ГруппаЗапасы'), type
)
,(
select cast( doVal_3 as numeric(18,2)) 'СальдоОпераций' ,cast( doVal_4 as numeric(18,2)) 'ПроизводственныеЗатраты' ,cast( doVal_5 as numeric(18,2)) 'ПроизводственныеЗатратыДругихКонтрактов' ,cast( doVal_6 as numeric(18,2)) 'ПроизводственныеЗатратыСобственные' ,cast( doVal_7 as numeric(18,2)) 'Выпуск' ,cast( doVal_8 as numeric(18,2)) 'ВыпускНаДругиеКонтракты',cast( doVal_9 as numeric(18,2)) 'ВыпускНуждыОрганизации'
,(
select cast( doVal_1 as numeric(18,2)) 'ЦелевойПоказатель' ,cast( doVal_2 as numeric(18,2)) 'ПроцентВыполнения' ,cast( doVal_3 as numeric(18,2)) 'СальдоОпераций' ,cast( doVal_4 as numeric(18,2)) 'СписаноНаЗатраты' ,cast( doVal_5 as numeric(18,2)) 'СписаноЗатратДругихКонтрактов' ,cast( doVal_6 as numeric(18,2)) 'СписаноСобственныхЗатрат' ,cast( doVal_7 as numeric(18,2)) 'ИсключеноИзЗатрат' ,cast( doVal_8 as numeric(18,2)) 'ОтнесеноНаДругиеКонтракты',cast( doVal_9 as numeric(18,2)) 'ОтнесеноНаСобственныеЗатраты' 
from tQData
where tQData.IGK = DIM_IGK.NO_DOG and tQData.dOtch=@dateOtchS and tQData.NRow='2.3.1.'
FOR XML RAW ('МатериальныеЗатраты'), type
)
,(
select cast( doVal_1 as numeric(18,2)) 'ЦелевойПоказатель' ,cast( doVal_2 as numeric(18,2)) 'ПроцентВыполнения' ,cast( doVal_3 as numeric(18,2)) 'СальдоОпераций' ,cast( doVal_6 as numeric(18,2)) 'ЗарплатаИсполнителей' 
from tQData
where tQData.IGK = DIM_IGK.NO_DOG and tQData.dOtch=@dateOtchS and tQData.NRow='2.3.2.'
FOR XML RAW ('ЗатратыФОТ'), type
)
,(
select cast( doVal_1 as numeric(18,2)) 'ЦелевойПоказатель' ,cast( doVal_2 as numeric(18,2)) 'ПроцентВыполнения' ,cast( doVal_3 as numeric(18,2)) 'СальдоОпераций' ,cast( doVal_4 as numeric(18,2)) 'СписаноНаЗатраты' ,cast( doVal_5 as numeric(18,2)) 'СписаноЗатратДругихКонтрактов' ,cast( doVal_6 as numeric(18,2)) 'СписаноСобственныхЗатрат' ,cast( doVal_7 as numeric(18,2)) 'ИсключеноИзЗатрат' ,cast( doVal_8 as numeric(18,2)) 'ОтнесеноНаДругиеКонтракты' ,cast( doVal_9 as numeric(18,2)) 'ОтнесеноНаСобственныеЗатраты' 
from tQData
where tQData.IGK = DIM_IGK.NO_DOG and tQData.dOtch=@dateOtchS and tQData.NRow='2.3.3.'
FOR XML RAW ('ПрочиеПроизводственныеЗатраты'), type
)
,(
select cast( doVal_1 as numeric(18,2)) 'ЦелевойПоказатель' ,cast( doVal_2 as numeric(18,2)) 'ПроцентВыполнения' ,cast( doVal_3 as numeric(18,2)) 'СальдоОпераций' ,cast( doVal_6 as numeric(18,2)) 'РазмерЗатрат' 
from tQData
where tQData.IGK = DIM_IGK.NO_DOG and tQData.dOtch=@dateOtchS and tQData.NRow='2.3.4.'
FOR XML RAW ('ОбщепроизводственныеЗатраты'), type
)
,(
select cast( doVal_1 as numeric(18,2)) 'ЦелевойПоказатель' ,cast( doVal_2 as numeric(18,2)) 'ПроцентВыполнения' ,cast( doVal_3 as numeric(18,2)) 'СальдоОпераций' ,cast( doVal_6 as numeric(18,2)) 'РазмерЗатрат' 
from tQData
where tQData.IGK = DIM_IGK.NO_DOG and tQData.dOtch=@dateOtchS and tQData.NRow='2.3.5.'
FOR XML RAW ('ОбщехозяйственныеЗатраты'), type
)
,(
select cast( doVal_3 as numeric(18,2)) 'СальдоОпераций' ,cast( doVal_4 as numeric(18,2)) 'СписаноНаЗатраты' 
from tQData
where tQData.IGK = DIM_IGK.NO_DOG and tQData.dOtch=@dateOtchS and tQData.NRow='2.3.6.'
FOR XML RAW ('ПолуфабрикатыВнутренниеРаботы'), type
)
,(
select cast( doVal_3 as numeric(18,2)) 'СальдоОпераций' ,cast( doVal_7 as numeric(18,2)) 'Выпущено' 
from tQData
where tQData.IGK = DIM_IGK.NO_DOG and tQData.dOtch=@dateOtchS and tQData.NRow='2.3.7.'
FOR XML RAW ('ВыпускПолуфабрикатовВнутреннихРабот'), type
)
,(
select cast( doVal_3 as numeric(18,2)) 'СальдоОпераций' ,cast( doVal_7 as numeric(18,2)) 'Выпущено' 
from tQData
where tQData.IGK = DIM_IGK.NO_DOG and tQData.dOtch=@dateOtchS and tQData.NRow='2.3.8.'
FOR XML RAW ('ВыпускПродукции'), type
)
from tQData
where tQData.IGK = DIM_IGK.NO_DOG and tQData.dOtch=@dateOtchS and tQData.NRow='2.3.'
FOR XML RAW ('ГруппаПроизводство'), type
)
,(
select cast( doVal_3 as numeric(18,2)) 'СальдоОпераций' ,cast( doVal_4 as numeric(18,2)) 'Выпущено' ,cast( doVal_5 as numeric(18,2)) 'ИспользованоСДругихКонтрактов' ,cast( doVal_6 as numeric(18,2)) 'ИспользованоСобственной' ,cast( doVal_7 as numeric(18,2)) 'Отгружено' ,cast( doVal_8 as numeric(18,2)) 'ОтгруженоНаДругиеКонтракты' ,cast( doVal_9 as numeric(18,2)) 'ОтгруженоНаНуждыОрганизации' 
from tQData
where tQData.IGK = DIM_IGK.NO_DOG and tQData.dOtch=@dateOtchS and tQData.NRow='2.4.'
FOR XML RAW ('ГотоваяПродукция'), type
)
from tQData
where tQData.IGK = DIM_IGK.NO_DOG and tQData.dOtch=@dateOtchS and tQData.NRow='2.'
FOR XML RAW ('ГруппаРаспределениеРесурсовКонтракта'), type

)
/*****************************************/
,
(
select cast( doVal_1 as numeric(18,2)) 'ЦелевойПоказатель' ,cast( doVal_2 as numeric(18,2)) 'ПроцентВыполнения' ,cast( doVal_3 as numeric(18,2)) 'СальдоОпераций' 
,(
select cast( doVal_1 as numeric(18,2)) 'ЦелевойПоказатель' ,cast( doVal_2 as numeric(18,2)) 'ПроцентВыполнения' ,cast( doVal_3 as numeric(18,2)) 'СальдоОпераций' ,cast( doVal_4 as numeric(18,2)) 'СебестоимостьКонтракт' ,cast( doVal_6 as numeric(18,2)) 'СебестоимостьНеКонтракт' 
from tQData
where tQData.IGK = DIM_IGK.NO_DOG and tQData.dOtch=@dateOtchS and tQData.NRow='3.1.'
FOR XML RAW ('СебестоимостьПродаж'), type
)
,(
select cast( doVal_1 as numeric(18,2)) 'ЦелевойПоказатель' ,cast( doVal_2 as numeric(18,2)) 'ПроцентВыполнения' ,cast( doVal_3 as numeric(18,2)) 'СальдоОпераций' ,cast( doVal_6 as numeric(18,2)) 'РазмерЗатрат' 
from tQData
where tQData.IGK = DIM_IGK.NO_DOG and tQData.dOtch=@dateOtchS and tQData.NRow='3.2.'
FOR XML RAW ('АУР'), type
)
,(
select cast( doVal_1 as numeric(18,2)) 'ЦелевойПоказатель' ,cast( doVal_2 as numeric(18,2)) 'ПроцентВыполнения' ,cast( doVal_3 as numeric(18,2)) 'СальдоОпераций' ,cast( doVal_4 as numeric(18,2)) 'РазмерЗатрат' 
from tQData
where tQData.IGK = DIM_IGK.NO_DOG and tQData.dOtch=@dateOtchS and tQData.NRow='3.3.'
FOR XML RAW ('КоммерческиеРасходы'), type
)
,(
select cast( doVal_1 as numeric(18,2)) 'ЦелевойПоказатель' ,cast( doVal_2 as numeric(18,2)) 'ПроцентВыполнения' ,cast( doVal_3 as numeric(18,2)) 'СальдоОпераций' ,cast( doVal_4 as numeric(18,2)) 'РазмерЗатрат' 
from tQData
where tQData.IGK = DIM_IGK.NO_DOG and tQData.dOtch=@dateOtchS and tQData.NRow='3.4.'
FOR XML RAW ('ПроцентыПоБанковскимКредитам'), type
)
,(
select cast( doVal_3 as numeric(18,2)) 'СальдоОпераций', cast(doVal_6 as numeric(18,2)) 'СуммаНДС'
from tQData
where tQData.IGK = DIM_IGK.NO_DOG and tQData.dOtch=@dateOtchS and tQData.NRow='3.5.'
FOR XML RAW ('НДСПродажи'), type
)
,(
select cast( doVal_1 as numeric(18,2)) 'ЦелевойПоказатель' ,cast( doVal_2 as numeric(18,2)) 'ПроцентВыполнения' ,cast( doVal_3 as numeric(18,2)) 'СальдоОпераций' 
from tQData
where tQData.IGK = DIM_IGK.NO_DOG and tQData.dOtch=@dateOtchS and tQData.NRow='3.6.'
FOR XML RAW ('Прибыль'), type
)
from tQData
where tQData.IGK = DIM_IGK.NO_DOG and tQData.dOtch=@dateOtchS and tQData.NRow='3.'
FOR XML RAW ('ГруппаОтгрузкаПродукцииВыполнениеРабот'), type

)
/*****************************************/
,
(
select cast( doVal_3 as numeric(18,2)) 'СальдоОпераций' ,cast( doVal_5 as numeric(18,2)) 'ПривлеченоСредствДругихКонтрактов' ,cast( doVal_6 as numeric(18,2)) 'ПривлеченоСобственныхСредств' ,cast( doVal_8 as numeric(18,2)) 'ИспользованоНаДругиеКонтракты',cast( doVal_9 as numeric(18,2)) 'ИспользованоНаСобственныеНужды' 
from tQData
where tQData.IGK = DIM_IGK.NO_DOG and tQData.dOtch=@dateOtchS and tQData.NRow='4.'
FOR XML RAW ('ПеренаправлениеПривлечение'), type

)
/*****************************************/
,
(
select cast( doVal_1 as numeric(18,2)) 'ЦелевойПоказатель' ,cast( doVal_2 as numeric(18,2)) 'ПроцентВыполнения' ,cast( doVal_3 as numeric(18,2)) 'СальдоОпераций' 
from tQData
where tQData.IGK = DIM_IGK.NO_DOG and tQData.dOtch=@dateOtchS and tQData.NRow='5.'
FOR XML RAW ('СписаноСредств'), type

)
, null xmlns
,DIM_IGK.IGK ИГК
,dim_igk.N_SCHET НомерОтдельногоСчета
,@dateOtchS ДатаСоставленияОтчета
,DIM_IGK.NO_DOG НомерКонтракта
,CONVERT (date,dim_igk.DATE_DOG) ДатаЗаключенияКонтракта
,CONVERT (date,dim_igk.DATE_END) ПлановаяДатаИсполнения
from dim_igk
WHERE DIM_IGK.isMarked = 1
FOR XML RAW ('Контракт'), TYPE
)
,'EMART.Раздельный учет' ГенераторОтчета
,@inn ИННОрганизации
,@kpp КППОрганизации
,@orgname НаименованиеОрганизации
, format(getdate(),'yyyy-MM-ddTHH:mm:ss+05:00')  ДатаФормирования
,'http://mil.ru/discreteAccounting' xmlns
FOR XML RAW ('ДанныеРаздельногоУчета'), TYPE
)
as nvarchar(MAX))

SET @XMLResult = REPLACE(@XMLResult, 'xmlns="" ', '')

execute spWriteStringToFile @XMLResult, 'C:\EMART_RU','message_in.xml'

exec master..xp_cmdshell @conv

GO

/****** Object:  StoredProcedure [dbo].[MakeAkt10]    Script Date: 19.04.2019 13:20:01 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[MakeAkt10] AS
DECLARE @NO_DOG VARCHAR(100)
declare @dateOtchS datetime = getdate()
declare @dateOtchf datetime

set @dateOtchS = (dateadd(dd,-1, (dateadd(mm, Datediff(mm,0,@dateOtchS),0))))
-- set @dateOtchs = dateadd(MM,1, @dateOtchS)
set @dateOtchf = dateadd(dd,1, @dateOtchS)

delete from FACT_OBOROT1C where TIDK = 887 and DATOB = @dateOtchS



DECLARE @CURSOR CURSOR
SET @CURSOR  = CURSOR SCROLL
FOR
select no_dog from DIM_IGK where no_dog <> '-'

OPEN @CURSOR
FETCH NEXT FROM @CURSOR INTO @NO_DOG

WHILE @@FETCH_STATUS = 0
BEGIN

INSERT INTO [dbo].[FACT_OBOROT1C]
           ([OFICENO]
           ,[ID_]
           ,[НомерДокумента]
           ,[TIDK]
           ,[id_DOC]
           ,[DATOB]
           ,[CPLANSSCH]
           ,[F$SCHETO]
           ,[CDEBSS]
           ,[CDEB]
           ,[CSHPZ]
           ,[ШПЗ]
           ,[NO_DOG]
           ,[ORG_NAME]
           ,[CPODR]
           ,[CORG]
           ,[CPARTY]
           ,[CMC]
           ,[CSTZATR]
           ,[CPERSON]
           ,[SUMD1]
           ,[SUMD2]
           ,[SUMD3]
           ,[SUMK4]
           ,[SUMK5]
           ,[SUMK6]
           ,[F$SCHETK]
           ,[CcredsS]
           ,[CCRED]
           ,[CSHPZ_KORR]
           ,[ШПЗ_КОР]
           ,[NO_DOG_KORR]
           ,[ORG_NAME_KORR]
           ,[CORG_CORR]
           ,[CPARTY_CORR]
           ,[CMC_CORR]
           ,[CSTZATR_CORR]
           ,[CPERSON_CORR]
           ,[SUMD]
           ,[SUMK]
           ,[isDeb]
           ,[sum0]
           ,[sum3]
           ,[line]
           ,[line_txt])
SELECT 10 OFICENO, CONVERT(VARCHAR, SYSDATETIME()) ID_, 'Акт изменения целевого назначения по контракту №'+NO_DOG+' от '+CONVERT(VARCHAR, SYSDATETIME())  НомерДокумента,
887 TIDK,
CONVERT(VARCHAR, SYSDATETIME()) id_DOC,
@dateOtchS DATOB,
'1C' CPLANSSCH,
B.SCHET F$SCHETO,
B.SCHET CDEBSS,
'2.2.1.' CDEB,
B.CSHPZ CSHPZ,
'-' ШПЗ,
B.NO_DOG, B.ORG_NAME
, '-' CPODR
, '-' CORG
, '-' CPARTY
, B.CMC,
'-' CSTZATR,
'-' CPERSON,
0 SUMD1,
0 SUMD2,
SUMK-SUMD SUMD3,
0 SUMK4,
0 SUMK5,
0 SUMK6,
B.SCHET F$SCHETK,
B.SCHET CcredsS,
'2.2.1.' CCRED,
'-' CSHPZ_KORR,
'-' ШПЗ_КОР,
'-' NO_DOG_KORR,
'-' ORG_NAME_KORR,
'-' CORG_CORR,
'-' CPARTY_CORR,
B.CMC CMC_CORR,
'-' CSTZATR_CORR,
'-' CPERSON_CORR,
SUMK-SUMD SUMD,
0 SUMK, 
1 isDeb,
0 SUM0,
0 SUM3,
12 LINE,
'2.2.1.' LINE_TXT
FROM (
SELECT A.SCHET, CMC, CSHPZ, NO_DOG, ORG_NAME, SUM(SUMK) SUMK, SUM(SUMD) SUMD
FROM 
(
SELECT FO.F$SCHETO SCHET, FO.CSHPZ,FO.NO_DOG, FO.ORG_NAME, FO.CMC , SUM(FO.SUMK) SUMK, 0 SUMD
  FROM FACT_OBOROT1C FO
  left outer join DIM_IGK di on di.id = FO.CSHPZ
 WHERE     FO.isDeb = 0
       AND F$SCHETO LIKE '10%'
--       AND F$SCHETK LIKE '20%'
       AND line_txt = '2.2.1.'
       AND di.NO_DOG = @NO_DOG
	   AND FO.DATOB < @dateOtchf
 GROUP BY FO.F$SCHETO, FO.CSHPZ,FO.NO_DOG, FO.ORG_NAME, FO.CMC
UNION ALL
SELECT FO.F$SCHETO, FO.CSHPZ, FO.NO_DOG, FO.ORG_NAME, FO.CMC, 0 SUMK, SUM(FO.SUMD) SUMD  
FROM FACT_OBOROT1C FO
  left outer join DIM_IGK di on di.id = FO.CSHPZ
  WHERE     FO.isDeb = 1
       AND F$SCHETO LIKE '10%'
--       AND F$SCHETK LIKE '60%'
       AND line_txt = '2.2.1.'
       AND di.NO_DOG = @NO_DOG 
	   AND FO.DATOB < @dateOtchf
  GROUP BY FO.F$SCHETO, FO.CMC, FO.CSHPZ,FO.NO_DOG, FO.ORG_NAME
) A
GROUP BY SCHET, CMC, CSHPZ, NO_DOG, ORG_NAME
HAVING SUM(SUMK) > SUM(SUMD)
) B 
-- Парная часть
INSERT INTO [dbo].[FACT_OBOROT1C]
           ([OFICENO]
           ,[ID_]
           ,[НомерДокумента]
           ,[TIDK]
           ,[id_DOC]
           ,[DATOB]
           ,[CPLANSSCH]
           ,[F$SCHETO]
           ,[CDEBSS]
           ,[CDEB]
           ,[CSHPZ]
           ,[ШПЗ]
           ,[NO_DOG]
           ,[ORG_NAME]
           ,[CPODR]
           ,[CORG]
           ,[CPARTY]
           ,[CMC]
           ,[CSTZATR]
           ,[CPERSON]
           ,[SUMD1]
           ,[SUMD2]
           ,[SUMD3]
           ,[SUMK4]
           ,[SUMK5]
           ,[SUMK6]
           ,[F$SCHETK]
           ,[CcredsS]
           ,[CCRED]
           ,[CSHPZ_KORR]
           ,[ШПЗ_КОР]
           ,[NO_DOG_KORR]
           ,[ORG_NAME_KORR]
           ,[CORG_CORR]
           ,[CPARTY_CORR]
           ,[CMC_CORR]
           ,[CSTZATR_CORR]
           ,[CPERSON_CORR]
           ,[SUMD]
           ,[SUMK]
           ,[isDeb]
           ,[sum0]
           ,[sum3]
           ,[line]
           ,[line_txt])
SELECT 10 OFICENO, CONVERT(VARCHAR, SYSDATETIME()) ID_, 'Акт изменения целевого назначения по контракту №'+NO_DOG+' от '+CONVERT(VARCHAR, SYSDATETIME())  НомерДокумента,
887 TIDK,
CONVERT(VARCHAR, SYSDATETIME()) id_DOC,
@dateOtchS DATOB,
'1C' CPLANSSCH,
B.SCHET F$SCHETO,
B.SCHET CDEBSS,
'2.2.1.' CDEB,
'-' CSHPZ,
'-' ШПЗ,
'-', '-' 
, '-' CPODR
, '-' CORG
, '-' CPARTY
, B.CMC,
'-' CSTZATR,
'-' CPERSON,
0 SUMD1,
0 SUMD2,
0 SUMD3,
0 SUMK4,
0 SUMK5,
SUMK-SUMD SUMK6,
B.SCHET F$SCHETK,
B.SCHET CcredsS,
'2.2.1.' CCRED,
B.CSHPZ CSHPZ_KORR,
'-' ШПЗ_КОР,
B.NO_DOG NO_DOG_KORR,
B.ORG_NAME ORG_NAME_KORR,
'-' CORG_CORR,
'-' CPARTY_CORR,
B.CMC CMC_CORR,
'-' CSTZATR_CORR,
'-' CPERSON_CORR,
0 SUMD,
SUMK-SUMD SUMK, 
0 isDeb,
0 SUM0,
0 SUM3,
999 LINE,
'999' LINE_TXT
FROM (
SELECT A.SCHET, CMC, CSHPZ, NO_DOG, ORG_NAME, SUM(SUMK) SUMK, SUM(SUMD) SUMD
FROM 
(
SELECT FO.F$SCHETO SCHET, FO.CSHPZ,FO.NO_DOG, FO.ORG_NAME, FO.CMC , SUM(FO.SUMK) SUMK, 0 SUMD
  FROM FACT_OBOROT1C FO
  left outer join DIM_IGK di on di.id = FO.CSHPZ
 WHERE     FO.isDeb = 0
       AND F$SCHETO LIKE '10%'
--       AND F$SCHETK LIKE '20%'
       AND line_txt = '2.2.1.'
       AND di.NO_DOG = @NO_DOG
	   AND FO.DATOB < @dateOtchf
	   AND NOT (FO.TIDK = 887 AND FO.DATOB = @dateOtchS)
  GROUP BY FO.F$SCHETO, FO.CSHPZ,FO.NO_DOG, FO.ORG_NAME, FO.CMC
UNION 
SELECT FO.F$SCHETO, FO.CSHPZ, FO.NO_DOG, FO.ORG_NAME, FO.CMC, 0 SUMK, SUM(FO.SUMD) SUMD
  FROM FACT_OBOROT1C FO
  left outer join DIM_IGK di on di.id = FO.CSHPZ
  WHERE     FO.isDeb = 1
       AND F$SCHETO LIKE '10%'
--       AND F$SCHETK LIKE '60%'
       AND line_txt = '2.2.1.'
       AND di.NO_DOG = @NO_DOG 
	   AND FO.DATOB < @dateOtchf
	   AND NOT (FO.TIDK = 887 AND FO.DATOB = @dateOtchS)
  GROUP BY FO.F$SCHETO, FO.CMC, FO.CSHPZ,FO.NO_DOG, FO.ORG_NAME
) A
GROUP BY SCHET, CMC, CSHPZ, NO_DOG, ORG_NAME
HAVING SUM(SUMK) > SUM(SUMD)
) B 

FETCH NEXT FROM @CURSOR INTO @NO_DOG
END  
CLOSE @CURSOR


GO

