Counterparties

A summary of the load process for counterparties.

Step 1

Counterparties from Dynamics, SalesLogix and Calypso

Calypso Criteria:

Legal Entity Role = Beneficiary

Dynamics Criteria:

Attributes = statuscode, abi_visibility, abi_substatus, abi_type, abi_salesdevelopment

SalesLogix Criteria:

All Data

View SQL1

View SQL2

View SQL3

View SQL4

View SQL5

View SQL6

View SQL7

Load Calypso counterparties to table [dbo.stage_counterparties]

SELECT
	le.legal_entity_id as cpty_id,
	le.short_name,
	substring(le.long_name, 1, 200) as long_name,
	substring(le.country, 1, 25) as country,
	le.entered_date,
	le.le_status as cpty_status,
	case
		le.classification
		when 1 then 'Financial'
		when 0 then 'Non-Financial'
	end as cpty_type,
	case
		when upper(substring(le.short_name, 1, 5)) in ('START')
		or upper(substring(le.short_name, 1, 4)) in ('ABIUK', 'ABICI', 'ABIMU')
		or (
			upper(substring(le.short_name, 1, 3)) in ('ABIBL', 'ABICF')
			and le.short_name <> 'ABI INVESTMENTS LTD'
		)
		or (
			upper(substring(le.short_name, 1, 8)) in ('ABI')
			and le.short_name <> 'ABI GROUP INVESTMENTS'
		)
		or upper(substring(le.short_name, 1, 10)) in ('KINGSTON') then 'N'
		else 'Y'
	end cpty_ext_yn,
	case
		when lr.role_name is not null then 'Y'
		else 'N'
	end as beneficiary_yn,
	pro.pro_user,
	sd.sales_dealer,
	ref.referral,
	cer.credit_except_rep,
	coalesce(lec.cls, 'N') as cls_yn,
	'calypso' as system_name
FROM
	calypso.legal_entity le
	LEFT JOIN calypso.legal_entity_role lr ON le.legal_entity_id = lr.legal_entity_id
	AND lr.role_name = 'Beneficiary'
	LEFT JOIN (
		SELECT
			distinct legal_entity_id,
			'Y' as cls
		FROM
			le_attribute
		WHERE
			attribute_type = 'CLS'
	) lec ON le.legal_entity_id = lec.legal_entity_id
	LEFT JOIN (
		SELECT
			distinct legal_entity_id,
			rtrim(attribute_value) as pro_user
		FROM
			calypso.le_attribute
		WHERE
			attribute_type = 'PRO'
	) pro ON le.legal_entity_id = pro.legal_entity_id
	LEFT JOIN (
		SELECT
			distinct legal_entity_id,
			rtrim(attribute_value) as sales_dealer
		FROM
			calypso.le_attribute
		WHERE
			attribute_type = 'Sales_Dealer'
	) sd ON le.legal_entity_id = sd.legal_entity_id
	LEFT JOIN (
		SELECT
			distinct legal_entity_id,
			rtrim(attribute_value) as referral
		FROM
			calypso.le_attribute
		WHERE
			attribute_type = 'Referral'
	) ref ON le.legal_entity_id = ref.legal_entity_id
	LEFT JOIN (
		SELECT
			distinct legal_entity_id,
			rtrim(attribute_value) as credit_except_rep
		FROM
			calypso.le_attribute
		WHERE
			attribute_type = 'ABI Credit Exception Report'
	) cer ON le.legal_entity_id = cer.legal_entity_id

Load Dynamics counterparties to table [dbo.stage_crm_counterparties]

 SELECT
	distinct a.accountid as crm_cpty_id,
	a.abi_crossreferenceid as slx_cpty_id,
	a.Accountnumber as client_number,
	a.Name as cpty_name,
	c.value as cpty_status,
	d.value as cpty_sub_status,
	e.value as activity,
	a.abi_primaryrelationshipmanager as primary_acc_manager,
	a.abi_businessunit as business_unit,
	b.value as visibility,
	a.abi_leadsourceidname as lead_source,
	f.value as sales_development,
	cast(a.abi_nominal as numeric(12, 0)) as client_nominal,
	cast(a.abi_credit as numeric(12, 0)) as client_credit,
	cast(a.abi_dailysettlement as numeric(12, 0)) as client_dailysettlement,
	a.abi_tenor as client_tenor,
	a.abi_latestaccount as latest_account,
	a.abi_creditnotes as client_creditnotes,
	cast(a.abi_clientvolumespot as numeric(12, 0)) as annualvolume_spot,
	cast(
		a.abi_clientvolumeforwardoptions as numeric(12, 0)
	) as annualvolume_forward,
	cast(a.abi_clientvolumetotalfx as numeric(12, 0)) as annualvolume_fxtotal,
	cast(a.abi_moneymarkets as numeric(12, 0)) as annualvolume_moneymarkets,
	cast(a.abi_fxtarget as numeric(12, 0)) as fx_target,
	cast(a.abi_mmtarget as numeric(12, 0)) as mm_target,
	a.abi_clientmarginspot as investec_margin_spot,
	a.abi_clientmarginforward as investec_margin_fwd,
	a.abi_clientmarginoptions as investec_margin_option,
	a.abi_primarydealeridname as primarydealer,
	a.abi_numberofemployees as no_of_employees,
	cast(a.abi_balancesheettotal as numeric(12, 0)) as balance_sheet_total,
	cast(a.abi_annualturnover as numeric(12, 0)) as annual_turnover,
	a.abi_bcobsenterprisecategory as bcobs_enterprise_category,
	a.abi_fscseligible as fscs_eligible,
	a.abi_aml as aml_status,
	a.abi_fx,
	a.abi_mm
FROM
	dbo.Account a
	INNER JOIN StringMap c ON a.StatusCode = c.AttributeValue
	AND c.ObjectTypeCode = 1
	AND c.AttributeName = 'statuscode'
	LEFT JOIN StringMap b ON a.abi_Visibility = b.AttributeValue
	AND b.ObjectTypeCode = 1
	AND b.AttributeName = 'abi_visibility'
	LEFT JOIN StringMap d ON a.abi_SubStatus = d.AttributeValue
	AND d.ObjectTypeCode = 1
	AND d.AttributeName = 'abi_substatus'
	LEFT JOIN StringMap e ON a.abi_Type = e.AttributeValue
	AND e.ObjectTypeCode = 1
	AND e.AttributeName = 'abi_type'
	LEFT JOIN StringMap f ON a.abi_SalesDevelopment = f.AttributeValue
	AND f.ObjectTypeCode = 1
	AND f.AttributeName = 'abi_salesdevelopment'

Load Dynamics addresses to table [dbo.stage_crm_counterparty_addresses]

SELECT
	distinct accountid as crm_cpty_id,
	f.value as address_desc,
	address1_line1 as address_line1,
	address1_line2 as address_line2,
	address1_line3 as address_line3,
	address1_city as city,
	coalesce(address1_county, address1_stateorprovince) as county,
	address1_postalcode as post_code,
	address1_country as country
FROM
	dbo.Account a
	INNER JOIN StringMap b ON a.Abi_Visibility = b.AttributeValue
	AND b.ObjectTypeCode = 1
	AND b.AttributeName = 'abi_visibility'
	INNER JOIN StringMap c ON a.StatusCode = c.AttributeValue
	AND c.ObjectTypeCode = 1
	AND c.AttributeName = 'statuscode'
	INNER JOIN StringMap f ON a.Address1_AddressTypeCode = f.AttributeValue
	AND f.ObjectTypeCode = 1
	AND f.AttributeName = 'address1_addresstypecode'
UNION
SELECT
	distinct accountid as crm_cpty_id,
	f.value as address_desc,
	address2_line1 as address_line1,
	address2_line2 as address_line2,
	address2_line3 as address_line3,
	address2_city as city,
	coalesce(address2_county, address2_stateorprovince) as county,
	address2_postalcode as post_code,
	address2_country as country
FROM
	dbo.Account a
	INNER JOIN StringMap b ON a.Abi_Visibility = b.AttributeValue
	AND b.ObjectTypeCode = 1
	AND b.AttributeName = 'abi_visibility'
	INNER JOIN StringMap c ON a.StatusCode = c.AttributeValue
	AND c.ObjectTypeCode = 1
	AND c.AttributeName = 'statuscode'
	INNER JOIN StringMap f ON a.Address2_AddressTypeCode = f.AttributeValue
	AND f.ObjectTypeCode = 1
	AND f.AttributeName = 'address2_addresstypecode'

Load Dynamics account activity to table [dbo.stage_crm_counterparty_activity]

SELECT
	distinct a.accountid,
	a.Abi_CrossReferenceId,
	ap.OwnerIdName,
	ap.CreatedByName,
	ap.OwnerIdName as username,
	convert(
		datetime,
		convert(varchar(10), ap.CreatedOn, 103),
		103
	) as createdate,
	activityname,
	convert(
		datetime,
		convert(varchar(10), ap.ScheduledEnd, 103),
		103
	) as activitydate,
	sc.activitystatus,
	ap.subject as subject_text
FROM
	dbo.ActivityPointer ap
	INNER JOIN dbo.FilteredAccount a ON ap.RegardingObjectId = a.accountid
	LEFT JOIN (
		SELECT
			attributevalue,
			value as activityname
		FROM
			dbo.StringMap
		WHERE
			AttributeName = 'activitytypecode'
			AND ObjectTypeCode = 4200
	) ac ON ap.activityTypecode = ac.attributevalue
	LEFT JOIN (
		SELECT
			attributevalue,
			value as activitystatus
		FROM
			dbo.StringMap
		WHERE
			AttributeName = 'statecode'
			AND ObjectTypeCode = 4200
	) sc ON ap.statecode = sc.attributevalue
WHERE
	DeletionStateCode = 0

Load Dynamics contact activity to table [dbo.stage_crm_counterparty_activity]

SELECT
	distinct a.accountid,
	a.Abi_CrossReferenceId,
	ap.OwnerIdName,
	ap.CreatedByName,
	ap.OwnerIdName as username,
	convert(
		datetime,
		convert(varchar(10), ap.CreatedOn, 103),
		103
	) as createdate,
	activityname,
	convert(
		datetime,
		convert(varchar(10), ap.ScheduledEnd, 103),
		103
	) as activitydate,
	sc.activitystatus,
	ap.subject as subject_text
FROM
	dbo.ActivityPointer ap
	INNER JOIN dbo.FilteredAccount a ON ap.RegardingObjectId = a.accountid
	LEFT JOIN (
		SELECT
			attributevalue,
			value as activityname
		FROM
			dbo.StringMap
		WHERE
			AttributeName = 'activitytypecode'
			AND ObjectTypeCode = 4200
	) ac ON ap.activityTypecode = ac.attributevalue
	LEFT JOIN (
		SELECT
			attributevalue,
			value as activitystatus
		FROM
			dbo.StringMap
		WHERE
			AttributeName = 'statecode'
			AND ObjectTypeCode = 4200
	) sc ON ap.statecode = sc.attributevalue
WHERE
	DeletionStateCode = 0
UNION
SELECT
	distinct a.accountid,
	a.Abi_CrossReferenceId,
	ap.OwnerIdName,
	ap.CreatedByName,
	ap.OwnerIdName as username,
	convert(
		datetime,
		convert(varchar(10), ap.CreatedOn, 103),
		103
	) as createdate,
	activityname,
	convert(
		datetime,
		convert(varchar(10), ap.ScheduledEnd, 103),
		103
	) as activitydate,
	sc.activitystatus,
	ap.subject as subject_text
FROM
	dbo.ActivityPointer ap
	INNER JOIN dbo.ContactBase c ON ap.RegardingObjectId = c.contactid
	INNER JOIN dbo.FilteredAccount a ON c.accountid = a.accountid
	LEFT JOIN (
		SELECT
			attributevalue,
			value as activityname
		FROM
			dbo.StringMap
		WHERE
			AttributeName = 'activitytypecode'
			AND ObjectTypeCode = 4200
	) ac ON ap.activityTypecode = ac.attributevalue
	LEFT JOIN (
		SELECT
			attributevalue,
			value as activitystatus
		FROM
			dbo.StringMap
		WHERE
			AttributeName = 'statecode'
			AND ObjectTypeCode = 4200
	) sc ON ap.statecode = sc.attributevalue

Load Saleslogix counterparties to table [dbo.slx_counterparties]

SELECT
	distinct coalesce(acc.ACCOUNTID, client_no) as slx_cpty_id,
	acc.client_no as client_number,
	acc.ACCOUNT as cpty_name,
	case
		when acc.LOCATION is not null then acc.ACCOUNT + ' ' + acc.LOCATION
		else acc.ACCOUNT
	end as cpty_desc,
	acc.status as cpty_status,
	ae.ABI_SUB_STATUS as cpty_sub_status,
	acc.ABI_ACTIVITY as activity,
	ae.ABI_CLIENT_SUB_GROUP as cpty_sub_group,
	bu.primary_acc_manager,
	bu.BUSINESSUNIT as business_unit,
	s.SECCODEDESC as visibility,
	l.DESCRIPTION as lead_source,
	ae.abi_referral as referral_source,
	ae.abi_referral_code as referral_code,
	convert(
		datetime,
		convert(varchar(10), k.meetingbookeddate, 111)
	) as meeting_booked_date,
	convert(
		datetime,
		convert(varchar(10), k.firstmeetingdate, 111)
	) as first_meeting_date,
	convert(
		datetime,
		convert(varchar(10), k.applicationformreceiveddate, 111)
	) as application_form_received_date,
	convert(
		datetime,
		convert(varchar(10), k.welcomepacksentdate, 111)
	) as welcome_pack_sent_date,
	convert(
		datetime,
		convert(varchar(10), k.welcomepackreceiveddate, 111)
	) as welcome_pack_received_date,
	convert(
		datetime,
		convert(varchar(10), k.firsttradedate, 111)
	) as first_trade_date,
	convert(
		datetime,
		convert(varchar(10), k.fifthtradedate, 111)
	) as fifth_trade_date,
	k.salesdevelopment,
	dt.client_nominal,
	dt.client_credit,
	dt.client_dailysettlement,
	dt.client_tenor,
	dt.latest_account,
	dt.client_creditnotes,
	dt.annualvolume_spot,
	dt.annualvolume_forward,
	dt.annualvolume_moneymarkets,
	dt.money_markets as fx_target,
	dt.ABI_SPOT,
	ABI_FWD,
	ABI_OPTION,
	COMPETITOR_SPOT,
	COMPETITORFWD,
	COMPETITOROPTION,
	dt.primarycurrencies,
	dt.currencydirection,
	dt.prioritycall,
	dt.competitor,
	d.dealer_name as primarydealer,
	dt.servicelevel,
	b.DATE_RECEIVED as app_form_rcvd_date,
	b.NO_OF_EMPLOYEES,
	b.BALANCE_SHEET_TOTAL,
	b.ANNUAL_TURNOVER,
	b.BCOBS_ENTERPRISE_CATEGORY,
	b.FSCS_ELIGBILE as FSCS_ELIGIBLE,
	am.STATUS as aml_status
FROM
	sysdba.account acc
	LEFT JOIN sysdba.ABI_DEALING_TRANSACTIO dt ON acc.ACCOUNTID = dt.ACCOUNTID
	LEFT JOIN sysdba.QG_ACC_EXT ae ON acc.ACCOUNTID = ae.ACCOUNTID
	LEFT JOIN sysdba.SECCODE s ON acc.SECCODEID = s.SECCODEID
	LEFT JOIN sysdba.LEADSOURCE l ON acc.LEADSOURCEID = l.LEADSOURCEID
	LEFT JOIN sysdba.ABI_BCOBSFSCS b ON acc.ACCOUNTID = b.ACCOUNTID
	LEFT JOIN sysdba.ABI_KPI k ON acc.ACCOUNTID = k.ACCOUNTID
	LEFT JOIN sysdba.QG_AML_INFO am ON acc.ACCOUNTID = am.ACCOUNTID
	LEFT JOIN (
		SELECT
			ACCOUNTID,
			DISPLAYNAME as primary_acc_manager,
			case
				when len(rtrim(BUSINESSUNIT)) = 0 then null
				else BUSINESSUNIT
			end as BUSINESSUNIT
		FROM
			sysdba.ICM_EMP_RELATIONSHIP
		WHERE
			ACCT_MANAGER_ROLE = 'Primary Account Manager'
			AND EMPSTATUS = 'Active'
	) bu ON acc.ACCOUNTID = bu.ACCOUNTID
	LEFT JOIN (
		SELECT
			distinct FK_ACCOUNTID,
			l.FIRSTNAME + ' ' + l.LASTNAME as dealer_name
		FROM
			sysdba.ABI_EMPLOYEE_MAPPING em
			INNER JOIN sysdba.ABI_LDAP l ON em.FK_ABI_LDAPID = l.ABI_LDAPID
		WHERE
			ACCT_MANAGER_ROLE = 'Dealer'
	) d ON acc.ACCOUNTID = d.FK_ACCOUNTID

Load Saleslogix counterparty system ids to table [dbo.slx_counterparty_system_ids]

SELECT
	distinct acc.ACCOUNTID as slx_cpty_id,
	calypso_cpty_id,
	loaniq_cpty_id,
	equation_cpty_id,
	arena_cpty_id
FROM
	sysdba.account acc
	LEFT JOIN (
		SELECT
			A1.ACCOUNTID,
			rtrim(A2.CSCOUNTERPARTYID) as calypso_cpty_id
		FROM
			sysdba.QG_ACCINTTOCORESYSIDS A2
			INNER JOIN sysdba.ACCOUNT A1 ON A2.ACCOUNTID = A1.ACCOUNTID
			INNER JOIN sysdba.QG_CORESYSTEM A3 ON A2.CORESYSTEMID = A3.QG_CORESYSTEMID
		WHERE
			A3.SYSTEMNAME = 'Calypso ID'
	) calypso ON acc.ACCOUNTID = calypso.ACCOUNTID
	LEFT JOIN (
		SELECT
			A1.ACCOUNTID,
			rtrim(A2.CSCOUNTERPARTYID) as loaniq_cpty_id
		FROM
			sysdba.QG_ACCINTTOCORESYSIDS A2
			INNER JOIN sysdba.ACCOUNT A1 ON A2.ACCOUNTID = A1.ACCOUNTID
			INNER JOIN sysdba.QG_CORESYSTEM A3 ON A2.CORESYSTEMID = A3.QG_CORESYSTEMID
		WHERE
			len(rtrim(A2.CSCOUNTERPARTYID)) <= 8
			AND A3.SYSTEMNAME = 'LoanIQ'
	) loaniq ON acc.ACCOUNTID = loaniq.ACCOUNTID
	LEFT JOIN (
		SELECT
			A1.ACCOUNTID,
			rtrim(A2.CSCOUNTERPARTYID) as equation_cpty_id
		FROM
			sysdba.QG_ACCINTTOCORESYSIDS A2
			INNER JOIN sysdba.ACCOUNT A1 ON A2.ACCOUNTID = A1.ACCOUNTID
			INNER JOIN sysdba.QG_CORESYSTEM A3 ON A2.CORESYSTEMID = A3.QG_CORESYSTEMID
		WHERE
			A3.SYSTEMNAME = 'Equation'
	) equation ON acc.ACCOUNTID = equation.ACCOUNTID
	LEFT JOIN (
		SELECT
			A1.ACCOUNTID,
			rtrim(A2.CSCOUNTERPARTYID) as arena_cpty_id
		FROM
			sysdba.QG_ACCINTTOCORESYSIDS A2
			INNER JOIN sysdba.ACCOUNT A1 ON A2.ACCOUNTID = A1.ACCOUNTID
			INNER JOIN sysdba.QG_CORESYSTEM A3 ON A2.CORESYSTEMID = A3.QG_CORESYSTEMID
		WHERE
			A3.SYSTEMNAME = 'Arena SN'
	) arena ON acc.ACCOUNTID = arena.ACCOUNTID
WHERE
	coalesce(len(calypso_cpty_id), 0) + coalesce(len(loaniq_cpty_id), 0) + coalesce(len(equation_cpty_id), 0) + coalesce(len(arena_cpty_id), 0) > 0

Step 2

Load counterparties with only one primary dealer.

View SQL

Load Dynamics and SalesLogix counterparties to table [dbo.dw_crm_counterparties]

SELECT
	distinct sc.crm_cpty_id,
	sc.slx_cpty_id,
	sc.client_number,
	sc.cpty_name,
	slx.cpty_desc,
	sc.cpty_status,
	sc.cpty_sub_status,
	sc.activity,
	slx.cpty_sub_group,
	sc.primary_acc_manager,
	sc.business_unit,
	sc.visibility,
	sc.lead_source,
	slx.referral_source,
	slx.referral_code,
	slx.meeting_booked_date,
	slx.first_meeting_date,
	slx.application_form_received_date,
	slx.welcome_pack_sent_date,
	slx.welcome_pack_received_date,
	sc.sales_development,
	slx.client_nominal,
	slx.client_credit,
	slx.client_dailysettlement,
	slx.client_tenor,
	sc.latest_account,
	cast(sc.client_creditnotes as varchar(1000)) as client_creditnotes,
	sc.annualvolume_spot,
	sc.annualvolume_forward,
	sc.annualvolume_moneymarkets,
	replace(sc.fx_target, ',', '') as fx_target,
	replace(sc.mm_target, ',', '') as mm_target,
	sc.abi_margin_spot,
	sc.abi_margin_fwd,
	sc.abi_margin_option,
	slx.competitor_margin_spot,
	slx.competitor_margin_fwd,
	slx.competitor_margin_option,
	slx.primarycurrencies,
	slx.currencydirection,
	slx.prioritycall,
	slx.competitor,
	dbo.fnc_get_crm_dealers(sc.crm_cpty_id) as primarydealer,
	slx.servicelevel,
	slx.app_form_rcv_date,
	sc.no_of_employees,
	sc.balance_sheet_total,
	sc.annual_turnover,
	sc.bcobs_enterprise_category,
	sc.fscs_eligible,
	sc.aml_status,
	sc.lead_classification_fx,
	sc.lead_classification_mm
FROM
	dbo.stage_crm_counterparties sc
	LEFT JOIN dbo.stage_slx_counterparties slx ON coalesce(cast(sc.client_number as char), sc.slx_cpty_id) = coalesce(cast(slx.client_number as char), slx.crm_cpty_id)
WHERE
	sc.crm_cpty_id not in (
		SELECT
			distinct sc.crm_cpty_id
		FROM
			dbo.stage_crm_counterparties sc
			LEFT JOIN dbo.stage_slx_counterparties slx ON coalesce(cast(sc.client_number as char), sc.slx_cpty_id) = coalesce(cast(slx.client_number as char), slx.crm_cpty_id)
		group by
			sc.crm_cpty_id
		having
			count(*) > 1
	)

Step 3

Load Dynamics counterparty systems ids with no equivalent record in SalesLogix and SalesLogix counterparty systems ids with no equivalent record in Dynamics .

View SQL

Load counterparty system ids to table [dbo.dw_crm_counterparty_system_ids]

TRUNCATE TABLE dbo.dw_crm_counterparty_system_ids

--load Dynamics accounts not in SalesLogix
INSERT INTO dbo.dw_crm_counterparty_system_ids
(
crm_cpty_id,
calypso_cpty_id,
loaniq_cpty_id,
equation_cpty_id,
arena_cpty_id
)
SELECT sc.crm_cpty_id,
	case when isnumeric(si.calypso_cpty_id)=1 then cast(si.calypso_cpty_id as numeric) end as calypso_cpty_id,
	loaniq_cpty_id,
	case when isnumeric(si.equation_cpty_id)=1 then cast(si.equation_cpty_id as numeric) end as equation_cpty_id,
	si.arena_cpty_id
FROM dbo.stage_crm_counterparties sc
INNER JOIN dbo.stage_slx_counterparties slx ON sc.client_number=slx.client_number
INNER JOIN dbo.stage_slx_counterparty_system_ids si ON slx.crm_cpty_id=si.crm_cpty_id
WHERE sc.client_number is not null
AND slx_cpty_id not in (SELECT crm_cpty_id
						FROM dbo.stage_slx_counterparty_system_ids)

UNION

--load SalesLogix accounts not in Dynamics
INSERT INTO
	dbo.dw_crm_counterparty_system_ids (
		crm_cpty_id,
		calypso_cpty_id,
		loaniq_cpty_id,
		equation_cpty_id,
		arena_cpty_id
	)
SELECT
	c.crm_cpty_id,
	case
		when isnumeric(si.calypso_cpty_id) = 1 then cast(si.calypso_cpty_id as numeric)
	end as calypso_cpty_id,
	loaniq_cpty_id,
	case
		when isnumeric(si.equation_cpty_id) = 1 then cast(si.equation_cpty_id as numeric)
	end as equation_cpty_id,
	si.arena_cpty_id
FROM
	dbo.stage_slx_counterparty_system_ids si
	INNER JOIN dbo.stage_slx_counterparties sc ON si.crm_cpty_id = sc.crm_cpty_id
	LEFT JOIN dbo.stage_crm_counterparties c ON sc.crm_cpty_id = c.slx_cpty_id
WHERE
	c.crm_cpty_id is not null
	AND c.crm_cpty_id not in (
		SELECT
			distinct sc.crm_cpty_id
		FROM
			dbo.stage_crm_counterparties sc
			LEFT JOIN dbo.stage_slx_counterparties slx ON coalesce(cast(sc.client_number as char), sc.slx_cpty_id) = coalesce(cast(slx.client_number as char), slx.crm_cpty_id)
		group by
			sc.crm_cpty_id
		having
			count(*) > 1
	)

Step 4

Load counterparty addresses.

View SQL

Load counterparty addresses to table [dbo.dw_crm_counterparty_addresses]

TRUNCATE TABLE dbo.dw_crm_counterparty_addresses
INSERT INTO
	dbo.dw_crm_counterparty_addresses (
		crm_cpty_id,
		address_desc,
		address_line_1,
		address_line_2,
		city,
		county,
		post_code,
		country,
		primary_addr_yn,
		mailing_addr_yn
	)
SELECT
	distinct crm_cpty_id,
	address_desc,
	address_line_1,
	address_line_2,
	city,
	county,
	post_code,
	country,
	null as primary_addr_yn,
	null as mailing_addr_yn
FROM
	dbo.stage_crm_counterparty_addresses

Step 5

Load Calypso counterparties.

View SQL

Load Calypso counterparties to table [dbo.dw_counterparties]

TRUNCATE TABLE dbo.dw_counterparties
INSERT INTO
	dbo.dw_counterparties (
		cpty_id,
		short_name,
		long_name,
		country,
		entered_date,
		cpty_status,
		cpty_type,
		cpty_ext_yn,
		beneficiary_yn,
		sales_person_id,
		sales_dealer,
		referral,
		credit_except_rep,
		cls_yn,
		system_id
	)

SELECT
	distinct cpty_id,
	case
		when len(rtrim(short_name)) = 0 then null
		else short_name
	end as short_name,
	case
		when len(rtrim(long_name)) = 0 then null
		else long_name
	end as long_name,
	case
		when len(rtrim(country)) = 0 then null
		else country
	end as country,
	entered_date,
	cpty_status,
	cpty_type,
	cpty_ext_yn,
	beneficiary_yn,
	sales_person_id,
	rtrim(dbo.fnc_get_sales_dealers(cpty_id)) as sales_dealer,
	rtrim(referral) as referral,
	rtrim(credit_except_rep) as credit_except_rep,
	cls_yn,
	s.system_id
FROM
	dbo.stage_counterparties c
	LEFT JOIN dbo.dw_control_source_systems s ON c.system_name = s.system_name