Sterling OMS How Template Customization improves performance

By | 06/09/2018

Sterling OMS How Template Customization improves performance ?

Dear Readers,

Thanks lot for your support and good response. Please read our post and if you like share with your friends. Lets  learn together. Share your feedback and comments. Thanks

Sterling OMS How Template Customization improves performance

Sterling OMS How Template Customization improves performance

In this session we are going to see how template customization can improve the OMS system performance. Before reading this session please read Sterling Template Customization

As per our previous post if we don’t pass any template while calling API default template gets applied from system. But default template has lot fields part of XML. Which means to create the default output lot of database called been made. Over the time which creates performance related issues.

Lets do analysis using simple API called getOrderList

API Name Input XML Template XML Output XML
getOrderList <Order OrderNo=”Y100000200″ /> None <OrderList LastOrderHeaderKey=”2018060420083884271″ LastRecordSet=”Y” ReadFromHistory=”” TotalOrderList=”1″>
<Order AuthorizationExpirationDate=”2018-06-04T20:08:42-04:00″ CarrierAccountNo=”” CarrierServiceCode=”” ChargeActualFreightFlag=”N” CustCustPONo=”J0380257″ CustomerEMailID=”” CustomerPONo=”J0380257″ DeliveryCode=”” Division=”” DocumentType=”0001″ DraftOrderFlag=”N” EnteredBy=”EDI” EnterpriseCode=”Matrix-R” EntryType=”” FreightTerms=”3rd Party” HoldFlag=”N” HoldReasonCode=”” MaxOrderStatus=”1100″ MaxOrderStatusDesc=”Created” MinOrderStatus=”1100″ MinOrderStatusDesc=”Created” NotifyAfterShipmentFlag=”N” OrderDate=”2018-06-04T20:08:39-04:00″ OrderHeaderKey=”2018060420083884271″ OrderName=”Demo10082017_01″ OrderNo=”Y100000200″ OrderType=”” OriginalTax=”0.00″ OriginalTotalAmount=”0.00″ OtherCharges=”0.00″ PaymentStatus=”AUTHORIZED” PersonalizeCode=”” PriorityCode=”” PriorityNumber=”0″ Purpose=”” SCAC=”” ScacAndService=”” ScacAndServiceKey=”” SearchCriteria1=”” SearchCriteria2=”” SellerOrganizationCode=”Matrix-R” Status=”Created” TaxExemptFlag=”N” TaxExemptionCertificate=”” TaxJurisdiction=”” TaxPayerId=”” TermsCode=”” TotalAdjustmentAmount=”0.00″ isHistory=”N”>
<PriceInfo Currency=”USD” EnterpriseCurrency=”USD” ReportingConversionDate=”2018-06-04T20:08:44-04:00″ ReportingConversionRate=”1.00″ TotalAmount=”0.00″/>
<OverallTotals GrandCharges=”0.00″ GrandDiscount=”0.00″ GrandTax=”0.00″ GrandTotal=”0.00″ HdrCharges=”0.00″ HdrDiscount=”0.00″ HdrTax=”0.00″ HdrTotal=”0.00″ LineSubTotal=”0.00″/>
<InvoicedTotals GrandCharges=”0.00″ GrandDiscount=”0.00″ GrandTax=”0.00″ GrandTotal=”0.00″ HdrCharges=”0.00″ HdrDiscount=”0.00″ HdrTax=”0.00″ HdrTotal=”0.00″ LineSubTotal=”0.00″/>
<RemainingTotals GrandCharges=”0.00″ GrandDiscount=”0.00″ GrandTax=”0.00″ GrandTotal=”0.00″ HdrCharges=”0.00″ HdrDiscount=”0.00″ HdrTax=”0.00″ HdrTotal=”0.00″ LineSubTotal=”0.00″/>
</Order>
</OrderList>

Enabled SQL DEBUG for getOrderList API. See how to enable log and perform debugging

  • Prepared Statement – SELECT /*YANTRA*/ YFS_ORDER_HEADER.* FROM YFS_ORDER_HEADER YFS_ORDER_HEADER WHERE ( ( YFS_ORDER_HEADER.ORDER_NO = ‘Y100000200’ ) ) ORDER BY ORDER_HEADER_KEY
  • Prepared Statement – SELECT /*YANTRA*/ YFS_ORDER_RELEASE_STATUS.* FROM YFS_ORDER_RELEASE_STATUS YFS_ORDER_RELEASE_STATUS WHERE STATUS_QUANTITY > 0 AND ORDER_HEADER_KEY = ‘2018060420083884271’ ORDER BY ORDER_HEADER_KEY, ORDER_LINE_KEY, ORDER_RELEASE_KEY, STATUS, ORDER_LINE_SCHEDULE_KEY
  • Prepared Statement – SELECT /*YANTRA*/ YFS_HEADER_CHARGES.* FROM YFS_HEADER_CHARGES YFS_HEADER_CHARGES WHERE YFS_HEADER_CHARGES.HEADER_KEY= ‘2018060420083884271’ AND YFS_HEADER_CHARGES.RECORD_TYPE= ‘ORD’
  • Prepared Statement – SELECT /*YANTRA*/ YFS_TAX_BREAKUP.* FROM YFS_TAX_BREAKUP YFS_TAX_BREAKUP WHERE YFS_TAX_BREAKUP.HEADER_KEY= ‘2018060420083884271’ AND YFS_TAX_BREAKUP.RECORD_TYPE= ‘ORD’ AND YFS_TAX_BREAKUP.LINE_KEY=’ ‘
  • Prepared Statement – SELECT /*YANTRA*/ YFS_ORDER_LINE.* FROM YFS_ORDER_LINE YFS_ORDER_LINE WHERE YFS_ORDER_LINE.ORDER_HEADER_KEY= ‘2018060420083884271’
  • Prepared Statement – SELECT /*YANTRA*/ YFS_LINE_CHARGES.* FROM YFS_LINE_CHARGES YFS_LINE_CHARGES WHERE HEADER_KEY = ‘2018060420083884271’ AND RECORD_TYPE = ‘ORD’
  • Prepared Statement – SELECT /*YANTRA*/ YFS_TAX_BREAKUP.* FROM YFS_TAX_BREAKUP YFS_TAX_BREAKUP WHERE HEADER_KEY = ‘2018060420083884271’ AND RECORD_TYPE = ‘ORD’
  • Prepared Statement – SELECT /*YANTRA*/ YFS_ORDER_LINE_OPTION.* FROM YFS_ORDER_LINE_OPTION YFS_ORDER_LINE_OPTION WHERE ORDER_HEADER_KEY = ‘2018060420083884271’
  •  Prepared Statement – SELECT /*YANTRA*/ YFS_ADDNL_LINE_PRICE.* FROM YFS_ADDNL_LINE_PRICE YFS_ADDNL_LINE_PRICE WHERE ORDER_HEADER_KEY = ‘2018060420083884271’
  • SELECT /*YANTRA*/ YFS_BASE_RULES.* FROM YFS_BASE_RULES YFS_BASE_RULES WHERE RULE_NAME = ‘PRICING_ROUNDING_TYPE_TOTAL’
  •  SELECT /*YANTRA*/ YFS_BASE_CONFIG_GRP_DEFN.* FROM YFS_BASE_CONFIG_GRP_DEFN YFS_BASE_CONFIG_GRP_DEFN WHERE GROUP_NAME=’Pricing’
  •  Prepared Statement – SELECT /*YANTRA*/ YFS_ORGANIZATION.* FROM YFS_ORGANIZATION YFS_ORGANIZATION WHERE (YFS_ORGANIZATION.ORGANIZATION_KEY = ‘Matrix-R’)
  • Prepared Statement – SELECT /*YANTRA*/ YFS_ORGANIZATION.* FROM YFS_ORGANIZATION YFS_ORGANIZATION WHERE (YFS_ORGANIZATION.ORGANIZATION_KEY = ‘Matrix’)
  • SELECT /*YANTRA*/ YFS_RULES.* FROM YFS_RULES YFS_RULES WHERE RULE_SET_FIELD_NAME = ‘PRICING_ROUNDING_TYPE_TOTAL’ AND ( ORGANIZATION_CODE = ‘Matrix’ )
  • SELECT /*YANTRA*/ YFS_BASE_RULES.* FROM YFS_BASE_RULES YFS_BASE_RULES WHERE RULE_NAME = ‘ALLOW_REPRICE_FOR_RETURN’
  • SELECT /*YANTRA*/ YFS_RULES.* FROM YFS_RULES YFS_RULES WHERE RULE_SET_FIELD_NAME = ‘ALLOW_REPRICE_FOR_RETURN’ AND ( ORGANIZATION_CODE = ‘Matrix’ )
  • Prepared Statement – SELECT /*YANTRA*/ YFS_USER_EXIT.* FROM YFS_USER_EXIT YFS_USER_EXIT WHERE PROPERTY_NAME = ‘yfs.ue.convertCurrency.class’
  • Prepared Statement – SELECT /*YANTRA*/ YFS_USER_EXIT_IMPL.* FROM YFS_USER_EXIT_IMPL YFS_USER_EXIT_IMPL WHERE USER_EXIT_KEY = ‘YFSUE00008’ ORDER BY ORG_KEY DESC,DOCUMENT_TYPE_KEY DESC

So we could see many queries been fired to create default getOrderList output. Now lets add simple template and test using API tester.

API Name Input XML Template XML Output XML
getOrderList <Order OrderNo=”Y100000200″ /> <OrderList>
<Order/>
</OrderList>
<OrderList LastOrderHeaderKey=”2018060420083884271″ LastRecordSet=”Y” ReadFromHistory=”” TotalOrderList=”1″>
<Order ActualPricingDate=”2018-06-09T23:30:55-04:00″ AdjustmentInvoicePending=”N” AllAddressesVerified=”N” AuthorizationExpirationDate=”2018-06-04T20:08:42-04:00″ BillToKey=”2018051300101212723″ CarrierAccountNo=”” CarrierServiceCode=”” ChainType=”” ChargeActualFreightFlag=”N” ComplimentaryGiftBoxQty=”0″ CreatedAtNode=”” Createprogid=”CreateOrderServer” Createts=”2018-06-04T20:08:44-04:00″ Createuserid=”CreateOrderServer” CustCustPONo=”J0380257″ CustomerAge=”0.00″ CustomerEMailID=”” CustomerFirstName=”xxx” CustomerLastName=”YYYY” CustomerPONo=”J0380257″ CustomerPhoneNo=”999-999-999″ CustomerZipCode=”63045″ DeliveryCode=”” Division=”” DoNotConsolidate=”N” DocumentType=”0001″ DraftOrderFlag=”N” EnteredBy=”EDI” EnterpriseCode=”Matrix-R” EntryType=”” FreightTerms=”3rd Party” HasDerivedChild=”” HasDerivedParent=”” HoldFlag=”N” HoldReasonCode=”” InternalApp=”” InvoiceComplete=”N” Lockid=”2″ Modifyprogid=”Console” Modifyts=”2018-06-04T20:15:39-04:00″ Modifyuserid=”admin” NextAlertTs=”2018-06-04T20:15:39-04:00″ NoOfAuthStrikes=”0″ NotifyAfterShipmentFlag=”N” OrderComplete=”N” OrderDate=”2018-06-04T20:08:39-04:00″ OrderHeaderKey=”2018060420083884271″ OrderName=”Demo10082017_01″ OrderNo=”Y100000200″ OrderType=”” OriginalTax=”0.00″ OriginalTotalAmount=”0.00″ OtherCharges=”0.00″ Override=”” PaymentRuleId=”Matrix-R_DEFAULT” PaymentStatus=”AUTHORIZED” PendingTransferIn=”0.00″ PersonalizeCode=”” PriceOrder=”” PriceProgramName=”” PriorityCode=”” PriorityNumber=”0″ PropagateCancellations=”” Purpose=”” ReserveInventoryFlag=”” ReturnByGiftRecipient=”” SCAC=”” SaleVoided=”N” SearchCriteria1=”” SearchCriteria2=”” SellerOrganizationCode=”Matrix-R” ShipNode=”Matrix_WH1″ ShipToKey=”2018051300101212722″ SourceIPAddress=” ” TaxExemptFlag=”N” TaxExemptionCertificate=”” TaxJurisdiction=”” TaxPayerId=”” TermsCode=”” TotalAdjustmentAmount=”0.00″ isHistory=”N”/>
</OrderList

Lets collect the SQL statements been fired after passing template

  • Prepared Statement – SELECT /*YANTRA*/ YFS_ORDER_HEADER.* FROM YFS_ORDER_HEADER YFS_ORDER_HEADER WHERE ( ( YFS_ORDER_HEADER.ORDER_NO = ‘Y100000200’ ) ) ORDER BY ORDER_HEADER_KEY
  • Prepared Statement – SELECT /*YANTRA*/ YFS_ORDER_RELEASE_STATUS.* FROM YFS_ORDER_RELEASE_STATUS YFS_ORDER_RELEASE_STATUS WHERE STATUS_QUANTITY > 0 AND ORDER_HEADER_KEY = ‘2018060420083884271’ ORDER BY ORDER_HEADER_KEY, ORDER_LINE_KEY, ORDER_RELEASE_KEY, STATUS, ORDER_LINE_SCHEDULE_KEY
  • Prepared Statement – SELECT /*YANTRA*/ YFS_ORDER_DATE.* FROM YFS_ORDER_DATE YFS_ORDER_DATE WHERE YFS_ORDER_DATE.ORDER_HEADER_KEY= ‘2018060420083884271’ AND YFS_ORDER_DATE.ORDER_LINE_KEY= ‘ ‘ AND YFS_ORDER_DATE.ORDER_RELEASE_KEY= ‘ ‘
  • SELECT /*YANTRA*/ YFS_BASE_RULES.* FROM YFS_BASE_RULES YFS_BASE_RULES WHERE RULE_NAME = ‘DATE_TO_USE_FOR_PRICING’
  • SELECT /*YANTRA*/ YFS_RULES.* FROM YFS_RULES YFS_RULES WHERE RULE_SET_FIELD_NAME = ‘DATE_TO_USE_FOR_PRICING’ AND DOCUMENT_TYPE = ‘0001’ AND ( ORGANIZATION_CODE = ‘Matrix-R’ )

Now you can see only 3 tables related to order been called to create the XML output.

Conclusion : We should have template defined for each API call. Format the template as per customer requirement. Don’t add elements to template which is not required.

Question Time:

  1. In order detail screen order status derived from which database column? Answer: Order Header table does not have status column. So status column been calculated based on status column of YFS_ORDER_RELEASE_STATUS table
  2. If order has more lines how the order status been derived ? Answer : YFS_ORDER_RELEASE_STATUS table status column will be considered with Max status with word Partially
  3. Which table allows to store custom dates related to order ? Answer : YFS_ORDER_DATE

If you like our post please share with your friends. Happy Learning !!!

You can register with us to get more OMS learning updates

OMS Interview Questions

Leave a Reply

Your email address will not be published. Required fields are marked *