Bookshelf Home | Contents | Index | PDF     

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

Statement Level File Format

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

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

Table 105.  Summary-Level Detail Record
RECORD TYPE
FIELD
POSITION
MAX LENGTH
DATA TYPE
REQ?
STAGING TABLE (STG_CDR) COLUMN NAME
DB_TABLE_NAME
DB_COLUMN_NAME
0000
Rec Type
1
3
VARCHAR2
Yes
N/A
N/A
N/A
0000
HEADER TYPE
2
20
VARCHAR2
Yes
N/A
N/A
N/A
0000
BILLING SYSTEM
3
20
VARCHAR2
Yes
N/A
N/A
N/A
1000
Rec Type
1
3
VARCHAR2
Yes
N/A
N/A
N/A
1000
Statement Number
2
20
VARCHAR2
Yes
COL1
EDX_RPT_STATEMENT_FACT
STATEMENT_NUMBER
1000
Company ID
3
20
VARCHAR2
No
COL2
EDX_RPT_COMPANY_DIM
COMPANY_CD
1000
Company Name
4
255
VARCHAR2
No
COL3
EDX_RPT_COMPANY_DIM
COMPANY_NAME
1000
Statement Date
5
8
DATE
No
COL4
EDX_RPT_STATEMENT_FACT
STATEMENT_DATE
1000
Billing Cycle Start Date
6
8
DATE
Yes
COL5
EDX_RPT_STATEMENT_FACT
BILL_CYCLE_START_DATE
1000
Billing Cycle End Date
7
8
DATE
Yes
COL6
EDX_RPT_STATEMENT_FACT
BILL_CYCLE_END_DATE
1000
Previous Balance
8
16,2
NUMBER
No
COL7
EDX_RPT_STATEMENT_FACT
PREVIOUS_BALANCE
1000
Total Payment Posted
9
16,2
NUMBER
No
COL8
EDX_RPT_STATEMENT_FACT
TOTAL_PAYMENT_POSTED
1000
Total Adjustments
10
16,2
NUMBER
No
COL9
EDX_RPT_STATEMENT_FACT
TOTAL_ADJUSTMENTS
1000
Balance Forward Due
11
16,2
NUMBER
No
COL10
EDX_RPT_STATEMENT_FACT
BALANCE_FORWARD_DUE
1000
Total Current Charge Due
12
16,2
NUMBER
No
COL11
EDX_RPT_STATEMENT_FACT
TOTAL_CURRENT_CHARGE_DUE
1000
Total Amount Due
13
16,2
NUMBER
No
COL12
EDX_RPT_STATEMENT_FACT
TOTAL_AMOUNT_DUE
1000
Monthly Service Charges
14
16,2
NUMBER
No
COL13
EDX_RPT_STATEMENT_FACT
MONTHLY_CHARGE_AMT
1000
Usage Charges
15
16,2
NUMBER
No
COL14
EDX_RPT_STATEMENT_FACT
USAGE_CHARGE_AMT
1000
Credits
16
16,2
NUMBER
No
COL15
EDX_RPT_STATEMENT_FACT
CREDIT_ADJUST_AMT
1000
Other Charges
17
16,2
NUMBER
No
COL16
EDX_RPT_STATEMENT_FACT
OTHER_CHARGE_AMT
1000
Taxes and Fees
18
16,2
NUMBER
No
COL17
EDX_RPT_STATEMENT_FACT
TAXES_SURCHARGES_FEE
1000
Flex Field_1
19
16,2
NUMBER
No
COL18
EDX_RPT_STATEMENT_FACT
FLEX_FIELD1
1000
Flex Field_2
20
16,2
NUMBER
No
COL19
EDX_RPT_STATEMENT_FACT
FLEX_FIELD2
1000
Flex Field_3
21
16,2
NUMBER
No
COL20
EDX_RPT_STATEMENT_FACT
FLEX_FIELD3
1000
Flex Field_4
22
16,2
NUMBER
No
COL21
EDX_RPT_STATEMENT_FACT
FLEX_FIELD4
1000
Flex Field_5
23
16,2
NUMBER
No
COL22
EDX_RPT_STATEMENT_FACT
FLEX_FIELD5
1000
Minimum Amount Due
24
16,2
NUMBER
No
COL23
EDX_RPT_STATEMENT_FACT
MINIMUM_DUE_AMT
1000
Statement Due Date
25
8
DATE
No
COL24
EDX_RPT_STATEMENT_FACT
STATEMENT_DUE_DATE
1000
Statement Currency
26
50
VARCHAR2
No
COL25
EDX_RPT_STATEMENT_FACT
CURRENCY_TYPE_CD
1000
Statement Country
27
50
VARCHAR2
No
COL26
EDX_RPT_STATEMENT_FACT
COUNTRY_CD
1000
Statement Time Zone
28
50
VARCHAR2
No
COL27
EDX_RPT_STATEMENT_FACT
TIME_ZONE_CD
1000
Note1
29
2010
VARCHAR2
No
COL28
EDX_RPT_STATEMENT_FACT
NOTE1
1000
Note2
30
2010
VARCHAR2
No
COL29
EDX_RPT_STATEMENT_FACT
NOTE2
1000
Note3
31
2010
VARCHAR2
No
COL30
EDX_RPT_STATEMENT_FACT
NOTE3
1000
Note4
32
2010
VARCHAR2
No
COL31
EDX_RPT_STATEMENT_FACT
NOTE4
1000
Note5
33
2010
VARCHAR2
No
COL32
EDX_RPT_STATEMENT_FACT
NOTE5
1000
MEDIA_TYPE
34
50
VARCHAR2
No
COL33
EDX_RPT_STATEMENT_FACT
MEDIA_TYPE
1000
Corporation Account No
35
255
VARCHAR2
No
COL34
EDX_RPT_COMPANY_DIM
FLEX_FIELD1
1000
Corporation Tax ID
36
255
VARCHAR2
No
COL35
EDX_RPT_COMPANY_DIM
FLEX_FIELD2
 
1000
Street
37
255
VARCHAR2
No
COL36
EDX_RPT_COMPANY_DIM
FLEX_FIELD3
 
1000
City
38
255
VARCHAR2
No
COL37
EDX_RPT_COMPANY_DIM
FLEX_FIELD4
 
1000
State
39
255
VARCHAR2
No
COL38
EDX_RPT_COMPANY_DIM
FLEX_FIELD5
 
1000
Country
40
255
VARCHAR2
No
COL39
EDX_RPT_COMPANY_DIM
FLEX_FIELD6
 
1000
Zipcode
41
10
VARCHAR2
No
COL40
EDX_RPT_COMPANY_DIM
FLEX_FIELD7
 
1000
Company display name
42
255
VARCHAR2
No
COL41
EDX_RPT_COMPANY_DIM
FLEX_FIELD8
Format

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

Table 106. Statement Payment Fact
RECORD TYPE
FIELD
POSITION
MAX LENGTH
DATA TYPE
REQ?
STAGING TABLE (STG_CDR) COLUMN NAME
DB_TABLE_NAME
DB_COLUMN_NAME
1100
Rec Type
1
3
VARCHAR2
Yes
REC_TYPE
 
 
1100
Statement Number
2
20
VARCHAR2
Yes
COL1
EDX_RPT_STATEMENT_PAYMENT_FACT
STATEMENT_KEY
1100
Payment Type Code
3
50
VARCHAR2
Yes
COL2
EDX_RPT_STATEMENT_PAYMENT_FACT
PAYMENT_TYPE_KEY
1100
Payment Amount
4
16,2
NUMBER
Yes
COL3
EDX_RPT_STATEMENT_PAYMENT_FACT
PAYMENT_AMOUNT
1100
Payment Date
5
8
DATE
No
COL4
EDX_RPT_STATEMENT_PAYMENT_FACT
PAYMENT_DATE
1100
Payment Note
6
255
VARCHAR2
No
COL5
EDX_RPT_STATEMENT_PAYMENT_FACT
PAYMENT_NOTE
Format

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

Table 107. Statement Adjustment Fact
RECORD TYPE
FIELD
POSITION
MAX LENGTH
DATA TYPE
REQ?
STAGING TABLE (STG_CDR) COLUMN NAME
DB_TABLE_NAME
DB_COLUMN_NAME
1200
Rec Type
1
3
VARCHAR2
Yes
REC_TYPE
 
 
1200
Statement Number
2
20
VARCHAR2
Yes
COL1
EDX_RPT_STATEMENT_ADJUST_FACT
STATEMENT_KEY
1200
Adjustment Type Code
3
50
VARCHAR2
Yes
COL2
EDX_RPT_STATEMENT_ADJUST_FACT
ADJUSTMENT_TYPE_KEY
1200
Adjustment Amount
4
16,2
NUMBER
Yes
COL3
EDX_RPT_STATEMENT_ADJUST_FACT
ADJUSTMENT_AMOUNT
1200
Adjustment Date
5
8
DATE
No
COL4
EDX_RPT_STATEMENT_ADJUST_FACT
ADJUSTMENT_DATE
1200
Service Number
6
20
VARCHAR2
No
COL5
EDX_RPT_STATEMENT_ADJUST_FACT
SERVICE_NUMBER
1200
Adjustment Note
7
255
VARCHAR2
No
COL6
EDX_RPT_STATEMENT_ADJUST_FACT
ADJUSTMENT_NOTE
Format
    
Implementation Guide for Oracle Self-Service E-Billing Copyright © 2012, Oracle and/or its affiliates. All rights reserved. Legal Notices.