Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Open Order Report

Status
Not open for further replies.

mason188

IS-IT--Management
Jul 29, 2004
2
0
0
US
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
 
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.
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top