ALTER PROCEDURE [dbo].[spGetPerCaseInvoiceWorkItemsForStaff]
@StatusID INT,
@BranchID INT,
@PageNumber INT = 1,
@PageSize INT = 50
AS
BEGIN
SET NOCOUNT ON
CREATE TABLE #TotalInvoices(InvoiceWorkItemID INT, ClientID INT, ContractClaimStatusTypeID INT, CreatedDate DATETIME, ModifiedDate DATETIME, ApprovedDate DATETIME, CATStaffApprovedDate DATETIME);
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
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;
SELECT *
FROM
(
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
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
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
) T;
SELECT COUNT(InvoiceWorkItemID) AS TotalRecords FROM #TotalInvoices;
DROP TABLE #TotalInvoices;
DROP TABLE #TempInvoices;
DROP TABLE #TCInvoices;
DROP TABLE #TCCInvoices;
END
The issue is the result from select query and count from #totalinvoices are mismatching