Pending Transactions Billcode in softpro 3.0/selecdb

Discussions related to Crystal Reports development.

Moderator: Lisa Ennis

Post Reply
smadderla
Posts: 16
Joined: Fri Nov 02, 2012 5:31 pm

Pending Transactions Billcode in softpro 3.0/selecdb

Post by smadderla »

Hi ,
I am working on Migrating our SoftPro 2.6 custom reports to SoftPro 3.0

I am trying to pull Fees by Bill code for Pending Transactions
It appears fees are in pt. Transaction table but I don't see Bill Code in this table
Please suggest how I can get bill codes for pending amounts in pt. Transaction table.

PS: I queried Pt. Revenue Detail table but I think this table contains only "Posted" transactions.


Thanks,
Sangeetha.
Lisa Ennis
Posts: 84
Joined: Thu Sep 11, 2008 1:57 pm

Re: Pending Transactions Billcode in softpro 3.0/selecdb

Post by Lisa Ennis »

The transaction source is stored in XML in the pt.TransactionSource table. The
statement below should get you started. Once a transaction is posted, it writes data to the pt.RevenueDetail table.

Code: Select all

SELECT
    A.ID AS TransactionID,
    A.GUID AS TransactionID_GUID,
    T.c.value('./@HUDLine', 'INT') AS HUDLine,
    T.c.value('./@BillCode', 'varchar(20)') AS BillCode,
    T.c.value('./@Amount', 'decimal(14,2)') AS Amount,
    T.c.value('./@Description', 'VARCHAR(200)') AS HUDLine,
    A.IsDebit,
    A.Amount AS TransactionAmount        
FROM 
    pt.[Transaction] A WITH (NOLOCK)
    INNER JOIN pt.TransactionSource TS WITH (NOLOCK) ON A.SourceID = TS.ID
    CROSS APPLY TS.Sources.nodes('/Sources/Source') T(c)
WHERE
    A.[Status] = 1 --pending
Lisa Ennis
Senior Report Developer
SoftPro
smadderla
Posts: 16
Joined: Fri Nov 02, 2012 5:31 pm

Re: Pending Transactions Billcode in softpro 3.0/selecdb

Post by smadderla »

Thanks for your help Lisa.
Post Reply