Using Integration Saved Queries
You can select or identify objects to update or delete without using a GID by using a configurable matching integration saved query. Integration Saved Queries are defined in Power Data via the following menu: Business Process Automation > Power Data > Integration > Integration Saved Queries. The queries are written as SQL statements that contain references to the information in the incoming XML transmission. For example, a query for a shipment GID given a shipment reference number would be as follows (e.g. if query GID is "GUEST.TEST_SAVED_QUERY_001"):
select s.shipment_GID from shipment_refnum s where s.shipment_refnum_qual_GID = '{%QUAL%}' and s.shipment_refnum_value = '{%VALUE%}'
The inbound XML would then contain the following IntSavedQuery element to use the above query:
<IntSavedQuery>
<IntSavedQueryGid>
<Gid>
<DomainName>GUEST</DomainName>
<Xid>TEST_SAVED_QUERY_001</Xid>
</Gid>
</IntSavedQueryGid>
<IntSavedQueryArg>
<ArgName>QUAL</ArgName>
<ArgValue>ZZ</ArgValue>
</IntSavedQueryArg>
<IntSavedQueryArg>
<ArgName>VALUE</ArgName>
<ArgValue>MY_SHIP_REFNUM_001</ArgValue>
</IntSavedQueryArg>
<IsMultiMatch>Y</IsMultiMatch>
<NoDataFoundAction></NoDataFoundAction>
</IntSavedQuery>
During processing, Transportation and Global Trade Management Cloud would then replace
the '{%QUAL%}' with 'ZZ' and the '{%VALUE%}' with ' MY_SHIP_REFNUM_001' to search for
the shipment GID. And the transaction would be processed with using the shipment GID
returned from the query. Note that the IsMultiMatch
element can be
used to indicate if multiple GIDs can be returned from the query and used for
processing. Refer to the schema notes for additional fields that are available for the
query.
The queries can also be written using XPath expressions to search for values from specific elements in the XML. An example of a query that relies on XPath expressions is as follows:
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}'
Note that the preferred method is to use the ArgName and ArgValue as they perform much better than the XPath expressions.
If NoDataFoundAction
is not null and the Integration Saved Query returns
no value, then the transaction code for the XML is switched to the transaction code
specified by NoDataFoundAction
, which must be a valid transaction code.
NoDataFoundAction
tag does not work on
child elements. It is designed primarily for the root elements.Default Integration Saved Queries for Updates
There are a few interfaces that support default integration saved queries without having to specify the IntSavedQuery element in the inbound XML. This functionality is supported for Location, TransOrder, TransOrderLine, and Shipment.
The default integration saved queries are only used when the transaction code element has a value of U or D, and the primary GID for the interface is missing. If a TransOrderGid is missing, then the INT_TRANS_ORDER_GID_1 and INT_TRANS_ORDER_GID_2 saved queries are used. If a TransOrderLineGid is missing, then the INT_TRANS_ORDER_LINE_GID_1 and INT_TRANS_ORDER_LINE_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 defined queries must return a single GID of the element being referenced (for example, Order_Base_GID for TransOrder, Shipment_GID for Shipment, etc.). When a query returns multiple records, it will generate an error. Transportation and Global Trade Management Cloud supports up to two queries for each interface/record. If the first query generates an error or does not resolve to a single record, the second query will be applied. And if nothing is returned, then an error is generated.