OIC - ERP - How to get the child process id from import request id using OTBI report

What: To show how to get child process id from process request id.

How: Highlevel:

• Create a BI report using SQL query 
• Call it from OIC using soap connection
• Check it from ERP schedule processes

Step1: create BI report

Sql query to use in data model:

See the below blogs:.

How to create data model and test sql:

oic-erp-how-to-test-sql-query-in-otbi

How to create BI reports:

oic-how-to-create-bi-report-in-oracle

 How to create eText based BI report:

oic-create-etext-template-based-bi

How to invoke erp Bi report:

oic-invoke-erp-bi-report

Step2:

Create a conneection using ExternalReportWSSServices wsdl

In the Integration, Invoke the connection and operarion as runreport and map as following:

• Provide name values pair as Name: P_REQUEST_ID & value: importRequestID
• provide report absolute path
• SizeofDatachunkDownload as -1

Step3: We can see the processes status from ERP Schedule process : 

Import accounting Trasactions is the import req id: 1720657

Child process : Create Accounting for Data Set : 1720660

OIC -ERP - How to test sql query in OTBI data model

What: Here I will show you how we can test ERP OTBI sql query from data model:

Why: Suppose we have an OTBI report which we are invoking from OIC to get some details. Sometimes it gets failed and showing BI report db sql query related issue. For that issue triage, we need to test the data set sql query behind the BI report. Most of the cases, its a big query so we can break the sql into small executable module and test in data model.

How: Steps:

Step1: Navigate to Menu 》 Tools 》 Reports and Analytics 

Step2: Browse Catalog

 

Step3: shared folders 》 Custom 》 GO to the path and select desired folder.

 Step4: Edit the report 

Step5: click on the data model.

Step6: Hit on the + sign button to create a Dataset for SQL Query

Step7: Provide the sql query to test and press ok.

This will create the dataset. 

Step8: Click on data tab and view

It will show the tested query result.

OIC - Reactivate the Integration after Connection gets changed

Why we need to do reactivate :

If we do changes in a connection and that connection is using by multiple integration, then we must save the connection and reactivate all thr integration otherwise the changes in the connection will not reflect to those integrarions.

Steps:

Do the change in the connection, like password, uri etc and hit save button and it will allow you to reactivate all the integrarions.

ODI - Steps to increase overall ADW database storage size

Step1: Log in to OCI instance and hamburger -- select Oracle Database and Autonomous Database

Step2: select the required compartment and click on database.

Step3: Navigate to More actions and then Manage Scaling.

Step4: Provide new required storage size and then click update.

In few minutes ADW database size will be increased.

OIC - Add Header or line file field names with easy method

Sometimes when we send FBDI files to ERP/HCM or other applications, we may also need to share the header or line field names to the file itself. To add huge number of field names manually is a hectice process. But there is option to easily we can add the field names. See thr below steps.

Steps:

Step1: Take a stage activity and write file operation and privide file name, virtual stage directory and select append to existing file.

Step2: provide a sample csv file with one field name and select Use first row as column headers.

Step3: add all the fields as comma separated within double quotes to target field .

And its done.

ODI 12c - Stop and Start ODI Instance from OCI console

Here, We will see how to Stop and start ODI server instance on OCI

Steps:

Step1: Login to OCI console with the account 

Step2: After login, click on Hamburger and navigate to instances

Step3: Select the appropriate compartment

Step4: It will show all the available instances. Select the instance to restart.

Step5: Use Stop button to stop the instance and Once status says "Stopped" we can restart using "Start" button.

Use below useful commands whenever needed:

"systemctl status agentodi.service" -- To check if its disablwd or not.

"sudo systemctl daemon-reload" 

Path of ODI studio: /u01/oracle/mwh/odi/studio

To restart odi normally "./odi.sh"

To restart odi in clean way "./odi.sh -clean -initialize"

ODI 12c - Scheduled jobs and load plans executed twice by agent after instance restart

This issue occurs for the following reasons:

• The instance was restarted without the agent being stopped first.
• The agentodi.service is active even after the management service was moved to the multi-app Jetty service manageappsodi.service. since the agentodi.service is active , it is causing a second agent to start resulting in schedule duplication .

Solution:

When the agent is stopped gracefully, this issue is not observed in ODI on Marketplace version 12.2.1.4.210321 or higher.

Ensure to always stop the agent gracefully anytime before restarting the instance.

Disable the agentodi.service

Log in to the compute VM instance using the opc user using putty or from command line:.

ssh -i <private key> opc@<ipaddress>

Issue the following commands for disabling the agentodi.service:

sudo systemctl disable agentodi.service

sudo systemctl daemon-reload

Reboot the instance and test the scheduler behavior.