This chapter describes how to create Oracle Communications Billing and Revenue Management (BRM) general ledger reports.
Before reading this chapter, read "About Collecting General Ledger Data".
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.
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 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".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.
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.
list_previous: This mode lists previously run 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 mode does that for you. See "Exporting General Ledger 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".
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.
To generate a G/L report:
If you are creating a report for billed revenue, create an index on the ITEM_T table's POID_TYPE column prior to running pin_ledger_report. See "Creating an Index for the POID_TYPE Column of the ITEM_T Table".
Run the pin_ledger_report utility in run_report mode. See "Running Reports with the pin_ledger_report Utility".
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 and drop it after all runs of pin_ledger_report have completed.
To create the index, run the following commands:
% sqlplus pin/password@databaseAlias SQL> create index i_item__poid_type on item_t (poid_type) tablespace tablespaceName storageParameters partitionInfo
Note:
Specify [partitionInfo] only if the ITEM_T table is partitioned.To create a G/L report, run the following command:
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.
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
To post specific data in a G/L report, run the following command:
pin_ledger_report -mode post_only [-verbose] [-segment gl_segment] [-posted posted_date | -unpost] [-help]
See "pin_ledger_report" for more information.
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
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
To display a list of previously run G/L reports, run the following command:
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.
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
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
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
To export G/L reports to XML files, run the following command:
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 Reports to XML Files" and "pin_ledger_report" for more information.
To make the pin_ledger_report utility more scalable, 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:
Open the (BRM_home/apps/pin_billd).
Edit the following entry:
pin_ledger_report transaction_grouping 100000
Save the file.
By default, the G/L report for unbilled revenue considers the time the journal was created to determine if the revenue was unbilled until the end of the G/L cycles. Because 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 the prior G/L cycle and a usage event for that item occurs in the current G/L cycle, 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.
Segregate unbilled business parameter to segregate revenue for the same item across multiple G/L cycles 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 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, 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:
Segregating unbilled revenue increases the number of journal in the BRM system.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 G/L posting, the event processing determines the G/L 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, future journal creation will be based on the new calendar. However, the previously created journals will remain unchanged and do not reflect the new calendar.
To segregate the unbilled revenue report for G/L cycle:
Go to the BRM_Home/sys/data/config directory.
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.
Open the bus_params_billing.xml.out file.
Search for the following line:
<SegregateJournalsByGLPeriod>disabled</SegregateJournalsByGLPeriod>
Note:
Add this entry in the bus_params_billing.xml.out file if it does not already exist.Change disabled to enabled.
Save this file as bus_params_billing.xml.
Go to the BRM_Home/sys/data/config directory, which includes support files used by the pin_bus_params utility.
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.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.
Stop and restart the CM.
(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.
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 must match the appropriate time zone of the BRM server.To load data for the unbilled earned revenue report, manually configure the /config/gl_calendar object using the PCM_OP_CREATE_OBJ opcode.
To create an object using the PCM_OP_CREATE_OBJ opcode, see "Creating Objects" in BRM Developer's Guide.
Following is a 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, 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 the PCM_OP_DELETE_OBJ opcode and reload it using the PCM_OP_CREATE_OBJ opcode. See "Deleting Objects" in BRM Developer's Guide.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 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.