Creating a New Message Template SQL

Note: Message template SQLs are not available for StoreFlow Cloud customers.

The SQL queries are used to retrieve data for the message.

SQL queries are used to generate XML code that is, in turn, used by the XSL to create a message in HTML format.

To create a new message template SQL:

  1. On the toolbar click Presets, and then select System Setup..

  2. Click the Message Template SQL link.

    The Message Template SQL page is displayed.

  3. Click Add New.

  4. Fill the following fields:

    • Message Template Id: ID number of the message template with which this SQL is associated.

    • SQL Text: SQL statement. The SQL may use parameters as specified in the section below.

    • Name: A unique name for the record set that will be fetched.

SQL examples

exec ('SELECT FirstName, LastName FROM Users WHERE Users.UserID = ' + @UserId)

exec('SELECT Store.[Name] as StoreName, LandingDomain, LandingFolder FROM Store WHERE StoreId = ' + @StoreId)

exec('SELECT EncryptedOrderId FROM Orders WHERE OrderId = ' + @OrderId)

Note: Other SQL statements samples can be previewed in the backoffice . Go to uStore Presets > System Setup > Message Template SQL and click the View link.

The parameters that are passed to the SQL statement by event points are:

Event Type

Event

Parameters

User Life Cycle Events

Customer Login

@StoreId

@CultureId

@UserId

@ExternalUserID

@UserEmail

Customer Registration

@StoreId

@CultureId

@UserId

@ExternalUserID

@UserEmail

Customer Pending Registration

@StoreId

@CultureId

@UserId

@ExternalUserID

@UserEmail

Password Recovery

@StoreId

@CultureId

@UserId

@ExternalUserID

@UserEmail

User Creation in Admin Application

@StoreId

@CultureId

@UserId

@ExternalUserID

@UserEmail

User Details Update In Admin Application

@StoreId

@CultureId

@UserId

@ExternalUserID

@UserEmail

User Details Update in Storefront

@StoreId

@CultureId

@UserId

@ExternalUserID

@UserEmail

Order Life Cycle Events

Delivery Creation

@StoreId

@CultureId

@OrderId

@UserId

@DeliveryId

Order Product Enters Shopping Cart

@StoreId

@CultureId

@OrderId

@UserId

@OrderProductId

Order State Transition

@StoreId

@CultureId

@OrderProductId,

@FromState

@Tostate

Order Submission in Customer Application

@StoreId

@CultureId

@UserId

@ApproverUserId (if applicable)

@OrderId

Reorder

@StoreId

@CultureId

@ OrderProductId

@OldOrderProductId

Order Approval Process Events

Approver Change - Notification to New Approver

@StoreId

@CultureId

@OrderId

@ApproverUserId (of new approver)

Approver Change - Notification to Old Approver

@StoreId

@CultureId

@OrderId

@ApproverUserId (of old approver)

Order Approved

@StoreId

@CultureId

@OrderId @ApproverUserId

Order Rejection

@StoreId

@CultureId

@OrderId

@RejecterUserId

Order Sent To Approval

@StoreId

@CultureId

@OrderId

@UserId

@ApproverUserId (if specific approver was selected)

Unassign Group from Approval Process

@StoreId

@CultureId

@UserId

@ApproverUserId

@ApproveeGroupId

Inventory Events

Inventory Level Is Low

@StoreId

@CultureId

@OrderId

@ProductId

@OrderProductId

Inventory Changed

@StoreId

@CultureId

@OrderId

@ProductId

@OrderProductId

Other Events

Order Item Job Ticket on New Order Creation

@StoreId

@CultureId

@OrderId

@UserId

@OrderProductId

Redirect Clearing Failed

@StoreId

@CultureId

@OrderId

@UserId

USADATA Clearing Failed

@StoreId

@CultureId

@OrderId

The SQL queries you create will be formatted by uStore into XML that, in turn, will be transformed into the email message subject and body using XSLs.

The SQL is formatted as follows:

<sqls>
   <RecordSetName>
      <Row>
         <FieldName>value</FieldName>
      </Row>
   </RecordSetName>
</sqls>

Where:

  • RecordSetName is the name of the message template SQL (the number of RecordSetName nodes are identical to the number of message template SQLs).

  • Row encapsulates all fields of one record (the number of Row nodes is identical to the number of records that the SQL query returned)

  • FieldName is the name of the field that the SQL fetches (number of FieldName nodes is identical to the number of all fields that are declared in the SQL)

  • value is the value of this field.

Sample of the XML data retrieved from the database

<Sqls>
   <CustomerName>
      <Row>
         <FirstName>d</FirstName>
         <LastName>d</LastName>
      </Row>
   </CustomerName>
   <StoreName>
      <Row>
         <StoreName>MyStore</StoreName>
         <LandingDomain>www.xmpie.com</LandingDomain>
         <LandingFolder>MyStore</LandingFolder>
      </Row>
   </StoreName>
   <Order>
      <Row>
         <EncryptedOrderId>82104</EncryptedOrderId>
      </Row>
   </Order>
</Sqls>