How to schedule Mass delivery to multiple time zones

This is not a problem.

I just want to share with you a mass  delivery implementation I created for a  customer in Toronto.  We have implemented a ‘modified’ EDI functionality with them.  They have a buying group of 180 customers that send XML orders to my customer.  When these sales order documents are loaded into SAP by an EXE add-on, they are created in ‘Unapproved status’.  Using mass delivery, I needed to send an email to the sales  teams when an order came in so they can review the order, update its status to ‘approved’ and then send a PO acknowledgement back to their customer via EDI.  All straightforward.  I repeat the email every 10 minutes until the order’s status has been changed.  The staff has been excellent about updating the order status to ‘Approved’.  However, the customer has multiple locations for their sales staff – in 3 different time zones.  So the issue is that the mass delivery schedule has a start time and frequency, but no capability to distinguish between time zones.  This resulted in 18 – 21 emails being sent to the sales staff if they received an EDI order 5 minutes after closing time in their time zone (every 10 minutes for the next 3 hours or so).

 To get around this problem, I altered the Mass delivery configuration to look at the customer and determine the time zone associated with this customer and then check the current time to determine if this order mass delivery result ‘qualified’ for mass delivery.  See the code in red below.  The SAP server is located in the Eastern Time Zone (Toronto).

SELECT h.DocNum  as DocKey@

                ,case      when left(h.cardcode,2) in ('10','40') then 'sales@customer.com'

                                                when left(h.cardcode,2) in ('30','50') then 'westernsales@buchananrubber.com'

                                                when left(h.cardcode,2) = '20' then 'sales@customerb.com'

                                                else 'sales@customer.com' end as 'Email'

, h.cardcode as 'BP Code'

FROM  ORDR h

where h.DocStatus <> 'C'

                and h.CANCELED ='N'

                and left (convert(varchar(250),h.header),10) = 'EDI Import'

                and convert(varchar(8),h.CreateDate,112) = convert(varchar(8),getdate(),112)

and h.confirmed = 'N'

/*  Timing  of this. 

                Customers beginning with 30,  50 and 70 are on Mountain time - 2 hours behind Eastern.

                Customers beginning with 40 are in the central time zone - 1 hour behind Eastern.

                Customers beginning with 10 and 20 are in the Eastern time zone.

                We want this routine to pull data between 6:30AM and 6:00PM.

                The server only knows Eastern time.

*/

                and

                (

                (left(h.cardcode,2) in ('10','20')

                                                                                                and replace(convert(varchar(5),getdate(),108),':','') > 0630

                                                                                                and replace(convert(varchar(5),getdate(),108),':','') < 1800)

                or

                                (left(h.cardcode,2) in ('30','50','70')

                                                                                                and replace(convert(varchar(5),getdate(),108),':','') > 0830

                                                                                                and replace(convert(varchar(5),getdate(),108),':','') < 2000)

                or

                                (left(h.cardcode,2) = '40'

                                                                                                and replace(convert(varchar(5),getdate(),108),':','') > 0730

                                                                                                and replace(convert(varchar(5),getdate(),108),':','') < 1900)

                )

ORDER BY h.Docnum

Dr. Don Maes

248-568-0418 cell

248-432-2676 office

Skype: donmaes