GX:Event System

From coolearthwiki
Jump to: navigation, search

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)
4 HttpPost (posts a stream to a remote site via HTTP)

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.

FTP an Extract Once Per Day

wmEvents

Column Value
Id auto-assigned
EventName Send Extracts
Company ME
Warehouse 00
Plant HS
Branch MC
QueryType 0
Parameters (NULL)
Query 
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
Interval 6
Action 3 (Ftp)
ActionArgs @QcVars.FtpServerTimeout, @Address, @User, @Password, @Contents, @RemoteFileName, @SuccessSqlToExecute
Active 1
StartOn 2013-02-14 00:00:00.000
ExpiresOn (NULL)
ModUser whistle
EventTemplateId 6
CatchUp 0

wmEventTemplates

Column Value
Id 6
Name Null Template
Subject Template (NULL)
Content Template (NULL)

wmEventBounds

No entries required.

Retrieved from "http://demo.coolearth.com/index.php?title=GX:Event_System&oldid=11248"