TELSQL/i — Telecom Rating & Billing

TELSQL/i is a telecom rating & billing application: it ingests raw call-detail records (CDRs) from a switch/mediation feed, parses each packed CDR string, classifies it into a time-of-day band, applies a bundle-then-overage minute split against the customer's rate plan, prices every call through a nested rate rule, journals rated usage, and rolls a period's usage into a taxed invoice that is then finalized. Every rating and billing rule lives in DB2 for i SQL PL — functions, stored procedures and table triggers. Unlike the RPG-orchestrated applications, TELSQL/i has no 5250 screen and no RPG driver: it is a pure SQL-PL application invoked by CALL (from STRSQL, an embedded-SQL caller, or a scheduled job) and exercised end-to-end by the test/tel_daily.mjs battle. This manual is the reference for the operator who runs the rating and invoicing cycle and for the developer maintaining it. It is grounded entirely in the committed source (sqlpl-app-tel/src/schema.sql, src/routines.sql, and the test/tel_daily.mjs driver). Everything runs in library TELSQL.

A. Overview & Architecture ↑ top

A.1 What it does

TELSQL/i takes a telecom CDR feed all the way to a finalized invoice:

• CDR mediation & ingest — raw call records land in TELCDR, each carrying a single packed pipe-delimited string ORIGNO|DESTNO|DUR|CTYPE exactly as a switch feed delivers it, plus a structured CALLTS timestamp and CALLDUR seconds. A BEFORE-INSERT trigger vetoes a NULL or implausible duration outright, before the row is ever stored.
• Rating — the rating loop parses each CDR string field-by-field, cross-checks the parsed duration against the structured CALLDUR (a mediation consistency oracle), classifies the call into a time-of-day band (peak / off-peak / weekend), rounds duration up to whole billing-increment minutes, splits those minutes bundle-then-overage against the customer's remaining cycle bundle, prices the overage through a deeply nested rate rule, and writes a TELUSAGE row.
• Reject handling — a malformed, mismatched, unknown-customer or non-active-customer CDR is never allowed to abort the batch: a CONTINUE handler logs the reason to TELREJ, flips the CDR to rejected, and the loop moves on.
• Bundle accrual — an AFTER-INSERT trigger on TELUSAGE accrues the bundle-applied minutes of each rated call into the customer's cycle-to-date bundle counter (CYCMIN).
• Invoicing — per customer/period, usage charges are aggregated (with a window-function cross-check), the plan's flat fee is added, tax is computed from the plan's tax percent, and one invoice header + line rows are written. A period already invoiced is skipped (idempotent).
• Finalize — open invoices for a period are moved to finalized (ISTAT O→F), idempotently.

A.2 Pure-SQL-PL architecture (no RPG, no 5250 screen)

TELSQL/i is deliberately built as a pure SQL-PL application. There is no SQLRPGLE orchestration layer and no DDS display file — the entire application is the DB2 for i routines in routines.sql, over the tables in schema.sql:

• Logic layer (all of it). Rating, band classification, string parsing, the bundle/overage split, the nested rate CASE, tax, aggregation, idempotency guards and integrity vetoes are all implemented as SQL functions, stored procedures and triggers. There is nowhere else the logic lives.
• Access layer (thin, external). Whatever drives the application — ad-hoc STRSQL, an embedded-SQL program, or a scheduled job — only issues CALL statements and reads OUT parameters or result tables. In the committed repository the driver is the test/tel_daily.mjs battle, which loads the schema/routines, seeds CDRs, and calls the procedures exactly as an operator would.

The benefit for operations: the rules are one auditable place (the SQL-PL routines), reachable identically from any caller, and the application is self-contained in library TELSQL. The cost, documented honestly throughout this manual, is that there is no interactive screen; the operator works through SQL CALLs.

A.3 Component & flow

  INGEST                 RATING (batch)              INVOICING (batch)
  ------                 --------------              -----------------
  INSERT TELCDR          SP_RATE_BATCH  ---loop--->  SP_GEN_INVOICE (per cust/period)
    |  TR_TELCDR_VETO       FETCH CSTAT='E' rows       aggregate TELUSAGE over the period
    |  (75027/75028)        CALL SP_RATE_CDR             SUM(CHARGE) + window cross-check
    v                         FN_CDRFLD  (parse)         + plan FLATFEE, + TAX (TAXPCT)
  TELCDR (CSTAT E)            FN_TODBAND (band)          write TELINVH + TELINVL
    |                         FN_CEILMIN (minutes)         |
    |                         bundle/overage split         v
    |                         nested-CASE rate           SP_FINALIZE (ISTAT O->F)
    |                         INSERT TELUSAGE  --TR_TELUSAGE_ACCR--> TELCUST.CYCMIN += bundle
    |                         UPDATE CSTAT='R'
    |                       CONTINUE HANDLER (SQLEXCEPTION)
    +--> bad row -----------> SP_MARK_REJECT -> TELREJ + CSTAT='X'
                             TELAUD  <-- batch summary row (idempotency proof)

A single rating run flows: operator seeds/receives CDRs into TELCDR (the veto trigger screens duration) → calls SP_RATE_BATCH → the procedure cursors every to-rate CDR, calling SP_RATE_CDR per row, which parses, bands, splits, prices and inserts TELUSAGE (firing the bundle-accrual trigger) or, on any error, is caught by the batch's CONTINUE handler and logged to TELREJ → then per customer the operator calls SP_GEN_INVOICE and finally SP_FINALIZE.

A.4 Object inventory

The full catalogue is 3 functions + 5 procedures + 2 triggers over 9 physical files, all in library TELSQL. Sections C, D and F expand each.

B. Online & Access ↑ top

B.1 STRSQL CALL — honest: there is no screen

Honest statement: TELSQL/i has no 5250 display file and no interactive program. There is nothing to "open" — no subfile, no function keys, no maintenance panel. The application is reached entirely by issuing SQL CALL statements against its stored procedures. The operator equivalent of "run the transaction" is "type a CALL and Enter" from STRSQL (or drive the same CALL from an embedded-SQL program or a scheduled job). Before invoking anything, the job's library list must include TELSQL — the tested job runs with LIBL = QSYS QGPL TELSQL QTEMP and CURLIB = TELSQL.

Because there is no screen, all state is inspected with ordinary SELECTs (section E). The procedures signal their outcome two ways: through OUT parameters (counts, the generated invoice number, a return-code string such as GENERATED/SKIPPED) and through application SQLSTATEs raised by SIGNAL (section F.5) when a business rule is violated.

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

Honest statement: TELSQL/i does not model a true four-eyes maker–checker / separate-authorization workflow. No second user approves a rating run or an invoice; a CALL takes effect immediately. The manual documents the control model the application does have — audit + integrity + idempotency, all enforced in the data layer:

• Audit / trace trail (TELAUD). Every rating batch writes a summary row (OP='RATE_BATCH', detail seen=.. rated=.. rejected=..), and every invoice generation writes a row (OP='GEN_INVOICE', the invoice number + last-DML row count). This is the after-the-fact accountability and idempotency-proof trail.
• Reject log (TELREJ). Every CDR the rating loop could not rate is journaled with its CDRID and a human-readable reason carried from the SIGNAL message text — a complete, reconstructable record of what was rejected and why, with the CDR itself flipped to CSTAT='X' so it is never silently left un-rated.
• Ingest gating (TR_TELCDR_VETO). A BEFORE-INSERT trigger rejects a NULL call duration (75027) and an implausible one (<0 or >86400s, 75028) before the row is stored — the INSERT itself fails, so bad mediation data never enters rating.
• Rating gating. SP_RATE_CDR refuses to rate a CDR that is not found (75020), malformed (75021), whose parsed duration disagrees with the structured value (75022), whose customer is unknown (75023), or whose customer is not active (75024).
• Idempotency guards. SP_RATE_BATCH only ever selects CSTAT='E' rows and flips each to R/X, so a re-run rates nothing twice; SP_GEN_INVOICE refuses to re-invoice a customer/period that already has an invoice (returns SKIPPED); SP_FINALIZE only touches ISTAT='O' rows.

In sum, the control posture is audit + ingest/rating gating + idempotency enforced in the data layer, rather than a segregation-of-duties approval workflow.

C. Batch / Cycle Procedures ↑ top

TELSQL/i's work is a rating & billing cycle rather than a single monolithic job: an ingest step (CDRs arrive and are screened), a rating step (one batch call rates the whole feed), an invoicing step (one call per customer/period), and a finalize step (one call per period). Every step is a bare SQL CALL — there are no control-row-driven parameters as in the RPG applications; the period and customer are passed as CALL arguments.

-- 1. ingest: mediation feeds CDRs (veto trigger screens duration)
INSERT INTO TELSQL.TELCDR VALUES ('CDR0000001','C000001','5550001|5559001|1800|L',
                                  TIMESTAMP('2026-08-03-09.00.00'), 1800, 'E');
-- 2. rate the whole feed in one batch
CALL TELSQL.SP_RATE_BATCH(?, ?, ?);          -- OUT rated, rejected, seen
-- 3. invoice each customer for the period
CALL TELSQL.SP_GEN_INVOICE('C000001', '202608', ?, ?);   -- OUT invno, rc
-- 4. finalize the period
CALL TELSQL.SP_FINALIZE('202608', ?);        -- OUT finalized count

C.1 The rating/billing cycle

Ingest — INSERT TELCDR (screened by TR_TELCDR_VETO)

Raw CDRs land as CSTAT='E' ("to-rate") rows. The BEFORE-INSERT trigger TR_TELCDR_VETO fires first: a NULL CALLDUR is rejected with SQLSTATE 75027, and a duration < 0 or > 86400 seconds (24h) with 75028. A vetoed INSERT fails outright — the row is never written — so implausible mediation data can never reach rating.

Rate — SP_RATE_BATCH → SP_RATE_CDR

SP_RATE_BATCH opens a cursor over every CSTAT='E' CDR (ORDER BY CDRID) and, per row, calls SP_RATE_CDR. For each CDR the rating procedure:

1. parses the four packed fields with FN_CDRFLD (origin, dest, duration, call type);
2. validates the parsed duration is purely numeric and equals the structured CALLDUR (mediation cross-check; mismatch → 75022);
3. looks up the customer (unknown → 75023, not active → 75024) and their plan;
4. classifies the band with FN_TODBAND(CALLTS) and computes billing minutes with FN_CEILMIN(CALLDUR);
5. splits minutes bundle-then-overage against remaining bundle (BUNDMIN−CYCMIN, floored at 0);
6. prices the overage through the nested rate CASE (see below), floored at the plan's contracted overage rate;
7. inserts a TELUSAGE row (firing TR_TELUSAGE_ACCR, which adds the bundle-applied minutes to the customer's CYCMIN) and flips the CDR to CSTAT='R'.

The batch's CONTINUE handler for SQLEXCEPTION catches any per-row SIGNAL (or unexpected error), captures the message with GET DIAGNOSTICS, calls SP_MARK_REJECT to log to TELREJ and flip the CDR to CSTAT='X', increments the rejected count, and the loop moves on — one bad record never aborts the batch. An OKFLAG set immediately before each CALL and cleared by the handler ensures only a surviving call is counted as rated. After the loop, one TELAUD summary row records seen/rated/rejected.

The nested overage-rate rule (SP_RATE_CDR):
  intl (I)         -> PEAKRT * 3            (regardless of band)
  weekend (W band) -> OFFRT                 (regardless of call type)
  peak    (P band) -> local: PEAKRT*0.5, else PEAKRT
  off-pk  (O band) -> local: OFFRT*0.5,  else OFFRT
  then floor: RATE = GREATEST(RATE, plan OVERRT)
  CHARGE = OVERMIN * RATE     (bundled minutes are free)

Invoice — SP_GEN_INVOICE

Per customer/period, SP_GEN_INVOICE first checks for an existing invoice (returns SKIPPED with the existing invoice number if found — the idempotency guard). Otherwise it derives the period bounds (PSTART = first-of-month, PEND = PSTART + 1 MONTH), aggregates SUM(CHARGE) and a call count over the customer's rated usage in that window, cross-checks that plain aggregate against an independent window-function running total (mismatch → 75026), computes SUBTOT = FLATFEE + usage, TAX = ROUND(SUBTOT×TAXPCT/100, 2) and TOTAL, then writes the TELINVH header (ISTAT='O') and TELINVL lines (a flat-fee line always; a usage line when the call count > 0). The invoice number is 'IV' || CUSTNO || last-4-of-period. A TELAUD row records the generation.

Finalize — SP_FINALIZE

SP_FINALIZE(period) updates every ISTAT='O' invoice for the period to ISTAT='F' and returns the count finalized via GET DIAGNOSTICS ROW_COUNT. Idempotent: a re-run touches nothing (no open rows remain).

C.2 Procedure & trigger table

C.3 Ordering & dependencies

• INGEST before RATE. Only CSTAT='E' rows are rated; a CDR must be inserted (and pass the veto trigger) before SP_RATE_BATCH will see it.
• RATE before INVOICE. SP_GEN_INVOICE aggregates TELUSAGE rows, which only exist after rating. Invoicing a customer before their CDRs are rated produces a flat-fee-only invoice (usage 0).
• INVOICE before FINALIZE. SP_FINALIZE only moves existing open invoices to finalized; it never generates one.
• Bundle order within rating is CDRID order. The batch cursor is ORDER BY CDRID, and the bundle-then-overage split consumes bundle in that order — so a customer's earlier-id calls draw bundle first and later calls tip into overage once the bundle is exhausted (the C000001 six-call walk across the 100-minute boundary demonstrates this exactly).

D. Data Files (data dictionary) ↑ top

All files are in library TELSQL, grounded in schema.sql. Rates and charges are DECIMAL; the packed CDR string is VARCHAR; timestamps are structured TIMESTAMP; periods are CHAR(6) YYYYMM.

TELPLAN — Rate-plan catalog (PK PLANCD)

Seeded plans: BAS 100min/.15/.08/.04/19.99/6%; PLUS 500min/.10/.06/.03/39.99/7.5%; UNLM 9999min/0/0/0/69.99/8.25%; ZERO 0min/.12/.09/.05/9.99/5%.

TELTOD — Time-of-day band table (PK BANDCD, HRSTART, WKND)

TELCUST — Customer master (PK CUSTNO)

TELCDR — Raw mediation CDR feed (PK CDRID)

TELUSAGE — Rated usage detail (PK CDRID)

TELREJ — Rejected-CDR log (identity RSEQ)

TELINVH — Invoice header (PK INVNO)

TELINVL — Invoice line detail (PK INVNO, ILINE)

TELAUD — Audit/trace log (identity ASEQ)

Relationships

• TELCUST.PLANCD → TELPLAN.PLANCD (plan lookup; matched on the exact CHAR(4), not a declared FK).
• TELCDR.CUSTNO → TELCUST.CUSTNO (rating resolves the customer & plan from the CDR).
• TELUSAGE.CDRID → TELCDR.CDRID (one rated row per CDR); TELUSAGE.CUSTNO carries the customer for aggregation.
• TELREJ.CDRID / TELINVL.INVNO reference their CDR / invoice by key (logs, no declared FK).
• Invoicing joins TELUSAGE⨯TELCDR on CDRID to bound usage by the CDR's CALLTS within the period.

E. Operations Runbook ↑ top

E.1 Run the rating & billing cycle

Pre-checks: confirm the job's library list includes TELSQL; confirm the plan and customer masters are loaded and that customer plan codes are at the exact CHAR(4) width (B.1). Then run the four steps in order.

1. Ingest. Let mediation feed CDRs into TELCDR (CSTAT='E'). The veto trigger screens each duration at insert; a rejected insert (75027/75028) means bad feed data — correct it upstream, it was never stored.
2. Rate. CALL TELSQL.SP_RATE_BATCH(?, ?, ?) and read the three OUT counts.
3. Invoice. For each customer, CALL TELSQL.SP_GEN_INVOICE(cust, period, ?, ?); the RC is GENERATED or SKIPPED.
4. Finalize. CALL TELSQL.SP_FINALIZE(period, ?) and confirm the finalized count.

Expected rating outcome on the seeded feed (test/tel_daily.mjs):
  SP_RATE_BATCH -> seen=16  rated=12  rejected=4
    12 good  = 6 (C000001) + 3 (C000002) + 1 (C000003) + 2 (C000004)
     4 bad   = 1 suspended (75024) + 3 malformed (75021 / 75022 / 75023)

Post-checks after rating:

• rated + rejected = seen, and every good CDR is now CSTAT='R', every bad one CSTAT='X' (never silently left at E).
• TELREJ holds exactly one row per rejected CDR with a reason matching its SQLSTATE (e.g. %not active% for 75024, %missing field% for 75021, %mismatch% for 75022, %unknown customer% for 75023).
• Each customer's CYCMIN equals the sum of bundle-applied minutes across their rated calls (C000001 ends at exactly 100, the bundle cap; a zero-bundle customer stays 0).
• A TELAUD RATE_BATCH row records the run's counts.

E.2 Reconciling figures

These are the same figures the volume battle checks against an independent hand-derived oracle (test/tel_daily.mjs). Reconcile them from the base tables, not from the invoice itself.

SELECT FLATFEE, USGCHG, SUBTOT, TAX, TOTAL
  FROM TELSQL.TELINVH WHERE CUSTNO = 'C000001' AND PERIOD = '202608';

• Per-call charge — CHARGE = OVERMIN × rate, rate from the nested CASE floored at the plan OVERRT. E.g. C000001 CDR0000004 (10 overage min, peak domestic, floored to .15) = 1.50; CDR0000005 (5 min, weekend, floored to .15) = 0.75; CDR0000006 (1 min, intl, .24) = 0.24.
• Usage total — USGCHG = SUM(TELUSAGE.CHARGE) over the customer's rated calls in the period. C000001: 1.50 + 0.75 + 0.24 = 2.49.
• Subtotal / tax / total — SUBTOT = FLATFEE + USGCHG; TAX = ROUND(SUBTOT×TAXPCT/100, 2); TOTAL = SUBTOT + TAX. C000001: 19.99 + 2.49 = 22.48; tax 22.48×6% = 1.3488→1.35; total 23.83.
• Bundle-only customers — a customer whose bundle is never exhausted has USGCHG = 0.00 (C000002 PLUS: 39.99/3.00/42.99; C000003 UNLM: 69.99/5.77/75.76).
• Zero-bundle customer — every minute is overage from call 1 (C000004 ZERO: 0.24 + 0.36 = 0.60 usage; 9.99→10.59→total 11.12).
• Grand-total invariant — SUM(TELINVH.TOTAL) for the period equals the independently derived sum of per-customer totals (seeded feed: 23.83 + 42.99 + 75.76 + 11.12).

E.3 Failure & re-run rules

The procedures surface outcomes through OUT parameters and application SQLSTATEs (F.5). Every step is built to be safely re-runnable.

F. Developer Reference ↑ top

The complete SQL-PL surface, from routines.sql and schema.sql. Every function, procedure and trigger is listed with its signature. All objects are in library TELSQL.

F.1 Functions (3)

FN_TODBAND (TS TIMESTAMP) RETURNS CHAR(1)

Time-of-day band classifier. A deeply nested searched CASE: weekend first (DAYOFWEEK(TS) IN (1,7), DB2 1=Sunday..7=Saturday → 'W'), else the hour band from EXTRACT(HOUR FROM TS) — 08≤HR<18 → 'P', otherwise 'O'. Encodes the TELTOD layout directly (D notes TELTOD is not read at runtime).

FN_CDRFLD (SRC VARCHAR(60), FLDNO INT) RETURNS VARCHAR(30)

Character-in-a-loop pipe-delimited field extractor for the packed CDRTXT. A WHILE loop walks the string one '|' at a time with POSITION/SUBSTR, returning the 1-based FLDNO'th field, or NULL if fewer than FLDNO fields exist; the last field (no trailing pipe) is handled specially. TRIMs stray whitespace. NULL source propagates to NULL.

FN_CEILMIN (SECS INT) RETURNS INT

Standard telco billing-increment rule: any partial minute bills as a full minute (WHOLE = SECS/60, +1 if MOD(SECS,60) > 0). Guards NULL or ≤0 seconds to return 0 (a no-call bills nothing; NULL never propagates).

F.2 Procedures (5)

SP_RATE_CDR (IN CDRIDIN CHAR(10))

Rate one CDR. Reads the CDR (not found → 75020); parses the four fields with FN_CDRFLD (any missing → 75021); validates the parsed duration is digits-only (via a TRANSLATE digits-to-spaces + TRIM/LENGTH=0 test, NULLIF-guarded for empty) and equals the structured CALLDUR (mismatch → 75022); resolves the customer (unknown → 75023) and requires CSTAT='A' (else 75024); classifies the band and minutes; performs the bundle-then-overage split; prices via the nested rate CASE floored at OVERRT; inserts TELUSAGE (fires TR_TELUSAGE_ACCR) and flips the CDR to 'R'.

SP_MARK_REJECT (IN CDRIDIN CHAR(10), IN REASONIN VARCHAR(60))

Inserts a TELREJ row and flips the CDR to CSTAT='X'. Called by SP_RATE_BATCH's CONTINUE handler, never directly in the happy path.

SP_RATE_BATCH (OUT RATEDOUT INT, OUT REJECTEDOUT INT, OUT SEENOUT INT)

The big rating loop. DECLARE CURSOR + OPEN + WHILE + FETCH over every CSTAT='E' CDR; a CONTINUE HANDLER FOR SQLEXCEPTION captures the message with GET DIAGNOSTICS, calls SP_MARK_REJECT, and counts the reject; an OKFLAG distinguishes a rated row from a caught one. Writes a TELAUD summary. See F.4 for why this uses the explicit-cursor idiom rather than a FOR loop.

SP_GEN_INVOICE (IN CUSTNOIN CHAR(7), IN PERIODIN CHAR(6), OUT INVNOOUT CHAR(13), OUT RCOUT VARCHAR(20))

Generate one invoice. Idempotency guard: existing invoice → return its number with RCOUT='SKIPPED'. Else unknown customer → 75025; derives period bounds via a labeled duration (PSTART + 1 MONTH); aggregates SUM(CHARGE)/count over the period; window-function cross-check (mismatch → 75026); computes subtotal/tax/total; writes TELINVH (ISTAT='O') + TELINVL (flat-fee line always, usage line when calls>0); TELAUD row; RCOUT='GENERATED'.

SP_FINALIZE (IN PERIODIN CHAR(6), OUT FINALIZEDOUT INT)

Updates all ISTAT='O' invoices for the period to 'F'; returns the count via GET DIAGNOSTICS ... = ROW_COUNT. Idempotent.

F.3 Triggers (2)

TR_TELCDR_VETO — BEFORE INSERT ON TELCDR (FOR EACH ROW, ATOMIC)

Ingest gate. N.CALLDUR IS NULL → SIGNAL 75027; N.CALLDUR < 0 OR > 86400 → SIGNAL 75028. A BEFORE trigger, so a SIGNAL aborts the INSERT before the row is stored.

TR_TELUSAGE_ACCR — AFTER INSERT ON TELUSAGE (FOR EACH ROW, ATOMIC)

Bundle accumulator. UPDATE TELCUST SET CYCMIN = CYCMIN + N.BUNDMIN WHERE CUSTNO = N.CUSTNO — adds only the bundle-applied minutes (not overage) to the customer's cycle-to-date counter. The customer row is guaranteed to pre-exist (SP_RATE_CDR already validated it).

F.4 SQL-PL patterns & the platform workaround

• String scan (FN_CDRFLD). The mediation-parser idiom: WHILE + POSITION('|' IN REMAIN) + SUBSTR, walking a packed record one delimiter at a time rather than assuming a fixed layout.
• Digits-only validation (SP_RATE_CDR). LENGTH(TRIM(TRANSLATE(s, REPEAT(' ', LENGTH(s)), '0123456789'))) = 0 maps every digit to a space; an empty result means the string was purely numeric. NULLIF(s,'') guards the empty-string edge.
• Bundle-then-overage split. REMAINBUND = MAX(0, BUNDMIN − CYCMIN); minutes up to the remainder are bundled, the rest overage.
• Nested rate CASE with a floor. A searched CASE over (call type, band), then RATE = GREATEST(RATE, OVERRT) expressed as a follow-up CASE.
• OLAP self-check. A SUM(...) OVER (ORDER BY ... ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) running total cross-checked against a plain aggregate SUM.
• GET DIAGNOSTICS. Used for both MESSAGE_TEXT (reject reason capture) and ROW_COUNT (finalize count / invoice DML count).
• Labeled duration. PSTART + 1 MONTH on a TIMESTAMP expression for period bounds (DB2 clamps a month-end rollover to the shorter month).

F.5 Application SQLSTATE table

SQLSTATE 75099 appears only in an isolated in-battle platform probe (SQLPL-PLAT-TEL-03), not in the application routines.

G. Glossary ↑ top

Band (time-of-day band)

The peak / off-peak / weekend classification of a call from its timestamp, driving the per-minute rate. Computed by FN_TODBAND; weekend overrides the hour band.

Billing increment

The rounding rule that any partial minute bills as a full minute (FN_CEILMIN) — standard telco billing.

Bundle (bundle minutes)

The plan's included minutes per cycle (BUNDMIN). Minutes are drawn from the bundle first; the remainder is overage. Consumption is tracked per customer in CYCMIN.

CDR — Call Detail Record

One raw record per call from the switch/mediation feed (TELCDR), carrying a packed field string plus a structured timestamp and duration.

CDR mediation

Taking a raw switch feed and preparing it for rating — here, parsing the packed CDRTXT, cross-checking the parsed duration against the structured value, and screening bad durations at ingest.

CONTINUE handler

An SQL/PSM exception handler that, after handling, resumes at the statement after the one that raised — used by SP_RATE_BATCH so a single bad CDR is logged and skipped rather than aborting the batch.

Cycle-to-date (CYCMIN)

The running total of bundle minutes a customer has consumed this billing cycle, accrued by TR_TELUSAGE_ACCR.

Finalize

Moving an invoice from open (ISTAT='O') to finalized ('F') so it is locked for the period (SP_FINALIZE).

Idempotent

Safe to run again with the same result. The rate batch (E-rows only), invoice generation (existing-invoice guard) and finalize (open-rows only) are all idempotent.

Overage

Minutes billed at a per-minute rate once the bundle is exhausted, priced by the nested rate CASE and never below the plan's contracted OVERRT floor.

Rating

Turning a raw CDR into a priced usage record: parse, band, minute-round, bundle/overage split, price, journal (SP_RATE_CDR / SP_RATE_BATCH).

SIGNAL / SQLSTATE

The SQL-PL mechanism for raising a business-rule error with a specific state code (F.5) and message text; caught by the batch's CONTINUE handler and logged to TELREJ.

SQL PL

DB2 for i's procedural SQL language (functions, procedures, triggers). TELSQL/i is written entirely in it, with no RPG orchestration layer.

STRSQL

Start SQL — the IBM i interactive SQL session an operator uses to CALL the procedures, since this application has no 5250 screen.

Window function (OLAP)

A SQL function computing over a window of rows (e.g. SUM(...) OVER (...)); used inside SP_GEN_INVOICE as an independent cross-check on the plain aggregate usage total.