Skip Headers
Oracle® Communications Billing and Revenue Management Collecting General Ledger Data
Release 7.5

E16704-06
Go to Documentation Home
Home
Go to Table of Contents
Contents
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

4 Creating General Ledger (G/L) Reports

This chapter describes how to create Oracle Communications Billing and Revenue Management (BRM) General Ledger (G/L) reports to your accounting software.

Before reading this chapter, read "About Collecting General Ledger Data".

About Running G/L Reports

Use the pin_ledger_report utility to extract data from BRM and G/L reports. This utility searches the BRM database for /journal objects that include summaries of balance impacts for which you want to record G/L data. The report then either displays the data or writes it to the BRM database.

You can generate reports for a variety of options, including those run for specific types of revenue recognition. Your accounting department determines which types of revenue to include in reports, for example, billed, unbilled, earned, or unearned and how often you run G/L reports. See "About Revenue Recognition" for more information.

About Exporting G/L Reports to XML Files

You use the pin_ledger_report utility to export G/L reports to XML files. This enables you to import the G/L reports into an external G/L system. When you run the pin_ledger_report utility in export mode, G/L reports are automatically generated, posted, and saved to the BRM database before they are exported. You do not need to perform these tasks independently before you export them.

See "Exporting General Ledger (G/L) Reports to XML Files" for more information.

Note:

You can also write a custom program to export your G/L data to a format compatible with your external system. For more information, see "Exporting G/L Information".

About the pin_ledger_report Utility

Use the pin_ledger_report utility to generate G/L reports in these modes:

  • run_report: This mode runs the report. You can choose to display the report or to print it to a file. You can choose to post the report or not.

    See "Generating a G/L Report".

  • post_only: This mode writes G/L report data to the BRM database. It prevents BRM from backdating events that occurred before the posting date. You can also use it to unpost the last posted report.

    See "Posting G/L Reports".

  • list_previous: This mode lists previously run G/L reports.

    See "Listing Previously-Posted G/L Reports".

  • export: This mode generates the report, posts it to the database, and exports it to XML files that can be imported into an external G/L system. If you plan on exporting your G/L reports, you do not need to create them first; the export operation does that for you. See "Exporting General Ledger (G/L) Reports to XML Files" for more information.

The pin_ledger_report utility is located in the BRM_Home/bin directory. For complete syntax information, see "pin_ledger_report".

Controlling the Destination of pin_ledger_report Output

To display or save the output from pin_ledger_report you can:

  • Save a report as an object in the BRM database by running pin_ledger_report without the -test option.

Storing the data in the BRM database can help protect the integrity of your accounting data.

  • Save a report to an ASCII file by running pin_ledger_report with the -report option and redirect the output to a file name.

  • Display a report by running pin_ledger_report with the -report option.

Generating a G/L Report

To generate a G/L report:

Creating an Index for the POID_TYPE Column of the ITEM_T Table

If you are creating a 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/password@databaseAlias
  
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.

Running Reports with the pin_ledger_report Utility

To create a G/L report, you run the pin_ledger_report utility in the run_report mode. The syntax for this mode is as follows:

pin_ledger_report -mode run_report
                  -start start_date
                 [-end end_date]
                 [-segment gl_segment]
                 [-type billed | unbilled 
                        billed_earned | billed_unearned |
                        unbilled_earned | unbilled_unearned |
                        prev_billed_earned] 
                 [-report file_name]
                 [-summary | -detail]
                 [-noncurrency]
                 [-verbose]
                 [-test]
                 [-posted]
                 [-help]
  

See "pin_ledger_report" for more information.

The only required parameter is the -start parameter. This command runs a G/L report on events that occurred from May 25 to the current date:

pin_ledger_report -mode run_report -start 05/25/03
  

Example: Reporting G/L revenue for a specific time period

To report G/L revenue for a specific time period, use the -start and -end options. For example:

pin_ledger_report -mode run_report -start 05/25/03 -end 05/31/03
  

Example: Reporting G/L revenue about a specific type of revenue

To report G/L revenue about a specific type of revenue, use the -type options. For example, to generate a report for the unbilled revenue type:

pin_ledger_report -mode run_report -start 05/25/03 -end 05/31/03 
                  -type unbilled
  

Example: Reporting G/L revenue about single or multiple G/L segments and brands

To run a G/L report for a specific segment, use the -segment option. The following example generates a G/L report for the East Coast Enterprises segment:

pin_ledger_report -mode run_report -start 05/25/03 -end 05/31/03 -segment EastCoastEnterprises

Posting G/L Reports

To post specific data in a G/L report, run the pin_ledger_report utility in the post_only mode. The syntax for the post_only mode is as follows:

pin_ledger_report -mode post_only
                  [-verbose] 
                  [-segment gl_segment] 
                  [-posted posted_date | -unpost]
                  [-help]
  

See "pin_ledger_report" for more information.

Example: Posting a Previously Run G/L Report

To post a previously run G/L report, use the -posted and -segment options. The example below posts the G/L report run on 6/11/00 for the root segment:

pin_ledger_report -mode post_only -posted 06/11/03 -segment 

Example: Unposting a G/L report

To unpost a previously run G/L report, use the -unpost option.

Note:

You can only unpost the last previous post. For example, if you post on January 1 and February 1, you can unpost the February 1 post, but not the January 1 post.

The example below unposts the G/L report for the G/L segment EastCoastEnterprises:

pin_ledger_report -mode post_only -unpost -segment EastCoastEnterprises

Listing Previously-Posted G/L Reports

To display a list of previously run G/L reports, run the pin_ledger_report utility in the list_previous mode as follows:

pin_ledger_report -mode list_previous 
                  -start start_date 
                  [-end end_date]
                  [-segment gl_segment] 
                  [-type billed | unbilled 
                         billed_earned | billed_unearned |
                         unbilled_earned | unbilled_unearned |
                         prev_billed_earned] 
                 [-verbose]
                 [-help]
  

See "pin_ledger_report" for more information.

Example: Listing G/L Reports for a Specified Time Period

To show a list of previously run G/L reports during a specific time period, use the -start and -end options. The example below lists G/L reports that were run between 5/25/03 and 5/31/03:

pin_ledger_report -mode list_previous -start 05/25/03 -end 05/31/03

Example: Listing G/L Reports for a Specific Segment or Brand

To show a list of previously run G/L reports for a specific segment of accounts or brand, use the -segment option. The following example lists G/L reports that were run for the brand, East Coast Enterprises:

pin_ledger_report -mode list_previous -start 05/25/00 -segment EastCoastEnterprises

Listing G/L Reports for Specific Types of Revenue

To show a list of previously run G/L reports for a specific type of revenue, use the -type option. By default, the utility generates reports for all types of revenue. The example below lists G/L reports that were run to show unbilled revenue:

pin_ledger_report -mode list_previous -start 05/25/00 -type unbilled

Exporting G/L Reports to XML files

To export G/L reports to XML files, run the pin_ledger_report utility in the export mode:

pin_ledger_report  -mode export 
                   [-segment gl_segment]
                   [-resend ReportId] 
                   [-regenerate ReportId] 
                   [-restart] 
                   [-verbose] 
                   [-help]
  

When you run the pin_ledger_report utility in export mode, G/L reports are automatically generated, posted, and saved to the BRM database before they are exported. You do not need to perform these tasks independently before you export them.

See "Exporting General Ledger (G/L) Reports to XML Files" and "pin_ledger_report" for more information.

Setting the Number of A/R Accounts per G/L Report

To make the pin_ledger_report utility more scalable, you can specify the number of accounts receivable (A/R) accounts to include in a G/L report. To change the default transaction_grouping entry for pin_ledger_report, you must edit the pin_billd configuration file. Follow these steps:

  1. Open the configuration file for BRM billing applications and utilities (BRM_Home/apps/pin_billd).

  2. Edit the following pin.conf entry:

    pin_ledger_report     transaction_grouping     100000
      
    
  3. Save the pin.conf file.

Exporting G/L Information

You can export G/L information from BRM in the following ways:

  • Configure your G/L data for export and run the pin_ledger_report utility with the -export parameter to generate the XML files. Then, write a program to parse the XML output files to a format your accounting system can read. This is the recommended method for exporting G/L information. See "Exporting General Ledger (G/L) Reports to XML Files" for more information.

  • Write a BRM client program to read the ledger_report object and write the output to a file that is loaded into your accounting system. You then run the pin_ledger_report utility without the -test option to create a ledger_report object in the BRM database.

  • Write a program to parse the redirected output from pin_ledger_report to a format your accounting system can read.