Extract Flat File Formats
This section outlines the format of output flat files created by master and billing data extracts.
C1-CustomerDataSnapshot
C1-CustomerDataSnapshot is used for customer data extracts, and includes the following information:
Field Name |
Schema Element |
Metadata Field |
---|---|---|
Account ID |
accountId |
ACCOUNT_ID |
Account Name |
accountName |
ACCOUNT_NAME |
Account Setup Date/Time |
accountSetUpDateTime |
SETUP_DATETIME |
Customer Class |
customerClass |
CUSTOMER_CLASS |
Bill Cycle |
billCycle |
BILL_CYCLE_CODE |
Bill Cycle Description |
billCycleDescription |
BILL_CYCLE_DESCRIPTION |
Account Updated at |
accountUpdatedAt |
ACCOUNT_UPDATED_AT |
Person ID |
personId |
PERSON_ID |
Person Type |
personOrBusiness |
PERSON_TYPE |
First NamE |
firstName |
FIRST_NAME |
Last Name |
lastName |
LAST_NAME |
Business Name |
businessName |
BUSINESS_NAME |
Language Preference |
language |
LANGUAGE_PREFERENCE |
Is Main Customer |
isMainCustomer |
IS_MAIN_CUSTOMER |
Bill Route Typ |
billRouteType |
BILL_ROUTE_TYPE |
Person Updated at |
personUpdatedAt |
PERSON_UPDATED_AT |
Phone Type |
phoneType |
PHONE_TYPE_ |
Phone Number |
phoneNumber |
PHONE_NUMBER_ |
Phone Extension |
phoneExtension |
PHONE_EXTENSION_ |
|
|
EMAIL_ADDRESS_ |
Email Type |
emailType |
EMAIL_TYPE_ |
Service Agreement ID |
saId |
SERVICE_AGREEMENT_ID |
Service Agreement Name |
saName |
SERVICE_AGREEMENT_NAME |
Start Date/Time |
startDateTime |
SA_START_DATETIME |
End Date/Time |
endDateTime |
SA_END_DATETIME |
Service Type |
serviceType |
SERVICE_TYPE_CODE |
Service Type Description |
serviceTypeDescription |
SERVICE_TYPE_DESC |
SA Status |
saStatus |
STATUS_CODE |
SA Status Description |
saStatusDescription |
STATUS_DESCRIPTION |
SA Type |
saType |
SERVICE_AGREEMENT_TYPE_CODE |
SA Type Description |
saTypeDescription |
SA_TYPE_DESCRIPTION |
Special Role Flag |
specialRole |
SPECIAL_ROLE_FLAG_CODE |
Special Role Flag Description |
specialRoleDescription |
SPECIAL_ROLE_FLAG_DESCRIPTION |
CIS Division |
cisDivision |
CIS_DIVISION |
SIC |
sic |
BUSINESS_CLASSIFICATION_CODE |
SA Updated at |
saUpdatedAt |
SERVICE_AGREEMENT_UPDATED_AT |
SA Rate Code |
saRateCode |
SA_RATE_CODE |
SA Rate Effective Date/Time |
saRateEffectiveDateTime |
EFFECTIVE_DATETIME |
SA Rate Updated at |
saRateUpdatedAt |
SA_RATE_UPDATED_AT |
C1-ExternalRepoSABasedSnapshot
C1-ExternalRepoSABasedSnapshot is used for the SA-based extracts and includes the following information:
For DataConnect v1+ please refer to data area C1-CustomerDataSnapshot in the application for the layout/schema.
Field Name | Schema Element | Metadata Field |
---|---|---|
Service Agreement Id |
saId |
SA_ID |
Account Id |
accountId |
ACCT_ID |
Person Id |
personId |
PER_ID |
Bill Cycle |
billCycle |
BILL_CYC_CD |
Bill Cycle Description |
billCycleDescription |
DESCR |
Collection Class |
collectionClass |
COLL_CL_CD |
Collection Class Description |
collectionClassDescription |
DESCR |
Account Information |
accountInformation |
ACCT_INFO |
Customer Class |
customerClass |
CUST_CL_CD |
Customer Class Description |
customerClassDescription |
DESCR |
Landlord Indicator |
landlordIndicator |
C1_LANDLORD_IND |
Mailing Address |
mailingAddress1 |
ADDRESS1 |
Mailing Address 2 |
mailingAddress2 |
ADDRESS2 |
Mailing Address 3 |
mailingAddress3 |
ADDRESS3 |
Mailing City |
mailingCity |
CITY |
Mailing State |
mailingState |
STATE |
Mailing Postal |
mailingPostal |
POSTAL |
First Name |
firstName |
ENTITY_NAME |
Last Name |
lastName |
ENTITY_NAME |
Phone Number 1 |
phone1 |
PHONE |
Phone Number 2 |
phone2 |
PHONE |
Email Address |
|
EMAILID |
Special Role |
specialRole |
SPECIAL_ROLE_FLG |
Special Role Description |
specialRoleDescription |
DESCR |
Service Type |
serviceType |
SVC_TYPE_CD |
Service Type Description |
serviceTypeDescription |
DESCR |
CIS Division |
cisDivision |
CIS_DIVISION |
CIS Division Description |
cisDivisionDescription |
DESCR |
SA Type |
saType |
SA_TYPE_CD |
SA Type Description |
saTypeDescription |
DESCR |
Revenue Class |
revenueClass |
REV_CL_CD |
Revenue Class Description |
revenueClassDescription |
DESCR |
SIC |
sic |
SIC_CD |
SIC Code Description |
sicDescription |
DESCR |
Deposit Class |
depositClass |
DEP_CL_CD |
Deposit Class Description |
depositClassDescription |
DESCR |
Campaign |
campaign |
CAMPAIGN_CD |
Campaign Description |
campaignDescription |
DESCR |
Debt Class |
debtClass |
DEBT_CL_CD |
Debt Class Description |
debtClassDescription |
DESCR |
SA Start Date |
startDate |
START_DT |
SA End Date |
endDate |
END_DT |
SA Status |
saStatus |
SA_STATUS_FLG |
SA Status Description |
saStatusDescription |
DESCR |
Rate Schedule |
rateSchedule |
RS_CD |
Rate Schedule Description |
rateScheduleDescription |
DESCR |
SA's Service Point(s) Id |
spId |
SP_ID |
SA's Service Point(s) Start Date/Time |
startDateTime |
START_DTTM |
SA's Service Point(s) End Date/Time |
stopDateTime |
STOP_DTTM |
C1-ExternalRepoSPBasedSnapshot
C1-ExternalRepoSPBasedSnapshot is used for the SP-based extracts and includes the following:
For DataConnect v1+ please refer to data area C1-SPMeterDataSnapshot in the application for the layout/schema.
Field Name | Schema Element | Metadata Field |
---|---|---|
Service Point Id |
spId |
SP_ID |
Premise Id |
premiseId |
PREM_ID |
Address Line 1 |
addressLine1 |
ADDRESS1 |
City |
city |
CITY |
State |
state |
STATE |
Postal |
postal |
POSTAL |
County |
county |
COUNTY |
Country |
country |
COUNTRY |
Premise Type |
premiseType |
PREM_TYPE_CD |
Premise Type Description |
premiseTypeDescription |
DESCR |
CIS Division |
cisDivision |
CIS_DIVISION |
CIS Division Description |
cisDivisionDescription |
DESCR |
Life Support Sensitive Load |
lifeSupportSensitiveLoad |
LS_SL_FLG |
Life Support Sensitive Load Description |
lifeSupportSensitiveLoadDescription |
LS_SL_DESCR |
Trend Area |
trendArea |
TREND_AREA_CD |
Trend Area Description |
trendAreaDescription |
DESCR |
In City Limit |
inCityLimit |
IN_CITY_LIMIT |
Address Line 2 |
addressLine2 |
ADDRESS2 |
Address Line 3 |
addressLine3 |
ADDRESS3 |
Number 1 |
number1 |
NUM1 |
Number 2 |
number2 |
NUM2 |
Landlord Id |
landlord |
LL_ID |
Landlord Description |
landlordDescription |
DESCR |
SP Status |
spStatus |
SP_STATUS_FLG |
SP Status Description |
spStatusDescription |
DESCR |
Service Cycle |
serviceCycle |
MR_CYC_CD |
Service Cycle Description |
serviceCycleDescription |
DESCR |
Service Route |
serviceRoute |
MR_RTE_CD |
Service Route Description |
serviceRouteDescription |
DESCR |
Service Route Sequence |
serviceRouteSequence |
MR_CYC_RTE_SEQ |
Meter Location |
location |
MTR_LOC_CD |
Meter Location Description |
locationDescription |
DESCR |
Latitude |
latitude |
C1_LATITUDE |
Longitude |
longitude |
C1_LONGITUDE |
Facility Level 1 |
facilityLevel1 |
FAC_LVL_1_CD |
Facility Level 2 |
facilityLevel2 |
FAC_LVL_2_CD |
Facility Level 3 |
facilityLevel3 |
FAC_LVL_3_CD |
SP Disconnect Location |
disconnectLocation |
DISCON_LOC_CD |
SP Disconnect Location Description |
disconnectLocationDescription |
DESCR |
SP Source Status |
spSourceStatus |
SP_SRC_STATUS_FLG |
SP Source Status Description |
spSourceStatusDescription |
DESCR |
Active Service Agreement ID |
activeSaid |
SA_ID |
Has No Active SA |
noActiveSA |
HAS_NO_ACTIVE_SA_SW |
SA As Of Date/Time |
saAsOfDttm |
SA_AS_OF_DTTM |
C1-ExternalRepoSPMtrHtSnapshot
C1-ExternalRepoSPMtrHtSnapshot is used for the SP/Meter History based extracts and includes the following:
For DataConnect v1+ please refer to data area C1-SPMeterDataSnapshot in the application for the layout/schema.
Field Name | Schema Element | Metadata Field |
---|---|---|
Service Point Id |
spId |
SP_ID |
Meter Type |
meterType |
MTR_TYPE_CD |
Meter Type Description |
meterTypeDescription |
DESCR |
Meter Status |
meterStatus |
MTR_STATUS_FLG |
Meter Status Description |
meterStatusDescription |
DESCR |
Register Constant |
registerMultiplier |
REG_CONST |
Installation Constant |
installationMultiplier |
INSTALL_CONST |
Meter's Number of Dials |
numberOfDials |
C1_NBR_OF_DIALS |
Manufacturer |
manufacturer |
MFG_CD |
Manufacturer Description |
manufacturerDescription |
DESCR |
Model |
model |
MODEL_CD |
Model Description |
modelDescription |
DESCR |
Badge Number |
badgeNumber |
BADGE_NBR |
Serial Number |
serialNumber |
SERIAL_NBR |
Meter Id |
meterId |
MTR_ID |
Installation Date/Time |
installDateTime |
INSTALL_DTTM |
Removal Date/Time |
removalDateTime |
REMOVAL_DTTM |
SP Meter On/Off Status |
meterOnOffStatus |
MTR_ON_OFF_FLG |
C1-ExternalRepoBillingSnapshot
C1-ExternalRepoBillingSnapshot is used for the Billing-based extracts and includes the following:
Field Name | Schema Element | Metadata Field |
---|---|---|
Billable Service Quantity |
billSQ |
BILL_SQ |
Bill Segment End Date |
endDate |
END_DT |
Consumption Days |
duration |
CONSUMPTION_DAYS |
Bill Segment Estimate Indicator |
estimateIndicator |
C1_BS_ESTIMATE_IND |
Calculated Amount |
calculatedAmount |
CALC_AMT |
Unit of Measure |
uom |
UOM_CD |
Time of Use |
tou |
TOU_CD |
SQI |
sqi |
SQI_CD |
Service Agreement ID |
saId |
SA_ID |
Service Point ID |
spId |
SP_ID |