Data Model

RAW DATA tblPEPreliminary ClientID PhysUniqueID PhysInternalID PhysUPIN PhysLastName PhysFirstName PhysMI PhysSpecialty PlanName DateEncounter FacName PatSocialSecurity PatUniqueID PatID PatLastName PatFirstName PatMI PatAddress1 PatAddress2 PatCity PatState PatZipcode PatAreaCode PatPhoneNumber PatBirthdate PatSex PatTotalCharges PrincipleDiagnosis CPT1 CPT1Charge Diagnosis2 CPT2 CPT2Charge Diagnosis3 CPT3 CPT3Charge Diagnosis4 CPT4 CPT4Charge Diagnosis5 CPT5 CPT5Charge Diagnosis6 CPT6 CPT6Charge Diagnosis7 CPT7 CPT7Charge Diagnosis8 CPT8 CPT8Charge Diagnosis9 CPT9 CPT9Charge Diagnosis10 CPT10 CPT10Charge Error ErrorOverrideAllowed ErrorDescription ErrorFields ImportDate Promoted Language

PROMOTED DATA tblPECurrentQuarter ClientID PhysUniqueID PlanID DateEncounter QuarterID FacID PatSocialSecurity PatUniqueID PatID PatLastName PatFirstName PatMI PatAddress1 PatAddress2 PatCity PatState PatZipcode PatAreaCode PatPhoneNumber PatAge PatAgeGrouping PatSex PatTotalCharges PrincipleDiagnosis CPT1 CPT1Charge Diagnosis2 CPT2 CPT2Charge Diagnosis3 CPT3 CPT3Charge Diagnosis4 CPT4 CPT4Charge Diagnosis5 CPT5 CPT5Charge Diagnosis6 CPT6 CPT6Charge Diagnosis7 CPT7 CPT7Charge Diagnosis8 CPT8 CPT8Charge Diagnosis9 CPT9 CPT9Charge Diagnosis10 CPT10 CPT10Charge SelectedForMailing MailedTo DateMailedSurvey DateReceivedSurvey ImportDate UserOverRide Language

LOOKUP TABLES tblLKUPPhysician ClientID PhysUniqueID PhysUPIN PhysInternalID PhysLastName PhysFirstName PhysMI PhysSpecialty PhysSpecialtyTwo PhysSpecialtyThree PhysCity PhysState PhysZipcode PhysGroupName1 PhysGroupName2 PhysGroupName3 PhysicianSelection UpdateFlag

tblLKUPQuarter QuarterID PopulationSelection UpdateFlag

LOOKUP TABLES: GENERATED AT PROMOTION FROM PRELIMINARY TABLES TO PROMOTED TABLE REFERENCE TABLES: BUILT AT HBSI FOR CLIENT CLIENT SPECIFIC REFERENCE TABLES : CLIENT ID UNIQUELY IDENTIFIES INSTANCES WITHIN REF TABLE GENERIC REFERENCE TABLES: ALL CLIENTS CAN USE TABLE AS IS.

REFERENCE TABLES tblREFSpecialty

tblREFUDPhys1 (...2, ...3) PhysGroupName PhysGroupSelection UpdateFlag

tblREFPlan ClientID Plan ID PlanName PlanSelection

tblREFSex SexCode Sex SexSelection

tblREFZipcode Zipcode ZipcodeDescription PIRAMEDServiceArea CustomerServiceAreaOne CustomerServiceAreaTwo State County CountyDescription CountyCode MSAName GeoName TotalPop PopAge0to14 PopAge15to44 PopAge45to64 PopAge65to84 PopAge85Plus ProductLineDescription ZipcodeSelection

tblREFUDFac1 (...2, ...3) FacGroupName FacGroupSelection UpdateFlag

tblREFCounty CountyCode CountyDescription CountySelection

tblREFDiagnosis DIagnosisCode DiagnosisCodeDescription CustomerDiagnosisGroup CCFlag DiagnosisSelection

tblREFCPT CPT Modifier ProcedureName SurgicalFlag CPTSelection

LEGEND ITALICS BOLD SHADED

-

FOREIGN KEY PRIMARY KEY CALCULATED FIELD

GENERIC REFERENCE TABLES

tblREFAgeGrouping AgeGroup AgeGroupDescription AgeGroupSelection

tblREFFacility ClientID FacID FacName FacAddress1 FacAddress2 FacCity FacState FacZipcode FacGroupName1 FacGroupName2 FacGroupName3 FacilitySelection

CLIENT SPECIFIC REFERENCE TABLES

ClientID PhysSpecialty Name PhysSpecialtySelection

tblREFClient ClientID ClientName ClientAddress1 ClientAddress2 ClientCity ClientState ClientZipcode ClientSelection

CLIENT SPECIFIC REFERENCE TABLES

GENERIC REFERENCE TABLES