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 zraw._fbcshcfa
(
--source: A06, CDWWork, FBCS.hcfa

--fields
station__no VARCHAR(30) NOT NULL,
id BIGINT,
imageid BIGINT,
dropoutid BIGINT,
dtstamp DATETIME2(7),
verified BIGINT,
verifiedby BIGINT,
verifieddtstamp DATETIME2(7),
rejected BIGINT,
imported BIGINT,
importednote VARCHAR(8000),
importedby BIGINT,
importeddtstamp DATETIME2(7),
box1a VARCHAR(8000), --this is SSN
box2 VARCHAR(8000), --this is patient name, but better to use SPatient (e.g., Duck Daffy here, vs Duck,Daffy)
box211 VARCHAR(8000),
box212 VARCHAR(8000),
box213 VARCHAR(8000),
box214 VARCHAR(8000),
box25 VARCHAR(8000),
box26 VARCHAR(8000),
box28 NUMERIC(28,6),
box31 DATETIME2(7),
box3dob DATETIME2(7),
box3sex VARCHAR(8000),
box17 VARCHAR(8000),
box17a VARCHAR(8000),
box29 NUMERIC(28,6),
box30 NUMERIC(28,6),
box32 VARCHAR(8000),
box33a VARCHAR(8000),
repricedtotal NUMERIC(28,6),
repricedamountdue NUMERIC(28,6),
repriced INT,
printedletter INT,
printedletterdtstamp DATETIME2(7),
printedby BIGINT,
box33b VARCHAR(8000),
offx INT,
offy INT,
scrubbed INT,
reasonid BIGINT,
documentsattached INT,
taxidclean VARCHAR(8000),
[status] VARCHAR(8000),
suspensionreasonid BIGINT,
suspendednote VARCHAR(8000),
rejectednote VARCHAR(8000),
facility BIGINT,
verificationinprogressdtstamp DATETIME2(7),
vafcp VARCHAR(8000),
vafb VARCHAR(8000),
vain VARCHAR(8000),
vaon VARCHAR(8000),
authnum VARCHAR(8000),
preimportedby BIGINT,
preimporteddtstamp DATETIME2(7),
vistapatkey BIGINT,
vistaauthkey BIGINT,
editactionstaken INT,
edihcfatempid BIGINT,
va_referringphysician VARCHAR(8000),
categoryofcare VARCHAR(8000),
inpatient INT,
healthnetrepricedamt NUMERIC(28,6),
repricedstatus VARCHAR(8000),
contractclaim INT,
jobnumber INT,
providerid BIGINT,
claimentrytype VARCHAR(8000),
batchid BIGINT,
sc_conditions VARCHAR(8000),
billable_insurance VARCHAR(8000),
boxrenderingprovider VARCHAR(8000),
patienttypeid BIGINT,
repricersreferenceid VARCHAR(8000),
healthnetproductcode INT,
renderingproviderid BIGINT,
referringprovider VARCHAR(8000),
facilityid BIGINT,
edifacilitytorouteto VARCHAR(50),
hero_claim_flag CHAR(1),
edibillingprov VARCHAR(2500),
edirenderingprov VARCHAR(2500),
hacedi_id VARCHAR(8000),
box31a VARCHAR(8000),
fpps_id VARCHAR(255),
hacediupdatesent BIGINT,
dtreopened DATETIME2(7),
reopeneduser BIGINT,
reopenedreason VARCHAR(8000),
sc_connected INT,
totalsc VARCHAR(8000),
meansteststatus VARCHAR(8000),
dtsenttopayment DATETIME2(7),
dtreturnedfrompayment DATETIME2(7),
primaryeligibility VARCHAR(500),
previousimportedby BIGINT,
previouspreimportedby BIGINT,
senttopaymentby BIGINT,
checknumber VARCHAR(50),
voucherdate DATETIME2(7),
newocrmethod INT,
taxidoverride INT,
claimmemo VARCHAR(255),
rendering_facility_npi VARCHAR(8000),
box4 VARCHAR(8000),
box11 VARCHAR(8000),
box11c VARCHAR(8000),
rendering_facility_id VARCHAR(8000),
billing_provider_npi VARCHAR(8000),
billing_provider_id VARCHAR(8000),
bwclaim INT,
wrongclaimtype INT,
verificationinprogressguid VARCHAR(50),
senttocalculate INT,
dtcalculated DATETIME2(7),
dirty INT,
lastcalcdjobnumber VARCHAR(100),
box215 VARCHAR(8000),
box216 VARCHAR(8000),
box217 VARCHAR(8000),
box218 VARCHAR(8000),
box219 VARCHAR(8000),
box2110 VARCHAR(8000),
box2111 VARCHAR(8000),
box2112 VARCHAR(8000),
ocrcompleted INT,
rendering_prov_taxonomy VARCHAR(8000),
pay_to_provider_taxonomy VARCHAR(8000),
billing_provider_taxonomy VARCHAR(8000),
referring_provider_taxonomy VARCHAR(8000),
box23 VARCHAR(30),
edi_pay_to_prov_name_box33 VARCHAR(5000),
edi_pay_to_prov_addr1_box33 VARCHAR(5000),
edi_pay_to_prov_city_box33 VARCHAR(5000),
edi_pay_to_prov_st_box33 VARCHAR(5000),
edi_pay_to_prov_zip_box33 VARCHAR(5000),
edi_svc_fac_loc_name_box32 VARCHAR(5000),
edi_svc_fac_loc_addr1_box32 VARCHAR(5000),
edi_svc_fac_loc_city_box32 VARCHAR(5000),
edi_svc_fac_loc_st_box32 VARCHAR(5000),
edi_svc_fac_loc_zip_box32 VARCHAR(5000),
edi_bill_prov_name_box33 VARCHAR(5000),
edi_bill_prov_addr1_box33 VARCHAR(5000),
edi_bill_prov_city_box33 VARCHAR(5000),
edi_bill_prov_st_box33 VARCHAR(5000),
edi_bill_prov_zip_box33 VARCHAR(5000),
payment_reset_date DATETIME2(7),
payment_reset INT,
senttobescored INT,
senttobescoreddtstamp DATETIME2(7),
scoringrcvddtstamp DATETIME2(7),
pitstatus BIGINT,
terminalstatusupdatesentdtstamp DATETIME2(7),
pitrejected INT,
terminalstatusfeedcreated INT,
numberofvisitstodeduct INT,
effectivedate INT,
insjustificationcode INT,
filingeffectivedate DATETIME2(7),
other_hlth_ins_present VARCHAR(1),
hasdups INT,
nuccversion VARCHAR(5),
box22b VARCHAR(30),
icdindicator INT,
hospadmdt DATETIME2(7),
hospdiscdt DATETIME2(7),
notetxt VARCHAR(500),
on30dayhold INT,
specialprovcat VARCHAR(50),
thread__id NUMERIC(18,0) NOT NULL,
--BatchLogID should always be the last of the authoritative source fields listed
--in the data gathered by extractors, it should be the first of the EPRS-specific fields
BatchLogID BIGINT,
--EPRS table specific: Any fields beyond this point are EPRS specific fields/FKs
PatientID bigint, --EPRS assigned (FK), reference Patients, TODO: combo trade on vistapatkey, box2 with SPatient
StationID smallint 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
EPRSProviderID bigint, --EPRS assigned (FK), reference Providers, provider
ReferringProviderID bigint, --EPRS assigned (FK), reference Providers, referringprovider
ReferringPhysicianProviderID bigint, --EPRS assigned (FK), reference Providers, box17
EPRSRenderingProviderID 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
EPRSReasonID smallint, --EPRS assigned (FK), reference Reasons, reasonid
EPRSSuspensionReasonID smallint, --EPRS assigned (FK), reference Reasons, suspensionreason
RepricedStatusID tinyint, --EPRS assigned (FK), reference RepricedStatuses, repricedstatus
MeansTestStatusID tinyint, --EPRS assigned (FK), reference MeansTestStatuses, meansteststatus

--primary key

--unique key (if any)
)
ON StagingData
--when using clustered columnstore index, can't set data compression to PAGE
WITH (DATA_COMPRESSION = PAGE)

GO

--foreign keys

--indexes

--constraints

--Metadata descriptions for each field