Oracle Self-Service E-Billing v6.2: File Formats for Statement-Level Information

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

File Formats for Statement-Level Information

This topic shows the file formats for statement-level information.

Table 152 shows the file format for control records for billed file information.

Table 152. Billed Control Record File Format
Field	Pos	Max Len	Data Type	Req?	Staging Table (STG_CDR) Column Name	DB_TABLE_NAME	DB_COLUMN_NAME	Industry Application
Rec Type	1	3	VARCHAR2	Yes	None	None	None	Telco and Utility
HEADER TYPE	2	20	VARCHAR2	Yes	None	None	None	Telco and Utility
BILLING SYSTEM	3	20	VARCHAR2	Yes	None	None	None	Telco and Utility
Line of Business	4	20	VARCHAR2	Yes	None	None	WIRELESS for telecommunications and UTILITY for the utility industry	Telco and Utility
Group ID/Key	5	7	NUMBER	Yes	COL1	None	None	Telco and Utility
Master Bill File Name	6	255	VARCHAR2	No	COL2	None	None	Telco and Utility

Table 153 shows the file format for control record for unbilled file information.

Table 153. Unbilled Control Record File Format
Field	Pos	Max Len	Data Type	Req?	Staging Table (STG_CDR) Column Name	DB_TABLE_NAME	DB_COLUMN_NAME	Industry Application
Rec Type	1	4	VARCHAR2	Yes	None	None	None	Telco
HEADER TYPE	2	20	VARCHAR2	Yes	None	None	None	Telco
BILL SYSTEM	3	20	VARCHAR2	Yes	None	None	None	Telco
Line of Business	4	20	VARCHAR2	Yes	None	WIRELESS	None	Telco
File Type	5	20	VARCHAR2	Yes	COL1	UNBILLED	None	Telco

Table 154 shows the file format for control record for prepaid file information.

Table 154. Prepaid Control Record File Format
Field	Pos	Max Len	Data Type	Req?	Staging Table (STG_CDR) Column Name	DB_TABLE_NAME	DB_COLUMN_NAME	Industry Application
Rec Type	1	4	VARCHAR2	Yes	None	None	None	Telco
HEADER TYPE	2	20	VARCHAR2	Yes	None	None	None	Telco
BILL SYSTEM	3	20	VARCHAR2	Yes	None	None	None	Telco
Line of Business	4	20	VARCHAR2	Yes	None	WIRELESS	None	Telco
File Type	5	20	VARCHAR2	Yes	COL1	PREPAY	None	Telco

Table 155 shows the file format for summary-level detail information.

Table 155. Summary-Level Detail Record File Format
Record Type	Field	Pos	Max Len	Data Type	Req?	Staging Table (STG_CDR) Column Name	DB_TABLE_NAME	DB_COLUMN_NAME	Industry Application
1000	Rec Type	1	3	VARCHAR2	No	REC_TYPE	None	None	Telco and Utility
1000	Statement Number	2	20	VARCHAR2	Yes	COL1	EDX_RPT _STATEMENT_FACT	STATEMENT_NUMBER	Telco and Utility
1000	Company ID	3	20	VARCHAR2	No	COL2	EDX_RPT_COMPANY_DIM	COMPANY_CD	Telco and Utility
1000	Company Name	4	255	VARCHAR2	No	COL3	EDX_RPT_COMPANY_DIM	COMPANY_NAME	Telco and Utility
1000	Statement Date	5	8	DATE	No	COL4	EDX_RPT _STATEMENT_FACT	STATEMENT_DATE	Telco and Utility
1000	Billing Cycle Start Date	6	8	DATE	Yes	COL5	EDX_RPT _STATEMENT_FACT	BILL_CYCLE_START _DATE	Telco and Utility
1000	Billing Cycle End Date	7	8	DATE	Yes	COL6	EDX_RPT _STATEMENT_FACT	BILL_CYCLE_END _DATE	Telco and Utility
1000	Previous Balance	8	16,2	NUMBER	No	COL7	EDX_RPT _STATEMENT_FACT	PREVIOUS_BALANCE	Telco and Utility
1000	Total Payment Posted	9	16,2	NUMBER	No	COL8	EDX_RPT _STATEMENT_FACT	TOTAL_PAYMENT _POSTED	Telco and Utility
1000	Total Adjustments	10	16,2	NUMBER	No	COL9	EDX_RPT _STATEMENT_FACT	TOTAL_ADJUSTMENTS	Telco
1000	Balance Forward Due	11	16,2	NUMBER	No	COL10	EDX_RPT _STATEMENT_FACT	BALANCE_FORWARD _DUE	Telco and Utility
1000	Total Current Charge Due	12	16,2	NUMBER	No	COL11	EDX_RPT _STATEMENT_FACT	TOTAL_CURRENT _CHARGE_DUE	Telco and Utility
1000	Total Amount Due	13	16,2	NUMBER	No	COL12	EDX_RPT _STATEMENT_FACT	TOTAL_AMOUNT_DUE	Telco and Utility
1000	Monthly Service Charges	14	16,2	NUMBER	No	COL13	EDX_RPT _STATEMENT_FACT	MONTHLY_CHARGE _AMT	Telco
1000	Usage Charges	15	16,2	NUMBER	No	COL14	EDX_RPT _STATEMENT_FACT	USAGE_CHARGE _AMT	Telco
1000	Credits	16	16,2	NUMBER	No	COL15	EDX_RPT _STATEMENT_FACT	CREDIT_ADJUST_AMT	Telco and Utility
1000	Other Charges	17	16,2	NUMBER	No	COL16	EDX_RPT _STATEMENT_FACT	OTHER_CHARGE_AMT	Telco
1000	Taxes and Fees	18	16,2	NUMBER	No	COL17	EDX_RPT _STATEMENT_FACT	TAXES_SURCHARGES _FEE	Telco
1000	Flex Field_1	19	16,2	NUMBER	No	COL18	EDX_RPT _STATEMENT_FACT	FLEX_FIELD1	Telco and Utility
1000	Flex Field_2	20	16,2	NUMBER	No	COL19	EDX_RPT _STATEMENT_FACT	FLEX_FIELD2	Telco and Utility
1000	Flex Field_3	21	16,2	NUMBER	No	COL20	EDX_RPT _STATEMENT_FACT	FLEX_FIELD3	Telco and Utility
1000	Flex Field_4	22	16,2	NUMBER	No	COL21	EDX_RPT _STATEMENT_FACT	FLEX_FIELD4	Telco and Utility
1000	Flex Field_5	23	16,2	NUMBER	No	COL22	EDX_RPT _STATEMENT_FACT	FLEX_FIELD5	Telco and Utility
1000	Minimum Amount Due	24	16,2	NUMBER	No	COL23	EDX_RPT _STATEMENT_FACT	MINIMUM_DUE_AMT	Telco
1000	Statement Due Date	25	8	DATE	No	COL24	EDX_RPT _STATEMENT_FACT	STATEMENT_DUE _DATE	Telco and Utility
1000	Statement Currency	26	50	VARCHAR2	No	COL25	EDX_RPT _STATEMENT_FACT	CURRENCY_TYPE_CD	Telco and Utility
1000	Statement Country	27	50	VARCHAR2	No	COL26	EDX_RPT _STATEMENT_FACT	COUNTRY_CD	Telco
1000	Statement Time Zone	28	50	VARCHAR2	No	COL27	EDX_RPT _STATEMENT_FACT	TIME_ZONE_CD	Telco
1000	Note1	29	2010	VARCHAR2	No	COL28	EDX_RPT _STATEMENT_FACT	NOTE1	Telco and Utility
1000	Note2	30	2010	VARCHAR2	No	COL29	EDX_RPT _STATEMENT_FACT	NOTE2	Telco and Utility
1000	Note3	31	2010	VARCHAR2	No	COL30	EDX_RPT _STATEMENT_FACT	NOTE3	Telco and Utility
1000	Note4	32	2010	VARCHAR2	No	COL31	EDX_RPT _STATEMENT_FACT	NOTE4	Telco and Utility
1000	Note5	33	2010	VARCHAR2	No	COL32	EDX_RPT _STATEMENT_FACT	NOTE5	Telco and Utility
1000	MEDIA _TYPE	34	50	VARCHAR2	No	COL33	EDX_RPT _STATEMENT_FACT	MEDIA_TYPE	Telco
1000	Corporation Account No	35	255	VARCHAR2	No	COL34	EDX_RPT_COMPANY_DIM	FLEX_FIELD1	Telco
1000	Corporation Tax ID	36	255	VARCHAR2	No	COL35	EDX_RPT_COMPANY_DIM	FLEX_FIELD2	Telco
1000	Flex Field_6	37	255	VARCHAR2	No	COL36	EDX_RPT _STATEMENT_FACT	FLEX_FIELD6	Telco and Utility
1000	Flex Field_7	38	255	VARCHAR2	No	COL37	EDX_RPT _STATEMENT_FACT	FLEX_FIELD7	Telco and Utility
1000	Flex Field_8	39	255	VARCHAR2	No	COL38	EDX_RPT _STATEMENT_FACT	FLEX_FIELD8	Telco and Utility
1000	Company Display Name	42	255	VARCHAR2	No	COL41	EDX_RPT_COMPANY_DIM	FLEX_FIELD3	Telco

Table 156 shows the file format for statement payment fact information.

Table 156. Statement Payment Fact File Format
Record Type	Field	Pos	Max Len	Data Type	Req?	Staging Table (STG_CDR) Column Name	DB_TABLE_NAME	DB_COLUMN_NAME	Industry Application
1100	Rec Type	1	3	VARCHAR2	No	REC_TYPE	None	None	Telco and Utility
1100	Statement Number	2	20	VARCHAR2	Yes	COL1	EDX_RPT_STATEMENT_PAYMENT_FACT	STATEMENT_KEY	Telco and Utility
1100	Payment Type Code	3	50	VARCHAR2	Yes	COL2	EDX_RPT_STATEMENT_PAYMENT_FACT	PAYMENT_TYPE_KEY	Telco and Utility
1100	Payment Amount	4	16,2	NUMBER	Yes	COL3	EDX_RPT_STATEMENT_PAYMENT_FACT	PAYMENT_AMOUNT	Telco and Utility
1100	Payment Date	5	8	DATE	No	COL4	EDX_RPT_STATEMENT_PAYMENT_FACT	PAYMENT_DATE	Telco and Utility
1100	Payment Note	6	255	VARCHAR2	No	COL5	EDX_RPT_STATEMENT_PAYMENT_FACT	PAYMENT_NOTE	Telco and Utility

Table 157 shows the file format for statement adjustment fact information.

Table 157. Statement Adjustment Fact File Format
Record Type	Field	Pos	Max Len	Data Type	Req?	Staging Table (STG_CDR) Column Name	DB_TABLE_NAME	DB_COLUMN_NAME	Industry Application
1200	Rec Type	1	3	VARCHAR2	No	REC_TYPE	None	None	Telco and Utility
1200	Statement Number	2	20	VARCHAR2	Yes	COL1	EDX_RPT_STATEMENT_ADJUST_FACT	STATEMENT_KEY	Telco and Utility
1200	Adjustment Type Code	3	50	VARCHAR2	Yes	COL2	EDX_RPT_STATEMENT_ADJUST_FACT	ADJUSTMENT _TYPE_KEY	Telco and Utility
1200	Adjustment Amount	4	16,2	NUMBER	Yes	COL3	EDX_RPT_STATEMENT_ADJUST_FACT	ADJUSTMENT _AMOUNT	Telco and Utility
1200	Adjustment Date	5	8	DATE	No	COL4	EDX_RPT_STATEMENT_ADJUST_FACT	ADJUSTMENT _DATE	Telco and Utility
1200	Service Number	6	20	VARCHAR2	No	COL5	EDX_RPT_STATEMENT_ADJUST_FACT	SERVICE_NUMBER	Telco and Utility
1200	Adjustment Note	7	255	VARCHAR2	No	COL6	EDX_RPT_STATEMENT_ADJUST_FACT	ADJUSTMENT_NOTE	Telco and Utility

Table 158 shows the file format for statement mail to address type information.

Table 158. Statement Mail to Address File Format
Record Type	Field	Pos	Max Len	Data Type	Req?	Staging Table (STG_CDR) Column Name	DB_TABLE_NAME	DB_COLUMN_NAME	Industry Application
1300	Rec Type	1	3	VARCHAR2	No	REC_TYPE	None	None	Telco and Utility
1300	Statement Number	2	20	VARCHAR2	Yes	COL1	None	None	Telco and Utility
1300	Statement Address Line 1	3	50	VARCHAR2	No	COL2	EDX_RPT_ADDRESS _DIM	ADDRESS1	Telco and Utility
1300	Statement Address Line 2	4	50	VARCHAR2	No	COL3	EDX_RPT_ADDRESS _DIM	ADDRESS2	Telco and Utility
1300	Statement Address Line 3	5	50	VARCHAR2	No	COL4	EDX_RPT_ADDRESS _DIM	ADDRESS3	Telco and Utility
1300	Statement City	6	50	VARCHAR2	No	COL5	EDX_RPT_ADDRESS _DIM	CITY	Telco and Utility
1300	Statement State	7	50	VARCHAR2	No	COL6	EDX_RPT_ADDRESS _DIM	STATE	Telco and Utility
1300	Statement Postal Code	8	20	VARCHAR2	No	COL7	EDX_RPT_ADDRESS _DIM	ZIP_CD	Telco and Utility
1300	Statement Country Code	9	20	VARCHAR2	No	COL8	EDX_RPT_ADDRESS _DIM	COUNTRY	Telco and Utility

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