CREATE PROCEDURE [dbo].[XLedgerImportAccountingPeriods]
    @items XLedgerAccountingPeriodImportItems readonly
AS
begin
    set nocount on

    declare @now datetimeoffset = sysdatetimeoffset();

    ;with Items as
    (
        select
            ImportItems.XLedgerDbId,
            ImportItems.FromDate,
            ImportItems.ToDate,
            ImportItems.FiscalYear,
            ImportItems.FiscalPeriod,
            ImportItems.Code,
            ImportItems.[Description]
        from @items ImportItems
    )
    merge XLedgerAccountingPeriod with (tabLock) using Items ON
        Items.XLedgerDbId = XLedgerAccountingPeriod.ExternalDbId
    when not matched then
        insert(
            ExternalDbId,
            FromDate,
            ToDate,
            FiscalYear,
            FiscalPeriod,
            Code,
            [Description],
            [Open],
            CreatedOn,
            LastImportedOn,
            OpenStatusChangedOn)
        values(
            Items.XLedgerDbId,
            Items.FromDate,
            Items.ToDate,
            Items.FiscalYear,
            Items.FiscalPeriod,
            Items.Code,
            Items.Description,
            1, --open
            @now,
            @now,
            @now
        )
    when matched then update set
            FromDate = Items.FromDate,
            ToDate = Items.ToDate,
            FiscalYear = Items.FiscalYear,
            FiscalPeriod = Items.FiscalPeriod,
            Code = Items.Code,
            [Description] = Items.Description,
            [Open] = 1,
            LastImportedOn = @now,
            OpenStatusChangedOn =
                case
                    when 0 = XLedgerAccountingPeriod.[Open] then @now -- going from closed to open, so update this value
                    else XLedgerAccountingPeriod.OpenStatusChangedOn -- keep as is since it was already open.
                end
    when not matched by source and XLedgerAccountingPeriod.[Open] = 1 then
        update set
            [Open] = 0,
            OpenStatusChangedOn = @now
    ;
end
GO
GRANT EXECUTE ON  [dbo].[XLedgerImportAccountingPeriods] TO [MssExec]
GO