Open Dental Forum

Can we identify and fetch details of financial transaction (collection) and adjustments applied against the specific procedure?
For Example:
- Transaction ID for Procedure Production
- Patient Payment collection or deposit Date
- -Patient Payment collection or deposit AMOUNT
- Patient Payment collection Transaction ID
- -Patient Payment Amount applied to the procedure code
- -Insurance Payment Amount applied to the procedure code
- +/- Adjustment Amount applied to the procedure code

Yes, the description of all columns for all tables in the database can be found on https://www.opendental.com/OpenDentalDo ... on19-1.xml.

- Transaction ID for Procedure Production
This would be procedurelog.ProcNum.
- Patient Payment collection or deposit Date
You can link use the paysplit table to link a payment to a procedure. The payment table has a PayDate column and also a DepositNum column to link to the deposit.
- -Patient Payment collection or deposit AMOUNT
Use payment.PayAmt and deposit.Amount
- Patient Payment collection Transaction ID
payment.PayNum
- -Patient Payment Amount applied to the procedure code
Use paysplit.SplitAmt to get the amount for a particular ProcNum.
- -Insurance Payment Amount applied to the procedure code
For this, you would use the claimproc table which has a foreign key to the procedurelog.ProcNum column. Then use claimproc.InsPayAmt to get what the insurance actually paid.
- +/- Adjustment Amount applied to the procedure code
You can find that in the adjustment table specifically the AdjAmt column. The adjustment table has a ProcNum column to link it to procedures.

Thanks for the clarification.

Can you please clarify if OpenDental is maintaining "Adjustment Transaction ID"

If you mean adjustment.AdjNum, then yes, that is maintained by Open Dental since it is the primary key of that table.