Scheduled Jobs

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)

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

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.

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