create procedure [dbo].[BcImportVendors]
    @items BcVendorImportItems 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,
        BcId uniqueidentifier not null,
        LastUpdatedInBcOn datetimeoffset not null

    )
    ;with MergeData as
    (
        select
            BcVendor.AccountingVendorFid,
            -- BcVendorClass.AccountingVendorClassFid,
            Items.BcId,
            -- Items.SubLedgerGroupExternalId,
            Items.[Name],
            Items.Number,
            -- Items.OnHold,
            Items.Phone,
            -- Items.Fax,
            -- Items.Contact,
            Items.Ten99Type,    
            -- Items.[Status],
            -- Items.[Hidden],
            Items.LastModified
        from @items Items
        left outer join BcVendor on Items.BcId = BcVendor.BcId
        --left outer join BcVendorClass on Items.SubLedgerGroupExternalId = BcVendorClass.BcId
    )
    merge AccountingVendor with (tablock) using MergeData on
        MergeData.AccountingVendorFid = AccountingVendor.AccountingVendorId
    when matched then update set
        [Name] = MergeData.[Name],
        VendorNumber = MergeData.Number,
        --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.Number,
            --MergeData.OnHold,
            0,
            MergeData.Phone,
            -- MergeData.Fax,
            --MergeData.Contact,
            MergeData.Ten99Type
            -- MergeData.[Status],
            -- MergeData.[Hidden],
            -- MergeData.AccountingVendorClassFid
        )
    output
        inserted.AccountingVendorId,
        MergeData.BcId,
        MergeData.LastModified
    into @AccountingVendorIdHolder;

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

        )
        values
        (
            MergeData.BcId,
            MergeData.AccountingVendorFid,
            @now, -- CreatedOn
            @now, -- LastImportedOn
            LastUpdatedInBcOn
        );


    --BCTD:  Import Addresses

    -- 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].[BcImportVendors] TO [MssExec]
GO