SQL-011: clsProviderEFTCollection¶

SELECT provider EFT locations.

Classification¶

Field	Value
SQL ID	SQL-011
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	`GetProviderEFTLocations`

Parameters¶

Parameter	Direction	Type	Max length	Description
`:ProviderSeqNum`	Input	String	30	Provider identifier used to filter locations with active physical addresses.

Tables¶

Table	Access	Columns used
PROVIDER_LOCATION	READ	See sample SQL.
PROVIDER_EFT_INTERFACE	READ	See sample SQL.
PROVIDER_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,
    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,
    '' AS ExtUserEmailAddr
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 PROVIDER_ADDRESS PADDR
    ON LOC.PROVIDER_LOCATION_SEQ_NUM = PADDR.PROVIDER_LOCATION_SEQ_NUM
   AND PADDR.PROVIDER_ADDRESS_CODE = 'PHY'
WHERE LOC.PROVIDER_SEQ_NUM = :ProviderSeqNum
  AND TO_DATE(PADDR.EFF_DATE) <= TRUNC(SYSDATE)
  AND (TO_DATE(PADDR.EXP_DATE) > TRUNC(SYSDATE) OR TO_DATE(PADDR.EXP_DATE) IS NULL)

Operations and processes¶

Provider EFT collection retrieval

Business rules¶

BR-15: EFT location retrieval requires an active physical address.

Sources¶

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