SQL-007: clsProviderEOBCollection

SELECT provider locations with EOB preference.

Classification

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

Parameters

Parameter	Direction	Type	Max length	Description
:ProviderSeqNum	Input	Decimal	N/A	Provider filter for collection retrieval.

Tables

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

Sample SQL

SELECT
    LOC.PROVIDER_SEQ_NUM AS ProviderSeqNum,
    LOC.PROVIDER_LOCATION_SEQ_NUM AS ProviderLocationSeqNum,
    EOB.EXT_USER_TYPE_SEQ_NUM AS ExtUserTypeSeqNum,
    EOB.PAPERLESS_EOB_FLAG AS PaperlessEOBFlag,
    EOB.DEFAULT_TO_PRIMARY_FLAG AS DefaultPrimaryFlag,
    EOB.UPDATE_DATE AS UpdateDate,
    EOB.UPDATE_NUM AS UpdateNum,
    EOB.UPDATE_USER AS UpdateUser,
    EUSR.ACTIVE_DIRECTORY_ID AS ExtUserId,
    PER.FIRST_NAME || ' ' || PER.LAST_NAME AS UserName,
    ADDR.EMAIL_ADDRESS AS emailAddress
FROM PROVIDER_LOCATION LOC
LEFT OUTER JOIN PROVIDER_EOB_INTERFACE EOB
    ON LOC.PROVIDER_LOCATION_SEQ_NUM = EOB.PROVIDER_LOCATION_SEQ_NUM
LEFT OUTER JOIN EXT_USER_TYPE EUT
    ON EUT.EXT_USER_TYPE_SEQ_NUM = EOB.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 EMAIL_ADDRESS ADDR
    ON ADDR.EMAIL_ADDRESS_SEQ_NUM = EUSR.EMAIL_ADDRESS_SEQ_NUM
LEFT OUTER JOIN PERSON PER
    ON PER.PERSON_SEQ_NUM = EUSR.PERSON_SEQ_NUM
WHERE LOC.PROVIDER_SEQ_NUM = :ProviderSeqNum

Operations and processes

• EOB preference management

Business rules

• BR-17: Blank EOB user names display as Paper EOB.

Sources

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