16.2 -Bad debt -Adjustment Categorization Query

[drmkpillai]

Hi all,
Using OD 16.2.30- Great New Feature of being able to categorize Certain Adjustment Types as Bad Debts.
Would like to know, the table structure to generate specific queries,Such Total adjustment in a period that are Bad debts
Thanking all

[cmcgehee]

To determine which adjustment types are considered Bad Debt, you would look in the preference table. There will be a row that has a PrefName of BadDebtAdjustmentTypes. The ValueString for that row will be a comma-delimited list of numbers. Each of those values will be a pointer to the DefNum on the definition table. These definitions are your bad debt adjustment types. Here's a simple query that will sum up bad debt adjustments:

Code: Select all

SELECT SUM(adjustment.AdjAmt)
FROM adjustment
INNER JOIN definition ON definition.DefNum=adjustment.AdjType
       AND FIND_IN_SET(definition.DefNum,(SELECT ValueString FROM preference WHERE PrefName='BadDebtAdjustmentTypes'))>0

Happy querying!

[JoeMontano]

In addition to Chris, here is a link to our database documentation/structure that we provide on our website.
http://opendental.com/manual/programmingresources.html

[drmkpillai]

Thanks for all your support