Retrieving Check Info from the Select Database

[tim.pageler]

We are in the process of upgrading from Enterprise to Select v2.6. Currently, we have a nightly job that runs against the ProTrust database to query all of the checks that were issued during the day from across all of our Trust Accounts. The results returned from the query are then transformed into a format the bank can accept and transmitted via FTP. As we upgrade to Select, we would like to continue this process. Do you have any documentation on the best way for us to pull the check information out of the Select database in order to accomplish this? Any direction here would be helpful. Thanks.

[sindrapal]

Tim,

I am posting a code snippet here that shows how you can accomplish this through our API. Please let me know if you have any other questions.

NetworkCredential creds = new NetworkCredential("username", "password", "[SERVER]");
SelectServer selectServer = new SelectServer("http://localhost:8080", creds); // please plug-in your mid-tier address where it says 'http://localhost:8080'
selectServer.EnsureAuthenticated();

ITransactionsManager tm = (ITransactionsManager)selectServer.GetService(typeof(ITransactionsManager));

// query the transactions we are interested in
List<ITransactionInfo> infos = tm.Transactions
.Where(t => t.Kind == TransactionKind.Check && // Check transactions
t.Status == TransactionStatus.Posted && // posted checks
t.UserPostedOn == DateTime.Today && // posted today (transaction date)
t.IsCurrent && !t.IsDeleted) // look at the current version of the transaction (latest)
.ToList();

// Get the full version of the transactions
List<ICheckTransaction> checks = new List<ICheckTransaction>();
foreach (ITransactionInfo info in infos)
{
checks.Add((ICheckTransaction)tm.GetTransaction(info));
}

[tim.pageler]

Thanks for the response. This was very helpful.

[sindrapal]

Tim,

I really apologize for the confusion. The code snippet I posted is intended for V3.0. It looks like you are actually going to V2.6. Unfortunately, you have to hit the database directly in V2.6 to get the check information you are looking for.


You can use the following query to get all checks posted today. Hope this helps!

declare @today datetime
set @today = CONVERT(VARCHAR(8), GETDATE(), 112)

select t.*, eti.*
from dbo.[Transaction] as t
inner join dbo.[zrefTransactionType] as tt on tt.TransactionTypeID = t.TransactionTypeID
left outer join dbo.ExtendedTransactionInfo as eti on eti.ID = t.ExtendedInfoID -- other transaction details
where tt.Code = 'CHK'
and t.TransactionStatusID = 2 -- posted
and CONVERT(VARCHAR(8), t.TransactionDate, 112) = @today

[tim.pageler]

Based on previous conversations I had had with R&D, I kind of figured the code snippet was from v3.0. Fortunately, the object references resemble the database tables and I was able to deduce where to start. Lucky for me, your query confirmed that the work I had done this afternoon was not worthless. Thanks for the clarification.