/**
*    Don't use this stored proc directly but instead use UpdateCommissionOnHoldStatus_Synonym.
*    UpdateCommissionOnHoldStatus_Synonym will either point to this stored proc or to
*    UpdateCommissionOnHoldStatus_Legacy.
*
*    Places the specified commissions or split commissions on hold or removes
*    the hold in the CommissionedDetailHold and SplitCommissionsHold tables.
*    Note: This does not change the OnHoldFlag in AcctTransactions.
*    Logs an audit trail of the action for the specified commissions/splits.
*    
*    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_XLedger]
(
    @inOrderID int,
    @inCommissionedDetailIds varchar(max),
    @inSplitCommissionsIds varchar(max),
    @inRemoveHold bit,
    @inHoldUpdateDescription varchar(30),
    @inSysUserId int,
    @outErrorCode int output
)
as
set nocount on

declare @theCommissionedDetailCount int
declare @theSplitCommissionsCount int
declare @theCurrentUtcDateTime datetime
declare @theNewHoldStatus nchar(1)
declare @theOldHoldStatus nchar(1)
declare @theCommissionsTable AcctTransactionsSourceType

-- Potential error code
declare @REMOVE_HOLD_PARAMETER_IS_NULL int
select @REMOVE_HOLD_PARAMETER_IS_NULL = 2302

-- Initialize variables
select
    @outErrorCode = 0,
    @theCommissionedDetailCount = 0,
    @theSplitCommissionsCount = 0,
    @theCurrentUtcDateTime = getutcdate(),
    @theOldHoldStatus = case
        when @inRemoveHold = 1 then N'1'
        else N'0'
    end,
    @theNewHoldStatus = case
        when @inRemoveHold = 1 then N'0'
        else N'1'
    end

if( @inRemoveHold is null )
begin
    -- @inRemoveHold must be specified.
    set @outErrorCode = @REMOVE_HOLD_PARAMETER_IS_NULL
end
else
begin
    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

        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
    end
end
GO
GRANT EXECUTE ON  [dbo].[UpdateCommissionOnHoldStatus_XLedger] TO [MssExec]
GO