ERP/HCM - Create OTBI report with parameter

Usecase: Here, we will create an OTBI Payable invoice report with a invoince number parameter named as P_INVOICE_NUM.

Implemented steps:

1. Navigation >> Tools >> Reports & Analytics >> browse Catalog >> Create >> Analysis 
2. Select Subject area >> Payables Invoices - Transactions Real Time
3. Select the required columns or fields from subject areas to selected columns sections
4. Go to the Results tab and see the output.
5. Go to Prompts tab >> create a variable prompt >> provide variable name as P_INVOICE_NUM >> user input as Choice List >> select invoice number as column >> ok
6. Go back to Criteria tab >> add filter >> Invoice Number >> value as Presentation Variable >>  put created Parameter name: P_INVOICE_NUM in Variable_Expr >> put a default value >> ok
7.  Go to the Results tab and see the output
8. Save the report
9. Go th catalog >>open report >> select value >> ok >> we can see the output.

Detailed screenshots:

ERP / HCM - BIP vs OTBI

Fusion report Or OBIEE (Oracle Business Inteligence enterprise edition) is divided into 2 sections:

1. BIP( Business Inteligence publisher)
1. SQL Query >> Data Model >>select required columns >> Layout >> Run >> Output
2. OTBI (Oracle Transactional Business Inteligence)
1. Subject Area >>  Select Columns >> Layout >> Run >> Output 

WHAT IS ORACLE BUSINESS INTELLIGENCE (BI) PUBLISHER?

• Oracle Business Intelligence Applications are complete, prebuilt BI solutions that deliver intuitive, role-based intelligence throughout an organization. 
• BI Publisher can be used as an alternative reporting solution to OTBI. Some of the data sources available are SQL Query, XML, HCM Extract, and View Object (brought in through web service, ADPI, etc.). 
•  BI Publisher works as an enterprise reporting solution for authoring, managing, and delivering reports from multiple data sources in multiple formats through multiple channels (E.g., checks, invoices, remittance, etc.). Utilize BI Publisher for writing your own SQL query, formatting reports, joining historical data with real-time information, and report bursting. Typical end users include Technical Human Resource Information System (HRIS) and IT Developers.

WHAT IS ORACLE TRANSACTIONAL BUSINESS INTELLIGENCE (OTBI)?

• OTBI provides a flexible and easy to use analysis tool that helps to gain real-time insight into transactional data.
• OTBI is aimed at business users and especially shines when the report creator is someone who does not know SQL and/or the underlying table structures. 
• OTBI’s core functionality allows users to simply drag and drop selected data from subject areas and use different graphical views. Data can be filtered, drill-down can be enabled, and action links can be created. Reports or dashboards can be scheduled for delivery and reports can be exported to Excel, Word, and other formats. A reporting wizard (BI Composer) is available to guide casual business users through a few simple steps to run, edit or create OTBI reports and queries. 
• Typical end users include Financial Analysts, Compensation Analysts, Benefit Analysts, and Human Resources Analysts.

BIP vs OTBI

1. For BIP: Manual SQL we can write, but In OTBI: No SQL needed, drag and drop interface,subject Areas available.
2. For BIP: its more technical where as for OTBI: functional and technical both can develop
3. For BIP: thousands of tables, joins needed but for OTBI: presentation layer developed so user does not need to join different tables together.
4. For BIP: reports can takr very long time to build where as in OTBI: relatively quick build.
5. For BIP: can get at almost all needed fields and information but in OTBI: not all fields are available in subject areas.
6. For BIP: we can use multiple report formats but for OTBI: restricted number of formats.

See the table below for more differences:

Advantages of OTBI over BIP:

1. Functional consultant can build the OTBI report.
2. Oracle has already provided pre build subject areas.
3. We cab build the otbi report for scm, crm finance etc.
4. We can creatw the dashboard using OTBI

DIsadvantages of OTBI over BIP:

1. We cant do custom report
2. We cant build any custom subject areas.

Reference:

https://elire.com/oracle-cloud-reporting-otbi-and-bi-publisher/

OIC HCM - Configure HCM Extract bulk data in an OIC Integration

Usecase: Here, we will perform the following:

1. Define an HCM extract and submit/upload to Webcenter content
2. Create an OIC integration to download the HCM bulk data from UCM or webcenter content.

Note: we can extract data from HCM following 3 ways:

1. BIP report ( SQL queries >> data model >> report like rtf)
2. OTBI( Subject Areas >> drag and drop)
3. HCM Extract ( User Entities >> SQL queries written by Oracle)

HCM Extract Logic steps:

1. Define hcm extract >> create new
2. Select extract types like HR Archive
3. Define root data group like persons with user entity : PER_EXT_SEC_PERSON_UE
4. Add filters like: Extract Person System Person Type = 'EMP' and calculate rows
5. Add record under data group : person details 
6. Addd record attributes as database items like Person First Name, Person Last Name,Person Number etc.
7. Define extract Delivery options as WebCenter Content, provide output name as uploadToUCM.xml
8. Go to advanced  sectiona and provide encryption mode and Integration name.
9. Validate the created delivery >> validate
10. Submit extract and check the content uploaded to webcenter content server.

Download from UCM in OIC logic steps:

1. Create an HCM connection and create an scheduled orchestration integration
2. Add a schedule parameter to store processed document id and initialize it with value of 0.
3. Drag and drop Oracle HCM cloud adpater and select Receive Files from HCM cloud option
4. Select the integration name for the HCM extract like IntegrationDemo123
5. In the mapper, map the schedule parameter to lastProcessedDocumentId
6. Drag a stage and read file in segements using response file reference and use it as required.
7. Take a assign and update the schedule parameter  document id.

Step1: Define an HCM extract and submit to Webcenter content

Navigation >> My Client Groups >> Data Exchange >> Extract definitions

Step2: Create an OIC integration to download the HCM bulk data from UCM or webcentet content.

References:

https://docs.oracle.com/en/cloud/paas/integration-cloud/hcm-adapter/sample-integration-flow-demonstrate-extract-bulk-data-option.html

OIC - Why should we choose OIC over SOACS?

When To Choose OIC?

• The OIC is more suitable to implement the integration when you want to do the integration with services and APIs already in the cloud with the On-Premises system.
• If the Pre-existing integration is simple, data can be extracted from On-Premises service, legacy API and applications and can be utilized on OIC.
• If you have an existing SOA architecture On-Premises and working efficiently and the requirement is to only connect the already existing services with the cloud systems then OIC would be perfect for it.
• Using connectivity agents, the integration can be created with the already existing services.
• It is quicker and easier to develop atomic and simple integrations in OIC.
• It requires fewer investments as the license is cheaper then SOA CS.
• Oracle ICS is positioned for the LOB developer(Line of Business Developer) who does not necessarily understand SOAP/REST/XML and other integration related technologies, who functionally knows the application's being integrated  and does understand the underlying business entities.

When To Choose SOA CS?

• When large amounts of messages or the size of files are to be sent, then the SOA CS is the best option.
• If SOA CS the Integration can be shifted from Cloud to On-Premises effortlessly.
• If the logic of orchestrated transaction and service is complex then SOA CS would be preferred with all BPEL features.
• If the integration or requirement involves B2B, API Management or streaming monitoring then it will be necessary to apply SOA CS.
• In case of pure On-Premise integration, Go with SOA CS.

Conclusion

In my opinion, the main differentiating factor of OIC from other integration cloud products is,

• The rich variety of connectors that they offer (currently 50) that includes connections to other cloud products like RightNow, Eloqua, SalesCloud and various social media like Facebook, Twitter, LinkedIn and a lot more.

OIC HCM - Subscribe to Atom Feeds in a scheduled integration | Oracle Integration Cloud

Usecase: Here, we will create a Scheduled integration and subscribe HCM Atom Feeds for Employee Update and write them to a FTP directory.

Summary:

1. Atom Feeds enable to track changes made to feed enabled resources in Oracle Global Human Resources Cloud and to send them to different downstream applications.
2. It supports following resources for Atom feeds:
1. Employee New Hire
2. Employee Update
3. Employee Termination
4. Employee Assignement
5. Grade
6. Job
7. Location
8. Organization
9. Position

Logic Implemented steps:

1. Create a schedule parameter to hold timestamp , which will help to take Atom data after this timestamp. Format: YYYY-MM-DDTHH:MM:SS:sssZ e.g. 2018-06-03T02:34:06.000Z
2. Configure HCM adapter - subscribe to updates (via Atom Feed) >> select an Atom feed like Employee Update and map the timestamp to updated Min.
3. Take a for each loop and assign repeating element for employeeupdatefeed_update
4. Take 2nd inner loop and assign repeating element as changedAtrributes
5. In the inner loop, configure ftp adapter and write as binary, privide output directory, file name pattern like EmployeeUpdate%SEQ%.csv >> privide sample file where fields are: PersonId,PersonNumber,Attribute,New,Old >> map the required fields.
6. In the outer loop, update the timestamp using updated_timestamp field of the feed response.

Integration flow:

Detailed screenshots:

References:

https://docs.oracle.com/en/cloud/paas/integration-cloud/hcm-adapter/subscribe-atom-feeds-scheduled-integration.html

https://www.oicbasics.com/2022/04/subscribe-to-hcm-cloud-atom-feeds.html?m=1

OIC HCM - Oracle Cloud HCM integration patterns Highlevel

Oracle HCM Cloud has multiple capabilities for Batch & Real time Integration Scenarios, below diagram depicts product capabilities -

Reference:

https://www.ateam-oracle.com/post/oracle-cloud-hcm-integration-using-oracle-paas-patterns-use-cases