WITH
  --------------Фильтр на 4 и 5 колонки------------
  filtr_acc_code AS
  (SELECT /*+ materialize */
      SUBSTR(pvl.value, 1 , 1 )                          col
    , ad_pk_text_utils.fn_substr(pvl.value, '@',  2 )  acc_code
   FROM ad_parameter_values pvl
   WHERE  1  =  1 
     AND pvl.par_name = 'GL_RP_517_ACC_CODE_COLUMN'
   ),
  --------фильтр на строки 011 201 521 621----------
  filtr_acc_end AS
  (SELECT /*+ materialize */
      CASE pvl.par_name
      WHEN 'GL_RP_517_ACC_END_INCOME'     THEN 'Д'
      WHEN 'GL_RP_517_ACC_END_EXPENSES'   THEN 'Р'
      WHEN 'GL_RP_517_ACC_END_SOURCE_520' THEN 'И'
      WHEN 'GL_RP_517_ACC_END_SOURCE_620' THEN 'И'
      END                                                             part
    , CASE pvl.par_name
      WHEN 'GL_RP_517_ACC_END_INCOME'     THEN '011'
      WHEN 'GL_RP_517_ACC_END_EXPENSES'   THEN '201'
      WHEN 'GL_RP_517_ACC_END_SOURCE_520' THEN '521'
      WHEN 'GL_RP_517_ACC_END_SOURCE_620' THEN '621'
      END                                                             rw
    , REPLACE(ad_pk_text_utils.fn_substr(pvl.value, '@',  1 ),'X','_')  acc_end
    , NVL(ad_pk_text_utils.fn_substr(pvl.value, '@',  2 ),'%')          rse_code
   FROM ad_parameter_values pvl
   WHERE  1  =  1 
     AND pvl.par_name IN (  'GL_RP_517_ACC_END_INCOME'
                          , 'GL_RP_517_ACC_END_EXPENSES'
                          , 'GL_RP_517_ACC_END_SOURCE_520'
                          , 'GL_RP_517_ACC_END_SOURCE_620'
                          )
   ),
  --------фильтр на строки 710 720 823 824------------
  seq AS
  (
   SELECT level- 1  num FROM dual CONNECT BY level< 11 
   ),
  --  --  --  --  --  --  --  --  --  --
  filtr_val AS
  (SELECT /*+ materialize */
      'И'                                             part
    , SUBSTR(pvl.value, 1 , 3 )                           rw
    , ad_pk_text_utils.fn_substr(pvl.value, '@',  2 )   acc_code
    , ad_pk_text_utils.fn_substr(pvl.value, '@',  3 )   acc_end
   FROM ad_parameter_values pvl
   WHERE  1  =  1 
     AND pvl.par_name = 'GL_RP_517_ACC_CODE_ACC_END'
   ),
  --  --  --  --  --  --  --  --  --  --
  filtr_acc_code_acc_end AS
  (SELECT /*+ materialize */
      DISTINCT
      part
    , rw
    , acc_code
    , REPLACE(SUBSTR(acc_end, 1 , 1 ), 'X', s1.num)||
      REPLACE(SUBSTR(acc_end, 2 , 1 ), 'X', s2.num)||
      REPLACE(SUBSTR(acc_end, 3 , 1 ), 'X', s3.num)  AS acc_end
   FROM
      filtr_val
    , seq s1
    , seq s2
    , seq s3
   ),
  ------------Фильтр на 7 колонку------------
  filtr_aco_code AS
  (SELECT
      pvl.value  aco_code
   FROM ad_parameter_values pvl
   WHERE  1  =  1 
     AND pvl.par_name = 'GL_RP_517_ACO_CODE_COLUMN_7'
   ),
  ---------Основной массив проводок------------
  trn AS
  (
   SELECT
       TRIM(cse.acc_begin)||' '||TRIM(cse.acc_end) kbk
     , DECODE(  ind.type
              , 'P'
              , (ROUND(trn.base_db_amount, 2 ) - ROUND(trn.base_cr_amount, 2 ))
              , (ROUND(trn.base_cr_amount, 2 ) - ROUND(trn.base_db_amount, 2 ))
              ) amount
     , TRIM(cse.acc_end) acc_end
     , ind.acc_code
     , trn.aco_code
     , trn.operational_day
     , CASE
       WHEN cse.cls_id1  IS NULL THEN null
       WHEN cse.cls_id2  IS NULL THEN cse.cls_id1
       WHEN cse.cls_id3  IS NULL THEN cse.cls_id2
       WHEN cse.cls_id4  IS NULL THEN cse.cls_id3
       WHEN cse.cls_id5  IS NULL THEN cse.cls_id4
       WHEN cse.cls_id6  IS NULL THEN cse.cls_id5
       WHEN cse.cls_id7  IS NULL THEN cse.cls_id6
       WHEN cse.cls_id8  IS NULL THEN cse.cls_id7
       WHEN cse.cls_id9  IS NULL THEN cse.cls_id8
       WHEN cse.cls_id10 IS NULL THEN cse.cls_id9
       ELSE cse.cls_id10
       END cls_id
     , CASE TRIM(cse.acc_begin)||ind.acc_code||TRIM(cse.acc_end)
       WHEN MAX(TRIM(cse.acc_begin)||ind.acc_code||TRIM(cse.acc_end)) over (PARTITION BY trn.group_id, trn.aco_code)
       THEN MIN(TRIM(cse.acc_begin)||ind.acc_code||TRIM(cse.acc_end)) over (PARTITION BY trn.group_id, trn.aco_code)
       WHEN MIN(TRIM(cse.acc_begin)||ind.acc_code||TRIM(cse.acc_end)) over (PARTITION BY trn.group_id, trn.aco_code)
       THEN MAX(TRIM(cse.acc_begin)||ind.acc_code||TRIM(cse.acc_end)) over (PARTITION BY trn.group_id, trn.aco_code)
       ELSE NULL
       END kbk_two
     , cse.rse_code
   FROM
       sk_transactions trn
     , sk_accounting_operation_types aco
     , sk_classifications gro
     , sk_classification_sets cse
     , sk_account_indicators ind
     , sk_analytical_accounts aac
     , sk_personal_accounts pac
   WHERE  1  =  1 
     AND trn.aco_code = aco.code
     AND aco.gro_id = gro.id
     AND gro.ref_code = 'ГРО'
     AND gro.code = p_gro
     AND trn.acc_cse_id = cse.id
     AND ind.code = trn.ind_code
     AND trn.operational_day BETWEEN TRUNC(p_date, 'yyyy') AND p_date- 1 
     AND trn.aac_id = aac.id
     AND aac.pac_id   = pac.id
     AND (p_is_except =  0 
          OR
          (p_is_except !=  0 
           AND
           pac.account != vr_ex_zsi_pat_code
           )
          )
   ),
   --------Первая часть итогового массива------------
   first_trn AS
   (
    SELECT
       fil.part
     , DECODE(  GROUPING(trn.kbk)
              ,  1 
              , CASE
                WHEN fil.rw = '011' AND fil.part = 'Д' THEN '010'
                WHEN fil.rw = '201' AND fil.part = 'Р' THEN '200'
                WHEN fil.rw = '521' AND fil.part = 'И' THEN '520'
                WHEN fil.rw = '621' AND fil.part = 'И' THEN '620'
                WHEN fil.rw IS NULL AND fil.part = 'И' THEN '500'
                ELSE NULL
                END
              , fil.rw
              ) rw
     , trn.cls_id
     , trn.kbk
     , SUM(CASE
           WHEN trn.acc_code IN (SELECT acc_code FROM filtr_acc_code WHERE col =  4 ) THEN trn.amount
           ELSE  0 
           END) amount_4
     , SUM(CASE
           WHEN trn.acc_code IN (SELECT acc_code FROM filtr_acc_code WHERE col =  5 ) THEN trn.amount
           ELSE  0 
           END) amount_5
     , SUM(CASE
           WHEN trn.acc_code IN (SELECT acc_code FROM filtr_acc_code WHERE col =  5 )
                AND
                trn.aco_code IN (SELECT aco_code FROM filtr_aco_code) THEN trn.amount
           ELSE  0 
           END) amount_7
    FROM
       trn
     , filtr_acc_end fil
    WHERE  1  =  1 
      AND trn.acc_end LIKE fil.acc_end
      AND (fil.rse_code = '%'
           OR
           (fil.rse_code <> '%' AND fil.rse_code = trn.rse_code)
           )
    GROUP BY GROUPING SETS
     (  (fil.part) --суммы по разделам
      , (fil.part, fil.rw) --суммы по строкам
      , (fil.part, fil.rw, trn.kbk, trn.cls_id)
      )
   ),
   ---------Вторая часть итогового массива по строкам 710 и 720 --------------
   two_trn AS
   (
    SELECT
       fil.part
     , fil.rw
     , trn.amount
     , trn.aco_code
     , trn.operational_day
     , trn.kbk_two
     , SUBSTR(trn.kbk_two, - 3 ) acc_end_two
    FROM
       trn
     , filtr_acc_code_acc_end fil
    WHERE  1  =  1 
      AND trn.acc_code = fil.acc_code
      AND trn.acc_end = fil.acc_end
      AND fil.rw IN ('710', '720')
      AND NOT EXISTS (SELECT  1 
                      FROM ad_parameter_values pvl
                      WHERE  1  =  1 
                        AND pvl.par_name = 'GL_RP_517_ACC_END_EXPENSES'
                        AND pvl.value = trn.aco_code)
    ),
    -------Для строк 710 и 720 курсовая разница--------
    amount_exc AS
    (
    SELECT
       kbk_two
     , operational_day
     , SUM(amount) amount_5
     , SUM(CASE
           WHEN aco_code IN (SELECT aco_code FROM filtr_aco_code) THEN amount
           ELSE  0 
           END) amount_7
    FROM
      two_trn
    WHERE  1  =  1 
      AND EXISTS (SELECT  1 
                  FROM ad_parameter_values pvl
                  WHERE  1  =  1 
                    AND pvl.par_name = 'GL_EXCHANGE_DIFFERENCE_EKN'
                    AND pvl.value = acc_end_two)
    GROUP BY
       kbk_two
     , operational_day
    ),
   -------Сборка данных для строк 710 и 720 ---------------------
   trn_700 AS
   (
    SELECT
       part
     , rw
     , SUM(amount) amount_5
     , SUM(CASE
           WHEN aco_code IN (SELECT aco_code FROM filtr_aco_code) THEN amount
           ELSE  0 
           END) amount_7
    FROM
      two_trn
    WHERE  1  =  1 
      AND NOT EXISTS (SELECT  1 
                      FROM ad_parameter_values pvl
                      WHERE  1  =  1 
                        AND pvl.par_name = 'GL_EXCHANGE_DIFFERENCE_EKN'
                        AND pvl.value = two_trn.acc_end_two)
    GROUP BY
       part
     , rw
    UNION ALL
    SELECT
       'И' part
     , CASE
       WHEN amount_5 >=  0  THEN '710'
       ELSE '720'
       END rw
--     , ABS(amount_5) amount_5
--     , ABS(amount_7) amount_7
     , amount_5
     , amount_7
    FROM
      amount_exc
    ),
   -------Слияние первой и второй части итогового массива---------
   itog AS
   (
    SELECT
       part
     , rw
     , cls.name
     , kbk
     , amount_4
     , amount_5
     , amount_5 - amount_7 AS amount_6
     , amount_7
    FROM
       first_trn
     , sk_classifications cls
    WHERE  1  =  1 
      AND first_trn.cls_id = cls.id(+)
      AND first_trn.rw IS NOT NULL
    -- 823 -- 824 -- 800 --
    UNION ALL
    SELECT
       fil.part
     , DECODE(  GROUPING(fil.rw)
              ,  1 
              , SUBSTR(fil.rw, 1 , 1 )||'00'
              , fil.rw
              ) rw
     , NULL    name
     , NULL    kbk
     , NULL    amount_4
     , SUM(trn.amount) amount_5
     , SUM(trn.amount) amount_6
     , NULL            amount_7
    FROM
       trn
     , filtr_acc_code_acc_end fil
    WHERE  1  =  1 
      AND trn.acc_code = fil.acc_code AND trn.acc_end = fil.acc_end
      AND fil.rw IN ('823', '824')
    GROUP BY GROUPING SETS
      (  (fil.part, fil.rw)
       , (fil.part, SUBSTR(fil.rw,  1 , 1 ))
       )
    -- 710 -- 720 -- 700 --
    UNION ALL
    SELECT
       part
     , DECODE(  GROUPING(rw)
              ,  1 
              , '700'
              , rw
              )                       rw
     , NULL                           name
     , NULL                           kbk
     , NULL                           amount_4
     , SUM(amount_5)                  amount_5
     , SUM(amount_5) - SUM(amount_7)  amount_6
     , SUM(amount_7)                  amount_7
    FROM
       trn_700
    GROUP BY GROUPING SETS
      (  (part, rw)
       , (part, SUBSTR(rw,  1 , 1 ))
       )
    )
   ---------------Сборка данных + сессия-------------------
   SELECT
      p_session_id --session_id
    , p_rep_name   --rep_name
    ,  1             --serial_number
    , part
    , rw
    , name
    , kbk
    , amount_4
    , amount_5
    , amount_6
    , amount_7
   FROM itog
   WHERE  1  =  1 
     AND (p_part IS NULL
          OR
          (p_part IS NOT NULL
           AND
           part IN (SELECT SUBSTR(char_col1, 1 , 1 )
                    FROM sk_rep_output
                    WHERE  1  =  1 
                      AND session_id = p_session_id
                      AND serial_number =  10 
                      AND rep_name = 'P_PART'
                    )
           )
          );