CREATE PROCEDURE [dbo].[spGetPerCaseInvoiceWorkItemsForStaff] @StatusID INT, @BranchID INT, @PageNumber INT = 1, @PageSize INT = 50, @Search NVARCHAR(255) AS BEGIN SET NOCOUNT ON CREATE TABLE #TotalInvoices(InvoiceWorkItemID INT, ClientID INT, ContractClaimStatusTypeID INT, CreatedDate DATETIME, ModifiedDate DATETIME, ApprovedDate DATETIME, CATStaffApprovedDate DATETIME); CREATE TABLE #FinalRes ( InvoiceWorkItemID INT, ClientContractID INT, DeploymentReportId INT, WorkEnvironmentTypeID INT, WorkEnvironmentTypeName NVARCHAR(255), ClaimNumber NVARCHAR(255), DateInspected DATETIME, GrossLoss DECIMAL(18,2), City NVARCHAR(255), ProvinceID INT, ProvinceName NVARCHAR(255), CountryID INT, CountryName NVARCHAR(255), PostalCode NVARCHAR(20), LocationAddress NVARCHAR(1000), LocationAddress2 NVARCHAR(1000), ContractClaimStatusTypeID INT, ContractClaimStatusTypeName NVARCHAR(255), IsConMasterPolicy BIT, IsConUnitPolicy BIT, ContactID INT, FirstName NVARCHAR(255), MiddleName NVARCHAR(255), LastName NVARCHAR(255), EmployeeNumber NVARCHAR(255), BranchID INT, BranchName NVARCHAR(255), BranchCode NVARCHAR(255), BranchNumber NVARCHAR(255), policyholderLastName NVARCHAR(255), Apartment NVARCHAR(255), Building NVARCHAR(255), IsSupplimental BIT, Supplimental NVARCHAR(255), PolicyNumber NVARCHAR(255), ContactAssignmentID INT, ClientID INT, ClientName NVARCHAR(255), BranchManagers NVARCHAR(MAX), Comment NVARCHAR(MAX), InvoiceAmount DECIMAL(18,2), InvoiceAmount_Suppliment DECIMAL(18,2), ApprovedBy NVARCHAR(255),ApprovedDate DATETIME, CATStaffApprovedBy NVARCHAR(255), CATStaffApprovedDate DATETIME, isClaimToCMS BIT, AssignmentDate DATETIME, CompletionDate DATETIME, wastheLossSettled BIT, isTotalLoss BIT, PolicyholderName NVARCHAR(255), AliasName NVARCHAR(255), IsWaterBackUp BIT, WaterBackUpPolicyLimit DECIMAL(18,2), IsCondominium BIT, IsMarine BIT, ContractPerilID INT, ContractPerilName NVARCHAR(255), DateOfLoss DATETIME, OrigGrossAmountLoss DECIMAL(18,3), CreatedDate DATETIME, IsExpressClaim BIT, IsDifferentSUPPAdjuster BIT, ContractInvoicePerCaseCWPTypeID INT, CWPName NVARCHAR(500), ModifiedDate DATETIME, ActivityLogCount INT, ClaimStatus NVARCHAR(255), CMSClaimID NVARCHAR(255), CMSInvoiceNumber NVARCHAR(255), Severity INT ); DECLARE @strSql NVARCHAR(MAX); DECLARE @strSqlClouse NVARCHAR(MAX); IF @StatusID IS NULL BEGIN SET @StatusID = 3; END SET @strSqlClouse = CONCAT(N' WHERE ISNULL(IsDeleted, 0) = 0 AND ContractClaimStatusTypeID = ', @StatusID); IF @BranchID IS NOT NULL AND @BranchID <> 0 BEGIN SET @strSqlClouse = CONCAT(@strSqlClouse, ' AND BranchID = ', @BranchID); END --DECLARE @CmsClaimIDStr NVARCHAR(255); -- SET @CmsClaimIDStr = CONCAT('%', CONVERT(NVARCHAR(255), @CmsClaimID), '%'); -- -- Modify WHERE clause to use LIKE for CmsClaimID -- SET @strSqlClouse = CONCAT(@strSqlClouse, ' AND CMSClaimID LIKE ', QUOTENAME(@CmsClaimIDStr, '''')); SET @strSql = N'INSERT INTO #TotalInvoices (InvoiceWorkItemID, ClientID, ContractClaimStatusTypeID, CreatedDate, ModifiedDate, ApprovedDate, CATStaffApprovedDate)' + 'SELECT InvoiceWorkItemID, ClientID, ContractClaimStatusTypeID, CreatedDate, ModifiedDate, ApprovedDate, CATStaffApprovedDate FROM [dbo].[InvoiceWorkItem] ' + @strSqlClouse + ' UNION ALL ' + 'SELECT ContractInvoicePerCaseID, ClientID, ContractClaimStatusTypeID, CreatedDate, ModifiedDate, ApprovedDate, CATStaffApprovedDate FROM [dbo].[ContractInvoicePerCase] ' + @strSqlClouse; EXEC sp_executesql @strSql; SELECT * INTO #TempInvoices FROM #TotalInvoices T ORDER BY CASE WHEN ContractClaimStatusTypeID = 3 THEN ModifiedDate WHEN ContractClaimStatusTypeID = 4 THEN ApprovedDate WHEN ContractClaimStatusTypeID = 5 THEN CATStaffApprovedDate ELSE CreatedDate END DESC OFFSET((@PageNumber - 1) * @PageSize) ROWS FETCH NEXT(@PageSize) ROWS ONLY; SELECT I.TimeCardId, I.ClaimId, I.InvoiceNumber, I.InvoiceTypeID, IPST.InvoiceProcessStatusTypeName AS ClaimStatus INTO #TCInvoices FROM (SELECT TimeCardId, InvoiceId, InvoiceNumber, ClaimId, InvoiceTypeId, InvoiceSourceTypeID, ROW_NUMBER() OVER(PARTITION BY TimeCardId ORDER BY CreatedDate DESC) RN FROM [dbo].[Invoice] WHERE InvoiceSourceTypeID = 2) I LEFT JOIN (SELECT *, ROW_NUMBER() OVER(PARTITION BY InvoiceId, TimeCardId ORDER BY CreatedDate DESC) RN FROM [dbo].[InvoiceProcessLog] WHERE InvoiceSourceTypeID = 2) IPL ON IPL.InvoiceId = I.InvoiceId AND IPL.TimeCardId = I.TimeCardId AND IPL.InvoiceSourceTypeID = I.InvoiceSourceTypeID AND I.RN = 1 AND IPL.RN = 1 LEFT JOIN [dbo].[InvoiceProcessStatusType] IPST ON IPL.InvoiceProcessStatusTypeId = IPST.InvoiceProcessStatusTypeId WHERE I.RN = 1; SELECT I.TimeCardId, I.ClaimId, I.InvoiceTypeID, I.InvoiceNumber, IPST.InvoiceProcessStatusTypeName AS ClaimStatus INTO #TCCInvoices FROM (SELECT TimeCardId, InvoiceId, InvoiceNumber, ClaimId, InvoiceTypeId, InvoiceSourceTypeID, ROW_NUMBER() OVER(PARTITION BY TimeCardId ORDER BY CreatedDate DESC) RN FROM [dbo].[Invoice] WHERE InvoiceSourceTypeID = 5) I LEFT JOIN (SELECT *, ROW_NUMBER() OVER(PARTITION BY InvoiceId, TimeCardId ORDER BY CreatedDate DESC) RN FROM [dbo].[InvoiceProcessLog] WHERE InvoiceSourceTypeID = 5) IPL ON IPL.InvoiceId = I.InvoiceId AND IPL.TimeCardId = I.TimeCardId AND IPL.InvoiceSourceTypeID = I.InvoiceSourceTypeID AND I.RN = 1 AND IPL.RN = 1 LEFT JOIN [dbo].[InvoiceProcessStatusType] IPST ON IPL.InvoiceProcessStatusTypeId = IPST.InvoiceProcessStatusTypeId WHERE I.RN = 1; Insert Into #FinalRes( InvoiceWorkItemID, ClientContractID , DeploymentReportId , WorkEnvironmentTypeID , WorkEnvironmentTypeName , ClaimNumber , DateInspected, GrossLoss, City , ProvinceID , ProvinceName , CountryID , CountryName , PostalCode , LocationAddress , LocationAddress2 , ContractClaimStatusTypeID , ContractClaimStatusTypeName , IsConMasterPolicy , IsConUnitPolicy, ContactID , FirstName , MiddleName , LastName , EmployeeNumber , BranchID , BranchName , BranchCode , BranchNumber , policyholderLastName , Apartment , Building , IsSupplimental , Supplimental , PolicyNumber , ContactAssignmentID , ClientID , ClientName , BranchManagers , Comment , InvoiceAmount , InvoiceAmount_Suppliment , ApprovedBy ,ApprovedDate , CATStaffApprovedBy , CATStaffApprovedDate , isClaimToCMS , AssignmentDate , CompletionDate , wastheLossSettled , isTotalLoss , PolicyholderName , AliasName , IsWaterBackUp , WaterBackUpPolicyLimit , IsCondominium , IsMarine , ContractPerilID , ContractPerilName , DateOfLoss , OrigGrossAmountLoss , CreatedDate , IsExpressClaim , IsDifferentSUPPAdjuster , ContractInvoicePerCaseCWPTypeID , CWPName, ModifiedDate , ActivityLogCount , ClaimStatus , CMSClaimID , CMSInvoiceNumber, Severity) SELECT IWI.InvoiceWorkItemID AS InvoiceWorkItemID, IWI.ClientContractID, IWI.DeploymentReportId, IWI.WorkEnvironmentTypeID,WET.WorkEnvironmentTypeName,IWI.ClaimNumber,IWI.DateInspected,IWI.GrossLoss,IWI.City,IWI.ProvinceID,PR.ProvinceName ,CT.CountryID,CT.CountryName,IWI.PostalCode,IWI.LocationAddress,LocationAddress2 = CAST(NULL AS NVARCHAR(1000)),IWI.ContractClaimStatusTypeID,ST.ContractClaimStatusTypeName,IWI.IsConMasterPolicy,IWI.IsConUnitPolicy, IWI.ContactID,RTRIM(LTRIM(ISNULL(VIWI.FirstName,''))) FirstName, RTRIM(LTRIM(ISNULL(VIWI.MiddleName,''))) MiddleName, RTRIM(LTRIM(ISNULL(VIWI.LastName,''))) LastName, VIWI.EmployeeNumber, IWI.BranchID, VIWI.BranchName, VIWI.Code AS BranchCode, VIWI.Code AS BranchNumber,(ISNULL(IWI.policyholderLastName,'')) AS policyholderLastName , (ISNULL(IWI.Apartment,'')) Apartment, (ISNULL(IWI.Building,'')) Building ,(ISNULL(IWI.IsSupplimental,0)) IsSupplimental , (ISNULL(IWI.Supplimental,'')) Supplimental, CAST(NULL AS NVARCHAR(255)) AS PolicyNumber, CAST(0 AS INT) AS ContactAssignmentID, IWI.ClientID,VIWI.ClientName,STUFF((SELECT DISTINCT (', '+ISNULL((LTRIM(RTRIM(CTMM.FirstName)) +' '+ LTRIM(RTRIM(CTMM.LastName))),'')) FROM [dbo].vw_BranchMgrSummary CTMM WHERE CTMM.ContactID=VIWI.ManagerContactID AND IWI.BranchID = CTMM.BranchID FOR XML PATH('')),1,1,'')BranchManagers, Comment= '',InvoiceAmount=(SELECT ISNULL(SUM(IDWI.InvoiceAmount),0) FROM [dbo].InvoiceWorkItemDetail IDWI WITH(NOLOCK) WHERE IDWI.InvoiceWorkItemID=IWI.InvoiceWorkItemID) ,InvoiceAmount_Suppliment=(SELECT ISNULL(SUM(IDWI.InvoiceAmount),0) FROM [dbo].InvoiceWorkItemDetail_Suppliment IDWI WITH(NOLOCK) WHERE IDWI.InvoiceWorkItemID=IWI.InvoiceWorkItemID) ,(LTRIM(RTRIM(ISNULL(CTM.FirstName,''))) + ' ' + LTRIM(RTRIM(ISNULL(CTM.LastName,'')))) AS ApprovedBy,IWI.ApprovedDate ,(LTRIM(RTRIM(ISNULL(CTAU.FirstName,''))) + ' ' + LTRIM(RTRIM(ISNULL(CTAU.LastName,'')))) AS CATStaffApprovedBy,IWI.CATStaffApprovedDate ,IWI.isClaimToCMS,VIWI.AssignmentDate, VIWI.CompletionDate,IWI.wastheLossSettled,IWI.isTotalLoss,IWI.PolicyholderName,VIWI.AliasName ,IsWaterBackUp = CAST(NULL AS BIT),WaterBackUpPolicyLimit = CAST(NULL AS money) ,IsCondominium=CAST((CASE WHEN IWI.IsConMasterPolicy=1 OR IWI.IsConUnitPolicy=1 THEN 1 ELSE 0 END ) AS BIT) ,IsMarine=CAST((CASE WHEN VIWI.CcompCode='MFS' THEN 1 ELSE 0 END ) AS BIT) ,ContractPerilID = CAST(NULL AS INT),ContractPerilName = CAST(NULL AS NVARCHAR(1000)),DateOfLoss = CAST(NULL AS DATETIME),OrigGrossAmountLoss = CAST(NULL AS DECIMAL(18,3)), IWI.CreatedDate,IsExpressClaim=CAST(NULL AS BIT), IsDifferentSUPPAdjuster=CAST(NULL AS BIT),ContractInvoicePerCaseCWPTypeID = CAST(NULL AS INT),CWPName = CAST(NULL AS NVARCHAR(500)) ,IWI.ModifiedDate,ActivityLogCount = (SELECT COUNT(1) FROM [dbo].[InvoiceActivityLog] TRHH WHERE TRHH.InvoiceWorkItemID=IWI.InvoiceWorkItemID AND ISNULL(TRHH.InvoiceSourceTypeID, 2) = 2 AND TRHH.ContractClaimStatusTypeID=2) ,(CASE WHEN TINC.InvoiceTypeId = 2 THEN 'Credited' ELSE ISNULL(TINC.ClaimStatus, 'Pending') END) AS ClaimStatus ,TINC.ClaimID CMSClaimID ,TINC.InvoiceNumber CMSInvoiceNumber,isnull(IWI.Severity,0) as Severity FROM (SELECT InvoiceWorkItemID FROM #TempInvoices WHERE ClientID = 2) TI INNER JOIN [dbo].InvoiceWorkItem IWI WITH(NOLOCK) ON TI.InvoiceWorkItemID = IWI.InvoiceWorkItemID INNER JOIN [dbo].[vw_InvoiceWorkItemSummary] VIWI ON VIWI.ContactID=IWI.ContactID AND VIWI.BranchID=IWI.BranchID AND VIWI.DeploymentReportID=IWI.DeploymentReportID INNER JOIN [dbo].[WorkEnvironmentType] WET WITH(NOLOCK) ON WET.WorkEnvironmentTypeID=IWI.WorkEnvironmentTypeID INNER JOIN [dbo].[ContractClaimStatusType] ST WITH(NOLOCK) ON ST.ContractClaimStatusTypeID=IWI.ContractClaimStatusTypeID LEFT JOIN [dbo].[Country] CT WITH(NOLOCK) ON CT.CountryID=IWI.CountryID LEFT JOIN [dbo].[Province] PR WITH(NOLOCK) ON PR.ProvinceID=IWI.ProvinceID LEFT JOIN [dbo].[Contact] CTM ON IWI.ApprovedBy = CTM.ContactID LEFT JOIN [dbo].[ApplicationUser] CTAU ON IWI.CATStaffApprovedBy = CTAU.ApplicationUserID LEFT JOIN #TCInvoices TINC ON IWI.InvoiceWorkItemID = TINC.TimeCardId UNION ALL SELECT IWI.ContractInvoicePerCaseID AS InvoiceWorkItemID, IWI.ClientContractID, DeploymentReportId = CAST(NULL AS INT), IWI.WorkEnvironmentTypeID,WET.WorkEnvironmentTypeName, --CONCAT(ISNULL(IWI.ClaimNumber, ''), ((CASE WHEN IsSupplimental = 1 THEN CONCAT(' (Supp: ', Supplimental, ')') ELSE '' END))) AS ClaimNumber IWI.ClaimNumber,IWI.DateInspected,GrossLoss = CAST(IWI.GrossLoss AS DECIMAL(18,3)),IWI.City,IWI.ProvinceID,PR.ProvinceName ,CT.CountryID,CT.CountryName,IWI.PostalCode,IWI.LocationAddress,IWI.LocationAddress2,IWI.ContractClaimStatusTypeID,ST.ContractClaimStatusTypeName,IsConMasterPolicy = CAST(NULL AS BIT),IsConUnitPolicy = CAST(NULL AS BIT), IWI.ContactID,RTRIM(LTRIM(ISNULL(VIWI.FirstName,''))) FirstName, RTRIM(LTRIM(ISNULL(VIWI.MiddleName,''))) MiddleName, RTRIM(LTRIM(ISNULL(VIWI.LastName,''))) LastName, VIWI.EmployeeNumber, IWI.BranchID, VIWI.BranchName, VIWI.Code AS BranchCode, VIWI.Code AS BranchNumber,(ISNULL(IWI.PolicyholderLastName,'')) AS policyholderLastName , (ISNULL(IWI.Apartment,'')) Apartment, (ISNULL(IWI.Building,'')) Building ,(ISNULL(IWI.IsSupplimental,0)) IsSupplimental , (ISNULL(IWI.Supplimental,'')) Supplimental, IWI.PolicyNumber, IWI.ContactAssignmentID, IWI.ClientID,VIWI.ClientName,STUFF((SELECT DISTINCT (', '+ISNULL((LTRIM(RTRIM(CTMM.FirstName)) +' '+ LTRIM(RTRIM(CTMM.LastName))),'')) FROM [dbo].vw_BranchMgrSummary CTMM WHERE CTMM.ContactID=VIWI.ManagerContactID AND IWI.BranchID = CTMM.BranchID FOR XML PATH('')),1,1,'')BranchManagers, Comment='',InvoiceAmount=(SELECT ISNULL(SUM(IDWI.InvoiceAmount),0) FROM [dbo].[ContractInvoicePerCaseDetail] IDWI WITH(NOLOCK) WHERE IDWI.ContractInvoicePerCaseID=IWI.ContractInvoicePerCaseID) ,InvoiceAmount_Suppliment=(SELECT ISNULL(SUM(IDWI.InvoiceAmount),0) FROM [dbo].[ContractInvoicePerCaseDetailSupplimental] IDWI WITH(NOLOCK) WHERE IDWI.ContractInvoicePerCaseID=IWI.ContractInvoicePerCaseID) ,(LTRIM(RTRIM(ISNULL(CTM.FirstName,''))) + ' ' + LTRIM(RTRIM(ISNULL(CTM.LastName,'')))) AS ApprovedBy,IWI.ApprovedDate ,(LTRIM(RTRIM(ISNULL(CTAU.FirstName,''))) + ' ' + LTRIM(RTRIM(ISNULL(CTAU.LastName,'')))) AS CATStaffApprovedBy,IWI.CATStaffApprovedDate ,isClaimToCMS = CAST(NULL AS BIT),VIWI.AssignmentDate, VIWI.CompletionDate,wastheLossSettled = CAST(NULL AS BIT),isTotalLoss = CAST(NULL AS BIT),IWI.PolicyholderName,VIWI.AliasName ,IWI.IsWaterBackUp,IWI.WaterBackUpPolicyLimit ,IsCondominium = CAST(NULL AS BIT),IsMarine = CAST(NULL AS BIT),IWI.ContractPerilID, CP.PerilName AS ContractPerilName,IWI.DateOfLoss,OrigGrossAmountLoss = CAST(IWI.OrigGrossAmountLoss AS DECIMAL(18,3)), IWI.CreatedDate,IWI.IsExpressClaim, IWI.IsDifferentSUPPAdjuster,IWI.ContractInvoicePerCaseCWPTypeID ,CWPName =(SELECT TOP 1 CWPName FROM ContractInvoicePerCaseCWPType WHERE ContractInvoicePerCaseCWPTypeID=iwi.ContractInvoicePerCaseCWPTypeID) ,IWI.ModifiedDate,ActivityLogCount = (SELECT COUNT(1) FROM [dbo].[InvoiceActivityLog] TRHH WHERE TRHH.InvoiceWorkItemID=IWI.ContractInvoicePerCaseID AND ISNULL(TRHH.InvoiceSourceTypeID, 2) = 5 AND TRHH.ContractClaimStatusTypeID=2) ,(CASE WHEN TINC.InvoiceTypeId = 2 THEN 'Credited' ELSE ISNULL(TINC.ClaimStatus, 'Pending') END) AS ClaimStatus ,TINC.ClaimID CMSClaimID ,TINC.InvoiceNumber CMSInvoiceNumber,0 as Severity FROM (SELECT InvoiceWorkItemID FROM #TempInvoices WHERE ClientID <> 2) TI INNER JOIN [dbo].[ContractInvoicePerCase] IWI WITH(NOLOCK) ON TI.InvoiceWorkItemID = IWI.ContractInvoicePerCaseID INNER JOIN [dbo].[vw_ContractInvoicePerCaseSummary] VIWI ON VIWI.ContactID=IWI.ContactID AND VIWI.BranchID=IWI.BranchID AND VIWI.ContactAssignmentID = IWI.ContactAssignmentID INNER JOIN [dbo].[WorkEnvironmentType] WET WITH(NOLOCK) ON WET.WorkEnvironmentTypeID=IWI.WorkEnvironmentTypeID INNER JOIN [dbo].[ContractClaimStatusType] ST WITH(NOLOCK) ON ST.ContractClaimStatusTypeID=IWI.ContractClaimStatusTypeID LEFT JOIN [dbo].[Country] CT WITH(NOLOCK) ON CT.CountryID=IWI.CountryID LEFT JOIN [dbo].[Province] PR WITH(NOLOCK) ON PR.ProvinceID=IWI.ProvinceID LEFT JOIN [dbo].[ContractPeril] CP WITH(NOLOCK) ON CP.ContractPerilID = IWI.ContractPerilID LEFT JOIN [dbo].[Contact] CTM ON IWI.ApprovedBy = CTM.ContactID LEFT JOIN [dbo].[ApplicationUser] CTAU ON IWI.CATStaffApprovedBy = CTAU.ApplicationUserID LEFT JOIN #TCCInvoices TINC ON IWI.ContractInvoicePerCaseID = TINC.TimeCardId; SELECT * INTO #ResSet FROM #FinalRes WHERE (@Search IS NULL OR ( RTRIM(LTRIM(ISNULL(FirstName, ''))) LIKE '%' + @Search + '%' OR RTRIM(LTRIM(ISNULL(LastName, ''))) LIKE '%' + @Search + '%' OR EmployeeNumber LIKE '%' + @Search + '%' OR ClaimNumber LIKE '%' + @Search + '%' OR CMSInvoiceNumber LIKE '%' + @Search + '%' OR CMSClaimID LIKE CONCAT('%', CONVERT(NVARCHAR(255), @Search), '%') )) ORDER BY InvoiceWorkItemID DESC --OFFSET (@PageSize *(@PageNumber - 1) ) ROWS --FETCH NEXT @PageSize ROWS ONLY; SELECT * FROM #ResSet; SELECT COUNT(InvoiceWorkItemID) AS TotalRecords FROM #TempInvoices; DROP TABLE #TotalInvoices; DROP TABLE #TempInvoices; DROP TABLE #TCInvoices; DROP TABLE #TCCInvoices; DROP TABLE #FinalRes; DROP TABLE #ResSet; END GO