Sales Operations SQL Validation

Verify End-to-End Journey Data Consistency

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

-- Check Preorder, Payment, Stock, and Visit records after full journey
SELECT
    v.visit_id,
    p.preorder_id,
    pay.payment_id,
    s.stock_transaction_id,
    v.status AS visit_status,
    p.status AS preorder_status,
    pay.status AS payment_status
FROM visits v
LEFT JOIN preorders p
  ON v.visit_id = p.visit_id
LEFT JOIN payments pay
  ON v.visit_id = pay.visit_id
LEFT JOIN stock_transactions s
  ON v.visit_id = s.visit_id
WHERE v.visit_id = 'VISIT-20260630-045';

Expected result:

All related records exist and statuses are consistent.

Validate Stock Movement After Transaction

1
2
3
4
5
6
7
8
9
10
11

-- Check stock movement from mobile warehouse to Outlet
SELECT
    product_code,
    from_location,
    to_location,
    quantity,
    transaction_type,
    visit_id
FROM stock_transactions
WHERE visit_id = 'VISIT-20260630-045'
  AND product_code = 'PROD-001';

Expected result:

Quantity moved correctly from mobile warehouse to outlet.

Validate Credit Limit Update After Payment

1
2
3
4
5
6
7
8
9

-- Check credit limit deduction
SELECT
    c.customer_id,
    c.available_credit_limit AS current_limit,
    (c.available_credit_limit + pay.amount) AS previous_limit
FROM customers c
JOIN payments pay
  ON c.customer_id = pay.customer_id
WHERE pay.visit_id = 'VISIT-20260630-045';

Expected result:

Credit limit reduced by the payment amount.

Verify Final Synchronization Status

1
2
3
4
5
6
7
8
9
10

-- Check synchronization log
SELECT
    visit_id,
    sync_type,
    status,
    synced_at,
    error_message
FROM synchronization_log
WHERE visit_id = 'VISIT-20260630-045'
ORDER BY synced_at DESC;

Expected result:

Final sync status = SUCCESS with no error.

Cross-Module Data Reconciliation

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

-- Reconcile total sales, payment, and stock
SELECT
    v.visit_id,
    SUM(p.total_amount) AS total_preorder,
    SUM(pay.amount) AS total_payment,
    SUM(st.quantity) AS total_stock_moved
FROM visits v
LEFT JOIN preorders p
  ON v.visit_id = p.visit_id
LEFT JOIN payments pay
  ON v.visit_id = pay.visit_id
LEFT JOIN stock_transactions st
  ON v.visit_id = st.visit_id
WHERE v.visit_id = 'VISIT-20260630-045'
GROUP BY v.visit_id;

Expected result:

Totals are consistent across modules.

SQL Validation Coverage

Validation Purpose
End-to-End Journey Verify all modules are linked
Stock Movement Confirm inventory update
Credit Limit Verify deduction after payment
Synchronization Log Ensure final sync success
Reconciliation Check data consistency across tables

Back to Project