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.FBCSHCFAClaims
(
--authoritative source:
--A06.CDWWork.FBCS.hcfa

--fields
FBCSHCFAClaimID 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
ReferringProviderID bigint, --EPRS assigned (FK), reference Providers, referringprovider
ReferringPhysicianProviderID bigint, --EPRS assigned (FK), reference Providers, box17
RenderingProviderID bigint, --EPRS assigned (FK), reference Providers, renderingproviderid
EDIRenderingProviderID bigint, --EPRS assigned (FK), reference Providers, edirenderingprov
RenderingPhysicianProviderID bigint, --EPRS assigned (FK), reference Providers, box31a
RenderingFacilityProviderID bigint, --EPRS assigned (FK), reference Providers, box32
EDIServiceFacilityProviderID bigint, --EPRS assigned (FK), reference Providers, edi_svc_fac_loc_name_box32
EDIBillingConcatProviderID bigint, --EPRS assigned (FK), reference Providers, edibillingprov
EDIBillingProviderID bigint, --EPRS assigned (FK), reference Providers, edi_bill_prov_name_box33
EDIPayToProviderID bigint, --EPRS assigned (FK), reference Providers, edi_pay_to_prov_name_box33
BillingFacilityProviderID bigint, --EPRS assigned (FK), reference Providers, box33a (+box33b)
EntryClaimTypeID tinyint, --EPRS assigned (FK), reference ClaimTypes, claimentrytype
ClaimStatusID tinyint, --EPRS assigned (FK), reference ClaimStatuses, status
ImportedClaimStatusID tinyint, --EPRS assigned (FK), reference ClaimStatuses, importednote
ReasonID smallint, --EPRS assigned (FK), reference Reasons, reasonid
SuspensionReasonID smallint, --EPRS assigned (FK), reference Reasons, suspensionreason
RepricedStatusID tinyint, --EPRS assigned (FK), reference RepricedStatuses, repricedstatus
MeansTestStatusID tinyint, --EPRS assigned (FK), reference MeansTestStatuses, meansteststatus
BatchLogID bigint, --EPRS assigned (FK), reference BatchLogs
CDWHCFANumber bigint, --id
FBCSClaimNumber bigint, --claim_id, TODO: Make as a FBCSClaimNumber for now, not sure if this is FBCS/HCFA-specific id or generic claim id
FBCSImageNumber bigint, --imageid
FBCSBatchNumber bigint, --batchid
RepricersReferenceNumber varchar(50), --repricersreferenceid
FederalTaxNumber varchar(50), --box25
CleanTaxNumber varchar(50), --taxidclean
FPPSNumber bigint, --fpps_id, Federal Personal Payroll System ID?
PatientAccountNumber varchar(4000), --box26
InsuredNumberSSN varchar(50), --box1a
JobNumber int, --jobnumber
HACEDINumber varchar(50), --hacdei_id
CheckNumber varchar(50), --checknumber
PreAuthorizationNumber varchar(30), --box23
OriginalReferralNumber varchar(30), --box22b
DateAdded datetime2(7), --dtstamp
DateVerified datetime2(7), --verifieddtstamp
DatePreImported datetime2(7), --preimporteddtstamp
DateImported datetime2(7), --importeddtstamp
DateSigned datetime2(7), --box31
DateVerficationInProgress datetime2(7), --verificationinprogressdtstamp
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
DateHospitalAdmission datetime2(7), --hospadmdt
DateHospitalDischarge datetime2(7), --hospdiscdt
ChargeTotal money, --box28
AmountPaid money, --box29
BalanceDue money, --box30
RepricedTotal money, --repricedtotal
RepricedAmountDue money, --repricedamountdue
HealthnetRepricedAmount money, --healthnetrepricedamt
NumberOfVisitsToDeduct smallint, --numberofvisitstodeduct
ICDIndicator tinyint, --icdindicator
ReferringPhysicianProviderNumber varchar(1000), --box17a, TODO: This field will need to be set to only take the LEFT 1000 characters
RenderingProviderBox varchar(1000), --boxrenderingprovider, TODO: This field will need to be set to only take the LEFT 1000 characters
RenderingProviderTaxonomy varchar(20), --rendering_prov_taxonomy
--RenderingFacilityProviderNPI varchar(20), --rendering_facility_npi
RenderingFacilityProviderNumber varchar(1000), --rendering_facility_id, TODO: This field will need to be set to only take the LEFT 1000 characters
--BillingProviderNPI varchar(20), --billing_provider_npi
BillingProviderNumber varchar(1000), --billing_provider_id
BillingProviderTaxonomy varchar(20), --billing_provider_taxonomy
EDIFacilityToRouteTo varchar(50), --edifacilitytorouteto
VAFundControlPoint varchar(8000), --vafcp
VABatchNumber varchar(8000), --vafb
VAInvoiceNumber varchar(8000), --vain
VAObligationNumber varchar(8000), --vaon
AuthorizationNumber varchar(500), --authnum
HealthnetProductCodeNumber tinyint, --healthnetproductcode
InsuranceJustificationCode smallint, --insjustificationcode
LastCalculatedJobNumber varchar(100), --lasatcalcdjobnumber
PatientType varchar(50), --patienttypeid, Replace 1 with Inpatient, 2 with Outpatient
InsuredName varchar(255), --box4
InsuredPolicyNumber varchar(1000), --box11, TODO: This field will need to be set to only take the LEFT 1000 characters
InsurancePlanName varchar(1000), --box11c, TODO: This field will need to be set to only take teh LEFT 1000 characters
BillableInsurance varchar(8000), --billable_insurance
SpecialProvisionCategory varchar(50), --specialprovcat
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
IsRepriced bit NULL, --repriced, 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
--when using clustered columnstore, this has to be nonclustered
CONSTRAINT PK_FBCSHCFAClaims PRIMARY KEY NONCLUSTERED --not clustered due to clust. columnstore index
(
FBCSHCFAClaimID 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,> --CDWHCFANumber
** ,<imageid, bigint,> --FBCSImageNumber
** ,<dropoutid, bigint,> --Ignore this field, no longer used
** ,<dtstamp, datetime2(7),> --DateAdded
** ,<verified, bigint,> --IsVerified
** ,<verifiedby, bigint,> --VerifiedByFBCSUserID (TODO: we need to get zraw._fbcsusers built and cookie cutter extractor done)
** ,<verifieddtstamp, datetime2(7),> --DateVerified
** ,<rejected, bigint,> --IsRejected
** ,<imported, bigint,> --IsImported
** ,<importednote, varchar(8000),> --ImportedStatusID
** ,<importedby, bigint,> --ImportedByFBCSUserID
** ,<importeddtstamp, datetime2(7),> --DateImported
** ,<box1a, varchar(8000),> --InsuredNumberSSN
** ,<box2, varchar(8000),> --PatientID
** ,<box211, varchar(8000),> [migrate to FBCSClaimDiagnoses, Diag Code 1]
** ,<box212, varchar(8000),> [migrate to FBCSClaimDiagnoses, Diag Code 2]
** ,<box213, varchar(8000),> [migrate to FBCSClaimDiagnoses, Diag Code 3]
** ,<box214, varchar(8000),> [migrate to FBCSClaimDiagnoses, Diag Code 4]
** ,<box25, varchar(8000),> --FederalTaxNumber
** ,<box26, varchar(8000),> --PatientAccountNumber
** ,<box28, numeric(28,6),> --ChargeTotal
** ,<box31, datetime2(7),> --DateSigned
** ,<box3dob, datetime2(7),> ----skip this field, will be using PatientID and pulling Patient info from SPatient to do links
** ,<box3sex, varchar(8000),> ----skip this field, will be using PatientID and pulling Patient info from SPatient to do links
** ,<box17, varchar(8000),> --ReferringPhysicianName
** ,<box17a, varchar(8000),> --ReferringPhysicianNumber
** ,<box29, numeric(28,6),> --AmountPaid
** ,<box30, numeric(28,6),> --BalanceDue
** ,<box32, varchar(8000),> --RenderingFacilityProviderID
** ,<box33a, varchar(8000),> --BillingFacilityProviderID
** ,<repricedtotal, numeric(28,6),> --RepricedTotal
** ,<repricedamountdue, numeric(28,6),> --RepricedAmountDue
** ,<repriced, int,> --IsRepriced
** ,<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]
** ,<box33b, varchar(8000),> --BillingFacilityAddress
** ,<offx, int,> --[skip this field, no longer used]
** ,<offy, int,> --[skip this field, no longer used]
** ,<scrubbed, int,> --IsScrubbed
** ,<reasonid, bigint,> --ReasonID
** ,<documentsattached, int,> --IsDocumentsAttached
** ,<taxidclean, varchar(8000),> --CleanTaxNumber
** ,<status, varchar(8000),> --ClaimStatusID
** ,<suspensionreasonid, bigint,> --SuspensionReasonID
** ,<suspendednote, varchar(8000),> --SuspendedNote
** ,<rejectednote, varchar(8000),> --RejectedNote
** ,<facility, bigint,> --[skip this field, doing a trade-in]
** ,<verificationinprogressdtstamp, datetime2(7),> --DateVerificationInProgress
** ,<vafcp, varchar(8000),> --VAFundControlPoint
** ,<vafb, varchar(8000),> --VABatchNumber
** ,<vain, varchar(8000),> --VAInvoiceNumber
** ,<vaon, varchar(8000),> --VAObligationNumber
** ,<authnum, varchar(8000),> --AuthorizationNumber
** ,<preimportedby, bigint,> --PreImportedByUserID
** ,<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]
** ,<edihcfatempid, bigint,> --[skip this field, no longer used]
** ,<va_referringphysician, varchar(8000),> --[skip this field, no longer used]
** ,<categoryofcare, varchar(8000),> --[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
** ,<claimentrytype, varchar(8000),> --ClaimEntryTypeID
** ,<batchid, bigint,> (relates to batchnumber in batchname table [FBCS])--FBCSBatchNumber
** ,<sc_conditions, varchar(8000),> --ServiceConnectedConditions
** ,<billable_insurance, varchar(8000),> --BillableInsurance
** ,<boxrenderingprovider, varchar(8000),> --RenderingProvider
** ,<patienttypeid, bigint,> --PatientType
** ,<repricersreferenceid, varchar(8000),> --RepricersReferenceNumber
** ,<healthnetproductcode, int,> --HealthnetProductCodeNumber
** ,<renderingproviderid, bigint,> --RenderingProviderID
** ,<referringprovider, varchar(8000),> --ReferringProviderID
** ,<facilityid, bigint,> --[skip this field, going to use this and station__no to figure out the real StationID to apply]
** ,<edifacilitytorouteto, varchar(50),> --EDIFacilityToRouteTo
** ,<hero_claim_flag, char(1),> --IsHeroClaim
** ,<edibillingprov, varchar(2500),> --EDIBillingConcatProviderID
** ,<edirenderingprov, varchar(2500),> --EDIRenderingProviderID
** ,<hacedi_id, varchar(8000),> --HACEDINumber
** ,<box31a, varchar(8000),> --RenderingPhysicianProviderID
** ,<fpps_id, varchar(255),> --FPPSNumber
** ,<hacediupdatesent, bigint,> --IsHACEDIUpdateSent
** ,<dtreopened, datetime2(7),> --DateReopened
** ,<reopeneduser, bigint,> --ReopenedByFBCSUserID
** ,<reopenedreason, varchar(8000),> --ReopenedReason
** ,<sc_connected, int,> --IsServiceConnected
** ,<totalsc, varchar(8000),> --TotalServiceConnected
** ,<meansteststatus, varchar(8000),> --MeansTestStatusID
** ,<dtsenttopayment, datetime2(7),> --DateSentToPayment
** ,<dtreturnedfrompayment, datetime2(7),> --DateReturnedFromPayment
** ,<primaryeligibility, varchar(500),> --PrimaryEligibility
** ,<previousimportedby, bigint,> --PreviousImportedByFBCSUserID
** ,<previouspreimportedby, bigint,> --PreviousPreImportedByFBCSUserID
** ,<senttopaymentby, bigint,> --SentToPaymentByFBCSUserID
** ,<checknumber, varchar(50),> --CheckNumber
** ,<voucherdate, datetime2(7),> --DateVoucher
** ,<newocrmethod, int,> --IsNewOCRMethod
** ,<taxidoverride, int,> --IsTaxiOverride
** ,<claimmemo, varchar(255),> --ClaimMemo
** ,<rendering_facility_npi, varchar(8000),> --RenderingFacilityProviderNPI
** ,<box4, varchar(8000),> --InsuredName
** ,<box11, varchar(8000),> --InsuredPolicyNumber
** ,<box11c, varchar(8000),> --InsurancePlanName
** ,<rendering_facility_id, varchar(8000),> --RenderingFacilityProviderNumber
** ,<billing_provider_npi, varchar(8000),> --BillingProviderNPI
** ,<billing_provider_id, varchar(8000),> --BillingProviderNumber
** ,<bwclaim, int,> --IsBlackWhiteClaim
** ,<wrongclaimtype, int,> --IsWrongClaimType
** ,<verificationinprogressguid, varchar(50),> VerficationInProgressGUID
** ,<senttocalculate, int,> --IsSentToCalculate
** ,<dtcalculated, datetime2(7),> --DateCalculated
** ,<dirty, int,> --IsDirty
** ,<lastcalcdjobnumber, varchar(100),> --LastCalculatedJobNumber
** ,<box215, varchar(8000),> --[migrate to FBCSClaimDiagnoses, Diag Code 5]
** ,<box216, varchar(8000),> --[migrate to FBCSClaimDiagnoses, Diag Code 6]
** ,<box217, varchar(8000),> --[migrate to FBCSClaimDiagnoses, Diag Code 7]
** ,<box218, varchar(8000),> --[migrate to FBCSClaimDiagnoses, Diag Code 8]
** ,<box219, varchar(8000),> --[migrate to FBCSClaimDiagnoses, Diag Code 9]
** ,<box2110, varchar(8000),> --[migrate to FBCSClaimDiagnoses, Diag Code 10]
** ,<box2111, varchar(8000),> --[migrate to FBCSClaimDiagnoses, Diag Code 11]
** ,<box2112, varchar(8000),> --[migrate to FBCSClaimDiagnoses, Diag Code 12]
** ,<ocrcompleted, int,> --IsOCRCompleted
** ,<rendering_prov_taxonomy, varchar(8000),> --RenderingProviderTaxonomy
** ,<pay_to_provider_taxonomy, varchar(8000),> --skip this, not used
** ,<billing_provider_taxonomy, varchar(8000),> --BillingProviderTaxonomy
** ,<referring_provider_taxonomy, varchar(8000),> --skip this, not used
** ,<box23, varchar(30),> --PreAuthorizationNumber
** ,<edi_pay_to_prov_name_box33, varchar(5000),> -- max len = 60; EDIPayToProviderID TODO: hcfa provider compare
** ,<edi_pay_to_prov_addr1_box33, varchar(5000),> -- max len = 55; EDIPayToProviderID
** ,<edi_pay_to_prov_city_box33, varchar(5000),> -- max len = 29; EDIPayToProviderID
** ,<edi_pay_to_prov_st_box33, varchar(5000),> -- max len = 2; EDIPayToProviderID
** ,<edi_pay_to_prov_zip_box33, varchar(5000),> -- max len = 9; EDIPayToProviderID
** ,<edi_svc_fac_loc_name_box32, varchar(5000),> -- max len = 60; EDIServiceFacilityProviderID
** ,<edi_svc_fac_loc_addr1_box32, varchar(5000),> -- max len = 55; EDIServiceFacilityProviderID
** ,<edi_svc_fac_loc_city_box32, varchar(5000),> -- max len = 29; EDIServiceFacilityProviderID
** ,<edi_svc_fac_loc_st_box32, varchar(5000),> -- max len = 2; EDIServiceFacilityProviderID
** ,<edi_svc_fac_loc_zip_box32, varchar(5000),> -- max len = 9; EDIServiceFacilityProviderID
** ,<edi_bill_prov_name_box33, varchar(5000),> -- max len = 60, EDIBillingProviderID TODO: BillingProvider <> RenderingProvider
** ,<edi_bill_prov_addr1_box33, varchar(5000),> -- max len = 55, EDIBillingProviderID
** ,<edi_bill_prov_city_box33, varchar(5000),> -- max len = 29, EDIBillingProviderID
** ,<edi_bill_prov_st_box33, varchar(5000),> -- max len = 2, EDIBillingProviderID
** ,<edi_bill_prov_zip_box33, varchar(5000),> -- max len = 9, EDIBillingProviderID
** ,<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
** ,<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
** ,<box22b, varchar(30),> --OriginalReferralNumber
** ,<icdindicator, int,> --ICDIndicator
** ,<hospadmdt, datetime2(7),> --DateHospitalAdmission
** ,<hospdiscdt, datetime2(7),> --DateHospitalDischarge
** ,<notetxt, varchar(500),> --NoteText
** ,<on30dayhold, int,> --IsOn30DayHold
** ,<specialprovcat, varchar(50),> --SpecialProvisionCategory
** ,<thread__id, numeric(18,0),>


*/
--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.FBCSHCFAClaims
-- ADD CONSTRAINT FK_FBCSHCFAClaims_ClaimID
-- FOREIGN KEY (ClaimID)
-- REFERENCES dbo.Claims (ClaimID)
--GO

ALTER TABLE dbo.FBCSHCFAClaims
ADD CONSTRAINT FK_FBCSHCFAClaims_Patients
FOREIGN KEY (PatientID)
REFERENCES dbo.Patients (PatientID)
GO

ALTER TABLE dbo.FBCSHCFAClaims
ADD CONSTRAINT FK_FBCSHCFAClaims_Stations
FOREIGN KEY (StationID)
REFERENCES dbo.Stations (StationID)
GO

ALTER TABLE dbo.FBCSHCFAClaims
ADD CONSTRAINT FK_FBCSHCFAClaims_VerifiedByFBCSUserID
FOREIGN KEY (VerifiedByFBCSUserID)
REFERENCES dbo.FBCSUsers (FBCSUserID)
GO

ALTER TABLE dbo.FBCSHCFAClaims
ADD CONSTRAINT FK_FBCSHCFAClaims_PreImportedByFBCSUserID
FOREIGN KEY (PreImportedByFBCSUserID)
REFERENCES dbo.FBCSUsers (FBCSUserID)
GO

ALTER TABLE dbo.FBCSHCFAClaims
ADD CONSTRAINT FK_FBCSHCFAClaims_ImportedByFBCSUserID
FOREIGN KEY (ImportedByFBCSUserID)
REFERENCES dbo.FBCSUsers (FBCSUserID)
GO

ALTER TABLE dbo.FBCSHCFAClaims
ADD CONSTRAINT FK_FBCSHCFAClaims_PreviousPreImportedByFBCSUserID
FOREIGN KEY (PreviousPreImportedByFBCSUserID)
REFERENCES dbo.FBCSUsers (FBCSUserID)
GO

ALTER TABLE dbo.FBCSHCFAClaims
ADD CONSTRAINT FK_FBCSHCFAClaims_PreviousImportedByFBCSUserID
FOREIGN KEY (PreviousImportedByFBCSUserID)
REFERENCES dbo.FBCSUsers (FBCSUserID)
GO

ALTER TABLE dbo.FBCSHCFAClaims
ADD CONSTRAINT FK_FBCSHCFAClaims_ReopenedByFBCSUserID
FOREIGN KEY (ReopenedByFBCSUserID)
REFERENCES dbo.FBCSUsers (FBCSUserID)
GO

ALTER TABLE dbo.FBCSHCFAClaims
ADD CONSTRAINT FK_FBCSHCFAClaims_SentToPaymentByFBCSUserID
FOREIGN KEY (SentToPaymentByFBCSUserID)
REFERENCES dbo.FBCSUsers (FBCSUserID)
GO

ALTER TABLE dbo.FBCSHCFAClaims
ADD CONSTRAINT FK_FBCSHCFAClaims_ProviderID
FOREIGN KEY (ProviderID)
REFERENCES dbo.FBCSProviders (ProviderID)
GO

ALTER TABLE dbo.FBCSHCFAClaims
ADD CONSTRAINT FK_FBCSHCFAClaims_ReferringProviderID
FOREIGN KEY (ReferringProviderID)
REFERENCES dbo.FBCSProviders (ProviderID)
GO

ALTER TABLE dbo.FBCSHCFAClaims
ADD CONSTRAINT FK_FBCSHCFAClaims_ReferringPhysicianProviderID
FOREIGN KEY (ReferringPhysicianProviderID)
REFERENCES dbo.FBCSProviders (ProviderID)
GO

ALTER TABLE dbo.FBCSHCFAClaims
ADD CONSTRAINT FK_FBCSHCFAClaims_RenderingProviderID
FOREIGN KEY (RenderingProviderID)
REFERENCES dbo.FBCSProviders (ProviderID)
GO

ALTER TABLE dbo.FBCSHCFAClaims
ADD CONSTRAINT FK_FBCSHCFAClaims_EDIRenderingProviderID
FOREIGN KEY (EDIRenderingProviderID)
REFERENCES dbo.FBCSProviders (ProviderID)
GO

ALTER TABLE dbo.FBCSHCFAClaims
ADD CONSTRAINT FK_FBCSHCFAClaims_RenderingPhysicianProviderID
FOREIGN KEY (RenderingPhysicianProviderID)
REFERENCES dbo.FBCSProviders (ProviderID)
GO

ALTER TABLE dbo.FBCSHCFAClaims
ADD CONSTRAINT FK_FBCSHCFAClaims_RenderingFacilityProviderID
FOREIGN KEY (RenderingFacilityProviderID)
REFERENCES dbo.FBCSProviders (ProviderID)
GO

ALTER TABLE dbo.FBCSHCFAClaims
ADD CONSTRAINT FK_FBCSHCFAClaims_EDIServiceFacilityProviderID
FOREIGN KEY (EDIServiceFacilityProviderID)
REFERENCES dbo.FBCSProviders (ProviderID)
GO

ALTER TABLE dbo.FBCSHCFAClaims
ADD CONSTRAINT FK_FBCSHCFAClaims_EDIBillingConcatProviderID
FOREIGN KEY (EDIBillingConcatProviderID)
REFERENCES dbo.FBCSProviders (ProviderID)
GO

ALTER TABLE dbo.FBCSHCFAClaims
ADD CONSTRAINT FK_FBCSHCFAClaims_EDIBillingProviderID
FOREIGN KEY (EDIBillingProviderID)
REFERENCES dbo.FBCSProviders (ProviderID)
GO

ALTER TABLE dbo.FBCSHCFAClaims
ADD CONSTRAINT FK_FBCSHCFAClaims_EDIPayToProviderID
FOREIGN KEY (EDIPayToProviderID)
REFERENCES dbo.FBCSProviders (ProviderID)
GO

ALTER TABLE dbo.FBCSHCFAClaims
ADD CONSTRAINT FK_FBCSHCFAClaims_BillingFacilityProviderID
FOREIGN KEY (BillingFacilityProviderID)
REFERENCES dbo.FBCSProviders (ProviderID)
GO

ALTER TABLE dbo.FBCSHCFAClaims
ADD CONSTRAINT FK_FBCSHCFAClaims_EntryClaimTypeID
FOREIGN KEY (EntryClaimTypeID)
REFERENCES dbo.ClaimTypes (ClaimTypeID)
GO

ALTER TABLE dbo.FBCSHCFAClaims
ADD CONSTRAINT FK_FBCSHCFAClaims_ClaimStatusID
FOREIGN KEY (ClaimStatusID)
REFERENCES dbo.ClaimStatuses (ClaimStatusID)
GO

ALTER TABLE dbo.FBCSHCFAClaims
ADD CONSTRAINT FK_FBCSHCFAClaims_ImportedClaimStatusID
FOREIGN KEY (ImportedClaimStatusID)
REFERENCES dbo.ClaimStatuses (ClaimStatusID)
GO

ALTER TABLE dbo.FBCSHCFAClaims
ADD CONSTRAINT FK_FBCSHCFAClaims_ReasonID
FOREIGN KEY (ReasonID)
REFERENCES dbo.Reasons (ReasonID)
GO

ALTER TABLE dbo.FBCSHCFAClaims
ADD CONSTRAINT FK_FBCSHCFAClaims_SuspensionReasonID
FOREIGN KEY (SuspensionReasonID)
REFERENCES dbo.Reasons (ReasonID)
GO

ALTER TABLE dbo.FBCSHCFAClaims
ADD CONSTRAINT FK_FBCSHCFAClaims_RepricedStatusID
FOREIGN KEY (RepricedStatusID)
REFERENCES dbo.RepricedStatuses (RepricedStatusID)
GO

ALTER TABLE dbo.FBCSHCFAClaims
ADD CONSTRAINT FK_FBCSHCFAClaims_MeansTestStatusID
FOREIGN KEY (MeansTestStatusID)
REFERENCES dbo.MeansTestStatuses (MeansTestStatusID)
GO

ALTER TABLE dbo.FBCSHCFAClaims
ADD CONSTRAINT FK_FBCSHCFAClaims_BatchLogs
FOREIGN KEY (BatchLogID)
REFERENCES dbo.BatchLogs (BatchLogID)
GO

--indexes
/**
--CREATE INDEX statement failed. A clustered columnstore index cannot be created over referencing column 'FK_FBCSHCFAClaims_VerifiedByFBCSUserID' on table 'FBCSHCFAClaims'.
--Create clustered columnstore index that doesn't exist yet
CREATE CLUSTERED COLUMNSTORE INDEX CCIX_FBCSHCFAClaims ON dbo.FBCSHCFAClaims
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_FBCSHCFAClaims_PatientID ON dbo.FBCSHCFAClaims
(
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_FBCSHCFAClaims_EntryClaimTypeID ON dbo.FBCSHCFAClaims
(
EntryClaimTypeID
) 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_FBCSHCFAClaims_DateAdded ON dbo.FBCSHCFAClaims
(
DateAdded
) 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_FBCSHCFAClaims_CDWHCFANumber ON dbo.FBCSHCFAClaims
(
CDWHCFANumber
) 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; note the authoritative source table is A06.CDWWork.FBCS.HCFA',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSHCFAClaims',
N'COLUMN', N'FBCSHCFAClaimID'
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'FBCSHCFAClaims',
N'COLUMN', N'PatientID'
GO

EXECUTE sp_addextendedproperty
N'MS_Description', N'Foreign key, unique to EPRS, relates to Stations table, source field: trade-in of station__no + facility',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSHCFAClaims',
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'FBCSHCFAClaims',
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'FBCSHCFAClaims',
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'FBCSHCFAClaims',
N'COLUMN', N'ImportedByFBCSUserID'
GO

EXECUTE sp_addextendedproperty
N'MS_Description', N'Foreign key, unique to EPRS, relates to FBCS Users table, source field: previewpreimportedby',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSHCFAClaims',
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'FBCSHCFAClaims',
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'FBCSHCFAClaims',
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'FBCSHCFAClaims',
N'COLUMN', N'SentToPaymentByFBCSUserID'
GO

EXECUTE sp_addextendedproperty
N'MS_Description', N'Foreign key, unique to EPRS, relates to Providers table, source field: provider',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSHCFAClaims',
N'COLUMN', N'ProviderID'
GO

EXECUTE sp_addextendedproperty
N'MS_Description', N'Foreign key, unique to EPRS, relates to Providers table, source field: referringprovider',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSHCFAClaims',
N'COLUMN', N'ReferringProviderID'
GO

EXECUTE sp_addextendedproperty
N'MS_Description', N'Foreign key, unique to EPRS, relates to Providers table, source field: box17',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSHCFAClaims',
N'COLUMN', N'ReferringPhysicianProviderID'
GO

EXECUTE sp_addextendedproperty
N'MS_Description', N'Foreign key, unique to EPRS, relates to Providers table, source field: renderingproviderid',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSHCFAClaims',
N'COLUMN', N'RenderingProviderID'
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'FBCSHCFAClaims',
N'COLUMN', N'EDIRenderingProviderID'
GO

EXECUTE sp_addextendedproperty
N'MS_Description', N'Foreign key, unique to EPRS, relates to Providers table, source field: box31a',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSHCFAClaims',
N'COLUMN', N'RenderingPhysicianProviderID'
GO

EXECUTE sp_addextendedproperty
N'MS_Description', N'Foreign key, unique to EPRS, relates to Providers table, source field: edi_svc_fac_loc_name_box32',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSHCFAClaims',
N'COLUMN', N'RenderingFacilityProviderID'
GO

EXECUTE sp_addextendedproperty
N'MS_Description', N'Foreign key, unique to EPRS, relates to Providers table, source field: edibillingprov',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSHCFAClaims',
N'COLUMN', N'EDIBillingConcatProviderID'
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'FBCSHCFAClaims',
N'COLUMN', N'EDIBillingProviderID'
GO

EXECUTE sp_addextendedproperty
N'MS_Description', N'Foreign key, unique to EPRS, relates to Providers table, source field: edi_pay_to_prov_name_box33',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSHCFAClaims',
N'COLUMN', N'EDIPayToProviderID'
GO

EXECUTE sp_addextendedproperty
N'MS_Description', N'Foreign key, unique to EPRS, relates to Providers table, source field: box33a (+box33b)',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSHCFAClaims',
N'COLUMN', N'BillingFacilityProviderID'
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'FBCSHCFAClaims',
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'FBCSHCFAClaims',
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'FBCSHCFAClaims',
N'COLUMN', N'ImportedClaimStatusID'
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'FBCSHCFAClaims',
N'COLUMN', N'ReasonID'
GO

EXECUTE sp_addextendedproperty
N'MS_Description', N'Foreign key, unique to EPRS, relates to Reasons table, source field: suspension_reason',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSHCFAClaims',
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'FBCSHCFAClaims',
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'FBCSHCFAClaims',
N'COLUMN', N'MeansTestStatusID'
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'FBCSHCFAClaims',
N'COLUMN', N'BatchLogID'
GO

EXECUTE sp_addextendedproperty
N'MS_Description', N'Identifier from CDW FBCS HCFA, source field: id',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSHCFAClaims',
N'COLUMN', N'CDWHCFANumber'
GO

--TODO: Revisit the description on ClaimID if we have a Claims table in future
EXECUTE sp_addextendedproperty
N'MS_Description', N'Identifier from FBCS, source field: claim_id',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSHCFAClaims',
N'COLUMN', N'FBCSClaimNumber'
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'FBCSHCFAClaims',
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'FBCSHCFAClaims',
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'FBCSHCFAClaims',
N'COLUMN', N'RepricersReferenceNumber'
GO

EXECUTE sp_addextendedproperty
N'MS_Description', N'Federal Tax ID, source field: box25',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSHCFAClaims',
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'FBCSHCFAClaims',
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'FBCSHCFAClaims',
N'COLUMN', N'FPPSNumber'
GO

EXECUTE sp_addextendedproperty
N'MS_Description', N'Patient Account Number, source field: box26',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSHCFAClaims',
N'COLUMN', N'PatientAccountNumber'
GO

EXECUTE sp_addextendedproperty
N'MS_Description', N'Insured SSN, source field: box1a',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSHCFAClaims',
N'COLUMN', N'InsuredNumberSSN'
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'FBCSHCFAClaims',
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'FBCSHCFAClaims',
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'FBCSHCFAClaims',
N'COLUMN', N'CheckNumber'
GO

EXECUTE sp_addextendedproperty
N'MS_Description', N'Preauthorized number, source field: box23',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSHCFAClaims',
N'COLUMN', N'PreAuthorizationNumber'
GO

EXECUTE sp_addextendedproperty
N'MS_Description', N'Original referral number, source field: box22b',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSHCFAClaims',
N'COLUMN', N'OriginalReferralNumber'
GO

EXECUTE sp_addextendedproperty
N'MS_Description', N'Records the date and time of HCFA Claim entry for the first time, source: dtstamp',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSHCFAClaims',
N'COLUMN', N'DateAdded'
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'FBCSHCFAClaims',
N'COLUMN', N'DateVerified'
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'FBCSHCFAClaims',
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'FBCSHCFAClaims',
N'COLUMN', N'DateImported'
GO

EXECUTE sp_addextendedproperty
N'MS_Description', N'Date when claim was signed, source field: box31',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSHCFAClaims',
N'COLUMN', N'DateSigned'
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'FBCSHCFAClaims',
N'COLUMN', N'DateVerficationInProgress'
GO

EXECUTE sp_addextendedproperty
N'MS_Description', N'Date when claim was reopened, source field: dtreopened',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSHCFAClaims',
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'FBCSHCFAClaims',
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'FBCSHCFAClaims',
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'FBCSHCFAClaims',
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'FBCSHCFAClaims',
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'FBCSHCFAClaims',
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'FBCSHCFAClaims',
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'FBCSHCFAClaims',
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'FBCSHCFAClaims',
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'FBCSHCFAClaims',
N'COLUMN', N'DateFilingEffective'
GO

EXECUTE sp_addextendedproperty
N'MS_Description', N'Date of hospital admission, source field: hospadmdt',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSHCFAClaims',
N'COLUMN', N'DateHospitalAdmission'
GO

EXECUTE sp_addextendedproperty
N'MS_Description', N'Date of hospital discharge, source field: hospdiscdt',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSHCFAClaims',
N'COLUMN', N'DateHospitalDischarge'
GO

EXECUTE sp_addextendedproperty
N'MS_Description', N'Total charges, source field: box28',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSHCFAClaims',
N'COLUMN', N'ChargeTotal'
GO

EXECUTE sp_addextendedproperty
N'MS_Description', N'Amount paid, source field: box29',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSHCFAClaims',
N'COLUMN', N'AmountPaid'
GO

EXECUTE sp_addextendedproperty
N'MS_Description', N'Balance due, source field: box30',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSHCFAClaims',
N'COLUMN', N'BalanceDue'
GO

EXECUTE sp_addextendedproperty
N'MS_Description', N'Sum of all the line item repriced amounts that are marked to pay, source field: repricedtotal',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSHCFAClaims',
N'COLUMN', N'RepricedTotal'
GO

EXECUTE sp_addextendedproperty
N'MS_Description', N'Sum of all the line item repriced amount due, source field: repricedamountdue',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSHCFAClaims',
N'COLUMN', N'RepricedAmountDue'
GO

EXECUTE sp_addextendedproperty
N'MS_Description', N'Healthnet vendor claim repriced amount, source field: healthnetrepricedamt',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSHCFAClaims',
N'COLUMN', N'HealthnetRepricedAmount'
GO

EXECUTE sp_addextendedproperty
N'MS_Description', N'Number of visits to deduct, source field: numberofvisitstodeduct',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSHCFAClaims',
N'COLUMN', N'NumberOfVisitsToDeduct'
GO

EXECUTE sp_addextendedproperty
N'MS_Description', N'ICD indicator (NULL, 0, or 9), source field: icdindicator',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSHCFAClaims',
N'COLUMN', N'ICDIndicator'
GO

EXECUTE sp_addextendedproperty
N'MS_Description', N'Referring Physician Number, source field: box17a',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSHCFAClaims',
N'COLUMN', N'ReferringPhysicianProviderNumber'
GO

EXECUTE sp_addextendedproperty
N'MS_Description', N'Rendering Provider box (does not always match Rendering Provider, source field: boxrenderingprovider',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSHCFAClaims',
N'COLUMN', N'RenderingProviderBox'
GO

EXECUTE sp_addextendedproperty
N'MS_Description', N'Taxonomy applied to rendering provider, source field: rendering_prov_taxonomy',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSHCFAClaims',
N'COLUMN', N'RenderingProviderTaxonomy'
GO

EXECUTE sp_addextendedproperty
N'MS_Description', N'Identifier for the rendering facility, source field: rendering_facility_id',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSHCFAClaims',
N'COLUMN', N'RenderingFacilityProviderNumber'
GO

EXECUTE sp_addextendedproperty
N'MS_Description', N'Identifier for Billing Provider, source field: billing_provider_id',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSHCFAClaims',
N'COLUMN', N'BillingProviderNumber'
GO

EXECUTE sp_addextendedproperty
N'MS_Description', N'Taxonomy for Billing Provider, source field: billing_provider_taxonomy',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSHCFAClaims',
N'COLUMN', N'BillingProviderTaxonomy'
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'FBCSHCFAClaims',
N'COLUMN', N'EDIFacilityToRouteTo'
GO

EXECUTE sp_addextendedproperty
N'MS_Description', N'VA Fund Control Point, source field: vafcp',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSHCFAClaims',
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'FBCSHCFAClaims',
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'FBCSHCFAClaims',
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'FBCSHCFAClaims',
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'FBCSHCFAClaims',
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'FBCSHCFAClaims',
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'FBCSHCFAClaims',
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'FBCSHCFAClaims',
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'FBCSHCFAClaims',
N'COLUMN', N'PatientType'
GO

EXECUTE sp_addextendedproperty
N'MS_Description', N'Insured Name, source field: box4',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSHCFAClaims',
N'COLUMN', N'InsuredName'
GO

EXECUTE sp_addextendedproperty
N'MS_Description', N'Insured policy number, source field: box11',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSHCFAClaims',
N'COLUMN', N'InsuredPolicyNumber'
GO

EXECUTE sp_addextendedproperty
N'MS_Description', N'Insurance plan name, source field: box11c',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSHCFAClaims',
N'COLUMN', N'InsurancePlanName'
GO

EXECUTE sp_addextendedproperty
N'MS_Description', N'Special provision category, source field: specialprovcat',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSHCFAClaims',
N'COLUMN', N'SpecialProvisionCategory'
GO

EXECUTE sp_addextendedproperty
N'MS_Description', N'Total service connected, source field: totalsc',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSHCFAClaims',
N'COLUMN', N'TotalServiceConnected'
GO

EXECUTE sp_addextendedproperty
N'MS_Description', N'Billable insurance, source field: billable_insurance',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSHCFAClaims',
N'COLUMN', N'BillableInsurance'
GO

EXECUTE sp_addextendedproperty
N'MS_Description', N'Service Connected Conditions, source field: sc_conditions',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSHCFAClaims',
N'COLUMN', N'ServiceConnectedConditions'
GO

EXECUTE sp_addextendedproperty
N'MS_Description', N'Primary eligibility, source field: primaryeligibility',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSHCFAClaims',
N'COLUMN', N'PrimaryEligibility'
GO

EXECUTE sp_addextendedproperty
N'MS_Description', N'Suspended note, source field: suspendednote',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSHCFAClaims',
N'COLUMN', N'SuspendedNote'
GO

EXECUTE sp_addextendedproperty
N'MS_Description', N'Rejected note, source field: rejectednote',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSHCFAClaims',
N'COLUMN', N'RejectedNote'
GO

EXECUTE sp_addextendedproperty
N'MS_Description', N'Note text, source field: notetxt',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSHCFAClaims',
N'COLUMN', N'NoteText'
GO

EXECUTE sp_addextendedproperty
N'MS_Description', N'Reopened reason, source field: reopenedreason',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSHCFAClaims',
N'COLUMN', N'ReopenedReason'
GO

EXECUTE sp_addextendedproperty
N'MS_Description', N'Claim memo, source field: claimmemo',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSHCFAClaims',
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'FBCSHCFAClaims',
N'COLUMN', N'VerficationInProgressGUID'
GO

EXECUTE sp_addextendedproperty
N'MS_Description', N'NUCC Version, source field: nuccversion',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSHCFAClaims',
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'FBCSHCFAClaims',
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'FBCSHCFAClaims',
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'FBCSHCFAClaims',
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'FBCSHCFAClaims',
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'FBCSHCFAClaims',
N'COLUMN', N'IsImported'
GO

EXECUTE sp_addextendedproperty
N'MS_Description', N'True/False, Has claim been repriced (1=yes/true, 0=no/false), source field: repriced',
N'SCHEMA', N'dbo',
N'TABLE', N'FBCSHCFAClaims',
N'COLUMN', N'IsRepriced'
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'FBCSHCFAClaims',
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'FBCSHCFAClaims',
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'FBCSHCFAClaims',
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'FBCSHCFAClaims',
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'FBCSHCFAClaims',
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'FBCSHCFAClaims',
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'FBCSHCFAClaims',
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'FBCSHCFAClaims',
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'FBCSHCFAClaims',
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'FBCSHCFAClaims',
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'FBCSHCFAClaims',
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'FBCSHCFAClaims',
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'FBCSHCFAClaims',
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'FBCSHCFAClaims',
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'FBCSHCFAClaims',
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'FBCSHCFAClaims',
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'FBCSHCFAClaims',
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'FBCSHCFAClaims',
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'FBCSHCFAClaims',
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'FBCSHCFAClaims',
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'FBCSHCFAClaims',
N'COLUMN', N'IsOCRCompleted'
GO