CREATE PROCEDURE [dbo].[XLedgerImportXgls]
    @items XglImportItems readonly
as
begin
    set nocount on

    declare @now datetimeoffset = sysdatetimeoffset()

    ;with ImportData as
    (
        select
            Item.XLedgerDbId,
            Item.CanPost,
            Item.Code,
            Item.[Description],
            XLedgerAuthorityTypeObjectValue.XLedgerAuthorityTypeObjectValueId,
            XLedgerMoveTypeObjectValue.XLedgerMoveTypeObjectValueId,
            XLedgerCommodityTypeObjectValue.XLedgerCommodityTypeObjectValueId,
            XLedgerExpenseType.XLedgerExpenseTypeId
        from @items Item
        inner join XLedgerAuthorityTypeObjectValue on
            Item.LineOfBusinessXLedgerDbId = XLedgerAuthorityTypeObjectValue.XLedgerDbId
        inner join XLedgerMoveTypeObjectValue on
            Item.MoveTypeXLedgerDbId = XLedgerMoveTypeObjectValue.XLedgerDbId
        inner join XLedgerCommodityTypeObjectValue on Item.CommodityTypeXLedgerDbId = XLedgerCommodityTypeObjectValue.XLedgerDbId
        inner join XLedgerExpenseType on Item.ExpenseTypeName = XLedgerExpenseType.TypeName
    )
    merge Xgl with(tablock) using ImportData on
        ImportData.XLedgerDbId = Xgl.XLedgerDbId
    when not matched then
        insert(
            Code,
            [Description],
            XLedgerDbId,
            [Hidden],
            CanPost,
            XLedgerAuthorityTypeObjectValueFid,
            XLedgerMoveTypeObjectValueFid,
            XLedgerCommodityTypeObjectValueFid,
            XLedgerExpenseTypeFid,
            CreatedOn,
            LastImportedOn
            )
        values(
            ImportData.Code,
            ImportData.[Description],
            ImportData.XLedgerDbId,
            0, -- Hidden
            ImportData.CanPost,
            XLedgerAuthorityTypeObjectValueId,
            XLedgerMoveTypeObjectValueId,
            XLedgerCommodityTypeObjectValueId,
            XLedgerExpenseTypeId,
            @now, -- CreatedOn
            @now ) -- LastImportedOn
    when matched then update set
        Code = ImportData.Code,
        [Description] = ImportData.[Description],
        [Hidden] = 0, -- the presence of the item means it's not hidden.
        [CanPost] = ImportData.CanPost,
        XLedgerAuthorityTypeObjectValueFID = XLedgerAuthorityTypeObjectValueId,
        XLedgerMoveTypeObjectValueFID = XLedgerMoveTypeObjectValueId,
        XLedgerCommodityTypeObjectValueFID = XLedgerCommodityTypeObjectValueId,
        XLedgerExpenseTypeFid = XLedgerExpenseTypeId,
        LastImportedOn = @now
    when not matched by source and Xgl.[Hidden] = 0 then update set
        [Hidden] = 1,
        [LastImportedOn] = @now
    ;

end
GO
GRANT EXECUTE ON  [dbo].[XLedgerImportXgls] TO [MssExec]
GO