CREATE TABLE EXTRACT_TABLE_COUNTS ( RECORD_COUNT NUMBER(10), TABLE_NAME VARCHAR2(70) ); COMMENT ON TABLE EXTRACT_TABLE_COUNTS IS 'This table contains the record counts and filenames in the compressed file downloaded from ERCOT.'; COMMENT ON COLUMN EXTRACT_TABLE_COUNTS.RECORD_COUNT is 'The record count for the associated table.'; COMMENT ON COLUMN EXTRACT_TABLE_COUNTS.TABLE_NAME is 'The ESI ID file level: DUNS Number-Table Name-Run Date.csv. The dimensional file level: Table Name - Run Date.csv'; CREATE TABLE AMSINTERVAL ( UIDAMSINTERVAL NUMBER(19) not null, UIDESIID NUMBER(19) not null, UIDAMSDATATYPE NUMBER(19) not null, STARTTIME DATE not null, STOPTIME DATE not null, PROXYDAY VARCHAR2(8) , SPI NUMBER(10) not null, UOMCODE VARCHAR2(64) not null, DSTPARTICIPANT CHAR(1) not null, ORIGIN CHAR(1) , METERREADTS DATE , TZSTDNAME VARCHAR2(32) , TOTAL FLOAT(52) not null, MAXIMUM FLOAT(52) not null, MINIMUM FLOAT(52) not null, INTERVALCOUNT NUMBER(10) not null, LSTIME DATE DEFAULT SYSDATE, INT001 NUMBER , INT002 NUMBER , INT003 NUMBER , INT004 NUMBER , INT005 NUMBER , INT006 NUMBER , INT007 NUMBER , INT008 NUMBER , INT009 NUMBER , INT010 NUMBER , INT011 NUMBER , INT012 NUMBER , INT013 NUMBER , INT014 NUMBER , INT015 NUMBER , INT016 NUMBER , INT017 NUMBER , INT018 NUMBER , INT019 NUMBER , INT020 NUMBER , INT021 NUMBER , INT022 NUMBER , INT023 NUMBER , INT024 NUMBER , INT025 NUMBER , INT026 NUMBER , INT027 NUMBER , INT028 NUMBER , INT029 NUMBER , INT030 NUMBER , INT031 NUMBER , INT032 NUMBER , INT033 NUMBER , INT034 NUMBER , INT035 NUMBER , INT036 NUMBER , INT037 NUMBER , INT038 NUMBER , INT039 NUMBER , INT040 NUMBER , INT041 NUMBER , INT042 NUMBER , INT043 NUMBER , INT044 NUMBER , INT045 NUMBER , INT046 NUMBER , INT047 NUMBER , INT048 NUMBER , INT049 NUMBER , INT050 NUMBER , INT051 NUMBER , INT052 NUMBER , INT053 NUMBER , INT054 NUMBER , INT055 NUMBER , INT056 NUMBER , INT057 NUMBER , INT058 NUMBER , INT059 NUMBER , INT060 NUMBER , INT061 NUMBER , INT062 NUMBER , INT063 NUMBER , INT064 NUMBER , INT065 NUMBER , INT066 NUMBER , INT067 NUMBER , INT068 NUMBER , INT069 NUMBER , INT070 NUMBER , INT071 NUMBER , INT072 NUMBER , INT073 NUMBER , INT074 NUMBER , INT075 NUMBER , INT076 NUMBER , INT077 NUMBER , INT078 NUMBER , INT079 NUMBER , INT080 NUMBER , INT081 NUMBER , INT082 NUMBER , INT083 NUMBER , INT084 NUMBER , INT085 NUMBER , INT086 NUMBER , INT087 NUMBER , INT088 NUMBER , INT089 NUMBER , INT090 NUMBER , INT091 NUMBER , INT092 NUMBER , INT093 NUMBER , INT094 NUMBER , INT095 NUMBER , INT096 NUMBER , INT097 NUMBER , INT098 NUMBER , INT099 NUMBER , INT100 NUMBER ); CREATE UNIQUE INDEX UK_AMSINT_ID ON AMSINTERVAL (UIDESIID, UIDAMSDATATYPE, STARTTIME, STOPTIME); COMMENT ON TABLE AMSINTERVAL IS 'This table is a new lodestar enhanced interval data table and will be the permanent location of AMS IDR data.'; COMMENT ON COLUMN AMSINTERVAL.UIDAMSINTERVAL is 'The ERCOT Lodestar unique identifier for this cut of data'; COMMENT ON COLUMN AMSINTERVAL.UIDESIID is 'The ERCOT Lodestar unique numeric identifier for the ESIID.'; COMMENT ON COLUMN AMSINTERVAL.UIDAMSDATATYPE is 'The Lodestar AMS channel equivalent ERCOT uses for interval data storage (1=generation kWh, 4=load kWh).'; COMMENT ON COLUMN AMSINTERVAL.STARTTIME is 'The start time of the interval meter read'; COMMENT ON COLUMN AMSINTERVAL.STOPTIME is 'The stop time of the interval meter read'; COMMENT ON COLUMN AMSINTERVAL.PROXYDAY is 'The proxy date populated for a given operating day of calculated load by data aggregation, ORIGIN = 'C'. Null if load not calculated, ORIGIN = M'; COMMENT ON COLUMN AMSINTERVAL.SPI is 'The number of seconds per interval (SPI) for the interval data cut (I.E. 15 MINUTES = 900, 1 HOUR = 3600).'; COMMENT ON COLUMN AMSINTERVAL.UOMCODE is 'The Lodestar defined numerical code to indicate the Unit Of Measure of the interval data (01=kWh.'; COMMENT ON COLUMN AMSINTERVAL.DSTPARTICIPANT is 'This flag indicates if the data participates in DST adjustments (always = Y).'; COMMENT ON COLUMN AMSINTERVAL.ORIGIN is 'A variable that represents the origin of the interval cut: M = Metered or C = Calculated'; COMMENT ON COLUMN AMSINTERVAL.METERREADTS is 'This is the actual meter read date provided in the AMS LSE file'; COMMENT ON COLUMN AMSINTERVAL.TZSTDNAME is 'This column is for the time zone standard name which will CPT(Central Prevailing Time)'; COMMENT ON COLUMN AMSINTERVAL.TOTAL is 'Total of all KWH intervals'; COMMENT ON COLUMN AMSINTERVAL.MAXIMUM is 'Displays the maximum value of all intervals.'; COMMENT ON COLUMN AMSINTERVAL.MINIMUM is 'Displays the minimum value of all intervals.'; COMMENT ON COLUMN AMSINTERVAL.INTERVALCOUNT is 'Count of intervals within the AMSINTERVAL.STARTTIME and AMSINTERVAL.STOPTIME.'; COMMENT ON COLUMN AMSINTERVAL.LSTIME is 'The data record timestamp.'; COMMENT ON COLUMN AMSINTERVAL.INT001 is 'The parsed value for the first interval.'; COMMENT ON COLUMN AMSINTERVAL.INT002 is 'The parsed value for the second interval.'; COMMENT ON COLUMN AMSINTERVAL.INT003 is 'The parsed value for the third interval.'; COMMENT ON COLUMN AMSINTERVAL.INT004 is 'The parsed value for the fourth interval.'; COMMENT ON COLUMN AMSINTERVAL.INT005 is 'The parsed value for the fifth interval.'; COMMENT ON COLUMN AMSINTERVAL.INT006 is 'The parsed value for the 6th interval.'; COMMENT ON COLUMN AMSINTERVAL.INT007 is 'The parsed value for the 7th interval.'; COMMENT ON COLUMN AMSINTERVAL.INT008 is 'The parsed value for the 8th interval.'; COMMENT ON COLUMN AMSINTERVAL.INT009 is 'The parsed value for the 9th interval.'; COMMENT ON COLUMN AMSINTERVAL.INT010 is 'The parsed value for the 10th interval.'; COMMENT ON COLUMN AMSINTERVAL.INT011 is 'The parsed value for the 11th interval.'; COMMENT ON COLUMN AMSINTERVAL.INT012 is 'The parsed value for the 12th interval.'; COMMENT ON COLUMN AMSINTERVAL.INT013 is 'The parsed value for the 13th interval.'; COMMENT ON COLUMN AMSINTERVAL.INT014 is 'The parsed value for the 14th interval.'; COMMENT ON COLUMN AMSINTERVAL.INT015 is 'The parsed value for the 15th interval.'; COMMENT ON COLUMN AMSINTERVAL.INT016 is 'The parsed value for the 16th interval.'; COMMENT ON COLUMN AMSINTERVAL.INT017 is 'The parsed value for the 17th interval.'; COMMENT ON COLUMN AMSINTERVAL.INT018 is 'The parsed value for the 18th interval.'; COMMENT ON COLUMN AMSINTERVAL.INT019 is 'The parsed value for the 19th interval.'; COMMENT ON COLUMN AMSINTERVAL.INT020 is 'The parsed value for the 20th interval.'; COMMENT ON COLUMN AMSINTERVAL.INT021 is 'The parsed value for the 21th interval.'; COMMENT ON COLUMN AMSINTERVAL.INT022 is 'The parsed value for the 22th interval.'; COMMENT ON COLUMN AMSINTERVAL.INT023 is 'The parsed value for the 23th interval.'; COMMENT ON COLUMN AMSINTERVAL.INT024 is 'The parsed value for the 24th interval.'; COMMENT ON COLUMN AMSINTERVAL.INT025 is 'The parsed value for the 25th interval.'; COMMENT ON COLUMN AMSINTERVAL.INT026 is 'The parsed value for the 26th interval.'; COMMENT ON COLUMN AMSINTERVAL.INT027 is 'The parsed value for the 27th interval.'; COMMENT ON COLUMN AMSINTERVAL.INT028 is 'The parsed value for the 28th interval.'; COMMENT ON COLUMN AMSINTERVAL.INT029 is 'The parsed value for the 29th interval.'; COMMENT ON COLUMN AMSINTERVAL.INT030 is 'The parsed value for the 30th interval.'; COMMENT ON COLUMN AMSINTERVAL.INT031 is 'The parsed value for the 31th interval.'; COMMENT ON COLUMN AMSINTERVAL.INT032 is 'The parsed value for the 32th interval.'; COMMENT ON COLUMN AMSINTERVAL.INT033 is 'The parsed value for the 33th interval.'; COMMENT ON COLUMN AMSINTERVAL.INT034 is 'The parsed value for the 34th interval.'; COMMENT ON COLUMN AMSINTERVAL.INT035 is 'The parsed value for the 35th interval.'; COMMENT ON COLUMN AMSINTERVAL.INT036 is 'The parsed value for the 36th interval.'; COMMENT ON COLUMN AMSINTERVAL.INT037 is 'The parsed value for the 37th interval.'; COMMENT ON COLUMN AMSINTERVAL.INT038 is 'The parsed value for the 38th interval.'; COMMENT ON COLUMN AMSINTERVAL.INT039 is 'The parsed value for the 39th interval.'; COMMENT ON COLUMN AMSINTERVAL.INT040 is 'The parsed value for the 40th interval.'; COMMENT ON COLUMN AMSINTERVAL.INT041 is 'The parsed value for the 41th interval.'; COMMENT ON COLUMN AMSINTERVAL.INT042 is 'The parsed value for the 42th interval.'; COMMENT ON COLUMN AMSINTERVAL.INT043 is 'The parsed value for the 43th interval.'; COMMENT ON COLUMN AMSINTERVAL.INT044 is 'The parsed value for the 44th interval.'; COMMENT ON COLUMN AMSINTERVAL.INT045 is 'The parsed value for the 45th interval.'; COMMENT ON COLUMN AMSINTERVAL.INT046 is 'The parsed value for the 46th interval.'; COMMENT ON COLUMN AMSINTERVAL.INT047 is 'The parsed value for the 47th interval.'; COMMENT ON COLUMN AMSINTERVAL.INT048 is 'The parsed value for the 48th interval.'; COMMENT ON COLUMN AMSINTERVAL.INT049 is 'The parsed value for the 49th interval.'; COMMENT ON COLUMN AMSINTERVAL.INT050 is 'The parsed value for the 50th interval.'; COMMENT ON COLUMN AMSINTERVAL.INT051 is 'The parsed value for the 51th interval.'; COMMENT ON COLUMN AMSINTERVAL.INT052 is 'The parsed value for the 52th interval.'; COMMENT ON COLUMN AMSINTERVAL.INT053 is 'The parsed value for the 53th interval.'; COMMENT ON COLUMN AMSINTERVAL.INT054 is 'The parsed value for the 54th interval.'; COMMENT ON COLUMN AMSINTERVAL.INT055 is 'The parsed value for the 55th interval.'; COMMENT ON COLUMN AMSINTERVAL.INT056 is 'The parsed value for the 56th interval.'; COMMENT ON COLUMN AMSINTERVAL.INT057 is 'The parsed value for the 57th interval.'; COMMENT ON COLUMN AMSINTERVAL.INT058 is 'The parsed value for the 58th interval.'; COMMENT ON COLUMN AMSINTERVAL.INT059 is 'The parsed value for the 59th interval.'; COMMENT ON COLUMN AMSINTERVAL.INT060 is 'The parsed value for the 60th interval.'; COMMENT ON COLUMN AMSINTERVAL.INT061 is 'The parsed value for the 61th interval.'; COMMENT ON COLUMN AMSINTERVAL.INT062 is 'The parsed value for the 62th interval.'; COMMENT ON COLUMN AMSINTERVAL.INT063 is 'The parsed value for the 63th interval.'; COMMENT ON COLUMN AMSINTERVAL.INT064 is 'The parsed value for the 64th interval.'; COMMENT ON COLUMN AMSINTERVAL.INT065 is 'The parsed value for the 65th interval.'; COMMENT ON COLUMN AMSINTERVAL.INT066 is 'The parsed value for the 66th interval.'; COMMENT ON COLUMN AMSINTERVAL.INT067 is 'The parsed value for the 67th interval.'; COMMENT ON COLUMN AMSINTERVAL.INT068 is 'The parsed value for the 68th interval.'; COMMENT ON COLUMN AMSINTERVAL.INT069 is 'The parsed value for the 69th interval.'; COMMENT ON COLUMN AMSINTERVAL.INT070 is 'The parsed value for the 70th interval.'; COMMENT ON COLUMN AMSINTERVAL.INT071 is 'The parsed value for the 71th interval.'; COMMENT ON COLUMN AMSINTERVAL.INT072 is 'The parsed value for the 72th interval.'; COMMENT ON COLUMN AMSINTERVAL.INT073 is 'The parsed value for the 73th interval.'; COMMENT ON COLUMN AMSINTERVAL.INT074 is 'The parsed value for the 74th interval.'; COMMENT ON COLUMN AMSINTERVAL.INT075 is 'The parsed value for the 75th interval.'; COMMENT ON COLUMN AMSINTERVAL.INT076 is 'The parsed value for the 76th interval.'; COMMENT ON COLUMN AMSINTERVAL.INT077 is 'The parsed value for the 77th interval.'; COMMENT ON COLUMN AMSINTERVAL.INT078 is 'The parsed value for the 78th interval.'; COMMENT ON COLUMN AMSINTERVAL.INT079 is 'The parsed value for the 79th interval.'; COMMENT ON COLUMN AMSINTERVAL.INT080 is 'The parsed value for the 80th interval.'; COMMENT ON COLUMN AMSINTERVAL.INT081 is 'The parsed value for the 81th interval.'; COMMENT ON COLUMN AMSINTERVAL.INT082 is 'The parsed value for the 82th interval.'; COMMENT ON COLUMN AMSINTERVAL.INT083 is 'The parsed value for the 83th interval.'; COMMENT ON COLUMN AMSINTERVAL.INT084 is 'The parsed value for the 84th interval.'; COMMENT ON COLUMN AMSINTERVAL.INT085 is 'The parsed value for the 85th interval.'; COMMENT ON COLUMN AMSINTERVAL.INT086 is 'The parsed value for the 86th interval.'; COMMENT ON COLUMN AMSINTERVAL.INT087 is 'The parsed value for the 87th interval.'; COMMENT ON COLUMN AMSINTERVAL.INT088 is 'The parsed value for the 88th interval.'; COMMENT ON COLUMN AMSINTERVAL.INT089 is 'The parsed value for the 89th interval.'; COMMENT ON COLUMN AMSINTERVAL.INT090 is 'The parsed value for the 90th interval.'; COMMENT ON COLUMN AMSINTERVAL.INT091 is 'The parsed value for the 91th interval.'; COMMENT ON COLUMN AMSINTERVAL.INT092 is 'The parsed value for the 92th interval.'; COMMENT ON COLUMN AMSINTERVAL.INT093 is 'The parsed value for the 93th interval.'; COMMENT ON COLUMN AMSINTERVAL.INT094 is 'The parsed value for the 94th interval.'; COMMENT ON COLUMN AMSINTERVAL.INT095 is 'The parsed value for the 95th interval.'; COMMENT ON COLUMN AMSINTERVAL.INT096 is 'The parsed value for the 96th interval.'; COMMENT ON COLUMN AMSINTERVAL.INT097 is 'The parsed value for the 97th interval.'; COMMENT ON COLUMN AMSINTERVAL.INT098 is 'The parsed value for the 98th interval.'; COMMENT ON COLUMN AMSINTERVAL.INT099 is 'The parsed value for the 99th interval.'; COMMENT ON COLUMN AMSINTERVAL.INT100 is 'The parsed value for the 100th interval.'; CREATE TABLE CMZONE ( CMZONECODE VARCHAR2(64) NOT NULL, CMZONENAME VARCHAR2(64) NOT NULL, STARTTIME DATE NOT NULL, STOPTIME DATE , ADDTIME DATE ); ALTER TABLE CMZONE ADD CONSTRAINT PK_CMZN2_1 PRIMARY KEY (CMZONECODE); COMMENT ON TABLE CMZONE IS 'This table describes the ERCOT defined Congestion Management Zones found in ERCOTs territory.'; COMMENT ON COLUMN CMZONE.CMZONECODE is 'The ERCOT Lodestar code associated to an ERCOT congestion management zone.'; COMMENT ON COLUMN CMZONE.CMZONENAME is 'The long name for an ERCOT congestion management zone.'; COMMENT ON COLUMN CMZONE.STARTTIME is 'This timestamp represents the date and time when the data in a row takes effect.'; COMMENT ON COLUMN CMZONE.STOPTIME is 'This timestamp represents the date and time when the data in a row is no longer in effect.'; COMMENT ON COLUMN CMZONE.ADDTIME is 'This timestamp represents the date and time when a new row is added or a column value in an existing row is updated.'; CREATE TABLE ESIID ( UIDESIID NUMBER(10) NOT NULL, ESIID VARCHAR2(64) NOT NULL, STARTTIME DATE NOT NULL, STOPTIME DATE , ADDTIME DATE ); ALTER TABLE ESIID ADD CONSTRAINT PK_SD3_2 PRIMARY KEY (UIDESIID); COMMENT ON TABLE ESIID IS 'This table lists ESIIDs and their Start and Stop times of existence.'; COMMENT ON COLUMN ESIID.UIDESIID is 'The ERCOT Lodestar unique numeric identifier for the ESIID.'; COMMENT ON COLUMN ESIID.ESIID is 'The Electric Service Industry Identifier (ESIID) as assigned by the TDSP.'; COMMENT ON COLUMN ESIID.STARTTIME is 'The date and time when the ESIID was created by the TDSP.'; COMMENT ON COLUMN ESIID.STOPTIME is 'If this field is not null, the date and time represents when the ESIID was retired by the TDSP.'; COMMENT ON COLUMN ESIID.ADDTIME is 'The date and time when an ESIID record is added or updated in ERCOTs Lodestar system.'; CREATE TABLE ESIIDSERVICEHIST ( UIDESIID NUMBER(10) NOT NULL, SERVICECODE VARCHAR2(64) NOT NULL, STARTTIME DATE NOT NULL, STOPTIME DATE , REPCODE VARCHAR2(64) , STATIONCODE VARCHAR2(64) , PROFILECODE VARCHAR2(64) , LOSSCODE VARCHAR2(64) , ADDTIME DATE , DISPATCHFL CHAR(1) NOT NULL, MRECODE VARCHAR2(64) NOT NULL, TDSPCODE VARCHAR2(64) NOT NULL, REGIONCODE VARCHAR2(64) NOT NULL, DISPATCHASSETCODE VARCHAR2(14) , STATUS VARCHAR2(64) NOT NULL, ZIP VARCHAR2(10) , PGCCODE VARCHAR2(64) , DISPATCHTYPE VARCHAR2(3) ); ALTER TABLE ESIIDSERVICEHIST ADD CONSTRAINT PK_SDSRVCHST7_3 PRIMARY KEY (UIDESIID, SERVICECODE, STARTTIME); COMMENT ON TABLE ESIIDSERVICEHIST IS 'This table identifies the settlement characteristics associated with each ESIID such as the load serving entity, profile code, loss code, TDSP code, etc.'; COMMENT ON COLUMN ESIIDSERVICEHIST.UIDESIID is 'The ERCOT Lodestar unique numeric identifier for the ESIID.'; COMMENT ON COLUMN ESIIDSERVICEHIST.SERVICECODE is 'The unique identifier of ELE for electrical service.'; COMMENT ON COLUMN ESIIDSERVICEHIST.STARTTIME is 'The date and time when the data elements in the row take effect.'; COMMENT ON COLUMN ESIIDSERVICEHIST.STOPTIME is 'The date and time when the data elements in a row are no longer in effect.'; COMMENT ON COLUMN ESIIDSERVICEHIST.REPCODE is 'The ERCOT Lodestar code used to identify a load serving entity (LSE). See values in REP table.'; COMMENT ON COLUMN ESIIDSERVICEHIST.STATIONCODE is 'The unique character code to identify a substation serving an ESIID as assigned by the TDSP and used in the ERCOT Network Model. See values in STATION table. To identify the CM Zone and the UFE Zone, see STATIONSERVICEHIST.'; COMMENT ON COLUMN ESIIDSERVICEHIST.PROFILECODE is 'The unique character code that identifies the profile type, weather zone, meter data type, weather sensitivity and Time Of Use code as assigned by the TDSP. See values in the PROFILECLASS table.'; COMMENT ON COLUMN ESIIDSERVICEHIST.LOSSCODE is 'The unique character code that identifies a distribution loss factor code as assigned by the TDSP. LOSSCODEs are defined in ERCOT Protocols (A, B, C, D, E, T).'; COMMENT ON COLUMN ESIIDSERVICEHIST.ADDTIME is 'The date and time when an ESIIDSERVICEHIST record is added or updated in ERCOTs Lodestar system.'; COMMENT ON COLUMN ESIIDSERVICEHIST.DISPATCHFL is 'A flag indicating if the ESIID is registered as a load resource.'; COMMENT ON COLUMN ESIIDSERVICEHIST.MRECODE is 'The ERCOT Lodestar code identifying the Meter Reading Entity. MRECODE defaults to the submitting TDSP, unless otherwise assigned by ERCOT. See values in the MRE table.'; COMMENT ON COLUMN ESIIDSERVICEHIST.TDSPCODE is 'The ERCOT Lodestar code identifying the Transmission and Distribution Service Provider. See values in TDSP table.'; COMMENT ON COLUMN ESIIDSERVICEHIST.REGIONCODE is 'The unique character code identifying the power region the ESIID is electrically connected in as assigned by the TDSP (ERCOT, SPP, SERC, WSCC). If REGIONCODE is not ERCOT, the STATIONCODE, PROFILECODE, LOSSCODE should be null. See values in REGION table.'; COMMENT ON COLUMN ESIIDSERVICEHIST.DISPATCHASSETCODE is 'The unique character code assigned by ERCOT Operations to a registered load resource.'; COMMENT ON COLUMN ESIIDSERVICEHIST.STATUS is 'The status of the ESIIDSERVICEHIST row identified as A (Active-ESIID affiliated with LSE), D (De-Energized-ESIID not affiliated with any LSE), or I (Inactive-ESIID Retired).'; COMMENT ON COLUMN ESIIDSERVICEHIST.ZIP is 'Service Address ZIP Code as assigned by the TDSP.'; COMMENT ON COLUMN ESIIDSERVICEHIST.PGCCODE is 'The ERCOT Lodestar code assigned to a Power Generation Company (PGC), if the ESIID is registered as a load resource. See values in PGC table.'; COMMENT ON COLUMN ESIIDSERVICEHIST.DISPATCHTYPE is 'The code identifying the type of load resource as assigned by ERCOT Operations.'; CREATE TABLE ESIIDSERVICEHIST_DELETE ( UIDESIID NUMBER(10) NOT NULL, SERVICECODE VARCHAR2(64) NOT NULL, STARTTIME DATE NOT NULL, D_TIMESTAMP DATE, SRC_ADDTIME DATE NOT NULL ); ALTER TABLE ESIIDSERVICEHIST_DELETE ADD CONSTRAINT PK_SDSRVCHST11_4 PRIMARY KEY (UIDESIID, SERVICECODE, STARTTIME, SRC_ADDTIME); COMMENT ON TABLE ESIIDSERVICEHIST_DELETE IS 'Records that appear in this table have been deleted in ERCOTs system. The keys sRecords that appear in this table have been deleted in ERCOTs system. The keys should be used to delete the corresponding records from ESIIDSERVICEHIST.hould be used to delete the corresponding records from ESIIDSERVICEHIST.'; COMMENT ON COLUMN ESIIDSERVICEHIST_DELETE.UIDESIID is 'The ERCOT Lodestar unique numeric identifier for the ESIID.'; COMMENT ON COLUMN ESIIDSERVICEHIST_DELETE.SERVICECODE is 'The unique identifier of ELE for electrical service.'; COMMENT ON COLUMN ESIIDSERVICEHIST_DELETE.STARTTIME is 'The date and time when the data elements in the row take effect.'; COMMENT ON COLUMN ESIIDSERVICEHIST_DELETE.D_TIMESTAMP is 'This timestamp represents the date and time when a record in ESIIDSERVICEHIST has been deleted'; COMMENT ON COLUMN ESIIDSERVICEHIST_DELETE.SRC_ADDTIME is 'This timestamp represents the ADDTIME of the record in ESIIDSERVICEHIST that was deleted'; CREATE TABLE ESIIDUSAGE ( UIDESIID NUMBER(10) NOT NULL, STARTTIME DATE NOT NULL, STOPTIME DATE NOT NULL, BILLMONTH NUMBER(5) , METERTYPE VARCHAR2(2) NOT NULL, TOTAL NUMBER , READSTATUS VARCHAR2(64) , AVGDAILYUSG NUMBER , ONPK NUMBER , OFFPK NUMBER , MDPK NUMBER , SPK NUMBER , ONPKADU NUMBER , OFFPKADU NUMBER , MDPKADU NUMBER , SPKADU NUMBER , ADDTIME DATE , GLOBPROCID VARCHAR2(128) , TIMESTAMP DATE ); ALTER TABLE ESIIDUSAGE ADD CONSTRAINT PK_SDSG6_5 PRIMARY KEY (UIDESIID, STARTTIME, METERTYPE); COMMENT ON TABLE ESIIDUSAGE IS 'This table contains Non-IDR usage records submitted by the Meter Reading Entity (typically the TSDP)that have passed ERCOT validation and loaded into Lodestar. For those ESIIDs that are demand metered, there will a separate record for the demand value.'; COMMENT ON COLUMN ESIIDUSAGE.UIDESIID is 'The ERCOT Lodestar unique numeric identifier for the ESIID.'; COMMENT ON COLUMN ESIIDUSAGE.STARTTIME is 'The start time of the meter read.'; COMMENT ON COLUMN ESIIDUSAGE.STOPTIME is 'The stop time of the meter read.'; COMMENT ON COLUMN ESIIDUSAGE.BILLMONTH is 'The ERCOT Lodestar assigned month and year associated to the meter read to be used only to identify Non-IDR profiled ESIIDs subject to the IDR Requirement. This field is NOT used for usage month determination for the Load Profile assignment.'; COMMENT ON COLUMN ESIIDUSAGE.METERTYPE is 'The unit of measure of the meter data as defined by Texas SET.'; COMMENT ON COLUMN ESIIDUSAGE.TOTAL is 'The total energy or maximum demand for the record submitted by the Meter Reading Entity (typically the TDSP).'; COMMENT ON COLUMN ESIIDUSAGE.READSTATUS is 'The code submitted by the Meter Reading Entity (typically the TDSP) representing whether the meter read is actual (A) or estimated (E).'; COMMENT ON COLUMN ESIIDUSAGE.AVGDAILYUSG is '(No longer used in ERCOT settlement calculations.) The ERCOT Lodestar calculated Total Average Daily Usage, which represents the ESIIDUSAGE.TOTAL divided by the number of days in the meter read record.'; COMMENT ON COLUMN ESIIDUSAGE.ONPK is 'On Peak value is the total on-peak energy for the record submitted by the Meter Reading Entity (typically the TDSP). This field is only required to be populated when the ESIID has a Time Of Use assignment in the ESIIDSERVICEHIST.PROFILECODE.'; COMMENT ON COLUMN ESIIDUSAGE.OFFPK is 'Off Peak value is the total off-peak energy for the record submitted by the Meter Reading Entity (typically the TDSP). This field is only required to be populated when the ESIID has a Time Of Use assignment in the ESIIDSERVICEHIST.PROFILECODE.'; COMMENT ON COLUMN ESIIDUSAGE.MDPK is 'Mid Peak value is the total mid-peak energy for the record submitted by the Meter Reading Entity (typically the TDSP). This field is only required to be populated when the ESIID has a Time Of Use assignment in the ESIIDSERVICEHIST.PROFILECODE.'; COMMENT ON COLUMN ESIIDUSAGE.SPK is 'Super Peak value is the total super-peak energy for the record submitted by the Meter Reading Entity (typically the TDSP). This field is only required to be populated when the ESIID has a Time Of Use assignment in the ESIIDSERVICEHIST.PROFILECODE.'; COMMENT ON COLUMN ESIIDUSAGE.ONPKADU is '(No longer used in ERCOT settlement calculations.) The ERCOT Lodestar calculated on-peak Average Daily Usage, which represents the ESIIDUSAGE.ONPK divided by the number of days in the meter read record.'; COMMENT ON COLUMN ESIIDUSAGE.OFFPKADU is '(No longer used in ERCOT settlement calculations.) The ERCOT Lodestar calculated off-peak Average Daily Usage, which represents the ESIIDUSAGE.OFFPK divided by the number of days in the meter read record.'; COMMENT ON COLUMN ESIIDUSAGE.MDPKADU is '(No longer used in ERCOT settlement calculations.) The ERCOT Lodestar calculated mid-peak Average Daily Usage, which represents the ESIIDUSAGE.MDPK divided by the number of days in the meter read record.'; COMMENT ON COLUMN ESIIDUSAGE.SPKADU is '(No longer used in ERCOT settlement calculations.) The ERCOT Lodestar calculated super-peak Average Daily Usage, which represents the ESIIDUSAGE.SPK divided by the number of days in the meter read record.'; COMMENT ON COLUMN ESIIDUSAGE.ADDTIME is 'The batch processing date when an ESIIDUSAGE record is added in ERCOTs Lodestar system. This timestamp is not an indication of Non-IDR data being available for settlements.'; COMMENT ON COLUMN ESIIDUSAGE.GLOBPROCID is 'The ERCOT system generated unique transaction number.'; COMMENT ON COLUMN ESIIDUSAGE.TIMESTAMP is 'The date and time when an ESIIDUSAGE record is added or updated in ERCOTs Lodestar system.'; CREATE TABLE ESIIDUSAGE_DELETE ( UIDESIID NUMBER(10) NOT NULL, STARTTIME DATE NOT NULL, METERTYPE VARCHAR2(2) NOT NULL, D_TIMESTAMP DATE, SRC_TIMESTAMP DATE NOT NULL ); ALTER TABLE ESIIDUSAGE_DELETE ADD CONSTRAINT PK_SDSGDLT10_6 PRIMARY KEY (UIDESIID, STARTTIME, METERTYPE, SRC_TIMESTAMP); COMMENT ON TABLE ESIIDUSAGE_DELETE IS 'Records that appear in this table have been deleted in ERCOTs system. The keys should be used to delete the corresponding records from ESIIDUSAGE.'; COMMENT ON COLUMN ESIIDUSAGE_DELETE.UIDESIID is 'The ERCOT Lodestar unique numeric identifier for the ESIID.'; COMMENT ON COLUMN ESIIDUSAGE_DELETE.STARTTIME is 'The start time of the meter read.'; COMMENT ON COLUMN ESIIDUSAGE_DELETE.METERTYPE is 'The unit of measure of the meter data as defined by Texas SET.'; COMMENT ON COLUMN ESIIDUSAGE_DELETE.D_TIMESTAMP is 'This timestamp represents the date and time when a record in ESIIDUSAGE has been deleted.'; COMMENT ON COLUMN ESIIDUSAGE_DELETE.SRC_TIMESTAMP is 'This timestamp represents the TIMESTAMP of the record in ESIIDUSAGE that was deleted'; CREATE TABLE LSCHANNELCUTDATA ( UIDCHANNELCUT NUMBER(10) NOT NULL, ADDTIME DATE , TRADE_DATE DATE NOT NULL, INT001 NUMBER , INT002 NUMBER , INT003 NUMBER , INT004 NUMBER , INT005 NUMBER , INT006 NUMBER , INT007 NUMBER , INT008 NUMBER , INT009 NUMBER , INT010 NUMBER , INT011 NUMBER , INT012 NUMBER , INT013 NUMBER , INT014 NUMBER , INT015 NUMBER , INT016 NUMBER , INT017 NUMBER , INT018 NUMBER , INT019 NUMBER , INT020 NUMBER , INT021 NUMBER , INT022 NUMBER , INT023 NUMBER , INT024 NUMBER , INT025 NUMBER , INT026 NUMBER , INT027 NUMBER , INT028 NUMBER , INT029 NUMBER , INT030 NUMBER , INT031 NUMBER , INT032 NUMBER , INT033 NUMBER , INT034 NUMBER , INT035 NUMBER , INT036 NUMBER , INT037 NUMBER , INT038 NUMBER , INT039 NUMBER , INT040 NUMBER , INT041 NUMBER , INT042 NUMBER , INT043 NUMBER , INT044 NUMBER , INT045 NUMBER , INT046 NUMBER , INT047 NUMBER , INT048 NUMBER , INT049 NUMBER , INT050 NUMBER , INT051 NUMBER , INT052 NUMBER , INT053 NUMBER , INT054 NUMBER , INT055 NUMBER , INT056 NUMBER , INT057 NUMBER , INT058 NUMBER , INT059 NUMBER , INT060 NUMBER , INT061 NUMBER , INT062 NUMBER , INT063 NUMBER , INT064 NUMBER , INT065 NUMBER , INT066 NUMBER , INT067 NUMBER , INT068 NUMBER , INT069 NUMBER , INT070 NUMBER , INT071 NUMBER , INT072 NUMBER , INT073 NUMBER , INT074 NUMBER , INT075 NUMBER , INT076 NUMBER , INT077 NUMBER , INT078 NUMBER , INT079 NUMBER , INT080 NUMBER , INT081 NUMBER , INT082 NUMBER , INT083 NUMBER , INT084 NUMBER , INT085 NUMBER , INT086 NUMBER , INT087 NUMBER , INT088 NUMBER , INT089 NUMBER , INT090 NUMBER , INT091 NUMBER , INT092 NUMBER , INT093 NUMBER , INT094 NUMBER , INT095 NUMBER , INT096 NUMBER , INT097 NUMBER , INT098 NUMBER , INT099 NUMBER , INT100 NUMBER ); ALTER TABLE LSCHANNELCUTDATA ADD CONSTRAINT PK_LSCHNNLCT5_8 PRIMARY KEY (UIDCHANNELCUT, TRADE_DATE); COMMENT ON TABLE LSCHANNELCUTDATA IS 'This table contains IDR usage records submitted by the Meter Reading Entity (typically the TSDP) that have passed ERCOT validation and loaded into Lodestar. Records in this table are joined to the LSCHANNELCUTHEADER table by the UIDCHANNELCUT variable.'; COMMENT ON COLUMN LSCHANNELCUTDATA.UIDCHANNELCUT is 'Variable used to join the LSCHANNELCUTDATA table to the LSCHANNELCUTHEADER table.'; COMMENT ON COLUMN LSCHANNELCUTDATA.ADDTIME is 'The date and time when an IDR usage record is added or updated in ERCOTs Lodestar system. This timestamp indicates that IDR data is available for settlements.'; COMMENT ON COLUMN LSCHANNELCUTDATA.TRADE_DATE is 'The ERCOT Lodestar market trade date for which the interval data applies.'; COMMENT ON COLUMN LSCHANNELCUTDATA.INT001 is 'The total energy for the interval record submitted by the Meter Reading Entity (typically the TDSP). This comment applies to LSCHANNELCUTDATA.INT001 through LSCHANNELCUTDATA.INT100.'; CREATE TABLE LSCHANNELCUTHEADER ( UIDCHANNELCUT NUMBER(10) NOT NULL, UIDCHANNEL NUMBER(10) NOT NULL, RECORDER VARCHAR2(64) , CHANNEL NUMBER(5) , STARTTIME DATE , STOPTIME DATE , SPI NUMBER(10) , UOMCODE VARCHAR2(64) , DSTPARTICIPANT CHAR(1) , TIMEZONE NUMBER(5) , ORIGIN CHAR(1) , STARTREADING NUMBER , STOPREADING NUMBER , METERMULTIPLIER NUMBER , METEROFFSET NUMBER , PULSEMULTIPLIER NUMBER , PULSEOFFSET NUMBER , EDITED CHAR(1) , INTERNALVALIDATION CHAR(1) , EXTERNALVALIDATION CHAR(1) , MERGEFLAG CHAR(1) , DELETEFLAG CHAR(1) , VALFLAGE CHAR(1) , VALFLAGI CHAR(1) , VALFLAGO CHAR(1) , VALFLAGN CHAR(1) , TKWRITTENFLAG CHAR(1) , DCFLOW CHAR(1) , ACCEPTREJECTSTATUS CHAR(2) , TRANSLATIONTIME DATE , DESCRIPTOR VARCHAR2(254) , ADDTIME DATE , INTERVALCOUNT NUMBER(10) , CHNLCUTTIMESTAMP DATE ); ALTER TABLE LSCHANNELCUTHEADER ADD CONSTRAINT PK_LSCHNNLCT6_9 PRIMARY KEY (UIDCHANNELCUT); COMMENT ON TABLE LSCHANNELCUTHEADER IS 'This table contains the header data for IDR usage records submitted by the Meter Reading Entity (typically the TSDP) that have passed ERCOT validation and loaded into Lodestar. Records in this table are joined to the LSCHANNELCUTDATA table by the UIDCHANNELCUT variable.'; COMMENT ON COLUMN LSCHANNELCUTHEADER.UIDCHANNELCUT is 'Variable used to join the LSCHANNELCUTHEADER table to the LSCHANNELCUTDATA.'; COMMENT ON COLUMN LSCHANNELCUTHEADER.UIDCHANNEL is 'Lodestar variable not used by ERCOT.'; COMMENT ON COLUMN LSCHANNELCUTHEADER.RECORDER is 'The Electric Service Industry Identifier (ESIID) as assigned by the TDSP or the Resource ID for TDSP submitted distributed generation.'; COMMENT ON COLUMN LSCHANNELCUTHEADER.CHANNEL is 'The Lodestar channel ERCOT uses for interval data storage (1=generation kWh, 3=load kVARh, 4=load kWh).'; COMMENT ON COLUMN LSCHANNELCUTHEADER.STARTTIME is 'The start time of the interval meter read.'; COMMENT ON COLUMN LSCHANNELCUTHEADER.STOPTIME is 'The stop time of the interval meter read.'; COMMENT ON COLUMN LSCHANNELCUTHEADER.SPI is 'The number of seconds per interval (SPI) for the interval data cut (I.E. 15 MINUTES = 900, 1 HOUR = 3600).'; COMMENT ON COLUMN LSCHANNELCUTHEADER.UOMCODE is 'The Lodestar defined numerical code to indicate the Unit Of Measure of the interval data (01=kWh, 03=kVARh).'; COMMENT ON COLUMN LSCHANNELCUTHEADER.DSTPARTICIPANT is 'This flag indicates if the data participates in DST adjustments (always = Y).'; COMMENT ON COLUMN LSCHANNELCUTHEADER.TIMEZONE is 'Not in use'; COMMENT ON COLUMN LSCHANNELCUTHEADER.ORIGIN is 'A variable that represents the origin of the interval cut: M = TDSP submitted, C = ERCOT computed or EPS data.'; COMMENT ON COLUMN LSCHANNELCUTHEADER.STARTREADING is 'Not in use'; COMMENT ON COLUMN LSCHANNELCUTHEADER.STOPREADING is 'Not in use'; COMMENT ON COLUMN LSCHANNELCUTHEADER.METERMULTIPLIER is 'The determined meter multiplier for this data record. It is defaulted to 1.'; COMMENT ON COLUMN LSCHANNELCUTHEADER.METEROFFSET is 'The determined meter off set value for this data record. It is defaulted to 1.'; COMMENT ON COLUMN LSCHANNELCUTHEADER.PULSEMULTIPLIER is 'The determined pulse multiplier for this data record. It is defaulted to 1.'; COMMENT ON COLUMN LSCHANNELCUTHEADER.PULSEOFFSET is 'The determined pulse off set value for this data record. It is defaulted to 1.'; COMMENT ON COLUMN LSCHANNELCUTHEADER.EDITED is 'Not in use'; COMMENT ON COLUMN LSCHANNELCUTHEADER.INTERNALVALIDATION is 'Not in use'; COMMENT ON COLUMN LSCHANNELCUTHEADER.EXTERNALVALIDATION is 'Not in use'; COMMENT ON COLUMN LSCHANNELCUTHEADER.MERGEFLAG is 'Not in use'; COMMENT ON COLUMN LSCHANNELCUTHEADER.DELETEFLAG is 'Not in use'; COMMENT ON COLUMN LSCHANNELCUTHEADER.VALFLAGE is 'Not in use'; COMMENT ON COLUMN LSCHANNELCUTHEADER.VALFLAGI is 'Not in use'; COMMENT ON COLUMN LSCHANNELCUTHEADER.VALFLAGO is 'Not in use'; COMMENT ON COLUMN LSCHANNELCUTHEADER.VALFLAGN is 'Not in use'; COMMENT ON COLUMN LSCHANNELCUTHEADER.TKWRITTENFLAG is 'Not in use'; COMMENT ON COLUMN LSCHANNELCUTHEADER.DCFLOW is 'Not in use'; COMMENT ON COLUMN LSCHANNELCUTHEADER.ACCEPTREJECTSTATUS is 'Not in use'; COMMENT ON COLUMN LSCHANNELCUTHEADER.TRANSLATIONTIME is 'Not in use'; COMMENT ON COLUMN LSCHANNELCUTHEADER.DESCRIPTOR is 'This is a comment field. If data is loaded via EDI transaction, ERCOT populates this field with the GLOBAL Processing ID.'; COMMENT ON COLUMN LSCHANNELCUTHEADER.ADDTIME is 'The batch processing date when an IDR record is added in ERCOTs Lodestar system. This timestamp is not an indication of IDR data being available for settlements.'; COMMENT ON COLUMN LSCHANNELCUTHEADER.INTERVALCOUNT is 'Count of intervals within the LSCHANNELCUTHEADER.STARTTIME and LSCHANNELCUTHEADER.STOPTIME.'; COMMENT ON COLUMN LSCHANNELCUTHEADER.CHNLCUTTIMESTAMP is 'The date when the EDI transaction translates into XML, which is then translated into LSE to be validated and loaded into Lodestar. This timestamp is not an indication of IDR data being available for settlements.'; CREATE TABLE LSCHANNELCUTHEADER_DELETE ( UIDCHANNELCUT NUMBER(10) NOT NULL, D_TIMESTAMP DATE, SRC_CHNLCUTTIMESTAMP DATE NOT NULL ); ALTER TABLE LSCHANNELCUTHEADER_DELETE ADD CONSTRAINT PK_LSCHNNLCT10_10 PRIMARY KEY (UIDCHANNELCUT, SRC_CHNLCUTTIMESTAMP); COMMENT ON TABLE LSCHANNELCUTHEADER_DELETE IS 'Records that appear in this table have been deleted in ERCOTs system. The keys should be used to delete the corresponding records from LSCHANNELCUTHEADER.'; COMMENT ON COLUMN LSCHANNELCUTHEADER_DELETE.UIDCHANNELCUT is 'Variable used to join the LSCHANNELCUTHEADER table to the LSCHANNELCUTDATA.'; COMMENT ON COLUMN LSCHANNELCUTHEADER_DELETE.D_TIMESTAMP is 'This timestamp represents the date and time when a record in LSCHANNELCUTHEADER has been deleted.'; COMMENT ON COLUMN LSCHANNELCUTHEADER_DELETE.SRC_CHNLCUTTIMESTAMP is 'This timestamp represents the CHNLCUTTIMESTAMP of the record in LSCHANNELCUTHEADER that was deleted'; CREATE TABLE MRE ( MRECODE VARCHAR2(64) NOT NULL, MRENAME VARCHAR2(64) NOT NULL, STARTTIIME DATE NOT NULL, STOPTIME DATE , ADDTIME DATE , DUNSNUMBER VARCHAR2(64) NOT NULL ); ALTER TABLE MRE ADD CONSTRAINT PK_MR1_12 PRIMARY KEY (MRECODE); COMMENT ON TABLE MRE IS 'This table lists Meter Reading Entities (MRE) and DUNS numbers.'; COMMENT ON COLUMN MRE.MRECODE is 'The ERCOT Lodestar code identifying the Meter Reading Entity. MRECODE defaults to the submitting TDSP, unless otherwise assigned by ERCOT.'; COMMENT ON COLUMN MRE.MRENAME is 'The long name identifying a meter reading entity.'; COMMENT ON COLUMN MRE.STARTTIIME is 'This timestamp represents the date and time when the data in a row takes effect.'; COMMENT ON COLUMN MRE.STOPTIME is 'This timestamp represents the date and time when the data in a row is no longer in effect.'; COMMENT ON COLUMN MRE.ADDTIME is 'This timestamp represents the date and time when a new row is added or a column value in an existing row is updated.'; COMMENT ON COLUMN MRE.DUNSNUMBER is 'This code uniquely identifies the market participant as registered at ERCOT.'; CREATE TABLE PGC ( PGCCODE VARCHAR2(64) NOT NULL, PGCNAME VARCHAR2(64) NOT NULL, STARTTIME DATE NOT NULL, STOPTIME DATE , ADDTIME DATE , DUNSNUMBER VARCHAR2(64) NOT NULL ); ALTER TABLE PGC ADD CONSTRAINT PK_PGC0_13 PRIMARY KEY (PGCCODE); COMMENT ON TABLE PGC IS 'This table record the ownership percentage in a generator by a power generating company (PGC).'; COMMENT ON COLUMN PGC.PGCCODE is 'The ERCOT Lodestar code assigned to a Power Generation Company (PGC), if the ESIID is registered as a load resource.'; COMMENT ON COLUMN PGC.PGCNAME is 'The long name of a power generation company as registered at ERCOT.'; COMMENT ON COLUMN PGC.STARTTIME is 'This timestamp represents the date and time when the data in a row takes effect.'; COMMENT ON COLUMN PGC.STOPTIME is 'This timestamp represents the date and time when the data in a row is no longer in effect.'; COMMENT ON COLUMN PGC.ADDTIME is 'This timestamp represents the date and time when a new row is added or a column value in an existing row is updated.'; COMMENT ON COLUMN PGC.DUNSNUMBER is 'This code uniquely identifies the market participant as registered at ERCOT.'; CREATE TABLE PROFILECLASS ( PROFILECODE VARCHAR2(64) NOT NULL, WEATHERSENSITIVITY VARCHAR2(3) NOT NULL, METERTYPE VARCHAR2(4) NOT NULL, STARTTIME DATE NOT NULL, STOPTIME DATE , ADDTIME DATE , TOUTYPE VARCHAR2(64) NOT NULL, PROFILECUTCODE VARCHAR2(64) NOT NULL ); ALTER TABLE PROFILECLASS ADD CONSTRAINT PK_PRFLCLSS4_14 PRIMARY KEY (PROFILECODE); COMMENT ON TABLE PROFILECLASS IS 'The PROFILECLASS table identifies the components of profile codes, including weather zone, meter type, weather sensitivity and time of use code. Refer to the Profile Decision Tree on the ERCOT website for additional details.'; COMMENT ON COLUMN PROFILECLASS.PROFILECODE is 'The unique character code that identifies the profile type, weather zone, meter data type, weather sensitivity and Time Of Use code as assigned by the TDSP.'; COMMENT ON COLUMN PROFILECLASS.WEATHERSENSITIVITY is 'This code identifies if the ESIID is weather sensitive.'; COMMENT ON COLUMN PROFILECLASS.METERTYPE is 'Defines the type of meter (IDR or NIDR).'; COMMENT ON COLUMN PROFILECLASS.STARTTIME is 'This timestamp represents the date and time when the data in a row takes effect.'; COMMENT ON COLUMN PROFILECLASS.STOPTIME is 'This timestamp represents the date and time when the data in a row is no longer in effect.'; COMMENT ON COLUMN PROFILECLASS.ADDTIME is 'This timestamp represents the date and time when a new row is added or a column value in an existing row is updated.'; COMMENT ON COLUMN PROFILECLASS.TOUTYPE is 'Time of use schedule assigned to the ESIID by the TDSP.'; COMMENT ON COLUMN PROFILECLASS.PROFILECUTCODE is 'The unique character code that identifies the unique combinations of profile types and weather zones.'; CREATE TABLE REP ( REPCODE VARCHAR2(64) NOT NULL, REPNAME VARCHAR2(64) NOT NULL, STARTTIME DATE NOT NULL, STOPTIME DATE , ADDTIME DATE , DUNSNUMBER VARCHAR2(64) NOT NULL ); ALTER TABLE REP ADD CONSTRAINT PK_RP1_15 PRIMARY KEY (REPCODE); COMMENT ON TABLE REP IS 'This table lists Load Serving Entities (LSE) and DUNS number.'; COMMENT ON COLUMN REP.REPCODE is 'The ERCOT Lodestar code used to identify a load serving entity (LSE).'; COMMENT ON COLUMN REP.REPNAME is 'The company name used by the load serving entity as registered at ERCOT.'; COMMENT ON COLUMN REP.STARTTIME is 'This timestamp represents the date and time when the data in a row takes effect.'; COMMENT ON COLUMN REP.STOPTIME is 'This timestamp represents the date and time when the data in a row is no longer in effect.'; COMMENT ON COLUMN REP.ADDTIME is 'This timestamp represents the date and time when a new row is added or a column value in an existing row is updated.'; COMMENT ON COLUMN REP.DUNSNUMBER is 'This code uniquely identifies the market participant as registered at ERCOT.'; CREATE TABLE STATION ( STATIONCODE VARCHAR2(64) NOT NULL, STATIONNAME VARCHAR2(64) NOT NULL, STARTTIME DATE NOT NULL, STOPTIME DATE , ADDTIME DATE ); ALTER TABLE STATION ADD CONSTRAINT PK_STTN3_16 PRIMARY KEY (STATIONCODE); COMMENT ON TABLE STATION IS 'A physical electric substation location that exists in the ERCOT Network Model and is used to associate load (ESIID) and generation (resources) to CM zones and UFE zones.'; COMMENT ON COLUMN STATION.STATIONCODE is 'The unique character code to identify a substation serving an ESIID as assigned by the TDSP and used in the ERCOT Network Model.'; COMMENT ON COLUMN STATION.STATIONNAME is 'The long name to identify a substation used in the ERCOT Network Model.'; COMMENT ON COLUMN STATION.STARTTIME is 'This timestamp represents the date and time when the data in a row takes effect.'; COMMENT ON COLUMN STATION.STOPTIME is 'This timestamp represents the date and time when the data in a row is no longer in effect.'; COMMENT ON COLUMN STATION.ADDTIME is 'This timestamp represents the date and time when a new row is added or a column value in an existing row is updated.'; CREATE TABLE STATIONSERVICEHIST ( STATIONCODE VARCHAR2(64) NOT NULL, STARTTIME DATE NOT NULL, STOPTIME DATE , UFEZONECODE VARCHAR2(64) NOT NULL, CMZONECODE VARCHAR2(64) NOT NULL, ADDTIME DATE , SUBUFECODE VARCHAR2(64) NOT NULL ); ALTER TABLE STATIONSERVICEHIST ADD CONSTRAINT PK_STTNSRVCH7_17 PRIMARY KEY (STATIONCODE, STARTTIME); COMMENT ON TABLE STATIONSERVICEHIST IS 'Historical changes in STATION table.'; COMMENT ON COLUMN STATIONSERVICEHIST.STATIONCODE is 'The unique character code to identify a substation serving an ESIID as assigned by the TDSP and used in the ERCOT Network Model.'; COMMENT ON COLUMN STATIONSERVICEHIST.STARTTIME is 'This timestamp represents the date and time when the data in a row takes effect.'; COMMENT ON COLUMN STATIONSERVICEHIST.STOPTIME is 'This timestamp represents the date and time when the data in a row is no longer in effect.'; COMMENT ON COLUMN STATIONSERVICEHIST.UFEZONECODE is 'The ERCOT Lodestar code identifying an electrically connected area where the total generation is compared to the total load and variences attributed to Unaccounted For Energy (UFE). The STATIONSERVICEHIST.UFEZONECODE is U1.'; COMMENT ON COLUMN STATIONSERVICEHIST.CMZONECODE is 'The ERCOT Lodestar code associated to an ERCOT congestion management zone. See CMZONE table.'; COMMENT ON COLUMN STATIONSERVICEHIST.ADDTIME is 'This timestamp represents the date and time when a new row is added or a column value in an existing row is updated.'; COMMENT ON COLUMN STATIONSERVICEHIST.SUBUFECODE is 'The ERCOT Lodestar code identifying an analysis subzone of electerically connected area where the total generation is compared to the total load and variences attributed to Unaccounted For Energy (UFE).'; CREATE TABLE TDSP ( TDSPCODE VARCHAR2(64) NOT NULL, TDSPNAME VARCHAR2(64) NOT NULL, STARTTIME DATE NOT NULL, STOPTIME DATE , ADDTIME DATE , DUNSNUMBER VARCHAR2(64) NOT NULL, NOIECODE VARCHAR2(64) ); ALTER TABLE TDSP ADD CONSTRAINT PK_TDSP0_18 PRIMARY KEY (TDSPCODE); COMMENT ON TABLE TDSP IS 'Any entity under the jurisdiction of the PUCT and registered with ERCOT that owns and maintains a transmission or distribution system for the delivery of energy to and from the grid including a municipal or coop.'; COMMENT ON COLUMN TDSP.TDSPCODE is 'The ERCOT Lodestar code identifying the Transmission and Distribution Service Provider.'; COMMENT ON COLUMN TDSP.TDSPNAME is 'The long name identifying the Transmission and Distribution Service Provider.'; COMMENT ON COLUMN TDSP.STARTTIME is 'This timestamp represents the date and time when the data in a row takes effect.'; COMMENT ON COLUMN TDSP.STOPTIME is 'This timestamp represents the date and time when the data in a row is no longer in effect.'; COMMENT ON COLUMN TDSP.ADDTIME is 'This timestamp represents the date and time when a new row is added or a column value in an existing row is updated.'; COMMENT ON COLUMN TDSP.DUNSNUMBER is 'This code uniquely identifies the market participant as registered at ERCOT.'; COMMENT ON COLUMN TDSP.NOIECODE is 'The ERCOT Lodestar unique code associated to TDSPs that are registered with ERCOT as a non opt-in entity (NOIE).'; ALTER TABLE STATIONSERVICEHIST ADD CONSTRAINT FK_STTNSRVCH7_CMZN21 FOREIGN KEY (CMZONECODE) REFERENCES CMZONE; ALTER TABLE ESIIDSERVICEHIST ADD CONSTRAINT FK_SDSRVCHST7_SD32 FOREIGN KEY (UIDESIID) REFERENCES ESIID; ALTER TABLE ESIIDUSAGE ADD CONSTRAINT FK_SDSG6_SD33 FOREIGN KEY (UIDESIID) REFERENCES ESIID; ALTER TABLE LSCHANNELCUTDATA ADD CONSTRAINT FK_LSCHNNLCT5_LSCHNNLCT65 FOREIGN KEY (UIDCHANNELCUT) REFERENCES LSCHANNELCUTHEADER; ALTER TABLE ESIIDSERVICEHIST ADD CONSTRAINT FK_SDSRVCHST7_MR16 FOREIGN KEY (MRECODE) REFERENCES MRE; ALTER TABLE ESIIDSERVICEHIST ADD CONSTRAINT FK_SDSRVCHST7_PGC07 FOREIGN KEY (PGCCODE) REFERENCES PGC; ALTER TABLE ESIIDSERVICEHIST ADD CONSTRAINT FK_SDSRVCHST7_PRFLCLSS48 FOREIGN KEY (PROFILECODE) REFERENCES PROFILECLASS; ALTER TABLE ESIIDSERVICEHIST ADD CONSTRAINT FK_SDSRVCHST7_RP19 FOREIGN KEY (REPCODE) REFERENCES REP; ALTER TABLE ESIIDSERVICEHIST ADD CONSTRAINT FK_SDSRVCHST7_STTN310 FOREIGN KEY (STATIONCODE) REFERENCES STATION; ALTER TABLE STATIONSERVICEHIST ADD CONSTRAINT FK_STTNSRVCH7_STTN311 FOREIGN KEY (STATIONCODE) REFERENCES STATION; ALTER TABLE ESIIDSERVICEHIST ADD CONSTRAINT FK_SDSRVCHST7_TDSP012 FOREIGN KEY (TDSPCODE) REFERENCES TDSP; ALTER TABLE AMSINTERVAL ADD CONSTRAINT FK_AMSINT_ESIID FOREIGN KEY (UIDESIID) REFERENCES ESIID (UIDESIID);