Dear All

QuickBooks Ver. :   Enterprise Solutions 5.0

I am trying to find cost of sales for each line item, I have checked all the tables with no use !!! it has to be there because when I check on cost of goods sold account the report gave me cost per each line item.

Please Help

Regards

Dear All

I have checked this post before but I think it is not the correct reply, becuase there is an account called "cost of sales", this account saves  invoice cost when it is issued based on the Avarege Cost.

 I may have multiple purchase costs for the same item, so purchase cost not the cost of sales. As far you know avarege cost always changing based on receipts , so you can't consider current avarege cost is the cost of sales for previous invoices. I am certain there is a table has the cost of sales for each line item invoiced becuase cost of sales account report has that information.

Regards

Below you can find COGS Account sample report, you can see  ITEM NO, DESCRIPTION and cost for the sold items, that data is there how can I find it Thru QODBC,which table has that info.?

Type

Date

Num

Name

Item

Item Description

Split

Amount

Invoice

20/09/2006

9290

AL SHOLA TRANSPORT CO.LLC

8M5249

SEAL-O-RING

Accounts Receivable

0.95

Invoice

20/09/2006

9290

AL SHOLA TRANSPORT CO.LLC

4N7253

GASKET

Accounts Receivable

1.41

It is a standard quick report  within chart of accounts for the cost  of sales account. 

What I was saying was correct, but I was showing the COGS for each item, not the GOGS for the total number of items for each Invoice line. For example, this Account QuickReport:

If I run this query using the Quantity instead:

SELECT "InvoiceLine"."TxnDate" as Date, "InvoiceLine"."RefNumber" as Num, "Customer"."FullName" as Name, 
"InvoiceLine"."InvoiceLineDesc" as Memo, "InvoiceLine"."InvoiceLineQuantity" as Qty,
"InvoiceLine"."InvoiceLineItemRefFullName" as Item,
"InvoiceLine"."InvoiceLineRate" as Rate, "ItemInventory"."PurchaseCost" as Cost,
"ItemInventory"."AverageCost" as "Average Cost",
("InvoiceLine"."InvoiceLineQuantity" * "ItemInventory"."AverageCost") as Amount
FROM   "Customer" "Customer","InvoiceLine" "InvoiceLine", "ItemInventory" "ItemInventory"
WHERE  ("InvoiceLine"."CustomerRefListID"="Customer"."ListID")
AND  ("InvoiceLine"."InvoiceLineItemRefListID" = "ItemInventory"."ListID")
ORDER BY "InvoiceLine"."TxnDate"

I get a better representation of what my true COGS really is. But to get the GOGS for all invoices seen in your Account QuickReport you can run this report directly by using this QODBC stored procedure instead:

sp_report TxnDetailByAccount show Text, TxnType, Date, RefNumber as Num, Name, Memo, Debit as Amount
parameters AccountFilterType = 'CostOfSales', TxnFilterTypes = 'Invoice', DateMacro = 'All'

In the first example you used current AVE. COST and this doesn't represnt exactly the COGS but the procedure given in the second exmaple shows  the correct COGS values , my question is where in the second example the COGS saved  ? which table ? I need to have a precise results .

Thanks