/**
*
*    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 @theErrorMessage varchar(max)
    set xact_abort on
    begin try

        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 @theMasterCustomerAddressCode varchar(15)

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

        -- One contact lives in both the RM00101 table and the RM00102 table.
        -- Note that in all cases, the RM00101 record MUST exist.  If we don't
        -- update it here, then a user modified the RM00101 and changed the
        -- CUSTNMBR, which is a hard NO-NO!!
        update RM00101 set
            @theMasterCustomerAddressCode = 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 and
            (
                isnull( @inCustomerAddressCode, '' ) = '' or
                RM00101.ADRSCODE = @inCustomerAddressCode
            )

        if( isnull( @inCustomerAddressCode, '' ) = '' and isnull( @theMasterCustomerAddressCode, '' ) != '' )
        begin
            -- The only time we will get here will be if the RM00101.ADRSCODE does NOT
            -- exist in the RM00102 table.  If it is missing, we will insert it below.
            set @inCustomerAddressCode = @theMasterCustomerAddressCode
        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 try
    begin catch
        set @theErrorMessage = concat( 'There was a problem running UpsertARCCustomerContact_Legacy. ',
            'Error: ', ltrim( str( error_number() ) ), ' ', error_message(), ' ',
            'Line(', ltrim( str( error_line() ) ), ')' )

        raiserror ( @theErrorMessage, 16, 1 )
    end catch
end
GO
GRANT EXECUTE ON  [dbo].[UpsertARCCustomerContact_Legacy] TO [MssExec]
GO