Integration Saved Queries

This page is accessed via Business Process Automation > Power Data > Integration > Integration Saved Queries.

Integration saved queries are used to view or modify integration saved queries. Integration saved queries provide greater flexibility for identifying and searching for information without using a GID. Queries are written as SQL statements that contain references to the information in the incoming XML transmission.

For example, a query for the TransOrderGid using a Refnum value with a qualifier of 'PO' may look like this:

select ob.ORDER_BASE_GID from OB_REFNUM ob where ob.ORDER_REFNUM_QUAL_GID = 'PO' and ob.OB_REFNUM_VALUE = '{TransOrder/TransOrderHeader/ OrderRefnum[OrderRefnumQualifierGid/Gid/ Xid='PO' and ( not(OrderRefnumQualifierGid/Gid/DomainName) or OrderRefnumQualifierGid/Gid/DomainName = '' or OrderRefnumQualifierGid/Gid/DomainName = 'PUBLIC')]/ OrderRefnumValue}'

The {} expression is an XPath statement. An example Xpath is {Location/LocationRefnum/ LocationRefnumValue} which would resolve to the value of LocationRefnumValue as set in the incoming xml transaction. The root element for the XPath statement is the element being searched. That is TransOrder, TransOrderLine, and so on.

There are several integration saved queries that are used as defaults if certain elements are missing in your XML. If a TransOrder Gid is missing, then the INT_TRANS_ORDER_GID_1 and INT_TRANS_ORDER_GID_2 saved queries are used. If a Shipment GID is missing, then the INT_SHIPMENT_GID_1 and INT_SHIPMENT_GID_2 saved queries are used. If a Location GID is missing, then the INT_LOCATION_GID_1 and INT_LOCATION_GID_2 saved queries are used.

The default integration saved queries are only used when the transaction code element has a value of U or D, and the above mentioned GIDs are missing. Otherwise, any integration saved query you have defined with your XML will be applied.

Note: Modifying integration saved queries requires knowledge of SQL statements.

Note: The SQL must NOT contain any carriage returns.

You can define queries where the query arguments are concatenated with fixed text. This is helpful if the domain name is always fixed and the XID is specified as an argument. Some examples include:

• select shipment_gid from shipment where shipment_gid = 'GUEST.' || '{% Xid%}'
• select shipment_gid from shipment where shipment_gid = 'GUEST.{% Xid%}'
• select shipment_gid from shipment where shipment_gid = 'GUEST' || '.{% Xid%}'
• select shipment_gid from shipment where shipment_gid = 'GUEST' || '.' || '{% Xid%}'

Create

To create a new integration saved query:

1. Enter a unique Integration Saved Query ID.
2. Enter a Query Name.
3. Enter the appropriate SQL Find.
   
   See above description for further information about SQL statements.
4. Select a Domain Name, or accept the current domain.
5. Click Finished.

Public Queries

Public integration saved queries (names shown below) are shipped with Oracle Transportation Management for you to modify as needed. Note that integration code searches the database for the specific names shown below. Therefore, when you create or modify an integration saved query, use the names as they are shown below.

Interface/Record	Primary Name	Secondary Name
Invoice	INT_INVOICE_GID_EXAMPLE_1	INT_INVOICE_GID_EXAMPLE_2
Location	INT_LOCATION_GID_1	INT_LOCATION_GID_2
Shipment	INT_SHIPMENT_GID_1	INT_SHIPMENT_GID_2
ShipmentStatus (for Shipment)	INT_SHIPMENT_STATUS_GID_1	INT_SHIPMENT_STATUS_GID_2
ShipmentStatus (for Shipment Group)	INT_SHIPMENT_GROUP_STATUS_GID_1	INT_SHIPMENT_GROUP_STATUS_GID_2
SShipUnit	INT_S_SHIP_UNIT_GID_EXAMPLE_1	INT_S_SHIP_UNIT_GID_EXAMPLE_2
TransOrder	INT_TRANS_ORDER_GID_1	INT_TRANS_ORDER_GID_2
TransOrderLine	INT_TRANS_ORDER_LINE_GID_1	INT_TRANS_ORDER_LINE_GID_2