/**
*  
*  This Stored Procedure creates an error in the BATErrorLog if either of the provided dates are
*    not within an open period in the current accounting system.
*
*    NOTE: This sp requires that a temp table (#theBranchTable) exist to put the branch information into
*        #theBranchTable
*            BranchTableID int identity( 1, 1 ),
*            BranchPrimaryKey int,
*            BranchID varchar(5),
*            Series int  -- Either 2(GL), 3(AR), or 4(AP)
*
*    Error Codes:
*    @ERROR_DATE_OUTSIDE_OPEN_PERIOD Used when a provided date is not within an open period in Great Plains
*    
*    Parameters:
*    @param @inAccountingSystemTypeCode The accounting system code.  See dbo.GetAccountingSystemType().
*    @param @inSessionID The unique session id for the current transaction processing
*    @param @inObjectName The name of the object responsible for the calling of the stored proc
*    @param @inObjectID The primary key of the object
*    @param @inDocumentDate The Great Plains date that the transaction is tagged with
*    @param @inJournalDate The Great Plains date that the transaction is recognized in the financials
*/

CREATE PROCEDURE [dbo].[VerifyDateInOpenAccountingPostingPeriodForSelectedBranches]
    @inAccountingSystemTypeCode varchar(2),
    @inSessionID varchar(50),
    @inObjectName varchar(20),
    @inObjectID int,
    @inJournalDate datetime
as
set nocount on

-- Constants
declare
    @ERROR_DATE_OUTSIDE_OPEN_PERIOD int,
    @GENERAL_LEDGER int,
    @ACCOUNTS_RECEIVABLE int,
    @ACCOUNTS_PAYABLE int
    
set @ERROR_DATE_OUTSIDE_OPEN_PERIOD = 231
set @GENERAL_LEDGER = 2
set @ACCOUNTS_RECEIVABLE = 3
set @ACCOUNTS_PAYABLE = 4

-- Check for the existance of the required temporary table
if object_id( 'tempdb..#theBranchTable' ) is null
begin
    raiserror( 'The temporary table [#theBranchTable] is required to execute [VerifyDateInOpenAccountingPostingPeriodForSelectedBranches] but could not be found.', 16, 1 )
end
else
begin
    declare
        @theStatusCode int,
        @theGPDBName sysname,
        @theJournalDateFormatted varchar(10),
        @theAccountingSystemDescription varchar(128)

    -- Remove the time from the dates for output to the error log
    set @theJournalDateFormatted = convert( varchar(10), @inJournalDate, dbo.GetDefaultDateFormat() )

    -- Loop through the branches and create an error for each invalid branch
    declare
        @theCounter int,
        @theRecordCount int,
        @theCurrentBranchPriKey int,
        @theCurrentBranchID varchar(5),
        @theCurrentSeries int,
        @theCurrentSeriesName varchar(2)
        
    set @theCounter = 1
    set @theRecordCount = ( select count(*) from #theBranchTable )

    while( @theCounter <= @theRecordCount )
    begin
        -- Get the current branch information
        select
            @theCurrentBranchPriKey = BranchPrimaryKey,
            @theCurrentBranchID = BranchID,
            @theCurrentSeries = Series,
            @theCurrentSeriesName =
                case Series
                    when @GENERAL_LEDGER then 'GL'
                    when @ACCOUNTS_RECEIVABLE then 'AR'
                    when @ACCOUNTS_PAYABLE then 'AP'
                    else 'XX'
                end
        from #theBranchTable as theBranchTable
        where BranchTableID = @theCounter
        
        if( @inAccountingSystemTypeCode = 'XL' )
        begin
            set @theAccountingSystemDescription = 'XLedger'
        end
        else if( @inAccountingSystemTypeCode = 'GP' )
        begin
            select
                @theAccountingSystemDescription = concat( GLControl.ADataBase, ' Dynamics GP database' ),
                @theGPDBName = GLControl.ADataBase
            from Branch
            inner join GLControl on Branch.GLCPriKey = GLControl.GLCPriKey
            where Branch.BranchPriKey = @theCurrentBranchPriKey
        end
        else
        begin
            set @theAccountingSystemDescription = @inAccountingSystemTypeCode
        end

        if( @theCurrentSeries in ( @GENERAL_LEDGER, @ACCOUNTS_RECEIVABLE, @ACCOUNTS_PAYABLE ) )
        begin
            -- Check for a valid Open Posting Period for the Journal Date on the current series
            exec VerifyDateInOpenAccountingPostingPeriod
                @inDate = @inJournalDate,
                @inGPDBName = @theGPDBName,
                @inSeries = @theCurrentSeries,
                @outStatusCode = @theStatusCode output,
                @inAccountingSystem = @inAccountingSystemTypeCode

            if( @theStatusCode != 0 )
            begin
                exec spBATRecordError
                    @inSessionID,
                    'Branch',
                    @theCurrentBranchPriKey,
                    @ERROR_DATE_OUTSIDE_OPEN_PERIOD,
                    @inObjectName,
                    @inObjectID,
                    'Journal',
                    @theJournalDateFormatted,
                    @theCurrentSeriesName,
                    @theAccountingSystemDescription,
                    @theCurrentBranchID
            end
        end
        else
        begin
            -- The current series is not a recognized series, so raise an exception
            declare @theErrorMessage varchar(128)
            set @theErrorMessage = 'The series [' + ltrim( str( @theCurrentSeries ) ) + '] is unknown to [VerifyDateInOpenAccountingPostingPeriodForSelectedBranches].  Processing cannot continue.'
            raiserror( @theErrorMessage, 16, 1 )
        end
        
        -- Update the counter to move to the next record
        set @theCounter = @theCounter + 1
    end
end
GO
GRANT EXECUTE ON  [dbo].[VerifyDateInOpenAccountingPostingPeriodForSelectedBranches] TO [MssExec]
GO