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 |
Parent topic: Defining DataConnect Options