LOANSVC/i — Consumer Loan Servicing

SteelFrame X application operation manual  ·  ← back to Operation Manuals  ·  Sign On

LOANSVC/i is a consumer-loan servicing application: loan origination and disbursement, daily interest accrual with missed-due-date detection, weekly delinquency-fee assessment, and a monthly charge-off plus portfolio valuation (WAC / ALLL) close. Every business rule lives in DB2 for i SQL PL; the RPG programs orchestrate it through embedded EXEC SQL CALL. This manual is the reference for the operator who runs the online screen and the periodic batch cycles, and for the developer maintaining the application. It is grounded entirely in the committed source (sqlpl-rpg-app/src/schema.sql, routines.sql, the five *.rpgle.txt programs, LNINQDF.dds.txt, and the test/ln_*.mjs drivers).

A. Overview & Architecture ↑ top

A.1 What it does

LOANSVC/i services the full life of a consumer instalment loan:

• Origination — underwrite an application against the customer's credit-score rate tier, approve or reject, and disburse an approved loan (set it active, schedule the first payment, post the disbursement).
• Daily accrual & due-date tracking — accrue simple daily interest on every open loan, and detect loans whose scheduled payment date has passed unpaid, marking them delinquent.
• Payment application — apply a borrower payment through a fees→interest→principal waterfall, clearing delinquency and, at a zero balance, paying the loan off.
• Weekly delinquency fees — assess a flat late fee per missed cycle on delinquent loans.
• Monthly charge-off & close — write off loans delinquent 3+ cycles, then snapshot the portfolio's counts, balances, weighted-average coupon (WAC), and loss reserve (ALLL).

A.2 Two-layer architecture: SQL PL owns logic, SQLRPGLE orchestrates

The application is deliberately split into two layers with a hard boundary:

• SQL PL layer (business logic). All rules — underwriting, the interest formula, the payment waterfall, delinquency progression, charge-off, valuation — are implemented as DB2 for i functions, stored procedures, and table triggers in routines.sql. This layer also enforces integrity through triggers (non-negative balances, positive principal, audit).
• SQLRPGLE layer (orchestration only). Each RPG program is a thin driver that reads its processing date from the control row and issues EXEC SQL CALL against the SQL-PL procedures, checking SQLCODE after each call and DSPLYing the result. No loan math is coded in RPG — the RPG never computes interest, applies a waterfall, or decides a charge-off; it only drives the procedures that do.

The benefit for operations: the rules are one auditable place (the SQL-PL routines), the same procedures are reachable from RPG batch jobs, the interactive screen, and ad-hoc STRSQL alike, and the RPG side stays trivial. Everything runs in library LOANSVC.

A.3 Component & flow

  ORIGINATION            ONLINE                 BATCH (periodic)
  -----------            ------                 ----------------
  LNAPPLY  ---CALL--->   LNINQPG (5250)         LNDAILY   ---> LN_ACCRUEDAILY -> LN_ACCRUEONE
    LN_APPLY               subfile over            (accrual)    LN_DUECHECK
    LN_DISBURSE            LNLOAN (A/D)          LNWEEKLY  ---> LN_WEEKLYFEES
                          opt 1 -> LN_POSTPAYMENT  (fees)
                          opt 4 -> LN_INQUIRE    LNMONTHLY ---> LN_CHARGEOFF
                                                   (close)      LN_MONTHLYCLOSE
                          \                    /
                           \                  /
                            v                v
     LNCTL (control row: ASOFDATE, CLOSEYM)  <-- operator sets before each batch run
            |
            v
     LNLOAN  <---writes---  every procedure          triggers: TR_LOAN_BI/AI/AU/BU (LNLOAN)
       |  \                                                     TR_LOANV_IOU (LNLOANV view)
       |   +--> LNTXN   (one row per DISB/ACCR/PMT/LFEE/CHGO, seq from LNTXNSEQ)
       |   +--> LNAUD   (trigger-written audit trail)
       +------> LNCLOSE (monthly snapshot)   LNWKLOG (weekly fee-run log)

A single processing event (say, a daily accrual) flows: operator sets LNCTL.ASOFDATE → submits LNDAILY → RPG reads the date and CALLs LN_ACCRUEDAILY → the procedure loops the portfolio, updating LNLOAN balances and inserting LNTXN rows → triggers fire on those base-table changes (audit / integrity).

A.4 Object inventory

Object	Type	Role
LNCUST	PF	Customer master.
LNRATETIER	PF	Credit-score rate/limit tiers.
LNLOAN	PF	Loan master (the heart of the app).
LNTXN	PF	Per-loan transaction journal.
LNAUD	PF	Trigger-written audit trail.
LNCLOSE	PF	Monthly portfolio valuation snapshot.
LNWKLOG	PF	Weekly fee-run log.
LNCTL	PF	One-row batch control table.
LNLOANV	View	LNLOAN⨯LNCUST join for maintenance.
LNTXNSEQ	Sequence	Transaction-sequence generator.
LN_* (8)	SQL functions	Date math, tier lookup, daily interest, delinquency bucket.
LN_* (12)	SQL procedures	Apply/disburse/accrue/due-check/pay/fees/charge-off/close/inquire/portfolio.
TR_* (5)	Triggers	Integrity, audit, view rerouting.
LNAPPLY	SQLRPGLE	Origination driver.
LNDAILY	SQLRPGLE	Daily cycle driver.
LNWEEKLY	SQLRPGLE	Weekly cycle driver.
LNMONTHLY	SQLRPGLE	Monthly cycle driver.
LNINQPG	SQLRPGLE	Interactive inquiry/maintenance program.
LNINQDF	DSPF (DDS)	Subfile display file for LNINQPG.

The full object catalogue is 8 functions + 12 procedures + 5 triggers, over 8 PFs, 1 LF/view and 1 sequence, driven by 5 programs and 1 DDS member. Sections D and F expand each.

B. Online Transactions & Screens ↑ top

B.1 The command/entry line

LOANSVC/i has no CICS transaction identifiers and no menu-driven transid switch. On IBM i, each program is reached by name from a 5250 command-entry line (or a JOBQ/scheduler for the batch jobs). The operator equivalent of "type a transid and Enter" is "type a CALL command and Enter". Before invoking anything, the job's library list must include LOANSVC — the tested jobs run with LIBL = QSYS QGPL LOANSVC QTEMP and CURLIB = LOANSVC.

To do this	Type on the command line
Open the online inquiry/maintenance screen	CALL LOANSVC/LNINQPG
Originate + disburse (the packaged origination driver)	CALL LOANSVC/LNAPPLY
Run the daily cycle	CALL LOANSVC/LNDAILY (or SBMJOB it)
Run the weekly cycle	CALL LOANSVC/LNWEEKLY
Run the monthly cycle	CALL LOANSVC/LNMONTHLY
Set/inspect the batch control date (ad hoc SQL)	STRSQL → UPDATE LOANSVC/LNCTL ...

The batch drivers take no CALL parameters — each reads its date from the LNCTL control row (see section C), so a scheduled submission is a bare CALL. Only LNINQPG is interactive; the four batch programs run to completion and DSPLY a one-line result per step.

B.2 The inquiry+maintenance screen (LNINQPG / LNINQDF)

LNINQPG is the one interactive program. It presents a subfile (DDS record SFLREC under control record SFLCTLR, SFLPAG(10) per page, SFLSIZ(50)) listing every open loan (LSTAT IN ('A','D')), lets the operator key an option against any row, processes the options, then reloads the subfile so the effect of a maintenance action is visible immediately (real subfile-refresh discipline, not a static load-once list).

Subfile columns (SFLREC)

Field	Type (DDS)	Shows
SOPT	1A input/output	Option: 1=Post Payment, 4=Detail.
SLOANNO	8A output	Loan number.
SCUSTNO	6A output	Customer number.
SLSTAT	1A output	Status: A active, D delinquent.
SPRINBAL	11A output	Remaining principal (rendered from PRINBAL).
SACCRINT	11A output	Accrued-unpaid interest.
SBUCKET	10A output	Delinquency bucket from FN_DELQBUCKET(MISSEDCNT).
AMTIN	9S,2 input	Payment amount on the control line, used with option 1.

Actions & keys

• Option 1 — Post Payment. Key 1 against a loan row and type the amount in Amt, then Enter. LNINQPG reads ASOFDATE from LNCTL and calls LN_POSTPAYMENT(loan, amount, asof). The payment applies fees→interest→principal; delinquency resets (MISSEDCNT=0), the due date rolls +30d; a full-balance payment marks the loan paid off (LSTAT='F'). Result is DSPLYed (APPLIED/REJECTED + SQLCODE) and the subfile reloads.
• Option 4 — Detail. Key 4, Enter. Calls LN_INQUIRE and DSPLYs the loan's customer, status and balances.
• Enter — process keyed options, reload the subfile.
• F3 (Exit) / F12 (Cancel) — leave the program (both DDS CA03/CA12; the program exits on either).

B.3 Controls & audit workflow (no four-eyes maker–checker)

Honest statement: LOANSVC/i does not model a true four-eyes maker–checker / separate-authorization workflow. There is no "one user posts, a second user approves" step in the code: a payment posted through LNINQPG option 1 is applied immediately by LN_POSTPAYMENT, and an origination through LNAPPLY approves-and-disburses in one flow. The manual documents the control model the application does have:

• Audit trail (LNAUD). Every loan insert and every status change is recorded automatically by triggers: TR_LOAN_AI writes an APPLY row on each new loan (customer, principal, status); TR_LOAN_AU writes a STATUS row only when LSTAT actually changes (WHEN clause), e.g. P->A at disbursement or A->D on a missed due date. This is the after-the-fact accountability trail, not an approval gate.
• Transaction journal (LNTXN). Every posted money movement (DISB/ACCR/PMT/LFEE/CHGO) is journaled with a post-transaction balance snapshot and a memo, giving a complete, reconstructable history per loan.
• Status gating. Business rules gate what can happen by loan state: LN_DISBURSE rejects a loan not in PENDING (SQLSTATE 76101); LN_POSTPAYMENT rejects a payment against a loan not active/delinquent (76103) and a non-positive amount (76102).
• Integrity triggers. TR_LOAN_BI vetoes non-positive principal (76201); TR_LOAN_BU vetoes any balance going negative (76202).
• Channel gating via the view. TR_LOANV_IOU (INSTEAD OF UPDATE on LNLOANV) rejects any attempt to edit balances through the maintenance view (76203) — balances may change only through LN_POSTPAYMENT. It does allow a servicer to override a loan's LSTAT through the view, rerouting that change to the base table (which then trips the TR_LOAN_AU audit).

In sum, the control posture is audit + status/state gating + integrity enforcement, all enforced in the data layer, rather than a segregation-of-duties approval workflow.

C. Batch Jobs & the Periodic Cycle ↑ top

LOANSVC/i's servicing runs as periodic cycles rather than one monolithic nightly job: a daily cycle (accrual + due-check), a weekly cycle (delinquency fees), and a monthly cycle (charge-off + close). All three batch programs take no CALL parameters; each reads its processing date — and, for the monthly job, the close year-month — from the single-row LNCTL control table. This is the SBMJOB idiom: a scheduled/JOBQ submission sets the date on the control row, then simply calls the program with no parms.

-- CTL maintenance: set the processing date the next cycle runs for
UPDATE LOANSVC/LNCTL SET ASOFDATE = 20260805 WHERE CTLKEY = 'X';
-- for the monthly job, also set the close month (YYYYMM)
UPDATE LOANSVC/LNCTL SET ASOFDATE = 20260901, CLOSEYM = 202609 WHERE CTLKEY = 'X';

-- then submit the (parameterless) cycle
SBMJOB CMD(CALL PGM(LOANSVC/LNDAILY)) JOB(LNDAILY)

C.1 Full batch program set

Program	Purpose	Procedures / functions called	Inputs	Outputs	Frequency
LNAPPLY	Originate & (if approved) disburse a loan.	LN_APPLY then LN_DISBURSE; indirectly FN_RATETIER/FN_MAXPRIN/FN_ADDDAYS.	Host-variable applications coded in the driver (custno, principal, term, appdate).	DSPLY per application (APPn=loanno/status/sqlcode, DISBn=sqlcode); LNLOAN + LNTXN(DISB) + LNAUD rows.	Ad hoc / on demand.
LNDAILY	Accrue daily interest, then detect missed due dates.	LN_ACCRUEDAILY (→ LN_ACCRUEONE → FN_DAILYINT/FN_ADDDAYS), LN_DUECHECK.	LNCTL.ASOFDATE.	DSPLY ASOF=, ACCR=nloans/totaccr/sqlcode, DUECHK=nmissed/sqlcode; ACCR txns; loans flip to D.	Daily.
LNWEEKLY	Assess delinquency late fees.	LN_WEEKLYFEES.	LNCTL.ASOFDATE (used as week-ending date).	DSPLY WKFEES=nfeed/totfees/sqlcode; LFEE txns; LNWKLOG row; FEEBAL increased.	Weekly.
LNMONTHLY	Charge off 3+cycle delinquents, then close/value the portfolio.	LN_CHARGEOFF, LN_MONTHLYCLOSE.	LNCTL.ASOFDATE and LNCTL.CLOSEYM.	DSPLY CHGOFF=nchgoff/totloss/sqlcode, CLOSE=closeym/sqlcode; CHGO txns; LNCLOSE row.	Monthly (month-end).

C.2 Daily / weekly / monthly detail

Daily — LNDAILY

Reads ASOFDATE from LNCTL, then: (1) LN_ACCRUEDAILY(asof) accrues simple daily interest on every A/D loan from each loan's LASTACCRDT+1 through the as-of date inclusive — a loan that missed processing days catches all of them up in one call, posting one ACCR txn and advancing LASTACCRDT; (2) LN_DUECHECK(asof) bumps MISSEDCNT, rolls NEXTDUEDT +30d and flips to DELINQUENT any A/D loan whose NEXTDUEDT < asof.

Expected DSPLY (day-4 accrual on one 5000.00 @8% loan):
  ASOF=20260805/0
  ACCR=1/4.40/0        1 loan, 4 days x 1.10 = 4.40, SQLCODE 0
  DUECHK=0/0           no missed loans yet

Weekly — LNWEEKLY

Reads the week-ending date from LNCTL.ASOFDATE and calls LN_WEEKLYFEES(wkenddt): for each DELINQUENT loan it adds $25.00 × MISSEDCNT to FEEBAL, posts an LFEE txn, and writes one LNWKLOG row for the week. Active loans are never fee'd.

Expected DSPLY (1 delinquent loan, MISSEDCNT=1):
  WKFEES=1/25.00/0     1 loan fee'd, $25.00 total, SQLCODE 0

Monthly — LNMONTHLY

Reads ASOFDATE and CLOSEYM, then: (1) LN_CHARGEOFF(asof) writes off every delinquent loan with MISSEDCNT ≥ 3 — loss = PRINBAL+ACCRINT+FEEBAL, posts a CHGO txn, sets LSTAT='X' and zeroes balances; (2) LN_MONTHLYCLOSE(closeym) snapshots the portfolio into LNCLOSE: A/D counts, total principal & accrued interest, the principal-weighted average coupon (WAC_BP), and the ALLL reserve (2% of active PRINBAL + 25% of delinquent PRINBAL — a deliberately simple, auditable figure, not a regulatory model). One row per month via DELETE-then-INSERT.

Expected DSPLY (one 3+cycle loan charged off, then close):
  CHGOFF=1/2083.00/0   1 loan written off, 2083.00 loss
  CLOSE=202609/0       valuation snapshot written for 202609

C.3 Ordering & dependencies

• DAILY before WEEKLY. Only the daily LN_DUECHECK flips loans to DELINQUENT and increments MISSEDCNT; weekly fees are $25 × MISSEDCNT, so daily must run first or fees are under-assessed.
• DAILY (and WEEKLY) before the MONTHLY close. Charge-off keys off MISSEDCNT ≥ 3 (built up by daily due-checks), and the valuation must be computed on fully accrued, fully fee'd balances. Run the month's dailies and weeklies, then the monthly close last.
• Within a cycle the order is fixed by the driver: accrual before due-check (LNDAILY); charge-off before close (LNMONTHLY) so the snapshot reflects the write-offs.

D. Data Files (data dictionary) ↑ top

All files are in library LOANSVC, grounded in schema.sql. Dates are stored as INT in YYYYMMDD form (or YYYYMM for close month); rates are INT basis points (500 = 5.00%); money is DECIMAL.

LNCUST — Customer master (PK CUSTNO)

Field	Type	Meaning
CUSTNO	CHAR(6)	Customer number (PK).
CNAME	VARCHAR(40)	Customer name.
CSTAT	CHAR(1)	A active, H hold (no new loans), C closed.
CSCORE	SMALLINT	Credit score 300–850 (drives the rate tier).

LNRATETIER — Score-based rate/limit tiers (PK TIERNO)

Field	Type	Meaning
TIERNO	SMALLINT	Tier number (PK).
MINSCORE	SMALLINT	Lowest score (inclusive) this tier covers; the highest floor ≤ a score wins.
RATEBP	INT	Annual rate in basis points.
MAXPRIN	DECIMAL(11,2)	Max principal approvable at this tier.

Tested tier set: 300→1800bp/2000, 580→1200bp/10000, 680→800bp/25000, 750→500bp/50000.

LNLOAN — Loan master (PK LOANNO; FK CUSTNO→LNCUST)

Field	Type	Meaning
LOANNO	CHAR(8)	Loan number (PK), e.g. LN000001.
CUSTNO	CHAR(6)	Owning customer (FK).
PRINCIPAL	DECIMAL(11,2)	Original principal.
RATEBP	INT	Annual rate, basis points (from the score tier).
TERMMOS	SMALLINT	Term in months (informational).
APPDATE / STARTDATE	INT	Applied date / disbursed date (YYYYMMDD).
LSTAT	CHAR(1)	P pending, A active, D delinquent, F paid-off, X charged-off, R rejected.
PRINBAL	DECIMAL(11,2)	Remaining principal.
ACCRINT	DECIMAL(11,2)	Accrued-unpaid interest bucket.
FEEBAL	DECIMAL(11,2)	Accrued-unpaid late fees.
LASTACCRDT	INT	Interest last accrued through (accrual basis).
NEXTDUEDT	INT	Next scheduled payment due date.
DUEDAY	SMALLINT	Day-of-month due (1–28).
MISSEDCNT	SMALLINT	Consecutive missed due dates (drives fees & charge-off).
PAIDOFFDT / CHGOFFDT	INT	Paid-off / charged-off dates when applicable.

LNTXN — Transaction journal (PK LOANNO, TXNSEQ)

Field	Type	Meaning
LOANNO / TXNSEQ	CHAR(8) / INT	Loan + sequence number (from LNTXNSEQ) (PK).
TXNDATE	INT	Transaction date (YYYYMMDD).
TXNTYPE	CHAR(4)	DISB disbursement, ACCR accrual, PMT  payment, LFEE late fee, CHGO charge-off.
AMT	DECIMAL(11,2)	Signed amount of what the txn represents.
PRINBAL	DECIMAL(11,2)	Principal balance snapshot AFTER this txn.
MEMO	VARCHAR(40)	Free-text detail (e.g. the payment waterfall split).

LNAUD — Audit trail (PK AUDSEQ identity)

Field	Type	Meaning
AUDSEQ	INT identity	Generated-always audit sequence (PK).
AUDTS	TIMESTAMP	When (default CURRENT TIMESTAMP).
LOANNO	CHAR(8)	Loan affected.
OP	VARCHAR(10)	APPLY (insert) or STATUS (LSTAT change).
DETAIL	VARCHAR(80)	Human-readable detail written by the trigger.

LNCLOSE — Monthly valuation snapshot (PK CLOSEYM)

Field	Type	Meaning
CLOSEYM	INT	Close year-month YYYYMM (PK).
NUMACTIVE / NUMDELINQ	INT	Active / delinquent loan counts.
TOTPRINBAL	DECIMAL(13,2)	Total principal over open loans.
TOTACCRINT	DECIMAL(13,2)	Total accrued interest over open loans.
WAC_BP	DECIMAL(9,2)	Principal-weighted average coupon, basis points.
ALLLRESERVE	DECIMAL(13,2)	Loss-reserve estimate.
CLOSEDTS	TIMESTAMP	When the close ran.

LNWKLOG — Weekly fee-run log (PK WKENDDT)

Field	Type	Meaning
WKENDDT	INT	Week-ending date (PK) — the duplicate-run guard.
NUMFEED	INT	Loans fee'd that week.
TOTFEES	DECIMAL(11,2)	Total fees assessed that week.

LNCTL — Batch control (one row, PK CTLKEY='X')

Field	Type	Meaning
CTLKEY	CHAR(1)	Always 'X' (PK, single row).
ASOFDATE	INT	Processing/as-of date read by every batch program.
CLOSEYM	INT	Close year-month read by the monthly program.

Seeded row: ('X', 20260801, 202608). Operators advance it before each cycle.

LNLOANV — Maintenance view (LNLOAN ⨯ LNCUST)

Join view exposing LOANNO, CUSTNO, CNAME, PRINCIPAL, RATEBP, TERMMOS, LSTAT, PRINBAL, ACCRINT, FEEBAL, NEXTDUEDT, MISSEDCNT. Updatable only through TR_LOANV_IOU (INSTEAD OF UPDATE): balance edits are rejected (76203); an LSTAT override reroutes to the base table.

LNTXNSEQ — Sequence

CREATE SEQUENCE LOANSVC/LNTXNSEQ AS INT START WITH 1 NO CYCLE — the global generator every procedure draws TXNSEQ from via NEXT VALUE FOR, so all LNTXN rows share one monotonic sequence.

Relationships

• LNLOAN.CUSTNO → LNCUST.CUSTNO (declared FK).
• LNLOAN.RATEBP is derived at origination from LNRATETIER via the customer's CSCORE.
• LNTXN.LOANNO → LNLOAN.LOANNO (per-loan journal; TXNSEQ from LNTXNSEQ).
• LNAUD.LOANNO → LNLOAN.LOANNO (audit references the loan).
• LNCLOSE and LNWKLOG are aggregate logs (no per-row FK; keyed by period).
• LNCTL is standalone control; LNLOANV joins LNLOAN and LNCUST.

E. Operations Runbook ↑ top

E.1 Day-in-the-life

1. Advance the control row to today: UPDATE LOANSVC/LNCTL SET ASOFDATE = <YYYYMMDD> WHERE CTLKEY='X'.
2. Submit the daily cycle: SBMJOB CMD(CALL PGM(LOANSVC/LNDAILY)).
3. Post-check the daily run (see below).
4. If today is a weekly fee day, submit LNWEEKLY (same ASOFDATE = week-ending date), then check the WKFEES= line and the new LNWKLOG row.
5. Handle interactive payments through CALL LOANSVC/LNINQPG (option 1) as they arrive.
6. Originate new loans on demand with CALL LOANSVC/LNAPPLY (or the procedures directly).

Pre-checks: confirm LNCTL holds the intended date; confirm the job's library list includes LOANSVC.

Post-checks after the daily cycle:

• ASOF= matches the date you set; both CALL lines end in /0 (SQLCODE 0).
• ACCR= loan count & total reconcile to the open portfolio; exactly one ACCR txn per loan per run.
• DUECHK= equals the loans whose NEXTDUEDT passed; those are now LSTAT='D' with MISSEDCNT bumped and NEXTDUEDT rolled +30d.
• No loan outside A/D scope changed (the FOR-loop procedures update only their keyed row — a regression guard the tests verify).

E.2 Month-end close

1. Confirm every business day's daily cycle for the month has run and every weekly fee run is logged in LNWKLOG.
2. Set the control row: UPDATE LOANSVC/LNCTL SET ASOFDATE = <month-end>, CLOSEYM = <YYYYMM> WHERE CTLKEY='X'.
3. Submit LNMONTHLY. Confirm CHGOFF= and CLOSE=<YYYYMM>/0.
4. Review and reconcile the LNCLOSE snapshot:

SELECT NUMACTIVE, NUMDELINQ, TOTPRINBAL, TOTACCRINT, WAC_BP, ALLLRESERVE
  FROM LOANSVC/LNCLOSE WHERE CLOSEYM = <YYYYMM>;

Reconciling figures (the same ones the volume simulation checks against an independent JS oracle):

• Portfolio balance — TOTPRINBAL = SUM(PRINBAL) over open (A+D) loans; TOTACCRINT = SUM(ACCRINT) over the same set.
• Charge-offs — CHGOFF= count/loss equal the loans that were D with MISSEDCNT≥3; those are now X, zero-balance, with a CHGO txn each.
• WAC — WAC_BP = Σ(PRINBAL×RATEBP)/TOTPRINBAL, e.g. (5000×800 + 3000×1200)/8000 = 950.00 bp.
• ALLL reserve — ALLLRESERVE = 2% of active PRINBAL + 25% of delinquent PRINBAL, e.g. 100.00 + 750.00 = 850.00.

Ad-hoc portfolio listing: CALL LOANSVC/LN_PORTFOLIO() returns one row per open loan (loan, customer, status, balances, missed count, delinquency bucket) for a report or STRSQL review.

E.3 Failure & re-run rules

Each RPG driver DSPLYs its result with the trailing SQLCODE. A healthy run ends every line in /0; a negative trailing value means the EXEC SQL CALL failed with a specific SQLSTATE (section F.5).

Situation	Behaviour	Action
Daily cycle fails partway	Un-accrued loans keep their old LASTACCRDT.	Re-submit LNDAILY for the same ASOFDATE: already-accrued loans are a no-op, the rest catch up. Idempotent off the control row.
Re-run same day's accrual	Accrues 0 additional days (LASTACCRDT+1 > ASOF).	Safe no-op. Only advance ASOFDATE to process a new day.
Monthly close fails	No partial LNCLOSE row (INSERT is the last step).	Fix data and re-submit; DELETE-then-INSERT replaces the month cleanly (idempotent per month).
Charge-off re-run	Charged-off loan is X, no longer matches D&MISSEDCNT≥3.	Naturally idempotent for a fixed portfolio — never charged off twice.
Weekly fees re-run same week	Not idempotent by design.	Do not resubmit the same WKENDDT; LNWKLOG PK blocks a duplicate.
SQLSTATE 76204 on accrual	A loan reached A/D with NULL LASTACCRDT (never properly disbursed).	Disburse it via LN_DISBURSE or move it out of A/D scope, then re-run. The guard prevents an infinite accrual loop.
Rejected payment	LN_POSTPAYMENT traps the exception, sets result REJECTED and RESIGNALs.	Non-zero SQLCODE surfaces to the caller; balances are left unchanged. Correct the input and retry.

F. Developer Reference ↑ top

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

F.1 Functions (8)

FN_YMD2ISO (YMD INT) RETURNS CHAR(10)

Formats YYYYMMDD as ISO YYYY-MM-DD (so DATE() can parse it).

FN_ADDDAYS (YMD INT, NDAYS INT) RETURNS INT

Adds N days to a YYYYMMDD date, returning YYYYMMDD (via DAYS()/DATE integer arithmetic).

FN_DAYSBETWEEN (YMD1 INT, YMD2 INT) RETURNS INT

Whole days from YMD1 to YMD2 (positive if YMD2 is later).

FN_RATETIER (SCORE SMALLINT) RETURNS INT

The RATEBP of the highest score-floor tier ≤ the score (correlated subquery on LNRATETIER).

FN_MAXPRIN (SCORE SMALLINT) RETURNS DECIMAL(11,2)

The MAXPRIN of that same tier — the underwriting ceiling.

FN_DAILYINT (PRINBAL DECIMAL(11,2), RATEBP INT) RETURNS DECIMAL(11,2)

One day of simple interest: ROUND(PRINBAL × (RATEBP/100/100) / 365, 2), 365-day convention.

FN_DELQBUCKET (MISSEDCNT SMALLINT) RETURNS VARCHAR(10)

CURRENT / 1-CYCLE / 2-CYCLE / 3+CYCLE from the missed-cycle count.

F.2 Procedures (12)

LN_APPLY (IN CUSTNO, PRINCIPAL, TERMMOS, APPDATE; OUT LOANNO, STATUS)

Underwrites: unknown customer → SQLSTATE 76100; on-hold customer or over-tier-max principal → inserted as REJECTED (LSTAT='R'); otherwise PENDING (LSTAT='P'). Generates the next LNnnnnnn.

LN_DISBURSE (IN LOANNO, STARTDATE)

PENDING→ACTIVE: sets PRINBAL=PRINCIPAL, STARTDATE, DUEDAY (capped 28), NEXTDUEDT +30d, LASTACCRDT=STARTDATE; posts a DISB txn. Non-pending → 76101.

LN_ACCRUEONE (IN LOANNO, ASOF; OUT ACCRUED)

Day-walking WHILE loop accruing FN_DAILYINT from LASTACCRDT+1 through ASOF; posts one ACCR txn and advances LASTACCRDT. Guards NULL LASTACCRDT with 76204.

LN_ACCRUEDAILY (IN ASOF; OUT NLOANS, TOTACCR)

FOR-loop over A/D loans calling LN_ACCRUEONE; returns count and total accrued.

LN_DUECHECK (IN ASOF; OUT NMISSED)

For A/D loans with NEXTDUEDT<ASOF: MISSEDCNT+1, NEXTDUEDT +30d, LSTAT='D'. Keys the UPDATE off a captured local loan number (regression-fixed).

LN_POSTPAYMENT (IN LOANNO, AMT, PAYDATE; OUT RESULT)

Waterfall fees→interest→principal; MISSEDCNT=0, NEXTDUEDT +30d; zero balance → LSTAT='F', PAIDOFFDT set. Guards: 76102 non-positive amount, 76103 not active/delinquent, 76100 not found. EXIT handler sets RESULT='REJECTED' and RESIGNALs.

LN_WEEKLYFEES (IN WKENDDT; OUT NFEED, TOTFEES)

For LSTAT='D' loans: FEEBAL += 25.00×MISSEDCNT, post LFEE txn; write one LNWKLOG row.

LN_CHARGEOFF (IN ASOF; OUT NCHGOFF, TOTLOSS)

For LSTAT='D' & MISSEDCNT≥3: LSTAT='X', CHGOFFDT=ASOF, balances zeroed; post CHGO txn for PRINBAL+ACCRINT+FEEBAL.

LN_MONTHLYCLOSE (IN CLOSEYM)

Computes counts, TOTPRINBAL, TOTACCRINT, WAC_BP, ALLLRESERVE; DELETE-then-INSERT one LNCLOSE row (idempotent per month).

LN_PORTFOLIO () DYNAMIC RESULT SETS 1

WITH RETURN cursor: one row per open loan incl. FN_DELQBUCKET, for reports / STRSQL.

LN_INQUIRE (IN LOANNO; OUT CUSTNO, CNAME, LSTAT, PRINBAL, ACCRINT, FEEBAL, MISSEDCNT, BUCKET)

Single-loan inquiry (OUT parms) for the 5250 detail display; joins LNCUST; 76100 if not found.

F.3 Triggers (5) & the view

TR_LOAN_BI — BEFORE INSERT on LNLOAN

Vetoes non-positive PRINCIPAL (SQLSTATE 76201).

TR_LOAN_AI — AFTER INSERT on LNLOAN

Writes an LNAUD 'APPLY' row (customer, principal, status).

TR_LOAN_AU — AFTER UPDATE OF LSTAT, WHEN (O.LSTAT≠N.LSTAT)

Writes an LNAUD 'STATUS' row only on a real status change (e.g. P→A, A→D).

TR_LOAN_BU — BEFORE UPDATE on LNLOAN

Vetoes PRINBAL/ACCRINT/FEEBAL going negative (76202).

TR_LOANV_IOU — INSTEAD OF UPDATE on LNLOANV

Rejects balance edits through the view (76203); reroutes an LSTAT change to the base table.

F.4 Embedded-SQL patterns (SQLRPGLE)

The RPG programs use these embedded-SQL idioms against the SQL-PL layer:

• SELECT INTO — read the control row into host variables, e.g. exec sql select ASOFDATE into :asof from LOANSVC.LNCTL where CTLKEY='X';
• EXEC SQL CALL — drive a procedure with host-variable IN/OUT parms, e.g. exec sql call LOANSVC.LN_ACCRUEDAILY(:asof, :nloans, :totaccr); then check SQLCODE.
• DECLARE / OPEN / FETCH cursor — the LNINQPG subfile load, selecting only plain columns, looping dow sqlcode = 0.
• VALUES ... INTO — a cursor-free scalar-function call per fetched row: exec sql values FN_DELQBUCKET(:missedcnt) into :bucket;
• SQLCA / SQLCODE / SQLSTATE handling — every driver DSPLYs %char(sqlcode) after each call; the SQL-PL procedures raise application SQLSTATEs (below) via SIGNAL/RESIGNAL, and LN_POSTPAYMENT captures the message with GET DIAGNOSTICS.

F.5 Application SQLSTATE table

SQLSTATE	Raised by	Meaning
76100	LN_APPLY / LN_DISBURSE / LN_POSTPAYMENT / LN_INQUIRE	Customer or loan not found (NOT FOUND handler).
76101	LN_DISBURSE	Loan is not in PENDING status.
76102	LN_POSTPAYMENT	Payment amount must be positive.
76103	LN_POSTPAYMENT	Loan is not active or delinquent.
76201	TR_LOAN_BI	Principal must be positive.
76202	TR_LOAN_BU	A loan balance cannot go negative.
76203	TR_LOANV_IOU	Balances must change via LN_POSTPAYMENT, not the view.
76204	LN_ACCRUEONE	Loan has no LASTACCRDT — must be disbursed via LN_DISBURSE before accrual.

G. Glossary ↑ top

Accrual (interest accrual)

Recognising interest earned each day on the outstanding principal, added to the ACCRINT bucket. Here: simple daily interest, 365-day convention, via FN_DAILYINT.

ALLL — Allowance for Loan & Lease Losses

A reserve estimate for expected credit losses. LOANSVC/i uses a deliberately simple, auditable proxy: 2% of active principal + 25% of delinquent principal (not a regulatory model).

Basis point (bp)

One hundredth of a percent; rates are stored as INT basis points (500 = 5.00%).

Charge-off

Writing a loan off as a loss once it is severely delinquent (here MISSEDCNT≥3): LSTAT→X, balances zeroed, a CHGO transaction posted for the loss.

Control row / control table (LNCTL)

The single-row table holding the processing date (and close month) the parameterless batch programs read, so a bare SBMJOB can drive them.

Delinquency bucket

A classification of how many payment cycles a loan has missed: CURRENT / 1-CYCLE / 2-CYCLE / 3+CYCLE (FN_DELQBUCKET).

Disbursement

Funding an approved loan — moving it PENDING→ACTIVE, setting the balance and first due date, posting a DISB transaction.

Idempotent

Safe to run again with the same result. Daily accrual (off LASTACCRDT) and the monthly close (DELETE-then-INSERT) are idempotent; weekly fees are not (a per-week event).

Payment waterfall

The order a payment is applied: fees first, then accrued interest, then principal (LN_POSTPAYMENT).

SBMJOB

Submit Job — the IBM i command that queues a program to run as a batch job (e.g. SBMJOB CMD(CALL PGM(LOANSVC/LNDAILY))).

SQLRPGLE / EXEC SQL

RPG with embedded SQL. Here the RPG programs orchestrate by CALLing SQL-PL procedures via EXEC SQL CALL and reading data with SELECT INTO / cursors.

SQLCODE / SQLSTATE / SQLCA

The SQL return-status fields. SQLCODE 0 is success; a negative code (with an application SQLSTATE such as 76102) signals a business-rule rejection.

Subfile

A 5250 display construct listing many rows on one screen (DDS SFL/SFLCTL). LNINQPG's portfolio list is a subfile; the operator keys options against its rows.

Tier (rate tier)

A credit-score band (LNRATETIER) setting the annual rate and maximum approvable principal used at origination.

WAC — Weighted-Average Coupon

The principal-weighted average interest rate across the open portfolio: Σ(PRINBAL×RATEBP)/ΣPRINBAL, in basis points.