Dropship SQL Validation

Scope

SQL validation confirms that a DROPSHIP-SPECIAL Sales Order is created correctly while standard inventory remains unaffected.

1
2
3
4
5
6
7
8
9
10
11
12
13
14

-- Check Sales Order record
SELECT
    order_id,
    legal_number,
    customer_id,
    business_model,
    status,
    delivery_date,
    created_at
FROM sales_orders
WHERE business_model = 'DROPSHIP-SPECIAL'
  AND product_code = 'DROPSHIP-PROD-001'
ORDER BY created_at DESC
LIMIT 1;

Expected result:

  • legal_number starts with LEGAL-XXX.
  • status = 'SUBMITTED'.
  • delivery_date = device date + 3 days.
  • business_model = 'DROPSHIP-SPECIAL'.

Validate Hardcoded Available Stock Display

1
2
3
4
5
6
7
8
9

-- Check product stock display logic
SELECT
    product_code,
    business_model,
    displayed_stock,
    actual_stock,
    available_stock
FROM product_master
WHERE product_code = 'DROPSHIP-PROD-001';

Expected result:

  • displayed_stock = 999999 or 999.999.
  • actual_stock may be NULL or not updated because of HARD CODE behavior.

Validate No Stock Movement in Inventory Ledger

1
2
3
4
5
6
7
8
9
10
11
12

-- Check that no stock deduction occurred in inventory
SELECT
    transaction_id,
    product_code,
    movement_type,
    quantity,
    from_location,
    to_location,
    transaction_date
FROM stock_ledger
WHERE product_code = 'DROPSHIP-PROD-001'
ORDER BY transaction_date DESC;

Expected result:

Returns 0 rows or no deduction records because stock is not reflected in standard inventory.

Cross-Check Order vs Inventory Consistency

1
2
3
4
5
6
7
8
9
10
11

-- Verify order exists but inventory not affected
SELECT
    so.order_id,
    so.business_model,
    so.quantity,
    sl.transaction_id AS inventory_transaction
FROM sales_orders so
LEFT JOIN stock_ledger sl
  ON so.product_code = sl.product_code
 AND sl.transaction_date >= so.created_at
WHERE so.order_id = 'SO-LEGAL-20260629-045';

Evidence Captured

  • Sales Order record with correct prefix and status.
  • Product master record showing hardcoded displayed stock.
  • Stock ledger query showing no standard inventory movement.
  • Cross-check showing order exists while inventory transaction is NULL.

Back to Project