ALTER PROCEDURE [dbo].[GetClaimDetailsByMasterRefDev](@MasterRef char(10)) with recompile AS set dateformat dmy Declare @POALetterCode int Declare @PIFLetterCode int Select @PIFLetterCode = dbo.GetLetterCode('PIF',@MasterRef) -- 20 Select @POALetterCode = dbo.GetLetterCode('POA',@MasterRef) -- 21 SELECT dbo.Claim.ClaimRef, dbo.Claim.MasterRef, ClaimStatusDesc = CASE dbo.Claim.ClaimStatus When '01' Then 'Inc' When '02' Then 'Chk' When '03' Then 'Agr' When '04' Then 'Rej' When '05' Then 'Adm' Else '' end, dbo.Claim.ClaimStatus, dbo.Claim.CreditorName, dbo.Claim.ClaimNarrative, dbo.Claim.PayDetailsID, dbo.Claim.AdmittedValUSD, convert(varchar(10),dbo.ListNos.listdate,5) As AdmittedDate, dbo.PaymentDetails.PayAddressID, dbo.PaymentDetails.PIFStatusCode, (CASE WHEN ((dbo.Claim.PayDetailsConf = 0) OR EXISTS (Select PayDetailsID From TTDetails Where PayDetailsID = Claim.PayDetailsID And TTDetailsConf = 0)) And ClaimStatus = '05' THEN 'Y' WHEN ClaimStatus != '05' THEN '' ELSE 'N' END) As PIF, dbo.Master.CorrAddressID, convert(varchar(10),dbo.Claim.PayDetailsConfDate,5) As PayDetailsConfDate, dbo.Claim.PayDetailsConfUser, dbo.Claim.PayDetailsConf, PayDetailsRequestedDate = (Select convert(varchar(10),Max(LetterSentDate),5) From dbo.LetterHistory Where claimref = claim.claimref and lettercode = @PIFLetterCode), PayDetailsRequestedUser = (select top 1 LetterSentUser from letterhistory where LetterSentDate = (select max(LetterSentDate) from LetterHistory Where LetterCode = @PIFLetterCode And ClaimRef = Claim.ClaimRef) And ClaimRef = Claim.ClaimRef), (Select Distinct convert(varchar(10),Max(DateReceived),5) From dbo.PayDetailsReplies Where ClaimRef = Claim.ClaimRef) As PayDetailsReceiveddate, (Select Top 1 InputBy from PayDetailsReplies where DateReceived = (select max(DateReceived) from PayDetailsReplies Where ClaimRef = Claim.ClaimRef)) As PayDetailsReceivedUser, dbo.Address.CountryCode, dbo.Listnos.luxoruk, dbo.Claim.AdmittedListNo, dbo.multiblk.block As PayBlockCode, PayBlockDescription = multiblk.block + ': - ' + rtrim(blockpay.blkd_desc) + ' - ' + multiblk.raised_by + ' - ' + convert(varchar(10),multiblk.raised_dt,5), (select count(block) from dbo.multiblk Where active = 1 and clm_ref = dbo.Claim.ClaimRef) As NumBlocks, (CASE WHEN EXISTS (SELECT block FROM dbo.multiblk WHERE dbo.multiblk.block = '40' AND dbo.Multiblk.clm_ref = dbo.Claim.ClaimRef AND dbo.Multiblk.Active = 1) THEN 'Y' ELSE 'N' END) AS GadFlag, (CASE WHEN EXISTS (SELECT block FROM dbo.multiblk WHERE dbo.multiblk.block = '94' AND dbo.Multiblk.clm_ref = dbo.Claim.ClaimRef AND dbo.Multiblk.Active = 1) THEN 'Y' ELSE 'N' END) AS Deceased, Art39.BenstatusCode As DBO, (Select distinct(convert(varchar(10),Max(LetterSentDate),5)) from LetterHistory Where LetterCode =@POALetterCode And ClaimRef = Claim.ClaimRef) As POADetailsRequestedDate, (select distinct(LetterSentUser) from letterhistory where LetterSentDate = (select distinct(Max(LetterSentDate)) from LetterHistory Where LetterCode = @POALetterCode) And ClaimRef = Claim.ClaimRef) As POADetailsRequestedUser, (Select convert(varchar(10),Max(DateReceived),5) from POADetailsReceived Where ClaimRef = Claim.ClaimRef) As POADetailsReceivedDate, (select distinct(InputBy) from POADetailsReceived where DateReceived = (select Max(DateReceived) from POADetailsReceived Where ClaimRef = Claim.ClaimRef)) As POADetailsReceivedUser, DBOReceivedDate = (Select convert(varchar(10),Max(DBOReceivedDate),5) From dbo.DBOSReceived Where dbo.DBOSReceived.ClaimRef = Claim.ClaimRef), DBOReceivedUser = (Select DBOReceivedUser From dbo.DBOSReceived Where dbo.DBOSReceived.ClaimRef = Claim.ClaimRef And DBOReceivedDate = (Select Max(DBOReceivedDate) From dbo.DBOSReceived Where dbo.DBOSReceived.ClaimRef = Claim.ClaimRef)), lds.due, lds.dpbflag, lds.adjustflag, lds.empdivflag From dbo.Claim Left Outer Join Listnos ON Claim.AdmittedListNo = Listnos.AdmitList Left Outer Join Art39 ON Claim.ClaimRef = Art39.ClaimRef Inner JOIN dbo.Master On dbo.Master.MasterRef = dbo.Claim.MasterRef Inner Join dbo.Address On Master.CorrAddressID = dbo.Address.AddressID Inner Join dbo.PaymentDetails On PaymentDetails.PayDetailsID = dbo.Claim.PayDetailsID left outer join lds.dbo.vw_cms_claims_main lds on dbo.Claim.ClaimRef = lds.claim_reference left outer join multiblk on Claim.ClaimRef = multiblk.clm_ref And multiblk.Status = 1 And multiblk.Active = 1 left outer join blockpay On blockpay.blkd_code = multiblk.block Where dbo.Claim.MasterRef = @MasterRef Order By Claim.ClaimRef