COMPLEX QUERY

INTRODUCTION

A complex query in the IBM Sterling Order Management System retrieves and processes multiple layers of data related to orders, customers, products, inventory levels, and fulfillment processes. It employs various filters to obtain the necessary results based on criteria such as order status (e.g., pending or shipped), customer attributes, product availability, and date ranges. For huge datasets handling, complex queries are using sorting and pagination to enhance user-friendliness.

Search criteria for order console, inventory console are the best example for complex query.

LIMITATIONS FOR COMPLEX QUERY

• Single element cannot support multiple complex query elements within it.
• Complex query does not fired against different tables.
• Attribute with no value does not supported by the complex query. Example : AttributeName=””
• Attributes are appended with the below following query type table and it is case sensitive.
• Only one element (And or Or) will be under the complex query element. Also, And or Or elements may have multiple And or Or elements as their child elements.

EXTENDED COLUMN IN COMPLEX QUERY

• Complex queries allow you to pass an extended column as an additional filter to retrieve the list of data.
• Identifier for the extended column :  ‘Extn_’ prefix is added to the attribute name.

SAMPLE

<Exp Name=”Extn_AttributeName” Value=” ” QryType=” “/>

WORKING SAMPLES :

1.  SPECIFIC RANGES

SCENARIO 1:

Fetch the list of order line details which meet the following conditions

1. date range for order creation date in order level
2. Status should present between ‘Created’ and ‘Released’
3. Ordered quantity is not equals to zero

API Name :  getOrderLineList

<OrderLine StatusQryType=”BETWEEN” FromStatus=”1100″ ToStatus=”1500″ OrderedQty=”0″ OrderedQtyQryType=”NE”>     <Order DocumentType=”0001″ EnterpriseCode=”SRK_Retail” OrderDateQryType=”BETWEEN” FromOrderDate=”2023-07-14″ ToOrderDate=”2024-07-14″/> </OrderLine>

Output

SCENARIO 2 :

Fetch the Shipment details which meet the following criterias

1. Find the shipments which is in shipment created status
2. Shipment should created between Oct, 2024 to Nov, 2024

API Name : getShipmentList

<Shipment ShipmentKeyQryType=”BETWEEN” FromShipmentKey=”202410″ ToShipmentKey=”202411″ EnterpriseCode=”SRK_Retail” Status=”1100″ />

Output

SCENARIO 3 :

Fetch the exception details which meet the following criterias

1. Find the exception which is open and work in progress status
2. Exception should created between Oct, 2024 to Nov, 2024

API Name : getExceptionList

<Inbox InboxKeyQryType=“BETWEEN” FromInboxKey=“202410” ToInboxKey=“202411”>      <ComplexQuery Operator=“OR”>         <Or>             <Exp Name=“Status” Value=“OPEN” QryType=“EQ”/>             <Exp Name=“Status” Value=“WIP” QryType=“EQ”/>         </Or>     </ComplexQuery> </Inbox>

Output

2.  COMPARATIVE RANGES

Scenario 1:

Fetch the order details which meet the following criterias

1. Find the latest orders
2. Order should created after Oct 01, 2024
3. Ship date of the order should be greater than or equal to Oct 10, 2024
4. Delivery date of the order should be less than Nov 01, 2024.

API Name : getOrderList API

<Order DocumentType=“0001” EnterpriseCode=“SRK_Retail”>     <OrderBy>         <Attribute Name=“OrderHeaderKey” Desc=“Y”/>     </OrderBy>     <ComplexQuery Operator=“AND”>         <And>             <Exp Name=“OrderDate” Value=“20241001” QryType=“GT”/>             <Exp Name=“ReqShipDate” Value=“20241010” QryType=“GE”/>             <Exp Name=“ReqDeliveryDate” Value=“20241101” QryType=“LT”/>         </And>     </ComplexQuery> </Order>

Output

SCENARIO 2 :

Fetch the reservation details based on the expiration date

API Name: getInventoryReservationList

<Shipment ShipmentKeyQryType=”BETWEEN” FromShipmentKey=”202410″ ToShipmentKey=”202411″ EnterpriseCode=”SRK_Retail” Status=”1100″/>

Output

3.  EQUAL, NOT EQUAL, LIKE OPERATORS

SCENARIO 1 :

Fetch the error list, which meet the following criteria

1. State should be in Initial, not in fixed, Ignored.
2. Error Code should contains Class not found exception.
3. ErrorTxnId should starts with specific string.

API Name : getIntegrationErrorList

<IntegrationError>     <ComplexQuery Operator=”And”>         <And>             <Exp Name=”State” Value=”Initial” QryType=”EQ”/>             <Exp Name=”State” Value=”Fixed” QryType=”NE”/>             <Exp Name=”State” Value=”Ignored” QryType=”NE”/>             <Exp Name=”ErrorCode” Value=”ClassNotFoundException” QryType=”LIKE”/>             <Exp Name=”ErrorTxnId” Value=”2024082221″ QryType=”FLIKE”/>         </And>     </ComplexQuery> </IntegrationError>

Output :

SCENARIO 2 :

Fetch the node list which allows the procurement

API Name : getOrganizationList

<Organization IsNode=”Y”>    <Node>        <ComplexQuery Operator=”AND”>             <And>                <Exp Name=”NodeType” Value=”Store” QryType=”EQ”/>                 <Exp Name=”ProcureToShipAllowed” Value=”Y” QryType=”EQ”/>                 <Exp Name=”ProcureToShipAllowed” QryType=”NOTNULL”/>             </And>          </ComplexQuery>   </Node> </Organization>

Output

SCENARIO 3 :

Get the list of item details which meet the following conditions

1. Item has short description as electronics
   1. Status value should not be in 2000
   1. UseUnplannedInv  attribute value is equal to N
   1. IsItemBasedAllocation value is not null

API Name: getItemList

<Item >             <PrimaryInformation/>             <ComplexQuery Operator=“AND”>                         <And>                                     <Exp Name=“ShortDescription” Value=“Electronics” QryType=“EQ”/>                                     <Exp Name=“Status” Value=“2000” QryType=“NE”/>                                     <Exp Name=“IsItemBasedAllocationAllowed” QryType=“NOTNULL”/>                         </And>             </ComplexQuery> </Item>

Output

4.  NULL OPERATOR

SCENARIO 1 :

Input to fetch sales order details

API Name : getOrderList

<Order DocumentType=”0001″ EnterpriseCode=”SRK_Retail”>     <ComplexQuery Operator=”AND”>         <And>             <Exp Name=”OrderPurpose” QryType=”ISNULL”/>         </And>     </ComplexQuery> </Order>

Output

SCENARIO 2 :

Input to get exchange order details

API Name : getOrderList

<Order DocumentType=”0001″ EnterpriseCode=”SRK_Retail”>     <ComplexQuery Operator=”AND”>         <And>             <Exp Name=”OrderPurpose” QryType=”NOTNULL”/>             <Exp Name=”OrderPurpose” Value=”EXCHANGE” QryType=”EQ”/>         </And>     </ComplexQuery> </Order>

Output

SCENARIO 3 :

Fetch the Shipment details for latest shipments which have the order number within it.

API Name : getShipmentList

<Shipment ShipmentKeyQryType=”BETWEEN” FromShipmentKey=”202410″ ToShipmentKey=”202411″ EnterpriseCode=”SPICA_INDIA” Status=”1100″>     <ComplexQuery Operator=”AND”>         <And>             <Exp Name=”OrderNo” QryType=”ISNULL”/>         </And>     </ComplexQuery> </Shipment>

Output

5.  PAGINATE STRATEGY

SCENARIO 1 :

Fetch the sales order details with the paginate strategy to get results across multiple pages.

API Name : getOrderList

<Order DocumentType=“0001” EnterpriseCode=“SRK_Retail”>     <Paginate IgnoreOrdering=“Y” PageSize=“30” PaginationStrategy=“RESULTSET”/>     <ComplexQuery Operator=“AND”>         <And>             <Exp Name=“OrderPurpose” QryType=“ISNULL”/>         </And>     </ComplexQuery> </Order>

Output

Second page

Input

<Order DocumentType=”0001″ EnterpriseCode=”SRK_Retail”>     <Paginate IgnoreOrdering=”Y” PageNumber=”2″ PageSize=”30″ PaginationStrategy=”RESULTSET”/>     <ComplexQuery Operator=”AND”>         <And>             <Exp Name=”OrderPurpose” QryType=”ISNULL”/>         </And>     </ComplexQuery> </Order>

Output

COMPLEX QUERY SUPPORTED API’S

Complex query does not support all the API’s. Few API’s are listed out here that will be supported by this complex query.

• getOrderList
• getOrderLineList
• getShipmentList
• getOrganizationList
• getItemList
• getExceptionList
• getInventoryReservationList
• deletePriceListAssignmentList
• deletePricingRuleAssignmentList
• getAttributeAllowedValueList
• getclassificationPurposeList
• getCustomerContactList
• getSearchIndexTriggerList

COMPLEX QUERY TYPE DEPENDING ON THE DATA TYPE

Data Type	Query Values
Char/VarChar2	EQ – Equal to FLIKE – Starts with LIKE – Contains GT – Greater than LT – Less than NE – Not equal to
Number	BETWEEN – Range of values EQ – Equal to GE – Greater than or equal to GT – Greater than LE – Less than or equal to LT – Less than NE – Not equal to
Date	DATERANGE – Range of dates EQ – Equals GE – Greater than or equal to GT – Greater than LE – Less than or equal to LT – Less than NE – Not equal to
Null	ISNULL – Return records that are null. NOTNULL – Return records that are not null. Note: The above query types are used when the attribute declared as nullable in entity xml.

The information provided above offers a detailed explanation of complex queries.

#LetsTalkOMS #SpicaTech