SELECT
ROWNUM AS “No.”,
“Relationship ID”,
mbr_net_worth,
“LEGAL DOMICILE”,
prim_tax_residence,
“Active Rel Head date onboarded”,
became_actv_rel_head_dt,
reln_status,
managed_region,
“GCI Client”,
cross_rgn_open_acct_exists,
first_ac_open_dt,
“Family Office Applicability”,
“Family Office Type”,
“Family Office Status”,
“Lead Banker”,
“Lead Banker GEID”,
“Lead Banker Region”,
“Lead Banker GMM”,
“Lead GMM GEID”,
nmlist.”Host Banker” AS “NAM Host Banker”,
nmlist.”Host Banker GEID” AS “NAM Host Banker GEID”,
nmlist.”Host GMM” AS “NAM Host GMM”,
nmlist.”Host GMM GEID” AS “NAM Host GMM GEID”,
ltlist.”Host Banker” AS “LATAM Host Banker”,
ltlist.”Host Banker GEID” AS “LATAM Host Banker GEID”,
ltlist.”Host GMM” AS “LATAM Host GMM”,
ltlist.”Host GMM GEID” AS “LATAM Host GMM GEID”,
emlist.”Host Banker” AS “EMEA Host Banker”,
emlist.”Host Banker GEID” AS “EMEA Host Banker GEID”,
emlist.”Host GMM” AS “EMEA Host GMM”,
emlist.”Host GMM GEID” AS “EMEA Host GMM GEID”,
aplist.”Host Banker” AS “APAC Host Banker”,
aplist.”Host Banker GEID” AS “APAC Host Banker GEID”,
aplist.”Host GMM” AS “APAC Host GMM”,
aplist.”Host GMM GEID” AS “APAC Host GMM GEID”
FROM
(
SELECT
reln_nbr AS “Relationship ID”,
CASE
WHEN m1.mbr_entity_type_cd <> ‘I’ THEN m1.mbr_entity_extn_legal_nm
ELSE m1.mbr_nm_prefix
|| ‘ ‘
|| m1.mbr_fst_nm
|| ‘ ‘
|| m1.mbr_mid_nm
|| ‘ ‘
|| m1.mbr_last_nm
END
AS “Relationship Head Name”,
m1.mbr_net_worth,
(
SELECT
cwrk_last_nm || ‘,’ || cwrk_fst_nm
FROM
ods.cwrk
WHERE
cwrk_id = r.reln_lead_bnkr_id
) AS “Lead Banker”,
r.reln_lead_bnkr_id AS “Lead Banker GEID”,
r.expn_mkt_rgn_cd AS “Lead Banker Region”,
gl.cwrk_nm AS “Lead Banker GMM”,
gl.supr_id AS “Lead GMM GEID”,
DECODE(
m1.mbr_entity_type_cd,
‘I’,
m1.mbr_dmcl_ctry_cd,
m1.prin_plac_of_bus
) AS “LEGAL DOMICILE”,
m1.mbr_perm_res_ctry_cd AS prim_tax_residence,
(
SELECT
reln_status_desc
FROM
ods.reln_status rs
WHERE
rs.reln_status_cd = r.reln_status_cd
) AS reln_status,
CASE
WHEN
‘US’ = (
SELECT
prm_cd
FROM
etl.trf_def_prm
WHERE
prm_nm = ‘RGN’
)
AND
r.reln_sect = ‘LFG’
THEN ‘LFG’
WHEN
‘US’ = (
SELECT
prm_cd
FROM
etl.trf_def_prm
WHERE
prm_nm = ‘RGN’
)
AND
r.expn_mkt_rgn_cd = ‘LA’
THEN ‘LATAM’
WHEN
‘US’ = (
SELECT
prm_cd
FROM
etl.trf_def_prm
WHERE
prm_nm = ‘RGN’
)
AND
r.expn_mkt_rgn_cd <> ‘LA’
THEN ‘NAM’
WHEN ‘EM’ = (
SELECT
prm_cd
FROM
etl.trf_def_prm
WHERE
prm_nm = ‘RGN’
)THEN ‘EMEA’
WHEN ‘AP’ = (
SELECT
prm_cd
FROM
etl.trf_def_prm
WHERE
prm_nm = ‘RGN’
)THEN ‘APAC’
ELSE ‘Other’
END
managed_region,
(
SELECT
DECODE(
COUNT(1),
0,
‘N’,
‘Y’
)
FROM
ods.reln_attr_detl rad
WHERE
rad.reln_key = r.reln_key
AND
rad.fld_cd = 1501
AND
rad.lov_cd = 111
AND
EXISTS (
SELECT
‘1’
FROM
cod.reln_cvrg_role rcr
WHERE
rcr.reln_key = r.reln_key
AND
role_cd IN (
SELECT
prm_val
FROM
ods.os_config
WHERE
prm_cd = ‘CR#1286_CVRG_ROLE_OS_OS’
)
AND
auth_ind = ‘A’
UNION
SELECT
‘1’
FROM
ods.eg_cvrg_role ecr,
ods.eg
WHERE
ecr.entl_key = eg.entl_key
AND
eg.reln_key = r.reln_key
AND
role_cd IN (
SELECT
prm_val
FROM
ods.os_config
WHERE
prm_cd = ‘CR#1286_CVRG_ROLE_OS_OS’
)
AND
ecr.auth_ind = ‘A’
)
AND
EXISTS (
SELECT
‘1’
FROM
ods.os_config
WHERE
prm_cd = ‘CR#1286_OS_OS’
AND
prm_val = ‘Y’
)
AND (
r.lb_ctry_cd NOT IN (
SELECT
nvl(
prm_val,
‘$$$’
)
FROM
ods.os_config
WHERE
prm_cd = ‘CR#1286_OS_OS_SWISS_CONFIG’
)
OR (
r.lb_ctry_cd IN (
SELECT
nvl(
prm_val,
‘$$$’
)
FROM
ods.os_config
WHERE
prm_cd = ‘CR#1286_OS_OS_SWISS_CONFIG’
)
AND
nvl(
r.secrcy_wv_cd,
‘N’
) = ‘Y’
)
)
) AS “GCI Client”,
CASE
WHEN EXISTS (
SELECT
1
FROM
ods.acct_portfolio ap
WHERE
acct_reln_key = reln_key
AND
acct_status_cd = ’00’
AND
1 =
CASE
WHEN r.curr_ods_ind = ‘N’ THEN 1
WHEN ap.src_sys_cd NOT IN (
SELECT
prm_cd
FROM
etl.trf_def_prm
WHERE
prm_nm = ‘RGN’
) THEN 1
ELSE 0
END
) THEN ‘Y’
ELSE ‘N’
END
cross_rgn_open_acct_exists,
(
SELECT
DECODE(
radqm.rsp_type,
‘DropDown’,
(
SELECT
ralm.lov_desc
FROM
cod.reln_addn_lov_mstr ralm
WHERE
ralm.fld_cd = rad.fld_cd
AND
ralm.lov_cd = rad.lov_cd
),
rad.fld_val_txt
)
FROM
cod.reln_addn_detl_qst_mstr radqm,
ods.reln_attr_detl rad
WHERE
r.reln_key = rad.reln_key
AND
rad.fld_cd = radqm.fld_cd
AND
radqm.attr_type = ‘Family Office’
AND (
rad.fld_val_txt IS NOT NULL
OR
rad.fld_val_dt IS NOT NULL
OR
rad.lov_cd IS NOT NULL
) AND
ROWNUM = 1
AND
radqm.attr_char = ‘Family Office Applicability’
) AS “Family Office Applicability”,
(
SELECT
DECODE(
radqm.rsp_type,
‘DropDown’,
(
SELECT
ralm.lov_desc
FROM
cod.reln_addn_lov_mstr ralm
WHERE
ralm.fld_cd = rad.fld_cd
AND
ralm.lov_cd = rad.lov_cd
),
rad.fld_val_txt
)
FROM
cod.reln_addn_detl_qst_mstr radqm,
ods.reln_attr_detl rad
WHERE
r.reln_key = rad.reln_key
AND
rad.fld_cd = radqm.fld_cd
AND
radqm.attr_type = ‘Family Office’
AND (
rad.fld_val_txt IS NOT NULL
OR
rad.fld_val_dt IS NOT NULL
OR
rad.lov_cd IS NOT NULL
) AND
ROWNUM = 1
AND
radqm.attr_char = ‘Family Office Type’
) AS “Family Office Type”,
(
SELECT
DECODE(
radqm.rsp_type,
‘DropDown’,
(
SELECT
ralm.lov_desc
FROM
cod.reln_addn_lov_mstr ralm
WHERE
ralm.fld_cd = rad.fld_cd
AND
ralm.lov_cd = rad.lov_cd
),
rad.fld_val_txt
)
FROM
cod.reln_addn_detl_qst_mstr radqm,
ods.reln_attr_detl rad
WHERE
r.reln_key = rad.reln_key
AND
rad.fld_cd = radqm.fld_cd
AND
radqm.attr_type = ‘Family Office’
AND (
rad.fld_val_txt IS NOT NULL
OR
rad.fld_val_dt IS NOT NULL
OR
rad.lov_cd IS NOT NULL
) AND
ROWNUM = 1
AND
radqm.attr_char = ‘Family Office Status’
) AS “Family Office Status”,
reln_key,
r.reln_since_dt “Active Rel Head date onboarded”,
(
SELECT
MIN(acct_open_dt)
FROM
ods.acct_portfolio
WHERE
acct_reln_key = r.reln_key
) AS first_ac_open_dt,
became_actv_rel_head_dt
FROM
ods.reln r,
mbr_chan_type mct,
ods.mbr m1,
(
SELECT
*
FROM
(
WITH supid (
cwrk_id,
cwrk_nm,
supr_id,
lvl,
root_id,
path,
cwrk_job_title
) AS (
— Anchor member.
SELECT
cwrk_id,
cwrk_last_nm || ‘,’ || cwrk_fst_nm AS cwrk_nm,
nvl(
ovrd_supr_id,
supr_id
) AS supr_id,
1 AS lvl,
cwrk_id AS root_id,
cwrk_id AS path,
cwrk_job_title
FROM
ods.cwrk
WHERE
cwrk_actv_ind = ‘Y’
AND
EXISTS (
SELECT
1
FROM
ods.reln
WHERE
reln_lead_bnkr_id = cwrk_id
)
UNION ALL
— Recursive member.
SELECT
c.cwrk_id,
c.cwrk_last_nm
|| ‘,’
|| c.cwrk_fst_nm AS cwrk_nm,
nvl(
c.ovrd_supr_id,
c.supr_id
) AS supr_id,
lvl + 1,
c1.root_id,
c1.path
|| ‘-‘
|| c.cwrk_id AS path,
c.cwrk_job_title
FROM
ods.cwrk c,
supid c1
WHERE
c.cwrk_id = c1.supr_id
AND
c.cwrk_actv_ind = ‘Y’
)
SEARCH DEPTH FIRST BY cwrk_id SET order1
CYCLE cwrk_id SET cycle TO 1 DEFAULT 0
SELECT
cwrk_id,
supr_id,
cwrk_nm
FROM
(
SELECT
root_id AS cwrk_id,
cwrk_id AS supr_id,
cwrk_nm,
lvl,
MIN(
lvl
) OVER(PARTITION BY
root_id
) AS minlvl
FROM
supid
WHERE
root_id <> cwrk_id
AND
upper(cwrk_job_title) LIKE ‘%GMM%’
)
WHERE
lvl = minlvl
)
) gl
WHERE
r.reln_default_ind = ‘N’
AND
r.curr_ods_ind = ‘Y’
AND
m1.mbr_key = r.reln_head_mbr_key
AND
‘Y’ =
CASE
WHEN ‘Y’ = (
SELECT
DECODE(
COUNT(1),
0,
‘N’,
‘Y’
)
FROM
ods.reln_attr_detl rad