Adjusting Order Status Through a TSQL Command

[it@isa-arbor.com]

Hello,

We want to automate the transfer of orders from AbleCommerce to our primary operations database. Both are in SQL and transfering information about the customer, address, items purchased and payments is relatively straight forward.

We plan to use an intermediate interface to match the customer and address from AbleCommerce to existing customer and address records in our database. When the record is matched it will be imported (updating information or inserting new records as needed).

We would like to update the status of the order in Able Commerce when the record is imported into our Operations database. Updating the status via TSQL is fairly easy but, will this cause problems in AbleCommerce? For example an e-mail is sent from able commerce when an order is marked as 'Shipped'. Is this done via SQL or the ASP.NET pages that comprise the store? If we update they status in TSQL will the e-mail still be triggered.

We are considering an intermediate custom status of 'Imported' in AbleCommerce. Our order entry folks would still need to go into Able Commerce and manual change orders marked 'Imported' to 'Shipped'.

Thoughts? Comments? If we change it directly to 'Shipped' via TSQL will this still work?

Thanks,

Ernie Noa
IT Manager
ISA
http://secure.isa-arbor.com/webstore/

[afm]

For example an e-mail is sent from able commerce when an order is marked as 'Shipped'. Is this done via SQL or the ASP.NET pages that comprise the store?

Based on my sleuthing...neither. AbleCommerce, the website, is a collection of ASP.NET web pages on top of an e-commerce platform called CommerceBuilder. CommerceBuilder is a collection of .NET DLL's that contain almost all of the business logic for store (a little has sneaked out into the ASP.NET pages). The email is sent by CommerceBuilder. The database is completely "dumb" in the sense that it does not implement any business logic.

If we update the status in TSQL will the e-mail still be triggered?

No.

If we change it directly to 'Shipped' via TSQL will this still work?

The CommerceBuilder method that marks a shipment shipped is called OrderShipment.Ship. I do not have the source to CommerceBuilder, so I don't know what OrderShipment.Ship does (other than the observable changes in the database). But you could purchase the source to find out what Ship does (and any other method), then replicate the functionality in TSQL.

[it@isa-arbor.com]

Thanks. I think I'm going to try a custom order status of imported and have our order operators change that to Shipped so the asp.net code still executes adjusting the order as needed. (at least that's the plan).

[afm]

IIRC, there is no order status called "Shipped". Or at least not out of the box. There is an Shipment status called "Shipped" (and another one called "Unshipped"). And if all the shipments in an order are Shipped, then the order status is changed to Complete (oddly...even if there is a pending payment).

[AbleMods]

The status "triggers" are fired either within the code-behind files (very rare) or within the CommerceBuilder DLL files (most common). Altering the OrderStatusId in TSQL should have no adverse affect as long as you understand that the integrated AC7 triggers will NOT fire as a result of your TSQL changes.

Be careful if you choose to manipulate ShipmentStatusId and PaymentStatusId. I believe these fields are calculated and altered by CommerceBuilder on-the-fly.

Also be aware, certain actions in AC7 will alter the order status. For instance, if you set the Order status from "Unshipped" to "MyNewStatus" and then capture the payment, the order status will be changed back to "Unshipped". This could have a bearing on your TSQL manipulation of the field, so plan your order processing procedures accordingly.

[it@isa-arbor.com]

IIRC, there is no order status called "Shipped". Or at least not out of the box. There is an Shipment status called "Shipped" (and another one called "Unshipped"). And if all the shipments in an order are Shipped, then the order status is changed to Complete (oddly...even if there is a pending payment).
IIRC, there is no order status called "Shipped". Or at least not out of the box. There is an Shipment status called "Shipped" (and another one called "Unshipped"). And if all the shipments in an order are Shipped, then the order status is changed to Complete (oddly...even if there is a pending payment).

Yes, you are correct. Sorry about the error. When you look up the order from the management section of the back end the column is simply labelled, 'Status'. The order detail lists the full label of 'Shipping Status'. My mistake. However my general though process and plan remains the same.

Altering the OrderStatusId in TSQL should have no adverse affect as long as you understand that the integrated AC7 triggers will NOT fire as a result of your TSQL changes.

I can create a custom shipping status in the back end. I assume a customer shipping status would do nothing when selected. No DLL files would be called nor would any code in code behind files be run.

I think when the user imports an ordeder I'll change the shipping status to a custom status of 'Imported'. Then I'll require the user to log into the store and manually change from Imported to 'Shipped'. Right now users are manually re-keying orders in our Operations database then manually changing the status in AC from Shipment Pending to Shipped. I'm guessing the net effect will be the same. I suspected this is what I would need to do but figured asking was a good idea.

Thanks to all for the advice!