Open Dental Forum

I had a discussion with Dr Sparks about FIFO Splits. We talked at length about what to do and what not to do. My main problem with how it is now that I cannot expect my employees to reasonable determine how payments should be split for providers or otherwise. I have code that allocates Payments to providers based on a when a payment was received irrespective of whether it was attached to an EOB. There is a problem with injecting the philosophy into the main part of the payment structure. It would require massive amounts of code change. My solution would be to add a separate table for AllocationSplits. The entries in this table would be very similar to the PaySplit table. The entries would have a marker for the type of split or philosophy used for the split. Using the marker you can pull the splits that are allocated based on the rules assigned in the split methodology. There would be a hook in the program that will be called just after to the payment creation, modification, and deletion, and patient moving (from account to account). Also when a charge item is entered/modified/deleted there will need to be a check to see if there is any part of a payment that is unallocated. Putting these values in a separate table allows for the generation of split payment data.

-The allocation logic would be hardcoded so that simple users cannot alter the allocation.
-Creation of this split data would be only for reporting purposes to develop a FIFO split for provider revenues.
-The design of the allocation is to make the data immutable for the most part, but since payments move when transferred from guarantor to guarantor this will have to be dealt with. Perfect immutability may not be achievable with current setup.
-There will be a preference setting to turn on/or off this feature

General Programming Philosophy is to create an interface for an Allocator Object. The interface will mandate that the Allocator object will have two methods. Paymt_Allocator Paymt_Deallocator. So when a payment is altered the deallocator is called and the the Allocator is called when payment is created or solidified. The deallocator will likely just delete all the splits assigned to the Allocator object and expect the Allocator to be realled to re-run the logic of allocation.

I will post a second post to outline the Logic for choosing how the splits will be allocated in the next post. The idea is to generate code that if someone wants a different allocation it can be done.

The FIFO Logic that I will use in the first Allocator

FIFO payment will be based on a Guarantor Account system.
Payment will be allocated to provider irrespective of Patient
-This means that all the payments for a guarantor will be grouped together and allocation as if from one guarantor. The Provider Allocation will stay with the the patient assigned to the Payment.PatNum so if a patient moves account then the payment will still be allocated to the provider but will not be reallocated unless the payment physically changes.

-Adjustments and WriteOff's will explicitly state their provider and will not be reallocated to a different provider.
-If an adjustment and a WriteOff are altered the payment split structure will not be reallocated. Any imbalance in provider payment will have to be managed by the next payment made.
-(-ve) Adjustments and are subtracted from provider charges prior to the application of a payment.
-(-ve)WriteOffs are treated as adjustments
-(+ve)WriteOffs and Adjustments are treated like charges.
-If payments are inexcess of the charges on the account they will have a provider set to 0

Provider Report Query should be something similar to:

SELECT Provider.Abbr, Sum(AT.SplitAmt) From Provider, Allocations as AT, Payment
WHERE
PS.ProvNum = Provider.ProvNum
&& Payment.PayNum = AT.PayNum
&& Payment.PayDate > '2007-01-01'
&& Payment.PayDate < '2007-12-31'
Group By
Provider.Abbr

Table structure is the most important part of this. I have some minor comments about your query:
1. Table names are always lowercase for compatibility with Linux.
2. Table names are singular for consistency.
3. SQL keywords should be all caps.
4. We don't use AS for compatibility with Oracle.
5. We use AND instead of && for compatibility with Oracle and MS.

I know these are just style comments. So it would be

SELECT provider.Abbr, SUM(AT.SplitAmt)
FROM provider, allocation AT, payment
WHERE
AT.ProvNum = provider.ProvNum
AND payment.PayNum = AT.PayNum
AND payment.PayDate > '2007-01-01'
AND payment.PayDate < '2007-12-31'
GROUP BY
provider.Abbr

Anyway, there are going to be other style issues that come up. I don't have boilerplate pages on the website yet for our current strategy and style issues. But the first issue that I think is going to come up is when we decide on the table structure and it's time to add the CRUD code. I'll find the appropriate template file from the existing code. There are probably only one or two files which are correct, and that format will need to be followed.

But beyond that, I don't see any problems. Your code can all go in its very own files that nobody else will touch. It should be fairly simple. Also, I can easily create a global option to not allow rearrangement of families so that your guarantor calculations will never get altered. I can make sure all the hooks into your code are in the right place. It helps a lot that you don't want to build any UI elements.

I agree with your comments. Good to have a consistent style. Also useful to know the AS rule. As for lowercase. MySql has an option that forces all table names to lower case regardless of the query. When a table is created even if you create it in mixed case it will force a lower cases. I believe this rule also applies to column names as well. I just like the modified hungarian notation for being able to see it quickly.

I believe having this option of the forcing lower case may actually improve mysql performance because then no mixed case will be done. I'll make sure queries all have lower case in them though.

What do you mean by CRUD Code?