SQL-012: Existing benefit-payment search¶

This document describes SQL-012, the existing benefit-payment search statement or call used by SAIF.Business.

Classification¶

Field	Value
SQL ID	SQL-012
Operation type	Read
Execution method	Inline
Database	Oracle
Schema	[unqualified]**
Command type	Text
Business object	`ClaimServices`
Criteria property	`N/A`
Lifecycle method	`HasExistingBenefitPayment`

Parameters¶

Parameter	Direction	Type	Max length	Description
`:ClaimID`, `:PaymentCategory`, `:TransactionAmount`, `:IssueDate`	Input	Mixed	1 / 11 / N/A	Benefit-payment existence search; amount/date only for DCS partial branch

Tables¶

Table	Access	Columns used
CL_FINANCIAL_TRANS	SELECT	See sample SQL and source implementation.
CL_FINANCIAL_TRANS_DTL	SELECT	See sample SQL and source implementation.

Sample SQL¶

SELECT COUNT(*)
  FROM cl_financial_trans trn
 WHERE trn.claim_id = :ClaimID
   AND financial_trans_type_code = '1'
   AND EXISTS (
       SELECT 1
         FROM cl_financial_trans_dtl dtl
        WHERE dtl.claim_financial_trans_seq_num = trn.claim_financial_trans_seq_num
          AND dtl.payment_category_code = :PaymentCategory
   )

Runtime branches append DCS full/partial or legal-order predicates.

Operations and processes¶

Claim authorization and visibility checks

Business rules¶

BR-SB-010: Benefit-payment history checks map business context to payment-category filters

Note: Statements marked with * were inferred from code structure, naming conventions, or contextual evidence where direct confirmation was not available in source artifacts. Statements marked with ** could not be confirmed; treat as unknown until verified.

Sources¶

Business\SAIF.Business\ClaimServices.vb