SQL-008: Retrieve US Bank user by policy and business-online user

Retrieve a single user row using policy number plus case-insensitive business-online user matching.

Classification

Field	Value
SQL ID	SQL-008
Operation type	Read
Execution method	Inline SQL
Database	Oracle
Schema	Unqualified in library SQL
Command type	Text
Business object	clsUSBankUser
Criteria property	RetrieveSQL
Lifecycle method	BeforeRetrieve

Parameters

Parameter	Direction	Type	Max length	Description
:PolicyNum	Input	Decimal	N/A	Policy filter used by GetUSBankUser(inPolicyNumber, inBusinessOnlineUserId).
:BusinessOnlineUser	Input	String	30	Case-insensitive Business Online user filter via UPPER(:BusinessOnlineUser).

Tables

Table	Access	Columns used
US_BANK_USER	SELECT	POLICY_NUM, BUSINESS_ONLINE_USER, FIRST_NAME, LAST_NAME, TRANSFER_BANK_ACCOUNTS_FLAG

Sample SQL

SELECT
    US_BANK_USER.US_BANK_USER_SEQ_NUM AS USBankUserSeqNum,
    US_BANK_USER.POLICY_NUM AS PolicyNum,
    US_BANK_USER.BUSINESS_ONLINE_USER AS BusinessOnlineUser,
    US_BANK_USER.FIRST_NAME AS FirstName,
    US_BANK_USER.LAST_NAME AS LastName,
    US_BANK_USER.TRANSFER_BANK_ACCOUNTS_FLAG AS TransferBankAccountsFlag
FROM US_BANK_USER
WHERE US_BANK_USER.POLICY_NUM = :PolicyNum
  AND UPPER(US_BANK_USER.BUSINESS_ONLINE_USER) = UPPER(:BusinessOnlineUser)

Operations and processes

• Register or update a US Bank user
• Persist and retrieve SAIF-side integration records

Business rules

• BR-009: User lookup is case-insensitive

Sources

• Business\SAIF.Business.EPay.USBank\clsUSBankUser.vb