INSCLM/i — Health-Claims Adjudication

INSCLM/i is a health / insurance claims-adjudication application: claim intake with duplicate detection, member-eligibility checking, benefit calculation (network-rate allowed amount, annual deductible, plan/member coinsurance split, out-of-pocket-maximum cap), pro-rata allocation down to claim lines, member-accumulator carry-forward, GL posting and payment/remittance generation, and a sanctioned payment-void path. It is a pure SQL PL application: every business rule lives in DB2 for i compound functions, stored procedures and triggers — there is no 5250 screen and no RPG business logic. The routines are driven from STRSQL (interactive CALL), from a batch CALL of SP_ADJUDICATE_BATCH, and — to prove the reach path only — from a thin SQLRPGLE caller. This manual is the reference for the operator who runs the adjudication cycle and for the developer maintaining the routines. It is grounded entirely in the committed source (sqlpl-app-claims/src/schema.sql, src/routines.sql, src/seed.mjs, and the test/hc_sqlpl_daily.mjs / test/hc_sqlpl_volume_sim.mjs drivers).

A. Overview & Architecture ↑ top

A.1 What it does

INSCLM/i adjudicates a health-insurance claim from intake to payment:

• Intake & duplicate detection — a claim header + its lines are entered (CSTAT E). A BEFORE-INSERT trigger stamps a duplicate-detection hash (member+provider+service-date+billed) and, if a matching claim already exists, pends the new one (P/D1) so it can never be double-paid; an inbound row masquerading as an already-adjudicated duplicate is vetoed outright.
• Eligibility — the member must exist, be active, and have the service date inside the coverage window (effective…termination). Ineligible claims are denied with a hold code (D2 termed, D3 not yet effective, D4 suspended, D1 not found).
• Benefit calculation — allowed = billed × network rate; deductible taken from the member's remaining deductible; plan/member coinsurance split on the post-deductible remainder; the member's total responsibility capped at the out-of-pocket maximum (whatever is shaved off flows back to the plan).
• Line pro-rata — the header allowed / plan-liability / member-responsibility are prorated down to each claim line by billed ratio, with the last line absorbing the rounding residue so the lines sum back to the header exactly.
• Accumulator, GL & payment — on adjudication a trigger folds this claim's deductible and member responsibility into the member's benefit-year accumulators; the GL is posted (plan-expense + member-contra rows) and a payment/remittance row is generated — each idempotently.
• Void — a payment can be reversed only through SP_VOID_PAYMENT, which flips the payment to void and posts a negative GL reversal; a direct edit of the remittance view is rejected or rerouted through that procedure.

A.2 SQL-PL-only architecture: the data layer is the application

Unlike a mixed RPG/SQL-PL application, INSCLM/i has no orchestration layer and no display file. Every rule lives in routines.sql as DB2 for i SQL PL:

• Compound functions (FN_*) — the pure arithmetic primitives: the duplicate hash, the network-rate allowed amount, the deductible-apply and the OOP-cap. Kept as functions (not inlined) so the same logic is reused by procedures, triggers and ad-hoc STRSQL.
• Compound procedures (SP_*) — the adjudication engine (SP_ADJUDICATE_CLAIM), its nested helpers (SP_ELIGIBILITY, SP_PRORATE_LINES, SP_POST_GL, SP_GEN_PAYMENT), the batch driver (SP_ADJUDICATE_BATCH) and the void path (SP_VOID_PAYMENT).
• Triggers (TR_*) — genuine trigger-driven state: intake duplicate pend/veto, the member-accumulator carry-forward (AFTER UPDATE OF CSTAT), and the remittance view's INSTEAD OF UPDATE guard.

The RPG layer is present only as a reach proof: the daily test compiles a tiny SQLRPGLE program (CALLADJ) whose whole job is one EXEC SQL CALL SP_ADJUDICATE_CLAIM(…) and one EXEC SQL CALL SP_VOID_PAYMENT(…), DSPLYing the host-variable round trip. No claim math is coded in RPG. Everything runs in library INSCLM.

A.3 Component & flow

  INTAKE                 ADJUDICATE (per claim)              BATCH / VOID
  ------                 ---------------------               ------------
  INSERT HCCLMH  ----->  SP_ADJUDICATE_CLAIM                 SP_ADJUDICATE_BATCH
    TR_HCCLMH_DUP          1 line-sum edit (E2)                cursor over CSTAT='E'
    (pend D1 / veto)       2 SP_ELIGIBILITY  (nested CALL)      -> SP_ADJUDICATE_CLAIM
  INSERT HCCLML            3 dup re-check (FN_DUPHASH)           per claim
                          4 FN_ALLOWED (network rate)         WITH RETURN result set
                          5 FN_APPLY_DEDUCTIBLE                (HCCLMRUN via HCCLMTMP)
                          6 coinsurance split
                          7 FN_APPLY_OOPCAP                    SP_VOID_PAYMENT
                          8 UPDATE HCCLMH  --fires-->            flip HCPAY -> V
                             TR_HCCLMH_ACCUM (HCMEM accum)        post -LAMT to HCGL
                            SP_PRORATE_LINES (nested, cursor)
                            SP_POST_GL     (nested)            HCREMIT view:
                            SP_GEN_PAYMENT (nested)             TR_HCREMIT_IOU reroutes
                                                                a void-style UPDATE
     HCCLMH  <--writes-- every procedure     triggers: TR_HCCLMH_DUP / _ACCUM (HCCLMH)
       |  \                                             TR_HCREMIT_IOU (HCREMIT view)
       |   +--> HCCLML  (prorated lines, sum back exactly)
       |   +--> HCGL    (append-only ledger: E plan-expense, M member, V reversal)
       |   +--> HCPAY   (one payment per paid claim)  --view--> HCREMIT
       |   +--> HCAUD   (trigger-written accumulator audit)
       +------> HCMEM   (DEDMET / OOPMET accumulators, carried forward)

A single claim flows: INSERT header (the duplicate trigger stamps/pends it) and lines → CALL SP_ADJUDICATE_CLAIM (or let the batch pick it up) → the eight-step engine computes and persists the header, prorates the lines, and the CSTAT→'A' update fires the accumulator trigger → GL rows and a payment are generated. Balances and accumulators are only ever moved by the sanctioned procedures and triggers.

A.4 Object inventory

The full catalogue is 4 functions + 7 procedures + 3 triggers, over 9 tables and 1 view, driven from STRSQL / a batch CALL and reached (for proof) by one SQLRPGLE program. Sections D and F expand each.

B. Online / Access ↑ top

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

Honest statement: INSCLM/i has no display file and no interactive 5250 program. It is a pure SQL PL application; the "user interface" is the SQL CALL statement issued from interactive SQL (STRSQL), from an ad-hoc RUNSQL/JDBC session, or from a batch job. The operator equivalent of "open a screen" is "enter a CALL and press Enter" in STRSQL; the job's library list must include INSCLM — the tested jobs run with LIBL = QSYS QGPL INSCLM QTEMP and CURLIB = INSCLM.

The functions are callable from a bare SELECT … FROM SYSIBM.SYSDUMMY1 — the daily test exercises each in isolation exactly this way (e.g. FN_APPLY_OOPCAP(1600.00, 2000.00, 760.00) returns 1240.00). The OUT parameters of SP_ADJUDICATE_CLAIM (status, hold code, plan liability, member responsibility) come back on the CALL so a caller gets the full result without re-querying; SP_ADJUDICATE_BATCH additionally surfaces a WITH RETURN result set (one row per adjudicated claim) that STRSQL displays.

SQLRPGLE reach path (proof only)

To prove the platform's SQLRPGLE→SQL-PL bridge works, the daily test compiles a minimal SQLRPGLE program, CALLADJ, that does nothing but the host-variable round trip:

dcl-s clmno char(10);   dcl-s cstat char(1);   dcl-s holdcd char(2);
dcl-s planlia packed(9:2);   dcl-s membresp packed(9:2);
clmno = 'CLM0000005';
exec sql call SP_ADJUDICATE_CLAIM(:clmno, :cstat, :holdcd, :planlia, :membresp);
dsply ('R1=' + cstat + '/' + holdcd + '/' + %char(sqlcode));   -> R1=D/D2/0
exec sql call SP_VOID_PAYMENT('PYNOSUCH02');
dsply ('R2=' + %char(sqlcode));                                -> R2=-438 (unhandled SIGNAL)

An in-range business result surfaces as SQLCODE 0; an unhandled SIGNAL raised by a procedure surfaces to the RPG caller as SQLCODE -438. No claim logic lives in this program — it is a reach proof, and operationally you drive the app from STRSQL/batch as above.

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

Honest statement: INSCLM/i does not model a true four-eyes maker–checker / separate-authorization workflow. A claim entered and adjudicated is paid in one flow; there is no "one user adjudicates, a second approves" gate in the code. The control model the application does have is enforced entirely in the data layer:

• Duplicate control (TR_HCCLMH_DUP). At INSERT time, the BEFORE trigger stamps the duplicate hash and either pends a plausible duplicate (CSTAT='P', HOLDCD='D1') so the batch never picks it up, or vetoes (SIGNAL 75020) an inbound row that claims to be an already-adjudicated duplicate. SP_ADJUDICATE_CLAIM re-checks duplicates at adjudicate time as a belt-and-braces second guard.
• Eligibility gating. SP_ELIGIBILITY denies out-of-window / suspended / unknown members before any money is computed (hold codes D1–D4).
• Intake edit. The claim's line billed amounts must sum to the header billed total; a mismatch is denied E2 before eligibility is even checked.
• Accumulator audit (HCAUD). Each successful adjudication writes one ACCUM audit row (TR_HCCLMH_ACCUM) recording the deductible and member responsibility folded into the member — the after-the-fact accountability trail.
• Append-only ledger (HCGL). Every plan-expense, member-contra and void-reversal entry is an immutable ledger row keyed by LSEQ; nothing is updated in place, so the financial history is fully reconstructable.
• Channel gating via the view. TR_HCREMIT_IOU (INSTEAD OF UPDATE on HCREMIT) rejects a direct edit of a remittance amount (SIGNAL 75021) and reroutes a void-style update through SP_VOID_PAYMENT — a payment amount can change only through the sanctioned procedure.

In sum, the control posture is duplicate/eligibility/intake gating + trigger-written audit + append-only ledger + channel gating, all in the data layer, rather than a segregation-of-duties approval workflow.

C. Batch / Cycle Procedures ↑ top

INSCLM/i's "batch cycle" is a single driver, SP_ADJUDICATE_BATCH, run the way a real IBM i shop runs it: once per business day against whatever claims were entered that day. It cursor-loops every claim still in status E (entered), calls SP_ADJUDICATE_CLAIM for each, and surfaces a WITH RETURN result set of the per-claim outcome. There is no control-row date to advance — the driver simply picks up all CSTAT='E' work, so a bare CALL is the whole submission.

-- enter the day's claims (header then lines) ...
INSERT INTO INSCLM/HCCLMH VALUES ('CLM0000001','MB0000001','PRV000001','PLAN0001',
   20260715, 800.00, 0,0,0,0, ' ', 'E', '  ', '');
INSERT INTO INSCLM/HCCLML VALUES ('CLM0000001', 1, 600.00, 0,0,0), ('CLM0000001', 2, 200.00, 0,0,0);

-- ... then run the (parameterless-input) cycle
CALL INSCLM/SP_ADJUDICATE_BATCH(?, ?, ?);          -- OUT paid/denied/pended + a result set
-- or as a scheduled batch job:
SBMJOB CMD(RUNSQL SQL('CALL INSCLM/SP_ADJUDICATE_BATCH(?,?,?)')) JOB(HCADJ)

C.1 The adjudication cycle

SP_ADJUDICATE_BATCH opens a cursor over HCCLMH WHERE CSTAT='E' ORDER BY CLMNO and, for each claim, calls SP_ADJUDICATE_CLAIM, accumulating the per-claim outcome into the HCCLMTMP staging table during the loop. After the loop's cursor is closed it swaps the staging rows into HCCLMRUN (DELETE-then-INSERT) and opens the WITH RETURN cursor over HCCLMRUN. Each SP_ADJUDICATE_CLAIM runs the eight-step engine:

1. Intake edit — the lines' billed amounts must sum to the header billed total; a mismatch denies the claim D/E2 and returns immediately.
2. Eligibility — nested CALL SP_ELIGIBILITY; ineligible denies D/D2|D3|D4|D1.
3. Duplicate re-check — FN_DUPHASH against other CSTAT='A' claims; a match pends P/D1.
4. Allowed amount — FN_ALLOWED (provider contract rate in-network, else the plan's out-of-network rate).
5. Deductible — FN_APPLY_DEDUCTIBLE from the member's remaining deductible.
6. Coinsurance split — plan share = post-deductible remainder × plan coinsurance; member share = the rest.
7. OOP cap — FN_APPLY_OOPCAP caps the member's total responsibility (deductible + coinsurance) at the OOP room remaining; the shaved amount flows back to plan liability.
8. Persist — UPDATE the header (which fires TR_HCCLMH_ACCUM to move the member accumulator), then nested SP_PRORATE_LINES, SP_POST_GL, SP_GEN_PAYMENT.

Worked example (the daily-test seed, plan GOLD, member MB0000001, ded met 0):
  CLAIM 1  billed 800.00  allowed 800.00  ded 500.00  plan 240.00  member 560.00   [A]
  CLAIM 2  billed 1000.00 allowed 1000.00 ded   0.00  plan 800.00  member 200.00   [A]  ded carried fwd
  CLAIM 3  billed 8000.00 allowed 8000.00 ded   0.00  plan 6760.00 member 1240.00  [A]  OOP cap bites
  CLAIM 4  OON  billed 500.00 allowed 250.00 (0.5000) ded 250.00 plan 0.00 mbr 250 [A]
  CLAIM 5  termed member, svc 20260715 > term 20260630                             [D/D2]
  CLAIM 6  header 400.00 but lines sum 250.00                                       [D/E2]

C.2 Procedure / trigger table (the full driven set)

C.3 Ordering & dependencies

• Intake before adjudication. Header and lines must both be inserted before SP_ADJUDICATE_BATCH runs — the intake edit (step 1) reads HCCLML, and the duplicate trigger fires on the header INSERT.
• Only E claims are picked up. The batch cursor selects CSTAT='E', so pended (P) and already-adjudicated (A/D) claims are skipped — this is what makes a re-run a no-op.
• Within a claim the order is fixed by the engine: edit → eligibility → dup → allowed → deductible → coinsurance → OOP cap → persist (accumulator trigger) → prorate → GL → payment. The accumulator carry-forward happens via the trigger on the persist UPDATE, so it always reflects the values just computed.
• Void is independent and runs after a claim is paid; it never touches the member accumulator (a void is a financial reversal, not a clinical un-adjudication).

D. Data Files (data dictionary) ↑ top

All tables are in library INSCLM, grounded in schema.sql. Dates are stored as INT in YYYYMMDD form (99999999 = no termination / active); rates and coinsurance shares are DECIMAL(5,4) fractions (0.8000 = 80%); money is DECIMAL(9,2).

HCPLAN — Plan master (PK PLANNO)

Seed: PLAN0001 GOLD (deduct 500.00, coins .8000, OOP max 2000.00); PLAN0002 SILVER (deduct 1000.00, coins .7000, OUTNET .5000, OOP max 4000.00).

HCPROV — Provider master (PK PROVNO)

Seed: PRV000001 in-net rate 1.0000 (allowed = billed); PRV000003 out-of-network.

HCMEM — Member master + accumulators (PK MEMBNO)

DEDMET/OOPMET are the carried-forward state driven by TR_HCCLMH_ACCUM; they must survive claim-to-claim and across separate batch runs within a benefit year.

HCCLMH — Claim header (PK CLMNO)

HCCLML — Claim lines (PK CLMNO, CLINE)

The prorated ALLOWED/PLANLIA/MEMBRESP over all lines sum exactly back to the header (the last line absorbs the rounding residue).

HCPAY — Payment / remittance (PK PAYNO)

HCGL — GL ledger (append-only, PK LSEQ)

HCAUD — Trigger audit log (PK ASEQ)

HCCLMRUN / HCCLMTMP — Batch outcome + staging (PK CLMNO each)

Both carry CLMNO, CSTAT, HOLDCD, PLANLIA, MEMBRESP. SP_ADJUDICATE_BATCH accumulates each claim's outcome into HCCLMTMP during its cursor loop, then — only after the loop cursor is closed — swaps the contents into HCCLMRUN and opens the WITH RETURN cursor over it. The staging twin exists solely to work around confirmed platform bug SQLPL-PLAT-CLM-05 (a DELETE between a cursor's DECLARE and its OPEN corrupts that OPEN's materialization), documented in the schema; it is not a modelling concept the operator needs to track.

HCREMIT — Remittance view

CREATE VIEW INSCLM/HCREMIT AS SELECT PAYNO, CLMNO, PAYDT, PAMT, PSTAT FROM INSCLM/HCPAY. Updatable only through TR_HCREMIT_IOU (INSTEAD OF UPDATE): a remit-amount edit is rejected (75021); a void-style update (PSTAT → 'V') is rerouted through SP_VOID_PAYMENT.

Relationships

• HCMEM.PLANNO, HCCLMH.PLANNO → HCPLAN.PLANNO (plan lookup).
• HCCLMH.MEMBNO → HCMEM.MEMBNO; HCCLMH.PROVNO → HCPROV.PROVNO.
• HCCLML.CLMNO → HCCLMH.CLMNO (per-claim lines; PK CLMNO+CLINE).
• HCPAY.CLMNO → HCCLMH.CLMNO; HCCLMH.PAYNO → HCPAY.PAYNO (set once paid).
• HCGL.LREF references a claim (E/M/V) or payment; HCGL.MEMBNO → HCMEM.MEMBNO.
• HCAUD.KEYVAL holds the affected claim number; HCREMIT is a view over HCPAY.

Note: the schema declares primary keys but no explicit FOREIGN KEY constraints — referential integrity is maintained by the procedures/triggers, and the relationships above are the logical (not DDL-declared) keys.

E. Operations Runbook ↑ top

E.1 Day-in-the-life

1. Confirm the job's library list includes INSCLM (LIBL = QSYS QGPL INSCLM QTEMP, CURLIB = INSCLM).
2. Enter the day's claims: INSERT each header into INSCLM/HCCLMH (CSTAT E) and its lines into INSCLM/HCCLML. The BEFORE trigger stamps the dup hash and pends/vetoes duplicates at this point.
3. Run the cycle: CALL INSCLM/SP_ADJUDICATE_BATCH(?, ?, ?) (or SBMJOB a RUNSQL of it).
4. Post-check by re-querying the tables (not the OUT counters — see the CLM-04 note in C):

-- outcome by status
SELECT CSTAT, COUNT(*) FROM INSCLM/HCCLMH GROUP BY CSTAT;
-- any denials, with reason
SELECT CLMNO, HOLDCD FROM INSCLM/HCCLMH WHERE CSTAT IN ('D','P') ORDER BY CLMNO;
-- payments generated this run
SELECT COUNT(*) FROM INSCLM/HCPAY WHERE PSTAT = 'A';

Post-checks after the cycle:

• No claim remains CSTAT='E' for a member that should have adjudicated (every entered claim was reached by the loop).
• Exactly one PSTAT='A' payment per paid (CSTAT='A') claim — no double-pay.
• The GL has an E and an M row per paid claim (2 rows each).
• Pended (P/D1) claims are duplicates and were never paid; denials carry the expected hold code.

Handle a payment reversal on demand with CALL INSCLM/SP_VOID_PAYMENT('<PAYNO>'); never edit HCPAY.PAMT or the HCREMIT view directly.

E.2 Reconciling figures

These are the same invariants the daily and volume drivers check against an independent hand-derived JS oracle. They are the operator's reconciliation checklist.

• Per-claim benefit split — PLANLIA + MEMBRESP = ALLOWED on every paid claim (e.g. 240.00 + 560.00 = 800.00 on claim 1).
• Line sum-back — SUM of line ALLOWED/PLANLIA/MEMBRESP equals the header, exactly (claim 1: lines 600+200 allowed, 180+60 plan, 420+140 member).
• Deductible carry-forward — a member's second claim in a benefit year applies DEDAPPL = 0 once the deductible is met (claim 2: deductible met by claim 1).
• OOP cap — a member's OOPMET never exceeds the plan OOPMAX; the capping claim shifts the shaved amount to plan liability (claim 3: member capped at 1240.00, plan absorbs 6760.00, OOPMET lands at exactly 2000.00).
• GL tie-out — SUM(HCGL LAMT WHERE LTYPE='E') = SUM(HCCLMH.PLANLIA WHERE CSTAT='A') and the member (M) rows tie to SUM(MEMBRESP). Daily seed: GL E total = 240+800+6760+0 = 7800.00.
• Void tie-out — once a payment is voided, SUM(E) + SUM(V) = SUM(PLANLIA of claims still ACTIVELY paid); the reversal row is −PLANLIA of the voided claim.

-- reconcile the GL to paid claims
SELECT (SELECT SUM(LAMT) FROM INSCLM/HCGL WHERE LTYPE='E') AS GL_PLAN_EXPENSE,
       (SELECT SUM(PLANLIA) FROM INSCLM/HCCLMH WHERE CSTAT='A') AS CLAIMS_PLANLIA
  FROM SYSIBM.SYSDUMMY1;
-- a member's benefit-year accumulators
SELECT MEMBNO, DEDMET, OOPMET FROM INSCLM/HCMEM WHERE MEMBNO = 'MB0000001';

E.3 Failure & re-run rules

F. Developer Reference ↑ top

The complete SQL-PL surface, from routines.sql. All objects are in library INSCLM. Signatures are given as declared.

F.1 Functions (4)

FN_DUPHASH (MEMBNO CHAR(9), PROVNO CHAR(9), SVCDT INT, BILLED DECIMAL(9,2)) RETURNS CHAR(32)

The duplicate-detection composite key: MEMBNO || '-' || PROVNO || '-' || CHAR(SVCDT) || '-' || CHAR(INT(BILLED*100)). DETERMINISTIC. Kept as a function so the BEFORE trigger, the adjudicate-time re-check and ad-hoc STRSQL all use identical logic.

FN_ALLOWED (BILLED DECIMAL(9,2), INNETW CHAR(1), CTRRATE DECIMAL(5,4), OUTNET DECIMAL(5,4)) RETURNS DECIMAL(9,2)

Allowed amount = BILLED × (INNETW='Y' ? CTRRATE : OUTNET) via an IF ladder. In-network uses the provider contract rate; out-of-network uses the plan's OUTNET rate. DETERMINISTIC.

FN_APPLY_DEDUCTIBLE (ALLOWED DECIMAL(9,2), DEDUCT DECIMAL(9,2), DEDMET DECIMAL(9,2)) RETURNS DECIMAL(9,2)

Deductible taken on this claim = MIN(ALLOWED, DEDUCT−DEDMET), floored at 0 (never negative once met). DETERMINISTIC.

FN_APPLY_OOPCAP (MEMBSHARE DECIMAL(9,2), OOPMAX DECIMAL(9,2), OOPMET DECIMAL(9,2)) RETURNS DECIMAL(9,2)

Caps a member share at the OOP room remaining: ROOM = MAX(0, OOPMAX−OOPMET); returns MIN(MEMBSHARE, ROOM). DETERMINISTIC.

F.2 Procedures (7)

SP_ELIGIBILITY (IN P_MEMBNO CHAR(9), IN P_SVCDT INT; OUT P_ELIG CHAR(1), OUT P_REASON CHAR(2))

Reads HCMEM (CONTINUE HANDLER FOR NOT FOUND sets a found flag). IF/ELSEIF ladder: not found → D1; MSTAT≠'A' → D4; svc < EFFDT → D3; svc > TERMDT → D2; else eligible Y. Called nested from SP_ADJUDICATE_CLAIM.

SP_PRORATE_LINES (IN P_CLMNO CHAR(10), IN P_ALLOWED, P_PLANLIA, P_MEMBRESP, P_BILLEDTOT DECIMAL(9,2))

Cursor loop over the claim's lines ordered by CLINE, prorating each of allowed/plan/member by billed ratio (DECIMAL(header × lineBilled / billedTot, 9, 2)); the last line absorbs the residue so lines sum back to the header exactly. CONTINUE handler on NOT FOUND drives the fetch.

SP_ADJUDICATE_CLAIM (IN P_CLMNO CHAR(10); OUT P_CSTAT CHAR(1), P_HOLDCD CHAR(2), P_PLANLIA, P_MEMBRESP DECIMAL(9,2))

The 8-step engine (see C.1): intake edit (E2), nested eligibility, dup re-check, allowed, deductible, coinsurance split, OOP cap on the member total, persist header + nested prorate/GL/payment. The header UPDATE fires TR_HCCLMH_ACCUM for the accumulator carry-forward — the procedure does not touch DEDMET/OOPMET itself.

SP_POST_GL (IN P_CLMNO CHAR(10), P_MEMBNO CHAR(9), P_PLANLIA, P_MEMBRESP DECIMAL(9,2), P_SVCDT INT)

Idempotent (guarded by NOT EXISTS (… LTYPE='E' AND LREF=P_CLMNO)): posts an E plan-expense row and an M member-contra row, each at MAX(LSEQ)+1/+2. Split out of the engine per SQLPL-PLAT-CLM-03.

SP_GEN_PAYMENT (IN P_CLMNO CHAR(10), P_SVCDT INT, P_PLANLIA DECIMAL(9,2))

Idempotent (guarded by NOT EXISTS (… HCPAY WHERE CLMNO=P_CLMNO)): generates PY+claim-suffix, inserts HCPAY (PSTAT 'A', PAMT = plan liability), stamps HCCLMH.PAYNO. Also split out per SQLPL-PLAT-CLM-03.

SP_ADJUDICATE_BATCH (OUT P_PAID INT, OUT P_DENIED INT, OUT P_PENDED INT) — DYNAMIC RESULT SETS 1

Cursor over CSTAT='E'; nested CALL SP_ADJUDICATE_CLAIM per claim, accumulating outcomes into HCCLMTMP; after CLOSE, swaps HCCLMTMP→HCCLMRUN and opens the WITH RETURN cursor over HCCLMRUN. OUT counters are unreliable (SQLPL-PLAT-CLM-04) — read outcomes from the result set / tables.

SP_VOID_PAYMENT (IN P_PAYNO CHAR(10))

The only sanctioned void. NOT FOUND → SIGNAL 75010; already void → SIGNAL 75011. Otherwise flips HCPAY.PSTAT='V' and posts a V reversal GL row of −PAMT. Does not touch the member accumulator.

F.3 Triggers (3) & the view

TR_HCCLMH_DUP — BEFORE INSERT ON HCCLMH, FOR EACH ROW

Computes N.DUPHASH = FN_DUPHASH(…). If an existing CSTAT='A' duplicate exists and the inbound row is itself CSTAT='A' → SIGNAL 75020 (veto). Otherwise, if any duplicate in (A,P,E) exists, pre-mark the inbound row CSTAT='P', HOLDCD='D1'. Always stamps N.DUPHASH.

TR_HCCLMH_ACCUM — AFTER UPDATE OF CSTAT ON HCCLMH, WHEN (N.CSTAT='A' AND O.CSTAT≠'A')

The accumulator carry-forward: UPDATE HCMEM SET DEDMET=DEDMET+N.DEDAPPL, OOPMET=OOPMET+N.MEMBRESP WHERE MEMBNO=N.MEMBNO, then writes an ACCUM HCAUD row at MAX(ASEQ)+1. Fires only on the transition into paid state, so re-running the batch cannot double the accumulator.

TR_HCREMIT_IOU — INSTEAD OF UPDATE ON HCREMIT, FOR EACH ROW

If N.PAMT ≠ O.PAMT → SIGNAL 75021 (remit amount immutable). If N.PSTAT='V' AND O.PSTAT≠'V' → CALL SP_VOID_PAYMENT(O.PAYNO). Ensures remittance amounts move only through the sanctioned procedure.

HCREMIT view: SELECT PAYNO, CLMNO, PAYDT, PAMT, PSTAT FROM INSCLM/HCPAY — read-through, update-guarded by the trigger above.

F.4 SQL-PL patterns used here

• Compound functions with control flow — IF-ladder FN_ALLOWED and MIN/MAX clamps in FN_APPLY_DEDUCTIBLE/FN_APPLY_OOPCAP; all DETERMINISTIC.
• NOT FOUND handler — DECLARE CONTINUE HANDLER FOR NOT FOUND SET V_FOUND=0 to turn a missing row into a branch (eligibility, void).
• Cursor + fetch loop — DECLARE C1 CURSOR… OPEN / FETCH / WHILE V_DONE=0 DO … END WHILE / CLOSE, driven by a CONTINUE NOT FOUND handler (prorate, batch).
• Nested CALL — SP_ADJUDICATE_CLAIM calls four sub-procedures; SP_ADJUDICATE_BATCH nests a CALL inside a cursor loop.
• WITH RETURN result set — DECLARE RS CURSOR WITH RETURN FOR …; OPEN RS under DYNAMIC RESULT SETS 1 to surface batch outcomes to STRSQL/CALL.
• Trigger-driven state — the member accumulator is moved only by a WHEN-guarded AFTER trigger, not by inline procedural code.
• SIGNAL / SQLSTATE — business-rule rejections raise application SQLSTATEs (below); unhandled, they surface to an RPG caller as SQLCODE -438.
• Idempotency by guard — NOT EXISTS guards on GL/payment inserts and the E-only batch cursor make re-runs no-ops without a control flag.
• Platform-bug avoidances (documented in source, business logic unchanged) — IF/ELSEIF instead of CASE…END CASE (CLM-01); GL/payment split into own procedures (CLM-03); HCCLMTMP staging twin (CLM-05); caller-assigned ASEQ/LSEQ instead of IDENTITY (PLAT-03); OUT counters treated as unreliable (CLM-04).
• DECIMAL truncation — DB2-for-i DECIMAL assignment truncates extra scale (not rounding); the pro-rata math and the JS oracle both mirror this so figures reconcile to the cent.

F.5 Application SQLSTATE table

The claim-level denials/pends use hold codes on the row (HCCLMH.HOLDCD: D1–D4, E2) rather than SQLSTATE — a denied or pended claim is a normal, recorded outcome, not an SQL exception. Only the guard conditions above raise SQLSTATE. An unhandled SIGNAL surfaces to a SQLRPGLE caller as SQLCODE -438.

G. Glossary ↑ top

Adjudication

The end-to-end processing of a claim: intake edit, eligibility, duplicate check, benefit calculation, line pro-rata, accumulator carry-forward, GL post and payment generation (SP_ADJUDICATE_CLAIM).

Allowed amount

Billed × the applicable network rate (in-network provider contract rate, else the plan's out-of-network rate). The base on which deductible and coinsurance are computed (FN_ALLOWED).

Accumulator (DEDMET / OOPMET)

The member's running deductible-met and out-of-pocket-met totals for the benefit year, carried forward claim to claim by TR_HCCLMH_ACCUM.

Coinsurance

The share split of the post-deductible remainder between plan and member (plan share = remainder × plan COINS; member share = the rest).

Deductible

The amount a member pays before the plan begins sharing cost; taken from the member's remaining deductible on each claim (FN_APPLY_DEDUCTIBLE).

Duplicate hash (DUPHASH)

A composite of member+provider+service-date+billed used to detect a re-billed claim (FN_DUPHASH); a match pends the new claim so it is never double-paid.

Hold code (HOLDCD)

The reason a claim was denied or pended: D1 duplicate/not-found, D2 termed, D3 not-yet-effective, D4 suspended, E2 line/header mismatch.

Idempotent

Safe to run again with the same result. The batch (E-only cursor), GL post and payment generation (NOT EXISTS guards) are idempotent; re-running never doubles the accumulator, GL or payments.

INSTEAD OF trigger

A trigger on a view that replaces the attempted DML with its own logic. TR_HCREMIT_IOU rejects a remit-amount edit and reroutes a void through SP_VOID_PAYMENT.

Out-of-pocket maximum (OOP max)

The cap on a member's total cost share for the benefit year; once met, further claims cost the member nothing and the plan absorbs the full allowed amount (FN_APPLY_OOPCAP).

Pro-rata (line allocation)

Distributing the header allowed/plan/member amounts down to claim lines by billed ratio, with the last line absorbing the rounding residue so lines sum back to the header exactly (SP_PRORATE_LINES).

SIGNAL / SQLSTATE / SQLCODE

SQL PL raises a business-rule exception with SIGNAL SQLSTATE '…'; SQLCODE 0 is success, and an unhandled SIGNAL reaches an RPG caller as SQLCODE -438.

SQL PL

SQL Procedural Language — DB2 for i's compound-statement language for functions, procedures and triggers. This entire application is written in it.

STRSQL

Start Interactive SQL — the IBM i session from which an operator drives this app by entering CALL and SELECT statements (there is no 5250 screen).

Void

Reversing a payment: flipping HCPAY to V and posting a negative GL reversal (SP_VOID_PAYMENT). A financial reversal only — it does not un-adjudicate the claim or move the accumulator.

WITH RETURN result set

A cursor opened under DYNAMIC RESULT SETS so a procedure hands rows back to its caller; SP_ADJUDICATE_BATCH returns one row per adjudicated claim this way.