/**
*
*    Inserts or updates a single customer contact for a specific customer number.  This is used by the
*    AR Credit and Collections module to add or modify customer contacts.
*
*    Do NOT call this SP directly.  Instead, call UpsertARCCustomerContact_Synonym and then that
*    synonym knows to call this SP or another one based upon that system's active accounting
*    system type.
*/

create procedure [dbo].[UpsertARCCustomerContact_Legacy]
    @inCustomerNumber varchar(15),
    @inCustomerAddressCode varchar(15),
    @inARCCustomerContactId int,
    @inContactPerson nvarchar(64),
    @inEmailAddress nvarchar(256),
    @inAddress1 dbo.Address,
    @inAddress2 dbo.Address,
    @inAddress3 dbo.Address,
    @inCity dbo.AddressCity,
    @inState dbo.AddressState,
    @inPostalCode dbo.AddressPostalCode,
    @inAddressCountryCodeStandardFID int,
    @inPhone1CountryCodeStandardFID int,
    @inPhone1AreaCode dbo.PhoneAreaCode,
    @inPhone1LocalNumber dbo.PhoneLocalNumber,
    @inPhone1Extension dbo.PhoneExtension,
    @inPhone2CountryCodeStandardFID int,
    @inPhone2AreaCode dbo.PhoneAreaCode,
    @inPhone2LocalNumber dbo.PhoneLocalNumber,
    @inPhone2Extension dbo.PhoneExtension,
    @inPhone3CountryCodeStandardFID int,
    @inPhone3AreaCode dbo.PhoneAreaCode,
    @inPhone3LocalNumber dbo.PhoneLocalNumber,
    @inPhone3Extension dbo.PhoneExtension,
    @inFaxCountryCodeStandardFID int,
    @inFaxAreaCode dbo.PhoneAreaCode,
    @inFaxLocalNumber dbo.PhoneLocalNumber
as
begin
    set nocount on

    declare @theRecordCount int
    declare @thePhone1 varchar(21) = dbo.FormatGreatPlainsPhoneNumberFromParts( @inPhone1AreaCode, @inPhone1LocalNumber, @inPhone1Extension )
    declare @thePhone2 varchar(21) = dbo.FormatGreatPlainsPhoneNumberFromParts( @inPhone2AreaCode, @inPhone2LocalNumber, @inPhone2Extension )
    declare @thePhone3 varchar(21) = dbo.FormatGreatPlainsPhoneNumberFromParts( @inPhone3AreaCode, @inPhone3LocalNumber, @inPhone3Extension )
    declare @theFaxPhone varchar(21) = dbo.FormatGreatPlainsPhoneNumberFromParts( @inFaxAreaCode, @inFaxLocalNumber, null )

    declare @theAlpha3CountryCode varchar(3)
    if( @inAddressCountryCodeStandardFID > 0 )
    begin
        set @theAlpha3CountryCode = ( select VanlineCountryCode from CountryCodeStandard where CountryCodeStandardID = @inAddressCountryCodeStandardFID )
    end

    if( isnull( @inCustomerAddressCode, '' ) = '' )
    begin
        -- Contact lives in both the RM00101 table and the RM00102 table.
        -- Note that in all cases, this record MUST exist.  If we don't update it,
        -- then a user modified the RM00101 and changed the CUSTNMBR, which is a
        -- hard NO-NO!!
        update RM00101 set
            @inCustomerAddressCode = RM00101.ADRSCODE,
            CNTCPRSN = case
                when len( @inContactPerson ) > 61 then substring( @inContactPerson, 1, 61 )
                else isnull( @inContactPerson, '' )
            end,
            ADDRESS1 = case
                    when len( @inAddress1 ) > 61 then substring( @inAddress1, 1, 61 )
                    else isnull( @inAddress1, '' )
                end,
            ADDRESS2 = case
                    when len( @inAddress2 ) > 61 then substring( @inAddress2, 1, 61 )
                    else isnull( @inAddress2, '' )
                end,
            ADDRESS3 = case
                    when len( @inAddress3 ) > 61 then substring( @inAddress3, 1, 61 )
                    else isnull( @inAddress3, '' )
                end,
            CITY = case
                    when len( @inCity ) > 35 then substring( @inCity, 1, 35 )
                    else isnull( @inCity, '' )
                end,
            [STATE] = case
                    when len( @inState ) > 29 then substring( @inState, 1, 29 )
                    else isnull( @inState, '' )
                end,
            ZIP = case
                    when len( @inPostalCode ) > 11 then substring( @inPostalCode, 1, 11 )
                    else isnull( @inPostalCode, '' )
                end,
            COUNTRY = isnull( @theAlpha3CountryCode, '' ),
            PHONE1 = @thePhone1,
            PHONE2 = @thePhone2,
            PHONE3 = @thePhone3,
            FAX = @theFaxPhone,
            MODIFDT = convert( date, getdate() )
        from RM00101_Synonym as RM00101
        left outer join CountryCodeStandard on CountryCodeStandard.CountryCodeStandardID = @inAddressCountryCodeStandardFID
        where RM00101.CUSTNMBR = @inCustomerNumber
    end

    if( isnull( @inCustomerAddressCode, '' ) != '' )
    begin
        -- Contact lives in RM00102 table
        update RM00102 set
            CNTCPRSN = case
                when len( @inContactPerson ) > 61 then substring( @inContactPerson, 1, 61 )
                else isnull( @inContactPerson, '' )
            end,
            ADDRESS1 = case
                    when len( @inAddress1 ) > 61 then substring( @inAddress1, 1, 61 )
                    else isnull( @inAddress1, '' )
                end,
            ADDRESS2 = case
                    when len( @inAddress2 ) > 61 then substring( @inAddress2, 1, 61 )
                    else isnull( @inAddress2, '' )
                end,
            ADDRESS3 = case
                    when len( @inAddress3 ) > 61 then substring( @inAddress3, 1, 61 )
                    else isnull( @inAddress3, '' )
                end,
            CITY = case
                    when len( @inCity ) > 35 then substring( @inCity, 1, 35 )
                    else isnull( @inCity, '' )
                end,
            [STATE] = case
                    when len( @inState ) > 29 then substring( @inState, 1, 29 )
                    else isnull( @inState, '' )
                end,
            ZIP = case
                    when len( @inPostalCode ) > 11 then substring( @inPostalCode, 1, 11 )
                    else isnull( @inPostalCode, '' )
                end,
            COUNTRY = isnull( @theAlpha3CountryCode, '' ),
            PHONE1 = @thePhone1,
            PHONE2 = @thePhone2,
            PHONE3 = @thePhone3,
            FAX = @theFaxPhone,
            MODIFDT = convert( date, getdate() )
        from RM00102_Synonym as RM00102
        where RM00102.CUSTNMBR = @inCustomerNumber and
            RM00102.ADRSCODE = @inCustomerAddressCode

        set @theRecordCount = @@ROWCOUNT

        if( @theRecordCount = 0 )
        begin
            -- We need to insert a record here.  Note that if the RM00101 customer
            -- number does NOT exist, we will not be inserting anything here.  Also,
            -- all GP fields not listed here insert a non null default as defined by
            -- the COLUMN_DEFAULT in INFORMATION_SCHEMA.COLUMNS.
            insert into RM00102_Synonym
            (
                CUSTNMBR,
                ADRSCODE,
                CNTCPRSN,
                ADDRESS1,
                ADDRESS2,
                ADDRESS3,
                CITY,
                [STATE],
                ZIP,
                COUNTRY,
                PHONE1,
                PHONE2,
                PHONE3,
                FAX,
                MODIFDT,
                CREATDDT
            )
            select
                CUSTNMBR = @inCustomerNumber,
                ADRSCODE = @inCustomerAddressCode,
                CNTCPRSN = case
                    when len( @inContactPerson ) > 61 then substring( @inContactPerson, 1, 61 )
                    else isnull( @inContactPerson, '' )
                end,
                ADDRESS1 = case
                        when len( @inAddress1 ) > 61 then substring( @inAddress1, 1, 61 )
                        else isnull( @inAddress1, '' )
                    end,
                ADDRESS2 = case
                        when len( @inAddress2 ) > 61 then substring( @inAddress2, 1, 61 )
                        else isnull( @inAddress2, '' )
                    end,
                ADDRESS3 = case
                        when len( @inAddress3 ) > 61 then substring( @inAddress3, 1, 61 )
                        else isnull( @inAddress3, '' )
                    end,
                CITY = case
                        when len( @inCity ) > 35 then substring( @inCity, 1, 35 )
                        else isnull( @inCity, '' )
                    end,
                [STATE] = case
                        when len( @inState ) > 29 then substring( @inState, 1, 29 )
                        else isnull( @inState, '' )
                    end,
                ZIP = case
                        when len( @inPostalCode ) > 11 then substring( @inPostalCode, 1, 11 )
                        else isnull( @inPostalCode, '' )
                    end,
                COUNTRY = isnull( @theAlpha3CountryCode, '' ),
                PHONE1 = @thePhone1,
                PHONE2 = @thePhone2,
                PHONE3 = @thePhone3,
                FAX = @theFaxPhone,
                MODIFDT = convert( date, getdate() ),
                CREATDDT = convert( date, getdate() )
            from RM00101_Synonym as RM00101
            where RM00101.CUSTNMBR = @inCustomerNumber
        end

        update SY01200 set
            INET1 = case
                when len( @inEmailAddress ) > 201 then substring( @inEmailAddress, 1, 201 )
                else isnull( @inEmailAddress, '' )
            end
        from SY01200_Synonym as SY01200
        where SY01200.Master_Type = 'CUS' and
            SY01200.Master_ID = @inCustomerNumber and
            SY01200.ADRSCODE = @inCustomerAddressCode

        set @theRecordCount = @@ROWCOUNT

        if( @theRecordCount = 0 and len( @inEmailAddress ) > 0 )
        begin
            -- We need to insert a record here, provided there is an email
            -- address to save.
            insert into SY01200_Synonym
            (
                Master_Type,
                Master_ID,
                ADRSCODE,
                INET1,
                INETINFO,
                EmailToAddress,
                EmailCcAddress,
                EmailBccAddress
            )
            select
                Master_Type = 'CUS',
                Master_ID = @inCustomerNumber,
                ADRSCODE = @inCustomerAddressCode,
                INET1 = case
                    when len( @inEmailAddress ) > 201 then substring( @inEmailAddress, 1, 201 )
                    else isnull( @inEmailAddress, '' )
                end,
                INETINFO = '',
                EmailToAddress = '',
                EmailCcAddress = '',
                EmailBccAddress = ''
        end
    end
end
GO
GRANT EXECUTE ON  [dbo].[UpsertARCCustomerContact_Legacy] TO [MssExec]
GO