WITH insurerQ AS (
        SELECT
          gi.GLOBAL_INSURER_ID,
          mgi.MAIN_GLOBAL_INSURER_ID,
          i.INSURER_REG_NUM,
          i.INSURER_FULL_NAME,
          i.INSURER_SHORT_NAME,
          COALESCE(gi.GLOBAL_INSURER_INN, mgi.MAIN_GLOBAL_INSURER_INN) AS inn_inner,
          COALESCE(gi.GLOBAL_INSURER_OGRN, mgi.MAIN_GLOBAL_INSURER_OGRN) AS ogrn_ip_inner,
          gi.GLOBAL_INSURER_KPP,
          i.CATEGORY_ID
        FROM ASV_INSURER i
          LEFT JOIN ASV_GLOBAL_INSURER gi ON i.INSURER_TYPE = 0 AND i.INSURER_OGRN IS NOT NULL AND i.INSURER_KPP IS NOT NULL
                                             AND i.INSURER_INN = gi.GLOBAL_INSURER_INN AND i.INSURER_KPP = gi
          .GLOBAL_INSURER_KPP AND i.INSURER_OGRN = gi.GLOBAL_INSURER_OGRN
          LEFT JOIN ASV_MAIN_GLOBAL_INSURER mgi ON i.INSURER_TYPE = 1 AND i.INSURER_KPP IS NULL AND i.INSURER_INN = mgi
          .MAIN_GLOBAL_INSURER_INN
                                                   AND i.INSURER_OGRNIP = mgi.MAIN_GLOBAL_INSURER_OGRN
        WHERE
          i.INSURER_REG_NUM = #{insurerRegNum}
    )
    SELECT
      a.CB_ACCOUNT_ID                          AS cbAccountId,
      a.CB_ACCOUNT_ACCOUNT                     AS account,
      a.CB_ACCOUNT_OPEN_DATE                   AS openDate,
      a.CB_ACCOUNT_CLOSE_DATE                  AS closeDate,
      a.CB_ACCOUNT_CHANGE_DATE                 AS changeDate,
      a.CB_ACCOUNT_ELECTRONIC                  AS electronic,
      a.CB_ACCOUNT_BANK_MAIN_ID                AS bankMain,
      a.CB_ACCOUNT_BANK_BRANCH_ID              AS bankBranch,
      a.CB_ACCOUNT_GLOBAL_INSURER_ID           AS insurer,
      a.CB_ACCOUNT_COMMON_INSURER_ID           AS commonInsurer,
      a.CB_ACCOUNT_STATUS_ID                   AS statusCode,
      a.CB_ACCOUNT_CREATE                      AS createDate,
      a.CB_ACCOUNT_UPDATE                      AS updateDate,
      a.CB_ACCOUNT_BANK_OGRN                   AS bankOgrn,
      COALESCE(COALESCE(b.BANK_BIC, bb.BANK_BRANCH_BIC), omg.BANK_BIC ) AS bankBIC,
      COALESCE(COALESCE(b.BANK_FULL_NAME_BY_KGRKO, bb.BANK_BRANCH_NAME_BY_KGRKO), omg.BANK_FULL_NAME_BY_KGRKO) AS
        bankFullName,
      COALESCE(COALESCE(b.BANK_SHORT_NAME_BY_KGRKO, NULL), omg.BANK_SHORT_NAME_BY_KGRKO) AS bankShortName,
      COALESCE(COALESCE(b.BANK_KSNP_BY_BIC, bb.BANK_BRANCH_KSNP_BY_BIC), omg.BANK_KSNP_BY_BIC) AS bankKSNP,
      COALESCE(COALESCE(b.BANK_REGNUM_BY_KGRKO, NULL), omg.BANK_REGNUM_BY_KGRKO) AS bankRegNum,
      COALESCE(COALESCE(b.BANK_INN_BY_GLOBAL_INSURER, bb.BANK_BRANCH_INN_BY_GLOBAL_INSURER),
               omg.BANK_INN_BY_GLOBAL_INSURER) AS bankINN,
      COALESCE(COALESCE(b.BANK_KPP_BY_GLOBAL_INSURER, bb.BANK_BRANCH_KPP_BY_GLOBAL_INSURER),
               omg.BANK_KPP_BY_GLOBAL_INSURER) AS bankKPP,
      s.GLOBAL_INSURER_KPP AS insurerKPP,
      s.MAIN_GLOBAL_INSURER_ID AS commonInsurer,
      s.GLOBAL_INSURER_ID AS insurer,
      s.INSURER_REG_NUM AS insurerRegNum,
      s.inn_inner AS insurerINN,
      s.ogrn_ip_inner AS insurerOGRN,
      s.INSURER_FULL_NAME AS insurerFullName,
      s.INSURER_SHORT_NAME AS insurerShortName,
      s.CATEGORY_ID AS insurerCategoryId
    FROM
      ASV_CB_ACCOUNT a
      LEFT JOIN ASV_BANK b ON (b.BANK_ALIAS IS NULL AND a.CB_ACCOUNT_BANK_MAIN_ID = b.BANK_ID)
                              AND a
                                  .CB_ACCOUNT_BANK_BRANCH_ID
                                  IS NULL
      LEFT JOIN (SELECT
                   * FROM ASV_BANK b
                 WHERE b.BANK_ID = (SELECT DISTINCT
                                      b2.BANK_ALIAS
                                    FROM
                                      ASV_BANK b2
                                      JOIN
                                      ASV_CB_ACCOUNT a ON b2.BANK_ID = a.CB_ACCOUNT_BANK_MAIN_ID
                                    WHERE
                                      b2.BANK_ALIAS IS NOT NULL fetch FIRST 1 ROW ONLY)) omg ON omg.BANK_ROOT = a
    .CB_ACCOUNT_BANK_MAIN_ID
      LEFT JOIN ASV_BANK_BRANCH bb ON a.CB_ACCOUNT_BANK_BRANCH_ID = bb.BANK_BRANCH_ID AND a.CB_ACCOUNT_BANK_MAIN_ID IS
                                                                                          NULL
      JOIN insurerQ AS s ON s.GLOBAL_INSURER_ID = a.CB_ACCOUNT_GLOBAL_INSURER_ID OR s.MAIN_GLOBAL_INSURER_ID = a.CB_ACCOUNT_COMMON_INSURER_ID
    WHERE
      a.CB_ACCOUNT_STATUS_ID IN (0,1,3)