|
Implementation Guide for Oracle Self-Service E-Billing > Customizing Billing Statements >
Defining Billing Periods in the OLAP Database
To use Oracle Self-Service E-Billing to process bills, you must prepopulate the period and aggregate dimension database tables with billing period data. You must prepopulate the period tables as a new implementation, and then again periodically to add additional periods for ongoing use by the Billing and Payment application. In the Oracle Self-Service E-Billing OLAP database, period dimension tables require an entry for each monthly billing period start date and end date. You use a stored procedure to prepopulate the billing tables, and you specify the billing start and end dates, number of years to populate, and other information. This information is used as a period key for partitioning statement fact records. Aggregate billing period dimension tables also require entries for each billing quarter and year. You must also use a stored procedure to synchronize this data in the OLTP database. Table 9 describes the period-related database tables in the Oracle Self-Service E-Billing database.
Table 9. Period-Related Database Tables
|
|
|
EDX_RPT_PERIOD_DIM |
OLAP |
Contains entries for each billing period start and end date. |
EDX_RPT_AGGREGATE_PERIOD_DIM |
OLAP |
Contains quarterly and yearly aggregation master dimension data. |
EDX_RPT_AGGREGATE_PERIOD_LINK |
OLAP |
Contains links between the aggregation table, EDX_RPT_AGGREGATE_PERIOD_DIM, and the entries in the period dimension table, EDX_RPT_PERIOD_DIM. |
EDX_OMF_PERIOD |
OLTP |
Contains OLTP synchronization data for the entries in the OLAP period dimension table, EDX_RPT_PERIOD_DIM. |
To define billing periods in the OLAP database
- Locate the pr_period_dim_population.sql script in the following directory:
- UNIX.
EDX_HOME/db/oracle/olap/Scripts
- Windows.
EDX_HOME\db\oracle\olap\Scripts
- Run the stored procedure, populate_period_dim, in the OLAP schema, specifying the input parameters described in Table 10.
The following example shows the populate_period_dim procedure:
BEGIN
populate_period_dim(p_first_period_start_date => 01/03/2013,
p_first_period_end_date => 02/02/2013,
p_number_of_years => 10,
p_delete_existing_entries => 'Y',
p_first_quarter_start_date => 02/01);
END;
In the example, the parameters indicate the following:
- 01/03/2013. Specifies that the start date of the first billing period is 01/03/2013, and indicates that all subsequent billing periods will start on the third day of each calendar month.
- 02/02/2013. Specifies that the end date of the first billing period is 02/02/2013, and indicates that all subsequent billing periods will end on the second day of each calendar month.
- 10. Specifies to populate ten years of billing periods, starting at 01/03/2013.
- Y. Specifies to delete all existing entries from period and aggregation dimension tables before adding new period entries.
- 02/01. Specifies that the first quarter start date is 02/01, indicating that the first quarter is 02/01 through 04/30, the second quarter is 05/01 through 07/31, and so on.
- Synchronize the entries in the EDX_RPT_PERIOD_DIM table to the OLTP period table, EDX_OMF_PERIOD. Locate the pr_oltp_period_sync.sql script in the following directory, and run the stored procedure oltp_period_sync in the OLTP schema:
Table 10 describes the input parameters for the stored procedure, populate_period_dim, for populating billing period data in the OLAP schema.
Table 10. Input Parameters for the populate_period_dim Stored Procedure
|
|
|
|
P_FIRST_PERIOD_
START_DATE |
VARCHAR2 |
MM/DD/YYYY |
Specifies the start date of the first billing period. Each subsequent billing period entry that the stored procedure adds to the period dimension table, EDX_RPT_PERIOD_DIM, will start on this day of the month. |
P_FIRST_PERIOD_
END_DATE |
VARCHAR2 |
MM/DD/YYYY |
Specifies the end date of the first billing period. Each subsequent billing period entry that the stored procedure adds to the period dimension table, EDX_RPT_PERIOD_DIM, will end on this day of the month. |
P_NUMBER_OF_
YEARS |
NUMBER |
None |
Indicates the number of years of billing periods to populate in the billing period and aggregate dimension tables. For example, if you specify a value of 5, then the stored procedure adds 60 monthly billing period entries to the EDX_RPT_PERIOD_DIM table starting from the first period start date, P_FIRST_PERIOD_START_DATE. The procedure also adds 20 quarter billing entries and 5 yearly aggregation entries to the EDX_RPT_AGGREGATE_PERIOD_DIM table, starting from the first quarter start date, P_FIRST_QUARTER_START_DATE. |
P_DELETE_
EXISTING_
ENTRIES |
VARCHAR2 |
Y or N |
Specifies whether to delete all existing entries from the billing period dimension tables before adding new entries as specified by your input. Specify Y only if you are prepopulating dimension tables at the beginning of an implementation. This option deletes all entries from the tables before adding new ones. Specify N if the dimension tables are already populated and you want to add additional entries for the coming years as specified by your input. This option does not delete existing data from the tables. |
P_FIRST_QUARTER_|START_DATE |
VARCHAR2 |
MM/DD |
The start date of the first billing quarter, used to populate quarter and yearly aggregation periods in the EDX_RPT_AGGREGATE_PERIOD_DIM table. After the first quarter, subsequent quarter entries that the stored procedure adds to the aggregation table will start at three-month increments from this day. For example, 01/01 indicates that the first quarter starts on 01/01 and ends on 03/31, the second quarter entry starts on 04/01 and ends on 06/30, and so on. For each yearly and quarterly aggregation based on the first quarter start date, the procedure also populates associated period keys in the aggregation period link table, EDX_RPT_AGGREGATE_PERIOD_LINK. |
|
