Hi,

We are facing problem while fetching transactions using “sp_report” stored procedure for the “TxnListByDate” report through QOBBC via VB Demo. We have pasted down snippet of the query…

<Snip>
sp_report TxnListByDate show TxnNumber, Date, TxnType, Debit, Credit, RefNumber, Name, Memo parameters DateFrom = {d'2002-01-04'}, DateTo = {d'2002-01-04'}, AccountFilterListIDs= ‘10001-1033056814’, ReportDetailLevelFilter='All'
<Snip>

We have created following transaction in QuickBooks Enterprise Canadian Edition 2004….

Date of Transaction: - 01/04/2002
It is General Journal Type Of transaction.
Account                  Debit            Credit          
--------------------------------------------------
Chequing                 100                       
Retained Earnings                        100

Consider Account List ID for Retained Earning account is “20000-1009976761”. When we tried to execute query above using VB Demo for QuickBooks Enterprise Canadian Edition 2004 and QODBC Version 7.0.0.204…It returned following records….

Account                  Debit            Credit          
--------------------------------------------------     
Retained Earnings                        100

We created same transaction in QuickBooks Enterprise Solutions 6.0 – US Edition…But after executing the same query for QuickBooks Enterprise Solutions 6.0 – US Edition and QODBC Version 7.0.0.204…we got following results...

Account                  Debit            Credit          
--------------------------------------------------     
Retained Earnings        100
Retained Earnings                        100

Not sure what we are missing here but the same query in US Edition returns duplicate records. One with Amount debited in Retained Earning Account and another entry of with amount credited for the same account.

The Problem occurs for Retained Earning Account only. Instead of General Journal entry, we tried it using Check transactions, but again we found out same problem with Retained Earning Account.

Also, we found the solution where the query returns single record in US Edition…Changing filter…ReportDetailLevelFilter to ‘AllExceptSummary’. But then this filter out all Summary records from the list.

Do we missing anything? Is there any solution to resolve the problem?

Any help would be appreciated.

Thanks and Regards,
Clifford

The results will depend on the accounting period (year) QuickBooks is in when the journal is done to Retained Earnings. But the QODBC tables will always get you the correct result, where the QuickBooks reporting engine will show the results after internal adjustments for Retained Earnings based on the year.

For this journal:

I also don't see the Retained Earnings line (because the date on the journal is dated before the current year for Retaining Earnings):

But by using the QODBC tables, I can see both (and all) lines correctly by doing:

SELECT TxnNumber, TxnDate as Date, 'General Journal', JournalLineDebitAmount as Debit,
JournalLineCreditAmount as Credit, Refnumber, JournalLineAccountRefFullName as Name,
JournalLineEntityRefFullName as Memo FROM JournalEntryLine where TxnDate={d'2007-01-10'}