Oracle Cloud Certifications are only valid for 18 Months !!!

I was shocked to see an email that my OTM certification will expire shortly because Oracle decided to introduce the recertification policy. 

OTM Cloud was a big change in the market but nothing much changes in the system every 18 months. 

So I fail to understand why OTM exam need to be taken every 18 months to keep it active.

Order Release - Equipments

If a user want to decide how many shipment should be built for an Order Release, he/she can do so by specifying the list of of equipments in the OR Edit Screen.

OTM will not use its optimization logic in these cases. Instead, it runs an algorithm to utilize the containers mentioned in the OR even if the Shipment optimization is 1%. 

Planning will only fail when OTM is unable to allocate OR lines to all the equipments. 

Scenario 1:
OR has 10 lines. 
User has specified 2 x 20FT containers in the OR Equipments section. 

After planning, OTM is very likely to distribute OR lines like below

SH1 : 5 OR Lines
SH2 : 5 OR Lines

Scenario 2:
OR has 2 lines
User has specified 3 x 20FT containers.
Planning is very likely to fail, especially if the OR lines are not splittable. 

Custom Status Codes in Event Page - Setup

We may need to add some custom events/reasons while configuring OTM.
Sometimes its very confusing to setup several statuses their profiles, along with reason codes and profiles, etc.

Hope this helps

CSV Import Using CSVUTIL utility

In my previous post, we've seen how to import an individual CSV to insert/update/delete records of one table. But when we need to upload data into several tables and doing it one by one can be very very time consuming. Luckily, OTM gives us an utility with which we can import more than one CSV file.

Let’s take a scenario of creating a CONTACT and assign it to an existing contact group.

1. Prepare a CSV file for CONTACT Table

2. Prepare a CSV for CONTACT_GROUP Table

3. Open a notepad and write the below commands

-dataFileName CONTACT.CSV -command i

-dataFileName CONTACT_GROUP.CSV -command i

4. Save the file as csvutil.ctl

List the CSV files in the order they should be uploaded.

In this case, first Contact needs to be created & then assigned to a group.

5. Create a zip file with 3 files (CONTACT.CSV, CONTACT_GROUP.CSV, csvutil.ctl)

6. Navigate to Business Process Automation -> Integration -> Integration manager -> Upload a CSV/XML Transmission

7. Upload the zip file

8. If there are no errors OTM returns a log file.

Importing CSV files into OTM

OTM requires lot of data to model your business. During Implementation, you may require to import lot of static data like
-> Locations
-> Service Providers
-> Items
-> Contacts, etc

Instead of entering manually on OTM web pages, importing static data in CSV format is a better option. Because with CSV you can import large amounts of data very quickly.

Important Note: 
1. When you import the data via CSV, OTM inserts it directly into the database without calling any events or setting any statuses.
2. Also OTM changes the data into UPPER case , where as this doesn't happen during the data via integration.
3. Use a text editor or Ms-excel with extreme care. 
4. Try to skip optional columns.

Format of the CSV file:
1. First line of the file must state the table name in which the data goes to.
2. Second line must contain the list of column names separated with a comma
3. Third line may contain a SQL command to execture. [Eg: Alter session]
4. Fourth Line onwards, it should be data which is to be inserted into the table.

Example of Location CSV:

Data Import Order
A single CSV file can load the data of only one table. Hence, you must first load the data of master tables and then the child tables which use foreign keys.

Steps to Import
1. Navigate to Business Process Automation -> Integration -> Integration Manager
2. Select 'Upload an XML/CSV Transmission'
3. Click on browser and select the csv file which needs to be uploaded.
4. Choose one of the below command and click on Run button
i - insert CSV data into database
u - update the data in the database
d - delete the data from database
ii,uu,dd - suppressing error messages for corresponding commands and continue with next record.
iu - attempts to insert the data. If record exists, updates the data.
5. Make Sure that the error count is 0 and the process count is the number of records you are expecting. 




.

CSV Export from OTM

There will be several occasions when you might have to export the data from OTM.

Look at the steps to follow for exporting CSV Data.

1.Navigate to Business Process Automation -> Data Export -> CSV Export

2.OTM asks what you want to export

exportWhat	You can select either exportTable – One table at a time exportTableSet – All tables in a table set exportQueryResults – All records in a SQL Query
outputDestination	You can export to Browser A remote OTM instance A file on your OTM Server
runInBackground	If you select ‘Yes’, this process will run in background and then output will be emailed to you. For that in 2nd step you need to provide your email address and SMTP host.
useSelectList	If you select ‘Yes’, you will be able to select specific records which you want to export.

3. Click on Run Button

4. Assuming that you selected ‘exportTable’, you can select a table from the drop down list.

5. Optionally you can write a condition. [No Need to use ‘where’; For eg: domain_name = ‘ABC’. ]

6. Click on Run & Copy the records into Notepad which are available in comma separated format.

7. Save the notepad with CSV extension. 

OTM Data Dictionary

OTM Data Dictionary has the list of all OTM database tables and their columns, foreign key references etc,

Data Dictionary can be opened with <OTM_Installation_Directory>/apache/htdocs/html/data_dict/index.html

Below diagram show the various sections of Data Dictionary.

I opened OB_REFNUM table & can see that there is a composite primary key for this table. Also 2 columns are referring to OB_ORDER_BASE & OB_REFNUM_QUAL tables.