Отчеты с использованием "рассчета себестоимости" по оперативной БД. : Супермаг Плюс (Супермаг 2000)

deucel · █ 05.02.2009 18:48

Цитата:

Andrew_Konev ➤ те же остатки по поставщикам - вообще нереально посчитать по другому на базе, которую 4 года "портили". особенно, если базу обрезать.

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

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

Раз была затронута тема с поставщиками, то еще один запросик попытки вычислить остатки по магазинам в закупочных ценах по поставщикам и поставкам (относительно точно и быстро). За основу был взят запрос в начале темы. Специально не группировал, чтоб было видно привязку к партиям по датам.

Код:

SELECT   z.LOCATION, z.article, z.createdat, NVL (z.clientindex, -1) clientindex, z.goods, 
         CASE
            WHEN z.quantity IS NULL
               THEN z.goods
            WHEN z.goods2 - z.quantity >= z.goods
               THEN NULL
            WHEN z.goods2 - z.quantity < z.goods AND z.goods >= z.goods2
               THEN z.quantity
            WHEN z.goods2 - z.quantity < z.goods AND z.goods < z.goods2
               THEN z.quantity - (z.goods2 - z.goods)
         END quantity,
         (CASE
             WHEN z.quantity IS NULL
                THEN z.goods * z.itemprice
             WHEN z.goods2 - z.quantity >= z.goods
                THEN NULL
             WHEN z.goods2 - z.quantity < z.goods AND z.goods >= z.goods2
                THEN z.quantity * z.itemprice
             WHEN z.goods2 - z.quantity < z.goods AND z.goods < z.goods2
                THEN (z.quantity - (z.goods2 - z.goods)) * z.itemprice
          END
         ) totalprice
    FROM (SELECT g.LOCATION, g.article, s.clientindex, s.createdat, g.goods,
                 CASE
                    WHEN COUNT (s.quantity) OVER (PARTITION BY g.LOCATION, g.article) = 1
                       THEN g.goods
                    ELSE s.quantity
                 END quantity, NVL (s.totalprice / s.quantity,
                                    NVL ((SELECT p.price
                                            FROM smprices p
                                           WHERE p.storeloc = g.LOCATION AND p.article = g.article AND p.pricetype = 6), 0)) itemprice,
                 CASE
                    WHEN COUNT (s.quantity) OVER (PARTITION BY g.LOCATION, g.article) = 1
                       THEN g.goods
                    ELSE SUM (s.quantity) OVER (PARTITION BY g.LOCATION, g.article ORDER BY s.createdat DESC)
                 END goods2
            FROM (SELECT storeloc LOCATION, article, quantity goods
                    FROM smgoods g
                   WHERE g.quantity > 0 AND EXISTS (SELECT /*+ INDEX (C)*/
                                                           1
                                                      FROM smcard c
                                                     WHERE c.datatype = 0 AND c.article = g.article)) g,
                 (SELECT   /*+ INDEX(D)*/
                           d.locationto LOCATION, d.clientindex, d.createdat, s.article, SUM (s.quantity) quantity, SUM (s.totalprice) totalprice
                      FROM smdocuments d, smspec s
                     WHERE d.doctype = 'WI' AND d.createdat > SYSDATE - 90 AND d.docstate = 3 AND d.opcode != 9 AND s.doctype = d.doctype AND s.docid = d.ID
                  GROUP BY d.locationto, d.clientindex, d.createdat, s.article) s
           WHERE g.LOCATION = s.LOCATION(+) AND g.article = s.article(+)
          UNION ALL
          SELECT g.LOCATION, g.article, NULL clientindex, NULL createdat, g.goods, NULL quantity,
                 NVL ((SELECT p.price
                         FROM smprices p
                        WHERE p.storeloc = g.LOCATION AND p.article = g.article AND p.pricetype = 6), 0) totalprice, NULL goods2
            FROM (SELECT storeloc LOCATION, article, quantity goods
                    FROM smgoods g
                   WHERE g.quantity < 0 AND EXISTS (SELECT /*+ INDEX (C)*/
                                                           1
                                                      FROM smcard c
                                                     WHERE c.datatype = 0 AND c.article = g.article)) g) z
   WHERE z.quantity IS NULL OR z.goods2 - z.quantity < z.goods

Чем меньше магазинов - тем быстрее отработает (сейчас по всем)
Чем больше период (сейчас 90 дней) - тем больше точность данных привязки к партиям.
Отрицательные остатки изначально к поставщикам не привязываем и используем pricetype = 6, это же относится и к положительным если не обнаружено ни одной поставки (если и это невозможно то цена 0). Если были поставки которых не достаточно для перекрытия остатка, то не перекрытый приходами остаток будет привязан к цене и соотв. поставщику самой ранней по дате.

konst · █ 06.02.2009 08:12

Небольшое разъяснение - pricetype = 6 - в нашем случае это еще один вид розничной цены с 0 наценкой - привязанный ко всем местам хранения - Цена Последнего Прихода.
В первую очередь данный вид цены используется для проведения инвентаризаций ... и не только.

deucel · █ 05.03.2009 17:49

Еще один отчетик, назовем его Реализация за месяц в закупочных ценах по партиям с учетом остатка:

специально не убирал промежуточные и контрольные данные, чтоб проще было понять логику

Код:

SELECT *
  FROM (SELECT s2.*, SUM (NVL (s2.q1, 0)) OVER (PARTITION BY s2.LOCATION, s2.article) t2
          FROM (SELECT s.LOCATION, s.article, s.clientindex, s.createdat,
                       CASE
-- Если только одна запись для артикула (с приходами или без).
                       WHEN COUNT (ROWNUM) OVER (PARTITION BY s.LOCATION, s.article) = 1
                             THEN s.goods - s.goods_in
-- Если нарастающий итог приходов больше.
                       WHEN SUM (s.quantity) OVER (PARTITION BY s.LOCATION, s.article ORDER BY s.createdat DESC, s.clientindex) - s.quantity >= s.goods
                             THEN NULL
-- Если нарастающий итог приходов меньше или равен остатку, но есть еще приходы.
                       WHEN SUM (s.quantity) OVER (PARTITION BY s.LOCATION, s.article ORDER BY s.createdat DESC, s.clientindex) <= s.goods_in
                       AND ROW_NUMBER () OVER (PARTITION BY s.LOCATION, s.article ORDER BY s.createdat DESC, s.clientindex) <
                                                                                                                   COUNT (ROWNUM) OVER (PARTITION BY s.LOCATION, s.article)
                             THEN NULL
-- Если сумма нарастающего итога приходов и равна остатку.
                       WHEN SUM (s.quantity) OVER (PARTITION BY s.LOCATION, s.article ORDER BY s.createdat DESC, s.clientindex) = s.goods
                             THEN CASE
                                    WHEN SUM (s.quantity) OVER (PARTITION BY s.LOCATION, s.article ORDER BY s.createdat DESC, s.clientindex) - s.quantity >= s.goods_in
                                       THEN s.quantity
                                    ELSE s.goods - s.goods_in
                                 END
-- Если последняя запись нарастающего итога приходов меньше общего остатка.
                       WHEN SUM (s.quantity) OVER (PARTITION BY s.LOCATION, s.article ORDER BY s.createdat DESC, s.clientindex) =
                                                                                                                 SUM (s.quantity) OVER (PARTITION BY s.LOCATION, s.article)
                       AND SUM (s.quantity) OVER (PARTITION BY s.LOCATION, s.article ORDER BY s.createdat DESC, s.clientindex) < s.goods
                             THEN CASE
                                    WHEN SUM (s.quantity) OVER (PARTITION BY s.LOCATION, s.article ORDER BY s.createdat DESC, s.clientindex) - s.quantity >= s.goods_in
                                       THEN s.quantity + (s.goods - SUM (s.quantity) OVER (PARTITION BY s.LOCATION, s.article ORDER BY s.createdat DESC, s.clientindex))
                                    ELSE s.goods - s.goods_in
                                 END
-- Если последняя запись нарастающего итога приходов и больше остатка.
                       WHEN SUM (s.quantity) OVER (PARTITION BY s.LOCATION, s.article ORDER BY s.createdat DESC, s.clientindex) =
                                                                                                                 SUM (s.quantity) OVER (PARTITION BY s.LOCATION, s.article)
                       AND SUM (s.quantity) OVER (PARTITION BY s.LOCATION, s.article ORDER BY s.createdat DESC, s.clientindex) > s.goods
                             THEN CASE
                                    WHEN SUM (s.quantity) OVER (PARTITION BY s.LOCATION, s.article ORDER BY s.createdat DESC, s.clientindex) - s.quantity >= s.goods_in
                                       THEN s.quantity - (SUM (s.quantity) OVER (PARTITION BY s.LOCATION, s.article ORDER BY s.createdat DESC, s.clientindex) - s.goods)
                                    ELSE s.goods - s.goods_in
                                 END
-- Если сумма нарастающего итога меньше остатка
                       WHEN SUM (s.quantity) OVER (PARTITION BY s.LOCATION, s.article ORDER BY s.createdat DESC, s.clientindex) < s.goods
                             THEN CASE
                                    WHEN SUM (s.quantity) OVER (PARTITION BY s.LOCATION, s.article ORDER BY s.createdat DESC, s.clientindex) - s.quantity >= s.goods_in
                                       THEN s.quantity
                                    ELSE SUM (s.quantity) OVER (PARTITION BY s.LOCATION, s.article ORDER BY s.createdat DESC, s.clientindex) - s.goods_in
                                 END
-- Если сумма нарастающего итога больше остатка
                       WHEN SUM (s.quantity) OVER (PARTITION BY s.LOCATION, s.article ORDER BY s.createdat DESC, s.clientindex) > s.goods
                             THEN CASE
                                    WHEN SUM (s.quantity) OVER (PARTITION BY s.LOCATION, s.article ORDER BY s.createdat DESC, s.clientindex) - s.quantity >= s.goods_in
                                       THEN s.quantity - (SUM (s.quantity) OVER (PARTITION BY s.LOCATION, s.article ORDER BY s.createdat DESC, s.clientindex) - s.goods)
                                    ELSE s.goods - s.goods_in
                                 END
--                          ELSE s.quantity
-- >>>
                       END q1,
                       
-- Какое правило отработало
                       CASE
-- Если только одна запись для артикула (с приходами или без).
                       WHEN COUNT (ROWNUM) OVER (PARTITION BY s.LOCATION, s.article) = 1
                             THEN '<< 1'
-- Если нарастающий итог приходов больше.
                       WHEN SUM (s.quantity) OVER (PARTITION BY s.LOCATION, s.article ORDER BY s.createdat DESC, s.clientindex) - s.quantity >= s.goods
                             THEN '<< 2'
-- Если нарастающий итог приходов меньше или равен остатку, но есть еще приходы.
                       WHEN SUM (s.quantity) OVER (PARTITION BY s.LOCATION, s.article ORDER BY s.createdat DESC, s.clientindex) <= s.goods_in
                       AND ROW_NUMBER () OVER (PARTITION BY s.LOCATION, s.article ORDER BY s.createdat DESC, s.clientindex) <
                                                                                                                   COUNT (ROWNUM) OVER (PARTITION BY s.LOCATION, s.article)
                             THEN '<< 3'
-- Если сумма нарастающего итога приходов и равна остатку.
                       WHEN SUM (s.quantity) OVER (PARTITION BY s.LOCATION, s.article ORDER BY s.createdat DESC, s.clientindex) = s.goods
                             THEN CASE
                                    WHEN SUM (s.quantity) OVER (PARTITION BY s.LOCATION, s.article ORDER BY s.createdat DESC, s.clientindex) - s.quantity >= s.goods_in
                                       THEN '<< 4a'
                                    ELSE '<< 4b'
                                 END
-- Если последняя запись нарастающего итога приходов меньше общего остатка.
                       WHEN SUM (s.quantity) OVER (PARTITION BY s.LOCATION, s.article ORDER BY s.createdat DESC, s.clientindex) =
                                                                                                                 SUM (s.quantity) OVER (PARTITION BY s.LOCATION, s.article)
                       AND SUM (s.quantity) OVER (PARTITION BY s.LOCATION, s.article ORDER BY s.createdat DESC, s.clientindex) < s.goods
                             THEN CASE
                                    WHEN SUM (s.quantity) OVER (PARTITION BY s.LOCATION, s.article ORDER BY s.createdat DESC, s.clientindex) - s.quantity >= s.goods_in
                                       THEN '<< 5a'
                                    ELSE '<< 5b'
                                 END
-- Если последняя запись нарастающего итога приходов и больше остатка.
                       WHEN SUM (s.quantity) OVER (PARTITION BY s.LOCATION, s.article ORDER BY s.createdat DESC, s.clientindex) =
                                                                                                                 SUM (s.quantity) OVER (PARTITION BY s.LOCATION, s.article)
                       AND SUM (s.quantity) OVER (PARTITION BY s.LOCATION, s.article ORDER BY s.createdat DESC, s.clientindex) > s.goods
                             THEN CASE
                                    WHEN SUM (s.quantity) OVER (PARTITION BY s.LOCATION, s.article ORDER BY s.createdat DESC, s.clientindex) - s.quantity >= s.goods_in
                                       THEN '<< 6a'
                                    ELSE '<< 6b'
                                 END
-- Если сумма нарастающего итога меньше остатка
                       WHEN SUM (s.quantity) OVER (PARTITION BY s.LOCATION, s.article ORDER BY s.createdat DESC, s.clientindex) < s.goods
                             THEN CASE
                                    WHEN SUM (s.quantity) OVER (PARTITION BY s.LOCATION, s.article ORDER BY s.createdat DESC, s.clientindex) - s.quantity >= s.goods_in
                                       THEN '<< 7a'
                                    ELSE '<< 7b'
                                 END
-- Если сумма нарастающего итога больше остатка
                       WHEN SUM (s.quantity) OVER (PARTITION BY s.LOCATION, s.article ORDER BY s.createdat DESC, s.clientindex) > s.goods
                             THEN CASE
                                    WHEN SUM (s.quantity) OVER (PARTITION BY s.LOCATION, s.article ORDER BY s.createdat DESC, s.clientindex) - s.quantity >= s.goods_in
                                       THEN '<< 8a'
                                    ELSE '<< 8b'
                                 END
                          ELSE '<< 9'
                       END prav,
                       CASE
                          WHEN COUNT (s.quantity) OVER (PARTITION BY s.LOCATION, s.article) = 1
                             THEN s.goods
                          ELSE s.quantity
                       END quantity,
                       NVL (s.totalprice / DECODE (s.quantity, 0, NULL),
                            NVL ((SELECT p.price
                                    FROM smprices p
                                   WHERE p.storeloc = s.LOCATION AND p.article = s.article AND p.pricetype = 6), 0)) itemprice, s.goods,
                       CASE
                          WHEN COUNT (s.quantity) OVER (PARTITION BY s.LOCATION, s.article) = 1
                             THEN s.goods
                          ELSE SUM (s.quantity) OVER (PARTITION BY s.LOCATION, s.article ORDER BY s.createdat DESC, s.clientindex)
                       END goods2,
                       s.goods_in, s.goods_out
                  FROM (SELECT s.LOCATION, s.article, d.clientindex, d.createdat, NVL (d.quantity, 0) quantity, s.goods_out + NVL (g.quantity, 0) goods,
                               NVL (g.quantity, 0) goods_in, s.goods_out, NVL (d.totalprice, 0) totalprice
                          FROM (SELECT   /*+ INDEX(D) INDEX(S)*/
                                         d.locationfrom LOCATION, s.article, SUM (s.quantity) goods_out
                                    FROM smspec s, smdocuments d
                                   WHERE s.doctype = d.doctype AND s.docid = d.ID AND d.doctype = 'CS' AND d.createdat BETWEEN TRUNC (SYSDATE, 'MON') AND SYSDATE
                                GROUP BY d.locationfrom, s.article) s,
                               (SELECT storeloc, article, quantity
                                  FROM smgoods
                                 WHERE quantity > 0) g,
                               (SELECT   /*+ INDEX(D) INDEX(S)*/
                                         d.locationto LOCATION, d.clientindex, d.createdat, s.article, SUM (s.quantity) quantity, SUM (s.totalprice) totalprice
                                    FROM smdocuments d, smspec s
                                   WHERE d.doctype = 'WI'
                                     AND d.createdat > SYSDATE - (90 + (TRUNC (SYSDATE) - TRUNC (SYSDATE, 'MON')))
                                     AND d.docstate = 3
                                     AND d.opcode != 9
                                     AND s.doctype = d.doctype
                                     AND s.docid = d.ID
                                GROUP BY d.locationto, d.clientindex, d.createdat, s.article) d
                         WHERE s.LOCATION = g.storeloc(+) AND s.article = g.article(+) AND s.LOCATION = d.LOCATION(+) AND s.article = d.article(+)) s) s2
         WHERE s2.q1 IS NOT NULL)
-- WHERE t2 != goods_out

q1 - кол-во которое берем из партии
prav - правило q1
quantity - кол-во в приходе
goods - кол-во товара для раскидки по партиям (goods_in + goods_out, постоянное значение)
goods2 - нарастающий итог по партиям (sum(q1))
goods_in - остаток (постоянное значение)
goods_out - проданное кол-во (постоянное значение)
t2 - сумма q1 (проверочное значение, использовалось для отладки - sum(q1) = goods_out т.е., что у нас есть все кол-во проданного товара)

для получения конечного результата нас будет интересовать sum(q1*itemprice)

deucel · █ 05.03.2009 17:51

Единственное, что не могу пока достойно реализовать в sql - это динамический отбор документов для раскидки по партиям.
Мож кто мысль подкинет интересную.
:)

Mtirt · █ 19.03.2009 11:48

Уточни пожалуйста, то ты понимаешь под динамическим отбором документов?

deucel · █ 19.03.2009 12:08

Не указание определенного диапазона дат, а до момента пока не закроется нужное количество.
У меня есть несколько вариантов как можно это сделать (временные таблицы, процедуры...), но скорость работы при этом заметно падает.
В текущем варианте довольно приемлемая скорость работы при условии, что для большего процента товаров (>95%) мы находим все необходимые партии (конечно с излишней выборкой документов), остальное округляется до последней партии или цены последнего прихода (p.pricetype = 6, можно былоб и запросом находить но важна скорость).
Соотв. для принятия решений результаты вполне подходят.