Hi folks!
I'm hoping one of your brains can help me. I'm trying to come up with a code that will help us with out audit that will return all amounts paid on specific GL's by batch. Very beginner here, but this is what I've come up with:
select distinct O.customer_no, C.fname, C.lname, O.order_no, O.batch_no, O.created_by, O.create_dt, O.tot_ticket_paid_amt,O.tot_fee_paid_amt, O.transaction_no, P.post_no, P.gl_act_no, G.gl_description from T_ORDER as O join T_BATCH as B on O.batch_no = B.batch_no right join T_GL_POSTING_HISTORY as P on B.post_no = P.post_no join T_CUSTOMER as C on O.customer_no = C.customer_no join T_GL_ACCOUNT as G on P.gl_act_no =G.gl_account_no where O.batch_no = '35117' and G.gl_description NOT IN ('Visa', 'Master Card', 'E Transfer Payments School') order by O.customer_no desc
It's almost returning the correct things, but there are 7 GL's on each person, even though they didn't all pay towards them, because there are 7 GL's in the batch. I'm almost certain this has something to do with my left and right joins, but I'm not sure how to fix it. Any help with this would be appreciated!!
Thanks so much!
Nicki
What does
Select * from T_PAYMENT where batch_no = 35117';
look like?
Returns everything from that Batch as expected (names etc redacted:):
Okay, I've made a mistake here, which is that I thought that payments were tied to orders, but they're not, the two are connected via transactions (and batches, as written by John above). This should give you something:
select p.batch_no, p.payment_no, pm.description as payment_method, p.pmt_amt, p.customer_no as payment_customer, o.customer_no as order_customer, o.order_no, p.transaction_no, p.sequence_no
from T_PAYMENT as p inner join TR_PAYMENT_METHOD as pm on pm.id = p.pmt_method inner join T_TRANSACTION as t on t.transaction_no = p.transaction_no and t.sequence_no = p.sequence_no left outer join T_ORDER as o on o.order_no = t.order_nowhere t.batch_no = 35117order by p.payment_no;
Ok, yes, that definitely returned what I'm looking for!! Thank you!!!!!
One more question.... how would you add the GL's to that? As in the GL's that the payments were made towards?
That's where it starts to depend. For us I would typically just add "pm.asset_gl_no" to the end of the select list.
However, if there is concern that at the time of transaction the gls might have been different, and you need that historical value, then we have to dig a little deeper. T_GL_POSTING_HISTORY is kind of a horrible table in that it doesn't store or connect to the transaction/payment activities that we are using to screen on (for payment method type), and I believe it just has totals for the posting, so we would have to turn to T_GL_ACCOUNT_HIST. Getting that to translate the payment methods and the posting date is tricky, I'd want to dig into some Tessitura stored procedures to figure out how they do it.
I'm definitely closer here then I was before. I'm going to go to my accounting manager and see if this is what she needs and can work with. Thank you SO MUCH for your help!