USE [P543_V2.2]
GO

/****** Object:  StoredProcedure [dbo].[FIX_REPORT]    Script Date: 09.04.2019 13:07:55 ******/
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 else 0 end, 2) * 100

update tQData set doVal_9 = 0 where tQData.NRow = '1.4.'
GO

/****** Object:  StoredProcedure [dbo].[FIX_REPORT_XML]    Script Date: 09.04.2019 13:07:55 ******/
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
)
,'Март 1.0: Раздельный учет' ГенераторОтчета
,@inn ИННОрганизации
,@kpp КППОрганизации
,@orgname НаименованиеОрганизации
, format(getdate(),'yyyy/MM/ddTHH:mm+03: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].[LOAD_DIM_BUHSCHET]    Script Date: 09.04.2019 13:07:55 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO






-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[LOAD_DIM_BUHSCHET] 
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
truncate table [dbo].[DIM_buhschet]
INSERT INTO [dbo].[DIM_buhschet]
           ([id]
           ,[cnode]
           ,[Код счета БУ]
           ,[Счет БУ]
		   , schet
)
select 
t4.[СчетСервиса]  id
, null cnode, t4.[Код] [Код счета БУ], t4.[Наименование] [Счет БУ], ''
from [dbo].[Tab4_plan_1c] t4
union  all
select '-', null, '-', '-', ''
union  all
select '0_888.01', null, '888.01', 'Плановый объем финансирования контракта заказчиком', ''
union  all
select '0_888.02', null, '888.02', 'Планируемая при заключении контракта сумма кредитов', ''
union  all
select '0_888.03', null, '888.03', 'Плановая стоимость материалов', ''
union  all
select '0_888.04', null, '888.04', 'Плановый объем заработной платы основных исполнителей контракта с учетом соответствующих налогов и страховых взносов', ''
union  all
select '0_888.05', null, '888.05', 'Плановый объем прочих производственных затрат', ''
union  all
select '0_888.06', null, '888.06', 'Плановый объем общепроизводственных затрат', ''
union  all
select '0_888.07', null, '888.07', 'Плановый объем общехозяйственных затрат', ''
union  all
select '0_888.08', null, '888.08', 'Плановый объем выполнения обязательств по контракту', ''
union  all
select '0_888.09', null, '888.09', 'Плановый объем административно-управленческих расходов', ''
union  all
select '0_888.10', null, '888.10', 'Плановый объем коммерческих расходов', ''
union  all
select '0_888.11', null, '888.11', 'Планируемая при заключении контракта сумма процентов по кредитам', ''
union  all
select '0_888.12', null, '888.12', 'Размер прибыли, планируемой при заключении контракта', ''
union  all
select '0_888.13', null, '888.13', 'Сумма согласованных сторонами размера прибыли и понесенных за счет собственных средств расходов на формирование запаса продукции', ''
union  all
select '0_888.12', null, '888.14', 'Плановая себестоимость', ''

INSERT INTO [dbo].[DIM_buhschet]
           ([id]
           ,[cnode]
           ,[Код счета БУ]
           ,[Счет БУ]
)
select distinct b.* from (
select '-' id, NULL cnode, T5.Счет_x0020_Дт sh1, T5.Счет_x0020_Дт sh2
from [dbo].[Tab5_oper_1c] t5
WHERE T5.Счет_x0020_Дт NOT IN (SELECT [Код счета БУ] FROM [dbo].[DIM_buhschet])
UNION 
select '-', NULL, T5.Счет_x0020_Кт, T5.Счет_x0020_Кт
from [dbo].[Tab5_oper_1c] t5
WHERE T5.Счет_x0020_Кт NOT IN (SELECT [Код счета БУ] FROM [dbo].[DIM_buhschet])
) b

END


GO

/****** Object:  StoredProcedure [dbo].[Load_Dim_Docs]    Script Date: 09.04.2019 13:07:55 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO






CREATE PROCEDURE [dbo].[Load_Dim_Docs]
AS
BEGIN
TRUNCATE TABLE [dbo].[dim_documents]

INSERT INTO [dbo].[dim_documents]
           ([F$TIDK]
           ,[id_DOC]
           ,[docs])
SELECT DISTINCT TIDK, OB.НомерДокумента, OB.НомерДокумента FROM [dbo].[FACT_OBOROT1C] OB

END

GO

/****** Object:  StoredProcedure [dbo].[Load_Dim_Docstab5]    Script Date: 09.04.2019 13:07:55 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO








CREATE PROCEDURE [dbo].[Load_Dim_Docstab5]
AS
BEGIN
TRUNCATE TABLE [dbo].[dim_documents]

INSERT INTO [dbo].[dim_documents]
           ([F$TIDK]
           ,[id_DOC]
           ,[docs])
SELECT DISTINCT 777, tab5.Документ, tab5.Документ FROM Tab5_oper_1c tab5

END

GO

/****** Object:  StoredProcedure [dbo].[Load_Dim_igk]    Script Date: 09.04.2019 13:07:55 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO






CREATE PROCEDURE [dbo].[Load_Dim_igk]
AS
BEGIN
TRUNCATE TABLE [dbo].[dim_igk]

INSERT INTO [dbo].[DIM_IGK]
           ([id]
           ,[Контрагент]
           ,[NO_DOG]
           ,[IGK]
           ,[DATE_END]
           ,[DATE_DOG]
           ,[N_SCHET]
		   , [Госзаказчик] )
select vv.CSHPZ, isnull(vv.Контрагент,'-'), substring(isnull([НомерКонтракта],'-'),1,100), isnull([ИГК],'-'), [ПлановаяДатаисполненияКонтракта],  [ДатаКонтракта], isnull([Номервыделенногосчета],'-'), ISNULL([Госзаказчик],'-')
from [dbo].[v_igk] vv

update [dbo].[FACT_OBOROT1C] 
set no_dog = (select dim_igk.NO_DOG from dim_igk where dim_igk.id = CSHPZ),
    ORG_NAME = (select dim_igk.Контрагент from dim_igk where dim_igk.id = CSHPZ), 
    NO_DOG_KORR = (select dim_igk.NO_DOG from dim_igk where dim_igk.id = CSHPZ_KORR),   
	ORG_NAME_KORR = (select dim_igk.Контрагент from dim_igk where dim_igk.id = CSHPZ_KORR)

END

GO

/****** Object:  StoredProcedure [dbo].[Load_Dim_Mc]    Script Date: 09.04.2019 13:07:55 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO






CREATE PROCEDURE [dbo].[Load_Dim_Mc]
AS
BEGIN
TRUNCATE TABLE [dbo].[dim_mc]

INSERT INTO [dbo].[dim_mc]
           ([id]
           ,[Номенклатура]
           ,[KGROUPMC])
SELECT DISTINCT CMC, CMC, 0 FROM [dbo].[FACT_OBOROT1C] WHERE CMC IS NOT NULL

END

GO

/****** Object:  StoredProcedure [dbo].[Load_Dim_Org]    Script Date: 09.04.2019 13:07:55 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO








CREATE PROCEDURE [dbo].[Load_Dim_Org]
AS
BEGIN
TRUNCATE TABLE [dbo].[dim_katorg]

INSERT INTO [dbo].[dim_katorg]
           ([id]
           ,[Контрагент])
SELECT DISTINCT Corg, Corg FROM [dbo].[FACT_OBOROT1C] WHERE Corg IS NOT NULL

END


GO

/****** Object:  StoredProcedure [dbo].[Load_Dim_SHPZ]    Script Date: 09.04.2019 13:07:55 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO







-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[Load_Dim_SHPZ]
AS
BEGIN
TRUNCATE TABLE [dbo].[dim_shpz]

INSERT INTO [dbo].[dim_shpz]
           ([id]
           ,[Контрагент]
           ,[ШПЗ]
           ,[NO_DOG], [ИГК])
SELECT DISTINCT  isNull([ШПЗ], '-') id , isNull(T1.Госзаказчик, '-'), isNull([ШПЗ], '-'), isNull(НомерКонтракта,'-'), isNull([ИГК],'-')
FROM [dbo].[FACT_OBOROT1C]
LEFT OUTER JOIN [dbo].[Tab3_reestr_shpz_1C] T3 ON T3.КодВидаНоменклатуры = [ШПЗ]
left outer join [dbo].[Tab1_param_contr_1с] t1 on t1.ВиДоговора = t3.ВидДоговора
END


GO

/****** Object:  StoredProcedure [dbo].[Load_DIM_ZATR]    Script Date: 09.04.2019 13:07:55 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO







CREATE PROCEDURE [dbo].[Load_DIM_ZATR]
AS
BEGIN
TRUNCATE TABLE [dbo].[DIM_ZATR]

INSERT INTO [dbo].[DIM_ZATR]
           ([id]
           ,[Статья затрат]
           ,[Код затрат])
SELECT DISTINCT CSTZATR, CSTZATR, 0 FROM [dbo].[FACT_OBOROT1C] WHERE CSTZATR IS NOT NULL
END


GO

/****** Object:  StoredProcedure [dbo].[LoadObor]    Script Date: 09.04.2019 13:07:55 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO








CREATE PROCEDURE [dbo].[LoadObor] AS
DECLARE @IGK VARCHAR(100)
DECLARE @NO_DOG VARCHAR(100)
DECLARE @FIL VARCHAR(100)
TRUNCATE TABLE FACT_OBOROT1C

INSERT INTO FACT_OBOROT2
SELECT * FROM FACT_OBOROT1C WHERE [TIDK]=887

TRUNCATE TABLE FACT_OBOROT1C

INSERT INTO FACT_OBOROT1C
SELECT * FROM FACT_OBOROT2

INSERT INTO dbo.FACT_OBOROT1C ([OFICENO],
                               [ID_],
                               [НомерДокумента],
                               [id_DOC],
                               [TIDK],
                               [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])
   SELECT 10,
          oper.id_obor,
          OPER.Документ,
          oper.id_doc,
          777 tidk,
          CONVERT (DATETIME, ROUND (CONVERT (FLOAT, oper.[Дата]), 0, 1))
             [DATOB],
          '1C' CPLANSSCH,
          ISNULL (oper.[Счет_x0020_Дт], '-') F$SCHETO,
          oper.[Счет_x0020_Дт] CDEBSS,
          isnull (pschd.[СчетСервиса], '-') CDEB,
          ISNULL (
             DOG_D.ВидДоговора,
             ISNULL (SHPZ_D.ВидДоговора,
                     isnull (RASH_D.ВиДоговора, '-')))
             cSHPZ,
          CASE
             WHEN ISNULL (pschd.[ИдентификацияШПЗ], '-') =
                     '1'
             THEN
                OPER.[Субконто1_x0020_Дт]
             WHEN ISNULL (pschd.[ИдентификацияШПЗ], '-') =
                     '2'
             THEN
                OPER.[Субконто2_x0020_Дт]
             WHEN ISNULL (pschd.[ИдентификацияШПЗ], '-') =
                     '3'
             THEN
                OPER.[Субконто3_x0020_Дт]
             ELSE
                '-'
          END
             [ШПЗ],
          CASE
             WHEN pschd.[ИдентификацияИГК] = '1'
                  AND pschd.Код NOT LIKE '51%'
             THEN
                oper.[Субконто1_x0020_Дт]
             WHEN pschd.[ИдентификацияИГК] = '2'
                  AND pschd.Код NOT LIKE '51%'
             THEN
                oper.[Субконто2_x0020_Дт]
             WHEN pschd.[ИдентификацияИГК] = '3'
                  AND pschd.Код NOT LIKE '51%'
             THEN
                oper.[Субконто1_x0020_Дт]
             ELSE
                '-'
          END
             NO_DOG,
          CASE
             WHEN pschd.[ИдентификацияИГК] = '1'
             THEN
                oper.[Субконто1_x0020_Дт]
             WHEN pschd.[ИдентификацияИГК] = '2'
             THEN
                oper.[Субконто2_x0020_Дт]
             WHEN pschd.[ИдентификацияИГК] = '3'
             THEN
                oper.[Субконто3_x0020_Дт]
             ELSE
                '-'
          END
             ORG_NAME,
          CASE
             WHEN pschd.[ИдентификацияИГК] = '1'
                  AND pschd.Код LIKE '51%'
             THEN
                oper.[Субконто1_x0020_Дт]
             WHEN pschd.[ИдентификацияИГК] = '2'
                  AND pschd.Код LIKE '51%'
             THEN
                oper.[Субконто2_x0020_Дт]
             WHEN pschd.[ИдентификацияИГК] = '3'
                  AND pschd.Код LIKE '51%'
             THEN
                oper.[Субконто3_x0020_Дт]
             ELSE
                '-'
          END
             cPODR,
          CASE
             WHEN pschd.[ИдентификацияКонтрагент] =
                     '1'
             THEN
                oper.[Субконто1_x0020_Дт]
             WHEN pschd.[ИдентификацияКонтрагент] =
                     '2'
             THEN
                oper.[Субконто2_x0020_Дт]
             WHEN pschd.[ИдентификацияКонтрагент] =
                     '3'
             THEN
                oper.[Субконто3_x0020_Дт]
             ELSE
                '-'
          END
             cORG,
          CASE
             WHEN pschd.[ИдентификацияПарт] = '1'
             THEN
                oper.[Субконто1_x0020_Дт]
             WHEN pschd.[ИдентификацияПарт] = '2'
             THEN
                oper.[Субконто2_x0020_Дт]
             WHEN pschd.[ИдентификацияПарт] = '3'
             THEN
                oper.[Субконто3_x0020_Дт]
             ELSE
                '-'
          END
             cParty,
          CASE
             WHEN pschd.[ИдентификацияНоменкл] = '1'
             THEN
                oper.[Субконто1_x0020_Дт]
             WHEN pschd.[ИдентификацияНоменкл] = '2'
             THEN
                oper.[Субконто2_x0020_Дт]
             WHEN pschd.[ИдентификацияНоменкл] = '3'
             THEN
                oper.[Субконто3_x0020_Дт]
             ELSE
                '-'
          END
             cMc,
          CASE
             WHEN pschd.[ИдентификацияСтЗатр] = '1'
             THEN
                oper.[Субконто1_x0020_Дт]
             WHEN pschd.[ИдентификацияСтЗатр] = '2'
             THEN
                oper.[Субконто2_x0020_Дт]
             WHEN pschd.[ИдентификацияСтЗатр] = '3'
             THEN
                oper.[Субконто3_x0020_Дт]
             ELSE
                '-'
          END
             CSTZATR,
          CASE
             WHEN pschd.[Субконто_x0020_1] = 'Сотрудники'
             THEN
                oper.[Субконто1_x0020_Дт]
             WHEN pschd.[Субконто_x0020_2] = 'Сотрудники'
             THEN
                oper.[Субконто2_x0020_Дт]
             WHEN pschd.[Субконто_x0020_3] = 'Сотрудники'
             THEN
                oper.[Субконто3_x0020_Дт]
             ELSE
                '-'
          END
             CPERSON,
          NULL,
          NULL,
          OPER.[Сумма],
          NULL,
          NULL,
          NULL,
          ISNULL (oper.[Счет_x0020_Кт], '-') [F$SCHETK],
          oper.[Счет_x0020_Кт] CCREDSS,
          isnull (pschc.[СчетСервиса], '-') CCRED,
          ISNULL (
             DOG_C.ВидДоговора,
             ISNULL (SHPZ_C.ВидДоговора,
                     isnull (RASH_C.ВиДоговора, '-')))
             cSHPZ_KORR,
          CASE
             WHEN ISNULL (pschc.[ИдентификацияШПЗ], '-') =
                     '1'
             THEN
                OPER.[Субконто1_x0020_Кт]
             WHEN ISNULL (pschc.[ИдентификацияШПЗ], '-') =
                     '2'
             THEN
                OPER.[Субконто2_x0020_Кт]
             WHEN ISNULL (pschc.[ИдентификацияШПЗ], '-') =
                     '3'
             THEN
                OPER.[Субконто3_x0020_Кт]
             ELSE
                '-'
          END
             [ШПЗ_КОР],
          CASE
             WHEN pschc.[ИдентификацияИГК] = '1'
                  AND pschd.Код NOT LIKE '51%'
             THEN
                oper.[Субконто1_x0020_Кт]
             WHEN pschc.[ИдентификацияИГК] = '2'
                  AND pschd.Код NOT LIKE '51%'
             THEN
                oper.[Субконто2_x0020_Кт]
             WHEN pschc.[ИдентификацияИГК] = '3'
                  AND pschd.Код NOT LIKE '51%'
             THEN
                oper.[Субконто1_x0020_Кт]
             ELSE
                '-'
          END
             [NO_DOG_KORR],
          CASE
             WHEN pschc.[ИдентификацияИГК] = '1'
             THEN
                oper.[Субконто1_x0020_Кт]
             WHEN pschc.[ИдентификацияИГК] = '2'
             THEN
                oper.[Субконто2_x0020_Кт]
             WHEN pschc.[ИдентификацияИГК] = '3'
             THEN
                oper.[Субконто3_x0020_Кт]
             ELSE
                '-'
          END
             [ORG_NAME_KORR],
          CASE
             WHEN pschc.[ИдентификацияКонтрагент] =
                     '1'
             THEN
                oper.[Субконто1_x0020_Кт]
             WHEN pschc.[ИдентификацияКонтрагент] =
                     '2'
             THEN
                oper.[Субконто2_x0020_Кт]
             WHEN pschc.[ИдентификацияКонтрагент] =
                     '3'
             THEN
                oper.[Субконто3_x0020_Кт]
             ELSE
                '-'
          END
             cORG_KORR,
          CASE
             WHEN pschc.[ИдентификацияПарт] = '1'
             THEN
                oper.[Субконто1_x0020_Кт]
             WHEN pschc.[ИдентификацияПарт] = '2'
             THEN
                oper.[Субконто2_x0020_Кт]
             WHEN pschc.[ИдентификацияПарт] = '3'
             THEN
                oper.[Субконто3_x0020_Кт]
             ELSE
                '-'
          END
             cParty_CORR,
          CASE
             WHEN pschc.[ИдентификацияНоменкл] = '1'
             THEN
                oper.[Субконто1_x0020_Кт]
             WHEN pschc.[ИдентификацияНоменкл] = '2'
             THEN
                oper.[Субконто2_x0020_Кт]
             WHEN pschc.[ИдентификацияНоменкл] = '3'
             THEN
                oper.[Субконто3_x0020_Кт]
             ELSE
                '-'
          END
             cMc_CORR,
          CASE
             WHEN pschc.[ИдентификацияСтЗатр] = '1'
             THEN
                oper.[Субконто1_x0020_Кт]
             WHEN pschc.[ИдентификацияСтЗатр] = '2'
             THEN
                oper.[Субконто2_x0020_Кт]
             WHEN pschc.[ИдентификацияСтЗатр] = '3'
             THEN
                oper.[Субконто3_x0020_Кт]
             ELSE
                '-'
          END
             CSTZATR_KORR,
          CASE
             WHEN pschc.[Субконто_x0020_1] = 'Сотрудники'
             THEN
                oper.[Субконто1_x0020_Кт]
             WHEN pschc.[Субконто_x0020_2] = 'Сотрудники'
             THEN
                oper.[Субконто2_x0020_Кт]
             WHEN pschc.[Субконто_x0020_3] = 'Сотрудники'
             THEN
                oper.[Субконто3_x0020_Кт]
             ELSE
                '-'
          END
             CPERSON_KORR,
          OPER.[Сумма],
          0,
          1
     FROM [dbo].[Tab5_oper_1c] OPER
          LEFT OUTER JOIN [dbo].[vt4] pschd
             ON pschd.[Код] = oper.[Счет_x0020_Дт]
          LEFT OUTER JOIN [dbo].[vt4] pschc
             ON pschc.[Код] = oper.[Счет_x0020_Кт]
          LEFT OUTER JOIN [dbo].[Tab2_reestr_dogovor_1с] DOG_D
             ON DOG_D.Идентификатор = OPER.KAU2D
          LEFT OUTER JOIN [dbo].[Tab3_reestr_shpz_1c] SHPZ_D
             ON SHPZ_D.[КодВидаНоменклатуры] = OPER.KAU1D
          LEFT OUTER JOIN [dbo].VT7 RASH_D
             ON RASH_D.[РасчетныйСчетИд] = OPER.KAU3D
          LEFT OUTER JOIN [dbo].[Tab2_reestr_dogovor_1с] DOG_C
             ON DOG_C.Идентификатор = OPER.KAU2K
          LEFT OUTER JOIN [dbo].[Tab3_reestr_shpz_1c] SHPZ_C
             ON SHPZ_C.[КодВидаНоменклатуры] = OPER.KAU1K
          LEFT OUTER JOIN [dbo].[VT7] RASH_C
             ON RASH_C.[РасчетныйСчетИд] = OPER.KAU3K

INSERT INTO dbo.FACT_OBOROT1C ([OFICENO],
                               [ID_],
                               [НомерДокумента],
                               [id_DOC],
                               [TIDK],
                               [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])
   SELECT 10,
          oper.id_obor,
          OPER.Документ,
          oper.id_doc,
          777 tidk,
          CONVERT (DATETIME, ROUND (CONVERT (FLOAT, oper.[Дата]), 0, 1))
             [DATOB],
          '1C' CPLANSSCH,
          ISNULL (oper.[Счет_x0020_Кт], '-') F$SCHETO,
          oper.[Счет_x0020_Кт] CDEBSS,
          isnull (pschc.[СчетСервиса], '-') CDEB,
          ISNULL (
             DOG_C.ВидДоговора,
             ISNULL (SHPZ_C.ВидДоговора,
                     isnull (RASH_C.ВиДоговора, '-')))
             cSHPZ,
          CASE
             WHEN ISNULL (pschc.[ИдентификацияШПЗ], '-') =
                     '1'
             THEN
                OPER.[Субконто1_x0020_Кт]
             WHEN ISNULL (pschc.[ИдентификацияШПЗ], '-') =
                     '2'
             THEN
                OPER.[Субконто2_x0020_Кт]
             WHEN ISNULL (pschc.[ИдентификацияШПЗ], '-') =
                     '3'
             THEN
                OPER.[Субконто3_x0020_Кт]
             ELSE
                '-'
          END
             [ШПЗ],
          CASE
             WHEN pschc.[ИдентификацияИГК] = '1'
                  AND pschc.Код NOT LIKE '51%'
             THEN
                oper.[Субконто1_x0020_Кт]
             WHEN pschc.[ИдентификацияИГК] = '2'
                  AND pschc.Код NOT LIKE '51%'
             THEN
                oper.[Субконто2_x0020_Кт]
             WHEN pschc.[ИдентификацияИГК] = '3'
                  AND pschc.Код NOT LIKE '51%'
             THEN
                oper.[Субконто3_x0020_Кт]
             ELSE
                '-'
          END
             NO_DOG,
          CASE
             WHEN pschc.[ИдентификацияИГК] = '1'
             THEN
                oper.[Субконто1_x0020_Кт]
             WHEN pschc.[ИдентификацияИГК] = '2'
             THEN
                oper.[Субконто2_x0020_Кт]
             WHEN pschc.[ИдентификацияИГК] = '3'
             THEN
                oper.[Субконто3_x0020_Кт]
             ELSE
                '-'
          END
             ORG_NAME,
          CASE
             WHEN pschc.[ИдентификацияИГК] = '1'
                  AND pschc.Код LIKE '51%'
             THEN
                oper.[Субконто1_x0020_Кт]
             WHEN pschc.[ИдентификацияИГК] = '2'
                  AND pschc.Код LIKE '51%'
             THEN
                oper.[Субконто2_x0020_Кт]
             WHEN pschc.[ИдентификацияИГК] = '3'
                  AND pschc.Код LIKE '51%'
             THEN
                oper.[Субконто1_x0020_Кт]
             ELSE
                '-'
          END
             cPODR,
          CASE
             WHEN pschc.[ИдентификацияКонтрагент] =
                     '1'
             THEN
                oper.[Субконто1_x0020_Кт]
             WHEN pschc.[ИдентификацияКонтрагент] =
                     '2'
             THEN
                oper.[Субконто2_x0020_Кт]
             WHEN pschc.[ИдентификацияКонтрагент] =
                     '3'
             THEN
                oper.[Субконто3_x0020_Кт]
             ELSE
                '-'
          END
             cORG,
          CASE
             WHEN pschc.[ИдентификацияПарт] = '1'
             THEN
                oper.[Субконто1_x0020_Кт]
             WHEN pschc.[ИдентификацияПарт] = '2'
             THEN
                oper.[Субконто2_x0020_Кт]
             WHEN pschc.[ИдентификацияПарт] = '3'
             THEN
                oper.[Субконто3_x0020_Кт]
             ELSE
                '-'
          END
             cParty,
          CASE
             WHEN pschc.[ИдентификацияНоменкл] = '1'
             THEN
                oper.[Субконто1_x0020_Кт]
             WHEN pschc.[ИдентификацияНоменкл] = '2'
             THEN
                oper.[Субконто2_x0020_Кт]
             WHEN pschc.[ИдентификацияНоменкл] = '3'
             THEN
                oper.[Субконто3_x0020_Кт]
             ELSE
                '-'
          END
             cMc,
          CASE
             WHEN pschc.[ИдентификацияСтЗатр] = '1'
             THEN
                oper.[Субконто1_x0020_Дт]
             WHEN pschc.[ИдентификацияСтЗатр] = '2'
             THEN
                oper.[Субконто2_x0020_Дт]
             WHEN pschc.[ИдентификацияСтЗатр] = '3'
             THEN
                oper.[Субконто3_x0020_Дт]
             ELSE
                '-'
          END
             CSTZATR,
          CASE
             WHEN pschc.[Субконто_x0020_1] = 'Сотрудники'
             THEN
                oper.[Субконто1_x0020_Кт]
             WHEN pschc.[Субконто_x0020_2] = 'Сотрудники'
             THEN
                oper.[Субконто2_x0020_Кт]
             WHEN pschc.[Субконто_x0020_3] = 'Сотрудники'
             THEN
                oper.[Субконто3_x0020_Кт]
             ELSE
                '-'
          END
             CPERSON,
          NULL,
          NULL,
          OPER.[Сумма],
          NULL,
          NULL,
          NULL,
          ISNULL (oper.[Счет_x0020_Дт], '-') [F$SCHETK],
          oper.[Счет_x0020_Дт] CCREDSS,
          isnull (pschd.[СчетСервиса], '-') CCRED,
          ISNULL (
             DOG_D.ВидДоговора,
             ISNULL (SHPZ_D.ВидДоговора,
                     isnull (RASH_D.ВиДоговора, '-')))
             cSHPZ_KORR,
          CASE
             WHEN ISNULL (pschd.[ИдентификацияШПЗ], '-') =
                     '1'
             THEN
                OPER.[Субконто1_x0020_Дт]
             WHEN ISNULL (pschd.[ИдентификацияШПЗ], '-') =
                     '2'
             THEN
                OPER.[Субконто2_x0020_Дт]
             WHEN ISNULL (pschd.[ИдентификацияШПЗ], '-') =
                     '3'
             THEN
                OPER.[Субконто3_x0020_Дт]
             ELSE
                '-'
          END
             [ШПЗ_КОР],
          CASE
             WHEN pschd.[ИдентификацияИГК] = '1'
                  AND pschd.Код NOT LIKE '51%'
             THEN
                oper.[Субконто1_x0020_Дт]
             WHEN pschc.[ИдентификацияИГК] = '2'
                  AND pschd.Код NOT LIKE '51%'
             THEN
                oper.[Субконто2_x0020_Дт]
             WHEN pschc.[ИдентификацияИГК] = '3'
                  AND pschd.Код NOT LIKE '51%'
             THEN
                oper.[Субконто1_x0020_Дт]
             ELSE
                '-'
          END
             [NO_DOG_KORR],
          CASE
             WHEN pschd.[ИдентификацияИГК] = '1'
             THEN
                oper.[Субконто1_x0020_Дт]
             WHEN pschd.[ИдентификацияИГК] = '2'
             THEN
                oper.[Субконто2_x0020_Дт]
             WHEN pschd.[ИдентификацияИГК] = '3'
             THEN
                oper.[Субконто3_x0020_Дт]
             ELSE
                '-'
          END
             [ORG_NAME_KORR],
          CASE
             WHEN pschd.[ИдентификацияКонтрагент] =
                     '1'
             THEN
                oper.[Субконто1_x0020_Дт]
             WHEN pschd.[ИдентификацияКонтрагент] =
                     '2'
             THEN
                oper.[Субконто2_x0020_Дт]
             WHEN pschd.[ИдентификацияКонтрагент] =
                     '3'
             THEN
                oper.[Субконто3_x0020_Дт]
             ELSE
                '-'
          END
             cORG_KORR,
          CASE
             WHEN pschd.[ИдентификацияПарт] = '1'
             THEN
                oper.[Субконто1_x0020_Дт]
             WHEN pschd.[ИдентификацияПарт] = '2'
             THEN
                oper.[Субконто2_x0020_Дт]
             WHEN pschd.[ИдентификацияПарт] = '3'
             THEN
                oper.[Субконто3_x0020_Дт]
             ELSE
                '-'
          END
             cParty_CORR,
          CASE
             WHEN pschd.[ИдентификацияНоменкл] = '1'
             THEN
                oper.[Субконто1_x0020_Дт]
             WHEN pschd.[ИдентификацияНоменкл] = '2'
             THEN
                oper.[Субконто2_x0020_Дт]
             WHEN pschd.[ИдентификацияНоменкл] = '3'
             THEN
                oper.[Субконто3_x0020_Дт]
             ELSE
                '-'
          END
             cMc_CORR,
          CASE
             WHEN pschd.[ИдентификацияСтЗатр] = '1'
             THEN
                oper.[Субконто1_x0020_Дт]
             WHEN pschd.[ИдентификацияСтЗатр] = '2'
             THEN
                oper.[Субконто2_x0020_Дт]
             WHEN pschd.[ИдентификацияСтЗатр] = '3'
             THEN
                oper.[Субконто3_x0020_Дт]
             ELSE
                '-'
          END
             CSTZATR_KORR,
          CASE
             WHEN pschd.[Субконто_x0020_1] = 'Сотрудники'
             THEN
                oper.[Субконто1_x0020_Дт]
             WHEN pschd.[Субконто_x0020_2] = 'Сотрудники'
             THEN
                oper.[Субконто2_x0020_Дт]
             WHEN pschd.[Субконто_x0020_3] = 'Сотрудники'
             THEN
                oper.[Субконто3_x0020_Дт]
             ELSE
                '-'
          END
             CPERSON_KORR,
          0,
          OPER.[Сумма],
          0
     FROM [dbo].[Tab5_oper_1c] OPER
          LEFT OUTER JOIN [dbo].[vt4] pschd
             ON pschd.[Код] = oper.[Счет_x0020_Дт]
          LEFT OUTER JOIN [dbo].[vt4] pschc
             ON pschc.[Код] = oper.[Счет_x0020_Кт]
          LEFT OUTER JOIN [dbo].[Tab2_reestr_dogovor_1с] DOG_D
             ON DOG_D.Идентификатор = OPER.KAU2D
          LEFT OUTER JOIN [dbo].[Tab3_reestr_shpz_1c] SHPZ_D
             ON SHPZ_D.[КодВидаНоменклатуры] = OPER.KAU1D
          LEFT OUTER JOIN [dbo].[VT7] RASH_D
             ON RASH_D.[РасчетныйСчетИд] = OPER.KAU3D
          LEFT OUTER JOIN [dbo].[Tab2_reestr_dogovor_1с] DOG_C
             ON DOG_C.Идентификатор = OPER.KAU2K
          LEFT OUTER JOIN [dbo].[Tab3_reestr_shpz_1c] SHPZ_C
             ON SHPZ_C.[КодВидаНоменклатуры] = OPER.KAU1K
          LEFT OUTER JOIN [dbo].[VT7] RASH_C 
             ON RASH_C.[РасчетныйСчетИд] = OPER.KAU3K



GO

/****** Object:  StoredProcedure [dbo].[LoadPlan]    Script Date: 09.04.2019 13:07:55 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO






CREATE PROCEDURE [dbo].[LoadPlan] AS
DECLARE @IGK VARCHAR(100)
DECLARE @NO_DOG VARCHAR(100)
DECLARE @FIL VARCHAR(100)

DECLARE @CURSOR CURSOR
SET @CURSOR  = CURSOR SCROLL
FOR
SELECT  PPP.[ИГК], PPP.[НомерКонтракта]
  FROM [dbo].[Tab1_param_contr_1с] PPP
OPEN @CURSOR
FETCH NEXT FROM @CURSOR INTO @IGK, @NO_DOG

WHILE @@FETCH_STATUS = 0
BEGIN

SET @FIL = SUBSTRING (@IGK, 1, 50)
          + '_'
          + SUBSTRING (@NO_DOG, 1, 100)

DELETE FROM FACT_OBOROT1C WHERE ID_DOC = @FIL

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])
   SELECT 10,
            SUBSTRING (par.[ИГК], 1, 50)
          + '_'
          + SUBSTRING (par.[Номерконтракта], 1, 150)
          + '_01'
             ID_,
          SUBSTRING (par.[Номерконтракта], 1, 150)
             [НомерДокумента],
          888 TIDK,
            SUBSTRING (par.[ИГК], 1, 50)
          + '_'
          + SUBSTRING (par.[Номерконтракта], 1, 150)
             [id_DOC],
          PAR.[ДатаКонтракта]
             [DATOB],
          '1C' CPLANSSCH,
          '888.01' F$SCHETO,
          '888.01' CDEBSS,
          '0_888.01' CDEB,
          par.[ВиДоговора] CSHPZ,
          '-'  [ШПЗ],
          SUBSTRING (par.[Номерконтракта], 1, 150) NO_DOG,
          SUBSTRING (par.[Контрагент], 1, 40) ORG_NAME,
          '-',
          '-',
          '-',
          '-',
          '-',
          '-',
          NULL,
          NULL,
          NULL,
          NULL,
          NULL,
          NULL,
          '-',
          '-',
          '-',
          '-',
          '-',
          '-',
          '-',
          '-',
          '-',
          '-',
          '-',
          '-',
          0,
          0,
          1, SUM_IGK
     FROM Tab1_param_contr_1с par
     WHERE par.[ИГК] = @IGK AND par.[Номерконтракта] = @NO_DOG
--- 2     
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])
   SELECT 10,
            SUBSTRING (par.[ИГК], 1, 50)
          + '_'
          + SUBSTRING (par.[Номерконтракта], 1, 150)
          + '_02'
             ID_,
          SUBSTRING (par.[Номерконтракта], 1, 150)
             [НомерДокумента],
          888 TIDK,
            SUBSTRING (par.[ИГК], 1, 50)
          + '_'
          + SUBSTRING (par.[Номерконтракта], 1, 150)
             [id_DOC],
          PAR.[ДатаКонтракта]
             [DATOB],
          '1C' CPLANSSCH,
          '888.02' F$SCHETO,
          '888.02' CDEBSS,
          '0_888.02' CDEB,
          par.[ВиДоговора] CSHPZ,
          '-'  [ШПЗ],
          SUBSTRING (par.[Номерконтракта], 1, 150) NO_DOG,
          SUBSTRING (par.[Контрагент], 1, 40) ORG_NAME,
          '-',
          '-',
          '-',
          '-',
          '-',
          '-',
          NULL,
          NULL,
          NULL,
          NULL,
          NULL,
          NULL,
          '-',
          '-',
          '-',
          '-',
          '-',
          '-',
          '-',
          '-',
          '-',
          '-',
          '-',
          '-',
          0,
          0,
          1, Sum1
     FROM Tab1_param_contr_1с par
     WHERE par.[ИГК] = @IGK AND par.[Номерконтракта] = @NO_DOG
--- 3     
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])
   SELECT 10,
            SUBSTRING (par.[ИГК], 1, 50)
          + '_'
          + SUBSTRING (par.[Номерконтракта], 1, 150)
          + '_03'
             ID_,
          SUBSTRING (par.[Номерконтракта], 1, 150)
             [НомерДокумента],
          888 TIDK,
            SUBSTRING (par.[ИГК], 1, 50)
          + '_'
          + SUBSTRING (par.[Номерконтракта], 1, 150)
             [id_DOC],
          PAR.[ДатаКонтракта]
             [DATOB],
          '1C' CPLANSSCH,
          '888.03' F$SCHETO,
          '888.03' CDEBSS,
          '0_888.03' CDEB,
          par.[ВиДоговора] CSHPZ,
          '-'  [ШПЗ],
          SUBSTRING (par.[Номерконтракта], 1, 150) NO_DOG,
          SUBSTRING (par.[Контрагент], 1, 40) ORG_NAME,
          '-',
          '-',
          '-',
          '-',
          '-',
          '-',
          NULL,
          NULL,
          NULL,
          NULL,
          NULL,
          NULL,
          '-',
          '-',
          '-',
          '-',
          '-',
          '-',
          '-',
          '-',
          '-',
          '-',
          '-',
          '-',
          0,
          0,
          1, Sum2
     FROM Tab1_param_contr_1с  par
     WHERE par.[ИГК] = @IGK AND par.[Номерконтракта] = @NO_DOG
--- 4     
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])
   SELECT 10,
            SUBSTRING (par.[ИГК], 1, 50)
          + '_'
          + SUBSTRING (par.[Номерконтракта], 1, 150)
          + '_04'
             ID_,
          SUBSTRING (par.[Номерконтракта], 1, 150)
             [НомерДокумента],
          888 TIDK,
            SUBSTRING (par.[ИГК], 1, 50)
          + '_'
          + SUBSTRING (par.[Номерконтракта], 1, 150)
             [id_DOC],
          PAR.[ДатаКонтракта]
             [DATOB],
          '1C' CPLANSSCH,
          '888.04' F$SCHETO,
          '888.04' CDEBSS,
          '0_888.04' CDEB,
          par.[ВиДоговора] CSHPZ,
          '-'  [ШПЗ],
          SUBSTRING (par.[Номерконтракта], 1, 150) NO_DOG,
          SUBSTRING (par.[Контрагент], 1, 40) ORG_NAME,
          '-',
          '-',
          '-',
          '-',
          '-',
          '-',
          NULL,
          NULL,
          NULL,
          NULL,
          NULL,
          NULL,
          '-',
          '-',
          '-',
          '-',
          '-',
          '-',
          '-',
          '-',
          '-',
          '-',
          '-',
          '-',
          0,
          0,
          1, Sum3
     FROM Tab1_param_contr_1с  par
     WHERE par.[ИГК] = @IGK AND par.[Номерконтракта] = @NO_DOG
     
--- 5     
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])
   SELECT 10,
            SUBSTRING (par.[ИГК], 1, 50)
          + '_'
          + SUBSTRING (par.[Номерконтракта], 1, 150)
          + '_05'
             ID_,
          SUBSTRING (par.[Номерконтракта], 1, 150)
             [НомерДокумента],
          888 TIDK,
            SUBSTRING (par.[ИГК], 1, 50)
          + '_'
          + SUBSTRING (par.[Номерконтракта], 1, 150)
             [id_DOC],
          PAR.[ДатаКонтракта]
             [DATOB],
          '1C' CPLANSSCH,
          '888.05' F$SCHETO,
          '888.05' CDEBSS,
          '0_888.05' CDEB,
          par.[ВиДоговора] CSHPZ,
          '-'  [ШПЗ],
          SUBSTRING (par.[Номерконтракта], 1, 150) NO_DOG,
          SUBSTRING (par.[Контрагент], 1, 40) ORG_NAME,
          '-',
          '-',
          '-',
          '-',
          '-',
          '-',
          NULL,
          NULL,
          NULL,
          NULL,
          NULL,
          NULL,
          '-',
          '-',
          '-',
          '-',
          '-',
          '-',
          '-',
          '-',
          '-',
          '-',
          '-',
          '-',
          0,
          0,
          1, Sum4
     FROM Tab1_param_contr_1с  par
     WHERE par.[ИГК] = @IGK AND par.[Номерконтракта] = @NO_DOG
--- 6     
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])
   SELECT 10,
            SUBSTRING (par.[ИГК], 1, 50)
          + '_'
          + SUBSTRING (par.[Номерконтракта], 1, 150)
          + '_06'
             ID_,
          SUBSTRING (par.[Номерконтракта], 1, 150)
             [НомерДокумента],
          888 TIDK,
            SUBSTRING (par.[ИГК], 1, 50)
          + '_'
          + SUBSTRING (par.[Номерконтракта], 1, 150)
             [id_DOC],
          PAR.[ДатаКонтракта]
             [DATOB],
          '1C' CPLANSSCH,
          '888.06' F$SCHETO,
          '888.06' CDEBSS,
          '0_888.06' CDEB,
          par.[ВиДоговора] CSHPZ,
          '-'  [ШПЗ],
          SUBSTRING (par.[Номерконтракта], 1, 150) NO_DOG,
          SUBSTRING (par.[Контрагент], 1, 40) ORG_NAME,
          '-',
          '-',
          '-',
          '-',
          '-',
          '-',
          NULL,
          NULL,
          NULL,
          NULL,
          NULL,
          NULL,
          '-',
          '-',
          '-',
          '-',
          '-',
          '-',
          '-',
          '-',
          '-',
          '-',
          '-',
          '-',
          0,
          0,
          1, Sum5
     FROM Tab1_param_contr_1с  par
     WHERE par.[ИГК] = @IGK AND par.[Номерконтракта] = @NO_DOG
--- 7     
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])
   SELECT 10,
            SUBSTRING (par.[ИГК], 1, 50)
          + '_'
          + SUBSTRING (par.[Номерконтракта], 1, 150)
          + '_07'
             ID_,
          SUBSTRING (par.[Номерконтракта], 1, 150)
             [НомерДокумента],
          888 TIDK,
            SUBSTRING (par.[ИГК], 1, 50)
          + '_'
          + SUBSTRING (par.[Номерконтракта], 1, 150)
             [id_DOC],
          PAR.[ДатаКонтракта]
             [DATOB],
          '1C' CPLANSSCH,
          '888.07' F$SCHETO,
          '888.07' CDEBSS,
          '0_888.07' CDEB,
          par.[ВиДоговора] CSHPZ,
          '-'  [ШПЗ],
          SUBSTRING (par.[Номерконтракта], 1, 150) NO_DOG,
          SUBSTRING (par.[Контрагент], 1, 40) ORG_NAME,
          '-',
          '-',
          '-',
          '-',
          '-',
          '-',
          NULL,
          NULL,
          NULL,
          NULL,
          NULL,
          NULL,
          '-',
          '-',
          '-',
          '-',
          '-',
          '-',
          '-',
          '-',
          '-',
          '-',
          '-',
          '-',
          0,
          0,
          1, Sum6
     FROM Tab1_param_contr_1с  par
     WHERE par.[ИГК] = @IGK AND par.[Номерконтракта] = @NO_DOG
 
--- 8     
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])
   SELECT 10,
            SUBSTRING (par.[ИГК], 1, 50)
          + '_'
          + SUBSTRING (par.[Номерконтракта], 1, 150)
          + '_08'
             ID_,
          SUBSTRING (par.[Номерконтракта], 1, 150)
             [НомерДокумента],
          888 TIDK,
            SUBSTRING (par.[ИГК], 1, 50)
          + '_'
          + SUBSTRING (par.[Номерконтракта], 1, 150)
             [id_DOC],
          PAR.[ДатаКонтракта]
             [DATOB],
          '1C' CPLANSSCH,
          '888.08' F$SCHETO,
          '888.08' CDEBSS,
          '0_888.08' CDEB,
          par.[ВиДоговора] CSHPZ,
          '-'  [ШПЗ],
          SUBSTRING (par.[Номерконтракта], 1, 150) NO_DOG,
          SUBSTRING (par.[Контрагент], 1, 40) ORG_NAME,
          '-',
          '-',
          '-',
          '-',
          '-',
          '-',
          NULL,
          NULL,
          NULL,
          NULL,
          NULL,
          NULL,
          '-',
          '-',
          '-',
          '-',
          '-',
          '-',
          '-',
          '-',
          '-',
          '-',
          '-',
          '-',
          0,
          0,
          1, Sum7
     FROM Tab1_param_contr_1с  par
     WHERE par.[ИГК] = @IGK AND par.[Номерконтракта] = @NO_DOG
                                                                                                                                                                                    
--- 9     
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])
   SELECT 10,
            SUBSTRING (par.[ИГК], 1, 50)
          + '_'
          + SUBSTRING (par.[Номерконтракта], 1, 150)
          + '_09'
             ID_,
          SUBSTRING (par.[Номерконтракта], 1, 150)
             [НомерДокумента],
          888 TIDK,
            SUBSTRING (par.[ИГК], 1, 50)
          + '_'
          + SUBSTRING (par.[Номерконтракта], 1, 150)
             [id_DOC],
          PAR.[ДатаКонтракта]
             [DATOB],
          '1C' CPLANSSCH,
          '888.09' F$SCHETO,
          '888.09' CDEBSS,
          '0_888.09' CDEB,
          par.[ВиДоговора] CSHPZ,
          '-'  [ШПЗ],
          SUBSTRING (par.[Номерконтракта], 1, 150) NO_DOG,
          SUBSTRING (par.[Контрагент], 1, 40) ORG_NAME,
          '-',
          '-',
          '-',
          '-',
          '-',
          '-',
          NULL,
          NULL,
          NULL,
          NULL,
          NULL,
          NULL,
          '-',
          '-',
          '-',
          '-',
          '-',
          '-',
          '-',
          '-',
          '-',
          '-',
          '-',
          '-',
          0,
          0,
          1, Sum8
     FROM Tab1_param_contr_1с  par
     WHERE par.[ИГК] = @IGK AND par.[Номерконтракта] = @NO_DOG
 
 
--- 10
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])
   SELECT 10,
            SUBSTRING (par.[ИГК], 1, 50)
          + '_'
          + SUBSTRING (par.[Номерконтракта], 1, 150)
          + '_10'
             ID_,
          SUBSTRING (par.[Номерконтракта], 1, 150)
             [НомерДокумента],
          888 TIDK,
            SUBSTRING (par.[ИГК], 1, 50)
          + '_'
          + SUBSTRING (par.[Номерконтракта], 1, 150)
             [id_DOC],
          PAR.[ДатаКонтракта]
             [DATOB],
          '1C' CPLANSSCH,
          '888.10' F$SCHETO,
          '888.10' CDEBSS,
          '0_888.10' CDEB,
          par.[ВиДоговора] CSHPZ,
          '-'  [ШПЗ],
          SUBSTRING (par.[Номерконтракта], 1, 150) NO_DOG,
          SUBSTRING (par.[Контрагент], 1, 40) ORG_NAME,
          '-',
          '-',
          '-',
          '-',
          '-',
          '-',
          NULL,
          NULL,
          NULL,
          NULL,
          NULL,
          NULL,
          '-',
          '-',
          '-',
          '-',
          '-',
          '-',
          '-',
          '-',
          '-',
          '-',
          '-',
          '-',
          0,
          0,
          1, Sum9
     FROM Tab1_param_contr_1с  par
     WHERE par.[ИГК] = @IGK AND par.[Номерконтракта] = @NO_DOG
      
--- 1Q
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])
   SELECT 10,
            SUBSTRING (par.[ИГК], 1, 50)
          + '_'
          + SUBSTRING (par.[Номерконтракта], 1, 150)
          + '_11'
             ID_,
          SUBSTRING (par.[Номерконтракта], 1, 150)
             [НомерДокумента],
          888 TIDK,
            SUBSTRING (par.[ИГК], 1, 50)
          + '_'
          + SUBSTRING (par.[Номерконтракта], 1, 150)
             [id_DOC],
          PAR.[ДатаКонтракта]
             [DATOB],
          '1C' CPLANSSCH,
          '888.11' F$SCHETO,
          '888.11' CDEBSS,
          '0_888.11' CDEB,
          par.[ВиДоговора] CSHPZ,
          '-'  [ШПЗ],
          SUBSTRING (par.[Номерконтракта], 1, 150) NO_DOG,
          SUBSTRING (par.[Контрагент], 1, 40) ORG_NAME,
          '-',
          '-',
          '-',
          '-',
          '-',
          '-',
          NULL,
          NULL,
          NULL,
          NULL,
          NULL,
          NULL,
          '-',
          '-',
          '-',
          '-',
          '-',
          '-',
          '-',
          '-',
          '-',
          '-',
          '-',
          '-',
          0,
          0,
          1, Sum10
     FROM Tab1_param_contr_1с  par
     WHERE par.[ИГК] = @IGK AND par.[Номерконтракта] = @NO_DOG
--- 11
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])
   SELECT 10,
            SUBSTRING (par.[ИГК], 1, 50)
          + '_'
          + SUBSTRING (par.[Номерконтракта], 1, 150)
          + '_12'
             ID_,
          SUBSTRING (par.[Номерконтракта], 1, 150)
             [НомерДокумента],
          888 TIDK,
            SUBSTRING (par.[ИГК], 1, 50)
          + '_'
          + SUBSTRING (par.[Номерконтракта], 1, 150)
             [id_DOC],
          PAR.[ДатаКонтракта]
             [DATOB],
          '1C' CPLANSSCH,
          '888.12' F$SCHETO,
          '888.12' CDEBSS,
          '0_888.12' CDEB,
          par.[ВиДоговора] CSHPZ,
          '-'  [ШПЗ],
          SUBSTRING (par.[Номерконтракта], 1, 150) NO_DOG,
          SUBSTRING (par.[Контрагент], 1, 40) ORG_NAME,
          '-',
          '-',
          '-',
          '-',
          '-',
          '-',
          NULL,
          NULL,
          NULL,
          NULL,
          NULL,
          NULL,
          '-',
          '-',
          '-',
          '-',
          '-',
          '-',
          '-',
          '-',
          '-',
          '-',
          '-',
          '-',
          0,
          0,
          1, Sum11
     FROM Tab1_param_contr_1с  par
     WHERE par.[ИГК] = @IGK AND par.[Номерконтракта] = @NO_DOG

--- 12
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])
   SELECT 10,
            SUBSTRING (par.[ИГК], 1, 50)
          + '_'
          + SUBSTRING (par.[Номерконтракта], 1, 150)
          + '_13'
             ID_,
          SUBSTRING (par.[Номерконтракта], 1, 150)
             [НомерДокумента],
          888 TIDK,
            SUBSTRING (par.[ИГК], 1, 50)
          + '_'
          + SUBSTRING (par.[Номерконтракта], 1, 150)
             [id_DOC],
          PAR.[ДатаКонтракта]
             [DATOB],
          '1C' CPLANSSCH,
          '888.13' F$SCHETO,
          '888.13' CDEBSS,
          '0_888.13' CDEB,
          par.[ВиДоговора] CSHPZ,
          '-'  [ШПЗ],
          SUBSTRING (par.[Номерконтракта], 1, 150) NO_DOG,
          SUBSTRING (par.[Контрагент], 1, 40) ORG_NAME,
          '-',
          '-',
          '-',
          '-',
          '-',
          '-',
          NULL,
          NULL,
          NULL,
          NULL,
          NULL,
          NULL,
          '-',
          '-',
          '-',
          '-',
          '-',
          '-',
          '-',
          '-',
          '-',
          '-',
          '-',
          '-',
          0,
          0,
          1, Sum12
     FROM Tab1_param_contr_1с  par
     WHERE par.[ИГК] = @IGK AND par.[Номерконтракта] = @NO_DOG
--- Сумма по 29
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])
   SELECT 10,
            SUBSTRING (par.[ИГК], 1, 50)
          + '_'
          + SUBSTRING (par.[Номерконтракта], 1, 150)
          + '_14'
             ID_,
          SUBSTRING (par.[Номерконтракта], 1, 150)
             [НомерДокумента],
          888 TIDK,
            SUBSTRING (par.[ИГК], 1, 50)
          + '_'
          + SUBSTRING (par.[Номерконтракта], 1, 150)
             [id_DOC],
          PAR.[ДатаКонтракта]
             [DATOB],
          '1C' CPLANSSCH,
          '888.14' F$SCHETO,
          '888.14' CDEBSS,
          '0_888.14' CDEB,
          par.[ВиДоговора] CSHPZ,
          '-'  [ШПЗ],
          SUBSTRING (par.[Номерконтракта], 1, 150) NO_DOG,
          SUBSTRING (par.[Контрагент], 1, 40) ORG_NAME,
          '-',
          '-',
          '-',
          '-',
          '-',
          '-',
          NULL,
          NULL,
          NULL,
          NULL,
          NULL,
          NULL,
          '-',
          '-',
          '-',
          '-',
          '-',
          '-',
          '-',
          '-',
          '-',
          '-',
          '-',
          '-',
          0,
          0,
          1, Sum2+Sum3+Sum4+Sum5+Sum6
     FROM Tab1_param_contr_1с  par
     WHERE par.[ИГК] = @IGK AND par.[Номерконтракта] = @NO_DOG

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


/*
UPDATE FACT_OBOROT1C
SET LINE =  dbo.mart_Get543(isDeb, F$SCHETO, SUBSTRING(CDEB, 1, CASE WHEN LEN(CDEB) IS NULL THEN 1 WHEN LEN(CDEB) < 2 THEN 1 ELSE LEN(CDEB) - 1 END), F$SCHETK, CSTZATR)

UPDATE [dbo].[FACT_OBOROT1C] 
set line_txt = (SELECT no_str FROM [dbo].[rep_shbl] WHERE id = line)
GO

*/
GO

/****** Object:  StoredProcedure [dbo].[LOADTAB5]    Script Date: 09.04.2019 13:07:55 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[LOADTAB5] 
@count   INT
AS
DECLARE @hDoc   INT
DECLARE @XML   XML
DECLARE @k   INT = 1

TRUNCATE TABLE Tab5_temp

WHILE @k < @count 
BEGIN
   IF @k = 1
      BEGIN
         TRUNCATE TABLE XMLwithOpenXML

         INSERT INTO XMLwithOpenXML (XMLData, LoadedDateTime)
            SELECT CONVERT (XML, BulkColumn) AS BulkColumn, GETDATE ()
              FROM OPENROWSET (BULK 'C:/EMART_RU/tab5_provodki-1.xml',
                               SINGLE_BLOB) AS x
      END

   IF @k = 2
      BEGIN
         TRUNCATE TABLE XMLwithOpenXML

         INSERT INTO XMLwithOpenXML (XMLData, LoadedDateTime)
            SELECT CONVERT (XML, BulkColumn) AS BulkColumn, GETDATE ()
              FROM OPENROWSET (BULK 'C:/EMART_RU/tab5_provodki-2.xml',
                               SINGLE_BLOB) AS x
      END

   IF @k = 3
      BEGIN
         TRUNCATE TABLE XMLwithOpenXML

         INSERT INTO XMLwithOpenXML (XMLData, LoadedDateTime)
            SELECT CONVERT (XML, BulkColumn) AS BulkColumn, GETDATE ()
              FROM OPENROWSET (BULK 'C:/EMART_RU/tab5_provodki-3.xml',
                               SINGLE_BLOB) AS x
      END

   IF @k = 4
      BEGIN
         TRUNCATE TABLE XMLwithOpenXML

         INSERT INTO XMLwithOpenXML (XMLData, LoadedDateTime)
            SELECT CONVERT (XML, BulkColumn) AS BulkColumn, GETDATE ()
              FROM OPENROWSET (BULK 'C:/EMART_RU/tab5_provodki-4.xml',
                               SINGLE_BLOB) AS x
      END

   IF @k = 5
      BEGIN
         TRUNCATE TABLE XMLwithOpenXML

         INSERT INTO XMLwithOpenXML (XMLData, LoadedDateTime)
            SELECT CONVERT (XML, BulkColumn) AS BulkColumn, GETDATE ()
              FROM OPENROWSET (BULK 'C:/EMART_RU/tab5_provodki-5.xml',
                               SINGLE_BLOB) AS x
      END

   IF @k = 6
      BEGIN
         TRUNCATE TABLE XMLwithOpenXML

         INSERT INTO XMLwithOpenXML (XMLData, LoadedDateTime)
            SELECT CONVERT (XML, BulkColumn) AS BulkColumn, GETDATE ()
              FROM OPENROWSET (BULK 'C:/EMART_RU/tab5_provodki-6.xml',
                               SINGLE_BLOB) AS x
      END

   IF @k = 7
      BEGIN
         TRUNCATE TABLE XMLwithOpenXML

         INSERT INTO XMLwithOpenXML (XMLData, LoadedDateTime)
            SELECT CONVERT (XML, BulkColumn) AS BulkColumn, GETDATE ()
              FROM OPENROWSET (BULK 'C:/EMART_RU/tab5_provodki-7.xml',
                               SINGLE_BLOB) AS x
      END

   IF @k = 8
      BEGIN
         TRUNCATE TABLE XMLwithOpenXML

         INSERT INTO XMLwithOpenXML (XMLData, LoadedDateTime)
            SELECT CONVERT (XML, BulkColumn) AS BulkColumn, GETDATE ()
              FROM OPENROWSET (BULK 'C:/EMART_RU/tab5_provodki-8.xml',
                               SINGLE_BLOB) AS x
      END

   IF @k = 9
      BEGIN
         TRUNCATE TABLE XMLwithOpenXML

         INSERT INTO XMLwithOpenXML (XMLData, LoadedDateTime)
            SELECT CONVERT (XML, BulkColumn) AS BulkColumn, GETDATE ()
              FROM OPENROWSET (BULK 'C:/EMART_RU/tab5_provodki-9.xml',
                               SINGLE_BLOB) AS x
      END

   IF @k = 10
      BEGIN
         TRUNCATE TABLE XMLwithOpenXML

         INSERT INTO XMLwithOpenXML (XMLData, LoadedDateTime)
            SELECT CONVERT (XML, BulkColumn) AS BulkColumn, GETDATE ()
              FROM OPENROWSET (BULK 'C:/EMART_RU/tab5_provodki-10.xml',
                               SINGLE_BLOB) AS x
      END

   IF @k = 11
      BEGIN
         TRUNCATE TABLE XMLwithOpenXML

         INSERT INTO XMLwithOpenXML (XMLData, LoadedDateTime)
            SELECT CONVERT (XML, BulkColumn) AS BulkColumn, GETDATE ()
              FROM OPENROWSET (BULK 'C:/EMART_RU/tab5_provodki-11.xml',
                               SINGLE_BLOB) AS x
      END

   IF @k = 12
      BEGIN
         TRUNCATE TABLE XMLwithOpenXML

         INSERT INTO XMLwithOpenXML (XMLData, LoadedDateTime)
            SELECT CONVERT (XML, BulkColumn) AS BulkColumn, GETDATE ()
              FROM OPENROWSET (BULK 'C:/EMART_RU/tab5_provodki-12.xml',
                               SINGLE_BLOB) AS x
      END

   IF @k = 13
      BEGIN
         TRUNCATE TABLE XMLwithOpenXML

         INSERT INTO XMLwithOpenXML (XMLData, LoadedDateTime)
            SELECT CONVERT (XML, BulkColumn) AS BulkColumn, GETDATE ()
              FROM OPENROWSET (BULK 'C:/EMART_RU/tab5_provodki-13.xml',
                               SINGLE_BLOB) AS x
      END

   IF @k = 14
      BEGIN
         TRUNCATE TABLE XMLwithOpenXML

         INSERT INTO XMLwithOpenXML (XMLData, LoadedDateTime)
            SELECT CONVERT (XML, BulkColumn) AS BulkColumn, GETDATE ()
              FROM OPENROWSET (BULK 'C:/EMART_RU/tab5_provodki-14.xml',
                               SINGLE_BLOB) AS x
      END

   IF @k = 15
      BEGIN
         TRUNCATE TABLE XMLwithOpenXML

         INSERT INTO XMLwithOpenXML (XMLData, LoadedDateTime)
            SELECT CONVERT (XML, BulkColumn) AS BulkColumn, GETDATE ()
              FROM OPENROWSET (BULK 'C:/EMART_RU/tab5_provodki-15.xml',
                               SINGLE_BLOB) AS x
      END

   IF @k = 16
      BEGIN
         TRUNCATE TABLE XMLwithOpenXML

         INSERT INTO XMLwithOpenXML (XMLData, LoadedDateTime)
            SELECT CONVERT (XML, BulkColumn) AS BulkColumn, GETDATE ()
              FROM OPENROWSET (BULK 'C:/EMART_RU/tab5_provodki-16.xml',
                               SINGLE_BLOB) AS x
      END

   IF @k = 17
      BEGIN
         TRUNCATE TABLE XMLwithOpenXML

         INSERT INTO XMLwithOpenXML (XMLData, LoadedDateTime)
            SELECT CONVERT (XML, BulkColumn) AS BulkColumn, GETDATE ()
              FROM OPENROWSET (BULK 'C:/EMART_RU/tab5_provodki-17.xml',
                               SINGLE_BLOB) AS x
      END

   IF @k = 18
      BEGIN
         TRUNCATE TABLE XMLwithOpenXML

         INSERT INTO XMLwithOpenXML (XMLData, LoadedDateTime)
            SELECT CONVERT (XML, BulkColumn) AS BulkColumn, GETDATE ()
              FROM OPENROWSET (BULK 'C:/EMART_RU/tab5_provodki-18.xml',
                               SINGLE_BLOB) AS x
      END

   IF @k = 19
      BEGIN
         TRUNCATE TABLE XMLwithOpenXML

         INSERT INTO XMLwithOpenXML (XMLData, LoadedDateTime)
            SELECT CONVERT (XML, BulkColumn) AS BulkColumn, GETDATE ()
              FROM OPENROWSET (BULK 'C:/EMART_RU/tab5_provodki-19.xml',
                               SINGLE_BLOB) AS x
      END

   IF @k = 20
      BEGIN
         TRUNCATE TABLE XMLwithOpenXML

         INSERT INTO XMLwithOpenXML (XMLData, LoadedDateTime)
            SELECT CONVERT (XML, BulkColumn) AS BulkColumn, GETDATE ()
              FROM OPENROWSET (BULK 'C:/EMART_RU/tab5_provodki-20.xml',
                               SINGLE_BLOB) AS x
      END


   SELECT @XML = XMLData FROM XMLwithOpenXML

   EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML

   INSERT INTO [dbo].[Tab5_temp] ([id_obor],
                                  [id_doc],
                                  [KAU1D],
                                  [Документ],
                                  [СчетДт],
                                  [Субконто1Дт],
                                  [Субконто2Дт],
                                  [Субконто3Дт],
                                  [СчетКт],
                                  [Субконто1Кт],
                                  [Субконто2Кт],
                                  [Субконто3Кт],
                                  [KAU1K],
                                  [Содержание])
      SELECT *
        FROM OPENXML (@hDoc, N'/dataroot/Tab5_provodki', 2)
                WITH (id_obor[nvarchar] (255),
                      id_doc[nvarchar] (255),
                      [Дата][nvarchar] (255),
                      [Документ][nvarchar] (255),
                      [СчетДт][nvarchar] (255),
                      [Субконто1Дт][nvarchar] (255),
                      [Субконто2Дт][nvarchar] (255),
                      [Субконто3Дт][nvarchar] (255),
                      [СчетКт][nvarchar] (255),
                      [Субконто1Кт][nvarchar] (255),
                      [Субконто2Кт][nvarchar] (255),
                      [Субконто3Кт][nvarchar] (255),
                      [Сумма][nvarchar] (255),
                      [Содержание][nvarchar] (255))

   EXEC sp_xml_removedocument @hDoc

   SET @k = @k + 1
END

UPDATE Tab5_temp 
SET [Дата] = [KAU1D], [Сумма]=[KAU1K]
GO

/****** Object:  StoredProcedure [dbo].[MAKE_REP_XML]    Script Date: 09.04.2019 13:07:55 ******/
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+03: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_xmlLog]    Script Date: 09.04.2019 13:07:55 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE procedure [dbo].[make_xmlLog]
as
declare @str_out varchar(MAX)
declare @str_tmp varchar(MAX)
DECLARE @FIL VARCHAR(100)
set @str_out= 'Протокол загрузки исходных данных' + char(13)


DECLARE @CURSOR CURSOR
SET @CURSOR  = CURSOR SCROLL
FOR
SELECT * FROM (
SELECT '1  OШИБКИ ИСХОДНЫХ ДАННЫХ' MEES
union 
SELECT '3  ПРЕДУПРЕЖДЕНИЯ И СООБЩЕНИЯ' 
union 
SELECT '1 Tab1: Неуникальный код контракта (ВидДоговора): ' +  t1.ВиДоговора + '(' +convert(nvarchar,count(*)) + ')'  MEES FROM Tab1_param_contr_1с T1 group by t1.ВиДоговора having count(*) > 1 
union 
SELECT '1 Tab1: Неуникальный номер контракта: ' + t1.НомерКонтракта + '(' +convert(nvarchar,count(*)) + ')'  FROM Tab1_param_contr_1с T1 group by t1.НомерКонтракта having count(*) > 1
union 
SELECT '1 Tab1: Неуникальный номер отдельного счета контракта: ' + t1.Номервыделенногосчета+ '(' +convert(nvarchar,count(*)) + ')'  FROM Tab1_param_contr_1с T1 group by t1.Номервыделенногосчета having count(*) > 1
union 
SELECT '1 Tab1: Не опредены плановые показатели у Госконтракта № ' + t1.НомерКонтракта FROM Tab1_param_contr_1с T1 where t1.Sum_IGK is Null or t1.Sum2 is Null or t1.Sum3 is Null or t1.Sum4 is Null or t1.Sum5 is Null or t1.Sum6 is Null or t1.Sum7 is Null or t1.Sum8 is Null or t1.Sum9+t1.Sum10 is Null or t1.Sum11 is Null or t1.Sum12  is Null 
union
SELECT '1 Tab2: Неуникальный идентификатор договора: ' + t2.Идентификатор + '(' +convert(nvarchar,count(*)) + ')'  FROM Tab2_reestr_dogovor_1с t2 group by t2.Идентификатор having count(*) > 1
union
SELECT '1 Tab3: Неуникальный код ШПЗ: ' + t3.КодВидаНоменклатуры + '(' +convert(nvarchar,count(*)) + ')' FROM Tab3_reestr_shpz_1C t3 group by t3.КодВидаНоменклатуры having count(*) > 1
union
SELECT '1 Tab6: Неуникальный код статьи затрат: ' + t6.Наименование + '(' +convert(nvarchar,count(*)) + ')'  FROM Tab6_zatraty_20_1c t6 group by t6.Наименование having count(*) > 1
union 
select '3_Tab1: импортировано записей :' + convert(varchar,count(*)) from Tab1_param_contr_1с
union
select '3_Tab2: импортировано записей :' + convert(varchar,count(*)) from Tab2_reestr_dogovor_1с
union
select '3_Tab3: импортировано записей :' + convert(varchar,count(*)) from Tab3_reestr_shpz_1C
union
select '3_Tab4: импортировано записей :' + convert(varchar,count(*)) from Tab4_plan_1c
union
select '3_Tab5: импортировано записей :' + convert(varchar,count(*)) from Tab5_oper_1c
union
select '3_Tab6: импортировано записей :' + convert(varchar,count(*)) from Tab6_zatraty_20_1c
union
select '3_Tab7: импортировано записей :' + convert(varchar,count(*)) from tab7_schet_1c
union
SELECT '1 Tab1: Госконтракт не имеет привязанных договоров в реестре договоров (Tab2): ' + t1.НомерКонтракта 
FROM Tab1_param_contr_1с T1 
left outer join Tab2_reestr_dogovor_1с t2 on t2.ВидДоговора = t1.ВиДоговора
where t2.ВидДоговора  is null 
union
SELECT '1 Tab1: У Госконтракта нет ШПЗ в Tab3: ' + t1.НомерКонтракта 
FROM Tab3_reestr_shpz_1C t3
left outer join Tab1_param_contr_1с T1 on t3.ВидДоговора = t1.ВиДоговора
where t3.ВидДоговора  is null
union
SELECT distinct '3 Tab3: ВидДоговора непривязанный к Госконтракту из Tab1: ' + t3.ВидДоговора
FROM Tab3_reestr_shpz_1C t3
left outer join Tab1_param_contr_1с T1 on t3.ВидДоговора = t1.ВиДоговора
where t1.ВиДоговора  is null
union
SELECT '1 Tab6: Статья затрат не привязана к строке отчета: ' +  t6.Наименование
FROM Tab6_zatraty_20_1c t6
where len(rtrim(t6.ГрафаОтчета))=0
union 
Select distinct A.txt
from (
SELECT '3 Tab5: Отсутствует счет бухучета в Tab4: ' + t5.Счет_x0020_Дт txt
FROM Tab5_oper_1c t5
left outer join Tab4_plan_1c t4 on t4.Код = t5.Счет_x0020_Дт
where t4.Код is null and t5.Счет_x0020_Дт is not null
union 
SELECT '3 Tab5: Отсутствует счет бухучета в Tab4: ' + t5.Счет_x0020_Кт
FROM Tab5_oper_1c t5
left outer join Tab4_plan_1c t4 on t4.Код = t5.Счет_x0020_Кт
where t4.Код is null and t5.Счет_x0020_Кт is not null
) A
union 
Select '3 Tab5: Отсутствует ШПЗ в Tab3: ' + a.ШПЗ
from (
SELECT 
case when t4.ИдентификацияШПЗ = '1' then t5.Субконто1_x0020_Дт
     when t4.ИдентификацияШПЗ = '2' then t5.Субконто2_x0020_Дт
     when t4.ИдентификацияШПЗ = '3' then t5.Субконто3_x0020_Дт
	 end ШПЗ
FROM Tab5_oper_1c t5
left outer join Tab4_plan_1c t4 on t4.Код = t5.Счет_x0020_Дт
where t4.ИдентификацияШПЗ is not null
union 
SELECT 
case when t4.ИдентификацияШПЗ = '1' then t5.Субконто1_x0020_Кт
     when t4.ИдентификацияШПЗ = '2' then t5.Субконто2_x0020_Кт
     when t4.ИдентификацияШПЗ = '3' then t5.Субконто3_x0020_Кт
	 end
FROM Tab5_oper_1c t5
left outer join Tab4_plan_1c t4 on t4.Код = t5.Счет_x0020_Кт
where t4.ИдентификацияШПЗ is not null
) a where a.ШПЗ not in (select t3.КодВидаНоменклатуры from Tab3_reestr_shpz_1C t3)

union 
Select '3 Tab5: Отсутствует статья затрат в Tab6: ' + a.ШПЗ
from (
SELECT 
case when t4.ИдентификацияСтЗатр = '1' then t5.Субконто1_x0020_Дт
     when t4.ИдентификацияСтЗатр = '2' then t5.Субконто2_x0020_Дт
     when t4.ИдентификацияСтЗатр = '3' then t5.Субконто3_x0020_Дт
	 end ШПЗ
FROM Tab5_oper_1c t5
left outer join Tab4_plan_1c t4 on t4.Код = t5.Счет_x0020_Дт
where t4.ИдентификацияСтЗатр is not null
union 
SELECT 
case when t4.ИдентификацияСтЗатр = '1' then t5.Субконто1_x0020_Кт
     when t4.ИдентификацияСтЗатр = '2' then t5.Субконто2_x0020_Кт
     when t4.ИдентификацияСтЗатр = '3' then t5.Субконто3_x0020_Кт
	 end
FROM Tab5_oper_1c t5
left outer join Tab4_plan_1c t4 on t4.Код = t5.Счет_x0020_Кт
where t4.ИдентификацияСтЗатр is not null
) a where a.ШПЗ not in (select t6.Наименование from Tab6_zatraty_20_1c t6)

union
Select '3 Tab5: Отсутствует договор в реестре договоров (Tab2): ' + a.ШПЗ
from (
SELECT 
case when t4.ИдентификацияИГК = '1' then t5.Субконто1_x0020_Дт
     when t4.ИдентификацияИГК = '2' then t5.Субконто2_x0020_Дт
     when t4.ИдентификацияИГК = '3' then t5.Субконто3_x0020_Дт
	 end ШПЗ
FROM Tab5_oper_1c t5
left outer join Tab4_plan_1c t4 on t4.Код = t5.Счет_x0020_Дт
where t4.ИдентификацияИГК is not null and t4.Код not like '51%'
union 
SELECT 
case when t4.ИдентификацияИГК = '1' then t5.Субконто1_x0020_Кт
     when t4.ИдентификацияИГК = '2' then t5.Субконто2_x0020_Кт
     when t4.ИдентификацияИГК = '3' then t5.Субконто3_x0020_Кт
	 end
FROM Tab5_oper_1c t5
left outer join Tab4_plan_1c t4 on t4.Код = t5.Счет_x0020_Кт
where t4.ИдентификацияИГК is not null and t4.Код not like '51%'
) a where a.ШПЗ not in (select t2.Идентификатор from Tab2_reestr_dogovor_1с t2)

union
Select distinct '3 Tab5: Счет, не привязанный к Госконтракту (Tab1): ' + a.ШПЗ
from (
SELECT 
case when t4.ИдентификацияИГК = '1' then t5.Субконто1_x0020_Дт
     when t4.ИдентификацияИГК = '2' then t5.Субконто2_x0020_Дт
     when t4.ИдентификацияИГК = '3' then t5.Субконто3_x0020_Дт
	 end ШПЗ
FROM Tab5_oper_1c t5
left outer join Tab4_plan_1c t4 on t4.Код = t5.Счет_x0020_Дт
where t4.ИдентификацияИГК is not null and t4.Код like '51%'
union 
SELECT 
case when t4.ИдентификацияИГК = '1' then t5.Субконто1_x0020_Кт
     when t4.ИдентификацияИГК = '2' then t5.Субконто2_x0020_Кт
     when t4.ИдентификацияИГК = '3' then t5.Субконто3_x0020_Кт
	 end
FROM Tab5_oper_1c t5
left outer join Tab4_plan_1c t4 on t4.Код = t5.Счет_x0020_Кт
where t4.ИдентификацияИГК is not null and t4.Код like '51%'
) a where a.ШПЗ not in (select t1.Номервыделенногосчета from Tab1_param_contr_1с t1)



) B 
WHERE B.MEES IS NOT NULL
ORDER BY B.MEES
OPEN @CURSOR
FETCH NEXT FROM @CURSOR INTO @str_tmp

WHILE @@FETCH_STATUS = 0
BEGIN
set @str_out= @str_out + @str_tmp + char(13)
FETCH NEXT FROM @CURSOR INTO @str_tmp
END  
CLOSE @CURSOR
set @str_out= @str_out + 'Сформирован ' + format(getdate(),'dd.MM.yyyy')
execute spWriteStringToFile @str_out, 'C:\EMART_RU','LoadXML.log'


GO

/****** Object:  StoredProcedure [dbo].[MakeAkt10]    Script Date: 09.04.2019 13:07:55 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[MakeAkt10] 
(@indate datetime)
AS

DECLARE @NO_DOG VARCHAR(100)
DECLARE @dateOtch dateTime
DECLARE @dateDo dateTime

set @dateDo =(dateadd(mm,1, (dateadd(mm, Datediff(mm,0,@indate),0))))
set @dateOtch = dateadd(day, -1, @dateDo)


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,
@dateOtch id_DOC,
@dateOtch 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 < @dateDo
 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 < @dateDo
  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,
@dateOtch id_DOC,
@dateOtch 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 < @dateDo
  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 < @dateDo
  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

/****** Object:  StoredProcedure [dbo].[MakeAkt10_WD]    Script Date: 09.04.2019 13:07:55 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[MakeAkt10_WD] 
(@indate datetime,  @cnt int output)
AS
DECLARE @NO_DOG VARCHAR(100)
DECLARE @dateOtch dateTime
DECLARE @dateDo dateTime

set @dateDo =(dateadd(mm,1, (dateadd(mm, Datediff(mm,0,@indate),0))))
set @dateOtch = dateadd(day, -1, @dateDo)

delete from FACT_OBOROT1C where datob >= @dateOtch and TIDK = 887


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
set @cnt = 0

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,
@dateOtch id_DOC,
@dateOtch 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 < @dateDo
 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 < @dateDo
  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,
@dateOtch id_DOC,
@dateOtch 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 < @dateDo
  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 < @dateDo
  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

SELECT CNT=COUNT(*) FROM 
( SELECT DISTINCT NO_DOG
FROM FACT_OBOROT1C WHERE DATOB = @dateOtch AND tidk = 887 
) A 
GO

/****** Object:  StoredProcedure [dbo].[MAKEAKT10_WD2]    Script Date: 09.04.2019 13:07:55 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[MAKEAKT10_WD2]
@INDATE datetime, @CNT int OUTPUT
WITH EXEC AS CALLER
AS
DECLARE @NO_DOG   VARCHAR(100)
DECLARE @dateOtch   DATETIME
DECLARE @dateDo   DATETIME

SET @dateDo        = (dateadd(
                        mm
                       ,1
                       ,(dateadd(
                           mm
                          ,Datediff(
                             mm
                            ,0
                            ,@indate)
                          ,0))))
SET @dateOtch      = dateadd(
                       day
                      ,-1
                      ,@dateDo)



DECLARE @CURSOR   CURSOR
SET @CURSOR        = CURSOR SCROLL FOR
                       SELECT
                         no_dog
                       FROM
                         DIM_IGK
                       WHERE
                         no_dog <> '-'

DELETE FROM FACT_OBOROT1C WHERE TIDK = 887

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
   ,@dateOtch id_DOC
   ,@dateOtch 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 < @dateDo
        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 < @dateDo
        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,
@dateOtch id_DOC,
@dateOtch 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 < @dateDo
	   AND FO.TIDK <> 887
  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 < @dateDo
	   AND FO.TIDK <> 887
  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

/****** Object:  StoredProcedure [dbo].[replaceTab5]    Script Date: 09.04.2019 13:07:55 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

create procedure [dbo].[replaceTab5] 
as
declare @datemin date
declare @datemax date

select  @datemax= max(convert(date,t5t.Дата)), @datemin=min (convert(date,t5t.Дата))  from [dbo].[Tab5_temp] t5t

delete from [dbo].[Tab5_oper_1c] where Tab5_oper_1c.Дата >= @datemin and Tab5_oper_1c.Дата <= @datemax

INSERT INTO [dbo].[Tab5_oper_1c]
           ([id_obor]
           ,[id_doc]
           ,[Дата]
           ,[Документ]
           ,[Счет_x0020_Дт]
           ,[Субконто1_x0020_Дт]
           ,[Субконто2_x0020_Дт]
           ,[Субконто3_x0020_Дт]
           ,[Счет_x0020_Кт]
           ,[Субконто1_x0020_Кт]
           ,[Субконто2_x0020_Кт]
           ,[Субконто3_x0020_Кт]
           ,[Сумма]
           )
SELECT [id_obor]
      ,[id_doc]
      ,convert(date,[Дата])
      ,[Документ]
      ,[СчетДт]
      ,[Субконто1Дт]
      ,[Субконто2Дт]
      ,[Субконто3Дт]
      ,[СчетКт]
      ,[Субконто1Кт]
      ,[Субконто2Кт]
      ,[Субконто3Кт]
      ,[Сумма]
  FROM [dbo].[Tab5_temp] 
GO

/****** Object:  StoredProcedure [dbo].[spWriteStringToFile]    Script Date: 09.04.2019 13:07:55 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO





create PROCEDURE [dbo].[spWriteStringToFile]
 (
@String Varchar(max), --8000 in SQL Server 2000
@Path VARCHAR(255),
@Filename VARCHAR(100)

--
)
AS
DECLARE  @objFileSystem int
        ,@objTextStream int,
		@objErrorObject int,
		@strErrorMessage Varchar(1000),
	    @Command varchar(1000),
	    @hr int,
		@fileAndPath varchar(80)

set nocount on

select @strErrorMessage='opening the File System Object'
EXECUTE @hr = sp_OACreate  'Scripting.FileSystemObject' , @objFileSystem OUT

Select @FileAndPath=@path+'\'+@filename
if @HR=0 Select @objErrorObject=@objFileSystem , @strErrorMessage='Creating file "'+@FileAndPath+'"'
if @HR=0 execute @hr = sp_OAMethod   @objFileSystem   , 'CreateTextFile'
	, @objTextStream OUT, @FileAndPath,2,True

if @HR=0 Select @objErrorObject=@objTextStream, 
	@strErrorMessage='writing to the file "'+@FileAndPath+'"'
if @HR=0 execute @hr = sp_OAMethod  @objTextStream, 'Write', Null, @String

if @HR=0 Select @objErrorObject=@objTextStream, @strErrorMessage='closing the file "'+@FileAndPath+'"'
if @HR=0 execute @hr = sp_OAMethod  @objTextStream, 'Close'

if @hr<>0
	begin
	Declare 
		@Source varchar(255),
		@Description Varchar(255),
		@Helpfile Varchar(255),
		@HelpID int
	
	EXECUTE sp_OAGetErrorInfo  @objErrorObject, 
		@source output,@Description output,@Helpfile output,@HelpID output
	Select @strErrorMessage='Error whilst '
			+coalesce(@strErrorMessage,'doing something')
			+', '+coalesce(@Description,'')
	raiserror (@strErrorMessage,16,1)
	end
EXECUTE  sp_OADestroy @objTextStream
EXECUTE sp_OADestroy @objFileSystem
GO

