ALTER PROCEDURE [dbo].[sp_LossRunSummaryClaimantList]
@SECURITY_ID AS VARCHAR(50), @ACCOUNT_NBR AS VARCHAR(40), @UNDERWRITING_PROGRAM_NBR AS VARCHAR(50), @CLAIM_STATUS AS VARCHAR(50), @LOB_KEY AS VARCHAR(100), @POLICY_YR AS INT --@POLICY_YR AS INT
AS
BEGIN
-- EXEC [dbo].[sp_LMCClaimListAll5Year] 'S-1-5-21-3783409357-2204173064-303048338-183906', '0000011003', '0011'
IF EXISTS (SELECT * FROM dbo.PORTAL_USER u JOIN dbo.PORTAL_USER_PERMISSION up ON up.USER_KEY = u.USER_KEY WHERE u.SECURITY_ID = @SECURITY_ID AND up.ACCOUNT_NBR IN (@ACCOUNT_NBR,'ALL') AND up.UNDERWRITING_PROGRAM_NBR = @UNDERWRITING_PROGRAM_NBR)
BEGIN
SELECT
LineOfBusiness = LOB_NM,
PolicyYear = POLICY_YR,
ClaimStatus = CLAIM_LAST_STATUS,
ClaimNumber = ISNULL(CLAIM_LEGACY_NBR, CLAIM_NBR),
ClaimantNumber = CLAIMANT_NBR,
ClaimantName = CLAIMANT_NM,
LossDate = LOSS_DT,
Paid = SUM(GROSS_LOSS_PAID + GROSS_EXPENSE_PAID) + SUM(WC_GROSS_PAID) ,
Reserves = SUM(LOSS_RESERVE + EXPENSE_RESERVE) + SUM(WC_RESERVE) ,
TotalIncurredAmount = SUM(GROSS_LOSS_PAID + GROSS_EXPENSE_PAID + LOSS_RESERVE + EXPENSE_RESERVE + SUBROGATION + SALVAGE + DEDUCTIBLE_RECOVERY + RECOVERIES_OTHER) +
SUM(WC_GROSS_PAID + WC_RESERVE + WC_RECOVERIES),
Recoveries = SUM(SUBROGATION + SALVAGE + DEDUCTIBLE_RECOVERY + RECOVERIES_OTHER) + SUM(WC_RECOVERIES)
FROM dbo.AGG_MEMBER_CENTER
WHERE ACCOUNT_NBR = @ACCOUNT_NBR
AND UNDERWRITING_PROGRAM_NBR = @UNDERWRITING_PROGRAM_NBR
AND LAST_STATUS IN (@CLAIM_STATUS)
AND LOB_KEY IN (dbo.ufn_ParseString2Table(@LOB_KEY,',')) -- (@LOB_KEY)
AND POLICY_YR IN (@POLICY_YR) --(dbo.ufn_ParseString2Table(@POLICY_YR,',')) -- (@POLICY_YR)
GROUP BY LOB_NM,
POLICY_YR,
ISNULL(CLAIM_LEGACY_NBR, CLAIM_NBR),
CLAIM_LAST_STATUS,
CLAIMANT_NBR,
CLAIMANT_NM,
LOSS_DT
ORDER BY 1 ASC ,
2 DESC,
4 DESC;
END
USE [BRAC_Portal]
GO
/****** Object: UserDefinedFunction [dbo].[ufn_ParseString2Table] Script Date: 8/8/2016 1:48:53 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[ufn_ParseString2Table] (@String VARCHAR(8000), @Delimiter VARCHAR(10) = ',')
/************************************************************************
PROCEDURE uf_ManageSecurityFlag
ARGUMENTS
RETURNS Table in table variable
DESCRIPTION RETRIEVES CURRENT USER INFO
AUTHOR DATE DESCRIPTION
DOL 08/8/2016 Parses Delimited String to Table
EXAMPLE
SELECT CONVERT(INT,ITEM) FROM dbo.ufn_ParseString2Table ('39, 549, 324, 3556, 24, 2132, 345 ,', ',')
************************************************************************/
RETURNS @ReturnTable TABLE(Item VARCHAR(100))
AS
BEGIN
WHILE CHARINDEX(@Delimiter,@String)>0
BEGIN
INSERT @ReturnTable
SELECT SUBSTRING(@String,1,(CHARINDEX(@Delimiter,@String)-1))
SELECT @String=RIGHT(@String,DATALENGTH(@String)-CHARINDEX(@Delimiter,@String))
END
INSERT @ReturnTable SELECT @String
RETURN
END
Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.
”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.
Our community of experts have been thoroughly vetted for their expertise and industry experience.