Trying to Write a report to see how my sales are what tables do I need to look at?

Posted By imScott over 9 years ago

I will be making a number of reports and though best to create some views in my database that have as much information as possible in order to supply the data to the myriad of reports I will write.

CURRENTLY I HAVE:

- OEINVH joined with OEINVD so I can pull all the items sold qty and price

- Then I subtract credits and add debits from OECRDH Joined with OECRDD

My thought was to check against the O/E Sales Statistics but the Net Invoice amount s off by around 1% for every period.

Does anyone know from where O/E sales statistics Net Invoice Amount is gathered and how? I assume these are in statistics tables but the values should have come from entering invoices, credits and debits.

Parents

0 Jay Converse Acumen over 9 years ago

Forget OEINV* and OECRD*, use only OEAUDH and OEAUDD
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 imScott over 9 years ago in reply to Jay Converse Acumen

Please explain why. I am a bit new and cannot see why these tables would be different. If using OEAUDH and OEAUDD do I ignore credits and debits? Are these figures accurate? Will they match "O/E Sales Statistics" -> "Net Invoice Amount"

Also, thank you for the fast response!
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 Jay Converse Acumen over 9 years ago in reply to imScott

The reason why, is because these are the tables that those of us who have been using this product for 15 years use. Search the Sage 300 forums for Application Object Model (AOM) to get table/field layouts.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel

Reply

0 Jay Converse Acumen over 9 years ago in reply to imScott

The reason why, is because these are the tables that those of us who have been using this product for 15 years use. Search the Sage 300 forums for Application Object Model (AOM) to get table/field layouts.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel

Children

0 imScott over 9 years ago in reply to Jay Converse Acumen

My issues with the AOM is that it does not explain or show how the data actually flows.

When I look at (OEAUDH with TRANSTYPE=1) it does match "O/E Sales Statistics" -> "Net Invoice Amount"

Am I to assume that when a credit is posted pertaining to an Invoice it also effects the rows in OEAUDH and OEAUDD for TRANSTYPE=1 that had (before the credit was posted) applied to the given invoice? Does it remove them? or change them....or something else.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 Ettienne Schwagele over 9 years ago in reply to imScott

OESHDT = OE sales history detail
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 5107763 over 9 years ago in reply to imScott

OEAUDD and H are Audit tables - therefore once posted, they will never be changed or deleted as part of a transaction.

I think I speak on behalf of most of us 'old' consultants in that the object models are 100% accurate, but the relationships between associated tables (audit and history) are not documented anywhere and I don't think anyone on the forum could tell you exactly how all the relationships work. You should learn them the same way we did: create a new customer, post some transactions and monitor the tables. With that approach, you will own the relationships when you are done.

Note: The thing you have to be careful about is that in many cases in Sage, Credits are not stored as negatives, the are just flipped to a negative based upon the TRANSTYPE.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 Ettienne Schwagele over 9 years ago in reply to 5107763

OESHDT stores credits as negatives :)
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel

Sage 300

Trying to Write a report to see how my sales are what tables do I need to look at?

COMMUNITY

COMPANY

PARTNERS

SUPPORT & TRAINING