💸Better Payment Complete Filters in CJs

On the legacy STL library, there is a filter called "Payment Complete". The quickest way to replicate this is the subquery filter using the Payment Activities join (as seen in 🏗️ Recreating Slate Template Library Filters). And, most of the time, this works great, and you're done.

However, many institutions will find a scenario where this does not find all records whose payment status is "complete".

Two hypothetical scenarios:

1. An applicant pays their enrollment deposit, then wishes to defer to another term. You clone the application, but don't want to roll the payment activity, since the original transaction data can only be connected to the original application. Instead, the deferred application has zero payment activities.
2. You waive the application fee as part of a promotion, but your fee waiver rule runs "upon update" because it can be applied both upon submission or after submission. And your Payment Due rule is usually added upon submission... so you have to (🎶wait for it, wait for it) until the rules run to add the payment waived activity. 
   
   In the meantime, the student submits the application and sees Slate's default "Pay App Fee Now" element on their status portal and calls your office, confused why it wasn't waived automatically.
   
   The answer? What if... you didn't add the payment due upon submission if the fee is waived? Then, the student never sees the payment widget - it's a seamless "discount" process.

In both of these scenarios, the simple Payment Activities Subquery Filter will not find these records, because the Payment Activities Subquery Filter assumes you will have both a Payment Due activity and a Payment Waived/Received activity.

How can you find them? With a slightly better "Payment Complete" filter, that uses a user-defined SQL function, dbo.getPaymentDueTable. (ref. 🔧 Slate User-Defined SQL Functions)

SQL Formula Structure:

(select due from dbo.getPaymentDueTable(record-guid-here, 'Payment Account Name With Spaces Here')) <= 0

This is actually the same SQL that appears in the STL filter (along with some other filters).

The difference is, the dbo.getPaymentDueTable function will include both the Payment Complete and Payment Never Existed population. If you're using scenario 2, like I ran at one school, that's the difference between 30,000 records and 70,000 records being able to move on from the status of "Awaiting Payment".

Now, there are two flaws with this new filter:

1. You need to know the exact name of your payment accounts - no typos allowed - to use the SQL dbo function correctly.
2. This one filter alone is probably not "enough" to define your population:

What we mean by that is best illustrated by the Awaiting Payments scenario. If you want to find all records who can move on from Awaiting Payments, and your only filter is the dbo.GetPaymentDueTable filter, it would logically include unsubmitted applications that also do not have a payment due. So, you need to have a second filter "Submission Status = Submitted" to truly find the payment complete population that can move on - otherwise, you'd accidentally try to move unsubmitted applications into Awaiting Materials.