--USE Prasanna_LHP
--GO
--EXEC OES_SP_Get_EmployeeAttributeFields 2, 4567, 0, 5, '',  11694 ,1, 0  
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER PROCEDURE [dbo].[OES_SP_Get_EmployeeAttributeFields]       
 @PageID int,      
 @EmployeeID Bigint,      
 @ROWID bigint,      
 @RoleID int,      
 @RequireDependentSSN varchar(30) output,      
 @CompanyID  BigInt,      
 @LanguageID int ,
 @RequireOnlyFieldInfo bit = 0      
AS        

BEGIN    
    --------Set Nocount on    
    --DECLARE @PageID INT;
    --DECLARE @EmployeeID BIGINT;
    --DECLARE @OEmployeeAttributeFields VARCHAR(4000);
    --DECLARE @ROWID BIGINT;
    --DECLARE @RequireDependentSSN VARCHAR(30);
    --DECLARE @RoleID AS BIGINT;
    --DECLARE @LanguageID AS INT;
    --DECLARE @CompanyID AS BIGINT;
    --DECLARE @RequireOnlyFieldInfo AS BIT;

    --SET @EmployeeID = 24193;
    --SET @ROWID = 0;
    --SET @PageID = 2;
    --SET @RoleID = 5;
    --SET @LanguageID = 1;
    --SET @CompanyID = 12911;
    --SET @RequireOnlyFieldInfo = 0;

    Declare @IsXpressCompany Bit = 0
    SELECT @IsXpressCompany = IsXpressCompany FROM OnlineEnrollment.DBO.Company WHERE ID = @CompanyID

    CREATE TABLE #EmployeeAttributeTempTable
    (
	   ColumnName VARCHAR(500),
	   ColumnValue VARCHAR(1000)
    );
    CREATE TABLE #EmployeeAttributeTempTableFloat
    (
	   ColumnName VARCHAR(500),				
	   ColumnValue FLOAT
    );
    CREATE TABLE #TempTableEmployeeAttributeFields
    (
                 Tname                       VARCHAR(100), 
                 ColumnName                  VARCHAR(500), 
                 ControlName                 VARCHAR(100), 
                 ControlType                 INT, 
                 DataType                    VARCHAR(50), 
                 ControlValue                VARCHAR(4000), 
                 ControlValueDouble          FLOAT, 
                 CustomLabel                 VARCHAR(MAX), 
                 Controlsize                 INT, 
                 IsPassword                  BIT, 
                 IsRequired                  BIT, 
                 RegularExpression           VARCHAR(500), 
                 AdditionalFieldValidationID INT, 
                 NoOfColumns                 INT, 
                 NoOfRows                    INT, 
                 IsWordWrap                  BIT, 
                 isMultiSelect               BIT, 
                 URL                         VARCHAR(100), 
                 Width                       INT, 
                 Height                      INT, 
                 Position                    INT, 
                 MediaTypeID                 INT, 
                 Options                     VARCHAR(MAX), 
                 FieldNote                   VARCHAR(500), 
                 ComponentSizeID             INT, 
                 FieldSubTypeID              INT, 
                 SequenceNo                  INT, 
                 IsAdditionalField           INT, 
                 MemberRight                 INT, 
                 FieldID                     BIGINT                 
    );

    CREATE TABLE #TablesUsed
    (
	   ID INT IDENTITY,	   -- Idenetity will get reset once the table is dropped and created again
	   TableName VARCHAR(500),
	   ColumnList VARCHAR(MAX),
	   DataType VARCHAR(50)
    )
    
    DECLARE @TableName VARCHAR(100);
    DECLARE @ColumnName VARCHAR(100);
    DECLARE @Cnt INT;
    DECLARE @DataType NVARCHAR(50);
    DECLARE @EmployeeValue VARCHAR(MAX);
    DECLARE @RequireSpouseSSN BIT;
    DECLARE @RequireChildSSN BIT;
    DECLARE @SpouseAgeForSSN_Years INT;
    DECLARE @SpouseAgeForSSN_Month INT;
    DECLARE @ChildAgeForSSN_Years INT;
    DECLARE @ChildAgeForSSN_Month INT;
    DECLARE @CustomLabel AS VARCHAR(MAX);

    SET @RequireDependentSSN = '';
       
    IF @RequireOnlyFieldInfo <> 1
    BEGIN
        DECLARE @RaisedQualifyingEventIsAdminApprovedFlag AS BIT;
        DECLARE @EffectiveDate AS DATETIME;
        DECLARE @BASEDATE AS DATETIME;
        SET @BASEDATE = GETDATE();
        EXECUTE OES_SP_GENERIC_EFFECTIVEDATE_CALCULATION 
                @BASEDATE, 
                @EmployeeID, 
                @RoleID, 
                0, 
                @LanguageID, 
                0, 
                0, 
                NULL, 
                0, 
                0, 
                '', 
                @RaisedQualifyingEventIsAdminApproved = @RaisedQualifyingEventIsAdminApprovedFlag OUTPUT, 
                @NewEffectiveDate = @EffectiveDate OUTPUT;
        IF @EffectiveDate IS NULL
           OR @EffectiveDate = ''
            SELECT @EffectiveDate = ISNULL(EffectiveDate, GETDATE())
            FROM Employee
            WHERE ID = @EmployeeID;        
        ------------------------------------------------------------------------------------------------------------------------    
    END;

    INSERT INTO #TempTableEmployeeAttributeFields (FieldID, Tname, ColumnName, ControlName, ControlType, DataType, ControlValue, ControlValueDouble, 
    CustomLabel, Controlsize, IsPassword, IsRequired, RegularExpression, AdditionalFieldValidationID, NoOfColumns, NoOfRows, IsWordWrap, isMultiSelect, 
    URL, Width, Height, Position, MediaTypeID, Options, FieldNote, ComponentSizeID, FieldSubTypeID, SequenceNo, IsAdditionalField, MemberRight)           
    SELECT dbo.fields.ID, dbo.TableRef.Name, dbo.Fields.DestinationColumnName, 
    FT.Name,
    dbo.Fields.FieldTypeID AS ControlType, -- verify for modification
    '' AS Datatype, 
    '0' AS ControlValue,
    0.0 AS ControlValueDouble,
    CASE
	   WHEN @LanguageID = 1 THEN ISNULL(dbo.FieldsLabel_Multilingual.FieldLabelEN, '')
        WHEN @LanguageID = 2 THEN ISNULL(dbo.FieldsLabel_Multilingual.FieldLabelSP, '')
        WHEN @LanguageID = 3 THEN ISNULL(dbo.FieldsLabel_Multilingual.FieldLabelFR, '')
    END, ISNULL(dbo.Fields.MaximumLength, 0) AS Controlsize, 0 AS IsPassword, 0 AS IsRequired, 
    '' AS RegularExpression, 0 AS AdditionalFieldValidationID, 
    0 AS NoOfColumns, 0 AS NoOfRows, 0 AS IsWordWrap, 0 AS isMultiSelect, '' AS URL, 0 AS Width, 0 AS Height, ISNULL(RowNo, 0) AS Position, 
    0 AS MediaTypeID, '' AS Options,               
        --'' as FieldNote 
    CASE
	   WHEN @LanguageID = 1 THEN COALESCE(FM_FieldNote.FieldLabelEN, '')
        WHEN @LanguageID = 2 THEN COALESCE(FM_FieldNote.FieldLabelSP, FM_FieldNote.FieldLabelEN, '')
        WHEN @LanguageID = 3 THEN COALESCE(FM_FieldNote.FieldLabelFR, FM_FieldNote.FieldLabelEN, '')
    END AS FieldNote,
    CASE
	   WHEN Fields.ControlWidth = '8%' THEN (SELECT 1)    
	   WHEN Fields.ControlWidth = '15%' THEN (SELECT 1)
	   WHEN Fields.ControlWidth = '16%' THEN (SELECT 2)
	   WHEN Fields.ControlWidth = '20%' THEN (SELECT 2)
	   WHEN Fields.ControlWidth = '25%' THEN (SELECT 2)
	   WHEN Fields.ControlWidth = '52%' THEN (SELECT 3)
	   WHEN Fields.ControlWidth IS NULL THEN (SELECT 1)
    END AS ComponentSizeID, 0 AS FieldSubTypeID, SequenceNumber AS SequenceNo, 0 AS IsAdditionalField, 0 AS MemberRight
    FROM dbo.Fields
    INNER JOIN dbo.TableRef ON dbo.Fields.TableRefID = dbo.TableRef.ID
    INNER JOIN dbo.FieldsLabel_Multilingual ON dbo.FieldsLabel_Multilingual.DestinationTableRefID = dbo.Fields.TableRefID
    LEFT OUTER JOIN FieldType FT ON FT.ID = FieldTypeID
    LEFT OUTER JOIN FieldsLabel_Multilingual FM_FieldNote ON Fields.ID = FM_FieldNote.DestinationFieldID
                                                             AND FM_FieldNote.DestinationTableRefID = 2
                                                             AND FM_FieldNote.IsadditionalField = 0
    WHERE(dbo.Fields.PageID = @PageID)
         AND (FieldTypeID < 7
              OR FieldTypeID = 11)
         AND dbo.Fields.ID = dbo.FieldsLabel_Multilingual.DestinationFieldID
         AND dbo.FieldsLabel_Multilingual.IsAdditionalField = 0
         AND dbo.Fields.DestinationColumnName IS NOT NULL
    ORDER BY dbo.Fields.SequenceNumber;
    
    ---------------- Prasanna: Need to check if multiple enrties can come from Information schema -------------   
    UPDATE FormFields
    SET DataType = DATA_TYPE
    FROM #TempTableEmployeeAttributeFields FormFields    
    INNER JOIN INFORMATION_SCHEMA.COLUMNS ON Column_Name = FormFields.ColumnName AND Table_Name = FormFields.Tname; 
    
    DECLARE @ColumnList VARCHAR(MAX)
    INSERT INTO #TablesUsed(TableName, ColumnList)
    SELECT Tname, 
	   (STUFF(
		  (
			 SELECT CASE WHEN DataType = 'smalldatetime' OR DataType = 'datetime' THEN ',CAST([' + ColumnName + '] AS VARCHAR(50)) AS ' + ColumnName
			 ELSE ',[' + ColumnName + ']' END
			 FROM #TempTableEmployeeAttributeFields TInner
			 WHERE TInner.Tname = TOuter.Tname
			 AND TInner.DataType <> 'float'
			 AND IsAdditionalField = 0
			 AND TInner.ColumnName IS NOT NULL
			 FOR XML PATH ('')
		  ),1,1,'')) AS ColumnList
    FROM #TempTableEmployeeAttributeFields TOuter
    WHERE IsAdditionalField = 0 AND DataType <> 'float'   
    AND Tname IS NOT NULL
    GROUP BY Tname, DataType
    
    INSERT INTO #TablesUsed(TableName, ColumnList, DataType)
    SELECT Tname, 
	   (STUFF(
		  (
			 SELECT ',[' + ColumnName + ']'
			 FROM #TempTableEmployeeAttributeFields TInner
			 WHERE TInner.Tname = TOuter.Tname
			 AND TInner.DataType = 'float'
			 AND IsAdditionalField = 0
			 AND TInner.ColumnName IS NOT NULL
			 FOR XML PATH ('')
		  ),1,1,'')) AS ColumnList, DataType
    FROM #TempTableEmployeeAttributeFields TOuter
    WHERE IsAdditionalField = 0 AND DataType = 'float'  
    AND Tname IS NOT NULL  
    GROUP BY Tname, DataType
    
    DECLARE @cntTablesUsed INT = (SELECT COUNT(TableName) FROM #TablesUsed)
    SET @cnt = 1
    
    --SELECT * FROM #TablesUsed
    
    ---- DestinationTableRefID = 2 belongs to Fields Table. It has been used for Field Note in Fields Label Multilingual   
    ---- For Base fields on specific page       
    WHILE(@cnt <= @cntTablesUsed)              
    BEGIN  
	   SELECT @TableName = TableName, @ColumnList = ColumnList, @DataType = Datatype
	   FROM #TablesUsed WHERE ID = @cnt
	                    
        IF @TableName = 'AddEmployee'
            SET @TableName = 'Employee';
                             
        IF @TableName = 'Employee'
            BEGIN                         
                SET @EmployeeValue = 'Select  Top 1 '+@ColumnList+'  From  '+@TableName+'  where ID = '+CAST(@EmployeeID AS VARCHAR);
            END;
        ELSE
        IF @TableName = 'Employee_Medicare'
           OR @TableName = 'Employee_Medicaid'
            BEGIN
                SET @EmployeeValue = 'Select Top 1 '+@ColumnList+'  From  '+@TableName+'  where EmployeeID = '+CAST(@EmployeeID AS VARCHAR)+' AND ID = '+CAST(@ROWID AS VARCHAR(100));
            END;
        ELSE
        IF @TableName = 'Employee_Medical'
           OR @TableName = 'Employee_Emergency'
            BEGIN
                SET @EmployeeValue = 'Select  Top 1 '+@ColumnList+' From  '+@TableName+'  where EmployeeID = '+CAST(@EmployeeID AS VARCHAR);
            END;
        ELSE
        IF @TableName = 'Employee_Payroll'
            BEGIN                          
                SET @EmployeeValue = 'Select  Top 1 '+@ColumnList+' From  '+@TableName+'  where EmployeeID = '+CAST(@EmployeeID AS VARCHAR);
            END;
        ELSE
        IF @TableName = 'Employee_HealthData'
            BEGIN                           
                SET @EmployeeValue = 'Select  Top 1 '+@ColumnList+' From  '+@TableName+'  where EmployeeID = '+CAST(@EmployeeID AS VARCHAR)+' order by EffectiveDate desc ';
            END;
        ELSE
        BEGIN                           
            SET @EmployeeValue = 'Select Top 1 '+@ColumnList+' From  '+@TableName+'  where EmployeeID = '+CAST(@EmployeeID AS VARCHAR)+' AND ID = '+CAST(@ROWID AS VARCHAR(100));
        END;
            
        SET @EmployeeValue = 'DECLARE @xmlColumns AS XML; SET @xmlColumns = ( ' + @EmployeeValue + ' FOR XML RAW)'
        --PRINT @EmployeeValue
	   IF @DataType = 'float'
	   BEGIN
		  SET @EmployeeValue = @EmployeeValue + 'INSERT INTO #EmployeeAttributeTempTableFloat SELECT CAST(AttributeColumn.query(''local-name(.)'') AS VARCHAR(500)) ColName, AttributeColumn.value(''.'',''FLOAT'') ColValue FROM @xmlColumns.nodes(''//@*'') AttributeTable(AttributeColumn)';
               
		  EXEC (@EmployeeValue);
		  
		  UPDATE tmpAttributeFields
		  SET tmpAttributeFields.ControlValueDouble = ISNULL(tmpFloatValues.ColumnValue,0.0),
		  tmpAttributeFields.ControlValue = NULL
		  FROM #TempTableEmployeeAttributeFields tmpAttributeFields
		  LEFT OUTER JOIN #EmployeeAttributeTempTableFloat tmpFloatValues ON tmpFloatValues.ColumnName = tmpAttributeFields.ColumnName
		  WHERE tmpAttributeFields.Tname = @TableName AND DataType = 'float'
               
		  DELETE FROM #EmployeeAttributeTempTableFloat
	   END					   
	   ELSE
	   BEGIN
		  SET @EmployeeValue = @EmployeeValue + 'INSERT INTO #EmployeeAttributeTempTable SELECT CAST(AttributeColumn.query(''local-name(.)'') AS VARCHAR(500)) ColName, AttributeColumn.value(''.'',''VARCHAR(4000)'') ColValue FROM @xmlColumns.nodes(''//@*'') AttributeTable(AttributeColumn)'
                           
		  EXEC (@EmployeeValue);
                           
		  UPDATE tmpAttributeFields
		  SET tmpAttributeFields.ControlValue = ISNULL(tmpVarcharValues.ColumnValue, '0'),
		  tmpAttributeFields.ControlValueDouble = NULL		  
		  FROM #TempTableEmployeeAttributeFields tmpAttributeFields
		  LEFT OUTER JOIN #EmployeeAttributeTempTable tmpVarcharValues ON tmpVarcharValues.ColumnName = tmpAttributeFields.ColumnName
		  WHERE tmpAttributeFields.Tname = @TableName AND DataType <> 'float'
                           
		  DELETE FROM #EmployeeAttributeTempTable
	   END
	    					                          
                                                      				
	   IF @ROWID = 0 AND (@PageID = 8 OR @PageID = 9) 					  
	   BEGIN							 
		   UPDATE #TempTableEmployeeAttributeFields
		   SET ControlValue = 
				  CASE WHEN ColumnName =  'ResidingAddressLine1' AND ControlValue = '0' THEN ISNULL(StreetAddress1,'0')  
				 WHEN ColumnName =  'ResidingAddressLine2' AND ControlValue = '0' THEN ISNULL(StreetAddress2,'0')
				 WHEN ColumnName =  'City' AND ControlValue = '0' THEN ISNULL(City, '0') 
				 WHEN ColumnName =  'CountryID' AND ControlValue = '0' THEN CAST(ISNULL(CountryID ,0) AS VARCHAR(4000)) 
				 WHEN ColumnName =  'StateID' AND ControlValue = '0' THEN CAST(ISNULL(StateID,0) AS VARCHAR(4000)) 
				 WHEN ColumnName =  'PostalCode' AND ControlValue = '0' THEN ISNULL(PostalCode , '0')
				 WHEN ColumnName =  'County' AND ControlValue = '0' THEN ISNULL(County,'0') END
		   FROM Employee
		   WHERE ID = @EmployeeID															
	   END;   
	   
	   SET @cnt = @cnt + 1                              				
    END;
		          
         
    ---- FieldSubTypeID update for smalldatatime and drodownlist control
    UPDATE #TempTableEmployeeAttributeFields
    SET FieldSubTypeID = CASE 
	   WHEN ControlType = 1 AND (DataType = 'smalldatetime' OR DataType = 'datetime') THEN 4
	   WHEN ControlType = 3 THEN (SELECT id FROM additionalFieldsubtype WHERE AdditionalFieldTypeID = 3) 
	   ELSE FieldSubTypeID END
	   --,
    --ControlValue = CASE WHEN DataType = 'smalldatetime' OR DataType = 'datetime' THEN
	   --CAST(CONVERT(DATETIME,ControlValue, 103) AS VARCHAR) ELSE ControlValue END
        
    ---- Member Field Rights
    IF ((SELECT CAST(@IsXpressCompany AS BIT)) = 1)
    BEGIN 
	   UPDATE FormFields
		  SET MemberRight = MFR.CompanyAdminMemberRightID
	   FROM #TempTableEmployeeAttributeFields FormFields  
	   LEFT OUTER JOIN MemberFieldRights MFR ON MFR.FieldID = FormFields.FieldID AND MFR.IsAdditionalField = 0	   	   		  	  
    END
    ELSE 
    BEGIN
	   IF @RoleID < 4    
		  UPDATE FormFields
		  SET MemberRight = CASE
			 WHEN @RoleID = 1 THEN MFR.SaMemberRightID			 
			 WHEN @RoleID = 2 THEN MFR.PartnerMemberRightID	   
			 WHEN @RoleID = 3 THEN MFR.BrokerMemberRightID END
		  FROM #TempTableEmployeeAttributeFields FormFields  
		  LEFT OUTER JOIN [WTDEVDBSERVER].OnlineEnrollment.DBO.MemberFieldRights MFR ON MFR.FieldID = FormFields.FieldID AND MFR.IsAdditionalField = 0	   	   
	   ELSE
		  UPDATE FormFields
		  SET MemberRight = CASE
			 WHEN @RoleID = 4 THEN MFR.CompanyAdminMemberRightID		 
			 WHEN @RoleID = 5 THEN MFR.EmployeeMemberRightID END
		  FROM #TempTableEmployeeAttributeFields FormFields  
		  LEFT OUTER JOIN MemberFieldRights MFR ON MFR.FieldID = FormFields.FieldID AND MFR.IsAdditionalField = 0	   	   
    END
    
    ---- Regular Expressions
    UPDATE FormFields
    SET RegularExpression = ValidationExpression
    FROM #TempTableEmployeeAttributeFields FormFields 
    INNER JOIN FieldValidation FV ON FV.FieldID = FormFields.FieldID AND ValidationTypeID = 2  
        
    ---- To fetch Options as per Column Names
    IF EXISTS (SELECT 1 FROM #TempTableEmployeeAttributeFields WHERE ControlType = 3)
    BEGIN	   
	    CREATE TABLE #DDLOptions
	   (
		  ColumnName VARCHAR(500),
		  Options VARCHAR(MAX)
	   )
    
	   DECLARE @DDLColumnName AS VARCHAR(MAX)
	   SELECT @DDLColumnName =
	   STUFF(
	   (
		  SELECT ',' + ColumnName
		  FROM #TempTableEmployeeAttributeFields TInner
		  WHERE TInner.ControlType = TOuter.ControlType
		  FOR XML PATH ('')
	   ),1,1,'') 
	   FROM #TempTableEmployeeAttributeFields TOuter
	   WHERE ControlType = 3
	   GROUP BY ControlType
	   
	   --PRINT @DDLColumnName
	   
	   INSERT INTO #DDLOptions	   
	   EXEC OES_SP_GetOptionsForFieldsWithListControl @PageID, @EmployeeID, @LanguageID, @ROWID, @DDLColumnName
	   	   	   
	   UPDATE FormFields
	   SET Options = DDLOptions.Options
	   FROM #TempTableEmployeeAttributeFields FormFields 
	   INNER JOIN #DDLOptions DDLOptions ON DDLOptions.ColumnName = FormFields.ColumnName
	   
	   --SELECT * FROM #DDLOptions
	   
	   DROP TABLE #DDLOptions
    END       
          
    IF @RequireOnlyFieldInfo <> 1
        BEGIN
            INSERT INTO #TempTableEmployeeAttributeFields(Tname, ColumnName, ControlName, ControlType, DataType, ControlValue, ControlValueDouble, CustomLabel, Controlsize, IsPassword, IsRequired, RegularExpression, AdditionalFieldValidationID, NoOfColumns, NoOfRows, IsWordWrap, isMultiSelect, URL, Width, Height, Position, MediaTypeID, Options, FieldNote, ComponentSizeID, FieldSubTypeID, SequenceNo, IsAdditionalField, MemberRight)
            EXEC OES_SP_Get_FormBuilderAttributeFields 
                 @PageID, 
                 @EmployeeID, 
                 @LanguageID, 
                 @CompanyID, 
                 @RoleID, 
                 1, 
                 @ROWID, 
                 0, 
                 0, 
                 @EffectiveDate
            -------------------------------------------------------------------------------------------------------------------------------------  
            IF EXISTS (SELECT Options FROM #TempTableEmployeeAttributeFields WHERE Options LIKE '%^0%' AND ControlValue IS NULL AND ControlType = 3)
		  BEGIN
			 UPDATE #TempTableEmployeeAttributeFields
			 SET ControlValue = ' '
			 WHERE ControlType = 3
				  AND ControlValue IS NULL
				  AND Options LIKE '%^0%';
		  END;

            UPDATE #TempTableEmployeeAttributeFields
		  SET ControlValue = '0'
            WHERE ColumnName <> 'IsTobaccoUser'
                  AND ControlValue IS NULL
                  AND Datatype <> 'Float';
            
            UPDATE #TempTableEmployeeAttributeFields
            SET ControlValueDouble = '0.0'
            WHERE Datatype = 'Float'
                  AND ControlValueDouble IS NULL;

            UPDATE #TempTableEmployeeAttributeFields
		  SET Controlname = REPLACE(ControlName, ' ', '')
            WHERE CHARINDEX(' ', ControlName, 1) > 0;
            
            UPDATE #TempTableEmployeeAttributeFields
            SET Controlname = REPLACE(ControlName, '''', '')
            WHERE CHARINDEX('''', ControlName, 1) > 0;
            
            UPDATE #TempTableEmployeeAttributeFields
            SET Controlname = REPLACE(ControlName, '`', '')
            WHERE CHARINDEX('`', ControlName, 1) > 0;
            
            UPDATE #TempTableEmployeeAttributeFields
            SET Controlname = REPLACE(ControlName, '~', '')
            WHERE CHARINDEX('~', ControlName, 1) > 0;
            
            UPDATE #TempTableEmployeeAttributeFields
            SET Controlname = REPLACE(ControlName, '!', '')
            WHERE CHARINDEX('!', ControlName, 1) > 0;
            
            UPDATE #TempTableEmployeeAttributeFields
            SET Controlname = REPLACE(ControlName, '@', '')
            WHERE CHARINDEX('@', ControlName, 1) > 0;
            
            UPDATE #TempTableEmployeeAttributeFields
            SET Controlname = REPLACE(ControlName, '#', '')
            WHERE CHARINDEX('#', ControlName, 1) > 0;
            
            UPDATE #TempTableEmployeeAttributeFields
            SET Controlname = REPLACE(ControlName, '$', '')
            WHERE CHARINDEX('$', ControlName, 1) > 0;
            
            UPDATE #TempTableEmployeeAttributeFields
            SET Controlname = REPLACE(ControlName, '%', '')
            WHERE CHARINDEX('%', ControlName, 1) > 0;
            
            UPDATE #TempTableEmployeeAttributeFields
            SET Controlname = REPLACE(ControlName, '^', '')
            WHERE CHARINDEX('^', ControlName, 1) > 0;
            
            UPDATE #TempTableEmployeeAttributeFields
            SET Controlname = REPLACE(ControlName, '&', '')
            WHERE CHARINDEX('&', ControlName, 1) > 0;
            
            UPDATE #TempTableEmployeeAttributeFields
            SET Controlname = REPLACE(ControlName, '*', '')
            WHERE CHARINDEX('*', ControlName, 1) > 0;
            
            UPDATE #TempTableEmployeeAttributeFields
            SET Controlname = REPLACE(ControlName, '(', '')
            WHERE CHARINDEX('(', ControlName, 1) > 0;
            UPDATE #TempTableEmployeeAttributeFields
		  SET Controlname = REPLACE(ControlName, ')', '')
            WHERE CHARINDEX(')', ControlName, 1) > 0;
            
            UPDATE #TempTableEmployeeAttributeFields
            SET 
                  Controlname = REPLACE(ControlName, '[', '')
            WHERE CHARINDEX('[', ControlName, 1) > 0;
            UPDATE #TempTableEmployeeAttributeFields
              SET 
                  Controlname = REPLACE(ControlName, ']', '')
            WHERE CHARINDEX(']', ControlName, 1) > 0;
            UPDATE #TempTableEmployeeAttributeFields
              SET 
                  Controlname = REPLACE(ControlName, '\', '')
            WHERE CHARINDEX('\', ControlName, 1) > 0;
            UPDATE #TempTableEmployeeAttributeFields
              SET 
                  Controlname = REPLACE(ControlName, '/', '')
            WHERE CHARINDEX('/', ControlName, 1) > 0;
            UPDATE #TempTableEmployeeAttributeFields
              SET 
                  Controlname = REPLACE(ControlName, '|', '')
            WHERE CHARINDEX('|', ControlName, 1) > 0;
            UPDATE #TempTableEmployeeAttributeFields
              SET 
                  Controlname = REPLACE(ControlName, '?', '')
            WHERE CHARINDEX('?', ControlName, 1) > 0;
            UPDATE #TempTableEmployeeAttributeFields
              SET 
                  Controlname = REPLACE(ControlName, '-', '')
            WHERE CHARINDEX('-', ControlName, 1) > 0;
            UPDATE #TempTableEmployeeAttributeFields
              SET 
                  Controlname = REPLACE(ControlName, '.', '')
            WHERE CHARINDEX('.', ControlName, 1) > 0;
            UPDATE #TempTableEmployeeAttributeFields
              SET 
                  Controlname = REPLACE(ControlName, ',', '')
            WHERE CHARINDEX(',', ControlName, 1) > 0;
            UPDATE #TempTableEmployeeAttributeFields
              SET 
                  Controlname = REPLACE(ControlName, ';', '')
            WHERE CHARINDEX(';', ControlName, 1) > 0;
            UPDATE #TempTableEmployeeAttributeFields
              SET 
                  Controlname = REPLACE(ControlName, '{', '')
            WHERE CHARINDEX('{', ControlName, 1) > 0;
            UPDATE #TempTableEmployeeAttributeFields
              SET 
                  Controlname = REPLACE(ControlName, '}', '')
            WHERE CHARINDEX('}', ControlName, 1) > 0;
            UPDATE #TempTableEmployeeAttributeFields
              SET 
                  Controlname = REPLACE(ControlName, '_', '')
            WHERE CHARINDEX('_', ControlName, 1) > 0
                  AND (Controlname != 'txtSTSUI_SDIMaximum'
                       AND ControlName != 'txtSTSUI_SDITaxWithHolding');

        END;
        
    ----DELETE FROM TempNewOutput
    ----INSERT INTO TempNewOutput    
    SELECT *      
    FROM #TempTableEmployeeAttributeFields
    ORDER BY Position, SequenceNo
    
    DROP TABLE #TempTableEmployeeAttributeFields;
    DROP TABLE #EmployeeAttributeTempTable;
    DROP TABLE #EmployeeAttributeTempTableFloat;
    DROP TABLE #TablesUsed    

    IF @RequireOnlyFieldInfo <> 1
    BEGIN
        IF @PageID = 8 OR @PageID = 9
        BEGIN

            SELECT @RequireSpouseSSN = RequireSpouseSSN, @RequireChildSSN = RequireChildSSN, @SpouseAgeForSSN_Years = SpouseAgeForSSN_Years, @SpouseAgeForSSN_Month = SpouseAgeForSSN_Month, @ChildAgeForSSN_Years = ChildAgeForSSN_Years, @ChildAgeForSSN_Month = ChildAgeForSSN_Month
            FROM CompanyInfo;

            SET @RequireDependentSSN = CAST(@RequireSpouseSSN AS VARCHAR(1))+','+CAST(@RequireChildSSN AS VARCHAR(1))+',';

            IF @SpouseAgeForSSN_Years IS NULL
            BEGIN
                SET @RequireDependentSSN = @RequireDependentSSN+'0'+',';
            END;
            ELSE
            BEGIN
                SET @RequireDependentSSN = @RequireDependentSSN+CAST(@SpouseAgeForSSN_Years AS VARCHAR(3))+',';
            END;

            IF @SpouseAgeForSSN_Month IS NULL
            BEGIN
                SET @RequireDependentSSN = @RequireDependentSSN+'0'+',';
            END;
            ELSE
            BEGIN
                SET @RequireDependentSSN = @RequireDependentSSN+CAST(@SpouseAgeForSSN_Month AS VARCHAR(2))+',';
            END;

            IF @ChildAgeForSSN_Years IS NULL
            BEGIN
                SET @RequireDependentSSN = @RequireDependentSSN+'0'+',';
            END;
            ELSE
            BEGIN
                SET @RequireDependentSSN = @RequireDependentSSN+CAST(@ChildAgeForSSN_Years AS VARCHAR(3))+',';
            END;

            IF @ChildAgeForSSN_Month IS NULL
            BEGIN
                SET @RequireDependentSSN = @RequireDependentSSN+'0';
            END;
            ELSE
            BEGIN
                SET @RequireDependentSSN = @RequireDependentSSN + CAST(@ChildAgeForSSN_Month AS VARCHAR(2));
            END;       			
        END;

        SELECT @RequireDependentSSN RequireDependentSSN;

        IF @RoleID > 3
        BEGIN    
            --SELECT DisplayPopUP as DISPLAYPOPUP, isnull(PopUPMessageDescription,'' ) as PopUPMessageDescription from FormatMemberDocument where PageID =@PageID and RoleID = @RoleID;    
            SELECT DisplayPopUP AS DISPLAYPOPUP, ISNULL(PopUPMessageDescription, '') AS PopUPMessageDescription, ISNULL(DescriptionEN, '') AS DescriptionEN, ISNULL(DescriptionFR, '') AS DescriptionFR, ISNULL(DescriptionES, '') AS DescriptionES
            FROM FormatMemberDocument
            WHERE PageID = @PageID
                  AND RoleID = @RoleID;
        END;
        ELSE
        BEGIN
            SELECT DisplayPopUP AS DISPLAYPOPUP, ISNULL(PopUPMessageDescription, '') AS PopUPMessageDescription, ISNULL(DescriptionEN, '') AS DescriptionEN, ISNULL(DescriptionFR, '') AS DescriptionFR, ISNULL(DescriptionES, '') AS DescriptionES
            FROM [WTDEVDBSERVER].OnlineEnrollment.DBO.FormatMemberDocument
            WHERE PageID = @PageID
                  AND RoleID = @RoleID;
        END;

        SELECT CASE
                   WHEN @LanguageID = 1 THEN ISNULL(dbo.FieldsLabel_Multilingual.FieldLabelEN, '')
                   WHEN @LanguageID = 2 THEN ISNULL(dbo.FieldsLabel_Multilingual.FieldLabelSP, '')
                   WHEN @LanguageID = 3 THEN ISNULL(dbo.FieldsLabel_Multilingual.FieldLabelFR, '')
               END AS PageTitle
        FROM FieldsLabel_Multilingual
        WHERE DestinationFieldID = @PageID
              AND DestinationTableRefID = 104
              AND IsAdditionalField = 0;    

        --Added to get IsMailID Required flag to generate UserID    
        SELECT ISNULL(IsEmailID, 0) AS IsEmailIDRequired
        FROM UserNameCredentials;

        EXEC OES_SP_GetValidationMessagesForFields 
             @PageID, 
             @RoleID, 
             @LanguageID, 
             @CompanyID;
             
        DECLARE @Result BIT= 0;
        EXEC @Result = OES_UDF_GetIsFieldEligibilityOnSamePage 
             @PageID = @PageID;
        SELECT @Result AS IsApplyTrue;

    END;    
END;    