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	Read	All columns
AUTHORIZATION_TYPE_CODE	Read (join)	CODE, CODE_DESCR, RECURRING_PAYMENT_FLAG
REVOKE_REASON_CODE	Read (left join)	CODE, CODE_DESCR
PAYMENT_OWNER.PAYMENT_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.*

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*

Business rules¶

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