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.
Pending Transactions Billcode in softpro 3.0/selecdb
Moderator: Lisa Ennis
-
- Posts: 84
- Joined: Thu Sep 11, 2008 1:57 pm
Re: Pending Transactions Billcode in softpro 3.0/selecdb
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.
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
Senior Report Developer
SoftPro
Re: Pending Transactions Billcode in softpro 3.0/selecdb
Thanks for your help Lisa.