/**
*    Don't use this stored proc directly but instead use UpdateCommissionOnHoldStatus_Synonym.
*    UpdateCommissionOnHoldStatus_Synonym will either point to this stored proc or to
*    UpdateCommissionOnHoldStatus_XLedger.
*
*    Places the specified commissions or split commissions on hold or removes
*    the hold as recorded in GP.  Logs an audit trail of the action against
*    each such commission.
*    
*    Parameters:
*    ===========
*    @inOrderID: The primary key of the order whose balance is desired.
*    @inCommissionedDetailIds: A comma separated list of CommissionedDetail primary
*        keys to be placed on or removed from hold.
*    @inSplitCommissionsIds: A comma separated list of SplitCommissions primary
*        keys to be placed on or removed from hold.
*    @inRemoveHold: 1 to remove the hold and 0 to place it back on hold.
*    @inHoldUpdateDescription: Audit log change reason.
*    @inSysUserId: Primary key of the SysUser performing this action.
*    @outErrorCode: Returned error code.
**/

create procedure [dbo].[UpdateCommissionOnHoldStatus_Legacy]
(
    @inOrderID int,
    @inCommissionedDetailIds varchar(max),
    @inSplitCommissionsIds varchar(max),
    @inRemoveHold bit,
    @inHoldUpdateDescription varchar(30),
    @inSysUserId int,
    @outErrorCode int output
)
as
set nocount on

declare @theNewHoldStatus nchar(1)
declare @theOldHoldStatus nchar(1)
declare @theGpBranchPriKey int
declare @theCommissionedDetailCount int
declare @theSplitCommissionsCount int
declare @theGPUpdateCount int
declare @theTempGPUpdateCount int
declare @theCurrentUtcDateTime datetime
declare @theGPDB_PM10000 sysname
declare @theGPDB_PM20000 sysname
declare @theParamList nvarchar(128)
declare @theRowCountSql nvarchar(128)
declare @theSQL nvarchar(4000)
declare @theCommissionsTable AcctTransactionsSourceType

-- Return error codes
declare @NO_GP_ACCOUNTING_SYSTEM int
declare @GP_BRANCH_SETUP_INCOMPLETE int
declare @REMOVE_HOLD_PARAMETER_IS_NULL int
declare @SOME_GP_ITEMS_ALREADY_ON_HOLD int
declare @SOME_GP_ITEMS_ALREADY_OFF_HOLD int

select
    @outErrorCode = 0,
    @NO_GP_ACCOUNTING_SYSTEM = 2300,
    @GP_BRANCH_SETUP_INCOMPLETE = 2301,
    @REMOVE_HOLD_PARAMETER_IS_NULL = 2302,
    @SOME_GP_ITEMS_ALREADY_ON_HOLD = 2303,
    @SOME_GP_ITEMS_ALREADY_OFF_HOLD = 2304,
    @theCommissionedDetailCount = 0,
    @theSplitCommissionsCount = 0,
    @theGPUpdateCount = 0,
    @theTempGPUpdateCount = 0,
    @theCurrentUtcDateTime = getutcdate(),
    @theGpBranchPriKey = ( select top 1 BranchPriKey from Branch where GLCPriKey is not null ),
    @theOldHoldStatus = case
        when @inRemoveHold = 1 then N'1'
        else N'0'
    end,
    @theNewHoldStatus = case
        when @inRemoveHold = 1 then N'0'
        else N'1'
    end

-- Gets the accounting system type (XL/GP/QB/NO)
declare @theAccountingSystemType varchar(2) = dbo.GetAccountingSystemType()

-- XLTD: Set hold flags on XLedger tables?
if( @theAccountingSystemType = 'GP' )
begin
    -- If we get here, GP IS turned on.  (@NO_GP_ACCOUNTING_SYSTEM can't happen.)
    if( @theGpBranchPriKey is null )
    begin
        -- GP is not setup completely.
        set @outErrorCode = @GP_BRANCH_SETUP_INCOMPLETE
    end
    else if( @inRemoveHold is null )
    begin
        -- @inRemoveHold must be specified.
        set @outErrorCode = @REMOVE_HOLD_PARAMETER_IS_NULL
    end
    else
    begin
        -- OK, Great Plains is an active module so proceed with updating the commission holds update (set or clear)
        if( len( isnull( @inCommissionedDetailIds, '' ) ) > 0 )
        begin
            insert into @theCommissionsTable ( OkToUpdate, Source, SourceRecord )
            select
                OkToUpdate = 0,
                Source = 'CommissionedDetail',
                SourceRecord = Item
            from dbo.ParseMultiValuedParameter( @inCommissionedDetailIds, ',' )

            set @theCommissionedDetailCount = @@rowcount
        end
        if( len( isnull( @inSplitCommissionsIds, '' ) ) > 0 )
        begin
            insert into @theCommissionsTable ( OkToUpdate, Source, SourceRecord )
            select
                OkToUpdate = 0,
                Source = 'SplitCommissions',
                SourceRecord = Item
            from dbo.ParseMultiValuedParameter( @inSplitCommissionsIds, ',' )

            set @theSplitCommissionsCount = @@rowcount
        end

        if( @theCommissionedDetailCount + @theSplitCommissionsCount > 0 )
        begin
            update @theCommissionsTable set
                OkToUpdate = 1,
                VendorNumber = AcctTransactions.VendorNumber,
                VoucherNumber = AcctTransactions.VoucherNumber
            from @theCommissionsTable as theCommissionsTable
            inner join AcctTransactions on AcctTransactions.Source = theCommissionsTable.Source and
                AcctTransactions.SourceRecord = theCommissionsTable.SourceRecord
            where AcctTransactions.OrdPriKey = @inOrderID

            set @theGPDB_PM20000 = 'PM20000'
            set @theGPDB_PM20000 = dbo.udfGetGPDB( @theGpBranchPriKey, @theGPDB_PM20000 )
            set @theGPDB_PM10000 = 'PM10000'
            set @theGPDB_PM10000 = dbo.udfGetGPDB( @theGpBranchPriKey, @theGPDB_PM10000 )
            set @theParamList = N'@theCommissionsTable [dbo].[AcctTransactionsSourceType] readonly, @theTempGPUpdateCount int output'
            set @theRowCountSql = N' set @theTempGPUpdateCount = @@rowcount'

            -- Mark GP AP Vendor credit memo records as being off hold if they are still on hold.
            set @theSQL = N'update ' + @theGPDB_PM20000 + ' set ' +
                    'HOLD = ' + @theNewHoldStatus + ' ' +
                'from @theCommissionsTable as theCommissionsTable ' +
                'inner join ' + @theGPDB_PM20000 + ' as PM20000 on PM20000.VendorID = theCommissionsTable.VendorNumber and ' +
                    'PM20000.VCHRNMBR = theCommissionsTable.VoucherNumber ' +
                'where theCommissionsTable.OkToUpdate = 1 and ' +
                    'PM20000.HOLD = ' + @theOldHoldStatus +
                @theRowCountSql

            exec sp_executesql
                @stmt = @theSQL,
                @params = @theParamList,
                @theCommissionsTable = @theCommissionsTable,
                @theTempGPUpdateCount = @theTempGPUpdateCount output

            set @theGPUpdateCount = @theTempGPUpdateCount

            -- Mark GP unposted order records as being off hold if they are still on hold.
            set @theSQL = N'update ' + @theGPDB_PM10000 + ' set ' +
                    'SIMPLIFD = ' + @theNewHoldStatus + ' ' +
                'from @theCommissionsTable as theCommissionsTable ' +
                'inner join ' + @theGPDB_PM10000 + ' as PM10000 on PM10000.VendorID = theCommissionsTable.VendorNumber and ' +
                    'PM10000.VCHRNMBR = theCommissionsTable.VoucherNumber ' +
                'where theCommissionsTable.OkToUpdate = 1 and ' +
                    'PM10000.SIMPLIFD = ' + @theOldHoldStatus +
                @theRowCountSql

            exec sp_executesql
                @stmt = @theSQL,
                @params = @theParamList,
                @theCommissionsTable = @theCommissionsTable,
                @theTempGPUpdateCount = @theTempGPUpdateCount output

            set @theGPUpdateCount = @theGPUpdateCount + @theTempGPUpdateCount

            if( @theCommissionedDetailCount > 0 )
            begin
                -- We are adding new CommissionDetailHold records
                insert into CommissionedDetailHold
                (
                    CommissionedDetailFID,
                    SysUserFID,
                    ChangedOn,
                    HoldUpdateStatus,
                    HoldUpdateDescription
                )
                select
                    CommissionedDetailFID = CommissionedDetail.CDPriKey,
                    SysUserFID = @inSysUserId,
                    ChangedOn = @theCurrentUtcDateTime,
                    HoldUpdateStatus = case
                        when @inRemoveHold = 1 then 0
                        else 1
                    end,
                    HoldUpdateDescription = @inHoldUpdateDescription
                from @theCommissionsTable as theCommissionsTable
                inner join CommissionedDetail on theCommissionsTable.SourceRecord = CommissionedDetail.CDPriKey
                inner join SysUser on SysUser.SysUserID = @inSysUserId
                where theCommissionsTable.Source = 'CommissionedDetail' and
                    theCommissionsTable.OkToUpdate = 1
            end

            if( @theSplitCommissionsCount > 0 )
            begin
                -- We are adding new SplitCommissionsHold records
                insert into SplitCommissionsHold
                (
                    SplitCommissionsFID,
                    SysUserFID,
                    ChangedOn,
                    HoldUpdateStatus,
                    HoldUpdateDescription
                )
                select
                    SplitCommissionsFID = SplitCommissions.SCPriKey,
                    SysUserFID = @inSysUserId,
                    ChangedOn = @theCurrentUtcDateTime,
                    HoldUpdateStatus = case
                        when @inRemoveHold = 1 then 0
                        else 1
                    end,
                    HoldUpdateDescription = @inHoldUpdateDescription
                from @theCommissionsTable as theCommissionsTable
                inner join SplitCommissions on theCommissionsTable.SourceRecord = SplitCommissions.SCPriKey
                inner join SysUser on SysUser.SysUserID = @inSysUserId
                where theCommissionsTable.Source = 'SplitCommissions' and
                    theCommissionsTable.OkToUpdate = 1
            end

            -- Report partial results
            if( @theCommissionedDetailCount + @theSplitCommissionsCount != @theGPUpdateCount )
            begin
                -- Some items were already on/off hold in GP
                set @outErrorCode = case @inRemoveHold
                    when 1 then @SOME_GP_ITEMS_ALREADY_OFF_HOLD
                    else @SOME_GP_ITEMS_ALREADY_ON_HOLD
                end
            end
        end
    end
end -- if( @theAccountingSystemType = 'GP' )
GO
GRANT EXECUTE ON  [dbo].[UpdateCommissionOnHoldStatus_Legacy] TO [MssExec]
GO