/**
*    
*    This procedure is called to determine if a given date falls within an open
*    accounting system fiscal period.  This works for Great Plains (GP) or
*    XLedger (XL) accounting systems.  Other accounting systems will always
*    return zero (0) (@inDate accounting period is open).
*    
*    Input Parameters:
*    @param @inDate             Date to verify against Great Plains (GP) or XLedger (XL) posting period.
*    @param @inSeries        The series module we are looking to determine open posting dates for.
*        This is only used for GP since you can open and close GL(2), AR(3) and AP(4) individually.
*    @param @outStatusCode    Status indicating whether or not the date provided falls within an open
*        posting period for the specified series module in the current accounting system:
*        0 - no closed and one open accounting system record was found for date given.
*        1 - a closed accounting system record was found for date given.
*        -1- no accounting system record was found for date given.
*/
CREATE PROCEDURE [dbo].[VerifyDateInOpenAccountingPostingPeriodForAllBranches]
    @inDate datetime,
    @inSeries int,
    @outStatusCode int output
as
set nocount on

-- Local variables
declare @theGPDBName sysname
declare @theGPNameId int
declare @theGPNameCount int
declare @theGPDBNames table
(
    GPNameId int identity( 1, 1 ) not null,
    GPDBName sysname
)
declare @theStatusCode int

-- Initialization
set @theStatusCode = 0
set @outStatusCode = 0

declare @theAccountingSystem varchar(2) = dbo.GetAccountingSystemType()
if( @theAccountingSystem = 'XL' )
begin
    exec dbo.VerifyDateInOpenAccountingPostingPeriod
        @inDate = @inDate,
        @outStatusCode = @theStatusCode output,
        @inAccountingSystem = @theAccountingSystem

    if( @theStatusCode != 0 )
    begin
        set @outStatusCode = 1
    end
end
else if( @theAccountingSystem = 'GP' )
    begin
    -- Get the names of all the GP databases that are mapped to branches.
    insert into @theGPDBNames ( GPDBName)
    select distinct GLControl.ADataBase
    from Branch
    inner join GLControl on GLControl.GLCPriKey = Branch.GLCPriKey

    set @theGPNameCount = @@rowcount

    -- Loop through each distinct GP database name
    set @theGPNameId = 1
    while ( @theGPNameId <= @theGPNameCount )
    begin
        -- Determine if the provided date falls within an open period in the provided account
        set @theGPDBName = ( select GPDBName from @theGPDBNames where GPNameId = @theGPNameId )
        exec dbo.VerifyDateInOpenAccountingPostingPeriod
            @inDate = @inDate,
            @inGPDBName = @theGPDBName,
            @inSeries = @inSeries,
            @outStatusCode = @theStatusCode output,
            @inAccountingSystem = @theAccountingSystem

        -- If the date fell in a closed period of the provided account, set the status to 1
        if( 0 <> isnull( @theStatusCode, 1 ) )
        begin
            set @outStatusCode = 1
            set @theGPNameId = @theGPNameCount
        end

        -- Increment to the next distinct GP database name (they all should be the same name).
        set @theGPNameId = @theGPNameId + 1
    end
end
GO
GRANT EXECUTE ON  [dbo].[VerifyDateInOpenAccountingPostingPeriodForAllBranches] TO [MssExec]
GO