SQL, Анализ данных

Как при помощи запроса SQL рассчитать коды для формирования обязательных нормативов банка

Время прочтения: 3 мин.

Мой лайфхак будет интересен и полезен тем аудиторам, кто занимается проведением проверок процентного и валютного рисков банковской книги (ПВРБК). В рамках данной проверки одной из важных задач является проверка полноты и корректности обязательной отчетности, предоставляемой в Банк России, а также проверка правильности формирования кодов, учитываемых при расчете обязательных нормативов Банка.

 Аудиторы знают не понаслышке, что при осуществлении процедуры подтверждения правильности и полноты пруденциальной отчетности, приходится работать с большими объемами данных, при проверке расчетов кодов требуется особая  внимательность и скрупулезность,  чтобы адекватно оценить качество обязательной отчетности и снизить риск предоставления некорректной отчетности регулятору.

 Для облегчения работы при проверке правильности формирования кодов, я обратилась к языку программирования SQL. Посредством  перекладчика PL/SQL Developer, я получила выгрузку данных, в необходимом для анализа формате. При выгрузке происходит автоматический анализ назначения платежа и присваивание операции соответствующего кода (8914 или 8994), благодаря чему существенно сокращается время на проверку.

     Итак, предлагаю ознакомиться с инструментом автоматизированного расчета кодов 8914,8994:

-- выбор необходимых столбцов из базы данных, необходимых для расчета кодов 
     select t1. val ,"Номер счета", dpd, "Дата проводки",
     -- переименование столбца "Сумма" в наименование "Сумма_кредит" с преобразованием точки в 
     запятую
     replace(replace("Сумма",',',''),'.',',')  "Сумма_кредит","Счет корреспондента", "Зачисления
     (Орг)", "Зачисления (Счет)", "Списания (Счет)", "Списания (Орг)", "Назначение платежа", 
     "КОД"
     from (select  val, 
     "Номер счета",
     dpd,
      "Дата проводки",
      -- переименование столбца "Исходящий остаток, Кт" в наименование "Суммa"
      TO_CHAR(replace("Исходящий остаток",'Кт','')) "Сумма",
      "Счет корреспондента",
       "Зачисления (Орг)", 
       "Зачисления (Счет)",
       "Списания (Счет)",
       "Списания (Орг)",        
       "Назначение платежа",
        -- выделение первых 30 символов из "Назначения платежа"
       SUBSTR ("Назначение платежа", 1, 30), 
       --присвоение операции соответствующего кода 8914 или 8994 в зависимости от назначения платежа
       (CASE
       WHEN "Назначение платежа" like '%ажение досрочно погашен%' THEN '8914'
       WHEN "Назначение платежа" like '%ашение учтенных процентов з%' THEN '8914'
       WHEN "Назначение платежа" like '%шение учтенной платы за%' THEN '8914'
       WHEN "Назначение платежа" like '%переплаты по кредитному%'THEN '8914'
       WHEN "Назначение платежа" like '%Переоценка счета%'THEN '8994'
       WHEN "Назначение платежа" like '%Перенос остат%'THEN '8994'  
       WHEN "Назначение платежа" like '%Внесение изменений%'THEN '8914'
       WHEN "Назначение платежа" like '%Погашение процент%'THEN '8914'   
       ELSE ''
       END) AS "КОД"
       from (
       -- выделение дублей из базы данных
       SELECT a.id,a.C_5 "ИНН", a.c_36 podr, a.c_37, a.c_38, a.c_56 segm,
        a.c_3 "Наименование",
        a.c_2 val,
        a.C_1 "Номер счета", a.c_12 ost_nc,
        a.c_14 dpd,
        a.c_18 otkr, a.c_21 zakr, a.c_24 state, a.c_30, 
        to_char(b.C_1,'yyyy.MM.dd hh24:mi:ss') "Дата проводки", 
        b.C_2 "Входящий остаток",
        b.C_3 "Дебет",
        b.C_4 "Кредит",
        b.C_5 "Исходящий остаток",
        b.C_6 "Счет корреспондента",
       b.c_8 "Зачисления (Орг)",
        b.c_9 "Зачисления (Счет)",
        b.c_10 "Списания (Орг)",
       b.c_11 "Списания (Счет)",        
        b.c_12 "Назначение платежа",
        b.ref13 "id_plat_doc", row_number()over(partition by a.C_1  order by a.C_1, 
        to_char(b.C_1,'yyyy.MM.dd hh24:mi:ss')  desc ,b.ref13  desc ) as "Дубли"
        FROM IBS.VW_CRIT_AC_FIN a
        inner join VW_CRIT_298600423 b on b.collection_id=a.ref13  
        -- дата отчета
        where b.c_1 < '00.00.0000' 
        -- номер балансового счета 
        and  a.c_1 like ('*****________**%') 
        -- дата отчета
        and (a.c_21 is null or a.c_21>='00.00.0000')
        -- исключение  излишних операций, не участвующих при расчете кодов, дублей, операций с нулевыми
        остатками
        and a.c_14 not like ('01%')
        and b.c_12 not like ('Вознаграждение за предоставление банковской гарантии%') and
         b.c_12 not like('Отражение переплаты по гарантии%') and b.c_12 not
         like('%излишнеуплаченного вознаграждения%') 
         order by "Номер счета" ) one
         where "Дубли" ='1' 
         and "Исходящий остаток" <> '0.00')t1

При помощи данного скрипта из базы данных формируется выгрузка с указанием сумм в разрезе кодов 8914, 8994.

        Данный пример наглядно демонстрирует удобство и эффективность применения запросов SQL при проведении контрольных процедур, что позволяет сократить время и трудоемкость проверки, а также снизить риски формирования и предоставления в Банк России некорректной отчетности.

Советуем почитать