baclight/card_view.sql

USE [VHCS_HIPAA]
GO

/****** Object:  View [dbo].[HLIDCardsViewEgg]    Script Date: 12/3/2025 12:34:01 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO






ALTER VIEW [dbo].[HLIDCardsViewEgg]
AS
    SELECT  dbo.HLIDCardsEgg.Facility, 
			dbo.HLIDCardsEgg.Division AS Division, 
			UPPER(dbo.HLIDCardsEgg.FullName) AS FullName, 
            'XXX-XX-' + SUBSTRING(dbo.HLIDCardsEgg.SSN, 6, 4) AS SSN, 
			dbo.HLIDCardsEgg.MedicalCoverage, 
			dbo.HLIDCardsEgg.MedicalEffDate, 
			dbo.HLIDCardsEgg.MedicalGroupNum, 
            dbo.HLIDCardsEgg.DentalCoverage, 
			dbo.HLIDCardsEgg.DentalEffDate, 
			dbo.HLIDCardsEgg.DentalGroupNum, 
			dbo.HLIDCardsEgg.CardType, 
			dbo.HLIDCardProvider.ProviderCode, 
            dbo.HLIDCardProvider.ProviderLine1, 
			dbo.HLIDCardProvider.ProviderLine2, 
			dbo.HLIDCardProvider.ProviderLine3, 
			dbo.HLIDCardProvider.ProviderLine4, 
            dbo.HLIDCardProvider.ProviderLine5, 
			dbo.HLIDCardProvider.ProviderLine6, 
			dbo.HLIDCardProvider.ProviderLine7, 
			dbo.HLIDCardProvider.ProviderLine8, 
            dbo.HLIDCardProvider.ProviderLine9, 
			dbo.HLIDCardProvider.ProviderLine10, 
			dbo.HLIDCardProvider.ProviderLine11, 
			dbo.HLIDCardProvider.MailTo, 
            dbo.HLIDCardProvider.MailTo2, 
			dbo.HLIDCardProvider.ClaimTo1, 
			dbo.HLIDCardProvider.ClaimTo2, 
			dbo.HLIDCardProvider.ClaimTo3, 
            dbo.HLIDCardProvider.ClaimTo4, 
			dbo.HLIDCardProvider.ClaimTo5, 
			dbo.HLIDCardProvider.ClaimTo6, 
			dbo.HLIDCardProvider.ClaimTo7, 
            dbo.HLIDCardProvider.ClaimTo8, 
			dbo.HLIDCardProvider.ClaimTo9, 
			dbo.HLIDCardProvider.ClaimTo10, 
			dbo.HLIDCardProvider.ClaimTo11, 
            dbo.HLIDCardProvider.ContactLine1, 
			dbo.HLIDCardProvider.ContactLine2, 
			dbo.HLIDCardProvider.ContactLine3, 
			dbo.HLIDCardsEgg.GroupNumber, 
            REPLACE(dbo.HLIDCardsEgg.SSN, '-', '') AS FamilyId, dbo.HLIDCardsEgg.GroupNumber AS GroupNo, 
			
			CASE 
			  WHEN dbo.HLIDCardsEgg.PLPlanKey = 56 -- HealthBus/TCRM BENEFITS - Plan Specific RXGroupID
                THEN (Select RXGroupID From HLRXCrosRef_Product Where PLPlanKey = dbo.HLIDCardsEgg.PLPlanKey And PBProductKey = dbo.HLIDCardsEgg.PBProductKey)			  
			  ELSE dbo.HLRXCrosRef.RXGroupID  
			END as RXGroupID,
			
			dbo.HLRXCrosRef.HelpDesk, 
            dbo.HLRXCrosRef.CustomerService, 
			dbo.HLRXCrosRef.WebUrl, 
			dbo.HLIDCardProvider.MailTo2 AS Expr1,
			
			CASE 
			  WHEN CardType = 'T' THEN '1-800-676-1182' 
			  WHEN CardType = 'I' THEN '1-800-676-1182' 
			  WHEN CardType = 'A' THEN '1-800-676-1182' 
			  WHEN CardType = 'J' THEN '1-800-676-1182' 
			  WHEN CardType = 'K' THEN '1-800-676-1182' 
			  ELSE 'Britton Benefits at 1-800-676-1182' 
			END Line3,
			
	        ISNULL((Select DependentName 
			  From (Select distinct TOP(1) dbo.HLEggIdCardDependent.DependentName DependentName, dbo.HLEggIdCardDependent.SequenceNumber SequenceNumber
					From dbo.HLEggIdCardDependent Where dbo.HLEggIdCardDependent.FamilyId = dbo.HLIDCardsEgg.SSN
					order by dbo.HLEggIdCardDependent.SequenceNumber) A),'') as Dependent1,
	        ISNULL((Select DependentName
			  From (Select distinct dbo.HLEggIdCardDependent.DependentName DependentName, dbo.HLEggIdCardDependent.SequenceNumber SequenceNumber
					From dbo.HLEggIdCardDependent Where dbo.HLEggIdCardDependent.FamilyId = dbo.HLIDCardsEgg.SSN
					order by dbo.HLEggIdCardDependent.SequenceNumber
					OFFSET 1 ROWS -- backup 1 row
					FETCH NEXT 1 ROWS ONLY) A),'') as Dependent2,
	        ISNULL((Select DependentName
			  From (Select distinct dbo.HLEggIdCardDependent.DependentName DependentName, dbo.HLEggIdCardDependent.SequenceNumber SequenceNumber
					From dbo.HLEggIdCardDependent Where dbo.HLEggIdCardDependent.FamilyId = dbo.HLIDCardsEgg.SSN
					order by dbo.HLEggIdCardDependent.SequenceNumber
					OFFSET 2 ROWS -- backup 2 rows
					FETCH NEXT 1 ROWS ONLY) A),'') as Dependent3,
	        ISNULL((Select DependentName
			  From (Select distinct dbo.HLEggIdCardDependent.DependentName DependentName, dbo.HLEggIdCardDependent.SequenceNumber SequenceNumber
					From dbo.HLEggIdCardDependent Where dbo.HLEggIdCardDependent.FamilyId = dbo.HLIDCardsEgg.SSN
					order by dbo.HLEggIdCardDependent.SequenceNumber
					OFFSET 3 ROWS -- backup 3 rows
					FETCH NEXT 1 ROWS ONLY) A),'') as Dependent4,
	        ISNULL((Select DependentName
			  From (Select distinct dbo.HLEggIdCardDependent.DependentName DependentName, dbo.HLEggIdCardDependent.SequenceNumber SequenceNumber
					From dbo.HLEggIdCardDependent Where dbo.HLEggIdCardDependent.FamilyId = dbo.HLIDCardsEgg.SSN
					order by dbo.HLEggIdCardDependent.SequenceNumber
					OFFSET 4 ROWS -- backup 4 rows
					FETCH NEXT 1 ROWS ONLY) A),'') as Dependent5,
            ISNULL((Select DependentName
			  From (Select distinct dbo.HLEggIdCardDependent.DependentName DependentName, dbo.HLEggIdCardDependent.SequenceNumber SequenceNumber
					From dbo.HLEggIdCardDependent Where dbo.HLEggIdCardDependent.FamilyId = dbo.HLIDCardsEgg.SSN
					order by dbo.HLEggIdCardDependent.SequenceNumber
					OFFSET 5 ROWS -- backup 5 rows
					FETCH NEXT 1 ROWS ONLY) A),'') as Dependent6,
	        ISNULL((Select DependentName
			  From (Select distinct dbo.HLEggIdCardDependent.DependentName DependentName, dbo.HLEggIdCardDependent.SequenceNumber SequenceNumber
					From dbo.HLEggIdCardDependent Where dbo.HLEggIdCardDependent.FamilyId = dbo.HLIDCardsEgg.SSN
					order by dbo.HLEggIdCardDependent.SequenceNumber
					OFFSET 6 ROWS -- backup 6 rows
					FETCH NEXT 1 ROWS ONLY) A),'') as Dependent7,
	        ISNULL((Select DependentName
			  From (Select distinct dbo.HLEggIdCardDependent.DependentName DependentName, dbo.HLEggIdCardDependent.SequenceNumber SequenceNumber
					From dbo.HLEggIdCardDependent Where dbo.HLEggIdCardDependent.FamilyId = dbo.HLIDCardsEgg.SSN
					order by dbo.HLEggIdCardDependent.SequenceNumber
					OFFSET 7 ROWS -- backup 7 rows
					FETCH NEXT 1 ROWS ONLY) A),'') as Dependent8,
	
	        ISNULL((Select h.BenefitDesc From HLEgglestonCardBenefit  h Where PlanId = dbo.HLIDCardsEgg.PBProductKey AND h.Sequence = 1), '') as BenDesc1,
	        ISNULL((Select h.Benefit     From HLEgglestonCardBenefit  h Where PlanId = dbo.HLIDCardsEgg.PBProductKey AND h.Sequence = 1), '') as Ben1,
	        ISNULL((Select h.BenefitDesc From HLEgglestonCardBenefit  h Where PlanId = dbo.HLIDCardsEgg.PBProductKey AND h.Sequence = 2), '') as BenDesc2,
	        ISNULL((Select h.Benefit     From HLEgglestonCardBenefit  h Where PlanId = dbo.HLIDCardsEgg.PBProductKey AND h.Sequence = 2), '') as Ben2,
	        ISNULL((Select h.BenefitDesc From HLEgglestonCardBenefit  h Where PlanId = dbo.HLIDCardsEgg.PBProductKey AND h.Sequence = 3), '') as BenDesc3,
	        ISNULL((Select h.Benefit     From HLEgglestonCardBenefit  h Where PlanId = dbo.HLIDCardsEgg.PBProductKey AND h.Sequence = 3), '') as Ben3,
	        ISNULL((Select h.BenefitDesc From HLEgglestonCardBenefit  h Where PlanId = dbo.HLIDCardsEgg.PBProductKey AND h.Sequence = 4), '') as BenDesc4,
	        ISNULL((Select h.Benefit     From HLEgglestonCardBenefit  h Where PlanId = dbo.HLIDCardsEgg.PBProductKey AND h.Sequence = 4), '') as Ben4,
	        ISNULL((Select h.BenefitDesc From HLEgglestonCardBenefit  h Where PlanId = dbo.HLIDCardsEgg.PBProductKey AND h.Sequence = 5), '') as BenDesc5,
	        ISNULL((Select h.Benefit     From HLEgglestonCardBenefit  h Where PlanId = dbo.HLIDCardsEgg.PBProductKey AND h.Sequence = 5), '') as Ben5,
	        ISNULL((Select h.BenefitDesc From HLEgglestonCardBenefit  h Where PlanId = dbo.HLIDCardsEgg.PBProductKey AND h.Sequence = 6), '') as BenDesc6,
	        ISNULL((Select h.Benefit     From HLEgglestonCardBenefit  h Where PlanId = dbo.HLIDCardsEgg.PBProductKey AND h.Sequence = 6), '') as Ben6,
	        ISNULL((Select h.BenefitDesc From HLEgglestonCardBenefit  h Where PlanId = dbo.HLIDCardsEgg.PBProductKey AND h.Sequence = 7), '') as BenDesc7,
	        ISNULL((Select h.Benefit     From HLEgglestonCardBenefit  h Where PlanId = dbo.HLIDCardsEgg.PBProductKey AND h.Sequence = 7), '') as Ben7,
	        ISNULL((Select h.BenefitDesc From HLEgglestonCardBenefit  h Where PlanId = dbo.HLIDCardsEgg.PBProductKey AND h.Sequence = 8), '') as BenDesc8,
	        ISNULL((Select h.Benefit     From HLEgglestonCardBenefit  h Where PlanId = dbo.HLIDCardsEgg.PBProductKey AND h.Sequence = 8), '') as Ben8,
	        ISNULL((Select h.BenefitDesc From HLEgglestonCardBenefit  h Where PlanId = dbo.HLIDCardsEgg.PBProductKey AND h.Sequence = 9), '') as BenDesc9,
	        ISNULL((Select h.Benefit     From HLEgglestonCardBenefit  h Where PlanId = dbo.HLIDCardsEgg.PBProductKey AND h.Sequence = 9), '') as Ben9,
	        ISNULL((Select h.BenefitDesc From HLEgglestonCardBenefit  h Where PlanId = dbo.HLIDCardsEgg.PBProductKey AND h.Sequence = 10),'') as BenDesc10,
	        ISNULL((Select h.Benefit     From HLEgglestonCardBenefit  h Where PlanId = dbo.HLIDCardsEgg.PBProductKey AND h.Sequence = 10),'') as Ben10,
	        ISNULL((Select h.BenefitDesc From HLEgglestonCardBenefit  h Where PlanId = dbo.HLIDCardsEgg.PBProductKey AND h.Sequence = 11),'') as BenDesc11,
	        ISNULL((Select h.Benefit     From HLEgglestonCardBenefit  h Where PlanId = dbo.HLIDCardsEgg.PBProductKey AND h.Sequence = 11),'') as Ben11,
	        ISNULL((Select h.BenefitDesc From HLEgglestonCardBenefit  h Where PlanId = dbo.HLIDCardsEgg.PBProductKey AND h.Sequence = 12),'') as BenDesc12,
	        ISNULL((Select h.Benefit     From HLEgglestonCardBenefit  h Where PlanId = dbo.HLIDCardsEgg.PBProductKey AND h.Sequence = 12),'') as Ben12,
	        ISNULL((Select h.BenefitDesc From HLEgglestonCardBenefit  h Where PlanId = dbo.HLIDCardsEgg.PBProductKey AND h.Sequence = 13),'') as BenDesc13,
	        ISNULL((Select h.Benefit     From HLEgglestonCardBenefit  h Where PlanId = dbo.HLIDCardsEgg.PBProductKey AND h.Sequence = 13),'') as Ben13,
	        ISNULL((Select h.BenefitDesc From HLEgglestonCardBenefit  h Where PlanId = dbo.HLIDCardsEgg.PBProductKey AND h.Sequence = 14),'') as BenDesc14,
	        ISNULL((Select h.Benefit     From HLEgglestonCardBenefit  h Where PlanId = dbo.HLIDCardsEgg.PBProductKey AND h.Sequence = 14),'') as Ben14,

	        dbo.HLIDCardsEgg.PLPlanKey,
	        dbo.HLIDCardsEgg.MBMemberKey,
	        dbo.HLIDCardProvider.ProviderLookup1,
			dbo.HLIDCardProvider.ProviderLookup2,
	        dbo.HLIDCardProvider.Precert1,
			dbo.HLIDCardProvider.Precert2,
			dbo.HLIDCardProvider.Precert3,
			dbo.HLIDCardProvider.Precert4,
			dbo.HLIDCardProvider.Precert5,
			dbo.HLIDCardProvider.Precert6,
			
	        CASE 
			  WHEN dbo.HLIDCardsEgg.PLPlanKey IN (13, 21, 39, 49, 50, 51, 20, 52, 19, 53, 54, 56, 58, 60, 61, 62) 
			    THEN 'Benefits are not insured by Cigna Healthcare or affiliates' 
		      ELSE '' 
			END AS MISCDATA,
			
	        CASE WHEN dbo.HLIDCardsEgg.PLPlanKey IN (21,39) THEN 'CIGNA' ELSE '' END AS PPODATA,
	        CASE WHEN dbo.HLIDCardsEgg.PLPlanKey IN (21,39) THEN 'PPO'   ELSE '' END AS PPODATA2,
	        CASE WHEN dbo.HLIDCardsEgg.PLPlanKey IN (21,39) THEN '"S"'   ELSE '' END AS PPODATA3,
	        
			CASE
	          WHEN LEN(FullName) - LEN(REPLACE(FullName,' ','')) > 2
		        THEN FullName
	          WHEN LEN(FullName) - LEN(REPLACE(FullName,' ','')) > 1
		        THEN SUBSTRING(SUBSTRING(FullName , CHARINDEX(' ', FullName ) + 1, LEN(FullName )), 
				        CHARINDEX(' ', SUBSTRING(FullName , CHARINDEX(' ', FullName ) + 1, LEN(FullName )) ) + 1, 
						LEN(SUBSTRING(FullName , CHARINDEX(' ', FullName ) + 1, LEN(FullName )) ))
	          WHEN LEN(FullName) - LEN(REPLACE(FullName,' ','')) > 0
		        THEN SUBSTRING(FullName , CHARINDEX(' ', FullName ) + 1, LEN(FullName ))
	          ELSE FullName
	        END AS LastName   

    FROM dbo.HLIDCardsEgg 
    INNER JOIN dbo.HLIDCardProvider 
	  ON dbo.HLIDCardsEgg.CardType = dbo.HLIDCardProvider.ProviderCode 
    INNER JOIN dbo.HLRXCrosRef 
	  ON dbo.HLRXCrosRef.GroupNo = dbo.HLIDCardsEgg.GroupNumber
    WHERE dbo.HLIDCardsEgg.PBProductKey IN (Select PBProductKey From PBProduct Where FullDescription NOT LIKE '%LIFE%' AND FullDescription NOT LIKE '%VISION%')  
      AND dbo.HLIDCardsEgg.PBProductKey <> 1059 -- Grace Chapel Vision

GO