ВВЕДЕНИЕ
Отгрузка товаров и их оплата фиксируются в информационных базах соответственно времени самих событий в разное время разными документами и пользователями. Для того, чтобы правильно и быстро связывать документы оплаты с документами отгрузки требуется, кроме наличия достоверной информации об отношении оплаты к конкретным отгрузкам, еще высокая дисциплина работы клиентов и пользователей, хорошая организация работ, удобные инструменты автоматизации. Правильная информация, высокая дисциплина и хорошая организация - эти звезды не часто сходятся вместе, что приводит к недостаточному порядку и высокой трудоемкости в учете взаиморасчетов. В результате многие организации выбирают для себя более легкий путь – декларируют распределение поступающей оплаты по документам отгрузки правилом ФИФО, перекладывая, таким образом, задачу связывания оплат с отгрузками на программу.
В учетных системах правило ФИФО может применяться в процессе проведения документов, который фиксирует результат распределения оплаты в информационной базе. Это порождает проблему поддержания последовательности проведения документов. Поэтому часто бывают желательны более легкие (с точки зрения вмешательства в работу учетной системы) и быстрые (не требующие перепроведения документов) альтернативные решения. В отчетах по неоплаченным долгам такие альтернативные решения основаны на динамическом распределении долга покупателя по документам отгрузки, то есть распределении, выполняемом непосредственно при построении управленческих отчетов.
Препятствием для широкого использования динамического определения неоплаченных долгов является медленная работа известного алгоритма при работе с реляционными СУБД. Это обусловлено расчетом нарастающего итога за время, пропорциональное квадрату числа документов в цепочке взаиморасчетов контрагента. Однако в работе «Баттерфляй – метод быстрого расчета нарастающего итога» было показано, что существуют алгоритмы, решающие эту задачу за линейное время.
Тем не менее, в данной работе предложен другой, отличный от «Баттерфляй», более простой и быстрый специализированный метод, нацеленный на конкретную задачу нахождения неоплаченных долгов. Метод основан на алгоритме бинарного поиска - дихотомии. Метод реализован одним пакетным запросом.
Предлагаемый алгоритм по своей схеме похож на «метод Больцано-Вейерштрасса», опубликованный "группой математиков" в 1938 году. В указанной работе рассматривалась задача поимки льва в пустыне. А суть метода заключалась в том, что пустыня перегораживалась решеткой пополам, потом половина, где находится лев, снова делилась пополам и так до тех пор, пока лев не окажется в достаточно маленькой клетке. Эта известная шутка объясняет картинку в заголовке статьи.
ТЕОРЕТИЧЕСКАЯ ЧАСТЬ
Ключом к решению задачи определения неоплаченных долгов является нахождения момента Х – времени возникновения первого неоплаченного долга. Правило распределения оплат по дисциплине ФИФО говорит о том, что в последовательности упорядоченных по времени документов не могут чередоваться оплаченные и не оплаченные документы. Значит, обязательно существует секунда Х, ранее которой все документы оплачены, а после которой все документы не оплачены.
Чтобы найти искомую секунду Х, возьмем отрезок времени длиной в 536870912 (два в двадцать девятой степени) секунд и назовем его Шаг536870912. Будем считать, что отрезок заканчивается в текущей дате (моменте, на который строится отчет). Она обозначена как «Край». Такое число секунд соответствует примерно семнадцати годам. Значит, начало отрезка придется на февраль 1997 года.
Пусть текущий долг одного конкретного контрагента соответствует величине «Долг». Найдем сумму отгрузок «ПолуСумма» этому контрагенту в правой половине исходного отрезка. Правая половина имеет длину 268435456 секунд и оканчивается в конце шага.
Далее сравним величину долга с отгрузками в правой половине отрезка.
Для случая, когда текущий долг меньше или равен сумме отгрузок справа, делаем вывод, что точка Х находится в пределах правой половины отрезка. Поэтому просто уменьшим вдвое размер шага, оставив его конец на месте. И перейдем, таким образом, к шагу Шаг268435456 размером 268435456 секунд.
Для случая, когда текущий долг больше суммы отгрузок справа, делаем вывод, что точка Х находится в пределах левой половины отрезка. Поэтому делаем сдвиг - переносим конец шага на 268435456 секунд влево, также сокращая его размер вдвое, переходя к шагу Шаг268435456, но заканчивающемуся левее. При этом величину долга нужно сократить на найденную сумму отгрузок «ПолуСумма».
Повторив эту процедуру 29 раз, можно перейти к шагу Шаг1 длины 1, "край" которого равно искомому моменту Х.
Естественно, в запросе эта процедура выполняется для всех контрагентов сразу. Размер шага на каждой итерации для всех контрагентов является одинаковым, уникальны лишь величина долга и времеположение конца шага.
На фиг.1 приведена схема, иллюстрирующая начало работы метода.
ОЦЕНКА ТРУДОЕМКОСТИ
Область поиска – 17 лет была задана изначально, поэтому затраты на реализацию схемы метода, которая сводится к переписыванию данных из одних небольших (по числу покупателей-должников) таблиц в другие с простейшими преобразованиями полей типа сравнения, сложения и вычитания, будут небольшой величиной, линейно связанной с числом контрагентов-должников.
Другие затраты будут связаны с суммированием отгрузок по документам. На первом шаге в среднем будут суммироваться 1/2 всех документов, на втором 1/4, на третьем 1/8 и так далее. Сумма ряда ½ + ¼ + 1/8 + 1/16 + 1/32 и так далее равна, как известно единице. Поэтому затраты времени на суммирование отгрузок будут пропорциональны числу документов, то есть ЛИНЕЙНО зависеть от их количества! Так же ЛИНЕЙНО от числа документов будет зависеть и время работы всего метода. Это значит, что если учет в базе ведется 5 лет, а отчет по всем контрагентам работает 30 секунд, то через 5 лет на том же сервере отчет будет работать всего 60 секунд!
РЕАЛИЗАЦИЯ
Приведем запрос, решающий данную задачу.
ВЫБРАТЬ
Остатки.Организация,
Остатки.Контрагент,
Остатки.ДоговорКонтрагента,
&Дата КАК Край,
Остатки.СуммаВзаиморасчетовОстаток КАК Долг,
0 КАК ПолуСумма,
0 КАК Сдвиг
ПОМЕСТИТЬ Шаг536870912
ИЗ
РегистрНакопления.ВзаиморасчетыСКонтрагентами.Остатки(ДОБАВИТЬКДАТЕ(&Дата, СЕКУНДА, 1), ДоговорКонтрагента.ВидДоговора = ЗНАЧЕНИЕ(Перечисление.ВидыДоговоровКонтрагентов.СПокупателем)) КАК Остатки
ГДЕ
Остатки.СуммаВзаиморасчетовОстаток > 0
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
Обороты.ДоговорКонтрагента,
Обороты.Период КАК Период,
СУММА(Обороты.СуммаВзаиморасчетовОборот) КАК СуммаВзаиморасчетовОборот
ПОМЕСТИТЬ Обороты
ИЗ
РегистрНакопления.ВзаиморасчетыСКонтрагентами.Обороты( , &Дата, Регистратор, ДоговорКонтрагента В (ВЫБРАТЬ Шаг.ДоговорКонтрагента ИЗ Шаг536870912 КАК Шаг)) КАК Обороты
ГДЕ
Обороты.СуммаВзаиморасчетовОборот > 0
СГРУППИРОВАТЬ ПО
Обороты.ДоговорКонтрагента,
Обороты.Период
ИНДЕКСИРОВАТЬ ПО
Обороты.ДоговорКонтрагента,
Период
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
Шаг.ДоговорКонтрагента,
Шаг.Край,
Шаг.Долг,
ЕСТЬNULL(СУММА(Обороты.СуммаВзаиморасчетовОборот), 0) КАК ПолуСумма,
ВЫБОР КОГДА Шаг.Долг > ЕСТЬNULL(СУММА(Обороты.СуммаВзаиморасчетовОборот), 0) ТОГДА -1 ИНАЧЕ 0 КОНЕЦ КАК Сдвиг
ПОМЕСТИТЬ Шаг268435456
ИЗ
(ВЫБРАТЬ
Шаг.ДоговорКонтрагента КАК ДоговорКонтрагента,
Шаг.Долг + Шаг.Сдвиг * Шаг.ПолуСумма КАК Долг,
ДОБАВИТЬКДАТЕ(Шаг.Край, СЕКУНДА, 536870912 * (Шаг.Сдвиг - 0.5) + 1) КАК Центр,
ДОБАВИТЬКДАТЕ(Шаг.Край, СЕКУНДА, 536870912 * Шаг.Сдвиг) КАК Край
ИЗ
Шаг536870912 КАК Шаг) КАК Шаг
ЛЕВОЕ СОЕДИНЕНИЕ Обороты КАК Обороты
ПО Шаг.ДоговорКонтрагента = Обороты.ДоговорКонтрагента
И (Обороты.Период МЕЖДУ Шаг.Центр И Шаг.Край)
СГРУППИРОВАТЬ ПО
Шаг.ДоговорКонтрагента,
Шаг.Край,
Шаг.Долг
;
...
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
Шаг.ДоговорКонтрагента,
Шаг.Край,
Шаг.Долг,
ЕСТЬNULL(СУММА(Обороты.СуммаВзаиморасчетовОборот), 0) КАК Сумма
ПОМЕСТИТЬ Шаг0
ИЗ
(ВЫБРАТЬ
Шаг.ДоговорКонтрагента КАК ДоговорКонтрагента,
Шаг.Долг + Шаг.Сдвиг * Шаг.ПолуСумма КАК Долг,
ДОБАВИТЬКДАТЕ(Шаг.Край, СЕКУНДА, Шаг.Сдвиг) КАК Край
ИЗ
Шаг1 КАК Шаг) КАК Шаг
ЛЕВОЕ СОЕДИНЕНИЕ Обороты КАК Обороты
ПО Шаг.ДоговорКонтрагента = Обороты.ДоговорКонтрагента
И (Обороты.Период = Шаг.Край)
СГРУППИРОВАТЬ ПО
Шаг.ДоговорКонтрагента,
Шаг.Край,
Шаг.Долг
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
Шаг.ДоговорКонтрагента.Организация КАК Организация,
Шаг.ДоговорКонтрагента.Владелец КАК Контрагент,
Шаг.ДоговорКонтрагента КАК ДоговорКонтрагента,
Обороты.Период КАК Период,
Обороты.Регистратор КАК Регистратор,
ВЫБОР
КОГДА Обороты.Период = Шаг.Край
ТОГДА Шаг.Долг * Обороты.СуммаВзаиморасчетовОборот / Шаг.Сумма
ИНАЧЕ Обороты.СуммаВзаиморасчетовОборот
КОНЕЦ КАК Долг,
РАЗНОСТЬДАТ(Обороты.Период, &Дата, ДЕНЬ) КАК Долгота
ИЗ
Шаг0 КАК Шаг
ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрНакопления.ВзаиморасчетыСКонтрагентами.Обороты( , &Дата, Регистратор, ДоговорКонтрагента В (ВЫБРАТЬ Шаг.ДоговорКонтрагента ИЗ Шаг0 КАК Шаг)) КАК Обороты
ПО Шаг.ДоговорКонтрагента = Обороты.ДоговорКонтрагента
И Шаг.Край < = Обороты.Период
ГДЕ Обороты.СуммаВзаиморасчетовОборот > 0
В первом запросе пакета находится величина долга по каждому договору. Эта величина заносится в таблицу Шаг536870912. Во втором запросе пакета обороты отгрузок в разрезе договор+период по всем должникам переносятся во временную проиндексированную таблицу «Обороты». Далее запрос построен в точном соответствии с описанным алгоритмом и поэтому достаточно прозрачен. Поле «Сдвиг» отражает выбор того, в какую половину текущего интервала неопределенности нужно будет переходить на следующем шаге метода. Если Сдвиг равен -1, то производится смещение влево.
Для экономии места повторяющиеся запросы пакета после третьего сокращены - заменены многоточием.
Предпоследний запрос пакета имеет небольшие особенности, связанный с тем, что если в последний момент производился сдвиг влево, то оказываются неизвестными обороты в финальной точке.
В последнем запросе пакета на основе определенной секунды первой неоплаченной отгрузки выбираются все отгрузки с этой секунды. Доля неоплаченных отгрузок первой секунды вычисляются в соответствии со сделанным далее замечанием.
ВАЖНОЕ ЗАМЕЧАНИЕ
В бочке меда достоинств данного метода есть одна ложка сахара. Метод не разделяет оплаченные и не оплаченные отгрузки по одному договору, если они были выполнены в одну и ту же секунду Х. Конечно, это очень редкая ситуация – несколько документов отгрузки одному контрагенту, приходящиеся на одну и ту же единственную Х-секунду. Правило ФИФО ничего не говорит о порядке погашения этих документов. Поэтому логично включать в неоплаченные все такие отгрузки. При этом неоплаченной суммой каждой односекундной отгрузки предлагается считать одну и ту же долю суммы каждого документа. То есть, если в одной секунде есть две отгрузки по 100 рублей, а текущий долг составляет 150 рублей, то обе эти отгрузки будут считаться не оплаченными на 75 рублей каждая. Это решение отличается от применяемого сейчас сомнительного приема, когда документы внутри одной секунды упорядочиваются по внутреннему идентификатору и таким образом погашаются в случайной последовательности.
Нужно сказать, что, кроме приведенного варианта записи запроса, было опробовано множество других вариантов. Например, варианты с более быстрым сужением интервала неопределенности за счет определения крайних документов в интервале поиска. Или варианты, пропускающие соединения с таблицей оборотов после достижения однозначности, варианты с хранением не только конца, но и начала шага. Однако оказалось, что есть неудобный случай (документы располагаются по степеням двойки), который всегда будет требовать всех 29-ти итераций. В результате существенного прироста скорости опробованные усложнения не принесли, поэтому был выбран самый простой вариант.
ПРАКТИЧЕСКОЕ БЫСТРОДЕЙСТВИЕ
Метод демонстрирует хорошее практическое быстродействие. Пока не удалось найти случая (помогите!), чтобы время работы метода определения неоплаченных долгов по всем покупателям-должникам превышало 35 секунд (крупная торговая компания с документами в базе за 7 лет).
ЧАСТНЫЕ ВЫВОДЫ
-
Предложенный метод очень универсален. Он берет информацию всего из одного регистра. Меняя название этого регистра и условия выборки остатков и оборотов, можно легко настроить отчет на работу с самыми разными конфигурациями, быстрее, чем сейчас решать многие другие актуальные задачи. Например, строить отчеты по просроченным долгам (нужно добавить одно сравнение), кредиторской задолженности, остаткам партий товаров без ведения полноценного партионного учета и прочее.
-
Метод не заменяет собой метод «Баттерфляй», который строит полный массив нарастающих итогов, что может пригодиться в соответствующих задачах.
-
Остается открытым вопрос сравнения быстродействия метода с «двухступенчатой СКД», где в СКД первой ступени постобработкой результатов запросов считается нарастающий итог, а во втором СКД отбираются неоплаченные отгрузки.
ОБЩИЕ ВЫВОДЫ
-
Ключом к найденному решению была ее формулировка как задачи поиска определенного момента времени. Поэтому нельзя жалеть времени на анализ и правильную формулировку исходной задачи. Нельзя забывать, что «хорошо поставленная задача уже наполовину решена».
-
Также нужно изучать и чаще вспоминать математику – ее методы универсальны и применимы при решении многих и очень разных проблем – от поимки львов в пустыне до управления дебиторской задолженностью!
НЕКОТОРЫЕ ССЫЛКИ
Тема реализации ФИФО достаточно популярна на Инфостарте. Из многих работ на эту тему хотелось бы выделить Нарастающие итоги в запросе и методы ускорения его выполнения. Ее автор впервые упомянул термин «последовательное приближение», хотя, судя по описанию, и ограничился в решении только сокращением объема группировок за счет их каскадирования (год – месяц - день). Стандартный метод описан в работе Дебиторка fifo по долгам контрагентов (УТ 10.3). Об актуальности задачи убедительно говорится в работе Отчет по дебиторской задолженности. Незаменим для компаний, реализующих товары с отсрочкой платежа и ведущих взаиморасчеты по договору "в целом". Метод ФИФО. Отсрочка платежа, сумма, дней просрочки. Дней средневзвешенное. Можно еще отметить недавнюю работу [УТ11] Дебиторка Фифо, вариант с внедрением нового регистра накопления (для значительного ускорения формирования отчета), которая подтолкнула данную публикацию, вызвав желания показать более легкий путь решения задачи. Ну и работу Просроченная дебиторская задолженность по датам без ведения учета по документам расчетов для УТ 10.3, в комментариях к которой есть слова "...мониторю различные ресурсы в надежде найти "третий вариант", но возможно его просто не существует в природе...", которые окончательно убедили заняться данной задачей.
P.S.: По просьбе автора еще одной публикации, посвященной этой теме, добавлена ссылка: Универсальный отчет "[П]: Дебиторка & Кредиторка" [УТ, УПП, КА].
