· 9 years ago · Oct 31, 2016, 06:24 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: ETL_USER_DEV
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-- Table structure for table `ETL_APPL`
20--
21
22DROP TABLE IF EXISTS `ETL_APPL`;
23/*!40101 SET @saved_cs_client = @@character_set_client */;
24/*!40101 SET character_set_client = utf8 */;
25CREATE TABLE `ETL_APPL` (
26 `ID` int(11) NOT NULL AUTO_INCREMENT,
27 `APPL_CD` varchar(4) NOT NULL,
28 `APPL_NAME` varchar(100) NOT NULL,
29 PRIMARY KEY (`ID`)
30) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=latin1;
31/*!40101 SET character_set_client = @saved_cs_client */;
32
33--
34-- Dumping data for table `ETL_APPL`
35--
36
37LOCK TABLES `ETL_APPL` WRITE;
38/*!40000 ALTER TABLE `ETL_APPL` DISABLE KEYS */;
39INSERT INTO `ETL_APPL` VALUES (1,'MEDI','MEDICAID'),(2,'DPSL','Drivers License and Previous Addresses'),(3,'TELD','Telephone Data'),(4,'NHRD','New Hire Report Data'),(5,'TIER','TIERS Daily Extract'),(6,'SNAP','SNAP-CAP Invitees'),(7,'TDCJ','Texas Departmemt Of Criminal Justice'),(8,'SSAD','SSA Death Master File'),(9,'LSBS','LSBS - Out of State TxEBT Shopping Data'),(10,'TEXC','Texas Counties Real Property Ownership Value'),(11,'DSHS','DSHS - Marriage and Divorce'),(12,'NARB','National Auto Research Black Book'),(13,'DMVR','DMV Vehicle Registrations'),(14,'ENHR','ENHR Texas Employer New Hire Report'),(15,'TDPS','Texas Department of Public Safety (DPS) Sex Offender Registry');
40/*!40000 ALTER TABLE `ETL_APPL` ENABLE KEYS */;
41UNLOCK TABLES;
42
43--
44-- Table structure for table `ETL_APPL_PROC`
45--
46
47DROP TABLE IF EXISTS `ETL_APPL_PROC`;
48/*!40101 SET @saved_cs_client = @@character_set_client */;
49/*!40101 SET character_set_client = utf8 */;
50CREATE TABLE `ETL_APPL_PROC` (
51 `ID` int(11) NOT NULL AUTO_INCREMENT,
52 `APPL_ID` int(11) NOT NULL,
53 `PROC_ID` int(11) NOT NULL,
54 PRIMARY KEY (`ID`)
55) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=latin1;
56/*!40101 SET character_set_client = @saved_cs_client */;
57
58--
59-- Dumping data for table `ETL_APPL_PROC`
60--
61
62LOCK TABLES `ETL_APPL_PROC` WRITE;
63/*!40000 ALTER TABLE `ETL_APPL_PROC` DISABLE KEYS */;
64INSERT INTO `ETL_APPL_PROC` VALUES (1,1,1),(2,2,1),(3,3,1),(4,4,1),(5,5,1),(6,6,1),(7,7,1),(8,8,1),(9,9,1),(10,10,1),(11,11,1),(12,12,1),(13,13,1),(14,14,1),(15,15,1),(16,4,3);
65/*!40000 ALTER TABLE `ETL_APPL_PROC` ENABLE KEYS */;
66UNLOCK TABLES;
67
68--
69-- Table structure for table `ETL_AUDIT`
70--
71
72DROP TABLE IF EXISTS `ETL_AUDIT`;
73/*!40101 SET @saved_cs_client = @@character_set_client */;
74/*!40101 SET character_set_client = utf8 */;
75CREATE TABLE `ETL_AUDIT` (
76 `AUDIT_ID` int(11) NOT NULL AUTO_INCREMENT,
77 `PROCESS_DT` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
78 `BATCH_RUN_ID` int(11) NOT NULL,
79 `SCHEMA_NAME` varchar(128) NOT NULL,
80 `TABLE_NAME` varchar(128) NOT NULL,
81 `EXPECTED_CNT` int(11) NOT NULL,
82 `ACTUAL_CNT` int(11) DEFAULT NULL,
83 `APPL_PROC_ID` int(11) NOT NULL,
84 `AUDIT_RESULT` int(1) DEFAULT NULL,
85 PRIMARY KEY (`AUDIT_ID`)
86) ENGINE=InnoDB DEFAULT CHARSET=latin1;
87/*!40101 SET character_set_client = @saved_cs_client */;
88
89--
90-- Dumping data for table `ETL_AUDIT`
91--
92
93LOCK TABLES `ETL_AUDIT` WRITE;
94/*!40000 ALTER TABLE `ETL_AUDIT` DISABLE KEYS */;
95/*!40000 ALTER TABLE `ETL_AUDIT` ENABLE KEYS */;
96UNLOCK TABLES;
97
98--
99-- Table structure for table `ETL_BATCH_RUN`
100--
101
102DROP TABLE IF EXISTS `ETL_BATCH_RUN`;
103/*!40101 SET @saved_cs_client = @@character_set_client */;
104/*!40101 SET character_set_client = utf8 */;
105CREATE TABLE `ETL_BATCH_RUN` (
106 `APPL_PROC_ID` int(11) NOT NULL,
107 `BATCH_RUN_ID` int(11) NOT NULL
108) ENGINE=InnoDB DEFAULT CHARSET=latin1;
109/*!40101 SET character_set_client = @saved_cs_client */;
110
111--
112-- Dumping data for table `ETL_BATCH_RUN`
113--
114
115LOCK TABLES `ETL_BATCH_RUN` WRITE;
116/*!40000 ALTER TABLE `ETL_BATCH_RUN` DISABLE KEYS */;
117INSERT INTO `ETL_BATCH_RUN` VALUES (1,151),(2,5),(4,28),(3,1),(5,443),(6,1),(7,16),(8,1),(9,1),(10,1),(11,1),(13,1),(12,1),(14,1),(15,64),(16,38);
118/*!40000 ALTER TABLE `ETL_BATCH_RUN` ENABLE KEYS */;
119UNLOCK TABLES;
120
121--
122-- Table structure for table `ETL_CODE`
123--
124
125DROP TABLE IF EXISTS `ETL_CODE`;
126/*!40101 SET @saved_cs_client = @@character_set_client */;
127/*!40101 SET character_set_client = utf8 */;
128CREATE TABLE `ETL_CODE` (
129 `ID` int(11) NOT NULL AUTO_INCREMENT,
130 `APPL_PROC_ID` int(11) DEFAULT NULL,
131 `CODE_NAME` varchar(100) DEFAULT NULL,
132 `CODE_VALUE` varchar(100) DEFAULT NULL,
133 PRIMARY KEY (`ID`)
134) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=latin1;
135/*!40101 SET character_set_client = @saved_cs_client */;
136
137--
138-- Dumping data for table `ETL_CODE`
139--
140
141LOCK TABLES `ETL_CODE` WRITE;
142/*!40000 ALTER TABLE `ETL_CODE` DISABLE KEYS */;
143INSERT INTO `ETL_CODE` VALUES (1,1,'TPR_AS_OF_DT','2016-03-04'),(2,2,'TPR_AS_OF_DT','2016-03-04'),(3,3,'TPR_AS_OF_DT','2016-09-12'),(4,4,'TPR_AS_OF_DT','2016-03-04'),(5,5,'TPR_AS_OF_DT','2016-03-04'),(6,6,'TPR_AS_OF_DT','2016-09-12'),(7,7,'TPR_AS_OF_DT','2016-03-04'),(8,8,'TPR_AS_OF_DT','2016-09-12'),(9,9,'TPR_AS_OF_DT','2016-09-12'),(10,10,'TPR_AS_OF_DT','2016-09-12'),(11,11,'TPR_AS_OF_DT','2016-09-12'),(12,12,'TPR_AS_OF_DT','2016-09-12'),(13,13,'TPR_AS_OF_DT','2016-09-12'),(14,14,'TPR_AS_OF_DT','2016-09-12'),(15,15,'TPR_AS_OF_DT','2016-03-04');
144/*!40000 ALTER TABLE `ETL_CODE` ENABLE KEYS */;
145UNLOCK TABLES;
146
147--
148-- Table structure for table `ETL_EXPECTED_TRENDS`
149--
150
151DROP TABLE IF EXISTS `ETL_EXPECTED_TRENDS`;
152/*!40101 SET @saved_cs_client = @@character_set_client */;
153/*!40101 SET character_set_client = utf8 */;
154CREATE TABLE `ETL_EXPECTED_TRENDS` (
155 `ID` int(11) NOT NULL AUTO_INCREMENT,
156 `SCHEMA_NAME` varchar(100) NOT NULL,
157 `TABLE_NAME` varchar(100) NOT NULL,
158 `MIN_NORMAL` int(11) DEFAULT NULL,
159 `MAX_NORMAL` int(11) DEFAULT NULL,
160 PRIMARY KEY (`ID`),
161 UNIQUE KEY `unique_table_schema` (`TABLE_NAME`,`SCHEMA_NAME`)
162) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=latin1;
163/*!40101 SET character_set_client = @saved_cs_client */;
164
165--
166-- Dumping data for table `ETL_EXPECTED_TRENDS`
167--
168
169LOCK TABLES `ETL_EXPECTED_TRENDS` WRITE;
170/*!40000 ALTER TABLE `ETL_EXPECTED_TRENDS` DISABLE KEYS */;
171INSERT INTO `ETL_EXPECTED_TRENDS` VALUES (1,'healthdev','NHRD_TPR_IN_DTL_1',50,1000),(3,'healthdev','NHRD_TPR_IN_DTL_2',50,1000),(4,'healthdev','MEDI_TPR_PIC_DTL',50,1000),(5,'healthdev','DPSL_TPR_DTL',50,1000),(6,'healthdev','ENHR_TPR_DTL',50,1000),(9,'healthdev','MEDI_TPR_REN_DTL',50,1000),(11,'healthdev','TDCJ_TPR_MASTER',50,1000),(12,'healthdev','TDCJ_TPR_TRANSFER',50,1000),(13,'healthdev','TDCJ_TPR_UNIT',50,1000),(14,'healthdev','TIER_STG_DC_CASES',50,1000),(15,'healthdev','TIER_STG_DC_CASE_PROGRAM',50,1000),(16,'healthdev','TIER_STG_DC_EMPLOYMENT',50,1000),(17,'healthdev','TIER_STG_DC_EMP_BUDGET',50,1000),(18,'healthdev','TIER_STG_UNIV_DI',50,1000),(19,'healthdev','TIER_STG_UNIV_INDV',50,1000),(20,'healthdev','TIER_STG_UNIV_INDV_ELIG',50,1000);
172/*!40000 ALTER TABLE `ETL_EXPECTED_TRENDS` ENABLE KEYS */;
173UNLOCK TABLES;
174
175--
176-- Table structure for table `ETL_LOG`
177--
178
179DROP TABLE IF EXISTS `ETL_LOG`;
180/*!40101 SET @saved_cs_client = @@character_set_client */;
181/*!40101 SET character_set_client = utf8 */;
182CREATE TABLE `ETL_LOG` (
183 `ID` int(11) NOT NULL AUTO_INCREMENT,
184 `LOG_CODE` varchar(100) DEFAULT NULL,
185 `LOG_DESCRIPTION` varchar(1000) DEFAULT NULL,
186 `INSERT_DT` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
187 PRIMARY KEY (`ID`)
188) ENGINE=InnoDB DEFAULT CHARSET=latin1;
189/*!40101 SET character_set_client = @saved_cs_client */;
190
191--
192-- Dumping data for table `ETL_LOG`
193--
194
195LOCK TABLES `ETL_LOG` WRITE;
196/*!40000 ALTER TABLE `ETL_LOG` DISABLE KEYS */;
197/*!40000 ALTER TABLE `ETL_LOG` ENABLE KEYS */;
198UNLOCK TABLES;
199
200--
201-- Table structure for table `ETL_OUTBND_FL_LST`
202--
203
204DROP TABLE IF EXISTS `ETL_OUTBND_FL_LST`;
205/*!40101 SET @saved_cs_client = @@character_set_client */;
206/*!40101 SET character_set_client = utf8 */;
207CREATE TABLE `ETL_OUTBND_FL_LST` (
208 `FILE_SRC_NM` varchar(75) NOT NULL,
209 `FILE_OWNER` varchar(30) DEFAULT NULL,
210 `FILE_DESC` varchar(55) DEFAULT NULL,
211 `FILE_FREQ` char(1) NOT NULL,
212 `FILE_DAY` varchar(4) DEFAULT NULL,
213 `FILE_SRC_LOC` varchar(55) DEFAULT NULL,
214 `FILE_TRGT_LOC` varchar(55) DEFAULT NULL,
215 `FILE_SRC` varchar(9) DEFAULT NULL,
216 `FILE_CPY_TYP` char(1) DEFAULT NULL,
217 `FILE_IP` varchar(55) DEFAULT NULL,
218 `USER_PWD` varchar(40) DEFAULT NULL,
219 `FILE_TRGT_NM` varchar(55) DEFAULT NULL,
220 `SERVER` varchar(3) DEFAULT NULL,
221 `FILE_EXEC` varchar(75) DEFAULT NULL,
222 `FILE_FREQ_PROD` char(1) DEFAULT NULL,
223 `FILE_PRIORITY` int(11) DEFAULT '99',
224 `FILE_PARM` varchar(1000) DEFAULT NULL,
225 `DATA_SOURCE` varchar(9) DEFAULT NULL,
226 `OVERRIDE_DATE` date DEFAULT NULL,
227 `DO_PORTAL_UPLOAD` char(1) DEFAULT NULL,
228 `PORTAL_LOC` varchar(500) DEFAULT NULL,
229 `DO_CMOD_UPLOAD` varchar(1) DEFAULT NULL,
230 `FILE_DL_CODE` char(1) DEFAULT NULL,
231 `FILE_DL_DATE` date DEFAULT NULL
232) ENGINE=InnoDB DEFAULT CHARSET=latin1;
233/*!40101 SET character_set_client = @saved_cs_client */;
234
235--
236-- Dumping data for table `ETL_OUTBND_FL_LST`
237--
238
239LOCK TABLES `ETL_OUTBND_FL_LST` WRITE;
240/*!40000 ALTER TABLE `ETL_OUTBND_FL_LST` DISABLE KEYS */;
241INSERT INTO `ETL_OUTBND_FL_LST` VALUES ('T_DB_UNIV_DI-20160304.csv',NULL,NULL,'D',NULL,'S3: ARKA PCG','PCGDEV.TIER_TPR_UNIV_DI','TIER',NULL,NULL,NULL,NULL,NULL,NULL,NULL,99,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
242/*!40000 ALTER TABLE `ETL_OUTBND_FL_LST` ENABLE KEYS */;
243UNLOCK TABLES;
244
245--
246-- Table structure for table `ETL_PROC`
247--
248
249DROP TABLE IF EXISTS `ETL_PROC`;
250/*!40101 SET @saved_cs_client = @@character_set_client */;
251/*!40101 SET character_set_client = utf8 */;
252CREATE TABLE `ETL_PROC` (
253 `ID` int(11) NOT NULL AUTO_INCREMENT,
254 `PROC_CD` varchar(25) DEFAULT NULL,
255 `PROC_NAME` varchar(100) DEFAULT NULL,
256 PRIMARY KEY (`ID`)
257) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
258/*!40101 SET character_set_client = @saved_cs_client */;
259
260--
261-- Dumping data for table `ETL_PROC`
262--
263
264LOCK TABLES `ETL_PROC` WRITE;
265/*!40000 ALTER TABLE `ETL_PROC` DISABLE KEYS */;
266INSERT INTO `ETL_PROC` VALUES (1,'SRC_TO_TPR','Source to TPR'),(3,'TPR_TO_S3','TPR to S3 file');
267/*!40000 ALTER TABLE `ETL_PROC` ENABLE KEYS */;
268UNLOCK TABLES;
269
270--
271-- Temporary view structure for view `V_APPL_PROC`
272--
273
274DROP TABLE IF EXISTS `V_APPL_PROC`;
275/*!50001 DROP VIEW IF EXISTS `V_APPL_PROC`*/;
276SET @saved_cs_client = @@character_set_client;
277SET character_set_client = utf8;
278/*!50001 CREATE VIEW `V_APPL_PROC` AS SELECT
279 1 AS `APPL_PROC_ID`,
280 1 AS `APPL_ID`,
281 1 AS `PROC_ID`,
282 1 AS `APPL_CD`,
283 1 AS `APPL_NAME`,
284 1 AS `PROC_CD`,
285 1 AS `PROC_NAME`*/;
286SET character_set_client = @saved_cs_client;
287
288--
289-- Temporary view structure for view `V_AS_OF_DT`
290--
291
292DROP TABLE IF EXISTS `V_AS_OF_DT`;
293/*!50001 DROP VIEW IF EXISTS `V_AS_OF_DT`*/;
294SET @saved_cs_client = @@character_set_client;
295SET character_set_client = utf8;
296/*!50001 CREATE VIEW `V_AS_OF_DT` AS SELECT
297 1 AS `ID`,
298 1 AS `APPL_CD`,
299 1 AS `PROC_NAME`,
300 1 AS `CODE_NAME`,
301 1 AS `CODE_VALUE`*/;
302SET character_set_client = @saved_cs_client;
303
304--
305-- Temporary view structure for view `V_ETL_CODE`
306--
307
308DROP TABLE IF EXISTS `V_ETL_CODE`;
309/*!50001 DROP VIEW IF EXISTS `V_ETL_CODE`*/;
310SET @saved_cs_client = @@character_set_client;
311SET character_set_client = utf8;
312/*!50001 CREATE VIEW `V_ETL_CODE` AS SELECT
313 1 AS `ID`,
314 1 AS `APPL_CD`,
315 1 AS `PROC_NAME`,
316 1 AS `CODE_NM`,
317 1 AS `CODE_VALUE`*/;
318SET character_set_client = @saved_cs_client;
319
320--
321-- Dumping routines for database 'ETL_USER_DEV'
322--
323/*!50003 DROP PROCEDURE IF EXISTS `AUDIT_PROC` */;
324/*!50003 SET @saved_cs_client = @@character_set_client */ ;
325/*!50003 SET @saved_cs_results = @@character_set_results */ ;
326/*!50003 SET @saved_col_connection = @@collation_connection */ ;
327/*!50003 SET character_set_client = utf8 */ ;
328/*!50003 SET character_set_results = utf8 */ ;
329/*!50003 SET collation_connection = utf8_general_ci */ ;
330/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
331/*!50003 SET sql_mode = 'STRICT_TRANS_TABLES' */ ;
332DELIMITER ;;
333CREATE DEFINER=`PCGROOT`@`%` PROCEDURE `AUDIT_PROC`(IN batchRunId INT(11), IN schemaName VARCHAR(128), IN tableName VARCHAR(128), IN expected INT(11), IN applProcId INT(11), OUT auditId INT(11), OUT auditResult INT(11))
334BEGIN
335 SET @actualCount = 0;
336 SET @dynamicCount=CONCAT('SELECT COUNT(*) INTO @actualCount FROM ', schemaName,'.',tableName, ' WHERE TPR_BATCH_RUN_ID=',batchRunId);
337 PREPARE dynamicCount from @dynamicCount;
338 EXECUTE dynamicCount;
339 SET @pass=0;
340 IF @actualCount = expected THEN SET @pass=1;
341 END IF;
342 INSERT INTO ETL_AUDIT
343 (BATCH_RUN_ID, SCHEMA_NAME, TABLE_NAME, EXPECTED_CNT,ACTUAL_CNT, APPL_PROC_ID, AUDIT_RESULT) VALUES
344 (batchRunId, schemaName, tableName, expected, @actualCount, applProcId, @pass);
345 SET @auditId=LAST_INSERT_ID();
346 SET @auditResult=@pass;
347 SELECT @auditId,@auditResult;
348END ;;
349DELIMITER ;
350/*!50003 SET sql_mode = @saved_sql_mode */ ;
351/*!50003 SET character_set_client = @saved_cs_client */ ;
352/*!50003 SET character_set_results = @saved_cs_results */ ;
353/*!50003 SET collation_connection = @saved_col_connection */ ;
354/*!50003 DROP PROCEDURE IF EXISTS `DELTA_PROC` */;
355/*!50003 SET @saved_cs_client = @@character_set_client */ ;
356/*!50003 SET @saved_cs_results = @@character_set_results */ ;
357/*!50003 SET @saved_col_connection = @@collation_connection */ ;
358/*!50003 SET character_set_client = utf8 */ ;
359/*!50003 SET character_set_results = utf8 */ ;
360/*!50003 SET collation_connection = utf8_general_ci */ ;
361/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
362/*!50003 SET sql_mode = 'STRICT_TRANS_TABLES' */ ;
363DELIMITER ;;
364CREATE DEFINER=`PCGROOT`@`%` PROCEDURE `DELTA_PROC`(IN stgSchema VARCHAR(128), IN stgTable VARCHAR(128),IN tprSchema VARCHAR(128), IN tprTable VARCHAR(128))
365BEGIN
366 SET @columns ='';
367 SET @tableKeys = '';
368 SELECT GROUP_CONCAT(COLUMN_NAME SEPARATOR ', ') INTO @columns FROM information_schema.COLUMNS WHERE table_schema='PCGDEV' AND table_name='TIER_STG_UNIV_INDV_ELIG' AND column_name != "TPR_AS_OF_DT";
369 SELECT GROUP_CONCAT(column_name SEPARATOR ',') INTO @tableKeys FROM information_schema.statistics WHERE table_schema='PCGDEV' AND table_name='TIER_TPR_UNIV_INDV_ELIG' AND index_name='PRIMARY' AND column_name != 'TPR_AS_OF_DT' ORDER BY seq_in_index;
370 SET @dynamicDelta = '';
371 SELECT CONCAT_WS('', 'INSERT INTO ',tprSchema,'.',tprTable,' SELECT (SELECT MAX(TPR_AS_OF_DT) FROM ',stgSchema,'.',stgTable, ') as TPR_AS_OF_DT,',@columns, ' FROM ', stgSchema,'.',stgTable, ' GROUP BY ', @tableKeys) into @dynamicDelta;
372 PREPARE dynamicDelta from @dynamicDelta;
373 EXECUTE dynamicDelta;
374END ;;
375DELIMITER ;
376/*!50003 SET sql_mode = @saved_sql_mode */ ;
377/*!50003 SET character_set_client = @saved_cs_client */ ;
378/*!50003 SET character_set_results = @saved_cs_results */ ;
379/*!50003 SET collation_connection = @saved_col_connection */ ;
380
381--
382-- Final view structure for view `V_APPL_PROC`
383--
384
385/*!50001 DROP VIEW IF EXISTS `V_APPL_PROC`*/;
386/*!50001 SET @saved_cs_client = @@character_set_client */;
387/*!50001 SET @saved_cs_results = @@character_set_results */;
388/*!50001 SET @saved_col_connection = @@collation_connection */;
389/*!50001 SET character_set_client = utf8 */;
390/*!50001 SET character_set_results = utf8 */;
391/*!50001 SET collation_connection = utf8_general_ci */;
392/*!50001 CREATE ALGORITHM=UNDEFINED */
393/*!50013 DEFINER=`PCGROOT`@`%` SQL SECURITY DEFINER */
394/*!50001 VIEW `V_APPL_PROC` AS (select `EAP`.`ID` AS `APPL_PROC_ID`,`EAP`.`APPL_ID` AS `APPL_ID`,`EAP`.`PROC_ID` AS `PROC_ID`,`EA`.`APPL_CD` AS `APPL_CD`,`EA`.`APPL_NAME` AS `APPL_NAME`,`EP`.`PROC_CD` AS `PROC_CD`,`EP`.`PROC_NAME` AS `PROC_NAME` from ((`ETL_APPL_PROC` `EAP` join `ETL_APPL` `EA` on((`EAP`.`APPL_ID` = `EA`.`ID`))) join `ETL_PROC` `EP` on((`EAP`.`PROC_ID` = `EP`.`ID`)))) */;
395/*!50001 SET character_set_client = @saved_cs_client */;
396/*!50001 SET character_set_results = @saved_cs_results */;
397/*!50001 SET collation_connection = @saved_col_connection */;
398
399--
400-- Final view structure for view `V_AS_OF_DT`
401--
402
403/*!50001 DROP VIEW IF EXISTS `V_AS_OF_DT`*/;
404/*!50001 SET @saved_cs_client = @@character_set_client */;
405/*!50001 SET @saved_cs_results = @@character_set_results */;
406/*!50001 SET @saved_col_connection = @@collation_connection */;
407/*!50001 SET character_set_client = utf8 */;
408/*!50001 SET character_set_results = utf8 */;
409/*!50001 SET collation_connection = utf8_general_ci */;
410/*!50001 CREATE ALGORITHM=UNDEFINED */
411/*!50013 DEFINER=`PCGROOT`@`%` SQL SECURITY DEFINER */
412/*!50001 VIEW `V_AS_OF_DT` AS select `ETL_APPL_PROC`.`ID` AS `ID`,`ETL_APPL`.`APPL_CD` AS `APPL_CD`,`ETL_PROC`.`PROC_NAME` AS `PROC_NAME`,`ETL_CODE`.`CODE_NAME` AS `CODE_NAME`,cast(`ETL_CODE`.`CODE_VALUE` as date) AS `CODE_VALUE` from (((`ETL_APPL` join `ETL_APPL_PROC` on((`ETL_APPL`.`ID` = `ETL_APPL_PROC`.`APPL_ID`))) join `ETL_CODE` on((`ETL_APPL_PROC`.`ID` = `ETL_CODE`.`APPL_PROC_ID`))) join `ETL_PROC` on((`ETL_APPL_PROC`.`PROC_ID` = `ETL_PROC`.`ID`))) where ((`ETL_CODE`.`CODE_NAME` = 'TPR_AS_OF_DT') and (`ETL_PROC`.`PROC_NAME` = 'Source to TPR')) */;
413/*!50001 SET character_set_client = @saved_cs_client */;
414/*!50001 SET character_set_results = @saved_cs_results */;
415/*!50001 SET collation_connection = @saved_col_connection */;
416
417--
418-- Final view structure for view `V_ETL_CODE`
419--
420
421/*!50001 DROP VIEW IF EXISTS `V_ETL_CODE`*/;
422/*!50001 SET @saved_cs_client = @@character_set_client */;
423/*!50001 SET @saved_cs_results = @@character_set_results */;
424/*!50001 SET @saved_col_connection = @@collation_connection */;
425/*!50001 SET character_set_client = utf8 */;
426/*!50001 SET character_set_results = utf8 */;
427/*!50001 SET collation_connection = utf8_general_ci */;
428/*!50001 CREATE ALGORITHM=UNDEFINED */
429/*!50013 DEFINER=`PCGROOT`@`%` SQL SECURITY DEFINER */
430/*!50001 VIEW `V_ETL_CODE` AS select `ETL_APPL_PROC`.`ID` AS `ID`,`ETL_APPL`.`APPL_CD` AS `APPL_CD`,`ETL_PROC`.`PROC_NAME` AS `PROC_NAME`,`ETL_CODE`.`CODE_NAME` AS `CODE_NM`,`ETL_CODE`.`CODE_VALUE` AS `CODE_VALUE` from (((`ETL_APPL` join `ETL_APPL_PROC` on((`ETL_APPL`.`ID` = `ETL_APPL_PROC`.`APPL_ID`))) join `ETL_CODE` on((`ETL_APPL_PROC`.`ID` = `ETL_CODE`.`APPL_PROC_ID`))) join `ETL_PROC` on((`ETL_APPL_PROC`.`PROC_ID` = `ETL_PROC`.`ID`))) */;
431/*!50001 SET character_set_client = @saved_cs_client */;
432/*!50001 SET character_set_results = @saved_cs_results */;
433/*!50001 SET collation_connection = @saved_col_connection */;
434/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
435
436/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
437/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
438/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
439/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
440/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
441/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
442/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
443
444-- Dump completed on 2016-10-31 14:20:38