use ResWareif (objectProperty(object_id('GetPartnerCompanies'), 'IsProcedure') is not null) drop procedure dbo.GetPartnerCompaniesGOCREATE PROCEDURE dbo.GetPartnerCompanies @FileID int, @ActionListDefID int = -1, @ReturnDisabled bit, @ReturnClients bit, @ReturnAllNonApproved bit, @ReturnExistingPartners bit, @ReturnOnlyApproved bit, @LastName varchar(80) = NULL, @FirstName varchar(80) = NULL, @DoingBusinessIn bit, @CountyID int = NULL, @State varchar(4) = NULL, @Override bit, @ClientID int, @CompanyName varchar(80) = NULL, @CompanyNameID int = NULL, @CompanyAbbr varchar(20) = NULL, @PartnerCompanyID int = NULL, @ParentSearch bit, @IgnoreDecliningPartner bit, @PartnerTypeID int = -1, @TitleCompanies varchar(200) -- Comma delimited list of IDsASDECLARE @OurError INT/******************************************************************** PROCEDURE: GetPartnerCompanies* PURPOSE: Search on partner companies* CALLED FM: PartnerCompanySearchDAO********************************************************************/SET NOCOUNT ONDECLARE @FileActionListDefID intDECLARE @ActionListTransactionTypeID intIF @FileID > 0BEGIN SET @ActionListTransactionTypeID = (SELECT ActionListTransactionTypeID FROM FileMain FM WHERE FM.FileID = @FileID) SET @FileActionListDefID = (SELECT ActionListOrigID FROM FileMain FM WHERE FM.FileID = @FileID)ENDELSEBEGIN SET @ActionListTransactionTypeID = NULL SET @FileActionListDefID = @ActionListDefIDENDSELECT PC.Approved, ISNULL((SELECT PS2.Name FROM PriorityStatus PS2 WHERE PS2.PriorityStatusID = MIN(PS.PriorityStatusID)), 'None') AS PriorityStatus, FM.FileNumber AS [File Number], PT.Name AS PartnerType, PC.Name, ISNULL(PC.City, '') + (CASE WHEN PC.City IS NOT NULL AND PC.State IS NOT NULL THEN ', ' ELSE '' END) + ISNULL(PC.State,'') AS Location, ISNULL(PC.Street1, '') + (CASE WHEN PC.Street1 IS NOT NULL AND PC.Street2 IS NOT NULL THEN ', ' ELSE '' END) + ISNULL(PC.Street2,'') AS Address, PC.PartnerCompanyID, GU.Enabled, PCPTR.PartnerTypeID, ISNULL(MAX(PCountyPTR.Weight), ISNULL(MAX(PCTTR.Weight), ISNULL(MAX(PCDR.Weight), MAX(PCPTR.Weight)))) AS Weight FROM PartnerCompany PC JOIN GeneralUser GU ON PC.PartnerCompanyID = GU.UserID AND (@ReturnDisabled = 1 OR GU.Enabled = 1) JOIN PartnerCompanyPartnerTypeRel PCPTR ON PC.PartnerCompanyID = PCPTR.PartnerCompanyID AND (@ReturnClients = 1 OR PCPTR.PartnerTypeID != 1) JOIN PartnerType PT ON PCPTR.PartnerTypeID = PT.PartnerTypeID LEFT JOIN (FilePartnerRel FPR JOIN FileMain FM ON FM.FileID = FPR.FileID AND @ReturnAllNonApproved = 1) ON FPR.PartnerCompanyID = PC.PartnerCompanyID AND PC.Approved = 0 LEFT JOIN PartnerEmployee PE ON PC.PartnerCompanyID = PE.PartnerCompanyID AND (PE.LastName LIKE @LastName OR PE.FirstName LIKE @FirstName) LEFT JOIN GeneralUser GU2 ON PE.PartnerEmployeeID = GU2.UserID LEFT JOIN ActionListDef ALD ON ALD.ActionListDefID = @ActionListDefID LEFT JOIN County C ON C.State = @State AND (C.CountyID = @CountyID OR @CountyID IS NULL) LEFT JOIN FileMain FM2 ON FM2.FileID = @FileID LEFT JOIN PartnerCountyDefaultRel PCDR ON PCDR.PartnerCompanyID = PC.PartnerCompanyID AND PCDR.PartnerTypeID = PCPTR.PartnerTypeID AND PCDR.CountyID = C.CountyID LEFT JOIN PartnerCountyTransactionTypeRel PCTTR ON PCTTR.PartnerCompanyID = PC.PartnerCompanyID AND PCTTR.PartnerTypeID = PCPTR.PartnerTypeID AND (PCTTR.ActionListTransactionTypeID = FM2.ActionListTransactionTypeID OR FM2.ActionListTransactionTypeID IS NULL) AND PCTTR.CountyID = C.CountyID LEFT JOIN PartnerCountyProductTypeRel PCountyPTR ON PCountyPTR.PartnerCompanyID = PC.PartnerCompanyID AND PCountyPTR.PartnerTypeID = PCPTR.PartnerTypeID AND (PCountyPTR.ActionListTransactionTypeID = FM2.ActionListTransactionTypeID OR FM2.ActionListTransactionTypeID IS NULL) AND (PCountyPTR.ActionListProductTypeID = FM2.ActionListProductTypeID OR FM2.ActionListProductTypeID IS NULL) AND PCountyPTR.CountyID = C.CountyID LEFT JOIN PriorityStatus PS ON PS.PriorityStatusID = ISNULL(PCountyPTR.PriorityStatusID,ISNULL(PCTTR.PriorityStatusID,ISNULL(PCDR.PriorityStatusID,PCPTR.PriorityStatusID))) LEFT JOIN ClientPartnerRel CPR ON CPR.PartnerCompanyID = PC.PartnerCompanyID AND CPR.PartnerTypeID = PCPTR.PartnerTypeID AND ((CPR.ClientID IS NULL AND CPR.ActionListTransactionTypeID IS NULL) OR (CPR.ClientID = @ClientID AND CPR.ActionListTransactionTypeID IS NULL) OR (CPR.ClientID = @ClientID AND CPR.ActionListTransactionTypeID = @ActionListTransactionTypeID)) LEFT JOIN ClientUnderwriterRel CUR ON CUR.ClientID = @ClientID LEFT JOIN TitleCompanyUnderwriterRel TCUR ON CUR.ClientID IS NULL OR CUR.UnderwriterID = TCUR.UnderwriterID LEFT JOIN FilePartnerRel FPRTC ON FPRTC.PartnerCompanyID = TCUR.TitleCompanyID AND FPRTC.PartnerTypeID = 2 AND FPRTC.FileID = @FileID LEFT JOIN FilePartnerRel FPR2 ON FPR2.PartnerCompanyID = PCPTR.PartnerCompanyID AND FPR2.PartnerTypeID = PCPTR.PartnerTypeID AND FPR2.FileID = @FileID WHERE (@ReturnAllNonApproved = 0 OR ((PC.Approved = 0 AND GU.Enabled = 1) OR PC.PartnerCompanyID IN (SELECT PartnerCompanyID FROM PartnerEmployee PE, GeneralUser GU2 WHERE PE.PartnerEmployeeID = GU2.UserID AND GU2.Enabled = 1 AND PE.Approved = 0))) AND (@ReturnOnlyApproved = 0 OR PC.Approved = 1) AND ((@LastName IS NULL AND @FirstName IS NULL) OR ((PE.LastName IS NOT NULL OR PE.FirstName IS NOT NULL) AND GU2.Enabled = 1)) AND (@DoingBusinessIn = 0 OR ((@CountyID IS NULL OR (PCountyPTR.CountyID = @CountyID AND PCountyPTR.Enabled = 1) OR (PCountyPTR.Enabled IS NULL AND PCTTR.CountyID = @CountyID AND PCTTR.Enabled = 1) OR (PCountyPTR.Enabled IS NULL AND PCTTR.Enabled IS NULL AND PCDR.CountyID = @CountyID AND PCDR.Enabled = 1)) AND (C.State = @State OR @State IS NULL))) AND (@DoingBusinessIn = 1 OR @State IS NULL OR PC.State = @State) AND (@CompanyName IS NULL OR PC.Name LIKE @CompanyName OR PC.PartnerCompanyID = @CompanyNameID) AND (@CompanyAbbr IS NULL OR PC.Abbreviation LIKE @CompanyAbbr) AND (@PartnerCompanyID IS NULL OR PC.PartnerCompanyID = @PartnerCompanyID) AND (@ParentSearch = 0 OR PC.ParentPartnerCompanyID IS NULL) AND (@ReturnExistingPartners = 1 OR FPR2.PartnerCompanyID IS NULL) AND (@IgnoreDecliningPartner = 1 OR @FileID <= 0 OR NOT EXISTS(SELECT FPFAR.PartnerCompanyID FROM FilePartnerFileActionRefused FPFAR JOIN FileActions FA ON FA.FileActionsID = FPFAR.FileActionsID WHERE FPFAR.PartnerCompanyID = PC.PartnerCompanyID AND FPFAR.PartnerTypeID = PT.PartnerTypeID AND FA.FileID = @FileID)) AND (@Override = 1 OR (CPR.PartnerCompanyID IS NOT NULL AND EXISTS (SELECT * FROM ClientPartnerRel CPR WHERE CPR.PartnerCompanyID = PC.PartnerCompanyID AND CPR.PartnerTypeID = PCPTR.PartnerTypeID AND ((CPR.ClientID IS NULL AND CPR.ActionListTransactionTypeID IS NULL) OR (CPR.ClientID = @ClientID AND CPR.ActionListTransactionTypeID IS NULL OR (CPR.ClientID = @ClientID AND CPR.ActionListTransactionTypeID = @ActionListTransactionTypeID)))))) -- Title Company, Underwriter & Clients Client AND ( (@PartnerTypeID = 2 AND PT.PartnerTypeID = 2 AND (TCUR.TitleCompanyID = PC.PartnerCompanyID OR TCUR.TitleCompanyID IS NULL)) OR (@PartnerTypeID = 5 AND PT.PartnerTypeID = 5 AND (@FileID = -1 OR @ActionListTransactionTypeID IS NULL OR PC.PartnerCompanyID IN (SELECT CPR.PartnerCompanyID FROM FileMain FMSub LEFT JOIN Property P ON P.PropertyID = FMSub.PrimaryPropertyID JOIN ClientPartnerRel CPR ON CPR.ClientID = FMSub.ClientID AND CPR.PartnerTypeID = 5 LEFT JOIN ClientsClientClientRelInfo CCCRI ON CCCRI.ClientPartnerRelID = CPR.ClientPartnerRelID LEFT JOIN ClientsClientClientActionListTransactionTypeActionListProductTypeRel CCCPTR ON CCCPTR.ClientID = FMSub.ClientID AND CCCPTR.ActionListTransactionTypeID = FMSub.ActionListTransactionTypeID AND CCCPTR.ActionListProductTypeID = FMSub.ActionListProductTypeID AND CCCPTR.ClientsClientID = CPR.PartnerCompanyID LEFT JOIN ClientsClientClientActionListTransactionTypeActionListProductTypeCountyRel CCCPTCR ON CCCPTCR.ActionListProductTypeID = CCCPTR.ActionListProductTypeID AND CCCPTCR.ActionListTransactionTypeID = CCCPTR.ActionListTransactionTypeID AND CCCPTCR.ClientID = CCCPTR.ClientID AND CCCPTCR.ClientsClientID = CCCPTR.ClientsClientID AND CCCPTR.OverrideCoverage = 1 AND P.CountyID = CCCPTCR.CountyID WHERE (((CCCPTR.OverrideCoverage = 1 AND (CCCPTCR.CountyID IS NOT NULL OR P.CountyID IS NULL)) OR CCCPTR.OverrideCoverage = 0) OR CCCRI.OverrideProducts = 0 OR CCCRI.OverrideProducts IS NULL) AND FMSub.FileID = @FileID))) OR (@PartnerTypeID = 7 AND PT.PartnerTypeID = 7 AND (@FileID = -1 OR ((TCUR.UnderwriterID = PC.PartnerCompanyID OR TCUR.UnderwriterID IS NULL OR FPRTC.PartnerCompanyID IS NULL) AND dbo.GetUnderwriterHasTitleCompaniesFunc(TCUR.UnderwriterID, @TitleCompanies) = 1 AND (@ClientID IS NULL OR CUR.ClientID IS NULL OR (CUR.ClientID IS NOT NULL AND CUR.UnderwriterID = PC.PartnerCompanyID))))) OR (@PartnerTypeID = -1 AND ((PT.PartnerTypeID = 2 AND (TCUR.TitleCompanyID = PC.PartnerCompanyID OR TCUR.TitleCompanyID IS NULL)) OR (PT.PartnerTypeID = 7 AND (TCUR.UnderwriterID = PC.PartnerCompanyID OR TCUR.UnderwriterID IS NULL OR FPRTC.PartnerCompanyID IS NULL) AND dbo.GetUnderwriterHasTitleCompaniesFunc(TCUR.UnderwriterID, @TitleCompanies) = 1 AND (@ClientID IS NULL OR CUR.ClientID IS NULL OR (CUR.ClientID IS NOT NULL AND CUR.UnderwriterID = PC.PartnerCompanyID))) OR (@FileID = -1 OR @ActionListTransactionTypeID IS NULL OR PC.PartnerCompanyID IN (SELECT CPR.PartnerCompanyID FROM FileMain FMSub LEFT JOIN Property P ON P.PropertyID = FMSub.PrimaryPropertyID JOIN ClientPartnerRel CPR ON CPR.ClientID = FMSub.ClientID AND CPR.PartnerTypeID = 5 LEFT JOIN ClientsClientClientRelInfo CCCRI ON CCCRI.ClientPartnerRelID = CPR.ClientPartnerRelID LEFT JOIN ClientsClientClientActionListTransactionTypeActionListProductTypeRel CCCPTR ON CCCPTR.ClientID = FMSub.ClientID AND CCCPTR.ActionListTransactionTypeID = FMSub.ActionListTransactionTypeID AND CCCPTR.ActionListProductTypeID = FMSub.ActionListProductTypeID AND CCCPTR.ClientsClientID = CPR.PartnerCompanyID LEFT JOIN ClientsClientClientActionListTransactionTypeActionListProductTypeCountyRel CCCPTCR ON CCCPTCR.ActionListProductTypeID = CCCPTR.ActionListProductTypeID AND CCCPTCR.ActionListTransactionTypeID = CCCPTR.ActionListTransactionTypeID AND CCCPTCR.ClientID = CCCPTR.ClientID AND CCCPTCR.ClientsClientID = CCCPTR.ClientsClientID AND CCCPTR.OverrideCoverage = 1 AND P.CountyID = CCCPTCR.CountyID WHERE (((CCCPTR.OverrideCoverage = 1 AND (CCCPTCR.CountyID IS NOT NULL OR P.CountyID IS NULL)) OR CCCPTR.OverrideCoverage = 0) OR CCCRI.OverrideProducts = 0 OR CCCRI.OverrideProducts IS NULL) AND FMSub.FileID = @FileID)) OR (PT.PartnerTypeID NOT IN (2,5,7)))) OR (@PartnerTypeID NOT IN (-1, 2, 5, 7) AND PT.PartnerTypeID = @PartnerTypeID)) GROUP BY PC.Approved, FM.FileNumber, PT.Name, PC.Name, PC.City, PC.State, PC.Street1, PC.Street2, PC.PartnerCompanyID, GU.Enabled, PCPTR.PartnerTypeID ORDER BY PriorityStatus, PC.Name, FM.FileNumberSET @OurError = @@ERRORIF @OurError <> 0BEGIN EXEC dbo.HandleError @ErrorNum = @OurError, @ProcID = @@PROCIDENDSET NOCOUNT OFFGO