SQL Stored Procedure - Import Payment
WARNING: This is not kids stuff. 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. If you are interested in using sql_bridge but are not a SQL guru, please contact a sales or consulting at Cyrious.
Note: This feature requires Control 5.75 or higher.
Overview
The stored procedure can be used to add a payment to an existing order in Control
One (and only one) of the following values are required:
- @THID - The ID of the TransHeader record for the Order.
- @OrderNumber - The Order Number of the TransHeader record.
One (and only one) of the following values are required:
- @PaymentMethod - The exact name of the payment method (e.g., 'Visa', 'Check', etc.)
- @PaymentMethodID - The ID from the PaymentAccount table for the payment method.
The following value is also required:
- @PaymentAmount - The amount of the payment applied. May not be larger than the balance due.
The following values may be supplied, but will use the default value is not supplied:
- @DisplayNumber - The display number to show for this payment
- @EmployeeID - The ID from the Employee table. House Account (10) if not specified
- @PaymentDT - The effective DateTime of Payment. Uses current DateTime is omitted.
- @PaymentDivisionID - The division the payment is received at. Use the order division if not supplied
- @ContactID - The contact making the payment. Use the order contact if not supplied.
- @Notes - Optional notes for the Journal and Ledger
* @LockRecords - Set this to 0 to nor lock the records * @RefreshRecords - Set this to 0 to not refresh the order and/or customer (as appropriate) -- If you want to post part of the payment to credit, you must supply BOTH of these values and they must add to the Payment Amount * @PaymentToOrder - Set the order payment amount if you also want to apply part of the payment to the customer credit * @PaymentToCredit - Set the customer credit amount to apply part of the payment to the customer credit. If set, you must also set @PaymentToORder
– The following optional OUTPUT parameters can be used to obtain the new IDs.
– They can also be supplied, in which case sql_bridge will not be used to obtain them.
- @MasterPaymentID - The ID of the master payment record
- @DetailPaymentID - The ID of the detail ORDER payment record
- @DetailCreditID - The ID of the detail CREDIT payment record
- @LedgerID - The first ID of the GL Ledger (2 are required. The next number is used for the second.)
- @OrderGLGroupID - The GroupID of the ORDERS Ledger entries.
- @CreditGLGroupID - The GroupID of the CREDITS Ledger entries.
Notes:
- In general, do not set values you want to use the default to.
The stored procedure requires sql_bridge in order to obtain IDs for the inserted records. 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.
Example Usage
Post a Payment on an Order
EXEC dbo.csp_ImportPayment @OrderNumber = 1977, @PaymentAmount = 238.15, @PaymentMethod = 'Check', @DisplayNumber = 'Check #1234' ;
Post a Payment with an Overpayment
EXEC dbo.csp_ImportPayment @OrderNumber = 1971 , @PaymentAmount = 300.00 , @PaymentMethod = 'Visa' , @DisplayNumber = 'Visa *****4520' , @EmployeeID = 10005 , @Notes = 'Payment with Overpayment' , @PaymentToOrder = 200.00 , @PaymentToCredit = 100.00 ;
Post a Refund of an Amount from Previous Order Payments
Note: This is not voiding the earlier payment(s). This issues a refund.
EXEC dbo.csp_ImportPayment @OrderNumber = 1971 , @PaymentAmount = -175.00 , @PaymentMethod = 'Visa' , @DisplayNumber = 'Visa *****4520' , @EmployeeID = 10005 , @Notes = 'Refund From Previous Order Payments' ;
Apply a Customer Credit to an Order
Note: Always use @PaymentMethodID = 16 for system credit transfers!
EXEC dbo.csp_ImportPayment @OrderNumber = 1971 , @PaymentAmount = 0.00 , @PaymentMethodID = 16 -- System Transfer Account , @DisplayNumber = 'Credit Applied to Order' , @EmployeeID = 10005 , @Notes = 'Apply Credit to Order' , @PaymentToOrder = 500 , @PaymentToCredit = -500 ;
Move the Payment Balance from an Order to the Customer Credit
Note: Always use @PaymentMethodID = 16 for system credit transfers!
EXEC dbo.csp_ImportPayment @OrderNumber = 1971 , @PaymentAmount = 0.00 , @PaymentMethodID = 16 -- System Transfer Account , @DisplayNumber = 'Payments Transferred to Credit' , @EmployeeID = 10005 , @Notes = 'Transfer Order Payment to Credit' , @PaymentToOrder = 175.00 , @PaymentToCredit = -175.00 ;
Post a Payment Only to Customer Credit
Note: This demonstrates the routine, but this is not the best way to accomplish this task since you still need an order to specify the company (though the order isn't used). It is better to use the csp_ImportPaymentToCredit function.
EXEC dbo.csp_ImportPayment @OrderNumber = 1971 , @PaymentAmount = -175.00 , @PaymentMethod = 'Check' , @DisplayNumber = 'Check #1234' , @Notes = 'Payment straight to Credit' , @PaymentToOrder = 0.00 , @PaymentToCredit = -175.00 ;
Stored Procedure
The SQL to create the imported payment stored procedure follows. This must be run to create the stored procedure before it can be used.
-- ============================================= -- Author: Cyrious Software -- Create date: August-2016 -- Description: This stored procedure imports a payment record into Control. -- -- Returns: 1 for Success. Error Message on Failure. -- ============================================= CREATE PROCEDURE csp_ImportPayment -- You must supply 1 (and only 1) of the following @THID INT = NULL OUTPUT , @OrderNumber INT = NULL OUTPUT -- You must supply 1 (and only 1) of the following , @PaymentMethod VARCHAR(50) = NULL -- the AccountName from PaymentAccount table (e.g., Visa, Check) , @PaymentMethodID INT = NULL -- the ID from PaymentAccount table (e.g., Visa, Check) -- You must supply this Required Fields , @PaymentAmount DECIMAL(18,4) -- must be an amount less than the order balance due -- You may or may not supply these Optional Fields , @EmployeeID INT = 10 -- must be an ID from the Employee table. House Account (10) if not specified , @PaymentDT DATETIME = NULL -- Effective DateTime of Payment. Uses current DateTime is omitted. , @PaymentDivisionID INT = NULL -- must be an ID from the Division View , @ContactID INT = NULL -- The contact the payment is for. Defaults to the order's primary contact , @Notes VARCHAR(255) = NULL -- The notes for the journal , @DisplayNumber VARCHAR(32) = NULL -- The payment tracking display number -- If you want to post part of the payment to credit, you must supply BOTH of these values and they must add to the Payment Amount , @PaymentToOrder DECIMAL(18,4) = NULL -- The amount of the payment to apply to the order. Leave blank to apply the full payment to the otder. , @PaymentToCredit DECIMAL(18,4) = NULL -- The amount of the payment to apply to the customer credit. Leave blank for zero. -- Optional Configuration Values , @LockRecords BIT = 1 -- Set this to 0 to nor lock the records , @RefreshRecords BIT = 1 -- Set this to 0 to not refresh the order -- Some OUTPUT Parameters in case the caller wants any of these value back. Pass this value in to NOT use SQL Bridge to look up the records , @MasterPaymentID INT = NULL OUTPUT -- The ID of the Master Payment Record , @DetailPaymentID INT = NULL OUTPUT -- The ID of the Detail Order Payment Record , @DetailCreditID INT = NULL OUTPUT -- The ID of the Detail Credit Record , @LedgerID INT = NULL OUTPUT -- The first ID of the Ledger Records. You need 2 if only payment to order; 4 if customesr credit also , @OrderGLGroupID INT = NULL OUTPUT -- The ID of the GL Group Record , @CreditGLGroupID INT = NULL OUTPUT -- The ID of the GL Group Record AS BEGIN SET NOCOUNT ON; -- ----------------------------------------------------------- -- Initial Variable Declarations -- ----------------------------------------------------------- DECLARE @OrderStatusID INT; DECLARE @AccountID INT; DECLARE @DivisionID INT; DECLARE @OrderSeqNo INT; DECLARE @GLPaymentAccountID INT; -- must be an ID from GLAccount where GLClassificationType = 1007 DECLARE @NewSeqNo INT; DECLARE @CompanyName VARCHAR(64); DECLARE @BalanceDue DECIMAL(18,4); DECLARE @CurrentPayments DECIMAL(18,4); DECLARE @GLAccountOffset INT; DECLARE @CloseOrder BIT; DECLARE @UnCloseOrder BIT; DECLARE @OrderLocked BIT = 0; DECLARE @CompanyLocked BIT = 0; DECLARE @PaymentGLClassificationType INT; DECLARE @PaymentGLClassificationName INT; DECLARE @LedgerIDCount TINYINT; DECLARE @LedgerIDOffset TINYINT = 0; DECLARE @StartGLGroupID INT; DECLARE @EndGLGroupID INT; -- Define some error variables in case we need them DECLARE @DT SMALLDATETIME = GetDate(); DECLARE @ComputerName VARCHAR(25) = @@ServerName; DECLARE @NewLine CHAR(2) = CHAR(10)+CHAR(13); DECLARE @ProcName VARCHAR(50) = OBJECT_NAME(@@PROCID); DECLARE @ErrorMessage VARCHAR(2048) = ''; DECLARE @ErrorNumber INT = 99; DECLARE @ErrorSeverity INT = 15; DECLARE @ErrorState INT = 0; DECLARE @ErrorLine INT = 0; DECLARE @ErrorProcedure VARCHAR(200) = @ProcName; -- ----------------------------------------------------------- -- Fill in initial data -- ----------------------------------------------------------- IF (@PaymentDT IS NULL) SET @PaymentDT = @DT; IF ((@THID IS NOT NULL) AND (@OrderNumber IS NOT NULL)) SET @ErrorMessage = @ErrorMessage + 'Only one of the parameters THID and OrderNumber may be supplied.; '; IF (@PaymentToOrder IS NULL) BEGIN IF (@PaymentToCredit IS NOT NULL) SET @ErrorMessage = @ErrorMessage + 'You must specify the @PaymentToCredit when the @PaymentToOrder if provided; ' SET @PaymentToOrder = @PaymentAmount; SET @PaymentToCredit = 0.0; END ELSE BEGIN IF (@PaymentToCredit IS NULL) SET @ErrorMessage = @ErrorMessage + 'You must specify the @PaymentToOrder when the @PaymentToCredit if provided; ' IF ((@PaymentToOrder + @PaymentToCredit) != @PaymentAmount) SET @ErrorMessage = @ErrorMessage + 'The @PaymentToCredit plus @PaymentToOrder must equal the @PaymentTotal; '; END; SELECT @THID = ID , @OrderNumber = OrderNumber , @AccountID = AccountID , @OrderStatusID = StatusID , @DivisionID = DivisionID , @PaymentDivisionID = COALESCE(@PaymentDivisionID, DivisionID) , @ContactID = COALESCE(@ContactID, ContactID) , @OrderSeqNo = COALESCE(SeqID,0) , @NewSeqNo = COALESCE(SeqID,0) + 1 , @BalanceDue = BalanceDue , @CurrentPayments = PaymentTotal , @CompanyName = (SELECT CompanyName FROM Account WHERE ID = AccountID) , @GLAccountOffset = (CASE WHEN StatusID IN (1,2) THEN 24 ELSE 14 END) -- If in WIP or Built, GL Offset is Customer Deposits (24), Otherwise A/Rs (14) , @CloseOrder = (CASE WHEN (@OrderStatusID = 3) AND (@BalanceDue = @PaymentToOrder) AND (COALESCE(ManuallyReopened,0) = 0) THEN 1 ELSE 0 END) , @UnCloseOrder = (CASE WHEN (@OrderStatusID = 4) AND (@PaymentToOrder < 0) THEN 1 ELSE 0 END) FROM TransHeader WHERE (ID = @THID) OR (OrderNumber = @OrderNumber AND TransactionType IN (1,6)); SELECT @PaymentGLClassificationType = GLClassificationType , @PaymentGLClassificationName = GLClassTypeName FROM GLAccount WHERE ID = @GLPaymentAccountID; -- ----------------------------------------------------------- -- Validate the Input -- ----------------------------------------------------------- IF (@PaymentMethodID IS NOT NULL) BEGIN IF (@PaymentMethod IS NOT NULL) SET @ErrorMessage = @ErrorMessage + 'Only one of the parameters PaymentMethodID and PaymentMethodName may be supplied.; '; END ELSE SET @PaymentMethodID = (SELECT ID FROM PaymentAccount WHERE AccountName = @PaymentMethod AND ClassTypeID = 8002); SET @GLPaymentAccountID = (SELECT BankAccountID FROM PaymentAccount WHERE ID = @PaymentMethodID); IF (@PaymentToOrder > @BalanceDue) SET @ErrorMessage = @ErrorMessage + 'Payment Amount Greater than Balance Due; '; IF ((@PaymentToOrder < 0) AND (-@PaymentToOrder > @CurrentPayments) ) SET @ErrorMessage = @ErrorMessage + 'Can''t Refund More than the Current Payment Balance; '; IF (@OrderStatusID NOT IN (1,2,3) AND (@PaymentToOrder > 0) ) SET @ErrorMessage = @ErrorMessage + 'Payment Can Only be Applied to Order in WIP, Built, or Sale; '; IF (@GLPaymentAccountID IS NULL) SET @ErrorMessage = @ErrorMessage + 'Invalid Payment Method or no GL Account Associated with it; '; -- ----------------------------------------------------------- -- Lock the Order -- ----------------------------------------------------------- IF ((@LockRecords = 1) AND (@ErrorMessage = '')) BEGIN BEGIN TRY IF (@PaymentToOrder <> 0.0) BEGIN EXEC dbo.csf_chapi_lock @THID, 10000; SET @OrderLocked = 1; END; IF (@PaymentToCredit <> 0) BEGIN EXEC dbo.csf_chapi_lock @AccountID, 2000; SET @CompanyLocked = 1; END; END TRY BEGIN CATCH IF (@OrderLocked = 1) BEGIN EXEC dbo.csf_chapi_unlock @THID, 10000; SET @OrderLocked = 0; END; SET @ErrorMessage = 'Unable to lock Order ID '+CONVERT(VARCHAR(16), @THID); -- 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(), '-'); END CATCH; END; IF (@ErrorMessage != '') BEGIN -- Build the message string that will contain original -- error information. SET @ErrorMessage = 'Error %d, Level %d, State %d, Procedure %s, Line %d, Message: '+ @ErrorMessage; -- 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. ); RETURN; END; -- ----------------------------------------------------------- -- Step 3, Request New IDs if needed -- ----------------------------------------------------------- SET @LedgerIDCount = ((CASE WHEN @PaymentToOrder != 0.0 THEN 2 ELSE 0 END) + (CASE WHEN @PaymentToCredit != 0.0 THEN 2 ELSE 0 END)) IF (@MasterPaymentID IS NULL) SET @MasterPaymentID = (SELECT dbo.csf_chapi_nextid(20000, 1)); IF (@LedgerID IS NULL) SET @LedgerID = (SELECT dbo.csf_chapi_nextid(8900, @LedgerIDCount)); IF (@PaymentToOrder != 0.0) BEGIN IF (@DetailPaymentID IS NULL) SET @DetailPaymentID = (SELECT dbo.csf_chapi_nextid(20001, 1)); IF (@OrderGLGroupID IS NULL) SET @OrderGLGroupID = (SELECT dbo.csf_chapi_nextnumber('GLGroupID', 1)); END; IF (@PaymentToCredit != 0.0) BEGIN IF (@DetailCreditID IS NULL) SET @DetailCreditID = (SELECT dbo.csf_chapi_nextid(20002, 1)); IF (@CreditGLGroupID IS NULL) SET @CreditGLGroupID = (SELECT dbo.csf_chapi_nextnumber('GLGroupID', 1)); END; SET @StartGLGroupID = ( SELECT MIN(ID) FROM (VALUES (@OrderGLGroupID),(@CreditGLGroupID)) AS AllIDs(ID) ); SET @EndGLGroupID = ( SELECT MAX(ID) FROM (VALUES (@OrderGLGroupID),(@CreditGLGroupID)) AS AllIDs(ID) ); -- ----------------------------------------------------------- -- Step 4, Insert the new Records (in a transaction) and update the Order Total -- ----------------------------------------------------------- BEGIN TRANSACTION BEGIN TRY -- ----------------------------------------------------------- -- Insert MASTER Payment Records -- ----------------------------------------------------------- -- 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 -- (<ID, int,> , -1 -- ,<StoreID, int,> , 20000 -- ,<ClassTypeID, int,> , @ProcName -- ,<ModifiedByUser, nvarchar(25),> , @ComputerName -- ,<ModifiedByComputer, nvarchar(25),> , @DT -- ,<ModifiedDate, datetime,> , 0 -- ,<SeqID, int,> , 0 -- ,<IsSystem, bit,> , 1 -- ,<IsActive, bit,> , @EmployeeID -- <EmployeeID, int,> , 2 -- <JournalActivityType, int,> , 'Payment' -- <JournalActivityText, nvarchar(25),> , LEFT( (CASE WHEN @PaymentAmount > 0 THEN 'Master Payment for ' ELSE 'Master Refund for ' END)+CONVERT(VARCHAR(12), @OrderNumber)+' - '+@CompanyName, 50) -- <Description, nvarchar(50),> , @Notes -- <Notes, nvarchar(max),> , @PaymentDT -- <StartDateTime, datetime,> , @PaymentDT -- <EndDateTime, datetime,> , NULL -- <TotalTime, datetime,> , NULL -- <ScheduledDateTime, datetime,> , @EmployeeID -- <CompletedByID, int,> , @PaymentDT -- <CompletedDateTime, datetime,> , 1 -- <IsSummary, bit,> , 0 -- <IsDetail, bit,> , NULL -- <SummaryID, int,> , NULL -- <SummaryClassTypeID, int,> , @PaymentAmount -- <SummaryAmount, decimal(18,4),> , 0 -- <DetailAmount, decimal(18,4),> , @StartGLGroupID -- <StartGLGroupID, int,> , @EndGLGroupID -- <EndGLGroupID, int,> , @AccountID -- <AccountID, int,> , 2000 -- <AccountClassTypeID, int,> , @ContactID -- <ContactID, int,> , 3000 -- <ContactClassTypeID, int,> , NULL -- <TransactionID, int,> , NULL -- <TransactionClassTypeID, int,> , 0 -- <IsVoided, bit,> , NULL -- <VoidedDateTime, datetime,> , NULL -- <VoidedEntryID, int,> , NULL -- <VoidedEntryClassTypeID, int,> , NULL -- <VoidedReason, nvarchar(max),> , @PaymentDT -- <QueryStartDateTime, datetime,> , @PaymentDT -- <QueryEndDateTime, datetime,> , NULL -- <ReminderDateTime, datetime,> , 0 -- <ReminderPrompt, bit,> , NULL -- <PartID, int,> , 0 -- <ActivityType, int,> , NULL -- <ActivityTypeText, nvarchar(50),> , 0 -- <IsBillable, bit,> , NULL -- <BillableDateTime, datetime,> , 0 -- <UseActualTime, bit,> , NULL -- <BillingNotes, nvarchar(max),> , 0 -- <BillingType, int,> , 0 -- <TotalBilledTime, float,> , NULL -- <RecurringActivityID, int,> , NULL -- <LinkID, int,> , NULL -- <LinkStoreID, int,> , NULL -- <LinkClassTypeID, int,> , NULL -- <SpecialCode, nvarchar(25),> , @DivisionID -- <DivisionID, int,> , 0 -- <HasCalendarLinks, bit,> , NULL -- <TipRecipientID, int,> , NULL -- <PartClassTypeID, int,> , NULL -- <RecurringClassTypeID, int,> , NULL -- <StationID, int,> , NULL -- <StationClassTypeID, int,> , NULL -- <CurrentState, int,> , NULL -- <StageID, int,> , NULL -- <StageClassTypeID, int,>) ); -- 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 -- (<ID, int,> , -1 -- ,<StoreID, int,> , 20000 -- ,<ClassTypeID, int,> , @ProcName -- ,<ModifiedByUser, nvarchar(25),> , @ComputerName -- ,<ModifiedByComputer, nvarchar(25),> , @DT -- ,<ModifiedDate, datetime,> , 0 -- ,<SeqID, int,> , 0 -- ,<IsSystem, bit,> , 1 -- ,<IsActive, bit,> , '' -- ,<NameOnCard, nvarchar(25),> , NULL -- ,<TrackingNumber, nvarchar(25),> , @PaymentMethodID -- ,<PaymentAccountID, int,> , 8002 -- ,<PaymentAccountClassTypeID, int,> , NULL -- ,<ExpirationDate, datetime,> , NULL -- ,<VCode, char(10),> , 0 -- ,<IsCCNumEncrypt, bit,> , @DisplayNumber -- ,<DisplayNumber, nvarchar(50),> , NULL -- ,<IsVCodeEncrypted, bit,> , 0 -- ,<Undeposited, bit,> , NULL -- ,<DepositGLGroupID, int,> , NULL -- ,<DepositJournalID, int,> , @PaymentDT -- ,<PaymentDate, datetime,> , @GLPaymentAccountID -- ,<BankAccountID, int,> , 8001 -- ,<BankAccountClasstypeID, int,> , 2 -- ,<TenderType, int,> , NULL -- ,<PayrollID, int,> , NULL -- ,<PayrollClassTypeID, int,> , NULL -- ,<PaycheckID, int,> , NULL -- ,<BankReference, nvarchar(50),> , NULL -- ,<BankCode, nvarchar(50),> , NULL -- ,<BranchCode, nvarchar(50),> , NULL -- ,<CIN, nvarchar(50),> , NULL -- ,<State, nvarchar(50),> , NULL -- ,<CCAccount, nvarchar(50),> , NULL -- ,<CCCSTransactionGuid, nvarchar(50),> , NULL -- ,<CCCSCustomerGuid, nvarchar(50),> , 0 -- ,<IsActualCheck, bit,>) ); -- ----------------------------------------------------------- -- Handle ORDER part of Payment -- ----------------------------------------------------------- IF (@PaymentToOrder <> 0.0) BEGIN -- Insert ORDER 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 -- (<ID, int,> , -1 -- ,<StoreID, int,> , 20001 -- ,<ClassTypeID, int,> , @ProcName -- ,<ModifiedByUser, nvarchar(25),> , @ComputerName -- ,<ModifiedByComputer, nvarchar(25),> , @DT -- ,<ModifiedDate, datetime,> , 0 -- ,<SeqID, int,> , 0 -- ,<IsSystem, bit,> , 1 -- ,<IsActive, bit,> , @EmployeeID -- <EmployeeID, int,> , 2 -- <JournalActivityType, int,> , 'Payment' -- <JournalActivityText, nvarchar(25),> , LEFT( (CASE WHEN @PaymentToOrder > 0 THEN 'Order Payment for #' ELSE 'Refund Payment for #' END)+CONVERT(VARCHAR(12), @OrderNumber)+' - '+@CompanyName, 50) -- <Description, nvarchar(50),> , @Notes -- <Notes, nvarchar(max),> , @PaymentDT -- <StartDateTime, datetime,> , @PaymentDT -- <EndDateTime, datetime,> , NULL -- <TotalTime, datetime,> , NULL -- <ScheduledDateTime, datetime,> , @EmployeeID -- <CompletedByID, int,> , @PaymentDT -- <CompletedDateTime, datetime,> , 0 -- <IsSummary, bit,> , 1 -- <IsDetail, bit,> , @MasterPaymentID -- <SummaryID, int,> , 20000 -- <SummaryClassTypeID, int,> , 0 -- <SummaryAmount, decimal(18,4),> , @PaymentToOrder -- <DetailAmount, decimal(18,4),> , @OrderGLGroupID -- <StartGLGroupID, int,> , @OrderGLGroupID -- <EndGLGroupID, int,> , @AccountID -- <AccountID, int,> , 2000 -- <AccountClassTypeID, int,> , @ContactID -- <ContactID, int,> , 3000 -- <ContactClassTypeID, int,> , @THID -- <TransactionID, int,> , 10000 -- <TransactionClassTypeID, int,> , 0 -- <IsVoided, bit,> , NULL -- <VoidedDateTime, datetime,> , NULL -- <VoidedEntryID, int,> , NULL -- <VoidedEntryClassTypeID, int,> , NULL -- <VoidedReason, nvarchar(max),> , @PaymentDT -- <QueryStartDateTime, datetime,> , @PaymentDT -- <QueryEndDateTime, datetime,> , NULL -- <ReminderDateTime, datetime,> , 0 -- <ReminderPrompt, bit,> , NULL -- <PartID, int,> , 0 -- <ActivityType, int,> , NULL -- <ActivityTypeText, nvarchar(50),> , 0 -- <IsBillable, bit,> , NULL -- <BillableDateTime, datetime,> , 0 -- <UseActualTime, bit,> , NULL -- <BillingNotes, nvarchar(max),> , 0 -- <BillingType, int,> , 0 -- <TotalBilledTime, float,> , NULL -- <RecurringActivityID, int,> , NULL -- <LinkID, int,> , NULL -- <LinkStoreID, int,> , NULL -- <LinkClassTypeID, int,> , NULL -- <SpecialCode, nvarchar(25),> , @DivisionID -- <DivisionID, int,> , 0 -- <HasCalendarLinks, bit,> , NULL -- <TipRecipientID, int,> , NULL -- <PartClassTypeID, int,> , NULL -- <RecurringClassTypeID, int,> , NULL -- <StationID, int,> , NULL -- <StationClassTypeID, int,> , NULL -- <CurrentState, int,> , NULL -- <StageID, int,> , NULL -- <StageClassTypeID, int,>) ); -- 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 -- (<ID, int,> , -1 -- ,<StoreID, int,> , 20001 -- ,<ClassTypeID, int,> , @ProcName -- ,<ModifiedByUser, nvarchar(25),> , @ComputerName -- ,<ModifiedByComputer, nvarchar(25),> , @DT -- ,<ModifiedDate, datetime,> , 0 -- ,<SeqID, int,> , 0 -- ,<IsSystem, bit,> , 1 -- ,<IsActive, bit,> , '' -- ,<NameOnCard, nvarchar(25),> , NULL -- ,<TrackingNumber, nvarchar(25),> , @PaymentMethodID -- ,<PaymentAccountID, int,> , 8002 -- ,<PaymentAccountClassTypeID, int,> , NULL -- ,<ExpirationDate, datetime,> , NULL -- ,<VCode, char(10),> , 0 -- ,<IsCCNumEncrypt, bit,> , @DisplayNumber -- ,<DisplayNumber, nvarchar(50),> , NULL -- ,<IsVCodeEncrypted, bit,> , 0 -- ,<Undeposited, bit,> , NULL -- ,<DepositGLGroupID, int,> , NULL -- ,<DepositJournalID, int,> , @PaymentDT -- ,<PaymentDate, datetime,> , @GLPaymentAccountID -- ,<BankAccountID, int,> , 8001 -- ,<BankAccountClasstypeID, int,> , 2 -- ,<TenderType, int,> , NULL -- ,<PayrollID, int,> , NULL -- ,<PayrollClassTypeID, int,> , NULL -- ,<PaycheckID, int,> , NULL -- ,<BankReference, nvarchar(50),> , NULL -- ,<BankCode, nvarchar(50),> , NULL -- ,<BranchCode, nvarchar(50),> , NULL -- ,<CIN, nvarchar(50),> , NULL -- ,<State, nvarchar(50),> , NULL -- ,<CCAccount, nvarchar(50),> , NULL -- ,<CCCSTransactionGuid, nvarchar(50),> , NULL -- ,<CCCSCustomerGuid, nvarchar(50),> , 0 -- ,<IsActualCheck, bit,>) ); -- Create GL Entries 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+@LedgerIDOffset -- <ID, int,> , -1 -- ,<StoreID, int,> , 8900 -- ,<ClassTypeID, int,> , @ProcName -- ,<ModifiedByUser, nvarchar(25),> , @ComputerName -- ,<ModifiedByComputer, nvarchar(25),> , @DT -- ,<ModifiedDate, datetime,> , 0 -- ,<SeqID, int,> , 0 -- ,<IsSystem, bit,> , 1 -- ,<IsActive, bit,> , @PaymentDT -- <EntryDateTime, datetime,> , @PaymentToOrder -- <Amount, decimal(18,4),> , @PaymentMethodID -- <Classification, int,> , 0 -- <IsTaxable, bit,> , @OrderGLGroupID -- <GroupID, int,> , @GLPaymentAccountID -- <GLAccountID, int,> , 8001 -- <GLAccountClassTypeID, int,> , @AccountID -- <AccountID, int,> , 2000 -- <AccountClassTypeID, int,> , @THID -- <TransactionID, int,> , 10000 -- <TransactionClassTypeID, int,> , NULL -- <TransDetailID, int,> , NULL -- <TransDetailClassTypeID, int,> , NULL -- <GoodsItemID, int,> , NULL -- <GoodsItemClassTypeID, int,> , (CASE WHEN @PaymentToOrder > 0 THEN 'Order Payment' ELSE 'Refund Payment' END) -- <Description, varchar(50),> , @DivisionID -- <DivisionID, int,> , @Notes -- <Notes, varchar(max),> , 0 -- <IsModified, bit,> , 0 -- <IsUser, bit,> , NULL -- <TaxClassID, int,> , 0 -- <Quantity, float,> , NULL -- <PartID, int,> , NULL -- <PartClassTypeID, int,> , @DetailPaymentID -- <JournalID, int,> , 20001 -- <JournalClassTypeID, int,> , 0 -- <Reconciled, bit,> , NULL -- <ReconciliationDateTime, datetime,> , NULL -- <ReconciliationID, int,> , NULL -- <ReconciliationClassTypeID, int,> , @DivisionID -- <ProcessedDivisionID, int,> , @PaymentGLClassificationType -- <GLClassificationType, int,> , @PaymentGLClassificationName -- <GLClassTypeName, varchar(50),> , NULL -- <TransPartID, int,> , NULL -- <TransPartClassTypeID, int,> , NULL -- <StationID, int,> , NULL -- <PayrollID, int,> , NULL -- <PayrollClassTypeID, int,> , NULL -- <DepositJournalID, int,> , 3 -- <EntryType, int,> , NULL -- <EmployeeID, int,> , 0 -- <OffBalanceSheet, bit,> , NULL -- <WarehouseID, int,> , NULL -- <InventoryID, int,> ) SET @LedgerIDOffset = @LedgerIDOffset + 1; -- Create GL Entry #2 for Offset to Order Payments 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+@LedgerIDOffset -- <ID, int,> , -1 -- ,<StoreID, int,> , 8900 -- ,<ClassTypeID, int,> , @ProcName -- ,<ModifiedByUser, nvarchar(25),> , @ComputerName -- ,<ModifiedByComputer, nvarchar(25),> , @DT -- ,<ModifiedDate, datetime,> , 0 -- ,<SeqID, int,> , 0 -- ,<IsSystem, bit,> , 1 -- ,<IsActive, bit,> , @PaymentDT -- <EntryDateTime, datetime,> , -@PaymentToOrder -- <Amount, decimal(18,4),> , 0 -- <Classification, int,> , 0 -- <IsTaxable, bit,> , @OrderGLGroupID -- <GroupID, int,> , @GLAccountOffset -- <GLAccountID, int,> , 8001 -- <GLAccountClassTypeID, int,> , @AccountID -- <AccountID, int,> , 2000 -- <AccountClassTypeID, int,> , @THID -- <TransactionID, int,> , 10000 -- <TransactionClassTypeID, int,> , NULL -- <TransDetailID, int,> , NULL -- <TransDetailClassTypeID, int,> , NULL -- <GoodsItemID, int,> , NULL -- <GoodsItemClassTypeID, int,> , (CASE WHEN @PaymentToOrder > 0 THEN 'Order Payment' ELSE 'Refund Payment' END) -- <Description, varchar(50),> , @DivisionID -- <DivisionID, int,> , @Notes -- <Notes, varchar(max),> , 0 -- <IsModified, bit,> , 0 -- <IsUser, bit,> , NULL -- <TaxClassID, int,> , 0 -- <Quantity, float,> , NULL -- <PartID, int,> , NULL -- <PartClassTypeID, int,> , @DetailPaymentID -- <JournalID, int,> , 20001 -- <JournalClassTypeID, int,> , 0 -- <Reconciled, bit,> , NULL -- <ReconciliationDateTime, datetime,> , NULL -- <ReconciliationID, int,> , NULL -- <ReconciliationClassTypeID, int,> , @DivisionID -- <ProcessedDivisionID, int,> , (CASE WHEN @GLAccountOffset = 24 THEN 2002 ELSE 1002 END) -- <GLClassificationType, int,> , (CASE WHEN @GLAccountOffset = 24 THEN 'Current Asset' ELSE 'Current Liability' END) -- <GLClassTypeName, varchar(50),> , NULL -- <TransPartID, int,> , NULL -- <TransPartClassTypeID, int,> , NULL -- <StationID, int,> , NULL -- <PayrollID, int,> , NULL -- <PayrollClassTypeID, int,> , NULL -- <DepositJournalID, int,> , 3 -- <EntryType, int,> , NULL -- <EmployeeID, int,> , 0 -- <OffBalanceSheet, bit,> , NULL -- <WarehouseID, int,> , NULL -- <InventoryID, int,> ); SET @LedgerIDOffset = @LedgerIDOffset + 1; -- ----------------------------------------------------------- -- Update Order with new Balance and check if the Order should be closed -- ----------------------------------------------------------- UPDATE TransHeader SET SeqID = @NewSeqNo , ModifiedDate = @DT , ModifiedByComputer = @ComputerName , ModifiedByUser = @ProcName , PaymentTotal = (PaymentTotal + @PaymentToOrder) , BalanceDue = (BalanceDue - @PaymentToOrder) , StatusID = (CASE WHEN @closeOrder = 1 THEN 4 WHEN @UncLoseOrder = 1 THEN 3 ELSE StatusID -- keep the same END) , ClosedDate = (CASE WHEN @closeOrder = 1 THEN @PaymentDT WHEN @UncLoseOrder = 1 THEN NULL ELSE ClosedDate -- keep the same END) , StatusText = (CASE WHEN @closeOrder = 1 THEN 'Closed' ELSE StatusText END) WHERE ID = @THID; END; -- ----------------------------------------------------------- -- Handle CREDIT part of Payment -- ----------------------------------------------------------- IF (@PaymentToCredit <> 0.0) BEGIN /* A region */ -- Insert CREDIT 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 ( @DetailCreditID -- (<ID, int,> , -1 -- ,<StoreID, int,> , 20002 -- ,<ClassTypeID, int,> , @ProcName -- ,<ModifiedByUser, nvarchar(25),> , @ComputerName -- ,<ModifiedByComputer, nvarchar(25),> , @DT -- ,<ModifiedDate, datetime,> , 0 -- ,<SeqID, int,> , 0 -- ,<IsSystem, bit,> , 1 -- ,<IsActive, bit,> , @EmployeeID -- <EmployeeID, int,> , 2 -- <JournalActivityType, int,> , 'Payment' -- <JournalActivityText, nvarchar(25),> , LEFT( (CASE WHEN @PaymentToCredit > 0 THEN 'OverPayment for ' ELSE 'Refund Credit for ' END)+@CompanyName, 50) -- <Description, nvarchar(50),> , @Notes -- <Notes, nvarchar(max),> , @PaymentDT -- <StartDateTime, datetime,> , @PaymentDT -- <EndDateTime, datetime,> , NULL -- <TotalTime, datetime,> , NULL -- <ScheduledDateTime, datetime,> , @EmployeeID -- <CompletedByID, int,> , @PaymentDT -- <CompletedDateTime, datetime,> , 0 -- <IsSummary, bit,> , 1 -- <IsDetail, bit,> , @MasterPaymentID -- <SummaryID, int,> , 20000 -- <SummaryClassTypeID, int,> , 0 -- <SummaryAmount, decimal(18,4),> , @PaymentToCredit -- <DetailAmount, decimal(18,4),> , @CreditGLGroupID -- <StartGLGroupID, int,> , @CreditGLGroupID -- <EndGLGroupID, int,> , @AccountID -- <AccountID, int,> , 2000 -- <AccountClassTypeID, int,> , @ContactID -- <ContactID, int,> , 3000 -- <ContactClassTypeID, int,> , NULL -- <TransactionID, int,> , NULL -- <TransactionClassTypeID, int,> , 0 -- <IsVoided, bit,> , NULL -- <VoidedDateTime, datetime,> , NULL -- <VoidedEntryID, int,> , NULL -- <VoidedEntryClassTypeID, int,> , NULL -- <VoidedReason, nvarchar(max),> , @PaymentDT -- <QueryStartDateTime, datetime,> , @PaymentDT -- <QueryEndDateTime, datetime,> , NULL -- <ReminderDateTime, datetime,> , 0 -- <ReminderPrompt, bit,> , NULL -- <PartID, int,> , 0 -- <ActivityType, int,> , NULL -- <ActivityTypeText, nvarchar(50),> , 0 -- <IsBillable, bit,> , NULL -- <BillableDateTime, datetime,> , 0 -- <UseActualTime, bit,> , NULL -- <BillingNotes, nvarchar(max),> , 0 -- <BillingType, int,> , 0 -- <TotalBilledTime, float,> , NULL -- <RecurringActivityID, int,> , NULL -- <LinkID, int,> , NULL -- <LinkStoreID, int,> , NULL -- <LinkClassTypeID, int,> , NULL -- <SpecialCode, nvarchar(25),> , @DivisionID -- <DivisionID, int,> , 0 -- <HasCalendarLinks, bit,> , NULL -- <TipRecipientID, int,> , NULL -- <PartClassTypeID, int,> , NULL -- <RecurringClassTypeID, int,> , NULL -- <StationID, int,> , NULL -- <StationClassTypeID, int,> , NULL -- <CurrentState, int,> , NULL -- <StageID, int,> , NULL -- <StageClassTypeID, int,>) ); -- Insert CREDIT 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 ( @DetailCreditID -- (<ID, int,> , -1 -- ,<StoreID, int,> , 20002 -- ,<ClassTypeID, int,> , @ProcName -- ,<ModifiedByUser, nvarchar(25),> , @ComputerName -- ,<ModifiedByComputer, nvarchar(25),> , @DT -- ,<ModifiedDate, datetime,> , 0 -- ,<SeqID, int,> , 0 -- ,<IsSystem, bit,> , 1 -- ,<IsActive, bit,> , '' -- ,<NameOnCard, nvarchar(25),> , NULL -- ,<TrackingNumber, nvarchar(25),> , @PaymentMethodID -- ,<PaymentAccountID, int,> , 8002 -- ,<PaymentAccountClassTypeID, int,> , NULL -- ,<ExpirationDate, datetime,> , NULL -- ,<VCode, char(10),> , 0 -- ,<IsCCNumEncrypt, bit,> , @DisplayNumber -- ,<DisplayNumber, nvarchar(50),> , NULL -- ,<IsVCodeEncrypted, bit,> , 0 -- ,<Undeposited, bit,> , NULL -- ,<DepositGLGroupID, int,> , NULL -- ,<DepositJournalID, int,> , @PaymentDT -- ,<PaymentDate, datetime,> , @GLPaymentAccountID -- ,<BankAccountID, int,> , 8001 -- ,<BankAccountClasstypeID, int,> , 2 -- ,<TenderType, int,> , NULL -- ,<PayrollID, int,> , NULL -- ,<PayrollClassTypeID, int,> , NULL -- ,<PaycheckID, int,> , NULL -- ,<BankReference, nvarchar(50),> , NULL -- ,<BankCode, nvarchar(50),> , NULL -- ,<BranchCode, nvarchar(50),> , NULL -- ,<CIN, nvarchar(50),> , NULL -- ,<State, nvarchar(50),> , NULL -- ,<CCAccount, nvarchar(50),> , NULL -- ,<CCCSTransactionGuid, nvarchar(50),> , NULL -- ,<CCCSCustomerGuid, nvarchar(50),> , 0 -- ,<IsActualCheck, bit,>) ); -- Create GL Entries for Customer Credit 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+@LedgerIDOffset -- <ID, int,> , -1 -- ,<StoreID, int,> , 8900 -- ,<ClassTypeID, int,> , @ProcName -- ,<ModifiedByUser, nvarchar(25),> , @ComputerName -- ,<ModifiedByComputer, nvarchar(25),> , @DT -- ,<ModifiedDate, datetime,> , 0 -- ,<SeqID, int,> , 0 -- ,<IsSystem, bit,> , 1 -- ,<IsActive, bit,> , @PaymentDT -- <EntryDateTime, datetime,> , @PaymentToCredit -- <Amount, decimal(18,4),> , @PaymentMethodID -- <Classification, int,> , 0 -- <IsTaxable, bit,> , @CreditGLGroupID -- <GroupID, int,> , @GLPaymentAccountID -- <GLAccountID, int,> , 8001 -- <GLAccountClassTypeID, int,> , @AccountID -- <AccountID, int,> , 2000 -- <AccountClassTypeID, int,> , NULL -- <TransactionID, int,> , NULL -- <TransactionClassTypeID, int,> , NULL -- <TransDetailID, int,> , NULL -- <TransDetailClassTypeID, int,> , NULL -- <GoodsItemID, int,> , NULL -- <GoodsItemClassTypeID, int,> , LEFT( (CASE WHEN @PaymentToCredit > 0 THEN 'Overpayment for ' ELSE 'Refund Payment for' END)+@CompanyName, 50) -- <Description, varchar(50),> , @DivisionID -- <DivisionID, int,> , @Notes -- <Notes, varchar(max),> , 0 -- <IsModified, bit,> , 0 -- <IsUser, bit,> , NULL -- <TaxClassID, int,> , 0 -- <Quantity, float,> , NULL -- <PartID, int,> , NULL -- <PartClassTypeID, int,> , @DetailCreditID -- <JournalID, int,> , 20002 -- <JournalClassTypeID, int,> , 0 -- <Reconciled, bit,> , NULL -- <ReconciliationDateTime, datetime,> , NULL -- <ReconciliationID, int,> , NULL -- <ReconciliationClassTypeID, int,> , @DivisionID -- <ProcessedDivisionID, int,> , @PaymentGLClassificationType -- <GLClassificationType, int,> , @PaymentGLClassificationName -- <GLClassTypeName, varchar(50),> , NULL -- <TransPartID, int,> , NULL -- <TransPartClassTypeID, int,> , NULL -- <StationID, int,> , NULL -- <PayrollID, int,> , NULL -- <PayrollClassTypeID, int,> , NULL -- <DepositJournalID, int,> , 3 -- <EntryType, int,> , NULL -- <EmployeeID, int,> , 0 -- <OffBalanceSheet, bit,> , NULL -- <WarehouseID, int,> , NULL -- <InventoryID, int,> ) SET @LedgerIDOffset = @LedgerIDOffset + 1; -- Create GL Entry #2 for Offset to Credits 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+@LedgerIDOffset -- <ID, int,> , -1 -- ,<StoreID, int,> , 8900 -- ,<ClassTypeID, int,> , @ProcName -- ,<ModifiedByUser, nvarchar(25),> , @ComputerName -- ,<ModifiedByComputer, nvarchar(25),> , @DT -- ,<ModifiedDate, datetime,> , 0 -- ,<SeqID, int,> , 0 -- ,<IsSystem, bit,> , 1 -- ,<IsActive, bit,> , @PaymentDT -- <EntryDateTime, datetime,> , -@PaymentToCredit -- <Amount, decimal(18,4),> , 0 -- <Classification, int,> , 0 -- <IsTaxable, bit,> , @CreditGLGroupID -- <GroupID, int,> , 23 -- <GLAccountID, int,> , 8001 -- <GLAccountClassTypeID, int,> , @AccountID -- <AccountID, int,> , 2000 -- <AccountClassTypeID, int,> , NULL -- <TransactionID, int,> , NULL -- <TransactionClassTypeID, int,> , NULL -- <TransDetailID, int,> , NULL -- <TransDetailClassTypeID, int,> , NULL -- <GoodsItemID, int,> , NULL -- <GoodsItemClassTypeID, int,> , LEFT( (CASE WHEN @PaymentToCredit > 0 THEN 'Overpayment for ' ELSE 'Refund Payment for' END)+@CompanyName, 50) -- <Description, varchar(50),> , @DivisionID -- <DivisionID, int,> , @Notes -- <Notes, varchar(max),> , 0 -- <IsModified, bit,> , 0 -- <IsUser, bit,> , NULL -- <TaxClassID, int,> , 0 -- <Quantity, float,> , NULL -- <PartID, int,> , NULL -- <PartClassTypeID, int,> , @DetailCreditID -- <JournalID, int,> , 20002 -- <JournalClassTypeID, int,> , 0 -- <Reconciled, bit,> , NULL -- <ReconciliationDateTime, datetime,> , NULL -- <ReconciliationID, int,> , NULL -- <ReconciliationClassTypeID, int,> , @DivisionID -- <ProcessedDivisionID, int,> , 2002 -- <GLClassificationType, int,> , 'Current Liability' -- <GLClassTypeName, varchar(50),> , NULL -- <TransPartID, int,> , NULL -- <TransPartClassTypeID, int,> , NULL -- <StationID, int,> , NULL -- <PayrollID, int,> , NULL -- <PayrollClassTypeID, int,> , NULL -- <DepositJournalID, int,> , 3 -- <EntryType, int,> , NULL -- <EmployeeID, int,> , 0 -- <OffBalanceSheet, bit,> , NULL -- <WarehouseID, int,> , NULL -- <InventoryID, int,> ); SET @LedgerIDOffset = @LedgerIDOffset + 1; -- ----------------------------------------------------------- -- Update Customer Record with new credit Balance -- ----------------------------------------------------------- UPDATE Account SET SeqID = @NewSeqNo , ModifiedDate = @DT , ModifiedByComputer = @ComputerName , ModifiedByUser = @ProcName , CreditBalance = (CreditBalance + @PaymentToCredit) WHERE ID = @AccountID; END; -- ----------------------------------------------------------- -- Now commit the Transaction -- ----------------------------------------------------------- COMMIT TRANSACTION END TRY -- ----------------------------------------------------------- -- Handle any Errors -- ----------------------------------------------------------- BEGIN CATCH ROLLBACK TRANSACTION; IF (@OrderLocked = 1) EXEC dbo.csf_chapi_unlock @THID, 10000; IF (@CompanyLocked = 1) EXEC dbo.csf_chapi_unlock @AccountID, 2000; SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorNumber = ERROR_NUMBER(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE(), @ErrorLine = ERROR_LINE(), @ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-'); -- 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, @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. ); RETURN; END CATCH; -- ----------------------------------------------------------- -- Step 5, Release the Locks and Refresh -- ----------------------------------------------------------- IF (@OrderLocked = 1) EXEC dbo.csf_chapi_unlock @THID, 10000; IF (@CompanyLocked = 1) EXEC dbo.csf_chapi_unlock @AccountID, 2000; IF (@RefreshRecords = 1) BEGIN IF (@PaymentToOrder <> 0.0) EXEC dbo.csf_chapi_refresh @THID, 10000, @NewSeqNo; IF (@PaymentToCredit <> 0.0) EXEC dbo.csf_chapi_refresh @AccountID, 2000, @NewSeqNo; END; SELECT CONVERT(bit, 1) AS RESULT; -- True = Success END;
Source
Contributor: Cyrious Software
Date: 8/2016
Version: Control 5.7+
You could leave a comment if you were logged in.