Workbenches

Workbenches provided easy way for users to view data across multiple object types on the same screen and also load data using some default criteria (query) as soon as workbench is launched.

There are several ways to configure the workbench and several features provided by Oracle but let's take a simple example of how to create a 3 level hierarchy display to show order releases related to a PO and shipment related to order releases.

A workbench will have one or more layouts and each layout can be assoicated to content from standard object types like Order Base, Order Release, etc.

For our scenario, we need to create three layouts:

• Purchase Orders layout with content from Order Base table
• Order Releases/Bookings layout with content from Order Release table. This content should be detail (child level) for Purchase Order content.
• Shipment layout with contect from Buy Shipments table. This content should be detail (child) level content associated to order release selected.

Note: As pre-requisite you need to have screensets defined for each of above layouts ready before you proceed further. Screenset configuration is covered in a different post.

To create a new workbench layout:

Navigate to:

Configuration and Administration > User Configuration > Workbench Designer New

On the left hand side, select 'Create Layout' action. Enter below details:

• Component Type: Table
• Object Type: Order Base
• Tab Name: Purchase Orders
• Screen Set: OB_ORDER_BASE
• Check 'Default first row selection'

Click OK and You will now see Layout added.

Click 'Done Editing' action on the right side panel.

To see PO data using this new Layout, click 'Add' button and query required Order Base(PO) as shown:

Now let's add a new layout to show order releases associated to PO.

For this, let's write an SQL that takes PO number as input and fetches Order Release GID:

Saved Query: TEMP_ORDER_REL

select order_release_gid  from order_release  where order_base_gid = '?'

On the right panel click 'Edit Layout', and on the top right corner of this layout, click button 'Split Horizontally'.

This will create a new empty layout on the right side of existing 'Purchase Orders' layout.

On this new blank layout, go to right top corner and click 'Add content' button and enter below details:

• Component Type: Table
• Object Type: Order Release
• Tab Name: Bookings
• Screen Set: ORDER_RELEASE
• Detail Table: Check this option
• Associated Tables: Purchase Order Saved Search: TEMP_ORDER_REL
• Note: Using this 'Associated Table' option - link is being establshed from Parent PO level data to child order release level data record.

Click OK

Click 'Done Editing' from right side panel.

Now to test this select a PO that has order releases, and add query this PO using first layout. This should automatically display related order releases in second layout.

Now to see shipments associated to Bookings repeat the same steps as above but note that query associated should always point to primary key of the object type and to the associated base table. If you have complex SQL to fetch data, use IN clause on final SQL as shown in below example.

Create below Saved Query to pull Shipment GID for a particular Order Release:

Saved Query ID: TEMP_SHIPMENT with below SQL:

 

select shipment_gid 

from shipment

where shipment_gid in 

(select ssej.shipment_gid

from S_SHIP_UNIT_LINE ssulej, 

     S_SHIP_UNIT ssuej, 

S_EQUIPMENT_S_SHIP_UNIT_JOIN sessuj, 

ORDER_RELEASE orej, 

SHIPMENT_S_EQUIPMENT_JOIN ssej,

shipment shp

where ssulej.order_release_gid=orej.order_release_gid 

  and ssuej.s_ship_unit_gid=ssulej.s_ship_unit_gid 

  and sessuj.s_ship_unit_gid=ssuej.s_ship_unit_gid

  and orej.order_release_gid='?'

  and ssej.s_equipment_gid=sessuj.s_equipment_gid)

Now edit 'Bookings' Layout and from top right corner click 'Split Vertically'

This will add blank layout on bottom of the 'Bookings' Layout. 

On this blank layout. go to top right corner and click 'Add Content' and enter below details:

• Component Type: Table
• Object Type: Buy Shipment
• Tab Name: Shipments
• Screen Set: BUY_SHIPMENT
• Detail Table: Check this option
• Associated Tables: 
• Purchase Order Saved Search: Leave this as blank
• Bookings Saved Search: TEMP_SHIPMENT
• Note: Using this 'Associated Table' option - link is being establshed from Parent Order Release level data to child shipment level data record for this scenario

Click OK

Click 'Done Editing' from right side panel.

Now to test this select a PO that has order releases, and add query this PO using first layout. This should automatically display related order releases in second layout. Now select an order release that has shipments, you should see the shipment records on the third layout.

CSV Data Uploads

We can load data to OTM is using CSV files. You can follow below steps to load location into OTM using CSV upload. Note that same steps are applicable to any OTM object like order release,shipment, invoice, etc.

1. Create a sample location manually in OTM with requirement information.

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.

Posting Data to OTM using HTTP request

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.