Skip to main content

POV String and Query Builder Documentation - Oracle Cloud ERP Modules

Updated

The POV String is a user-friendly language developed by Casabase Software designed to provide a familiar and intuitive way to pull back data intersections (or data “slices”) from a source.

While syntax differs slightly depending on the data source, the POV String enables users to easily construct queries and pull data using their favorite reporting or analytic tool.

This document details the POV String for the following Oracle Cloud ERP sources:

  • Oracle Financials Cloud
  • Oracle Procurement Cloud
  • Oracle Human Capital Management Cloud
  • Oracle Supply Chain Management Cloud
  • Oracle Project Management Portfolio Cloud
  • Oracle Sales and CRM Cloud

Sections

 

Syntax Basics

The POV String for Oracle Cloud ERP sources consists of the following required components, each separated by a dash-semicolon delimiter (-;):

ExoPowerPOV: Must appear at the start of every POV String. The syntax of the ExoPowerPOV section is

ExoPowerPOV-;

module: This is a three-character code that specifies which Oracle Cloud ERP module is being queried. Valid values are. The syntax of the module section is

module=ModuleName-;
  • FIN (Oracle Financials Cloud)
  • PRC (Oracle Procurement Cloud)
  • HCM (Oracle Human Capital Management Cloud)
  • SCM (Oracle Supply Chain Management Cloud)
  • PPM (Oracle Project Management Portfolio Cloud)
  • CRM (Oracle Sales and CRM Cloud)

task: The task is the primary subject area of the Oracle Cloud ERP module that is being queried. Each module has a specific set of task values that can be used. The syntax of the task section is

task=TaskName-;

The list below is what comes standard with each ExoInsight installation. However, additional tasks can be configured as needed.

  •  FIN
    • journalBatches
    • invoices
    • receivablesInvoices
    • expenses
    • jointVentures
    • currencyRates
  • PRC
    • purchaseOrders
    • purchaseRequisitions
    • suppliers
  • HCM
    • emps
    • jobs
    • locations
    • organizations
    • positions
    • salaries
  • SCM
    • inventoryCompletedTransactions
    • inventoryOnhandBalances
    • itemCosts
    • physicalInventories
    • receiptCosts
  • PPM
    • projects
    • enterpriseProjectAndTaskCodes
    • expenditureTypes
    • financialProjectPlans
    • projectAssets
    • projectBudgets
    • projectCommitments
    • projectCosts
    • projectEvents
    • projectFinancialTasks
    • projectForecasts
    • projectProgress
    • taskPerformance
  • CRM
    • accounts
    • mdfBudgets
    • campaigns
    • contacts
    • territoryForecasts
    • goals
    • opportunities
    • products
    • leads
    • customerWorkOrders

Many other tasks can be added to ExoInsight. Please see the configuration guide for steps on adding additional tasks.

filters: Filters are used to limit the data returned from Fusion ERP. One or more filter is required per ExoInsight query. The syntax of the filter section is

filters=FilterCriteria-;

The FilterCriteria section consists of a field name to be filtered and the filter operator. Available filter operators are:

  • =
  • <>
  • LIKE
  • NOT LIKE
  • IN
  • NOT IN
  • IS NULL
  • IS NOT NULL

The filter operators work like many common database filter criteria and can be combined to return the exact data required. Filter operators can be combined with traditional Boolean logic, such as AND and OR.

columns: The column section tells ExoInsight which columns/fields should be included in the query result set. One or more columns is required for most ExoInsight queries (some Values queries do not require a column section). The syntax of the columns section is

columns=ColumnList

ExoInsight has the ability to return data from multiple levels in a single query. Levels refers to the standard grouping of data in each ERP module. For example, in the Financials module Journal Batches would be considered the first (highest) level, followed by Journal Headers at the second level, and Journal Lines would be considered the third level.

The ColumnList section is a comma-separated list of columns. The first level columns can be listed by name. Subsequent levels must be separated by a semi-colon (;) and prefaced by the level name, followed by a colon (:).

For example, to list the JeBatchID and DefaultPeriodName of the Journal Batch level (first level) along with the JournalName and JournalDescription of the Journal Header level (second level), the syntax would be as follows:

columns=JeBatchID,DefaultPeriodName;journalHeaders:JournalName,JournalDescription

The easiest way to construct the column list is to use the ERP Query Builder. Please consult the ERP Query Builder section for more information.

 

An example POV String to return Journal Batches, the associated Journal Headers, and the associated Journal Lines for all Journal Batches with an Accounting Date of September of 2021 is below:

ExoPowerPOV-;
module=FIN-;
task=journalBatches-;
filters=DefaultPeriodName = '09-21'-;
columns=JeBatchId,AccountedPeriodType,DefaultPeriodName,Status,PostedDate;journalHeaders:JournalName,JournalDescription,PeriodName;journalHeaders.journalLines:JeLineNumber,AccountedCr,AccountedDr,Description;

This POV String was created with the ERP Query Builder. Please consult the next section on how to use the ERP Query Builder to automatically create POV Strings like this one.

 

ERP Query Builder

The ERP Query Builder provides an easy user interface to automatically build POV Strings. To access the ERP Query Builder, click the ERP Query Builder section of the ExoInsight homepage:

 

The ERP Query Builder will open in another tab of your web browser. You'll first enter your Oracle Cloud Account username and password, or log in with your Single Sign on credentials. The permissions of the user you log in with will dictate what modules and data you can access within your Fusion ERP instance.

 

 

Once you have successfully logged in, you will see the main ERP Query Builder page. The four main sections can be seen below:

  1. This is where the ERP Query Builder will display the POV String as you create it.
  2. This is where the primary Fusion ERP modules are displayed. Note: This section may look different depending on what modules you have licensed.
  3. This is the section where the filter criteria will be displayed and edited.
  4. This section and below is where you will run the query and see the results.

To begin building a query, expand the section that contains the required data. For the purposes of this example we will be building a query that pulls Journal Batches, Headers, and Lines for a particular Accounting Period.

Since the Journals data is located in the Financials module of Fusion ERP, expand Financials, then expand Tasks:

Finally, expand Journal Batches to expose the queryable fields for Journals. The available fields for Journal Batches are shown.

To create the columns section of the POV String, simply check the box next to the field you'd like to appear in the result set.

To create the filters section of the POV String, right click on the column that you'd like to apply a filter to. The filter will appear in the middle section where you'll be able to select the filter operator and filter criteria.

As you add/remove columns and add/remove/edit the filter criteria, the POV String is dynamically built for you in the top box. To test the query, click the blue Run Query button to see the results.

 

 

The query above is pulling Journal Batches data. If you'd like to see the Journal Header information associated with these batches, scroll down the column list until you see the journalHeaders section. Simply expand the journalHeaders section and start selecting columns that you'd like to appear in the result set. As you do, the POV String will be automatically updated to accommodate these new fields.

To expand down even further to Journal Lines, scroll down to the journalLines section, expand the section, and start selecting fields you'd like to appear. 

 

 

 

 

Related articles

Powered by Zendesk