MERGE INTO appeals apl
USING (
    -- все обращения, по которым необходимо проставить FID-ы
    WITH sq_appeals AS (
        SELECT
              id_appeal
            , fid_appeal
            , fid_type
        FROM
            appeals
        WHERE
            fid_post_data_status = -1
            AND fid_type IN (1, 2, 4, 7)
    )
    -- обращения клиентов
    , sq_client_appeals AS (
        SELECT
              ca.ext_id
            , ca.ops_index     AS ops_index
            , apl.fid_type
            , ca.ufps_name     AS ufps_name
            , apl.id_appeal
        FROM
            sq_appeals                               apl
            JOIN client_appeals                      ca
                ON apl.fid_appeal = ca.ext_id
                AND apl.fid_type = 1
    )
    -- обращения сотрудников
    , sq_staff_appeals AS (
        SELECT
              sa.ext_id
            , sa.ops           AS ops_index
            , apl.fid_type
            , sa.ufps_title    AS ufps_name
            , apl.id_appeal
        FROM
            sq_appeals                               apl
            JOIN staff_appeals                       sa
                ON apl.fid_appeal = sa.ext_id
                AND apl.fid_type = 2
    )
    -- обращения по доступной среде
    , sq_av_env_appeals AS (
        SELECT
              aa.ext_id
            , aa.post_index AS ops_index
            , apl.fid_type
            , NULL AS ufps_name
            , apl.id_appeal
        FROM
            sq_appeals                               apl
            JOIN available_env_appeals               aa
                ON apl.fid_appeal = aa.ext_id
                AND apl.fid_type = 4
    )
    -- обращения по типу пенсии
    , sq_pension_appeals AS (
        SELECT
              pa.ext_id
            , pa.ops_index AS ops_index
            , apl.fid_type
            , NULL AS ufps_name
            , apl.id_appeal
        FROM
            sq_appeals                               apl
        JOIN pension_appeals                         pa
                ON apl.fid_appeal = pa.ext_id
                AND apl.fid_type = 7
    )
    -- все обращения, по которым необходимо проставить FID-ы
    , sq_all_appeals AS (
        SELECT * FROM sq_client_appeals
        UNION
        SELECT * FROM sq_staff_appeals
        UNION
        SELECT * FROM sq_av_env_appeals
        UNION
        SELECT * FROM sq_pension_appeals
    )
    --формируем справочник МР-УФПС
    , sq_ufps_mr as (
        SELECT DISTINCT FID_MR, FID_UFPS
        FROM MV_REL_MR_UFPS_POST
    )
    SELECT
          aa.id_appeal
        , CASE
              WHEN po.post_index IS NOT NULL                                                       -- нашлась связь в справочнике D_AVITO_POST_OFFICES
                  THEN 0
              WHEN (aa.ops_index IS NOT NULL) AND (REGEXP_LIKE(aa.ops_index, '\d{6}'))             -- индекс из обращения не найден в справочнике D_AVITO_POST_OFFICES,
                  THEN 1                                                                           -- либо в поле индекса записан не индекс
              WHEN du.id_ufps IS NOT NULL                                                          -- индекса нет (либо он некорректный), но нашлось соответствие по УФПС в справочнике D_UFPS
                  THEN 0
              WHEN aa.ufps_name IS NOT NULL                                                        -- УФПС не найден в справочнике D_UFPS
                  THEN 2
              ELSE 3                                                                               -- не проставлен ни индекс, ни УФПС
          END                                                                            AS fid_post_data_status
        , CASE WHEN po.post_index IS NOT NULL THEN po.fid_post_office END                AS fid_post_office
        , CASE
              WHEN po.post_index IS NOT NULL THEN po.fid_ufps
              ELSE du.id_ufps
          END                                                                            AS fid_ufps
        , CASE
              WHEN po.post_index IS NOT NULL THEN po.fid_mr
              ELSE rl.fid_mr
          END                                                                            AS fid_mr
    FROM
        sq_all_appeals                                                     aa
        LEFT JOIN d_avito_post_offices                                     po
            ON TRIM(aa.ops_index) = TRIM(po.post_index)
        LEFT JOIN d_ufps                                                   du
            ON UPPER(TRIM(aa.ufps_name)) = UPPER(TRIM(du.ufps_name))
        LEFT JOIN sq_ufps_mr                                              rl
            ON du.id_ufps = rl.fid_ufps
) sq
ON (apl.id_appeal = sq.id_appeal)
WHEN MATCHED THEN
    UPDATE SET
          apl.fid_mr = sq.fid_mr
        , apl.fid_ufps = sq.fid_ufps
        , apl.fid_post_office = sq.fid_post_office
        , apl.fid_post_data_status = sq.fid_post_data_status