Voided Transactions - What SQL Table Identifies This Status
Voided Transactions - What SQL Table Identifies This Status
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?
Re: Voided Transactions - What SQL Table Identifies This Sta
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
-- Get the list of voided transactions
select top 10 *
from pt.[Transaction]
where [Status] = 4
and IsCurrent = 1
Re: Voided Transactions - What SQL Table Identifies This Sta
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
Re: Voided Transactions - What SQL Table Identifies This Sta
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
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