rJ2Tzxf2

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    /* Foreign key constraint(s) optional */
340    , CONSTRAINT FK_CLAIM_ITEM_CLAIM_FACT
341        FOREIGN KEY (CLAIM_ID)
342        REFERENCES dbo.CLAIM_FACT (CLAIM_ID)
343        ON DELETE CASCADE
344    , CONSTRAINT FK_CLAIM_ITEM_DIAGNOSIS_CODE_DIM
345        FOREIGN KEY (CLAIM_DIAGNOSIS_CODE)
346        REFERENCES dbo.DIAGNOSIS_CODE_DIM (DIAGNOSIS_CODE_ID)
347        ON DELETE CASCADE
348    , CONSTRAINT FK_CLAIM_ITEM_CLAIM_STATUS_DIM
349        FOREIGN KEY (CLAIM_STATUS)
350        REFERENCES dbo.CLAIM_STATUS_DIM (CLAIM_STATUS_ID)
351        ON DELETE CASCADE
352    
353);
354GO
355/*
356  DROP TABLE REMIT_ITEM if it exists
357 */
358
359IF OBJECT_ID('edw.dbo.REMIT_ITEM', 'U') IS NOT NULL 
360    DROP TABLE edw.dbo.REMIT_ITEM;
361GO
362
363/*
364  CREATE TABLE REMIT_ITEM
365 */
366
367CREATE TABLE edw.dbo.REMIT_ITEM
368(
369    /* Columns */
370    REMIT_ID INT NOT NULL,
371    REMIT_ADJUSTMENT_ID INT NULL,
372    REMIT_DENIAL_ID INT NULL,
373    REMIT_BILL_DATE DATETIME NOT NULL,
374    REMIT_PAY_DATE DATETIME NOT NULL,
375    CONTRACTUAL_OBLIGATION_AMOUNT MONEY NOT NULL,
376    REVERSAL_AMOUNT MONEY NOT NULL,
377    PATIENT_RESPONSIBILITY_AMOUNT MONEY NOT NULL,
378    PAYER_INITIATED_AMOUNT MONEY NOT NULL,
379    ADJUSTMENT_AMOUNT MONEY NOT NULL,
380    PAYMENT_AMOUNT MONEY NOT NULL
381    /* Primary key(s) optional */
382    /* Foreign key constraint(s) optional */
383    , CONSTRAINT FK_REMIT_ITEM_REMIT_FACT
384        FOREIGN KEY (REMIT_ID)
385        REFERENCES dbo.REMIT_FACT (REMIT_ID)
386        ON DELETE CASCADE
387    , CONSTRAINT FK_REMIT_ITEM_ADJUSTMENT_CODE_DIM
388        FOREIGN KEY (REMIT_ADJUSTMENT_ID)
389        REFERENCES dbo.ADJUSTMENT_CODE_DIM (ADJUSTMENT_CODE_ID)
390        ON DELETE CASCADE
391    , CONSTRAINT FK_REMIT_ITEM_DENIAL_CODE_DIM
392        FOREIGN KEY (REMIT_DENIAL_ID)
393        REFERENCES dbo.DENIAL_CODE_DIM (DENIAL_CODE_ID)
394        ON DELETE CASCADE
395    
396);
397GO
398/*
399  DROP TABLE CLAIM_RAW if it exists
400 */
401
402IF OBJECT_ID('raw.dbo.CLAIM_RAW', 'U') IS NOT NULL 
403    DROP TABLE raw.dbo.CLAIM_RAW;
404GO
405
406/*
407  CREATE TABLE CLAIM_RAW
408 */
409
410CREATE TABLE raw.dbo.CLAIM_RAW
411(
412    /* Columns */
413    PATIENT_NAME VARCHAR(25) NOT NULL,
414    PATIENT_CITY VARCHAR(25) NOT NULL,
415    PATIENT_STATE VARCHAR(2) NOT NULL,
416    PATIENT_ZIP VARCHAR(10) NOT NULL,
417    PATIENT_SSN VARCHAR(10) NULL,
418    PATIENT_DOB DATETIME NOT NULL,
419    PAYER_NAME VARCHAR(25) NOT NULL,
420    PAYER_CITY VARCHAR(25) NOT NULL,
421    PAYER_STATE VARCHAR(2) NOT NULL,
422    PAYER_ZIP VARCHAR(10) NOT NULL,
423    PHYSICIAN_NAME VARCHAR(25) NOT NULL,
424    PHYSICIAN_CITY VARCHAR(25) NOT NULL,
425    PHYSICIAN_STATE VARCHAR(2) NOT NULL,
426    PHYSICIAN_ZIP VARCHAR(10) NOT NULL,
427    PROVIDER_NAME VARCHAR(25) NOT NULL,
428    PROVIDER_CITY VARCHAR(25) NOT NULL,
429    PROVIDER_STATE VARCHAR(2) NOT NULL,
430    PROVIDER_ZIP VARCHAR(10) NOT NULL,
431    CLAIM_DATE DATETIME NOT NULL,
432    CLAIM_AMOUNT MONEY NOT NULL
433    /* Primary key(s) optional */
434    /* Foreign key constraint(s) optional */
435    
436);
437GO
438/*
439  DROP TABLE REMIT_RAW if it exists
440 */
441
442IF OBJECT_ID('raw.dbo.REMIT_RAW', 'U') IS NOT NULL 
443    DROP TABLE raw.dbo.REMIT_RAW;
444GO
445
446/*
447  CREATE TABLE REMIT_RAW
448 */
449
450CREATE TABLE raw.dbo.REMIT_RAW
451(
452    /* Columns */
453    PATIENT_NAME VARCHAR(25) NOT NULL,
454    PATIENT_CITY VARCHAR(25) NOT NULL,
455    PATIENT_STATE VARCHAR(2) NOT NULL,
456    PATIENT_ZIP VARCHAR(10) NOT NULL,
457    PATIENT_SSN VARCHAR(10) NULL,
458    PATIENT_DOB DATETIME NOT NULL,
459    PAYER_NAME VARCHAR(25) NOT NULL,
460    PAYER_CITY VARCHAR(25) NOT NULL,
461    PAYER_STATE VARCHAR(2) NOT NULL,
462    PAYER_ZIP VARCHAR(10) NOT NULL,
463    PHYSICIAN_NAME VARCHAR(25) NOT NULL,
464    PHYSICIAN_CITY VARCHAR(25) NOT NULL,
465    PHYSICIAN_STATE VARCHAR(2) NOT NULL,
466    PHYSICIAN_ZIP VARCHAR(10) NOT NULL,
467    PROVIDER_NAME VARCHAR(25) NOT NULL,
468    PROVIDER_CITY VARCHAR(25) NOT NULL,
469    PROVIDER_STATE VARCHAR(2) NOT NULL,
470    PROVIDER_ZIP VARCHAR(10) NOT NULL,
471    REMIT_DATE DATETIME NOT NULL,
472    REMIT_AMOUNT MONEY NOT NULL
473    /* Primary key(s) optional */
474    /* Foreign key constraint(s) optional */
475    
476);
477GO