---
title: "SQL-034: CriteriaBase"
summary: Read statement for CriteriaBase.
status: draft
owner: SAIF Doc Gen
last_reviewed: 2026-07-28
library: SAIF.Business.Claim.Payment
reference_type: package
---

# SQL-034: CriteriaBase

This statement supports `CriteriaBase` by executing a Read operation through inline.

## Classification

| Field | Value |
| --- | --- |
| **SQL ID** | SQL-034 |
| **Operation type** | Read |
| **Execution method** | Inline SQL |
| **Database** | Oracle |
| **Schema** | [unqualified]** |
| **Command type** | Text |
| **Business object** | `CriteriaBase` |
| **Criteria property** | `RetrieveSQL` |
| **Lifecycle method** | `—` |

## Parameters

| Parameter | Direction | Type | Max length | Description |
| --- | --- | --- | --- | --- |
| `:BenefitTypeCode` | Input | Unknown | — | Always applied; filters by benefit type. |
| `:ClaimID` | Input | Unknown | — | Applied when `RetrieveBy` is `ClaimID` or `ClaimIDAndSchedule`. |
| `:ApprovedStatus` | Input | Unknown | — | Applied when `RetrieveBy` is `ClaimID` or `ClaimIDAndSchedule`; excludes/matches the approved benefit status. |
| `:PaymentID` | Input | Unknown | — | Applied when `RetrieveBy` is `PaymentID`. |

## Tables

| Table | Access | Columns used |
| --- | --- | --- |
| [CL_BENEFIT_PAYMENT](../database-tables.md#cl_benefit_payment) | Read | Not enumerated directly in the stage findings.** |
| `CL_BENEFIT_SCHEDULE` | Read | Not enumerated directly in the stage findings.** |

## Sample SQL

```sql
-- RetrieveSQL composes the SelectSQL column list with a FROM/WHERE clause
-- that varies by Me.RetrieveBy (RetrieveByOptions.ClaimID, .PaymentID, or
-- .ClaimIDAndSchedule). The base SELECT and the common WHERE condition are
-- shown below, followed by each RetrieveBy branch's additional condition.
SELECT
    CL_BENEFIT_PAYMENT.BENEFIT_PAYMENT_SEQ_NUM AS BenefitPaymentID,
    CL_BENEFIT_PAYMENT.CLAIM_ID AS ClaimID,
    CL_BENEFIT_PAYMENT.SAIF_AWARD_NUM AS AwardNumber,
    CL_BENEFIT_PAYMENT.ISSUE_DATE AS IssueDate,
    CL_BENEFIT_PAYMENT.INITIATOR_USER AS InitiatorUser,
    CL_BENEFIT_PAYMENT.APPROVER_USER AS ApproverUser,
    CL_BENEFIT_PAYMENT.REVOLVING_FUND_FLAG AS RevolvingFundFlag,
    CL_BENEFIT_PAYMENT.SETTLEMENT_TOTAL_AMT AS SettlementTotalAmount,
    CL_BENEFIT_PAYMENT.BENEFIT_STATUS_CODE AS BenefitStatusCode,
    CL_BENEFIT_PAYMENT.BENEFIT_TYPE_CODE AS BenefitTypeCode,
    CL_BENEFIT_PAYMENT.UPDATE_NUM AS UpdateNumber,
    CL_BENEFIT_PAYMENT.UPDATE_DATE AS UpdateDate,
    CL_BENEFIT_PAYMENT.UPDATE_USER AS UpdateUser,
    CL_BENEFIT_PAYMENT.DCS_FULL_PART_CODE AS DcsFullPartCode,
    CL_BENEFIT_PAYMENT.FEDERAL_TYPE_CODE AS FederalTypeCode,
    CL_BENEFIT_PAYMENT.LEGAL_ORDER_TYPE_CODE AS LegalOrderTypeCode,
    CL_BENEFIT_PAYMENT.ORDER_APPROVED_DATE AS OrderApprovedDate,
    CL_BENEFIT_PAYMENT.STRUCTURED_SETTLEMENT_FLAG AS StructuredSettlementFlag,
    CL_BENEFIT_PAYMENT.CHARGEABLE_COST_FLAG AS ChargeableCostFlag,
    CL_BENEFIT_PAYMENT.PENALTY_FLAG AS PenaltyFlag,
    CL_BENEFIT_PAYMENT.ADDITION_TO_COMP_FLAG AS AdditionTOCompFlag,
    CL_BENEFIT_PAYMENT.RUSH_CHECK_FLAG AS RushCheckFlag,
    CL_BENEFIT_PAYMENT.MONTHLY_STAT_AMT AS MonthlyStatAmount,
    CL_BENEFIT_PAYMENT.MONTHLY_RETRO_RESERVE_AMT AS MonthlyRetroReserveAmount,
    CL_BENEFIT_PAYMENT.MONTHLY_BENEFIT_AMT AS MonthlyBenefitAmount,
    CL_BENEFIT_PAYMENT.LAST_CALC_DATE AS LastCalcDate,
    CL_BENEFIT_PAYMENT.NEXT_FROM_DATE AS NextFromDate,
    CL_BENEFIT_PAYMENT.NEXT_THRU_DATE AS NextThruDate,
    CL_BENEFIT_PAYMENT.NEXT_ISSUE_DATE AS NextIssueDate
FROM CL_BENEFIT_PAYMENT
WHERE
    CL_BENEFIT_PAYMENT.BENEFIT_TYPE_CODE = :BenefitTypeCode
    -- RetrieveByOptions.ClaimID:
    --     AND CL_BENEFIT_PAYMENT.CLAIM_ID = :ClaimID
    --     AND CL_BENEFIT_PAYMENT.BENEFIT_STATUS_CODE <> :ApprovedStatus
    -- RetrieveByOptions.PaymentID:
    --     AND CL_BENEFIT_PAYMENT.BENEFIT_PAYMENT_SEQ_NUM = :PaymentID
    -- RetrieveByOptions.ClaimIDAndSchedule:
    --     AND CL_BENEFIT_PAYMENT.CLAIM_ID = :ClaimID
    --     AND (
    --         CL_BENEFIT_PAYMENT.BENEFIT_STATUS_CODE <> :ApprovedStatus
    --         OR CL_BENEFIT_PAYMENT.BENEFIT_PAYMENT_SEQ_NUM = (
    --             SELECT CL_BENEFIT_SCHEDULE.BENEFIT_PAYMENT_SEQ_NUM
    --             FROM CL_BENEFIT_SCHEDULE
    --             WHERE CL_BENEFIT_SCHEDULE.CLAIM_ID = :ClaimID
    --                 AND (
    --                     CL_BENEFIT_SCHEDULE.RELEASED_PAYMENT_COUNT > 0
    --                     OR CL_BENEFIT_SCHEDULE.REMAINING_PAYMENT_COUNT > 0
    --                 )
    --         )
    --     )
```

## Operations and processes

- [CriteriaBase SelectSQL](../operations-processes.md)

> **Note:** Statements marked with `**` could not be confirmed; treat them as unknown until verified.

## Sources

- `Business/SAIF.Business.Claim.Payment/Payments/clsPaymentBase.vb` (`SelectSQL` and `RetrieveSQL` properties)