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	Read	Not enumerated directly in the stage findings.**
CL_BENEFIT_SCHEDULE	Read	Not enumerated directly in the stage findings.**

Sample 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

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)