I'm working on some code to pull a list of orders containing a particular performance and using a particular payment method. The problem I'm running into is that T_PAYMENT and T_TRANSACTION don't seem to contain payments made for orders that don't have customer numbers attached. And in this instance, all of the orders I'm looking to isolate do not have a customer attached. Is there any other table that contains ALL payments/transactions, so I can edit my joins accordingly? I'm coming up blank when I look around for one.
I have a Payments by Posting report that uses the query below to return Credit Card payments (you could choose another payment method by switching the value for VRS_PAYMENT_METHOD.pmt_type). It returns general pubilc orders too, so it might work for you.
BTW - VS_TRANSACTION.ref_no = order number
SELECT p.payment_no, p.sequence_no, p.transaction_no, p.customer_no, p.pmt_dt, p.pmt_amt, p.pmt_method, p.account_no, p.check_name, p.batch_no, b.batch_no AS Batch_BatchNumber, b.post_no, FT_CONSTITUENT_DISPLAY_NAME_1.display_name, p.cc_start_date, p.cc_issue_no, p.ccref_no, p.auth_no, p.card_expiry_dt, VRS_PAYMENT_METHOD.id, VRS_PAYMENT_METHOD.description AS Payment_Method_Desc, VRS_PAYMENT_METHOD.pmt_type, VS_TRANSACTION.ref_no, TR_TRANSACTION_TYPE.description AS Transaction_Type
FROM T_PAYMENT AS p INNER JOIN T_BATCH AS b ON p.batch_no = b.batch_no INNER JOIN dbo.FT_CONSTITUENT_DISPLAY_NAME() AS FT_CONSTITUENT_DISPLAY_NAME_1 ON p.customer_no = FT_CONSTITUENT_DISPLAY_NAME_1.customer_no INNER JOIN VRS_PAYMENT_METHOD ON p.pmt_method = VRS_PAYMENT_METHOD.id INNER JOIN VS_TRANSACTION ON p.sequence_no = VS_TRANSACTION.sequence_no INNER JOIN TR_TRANSACTION_TYPE ON VS_TRANSACTION.trn_type = TR_TRANSACTION_TYPE.id
WHERE (b.post_no = @PostingNumber) AND (VRS_PAYMENT_METHOD.pmt_type = 1)