Skip to content

Dynamic balance calculation

Some balance types (e.g., a policy's currently-owed premium balance) cannot be computed from static columns alone and instead require executing a database-side calculation. clsBalanceTypeCode.IsDynamicallyCalculated reports whether a given balance type requires this approach (true whenever its GET_BALANCE_SQL_TXT column is non-empty), and DynamicallyCalculatedBalanceInfoFactory is the object that actually performs the calculation.

Field Value
Operation ID OP-EFT-09
Trigger Caller asks DynamicallyCalculatedBalanceInfoFactory to compute the balance for a policy, given a clsBalanceTypeCode whose IsDynamicallyCalculated is True
Inputs Policy number (inpolicynum), balance type code
Outputs A DynamicallyCalculatedBalanceInfo object holding the computed balance amount, net of pending online-payment allocations
Primary components clsBalanceTypeCode.IsDynamicallyCalculated, DynamicallyCalculatedBalanceInfoFactory, DynamicallyCalculatedBalanceInfo
Entry point Business\SAIF.Business.Payment.EFT\DynamicallyCalculatedBalanceInfoFactory.vb

Steps

  1. Determine whether the balance type is dynamically calculated. clsBalanceTypeCode.IsDynamicallyCalculated returns True whenever the code table's GET_BALANCE_SQL_TXT column for that balance type is non-blank (SQL-029, BALANCE_TYPE_CODE).
  2. Execute the balance calculation as a stored procedure — never inline SQL. DynamicallyCalculatedBalanceInfoFactory always issues the GET_BALANCE_SQL_TXT value with CommandType.StoredProcedure (hard-coded, regardless of the text's actual contents), passing a single input parameter inpolicynum and reading the result through an output cursor parameter named recordset.
  3. The exact calculation logic executed by the stored procedure is owned by the database package referenced in GET_BALANCE_SQL_TXT and cannot be traced from this library's source code alone.*
  4. Net the raw balance against pending allocations. The raw amount returned by the stored procedure is reduced by the sum of pending, non-warehoused online-payment allocations whose DynamicBalanceSeqNum matches the balance type — i.e., payments already scheduled/allocated against this balance are subtracted so the caller sees the true remaining amount still owed, not the gross balance before in-flight payments are accounted for. This uses the allocation data maintained in Payment detail, events, and allocation.
  5. Return the computed balance. The factory returns a DynamicallyCalculatedBalanceInfo instance carrying the net amount and the balance-type code it was computed for.

Process flow

flowchart TD
    Start([Caller requests balance<br>for a policy]) --> Check{IsDynamicallyCalculated?}
    Check -->|No| Skip([Use static balance column instead;<br>not part of this operation])
    Check -->|Yes| Exec[Execute GET_BALANCE_SQL_TXT<br>as stored procedure]
    Exec --> Raw[Receive raw balance via<br>output cursor 'recordset']
    Raw --> Net[Subtract pending non-warehoused<br>online-payment allocations]
    Net --> Done([Return DynamicallyCalculatedBalanceInfo])

Failure and edge-case behavior

  • The exact SQL/PL-SQL logic inside the stored procedure identified by GET_BALANCE_SQL_TXT is outside this library's own source and cannot be verified or documented from this codebase alone.*
  • Because CommandType.StoredProcedure is hard-coded regardless of the actual text in GET_BALANCE_SQL_TXT, if that column were ever populated with literal inline SQL text rather than a callable procedure/package name, the call would fail at execution time — the factory does not detect or handle this mismatch.*
  • The netting step depends on clsPaymentAllocation data being current and matched correctly by DynamicBalanceSeqNum; a payment allocation that is mis-tagged with the wrong balance-type reference would cause the computed balance to be incorrect without raising an error.*

Note: Statements marked with * were inferred from code structure, naming conventions, or contextual evidence where direct confirmation was not available in source artifacts.

Sources

  • Business\SAIF.Business.Payment.EFT\DynamicallyCalculatedBalanceInfoFactory.vb
  • Business\SAIF.Business.Payment.EFT\DynamicallyCalculatedBalanceInfo.vb
  • Business\SAIF.Business.Payment.EFT\clsBalanceTypeCode.vb