SQL-004: clsProviderBankAccountCollection

SELECT provider bank accounts by provider.

Classification

Field	Value
SQL ID	SQL-004
Operation type	Read
Execution method	Inline SQL
Database	Oracle
Schema	Unqualified in SQL text
Command type	Text
Business object	clsProviderBankAccountCollection
Criteria property	N/A — direct SQL
Lifecycle method	GetProviderBankAccounts

Parameters

Parameter	Direction	Type	Max length	Description
:ProviderSeqNum	Input	String	30	Provider identifier converted to upper-case text before SqlHelper.NewParm.

Tables

Table	Access	Columns used
BANK_ACCOUNT	READ	See sample SQL.
PAYMENT_AUTHORIZATION	READ	See sample SQL.
PROVIDER_LOCATION	READ	See sample SQL.

Sample SQL

SELECT
    BA.BANK_ACCOUNT_SEQ_NUM AS BankAccountSeqNum
FROM BANK_ACCOUNT BA
WHERE BA.BANK_ACCOUNT_STATUS_CODE <> 'DELET'
  AND BA.BANK_ACCOUNT_SEQ_NUM IN (
    SELECT PAY.BANK_ACCOUNT_SEQ_NUM
    FROM PAYMENT_AUTHORIZATION PAY
    WHERE PAY.CUSTOMER_ID IN (
        SELECT TO_CHAR(PROVIDER_LOCATION_SEQ_NUM)
        FROM PROVIDER_LOCATION
        WHERE PROVIDER_SEQ_NUM = :ProviderSeqNum
    )
)

Operations and processes

• Provider bank account retrieval

Business rules

• BR-14: Deleted bank accounts are excluded from provider bank-account retrieval.

Sources

• Business\SAIF.Business.Provider.PaymentPreference\clsProviderBankAccounts.vb