OCASH/i — Order-to-Cash / Billing / AR-Ledger

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

OCASH/i is an order-to-cash application: sales-order credit approval, invoice generation (billing), cash-receipt application, an append-only AR ledger, and an invoice-aging report. Unlike a traditional IBM i application, OCASH/i has no RPG business logic and no 5250 online screen of its own — every business rule lives in DB2 for i SQL PL: schema, scalar/table functions, stored procedures, and table/view triggers. The application is driven from STRSQL (CALL / ad-hoc SQL), from a batch driver, or through a thin SQLRPGLE caller that only proves the EXEC SQL CALL reach path. This manual is the reference for the operator who runs the daily order-to-cash cycle and for the developer maintaining the SQL-PL layer. It is grounded entirely in the committed source (sqlpl-app-oc/src/schema.sql, routines.sql, src/OCCALLRPG.rpgle.txt, and the test/oc_daily.mjs / test/oc_sim_daily_volume.mjs drivers).

A. Overview & Architecture ↑ top

A.1 What it does

OCASH/i runs the full order-to-cash lifecycle for a wholesale/distribution AR ledger:

Order entry & discounting — a sales-order header (OCORDH) with lines (OCORDL); each line's tiered quantity discount and extended amount are computed automatically by a BEFORE INSERT trigger calling the discount functions.
Credit approval — OC_APPROVE sweeps every entered order, sums its lines, rejects orders for on-hold customers or those that would breach the customer's credit limit, and approves the rest (OSTAT E→A or E→R).
Billing — OC_BILLRUN bills every approved order, generating an invoice header + lines (OCINVH/OCINVL), computing the due date from the customer's payment terms, posting a positive AR-ledger entry, and raising the customer's balance and YTD.
Cash application — OC_APPLYCASH applies a cash receipt to its invoice through a full/partial/over-payment path, posting a negative AR-ledger entry and lowering the customer's balance; OC_CASHRUN is the batch driver over all entered receipts.
Aging — OC_AGING returns, as a dynamic result set, every still-open invoice classified into an aging bucket (CURRENT / 1-30 / 31-60 / 61-90 / 90+) as of a given date.
Credit-limit maintenance — OC_ADJCREDIT is the one sanctioned way to change a credit limit (clamped to [0, 1,000,000]), reachable directly or through the OCCUSTV view's INSTEAD OF trigger.

A.2 SQL PL owns everything (there is no RPG business layer)

OCASH/i is a deliberately pure SQL PL application. The design boundary is not "SQL PL for logic, RPG for orchestration" (as in LOANSVC/i) — here all of the application is SQL PL:

SQL PL layer (the entire application). Every rule — the tiered discount, line-amount math, credit approval, billing, the due-date calendar math, cash application with over-payment rejection, the aging buckets, and the credit-limit clamp — is implemented as DB2 for i functions, stored procedures, and table/view triggers in routines.sql, over the schema in schema.sql. Integrity and audit are enforced by triggers (positive quantity, non-negative balance, audit rows, and the view's INSTEAD OF reroute).
SQLRPGLE caller (proof only, not logic). OCCALLRPG is a single thin program that exists purely to prove the reach path: it issues EXEC SQL CALL against OC_APPROVE, OC_BILLRUN, OC_APPLYCASH, and OC_ADJCREDIT and DSPLYs each result + SQLCODE. It computes no order-to-cash math of its own. In production the same procedures are called from STRSQL or a scheduler; the RPG program is not required to run the application.

The benefit for operations: the rules live in one auditable place (the SQL-PL routines), and the exact same procedures are reachable from a batch driver, from ad-hoc STRSQL, and from the SQLRPGLE caller alike. Everything runs in library OCASH.

A.3 Component & flow

  ENTRY                  APPROVE              BILL                 CASH
  -----                  -------              ----                 ----
  INSERT OCORDH ---+     OC_APPROVE  ---+     OC_BILLRUN  ---+     OC_CASHRUN ---+
  INSERT OCORDL    |      (WHILE cursor)|      (FOR loop)    |      (WHILE cursor)|
   TR_ORDL_BI      |       FN_LINEAMT   |       CALL OC_BILL |       CALL OC_APPLYCASH
    FN_TIERDISC    |       FN_TIERDISC  |        FN_DUEDATE   |        SIGNAL/RESIGNAL
    FN_LINEAMT     |       E->A / E->R  |        A->B         |        handlers
   TR_ORDH_AI (aud)|                    |        OCINVH/OCINVL|        OCINVH IPAID
                   v                    v        OCLEDG (+)   v        OCLEDG (-)
                OCORDH/OCORDL  <--- OCCUST.CBAL/CYTD +  |  <--- OCCUST.CBAL -
                                                        |
   AGING: OC_AGING(asof) --WITH RETURN--> open invoices x FN_AGEBUCKET
   MAINT: OC_ADJCREDIT (clamp) <--INSTEAD OF-- UPDATE OCCUSTV
   triggers: TR_ORDL_BI / TR_ORDH_AI / TR_INVH_AU / TR_CUST_BU (OCCUST)
             TR_CUSTV_IOU (OCCUSTV view)      audit -> OCAUD      ledger -> OCLEDG

A single order flows: INSERT the header and lines (the TR_ORDL_BI trigger computes each line's discount and amount, TR_ORDH_AI writes an audit row) → OC_APPROVE credit-checks it (E→A) → OC_BILLRUN bills it, creating an invoice, a positive OCLEDG row, and raising OCCUST.CBAL → a cash receipt is applied by OC_APPLYCASH, creating a negative OCLEDG row and lowering OCCUST.CBAL. The AR ledger is the append-only reconciliation trail: for customers that start at zero balance, CBAL == SUM(OCLEDG.LAMT) at all times.

A.4 Object inventory

Object	Type	Role
OCCUST	PF	Customer master: credit limit, running AR balance, terms.
OCORDH	PF	Sales-order header.
OCORDL	PF	Sales-order line (qty, price, discount, amount).
OCINVH	PF	Invoice header (from a billed order).
OCINVL	PF	Invoice line (mirrors the order line at billing).
OCPAY	PF	Cash receipts, applied against an invoice.
OCLEDG	PF	Append-only AR ledger (signed balance-movement trail).
OCAUD	PF	Generic trigger audit log.
OCTIER	PF	Quantity-breakpoint discount tiers.
OCCUSTV	View	OCCUST view with an INSTEAD OF UPDATE reroute.
FN_* (5)	SQL functions	ISO date, tier discount, line amount, due date, age bucket.
OC_* (7)	SQL procedures	Approve/bill/bill-run/apply-cash/cash-run/adjust-credit/aging.
TR_* (5)	Triggers	Line default+veto, audit, status-audit, balance veto, view reroute.
OCCALLRPG	SQLRPGLE	Reach-path proof caller (no business logic).

The full catalogue is 5 functions + 7 procedures + 5 triggers, over 9 PFs and 1 view, seeded with a 4-row discount tier, plus 1 SQLRPGLE caller. Sections D and F expand each object.

B. Online Access & Controls ↑ top

B.1 How it is invoked (there is no 5250 DSPF screen)

Honest statement: OCASH/i has no interactive 5250 display file and no subfile inquiry/maintenance program. It is a pure data-and-logic application: the operator drives it by CALLing the SQL-PL procedures. There are three equivalent access routes, and all three reach the identical procedures in library OCASH:

STRSQL (interactive SQL). The everyday route. Start an interactive SQL session and CALL a procedure, or run ad-hoc SELECT/INSERT/UPDATE. The job's library list must include OCASH — the tested jobs run with LIBL = QSYS QGPL OCASH QTEMP and CURLIB = OCASH.
Batch driver (embedded SQL over a scripted cycle). The test/oc_daily.mjs and test/oc_sim_daily_volume.mjs drivers run the full order-to-cash cycle as an executeSqlBatch / callProc sequence — the operational template for a scheduled nightly run.
SQLRPGLE caller (OCCALLRPG). A thin program proving host-code reach: CALL OCASH/OCCALLRPG issues EXEC SQL CALL against the same procedures. It is a reach-path proof, not the operator's normal entry point.

To do this	Type in STRSQL (or a CALL command)
Credit-approve all entered orders	`CALL OCASH/OC_APPROVE(?, ?)`
Bill all approved orders	`CALL OCASH/OC_BILLRUN(?)`
Bill one order by hand	`CALL OCASH/OC_BILL('O0000001', ?, ?)`
Apply one cash receipt	`CALL OCASH/OC_APPLYCASH('PY000001', ?)`
Apply all entered cash receipts (batch)	`CALL OCASH/OC_CASHRUN(?, ?)`
Adjust a customer's credit limit (clamped)	`CALL OCASH/OC_ADJCREDIT('C00001', 25000.00)`
Run the invoice-aging report	`CALL OCASH/OC_AGING(20260901)`
Run the whole reach-path proof from RPG	`CALL OCASH/OCCALLRPG`

Procedures with OUT parameters are called with a placeholder marker per OUT (shown as ? above); the result set from OC_AGING is returned via a WITH RETURN cursor. Unlike LOANSVC/i, there is no control table — every procedure takes its inputs as explicit CALL parameters (an as-of date for aging, a customer and amount for a credit adjustment, and so on), so a scheduled submission passes the values directly rather than reading them from a control row.

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

Honest statement: OCASH/i does not model a true four-eyes maker–checker / separate-authorization workflow. There is no "one user enters, a second user approves" step in the code: OC_APPROVE approves or rejects in one pass, and cash is applied immediately by OC_APPLYCASH. The manual documents the control model the application does have — all of it enforced in the SQL-PL/data layer:

Audit trail (OCAUD). Trigger-written accountability rows: TR_ORDH_AI writes an INSERT row for each new order header (with the customer); TR_INVH_AU writes a STATUS row only when an invoice's ISTAT actually changes (WHEN clause), e.g. O->P when an invoice is fully paid; OC_ADJCREDIT writes an ADJCREDIT row on every credit-limit change (including one rerouted from the view). This is after-the-fact accountability, not an approval gate.
AR ledger (OCLEDG). Every balance-affecting event is journaled append-only with a signed amount: billing posts a positive 'B' row, cash a negative 'C' row, and a credit-limit change an informational 'A' row (amount 0). For a customer starting at zero balance, CBAL == SUM(OCLEDG.LAMT) — the reconciliation invariant the volume simulation checks per customer across ten days.
Status gating. Business rules gate what can happen by document state: OC_APPROVE only considers OSTAT='E' orders (so a re-run is a no-op); OC_BILL only bills approved orders and flips them to 'B' (so re-billing is a no-op); OC_APPLYCASH only applies against an ISTAT='O' (open) invoice and a PSTAT='E' (entered) receipt, rejecting anything else.
Business-rule vetoes (SIGNAL). OC_APPLYCASH rejects an over-payment (SQLSTATE 75102) and a non-open invoice (75101); TR_ORDL_BI vetoes a non-positive order line quantity (75201); TR_CUST_BU vetoes a customer balance going negative (75202).
Channel gating via the view. TR_CUSTV_IOU (INSTEAD OF UPDATE on OCCUSTV) reroutes any credit-limit edit through OC_ADJCREDIT, so the [0, 1,000,000] clamp always applies — a limit can never be written to the base table unclamped through the view. Other columns (e.g. CSTAT) in the same UPDATE pass straight through to the base table.

In sum, the control posture is audit + append-only ledger + status/state gating + SIGNAL vetoes + a view-level reroute, all enforced in the data layer, rather than a segregation-of-duties approval workflow.

C. Batch & the Order-to-Cash Cycle ↑ top

OCASH/i's processing is a repeatable daily order-to-cash cycle rather than a single monolithic job: enter orders → approve → bill → apply cash → age. Each stage is one SQL-PL procedure call, and every stage is idempotent off document status — approve/bill/cash-apply only touch rows in their entry state and flip them, so a re-run of a stage processes nothing new. There is no control table: procedures take their inputs (e.g. the aging as-of date) as explicit CALL parameters. Order lines get their discount and amount at insert time (the TR_ORDL_BI BEFORE-INSERT trigger), so even a raw INSERT—not just OC_APPROVE's UPDATE—yields a computed line amount.

-- the daily order-to-cash cycle, as a scripted STRSQL / batch sequence
CALL OCASH/OC_APPROVE(?, ?);        -- credit-approve entered orders  (OUT nappr, nrej)
CALL OCASH/OC_BILLRUN(?);           -- bill approved orders           (OUT nbilled)
CALL OCASH/OC_CASHRUN(?, ?);        -- apply entered cash receipts    (OUT napplied, nrejected)
CALL OCASH/OC_AGING(20260901);      -- aging result set as-of a date  (WITH RETURN)

C.1 Full procedure / trigger cycle

Stage	Procedure / trigger	What it does	Inputs	Outputs / effects
Line default	TR_ORDL_BI	BEFORE INSERT on OCORDL: veto qty≤0 (75201), set DISCPCT/LAMT from the functions.	NEW order-line row.	DISCPCT = FN_TIERDISC(QTY); LAMT = FN_LINEAMT(QTY,PRICE).
Order audit	TR_ORDH_AI	AFTER INSERT on OCORDH: write an audit row.	NEW order-header row.	OCAUD 'INSERT' row (customer).
Approve	OC_APPROVE	WHILE-cursor over OSTAT='E' orders; sum lines (FN_LINEAMT), persist DISCPCT/LAMT, credit-check, set E→A or E→R.	(all entered orders).	OUT NAPPR, NREJ; OCORDH.OSTAT/OTOT updated.
Bill (one)	OC_BILL	Bill ONE order: create OCINVH/OCINVL, compute due date (FN_DUEDATE), post +OCLEDG, raise CBAL/CYTD, flip order to 'B'.	IN P_ORDNO.	OUT P_INVNO, P_TOTAL; invoice + ledger + balance.
Bill (run)	OC_BILLRUN	FOR-loop over OSTAT='A' orders, nested CALL OC_BILL per row.	(all approved orders).	OUT NBILLED.
Invoice audit	TR_INVH_AU	AFTER UPDATE OF ISTAT on OCINVH, WHEN status changes: audit row.	OLD/NEW invoice ISTAT.	OCAUD 'STATUS' row (e.g. O->P).
Apply cash (one)	OC_APPLYCASH	Apply ONE receipt: validate open invoice, reject over-payment (SIGNAL 75102), update IPAID/ISTAT, post -OCLEDG, lower CBAL. EXIT handler marks PSTAT='X' + RESIGNAL on any error.	IN P_PAYNO.	OUT P_RESULT ('APPLIED'/'REJECTED').
Apply cash (run)	OC_CASHRUN	WHILE-cursor over PSTAT='E' receipts, nested CALL OC_APPLYCASH; CONTINUE handler absorbs a bad row so the run does not abort.	(all entered receipts).	OUT NAPPLIED, NREJECTED.
Balance veto	TR_CUST_BU	BEFORE UPDATE on OCCUST: veto CBAL<0 (75202).	NEW OCCUST row.	Rejects the update all-or-nothing.
Age	OC_AGING	WITH RETURN cursor: each ISTAT='O' invoice, days-past-due, FN_AGEBUCKET.	IN P_ASOF (YYYYMMDD).	Dynamic result set (5 columns).
Credit adjust	OC_ADJCREDIT	Clamp new limit to [0,1000000], UPDATE OCCUST.CLIMIT, write OCAUD + informational OCLEDG 'A'.	IN P_CUSTNO, P_NEWLIMIT.	Clamped CLIMIT; audit row.
Credit reroute	TR_CUSTV_IOU	INSTEAD OF UPDATE on OCCUSTV: reroute a CLIMIT change through OC_ADJCREDIT (clamp); apply CSTAT directly.	OLD/NEW view row.	Clamped limit change; no unclamped write.

Worked example (from the daily driver's hand-derived oracle)

Four seeded orders on 2026-08-01, tier table 0→0% / 10→5% / 50→10% / 100→15%:

APPROVE:
  O0000001 C00001  L1 qty20@10.00 (5%)=190.00  L2 qty5@50.00 (0%)=250.00  tot 440.00  -> APPROVE (0+440<=5000)
  O0000002 C00002  L1 qty60@12.00 (10%)=648.00                            tot 648.00  -> APPROVE (0+648<=1000)
  O0000003 C00003  L1 qty5@20.00 (0%)=100.00      customer CSTAT='H'                  -> REJECT (on hold)
  O0000004 C00001  L1 qty200@100.00 (15%)=17000.00                        tot 17000   -> REJECT (0+17000>5000)
  => OC_APPROVE OUT: NAPPR=2, NREJ=2

BILL (OC_BILLRUN over the 2 approved, ORDNO order):
  O0000001 -> IN000001  total 440.00  due 20260801 +30d = 20260831   C00001 CBAL 0->440, CYTD 440
  O0000002 -> IN000002  total 648.00  due 20260801 +15d = 20260816   C00002 CBAL 0->648, CYTD 648
  OCLEDG 'B' rows: +440 (IN000001), +648 (IN000002)

CASH:
  PY000001 IN000001 440.00 (full)    -> APPLIED   IN000001 ISTAT O->P   C00001 CBAL 440-440=0
  PY000002 IN000002 300.00 (partial) -> APPLIED   IN000002 stays O, IPAID=300  C00002 CBAL 648-300=348
  PY000003 IN000002 500.00           -> 300+500=800 > 648  -> REJECTED (SIGNAL 75102), PSTAT='X', bal unchanged

AGING as-of 20260901:
  IN000001 is 'P' (paid) -> excluded
  IN000002 due 20260816, 16 days past due -> bucket '1-30'

C.2 Ordering & dependencies

Insert before APPROVE. Order lines must be inserted first (so TR_ORDL_BI has set DISCPCT/LAMT); OC_APPROVE then sums those line amounts to set the order total and make the credit decision.
APPROVE before BILL. OC_BILLRUN only bills OSTAT='A' orders, which only OC_APPROVE produces. Billing raises CBAL; credit checks read CBAL — so bill in cycle order, never before approving.
BILL before CASH. OC_APPLYCASH applies against an open invoice (ISTAT='O'), which only billing creates.
AGING last (read-only). OC_AGING reports whatever open invoices exist as of its as-of date; run it after billing/cash to see the current picture. It changes nothing.
Within OC_BILL the order is fixed: create the invoice, then post the ledger row and raise the balance, then flip the order to billed — the nested-CALL/FOR-loop shape guarantees exactly one invoice per approved order.

D. Data Files (data dictionary) ↑ top

All files are in library OCASH, grounded in schema.sql. Dates are stored as INT in YYYYMMDD form; money is DECIMAL; discount percentages are DECIMAL(5,2) (10.00 = 10%); ledger amounts are signed (+ increases AR, - decreases AR).

OCCUST — Customer master (PK CUSTNO)

Field	Type	Meaning
CUSTNO	CHAR(6)	Customer number (PK).
CNAME	VARCHAR(30)	Customer name.
CTERMS	INT	Payment terms, net days (drives the invoice due date).
CLIMIT	DECIMAL(11,2)	Credit limit (only OC_ADJCREDIT clamps it via the view).
CBAL	DECIMAL(11,2)	Current open (unpaid) AR balance.
CYTD	DECIMAL(11,2)	Year-to-date billed.
CSTAT	CHAR(1)	A active, H credit hold (no new orders approved).

OCORDH — Sales-order header (PK ORDNO)

Field	Type	Meaning
ORDNO	CHAR(8)	Order number (PK).
CUSTNO	CHAR(6)	Owning customer.
ODATE	INT	Order date (YYYYMMDD).
OSTAT	CHAR(1)	E entered, A approved, R rejected, B billed.
OTOT	DECIMAL(11,2)	Order total (set by OC_APPROVE from the summed lines).

OCORDL — Sales-order line (PK ORDNO, OLINE)

Field	Type	Meaning
ORDNO / OLINE	CHAR(8) / INT	Order + line number (PK).
ITEMNO	CHAR(8)	Item number.
QTY	INT	Quantity (TR_ORDL_BI vetoes ≤0).
PRICE	DECIMAL(9,4)	List unit price.
DISCPCT	DECIMAL(5,2)	Tiered discount % applied (from FN_TIERDISC, set by the trigger).
LAMT	DECIMAL(11,2)	Extended, post-discount amount (from FN_LINEAMT).

OCINVH — Invoice header (PK INVNO)

Field	Type	Meaning
INVNO	CHAR(8)	Invoice number (PK), e.g. `IN000001`.
ORDNO	CHAR(8)	Source order.
CUSTNO	CHAR(6)	Billed customer.
IDATE	INT	Invoice date (= order date at billing).
DUEDT	INT	Due date = FN_DUEDATE(order date, customer terms).
ITOT	DECIMAL(11,2)	Invoice total.
IPAID	DECIMAL(11,2)	Cumulative amount paid so far.
ISTAT	CHAR(1)	O open, P paid, V void.

OCINVL — Invoice line (PK INVNO, ILINE)

Field	Type	Meaning
INVNO / ILINE	CHAR(8) / INT	Invoice + line number (PK); mirrors the order line at billing.
ITEMNO / QTY / PRICE	CHAR(8) / INT / DECIMAL(9,4)	Copied from the order line.
LAMT	DECIMAL(11,2)	Extended, post-discount line amount (copied).

OCPAY — Cash receipts (PK PAYNO)

Field	Type	Meaning
PAYNO	CHAR(8)	Payment number (PK).
INVNO	CHAR(8)	Invoice the receipt is applied against.
PDATE	INT	Receipt date (YYYYMMDD).
PAMT	DECIMAL(11,2)	Receipt amount.
PSTAT	CHAR(1)	E entered, A applied, X rejected (set by the EXIT handler on failure).

OCLEDG — AR ledger, append-only (PK LSEQ)

Field	Type	Meaning
LSEQ	INT	Caller-assigned ordering/uniqueness key (PK). Billing rows use 10,000,000+seq; cash rows 20,000,001+.
LTYPE	CHAR(1)	B billed, C cash, A adjustment (informational).
LREF	CHAR(8)	Reference document (invoice or payment number).
CUSTNO	CHAR(6)	Customer whose balance moved.
LAMT	DECIMAL(11,2)	Signed amount: + increases AR (billing), - decreases AR (cash), 0 (adjustment).
LDATE	INT	Event date (YYYYMMDD).

OCAUD — Generic trigger audit log (ASEQ identity)

Field	Type	Meaning
ASEQ	INT identity	Generated-always audit sequence.
OP	CHAR(10)	INSERT / STATUS / ADJCREDIT.
TNAME	CHAR(10)	Table affected (e.g. OCORDH, OCINVH, OCCUST).
KEYVAL	CHAR(10)	Key of the affected row.
DETAIL	VARCHAR(60)	Human-readable detail written by the trigger/procedure.

OCTIER — Discount tiers (PK MINQTY)

Field	Type	Meaning
MINQTY	INT	Quantity breakpoint (inclusive floor); the highest MINQTY ≤ qty wins (PK).
DISCPCT	DECIMAL(5,2)	Discount % for that tier.

Seeded tiers: 0→0.00%, 10→5.00%, 50→10.00%, 100→15.00%.

OCCUSTV — Customer view (over OCCUST)

View SELECT CUSTNO, CNAME, CLIMIT, CBAL, CSTAT FROM OCCUST. Direct UPDATEs are intercepted by TR_CUSTV_IOU (INSTEAD OF UPDATE): a CLIMIT change is rerouted through OC_ADJCREDIT (so the [0, 1,000,000] clamp always applies), and a CSTAT change is applied directly to the base table. This is the sanctioned path for adjusting a credit limit.

Relationships

OCORDH.CUSTNO / OCINVH.CUSTNO / OCPAY(→OCINVH)→CUSTNO / OCLEDG.CUSTNO all reference a customer in OCCUST (by convention; keys are application-managed).
OCORDL.ORDNO → OCORDH.ORDNO (order lines); OCINVL.INVNO → OCINVH.INVNO (invoice lines).
OCINVH.ORDNO → OCORDH.ORDNO (an invoice is generated from one order); OCPAY.INVNO → OCINVH.INVNO.
OCLEDG is the append-only movement trail; for a zero-start customer, CBAL == SUM(OCLEDG.LAMT).
OCTIER drives FN_TIERDISC; OCAUD is the generic audit log; OCCUSTV is the maintenance view over OCCUST.

E. Operations Runbook ↑ top

E.1 Day-in-the-life cycle

Pre-checks: confirm the job's library list includes OCASH (LIBL = QSYS QGPL OCASH QTEMP, CURLIB = OCASH).

Enter orders. INSERT the day's order headers (OSTAT='E') and their lines. The TR_ORDL_BI trigger computes each line's DISCPCT/LAMT; TR_ORDH_AI writes an audit row per header.
Approve. CALL OCASH/OC_APPROVE(?, ?). Read back NAPPR + NREJ; they must sum to the number of newly entered orders.
Bill. CALL OCASH/OC_BILLRUN(?). NBILLED must equal NAPPR. Each approved order becomes exactly one invoice.
Enter & apply cash. INSERT the day's receipts (PSTAT='E'), then CALL OCASH/OC_CASHRUN(?, ?). Read back NAPPLIED + NREJECTED; rejected receipts are marked PSTAT='X' and leave balances untouched.
Age (read-only). CALL OCASH/OC_AGING(<asof YYYYMMDD>) to see every open invoice by bucket. Nothing is changed.
Credit maintenance on demand. Adjust a limit via the view (UPDATE OCASH/OCCUSTV SET CLIMIT = <n> WHERE CUSTNO = '...') or directly (CALL OCASH/OC_ADJCREDIT('C00001', <n>)) — both clamp to [0, 1,000,000].

Post-checks:

OC_APPROVE: NAPPR+NREJ == new orders; a same-day re-run returns 0/0 (status guard).
OC_BILLRUN: NBILLED == NAPPR; a re-run returns 0 and does not double any balance.
OC_CASHRUN: NAPPLIED + NREJECTED == entered receipts; a re-run returns 0/0.
Every new invoice's DUEDT == FN_DUEDATE(order date, customer terms).
Per customer, the balance rose by exactly the sum of that day's billed invoice totals.

E.2 Reconciling figures

The same invariants the volume simulation checks against an independent JS oracle:

AR reconciliation (the master invariant). For a customer that started at zero balance, CBAL = SUM(OCLEDG.LAMT) — billing (+) and cash (-) rows net to the current balance. Run SELECT CUSTNO, SUM(LAMT) FROM OCASH/OCLEDG GROUP BY CUSTNO and compare to OCCUST.CBAL. A seeded pre-existing balance (with no ledger row) is the only expected difference.
Billing balance rise. Per customer, the CBAL / CYTD increase equals SUM(OTOT) over that customer's orders billed this cycle (OSTAT='B').
Discount / line amount. Each line's LAMT = ROUND(QTY×PRICE×(1 - DISCPCT/100), 2) with DISCPCT = FN_TIERDISC(QTY) (highest MINQTY≤QTY).
Due dates. DUEDT = the invoice date advanced by the customer's CTERMS net days (calendar math, leap-year aware).
Cash application. An invoice's IPAID equals the sum of its applied receipts; ISTAT flips O→P only when IPAID = ITOT exactly; any receipt that would push IPAID > ITOT is rejected (75102) and marked PSTAT='X'.

-- aging review as-of a date (dynamic result set)
CALL OCASH/OC_AGING(20260901);

-- AR reconciliation spot-check
SELECT C.CUSTNO, C.CBAL, COALESCE(SUM(L.LAMT),0) AS LEDGER
  FROM OCASH/OCCUST C LEFT JOIN OCASH/OCLEDG L ON L.CUSTNO = C.CUSTNO
  GROUP BY C.CUSTNO, C.CBAL ORDER BY C.CUSTNO;

E.3 Failure & re-run rules

Each procedure signals a specific SQLSTATE on a business-rule rejection (section F.6); a caller checks SQLCODE after the CALL. Every stage is idempotent off document status, so a re-run after a partial failure is safe.

Situation	Behaviour	Action
Approve run fails partway	Un-decided orders keep OSTAT='E'.	Re-run `OC_APPROVE`: already-decided (A/R) orders are skipped, the rest are processed. Idempotent off OSTAT.
Re-run approve with nothing entered	No OSTAT='E' rows.	Returns NAPPR=0/NREJ=0. Safe no-op.
Bill run fails partway	Un-billed approved orders stay OSTAT='A'.	Re-run `OC_BILLRUN`: billed orders are 'B' (skipped), the rest bill. No balance is doubled.
Over-payment (75102)	OC_APPLYCASH's EXIT handler marks PSTAT='X', RESIGNALs; balance/IPAID untouched.	Correct the amount, seed a fresh receipt, re-run. The rejected row stays 'X'.
Non-open invoice (75101)	Same EXIT-handler path: PSTAT='X', RESIGNAL.	Only apply cash to ISTAT='O' invoices.
No such entered receipt (NOT FOUND)	The NOT FOUND handler SIGNALs 75100, EXIT handler marks X + RESIGNAL (e.g. re-applying an already-applied PAYNO surfaces SQLCODE -438 to a caller).	Expected when a receipt is re-applied; only PSTAT='E' rows are applicable.
Bad row inside OC_CASHRUN	The run's CONTINUE handler absorbs the per-row SIGNAL and increments NREJECTED; the loop does not abort.	Nothing — one bad receipt never stops the batch. Review the X-marked rows afterward.
Direct UPDATE drives CBAL negative	TR_CUST_BU vetoes it (75202), all-or-nothing.	Never write a negative balance; apply cash through OC_APPLYCASH.
qty≤0 order line	TR_ORDL_BI vetoes it (75201) on INSERT.	Correct the quantity; the veto holds at any volume.

Because every balance movement is journaled to OCLEDG (signed) and every insert / status change / credit adjustment to OCAUD, any cycle's effect is fully reconstructable after the fact for reconciliation and recovery.

F. Developer Reference ↑ top

The complete SQL-PL surface, from schema.sql and routines.sql. All objects are in library OCASH. This is the fullest section of the manual, since OCASH/i is its SQL PL: every signature and behavior below is the application.

F.1 Tables, view & the discount tier

Nine base tables + one view; see section D for the field-level data dictionary. Keys of note:

Object	Key	Notes
OCCUST	PK CUSTNO	CBAL is mutated by OC_BILL (+) and OC_APPLYCASH (-); TR_CUST_BU floors it at 0.
OCORDH / OCORDL	PK ORDNO / (ORDNO,OLINE)	Lines default DISCPCT/LAMT via TR_ORDL_BI on insert.
OCINVH / OCINVL	PK INVNO / (INVNO,ILINE)	Created by OC_BILL; INVNO = 'IN' + zero-padded MAX(seq)+1.
OCPAY	PK PAYNO	PSTAT E→A on apply, →X on any rejection (EXIT handler).
OCLEDG	PK LSEQ	Caller-assigned LSEQ ranges keep billing (10M+) and cash (20M+) rows apart.
OCAUD	ASEQ identity	GENERATED ALWAYS AS IDENTITY; written by three triggers/procs.
OCTIER	PK MINQTY	Seeded 0/10/50/100 → 0/5/10/15%.
OCCUSTV	view	INSTEAD OF UPDATE (TR_CUSTV_IOU) reroutes CLIMIT through OC_ADJCREDIT.

F.2 Functions (5)

FN_YMD2ISO (YMD INT) RETURNS CHAR(10): RETURN-expression scalar. Formats a YYYYMMDD integer as ISO YYYY-MM-DD via SUBSTR/concat, so DATE()/DAYS() can parse it. Every date-int in the app routes through here before it reaches a DB2 date scalar (the engine's DATE() only parses ISO strings, not a bare 8-digit integer — a documented platform gap).
FN_TIERDISC (QTY INT) RETURNS DECIMAL(5,2): Compound body (BEGIN...END), cursor-free: SELECT DISCPCT INTO D where MINQTY = the MAX(MINQTY) ≤ QTY (correlated subquery). Returns the discount % for a quantity — highest breakpoint wins.
FN_LINEAMT (QTY INT, PRICE DECIMAL(9,4)) RETURNS DECIMAL(11,2): Compound scalar: GROSS = QTY×PRICE; PCT = FN_TIERDISC(QTY); NET = GROSS×(1 - PCT/100.0); RETURN ROUND(NET, 2). The 100.0 (not 100) divisor is a deliberate workaround for a confirmed engine integer-division defect (SQLPL-PLAT-01, now fixed) — the source keeps the explicit-decimal form; the volume sim proves the naive /100 form now agrees too.
FN_DUEDATE (ADATE INT, TERMDAYS INT) RETURNS INT: RETURN-expression scalar (no BEGIN...END): adds TERMDAYS to a YYYYMMDD date and returns YYYYMMDD. Implemented as DECIMAL(REPLACE(CHAR(DATE(DAYS(DATE(FN_YMD2ISO(ADATE))) + TERMDAYS)),'-',''),8,0) — going through DAYS() (date→integer) and back through DATE(n) (integer→date) as plain integer arithmetic. This avoids a confirmed platform defect in the labeled-duration + n DAYS operator (SQLPL-PLAT-03, now fixed) on any non-literal date expression.
FN_AGEBUCKET (DAYSPASTDUE INT) RETURNS VARCHAR(10): Compound body, pure IF/ELSEIF control flow: ≤0 → CURRENT, ≤30 → 1-30, ≤60 → 31-60, ≤90 → 61-90, else 90+. Boundaries are inclusive on the upper edge (30→1-30, 60→31-60, 90→61-90), as the daily driver's boundary oracle verifies.

F.3 Procedures (7)

OC_APPROVE (OUT NAPPR INT, OUT NREJ INT): WHILE-driven cursor loop over OSTAT='E' orders joined to their customer, in ORDNO order. Per order: SUM(FN_LINEAMT(QTY,PRICE)) into V_SUM; persist DISCPCT/LAMT back to the lines via an UPDATE (proving the function inside a SET expression); then if the customer is on hold (CSTAT='H') or CBAL+V_SUM > CLIMIT, set OSTAT='R', else 'A' (and OTOT=V_SUM either way). Counts into the OUT params. Idempotent: only entered orders are considered.
OC_BILL (IN P_ORDNO CHAR(8), OUT P_INVNO CHAR(8), OUT P_TOTAL DECIMAL(11,2)): Bills ONE order (the nested-CALL target). Reads the order's customer/date and the customer's terms; generates the next invoice number as 'IN' || RIGHT('000000'||CHAR(MAX(seq)+1), 6); computes DUEDT = FN_DUEDATE(ODATE, terms); inserts OCINVH + copies OCINVL from OCORDL; flips the order to OSTAT='B'; posts a positive OCLEDG 'B' row (LSEQ = 10,000,000+seq); raises CBAL and CYTD by the total.
OC_BILLRUN (OUT NBILLED INT): Query-driven FOR-loop over OSTAT='A' orders, ORDNO order, with a nested CALL OC_BILL per row; accumulates NBILLED. Idempotent because OC_BILL flips each order to 'B' (so a re-run sees no 'A' rows). Proves FOR-loop + nested CALL + accumulation together.
OC_APPLYCASH (IN P_PAYNO CHAR(8), OUT P_RESULT VARCHAR(10)): Multi-step transaction with handlers. Selects the entered receipt (PSTAT='E') and its invoice; rejects a non-open invoice (SIGNAL 75101) and an over-payment (SIGNAL 75102, when IPAID+PAMT>ITOT); updates IPAID and flips ISTAT to 'P' when fully paid; marks the receipt 'A'; posts a negative OCLEDG 'C' row; lowers CBAL; sets P_RESULT='APPLIED'. A dedicated NOT FOUND handler turns a missing entered receipt into a real SIGNAL (75100); the EXIT HANDLER FOR SQLEXCEPTION captures the message (GET DIAGNOSTICS), marks the receipt PSTAT='X', sets P_RESULT='REJECTED', and RESIGNALs — so every failure mode leaves the receipt cleanly rejected rather than dangling.
OC_CASHRUN (OUT NAPPLIED INT, OUT NREJECTED INT): WHILE-driven cursor loop over PSTAT='E' receipts, ORDNO/PAYNO order, nested CALL OC_APPLYCASH per row. A CONTINUE HANDLER FOR SQLEXCEPTION absorbs the per-row SIGNAL from a bad receipt (incrementing NREJECTED) so one bad row does not abort the run; applied rows increment NAPPLIED. Non-atomic: a rejected receipt's EXIT-handler side effect (PSTAT='X') commits even as the outer loop continues.
OC_ADJCREDIT (IN P_CUSTNO CHAR(6), IN P_NEWLIMIT DECIMAL(11,2)): The only sanctioned way to change a credit limit. Clamps to [0, 1000000], updates OCCUST.CLIMIT, and writes an OCAUD 'ADJCREDIT' row with the new limit. Reachable directly and from the OCCUSTV INSTEAD OF trigger (so the clamp holds on both paths).
OC_AGING (IN P_ASOF INT) DYNAMIC RESULT SETS 1: DECLARE CURSOR ... WITH RETURN over ISTAT='O' invoices: returns INVNO, CUSTNO, open amount (ITOT-IPAID), days-past-due (DAYS(asof)-DAYS(DUEDT) via FN_YMD2ISO), and the FN_AGEBUCKET classification. The cursor is left open so the result set flows back to the caller (STRSQL, a driver, or a report).

F.4 Triggers (5) & the view reroute

TR_ORDL_BI — BEFORE INSERT on OCORDL (FOR EACH ROW, ATOMIC): Vetoes non-positive quantity (SIGNAL 75201), then defaults N.DISCPCT = FN_TIERDISC(N.QTY) and N.LAMT = FN_LINEAMT(N.QTY, N.PRICE) — so even a raw INSERT (not just OC_APPROVE) gets a computed amount. Proves a function called from inside a trigger's SET.
TR_ORDH_AI — AFTER INSERT on OCORDH (FOR EACH ROW, ATOMIC): Writes an OCAUD 'INSERT' row (op INSERT, table OCORDH, key = order number, detail = the customer). One audit row per order header.
TR_INVH_AU — AFTER UPDATE OF ISTAT on OCINVH, WHEN (O.ISTAT≠N.ISTAT): Writes an OCAUD 'STATUS' row (detail O.ISTAT || '->' || N.ISTAT) only when the invoice status actually changes — e.g. one row for O->P at full payment, and none for a partial payment that leaves ISTAT at 'O'. Proves UPDATE OF <col> + OLD/NEW + WHEN together.
TR_CUST_BU — BEFORE UPDATE on OCCUST (FOR EACH ROW, ATOMIC): Vetoes a balance going negative (N.CBAL < 0 → SIGNAL 75202), all-or-nothing. Direct UPDATEs to CBAL are otherwise legal on the base table (unlike CLIMIT, which is view-gated).
TR_CUSTV_IOU — INSTEAD OF UPDATE on OCCUSTV (FOR EACH ROW, ATOMIC): Intercepts a direct UPDATE to the view. If N.CLIMIT≠O.CLIMIT, it CALL OC_ADJCREDIT(N.CUSTNO, N.CLIMIT) so the [0, 1,000,000] clamp always applies (never an unclamped straight write to OCCUST). If N.CSTAT≠O.CSTAT, it applies that change directly to the base table. Proves INSTEAD OF + a procedure CALLed from a trigger.

F.5 SQL-PL control-flow / cursor / handler / SIGNAL patterns

OCASH/i is a compact catalogue of the SQL-PL idioms the engine must support. Each is used for real in a named routine:

Cursor + CONTINUE HANDLER FOR NOT FOUND. OC_APPROVE / OC_CASHRUN: DECLARE C1 CURSOR FOR SELECT ..., a DONE flag set by a NOT FOUND handler, an OPEN/prime-FETCH/WHILE DONE=0 loop with a fetch at the bottom, then CLOSE.
Query-driven FOR loop. OC_BILLRUN: FOR RLOOP AS SELECT ... DO ... END FOR; — implicit per-row cursor, no explicit OPEN/FETCH.
Nested CALL. OC_BILLRUN→OC_BILL and OC_CASHRUN→OC_APPLYCASH — a procedure calling another procedure per loop iteration, with OUT params flowing back.
WITH RETURN result set. OC_AGING: DECLARE ... CURSOR WITH RETURN FOR ... + DYNAMIC RESULT SETS 1, left open so the caller receives it.
SIGNAL with SQLSTATE + MESSAGE_TEXT. OC_APPLYCASH raises 75101/75102; TR_ORDL_BI raises 75201; TR_CUST_BU raises 75202 — SIGNAL SQLSTATE '7510x' SET MESSAGE_TEXT = '...'.
NOT FOUND → SIGNAL bridge. In OC_APPLYCASH, a DECLARE ... CONDITION FOR SQLSTATE '75100' plus a CONTINUE HANDLER FOR NOT FOUND that SIGNALs it — converting a SQL/PSM class-02 completion condition (which does not fall into SQLEXCEPTION) into a real exception, so the EXIT handler fires uniformly for every failure mode.
EXIT HANDLER FOR SQLEXCEPTION + GET DIAGNOSTICS + RESIGNAL. OC_APPLYCASH's cleanup: capture MESSAGE_TEXT, mark the receipt rejected, set the OUT result, then RESIGNAL — a controlled failure whose side effect survives.
CONTINUE HANDLER FOR SQLEXCEPTION (absorb). OC_CASHRUN counts a bad row as rejected and keeps looping instead of aborting.
Compound & RETURN-expression functions. Compound (FN_TIERDISC/FN_LINEAMT/FN_AGEBUCKET, with local DECLAREs and IF chains) and single-expression (FN_YMD2ISO/FN_DUEDATE) forms both used.
IF / ELSEIF / END IF control flow. FN_AGEBUCKET's bucket chain; the clamp branches in OC_ADJCREDIT; the approve/reject branch in OC_APPROVE.

Embedded-SQL reach (OCCALLRPG)

The one SQLRPGLE program uses the minimal reach idioms — it holds no business logic:

EXEC SQL CALL with host IN/OUT. exec sql call OCASH.OC_APPROVE(:napp, :nrej); then DSPLY %char(sqlcode).
Nested-procedure CALL. exec sql call OCASH.OC_BILLRUN(:nbilled);.
Failing CALL surfacing a negative SQLCODE. Re-applying an already-applied payment yields the routine's own RESIGNAL → SQLCODE -438 at the host; on a failed CALL the engine does not propagate OUT params, so the RPG RESULT stays blank — the failure is explicit, not silent.

F.6 Application SQLSTATE table

SQLSTATE	Raised by	Meaning
75100	OC_APPLYCASH (NOT FOUND bridge)	No open payment/invoice found for this PAYNO (converts a NOT FOUND completion into an exception).
75101	OC_APPLYCASH	Invoice is not open for payment (ISTAT≠'O').
75102	OC_APPLYCASH	Over-payment: IPAID+PAMT would exceed the invoice total.
75201	TR_ORDL_BI	Order line quantity must be positive.
75202	TR_CUST_BU	AR balance cannot go negative.

Both OC_APPLYCASH's EXIT handler and OC_CASHRUN's CONTINUE handler are keyed on SQLEXCEPTION generally, so they catch all of 75100–75102 (plus any unexpected engine error) uniformly. The -438 host SQLCODE seen in the RPG reach test is the standard code for an application-raised SQLSTATE surfacing through a failed CALL.

G. Glossary ↑ top

Aging bucket: A classification of how overdue an open invoice is: CURRENT / 1-30 / 31-60 / 61-90 / 90+ days past due (FN_AGEBUCKET), as of a given as-of date (OC_AGING).
AR ledger (OCLEDG): The append-only, signed trail of every balance-affecting event: billing (+), cash (-), credit adjustment (0). For a zero-start customer, CBAL == SUM(OCLEDG.LAMT) — the reconciliation invariant.
Billing: Generating an invoice from an approved order (OC_BILL): create OCINVH/OCINVL, compute the due date, post a positive ledger row, and raise the customer's AR balance and YTD.
Credit approval / credit gate: The rule OC_APPROVE applies: reject an order for an on-hold customer, or one whose amount would push the customer's balance over the credit limit; otherwise approve.
Credit-limit clamp: OC_ADJCREDIT bounds any new credit limit to [0, 1,000,000]. The OCCUSTV view routes all limit edits through it (TR_CUSTV_IOU), so a limit can never be written unclamped through the view.
Cash application (waterfall-free): Applying a receipt to its invoice (OC_APPLYCASH): validate the invoice is open, reject an over-payment, raise IPAID (flip to Paid when fully paid), post a negative ledger row, lower the balance. OCASH/i applies one receipt to one invoice (no fees/interest waterfall).
Discount tier: A quantity breakpoint (OCTIER) giving a discount % for orders at or above a minimum quantity; the highest breakpoint ≤ the quantity wins (FN_TIERDISC).
DYNAMIC RESULT SETS / WITH RETURN: The SQL-PL construct that lets a procedure return a query result set to its caller by leaving a declared cursor open (OC_AGING).
EXIT / CONTINUE handler: A declared reaction to a condition. An EXIT handler runs its body then leaves the routine (used for cleanup + RESIGNAL in OC_APPLYCASH); a CONTINUE handler runs its body then resumes after the statement that raised the condition (used to absorb a bad row in OC_CASHRUN, and for NOT FOUND loop termination).
GET DIAGNOSTICS: The SQL-PL statement that reads diagnostic info (e.g. MESSAGE_TEXT of the current condition) inside a handler — used by OC_APPLYCASH's EXIT handler to capture the failing message.
Idempotent: Safe to run again with the same result. Every OCASH/i stage is idempotent off document status: approve/bill/cash-apply only touch rows in their entry state and flip them, so a re-run is a no-op.
INSTEAD OF trigger: A trigger on a view that replaces the attempted INSERT/UPDATE/DELETE with its own logic. TR_CUSTV_IOU reroutes an OCCUSTV credit-limit UPDATE through the clamping procedure.
Net days / payment terms: The number of days a customer has to pay (OCCUST.CTERMS); the invoice due date is the invoice date + net days (FN_DUEDATE).
SIGNAL / RESIGNAL: SIGNAL raises an application condition (an SQLSTATE + message); RESIGNAL re-raises the condition currently being handled, propagating it after a handler has done its cleanup.
SQL PL: SQL Procedural Language — the DB2 for i procedural dialect (functions, procedures, triggers with variables, cursors, control flow, handlers). OCASH/i is written entirely in it.
SQLCODE / SQLSTATE: SQL return-status values. SQLCODE 0 is success; a negative code (e.g. -438) with an application SQLSTATE (e.g. 75102) signals a business-rule rejection.
SQLRPGLE / EXEC SQL CALL: RPG with embedded SQL. In OCASH/i the SQLRPGLE program is only a reach-path proof — it EXEC SQL CALLs the SQL-PL procedures and DSPLYs the SQLCODE; it holds no business logic.
STRSQL: Start Interactive SQL — the IBM i interactive SQL session from which an operator CALLs the procedures and runs ad-hoc SQL (OCASH/i's everyday entry point).