Oracle Integration Cloud (OIC) - Integration Activation

 Activate an Integration

Once you create an integration and the progress indicator shows 100 percent, you can activate that integration to the runtime environment. An integration shows as 100% and is eligible for activation after you have specified the source connection, the target connection, the data mappings, and the tracking fields.

To activate an integration:

Note: 

If you activate a new version of an existing integration, tracking instances or logs of the old version are not deleted. However, related artifacts are deleted and redeployment is performed on the back end. Monitoring data is also removed.

1. In the left navigation pane, click Home > Integrations > Integrations.
2. Go to the row of the integration to activate.
3. Click the  icon to activate the integration.
   The Confirmation dialog is displayed.
4. Select options appropriate to your integration.

   Element	Description
   Contribute integration mappings to Oracle Recommendations	Click to enable the Oracle Recommendations Engine. Uses the collective intelligence to recommend which fields should be mapped while developing an integration. These recommendations are built based on the mappings contributed to Oracle Recommendations Engine anonymously. You can change this on the Recommendations page by selecting Settings > Recommendations in the navigation pane.
   Enable Asserter Recording	Click to capture payloads and record instances for playing back and testing,
   Enable tracing	Click to enable detailed tracing information in the activity stream. When this checkbox is selected, detailed logging information about messages processed by this integration flow at runtime is collected. This can aid in troubleshooting issues. However, detailed tracing may also impact performance. To disable tracing, you must deactivate the integration, then reactivate it without selecting the Enable tracing checkbox. If you do not enable tracing, minimal logging details are created in ics-flow.log to indicate when the activated integration begins and completes execution (regardless of execution success or failure). You can download the logs on the Dashboard page. If you want to enable payload tracing to log input and output payloads to the activity stream, click the Include payload checkbox. When this checkbox is selected, information from the payload is also written to the log files, which can be downloaded and viewed. For example, you see more detailed logging information about payload activity at several points in the integration flow, such as the payload prior to data mapping and the payload after data mapping. This action can present a security risk and also impact the performance of your system. This setting is not recommended for a production environment.

5. Select an activation option. The options available are based on the type of integration you are activating:
   1. Click Activate (if you are activating a nonscheduled integration).
   2. Click Activate and Schedule (if you are activating a scheduled integration).
      The Run integration_name page is displayed. Create a schedule for running this integration, and click Save. 
   A status message is displayed in the banner at the top of the page. For example:
   
   
6. If integration is unsuccessful, an Activation Failed warning icon is displayed in the banner. If you click the icon, a dialog is displayed with the option to download details about the activation failure incident.
   
   
   1. If your integration includes a function that is not completely configured, an error message is displayed in the banner. You must complete configuration of this function before you can activate the integration. Click inside the integration and note the following errors/warnings:

      • An error icon is displayed on the function call action that uses the incomplete function. The Error panel on the right side of the integration canvas provides specific details about the incomplete function.

      • A warning icon is displayed on the mapper that uses the inputs and outputs of this function. After completing function configuration, you must verify the input and output mappings before activating the integration.

   If activation is successful, the status of the integration changes to ACTIVE in the row.
7. Click the  icon to display details about how to run, test, and track instances for this integration.
   For example:

   If you selected to enable tracing, the words TRACE ENABLED are displayed under the icon you clicked to activate the integration. If you click the integration instance on the Track Instances page, the  menu includes an option called View Activity Stream for viewing payload details.

   To access the detailed trace logging information:

   1. In the left navigation pane, click Home > Monitoring > Integrations > Dashboards.

   2. Click Download Diagnostic Logs to download Oracle Integration logs and diagnostics logs.

8. View active integrations by clicking the integration name or selecting View from the  menu. The active integration is displayed with a message saying View Only in the banner at the top.

Note the following details about read-only mode:

• No Save button is displayed.

• There are no Invokes, Actions, or Errors icons.

• You can click through multiple parts of the integration to view configuration details, such viewing the business identifiers under the Tracking link, viewing the source-to-target and target-to-source mappings in the mapper, and viewing the configurations on the pages of the connection wizards, but you cannot modify anything.

Oracle Integration Cloud (OIC) - Integration Patterns

Introduction:

OIC is a PaaS service as available from Oracle used in developing integrations between SaaS application or on-premise application. Using drag and drop capabilities, an integration can easily be defined. Oracle Integration Cloud Service provides native connectivity to Oracle Software as a Service (SaaS) applications.

Integration

    Integration uses connections to implement business process. An integration includes at least a trigger or source connection and invoke or target connection and the field mapping between those two connections.

    OIC offers Six integration patterns which help us to build from simple to complex integrations. When we choose to build an Integration, the starting point is to choose from the available patterns.

1. App Drive Orchestration

The Orchestration patterns allows you to create from simple to complex integration. Create an integration that uses an event or a business object to trigger. For example, create an integration with an Salesforce adapter as a trigger and Oracle Sales Cloud as a invoke. Salesforce sends an Outbound messages which triggers an OIC integration. Use this pattern when you wanted to create complex integration. In Orchestration pattern, we can invoke any available activity as below:

• For-Each activity to loop over the array element
• Switch activity for the multiple routes
• Scope activity to handle exceptions
• Ad-hoc mapping
• Wait activity to wait certain amount of time

2. Scheduled Orchestration

The pattern allows you to develop an integration which can be scheduled to run on pre-defined frequency as well as on ad-hoc basis. While using this pattern, a scheduled activity occurs as a trigger. For example, you add an initial invoke adapter to read a trigger file and a second FTP adapter to download the file for further processing. After designing this integration, you schedule when to run it. Use this pattern when you want to develop an integration which can run on some scheduled frequency.

3. File Transfer

This pattern create an integration to move the file across network. This is again a scheduled type of integration. Use this pattern when you wanted to move files from one FTP location to another.

4. Basic Routing

This help to create a very simple integration where you have single source and single target system with no logic and processing on data. Create an integration with a blank trigger and invoke in which to add your own adapters. You can also create a single routing expression and request and response enrichment’s, as needed. You cannot create multiple routing expressions. Use this pattern when you have single source and single target.

5. Publish to OIC

Oracle Integration Cloud, provide inbuilt messaging queues which you can leverage to publish and subscribe messages.

Create an integration in which you add a trigger adapter to publish messages to Oracle Integration through a predefined Oracle Integration Messaging invoke. No configuration of the invoke subscriber is required.

The publisher and subscribers participating in this integration pattern can be activated and deactivated independently of each other.

6. Subscribe to OIC

This pattern can only be used in conjunction of Public to OIC pattern.

Create an integration in which you add an invoke adapter to subscribe to messages from Oracle Integration through an Oracle Integration Messaging trigger. You are prompted to select the publisher to which to subscribe. You must have already created a publisher to which to subscribe. The publisher does not need to be active, but must already be completely configured.

Any business identifiers defined on fields in the published integration are copied to the subscriber. Any changes made to the published integration’s business identifiers after copying are not reflected in the subscriber. The publisher and subscribers participating in this integration pattern can be activated and deactivated independently of each other.

Oracle Fusion: Development of Custom BI Publisher Report

This blog is for Oracle Fusion Applications Beginners

Steps to develop Custom BI Publisher report in Oracle Fusion Cloud Applications

Step 1 : Creation of Data Model

Navigation : Navigator --> Reports and Analytics

Click on Browse Catalog

Page will be redirected to Business Intelligence Home Page

 

Click on New Icon which is available on let side of BI home Page

In Diagram tab click New Data Set -- SQL Query

Enter details

Name : Can give as per your Naming Standards (E. g SP_JOUR_DM)
Data Source : This can be selected based fusion DB Schema which we would like to access
Note :
ApplicationDB_FSCM ( Financial and Supply Chain Management )
ApplicationDB_HCM ( Human Capital Management )
ApplicationDB_CRM ( Customer Relationship Management )

Type of SQL: This is can based on Type of SQL which using for report

Standard SQL :Standard SQL used for normal SELECT statements interpreted to understand database schema
Procedure Call : query type to call a database procedure
To use REF cursor in Oracle BI Publisher :
Use this query type to issue SQL statements that can include the following:
Cursor statements that return nested results sets
Functions returning REF cursors
To use REF cursor in Oracle BI Publisher

In SQL Query Section we can provide Query If readily available . Otherwise can use Query Builder to Build Query for report

 

Click Ok . Then Save Data Model

Enter Details of Data Model

Click on Data Tab to save sample data ( This sample data is required to created layout)

Step 2 : Creation of Report

Click on Create Report Which is available in BI Home Page

Default Data Model will selected Click On Next

Select Page Options : Portrait
           Layout : Table

Click next

Drag Fields from Data Source --> Click Next

Click on Finish to Save report and View Output

We can report output in Interactive Dashboard

To View or Download output in different format . Click on View report icon which is available in extreme right corner .

Oracle Fusion : Supplier Bank Account REST APIs

(GET) External Payees

The below REST API is used to check whether the external payee exists or not.

Endpoint:

https://ERPCloudInstance/fscmRestApi/resources/latest/paymentsExternalPayees?finder=ExternalPayeeSearch;SupplierSiteIdentifier={SupplierSiteIdentifier},PayeePartyIdentifier={PayeePartyIdentifier},Intent=Supplier

{SupplierSiteIdentifier} represents the Supplier Site Id
{PayeePartyIdentifier} represents the Supplier Party Id

(GET) External Bank Account Details

The below REST API is used to check whether the external bank account exists for the supplier or not.

Endpoint:

1.To fetch all bank accounts of the supplier

https://ERPCloudInstance/fscmRestApi/resources/latest/externalBankAccounts?q=VendorId={VendorId}&finder=SearchExternalBankAccount;Intent=Supplier

{VendorId} represents the Supplier Id

2. To check for particular bank account using the attributes bank name , branch name, account number

https://ERPCloudInstance/fscmRestApi/resources/latest/externalBankAccounts?finder=SearchExternalBankAccount;BankName={BankName},BankBranchName={BankBranchName},BankAccountNumber={BankAccountNumber},Intent=Supplier

In order to check for bank account exits for the supplier, the bank account number masking should be kept as Display all digits in Manage System Security Options

For security reasons the bank number is kept masked , So querying the supplier bank account details w.r.t to bank account number is not possible.

(POST) Create External Bank Account and Bank Account Owners

The below REST API is used to create external bank account for the supplier.

Endpoint:

https://ERPCloudInstance/fscmRestApi/resources/latest/externalBankAccounts

Sample Request Payload:

{
"BankAccountNumber": "123456676765",
"CountryCode": "US",
"BankName": "DUMMY",
"BankNumber": null,
"BankBranchName": "DUMMY_0999",
"BankBranchNumber": null,
"CurrencyCode": "USD",
"AccountType": "SAVINGS",
"AllowInternationalPaymentIndicator": "N",
"Intent": "Supplier",
"PartyId": 23231231132,
"BankAccountName": "DDP_011",
"accountOwners": [
{
"AccountOwnerPartyIdentifier": 23231231132,
"Intent": "Supplier"
}

]}

{AccountOwnerPartyIdentifier} and {PartyId} from the above request payload represents Supplier’s Party Id

(GET) Instrument Assignment

Rest API to get instrument assignments. 

Endpoint:

https://ERPCloudInstance/fscmRestApi/resources/latest/instrumentAssignments?finder=PaymentInstrumentAttributes;Intent=Supplier,PaymentPartyId={PaymentPartyId}

{PaymentPartyId} – External {PayeeId} from the call GET External Payee Rest API.

(POST) Create Instrument Assignment

The below REST API is used to create instrument assignment to the created bank account.
In order to create Instrument Assignment, we need to setup our user as active worker.

Endpoint:

https://ERPCloudInstance/fscmRestApi/resources/latest/instrumentAssignments

Request Payload :

{
"PaymentPartyId": 333333,
"PaymentFlow": "DISBURSEMENTS",
"PaymentInstrumentId":122222223,
"PaymentInstrumentType": "BANKACCOUNT",
"Intent": "Supplier"
}

{PaymentPartyId} - represents external payee id from the REST call GET External Payees (which was derived from corresponding level (supplier/site) the bank is attached to the supplier )

{PaymentInstrumentId} – is the {BankAccountId} from the REST call GET External Bank Accounts.

Oracle Fusion : Query to fetch privileges and roles assigned to the user

Privileges:

/*Query to fetch privileges assigned to the user*/

SELECT DISTINCT u.user_display_name AS UserName,

  rt.role_name ,

  pt.name privilege_name

FROM ASE_PRIVILEGE_B p ,

  ASE_PRIVILEGE_TL pt,

  ASE_PRIV_ROLE_MBR m ,

  ASE_ROLE_B r ,

  ASE_ROLE_TL rt,

  ASE_USER_TL u ,

  ASE_USER_ROLE_MBR rm

WHERE 1=1

AND ( SYSDATE BETWEEN p.EFFECTIVE_START_DATE AND p.EFFECTIVE_END_DATE

OR p.EFFECTIVE_END_DATE IS NULL)

AND ( SYSDATE BETWEEN r.EFFECTIVE_START_DATE AND r.EFFECTIVE_END_DATE

OR r.EFFECTIVE_END_DATE IS NULL)

AND p.PRIVILEGE_ID       = m.PRIVILEGE_ID

AND m.role_id            =r.role_id

AND u.LANGUAGE           = 'US'

AND rm.user_id           = u.user_id

AND rm.role_id           = r.role_id

AND pt.PRIVILEGE_ID      = p.PRIVILEGE_ID

AND pt.LANGUAGE          = 'US'

AND rt.role_id           = r.role_id

AND rt.LANGUAGE          = 'US'

AND ( SYSDATE BETWEEN m.EFFECTIVE_START_DATE AND m.EFFECTIVE_END_DATE

OR m.EFFECTIVE_END_DATE IS NULL)

AND ( SYSDATE BETWEEN rm.EFFECTIVE_START_DATE AND rm.EFFECTIVE_END_DATE

OR rm.effective_end_date IS NULL)

AND u.user_display_name   = :p_user_name

ORDER BY 1,2,3

Roles:

/*Query to fetch roles assigned to the user*/

SELECT pu.user_id,

  pu.username,

  ppnf.full_name,

  prdt.role_id,

  prdt.role_name,

  prd.role_common_name,

  prdt.description,

  TO_CHAR (pur.start_date, 'DD-MON-YYYY') role_start_date,

  TO_CHAR (pur.end_date, 'DD-MON-YYYY') role_end_date,

  prd.abstract_role,

  prd.job_role,

  prd.data_role,

  prd.duty_role,

  prd.active_flag

FROM per_user_roles pur,

  per_users pu,

  per_roles_dn_tl prdt,

  per_roles_dn prd,

  per_person_names_f ppnf

WHERE 1                           = 1

AND pu.user_id                    = pur.user_id

AND prdt.role_id                  = pur.role_id

AND prdt.language                 = USERENV ('lang')

AND prdt.role_id                  = prd.role_id

AND NVL (pu.suspended, 'N')       = 'N'

AND ppnf.person_id(+)             = pu.person_id

AND ppnf.name_type(+)             = 'GLOBAL'

AND pu.active_flag                = 'Y'

AND NVL (pu.start_date, SYSDATE) <= SYSDATE

AND NVL (pu.end_date, sysdate)   >= sysdate

AND pu.username                   = :p_user_name 

Oracle Cloud Supplier Portal View Access - Privileges

The following are the privileges required for a user to have view access of the supplier portal.

1. Access Suppliers Work Area Overview
2. Search Supplier
3. Search Supplier Assessment
4. Search Supplier Qualification
5. View Supplier
6. View Supplier Address
7. View Supplier Assessment
8. View Supplier Attachment
9. View Supplier Business Classification
10. View Supplier Contact
11. View Supplier Contact Change Request
12. View Supplier Products and Services Categories
13. View Supplier Profile Change Request
14. View Supplier Qualification
15. View Supplier Qualification Initiative
16. View Supplier Qualification Question
17. View Supplier Registration Bank Account
18. View Supplier Registration Request
19. View Supplier Site
20. View Supplier Site Assignment
21. View Supplier Site Invoicing Information
22. View Supplier Site Purchasing Information
23. View Supplier Site Receiving Information
24. View Supplier Transaction Tax

Dashboards in Oracle Transactional Business Intelligence for Financial Reporting

Understanding dashboard functions

Dashboards offer custom views of corporate and external information. A dashboard can have one or more pages and will have at least one empty page for editing.When you open a dashboard, the dashboard content appears in pages with tabs to identify each page. This arrangement allows you to perform the following actions:

Alter the dashboard if you have the correct permissions and privileges.

Show different options for working with a dashboard page.

Carry out options with the Dashboard Builder.

Dashboard pages can show anything you can reach or open with a Web browser, such as the following:

Action links and action link menus

Agent alerts

Analysis results

Business Intelligence (BI) Publisher reports

Document links

Embedded content, including Web pages or documents

Folder views in the BI Presentation Catalog

Images

Text

Web site links

Analysis results are the output the BI server gives back that matches the analysis criteria. You can display these results in several ways, including a table or graph, where you can do the following:

Go through and analyze results.

Print the results as a PDF or HTML.

Save the results as a PDF or export them to a spreadsheet.

When exporting or printing, you can control settings, such as the orientation (landscape or portrait). You can also decide whether you want to include charts, images, and formatting. Additionally, you can include a header and footer.

Using the Dashboard Builder

You can use the Dashboard Builder to add new pages or alter existing pages in a dashboard. It also lets you include objects and manage the layout of dashboard pages. The following are objects you can include:

Dashboard objects: These are objects that you only use in a dashboard.

For example, segments to hold content, action links, and embedded content that appear in a frame in the dashboard

Catalog objects: These are items that someone saved in the BI Presentation Catalog.

For example, analyses and prompts for which you have the correct permissions

Embedding an analysis in a dashboard makes it automatically operate and show the most up-to-date results each time you access the dashboard. This gives you access to the latest results. You can also embed analyses you saved with the Dashboard Editor.

Accessing and updating dashboards

The Dashboard Builder lets you create or edit dashboards. When you create a dashboard, you have the option to add content now or later. If you want a dashboard to appear in the global header's Dashboard menu, save the dashboard in the Dashboards subfolder under a shared folder's first-level subfolder. The dashboard will not appear in the Dashboard menu list if you save it elsewhere.

The first time you attempt to save a dashboard within a first-level subfolder, the application will create a folder called Dashboard, and the Location field will automatically change to that folder. This Dashboard folder is required in these first-level subfolders for you to save dashboards in them. If you use a different level subfolder, the system will not create a specific dashboards folder. 

Places you can add or modify content include:

A new dashboard in the Dashboard Builder, which has one blank page and is open for editing.

An empty dashboard that you establish but don’t show and for which you can include content at a later time.

Overview of Oracle Transactional Business Intelligence

 Understanding types of reporting in Oracle Cloud

Oracle Cloud applications provide customers with generational reporting. There are three types of reporting in Cloud:

Financial Reporting Center

Embedded analytics

Oracle Transactional Business Intelligence (OTBI)

The Financial Reporting Center provides multiple reporting methods. Oracle considers it to be a self-service reporting tool. It provides you with the ability to conduct analyses against real-time accounting information. Embedded analytics allow users to view real-time updates of data, making it easier to access and see the impact of that data.

With traditional business intelligence (BI), data is usually historic in nature and looks backwards. In Oracle Cloud applications, BI works in real time. It is both historic and predictive, providing broad insight for improved decision-making.

From a solution perspective, there are three key components that make up BI:

The first is a real-time, self-service reporting and analysis capability. You can pull transactional-level details for ad hoc analyses that can be very granular.

The second is a historical, complex analysis that typically requires a data warehouse to do a complex cycle of analysis, historical trends, and year-over-year comparison.

Lastly, advanced analytics provide domain-specific analyses, leveraging the underlying BI foundation.

The key observation about these three components is that from an end-user perspective, they don’t look different. From a functional architecture perspective, there is integration between BI and Cloud applications.

Exploring OTBI key features

OTBI provides strategic insight to your business, addressing questions about operational responsibilities. The reports it generates come directly from transactional tables and allow you to see alterations happening in real time. OTBI shows transactional analytics in different forms, including the following:

Charts

Columns

Fields

Icons

Inserted rows

Labels

Tables

OTBI runs queries straight from Cloud tables and doesn’t need a data warehouse or a data source. OTBI is primarily an ad hoc reporting layer against the Cloud transactional tables and balances cube; it allows ad hoc analysis with the Oracle Business Intelligence Enterprise Edition (OBIEE) platform. The main strength of this design is its ability to let you create unique reports and carry out ad hoc analyses.

OTBI includes the following interactive features:

Permits users to include graphs and build dashboards as well as establish ad hoc queries.

Uses OBIEE features, such as alerts.

Transfers data and contextualizes Cloud and BI.

Semantic model and extensible features

A semantic model is informational in that it allows the modeling of entities and their relationships. Extensibility is a principle of system design whereby the implementation considers future growth. OTBI supports consistency in the semantic model and in the extensible features. In addition, OTBI includes hierarchies that it transmits to BI from Cloud applications. It also contains a unified definition for key entities, including dates, invoices, and customers. The architecture uses Cloud application ideas (such as flexfield and trees) to extend functionality. OTBI also includes schemas optimized for reporting.

Integration with Cloud applications
OTBI can combine with different Cloud applications. You can navigate straight from Cloud applications with a single sign-on, and you can embed content from Cloud applications within OTBI. Since users' roles apply the same in OTBI as they do in the Cloud user interface, it has the same functional and data security as Cloud applications.

Additional features in OTBI
Oracle continues to add key features and functionalities to Oracle Transactional Business Intelligence for Financials. 

The following list details some additional features for OTBI:

Budgetary Control: The Budgetary Control subject area supports receipt accounting information for reporting.

Cash Management: Legal Entity and Fiscal Period folders are available within the Cash Management subject areas. You can sort the Cash Management key performance indicators (KPI) according to Legal Entity and Fiscal Period. The External Transaction Accounting folder is available within the External Cash Transactions Real Time subject area.

Expenses: Credit card transaction data and expense report approval notes are available within the Expenses subject area.

Expenses: OTBI for Financials provides the capability to drill from an OTBI ad hoc report based on Expenses Transactions OTBI subject area to view Expense Report in Oracle Expenses.

Payables: The Payables subject areas contain the Supplier Site Address folder, which includes the Supplier Site Location Details and Supplier Site Contact Details subfolders. The Payables subject areas have the following additional columns and folders for Invoices:

Disbursements: approval iteration, header batch identifier, supplier alternate name

Holds: approval iteration, header batch identifier, purchase order status, supplier alternate name

The invoice header holds: hold identifier, hold date, hold release date, hold name, hold description, hold reason, held by, released by, hold details, hold release name, hold release description, release reason, invoice hold workflow status, invoice hold receipt number, hold creation date, hold created by, hold last updated date, hold last updated by

The invoice line level holds: line hold identifier, line hold date, line hold release date, line hold name, line hold description, line hold reason, line held by, line released by, line hold details, line hold release name, line hold release description, line release reason, line invoice hold workflow status, line invoice hold receipt number, line hold creation date, line hold created by, line hold last updated date, line hold last updated by

The Invoice Amounts and Invoice Line Amounts folders contain the following columns: line hold count, released line hold count, total number of holds, total number of active holds

Installments: header batch identifier, supplier alternate name

Prepayments: approval iteration, header batch identifier, purchase order status, supplier alternate name

Transactions: purchase order status, approval iteration, header batch identifier

Withholding: supplier alternate name

Revenue Management: The Revenue Management subject areas have the following additional folders and columns for customer contracts:

Customer Contracts Real Time: This subject area includes attributes that support reporting activity when the system automatically renews revenue contracts as well as suspends and reinstates satisfaction events for revenue contracts that are based on periods.

Revision Intent: Manual revenue required flag, add to existing flag, add to existing contract flag, add to contact action code, revision intent type code, latest version date, latest revision intent code columns are included in Promised Details. The Promised Details folder includes the Promised Detail Contract Revision Details subfolder and the Promised Detail Contract Revision Amounts subfolder. Latest version date and latest revision intent code columns are included in Performance Obligations and Customer Contracts. The Performance Obligations folder contains the Devolved Series, Series Details, and Series Amounts subfolders.

Contract and Performance Obligation Reference: Contract Identification Rules Details and Performance Obligation Template folders include the Columns Copy Reference flag and Reference prefix.

Unit Selling Price from source: The Promised detail source document line and the Source document line reference folders include Unit SSP and Net Unit SSP columns.

Clear Residual Account Balances: The Customer Details folder includes these attributes: residual balance adjustment status and exclude from automatic, write-off, last activity date, satisfaction, status, and contract full satisfaction date.

Contact Group Number: The Contract Details folder includes a Contact group number column. The Contract Identification Rules Details folder includes a Use as group number flag column.

Fixed Assets: The Fixed Assets subject areas let you access non-qualified segments and qualified General Ledger segments in the Depreciation Accounting Details folder. To see these fields, you must run the BI Extender.

Receivables: OTBI for Financials provides the capability to drill from an OTBI ad hoc report based on a Receivables OTBI subject area to Customer details (Collections Customer UI) in Oracle Receivables.

Receivables: The Receivables subject areas include an Email Recipients reporting attribute. The Receivables subject areas have the following additional reporting attributes for folders and subfolders:

Customer Accounts folder:

Subfolders: Customer Account Bank Account Details and Customer Account Debit Authorizations

Reporting attributes: bank account primary, bank account version number, bank account signing date, bank account cancellation date, bank account effective start date, bank account effective end date, bank account transaction type, bank account debit authorization method, bank account reason for amendment, bank account creditor identifier, bank account debtor, bank account debit authorization status

Customer Account Site folder:

Subfolders: Customer Account Site Debit Authorizations

Reporting attributes: site account primary, site account version number, site account reference number, site account signing date, site account cancellation date, site account effective start date, site account effective end date, site account transaction type, site account debit authorization method, site account reason for amendment, site account creditor identifier, site account creditor, site account debtor, site account debit authorization status

Customer Accounts folder:

Subfolders: Profile Credit and Collections

Reporting attributes: account profile credit analyst, order amount limit

Customer Account Site folder:

Subfolders: Customer Account Site Payment Details

Reporting attributes: site payment channel, site payment channel code

Receivables subject area: Miscellaneous Receipts Real Time and Miscellaneous Receipt Details

Folder: Miscellaneous Receipt Details

Reporting attributes: payment channel, payment channel code

Receivables subject area: Standard Receipts Application Details Real Time

Folder: Standard Receipt Details

Reporting attributes: batch identifier, batch status

Receivables subject area: Transactions Real Time

Folder: Payment

Reporting attributes: payment channel, payment channel code

Sample reports

The following list represents some of the available OTBI reports for Financials:

Account Analysis Report

Bank Statement Analysis Report

General Journals Report

Intercompany Transaction Summary Report

Payables Credit Memo Matching Report

Payables Discount Taken and Lost Report

Payables Invoice Audit by Voucher Number Listing

Payables Invoice Audit Listing

Payables Invoice Register

Payables Negative Supplier Balance Report

Payables Payment Register

Payables Posted Invoice Register

Payables Posted Payment Register

Payables to Ledger Reconciliation Report

Payables Withholding Tax by Tax Authority Report

Payables Withholding Tax Report

Receivables to Ledger Reconciliation Report

Trial Balance Report

Using the BI Administration Tool

Use the BI Administration Tool to set up the metadata called the Repository (also known as the Repository Protocol Design or RPD) and the repository files. The Repository consists of the following three layers:

Physical layer: Configures the database table structure and the relationships

Business Model and Mapping layer: Sorts information by business model, identifying the logical dimensions and facts

Presentation layer: Sorts the information by subject area, folders, and columns; represents the user’s view of the business model

In OTBI, you can use View Objects (VOs) via an application development framework (ADF) as the physical source rather than the database tables. The benefits to this are as follows:

It updates the same security model as Cloud applications.

It enhances the performance of the queries through SQL pruning. This pruning takes some of the partitions out of consideration while carrying out statement execution.

Using star schemas

You can set up star schemas for Oracle Transactional Business Intelligence analysis and reporting. The star schema is a data model, so named because it looks like a star, made up of several tables. Relational databases frequently use star schemas.

Key characteristics of star schemas include the following:

At least one fact table at the center

The fact tables link with several dimension tables, which make up the points of the star. The dimension tables store various transaction details.

A compound primary key that refers to various dimension tables

As an example, a star schema for analyzing receivables transactions may include the following dimension tables:

Accounting periods

Business units

Customers

Invoice details

Ledgers

Fact tables and dimension tables

A fact table is at the center of a star schema. The fact table has measures (or business facts) and connections to at least one dimension table. Within the fact table, the Foreign Keys column determine how it links with dimension tables. The measures columns within a fact table contain the data you are analyzing.

A dimension table contains additional data, which fleshes out the information regarding transactions. For example, a fact table may contain customer ID numbers for each transaction included in it. From this, the fact table links to a dimension table containing additional data about the person behind the ID number, such as their name, address, etc.