select opr.operatorId, opr.companyName, opr.address, opr.city, opr.state, opr.zipcode from tbl_OPR_ClientOperators opr with (nolock)
where opr.ownerId = 14
and opr.fsltablecode = 'BRO'
and opr.crmActive = 'Y'
and opr.operatorId in (
select distinct operatorId from tbl_OPR_ClientOperators opr with (nolock)
left outer join tbl_CRM_Interactions int with (nolock)
on int.partnerId = opr.operatorId
and int.partnerType = 'OPR'
where opr.ownerId = 14
and opr.fsltablecode = 'BRO'
and opr.crmActive = 'Y'
and ( -- Operator has at least one finalized interaction since 1/1/2019
int.disposition <> '?' and cast( int.interactioNDate as date ) >= '2019/01/01'
-- Operator has an assigned account owner
or ( nullif( mfr_bsr_id, 0 ) <> 0 or nullif( mfr_bsr_id2, 0 ) <> 0 )
-- Operator has an open sales call
or int.disposition = '?' )
)
and not exists ( select imp.operatorId
from tbl_IMPORT_1FSOperators imp with (nolock)
where imp.eai_batchId = 2891063
and imp.compareStatus = 'G'
and imp.operatorId = opr.operatorId )
and nullif( opr.fsl_choId, 0 ) is null
SELECT bro.BROKER_NAME AS name
FROM tblBrokers bro
WHERE bro.broker_Id =
SELECT mfr.MFR_NAME AS name
FROM tblManufacturers mfr
WHERE mfr.mfr_id =
SELECT cho.cho_name AS name
FROM tbl_CHO_Operators cho
WHERE cho.cho_id =
SELECT msc.company AS name
FROM tbl_MSC_FSLibraryUsers msc
WHERE msc.userId =
SELECT dst.dstname AS name
FROM tbl_DST_Distributors dst
WHERE dstId =
theDir = GetDirectoryFromPath( GetCurrentTemplatePath() );
theFile = theDir & attributes.fileName;
theSheet = SpreadsheetNew( "Matched Operators" );
SpreadsheetAddColumn( theSheet, "FSL Account", 1, 1, true );
SpreadsheetAddColumn( theSheet, "Operator ID", 1, 2, true );
SpreadsheetAddColumn( theSheet, "Operator", 1, 3, true );
SpreadsheetAddColumn( theSheet, "Matched OwnerType", 1, 4, true );
SpreadsheetAddColumn( theSheet, "Matched FSL Account", 1, 5, true );
SpreadsheetAddColumn( theSheet, "Matched OperatorId", 1, 6, true );
SpreadsheetAddColumn( theSheet, "Matched Operator", 1, 7, true );
SpreadsheetAddColumn( theSheet, "Matched CRM Active", 1, 8, true );
SpreadsheetAddColumn( theSheet, "FSL ChoId", 1, 9, true );
SpreadsheetAddRows( theSheet, qmd_matches );