This past Monday, I got a call from a customer, who thought she was losing her mind. She swore she ordered staplers, but could find no evidence of it – not on her order that showed up, not an any confirmations from ECInteractive, nowhere to be found. I pulled up her account in OE History, and as DDMS does, it pulled the order with the staplers up first. First because it was the only order on the account in P-MASTER. It was still in P-MASTER because it had gotten stuck in the dreaded Status 1.
Normally the sign that one of your customer service people accidentally fat-fingered the keyboard when they were ending an order, sending it to a non-existent printer, this one was different. The customer had placed the order on ECInteractive. While I’ve been reassured that such a thing “can’t happen”, this is far from the first time. I checked her settings as a matter of formality – of all the orders placed on ECInteractive that have gotten stuck in a 1, never once has it been due to bad settings.
The customer was all right with it, being glad that she wasn’t going crazy and she wasn’t in a hurry to get the staplers, so the day lag didn’t much matter to her at all. It got me thinking, though, that maybe now was the time to fix the problem, or at least mitigate it. I opened up SQL Server Management Studio and banged out the following query in short order:
select distinct(SalesOrderDetail.SalesOrderId)
from SalesOrderDetail
where SalesOrderDetail.StatusCode = '1'
and (SalesOrderDetail.OrderDate = '2013-06-12'
or SalesOrderDetail.OrderDate = '2013-06-11')
As you can see, all it gives me is the invoice number (SalesOrderId, in SQL parlance). That’s really all I need, since I’ll need to pull up the order and deal with it in DDMS anyway. We do a select distinct on it because we’re pulling from SalesOrderDetail. The Detail tables get down to the line item level, and we need to pull it from there as that is where the status (StatusCode) is held. I don’t need the same invoice number once per line on the order – just once is all I need. I pull yesterday and today, as it doesn’t need to wade through the entire file, since I cleaned up my status 1 tickets when I put this in place. We use a bit of PHP to handle that – I need to account for the last business day on Monday, after all. For that, we use
$dow = (date('w'));
if ($dow == '1') {
$d = 3;
} else {
$d = 1;
}
and then specify our dates:
$fnDate = date('Y-m-d', time());
$yest = date('Y/m/d', mktime(0, 0, 0, date('m'), date('d') - $d, date('Y')));
Using PHP’s date and mktime functions, we’re telling our script that if today is Monday, go back three days, to Friday.
Beyond that, I ripped most of the rest of the code from a previously-created report, Flushed Today, since we only need to plug in our new query and fix the table to match our single-column output.
If you’d rather not futz with it yourself, the report file is available from bendustries.co/files/cli/status_1_today.zip.
Granted, this isn’t perfect, and a dealer that’s been around for any length of time likely has come up with a way to deal with these. Where I work, though, we had nothing. We had tried this and that over the years, but nothing stuck. This, though, makes it quick and easy – I get the email showing an order has gotten stuck, I do the reprint trick in OE History to move it to a 6, and I’m done.
Take this file, put in the email address(es) to send it to (and a from address while you’re about it), put it where your command-line scripts live (I’ve recently put a ‘cli’ directory on the root of my /htdocs) and schedule it in Task Scheduler. That’s all there is to it.
As ever, find me at 
if you need my help.
Recent Comments