Open Order Report

[mason188]

Hi I am trying to create an open order report in ODBC that also shows the amount invoiced, if the sales order has been invoiced. I am using the SO_SalesOrderHeader and SO_SalesOrderDetail tables and linking with the AR_InvoiceHistoryHeader and AR_InvoiceHistoryDetail tables, however the Sequence # in the SO tables is different than the DetailSeqNo in the AR tables, so can't really match up the lines.

Can anyone help on how to match up the line items in the AR tables with the SO tables? It looks I can do it with SO_InvoiceDetail, however after invoicing that table seems to empty itself.

Thanks in advance.

Mason

 

[AndyMc]

You really can't get it done going that route. One issue is a Sales Order can have multiple invoices. Also once an invoice(s) is created from a Sales Order, the invoice can be modified - lines can be added, subtracted, or modified.

The only thing you can depend on is that the Invoice History Header tells you what Sales Order it came from originally.

 

[LouieGrandie]

In MAS errr Sage 100 Orders are in the SO_SalesOrderHeader and SO_SalesOrderDetail files until they are invoiced. Once invoiced they are purged from those files and exist only in the SO_SalesOrderHistoryHeader and SO_SalesOrderHistoryDetail files. The Invoices files have data in them only until they are updated/posted, then they are emptied and exist only the in the AR_InvoiceHistoryHeader and Detail files. Your best option is probably to use the SO_SalesOrderHistory files as these files contain the original order amount, edited order amount and shipped amount plus the current status of the sales order.

Visit Sage's Online Community

http://community.sagemas.com/sagemas/?category.id=SageMAS90and200Disc