Что такое нарастающие итоги можно посмотреть здесь: //infostart.ru/public/61295/
Постановка задачи.
Менеджерам компании понадобилось знать, что нужно заказать у поставщиков для клиентов и видеть что уже едет под заказы клиентов. Они захотели использовать метод ФИФО, то есть кто раньше заказал тому и раньше приедет товар, дата отгрузки товаров и дата поставки товаров из заказов не учитывается. Так же считается что все заказы имеют уникальную дату. Операции учета ведутся в Управлении торговлей версии 11.0.9.8.
Задача специально упрощена для демонстрации объединения таблиц, в противном случае решение будет громоздким и сложным для понимания с первого взгляда.
1. Для начало нужно сформировать список номенклатуры, которую необходимо заказать и которая ожидается к поступлению. Для чего это нужно? - Справочник номенклатуры содержит более 100 тыс. наименований и нужно как-то ограничить выборку из виртуальных таблиц регистров.
ВЫБРАТЬ
Номенклатура
ПОМЕСТИТЬ СпрНоменклатура
ИЗ
РегистрНакопления.СвободныеОстатки.Остатки
ГДЕ
ВНаличииОстаток - ВРезервеОстаток < 0
ОБЪЕДИНИТЬ
ВЫБРАТЬ
Номенклатура
ИЗ
РегистрНакопления.ЗаказыПоставщикам.Остатки
ИНДЕКСИРОВАТЬ ПО
Номенклатура;
Конечно можно было бы использовать РегистрНакопления.ЗаказыКлиентов.Остатки вместо РегистрНакопления.СвободныеОстатки.Остатки, но ведь нужно же выбрать только номенклатуру, которую нужно заказать.
2. Далее выбираем остатки заказов поставщиков и клиентов.
ВЫБРАТЬ
ОстаткиЗаказов.ЗаказПоставщику.Дата КАК Период,
ОстаткиЗаказов.Номенклатура,
ОстаткиЗаказов.ЗаказПоставщику,
ОстаткиЗаказов.КОформлениюОстаток
ПОМЕСТИТЬ ОстаткиЗаказовПоставщиков
ИЗ
РегистрНакопления.ЗаказыПоставщикам.Остатки(, Номенклатура В (ВЫБРАТЬ Номенклатура ИЗ СпрНоменклатура)) КАК ОстаткиЗаказов
;
ВЫБРАТЬ
ОстаткиЗаказов.ЗаказКлиента.Дата КАК Период,
ОстаткиЗаказов.Номенклатура,
ОстаткиЗаказов.ЗаказКлиента,
ОстаткиЗаказов.ЗаказаноОстаток
ПОМЕСТИТЬ ОстаткиЗаказовКлиентов
Из
РегистрНакопления.ЗаказыКлиентов.Остатки(, ЗаказКлиента.Статус НЕ В (ЗНАЧЕНИЕ(Перечисление.СтатусыЗаказовКлиентов.НеСогласован), ЗНАЧЕНИЕ(Перечисление.СтатусыЗаказовКлиентов.Согласован))
И Номенклатура В (ВЫБРАТЬ Номенклатура ИЗ СпрНоменклатура)) КАК ОстаткиЗаказов
;
Используем статусы заказов клиентов для отсечения заказов которые не делают движений в РегистрНакопления.СвободныеОстатки.Остатки.
3. Получаем для остатков заказов поставщиков и клиентов нарастаюище итоги.
ВЫБРАТЬ
ОстаткиПоЗаказам.Номенклатура,
ОстаткиПоЗаказам.ЗаказПоставщику,
ОстаткиПоЗаказам.КОформлениюОстаток,
СУММА(ОстаткиПоЗаказамКопия.КОформлениюОстаток) КАК СуммаПосле,
СУММА(ОстаткиПоЗаказамКопия.КОформлениюОстаток) - ОстаткиПоЗаказам.КОформлениюОстаток КАК СуммаДо
ПОМЕСТИТЬ КОформлениюНарастающие
ИЗ
ОстаткиЗаказовПоставщиков КАК ОстаткиПоЗаказам
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ОстаткиЗаказовПоставщиков КАК ОстаткиПоЗаказамКопия
ПО ОстаткиПоЗаказам.Номенклатура = ОстаткиПоЗаказамКопия.Номенклатура
И ОстаткиПоЗаказам.Период >= ОстаткиПоЗаказамКопия.Период
СГРУППИРОВАТЬ ПО
ОстаткиПоЗаказам.Номенклатура,
ОстаткиПоЗаказам.ЗаказПоставщику,
ОстаткиПоЗаказам.КОформлениюОстаток
;
ВЫБРАТЬ
ОстаткиПоЗаказам.Номенклатура,
ОстаткиПоЗаказам.ЗаказКлиента,
ОстаткиПоЗаказам.ЗаказаноОстаток,
СУММА(ОстаткиПоЗаказамКопия.ЗаказаноОстаток) КАК СуммаПосле,
СУММА(ОстаткиПоЗаказамКопия.ЗаказаноОстаток) - ОстаткиПоЗаказам.ЗаказаноОстаток КАК СуммаДо
ПОМЕСТИТЬ ОстаткиНарастающие
ИЗ
ОстаткиЗаказовКлиентов КАК ОстаткиПоЗаказам
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ОстаткиЗаказовКлиентов КАК ОстаткиПоЗаказамКопия
ПО ОстаткиПоЗаказам.Номенклатура = ОстаткиПоЗаказамКопия.Номенклатура
И ОстаткиПоЗаказам.Период >= ОстаткиПоЗаказамКопия.Период
СГРУППИРОВАТЬ ПО
ОстаткиПоЗаказам.Номенклатура,
ОстаткиПоЗаказам.ЗаказКлиента,
ОстаткиПоЗаказам.ЗаказаноОстаток
;
4. Теперь можно было бы попытаться объединить эти таблицы с помощью Полного, Левого и прочего соединения, но когда таблиц нарастающих итогов больше 2 это уже очень проблематично. Но есть довольно простое решение - достаточно выбрать номенклатуру с уникальными значениями "СуммаПосле" из нарастающих итогов обеих таблиц и потом к этой таблице присоединить все что нужно.
ВЫБРАТЬ
ОстаткиНарастающие.Номенклатура,
ОстаткиНарастающие.СуммаПосле КАК Точка
ПОМЕСТИТЬ ТочкиПересечений
ИЗ
ОстаткиНарастающие
ОБЪЕДИНИТЬ
ВЫБРАТЬ
Номенклатура,
СуммаПосле
ИЗ
КОформлениюНарастающие
;
5. Получим общие нарастающие итоги заказов поставщиков и заказов клиентов.
ВЫБРАТЬ
ТочкиПересечений.Номенклатура,
ТочкиПересечений.Точка,
КОформлениюНарастающие.ЗаказПоставщику,
ОстаткиНарастающие.ЗаказКлиента,
ОстаткиНарастающие.ЗаказаноОстаток,
ОстаткиНарастающие.СуммаПосле,
ОстаткиНарастающие.СуммаДо
ПОМЕСТИТЬ ОстаткиПоЗаказамНаростающие
ИЗ
ТочкиПересечений КАК ТочкиПересечений
ЛЕВОЕ СОЕДИНЕНИЕ КОформлениюНарастающие КАК КОформлениюНарастающие
ПО КОформлениюНарастающие.Номенклатура = ТочкиПересечений.Номенклатура
И КОформлениюНарастающие.СуммаПосле >= ТочкиПересечений.Точка
И КОформлениюНарастающие.СуммаДо < ТочкиПересечений.Точка
ЛЕВОЕ СОЕДИНЕНИЕ ОстаткиНарастающие КАК ОстаткиНарастающие
ПО ОстаткиНарастающие.Номенклатура = ТочкиПересечений.Номенклатура
И ОстаткиНарастающие.СуммаПосле >= ТочкиПересечений.Точка
И ОстаткиНарастающие.СуммаДо < ТочкиПересечений.Точка
;
6. Следующие 2 запроса формирует решение поставленной задачи с дополнительными полями "ВНаличии", "ВПеремещениях", "ВЗаказахПоставщику" и "НужноЗаказать".
ВЫБРАТЬ
СпрНоменклатура.Номенклатура,
IsNull(ОстаткиТоваров.ВНаличииОстаток, 0) КАК ВНаличии,
IsNull(ОстаткиТоваров.ВРезервеОстаток, 0) КАК ВРезерве,
IsNull(ТоварыВДвиженииПеремещения.КПоступлениюОстаток, 0) КАК ВПеремещениях,
IsNull(ТоварыВДвиженииЗаказы.КПоступлениюОстаток, 0) КАК ВЗаказахПоставщику,
IsNull(ОстаткиТоваров.ВНаличииОстаток, 0)
+ IsNull(ТоварыВДвиженииПеремещения.КПоступлениюОстаток, 0)
+ IsNull(ТоварыВДвиженииЗаказы.КПоступлениюОстаток, 0) КАК Доступно
ПОМЕСТИТЬ ТаблицаНаличия
ИЗ
СпрНоменклатура КАК СпрНоменклатура
ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.СвободныеОстатки.Остатки(,Номенклатура В (ВЫБРАТЬ Номенклатура ИЗ СпрНоменклатура)) КАК ОстаткиТоваров
ПО ОстаткиТоваров.Номенклатура = СпрНоменклатура.Номенклатура
ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ТоварыКПоступлению.Остатки(, ТипЗначения(ДокументПоступления) <> Тип(Документ.ЗаказПоставщику)
И Номенклатура В (ВЫБРАТЬ Номенклатура ИЗ СпрНоменклатура)) КАК ТоварыВДвиженииПеремещения
ПО ТоварыВДвиженииПеремещения.Номенклатура = СпрНоменклатура.Номенклатура
ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ТоварыКПоступлению.Остатки(, ТипЗначения(ДокументПоступления) = Тип(Документ.ЗаказПоставщику)
И Номенклатура В (ВЫБРАТЬ Номенклатура ИЗ СпрНоменклатура)) КАК ТоварыВДвиженииЗаказы
ПО ТоварыВДвиженииЗаказы.Номенклатура = СпрНоменклатура.Номенклатура
;
ВЫБРАТЬ
ОстаткиПоЗаказам.ЗаказПоставщику,
ОстаткиПоЗаказам.ЗаказКлиента,
ОстаткиПоЗаказам.Номенклатура,
ОстаткиПоЗаказам.ЗаказаноОстаток КАК Количество,
ТаблицаНаличия.ВНаличии,
ТаблицаНаличия.ВПеремещениях,
ТаблицаНаличия.ВЗаказахПоставщику,
ВЫБОР
КОГДА ТаблицаНаличия.Доступно > ОстаткиПоЗаказам.СуммаПосле
ТОГДА 0
КОГДА ТаблицаНаличия.Доступно > ОстаткиПоЗаказам.СуммаДо
ТОГДА ОстаткиПоЗаказам.СуммаПосле - ТаблицаНаличия.Доступно
ИНАЧЕ ОстаткиПоЗаказам.ЗаказаноОстаток
КОНЕЦ КАК НужноЗаказать
ИЗ
ОстаткиПоЗаказамНаростающие КАК ОстаткиПоЗаказам
ЛЕВОЕ СОЕДИНЕНИЕ ТаблицаНаличия КАК ТаблицаНаличия
ПО ТаблицаНаличия.Номенклатура = ОстаткиПоЗаказам.Номенклатура
И ТаблицаНаличия.Доступно < ОстаткиПоЗаказам.СуммаПосле
ГДЕ
ТаблицаНаличия.Доступно - ТаблицаНаличия.ВРезерве < 0
УПОРЯДОЧИТЬ ПО ОстаткиПоЗаказам.Номенклатура, ОстаткиПоЗаказам.Точка
;
В результате получится что-то типа этого очета (с двумя вариантами внутри):
Послесловие.
Если взять и немножко провести апгрейд технического задания - то можно получить довольно хороший и простой механизм для создания заказов поставщикам, с учетом даты доставки, даты отгрузки, графиками движений между складами, заказами для внутреннего потребления, заказами на сборку и заказами для перемещения. Так же с подбором подходящего поставщика по цене\скорость доставки, если используется импорт цен поставщиков в базу. И будет выглядеть это уже как-то так:
