Transaciton History

Discussions related to Crystal Reports development.

Moderator: Lisa Ennis

Post Reply
cgriffin
Posts: 22
Joined: Mon Nov 17, 2014 6:14 pm

Transaciton History

Post by cgriffin »

Good evening,

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?

Thank you,
Colin.
Lisa Ennis
Posts: 84
Joined: Thu Sep 11, 2008 1:57 pm

Re: Transaciton History

Post by Lisa Ennis »

Hi Colin,
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.
Lisa Ennis
Senior Report Developer
SoftPro
cgriffin
Posts: 22
Joined: Mon Nov 17, 2014 6:14 pm

Re: Transaciton History

Post by cgriffin »

Good morning Lisa,

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!

Thank you,
Colin.
cgriffin
Posts: 22
Joined: Mon Nov 17, 2014 6:14 pm

Re: Transaciton History

Post by cgriffin »

Here is some additional detail on one transaction that I would expect to be on the May revenue report for our Minnesota office. I am not sure if this is indicative of every transaction, but I thought I would share in the hopes that we can better understand how to pull revenue. The order in question is 130-151900008. Looking at the transaction table, I found two transactions for this order. The original transaction was voided then a posted transaction. As you can see from the first screenshot, the current transaction has a TransactionID of -2147139856. When I went to the revenue detail table, I did not find corresponding records for this transaction id. However, I did find corresponding entries in the revenue detail table for the original transaction. Any advice would be greatly appreciative.

SELECT TOP 1000 l.Name AS 'Ledger Name'
,t.[ID] AS 'TransactionID'
,[Amount]
,[AdjustmentKind]
,[Reason]
,[SequenceNumber]
,[AmountDifference]
,[RelatedTransactionID]
,[IsCurrent]
,[IsDeleted]
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'
,t.Amount
,rd.ID AS 'DetailID'
,rd.[TransactionID]
,rd.[RevenueDate]
,rd.[HUDLine]
,rd.[Amount]
,rd.[BillCode]
,rd.[ChargeSource]
,rd.[ChargeDescription]
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)
Lisa Ennis
Posts: 84
Joined: Thu Sep 11, 2008 1:57 pm

Re: Transaciton History

Post by Lisa Ennis »

Data for May may be different since you recently upgraded. In 2.6, the revenue data was capture when the transaction was posted in the TransactionHUDLineAndAmount table. Once the transaction was posted, if revisions were made on the HUD, or the transaction was changed that affected the posted transaction, the transaction became unliked.

In 3.0, the unlinked transaction is no more :D. 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
Hope this help!
Lisa Ennis
Senior Report Developer
SoftPro
Post Reply