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)
Hi Alicia
T_PAYMENT and T_TRANSACTION have all the payments in them, unless there's something very wrong with your system.
But all Orders have a customer number on them - if the sale is to the general public, rather than a specific customer, it should have customer_no = 0. That can trip up joins sometimes, if you're not careful.
...If you post your code here, someone on the forums will probably spot the problem....
Ken