Moderator: Lisa Ennis
We recently moved from 2.6 to 3.0.
It appears that the 'IsCurrent' flag in pt.transaction is recommend to be: IsCurrent= 1.
I would like to know, how does the system determine or perhaps change 'IsCurrent=0 to IsCurrent=1 - essentially what is the trigger?
For the month of April, IsCurrent=1 brings back the values for the bill codes. However, for the month of May, values are not brought back for IsCurrent=1, but if you change it to IsCurrent=0 bill code values are returned, but also transactions I do not want to see, such as historical transactions, which makes me believe IsCurrent=1 only displays current data.
Ultimately, how do I get IsCurrent=1 to work for May?
I hope to help but I think I need more information. The IsCurrent flag on the transaction indicates that it is the transaction as it currently sits. All other records for the transaction other than the one with the is current flag are adjustments.
You mention Bill Codes. Are you trying to get a revenue report for May? The way revenue is handled in 3.0 is completely different from 2.6. The pt.RevenueDetail table is where you want to start for revenue and bill codes. In 2.6, we had to rely on the adjustments table determining the dates for revenue, and now all of those adjustments write to the pt.RevenueDetail table.
If I am offbase with what you need, let me know more about what your report and I will try to help.
Thank you for your quick response.
The report itself works fine in 3.0, for every month I spot checked, except for May 2015.
I went ahead and modified the report early yesterday from 2.6 to 3.0, to take account for the database changes, including the pt.RevenueDetail table.
The issue I find is that for May 2015, the exact same report in 3.0 which worked for April 2015 data, retrieved the bill code values is not retrieving the bill code values for May 2015 even though they exist.
From my troubleshooting, if I change IsCurrent=0, the bill code values are returned.
However, I do not wish to do this due to it retrieving other inaccurate data.
I was therefore curious as to why this would be?
Why is the system not "seeing" the bill code values as IsCurrent=1?
Any and all assistance is greatly appreciated!
SELECT TOP 1000 l.Name AS 'Ledger Name'
,t.[ID] AS 'TransactionID'
FROM [SelectDb].[pt].[Transaction] t
JOIN SelectDb.pt.Ledger l ON l.ID = t.LedgerID
WHERE t. ID IN (-2147217529, -2147217528, -2147139856, -2147139857)
AND l.Name = '130-151900088'
SELECT TOP 1000 l.Name
,t.[ID] AS 'transID'
,rd.ID AS 'DetailID'
FROM SelectDb.pt.Ledger l
JOIN SelectDb.pt.[Transaction] t ON t.LedgerID = l.ID
LEFT JOIN [SelectDb].[pt].[RevenueDetail] rd ON rd.TransactionID = t.ID
WHERE L.Name = '130-151900088'
AND (rd.BillCode IS NOT NULL OR rd.BillCode <> '')
AND t.ID IN (-2147217529, -2147227156)
In 3.0, the unlinked transaction is no more . All your data for revenue is now in the RevenueDetail table. If you make a change on the HUD line to an amount or a bill code, then the RevenueDetail table is updated with that information. We didn't have that in 2.6.
The RevenueDetail table has the TransactionID field. If you need the current record for that transaction, then join on the GUID and IsCurrent flag.
Code: Select all
pt.RevenueDetail AS RD INNER JOIN pt.Transaction AS TA on RD.TransactionID=TA.ID INNER JOIN pt.Transaction AS T on TA.GUID=T.GUID AND T.IsCurrent=1