667 lines
38 KiB
Markdown
667 lines
38 KiB
Markdown
# Delivery investigation
|
||
|
||
## Problem
|
||
|
||
Orders created via `jtlsrv` (`AU-202607-10025`) get `nKomplettAusgeliefert = 1` and a
|
||
`dbo.tLieferschein` / `dbo.tLieferscheinPos` row, but **no** `dbo.tPickliste` /
|
||
`dbo.tPicklistePos` rows and no real warehouse stock booking. Orders created by the
|
||
real JTL-Wawi/POS stack (`AU-202607-10026`) get all of that correctly (Pickliste with
|
||
proper status progression 5 → 10 → 40, a numbered Lieferschein like
|
||
`AU-202607-10026-001`, etc).
|
||
|
||
Root cause: our code (`src/queries/create-order.js`) calls the low-level
|
||
`Versand.spLieferscheinErstellen` / `Versand.spLieferscheinPosErstellen` procedures
|
||
directly. These only insert the Lieferschein rows and call
|
||
`Verkauf.spAuftragEckdatenBerechnen` (which derives `nKomplettAusgeliefert`). They
|
||
never touch the Pickliste/warehouse-reservation subsystem, so nothing is actually
|
||
picked/booked out of stock. The real JTL code path is much deeper and goes through
|
||
the same "Auslieferung" (fulfillment) engine used by the Wawi GUI's "Auftrag
|
||
ausliefern" feature. This document records that engine in full, down to exact SQL
|
||
parameter names/types and XML schemas, gathered from:
|
||
|
||
- Decompiled C# (`ilspycmd`) of `jtlCore.dll` (obfuscated string literals, but
|
||
control flow / call graph is readable) and `jtlDatabase.dll` (**not** obfuscated —
|
||
this is where the actual ADO.NET parameter wiring lives).
|
||
- `sp_helptext` of the actual stored procedures in `eazybusiness` (SQL Server ground
|
||
truth — these fully document their own XML input schemas in comments and in the
|
||
`.nodes()`/`.value()` shredding code).
|
||
|
||
## 0. Step-by-step SQL to issue for one delivery (ready to run/adapt)
|
||
|
||
This is the literal sequence of statements to execute, in order, inside the same
|
||
transaction as the rest of order creation, to deliver one order
|
||
(`@kAuftrag`) for one user (`@kBenutzer`). Everything here is derived 1:1 from the
|
||
stored-procedure signatures and XML schemas documented in §3; treat this section as
|
||
the concrete checklist, §3 as the "why"/evidence.
|
||
|
||
Placeholders to fill in per order: `@kAuftrag`, `@kBenutzer`, `@kWarenLager` (see
|
||
§7 — outgoing warehouse, still unresolved), and one `<Bestellposition>`/`<Lager>`
|
||
pair per order position (only positions with real, stock-tracked articles need to go
|
||
through the reservation dance below at all — see §7 for free positions).
|
||
|
||
```sql
|
||
-- Step 1: open a session (mirrors jtlCore's SessionManager / dbo.tSessionId row)
|
||
DECLARE @kSessionId INT;
|
||
INSERT INTO dbo.tSessionId (cRechnername, kBenutzer, dLastAction)
|
||
VALUES ('jtlsrv', @kBenutzer, DATEADD(day, 10, GETDATE()));
|
||
SET @kSessionId = SCOPE_IDENTITY();
|
||
|
||
-- Step 2: reserve every order position against the outgoing warehouse
|
||
-- (one call per position, or batch them all into @Bestellpositionen and call
|
||
-- Auslieferung.spReserviereBestellpositionen once for the whole order — same effect)
|
||
DECLARE @Bestellpositionen XML = '
|
||
<Bestellposition><kBestellPos>{kAuftragPosition}</kBestellPos><fAnzahl>{menge}</fAnzahl></Bestellposition>
|
||
<!-- repeat one <Bestellposition> per order position -->
|
||
';
|
||
DECLARE @Laeger XML = '
|
||
<Lager><kWarenlager>{kWarenLager}</kWarenlager><nPrio>0</nPrio><kLieferant>0</kLieferant><kAnsprechpartner>0</kAnsprechpartner></Lager>
|
||
';
|
||
EXEC Auslieferung.spReserviereBestellpositionen
|
||
@Bestellpositionen = @Bestellpositionen,
|
||
@Laeger = @Laeger,
|
||
@Warenlagereingaenge = NULL,
|
||
@nOptions = 0x0102, -- ChargenVorbelegen (0x0002) | Stücklistenkorrektur (0x0100)
|
||
@kBenutzer = @kBenutzer,
|
||
@kSessionId = @kSessionId;
|
||
-- -> creates dbo.tPickliste (kSessionId = @kSessionId) + dbo.tPicklistePos rows, status 0 then 5
|
||
|
||
-- Step 3: commit the session's reservations into "real" Picklisten
|
||
DECLARE @Bestellungen XML = '<Bestellung><kBestellung>{kAuftrag}</kBestellung></Bestellung>';
|
||
DECLARE @xResultUebernehmen XML;
|
||
EXEC Auslieferung.spPicklistenUebernehmen
|
||
@Bestellungen = @Bestellungen,
|
||
@kBenutzer = @kBenutzer,
|
||
@nTeillieferung = 0, -- 0 = partial delivery forbidden (we always deliver in full)
|
||
@kSessionId = @kSessionId,
|
||
@xResult = @xResultUebernehmen OUTPUT;
|
||
-- -> bumps dbo.tPicklistePos.nStatus from <10 to 10 (or 20 for position types 18/20) for this session
|
||
|
||
-- Step 4: actually deliver — creates the numbered Lieferschein, decrements real
|
||
-- stock, marks positions delivered
|
||
--
|
||
-- IMPORTANT: @Pakete must NOT be NULL/empty if you want dbo.tVersand populated.
|
||
-- The nested Auslieferung.spPicklistenAusliefern_PaketeErzeugen builds a temp
|
||
-- table straight from @Pakete.nodes('/Paket'); if @Pakete is NULL that temp
|
||
-- table stays empty and NOT A SINGLE dbo.tVersand row gets written for ANY
|
||
-- Lieferschein in the session (not even the "no Paket given" fallback row,
|
||
-- because even that fallback branch is keyed off a per-kBestellung JOIN
|
||
-- against the temp table). So pass one <Paket> per order, at minimum with
|
||
-- <kBestellung>/<kVersandart>/<fGewicht> (fGewicht=0 is the sentinel the proc
|
||
-- itself later replaces with an article-weight-based value; the column is
|
||
-- NOT NULL so it can't be omitted).
|
||
DECLARE @Pakete XML = '<Paket><kBestellung>{kAuftrag}</kBestellung><kVersandart>{kVersandArt}</kVersandart><fGewicht>0</fGewicht></Paket>';
|
||
DECLARE @xResultAusliefern XML;
|
||
EXEC Auslieferung.spPicklistenAusliefern
|
||
@xHinweise = NULL, -- optional <Hinweis><kBestellPos>/<kBestellung></kBestellung><cHinweis>...
|
||
@Pakete = @Pakete,
|
||
@nOptions = 0x0002, -- VersandSetzen (DeliveryStep always sets this); OR 0x0001 for one Lieferschein per warehouse
|
||
@kBenutzer = @kBenutzer,
|
||
@kSessionId = @kSessionId,
|
||
@xResult = @xResultAusliefern OUTPUT;
|
||
-- -> creates dbo.tLieferschein/tLieferscheinPos (properly numbered "<AuftragsNr>-NNN"),
|
||
-- links kLieferscheinPos back onto tPicklistePos, calls dbo.spWarenlagerAusgangPicklistePos
|
||
-- (real stock decrement), bumps tPicklistePos.nStatus to 40, AND (via
|
||
-- spPicklistenAusliefern_PaketeErzeugen) inserts a dbo.tVersand row per
|
||
-- Lieferschein carrying {kVersandArt} and, since VersandSetzen is set and this
|
||
-- is a local (non-fulfillment) warehouse, dVersendet = GETDATE()
|
||
|
||
-- Step 5: recalculate order eckdaten (nKomplettAusgeliefert etc.) — already implemented today
|
||
DECLARE @p1 Verkauf.TYPE_spAuftragEckdatenBerechnen;
|
||
INSERT INTO @p1 VALUES (@kAuftrag);
|
||
EXEC Verkauf.spAuftragEckdatenBerechnen @auftrag = @p1;
|
||
|
||
-- Step 6: discard anything left in this session that never got committed/delivered.
|
||
-- Safe to call unconditionally (success AND error path) — its body is just
|
||
-- `DELETE FROM dbo.tPickliste WHERE kSessionId = @kSessionId AND nStatus < 10`,
|
||
-- so it never touches already-delivered (nStatus > 10) Picklisten. On the error
|
||
-- path it also cleans up whatever Step 2 managed to reserve before the failure.
|
||
EXEC Auslieferung.spPicklistenVerwerfen @kBenutzer = @kBenutzer, @kSessionId = @kSessionId;
|
||
|
||
-- Step 7 (optional cleanup, mirrors SessionManager.Dispose()):
|
||
DELETE FROM dbo.tSessionId WHERE kSessionId = @kSessionId;
|
||
```
|
||
|
||
Notes on the XML construction above (see §3 for full schema/behavior detail):
|
||
- `{kAuftragPosition}` = `Verkauf.tAuftragPosition.kAuftragPosition` (what our code
|
||
calls `kAuftragPosition`), `{menge}` = ordered quantity, `{kWarenLager}` = the
|
||
resolved outgoing warehouse (§7, open question), `{kAuftrag}` =
|
||
`Verkauf.tAuftrag.kAuftrag`.
|
||
- `@Bestellpositionen`/`@Laeger` are XML *fragments* — SQL Server's `xml` type
|
||
happily holds multiple top-level elements, so just concatenate one
|
||
`<Bestellposition>...</Bestellposition>` per position (same for `<Lager>` if ever
|
||
passing multiple candidate warehouses).
|
||
- Steps 2-4 must run inside the *same* transaction/connection context as order
|
||
creation so `@kSessionId` and the temp Pickliste rows are visible to each other and
|
||
everything rolls back together on failure.
|
||
|
||
## 0.1 Cross-check against another (incomplete) implementation
|
||
|
||
A second, independently-written `deliverOrder()` implementation was reviewed. It was
|
||
apparently built by SQL-tracing manual "Auftrag ausliefern" actions in the Wawi GUI
|
||
(comments like `// Based on Statement 30 from JTL dump`). Cross-checking it against
|
||
the decompiled C#/`sp_helptext` ground truth above:
|
||
|
||
**Confirms our findings:**
|
||
- Same overall call order: `Auslieferung.spReserviereBestellungen`/`spReserviereBestellpositionen`
|
||
→ `Auslieferung.spPicklistenUebernehmen` → `Auslieferung.spPicklistenAusliefern` →
|
||
`Verkauf.spAuftragEckdatenBerechnen`.
|
||
- Session handling via a `dbo.tSessionId` row (`kSessionId`), matching §2 exactly.
|
||
- `@nOptions = 258` (`0x102`) for `Auslieferung.spReserviereBestellpositionen` — exact
|
||
match with our decompiled-C#-derived value in §3.1.
|
||
- `@nOptions = 2` (no `Stücklistenkorrektur` bit) for `Auslieferung.spReserviereBestellungen`
|
||
— matches the decompiled C# wrapper for that specific overload, which (unlike the
|
||
`...Bestellpositionen` wrapper) does **not** OR in `Stücklistenkorrektur`.
|
||
- Real example `@Laeger` XML with multiple prioritised warehouses (`kWarenlager` 1,
|
||
4, 5, 6, 7) plus dropshipping-supplier fallback rows (`kLieferant` −3/−1/−2/`n`,
|
||
i.e. default/cheapest/fastest/specific supplier) — confirms the magic-value scheme
|
||
documented in §3.1 with a concrete real-world example, and confirms
|
||
`@Laeger` supports arbitrarily many `<Lager>` candidates, not just one.
|
||
- After delivery, Picklisten/`tPicklistePos` rows are deliberately **kept** (not
|
||
deleted) — their `cleanupSessions()` only ever deletes `dbo.tSessionId` (the
|
||
`picklistId` branch that would delete `tPickliste`/`tPicklistePos` is called with
|
||
`null` and never actually runs). Matches expectations: the Pickliste is the audit
|
||
trail the Wawi GUI still shows after delivery, so it must survive.
|
||
- Calls `Auslieferung.spPicklistenVerwerfen` again at the very end, even after a
|
||
successful `spPicklistenAusliefern`. Checked its SQL body
|
||
(`Auslieferung.spPicklistenVerwerfen`, `/tmp/sp_verwerfen.sql`):
|
||
```sql
|
||
DELETE FROM dbo.tPickliste WHERE kSessionId = @kSessionId AND nStatus < 10
|
||
```
|
||
It only ever removes *uncommitted* (`nStatus < 10`) Picklisten for the session, so
|
||
calling it unconditionally at the end of a successful delivery is harmless/a good
|
||
defensive habit (cleans up any leftover reservation that didn't make it into the
|
||
delivery, e.g. a position that failed stock checks), not just an error-path call
|
||
as §6 step 6 assumed. **Recommendation: call it unconditionally after step 4/5,
|
||
not only on error.**
|
||
|
||
**Contradicts/corrects our findings — resolved in favor of the ground truth:**
|
||
- It resolves order positions through legacy `dbo.tBestellung`/`dbo.tBestellPos`
|
||
tables, joined back to `Verkauf.tAuftragPosition` by **matching `kArtikel`**
|
||
(`ap.kArtikel = bp.tArtikel_kArtikel AND ap.kAuftrag = a.kAuftrag`) rather than by
|
||
ID. This is unnecessary and fragile (breaks for two order lines with the same
|
||
SKU/article). Checked directly: `Auslieferung.vBestellPos`
|
||
(`/tmp/vbestellpos.sql`) —
|
||
```sql
|
||
CREATE VIEW [Auslieferung].[vBestellPos] AS
|
||
SELECT tAuftragPosition.kAuftragPosition AS kBestellPos,
|
||
tAuftrag.kAuftrag AS kBestellung, ...
|
||
```
|
||
confirms **`kBestellPos` *is* `Verkauf.tAuftragPosition.kAuftragPosition` and
|
||
`kBestellung` *is* `Verkauf.tAuftrag.kAuftrag`, directly** — "Bestellung"/"BestellPos"
|
||
is just the historical/legacy parameter naming carried over from pre-3.0 JTL, not a
|
||
separate table to join through. Our `create-order.js` already has these ids
|
||
directly (`kAuftrag`, `kAuftragPosition`) — no extra lookup needed, use them as-is
|
||
in all the `@Bestellpositionen`/`@Bestellungen`/`@Laeger` XML.
|
||
- It calls `Auslieferung.spPicklistenAusliefern` with **`@nOptions = 0`** (no
|
||
`VersandSetzen`). This contradicts `DeliveryStep.cs` (POS/API order path), which
|
||
explicitly does:
|
||
```csharp
|
||
auslieferungAusliefernContext.Optionen.VersandSetzen = true;
|
||
```
|
||
(`decompiledReference/decompiled/jtlCore/jtlCore.Classes.Sync.PosOrderSteps/DeliveryStep.cs:213`,
|
||
confirmed present, not just inferred). The other implementation was likely traced
|
||
from a manual GUI delivery where the "Versand setzen" checkbox happened to be
|
||
unchecked. **For reproducing POS/API-style immediate delivery, use `@nOptions = 2`
|
||
(`VersandSetzen`), not `0`, per DeliveryStep's actual behavior — keep §0/§3.3 as
|
||
documented.**
|
||
- It also creates a `dbo.tUserSession` row (`createJTLSessions`) alongside
|
||
`dbo.tSessionId`, mirroring a full GUI login. No stored procedure body read so far
|
||
(§3, plus `spWarenlagerAusgangPicklistePos`, `spPicklistenVerwerfen`,
|
||
`vBestellPos`) references or validates against `tUserSession` — the only
|
||
server-side check seen is `kBenutzer` existing in `dbo.tbenutzer` (a plain FK-style
|
||
check, e.g. inside `spWarenlagerAusgangPicklistePos`). **Recommendation: skip
|
||
`tUserSession` entirely** unless a not-yet-encountered procedure turns out to
|
||
require it.
|
||
- Its partial-delivery path (multiple `spReserviereBestellungen` calls interleaved
|
||
with manual `DELETE FROM tPicklistePos`/quantity-adjustment dances) is real
|
||
evidence of how the Wawi GUI implements *partial* delivery, but is unnecessary
|
||
complexity for us — `create-order.js` always delivers the full ordered quantity
|
||
(mirrors `DeliveryStep`'s non-voucher path), so the single-pass §0 sequence
|
||
(reserve once, commit with `nTeillieferung = 0`, deliver) is sufficient and matches
|
||
what `DeliveryStep.Run` itself does for a new POS order.
|
||
|
||
## 1. C# call chain: HTTP request → delivery commit
|
||
|
||
1. **`OrderController.Put`**
|
||
(`decompiledReference/JTL.Wawi.PosServer.Controller/OrderController.cs:33-67`)
|
||
Receives `PUT /api/v1/order`, delegates to `Q5hG5CfaiXW` (line 101-127), which
|
||
builds a `PosFrontend<...>` and calls `.ExecuteAsync(new PosOrderCommand {
|
||
Origin = OriginType.Bestellung, ... })`.
|
||
|
||
2. **`PosFrontend.ExecuteAsync` → `PosFrontend.HandleOrders`**
|
||
(`decompiledReference/JTL.Wawi.Sync.Core.decompiled.cs:6168-6122`)
|
||
Calls `new PosOrderImportService(...).ImportOrders(...)`.
|
||
|
||
3. **`PosOrderImportService.ImportOrders`**
|
||
(`decompiledReference/JTL.Wawi.Sync.Core.decompiled.cs:6231-6281`)
|
||
For a brand-new order (no existing `OrderNumber`/`OrderId` match — our case):
|
||
`CreateOrUpdatePosAuftrag(order, list, shopSubShopId)` →
|
||
`SyncCoreService.Service.CreateOrUpdatePosAuftrag(...)`.
|
||
|
||
4. **`SyncCoreService.CreateOrUpdatePosAuftrag`**
|
||
(`decompiledReference/decompiled/jtlCore/jtlCore.Classes.Sync/SyncCoreService.cs:77-80`)
|
||
Delegates to `new PosOrderCreationService(...).CreateOrUpdatePosAuftrag(...)`.
|
||
|
||
5. **`PosOrderCreationService.CreateOrUpdatePosAuftrag`**
|
||
(`decompiledReference/decompiled/jtlCore/jtlCore.Classes.Sync.Pos/PosOrderCreationService.cs:102-221`)
|
||
Creates the order via `importer.TrySaveOrder(...)`, then runs a step pipeline:
|
||
```
|
||
new RoundingErrorStep(...).Run(jtlAuftrag, orderEntity);
|
||
new DeliveryStep(...).Run(jtlAuftrag, orderEntity);
|
||
new InvoicePrintStep(...).Run(jtlAuftrag, orderEntity);
|
||
new VoucherPrintStep(...).Run(jtlAuftrag, orderEntity);
|
||
```
|
||
|
||
6. **`DeliveryStep.Run`**
|
||
(`decompiledReference/decompiled/jtlCore/jtlCore.Classes.Sync.PosOrderSteps/DeliveryStep.cs:184-273`)
|
||
`bool flag = orderEntity.Settings?.Deliver ?? true;` — this is where `deliver`
|
||
from the POS payload is consumed. When true:
|
||
- `PosWarehouseService.FindeWarenlagerFürAusgang()` — resolves the outgoing
|
||
warehouse (`kWarenLager`) to use. **Still unresolved in this doc — see "Open
|
||
questions" below.**
|
||
- `AuslieferungAusliefernContext.CreateForAufträge([kAuftrag], ...)` — opens a
|
||
delivery context (constructs an `AuslieferungContext`, which immediately opens
|
||
a `SessionManager`/`kSessionId`, see §2).
|
||
- `auslieferungContext.LadeBestände()` — loads current stock (read-only, for UI).
|
||
- For every position with a real `ArtikelId`:
|
||
`PosBookPositionService.BookPosition(...)` →
|
||
`auslieferungContext.NeuReservieren(positions, warehouseDetails)` → calls
|
||
`Auslieferung.spReserviereBestellpositionen` (§3.1).
|
||
- For positions without an `ArtikelId` (shipping, deposit/Pfand lines):
|
||
`auslieferungContext.NeuReservieren(...)` directly with the full quantity (same
|
||
stored procedure).
|
||
- If the order is paid (or the payment method allows delivery-before-payment):
|
||
`posStockPositionService.FehlbestandEinbuchen(...)` — books any stock shortfall
|
||
so delivery isn't blocked by missing stock.
|
||
- **`auslieferungAusliefernContext.Commit();`** — persists everything (§2).
|
||
- If backdated: `ApTVuv7PTPl.SetVersanddatum(auftrag.kAuftrag, orderEntity.CreationDate)`.
|
||
|
||
7. **`AuslieferungAusliefernContext.Commit()`**
|
||
(`decompiledReference/decompiled/jtlCore/jtlCore.Classes.Versand.Auslieferung/AuslieferungAusliefernContext.cs:223-321`)
|
||
```csharp
|
||
Validiere();
|
||
PicklistenCommitten(); // -> Auslieferung.spPicklistenUebernehmen (§3.2)
|
||
...
|
||
auslieferungSpResult = IScXmooPhGf(); // -> Auslieferung.spPicklistenAusliefern (§3.3)
|
||
...
|
||
kHtXmjSeHbm(T5jXmkcZPKf()); // -> Verkauf.spAuftragEckdatenBerechnen (already replicated)
|
||
ExecuteWorkflows(auslieferungSpResult); // UI/event notifications only, no DB writes
|
||
```
|
||
|
||
## 2. The session concept (`kSessionId`)
|
||
|
||
Every reservation/commit call is scoped by a `kSessionId`. This comes from
|
||
`jtlCore.Classes.SessionManager` (`decompiledReference/decompiled/jtlCore/jtlCore.Classes/SessionManager.cs`),
|
||
which lazily does, on first use:
|
||
|
||
```csharp
|
||
var s = new jtlSessionid {
|
||
cRechnername = Environment.MachineName,
|
||
kBenutzer = BenutzerManager.Current.kAngemeldeterBenutzer,
|
||
dLastAction = DateTime.Now + TimeSpan.FromDays(10.0)
|
||
};
|
||
s.Save(); // INSERT INTO dbo.tSessionId (...)
|
||
s.Touch();
|
||
```
|
||
|
||
`jtlSessionidBase` (`jtlDatabase.dll`, decompiled) maps to table **`dbo.tSessionId`**:
|
||
|
||
| Column | Type | Notes |
|
||
|---------------|-----------------|-----------------------------------------|
|
||
| `kSessionId` | `int` identity | PK, returned via `SCOPE_IDENTITY()` |
|
||
| `cRechnername`| `nvarchar(255)` | machine name (cosmetic, can be any string) |
|
||
| `kBenutzer` | `int` | FK `dbo.tBenutzer` |
|
||
| `dLastAction` | `datetime` | kept alive by a 60s "touch" timer; only relevant for GUI housekeeping/timeouts |
|
||
|
||
So reproducing this is just: **insert one row into `dbo.tSessionId`, read back
|
||
`kSessionId`**, use it for the whole delivery operation, nothing else needed (no need
|
||
to periodically "touch" it for a short-lived one-shot API request).
|
||
|
||
On `Dispose()`, if the context was constructed for "new reservation" mode
|
||
(`BQ9XLnEpso5 == true`, i.e. our case, an `AuslieferungAusliefernContext`), and
|
||
`Commit()` was never called, it rolls back via `Auslieferung.spPicklistenVerwerfen`
|
||
instead. We don't need this if we always commit; keep it in mind for error handling
|
||
(wrap in try/catch and call `spPicklistenVerwerfen` on failure to avoid leaving orphan
|
||
Picklisten in that session).
|
||
|
||
## 3. The stored procedures, in call order
|
||
|
||
All four procedures below live in `jtlDatabase.classes.jtlDBClasses.StoredProcedures`
|
||
(decompiled with `ilspycmd -t jtlDatabase.classes.jtlDBClasses.StoredProcedures
|
||
jtlDatabase.dll`) and their exact ADO.NET parameter wiring is unobfuscated. Their SQL
|
||
bodies were read directly via `sp_helptext` against `eazybusiness`.
|
||
|
||
### 3.1 `Auslieferung.spReserviereBestellpositionen` — soft-reserve positions
|
||
|
||
C# wrapper (`StoredProcedures.cs:1413-1440`):
|
||
|
||
```csharp
|
||
private static int? Auslieferung_spReserviereBestellpositionen(
|
||
string Bestellpositionen, string Laeger, string Warenlagereingaenge,
|
||
int? nOptions, int? kBenutzer, int? kSessionId, IDbConnection connection = null)
|
||
```
|
||
|
||
SQL parameters (`@Bestellpositionen XML, @Laeger XML, @Warenlagereingaenge XML,
|
||
@nOptions INT, @kBenutzer INT, @kSessionId INT`):
|
||
|
||
```xml
|
||
<!-- @Bestellpositionen: one per order position to reserve -->
|
||
<Bestellposition>
|
||
<kBestellPos>1</kBestellPos> <!-- = Verkauf.tAuftragPosition.kAuftragPosition -->
|
||
<fAnzahl>1.234</fAnzahl> <!-- quantity to reserve -->
|
||
</Bestellposition>
|
||
|
||
<!-- @Laeger: candidate warehouses, tried in nPrio order -->
|
||
<Lager>
|
||
<kWarenlager>1</kWarenlager>
|
||
<nPrio>0</nPrio>
|
||
<kLieferant>0</kLieferant> <!-- 0 = normal; -1/-2/-3 = cheapest/fastest/default supplier (dropshipping) -->
|
||
<kAnsprechpartner>0</kAnsprechpartner>
|
||
</Lager>
|
||
|
||
<!-- @Warenlagereingaenge: optional, specific goods-receipt rows (FIFO/batch/serial) — leave empty/NULL for "any" -->
|
||
<Warenlagereingang>
|
||
<kWarenlagereingang>17</kWarenlagereingang>
|
||
<fAnzahl>2.0</fAnzahl>
|
||
<nPrio>0</nPrio>
|
||
</Warenlagereingang>
|
||
```
|
||
|
||
`nOptions` bitmask (`StoredProcedures.ReserviereBestellpositionenOptionen`):
|
||
`0x0001 PicklisteProBestellung`, `0x0002 ChargenVorbelegen`, `0x0100
|
||
Stücklistenkorrektur`. The C# `AuslieferungContext` ctor always sets
|
||
`ChargenVorbelegen` (`0x0002`), and the low-level SP wrapper always OR's in
|
||
`Stücklistenkorrektur` (`0x0100`) — so the effective default is **`0x0102` (258)**
|
||
unless "one Pickliste per order" is also wanted (`+0x0001`).
|
||
|
||
Behavior (from `Auslieferung.spReserviereBestellposition`, called once per position
|
||
in a cursor loop, `/tmp/sp_reserviere_single.sql`):
|
||
- Resolves candidate stock sources from `@Laeger`/`@Warenlagereingaenge` into a temp
|
||
table (`#WARENLAGEREINGANG`), ordered by `nPrio`, `dMHD`, `cCharge`,
|
||
`kWarenlagereingang`.
|
||
- For each source, in order, until `@fAnzahl` is exhausted:
|
||
- Finds or creates a `dbo.tPickliste` row (`kSessionId = @kSessionId`, `nStatus =
|
||
0`, `cPicklisteNr` from `dbo.spGetNextNummer('Pickliste', ...)`). Reused across
|
||
positions of the same session/warehouse/supplier (one Pickliste per
|
||
warehouse+supplier+session, unless `PicklisteProBestellung` requested).
|
||
- Inserts a `dbo.tPicklistePos` row: `(kPickliste, kWarenLager, kWarenLagerEingang,
|
||
fAnzahl, kBestellPos, kPicklistePosStatus=0, kArtikel, kWarenlagerPlatz,
|
||
kPicklistePos_Ursprung=0, kLieferscheinPos=0, kBestellung)`.
|
||
- After the loop: deletes zero-quantity `tPicklistePos` rows for this
|
||
`kBestellPos`/session, then inserts a `dbo.tPicklistePosStatus` row with
|
||
**`nStatus = 5`** ("angelegt"/created) for every newly created `tPicklistePos`
|
||
(`kPicklistePosStatus = 0`).
|
||
|
||
This is the step that's entirely missing from our code — **this is why no Pickliste
|
||
exists for jtlsrv orders.**
|
||
|
||
### 3.2 `Auslieferung.spPicklistenUebernehmen` — commit reservations into "real" Picklisten
|
||
|
||
C# wrapper (`StoredProcedures.cs:1358-1386`):
|
||
|
||
```csharp
|
||
private static int? Auslieferung_spPicklistenUebernehmen(
|
||
string Bestellungen, int? kBenutzer, bool? nTeillieferung, int? kSessionId,
|
||
ref string xResult, IDbConnection connection = null)
|
||
```
|
||
|
||
SQL parameters (`@Bestellungen XML, @kBenutzer INT, @nTeillieferung BIT, @kSessionId
|
||
INT, @xResult XML OUTPUT`):
|
||
|
||
```xml
|
||
<Bestellung>
|
||
<kBestellung>7</kBestellung> <!-- Verkauf.tAuftrag.kAuftrag, one element per order -->
|
||
</Bestellung>
|
||
```
|
||
|
||
`@nTeillieferung`: `0` = partial delivery forbidden (raises an error if any reserved
|
||
order has both open and already-picked quantity outstanding — not our concern for a
|
||
"deliver everything now" POS order, so pass `0`); the C# side derives it from
|
||
`AuslieferungTeillieferungOptionen.GetCodeForSql() >= 1`.
|
||
|
||
Behavior (`/tmp/sp_uebernehmen.sql`):
|
||
- If `@nTeillieferung = 0`, verifies no order in `@Bestellungen` would end up
|
||
partially delivered; raises `RAISERROR` with an XML error payload otherwise.
|
||
- Deletes now-superfluous "Stücklistenvater" pick positions and any now-empty
|
||
Picklisten in this session.
|
||
- Bumps every `dbo.tPicklistePos` row in this session from status `< 10` to
|
||
**`nStatus = 10`** ("übernommen"/committed) via a new `tPicklistePosStatus` row.
|
||
- For a subset of position types (`tbestellpos.nType IN (18, 20)`) bumps further to
|
||
**`nStatus = 20`**.
|
||
- Returns `@xResult`:
|
||
```xml
|
||
<Result><NewPicklisten><Pickliste><kPickliste>...</kPickliste></Pickliste>...</NewPicklisten></Result>
|
||
```
|
||
|
||
### 3.3 `Auslieferung.spPicklistenAusliefern` — the actual delivery
|
||
|
||
C# wrapper (`StoredProcedures.cs:1327-1356`) — **this is the procedure the user
|
||
correctly identified as missing:**
|
||
|
||
```csharp
|
||
private static int? Auslieferung_spPicklistenAusliefern(
|
||
string xHinweise, string Pakete, int? nOptions, int? kBenutzer, int? kSessionId,
|
||
ref string xResult, IDbConnection connection = null)
|
||
```
|
||
|
||
SQL parameters (`@xHinweise XML, @Pakete XML, @nOptions INT, @kBenutzer INT,
|
||
@kSessionId INT, @xResult XML OUTPUT`):
|
||
|
||
```xml
|
||
<!-- @xHinweise: optional per-position/per-order notes copied onto the Lieferschein(Pos); can be empty/NULL -->
|
||
<Hinweis>
|
||
<kBestellPos>5</kBestellPos> <!-- OR <kBestellung>7</kBestellung> for the order-level note -->
|
||
<cHinweis>blah</cHinweis>
|
||
</Hinweis>
|
||
|
||
<!-- @Pakete: optional shipping/tracking info; can be empty/NULL for "no shipment info yet" -->
|
||
<Paket>
|
||
<kBestellung>1</kBestellung>
|
||
<kVersandart>5</kVersandart>
|
||
<dVersanddatum>...</dVersanddatum>
|
||
<cTrackingId>trackingid</cTrackingId>
|
||
<cEnclosedReturnIdentCode>...</cEnclosedReturnIdentCode>
|
||
<cHinweis>...</cHinweis>
|
||
</Paket>
|
||
```
|
||
|
||
`@nOptions` bitmask (`StoredProcedures.PicklistenAusliefernOptionen`): `0x0001
|
||
LieferscheinProLager` (one Lieferschein per warehouse), `0x0002 VersandSetzen` (also
|
||
create a `dbo.tVersandInfo`/package row + set shipped date on local-warehouse
|
||
deliveries). `DeliveryStep` always sets `VersandSetzen`, so the effective value is
|
||
**`2`** (or `3` if also splitting by warehouse).
|
||
|
||
Behavior (`/tmp/sp_ausliefern.sql`, delegates most of the work to sub-procedures, all
|
||
scoped to `WHERE ... kSessionId = @kSessionId`):
|
||
1. Validates (raises on partially-assigned serial numbers / empty serials for
|
||
serial-tracked articles — not relevant unless articles use `cLagerArtikel = 'Y'`
|
||
serial tracking).
|
||
2. **`Auslieferung.spPicklistenAusliefern_LokaleLager`** (`/tmp/sp_lokal.sql`) — the
|
||
part that matters for a normal local-warehouse delivery:
|
||
- Builds `@xLieferschein` XML for every order that has committed `tPicklistePos`
|
||
rows in this session on a local warehouse (`tWarenLager.nFulfillment = 0`), and
|
||
calls **`Versand.spLieferscheinErstellen`** (same proc our code already calls,
|
||
but now driven from the Pickliste data, with a properly numbered
|
||
`cLieferscheinNr` = `<Auftragsnummer>-NNN`).
|
||
- Builds `@xLieferscheinPos` XML (`kLieferschein`, `kBestellPos`,
|
||
`fAnzahl` summed per position) and calls **`Versand.spLieferscheinPosErstellen`**
|
||
(again, same proc, but now the quantities come from the Picklisten, not
|
||
directly from the order).
|
||
- **Links the new `kLieferscheinPos` back onto the `tPicklistePos` rows**
|
||
(`UPDATE dbo.tPicklistePos SET kLieferscheinPos = ...`) — this is the row our
|
||
current code never populates, which is presumably one of the visible
|
||
differences you saw between order 25 and order 26.
|
||
3. `Auslieferung.spPicklistenAusliefern_Fulfillment` /
|
||
`..._Dropshipping` — not relevant for local-warehouse orders.
|
||
4. Marks serial-numbered stock items (`dbo.tlagerartikel`) with the resulting
|
||
`kBestellPos`/`kLieferscheinPos` (only relevant for `cLagerArtikel = 'Y'`
|
||
articles).
|
||
5. **Real stock decrement**: builds `@xPicklistePos` (`kPicklistePos`, `kBenutzer`,
|
||
`cKommentar`) for every committed local-warehouse `tPicklistePos` row and calls
|
||
**`dbo.spWarenlagerAusgangPicklistePos`** (`/tmp/sp_ausgang.sql`), which in turn
|
||
builds a `WarenAusgang` XML per position (`kWarenLagerEingang`, `kLieferscheinPos`,
|
||
`fAnzahl`, `kWarenlagerPlatz`, `kArtikel`, `kBuchungsart = 20`) and calls
|
||
**`dbo.spWarenlagerAusgangSchreiben`** — the actual, final stock-decrement
|
||
procedure (writes the goods-issue row and reduces
|
||
`tWarenLagerEingang.fAnzahlAktuell`/warehouse stock). **This is the step that
|
||
explains the "no real stock decrement" symptom.**
|
||
6. Bumps `tPicklistePos` status to **`nStatus = 40`** ("ausgeliefert"/delivered).
|
||
7. Copies `@xHinweise` notes onto the new `tLieferschein`/`tLieferscheinPos` rows.
|
||
8. `Auslieferung.spPicklistenAusliefern_PaketeErzeugen` (`/tmp/sp_paketeerzeugen.sql`) —
|
||
creates the `dbo.tVersand` row(s) and, because `VersandSetzen` is set, marks
|
||
local-warehouse deliveries as shipped (`dVersendet = GETDATE()`).
|
||
- **Gotcha confirmed by testing**: this sub-proc builds a `#XMLPAKET` temp table
|
||
straight from `@Pakete.nodes('/Paket')` and then drives *every* `INSERT INTO
|
||
dbo.tVersand` off a join against that temp table (keyed by `kBestellung`). If
|
||
`@Pakete` is `NULL`/empty, `#XMLPAKET` has zero rows and the join finds
|
||
nothing for **any** order in the session — not even the "no explicit Paket"
|
||
fallback branch runs, because that branch is also reached only via the
|
||
`cLieferschein` cursor which is itself driven by a `JOIN #XMLPAKET`. Net
|
||
effect: passing `@Pakete = NULL` silently skips `dbo.tVersand` entirely, no
|
||
error, no shipment method recorded — this is exactly the bug the user
|
||
reported (rows shown by the user all have `kVersandArt = 2` /
|
||
"Selbstabholer" and a real `dVersendet`, which only happens when `@Pakete`
|
||
contains a matching `<Paket>` element for that order).
|
||
- Fix: always pass at least
|
||
`<Paket><kBestellung>{kAuftrag}</kBestellung><kVersandart>{kVersandArt}</kVersandart><fGewicht>0</fGewicht></Paket>`
|
||
(using the order's own `Verkauf.tAuftrag.kVersandArt`). `fGewicht` is a
|
||
`NOT NULL` column with no default, so it must be present in the XML — `0` is
|
||
safe, it's the exact sentinel value this same proc later looks for
|
||
(`WHERE dbo.tVersand.fGewicht = 0`) to recompute a real weight from the
|
||
order's article weights a few statements later.
|
||
- `cIdentCode`/`cTrackingId`, `dVersanddatum`, `cEnclosedReturnIdentCode`,
|
||
`cHinweis` are all optional and can be added later (e.g. once real carrier
|
||
integration exists) without changing anything else in this flow.
|
||
9. `Auslieferung.spPicklistenAusliefern_Umlagerungen` — stock-transfer orders only,
|
||
not relevant here.
|
||
10. Returns `@xResult`:
|
||
```xml
|
||
<Result>
|
||
<NewDeliveryNotes><DeliveryNote><kLieferschein>...</kLieferschein></DeliveryNote>...</NewDeliveryNotes>
|
||
<ProcessedPicklisten><Pickliste><kPickliste>...</kPickliste></Pickliste>...</ProcessedPicklisten>
|
||
<NewFulfillmentauftraege>...</NewFulfillmentauftraege>
|
||
<NewLieferantenbestellungen>...</NewLieferantenbestellungen>
|
||
<DeliveredOrders><Order><kBestellung>...</kBestellung></Order>...</DeliveredOrders>
|
||
<DeliveredUmlagerungen>...</DeliveredUmlagerungen>
|
||
</Result>
|
||
```
|
||
(schema confirmed unobfuscated in `StoredProcedures.AuslieferungSpResult`,
|
||
`StoredProcedures.cs:52-186`)
|
||
|
||
### 3.4 `Verkauf.spAuftragEckdatenBerechnen`
|
||
|
||
Already correctly called by our existing code (`create-order.js`); recalculates
|
||
`nKomplettAusgeliefert` and other derived order fields from the (now populated)
|
||
Lieferschein/Pickliste data. No change needed here, just needs to run *after* step
|
||
3.3 instead of instead of it.
|
||
|
||
## 4. `tPicklistePos.nStatus` progression (for reference)
|
||
|
||
| Status | Meaning | Set by |
|
||
|--------|---------------------------------------|-------------------------------------------|
|
||
| 0 | just inserted | `spReserviereBestellposition` |
|
||
| 5 | reserved / "angelegt" | `spReserviereBestellpositionen` (end) |
|
||
| 10 | committed / "übernommen" | `spPicklistenUebernehmen` |
|
||
| 20 | committed (special position types 18/20) | `spPicklistenUebernehmen` |
|
||
| 40 | delivered / "ausgeliefert" | `spPicklistenAusliefern` (via `_LokaleLager`) |
|
||
|
||
## 5. What our code does today (`src/queries/create-order.js`)
|
||
|
||
`deliverOrder()` calls `Versand.spLieferscheinErstellen` +
|
||
`Versand.spLieferscheinPosErstellen` directly per position — i.e. it starts at
|
||
step 3.3's *inner* Lieferschein-creation calls, skipping everything before it
|
||
(§3.1 reserve, §3.2 commit) and everything after within 3.3 (Pickliste linking,
|
||
real stock decrement, package/ship-date handling). It happens to still flip
|
||
`nKomplettAusgeliefert` because `spLieferscheinPosErstellen`/our subsequent call to
|
||
`Verkauf.spAuftragEckdatenBerechnen` don't care where the Lieferschein numbers came
|
||
from — they just see delivered quantities recorded on `tLieferschein`. Net effect:
|
||
- No `dbo.tPickliste` / `dbo.tPicklistePos` rows.
|
||
- No real warehouse stock (`dbo.tWarenLagerEingang.fAnzahlAktuell` /
|
||
`dbo.tlagerbestand`) decrement.
|
||
- `cLieferscheinNr` isn't numbered per-order the way the real flow does it
|
||
(`<AuftragsNr>-001`).
|
||
|
||
## 6. Plan to reproduce exactly
|
||
|
||
Rewrite `deliverOrder()` in `create-order.js` to, per order:
|
||
|
||
1. `INSERT INTO dbo.tSessionId (cRechnername, kBenutzer, dLastAction) VALUES
|
||
('jtlsrv', @kBenutzer, DATEADD(day, 10, GETDATE()))`, read back `kSessionId`
|
||
(`SCOPE_IDENTITY()`).
|
||
2. For every order position with a real `kArtikel` (skip pure text/shipping-only
|
||
rows if the article resolves to 0 — those go through the "Freiposition" path in
|
||
§3.1, which our `@Laeger` XML already supports the same way): build
|
||
`@Bestellpositionen` (one `<Bestellposition>` per position, full ordered
|
||
quantity) and `@Laeger` (single `<Lager>` with the resolved outgoing
|
||
`kWarenlager`, `nPrio=0`, `kLieferant=0`, `kAnsprechpartner=0`), leave
|
||
`@Warenlagereingaenge` `NULL`, `@nOptions = 0x0102` (`ChargenVorbelegen |
|
||
Stücklistenkorrektur`). Call `Auslieferung.spReserviereBestellpositionen`.
|
||
3. Call `Auslieferung.spPicklistenUebernehmen` with `@Bestellungen =
|
||
<Bestellung><kBestellung>{kAuftrag}</kBestellung></Bestellung>`,
|
||
`@nTeillieferung = 0`, same `@kSessionId`.
|
||
4. Call `Auslieferung.spPicklistenAusliefern` with `@xHinweise = NULL`, `@Pakete =
|
||
<Paket><kBestellung>{kAuftrag}</kBestellung><kVersandart>{kVersandArt}</kVersandart><fGewicht>0</fGewicht></Paket>`
|
||
(using the order's own `Verkauf.tAuftrag.kVersandArt`; **must not be `NULL`** or
|
||
`dbo.tVersand` never gets a row for this order at all — see §3.3 step 8),
|
||
`@nOptions = 2` (`VersandSetzen`), same `@kSessionId`.
|
||
5. Call `Verkauf.spAuftragEckdatenBerechnen(@kAuftrag)` (already implemented).
|
||
6. On any failure in 2-4, call `Auslieferung.spPicklistenVerwerfen(@kBenutzer,
|
||
@kSessionId)` to discard the session's half-finished Picklisten before
|
||
re-throwing.
|
||
7. Optionally `DELETE FROM dbo.tSessionId WHERE kSessionId = @kSessionId` at the
|
||
end (mirrors `SessionManager.Dispose()`); not strictly required for
|
||
correctness since it only affects the GUI's "who's editing what" bookkeeping,
|
||
but keeps `tSessionId` from accumulating rows.
|
||
|
||
## 7. Open questions / remaining unknowns
|
||
|
||
- **Outgoing warehouse resolution** (`PosWarehouseService.FindeWarenlagerFürAusgang()`,
|
||
`jtlCore`, obfuscated): need to determine which `dbo.tWarenLager.kWarenLager` to
|
||
put in `@Laeger`. Likely candidates: the single active local warehouse
|
||
(`nFulfillment = 0`) if there's only one, or a per-shop/subshop default warehouse
|
||
setting somewhere in `dbo.tShopSubshop`/shop config. Needs to be resolved (e.g. by
|
||
decompiling `PosWarehouseService` from `jtlCore.dll` with `ilspycmd`, or reading
|
||
`sp_helptext` of whatever proc/view it ultimately queries) before step 2 above can
|
||
pick the right warehouse automatically instead of hard-coding it.
|
||
- **Article resolution / stock articles vs. free positions**: `spReserviereBestellposition`
|
||
branches heavily on `tArtikel.cLagerVariation`/`cLagerAktiv`/`cLagerArtikel` and
|
||
dropshipping supplier data; our current order items are simple retail SKUs, so the
|
||
"Artikel mit Lagerbestand (Warenläger)" branch should apply, but this needs
|
||
verification against a real low/zero-stock article to see how shortfalls are
|
||
handled (`PosStockPositionService.FehlbestandEinbuchen` in `DeliveryStep`, not yet
|
||
traced).
|
||
- **Serial-number / batch tracked articles**: out of scope for now (our catalog
|
||
doesn't seem to use `cLagerArtikel = 'Y'` serial tracking), but `spPicklistenAusliefern`
|
||
will raise an error (`RAISERROR(..., 18, 3)` / `(..., 18, 6)`) if it ever does and we
|
||
don't supply serial numbers via `Auslieferung.spReserviereSeriennummern` first.
|
||
|
||
## 8. Implementation status
|
||
|
||
Implemented in `src/queries/delivery/` (`session.js`, `warehouse.js`, `reserve.js`,
|
||
`commit.js`, `deliver.js`, `index.js`), wired into `create-order.js` in place of the
|
||
old `Versand.spLieferscheinErstellen`/`spLieferscheinPosErstellen` shortcut. See
|
||
those files' doc comments for the mapping back to the steps in §0/§6.
|
||
|
||
Verified end-to-end against a live test order in `Mandant_3`: real stock decrement,
|
||
`dbo.tPickliste`/`tPicklistePos` created with `nStatus = 40` and a real
|
||
`kLieferscheinPos` link, properly numbered `Lieferschein` (`<AuftragsNr>-001`),
|
||
`nKomplettAusgeliefert = 1`, and the session row cleaned up afterward.
|
||
|
||
One gotcha found only through this live testing, not visible from the decompiled
|
||
C# alone (**`AuslieferungAusliefernContext`/`PosBookPositionService` build their own
|
||
`Paket`-equivalent state in-memory before calling the stored procedure, so this
|
||
particular pitfall is specific to calling the raw SQL procedure directly**): passing
|
||
`@Pakete = NULL` to `Auslieferung.spPicklistenAusliefern` silently skips `dbo.tVersand`
|
||
row creation entirely (see §3.3 step 8 for the full mechanism). Fixed by always
|
||
passing a `<Paket>` element with the order's `kVersandArt` (and a `fGewicht = 0`
|
||
sentinel, since the column is `NOT NULL`). This is what actually makes
|
||
`dbo.tVersand` end up with a row like `kVersandArt = 2` ("Selbstabholer") and a real
|
||
`dVersendet`, matching native JTL-Wawi/POS orders.
|
||
|
||
A second, unrelated tedious/mssql driver limitation was hit and fixed: the `xml` SQL
|
||
type does not reliably round-trip through `sql.Xml` typed parameters/output
|
||
parameters for these particular procedures (`Implicit conversion from data type xml
|
||
to nvarchar is not allowed`). Worked around by passing XML as `NVarChar(MAX)` and
|
||
`CONVERT(XML, @param)`-ing it inside the SQL batch itself, and reading `@xResult
|
||
OUTPUT` back via a trailing `SELECT` instead of a driver-level output parameter.
|