Preorder SQL Validation

Scope

SQL validation reconciles Preorder records, pricing totals, blocked order state, and available credit adjustments.

Verify Preorder Creation and Credit Limit Deduction

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

-- Check Preorder record and credit impact
SELECT
    p.preorder_id,
    p.customer_id,
    p.total_amount,
    p.payment_method,
    p.status,
    c.available_credit_limit,
    c.used_credit_limit
FROM preorders p
JOIN customers c
  ON p.customer_id = c.customer_id
WHERE p.preorder_id = 'PO-20260630-089';

Expected result:

  • payment_method = 'CREDIT'.
  • status = 'SUBMITTED'.
  • available_credit_limit has been reduced by the preorder total amount.

Validate Credit Limit Before & After Preorder

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

-- Compare credit limit before
SELECT
    customer_id,
    available_credit_limit AS before_limit
FROM customers
WHERE customer_id = 'CUST-DEMO-078';

-- After Preorder (run after creation)
SELECT
    customer_id,
    available_credit_limit AS after_limit,
    (before_limit - after_limit) AS deducted_amount
FROM customers
WHERE customer_id = 'CUST-DEMO-078';

Expected result:

deducted_amount equals the Preorder final total.

Validate Pricing Calculation in Preorder

1
2
3
4
5
6
7
8
9
10

-- Check detailed pricing breakdown
SELECT
    preorder_id,
    subtotal,
    discount_amount,
    ppn_amount,
    final_total,
    ROUND(subtotal * 0.11, 0) AS expected_ppn
FROM preorders
WHERE preorder_id = 'PO-20260630-089';

Expected result:

  • final_total is correctly calculated.
  • ppn_amount matches 11% of subtotal or the configured rate.

Validate No Preorder if Credit Limit Exceeded

1
2
3
4
5
6
7
8
9

-- Attempt to find blocked preorder
SELECT
    preorder_id,
    status,
    rejection_reason
FROM preorders
WHERE customer_id = 'CUST-DEMO-078'
  AND status IN ('BLOCKED')
ORDER BY created_at DESC;

Expected result:

Record exists with rejection_reason containing CREDIT_LIMIT_EXCEEDED or similar.

SQL Validation Coverage

Validation Purpose
Preorder + Credit Join Confirm credit limit deduction after CREDIT Preorder
Before / After Limit Verify exact amount deducted
Pricing Breakdown Validate subtotal, discount, and PPN calculation
Blocked Preorder Ensure system rejects when limit is insufficient

Back to Project