5 BRM Base Reports
This document describes the Oracle Communications Billing and Revenue Management (BRM) reports contained in the BRM Base Reports installation package.
Topics in this document:
Accounts Receivable Reports
You use these reports to get information about payments due, payments received, payment reversals, refunds, and bad debt write-offs.
The Accounts Receivable Summary report and Accounts Receivable Detail report show the accounts receivable (A/R) data in your BRM database. The summary report shows data for accounts that own paying bill units (amounts for nonpaying child bill units are rolled into the paying bill unit totals). The detail report shows item data for individual accounts, including accounts that own nonpaying child bill units.
The reports on payments received, payment reversals, and refunds show information about account payment and refund activity that occurred during time periods you specify. These reports have two versions:
-
The Payments Summary report, Reversals Summary report, and Refunds Summary report show the total amount of a period's payments, reversals, or refunds by type. Types include cash, check, credit card, direct debit, interbank transfer, postal order, and wire transfer.
-
The Payments Detail report, Reversals Detail report, and Refunds Detail report show event information—such as account number, customer name, amount, and transaction ID—for one payment, reversal, or refund type only. You can generate a detail report for each type.
The Bad Debt Write-Offs report lists all the bad debt write-offs recorded in your BRM system during a specified time period, grouped by account.
Accounts Receivable Summary Report
The Accounts Receivable Summary report (ARSummary.rtf) summarizes the amounts currently due in the aging time periods you specify. You can specify up to three time periods dating back from the current date. You specify the time periods by selecting the number of days in each period. The default time periods are 30 days long.
Note:
The end date for this report is always the current date.
When the Account Details parameter is set to NO, the ARSummary report shows data for states or countries, but not for individual accounts.
When the Account Details parameter is set to YES, the report shows data for paying bill units only; amounts owed by nonpaying child bill units are rolled into the paying parent bill unit totals. To see information about nonpaying child bill units, use the Accounts Receivable Detail report.
You can find the SQL query data model for this report in the following directory:
Reports/BRM Reports/Base BRM Reports/ARDetail/ARSummary.xdm
ARSummary Parameters
You can change the following parameters to modify the output of the ARSummary report:
-
State Details
-
Account Details
-
Include Chart
-
Period 1 Day
-
Period 2 Days
-
Period 3 Days
-
Country
-
State
-
Billing Type
-
Account Status
If your BRM system does not contain state data, see "When to Modify Formulas for New Values".
To add the Promotion Code and Software Package parameters to this report, see "Adding Promotion Code and Software Package Parameters".
For information on parameters not described in Table 5-1, see "Understanding the Standard Parameters".
Table 5-1 ARSummary Parameters
Parameter | Description | Valid Values | Default Value |
---|---|---|---|
Period 1 Day |
Number of days to be covered by the first of three time periods. See "Day Ranges". Note: The end date for the ARSummary report is always the current date. |
1–366. |
30 (0–30 days before the current date). |
Period 2 Days |
Number of days to be covered by the second time period. See "Day Ranges". |
1–366. |
30 (31–60 days before the current date). |
Period 3 Days |
Number of days to be covered by the third time period. See "Day Ranges". |
1–366. |
30 (61–90 days before the current date). |
Accounts Receivable Detail Report
The Accounts Receivable Detail report (ARDetail.rtf) expands the ARSummary information by displaying item details for individual accounts. The time periods function the same way they do in the ARSummary report. Accounts within the same billing group (accounts with paying parent bill units and accounts whose bill units are nonpaying children of those paying bill units) are enclosed within dotted lines.
Note:
This report sorts items by the ITEM_T.CREATED_T.
You can find the SQL query data model for this report in the following directory:
Reports/BRM Reports/Base BRM Reports/ARSummary/ARDetail.xdm
No charts are available for this report.
ARDetail Parameters
You can change the following parameters to modify the output of the ARDetail report:
-
State Details
-
Period 1 Day
-
Period 2 Days
-
Period 3 Days
-
Country
-
State
-
Billing Type
-
Account Status
If your BRM system does not contain state data, see "When to Modify Formulas for New Values".
To add the Promotion Code and Software Package parameters to this report, see "Adding Promotion Code and Software Package Parameters".
For information on parameters not described in Table 5-2, see "Understanding the Standard Parameters".
Table 5-2 ARDetail Parameters
Parameter | Description | Valid Values | Default Value |
---|---|---|---|
Period 1 Day |
Number of days to be covered by the first of three time periods. See "Day Ranges". Note: The end date for the ARDetail report is always the current date. |
1–366. |
30 (0–30 days before the current date). |
Period 2 Days |
Number of days to be covered by the second time period. See "Day Ranges". |
1–366. |
30 (31–60 days before the current date). |
Period 3 Days |
Number of days to be covered by the third time period. See "Day Ranges". |
1–366. |
30 (61–90 days before the current date). |
Payments Summary Report
Note:
You must upload the PaymentsSummary.source file that is in the StoredProcedures folder to the database after the base reports are installed. For more information about loading stored procedures, see "Loading Stored Procedures".
The Payments Summary report (PaymentsSummary.rtf) shows the total amount of payments received during a specified time period. The amount is listed by type. payment methods include cash, check, credit card, direct debit, postal order, SEPA, wire transfer, and interbank transfer. The amounts shown are the sum of all payments received during the period, including advance, full, partial, and overpayments.
You can generate two versions of this report: a version that shows totals for all payment methods and a version that shows the total for only one payment method.
You can find the SQL query data model for this report in the following directory:
Reports/BRM Reports/Base BRM Reports/StoredProcedures/PaymentsSummary.xdm
No charts are available for this report.
PaymentsSummary Parameters
You can change the following parameters to modify the output of the PaymentsSummary report:
-
Country
-
State
-
Account Status
-
Payment Type
-
Start Date (date or date-time)
-
End Date (date or date-time)
If your BRM system does not contain state data, see "When to Modify Formulas for New Values".
For information on parameters not described in Table 5-3, see "Understanding the Standard Parameters".
Table 5-3 Payments Summary Parameters
Parameter | Description | Valid Values | Default Value |
---|---|---|---|
Payment Type |
The method used by customers to pay a bill. Select ALL to include information for all payment methods, or select a specific payment method to include information for that type only. |
Any of the following:
|
ALL |
Payments Detail Report
Note:
-
You must upload the PaymentsDetail.source file that is in the StoredProcedures folder to the database after the base reports are installed. For more information about loading stored procedures, see "Loading Stored Procedures".
-
The Payments Detail report (PaymentsDetail.rtf) shows detailed information about only one type of payment. You can generate a PaymentsDetail report for each of the payment methods (cash, check, credit card, direct debit, postal order, SEPA, wire transfer, interbank transfer, and failed).
-
Failed payments are failed unconfirmed payments and have a balance of 0.
This report lists all payments of the specified type that were received during a particular time period. It shows the account number, customer name, payment amount, and transaction ID associated with each payment event.
Credit card versions of this report show the credit card numbers, direct debit versions show the direct debit numbers, and SEPA versions show IBAN numbers. If you choose to mask these numbers, the report shows only the last four digits of each number. Otherwise, the full numbers are displayed.
Note:
If credit card tokenization is enabled, the credit card versions of this report show only the tokens.
Two dates are shown for every payment. The Payment Entered date is the time the payment was entered in the BRM system. The Payment Effective date is the time the payment had an impact on the account balance.
Note:
Credit card and direct debit versions of this report show only the payment effective date.
You can find the SQL query data model for this report in the following directory:
Reports/BRM Reports/Base BRM Reports/StoredProcedures/PaymentsDetail.xdm
No charts are available for this report.
Failed payment report displays records of failed confirmed payments that are rejected. Failed unconfirmed payments are not displayed. BRM obtains failed confirmed payments that are rejected from the /event/billing/payment/failed object. Failed unconfirmed payments that are rejected are obtained from the /event/billing/fee/failed_payment object.
PaymentsDetail Parameters
You can change the following parameters to modify the output of the PaymentsDetail report:
-
Country
-
State
-
Account Status
-
Payment Type
-
Start Date (date or date-time)
-
End Date (date or date-time)
-
Mask CC/DD/IBAN Numbers
If your BRM system does not contain state data, see "When to Modify Formulas for New Values".
For information on parameters not described in Table 5-4, see "Understanding the Standard Parameters".
Table 5-4 PaymentsDetail Parameters
Parameter | Description | Valid Values | Default Value |
---|---|---|---|
Payment Type |
The method used by customers to pay a bill. |
Any of the following:
|
CASH. |
Mask CC/DD/IBAN Numbers |
The way credit card, direct debit, and International Bank Account Numbers (for SEPA) are displayed. Select YES to show only the last four digits of all credit card, direct debit, or IBAN numbers. Select NO to display the entire number. This parameter applies to credit card and direct debit reports only. |
YES or NO |
YES |
Reversals Summary Report
Note:
-
You must upload the ReversalsSummary.source file that is in the StoredProcedures folder to the database after the base reports are installed. For more information about loading stored procedures, see "Loading Stored Procedures".
-
The Reversals Summary report (ReversalsSummary.rtf) shows the total amount of the payment reversals that were made during a specified time period. The amount is listed by type. Reversal types include check, credit card, direct debit, postal order, SEPA, wire transfer, and interbank transfer.
You can generate two versions of this report: a version that shows totals for all reversal types and a version that shows the total for only one reversal type.
Caution:
Do not perform cross payment reversals. For example, if the payment is by check, you should not reverse the payment to a credit card. This will display wrong data in the report.
You can find the SQL query data model for this report in the following directory:
Reports/BRM Reports/Base BRM Reports/StoredProcedures/ReversalsSummary.xdm
No charts are available for this report.
ReversalsSummary Parameters
You can change the following parameters to modify the output of the ReversalsSummary report:
-
Country
-
State
-
Account Status
-
Reversal Type
-
Start Date (date or date-time)
-
End Date (date or date-time)
If your BRM system does not contain state data, see "When to Modify Formulas for New Values".
For information on parameters not described in Table 5-5, see "Understanding the Standard Parameters".
Table 5-5 ReversalsSummary Parameters
Parameter | Description | Valid Values | Default Value |
---|---|---|---|
Reversal Type |
The method used by customers to make the payment that was reversed. Select ALL to include information for all reversal types, or select a specific reversal type to include information for that type only. |
Any of the following:
|
ALL |
Reversals Detail Report
Note:
-
You must upload the ReversalsDetail.source file that is in the StoredProcedures folder to the database after the base reports are installed. For more information about loading stored procedures, see "Loading Stored Procedures".
-
The Reversals Detail report (ReversalsDetail.rtf) shows detailed information about only one type of payment reversal. You can generate a ReversalsDetail report for each of the reversal types (check, credit card, direct debit, postal order, SEPA, wire transfer, and interbank transfer).
This report lists all reversals of the specified type that were made during a particular time period. It shows the account number, customer name, amount, and transaction ID associated with each reversal event. It also shows the reason each reversal was made.
Credit card versions of this report show the credit card numbers, direct debit versions show the direct debit numbers, and SEPA versions show IBAN numbers. If you choose to mask these numbers, the report shows only the last four digits of each number. Otherwise, the full numbers are displayed.
Note:
If credit card tokenization is enabled, the credit card versions of this report show only the tokens.
Two dates are shown for every reversal. The Reversal Entered date is the time the reversal was entered in the BRM system. The Reversal Effective date is the time the reversal had an impact on the account balance.
You can find the SQL query data model for this report in the following directory:
Reports/BRM Reports/Base BRM Reports/StoredProcedures/ReversalsDetail.xdm
No charts are available for this report.
ReversalsDetail Parameters
You can change the following parameters to modify the output of the ReversalsDetail report:
-
Country
-
State
-
Account Status
-
Reversal Type
-
Start Date (date or date-time)
-
End Date (date or date-time)
-
Mask CC/DD/IBAN Numbers
If your BRM system does not contain state data, see "When to Modify Formulas for New Values".
For information on parameters not described in Table 5-6, see "Understanding the Standard Parameters".
Table 5-6 ReversalsDetail Parameters
Parameter | Description | Valid Values | Default Value |
---|---|---|---|
Reversal Type |
The method used by customers to make the payment that was reversed. |
Any of the following:
|
CHECK |
Mask CC/DD/IBAN Numbers |
The way credit card, direct debit, and International Bank Account Numbers (for SEPA) are displayed. Select YES to show only the last four digits of all credit card, direct debit, or IBAN numbers. Select NO to display the entire number. This parameter applies to credit card and direct debit reports only. |
YES or NO. |
YES |
Refunds Summary Report
Note:
-
You must upload the RefundsSummary.source file that is in the StoredProcedures folder to the database after the base reports are installed. For more information about loading stored procedures, see "Loading Stored Procedures".
-
The Refunds Summary report (RefundsSummary.rtf) shows the total amount of the refunds that were applied to accounts during a specified time period. The amount is listed by type. Refund types include cash, check, credit card, direct debit, postal order, SEPA, wire transfer, and interbank transfer.
You can generate two versions of this report: a version that shows totals for all refund types and a version that shows the total for only one refund type.
You can find the SQL query data model for this report in the following directory:
Reports/BRM Reports/Base BRM Reports/StoredProcedures/RefundsSummary.xdm
No charts are available for this report.
RefundsSummary Parameters
You can change the following parameters to modify the output of the RefundsSummary report:
-
Country
-
State
-
Account Status
-
Refund Type
-
Start Date (date or date-time)
-
End Date (date or date-time)
If your BRM system does not contain state data, see "When to Modify Formulas for New Values".
For information on parameters not described in Table 5-7, see "Understanding the Standard Parameters".
Table 5-7 RefundsSummary Parameters
Parameter | Description | Valid Values | Default Value |
---|---|---|---|
Refund Type |
The method used to give back a payment. Select ALL to include information for all refund types, or select a specific refund type to include information for that type only. |
Any of the following:
|
ALL |
Refunds Detail Report
Note:
-
You must upload the RefundsDetail.source file that is in the StoredProcedures folder to the database after the base reports are installed. For more information about loading stored procedures, see "Loading Stored Procedures".
-
The Refunds Detail report (RefundsDetail.rtf) shows detailed information about only one type of refund. You can generate a RefundsDetail report for each of the refund types (cash, check, credit card, direct debit, postal order, SEPA, wire transfer, and interbank transfer).
This report lists all refunds of the specified type that took place during a particular time period. It shows the account number, customer name, amount, and transaction ID associated with each refund event.
Credit card versions of this report show the credit card numbers, direct debit versions show the direct debit numbers, and SEPA versions show IBAN numbers. If you choose to mask these numbers, the report shows only the last four digits of each number. Otherwise, the full numbers are displayed.
Note:
If credit card tokenization is enabled, the credit card versions of this report show only the tokens.
Two dates are shown for every refund. The Refund Entered date is the time the refund was entered in the BRM system. The Refund Effective date is the time the refund had an impact on the account balance.
Note:
Credit card and direct debit versions of this report show only the refund effective date.
You can find the SQL query data model for this report in the following directory:
Reports/BRM Reports/Base BRM Reports/StoredProcedures/RefundsDetail.xdm
No charts are available for this report.
RefundsDetail Parameters
You can change the following parameters to modify the output of the RefundsDetail report:
-
Country
-
State
-
Account Status
-
Refund Type
-
Start Date (date or date-time)
-
End Date (date or date-time)
-
Mask CC/DD/IBAN Numbers
If your BRM system does not contain state data, see "When to Modify Formulas for New Values".
For information on parameters not described in Table 5-8, see "Understanding the Standard Parameters".
Table 5-8 RefundsDetail Parameters
Parameter | Description | Valid Values | Default Value |
---|---|---|---|
Refund Type |
The method used to give back a payment. |
Any of the following:
|
CASH |
Mask CC/DD/IBAN Numbers |
The way credit card, direct debit numbers, and International Bank Account Numbers (for SEPA) are displayed. Select YES to show only the last four digits of all credit card, direct debit, or IBAN numbers. Select NO to display the entire number. This parameter applies to credit card and direct debit reports only. |
YES or NO. |
YES |
Bad Debt Write-Offs Report
The Bad Debt Write-Offs report (BadDebtWriteOff.rtf) lists all the bad debt write-offs recorded in your BRM system for a specified time period. This report groups write-offs by account number. For each write-off, the report shows the associated customer name, a comment explaining the reason for the write-off, the date the write-off became effective, and the amount written off.
Note:
For information on how BRM handles bad debt, see "About Write-Offs" in BRM Managing Accounts Receivable.
You can find the SQL query data model for this report in the following directory:
Reports/BRM Reports/Base BRM Reports/BadDebtWriteOff/BadDebtWriteOff.xdm
BadDebtWriteOff Parameters
You can change the following parameters to modify the output of the BadDebtWriteOff report:
-
State Details
-
Country
-
State
-
Billing Type
-
Start Date (date only, not date-time)
-
End Date (date only, not date-time)
If your BRM system does not contain state data, see "When to Modify Formulas for New Values".
Credit Card Reports
To help you manage credit card payments, the following reports are designed specifically for tracking credit card information:
-
The Credit Card Charges or Declines report enables you to track successful and unsuccessful credit card charges.
-
The Credit Cards Due to Expire report shows you when your customers' credit cards will expire, enabling you to provide continuous service to your customers by reminding them to update their credit information before it is outdated.
-
The Credit Card Transactions report helps you monitor transactions between your BRM system and your credit card processor.
Credit Card Charges or Declines Report
Note:
You must upload the CCChgDecl.source file that is in the StoredProcedures folder to the database after the base reports are installed. For more information about loading stored procedures, see "Loading Stored Procedures".
The Credit Card Charges or Declines report (CCChgDecl.rtf) displays either successful credit card charges or those that were declined (a single report cannot show both). Both versions of this report show the customer name, transaction date, credit card number, and amount charged or declined. In the decline version, the decline code is included for each transaction.
Note:
If credit card tokenization is enabled, the credit card versions of this report show only the tokens.
You can choose to mask the credit card numbers displayed in either version of this report. If you do, the report shows only the last four digits of each credit card number.
Note:
This report sorts credit card transactions by transaction ID. Because transaction IDs are stored as string data, this sorting method produces lists that are randomly, not chronologically, ordered. For example, the transaction ID T,13d,a might be assigned at an earlier date than the ID T,111d,a. When sorted, however, the string T,111d,a is listed before the string T,13d,a.
You can find the SQL query data model for this report in the following directory:
Reports/BRM Reports/Base BRM Reports/StoredProcedures/CCChgDecl.xdm
No charts are available for this report.
CCChgDecl Parameters
You can change the following parameters to modify the output of the CCChgDecl report:
-
Country
-
State
-
Account Status
-
Charge or Decline
-
Start Date (date only, not date-time)
-
End Date (date only, not date-time)
-
Mask CC Numbers
If your BRM system does not contain state data, see "When to Modify Formulas for New Values".
For information on parameters not described in Table 5-9, see "Understanding the Standard Parameters".
Table 5-9 CCChgDecl Parameters
Parameter | Description | Valid Values | Default Value |
---|---|---|---|
Charge or Decline |
The type of transaction displayed in the report. Select CHARGE to display successful charges or DECLINE to display unsuccessful charges. |
CHARGE or DECLINE |
CHARGE |
Mask CC Numbers |
The way credit card numbers are displayed. Select YES to show only the last four digits of all credit card numbers. Select NO to display the entire number. |
YES or NO |
YES |
Credit Cards Due to Expire Report
Note:
You must upload the CCExpire.source file that is in the StoredProcedures folder to the database after the base reports are installed. For more information about loading stored procedures, see "Loading Stored Procedures".
The Credit Cards due to Expire report (CCExpire.rtf) shows information about all accounts whose credit cards will expire during a one-month, two-month, or three-month time period. This information enables you to remind subscribers to update the expiration date of their cards or to provide new credit card numbers so that their service will continue without interruption.
The report shows the account numbers and customer names associated with the expiring credit cards. It also shows the number, owner name, and expiration date for each card.
You can choose to mask the credit card numbers displayed in the report. If you do, the report shows only the last four digits of each credit card number.
Note:
If credit card tokenization is enabled, the report shows only the tokens.
You can find the SQL query data model for this report in the following directory:
Reports/BRM Reports/Base BRM Reports/StoredProcedures/CCExpire.xdm
No charts are available for this report.
CCExpire Parameters
You can change the following parameters to modify the output of the CCExpire report:
-
Country
-
State
-
Account Status
-
Expiration Period
-
Mask CC Numbers
If your BRM system does not contain state data, see "When to Modify Formulas for New Values".
For information on parameters not described in Table 5-10, see "Understanding the Standard Parameters".
Table 5-10 CCExpire Parameters
Parameter | Description | Valid Values | Default Value |
---|---|---|---|
Expiration Period |
The time period covered by the report:
|
Current Month, Current & Next Months, or Current & Next 2 Months |
Current Month |
Mask CC Numbers |
The way credit card numbers are displayed. Select YES to show only the last four digits of all credit card numbers. Select NO to display the entire number. |
YES or NO |
YES |
Credit Card Transactions Report
The Credit Card Transactions report (CCTransactions.rtf) shows the status of all credit card transactions, both successful and failed, that were recorded in your BRM database during a specified time period. You can use this report to do the following:
-
Monitor quality of service on the lease line between your BRM system and your credit card processor.
-
Identify the types of credit card transactions that are failing. (For information on resolving failed credit card transactions, see "Resolving Failed BRM-Initiated Payment Transactions" in BRM Configuring and Collecting Payments.)
-
Check the total monetary amount of outstanding credit card transactions.
-
Learn the status of a particular customer's unresolved charge.
The CCTransactions report has two versions:
-
Without Transaction Details: This version shows the total number and monetary amount of credit card transactions that occurred during the specified time period. It divides the transactions into two groups: Successful and Failed. Within these groups, it lists the transactions by charge operation. Charge operations, shown in the Command column, include the following:
-
Verify: Verifies a customer's address.
-
Authorize Only: Authorizes a charge.
-
Deposit: Deposits a previously authorized charge.
-
Conditional Deposit: Authorizes and deposits a charge.
-
Refund: Refunds a charge.
-
Recover Payment: Creates payment events. See "pin_recover" in BRM Configuring and Collecting Payments.
-
Unknown: Indicates the charge operation is not known.
-
-
With Transaction Details: This version expands the report by displaying information about each transaction, including credit card number, transaction ID, date, and monetary amount.
Note:
If credit card tokenization is enabled, the report shows only the tokens.
You can choose to mask the credit card numbers displayed in this version. If you do, the report shows only the last four digits of each credit card number.
Both versions group totals by balance element.
You can find the SQL query data model for this report in the following directory:
Reports/BRM Reports/Base BRM Reports/CCTransactions/CCTransactions.xdm
CCTransactions Parameters
You can change the following parameters to modify the output of the CCTransactions report:
-
Transaction Details
-
Start Date (date only, not date-time)
-
End Date (date only, not date-time)
-
Mask CC Numbers
For information on parameters not described in Table 5-11, see "Understanding the Standard Parameters".
Table 5-11 CCTransactions Parameters
Parameter | Description | Valid Values | Default Value |
---|---|---|---|
Transaction Details |
The credit card number, ID, date, and amount of each transaction. Select YES to display this information. |
YES or NO |
NO |
Mask CC Numbers |
The way credit card numbers are displayed. Select YES to show only the last four digits of all credit card numbers. Select NO to display the entire number. This parameter applies only when Transaction Details is set to YES. |
YES or NO |
YES |
Customer Account Reports
The customer account reports track the number and service length of new and closed accounts. They also track the credit remaining in batches of accounts and track changes in the number of charge offer subscriptions.
The Account Changes - Single Time Period report displays new and closed account information for a single time period that you specify, and the Account Changes – Multiple Time Periods report displays new and closed account information in weekly or monthly intervals, whichever you select.
The New Account Lifetime report displays the service length of accounts opened during the specified time period. The Closed Account Lifetime report displays the service length of accounts closed during the specified time period. You can use this information to find out how many customers remain with the company after a particular sales promotion.
Note:
The "period" reports display information for new and closed accounts. The "lifetime" reports display information for new or closed accounts, whichever you select.
The Product Subscriptions report displays information about changes in the number of subscriptions for a particular charge offer during a time period you specify. The Product Cancellations report shows whether a subscription was canceled because the customer discontinued it or because the account containing the subscription was closed.
Account Changes - Single Time Period Report
The Account Changes - Single Time Period report (NCSinglePeriod.rtf) displays the number of new accounts, the number of closed accounts, and the net change in new and closed accounts for a single time period.
Note:
If the same account is opened and closed during the reporting period, the account is counted twice: once as a new account and once as a closed account.
You can find the SQL query data model for this report in the following directory:
Reports/BRM Reports/Base BRM Reports/NCSinglePeriod/NCSinglePeriod.xdm
NCSinglePeriod Parameters
You can change the following parameters to modify the output of the NCSinglePeriod report:
-
State Details
-
Account Details
-
Include Chart
-
Country
-
State
-
Billing Type
-
Start Date (date only, not date-time)
-
End Date (date only, not date-time)
If your BRM system does not contain state data, see "When to Modify Formulas for New Values".
To add the Promotion Code and Software Package parameters to this report, see "Adding Promotion Code and Software Package Parameters".
For parameter descriptions, see "Understanding the Standard Parameters".
Account Changes – Multiple Time Periods Report
Note:
-
You must upload the NCMultiplePeriods.source file that is in the StoredProcedures folder to the database after the base reports are installed. For more information about loading stored procedures, see "Loading Stored Procedures".
-
The Account Changes - Multiple Time Periods report (NCMultiplePeriods.rtf) displays the number of new and closed accounts and the net change in the number of accounts over multiple time periods. The difference between this report and the NCSinglePeriod report is that NCMultiplePeriods includes data for several time periods. You can choose to have these time periods divided into weeks or months. The weekly report shows 13 one-week periods, covering one fiscal quarter. The monthly report shows 12 one-month periods, covering one fiscal year. The multiple periods report does not include account details.
-
If the same account is opened and closed during the reporting period, the account is counted twice: once as a new account and once as a closed account.
You can find the SQL query data model for this report in the following directory:
Reports/BRM Reports/Base BRM Reports/StoredProcedures/NCMultiplePeriods.xdm
NCMultiplePeriods Parameters
You can change the following parameters to modify the output of the NCMultiplePeriods report:
-
State Details
-
Include Chart
-
Country
-
State
-
Billing Type
-
Time Period
-
Weekly Start Date
-
Monthly Start Date
If your BRM system does not contain state data, see "When to Modify Formulas for New Values".
To add the Promotion Code and Software Package parameters to this report, see "Adding Promotion Code and Software Package Parameters".
If you generate a chart, the first bar refers to the sum of New Account and the second bar refers to the sum of Closed Account for each period.
For information on parameters not described in Table 5-12, see "Understanding the Standard Parameters".
Table 5-12 NCMultiplePeriods Parameters
Parameter | Description | Valid Values | Default Value |
---|---|---|---|
Time Period |
The way the report output is divided. Can be by weeks or by months. |
WEEKS or MONTHS |
WEEKS |
Weekly Start Date |
The first day of the first week for the report. You set this parameter when you select WEEKS for the Time Period parameter. To select MONTHS, set the Monthly Start Date parameter instead. The report covers from 00:00:00 on the first day of the week you specify to 23:59:59 on the previous day of the week 13 weeks later. For example, if you enter "07/12/01," which is a Thursday, the report measures from 00:00:00 (midnight) on that Thursday to 23:59:59 on Wednesday, 10/10/01. |
Use MM/DD/YY or MM/DD/YYYY date format for the start date. |
91 days (13 weeks) before the current date. The default value is not displayed in the parameter prompt box. |
Monthly Start Date |
The first day of the first month for the report. You set this parameter when you select MONTHS for the Time Period parameter. To select WEEKS, set the Weekly Start Date parameter instead. The report measures from 00:00:00 on the first day of the month you specify to 23:59:59 on the last day of the month 12 months later. |
Use MM/YY or MM/YYYY date format for the month. |
12 months before the current month. The default value is not displayed in the parameter prompt box. |
New Account Lifetime Report
The New Account Lifetime report (NewLifetime.rtf) displays the length of service of currently active accounts created during a specified time period. This information is valuable if you want to see how many customers who opened accounts during a specified time period are still with your company.
Use the Offset Days and Period Days parameters to sort accounts by age.
You can find the SQL query data model for this report in the following directory:
Reports/BRM Reports/Base BRM Reports/NewLifetime/NewLifetime.xdm
NewLifetime Parameters
You can change the following parameters to modify the output of the NewLifetime report:
-
State Details
-
Include Chart
-
Offset Days
-
Period 1 Day
-
Period 2 Days
-
Period 3 Days
-
Country
-
State
-
Billing Type
-
Start Date (date only, not date-time)
-
End Date (date only, not date-time)
If your BRM system does not contain state data, see "When to Modify Formulas for New Values".
To add the Promotion Code and Software Package parameters to this report, see "Adding Promotion Code and Software Package Parameters".
For information on parameters not described in Table 5-13, see "Understanding the Standard Parameters".
Table 5-13 NewLifetime Parameters
Parameter | Description | Valid Values | Default Value |
---|---|---|---|
Offset Days |
The minimum number of days old an account must be before it is included in the report. For example, if you set Offset Days to 5 and an active account created during the reporting period is only four days old, it is not included. See "Day Ranges". |
0–366. |
0 |
Period 1 Day |
Number of days covered by the first time period. This number is added to the Offset Days value to define the first time period range. See "Day Ranges". |
1–366. |
30 (Offset Days plus 0–30 days before the current date). |
Period 2 Days |
Number of days covered by the second time period. See "Day Ranges". |
1–366. |
30 (Offset Days plus 31–60 days before the current date). |
Period 3 Days |
Number of days covered by the third time period. See "Day Ranges". |
1–366. |
30 (Offset Days plus 61–90 days before the current date). |
If you do not enter valid values for the above parameters, a blank report is generated.
Closed Account Lifetime Report
The Closed Account Lifetime report (ClosedLifetime.rtf) displays the number of accounts closed during a specified time period, and it measures the number of days the accounts were open before they were closed.
Use the Offset Days and Period Days parameters to sort accounts by length of life.
You can find the SQL query data model for this report in the following directory:
Reports/BRM Reports/Base BRM Reports/ClosedLifetime/ClosedLifetime.xdm
ClosedLifetime Parameters
You can change the following parameters to modify the output of the ClosedLifetime report:
-
State Details
-
Include Chart
-
Offset Days
-
Period 1 Day
-
Period 2 Days
-
Period 3 Days
-
Country
-
State
-
Billing Type
-
Start Date (date only, not date-time)
-
End Date (date only, not date-time)
If your BRM system does not contain state data, see "When to Modify Formulas for New Values".
To add the Promotion Code and Software Package parameters to this report, see "Adding Promotion Code and Software Package Parameters".
For information on parameters not described in Table 5-14, see "Understanding the Standard Parameters".
Table 5-14 ClosedLifetime Parameters
Parameter | Description | Valid Values | Default Value |
---|---|---|---|
Offset Days |
The minimum number of days old an account must be to be included in the report. For example, if you set Offset Days to 5, a four-day-old account closed during the reporting period is not included. See "Day Ranges". |
0–366. |
0 |
Period 1 Day |
Number of days covered by the first time period. This number is added to the Offset Days value to define the first time period range. See "Day Ranges". |
1–366. |
30 (Offset Days plus 0–30 days before the current date). |
Period 2 Days |
Number of days covered by the second time period. See "Day Ranges". |
1–366. |
30 (Offset Days plus 31–60 days before the current date). |
Period 3 Days |
Number of days covered by the third time period. See "Day Ranges". |
1–366. |
30 (Offset Days plus 61–90 days before the current date). |
If you do not enter valid values for the above parameters, a blank report is generated.
Product Subscriptions Report
The Product Subscriptions report (ProductSubscriptions.rtf) shows the change in the number of subscriptions for a particular charge offer over a time period you specify. You can use this information to determine which charge offers are driving your company's revenue.
This report shows the following information:
-
Subscriptions at Beginning: The number of subscriptions in active accounts at the beginning of the time period.
-
New Subscriptions: The number of subscriptions purchased during the time period.
-
Inactivated Subscriptions: The number of subscriptions in accounts whose status changed to inactive during the time period.
-
Activated Subscriptions: The number of subscriptions in accounts whose status changed to active during the time period.
-
Canceled/Closed Subscriptions: The number of subscriptions that were canceled during the period. To find out how many cancellations were due to account closures and how many were due to discontinued subscriptions, run the Product Cancellations report with the same parameter settings you used to run this report.
-
Subscriptions at End: The number of subscriptions in active accounts at the end of the period.
Note:
If an account becomes active and then is closed during the reporting period, the account is counted twice in the report (once as an active account and once as a closed account).
You can find the SQL query data model for this report in the following directory:
Reports/BRM Reports/Base BRM Reports/ProductSubscriptions/ProductSubscriptions.xdm
ProductSubscriptions Parameters
You can change the following parameters to modify the output of the ProductSubscriptions report:
-
Country
-
State
-
Product
-
Start Date (date only, not date-time)
-
End Date (date only, not date-time)
If your BRM system does not contain state data, see "When to Modify Formulas for New Values".
For information on parameters not described in Table 5-15, see "Understanding the Standard Parameters".
Table 5-15 ProductSubscription Parameters
Parameter | Description | Valid Values | Default Value |
---|---|---|---|
Product |
The name of the charge offer for which you want to track subscriptions. |
Any valid charge offer name or ALL. The parameter value list might not include all the charge offer names stored in your BRM database. To add charge offer names to this list, see "Adding Parameters in Oracle Analytics Publisher". |
ALL |
Product Cancellations Report
The Product Cancellations report (ProductCancellations.rtf) shows the total number of charge offer subscriptions that were canceled during a time period you specify. This report lists cancellations by charge offer. For each charge offer, the following information is provided:
-
Total Cancellations: The total number of subscriptions that were canceled during the specified time period. This number matches the number in the Canceled/Closed Subscriptions column of the Product Subscriptions report for the same time period.
-
Cancellations due to Account Closure: The number of subscriptions that were indirectly canceled when the accounts containing them were closed. This is a subset of Total Cancellations.
-
Cancellations due to Discontinued Subscriptions: The number of subscriptions that were directly canceled, even though the accounts containing them remained active. This is a subset of Total Cancellations.
Note:
This report sorts charge offers numerically by POID, not alphabetically by charge offer name.
You can find the SQL query data model for this report in the following directory:
Reports/BRM Reports/Base BRM Reports/ProductCancellations/ProductCancellations.xdm
ProductCancellations Parameters
You can change the following parameters to modify the output of the ProductCancellations report:
-
Product
-
Start Date (date only, not date-time)
-
End Date (date only, not date-time)
For information on parameters not described in Table 5-16, see "Understanding the Standard Parameters".
Table 5-16 ProductCancellations Parameters
Parameter | Description | Valid Values | Default Value |
---|---|---|---|
Product |
The name of the charge offer whose cancellations you want to show. |
Any valid charge offer name or ALL The parameter value list might not include all the charge offer names stored in your BRM database. To add charge offer names to this list, see "Adding Parameters in Oracle Analytics Publisher". |
ALL |
Device Management Framework Reports
The Device Management framework reports provide information about devices in BRM. For information on the Device Management framework, see "Managing Devices with BRM" in BRM Developer's Guide.
These reports help you track the current status and status history of your devices.
Device Status Report
The Device Status report (DeviceStatus.rtf) shows the current status of devices grouped by device type. See "Creating Devices" in BRM Developer's Guide.
For each device, the report shows the following information:
-
Device Status: The current status of the device.
-
Device ID: The unique ID given to the device.
To generate a report that shows devices with a specific status, you must select a device type. If you select a status, such as ASSIGNED, but not a type, such as /device/sim/, data will not be returned.
No charts are available for this report.
The SQL query data model for this report is in the following directory:
Reports/BRM Reports/Base BRM Reports/DeviceStatus/DeviceStatus.xdm
No charts are available for this report.
DeviceStatus Parameters
You can change the following parameters to modify the output of the DeviceStatus report:
-
Device Status
-
Device Type
For information on parameters not described in Table 5-17, see "Understanding the Standard Parameters".
Table 5-17 DeviceStatus Parameters
Parameter | Description | Valid Values | Default Value |
---|---|---|---|
Device Status |
The status of the device, such as new. For information, see "Managing the Device Life Cycle" in BRM Developer's Guide. |
Any valid device status or ALL |
ALL |
Device Type |
A group of devices that have similar characteristics, such as /device/num for BRM Number Manager devices. |
Any valid device type or ALL |
ALL |
Device History Report
The Device History report (DeviceHistory.rtf) shows the history of devices, grouped by device type. See "Creating Devices" in BRM Developer's Guide.
For each device, the report shows the following information:
-
Device ID: The unique ID given to a device.
-
Device Status: The status of the device on the date the report is generated date as well as all prior changes in status.
-
Start Date: The date and time that a device entered a certain device status.
-
End Date: The date and time that a device stopped being in a certain device status.
You can find the SQL query data model for this report in the following directory:
Reports/BRM Reports/Base BRM Reports/DeviceHistory/DeviceHistory.xdm
DeviceHistory Parameters
You can change the following parameters to modify the output of the DeviceHistory report:
-
Device Type
-
Device ID
-
Start Date
-
End Date
For information on parameters not described in Table 5-18, see "Understanding the Standard Parameters".
Table 5-18 DeviceHistory Parameters
Parameter | Description | Valid Values | Default Value |
---|---|---|---|
Device Type |
A group of devices that have similar characteristics, such as /device/num for BRM Number Manager devices. |
Any valid device type, or ALL |
ALL |
Device ID |
The unique ID associated with an individual device. |
Any valid device ID, or ALL |
ALL |
Dialup Usage Reports
The dialup usage reports provide information about dialup connections. These reports measure the quantity and duration of customer dialup sessions.
The POP Usage report displays the POP usage of customers for both network and terminal server activity.
The Current Sessions by Session Length report and Historic Sessions by Session Length report display the number and duration of currently open and past customer dialup sessions, respectively.
POP Usage Report
Note:
You must upload the SessionPOP.source file that is in the StoredProcedures folder to the database after the base reports are installed. For more information about loading stored procedures, see "Loading Stored Procedures".
The POP Usage report (SessionPOP.rtf) lists the POPs (Points of Presence) your customers use to log in to the Internet, the number of sessions handled by each POP, and the average length of those sessions. This report tracks dialup usage by terminal server or by network ID.
Note:
Terminal servers are also known as Network Access Servers (NASs).
The values displayed in the Average Length of Session column are rounded to two decimal places from six decimal places stored in the BRM database. Totals for this column are derived by adding the original six decimal values and then rounding the sum to two decimals. Sometimes the rounding makes the displayed total look incorrect.
You can find the SQL query data model for this report in the following directory:
Reports/BRM Reports/Base BRM Reports/StoredProcedures/SessionPOP.xdm
SessionPOP Parameters
You can change the following parameters to modify the output of the SessionPOP report:
-
State Details
-
Include Chart
-
Country
-
State
-
Billing Type
-
Account Status
-
Terminal Server or Network ID
-
Start Date (date only, not date-time)
-
End Date (date only, not date-time)
If your BRM system does not contain state data, see "When to Modify Formulas for New Values".
To add the Promotion Code and Software Package parameters to this report, see "Adding Promotion Code and Software Package Parameters".
For information on parameters not described in Table 5-19, see "Understanding the Standard Parameters".
Table 5-19 POP Usage Report Parameters
Parameter | Description | Valid Values | Default Value |
---|---|---|---|
Terminal Server or Network ID |
The way POPs are identified in the report. Select T to identify POPs by terminal server ID. Select N to identify POPs by network ID. |
T or N |
T |
Current Sessions by Session Length Report
The Current Sessions by Session Length report (UsageTimeCurrent.rtf) displays the number of customer dialup sessions that are open at the time the report is generated. The sessions are grouped by duration. The duration time periods are measured in minutes before the current time.
UsageTimeCurrent Parameters
You can change the following parameters to modify the output of the UsageTimeCurrent report:
-
State Details
-
Include Chart
-
Offset Mins
-
Period 1 Mins
-
Period 2 Mins
-
Period 3 Mins
-
Country
-
State
-
Billing Type
If your BRM system does not contain state data, see "When to Modify Formulas for New Values".
To add the Promotion Code and Software Package parameters to this report, see "Adding Promotion Code and Software Package Parameters".
For information on parameters not described in Table 5-20, see "Understanding the Standard Parameters".
Table 5-20 Current Sessions by Session Length Report Parameters
Parameter | Description | Valid Values | Default Value |
---|---|---|---|
Offset Mins |
The minimum number of minutes old a usage session must be before it is included in the report. For example, if you set Offset Mins to 5 and a particular usage session is less than 5 minutes old, it is not included. See "Minute Ranges". |
0–1440. |
0 |
Period 1 Mins |
Number of minutes covered by the first time period. See "Minute Ranges". |
1–1440. |
15 (Offset Mins plus 0–15 minutes before the current time). |
Period 2 Mins |
Number of minutes covered by the second time period. See "Minute Ranges". |
1–1440. |
15 (Offset Mins plus 15–30 minutes before the current time). |
Period 3 Mins |
Number of minutes covered by the third time period. See "Minute Ranges". |
1–1440. |
15 (Offset Mins plus 30–45 minutes before the current time). |
Historic Sessions by Session Length Report
The Historic Sessions by Session Length report (UsageTimeHistoric.rtf) displays the number of customer dialup sessions that took place within a specified time period. Only sessions that ended within the time period are shown in this report. The sessions are grouped by duration.
You can find the SQL query data model for this report in the following directory:
Reports/BRM Reports/Base BRM Reports/UsageTimeHistoric/UsageTimeHistoric.xdm
UsageTimeHistoric Parameters
You can change the following parameters to modify the output of the UsageTimeHistoric report:
-
State Details
-
Include Chart
-
Offset Mins
-
Period 1 Mins
-
Period 2 Mins
-
Period 3 Mins
-
Country
-
State
-
Billing Type
-
Start Date (date only, not date-time)
-
End Date (date only, not date-time)
If your BRM system does not contain state data, see "When to Modify Formulas for New Values".
To add the Promotion Code and Software Package parameters to this report, see "Adding Promotion Code and Software Package Parameters".
For information on parameters not described in Table 5-21, see "Understanding the Standard Parameters".
Table 5-21 Historic Sessions by Session Length Report Parameters
Parameter | Description | Valid Values | Default Value |
---|---|---|---|
Offset Mins |
The minimum number of minutes old a usage session must be to be included in the report. For example, if you set Offset Mins to 5 and a particular usage session that falls within the start and end dates of the report is less than 5 minutes old, it is not included. See "Minute Ranges". |
0–1440. |
0 |
Period 1 Mins |
Number of minutes covered by the first time period. See "Minute Ranges". |
1–1440. |
15 (sessions that were Offset Mins plus 0–15 minutes long). |
Period 2 Mins |
Number of minutes covered by the second time period. See "Minute Ranges". |
1–1440. |
15 (sessions that were Offset Mins plus 15–30 minutes long). |
Period 3 Mins |
Number of minutes covered by the third time period. See "Minute Ranges". |
1–1440. |
15 (sessions that were Offset Mins plus 30–45 minutes long). |
Dropped Sessions Report
Note:
You must upload the DroppedSessions.source file that is in the StoredProcedures folder to the database after the base reports are installed. For more information about loading stored procedures, see "Loading Stored Procedures".
The Dropped Sessions report (DroppedSessions.rtf) shows dialup sessions that were not rated because of a lost connection between the RADIUS server and your BRM system.
Revenue leaks can occur if the connection between the RADIUS server and BRM is dropped in the middle of a session. To help you identify such leaks, this report lists dialup sessions that have no end time.
Note:
Dropped sessions are indistinguishable from current sessions until users log back in to the terminal server with the same account they used for the dropped session. When they do, BRM creates a new session for the account and updates the dropped session's EVENT_SESSION_DIALUP_T.TERM_CAUSE field to indicate that the session was dropped. This report shows data only for sessions that BRM has identified as dropped.
You can find the SQL query data model for this report in the following directory:
Reports/BRM Reports/Base BRM Reports/StoredProcedures/DroppedSessions.xdm
DroppedSessions Parameters
You can change the following parameters to modify the output of the Dropped Sessions report:
-
State Details
-
Country
-
State
-
Terminal Server
-
Start Date (date only, not date-time)
-
End Date (date only, not date-time)
If your BRM system does not contain state data, see "When to Modify Formulas for New Values".
For information on parameters not described in Table 5-22, see "Understanding the Standard Parameters".
Table 5-22 Dropped Sessions Report Parameters
Parameter | Description | Valid Values | Default Value |
---|---|---|---|
Terminal Server |
A terminal server that your customers connect to when dialing in to your IP services. Also known as a Network Access Server (NAS). |
Any valid terminal server IP address or host name, or ALL If terminal servers can be identified by either IP address or host name in your BRM system, select ALL to ensure that all records for every terminal server are included in the report. |
ALL |
General Ledger Reports
The general ledger (G/L) reports provide information about your company's revenue:
-
The General Ledger Summary report lists the balance impacts for G/L debit and credit accounts by balance element for a time period you specify.
-
The General Ledger Detail report lists the balance impacts by customer account.
-
The General Ledger Preview report lists the balance impacts for G/L debit and credit accounts by balance element for a time period you specify.
-
The General Ledger Chart of Accounts report lists all G/L debit accounts and G/L credit accounts by G/L ID and G/L ID description. This report is helpful if you want to see all the G/L accounts currently configured in the BRM database.
-
The Product Revenue report breaks your company's revenue down by charge offer for a time period you specify. You do not need to assign G/L IDs to your charge offers to include them in this report.
General Ledger Chart of Accounts Report
The General Ledger Chart of Accounts report (GLAccounts.rtf) shows a simple list of all G/L debit accounts, G/L credit accounts, G/L IDs, and G/L ID descriptions in your BRM database. You can run this report to check the G/L information that has been set up for your BRM database or to see the G/L ID descriptions referenced by G/L ID numbers in a GLDetail report. The GLAccounts report has no user-entered parameters.
You can find the SQL query data model for this report in the following directory:
Reports/BRM Reports/Base BRM Reports/GLAccounts/GLAccounts.xdm
General Ledger Summary Report
The General Ledger Summary report (GLSummary.rtf) lists the balance impacts for G/L debit and credit accounts by balance element for a time period and bill type you specify. You can choose to show only a particular balance element or only the G/L accounts for a particular G/L ID.
To populate the BRM database with the data needed for the summary report:
-
Generate an Individual Summary report using the same start and end dates and the same bill type by running either:
-
The pin_ledger_report utility. See "Creating Individual G/L Reports with pin_ledger_report" in BRM Collecting General Ledger Data.
-
A general ledger job in Business Operations Center. See "Running G/L Jobs in Business Operations Center" in BRM Collecting General Ledger Data.
-
-
Run the report in Oracle Analytics Publisher to format and display the data.
You can find the SQL query data model for this report in the following directory:
Reports/BRM Reports/Base BRM Reports/GLSummary/GLSummary.xdm
GLSummary Parameters
You can change the following parameters to modify the output of the GLSummary report:
-
Report Type
-
Resource Name (balance element name)
-
G/L Attribute
-
G/L Segment
-
G/L ID
-
Start Date (date used for last run of pin_ledger_report utility)
-
End Date (date used for last run of pin_ledger_report utility)
For information on parameters not described in Table 5-23, see "Understanding the Standard Parameters".
Table 5-23 General Ledger Summary Report Parameters
Parameter | Description | Valid Values | Default Value |
---|---|---|---|
Report Type |
The type of billing items you want to show in the report. For information on each item type, see "About Event-Based Revenue Recognition" in BRM Collecting General Ledger Data. |
Any of the following:
|
BILLED |
Resource |
The balance element affected by the G/L debit and credit account entries. |
All Resources, All Currencies, All Noncurrencies, or individual balance elements set up for your BRM database. The parameter list might not include all the balance element names stored in your BRM database. To add balance element names to this list, see "Adding Parameters in Oracle Analytics Publisher". |
All Resources |
G/L Attribute |
The G/L account pair attribute you want to display report data for. See "About G/L IDs" in BRM Collecting General Ledger Data. |
ALL, NET, DISC, TAX, or GROSS. |
ALL. |
G/L Segment |
The G/L segment you want to run the report for. |
The G/L segment you specified when you ran pin_ledger_report. |
A period ( . ), which means to run the report with the default BRM G/L segment value. If you did not use the default G/L segment to run pin_ledger_report, make sure you enter the value you did use. |
G/L ID |
The general ledger ID value that maps to G/L debit and credit accounts. |
Any G/L ID set up for your BRM database or ALL. The parameter list might not include all the G/L IDs stored in your BRM database. To add G/L IDs to this list, see "Adding Parameters in Oracle Analytics Publisher". To list all the G/L IDs in your database, run the GLAccounts report. |
ALL |
General Ledger Preview Report
The General Ledger Preview report (GLPreviewReport_en_US.rtf) lists the balance impacts for G/L debit and credit accounts for a specified time period.
To populate the BRM database with the data needed for the summary report:
-
Generate a Preview report by running either:
-
The pin_ledger_report utility. See "Creating Preview G/L Reports with pin_ledger_report" in BRM Collecting General Ledger Data.
-
A general ledger job in Business Operations Center. See "Running G/L Jobs in Business Operations Center" in BRM Collecting General Ledger Data.
-
-
Run the report in Publisher to format and display the data.
You can find the SQL query data model for this report in the following directory:
Reports/BRM_Reports/Base_BRM_Reports/GLPreviewReport/GLPreviewReport.xdm
General Ledger Detail Report
The General Ledger Detail report (GLDetail.rtf) lists the balance impacts for G/L debit and credit accounts by customer account for a time period and bill type you specify. You can choose to show only a particular balance element or only the G/L accounts for a particular G/L ID.
To populate the BRM database with the data needed for the summary report:
-
Generate an Individual Detail report using the same start and end dates and the same bill type by running either:
-
The pin_ledger_report utility. See "Creating Individual G/L Reports with pin_ledger_report" in BRM Collecting General Ledger Data.
-
A general ledger job in Business Operations Center. See "Running G/L Jobs in Business Operations Center" in BRM Collecting General Ledger Data.
-
-
Run the report in Publisher to format and display the data.
This report shows customer accounts, affected balances, G/L accounts, and the impact of each G/L entry. This report also shows the date, G/L ID, and bill number of each G/L entry.
You can find the SQL query data model for this report in the following directory:
Reports/BRM Reports/Base BRM Reports/GLDetail/GLDetail.xdm
GLDetail Parameters
You can change the following parameters to modify the output of the GLDetail report:
-
Report Type
-
Resource (balance element)
-
G/L Attribute
-
G/L Segment
-
G/L ID
-
Start Date (date used for last run of pin_ledger_report utility)
-
End Date (date used for last run of pin_ledger_report utility)
For information on parameters not described in Table 5-24, see "Understanding the Standard Parameters".
Table 5-24 General Ledger Detail Report Parameters
Parameter | Description | Valid Values | Default Value |
---|---|---|---|
Report Type |
The type of billing items you want to show in the report. For information on each item type, see "About Revenue Recognition" in BRM Collecting General Ledger Data. |
Any of the following:
|
BILLED |
Resource |
The balance element affected by the G/L debit and credit account entries. |
All Resources, All Currencies, All Noncurrencies, or individual balance elements set up for your BRM database. The parameter value list might not include all the balance element names stored in your BRM database. To add balance element names to this list, see "Adding Parameters in Oracle Analytics Publisher". |
All Resources |
G/L Attribute |
The G/L account pair attribute you want to display report data for. See "About G/L IDs" in BRM Collecting General Ledger Data. |
ALL, NET, DISC, TAX, or GROSS |
ALL |
G/L Segment |
The G/L segment you want to run the report for. |
The G/L segment you specified when you ran pin_ledger_report. |
A period ( . ), which means to run the report with the default BRM G/L segment value. If you did not use the default G/L segment to run pin_ledger_report, make sure you enter the value you did use. |
G/L ID |
The general ledger ID value that maps to G/L debit and credit accounts. |
Any G/L ID set up for your BRM database or ALL. The parameter value list might not include all the G/L IDs stored in your BRM database. To add G/L IDs to this list, see "Adding Parameters in Oracle Analytics Publisher". To list all the G/L IDs in your database, run the GLAccounts report. |
ALL |
Product Revenue Report
The Product Revenue report (ProductRevenue.rtf) shows revenue by charge offer for a time period you specify. It shows the gross and net revenue and the discount (if any) for each charge offer. You do not need to assign G/L IDs to your charge offers to include them in this report.
This report shows the total revenue for each charge offer, but it does not show which part of the revenue is earned and which is unearned. To see that, you must assign G/L IDs to the charge offers and run a General Ledger Detail report. See "About G/L IDs" in BRM Collecting General Ledger Data.
The ProductRevenue report includes a six-section pie chart. Each of the top five revenue-making charge offers is represented by one of the pie sections. The sixth section represents the revenue attributed to the rest of the charge offers. This report produces a pie chart for each reported currency (US Dollar, euro, and so on).
You can find the SQL query data model for this report in the following directory:
Reports/BRM Reports/Base BRM Reports/ProductRevenue/ProductRevenue.xdm
ProductRevenue Parameters
You can change the following parameters to modify the output of the ProductRevenue report:
-
Include Chart
-
Country
-
State
-
Product
-
Start Date (date only, not date-time)
-
End Date (date only, not date-time)
If your BRM system does not contain state data, see "When to Modify Formulas for New Values".
For information on parameters not described in Table 5-25, see "Understanding the Standard Parameters".
Table 5-25 Product Revenue Report Parameters
Parameter | Description | Valid Values | Default Value |
---|---|---|---|
Product |
The name of the charge offer whose revenue you want to show. |
Any valid charge offer name or ALL. The parameter value list might not include all of the charge offer names stored in your BRM database. To add charge offer names to this list, see "Adding Parameters in Oracle Analytics Publisher". |
ALL |
Miscellaneous Adjustments Reports
The miscellaneous adjustments reports display miscellaneous adjustments only; they do not show A/R adjustments. (You can see A/R adjustments for accounts by using Billing Care.) You can generate summary reports (see "Miscellaneous Adjustments Summary Report") or detail reports (see "Miscellaneous Adjustments Detail Report"). The only difference between these two reports is that the detail report includes event data, showing every adjustment rather than just the total amount adjusted for each account. You can use these reports to track the activity of CSRs and to see what balances they are adjusting.
Miscellaneous Adjustments Summary Report
The Miscellaneous Adjustments Summary report (MASummary.rtf) displays the adjusted balances, amount credited or debited, and net change for the time period you select. CSR information is obtained by determining the session in which the adjustment event occurred and then getting the service login (the CSR's login name) used for that session. You can include a pie chart in this report.
You can find the SQL query data model for this report in the following directory:
Reports/BRM Reports/Base BRM Reports/MASummary/MASummary.xdm
MASummary Parameters
You can change the following parameters to modify the output of the MASummary report:
-
State Details
-
Include Chart
-
Country
-
State
-
Billing Type
-
Account Status
-
Start Date (date only, not date-time)
-
End Date (date only, not date-time)
If your BRM system does not contain state data, see "When to Modify Formulas for New Values".
To add the Promotion Code and Software Package parameters to this report, see "Adding Promotion Code and Software Package Parameters".
For parameter descriptions, see "Understanding the Standard Parameters".
Miscellaneous Adjustments Detail Report
The Miscellaneous Adjustments Detail report (MADetail.rtf) expands the MASummary report by displaying each adjusted account, including the account number, the adjustments made to the account during the specified time period, the date of each adjustment, the reason for the adjustment, and the CSR who made the adjustment.
You can find the SQL query data model for this report in the following directory:
Reports/BRM Reports/Base BRM Reports/MADetail/MADetail.xdm
MADetail Parameters
You can change the following parameters to modify the output of the MADetail report:
-
State Details
-
Country
-
State
-
Billing Type
-
Account Status
-
Start Date (date only, not date-time)
-
End Date (date only, not date-time)
If your BRM system does not contain state data, see "When to Modify Formulas for New Values".
To add the Promotion Code and Software Package parameters to this report, see "Adding Promotion Code and Software Package Parameters".
For parameter descriptions, see "Understanding the Standard Parameters".
Tax Reports
Note:
To use the reports with Vertex or a custom tax calculation program, you must customize the reports.
The following reports display taxation information for the United States and Canada:
-
The "Tax Jurisdiction Summary Report" shows the total amount of city, county, state, or Federal sales tax by jurisdiction for items billed during a specified time period.
-
The "Tax Jurisdiction Detail Report" expands the summary report by displaying nonpaying bill unit (/billinfo object) data.
-
The "Summary of All Taxes Report" shows the sum of all city, county, state, and Federal sales taxes for items billed during a specified time period.
-
The "Tax Exempt Accounts Report" displays tax exemption information for all tax-exempt accounts.
These reports show data for billed events only.
Some of these reports show secondary city, county, and Federal taxes. (Secondary taxes are applied when more than one entity has tax jurisdiction.) In all the tax reports except Tax Exempt Accounts, taxes are grouped by tax supplier if you set the Tax Supplier Details parameter to YES.
Note:
You must upload the BIPTaxes.source file that is in the StoredProcedures folder to the database after the base reports are installed. For more information about loading stored procedures, see "Loading Stored Procedures".
Tax Jurisdiction Summary Report
Note:
You must upload the TaxJurSummary.source file that is in the StoredProcedures folder to the database after the base reports are installed. For more information about loading stored procedures, see "Loading Stored Procedures".
The Tax Jurisdiction Summary report (TaxJurSummary.rtf) displays the gross sales, the amount of the gross that is tax exempt, the amount of the gross that is taxable, and the total city, county, state, or Federal sales tax for items billed during a specified time period. Taxes are grouped by jurisdiction. If you show tax supplier details, the report also groups the information by tax supplier. The TaxJurSummary report shows only parent account information; nonpaying bill unit information is displayed in the TaxJurDetail report.
You can find the SQL query data model for this report in the following directory:
Reports/BRM Reports/Base BRM Reports/StoredProcedures/TaxJurSummary.xdm
TaxJurSummary Parameters
You can change the following parameters to modify the output of the TaxJurSummary report:
-
Country (either US for the United States or CA for Canada)
-
State
-
Federal, State, County, or City Tax
-
Tax Supplier Details
-
Tax Supplier
-
Start Date (date only, not date-time)
-
End Date (date only, not date-time)
If your BRM system does not contain state data, see "When to Modify Formulas for New Values".
For information on parameters not described in Table 5-26, see "Understanding the Standard Parameters".
Table 5-26 Tax Jurisdiction Summary Report Parameters
Parameter | Description | Valid Values | Default Value |
---|---|---|---|
Federal, State, County, or City Tax |
The tax jurisdiction:
|
FEDERAL, STATE, COUNTY, or CITY |
FEDERAL |
Tax Supplier Details |
The parameter that determines whether taxes are sorted by tax supplier. Select YES to sort taxes by supplier. |
YES or NO |
NO |
Tax Supplier |
The supplier of a charge offer, bundle, or service. |
Any valid tax supplier name or ALL |
ALL |
Tax Jurisdiction Detail Report
Note:
You must upload the TaxJurDetail.source file that is in the StoredProcedures folder to the database after the base reports are installed. For more information about loading stored procedures, see "Loading Stored Procedures".
The Tax Jurisdiction Detail report (TaxJurDetail.rtf) displays tax information for items billed during a specified time period. This report expands the TaxJurSummary report by displaying nonpaying bill unit data. You can use this report for auditing purposes.
You can find the SQL query data model for this report in the following directory:
Reports/BRM Reports/Base BRM Reports/StoredProcedures/TaxJurDetail.xdm
TaxJurDetail Parameters
You can change the following parameters to modify the output of the TaxJurDetail report:
-
Country (either US for the United States or CA for Canada)
-
State
-
Federal, State, County, or City Tax
-
Tax Supplier Details
-
Tax Supplier
-
Start Date (date only, not date-time)
-
End Date (date only, not date-time)
If your BRM system does not contain state data, see "When to Modify Formulas for New Values".
For information on parameters not described in Table 5-27, see "Understanding the Standard Parameters".
Table 5-27 Tax Jurisdiction Detail Report Parameters
Parameter | Description | Valid Values | Default Value |
---|---|---|---|
Federal, State, County, or City Tax |
The tax jurisdiction:
|
FEDERAL, STATE, COUNTY, or CITY |
FEDERAL |
Tax Supplier Details |
The parameter that determines whether taxes are sorted by tax supplier. Select YES to sort taxes by supplier. |
YES or NO |
NO |
Tax Supplier |
The supplier of a charge offer, bundle, or service. |
Any valid tax supplier name or ALL |
ALL |
Summary of All Taxes Report
Note:
You must upload the TaxAllSummary.source file that is in the StoredProcedures folder to the database after the base reports are installed. For more information about loading stored procedures, see "Loading Stored Procedures".
The Summary of All Taxes report (TaxAllSummary.rtf) displays summary information for Federal, state, county, and city taxes. Secondary taxes for state, county, and city taxes are included. Individual account information is displayed, but item details are not available for this report.
You can find the SQL query data model for this report in the following directory:
Reports/BRM Reports/Base BRM Reports/StoredProcedures/TaxAllSummary.xdm
TaxAllSummary Parameters
You can change the following parameters to modify the output of the TaxAllSummary report:
-
State Details
-
Country (either US for the United States or CA for Canada)
-
State
-
Tax Supplier Details
-
Tax Supplier
-
Start Date (date only, not date-time)
-
End Date (date only, not date-time)
If your BRM system does not contain state data, see "When to Modify Formulas for New Values".
For information on parameters not described in Table 5-28, see "Understanding the Standard Parameters".
Table 5-28 Summary of all Taxes Report Parameters
Parameter | Description | Valid Values | Default Value |
---|---|---|---|
Tax Supplier Details |
The parameter that determines whether taxes are sorted by tax supplier. Select YES to sort taxes by supplier. |
YES or NO |
NO |
Tax Supplier |
The supplier of a charge offer, bundle, or service. |
Any valid tax supplier name or ALL |
ALL |
Tax Exempt Accounts Report
Note:
You must upload the TaxExempt.source file that is in the StoredProcedures folder to the database after the base reports are installed. For more information about loading stored procedures, see "Loading Stored Procedures".
The Tax Exempt Accounts report (TaxExempt.rtf) displays primary and secondary tax exemptions for Federal, state, county, and city taxes. All tax-exempt accounts in your BRM system are included in this report. For each account, the report shows the account number, customer name, and billing address. It then lists the account's tax exemption certificates, including each certificate's jurisdiction, exemption percentage, number, effective date, and expiration date. Expired certificates are flagged with an asterisk ( * ). Certificates that are not yet effective are flagged with a plus sign ( + ).
There are no start and end dates for this report.
You can find the SQL query data model for this report in the following directory:
Reports/BRM Reports/Base BRM Reports/StoredProcedures/TaxExempt.xdm
TaxExempt Parameters
You can change the following parameters to modify the output of the TaxExempt report:
-
State Details
-
Country
-
State
-
Start Date (date only, not date-time)
-
End Date (date only, not date-time)
If your BRM system does not contain state data, see "When to Modify Formulas for New Values".
See "Understanding the Standard Parameters" for parameter descriptions.
Remittance Reports
These reports help you track the payments owed to each remittance account in your BRM system.
Remittance Report
The Remittance report (Remittance.rtf) lists the remittance owed to each remittance account in your BRM system. See "Remitting Funds to Third Parties" in BRM Configuring and Running Billing.
You can find the SQL query data model for this report in the following directory:
Reports/BRM Reports/Base BRM Reports/Remittance/Remittance.xdm
Note:
Before running the Remittance report, you must run the pin_remittance utility to populate the BRM database with data required by the Remittance report. See "pin_remittance" in BRM Configuring and Running Billing. The time period covered by the report should match the period covered by one or more runs of pin_remittance.Caution:
Do not run this report in a multischema environment. If you do, it might be inaccurate. For more information, see "Creating Remittance Reports" in BRM Configuring and Running Billing.Remittance Parameters
You can change the following parameters to modify the output of the Remittance report:
-
Account number
-
Country
-
State
-
Start Date (date only, not date-time)
-
End Date (date only, not date-time)
If your BRM system does not contain state data, see "When to Modify Formulas for New Values".
For information on parameters not described in Table 5-29, see "Understanding the Standard Parameters".
Table 5-29 Remittance Report Parameters
Parameter | Description | Valid Values | Default Value |
---|---|---|---|
Account number |
The number of the remittance account whose payments you want to show in the report. Note: Remittance accounts do not make payments; they receive payments. The payments shown in this report are payments you make to third parties. |
Any valid remittance account number or ALL. |
ALL |