Loading ...

Applied / Open Amounts and Dates in QQube 7.1 | CLEARIFY

Applied / Open Amounts and Dates in QQube 7.1

 /5
0 (0votes)

We Did What QuickBooks Didn't - and Couldn't

One of the frustrating quirks of QuickBooks reporting, is that if you ran any report, and pulled in the fields "Paid" or "Open Balance", you won't get what you expect.  And it appears that there is no particular logic to the results.

Additionally, the Intuit SDK (Software Development Kit) is missing some of the transaction links that we would need to reverse engineer A/R or A/P from the raw data. Granted the missing transaction links were rare types, such as journal entry to journal entry, but nevertheless possible within a QuickBooks file.

So suffice it to say that QQube was missing a few items, and to compound it, we did not have consistent or congruent rules.

So, in QQube 7.1, we set out to provide four things:

  1. Consistent Document Applied and Open Rules and Signage.
  2. Consistent Line Paid, Applied and Open Rules and Signage.
  3. Properly record Fully Paid Dates for those specific transactions which *may* have an A/R or A/P line, such as a check or credit card.
  4. Match Transaction Due Dates to QuickBooks when present in an A/R or A/P aging report.

Document Level Rules

It is important to note that the signage rules have changed from previous versions. Previously Document Amounts for Credit Memos and Vendor Credits were reversed sign.  This has now changed to the following consistent rules:

  • Document Total is always a positive number, regardless of transaction.
  • Document Applied is what is applied to A/P or A/R (negative number).
  • Document Open is what is remaining for either open A/R or A/P (positive number).
  • Documents that cannot be A/R or A/P - or have no lines that are A/R or A/P - are neither applied nor open e.g. 0.00
  • Document Applied and Open Amounts are formatted to 6 decimal places to ensure accuracy in a pivot table - without rounding.

Line Level Rules

There are also some changes from previous versions with regard to signage, but the current rules are consistent, and congruent across all detail analytics.

  • Line Paid is what is paid (as opposed to applied).
    1. Sales Receipt is always fully paid and fully applied.
    2. If the transaction is an invoice, credit memo, statement charge, bill, vendor credit then both A/R|A/P and Non A/R|A/P is partially paid if partially applied. (this is necessary for job costing, sales, purchasing).
    3. If the transaction is A/R Refund, Deposit, Check, Credit Card Charge/Credit, Journal Entry, Bill Pay Check, Bill Pay CCard, then only A/R|A/P posting lines are partially paid/applied.  Non A/R|A/P lines are always fully paid - again for job costing, sales, purchasing.
    4. Payroll Transactions and Inventory Transactions are always fully paid per line, but never applied (as in Document Open/Paid).
    5. Line Open is what is remaining for either open A/R or A/P where applicable. (*B,*C)
  • Signage
    1. Line Open signage is the same signage as the original line credit or debit amount.
    2. Line Paid signage is the reverse signage as the original line credit or debit amount
    3. Line Original Amount plus Line Paid Amount = Line Open Amount

NOTE: There is one thing you can do in QQube, that you couldn't do before: You can now choose Line Open Amounts in the General Ledger Detail Analytic, filtering for either A/R or A/P account(s) and match to QB Open A/R or A/P reports (for all dates) respectively.

Here is an example of a few types of transactions and various open and paid lines, and applied and open document amounts:

Paid Open Status Examples

Fully Paid Dates

We now address transactions that could appear in A/R or A/P, but were not handled correctly in earlier versions of QQube:

  • Check, Credit Card Charge, Credit CardCredit, A/R Refund, Deposit, Journal Entry, Unapplied and over Payments, etc.

The rules for this date is as follows:

  • All posting transactions that can appear in A/R or A/P will have no fully paid date value if open, and a Fully Paid Date if Paid.
  • All other posting transactions that cannot / do not appear in A/R or A/P will use the Txn Date as the Fully Paid Date.

Due Dates

We now address transactions (as listed under Fully Paid Dates above) that have no due date in the SDK, but could appear in A/R or A/P - and were not handled correctly in earlier versions of QQube.

The rules for this date is as follows:

  • All posting and non-posting transactions which have a naturally occurring due date field will use the SDK results for that field as the due date
  • All posting transactions which don't have a due date field, but have open A/R or A/P lines will use the txn date as the due date, otherwise there is no due date.

One more reason why QQube is still the most complete and comprehensive data warehouse on the market.

Go here for more information on the QQube Version 7.1 release

Comments (no comments yet)

Top Posts