Hello everyone,
Does anyone know what table on-account money is tracked in SQL Server? I'd like to be able to do a query but I'm having a heck of a time figuring this out.
Christopher Cuhel
It is in T_PAYMENT
From the Tessitura Table Structures document: This table also is where “On Account” balances are generated from. A customer’s on account balance for any particular payment method can be calculated by summing pmt_amt for that customer and payment method.
Hi Sadie! Do you know if the value in the T_PAYMENT table is per transaction, or cumulative? For instance, when I look at the pmt_amt field, I'm assuming this is per transaction. We often have more than one transaction that then ends up in the same On Account type. Is this calculated each time, or is there some sort of table value somewhere to pull from?
Brandi,
It is per transaction. You will need to sub up all payment amount values for a patron to get it. Here is the code we use for our headers.
Select NULLIF((-1 * SUM(pmt_amt)),0) From [dbo].t_payment a JOIN [dbo].vrs_payment_method b WITH (NOLOCK) ON a.pmt_method = b.id Where a.customer_no = @customer_no and b.pmt_type = 2