OEINVD item cost in different fields / MSSQL 3

[kyariexcel]

So I'm trying to make a gross profit report for my boss, and to do that we want to compare all items on all invoices per order with their unit price against their unit cost. So all's going well, until I start noticing that the cost of each item displayed in Order Entry doesn't always match the UNITCOST field in the OEINVD table. Sometimes it does match, however there are two other fields that appear to be sometimes used: AVGCOST and MOSTREC. This impacts my ability to make the report in SQL because I don't know when to use which field for the calculation.

My question is - can I find another field and/or another table that will help me always get the cost of the item for that particular invoice?

Here's my current SQL query:

SELECT oeinvd.ordnumber AS ordernum,
oeinvd.item AS item,
oeinvd.qtyshipped * oeinvd.unitcost AS cost,
oeinvd.qtyshipped * oeinvd.unitprice AS price
FROM oeinvd
WHERE oeinvd.linetype = 1

It must be possible somehow, because ACCPAC always displays the "right" value - I just don't know where to look in the SQL database.

 

[Jay Converse]

EXTICOST

 

[kyariexcel]

This is a report I wrote in PHP. Why would the EXTICOST not match what's displayed in ACCPAC?

 

[ettienne]

I would use OESHDT for this report instead of OEINVD.

 

[Jay Converse]

Because it's the extended cost. From the AOM:

COSUNTCST BCD 10 6 Costing Unit Cost 37 EAX 0
COSUNTCONV BCD 10 6 Costing Unit Conversion 38 EA 0
EXTICOST BCD 10 3 Extended Detail Cost 39 CEAX 0
EXTINVMISC BCD 10 3 Extended Shipped Price/Misc. Cha 40 EAX 0

But Ettienne is right, OESHDT, or OEAUDD are better for margin reports.

 

[kyariexcel]

Ooooh, I didn't see the sales history details table... that might help. I'm still somewhat new to ACCPAC and it's various tables, it's a little overwhelming for me at times!

I saw the OEAUDD table and didn't know what I was looking at, basically. I'll try to give it another look over, along with OESHDT.

Thanks everyone

 

[Jay Converse]

https://partners.sagenorthamerica.c...e Accpac ERP/Ungated Customers/210-30254.html

 

[kyariexcel]

Wow, such a helpful post Had no idea they provided that kind of information, makes my job a lot easier!

The report is looking good so far now, also I'm noticing that there are a few functional costs that are not showing the same value in order entry, but are showing the same value as the extended cost in the invoice details which is probably what I want anyway.

Thanks again all!