Resend Order Complete Emails

Resend ALL Order Complete Emails

Sometimes it is necessary to re-send order complete emails for a specific customer, order and/or date range.

Some email servers limit the number of emails that can be sent within a short timeframe. It may be necessary to add a SENDSPACING parameter to the notification device configuration.

Be careful! Always run the first query so you can confirm that the number of emails to be sent is correct!

This first query will tell you the number of emails that fit the criteria you specify (customer, order, date range). Notice that the "order_code" parameter is done as a LIKE within the query. You can use % to indicate all orders for the customer.

SELECT COUNT(*)
FROM ARTORDHD OH
INNER JOIN ARTORDCMPNOTIFY OC ON (OC.SESSION_NO = OH.SESSION_NO) AND (OC.TRANS_NO = OH.TRANS_NO)
WHERE
 (OH.CUST_NO = :cust_no) AND
 (OH.ORDER_CODE LIKE :order_code) AND
 (OH.DELIVERY_DATE BETWEEN :BEG_DELIVERY_DATE AND :END_DELIVERY_DATE)

This query will configure the selected order complete emails to be re-sent automatically:

DELETE FROM ARTORDCMPNOTIFY
WHERE ((SESSION_NO || 'K' || TRANS_NO) IN
(SELECT OH.SESSION_NO || 'K' || OH.TRANS_NO
FROM ARTORDHD OH
INNER JOIN ARTORDCMPNOTIFY OC ON (OC.SESSION_NO = OH.SESSION_NO) AND (OC.TRANS_NO = OH.TRANS_NO)
WHERE
 (OH.CUST_NO = :cust_no) AND
 (OH.ORDER_CODE LIKE :order_code) AND
 (OH.DELIVERY_DATE BETWEEN :BEG_DELIVERY_DATE AND :END_DELIVERY_DATE)))

The final step is to issue the following request from the customer's KServer using Postman or the REST Debugger:

http://<server_ip_address>:8211/rest/ktrapi1/ordcmpautonotify?LO_DATE=<BEG_DELIVERY_DATE>&HI_DATE=<END_DELIVERY_DATE>

This will trigger the emails to be sent, usually within an hour.

Resend Order Complete Emails that failed to send

This procedure is supported for Keystone 3.6.30 and higher.

Sometimes it is necessary to re-send the order complete emails that failed to send for a specific customer, order and/or date range.

Some email servers limit the number of emails that can be sent within a short timeframe. It may be necessary to add a SENDSPACING parameter to the notification device configuration.

Be careful! Always run the first query so you can confirm that the number of emails to be sent is correct!

This first query will tell you the number of emails that fit the criteria you specify (customer, order, date range). Notice that the "order_code" parameter is done as a LIKE within the query. You can use % to indicate all orders for the customer.

SELECT COUNT(*)
FROM ARTORDHD OH
INNER JOIN ARTORDCMPNOTIFY OC ON (OC.SESSION_NO = OH.SESSION_NO) AND (OC.TRANS_NO = OH.TRANS_NO)
WHERE
 (OH.CUST_NO = :cust_no) AND
 (OH.ORDER_CODE LIKE :order_code) AND
 (OH.DELIVERY_DATE BETWEEN :BEG_DELIVERY_DATE AND :END_DELIVERY_DATE) AND
 (OH.OC_EMAIL_STATUS LIKE 'ERROR%')

This query will configure the selected order complete emails to be re-sent automatically:

DELETE FROM ARTORDCMPNOTIFY
WHERE ((SESSION_NO || 'K' || TRANS_NO) IN
(SELECT OH.SESSION_NO || 'K' || OH.TRANS_NO
FROM ARTORDHD OH
INNER JOIN ARTORDCMPNOTIFY OC ON (OC.SESSION_NO = OH.SESSION_NO) AND (OC.TRANS_NO = OH.TRANS_NO)
WHERE
 (OH.CUST_NO = :cust_no) AND
 (OH.ORDER_CODE LIKE :order_code) AND
 (OH.DELIVERY_DATE BETWEEN :BEG_DELIVERY_DATE AND :END_DELIVERY_DATE) AND
 (OH.OC_EMAIL_STATUS LIKE 'ERROR%')
))

The final step is to issue the following request from the customer's KServer using Postman or the REST Debugger:

http://<server_ip_address>:8211/rest/ktrapi1/ordcmpautonotify?LO_DATE=<BEG_DELIVERY_DATE>&HI_DATE=<END_DELIVERY_DATE>

This will trigger the emails to be sent, usually within an hour.