Scheduled Job
A System Event is something which occurs based upon certain conditions (such as a Shipment being marked complete).
An Action is something which is done as a result of an event.
With the help of an installed Windows Service (Job Agent), Shipper can respond to events in the system in the following ways:
Export Data (to a destination on the server)
Run SQL
Send Email Notification (via configured mail server)
Send a Report (as an attachment to an email)
Send SMS (a text message)
Update MailChimp Subscribers (see MailChimp Integration)
Update QuickBooks Online (see QuickBooks Online Integration)
All events are logged as soon as they occur. They cannot be undone. For example, if you mark a Shipment as Complete, then moments later decide that was not a good idea the event will fire anyway. Also, you can mark it complete, then clear it, then mark it complete again and the event will fire twice. The only exception here is Schedule Tasks. These occur on a regular basis. Every hour (at a certain number of minutes past the hour); daily (or multiple days per week) at a certain time of day; or monthly at a certain time on a certain day of the month.
Actions each have their own configuration. For example, to run a report the system will need to know which report to run.
For all actions, you may use replaceable parameters. For example, @ShipmentId is replace with the actual ShipmentId. This is true for any field on the Shipment, Booking, Agreement, or Bill tables. The parameters are Case Sensitive!
For Email Notifications, SMS, and Reports, the "Iteration SQL" allows you to run the same event for each row returned by your SQL. Each of the columns in your result becomes a case-sensitive replaceable parameter. You could use this to send Shipment Advice to everyone who has had activity.
Currently, the following events are supported:
Add to Queue
Bill Completed
Bill Payment Posted
Bill Posted
Booking Completed
Credit Memo Completed
Credit Memo Payment Posted
Credit Memo Posted
Invoice Completed
Invoice Payment Posted
Invoice Posted
Letter of Credit Completed
Letter of Credit Payment Posted
Letter of Credit Posted
Purchase Contract Completed
Purchase Contract Posted
Purchase Order Completed
Purchase Order Posted
Remove from Queue
Sales Contract Completed
Sales Contract Posted
Sales Order Completed
Sales Order Posted
Sales Quote Completed
Sales Quote Posted
Scheduled Task
Shipment Completed
Shipment Document Created
Additionally, the following are available, whenever they are associated with an event.
Agreements:
All columns in the dbo.Agreement base table
@BuyerEmail
@BuyerContactEmail
@SellerEmail
@SellerContactEmail
@ShipToEmail
@ShipToContactEmail
Bookings:
All columns in the dbo.Booking base table
@CarrierEmail
@ForwarderEmail
@BuyerEmail
@BuyerContactEmail
@BuyerContactName
@SellerEmail
@SellerContactEmail
@SellerContactName
@ShipToEmail
@ShipToContactEmail
@ShipToContactName
@DestinationEmail
@OriginEmail
@ForwarderEmail
Shipments:
All columns in the dbo.Shipment base table
@BuyerEmail
@BuyerContactEmail
@BuyerContactName
@SellerEmail
@SellerContactEmail
@SellerContactName
@ShipToEmail
@ShipToContactEmail
@ShipToContactName
@DestinationEmail
@OriginEmail
@ForwarderEmail
@CarrierEmail
Bills:
All columns in the dbo.Bill base table
@BuyerEmail
@SellerEmail
@ShipToEmail
All Types:
@ExecutionTime = time the action was processed
@ExportFolder = a local server folder where exported data can be stored. i.e. @ExportFolder/test.csv becomes F:\Export\test.csv
@ExportURL = the URL for the local server that can be sent via a notification. i.e. @ExportURL/test.csv becomes http://db03.hurkin.com/export/test.csv
@GUID = a globally unique identifier that is unique for a particular event occurrence but not for each iteration of an iteration SQL.
Examples of Iteration SQL:
Watch for errors in the event log:
select
count(*) as Records
from
dbo.EventActionLog eal
where
eal.Result not in ('Success','')
and eal.CreatedDate > dateadd(day,-1,getutcdate())
having count(*) > 0
Including payment information for the "Invoice Payment Posted" event:
SELECT TOP 1 p.Reference1 as PaymentReference, pm.Description as PaymentMethod,
format(p.Amount,'N2') as PaymentAmount, dbo.ConvertTime(p.Posted,-480) as PaymentPosted
FROM dbo.Payment p
JOIN dbo.PaymentMethod pm on pm.PaymentMethodId = p.PaymentMethodId
WHERE p.BillId = @BillId
ORDER BY p.Posted DESC
URL's for public images associated with an item;
select Q.Attachments
from dbo.Shipment s
JOIN ( -- Attachments
SELECT qq.ShipmentId,
Attachments = (SELECT STUFF((SELECT distinct ', ' + q.AttachmentURL as [text()]
FROM dbo.Attachment q
WHERE q.AttachmentURL like '%public%'
AND q.ShipmentId = qq.ShipmentId
FOR XML Path ('')), 1, 2, ''))
FROM dbo.Attachment qq
GROUP BY qq.ShipmentId
) Q ON Q.ShipmentId = s.ShipmentId
WHERE
s.ShipmentId = @ShipmentId