Создание отчета о задолженности по FIFO

В данной статье мы рассмотрим создание запроса для отчета по кредиторской задолженности с возможностью распределения долгов по методу ФИФО (FIFO).

В качестве конфигурации будет выступать «Розница 2.2», хотя методика будет применима для множества типовых решений.

Задача: разработать отчет по задолженности поставщикам с распределением общей суммы задолженности по документам поступления. То есть, хотим видеть долги в разрезе документов поступления.

Сделаем оговорку: во многих конфигурации есть возможность учета расчетов с поставщиками по документам. Мы условимся, что у нас нет такой возможности в программе. Наша цель разобрать способ решения задачи.

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

Содержание

1. Ввод начальных данных
2. Решение задачи
Этап 1. Получение общей таблицы долгов
Результат
Этап 2. Получение данных документов
Результат
Этап 3. Получение суммы нарастающим итогом
Результат
Комментарий
Этап 4. Финальный запрос
Результат
Комментарий
Подведение итогов

1. Ввод начальных данных

Внесем в тестовую базу данных несколько документов поступления и частичную их оплату, чтобы получить долг поставщику.

Дата	Номер	Поставщик	Сумма
01.04.2020	0000-000001	ООО Поставщик	15 000,00
10.04.2020	0000-000002	ООО Поставщик	32 000,00
20.04.2020	0000-000003	ООО Поставщик	22 500,00
		Итого:	69 500

Оплата

Дата	Номер	Контрагент	Сумма оплаты
04.04.2020	0000-000001	ООО Поставщик	8 000,00

2. Решение задачи

Общая идея решения такова: мы должны получить таблицу долгов в разрезе контрагентов и магазинов и приклеить к ней детальную информацию по документам.

Задача сводится к построению запроса в СКД. Первая подзадача (таблица долгов) реализуется в 1С Розница таким образом:

Этап 1. Получение общей таблицы долгов

В первой таблице выберем только долги в пользу поставщиков (кредиторская задолженность).

ВЫБРАТЬ
	РасчетыСПоставщикамиОстатки.Магазин КАК Магазин,
	РасчетыСПоставщикамиОстатки.Поставщик КАК Поставщик,
	-РасчетыСПоставщикамиОстатки.СуммаОстаток КАК СуммаОстаток
ИЗ
	РегистрНакопления.РасчетыСПоставщиками.Остатки КАК РасчетыСПоставщикамиОстатки
ГДЕ
	РасчетыСПоставщикамиОстатки.СуммаОстаток < 0

Результат

Магазин	Поставщик	СуммаОстаток
Магазин №1	ООО Поставщик	61 500

Этап 2. Получение данных документов

Будем использовать пакетный запрос. Во втором пакете получим данные по документам тех поставщиков, перед которыми мы имеем задолженность.

ВЫБРАТЬ
	ПоступлениеТоваров.Дата КАК Дата,
	ПоступлениеТоваров.Ссылка КАК Ссылка,
	ПоступлениеТоваров.Организация КАК Магазин,
	ПоступлениеТоваров.Контрагент КАК Контрагент,
	ПоступлениеТоваров.СуммаДокумента КАК СуммаДокумента
ПОМЕСТИТЬ ТаблПриход
ИЗ
	Документ.ПоступлениеТоваров КАК ПоступлениеТоваров
ГДЕ
	ПоступлениеТоваров.Проведен = ИСТИНА
	И ПоступлениеТоваров.Контрагент В
			(ВЫБРАТЬ
				ТаблДолги.Поставщик
			ИЗ
				ТаблДолги КАК ТаблДолги)

Результат

Дата	Ссылка	Магазин	Контрагент	СуммаДокумента
01.04.2020	Поступление товаров 0000-000001 от 01.04.2020	ООО ТЕСТ	ООО Поставщик	15 000
10.04.2020	Поступление товаров 0000-000002 от 10.04.2020	ООО ТЕСТ	ООО Поставщик	32 000
20.04.2020	Поступление товаров 0000-000003 от 20.04.2020	ООО ТЕСТ	ООО Поставщик	22 500

Этап 3. Получение суммы нарастающим итогом

Создаем новый пакет запроса.

ВЫБРАТЬ
	РасчетыСПоставщикамиОстатки.Магазин КАК Магазин,
	РасчетыСПоставщикамиОстатки.Поставщик КАК Поставщик,
	-РасчетыСПоставщикамиОстатки.СуммаОстаток КАК СуммаОстаток
ПОМЕСТИТЬ ТаблДолги
ИЗ
	РегистрНакопления.РасчетыСПоставщиками.Остатки КАК РасчетыСПоставщикамиОстатки
ГДЕ
	РасчетыСПоставщикамиОстатки.СуммаОстаток < 0
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
	ПоступлениеТоваров.Дата КАК Дата,
	ПоступлениеТоваров.Ссылка КАК Ссылка,
	ПоступлениеТоваров.Магазин КАК Магазин,
	ПоступлениеТоваров.Контрагент КАК Контрагент,
	ПоступлениеТоваров.СуммаДокумента КАК СуммаДокумента
ПОМЕСТИТЬ ТаблПриход
ИЗ
	Документ.ПоступлениеТоваров КАК ПоступлениеТоваров
ГДЕ
	ПоступлениеТоваров.Проведен = ИСТИНА
	И ПоступлениеТоваров.Контрагент В
			(ВЫБРАТЬ
				ТаблДолги.Поставщик
			ИЗ
				ТаблДолги КАК ТаблДолги)
	;
	
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
	ТаблПриход.Дата КАК Дата,
	ТаблПриход.Ссылка КАК Ссылка,
	ТаблПриход.Магазин КАК Магазин,
	ТаблПриход.Контрагент КАК Контрагент,
	ТаблПриход.СуммаДокумента КАК СуммаДокумента,
	СУММА(ТаблПриходКопия.СуммаДокумента) КАК СуммаПосле
ПОМЕСТИТЬ ТаблИтоги
ИЗ
	ТаблПриход КАК ТаблПриход
		ВНУТРЕННЕЕ СОЕДИНЕНИЕ ТаблПриход КАК ТаблПриходКопия
		ПО ТаблПриход.Магазин = ТаблПриходКопия.Магазин
			И ТаблПриход.Контрагент = ТаблПриходКопия.Контрагент
			И ТаблПриход.Магазин = ТаблПриходКопия.Магазин
			И ТаблПриход.Дата <= ТаблПриходКопия.Дата

СГРУППИРОВАТЬ ПО
	ТаблПриход.Дата,
	ТаблПриход.Ссылка,
	ТаблПриход.Магазин,
	ТаблПриход.Контрагент,
	ТаблПриход.СуммаДокумента
;

Результат

Дата	Ссылка	Магазин	Контрагент	СуммаДокумента	СуммаПосле
01.04.2020	Поступление товаров 0000-000001 от 01.04.2020	Магазин №1	ООО Поставщик	15 000	69 500
10.04.2020	Поступление товаров 0000-000002 от 10.04.2020	Магазин №1	ООО Поставщик	32 000	54 500
20.04.2020	Поступление товаров 0000-000003 от 20.04.2020	Магазин №1	ООО Поставщик	22 500	22 500

Комментарий

На данном этапе мы сделаем ключевой шаг для решения задачи.

Мы выбрали данные из временной таблицы ТаблИтоги и к ней приклеили ее же копию с помощью конструкции ВНУТРЕННЕЕ СОЕДИНЕНИЕ.

Внутреннее соединение отсекает записи двух таблиц, если эти записи не удовлетворяют условиям связи таблиц, одно из которых выглядит в запросе так:

ТаблПриход.Дата <= ТаблПриходКопия.Дата

В полученной таблице поле «СуммаПосле» означает сумму всех документов начиная с даты документа текущей строки и заканчивая датой последнего документа. Проще говоря, это сумма поступлений с текущего по последний включительно.

По сути мы получили в запросе сумму документов нарастающим итогом.

Этап 4. Финальный запрос

ВЫБРАТЬ
	РасчетыСПоставщикамиОстатки.Магазин КАК Магазин,
	РасчетыСПоставщикамиОстатки.Поставщик КАК Поставщик,
	-РасчетыСПоставщикамиОстатки.СуммаОстаток КАК СуммаДолга
ПОМЕСТИТЬ ТаблДолги
ИЗ
	РегистрНакопления.РасчетыСПоставщиками.Остатки КАК РасчетыСПоставщикамиОстатки
ГДЕ
	РасчетыСПоставщикамиОстатки.СуммаОстаток < 0
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
	ПоступлениеТоваров.Дата КАК Дата,
	ПоступлениеТоваров.Ссылка КАК Ссылка,
	ПоступлениеТоваров.Магазин КАК Магазин,
	ПоступлениеТоваров.Контрагент КАК Контрагент,
	ПоступлениеТоваров.СуммаДокумента КАК СуммаДокумента
ПОМЕСТИТЬ ТаблПриход
ИЗ
	Документ.ПоступлениеТоваров КАК ПоступлениеТоваров
ГДЕ
	ПоступлениеТоваров.Проведен = ИСТИНА
	И ПоступлениеТоваров.Контрагент В
			(ВЫБРАТЬ
				ТаблДолги.Поставщик
			ИЗ
				ТаблДолги КАК ТаблДолги)
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
	ТаблПриход.Дата КАК Дата,
	ТаблПриход.Ссылка КАК Ссылка,
	ТаблПриход.Магазин КАК Магазин,
	ТаблПриход.Контрагент КАК Контрагент,
	ТаблПриход.СуммаДокумента КАК СуммаДокумента,
	СУММА(ТаблПриходКопия.СуммаДокумента) КАК СуммаПосле
ПОМЕСТИТЬ ТаблИтоги
ИЗ
	ТаблПриход КАК ТаблПриход
		ВНУТРЕННЕЕ СОЕДИНЕНИЕ ТаблПриход КАК ТаблПриходКопия
		ПО ТаблПриход.Магазин = ТаблПриходКопия.Магазин
			И ТаблПриход.Контрагент = ТаблПриходКопия.Контрагент
			И ТаблПриход.Магазин = ТаблПриходКопия.Магазин
			И ТаблПриход.Дата <= ТаблПриходКопия.Дата

СГРУППИРОВАТЬ ПО
	ТаблПриход.Дата,
	ТаблПриход.Ссылка,
	ТаблПриход.Магазин,
	ТаблПриход.Контрагент,
	ТаблПриход.СуммаДокумента
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
	ТаблИтоги.Дата КАК Дата,
	ТаблИтоги.Магазин КАК Магазин,
	ТаблИтоги.Контрагент КАК Контрагент,
	ТаблИтоги.Ссылка КАК Ссылка,
	ТаблИтоги.СуммаДокумента КАК СуммаДокумента,
	ТаблИтоги.СуммаПосле КАК СуммаПосле,
	ТаблДолги.СуммаДолга КАК СуммаДолга,
	ТаблИтоги.СуммаПосле - ТаблДолги.СуммаДолга КАК Разница,
	ВЫБОР
		КОГДА ТаблИтоги.СуммаПосле > ТаблДолги.СуммаДолга
			ТОГДА ВЫБОР
					КОГДА ТаблИтоги.СуммаПосле - ТаблДолги.СуммаДолга > ТаблИтоги.СуммаДокумента
						ТОГДА 0
					ИНАЧЕ ТаблИтоги.СуммаДокумента - (ТаблИтоги.СуммаПосле - ТаблДолги.СуммаДолга)
				КОНЕЦ
		ИНАЧЕ ТаблИтоги.СуммаДокумента
	КОНЕЦ КАК ДолгПоДокументу
ИЗ
	ТаблДолги КАК ТаблДолги
		ЛЕВОЕ СОЕДИНЕНИЕ ТаблИтоги КАК ТаблИтоги
		ПО (ТаблИтоги.Контрагент = ТаблДолги.Поставщик)
			И (ТаблИтоги.Магазин = ТаблДолги.Магазин)

Результат

Дата	Магазин	Контрагент	Ссылка	СуммаДокумента	СуммаПосле	СуммаДолга	Разница	ДолгПоДокументу
01.04.2020	Магазин №1	ООО Поставщик	Поступление товаров 0000-000001 от 01.04.2020	15 000	69 500	61 500	8 000	7 000
10.04.2020	Магазин №1	ООО Поставщик	Поступление товаров 0000-000002 от 10.04.2020	32 000	54 500	61 500	-7 000	32 000
20.04.2020	Магазин №1	ООО Поставщик	Поступление товаров 0000-000003 от 20.04.2020	22 500	22 500	61 500	-39 000	22 500

Комментарий

В финальном пакете запроса мы рассчитываем поле ДолгПоДокументу.

Алгоритм расчета в переводе на человеческий язык таков:

если поле СуммаПосле (сумма всех документов с даты текущей строки включительно) больше СуммаДолга, то очевидно, что какие-то из документов были оплачены, раз их сумма выше суммы долга.

Разберем на примере первой строки:

Разница = 69500 – 61500 = 8000.

Если полученная сумма разницы (8000) не превышает сумму текущего документа (15000), то Долг по документу будет равен 15000 – 8000 (сумма разницы) = 7000.

По второй строке СуммаПосле (54500) меньше общей суммы долга (61500), а значит вся сумма документа (32000) идет в поле Долг по документу.

По третьей строке СуммаПосле (22500) меньше общей суммы долга (61500), а значит вся сумма документа (22500) идет в поле Долг по документу.

Подведение итогов

Использовав прием расчета суммы нарастающим итогом по дате документа и последующим сопоставлением этой суммы с суммой долга, мы решили задачу расчета долга в разрезе документов по ФИФО.

Конечно же, в реальной работе надо иметь ввиду, что клиент может возвращать товары поставщику, делать корректировки долга и прочее.