CREATE procedure [dbo].[XLedgerImportSuppliers]
    @items XLedgerSupplierImportItems readonly,
    @addresses XLedgerAddressImportItems readonly

as
begin
    set nocount on

    declare @now datetimeoffset = sysdatetimeoffset()

    -- Insert/Update the AccountingVendorClass table from the import items.
    declare @AccountingVendorClassIdHolder table
    (
        AccountingVendorClassFid int not null,
        SubLedgerGroupExternalId int not null
    )
    ;with MergeData as
    (
        select distinct
            XLedgerSupplierClass.AccountingVendorClassFid,
            Items.SubLedgerGroupExternalId,
            Items.SubLedgerGroupCode,
            Items.SubLedgerGroupDescription
        from @items Items
        left outer join XLedgerSupplierClass on Items.SubLedgerGroupExternalId = XLedgerSupplierClass.ExternalDbId
        where Items.SubLedgerGroupExternalId is not null
    )
    merge AccountingVendorClass with (tablock) using MergeData on
        MergeData.AccountingVendorClassFid = AccountingVendorClass.AccountingVendorClassId
    when matched then update set
        ClassCode = MergeData.SubLedgerGroupCode,
        ClassDescription = MergeData.SubLedgerGroupDescription
    when not matched then
        insert
        (
            ClassCode,
            ClassDescription
        )
        values
        (
            MergeData.SubLedgerGroupCode,
            MergeData.SubLedgerGroupDescription
        )
    output
        inserted.AccountingVendorClassId,
        MergeData.SubLedgerGroupExternalId
    into @AccountingVendorClassIdHolder;

    -- Insert/Update the XLedgerSupplierClass table from the import items.
    ;with MergeData as
    (
        select distinct
            AccountingVendorClassIdHolder.SubLedgerGroupExternalId,
            AccountingVendorClassIdHolder.AccountingVendorClassFid
        from @AccountingVendorClassIdHolder as AccountingVendorClassIdHolder
    )
    merge XLedgerSupplierClass with (tablock) using MergeData on
        MergeData.SubLedgerGroupExternalId = XLedgerSupplierClass.ExternalDbId
    when matched then update set
        LastImportedOn = @now
    when not matched then
        insert
        (
            ExternalDbId,
            AccountingVendorClassFid,
            CreatedOn,
            LastImportedOn
        )
        values
        (
            MergeData.SubLedgerGroupExternalId,
            MergeData.AccountingVendorClassFid,
            @now, -- CreatedOn
            @now -- LastImportedOn
        );

    -- Insert/Update the AccountingVendor table from the import items.
    declare @AccountingVendorIdHolder table
    (
        AccountingVendorFid int not null,
        ExternalDbId int not null
    )
    ;with MergeData as
    (
        select
            XLedgerSupplier.AccountingVendorFid,
            XLedgerSupplierClass.AccountingVendorClassFid,
            Items.ExternalDbId,
            Items.SubLedgerGroupExternalId,
            Items.[Name],
            Items.VendorNumber,
            Items.OnHold,
            Items.Phone,
            Items.Fax,
            Items.Contact,
            Items.Ten99Type,    
            Items.[Status],
            Items.[Hidden]
        from @items Items
        left outer join XLedgerSupplier on Items.ExternalDbId = XLedgerSupplier.ExternalDbId
        left outer join XLedgerSupplierClass on Items.SubLedgerGroupExternalId = XLedgerSupplierClass.ExternalDbId
    )
    merge AccountingVendor with (tablock) using MergeData on
        MergeData.AccountingVendorFid = AccountingVendor.AccountingVendorId
    when matched then update set
        [Name] = MergeData.[Name],
        VendorNumber = MergeData.VendorNumber,
        OnHold = MergeData.OnHold,
        Phone = MergeData.Phone,
        Fax = MergeData.Fax,
        Contact = MergeData.Contact,
        Ten99Type = MergeData.Ten99Type,
        [Status] = MergeData.[Status],
        [Hidden] = MergeData.[Hidden],
        AccountingVendorClassFid = MergeData.AccountingVendorClassFid
    when not matched then
        insert
        (
            [Name],
            VendorNumber,
            OnHold,
            Phone,
            Fax,
            Contact,
            Ten99Type,
            [Status],
            [Hidden],
            AccountingVendorClassFid
        )
        values
        (
            MergeData.[Name],
            MergeData.VendorNumber,
            MergeData.OnHold,
            MergeData.Phone,
            MergeData.Fax,
            MergeData.Contact,
            MergeData.Ten99Type,
            MergeData.[Status],
            MergeData.[Hidden],
            MergeData.AccountingVendorClassFid
        )
    output
        inserted.AccountingVendorId,
        MergeData.ExternalDbId
    into @AccountingVendorIdHolder;

    -- Insert/Update the XLedgerSupplier table from the import items.
    ;with MergeData as
    (
        select
            AccountingVendorIdHolder.ExternalDbId,
            AccountingVendorIdHolder.AccountingVendorFid
        from @AccountingVendorIdHolder as AccountingVendorIdHolder
    )
    merge XLedgerSupplier with (tablock) using MergeData on
        MergeData.ExternalDbId = XLedgerSupplier.ExternalDbId
    when matched then update set
        LastImportedOn = @now
    when not matched then
        insert
        (
            ExternalDbId,
            AccountingVendorFid,
            CreatedOn,
            LastImportedOn
        )
        values
        (
            MergeData.ExternalDbId,
            MergeData.AccountingVendorFid,
            @now, -- CreatedOn
            @now -- LastImportedOn
        );

    -- Insert/Update the AccountingVendorAddress table from the import items.
    ;with MergeData as
    (
        select
            AccountingVendor.AccountingVendorId,
            AccountingVendorAddressType.AccountingVendorAddressTypeId,
            AddressDeleted = convert(bit, case when Addresses.ParentExternalDbId is null then 1 else 0 end ),
            Addresses.Address1,
            Addresses.Address2,
            Addresses.Address3,
            Addresses.City,
            Addresses.[State],
            Addresses.PostalCode,
            CountryCodeStandard.CountryCodeStandardID
        from @items Items
        inner join XLedgerSupplier on Items.ExternalDbId = XLedgerSupplier.ExternalDbId
        cross join AccountingVendorAddressType
        inner join AccountingVendor on XLedgerSupplier.AccountingVendorFid = AccountingVendor.AccountingVendorId
        left outer join @addresses Addresses on
            XLedgerSupplier.ExternalDbId = Addresses.ParentExternalDbId and
            AccountingVendorAddressType.TypeName = Addresses.AddressType
        left outer join CountryCodeStandard on Addresses.CountryAlpha2Code = CountryCodeStandard.Alpha2Code
    ) merge AccountingVendorAddress with (tablock) using MergeData ON
        MergeData.AccountingVendorId = AccountingVendorAddress.AccountingVendorFid AND
        MergeData.AccountingVendorAddressTypeId = AccountingVendorAddress.AccountingVendorAddressTypeFid
    when matched and MergeData.AddressDeleted = 0 then update SET
        Address1 = MergeData.Address1,
        Address2 = MergeData.Address2,
        Address3 = MergeData.Address3,
        City = MergeData.City,
        [State] = MergeData.[State],
        [PostalCode] = MergeData.PostalCode,
        [CountryCodeStandardFID] = MergeData.CountryCodeStandardID
    when matched and MergeData.AddressDeleted = 1 then delete
    when not matched and AddressDeleted = 0 then insert (
            AccountingVendorFid,
            AccountingVendorAddressTypeFid,
            Address1,
            Address2,
            Address3,
            City,
            [State],
            PostalCode,
            CountryCodeStandardFID
    ) values (
        MergeData.AccountingVendorId,
        MergeData.AccountingVendorAddressTypeId,
        MergeData.Address1,
        MergeData.Address2,
        MergeData.Address3,
        MergeData.City,
        MergeData.[State],
        MergeData.PostalCode,
        MergeData.CountryCodeStandardID
    );

    -- Lastly, make sure every AccountingVendor has an AccountingVendorAddress of the default type.
    -- This ensures that every vendor has a default address, which is an assurance that Dynamics
    -- GP gave us.
    insert into AccountingVendorAddress
    (
        AccountingVendorFid,
        AccountingVendorAddressTypeFid,
        CountryCodeStandardFID
    )
    select
        AccountingVendorFid = XLedgerSupplier.AccountingVendorFid,
        AccountingVendorAddressTypeFid = AccountingVendorAddressType.AccountingVendorAddressTypeId,
        CountryCodeStandardFID = dbo.udfGetDefaultCountryCodeStandardID()
    from @items as Items
    inner join XLedgerSupplier on Items.ExternalDbId = XLedgerSupplier.ExternalDbId
    inner join AccountingVendorAddressType on AccountingVendorAddressType.TypeName = dbo.GetDefaultAccountingVendorAddressTypeName()
    left outer join AccountingVendorAddress on AccountingVendorAddress.AccountingVendorFid = XLedgerSupplier.AccountingVendorFid and
        AccountingVendorAddress.AccountingVendorAddressTypeFid = AccountingVendorAddressType.AccountingVendorAddressTypeId
    where AccountingVendorAddress.AccountingVendorAddressId is null

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