Summary Table
Categories |
Total Count |
PII |
0 |
URL |
0 |
DNS |
0 |
EKL |
0 |
IP |
0 |
PORT |
0 |
VsID |
0 |
CF |
0 |
AI |
0 |
VPD |
0 |
PL |
0 |
Other |
0 |
File Content
?CREATE TABLE dbo.FBCSUBClaims
(
--authoritative source:
--A06.CDWWork.FBCS.ub92
--fields
FBCSUBClaimID bigint IDENTITY(1,1) NOT NULL, --EPRS assigned (PK)
PatientID bigint, --EPRS assigned (FK), reference Patients, TODO: combo trade on vistapatkey, box2 with SPatient
StationID smallint NOT NULL, --EPRS assigned (FK), reference Stations, (trade in station__no + facility)
VerifiedByFBCSUserID int, --EPRS assigned (FK), reference FBCSUsers, verifiedby
PreImportedByFBCSUserID int, --EPRS assigned (FK), reference FBCSUsers, preimportedby
ImportedByFBCSUserID int, --EPRS assigned (FK), reference FBCSUsers, importedby
PreviousPreImportedByFBCSUserID int, --EPRS assigned (FK), reference FBCSUsers, previewpreimportedby
PreviousImportedByFBCSUserID int, --EPRS assigned (FK), reference FBCSUsers, previousimportedby
ReopenedByFBCSUserID int, --EPRS assigned (FK), reference FBCSUsers, reopeneduser
SentToPaymentByFBCSUserID int, --EPRS assigned (FK), reference FBCSUsers, senttopaymentby
ProviderID bigint, --EPRS assigned (FK), reference Providers, provider + providernpi
PhysicianProviderID bigint, --EPRS assigned (FK), reference Providers, box1 (TODO: what we do with box1b)
AttendingPhysicianProviderID bigint, --EPRS assigned (FK), reference Providers, take attendingphysicianid if filled, otherwise take attendingphysicianname + attendingphysiciannpi if filled (if both filled, they are always the same)
OperatingPhysicianProviderID bigint, --EPRS assigned (FK), reference Providers, operationphysiciannpi, operatingphysicianname
PaymentFacilityProviderID bigint, --EPRS assigned (FK), reference Providers, paymentfacilityid
EDIBillingProviderID bigint, --EPRS assigned (FK), reference Providers, edi_bill_prov_name_box33
EDIRenderingProviderID bigint, --EPRS assigned (FK), reference Providers, edirenderingprov
EntryClaimTypeID tinyint, --EPRS assigned (FK), reference ClaimTypes, claimentrytype
ClaimStatusID tinyint, --EPRS assigned (FK), reference ClaimStatuses, status
ImportedClaimStatusID tinyint, --EPRS assigned (FK), reference ClaimStatuses, importednote
AdmissionTypeID tinyint, --reference AdmissionTypes, admissiontype (write field to AdmissionTypeID, 0 - 9)
AdmissionSourceID tinyint, --EPRS assigned (FK), reference AdmissionSources, admissionsrc
ReasonID smallint, --EPRS assigned (FK), reference Reasons, reasonid
AdmissionReasonID smallint, --EPRS assigned (FK), reference Reasons, admissionreasonid
DischargeReasonID smallint, --EPRS assigned (FK), reference Reasons, dischargereasonid
SuspensionReasonID smallint, --EPRS assigned (FK), reference Reasons, suspensionreason
RepricedStatusID tinyint, --EPRS assigned (FK), reference RepricedStatuses, repricedstatus
MeansTestStatusID tinyint, --EPRS assigned (FK), reference MeansTestStatuses, meansteststatus
DischargeStatusID tinyint, --EPRS assigned (FK), reference DischargeStatuses, dischargestatus
BatchLogID bigint, --EPRS assigned (FK), reference BatchLogs
CDWUBNumber bigint, --id
FBCSImageNumber bigint, --imageid
FBCSBatchNumber bigint, --batchid
RepricersReferenceNumber varchar(50), --repricersreferenceid, take LEFT(50) characters
FederalTaxNumber varchar(50), --box5
CleanTaxNumber varchar(50), --taxidclean
FPPSNumber bigint, --fpps_id, Federal Personal Payroll System ID?
PatientControlNumber varchar(100), --box3, TODO: This field will need to be set to only take the LEFT 100 characters
JobNumber int, --jobnumber
HACEDINumber varchar(50), --hacdei_id
CheckNumber varchar(50), --checknumber
OriginalReferralNumber varchar(30), --box22b
DateAdded datetime2(7), --dtstamp
DateFrom datetime2(7), --box6from
DateTo datetime2(7), --box6to
DateVerified datetime2(7), --verifieddtstamp
DateCreated datetime2(7), --creationdate, TODO: need to do a cast from varchar to datetime2(7)
DatePreImported datetime2(7), --preimporteddtstamp
DateImported datetime2(7), --importeddtstamp
DateVerficationInProgress datetime2(7), --verificationinprogressdtstamp
DateAdmission datetime2(7), --admissiondate
AdmissionHour varchar(4), --admissionhour, TODO: factor for 5 as well as 0500
DischargeHour varchar(4), --dischargehour, TODO: factor for 5 as well as 0500
DateReopened datetime2(7), --dtreopened
DateSentToPayment datetime2(7), --dtsenttopayment
DateReturnedFromPayment datetime2(7), --dtreturnedfrompayment
DateVoucher datetime2(7), --voucherdate
DateCalculated datetime2(7), --dtcalculated
DatePaymentReset datetime2(7), --payment_reset_date
DateSentToBeScored datetime2(7), --senttobescoreddtstamp
DateScoringReceived datetime2(7), --scoringrcvddtstamp
DateTerminalStatusUpdateSent datetime2(7), --terminalstatusupdatesentdtstamp
DateFilingEffective datetime2(7), --filingeffectivedate
ChargeTotal money, --totalcharges
ClaimChosenAmountToPay money, --claimchosenamounttopay
InpatientAmountPaid money, --inpatientamtpaid
VARepricedAmount money, --varepricedamount
HealthnetRepricedAmount money, --healthnetrepricedamt
DaysApproved int, --Approveddays
NumberOfVisitsToDeduct smallint, --numberofvisitstodeduct
ProspectivePaymentSystemCode varchar(100), --pps_code
ReferringProviderTaxonomy varchar(20), --referring_provider_taxonomy
AttendingPhysicianProviderTaxonomy varchar(20), --attending_physician_taxonomy
BillingProviderTaxonomy varchar(20), --billing_provider_taxonomy
PayToProviderTaxonomy varchar(20), --pay_to_provider_taxonomy
EDIFacilityToRouteTo varchar(50), --edifacilitytorouteto
BillType varchar(50), --box4, TODO: is there any way to normalize this and provide names to 700+ of numerical entries?
VAFundControlPoint varchar(8000), --vafcp
VABatchNumber varchar(8000), --vafb
VAInvoiceNumber varchar(8000), --vain
VAObligationNumber varchar(8000), --vaon
AuthorizationNumber varchar(50), --authnum, TODO: take LEFT(50)
HealthnetProductCodeNumber tinyint, --healthnetproductcode
InsuranceJustificationCode smallint, --insjustificationcode
LastCalculatedJobNumber varchar(100), --lasatcalcdjobnumber
PatientType varchar(50), --patienttypeid, Replace 1 with Inpatient, 2 with Outpatient
AccidentState varchar(10), --accidentstate
BillableInsurance varchar(8000), --billable_insurance
PlaceOfService varchar(20), --pos
SpecialProvisionCategory varchar(50), --specialprovcat
ExternalCauseInjury varchar(100), --eci
TotalServiceConnected varchar(50), --totalsc
ServiceConnectedConditions varchar(8000), --sc_conditions
PrimaryEligibility varchar(100), --primaryeligibility
SuspendedNote varchar(8000), --suspendednote
RejectedNote varchar(8000), --rejectednote
NoteText varchar(200), --notetxt, Note max size is 80 presently
ReopenedReason varchar(200), --reopenedreason
ClaimMemo varchar(255), --claimmemo
VerficationInProgressGUID varchar(50), --verificationinprogressguid
NUCCVersion varchar(5), --nuccversion
SentToBeScored int NULL, --senttobescored, Another magic decoder ring, can't use bit, b/c values are NULL, -3, -2, -1, 0 (too many -3 and -2 to be accidents)
PITStatus tinyint NULL, --pitstatus, another magic decoder ring (0 - 5 or NULL)
IsVerified bit NULL, --verified, Needs to be tri-state bit, because source has nulls
IsRejected bit NULL, --rejected, Needs to be tri-state bit, because source has nulls
IsImported bit NULL, --imported, Needs to be tri-state bit, because source has nulls
IsScrubbed bit NULL, --scrubbed, Needs to be tri-state bit, because source has nulls
IsDocumentsAttached bit NULL, --documentsattached, Needs to be tri-state bit, because source has nulls
IsInpatient bit NULL, --inpatient, Needs to be tri-state bit, because source has nulls
IsContractClaim bit NULL, --contractclaim, Needs to be tri-state bit, because source has nulls
IsHeroClaim bit NULL, --hero_claim_flag, Needs to be tri-state bit, because source has nulls
IsHACEDIUpdateSent bit NULL, --hacediupdatesent, Needs to be tri-state bit, because source has nulls
IsServiceConnected bit NULL, --sc_connected, Needs to be tri-state bit, because source has nulls
IsTaxIDOverride bit NULL, --taxidoverride, Needs to be tri-state bit, because source has nulls
IsBlackWhiteClaim bit NULL, --bwclaim, Needs to be tri-state bit, because source has nulls
IsWrongClaimType bit NULL, --wrongclaimtype, Needs to be tri-state bit, because source has nulls
IsSentToCalculate bit NULL, --senttocalculate, Needs to be tri-state bit, because source has nulls
IsDirty bit NULL, --dirty, Needs to be tri-state bit, because souurce has nulls
IsPaymentReset bit NULL, --payment_reset, Needs to be tri-state bit, because source has nulls
IsPITRejected bit NULL, --pitrejected, Needs to be tri-state bit, because source has nulls
IsTerminalStatusFeedCreated bit NULL, --terminalstatusfeedcreated, Needs to be tri-state bit, because source has nulls
IsEffectiveDate bit NULL, --effectivedate, Needs to be tri-state bit, because source has nulls
IsOtherHealthInsurancePresent bit NULL, --other_hlth_ins_present, Need to convert from Y/N, also need to be tri-state bit, because source has nulls
HasDuplicates bit NULL, --hasdups, Needs to be tri-state bit, because source has nulls
IsOn30DayHold bit NULL, --on30dayhold, Needs to be tri-state bit, because source has nulls
IsNewOCRMethod bit NULL, --newocrmethod, Needs to be tri-state bit, because source has nulls
IsOCRCompleted bit NULL, --ocrcompleted, Needs to be tri-state bit, because source has nulls
--primary key
CONSTRAINT PK_FBCSUBClaims PRIMARY KEY NONCLUSTERED --not clustered due to clust. columnstore index
(
FBCSUBClaimID ASC
)
)
ON CoreData
--when using clustered columnstore index, can't set data compression to PAGE
--WITH (DATA_COMPRESSION = PAGE)
GO
/*
<station__no, varchar(30),> --StationID
,<id, bigint,> --CDWUBNumber
,<imageid, bigint,> --FBCSImageNumber
,<dtstamp, datetime2(7),> --DateCreated
,<verified, bigint,> --verified
,<verifiedby, bigint,> --VerifiedByFBCSUserID
,<verifieddtstamp, datetime2(7),> --DateVerified
,<rejected, bigint,> --IsRejected
,<imported, bigint,> --IsImported
,<importednote, varchar(8000),> --ImportedClaimStatusID
,<importedby, bigint,> --ImportedByFBCSUserID
,<importeddtstamp, datetime2(7),> --DateImported
,<printedletter, int,> --skip this field, no longer used
,<printedletterdtstamp, datetime2(7),> --skip this field, no longer used
,<printedby, bigint,> --[skip this field, no longer used]
,<box1, varchar(8000),> --PhysicianProviderID
,<box3, varchar(8000),> --PatientControlNumber
,<box5, varchar(8000),> --FederalTaxNumber
,<box6from, datetime2(7),> --DateFrom
,<box6to, datetime2(7),> --DateTo
,<patientname, varchar(8000),> --skip this field, will be using PatientID and pulling Patient info from SPatient to do links
,<dob, datetime2(7),> --skip this field, will be using PatientID and pulling Patient info from SPatient to do links
,<sex, varchar(8000),> --skip this field, will be using PatientID and pulling Patient info from SPatient to do links
,<box60a, varchar(8000),> --[migrate to FBCSUBClaimABCs, InsuredIdentificationNumber, code=A]
,<box60b, varchar(8000),> --[migrate to FBCSUBClaimABCs, InsuredIdentificationNumber, code=B]
,<box60c, varchar(8000),> --[migrate to FBCSUBClaimABCs, InsuredIdentificationNumber, code=C]
,<diag1, varchar(8000),> --[migrate to FBCSClaimDiagnoses, Diag Code 01]
,<attendingphysicianid, varchar(8000),> --AttendingPhysicianProviderID
,<attendingphysicianname, varchar(8000),> --AttendingPhysicianProviderID
,<creationdate, varchar(8000),> --DateCreated
,<offx, int,> --[skip this field, no longer used]
,<offy, int,> --[skip this field, no longer used]
,<diag2, varchar(8000),> --[migrate to FBCSClaimDiagnoses, Diag Code 02]
,<diag3, varchar(8000),> --[migrate to FBCSClaimDiagnoses, Diag Code 03]
,<diag4, varchar(8000),> --[migrate to FBCSClaimDiagnoses, Diag Code 04]
,<diag5, varchar(8000),> --[migrate to FBCSClaimDiagnoses, Diag Code 05]
,<diag6, varchar(8000),> --[migrate to FBCSClaimDiagnoses, Diag Code 06]
,<diag7, varchar(8000),> --[migrate to FBCSClaimDiagnoses, Diag Code 07]
,<diag8, varchar(8000),> --[migrate to FBCSClaimDiagnoses, Diag Code 08]
,<diag9, varchar(8000),> --[migrate to FBCSClaimDiagnoses, Diag Code 09]
,<diag10, varchar(8000),> --[migrate to FBCSClaimDiagnoses, Diag Code 10]
,<reasonid, bigint,> --ReasonID
,<eci, varchar(8000),> --ExternalCauseInjury
,<documentsattached, int,> --IsDocumentsAttached
,<box4, varchar(8000),> --BillType
,<taxidclean, varchar(8000),> --CleanTaxNumber
,<status, varchar(8000),> --ClaimStatusID
,<suspensionreasonid, varchar(8000),> --SuspensionReasonID
,<suspendednote, varchar(8000),> --SuspendedNote
,<rejectednote, varchar(8000),> --RejectedNote
,<scrubbed, int,> --IsScrubbed
,<facility, bigint,> ----[skip this field, doing a trade-in with station__no to determine StationID]
,<verificationinprogressdtstamp, datetime2(7),> --DateVerficationInProgress
,<vafcp, varchar(8000),> --VAFundControlPoint
,<vafb, varchar(8000),> --VABatchNumber
,<vain, varchar(8000),> --VAInvoiceNumber
,<vaon, varchar(8000),> --VAObligationNumber
,<authnum, varchar(8000),> --AuthorizationNumber
*** ,<box1b, varchar(8000),> --TODO: this is the provider address. If we can figure out how to use, we will, but not here
,<preimportedby, bigint,> --PreImportedByFBCSUserID
,<preimporteddtstamp, datetime2(7),> --DatePreImported
,<vistapatkey, bigint,> --[skip this field, Will be in Patients table]
,<vistaauthkey, bigint,> --[skip this field, duplicate]
,<editactionstaken, int,> --[skip this field, no longer used]
,<inpatient, int,> --IsInpatient
,<healthnetrepricedamt, numeric(28,6),> --HealthnetRepricedAmount
,<repricedstatus, varchar(8000),> --RepricedStatusID
,<contractclaim, int,> --IsContractClaim
,<jobnumber, int,> --JobNumber
,<providerid, bigint,> --ProviderID
,<varepricedamount, numeric(28,6),> --VARepricedAmount
,<Approveddays, int,> --ApprovedDays
,<inpatientamtpaid, numeric(28,6),> --InpatientAmountPaid
,<claimentrytype, varchar(8000),> --EntryClaimTypeID
,<batchid, bigint,> --FBCSBatchNumber
,<sc_conditions, varchar(8000),> --ServiceConnectedConditions
,<billable_insurance, varchar(8000),> --BillableInsurance
,<patienttypeid, bigint,> --PatientType
,<diag11, varchar(8000),> --[migrate to FBCSClaimDiagnoses, Diag Code 11]
,<diag12, varchar(8000),> --[migrate to FBCSClaimDiagnoses, Diag Code 12]
,<diag13, varchar(8000),> --[migrate to FBCSClaimDiagnoses, Diag Code 13]
,<diag14, varchar(8000),> --[migrate to FBCSClaimDiagnoses, Diag Code 14]
,<diag15, varchar(8000),> --[migrate to FBCSClaimDiagnoses, Diag Code 15]
,<diag16, varchar(8000),> --[migrate to FBCSClaimDiagnoses, Diag Code 16]
,<diag17, varchar(8000),> --[migrate to FBCSClaimDiagnoses, Diag Code 17]
,<diag18, varchar(8000),> --[migrate to FBCSClaimDiagnoses, Diag Code 18]
,<admissiondate, datetime2(7),> --DateAdmission
,<conditioncode1, varchar(8000),> --[migrate to FBCSConditionCodes, Code 1]
,<conditioncode2, varchar(8000),> --[migrate to FBCSConditionCodes, Code 2]
,<conditioncode3, varchar(8000),> --[migrate to FBCSConditionCodes, Code 3]
,<conditioncode4, varchar(8000),> --[migrate to FBCSConditionCodes, Code 4]
,<conditioncode5, varchar(8000),> --[migrate to FBCSConditionCodes, Code 5]
,<conditioncode6, varchar(8000),> --[migrate to FBCSConditionCodes, Code 6]
,<conditioncode7, varchar(8000),> --[migrate to FBCSConditionCodes, Code 7]
,<conditioncode8, varchar(8000),> --[migrate to FBCSConditionCodes, Code 8]
,<conditioncode9, varchar(8000),> --[migrate to FBCSConditionCodes, Code 9]
,<conditioncode10, varchar(8000),> --[migrate to FBCSConditionCodes, Code 10]
,<conditioncode11, varchar(8000),> --[migrate to FBCSConditionCodes, Code 11]
,<accidentstate, varchar(8000),> --AccidentState
,<payernamea, varchar(8000),> --[migrate to FBCSUBClaimABCs, PayerName, code=A]
,<payernameb, varchar(8000),> --[migrate to FBCSUBClaimABCs, PayerName, code=B]
,<payernamec, varchar(8000),> --[migrate to FBCSUBClaimABCs, PayerName, code=C]
,<healthplanida, varchar(8000),> --[migrate to FBCSUBClaimABCs, HealthPlanNumber, code=A]
,<healthplanidb, varchar(8000),> --[migrate to FBCSUBClaimABCs, HealthPlanNumber, code=B]
,<healthplanidc, varchar(8000),> --[migrate to FBCSUBClaimABCs, HealthPlanNumber, code=C]
,<relinfoa, varchar(8000),> --[migrate to FBCSUBClaimABCs, ReleaseInformation, code=A]
,<relinfob, varchar(8000),> --[migrate to FBCSUBClaimABCs, ReleaseInformation, code=B]
,<relinfoc, varchar(8000),> --[migrate to FBCSUBClaimABCs, ReleaseInformation, code=C]
,<asssignmentofbenefitsa, varchar(8000),> --[migrate to FBCSUBClaimABCs, BenefitsAssignment, code=A]
,<asssignmentofbenefitsb, varchar(8000),> --[migrate to FBCSUBClaimABCs, BenefitsAssignment, code=B]
,<asssignmentofbenefitsc, varchar(8000),> --[migrate to FBCSUBClaimABCs, BenefitsAssignment, code=C]
,<priorpaymentsa, numeric(28,6),> --[migrate to FBCSUBClaimABCs, PriorPayment, code=A]
,<priorpaymentsb, numeric(28,6),> --[migrate to FBCSUBClaimABCs, PriorPayment, code=B]
,<priorpaymentsc, numeric(28,6),> --[migrate to FBCSUBClaimABCs, PriorPayment, code=C]
,<estamountduea, numeric(28,6),> --[migrate to FBCSUBClaimABCs, EstimatedAmountDue, code=A]
,<estamountdueb, numeric(28,6),> --[migrate to FBCSUBClaimABCs, EstimatedAmountDue, code=B]
,<estamountduec, numeric(28,6),> --[migrate to FBCSUBClaimABCs, EstimatedAmountDue, code=C]
,<providernpi, varchar(8000),> --[migrate to Providers]
,<insuredsnamea, varchar(8000),> --[migrate to FBCSUBClaimABCs, InsuredName, code=A]
,<insuredsnameb, varchar(8000),> --[migrate to FBCSUBClaimABCs, InsuredName, code=B]
,<insuredsnamec, varchar(8000),> --[migrate to FBCSUBClaimABCs, InsuredName, code=C]
,<patientsrelationshipa, varchar(8000),> --[migrate to FBCSUBClaimABCs, PatientRelationship, code=A]
,<patientsrelationshipb, varchar(8000),> --[migrate to FBCSUBClaimABCs, PatientRelationship, code=B]
,<patientsrelationshipc, varchar(8000),> --[migrate to FBCSUBClaimABCs, PatientRelationship, code=C]
,<insureduniqueida, varchar(8000),> --[migrate to FBCSUBClaimABCs, InsuredUniqueNumber, code=A]
,<insureduniqueidb, varchar(8000),> --[migrate to FBCSUBClaimABCs, InsuredUniqueNumber, code=B]
,<insureduniqueidc, varchar(8000),> --[migrate to FBCSUBClaimABCs, InsuredUniqueNumber, code=C]
,<groupnamea, varchar(8000),> --[migrate to FBCSUBClaimABCs, GroupName, code=A]
,<groupnameb, varchar(8000),> --[migrate to FBCSUBClaimABCs, GroupName, code=B]
,<groupnamec, varchar(8000),> --[migrate to FBCSUBClaimABCs, GroupName, code=C]
,<insurancegroupnoa, varchar(8000),> --[migrate to FBCSUBClaimABCs, InsuranceGroupName, code=A]
,<insurancegroupnob, varchar(8000),> --[migrate to FBCSUBClaimABCs, InsuranceGroupName, code=B]
,<insurancegroupnoc, varchar(8000),> --[migrate to FBCSUBClaimABCs, InsuranceGroupName, code=C]
,<admittingdiag, varchar(8000),> --[migrate to FBCSClaimDiagnoses, DiagnosisID, code=0 for admission]
,<pps_code, varchar(8000),> --ProspectivePaymentSystemCode
,<prinproccode, varchar(8000),> --[migrate to FBCSUBClaimABCs, ProcedureCodeID, code=0]
,<prinprocdate, datetime2(7),> --[migrate to FBCSUBClaimProcedureCodes, ProcedureDate, code=0]
,<otherproccodea, varchar(8000),> --[migrate to FBCSUBClaimProcedureCodes, ProcedureCodeID, code=A]
,<otherprocdatea, datetime2(7),> --[migrate to FBCSUBClaimProcedureCodes, ProcedureDate, code=A]
,<otherproccodeb, varchar(8000),> --[migrate to FBCSUBClaimProcedureCodes, ProcedureCodeID, code=B]
,<otherprocdateb, datetime2(7),> --[migrate to FBCSUBClaimProcedureCodes, ProcedureDate, code=B]
,<otherproccodec, varchar(8000),> --[migrate to FBCSUBClaimProcedureCodes, ProcedureCodeID, code=C]
,<otherprocdatec, datetime2(7),> --[migrate to FBCSUBClaimProcedureCodes, ProcedureDate, code=C]
,<otherproccoded, varchar(8000),> --[migrate to FBCSUBClaimProcedureCodes, ProcedureCodeID, code=D]
,<otherprocdated, datetime2(7),> --[migrate to FBCSUBClaimProcedureCodes, ProcedureDate, code=D]
,<otherproccodee, varchar(8000),> --[migrate to FBCSUBClaimProcedureCodes, ProcedureCodeID, code=E]
,<otherprocdatee, datetime2(7),> --[migrate to FBCSUBClaimProcedureCodes, ProcedureDate, code=E]
,<attendingphysiciannpi, varchar(8000),> --providers table
,<operationphysiciannpi, varchar(8000),> --providers table
,<operatingphysicianname, varchar(8000),> --OperatingPhysicianProviderID
,<otherphysiciannpia, varchar(8000),> --[migrate to FBCSUBClaimABCs, PhysicianProviderID, code=A]
,<otherphysiciannamea, varchar(8000),> --[migrate to FBCSUBClaimABCs, PhysicianProviderID, code=A]
,<otherphysiciannpib, varchar(8000),> --[migrate to FBCSUBClaimABCs, PhysicianProviderID, code=B]
,<otherphysiciannameb, varchar(8000),> --[migrate to FBCSUBClaimABCs, PhysicianProviderID, code=B]
,<paymentfacilityid, bigint,> --PaymentFacilityProviderID
,<admissiontype, varchar(8000),> --AdmissionTypeID
,<admissionsrc, varchar(8000),> --AdmissionSourceID
,<patientssn, varchar(8000),> --[migrate to Patients table]
,<repricersreferenceid, varchar(8000),> --RepricersReferenceNumber
,<healthnetproductcode, int,> --HealthnetProductCodeNumber
,<totalcharges, numeric(28,6),> --ChargeTotal
,<ip_alos, numeric(28,6),> --[migrate to FBCSUBClaimInpatientStays, AverageLengthOfStay]
,<ip_Approvedfrom, datetime2(7),> --[migrate to FBCSUBClaimInpatientStays, DateApprovedFrom]
,<ip_Approvedto, datetime2(7),> --[migrate to FBCSUBClaimInpatientStays, DateApprovedTo]
,<ip_drgnumber, bigint,> --[migrate to FBCSUBClaimInpatientStays, DiagnosisRelatedGroupID]
,<ip_drgprice, numeric(28,6),> --[migrate to FBCSUBClaimInpatientStays, DRGPrice]
,<ip_percentdrg, numeric(28,6),> --[migrate to FBCSUBClaimInpatientStays, DRGPercent]
,<ip_Disapprovedfrom, datetime2(7),> --[migrate to FBCSUBClaimInpatientStays, DateDisapprovedFrom]
,<ip_Disapprovedto, datetime2(7),> --[migrate to FBCSUBClaimInpatientStays, DateDisapprovedTo]
,<ip_Disapprovedreasons, varchar(8000),> --[migrate to FBCSUBClaimInpatientStays, DisapprovedReasons]
,<ip_priceperday, numeric(28,6),> --[migrate to FBCSUBClaimInpatientStays, PricePerDay]
,<ip_numDisapprovedda, numeric(28,6),> --[migrate to FBCSUBClaimInpatientStays, NumberDaysDisapproved]
,<ip_perdiem, numeric(28,6),> --[migrate to FBCSUBClaimInpatientStays, PerDiem]
,<edifacilitytorouteto, varchar(50),> --EDIFacilityToRouteTo
,<hero_claim_flag, char(1),> --IsHeroClaim
,<edibillingprov, varchar(2500),> --EDIBillingProviderID
,<edirenderingprov, varchar(2500),> --EDIRenderingProviderID
,<hacedi_id, varchar(8000),> --HACEDINumber
,<fpps_id, varchar(255),> --FPPSNumber
,<hacediupdatesent, bigint,> --IsHACEDIUpdateSent
,<dtreopened, datetime2(7),> --DateReopened
,<reopeneduser, bigint,> --ReopenedByFBCSUserID
,<reopenedreason, varchar(8000),> --ReopenedReason
,<sc_connected, int,> --IsServiceConnected
,<pos, varchar(50),> --PlaceOfService
,<totalsc, varchar(8000),> --TotalServiceConnected
,<meansteststatus, varchar(8000),> --MeansTestStatusID
,<dischargestatus, varchar(8000),> --DischargeStatusID
,<ip_drgweight, numeric(28,6),> --[migrate to FBCSUBClaimInpatientStays, DRGWeight]
,<dtsenttopayment, datetime2(7),> --DateSentToPayment
,<dtreturnedfrompayment, datetime2(7),> --DateReturnedFromPayment
,<primaryeligibility, varchar(500),> --PrimaryEligibility
,<previousimportedby, bigint,> --PreviousImportedByFBCSUserID
,<previouspreimportedby, bigint,> --PreviousPreImportedByFBCSUserID
,<admissionreasonid, bigint,> --AdmissionReasonID
,<dischargereasonid, bigint,> --DischargeReasonID
,<senttopaymentby, bigint,> --SentToPaymentByFBCSUserID
,<checknumber, varchar(50),> --CheckNumber
,<voucherdate, datetime2(7),> --DateVoucher
,<newocrmethod, int,> --IsNewOCRMethod
,<on30dayhold, int,> --IsOn30DayHold
,<taxidoverride, int,> --IsTaxIDOverride
,<claimmemo, varchar(255),> --ClaimMemo
,<bwclaim, int,> --IsBlackWhiteClaim
,<wrongclaimtype, int,> --IsWrongClaimType
,<verificationinprogressguid, varchar(50),> --VerficationInProgressGUID
,<senttocalculate, int,> --IsSentToCalculate
,<dtcalculated, datetime2(7),> --DateCalculated
,<dirty, int,> --IsDirty
,<lastcalcdjobnumber, varchar(100),> --LastCalculatedJobNumber
,<diag19, varchar(8000),> --[migrate to FBCSClaimDiagnoses, DiagnosisID, DiagnosisPosition 19]
,<diag20, varchar(8000),> --[migrate to FBCSClaimDiagnoses, DiagnosisID, DiagnosisPosition 20]
,<diag21, varchar(8000),> --[migrate to FBCSClaimDiagnoses, DiagnosisID, DiagnosisPosition 21]
,<diag22, varchar(8000),> --[migrate to FBCSClaimDiagnoses, DiagnosisID, DiagnosisPosition 22]
,<diag23, varchar(8000),> --[migrate to FBCSClaimDiagnoses, DiagnosisID, DiagnosisPosition 23]
,<diag24, varchar(8000),> --[migrate to FBCSClaimDiagnoses, DiagnosisID, DiagnosisPosition 24]
,<diag25, varchar(8000),> --[migrate to FBCSClaimDiagnoses, DiagnosisID, DiagnosisPosition 25]
,<diag1poa, varchar(8000),> --[migrate to FBCSClaimDiagnoses, PresentOnAdmissionCode, DiagnosisPosition 01]
,<diag2poa, varchar(8000),> --[migrate to FBCSClaimDiagnoses, PresentOnAdmissionCode, DiagnosisPosition 02]
,<diag3poa, varchar(8000),> --[migrate to FBCSClaimDiagnoses, PresentOnAdmissionCode, DiagnosisPosition 03]
,<diag4poa, varchar(8000),> --[migrate to FBCSClaimDiagnoses, PresentOnAdmissionCode, DiagnosisPosition 04]
,<diag5poa, varchar(8000),> --[migrate to FBCSClaimDiagnoses, PresentOnAdmissionCode, DiagnosisPosition 05]
,<diag6poa, varchar(8000),> --[migrate to FBCSClaimDiagnoses, PresentOnAdmissionCode, DiagnosisPosition 06]
,<diag7poa, varchar(8000),> --[migrate to FBCSClaimDiagnoses, PresentOnAdmissionCode, DiagnosisPosition 07]
,<diag8poa, varchar(8000),> --[migrate to FBCSClaimDiagnoses, PresentOnAdmissionCode, DiagnosisPosition 08]
,<diag9poa, varchar(8000),> --[migrate to FBCSClaimDiagnoses, PresentOnAdmissionCode, DiagnosisPosition 09]
,<diag10poa, varchar(8000),> --[migrate to FBCSClaimDiagnoses, PresentOnAdmissionCode, DiagnosisPosition 10]
,<diag11poa, varchar(8000),> --[migrate to FBCSClaimDiagnoses, PresentOnAdmissionCode, DiagnosisPosition 11]
,<diag12poa, varchar(8000),> --[migrate to FBCSClaimDiagnoses, PresentOnAdmissionCode, DiagnosisPosition 12]
,<diag13poa, varchar(8000),> --[migrate to FBCSClaimDiagnoses, PresentOnAdmissionCode, DiagnosisPosition 13]
,<diag14poa, varchar(8000),> --[migrate to FBCSClaimDiagnoses, PresentOnAdmissionCode, DiagnosisPosition 14]
,<diag15poa, varchar(8000),> --[migrate to FBCSClaimDiagnoses, PresentOnAdmissionCode, DiagnosisPosition 15]
,<diag16poa, varchar(8000),> --[migrate to FBCSClaimDiagnoses, PresentOnAdmissionCode, DiagnosisPosition 16]
,<diag17poa, varchar(8000),> --[migrate to FBCSClaimDiagnoses, PresentOnAdmissionCode, DiagnosisPosition 17]
,<diag18poa, varchar(8000),> --[migrate to FBCSClaimDiagnoses, PresentOnAdmissionCode, DiagnosisPosition 18]
,<diag19poa, varchar(8000),> --[migrate to FBCSClaimDiagnoses, PresentOnAdmissionCode, DiagnosisPosition 19]
,<diag20poa, varchar(8000),> --[migrate to FBCSClaimDiagnoses, PresentOnAdmissionCode, DiagnosisPosition 20]
,<diag21poa, varchar(8000),> --[migrate to FBCSClaimDiagnoses, PresentOnAdmissionCode, DiagnosisPosition 21]
,<diag22poa, varchar(8000),> --[migrate to FBCSClaimDiagnoses, PresentOnAdmissionCode, DiagnosisPosition 22]
,<diag23poa, varchar(8000),> --[migrate to FBCSClaimDiagnoses, PresentOnAdmissionCode, DiagnosisPosition 23]
,<diag24poa, varchar(8000),> --[migrate to FBCSClaimDiagnoses, PresentOnAdmissionCode, DiagnosisPosition 24]
,<diag25poa, varchar(8000),> --[migrate to FBCSClaimDiagnoses, PresentOnAdmissionCode, DiagnosisPosition 25]
,<occcode_box31a, varchar(8000),> --[migrate to FBCSUBClaimOccurrenceCodes, OccurrenceCodeID, referencecode=31a]
,<occdate_box31a, datetime2(7),> --[migrate to FBCSUBClaimOccurrenceCodes, DateFrom, referencecode=31a]
,<occcode_box31b, varchar(8000),> --[migrate to FBCSUBClaimOccurrenceCodes, OccurrenceCodeID, referencecode=31b]
,<occdate_box31b, datetime2(7),> --[migrate to FBCSUBClaimOccurrenceCodes, DateFrom, referencecode=31b]
,<occcode_box32a, varchar(8000),> --[migrate to FBCSUBClaimOccurrenceCodes, OccurrenceCodeID, referencecode=32a]
,<occdate_box32a, datetime2(7),> --[migrate to FBCSUBClaimOccurrenceCodes, DateFrom, referencecode=32a]
,<occcode_box32b, varchar(8000),> --[migrate to FBCSUBClaimOccurrenceCodes, OccurrenceCodeID, referencecode=32b]
,<occdate_box32b, datetime2(7),> --[migrate to FBCSUBClaimOccurrenceCodes, DateFrom, referencecode=32b]
,<occcode_box33a, varchar(8000),> --[migrate to FBCSUBClaimOccurrenceCodes, OccurrenceCodeID, referencecode=33a]
,<occdate_box33a, datetime2(7),> --[migrate to FBCSUBClaimOccurrenceCodes, DateFrom, referencecode=33a]
,<occcode_box33b, varchar(8000),> --[migrate to FBCSUBClaimOccurrenceCodes, OccurrenceCodeID, referencecode=33b]
,<occdate_box33b, datetime2(7),> --[migrate to FBCSUBClaimOccurrenceCodes, DateFrom, referencecode=33b]
,<occcode_box34a, varchar(8000),> --[migrate to FBCSUBClaimOccurrenceCodes, OccurrenceCodeID, referencecode=34a]
,<occdate_box34a, datetime2(7),> --[migrate to FBCSUBClaimOccurrenceCodes, DateFrom, referencecode=34a]
,<occcode_box34b, varchar(8000),> --[migrate to FBCSUBClaimOccurrenceCodes, OccurrenceCodeID, referencecode=34b]
,<occdate_box34b, datetime2(7),> --[migrate to FBCSUBClaimOccurrenceCodes, DateFrom, referencecode=34b]
,<occspancode_box35a, varchar(8000),> --[migrate to FBCSUBClaimOccurrenceCodes, OccurrenceCodeID, referencecode=35a]
,<occspanfrom_box35a, datetime2(7),> --[migrate to FBCSUBClaimOccurrenceCodes, DateFrom, referencecode=35a]
,<occspanthrough_box35a, datetime2(7),> --[migrate to FBCSUBClaimOccurrenceCodes, DateThrough, referencecode=35a]
,<occspancode_box35b, varchar(8000),> --[migrate to FBCSUBClaimOccurrenceCodes, OccurrenceCodeID, referencecode=35b]
,<occspanfrom_box35b, datetime2(7),> --[migrate to FBCSUBClaimOccurrenceCodes, DateFrom, referencecode=35b]
,<occspanthrough_box35b, datetime2(7),> --[migrate to FBCSUBClaimOccurrenceCodes, DateThrough, referencecode=35b]
,<occspancode_box36a, varchar(8000),> --[migrate to FBCSUBClaimOccurrenceCodes, OccurrenceCodeID, referencecode=35a]
,<occspanfrom_box36a, datetime2(7),> --[migrate to FBCSUBClaimOccurrenceCodes, DateFrom, referencecode=36a]
,<occspanthrough_box36a, datetime2(7),> --[migrate to FBCSUBClaimOccurrenceCodes, DateThrough, referencecode=36a]
,<occspancode_box36b, varchar(8000),> --[migrate to FBCSUBClaimOccurrenceCodes, OccurrenceCodeID, referencecode=36b]
,<occspanfrom_box36b, datetime2(7),> --[migrate to FBCSUBClaimOccurrenceCodes, DateFrom, referencecode=36b]
,<occspanthrough_box36b, datetime2(7),> --[migrate to FBCSUBClaimOccurrenceCodes, DateThrough, referencecode=36b]
,<valuecode_box39a, varchar(8000),> --[migrate to FBCSUBClaimValueCodes, ValueCodeID, referencecode=39a]
,<valuecodeamt_box39a, varchar(8000),> --[migrate to FBCSUBClaimValueCodes, ValueAmount, referencecode=39a]
,<valuecode_box39b, varchar(8000),> --[migrate to FBCSUBClaimValueCodes, ValueCodeID, referencecode=39b]
,<valuecodeamt_box39b, varchar(8000),> --[migrate to FBCSUBClaimValueCodes, ValueAmount, referencecode=39b]
,<valuecode_box39c, varchar(8000),> --[migrate to FBCSUBClaimValueCodes, ValueCodeID, referencecode=39c]
,<valuecodeamt_box39c, varchar(8000),> --[migrate to FBCSUBClaimValueCodes, ValueAmount, referencecode=39c]
,<valuecode_box39d, varchar(8000),> --[migrate to FBCSUBClaimValueCodes, ValueCodeID, referencecode=39d]
,<valuecodeamt_box39d, varchar(8000),> --[migrate to FBCSUBClaimValueCodes, ValueAmount, referencecode=39d]
,<valuecode_box40a, varchar(8000),> --[migrate to FBCSUBClaimValueCodes, ValueCodeID, referencecode=40a]
,<valuecodeamt_box40a, varchar(8000),> --[migrate to FBCSUBClaimValueCodes, ValueAmount, referencecode=40a]
,<valuecode_box40b, varchar(8000),> --[migrate to FBCSUBClaimValueCodes, ValueCodeID, referencecode=40b]
,<valuecodeamt_box40b, varchar(8000),> --[migrate to FBCSUBClaimValueCodes, ValueAmount, referencecode=40b]
,<valuecode_box40c, varchar(8000),> --[migrate to FBCSUBClaimValueCodes, ValueCodeID, referencecode=40c]
,<valuecodeamt_box40c, varchar(8000),> --[migrate to FBCSUBClaimValueCodes, ValueAmount, referencecode=40c]
,<valuecode_box40d, varchar(8000),> --[migrate to FBCSUBClaimValueCodes, ValueCodeID, referencecode=40d]
,<valuecodeamt_box40d, varchar(8000),> --[migrate to FBCSUBClaimValueCodes, ValueAmount, referencecode=40d]
,<valuecode_box41a, varchar(8000),> --[migrate to FBCSUBClaimValueCodes, ValueCodeID, referencecode=41a]
,<valuecodeamt_box41a, varchar(8000),> --[migrate to FBCSUBClaimValueCodes, ValueAmount, referencecode=41a]
,<valuecode_box41b, varchar(8000),> --[migrate to FBCSUBClaimValueCodes, ValueCodeID, referencecode=41b]
,<valuecodeamt_box41b, varchar(8000),> --[migrate to FBCSUBClaimValueCodes, ValueAmount, referencecode=41b]
,<valuecode_box41c, varchar(8000),> --[migrate to FBCSUBClaimValueCodes, ValueCodeID, referencecode=41c]
,<valuecodeamt_box41c, varchar(8000),> --[migrate to FBCSUBClaimValueCodes, ValueAmount, referencecode=41c]
,<valuecode_box41d, varchar(8000),> --[migrate to FBCSUBClaimValueCodes, ValueCodeID, referencecode=41d]
,<valuecodeamt_box41d, varchar(8000),> --[migrate to FBCSUBClaimValueCodes, ValueAmount, referencecode=41d]
,<ocrcompleted, int,> --IsOCRCompleted
,<otherproccodef, varchar(8000),> --[migrate to FBCSUBClaimProcedureCodes, ProcedureCodeID, code=F]
,<otherproccodeg, varchar(8000),> --[migrate to FBCSUBClaimProcedureCodes, ProcedureCodeID, code=G]
,<otherproccodeh, varchar(8000),> --[migrate to FBCSUBClaimProcedureCodes, ProcedureCodeID, code=H]
,<otherproccodei, varchar(8000),> --[migrate to FBCSUBClaimProcedureCodes, ProcedureCodeID, code=I]
,<otherproccodej, varchar(8000),> --[migrate to FBCSUBClaimProcedureCodes, ProcedureCodeID, code=J]
,<otherproccodek, varchar(8000),> --[migrate to FBCSUBClaimProcedureCodes, ProcedureCodeID, code=K]
,<otherproccodel, varchar(8000),> --[migrate to FBCSUBClaimProcedureCodes, ProcedureCodeID, code=L]
,<otherproccodem, varchar(8000),> --[migrate to FBCSUBClaimProcedureCodes, ProcedureCodeID, code=M]
,<otherproccoden, varchar(8000),> --[migrate to FBCSUBClaimProcedureCodes, ProcedureCodeID, code=N]
,<otherproccodeo, varchar(8000),> --[migrate to FBCSUBClaimProcedureCodes, ProcedureCodeID, code=O]
,<otherproccodep, varchar(8000),> --[migrate to FBCSUBClaimProcedureCodes, ProcedureCodeID, code=P]
,<otherproccodeq, varchar(8000),> --[migrate to FBCSUBClaimProcedureCodes, ProcedureCodeID, code=Q]
,<otherproccoder, varchar(8000),> --[migrate to FBCSUBClaimProcedureCodes, ProcedureCodeID, code=R]
,<otherproccodes, varchar(8000),> --[migrate to FBCSUBClaimProcedureCodes, ProcedureCodeID, code=S]
,<otherproccodet, varchar(8000),> --[migrate to FBCSUBClaimProcedureCodes, ProcedureCodeID, code=T]
,<otherproccodeu, varchar(8000),> --[migrate to FBCSUBClaimProcedureCodes, ProcedureCodeID, code=U]
,<otherproccodev, varchar(8000),> --[migrate to FBCSUBClaimProcedureCodes, ProcedureCodeID, code=V]
,<otherproccodew, varchar(8000),> --[migrate to FBCSUBClaimProcedureCodes, ProcedureCodeID, code=W]
,<otherproccodex, varchar(8000),> --[migrate to FBCSUBClaimProcedureCodes, ProcedureCodeID, code=X]
,<otherproccodey, varchar(8000),> --[migrate to FBCSUBClaimProcedureCodes, ProcedureCodeID, code=Y]
,<otherproccodez, varchar(8000),> --[migrate to FBCSUBClaimProcedureCodes, ProcedureCodeID, code=Z]
,<attending_physician_taxonomy, varchar(8000),> --AttendingPhysicianProviderTaxonomy
,<billing_provider_taxonomy, varchar(8000),> --BillingProviderTaxonomy
,<referring_provider_taxonomy, varchar(8000),> --ReferringProviderTaxonomy
,<pay_to_provider_taxonomy, varchar(8000),> --PayToProviderTaxonomy
,<payment_reset_date, datetime2(7),> --DatePaymentReset
,<payment_reset, int,> --IsPaymentReset
,<senttobescored, int,> --SentToBeScored
,<senttobescoreddtstamp, datetime2(7),> --DateSentToBeScored
,<scoringrcvddtstamp, datetime2(7),> --DateScoringReceived
,<pitstatus, bigint,> --PITStatus
,<terminalstatusupdatesentdtstamp, datetime2(7),> --DateTerminalStatusUpdateSent
,<pitrejected, int,> --IsPITRejected
,<terminalstatusfeedcreated, int,> --IsTerminalStatusFeedCreated
,<claimchosenamounttopay, numeric(28,6),> --ClaimChosenAmountToPay
,<numberofvisitstodeduct, int,> --NumberOfVisitsToDeduct
,<effectivedate, int,> --IsEffectiveDate
,<insjustificationcode, int,> --InsuranceJustificationCode
,<filingeffectivedate, datetime2(7),> --DateFilingEffective
,<other_hlth_ins_present, varchar(1),> --IsOtherHealthInsurancePresent
,<hasdups, int,> --HasDuplicates
,<nuccversion, varchar(5),> --NUCCVersion
,<admissionhour, varchar(4),> --AdmissionHour
,<dischargehour, varchar(4),> --DischargeHour
,<ediag2, varchar(30),> --[migrate to FBCSClaimDiagnoses, DiagnosisID, DiagnosisPosition eTrauma02]
,<ediag3, varchar(30),> --[migrate to FBCSClaimDiagnoses, DiagnosisID, DiagnosisPosition eTrauma03]
,<icdindicator, int,> --[migrate to DiagnosisCodes, ICDIndicator]
,<notetxt, varchar(500),> --NoteText
,<patdiagbox70, varchar(30),> --[migrate to FBCSClaimDiagnoses, DiagnosisID, DiagnosisPosition Visit70]
,<specialprovcat, varchar(50),> --SpecialProvisionCategory
,<thread__id, numeric(18,0),> --skip this field
*/
--foreign keys
--TODO: Determine if there will be a claims table. If so, uncomment the following. Otherwise, assume no FK need on ClaimID
--ALTER TABLE dbo.FBCSUBClaims
-- ADD CONSTRAINT FK_FBCSUBClaims_ClaimID
-- FOREIGN KEY (ClaimID)
-- REFERENCES dbo.Claims (ClaimID)
--GO
ALTER TABLE dbo.FBCSUBClaims
ADD CONSTRAINT FK_FBCSUBClaims_Patients
FOREIGN KEY (PatientID)
REFERENCES dbo.Patients (PatientID)
GO
ALTER TABLE dbo.FBCSUBClaims
ADD CONSTRAINT FK_FBCSUBClaims_Stations
FOREIGN KEY (StationID)
REFERENCES dbo.Stations (StationID)
GO
ALTER TABLE dbo.FBCSUBClaims
ADD CONSTRAINT FK_FBCSUBClaims_VerifiedByFBCSUserID
FOREIGN KEY (VerifiedByFBCSUserID)
REFERENCES dbo.FBCSUsers (FBCSUserID)
GO
ALTER TABLE dbo.FBCSUBClaims
ADD CONSTRAINT FK_FBCSUBClaims_PreImportedByFBCSUserID
FOREIGN KEY (PreImportedByFBCSUserID)
REFERENCES dbo.FBCSUsers (FBCSUserID)
GO
ALTER TABLE dbo.FBCSUBClaims
ADD CONSTRAINT FK_FBCSUBClaims_ImportedByFBCSUserID
FOREIGN KEY (ImportedByFBCSUserID)
REFERENCES dbo.FBCSUsers (FBCSUserID)
GO
ALTER TABLE dbo.FBCSUBClaims
ADD CONSTRAINT FK_FBCSUBClaims_PreviousPreImportedByFBCSUserID
FOREIGN KEY (PreviousPreImportedByFBCSUserID)
REFERENCES dbo.FBCSUsers (FBCSUserID)
GO
ALTER TABLE dbo.FBCSUBClaims
ADD CONSTRAINT FK_FBCSUBClaims_PreviousImportedByFBCSUserID
FOREIGN KEY (PreviousImportedByFBCSUserID)
REFERENCES dbo.FBCSUsers (FBCSUserID)
GO
ALTER TABLE dbo.FBCSUBClaims
ADD CONSTRAINT FK_FBCSUBClaims_ReopenedByFBCSUserID
FOREIGN KEY (ReopenedByFBCSUserID)
REFERENCES dbo.FBCSUsers (FBCSUserID)
GO
ALTER TABLE dbo.FBCSUBClaims
ADD CONSTRAINT FK_FBCSUBClaims_SentToPaymentByFBCSUserID
FOREIGN KEY (SentToPaymentByFBCSUserID)
REFERENCES dbo.FBCSUsers (FBCSUserID)
GO
ALTER TABLE dbo.FBCSUBClaims
ADD CONSTRAINT FK_FBCSUBClaims_ProviderID
FOREIGN KEY (ProviderID)
REFERENCES dbo.FBCSProviders (ProviderID)
GO
ALTER TABLE dbo.FBCSUBClaims
ADD CONSTRAINT FK_FBCSUBClaims_PhysicianProviderID
FOREIGN KEY (PhysicianProviderID)
REFERENCES dbo.FBCSProviders (ProviderID)
GO
ALTER TABLE dbo.FBCSUBClaims
ADD CONSTRAINT FK_FBCSUBClaims_AttendingPhysicianProviderID
FOREIGN KEY (AttendingPhysicianProviderID)
REFERENCES dbo.FBCSProviders (ProviderID)
GO
ALTER TABLE dbo.FBCSUBClaims
ADD CONSTRAINT FK_FBCSUBClaims_OperatingPhysicianProviderID
FOREIGN KEY (OperatingPhysicianProviderID)
REFERENCES dbo.FBCSProviders (ProviderID)
GO
ALTER TABLE dbo.FBCSUBClaims
ADD CONSTRAINT FK_FBCSUBClaims_PaymentFacilityProviderID
FOREIGN KEY (PaymentFacilityProviderID)
REFERENCES dbo.FBCSProviders (ProviderID)
GO
ALTER TABLE dbo.FBCSUBClaims
ADD CONSTRAINT FK_FBCSUBClaims_EDIBillingProviderID
FOREIGN KEY (EDIBillingProviderID)
REFERENCES dbo.FBCSProviders (ProviderID)
GO
ALTER TABLE dbo.FBCSUBClaims
ADD CONSTRAINT FK_FBCSUBClaims_EDIRenderingProviderID
FOREIGN KEY (EDIRenderingProviderID)
REFERENCES dbo.FBCSProviders (ProviderID)
GO
ALTER TABLE dbo.FBCSUBClaims
ADD CONSTRAINT FK_FBCSUBClaims_EntryClaimTypeID
FOREIGN KEY (EntryClaimTypeID)
REFERENCES dbo.ClaimTypes (ClaimTypeID)
GO
ALTER TABLE dbo.FBCSUBClaims
ADD CONSTRAINT FK_FBCSUBClaims_ClaimStatusID
FOREIGN KEY (ClaimStatusID)
REFERENCES dbo.ClaimStatuses (ClaimStatusID)
GO
ALTER TABLE dbo.FBCSUBClaims
ADD CONSTRAINT FK_FBCSUBClaims_ImportedClaimStatusID
FOREIGN KEY (ImportedClaimStatusID)
REFERENCES dbo.ClaimStatuses (ClaimStatusID)
GO
ALTER TABLE dbo.FBCSUBClaims
ADD CONSTRAINT FK_FBCSUBClaims_AdmissionTypes
FOREIGN KEY (AdmissionTypeID)
REFERENCES dbo.AdmissionTypes (AdmissionTypeID)
GO
ALTER TABLE dbo.FBCSUBClaims
ADD CONSTRAINT FK_FBCSUBClaims_AdmissionSources
FOREIGN KEY (AdmissionSourceID)
REFERENCES dbo.AdmissionSources (AdmissionSourceID)
GO
ALTER TABLE dbo.FBCSUBClaims
ADD CONSTRAINT FK_FBCSUBClaims_ReasonID
FOREIGN KEY (ReasonID)
REFERENCES dbo.Reasons (ReasonID)
GO
ALTER TABLE dbo.FBCSUBClaims
ADD CONSTRAINT FK_FBCSUBClaims_AdmissionReasonID
FOREIGN KEY (AdmissionReasonID)
REFERENCES dbo.Reasons (ReasonID)
GO
ALTER TABLE dbo.FBCSUBClaims
ADD CONSTRAINT FK_FBCSUBClaims_DischargeReasonID
FOREIGN KEY (DischargeReasonID)
REFERENCES dbo.Reasons (ReasonID)
GO
ALTER TABLE dbo.FBCSUBClaims
ADD CONSTRAINT FK_FBCSUBClaims_SuspensionReasonID
FOREIGN KEY (SuspensionReasonID)
REFERENCES dbo.Reasons (ReasonID)
GO
ALTER TABLE dbo.FBCSUBClaims
ADD CONSTRAINT FK_FBCSUBClaims_RepricedStatuses
FOREIGN KEY (RepricedStatusID)
REFERENCES dbo.RepricedStatuses (RepricedStatusID)
GO
ALTER TABLE dbo.FBCSUBClaims
ADD CONSTRAINT FK_FBCSUBClaims_MeansTestStatuses
FOREIGN KEY (MeansTestStatusID)
REFERENCES dbo.MeansTestStatuses (MeansTestStatusID)
GO
ALTER TABLE dbo.FBCSUBClaims
ADD CONSTRAINT FK_FBCSUBClaims_DischargeStatuses
FOREIGN KEY (DischargeStatusID)
REFERENCES dbo.DischargeStatuses (DischargeStatusID)
GO
ALTER TABLE dbo.FBCSUBClaims
ADD CONSTRAINT FK_FBCSUBClaims_BatchLogs
FOREIGN KEY (BatchLogID)
REFERENCES dbo.BatchLogs (BatchLogID)
GO
--indexes
/**
--Create clustered columnstore index that doesn't exist yet
CREATE CLUSTERED COLUMNSTORE INDEX CCIX_FBCSUBClaims ON dbo.FBCSUBClaims
WITH
(
/*DROP_EXISTING = ON --if the index doesn't exist and this is active,
-- the code will go boom
,*/ MAXDOP = 1 --while 0 uses max degrees of parallelism, 1 in
-- this case helps tighten the ordering when
-- initially using a traditional clustered index
--SQL 2016-- , COMPRESSION_DELAY = 10 --in minutes; increase to 60 if doing OLTP to
--maintain data in deltarowgroup for 60 minutes
, DATA_COMPRESSION = COLUMNSTORE --or COLUMNSTORE_ARCHIVE
)
ON CoreData --keep the clustered columnstore with the data filegroup rather than index filegroup
GO
**/
--TODO: Are the traditional non-clustered indexes still needed?
CREATE NONCLUSTERED INDEX IX_FBCSUBClaims_PatientID ON dbo.FBCSUBClaims
(
PatientID
) WITH (
PAD_INDEX = ON, --If want to use a Fill Factor, then PAD_INDEX must = ON
FILLFACTOR = 100, --100 = max fill; set to 90 if going to insert new values incrementally; 100 if doing bulk load
SORT_IN_TEMPDB = ON, -- sorts the index in the TempDB; default = OFF
IGNORE_DUP_KEY = OFF,
STATISTICS_NORECOMPUTE = OFF,
STATISTICS_INCREMENTAL = OFF,
DROP_EXISTING = OFF,
ONLINE = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON,
MAXDOP = 0, --degrees of parallelism, controls how many CPUs to use; 0 = default (all available), max = 64
DATA_COMPRESSION = PAGE --use page compression; default = NONE
)
ON CoreIndex
GO
CREATE NONCLUSTERED INDEX IX_FBCSUBClaims_StationID ON dbo.FBCSUBClaims
(
StationID
) WITH (
PAD_INDEX = ON, --If want to use a Fill Factor, then PAD_INDEX must = ON
FILLFACTOR = 100, --100 = max fill; set to 90 if going to insert new values incrementally; 100 if doing bulk load
SORT_IN_TEMPDB = ON, -- sorts the index in the TempDB; default = OFF
IGNORE_DUP_KEY = OFF,
STATISTICS_NORECOMPUTE = OFF,
STATISTICS_INCREMENTAL = OFF,
DROP_EXISTING = OFF,
ONLINE = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON,
MAXDOP = 0, --degrees of parallelism, controls how many CPUs to use; 0 = default (all available), max = 64
DATA_COMPRESSION = PAGE --use page compression; default = NONE
)
ON CoreIndex
GO
CREATE NONCLUSTERED INDEX IX_FBCSUBClaims_DateFrom ON dbo.FBCSUBClaims
(
DateFrom
) WITH (
PAD_INDEX = ON, --If want to use a Fill Factor, then PAD_INDEX must = ON
FILLFACTOR = 100, --100 = max fill; set to 90 if going to insert new values incrementally; 100 if doing bulk load
SORT_IN_TEMPDB = ON, -- sorts the index in the TempDB; default = OFF
IGNORE_DUP_KEY = OFF,
STATISTICS_NORECOMPUTE = OFF,
STATISTICS_INCREMENTAL = OFF,
DROP_EXISTING = OFF,
ONLINE = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON,
MAXDOP = 0, --degrees of parallelism, controls how many CPUs to use; 0 = default (all available), max = 64
DATA_COMPRESSION = PAGE --use page compression; default = NONE
)
ON CoreIndex
GO
CREATE NONCLUSTERED INDEX IX_FBCSUBClaims_DateTo ON dbo.FBCSUBClaims
(
DateTo
) WITH (
PAD_INDEX = ON, --If want to use a Fill Factor, then PAD_INDEX must = ON
FILLFACTOR = 100, --100 = max fill; set to 90 if going to insert new values incrementally; 100 if doing bulk load
SORT_IN_TEMPDB = ON, -- sorts the index in the TempDB; default = OFF
IGNORE_DUP_KEY = OFF,
STATISTICS_NORECOMPUTE = OFF,
STATISTICS_INCREMENTAL = OFF,
DROP_EXISTING = OFF,
ONLINE = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON,
MAXDOP = 0, --degrees of parallelism, controls how many CPUs to use; 0 = default (all available), max = 64
DATA_COMPRESSION = PAGE --use page compression; default = NONE
)
ON CoreIndex
GO
--constraints
--Metadata descriptions for each field
EXECUTE sp_addextendedproperty
N'MS_Description', N'Primary Key, unique to EPRS; authoritative source table is A06.CDWWork.FBCS.ub92',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaims',
N'COLUMN', N'FBCSUBClaimID'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Foreign key, unique to EPRS, relates to Patients table, source field: box2 by way of SPatient/DFN',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaims',
N'COLUMN', N'PatientID'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Foreign key, unique to EPRS, relates to Stations table, source field: station__no + facility',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaims',
N'COLUMN', N'StationID'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Foreign key, unique to EPRS, relates to FBCS Users table, source field: verifiedby',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaims',
N'COLUMN', N'VerifiedByFBCSUserID'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Foreign key, unique to EPRS, relates to FBCS Users table, source field: preimportedby',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaims',
N'COLUMN', N'PreImportedByFBCSUserID'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Foreign key, unique to EPRS, relates to FBCS Users table, source field: importedby',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaims',
N'COLUMN', N'ImportedByFBCSUserID'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Foreign key, unique to EPRS, relates to FBCS Users table, source field: previouspreimportedby',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaims',
N'COLUMN', N'PreviousPreImportedByFBCSUserID'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Foreign key, unique to EPRS, relates to FBCS Users table, source field: previousimportedby',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaims',
N'COLUMN', N'PreviousImportedByFBCSUserID'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Foreign key, unique to EPRS, relates to FBCS Users table, source field: reopeneduser',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaims',
N'COLUMN', N'ReopenedByFBCSUserID'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Foreign key, unique to EPRS, relates to FBCS Users table, source field: senttopaymentby',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaims',
N'COLUMN', N'SentToPaymentByFBCSUserID'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Foreign key, unique to EPRS, relates to Providers table, source field: provider + providernpi',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaims',
N'COLUMN', N'ProviderID'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Foreign key, unique to EPRS, relates to Providers table, source field: box1',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaims',
N'COLUMN', N'PhysicianProviderID'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Foreign key, unique to EPRS, relates to Providers table, source field: attendingphysicianid',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaims',
N'COLUMN', N'AttendingPhysicianProviderID'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Foreign key, unique to EPRS, relates to Providers table, source field: operatingphysicianname + operationphysiciannpi',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaims',
N'COLUMN', N'OperatingPhysicianProviderID'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Foreign key, unique to EPRS, relates to Providers table, source field: paymentfacilityid',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaims',
N'COLUMN', N'PaymentFacilityProviderID'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Foreign key, unique to EPRS, relates to Providers table, source field: edi_bill_prov_name_box33',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaims',
N'COLUMN', N'EDIBillingProviderID'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Foreign key, unique to EPRS, relates to Providers table, source field: edirenderingprov',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaims',
N'COLUMN', N'EDIRenderingProviderID'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Foreign key, unique to EPRS, relates to Claim Types table, source field: claimentrytype',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaims',
N'COLUMN', N'EntryClaimTypeID'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Foreign key, unique to EPRS, relates to Claim Statuses table, source field: status',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaims',
N'COLUMN', N'ClaimStatusID'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Foreign key, unique to EPRS, relates to Claim Statuses table, source field: importednote',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaims',
N'COLUMN', N'ImportedClaimStatusID'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Foreign key, unique to EPRS, relates to Admission Types table, source field: admissiontype',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaims',
N'COLUMN', N'AdmissionTypeID'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Foreign key, unique to EPRS, relates to Admission Sources table, source field: admissionsrc',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaims',
N'COLUMN', N'AdmissionSourceID'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Foreign key, unique to EPRS, relates to Reasons table, source field: reasonid',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaims',
N'COLUMN', N'ReasonID'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Foreign key, unique to EPRS, relates to Reasons table, source field: admissionreasonid',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaims',
N'COLUMN', N'AdmissionReasonID'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Foreign key, unique to EPRS, relates to Reasons table, source field: dischargereasonid',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaims',
N'COLUMN', N'DischargeReasonID'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Foreign key, unique to EPRS, relates to Reasons table, source field: suspensionreason',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaims',
N'COLUMN', N'SuspensionReasonID'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Foreign key, unique to EPRS, relates to Repriced Statuses table, source field: repricedstatus',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaims',
N'COLUMN', N'RepricedStatusID'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Foreign key, unique to EPRS, relates to Means Test Statuses table, source field: meansteststatus',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaims',
N'COLUMN', N'MeansTestStatusID'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Foreign key, unique to EPRS, relates to Discharge Statuses table, source field: dischargestatus',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaims',
N'COLUMN', N'DischargeStatusID'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Foreign key, unique to EPRS, relates to Batch Logs table',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaims',
N'COLUMN', N'BatchLogID'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Identifier from CDW FBCS UB92, source field: id',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaims',
N'COLUMN', N'CDWUBNumber'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'ID of the corresponding claim image saved in FBCS Images Database, source field: imageid',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaims',
N'COLUMN', N'FBCSImageNumber'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'The scanned batch ID to which the claim has been assigned to when scanned into FBCS, source field: batchid',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaims',
N'COLUMN', N'FBCSBatchNumber'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'ETS Control Num assigned for that claim, source field: repricersreferenceid',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaims',
N'COLUMN', N'RepricersReferenceNumber'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Federal Tax ID, source field: box5',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaims',
N'COLUMN', N'FederalTaxNumber'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Filtered federal Tax ID, source field: taxidclean',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaims',
N'COLUMN', N'CleanTaxNumber'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Federal Personal Payroll System ID, source field: fpps_id',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaims',
N'COLUMN', N'FPPSNumber'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Patient control number, source field: box3',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaims',
N'COLUMN', N'PatientControlNumber'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Unique number that is assigned for a claim for the initial claim scrub, source field: jobnumber',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaims',
N'COLUMN', N'JobNumber'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Unique ID assigned for HACEDI claims, source field: hacdei_id',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaims',
N'COLUMN', N'HACEDINumber'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Check number assigned to the claim when the claim is vouchered, source field: checknumber',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaims',
N'COLUMN', N'CheckNumber'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Original referral number, source field: box22b',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaims',
N'COLUMN', N'OriginalReferralNumber'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Records the date and time of UB Claim entry for the first time, source: dtstamp',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaims',
N'COLUMN', N'DateAdded'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Date from, source field: box6from',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaims',
N'COLUMN', N'DateFrom'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Date from, source field: box6to',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaims',
N'COLUMN', N'DateTo'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Date and time when the claim verified, source field: verifieddtstamp',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaims',
N'COLUMN', N'DateVerified'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Date created, source field: creationdate',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaims',
N'COLUMN', N'DateCreated'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Date and time the claim was distributed, source field: preimporteddtstamp',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaims',
N'COLUMN', N'DatePreImported'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Date and time when the claim is Assigned/Routed, source field: importeddtstamp',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaims',
N'COLUMN', N'DateImported'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Date and time when the claim is being verified, source field: verificationinprogressdtstamp',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaims',
N'COLUMN', N'DateVerficationInProgress'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Admission date, source field: admissiondate',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaims',
N'COLUMN', N'DateAdmission'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Date claim was reopened, source field: dtreopened',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaims',
N'COLUMN', N'DateReopened'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Date when the claim is sent to payment module, source field: dtsenttopayment',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaims',
N'COLUMN', N'DateSentToPayment'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Date when the claim is returned from payment module, source field: dtreturnedfrompayment',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaims',
N'COLUMN', N'DateReturnedFromPayment'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Date when the claim is vouchered, source field: voucherdate',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaims',
N'COLUMN', N'DateVoucher'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Date when the claim is calculated, source field: dtcalculated',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaims',
N'COLUMN', N'DateCalculated'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Date when the claim payment is reset, source field: payment_reset_date',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaims',
N'COLUMN', N'DatePaymentReset'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Date and time when the claim was sent to be scored, source field: senttobescoreddtstamp',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaims',
N'COLUMN', N'DateSentToBeScored'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Date and time when the score was received, source field: scoringrcvddtstamp',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaims',
N'COLUMN', N'DateScoringReceived'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Date that terminal status update was sent, source field: terminalstatusupdatesentdtstamp',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaims',
N'COLUMN', N'DateTerminalStatusUpdateSent'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Date that filling was effective, source field: filingeffectivedate',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaims',
N'COLUMN', N'DateFilingEffective'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Total charges, source field: totalcharges',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaims',
N'COLUMN', N'ChargeTotal'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Total amount the clerk wishes to pay, source field: claimchosenamounttopay',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaims',
N'COLUMN', N'ClaimChosenAmountToPay'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Inpatient amount paid, source field: inpatientamtpaid',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaims',
N'COLUMN', N'InpatientAmountPaid'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'VA allowable total, source field: varepricedamount',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaims',
N'COLUMN', N'VARepricedAmount'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Healthnet vendor claim repriced amount, source field: healthnetrepricedamt',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaims',
N'COLUMN', N'HealthnetRepricedAmount'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Number of days Approved, source field: Approveddays',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaims',
N'COLUMN', N'DaysApproved'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Number of visits to deduct, source field: numberofvisitstodeduct',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaims',
N'COLUMN', N'NumberOfVisitsToDeduct'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Prospective payment system code, source field: pps_code',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaims',
N'COLUMN', N'ProspectivePaymentSystemCode'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Taxonomy applied to referring provider, source field: referring_provider_taxonomy',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaims',
N'COLUMN', N'ReferringProviderTaxonomy'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Taxonomy applied to attending physician provider, source field: attending_physician_taxonomy',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaims',
N'COLUMN', N'AttendingPhysicianProviderTaxonomy'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Taxonomy applied to billing provider, source field: billing_provider_taxonomy',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaims',
N'COLUMN', N'BillingProviderTaxonomy'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Taxonomy applied to pay-to provider, source field: pay_to_provider_taxonomy',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaims',
N'COLUMN', N'PayToProviderTaxonomy'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'VAMC or VISN the claim is routed to, source field: edifacilitytorouteto',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaims',
N'COLUMN', N'EDIFacilityToRouteTo'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Type of bill (numeric), source field: box4',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaims',
N'COLUMN', N'BillType'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'VA Fund Control Point, source field: vafcp',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaims',
N'COLUMN', N'VAFundControlPoint'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'VA Batch Number, source field: vafb',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaims',
N'COLUMN', N'VABatchNumber'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'VA Invoice Number, source field: vain',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaims',
N'COLUMN', N'VAInvoiceNumber'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'VA Obligation Number, source field: vaon',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaims',
N'COLUMN', N'VAObligationNumber'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'VA Authorization ID of the authorization that has been linked to the claim, source field: authnum',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaims',
N'COLUMN', N'AuthorizationNumber'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Healthnet vendor product code number, source field: healthnetproductcode',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaims',
N'COLUMN', N'HealthnetProductCodeNumber'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Insurance justification code number, source field: insjustificationcode',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaims',
N'COLUMN', N'InsuranceJustificationCode'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Last calculated job number, source field: lastcalcdjobnumber',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaims',
N'COLUMN', N'LastCalculatedJobNumber'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Patient type (1=Inpatient, 2=Outpatient), source field: patienttypeid',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaims',
N'COLUMN', N'PatientType'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Accident state, source field: accidentstate',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaims',
N'COLUMN', N'AccidentState'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Billable insurance, source field: billable_insurance',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaims',
N'COLUMN', N'BillableInsurance'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Place of service, source field: pos',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaims',
N'COLUMN', N'PlaceOfService'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Special provision category, source field: specialprovcat',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaims',
N'COLUMN', N'SpecialProvisionCategory'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'External cause of injury, source field: eci',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaims',
N'COLUMN', N'ExternalCauseInjury'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Total service connected, source field: totalsc',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaims',
N'COLUMN', N'TotalServiceConnected'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Service Connected Conditions, source field: sc_conditions',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaims',
N'COLUMN', N'ServiceConnectedConditions'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Primary eligibility, source field: primaryeligibility',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaims',
N'COLUMN', N'PrimaryEligibility'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Suspended note, source field: suspendednote',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaims',
N'COLUMN', N'SuspendedNote'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Rejected note, source field: rejectednote',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaims',
N'COLUMN', N'RejectedNote'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Note text, source field: notetxt',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaims',
N'COLUMN', N'NoteText'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Reopened reason, source field: reopenedreason',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaims',
N'COLUMN', N'ReopenedReason'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Claim memo, source field: claimmemo',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaims',
N'COLUMN', N'ClaimMemo'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Verification in progress GUID, source field: verificationinprogressguid',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaims',
N'COLUMN', N'VerficationInProgressGUID'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'NUCC Version, source field: nuccversion',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaims',
N'COLUMN', N'NUCCVersion'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Sent to be scored (values=-3, -2, -1, or 0), source field: senttobescored',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaims',
N'COLUMN', N'SentToBeScored'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'PIT status (values=0-5 or null), source field: pitstatus',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaims',
N'COLUMN', N'PITStatus'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'True/False, Has claim been verified (1=yes/true, 0=no/false), source field: verified',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaims',
N'COLUMN', N'IsVerified'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'True/False, Has claim been rejected (1=yes/true, 0=no/false), source field: rejected',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaims',
N'COLUMN', N'IsRejected'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'True/False, Has claim been imported (1=yes/true, 0=no/false), source field: imported',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaims',
N'COLUMN', N'IsImported'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'True/False, Has claim been scrubbed (1=yes/true, 0=no/false), source field: scrubbed',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaims',
N'COLUMN', N'IsScrubbed'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'True/False, Have documents been attached (1=yes/true, 0=no/false), source field: documentsattached',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaims',
N'COLUMN', N'IsDocumentsAttached'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'True/False, Is the claim an inpatient claim (1=yes/true, 0=no/false), source field: inpatient',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaims',
N'COLUMN', N'IsInpatient'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'True/False, Is the claim a contract claim (1=yes/true, 0=no/false), source field: contractclaim',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaims',
N'COLUMN', N'IsContractClaim'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'True/False, Is the claim a hero claim (1=yes/true, 0=no/false), source field: hero_claim_flag',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaims',
N'COLUMN', N'IsHeroClaim'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'True/False, Has HAC EDI update been sent (1=yes/true, 0=no/false), source field: hero_claim_flag',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaims',
N'COLUMN', N'IsHACEDIUpdateSent'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'True/False, Is the claim service connected (1=yes/true, 0=no/false), source field: sc_connected',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaims',
N'COLUMN', N'IsServiceConnected'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'True/False, Has the tax ID been overriden (1=yes/true, 0=no/false), source field: taxidoverride',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaims',
N'COLUMN', N'IsTaxIDOverride'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'True/False, Is claim black and white (1=yes/true, 0=no/false), source field: bwclaim',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaims',
N'COLUMN', N'IsBlackWhiteClaim'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'True/False, Is identified claim type the wrong claim type (1=yes/true, 0=no/false), source field: wrongclaimtype',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaims',
N'COLUMN', N'IsWrongClaimType'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'True/False, Has claim been sent for calculation (1=yes/true, 0=no/false), source field: senttocalculate',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaims',
N'COLUMN', N'IsSentToCalculate'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'True/False, Is the claim dirty (1=yes/true, 0=no/false), source field: dirty',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaims',
N'COLUMN', N'IsDirty'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'True/False, Has the claim payment been reset (1=yes/true, 0=no/false), source field: payment_reset',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaims',
N'COLUMN', N'IsPaymentReset'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'True/False, Is PIT rejected (1=yes/true, 0=no/false), source field: pitrejected',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaims',
N'COLUMN', N'IsPITRejected'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'True/False, Was terminal status feed created (1=yes/true, 0=no/false), source field: terminalstatusfeedcreated',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaims',
N'COLUMN', N'IsTerminalStatusFeedCreated'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'True/False, Is date effective (1=yes/true, 0=no/false), source field: effectivedate',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaims',
N'COLUMN', N'IsEffectiveDate'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'True/False, Is there any other insurance present (1=yes/true, 0=no/false), source field: other_hlth_ins_present',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaims',
N'COLUMN', N'IsOtherHealthInsurancePresent'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'True/False, Is claim duplicate (1=yes/true, 0=no/false), source field: hasdups',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaims',
N'COLUMN', N'HasDuplicates'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'True/False, Is on 30-day hold (1=yes/true, 0=no/false), source field: on30dayhold',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaims',
N'COLUMN', N'IsOn30DayHold'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'True/False, Has the claim been uploaded via OCR method (1=yes/true, 0=no/false), source field: newocrmethod',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaims',
N'COLUMN', N'IsNewOCRMethod'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'True/False, Is on OCR completed (1=yes/true, 0=no/false), source field: ocrcompleted',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSUBClaims',
N'COLUMN', N'IsOCRCompleted'
GO