WARNING: If you are interested in using sql_bridge, please contact a sales or implementation consultant at Cyrious.
Overview
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;
Source
Contributor: Cyrious Software
Date: 1/2014
Version: Control 5.1