Regulatory Statistical Reporting Automation for Farmowners on IBM i (AS400) for a Wisconsin-Based Insurer

Introduction

A Wisconsin-based property and casualty insurer writing Farmowners business on IBM i (AS400) needed to meet the quarterly statistical reporting requirements of the AAIS Farmowners Statistical Plan (Line 33), filed through the AAIS Statistical Data Management Application (SDMA). AAIS is a licensed statistical agent that reports insurer data to state departments of insurance on the industry’s behalf.

While the client’s core policy and claims systems were stable and transaction-heavy, the plan demands a level of precision that is hard to sustain by hand: fixed-width 150-position premium and loss records, several records per policy, reversal records for cancellations and changes, and claim-level rules for losses. The objective was clear: build an accurate, auditable and repeatable quarterly reporting process directly from the system of record, without disrupting core operations, and with room to absorb future plan revisions.

The Challenge

Bridging Legacy Stability with Regulatory Precision

Premium Transaction Coding for a Multi-Record Policy Structure

The plan requires several statistical records for every Farmowners policy, each coded to exact field positions. Challenges included:

  • Splitting one policy into the right records: the primary dwelling and liability record, one record per farm structure, one per class of scheduled farm personal property, a blanket farm personal property record, and separate records for premium-bearing endorsements
  • Reporting cancellations as reversal records with negative premium, negative exposure, the months of cover remaining and the cancellation date as accounting date
  • Applying offset and on-set logic to mid-term policy changes, and allocating minimum premiums across coverage records
  • Reporting terms longer than 12 months as separate 12-month records, without creating extra records for installments
  • Populating fields conditionally, since several fields do not apply to certain policy form codes

Loss Transaction Coding with Claim-Level Rules

Loss records must reflect the policy conditions at the time of loss and carry claim-level logic that a simple table extract cannot provide. Key obstacles:

  • Carrying the endorsement’s policy form code when a claim is covered by a premium-bearing endorsement
  • Assigning claim counts consistently: 1 on a claim’s first loss record, 0 on every later record, and –1 when a claim closes without payment or is fully recovered
  • Reporting the full outstanding reserve at each quarter-end for as long as a claim stays open
  • Including salvage, subrogation and allocated loss adjustment expense (ALAE) while excluding reinsurance, unallocated expense and IBNR estimates
  • Choosing the cause of loss that started the chain of events when several appear to apply

Quarterly Submission, Validation and Correction Against Fixed Deadlines

Data must reach AAIS within 60 calendar days of quarter close, in a strictly defined file format. Challenges included:

  • Producing fixed-width records of exactly 150 positions, with no decimal points, zero-filled numeric fields and amounts signed in the low-order position under EBCDIC or ASCII rules
  • Reconciling the SDMA control totals (written premium, exposure, paid and outstanding loss, claim counts and record counts) before submitting
  • Working through error detail reports and submitting documented corrections within 10 business days of a “Ready for Review” status
  • Keeping code mappings current as AAIS revises the statistical plan

Our Solution: A Governed, Repeatable Regulatory Reporting Framework

Nalashaa implemented a client-aligned approach that prioritized business continuity: the reporting layer was built alongside the existing AS/400 environment rather than around it, so nothing in day-to-day policy and claims processing had to change.

The solution allowed the client to:

  • Continue leveraging the stability of their AS/400 system of record
  • Generate premium and loss records directly from source data, with no re-keying
  • Absorb future AAIS plan revisions through maintainable code tables instead of program rewrites
  • Address extraction, validation and submission support in a single, cohesive engagement

We implemented a structured reporting pipeline directly within IBM i using RPGLE and embedded SQL, delivered as three tasks: premium records, loss records, and validation and submission.

Task 1

Premium Record Extraction & Coding

SOLUTION HIGHLIGHTS

  • Built SQLRPGLE extraction of booked premium transactions by accounting month for the reporting quarter
  • Generated the correct set of records per policy: dwelling and liability, each farm structure, each class of scheduled farm personal property, blanket farm personal property, and premium-bearing endorsements
  • Automated cancellation reversals with negative premium and exposure, remaining months covered and the cancellation date as accounting date
  • Implemented offset and on-set processing for mid-term changes, and proportional allocation of minimum premiums
  • Applied “does not apply” defaults for fields that are not relevant to a given policy form code

TECHNICAL ENHANCEMENTS

  • Reference tables for AAIS codes (class, construction, fire protection, territory and policy form) so plan revisions are handled as data changes
  • Summarized farm-structure records where structures are rated as a group, with matching amount of insurance and exposure
  • Splitting of multi-year terms into 12-month records
  • Signed-amount encoding and zero-fill handled once, in a single reusable routine

Coding rule in practice: under the plan, a 36-month policy written on 3/5/94 is reported as three separate 12-month submissions, dated 3/5/94, 3/5/95 and 3/5/96, each with its own policy term effective date.

Task 2

Loss Record Extraction & Claim Logic

SOLUTION HIGHLIGHTS

  • Built SQLRPGLE program to extract paid loss, outstanding loss, paid ALAE and outstanding ALAE transactions
  • Carried the policy attributes in force at the time of loss, including the endorsement’s policy form code where an endorsement responds
  • Developed a claim-count engine: 1 on the first loss record, 0 thereafter, –1 for closure without payment or full recovery, and a fresh 1 for each additional class affected
  • Reported outstanding reserves in full at each quarter-end, using the last month of the quarter as accounting date
  • Applied inclusion and exclusion filters: salvage, subrogation and ALAE in; reinsurance, unallocated expense and IBNR out
  • Mapped each claim to the initiating cause of loss

Coding rule in practice: a claim with an outstanding record in 3/94, another in 6/94 and a payment in 7/94 is reported with claim counts of 1, 0 and 0.

Outcome: Loss experience could now be reported consistently every quarter, without manual claim-by-claim adjustment.

Task 3

Validation, Reconciliation & AAIS Submission

SOLUTION HIGHLIGHTS

  • Built a pre-submission validation program that checks field positions, zero-filled numerics, valid codes and applicability rules before anything is uploaded
  • Produced a control-total reconciliation report that mirrors the SDMA Processing Results (written premium, exposure, paid and outstanding loss, claim counts and record counts)
  • Generated submission-ready files of 150-position records in AAIS-accepted formats (.txt, with .zip and encrypted .pgp as options)
  • Defined an error-correction runbook covering error detail report triage, individual versus bulk corrections and documentation, sized to the 10-business-day window
  • Scheduled the quarterly job cycle against the 60-day submission deadline

BUSINESS ENABLEMENT

  • Faster, more predictable quarter-end statistical close
  • Reduced dependency on manual data preparation
  • Timely, consistent filings across all four quarters
  • Less rework from rejected or mis-coded records

Together, the three tasks form one end-to-end pipeline, from quarter close to AAIS acceptance (Figure 1).

Quarterly Farmowners reporting workflow from quarter close to AAIS acceptance
Figure 1: Quarterly Farmowners reporting workflow, from quarter close to AAIS acceptance

Architecture & Governance

Statistical reporting architecture on IBM i, from system of record to AAIS submission
Figure 2: Statistical reporting architecture on IBM i, from system of record to AAIS submission

What we delivered

From the client’s standpoint, the architecture delivered

  1. 01

    Trusted system of record

    AS/400 retained as the trusted system of record for policy, premium and claims data

  2. 02

    Decoupled reporting logic

    Reporting logic decoupled from core policy and claims programs, so plan changes stay inside the reporting layer

  3. 03

    One governed path

    A single, governed path from source transactions to AAIS submission

  4. 04

    Reconciliation-ready data

    Reconciliation-ready data, supporting the annual reconciliation of statistical data to statutory financial information that AAIS supports under the NAIC Statistical Handbook

  5. 05

    Coexistence as systems modernize

    An architecture that supports coexistence: the reporting layer keeps working as source systems modernize incrementally

This architecture enabled regulatory compliance without sacrificing reliability.

Technology and Controls

CORE TECHNOLOGIES

  • IBM i (AS400) RPGLE / SQLRPGLE
  • DB2 for i SQL views and reference tables for extraction and AAIS code mapping
  • Fixed-width, 150-position record builder with EBCDIC / ASCII signed-amount handling
  • IFS stream-file output for submission files (.txt, .zip, .pgp)
  • Automated job scheduling for the quarterly cycle

OPERATIONAL CONTROLS

  • Control totals reconciled to source before every upload
  • Error detail reports triaged, corrected and documented within the 10-business-day window
  • Parameterized runs by company code and accounting quarter
  • Restricted authority over output folders and submission files
  • Named verifier sign-off at submission, as the SDMA acknowledgement requires, and an audit trail of every filing

Business Impact & ROI

AREA IMPACT

Regulatory Timeliness

Repeatable quarterly cycle built around the 60-day filing deadline

Premium Reporting

Accurate multi-record coding of policies, cancellations and mid-term changes

Loss Reporting

Consistent claim-count and outstanding-reserve reporting across quarters

Data Quality

Pre-submission validation reduces record rejections and rework

Auditability

Reconciled control totals and documented corrections

Scalability

Table-driven design that extends to plan revisions and other AAIS statistical plans

Risk Mitigation

Lower exposure to late, rejected or inaccurate regulatory filings

Quantifiable Outcomes

Measured against the AAIS plan’s own yardsticks:

4Quarterly filings a year run through one repeatable process
60 daysEvery filing prepared against the 60-calendar-day submission deadline
2 × 150Record layouts, premium and loss, of 150 positions each, generated programmatically
EveryRecord validated before upload, with corrections aligned to the 10-business-day window

Strategic Value Delivered

This initiative demonstrates how legacy IBM i systems can meet demanding regulatory statistical reporting requirements without costly replatforming.

By building rule-driven extraction, validation and submission layers, the client now benefits from:

01

A scalable regulatory reporting framework

02

A strengthened compliance posture

03

Faster, more reliable quarter-end reporting

04

Reconciled, auditable statistical data

05

Future-ready adaptability to AAIS plan revisions

Looking Ahead

With a governed reporting foundation in place, the organization is now positioned to:

Extend the framework to additional AAIS statistical plans and lines of business

Absorb future plan revisions through table-driven updates

Automate statistical-to-statutory reconciliation

Add pre-submission data-quality dashboards

Modernize incrementally without business disruption

Complywithout replacing.

Reportwithout rework.

Deliverwithout compromise.

Let’s Talk

Ready to Automate Regulatory Reporting on IBM i?

Tell us about your AS400 statistical filings, validation gaps, and submission deadlines. We will help you build a repeatable reporting layer without replacing core operations.

Field will not be visible to web visitor