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)
    )
    -- обращения клиентов
    , 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
    )
    -- все обращения, по которым необходимо проставить FID-ы
    , sq_all_appeals AS (
        SELECT * FROM sq_client_appeals
        UNION ALL
        SELECT * FROM sq_staff_appeals
        UNION ALL
        SELECT * FROM sq_av_env_appeals
    )
    SELECT
          aa.id_appeal
        , CASE
              WHEN cts.ops_index IS NOT NULL                                                       -- нашлась связь в справочнике D_AV_ENV_CONTACTS
                  THEN 0
              WHEN (aa.ops_index IS NOT NULL) AND (REGEXP_LIKE(aa.ops_index, '\d{6}'))             -- индекс из обращения не найден в справочнике D_AV_ENV_CONTACTS,
                  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 cts.ops_index IS NOT NULL THEN cts.fid_post_office END               AS fid_post_office
        , CASE
              WHEN cts.ops_index IS NOT NULL THEN cts.fid_ufps
              ELSE du.id_ufps
          END                                                                            AS fid_ufps
        , CASE
              WHEN cts.ops_index IS NOT NULL THEN cts.fid_mr
              ELSE rl.fid_mr
          END                                                                            AS fid_mr
    FROM
        sq_all_appeals                                                     aa
        LEFT JOIN d_av_env_contacts                                        cts
            ON TRIM(aa.ops_index) = TRIM(cts.ops_index)
        LEFT JOIN d_ufps                                                   du
            ON UPPER(TRIM(aa.ufps_name)) = UPPER(TRIM(du.ufps_name))
        LEFT JOIN rel_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