/**
*    
*    This procedure is called to determine if a given date falls within
*    an open accounting system posting period.  Great Plains and XLedger
*    have defined accounting period objects that may be open or closed.
*    
*    Input Parameters:
*    @param @inDate             Date to verify against Great Plains (GP) or XLedger (XL) posting period.
*    @param @inGPDBName        The Great Plains (GP) database name.  This is only used for GP.
*    @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.
*    @param @inAccountingSystem The two character code for the current accounting system.  This is
*        optional.  If not specified, we will look this up.  Some callers of this know the code and
*        by passing it it, we can be faster.
*/
CREATE PROCEDURE [dbo].[VerifyDateInOpenAccountingPostingPeriod]
    @inDate datetime,
    @inGPDBName sysname = null,
    @inSeries int = 2,
    @outStatusCode int output,
    @inAccountingSystem varchar(2) = null
as
set nocount on

declare @theSql nvarchar(512)
declare @theAccountingSystem varchar(2) = isnull( @inAccountingSystem, dbo.GetAccountingSystemType() )
set @outStatusCode = -1

if( @theAccountingSystem = 'XL' )
begin
    set @theSql = N'set @outStatusCode =
    (
        select top 1 [Closed] = case when XLedgerAccountingPeriod.[Open] = 1 then 0 else 1 end
        from XLedgerAccountingPeriod
        where convert( datetime, ''' + convert( varchar(10), @inDate, 101 ) + ''', 101 ) between XLedgerAccountingPeriod.FromDate and XLedgerAccountingPeriod.ToDate
    )'
end
else --if( @theAccountingSystem = 'GP' )
begin
    set @theSql = N'set @outStatusCode =
    (
        select top 1 SY40100.[Closed]
        from ' + @inGPDBName + '..SY40100 as SY40100
        where convert( datetime, ''' + convert( varchar(10), @inDate, 101 ) + ''', 101 ) between SY40100.PeriodDt and SY40100.PerdEnDt and
            SY40100.Series = ' + cast( @inSeries as varchar(32) ) + '
        order by SY40100.Closed desc
    )'
end

if( @theSql is not null )
begin
    exec sp_executesql @theSql, N'@outStatusCode int output', @outStatusCode output
end

set @outStatusCode = isnull( @outStatusCode, -1 )
GO
GRANT EXECUTE ON  [dbo].[VerifyDateInOpenAccountingPostingPeriod] TO [MssExec]
GO