SQL-005: clsProviderEOB

SELECT PROVIDER_EOB_INTERFACE by provider/location.

Classification

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

Parameters

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

Tables

Table	Access	Columns used
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
    PROVIDER_EOB_INTERFACE.PROVIDER_SEQ_NUM AS ProviderSeqNum,
    PROVIDER_EOB_INTERFACE.PROVIDER_LOCATION_SEQ_NUM AS ProviderLocationSeqNum,
    PROVIDER_EOB_INTERFACE.EXT_USER_TYPE_SEQ_NUM AS ExtUserTypeSeqNum,
    PROVIDER_EOB_INTERFACE.PAPERLESS_EOB_FLAG AS PaperlessEOBFlag,
    PROVIDER_EOB_INTERFACE.DEFAULT_TO_PRIMARY_FLAG AS DefaultPrimaryFlag,
    PROVIDER_EOB_INTERFACE.UPDATE_DATE AS UpdateDate,
    PROVIDER_EOB_INTERFACE.UPDATE_NUM AS UpdateNum,
    PROVIDER_EOB_INTERFACE.UPDATE_USER AS UpdateUser,
    EUSR.ACTIVE_DIRECTORY_ID AS ExtUserId,
    ADDR.EMAIL_ADDRESS AS emailAddress,
    PER.FIRST_NAME || ' ' || PER.LAST_NAME AS UserName
FROM PROVIDER_EOB_INTERFACE
LEFT OUTER JOIN EXT_USER_TYPE EUT
    ON EUT.EXT_USER_TYPE_SEQ_NUM = PROVIDER_EOB_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 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 PROVIDER_EOB_INTERFACE.PROVIDER_SEQ_NUM = :ProviderSeqNum
  AND PROVIDER_EOB_INTERFACE.PROVIDER_LOCATION_SEQ_NUM = :ProviderLocationSeqNum

Operations and processes

• EOB preference management

Business rules

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

Sources

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