· 8 years ago · May 27, 2018, 08:02 PM
1/*************************************************************************/
2/* To create BPCIA required schema/tables */
3/* Recommend to set schema name as bpci_CCN# */
4/*************************************************************************/
5/* create BPCIA schema */
6drop schema if exists bpcia_test;
7CREATE SCHEMA bpcia_test;
8use bpcia_test;
9
10set SQL_SAFE_UPDATES = 0;
11
12/*********************************************************************/
13/* Processing tables */
14/*********************************************************************/
15/*epi start */
16DROP TABLE IF EXISTS `bpci_epi`;
17CREATE TABLE `bpci_epi`
18(
19 `EPI_ID` varchar(35),
20 `BENE_SK` varchar(25),
21 `curhic_uneq` varchar(30), -- clm_hic_no
22 `anchor_provider` varchar(25), -- provider_ccn
23 `anchor_trigger_cd` varchar(10) , -- drg_cd
24 `episode_group_name` varchar(255), -- drg_epi
25 `FRACTURE` tinyint ,
26 `ANCHOR_BEG_DT` date,
27 `ANCHOR_END_DT` date,
28 `POST_DSCH_BEG_DT` date,
29 `POST_DSCH_END_DT` date,
30 `EPI_TOTAL` decimal(30,10),
31 `EPI_IPPS` decimal(30,10),
32 `EPI_PB_PHYS` decimal(30,10),
33 `EPI_PB_ANES` decimal(30,10),
34 `EPI_IRF` decimal(30,10),
35 `EPI_SNF_PPS` decimal(30,10),
36 `EPI_HH_PPS` decimal(30,10),
37 `EPI_OTHER` decimal(30,10),
38 `POSTEPI_TOTAL` decimal(30,10),
39 `POSTEPI_IPPS` decimal(30,10),
40 `POSTEPI_PB_PHYS` decimal(30,10),
41 `POSTEPI_PB_ANES` decimal(30,10),
42 `POSTEPI_IRF` decimal(30,10),
43 `POSTEPI_SNF_PPS` decimal(30,10),
44 `POSTEPI_HH_PPS` decimal(30,10),
45 `POSTEPI_OTHER` decimal(30,10),
46 `EPI_TOTAL_ALLOWED` decimal(30,10),
47 `EPI_IPPS_ALLOWED` decimal(30,10),
48 `EPI_PB_PHYS_ALLOWED` decimal(30,10),
49 `EPI_PB_ANES_ALLOWED` decimal(30,10),
50 `EPI_IRF_ALLOWED` decimal(30,10),
51 `EPI_SNF_PPS_ALLOWED` decimal(30,10),
52 `EPI_HH_PPS_ALLOWED` decimal(30,10),
53 `EPI_OTHER_ALLOWED` decimal(30,10),
54 `POSTEPI_TOTAL_ALLOWED` decimal(30,10),
55 `POSTEPI_IPPS_ALLOWED` decimal(30,10),
56 `POSTEPI_PB_PHYS_ALLOWED` decimal(30,10),
57 `POSTEPI_PB_ANES_ALLOWED` decimal(30,10),
58 `POSTEPI_IRF_ALLOWED` decimal(30,10),
59 `POSTEPI_SNF_PPS_ALLOWED` decimal(30,10),
60 `POSTEPI_HH_PPS_ALLOWED` decimal(30,10),
61 `POSTEPI_OTHER_ALLOWED` decimal(30,10),
62 `EPI_IPFILE` decimal(30,10),
63 `EPI_SNFILE` decimal(30,10),
64 `EPI_HHFILE` decimal(30,10),
65 `EPI_OPFILE` decimal(30,10),
66 `EPI_PBFILE` decimal(30,10),
67 `EPI_DMFILE` decimal(30,10),
68 `EPI_HSFILE` decimal(30,10),
69 `EPI_ANCHOR` varchar(30),
70 `ANCHOR_AT_NPI` varchar(30),
71 `ANCHOR_OP_NPI` varchar(30),
72 `ANCHOR_LOS` varchar(5),
73 `HAS_READMIT` varchar(2),
74 `IMPUTATION` varchar(2) ,
75 #exist fields
76 `EPI_ACUTE` decimal(30,10),
77 `EPI_PB` decimal(30,10) ,
78 `EPI_SNF` decimal(30,10) ,
79 `EPI_HHA` decimal(30,10) ,
80 `DUAL_STUS` varchar(2),
81 `FUOV` tinyint default 0,
82 `DEL_IND` tinyint,
83 `RUN_IND` tinyint,
84 `CONFLICT_IND` tinyint default 0,
85 `FILE_TYPE` varchar(25),
86 -- BPCIA new fields
87 `BENE_AGE` tinyint default 0,
88 `ANCHOR_TYPE` varchar(40),
89 `DRG_2018` varchar(40) default null,
90 `ANCHOR_APC` varchar(40),
91 `ANCHOR_APC_PMT_RATE` varchar(40),
92 `ANCHOR_C_APC_FLAG` varchar(40),
93 `PERF_APC` varchar(40),
94 `PRF_APC_PMT_RATE` varchar(40),
95 `PERF_C_APC_FLAG` varchar(40),
96 `COMP_ADJ_APC_FLAG` varchar(40),
97 -- `ANCHOR_AT_NPI` varchar(40),
98 -- `ANCHOR_OP_NPI` varchar(40),
99 `ANCHOR_STANDARD_ALLOWED_AMT` varchar(40),
100 `ANCHOR_ALLOWED_AMT` varchar(40),
101 `TRANSFER_STAY` varchar(40),
102 `DROP_EPISODE` tinyint,
103 `DROPFLAG_NOT_CONT_ENR_AB_NO_C` tinyint,
104 `DROPFLAG_ESRD` tinyint,
105 `DROPFLAG_OTHER_PRIMARY_PAYER` tinyint,
106 `DROPFLAG_NOT_IN_UBER` tinyint,
107 `DROPFLAG_NON_ACH` tinyint,
108 `DROPFLAG_LOS_GT_59` tinyint,
109 `DROPFLAG_STUS_CD` tinyint,
110 `DROPFLAG_EXCLUDED_STATE` tinyint,
111 `DROPFLAG_NON_HIGHEST_J1` tinyint,
112 `DROPFLAG_EPISODE_OVERLAP` tinyint,
113 `DROPFLAG_DEATH_DUR_ANCHOR` tinyint,
114 `DROPFLAG_TRANS_W_CAH_CANCER` tinyint,
115 `DEATH_DUR_POSTDSCHRG` tinyint,
116 `TOT_STD_ALLOWED` decimal(30,10),
117 `TOT_RAW_ALLOWED` decimal(30,10),
118 `TOT_STD_ALLOWED_OPL` decimal(30,10),
119 `TOT_STD_ALLOWED_IP` decimal(30,10),
120 `TOT_STD_ALLOWED_DM` decimal(30,10),
121 `TOT_STD_ALLOWED_PB` decimal(30,10),
122 `TOT_STD_ALLOWED_SN` decimal(30,10),
123 `TOT_STD_ALLOWED_HS` decimal(30,10),
124 `TOT_STD_ALLOWED_HH_NONRAP` decimal(30,10),
125 `EPI_STD_PMT_FCTR` decimal(30,10),
126 `WINSORIZE_1_99` tinyint,
127 `EPI_STD_PMT_FCTR_WIN_1_99` decimal(30,10)
128
129 ) ;
130ALTER TABLE `bpci_epi` ADD INDEX `bpci_epi_idx1` (`EPI_ID`);
131ALTER TABLE `bpci_epi` ADD INDEX `bpci_epi_idx2` (`FILE_TYPE`, `EPI_ID`, `BENE_SK`);
132
133
134
135/*epiexcl start */
136DROP TABLE IF EXISTS `bpci_epi_excl`;
137CREATE TABLE `bpci_epi_excl`
138(
139 `EPI_ID` varchar(35) nOT nuLL,
140 `BENE_SK` varchar(25),
141 `GEO_BENE_SK` varchar(50),
142 `CLM_DT_SGNTR_SK` varchar(50),
143 `CLM_TYPE_CD` varchar(5),
144 `claimno` varchar(5),
145 `curhic_uneq` varchar(20),
146 `anchor_provider` varchar(25),
147 `STUS_CD` varchar(2),
148 `PRPAYCD` varchar(1),
149 `MCOPDSW` varchar(1),
150 `anchor_trigger_cd` int,
151 `ADMSN_DT` date,
152 `DSCHRGDT` date,
153 `INTERIM_CLAIM` int,
154 `PRPAYAMT` varchar(20),
155 `STAY_BEG_DT` date,
156 `STAY_END_DT` date,
157 `ALLOWED_PAY` decimal(30,10),
158 `STD_ALLOWED_AMT` decimal(30,10),
159 `STD_NO_OUTLIER_ALOWD_AMT` decimal(30,10),
160 `FRACTURE` tinyint,
161 `DROPREASON` int,
162 `FILE_TYPE` varchar(25)
163) ;
164
165ALTER TABLE `bpci_epi_excl` ADD INDEX `bpci_epiexcl_idx1` ( `FILE_TYPE`,`EPI_ID`);
166
167DROP TABLE IF EXISTS `bpci_epi_excl_dropcode`;
168CREATE TABLE `bpci_epi_excl_dropcode`
169(
170 `EPI_ID` varchar(35) nOT nuLL,
171 `DROPREASON` tinyint(3),
172 `FILE_TYPE` varchar(25)
173 );
174ALTER TABLE `bpci_epi_excl_dropcode` ADD INDEX `bpci_epiexcldrop_idx1` ( `FILE_TYPE`,`EPI_ID`);
175
176
177
178
179/*start claims
180DROP TABLE IF EXISTS `bpci_claims`;
181CREATE TABLE `bpci_claims`
182(
183 uid INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
184 `EPI_ID` varchar(35) NOT NULL,
185 `MASTER_CLM_ID` varchar(100) NOT NULL default '',
186 `GEO_BENE_SK` varchar(25),
187 `CLM_DT_SGNTR_SK` varchar(20),
188 `CLM_TYPE_CD` varchar(5),
189 `CLM_TYPE_NO` int,
190 `claimno` varchar(5),
191 `BENE_SK` int,
192 `curhic_uneq` varchar(20),
193 `CLM_FROM_DT` date,
194 `CLM_THRU_DT` date,
195 `anchor_provider` varchar(25),
196 `STUS_CD` varchar(2),
197 `PRPAYCD` varchar(1),
198 `MCOPDSW` varchar(1),
199 `anchor_trigger_cd` varchar(10),
200 `ADMSN_DT` date,
201 `DSCHRGDT` date,
202 `OUTLR_CD` int,
203 `PMT_AMT` decimal(30,2),
204 `DED_AMT` decimal(30,2),
205 `COIN_AMT` decimal(30,2),
206 `PER_DIEM` decimal(30,2),
207 `CHARGAMT` decimal(30,2),
208 `PPS_CPTL` decimal(30,2),
209 `OUTLRPMT` decimal(30,2),
210 `BLDDEDAM` decimal(30,2),
211 `PRPAYAMT` decimal(30,2),
212 `FREQ_CD` varchar(5),
213 `FAC_TYPE` int,
214 `CLM_CNTL` varchar(50),
215 `TYPE_ADM` varchar(10),
216 `SRC_ADMS` varchar(10),
217 `UTIL_DAY` int,
218 `PRSTATE` varchar(10),
219 `PDGNS_CD` varchar(10),
220 `AT_NPI` varchar(10),
221 `OP_NPI` varchar(10),
222 `ADMTG_DGNS_CD` varchar(10),
223 `CLM_MDCL_REC` varchar(50),
224 `CLM_PTNT_CNTL` varchar(25),
225 `PRVDR_NAME` varchar(100),
226 `CLM_STD_ALOWD_AMT` varchar(25),
227 `CLM_NO_OUTLIER_ALOWD_AMT` varchar(25),
228 `STD_AMT_EPI` varchar(25),
229 `STD_AMT_HCI3` varchar(25) default 0,
230 `STD_AMT_HCI3_PRORATED` varchar(25) default 0,
231 `NON_STD_SUB` varchar(25),
232 `COSTINC` varchar(1),
233 `ANCHOR` varchar(1),
234 `PROV` varchar(10),
235 `PRORATED` int,
236 `UNCOMPD_CARE_AMT` decimal(30,2) null,
237 `VBP_AMT` decimal(30,2) null,
238 `READMSN_RDCTN_AMT` decimal(30,2),
239 `CARE_IMPRVMT_M4_CD` varchar(10),
240 `CLM_CARR_PMT_DNL_CD` varchar(2),
241 `ASGMNT_CD` varchar(10),
242 `ORG_NPI` varchar(20),
243 `VISIT_CNT` int,
244 `CLM_PD_DT` date,
245 `CLM_PROD_TYPE_CD` varchar(1),
246 `CLM_VAL_SQNC_NUM` varchar(1),
247 `CODE_VALUE` varchar(10),
248 `CLM_DGNS_PRCDR_ICD_IND` varchar(1),
249 `CARR_NUM` varchar(10) ,
250 `CLM_POA_IND` varchar(1),
251 `CLM_PRCDR_PRFRM_DT` date,
252 `ALOWD_CHRG_AMT` decimal(30,2),
253 `ALOWD_NET_PAY` decimal(30,2),
254 `DGNS_PRCDR_ICD_IND` varchar(2),
255 `HEMOFACA` varchar(1),
256 `SUM_VAL_AMT` decimal(30,2),
257 `IPPS_IND` tinyint (1) default 0,
258 `MSDRG_IND` tinyint(1) default 0,
259 `OVLAP_IND` tinyint(1) default 0,
260 `INTSTAY_IND` tinyint(1) default 0,
261 `STAY_ID` tinyint(1) default 0,
262 `READMIS_IND` tinyint(1) default 0,
263 `XFER_IND` tinyint(1) default 0,
264 `ANCHOR_IND` tinyint (1) default 0,
265 `SERVICE_TYPE` varchar(50),
266 `Rehab_IND` tinyint (1) default 0,
267 `LTCH_IND` tinyint (1) default 0,
268 `IPF_IND` tinyint (1) default 0,
269 `DRG_EXCL_IND` tinyint (1) default 0,
270 `post_dsch_win` varchar(10),
271 `clm_group` varchar(10),
272 `clm_assigmnt` varchar(10),
273 `PAC_CD` varchar(10),
274 `OFFICE_VISIT` varchar(1),
275 `ICD910` tinyint(1) default 0 ,
276 `
277 `DEL_IND` tinyint (1) default 0,
278 `ALLOWED_PAY` decimal(30,2) null,
279 `ALLOWED_AMT_EPI` decimal(30,2) null,
280 `ALLOWED_PAY_EPI` decimal(30,2) null,
281 `EXCLUSION_CODE` varchar(10) null,
282 `STAYID` varchar(10),
283 `CONFLICT_IND` tinyint default 0,
284 `FILE_TYPE` varchar(50),
285
286 -- BPCIA Additions
287 -- `ADMSN_DT` date,
288 -- `DSCHRGDT` date,
289 `STD_ALLOWED` decimal(30,2),
290 `LINE_ALLOWED` decimal(30,2),
291 `CLM_ALLOWED` decimal(30,2),
292 `LINE_STD_ALLOWED` decimal(30,2),
293 `RVNCVR01-RVNCVR45` decimal(30,2),
294 `LPRPDAMT` decimal(30,2),
295 `LINEITEM` varchar(60),
296 `LUPAIND` varchar(10),
297 `PRCRRTRN` varchar(10),
298 `RSTUSIND` varchar(10),
299 `SGMT_NUM` varchar(30),
300 `TAX_NUM` varchar(30),
301 `PRFNPI` varchar(30),
302 `HCPCS_CD` varchar(10),
303
304 -- from EPI
305 `BENE_AGE` tinyint default 0,
306 `ANCHOR_TYPE` varchar(40),
307 `DRG_2018` varchar(40),
308 `ANCHOR_APC` varchar(40),
309 `ANCHOR_APC_PMT_RATE` varchar(40),
310 `ANCHOR_C_APC_FLAG` varchar(40),
311 `PERF_APC` varchar(40),
312 `PERF_APC_PMT_RATE` varchar(40),
313 `PERF_C_APC_FLAG` varchar(40),
314 `COMP_ADJ_APC_FLAG` varchar(40),
315 `ANCHOR_AT_NPI` varchar(40),
316 `ANCHOR_OP_NPI` varchar(40),
317 `ANCHOR_STANDARD_ALLOWED_AMT` varchar(40),
318 `ANCHOR_ALLOWED_AMT` varchar(40),
319 `TRANSFER_STAY` varchar(40),
320 `TOT_STD_ALLOWED` decimal(30,10),
321 `TOT_RAW_ALLOWED` decimal(30,10),
322 `TOT_STD_ALLOWED_OPL` decimal(30,10),
323 `TOT_STD_ALLOWED_IP` decimal(30,10),
324 `TOT_STD_ALLOWED_DM` decimal(30,10),
325 `TOT_STD_ALLOWED_PB` decimal(30,10),
326 `TOT_STD_ALLOWED_SN` decimal(30,10),
327 `TOT_STD_ALLOWED_HS` decimal(30,10),
328 `TOT_STD_ALLOWED_HH_NONRAP` decimal(30,10),
329 `EPI_STD_PMT_FCTR` decimal(30,10),
330 `WINSORIZE_1_99` tinyint,
331 `EPI_STD_PMT_FCTR_WIN_1_99` decimal(30,10)
332
333) ;
334
335ALTER TABLE `bpci_claims` ADD INDEX `clm_idx1` ( `FILE_TYPE`,`EPI_ID`,`MASTER_CLM_ID`);
336ALTER TABLE `bpci_claims` ADD INDEX `clm_idx2` ( `FILE_TYPE`,`CLM_TYPE_CD`,`EPI_ID`,`MASTER_CLM_ID`);
337ALTER TABLE `bpci_claims` ADD INDEX `clm_idx3` (`FILE_TYPE`,`anchor_provider`);
338ALTER TABLE `bpci_claims` ADD INDEX `clm_idx4` (`prpayamt`);
339ALTER TABLE `bpci_claims` ADD INDEX `clm_idx5` (`ADMSN_DT`); */
340
341/*Claim detail start 1*/
342DROP TABLE IF EXISTS `bpci_claim_detail`;
343CREATE TABLE `bpci_claim_detail`
344(
345 uid INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
346 `FILE_TYPE` varchar(10),
347 `EPI_ID` varchar(35) NOT NULL,
348 `MASTER_CLM_ID` varchar(100) NOT NULL,
349 `CLM_TYPE_CD` varchar(5),
350 `LINE_NUM` int,
351 `REV_DT` date,
352 `REV_CNTR` varchar(10),
353 `REV_UNIT` varchar(10),
354 `HCPCS_CD` varchar(10),
355 `APCHIPPS` varchar(10),
356 `MDFR_CD1` varchar(10),
357 `MDFR_CD2` varchar(10),
358 `ICD_VRSN_CD` varchar(1),
359 `PMTMTHD` varchar(5),
360 `REV_PMT_AMT` decimal(30,2),
361 `PTNRSP_AMT` decimal(30,2),
362 `INSTNL_RATE_AMT` decimal(30,2),
363 `POS_CD` int,
364 `CLM_FROM_DT` date,
365 `CLM_TO_DT` date,
366
367 `ALOWD_CHRG_AMT` decimal(30,2),
368 `PROD_BETOS_CD` varchar(10),
369 `DGNS_PRCDR_ICD_IND` varchar(2),
370 `FED_TYPE_SRVC_CD` varchar(1),
371 `SRVC_UNIT_QTY` varchar(10),
372 `RNDRG_PRVDR_NPI` varchar(50),
373 `PRCSG_IND_CD` varchar(5),
374 `RNDRG_FED_PRVDR_SPCLTY_CD` varchar(5),
375 `PRCNG_LCLTY_CD` varchar(5),
376 `HCFASPCL` varchar(10),
377 `MDFR_CD3` varchar(10),
378 `MDFR_CD4` varchar(10),
379 `MDFR_CD5` varchar(10),
380 `RNDRG_PRVDR_TAX_NUM` int,
381 `PRFNL_MTUS_CNT` varchar(10),
382 `MTUS_IND_CD` varchar(1),
383 `OTHR_APLD_AMT` decimal(30,2),
384 `OTHR_APLD_CD` varchar(1),
385 `ALOWD_NET_PAY` decimal(30,2),
386 `HEMOFAC_B` varchar(1),
387 `PLCSRVC_IND` varchar(1),
388 `DRG_EXCL_IND` tinyint default 0,
389 `clm_assigmnt` varchar(10),
390 `PAC_CD` varchar(10),
391 `OFFICE_VISIT` varchar(1),
392 `LALOWCHRG` decimal(30,2),
393 `ALLOWED_OPD_PAY_EPI` decimal(30,2),
394 `ALLOWED_OPD_PAY_POSTEPI` decimal(30,2),
395 `STD_AMT_EPI` decimal(30,2),
396 `STD_ALOWD_AMT` varchar(25),
397 `ALLOWED_AMT_EPI` varchar(25),
398 `EXCLUSION_CODE` varchar(10),
399 `SERVICE_TYPE` varchar(50),
400 `RVU_STATUS` varchar(10),
401 `STD_AMT_HCI3_PRORATED` varchar(25) default 0,
402 `STD_AMT_HCI3` varchar(25) default 0,
403 `NON_STD_SUB` varchar(10),
404 `REVSTIND` varchar(5),
405 `PRORATED` tinyint(1) default 0,
406 `W_OUTLIER_ALOWD_AMT` decimal(30,2),
407 `COSTINC` varchar(1),
408 `CVRD_PD_AMT` decimal(30,2),
409 `CLM_GROUP` varchar(10),
410
411 `POST_DSCH_WIN` varchar(10),
412
413 -- merged from bpcia_claims
414 `ED_IND` tinyint (1) default null,
415 `provider` varchar(25),
416 `BENE_SK` int,
417 `curhic_uneq` varchar(20),
418 `claimno` varchar(5),
419 `PMT_AMT` decimal(30,2),
420 `FROM_DT` date,
421 `THRU_DT` date,
422 `PRPAYAMT` decimal(30,2),
423 `AT_NPI` varchar(10),
424 `OP_NPI` varchar(10),
425 `drg_cd` varchar(10),
426 `STUS_CD` varchar(2),
427 `IPPS_IND` tinyint (1) default 0,
428 `MSDRG_IND` tinyint(1) default 0,
429 `OVLAP_IND` tinyint(1) default 0,
430 `INTSTAY_IND` tinyint(1) default 0,
431 `STAY_ID` tinyint(1) default 0,
432 `READMIS_IND` tinyint(1) default 0,
433 `XFER_IND` tinyint(1) default 0,
434 `ANCHOR_IND` tinyint (1) default 0,
435 `Rehab_IND` tinyint (1) default 0,
436 `LTCH_IND` tinyint (1) default 0,
437 `IPF_IND` tinyint (1) default 0,
438 `del_ind` tinyint(1) default 0,
439
440 -- 2017 changes
441 `BPCI_PROV` varchar(2),
442 `BPCI_PGP_AT` varchar(2),
443 `BPCI_PGP_OP` varchar(2),
444 `BPCI_PAC` varchar(2),
445 CLM_DEMO_1ST_NUM varchar(10),
446CLM_DEMO_2ND_NUM varchar(10),
447CLM_DEMO_3RD_NUM varchar(10),
448CLM_DEMO_4TH_NUM varchar(10),
449CLM_DEMO_5TH_NUM varchar(10),
450BILL_TYPE varchar(25),
451 PDGNS_CD varchar(10),
452
453 ANCHOR_PERIOD tinyint(1),
454 -- BPCIA Additions
455 `ADMSN_DT` date,
456 `DSCHRGDT` date,
457 `STD_ALLOWED` decimal(30,2),
458 `LINEPMT` decimal(30,2),
459 `LINE_ALLOWED` decimal(30,2),
460 `CLM_ALLOWED` decimal(30,2),
461 `LINE_STD_ALLOWED` decimal(30,2),
462 `RVNCVR01-RVNCVR45` decimal(30,2),
463
464 `DGNSCD01-DGNSCD45` varchar(10),
465 `PRCDRCD01-PRCDRCD45` varchar(20),
466 `LPRPDAMT` decimal(30,2),
467 `LINEITEM` varchar(60),
468 `LUPAIND` varchar(10),
469 `PRCRRTRN` varchar(10),
470 `RSTUSIND` varchar(10),
471 `SGMT_NUM` varchar(30),
472 `TAX_NUM` varchar(30),
473 `PRFNPI` varchar(30),
474 `PLCSRVC` varchar(2),
475
476 `DEMONUM1-DEMONUM5` varchar(100),
477 FACTYPE varchar(100),
478 TYPESRVC varchar(100),
479 `IP_STAY_ID` varchar(20),
480
481 `EXPNSDT1` date,
482 `EXPNSDT2` date,
483
484 -- from EPI
485 `BENE_AGE` tinyint default 0,
486 `ANCHOR_TYPE` varchar(40),
487 `DRG_2018` varchar(40) default null,
488 `ANCHOR_APC` varchar(40),
489 `ANCHOR_APC_PMT_RATE` varchar(40),
490 `ANCHOR_C_APC_FLAG` varchar(40),
491 `PERF_APC` varchar(40),
492 `PERF_APC_PMT_RATE` varchar(40),
493 `PERF_C_APC_FLAG` varchar(40),
494 `COMP_ADJ_APC_FLAG` varchar(40),
495 `ANCHOR_AT_NPI` varchar(40),
496 `ANCHOR_OP_NPI` varchar(40),
497 `ANCHOR_STANDARD_ALLOWED_AMT` varchar(40),
498 `ANCHOR_ALLOWED_AMT` varchar(40),
499 `TRANSFER_STAY` varchar(40),
500 `TOT_STD_ALLOWED` decimal(30,10),
501 `TOT_RAW_ALLOWED` decimal(30,10),
502 `TOT_STD_ALLOWED_OPL` decimal(30,10),
503 `TOT_STD_ALLOWED_IP` decimal(30,10),
504 `TOT_STD_ALLOWED_DM` decimal(30,10),
505 `TOT_STD_ALLOWED_PB` decimal(30,10),
506 `TOT_STD_ALLOWED_SN` decimal(30,10),
507 `TOT_STD_ALLOWED_HS` decimal(30,10),
508 `TOT_STD_ALLOWED_HH_NONRAP` decimal(30,10),
509 `EPI_STD_PMT_FCTR` decimal(30,10),
510 `WINSORIZE_1_99` tinyint,
511 `EPI_STD_PMT_FCTR_WIN_1_99` decimal(30,10)
512
513) ;
514
515
516ALTER TABLE `bpci_claim_detail` ADD INDEX `clmdtl_idx1` (`FILE_TYPE`,`EPI_ID`,`clm_type_cd`,`MASTER_CLM_ID`,`REV_DT` );
517ALTER TABLE `bpci_claim_detail` ADD INDEX `clmdtl_idx2` (`FILE_TYPE`,`EPI_ID`,`clm_type_cd`,`MASTER_CLM_ID`,`line_num`);
518ALTER TABLE `bpci_claim_detail` ADD INDEX `clmdtl_idx3` (`FILE_TYPE`,`MASTER_CLM_ID`);
519
520
521/*claim dxpx*/
522DROP TABLE IF EXISTS `bpci_claim_dxpx`;
523CREATE TABLE `bpci_claim_dxpx` (
524 uid INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
525 `MASTER_CLM_ID` varchar(100) NOT NULL,
526 `CLM_TYPE_CD` varchar(5),
527 `PROD_TYPE_CD` varchar(1),
528 `VAL_SQNC_NUM` varchar(1),
529 `CODE_VALUE` varchar(10),
530 `PROD_VRSN_CD` varchar(10),
531 `DGNS_PRCDR_ICD_IND` varchar(1),
532 `POA_IND` varchar(1),
533 `PRCDR_PRFRM_DT` date,
534 `FILE_TYPE` varchar(10)
535) ;
536
537ALTER TABLE `bpci_claim_dxpx` ADD INDEX `dxpx_idx1` (`FILE_TYPE`,`CLM_TYPE_CD`, `MASTER_CLM_ID`,`PROD_TYPE_CD`);
538
539
540
541/*bene start
542DROP TABLE IF EXISTS `bpci_bene_info`;
543CREATE TABLE `bpci_bene_info` (
544 uid INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
545 `BENE_SK` varchar(25),
546 `BENE_HIC_NO` varchar(15),
547 `MDCR_STUS_CD1` varchar(3),
548 `MDCR_STUS_CD2` varchar(3),
549 `MDCR_STUS_CD3` varchar(3),
550 `MDCR_STUS_CD4` varchar(3),
551 `MDCR_STUS_CD5` varchar(3),
552 `MDCR_STUS_CD6` varchar(3),
553 `MDCR_STUS_CD7` varchar(3),
554 `MDCR_STUS_CD8` varchar(3),
555 `MDCR_STUS_CD9` varchar(3),
556 `MDCR_STUS_CD10` varchar(3),
557 `MDCR_STUS_CD11` varchar(3),
558 `MDCR_STUS_CD12` varchar(3),
559 `BUYIN_IND1` varchar(3),
560 `BUYIN_IND2` varchar(3),
561 `BUYIN_IND3` varchar(3),
562 `BUYIN_IND4` varchar(3),
563 `BUYIN_IND5` varchar(3),
564 `BUYIN_IND6` varchar(3),
565 `BUYIN_IND7` varchar(3),
566 `BUYIN_IND8` varchar(3),
567 `BUYIN_IND9` varchar(3),
568 `BUYIN_IND10` varchar(3),
569 `BUYIN_IND11` varchar(3),
570 `BUYIN_IND12` varchar(3),
571 `HMO_CVRG_IND1` varchar(3),
572 `HMO_CVRG_IND2` varchar(3),
573 `HMO_CVRG_IND3` varchar(3),
574 `HMO_CVRG_IND4` varchar(3),
575 `HMO_CVRG_IND5` varchar(3),
576 `HMO_CVRG_IND6` varchar(3),
577 `HMO_CVRG_IND7` varchar(3),
578 `HMO_CVRG_IND8` varchar(3),
579 `HMO_CVRG_IND9` varchar(3),
580 `HMO_CVRG_IND10` varchar(3),
581 `HMO_CVRG_IND11` varchar(3),
582 `HMO_CVRG_IND12` varchar(3),
583 `DOD_VLDTD_SW` varchar(1),
584 `DOD` date,
585 `DNMTR_FIL_RFRNC_YR` int,
586 `RACE_CD` varchar(1),
587 `AGE` int,
588 `SEX_CD` varchar(1),
589 `STATE_CD` int,
590 `CNTY_CD` int,
591 `ZIP` varchar(9),
592 `DUAL_ELGBL_IND` varchar(1),
593 `OREC_CD` varchar(1),
594 `CREC_CD` varchar(1),
595 `BENE_DOB` date,
596 `LAST_NAME` varchar(50),
597 `FIRST_NAME` varchar(50),
598 `MIDDLE_NAME` varchar(30),
599 `FILE_TYPE` varchar(10)
600) ;
601ALTER TABLE `bpci_bene_info` ADD INDEX `bene_idx` (`FILE_TYPE`,`BENE_SK`);
602*/
603/*bene dual
604DROP TABLE IF EXISTS `bpci_bene_dual`;
605CREATE TABLE `bpci_bene_dual`
606(
607 `BENE_SK` varchar(25),
608 `CLNDR_MO_ELGBL` varchar(6),
609 `DUAL_STUS_CD` varchar(5),
610 `CLNDR_MO_FIL` varchar(6),
611 `DUAL_MDCD` varchar(5),
612 `DUAL_INST` varchar(5),
613 `FILE_TYPE` varchar(10)
614) ;
615*/
616
617/**********************************************************************/
618/* run & output */
619/**********************************************************************/
620/*run log*/
621DROP TABLE IF EXISTS `bpci_run_log`;
622CREATE TABLE `bpci_run_log`
623(
624 uid INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
625 `run_step` varchar(100),
626 `run_msg` varchar(255)
627) ;
628
629/*claim output'0*/
630DROP TABLE IF EXISTS `bpci_claims_output`;
631CREATE TABLE `bpci_claims_output`
632(
633 uid INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
634 `FILE_TYPE` varchar(50),
635 `EPI_ID` varchar(35) NOT NULL,
636 `CONFLICTING_EPISODE` tinyint(1),
637 `BENE_SK` varchar(25),
638 `ANCHOR_anchor_trigger_cd` int,
639 `episode_group_name` varchar(255),
640 `FRACTURE` tinyint(4),
641 `ANCHOR_CCN` varchar(25),
642 `ANCHOR_BEG_DT` date,
643 `ANCHOR_END_DT` date,
644 `POST_DSCH_BEG_DT` date,
645 `POST_DSCH_END_DT` date,
646 `PERF_YEAR` varchar(4),
647 `PERF_QTR` varchar(6),
648 `CLAIM_ID` varchar(100) NOT NULL,
649 `sgmt_num` int,
650 `anchor_provider` varchar(25),
651 `AT_NPI` varchar(20),
652 `OP_NPI` varchar(20),
653 `PRFNPI` varchar(20),
654 `NPI` varchar(20),
655 `ANCHOR_NPI` varchar(20),
656 `ALLOWED_AMT` decimal(30,2),
657 `STANDARDIZED_PAYMENT` decimal(30,2),
658 `CLM_FROM_DT` date,
659 `CLM_THRU_DT` date,
660 `CLAIM_TYPE` varchar(5),
661 `SERVICE_TYPE` varchar(50),
662 `anchor_trigger_cd` varchar(3),
663 `PDGNS_CD` varchar(10),
664 `DGNS_CD` varchar(10),
665 `OFFICE_VISIT` tinyint(1),
666 `EPI_TRIGGER` tinyint(1),
667 `READMISSION` tinyint(1),
668 `IP_REHAB` tinyint(1),
669 `LTCH` tinyint(1),
670 `ED` tinyint(1),
671 `CLM_GROUP` varchar(10),
672 `POST_DSCH_WINDOW` varchar(10),
673 `PRORATED` tinyint(1),
674 `CLAIM_ASSIGNMENT` varchar(10),
675 `PAC_CD` varchar(10),
676 `HCPCS_CD` varchar(10),
677 `MDFR_CD1` varchar(10),
678 `MDFR_CD2` varchar(10),
679 `PLCSRVC` varchar(2),
680 `BETOS` varchar(5),
681 `HCPCS_CD1` varchar(10),
682 `HCPCS_CD2` varchar(10),
683 `HCPCS_CD3` varchar(10),
684 `HCPCS_CD4` varchar(10),
685 `HCPCS_CD5` varchar(10),
686 `HCPCS_CD6` varchar(10),
687 `HCPCS_CD7` varchar(10),
688 `HCPCS_CD8` varchar(10),
689 `HCPCS_CD9` varchar(10),
690 `HCPCS_CD10` varchar(10),
691 `HCPCS_CD11` varchar(10),
692 `HCPCS_CD12` varchar(10),
693 `HCPCS_CD13` varchar(10),
694 `HCPCS_CD14` varchar(10),
695 `HCPCS_CD15` varchar(10),
696 `HCPCS_CD16` varchar(10),
697 `HCPCS_CD17` varchar(10),
698 `HCPCS_CD18` varchar(10),
699 `HCPCS_CD19` varchar(10),
700 `HCPCS_CD20` varchar(10),
701 `HCPCS_CD21` varchar(10),
702 `HCPCS_CD22` varchar(10),
703 `HCPCS_CD23` varchar(10),
704 `HCPCS_CD24` varchar(10),
705 `HCPCS_CD25` varchar(10),
706 `HCPCS_CD26` varchar(10),
707 `HCPCS_CD27` varchar(10),
708 `HCPCS_CD28` varchar(10),
709 `HCPCS_CD29` varchar(10),
710 `HCPCS_CD30` varchar(10),
711 `ADMTG_DGNS_CD` varchar(15),
712 `CLM_MDCL_REC` varchar(50),
713 `PRVDR_NAME` varchar(100),
714 `ADMSN_DT` date,
715 `DSCHRGDT` date,
716 `curhic_uneq` varchar(30),
717 `TYPE_ADM` varchar(5),
718 `SRC_ADMS` varchar(5),
719 `CLM_PTNT_CNTL` varchar(50),
720 `STUS_CD` varchar(5),
721 `REV_CNTR1` varchar(5),
722 `REV_CNTR2` varchar(5),
723 `REV_CNTR3` varchar(5),
724 `REV_CNTR4` varchar(5),
725 `REV_CNTR5` varchar(5),
726 `REV_CNTR6` varchar(5),
727 `REV_CNTR7` varchar(5),
728 `REV_CNTR8` varchar(5),
729 `REV_CNTR9` varchar(5),
730 `REV_CNTR10` varchar(5),
731 `REV_CNTR11` varchar(5),
732 `REV_CNTR12` varchar(5),
733 `REV_CNTR13` varchar(5),
734 `REV_CNTR14` varchar(5),
735 `REV_CNTR15` varchar(5),
736 `REV_CNTR16` varchar(5),
737 `REV_CNTR17` varchar(5),
738 `REV_CNTR18` varchar(5),
739 `REV_CNTR19` varchar(5),
740 `REV_CNTR20` varchar(5),
741 `REV_CNTR21` varchar(5),
742 `REV_CNTR22` varchar(5),
743 `REV_CNTR23` varchar(5),
744 `REV_CNTR24` varchar(5),
745 `REV_CNTR25` varchar(5),
746 `REV_CNTR26` varchar(5),
747 `REV_CNTR27` varchar(5),
748 `REV_CNTR28` varchar(5),
749 `REV_CNTR29` varchar(5),
750 `REV_CNTR30` varchar(5),
751 `LINKED_PBCOSTS` varchar(15),
752 `MDFR_CD11` varchar(10),
753 `MDFR_CD12` varchar(10),
754 `MDFR_CD13` varchar(10),
755 `MDFR_CD14` varchar(10),
756 `MDFR_CD15` varchar(10),
757 `MDFR_CD21` varchar(10),
758 `MDFR_CD22` varchar(10),
759 `MDFR_CD23` varchar(10),
760 `MDFR_CD24` varchar(10),
761 `MDFR_CD25` varchar(10),
762 `ICD910` varchar(5),
763 `VISITCNT` varchar(3),
764 `ORGNPINM` varchar(20),
765 `HCFASPCL` varchar(5),
766 `CLM_CARR_PMT_DNL_CD` varchar(5),
767 `FREQ_CD` varchar(5),
768 `EPI_COMPLETION_FLAG` tinyint(4)
769) ;
770
771alter table bpci_claims_output ADD INDEX `clm_output_idx1` ( `FILE_TYPE`,`EPI_ID`,`CLAIM_ID`,`sgmt_num`);
772alter table bpci_claims_output ADD INDEX `clm_output_idx2` ( `FILE_TYPE`,`EPI_ID`,`CLAIM_TYPE`,`CLM_FROM_DT`);
773
774DROP TABLE IF EXISTS `bpci_epi_detail`;
775CREATE TABLE `bpci_epi_detail`
776(
777 uid INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
778 `FILE_TYPE` varchar(50),
779 `EPI_ID` varchar(35) NOT NULL,
780 `CONFLICTING_EPISODE` tinyint(1),
781 `BENE_SK` varchar(25),
782 `ANCHOR_anchor_trigger_cd` int,
783 `episode_group_name` varchar(255),
784 `FRACTURE` tinyint(4),
785 `ANCHOR_CCN` varchar(25),
786 `ANCHOR_BEG_DT` date,
787 `ANCHOR_END_DT` date,
788 `POST_DSCH_BEG_DT` date,
789 `POST_DSCH_END_DT` date,
790 `TOTAL_COST` decimal(30,2),
791 `ACUTE_TOTAL` decimal(30,2),
792 `ACUTE_ALLOWED_IP_NET_PAY` decimal(30,2),
793 `ACUTE_ALLOWED_PB_NET_PAY` decimal(30,2),
794 `POST_ACUTE_TOTAL` decimal(30,2),
795 `POST_ACUTE_ALLOWED_IP_NET_PAY` decimal(30,2),
796 `POST_ACUTE_ALLOWED_PB_NET_PAY` decimal(30,2),
797 `POST_ACUTE_ALLOWED_DM_NET_PAY` decimal(30,2),
798 `POST_ACUTE_ALLOWED_OP_NET_PAY` decimal(30,2),
799 `POST_ACUTE_ALLOWED_SN_NET_PAY` decimal(30,2),
800 `POST_ACUTE_ALLOWED_HH_NET_PAY` decimal(30,2),
801 `POST_ACUTE_ALLOWED_HS_NET_PAY` decimal(30,2),
802 `LOS` int,
803 `POST_IP_REHAB_PAY` decimal(30,2),
804 `POST_IP_LTCH_PAY` decimal(30,2),
805 `COMPL_ACUTE_PB` decimal(30,2),
806 `COMPL_POST_ACUTE_IP` decimal(30,2),
807 `COMPL_POST_ACUTE_OP` decimal(30,2),
808 `COMPL_POST_ACUTE_PB` decimal(30,2),
809 `COMPL_POST_ACUTE_DME` decimal(30,2),
810 `COMPL_POST_ACUTE_SNF_FLAG` tinyint,
811 `COMPL_POST_ACUTE_HH_FLAG` tinyint,
812 `COMPL_POST_ACUTE_HS_FLAG` tinyint,
813 `COMPL_POST_ACUTE_IP_REHAB_FLAG` tinyint,
814 `COMPL_POST_ACUTE_LTCH_FLAG` tinyint,
815 `COMPL_ACUTE_FLAG` tinyint,
816 `COMPL_POST_ACUTE_TOTAL` decimal(30,2),
817 `DOD` varchar(30),
818 `RACE` tinyint,
819 `AGE` tinyint,
820 `SEX` tinyint,
821 `STATE_CD` tinyint,
822 `COUNTY_CD` int(11),
823 `ZIP_CD` varchar(10),
824 `OREC` tinyint,
825 `CREC` tinyint,
826 `BENE_DOB` varchar(30),
827 `BENE_LAST_NAME` varchar(50),
828 `BENE_FIRST_NAME` varchar(50),
829 `BENE_MIDDLE_NAME` varchar(50),
830 `DUAL_ELIG` varchar(2),
831 `PERF_YEAR` varchar(4),
832 `PERF_QTR` varchar(6),
833 `WAGEFACTOR` varchar(20),
834 `GROWTHFACTOR` varchar(20),
835 `FUOV` tinyint,
836 `TARGET_PRICE` varchar(30),
837 `EPI_COMPLETION_FLAG` varchar(1),
838 `PCT_COMPLETION` varchar(30)
839) ;
840ALTER TABLE `bpci_epi_detail` ADD INDEX `bpci_epidtl_idx1` ( `FILE_TYPE`,`EPI_ID`);
841ALTER TABLE `bpci_epi_detail` ADD INDEX `bpci_epidtl_idx2` (`FILE_TYPE`,`perf_qtr`,`anchor_anchor_trigger_cd`);
842
843/*
844DROP TABLE IF EXISTS `bpci_bene_info`;
845CREATE TABLE `bpci_bene_info` (
846 uid INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
847 `BENE_SK` varchar(25),
848 `BENE_HIC_NO` varchar(15),
849 `MDCR_STUS_CD1` varchar(3),
850 `MDCR_STUS_CD2` varchar(3),
851 `MDCR_STUS_CD3` varchar(3),
852 `MDCR_STUS_CD4` varchar(3),
853 `MDCR_STUS_CD5` varchar(3),
854 `MDCR_STUS_CD6` varchar(3),
855 `MDCR_STUS_CD7` varchar(3),
856 `MDCR_STUS_CD8` varchar(3),
857 `MDCR_STUS_CD9` varchar(3),
858 `MDCR_STUS_CD10` varchar(3),
859 `MDCR_STUS_CD11` varchar(3),
860 `MDCR_STUS_CD12` varchar(3),
861 `BUYIN_IND1` varchar(3),
862 `BUYIN_IND2` varchar(3),
863 `BUYIN_IND3` varchar(3),
864 `BUYIN_IND4` varchar(3),
865 `BUYIN_IND5` varchar(3),
866 `BUYIN_IND6` varchar(3),
867 `BUYIN_IND7` varchar(3),
868 `BUYIN_IND8` varchar(3),
869 `BUYIN_IND9` varchar(3),
870 `BUYIN_IND10` varchar(3),
871 `BUYIN_IND11` varchar(3),
872 `BUYIN_IND12` varchar(3),
873 `HMO_CVRG_IND1` varchar(3),
874 `HMO_CVRG_IND2` varchar(3),
875 `HMO_CVRG_IND3` varchar(3),
876 `HMO_CVRG_IND4` varchar(3),
877 `HMO_CVRG_IND5` varchar(3),
878 `HMO_CVRG_IND6` varchar(3),
879 `HMO_CVRG_IND7` varchar(3),
880 `HMO_CVRG_IND8` varchar(3),
881 `HMO_CVRG_IND9` varchar(3),
882 `HMO_CVRG_IND10` varchar(3),
883 `HMO_CVRG_IND11` varchar(3),
884 `HMO_CVRG_IND12` varchar(3),
885 `DOD_VLDTD_SW` varchar(1),
886 `DOD` date,
887 `DNMTR_FIL_RFRNC_YR` int,
888 `RACE_CD` varchar(1),
889 `AGE` int,
890 `SEX_CD` varchar(1),
891 `STATE_CD` int,
892 `CNTY_CD` int,
893 `ZIP` varchar(9),
894 `DUAL_ELGBL_IND` varchar(1),
895 `OREC_CD` varchar(1),
896 `CREC_CD` varchar(1),
897 `BENE_DOB` date,
898 `LAST_NAME` varchar(50),
899 `FIRST_NAME` varchar(50),
900 `MIDDLE_NAME` varchar(30),
901 `FILE_TYPE` varchar(10)
902) ;
903ALTER TABLE `bpci_bene_info` ADD INDEX `bpci_bene_idx` (`FILE_TYPE`,`BENE_SK`);
904
905DROP TABLE IF EXISTS `bpci_bene_dual`;
906CREATE TABLE `bpci_bene_dual`
907(
908 `BENE_SK` varchar(25),
909 `CLNDR_MO_ELGBL` varchar(6),
910 `DUAL_STUS_CD` varchar(5),
911 `CLNDR_MO_FIL` varchar(6),
912 `DUAL_MDCD` varchar(5),
913 `DUAL_INST` varchar(5),
914 `FILE_TYPE` varchar(10)
915) ;*/
916
917DROP TABLE IF EXISTS `bpci_epi_npra`;
918CREATE TABLE `bpci_epi_npra`
919(
920 `FILE_TYPE` varchar(25) NOT NULL,
921 `FRACTURE` tinyint,
922 `ANCHOR_anchor_trigger_cd` int,
923 `EPI_VOLUME` int,
924 `TARGET_PRICE` decimal(30,2),
925 `TOTAL_TARGET_AMT` decimal(30,2),
926 `PERF_EPI_TOTAL` decimal(30,2),
927 `NPRA` decimal(30,2),
928 `PERF_EPI_TOTAL_ST` decimal(30,2),
929 `NPRA_ST` decimal(30,2)
930) ;
931ALTER TABLE `bpci_epi_npra` ADD INDEX `bpci_npra_idx` (`FILE_TYPE`,`ANCHOR_anchor_trigger_cd`,`FRACTURE` );
932
933
934/**********************************************************************/
935/* run & output */
936/**********************************************************************/
937/*run log*/
938DROP TABLE IF EXISTS `bpci_run_log`;
939CREATE TABLE `bpci_run_log`
940(
941 uid INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
942 `run_step` varchar(100),
943 `run_msg` varchar(255)
944) ;
945
946/*claim output*/
947DROP TABLE IF EXISTS `bpci_claims_output_all`;
948CREATE TABLE `bpci_claims_output_all`
949(
950 uid INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
951 `FILE_TYPE` varchar(25),
952 `EPI_ID` varchar(35) NOT NULL,
953 `CONFLICTING_EPISODE` tinyint(1),
954 `BENE_SK` int,
955 `ANCHOR_anchor_trigger_cd` varchar(10),
956 `episode_group_name` varchar(500),
957 `MODEL` varchar(1),
958 `ANCHOR_CCN` varchar(20),
959 `EPI_LEN` tinyint(1),
960 `EPI_LEN_CMS` tinyint(1),
961 `ANCHOR_BEG_DT` date,
962 `ANCHOR_END_DT` date,
963 `POST_DSCH_BEG_DT` date,
964 `POST_DSCH_END_DT` date,
965 `PERF_YEAR` varchar(4),
966 `PERF_QTR` varchar(6),
967 `CLAIM_ID` varchar(100) NOT NULL,
968 `sgmt_num` int,
969 `anchor_provider` varchar(20),
970 `AT_NPI` varchar(20),
971 `OP_NPI` varchar(20),
972 `PRFNPI` varchar(20),
973 `NPI` varchar(20),
974 `ANCHOR_NPI` varchar(20),
975 `ALLOWED_AMT` decimal(30,2),
976 `CLM_FROM_DT` date,
977 `CLM_THRU_DT` date,
978 `CLAIM_TYPE` varchar(5),
979 `SERVICE_TYPE` varchar(50),
980 `anchor_trigger_cd` varchar(10),
981 `PDGNS_CD` varchar(10),
982 `CLM_PROD_VRSN_CD` varchar(2),
983 `DGNS_CD` varchar(10),
984 `OFFICE_VISIT` tinyint(1),
985 `EPI_TRIGGER` tinyint(1),
986 `READMISSION` tinyint(1),
987 `IP_REHAB` tinyint(1),
988 `LTCH` tinyint(1),
989 `ED` tinyint(1),
990 `CLM_GROUP` varchar(10),
991 `POST_DSCH_WINDOW` varchar(10),
992 `PRORATED` tinyint(1),
993 `CLAIM_ASSIGNMENT` varchar(10),
994 `PAC_CD` varchar(10),
995 `HCPCS_CD` varchar(5),
996 `MDFR_CD1` varchar(10),
997 `MDFR_CD2` varchar(10),
998 `PLCSRVC` varchar(2),
999 `BETOS` varchar(5),
1000 `HCPCS_CD1` varchar(5),
1001 `HCPCS_CD2` varchar(5),
1002 `HCPCS_CD3` varchar(5),
1003 `HCPCS_CD4` varchar(5),
1004 `HCPCS_CD5` varchar(5),
1005 `HCPCS_CD6` varchar(5),
1006 `HCPCS_CD7` varchar(5),
1007 `HCPCS_CD8` varchar(5),
1008 `HCPCS_CD9` varchar(5),
1009 `HCPCS_CD10` varchar(5),
1010 `HCPCS_CD11` varchar(5),
1011 `HCPCS_CD12` varchar(5),
1012 `HCPCS_CD13` varchar(5),
1013 `HCPCS_CD14` varchar(5),
1014 `HCPCS_CD15` varchar(5),
1015 `HCPCS_CD16` varchar(5),
1016 `HCPCS_CD17` varchar(5),
1017 `HCPCS_CD18` varchar(5),
1018 `HCPCS_CD19` varchar(5),
1019 `HCPCS_CD20` varchar(5),
1020 `HCPCS_CD21` varchar(5),
1021 `HCPCS_CD22` varchar(5),
1022 `HCPCS_CD23` varchar(5),
1023 `HCPCS_CD24` varchar(5),
1024 `HCPCS_CD25` varchar(5),
1025 `HCPCS_CD26` varchar(5),
1026 `HCPCS_CD27` varchar(5),
1027 `HCPCS_CD28` varchar(5),
1028 `HCPCS_CD29` varchar(5),
1029 `HCPCS_CD30` varchar(5),
1030 `ADMTG_DGNS_CD` varchar(10),
1031 `CLM_MDCL_REC` varchar(50),
1032 `PRVDR_NAME` varchar(100),
1033 `ADMSN_DT` date,
1034 `DSCHRGDT` date,
1035 `curhic_uneq` varchar(30),
1036 `TYPE_ADM` varchar(5),
1037 `SRC_ADMS` varchar(5),
1038 `CLM_PTNT_CNTL` varchar(50),
1039 `CLM_ADMSN_HR` varchar(5),
1040 `CLM_DSCHRG_HR` varchar(5),
1041 `STUS_CD` varchar(5),
1042 `BPID` varchar(10),
1043 `REV_CNTR1` varchar(5),
1044 `REV_CNTR2` varchar(5),
1045 `REV_CNTR3` varchar(5),
1046 `REV_CNTR4` varchar(5),
1047 `REV_CNTR5` varchar(5),
1048 `REV_CNTR6` varchar(5),
1049 `REV_CNTR7` varchar(5),
1050 `REV_CNTR8` varchar(5),
1051 `REV_CNTR9` varchar(5),
1052 `REV_CNTR10` varchar(5),
1053 `REV_CNTR11` varchar(5),
1054 `REV_CNTR12` varchar(5),
1055 `REV_CNTR13` varchar(5),
1056 `REV_CNTR14` varchar(5),
1057 `REV_CNTR15` varchar(5),
1058 `REV_CNTR16` varchar(5),
1059 `REV_CNTR17` varchar(5),
1060 `REV_CNTR18` varchar(5),
1061 `REV_CNTR19` varchar(5),
1062 `REV_CNTR20` varchar(5),
1063 `REV_CNTR21` varchar(5),
1064 `REV_CNTR22` varchar(5),
1065 `REV_CNTR23` varchar(5),
1066 `REV_CNTR24` varchar(5),
1067 `REV_CNTR25` varchar(5),
1068 `REV_CNTR26` varchar(5),
1069 `REV_CNTR27` varchar(5),
1070 `REV_CNTR28` varchar(5),
1071 `REV_CNTR29` varchar(5),
1072 `REV_CNTR30` varchar(5),
1073 `LINKED_PBCOSTS` decimal(30,10),
1074 `CLM_PD_DT` date,
1075 `CLM_DEMO_1ST_NO` varchar(10),
1076 `CLM_DEMO_2ND_NO` varchar(10),
1077 `CLM_DEMO_3RD_NO` varchar(10),
1078 `CLM_DEMO_4TH_NO` varchar(10),
1079 `CLM_DEMO_5TH_NO` varchar(10),
1080 `MDFR_CD11` varchar(10),
1081 `MDFR_CD12` varchar(10),
1082 `MDFR_CD13` varchar(10),
1083 `MDFR_CD14` varchar(10),
1084 `MDFR_CD15` varchar(10),
1085 `MDFR_CD21` varchar(10),
1086 `MDFR_CD22` varchar(10),
1087 `MDFR_CD23` varchar(10),
1088 `MDFR_CD24` varchar(10),
1089 `MDFR_CD25` varchar(10),
1090 `ICD910` varchar(5),
1091 `VISITCNT` varchar(3),
1092 `ORGNPINM` varchar(20),
1093 `HCFASPCL` varchar(5),
1094 `CLM_CARR_PMT_DNL_CD` varchar(5),
1095 `FREQ_CD` varchar(5),
1096 `EPI_ALLOWED_PAY` decimal(30,10),
1097 `EPINUM` varchar(5),
1098 `EPI_COMPLETION_FLAG` tinyint(4)
1099) ;
1100
1101alter table bpci_claims_output_all ADD INDEX `clm_output_idx1` ( `FILE_TYPE`,`EPI_ID`,`EPI_LEN`,`CLAIM_ID`,`sgmt_num`);
1102
1103alter table bpci_claims_output_all ADD INDEX `clm_output_idx2` ( `FILE_TYPE`,`EPI_ID`,`EPI_LEN`,`CLAIM_TYPE`,`CLM_FROM_DT`);
1104
1105
1106DROP TABLE IF EXISTS `bpci_epi_detail_all`;
1107CREATE TABLE `bpci_epi_detail_all`
1108(
1109 uid INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
1110 `FILE_TYPE` varchar(25),
1111 `EPI_ID` varchar(35) NOT NULL,
1112 `CONFLICTING_EPISODE` tinyint(1),
1113 `BENE_SK` int,
1114 `ANCHOR_anchor_trigger_cd` varchar(10),
1115 `episode_group_name` varchar(500),
1116 `MODEL` varchar(1),
1117 `ANCHOR_CCN` varchar(20),
1118 `EPI_LEN` tinyint(1),
1119 `EPI_LEN_CMS` tinyint(1),
1120 `ANCHOR_BEG_DT` date,
1121 `ANCHOR_END_DT` date,
1122 `POST_DSCH_BEG_DT` date,
1123 `POST_DSCH_END_DT` date,
1124 `TOTAL_COST` decimal(30,2),
1125 `ACUTE_TOTAL` decimal(30,2),
1126 `ACUTE_ALLOWED_IP_NET_PAY` decimal(30,2),
1127 `ACUTE_ALLOWED_PB_NET_PAY` decimal(30,2),
1128 `POST_ACUTE_TOTAL` decimal(30,2),
1129 `POST_ACUTE_ALLOWED_IP_NET_PAY` decimal(30,2),
1130 `POST_ACUTE_ALLOWED_PB_NET_PAY` decimal(30,2),
1131 `POST_ACUTE_ALLOWED_DM_NET_PAY` decimal(30,2),
1132 `POST_ACUTE_ALLOWED_OP_NET_PAY` decimal(30,2),
1133 `POST_ACUTE_ALLOWED_SN_NET_PAY` decimal(30,2),
1134 `POST_ACUTE_ALLOWED_HH_NET_PAY` decimal(30,2),
1135 `LOS` int,
1136 `POST_IP_REHAB_PAY` decimal(30,2),
1137 `POST_IP_LTCH_PAY` decimal(30,2),
1138 `TAVR` tinyint,
1139 `PRFTIN` int,
1140 `BPID` varchar(30),
1141 `EPI_ALLOWED_PAY` decimal(30,10),
1142 `EPINUM` int,
1143 `COMPL_ACUTE_PB` decimal(30,2),
1144 `COMPL_POST_ACUTE_IP` decimal(30,2),
1145 `COMPL_POST_ACUTE_OP` decimal(30,2),
1146 `COMPL_POST_ACUTE_PB` decimal(30,2),
1147 `COMPL_POST_ACUTE_DME` decimal(30,2),
1148 `COMPL_POST_ACUTE_SNF_FLAG` tinyint,
1149 `COMPL_POST_ACUTE_HH_FLAG` tinyint,
1150 `COMPL_POST_ACUTE_IP_REHAB_FLAG` tinyint,
1151 `COMPL_POST_ACUTE_LTCH_FLAG` tinyint,
1152 `COMPL_ACUTE_FLAG` tinyint,
1153 `COMPL_POST_ACUTE_TOTAL` decimal(30,2),
1154 `DOD` varchar(30),
1155 `RACE` tinyint,
1156 `AGE` tinyint,
1157 `SEX` tinyint,
1158 `STATE_CD` tinyint,
1159 `COUNTY_CD` int(11),
1160 `ZIP_CD` varchar(10),
1161 `OREC` tinyint,
1162 `CREC` tinyint,
1163 `BENE_DOB` varchar(30),
1164 `BENE_LAST_NAME` varchar(50),
1165 `BENE_FIRST_NAME` varchar(50),
1166 `BENE_MIDDLE_NAME` varchar(50),
1167 `DUAL_ELIG` varchar(2),
1168 `ORIG_2012_TARGET_PRICE_A` varchar(30),
1169 `ORIG_2012_TARGET_PRICE_B` varchar(30),
1170 `ORIG_2012_TARGET_PRICE_C` varchar(30),
1171 `AWAPR_TRACK_A` varchar(30),
1172 `AWAPR_TRACK_B` varchar(30),
1173 `AWAPR_TRACK_C` varchar(30),
1174 `BMEANCOST_A` varchar(30),
1175 `BMEANCOST_B` varchar(30),
1176 `BMEANCOST_C` varchar(30),
1177 `BP_B4_DISCOUNT_A` varchar(30),
1178 `BP_B4_DISCOUNT_B` varchar(30),
1179 `BP_B4_DISCOUNT_C` varchar(30),
1180 `PERF_YEAR` varchar(6),
1181 `PERF_QTR` varchar(6),
1182 `WAGEFACTOR` varchar(20),
1183 `WAGEFACTOR_FY2012` varchar(20),
1184 `TRENDFACTOR` varchar(15),
1185 `FUOV` tinyint,
1186 `WINSORIZED_A` varchar(30),
1187 `WINSORIZED_B` varchar(30),
1188 `WINSORIZED_C` varchar(30),
1189 `TARGET_PRICE_A` varchar(30),
1190 `TARGET_PRICE_B` varchar(30),
1191 `TARGET_PRICE_C` varchar(30),
1192 `RECTEMPFLAG` tinyint default 0,
1193 `EPI_COMPLETION_FLAG` varchar(1),
1194 `PCT_COMPLETION` varchar(30)
1195) ;
1196ALTER TABLE `bpci_epi_detail_all` ADD INDEX `epidtl_all_idx1` ( `FILE_TYPE`,`EPI_ID`,`EPI_LEN`);
1197ALTER TABLE `bpci_epi_detail_all` ADD INDEX `epidtl_all_idx2` (`FILE_TYPE`,`perf_qtr`,`epi_len`,`anchor_anchor_trigger_cd`);
1198
1199
1200/*2017 changes */
1201alter table bpci_epi add column EPI_NCBP decimal(30,10);
1202alter table bpci_epi add column POSTEPI_NCBP decimal(30,10);
1203
1204alter table bpci_epi_excl
1205 add column `CLM_MDCL_REC` varchar(50) after curhic_uneq;
1206
1207alter table bpci_epi_excl
1208 add column `CLM_PTNT_CNTL` varchar(25) after CLM_MDCL_REC;
1209
1210
1211alter table bpci_epi_detail
1212add column BPCI_OVERLAP tinyint(1) default 0;
1213alter table bpci_claims_output
1214add column BPCI_OVERLAP tinyint(1) default 0;
1215
1216
1217
1218
1219alter table bpci_epi_detail
1220add column TARGET_PRICE_CMS varchar(30);
1221alter table bpci_epi_detail
1222add column ST_TARGET_PRICE varchar(30);
1223alter table bpci_epi_detail
1224add column HIGH_COST varchar(30);
1225
1226/*Oct,2017*/
1227ALTER TABLE bpci_epi
1228
1229add column EPI_RECON_BPCIA decimal(30,10),
1230
1231add column EPI_RECON_BPCI decimal(30,10);
1232
1233ALTER TABLE bpci_epi_excl
1234add column ANCHOR_AT_NPI varchar(25),
1235add column ANCHOR_OP_NPI varchar(25),
1236add column BPCI_PGP_AT varchar(1),
1237add column BPCI_PGP_OP varchar(1),
1238add column BPCI_SN varchar(1),
1239add column BPCI_HH varchar(1),
1240add column BPCI_IP varchar(1);
1241
1242ALTER TABLE bpci_epi
1243add column ACO_OVERLAP tinyint(1) default 0;
1244
1245ALTER TABLE bpci_epi_excl
1246add column ACO_OVERLAP tinyint(1) default 0;
1247
1248
1249DROP TABLE IF EXISTS `bpci_ncbp`;
1250CREATE TABLE `bpci_ncbp`
1251(
1252uid int NOT NULL AUTO_INCREMENT,
1253EPI_ID varchar(35),
1254BENE_SK varchar(25),
1255PROGRAM_ID varchar(10),
1256HCP_CD varchar(10),
1257HCP_ID varchar(30),
1258OFT_CD varchar(10),
1259OFT_ID varchar(30),
1260PAY_AMT varchar(30),
1261PAY_TYPE varchar(10),
1262STD_AMT_EPI decimal(30,20),
1263ALLOWED_AMT_EPI decimal(30,2),
1264COSTINC varchar(1),
1265PRORATED varchar(1),
1266EXCLUSION_CODE varchar(3),
1267PERIOD_END_DT date,
1268PERIOD_START_DT date,
1269O_PAY_DT date,
1270PAY_DT date,
1271FILE_TYPE varchar(25),
1272PRIMARY KEY (uid)
1273
1274) ;
1275ALTER TABLE `bpci_ncbp` ADD INDEX `bpci_ncbp_idx` (`EPI_ID`,`PROGRAM_ID`);
1276
1277
1278DROP TABLE IF EXISTS bpci_aco;
1279CREATE TABLE bpci_aco
1280(
1281uid int NOT NULL AUTO_INCREMENT,
1282PROGRAM_ID varchar(10),
1283TRACK varchar(3),
1284ORG_ID varchar(10),
1285BENE_SK varchar(25),
1286PERIOD_START_DT date,
1287PERIOD_END_DT date,
1288FILE_TYPE varchar(25),
1289PRIMARY KEY (uid)
1290) ;