Implement EBS Analytics for Supply Chain

About EBS Analytics for the Supply Chain Lifecycle

Order Management (OM) and Inventory (INV) Analytics provide critical insights into the complex relations between orders and inventory, helping organizations manage the supply chain lifecycle.

Order Management drives the order fulfillment process. Sales orders are the entry points for the sales process, while invoices (found in the Account Receivables module) are the exit points from the fulfillment process.

In order to evaluate the sales performance of individual sales representatives, sales teams, or departments, business analysts need critical visibility into which items are ordered, backlogged, and invoiced.

The EBS Analytics for Supply Chain solution requires the following modules:

  • Order Management (OM)
  • Inventory (INV) Analytics
  • Purchase Order (PO) Analytics
  • Enterprise Asset Management (EAM)
  • Advanced Supply Chain Planning (ASCP)

A module consists of schemas, business schemas, and dashboards.

With the EBS Analytics for Supply Chain dashboards, you can easily analyze the following:

  • Order and Fulfillment effectiveness
  • Customer Performance
  • Employee Performance
  • Inventory Visibility

Here is a diagram of the Order Management data model comprised of various Schemas in Incorta: OM DataModel

Order Management (OM) Configuration

The OM module consists of schemas business schemas, and a dashboard folder of dashboards.

Order Management Schemas

For the tenant, verify the following schemas:

  • EBS_ONT
  • EBS_WSH
  • EBS_AR (RA_CUSTOMER_TRX_ALL, RA_CUSTOMER_TRX_LINES_ALL, RA_SALESREPS_ALL)

  • COMMON schemas such as…

    • EBS_ITEM_COMMON
    • EBS_CAL_COMMON
    • EBS_FND_COMMON
    • EBS_HR_COMMON
    • EBS_FIN_COMMON
    • EBS_PARTY_COMMON
Order Management Business Schemas

For the tenant, verify the following business schemas:

  • Order Management
Order Management Dashboard Folder

For the tenant, verify the following dashboard folder:

  • Order Management Folder
Dashboard Drill-downs

Some dashboards allow users to drill-down to other dashboards. To drill-down to another dashboard, select a column that contains an asterisk (*). For example, the Delivery Performance (With Drills) dashboard has an insight with the Sales Rep Name column that contains a *.

Order Management Validation SQL

You can use the following SQL statement to validate the Incorta output data versus the source data base.

select count(distinct ohead.order_number) order_count,
sum(NVL (OLINE.ORDERED_QUANTITY, 0)) ORDERED_QUANTITY
FROM apps.OE_ORDER_HEADERS_ALL OHEAD,apps.OE_ORDER_LINES_ALL OLINE
WHERE OLINE.HEADER_ID = OHEAD.HEADER_ID AND oline.schedule_ship_
date between '01-Jan-2018' and '31-Mar-2018' and oline.flow_status_
code in ('AWAITING_FULFILLMENT','AWAITING_SHIPPING','AWAITING_
RETURN','BOOKED') and OHEAD.flow_status_code = 'BOOKED' ;

Order Management Row Level Security (RLS) with Runtime Security Filters

For tables in the EBS schemas, you can implement Row Level Security (RLS) using formula expressions for Runtime Security Filters that reference a session variable.

For example, you can

  • ensure that a given Sales Representative (SalesRep) can only view data that directly relates to their accounts or sales territory.
  • allow a specific Sales Manager (SalesMgr) to view only the data for their team.

To implement RLS, you must perform the following:

  • Create and upload a Data File
  • Create and load a Schema
  • Create an Internal Session Variable
  • Create a Runtime Security Filter
Create and upload a Data File

Begin by creating a CSV file that maps individual login names of Incorta users to a specific value, for example, the SALESREP_ID column.

For example, create a CSV file with three columns:

  • INCORTA_LOGIN
  • SALESREP_ID
  • FULL NAME

Here is an example CSV file, df_rls_sales_rep.csv.

LOGIN_NAME,SALESREP_ID,FULL_NAME
johndoe,1056,John Doe
frankw,1057,Frank Williams
barbaray,1058,Barbara Yang

Next, upload the file as a Data File for the given EBS tenant. Here are the steps:

  • In the Navigation bar, select Data.
  • In the Action bar, select + New.
  • In the Add New menu, select Add Data.
  • In the Choose a Data Source dialog, select Upload Data File.
  • In the Upload Data File dialog, drag & drop df_rls_sales_rep.csv.
Create and load a Schema

After creating and uploading the df_rls_sales_rep.csv, the next steps are to create a schema with a File System table, and then to load the schema. Here are the step:

  • In the Navigation bar, select Schema.
  • In the Action bar, select + New.
  • In the Add New menu, select Create Schema.
  • In the Create Schema dialog, enter a schema name, for example, sch_RLS.
  • Select Save.
  • In the Schema Designer, in Start adding table to your schema, select File System.

  • In the Data Source dialog…

    • for Data Source, select LocalFiles.
    • for file, click Select, and in the Add File from Local dialog, select df_security_sales_rep.csv, and then select Add.
    • to save, select Add.
  • In the Table Editor, enter the table name, for example, tbl_RLS.
  • In the Navigation bar, select Done.
  • To load the Schema, in the Schema Designer, in the Navigation bar, select Load → Load now → Full.
Create an Internal Session Variable

Next, create an internal session variable that returns the SALESREP_ID value for the signed in user. Here are the steps:

  • In the Navigation bar, select Schema.
  • In the Action bar, select + New.
  • In the Add New menu, select Session Variable → Internal Variable.

  • In the Internal Variable dialog…

    • for Name, enter ivar_getSalesRepIDforLoginName.
    • for Description, optionally enter a Description.

    • for Query, enter:

      queryDistinct(sch_RLS.tbl_RLS.SALESREP_ID,sch_RLS.tbl_RLS.LOGIN_NAME=$user)
  • Select Add.
  • Optionally test the variable as one of the users in the df_rls_sales_rep.csv file to verify the SALESREP_ID value for the user.
Create a Runtime Security Filter

Having now created the internal session variable, you can now create a Runtime Security Filter for various tables in the EBS schemas.

To create a Runtime Security Filter for a given table, follow these steps:

  • In the Table Editor, in the Runtime Security Filter section, first select Add Security Filter, and then select Formula.
  • To open the Formula Builder, select the Formula text box.

  • In the Formula Builder, enter an expression similar to following:

    or(
inList(EBS_ONT.OE_ORDER_HEADERS_ALL.SALESREP_ID , $ivar_getSalesRepIDforLoginName),
length($ivar_getSalesRepIDforLoginName) = 0
)
  • Select Validate & Save.
  • In the Action bar, select Done.

The example above, when a user has SALESREP_ID value for their login name, the expression applies a filter condition that specifies that the user only can view the rows where there is match between the SALESREP_ID and the return value of $ivar_getSalesRepIDforLoginName. For all other users, the example returns true, meaning all rows are visible.

You can apply a similar Runtime Security Filter to the following tables:

  • EBS_ONT.OE_ORDER_HEADERS_ALL
  • EBS_ONT.OE_ORDER_LINES_ALL
  • EBS_ONT.OrderDetails

Inventory (INV) Analytics

The INV module provides insights into inventory levels and operations.

INV Data Model

Here is a a diagram of the data model:

INV DataModel

INV Configuration

The INV module consists of schemas business schemas, and a dashboard folder of dashboards.

INV Schemas

For the tenant, verify the following schemas:

  • EBS_INV

  • COMMON schemas such as:

    • EBS_ITEM_COMMON
    • EBS_CAL_COMMON
    • EBS_FND_COMMON
    • EBS_HR_COMMON
    • EBS_FIN_COMMON
    • EBS_PARTY_COMMON
INV Business Schemas

For the tenant, verify the following business schemas:

  • InventoryItem
  • InventoryMetrics
  • ItemList
  • MaterialTransactions
INV Dashboard Folder

For the tenant, verify the following dashboard folder:

  • Inventory

INV Configuration steps

A scheduled job runs a daily load of the MTL_ONHAND_QUANTITIES_SN in the *EBS_INV** schema. As defined in the table’s data source, a SQL query returns a snapshot of the source table. You can modify this snapshot loading date, and thereby change the frequency. To modify the SQL statement in the data source, follow these steps:

  • In the Navigation bar, select Schema.
  • In the Schema Manager, select EBS_INV.
  • In the Schema Designer, select the MTL_ONHAND_QUANTITIES_SN table.
  • In the Table Editor, in the summary section, select the EBS SQL data source.
  • In the Data Source dialog, in the Query field, modify the SQL statement with the Edit Query dialog as required.
  • Select Save.
  • In the Navigation bar, select Done.

Purchase Order (PO) Analytics

This module provides data regarding different purchase order types, including Standard Purchase Order, Planned Purchase Order, Blanket Purchase Agreement, and Contract Purchase Agreement. Use the following steps for configuration information:

PO Data Model

PO DataModel

PO Configuration

The PO module consists of schemas business schemas, and a dashboard folder of dashboards.

PO Schemas

For the tenant, verify the following schemas:

  • EBS_PO

  • COMMON schemas such as

    • EBS_ITEM_COMMON
    • EBS_CAL_COMMON
    • EBS_FND_COMMON
    • EBS_HR_COMMON
    • EBS_FIN_COMMON
    • EBS_PARTY_COMMON

PO Business Schemas

For the tenant, verify the following business schemas:

  • PurchaseOrder
  • Receiving Transactions
  • SpendOverview

PO Dashboard Folder

For the tenant, verify the following dashboard folder:

  • Purchasing

Advanced Supply Chain Planning (ASCP)

The ASCP module provides insights into planning the extended supply chain.

ASCP Data Model

ASCP DataModel

ASCP Configuration

The ASCP module consists of schemas business schemas, and a dashboard folder of dashboards.

ASCP Schemas

For the tenant, verify the following schemas:

  • EBS_MSC
  • EBS_MSC_SN
  • EBS_MRP

  • COMMON schemas such as:

    • EBS_ITEM_COMMON
    • EBS_CAL_COMMON
    • EBS_FND_COMMON
    • EBS_HR_COMMON
    • EBS_FIN_COMMON
    • EBS_PARTY_COMMON
ASCP Business Schemas

For the tenant, verify the following business schemas:

  • SupplyDemandPlanning
  • VCPMeasures
  • VCPExceptions
  • Forecast
ASCP Dashboard Folders

For the tenant, verify the following dashboard folders:

  • Purchasing
  • Purchasing->Buyer Dashboards

Enterprise Asset Management (EAM)

The EAM module provides information about operational details regarding the number of planned/unplanned work order, the number of open/urgent work requests, and on-time completion percentage across organizations and Asset Groups.

EAM Data Model

EAM Model

EAM Configuration

The EAM module consists of schemas business schemas, and a dashboard folder of dashboards.

EAM Schemas

For the tenant, verify the following schemas:

  • EBS_WIP

  • COMMON schemas such as

    • EBS_ITEM_COMMON
    • EBS_CAL_COMMON
    • EBS_FND_COMMON
    • EBS_FND
    • EBS_FIN_COMMON
    • EBS_PARTY_COMMON
EAM Business Schemas

For the tenant, verify the following business schemas:

  • EnterpriseAssetManagementWorkRequest
  • EnterpriseAssetManagementWorkOrders
EAM Required Dashboard Folder

For the tenant, verify the following dashboard folder:

  • Enterprise Asset Management
© Incorta, Inc. All Rights Reserved.