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.

Segregating Unbilled Revenue by G/L Cycle within a Billing Cycle

By default, the G/L report for the unbilled revenue always considers the journal created time to determine if the revenue was unbilled until the end of the G/L cycles. As revenue for the same item across multiple ledger cycles is aggregated on the same journal entry, the G/L report shows the unbilled revenue as follows:

  • If there is a delay in loading the usage events across multiple ledger reporting cycles, the journal entry for these usage events is created in the new G/L cycle. The unbilled revenue report for the prior G/L cycle does not include these usage events. If an item is created in prior G/L cycle and a usage event for that item occurs in the current G/L cycle, then the usage revenue is considered as part of the prior G/L cycle. The unbilled revenue report for the prior G/L cycle includes this usage event revenue

Enable the SegregateJournalsByGLPeriod business parameter to segregate revenue for the same item across multiple general ledger (G/L) cycle by creating a separate journal entry for each of these G/L cycles for this item. This ensures the following:

  • If there is a delay in loading the usage events across multiple ledger reporting cycles, the journal entry for these usage events is created in the prior G/L cycle the event occurred in. The unbilled revenue report for the prior G/L cycle includes these usage events.

  • If an item is created in the prior G/L cycle and a usage event for that item occurs in the current G/L cycle, then the usage revenue is considered as part of the current G/L cycle. The unbilled revenue report for the prior G/L cycle does not include this usage event.

Note:

When the SegregateJournalsByGLPeriod business parameter is enabled, the number of journal entries in the BRM system increases.

BRM makes the journal effective in the G/L cycle in which the event's endtime falls. In the case of delayed and backdated events that are processed after billing or after GL posting, the event processing time is used to determine the GL period the journal belongs to.

Important:

When the SegregateJournalsByGLPeriod business parameter is enabled:
  • BRM assumes that a single G/L reporting cycle is used for all G/L segments in the system.

  • The future journals will be created based on the G/L calendar but existing journals will remain same.

  • If the existing G/L cycle start dates are modified in the /config/gl_calendar object, then future journal creation will be based on the new calendar. However, the previously created journals will remain unchanged and does not reflect the new calendar.

To segregate the unbilled revenue report for G/L cycle:

  1. Go to the BRM_Home/sys/data/config directory.

  2. Run the following command, which creates an editable XML file from the billing instance of the /config/business_params object:

    pin_bus_params -r BusParamsBilling bus_params_billing.xml
      
    

    This command creates the XML file named bus_params_billing.xml.out in your working directory. To place this file in a different directory, specify the path as part of the file name.

  3. Open the bus_params_billing.xml.out file.

  4. Search for the following line:

    <SegregateJournalsByG/LPeriod>disabled</SegregateJournalsByG/LPeriod>  
    

    Note:

    Add this entry in the bus_params_billing.xml.out file if it does not already exist.
  5. Change disabled to enabled.

  6. Save this file as bus_params_billing.xml.

  7. Go to the BRM_Home/sys/data/config directory, which includes support files used by the pin_bus_params utility.

  8. Run the following command, which loads this change into the /config/business_params object:

    pin_bus_params PathToWorkingDirectory/bus_params_billing.xml
      
    

    where PathToWorkingDirectory is the directory in which bus_params_billing.xml resides.

    Caution:

    BRM uses the XML in this file to overwrite the existing billing instance of the /config/business_params object. If you delete or modify any other parameters in the file, these changes affect the associated aspects of the BRM billing configuration.

    Note:

    To run this command from a different directory, see "pin_bus_params" in BRM Developer's Guide.
  9. Read the object with the testnap utility or Object Browser to verify that all fields are correct.

    See "Using testnap" in BRM Developer's Guide for general instructions on using the testnap utility. See "Reading Objects by Using Object Browser" in BRM Developer's Guide for information on how to use Object Browser.

  10. Stop and restart the CM. For more information, see "Starting and Stopping the BRM System" in BRM System Administrator's Guide.

  11. (Multischema systems only) Run the pin_multidb script with the -R CONFIG parameter. For more information, see "pin_multidb" in BRM System Administrator's Guide.

Setting Up G/L Calendar Configuration

When the SegregateJournalsByGLPeriod business parameter is enabled, BRM uses the /config/gl_calendar object to get information related to the G/L reporting cycle.

This configuration includes an array of timestamps that indicates the start dates of the G/L cycle. For example, if the G/L reporting cycle starts on the third of every month, the configuration contains 12 timestamps for each year, each indicating the third day of the month.

Note:

Ensure that the current G/L cycle start date is part of this configuration. The timestamps should match the appropriate time zone of the BRM server.

To load data for the unbilled earned revenue report, you need to manually configure the /config/gl_calendarobject using the PCM_OP_CREATE_OBJ opcode.

To create an object using PCM_OP_CREATE_OBJ opcode, see "Creating Objects" in BRM Developer's Guide.

Following is the sample flist to populate monthly G/L cycle start timestamps for three months in the US Pacific time zone.

0    PIN_FLD_POID                POID [0] 0.0.0.1 /config/gl_calendar -1 0
0     PIN_FLD_ACCOUNT_OBJ           POID [0] 0.0.0.1 /account 1 0
0     PIN_FLD_HOSTNAME          STR [0] "<any hostname>"
0     PIN_FLD_NAME             STR [0] "GL_CALENDAR"
0     PIN_FLD_PROGRAM_NAME          STR [0] "<any program name>"
0 PIN_FLD_CALENDAR_DATE             ARRAY [1] allocated 1, used 1
1        PIN_FLD_EFFECTIVE_START_T               TSTAMP [0] (1420099200) 01/01/2015 00:00:00:000 AM
0 PIN_FLD_CALENDAR_DATE           ARRAY [2] allocated 1, used 1
1  PIN_FLD_EFFECTIVE_START_T          TSTAMP [0] (1422777600) 01/02/2015 00:00:00:000 AM
0 PIN_FLD_CALENDAR_DATE             ARRAY [3] allocated 1, used 1
1  PIN_FLD_EFFECTIVE_START_T          TSTAMP [0] (1425196800) 01/03/2015 00:00:00:000 AM

After loading, you can read the configuration object with the testnap utility or Object Browser to verify that all fields are correct.

See "Using testnap" in BRM Developer's Guide for general instructions on using the testnap utility. See "Reading Objects by Using Object Browser" in BRM Developer's Guide for information on how to use Object Browser.

Note:

If you want to modify the /config/gl_calendar object, delete the existing object using PCM_OP_DELETE_OBJ opcode and reload using PCM_OP_CREATE_OBJ opcode. See "Deleting Objects" in BRM Developer's Guide.

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.