6 Generating General Ledger Reports

Learn how to generate general ledger (G/L) reports in Oracle Communications Billing and Revenue Management (BRM).

Topics in this document:

• About G/L Reports

• Overview of the G/L Report Process

• Running G/L Jobs in Business Operations Center

• Generating G/L Reports Manually

About G/L Reports

G/L reports summarize the debits and credits to your financial accounts during a given period. In BRM, you can generate reports for previewing your G/L data, reports that you can post to your corporate general ledger, and individual reports for each revenue type.

Figure 6-1 shows an example of the data that is included in preview reports.

Figure 6-1 Sample G/L Preview Report

Description of "Figure 6-1 Sample G/L Preview Report"

About the Monthly Export Report

If you are using a deliverable-based revenue recognition scheme, the monthly export report is the one that you post to your corporate general ledger on your external financial system. The report includes aggregated data about your unbilled, billed, and earned revenue by general ledger account. The report covers data for one month, starting on a set day of the month such as the 5th.

Note:

Because the report aggregates data by general ledger account, the report will always display 0 in the G/L ID field.

When you generate a monthly export report, it is saved to the BRM database and also to an XML output file for posting to your corporate general ledger.

The monthly export report is marked internally as having been generated for that month. This ensures that:

• Only one report XML output file is created for each month. This prevents double-counting in the corporate general ledger on your external financial system. A report XML output file is created only when one does not already exist for a specific month.

• A report XML output file is created for every month. When you generate a monthly export report, a report XML output file is automatically generated for any previous months that you have missed.

  For example, assume your monthly reports start on the 1st, and there are XML files for January and February. When you generate a monthly export report on May 10, two XML output files are created: one for March and one for April.

To ensure that your monthly export reports include the revenue for an entire month, schedule them to run at least 2 days after your G/L reports' starting day of month. For example, if the starting day of month is the 5th, schedule your G/L reports to run on the 7th or later. This provides time for BRM to process transactions that tend to consolidate at the end of the reporting period, such as contract cancellations and expirations.

About the Preview G/L Report

You use the preview report to validate your G/L data before creating the monthly export report. The preview G/L report contains the same data as your monthly export report, but it covers a different period of time and should not be posted to your corporate general ledger.

Note:

Because the preview report aggregates data by general ledger account, the report will always display 0 in the G/L ID field.

A preview G/L report covers data from the end date of your last monthly export report to the day the report is generated (exclusive). For example, assume that the last monthly export report that you generated was for Feb 5 through Mar 4. If you generate a preview report on:

• Mar 20th: The report contains data from Mar 5 through Mar 19.

• Mar 30th: The report contains data from Mar 5 through Mar 29.

• Apr 10th: The report contains data from Mar 5 through Apr 9.

When you generate preview reports, they are saved in /ledger_report objects in the BRM database and can be viewed in Oracle Analytics Publisher or BI Publisher. The reports are also exported to an XML output file, so you can view them in an external reporting system.

Caution:

Do not post preview G/L reports to the corporate general ledger on your external financial system. Doing so could cause double-counting in your corporate general ledger.

About Individual G/L Reports

If you are using an event-based revenue recognition scheme, you generate individual G/L reports for each type of revenue, such as unbilled revenue, unbilled unearned revenue, unbilled earned revenue, and so on. Each report covers data starting and ending on the dates you specify.

When you generate individual G/L reports, they are saved in /ledger_report objects in the BRM database and are also exported to an XML output file.

Overview of the G/L Report Process

The process for generating and posting your general ledger data involves these high-level steps:

1. Configuring the file naming convention and location for your G/L report XML output files. See "Configuring General Ledger Report XML Output Files".

2. Generating G/L reports in one of the following ways:

   • Using the Business Operations Center application. See "Running G/L Jobs in Business Operations Center".

   • Manually running the pin_ledger_report utility. See "Generating G/L Reports Manually".

   • Using a custom client application. To do so, configure your client to call the PCM_OP_JOB_EXECUTE opcode. See "Running Business Operations Jobs" in BRM Opcode Guide.

3. Previewing your reports in Oracle Analytics Publisher or BI Publisher to validate that your G/L setup is generating expected results.

4. Retrieving your G/L report XML output files. See the following:

   • About Preview and Monthly Export Report XML Output Files

   • About Individual Report XML Output Files

5. Uploading the report XML output files to your external financial system and then posting them to your corporate general ledger.

Running G/L Jobs in Business Operations Center

You can generate G/L reports by running a general ledger job in Business Operations Center.

You can specify to generate G/L reports based on the purpose for the report:

• Preview: Generates a report for previewing your G/L data.

• Export for Posting: Generates your monthly export report for deliverable-based revenue recognition.

• Individual: Generates a separate G/L report for each type of revenue that you pick. Use this report for event-based revenue recognition.

  You can generate individual reports based on these attributes:

  • Reporting Period: Includes revenue from bills that were created between the specified dates.

    For one-time jobs, you choose specific dates for the start and end of the reporting period, such as September 10 through September 30.

    For repeating jobs, you can adjust the reporting period by adding or subtracting days relative to the day the job runs. For example, if the general ledger job runs on the 30th day of the month, you can specify to end the reporting period one day earlier, on the 29th day of the month.

  • Types of Reports to Generate: Generates separate reports for one or more of these revenue types: unbilled, unbilled earned, unbilled unearned, previous billed earned, billed, billed earned, and billed unearned.

  • Include in Report: Specifies whether to include currency amounts, noncurrency amounts, or both. If you select breakdown by charge type, your reports will include the totals for each type of charge.

  • Post G/L revenue: Posts G/L data to prevent backdating events that occurred before the posting date.

    You typically run a G/L report without posting when you want to see the data before the end of the normal G/L cycle. Your regularly scheduled G/L reports should post data.

For more information, see "Generating General Ledger Reports" in Business Operations Center Help.

Generating G/L Reports Manually

You can generate G/L reports manually by running the pin_ledger_report utility. You can use the utility to do the following:

• Create a preview report for deliverable-based revenue recognition. See "Creating Preview G/L Reports with pin_ledger_report".

• Create your monthly export report for deliverable-based revenue recognition. See "Creating Monthly Export G/L Reports with pin_ledger_report".

• Create individual reports for event-based revenue recognition. See "Creating Individual G/L Reports with pin_ledger_report".

For more information about the utility, see "pin_ledger_report".

Creating Preview G/L Reports with pin_ledger_report

If you are using deliverable-based revenue recognition, you can create a preview report for validating your G/L data.

To create a preview G/L report, run the following command:

pin_ledger_report -mode export -preview

The utility saves your preview report to the database, so you can view it in Oracle Analytics Publisher or BI Publisher. It also exports the preview report to an XML file using the file name and location you specified in the pin_config_export_gl.xml file. See "Configuring Preview and Monthly Report XML Output Files".

Creating Monthly Export G/L Reports with pin_ledger_report

If you are using deliverable-based revenue recognition, you can create a monthly export G/L report that you can post to the corporate general ledger on your external financial system.

To create a monthly export G/L report, run the following command:

pin_ledger_report -mode export -aggregate

The utility exports the report to an XML file using the file name and location you specified in the pin_config_export_gl.xml file. See "Configuring Preview and Monthly Report XML Output Files".

Creating Individual G/L Reports with pin_ledger_report

If you are using event-based revenue recognition, you can create an individual G/L report for each revenue type specified in your pin_config_export_gl.xml file. For example, you could create separate G/L reports for billed revenue, unbilled revenue, billed unearned revenue, and so on.

The options for creating individual reports are:

• To create an individual G/L report that you can view in Oracle Analytics Publisher or BI Publisher, run pin_ledger_report in the run_report mode. For example, the following command creates a report on events that occurred from May 25 to the current date:

  pin_ledger_report -mode run_report -start 06/25/20

  For more information, see "run_report Mode Syntax".

• To export individual reports to XML files, run pin_ledger_report in the export mode. For example, the following command creates an XML report file for the EastCoastEnterprises segment:

  pin_ledger_report -mode export -segment EastCoastEnterprises

  For more information, see "export Mode Syntax".

• To post G/L data to maintain the validity of revenue data, run pin_ledger_report in the post_only mode. For example, the following command posts data up to the current date for the root segment:

  pin_ledger_report -mode post_only

  Posting G/L data prevents BRM from backdating events that occurred before the posting date; for example, by performing backdated accounts receivable operations.

  For more information, see "post_only Mode Syntax".

• To display a list of previously run reports, run pin_ledger_report in the list_previous mode. For example, the following command lists the reports that were created from June 25 to the current date:

  pin_ledger_report -mode list_previously -start 06/25/20

  For more information, see "list_previous Mode Syntax".

Requirement for Creating a Report for Billed Revenue

If you are creating an individual G/L report for billed revenue, you must create an index named i_item__poid_type on the ITEM_T table's POID_TYPE column. This index is required for billed revenue G/L reports only and is not used by other applications. The BRM installer does not create this index, so that it does not impact the performance of other applications.

To prevent the index from impacting performance, create the i_item__poid_type index prior to running pin_ledger_report and then drop the index after the report is generated. If you need to run pin_ledger_report multiple times, create the index just once and drop it after all runs of pin_ledger_report have completed.

To create the index, enter the following command:

sqlplus pin/@databaseAlias
Enter password: password

SQL> create index i_item__poid_type on item_t (poid_type) tablespace tablespaceName storageParameters partitionInfo

Note:

You need to specify partitionInfo only if the ITEM_T table is partitioned.