| SQL |
SELECT distinct 'BROSTF' as conType, bro.broker_name as companyname, fsp.fspro_userid, fsp.primaryofficeId, fsp.Address1, fsp.City, fsp.State, fsp.ZipCode, fsp.firstname, fsp.lastname, fsp.email, fsp.phone, bro.broker_id as companyId, bo.broker_office_id, bo.broker_street_address, bo.broker_city, bo.broker_state ,bo.broker_zip, bo.broker_areacode + '-' + bo.broker_phone_number as broker_phone
from tbl_SMA_BrokerOfficeLink bol with (nolock)
inner join tblMfrBrokerLink mbl with (nolock)
on mbl.broker_office_id = bol.brokerofficeid
and mbl.mfr_id = 1237
inner join tbl_ORG_AttributeLinks oal with (nolock)
on oal.child_attrID = mbl.broker_office_id
and oal.child_attrTypeID = 27
and oal.mstr_attrTypeID = 34
inner join tbl_fspro_members fsp with (nolock)
on fsp.fspro_userid = oal.mstr_attrID
and fsp.ownerid = bol.brokerid
and fsp.fsl_tablecode = 'bro'
and fsp.PrimaryOfficeID in ( select broker_office_id from tblbrokeroffices where broker_id = bol.brokerid )
inner join tblBrokerOffices bo with (nolock)
on bo.broker_office_id = mbl.broker_office_id
inner join tblmanufacturers mfr with (nolock)
on mfr.mfr_id = bol.mfr_id
and mfr.FseManagedAccess in (1,2,3)
inner join tblbrokers bro with (nolock)
on bro.broker_id = bol.brokerid
inner join tbl_sec_memgrplink mgl with (nolock)
on mgl.FSPro_UserID = fsp.FSPro_UserID
inner join tbl_SEC_Groups grp with (nolock)
on grp.GroupID = mgl.GroupID
where bol.mfr_id = 1237
and (
(mfr.FseManagedAccess = 1 AND grp.GroupTypeCD = 'MPR' AND mgl.PartnerID = 1237 ) /* primary roles only */
OR
(mfr.FseManagedAccess = 2 AND ((grp.GroupTypeCD = 'MPR' AND mgl.PartnerID = 1237 ) or (grp.GroupTypeCD = 'MRR' and 'all' = 'all' ) )) /* primary + general roles */
OR
(mfr.FseManagedAccess = 3 AND ((grp.GroupTypeCD = 'MPR' AND mgl.PartnerID = 1237 ) or (grp.GroupTypeCD = 'MRR' and 'all' = 'all' ) or (grp.GroupTypeCD = 'SME') )) /* primary + general roles + subject matter experts */
)
AND ( select count(*)
from tbl_FSPro_MembersCoExclude
where FSPro_Userid = fsp.FSPro_UserID
and CoID = 1237
and CoType = 'MFR') = 0
and (
(
fsp.firstname like ? or
fsp.lastname like ? or
bro.broker_name like ?
)
) |