SQL-012: clsProviderEFTCollection¶

SELECT provider EFT locations with external-user email.

Classification¶

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

Parameters¶

Parameter	Direction	Type	Max length	Description
`:inProviderID`	Input	String	30	Provider identifier used to retrieve location-level EFT email addresses.

Tables¶

Table	Access	Columns used
PROVIDER_LOCATION	READ	See sample SQL.
PROVIDER_EFT_INTERFACE	READ	See sample SQL.
EXT_USER_TYPE	READ	See sample SQL.
EXT_USER	READ	See sample SQL.
EMAIL_ADDRESS	READ	See sample SQL.

Sample SQL¶

SELECT
    LOC.PROVIDER_SEQ_NUM AS ProviderSeqNum,
    LOC.PROVIDER_LOCATION_SEQ_NUM AS ProviderLocationSeqNum,
    CASE WHEN EFT.UPDATE_USER IS NULL THEN 'Y' ELSE 'N' END AS IsNewEFTFlag,
    EFT.EXT_USER_TYPE_SEQ_NUM AS ExtUserTypeSeqNum,
    ADDR.EMAIL_ADDRESS AS ExtUserEmailAddr,
    EFT.EFF_DATE AS EffDate,
    EFT.EXP_DATE AS ExpDate,
    CASE WHEN EFT.DEFAULT_TO_PRIMARY_FLAG IS NULL THEN 'N' ELSE EFT.DEFAULT_TO_PRIMARY_FLAG END AS DefaultToPrimaryFlag,
    EFT.UPDATE_DATE AS UpdateDate,
    EFT.UPDATE_NUM AS UpdateNum,
    EFT.UPDATE_USER AS UpdateUser
FROM PROVIDER_LOCATION LOC
LEFT OUTER JOIN PROVIDER_EFT_INTERFACE EFT
    ON LOC.PROVIDER_LOCATION_SEQ_NUM = EFT.PROVIDER_LOCATION_SEQ_NUM
LEFT OUTER JOIN EXT_USER_TYPE EUT
    ON EUT.EXT_USER_TYPE_SEQ_NUM = EFT.EXT_USER_TYPE_SEQ_NUM
LEFT OUTER JOIN EXT_USER EUSER
    ON EUSER.EXT_USER_SEQ_NUM = EUT.EXT_USER_SEQ_NUM
LEFT OUTER JOIN EMAIL_ADDRESS ADDR
    ON ADDR.EMAIL_ADDRESS_SEQ_NUM = EUSER.EMAIL_ADDRESS_SEQ_NUM
WHERE LOC.PROVIDER_SEQ_NUM = :inProviderID

Operations and processes¶

EFT payment location assignment

Business rules¶

BR-09: Assigning EFT always queues customer email notification.

Sources¶

Business\SAIF.Business.Provider.PaymentPreference\clsProviderEFT.vb