This chapter describes how to design, generate, and customize Oracle Communications Billing and Revenue Management (BRM) invoice documents using Oracle Business Intelligence (BI) Publisher 11g.
Before reading this document, you should be familiar with the following:
Basic database administration concepts. See your database documentation.
BI Publisher 11g functions. See your BI Publisher documentation.
Note:
For information on integrating BRM with Oracle BI Publisher 10g, see "Designing and Generating Invoices in Oracle Business Intelligence Publisher 10g".BRM comes with a pre-integrated invoicing framework. This framework uses BI Publisher as the publishing application, allowing users to create more stylized and detailed invoices in PDF format. BRM also includes sample RTF templates for individual customers and for corporate customers. In these templates, some of the core business functions are pre-built and can be customized. You can configure and extend these templates for your specific business needs.
With BI Publisher 11g, you use an SQL bursting query, which is the process of splitting data into blocks and generating documents for those blocks. Bursting increases throughput performance in BI Publisher. With increased throughput, you reduce the number of times you have to extract data to create invoices. For example, in a non-bursting environment, 50,000 invoices would require BI Publisher to connect to BRM 50,000 times, which can significantly reduce system performance. In a bursting environment, you can tune BI Publisher by setting a chunk size parameter. In the example of creating 50,000 invoices, BI Publisher only has to connect to BRM 5 times if you set the chunk size to 10,000.
BI Publisher allows you to choose from a variety of desktop tools, such as Microsoft Word and Adobe Acrobat, to create document templates. While BI Publisher supports data sources such as database, Web services, and files, the BRM-BI Publisher invoicing integration only uses SQL queries directly on the database as the data source for better performance. By default, the BRM-BI Publisher invoicing integration supports PDF as an output format and delivery options such as email, FTP, and SFTP.
The pre-integrated invoicing framework eliminates the need for any proprietary invoice design tools, resulting in lowered costs. BI Publisher leverages familiar desktop software, so using the software requires minimal learning.
To use this feature, you must install BI Publisher and BI Publisher Desktop.
Note:
BRM-BI Publisher 11g invoicing integration is certified only on WebLogic Server, which is bundled with the BI Publisher product. The BRM-BI Publisher invoicing integration is certified on BI Publisher 11.1.1.7 and 11.1.1.9.BI Publisher is an enterprise reporting solution to author, manage, and deliver all types of formatted documents. You can customize the layout of the generated invoice document using the template builder capabilities of BI Publisher. A typical invoice document generated using BRM-BI Publisher integration is in "Understanding Invoice Layout".
When you use the BRM-BI Publisher integration:
BRM generates the invoicing data in XML format and then passes the data to BI Publisher.
BI Publisher formats the invoice, delivers, and stores it in the BI Publisher database.
Figure 3-1 shows the BRM and BI Publisher invoice generation process:
Figure 3-1 BRM and BI Publisher Invoice Generation Process
The BRM-BI Publisher integration generates invoice documents as follows:
Note:
The BRM-BI Publisher integration must be enabled. See "Enabling the BRM-BI Publisher Integration".BRM associates bill units with a BI Publisher invoice and report. When accounts are created, BRM associates each bill unit with a BI Publisher invoice report and layout template. See "About Associating Bill Units with a BI Publisher Invoice and Report".
Integrating BRM and BI Publisher for generating invoice documents. To generate invoice documents using BRM-BI Publisher integration framework, you must enable the integration between BRM and BI Publisher. See "Enabling the BRM-BI Publisher Integration".
BRM generates a bill. BRM creates a bill for each account bill unit when it runs the pin_bill_accts utility as part of the daily billing script. BRM needs a bill before generating an invoice. See "Running Billing Utilities" in BRM Configuring and Running Billing.
BRM generates the invoice and stores it in the BRM database. When the pin_inv_accts utility is executed, it stores the invoice data in an /invoice object in the BRM database. See "Generating Invoices".
Note:
To generate invoice documents in BI Publisher, you must store the invoice data in XML format. See "About Formats for Storing Invoices".The pin_inv_doc_gen utility:
Runs BI Publisher reports. This utility, based on the input parameters, starts and runs appropriate BI Publisher reports for invoice documents. It runs a BI Publisher invoicing report, with the SQL bursting data query, through a scheduled job. See "Generating BI Publisher Invoice Documents in BI Publisher 11g".
Calls the SQL bursting data query to pull the invoice data from the BRM database. The SQL bursting data query pulls the invoice data in XML format from the BRM database and returns the XML data necessary to generate invoice documents by the pin_inv_doc_gen utility.
Generates the invoice in the preferred format and sends it using the configured information. The delivery information is stored in the /payinfo/invoice object of the account/billinfo object.
Stores the invoice document in the BI Publisher database. The utility stores the generated invoice documents in the BI Publisher Scheduler database. See "Storing the Invoice Document".
When the BRM-BI Publisher integration is enabled, BRM automatically associates bill units with a BI Publisher report name and invoice template name during the account creation process. When an account is created, BRM performs the following for each bill unit (/billinfo object) in the account:
Determines the BI Publisher invoice report and template that the bill unit qualifies for by reading the /config/business_profile object. This object defines your invoice types, the criteria a bill unit must meet for the invoice type, and the BI Publisher invoice report and template names associated with the invoice type. For example, a /config/business_profile might define the following in Table 3-1 for a regular bill:
Table 3-1 Business Profile Example
Invoice Type | Criteria | BI Publisher invoice Report and Template Names |
---|---|---|
Monthly billing |
The /billinfo object's PIN_FLD_WHEN field is set to 1. |
monthly_invoice_report and monthly_invoice |
Quarterly billing |
The /billinfo object's PIN_FLD_WHEN field is set to 3. |
quarter_invoice_report and quarter_invoice |
Creates an /associated_bus_profile object for the bill unit. This object stores the following information associated with invoicing for the bill unit as an element in a template array called PIN_FLD_TEMPLATE_ARRAY.
PIN_FLD_TEMPLATE_NAME. The Template name configured in the pair key Template_Name of the corresponding invoicing /config/business_profile object.
PIN_FLD_REPORT_NAME. The Report name configured in the pair key Report_Name of the corresponding invoicing /config/business_profile object.
PIN_INV_TYPE. The type of invoice stored as PIN_INV_TYPE_REGULAR or PIN_INV_TYPE_CORRECTIVE.
Associates the bill unit with its /associated_bus_profile object by populating the /billinfo object's PIN_FLD_ASSOC_BUS_PROFILE_OBJ_LIST field.
BRM creates an /associated_bus_profile object for each bill unit in an account by calling the PCM_OP_CUST_CREATE_ASSOCIATED_BUS_PROFILE opcode. This opcode is called internally by the PCM_OP_CUST_CREATE_BILLINFO opcode during the account creation process.
Whenever invoice business profiles are modified in the /config/business_profile object, use the PCM_OP_CUST_SET_ASSOCIATED_BUS_PROFILE opcode to update all related /associated_bus_profile objects.
The BRM-BI Publisher invoicing integration package contains:
The SQL bursting query.
BI Publisher reports for generating invoice documents for consumer and corporate accounts. See "About BI Publisher Reports for Invoice Document Generation".
Sample layout templates in rich text format (RTF) for consumer and corporate accounts. See "About Invoice Layout Templates for Invoice Document Generation".
You can use the following capabilities of BI Publisher to design invoice documents:
Customizing invoice format: You can format the sample invoice layout templates or create new invoice templates. See "Customizing Invoice Layout Templates".
Enriching invoice data: You can enrich the data that is displayed in the invoice by getting data from various sources. See "Enriching Invoice Data".
The BRM-BI Publisher invoicing integration package contains six layout templates in RTF for the consumer and corporate accounts which can be customized.
If the BRM-BI Publisher integration framework is enabled, when you create an account, the bill units (/billinfo objects) of the account are associated with an /associated_bus_profile object. The /associated_bus_profile object contains the report and template names that are used for generating invoice documents for this account on the BI Publisher server. See "Specifying BI Publisher Invoice Report and Template Names in BRM".
BRM provides the following default templates for regular invoices:
BRM_Corporate_Invoice.rtf
BRM_Consumer_Invoice.rtf
BRM provides the following default Replacement Invoice templates:
BRM_Corporate_Replacement_Invoice.rtf
BRM_Consumer_Replacement_Invoice.rtf
When you use the default template for a replacement invoice, the resulting document contains all the line items and information from the previous bill or invoice. In addition, it lists the previous bill number, the previous bill date, the previous bill totals and the corrections totals. The item summary section lists the previous totals, the correction totals and the current totals for each item. The events details section lists the previous total, correction amount and current total for each event.
BRM provides the following default Invoice Correction Letter templates:
BRM_Corporate_Correction_Invoice.rtf
BRM_Consumer_Correction_Invoice.rtf
The invoice correction letter is in a letter format. When you use the default template for an invoice correction letter, the resulting document contains a brief explanation of the correction, a bill summary section and the details of only the corrections made to the previous bill or invoice. The invoice correction letter displays the previous bill number, the previous bill date, the previous bill total, the total correction amount and the current total.
The BRM-BI Publisher invoicing integration package contains two reports for consumer and corporate accounts. Details of these reports are:
The data source of the sample BI Publisher reports is the SQL bursting query.
Each report has a layout template in RTF associated with it that decides the look and feel of the invoice document.
The output formats supported for the invoice document are PDF and HTML. The default output format specified in the reports is PDF.
Note:
For the invoice documents to support data from various data sources, you need to configure additional data sources in the sample reports and customize the sample layout template.To use the BRM-BI Publisher integration framework to generate invoice documents:
Install BI Publisher. To install BI Publisher, follow the instructions in the BI Publisher documentation.
In BRM, specify the BI Publisher invoice report and layout templates names. See "Specifying BI Publisher Invoice Report and Template Names in BRM".
In BRM, specify the events that you want included in your invoices by editing the events.file file and then loading it into the BRM database with the pin_load_invoice_events utility. See "Including Payment, A/R, and Taxation Details in Invoices".
Install the BRM-BI Publisher invoicing integration package on the BI Publisher server. See "Installing BRM-BI Publisher Invoicing Integration Package".
Configure the pin_inv_doc_gen utility. See "Configuring the pin_inv_doc_gen Utility".
Configure data source and data model.
Configure for multischema. Store and modify copies of the report in each schema.
When the BRM-BI Publisher integration is enabled, BRM automatically performs the following during the bill unit (/billinfo object) creation process:
Reads the invoicing configuration business profile (/config/business_profile object) to determine which BI Publisher invoice report and layout template the bill unit should be associated with.
Creates an /associated_bus_profile object for the bill unit. This object specifies the names of the BI Publisher invoice report and layout template to use.
Links the bill unit to the /associated_bus_profile object it created.
To create an invoicing configuration business profile:
Enable the BRM-BI Publisher integration. See "Enabling the BRM-BI Publisher Integration".
Specify the requirements for a bill unit to be associated with an invoicing configuration business profile by creating a validation template. See "Creating Bill Unit Validation Templates for Invoices".
Specify the validation template, invoice report, and layout template to be associated with the bill unit. See "Specifying the BI Publisher Report and Template".
Load the business profile information into the BRM database by using the load_pin_business_profile utility. See "Loading Invoicing Business Profiles into the Database".
If your system includes accounts that were created before you integrated BRM invoicing with BI Publisher, associate your existing /billinfo objects with /associated_bus_profile objects. See "Associating Pre-Existing Bill Units with Business Profiles".
By default, BRM-BI Integration is disabled in BRM. You can enable this feature by modifying a field in the invoicing instance of the /config/business_params object.
You modify the /config/business_params object by using the pin_bus_params utility. For information on this utility, see "pin_bus_params" in BRM Developer's Guide.
To enable the BRM-BI Publisher integration for invoices:
Go to the BRM_Home/sys/data/config directory, where BRM_Home is the directory in which you installed BRM components.
Run the following command, which creates an editable XML file from the invoicing instance of the /config/business_params object:
pin_bus_params -r BusParamsInvoicing bus_params_Invoicing.xml
This command creates the XML file named bus_params_Invoicing.xml.out in your working directory. To place this file in a different directory, specify the path as part of the file name.
Open the bus_params_Invoicing.xml.out file.
Search for the following line:
<EnableInvoicingIntegration>disabled</EnableInvoicingIntegration>
Change disabled to enabled.
Save the file as bus_params_Invoicing.xml.
Go to the BRM_Home/sys/data/config directory, which includes support files used by the pin_bus_params utility.
Run the following command, which loads this change into the appropriate /config/business_params object:
pin_bus_params PathToWorkingDirectory/bus_params_Invoicing.xml
where PathToWorkingDirectory is the directory in which bus_params_Invoicing.xml resides.
Caution:
BRM uses the XML in this file to overwrite the existing invoicing instance of the /config/business_params object. If you delete or modify any other parameters in the file, these changes affect the associated aspects of the BRM invoicing configuration.Note:
To run this command from a different directory, see "pin_bus_params" in BRM Developer's Guide.Read the object with the testnap utility or Object Browser to verify that all fields are correct.
See "Using testnap" in BRM Developer's Guide for general instructions on using the testnap utility. See "Reading Objects by Using Object Browser" in BRM Developer's Guide for information on how to use Object Browser.
Stop and restart the Connection Manager (CM). For more information, see "Starting and Stopping the BRM System" in BRM System Administrator's Guide.
(Multischema systems only) Run the pin_multidb script with the -R CONFIG parameter. For more information, see "pin_multidb" in BRM System Administrator's Guide.
You specify the criteria a bill unit must meet to be associated with an invoicing configuration business profile by creating a bill unit validation template (/config/template/billinfo). For more information about validation templates, see "About Validation Templates" in BRM Managing Customers.
To create a bill unit validation template for invoices, edit the BRM_Home/sys/data/config/pin_business_profile.xml file. The file specifies the following:
The valid business profiles for each invoice type
The criteria a bill unit must meet to qualify for each business profile
The templateID name.
Note:
Each type of invoice can have multiple business profiles, but they must all point to the same TemplateID name.The default pin_business_profile.xml file includes entries for the consumer and corporate invoice documents. You can edit these entries or add entries for any other type of invoice document you want to generate.
For each type of invoice document you want to generate, edit the following entries:
Set the TemplateId name entry to InvoiceIntegration and the type entry to /billinfo, as shown below:
<TemplateId name="InvoiceIntegration" type="/billinfo"/>
Specify the criteria a bill unit must meet to be associated with an invoicing business profile in the Iscript section. Enter your conditions in the if and else if blocks and the name of the business profile in PIN_FLD_NAME.
Important:
The order in which criteria is written in the if and else if blocks defines the order in which a bill unit is evaluated against the criteria.If a particular condition is met in the if and else if blocks, the business profile name associated with the condition is set with the OUT label as one of the Iscript return values.
For example:
<Iscript> Long parentFlags = flistGetLong(IN,PIN_FLD_PARENT_FLAGS); if(parentFlags == 0) { flistSetEnum(OUT, PIN_FLD_RESULT, 1); flistSetStr(OUT, PIN_FLD_NAME, "ConsumerInvoice"); } else if(parentFlags > 0) { flistSetEnum(OUT, PIN_FLD_RESULT, 1); flistSetStr(OUT, PIN_FLD_NAME, "CorporateInvoice"); } </Iscript>
You specify the BI Publisher invoice reports and layout template names by editing the business profile configuration file (pin_business_profile.xml). The default XML file contains entries for generating a consumer invoice document. You can modify these entries and add entries for any other type of invoice document that you want to generate. For example, you can add entries to generate an invoice document for Internet customers or wireless telephony customers.
To specify the BI Publisher invoice report and layout template to use for generating an invoice document, edit the following entries in the BRM_Home/sys/data/config/pin_business_profile.xml file. Add a set of entries for each type of invoice document you want to generate:
Specify the name of the business profile in the BusinessProfile name entry. The name must match the PIN_FLD_NAME entry from the Iscript element.
Important:
The type attribute entry for the BusinessProfile tags must be set to Invoice.For example:
<BusinessProfile name="ConsumerInvoice" type="Invoice">
Make sure TemplateId name is set to InvoiceIntegration and the type is set to /billinfo. All invoice business profiles must use these values.
<TemplateId name="InvoiceIntegration" type="/billinfo"/>
Specify the name of the BI Publisher XDO report to use for generating invoice documents in the Report_Name value entry. For example:
NameValue key="Report_Name" value="Bursting Invoice Report"
Specify the name of the BI Publisher XDO report to use and the name of the layout template to use for each type of invoice, the regular invoice, the Replacement Invoice and the Invoice Correction Letter.
The following is a sample of the BusinessProfile tag entries for the default Corporate Invoice type in the pin_business_profile.xml file:
<BusinessProfile name="CorporateInvoice" type="Invoice"> <Desc> Description of the business profile </Desc> <!-- List templates --> <TemplateId name="InvoiceIntegration" type="/billinfo" /> <!-- List key values --> <NameValue key="Report_Name" value="Bursting Invoice Report"/> <NameValue key="Template_Name" value=" BRM_Corporate_Invoice"/> <NameValue key="Replacement_Report_Name" value="Bursting Invoice Report"/> <NameValue key="Replacement_Template_Name" value=" BRM_Corporate_Replacement_Invoice"/> <NameValue key="Correction_Report_Name" value="Bursting Invoice Report"/> <NameValue key="Correction_Template_Name" value=" BRM__Corporate_Correction_Invoice"/> </BusinessProfile>
After editing the pin_business_profile.xml file, use the load_pin_business_profile utility to load the contents of the file into /config/business_profile objects in the BRM database. For more information, see "Managing Business Profiles" in BRM Managing Customers.
All /billinfo objects that were created prior to your BRM-BI Publisher integration will not be linked with an /associated_business_profile object, which prevents you from creating BI Publisher invoices for those /billinfo objects. For example, this occurs if you upgraded to BRM 7.5 from a prior release.
To generate BI Publisher invoice documents for /billinfo objects created prior to the BRM-BI Publisher integration, you must run the pin_upd_assoc_bus_profile utility.
The pin_upd_assoc_bus_profile utility is a standalone multithreaded application (MTA) that performs the following operations:
Searches for /billinfo objects in the BRM database that have an empty PIN_FLD_ASSOC_BUS_PROFILE_OBJ_LIST field.
Creates an /associated_bus_profile object for each /billinfo object with an empty field.
Links the /billinfo object with its /associated_bus_profile object by populating the /billinfo object's PIN_FLD_ASSOC_BUS_PROFILE_OBJ_LIST field.
To associate pre-existing bill units with business profiles:
Make sure that the BRM-BI Publisher integration is enabled. See "Enabling the BRM-BI Publisher Integration".
Note:
The utility fails if you attempt to run it when the BRM-BI Publisher integration is disabled.(Optional) If you want to associate only a subset of your pre-existing bill units with business profiles, create a text file that specifies the /billinfo objects to update. The file must use the following format:
0 PIN_FLD_RESULTS ARRAY [0] allocated 20, used 1 1 PIN_FLD_POID POID [0] 0.0.0.1 /billinfo 37395 1 0 PIN_FLD_RESULTS ARRAY [1] allocated 20, used 1 1 PIN_FLD_POID POID [0] 0.0.0.1 /billinfo 37488 3
Go to the BRM_Home/apps/pin_billd directory.
Note:
pin_upd_assoc_bus_profile requires a configuration file in the directory from which you run the utility. The configuration file requires the standard CM connection and MTA entries. See "Configuring Your Multithreaded Application" in BRM Developer's Guide.Enter the following command:
pin_upd_assoc_bus_profile [-file filename]
where filename specifies the name and location of the file that lists the /billinfo objects to update.
If you run the utility without any parameters, the utility searches all /billinfo objects in the BRM database. If you run the utility with the -file parameter, the utility searches only those /billinfo objects specified in the file.
If you are using BRM-BI Publisher integration framework to generate invoice documents, at the time of customer account creation, the /associated_bus_profile object is created. See "Enabling the BRM-BI Publisher Integration". The /associated_bus_profile object stores the invoicing business profile information for a /billinfo object. The /associated_bus_profile object contains the layout template name in the PIN_FLD_TEMPLATE _NAME field and the report name in the PIN_FLD_REPORT_NAME field.
If the BRM-BI Publisher invoicing integration is enabled, during customer account creation, internally the PCM_OP_CUST_CREATE_BILLINFO opcode calls the PCM_OP_CUST_CREATE_ASSOCIATED_BUS_PROFILE opcode to create one /associated_bus_profile object for each bill unit in the account.
In the BRM_Invoices reports directory (BIP_Home/user_projects/domains/bifoundation_domain/config/bipublisher/repository/Reports/BRM_Invoices/0.0.0n), the ViewInvoiceReport report is generated to view individually each invoice on the BI Publisher console.
Configure ViewInvoiceReport to point to the data source by editing the data model, configuring the data source, saving changes, and restarting BI Publisher. Enter the invoice POID for the input parameter and click Submit to see the report.
View the invoice on the console, By choosing the applicable template, such as Consumer Invoice, Corporate Invoice, Consumer Correction Invoice, Corporate Correction Invoice, Consumer Replacement Invoice, or Corporate Replacement Invoice.
By default, displaying contact details, account and bill numbers when using the Web is disabled in BI Publisher.
To enable the display of customer details on the Web:
Go to the BIP_Home/user_projects/domains/bifoundation_domain/config/bipublisher/repository/Reports/BRM_Invoices/schema_number/schema_number.xdm directory.
Open the _datamodel.xdm file in a text editor.
Search for the following entry:
<property name="include_rowsettag" value="false"/>
Change false to true.
Save and close the file.
To create /config/business_profile objects for accounts in a branded environment:
In BRM_Home/sys/data/config directory, create a new directory for the brand.
For example, BrandA.
Copy pin.conf, business_configuration.xsd, and pin_business_profile.xml files from BRM_Home/sys/data/config to BRM_Home/sys/data/config/BrandA.
Open pin.conf and add the following entries:
nap login_name brandloginname nap login_pw brandpassword
where brandloginname is the login name for the brand and brandpassword is the password for the brand.
Save and close the file.
Open pin_business_profile.xml and replace the default values with the new values listed in Table 3-2:
Table 3-2 Default Values in pin_business_profile.xml File
Entry | Default Value | New Value |
---|---|---|
BusinessProfile name |
ConsumerInvoice |
ConsumerInvoice_BrandA |
TemplateId name |
InvoiceIntegration |
InvoiceIntegration_BrandA |
NameValue value |
BRM_Bursting_Invoice_Report |
BRM_Bursting_Invoice_Report_BrandA |
NameValue value |
BRM_Consumer_Invoice |
BRM_Consumer_Invoice_BrandA |
BusinessProfile name |
CorporateInvoice |
CorporateInvoice_BrandA |
TemplateId name |
InvoiceIntegration |
InvoiceIntegration_BrandA |
NameValue value |
BRM_Bursting_Invoice_Report |
BRM_Bursting_Invoice_Report_BrandA |
NameValue value |
BRM_Corporate_Invoice |
BRM_Corporate_Invoice_BrandA |
Template name |
InvoiceIntegration |
InvoiceIntegration_BrandA |
flistSetStr PIN_FLD_NAME |
ConsumerInvoice |
ConsumerInvoice_BrandA |
flistSetStr PIN_FLD_NAME |
CorporateInvoice |
CorporateInvoice_BrandA |
Save and close the file.
Go to the BRM_Home/sys/data/config/BrandA directory and enter the following command:
load_pin_business_profile pin_business_profile.xml
Read the object with the testnap utility or Object Browser to verify that all fields are correct.
See "Using testnap" in BRM Developer's Guide for general instructions on using the testnap utility. See "Reading Objects by Using Object Browser" in BRM Developer's Guide for information on how to use Object Browser.
In a Web browser, enter the following URL:
http://BI_Publisher_host:port/xmlpserver/
where:
BI_Publisher_host is the host name on which the BI Publisher server is installed.
port is the console port. The default port is 9704.
Log in to BI Publisher.
Create a new RTF template to associate it with the layout template specific for the accounts in a brand. See the discussion of creating an RTF template using the Template Builder for Word in the BI Publisher documentation.
Note:
The layout template name must be same as the template name specified in the updated pin_business_profile.xml file. For example, if you specify the layout template as BRM_Consumer_Invoice_BrandA, you must name the RTF template file as BRM_Consumer_Invoice_BrandA.rtf.In Shared Folders, click BRM_Invoices.
Click Create a new report link.
Enter the report name as BRM_Bursting_Invoice_Report_BrandA.
Note:
The report name must be same as the report name specified in the updated pin_business_profile.xml file.For the BRM_Bursting_Invoice_Report_BrandA report, click Edit. To specify report properties, see the discussion of creating the report entry and specify general properties in the BI Publisher documentation.
Note:
The configuration of the BRM_Bursting_Invoice_Report_BrandA report must be same as the sample BRM_Bursting_Invoice_Report report in the BRM-BI Publisher invoicing integration package. For example, the BRM_Bursting_Invoice_Report_BrandA report must have the same WSDL URL as given in the BRM_Bursting_Invoice_Report report.
The layout template name must be BRM_Consumer_Invoice_BrandA.
Click Save.
This section describes how to install the BRM-BI Publisher invoicing integration package. This package is set up on the BI Publisher server.
To install the BRM-BI Publisher invoicing integration package:
Ensure that the pre-installation requirements are met. See "Pre-Installation Requirements".
Manually remove previously installed versions of the BRM-BI Publisher 11g invoicing integration layout template files. See "Removing Older BRM-BI Publisher 11g Invoicing Integration Layout Template Files".
Run the BRM-BI Publisher invoicing integration package. See "Enabling the BRM-BI Publisher Invoicing Integration Package Application".
Important:
Install the BRM-BI Publisher invoicing integration package on the system on which BRM Reports is installed. If the BRM-BI Publisher invoicing integration package and BRM Reports are not installed on the same system, you cannot view an invoice in BI Publisher 11g.The following must be completed before you run the BRM-BI Publisher invoicing integration package application:
BI Publisher 11g is installed.
BI Publisher home environment variable has been successfully set.
BRM 7.5 Patch Set 4 is installed. The patch set contains the pin_inv_doc_gen utility updates that are required for BI Publisher 11g integration.
For more information on these requirements, see BRM Reports.
When you install the BRM-BI Publisher invoicing integration package, the older versions of BRM-BI Publisher 11g invoicing integration layout template files are not automatically overwritten. Before installing the BRM-BI Publisher invoicing integration package, you must manually remove all previous versions of BRM-BI Publisher 11g invoicing integration layout template files.
To remove older BRM-BI Publisher 11g invoicing integration layout template files:
Go to BIP_Home/user_projects/domains/bifoundation_domain/config/bipublisher/repository/Reports/BRM_Invoices/SchemaNumber/BRM_Bursting_Invoice_Report.xdo/.
where SchemaNumber is the directory for the BRM schema number to process BRM invoices; for example, 0.0.0.1.
Manually remove the following layout template files:
BRM_Consumer_Correction_Invoice.rtf
BRM_Consumer_Invoice.rtf
BRM_Consumer_Replacement_Invoice.rtf
BRM_Corporate_Correction_Invoice.rtf
BRM_Corporate_Invoice.rtf
BRM_Corporate_Replacement_Invoice.rtf
Go to BIP_Home/user_projects/domains/bifoundation_domain/config/bipublisher/repository/Reports/BRM_Invoices/SchemaNumber/ViewInvoiceReport.xdo/.
Manually remove the following layout template files:
BRM_Consumer_Correction_Invoice_en_US.rtf
BRM_Consumer_Invoice_en_US.rtf
BRM_Consumer_Replacement_Invoice_en_US.rtf
BRM_Corporate_Correction_Invoice_en_US.rtf
BRM_Corporate_Invoice_en_US.rtf
BRM_Corporate_Replacement_Invoice_en_US.rtf
To enable the BRM-BI Publisher invoicing integration package application:
On the system on which BRM Reports is installed, download the BRM-BI Publisher invoicing integration package 7.5PS4_BRM_BIP_Invoicing_Integration_platform_32_opt.bin file to a temporary directory (temp_dir).
Where platform is the platform on which BI Publisher is installed. For example, linux.
Go to the directory where you installed the Third-Party package and source the source.me file.
Caution:
You must source the source.me file to proceed with installation.If you fail to do so, ”suitable JVM not found” and other error messages appear.
For Bash shell:
source source.me.sh
For C shell:
source source.me.csh
Go to the temp_dir and enter the following command.
7.5PS4_BRM_BIP_Invoicing_Integration_platform_32_opt.bin -console
Note:
The search and verify step of the installation is optional. If the installation hangs, or appears to be taking too long during the search and verify step, you may interrupt the installer without impacting your installation.If you are using a custom BI Publisher server setup, do the following:
Copy the BIP_Home/user_projects/domains/bifoundation_domain/servers/AdminServer/tmp/WebLogic_user/bipublisher_11.1.1/6uc731/lib/BIPExtension.jar file to your domain lib directory.
Stop and restart the Oracle WebLogic Server, which adds the BIPExtension.jar file to the Oracle WebLogic server classpath and enables the BI Publisher extension library.
If you are using custom paths, copy the BRM-BI Publisher template files from BIP_Home/user_projects/domains/bifoundation_domain/config/bipublisher/repository/Reports to your custom directories.
The BRM-BI Publisher invoicing integration program copies the following files listed in Table 3-3 to your computer during installation:
Table 3-3 BI Publisher-Related Files Placed on Your Computer
File | Install Path | Description |
---|---|---|
docgen.sh |
BRM_Home/apps/pin_inv_doc_gen |
Batch script for setting environment variables and launching the pin_inv_doc_gen utility. |
BRM_Consumer_Correction_Invoice.rtf |
BIP_Home/user_projects/domains/bifoundation_domain/config/bipublisher/repository/Reports/BRM_Invoices/SchemaNumber |
RTF file associated with layout template for consumer corrective invoice letter document. |
BRM_Consumer_Invoice.rtf |
BIP_Home/user_projects/domains/bifoundation_domain/config/bipublisher/repository/Reports/BRM_Invoices/SchemaNumber |
RTF file associated with layout template for consumer invoice document. |
BRM_Consumer_Replacement_Invoice.rtf |
BIP_Home/user_projects/domains/bifoundation_domain/config/bipublisher/repository/Reports/BRM_Invoices/SchemaNumber |
RTF file associated with layout template for consumer replacement invoice document. |
BRM_Corporate_Correction_Invoice.rtf |
BIP_Home/user_projects/domains/bifoundation_domain/config/bipublisher/repository/Reports/BRM_Invoices/SchemaNumber |
RTF file associated with layout template for corporate corrective invoice letter document. |
BRM_Corporate_Invoice.rtf |
BIP_Home/user_projects/domains/bifoundation_domain/config/bipublisher/repository/Reports/BRM_Invoices/SchemaNumber |
RTF file associated with layout template for corporate invoice. |
BRM_Corporate_Replacement_Invoice.rtf |
BIP_Home/user_projects/domains/bifoundation_domain/config/bipublisher/repository/Reports/BRM_Invoices/SchemaNumber |
RTF file associated with layout template for corporate replacement invoice document. |
Create_Xmlp_Invoice_Job.sql |
BRM_Home/apps/pin_inv_doc_gen |
Script for creating XMLP_INVOICE_JOB table in BI Publisher Scheduler database. |
invoice_bursting.plb |
BRM_Home/apps/pin_inv_doc_gen |
Invoice bursting stored procedure. |
Infranet.properties.sample |
BRM_Home/apps/pin_inv_doc_gen |
Infranet.properties.sample file for pin_inv_doc_gen.jar. Contains the configuration entries for the pin_inv_doc_gen utility. Important: Before running the pin_inv_doc_gen utility, save the Infranet.properties.sample file as Infranet.properties. |
InvoiceList.xml |
BRM_Home/apps/pin_inv_doc_gen |
XML file containing the details of accounts and bill units. The InvoiceList.xml file is passed along with –accts_list as an input parameter to pin_inv_doc_gen. |
InvoiceList.xsd |
BRM_Home/apps/pin_inv_doc_gen |
XSD file that validates the InvoiceList.xml. |
pin_inv_doc_gen.jar |
BRM_Home/apps/pin_inv_doc_gen |
JAR file for pin_inv_doc_gen application. |
lib/ library |
BRM_Home/apps/pin_inv_doc_gen/lib |
Directory containing dependent pin_inv_doc_gen libraries. |
To use BI Publisher 10g templates in BI Publisher 11g, make the following BI Publisher 11g modifications listed in Table 3-4:
Table 3-4 BI Publisher 10g Template Modifications for BI Publisher 11g
Modification | BI Publisher 10g | BI Publisher 11g |
---|---|---|
XML Structure |
[data source = web service] <invoice> <POID>0.0.0.1 /bill 151665 3</POID> … </invoice> |
[data source = sql query for bursting] <ROWSET> <INVOICE_POID>159696</INVOICE_POID> <ROW> <OBJ_ID0>159696</OBJ_ID0> <REC_ID>0</REC_ID> <BUFFER_BUF> <invoice> <POID>0.0.0.1 /bill 151665 3</POID> … </invoice> </BUFFER_BUF> </ROW> </ROWSET> |
Referencing fields from the root |
<?xdoxslt:set_variable($_XDOCTX,'currency', /invoice/BILLINFO/CURRENCY)?> |
<?xdoxslt:set_variable($_XDOCTX,'currency',/ROWSET/ROW/BUFFER_BUF/invoice/BILLINFO/CURRENCY)?> |
'Contains' function |
<?if:contains(EVENT_OBJ,'adjustment')='true'?> |
<?if:contains(EVENT_OBJ,'adjustment')?> |
'Doesn't contain' function |
contains(ITEM_OBJ,'writeoff_reversal')!='true' |
not(contains(ITEM_OBJ,'writeoff_reversal')) |
Configuring pin_inv_doc_gen involves:
Configuring the Infranet.properties File for pin_inv_doc_gen with BI Publisher 11g
Creating the XMLP_INVOICE_JOB Table in the Scheduler Database
To configure the Infranet.properties file for pin_inv_doc_gen:
Go to BRM_home/apps/pin_inv_doc_gen.
Save the Infranet.properties.sample file as Infranet.properties.
The Infranet.properties.sample file for the pin_inv_doc_gen utility includes the standard configuration entries. See "Using Configuration Files to Connect and Configure Components" in BRM System Administrator's Guide.
Note:
You must have read-write permissions to access the /invoice object. The BRM system administrator creates this user and grants the required permissions.Open the Infranet.properties file in a text editor, and add your network entries and their values, as listed in Table 3-5.
Table 3-5 Infranet.properties.sample File Entries
Entry | Description |
---|---|
infranet.bip.password |
Specifies the password. Note: The password can be encrypted with Advanced Encryption Standard (AES) for security. |
infranet.bip.username |
Specifies the name of the BI Publisher server. |
infranet.bip.url |
Specifies the URL of BI Publisher in the following format http://BIP_Hostname:Port/xmlpserver Note: Enable HTTPS and the SSL port for secure communication. See "Configuring HTTPS for Secure Communication". |
infranet.burst.threadpool.chunksize |
Specifies the maximum number of BRM invoices per BI Publishing bursting query. |
infranet.burst.threadpool.maxsize |
Specifies the maximum number of concurrent threads in the pool. |
infranet.burst.threadpool.size |
Specifies the number of core threads in the pool. |
infranet.connection |
Specifies the URL to the BRM service. |
infranet.dmsearchargs.size |
Specifies the number of account and bill unit pairs the utility should process at one time. The maximum value is 14. |
infranet.dupinvdir.path |
Specifies the directory path for storing duplicate invoice documents. |
infranet.dupinvoice.watermark.angle |
Specifies the angle of the watermark text.The default value is 45 (angle of text is 45 degrees). See the BI Publisher Java API documentation. |
infranet.dupinvoice.watermark.colorr |
Specifies the red component of the watermark text color. The default value is 0.7f. |
infranet.dupinvoice.watermark.colorg |
Specifies the green component of the watermark text color. The default value is 0.7f. |
infranet.dupinvoice.watermark.colorb |
Specifies the blue component of the watermark text color. The default value is 0.7f. |
infranet.dupinvoice.watermark.coordx |
Specifies the x-coordinate of the start of the watermark text. The default value is 200f (starting coordinate lower left x). |
infranet.dupinvoice.watermark.coordy |
Specifies the y-coordinate of the start of the watermark text. The default value is 250f (starting coordinate lower left y). |
infranet.dupinvoice.watermark.font |
Specifies the font of the watermark text. The default value is Arial. |
infranet.dupinvoice.watermark.fontsize |
Specifies the size of the watermark text. The default value is 48. |
infranet.dupinvoice.watermark.text |
Specifies the text to be displayed as the background watermark in the duplicate invoice. The default value is DUPLICATE. |
infranet.http.notification.server |
Specifies the name of the HTTP server used for notifications. Important: Enable HTTP notifications in BI Publisher. See the BI Publisher documentation for details. |
infranet.http.notification.userName |
Specifies the HTTP server login name. |
infranet.http.notification.password |
Specifies the HTTP server login password. |
infranet.INITIAL_CONTEXT_FACTORY |
Environment property that specifies the initial context factory to use. Note: Do not modify the default value, "weblogic.jndi.WLInitialContextFactory". |
infranet.log.file |
Specifies the log file name for the pin_inv_doc_gen utility. |
infranet.log.level |
Specifies the level of log for the pin_inv_doc_gen utility. |
infranet.login.type |
Specifies the type of login. A type 1 login requires the application to provide a user name and password. A type 0 login is a trusted login that comes through a Connection Manager (CM) proxy. It doesn't require a user name or password. Use 1 or 0. |
infranet.notify.http.when.success |
Specifies whether to receive HTTP notification when a BI Publisher job is successful. By default, HTTP notification is enabled and set to true. To disable HTTP notification, set this entry to false. |
infranet.notify.http.when.warning |
Specifies whether to receive HTTP notification when a BI Publisher job contains warnings. By default, HTTP notification is enabled and set to true. To disable HTTP notification, set this entry to false. |
infranet.polling.sleeptime |
Specifies the sleep time (in milliseconds) between polling calls while waiting for the BI Publisher schedule to complete. The default value is 5000 (5 seconds). |
infranet.PROVIDER_URL |
Environment property that specifies the BI Publisher provider URL in the following format: t3s://BIP_Hostname:Port where BIP_Hostname is where BI Publisher is installed and Port is the port number. Note: Enable HTTPS in BI Publisher by using the t3s (secure) protocol. |
infranet.SECURITY_CREDENTIALS |
Environment property that specifies the credentials of the principal for authenticating the caller to the service. Note: The password can be encrypted with Advanced Encryption Standard (AES) for security. |
infranet.SECURITY_PRINCIPAL |
Environment property that specifies the identity of the principal for authenticating the caller to the service. |
infranet.threadpool.fetchsize |
Specifies the number of records that can be fetched by the thread. The default value is 5. |
infranet.threadpool.maxsize |
Specifies the maximum number of threads. The default value is 5. |
infranet.threadpool.size |
Specifies the number of threads. The default value is 3. |
Save and close the file.
To configure the docgen.sh file:
Open the BRM_Home/apps/pin_inv_doc_gen/docgen.sh file in a text editor.
Edit the default values available in the file to the values specific to your system environment.
Note:
The pin_inv_doc_gen utility runs only in the 32-bit mode. Set the Java executable in the docgen.sh file to the 32-bit Java version.Save and close the file.
To change the command-line parameters for pin_inv_doc_gen utility, you update the docgen.sh file:
Go to the BRM_Home/apps/pin_inv_doc_gen.
Open the docgen.sh file in a text editor.
Search for the following line:
invoicedocgen.pin_inv_doc_gen -status pending
Add or replace the pin_inv_doc_gen command-line parameter with the required parameter. For syntax and parameter usage, see "pin_inv_doc_gen".
Save and close the file.
In the command prompt, navigate to the BRM_Home/apps/pin_inv_doc_gen directory.
Enter the following command which runs the docgen.sh file:
docgen.sh
The pin_inv_doc_gen utility runs with the updated command-line parameter.
To set scheduler configuration, see the discussion of setting scheduler configuration options in the BI Publisher documentation.
To configure the email server in BI Publisher, see the discussion of setting up delivery options in the BI Publisher documentation.
BI Publisher can be configured to support HTTPS for secure communication. In addition to setting up HTTPS in BI Publisher, you also configure the Infranet.properties file and pin_inv_doc_gen utility.
To configure HTTPS for secure communication:
Enable HTTPS in BI Publisher. See the BI Publisher documentation for details.
Open the Infranet.properties file in a text editor.
Set and edit infranet.bip.url to use HTTPS and the SSL port. For example:
infranet.bip.url=https://BIP_Hostname:SSL_port/xmlpserver
Modify infranet.PROVIDER_URL to use T3S and the SSL port. For example:
infranet.PROVIDER_URL=t3s://BIP_Hostname:SSL_port
Launch pin_inv_doc_gen using docgen.sh.
Enter yes for:
Is it a trusted certificate?
The XMLP_INVOICE_JOB table stores the mapping of job ID and the Invoice_POID for which an invoice document is generated.
To create XMLP_INVOICE_JOB table in the Scheduler database:
Go to BRM_Home/apps/pin_inv_doc_gen.
Open the Create_Xmlp_Invoice_Job.sql file.
Search for <tablespacename>.
Replace <tablespacename> with the tablespace name where all the BI Publisher Scheduler tables in the BI Publisher Scheduler database are created.
Save and close the file.
Go to BRM_Home/apps/pin_inv_doc_gen.
At the prompt, enter the following command which open SQL*Plus:
sqlplus user_name/password@Database_Name
where
user_name is the user name for the Scheduler database.
password is the password for the Scheduler database.
Database_Name is the SID of the Scheduler database.
At the SQL prompt, enter the following command:
SQL> @Create_Xmlp_Invoice_Job.sql
Configure the Java Naming and Directory Interface (JNDI) for invoiceXREF to allow the pin_inv_doc_gen utility to connect to the Scheduler database:
Log on to the Oracle WebLogic Console.
Click Services.
Click Data Sources.
Click the Lock and Edit button.
Create a new data source.
Enter JNDI details:
Name=invoiceXREF
JNDI name= jdbc/OracleBRM/invoiceXREF
Click Next.
Enter the Scheduler database details that you used when you installed the XMLP_INVOICE_JOB table.
Click Next.
Select the server on which the BI Publisher software is installed.
Click Finish.
Click Activate Changes.
Stop and restart the BI Publisher server.
To configure the BRM-BI Publisher invoicing integration to support multischema systems, do the following:
Configure the bursting data source and query by loading stored procedures. See "Loading Stored Procedures".
Create new JDBC data sources. See "Creating New JDBC Data Sources".
To load stored procedures, do the following:
Go to BRM_Home/apps/pin_inv_doc_gen.
Enter the following commands:
sqlplus user/password@ORACLE_SID >@invoice_bursting.plb; >quit;
The BRM-BI Publisher invoicing integration packages invoicing reports and the data model for BRM primary schema. To create new JDBC sources to support BRM multischema, you follow these procedures. For more information, refer to the BI Publisher documentation.
To create a data source for each BRM schema:
In a Web browser, enter the following URL:
http://BI_Publisher_host:port/xmlpserver/
where:
BI_Publisher_host is the host name on which the BI Publisher server is installed.
port is the console port. The default port is 9704.
The BI Publisher Enterprise client opens.
On the global header, click Administration.
Under Data Sources, click JDBC Connection.
On the JDBC page, click the Add Data Source button.
Enter the following database connection details for the BRM schema:
In the Data Source Name field, enter the schema number in 0.0.0.n format. For example, 0.0.0.1 for the primary schema or 0.0.0.2 for the secondary schema.
In the Driver Type field, enter Oracle 11g.
In the Database Driver Class field, enter oracle.jdbc.OracleDriver.
In the Connection String field, enter your database connection details. For example:
jdbc:oracle:thin:@
Hostname
:port:SID
where Hostname is the host name for the database that contains the secondary schema.
Click Test Connection, which verifies the connection to BRM schema.
A confirmation message is displayed.
Click Apply, which saves your changes.
Repeat the previous steps for each schema.
To configure invoices for multischema:
In the BRM_Invoices reports directory, copy reports directory 0.0.0.1 to 0.0.0.n.
Rename the data model directory within 0.0.0.n. For example, to set up the data model configuration for 0.0.0.2, do the following:
cd BIP_Home/user_projects/domains/bifoundation_domain/config/bipublisher/repository/Reports/BRM_Invoices cp -r 0.0.0.1 0.0.0.2 cd 0.0.0.2 mv 0.0.0.1.xdm 0.0.0.2.xdm
Edit the data source name specified in "Creating Data Source for Each BRM Schema".
For example, in 0.0.0.2/0.0.0.2.xdm/ _datamodel.xdm, enter the data source name in defaultDataSourceRef and the BRM schema number in CDATA.
Set the URL of the BRM bursting report data model to the secondary schema by modifying dataModel URL in 0.0.0.2/BRM_Bursting_Invoice_Report.xdo/_report.xdo.
To configure pin_inv_doc_gen for multischema changes, edit docgen.sh. For example, you add -schema 0.0.0.n where n is the schema number after the -status pending flag of each schema.
Note:
The -schema 0.0.0.n parameter is optional. If it is not specified, schema information comes from the infranet.connection value in the Infranet.properties configuration file; the default is 0.0.0.1.You use the pin_inv_doc_gen utility to generate invoice documents by integrating the capabilities of BRM and BI Publisher. This utility takes a set of input parameters, generates the invoice document, and stores the instance of the invoice document generated in the BI Publisher Scheduler database. Based on the input parameters you pass to pin_inv_doc_gen, the utility fetches the corresponding invoice POID, BI Publisher report name, and layout template name of an /invoice object.
To configure pin_inv_doc_gen utility with the Infranet.properties file, see "Configuring the Infranet.properties File for pin_inv_doc_gen with BI Publisher 11g".
To run the pin_inv_doc_gen utility:
Configure the docgen.sh file to execute the appropriate pin_inv_doc_gen command. To view the utility's syntax and parameters, see "pin_inv_doc_gen".
Go to the BRM_Home/apps/pin_inv_doc_gen directory.
Enter the following command which runs the docgen.sh file:
docgen.sh
You can generate BI Publisher invoice documents for a list of accounts by using the pin_inv_doc_gen utility -accts_list InvoiceList.xml parameter. The number of data units (accounts and bill units) in the InvoiceList.xml file should be within the permissible range for the Data Manager (DM) to search the /invoice objects from the BRM database.
You can specify the number of account and bill unit pairs the pin_inv_doc_gen utility should process at one time by using the infranet.dmsearchargs.size parameter in the Infranet.properties file. The maximum value is 14. For example, if the InvoiceList.xml file lists 20 account and bill unit pairs and the infranet.dmsearchargs.size value is set to 6, the pin_inv_doc_gen utility processes six account and bill unit pairs at a time.
In the InvoiceList.xml file, if you specify the account and bill unit pair of a corporate account, the invoice document is generated only for the specified account and bill unit. If the specified corporate account has subordinate accounts, the /invoice objects of the subordinate account are not selected for generating the invoice document.
To generate invoice documents for a list of accounts and their bill units:
Create an XML file that lists all of the accounts and bill units for which to create BI Publisher invoices. The XML file must be in the following format:
Note:
You can use the BRM_Home/apps/pin_inv_doc_gen/InvoiceList.xml file as an example.<InvoiceDocGenConfig> <InvoicingList> <Account>101304</Account> <Billinfo>402678</Billinfo> </InvoicingList> <InvoicingList> <Account>405304</Account> <Billinfo>522678</Billinfo> </InvoicingList> <InvoiceDocGenConfig>
Save and close the file.
Go to the BRM_Home/apps/pin_inv_doc_gen directory.
Open the docgen.sh file in a text editor.
Search for the following line:
invoicedocgen.pin_inv_doc_gen -status pending
Replace with:
invoicedocgen.pin_inv_doc_gen -accts_list InvoiceList
where InvoiceList specifies the name and location of the file that includes a list of accounts and bill units for which to create invoice documents. This is the file you created in step 1.
Save and close the file.
Go to the BRM_Home/apps/pin_inv_doc_gen directory.
Enter the following command, which runs the docgen.sh file:
docgen.sh
To generate invoices based on the account type:
Go to the BRM_Home/apps/pin_inv_doc_gen directory.
Open the docgen.sh file in a text editor.
Search for the following line:
invoicedocgen.pin_inv_doc_gen -status pending
Do one of the following:
To generate invoice document for consumer account types, replace with:
invoicedocgen.pin_inv_doc_gen -type consumer
To generate invoice document for corporate account types, replace with:
invoicedocgen.pin_inv_doc_gen -type corporate
Save and close the file.
In the command prompt, navigate to the BRM_Home/apps/pin_inv_doc_gen directory.
Enter the following command which runs the docgen.sh file:
docgen.sh
You can generate duplicate BI Publisher invoice documents for invoices that you have already generated by using the pin_inv_doc_gen utility -status generated parameter.
To generate duplicate BI Publisher invoice documents:
Go to the BRM_Home/apps/pin_inv_doc_gen directory.
Make sure the infranet.dupinvdir.path entry in the utility's Infranet.properties file specifies the directory in which to write the file. See "Configuring the Infranet.properties File for pin_inv_doc_gen with BI Publisher 11g".
Open the docgen.sh file in a text editor.
Search for the following line:
invoicedocgen.pin_inv_doc_gen -status pending
Replace with:
invoicedocgen.pin_inv_doc_gen -status generated
Save and close the file.
In the command prompt, navigate to the BRM_Home/apps/pin_inv_doc_gen directory.
Enter the following command to execute the docgen.sh file:
docgen.sh
Important:
Oracle recommends DocMerger as the utility to generate a duplicate invoice with a DUPLICATE watermark. However, you can use this utility only for PDF documents. Hence, you can generate an invoice document with a DUPLICATE watermark only if the original invoice document was generated in PDF.Regardless of the payment type or delivery method, any invoice in pending status can be printed. Therefore, you can generate invoice documents for non-invoice accounts such as credit cards. BI Publisher generates PDFs for any invoice with a non-invoice payment type and stores them in the BI Publisher database.
To deliver invoice documents for non-invoice accounts, configure the delivery method of the bursting query by adding a delivery type (such as FTP) to the DEL_CHANNEL parameter and by adding the appropriate values to PARAMETER 1 through PARAMETER 8 as described in the Oracle Fusion Middleware Report Designer's Guide for Oracle Business Intelligence Publisher.
Hierarchical groups enable customers to pay other customers bills. Hierarchical accounts form a parent-child relationship, and parent and child accounts are on the same billing cycle. Generating trial and regular invoices with BI Publisher is suited for hierarchical accounts that contain large amounts of bill units due to the following:
Each bill unit is processed within its own transaction, which is later used in the processing cycle.
Each bill unit is not loaded into RAM, which reduces RAM utilization.
Collation is run after the generation of each bill unit within the account's hierarchy. Each bill unit is arranged in a predetermined sequence and by default is rendered in PDF.
Note:
By default, if any bill unit in the hierarchy fails to generate, collation is halted, and an invoice is not produced.You generate a trial invoice to validate billing charges and check for inaccuracies before creating the final bill.
When you run the pin_trial_bill_accts utility to perform trial billing with BI Publisher, the PCM_OP_BILL_MAKE_TRIAL_BILL opcode is called to create the trial invoices and collect revenue assurance data for the trial billing run.
In hierarchical account billing, charges for the subordinate accounts are calculated first and then rolled up to the parent account, as follows:
When the pin_trial_bill_accts utility is run with the -pay_type parameter and the payment method subordinate ID 10007, the PCM_OP_BILL_MAKE_TRIAL_BILL opcode creates a /invoice/trial object for each subordinate account.
When the pin_trial_bill_accts utility is run with the -pay_type parameter and the specified payment method parent ID, the PCM_OP_BILL_MAKE_TRIAL_BILL opcode is called to trial bill each account. The PCM_OP_BILL_MAKE_TRIAL_BILL opcode calculates the parent account bill totals using the subordinate totals from the PCM_OP_BILL_MAKE_BILL opcode and generates parent invoices.
For more information, see "How Trial Billing Works" in BRM Configuring and Running Billing.
The PCM_OP_BILL_MAKE_TRIAL_BILL opcode passes the PIN_FLD_INV_FLAGS field's value to the PCM_OP_INV_MAKE_INVOICE opcode. The PCM_OP_INV_MAKE_INVOICE opcode decides from the value whether to create a subordinate or parent trial invoice:
If the value is PIN_INV_TYPE_SUBORDINATE, a subordinate invoice is created.
If the value is PIN_INV_TYPE_PARENT, a parent invoice is created.
The PCM_OP_INV_MAKE_INVOICE opcode calls the PCM_OP_INV_POL_PREP_INVOICE policy opcode, which prepares invoice information prior to formatting and storing.
The PCM_OP_INV_POL_PREP_INVOICE policy opcode checks the value of the PIN_FLD_FLAGS field. If the value is PIN_INV_TYPE_TRIAL_INVOICE plus PIN_INV_TYPE_SUBORDINATE the PCM_OP_INV_POL_PREP_INVOICE policy opcode adds the PIN_FLD_BILLS array and the PIN_FLD_CHECK_SPLIT_FLAGS field, which are required to generate the trial invoices for the subordinate's parent.
The PIN_FLD_BILLS array contains the following fields:
PIN_FLD_START_T
PIN_FLD_END_T
PIN_FLD_AR_BILLINFO_OBJ
PIN_FLD_DUE
PIN_FLD_ADJUSTED
PIN_FLD_WRITEOFF
PIN_FLD_DISPUTED
PIN_FLD_RECVD
To set up invoicing for hierarchical accounts with BI Publisher:
Ensure that all the preliminary configurations required for billing are completed. See BRM Configuring and Running Billing.
Enable the BI Publisher 11g integration. See "Enabling the BRM-BI Publisher Integration".
Set your system to generate invoice documents using BI Publisher 11g. See "Setting Up Your System to Generate Invoice Documents Using BI Publisher".
Verify that BRM is set up with the required templates to generate hierarchical invoices. See "Specifying the BI Publisher Report and Template" and "Invoice Template for Hierarchical Accounts".
Associate bill units with a BI Publisher 11g invoice and report. See "About Associating Bill Units with a BI Publisher Invoice and Report".
Load the business profile information into the BRM database. See "Loading Invoicing Business Profiles into the Database".
Configure the pin_inv_doc_gen utility. See "Configuring the pin_inv_doc_gen Utility".
Verify that both BI Publisher 11g and BRM are set to generate the final invoice either with or without subordinate bill units. See "Setting Subordinate Account Processing in BI Publisher 11g and BRM for Hierarchical Accounts".
Verify that the BRM_Home/apps/pin_trial_bill/pin.conf file's Threshold entry is commented out. Setting thresholds limits the amount of subordinates that are invoiced, which leads to errors when the parent account is trial billed.
Use the BRM_Corporate_Invoice.rtf template for generating hierarchical account invoices. The BRM_Corporate_Invoice.rtf template includes the following layout and formatting:
Depending on how the hierarchical account tax is calculated, the template adds one of the following labels:
If the tax is calculated at the subordinate level, a Subord Total (Tax Included) label is added on the first page of the invoice.
If the tax is calculated at the parent level, a Subord Total (Tax Not Included) label is added on the first page of the invoice.
To distinguish the trial invoice from the regular invoice a Trial Invoice watermark is added to all the pages after the first page. You can change the watermark text by editing the PIN_FLD_MESSAGE field's value in the PCM_OP_INV_POL_PREP_INVOICE flist.
Important:
When generating invoices for hierarchical accounts both BI Publisher and BRM must be set to process the final invoice either with or without subordinate bill units:If you require an invoice that includes subordinate details, set BRM to process subordinate bill units and BI Publisher to generate a single invoice from a group of invoices.
If you require an invoice that does not include subordinate details, set BRM to not process subordinate bill units and BI Publisher to generate individual invoices.
In BRM and BI Publisher you can choose to include the full subordinate account details in the parent invoice or not to include the full subordinate details.
When you include the full subordinate account details, a separate invoice is generated for each subordinate bill unit and the totals rolled up into the parent's invoice. When the final invoice is generated, each subordinate invoice is collated and merged into the final invoice.
When you do not include the full details for hierarchical accounts, only one parent invoice is created, which contains totals from the subordinate accounts.
To set subordinate account processing in BI Publisher and BRM for hierarchical accounts:
In BRM set the threshold for the maximum amount of subordinate bill units allowed in the parent invoice with the ThresholdSubordsSummary and ThresholdSubordsDetail business parameters.
To generate an invoice for each subordinate account, which are included in the final invoice, set the threshold value higher than the number of subordinate bill units.
To generate an invoice without subordinate account details, set the threshold value less than or equal to the number of subordinate bill units.
For more information on how to set the ThresholdSubordsSummary and ThresholdSubordsDetail business parameters, see "Setting Defaults for Hierarchical Group Invoices".
Note:
A value of 0 disables the threshold parameter. BRM does not create separate bill units for subordinate accounts.
If subordinate details are not captured in the final invoice they are still available in the /invoice object of the subordinate bill info.
In BI Publisher set the BRM_Bursting_Invoice_Report to generate individual invoices or generate a single invoice from a group of invoices, as follows:
Log in to BI Publisher.
From the menu select Catalog and then select BRM-Invoices.
From the BRM-Invoices section, click the data model 0.0.0.1 link.
The 0.0.0.1 page appears.
In the Data Model section, expand the Bursting list and select BRM_Bursting_Invoice_Report_Bursting_Query.
From the Split By list in the BRM_Bursting_Invoice_Report_Bursting_Query section, select one of the following:
To generate a single invoice from a group of invoices, select /ROW_SET/LIST_ROW/ROW/BILL_NO.
To generate individual invoices, select /ROW_SET/LIST_ROW/ROW/OBJ_ID0.
Save the data model 0.0.0.1.
Exit BI Publisher.
By default the BRM report BRM_Bursting_Invoice_Report is set to create the final invoice from a group of invoices for hierarchical accounts. Invoices with the same bill number are generated and then collated and merged into the parent invoice as a single PDF.
To create hierarchical account trial invoices with BI Publisher:
Go to the BRM_Home/apps/pin_trial_bill directory.
Run the following command, which creates trial invoices for subordinate accounts:
pin_trial_bill_accts -pay_type 10007
Run the following command, which creates trial invoices for the parent accounts:
pin_trial_bill_accts -pay_type ID
where ID is the element ID of the payment method for the parent account. For more information on payment method IDs, see "Understanding Payment Methods" in BRM Managing Customers.
Go to the BRM_Home/apps/pin_inv_doc_gen directory.
Enter the following command to execute the docgen.sh file:
docgen.sh
To create hierarchical account invoices with BI Publisher:
Go to the BRM_Home/apps/pin_billd directory.
Run the following command, which creates billing for subordinate accounts:
pin_bill_accts -pay_type 10007
Run the following command, which creates billing for the parent accounts:
pin_bill_accts -pay_type ID
where ID is the element ID of the payment method for the parent account. For more information on payment method IDs, see "Understanding Payment Methods" in BRM Managing Customers.
Go to the BRM_Home/apps/pin_inv_accts directory.
Run the following command, which creates regular invoices for subordinate accounts:
pin_inv_accts -hierarchy
Run the following command, which creates regular invoices for the parent accounts:
pin_inv_accts -pay_type ID
Go to the BRM_Home/apps/pin_inv_doc_gen directory.
Enter the following command to execute the docgen.sh file:
docgen.sh
When an error occurs in trial billing, generation of the bill unit's invoice stops. You must rerun trial billing on the subordinate accounts before rerunning trial billing on the parent account. You must also regenerate trial invoices on the parent accounts that did not contain an error.
To regenerate invoices for hierarchical accounts that failed the last trial billing run:
Resolve the errors.
Go to the BRM_Home/apps/pin_trial_bill directory.
Run the following command, which reruns trial billing on subordinate accounts that contained errors in the last billing run:
pin_trial_bill_accts -pay_type 10007 -retry
Run the following command, which reruns trial billing on the parent accounts that contained an error in the last billing run:
pin_trial_bill_accts -pay_type ID -retry
where ID is the element ID of the payment method for the parent account. For more information on payment method IDs, see "Understanding Payment Methods" in BRM Managing Customers.
Run the following command, which generates trial invoicing on the parent accounts that did not contain an error in the last billing run:
pin_trial_bill_accts -pay_type ID
Go to the BRM_Home/apps/pin_inv_doc_gen directory.
Enter the following command to execute the docgen.sh file:
docgen.sh
To purge trial invoices, run the pin_trial_bill_purge utility.
By default, pin_trial_bill_purge purges invoices with the /invoice/trial POID type. If a custom /invoice subclass exists, invoices with /invoice/custom_subclass/trial POID type are purged.
Caution:
If the /invoice class is partitioned, running the purge utility (partition_utils -o) on /invoice purges both regular invoices and trial invoices.For a description of the syntax and parameters of this utility, see "pin_trial_bill_purge" in BRM Configuring and Running Billing.
For more information about partitioning and purging database tables, see the following in BRM System Administrator's Guide:
BRM offers two options for deferred taxation: taxes can be consolidated into a single item for both the parent and subordinate bill units, or taxes can be calculated for each individual nonpaying child bill unit, which are listed as separate items on the parent bill:
When taxes are consolidated into a single item for both the parent and subordinate bill unit, the total tax is calculated at the parent level for the entire hierarchy using an aggregated total due as its basis. In this instance, the cycle_tax_interval entry in BRM_Home/sys/cm/pin.conf configuration file is set as follows:
-fm_bill cycle_tax_interval billing
Important:
This option is not supported when using trial billing or trial invoicing with BI Publisher for Hierarchical Accounts.When taxes are calculated for each individual nonpaying child bill unit, the total tax is a summation of taxes that were calculated for each bill unit. In this instance the cycle_tax_interval entry in BRM Home/sys/cm/pin.conf configuration file is set as follows:
-fm_bill cycle_tax_interval accounting
Important:
This option is supported when using trial billing or trial invoicing with BI Publisher for Hierarchical Accounts, with the following limitations.The total tax value can contain small rounding errors caused by the system calculating taxes on smaller totals of each bill unit rather than the grand total.
The basis for determining the tax rate is with the individual subordinate's bill unit rather than the parent. To avoid tax rate discrepancies between trial and production runs, all tax-affecting attributes (for example, address information and tax exemptions) for all bill units within the hierarchy must be synchronized to those of the parent bill unit.
This section lists problems you might encounter while trying to generate hierarchical account invoices and provides information on how to fix them.
Problem: Subordinate invoices not generating
Scenario: There is one parent account and two subordinate accounts. The ThresholdSubordsSummary and ThresholdSubordsDetail business parameters are set to 1. The following command is run:
pin_inv_accts -pay_type 10018 -verbose
Solution: To create subordinate invoices the pin_inv_accts utility requires the -hierarchy parameter. Run the following commands:
pin_inv_accts –hierarchy -verbose pin_inv_accts –pay_type 10018 -verbose
Problem: Though invoice generation is successful, the billing amounts for the parent account in the final invoice are incorrect
Scenario: There is one parent account and two subordinate accounts. The ThresholdSubordsSummary and ThresholdSubordsDetail business parameters are set to 1. The following commands are run:
pin_inv_accts -pay_type 10007 -verbose pin_inv_accts -pay_type 10018 -verbose
Solution: The BI Publisher template is configured with a bursting option, which is using the same invoice details twice. Change the BI Publisher template to a non bursting option.
For more information, see "Setting Subordinate Account Processing in BI Publisher 11g and BRM for Hierarchical Accounts".
Problem: Multiple /invoice objects are created instead of one and after running the docgen.sh script the invoice is generated but the amounts are incorrect
Scenario: There is one parent account and two subordinate accounts. The ThresholdSubordsSummary and ThresholdSubordsDetail business parameters are set to 0. The following commands are run:
pin_inv_accts -pay_type 10007 -verbose pin_inv_accts -pay_type 10018 -verbose
Solution: BI Publisher is set to create invoices for subordinate accounts. Whereas the threshold business parameters setting in BRM is set to not create invoices for subordinate accounts. BI Publisher and BRM are in conflict. To solve the conflict, change the BI Publisher template to a non bursting option.
For more information, see "Setting Subordinate Account Processing in BI Publisher 11g and BRM for Hierarchical Accounts".
Problem: Error when running multiple trial invoicing runs for standalone accounts
Scenario: Unable to run multiple instances of trial billing on a standalone account. Invoices are not created for the second run. The following commands are run:
pin_trial_bill_accts -pay_type 10001 -verbose pin_trial_bill_accts -pay_type 10001 -verbose
Solution: As the trial bill number is the same for both trial invoices, BI Publisher is attempting to collate each generated invoice for the final invoice. Include the docgen.sh script after each run to create a final invoice for each instance, as follows:
pin_trial_bill_accts -pay_type 10001 -verbose doc_gen.sh pin_trial_bill_accts -pay_type 10001 -verbose doc_gen.sh
Note:
The trial bill number is used to collate invoices in BI Publisher 11g.You can store invoice documents in the BI Publisher Scheduler database. Oracle recommends configuring a separate database, other than the BRM database, to store invoice documents. See "Setting Scheduler Configuration".
After generating the invoice document, you can email it to the customers. To send the invoice document through email, you must:
Configure the Scheduler database in BI Publisher. See "Setting Scheduler Configuration".
Configure the email server and FTP in BI Publisher. See "Configuring the Email Server".
Configure the delivery option that is retrieved from the /payinfo object. For more information, see BRM Managing Customers.
Important:
To customize the invoice layout, you should have template-designing experience. See the BI Publisher documentation for instructions on how to create and design layout templates.You can customize the invoice documents by:
You can customize the invoice layout template by:
Updating the existing layout template file
Adding a new layout template file
You can update the existing layout template file by using BI Publisher Desktop (Template Builder) and Microsoft Word. For more information about updating the layout template in RTF, see the discussion of creating an RTF template in the BI Publisher documentation.
To add a new layout template:
Create a new layout template in BI Publisher. For information on how to create a layout template, see creating an RTF template in the BI Publisher documentation.
Create a new invoicing /config/business_profile object in BRM to reflect the addition of the new layout template. See "Specifying BI Publisher Invoice Report and Template Names in BRM" and "About Associating Bill Units with a BI Publisher Invoice and Report".
After receiving invoice data from the BRM database, you might want to enrich the invoice data with marketing messages, customer information available in a customer relationship management (CRM) application, and special messages from the accounting department. BI Publisher provides this functionality by retrieving data from additional data sources.
Important:
The sample layout templates that are available with BRM-BI Publisher invoicing integration package display data fetched from only the BRM database. If you want to enrich your invoice document with additional data sources, you need to customize the sample layout templates. See "Customizing Invoice Layout Templates".To enrich BRM invoice data:
The /invoice objects containing data in XML format should be present in the BRM database.
The additional data source must be configured in BI Publisher to enrich the invoice data. See the discussion of setting up data sources in the BI Publisher documentation.
Configure data and bursting queries to fetch data from custom data source.
For detailed instructions on creating or updating BI Publisher reports based on multiple data sources, see the discussion of creating BI Publisher reports based on various data sources in the BI Publisher documentation.
By default, BRM stores invoices in XML format. You can configure BRM to store invoices in flist format by modifying a field in the invoicing instance of the /config/business_params object.
You modify the /config/business_params object by using the pin_bus_params utility. For information on this utility, see "pin_bus_params" in BRM Developer's Guide.
To specify the default format in which to store invoices:
Go to the BRM_Home/sys/data/config directory.
Run the following command, which creates an editable XML file for the invoicing parameter class:
pin_bus_params -r BusParamsInvoicing bus_params_Invoicing.xml
This command creates the XML file named bus_params_Invoicing.xml.out in your working directory. To place this file in a different directory, specify the path as part of the file name.
Open the bus_params_Invoicing.xml.out file.
Search for following line:
<InvoiceStorageType>1</InvoiceStorageType>
Do one of the following:
To specify flist as the output format, set InvoiceStorageType to 0.
To specify XML as the output format, set InvoiceStorageType to 1.
Save this file as bus_params_Invoicing.xml.
Go to the BRM_Home/sys/data/config directory, which includes support files used by the pin_bus_params utility.
Run the following command, which loads this change into the appropriate /config/business_params object:
pin_bus_params PathToWorkingDirectory/bus_params_Invoicing.xml
where PathToWorkingDirectory is the directory in which bus_params_Invoicing.xml resides.
Caution:
BRM uses the XML in this file to overwrite the existing invoicing instance of the /config/business_params object. If you delete or modify any other parameters in the file, these changes affect the associated aspects of the BRM invoicing configuration.Note:
To run this command from a different directory, see "pin_bus_params" in BRM Developer's Guide.Read the object with the testnap utility or Object Browser to verify that all fields are correct.
See "Using testnap" in BRM Developer's Guide for general instructions on using the testnap utility. See "Reading Objects by Using Object Browser" in BRM Developer's Guide for information on how to use Object Browser.
Stop and restart the CM. For more information, see "Starting and Stopping the BRM System" in BRM System Administrator's Guide.
(Multischema systems only) Run the pin_multidb script with the -R CONFIG parameter. For more information, see "pin_multidb" in BRM System Administrator's Guide.