This chapter describes how to create Oracle Communications Billing and Revenue Management (BRM) general ledger (G/L) reports and export them to XML files that can be loaded into your external G/L system.
Before reading this chapter, you should be familiar with BRM general ledger data. See "About Collecting General Ledger Data".
To export G/L reports to XML files, run pin_ledger_report in export mode as follows:
pin_ledger_report -mode export [-segment gl_segment] [-resend ReportId] [-regenerate ReportId] [-restart] [-verbose] [-help]
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.
To configure G/L reports for export, you use the pin_config_export_gl.xml file. This file contains the settings for all reports that get exported to XML files. For more information, see "Configuring G/L Reports for Export".
Depending on your G/L report configuration, BRM generates the following G/L reports (/ledger_report objects) in the database for a given reporting period:
A basic G/L report. See "Generating a G/L Report".
A customized G/L report, when the PCM_OP_GL_POL_EXPORT_GL policy opcode performs custom processing. See "Customizing G/L Reports for Export".
An incremental report, when the revenue type is unbilled, unbilled unearned, unbilled earned, or billed unearned. See "About Incremental Reports and Cumulative Revenue".
Note:
When you are reporting in export mode, the previously earned revenue is reported only if you have configured the report to include the previously billed earned revenue. For more information on setting up your G/L reports for export, see "Configuring G/L Reports for Export".When you export G/L reports for the following revenue types, the reports include cumulative revenue across reporting periods:
Unbilled
Unbilled earned
Unbilled unearned
Billed unearned
For information about generating G/L reports, see "Generating a G/L Report."
G/L report displays cumulative revenue for a specified revenue type accumulated from the start date of the first reporting period to the end date of the current reporting period. Part of the total revenue reported for the current period may be included in the same report for the previous period.
When you run the pin_ledger_report utility in export mode, it automatically calculates the difference between the revenue reported in the current reporting period for a G/L segment and the revenue reported in the previous reporting period for the same G/L segment. (The end date for the previous report is the start date for the current report.) The difference, or increment, is the amount of revenue accumulated during the current reporting period. Thus, exported G/L reports contain the incremental revenue amounts; you do not need to calculate them manually.
For example, an unbilled report for the period January 1 to February 1 specifies an unbilled amount of $100 as of February 1. This is published to a financial system as $100 of unbilled charges (assuming it is the first ever reporting period).
For the period from February 1 to March 1, a new charge of $50 is applied and $30 of the original charge of $100 got billed. The unbilled report for the period from February 1 to March 1 specifies the amount of unbilled amount as of March 1. When you generate the base report in -run_report mode, the total unbilled amount is:$100 + $50 - $30 = $120This is published to the financial system as an increment of $20 ($120 - $100), so that the net amount in the financial system is:$100 + $20 = $120The reason for publishing incremental amounts is that multiple source systems could be publishing unbilled amount into the same financial system. If you publish the cumulative amount, the net amount in the financial system would be:$100 + $120 = $220All source systems should publish the incremental amounts (positive or negative), and not the latest cumulative amounts.
For information on revenue recognition, see "About Revenue Recognition".
When you export G/L reports for nested segments and the revenue type is unbilled, unbilled unearned, unbilled earned, or billed unearned (cumulative revenue), the segment you report on must be the same from reporting period to reporting period. For example, if a January G/L report calculates unbilled revenue for the .westcoast.california G/L segment, the February G/L report must calculate unbilled revenue for the .westcoast.california G/L segment. If, instead, the February report calculates unbilled revenue for the .westcoast G/L segment or the .westcoast.california.sf G/L segment, the G/L data overlaps and invalid revenue is reported.
Note:
You can specify overlapping segments for snapshot reports; however, it is not recommended.For information on revenue recognition, see "About Revenue Recognition".
For information on creating G/L segments, see "Creating General Ledger IDs".
To ensure data integrity within the BRM system and between BRM and your external G/L system, you may need to regenerate or re-export G/L reports that you exported previously. For example:
When you make changes to the G/L data in the BRM database after you generated the report for that G/L data
In such cases, running the pin_ledger_report utility in export mode with the -regenerate parameter:
Recreates the G/L report in the database
Reposts the G/L report
Recreates the XML export files
If you regenerate a G/L report that was not the last report generated, you must also regenerate all subsequent reports to ensure the data is accurate. For example, if you need to regenerate a report for March, and reports exist for April and May, you should regenerate the April and May reports.
Important:
A regenerated report does not correct G/L data that was previously imported into the external G/L system. You must reverse the original reports in the external G/L system and then re-import the regenerated data to maintain data integrity.For more information, see "Example: Regenerating G/L Reports".
When the G/L data in the BRM database has not changed and the XML export files were either created incorrectly or lost before they could be imported into the external G/L system.
In such cases, running the pin_ledger_report utility in export mode with the -resend parameter recreates the XML export files. It does not recreate or repost the G/L report in the database.
For more information, see "Example: Re-Exporting G/L Reports".
Important:
Do not use the -regenerate or -resend parameter if the pin_ledger_report utility exits abnormally. Instead, use the -restart parameter. See "Example: Restarting a Failed Export Operation".The pin_ledger_report utility performs the following tasks when it runs:
Retrieves G/L report configuration information from the /config/export_gl object to determine which reports to generate and which G/L segments to report.
Creates a new /process_audit/export_gl object for this run and sets the status of the /process_audit/export_gl object to IN_PROGRESS.
Reads the previous /process_audit/export_gl object and the ReportInitialStartDate value in the /config/export_gl object to determine if any G/L reports for previous periods have not been created. If so, creates them.
Calls the PCM_OP_GL_LEDGER_REPORT opcode to create the /ledger_report object for the current period.
Using the POID of the generated /ledger_report object, calls the PCM_OP_GL_POL_EXPORT_GL policy opcode and then does one of the following, based on the PIN_FLD_RESULT value:
0: No customization; generates and exports the original report.
1: Customization. If the G/L data was updated, creates a new /ledger_report object in the database and exports the new report.
Note:
If the report contains cumulative revenue, generates an incremental report by using the G/L report for the previous period, if one exists, and updates the /ledger_report object.If specified in the export configuration file, filters noncurrency resources from the generated /ledger_report object to be included in or excluded from the XML output file.
Generates the XML output file.
Updates the /process_audit/export_gl object with information about this run.
Posts reports in the database.
Sets the status of the /process_audit/export_gl object to RUN_COMPLETED.
Note:
If the pin_ledger_report utility encounters errors during processing, it exits without completing the report processing. (One or more prior reports and one or more G/L export reports may have been successfully generated.)When you export G/L reports, the pin_ledger_report utility uses the information in the /config/export_gl object to extract the necessary G/L data from the database and save it to XML files. One XML file is created for each combination of G/L segment and revenue type defined in the export configuration file.
If you configure your G/L reports for the root segment, G/L data for all segments is reported on, except those that roll up to their parent segments. As many XML files will be created as there are report types, and each one will contain data for the entire root segment.
By default, the XML files are saved with the following naming convention:
RevenueType_ReportEndDate_ReportStartDate_ReportId.xml
where:
RevenueType is the abbreviation for the revenue type being reported, for example, ue for unbilled earned and be for billed earned.
ReportEndDate is the end date of the current report in YYYYMMDD format.
ReportStartDate is the start date for the current report in YYYYMMDD format.
ReportId is the ID of the generated report. For details, see "ReportId".
For example:
ue_20070731_20070630_0.0.0.1-123456-10.xml
Table 6-1 describes the XML tags in the G/L export files.
Table 6-1 XML Tags in G/L Reports
| XML Element | Description |
|---|---|
|
SourceSystemID |
The unique ID for the database containing the G/L data. |
|
The unique ID of the G/L report in the BRM system. It comprises the following information, separated by dashes: database number, POID of the /process_audit/export_gl object, and report number. For example, 0.0.0.1-123456-10. |
|
|
RevenueType |
Revenue type:
|
|
BRM_GL_Segment |
The name of the G/L segment being reported on. |
|
ReportCreatedTime |
Report run time, which includes the following information:
|
|
PeriodStartTime |
The start time of the revenue reporting period, which includes the following information:
|
|
PeriodEndTime |
The end time of the revenue reporting period, which includes the following information:
|
|
RevenueAmounts |
The revenue reported for a specific G/L segment. Revenue amounts are specified with the <element> attribute. Each element contains:
The revenue accounts are retrieved from the pin_glid file. For information on configuring this data, see "Loading General Ledger Configuration Data". |
The following XML output file shows an unbilled earned revenue report for the .westcoast.california.sf G/L segment. It contains revenue amounts for two balance impacts, one for monthly cycle fees and the other for international airtime minutes.
Note:
The G/L account names for each balance impact type are the default BRM G/L accounts (for example, ARGrossAccount and OffsetGrossAccount). You can use the PCM_OP_GL_POL_EXPORT_GL policy opcode to convert the BRM values to custom G/L account names before the XML files are created. See "Mapping BRM G/L Account Names to Third-Party Account Names".<GeneralLedgerReport> <SourceSystemID>123.45.123</SourceSystemID> <ReportId>0.0.0.1-1927591-6</ReportId> <RevenueType>"Unbilled earned"</RevenueType> <BRM_GL_Segment>.west.california.sf</BRM_GL_Segment> <ReportCreatedTime> <Year>2006</Year> <Month>07</Month> <Day>01</Day> <Hours>18</Hours> <Minutes>44</Minutes> <Seconds>21</Seconds> </ReportCreatedTime> <PeriodStartTime> <Year>2006</Year> <Month>06</Month> <Day>01</Day> <Hours>12</Hours> <Minutes>00</Minutes> <Seconds>00</Seconds> </PeriodStartTime> <PeriodEndTime> <Year>2006</Year> <Month>07</Month> <Day>01</Day> <Hours>00</Hours> <Minutes>00</Minutes> <Seconds>00</Seconds> </PeriodEndTime> <RevenueAmounts element="1"> <ResourceId>840</ResourceId> <BRM_GLId>102</BRM_GLId> <ARGrossAccount name="monthly.debit"> <Credit>0.0</Credit> <Debit>30.0</Debit> </ARGrossAccount> <ARDiscountAccount name="monthly.credit"> <Credit>0.0</Credit> <Debit>0.0</Debit> </ARDiscountAccount> <ARNetAccount name="monthly.debit"> <Credit>0.0</Credit> <Debit>30.0</Debit> </ARNetAccount> <ARTaxAccount name="tax.debit"> <Credit>0.0</Credit> <Debit>0.0</Debit> </ARTaxAccount> <OffsetGrossAccount name="monthly.credit"> <Credit>30.0</Credit> <Debit>0.0</Debit> </OffsetGrossAccount> <OffsetDiscountAccount name="monthly.debit"> <Credit>0.0</Credit> <Debit>0.0</Debit> </OffsetDiscountAccount> <OffsetNetAccount name="monthly.credit"> <Credit>30.0</Credit> <Debit>0.0</Debit> </OffsetNetAccount> <OffsetTaxAccount name="tax.credit"> <Credit>0.0</Credit> <Debit>0.0</Debit> </OffsetTaxAccount> </RevenueAmounts> <RevenueAmounts element="2"> <ResourceId>10002</ResourceId> <BRM_GLId>1421</BRM_GLId> <ARGrossAccount name="minutes.credit"> <Credit>0</Credit> <Debit>100</Debit> </ARGrossAccount> <ARDiscountAccount name="minutes.debit"> <Credit>0</Credit> <Debit>0</Debit> </ARDiscountAccount> <ARNetAccount name="minutes.credit"> <Credit>0</Credit> <Debit>100</Debit> </ARNetAccount> <ARTaxAccount name="tax.credit"> <Credit>0</Credit> <Debit>0</Debit> </ARTaxAccount> <OffsetGrossAccount name="minutes.debit"> <Credit>100</Credit> <Debit>0</Debit> </OffsetGrossAccount> <OffsetDiscountAccount name="minutes.credit"> <Credit>0</Credit> <Debit>0</Debit> </OffsetDiscountAccount> <OffsetNetAccount name="minutes.debit"> <Credit>100</Credit> <Debit>0</Debit> </OffsetNetAccount> <OffsetTaxAccount name="tax.debit"> <Credit>0</Credit> <Debit>0</Debit> </OffsetTaxAccount> </RevenueAmounts> </GeneralLedgerReport>
You configure your G/L reports for export by editing the pin_config_export_gl.xml file and loading its contents into the /config/export_gl object in the database. The configuration file enables you to set up your reporting schedules, revenue types, and other report information. By default, it is located in the BRM_home/sys/data/config directory, where BRM_home is the directory in which BRM is installed.
See "load_pin_config_export_gl" for information on loading the export configuration into the database.
Table 6-2 contains the configuration tags you use to set up your G/L reports for export:
Table 6-2 Configuration Tags for G/L Reports (Export)
| Use This Tag: | To Do This: |
|---|---|
|
SourceSystemID |
Specify a unique ID for the BRM system containing the G/L data. Note: This tag is useful when the G/L data is exported from multiple BRM systems and the applications that process the exported XML files need to identify the source of the data. |
|
OutputDirectory |
Specify the directory on your system in which to create the exported XML files. |
|
FileNamePrefix |
Specify a prefix to append to the beginning of the generated XML file name. Note: The pin_ledger_report utility uses a specific naming convention to ensure the XML file names are unique within a directory; therefore a prefix is additional and can be left blank if it is not required. |
|
ReportInitialStartDate |
Important: This value is used as the report start date only the first time a G/L report is exported for the corresponding segment. All subsequent runs of the pin_ledger_report utility for that segment use the end date of the previous G/L report as the report's start date value. Specify the initial start date for each G/L segment for which data is exported. Use the following format: <Year>YYYY</Year> <Month>MM</Month> <Day>DD</Day> For example: <Year>2007</Year> <Month>01</Month> <Day>01</Day> The initial start date must be specified for the root G/L segment (Segment name="."). All G/L segments to be reported use this date. To set a different start date for a specific G/L segment, use the Segment tag and specify a different initial start date for that G/L segment. This date overrides the root segment start date. |
|
Contains the list of G/L segments on which to report. |
|
|
Use the name attribute to specify a G/L segment to report on. Note: If you specify a nested segment, be sure to include the root segment prefix. For example: <Segment name=.westcoast> One G/L report is exported for each combination of G/L segment and revenue type. Note: The Segment name value is the name you assigned to a G/L segment or brand when you created G/L IDs. Important: If you specify multiple instances of the same segment, each one must report on a different set of revenue types. |
|
|
Specify how often G/L reports are generated for the segment:
Important: The frequency for your BRM G/L reports must match the frequency of the reporting schedule in your external G/L system. For example, if the BRM G/L calendar is Monthly, the export frequency should not be Weekly. This creates inaccurate G/L data. |
|
|
Date |
If Frequency is Yearly, set the DayofMonth and Month sub-tags under the Date tag to specify the date to generate G/L reports. For Month, specify 01 through 12; for DayofMonth, specify 01 through 31. If the month has fewer days than specified, the last day of the month is used. For example: <Frequency>Yearly</Frequency> <Date> <DayofMonth>-29</DayofMonth> <Month>-02-</Month> </Date> |
|
DayofMonth |
If Frequency is Monthly, specify the day of the month to generate G/L reports: 01 through 31. If the month has fewer days than specified, the last day of the month is used. |
|
Day |
If Frequency is Weekly, specify the day of week to generate G/L reports: Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday. |
|
Date |
If Frequency is Specific Dates, add one Month/DayofMonth pair for each date on which to generate a G/L report. For Month specify 01 through 12; for DayofMonth specify 01 through 31. If the month has fewer days than specified, the last day of the month is used. For example: <Frequency>Specific Dates</Frequency> <Date> <DayofMonth>-29</DayofMonth> <Month>-02-</Month> </Date> <Date> <DayofMonth>-31</DayofMonth> <Month>-05-</Month> </Date> |
|
Contains the RevenueType list on which to report. |
|
|
Specify the revenue type for the G/L report:
Important: If you specify multiple instances of the same G/L segment, each one must report on a different set of revenue types. |
|
|
Specify the type of G/L report to generate: Summary or Detailed. Specify Detailed only when the report is customized using PCM_OP_GL_EXPORT_GL. See "Customizing G/L Reports for Export". |
|
|
Specify the resource type to include in the G/L report:
|
|
|
If ResourceType is NonMonetary or All, specify the resource ID of the nonmonetary resource to include. Use a separate ResourceID tag for each resource. All other nonmonetary resources are excluded from the report. Note: The IncludeNonMonetary and ExcludeNonMonetary tags are mutually exclusive. You can use only one of these tags. |
|
|
If ResourceType is NonMonetary or All, specify the resource ID of the nonmonetary resource to exclude. Use a separate ResourceID tag for each nonmonetary resource. All other nonmonetary resources are included in the report. Note: The IncludeNonMonetary and ExcludeNonMonetary tags are mutually exclusive. You can use only one of these tags. |
The following example shows the configuration of pin_config_export_gl.xml for the root ( . ) segment:
<GLReportConfiguration> <SourceSystemID>Germany</SourceSystemID> <OutputDirectory> /$PINHOME/GL_output </OutputDirectory> <FileNamePrefix> test </FileNamePrefix> <ReportInitialStartDate> <Segment name="."> <Year>2011</Year> <Month>--07--</Month> <Day>---01</Day> </Segment> <Segment name=".east"> <Year>2011</Year> <Month>--07--</Month> <Day>---01</Day> </Segment> </ReportInitialStartDate> <SegmentList> <Segment name="."> <Frequency>Daily</Frequency> <RevenueTypeList> <RevenueType>Billed earned </RevenueType> <RevenueType>Unbilled earned</RevenueType> <RevenueType>Billed</RevenueType> <RevenueType>Unbilled</RevenueType> <RevenueType>Previously billed earned</RevenueType> </RevenueTypeList> <ReportLevel>Detailed</ReportLevel> <ResourceType>All</ResourceType> <!--ExcludeNonMonetary> <ResourceID>111111111</ResourceID> </ExcludeNonMonetary--> </Segment> </SegmentList> </GLReportConfiguration>
The following example shows a pin_config_export_gl.xml file that contains two segments that are exported at different frequencies:
eastcoast is exported monthly.
westcoast is exported on specific dates.
The eastcoast segment gets its initial start date from the root segment, but the westcoast segment was added at a later date; therefore, it has a different ReportInitialStartDate value.
<GLReportConfiguration> <SourceSystemID>California</SourceSystemID> <OutputDirectory> /users/gluser/exported </OutputDirectory> <FileNamePrefix> USA_ </FileNamePrefix> <ReportInitialStartDate> <Segment name="."> <Year>2006</Year> <Month>07</Month> <Day>15</Day> </Segment> <!-- Segment '.westcoast' was introduced on 1/1/2007 --> <Segment name=".westcoast"> <Year>2007</Year> <Month>01</Month> <Day>01</Day> </Segment> </ReportInitialStartDate> <SegmentList> <Segment name=".eastcoast"> <Frequency>Monthly</Frequency> <DayOfMonth>–––01</DayOfMonth> <RevenueTypeList> <RevenueType>Billed</RevenueType> <RevenueType>Billed earned</RevenueType> </RevenueTypeList> <ReportLevel>Summary</ReportLevel> <ResourceType>All</ResourceType> <ExcludeNonMonetary> <ResourceID>100000</ResourceID> <ResourceID>100001</ResourceID> <ResourceID>100002</ResourceID> <ResourceID>100003</ResourceID> </ExcludeNonMonetary> </Segment> <Segment name=".westcoast"> <Frequency>Specific Dates</Frequency> <Date> <DayofMonth>–––29</DayofMonth> <Month>––02––</Month> </Date> <Date> <DayofMonth>–––31</DayofMonth> <Month>––05––</Month> </Date> <Date> <DayofMonth>–––31</DayofMonth> <Month>––08––</Month> </Date> <Date> <DayofMonth>–––30</DayofMonth> <Month>––11––</Month> </Date> <RevenueTypeList> <RevenueType>Billed</RevenueType> <RevenueType>Billed earned</RevenueType> </RevenueTypeList> <ReportLevel>Detailed</ReportLevel> <ResourceType>All</ResourceType> <ExcludeNonMonetary> <ResourceID>100000</ResourceID> <ResourceID>100001</ResourceID> <ResourceID>100002</ResourceID> <ResourceID>100003</ResourceID> </ExcludeNonMonetary> </Segment> </SegmentList> </GLReportConfiguration> </BusinessConfiguration>
To create or modify G/L reports for export:
Open the BRM_home/sys/data/config/pin_config_export_gl.xml in an XML editor or a text editor.
Enter the appropriate information into the file. See "Configuring G/L Reports for Export".
Save and close the file.
Run the following command, which loads the G/L configuration information into the /config/export_gl object:
load_pin_config_export_gl pin_config_export_gl.xml
Important:
Run this command from the directory in which the pin_config_export_gl.xml file is located, otherwise, include the complete path to the file. For example:
load_pin_config_export_gl BRM_home/sys/data/config/pin_config_export_gl.xml
When you run the utility, the pin_config_export_gl.xml and business_configuration.xsd files must be in the same directory. By default, both files are in BRM_home/sys/data/config.
The load_pin_config_export_gl utility needs a configuration (pin.conf) file in the directory from which you run the utility. For information about creating a configuration file, see "Creating Configuration Files for BRM Utilities" in BRM System Administrator's Guide.
The frequency for your BRM G/L reports must match the frequency of the reporting schedule in your external G/L system. For example, if the external G/L calendar is Monthly, the export frequency for your BRM G/L reports should be Monthly. If the two frequencies do not match, inaccurate G/L data will be reported.
You configure the export schedule for your G/L segments by setting the Frequency value for the G/L segment in the pin_config_export_gl.xml file. Each G/L segment you report on can use a different export frequency as long as it matches the reporting frequency for that G/L segment in the external G/L system.
To set up multiple reporting schedules for the same G/L segment, you must specify different revenue types for each report. You cannot specify overlapping revenue types to report on.
For example, to report on billed revenue on a monthly basis and unbilled revenue on a weekly basis for the eastcoast G/L segment, list multiple instances of the eastcoast segment in the SegmentList tag and specify the revenue type for each schedule, as shown in the following sample code:
<SegmentList>
<Segment name=".eastcoast">
<Frequency>Monthly</Frequency>
<DayOfMonth>01</DayOfMonth>
<RevenueTypeList>
<RevenueType>Billed</RevenueType>
</RevenueTypeList>
<ReportLevel>Summary</ReportLevel>
<ResourceType>Monetary</ResourceType>
</Segment>
<Segment name=".eastcoast">
<Frequency>Weekly</Frequency>
<Day>Saturday</Day>
<RevenueTypeList>
<RevenueType>Unbilled earned</RevenueType>
</RevenueTypeList>
<ReportLevel>Summary</ReportLevel>
<ResourceType>Monetary</ResourceType>
</Segment>
</SegmentList>
If you have a large number of noncurrency resources in your system, you can use the IncludeNonMonetary or ExcludeNonMonetary tag in the pin_config_export_gl.xml file to filter the resources you report on; for example, if your system contains 20 noncurrency resources, but you want to report on only a few. In this case, use IncludeNonMonetary and specify the noncurrency resource IDs to include in the report. To report on all but a few of the 20 noncurrency resources, use ExcludeNonMonetary and specify the noncurrency resource IDs to exclude.
Run the pin_ledger_report utility in export mode to generate G/L reports and export them to XML files. You can also regenerate and re-export G/L reports. Export mode exports G/L reports according to the configuration defined in the pin_config_export_gl.xml file.
Note:
When you export G/L reports, they are always posted to the database. To generate a report without posting it, use the without the -posted parameter.A separate G/L report is generated and exported for each combination of G/L segment and revenue specified in the pin_config_export_gl.xml file.
To export a G/L report for a specific segment, run the pin_ledger_report utility in -export mode with the -segment parameter and specify the segment to report. For example:
pin_ledger_report -mode export -segment .westcoast
To process multiple G/L reports at the same time, run multiple instances of the pin_ledger_report utility. For each instance, use -export mode with the -segment parameter and specify the segment for the desired report.
To regenerate a previously generated G/L report and re-export it to an XML file, run the pin_ledger_report utility in export mode with the -regenerate parameter:
pin_ledger_report -mode export -regenerate ReportId
where ReportId is a combination of the database number, the Portal object ID (POID) of the /process_audit/export_gl object, and the report number all separated by dashes. For example, if the database number is 0.0.0.1, the POID is 123456, and the report number is 4, enter:
pin_ledger_report -mode export -regenerate 0.0.0.1-123456-4
To find the report ID, look in the previously generated XML report file, if available, or in the export audit data. See "Retrieving Audit Data for Exported G/L Reports".
For more information about regenerating reports, see "About Regenerating and Re-Exporting G/L Reports".
To export a previously exported G/L report to an XML file, run the pin_ledger_report utility in export mode with the -resend parameter:
pin_ledger_report -mode export -resend ReportId
where reportId is a combination of the database number, the POID of the /process_audit/export_gl object, and the report number. For example, if the database number is 0.0.0.1, the POID is 123456, and the report number is 4, type:
pin_ledger_report -mode export -resend 0.0.0.1-123456-4
To find the report ID, look in the previously generated XML report file, if available, or in the export audit data. See "Retrieving Audit Data for Exported G/L Reports".
Important:
This command does not regenerate the report contained in the /audit_object/export_gl object. For information on regenerating an exported G/L report, see "Example: Regenerating G/L Reports".For more information about re-exporting reports, see "About Regenerating and Re-Exporting G/L Reports".
If the pin_ledger_report utility exits abnormally, it attempts to clean up any errors from that run and finishes processing. If it is not successful (the status is not set to COMPLETED after the run), subsequent runs will not work.
To clean up the previous run and finish processing successfully, use export mode with the -restart parameter:
pin_ledger_report -mode export -resend ReportId
The utility will continue to process the reports from the point at which it left off when it stopped; therefore, no duplicate reports are generated.
Information about each run of the pin_ledger_report utility is written to the /process_audit/export_gl object in the BRM database. One /process_audit object is created for each run of the utility.
To view the data in the /process_audit object, read the object with the testnap utility or with the Object Browser.
For information on how to use testnap or Object Browser, see "Reading an Object and Writing Its Contents to A File" in BRM Developer's Guide .
You can use the G/L export audit data to:
Ensure that reports have been generated for all periods prior to the start date of the next report.
Generate statistics.
Troubleshoot reporting errors.
The /process_audit/export_gl object contains the following information:
The status of the last export run: COMPLETED, IN_PROGRESS, or INCOMPLETE.
COMPLETED indicates that the application run has exited normally.
IN_PROGRESS indicates that the application is currently running.
INCOMPLETE indicates that the application had crashed during an earlier application run and the audit entry was marked as such during a subsequent run.
If the pin_ledger_report utility was run in export mode with the -resend or -regenerate parameter, the POID of the ledger report object last prepared for export.
Report information array. Each array element contains the following information for one report:
G/L segment for which report is generated
Type of ledger report; for example 4 for unbilled earned
Frequency (schedule) of exporting G/L reports
End time of the reporting period
If nonmonetary resources were reported on, the resource IDs that were included in or excluded from the report
A list of up to three POIDs
The first POID is the default report generated by calling the PCM_OP_GL_LEDGER_REPORT opcode.
If there are two POIDs, the second POID is either the custom report (billed, billed earned, and previously billed earned) or the cumulative report (unbilled, unbilled earned, unbilled unearned, and billed unearned) generated after execution of the PCM_OP_GL_POL_EXPORT_GL policy opcode.
If there are three POIDs, the second POID is the custom report and the third POID is the cumulative report.
You can customize the data in your exported G/L reports by configuring the PCM_OP_GL_POL_EXPORT_GL policy opcode. The pin_ledger_report utility calls this policy opcode in export mode after it generates a G/L report but before it exports the G/L report data to an XML file.
For example, you can map the G/L revenue account name in the BRM system to a different account name. To do this, you customize the PCM_OP_GL_POL_EXPORT_GL policy opcode to do the mapping and to return the modified PIN_FLD_GL_ACCTS array from the input flist.
When the pin_ledger_report utility runs in export mode, it creates a new ledger report object with the custom data in the PIN_FLD_GL_ACCTS array. This customized G/L report is used for further processing and is exported to the G/L report XML files.
The PIN_FLD_RESULT value in the output flist determines whether to create a customized report from the original report used for export:
0: Use the original G/L data to generate the export report. This is the default.
1: Use the G/L data in the output flist to generate a custom export report.
Note:
The values returned by the PCM_OP_GL_POL_EXPORT_GL policy opcode must be in summary format even when the input flist data is in detailed format.Use the PCM_OP_GL_POL_EXPORT_GL policy opcode to customize the contents of your exported G/L reports. For example, the BRM G/L account names could be mapped to the third-party account names, or the BRM resource IDs could be mapped to the third-party resource IDs.
Use the PCM_OP_GL_POL_EXPORT_GL policy opcode to convert BRM G/L account names to the G/L account names in your external G/L system before XML reports are exported. This makes importing them into the external system easier. The following is an example of the code used to map BRM G/L account names to the third-party account names after the PCM_OP_GL_POL_EXPORT_GL policy code has been called.
#***********************************************************
#Section to customize report
#***********************************************************
PCM_OP(ctxp, PCM_OP_READ_OBJ, 0, in_flistp, &r_flistp, ebufp);
PIN_ERR_LOG_FLIST(PIN_ERR_LEVEL_DEBUG,"Flistp after read obj in Custom report", r_flistp);
while ((tmp_flistp = PIN_FLIST_ELEM_TAKE_NEXT(r_flistp, PIN_FLD_GL_ACCTS,&rec_id, 1, &cookie, ebufp)) != (pin_flist_t *)NULL)
{
char *segment = NULL;
PIN_ERR_LOG_FLIST(PIN_ERR_LEVEL_DEBUG, "GL accts from input flist", tmp_flistp);
tmp1_flistp = PIN_FLIST_ELEM_ADD(out_flistp, PIN_FLD_GL_ACCTS, ctr, ebufp);
PIN_FLIST_CONCAT(tmp1_flistp, tmp_flistp, ebufp);
ctr++;
vp = PIN_FLIST_FLD_GET(r_flistp, PIN_FLD_GL_SEGMENT, 0, ebufp);
if (vp)
{
segment = (char *)vp;
}
modify_acct_name(tmp1_flistp, PIN_FLD_AR_GROSS_GL_ACCT, segment, ebufp);
modify_acct_name(tmp1_flistp, PIN_FLD_OFF_GROSS_GL_ACCT, segment, ebufp);
modify_acct_name(tmp1_flistp, PIN_FLD_AR_NET_GL_ACCT, segment, ebufp);
modify_acct_name(tmp1_flistp, PIN_FLD_OFF_NET_GL_ACCT, segment, ebufp);
modify_acct_name(tmp1_flistp, PIN_FLD_AR_DISC_GL_ACCT, segment, ebufp);
modify_acct_name(tmp1_flistp, PIN_FLD_OFF_DISC_GL_ACCT, segment, ebufp);
modify_acct_name(tmp1_flistp, PIN_FLD_AR_TAX_GL_ACCT, segment, ebufp);
modify_acct_name(tmp1_flistp, PIN_FLD_OFF_TAX_GL_ACCT, segment, ebufp);
PIN_ERR_LOG_FLIST(PIN_ERR_LEVEL_DEBUG, "GL accts from output flist ", tmp1_flistp);
PIN_ERR_LOG_FLIST(PIN_ERR_LEVEL_DEBUG, "Intermediate output flist ", out_flistp);
}
#************************************************************
#Set PIN_FLD_RESULT to indicate customization
#***********************************************************/
result = 1;
PIN_FLIST_FLD_SET(out_flistp, PIN_FLD_RESULT, (void *)&result, ebufp);
If the mapping capabilities provided with the BRM pin_glid mapping file are not sufficient, you can use the PCM_OP_GL_POL_EXPORT_GL policy opcode to convert BRM resource IDs to the resource IDs in your external G/L system before XML reports are exported. This makes importing them into the external system easier. The following is an example of the code used to map BRM resource IDs to the third-party resource IDs after the PCM_OP_GL_POL_EXPORT_GL policy code has been called.
#************************************************************
#Section to customize report
#****************************************************************
PCM_OP(ctxp, PCM_OP_READ_OBJ, 0, in_flistp, &r_flistp, ebufp);
PIN_ERR_LOG_FLIST(PIN_ERR_LEVEL_DEBUG, "Flistp after read obj in Custom report", r_flistp);
while ((tmp_flistp =PIN_FLIST_ELEM_TAKE_NEXT(r_flistp, PIN_FLD_GL_ACCTS,&rec_id, 1, &cookie, ebufp)) != (pin_flist_t *)NULL)
{
PIN_ERR_LOG_FLIST(PIN_ERR_LEVEL_DEBUG, "GL accts from input flist ", tmp_flistp);
tmp1_flistp = PIN_FLIST_ELEM_ADD(out_flistp, PIN_FLD_GL_ACCTS, ctr, ebufp);
PIN_FLIST_CONCAT(tmp1_flistp, tmp_flistp, ebufp);
ctr++;
vp = PIN_FLIST_FLD_GET(r_flistp, PIN_FLD_RESOURCE_ID, 0, ebufp);
if (vp)
{
resource_id = (int32 *)vp;
if (resource_id == 840)
{
new_resource_id = 1840;
}
}
PIN_FLIST_FLD_SET(out_flistp, PIN_FLD_RESOURCE_ID, (void *)&new_resource_id, ebufp);
PIN_ERR_LOG_FLIST(PIN_ERR_LEVEL_DEBUG, "GL accts from output flist ", tmp1_flistp);
PIN_ERR_LOG_FLIST(PIN_ERR_LEVEL_DEBUG, "Intermediate output flist ", out_flistp);
}
#***********************************************************
#Set PIN_FLD_RESULT to indicate customization
#***********************************************************
result = 1;
PIN_FLIST_FLD_SET(out_flistp, PIN_FLD_RESULT, (void *)&result, ebufp);
To customize the XML file names for your G/L reports, use the FileNamePrefix tag in the pin_config_export_gl.xml configuration file. For example, if your company tracks G/L data separately for multiple divisions of the same company, you can prefix the G/L data for each division with a unique identifier.
Before exporting your G/L reports, validate that your G/L export configuration data was loaded into the database correctly. Do the following:
Run the load_pin_config_export_gl utility with the -d parameter when you load the pin_config_export_gl.xml file into the database. This will write any errors to a log file, which you can use for debugging purposes.
Run the pin_ledger_report utility in post_only mode and verify the /data/ledger_report object is updated in the database.
Run the pin_ledger_report utility in run_report mode with the -report and -test parameters. This will display the G/L report without creating the /ledger_report object.
Verify that the revenue types and G/L segments are correct.
Run the pin_ledger_report utility in export mode and verify that the report name, report type, G/L segments, frequency, and revenue types are correct.
Verify that the /process_audit/export_gl object was created in the database. View the data in this object and verify the data is correct and that the status of the export run is COMPLETED. See "Retrieving Audit Data for Exported G/L Reports".
You can purge the following G/L report data:
Exported XML files on the file system
Keep the exported XML files for the latest period for each segment and report type until subsequent runs have been made and the XML files have been imported successfully into the external system.
/process_audit/export_gl objects and /ledger_report objects in the database
Keep /process_audit/export_gl objects for each G/L segment/report type combination (for example, a billed revenue report for the .westcoast G/L segment) until at least two newer sets of objects are in the database. This ensures you can re-export or regenerate previous G/L reports if necessary.
Important:
Each /process_audit/export_gl object contains a reference to a corresponding /ledger_report object; therefore, you should purge them at the same time. Before doing so, make sure they are no longer needed.For information on purging data, see "About Purging Data" in BRM System Administrator's Guide.