SQL-045: clsEFTAuthorization retrieve (3 variants)¶

Retrieves a single EFT authorization record from PAYMENT_AUTHORIZATION with joined authorization type and revoke reason code descriptions. Supports three look-up keys.

Classification¶

Field	Value
SQL ID	SQL-045
Operation type	Read
Execution method	Inline SQL (Variants A, B); Stored procedure (Variant C)**
Database	Oracle*
Schema	[unqualified]**
Command type	Text / StoredProcedure**
Business object	`clsEFTAuthorization`
Criteria property	`RetrieveSQL`
Lifecycle method	`BeforeRetrieve / AfterRetrieve`

Parameters¶

Three mutually exclusive lookup variants; only the parameters for the chosen branch are bound.

Parameter	Direction	Type	Description
`:PaymentAuthorizationSeqNum`	Input	Decimal	Variant A: retrieves by payment authorization sequence number.
`:AutoPayConfirmationNum`	Input	String (25)	Variant B: retrieves by auto-pay confirmation number.
`:in_customer_type`	Input	String (5)	Variant C (stored procedure): customer type code input.
`:in_customer_id`	Input	String (20)	Variant C (stored procedure): customer ID input.
`:in_customer_second_id`	Input	String (20)	Variant C (stored procedure): customer secondary ID input.
`recordset`	Output (Cursor)	REF CURSOR	Variant C (stored procedure): result set returned from `PK72970_EPAY.retrieve_EFTAuth_ByCustomerID`.

Tables¶

Table	Access	Columns used
PAYMENT_AUTHORIZATION	Read	All columns
AUTHORIZATION_TYPE_CODE	Read (join)	CODE, CODE_DESCR
REVOKE_REASON_CODE	Read (left join)	CODE, CODE_DESCR

Sample SQL¶

Variant A — retrieve by payment authorization sequence number:

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 PAYMENT_AUTHORIZATION.PAYMENT_AUTHORIZATION_SEQ_NUM = :PaymentAuthorizationSeqNum

Variant B — retrieve by auto-pay confirmation number:

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 PAYMENT_AUTHORIZATION.AUTO_PAY_CONFIRMATION_NUM = :AutoPayConfirmationNum

Variant C — CustomerID branch (status: not-found / possibly dead code):**

PK72970_EPAY.retrieve_EFTAuth_ByCustomerID(
  :in_customer_type,
  :in_customer_id,
  :in_customer_second_id,
  OUT recordset
)

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\clsEFTAuthorization.vb