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

AS
begin
    set nocount on

    declare @AccountingSupplierIdHolder table
    (
        AccountingVendorFid int not null,
        ExternalDbId int not null
    )
    ;with
    MergeData as
    (
        select
            XLedgerSupplier.AccountingVendorFid,
            Items.ExternalDbId,
            Items.[Name],
            Items.Code
        from @items Items
        left outer join XLedgerSupplier on Items.ExternalDbId = XLedgerSupplier.ExternalDbId
    )
    merge AccountingVendor with (tablock) using MergeData on
        MergeData.AccountingVendorFid = AccountingVendor.AccountingVendorId
    when matched then update set
        [Name] = MergeData.[Name],
        Code = MergeData.Code
    when not matched by target then insert( [Name], Code, Deleted ) values (MergeData.[Name], MergeData.Code, 0)
    output inserted.AccountingVendorId, MergeData.ExternalDbId into @AccountingSupplierIdHolder
;

    insert into XLedgerSupplier
    (
        ExternalDbId,
        AccountingVendorFid
    )
    select
        ExternalDbId = AccountingSupplierIdHolder.ExternalDbId,
        AccountingSupplierIdHolder.AccountingVendorFid
    from @AccountingSupplierIdHolder AccountingSupplierIdHolder
    where not exists (
        select top 1 1
            from XLedgerSupplier
            where ExternalDbId = AccountingSupplierIdHolder.ExternalDbId )



    ;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
    )
    ;

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