View field mappings:

• vw_RIA

 

 

 

View relation diagram:

 

 

Use case/sample query:

1. Select all HHs with Reps and RIA:

    SELECT hh.pkclient as HouseholdId,
        hhp.hh_pers_entityname AS HHName,
        rep.repno AS RepNo,
        prep.rep_pers_entityname AS RepFullName,
        pbd.bd_pers_entityname AS BrokerDealerName,
        ria.srianame
    FROM firm.vw_household hh
    JOIN firm.vw_representative rep ON hh.fkrep = rep.pkrep AND hh.fkalclient = rep.fkalclient
    LEFT JOIN firm.vw_personal_household hhp ON hh.fkpersonal = hhp.hh_fkpersonal AND hh.fkalclient = hhp.hh_fkalclient
    LEFT JOIN firm.vw_personal_representative prep ON rep.fkpersonal = prep.rep_fkpersonal AND rep.pkrep = prep.rep_pkrep 
        AND rep.fkalclient = prep.rep_fkalclient
    LEFT JOIN firm.vw_brokerdealer bd ON rep.fkbrokerdealer = bd.pkbrokerdealer AND rep.fkalclient = bd.fkalclient
    LEFT JOIN firm.vw_personal_brokerdealer pbd ON bd.fkpersonal = pbd.bd_fkpersonal AND bd.pkbrokerdealer = pbd.bd_pkbrokerdealer 
        AND bd.fkalclient = pbd.bd_fkalclient
    LEFT JOIN firm.vw_ria ria ON rep.fkria = ria.pkria and rep.fkalclient = ria.fkalclient
    WHERE hh.fkalclient = {fkalclient};

 

 

vw_RIA
Column Name	Format	Description	Example
fkalclient	integer	Firm ID	1769
pkria	integer	Orion RIA Id	2
srianame	varchar(60)	RIA Name	Treeline
sriadesc	varchar(300)	RIA Description	Brecek & Young Advisors, Inc.
fkpersonal	integer	Orion Personal Id (links to vw_personal.pkpersonal)	75
ibillingstyle	integer	Billing Style Id	0
riacreateddate	date	Date RIA added to Orion	2021-12-03
riacreatedby	varchar(50)	Who, or what process, added Rep to Orion	user.login
editeddate	date	Date Rep record was last edited in Orion	2021-12-03
editedby	varchar(50)	Who, or what process, edited Rep in Orion	someone@someplace.com
issolicitor	boolean	Is Solicitor	true/false
fkpayee	integer	Orion Payee Id	34
fiscalstartdate	varchar(10)	Start of Fiscal calendar	1/1
fiscalenddate	varchar(10)	End of Fiscal calendar	12/31
isdefault	boolean	Is Default RIA	true/false
isactive	boolean	Is RIA Active	true/false
fkriaglobal	integer	Orion RIA Id applied globally	8
fkbrokerdealer	integer	Orion BrokerDealer Id	46
createddate	timestamp	DateTime that record was created and/or updated in Redshift	2021-07-01 10:38:31