URS-043 · Link shipments to orders with LOT numbers
Status: PASS · Duration: 44s · Run Date: April 21, 2026
Test Scenarios
Section titled “Test Scenarios”Step 1: Orders list shows ZBO-2025-001
Section titled “Step 1: Orders list shows ZBO-2025-001”
Step 2: BO detail shows both LOT numbers
Section titled “Step 2: BO detail shows both LOT numbers”
Step 3: Packages list
Section titled “Step 3: Packages list”
Step 4: Package items reference source BO
Section titled “Step 4: Package items reference source BO”
Step 5: Tracking + carrier visible on shipment
Section titled “Step 5: Tracking + carrier visible on shipment”
Step 6: Package → Order reference
Section titled “Step 6: Package → Order reference”
Step 6: BO detail LOTs re-confirmed
Section titled “Step 6: BO detail LOTs re-confirmed”
Recordings
Section titled “Recordings”step 01 orders list
Section titled “step 01 orders list”step 02 bo detail lots
Section titled “step 02 bo detail lots”step 03 packages list
Section titled “step 03 packages list”step 04 package linked to order
Section titled “step 04 package linked to order”step 05 tracking visible
Section titled “step 05 tracking visible”step 06 traceability walk
Section titled “step 06 traceability walk”Database Validations
Section titled “Database Validations”Bill-Only Order carries the expected LOT numbers
Section titled “Bill-Only Order carries the expected LOT numbers”ZBO-2025-001 should own 2 line items with LOTs LOT-FL-2024-0334, LOT-SP-2024-0891, owned by the ZuriMED manufacturer org.
SELECT bo.order_number, bo.manufacturer_organization_id, boi.lot_number, op.sku AS product_sku FROM billing_orders bo JOIN billing_order_items boi ON boi.billing_order_id = bo.id JOIN org_products op ON op.id = boi.product_id WHERE bo.id = $1 ORDER BY boi.created_at, boi.id| order_number | manufacturer_organization_id | lot_number | product_sku |
|---|---|---|---|
| ZBO-2025-001 | a1b2c3d4-e5f6-7890-abcd-ef1234567890 | LOT-SP-2024-0891 | SP019N1A |
| ZBO-2025-001 | a1b2c3d4-e5f6-7890-abcd-ef1234567890 | LOT-FL-2024-0334 | FL516SNA |
Shipping Package items reference the Bill-Only Order by text linkage
Section titled “Shipping Package items reference the Bill-Only Order by text linkage”MAN-2025-0156 should have 2 items with order_number=ZBO-2025-001 and order_line_number ∈ (1, 2).
SELECT sp.manifest_id, spi.order_number, spi.order_line_number, spi.quantity FROM shipping_packages sp JOIN shipping_package_items spi ON spi.package_id = sp.id WHERE sp.id = $1 ORDER BY spi.order_line_number| manifest_id | order_number | order_line_number | quantity |
|---|---|---|---|
| MAN-2025-0156 | ZBO-2025-001 | 1 | 5 |
| MAN-2025-0156 | ZBO-2025-001 | 2 | 3 |
Shipping Package surfaces carrier + tracking number
Section titled “Shipping Package surfaces carrier + tracking number”MAN-2025-0147 should report tracking_number=775899342290, carrier=FedEx, status=in_transit.
SELECT manifest_id, tracking_number, carrier, status FROM shipping_packages WHERE id = $1| manifest_id | tracking_number | carrier | status |
|---|---|---|---|
| MAN-2025-0147 | 775899342290 | FedEx | in_transit |
Traceability join: BO ↔ Shipping Package links resolve to LOT-bearing rows
Section titled “Traceability join: BO ↔ Shipping Package links resolve to LOT-bearing rows”Joining billing_order_items ↔ shipping_package_items on order_number should yield 4 rows (2 BO lines × 2 package lines for ZBO-2025-001), every row carrying a LOT, covering LOT-SP-2024-0891, LOT-FL-2024-0334.
SELECT bo.order_number, boi.lot_number, sp.manifest_id, spi.order_line_number, spi.quantity FROM billing_orders bo JOIN billing_order_items boi ON boi.billing_order_id = bo.id JOIN shipping_package_items spi ON spi.order_number = bo.order_number JOIN shipping_packages sp ON sp.id = spi.package_id WHERE bo.id = $1 AND sp.id = $2 ORDER BY spi.order_line_number, boi.created_at, boi.id| order_number | lot_number | manifest_id | order_line_number | quantity |
|---|---|---|---|---|
| ZBO-2025-001 | LOT-SP-2024-0891 | MAN-2025-0156 | 1 | 5 |
| ZBO-2025-001 | LOT-FL-2024-0334 | MAN-2025-0156 | 1 | 5 |
| ZBO-2025-001 | LOT-SP-2024-0891 | MAN-2025-0156 | 2 | 3 |
| ZBO-2025-001 | LOT-FL-2024-0334 | MAN-2025-0156 | 2 | 3 |