1. HCM Extracts
  2. Define the BI Publisher Template in HCM Extracts

Define the BI Publisher Template in HCM Extracts

BI Publisher is a set of tools you use to create highly-formatted reports based on data models. With BI Publisher, you can:

  • Author, manage, and deliver documents

  • Create interactive management reports

  • Create highly-formatted, customer-facing documents

  • Create government documents

  • Create electronic funds transfer (EFT) documents

BI Publisher transforms the extracted data from the database and presents that data in a format of your choice.

This example shows you how to set up a BI Publisher template and create a document in the required format, using the extracted data. You can find BI Publisher using the Reports and Analytics link from the navigator. When the Reports and Analytics page is accessible, you can browse through the catalog using the Browse Catalog option.

FAST bank must send an XML file on their employees and departments to a third party and the HR Manager with employee details grouped by departments as a Headcount Report.

The following table summarizes some key decisions:

Decisions to Consider

In this Example

Why do I've to set up BI Publisher?

You set up BI Publisher to format the extracted XML data into the required format and to deliver the report or business document to the appropriate destination.

How does BI Publisher connect to HCM extracts?

You install the Template Builder for Word plug-in.

Do I need a separate license?

No, BI Publisher is included with Core HR.

Tasks

  1. Creating the BI Publisher Report Template

  2. Setting up the Data Model

  3. Uploading the Report

Create the BI Publisher Report Template

  1. On the Extract Definitions page in the Data Exchange work area, navigate to Extract Execution Tree using the Validate button.

  2. Export the XSD file for your extract using the Export XML Schema option.

  3. Install the Template Builder for Word plug-in from the BI Publisher Enterprise Home page.

    This plug-in provides sample documents, demos, templates and Template Viewer. Use these samples to help you understand the concepts and to create templates such as EFT and RTF to view the formatted XML output.

  4. Open MS Word and find the Add-ins tabbed region to view the Oracle BI Publisher option.

  5. Select the Load XML Schema option and load the exported XSD file. You can create a default RTF template using the All Fields option.

    After the XML schema has been loaded, you can arrange the fields and alter the layout, if required.

  6. Save the template.

Set up the Data Model

  1. Click Navigator > Tools > Reports and Analytics.

  2. On the Reports and Analytics page, click Browse Catalog.

  3. On the BI Publisher Home page, click Create > Report to select a data model for the report.

  4. Select Use Data Model to create a report using the existing data model.

  5. Search and browse for the data model from the directory and click Next.

    Note: Use the existing standard globalReportsDataModel data model (available at: /Human Capital Management/Payroll/Data Models/) for all your reports, unless you've a requirement to burst the output. For example, create a new data model for Documents of Record.

  6. Select the Report Editor option and click Finish to complete the data model setup.

  7. Provide a name and save the report in the following folder: /Shared Folders/Custom/Human Capital Management/

Upload the Report

  1. Select the Upload option from the Upload or Generate Layout region and enter the following information:

    Parameters

    Details

    Layout Name

    RTF Layout

    Template File

    Select the file name of the RTF template saved on your local drive.

    Type

    RTF Template

    Locale

    English (United States)

  2. Save the BI report in the following location: /Shared Folders/Custom/Human Capital Management/.

    This BI Publisher report template is now ready for you to select in the Delivery Options section when creating the extract definition.

  3. Verify the permissions for the report (Shared Folders > Human Capital Management > Payroll > Data Models > globalReportsDataModel > More > Permissions) and ensure the report is accessible for the user account (Enterprise Scheduler Job Application Identity for HCM or fusion_apps_hcm_ess_appid) that runs the Extracts process. You can either add full control access to this user or to the BI Consumer Role.

    Note: Before including this report in the HCM Extracts definition, test the report to verify the formatting and delivery configuration functions as designed. You can run the report from BI and provide your Extracts run instance name as input parameter to generate the output for verification of the layout and format defined in your BI report.

Bursting as Documents of Record

You can burst the output of HCM Extracts as a document of record (DoR) for specific person records.

  1. Create a custom data model by creating a copy of globalReportsDataModel from /Shared Folders/Human Capital Management/Payroll/Data Models.

  2. Save the copied data model in the custom folder /Shared Folders/Custom/Human Capital Management.

  3. In Bursting properties, edit the copied data model and update the values for the following attributes:

    • Split By - Contains the X-path of a data element, which has a unique value and has the following pattern: /DATA_DS/G_1/G_2/FILE_FRAGMENT/<ExtractTagName>/<DataGroupTagName>/<RecordTagName>/<RecordTagName>

    • Deliver By - Same as Split By

    • Bursting query - Use the following query:

      SELECT key, TEMPLATE, OUTPUT_FORMAT, 
               OUTPUT_NAME, CALENDAR, 'true' SAVE_OUTPUT,
               DEL_CHANNEL ,
               PARAMETER1,PARAMETER2,PARAMETER3,PARAMETER4 ,
               PARAMETER5, PARAMETER6 ,PARAMETER7 ,PARAMETER8 , 
               PARAMETER9 ,PARAMETE10 ,
               LOCALE,TIMEZONE 
FROM    FUSION.PAY_REP_DELIVERY_DTLS 
where     payroll_action_id=:payrollActionId 
and         ext_delivery_option_id=:deliveryOptionId
      Note: Take extra care when editing the above Split By and Deliver By values, as you can't correct the typed content using backspace. There's also no option to see the typed content while typing, neither is there an option to the paste content. If you misspell, you'll need to clear and start over.

  4. Use the above data model to complete your BI Publisher template and report creation.

  5. If you want the XML output to be split and delivered as separate files, in the delivery options that you define in the Extracts Definitions page for your Extracts, define the Bursting node to match with the Split By value defined above.

  6. Define the following other details for the Documents of Record delivery type:

    Property

    Value

    Key

    Attribute: PersonID

    Document Name

    <Any name>

    Locale

    en-US

    Person Id

    Same path as Split By or Bursting query

    System Document Type

    <Document Type Code> used by DoR
    To disable the bursting feature in the BIP report and deliver a merge output:

    • Select None as the delivery type on the Deliver page when creating a delivery option for the HCM extract.

    • Select Disable in the Bursting field to disable the bursting options associated with the BIP template.

Related Topics