SU6xKCme

1/*
2  DROP TABLE ADJUSTMENT_CODE_DIM if it exists
3 */
4
5IF OBJECT_ID('edw.dbo.ADJUSTMENT_CODE_DIM', 'U') IS NOT NULL 
6    DROP TABLE edw.dbo.ADJUSTMENT_CODE_DIM;
7GO
8
9/*
10  CREATE TABLE ADJUSTMENT_CODE_DIM
11 */
12
13CREATE TABLE edw.dbo.ADJUSTMENT_CODE_DIM
14(
15    /* Columns */
16    ADJUSTMENT_CODE_ID INT NOT NULL,
17    ADJUSTMENT_CODE_REASON VARCHAR(32) NOT NULL,
18    ADJUSTMENT_CODE_GROUP VARCHAR(32) NOT NULL
19    /* Primary key(s) optional */
20    , CONSTRAINT PK_ADJUSTMENT_CODE_DIM PRIMARY KEY (ADJUSTMENT_CODE_ID)
21    /* Foreign key constraint(s) optional */
22    
23);
24GO
25/*
26  DROP TABLE CLAIM_STATUS_DIM if it exists
27 */
28
29IF OBJECT_ID('edw.dbo.CLAIM_STATUS_DIM', 'U') IS NOT NULL 
30    DROP TABLE edw.dbo.CLAIM_STATUS_DIM;
31GO
32
33/*
34  CREATE TABLE CLAIM_STATUS_DIM
35 */
36
37CREATE TABLE edw.dbo.CLAIM_STATUS_DIM
38(
39    /* Columns */
40    CLAIM_STATUS_ID INT NOT NULL,
41    CLAIM_STATUS_TEXT VARCHAR(32) NOT NULL
42    /* Primary key(s) optional */
43    , CONSTRAINT PK_CLAIM_STATUS_DIM PRIMARY KEY (CLAIM_STATUS_ID)
44    /* Foreign key constraint(s) optional */
45    
46);
47GO
48/*
49  DROP TABLE DENIAL_CODE_DIM if it exists
50 */
51
52IF OBJECT_ID('edw.dbo.DENIAL_CODE_DIM', 'U') IS NOT NULL 
53    DROP TABLE edw.dbo.DENIAL_CODE_DIM;
54GO
55
56/*
57  CREATE TABLE DENIAL_CODE_DIM
58 */
59
60CREATE TABLE edw.dbo.DENIAL_CODE_DIM
61(
62    /* Columns */
63    DENIAL_CODE_ID INT NOT NULL,
64    DENIAL_TYPE VARCHAR(32) NOT NULL,
65    DENIAL_REASON VARCHAR(32) NOT NULL,
66    DENIAL_CATEGORY VARCHAR(32) NOT NULL
67    /* Primary key(s) optional */
68    , CONSTRAINT PK_DENIAL_CODE_DIM PRIMARY KEY (DENIAL_CODE_ID)
69    /* Foreign key constraint(s) optional */
70    
71);
72GO
73/*
74  DROP TABLE DIAGNOSIS_CODE_DIM if it exists
75 */
76
77IF OBJECT_ID('edw.dbo.DIAGNOSIS_CODE_DIM', 'U') IS NOT NULL 
78    DROP TABLE edw.dbo.DIAGNOSIS_CODE_DIM;
79GO
80
81/*
82  CREATE TABLE DIAGNOSIS_CODE_DIM
83 */
84
85CREATE TABLE edw.dbo.DIAGNOSIS_CODE_DIM
86(
87    /* Columns */
88    DIAGNOSIS_CODE_ID INT NOT NULL,
89    DIAGNOSIS_CODE_REASON VARCHAR(32) NOT NULL
90    /* Primary key(s) optional */
91    , CONSTRAINT PK_DIAGNOSIS_CODE_DIM PRIMARY KEY (DIAGNOSIS_CODE_ID)
92    /* Foreign key constraint(s) optional */
93    
94);
95GO
96/*
97  DROP TABLE PATIENT_DIM if it exists
98 */
99
100IF OBJECT_ID('edw.dbo.PATIENT_DIM', 'U') IS NOT NULL 
101    DROP TABLE edw.dbo.PATIENT_DIM;
102GO
103
104/*
105  CREATE TABLE PATIENT_DIM
106 */
107
108CREATE TABLE edw.dbo.PATIENT_DIM
109(
110    /* Columns */
111    PATIENT_ID INT NOT NULL,
112    PATIENT_NAME VARCHAR(25) NOT NULL,
113    PATIENT_CITY VARCHAR(25) NOT NULL,
114    PATIENT_STATE VARCHAR(2) NOT NULL,
115    PATIENT_ZIP VARCHAR(10) NOT NULL,
116    PATIENT_SSN VARCHAR(10) NULL,
117    PATIENT_DOB DATETIME NOT NULL
118    /* Primary key(s) optional */
119    , CONSTRAINT PK_PATIENT_DIM PRIMARY KEY (PATIENT_ID)
120    /* Foreign key constraint(s) optional */
121    
122);
123GO
124/*
125  DROP TABLE PAYER_DIM if it exists
126 */
127
128IF OBJECT_ID('edw.dbo.PAYER_DIM', 'U') IS NOT NULL 
129    DROP TABLE edw.dbo.PAYER_DIM;
130GO
131
132/*
133  CREATE TABLE PAYER_DIM
134 */
135
136CREATE TABLE edw.dbo.PAYER_DIM
137(
138    /* Columns */
139    PAYER_ID INT NOT NULL,
140    PAYER_NAME VARCHAR(25) NOT NULL,
141    PAYER_CITY VARCHAR(25) NOT NULL,
142    PAYER_STATE VARCHAR(2) NOT NULL,
143    PAYER_ZIP VARCHAR(10) NOT NULL
144    /* Primary key(s) optional */
145    , CONSTRAINT PK_PAYER_DIM PRIMARY KEY (PAYER_ID)
146    /* Foreign key constraint(s) optional */
147    
148);
149GO
150/*
151  DROP TABLE PHYSICIAN_DIM if it exists
152 */
153
154IF OBJECT_ID('edw.dbo.PHYSICIAN_DIM', 'U') IS NOT NULL 
155    DROP TABLE edw.dbo.PHYSICIAN_DIM;
156GO
157
158/*
159  CREATE TABLE PHYSICIAN_DIM
160 */
161
162CREATE TABLE edw.dbo.PHYSICIAN_DIM
163(
164    /* Columns */
165    PHYSICIAN_ID INT NOT NULL,
166    PHYSICIAN_NAME VARCHAR(25) NOT NULL,
167    PHYSICIAN_CITY VARCHAR(25) NOT NULL,
168    PHYSICIAN_STATE VARCHAR(2) NOT NULL,
169    PHYSICIAN_ZIP VARCHAR(10) NOT NULL
170    /* Primary key(s) optional */
171    , CONSTRAINT PK_PHYSICIAN_DIM PRIMARY KEY (PHYSICIAN_ID)
172    /* Foreign key constraint(s) optional */
173    
174);
175GO
176/*
177  DROP TABLE PROCEDURE_CODE_DIM if it exists
178 */
179
180IF OBJECT_ID('edw.dbo.PROCEDURE_CODE_DIM', 'U') IS NOT NULL 
181    DROP TABLE edw.dbo.PROCEDURE_CODE_DIM;
182GO
183
184/*
185  CREATE TABLE PROCEDURE_CODE_DIM
186 */
187
188CREATE TABLE edw.dbo.PROCEDURE_CODE_DIM
189(
190    /* Columns */
191    PROCEDURE_CODE_ID INT NOT NULL,
192    PROCEDURE_CODE_TEXT VARCHAR(32) NOT NULL
193    /* Primary key(s) optional */
194    , CONSTRAINT PK_PROCEDURE_CODE_DIM PRIMARY KEY (PROCEDURE_CODE_ID)
195    /* Foreign key constraint(s) optional */
196    
197);
198GO
199/*
200  DROP TABLE PROVIDER_DIM if it exists
201 */
202
203IF OBJECT_ID('edw.dbo.PROVIDER_DIM', 'U') IS NOT NULL 
204    DROP TABLE edw.dbo.PROVIDER_DIM;
205GO
206
207/*
208  CREATE TABLE PROVIDER_DIM
209 */
210
211CREATE TABLE edw.dbo.PROVIDER_DIM
212(
213    /* Columns */
214    PROVIDER_ID INT NOT NULL,
215    PROVIDER_NAME VARCHAR(25) NOT NULL,
216    PROVIDER_CITY VARCHAR(25) NOT NULL,
217    PROVIDER_STATE VARCHAR(2) NOT NULL,
218    PROVIDER_ZIP VARCHAR(10) NOT NULL
219    /* Primary key(s) optional */
220    , CONSTRAINT PK_PROVIDER_DIM PRIMARY KEY (PROVIDER_ID)
221    /* Foreign key constraint(s) optional */
222    
223);
224GO
225/*
226  DROP TABLE CLAIM_FACT if it exists
227 */
228
229IF OBJECT_ID('edw.dbo.CLAIM_FACT', 'U') IS NOT NULL 
230    DROP TABLE edw.dbo.CLAIM_FACT;
231GO
232
233/*
234  CREATE TABLE CLAIM_FACT
235 */
236
237CREATE TABLE edw.dbo.CLAIM_FACT
238(
239    /* Columns */
240    CLAIM_ID INT NOT NULL,
241    PROVIDER_ID INT NOT NULL,
242    PAYER_ID INT NOT NULL,
243    PHYSICIAN_ID INT NOT NULL,
244    PATIENT_ID INT NOT NULL
245    /* Primary key(s) optional */
246    , CONSTRAINT PK_CLAIM_FACT PRIMARY KEY (CLAIM_ID)
247    /* Foreign key constraint(s) optional */
248    , CONSTRAINT FK_CLAIM_FACT_PROVIDER_DIM
249        FOREIGN KEY (PROVIDER_ID)
250        REFERENCES dbo.PROVIDER_DIM (PROVIDER_ID)
251        ON DELETE CASCADE
252    , CONSTRAINT FK_CLAIM_FACT_PAYER_DIM
253        FOREIGN KEY (PAYER_ID)
254        REFERENCES dbo.PAYER_DIM (PAYER_ID)
255        ON DELETE CASCADE
256    , CONSTRAINT FK_CLAIM_FACT_PHYSICIAN_DIM
257        FOREIGN KEY (PHYSICIAN_ID)
258        REFERENCES dbo.PHYSICIAN_DIM (PHYSICIAN_ID)
259        ON DELETE CASCADE
260    , CONSTRAINT FK_CLAIM_FACT_PATIENT_DIM
261        FOREIGN KEY (PATIENT_ID)
262        REFERENCES dbo.PATIENT_DIM (PATIENT_ID)
263        ON DELETE CASCADE
264    
265);
266GO
267/*
268  DROP TABLE REMIT_FACT if it exists
269 */
270
271IF OBJECT_ID('edw.dbo.REMIT_FACT', 'U') IS NOT NULL 
272    DROP TABLE edw.dbo.REMIT_FACT;
273GO
274
275/*
276  CREATE TABLE REMIT_FACT
277 */
278
279CREATE TABLE edw.dbo.REMIT_FACT
280(
281    /* Columns */
282    REMIT_ID INT NOT NULL,
283    CLAIM_ID INT NOT NULL,
284    PROVIDER_ID INT NOT NULL,
285    PAYER_ID INT NOT NULL,
286    PHYSICIAN_ID INT NOT NULL,
287    PATIENT_ID INT NOT NULL
288    /* Primary key(s) optional */
289    , CONSTRAINT PK_REMIT_FACT PRIMARY KEY (REMIT_ID)
290    /* Foreign key constraint(s) optional */
291    , CONSTRAINT FK_REMIT_FACT_CLAIM_FACT
292        FOREIGN KEY (CLAIM_ID)
293        REFERENCES dbo.CLAIM_FACT (CLAIM_ID)
294        ON DELETE CASCADE
295    , CONSTRAINT FK_REMIT_FACT_PROVIDER_DIM
296        FOREIGN KEY (PROVIDER_ID)
297        REFERENCES dbo.PROVIDER_DIM (PROVIDER_ID)
298        ON DELETE CASCADE
299    , CONSTRAINT FK_REMIT_FACT_PAYER_DIM
300        FOREIGN KEY (PAYER_ID)
301        REFERENCES dbo.PAYER_DIM (PAYER_ID)
302        ON DELETE CASCADE
303    , CONSTRAINT FK_REMIT_FACT_PHYSICIAN_DIM
304        FOREIGN KEY (PHYSICIAN_ID)
305        REFERENCES dbo.PHYSICIAN_DIM (PHYSICIAN_ID)
306        ON DELETE CASCADE
307    , CONSTRAINT FK_REMIT_FACT_PATIENT_DIM
308        FOREIGN KEY (PATIENT_ID)
309        REFERENCES dbo.PATIENT_DIM (PATIENT_ID)
310        ON DELETE CASCADE
311    
312);
313GO
314/*
315  DROP TABLE CLAIM_ITEM if it exists
316 */
317
318IF OBJECT_ID('edw.dbo.CLAIM_ITEM', 'U') IS NOT NULL 
319    DROP TABLE edw.dbo.CLAIM_ITEM;
320GO
321
322/*
323  CREATE TABLE CLAIM_ITEM
324 */
325
326CREATE TABLE edw.dbo.CLAIM_ITEM
327(
328    /* Columns */
329    CLAIM_ID INT NOT NULL,
330    CLAIM_CHECK_DATE DATETIME NULL,
331    CLAIM_BILL_DATE DATETIME NOT NULL,
332    CLAIM_LAST_BILLED_DATE DATETIME NOT NULL,
333    CLAIM_TOTAL_AMOUNT MONEY NOT NULL,
334    CLAIM_EXPECTED_REIMBURSEMENT MONEY NOT NULL,
335    CLAIM_STATUS INT NOT NULL,
336    CLAIM_PROC_CODE INT NOT NULL,
337    CLAIM_DIAGNOSIS_CODE INT NOT NULL
338    /* Primary key(s) optional */
339    , CONSTRAINT PK_CLAIM_ITEM PRIMARY KEY ()
340    /* Foreign key constraint(s) optional */
341    , CONSTRAINT FK_CLAIM_ITEM_CLAIM_DIM
342        FOREIGN KEY (CLAIM_ID)
343        REFERENCES dbo.CLAIM_DIM (CLAIM_ID)
344        ON DELETE CASCADE
345    , CONSTRAINT FK_CLAIM_ITEM_DIAGNOSIS_CODE_DIM
346        FOREIGN KEY (CLAIM_DIAGNOSIS_CODE)
347        REFERENCES dbo.DIAGNOSIS_CODE_DIM (DIAGNOSIS_CODE_ID)
348        ON DELETE CASCADE
349    , CONSTRAINT FK_CLAIM_ITEM_CLAIM_STATUS_DIM
350        FOREIGN KEY (CLAIM_STATUS)
351        REFERENCES dbo.CLAIM_STATUS_DIM (CLAIM_STATUS_ID)
352        ON DELETE CASCADE
353    
354);
355GO
356/*
357  DROP TABLE REMIT_ITEM if it exists
358 */
359
360IF OBJECT_ID('edw.dbo.REMIT_ITEM', 'U') IS NOT NULL 
361    DROP TABLE edw.dbo.REMIT_ITEM;
362GO
363
364/*
365  CREATE TABLE REMIT_ITEM
366 */
367
368CREATE TABLE edw.dbo.REMIT_ITEM
369(
370    /* Columns */
371    REMIT_ID INT NOT NULL,
372    REMIT_ADJUSTMENT_ID INT NULL,
373    REMIT_DENIAL_ID INT NULL,
374    REMIT_BILL_DATE DATETIME NOT NULL,
375    REMIT_PAY_DATE DATETIME NOT NULL,
376    CONTRACTUAL_OBLIGATION_AMOUNT MONEY NOT NULL,
377    REVERSAL_AMOUNT MONEY NOT NULL,
378    PATIENT_RESPONSIBILITY_AMOUNT MONEY NOT NULL,
379    PAYER_INITIATED_AMOUNT MONEY NOT NULL,
380    ADJUSTMENT_AMOUNT MONEY NOT NULL,
381    PAYMENT_AMOUNT MONEY NOT NULL
382    /* Primary key(s) optional */
383    , CONSTRAINT PK_REMIT_ITEM PRIMARY KEY ()
384    /* Foreign key constraint(s) optional */
385    , CONSTRAINT FK_REMIT_ITEM_REMIT_FACT
386        FOREIGN KEY (REMIT_ID)
387        REFERENCES dbo.REMIT_FACT (REMIT_ID)
388        ON DELETE CASCADE
389    , CONSTRAINT FK_REMIT_ITEM_ADJUSTMENT_CODE_DIM
390        FOREIGN KEY (REMIT_ADJUSTMENT_ID)
391        REFERENCES dbo.ADJUSTMENT_CODE_DIM (ADJUSTMENT_CODE_ID)
392        ON DELETE CASCADE
393    , CONSTRAINT FK_REMIT_ITEM_DENIAL_CODE_DIM
394        FOREIGN KEY (REMIT_DENIAL_ID)
395        REFERENCES dbo.DENIAL_CODE_DIM (DENIAL_CODE_ID)
396        ON DELETE CASCADE
397    
398);
399GO
400/*
401  DROP TABLE CLAIM_RAW if it exists
402 */
403
404IF OBJECT_ID('raw.dbo.CLAIM_RAW', 'U') IS NOT NULL 
405    DROP TABLE raw.dbo.CLAIM_RAW;
406GO
407
408/*
409  CREATE TABLE CLAIM_RAW
410 */
411
412CREATE TABLE raw.dbo.CLAIM_RAW
413(
414    /* Columns */
415    PATIENT_NAME VARCHAR(25) NOT NULL,
416    PATIENT_CITY VARCHAR(25) NOT NULL,
417    PATIENT_STATE VARCHAR(2) NOT NULL,
418    PATIENT_ZIP VARCHAR(10) NOT NULL,
419    PATIENT_SSN VARCHAR(10) NULL,
420    PATIENT_DOB DATETIME NOT NULL,
421    PAYER_NAME VARCHAR(25) NOT NULL,
422    PAYER_CITY VARCHAR(25) NOT NULL,
423    PAYER_STATE VARCHAR(2) NOT NULL,
424    PAYER_ZIP VARCHAR(10) NOT NULL,
425    PHYSICIAN_NAME VARCHAR(25) NOT NULL,
426    PHYSICIAN_CITY VARCHAR(25) NOT NULL,
427    PHYSICIAN_STATE VARCHAR(2) NOT NULL,
428    PHYSICIAN_ZIP VARCHAR(10) NOT NULL,
429    PROVIDER_NAME VARCHAR(25) NOT NULL,
430    PROVIDER_CITY VARCHAR(25) NOT NULL,
431    PROVIDER_STATE VARCHAR(2) NOT NULL,
432    PROVIDER_ZIP VARCHAR(10) NOT NULL,
433    CLAIM_DATE DATETIME NOT NULL,
434    CLAIM_AMOUNT MONEY NOT NULL
435    /* Primary key(s) optional */
436    /* Foreign key constraint(s) optional */
437    
438);
439GO
440/*
441  DROP TABLE REMIT_RAW if it exists
442 */
443
444IF OBJECT_ID('raw.dbo.REMIT_RAW', 'U') IS NOT NULL 
445    DROP TABLE raw.dbo.REMIT_RAW;
446GO
447
448/*
449  CREATE TABLE REMIT_RAW
450 */
451
452CREATE TABLE raw.dbo.REMIT_RAW
453(
454    /* Columns */
455    PATIENT_NAME VARCHAR(25) NOT NULL,
456    PATIENT_CITY VARCHAR(25) NOT NULL,
457    PATIENT_STATE VARCHAR(2) NOT NULL,
458    PATIENT_ZIP VARCHAR(10) NOT NULL,
459    PATIENT_SSN VARCHAR(10) NULL,
460    PATIENT_DOB DATETIME NOT NULL,
461    PAYER_NAME VARCHAR(25) NOT NULL,
462    PAYER_CITY VARCHAR(25) NOT NULL,
463    PAYER_STATE VARCHAR(2) NOT NULL,
464    PAYER_ZIP VARCHAR(10) NOT NULL,
465    PHYSICIAN_NAME VARCHAR(25) NOT NULL,
466    PHYSICIAN_CITY VARCHAR(25) NOT NULL,
467    PHYSICIAN_STATE VARCHAR(2) NOT NULL,
468    PHYSICIAN_ZIP VARCHAR(10) NOT NULL,
469    PROVIDER_NAME VARCHAR(25) NOT NULL,
470    PROVIDER_CITY VARCHAR(25) NOT NULL,
471    PROVIDER_STATE VARCHAR(2) NOT NULL,
472    PROVIDER_ZIP VARCHAR(10) NOT NULL,
473    REMIT_DATE DATETIME NOT NULL,
474    REMIT_AMOUNT MONEY NOT NULL
475    /* Primary key(s) optional */
476    /* Foreign key constraint(s) optional */
477    
478);
479GO