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. Click the Presets view.

2. Click the System Setup link.

3. Click the Message Template SQL link.
The Message Template SQL page is displayed.

4. Click Add New.

5. 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 uStore Back Office Application. 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>

<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>

<Row>

</Row>

</CustomerName>

<Row>

<StoreName>MyStore</StoreName>

<LandingDomain>www.xmpie.com</LandingDomain>

<LandingFolder>MyStore</LandingFolder>

</Row>

</StoreName>

<Order>

<Row>

</Row>

</Order>

</Sqls>