x Want to participate? Join the group to interact
Options
  • State Suggested Answer
  • Replies 4 replies
  • Answers 2 answers
  • Subscribers 106 subscribers
  • Views 1401 views
  • Users 0 members are here
Browse Forums
Announcements
293
topics
370
replies
Business Object Interface
1k
topics
7k
replies
Core Financial Modules
2k
topics
8k
replies
Customization and Productivity
3k
topics
12k
replies
Distribution, Manufacturing, and Applications
2k
topics
7k
replies
General Discussion
1k
topics
3k
replies
Partner Cloud
47
topics
95
replies
Payroll, Time Tracking, and Job Cost
777
topics
1k
replies
Reporting and Analytics
965
topics
3k
replies
Sage CRM for Sage 100
164
topics
357
replies
Technical and Installation
2k
topics
9k
replies
Year-end Forum
89
topics
237
replies
Colin Korson

Identify Back Oder Sales Orders in SO_SalesOrderHistoryHeader table

SUGGESTED
Posted By

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

Parents
  • 0
    SUGGESTED

    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.

Reply
  • 0
    SUGGESTED

    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.

Children
No Data

*Community Hub is the new name for Sage City