OTM Domains, Standard Schema and Data Dictionary

Domains:

OTM Data is organized into domains. Each domain is like grouping of data / business transactions based on the different business units with in the same organization.

If there is a corporation with logistics requirements across various business units like "Merchandise Shipping", "Food and Beverages", etc - you typically create one domain for each of these business units so that users and their roles can be tied to each business unit. User/role tied to a particular domain can only access data for their specific domain/business unit. Also advantage with domains is that customization specific to a business unit can be better controlled/maintained. For example, OTM Agents(workflows) with custom code can be developed specific to each domain so they they trigger only for transactions within that domain.

Domains can be created in hierarchical manner - like one parent domain at corporation level and child domains at business unit level under the same parent domain. With this structure you can maintain customization/features common to both the child domains at parent domain level. For example, you may define Item Numbers at parent domain level these items can be used on both the child domain transactions.

Each table in OTM will have DOMAIN_NAME as column to distinguish both setup/transactional data between domains. Also each table will have GID/XID columns for each business object. For example a PO will have ORDER_BASE_GID and ORDER_BASE_XID:

• ORDER_BASE_GID to represent a unique system generated/user entered identifier(primary key in most cases) that takes format "Domain Name"."PO Number" (For example,  DMN.12345 would be ORDER_BASE_GID value where DMN is the domain name and 12345 is the PO Number) 
• ORDER_BASE_XID to represent actual PO number that can be recognized by business users like free text value. Most of the cases business users might  use same value for both these GID/XID columns.

Standard OTM Schema:

GLOGOWNER Schema:  This schema is used to maintain all the transnational data like PO Data, Order Release data, Shipment data, etc.

REPORT OWNER Schema: This schema is used to store report definitions, oracle stored procedures that will be used by BI Publisher Query templates, etc. For objects created in this schema we should create a public synonym to make the object accessible to OTM application.

OTM Data Dictionary

Once you know the table name for a business object you can get more details from OTM Data Dictionary. In the OTM URL place "html/data_dict" next to "GC3" to access data dictionary

Example: www.otm-oracle.com/GC3/html/data_dict/

Note:  There is '*' against the columns to represent primary key combination and next to each column is Foreign Key information within the brackets.

Below example says - SHIP_UNIT_LINE table has SHIP_UNIT_GID, SHIP_UNIT_LINE_NO as primary key combination and SHIP_UNIT_LINE.SHIP_UNIT_GID references SHIP_UNIT.SHIP_UNIT_GID.



Note: Please post corrections(if any) to 'learnotm@outlook.com' 

OTM Inbound Integrations

There are two main ways of loading data into OTM system via XML files or CSV files.

Most common way of receiving OTM Inbound data is via XML files. OTM can read XML files which are in the format specified by GlogXML Schema. You can download this schema from :

Business Process Automation > Integration > Integration Manager > Retrieve Schemas > GlogXML.xsd

This file will describe the data structure for each OTM element like 'Location', 'Order Release', 'Shipment', etc along with some documentation.

Say, if we want to upload a new location to OTM, you follow below steps:

1. Read the GlogXML.xsd and identify the XML structure for element "Location". Once you map your input data values to OTM XML elements, you will end up coming with XML file similar to one below:

• <Transmission>
     <TransmissionHeader>
       <UserName>DBA.ADMIN</UserName>
       <Password>CHANGEME</Password>
       <IsProcessInSequence>Y</IsProcessInSequence>
     </TransmissionHeader>
     <TransmissionBody>
        <GLogXMLElement>
         <Location>
              <TransactionCode>IU</TransactionCode>
              <LocationGid>
                 <Gid>
                    <DomainName>ABC</DomainName>
                    <Xid>TEST SH OTM CORPORATION-45769</Xid>
                 </Gid>
              </LocationGid>
              <LocationName>TEST SH OTM COR,CLUTE,TX,USA</LocationName>
              <Address>
                 <AddressLines>
                    <SequenceNumber>1</SequenceNumber>
                    <AddressLine>1039 EAST PLANTATION</AddressLine>
                 </AddressLines>
                 <City>CLUTE</City>
                 <ProvinceCode>TX</ProvinceCode>
                 <PostalCode>32830</PostalCode>
                 <CountryCode3Gid>
                    <Gid>
                       <Xid>USA</Xid>
                    </Gid>
                 </CountryCode3Gid>
              </Address>
              <LocationRefnum>
                 <LocationRefnumQualifierGid>
                    <Gid>
                       <Xid>ORIGIN</Xid>
                    </Gid>
                 </LocationRefnumQualifierGid>
                 <LocationRefnumValue>CUSTOMER</LocationRefnumValue>
              </LocationRefnum>
              <LocationRefnum>
                 <LocationRefnumQualifierGid>
                    <Gid>
                       <Xid>CUSID</Xid>
                    </Gid>
                 </LocationRefnumQualifierGid>
                 <LocationRefnumValue>1130</LocationRefnumValue>
              </LocationRefnum>
              <LocationRefnum>
                 <LocationRefnumQualifierGid>
                    <Gid>
                       <Xid>CUSNM</Xid>
                    </Gid>
                 </LocationRefnumQualifierGid>
                 <LocationRefnumValue>MOORE SUPPLY CO</LocationRefnumValue>
              </LocationRefnum>
              <LocationRole>
                 <LocationRoleGid>
                    <Gid>
                       <Xid>CUSTOMER</Xid>
                    </Gid>
                 </LocationRoleGid>
              </LocationRole>
              <Corporation>
                 <CorporationName>OTM CORPORATION</CorporationName>
              </Corporation>
           </Location>
        </GLogXMLElement>
      </TransmissionBody>
   </Transmission> 

2. Save this file with extension '.xml'

3. Once you have XML ready, you can upload to OTM as below: Goto Business Process Automation -> Integration -> Integration Manager -> Upload XML/CSV Transmission and Browse XML File. 

4. Click Upload.

5. You will see following log:

6. Note the transmission number from the log

7. Navigate to Business Process Automation -> Integration -> Transmission Manager

8. Query for transmission and you should see transmission status as ‘PROCESSED’. 

In case of errors Report button displays error reasons like foreign key reference missing etc.

Any external system like EBS, SAP can frame this XML structure programatically and 'POST' this xml to OTM Integration servlet: glog.integration.servlet.WMServlet

Below is an PLSQL program posting Location element to OTM:

DECLARE

-- This is OTM Application URL Where string after GC3 is replaced as shown below
v_chr_url VARCHAR2(1000) := 'http://OTM-SERVER:7777/GC3/glog.integration.servlet.WMServlet';

-- This is OTM USER from which transmission needs to be created
v_otm_user VARCHAR2(100) := 'DBA.ADMIN';

-- This is password for the OTM USER
v_otm_pwd VARCHAR2(100)  := 'CHANGEME';

-- You can programtically from the xml string and pass it to this variable
v_data_in          VARCHAR2 (10000);

     
      v_http_req         UTL_HTTP.req;
      v_http_resp        UTL_HTTP.resp;
      v_chr_resp_val     VARCHAR2 (3000);

BEGIN
-- This is a sample transmission that creates location(Shipment Management -> --Shipment Management -> Location Manager) in OTM
-- Note that in the Transmission Body, GLOGXMLElement chosen here is Location. By --choosing appropriate element, you can load required data.
--In this script file, you see XML structure to send location information.
--You can get this structure by referring the GLOGXML.xsd
--Navigation to fetch this from OTM.
--1. Login in to OTM using DBA.ADMIN user
--2. Business Process Automation -> Integration -> Integration Manager -> --GlogXML.xsd
v_data_in := '<Transmission>
   <TransmissionHeader>
     <UserName>DBA.ADMIN</UserName>
     <Password>CHANGEME</Password>
     <IsProcessInSequence>Y</IsProcessInSequence>
   </TransmissionHeader>
   <TransmissionBody>
      <GLogXMLElement>
       <Location>
            <TransactionCode>IU</TransactionCode>
            <LocationGid>
               <Gid>
                  <DomainName>WHD</DomainName>
                  <Xid>TEST SH HAJOCA CORPORATION-45769</Xid>
               </Gid>
            </LocationGid>
            <LocationName>TEST SH HAJOCA COR,CLUTE,TX,USA</LocationName>
            <Address>
               <AddressLines>
                  <SequenceNumber>1</SequenceNumber>
                  <AddressLine>449 TEST PLANTATION</AddressLine>
               </AddressLines>
               <City>CLUTE</City>
               <ProvinceCode>TX</ProvinceCode>
               <PostalCode>32830</PostalCode>
               <CountryCode3Gid>
                  <Gid>
                     <Xid>USA</Xid>
                  </Gid>
               </CountryCode3Gid>
            </Address>
            <LocationRefnum>
               <LocationRefnumQualifierGid>
                  <Gid>
                     <Xid>ORIGIN</Xid>
                  </Gid>
               </LocationRefnumQualifierGid>
               <LocationRefnumValue>CUSTOMER</LocationRefnumValue>
            </LocationRefnum>
            <LocationRefnum>
               <LocationRefnumQualifierGid>
                  <Gid>
                     <Xid>CUSID</Xid>
                  </Gid>
               </LocationRefnumQualifierGid>
               <LocationRefnumValue>1130</LocationRefnumValue>
            <LocationRole>
               <LocationRoleGid>
                  <Gid>
                     <Xid>CUSTOMER</Xid>
                  </Gid>
               </LocationRoleGid>
            </LocationRole>
            <Corporation>
               <CorporationName>TEST CORPORATION</CorporationName>
            </Corporation>
         </Location>
      </GLogXMLElement>
    </TransmissionBody>
 </Transmission>';

      UTL_HTTP.set_transfer_timeout (1000);
      UTL_HTTP.set_detailed_excp_support (ENABLE => TRUE);
     
      -- Invoking the Web Service.
      v_http_req := UTL_HTTP.begin_request (v_chr_url, 'POST');
     
      -- Set the HTTP request headers
      UTL_HTTP.set_header (v_http_req, 'content-type', 'text/html');
      UTL_HTTP.set_header (v_http_req, 'content-length', LENGTH (v_data_in));
     
      -- Write the data to the body of the HTTP request
      UTL_HTTP.write_text (v_http_req, v_data_in);
     
      --Retrieving response of Transmission ID for OTM
      v_http_resp := UTL_HTTP.get_response (v_http_req);
      UTL_HTTP.read_text (v_http_resp, v_chr_resp_val, 3000);
      UTL_HTTP.end_response (v_http_resp);

END;
/ 

Note that if you have multiple integrations to be built from external system to OTM system to load data coming in different formats like EDI, text files, etc you should typlically go for a middleware tool like Oracle SOA(BPEL), Webmethods etc to translate these files from various formats to XML format defined by GlogXML schema.

Second way loading data to OTM is using CSV files. You may follow below steps to load location into OTM using CSV upload.

1. First we need to create a sample location manually in OTM.

2. Query for this location from back end table as follows:

select * from location where location_xid = 'TEST SH CORPORATION-45769'

3. Note down the WHERE clause from the query.
4. Goto Business Process Automation > Data Export > CSV Export

  5.

Select Table Name as 'LOCATION' and copy the WHERE clause that you have noted down in earlier step.
6. Click 'Run' and you will have following output:

 7. Select the grey text which is location data and save it in .txt file.  
Open this .txt file using Microsoft xls application and select comma as delimiter. Save this file as .csv file. To verify the .csv has data is correct format open the file using textpad and you should see data with comma separated values. 8. Now upload the file using Business Process Automation -> Integration -> Integration Manager -> Upload XML/CSV Transmission:


 
 

Important Note:
Ensure that date columns will follow the NLS Date Format mentioned in line 2 of the csv file.The results screen is something like this. Note that Process Count and Error Count values are populated in the result.

Note: Please post corrections(if any) to 'learnotm@outlook.com'