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!

Register Log in
  • Congratulations bkrike 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

mason188

IS-IT--Management
Jul 29, 2004
2
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
 
Sort by date Sort by votes
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.
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

BrooksR
  • Locked
  • Question
Recommendation for a Purchase Order package/system
Replies
0
Views
510
BrooksR
BrooksR
shnares
  • Locked
  • Question
Bad Date Format String when generating any report
Replies
0
Views
558
shnares
shnares
DarbyIT
  • Locked
  • Question
VI Work Order Import
Replies
0
Views
554
DarbyIT
DarbyIT
simmyone
  • Locked
  • Question
Inventory Trial Balnce Report
Replies
1
Views
541
supportsvc
supportsvc
dalyj
  • Locked
  • Question
The AP aged report shows invoices due that have been paid and show up paid in vendor history.
Replies
1
Views
253
dalyj
dalyj

Part and Inventory Search

Sponsor

Back
Top