Tx Database Data Dictionary - Transactions Table

This document is to provide field level details of specific key database tables within the Tx database. As development of the application is an iteration over time, this information will change over time.

Transactions Table

The Transactions table contains the stored details of the submitted transaction at the top summary level. For Drug Specific information, reference the TransactionItems table. The two tables can be joined on the Id from transactions to the TransactionId on TransactionItems table.

COLUMN_NAME	DATA_TYPE	LENGTH	NULL ABLE	DESCRIPTION
Id	bigint		NO	Unique Id for the transaction. Referred to as TxId or TransactionId
TransmissionId	Unique identifier		NO	GUID value. Used to link to Transmissions table
ClaimId	bigint		NO	Claim ID is assigned to many transactions that share the same Pharmacy, Rx, Date of Service, BIN, and OCC (for some clients). Used to link to Claims table
PharmacyNpi	nvarchar	10	YES	NPI of the Pharmacy
PharmacyNcpdp	nvarchar	7	YES	NCPDP of the Pharmacy
PharmacyName	nvarchar	128	YES	Name of the Pharmacy
DOS	date		NO	Date of Service of the Transaction
RxNumber	nvarchar	12	YES	Rx Number
FillNumber	int		YES	Submitted Fill Number
ClaimStatus	nvarchar	8	YES	Status of the related Claim Record after processing transaction. For individual transaction results, use the TransactionStatus instead. Paid/Reversed/Rejected values appear in this field, but this does not always correspond to the transaction status.
TransactionStatus	tinyint		NO	Outcome of the transaction. Values: None = 0 Paid = 1 Reversed = 2 Rejected = 3 DupReversed = 4 Duplicate = 5
NDC	nvarchar	11	YES	NDC submitted on transaction. For compound ingredienbts, use the individual records in TransactionItems table.
GPI	nvarchar	14	YES	Related GPI of NDC Submitted
DrugName	nvarchar	30	YES	Drug Name of Submitted NDC
UOM	nvarchar	15	YES	Unit of Measure, such as MG, % , MCG/24Hr
DosageForm	nvarchar	4	YES	Dosage Form, such as SUPP, SUSP, TABS, SOPN, POWD
Strength	nvarchar	20	YES	Strength Value of the Drug Dispensed
FirstName	nvarchar	55	YES	Patient First Name
LastName	nvarchar	55	YES	Patient Last Name
DOB	date		YES	Patient DOB
CHID	nvarchar	20	YES	Cardholder ID
Gender	nvarchar	2	YES	Patient Gender. Values: 1 = Male 2 = Female 3 = Other
PersonCode	nvarchar	3	YES	Person Code from the Member record used to process the transaction. For the Submitted Person Code, see the Submitted Person Code field.
DateFilled	date		YES	Deprecated. Do Not Use. Use DOS instead.
GroupId	int		YES	Internal ID for Group
GroupName	nvarchar	100	YES	Group Name
GroupNumber	nvarchar	15	YES	Group Number
PlanId	int		YES	Internal ID for Plan used during processing.
PlanName	nvarchar	128	YES	Plan Name
CarrierId	int		YES	Internal Id for Carrier associated with Group.
CarrierName	nvarchar	128	YES	Carrier Name
OrganizationId	int		YES	Internal ID for Organization associated with Carrier
OrganizationName	nvarchar	MAX	YES	Organization Name
ChainCode	nvarchar	14	YES	Chain Code used for the Pharmacy
ChainName	nvarchar	128	YES	Chain Name
DAW	nvarchar	2	YES	Dispense as Written Code
DAWDifference	decimal		YES	Dispense As Written, or Brand/Generic Penalty difference calculated on transaction.
SubmittedUandC	decimal		YES	Submitted Usual and Customary Amount
SubmittedIngredientCost	decimal		YES	Submitted Ingredient Cost Amount
IngredientCost	decimal		YES	Deprecated - Use Buy or Sell Ingredient Cost Values instead
PatientPay	decimal		YES	Total Patient Responsibility
TotalPay	decimal		YES	Amount to be paid to the Pharmacy
PlanPay	decimal		YES	Amount to be Billed to the Plan related to Drug Spend. Additional fees added on top of this are included in the ClientBilled field.
PrescriberNpi	nvarchar	10	YES	NPI of the Prescriber
ProcessingFee	decimal		YES	Deprecated. Use Buy and Sell Processing Fee values instead.
DispensingFee	decimal		YES	Deprecated. Use Buy and Sell Dispensing Fee Values instead.
IncentiveFee	decimal		YES	Incentive Fee Amount Paid to the Pharmacy
Copay	decimal		YES	Do Not Use - Currently populating with same value as PatientPay
ClientBilled	decimal		YES	Overall Amount to Bill to Client, inclusive of pharmacy costs and other additional fees.
AdjudicationDateTime	datetime		NO	Adjudication Time of the transaction
InsertedDateTime	datetime		NO	Insert Date Time of the transaction
BIN	nvarchar	6	YES	BIN Number
PCN	nvarchar	10	YES	Processor Control Number
TxIndex	tinyint		NO	Deprecated
TxCount	tinyint		NO	Deprecated
TxVersion	nvarchar	2	YES	NCPDP Version transaction was submitted with.
TxCode	nvarchar	2	YES	B1 = Billing B2 = Reversal
TxStatus	nvarchar	1	YES	A = Overall Transmission was accepted.
OtherPatientPaidAmount	decimal		YES	On a COB Claim, the Total of the Other Patient Paid Amount values.
DaysSupply	int		YES	Days Supply on the transaction.
TaxPaid	decimal		YES	Deprecated. Use Buy and Sell Tax field instead.
IsCob	bit		NO	0 = Not COB 1 = COB
NetworkId	int		YES	ID of the Pharmacy Network Used
NetworkName	nvarchar	128	YES	Name of the Pharmacy Network Used
CopayRuleId	int		YES	ID of the Copay Rule Used
CopayRuleName	nvarchar	128	YES	Name of the Copay Rule Used
CopayRuleSource	tinyint		NO	Location where the Copay Rule was found. Not Applicable = 0 Plan = 1 ProcessRule = 2
CopayProcessRuleId	int		YES	ID of the Process Rule where the Copay was found (if applicable)
DispenseFeeRuleId	int		YES	Id of the Dispensing Fee used
DispenseFeeRuleSource	tinyint		NO	Location where the Dispensing Fee Rule was found. Not Applicable = 0 Plan = 1 ProcessRule = 2
DispenseFeeProcessRuleId	int		YES	ID of the Process Rule where the Dispensing Fee Rule was found (if applicable)
SDCRuleId	int		YES	ID of the SDC Rule used in adjudication
SDCRuleName	nvarchar	128	YES	Name of the SDC Rule used in adjudication.
SDCRuleSource	tinyint		NO	Location where the SDC Rule was found. Not Applicable = 0 Plan = 1 ProcessRule = 2
SDCProcessRuleId	int		YES	ID of the Process Rule where the SDC was found (if applicable)
SubmittedGrossAmtDue	decimal		YES	Submitted Gross amount Due
MemberPriorAuthId	int		YES	Prior Authorization Used on the Transaction.
BillingCycle_Id	int		YES	Deprecated
MONY	nvarchar	1	YES	MONY code of the transaction. For each individual NDC related to a transaction see the MSC field on TransactionItems table.
OtherPatientResponsibilityAmount	decimal		YES	Not currently used
MemberId	int		YES	Member ID of Member found when processing the transaction.
CompoundCode	nvarchar	1	YES	Submitted Compound Code 2 = Compound
BrandGeneric	nvarchar	1	YES	Not currently used
OCC	nvarchar	2	YES	Submitted Other Coverage Code
QuantityDispensed	decimal		YES	Quantity Dispensed value as submitted
SubmittedDispensingFee	decimal		YES	Submitted Dispensing Fee
SubmittedServiceFee	decimal		YES	Not currently used
SubmittedTaxRate	decimal		YES	Submitted Tax Rate Value
SubmittedSalesTax	decimal		YES	Submitted Sales tax, based on sum of the Flat and PCT Tax Amounts
SubmittedTaxBasis	nvarchar	2	YES	Tax Basis Submitted
AppliedToMOOP	decimal		YES	Amount Applied to Out of Pocket cap
AppliedToDeductible	decimal		YES	Amount Applied to Deductible
AppliedToBenefitCap	decimal		YES	Amount Applied to Benefit Cap
DispenseFeeRuleName	nvarchar	128	YES	Name of Dispensing Fee Rule Used
COBAmount	decimal		YES	Not currently used
CopayProcessRuleName	nvarchar	128	YES	Name of the Process Rule where Copay was assigned
DispenseFeeProcessRuleName	nvarchar	128	YES	Name of the Process Rule where Dispense Fee was assigned
SDCProcessRuleName	nvarchar	128	YES	Name of the Process Rule where SDC was assigned
MarginApplied	decimal		YES	DO NOT USE Field is supposed to capture the difference between buy and sell side pricing, but is not always accurate.
JobRunId	int		YES	Job Run ID for Imported Transactions
ClaimCycleId	int		YES	Claim Cycle ID assigned to the Transaction during a Claim Cycling run.
BillingCycleFileId	int		YES	Billing Cycle ID assigned to the Transaction during a Billing Cycle run.
SubgroupId	int		YES	ID of Subgroup if one was active for member at time of adjudication.
SubgroupName	nvarchar	100	YES	Name of Sub Group
BuyIngredientCost	decimal		YES	Ingredient Cost paid to the Pharmacy
SellIngredientCost	decimal		YES	Ingredient Cost billed to the Plan
BuyDispensingFee	decimal		YES	Dispensing Fee paid to the Pharmacy
SellDispensingFee	decimal		YES	Dispensing Fee billed to the Plan
BuyProcessingFee	decimal		YES	Processing Fee paid to the Pharmacy
SellProcessingFee	decimal		YES	Processing Fee billed to the Plan
VaccineAdminFee	decimal		YES	Not currently used. Max Vaccine Admin fee is included in the Incentive Fee field
PharmacyFee	decimal		YES	Fee returned in Professional Service Fee Paid (562-J1) field, triggered by Add Pharmacy Fee Process Rule option.
PharmacyContractFee	decimal		YES	Fee returned in Other Amount Paid (565-J4) field, triggered by the Add Contract Fee Process Rule option.
MembershipFee	decimal		YES	Not Used
PharmacyState	nvarchar	2	YES	State location of Pharmacy
AWP	decimal		YES	AWP Per Unit price. For compounds, use AWP field in TransactionItems table.
AWPExtended	decimal		YES	AWP Per Unit Price This field is based on Medispan’s Extended AWP which was a larger field for products with pricing over 99,999.99
SubmittedProfessionalServiceFee	decimal		YES	Not currently used
AdminFee	decimal		YES	Not currently used
COBRuleId	int		YES	ID of COB Rule
COBRuleName	nvarchar	128	YES	Name of COB Rule
COBProcessRuleId	int		YES	ID of Process Rule that invoked COB Rule
COBProcessRuleName	nvarchar	128	YES	Name of Proess Rule that invoked COB Rule
COBProcessRuleSource	tinyint		NO	Location where the COB Rule was found. Not Applicable = 0 Plan = 1 ProcessRule = 2
SellTax	decimal		YES	Client Billed Tax Amount
BuyTax	decimal		YES	Pharmacy Paid Tax Amount
CarrierCode	nvarchar	15	YES	Carrier Code
Historical	bit		YES	Indicator of if Transaction is considered Historical. Historical transactions are held out of certain processes, such as Billing.
ScriptTag	nvarchar	255	YES	List of Script Tags, separated by ;
RejectCodes	nvarchar	30	YES	List of Reject Codes, separated by ,
DateWritten	datetime		YES	Submitted Date Written
IsBillable	bit		NO	Indicator of if the transaction would be included in a billing file. This is set using netting logic during the claim cycling.
XOverAmount	decimal		YES	Not currently in use
NumberAuthorizedRefills	int		YES	Submitted Number of Refills Authorized value.
OriginalTxId	bigint		NO	On a reversal, this is the transaction being reversed.
LocationId	int		YES	ID of the Location Code assigned to the member at time of processing.
NetworkClientType	tinyint		YES	Network Client Type set on the Pharmacy Network used. Values of: 1 = Retail 2 = Retail90 3 = MailOrder 4 = Specialty 5 = In House 6 = Keyed 7 = SpecialUse 8 = Cashcard 9 = Rural
IsTest	bit		NO	Indicates if the Transaction is a test
IsSubrogation	bit		NO	Indicates if the Transaction was processed as part of a Subrogation file.
PharmacyAdminFee	decimal		YES	Override value for PharmacyAdminFee set via a process rule. This value is used in place of the default Pharmacy Admin Fees at time of Billing Cycle File Creation
PharmacyReversalAdminFee	decimal		YES	Override value for reversal admin fees set via a Process Rule.
SenderId	nvarchar	20	YES	Value submitted in the SenderId field on API Requests
AuthNum	nvarchar	20	YES	Starting in version 1.15.4, this is the value returned for Authorization Number to the Pharmacy.
NetworkReimbursementId	nvarchar	10	YES	Value returned for Network Reimbursement Id to the pharmacy. This is set via a Process Rule.
SelfPay	bit		NO	Selfpay indicator. If True, indicates payment is to be made to Member instead of Pharmacy.
Keyed	bit		NO	Keyed Claim Indicator
UserName	nvarchar	50	YES	User that submitted a claim via the UI.
SecondaryProcessor	tinyint		YES	External Adjudicator that was used to determine the lowest pricing for comparison. 1 = RxSense 2 = RxNXT 3 = RxlClient1 4 = RxlClient2 5 = RxPartner
SecondaryProcessorWon	bit		YES	Indicator of if the External Adjudicator pricing beat local pricing.
LosingPatientPay	decimal		YES	On External Adjudicator transactions, this is the amount the patient would have paid at the loser of the comparison.
Imported	bit		YES	Indicates if a claim was imported.
NoBill	bit		YES	Indicates that transaction should be separated into the ‘NoBill’ billing export instead of the standard Billing Export.
DAWResponsibleParty	tinyint		YES	Party responsible for paying any DAW related penalty. Values of: 1 = Member 2 = Plan 3 = Pharmacy
CopayAssistAdjAmt	decimal		YES	Not currently in use
SFSId	int		YES	ID of the State Fee Schedule Rule used during processing.
SFSVenueState	varchar	2	YES	Venue State used for SFS Rule
SFSDispensingFee	decimal		YES	Dispensing Fee determined by the SFS Rule
SFSDispenseFeeRuleId	int		YES	ID of the Dispensing Fee rule used in determination of the SFS Dispensing Fee
SFSDispenseFeeProcessRuleId	int		YES	Process Rule ID used to determine the SFS Dispense Fee
SFSDispenseFeeProcessRuleName	nvarchar	128	YES	Process Rule Name used to determine the SFS Dispense Fee.
OtherAmountClaimedProcessRuleId	int		YES	Process Rule used to assign the Other Amount Claimed rule
OtherAmountClaimedRuleId	int		YES	ID of the Other Amount Claimed Rule
OtherAmountClaimedRuleName	nvarchar	128	YES	Name of the Other Amount Claimed rule
OtherAmountClaimedTotal	decimal		YES	Total amount of Other Amount Claimed fields submitted.
MemberMergeIndicator	bit		YES	Indicates that the transaction has been impacted by a Member Merge process.
MemberMergeImportId	int		YES	Import ID of the Member Merge file that impacted the transaction.
ExemptFromPayables	bit		YES	Indicates that the transaction will be ignored in the Payables process.
OtherAmountPaidTotal	decimal		YES	Total Amount returned to pharmacy in the Other Amount Paid fields.
SecondaryProcessorGroup	nvarchar	15	YES	Group used for the submission to the External Adjudicator.
SellTotalOriginalPrice	decimal		YES	Value of the Sell Side total pricing prior to any changes to pricing due to the External Adjudicator process.
SecondaryProcessorBIN	varchar	6	YES	BIN used for the External Adjudicator
SecondaryProcessorPCN	varchar	10	YES	PCN used for the External Adjudicator
OriginalRejectCodes	varchar	30	YES	In an External Adjudicator Reject Wrap scenario, this is the Reject code the transaction would have rejected for if not for being sent to the External Adjudicator.
NoPay	bit		YES	Indicates the transaction will be included on Billing Extracts, but for Payables will be a payment type of NoPay. This will include the transaction on an 835, but exclude it from other Payment reports within the Payables process.
ExemptFromReceivables	bit		YES	Indicates the transaction will be ignored during the Receivables process.
ReimbursementThresholdAmount	decimal		YES	Amount determined in the calculation for the Reimbursement Reporting Threshold Process Rule option.
CashCardProcessorFee	decimal		YES	Cash Card Processor Fee from Proecess Rules used on the transaction
SubmittedPersonCode	nvarchar	3	YES	Person Code submitted on the transaction
ForceApproveMPA	int		YES	Contains the MPA # submitted as the ForceApproveMPA, and applied to the transaction.
PayToType	nvarchar	Max	YES	The type of PayToId in the next field, i.e. PaymentCenter. This only populates when a PayToType override is submitted.
PayToId	nvarchar	Max	YES	The PayToId Override value submitted.
ProviderIdQualifier	varchar	2	YES	Provider Id Qualifier as submitted on the Transaction in field 465-EY.
ProviderId	varchar	35	YES	Provider ID as submitted on the Transaction in field 444-E9.
OtherPayerAmountRecognized	decimal	14,4	YES	Stores the Other Payer Amount Recognized (566-J5) value when returned in an External Adjudicator response
CompoundDosageFormDescriptionCode	varchar	10	YES	Compound Dosage Form Description Code as submitted on the Transaction in field 450-EF.
CompoundDispensingUnitFormIndicator	varchar	10	YES	Compound Dispensing Unit Form Indicator as submitted on the Transaction in field 451-EG.
RouteofAdministration	varchar	11	YES	Route of Administration as submitted on the Transaction in field 995-E2.
SubmissionClarificationCode	nvarchar	128	YES	Submission Clarification Code as submitted on the Transaction in field 420-DK.
PatientResidenceCode	nvarchar	3	YES	Patient Residence as submitted on the transaction in field 384-4X
AppliedToGroupRxMaximum	int		YES	Indicates that the transaction counted to Maximum Prescriptions edit on the group. Value of 1 on a paid, -1 on a reversal.
PayToOverrideUsed	bit		YES	1 indicates that PayToId and Type on the transaction were provided as an override for the transaction.
SellIngredientCostAdjustmentTotal	decimal	18,6	YES	Adjustment amount used from the Sell Ingredient Cost Adjustment by Basis option on a Bill Cost Calculator.
ExternalEligServiceId	int		YES	ID of the Eligibility / Patient Pay service used during processing.
SDC2RuleId	int		YES	ID of the second SDC Rule used in adjudication
SDC2RuleName	nvarchar	128	YES	Name of the second SDC Rule used in adjudication.
SDC2RuleSource	tinyint		YES	Location where the second SDC Rule was found. Not Applicable = 0 Plan = 1 ProcessRule = 2
SDC2ProcessRuleId	int		YES	ID of the Process Rule where the second SDC was found (if applicable)
SDC2ProcessRuleName	nvarchar	128	YES	Name of the Process Rule where the second SDC was assigned
SDC3RuleId	int		YES	ID of the third SDC Rule used in adjudication
SDC3RuleName	nvarchar	128	YES	Name of the third SDC Rule used in adjudication.
SDC3RuleSource	tinyint		YES	Location where the third SDC Rule was found. Not Applicable = 0 Plan = 1 ProcessRule = 2
SDC3ProcessRuleId	int		YES	ID of the Process Rule where the third SDC was found (if applicable)
SDC3ProcessRuleName	nvarchar	128	YES	Name of the Process Rule where the third SDC was assigned

Price Source Values

1 = AAWP
2 = AWP
3 = CMSFUL
4 = DP
5 = GAD
6 = GEAP
7 = GEN
8 = WAC
9 = UnC
10 = Base100
11 = MAC1
12 = MAC2
13 = MAC3
14 = SelectedMAC
15 = CustomCostList
16 = FlatAmount
17 = PricePerUnit
18 = NetworkMAC
19 = SubmittedIngredientCost
20 = NADAC
21 = Ingredient Cost Plus MAC Fee
22 = State Fee Schedule
23 = ACAFul