GX:Event System
Contents
- 1 Introduction
- 2 Events
- 2.1 Context Fields (required)
- 2.2 Reserved Fields (optional)
- 2.3 Workstation (required)
- 2.4 Port (required)
- 2.5 Parameters (optional)
- 2.6 Query (optional)
- 2.7 Interval (optional)
- 2.8 Action (required)
- 2.9 ActionArgs (optional)
- 2.10 StartOn (required)
- 2.11 ExpiresOn (optional)
- 2.12 Active (required)
- 2.13 CatchUp
- 3 Event Templates
- 4 Event Bounds
- 5 Event History
- 6 Examples
Introduction
Events are maintained using the Events maintenance screens (Events, Event Templates, Event Bounds) located under the Whistle Office tab. Users must have SD EVENTS, SD EVENT BOUNDS, and SD EVENT TEMPLATES, respectively, to access these screens. There are three underlying tables corresponding to these maintenance screens: wmEvents, wmEventTemplates, and wmEventBounds.
Events
Events are stored in the wmEvents table. Each event has a name, a query, arguments to pass to that query, the date to begin, the date to end, the action to perform, and the arguments to pass to the action.
Context Fields (required)
Company, Warehouse, Plant, Branch, and ModUser indicate the context in which the event is run.
Reserved Fields (optional)
QueryType
Workstation (required)
This field contains the NetBios name of the machine hosting the Whistle Server that is to execute the event.
Port (required)
This is the port on which the executing Whistle Server receives requests. For now, this is always 8000. In the future, when multiple instances of Whistle Server are permitted to run on the same machine, this field will be used to differentiate between them.
Parameters (optional)
Configuration variables and values that are associated with all events may be specified here as a means of referring to them in the Query field. Configuration variables are supplied by using an “at” sign, i.e. @, followed by a period-delimited hierarchical expression for the configuration variable to use. For example, @QcVars.FtpServerTimeout. Event-associated values may also be supplied. They are @Now, @WhenScheduled, @EventId, @EventName, and @EndInterval.
| Argument | Description |
| @Now | current local time on the database server. |
| @WhenScheduled | the time the task was supposed to run, which could evaluate as any time before or at the current time, since previous attempts may have failed. |
| @EndInterval | evaluates to one second short of the next scheduled run of the current event. |
| @EventId | contents of wmEvents.Id for the associated event. |
| @EventName | contents of wmEvents.Name for the associated event. |
Query (optional)
This field contains the T-SQL to run when the event occurs. Any legal T-SQL code may run here with the caveat that statements must be delimited with a semicolon. If you wish the event to operate on values resulting from the T-SQL batch, you must end the batch with a query. For example, if you want the event to FTP a file somewhere, the Query field might contain:
SELECT
'ftp.coolearth.com' AS Address,
'someuser' AS [User],
'mypassword' AS Password,
AfterTransformation AS Contents,
Name + 'Extract' +
CONVERT(VARCHAR(11), wmTransformations_id) + '.csv' AS RemoteFileName,
'UPDATE wmTransformations SET Status = FTP WHERE
wmTransformations_id = ' +
CONVERT(VARCHAR(11), wmTransformations_id) AS SuccessSqlToExecute
FROM
wmTransformations T
INNER JOIN
(
SELECT GroupId, COUNT(*) AS Cnt
FROM wmTransformations
WHERE Status = 'New'
GROUP BY GroupId
HAVING COUNT(*) = 4
) G ON G.GroupId = T.GroupId
ORDER BY G.GroupId, wmTransformations_id
The event Action will be executed once for each row returned from the query, subject to the Event Bounds.
Interval (optional)
This field contains an integer corresponding to the following enumeration:
| Value | Description |
| 0 | Hourly |
| 1 | Every two hours (Hours2) |
| 2 | Every three hours (Hours3) |
| 3 | Every four hours (Hours4) |
| 4 | Every six hours (Hours6) |
| 5 | Every 12 hours (Hours12) |
| 6 | Daily |
| 7 | Weekly |
| 8 | Biweekly |
| 9 | Semimonthly |
| 10 | Monthly |
| 11 | Bimonthly |
| 12 | Quarterly |
| 13 | Semiannually |
| 14 | Annually |
| 15 | Biannually |
Action (required)
This field contains an integer corresponding to the following enumeration:
| Value | Description |
| 0 | Email (sends an email) |
| 1 | Ncr (currently unsupported – execution is undefined) |
| 2 | Sql (runs another SQL batch) |
| 3 | Ftp (sends a file via FTP to a remote site) |
ActionArgs (optional)
Contains a comma delimited list of arguments to pass to the action.
Email Action Required Arguments
| Argument | Description |
| @SubjectTemplate | mergable text blob into which every action argument will be substituted as part of processing. The result becomes the subject of the email. |
| @ContentTemplate | mergable text blob into which every action argument will be substituted as part of processing. The result becomes the content of the email. |
| @QcVars.MailServer | email server address. To override the QcVars version, SELECT QcVars_MailServer as part of your Query (note the underscore), and include @QcVars_MailServer in your ActionArgs in lieu of @QcVars.MailServer (note period). |
| @QcVars.MailServerTimeout | the time to wait for a response from the mail server. To override, apply the same rules as for @QcVars.MailServer. A “0” means “wait forever.” |
| @QcVars.MailServerSSLEnable | either a 1 or a 0 to indicate whether SSL is to be employed. To override, apply the same rules as for @QcVars.MailServer. |
| @QcVars.MailServerUsername | username with which to authenticate against the destination mail server. To override, apply the same rules as for @QcVars.MailServer. |
| @QcVars.MailServerPassword | password with which to authenticate against the destination mail server. To override, apply the same rules as for @QcVars.MailServer. |
| @QcVars.MailServerSslPort | port to use when communicating with the mail server. To override, apply the same rules as for @QcVars.MailServer. |
| @QcVars.AddressFrom | return address for the email. To override, apply the same rules as for @QcVars.MailServer. |
| @QcVars.AddressDisplayName | friendly name for the return address. To override, apply the same rules as for @QcVars.MailServer. |
| @Recipients | Comma-delimited list of recipient emails. |
Sql Action Required Arguments
| Argument | Description |
| @SubjectTemplate | mergable text blob into which every action argument will be substituted as part of processing. The result becomes the subject of the email. |
| @Contents | binary or ANSI blob to send. |
| @Address | FTP server address. |
| @QcVars.FtpServerTimeout | the time to wait for a response from the FTP server. To override the QcVars version, SELECT QcVars_FtpServerTimeout as part of your Query (note the underscore), and include @QcVars_FtpServerTimeout in your ActionArgs in lieu of @QcVars.FtpServerTimeout (note period). A “0” means “wait forever.” |
| @User | username with which to authenticate against the destination FTP server. |
| @Password | password with which to authenticate against the destination mail server. |
| @SuccessSqlToExecute | non-query T-SQL batch to execute upon successful completion of file transfer. |
| @RemoteFileName | the name to give the remote file that will contain the stream encoded by @Contents. |
Any additional arguments that are supplied will be substituted into the SubjectTemplate and ContentTemplate prior to executing the Action. These arguments may be the names of columns in the result returned from the current row in the Query or may be among the arguments that are valid in the Parameters field (e.g. @whenScheduled). Remember, all of these must be preceded by an “at” sign (@), so if your Query returns a column named CountCreditRequests, the you must specify @CountCreditRequests in this field.
All argument lists are case insensitive. That is @CountCreditRequests is the same as @countcreditrequests, for example.
StartOn (required)
This is the date and time to execute the first event. All future event execution times are calculated as a delta from this value.
ExpiresOn (optional)
This is the date and time after which this event will not execute. If NULL, the event will execute per its Interval indefinitely.
Active (required)
Set this value to 1 when you’re ready for Whistle Server to recognize the event. If 0, the system ignores the event.
CatchUp
If StartOn is in the past, and this is set to 1, and there are instances of the event that have not executed between the latest execution and the current time, the system will synchronously execute the event for every missing time period to date. If this is set to 0, events will only begin once the next corresponding offset from StartOn elaspses; thereafter, events will run as expected. As an example, if you want a quarterly report for every quarter last year, and for every quarter going forward, you might set this to 1, then set StartOn to a year ago and set Interval to 12.
Event Templates
Event Templates are stored in the wmEventTemplates table. This table was originally meant for merge processing emails but has been overloaded for general purpose use.
Name (required)
This is the name of the template and is for display use in the corresponding Coolearth Studio maintenance screens.
Subject Template (optional)
For the Email action, this is the mergeable blob used as a basis for the email’s subject. You may embed ActionArgs within the body. For example, “report for period @WhenScheduled to @EndInterval”.
Content Template (optional)
For the Email action, this is the mergable blob used as a basis for the email’s subject. You may embed ActionArgs within the body.
For the Sql action, this is the mergable blob used as the basis for the T-SQL batch to execute. You may embed ActionArgs within the body.
Event Bounds
For each row returned from wmEvents.Query, these bounds are evaluated. The corresponding Action is only executed for rows that pass.
EventId (required)
Foreign key that refers to wmEvents.Id.
ColumnName (required)
The column within the resultset returned by wmEvents.Query on which to apply the rule. For example “ShelfLife”.
LowerLimit, UpperLimit (optional)
The constraints against which to evaluate the value in ColumnName. In the example where ColumnName is “ShelfLife”, LowerLimit might be 7 and UpperLimit might be 30. Only numeric limits are supported. If a limit is omitted, then there is no limit going that direction on the number line.
Exclusive (optional)
If supplied and equal to 1 then the range of acceptable values do not include the boundaries specified in LowerLimit and UpperLimit. If not supplied or equal to 0, the range of acceptable values do include the boundaries specified in LowerLimit and UpperLimit.
InBounds (optional)
If supplied and equal to 1 then the rule is interpreted such that “pass” means falling within the range defined by UpperLimit and LowerLimit. If null or 0, the rules is interpreted such that “pass” means falling outside the range defined by UpperLimit and LowerLimit.
Event History
The event history is stored in the wmEventHistory table. While each event may be associated with entries in wmLog, wmTransactionQueue, and wmTransactionStatus/wmTransactionStatusDetail (as with everything the Whistle Server does), the event processor also stores data from successfully executed events in wmEventHistory. There is one row per event per row returned from wmEvents.Query.
The fields in wmEventHistory correspond to the associated fields in wmEvents and wmEventTemplates for the event at the time it was executed with the following exceptions: ContentTemplate and SubjectTemplate contain the values that were used after merging with the encapsulated arguments; ResultRow contains the XML serialization for the associated row returned by wmEvents.Query; EventBounds contains the XML serialization for the application wmEventBounds record.
Examples
Send an Email Once per Day with Summary Statistics
wmEvents
| Column | Value |
| Id | auto-assigned |
| EventName | Daily Credit Request Summary |
| Company | ME |
| Warehouse | 00 |
| Plant | HS |
| Branch | MC |
| QueryType | 0 |
| Parameters | @WhenScheduled |
| Query |
DECLARE @countCreditRequests INT, @countCreditApprovals INT, @countCreditDenials INT, @whistle_CmApproverEmail VARCHAR(MAX); SELECT @countCreditRequests = COUNT(*) FROM qcMiniatQC_ProductCreditRequestDetails WHERE Dialog_RequestStatus = 'Submitted' AND Status_AddTime > DATEADD(dd,-1,@WhenScheduled) AND Status_AddTime <= @WhenScheduled; SELECT @countCreditApprovals = COUNT(*) FROM qcMiniatQC_ProductCreditRequestDetails WHERE Dialog_RequestStatus = 'Approved' AND Status_AddTime > DATEADD(dd,-1,@WhenScheduled) AND Status_AddTime <= @WhenScheduled; SELECT @countCreditDenials = COUNT(*) FROM qcMiniatQC_ProductCreditRequestDetails WHERE Dialog_RequestStatus = 'Denied' AND Status_AddTime > DATEADD(dd,-1,@WhenScheduled) AND Status_AddTime <= @WhenScheduled; SELECT @whistle_CmApproverEmail = VariableValue FROM wmVariableValues WHERE VariableName = 'Whistle_CmApproverEmail'; -- here’s the query against which bounds would be evaluated -- if there are any -- one event executed per row returned -- (which in this case is only one) SELECT @countCreditRequests AS countCreditRequests, @countCreditApprovals AS countCreditApprovals, @countCreditDenials AS countCreditDenials, @whistle_CmApproverEmail AS Recipients, DATEADD(dd,-1,@WhenScheduled) AS beginPeriod; |
| Interval | 6 |
| Action | 0 (Email) |
| ActionArgs | @SubjectTemplate, @ContentTemplate,@QcVars.MailServer, @QcVars.MailServerTimeout, @QcVars.MailServerSSLEnable, @QcVars.MailServerUsername, @QcVars.MailServerPassword, @QcVars.MailServerSslPort, @QcVars.AddressFrom, @QcVars.AddressDisplayName, @Recipients, @countCreditRequests, @countCreditApprovals, @countCreditDenials, @beginPeriod, @whenScheduled |
| Active | 1 |
| StartOn | 2013-02-14 00:00:00.000 |
| ExpiresOn | (NULL) |
| ModUser | whistle |
| EventTemplateId | 1 |
| CatchUp | 1 |
wmEventTemplates
| Column | Value |
| Id | 2 |
| Name | Daily Credit Request Summary |
| Subject Template | Daily Credit Request Summary |
| Content Template |
Between @beginPeriod and @whenScheduled there were:
@countCreditRequests request(s) for credit;
@countCreditApprovals request(s) approved;
@countCreditDenials request(s) denied.
|
wmEventBounds
No entries required.
Run an Extract and Store the Result Once per Day
wmEvents
| Column | Value |
| Id | auto-assigned |
| EventName | Extract |
| Company | ME |
| Warehouse | 00 |
| Plant | HS |
| Branch | MC |
| QueryType | 0 |
| Parameters | (NULL) |
| Query |
DECLARE @counter INT;
SELECT @counter = wms_counter_idx
FROM wms_counter_tbl
WHERE wms_counter_type = 100;
IF @@ROWCOUNT = 0
BEGIN
INSERT wms_counter_tbl (wms_counter_type,
wms_counter_idx)
VALUES (100, 0)
END;
BEGIN TRANSACTION;
UPDATE wms_counter_tbl
SET wms_counter_idx = wms_counter_idx + 1
WHERE wms_counter_type = 100;
-- this is the query whence we will get parameters for the -- SQL to be executed as part of the event SELECT wms_counter_idx AS GroupId -- we will use @GroupId in the ContentTemplate FROM wms_counter_tbl WHERE wms_counter_type = 100; COMMIT; |
| Interval | 6 |
| Action | 2 (Sql) |
| ActionArgs | @ContentTemplate, @GroupId |
| Active | 1 |
| StartOn | 2013-02-14 00:00:00.000 |
| ExpiresOn | (NULL) |
| ModUser | whistle |
| EventTemplateId | 3 |
| CatchUp | 0 |
wmEventTemplates
| Column | Value |
| Id | 3 |
| Name | Extract |
| Subject Template | Extract |
| Content Template |
DECLARE @txGuid UNIQUEIDENTIFIER, @runGuid UNIQUEIDENTIFIER DECLARE @facility VARCHAR(50) DECLARE @queue VARCHAR(50) SET @facility = 'ME/00/HS/MC/qcdemo/WHISTLESERVER' SET @queue = 'Global/Main' -- inject shipping extract transaction into queue SET @txGuid = NEWID() SET @runGuid = NEWID() INSERT wmTransactionQueue ( TxGuid, TransactionType, Label, SessionSerialNumber, Facility, LocalGenerationDate, ProcessOrchestrator, ProcessHandle, ProcessStart, ProcessStop, TrxQueue, OrderKey, [Status], ApplicationId, ApplicationVersion, Workstation, Payload, lock_count, EventId, WhenScheduled ) VALUES ( @txGuid, 'ceTransactionGs1Transform', 'EIT/Shipping', 1000, @facility, GETDATE(), NULL, NULL, NULL, NULL, @queue, 1, 'Queued', 'SSMC', @@VERSION, 'ARE-PC', '<RequestEnvelope> <Guid>' + CONVERT(VARCHAR(100),@txGuid) + '</Guid> <IsUow>False</IsUow> <RunGuid>' + CONVERT(VARCHAR(100),@runGuid) + '</RunGuid> <TransactionType>ceTransactionGs1Transform</TransactionType> <SchemaVersion>10</SchemaVersion> <Label>ETT/Shipping</Label> <SessionSerialNumber>1000</SessionSerialNumber> <Facility>' + @facility + '</Facility> <LocalGenerationDate>' + CONVERT(VARCHAR(50),GETDATE()) + ' </LocalGenerationDate> <Payload> <ceTransactionGs1Transform> <Name>Shipping</Name> <ExtractSql> SELECT * FROM wmVwChipotleShippingEvent V INNER JOIN wms_outint_tbl OI ON OI.gl_cmp_key = V.gl_cmp_key AND OI.in_whs_key = V.in_whs_key AND OI.in_lot_key = V.CaseLot AND OI.wms_conthdr_key = V.SSCC AND OI.wms_contdtl_key = V.wms_contdtl_key AND OI.so_hdr_key = V.SO AND OI.wms_outdtl_key = V.wms_outdtl_key WHERE COALESCE(OI.extractStatus,0) = 0 </ExtractSql> <GroupId> @GroupId -- recall this from the ActionArgs </GroupId> <SuccessSql> UPDATE wms_outint_tbl SET extractStatus = 1 FROM wms_outint_tbl OI INNER JOIN wmVwChipotleShippingEvent S ON OI.gl_cmp_key = S.gl_cmp_key AND OI.in_whs_key = S.in_whs_key AND OI.so_hdr_key = S.SO AND OI.wms_outdtl_key = S.wms_outdtl_key AND OI.wms_conthdr_key = S.wms_conthdr_key AND OI.wms_contdtl_key = S.wms_contdtl_key WHERE COALESCE(OI.extractStatus,0) = 0 </SuccessSql> <Transformation> <Column> <Name>ShipFromLoc</Name> <ApplicationIdentifier>414</ApplicationIdentifier> </Column> <Column> <Name>ShipFromLocExt</Name> <ApplicationIdentifier>254</ApplicationIdentifier> </Column> <Column> <Name>ShipToLoc</Name> <ApplicationIdentifier>414</ApplicationIdentifier> </Column> <Column> <Name>ShipToLocExt</Name> <ApplicationIdentifier>254</ApplicationIdentifier> </Column> <Column> <Name>ShipDate</Name> <Length>10</Length> <FormatString>MM/dd/yyyy</FormatString> </Column> <Column> <Name>CaseGTIN</Name> <ApplicationIdentifier>1</ApplicationIdentifier> </Column> <Column> <Name>CaseLot</Name> <ApplicationIdentifier>10</ApplicationIdentifier> </Column> <Column> <Name>CaseQuantity</Name> <IsNumeric>True</IsNumeric> <Length>8</Length> <IsFixedLength>False</IsFixedLength> <IsStoredAsText>False</IsStoredAsText> </Column> <Column> <Name>CaseUOM</Name> <IsNumeric>False</IsNumeric> <Length>4</Length> <IsFixedLength>False</IsFixedLength> <IsStoredAsText>True</IsStoredAsText> </Column> <Column> <Name>SSCC</Name> <ApplicationIdentifier>0</ApplicationIdentifier> </Column> <Column> <Name>SO</Name> <IsNumeric>False</IsNumeric> <Length>30</Length> <IsFixedLength>False</IsFixedLength> <IsStoredAsText>True</IsStoredAsText> </Column> <Column> <Name>BOL</Name> <IsNumeric>False</IsNumeric> <Length>30</Length> <IsFixedLength>False</IsFixedLength> <IsStoredAsText>True</IsStoredAsText> </Column> <Column> <Name>PO</Name> <IsNumeric>False</IsNumeric> <Length>30</Length> <IsFixedLength>False</IsFixedLength> <IsStoredAsText>True</IsStoredAsText> </Column> <Column> <Name>Carrier</Name> <IsNumeric>False</IsNumeric> <Length>30</Length> <IsFixedLength>False</IsFixedLength> <IsStoredAsText>True</IsStoredAsText> </Column> <Column> <Name>Broker</Name> <IsNumeric>False</IsNumeric> <Length>30</Length> <IsFixedLength>False</IsFixedLength> <IsStoredAsText>True</IsStoredAsText> </Column> <Column> <Name>ShipVia</Name> <IsNumeric>False</IsNumeric> <Length>30</Length> <IsFixedLength>False</IsFixedLength> <IsStoredAsText>True</IsStoredAsText> </Column> <Column> <Name>Driver</Name> <IsNumeric>False</IsNumeric> <Length>30</Length> <IsFixedLength>False</IsFixedLength> <IsStoredAsText>True</IsStoredAsText> </Column> <Column> <Name>DriverLic</Name> <IsNumeric>False</IsNumeric> <Length>30</Length> <IsFixedLength>False</IsFixedLength> <IsStoredAsText>True</IsStoredAsText> </Column> <Column> <Name>SealNumber</Name> <IsNumeric>False</IsNumeric> <Length>30</Length> <IsFixedLength>False</IsFixedLength> <IsStoredAsText>True</IsStoredAsText> </Column> <Column> <Name>TruckLic</Name> <IsNumeric>False</IsNumeric> <Length>30</Length> <IsFixedLength>False</IsFixedLength> <IsStoredAsText>True</IsStoredAsText> </Column> <Column> <Name>TrailerLic</Name> <IsNumeric>False</IsNumeric> <Length>30</Length> <IsFixedLength>False</IsFixedLength> <IsStoredAsText>True</IsStoredAsText> </Column> <Column> <Name>LoadDate</Name> <Length>10</Length> <FormatString>MM/dd/yyyy</FormatString> </Column> <Column> <Name>OutDate</Name> <Length>10</Length> <FormatString>MM/dd/yyyy</FormatString> </Column> </Transformation> </ceTransactionGs1Transform> </Payload> </RequestEnvelope>', 0, NULL, NULL) |
wmEventBounds
No entries required.
