Oracle Billing Insight V7.0: File Formats for Service-Level Information

Implementation Guide for Oracle Billing Insight > Load Input File Specifications and Data Mapping >

File Formats for Service-Level Information

This topic shows the file formats for service-level information. Table 150 shows the file format for service level total information.

Table 150. Service Level Total 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
3000	Rec Type	1	3	VARCHAR2	No	REC_TYPE	None	None	Telco and Utility
3000	Statement Number	2	20	VARCHAR2	Yes	COL1	EDX_RPT_SERVICE _FACT	STATEMENT_KEY	Telco and Utility
3000	Account Number	3	20	VARCHAR2	Yes	COL2	EDX_RPT_SERVICE _DIM	ACCOUNT_KEY	Telco and Utility
3000	Service Number	4	20	VARCHAR2	Yes	COL3	EDX_RPT_SERVICE _DIM	SERVICE_NUM	Telco and Utility
3000	Service Owner Name	5	100	VARCHAR2	No	COL4	None	None	Telco and Utility
3000	Monthly Service Charges	6	16,2	NUMBER	Yes	COL5	EDX_RPT_SERVICE _FACT	MONTHLY_CHARGE _AMT	Telco and Utility
3000	Usage Charges	7	16,2	NUMBER	Yes	COL6	EDX_RPT_SERVICE _FACT	USAGE_CHARGE_AMT	Telco and Utility
3000	Adjustments	8	16,2	NUMBER	Yes	COL7	EDX_RPT_SERVICE _FACT	CREDIT_ADJUST_AMT	Telco and Utility
3000	Other Charges	9	16,2	NUMBER	Yes	COL8	EDX_RPT_SERVICE _FACT	OTHER_CHARGE_AMT	Telco and Utility
3000	Taxes and Fees	10	16,2	NUMBER	Yes	COL9	EDX_RPT_SERVICE _FACT	TAXES_SURCHARGES_FEE	Telco and Utility
3000	Total Charge Amount	11	16,2	NUMBER	Yes	COL10	EDX_RPT_SERVICE _FACT	TOTAL_CHARGE_AMT	Telco and Utility
3000	Charge Note	12	255	VARCHAR2	No	COL11	EDX_RPT_SERVICE _FACT	CHARGE_NOTE	Telco and Utility
3000	Flex Field_1	13	16,2	NUMBER	No	COL12	EDX_RPT_SERVICE _FACT	FLEX_FIELD1	Telco and Utility
3000	Flex Field_2	14	16,2	NUMBER	No	COL13	EDX_RPT_SERVICE _FACT	FLEX_FIELD2	Telco and Utility
3000	Flex Field_3	15	16,2	NUMBER	No	COL14	EDX_RPT_SERVICE _FACT	FLEX_FIELD3	Telco and Utility
3000	Flex Field_4	16	16,2	NUMBER	No	COL15	EDX_RPT_SERVICE _FACT	FLEX_FIELD4	Telco and Utility
3000	Flex Field_5	17	16,2	NUMBER	No	COL16	EDX_RPT_SERVICE _FACT	FLEX_FIELD5	Telco and Utility
3000	Sub Charge Type Code	18	50	VARCHAR2	No	COL17	EDX_RPT_SERVICE _DIM	SERVICE_TYPE_KEY	Utility
3000	Service Start Date	19	8	DATE	No	COL18	EDX_RPT_SERVICE _FACT	START_DT	Utility
3000	Service End Date	20	8	DATE	No	COL19	EDX_RPT_SERVICE _FACT	END_DT	Utility
3000	Premise ID	21	20	VARCHAR2	No	COL20	EDX_RPT_SERVICE _FACT	PREMISE_KEY	Utility
3000	Zoning	22	21	VARCHAR2	No	COL21	EDX_RPT_SERVICE _DIM	ZONING	Utility

Table 151 shows the file format for the service level on charge type information.

Table 151. Service Level on Charge Type Level 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
3100	Rec Type	1	3	VARCHAR2	No	REC_TYPE	None	None	Telco and Utility
3100	Statement Number	2	20	VARCHAR2	Yes	COL1	EDX_RPT_SERVICE_CHARGE_FACT	STATEMENT_KEY	Telco and Utility
3100	Account Number	3	20	VARCHAR2	Yes	COL2	EDX_RPT_SERVICE_CHARGE_FACT	ACCOUNT_KEY	Telco and Utility
3100	Service Number	4	20	VARCHAR2	Yes	COL3	EDX_RPT_SERVICE_CHARGE_FACT	SERVICE_KEY	Telco and Utility
3100	Charge Type Code	5	50	VARCHAR2	Yes	COL4	EDX_RPT_SERVICE_CHARGE_FACT	CHARGE_TYPE_KEY	Telco and Utility
3100	Sub Charge Type Code	6	50	VARCHAR2	No	COL5	EDX_RPT_SERVICE_CHARGE_FACT	SUB_CHARGE_TYPE _KEY	Telco and Utility
3100	Monthly Charge Amount	7	10	NUMBER	Yes	COL6	EDX_RPT_SERVICE_CHARGE_FACT	CHARGE_AMT	Telco and Utility
3100	Charge Note	8	255	VARCHAR2	No	COL7	EDX_RPT_SERVICE_CHARGE_FACT	CHARGE_NOTE	Telco and Utility
3100	Service Start Date	9	8	DATE	No	COL8	EDX_RPT_SERVICE_CHARGE_FACT	START_DT	Utility
3100	Service End Date	10	8	DATE	No	COL9	EDX_RPT_SERVICE_CHARGE_FACT	END_DT	Utility
3100	Charge Note Code	11	50	VARCHAR2	No	COL10	EDX_RPT_SERVICE_CHARGE_FACT	NOTES_KEY	Utility

Table 152 shows the file format for the service charge on product and plan information.

Table 152. Service Charge on Product and Plan 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
3200	Rec Type	1	4	VARCHAR2	No	REC_TYPE	None	None	Telco
3200	Statement Number	2	20	VARCHAR2	Yes	COL1	EDX_RPT_SERVICE_PRODUCT_FACT	STATEMENT_KEY	Telco
3200	Account Number	3	20	VARCHAR2	Yes	COL2	EDX_RPT_SERVICE_PRODUCT_FACT	ACCOUNT_KEY	Telco
3200	Service Number	4	20	VARCHAR2	Yes	COL3	EDX_RPT_SERVICE_PRODUCT_FACT	SERVICE_KEY	Telco
3200	Plan Type Code	5	50	VARCHAR2	Yes	COL4	EDX_RPT_SERVICE_PRODUCT_FACT	PLAN_TYPE_KEY	Telco
3200	Product Code	6	50	VARCHAR2	Yes	COL5	EDX_RPT_SERVICE_PRODUCT_FACT	PRODUCT_PARENT_KEY	Telco
3200	Monthly Charge Amount	7	16,2	NUMBER	Yes	COL6	EDX_RPT_SERVICE_PRODUCT_FACT	CHARGE_AMT	Telco
3200	Product Note	8	255	VARCHAR2	No	COL7	EDX_RPT_SERVICE_PRODUCT_FACT	PRODUCT_NOTE	Telco
3200	Product Subcode	9	50	VARCHAR2	Yes	COL8	EDX_RPT_SERVICE_PRODUCT_FACT	PRODUCT_CHILD_KEY	Telco

Table 153 shows the file format for service charge on usage type information.

Table 153. Service Charge on Usage Type 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
3300	Rec Type	1	3	VARCHAR2	No	REC_TYPE	None	None	Telco and Utility
3300	Statement Number	2	20	VARCHAR2	Yes	COL1	EDX_RPT_SERVICE_USAGE_FACT	STATEMENT_KEY	Telco and Utility
3300	Account Number	3	20	VARCHAR2	Yes	COL2	EDX_RPT_SERVICE_USAGE_FACT	ACCOUNT_KEY	Telco and Utility
3300	Service Number	4	20	VARCHAR2	Yes	COL3	EDX_RPT_SERVICE_USAGE_FACT	SERVICE_KEY	Telco and Utility
3300	Usage Type Code	5	50	VARCHAR2	Yes	COL4	EDX_RPT_SERVICE_USAGE_FACT	USAGE_TYPE_KEY	Telco and Utility
3300	Total Usage	6	16,2	NUMBER	Yes	COL5	EDX_RPT_SERVICE_USAGE_FACT	TOTAL_USAGE	Telco and Utility
3300	Usage Unit Code	7	50	VARCHAR2	Yes	COL6	EDX_RPT_SERVICE_USAGE_FACT	UNIT_KEY	Telco and Utility
3300	Amount	8	16,2	NUMBER	Yes	COL7	EDX_RPT_SERVICE_USAGE_FACT	TOTAL_CHARGE_AMT	Telco and Utility
3300	Usage Note	9	255	VARCHAR2	No	COL8	EDX_RPT_SERVICE_USAGE_FACT	USAGE_NOTE	Telco and Utility
3300	Gross Amount	10	16,2	NUMBER	No	COL9	EDX_RPT_SERVICE_USAGE_FACT	GROSS_AMT	Telco and Utility
3300	Discount Amount	11	16,2	NUMBER	No	COL10	EDX_RPT_SERVICE_USAGE_FACT	DISCOUNT_AMT	Telco and Utility
3300	Tax Amount	12	16,2	NUMBER	No	COL11	EDX_RPT_SERVICE_USAGE_FACT	TAX_AMT	Telco and Utility
3300	Total Call Count	13	16	NUMBER	No	COL12	EDX_RPT_SERVICE_USAGE_FACT	TOTAL_CALL_CNT	Telco and Utility

Table 154 shows the file format for the service charges on various tariff information.

Table 154. Service Charges on Various Tariffs 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
3400	Rec Type	1	3	VARCHAR2	No	REC_TYPE	None	None	Telco
3400	Statement Number	2	20	VARCHAR2	Yes	COL1	EDX_RPT_SERVICE_TARIFF_FACT	STATEMENT_KEY	Telco
3400	Account Number	3	20	VARCHAR2	Yes	COL2	EDX_RPT_SERVICE_TARIFF_FACT	ACCOUNT_KEY	Telco
3400	Service Number	4	20	VARCHAR2	Yes	COL3	EDX_RPT_SERVICE_TARIFF_FACT	SERVICE_KEY	Telco
3400	Usage Type Code	5	50	VARCHAR2	Yes	COL4	EDX_RPT_SERVICE_TARIFF_FACT	USAGE_TYPE_KEY	Telco
3400	Tariff Code	6	50	VARCHAR2	Yes	COL5	EDX_RPT_SERVICE_TARIFF_FACT	TARIFF_KEY	Telco
3400	Allowance	7	20	VARCHAR2	Yes	COL6	EDX_RPT_SERVICE_TARIFF_FACT	ALLOWANCE	Telco
3400	Total Usages	8	16,2	NUMBER	Yes	COL7	EDX_RPT_SERVICE_TARIFF_FACT	TOTAL_USAGE	Telco
3400	Usage Unit Code	9	50	VARCHAR2	Yes	COL8	EDX_RPT_SERVICE_TARIFF_FACT	UNIT_KEY	Telco
3400	Billable	10	16,2	NUMBER	Yes	COL9	EDX_RPT_SERVICE_TARIFF_FACT	BILLABLE	Telco
3400	Amount	11	16,2	NUMBER	Yes	COL10	EDX_RPT_SERVICE_TARIFF_FACT	CHARGE_AMT	Telco
3400	Gross Amount	12	16,2	NUMBER	No	COL11	EDX_RPT_SERVICE_TARIFF_FACT	GROSS_AMT	Telco and Utility
3400	Discount Amount	13	16,2	NUMBER	No	COL12	EDX_RPT_SERVICE_TARIFF_FACT	DISCOUNT_AMT	Telco and Utility
3400	Tax Amount	14	16,2	NUMBER	No	COL13	EDX_RPT_SERVICE_TARIFF_FACT	TAX_AMT	Telco and Utility

Table 155 shows the services miscellaneous charges type information.

Table 155. Services Miscellaneous Charges 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
3500	Rec Type	1	3	VARCHAR2	No	REC_TYPE	None	None	Utility
3500	Statement Number	2	20	VARCHAR2	Yes	COL1	EDX_RPT_SERVICE_MISC_FACT	STATEMENT_KEY	Utility
3500	Account Number	3	20	VARCHAR2	Yes	COL2	EDX_RPT_SERVICE_MISC_FACT	ACCOUNT_KEY	Utility
3500	Service Number	4	20	VARCHAR2	Yes	COL3	EDX_RPT_SERVICE_MISC_FACT	SERVICE_KEY	Utility
3500	Charge Description	5	255	VARCHAR2	No	COL4	EDX_RPT_SERVICE_MISC_FACT	MISC_COMMENT	Utility
3500	Total Amount	6	16,2	NUMBER	Yes	COL5	EDX_RPT_SERVICE_MISC_FACT	MISC_AMOUNT	Utility

Implementation Guide for Oracle Billing Insight		Copyright © 2016, Oracle and/or its affiliates. All rights reserved. Legal Notices.