---
title: "SQL-043: clsDebitCardEnrollmentRequest retrieve"
summary: Retrieves a debit card enrollment request, optionally filtering out terminated enrollments.
status: draft
owner: SAIF
last_reviewed: 2026-07-24
library: SAIF.Business.Payment.EFT
reference_type: package
---

# SQL-043: clsDebitCardEnrollmentRequest retrieve

Retrieves a debit card enrollment request, optionally filtering out terminated enrollments.

## Classification

| Field | Value |
| --- | --- |
| **SQL ID** | SQL-043 |
| **Operation type** | Read |
| **Execution method** | Inline SQL |
| **Database** | Oracle* |
| **Schema** | [unqualified]** |
| **Command type** | Text |
| **Business object** | `clsDebitCardEnrollmentRequest` |
| **Criteria property** | `RetrieveSQL` |
| **Lifecycle method** | `BeforeRetrieve / AfterRetrieve` |

## Parameters

`BeforeRetrieve` builds the WHERE clause conditionally based on populated criteria properties.

| Parameter | Direction | Type | Description |
| --- | --- | --- | --- |
| `:EnrollRequestSeqNum` | Input | Decimal | When populated (> 0), retrieves by enrollment request sequence number; mutually exclusive with the cardholder ID branch. |
| `:CardholderIDNum` | Input | Decimal | Used in the cardholder ID branch when `EnrollRequestSeqNum` is not populated. |
| `:IncludeTerminatedEnrollment` | Input | String (1) | `'Y'` to include records where `CARD_TERM_DATE IS NOT NULL`; any other value excludes terminated enrollments. Used only in the cardholder ID branch. |

## Tables

| Table | Access | Columns used |
| --- | --- | --- |
| [DEBIT_CARD_ENROLLMENT_REQUEST](../database-tables.md#debit_card_enrollment_request) | SELECT | All columns |

## Sample SQL

**Variant A — retrieve by enrollment request sequence number:**

```sql
Select DEBIT_CARD_ENROLLMENT_REQUEST.ENROLL_REQUEST_SEQ_NUM as EnrollRequestSeqNum,
  DEBIT_CARD_ENROLLMENT_REQUEST.ENROLL_STATUS_CODE as EnrollStatusCode,
  DEBIT_CARD_ENROLLMENT_REQUEST.NEW_ENROLL_FLAG as NewEnrollFlag,
  DEBIT_CARD_ENROLLMENT_REQUEST.CARDHOLDER_ID_NUM as CardholderIDNum,
  DEBIT_CARD_ENROLLMENT_REQUEST.FULL_NAME as FullName,
  DEBIT_CARD_ENROLLMENT_REQUEST.SECOND_FULL_NAME as SecondFullName,
  DEBIT_CARD_ENROLLMENT_REQUEST.CLAIM_ADDRESS_TYPE_CODE as ClaimAddressTypeCode,
  DEBIT_CARD_ENROLLMENT_REQUEST.CLAIM_ADDRESS_UPDATE_DATE as ClaimAddressUpdateDate,
  DEBIT_CARD_ENROLLMENT_REQUEST.CLAIM_PERSON_UPDATE_DATE as ClaimPersonUpdateDate,
  DEBIT_CARD_ENROLLMENT_REQUEST.ADDRESS_LINE1 as AddressLine1,
  DEBIT_CARD_ENROLLMENT_REQUEST.ADDRESS_LINE2 as AddressLine2,
  DEBIT_CARD_ENROLLMENT_REQUEST.CITY_NAME as CityName,
  DEBIT_CARD_ENROLLMENT_REQUEST.STATE_CODE as StateCode,
  DEBIT_CARD_ENROLLMENT_REQUEST.ZIP_CODE as ZipCode,
  DEBIT_CARD_ENROLLMENT_REQUEST.ZIP_CODE_EXT as ZipCodeExt,
  DEBIT_CARD_ENROLLMENT_REQUEST.ISO_COUNTRY_CODE as IsoCountryCode,
  DEBIT_CARD_ENROLLMENT_REQUEST.HOME_PHONE_NUM as HomePhoneNum,
  DEBIT_CARD_ENROLLMENT_REQUEST.BIRTH_DATE as BirthDate,
  DEBIT_CARD_ENROLLMENT_REQUEST.PRIMARY_TAX_ID as PrimaryTaxID,
  DEBIT_CARD_ENROLLMENT_REQUEST.PARTICIPANT_ID as ParticipantID,
  DEBIT_CARD_ENROLLMENT_REQUEST.CARD_TERM_DATE as CardTerminationDate,
  DEBIT_CARD_ENROLLMENT_REQUEST.CARD_ENROLL_DATE as CardEnrollDate,
  DEBIT_CARD_ENROLLMENT_REQUEST.CARD_ACTIVE_DATE as CardActiveDate,
  DEBIT_CARD_ENROLLMENT_REQUEST.CREATE_DATE as CreateDate,
  DEBIT_CARD_ENROLLMENT_REQUEST.CREATE_USER as CreateUser,
  DEBIT_CARD_ENROLLMENT_REQUEST.UPDATE_DATE as UpdateDate,
  DEBIT_CARD_ENROLLMENT_REQUEST.UPDATE_USER as UpdateUser,
  DEBIT_CARD_ENROLLMENT_REQUEST.UPDATE_SEQ_NUM as UpdateSeqNum
From DEBIT_CARD_ENROLLMENT_REQUEST
WHERE ENROLL_REQUEST_SEQ_NUM = :EnrollRequestSeqNum
```

**Variant B — retrieve by cardholder ID with optional terminated-enrollment filter:**

```sql
Select DEBIT_CARD_ENROLLMENT_REQUEST.ENROLL_REQUEST_SEQ_NUM as EnrollRequestSeqNum,
  DEBIT_CARD_ENROLLMENT_REQUEST.ENROLL_STATUS_CODE as EnrollStatusCode,
  DEBIT_CARD_ENROLLMENT_REQUEST.NEW_ENROLL_FLAG as NewEnrollFlag,
  DEBIT_CARD_ENROLLMENT_REQUEST.CARDHOLDER_ID_NUM as CardholderIDNum,
  DEBIT_CARD_ENROLLMENT_REQUEST.FULL_NAME as FullName,
  DEBIT_CARD_ENROLLMENT_REQUEST.SECOND_FULL_NAME as SecondFullName,
  DEBIT_CARD_ENROLLMENT_REQUEST.CLAIM_ADDRESS_TYPE_CODE as ClaimAddressTypeCode,
  DEBIT_CARD_ENROLLMENT_REQUEST.CLAIM_ADDRESS_UPDATE_DATE as ClaimAddressUpdateDate,
  DEBIT_CARD_ENROLLMENT_REQUEST.CLAIM_PERSON_UPDATE_DATE as ClaimPersonUpdateDate,
  DEBIT_CARD_ENROLLMENT_REQUEST.ADDRESS_LINE1 as AddressLine1,
  DEBIT_CARD_ENROLLMENT_REQUEST.ADDRESS_LINE2 as AddressLine2,
  DEBIT_CARD_ENROLLMENT_REQUEST.CITY_NAME as CityName,
  DEBIT_CARD_ENROLLMENT_REQUEST.STATE_CODE as StateCode,
  DEBIT_CARD_ENROLLMENT_REQUEST.ZIP_CODE as ZipCode,
  DEBIT_CARD_ENROLLMENT_REQUEST.ZIP_CODE_EXT as ZipCodeExt,
  DEBIT_CARD_ENROLLMENT_REQUEST.ISO_COUNTRY_CODE as IsoCountryCode,
  DEBIT_CARD_ENROLLMENT_REQUEST.HOME_PHONE_NUM as HomePhoneNum,
  DEBIT_CARD_ENROLLMENT_REQUEST.BIRTH_DATE as BirthDate,
  DEBIT_CARD_ENROLLMENT_REQUEST.PRIMARY_TAX_ID as PrimaryTaxID,
  DEBIT_CARD_ENROLLMENT_REQUEST.PARTICIPANT_ID as ParticipantID,
  DEBIT_CARD_ENROLLMENT_REQUEST.CARD_TERM_DATE as CardTerminationDate,
  DEBIT_CARD_ENROLLMENT_REQUEST.CARD_ENROLL_DATE as CardEnrollDate,
  DEBIT_CARD_ENROLLMENT_REQUEST.CARD_ACTIVE_DATE as CardActiveDate,
  DEBIT_CARD_ENROLLMENT_REQUEST.CREATE_DATE as CreateDate,
  DEBIT_CARD_ENROLLMENT_REQUEST.CREATE_USER as CreateUser,
  DEBIT_CARD_ENROLLMENT_REQUEST.UPDATE_DATE as UpdateDate,
  DEBIT_CARD_ENROLLMENT_REQUEST.UPDATE_USER as UpdateUser,
  DEBIT_CARD_ENROLLMENT_REQUEST.UPDATE_SEQ_NUM as UpdateSeqNum
From DEBIT_CARD_ENROLLMENT_REQUEST
WHERE CARDHOLDER_ID_NUM = :CardholderIDNum
AND ((:IncludeTerminatedEnrollment = 'N' AND CARD_TERM_DATE IS NULL)
  OR :IncludeTerminatedEnrollment <> 'N')
```

## Operations and processes
- [Debit card enrollment](../operations/debit-card-enrollment.md)*

## Business rules
- [BR-EFT-06: Debit card enrollment excludes terminated enrollments by default](../business-rules.md#br-eft-06-debit-card-enrollment-excludes-terminated-enrollments-by-default)

> **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.Payment.EFT\clsDebitCardEnrollmentRequest.vb`