dbo.SXAAMBeRX271TransDetail 
Default Table Name  SXAAMBeRX271TransDetail 
Definition  PHI: SXAAMBeRX271TransDetail table will be the operational table and at any point will only have the last 270/271 response for a patient (clientGUID), if exists. When inserting a new response for a patient, all previous record for the patient will be deleted. A eligibility request/response for a patient is valid for only 24 hours. Purge any data older than 2 years from both the tables From RxWriter dialog or Check Eligibility dialog, a 270 eligibility request will be issued to RXHUB (eRX vendor) and RXHUB will always return a valid 271 response or an error response after contacting all relevant PBMs o(Pharmacy Benefit Managers) or Clearing Houses and consolidating the messages to one single response from RXHUB. 

dbo.SXAAMBeRX271TransDetail Columns 
ColumnName  Domain  Datatype  NULL  Definition 
SiteID    smallint  NO  Identifies the unique distributed database ID where the row was inserted. Distributed databases are no longer supported so this column is deprecated. 
Build    int  NO  Identifies the build number and patch level of the software that inserted/updated this row. The format is the Build number followed by 3 digits for the patch level which is typically 000. The build number is tied to specific versions of the software, e.g. Sunrise 15.3 is Build 5021, Sunrise 16.3 is Build 5503. 
TouchedBy    varchar(50)  NO  Information about the user that last updated the row. This will typically be the UserID (IDCode from CV3User table) and may be prefixed with an environment identifier followed by an underscore, e.g. b96d2bf5_jongmore. 
TouchedWhen    datetime  NO  The date and time that the row was last updated and will be based on the Enterprise time zone. 
CreatedBy    varchar(50)  NO  Information about the user that created the row. This will typically be the UserID (IDCode from CV3User table) and may be prefixed with an environment identifier followed by an underscore, e.g. b96d2bf5_jongmore. 
CreatedWhen    datetime  NO  The date and time that the row was created and will be based on the Enterprise time zone. 
Active    bit  NO  Denotes whether the row is active or not, 0 means the row has been soft deleted or deactivated. 
eRxTransDetailID(PK)    int  NO  Auto generated unique primary key value. 
ClientGUID    HVCIDdt  NO  PHI: References GUID for table CV3Client 
ClientVisitGUID    HVCIDdt  NO  PHI: References GUID for table CV3ClientVisit 
TransactionNumber    varchar(50)  NO  PHI: Unique RxHub transaction ID from BHT03 for the request/response 
TransactionSetNumber    int  NO  PHI: When multiple transactions/benefit coverage are returned for patient, this will be used to identify the sequence number when making one among them active. Value in ST02 usually 01 to N number of transactions returned 
RequestedWhen    datetime  NO  PHI: Enterprise TimeZone - Datetime when the 270 was requested 
ResponseStatusType    int  NO  An enumerated type with the following list of possible values: 0 = [COMMUNICATION_ERROR],1 = [SUCCESS],2 = [AAA_ERROR],3 = [TA1_ERROR],4 = [NAK_ERROR],5 = [AK_ERROR], 
ResponseMessage    varchar(100)  YES  PHI: Response message from RXHUB when ResponseStatus is FALSE 
PBMPayerName    varchar(60)  YES  PHI: Name of PBM/Payer supplying the benefit information; from 271 loop 2100A, NM103 
PBMParticipantNumber    varchar(80)  YES  PHI: Internal use – required for drug history and new Rx requests; from 271 loop 2100A, NM109 
HealthPlanNumber    varchar(50)  YES  PHI: ID for the Health Plan; from 271, loop 2100C/D REF02 value, where REF01=”18” 
HealthPlanName    varchar(80)  YES  PHI: User-recognizable name for the Health Plan; from 271, loop 2100C/D REF03 value, where REF01=”18” 
CardholderNumber    varchar(50)  YES  PHI: Internal use – required for drug history and new Rx requests; from 271 loop 2100C/D REF02, where REF01=”1W”. 
CardholderName    varchar(80)  YES  PHI: Internal use – required for drug history and new Rx requests; from 271 loop 2100C/D REF03, where REF01=”1W”. 
PersonCode    varchar(50)  YES  PHI: An identification number assigned to the siblings within the same family; from 271 loop 2100C/D REF02, where REF01=”49”. 
GroupNumber    varchar(50)  YES  PHI: Internal use – required for drug history and new Rx requests; from 271 loop 2100C/D REF02, where REF01=”6P” 
GroupName    varchar(80)  YES  PHI: Internal use – required for drug history and new Rx requests; from 271 loop 2100C/D REF03, where REF01=”6P” 
FormularyListNumber    varchar(50)  YES  PHI: Internal use – required for drug history and new Rx requests; from 271 loop 2100C/D REF02, where REF01=”IF” 
AlternativeListNumber    varchar(80)  YES  PHI: Internal use – required for drug history and new Rx requests; from 271 loop 2100C/D REF03, where REF01=”IF” 
CoverageNumber    varchar(80)  YES  PHI: Use this only when GroupID or CopayID are not there; from 271 loop 2100C/D REF03, where REF01=”1L” 
PayerBIN    varchar(50)  YES  PHI: Plan Network Identification Number; from 271 loop 2100C/D REF02, where REF01=”N6” 
CopayNumber    varchar(80)  YES  PHI: Insurance Policy Number, from 271 loop 2100C/D REF03, where REF01=”IG” 
IsMailOrderRxElig    bit  NO  PHI: Is patient eligible for mail order benefits under this plan (Y/N/U) – from 271 loop 2110C1/2110D1 where EB03 = “88” and EB01=”1” 
IsRetailRxElig    bit  NO  PHI: Is patient eligible forretail benefits under this plan (Y/N/U) – from 271 loop 2110C2/2110D2 where EB03 = “90” and EB01=”1” 
IsSubscriber    bit  NO  PHI: Whether patient is a subscriber or a dependent;271 loop 2100C/D INS01=”Y” indicates insured is a subscriber. 
DependentRelationshipCode    varchar(30)  YES  PHI: From 271 loop 2100D INS02 value when INS01=”N” 01 Spouse 19 Child 21 Unknown 34 Other Adult 
DemogDiffsFlag    bit  NO  PHI: Indicator of demographic differences between what was sent in 270 and what PBM/Payer has on file. If there is a difference, user must be notified. True if values in 271, loop 2100C/D, if INS03=”001” and INS04=”25”.
The differences could be in NM1, N3, N4, or DMG 
SubscriberName    varchar(100)  YES  PHI: Last Name, First name, Middle Name, Suffix – NM103, NM104, NM105, NM107 from 271 loop 2100C 
DependentName    varchar(100)  YES  PHI: Last Name, First name, Middle Name, Suffix – NM103, NM104, NM105, NM107 from 271 loop 2100D 
PatientAddress    varchar(200)  YES  PHI: Concatenate for display the values from 271, loop 2100C/D, fields N301, N302, N401, N402, N403. Either dependent or subscriber 
PatientDOB    varchar(8)  YES  PHI: DMG02 from 271 2100C/D Either dependent or subscriber Date in the format of CCYYMMDD 
PatientGender    varchar(1)  YES  PHI: DMG03 from 271 2100C/D Either dependent or subscriber F/M/U Female/Male/Unknown 
MemberNumber    varchar(80)  YES  PHI: Member Identification Number from 271, loop 2100C, NM109 
MSrepl_tran_version    uniqueidentifier  NO  Optimistic lock identifier. 
MiscellaneousInfoXML    xml  YES  PHI: Miscellaneous patient benefit coverage information that may have to be shown. The X12 271 message specification has many optional elements that can occur multiple times. 
IsGeneralCoverageElig    bit  YES  PHI: Is General Coverage Eligible 
IsLongTermCareElig    bit  YES  PHI: Is Long Term Care Eligible 
IsSpecialtyPharmacyElig    bit  YES  PHI: Is Specialty Pharmacy Eligible 
MSReplrowguid    uniqueidentifier  NO  Unique GUID used to support merge replication. 
CardholderLastName    varchar(35)  YES  PHI: Last Name of the Card holder 
CardholderFirstName    varchar(35)  YES  PHI: First Name of the Card holder 
CardholderMiddleName    varchar(35)  YES  PHI: Middle Name of the Card holder 
CardholderSuffix    varchar(10)  YES  PHI: Prefix Name of the Card holder 
CardholderPrefix    varchar(10)  YES  PHI: Sufix Name of the Card holder 
CardHolderProcessorID    varchar(35)  YES  PHI: Processor Identification Number of the Card holder 
RxHubUniqueID    varchar(35)  YES  PHI: Unique identifier in Rx HUB for eligibility respose 
PartnerMsgID    varchar(35)  YES  PHI: Partner Message Identifier 
LastRequestSentDtm    datetime  YES  PHI: Holds date and time of last request sent. 

dbo.SXAAMBeRX271TransDetail Primary and Unique Keys 
Key Name  Key Type  Keys 
SXAAMBeRX271TransDetailPK  PrimaryKey  eRxTransDetailID