PL/SQL stored procedures to microservices: lift rules out of the database

1CREATE OR REPLACE PROCEDURE apply_late_fees (p_run_date IN DATE) IS
2  CURSOR c_overdue IS
3    SELECT inv.invoice_id, inv.balance, c.segment
4      FROM invoices inv
5      JOIN customers c ON c.customer_id = inv.customer_id
6     WHERE inv.due_date < p_run_date - 15
7       AND inv.status = 'OPEN';
8  v_fee invoices.balance%TYPE;
9BEGIN
10  FOR r IN c_overdue LOOP
11    IF r.segment = 'GOV' THEN
12      v_fee := 0;
13    ELSE
14      v_fee := GREATEST(ROUND(r.balance * 0.015, 2), 25);
15    END IF;
16    UPDATE invoices
17       SET balance = balance + v_fee,
18           late_fee_count = late_fee_count + 1
19     WHERE invoice_id = r.invoice_id;
20  END LOOP;
21  COMMIT;
22EXCEPTION
23  WHEN OTHERS THEN NULL;
24END apply_late_fees;

1openapi: 3.1.0
2info: { title: Late fee assessment, version: 1.0.0 }
3paths:
4  /late-fee-runs:
5    post:
6      operationId: runLateFeeAssessment
7      x-pyd-source: apply_late_fees.prc:1-25
8      x-pyd-decision: BILL-LATEFEE-01
9      x-pyd-side-effects:
10        - UPDATE invoices.balance, invoices.late_fee_count
11        - TRIGGER trg_invoices_au INSERT invoice_audit
12      requestBody:
13        content:
14          application/json:
15            schema:
16              type: object
17              required: [runDate]
18              properties:
19                runDate: { type: string, format: date }
20      responses:
21        "202": { description: Run accepted }
22# BILL-LATEFEE-01 (hit policy FIRST)
23#  segment = "GOV"          -> fee 0
24#  daysPastDue > 15, other  -> max(round(balance * 0.015, 2), 25)
25# review: WHEN OTHERS THEN NULL swallows all errors