declare @ownerId int; declare @ownerType varchar(3); set @ownerId = ; set @ownerType = ; with i as ( select distinct applicationCode from fsenablers..tbl_APP_Configurations with (nolock) where ownerId = @ownerId and ownerType = @ownerType ), c as ( select applicationCode, parameterName, parameterValue, 'C' as parameterSource from fsenablers..tbl_APP_Configurations a where a.ownerId = @ownerId and a.ownerType = @ownerType and a.applicationCode in ( select applicationCode from i) ), ct as ( select applicationCode, parameterName, parameterValue, parameterSource from c union all select a.applicationCode, a.parameterName, a.parameterValue, 'T' as parameterSource from fsenablers..tbl_APP_Configurations a where a.ownerType = @ownerType and a.ownerId = 0 and not exists ( select * from c where c.applicationCode = a.applicationCode and c.parameterName = a.parameterName ) and a.applicationCode in ( select applicationCode from i) ), cta as ( select applicationCode, parameterName, parameterValue, parameterSource from ct union all select a.applicationCode, a.parameterName, a.parameterValue, 'A' as parameterSource from fsenablers..tbl_APP_Configurations a where a.ownerType = '*' and a.ownerId = 0 and not exists ( select * from ct where ct.applicationCode = a.applicationCode and ct.parameterName = a.parameterName ) and a.applicationCode in ( select applicationCode from i) ) , p1 as ( select distinct applicationCode, parameterName from fsenablers..tbl_APP_Configurations a with (nolock) where ownerId = 0 and ownerType = '*' ), p2 as ( select applicationCode, parameterName from p1 union all select distinct applicationCode, parameterName from fsenablers..tbl_APP_Configurations a with (nolock) where ownerId = 0 and ownerType = 'MFR' and not exists ( select * from p1 where p1.applicationCode = a.applicationCode and p1.parameterName = a.parameterName ) ), p3 as ( select applicationCode, parameterName from p2 union all select distinct applicationCode, parameterName from fsenablers..tbl_APP_Configurations a with (nolock) where ownerId = 0 and ownerType = 'BRO' and not exists ( select * from p2 where p2.applicationCode = a.applicationCode and p2.parameterName = a.parameterName ) ), cta2 as ( select coalesce( p3.applicationCode, cta.applicationCode ) as applicationCode, coalesce( p3.parameterName, cta.parameterName) as parameterName, cta.parameterValue, cta.parameterSource from cta left outer join p3 on p3.applicationCode = cta.applicationCode and p3.parameterName = cta.parameterName ) select * from cta2 select ownerId userOwnerId, fsl_tablecode userOwnerType, firstName userFirstName, lastName userLastName, rtrim( firstName ) + ' ' + rtrim( lastName ) as userFullName, email userEmailAddress, nullif( address1, '' ) userAddress1, nullif( address2, '' ) userAddress2, nullif( city, '' ) userCity, nullif( state, '' ) userState, nullif( zipCode, '' ) userZipCode, nullif( title, '' ) userTitle, nullif( phone, '' ) userPhone, nullif( cellPhone, '' ) userCell from tbl_fspro_members with (nolock) where fspro_userId = select ownerId, ownerType, implementation from tbl_TPM_settings with (nolock) where ownerId = and ownerType = select navigationMaxItems from fsenablers..tbl_CUS_Websites with (nolock) where portal_ownerId = and portal_ownerType = select coalesce( nullif( primaryChannel, '' ), 'FOODSERVICE' ) as primaryChannel from tbl_Fspro_members with (nolock) where fspro_userId = select distinct teamMemberRole from tbl_TER_TeamMembers with (nolock) where ownerId = and ownerType = and teamMemberUserId = _allUnitsOfMeasure = ArrayNew(1); ArrayAppend( _allUnitsOfMeasure, { "uom" = "cases", "uomDisplay" : "Cases" } ); if( attributes.ownerType eq "MFR" ) { qmd_mfr = QueryExecute( "select equivUnitsLabel from tblManufacturers with (nolock) where mfr_id = ?", [ attributes.ownerId ], { dataSource = attributes.datasource }); if( qmd_mfr.equivUnitsLabel neq "" ) { ArrayAppend( _allUnitsOfMeasure, { "uom" = "equivUnits", "uomDisplay" : qmd_mfr.equivUnitsLabel } ); } else { ArrayAppend( _allUnitsOfMeasure, { "uom" = "lbs", "uomDisplay" : "Lbs" } ); } } else { ArrayAppend( _allUnitsOfMeasure, { "uom" = "lbs", "uomDisplay" : "Lbs" } ); } ArrayAppend( _allUnitsOfMeasure, { "uom" = "dollars", "uomDisplay" : "Dollars" } ); _config["CRM"]["allUnitsOfMeasure"] = _allUnitsOfMeasure; _config["CRM"]["defaultUnitOfMeasure"] = _config.CRM.unitOfMeasurePlural; if( attributes.ownerType eq "MFR" and attributes.ownerId eq 2078) { _config["CRM"]["defaultUnitOfMeasure"] = "equivUnits"; } declare @ownerId int = ; declare @ownerType varchar(3) = ; with fieldLookups as ( union all select jobFunctionId as caJobFunction, jobFunctionName as caJobFunctionText, sortRank, 'OPR' as partnerType from tbl_OPR_JobFunctions with (nolock) where ownerId = @ownerId and ownerType = @ownerType select cast( selectionValue as int ) as caJobFunction, selectionLabel as caJobFunctionText, sortRank, case when fieldName = 'CDRJobFunction' then 'CDR' when fieldName = 'BPLJobFunction' then 'BPL' else null end as partnerType from tbl_FSPRO_FieldLookups rr with (nolock) where rr.ownerId = @ownerId and rr.ownerType = @ownerType and rr.fieldName = ) , jobFunctions as ( select distinct partnerType from fieldLookups ) select * from jobFunctions