Hi,
I am working on creating a custom report, to get closed orders and its revenue
I need to pull the revenue irrespective of its Transaction Status (Pending or Posted)
Following is what I have for getting the revenue of Escrow orders, this is only giving me revenue which is posted (Transaction Status == Posted)
LEFT JOIN dbo.Ledger(NOLOCK) AS L ON dbo.[Order].ID = L.OrderID
LEFT JOIN dbo.[Transaction] (NOLOCK) AS T ON L.ID = T.LedgerID
LEFT JOIN dbo.Adjustment(NOLOCK) AS A ON T.LedgerID = A.LedgerID
JOIN dbo.Escrow (NOLOCK) AS E ON dbo.[Order].ID=E.OrderID
INNER JOIN dbo.zrefTransactionStatus AS zTT ON A.TransactionStatusID=zTT.ID
LEFT JOIN dbo.TransactionHUDLineAndAmount(NOLOCK) AS THLA ON A.TransactionID = THLA.TransactionID
Thanks,
Sangeetha.
Cutom Revenue Report
Moderator: Lisa Ennis
-
- Posts: 84
- Joined: Thu Sep 11, 2008 1:57 pm
Re: Cutom Revenue Report
You do not need a join to the Adjustment table at all for the 'Pending' transactions. I would recommend using a UNION statement where you first get all the posted transactions and their adjustments, and then UNION a SELECT statement that gets the pending transactions. Below is code from custom reports I have completed for SoftPro customers to get both posted and pending revenue transactions. Hope this helps. Have a great day.
Joins for getting the posted revenue:
dbo.TransactionHUDLineAndAmount AS THLA
INNER JOIN dbo.Adjustment AS A ON A.TransactionID=THLA.TransactionID
INNER JOIN dbo.TransactionType AS TT ON A.TransactionTypeID=TT.ID AND TT.IsDebit=1
INNER JOIN dbo.zrefTransactionStatus AS zTT ON A.TransactionStatusID=zTT.ID
INNER JOIN dbo.zrefAdjustmentType AS zAT ON A.AdjustmentTypeID=zAT.ID
INNER JOIN dbo.[Transaction] AS T ON THLA.TransactionID=T.ID
INNER JOIN dbo.OrderContact AS OC ON T.OrderContactID=OC.ID AND OC.IsLinkedDisb=1
INNER JOIN dbo.Ledger AS L ON T.LedgerID=L.ID
Joins for pending transactions:
dbo.TransactionHUDLineAndAmount AS THLA
INNER JOIN dbo.[Transaction] AS T ON THLA.TransactionID=T.ID
INNER JOIN dbo.zrefTransactionStatus AS zTT ON T.TransactionStatusID=zTT.ID AND zTT.Description = 'Pending'
INNER JOIN dbo.TransactionType AS TT ON T.TransactionTypeID=TT.ID AND TT.IsDebit=1
INNER JOIN dbo.OrderContact AS OC ON T.OrderContactID=OC.ID AND OC.IsLinkedDisb=1
INNER JOIN dbo.Ledger AS L ON T.LedgerID=L.ID
Joins for getting the posted revenue:
dbo.TransactionHUDLineAndAmount AS THLA
INNER JOIN dbo.Adjustment AS A ON A.TransactionID=THLA.TransactionID
INNER JOIN dbo.TransactionType AS TT ON A.TransactionTypeID=TT.ID AND TT.IsDebit=1
INNER JOIN dbo.zrefTransactionStatus AS zTT ON A.TransactionStatusID=zTT.ID
INNER JOIN dbo.zrefAdjustmentType AS zAT ON A.AdjustmentTypeID=zAT.ID
INNER JOIN dbo.[Transaction] AS T ON THLA.TransactionID=T.ID
INNER JOIN dbo.OrderContact AS OC ON T.OrderContactID=OC.ID AND OC.IsLinkedDisb=1
INNER JOIN dbo.Ledger AS L ON T.LedgerID=L.ID
Joins for pending transactions:
dbo.TransactionHUDLineAndAmount AS THLA
INNER JOIN dbo.[Transaction] AS T ON THLA.TransactionID=T.ID
INNER JOIN dbo.zrefTransactionStatus AS zTT ON T.TransactionStatusID=zTT.ID AND zTT.Description = 'Pending'
INNER JOIN dbo.TransactionType AS TT ON T.TransactionTypeID=TT.ID AND TT.IsDebit=1
INNER JOIN dbo.OrderContact AS OC ON T.OrderContactID=OC.ID AND OC.IsLinkedDisb=1
INNER JOIN dbo.Ledger AS L ON T.LedgerID=L.ID
Lisa Ennis
Senior Report Developer
SoftPro
Senior Report Developer
SoftPro
Re: Cutom Revenue Report
Thanks for your help Lisa.