SQL-008: clsProviderEFT

SELECT PROVIDER_EFT_INTERFACE by provider/location.

Classification

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

Parameters

Parameter	Direction	Type	Max length	Description
:ProviderSeqNum	Input	Decimal	N/A	Provider filter for single EFT retrieval.
:ProviderLocationSeqNum	Input	Decimal	N/A	Provider-location filter for single EFT retrieval.

Tables

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

Sample SQL

SELECT
    PROVIDER_EFT_INTERFACE.PROVIDER_SEQ_NUM AS ProviderSeqNum,
    PROVIDER_EFT_INTERFACE.PROVIDER_LOCATION_SEQ_NUM AS ProviderLocationSeqNum,
    PROVIDER_EFT_INTERFACE.EXT_USER_TYPE_SEQ_NUM AS ExtUserTypeSeqNum,
    PROVIDER_EFT_INTERFACE.EFF_DATE AS EffDate,
    PROVIDER_EFT_INTERFACE.EXP_DATE AS ExpDate,
    PROVIDER_EFT_INTERFACE.DEFAULT_TO_PRIMARY_FLAG AS DefaultToPrimaryFlag,
    PROVIDER_EFT_INTERFACE.UPDATE_DATE AS UpdateDate,
    PROVIDER_EFT_INTERFACE.UPDATE_NUM AS UpdateNum,
    PROVIDER_EFT_INTERFACE.UPDATE_USER AS UpdateUser,
    PER.FIRST_NAME || ' ' || PER.LAST_NAME AS UserName
FROM PROVIDER_EFT_INTERFACE
LEFT OUTER JOIN EXT_USER_TYPE EUT
    ON EUT.EXT_USER_TYPE_SEQ_NUM = PROVIDER_EFT_INTERFACE.EXT_USER_TYPE_SEQ_NUM
LEFT OUTER JOIN EXT_USER EUSR
    ON EUT.EXT_USER_SEQ_NUM = EUSR.EXT_USER_SEQ_NUM
LEFT OUTER JOIN PERSON PER
    ON PER.PERSON_SEQ_NUM = EUSR.PERSON_SEQ_NUM
WHERE PROVIDER_EFT_INTERFACE.PROVIDER_SEQ_NUM = :ProviderSeqNum
  AND PROVIDER_EFT_INTERFACE.PROVIDER_LOCATION_SEQ_NUM = :ProviderLocationSeqNum

Operations and processes

• EFT record management

Business rules

• BR-06: An EFT record is active only when today falls within the effective window.
• BR-07: LocationHasEFT requires direct deposit and non-revoked authorization.

Sources

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