Add Transaction to ledger Using SPROC

Discussions related to writing and managing custom business rules.

Moderator: Phil Barton

Post Reply
tlyde_LCTMHS
Posts: 8
Joined: Thu Jul 18, 2013 12:21 pm

Add Transaction to ledger Using SPROC

Post by tlyde_LCTMHS » Fri Aug 08, 2014 5:39 pm

We need some help with a stored procedure. We are trying to create a transaction for a ledger.
I did not see any existing stored procedures for creating a transaction so I combined a few things.
I insert a transaction into the Transaction table for the existing ledger, then add a ExtendedInfo item and an Adjustment.
I would like some help getting the attached sproc correct. I can not figure out how to get the next Reference Number for the transaction.

Thanks,

Stored procedure below:

ALTER PROCEDURE [dbo].[LedgerTransactionCreate]
@WireID varchar(100),
@ClearedDate datetime,
@Amount decimal(14,2),
@Memo varchar(255),
@PayorPayee varchar(255),
@LedgerID uniqueidentifier,
@TrustAccountID uniqueidentifier,
@FedReferenceNumber varchar(50),
@userID varchar(30),
@ledgerName varchar(30)
--@NewDBRowVersion rowversion output,
--@RowsAffected int output

AS
BEGIN

-- Need to create a new transaction on the ledger. ?? Use [TransactionSave], [TrustAccountAutoNumberingGetNextReferenceNumber]

DECLARE @TransactionTypeID uniqueidentifier = '372C2853-1774-4A6C-AA8A-A64E12625A6F' -- incoming wire
DECLARE @ReferenceNumber bigint
DECLARE @TransactionStatusID smallint = 2 -- 1=pending, 2=posted
DECLARE @MediumID smallint = 12 -- wire
DECLARE @rowsChanged int
DECLARE @newRowVersion rowversion
DECLARE @OverriddenDataItems varbinary(max) = 0x00
DECLARE @UserEnteredFlags varbinary(128) = 0xFE
DECLARE @ExtendedInfoID uniqueidentifier = NEWID()
DECLARE @TransactionID uniqueidentifier = NEWID()

SET @rowsChanged = 0

INSERT INTO [SelectDb].[dbo].[Transaction]
(
[ID],
ZIP,
TransactionDate,
TrustAccountingDate,
TransferToFromID,
ClearedDate,
Address1,
Amount,
ExtendedInfoID,
ExtendedMemo,
TransactionTypeID,
ReferenceNumber,
Memo,
PayorPayee,
Address2,
ForeignAddress,
UseForeignAddress,
State,
ParentTransactionID,
TransactionStatusID,
OrderContactID,
LedgerID,
TrustAccountID,
MediumID,
City,
Hud1AmountID,
IsProFormGenerated,
Hud,
IsDeleted,
IsTransferredDisbursement,
UserEnteredFlags,
OverriddenDataItems,
Formulas,
CustomFields,
IBA_ID,
ToIBA,
IsDraft,
DraftAuthorizationCode,
SelectedAddressContactID,
IsBankChangeSysGeneratedTrans,
LastSaved
)
VALUES
(
@TransactionID,
null,
GETDATE(),
GETDATE(),
null,
@ClearedDate,
null,
@Amount,
@ExtendedInfoID,
null,
@TransactionTypeID,
@ReferenceNumber,
@Memo,
@PayorPayee,
null,
null,
0,
null,
null,
@TransactionStatusID,
null,
@LedgerID,
@TrustAccountID,
@MediumID,
null,
null,
0,
null,
0,
0,
@UserEnteredFlags,
@OverriddenDataItems,
null, --@Formulas,
null,
null,
null,
0,
null,
null,
0,
getdate()
)

SET @rowsChanged = @@ROWCOUNT
SET @newRowVersion = @@DBTS

--SET @RowsAffected = @rowsChanged
--SET @NewDBRowVersion = @newRowVersion

-- add code to insert row into the extendedtransactioninfo table
INSERT INTO [SelectDb].[dbo].[ExtendedTransactionInfo]
(
[ID]
,[CreditAccountName]
,[FromCheck]
,[AccountNumber]
,[BankDrawnOn]
,[ABARoutingNumber]
,[Contact]
,[Comments]
,[FedReferenceNumber]
,[BankSequenceNumber]
,[Reference]
,[UserEnteredFlags]
,[OverriddenDataItems]
,[Formulas]
,[CustomFields]
,[SpecialInstructions]
,[FurtherCredit]
)
VALUES
(
@ExtendedInfoID,
null,
null,
null,
null,
null,
null,
null,
@FedReferenceNumber,
null,
null,
0x,
0x,
null,
null,
null,
null
)

-- add code to insert row into the adjustment table
INSERT INTO [SelectDb].[dbo].[Adjustment]
(
[ID],
AdjustmentTypeID,
ClearedDate,
TrusteeID,
AmountDifference,
Address1,
TransactionStatusID,
Address2,
ForeignAddress,
UseForeignAddress,
TransactionTypeID,
ZIP,
State,
ExtendedInfoID,
ReferenceNumber,
TrustAccountID,
Reason,
TrustAccountingDate,
MediumID,
TransactionDate,
Amount,
TransferToFromID,
PayorPayee,
TransactionID,
City,
ExtendedMemo,
SequenceNumber,
Memo,
LedgerID,
IsCreatedFromBusinessRule,
UserEnteredFlags,
OverriddenDataItems,
Sources,
Formulas,
CustomFields,
IBA_ID,
IsDraft
)
VALUES
(
NEWID(),
1,
@ClearedDate,
'D32365C7-6224-E211-8014-0024E8936175', -- morrisadmin
@Amount,
null,
@TransactionStatusID,
null,
null,
0,
@TransactionTypeID,
null,
null,
@ExtendedInfoID,
@ReferenceNumber,
@TrustAccountID,
'Assign wire to ledger',
GETDATE(),
@MediumID,
GETDATE(),
@Amount,
null,
@PayorPayee,
@TransactionID,
null,
null,
null,
null,
@LedgerID,
0,
null,
null,
null,
null,
null,
null,
0
)

John Morris
Posts: 411
Joined: Thu Sep 11, 2008 11:35 am
Location: Raleigh, NC, USA
Contact:

Re: Add Transaction to ledger Using SPROC

Post by John Morris » Wed Aug 13, 2014 3:45 pm

Manipulating the database records directly is not supported.

In the new v3.0 version of Select, there is a publicly supported API (.net based) for interacting with most all aspects of the application, including transactions. That is the proper was to accomplish this task.
John Morris
Sr. Software Architect
SoftPro

Post Reply