· 9 years ago · Oct 31, 2016, 06:48 PM
1-- MySQL dump 10.13 Distrib 5.7.16, for Linux (x86_64)
2--
3-- Host: pcghealth-cluster.cluster-cfuhzafu2oyb.us-east-1.rds.amazonaws.com Database: healthdev
4-- ------------------------------------------------------
5-- Server version 5.6.10
6
7/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
8/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
9/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
10/*!40101 SET NAMES utf8 */;
11/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
12/*!40103 SET TIME_ZONE='+00:00' */;
13/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
14/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
15/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
16/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
17
18--
19-- Temporary view structure for view `BCON_RPT_OAG_NEWHIRE`
20--
21
22DROP TABLE IF EXISTS `BCON_RPT_OAG_NEWHIRE`;
23/*!50001 DROP VIEW IF EXISTS `BCON_RPT_OAG_NEWHIRE`*/;
24SET @saved_cs_client = @@character_set_client;
25SET character_set_client = utf8;
26/*!50001 CREATE VIEW `BCON_RPT_OAG_NEWHIRE` AS SELECT
27 1 AS `Employer - Employee Hire Date`,
28 1 AS `Name`,
29 1 AS `Birth Date`,
30 1 AS `SSN`,
31 1 AS `Employee Address`,
32 1 AS `Employer Name`,
33 1 AS `Employee Hire Date`,
34 1 AS `FEIN`,
35 1 AS `Employer Address`*/;
36SET character_set_client = @saved_cs_client;
37
38--
39-- Table structure for table `DPSL_STG_DTL`
40--
41
42DROP TABLE IF EXISTS `DPSL_STG_DTL`;
43/*!40101 SET @saved_cs_client = @@character_set_client */;
44/*!40101 SET character_set_client = utf8 */;
45CREATE TABLE `DPSL_STG_DTL` (
46 `TPR_AS_OF_DT` date NOT NULL,
47 `TPR_INSERT_DT` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
48 `TPR_SOURCE_CD` varchar(4) DEFAULT NULL,
49 `TPR_BATCH_RUN_ID` int(11) DEFAULT NULL,
50 `Transaction_Type` varchar(1) DEFAULT NULL,
51 `Card_Type` varchar(2) NOT NULL DEFAULT '',
52 `Card_Number` varchar(10) NOT NULL DEFAULT '',
53 `Last_Name` varchar(40) DEFAULT NULL,
54 `First_Name` varchar(40) DEFAULT NULL,
55 `Middle_Name` varchar(40) DEFAULT NULL,
56 `Suffix` varchar(5) DEFAULT NULL,
57 `Date_Of_Birth` date DEFAULT NULL,
58 `Address_Line1` varchar(32) DEFAULT NULL,
59 `Address_Line2` varchar(32) DEFAULT NULL,
60 `City` varchar(33) DEFAULT NULL,
61 `State` varchar(2) DEFAULT NULL,
62 `Zip5` varchar(5) DEFAULT NULL,
63 `Zip4` varchar(4) DEFAULT NULL,
64 `Original_Issue_Date` date DEFAULT NULL,
65 `SSN` varchar(9) DEFAULT NULL,
66 `Expiration_Date` date DEFAULT NULL,
67 `Gender` varchar(1) DEFAULT NULL,
68 `Race_Cd` varchar(1) DEFAULT NULL,
69 `Height` varchar(3) DEFAULT NULL,
70 `Mailing_Address1` varchar(32) DEFAULT NULL,
71 `Mailing_Address2` varchar(32) DEFAULT NULL,
72 `Mailing_City` varchar(33) DEFAULT NULL,
73 `Mailing_State` varchar(2) DEFAULT NULL,
74 `Mailing_Zip5` varchar(5) DEFAULT NULL,
75 `Mailing_Zip4` varchar(4) DEFAULT NULL,
76 `Issue_Date` date DEFAULT NULL,
77 `Hair_Color` varchar(3) DEFAULT NULL,
78 `Eye_Color` varchar(3) DEFAULT NULL,
79 PRIMARY KEY (`TPR_AS_OF_DT`,`Card_Type`,`Card_Number`)
80) ENGINE=InnoDB DEFAULT CHARSET=utf8;
81/*!40101 SET character_set_client = @saved_cs_client */;
82
83--
84-- Dumping data for table `DPSL_STG_DTL`
85--
86
87LOCK TABLES `DPSL_STG_DTL` WRITE;
88/*!40000 ALTER TABLE `DPSL_STG_DTL` DISABLE KEYS */;
89/*!40000 ALTER TABLE `DPSL_STG_DTL` ENABLE KEYS */;
90UNLOCK TABLES;
91
92--
93-- Table structure for table `DPSL_TPR_DTL`
94--
95
96DROP TABLE IF EXISTS `DPSL_TPR_DTL`;
97/*!40101 SET @saved_cs_client = @@character_set_client */;
98/*!40101 SET character_set_client = utf8 */;
99CREATE TABLE `DPSL_TPR_DTL` (
100 `TPR_AS_OF_DT` date NOT NULL,
101 `TPR_INSERT_DT` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
102 `TPR_SOURCE_CD` varchar(4) DEFAULT NULL,
103 `TPR_BATCH_RUN_ID` int(11) DEFAULT NULL,
104 `Transaction_Type` varchar(1) DEFAULT NULL,
105 `Card_Type` varchar(2) NOT NULL DEFAULT '',
106 `Card_Number` varchar(10) NOT NULL DEFAULT '',
107 `Last_Name` varchar(40) DEFAULT NULL,
108 `First_Name` varchar(40) DEFAULT NULL,
109 `Middle_Name` varchar(40) DEFAULT NULL,
110 `Suffix` varchar(5) DEFAULT NULL,
111 `Date_Of_Birth` date DEFAULT NULL,
112 `Address_Line1` varchar(32) DEFAULT NULL,
113 `Address_Line2` varchar(32) DEFAULT NULL,
114 `City` varchar(33) DEFAULT NULL,
115 `State` varchar(2) DEFAULT NULL,
116 `Zip5` varchar(5) DEFAULT NULL,
117 `Zip4` varchar(4) DEFAULT NULL,
118 `Original_Issue_Date` date DEFAULT NULL,
119 `SSN` varchar(9) DEFAULT NULL,
120 `Expiration_Date` date DEFAULT NULL,
121 `Gender` varchar(1) DEFAULT NULL,
122 `Race_Cd` varchar(1) DEFAULT NULL,
123 `Height` varchar(3) DEFAULT NULL,
124 `Mailing_Address1` varchar(32) DEFAULT NULL,
125 `Mailing_Address2` varchar(32) DEFAULT NULL,
126 `Mailing_City` varchar(33) DEFAULT NULL,
127 `Mailing_State` varchar(2) DEFAULT NULL,
128 `Mailing_Zip5` varchar(5) DEFAULT NULL,
129 `Mailing_Zip4` varchar(4) DEFAULT NULL,
130 `Issue_Date` date DEFAULT NULL,
131 `Hair_Color` varchar(3) DEFAULT NULL,
132 `Eye_Color` varchar(3) DEFAULT NULL,
133 PRIMARY KEY (`TPR_AS_OF_DT`,`Card_Type`,`Card_Number`)
134) ENGINE=InnoDB DEFAULT CHARSET=utf8;
135/*!40101 SET character_set_client = @saved_cs_client */;
136
137--
138-- Table structure for table `DPSL_TPR_HDR`
139--
140
141DROP TABLE IF EXISTS `DPSL_TPR_HDR`;
142/*!40101 SET @saved_cs_client = @@character_set_client */;
143/*!40101 SET character_set_client = utf8 */;
144CREATE TABLE `DPSL_TPR_HDR` (
145 `TPR_AS_OF_DT` date NOT NULL,
146 `TPR_INSERT_DT` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
147 `TPR_SOURCE_CD` varchar(4) DEFAULT NULL,
148 `TPR_BATCH_RUN_ID` int(11) DEFAULT NULL,
149 `Header_Record_Type` varchar(1) DEFAULT NULL,
150 `Transaction_Entry_Date` date DEFAULT NULL
151) ENGINE=InnoDB DEFAULT CHARSET=utf8;
152/*!40101 SET character_set_client = @saved_cs_client */;
153
154--
155-- Dumping data for table `DPSL_TPR_HDR`
156--
157
158
159--
160-- Table structure for table `DPSL_TPR_TLR`
161--
162
163DROP TABLE IF EXISTS `DPSL_TPR_TLR`;
164/*!40101 SET @saved_cs_client = @@character_set_client */;
165/*!40101 SET character_set_client = utf8 */;
166CREATE TABLE `DPSL_TPR_TLR` (
167 `TPR_AS_OF_DT` date NOT NULL,
168 `TPR_INSERT_DT` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
169 `TPR_SOURCE_CD` varchar(4) DEFAULT NULL,
170 `TPR_BATCH_RUN_ID` int(11) DEFAULT NULL,
171 `Trailer_Record_Type` varchar(1) DEFAULT NULL,
172 `Total_Records` bigint(11) DEFAULT NULL
173) ENGINE=InnoDB DEFAULT CHARSET=utf8;
174/*!40101 SET character_set_client = @saved_cs_client */;
175
176--
177-- Dumping data for table `DPSL_TPR_TLR`
178--
179
180
181--
182-- Table structure for table `ENHR_TPR_DTL`
183--
184
185DROP TABLE IF EXISTS `ENHR_TPR_DTL`;
186/*!40101 SET @saved_cs_client = @@character_set_client */;
187/*!40101 SET character_set_client = utf8 */;
188CREATE TABLE `ENHR_TPR_DTL` (
189 `TPR_AS_OF_DT` date NOT NULL,
190 `TPR_INSERT_DT` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
191 `TPR_SOURCE_CD` varchar(4) DEFAULT NULL,
192 `TPR_BATCH_RUN_ID` int(11) DEFAULT NULL,
193 `RECORD_IDENTIFIER` varchar(2) DEFAULT NULL,
194 `EMPLOYEE_SSN` bigint(11) DEFAULT NULL,
195 `EMPLOYEE_FIRST_NAME` varchar(16) DEFAULT NULL,
196 `EMPLOYEE_MIDDLE_NAME` varchar(16) DEFAULT NULL,
197 `EMPLOYEE_LAST_NAME` varchar(30) DEFAULT NULL,
198 `EMPLOYEE_RES_ADDR_LINE1` varchar(40) DEFAULT NULL,
199 `EMPLOYEE_RES_ADDR_LINE2` varchar(40) DEFAULT NULL,
200 `EMPLOYEE_RES_ADDR_LINE3` varchar(40) DEFAULT NULL,
201 `EMPLOYEE_RES_ADDR_CITY` varchar(25) DEFAULT NULL,
202 `EMPLOYEE_RES_ADDR_STATE` varchar(2) DEFAULT NULL,
203 `EMPLOYEE_RES_ADDR_ZIP_CODE` bigint(11) DEFAULT NULL,
204 `EMPLOYEE_RES_ADDR_ZIP_CODE_4` varchar(4) DEFAULT NULL,
205 `F_COUNTRY_CODE` varchar(2) DEFAULT NULL,
206 `F_COUNTRY_NAME` varchar(25) DEFAULT NULL,
207 `F_ZIP_CODE` varchar(15) DEFAULT NULL,
208 `EMPLOYEE_DATE_OF_BIRTH` varchar(8) DEFAULT NULL,
209 `EMPLOYEE_DATE_OF_HIRE` varchar(8) DEFAULT NULL,
210 `EMPLOYEE_STATE_OF_HIRE` varchar(2) DEFAULT NULL,
211 `FERERAL_EIN` bigint(11) DEFAULT NULL,
212 `STATE_EIN` varchar(12) DEFAULT NULL,
213 `EMPLOYER_NAME` varchar(45) DEFAULT NULL,
214 `EMPLOYER_WRK_ADDR_LINE1` varchar(40) DEFAULT NULL,
215 `EMPLOYER_WRK_ADDR_LINE2` varchar(40) DEFAULT NULL,
216 `EMPLOYER_WRK_ADDR_LINE3` varchar(40) DEFAULT NULL,
217 `EMPLOYER_WRK_ADDR_CITY` varchar(25) DEFAULT NULL,
218 `EMPLOYEE_WRK_ADDR_STATE` varchar(2) DEFAULT NULL,
219 `EMPLOYER_WRK_ADDR_ZIP` bigint(11) DEFAULT NULL,
220 `EMPLOYER_WRK_ADDR_ZIP_CODE_4` varchar(4) DEFAULT NULL,
221 `EF_COUNTRY_CODE` varchar(2) DEFAULT NULL,
222 `EF_COUNTRY_NAME` varchar(25) DEFAULT NULL,
223 `EF_ZIP_CODE` varchar(15) DEFAULT NULL,
224 `EMPLOYER_OPT_ADDR_LINE1` varchar(40) DEFAULT NULL,
225 `EMPLOYER_OPT_ADDR_LINE2` varchar(40) DEFAULT NULL,
226 `EMPLOYER_OPT_ADDR_LINE3` varchar(40) DEFAULT NULL,
227 `EMPLOYER_OPT_ADDR_CITY` varchar(25) DEFAULT NULL,
228 `EMPLOYEE_OPT_ADDR_STATE` varchar(2) DEFAULT NULL,
229 `EMPLOYER_OPT_ADDR_ZIP` varchar(5) DEFAULT NULL,
230 `EMPLOYER_OPT_ADDR_ZIP_CODE_4` varchar(4) DEFAULT NULL,
231 `FOREIGN_COUNTRY_CODE` varchar(2) DEFAULT NULL,
232 `FOREIGN_COUNTRY_NAME` varchar(25) DEFAULT NULL,
233 `FOREIGN_ZIP` varchar(15) DEFAULT NULL,
234 `FILLER` varchar(50) DEFAULT NULL
235) ENGINE=InnoDB DEFAULT CHARSET=utf8;
236/*!40101 SET character_set_client = @saved_cs_client */;
237
238--
239-- Dumping data for table `ENHR_TPR_DTL`
240--
241
242
243
244--
245-- Table structure for table `ENHR_TPR_HDR`
246--
247
248DROP TABLE IF EXISTS `ENHR_TPR_HDR`;
249/*!40101 SET @saved_cs_client = @@character_set_client */;
250/*!40101 SET character_set_client = utf8 */;
251CREATE TABLE `ENHR_TPR_HDR` (
252 `TPR_AS_OF_DT` date NOT NULL,
253 `TPR_INSERT_DT` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
254 `TPR_SOURCE_CD` varchar(4) DEFAULT NULL,
255 `TPR_BATCH_RUN_ID` int(11) DEFAULT NULL,
256 `Header_Record_Type` varchar(2) DEFAULT NULL,
257 `FIPS_CODE` varchar(2) DEFAULT NULL,
258 `Transmitting_Fed_Agency_Code` varchar(9) DEFAULT NULL,
259 `Transmitting_Type` varchar(2) DEFAULT NULL,
260 `DOD_CODE` varchar(1) DEFAULT NULL,
261 `Version_number` varchar(2) DEFAULT NULL,
262 `Batch_Run_Date` varchar(8) DEFAULT NULL,
263 `Batch_Number` varchar(6) DEFAULT NULL
264) ENGINE=InnoDB DEFAULT CHARSET=utf8;
265/*!40101 SET character_set_client = @saved_cs_client */;
266
267--
268-- Table structure for table `ENHR_TPR_TLR`
269--
270
271DROP TABLE IF EXISTS `ENHR_TPR_TLR`;
272/*!40101 SET @saved_cs_client = @@character_set_client */;
273/*!40101 SET character_set_client = utf8 */;
274CREATE TABLE `ENHR_TPR_TLR` (
275 `TPR_AS_OF_DT` date NOT NULL,
276 `TPR_INSERT_DT` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
277 `TPR_SOURCE_CD` varchar(4) DEFAULT NULL,
278 `TPR_BATCH_RUN_ID` int(11) DEFAULT NULL,
279 `Trailer_Record_Type` varchar(2) DEFAULT NULL,
280 `Total_Records` bigint(11) DEFAULT NULL
281) ENGINE=InnoDB DEFAULT CHARSET=utf8;
282/*!40101 SET character_set_client = @saved_cs_client */;
283
284
285--
286-- Table structure for table `MEDI_TPR_PIC_DTL`
287--
288
289DROP TABLE IF EXISTS `MEDI_TPR_PIC_DTL`;
290/*!40101 SET @saved_cs_client = @@character_set_client */;
291/*!40101 SET character_set_client = utf8 */;
292CREATE TABLE `MEDI_TPR_PIC_DTL` (
293 `TPR_AS_OF_DT` date NOT NULL,
294 `TPR_INSERT_DT` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
295 `TPR_SOURCE_CD` varchar(4) DEFAULT NULL,
296 `TPR_BATCH_RUN_ID` int(11) DEFAULT NULL,
297 `CASE_NUM` bigint(11) DEFAULT NULL,
298 `INDV_ID` bigint(11) NOT NULL DEFAULT '0',
299 `FIRST_NAME` varchar(30) DEFAULT NULL,
300 `MIDDLE_NAME` varchar(30) DEFAULT NULL,
301 `LAST_NAME` varchar(30) DEFAULT NULL,
302 `SSN` bigint(11) DEFAULT NULL,
303 `STATE_ID` varchar(25) DEFAULT NULL,
304 `DOB` date DEFAULT NULL,
305 `ADDR_LINE_1` varchar(50) DEFAULT NULL,
306 `ADDR_LINE_2` varchar(50) DEFAULT NULL,
307 `ADDR_CITY` varchar(35) DEFAULT NULL,
308 `ADDR_STATE_CD` varchar(2) DEFAULT NULL,
309 `ADDR_ZIP5` bigint(11) DEFAULT NULL,
310 `ADDR_ZIP4` bigint(11) DEFAULT NULL,
311 `PH_NUM` bigint(11) DEFAULT NULL,
312 `ALIEN_RGISTRNUM` varchar(15) DEFAULT NULL,
313 `ALIEN_CARD_TYPE` varchar(50) DEFAULT NULL,
314 `ALIEN_CARD_NUM` varchar(32) DEFAULT NULL,
315 `ALIEN_CARD_EXPIRE_DT` date DEFAULT NULL,
316 `SPONSORSHIP_RQST_MD` bigint(11) DEFAULT NULL,
317 `PROGRAM_CDS` varchar(30) DEFAULT NULL,
318 `SAVE_REQUEST_IND` varchar(1) DEFAULT NULL,
319 `RV_REQUEST_IND` varchar(1) DEFAULT NULL,
320 `TWC_REQUEST_IND` varchar(1) DEFAULT NULL,
321 `TWN_REQUEST_IND` varchar(1) DEFAULT NULL,
322 `NH_REQUEST_IND` varchar(1) DEFAULT NULL,
323 `CS_REQUEST_IND` varchar(1) DEFAULT NULL,
324 PRIMARY KEY (`TPR_AS_OF_DT`,`INDV_ID`)
325) ENGINE=InnoDB DEFAULT CHARSET=utf8;
326/*!40101 SET character_set_client = @saved_cs_client */;
327
328--
329-- Table structure for table `MEDI_TPR_PIC_HDR`
330--
331
332DROP TABLE IF EXISTS `MEDI_TPR_PIC_HDR`;
333/*!40101 SET @saved_cs_client = @@character_set_client */;
334/*!40101 SET character_set_client = utf8 */;
335CREATE TABLE `MEDI_TPR_PIC_HDR` (
336 `TPR_AS_OF_DT` date NOT NULL,
337 `TPR_INSERT_DT` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
338 `TPR_SOURCE_CD` varchar(4) DEFAULT NULL,
339 `TPR_BATCH_RUN_ID` int(11) DEFAULT NULL,
340 `Header` varchar(1) DEFAULT NULL,
341 `Row_Count` bigint(11) DEFAULT NULL,
342 `File_Date` date DEFAULT NULL,
343 `Due_Date` date DEFAULT NULL,
344 `Batch_User_ID` varchar(20) DEFAULT NULL,
345 `Portfolio_Type` varchar(5) DEFAULT NULL
346) ENGINE=InnoDB DEFAULT CHARSET=utf8;
347/*!40101 SET character_set_client = @saved_cs_client */;
348
349
350--
351-- Table structure for table `MEDI_TPR_REN_DTL`
352--
353
354DROP TABLE IF EXISTS `MEDI_TPR_REN_DTL`;
355/*!40101 SET @saved_cs_client = @@character_set_client */;
356/*!40101 SET character_set_client = utf8 */;
357CREATE TABLE `MEDI_TPR_REN_DTL` (
358 `TPR_AS_OF_DT` date NOT NULL,
359 `TPR_INSERT_DT` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
360 `TPR_SOURCE_CD` varchar(4) DEFAULT NULL,
361 `TPR_BATCH_RUN_ID` int(11) DEFAULT NULL,
362 `CASE_NUM` bigint(11) DEFAULT NULL,
363 `INDV_ID` bigint(11) NOT NULL DEFAULT '0',
364 `FIRST_NAME` varchar(30) DEFAULT NULL,
365 `MIDDLE_NAME` varchar(30) DEFAULT NULL,
366 `LAST_NAME` varchar(30) DEFAULT NULL,
367 `SSN` bigint(11) DEFAULT NULL,
368 `STATE_ID` varchar(25) DEFAULT NULL,
369 `DOB` date DEFAULT NULL,
370 `ADDR_LINE_1` varchar(50) DEFAULT NULL,
371 `ADDR_LINE_2` varchar(50) DEFAULT NULL,
372 `ADDR_CITY` varchar(35) DEFAULT NULL,
373 `ADDR_STATE_CD` varchar(2) DEFAULT NULL,
374 `ADDR_ZIP5` bigint(11) DEFAULT NULL,
375 `ADDR_ZIP4` bigint(11) DEFAULT NULL,
376 `PH_NUM` bigint(11) DEFAULT NULL,
377 `ALIEN_RGISTRNUM` varchar(15) DEFAULT NULL,
378 `ALIEN_CARD_TYPE` varchar(50) DEFAULT NULL,
379 `ALIEN_CARD_NUM` varchar(32) DEFAULT NULL,
380 `ALIEN_CARD_EXPIRE_DT` date DEFAULT NULL,
381 `SPONSORSHIP_RQST_MD` bigint(11) DEFAULT NULL,
382 `PROGRAM_CDS` varchar(30) DEFAULT NULL,
383 `SAVE_REQUEST_IND` varchar(1) DEFAULT NULL,
384 `RV_REQUEST_IND` varchar(1) DEFAULT NULL,
385 `TWC_REQUEST_IND` varchar(1) DEFAULT NULL,
386 `TWN_REQUEST_IND` varchar(1) DEFAULT NULL,
387 `NH_REQUEST_IND` varchar(1) DEFAULT NULL,
388 `CS_REQUEST_IND` varchar(1) DEFAULT NULL,
389 PRIMARY KEY (`TPR_AS_OF_DT`,`INDV_ID`)
390) ENGINE=InnoDB DEFAULT CHARSET=utf8;
391/*!40101 SET character_set_client = @saved_cs_client */;
392
393--
394-- Table structure for table `MEDI_TPR_REN_HDR`
395--
396
397DROP TABLE IF EXISTS `MEDI_TPR_REN_HDR`;
398/*!40101 SET @saved_cs_client = @@character_set_client */;
399/*!40101 SET character_set_client = utf8 */;
400CREATE TABLE `MEDI_TPR_REN_HDR` (
401 `TPR_AS_OF_DT` date NOT NULL,
402 `TPR_INSERT_DT` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
403 `TPR_SOURCE_CD` varchar(4) DEFAULT NULL,
404 `TPR_BATCH_RUN_ID` int(11) DEFAULT NULL,
405 `Header` varchar(1) DEFAULT NULL,
406 `Row_Count` bigint(11) DEFAULT NULL,
407 `File_Date` date DEFAULT NULL,
408 `Due_Date` date DEFAULT NULL,
409 `Batch_User_ID` varchar(20) DEFAULT NULL,
410 `Portfolio_Type` varchar(5) DEFAULT NULL
411) ENGINE=InnoDB DEFAULT CHARSET=utf8;
412/*!40101 SET character_set_client = @saved_cs_client */;
413
414--
415-- Table structure for table `NHRD_TPR_IN_DTL_1`
416--
417
418DROP TABLE IF EXISTS `NHRD_TPR_IN_DTL_1`;
419/*!40101 SET @saved_cs_client = @@character_set_client */;
420/*!40101 SET character_set_client = utf8 */;
421CREATE TABLE `NHRD_TPR_IN_DTL_1` (
422 `TPR_AS_OF_DT` date NOT NULL,
423 `TPR_INSERT_DT` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
424 `TPR_SOURCE_CD` varchar(4) DEFAULT NULL,
425 `TPR_BATCH_RUN_ID` int(11) DEFAULT NULL,
426 `Submitter_Identifier` varchar(3) DEFAULT NULL,
427 `Record_Identifier` varchar(3) DEFAULT NULL,
428 `SSN` varchar(9) DEFAULT NULL,
429 `Verification_Request_Code` varchar(1) DEFAULT NULL,
430 `Filler1` varchar(5) DEFAULT NULL,
431 `Person_First_Name` varchar(10) DEFAULT NULL,
432 `Person_Middle_Name` varchar(10) DEFAULT NULL,
433 `Person_Last_Name` varchar(20) DEFAULT NULL,
434 `Filler2` varchar(10) DEFAULT NULL,
435 `W_4_Match_Indicator_1` varchar(1) DEFAULT NULL,
436 `W_4_Same_State_Data_Indicator_1` varchar(1) DEFAULT NULL,
437 `W_4_From_Date_1` varchar(8) DEFAULT NULL,
438 `W_4_Through_Date_1` varchar(8) DEFAULT NULL,
439 `QW_Match_Code` varchar(1) DEFAULT NULL,
440 `QW_Same_State_Data_Indicator` varchar(1) DEFAULT NULL,
441 `QW_From_Reporting_Period` varchar(5) DEFAULT NULL,
442 `QW_Through_Reporting_Period` varchar(5) DEFAULT NULL,
443 `QW_From_Date` varchar(8) DEFAULT NULL,
444 `QW_Through_Date` varchar(8) DEFAULT NULL,
445 `UI_Match_Code` varchar(1) DEFAULT NULL,
446 `UI_Same_State_Data_Indicator` varchar(1) DEFAULT NULL,
447 `UI_From_Reporting_Period` varchar(5) DEFAULT NULL,
448 `UI_Through_Reporting_Period` varchar(5) DEFAULT NULL,
449 `UI_From_Date` varchar(8) DEFAULT NULL,
450 `UI_Through_Date` varchar(8) DEFAULT NULL,
451 `Filler3` varchar(50) DEFAULT NULL,
452 `W_4_Processed_Date` varchar(8) DEFAULT NULL,
453 `W_4_Employee_First_Name` varchar(16) DEFAULT NULL,
454 `W_4_Employee_Middle_Name` varchar(16) DEFAULT NULL,
455 `W_4_Employee_Last_Name` varchar(30) DEFAULT NULL,
456 `W_4_Employee_Street__Address_Line_1` varchar(40) DEFAULT NULL,
457 `W_4_Employee_Street_Address_Line_2` varchar(40) DEFAULT NULL,
458 `W_4_Employee_Street_Address_Line_3` varchar(40) DEFAULT NULL,
459 `W_4_Employee_City` varchar(25) DEFAULT NULL,
460 `W_4_Employee_State` varchar(2) DEFAULT NULL,
461 `W_4_Employee_Zip_Code` varchar(5) DEFAULT NULL,
462 `W_4_Employee_Zip_Code_Extension` varchar(4) DEFAULT NULL,
463 `Employee_Foreign_Country_Code` varchar(2) DEFAULT NULL,
464 `Employee_Foreign_Country_Name` varchar(25) DEFAULT NULL,
465 `Employee_Foreign_Zip_Code__` varchar(15) DEFAULT NULL,
466 `Employee_Date_of_Hire` varchar(8) DEFAULT NULL,
467 `Employee_State_of_Hire` varchar(2) DEFAULT NULL,
468 `Federal_EIN` varchar(9) DEFAULT NULL,
469 `State_EIN` varchar(12) DEFAULT NULL,
470 `Department_of_Defense_Code_DOD` varchar(1) DEFAULT NULL,
471 `Employer_Name` varchar(45) DEFAULT NULL,
472 `Employer_Street_Address_Line_1` varchar(40) DEFAULT NULL,
473 `Employer_Street_Address_Line_2` varchar(40) DEFAULT NULL,
474 `Employer_Street_Address_Line_3` varchar(40) DEFAULT NULL,
475 `Employer_City` varchar(25) DEFAULT NULL,
476 `Employer_State` varchar(2) DEFAULT NULL,
477 `Employer_Zip_Code` varchar(5) DEFAULT NULL,
478 `Employer_Zip_Code_Extension` varchar(4) DEFAULT NULL,
479 `Employer_Foreign_Country_Code` varchar(2) DEFAULT NULL,
480 `Employer_Foreign_Country_Name` varchar(25) DEFAULT NULL,
481 `Employer_Foreign_Zip_Code__` varchar(15) DEFAULT NULL,
482 `Employer_Optional_Street_Address_Line_1` varchar(40) DEFAULT NULL,
483 `Employer_Optional_Street_Address_Line_2` varchar(40) DEFAULT NULL,
484 `Employer_Optional_Street_Address_Line_3` varchar(40) DEFAULT NULL,
485 `Employer_Optional_City` varchar(25) DEFAULT NULL,
486 `Employer_Optional_State` varchar(2) DEFAULT NULL,
487 `Employer_Optional_Zip_Code` varchar(5) DEFAULT NULL,
488 `Employer_Optional_Zip_Code_Extension` varchar(4) DEFAULT NULL,
489 `Employer_Optional_Foreign_Country_Code` varchar(2) DEFAULT NULL,
490 `Employer_Optional_Foreign_Country_Name` varchar(25) DEFAULT NULL,
491 `Employer_Optional_Foreign_Zip_Code` varchar(15) DEFAULT NULL,
492 `Filler4` varchar(2) DEFAULT NULL,
493 `Passback_Data` varchar(20) DEFAULT NULL,
494 `W_4_Match_Indicator_2` varchar(1) DEFAULT NULL,
495 `W_4_Same_State_Data_Indicator_2` varchar(1) DEFAULT NULL,
496 `W_4_From_Date_2` varchar(8) DEFAULT NULL,
497 `W_4_Through_Date_2` varchar(8) DEFAULT NULL,
498 `Filler5` varchar(56) DEFAULT NULL,
499 `Transmitter_Agency_Code` varchar(9) DEFAULT NULL,
500 `Transmitter_State_Code` varchar(2) DEFAULT NULL,
501 `Transmitter_State_or_Agency_Name` varchar(58) DEFAULT NULL,
502 `Filler6` varchar(23) DEFAULT NULL
503) ENGINE=InnoDB DEFAULT CHARSET=utf8;
504/*!40101 SET character_set_client = @saved_cs_client */;
505
506
507--
508-- Table structure for table `NHRD_TPR_IN_DTL_2`
509--
510
511DROP TABLE IF EXISTS `NHRD_TPR_IN_DTL_2`;
512/*!40101 SET @saved_cs_client = @@character_set_client */;
513/*!40101 SET character_set_client = utf8 */;
514CREATE TABLE `NHRD_TPR_IN_DTL_2` (
515 `TPR_AS_OF_DT` date NOT NULL,
516 `TPR_INSERT_DT` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
517 `TPR_SOURCE_CD` varchar(4) DEFAULT NULL,
518 `TPR_BATCH_RUN_ID` int(11) DEFAULT NULL,
519 `Submitter_Identifier` varchar(3) DEFAULT NULL,
520 `Record_Identifier` varchar(3) DEFAULT NULL,
521 `SSN` varchar(9) DEFAULT NULL,
522 `Verification_Request_Code` varchar(1) DEFAULT NULL,
523 `Person_First_Name` varchar(10) DEFAULT NULL,
524 `Person_Middle_Name` varchar(10) DEFAULT NULL,
525 `Person_Last_Name` varchar(20) DEFAULT NULL,
526 `Passback_Data` varchar(20) DEFAULT NULL,
527 `Filler1` varchar(74) DEFAULT NULL,
528 `Reject_Code` varchar(1) DEFAULT NULL,
529 `Filler2` varchar(849) DEFAULT NULL
530) ENGINE=InnoDB DEFAULT CHARSET=utf8;
531/*!40101 SET character_set_client = @saved_cs_client */;
532
533
534--
535-- Table structure for table `NHRD_TPR_IN_HDR`
536--
537
538DROP TABLE IF EXISTS `NHRD_TPR_IN_HDR`;
539/*!40101 SET @saved_cs_client = @@character_set_client */;
540/*!40101 SET character_set_client = utf8 */;
541CREATE TABLE `NHRD_TPR_IN_HDR` (
542 `TPR_AS_OF_DT` date NOT NULL,
543 `TPR_INSERT_DT` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
544 `TPR_SOURCE_CD` varchar(4) DEFAULT NULL,
545 `TPR_BATCH_RUN_ID` int(11) DEFAULT NULL,
546 `Submitter_Identifier` varchar(3) DEFAULT NULL,
547 `Record_Identifier` varchar(3) DEFAULT NULL,
548 `Date_Processed` varchar(8) DEFAULT NULL,
549 `Submitting_State_Code` varchar(2) DEFAULT NULL,
550 `Filler1` varchar(8) DEFAULT NULL,
551 `Batch_Number` bigint(11) DEFAULT NULL,
552 `Filler2` varchar(968) DEFAULT NULL
553) ENGINE=InnoDB DEFAULT CHARSET=utf8;
554/*!40101 SET character_set_client = @saved_cs_client */;
555
556--
557-- Table structure for table `NHRD_TPR_IN_TLR`
558--
559
560DROP TABLE IF EXISTS `NHRD_TPR_IN_TLR`;
561/*!40101 SET @saved_cs_client = @@character_set_client */;
562/*!40101 SET character_set_client = utf8 */;
563CREATE TABLE `NHRD_TPR_IN_TLR` (
564 `TPR_AS_OF_DT` date NOT NULL,
565 `TPR_INSERT_DT` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
566 `TPR_SOURCE_CD` varchar(4) DEFAULT NULL,
567 `TPR_BATCH_RUN_ID` int(11) DEFAULT NULL,
568 `Submitter_Identifier` varchar(3) DEFAULT NULL,
569 `Record_Identifier` varchar(3) DEFAULT NULL,
570 `W_4_Records` bigint(11) DEFAULT NULL,
571 `UI_Records` bigint(11) DEFAULT NULL,
572 `QW_Records` bigint(11) DEFAULT NULL,
573 `Notify_Records` bigint(11) DEFAULT NULL,
574 `Total_Records` bigint(11) DEFAULT NULL,
575 `Filler` varchar(939) DEFAULT NULL
576) ENGINE=InnoDB DEFAULT CHARSET=utf8;
577/*!40101 SET character_set_client = @saved_cs_client */;
578
579
580--
581-- Table structure for table `TDCJ_TPR_MASTER`
582--
583
584DROP TABLE IF EXISTS `TDCJ_TPR_MASTER`;
585/*!40101 SET @saved_cs_client = @@character_set_client */;
586/*!40101 SET character_set_client = utf8 */;
587CREATE TABLE `TDCJ_TPR_MASTER` (
588 `TPR_AS_OF_DT` date NOT NULL,
589 `TPR_INSERT_DT` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
590 `TPR_SOURCE_CD` varchar(4) DEFAULT NULL,
591 `TPR_BATCH_RUN_ID` int(11) DEFAULT NULL,
592 `TDCJ_ID` bigint(11) NOT NULL DEFAULT '0',
593 `SSN` varchar(9) DEFAULT NULL,
594 `NAME` varchar(30) DEFAULT NULL,
595 `DOB` date DEFAULT NULL,
596 `GENDER` varchar(1) DEFAULT NULL,
597 `RACE` varchar(1) DEFAULT NULL,
598 `UNIT` varchar(2) DEFAULT NULL,
599 `SID_NUMBER` bigint(11) DEFAULT NULL,
600 `STATUS` varchar(1) DEFAULT NULL,
601 `RECEIVE_DATE` date DEFAULT NULL,
602 PRIMARY KEY (`TPR_AS_OF_DT`,`TDCJ_ID`)
603) ENGINE=InnoDB DEFAULT CHARSET=latin1;
604/*!40101 SET character_set_client = @saved_cs_client */;
605
606--
607-- Table structure for table `TDCJ_TPR_TRANSFER`
608--
609
610DROP TABLE IF EXISTS `TDCJ_TPR_TRANSFER`;
611/*!40101 SET @saved_cs_client = @@character_set_client */;
612/*!40101 SET character_set_client = utf8 */;
613CREATE TABLE `TDCJ_TPR_TRANSFER` (
614 `TPR_AS_OF_DT` date NOT NULL,
615 `TPR_INSERT_DT` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
616 `TPR_SOURCE_CD` varchar(4) DEFAULT NULL,
617 `TPR_BATCH_RUN_ID` int(11) DEFAULT NULL,
618 `TDCJ_ID` bigint(11) NOT NULL DEFAULT '0',
619 `TRANSFER_DATE` date NOT NULL DEFAULT '0000-00-00',
620 `TRANSFER_CODE` varchar(2) NOT NULL DEFAULT '',
621 `UNIT_CODE` varchar(2) NOT NULL DEFAULT '',
622 `TRANSACTION_TIMESTAMP` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
623 PRIMARY KEY (`TPR_AS_OF_DT`,`TDCJ_ID`,`TRANSFER_DATE`,`TRANSFER_CODE`,`UNIT_CODE`,`TRANSACTION_TIMESTAMP`)
624) ENGINE=InnoDB DEFAULT CHARSET=latin1;
625/*!40101 SET character_set_client = @saved_cs_client */;
626
627--
628-- Table structure for table `TDCJ_TPR_UNIT`
629--
630
631DROP TABLE IF EXISTS `TDCJ_TPR_UNIT`;
632/*!40101 SET @saved_cs_client = @@character_set_client */;
633/*!40101 SET character_set_client = utf8 */;
634CREATE TABLE `TDCJ_TPR_UNIT` (
635 `TPR_AS_OF_DT` date NOT NULL,
636 `TPR_INSERT_DT` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
637 `TPR_SOURCE_CD` varchar(4) DEFAULT NULL,
638 `TPR_BATCH_RUN_ID` int(11) DEFAULT NULL,
639 `UNIT_CODE` varchar(2) NOT NULL DEFAULT '',
640 `UNIT_NAME` varchar(30) DEFAULT NULL,
641 `UNIT_ADDR` varchar(35) DEFAULT NULL,
642 `UNIT_CITY` varchar(20) DEFAULT NULL,
643 `UNIT_STATE` varchar(2) DEFAULT NULL,
644 `UNIT_ZIP` varchar(5) DEFAULT NULL,
645 `UNIT_ZIP4` varchar(4) DEFAULT NULL,
646 `UNIT_COUNTY_FIPS_CODE` varchar(3) DEFAULT NULL,
647 `UNIT_COUNTY_DESC` varchar(13) DEFAULT NULL,
648 PRIMARY KEY (`TPR_AS_OF_DT`,`UNIT_CODE`)
649) ENGINE=InnoDB DEFAULT CHARSET=latin1;
650/*!40101 SET character_set_client = @saved_cs_client */;
651
652--
653-- Table structure for table `TIER_STG_DC_CASES`
654--
655
656DROP TABLE IF EXISTS `TIER_STG_DC_CASES`;
657/*!40101 SET @saved_cs_client = @@character_set_client */;
658/*!40101 SET character_set_client = utf8 */;
659CREATE TABLE `TIER_STG_DC_CASES` (
660 `TPR_AS_OF_DT` date NOT NULL,
661 `TPR_INSERT_DT` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
662 `TPR_SOURCE_CD` varchar(4) DEFAULT NULL,
663 `TPR_BATCH_RUN_ID` int(11) DEFAULT NULL,
664 `ACTION_DT` datetime DEFAULT NULL,
665 `APPLICATION_SIGNED_SW` varchar(1) DEFAULT NULL,
666 `APP_INTAKE_IND` varchar(1) DEFAULT NULL,
667 `APP_RECVD_DT` datetime DEFAULT NULL,
668 `ARCHIVE_DT` datetime DEFAULT NULL,
669 `AUTHORIZED_EMAIL_SW` varchar(1) DEFAULT NULL,
670 `AUTHREP_CAREGIVER_SW` varchar(1) DEFAULT NULL,
671 `AUTHREP_FIRST_NAME` varchar(30) DEFAULT NULL,
672 `AUTHREP_INDV_ID_VRF_CD` varchar(2) DEFAULT NULL,
673 `AUTHREP_LAST_NAME` varchar(30) DEFAULT NULL,
674 `AUTHREP_MID_NAME` varchar(30) DEFAULT NULL,
675 `AUTHREP_ORG_NAME` varchar(30) DEFAULT NULL,
676 `AUTHREP_OTHER_NUM` varchar(10) DEFAULT NULL,
677 `AUTHREP_PH_NUM` varchar(10) DEFAULT NULL,
678 `AUTHREP_PREFIX_NAME` varchar(4) DEFAULT NULL,
679 `AUTHREP_SUFX_NAME` varchar(4) DEFAULT NULL,
680 `AUTHREP_TYPE_CD` varchar(2) DEFAULT NULL,
681 `AUTHREP_VERF_CD` varchar(2) DEFAULT NULL,
682 `AUTHREP_WORK_NUM` varchar(10) DEFAULT NULL,
683 `AUTH_REP_ID` varchar(30) DEFAULT NULL,
684 `AUTH_REP_SW` varchar(1) DEFAULT NULL,
685 `BPR_COMNTS_REFRESH_DT` datetime DEFAULT NULL,
686 `CASE_CLOSED_DT` datetime DEFAULT NULL,
687 `CASE_MODE_CD` varchar(8) DEFAULT NULL,
688 `CASE_NUM` int(11) NOT NULL DEFAULT '0',
689 `CASE_STATUS_CD` varchar(8) DEFAULT NULL,
690 `CBO_SW` varchar(1) DEFAULT NULL,
691 `CREATE_DT` datetime DEFAULT NULL,
692 `CREATE_USER_ID` varchar(20) DEFAULT NULL,
693 `DELETE_SW` varchar(1) DEFAULT NULL,
694 `DHS_EMP_CASE_SW` varchar(1) DEFAULT NULL,
695 `DISASTER_SEQ_NUM` int(11) DEFAULT NULL,
696 `EMAIL` varchar(125) DEFAULT NULL,
697 `EMP_ID` int(11) DEFAULT NULL,
698 `EVERYONE_LIVE_AT_HOUSEHOLD_SW` varchar(1) DEFAULT NULL,
699 `HISTORY_SEQ` int(11) DEFAULT NULL,
700 `INTERPRETER_LANG_CD` varchar(2) DEFAULT NULL,
701 `INTERVIEW_DT` datetime DEFAULT NULL,
702 `INTERVIEW_TYPE_CD` varchar(8) DEFAULT NULL,
703 `LANGUAGE_CD` varchar(2) DEFAULT NULL,
704 `MBCC_SW` varchar(1) DEFAULT NULL,
705 `MD_ALLOWED_SW` varchar(1) DEFAULT NULL,
706 `MD_EXISTS_SW` varchar(1) DEFAULT NULL,
707 `NAVIGATOR_SW` varchar(1) DEFAULT NULL,
708 `OFFICE_NUM` int(11) DEFAULT NULL,
709 `OTHER_LANGUAGE` varchar(255) DEFAULT NULL,
710 `OTHER_PH_NUM` varchar(10) DEFAULT NULL,
711 `PH_NUM` varchar(10) DEFAULT NULL,
712 `PROCESS_ID` varchar(8) DEFAULT NULL,
713 `REACTIVATION_DT` datetime DEFAULT NULL,
714 `REACTIVATION_IND` varchar(1) DEFAULT NULL,
715 `RELATIONSHIP_INDV_CD` varchar(2) DEFAULT NULL,
716 `RENEWAL_CVG_FUTURE_YRS_CD` varchar(4) DEFAULT NULL,
717 `SENT_AUTHREP_SW` varchar(1) DEFAULT NULL,
718 `SP_PROG_REQ_TYPE_CD` varchar(2) DEFAULT NULL,
719 `UNABLE_TO_LOCATE_SW` varchar(1) DEFAULT NULL,
720 `UNIQUE_TRANS_ID` int(11) DEFAULT NULL,
721 `VENDOR_STAFF_SW` varchar(1) DEFAULT NULL,
722 `WHP_PH_NUM` varchar(10) DEFAULT NULL,
723 `WORK_PH_NUM` varchar(10) DEFAULT NULL,
724 `WORK_PH_NUM_EXT` int(11) DEFAULT NULL,
725 `POSITION1` int(11) DEFAULT NULL,
726 PRIMARY KEY (`TPR_AS_OF_DT`,`CASE_NUM`)
727) ENGINE=InnoDB DEFAULT CHARSET=utf8;
728/*!40101 SET character_set_client = @saved_cs_client */;
729
730--
731-- Dumping data for table `TIER_STG_DC_CASES`
732--
733
734--
735-- Table structure for table `TIER_STG_DC_CASE_PROGRAM`
736--
737
738DROP TABLE IF EXISTS `TIER_STG_DC_CASE_PROGRAM`;
739/*!40101 SET @saved_cs_client = @@character_set_client */;
740/*!40101 SET character_set_client = utf8 */;
741CREATE TABLE `TIER_STG_DC_CASE_PROGRAM` (
742 `TPR_AS_OF_DT` date NOT NULL,
743 `TPR_INSERT_DT` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
744 `TPR_SOURCE_CD` varchar(4) DEFAULT NULL,
745 `TPR_BATCH_RUN_ID` int(11) DEFAULT NULL,
746 `APPLICATION_DT` datetime DEFAULT NULL,
747 `ARCHIVE_DT` datetime DEFAULT NULL,
748 `CASE_NUM` int(11) NOT NULL DEFAULT '0',
749 `CHIP_APP_NUM` varchar(7) DEFAULT NULL,
750 `CHIP_REFERRAL_SW` varchar(1) DEFAULT NULL,
751 `CONVERSION_DT` datetime DEFAULT NULL,
752 `CREATE_DT` datetime DEFAULT NULL,
753 `CREATE_USER_ID` varchar(20) DEFAULT NULL,
754 `DELAY_RSN_CD` varchar(4) DEFAULT NULL,
755 `DELAY_RSN_DT` datetime DEFAULT NULL,
756 `DENIAL_COMMENTS` varchar(500) DEFAULT NULL,
757 `DENIAL_REASON_CD` varchar(3) DEFAULT NULL,
758 `EFF_BEGIN_DT` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
759 `EFF_END_DT` datetime DEFAULT NULL,
760 `EXPEDITED_SW` varchar(1) DEFAULT NULL,
761 `GRANDPARENT_SUBSIDY_SW` varchar(1) DEFAULT NULL,
762 `HISTORY_SEQ` int(11) DEFAULT NULL,
763 `HIST_NAV_IND` varchar(1) DEFAULT NULL,
764 `INDV_REQ_PENDING_SW` varchar(1) DEFAULT NULL,
765 `INTERVIEW_DT` datetime DEFAULT NULL,
766 `IS_COMPLETE_SW` varchar(1) DEFAULT NULL,
767 `OTTANF_REQUESTED_SW` varchar(1) DEFAULT NULL,
768 `PRIOR_MA_SEQ_NUM` int(11) NOT NULL DEFAULT '0',
769 `PRIOR_MEDICAID_CD` varchar(8) NOT NULL DEFAULT '',
770 `PRIOR_REUSE_APP_DT` datetime DEFAULT NULL,
771 `PROCESS_ID` varchar(8) DEFAULT NULL,
772 `PROGRAM_ACTION_CD` varchar(8) DEFAULT NULL,
773 `PROG_CD` varchar(4) NOT NULL DEFAULT '',
774 `PROG_RESCIND_CD` varchar(8) DEFAULT NULL,
775 `PROG_RESCIND_DT` datetime DEFAULT NULL,
776 `PROG_STATUS_CD` varchar(2) DEFAULT NULL,
777 `PROG_STATUS_DT` datetime DEFAULT NULL,
778 `PROG_WITHDRAW_CD` varchar(1) DEFAULT NULL,
779 `REQUEST_DT` datetime DEFAULT NULL,
780 `REQUEST_DUE_DT` datetime DEFAULT NULL,
781 `REUSE_APP_DT` datetime DEFAULT NULL,
782 `REUSE_APP_SW` varchar(1) DEFAULT NULL,
783 `SUB_PROGRAM_CD` varchar(4) NOT NULL DEFAULT '',
784 `UNIQUE_TRANS_ID` int(11) DEFAULT NULL,
785 `UPDATE_DT` datetime DEFAULT NULL,
786 `UPDATE_USER_ID` varchar(20) DEFAULT NULL,
787 `WITHDRAW_DT` datetime DEFAULT NULL,
788 `WITHDRAW_SW` varchar(1) DEFAULT NULL,
789 `CHIP_EDG_TEST_CD` varchar(20) DEFAULT NULL,
790 `SPL_APP_PROG_MBCC_SW` varchar(1) DEFAULT NULL,
791 `SPL_APP_PROG_3038P_SW` varchar(1) DEFAULT NULL,
792 PRIMARY KEY (`TPR_AS_OF_DT`,`CASE_NUM`,`EFF_BEGIN_DT`,`PRIOR_MA_SEQ_NUM`,`PRIOR_MEDICAID_CD`,`PROG_CD`,`SUB_PROGRAM_CD`)
793) ENGINE=InnoDB DEFAULT CHARSET=utf8;
794/*!40101 SET character_set_client = @saved_cs_client */;
795
796--
797-- Table structure for table `TIER_STG_DC_EMPLOYMENT`
798--
799
800DROP TABLE IF EXISTS `TIER_STG_DC_EMPLOYMENT`;
801/*!40101 SET @saved_cs_client = @@character_set_client */;
802/*!40101 SET character_set_client = utf8 */;
803CREATE TABLE `TIER_STG_DC_EMPLOYMENT` (
804 `TPR_AS_OF_DT` date NOT NULL,
805 `TPR_INSERT_DT` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
806 `TPR_SOURCE_CD` varchar(4) DEFAULT NULL,
807 `TPR_BATCH_RUN_ID` int(11) DEFAULT NULL,
808 `INDV_ID` int(11) NOT NULL DEFAULT '0',
809 `EMP_SEQ_NUM` int(11) NOT NULL DEFAULT '0',
810 `EARNED_INCOME_TYPE_CD` varchar(8) DEFAULT NULL,
811 `EMPLOYMENT_START_DT` datetime DEFAULT NULL,
812 `EMPLOYMENT_VRF_CD` varchar(8) DEFAULT NULL,
813 `EFF_BEGIN_DT` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
814 `EFF_END_DT` datetime DEFAULT NULL,
815 `EIC_AMT` int(11) DEFAULT NULL,
816 `EMPLOYER_PAID_TAX_AMT` int(11) DEFAULT NULL,
817 `INCLUDES_EMPLOYER_PAID_TAX_SW` varchar(1) DEFAULT NULL,
818 `INDV_REDUCED_EMP_30_HRS_SW` varchar(1) DEFAULT NULL,
819 `GOOD_CAUSE_FOR_REDUCED_HRS_CD` varchar(2) DEFAULT NULL,
820 `GOOD_CAUSE_REDUCED_HRS_SW` varchar(1) DEFAULT NULL,
821 `REDUCED_HRS_BEGIN_DT` datetime DEFAULT NULL,
822 `JOB_TITLE` varchar(30) DEFAULT NULL,
823 `LOSS_OF_EMPLOYMENT_VRF_CD` varchar(8) DEFAULT NULL,
824 `VOL_QUIT_COOP_DT` datetime DEFAULT NULL,
825 `VOL_QUIT_NON_COOP_DT` datetime DEFAULT NULL,
826 `VOL_QUIT_REASON_CD` varchar(8) DEFAULT NULL,
827 `ON_STRIKE_SW` varchar(1) DEFAULT NULL,
828 `STRIKE_BEGIN_DT` datetime DEFAULT NULL,
829 `STRIKE_END_DT` datetime DEFAULT NULL,
830 `SUPERVISOR_NAME` varchar(60) DEFAULT NULL,
831 `SUPERVISOR_TITLE` varchar(25) DEFAULT NULL,
832 `SOURCE_IS_MILITARY_SW` varchar(1) DEFAULT NULL,
833 `CREATE_DT` datetime DEFAULT NULL,
834 `TERMINATION_DT` datetime DEFAULT NULL,
835 `TERMINATION_REASON_CD` varchar(8) DEFAULT NULL,
836 `HIRE_DT` datetime DEFAULT NULL,
837 `EMPLOYER_NAME` varchar(50) DEFAULT NULL,
838 `EMPLOYER_ID` int(11) DEFAULT NULL,
839 `ADDR_LINE` varchar(50) DEFAULT NULL,
840 `ADDR_ST_NM` varchar(30) DEFAULT NULL,
841 `ADDR_ST_NUM` varchar(6) DEFAULT NULL,
842 `ADDR_ST_NUM_FRAC` varchar(3) DEFAULT NULL,
843 `ADDR_ZIP5` varchar(5) DEFAULT NULL,
844 `ADDR_ZIP4` varchar(4) DEFAULT NULL,
845 `ADDR_CITY` varchar(25) DEFAULT NULL,
846 `ADDR_STATE_CD` varchar(2) DEFAULT NULL,
847 `ADDR_APT_NUM` varchar(8) DEFAULT NULL,
848 `REPORTED_INCOME_TO_IRS_AMT` int(11) DEFAULT NULL,
849 `REPORT_DT` datetime DEFAULT NULL,
850 `RESOURCE_SEQ_NUM` int(11) DEFAULT NULL,
851 `LOST_EMPLOYMENT_SW` varchar(1) DEFAULT NULL,
852 `EXTRACT_REF_DT` datetime DEFAULT NULL,
853 PRIMARY KEY (`TPR_AS_OF_DT`,`INDV_ID`,`EMP_SEQ_NUM`,`EFF_BEGIN_DT`)
854) ENGINE=InnoDB DEFAULT CHARSET=utf8;
855/*!40101 SET character_set_client = @saved_cs_client */;
856
857--
858-- Table structure for table `TIER_STG_DC_EMP_BUDGET`
859--
860
861DROP TABLE IF EXISTS `TIER_STG_DC_EMP_BUDGET`;
862/*!40101 SET @saved_cs_client = @@character_set_client */;
863/*!40101 SET character_set_client = utf8 */;
864CREATE TABLE `TIER_STG_DC_EMP_BUDGET` (
865 `TPR_AS_OF_DT` date NOT NULL,
866 `TPR_INSERT_DT` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
867 `TPR_SOURCE_CD` varchar(4) DEFAULT NULL,
868 `TPR_BATCH_RUN_ID` int(11) DEFAULT NULL,
869 `INDV_ID` int(11) NOT NULL DEFAULT '0',
870 `EMP_SEQ_NUM` int(11) NOT NULL DEFAULT '0',
871 `AVERAGE_MONTHLY_HOURS` int(11) DEFAULT NULL,
872 `BUDGETED_AMT` int(11) DEFAULT NULL,
873 `BUDGET_BEGIN_DT` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
874 `BUDGET_END_DT` datetime DEFAULT NULL,
875 `PROJECTED_HOURS` int(11) DEFAULT NULL,
876 `PROJECTION_AMT` int(11) DEFAULT NULL,
877 `CREATE_DT` datetime DEFAULT NULL,
878 `FIRST_PAYDT_FOR_CERTIFICATION` datetime DEFAULT NULL,
879 `FIRST_PAYDT_FOR_BUDGET_MONTH` datetime DEFAULT NULL,
880 `LAST_PAYDT_FOR_CERTIFICATION` datetime DEFAULT NULL,
881 `LAST_PAYDT_FOR_BUDGET_MONTH` datetime DEFAULT NULL,
882 `PAYMENTS_EXPECTED` int(11) DEFAULT NULL,
883 `PAYMENTS_RECEIVED` int(11) DEFAULT NULL,
884 `MISSING_PAY_PERIODS` varchar(4000) DEFAULT NULL,
885 `TOTAL_EXPENSE_AMT` int(11) DEFAULT NULL,
886 `PRIOR_MA_BUDGETED_AMT` int(11) DEFAULT NULL,
887 `PRIOR_MA_AVERAGE_MONTHLY_HOURS` int(11) DEFAULT NULL,
888 `PRIOR_MA_TOTAL_EXPENSE_AMT` int(11) DEFAULT NULL,
889 PRIMARY KEY (`TPR_AS_OF_DT`,`INDV_ID`,`EMP_SEQ_NUM`,`BUDGET_BEGIN_DT`)
890) ENGINE=InnoDB DEFAULT CHARSET=utf8;
891/*!40101 SET character_set_client = @saved_cs_client */;
892
893--
894-- Table structure for table `TIER_STG_UNIV_INDV`
895--
896
897DROP TABLE IF EXISTS `TIER_STG_UNIV_INDV`;
898/*!40101 SET @saved_cs_client = @@character_set_client */;
899/*!40101 SET character_set_client = utf8 */;
900CREATE TABLE `TIER_STG_UNIV_INDV` (
901 `TPR_AS_OF_DT` date NOT NULL,
902 `TPR_INSERT_DT` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
903 `TPR_SOURCE_CD` varchar(4) DEFAULT NULL,
904 `TPR_BATCH_RUN_ID` int(11) DEFAULT NULL,
905 `INDV_ID` int(11) NOT NULL DEFAULT '0',
906 `DOB_DT` datetime DEFAULT NULL,
907 `SSN` int(11) DEFAULT NULL,
908 `FIRST_NAME` varchar(30) DEFAULT NULL,
909 `MID_NAME` varchar(30) DEFAULT NULL,
910 `LAST_NAME` varchar(30) DEFAULT NULL,
911 `GENDER_CD` varchar(1) DEFAULT NULL,
912 `RACE_CD` varchar(8) DEFAULT NULL,
913 `DEATH_DT` datetime DEFAULT NULL,
914 `ETHNICITY_CD` varchar(8) DEFAULT NULL,
915 `POTENTIAL_DUP_PRIMARY_ID` int(11) DEFAULT NULL,
916 `POTENTIAL_DUPLICATE_DT` datetime DEFAULT NULL,
917 `POTENTIAL_DUPLICATE_SW` varchar(1) DEFAULT NULL,
918 `POTENTIAL_DUPLICATE_TYPE_CD` varchar(2) DEFAULT NULL,
919 `POTENTIAL_DUPLICATE_USER_ID` varchar(20) DEFAULT NULL,
920 `INACTIVATED_BY_USER_ID` varchar(20) DEFAULT NULL,
921 `INACTIVATED_DT` datetime DEFAULT NULL,
922 `INACTIVATE_TYPE_CD` varchar(2) DEFAULT NULL,
923 `INACTIVE_IND` varchar(1) DEFAULT NULL,
924 `MERGE_SEPARATE_IND` varchar(256) DEFAULT NULL,
925 PRIMARY KEY (`TPR_AS_OF_DT`,`INDV_ID`)
926) ENGINE=InnoDB DEFAULT CHARSET=utf8;
927/*!40101 SET character_set_client = @saved_cs_client */;
928
929
930--
931-- Table structure for table `TIER_STG_UNIV_INDV_ELIG`
932--
933
934DROP TABLE IF EXISTS `TIER_STG_UNIV_INDV_ELIG`;
935/*!40101 SET @saved_cs_client = @@character_set_client */;
936/*!40101 SET character_set_client = utf8 */;
937CREATE TABLE `TIER_STG_UNIV_INDV_ELIG` (
938 `TPR_AS_OF_DT` date NOT NULL,
939 `TPR_INSERT_DT` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
940 `TPR_SOURCE_CD` varchar(4) DEFAULT NULL,
941 `TPR_BATCH_RUN_ID` int(11) DEFAULT NULL,
942 `CASE_NUM` bigint(11) NOT NULL DEFAULT '0',
943 `EDG_NUM` bigint(11) DEFAULT NULL,
944 `EDG_TRACE_ID` bigint(11) NOT NULL DEFAULT '0',
945 `DI_ACTION_AU_DT` datetime DEFAULT NULL,
946 `CERT_BEG_DT` datetime DEFAULT NULL,
947 `CERTAPP_DT` datetime DEFAULT NULL,
948 `PAYMENT_BEG_DT` datetime DEFAULT NULL,
949 `EXTRACT_REF_DT` datetime DEFAULT NULL,
950 `INDV_ID` bigint(11) NOT NULL DEFAULT '0',
951 `ELIG_RSLT_CD` varchar(2) DEFAULT NULL,
952 `ENROLL_STATUS_CD` varchar(2) DEFAULT NULL,
953 `PART_STATUS_CD` varchar(2) DEFAULT NULL,
954 `INCLUSION_RSN_CD` varchar(3) DEFAULT NULL,
955 `PENALTY_SW` varchar(1) DEFAULT NULL,
956 `FORFEIT_SW` varchar(1) DEFAULT NULL,
957 `STATE_TL_EXT_HARDSHIP_RSN_CD` varchar(2) DEFAULT NULL,
958 `TL_EXTEND_HARDSHIP_RSN_CD` varchar(2) DEFAULT NULL,
959 `ET_EXEMPT_RSN_CD` varchar(2) DEFAULT NULL,
960 `OTHER_BENEFIT_IND` varchar(1) DEFAULT NULL,
961 `CAT_ELIG_RSN_CD` varchar(2) DEFAULT NULL,
962 `CATEGORY_IND` varchar(1) DEFAULT NULL,
963 `SSI_PART_STATUS_CD` varchar(2) DEFAULT NULL,
964 `UNBORN_COUNT` bigint(11) DEFAULT NULL,
965 `BATTERED_WOMAN_SW` varchar(1) DEFAULT NULL,
966 `URM_SW` varchar(1) DEFAULT NULL,
967 `CONSIDER_EXTRA_NEED_DED_SW` varchar(1) DEFAULT NULL,
968 `DISABILITY_SW` varchar(1) DEFAULT NULL,
969 `DISQUALIFIED_SW` varchar(1) DEFAULT NULL,
970 `AGE_IN_MONTHS` bigint(11) DEFAULT NULL,
971 `AID_REQUEST_DT` datetime DEFAULT NULL,
972 `MID_MONTH_SW` varchar(1) DEFAULT NULL,
973 `OLD_INDV_ID` bigint(11) DEFAULT NULL,
974 `MULTI_ELIG_REC_SW` varchar(1) DEFAULT NULL,
975 `NF_ELIG_RSLT_CD` varchar(2) DEFAULT NULL,
976 `POTENTIAL_ENROLL_BEG_DT` datetime DEFAULT NULL,
977 `POTENTIAL_ENROLL_END_DT` datetime DEFAULT NULL,
978 `POTENTIAL_ELIG_BEG_DT` datetime DEFAULT NULL,
979 `POTENTIAL_ELIG_END_DT` datetime DEFAULT NULL,
980 `ELIGIBILITY_BEG_DT` datetime DEFAULT NULL,
981 `ELIGIBILITY_END_DT` datetime DEFAULT NULL,
982 `COPAY_AMT` bigint(11) DEFAULT NULL,
983 `EDG_PART_STATUS_CD` varchar(2) DEFAULT NULL,
984 `EID_ELIG_SW` varchar(1) DEFAULT NULL,
985 `ELDS_ERN_UNERN_RES_SUCC_SW` varchar(1) DEFAULT NULL,
986 `ELDS_OAG_RESP_SUCCESS_SW` varchar(1) DEFAULT NULL,
987 `ELDS_RESULT_SUCCESS_SW` varchar(1) DEFAULT NULL,
988 `GROUP_IND` varchar(1) DEFAULT NULL,
989 `INDV_REL_TO_APPLICANT_CD` varchar(2) DEFAULT NULL,
990 `PRIORITY_STATUS_SW` varchar(1) DEFAULT NULL,
991 `TARGET_SW` varchar(1) DEFAULT NULL,
992 PRIMARY KEY (`TPR_AS_OF_DT`,`CASE_NUM`,`EDG_TRACE_ID`,`INDV_ID`)
993) ENGINE=InnoDB DEFAULT CHARSET=utf8;
994/*!40101 SET character_set_client = @saved_cs_client */;
995
996--
997-- Table structure for table `TIER_TPR_DC_CASES`
998--
999
1000DROP TABLE IF EXISTS `TIER_TPR_DC_CASES`;
1001/*!40101 SET @saved_cs_client = @@character_set_client */;
1002/*!40101 SET character_set_client = utf8 */;
1003CREATE TABLE `TIER_TPR_DC_CASES` (
1004 `TPR_AS_OF_DT` date NOT NULL,
1005 `TPR_INSERT_DT` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
1006 `TPR_SOURCE_CD` varchar(4) DEFAULT NULL,
1007 `TPR_BATCH_RUN_ID` int(11) DEFAULT NULL,
1008 `ACTION_DT` datetime DEFAULT NULL,
1009 `APPLICATION_SIGNED_SW` varchar(1) DEFAULT NULL,
1010 `APP_INTAKE_IND` varchar(1) DEFAULT NULL,
1011 `APP_RECVD_DT` datetime DEFAULT NULL,
1012 `ARCHIVE_DT` datetime DEFAULT NULL,
1013 `AUTHORIZED_EMAIL_SW` varchar(1) DEFAULT NULL,
1014 `AUTHREP_CAREGIVER_SW` varchar(1) DEFAULT NULL,
1015 `AUTHREP_FIRST_NAME` varchar(30) DEFAULT NULL,
1016 `AUTHREP_INDV_ID_VRF_CD` varchar(2) DEFAULT NULL,
1017 `AUTHREP_LAST_NAME` varchar(30) DEFAULT NULL,
1018 `AUTHREP_MID_NAME` varchar(30) DEFAULT NULL,
1019 `AUTHREP_ORG_NAME` varchar(30) DEFAULT NULL,
1020 `AUTHREP_OTHER_NUM` varchar(10) DEFAULT NULL,
1021 `AUTHREP_PH_NUM` varchar(10) DEFAULT NULL,
1022 `AUTHREP_PREFIX_NAME` varchar(4) DEFAULT NULL,
1023 `AUTHREP_SUFX_NAME` varchar(4) DEFAULT NULL,
1024 `AUTHREP_TYPE_CD` varchar(2) DEFAULT NULL,
1025 `AUTHREP_VERF_CD` varchar(2) DEFAULT NULL,
1026 `AUTHREP_WORK_NUM` varchar(10) DEFAULT NULL,
1027 `AUTH_REP_ID` varchar(30) DEFAULT NULL,
1028 `AUTH_REP_SW` varchar(1) DEFAULT NULL,
1029 `BPR_COMNTS_REFRESH_DT` datetime DEFAULT NULL,
1030 `CASE_CLOSED_DT` datetime DEFAULT NULL,
1031 `CASE_MODE_CD` varchar(8) DEFAULT NULL,
1032 `CASE_NUM` int(11) NOT NULL DEFAULT '0',
1033 `CASE_STATUS_CD` varchar(8) DEFAULT NULL,
1034 `CBO_SW` varchar(1) DEFAULT NULL,
1035 `CREATE_DT` datetime DEFAULT NULL,
1036 `CREATE_USER_ID` varchar(20) DEFAULT NULL,
1037 `DELETE_SW` varchar(1) DEFAULT NULL,
1038 `DHS_EMP_CASE_SW` varchar(1) DEFAULT NULL,
1039 `DISASTER_SEQ_NUM` int(11) DEFAULT NULL,
1040 `EMAIL` varchar(125) DEFAULT NULL,
1041 `EMP_ID` int(11) DEFAULT NULL,
1042 `EVERYONE_LIVE_AT_HOUSEHOLD_SW` varchar(1) DEFAULT NULL,
1043 `HISTORY_SEQ` int(11) DEFAULT NULL,
1044 `INTERPRETER_LANG_CD` varchar(2) DEFAULT NULL,
1045 `INTERVIEW_DT` datetime DEFAULT NULL,
1046 `INTERVIEW_TYPE_CD` varchar(8) DEFAULT NULL,
1047 `LANGUAGE_CD` varchar(2) DEFAULT NULL,
1048 `MBCC_SW` varchar(1) DEFAULT NULL,
1049 `MD_ALLOWED_SW` varchar(1) DEFAULT NULL,
1050 `MD_EXISTS_SW` varchar(1) DEFAULT NULL,
1051 `NAVIGATOR_SW` varchar(1) DEFAULT NULL,
1052 `OFFICE_NUM` int(11) DEFAULT NULL,
1053 `OTHER_LANGUAGE` varchar(255) DEFAULT NULL,
1054 `OTHER_PH_NUM` varchar(10) DEFAULT NULL,
1055 `PH_NUM` varchar(10) DEFAULT NULL,
1056 `PROCESS_ID` varchar(8) DEFAULT NULL,
1057 `REACTIVATION_DT` datetime DEFAULT NULL,
1058 `REACTIVATION_IND` varchar(1) DEFAULT NULL,
1059 `RELATIONSHIP_INDV_CD` varchar(2) DEFAULT NULL,
1060 `RENEWAL_CVG_FUTURE_YRS_CD` varchar(4) DEFAULT NULL,
1061 `SENT_AUTHREP_SW` varchar(1) DEFAULT NULL,
1062 `SP_PROG_REQ_TYPE_CD` varchar(2) DEFAULT NULL,
1063 `UNABLE_TO_LOCATE_SW` varchar(1) DEFAULT NULL,
1064 `UNIQUE_TRANS_ID` int(11) DEFAULT NULL,
1065 `VENDOR_STAFF_SW` varchar(1) DEFAULT NULL,
1066 `WHP_PH_NUM` varchar(10) DEFAULT NULL,
1067 `WORK_PH_NUM` varchar(10) DEFAULT NULL,
1068 `WORK_PH_NUM_EXT` int(11) DEFAULT NULL,
1069 `POSITION1` int(11) DEFAULT NULL,
1070 PRIMARY KEY (`TPR_AS_OF_DT`,`CASE_NUM`)
1071) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1072/*!40101 SET character_set_client = @saved_cs_client */;
1073
1074--
1075-- Dumping data for table `TIER_TPR_DC_CASES`
1076--
1077
1078LOCK TABLES `TIER_TPR_DC_CASES` WRITE;
1079/*!40000 ALTER TABLE `TIER_TPR_DC_CASES` DISABLE KEYS */;
1080/*!40000 ALTER TABLE `TIER_TPR_DC_CASES` ENABLE KEYS */;
1081UNLOCK TABLES;
1082
1083--
1084-- Table structure for table `TIER_TPR_DC_CASE_PROGRAM`
1085--
1086
1087DROP TABLE IF EXISTS `TIER_TPR_DC_CASE_PROGRAM`;
1088/*!40101 SET @saved_cs_client = @@character_set_client */;
1089/*!40101 SET character_set_client = utf8 */;
1090CREATE TABLE `TIER_TPR_DC_CASE_PROGRAM` (
1091 `TPR_AS_OF_DT` date NOT NULL,
1092 `TPR_INSERT_DT` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
1093 `TPR_SOURCE_CD` varchar(4) DEFAULT NULL,
1094 `TPR_BATCH_RUN_ID` int(11) DEFAULT NULL,
1095 `APPLICATION_DT` datetime DEFAULT NULL,
1096 `ARCHIVE_DT` datetime DEFAULT NULL,
1097 `CASE_NUM` int(11) NOT NULL DEFAULT '0',
1098 `CHIP_APP_NUM` varchar(7) DEFAULT NULL,
1099 `CHIP_REFERRAL_SW` varchar(1) DEFAULT NULL,
1100 `CONVERSION_DT` datetime DEFAULT NULL,
1101 `CREATE_DT` datetime DEFAULT NULL,
1102 `CREATE_USER_ID` varchar(20) DEFAULT NULL,
1103 `DELAY_RSN_CD` varchar(4) DEFAULT NULL,
1104 `DELAY_RSN_DT` datetime DEFAULT NULL,
1105 `DENIAL_COMMENTS` varchar(500) DEFAULT NULL,
1106 `DENIAL_REASON_CD` varchar(3) DEFAULT NULL,
1107 `EFF_BEGIN_DT` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
1108 `EFF_END_DT` datetime DEFAULT NULL,
1109 `EXPEDITED_SW` varchar(1) DEFAULT NULL,
1110 `GRANDPARENT_SUBSIDY_SW` varchar(1) DEFAULT NULL,
1111 `HISTORY_SEQ` int(11) DEFAULT NULL,
1112 `HIST_NAV_IND` varchar(1) DEFAULT NULL,
1113 `INDV_REQ_PENDING_SW` varchar(1) DEFAULT NULL,
1114 `INTERVIEW_DT` datetime DEFAULT NULL,
1115 `IS_COMPLETE_SW` varchar(1) DEFAULT NULL,
1116 `OTTANF_REQUESTED_SW` varchar(1) DEFAULT NULL,
1117 `PRIOR_MA_SEQ_NUM` int(11) NOT NULL DEFAULT '0',
1118 `PRIOR_MEDICAID_CD` varchar(8) NOT NULL DEFAULT '',
1119 `PRIOR_REUSE_APP_DT` datetime DEFAULT NULL,
1120 `PROCESS_ID` varchar(8) DEFAULT NULL,
1121 `PROGRAM_ACTION_CD` varchar(8) DEFAULT NULL,
1122 `PROG_CD` varchar(4) NOT NULL DEFAULT '',
1123 `PROG_RESCIND_CD` varchar(8) DEFAULT NULL,
1124 `PROG_RESCIND_DT` datetime DEFAULT NULL,
1125 `PROG_STATUS_CD` varchar(2) DEFAULT NULL,
1126 `PROG_STATUS_DT` datetime DEFAULT NULL,
1127 `PROG_WITHDRAW_CD` varchar(1) DEFAULT NULL,
1128 `REQUEST_DT` datetime DEFAULT NULL,
1129 `REQUEST_DUE_DT` datetime DEFAULT NULL,
1130 `REUSE_APP_DT` datetime DEFAULT NULL,
1131 `REUSE_APP_SW` varchar(1) DEFAULT NULL,
1132 `SUB_PROGRAM_CD` varchar(4) NOT NULL DEFAULT '',
1133 `UNIQUE_TRANS_ID` int(11) DEFAULT NULL,
1134 `UPDATE_DT` datetime DEFAULT NULL,
1135 `UPDATE_USER_ID` varchar(20) DEFAULT NULL,
1136 `WITHDRAW_DT` datetime DEFAULT NULL,
1137 `WITHDRAW_SW` varchar(1) DEFAULT NULL,
1138 `CHIP_EDG_TEST_CD` varchar(20) DEFAULT NULL,
1139 `SPL_APP_PROG_MBCC_SW` varchar(1) DEFAULT NULL,
1140 `SPL_APP_PROG_3038P_SW` varchar(1) DEFAULT NULL,
1141 PRIMARY KEY (`TPR_AS_OF_DT`,`CASE_NUM`,`EFF_BEGIN_DT`,`PRIOR_MA_SEQ_NUM`,`PRIOR_MEDICAID_CD`,`PROG_CD`,`SUB_PROGRAM_CD`)
1142) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1143/*!40101 SET character_set_client = @saved_cs_client */;
1144
1145--
1146-- Dumping data for table `TIER_TPR_DC_CASE_PROGRAM`
1147--
1148
1149LOCK TABLES `TIER_TPR_DC_CASE_PROGRAM` WRITE;
1150/*!40000 ALTER TABLE `TIER_TPR_DC_CASE_PROGRAM` DISABLE KEYS */;
1151/*!40000 ALTER TABLE `TIER_TPR_DC_CASE_PROGRAM` ENABLE KEYS */;
1152UNLOCK TABLES;
1153
1154--
1155-- Table structure for table `TIER_TPR_DC_EMPLOYMENT`
1156--
1157
1158DROP TABLE IF EXISTS `TIER_TPR_DC_EMPLOYMENT`;
1159/*!40101 SET @saved_cs_client = @@character_set_client */;
1160/*!40101 SET character_set_client = utf8 */;
1161CREATE TABLE `TIER_TPR_DC_EMPLOYMENT` (
1162 `TPR_AS_OF_DT` date NOT NULL,
1163 `TPR_INSERT_DT` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
1164 `TPR_SOURCE_CD` varchar(4) DEFAULT NULL,
1165 `TPR_BATCH_RUN_ID` int(11) DEFAULT NULL,
1166 `INDV_ID` int(11) NOT NULL DEFAULT '0',
1167 `EMP_SEQ_NUM` int(11) NOT NULL DEFAULT '0',
1168 `EARNED_INCOME_TYPE_CD` varchar(8) DEFAULT NULL,
1169 `EMPLOYMENT_START_DT` datetime DEFAULT NULL,
1170 `EMPLOYMENT_VRF_CD` varchar(8) DEFAULT NULL,
1171 `EFF_BEGIN_DT` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
1172 `EFF_END_DT` datetime DEFAULT NULL,
1173 `EIC_AMT` int(11) DEFAULT NULL,
1174 `EMPLOYER_PAID_TAX_AMT` int(11) DEFAULT NULL,
1175 `INCLUDES_EMPLOYER_PAID_TAX_SW` varchar(1) DEFAULT NULL,
1176 `INDV_REDUCED_EMP_30_HRS_SW` varchar(1) DEFAULT NULL,
1177 `GOOD_CAUSE_FOR_REDUCED_HRS_CD` varchar(2) DEFAULT NULL,
1178 `GOOD_CAUSE_REDUCED_HRS_SW` varchar(1) DEFAULT NULL,
1179 `REDUCED_HRS_BEGIN_DT` datetime DEFAULT NULL,
1180 `JOB_TITLE` varchar(30) DEFAULT NULL,
1181 `LOSS_OF_EMPLOYMENT_VRF_CD` varchar(8) DEFAULT NULL,
1182 `VOL_QUIT_COOP_DT` datetime DEFAULT NULL,
1183 `VOL_QUIT_NON_COOP_DT` datetime DEFAULT NULL,
1184 `VOL_QUIT_REASON_CD` varchar(8) DEFAULT NULL,
1185 `ON_STRIKE_SW` varchar(1) DEFAULT NULL,
1186 `STRIKE_BEGIN_DT` datetime DEFAULT NULL,
1187 `STRIKE_END_DT` datetime DEFAULT NULL,
1188 `SUPERVISOR_NAME` varchar(60) DEFAULT NULL,
1189 `SUPERVISOR_TITLE` varchar(25) DEFAULT NULL,
1190 `SOURCE_IS_MILITARY_SW` varchar(1) DEFAULT NULL,
1191 `CREATE_DT` datetime DEFAULT NULL,
1192 `TERMINATION_DT` datetime DEFAULT NULL,
1193 `TERMINATION_REASON_CD` varchar(8) DEFAULT NULL,
1194 `HIRE_DT` datetime DEFAULT NULL,
1195 `EMPLOYER_NAME` varchar(50) DEFAULT NULL,
1196 `EMPLOYER_ID` int(11) DEFAULT NULL,
1197 `ADDR_LINE` varchar(50) DEFAULT NULL,
1198 `ADDR_ST_NM` varchar(30) DEFAULT NULL,
1199 `ADDR_ST_NUM` varchar(6) DEFAULT NULL,
1200 `ADDR_ST_NUM_FRAC` varchar(3) DEFAULT NULL,
1201 `ADDR_ZIP5` varchar(5) DEFAULT NULL,
1202 `ADDR_ZIP4` varchar(4) DEFAULT NULL,
1203 `ADDR_CITY` varchar(25) DEFAULT NULL,
1204 `ADDR_STATE_CD` varchar(2) DEFAULT NULL,
1205 `ADDR_APT_NUM` varchar(8) DEFAULT NULL,
1206 `REPORTED_INCOME_TO_IRS_AMT` int(11) DEFAULT NULL,
1207 `REPORT_DT` datetime DEFAULT NULL,
1208 `RESOURCE_SEQ_NUM` int(11) DEFAULT NULL,
1209 `LOST_EMPLOYMENT_SW` varchar(1) DEFAULT NULL,
1210 `EXTRACT_REF_DT` datetime DEFAULT NULL,
1211 PRIMARY KEY (`TPR_AS_OF_DT`,`INDV_ID`,`EMP_SEQ_NUM`,`EFF_BEGIN_DT`)
1212) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1213/*!40101 SET character_set_client = @saved_cs_client */;
1214
1215--
1216-- Dumping data for table `TIER_TPR_DC_EMPLOYMENT`
1217--
1218
1219LOCK TABLES `TIER_TPR_DC_EMPLOYMENT` WRITE;
1220/*!40000 ALTER TABLE `TIER_TPR_DC_EMPLOYMENT` DISABLE KEYS */;
1221/*!40000 ALTER TABLE `TIER_TPR_DC_EMPLOYMENT` ENABLE KEYS */;
1222UNLOCK TABLES;
1223
1224--
1225-- Table structure for table `TIER_TPR_DC_EMP_BUDGET`
1226--
1227
1228DROP TABLE IF EXISTS `TIER_TPR_DC_EMP_BUDGET`;
1229/*!40101 SET @saved_cs_client = @@character_set_client */;
1230/*!40101 SET character_set_client = utf8 */;
1231CREATE TABLE `TIER_TPR_DC_EMP_BUDGET` (
1232 `TPR_AS_OF_DT` date NOT NULL,
1233 `TPR_INSERT_DT` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
1234 `TPR_SOURCE_CD` varchar(4) DEFAULT NULL,
1235 `TPR_BATCH_RUN_ID` int(11) DEFAULT NULL,
1236 `INDV_ID` int(11) NOT NULL DEFAULT '0',
1237 `EMP_SEQ_NUM` int(11) NOT NULL DEFAULT '0',
1238 `AVERAGE_MONTHLY_HOURS` int(11) DEFAULT NULL,
1239 `BUDGETED_AMT` int(11) DEFAULT NULL,
1240 `BUDGET_BEGIN_DT` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
1241 `BUDGET_END_DT` datetime DEFAULT NULL,
1242 `PROJECTED_HOURS` int(11) DEFAULT NULL,
1243 `PROJECTION_AMT` int(11) DEFAULT NULL,
1244 `CREATE_DT` datetime DEFAULT NULL,
1245 `FIRST_PAYDT_FOR_CERTIFICATION` datetime DEFAULT NULL,
1246 `FIRST_PAYDT_FOR_BUDGET_MONTH` datetime DEFAULT NULL,
1247 `LAST_PAYDT_FOR_CERTIFICATION` datetime DEFAULT NULL,
1248 `LAST_PAYDT_FOR_BUDGET_MONTH` datetime DEFAULT NULL,
1249 `PAYMENTS_EXPECTED` int(11) DEFAULT NULL,
1250 `PAYMENTS_RECEIVED` int(11) DEFAULT NULL,
1251 `MISSING_PAY_PERIODS` varchar(4000) DEFAULT NULL,
1252 `TOTAL_EXPENSE_AMT` int(11) DEFAULT NULL,
1253 `PRIOR_MA_BUDGETED_AMT` int(11) DEFAULT NULL,
1254 `PRIOR_MA_AVERAGE_MONTHLY_HOURS` int(11) DEFAULT NULL,
1255 `PRIOR_MA_TOTAL_EXPENSE_AMT` int(11) DEFAULT NULL,
1256 PRIMARY KEY (`TPR_AS_OF_DT`,`INDV_ID`,`EMP_SEQ_NUM`,`BUDGET_BEGIN_DT`)
1257) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1258/*!40101 SET character_set_client = @saved_cs_client */;
1259
1260--
1261-- Dumping data for table `TIER_TPR_DC_EMP_BUDGET`
1262--
1263
1264LOCK TABLES `TIER_TPR_DC_EMP_BUDGET` WRITE;
1265/*!40000 ALTER TABLE `TIER_TPR_DC_EMP_BUDGET` DISABLE KEYS */;
1266/*!40000 ALTER TABLE `TIER_TPR_DC_EMP_BUDGET` ENABLE KEYS */;
1267UNLOCK TABLES;
1268
1269--
1270-- Table structure for table `TIER_TPR_UNIV_DI`
1271--
1272
1273DROP TABLE IF EXISTS `TIER_TPR_UNIV_DI`;
1274/*!40101 SET @saved_cs_client = @@character_set_client */;
1275/*!40101 SET character_set_client = utf8 */;
1276CREATE TABLE `TIER_TPR_UNIV_DI` (
1277 `TPR_AS_OF_DT` date NOT NULL,
1278 `TPR_INSERT_DT` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
1279 `TPR_SOURCE_CD` varchar(4) DEFAULT NULL,
1280 `TPR_BATCH_RUN_ID` int(11) DEFAULT NULL,
1281 `ACTIVITY_TYPE` varchar(8) DEFAULT NULL,
1282 `APPLICATION_DT` datetime DEFAULT NULL,
1283 `AUTO_DISPOSE_SW` varchar(1) DEFAULT NULL,
1284 `BENEFIT_AMT` bigint(11) DEFAULT NULL,
1285 `BENEFIT_STATUS` varchar(2) DEFAULT NULL,
1286 `BUDGET_ADULT_SIZE` bigint(11) DEFAULT NULL,
1287 `BUDGET_CHILD_SIZE` bigint(11) DEFAULT NULL,
1288 `CASE_NUM` bigint(11) NOT NULL DEFAULT '0',
1289 `CERTAPP_DT` datetime DEFAULT NULL,
1290 `CERTBGDI_DT` datetime DEFAULT NULL,
1291 `CERT_BEG_DT` datetime DEFAULT NULL,
1292 `CG_ELIG_RSLT_CD` varchar(2) DEFAULT NULL,
1293 `CG_SIZE` bigint(11) DEFAULT NULL,
1294 `CG_STATUS_CD` varchar(2) DEFAULT NULL,
1295 `COUNT_OF_CG_ADULTS` bigint(11) DEFAULT NULL,
1296 `COUNT_OF_CG_CHILD` bigint(11) DEFAULT NULL,
1297 `CREATE_DT` datetime DEFAULT NULL,
1298 `CREATE_USER_ID` varchar(20) DEFAULT NULL,
1299 `CREATOR_IND` varchar(1) DEFAULT NULL,
1300 `CURRENT_ELIG_IND` varchar(1) DEFAULT NULL,
1301 `DELAY_REASON_CD` varchar(4) DEFAULT NULL,
1302 `DELAY_RSN_CD` varchar(4) DEFAULT NULL,
1303 `DELAY_RSN_DT` datetime DEFAULT NULL,
1304 `DI_ACTION_AU_DT` datetime DEFAULT NULL,
1305 `DI_ACTION_DT` datetime DEFAULT NULL,
1306 `EDBC_ACTION_CD` varchar(4) DEFAULT NULL,
1307 `EDBC_RUN_AU_DT` datetime DEFAULT NULL,
1308 `EDBC_RUN_DT` datetime DEFAULT NULL,
1309 `EDG_NUM` bigint(11) DEFAULT NULL,
1310 `EDG_OVERRIDE_SW` varchar(1) DEFAULT NULL,
1311 `EDG_REPROCESS_SW` varchar(1) DEFAULT NULL,
1312 `EDG_SIZE` bigint(11) DEFAULT NULL,
1313 `EDG_TRACE_ID` bigint(11) NOT NULL DEFAULT '0',
1314 `ELIGBGDI_DT` datetime DEFAULT NULL,
1315 `ELIGIBILITY_BEG_DT` datetime DEFAULT NULL,
1316 `ELIGIBILITY_END_DT` datetime DEFAULT NULL,
1317 `ESTIMATED_ELIG_END_DT` datetime DEFAULT NULL,
1318 `FIN_ELIG_RSLT_CD` varchar(2) DEFAULT NULL,
1319 `FORFEIT_AMT` bigint(11) DEFAULT NULL,
1320 `FORFEIT_MTH_COUNT` bigint(11) DEFAULT NULL,
1321 `FORFEIT_MTH_DT` datetime DEFAULT NULL,
1322 `FS_CAT_ELIG_CD` varchar(2) DEFAULT NULL,
1323 `FS_ELD_DIS_GROUP_SW` varchar(1) DEFAULT NULL,
1324 `FS_EXPEDITED_SW` varchar(1) DEFAULT NULL,
1325 `GROUP_NUM` bigint(11) DEFAULT NULL,
1326 `HOMELESS_HOUSEHOLD_SW` varchar(1) DEFAULT NULL,
1327 `INC_EXCL_ELIG_RSLT_CD` varchar(2) DEFAULT NULL,
1328 `NET_BENEFIT_AMT` bigint(11) DEFAULT NULL,
1329 `NF_ELIG_RSLT_CD` varchar(2) DEFAULT NULL,
1330 `OFFSET_AMT` bigint(11) DEFAULT NULL,
1331 `OVERPAYMENT_AMT` bigint(11) DEFAULT NULL,
1332 `PAYMENT_AMT` bigint(11) DEFAULT NULL,
1333 `PAYMENT_BEG_DT` datetime DEFAULT NULL,
1334 `PAYMENT_END_DT` datetime DEFAULT NULL,
1335 `PENALTY_AMT` bigint(11) DEFAULT NULL,
1336 `PRIMARY_WORKER_ID` bigint(11) DEFAULT NULL,
1337 `PROGRAM_CD` varchar(4) DEFAULT NULL,
1338 `PROG_REQ_APP_DT` datetime DEFAULT NULL,
1339 `RECOUPMENT_AMT` bigint(11) DEFAULT NULL,
1340 `REC_BEGIN_DT` datetime DEFAULT NULL,
1341 `REC_END_DT` datetime DEFAULT NULL,
1342 `REFERENCE_EDG_TRACE_ID` bigint(11) DEFAULT NULL,
1343 `REQUEST_DT` datetime DEFAULT NULL,
1344 `RSC_ELIG_RSLT_CD` varchar(2) DEFAULT NULL,
1345 `SANCTION_STATUS_CD` varchar(5) DEFAULT NULL,
1346 `SEC_ACTIVITY_TYPE` varchar(4) DEFAULT NULL,
1347 `SIGN_DT` datetime DEFAULT NULL,
1348 `SIMPLIFIED_REPORTING_GRP_CD` varchar(3) DEFAULT NULL,
1349 `SUPPLEMENT_AMT` bigint(11) DEFAULT NULL,
1350 `TIERS_RECUP_BALANCE_AMT` bigint(11) DEFAULT NULL,
1351 `TYPE_OF_ASSISTANCE_CD` varchar(4) DEFAULT NULL,
1352 `UNIQUE_TRANS_ID` bigint(11) DEFAULT NULL,
1353 `UPDATE_DT` datetime DEFAULT NULL,
1354 `VERIFICATION_STATUS_CD` varchar(2) DEFAULT NULL,
1355 `ABAWD_SW` varchar(1) DEFAULT NULL,
1356 `COPAY_AMT` bigint(11) DEFAULT NULL,
1357 `EDG_CLOSURE_SW` varchar(1) DEFAULT NULL,
1358 `EDG_COMBINE_SW` varchar(1) DEFAULT NULL,
1359 `EDG_TRANSACTION_ID` bigint(11) DEFAULT NULL,
1360 `ELDS_INDV_OUT_OF_CASE_SW` varchar(1) DEFAULT NULL,
1361 `FS_CERT_MTHS` bigint(11) DEFAULT NULL,
1362 `FS_SHORT_CERT_SW` varchar(1) DEFAULT NULL,
1363 `HIS_TYPE_IND` bigint(11) DEFAULT NULL,
1364 `MANUALLY_ISSUED_AMT` bigint(11) DEFAULT NULL,
1365 `PIC_RSLT_CD` varchar(100) DEFAULT NULL,
1366 `PREMIUM_AMT` bigint(11) DEFAULT NULL,
1367 `RES_TRF_SW` varchar(1) DEFAULT NULL,
1368 `RISK_LEVEL_CD` varchar(1) DEFAULT NULL,
1369 `SP_REVIEW_CD` varchar(2) DEFAULT NULL,
1370 `SP_REVIEW_DT` datetime DEFAULT NULL,
1371 `TO_BE_RECOVERED_AMT` bigint(11) DEFAULT NULL,
1372 `TOA_PRIORITY_NUM` bigint(11) DEFAULT NULL,
1373 `VCL_APP_DUE_DT` datetime DEFAULT NULL,
1374 `EXTRACT_REF_DT` datetime DEFAULT NULL,
1375 `POSITION1` bigint(11) DEFAULT NULL,
1376 `LASTBENEFITMONTH` datetime DEFAULT NULL,
1377 PRIMARY KEY (`TPR_AS_OF_DT`,`CASE_NUM`,`EDG_TRACE_ID`)
1378) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1379/*!40101 SET character_set_client = @saved_cs_client */;
1380
1381--
1382-- Dumping data for table `TIER_TPR_UNIV_DI`
1383--
1384
1385LOCK TABLES `TIER_TPR_UNIV_DI` WRITE;
1386/*!40000 ALTER TABLE `TIER_TPR_UNIV_DI` DISABLE KEYS */;
1387/*!40000 ALTER TABLE `TIER_TPR_UNIV_DI` ENABLE KEYS */;
1388UNLOCK TABLES;
1389
1390--
1391-- Table structure for table `TIER_TPR_UNIV_INDV`
1392--
1393
1394DROP TABLE IF EXISTS `TIER_TPR_UNIV_INDV`;
1395/*!40101 SET @saved_cs_client = @@character_set_client */;
1396/*!40101 SET character_set_client = utf8 */;
1397CREATE TABLE `TIER_TPR_UNIV_INDV` (
1398 `TPR_AS_OF_DT` date NOT NULL,
1399 `TPR_INSERT_DT` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
1400 `TPR_SOURCE_CD` varchar(4) DEFAULT NULL,
1401 `TPR_BATCH_RUN_ID` int(11) DEFAULT NULL,
1402 `INDV_ID` int(11) NOT NULL DEFAULT '0',
1403 `DOB_DT` datetime DEFAULT NULL,
1404 `SSN` int(11) DEFAULT NULL,
1405 `FIRST_NAME` varchar(30) DEFAULT NULL,
1406 `MID_NAME` varchar(30) DEFAULT NULL,
1407 `LAST_NAME` varchar(30) DEFAULT NULL,
1408 `GENDER_CD` varchar(1) DEFAULT NULL,
1409 `RACE_CD` varchar(8) DEFAULT NULL,
1410 `DEATH_DT` datetime DEFAULT NULL,
1411 `ETHNICITY_CD` varchar(8) DEFAULT NULL,
1412 `POTENTIAL_DUP_PRIMARY_ID` int(11) DEFAULT NULL,
1413 `POTENTIAL_DUPLICATE_DT` datetime DEFAULT NULL,
1414 `POTENTIAL_DUPLICATE_SW` varchar(1) DEFAULT NULL,
1415 `POTENTIAL_DUPLICATE_TYPE_CD` varchar(2) DEFAULT NULL,
1416 `POTENTIAL_DUPLICATE_USER_ID` varchar(20) DEFAULT NULL,
1417 `INACTIVATED_BY_USER_ID` varchar(20) DEFAULT NULL,
1418 `INACTIVATED_DT` datetime DEFAULT NULL,
1419 `INACTIVATE_TYPE_CD` varchar(2) DEFAULT NULL,
1420 `INACTIVE_IND` varchar(1) DEFAULT NULL,
1421 `MERGE_SEPARATE_IND` varchar(256) DEFAULT NULL,
1422 PRIMARY KEY (`TPR_AS_OF_DT`,`INDV_ID`)
1423) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1424/*!40101 SET character_set_client = @saved_cs_client */;
1425
1426--
1427-- Dumping data for table `TIER_TPR_UNIV_INDV`
1428--
1429
1430LOCK TABLES `TIER_TPR_UNIV_INDV` WRITE;
1431/*!40000 ALTER TABLE `TIER_TPR_UNIV_INDV` DISABLE KEYS */;
1432/*!40000 ALTER TABLE `TIER_TPR_UNIV_INDV` ENABLE KEYS */;
1433UNLOCK TABLES;
1434
1435--
1436-- Table structure for table `TIER_TPR_UNIV_INDV_ELIG`
1437--
1438
1439DROP TABLE IF EXISTS `TIER_TPR_UNIV_INDV_ELIG`;
1440/*!40101 SET @saved_cs_client = @@character_set_client */;
1441/*!40101 SET character_set_client = utf8 */;
1442CREATE TABLE `TIER_TPR_UNIV_INDV_ELIG` (
1443 `TPR_AS_OF_DT` date NOT NULL,
1444 `TPR_INSERT_DT` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
1445 `TPR_SOURCE_CD` varchar(4) DEFAULT NULL,
1446 `TPR_BATCH_RUN_ID` int(11) DEFAULT NULL,
1447 `CASE_NUM` bigint(11) NOT NULL DEFAULT '0',
1448 `EDG_NUM` bigint(11) DEFAULT NULL,
1449 `EDG_TRACE_ID` bigint(11) NOT NULL DEFAULT '0',
1450 `DI_ACTION_AU_DT` datetime DEFAULT NULL,
1451 `CERT_BEG_DT` datetime DEFAULT NULL,
1452 `CERTAPP_DT` datetime DEFAULT NULL,
1453 `PAYMENT_BEG_DT` datetime DEFAULT NULL,
1454 `EXTRACT_REF_DT` datetime DEFAULT NULL,
1455 `INDV_ID` bigint(11) NOT NULL DEFAULT '0',
1456 `ELIG_RSLT_CD` varchar(2) DEFAULT NULL,
1457 `ENROLL_STATUS_CD` varchar(2) DEFAULT NULL,
1458 `PART_STATUS_CD` varchar(2) DEFAULT NULL,
1459 `INCLUSION_RSN_CD` varchar(3) DEFAULT NULL,
1460 `PENALTY_SW` varchar(1) DEFAULT NULL,
1461 `FORFEIT_SW` varchar(1) DEFAULT NULL,
1462 `STATE_TL_EXT_HARDSHIP_RSN_CD` varchar(2) DEFAULT NULL,
1463 `TL_EXTEND_HARDSHIP_RSN_CD` varchar(2) DEFAULT NULL,
1464 `ET_EXEMPT_RSN_CD` varchar(2) DEFAULT NULL,
1465 `OTHER_BENEFIT_IND` varchar(1) DEFAULT NULL,
1466 `CAT_ELIG_RSN_CD` varchar(2) DEFAULT NULL,
1467 `CATEGORY_IND` varchar(1) DEFAULT NULL,
1468 `SSI_PART_STATUS_CD` varchar(2) DEFAULT NULL,
1469 `UNBORN_COUNT` bigint(11) DEFAULT NULL,
1470 `BATTERED_WOMAN_SW` varchar(1) DEFAULT NULL,
1471 `URM_SW` varchar(1) DEFAULT NULL,
1472 `CONSIDER_EXTRA_NEED_DED_SW` varchar(1) DEFAULT NULL,
1473 `DISABILITY_SW` varchar(1) DEFAULT NULL,
1474 `DISQUALIFIED_SW` varchar(1) DEFAULT NULL,
1475 `AGE_IN_MONTHS` bigint(11) DEFAULT NULL,
1476 `AID_REQUEST_DT` datetime DEFAULT NULL,
1477 `MID_MONTH_SW` varchar(1) DEFAULT NULL,
1478 `OLD_INDV_ID` bigint(11) DEFAULT NULL,
1479 `MULTI_ELIG_REC_SW` varchar(1) DEFAULT NULL,
1480 `NF_ELIG_RSLT_CD` varchar(2) DEFAULT NULL,
1481 `POTENTIAL_ENROLL_BEG_DT` datetime DEFAULT NULL,
1482 `POTENTIAL_ENROLL_END_DT` datetime DEFAULT NULL,
1483 `POTENTIAL_ELIG_BEG_DT` datetime DEFAULT NULL,
1484 `POTENTIAL_ELIG_END_DT` datetime DEFAULT NULL,
1485 `ELIGIBILITY_BEG_DT` datetime DEFAULT NULL,
1486 `ELIGIBILITY_END_DT` datetime DEFAULT NULL,
1487 `COPAY_AMT` bigint(11) DEFAULT NULL,
1488 `EDG_PART_STATUS_CD` varchar(2) DEFAULT NULL,
1489 `EID_ELIG_SW` varchar(1) DEFAULT NULL,
1490 `ELDS_ERN_UNERN_RES_SUCC_SW` varchar(1) DEFAULT NULL,
1491 `ELDS_OAG_RESP_SUCCESS_SW` varchar(1) DEFAULT NULL,
1492 `ELDS_RESULT_SUCCESS_SW` varchar(1) DEFAULT NULL,
1493 `GROUP_IND` varchar(1) DEFAULT NULL,
1494 `INDV_REL_TO_APPLICANT_CD` varchar(2) DEFAULT NULL,
1495 `PRIORITY_STATUS_SW` varchar(1) DEFAULT NULL,
1496 `TARGET_SW` varchar(1) DEFAULT NULL,
1497 PRIMARY KEY (`TPR_AS_OF_DT`,`CASE_NUM`,`EDG_TRACE_ID`,`INDV_ID`)
1498) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1499/*!40101 SET character_set_client = @saved_cs_client */;
1500
1501--
1502-- Dumping data for table `TIER_TPR_UNIV_INDV_ELIG`
1503--
1504
1505LOCK TABLES `TIER_TPR_UNIV_INDV_ELIG` WRITE;
1506/*!40000 ALTER TABLE `TIER_TPR_UNIV_INDV_ELIG` DISABLE KEYS */;
1507/*!40000 ALTER TABLE `TIER_TPR_UNIV_INDV_ELIG` ENABLE KEYS */;
1508UNLOCK TABLES;
1509
1510--
1511-- Temporary view structure for view `V_NDNH_DETAIL`
1512--
1513
1514DROP TABLE IF EXISTS `V_NDNH_DETAIL`;
1515/*!50001 DROP VIEW IF EXISTS `V_NDNH_DETAIL`*/;
1516SET @saved_cs_client = @@character_set_client;
1517SET character_set_client = utf8;
1518/*!50001 CREATE VIEW `V_NDNH_DETAIL` AS SELECT
1519 1 AS `SUBMITTER_IDENTIFIER`,
1520 1 AS `RECORD_IDENTIFIER`,
1521 1 AS `SSN`,
1522 1 AS `VERIFICATION_REQUEST_CODE`,
1523 1 AS `PERSON_FIRST_NAME`,
1524 1 AS `PERSON_MIDDLE_NAME`,
1525 1 AS `PERSON_LAST_NAME`,
1526 1 AS `PASSBACK_DATA`,
1527 1 AS `W_4_MATCH_INDICATOR`,
1528 1 AS `W_4_SAME_STATE_DATA_INDICATOR`,
1529 1 AS `W_4_FROM_DATE`,
1530 1 AS `W_4_THROUGH_DATE`,
1531 1 AS `QW_MATCH_CODE`,
1532 1 AS `QW_SAME_STATE_DATA_INDICATOR`,
1533 1 AS `QW_FROM_REPORTING_PERIOD`,
1534 1 AS `QW_THROUGH_REPORTING_PERIOD`,
1535 1 AS `QW_FROM_DATE`,
1536 1 AS `QW_THROUGH_DATE`,
1537 1 AS `UI_MATCH_CODE`,
1538 1 AS `UI_SAME_STATE_DATA_INDICATOR`,
1539 1 AS `UI_FROM_REPORTING_PERIOD`,
1540 1 AS `UI_THROUGH_REPORTING_PERIOD`,
1541 1 AS `UI_FROM_DATE`,
1542 1 AS `UI_THROUGH_DATE`,
1543 1 AS `FILLER`*/;
1544SET character_set_client = @saved_cs_client;
1545
1546--
1547-- Temporary view structure for view `V_NDNH_HEADER`
1548--
1549
1550DROP TABLE IF EXISTS `V_NDNH_HEADER`;
1551/*!50001 DROP VIEW IF EXISTS `V_NDNH_HEADER`*/;
1552SET @saved_cs_client = @@character_set_client;
1553SET character_set_client = utf8;
1554/*!50001 CREATE VIEW `V_NDNH_HEADER` AS SELECT
1555 1 AS `SUBMITTER_IDENTIFIER`,
1556 1 AS `RECORD_IDENTIFIER`,
1557 1 AS `SUBMITTING_STATE_CODE`,
1558 1 AS `DATE_STAMP`,
1559 1 AS `FILLER1`,
1560 1 AS `BATCH_NUMBER`,
1561 1 AS `FILLER2`*/;
1562SET character_set_client = @saved_cs_client;
1563
1564--
1565-- Temporary view structure for view `V_NDNH_TRAILER`
1566--
1567
1568DROP TABLE IF EXISTS `V_NDNH_TRAILER`;
1569/*!50001 DROP VIEW IF EXISTS `V_NDNH_TRAILER`*/;
1570SET @saved_cs_client = @@character_set_client;
1571SET character_set_client = utf8;
1572/*!50001 CREATE VIEW `V_NDNH_TRAILER` AS SELECT
1573 1 AS `SUBMITTER_IDENTIFIER`,
1574 1 AS `RECORD_IDENTIFIER`,
1575 1 AS `DATA_RECORD_COUNT`,
1576 1 AS `FILLER`*/;
1577SET character_set_client = @saved_cs_client;
1578
1579--
1580-- Dumping routines for database 'healthdev'
1581--
1582/*!50003 DROP PROCEDURE IF EXISTS `NDNH_DETAIL_PROC` */;
1583/*!50003 SET @saved_cs_client = @@character_set_client */ ;
1584/*!50003 SET @saved_cs_results = @@character_set_results */ ;
1585/*!50003 SET @saved_col_connection = @@collation_connection */ ;
1586/*!50003 SET character_set_client = utf8 */ ;
1587/*!50003 SET character_set_results = utf8 */ ;
1588/*!50003 SET collation_connection = utf8_general_ci */ ;
1589/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
1590/*!50003 SET sql_mode = 'STRICT_TRANS_TABLES' */ ;
1591DELIMITER ;;
1592CREATE PROCEDURE `NDNH_DETAIL_PROC`( IN RUN_DATE DATE)
1593BEGIN
1594 SELECT
1595 'SNP' AS SUBMITTER_IDENTIFIER,
1596 'MCH' AS RECORD_IDENTIFIER,
1597 IND.SSN,
1598 'N' AS VERIFICATION_REQUEST_CODE,
1599 UPPER(IND.FIRST_NAME) AS PERSON_FIRST_NAME,
1600 UPPER(IND.MID_NAME) AS PERSON_MIDDLE_NAME,
1601 UPPER(IND.LAST_NAME) AS PERSON_LAST_NAME,
1602 IND.INDV_ID AS PASSBACK_DATA,
1603 'Y' AS W_4_MATCH_INDICATOR,
1604 'N' AS W_4_SAME_STATE_DATA_INDICATOR,
1605 CASE WHEN DI.DI_ACTION_AU_DT BETWEEN DI.APPLICATION_DT AND DATE_ADD(DI.APPLICATION_DT,INTERVAL 60 DAY)
1606 THEN DATE_FORMAT(DATE_ADD(DI.APPLICATION_DT,INTERVAL 60 DAY),'%Y%m%d')
1607 ELSE DATE_FORMAT(DATE_ADD(DI.DI_ACTION_AU_DT, INTERVAL -90 DAY),'%Y%m%d')
1608 END AS W_4_FROM_DATE,
1609 ' ' AS W_4_THROUGH_DATE,
1610 'N' AS QW_MATCH_CODE,
1611 ' ' AS QW_SAME_STATE_DATA_INDICATOR,
1612 ' ' AS QW_FROM_REPORTING_PERIOD,
1613 ' ' AS QW_THROUGH_REPORTING_PERIOD,
1614 ' ' AS QW_FROM_DATE,
1615 ' ' AS QW_THROUGH_DATE,
1616 'N' AS UI_MATCH_CODE,
1617 ' ' AS UI_SAME_STATE_DATA_INDICATOR,
1618 ' ' AS UI_FROM_REPORTING_PERIOD,
1619 ' ' AS UI_THROUGH_REPORTING_PERIOD,
1620 ' ' AS UI_FROM_DATE,
1621 ' ' AS UI_THROUGH_DATE,
1622 ' ' AS FILLER
1623 FROM
1624 healthdev.TIER_TPR_UNIV_DI DI,
1625 healthdev.TIER_TPR_UNIV_INDV_ELIG ELG,
1626 healthdev.TIER_TPR_UNIV_INDV IND
1627 WHERE
1628 DI.CASE_NUM = ELG.CASE_NUM
1629 AND DI.EDG_TRACE_ID = ELG.EDG_TRACE_ID
1630 AND ELG.INDV_ID = IND.INDV_ID
1631 AND DI.PROGRAM_CD = 'FS'
1632 AND ELG.ELIG_RSLT_CD = 'PS'
1633 AND DI.ACTIVITY_TYPE IN ('IN', 'PR')
1634 AND FLOOR(DATEDIFF(CURRENT_DATE,IND.DOB_DT) / 365) >= 18
1635 AND DI.DI_ACTION_AU_DT BETWEEN DATE_SUB(DATE_FORMAT(RUN_DATE ,'%Y-%m-21'), INTERVAL 1 MONTH) AND DATE_FORMAT(RUN_DATE, '%Y-%m-20')
1636 AND ELG.ELIGIBILITY_END_DT > LAST_DAY(RUN_DATE);
1637END ;;
1638DELIMITER ;
1639/*!50003 SET sql_mode = @saved_sql_mode */ ;
1640/*!50003 SET character_set_client = @saved_cs_client */ ;
1641/*!50003 SET character_set_results = @saved_cs_results */ ;
1642/*!50003 SET collation_connection = @saved_col_connection */ ;
1643
1644--
1645-- Final view structure for view `BCON_RPT_OAG_NEWHIRE`
1646--
1647
1648/*!50001 DROP VIEW IF EXISTS `BCON_RPT_OAG_NEWHIRE`*/;
1649/*!50001 SET @saved_cs_client = @@character_set_client */;
1650/*!50001 SET @saved_cs_results = @@character_set_results */;
1651/*!50001 SET @saved_col_connection = @@collation_connection */;
1652/*!50001 SET character_set_client = utf8 */;
1653/*!50001 SET character_set_results = utf8 */;
1654/*!50001 SET collation_connection = utf8_general_ci */;
1655/*!50001 CREATE ALGORITHM=UNDEFINED */
1656/*!50013 SQL SECURITY DEFINER */
1657/*!50001 VIEW `BCON_RPT_OAG_NEWHIRE` AS select concat(`E`.`EMPLOYER_NAME`,' - ',date_format(str_to_date(`E`.`EMPLOYEE_DATE_OF_HIRE`,'%Y%m%d'),'%m/%d/%Y')) AS `Employer - Employee Hire Date`,concat(`E`.`EMPLOYEE_FIRST_NAME`,' ',`E`.`EMPLOYEE_MIDDLE_NAME`,' ',`E`.`EMPLOYEE_LAST_NAME`) AS `Name`,date_format(str_to_date(`E`.`EMPLOYEE_DATE_OF_BIRTH`,'%Y%m%d'),'%m/%d/%Y') AS `Birth Date`,concat('XXX-XX-',right(`E`.`EMPLOYEE_SSN`,4)) AS `SSN`,concat_ws('\n',`E`.`EMPLOYEE_RES_ADDR_LINE1`,`E`.`EMPLOYEE_RES_ADDR_LINE2`,`E`.`EMPLOYEE_RES_ADDR_LINE3`,concat(`E`.`EMPLOYEE_RES_ADDR_CITY`,' ',`E`.`EMPLOYEE_RES_ADDR_STATE`,' ',`E`.`EMPLOYEE_RES_ADDR_ZIP_CODE`,'-',`E`.`EMPLOYEE_RES_ADDR_ZIP_CODE_4`)) AS `Employee Address`,`E`.`EMPLOYER_NAME` AS `Employer Name`,date_format(str_to_date(`E`.`EMPLOYEE_DATE_OF_HIRE`,'%Y%m%d'),'%m/%d/%Y') AS `Employee Hire Date`,`E`.`FERERAL_EIN` AS `FEIN`,concat_ws('\n',`E`.`EMPLOYER_WRK_ADDR_LINE1`,`E`.`EMPLOYER_WRK_ADDR_LINE2`,`E`.`EMPLOYER_WRK_ADDR_LINE3`,concat(`E`.`EMPLOYER_WRK_ADDR_CITY`,' ',`E`.`EMPLOYEE_WRK_ADDR_STATE`,' ',`E`.`EMPLOYER_WRK_ADDR_ZIP`,'-',`E`.`EMPLOYER_WRK_ADDR_ZIP_CODE_4`)) AS `Employer Address` from `healthdev`.`ENHR_TPR_DTL` `E` where ((`E`.`EMPLOYEE_SSN` is not null) and (date_format(str_to_date(`E`.`EMPLOYEE_DATE_OF_HIRE`,'%Y%m%d'),'%m/%d/%Y') >= ((select `V_AS_OF_DT`.`CODE_VALUE` from `ETL_USER_DEV`.`V_AS_OF_DT` where ((`V_AS_OF_DT`.`APPL_CD` = 'ENHR') and (`V_AS_OF_DT`.`CODE_NAME` = 'TPR_AS_OF_DT'))) + interval -(180) day))) union all select concat(`N`.`Employer_Name`,' - ',date_format(str_to_date(`N`.`Employee_Date_of_Hire`,'%Y%M%D'),'%M/%D/%Y')) AS `Employer - Employee Hire Date`,concat(`N`.`W_4_Employee_First_Name`,' ',`N`.`W_4_Employee_Middle_Name`,' ',`N`.`W_4_Employee_Last_Name`) AS `Name`,NULL AS `Birth Date`,concat('XXX-XX-',right(`N`.`SSN`,4)) AS `SSN`,concat_ws('\n',`N`.`W_4_Employee_Street__Address_Line_1`,`N`.`W_4_Employee_Street_Address_Line_2`,`N`.`W_4_Employee_Street_Address_Line_3`,concat(`N`.`W_4_Employee_City`,' ',`N`.`W_4_Employee_State`,' ',`N`.`W_4_Employee_Zip_Code`,'-',`N`.`W_4_Employee_Zip_Code_Extension`)) AS `Employee Address`,`N`.`Employer_Name` AS `Employer Name`,date_format(str_to_date(`N`.`Employee_Date_of_Hire`,'%Y%m%d'),'%m/%d/%Y') AS `Employee Hire Date`,`N`.`Federal_EIN` AS `FEIN`,concat_ws('\n',`N`.`Employer_Street_Address_Line_1`,`N`.`Employer_Street_Address_Line_2`,`N`.`Employer_Street_Address_Line_3`,concat(`N`.`Employer_City`,' ',`N`.`Employer_State`,' ',`N`.`Employer_Zip_Code`,'-',`N`.`Employer_Zip_Code_Extension`)) AS `Employer Address` from `healthdev`.`NHRD_TPR_IN_DTL_1` `N` where ((`N`.`SSN` is not null) and (date_format(str_to_date(`N`.`Employee_Date_of_Hire`,'%Y%m%d'),'%m/%d/%Y') >= ((select `V_AS_OF_DT`.`CODE_VALUE` from `ETL_USER_DEV`.`V_AS_OF_DT` where ((`V_AS_OF_DT`.`APPL_CD` = 'NHRD') and (`V_AS_OF_DT`.`CODE_NAME` = 'TPR_AS_OF_DT'))) + interval -(180) day))) */;
1658/*!50001 SET character_set_client = @saved_cs_client */;
1659/*!50001 SET character_set_results = @saved_cs_results */;
1660/*!50001 SET collation_connection = @saved_col_connection */;
1661
1662--
1663-- Final view structure for view `V_NDNH_DETAIL`
1664--
1665
1666/*!50001 DROP VIEW IF EXISTS `V_NDNH_DETAIL`*/;
1667/*!50001 SET @saved_cs_client = @@character_set_client */;
1668/*!50001 SET @saved_cs_results = @@character_set_results */;
1669/*!50001 SET @saved_col_connection = @@collation_connection */;
1670/*!50001 SET character_set_client = utf8 */;
1671/*!50001 SET character_set_results = utf8 */;
1672/*!50001 SET collation_connection = utf8_general_ci */;
1673/*!50001 CREATE ALGORITHM=UNDEFINED */
1674/*!50013 SQL SECURITY DEFINER */
1675/*!50001 VIEW `V_NDNH_DETAIL` AS select 'SNP' AS `SUBMITTER_IDENTIFIER`,'MCH' AS `RECORD_IDENTIFIER`,`IND`.`SSN` AS `SSN`,'N' AS `VERIFICATION_REQUEST_CODE`,ucase(`IND`.`FIRST_NAME`) AS `PERSON_FIRST_NAME`,ucase(`IND`.`MID_NAME`) AS `PERSON_MIDDLE_NAME`,ucase(`IND`.`LAST_NAME`) AS `PERSON_LAST_NAME`,`IND`.`INDV_ID` AS `PASSBACK_DATA`,'Y' AS `W_4_MATCH_INDICATOR`,'N' AS `W_4_SAME_STATE_DATA_INDICATOR`,(case when (`DI`.`DI_ACTION_AU_DT` between `DI`.`APPLICATION_DT` and (`DI`.`APPLICATION_DT` + interval 60 day)) then date_format((`DI`.`APPLICATION_DT` + interval 60 day),'%Y%m%d') else date_format((`DI`.`DI_ACTION_AU_DT` + interval -(90) day),'%Y%m%d') end) AS `W_4_FROM_DATE`,' ' AS `W_4_THROUGH_DATE`,'N' AS `QW_MATCH_CODE`,' ' AS `QW_SAME_STATE_DATA_INDICATOR`,' ' AS `QW_FROM_REPORTING_PERIOD`,' ' AS `QW_THROUGH_REPORTING_PERIOD`,' ' AS `QW_FROM_DATE`,' ' AS `QW_THROUGH_DATE`,'N' AS `UI_MATCH_CODE`,' ' AS `UI_SAME_STATE_DATA_INDICATOR`,' ' AS `UI_FROM_REPORTING_PERIOD`,' ' AS `UI_THROUGH_REPORTING_PERIOD`,' ' AS `UI_FROM_DATE`,' ' AS `UI_THROUGH_DATE`,' ' AS `FILLER` from ((`TIER_TPR_UNIV_DI` `DI` join `TIER_TPR_UNIV_INDV_ELIG` `ELG`) join `TIER_TPR_UNIV_INDV` `IND`) where ((`DI`.`CASE_NUM` = `ELG`.`CASE_NUM`) and (`DI`.`EDG_TRACE_ID` = `ELG`.`EDG_TRACE_ID`) and (`ELG`.`INDV_ID` = `IND`.`INDV_ID`) and (`DI`.`PROGRAM_CD` = 'FS') and (`ELG`.`ELIG_RSLT_CD` = 'PS') and (`DI`.`ACTIVITY_TYPE` in ('IN','PR')) and (floor(((to_days(curdate()) - to_days(`IND`.`DOB_DT`)) / 365)) >= 18) and (`DI`.`DI_ACTION_AU_DT` between (date_format(now(),'%Y-%m-21') - interval 1 month) and date_format(now(),'%Y-%m-20')) and (`ELG`.`ELIGIBILITY_END_DT` > last_day(now()))) */;
1676/*!50001 SET character_set_client = @saved_cs_client */;
1677/*!50001 SET character_set_results = @saved_cs_results */;
1678/*!50001 SET collation_connection = @saved_col_connection */;
1679
1680--
1681-- Final view structure for view `V_NDNH_HEADER`
1682--
1683
1684/*!50001 DROP VIEW IF EXISTS `V_NDNH_HEADER`*/;
1685/*!50001 SET @saved_cs_client = @@character_set_client */;
1686/*!50001 SET @saved_cs_results = @@character_set_results */;
1687/*!50001 SET @saved_col_connection = @@collation_connection */;
1688/*!50001 SET character_set_client = utf8 */;
1689/*!50001 SET character_set_results = utf8 */;
1690/*!50001 SET collation_connection = utf8_general_ci */;
1691/*!50001 CREATE ALGORITHM=UNDEFINED */
1692/*!50013 SQL SECURITY DEFINER */
1693/*!50001 VIEW `V_NDNH_HEADER` AS select 'SNP' AS `SUBMITTER_IDENTIFIER`,'HDR' AS `RECORD_IDENTIFIER`,'48' AS `SUBMITTING_STATE_CODE`,date_format(now(),'%Y%m%d') AS `DATE_STAMP`,' ' AS `FILLER1`,`BR`.`BATCH_RUN_ID` AS `BATCH_NUMBER`,' ' AS `FILLER2` from `ETL_USER_DEV`.`ETL_BATCH_RUN` `BR` where (`BR`.`APPL_PROC_ID` = 16) */;
1694/*!50001 SET character_set_client = @saved_cs_client */;
1695/*!50001 SET character_set_results = @saved_cs_results */;
1696/*!50001 SET collation_connection = @saved_col_connection */;
1697
1698--
1699-- Final view structure for view `V_NDNH_TRAILER`
1700--
1701
1702/*!50001 DROP VIEW IF EXISTS `V_NDNH_TRAILER`*/;
1703/*!50001 SET @saved_cs_client = @@character_set_client */;
1704/*!50001 SET @saved_cs_results = @@character_set_results */;
1705/*!50001 SET @saved_col_connection = @@collation_connection */;
1706/*!50001 SET character_set_client = utf8 */;
1707/*!50001 SET character_set_results = utf8 */;
1708/*!50001 SET collation_connection = utf8_general_ci */;
1709/*!50001 CREATE ALGORITHM=UNDEFINED */
1710/*!50013 SQL SECURITY DEFINER */
1711/*!50001 VIEW `V_NDNH_TRAILER` AS select 'SNP' AS `SUBMITTER_IDENTIFIER`,'TRL' AS `RECORD_IDENTIFIER`,((select count(1) from `V_NDNH_DETAIL`) + 2) AS `DATA_RECORD_COUNT`,' ' AS `FILLER` limit 1 */;
1712/*!50001 SET character_set_client = @saved_cs_client */;
1713/*!50001 SET character_set_results = @saved_cs_results */;
1714/*!50001 SET collation_connection = @saved_col_connection */;
1715/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
1716
1717/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
1718/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
1719/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
1720/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
1721/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
1722/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
1723/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
1724
1725-- Dump completed on 2016-10-31 14:39:13