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.