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.

Note: The 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.