Voided Transactions - What SQL Table Identifies This Status

Discussions related to custom development with Select.
Post Reply
GHart
Posts: 2
Joined: Thu Feb 04, 2016 2:37 pm

Voided Transactions - What SQL Table Identifies This Status

Post by GHart »

Can you please clarify the best location in the “pt” schema to identify a Voided Transaction? I see “VoidedOn” and “Status” (voided status) in pt.TransactionInfo and “IsVoided” in pt.TransactionExtended, but the data doesn’t appear to be consistent between them. Which is the best table to use?
sindrapal
Posts: 42
Joined: Tue Oct 14, 2008 9:21 am

Re: Voided Transactions - What SQL Table Identifies This Sta

Post by sindrapal »

The best table to use to identify if a transaction is voided is the pt.[Transaction] table. If the transaction is voided, the value in the [Status] column will be 4. Please refer to the zref.[TransactionStatus] table for a list of Status values.

-- Get the list of voided transactions
select top 10 *
from pt.[Transaction]
where [Status] = 4
and IsCurrent = 1
GHart
Posts: 2
Joined: Thu Feb 04, 2016 2:37 pm

Re: Voided Transactions - What SQL Table Identifies This Sta

Post by GHart »

Our situation is a little more in-depth that the original post described. We are trying to identify specific fees, and then add them together to determine which fees should be included or not, so that the end result of our report matches what is displayed on the SoftPro interface. A current rendition of our script is as shown at bottom of post. However, this although this matches the SoftPro interface with about 97% accuracy, there is some fallout that causes these numbers to be different than what the tool displays, sometimes at the detail of the individual fees, and sometimes the individual fees added together total the correct fee for our law firm, but the individual fees are not correct.

Code: Select all

SELECT
		RootID,
		Number,
		AttorneyFee + ClosingFee + EndorsementFee + PremiumFee + CurativeFee AS Our_Firm_Fee,
		AttorneyFee,
		ClosingFee,
		EndorsementFee,
		PremiumFee,
		CurativeFee
FROM
	(
	SELECT 
		OI.RootID,
		O.Number,
		Sum(CASE WHEN RD.BillCode = 'ATT' AND CD.Type <> 26 THEN RD.Amount ELSE 0 END) AS AttorneyFee,
		Sum(CASE WHEN RD.BillCode = 'CLO' AND CD.Type <> 26 THEN RD.Amount ELSE 0 END) AS ClosingFee,
		Sum(CASE WHEN RD.BillCode = 'END' AND CD.Type <> 26 THEN RD.Amount ELSE 0 END) AS EndorsementFee,
		Sum(CASE WHEN RD.BillCode = 'TP' AND CD.Type <> 26 THEN RD.Amount ELSE 0 END) AS PremiumFee,
		Sum(CASE WHEN RD.BillCode = 'CUR' AND CD.Type <> 26 THEN RD.Amount ELSE 0 END) AS CurativeFee
	FROM
		pt.RevenueDetail RD
		INNER JOIN pt.TransactionInfo TI ON RD.TransactionID = TI.ID
		INNER JOIN pt.[Transaction] T ON T.ID = TI.ID
		INNER JOIN pt.ContactDetail CD ON RD.OrderContact = CD.ID
		INNER JOIN pf.OrderInfo OI ON CD.OrderID = OI.Guid
		INNER JOIN pfm.[order] O on O.RootId# = OI.RootID
                -- INNER JOIN pt.Ledger AS L ON T.LedgerID=L.ID
		LEFT OUTER JOIN pt.TransactionExtended te ON te.ID = ti.Guid AND IsNull(te.IsVoided,0) = 0
	WHERE
		RD.BillCode IN ('ATT', 'CLO', 'END', 'TP','CUR')
		AND TI.VoidedOn IS NULL
		AND T.[Status] NOT in (4,6) -- 4 = Voided and 6 = Stop Payment
		--AND TE.ID is NULL
		--and o.Number = 'TPA13-39478C'
	GROUP BY
		OI.RootID,
		O.Number
	) tbl
sindrapal
Posts: 42
Joined: Tue Oct 14, 2008 9:21 am

Re: Voided Transactions - What SQL Table Identifies This Sta

Post by sindrapal »

When you void a transaction, new records are added to the RevenueDetail table to offset the original posting. So in your query you don't have to filter out voided adjustments. I may be wrong in understanding your use case. But please try running the modified query below and let me know if it doesn't give you what you need.

SELECT
RootID,
Number,
AttorneyFee + ClosingFee + EndorsementFee + PremiumFee + CurativeFee AS Our_Firm_Fee,
AttorneyFee,
ClosingFee,
EndorsementFee,
PremiumFee,
CurativeFee
FROM
(
SELECT
OI.RootID,
O.Number,
Sum(CASE WHEN RD.BillCode = 'ATT' AND CD.Type <> 26 THEN RD.Amount ELSE 0 END) AS AttorneyFee,
Sum(CASE WHEN RD.BillCode = 'CLO' AND CD.Type <> 26 THEN RD.Amount ELSE 0 END) AS ClosingFee,
Sum(CASE WHEN RD.BillCode = 'END' AND CD.Type <> 26 THEN RD.Amount ELSE 0 END) AS EndorsementFee,
Sum(CASE WHEN RD.BillCode = 'TP' AND CD.Type <> 26 THEN RD.Amount ELSE 0 END) AS PremiumFee,
Sum(CASE WHEN RD.BillCode = 'CUR' AND CD.Type <> 26 THEN RD.Amount ELSE 0 END) AS CurativeFee
FROM
pt.RevenueDetail RD
INNER JOIN pt.[Transaction] T ON T.ID = RD.TransactionID
INNER JOIN pt.ContactDetail CD ON RD.OrderContact = CD.ID
INNER JOIN pf.OrderInfo OI ON CD.OrderID = OI.Guid
INNER JOIN pfm.[order] O on O.RootId# = OI.RootID
WHERE
RD.BillCode IN ('ATT', 'CLO', 'END', 'TP','CUR')
GROUP BY
OI.RootID,
O.Number
) tbl
Post Reply