Oracle Self-Service E-Billing v6.1: File Formats for Service Detail-Level Information

Implementation Guide for Oracle Self-Service E-Billing > ETL Input File Specifications and Data Mapping >

File Formats for Service Detail-Level Information

Table 162 shows the file format for service call usage detail (voice, data, message) information.

Table 162. Service Call Usage Details (Voice, Data, Message)


Record Type	Field	Pos	Max Len	Data Type	Req?	Staging Table (STG_CDR) Column Name	DB_TABLE_NAME	DB_COLUMN_NAME	Industry Application
4000	Rec Type	1	3	VARCHAR2	Yes	REC_TYPE	None	None	Telco
4000	Usage Name	2	50	VARCHAR2	Yes	COL1	EDX_RPT_SERVICE_DETAIL_FACT	USAGE_TYPE_KEY	Telco
4000	Statement Number	3	20	VARCHAR2	Yes	COL2	EDX_RPT_SERVICE_DETAIL_FACT	STATEMENT_KEY	Telco
4000	Account Number	4	20	VARCHAR2	Yes	COL3	EDX_RPT_SERVICE_DETAIL_FACT	ACCOUNT_KEY	Telco
4000	Service Number	5	20	VARCHAR2	Yes	COL4	EDX_RPT_SERVICE_DETAIL_FACT	SERVICE_KEY	Telco
4000	Called Date	6	8	DATE	Yes	COL5	EDX_RPT_SERVICE_DETAIL_FACT	DATE_KEY	Telco
4000	Called Time	7	10	VARCHAR2	Yes	COL6	EDX_RPT_SERVICE_DETAIL_FACT	DURATION	Telco
4000	Called Number	8	20	VARCHAR2	Yes	COL7	EDX_RPT_SERVICE_DETAIL_FACT	CALLED_NUM	Telco
4000	Tariff Code	9	50	VARCHAR2	Yes	COL8	EDX_RPT_SERVICE_DETAIL_FACT	TARIFF_KEY	Telco
4000	Call Type Code	10	50	VARCHAR2	No	COL9	EDX_RPT_SERVICE_DETAIL_FACT	CALL_TYPE_KEY	Telco
4000	Direction Code	11	20	VARCHAR2	Yes	COL10	EDX_RPT_SERVICE_DETAIL_FACT	DIRECTION_KEY	Telco
4000	Service Type Code	12	50	VARCHAR2	No	COL11	EDX_RPT_SERVICE_DETAIL_FACT	SERVICE_TYPE_KEY	Telco
4000	Total Usages	13	16,2	NUMBER	Yes	COL12	EDX_RPT_SERVICE_DETAIL_FACT	TOTAL_USAGE	Telco
4000	Usages Unit Code	14	50	VARCHAR2	Yes	COL13	EDX_RPT_SERVICE_DETAIL_FACT	UNIT_KEY	Telco
4000	Other Charge	15	16,2	NUMBER	Yes	COL14	EDX_RPT_SERVICE_DETAIL_FACT	OTHER_CHARGE _AMT	Telco
4000	Total Charge	16	16,2	NUMBER	Yes	COL15	EDX_RPT_SERVICE_DETAIL_FACT	TOTAL_CHARGE_AMT	Telco
4000	Calling City and State	17	100	VARCHAR2	Yes	COL16	EDX_RPT_SERVICE_DETAIL_FACT	CALLING_AREA_CD _KEY	Telco
4000	Calling Country Code	18	100	VARCHAR2	Yes	COL17	EDX_RPT_SERVICE_DETAIL_FACT	CALLING_AREA_CD _KEY	Telco
4000	Reference Number	19	100	VARCHAR2	No	COL18	EDX_RPT_SERVICE_DETAIL_FACT	REFERENCE_NUM	Telco
4000	Carrier Code	20	50	VARCHAR2	No	COL19	EDX_RPT_SERVICE_DETAIL_FACT	CARRIER_KEY	Telco
4000	Region Code	21	50	VARCHAR2	No	COL20	EDX_RPT_SERVICE_DETAIL_FACT	REGION_KEY	Telco
4000	Note1	22	2000	VARCHAR2	No	COL21	EDX_RPT_SERVICE_DETAIL_FACT	NOTE1	Telco
4000	Note2	23	2000	VARCHAR2	No	COL22	EDX_RPT_SERVICE_DETAIL_FACT	NOTE2	Telco
4000	Note3	24	2000	VARCHAR2	No	COL23	EDX_RPT_SERVICE_DETAIL_FACT	NOTE3	Telco
4000	Note4	25	2000	VARCHAR2	No	COL24	EDX_RPT_SERVICE_DETAIL_FACT	NOTE4	Telco
4000	Note5	26	2000	VARCHAR2	No	COL25	EDX_RPT_SERVICE_DETAIL_FACT	NOTE5	Telco
4000	Called City and State	27	100	VARCHAR2	Yes	COL26	EDX_RPT_SERVICE_DETAIL_FACT	CALLED_AREA_CD _KEY	Telco
4000	Called Country Code	28	100	VARCHAR2	Yes	COL27	EDX_RPT_SERVICE_DETAIL_FACT	CALLED_AREA_CD _KEY	Telco

File Format

Table 163 shows the file format for the premise data information.

Table 163. Premise Data


Record Type	Field	Pos	Max Len	Data Type	Req?	Staging Table (STG_CDR) Column Name	DB_TABLE_NAME	DB_COLUMN_NAME	Industry Application
5000	Rec Type	1	3	VARCHAR2	Yes	REC_TYPE	None	None	Utility
5000	Statement Number	2	20	VARCHAR2	Yes	COL1	EDX_RPT_PREMISE_DIM	STATEMENT_KEY	Utility
5000	Account Number	3	20	VARCHAR2	Yes	COL2	EDX_RPT_PREMISE_DIM	ACCOUNT_KEY	Utility
5000	Premise ID	4	20	VARCHAR2	Yes	COL3	EDX_RPT_PREMISE_DIM	PREMISE_KEY	Utility
5000	Address Line 1	5	200	VARCHAR2	Yes	COL4	EDX_RPT_PREMISE_DIM	ADDRESS1	Utility
5000	Address Line 2	6	200	VARCHAR2	No	COL5	EDX_RPT_PREMISE_DIM	ADDRESS2	Utility
5000	Address Line 3	7	200	VARCHAR2	No	COL6	EDX_RPT_PREMISE_DIM	ADDRESS3	Utility
5000	City	8	100	VARCHAR2	No	COL7	EDX_RPT_PREMISE_DIM	CITY	Utility
5000	State	9	100	VARCHAR2	No	COL8	EDX_RPT_PREMISE_DIM	STATE	Utility
5000	postal code	10	20	VARCHAR2	No	COL9	EDX_RPT_PREMISE_DIM	ZIP_CD	Utility
5000	Country	11	100	VARCHAR2	No	COL10	EDX_RPT_PREMISE_DIM	COUNTRY	Utility

File Format

Table 164 shows the file format for the consumption data information.

Table 164. Consumption Data File


Record Type	Field	Pos	Max Len	Data Type	Req?	Staging Table (STG_CDR) Column Name	DB_TABLE_NAME	DB_COLUMN_NAME	Industry Application
5100	Rec Type	1	3	VARCHAR2	Yes	REC_TYPE	None	None	Utility
5100	Statement Number	2	20	VARCHAR2	Yes	COL1	EDX_RPT_CONSUM_FACT	STATEMENT_KEY	Utility
5100	Account Number	3	20	VARCHAR2	Yes	COL2	EDX_RPT_CONSUM_FACT	ACCOUNT_KEY	Utility
5100	Service Agreement ID	4	20	VARCHAR2	Yes	COL3	EDX_RPT_CONSUM_FACT	SERVICE_KEY	Utility
5100	Premise ID	5	20	VARCHAR2	Yes	COL4	EDX_RPT_CONSUM_FACT	PREMISE_KEY	Utility
5100	Meter Number	6	20	VARCHAR2	Yes	COL5	EDX_RPT_CONSUM_FACT	METER_NUMBER	Utility
5100	Service Unit Code	7	50	VARCHAR2	No	COL6	EDX_RPT_CONSUM_FACT	UNIT_KEY	Utility
5100	Start Meter Reading Amount	8	16,2	NUMBER	No	COL7	EDX_RPT_CONSUM_FACT	START_AMT_CONSUM	Utility
5100	Start Meter Reading Date	9	8	DATE	No	COL8	EDX_RPT_CONSUM_FACT	START_READING_DT	Utility
5100	Start Meter Reading Time	10	8	DATE	No	COL9	EDX_RPT_CONSUM_FACT	START_READING_TM	Utility
5100	Start Reading Estimated Flag	11	10	VARCHAR2	No	COL10	EDX_RPT_CONSUM_FACT	START_READING_EST	Utility
5100	End Meter Reading Amount	12	16,2	NUMBER	No	COL11	EDX_RPT_CONSUM_FACT	END_AMT_CONSUM	Utility
5100	End Meter Reading Date	13	8	DATE	No	COL12	EDX_RPT_CONSUM_FACT	END_READING_DT	Utility
5100	End Meter Reading Time	14	8	DATE	No	COL13	EDX_RPT_CONSUM_FACT	END_READING_TM	Utility
5100	End Reading Estimated Flag	15	10	VARCHAR2	No	COL14	EDX_RPT_CONSUM_FACT	END_READING_EST	Utility
5100	Multiplier	16	10	NUMBER	No	COL15	EDX_RPT_CONSUM_FACT	MULTIPLIER	Utility
5100	Measure Quantity	17	16,2	NUMBER	No	COL16	EDX_RPT_CONSUM_FACT	MEASURE_QTY	Utility
5100	Time of Use	18	16,2	NUMBER	No	COL17	EDX_RPT_CONSUM_FACT	TIMEOFUSE	Utility
5100	Billed Quantity	19	16,2	NUMBER	No	COL18	EDX_RPT_CONSUM_FACT	BILLED_QTY	Utility
5100	Service Usage Type Code	20	50	VARCHAR2	No	COL19	EDX_RPT_CONSUM_FACT	USAGE_TYPE_KEY	Utility

Format

Implementation Guide for Oracle Self-Service E-Billing		Copyright © 2013, Oracle and/or its affiliates. All rights reserved. Legal Notices.