Ile zostało na pozycji zamówienia: pula dokumentów bez przypisania rozłożona po kolei w T-SQL

Autor: Adam Siemiątkowski, SoftwareStudio Group Sp. z o.o.

Bilans pozycji zamówienia w T-SQL komplikuje się, gdy część dokumentów przyjęcia wskazuje pozycję zamówienia, a część tylko produkt. Zapytanie ma wtedy powiedzieć, ile jeszcze można przyjąć na każdej pozycji, i nie policzyć żadnego dokumentu dwa razy.

Dokumenty przypisane odejmujemy od własnej pozycji, a pozostałe, bez odrzuconych, tworzą pulę produktu. Suma okienkowa z ramką ROWS rozkłada pulę po kolei na wolne miejsca pozycji, obcięte wcześniej do zera. W danych próbnych na trzeciej pozycji produktu ART-A zostaje 10 sztuk, a na dwóch pierwszych nic.

To wzorzec z nazwami przykładowymi. Uruchomiliśmy go na SQL Server 2022 przy poziomach zgodności 130 i 160 i dał identyczne wyniki. Suma okienkowa z jawną ramką jest dostępna od SQL Server 2012 (według dokumentacji).

Pozycje zamówienia i dokumenty bez wskazanej pozycji

Dane próbne to dwie tabele: pozycje zamówienia z ilością zamówioną i ilością już przyjętą dokumentami przyjęcia oraz dokumenty przyjęcia, z których część wskazuje pozycję, a część tylko produkt. Stan dokumentu przyjmuje jedną z trzech wartości widocznych w tabeli poniżej.

W danych są cztery pozycje: trzy produktu ART-A o ilościach 100, 50 i 30, z czego na pierwszej przyjęto już 20, oraz jedna produktu ART-B (40). Dokumenty opisuje tabela, a kolumna PozycjaNr ma wartość NULL, gdy dokument nie wskazuje pozycji.

Przykład ilustracyjny. Ilości w sztukach.

CREATE TABLE dbo.PozycjaZam (
    Nr       int           NOT NULL PRIMARY KEY,
    Produkt  varchar(10)   NOT NULL,
    Ilosc    decimal(12,3) NOT NULL,
    Przyjeto decimal(12,3) NOT NULL
);
CREATE TABLE dbo.DokumentPrzyjecia (
    Id        int IDENTITY(1,1) PRIMARY KEY,
    Produkt   varchar(10)   NOT NULL,
    Ilosc     decimal(12,3) NOT NULL,
    PozycjaNr int           NULL,
    Stan      varchar(12)   NOT NULL
);
INSERT dbo.PozycjaZam VALUES (1,'ART-A',100,20), (2,'ART-A',50,0), (3,'ART-A',30,0), (4,'ART-B',40,0);
INSERT dbo.DokumentPrzyjecia (Produkt, Ilosc, PozycjaNr, Stan) VALUES
 ('ART-A', 60, 2,    'PRZYJETY'),
 ('ART-A', 70, NULL, 'PRZYJETY'),
 ('ART-A', 30, NULL, 'W_KOLEJCE'),
 ('ART-A', 40, NULL, 'ODRZUCONY'),
 ('ART-B', 15, NULL, 'PRZYJETY');

Przykład: zamówienie 26-1001 u Dostawcy Testowego ma trzy pozycje Artykułu A, a na pierwszą z nich przyjęto już 20 sztuk. W bazie są dokumenty przyjęcia: jeden wskazuje pozycję drugą, dwa wskazują sam Artykuł A, jeden został odrzucony. Zapytanie odejmuje dokument przypisany od drugiej pozycji, a pulę liczy z dwóch dokumentów bez przypisania, pomijając odrzucony. Potem rozkłada pulę po kolei i zwraca 10 sztuk do przyjęcia na pozycji trzeciej i zero na pierwszej oraz drugiej.

Wolne miejsce pozycji obcięte do zera

Wolne miejsce pozycji to ilość minus ilość przyjęta dokumentami przyjęcia minus dokumenty przypisane do tej pozycji, bez odrzuconych, obcięte do zera. Sumę dokumentów przypisanych liczy OUTER APPLY, a obcięcie robi wyrażenie CASE. Trzy pozycje produktu ART-A mają wolne miejsce odpowiednio 80, 0 i 30.

Pozycja 2 ma ilość 50, a dokumenty przypisane 60, więc jej nadwyżka wynosi 10. Po obcięciu pozycja ma wolne miejsce 0, a nie -10. Bez obcięcia nadwyżka zjadłaby część puli, która należy się następnym pozycjom. To samo obcięcie stosuje widok w artykule o zamykaniu zlecenia po liczbie otwartych linii, a OUTER APPLY w innym zastosowaniu pokazuje artykuł o zakresach cennika.

Dokument przypisany odejmuje się od własnej pozycji, a reszta tworzy pulę produktu, więc żaden dokument nie liczy się dwa razy.

Pula produktu rozłożona sumą okienkową

Pula produktu to suma dokumentów bez przypisania, bez odrzuconych: 100 dla ART-A (70 plus 30) i 15 dla ART-B. Dokument przypisany nie wchodzi do puli, więc nie liczy się dwa razy. Dokument odrzucony nie liczy się wcale.

Suma okienkowa SUM(Wolne) OVER (PARTITION BY Produkt ORDER BY Nr ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) daje wolne miejsce pozycji wcześniejszych tego samego produktu. Dla pierwszej pozycji produktu ramka jest pusta, więc suma wynosi NULL, a ISNULL zamienia ją na 0. Opis klauzuli OVER jest w dokumentacji.

Pozostało na pozycji to wolne miejsce minus część puli, którą pozycja wchłania. Trzy gałęzie wyrażenia CASE odpowiadają trzem przypadkom: 0, gdy pula skończyła się przed pozycją, całe wolne miejsce, gdy pula starcza, albo reszta puli. Kolejność pozycji wyznacza numer pozycji, unikatowy w obrębie produktu. Dlaczego wpisujemy ramkę ROWS wprost, zamiast polegać na domyślnej ramce RANGE, wyjaśnia artykuł o alokacji wydania na dostawy, więc nie powtarzamy tego tutaj.

;WITH p AS (
    SELECT p.Nr, p.Produkt, p.Ilosc, p.Przyjeto,
           CASE WHEN p.Ilosc - p.Przyjeto - ISNULL(a.Przypisane, 0) > 0
                THEN p.Ilosc - p.Przyjeto - ISNULL(a.Przypisane, 0) ELSE 0 END AS Wolne,
           ISNULL(u.Pula, 0) AS Pula
    FROM dbo.PozycjaZam p
    OUTER APPLY (SELECT SUM(d.Ilosc) AS Przypisane FROM dbo.DokumentPrzyjecia d
                 WHERE d.PozycjaNr = p.Nr AND d.Stan <> 'ODRZUCONY') a
    OUTER APPLY (SELECT SUM(d.Ilosc) AS Pula FROM dbo.DokumentPrzyjecia d
                 WHERE d.Produkt = p.Produkt AND d.PozycjaNr IS NULL AND d.Stan <> 'ODRZUCONY') u
), k AS (
    SELECT *, ISNULL(SUM(Wolne) OVER (PARTITION BY Produkt ORDER BY Nr
                     ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 0) AS WolneWczesniej
    FROM p
)
SELECT Nr, Produkt, Wolne, Pula, WolneWczesniej,
       Wolne - CASE WHEN Pula - WolneWczesniej <= 0 THEN 0
                    WHEN Pula - WolneWczesniej >= Wolne THEN Wolne
                    ELSE Pula - WolneWczesniej END AS Pozostalo
FROM k
ORDER BY Produkt, Nr;

Wynik na danych próbnych i zapytanie kontrolne

Pozycja 1 ma wolne miejsce 80 i wchłania 80 z puli 100, więc zostaje jej 0. Pozycja 2 ma 0 wolnego miejsca i 0 pozostałych. Pozycja 3 ma wolne miejsce 30, a z puli zostało jej 20, więc zostaje 10. Pozycja 4 produktu ART-B ma wolne miejsce 40, pula wynosi 15, więc zostaje 25.

Wynik zapytania wzorcowego dla produktów ART-A (pula 100) i ART-B (pula 15).

Wynik pokazuje liczby z trzema miejscami po przecinku, bo kolumny mają typ decimal(12,3).

Zapytanie kontrolne dołącza do tego samego zapytania wzorcowego kolejne wyrażenie i podsumowuje wynik po produkcie. Dla każdego produktu suma wchłoniętych ilości, czyli wolne miejsce minus pozostało, jest równa mniejszej z dwóch liczb: puli albo sumy wolnych miejsc. Dla ART-A to 100 przy puli 100 i sumie wolnych 110, dla ART-B 15 przy puli 15 i sumie wolnych 40.

, r AS (
    SELECT *, Wolne - CASE WHEN Pula - WolneWczesniej <= 0 THEN 0
                           WHEN Pula - WolneWczesniej >= Wolne THEN Wolne
                           ELSE Pula - WolneWczesniej END AS Pozostalo
    FROM k
)
SELECT Produkt, SUM(Wolne) AS SumaWolnych, MAX(Pula) AS Pula, SUM(Wolne - Pozostalo) AS Wchloniete,
       CASE WHEN SUM(Wolne - Pozostalo) = CASE WHEN MAX(Pula) < SUM(Wolne) THEN MAX(Pula) ELSE SUM(Wolne) END
            THEN 'OK' ELSE 'BLAD' END AS Kontrola
FROM r GROUP BY Produkt ORDER BY Produkt;

Oba produkty przechodzą kontrolę.

Bilans pozycji a bilans produktu

Bilans na poziomie produktu to suma ilości minus suma ilości przyjętych dokumentami przyjęcia minus wszystkie nieodrzucone dokumenty. Dla ART-A daje zero, a rozkład na pozycje pokazuje 10 do przyjęcia na pozycji 3. Różnica bierze się z nadwyżki 10 na pozycji 2: bilans produktowy odejmuje ją od całego produktu, a rozkład pozycyjny zatrzymuje ją na tej pozycji. Oba bilanse odpowiadają na inne pytania.

SELECT p.Produkt,
       SUM(p.Ilosc) - SUM(p.Przyjeto)
         - ISNULL((SELECT SUM(d.Ilosc) FROM dbo.DokumentPrzyjecia d
                   WHERE d.Produkt = p.Produkt AND d.Stan <> 'ODRZUCONY'), 0) AS ZostaloNaProdukcie
FROM dbo.PozycjaZam p
GROUP BY p.Produkt
ORDER BY p.Produkt;

Bilans produktowy nie widzi, na której pozycji jest wolne miejsce.

Ekran, który pokazuje ilość pozostałą na pozycji zamówienia, opisuje strona o ilości pozostałej do dostawy. Kontrolę ilości w ERP przed zapisem przyjęcia opisuje strona o kontroli ilości w ERP, a liczenie z trzech stanów awizacji strona o bilansie pozycji zamówienia w awizacji. Ten artykuł dotyczy samego zapytania. Sumę narastającą w innym zastosowaniu pokazuje artykuł o paletodobie w T-SQL.

Cztery pułapki wyniku

Każdą pułapkę sprawdziliśmy uruchomieniem, zmieniając jedno miejsce w zapytaniu wzorcowym. Tabela pokazuje pulę i kolumnę Pozostało dla trzech pozycji produktu ART-A.

Bez ISNULL także pozycja 4 produktu ART-B ma wynik NULL.

Bez ISNULL pozycje pierwsze w swoich produktach, czyli 1 i 4, dostają WolneWczesniej NULL i Pozostalo NULL. Bez obcięcia pozycja 2 ma wolne miejsce -10, pozycja 3 dostaje WolneWczesniej 70 zamiast 80, a Pozostalo 0 zamiast 10, bo nadwyżka pozycji 2 zjadła część puli.

Pula bez warunku PozycjaNr IS NULL liczy dokument przypisany drugi raz i wynosi 160 zamiast 100, więc pozycja 3 ma 0 zamiast 10. Bez warunku na stan dokument odrzucony liczy się jak przyjęty, pula wynosi 140 i pozycja 3 znów ma 0. Wszystkie cztery błędy wyglądają jak poprawny wynik, bo zapytanie kończy się bez błędu.

Wzorzec łatwo połączyć z wysyłką wyniku do systemu zewnętrznego. Zabezpieczenie takiej wysyłki przed dubletem opisuje artykuł o idempotencji wysyłki w T-SQL, a wszystkie artykuły sekcji zebraliśmy w spisie artykułów o SQL Server.

Cztery pytania przed napisaniem własnego bilansu pozycji

Zanim przeniesiesz wzorzec do własnej bazy, odpowiedz sobie na cztery pytania.

• Dokumenty: czy dokumenty przyjęcia w twojej bazie zawsze wskazują pozycję zamówienia, czy czasem tylko produkt.
• Nadwyżka: co ma się stać, gdy dokument przypisany przekracza ilość swojej pozycji.
• Kolejność: w jakiej kolejności pozycje tego samego produktu mają przyjmować pulę dokumentów bez przypisania.
• Stany: które stany dokumentów wolno liczyć jako przyjęte.