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
)
Add Transaction to ledger Using SPROC
Moderator: Phil Barton
-
- Posts: 411
- Joined: Thu Sep 11, 2008 11:35 am
- Location: Raleigh, NC, USA
- Contact:
Re: Add Transaction to ledger Using SPROC
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.
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
Sr. Software Architect
SoftPro