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)

 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>

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