Extract Flat File Formats

This section outlines the format of output flat files created by master and billing data extracts.

Data Areas:

C1-CustomerDataSnapshot
C1-ExternalRepoSABasedSnapshot
C1-ExternalRepoSPBasedSnapshot
C1-ExternalRepoSPMtrHtSnapshot
C1-ExternalRepoBillingSnapshot

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	email	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

Top of Page

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	email	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

Top of Page

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

Top of Page

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

Top of Page

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

Top of Page