Skip to content

SQL-006: clsProviderEOB

SELECT PROVIDER_EOB_INTERFACE by location.

Classification

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

Parameters

Parameter Direction Type Max length Description
:ProviderLocationSeqNum Input Decimal N/A Location-only retrieval path used when MyCriteria.ProviderSeqNum = 0.

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,
    PER.FIRST_NAME || ' ' || PER.LAST_NAME AS UserName,
    ADDR.EMAIL_ADDRESS AS emailAddress
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_LOCATION_SEQ_NUM = :ProviderLocationSeqNum

Operations and processes

Business rules

Sources

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