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	SELECT	All columns

Sample SQL

Variant A — retrieve by enrollment request sequence number:

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:

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*

Business rules

• 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