Infosys Integration Saved Queries In OTM
Infosys
Integration Saved Queries In OTM
April, 2013
INFOSYS LIMITED
Bangalore
Document No. Ver. Rev. :
Authorized by: Signature/:
Date:
©2014 Infosys Limited, India
Infosys Integration Saved Queries In OTM
COPYRIGHT NOTICE
©2014 Infosys Limited, Bangalore, India. All rights reserved. Infosys believes the information
in this document is accurate as of its publication date; such information is subject to change
without notice. Infosys acknowledges the proprietary rights of other companies to the
trademarks, product names and such other intellectual property rights mentioned in this
document. Except as expressly permitted, neither this document nor any part of it may be
reproduced, stored in a retrieval system, or transmitted in any form or by any means,
electronic, mechanical, printing, photocopying, recording or otherwise, without the prior
permission of Infosys Limited and/or any named intellectual property rights holders under
this document.
Infosys Limited
Hosur Road
Electronic City, 3rd Cross
Bangalore 560 100
India.
Telephone: (91) (80)28520 261-270
Fax: (91) (80) 8520 362
Website: http://www.infosys.com
©2014 Infosys Limited, India
Infosys Integration Saved Queries In OTM
Author : Kiran Tnaikella
Date written (MM/DD/YY) : 16/04/14
Project Details
Project(s) involved : NOL1433H
H/W Platform : NA
S/W Environment : NA
Application Type : Oracle Transportation Management
Project Type : Integration
Target readers
All
Keywords
Integration Saved Queries, XML Tags, OTM
Prerequisite
PL / Sql
©2014 Infosys Limited, India
Infosys Integration Saved Queries In OTM
Table of Contents
1.0 Introduction ..................................................................................................... 5
2.0 How it works .................................................................................................... 5
3.0 Handling SQL within Integration Saved Queries .................................................... 7
i) Simple SQL Call ................................................................................................ 7
ii) Using Joins within SQL calls ................................................................................ 7
iii) Using Union within Integration Saved Quey .......................................................... 7
iv) Calling a procedure from SQL ............................................................................. 7
4.0 Other scenarios ................................................................................................ 8
5.0 Public Integration Saved queries in OTM .............................................................. 9
6.0 P.S ................................................................................................................ 10
7.0 Reference ...................................................................................................... 10
©2014 Infosys Limited, India
Infosys Integration Saved Queries In OTM
1.0 Introduction
Integration saved queries provide necessary control and flexibility to identifying and search
information without using a GID or when you want to generate a GID based on a query.
Integration Saved Query is a very powerful utility to do pre-processing of an Incoming XML
before it reaches the transmission tables within OTM
Within OTM Integration Saved queries can be accessed from Business Process Automation
> Power Data > Integration > Integration Saved Queries.
2.0 How it works
Please find the sample Incoming XML and how the integration saved query is used to populate
the GID given below
Sample Incoming XML
<TransmissionBody>
<GLogXMLElement>
<ShipmentStatus>
<ServiceProviderAlias>
<ServiceProviderAliasQualifierGid>
<Gid>
<Xid>GLOG</Xid>
</Gid>
</ServiceProviderAliasQualifierGid>
<ServiceProviderAliasValue>SAMPLE.EXAMPLE</ServiceProviderAliasValue>
</ServiceProviderAlias>
<Gid>
<DomainName>SAMPLE/EXAMPLE</DomainName>
<Value>11111</Value>
</Gid>
©2014 Infosys Limited, India
Infosys Integration Saved Queries In OTM
Sample Incoming XML with changes made to accommodate an Integration Saved Query
Changes
<TransmissionBody>
<GLogXMLElement>
<ShipmentStatus>
<ServiceProviderAlias>
<ServiceProviderAliasQualifierGid>
<Gid>
<Xid>GLOG</Xid>
</Gid>
</ServiceProviderAliasQualifierGid>
<ServiceProviderAliasValue>SAMPLE.EXAMPLE</ServiceProviderAliasValue>
</ServiceProviderAlias>
<IntSavedQuery>
<IntSavedQueryGid>
<Gid>
<DomainName>SAMPLE/TEST1</DomainName>
<Xid>GET_SHIPMENT_GID</Xid>
</Gid>
</IntSavedQueryGid>
<IntSavedQueryArg>
<ArgName>INPUTVALUE</ArgName>
<ArgValue>123456</ArgValue>
</IntSavedQueryArg>
</IntSavedQuery>
Sample Integration Saved query
SELECT SHIPMENT_XID from SHIPMENT WHERE SHIPMENT_GID= '{%INPUTVALUE%}'
“GET_SHIPMENT_GID” integration saved query is created by navigating to Business Process
Automation > Power Data > Integration > Integration Saved Queries and the above
query is used
The {} expression is an XPath statement
How it works
As soon as the XML comes in the “Get_Shipment_Gid” integration saved query is called before
populating the GID. The query within this integration saved query is executed to populated
the GID value within ArgValue
©2014 Infosys Limited, India
Infosys Integration Saved Queries In OTM
3.0 Handling SQL within Integration Saved Queries
There are different ways you can write sql within Integration Saved Queries.
Please find some Sample Integration Saved Query calls given below
i) Simple SQL Call
SELECT SHIPMENT_XID from SHIPMENT WHERE SHIPMENT_GID=
'{%INPUTVALUE%}'
ii) Using Joins within SQL calls
SELECT s.SHIPMENT_GID from SHIPMENT s, view_shipment_order_rlease vsor
WHERE vsor.order_release_gid= '{%INPUTVALUE%}' and
vsor.shipment_gid=s.shipment_Gid
iii) Using Union within Integration Saved Quey
Using Unions
SELECT SHIPMENT_GID FROM SHIPMENT WHERE
'{%INPUTVALUE%}'=SHIPMENT_XID
UNION
SELECT shipment_gid FROM SHIPMENT_REFNUM WHERE
SHIPMENT_REFNUM_QUAL_GID ='EXAMPLE.ORDERID' and
SHIPMENT_REFNUM_VALUE like '%'||'{%INPUTVALUE%}'
iv) Calling a procedure from SQL
SELECT SHIPMENT_GID from SHIPMENT WHERE 1=2
UNION
SELECT GET_SHIPMENT_GID
('{%SHIPMENTID%}','{%ORDERID%}','{%POID%}','{%CARRIERPRO%}') FROM
DUAL
Please note that the GET_SHIPMENT_GID is a procedure call that you are making and it
needs to be used in conjunction within a union as indicated above
©2014 Infosys Limited, India
Infosys Integration Saved Queries In OTM
4.0 Other scenarios
It is a common requirement that you might have to populate GID based on multiple
arguments within an XML. However, handling such requirement becomes tricky as you need
to make changes to the XML to hold multiple arguments
Please find a sample XML where you have to populate the Shipment GID based on multiple
arguments ie., either Shipment XID or Order Release GID or PO ID
XML changes need to made as given below to capture multiple arguments
<TransmissionBody>
<GLogXMLElement>
<ShipmentStatus>
<ServiceProviderAlias>
<ServiceProviderAliasQualifierGid>
<Gid>
<Xid>GLOG</Xid>
</Gid>
</ServiceProviderAliasQualifierGid>
<ServiceProviderAliasValue>SAMPLE.TEST1</ServiceProviderAliasValue>
</ServiceProviderAlias>
<IntSavedQuery>
<IntSavedQueryGid>
<Gid>
<DomainName> SAMPLE </DomainName>
<Xid>GET_SHIPMENT_GID</Xid>
</Gid>
</IntSavedQueryGid>
<IntSavedQueryArg>
<ArgName>SHIPMENTID</ArgName>
<ArgValue>123456</ArgValue>
</IntSavedQueryArg>
<IntSavedQueryArg>
<ArgName>ORDERID</ArgName>
<ArgValue>1234</ArgValue>
</IntSavedQueryArg>
<IntSavedQueryArg>
<ArgName>POID</ArgName>
<ArgValue>123456</ArgValue>
</IntSavedQueryArg>
<IntSavedQueryArg>
</IntSavedQueryArg>
</IntSavedQuery>
©2014 Infosys Limited, India
Infosys Integration Saved Queries In OTM
Sample integration saved query to handle multiple argument value to populate the Shipment
GID
SELECT SHIPMENT_GID from SHIPMENT WHERE 1=2
UNION ALL
SELECT GET_SHIPMENT_GID
('{%SHIPMENTID%}','{%ORDERID%}','{%POID%}','{%CARRIERPRO%}') FROM
DUAL
5.0 Public Integration Saved queries in OTM
Please find the list of public OTM saved queries within OTM given below
Domain
Saved Query Name Name
INT_INVOICE_GID_EXAMPLE_1 PUBLIC
INT_INVOICE_GID_EXAMPLE_2 PUBLIC
INT_LOCATION_GID_1 PUBLIC
INT_LOCATION_GID_2 PUBLIC
INT_SHIPMENT_GID_1 PUBLIC
INT_SHIPMENT_GID_2 PUBLIC
INT_SHIPMENT_GROUP_STATUS_GID_1 PUBLIC
INT_SHIPMENT_GROUP_STATUS_GID_2 PUBLIC
INT_SHIPMENT_STATUS_GID_1 PUBLIC
INT_SHIPMENT_STATUS_GID_2 PUBLIC
INT_S_SHIP_UNIT_GID_EXAMPLE_1 PUBLIC
INT_S_SHIP_UNIT_GID_EXAMPLE_2 PUBLIC
INT_TRANS_ORDER_GID_1 PUBLIC
INT_TRANS_ORDER_GID_2 PUBLIC
INT_TRANS_ORDER_LINE_GID_1 PUBLIC
INT_TRANS_ORDER_LINE_GID_2 PUBLIC
S_EQUIPMENT_1 PUBLIC
©2014 Infosys Limited, India
Infosys Integration Saved Queries In OTM
6.0 P.S
Integration Saved Query is a very powerful utility to do pre-processing of an Incoming XML
within OTM. This document is restricted only to the usage of Integration saved query and
ways of handling challenges using the same but not to making XSL changes.
7.0 Reference
1. OTM Online Help
2. www.OTMFAQ.com
©2014 Infosys Limited, India
