SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[RealMagnet_GetQueryData]
(
	@LoginName varchar(100),
	@EntityID varchar(100),
	@QueryID varchar(100),
	@KindID varchar(100)
)
as
declare @SQLText varchar(2000)
if @EntityID = 'View'
	begin
		select 
			@SQLText = 'select * from ' + quotename(S.name) + '.' + quotename(V.name) + ';'
		from
			sys.views V
			inner join
			sys.schemas S
				on S.schema_id = V.schema_id
		where V.object_id = @QueryID

		exec(@SQLText)
	end
else
	select 
		c.* 
	from 
		dbo.MKT_LIST_DETAIL mkt 
		inner join 
		dbo.CUSTOMER c on c.MASTER_CUSTOMER_ID = mkt.MASTER_CUSTOMER_ID or c.PROSPECT_ID = mkt.PROSPECT_ID 
	where 
		mkt.LIST_CODE = @QueryID

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[RealMagnet_GetQueries]
(
	@LoginName varchar(100),
	@EntityID varchar(100)
)
as
if @EntityID = 'View'
	select
		V.object_id as ID,
		V.name as Name,
		'SQL_View' as KindId,
		'SQL View' as KindName,
		S.schema_id as OwnerId,
		S.name as OwnerName
	from
		sys.views V
		inner join
		sys.schemas S
			on S.schema_id = V.schema_id
	where V.name like 'RealMagnet[_]%'
else
	select 
		mkt.LIST_CODE as ID,
		coalesce(mkt.[DESCRIPTION], mkt.LIST_QUERY_NAME) as Name,
		'mkt_list' as KindID,
		'Marketing List' as KindName,
		mkt.LIST_OWNER as OwnerID,
		usr.[USER_NAME] as OwnerName
	from 
		dbo.MKT_LIST_MASTER mkt
		left outer join
		dbo.PSM_USER usr on usr.[USER_ID] = mkt.LIST_OWNER
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[RealMagnet_GetFields]
(
	@LoginName varchar(100),
	@EntityID varchar(100),
	@QueryID varchar(100),
	@KindID varchar(100)
)
as
if @EntityID = 'View'
	select 
		C.name as ID,
		C.name as Name
	from 
		sys.columns C
	where 
		C.object_id = @QueryID
else	
	select 
		sac.name as ID, 
		Replace(sac.Name, '_', ' ') as Name 
	from 
		sys.all_columns sac 
	where 
		sac.object_id = object_id('dbo.CUSTOMER')
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[RealMagnet_GetEntities]
(
	@LoginName varchar(100)
)
as
select 'Customer' as ID, 'Customer' as Name
union all
select 'View', 'View'
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[RealMagnet_UpdateInterestAreaSubscription]
(
	@TrackingActivityID bigint,
	@ActivityCode varchar(20),
	@ActivitySubCode varchar(50),
	@CustomMemberID varchar(50),
	@DateStampUTC datetime,
	@InterestAreaID varchar(50)
)
AS

declare @OrgID varchar(50) = -- PLEASE SET THE CORRECT ORG ID
declare @OrgUnitID varchar(50) = -- PLEASE SET THE CORRECT ORG UNIT ID
declare @SubSystem varchar(10) = 'CUS'
declare @DateStampLocal datetime = convert(datetime,convert(datetimeoffset,convert(nvarchar(25),@DateStampUTC,127)))
declare @OptedInFlag YES_NO, @OptionCode CODE_CODE, @OptionSubCode CODE_CODE, @OptionDescr PRODUCT_MEDIUM_DESCRIPTION, @CodeLen int;

if len(@InterestAreaID) > 2 and 1=isnumeric('-' + substring(@InterestAreaID,1,2)+'.0e0')
	begin
	set @CodeLen = convert(int,substring(@InterestAreaID,1,2));
	set @OptionCode = substring(@InterestAreaID,3,@CodeLen);
	set @OptionSubcode = substring(@InterestAreaID,3+@CodeLen,Len(@InterestAreaID));
	set @OptionSubcode = case when len(@OptionSubCode)=0 then null else @OptionSubCode end;
	select @OptionDescr = coalesce(S.Descr,C.Descr)
	from APP_CODE C left outer join APP_SUBCODE S on S.CODE=C.CODE and S.[TYPE]=C.[TYPE] And S.SUBCODE = @OptionSubCode
	where	C.[TYPE] ='OPT_IN_INTEREST_AREA' 
		AND C.ACTIVE_FLAG='Y' 
		AND C.SUBSYSTEM = @SubSystem
		AND C.CODE = @OptionCode
		AND (S.SUBCODE is null or S.ACTIVE_FLAG='Y')
		AND (S.SUBCODE is null or S.OPTION_1 like '%EMAIL%')
	set @OptionDescr = coalesce(@OptionDescr,@OptionSubcode,@OptionCode);
	end
else
	begin
	set @OptionCode = @InterestAreaID;
	select @OptionDescr = C.Descr
	from APP_CODE C
	where	C.[TYPE] ='OPT_IN_INTEREST_AREA' 
		AND C.ACTIVE_FLAG='Y' 
		AND C.SUBSYSTEM = @SubSystem
		AND C.CODE = @OptionCode
	set @OptionDescr = coalesce(@OptionDescr,@OptionCode);
	end

if @ActivityCode = 'Subscribed'
	begin
	set @OptedInFlag = 'Y'
	end	
else
	begin
	set @OptedInFlag = 'N'
	end

update 
	dbo.CUS_OPT_IN
set
	OPTED_IN_FLAG  = @OptedInFlag, 
	OPTED_IN_DATE  = case when @OptedInFlag = 'Y' then @DateStampLocal else OPTED_IN_DATE end,
	OPTED_OUT_DATE = case when @OptedInFlag = 'N' then @DateStampLocal else OPTED_OUT_DATE end,
	MODOPER = 'RealMagnet', 
	MODDATE = GETDATE(), 
	CONCURRENCY_ID = CONCURRENCY_ID + 1
where 
	MASTER_CUSTOMER_ID = @CustomMemberID
	and 
	OPTION_SHORT_NAME = @OptionCode
	and
	((OPTION_SHORT_SUBNAME = @OptionSubCode) or (@OptionSubCode is null and (OPTION_SHORT_SUBNAME is null or 0=len(OPTION_SHORT_SUBNAME))))
	
IF (@@ROWCOUNT = 0)
BEGIN
	declare @NextID nvarchar(255)
	exec GetNextID @pstrTable=N'CUS_OPT_IN', @pstrColumn=N'CUS_OPT_IN_ID', @pstrUser=N'admin', @NewID=@NextID output

	insert CUS_OPT_IN
	(
		CUS_OPT_IN_ID, 
		MASTER_CUSTOMER_ID, 
		SUB_CUSTOMER_ID, 
		ORG_ID, 
		ORG_UNIT_ID, 
		OPTION_SHORT_NAME, 
		OPTION_SHORT_SUBNAME, 
		PRODUCT_ID, 
		OPTION_DESCR, 
		OPTED_IN_FLAG, 
		OPTED_IN_DATE,
		OPTED_OUT_DATE,
		ADDOPER, 
		CONCURRENCY_ID, 
		OPTION_TYPE_CODE, 
		COMMUNICATION_CHOICE_CODE, 
		PREFERRED_COMMUNICATION_METHOD_CODE, 
		EMAIL_FAX_USE_CODE, 
		ADDRESS_USE_CODE, 
		PREFERRED_ADDRESS_TYPE_CODE
	) 
	VALUES 
	(
		@NextID, 
		@CustomMemberID,
		0, 
		@OrgID, 
		@OrgUnitID, 
		@OptionCode, 
		@OptionSubCode, 
		0, 
		@OptionDescr, 
		@OptedInFlag, 
		case when @OptedInFlag = 'Y' then @DateStampLocal else null end,
		case when @OptedInFlag = 'N' then @DateStampLocal else null end,
		'RealMagnet', 
		0, 
		'INTEREST_AREA', 
		'CUSTOMER_DEFAULT_METHOD', 
		'EMAIL', 
		'PRIMARY', 
		'PRIMARY', 
		'HOME'
	)
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[RealMagnet_InsertTrackingActivity]
(
	@TrackingActivityId bigint,
	@ActivityCode varchar(20),
	@ActivitySubCode varchar(50),
	@CustomMemberId varchar(50),
	@EmailAddress varchar(100),
	@RecipientId bigint,
	@DateStampUTC datetime,
	@UnsubscribeCategory varchar(50),
	@GroupName varchar(50),
	@MessageName varchar(50),
	@LinkUrl varchar(1500),
	@LinkLabel varchar(50),
	@MessageCategory varchar(50)
)
as

declare @OrgID varchar(50) = -- PLEASE SET THE CORRECT ORG ID
declare @OrgUnitID varchar(50) = -- PLEASE SET THE CORRECT ORG UNIT ID
declare @SubSystem varchar(10) = 'CUS'
declare @DateStampUtcText varchar(100) = convert(varchar(100),@DateStampUTC)

if not len(@UnsubscribeCategory) > 0
	set @UnsubscribeCategory = 'None'
if not len(@GroupName) > 0
	set @GroupName = 'None'
if not len(@MessageName) > 0
	set @MessageName = 'None'
if not len(@LinkUrl) > 0
	set @LinkUrl = 'None'
if not len(@LinkLabel) > 0
	set @LinkLabel = 'None'
if not len(@MessageCategory) > 0
	set @MessageCategory = 'None'

declare @ActivityText varchar(1000)
declare @ActivityStatusForText varchar(50) = @ActivitySubCode

if (@ActivityCode = 'Sent' and @ActivitySubCode not in ('Failed', 'Delivered', 'InProgress', 'HardBounce'))
begin
  declare @result nvarchar(max)= left(@ActivitySubCode, 1), 
		  @i int = 2

  while @i <= len(@ActivitySubCode)
  begin
	if ascii(substring(@ActivitySubCode, @i, 1)) between 65 and 90
	  select @result += ' '
	select @result += substring(@ActivitySubCode, @i, 1)
	select @i += 1 
  end

  set @ActivityStatusForText = @result
end
 
select @ActivityText = 
	case
		when @ActivityCode = 'Opened' then 'Opened || ' + @MessageName + ' || ' + @DateStampUtcText
		when @ActivityCode = 'Unsubscribe' then 'Unsubscribed || ' + @MessageName + ' || ' + @DateStampUtcText + ' || ' + @GroupName
		when @ActivityCode = 'Unsubscribed' then 'Unsubscribed || ' + @MessageName + ' || ' + @DateStampUtcText + ' || ' + @GroupName
		when @ActivityCode = 'Subscribed' then 'Subscribed || ' + @MessageName + ' || ' + @DateStampUtcText + ' || ' + @GroupName
		when @ActivityCode = 'LinkClicked' then 'Clicked || ' + @MessageName + ' || '  + @DateStampUtcText  + ' || ' + @LinkLabel + ' || ' + @LinkUrl
		when @ActivityCode = 'Sent' and @ActivitySubCode = 'Failed' then 'Failed || ' + @MessageName + ' || ' + @DateStampUtcText
		when @ActivityCode = 'Sent' and @ActivitySubCode = 'Delivered' then 'Delivered || ' + @MessageName + ' || ' + @DateStampUtcText
		when @ActivityCode = 'Sent' and @ActivitySubCode = 'InProgress' then 'In Progress || ' + @MessageName + ' || ' + @DateStampUtcText
		when @ActivityCode = 'Sent' and @ActivitySubCode = 'HardBounce' then 'Bounced || ' + @MessageName + ' || ' + @DateStampUtcText
		when @ActivityCode = 'Sent' and @ActivitySubCode not in ('Failed', 'Delivered', 'InProgress') then 'Bounced || ' + @MessageName + ' || ' + @ActivityStatusForText
		when @ActivityCode = 'Suppressed' and @ActivitySubCode = 'ManualSuppress' then 'Suppressed || Suppressed (Manually) || ' + @DateStampUtcText
		when @ActivityCode = 'Suppressed' and @ActivitySubCode = 'ListUploadSuppress' then 'Suppressed || Suppressed (List Upload) || ' + @DateStampUtcText
		when @ActivityCode = 'Suppressed' and @ActivitySubCode = 'UserNotFoundSuppress' then 'Suppressed || Suppressed (User Not Found) ' + @DateStampUtcText
		when @ActivityCode = 'Suppressed' and @ActivitySubCode = 'SpamComplaintSuppress' then 'Suppressed || Suppressed (SPAM Complaint) ' + @DateStampUtcText
		when @ActivityCode = 'Suppressed' and @ActivitySubCode = 'CampaignSuppress' then 'Suppressed || Suppressed (Campaign) ' + @DateStampUtcText
		when @ActivityCode = 'Suppressed' and @ActivitySubCode = 'DomainSuppress' then 'Suppressed || Suppressed (Domain) ' + @DateStampUtcText
		when @ActivityCode = 'Suppressed' and @ActivitySubCode = 'SubscriptionSuppress' then 'Suppressed || Suppressed (Manage Subscription) ' + @DateStampUtcText
		when @ActivityCode = 'Suppressed' and @ActivitySubCode = 'BadDomainSuppress' then 'Suppressed || Suppressed (Bad Domain) ' + @DateStampUtcText
		when @ActivityCode = 'Suppressed' and @ActivitySubCode = 'UnsubscribedAcrossAccts' then 'Suppressed || Suppressed (Unsubscribed Across Accounts) ' + @DateStampUtcText
		when @ActivityCode = 'Suppressed' and @ActivitySubCode = 'SuppressedAcrossAccts' then 'Suppressed || Suppressed (Across Accounts) ' + @DateStampUtcText
		when @ActivityCode = 'Suppressed' and @ActivitySubCode = 'OldSuppress' then 'Suppressed || Suppressed (Old) ' + @DateStampUtcText
		else @ActivityCode + ' || ' + @MessageName + ' || ' + @DateStampUtcText
	end

if not exists (select * from dbo.APP_SUBCODE where CODE = 'REALMAGNET' and SUBCODE = @ActivitySubCode and SUBSYSTEM = @SubSystem and TYPE = 'ACTIVITY')
begin
	insert into dbo.APP_SUBCODE (SUBSYSTEM, TYPE, CODE, SUBCODE, SCREEN_RESTRICTION, DESCR, ACTIVE_FLAG, STATUS_CHANGE_DATE, ADDOPER, ADDDATE, CONCURRENCY_ID, AVAILABLE_TO_WEB_FLAG)
	values(@SubSystem,	'ACTIVITY',	'REALMAGNET', @ActivitySubCode,	NULL,	@ActivityStatusForText, 'Y',	GETDATE(),	'ADMIN',	GETDATE(),	0,	'Y')
end

declare @NextID nvarchar(255)
exec GetNextID @pstrTable=N'CUS_ACTIVITY', @pstrColumn=N'CUS_ACTIVITY', @pstrUser=N'admin', @NewID=@NextID output

insert into dbo.CUS_ACTIVITY (CUS_ACTIVITY_ID, MASTER_CUSTOMER_ID, SUB_CUSTOMER_ID, SUBSYSTEM, ORG_ID, ORG_UNIT_ID, ACTIVITY_CODE, ACTIVITY_SUBCODE, ACTIVITY_TEXT, ACTIVITY_DATE, ADDOPER, ADDDATE)
values (@NextID, @CustomMemberId, 0, @SubSystem, @OrgID, @OrgUnitID, 'RealMagnet', @ActivitySubCode, @ActivityText, @DateStampUTC, 'RealMagnet', GETDATE())
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[RealMagnet_GetInterestAreas]
as
declare @SubSystem varchar(10) = 'CUS'

select 
	case when s.SubCode is null then '' else substring(convert(char(3),100+LEN(C.CODE)),2,2) end + C.CODE + coalesce(S.SUBCODE,'') as [ID],
	C.DESCR + case when s.descr is null then '' else ', ' end + coalesce(S.DESCR,'') as [Name]
from 
	APP_CODE C 
	left outer join 
	APP_SUBCODE S 
		on S.CODE=C.CODE and S.[TYPE]=C.[TYPE]
where
        C.[TYPE] ='OPT_IN_INTEREST_AREA' 
	AND C.ACTIVE_FLAG='Y' 
	AND C.SUBSYSTEM = @SubSystem
	AND (S.SUBCODE is null or S.ACTIVE_FLAG='Y')	
	AND (S.SUBCODE is null or S.OPTION_1 like '%EMAIL%')
order by C.Descr, coalesce(S.Descr,'')
GO

create type [dbo].[RealMagnet_NameValuePair] as table
(
	 Name nvarchar(50) NOT NULL PRIMARY KEY
	,Value nvarchar(200) NOT NULL
)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[RealMagnet_InsertProspect]
(
	 @RecipientID bigint
	,@Properties RealMagnet_NameValuePair readonly
)
as

declare @OrgID varchar(50) = -- PLEASE SET THE CORRECT ORG ID
declare @OrgUnitID varchar(50) = -- PLEASE SET THE CORRECT ORG UNIT ID

declare 
	 @CustomMemberID varchar(12) = null 
	,@NextID numeric(12,0) = null
	,@matches int = 0
	,@newline     nvarchar(2)   = char(13) + char(10)
	,@first_name  nvarchar(200) = (select [Value] from @Properties where Name = 'FIRST_NAME')
	,@last_name   nvarchar(200) = (select [Value] from @Properties where Name = 'LAST_NAME')
	,@email_addr  nvarchar(200) = (select [Value] from @Properties where Name = 'EMAIL')
	,@phone       nvarchar(200) = (select [Value] from @Properties where Name = 'PHONE')
	,@fax         nvarchar(200) = (select [Value] from @Properties where Name = 'FAX')
	,@address1    nvarchar(200) = (select [Value] from @Properties where Name = 'ADDRESS1')
	,@address2    nvarchar(200) = (select [Value] from @Properties where Name = 'ADDRESS2')
	,@city        nvarchar(200) = (select [Value] from @Properties where Name = 'CITY')
	,@state       nvarchar(200) = (select [Value] from @Properties where Name = 'STATE')
	,@zip_code    nvarchar(200) = (select [Value] from @Properties where Name = 'ZIP')
	,@company     nvarchar(200) = (select [Value] from @Properties where Name = 'COMPANY')
	,@country     nvarchar(50)  = 'USA'
	,@addr_type   nvarchar(50)  = 'BLANK'
	,@postal_err  nvarchar(50)  = 'INSUFFICIENTINPUTDATA'
	,@search_name nvarchar(200) = null
	,@label_name  nvarchar(200) = null
	,@last_first  nvarchar(200) = null
	,@addr_detail nvarchar(400) = null
	;

select @matches = count(distinct MASTER_CUSTOMER_ID) 
from CUSTOMER 
where PRIMARY_EMAIL_ADDRESS = @email_addr 
--	and (FIRST_NAME = @first_name)
--	and (LAST_NAME = @last_name)
	;
	
if 1 = @matches
	begin
	
	select top 1 @RecipientID, MASTER_CUSTOMER_ID, null
	from CUSTOMER
	where PRIMARY_EMAIL_ADDRESS = @email_addr 
--		and FIRST_NAME = @first_name 
--		and LAST_NAME = @last_name
		;
	return ;

	end
	
if 1 < @matches
	begin
	
	select @RecipientID, null, 'Multiple matches found.'
	return ;

	end
	
exec GetNextID @pstrTable=N'CUSTOMER', @pstrColumn=N'MASTER_CUSTOMER_ID', @pstrUser=N'admin', @NewID=@CustomMemberID output;
if 12 > len(@CustomMemberID)
	set @CustomMemberID = replicate('0',12-len(@CustomMemberID)) + @CustomMemberID ;

exec GetSearchName null, @first_name, null, @last_name, null, null, @search_name output;

exec GetLabelName null, @first_name, null, @last_name, null, null, @label_name output;

set @last_first = case
	when @last_name is null and @first_name is null then null
	when @last_name is null or 0=len(@last_name) then ltrim(rtrim(@first_name))
	when @first_name is null or 0=len(@first_name) then ltrim(rtrim(@last_name))
	else ltrim(rtrim(@last_name)) + ', ' + ltrim(rtrim(@first_name))
	end;

insert into CUSTOMER(
	 MASTER_CUSTOMER_ID
	,SUB_CUSTOMER_ID
	,RECORD_TYPE
	,FIRST_NAME
	,LAST_NAME
	,PRIMARY_EMAIL_ADDRESS
	,SEARCH_NAME
	,LABEL_NAME
	,CUSTOMER_CLASS_CODE
	,CUSTOMER_STATUS_CODE
	,SEGMENT_RULE_CODE
	,SEGMENT_QUALIFIER1
	,SEGMENT_QUALIFIER2
	,ORG_ID
	,ORG_UNIT_ID
	,ADDOPER
	) values (
	 @CustomMemberID
	,0
	,'I'
	,@first_name
	,@last_name
	,@email_addr
	,@search_name
	,@label_name
	,'INDIV'
	,'ACTIVE'
	,'ORG_UNIT'
	,@OrgID
	,@OrgUnitID
	,@OrgID
	,@OrgUnitID
	,'ADMIN'
	);

exec GetNextID @pstrTable=N'CUS_SEGMENT_MEMBER', @pstrColumn=N'CUS_SEGMENT_ID', @pstrUser=N'admin', @NewID=@NextID output;

insert into CUS_SEGMENT_MEMBER(
	 CUS_SEGMENT_ID
	,MASTER_CUSTOMER_ID
	,SUB_CUSTOMER_ID
	,SEGMENT_QUALIFIER1
	,SEGMENT_QUALIFIER2
	,ADDOPER
	,CONCURRENCY_ID
	,ORG_ID
	,ORG_UNIT_ID
	,SEGMENT_LINK_ID1
	,SEGMENT_LINK_ID2
	,SEGMENT_RULE_CODE
	) VALUES (
	 @NextID
	,@CustomMemberID
	,0
	,@OrgID
	,@OrgUnitID
	,'ADMIN'
	,1
	,@OrgID
	,@OrgUnitID
	,'0'
	,'0'
	,'ORG_UNIT'
	);

insert into CUS_COMMUNICATION(
	MASTER_CUSTOMER_ID, 
	SUB_CUSTOMER_ID, 
	PRIMARY_FLAG, 
	ACTIVE_FLAG, 
	FORMATTED_PHONE_ADDRESS, 
	SEARCH_PHONE_ADDRESS, 
	COUNTRY_CODE, 
	COMM_TYPE_CODE
	,COMM_LOCATION_CODE
	,ADDOPER
	,CONCURRENCY_ID
	) VALUES (
	 @CustomMemberID
	,0
	,'Y'
	,'Y'
	,@email_addr
	,@email_addr
	,@country
	,'EMAIL'
	,'WORK'
	,'ADMIN'
	,0
	)
	;
	
if @phone is not null and 0 <> len(@phone)
	begin

	insert into CUS_COMMUNICATION(
		MASTER_CUSTOMER_ID, 
		SUB_CUSTOMER_ID, 
		PRIMARY_FLAG, 
		ACTIVE_FLAG, 
		FORMATTED_PHONE_ADDRESS, 
		SEARCH_PHONE_ADDRESS, 
		COUNTRY_CODE, 
		COMM_TYPE_CODE
		,COMM_LOCATION_CODE
		,ADDOPER
		,CONCURRENCY_ID
		) VALUES (
		 @CustomMemberID
		,0
		,'Y'
		,'Y'
		,@phone
		,@phone
		,@country
		,'PHONE'
		,'WORK'
		,'ADMIN'
		,0
		)
		;
		
	end
	
if @fax is not null and 0 <> len(@fax)
	begin

	insert into CUS_COMMUNICATION(
		MASTER_CUSTOMER_ID, 
		SUB_CUSTOMER_ID, 
		PRIMARY_FLAG, 
		ACTIVE_FLAG, 
		FORMATTED_PHONE_ADDRESS, 
		SEARCH_PHONE_ADDRESS, 
		COUNTRY_CODE, 
		COMM_TYPE_CODE
		,COMM_LOCATION_CODE
		,ADDOPER
		,CONCURRENCY_ID
		) VALUES (
		 @CustomMemberID
		,0
		,'Y'
		,'Y'
		,@fax
		,@fax
		,@country
		,'FAX'
		,'WORK'
		,'ADMIN'
		,0
		)
		;
		
	end
	
if  (@address1 is not null and 0 <> len(@address1))
	or (@address2 is not null and 0 <> len(@address2))
	or (@city is not null and 0 <> len(@city))
	or (@state is not null and 0 <> len(@state))
	or (@zip_code is not null and 0 <> len(@zip_code))
	or (@company is not null and 0 <> len(@company))
	begin
	
	set @addr_type = 'WORK';
	
	set @addr_detail = (
		select l + @newline as [text()] from (select row_number() over (order by n) n, l from (values 
			 (1,@label_name)
			,(2,@company)
			,(3,@address1)
			,(4,@address2)
			,(5,case when @city is null and @state is null and @zip_code is null then '' else dbo.udf_GetFormattedCityStatePostal(@city,coalesce(@state,''),coalesce(@zip_code,''),@country) end)
			) ls(n,l) where l is not null and 0 <> len(l)) ns order by n for xml path('') )
			;
	
	end

if @zip_code is not null and 0 <> len(@zip_code)
	begin

	set @postal_err = 'UNABLETOVALIDATE';
	
	end
	
exec GetNextID @pstrTable=N'CUS_ADDRESS', @pstrColumn=N'CUS_ADDRESS_ID', @pstrUser=N'admin', @NewID=@NextID output;

insert into CUS_ADDRESS(
	 CUS_ADDRESS_ID
	,MASTER_CUSTOMER_ID
	,SUB_CUSTOMER_ID
	,ADDRESS_1
	,ADDRESS_2
	,CITY
	,STATE
	,POSTAL_CODE
	,COUNTRY_CODE
	,COUNTRY_DESCR
	,ADDRESS_STATUS_CODE	
	,POSTAL_ERROR_CODE
	,ADDOPER
	,CONCURRENCY_ID
	) VALUES (
	 @NextID
	,@CustomMemberID
	,0
	,@address1
	,@address2
	,@city
	,@state
	,@zip_code
	,@country
	,'United States'
	,'BAD'
	,@postal_err
	,'ADMIN'
	,0
	);

insert into CUS_ADDRESS_DETAIL(
	 MASTER_CUSTOMER_ID
	,SUB_CUSTOMER_ID
	,CUS_ADDRESS_ID
	,ADDRESS_TYPE_CODE
	,PRIORITY_SEQ
	,LABEL_NAME
	,FORMATTED_DETAIL
	,DIRECTORY_PRIORITY
	,ADDRESS_STATUS_CODE
	,ADDOPER
	,CONCURRENCY_ID
	) values (
	 @CustomMemberID
	,0
	,@NextID
	,@addr_type
	,0
	,@label_name
	,@addr_detail
	,0
	,'BAD'
	,'ADMIN'
	,0
	);

select @RecipientID, @CustomMemberID, null
GO