Hello,
I am trying to write a new custom report and was wondering if anyone can tell me the difference between t_transaction and t_payment in terms of using it for financial reconciliation and settlement.
I need to tie in the the MOS, the price type category, price type, channel, performance, transaction date/payment date, customer, payment method, count of tickets/seats, and dollars.
I know I cannot use the t_transaction table and tie it to both MOS and price type, but I didn't know if I could do it with t_payment or if I should even use that table for financal reconciliation. I wanted to get some advice before I go down this road.
Any ideas?
Naomi
Hi Naomi,
Good day.
that is mine field.
what I can tell is t_payment table is useless for financial reconciliation.
take a look the script, similar thing will happen when you post a batch.
SELECT T.sequence_no, T.transaction_no, T.trn_dt, T.trn_type, T.trn_amt, T.fund_no, ISNULL(F.fee_gl_no, '') AS T_FEE_fee_gl_no, ISNULL(P.gl_hold_no, '') AS T_PMAP_gl_hold_no, ISNULL(P.gl_realize_no, '') AS T_PMAP_gl_realize_no, ISNULL(P.gl_benevolent_no, '') AS T_PMAP_gl_benevolent_no, ISNULL(T_FUND.restricted_income_gl_no, '') AS T_FUND_restricted_income_gl_no, ISNULL(T_FUND.nonrestricted_income_gl_no, '') AS T_FUND_nr_income_gl_no, ISNULL(T_FUND.future_rec_gl_no, '') AS T_FUND_future_rec_gl_no, ISNULL(T_FUND.current_rec_gl_no, '') AS T_FUND_current_rec_gl_no, ISNULL(T_FUND.written_off_gl_no, '') AS T_FUND_written_off_gl_no, T.batch_no, ISNULL(C.fyear, 0) AS fyear, ISNULL(Ty.batch_type_group, 0) AS batch_type_group, T.pmap_no, T.fee_no, T.posted_status, T.order_noFROM T_TRANSACTION AS T LEFT OUTER JOINT_FEE AS F ON T.fee_no = F.fee_no LEFT OUTER JOINT_FUND ON T.fund_no = T_FUND.fund_no LEFT OUTER JOINT_PMAP AS P ON T.pmap_no = P.pmap_no INNER JOINT_CAMPAIGN AS C ON T.campaign_no = C.campaign_no LEFT OUTER JOINT_BATCH AS B ON T.batch_no = B.batch_no LEFT OUTER JOINTR_BATCH_TYPE AS Ty ON B.batch_type = Ty.id
BTW, all the GL records are in T_GL_POSTING_HISTORY.
Financial reconciliation starts from this table.
have fun
Ben