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.

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 (Sales Order, Sales Contract, Purchase Order, Purchase Contract, Sales Quote):

All columns in the dbo.ReportAgreement base table
@BuyerEmail
@BuyerName
@BuyerContactEmail
@BuyerContactName
@SellerEmail
@SellerName
@SellerContactEmail
@SellerContactName
@ShipToEmail
@ShipToName
@ShipToContactEmail
@ShipToContactName

Shipments:

@ShipmentNumber
@PlannedDeparture
@PlannedArrival
@BookingClass
@Carrier
@Forwarder
@Customer
@ShipmentOriginPort
@BookingOriginPort
@BookingSeaOriginPort
@BookingSeaDestinationPort
@BookingDestinationPort
@ShipmentDestinationPort
@Quantity
@Vessel
@BuyerEmail
@BuyerName
@BuyerContactEmail
@BuyerContactName
@SellerEmail
@SellerName
@SellerContactEmail
@SellerContactName
@ShipToEmail
@ShipToName
@ShipToContactEmail
@ShipToContactName

Bills (Bills Credit Memo, Invoice):

@BillNumber
@BuyerEmail
@BuyerName
@SellerEmail
@SellerName
@ShipToEmail
@ShipToName

All Types:

@ExecutionTime = time the action was processed
@ExportURL = the URL for the report or export which is publicly accessible for up to 30 days
@GUID = a globally unique identifier that is unique for a particular event and iteration

Examples of Iteration SQL:

Watch for errors in the event log:
This gives you @Records

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:
This gives you @PaymentReference, @PaymentMethod, and @PaymentPosted

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

Shipment Advice List:
This gives you @PartyId, @SellerName, @SellerEmail, @SellerContactName, @SellerContactEmail, @BuyerName, @BuyerEmail, @BuyerContactName, @BuyerContactEmail, @BuyerRelatedEmails

declare

@StartDate date = cast(dateadd(d, -15, getutcdate()) as date)

, @EndDate date = cast(dateadd(m, 12, getutcdate()) as date);

with bookings as ( -- these are the parties with active shipment advice

select distinct

A.Seller_PartyId,

A.Buyer_PartyId,

PS.Name as SellerName,

PS.Email as SellerEmail,

PSC.Name as SellerContactName,

PSC.Email as SellerContactEmail,

PB.Name as BuyerName,

PB.Email as BuyerEmail,

PBC.Name as BuyerContactName,

PBC.Email as BuyerContactEmail

from dbo.Agreement A

join dbo.AgreementType at on at.AgreementTypeId = A.AgreementTypeId and at.Mnemonic in ('SORDER')

join dbo.Party PB on PB.PartyId = A.Buyer_PartyId

join dbo.Party PS on PS.PartyId = A.Seller_PartyId

left join dbo.Party PSC on PSC.PartyId = A.SellerContact_PartyId and PSC.IsTransactionOptedIn = 1

left join dbo.Party PBC on PBC.PartyId = A.BuyerContact_PartyId and PBC.IsTransactionOptedIn = 1

join dbo.AgreementItem AI on AI.AgreementId = A.AgreementId

join dbo.AgreementItemAssignment AIA on AIA.AgreementItemId = AI.AgreementItemId

join dbo.Shipment S on S.ShipmentId = AIA.ShipmentId

join dbo.Booking B on B.ShipmentId = S.ShipmentId

where B.PlannedArrival between @StartDate and @EndDate

emails as -- these are related parties

(

select distinct

b.*, trim(PP.Email) as Email

from bookings b

left join dbo.PartyRelation pr on pr.ToPartyId in (

case when pr.FromPartyId = b.Buyer_PartyId then pr.ToPartyId

when pr.ToPartyId = b.Buyer_PartyId then pr.FromPartyId

end

)

left join dbo.Party PP on PP.PartyId = pr.ToPartyId

where PP.Email is not null and trim(PP.Email) != '' and PP.IsTransactionOptedIn = 1

)

select -- combining them into a single list

P.Name as BuyerName,

b.Buyer_PartyId as PartyId,

b.SellerName,

b.SellerEmail,

b.SellerContactName,

b.SellerContactEmail,

b.BuyerName,

b.BuyerEmail,

b.BuyerContactName,

b.BuyerContactEmail,

STRING_AGG(e.Email, ',') as BuyerRelatedEmails

from bookings b

join dbo.Party P on P.Partyid = b.Buyer_PartyId

left join emails e on e.Buyer_PartyId = b.Buyer_PartyId

where

(b.BuyerEmail is not null and trim(b.BuyerEmail) != '') or

(b.BuyerContactEmail is not null and trim(b.BuyerContactEmail) != '')

group by

P.Name,

b.Buyer_PartyId,

b.Seller_PartyId,