locked
Awaiting Revalidation doesnt reveal the specific entity members RRS feed

  • Question

  • I have a small issue with validating version. When I validate version I get the awaiting revalidation count as valid count but it does not show any list for all entity members for which validation is awaited. Which makes its quite difficult since we need to browse each entity/DH to check which member has awaiting revalidation and manually validate that member?

    Any thought on this?

    One more point, is there any way to trigger validation for those members which are having awaiting validation status as I can't see any option for this. Whenever I try to validate version it never initiate validation for awaiting validation members and their count is always constant.

    Thursday, November 10, 2011 4:56 AM

Answers

  • This is workaround i am came up with in haste as its was urgent for me ; to fetch get all the entities for which validation is
     awaited . On executing this it displays all the entity along with their revalidation count .
    GO
    create PROCEDURE [mdm].[checkAwaitingRevalidation]
    @Version_ID INT  
    WITH EXECUTE AS N'mds_schema_user'  
    AS  
    BEGIN  
    	SET NOCOUNT ON  
      
    	DECLARE @SQL     NVARCHAR(MAX)  
    	DECLARE @Table   sysname     
    	DECLARE @MemberTypeID INT  
    	DECLARE @tblList TABLE (RowNumber INT IDENTITY (1, 1) PRIMARY KEY CLUSTERED NOT NULL, MemberTypeID INT, ValidationTable sysname COLLATE database_default) --Flag to track if the table is used for validation.  
    	DECLARE @ModelID INT = (SELECT TOP 1 Model_ID FROM mdm.tblModelVersion WHERE ID = @Version_ID);  
        DECLARE @EntityMemberType INT = 1;  
        DECLARE @HierarchyParentMemberType INT = 2;  
        DECLARE @HierarchyMemberType INT = 4;
        declare @validationcheck  int  
        declare @validationcheck_new  int  
        
        create  table #EntityTableRepository
        (
        id int identity,
        EntityTable varchar(max),
        Revalidation_count int
        )
      
    	CREATE TABLE #tblRevalidation_counts   
    	   (  
    	   ValidationStatus_ID    INT,   
    	   ValidationStatus_Count INT  
    	   )  
      
    	--Seed table with validation statuses, each with a count of zero  
    	INSERT INTO #tblRevalidation_counts   
    	SELECT CAST(OptionID AS INT), 0  
    	FROM mdm.tblList  
    	WHERE ListCode = N'lstValidationStatus' AND IsVisible = 1  
    	ORDER BY Seq  
     -- select * from #tblRevalidation_counts
    	--Get the list of member tables  
    	INSERT INTO @tblList  
    	  SELECT @EntityMemberType AS MemberTypeID, EntityTable AS ValidationTable FROM mdm.tblEntity WHERE Model_ID = @ModelID  
    	  UNION   
    	  SELECT @HierarchyParentMemberType, HierarchyParentTable FROM mdm.tblEntity WHERE Model_ID = @ModelID AND HierarchyParentTable IS NOT NULL  
    	  UNION  
    	  SELECT @HierarchyMemberType, HierarchyTable FROM mdm.tblEntity WHERE Model_ID = @ModelID AND HierarchyTable IS NOT NULL  
      
      --select * from @tblList
      
    	----Loop through each member table getting the validation status counts  
    	DECLARE @Counter INT = 1;  
    	DECLARE @MaxCounter INT = (SELECT MAX(RowNumber) FROM @tblList);  
      --print @MemberTypeID
    	WHILE @Counter <= @MaxCounter  
    	BEGIN  
    set @validationcheck= (select ValidationStatus_Count from #tblRevalidation_counts where ValidationStatus_ID = 4)
    	   SELECT   
    	        @MemberTypeID = MemberTypeID  
    	        ,@Table = ValidationTable   
    	   FROM @tblList WHERE [RowNumber] = @Counter  
      
    	   IF (@MemberTypeID <> @HierarchyMemberType)  
    	       SET @SQL =   
    	       N'  
    	       UPDATE #tblRevalidation_counts  
    	       SET ValidationStatus_Count = ValidationStatus_Count + ValCnt  
    	       FROM  
    		      (SELECT ValidationStatus_ID, COUNT(*) AS ValCnt  
    		      FROM  mdm.' + quotename(@Table) + N' a    
    		      WHERE Version_ID = @Version_ID AND Status_ID = 1   
    		      GROUP BY ValidationStatus_ID) b INNER JOIN #tblRevalidation_counts vc ON b.ValidationStatus_ID = vc.ValidationStatus_ID  
    	       '  
    	   ELSE  
        	   --We are only concerned about LevelNumber = -1 from the HR table.  These records need their LevelNumber recalculated.  
    	       SET @SQL =   
    	       N'  
    	       UPDATE #tblRevalidation_counts  
    	       SET ValidationStatus_Count = ValidationStatus_Count + ValCnt  
    	       FROM  
    		      (SELECT 4 ValidationStatus_ID, COUNT(*) AS ValCnt  
    		      FROM  mdm.' + quotename(@Table) + N' a    
    		      WHERE Version_ID = @Version_ID AND Status_ID = 1 AND LevelNumber = -1) b INNER JOIN #tblRevalidation_counts vc ON b.ValidationStatus_ID = vc.ValidationStatus_ID'  
    			
    		 
    	   EXEC sp_executesql @SQL, N'@Version_ID INT', @Version_ID  
    	   
    	   
    	   set @validationcheck_new=(select ValidationStatus_Count from #tblRevalidation_counts where ValidationStatus_ID = 4)
    	   If(@validationcheck<>@validationcheck_new)
    	   begin
    	   
    	    --select * from #tblRevalidation_counts
    	    insert into #EntityTableRepository Values (@Table,@validationcheck_new-@validationcheck)
    
    	  
    	   end
    	   
    	  
            SET @Counter += 1;  
    	END  
      --Return the name of entity 
      declare @max_count int = (select MAX(ID) from #EntityTableRepository)
      declare @counter_e int = (select MIN(ID) from #EntityTableRepository)
      declare @tablee_name varchar(max)
      
      
      while @counter_e < @max_count
      begin
      set @tablee_name = (select EntityTable from #EntityTableRepository where id = @counter_e)
      
      update #EntityTableRepository set EntityTable = (select Name from mdm.tblEntity where EntityTable = @tablee_name) where id = @counter_e
      
      
      set @counter_e = @counter_e+1
      end
      
      
      
      
      
      select * from #EntityTableRepository
    	--Return results  
    	SELECT     
    	   ValidationStatus_ID  AS [ValidationID],  
    	   ValidationStatus_Count AS [Count]  
    	FROM    
    	   #tblRevalidation_counts	  
      
    	DROP TABLE #tblRevalidation_counts  
      
    	SET NOCOUNT OFF  
    END

    • Marked as answer by Challen Fu Thursday, November 17, 2011 12:25 PM
    Thursday, November 10, 2011 9:47 AM