Skip to content

Bill payment adjustment and payment deletion

This operation covers two small, related utility classes: clsBillPaymentAdjustment, a simple insert-only record linking a refund or transfer transaction back to the audited bill service line it adjusted, and clsPaymentDeleter, which groups a claim's released payments into deletable sets and removes them (and everything related) from CL_FINANCIAL_TRANS in a single statement.

Field Value
Operation ID OP-CP-07
Trigger clsBillPaymentAdjustment.NewBillPaymentAdjustment(dpi) (called from Refund adjustment processing); New clsPaymentDeleter(dpi, claimID) followed by DeletePayments(payments)
Inputs Financial transaction sequence number and type, bill ID, audit sequence number, claim ID, adjustment amount (bill adjustment); claim ID and a selected set of DeletablePayment candidates (deletion)
Outputs Inserted CL_FINANCIAL_TRANS_BILL_ADJUST row; deleted CL_FINANCIAL_TRANS rows for the selected and related payments; a clsResultInfo(Of Boolean) result with a message
Primary components clsBillPaymentAdjustment, clsPaymentDeleter, DeletablePayment
Entry point Business\SAIF.Business.Claim.Payment\Adjustments\clsBillPaymentAdjustment.vb

Steps

Bill payment adjustment

  1. Create the adjustment record. NewBillPaymentAdjustment creates the object through the framework data portal and attaches update-tracking metadata.
  2. Validate the reference fields. Six field-level rules require a positive financial transaction sequence number, a financial transaction type code of Refund or Transfer only, a positive bill ID, a positive audit sequence number, a positive claim ID, and a non-negative adjustment amount (BR-CP-026).
  3. Insert the adjustment (SQL-017, CL_FINANCIAL_TRANS_BILL_ADJUST). The row records the adjustment amount and the referencing financial transaction; it does not modify the original bill, audit, or financial-transaction rows.
  4. On a rows-affected value other than 1: "Insert Failed - BillPaymentAdjustment" error added.
  5. No update, delete, or retrieve path exists. Criteria.CreateSQL/DeleteSQL/RetrieveSQL/UpdateSQL all throw ApplicationException(InvalidSQLProperty) except the insert statement — bill payment adjustments are permanent, insert-only records read back only through clsBillPaymentAdjustments.GetBillPaymentAdjustments (used by Refund adjustment processing).

Payment deletion

  1. Load candidate payments. The constructor retrieves every Released financial transaction for the claim and classifies each into one of five payment types — Award, Benefit, Transfer, EAIP, or Other — based on which identifying field (AwardNum, BenefitPaymentSubNum, TransferTransID, ExpenseID) is populated, in that precedence order.
  2. Resolve related payments. GetRelatedPayments expands a single selected payment to every payment sharing its grouping key: same BenefitPaymentID for Award/Benefit types, same transfer/financial-transaction ID pairing for Transfer, same ExpenseID for EAIP, and — for Other — same payment-transaction-type code and issue date when the transaction type is Settlement, or just the same financial transaction ID otherwise.
  3. Validate before deleting. PreDeleteValidation requires at least one payment selected, requires that the selected set already equals its own fully-expanded related-payment set (i.e., the caller cannot delete part of a related group), and requires every selected payment to still be in the Released financial-transaction status (BR-CP-027).
  4. On failure: a clsResultInfo(Of Boolean) is returned immediately with a message ("No payments selected for delete.", "...didn't include all related payments.", or "...no longer in a released state...") and no database call is made.
  5. Re-check released state immediately before deleting (BR-CP-027). PaymentsStillInReleasedState re-queries CL_FINANCIAL_TRANS (uncatalogued inline scalar count, not in the SQL index) to confirm the count of still-Released rows equals the number of payments requested, immediately before the delete statement runs.
  6. Delete the transactions. A single inline DELETE FROM cl_financial_trans WHERE (claim_financial_trans_seq_num IN (...) OR transfer_trans_id IN (...)) AND financial_trans_status_code = '1' removes every selected transaction and any transaction that points back to one of them via transfer_trans_id, but only while still Released.
  7. On success (deleted row count ≥ requested count): result True, "Payments deleted successfully."
  8. Otherwise: result False, "Delete failed - has the check process started?" — the message itself hints that the most common cause is a payment that has advanced past Released between the check and the delete.

Process flow

flowchart TD
    Start([Refund/transfer adjustment saves,<br>or adjuster requests payment deletion]) --> Which{Bill adjustment<br>or deletion?}
    Which -->|Bill adjustment| ValidateFields{Reference fields valid?}
    ValidateFields -->|No| BlockedAdj([Broken rule; insert blocked])
    ValidateFields -->|Yes| InsertAdj[Insert CL_FINANCIAL_TRANS_BILL_ADJUST]
    InsertAdj --> RowsAdj{RowsAffected = 1?}
    RowsAdj -->|No| FailAdj([Insert Failed])
    RowsAdj -->|Yes| DoneAdj([Adjustment recorded])
    Which -->|Deletion| Load[Load Released transactions;<br>classify into payment groups]
    Load --> Expand[Expand selection to<br>all related payments]
    Expand --> PreCheck{Full group selected<br>and still Released?}
    PreCheck -->|No| BlockedDel([Result False with message;<br>no database call])
    PreCheck -->|Yes| Recheck{Still Released<br>immediately before delete?}
    Recheck -->|No| BlockedDel
    Recheck -->|Yes| Delete[Delete transactions and<br>linked transfer pairs]
    Delete --> RowsDel{Deleted count >= requested?}
    RowsDel -->|No| FailDel([Result False:<br>Delete failed])
    RowsDel -->|Yes| DoneDel([Result True: Payments deleted])

Failure and edge-case behavior

  • The pre-delete validation and the immediately-following delete statement are two separate database round-trips; a payment could theoretically advance past Released between them, which is exactly the race the delete statement's own financial_trans_status_code = '1' predicate guards against (an affected payment is simply skipped, dropping the deleted count below the requested count and producing the generic "Delete failed" message).
  • Deletion is a hard DELETE, not a status change or reversal — there is no audit trail of what was removed beyond whatever database-level auditing exists outside this library.
  • clsBillPaymentAdjustment records are permanent once inserted; there is no way to update or remove one through this class if it was created in error.
  • PaymentsStillInReleasedState's scalar-count query has no catalogued SQL entry in this library's SQL index and is described here in prose rather than linked.

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.Claim.Payment\Adjustments\clsBillPaymentAdjustment.vb
  • Business\SAIF.Business.Claim.Payment\Adjustments\clsBillPaymentAdjustments.vb
  • Business\SAIF.Business.Claim.Payment\Adjustments\clsPaymentDeleter.vb