-
Notifications
You must be signed in to change notification settings - Fork 1
Expand file tree
/
Copy pathmetersense-base.sql
More file actions
182 lines (175 loc) · 6.28 KB
/
Copy pathmetersense-base.sql
File metadata and controls
182 lines (175 loc) · 6.28 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
CREATE TABLE IF NOT EXISTS METERSENSE_ACCOUNT_SERVICES_BASE (
ORG_ID VARCHAR(16777216) NOT NULL,
CREATED_TIME TIMESTAMP_TZ(9) NOT NULL,
SERVICE_ID VARCHAR(16777216),
ACCOUNT_ID VARCHAR(16777216),
LOCATION_NO VARCHAR(16777216),
COMMODITY_TP VARCHAR(16777216),
LAST_READ_DT VARCHAR(16777216),
ACTIVE_DT VARCHAR(16777216),
INACTIVE_DT VARCHAR(16777216),
UNIQUE (ORG_ID, ACCOUNT_ID, LOCATION_NO, INACTIVE_DT)
);
CREATE TABLE IF NOT EXISTS METERSENSE_LOCATIONS_BASE (
ORG_ID VARCHAR(16777216) NOT NULL,
CREATED_TIME TIMESTAMP_TZ(9) NOT NULL,
LOCATION_NO VARCHAR(16777216),
ALT_LOCATION_ID VARCHAR(16777216),
LOCATION_CLASS VARCHAR(16777216),
UNIT_NO VARCHAR(16777216),
STREET_NO VARCHAR(16777216),
STREET_PFX VARCHAR(16777216),
STREET_NAME VARCHAR(16777216),
STREET_SFX VARCHAR(16777216),
STREET_SFX_DIR VARCHAR(16777216),
CITY VARCHAR(16777216),
STATE VARCHAR(16777216),
POSTAL_CD VARCHAR(16777216),
BILLING_CYCLE VARCHAR(16777216),
ADD_BY VARCHAR(16777216),
ADD_DT VARCHAR(16777216),
CHANGE_BY VARCHAR(16777216),
CHANGE_DT VARCHAR(16777216),
LATITUDE VARCHAR(16777216),
LONGITUDE VARCHAR(16777216),
UNIQUE (ORG_ID, LOCATION_NO)
);
CREATE TABLE IF NOT EXISTS METERSENSE_METERS_BASE (
ORG_ID VARCHAR(16777216) NOT NULL,
CREATED_TIME TIMESTAMP_TZ(9) NOT NULL,
METER_ID VARCHAR(16777216),
ALT_METER_ID VARCHAR(16777216),
METER_TP VARCHAR(16777216),
COMMODITY_TP VARCHAR(16777216),
REGION_ID VARCHAR(16777216),
INTERVAL_LENGTH VARCHAR(16777216),
REGREAD_FREQUENCY VARCHAR(16777216),
CHANNEL1_RAW_UOM VARCHAR(16777216),
CHANNEL2_RAW_UOM VARCHAR(16777216),
CHANNEL3_RAW_UOM VARCHAR(16777216),
CHANNEL4_RAW_UOM VARCHAR(16777216),
CHANNEL5_RAW_UOM VARCHAR(16777216),
CHANNEL6_RAW_UOM VARCHAR(16777216),
CHANNEL7_RAW_UOM VARCHAR(16777216),
CHANNEL8_RAW_UOM VARCHAR(16777216),
CHANNEL1_MULTIPLIER VARCHAR(16777216),
CHANNEL2_MULTIPLIER VARCHAR(16777216),
CHANNEL3_MULTIPLIER VARCHAR(16777216),
CHANNEL4_MULTIPLIER VARCHAR(16777216),
CHANNEL5_MULTIPLIER VARCHAR(16777216),
CHANNEL6_MULTIPLIER VARCHAR(16777216),
CHANNEL7_MULTIPLIER VARCHAR(16777216),
CHANNEL8_MULTIPLIER VARCHAR(16777216),
CHANNEL1_FINAL_UOM VARCHAR(16777216),
CHANNEL2_FINAL_UOM VARCHAR(16777216),
CHANNEL3_FINAL_UOM VARCHAR(16777216),
CHANNEL4_FINAL_UOM VARCHAR(16777216),
CHANNEL5_FINAL_UOM VARCHAR(16777216),
CHANNEL6_FINAL_UOM VARCHAR(16777216),
CHANNEL7_FINAL_UOM VARCHAR(16777216),
CHANNEL8_FINAL_UOM VARCHAR(16777216),
FIRST_DATA_TS VARCHAR(16777216),
LAST_DATA_TS VARCHAR(16777216),
AMI_ID VARCHAR(16777216),
POWER_STATUS VARCHAR(16777216),
LATITUDE VARCHAR(16777216),
LONGITUDE VARCHAR(16777216),
EXCLUDE_IN_REPORTS VARCHAR(16777216),
ADD_BY VARCHAR(16777216),
ADD_DT VARCHAR(16777216),
CHANGE_BY VARCHAR(16777216),
CHANGE_DT VARCHAR(16777216),
UNIQUE (ORG_ID, METER_ID)
);
CREATE TABLE IF NOT EXISTS METERSENSE_METERS_VIEW_BASE (
ORG_ID VARCHAR(16777216) NOT NULL,
CREATED_TIME TIMESTAMP_TZ(9) NOT NULL,
METER_ID VARCHAR(16777216),
ALT_METER_ID VARCHAR(16777216),
METER_TP VARCHAR(16777216),
COMMODITY_TP VARCHAR(16777216),
REGION_ID VARCHAR(16777216),
INTERVAL_LENGTH VARCHAR(16777216),
REGREAD_FREQUENCY VARCHAR(16777216),
CHANNEL1_RAW_UOM VARCHAR(16777216),
CHANNEL2_RAW_UOM VARCHAR(16777216),
CHANNEL3_RAW_UOM VARCHAR(16777216),
CHANNEL4_RAW_UOM VARCHAR(16777216),
CHANNEL5_RAW_UOM VARCHAR(16777216),
CHANNEL6_RAW_UOM VARCHAR(16777216),
CHANNEL7_RAW_UOM VARCHAR(16777216),
CHANNEL8_RAW_UOM VARCHAR(16777216),
CHANNEL1_MULTIPLIER VARCHAR(16777216),
CHANNEL2_MULTIPLIER VARCHAR(16777216),
CHANNEL3_MULTIPLIER VARCHAR(16777216),
CHANNEL4_MULTIPLIER VARCHAR(16777216),
CHANNEL5_MULTIPLIER VARCHAR(16777216),
CHANNEL6_MULTIPLIER VARCHAR(16777216),
CHANNEL7_MULTIPLIER VARCHAR(16777216),
CHANNEL8_MULTIPLIER VARCHAR(16777216),
CHANNEL1_FINAL_UOM VARCHAR(16777216),
CHANNEL2_FINAL_UOM VARCHAR(16777216),
CHANNEL3_FINAL_UOM VARCHAR(16777216),
CHANNEL4_FINAL_UOM VARCHAR(16777216),
CHANNEL5_FINAL_UOM VARCHAR(16777216),
CHANNEL6_FINAL_UOM VARCHAR(16777216),
CHANNEL7_FINAL_UOM VARCHAR(16777216),
CHANNEL8_FINAL_UOM VARCHAR(16777216),
FIRST_DATA_TS VARCHAR(16777216),
LAST_DATA_TS VARCHAR(16777216),
AMI_ID VARCHAR(16777216),
POWER_STATUS VARCHAR(16777216),
LATITUDE VARCHAR(16777216),
LONGITUDE VARCHAR(16777216),
EXCLUDE_IN_REPORTS VARCHAR(16777216),
NB_DIALS VARCHAR(16777216),
BACKFLOW VARCHAR(16777216),
SERVICE_POINT_TYPE VARCHAR(16777216),
RECLAIM_INTER_PROG VARCHAR(16777216),
POWER_STATUS_DETAILS VARCHAR(16777216),
COMM_MODULE_ID VARCHAR(16777216),
REGISTER_CONSTANT VARCHAR(16777216),
UNIQUE (ORG_ID, METER_ID)
);
CREATE TABLE IF NOT EXISTS METERSENSE_METER_LOCATION_XREF_BASE (
ORG_ID VARCHAR(16777216) NOT NULL,
CREATED_TIME TIMESTAMP_TZ(9) NOT NULL,
METER_ID VARCHAR(16777216),
ACTIVE_DT VARCHAR(16777216),
LOCATION_NO VARCHAR(16777216),
INACTIVE_DT VARCHAR(16777216),
ADD_BY VARCHAR(16777216),
ADD_DT VARCHAR(16777216),
CHANGE_BY VARCHAR(16777216),
CHANGE_DT VARCHAR(16777216),
UNIQUE (ORG_ID, METER_ID, INACTIVE_DT)
);
CREATE TABLE IF NOT EXISTS METERSENSE_INTERVALREADS_BASE (
ORG_ID VARCHAR(16777216) NOT NULL,
CREATED_TIME TIMESTAMP_TZ(9) NOT NULL,
METER_ID VARCHAR(16777216),
CHANNEL_ID VARCHAR(16777216),
READ_DT VARCHAR(16777216),
READ_HR VARCHAR(16777216),
READ_30MIN_INT VARCHAR(16777216),
READ_15MIN_INT VARCHAR(16777216),
READ_5MIN_INT VARCHAR(16777216),
READ_DTM VARCHAR(16777216),
READ_VALUE VARCHAR(16777216),
UOM VARCHAR(16777216),
STATUS VARCHAR(16777216),
READ_VERSION VARCHAR(16777216),
UNIQUE (ORG_ID, METER_ID, READ_DTM)
);
CREATE TABLE IF NOT EXISTS METERSENSE_REGISTERREADS_BASE (
ORG_ID VARCHAR(16777216) NOT NULL,
CREATED_TIME TIMESTAMP_TZ(9) NOT NULL,
METER_ID VARCHAR(16777216),
CHANNEL_ID VARCHAR(16777216),
READ_DTM VARCHAR(16777216),
READ_VALUE VARCHAR(16777216),
UOM VARCHAR(16777216),
STATUS VARCHAR(16777216),
READ_VERSION VARCHAR(16777216),
UNIQUE (ORG_ID, METER_ID, READ_DTM)
);