sqlbridge_example_-_inserting_a_payment [Control]

WARNING: If you are interested in using sql_bridge, please contact a sales or implementation consultant at Cyrious.
Cyrious Control's sql_bridge is a collection of SQL Server stored prodedures and functions and external modules that can be called to safely insert or update data into the Control database and notify the CHAPI service that data has been updated. The CHAPI service is a controller program that will then notify the SSLIP and all copies of Control about the changes.
Caution: Use of this feature requires expertise in SQL Server and in the Cyrious database structure. It is very possible to irreversibly damage your Cyrious Control database if you make a mistake. Always develop your tests using a separate database on a separate machine.
Caution: Always wrap multi-record operations with SQL transaction statements. This ensure the integrity of the entire update by preventing partial changes or records from being saved. If you are not familiar with using SQL transactions, STOP! Do not proceed with any database insertions, deletions, or updates until you are.
Example

This example inserts a payment into the database for a particular order. It does NOT validate the payment amount, payment account, or status of the order. These are left to the programmer.
-- Sample Procedure to Add a Payment received on an Outside Gateway to an existing Order
-- Step 0, Fill in initial data
DECLARE @OrderNumber INT = 185956; -- NULL;
DECLARE @PaymentAmount money = 01.00;
DECLARE @PaymentDT datetime = GetDate();
DECLARE @EmployeeID INT = 10;       -- must be an ID from the Employee table
DECLARE @PaymentMethodID INT =  92; -- must be a valid ID from PaymentAccount table (e.g., Visa, Check)
DECLARE @GLPaymentAccountID INT = 91; -- must be an ID from GLAccount where GLClassificationType = 1007
DECLARE @DivisionID INT = 10;           -- must be an ID from the Division View
-- Step 1, Retrieve Existing IDs and Information
DECLARE @OrderID INT;
DECLARE @OrderStatusID INT;
DECLARE @AccountID INT;
DECLARE @ContactID INT;
DECLARE @OrderSeqNo INT;
SELECT  @OrderID = ID ,
        @OrderStatusID = StatusID,
        @AccountID = AccountID,
        @ContactID = ContactID,
        @OrderSeqNo = SeqID
FROM TransHeader
WHERE OrderNumber = @OrderNumber AND TransactionType IN (1,6);
DECLARE @NewSeqNo INT;
SET @NewSeqNo = @OrderSeqNo+1;
DECLARE @CompanyName VARCHAR(64) = (SELECT CompanyName FROM Account WHERE ID = @AccountID);
-- in case we need them
DECLARE @ErrorMessage    VARCHAR(2048);
DECLARE @ErrorNumber     INT;
DECLARE @ErrorSeverity   INT;
DECLARE @ErrorState      INT;
DECLARE @ErrorLine       INT;
DECLARE @ErrorProcedure  VARCHAR(200);
-- Step 2, Lock the Order
BEGIN try
    EXEC dbo.csf_chapi_lock @OrderID, 10000;
END try
BEGIN catch
    SET @ErrorMessage = 'Unable to lock Order ID '+CONVERT(VARCHAR(16), @OrderID);
    -- Assign variables to error-handling functions that
    -- capture information for RAISERROR.
    SELECT
        @ErrorNumber = ERROR_NUMBER(),
        @ErrorSeverity = ERROR_SEVERITY(),
        @ErrorState = ERROR_STATE(),
        @ErrorLine = ERROR_LINE(),
        @ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');
    -- Build the message string that will contain original
    -- error information.
    SELECT @ErrorMessage =
        N'Error %d, Level %d, State %d, Procedure %s, Line %d, ' +
            'Message: '+ ERROR_MESSAGE();
    -- Raise an error: msg_str parameter of RAISERROR will contain
    -- the original error information.
    RAISERROR
        (
        @ErrorMessage,
        @ErrorSeverity,
        1,
        @ErrorNumber,    -- parameter: original error number.
        @ErrorSeverity,  -- parameter: original error severity.
        @ErrorState,     -- parameter: original error state.
        @ErrorProcedure, -- parameter: original error procedure name.
        @ErrorLine       -- parameter: original error line number.
        );
END catch;
-- Step 3, Request New IDs
DECLARE @MasterPaymentID INT = (SELECT dbo.csf_chapi_nextid(20000, 1));
DECLARE @DetailPaymentID INT = (SELECT dbo.csf_chapi_nextid(20001, 1));
DECLARE @LedgerID        INT = (SELECT dbo.csf_chapi_nextid(8900, 2));
DECLARE @GLGroupID INT = @LedgerID; -- (select dbo.csf_chapi_nextid(-9, 2));
-- Step 4, Insert the new Records (in a transaction) and update the Order Total
BEGIN TRANSACTION
   BEGIN TRY
        -- Insert Master Record into Journal
 
        INSERT INTO [Journal] ([ID], [StoreID], [ClassTypeID] ,[ModifiedByUser] ,[ModifiedByComputer] ,[ModifiedDate] ,[SeqID] ,[IsSystem] ,[IsActive] ,[EmployeeID],[JournalActivityType] ,[JournalActivityText] ,[Description] ,[Notes] ,[StartDateTime] ,[EndDateTime] ,[TotalTime] ,[ScheduledDateTime]  ,[CompletedByID] ,[CompletedDateTime] ,[IsSummary] ,[IsDetail] ,[SummaryID] ,[SummaryClassTypeID] ,[SummaryAmount] ,[DetailAmount]
        ,[StartGLGroupID]  ,[EndGLGroupID]  ,[AccountID] ,[AccountClassTypeID]  ,[ContactID]  ,[ContactClassTypeID]  ,[TransactionID] ,[TransactionClassTypeID]  ,[IsVoided]  ,[VoidedDateTime]  ,[VoidedEntryID] ,[VoidedEntryClassTypeID]  ,[VoidedReason]  ,[QueryStartDateTime]
        ,[QueryEndDateTime]  ,[ReminderDateTime]  ,[ReminderPrompt]  ,[PartID]  ,[ActivityType]  ,[ActivityTypeText]  ,[IsBillable]   ,[BillableDateTime]  ,[UseActualTime]  ,[BillingNotes]  ,[BillingType]  ,[TotalBilledTime]  ,[RecurringActivityID]  ,[LinkID]  ,[LinkStoreID]
        ,[LinkClassTypeID] ,[SpecialCode] ,[DivisionID] ,[HasCalendarLinks] ,[TipRecipientID] ,[PartClassTypeID] ,[RecurringClassTypeID] ,[StationID] ,[StationClassTypeID] ,[CurrentState] ,[StageID] ,[StageClassTypeID])
 
        VALUES
            ( @MasterPaymentID     -- (
            , -1     -- ,
            , 20000     -- ,
            , 'SQLBridge'     -- ,
            , @@ServerName     -- ,
            ,  GetDate()    -- ,
            ,  0    -- ,
            ,  0    -- ,
            ,  1    -- ,
           , @EmployeeID  --
           , 2  --
           , 'Payment'  --
           , 'Master Payment for '+@CompanyName  --
           , 'Inserted by SQLBridge'  --
           , @PaymentDT  --
           , @PaymentDT  --
           , NULL  --
           , NULL  --
           , @EmployeeID  --
           , @PaymentDT  --
           , 1  --
           , 0  --
           , NULL  --
           , NULL  --
           , @PaymentAmount  --
           , 0  --
           , @GLGroupID  --
           , @GLGroupID  --
           , @AccountID  --
           , 2000  --
           , @ContactID  --
           , 3000  --
           , NULL  --
           , NULL  --
           , 0  --
           , NULL  --
           , NULL  --
           , NULL  --
           , NULL  --
           , @PaymentDT  --
           , @PaymentDT  --
           , NULL  --
           , 0  --
           , NULL  --
           , 0  --
           , NULL  --
           , 0  --
           , NULL  --
           , 0  --
           , NULL  --
           , 0  --
           , 0  --
           , NULL  --
           , NULL  --
           , NULL  --
           , NULL  --
           , NULL  --
           , @DivisionID  --
           , 0  --
           , NULL  --
           , NULL  --
           , NULL  --
           , NULL  --
           , NULL  --
           , NULL  --
           , NULL  --
           , NULL  -- )
        );
 
        -- Insert Master Record into Payment Table
        INSERT INTO [Payment]
        ([ID] ,[StoreID] ,[ClassTypeID] ,[ModifiedByUser] ,[ModifiedByComputer] ,[ModifiedDate] ,[SeqID] ,[IsSystem] ,[IsActive] ,[NameOnCard] ,[TrackingNumber] ,[PaymentAccountID] ,[PaymentAccountClassTypeID] ,[ExpirationDate] ,[VCode] ,[IsCCNumEncrypt] ,[DisplayNumber] ,[IsVCodeEncrypted] ,[Undeposited] ,[DepositGLGroupID] ,[DepositJournalID] ,[PaymentDate] ,[BankAccountID] ,[BankAccountClasstypeID] ,[TenderType] ,[PayrollID] ,[PayrollClassTypeID] ,[PaycheckID] ,[BankReference] ,[BankCode] ,[BranchCode] ,[CIN] ,[State] ,[CCAccount]
        ,[CCCSTransactionGuid] ,[CCCSCustomerGuid] ,[IsActualCheck])
        VALUES
            ( @MasterPaymentID     -- (
            , -1     -- ,
            , 20000     -- ,
            , 'SQLBridge'     -- ,
            , @@ServerName     -- ,
            , GetDate()    -- ,
            , 0    -- ,
            , 0    -- ,
            , 1    -- ,
            , ''     -- ,
            , NULL     -- ,
            , @PaymentMethodID     -- ,
            , 8002     -- ,
            , NULL     -- ,
            , NULL     -- ,
            , 0     -- ,
            , 'SQLBridge Entry'     -- ,
            , NULL     -- ,
            , 0        -- ,
            , NULL     -- ,
            , NULL     -- ,
            , @PaymentDT     -- ,
            , @GLPaymentAccountID     -- ,
            , 8001     -- ,
            , 2     -- ,
            , NULL     -- ,
            , NULL     -- ,
            , NULL     -- ,
            , NULL     -- ,
            , NULL     -- ,
            , NULL     -- ,
            , NULL     -- ,
            , NULL     -- ,
            , NULL     -- ,
            , NULL     -- ,
            , NULL     -- ,
            , 0     -- ,)
        );
        -- Insert Detail Record into Journal
 
        INSERT INTO [Journal] ([ID], [StoreID], [ClassTypeID] ,[ModifiedByUser] ,[ModifiedByComputer] ,[ModifiedDate] ,[SeqID] ,[IsSystem] ,[IsActive] ,[EmployeeID],[JournalActivityType] ,[JournalActivityText] ,[Description] ,[Notes] ,[StartDateTime] ,[EndDateTime] ,[TotalTime] ,[ScheduledDateTime]  ,[CompletedByID] ,[CompletedDateTime] ,[IsSummary] ,[IsDetail] ,[SummaryID] ,[SummaryClassTypeID] ,[SummaryAmount] ,[DetailAmount]
        ,[StartGLGroupID]  ,[EndGLGroupID]  ,[AccountID] ,[AccountClassTypeID]  ,[ContactID]  ,[ContactClassTypeID]  ,[TransactionID] ,[TransactionClassTypeID]  ,[IsVoided]  ,[VoidedDateTime]  ,[VoidedEntryID] ,[VoidedEntryClassTypeID]  ,[VoidedReason]  ,[QueryStartDateTime]
        ,[QueryEndDateTime]  ,[ReminderDateTime]  ,[ReminderPrompt]  ,[PartID]  ,[ActivityType]  ,[ActivityTypeText]  ,[IsBillable]   ,[BillableDateTime]  ,[UseActualTime]  ,[BillingNotes]  ,[BillingType]  ,[TotalBilledTime]  ,[RecurringActivityID]  ,[LinkID]  ,[LinkStoreID]
        ,[LinkClassTypeID] ,[SpecialCode] ,[DivisionID] ,[HasCalendarLinks] ,[TipRecipientID] ,[PartClassTypeID] ,[RecurringClassTypeID] ,[StationID] ,[StationClassTypeID] ,[CurrentState] ,[StageID] ,[StageClassTypeID])
 
        VALUES
            ( @DetailPaymentID     -- (
            , -1     -- ,
            , 20001     -- ,
            , 'SQLBridge'     -- ,
            , @@ServerName     -- ,
            ,  GetDate()    -- ,
            ,  0    -- ,
            ,  0    -- ,
            ,  1    -- ,
           , @EmployeeID  --
           , 2  --
           , 'Payment'  --
           , 'Order Payment for '+@CompanyName  --
           , 'Inserted by SQLBridge'  --
           , @PaymentDT  --
           , @PaymentDT  --
           , NULL  --
           , NULL  --
           , @EmployeeID  --
           , @PaymentDT  --
           , 0  --
           , 1  --
           , @MasterPaymentID  --
           , 20000  --
           , 0  --
           , @PaymentAmount  --
           , @GLGroupID  --
           , @GLGroupID  --
           , @AccountID  --
           , 2000  --
           , @ContactID  --
           , 3000  --
           , @OrderID  --
           , 10000  --
           , 0  --
           , NULL  --
           , NULL  --
           , NULL  --
           , NULL  --
           , @PaymentDT  --
           , @PaymentDT  --
           , NULL  --
           , 0  --
           , NULL  --
           , 0  --
           , NULL  --
           , 0  --
           , NULL  --
           , 0  --
           , NULL  --
           , 0  --
           , 0  --
           , NULL  --
           , NULL  --
           , NULL  --
           , NULL  --
           , NULL  --
           , @DivisionID  --
           , 0  --
           , NULL  --
           , NULL  --
           , NULL  --
           , NULL  --
           , NULL  --
           , NULL  --
           , NULL  --
           , NULL  -- )
        );
        -- Insert Detail Record into Payment Table
        INSERT INTO [Payment]
        ([ID] ,[StoreID] ,[ClassTypeID] ,[ModifiedByUser] ,[ModifiedByComputer] ,[ModifiedDate] ,[SeqID] ,[IsSystem] ,[IsActive] ,[NameOnCard] ,[TrackingNumber] ,[PaymentAccountID] ,[PaymentAccountClassTypeID] ,[ExpirationDate] ,[VCode] ,[IsCCNumEncrypt] ,[DisplayNumber] ,[IsVCodeEncrypted] ,[Undeposited] ,[DepositGLGroupID] ,[DepositJournalID] ,[PaymentDate] ,[BankAccountID] ,[BankAccountClasstypeID] ,[TenderType] ,[PayrollID] ,[PayrollClassTypeID] ,[PaycheckID] ,[BankReference] ,[BankCode] ,[BranchCode] ,[CIN] ,[State] ,[CCAccount]
        ,[CCCSTransactionGuid] ,[CCCSCustomerGuid] ,[IsActualCheck])
        VALUES
            ( @DetailPaymentID     -- (
            , -1     -- ,
            , 20001     -- ,
            , 'SQLBridge'     -- ,
            , @@ServerName     -- ,
            , GetDate()    -- ,
            , 0    -- ,
            , 0    -- ,
            , 1    -- ,
            , ''     -- ,
            , NULL     -- ,
            , @PaymentMethodID     -- ,
            , 8002     -- ,
            , NULL     -- ,
            , NULL     -- ,
            , 0     -- ,
            , 'SQLBridge Entry'     -- ,
            , NULL     -- ,
            , 0        -- ,
            , NULL     -- ,
            , NULL     -- ,
            , @PaymentDT     -- ,
            , @GLPaymentAccountID     -- ,
            , 8001     -- ,
            , 2     -- ,
            , NULL     -- ,
            , NULL     -- ,
            , NULL     -- ,
            , NULL     -- ,
            , NULL     -- ,
            , NULL     -- ,
            , NULL     -- ,
            , NULL     -- ,
            , NULL     -- ,
            , NULL     -- ,
            , NULL     -- ,
            , 0     -- ,)
        );
        -- Create GL Entry #1 for Payment Account
        INSERT INTO [Ledger]
        ([ID] ,[StoreID] ,[ClassTypeID] ,[ModifiedByUser] ,[ModifiedByComputer] ,[ModifiedDate] ,[SeqID] ,[IsSystem] ,[IsActive] ,[EntryDateTime] ,[Amount] ,[Classification] ,[IsTaxable] ,[GroupID] ,[GLAccountID] ,[GLAccountClassTypeID] ,[AccountID] ,[AccountClassTypeID] ,[TransactionID] ,[TransactionClassTypeID] ,[TransDetailID] ,[TransDetailClassTypeID] ,[GoodsItemID] ,[GoodsItemClassTypeID] ,[Description] ,[DivisionID] ,[Notes] ,[IsModified] ,[IsUser] ,[TaxClassID] ,[Quantity] ,[PartID] ,[PartClassTypeID] ,[JournalID] ,[JournalClassTypeID] ,[Reconciled] ,[ReconciliationDateTime] ,[ReconciliationID] ,[ReconciliationClassTypeID] ,[ProcessedDivisionID] ,[GLClassificationType] ,[GLClassTypeName] ,[TransPartID] ,[TransPartClassTypeID] ,[StationID] ,[PayrollID] ,[PayrollClassTypeID] ,[DepositJournalID] ,[EntryType] ,[EmployeeID] ,[OffBalanceSheet] ,[WarehouseID] ,[InventoryID])
 
        VALUES
            ( @LedgerID   --
            , -1     -- ,
            , 8900     -- ,
            , 'SQLBridge'     -- ,
            , @@ServerName     -- ,
            , GetDate()    -- ,
            , 0    -- ,
            , 0    -- ,
            , 1    -- ,
            , @PaymentDT   --
            , @PaymentAmount   --
            , @PaymentMethodID   --
            , 0   --
            , @GLGroupID   --
            , @GLPaymentAccountID   --
            , 8001   --
            , @AccountID   --
            , 2000   --
            , @OrderID   --
            , 10000   --
            , NULL   --
            , NULL   --
            , NULL   --
            , NULL   --
            , 'Order Payment (sql_bridge)'   --
            , @DivisionID   --
            , NULL   --
            , 0   --
            , 0   --
            , NULL   --
            , 0   --
            , NULL   --
            , NULL   --
            , @DetailPaymentID   --
            , 20001   --
            , 0   --
            , NULL   --
            , NULL   --
            , NULL   --
            , @DivisionID   --
            , (SELECT GLClassificationType FROM GLAccount WHERE ID = @GLPaymentAccountiD) --
            , (SELECT GLClassTypeName FROM GLAccount WHERE ID = @GLPaymentAccountiD)   --
            , NULL   --
            , NULL   --
            , NULL   --
            , NULL   --
            , NULL   --
            , NULL   --
            , 3   --
            , NULL   --
            , 0   --
            , NULL   --
            , NULL   --
        )
        -- Create GL Entry #2 for Offset to Payments
        -- If in WIP or Built, Offset is Customer Deposits (24)
        -- Otherwise, Offset is A/Rs (14)
        DECLARE @GLAccountOffset INT = (CASE WHEN @OrderStatusID IN (1,2) THEN 24 ELSE 14 END);
        INSERT INTO [Ledger]
        ([ID] ,[StoreID] ,[ClassTypeID] ,[ModifiedByUser] ,[ModifiedByComputer] ,[ModifiedDate] ,[SeqID] ,[IsSystem] ,[IsActive] ,[EntryDateTime] ,[Amount] ,[Classification] ,[IsTaxable] ,[GroupID] ,[GLAccountID] ,[GLAccountClassTypeID] ,[AccountID] ,[AccountClassTypeID] ,[TransactionID] ,[TransactionClassTypeID] ,[TransDetailID] ,[TransDetailClassTypeID] ,[GoodsItemID] ,[GoodsItemClassTypeID] ,[Description] ,[DivisionID] ,[Notes] ,[IsModified] ,[IsUser] ,[TaxClassID] ,[Quantity] ,[PartID] ,[PartClassTypeID] ,[JournalID] ,[JournalClassTypeID] ,[Reconciled] ,[ReconciliationDateTime] ,[ReconciliationID] ,[ReconciliationClassTypeID] ,[ProcessedDivisionID] ,[GLClassificationType] ,[GLClassTypeName] ,[TransPartID] ,[TransPartClassTypeID] ,[StationID] ,[PayrollID] ,[PayrollClassTypeID] ,[DepositJournalID] ,[EntryType] ,[EmployeeID] ,[OffBalanceSheet] ,[WarehouseID] ,[InventoryID])
 
        VALUES
            ( @LedgerID+1   --
            , -1     -- ,
            , 8900     -- ,
            , 'SQLBridge'     -- ,
            , @@ServerName     -- ,
            , GetDate()    -- ,
            , 0    -- ,
            , 0    -- ,
            , 1    -- ,
            , @PaymentDT   --
            , -@PaymentAmount   --
            , 0   --
            , 0   --
            , @GLGroupID   --
            , @GLAccountOffset  --
            , 8001   --
            , @AccountID   --
            , 2000   --
            , @OrderID   --
            , 10000   --
            , NULL   --
            , NULL   --
            , NULL   --
            , NULL   --
            , 'Order Payment (sql_bridge)'   --
            , @DivisionID   --
            , NULL   --
            , 0   --
            , 0   --
            , NULL   --
            , 0   --
            , NULL   --
            , NULL   --
            , @DetailPaymentID   --
            , 20001   --
            , 0   --
            , NULL   --
            , NULL   --
            , NULL   --
            , @DivisionID   --
            , (CASE WHEN @GLAccountOffset = 24 THEN 2002 ELSE 1002 END) --
            , (CASE WHEN @GLAccountOffset = 24 THEN 'Current Asset' ELSE 'Current Liability' END)   --
            , NULL   --
            , NULL   --
            , NULL   --
            , NULL   --
            , NULL   --
            , NULL   --
            , 3   --
            , NULL   --
            , 0   --
            , NULL   --
            , NULL   --
        )
        -- Update Order with new Balance
        UPDATE TransHeader
        SET     PaymentTotal = PaymentTotal + @PaymentAmount,
                BalanceDue = BalanceDue - @PaymentAmount,
                SeqID = @NewSeqNo
        WHERE ID = @OrderID
        -- Now commit the Transaction
        COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;
        EXEC dbo.csf_chapi_unlock @OrderID, 10000;
        SELECT @ErrorMessage = ERROR_MESSAGE(),
               @ErrorSeverity = ERROR_SEVERITY(),
               @ErrorState = ERROR_STATE();
        -- Use RAISERROR inside the CATCH block to return
        -- error information about the original error that
        -- caused execution to jump to the CATCH block.
        RAISERROR (@ErrorMessage, -- Message text.
                   @ErrorSeverity, -- Severity.
                   @ErrorState -- State.
                   );
    END CATCH;
-- Step 5, Release the Order Lock
EXEC dbo.csf_chapi_unlock @OrderID, 10000;
-- Step 6, Refresh the Order
EXEC dbo.csf_chapi_refresh @OrderId, 10000, @NewSeqNo;
Contributor: Cyrious Software
Date: 1/2014
Version: Control 5.1
You could leave a comment if you were logged in.
Overview

Example

Source
