SoftPro Developer Forum

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
)

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.