-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathNphies_Midtables_Oracle2.8.sql
293 lines (261 loc) · 11 KB
/
Nphies_Midtables_Oracle2.8.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
/*
Created 10/17/2021
Modified 20/02/2022
Project Waseel
Model
Company
Author Hardik
Version 0.4
Database Oracle
*/
-- Create Tables section
Create table NPHIES_BENEFICIARY (
BENEFICIARYID VARCHAR2 (20) NOT NULL,
PATIENTFILENO VARCHAR2(30) NOT NULL,
FIRSTNAME VARCHAR2 (50),
MIDDLENAME VARCHAR2 (50),
LASTNAME VARCHAR2 (50),
FULLNAME VARCHAR2 (200),
DOB DATE NOT NULL,
GENDER VARCHAR2(10) NOT NULL,
NATIONALITY VARCHAR2(30),
DOCUMENTTYPE VARCHAR2(30) NOT NULL,
DOCUMENTID VARCHAR2(50) NOT NULL,
CONTACTNUMBER VARCHAR2(50),
EHEALTHID VARCHAR2(50),
RESIDENCYTYPE VARCHAR2(50),
MARITALSTATUS VARCHAR2(10),
BLOODGROUP VARCHAR2(10),
PREFERREDLANGUAGE VARCHAR2(20),
EMAIL VARCHAR2(50),
ADDRESSLINE VARCHAR2(250),
ADDRESSSTREETNAME VARCHAR2(250),
ADDRESSCITY VARCHAR2(250),
ADDRESSDISTRICT VARCHAR2(250),
ADDRESSSTATE VARCHAR2(250),
ADDRESSPOSTALCODE VARCHAR2(100),
ADDRESSCOUNTRY VARCHAR2(250),
Constraint PK_BENEFICIARY primary key (BENEFICIARYID));
Create table NPHIES_COVERAGE (
COVERAGEID VARCHAR2 (20) NOT NULL,
MEMBERID VARCHAR2 (50) NOT NULL,
EXPIRYDATE DATE,
PAYERNPHIESID VARCHAR2(20) NOT NULL,
TPANPHIESID VARCHAR2(20) NULL,
RELATIONWITHSUBSCRIBER VARCHAR2(20) NOT NULL,
POLICYNUMBER VARCHAR2(30),
POLICYHOLDER VARCHAR2(250),
COVERAGETYPE VARCHAR2(20) NOT NULL,
BENEFICIARYID VARCHAR2(20) NOT NULL,
Constraint PK_COVERAGE primary key (COVERAGEID)
);
Alter table NPHIES_COVERAGE add Constraint FK_BENEFICIARY_COVERAGE foreign key (BENEFICIARYID) references NPHIES_BENEFICIARY (BENEFICIARYID);
Create table NPHIES_CLAIMINFO (
PROVCLAIMNO VARCHAR2 (40) NOT NULL,
EPISODEID VARCHAR2(40) NOT NULL,
ISNEWBORN VARCHAR2(10) DEFAULT NULL,
ISREFERRAL VARCHAR2(10) DEFAULT NULL,
REFERRINGPROVIDERNAME VARCHAR2 (200) DEFAULT NULL,
CLAIMTYPE VARCHAR2(20) NOT NULL,
CLAIMSUBTYPE VARCHAR2(20) NOT NULL,
PROVIDERNPHIESID VARCHAR2(20) NOT NULL,
CLAIMCREATEDDATE DATE NOT NULL,
ACCOUNTINGPERIOD DATE NOT NULL,
BILLABLEPERIODSTART DATE,
BILLABLEPERIODEND DATE,
ELIGIBILITYRESPONSEID VARCHAR2(30),
ELIGIBILITYIDENTIFIERURL VARCHAR2(250) DEFAULT NULL,
ELIGIBILITYOFFLINEID VARCHAR2(30),
ELIGIBILITYOFFLINEDATE DATE,
PREAUTHOFFLINEDATE DATE,
PREAUTHRESPONSEID VARCHAR2(30),
PREAUTHIDENTIFIERURL VARCHAR2(250) DEFAULT NULL,
PAYEETYPE VARCHAR2(10),
PAYEEID VARCHAR2(20),
COVERAGEID VARCHAR2 (20) NOT NULL,
BENEFICIARYID VARCHAR2(20) NOT NULL,
SUBSCRIBERID VARCHAR2(20) NULL,
TOTAL Decimal(14,2) NOT NULL,
PRESCRIPTION VARCHAR(255),
Constraint PK_CLAIMINFO primary key (PROVCLAIMNO)
);
Alter table NPHIES_CLAIMINFO add Constraint FK_BENEFICIARY_CLAIMINFO foreign key (BENEFICIARYID) references NPHIES_BENEFICIARY (BENEFICIARYID);
Alter table NPHIES_CLAIMINFO add Constraint FK_BENEFI_SUBSCRIB_CLAIMINFO foreign key (SUBSCRIBERID) references NPHIES_BENEFICIARY (BENEFICIARYID);
Alter table NPHIES_CLAIMINFO add Constraint FK_COVERAGE_CLAIMINFO foreign key (COVERAGEID) references NPHIES_COVERAGE (COVERAGEID);
Create table NPHIES_CLAIMPREAUTHDETAILS (
PROVCLAIMNO VARCHAR2 (40) NOT NULL,
PREAUTHREFNO VARCHAR2(20) NOT NULL,
Constraint PK_CLAIMPREAUTHDETAILS primary key (PROVCLAIMNO,PREAUTHREFNO)
);
Alter table NPHIES_CLAIMPREAUTHDETAILS add Constraint FK_CLAIMPREAUTH foreign key (PROVCLAIMNO) references NPHIES_CLAIMINFO (PROVCLAIMNO);
Create table NPHIES_CLAIMDIAGNOSIS (
PROVCLAIMNO VARCHAR2 (40) NOT NULL,
SEQUENCENO INTEGER NOT NULL,
DIAGNOSISCODE Varchar2 (30) NOT NULL,
DIAGNOSISDESC Varchar2 (256),
DIAGNOSISTYPE VARCHAR2(30),
ONADMISSION VARCHAR2(10),
Constraint PK_CLAIMDIAGNOSIS primary key (PROVCLAIMNO,SEQUENCENO)
);
Alter table NPHIES_CLAIMDIAGNOSIS add Constraint FK_CLAIMDIAGNOSIS foreign key (PROVCLAIMNO) references NPHIES_CLAIMINFO (PROVCLAIMNO);
Create table NPHIES_CLAIMCARETEAM (
PROVCLAIMNO VARCHAR2 (40) NOT NULL,
SEQUENCENO INTEGER NOT NULL,
PHYSICIANID VARCHAR2(30) NOT NULL,
PHYSICIANNAME VARCHAR2(60) NOT NULL,
PRACTITIONERROLE VARCHAR2(20),
CARETEAMROLE VARCHAR2(20),
CARETEAMQUALIFICATION VARCHAR2(30) NOT NULL,
Constraint PK_CLAIMCARETEAM primary key (PROVCLAIMNO,SEQUENCENO)
);
Alter table NPHIES_CLAIMCARETEAM add Constraint FK_CLAIMCARETEAM foreign key (PROVCLAIMNO) references NPHIES_CLAIMINFO (PROVCLAIMNO);
Create table NPHIES_CLAIMITEM (
PROVCLAIMNO VARCHAR2 (40) NOT NULL,
INVOICENO VARCHAR2(30) NOT NULL,
SEQUENCENO INTEGER NOT NULL,
SERVICETYPE VARCHAR2(30) NOT NULL,
SERVICECODE VARCHAR2(30) NOT NULL,
SERVICEDESC VARCHAR2(256) NOT NULL,
NONSTANDARDCODE VARCHAR2(30),
NONSTANDARDDESC VARCHAR2(256),
UDI VARCHAR2(30),
ISPACKAGE VARCHAR2(5) NOT NULL,
QUANTITY Decimal(10,2) NOT NULL,
QUANTITYCODE VARCHAR2(10) NULL,
UNITPRICE Decimal(14,2) NOT NULL,
DISCOUNT Decimal(14,2) NULL,
FACTOR Decimal(14,6) NOT NULL,
PATIENTSHARE Decimal(14,2) NOT NULL,
PAYERSHARE Decimal(14,2) NOT NULL,
TAX Decimal(14,2) NOT NULL,
NET Decimal(14,2) NOT NULL,
STARTDATE DATE NULL,
ENDDATE DATE NOT NULL,
BODYSITECODE VARCHAR2(10),
SUBSITECODE VARCHAR2(10),
DRUGSELECTIONREASON VARCHAR2(30),
PRESCRIBEDDRUGCODE VARCHAR2(50),
PHARMACISTSELECTIONREASON VARCHAR(255) ,
PHARMACISTSUBSTITUTE VARCHAR(255),
REASONPHARMACISTSUBSTITUTE VARCHAR(255),
Constraint PK_CLAIMITEM primary key (PROVCLAIMNO,SEQUENCENO)
);
Alter table NPHIES_CLAIMITEM add Constraint FK_CLAIMITEM foreign key (PROVCLAIMNO) references NPHIES_CLAIMINFO (PROVCLAIMNO);
Create table NPHIES_CLAIMSUPPORTINGINFO (
PROVCLAIMNO VARCHAR2 (40) NOT NULL,
SEQUENCENO INTEGER NOT NULL,
CATEGORY VARCHAR2(20) NOT NULL,
REASON VARCHAR2(20),
SUPPORTINGVALUE VARCHAR2(2000),
SUPPORTINGATTACHMENT BLOB,
ATTACHMENTFILENAME VARCHAR2(30),
ATTACHMENTTYPE VARCHAR2(20),
CODE VARCHAR2(30),
UNIT VARCHAR2(30) DEFAULT NULL,
TIMINGPERIODFROM DATE DEFAULT NULL,
TIMINGPERIODTO DATE DEFAULT NULL,
Constraint PK_CLAIMSUPPORTINGINFO primary key (PROVCLAIMNO,SEQUENCENO)
);
Alter table NPHIES_CLAIMSUPPORTINGINFO add Constraint FK_CLAIMSUPPORTINGINFO foreign key (PROVCLAIMNO) references NPHIES_CLAIMINFO (PROVCLAIMNO);
Create table NPHIES_CLAIMACCIDENTDETAIL (
PROVCLAIMNO VARCHAR2 (40) NOT NULL,
ACCIDENTTYPE VARCHAR2(20) NOT NULL,
ACCIDENTDATE DATE NOT NULL,
ADDRESSSTREETNAME VARCHAR2(250),
ADDRESSCITY VARCHAR2(250),
ADDRESSSTATE VARCHAR2(250),
ADDRESSCOUNTRY VARCHAR2(250),
Constraint PK_CLAIMACCIDENTDETAIL primary key (PROVCLAIMNO)
);
Alter table NPHIES_CLAIMACCIDENTDETAIL add Constraint FK_CLAIMACCIDENTDETAIL foreign key (PROVCLAIMNO) references NPHIES_CLAIMINFO (PROVCLAIMNO);
Create table NPHIES_CLAIMVISIONPRESCRIPTION (
PROVCLAIMNO VARCHAR2 (40) NOT NULL,
VISIONPRESCRIPTIONID VARCHAR2 (20) NOT NULL,
DATEWRITTEN DATE NOT NULL,
CARETEAMSEQUENCE INTEGER NOT NULL,
PRODUCT VARCHAR2(10) NOT NULL,
EYE VARCHAR2(10) NOT NULL,
SPHERE Decimal(14,2),
CYLINDER Decimal(14,2),
AXIS INTEGER,
PRISMAMOUNT Decimal(14,2),
PRISMBASE VARCHAR2(10),
MULTIFOCALPOWER Decimal(14,2),
LENSPOWER Decimal(14,2),
LENSBACKCURVE Decimal(14,2),
LENSDIAMETER Decimal(14,2),
LENSDURATION INTEGER,
LENSCOLOR VARCHAR2(10),
LENSBRAND VARCHAR2(50),
LENSNOTE VARCHAR2(256),
LENSDURATIONUNIT VARCHAR2(10),
Constraint PK_CLAIMVISIONPRESCRIPTION primary key (PROVCLAIMNO,VISIONPRESCRIPTIONID)
);
Alter table NPHIES_CLAIMVISIONPRESCRIPTION add Constraint FK_CLAIMVISIONPRESCRIPTION foreign key (PROVCLAIMNO) references NPHIES_CLAIMINFO (PROVCLAIMNO);
Alter table NPHIES_CLAIMVISIONPRESCRIPTION add Constraint FK_CARETEAMVISION foreign key (PROVCLAIMNO,CARETEAMSEQUENCE) references NPHIES_CLAIMCARETEAM (PROVCLAIMNO,SEQUENCENO);
Create table NPHIES_ITEMDIAGNOSIS (
PROVCLAIMNO VARCHAR2 (40) NOT NULL,
DIAGNOSISSEQUENCENO INTEGER NOT NULL,
ITEMSEQUENCENO INTEGER NOT NULL,
Constraint PK_ITEMDIAGNOSIS primary key (PROVCLAIMNO,DIAGNOSISSEQUENCENO,ITEMSEQUENCENO)
);
Alter table NPHIES_ITEMDIAGNOSIS add Constraint FK_ITEMDIAGNOSIS foreign key (PROVCLAIMNO) references NPHIES_CLAIMINFO (PROVCLAIMNO);
Alter table NPHIES_ITEMDIAGNOSIS add Constraint FK_DIAGNOSISSEQ foreign key (PROVCLAIMNO,DIAGNOSISSEQUENCENO) references NPHIES_CLAIMDIAGNOSIS (PROVCLAIMNO,SEQUENCENO);
Alter table NPHIES_ITEMDIAGNOSIS add Constraint FK_ITEMDIASEQ foreign key (PROVCLAIMNO,ITEMSEQUENCENO) references NPHIES_CLAIMITEM (PROVCLAIMNO,SEQUENCENO);
Create table NPHIES_ITEMCARETEAM (
PROVCLAIMNO VARCHAR2 (40) NOT NULL,
CARETEAMSEQUENCENO INTEGER NOT NULL,
ITEMSEQUENCENO INTEGER NOT NULL,
Constraint PK_ITEMCARETEAM primary key (PROVCLAIMNO,CARETEAMSEQUENCENO,ITEMSEQUENCENO)
);
Alter table NPHIES_ITEMCARETEAM add Constraint FK_ITEMCARETEAM foreign key (PROVCLAIMNO) references NPHIES_CLAIMINFO (PROVCLAIMNO);
Alter table NPHIES_ITEMCARETEAM add Constraint FK_CARETEAMSEQ foreign key (PROVCLAIMNO,CARETEAMSEQUENCENO) references NPHIES_CLAIMCARETEAM (PROVCLAIMNO,SEQUENCENO);
Alter table NPHIES_ITEMCARETEAM add Constraint FK_ITEMCARESEQ foreign key (PROVCLAIMNO,ITEMSEQUENCENO) references NPHIES_CLAIMITEM (PROVCLAIMNO,SEQUENCENO);
Create table NPHIES_ITEMSUPPORTINGINFO (
PROVCLAIMNO VARCHAR2 (40) NOT NULL,
SUPPORTINGINFOSEQUENCENO INTEGER NOT NULL,
ITEMSEQUENCENO INTEGER NOT NULL,
Constraint PK_ITEMSUPPORTINGINFO primary key (PROVCLAIMNO,SUPPORTINGINFOSEQUENCENO,ITEMSEQUENCENO)
);
Alter table NPHIES_ITEMSUPPORTINGINFO add Constraint FK_ITEMSUPPORTINGINFO foreign key (PROVCLAIMNO) references NPHIES_CLAIMINFO (PROVCLAIMNO);
Alter table NPHIES_ITEMSUPPORTINGINFO add Constraint FK_SUPPORTINGINFOSEQ foreign key (PROVCLAIMNO,SUPPORTINGINFOSEQUENCENO) references NPHIES_CLAIMSUPPORTINGINFO (PROVCLAIMNO,SEQUENCENO);
Alter table NPHIES_ITEMSUPPORTINGINFO add Constraint FK_ITEMSUPPORTINGSEQ foreign key (PROVCLAIMNO,ITEMSEQUENCENO) references NPHIES_CLAIMITEM (PROVCLAIMNO,SEQUENCENO);
Create table NPHIES_CLAIMITEMDETAILS (
ITEMSEQUENCENO INTEGER NOT NULL,
PROVCLAIMNO VARCHAR2 (40) NOT NULL,
SEQUENCENO INTEGER NOT NULL,
SERVICETYPE VARCHAR2(30) NOT NULL,
SERVICECODE VARCHAR2(30) NOT NULL,
SERVICEDESC VARCHAR2(256) NOT NULL,
NONSTANDARDCODE VARCHAR2(30),
NONSTANDARDDESC VARCHAR2(256),
UDI VARCHAR2(30),
QUANTITY Decimal(10,2) NOT NULL,
QUANTITYCODE VARCHAR2(10) NULL,
UNITPRICE Decimal(14,2),
TAX Decimal(14,2),
NET Decimal(14,2),
PHARMACISTSELECTIONREASON VARCHAR(255) ,
PHARMACISTSUBSTITUTE VARCHAR(255),
REASONPHARMACISTSUBSTITUTE VARCHAR(255),
PRESCRIBEDDRUGCODE VARCHAR2(50),
Constraint PK_CLAIMITEMDETAILS primary key (PROVCLAIMNO,SEQUENCENO)
);
Alter table NPHIES_CLAIMITEMDETAILS add Constraint FK_ITEMDETAILSSEQ foreign key (PROVCLAIMNO,ITEMSEQUENCENO) references NPHIES_CLAIMITEM (PROVCLAIMNO,SEQUENCENO);
Create table NPHIES_CLAIMENCOUNTERS (
PROVCLAIMNO VARCHAR2 (40) NOT NULL,
ENCOUNTERID VARCHAR2 (20) NOT NULL,
ENCOUNTERSTARTDATE DATE NOT NULL,
ENCOUNTERENDDATE DATE NOT NULL,
ENCOUNTERCLASS VARCHAR2(20),
ENCOUNTERSERVICETYPE VARCHAR2(20),
PRIORITY VARCHAR2(20),
HOSPITALIZATIONORIGIN VARCHAR2(20),
HOSPITALADMISSIONSOURCE VARCHAR2(20),
HOSPITALREADMISSION VARCHAR2(20),
HOSPITALDISCHARGEDISPOSITION VARCHAR2(20),
SERVICEPROVIDER VARCHAR2(20),
ENCOUNTERSTATUS VARCHAR2(20) NOT NULL,
Constraint PK_CLAIMENCOUNTERS primary key (PROVCLAIMNO,ENCOUNTERID)
);
Alter table NPHIES_CLAIMENCOUNTERS add Constraint FK_CLAIMENCOUNTERS foreign key (PROVCLAIMNO) references NPHIES_CLAIMINFO (PROVCLAIMNO);