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

E16704-05
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

6 Exporting General Ledger (G/L) Reports to XML Files

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".

About Exporting G/L Reports

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.

About Configuring G/L Reports for Export

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 will generate the following G/L reports (/ledger_report objects) in the database for a given reporting period:

About Incremental Reports and Cumulative Revenue

When you export G/L reports for the following revenue types, the basic reports include cumulative revenue across reporting periods:

  • Unbilled

  • Unbilled earned

  • Unbilled unearned

  • Billed unearned

Part of the total revenue reported for the current period may have been included in the same report for the previous period. The export operation determines the cumulative revenue amounts by calculating 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 G/L reports contain the incremental revenue amounts.

For information on revenue recognition, see "About Revenue Recognition".

About G/L Segments and Cumulative Revenue

When you export G/L reports for nested segments and the revenue type is unbilled, unbilled unearned, unbilled earned, or billed unearned revenue (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 .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".

About Regenerating and Re-Exporting G/L Reports

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".

How BRM Exports G/L Reports

The pin_ledger_report utility performs the following tasks when it runs:

  1. 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.

  2. 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.

  3. 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.

  4. Calls the PCM_OP_GL_LEDGER_REPORT opcode to create the /ledger_report object for the current period.

  5. Using the POID of the generated /ledger_report object, calls 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 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.
  6. If specified in the export configuration file, filters non-currency resources from the generated /ledger_report object to be included in or excluded from the XML output file.

  7. Generates the XML output file.

  8. Updates the /process_audit/export_gl object with information about this run.

  9. Posts reports in the database.

  10. 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.)

About Exported G/L Report Files

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 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.

ReportId

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:

  • Billed

  • Billed earned

  • Billed unearned

  • Prior billed earned (Prior billed earned revenue is also known as Previously billed earned revenue. See "About Revenue Recognition").

  • Unbilled

  • Unbilled earned

  • Unbilled unearned

BRM_GL_Segment

The name of the G/L segment being reported on.

ReportCreatedTime

Report run time.

PeriodStartTime

The start time of the revenue reporting period, which includes the following information:

  • Year

  • Month

  • Day

  • Hours

  • Minutes

  • Seconds

PeriodStartEnd

The end time of the revenue reporting period, which includes the following information:

  • Year

  • Month

  • Day

  • Hours

  • Minutes

  • Seconds

RevenueAmounts

The revenue reported for a specific G/L segment. Revenue amounts are specified with the element attribute. Each element contains:

  • The resource ID and its associated BRM G/L ID.

  • A list of revenue accounts and their credit and debit amounts. For example, ARGrossAccount name="monthly.debit".

The revenue accounts are retrieved from the pin_glid file. For information on configuring this data, see "Loading General Ledger Configuration Data".


Sample Output XML File

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>

Configuring G/L Reports for Export

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.

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:

OutputDirectory

Specify the directory on your system in which to create the exported XML files.

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.

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 only used as the report start date 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 YYYYMMDD 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.

SegmentList

Contains the list of G/L segments on which to report.

Segment

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 GL 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.

Frequency

Specify how often G/L reports are generated for the segment:

  • Daily

  • Weekly

  • Monthly

  • Yearly

  • Specific Dates

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 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>

RevenueTypeList

Contains the RevenueType list on which to report.

RevenueType

Specify the revenue type for the G/L report:

  • Billed

  • Billed earned

  • Prior billed earned (Prior billed earned revenue is also known as Previously billed earned revenue. See "About Revenue Recognition".)

  • Unbilled

  • Unbilled unearned

  • Unbilled earned

  • Billed unearned

Important: If you specify multiple instances of the same G/L segment, each one must report on a different set of revenue types.

ReportLevel

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".

ResourceType

Specify the resource type to include in the G/L report:

  • Monetary

  • Non-monetary

  • All

IncludeNonMonetary

If ResourceType is NonMonetary or All, specify the resource ID of the non-monetary resource to include. Use a separate ResourceID tag for each resource. All other non-monetary resources are excluded from the report.

Note: The IncludeNonMonetary and ExcludeNonMonetary tags are mutually exclusive. You can use only one.

ExcludeNonMonetary

If ResourceType is NonMonetary or All, specify the resource ID of the non-monetary resource to exclude. Use a separate ResourceID tag for each non-monetary resource. All other non-monetary resources are included in the report.

Note: The IncludeNonMonetary and ExcludeNonMonetary tags are mutually exclusive. You can use only one.


Sample G/L Report Configuration File

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.

<BusinessConfiguration>
 <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>

Editing the G/L Export Configuration File

To create or modify G/L reports for export, do the following:

  1. Open the G/L configuration file (BRM_Home/sys/data/config/pin_config_export_gl.xml) in an XML editor or a text editor.

  2. Enter the appropriate information into the file. See "Configuring G/L Reports for Export".

  3. Save and close the file.

  4. Use this command to load 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.

Setting up G/L Reporting Schedules

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, like shown in the sample code below:

   <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>

Filtering Non-Currency Resources

If you have a large number of non-currency 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, say your system contains 20 non-currency resources, but you want to report on only a few. In this case, use IncludeNonMonetary and specify the non-currency resource IDs to include in the report. To report on all but a few of the 20 non-currency resources, use ExcludeNonMonetary and specify the non-currency resource IDs to exclude.

Exporting G/L Reports

Run the pin_ledger_report utility in the -export mode to generate G/L reports and export them to XML files. You can also regenerate and re-export G/L reports. The -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.

Example: Exporting G/L Reports for a Specific G/L Segment

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

Example: Exporting G/L Reports in Parallel

To process multiple G/L reports at the same time, run multiple instances of the pin_ledger_report utility. For each instance, use the -export mode with the -segment parameter and specify the GL_segment for the desired report.

Example: Regenerating G/L Reports

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, type:

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".

Example: 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 report_ID

where report_ID 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 number, 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".

Example: Restarting a Failed Export Operation

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 the -restart parameter:

pin_ledger_report -mode export -resend report_ID

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.

Retrieving Audit Data for Exported G/L Reports

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 audit object, read the object with the testnap utility or 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 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 non-monetary resources were reported on, the resource IDs that were included in or excluded from the report.

    • A list of up to 3 POIDs.

      The first POID is the default report generated by calling the PCM_OP_GL_LEDGER_REPORT opcode.

      If there are 2 POIDs, the second one 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 3 POIDs, the second one is the custom report and the third one is the cumulative report.

Customizing G/L Reports for Export

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 based on your company's criteria. To do this, you customize the PCM_OP_GL_POL_EXPORT_GL policy opcode to do the mapping and 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.

Customizing G/L Export Report Contents

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.

Mapping BRM G/L Account Names to Third-Party Account Names

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);

Mapping BRM Resource IDs to Third-Party Resource IDs

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);

Customizing the G/L Report XML File Names

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.

Validating and Troubleshooting Exported G/L Reports

Before exporting your G/L reports, validate that your G/L export configuration data was loaded into the database correctly. Do the following:

  1. 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.

  2. Run the pin_ledger_report utility in post_only mode and verify the /data/ledger_report object is updated in the database.

  3. 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 storable object.

    Verify that the revenue types and G/L segments are correct.

  4. 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.

  5. 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".

Purging G/L Export Data

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 there are at least two newer sets of objects 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.