---
title: "SQL-046: clsEFTAuthorizationCollection retrieve (dynamic WHERE)"
summary: Retrieves EFT authorization records from PAYMENT_AUTHORIZATION using a dynamically assembled WHERE clause built by BuildSQL() based on populated collection criteria.
status: draft
owner: SAIF
last_reviewed: 2026-07-24
library: SAIF.Business.Payment.EFT
reference_type: package
---

# SQL-046: clsEFTAuthorizationCollection retrieve (dynamic WHERE)

Retrieves EFT authorization records from PAYMENT_AUTHORIZATION using a dynamically assembled WHERE clause built by `BuildSQL()` based on populated collection criteria.

## Classification

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

## Parameters

All parameters are optional and added dynamically by `BuildSQL()` only when the corresponding collection criterion property is populated.

| Parameter | Direction | Type | Description |
| --- | --- | --- | --- |
| `:BankAccountID` | Input | Decimal | Filters by `BANK_ACCOUNT_SEQ_NUM` when set. |
| `:RevokeDateStart` | Input | Date | Filters `NVL(trunc(REVOKE_DATE), to_date('01-jan-2999')) >= :RevokeDateStart`. |
| `:RevokeDateEnd` | Input | Date | Filters `NVL(trunc(REVOKE_DATE), to_date('01-jan-0001')) <= :RevokeDateEnd`. |
| `:AuthDateStart` | Input | Date | Filters `NVL(trunc(AUTH_DATE), to_date('01-jan-2999')) >= :AuthDateStart`. |
| `:AuthDateEnd` | Input | Date | Filters `NVL(trunc(AUTH_DATE), to_date('01-jan-0001')) <= :AuthDateEnd`. |
| `:PrenoteFailDateStart` | Input | Date | Filters `NVL(trunc(PRENOTE_FAIL_DATE), to_date('01-jan-2999')) >= :PrenoteFailDateStart`. |
| `:PrenoteFailDateEnd` | Input | Date | Filters `NVL(trunc(PRENOTE_FAIL_DATE), to_date('01-jan-0001')) <= :PrenoteFailDateEnd`. |
| `:EnrollSeqNum` | Input | Decimal | Filters `PAYMENT_AUTHORIZATION.ENROLL_REQUEST_SEQ_NUM = :EnrollSeqNum` when set. |
| `:CustomerID` | Input | String (30) | Filters `PAYMENT_AUTHORIZATION.CUSTOMER_ID = :CustomerID` when set. |
| `:CustomerSecondID` | Input | String (30) | Filters `PAYMENT_AUTHORIZATION.Customer_Second_ID = :CustomerSecondID` when set. |
| `:CUST1` | Input | String (5) | Filters `customer_type_code = :CUST1` when exactly one customer type code is in the criteria. |
| `:CUST1`, `:CUST2` ... `:CUSTn` | Input | String (5) | Filters `customer_type_code IN (:CUST1, :CUST2, ...)` when multiple customer type codes are in the criteria. |
| `:AuthorizationTypeCode` | Input | String (5) | Filters `Authorization_Type_Code = :AuthorizationTypeCode` when set. |
| `:PaymentSourceSystemCode` | Input | String (5) | Filters `Payment_Source_System_Code = :PaymentSourceSystemCode` when set. |
| `:PrenoteRequiredFlag` | Input | String (1) | Filters `Prenote_Required_Flag = :PrenoteRequiredFlag` when set. |

## Tables

| Table | Access | Columns used |
| --- | --- | --- |
| [PAYMENT_AUTHORIZATION](../database-tables.md#payment_authorization) | Read | All columns |
| [AUTHORIZATION_TYPE_CODE](../database-tables.md#authorization_type_code) | Read (join) | CODE, CODE_DESCR, RECURRING_PAYMENT_FLAG |
| [REVOKE_REASON_CODE](../database-tables.md#revoke_reason_code) | Read (left join) | CODE, CODE_DESCR |
| [PAYMENT_OWNER.PAYMENT_DETAIL](../database-tables.md#payment_ownerpayment_detail) | Read (subquery, ActiveOnly path) | PAYMENT_AUTHORIZATION_SEQ_NUM, CURRENT_PAYMENT_EVENT_CODE |

## Sample SQL

`RetrieveSQL` returns the shared base `clsEFTAuthorization.Criteria.RETRIEVE_SQL`; `BeforeRetrieve` then calls `BuildSQL()` to append dynamic `WHERE` predicates and `ORDER BY auth_date DESC` based on populated collection criteria.*

```sql
Select PAYMENT_AUTHORIZATION.AUTHORIZATION_TYPE_CODE as AuthorizationTypeCode,
  PAYMENT_AUTHORIZATION.ENROLL_REQUEST_SEQ_NUM as EnrollRequestSeqNum,
  PAYMENT_AUTHORIZATION.AUTH_AGREEMENT_VERSION as AuthAgreementVersion,
  PAYMENT_AUTHORIZATION.AUTH_DATE as AuthDate,
  PAYMENT_AUTHORIZATION.BANK_ACCOUNT_SEQ_NUM as BankAccountSeqNum,
  PAYMENT_AUTHORIZATION.CREATE_DATE as CreateDate,
  PAYMENT_AUTHORIZATION.CREATE_IP_ADDR as CreateIPAddr,
  PAYMENT_AUTHORIZATION.CREATE_USER as CreateUser,
  PAYMENT_AUTHORIZATION.CUSTOMER_CONTACT_EMAIL_ADDR as CustomerContactEmailAddr,
  PAYMENT_AUTHORIZATION.CUSTOMER_CONTACT_NAME as CustomerContactName,
  PAYMENT_AUTHORIZATION.CUSTOMER_CONTACT_PHONE_NUM as CustomerContactPhoneNum,
  PAYMENT_AUTHORIZATION.CUSTOMER_ID as CustomerID,
  PAYMENT_AUTHORIZATION.Customer_Second_ID as CustomerSecondID,
  PAYMENT_AUTHORIZATION.CUSTOMER_TYPE_CODE as CustomerTypeCode,
  PAYMENT_AUTHORIZATION.PAYMENT_AUTHORIZATION_SEQ_NUM as PaymentAuthorizationSeqNum,
  PAYMENT_AUTHORIZATION.PAYMENT_DAY_OF_MONTH_NUM as PaymentDayOFMonthNum,
  PAYMENT_AUTHORIZATION.PAYMENT_SOURCE_SYSTEM_CODE as PaymentSourceSystemCode,
  PAYMENT_AUTHORIZATION.PRENOTE_FAIL_DATE as PrenoteFailDate,
  PAYMENT_AUTHORIZATION.PRENOTE_REQUIRED_FLAG as PrenoteRequiredFlag,
  PAYMENT_AUTHORIZATION.PRENOTE_SUCCESS_DATE as PrenoteSuccessDate,
  PAYMENT_AUTHORIZATION.REVOKE_REASON_CODE as RevokeReasonCode,
  PAYMENT_AUTHORIZATION.REVOKE_DATE as RevokeDate,
  PAYMENT_AUTHORIZATION.UPDATE_DATE as UpdateDate,
  PAYMENT_AUTHORIZATION.UPDATE_IP_ADDR as UpdateIPAddr,
  PAYMENT_AUTHORIZATION.UPDATE_USER as UpdateUser,
  PAYMENT_AUTHORIZATION.AUTO_PAY_CONFIRMATION_NUM as AutoPayConfirmationNum,
  AUTHORIZATION_TYPE_CODE.CODE_DESCR as AuthorizationPaymentFrequency,
  REVOKE_REASON_CODE.CODE_DESCR as RevokeReasonCodeDescr,
  PAYMENT_AUTHORIZATION.IAT_FLAG as IATFlag,
  PAYMENT_AUTHORIZATION.IAT_CITY as IATCity,
  PAYMENT_AUTHORIZATION.IAT_STREET_ADDRESS as IATStreetAddress,
  PAYMENT_AUTHORIZATION.IAT_STATE_PROVINCE as IATStateProvince,
  PAYMENT_AUTHORIZATION.IAT_POSTAL_CODE as IATPostalCode,
  PAYMENT_AUTHORIZATION.IAT_COUNTRY_CODE as IATCountryCode,
  PAYMENT_AUTHORIZATION.EMAIL_NOTIFICATION_FLAG as CustomerEmailNotificationFlag
From PAYMENT_AUTHORIZATION
  JOIN AUTHORIZATION_TYPE_CODE ON (PAYMENT_AUTHORIZATION.AUTHORIZATION_TYPE_CODE = AUTHORIZATION_TYPE_CODE.CODE)
  LEFT OUTER JOIN REVOKE_REASON_CODE ON (PAYMENT_AUTHORIZATION.REVOKE_REASON_CODE = REVOKE_REASON_CODE.CODE)
WHERE
  -- Each predicate below is appended only when the matching criterion property is populated:
  AND BANK_ACCOUNT_SEQ_NUM = :BankAccountID                                           -- BankAccountID criteria
  AND NVL(trunc(PAYMENT_AUTHORIZATION.Revoke_date), to_date('01-jan-2999')) >= :RevokeDateStart   -- RevokeDateStart criteria
  AND NVL(trunc(PAYMENT_AUTHORIZATION.Revoke_date), to_date('01-jan-0001')) <= :RevokeDateEnd     -- RevokeDateEnd criteria
  AND NVL(trunc(PAYMENT_AUTHORIZATION.Auth_date), to_date('01-jan-2999')) >= :AuthDateStart       -- AuthDateStart criteria
  AND NVL(trunc(PAYMENT_AUTHORIZATION.Auth_date), to_date('01-jan-0001')) <= :AuthDateEnd         -- AuthDateEnd criteria
  AND NVL(trunc(PAYMENT_AUTHORIZATION.Prenote_Fail_date), to_date('01-jan-2999')) >= :PrenoteFailDateStart  -- PrenoteFailDateStart criteria
  AND NVL(trunc(PAYMENT_AUTHORIZATION.Prenote_Fail_date), to_date('01-jan-0001')) <= :PrenoteFailDateEnd    -- PrenoteFailDateEnd criteria
  AND PAYMENT_AUTHORIZATION.ENROLL_REQUEST_SEQ_NUM = :EnrollSeqNum                    -- EnrollRequestSeqNum criteria
  AND PAYMENT_AUTHORIZATION.CUSTOMER_ID = :CustomerID                                 -- CustomerID criteria
  AND PAYMENT_AUTHORIZATION.Customer_Second_ID = :CustomerSecondID                    -- CustomerSecondID criteria
  AND PAYMENT_AUTHORIZATION.customer_type_code = :CUST1                               -- single CustomerTypeCode criteria
  -- OR: AND PAYMENT_AUTHORIZATION.customer_type_code IN (:CUST1, :CUST2, ...)        -- multiple CustomerTypeCode criteria
  AND PAYMENT_AUTHORIZATION.Authorization_Type_Code = :AuthorizationTypeCode          -- AuthorizationTypeCode criteria
  AND PAYMENT_AUTHORIZATION.Payment_Source_System_Code = :PaymentSourceSystemCode     -- PaymentSourceSystemCode criteria
  AND PAYMENT_AUTHORIZATION.Prenote_Required_Flag = :PrenoteRequiredFlag              -- PrenoteRequiredFlag criteria
  -- ActiveOnly=true appends:
  AND (  (  authorization_type_code.recurring_payment_flag = 'Y'
     AND PAYMENT_AUTHORIZATION.revoke_date IS NULL )
     OR (   authorization_type_code.recurring_payment_flag = 'N'
     AND EXISTS (
       SELECT 'X'
       FROM payment_owner.payment_detail d
       WHERE d.payment_authorization_seq_num = PAYMENT_AUTHORIZATION.payment_authorization_seq_num
         AND d.current_payment_event_code = 'PEND') ) )
  -- ActiveRecurringOnly=true appends:
  AND authorization_type_code.recurring_payment_flag = 'Y'
ORDER BY auth_date DESC
```

`BuildSQL()` assembles the WHERE clause by appending only the predicates whose corresponding criterion properties are populated; the `WHERE  AND` artifact is cleaned up to `WHERE` before execution.*

## Operations and processes
- [EFT authorization / consent](../operations/eft-authorization-consent.md)*

## Business rules
- [BR-EFT-01: EFT authorization gates electronic payment eligibility](../business-rules.md#br-eft-01-eft-authorization-gates-electronic-payment-eligibility)

> **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\clsEFTAuthorizationCollection.vb`