Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Open Order Report

Open Order Report

Open Order Report

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.


RE: Open Order Report

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.

RE: Open Order Report

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close