Identify Back Oder Sales Orders in SO_SalesOrderHistoryHeader table

Question

Hello, 
 
 We are trying to run some basic metrics surrounding Back Ordered Sales Orders, i.e. "How many Sales Orders had back ordered items last month". Problem is, I can't seem to find any data fields in the SO_SalesOrderHistoryHeader table that would identify that a Sales Order had back ordered items. I could try to use the "QuantityBackordered" field on the SO_SalesOrderHistoryDetail table, but it appears that when the back ordered item gets shipped, it changes this field to 0, so it doesn't help me if I'm trying to report historically. 
 Do you have any suggestions on how we can track this? Is there any field available on the AR_InvoiceHistoryHeader table that would identify the Invoice as being related to a Back Ordered item? 
 Thanks, 
 Colin

David Speck · Answer

You may have to count the number of records in AR_InvoiceHistoryHeader and compare the OrderStatus in SO_SalesOrderHistoryHeader. If OrderStatus in SO_SalesOrderHistoryHeader is "C" and there is more than one record in AR_InvoiceHistoryHeader for the SalesOrderNo, then that would indicate the order was invoiced more than once. You would also need to compare if there is only one record in AR_InvoiceHistoryHeader where the OrderStatus is "A" in SO_SalesOrderHistoryHeader. You could also compare if an order exists in both AR_InvoiceHistoryHeader and SO_SalesOrderHeader as that would mean the order is still back ordered and exists in the open sales order files but this would be equivalent to the "A" OrderStatus in SO_SalesOrderHistoryHeader. 
 You may consider using a script and a checkbox UDF on SO_SalesOrderHeader and/or SO_SalesOrderDetail and the script would check during the pre-write event and if oSession.Updating equals 1, if is, you would then compare if the quantity backordered has been set to something other than zero and if so, set the value of the checkbox UDF to "Y". This UDF should be mirrored into the SO history tables and allow you to report on it as part of your WHERE clause. Once the checkbox UDF has been set to "Y", you would not reset it to "N" even if the back ordered quantity were to become zero. That way it acts like a one time flag.

btmlinesoft · Answer

Try an Excel query of the AR History Header table. Fields of interest would be invoice date, invoice number and sales order number. Add others if you like. Once information is downloaded into Excel, create a pivot table to summarize the SO number by the number of lines it appears in. Any time the SO count exceeds one, that would indicate a backordered shipment (unless your are shipping from multiple warehouses. This is quick and dirty and would give you a rough idea. This is the same idea that David mentions on the third line of his response.

Sage 100

Identify Back Oder Sales Orders in SO_SalesOrderHistoryHeader table

Top Replies

COMMUNITY

COMPANY

PARTNERS

SUPPORT & TRAINING