DEPOSIT-SQL/i — Retail Banking / Deposits Core

Which "DEPOSIT" is this? This is DEPOSIT-SQL/i, the SQL-PL deposits core in library DEPBK. It is a separate application from the RPG-based banking-app / DEPOSIT/i documented elsewhere. All object, procedure, function, trigger and SQLSTATE names below are this application's own.

A. Overview & Architecture ↑ top

A.1 What it does

DEPOSIT-SQL/i services the everyday life of a book of retail deposit accounts:

• Account holding — each account (BKACCT) carries a type (C checking / S savings), a ledger balance, a per-account annual rate, an overdraft limit, a status (A active / D dormant / C closed), and a last-activity date that drives dormancy.
• Teller postings — every balance movement (deposit, withdrawal, transfer in/out, interest, fee) flows through one procedure, BK_POSTTXN, which validates it, applies the signed effect, writes a BKTXN journal row and a balanced double-entry BKGL pair.
• Overdraft control — a withdrawal/transfer/fee that would drive the balance below the account's negative overdraft line is declined without touching the balance or the ledger; a BEFORE UPDATE trigger enforces the same rule independently.
• Daily interest accrual — BK_INTEREST_RUN walks every active account, computes one day's simple interest (FN_DAILYINT) and posts it — skipping zero-interest and already-posted accounts.
• Dormancy sweep — BK_DORMANCY_SWEEP marks accounts inactive beyond a threshold as dormant and applies a minimum-balance / dormancy fee.
• Day-end close — BK_DAYEND orchestrates the interest run and the dormancy sweep, then returns a GL summary result set (net signed amount per GL account) that must balance to zero across the day.

A.2 Single-choke-point architecture: one balance mover, journaled every time

The application is built around one hard rule: no balance moves except through code that journals it. Concretely:

• All logic is SQL PL. Every rule — the signed ledger effect of a transaction type, the overdraft veto, the interest formula, the dormancy day-count, the GL pairing — is implemented as DB2 for i functions, procedures and triggers in routines.sql. There is no RPG business logic anywhere; RPG appears only as a caller to prove the EXEC SQL CALL reach path.
• One choke point for money. BK_POSTTXN is the single procedure that changes a balance transactionally: validate → derive the signed effect from the transaction type → overdraft-check → update ABAL → journal (one BKTXN row + a balanced BKGL DR/CR pair). The interest run and dormancy sweep do not update balances themselves — they compute an amount and nest a CALL to BK_POSTTXN.
• Only one non-transactional exception, and it still journals. BK_ADJBAL (a manual GL correction) is the sole other balance mover; it computes the delta and routes it back through BK_POSTTXN, so the ledger still ties.
• The view seals the back door. A bare UPDATE BKACCT SET ABAL=... is never the sanctioned path. The statement view BKSTMTV carries an INSTEAD OF UPDATE trigger that reroutes any balance edit through BK_ADJBAL, so even an "ad-hoc correction" is journaled.

The operational benefit: the ledger (BKGL) always ties to the account balances, every movement is reconstructable from BKTXN, and the same procedures are reachable identically from STRSQL, the batch driver, and an EXEC SQL CALL in SQLRPGLE.

A.3 Component & flow

  TELLER / AD-HOC          DAY-END (per business date)      MANUAL CORRECTION
  --------------          ---------------------------      -----------------
  CALL BK_POSTTXN         CALL BK_DAYEND(asof, inactdays)   UPDATE BKSTMTV
    validate                |                                  SET ABAL=...
    sign by TTYPE           +--> BK_INTEREST_RUN                 |
    overdraft veto          |      FN_DAILYINT + nested          v
    UPDATE BKACCT.ABAL      |      CALL BK_POSTTXN            TR_BK_IOU (INSTEAD OF)
    INSERT BKTXN            +--> BK_DORMANCY_SWEEP               |
    INSERT BKGL (DR+CR)     |      FN_MINBAL_FEE + nested        v
        |                   |      CALL BK_POSTTXN            BK_ADJBAL --> BK_POSTTXN
        |                   +--> OPEN C3 (WITH RETURN):           (journaled)
        v                          GL summary per GACCT
     BKACCT  <--writes-- every posting     triggers: TR_BK_OD (BEFORE UPDATE veto)
       |  \                                          TR_BK_AI (AFTER INSERT audit)
       |   +--> BKTXN  (one row per DEP/WDL/XFRI/XFRO/INT/FEE)  TR_BK_AU (AFTER UPDATE audit)
       |   +--> BKGL   (balanced DR+CR pair per posted txn)     TR_BK_IOU (view reroute)
       +------> BKAUD  (trigger-written audit trail)

  BKCTL (single-row "current business date")  <-- driver advances TODAY before each day
  BKRATE (documented rate-tier oracle input)   BKSTMTV (statement view over BKACCT)

A single posting flows: caller issues BK_POSTTXN → it reads the account, derives the signed amount from the transaction type, applies the overdraft veto, updates ABAL, and journals one BKTXN row plus a balanced BKGL DR/CR pair → the BEFORE/AFTER triggers on BKACCT fire (overdraft guard, audit). A day-end run is the same posting path invoked in a loop from BK_INTEREST_RUN and BK_DORMANCY_SWEEP, followed by a GL-summary result set.

A.4 Object inventory

Object	Type	Role
BKACCT	PF	Deposit-account master (the heart of the app).
BKTXN	PF	Append-only teller transaction log.
BKGL	PF	Append-only double-entry general ledger.
BKAUD	PF	Trigger-written audit trail (identity key).
BKRATE	PF	Interest-rate tier table (documented oracle input).
BKCTL	PF	Single-row current-business-date control.
BKSTMTV	View	Statement view over BKACCT (INSTEAD OF UPDATE).
FN_* (4)	SQL functions	Daily interest, min-bal fee, compounding, YMD→DATE.
BK_* (5)	SQL procedures	Post/adjust/interest-run/dormancy-sweep/day-end.
TR_* (4)	Triggers	Overdraft veto, insert/update audit, view reroute.
BKCALL	SQLRPGLE	RPG reach test (EXEC SQL CALL BK_POSTTXN) — built by the driver.

The full catalogue is 4 functions + 5 procedures + 4 triggers, over 5 PFs, 1 view and 1 single-row control table, driven from STRSQL / the batch driver and reachable from one SQLRPGLE caller. Sections D and F expand each.

B. Online / Access Model ↑ top

B.1 STRSQL CALL invocation (there is no 5250 screen)

Honest statement: DEPOSIT-SQL/i has no interactive 5250 display file, no subfile, and no menu program. It is a pure data-layer application: the entire surface is DB2 for i procedures and functions. The "online" experience is therefore an operator (or an application) issuing CALL statements — interactively through STRSQL, from a scripted batch driver, or through an EXEC SQL CALL in a thin SQLRPGLE program. Before any of this, the job's library list must include DEPBK — the tested job runs with LIBL = QSYS QGPL DEPBK QTEMP and CURLIB = DEPBK.

To do this	Issue (STRSQL, or EXEC SQL CALL)
Post a teller deposit	CALL DEPBK.BK_POSTTXN('ACC0000001','DEP',300.00,20260801,'teller deposit',?,?)
Post a withdrawal (overdraft-checked)	CALL DEPBK.BK_POSTTXN('ACC0000004','WDL',50.00,20260801,'teller withdrawal',?,?)
Run the day-end cycle	CALL DEPBK.BK_DAYEND(20260801,180,?,?,?)
Run only the interest accrual	CALL DEPBK.BK_INTEREST_RUN(20260801,?,?)
Run only the dormancy sweep	CALL DEPBK.BK_DORMANCY_SWEEP(20260801,180,?)
Post a manual GL correction	CALL DEPBK.BK_ADJBAL('ACC0000002',16000.00,20260803) (or via the view, below)
Correct a balance through the view	UPDATE DEPBK.BKSTMTV SET ABAL = 16000.00 WHERE ACCTNO = 'ACC0000002'
Advance the business date	UPDATE DEPBK.BKCTL SET TODAY = 20260802 WHERE ONEROW = 'X'

The transaction amount passed to BK_POSTTXN is always positive on input — the procedure derives the signed ledger effect from the transaction type (DEP/XFRI/INT credit the account; WDL/XFRO/FEE debit it). The two OUT parameters return the posting status (P posted, D declined/overdraft, X rejected: closed or unknown account / bad type) and the resulting balance.

Reaching it from SQLRPGLE (the reach test, BKCALL)

The daily driver compiles a small SQLRPGLE program, BKCALL, to prove the compound procedure carries end-to-end over the EXEC SQL CALL path with host-variable IN/OUT parameters. It is not an interactive screen — it runs to completion and DSPLYs its result:

// BKCALL (SQLRPGLE) — the RPG reach test the driver builds
exec sql call DEPBK.BK_POSTTXN(:acct, :ttype, :tamt, :tdate, :tdesc, :pstat, :newbal);
dsply ('RPG1=' + pstat + '/' + %char(newbal) + '/' + %char(sqlcode));
// a second call proves the SAME procedure vetoes an over-limit withdrawal from RPG
exec sql call DEPBK.BK_POSTTXN(:acct, :ttype, :tamt, :tdate, :tdesc, :pstat, :newbal);
dsply ('RPG2=' + pstat + '/' + %char(newbal) + '/' + %char(sqlcode));

A healthy deposit shows RPG1=P/<newbal>/0; an over-limit withdrawal shows RPG2=D/<unchanged bal>/0 (declined, not an SQL error). This is the only RPG in the application, and it holds no business logic.

B.2 Controls, GL & audit workflow (no four-eyes maker–checker)

Honest statement: DEPOSIT-SQL/i does not model a true four-eyes maker–checker / separate-authorization workflow. A posting through BK_POSTTXN is applied immediately; there is no "one user posts, a second approves" step in the code. The control model it does have is enforced entirely in the data layer:

• Single validated choke point. Every balance movement passes BK_POSTTXN's validation: unknown account → status X (no journal); closed account (ASTAT='C') → X (no journal); unrecognised transaction type → X; overdraft breach → status D, balance and ledger untouched (a TSTAT='R' rejected BKTXN row is written as a record of the attempt, but no BKGL rows).
• Double-entry general ledger (BKGL). Every posted transaction writes exactly two BKGL rows — one DR, one CR — that net to zero. The account leg faces a control account (CASH for teller movements, INTEXP for interest, FEEINC for fees). The ledger always ties to the account balances (invariant M1 in the driver: every account's balance = opening balance + its own signed GL net).
• Independent overdraft trigger. TR_BK_OD (BEFORE UPDATE, WHEN N.ABAL < -O.AODLIM) vetoes any UPDATE — even a raw UPDATE BKACCT that bypasses BK_POSTTXN — that would drive a balance past the overdraft line, raising SQLSTATE 75001. Defense in depth for the same rule the procedure already checks.
• Audit trail (BKAUD). TR_BK_AI writes an INSERT audit row on every new account (with its opening balance); TR_BK_AU writes a BALCHG audit row (old->new) on every committed ABAL change. Together they give an after-the-fact accountability trail — not an approval gate.
• Channel gating via the view. TR_BK_IOU (INSTEAD OF UPDATE on BKSTMTV) makes a direct balance edit through the view reroute through BK_ADJBAL → BK_POSTTXN, so the correction is journaled and lands on the exact target; a zero-delta edit is a true no-op. Balances therefore never change by an un-journaled bare UPDATE.

In sum, the control posture is single-choke-point validation + balanced double-entry GL + overdraft enforcement + audit, all in the data layer, rather than a segregation-of-duties approval workflow.

C. Batch / Cycle Procedures ↑ top

DEPOSIT-SQL/i runs its periodic work as a single day-end cycle per business date, orchestrated by BK_DAYEND: interest accrual first, then the dormancy sweep, then a GL-summary result set. The cycle takes its processing date as a CALL parameter (ASOFDATE) and the dormancy threshold (INACTDAYS). A companion single-row control table, BKCTL.TODAY, holds the "current business date" that the driver advances each posting day; the INSTEAD OF UPDATE trigger reads that date because a trigger body has no host-variable input of its own. The idiom is: advance BKCTL.TODAY, do the day's teller postings, then call BK_DAYEND for that date.

-- advance the business date the next postings/day-end run for
UPDATE DEPBK.BKCTL SET TODAY = 20260802 WHERE ONEROW = 'X';

-- run the day-end cycle (interest run + dormancy sweep + GL summary)
CALL DEPBK.BK_DAYEND(20260802, 180, ?, ?, ?);

C.1 Full procedure / trigger table

Object	Kind	Purpose	Calls / functions used	Inputs	Outputs / effect
BK_POSTTXN	Procedure	Post one balance movement (the choke point).	— (called by everything else)	ACCTNO, TTYPE, TAMT(+), TDATE, TDESC	OUT PSTATUS (P/D/X), NEWBAL; BKACCT.ABAL updated; BKTXN row; balanced BKGL DR/CR pair.
BK_ADJBAL	Procedure	Manual GL correction (only non-txn mover).	BK_POSTTXN	ACCTNO, NEWBAL(target), TDATE	Posts the delta as a DEP or WDL through BK_POSTTXN (journaled); zero delta = no-op.
BK_INTEREST_RUN	Procedure	Accrue one day's interest on active accounts.	FN_DAILYINT, nested BK_POSTTXN	ASOFDATE	OUT PROCESSED, TOTALINT; one INT BKTXN per earning account; skips zero & already-posted.
BK_DORMANCY_SWEEP	Procedure	Mark inactive accounts dormant & fee them.	YMD2DATE, FN_MINBAL_FEE, nested BK_POSTTXN	ASOFDATE, INACTDAYS	OUT SWEPT; ASTAT→D on inactive accounts; FEE BKTXN if fee > 0.
BK_DAYEND	Procedure	Orchestrate interest + dormancy, return GL summary.	BK_INTEREST_RUN, BK_DORMANCY_SWEEP	ASOFDATE, INACTDAYS	OUT INTPROCESSED, INTTOTAL, DORMSWEPT; DYNAMIC RESULT SETS 1 (net per GACCT for the day).
TR_BK_OD	Trigger	BEFORE UPDATE overdraft veto.	—	WHEN N.ABAL < -O.AODLIM	SIGNAL SQLSTATE 75001 'overdraft limit exceeded'.
TR_BK_AI	Trigger	AFTER INSERT audit.	—	each BKACCT insert	BKAUD OP='INSERT' with opening balance.
TR_BK_AU	Trigger	AFTER UPDATE OF ABAL audit.	—	each committed ABAL change	BKAUD OP='BALCHG' with old->new.
TR_BK_IOU	Trigger	INSTEAD OF UPDATE on BKSTMTV.	BK_ADJBAL; reads BKCTL.TODAY	UPDATE BKSTMTV SET ABAL=...	Reroutes the balance edit through BK_ADJBAL (journaled).

C.2 The day-end cycle in detail

1 — Interest run (BK_INTEREST_RUN)

A FOR-loop cursor over every ASTAT='A' account (ordered by ACCTNO). For each, a guard query checks whether an INT transaction has already posted for this (ACCTNO, ASOFDATE); if not, it computes FN_DAILYINT(balance, rate) (truncated to cents, zero on a non-positive balance) and, when the amount is positive, nests a CALL BK_POSTTXN(..., 'INT', ...). Interest crediting is a system posting: it does not reset the dormancy clock (ALASTACT).

Expected (day-1 over the 4 seed accounts, INACTDAYS=180):
  ACC1  800.00 * 0.01000/365 = 0.0219.. -> TRUNC2 0.02 -> posted, bal 800.02
  ACC2 16000.00 * 0.01500/365 = 0.6575.. -> TRUNC2 0.65 -> posted, bal 16000.65
  ACC3    50.00 * 0.00500/365 = 0.0006.. -> TRUNC2 0.00 -> skipped (not > 0)
  ACC4 bal -50.00 <= 0 -> FN_DAILYINT short-circuits to 0 -> skipped
  => INTPROCESSED=2  INTTOTAL=0.67

2 — Dormancy sweep (BK_DORMANCY_SWEEP)

A FOR-loop cursor over active accounts computes the calendar day-count from ALASTACT to ASOFDATE (via YMD2DATE + DAYS()). Any account inactive ≥ INACTDAYS days is set ASTAT='D' and, if FN_MINBAL_FEE(balance) > 0 (flat 5.00 when balance < 100), a FEE is posted. A dormant account drops out of the next interest run (the cursor selects only ASTAT='A') and is not re-swept.

Expected (day-1, INACTDAYS=180):
  ACC3 ALASTACT=20250101 -> 20260801 is 577 days >= 180 -> ASTAT='D'
       FN_MINBAL_FEE(50.00)=5.00 -> FEE posted -> bal 50.00-5.00 = 45.00
  => DORMSWEPT=1

3 — GL summary result set (WITH RETURN)

After both steps, BK_DAYEND opens cursor C3 (WITH RETURN), a net-signed sum per GL account for the processing date — SUM(CASE WHEN GSIDE='CR' THEN GAMT ELSE -GAMT END) GROUP BY GACCT. This is the day-end oracle the driver reconciles against a hand-summed BKGL query; across the whole day the legs net to zero.

Day-1 GL summary (net CR-DR per GACCT), hand-derived:
  ACC0000001  +300.02   ACC0000002 +1000.65   ACC0000003  -5.00
  ACC0000004   -50.00   CASH      -1250.00     FEEINC      +5.00   INTEXP  -0.67
  sum of all legs across the day = 0  (double-entry proof)

C.3 Ordering & idempotency

• Teller postings before day-end. Day-end interest is computed on the day's ending balances, so post the day's deposits/withdrawals first, then call BK_DAYEND.
• Interest before dormancy (fixed inside BK_DAYEND). The order is enforced by the orchestrator: BK_INTEREST_RUN runs before BK_DORMANCY_SWEEP, so an account earns its interest for the day it is still active before being marked dormant.
• Idempotent per date. Re-running BK_DAYEND for a date already processed posts no new interest (the "already posted INT for this (acct,date)" guard) and sweeps nothing new (an ASTAT='D' account is excluded from both cursors). The driver verifies a full-chain re-run moves zero additional money.
• Advance BKCTL for real day-2 progress. To process a new business day, advance BKCTL.TODAY and call BK_DAYEND with the new ASOFDATE; the guard keys off the date, so a genuinely new day accrues again.

D. Data Files (data dictionary) ↑ top

All files are in library DEPBK, grounded in schema.sql. Dates are stored as INT in YYYYMMDD form; money is DECIMAL(13,2); rates are DECIMAL(7,5) as a fraction (0.01500 = 1.5%).

BKACCT — Deposit-account master (PK ACCTNO)

Field	Type	Meaning
ACCTNO	CHAR(10)	Account number (PK), e.g. ACC0000001.
CUSTNO	CHAR(6)	Owning customer.
ATYPE	CHAR(1)	C checking, S savings.
ABAL	DECIMAL(13,2)	Current ledger balance.
ARATE	DECIMAL(7,5)	Annual interest rate as a fraction (0.01500 = 1.5%).
AODLIM	DECIMAL(13,2)	Overdraft limit (0 = no overdraft allowed).
ASTAT	CHAR(1)	A active, D dormant, C closed.
AOPEN	INT	Open date (YYYYMMDD).
ALASTACT	INT	Last non-interest activity date — the dormancy clock.

BKTXN — Teller transaction log (append-only; PK TSEQ)

Field	Type	Meaning
TSEQ	INT	Caller-assigned ordering/uniqueness key (PK), MAX(TSEQ)+1.
ACCTNO	CHAR(10)	Account this movement belongs to.
TTYPE	CHAR(4)	DEP, WDL, XFRI (transfer in), XFRO, INT (interest), FEE.
TAMT	DECIMAL(13,2)	Signed ledger effect (+ increases, - decreases the balance).
TDATE	INT	Transaction date (YYYYMMDD).
TSTAT	CHAR(1)	P posted, R rejected (e.g. overdraft-declined attempt).
TDESC	VARCHAR(40)	Free-text description.

BKGL — General ledger (append-only double-entry; PK GSEQ)

Field	Type	Meaning
GSEQ	INT	GL sequence (PK), MAX(GSEQ)+1.
TSEQ	INT	The BKTXN row this leg belongs to.
GACCT	VARCHAR(12)	GL account: the deposit acct#, or a control account (CASH/INTEXP/FEEINC).
GSIDE	CHAR(2)	DR or CR (carries the sign).
GAMT	DECIMAL(13,2)	Always positive; GSIDE carries the sign.
GDATE	INT	Posting date (YYYYMMDD).

Every posted BKTXN produces exactly two BKGL rows (one DR, one CR) that net to zero.

BKAUD — Audit trail (PK ASEQ identity)

Field	Type	Meaning
ASEQ	INT identity	Generated-always audit sequence (PK).
OP	CHAR(10)	INSERT (new account) or BALCHG (balance change).
ACCTNO	CHAR(10)	Account affected.
DETAIL	VARCHAR(60)	Trigger-written detail (opening balance, or old->new).

BKRATE — Interest-rate tiers (PK MINBAL)

Field	Type	Meaning
MINBAL	DECIMAL(13,2)	Balance breakpoint (PK); highest match ≤ balance wins.
ARATE	DECIMAL(7,5)	Annual rate for that tier.

Seeded tiers: 0→0.00500, 1000→0.01000, 10000→0.01500. Note: this table is a documented oracle input only — interest is actually posted from each account's own BKACCT.ARATE, kept simple and exact.

BKCTL — Current-business-date control (one row, PK ONEROW='X')

Field	Type	Meaning
ONEROW	CHAR(1)	Always 'X' (PK, single row).
TODAY	INT	Current business date (YYYYMMDD), read by TR_BK_IOU; the driver advances it per day.

Seeded row: ('X', 20260801).

BKSTMTV — Statement view (over BKACCT)

View exposing ACCTNO, CUSTNO, ATYPE, ABAL, ASTAT. Updates are intercepted by TR_BK_IOU (INSTEAD OF UPDATE): an ABAL edit reroutes through BK_ADJBAL (which journals via BK_POSTTXN using BKCTL.TODAY as the posting date). A same-value edit is a no-op.

Relationships

• BKTXN.ACCTNO → BKACCT.ACCTNO (per-account teller journal; TSEQ = MAX+1).
• BKGL.TSEQ → BKTXN.TSEQ (each GL leg belongs to a transaction; GACCT is the account# or a control account).
• BKAUD.ACCTNO → BKACCT.ACCTNO (audit references the account).
• BKACCT.ARATE is the posted rate; BKRATE is a documented tier oracle, not a runtime lookup.
• BKCTL is standalone single-row control; BKSTMTV is a view over BKACCT.

E. Operations Runbook ↑ top

E.1 Day-in-the-life

1. Advance the business date: UPDATE DEPBK.BKCTL SET TODAY = <YYYYMMDD> WHERE ONEROW='X'.
2. Post the day's teller activity as it arrives: CALL DEPBK.BK_POSTTXN(acct, ttype, amount, date, desc, ?, ?) — amount always positive; check the returned status (P/D/X) and balance.
3. Handle manual corrections through the view (UPDATE DEPBK.BKSTMTV SET ABAL=...) or directly via BK_ADJBAL; both journal.
4. Run the day-end cycle for the date: CALL DEPBK.BK_DAYEND(<asof>, 180, ?, ?, ?).
5. Read back and reconcile the day-end figures (below).

Pre-checks: confirm BKCTL.TODAY holds the intended date; confirm the job's library list includes DEPBK; amounts passed to BK_POSTTXN are positive (the type carries the sign).

Post-checks after day-end:

• INTPROCESSED / INTTOTAL reconcile to the accounts that earned a truncated-positive daily interest; exactly one INT BKTXN per account per date.
• DORMSWEPT equals the accounts newly past the inactivity threshold; those are now ASTAT='D' (and fee'd once if under the min-balance line).
• The GL summary result set for the date nets to zero across all accounts (double-entry proof).
• No dormant/closed account earned interest; no account was fee'd twice.

E.2 Reconciling the day-end figures

Read the day's GL summary the same way BK_DAYEND returns it, and reconcile against the invariants the volume simulation checks against an independent JS oracle:

SELECT GACCT, SUM(CASE WHEN GSIDE='CR' THEN GAMT ELSE -GAMT END) AS NETAMT
  FROM DEPBK.BKGL WHERE GDATE = <YYYYMMDD> GROUP BY GACCT ORDER BY GACCT;

• Double-entry balances. SUM(CASE WHEN GSIDE='CR' THEN GAMT ELSE -GAMT END) over the whole day = 0. Every posted transaction contributed a DR and a CR of equal magnitude.
• GL ties to the ledger. For each account, opening balance + its own signed GL net = current ABAL. This is invariant M1 — the ledger and the account balances never drift.
• Interest total. INTTOTAL = Σ TRUNC2(ABAL × ARATE / 365) over exactly the active accounts with a positive truncated amount (non-positive balances contribute 0).
• Fees. Each newly-dormant account with balance < 100.00 contributed one 5.00 FEE (FEEINC CR leg, account DR leg).

E.3 Failure & re-run rules

Situation	Behaviour	Action
Posting to an unknown or closed account	BK_POSTTXN returns status X, no balance change, no journal.	Correct the account number / re-open the account, then retry. No cleanup needed.
Overdraft-declined withdrawal	Status D; balance and BKGL untouched; a TSTAT='R' BKTXN row records the attempt.	Reduce the amount or raise the overdraft limit; retry. The rejected row is an audit artifact, not an error.
Bad transaction type	Status X, no effect.	Use one of DEP/WDL/XFRI/XFRO/INT/FEE.
Re-running day-end for a processed date	Posts 0 new interest (already-posted guard) and sweeps nothing new (D accounts excluded).	Safe no-op. Idempotent per date. Advance BKCTL/ASOFDATE to process a new day.
Day-end fails partway	Accounts already posted for the date are guarded; the rest have not been touched.	Re-run BK_DAYEND for the same date: posted accounts are skipped, the rest catch up.
Dormancy re-sweep	An ASTAT='D' account is excluded from the cursor.	Naturally idempotent — never re-fee'd.
Direct UPDATE BKACCT past the overdraft line	TR_BK_OD vetoes with SQLSTATE 75001; balance untouched.	Move the balance through BK_POSTTXN/BK_ADJBAL instead; within-limit direct updates succeed but are un-journaled — avoid them.

F. Developer Reference ↑ top

The complete SQL-PL surface, from routines.sql. All objects are in library DEPBK.

F.1 Functions (4)

FN_DAILYINT (BAL DECIMAL(13,2), RATE DECIMAL(7,5)) RETURNS DECIMAL(13,2)

One day of simple interest, 365-day convention: BAL × RATE / 365, truncated to cents by the DECIMAL assignment. Returns 0 on a non-positive balance (overdrawn accounts earn nothing).

FN_MINBAL_FEE (BAL DECIMAL(13,2)) RETURNS DECIMAL(13,2)

Flat 5.00 when balance < 100.00, else 0.00. Used as the dormancy fee too.

FN_COMPOUND (PRIN DECIMAL(13,2), RATE DECIMAL(7,5), DAYS INT) RETURNS DECIMAL(13,2)

Compound interest PRIN × ((1 + RATE/365)^DAYS - 1), computed with an explicit WHILE loop (no POWER builtin), truncated to cents. Returns 0 if PRIN or DAYS ≤ 0. Exercises SQL-PL iteration.

YMD2DATE (D INT) RETURNS DATE

Converts a YYYYMMDD integer to a SQL DATE (via SUBSTR/CHAR string build), so DAYS() can compute a real calendar diff. Used only by the dormancy day-count.

F.2 Procedures (5)

BK_POSTTXN (IN P_ACCTNO, P_TTYPE, P_TAMT, P_TDATE, P_TDESC; OUT PSTATUS, NEWBAL)

The single balance-moving choke point. Reads the account (NOT FOUND → PSTATUS='X'); rejects a closed account (ASTAT='C' → X). Derives the signed effect: DEP/XFRI/INT credit (+P_TAMT), WDL/XFRO/FEE debit (-P_TAMT), any other type → X. On an overdraft breach (V_CURBAL + V_SIGNED < -V_ODLIM) → PSTATUS='D', writes a TSTAT='R' BKTXN, no balance/GL change. Otherwise updates ABAL (advancing ALASTACT only for customer movements DEP/WDL/XFRI/XFRO, never INT/FEE), inserts a posted BKTXN, and inserts a balanced BKGL pair (account leg vs CASH/INTEXP/FEEINC). PSTATUS='P'.

BK_ADJBAL (IN P_ACCTNO, P_NEWBAL, P_TDATE)

Manual GL correction: computes the delta to the target balance and routes it back through BK_POSTTXN as a DEP (delta > 0) or WDL (delta < 0) with description 'manual adjustment'. Zero delta returns early (no-op). The only non-transactional balance mover, and it still journals.

BK_INTEREST_RUN (IN ASOFDATE; OUT PROCESSED, TOTALINT)

FOR-loop cursor over ASTAT='A' accounts (columns aliased CUR_*, see F.4). Per account: guard against an already-posted INT for (acct, date); compute FN_DAILYINT; if > 0, nested CALL BK_POSTTXN(..., 'INT', ...); accumulate count and total on a posted result.

BK_DORMANCY_SWEEP (IN ASOFDATE, INACTDAYS; OUT SWEPT)

FOR-loop cursor over active accounts. Day-count = DAYS(YMD2DATE(ASOFDATE)) - DAYS(YMD2DATE(ALASTACT)); if ≥ INACTDAYS, set ASTAT='D' and, if FN_MINBAL_FEE > 0, nested CALL BK_POSTTXN(..., 'FEE', ...). Idempotent: D accounts are not selected.

BK_DAYEND (IN ASOFDATE, INACTDAYS; OUT INTPROCESSED, INTTOTAL, DORMSWEPT) DYNAMIC RESULT SETS 1

Declares a WITH RETURN cursor (net signed GL per GACCT for the date), calls BK_INTEREST_RUN then BK_DORMANCY_SWEEP, then OPENs the cursor so a batch CALL receives the GL-summary result set.

F.3 Triggers (4) & the view

TR_BK_OD — BEFORE UPDATE on BKACCT, WHEN (N.ABAL < -O.AODLIM)

Independent overdraft guard: SIGNALs SQLSTATE 75001 'overdraft limit exceeded' — vetoes even a raw UPDATE that bypasses BK_POSTTXN.

TR_BK_AI — AFTER INSERT on BKACCT

Writes a BKAUD OP='INSERT' row with 'opened bal='||CHAR(N.ABAL).

TR_BK_AU — AFTER UPDATE OF ABAL on BKACCT

Writes a BKAUD OP='BALCHG' row with CHAR(O.ABAL)||'->'||CHAR(N.ABAL) on every committed balance change.

TR_BK_IOU — INSTEAD OF UPDATE on BKSTMTV

Reads BKCTL.TODAY and calls BK_ADJBAL(O.ACCTNO, N.ABAL, today), rerouting a view balance edit through the journaled corrector rather than a bare base-table write.

F.4 SQL-PL patterns (and the platform notes that shaped them)

The routines rely on these SQL-PL idioms, several written a specific way for documented engine reasons (cited from the source's own comments and SQLPL-SIM-FINDINGS.md):

• Single choke point + nested CALL. BK_INTEREST_RUN/BK_DORMANCY_SWEEP never UPDATE a balance directly — they CALL BK_POSTTXN, so every movement is validated and journaled the same way. This proves compound routines nest and that triggers fire from PL-issued UPDATEs.
• FOR-loop cursor columns aliased CUR_* (SQLPL-PLAT-04). A FOR loop binds each selected column name as an in-scope PL variable via a blind text replace with no table awareness; a bare cursor column sharing a name with a column on another table used later in the body would be corrupted. Aliasing (SELECT ACCTNO AS CUR_ACCTNO ...) avoids the collision.
• Unqualified scalar-function calls (SQLPL-PLAT-03). Functions are invoked unqualified (FN_DAILYINT(...), not DEPBK.FN_DAILYINT(...)); a schema/library-qualified scalar-function call raises a syntax error. DEPBK on the library list makes the unqualified call resolve identically.
• Negative-argument spacing (SQLPL-PLAT-07, fixed platform-side). Expressions like -P_TAMT / -V_DIFF (no space) once spliced a negative literal's leading - against the source-text unary minus to form -- (an SQL comment). Fixed in engine commit 407bee974 (negative literals now parenthesized); the workaround has been removed here and re-verified at volume.
• DECIMAL SET-assignment truncation (SQLPL-PLAT-06). Assignment to a DECIMAL truncates to cents on an IEEE754 double with no epsilon; reconciliation of subtraction-derived amounts allows a one-cent tolerance (see the note in E.2).
• Identity DDL (SQLPL-PLAT-01/02). ASEQ INT GENERATED ALWAYS AS IDENTITY with no explicit NOT NULL and no separate PK clause is the accepted, semantically-equivalent form.
• WITH RETURN result set. BK_DAYEND declares a DYNAMIC RESULT SETS 1 cursor and OPENs it last, surfacing the GL summary to a batch CALL (and to SQLRPGLE).
• Single-row control read from a trigger. TR_BK_IOU reads BKCTL.TODAY because a trigger body has no host-variable input; the driver advances that row per business day.

F.5 Application SQLSTATE / status table

DEPOSIT-SQL/i signals only one application SQLSTATE (from the overdraft trigger); the procedures otherwise communicate outcomes through the PSTATUS OUT parameter rather than SIGNALs.

Code	Kind	Raised / returned by	Meaning
75001	SQLSTATE (SIGNAL)	TR_BK_OD	Overdraft limit exceeded — an UPDATE would drive ABAL below -AODLIM. Vetoes even a bypassing raw UPDATE.
PSTATUS='P'	OUT status	BK_POSTTXN	Posted: balance updated, BKTXN + balanced BKGL pair written.
PSTATUS='D'	OUT status	BK_POSTTXN	Declined for overdraft: balance/GL untouched, a TSTAT='R' BKTXN records the attempt.
PSTATUS='X'	OUT status	BK_POSTTXN	Rejected: account not found, account closed, or unrecognised transaction type.

G. Glossary ↑ top

ALASTACT (dormancy clock)

The account's last non-interest activity date. Advanced only by customer movements (DEP/WDL/XFRI/XFRO); INT and FEE postings are system-generated and deliberately do not reset it, so an interest-earning account can still go dormant.

Choke point

The single procedure (BK_POSTTXN) every balance movement passes through, so validation, the overdraft check and journaling happen in exactly one place.

Control account (GL)

A non-account GL leg an account movement faces: CASH for teller movements, INTEXP for interest, FEEINC for fees.

Day-end (BK_DAYEND)

The per-business-date cycle: interest run, then dormancy sweep, then a GL-summary result set.

Dormancy

Marking an account inactive (ASTAT='D') after no customer activity for a threshold number of days; it stops earning interest and may take a min-balance fee once.

Double-entry / GL pair

Every posted transaction writes two BKGL rows (one DR, one CR) that net to zero, so the ledger always balances and ties to the account balances.

Idempotent

Safe to run again with the same result. The day-end cycle is idempotent per date (already-posted INT guard + dormant-account exclusion).

INSTEAD OF UPDATE

A view trigger (TR_BK_IOU) that replaces a direct UPDATE with custom logic — here, rerouting a balance edit on BKSTMTV through the journaled corrector BK_ADJBAL.

Overdraft limit (AODLIM)

The most an account may go negative. A movement pushing the balance below -AODLIM is declined (status D); 0 means no overdraft allowed.

SQL PL

DB2 for i's procedural SQL language. The entire business logic here — procedures, functions, triggers — is SQL PL; RPG appears only as a caller.

SQLRPGLE / EXEC SQL CALL

RPG with embedded SQL. The BKCALL reach-test program drives BK_POSTTXN via EXEC SQL CALL with host-variable IN/OUT parameters; no business logic lives in RPG.

STRSQL

Start Interactive SQL — the IBM i utility from which an operator issues the CALL and UPDATE statements that drive this screenless application.

TSEQ / GSEQ

The caller-assigned monotonic keys for BKTXN and BKGL (each derived as MAX(...)+1), giving every transaction and every GL leg a unique ordering key.

WITH RETURN result set

A cursor a procedure opens and leaves open (DYNAMIC RESULT SETS 1) so its caller receives the rows — here the day-end GL summary.