Query

The query operation retrieves entities from the application by a list of filtering criteria. The amount of information included in the returned data can be controlled by setting flags on the request message. For performance reasons, it is best practice to retrieve as little information as required as part of a query operation invocation.

If you want to retrieve an item using the primary key, the Get operation should be used as it is optimized for this particular type of query. The Query operation exhibits longer latency than the Get operation, so the Get operation should be used whenever possible.

This operation supports pagination and sorting. Pagination can be used to retrieve subsets of matched data in a paged format. Specifying the count restricts the number of returned records. Specifying the start and end record restricts the records to those items in the list.

Sorting is used to order the list of returned items. A multi-level sort can be specified from a list of pre-defined fields in either an ascending or descending order.

Pagination does not create a recordset on the server. In order to page through the data effectively, it is necessary to invoke the operation using the same filtering criteria and sort each time to get consistent results. Even then the results are not guaranteed as new records may be added to the system in at the same time.

Users must have one of the following permissions for the respective action (query item or party) to call this operation:
  • PERM_READ_ITEM
  • PERM_READ_PARTY
The following table describes performance implications of using this operation:
Activity Impact Description
Querying using case insensitive Low Case insensitive queries append LOWER function to the SQL statement.
Querying with parent party filter High The parent party filter appends a sub-SELECT condition to the WHERE clause of the SQL statement.
Including parent party items Low Including parent party items is only relevant if querying using a parent party filter. Setting this flag has a low incremental impact over the high impact of filtering by parent party. Using this may slightly reduce the size of the returned resultset.
Querying with characteristic filters High Characteristic filters append a sub-SELECT condition to the WHERE clause of the SQL statement.
Querying with order filter High Order filters append a sub-SELECT condition to the WHERE clause of the SQL statement.
Querying with relationship filters High Relationship filters append a sub-SELECT condition to the WHERE clause of the SQL statement.
Including party name High Including party names requires a query against the database.
Including parent party High Including parent parties requires a query against the database.
Including parent party name Low Including parent party is only relevant if parent parties are included. Setting this flag has a low incremental impact over the high impact of including parent party because the names are retrieved at the same time during the same query.
Including characteristics Low Including characteristics increases the size of the response message.
Including characteristic names Low Including characteristics increases the size of the response message.
Including orders Low Including orders increases the size of the response message.
Including orderIds Low Including orders increases the size of the response message.
Including orderRefs Low Including orders increases the size of the response message.
Including relationships Low Including relationships increases the size of the response message.
Including relationship types Low Including relationships increases the size of the response message.
Including reverse relationships High Including reverse relationships requires a query against the backing store database.
Returning deleted items Low Returning deleted items increases the number of records in the response message.
Using pagination Low Pagination queries surround the SQL will another SELECT statement based on row number.
Sorting by multiple fields Low Each sort field is added to the SQL as part of the ORDER BY clause.

Query Request

This message is sent by the client application to the TIBCO Fulfillment Subscriber Inventory system.

Query Item Request Payload

Query Party Request Payload
Query Request Body Details for Item and Party
Element Type Cardinality Description
filter ItemFilterType

or

PartyFilterType

Mandatory This applies filters to the entity set to narrow the results. All filtering elements are ANDed together to construct the query.
include ItemIncludeType

or

PartyIncludeType

Optional This represents the fields to return for each matching entity.
include/characteristicName String Optional This is the characteristic name to include in the query.
include/includeCharacteristics Boolean Optional This indicates if characteristics should be returned.
include/includeParentParty Boolean Optional This indicates if the parent party should be returned.
pagination PaginationRequestType Optional This is the pagination request.
pagination/count long Optional This is how many records should be returned.
pagination/startRecord long Optional This defines the index of the record to return first.
sort FieldSortType Optional This is for sorting common fields. FieldName accepts a string with the name of the field to sort by.
The following are the common fields:
  • id
  • ref
  • name
  • type
  • subtype
  • status
  • owneBy
  • createBy
  • lastUpdatedBy

Party fields: common + parentPartyId

Item fields: common + productId, productVersion.

sort/descending Boolean Optional This indicates if the sort of common fields is ascending or descending.
sort/fieldName ItemField Optional This is the field name to query for.
filter/id String Optional This is the ID filter for the query.
filter/ref CaseString Optional This is the reference filter for the query.
filter/name CaseString Optional This is the entity name filter for the query.
filter/type CaseString Optional This is the entity type filter for the query.
filter/subType CaseString Optional This is the entity sub type filter for the query.
filter/status String Optional This is the status filter for the query.
filter/characteristic CharacteristicSearchType Optional This is the entity characteristic filter for the query. See the "Characteristic" section in Query Sub Elements for more details on the sub elements of this element.
filter/characteristic/name String Optional This is the characteristic name filter for the query.
filter/ownedBy String Optional

This is the owner filter for the query.

filter/createdBy String Optional This is the creator filter for the query.
filter/createdDate DateQueryType Optional This is the creation date filter for the query. See the "DateQueryType Filter" section in Query Sub Elements for more details on the sub elements of this element.
filter/lastUpdatedBy String Optional This is the last user to make an update filter for the query.
filter/lastUpdatedDate DateQueryType Optional This is the last updated date filter for the query. See the "DateQueryType Filter" section in Query Sub Elements for more details on the sub elements of this element.
filter/batchId String Optional This is the batch ID filter for the query.
filter/locked Boolean Optional This indicates if locked entities are returned.
filter/deleted Boolean Optional

If this element is true and an item has been logically deleted, it will be returned.

If this element is false or omitted and an item has been logically deleted, it will not be returned.

If an item has been physically deleted using the Purge operation, it will not be returned.

Query Request Body Details for Item Only
Element Type Cardinality Description
filter/productId CaseString Optional This is the product ID filter for the query.
filter/productVersion CaseString Optional This is the product version filter for the query.
filter/partyId String Optional This is the party ID filter for the query.
filter/order OrderType Optional This is the order filter for the query. See the "Order" section in Query Sub Elements for more details on the sub elements of this element.
filter/relationship RelationshipType Optional This is the relationship filter for the query. See the "Relationship" section in Query Sub Elements for more details on the sub elements of this element.
filter/startDate DateQueryType Optional This is the start date filter for the query. See the "DateQueryType" section in Query Sub Elements for more details on the sub elements of this element.
filter/endDate DateQueryType Optional This is the end date filter for the query. See the "DateQueryType" section in Query Sub Elements for more details on the sub elements of this element.
filter/order/orderId String Optional This is the order ID filter for the query.
filter/order/orderRef String Optional This is the order reference filter for the query.
filter/relationship/type String Optional This is the relationship type filter for the query.
filter/relationshipId String Optional This is the relationship ID filter for the query.
filter/startDate/afterDate dateTime Optional This is the date after the start date filter for the query.
filter/startDate/beforeDate dateTime Optional This is the date before the start date filter for the query.
filter/endDate/afterDate dateTime Optional This is the date after the end date filter for the query.
filter/endDate/beforeDate dateTime Optional This is the date before the end date filter for the query.
include/includePartyName Boolean Optional This indicates if the party name is returned.
include/includeParentPartyName Boolean Optional This indicates if the parent party name is returned.
include/includeOrders Boolean Optional This indicates if orders associated with the item are returned.
include/orderId String Optional This is the order ID to include in the query.
include/orderRef String Optional This is the order reference to include in the query.
include/includeRelationships Boolean Optional This indicates if relationships should be returned.
include/relationshipType String Optional This is the relationship type to include in the query.
include/includeReverseRelationships Boolean Optional This indicates if reverse relationships should be returned.
Query Request Body Details for Party Only
Element Type Cardinality Description
include/includeChildParties Boolean Optional This indicates if child parties should be returned.
include/includeChildPartyNames Boolean Optional This indicates if child party names should be returned.
filter/characteristic/value CaseString Optional This is the characteristic value filter for the query.
filter/parentPartyId String Optional This is the parent party ID filter for the query.

Query Response

This message is sent by the TIBCO Fulfillment Subscriber Inventory system to the requesting application to return the results of the operation invocation.

Query Item Response Payload
Query Party Response Payload
Query Response Body Details for Item and Party
Element Type Cardinality Description
resultStatus ResultStatusType Mandatory This is the result status type and the operation invocation status of the query.
pagination PaginationResponseType Optional This is the pagination information response.
pagination/startRecord long Optional This defines the first record of the paginated response.
pagination/endRecord long Optional This defines the last record of the paginated response.
pagination/totalRecords long Optional This is the total number of matching records.
entity ItemType

or

PartyType

Mandatory (0..N) This is the matching entities returned by the query.
entity/ref String Optional This is the reference of the retrieved entity.
entity/name String Optional This is the name of the retrieved entity.
entity/type String Optional This is the type of the retrieved entity.
entity/subType String Optional This the sub type of the retrieved.
entity/status String Optional This is the status of the retrieved entity.
entity/id String Optional This is the unique identifier for the retrieved entity.
entity/characteristic CharacteristicType Optional This is the characteristics of the retrieved entity, such as ID, name, and value.
entity/ownedBy String Optional This defines who owns the retrieved entity.
entity/createdBy String Optional This defines who created the retrieved entity.
entity/createdDate dateTime Optional This defines the date the retrieved entity was created.
entity/lastUpdatedBy String Optional This defines who last updated the retrieved entity.
entity/lastUpdatedDate String Optional This defines date the retrieved entity was updated.
entity/version long Optional This defines the version of the retrieved entity.
entity/batchId String Optional This is the associated batch ID of the retrieved entity.
entity/lock LockInformation Optional This is the lock information associated with the retrieved entity. See the "Lock Information" section in Get Sub Elements for more details on the sub elements of this element.
entity/deleted Boolean Optional This indicates if the retrieved entity is logically deleted.
entity/parentParty PartyReference Optional This defines the parent party of the retrieved entity.
Query Response Body Details for Item Only
Element Type Cardinality Description
entity/productId String Optional This is the ID of the product represented by the retrieved item.
entity/productVersion String Optional This is the product version number associated with the retrieved item.
entity/party PartyReference Optional This is the party associated with the retrieved item.
entity/order OrderType Optional This is the order associated with the retrieved item.
entity/relationship RetrievedRelationshipType Optional This is the relationship type associated with the retrieved item.
entity/startDate dateTime Optional This is the start date of the retrieved item.
entity/endDate dateTime Optional This is the end of the retrieved item.
Query Response Body Details for Party Only
Element Type Cardinality Description
entity/childParty PartyReference Optional This is the child party reference of the retrieved party.