---

SELECT 
    d."number"                                          AS "Номер заявления",
    d."dt_update"                                       AS "Дата заявления",
    d."dt_registration"                                 AS "Дата регистрации заявления",
    d."general_applicant name"                          AS "Источник подачи заявления",
    determine_application_type(d."general_applicant name") AS "Тип заявителя (функция)",
    CASE 
        WHEN d."type" IN ('AP 4', 'AP 5', 'TAP 6') THEN 'ФИЗ. ЛИЦО' 
        ELSE 'ЮР. ЛИЦО' 
    END                                                 AS "Тип заявителя",
    CASE 
        WHEN d."receiving office" IS NULL THEN '**' 
        ELSE ro."name" 
    END                                                 AS "Подразделение, принявшее заявление",
    po."name"                                           AS "Орган, ответственный за АП",
    CASE 
        WHEN d."source" = 'EPEU' THEN 'Электронное' 
        ELSE 'Бумажное' 
    END                                                 AS "Тип заявления",
    ap."code"                                           AS "№ АП",
    ap."short_name"                                     AS "АП",
    dec."general_decision"                              AS "Решение",
    d."uuid"                                            AS "ID_Заявления"
FROM 
    "bgap-reception-service"."declarations" d
    LEFT JOIN "bgap-admin-service"."offices" ro ON (
        (d."receiving office" IS NOT NULL AND ro."uuid" = d."receiving office")
        OR
        (d."receiving office" IS NULL AND ro."uuid" = d."processing office")
    )
    INNER JOIN "bgap-admin-service"."offices" po ON po."uuid" = d."processing office"
    INNER JOIN "bgap-administrative-decision-service"."administrative decisions" dec ON d."uuid" = dec."declaration"
    INNER JOIN (
        SELECT k."key", g."short_name", g."code" 
        FROM "bgap-documents-service"."passport keys" k
        JOIN "bgap-documents-service".passports_generations g ON k."uuid" = g."root"
        GROUP BY k."key", g."short_name", g."code"
    ) ap ON d."type" = ap."key"
WHERE 
    dec."general_decision" IS NOT NULL
    AND d."dt_update" > :sql_last_value
ORDER BY d."dt_update" ASC