locked
How to change the validation status of member in MDS 2012 using Stored Procedure in Custom Workflow RRS feed

  • Question

  • I'm trying to change the validation status in a custom workflow task to pass or fail for a member in MDS 2012. Are their stored rpcedures or maybe even a WF API method to update the validation status?
    Monday, November 19, 2012 1:43 AM

Answers

  • Hi

    you can try to use

    for one member:

    declare @p7 nvarchar(250)  
    set @p7=N''  
    exec mdm.udpMemberStatusSet @User_ID=1,@Version_ID=20,@Entity_ID=37,@MemberType_ID=1,@Member_ID=18,@Status_ID=2,@ReturnEntityName=@p7 output  
    select @p7  
      
     SELECT * FROM mdm.tblAttribute WHERE DomainEntity_ID=37  
      
     or

    EXEC mdm.udpMemberValidationStatusUpdate 1,9,7,1,2
    
    [mdm].[udpMemberValidationStatusUpdate]  
    (  
    	@Version_ID				INTEGER,  
    	@Entity_ID     			INTEGER,  
    	@Member_ID				INTEGER,  
    	@MemberType_ID			TINYINT,  
    	@ValidationStatus_ID	INTEGER   

    and

    for many members

    DECLARE @MemberIdList mdm.IdList;  
    INSERT INTO @MemberIdList  
    VALUES (1), (2), (3)  
      
    EXEC mdm.udpMembersStatusSet @User_ID=1, @Version_ID=20, @Entity_ID=37, @MemberIds = @MemberIds, @Status_ID=2  
      

    with

       @MemberType_Leaf               INT = 1  
            ,@MemberType_Consolidated       INT = 2  
            ,@MemberType_Collection         INT = 3  
            ,@MemberType_Hierarchy          INT = 4  
            ,@MemberType_CollectionMember   INT = 5  
      
            ,@Status_Active                 INT = 1  
            ,@Status_Deactivated            INT = 2  
      
            ,@ValidationStatus_AwaitingRevalidation INT = 4  
      
            ,@TransactionType_ChangeMemberStatus INT = 2  



    Regards,


    Xavier Averbouch
    Microsoft Community Contributor
    SOAT
    If a post answers your question, please click "Mark As Answer" on that post and "Vote as Helpful".





    Friday, February 1, 2013 6:52 PM

All replies

  • I wanted to do something like that recently after staging and wrote this.  It works for me, but obviously is "unofficial" so try it and see if it works for you:

    DECLARE @ModelName NVARCHAR(50) = 'yourModel' 
    DECLARE @Version NVARCHAR(50) = 'yourVersion' 
    DECLARE @UserName NVARCHAR(50) = 'yourUser' 
    DECLARE @Model_ID INT, @User_ID INT, @Version_ID INT, @Entity_ID INT, @MemberIdList INT, @MemberType_ID INT, @ProcessUIRulesOnly BIT
    DECLARE @x mdm.IdList 
    
    -- Lookups / parameter assignment
    SELECT @User_ID = u.Id FROM mdm.tblUser u WHERE u.UserName = @UserName
    SELECT @Model_ID = Model_ID, @Version_ID = ID FROM mdm.viw_SYSTEM_SCHEMA_VERSION WHERE Model_Name = @ModelName AND Name = 'yourModel'
    SELECT @Entity_ID = ID FROM mdm.tblEntity WHERE Model_ID = @MODEL_ID AND name = 'yourMember'
    SET @MemberIdList = 1
    SET @MemberType_ID = 1 
    SET @ProcessUIRulesOnly = 0
    
    EXEC mdm.udpValidateMembers
    	@User_ID = @User_ID,
    	@Version_ID = @Version_ID,
    	@Entity_ID = @Entity_ID,
    	@MemberIdList = @x,	-- Empty TVP = validate all?
    	@MemberType_ID = 1,	-- 1: Leaf	2: Consolidated	3: Collection	4: Hierarchy	5: Collection Member
    	@ProcessUIRulesOnly = 0

    Monday, November 19, 2012 12:38 PM
  • Thanks, but I'm trying to only update one member. I believe this update all the member in the Entity. Also when you do update member how can you get a red "X" to appear beside the member as a fail validation?

    Thanks

    Monday, November 19, 2012 5:48 PM
  • Hi, yes, my intention was to validate all members after a staging exercise.

    If you want to validate one member, then you can populate the TVP which is called @x in my script with the member id you want.  You'll have to look that id up in the database.

    I think the red X will appear if your member violates any of the business rules once you run this proc.

    Let me know if you need some help and I'll try and script out and update for one member - although hopefully you can work it out from the info available to you?

    Monday, November 19, 2012 6:10 PM
  • Hi,

    If you want to validate just one member, you can do it using custom workflow and .Net class library. Below link gives you more details on how to implement this.

    http://johanmachielse.blogspot.co.uk/2011/07/master-data-services-implementing.html 

    Hope this helps.

    • Proposed as answer by Elvis Long Friday, November 23, 2012 8:42 AM
    • Unproposed as answer by rogergd Friday, November 23, 2012 8:22 PM
    Wednesday, November 21, 2012 9:05 AM
  • How do you change the "?" beside the members in the Explorer UI to a Green "check" or Red "!". I know once you appy the business rule which valid the member. I'm using custom workflow task (Business Rule) to valid the member to see if it pass or fail. How can I change the validation status to a pass or failure from within a Custom workflow Task.
    Tuesday, November 27, 2012 6:14 PM
  • How do you change the "?" beside the members in the Explorer UI to a Green "check" or Red "!". I know once you appy the business rule which valid the member. I'm using custom workflow task (Business Rule) to valid the member to see if it pass or fail. How can I change the validation status to a pass or failure from within a Custom workflow Task.

    
    Tuesday, November 27, 2012 6:15 PM
  • Hi Roger

    In your custom workflow task, you could try to call MDS API, then use this code to process validation and get validation errors

     // Create the request object.
                    MDSTestService.ValidationProcessRequest validationProcessRequest = new MDSTestService.ValidationProcessRequest();
                    validationProcessRequest.ValidationProcessCriteria = new ValidationProcessCriteria();
                    validationProcessRequest.ValidationProcessCriteria.ModelId = modelId;
                    validationProcessRequest.ValidationProcessCriteria.EntityId = entityId;
                    validationProcessRequest.ValidationProcessCriteria.VersionId = versionId;
                    validationProcessRequest.ValidationProcessOptions = new ValidationProcessOptions();
                    validationProcessRequest.ValidationProcessOptions.ReturnValidationResults = true;
    
                    // Process validation and get a validation issue.
                    MDSTestService.ValidationProcessResponse validationProcessResponse = clientProxy.ValidationProcess(validationProcessRequest);
                    HandleOperationErrors(validationProcessResponse.OperationResult);
    
                    // Show the validation issue's description. 
                    if (validationProcessResponse.ValidationIssueList.Count > 0)
                    {
                        ValidationIssue validationIssue = validationProcessResponse.ValidationIssueList[0];
                        Console.WriteLine("Validation issue: " + validationIssue.Description);
                    }


    Regards,


    Xavier Averbouch
    Microsoft Community Contributor
    SOAT
    If a post answers your question, please click "Mark As Answer" on that post and "Vote as Helpful".

    Thursday, January 31, 2013 12:26 PM
  • Hi

    you can try to use

    for one member:

    declare @p7 nvarchar(250)  
    set @p7=N''  
    exec mdm.udpMemberStatusSet @User_ID=1,@Version_ID=20,@Entity_ID=37,@MemberType_ID=1,@Member_ID=18,@Status_ID=2,@ReturnEntityName=@p7 output  
    select @p7  
      
     SELECT * FROM mdm.tblAttribute WHERE DomainEntity_ID=37  
      
     or

    EXEC mdm.udpMemberValidationStatusUpdate 1,9,7,1,2
    
    [mdm].[udpMemberValidationStatusUpdate]  
    (  
    	@Version_ID				INTEGER,  
    	@Entity_ID     			INTEGER,  
    	@Member_ID				INTEGER,  
    	@MemberType_ID			TINYINT,  
    	@ValidationStatus_ID	INTEGER   

    and

    for many members

    DECLARE @MemberIdList mdm.IdList;  
    INSERT INTO @MemberIdList  
    VALUES (1), (2), (3)  
      
    EXEC mdm.udpMembersStatusSet @User_ID=1, @Version_ID=20, @Entity_ID=37, @MemberIds = @MemberIds, @Status_ID=2  
      

    with

       @MemberType_Leaf               INT = 1  
            ,@MemberType_Consolidated       INT = 2  
            ,@MemberType_Collection         INT = 3  
            ,@MemberType_Hierarchy          INT = 4  
            ,@MemberType_CollectionMember   INT = 5  
      
            ,@Status_Active                 INT = 1  
            ,@Status_Deactivated            INT = 2  
      
            ,@ValidationStatus_AwaitingRevalidation INT = 4  
      
            ,@TransactionType_ChangeMemberStatus INT = 2  



    Regards,


    Xavier Averbouch
    Microsoft Community Contributor
    SOAT
    If a post answers your question, please click "Mark As Answer" on that post and "Vote as Helpful".





    Friday, February 1, 2013 6:52 PM
  • DECLARE @MemberIdList mdm.IdList; INSERT INTO @MemberIdList VALUES (1), (2), (3) EXEC mdm.udpMembersStatusSet @User_ID=1, @Version_ID=20, @Entity_ID=37, @MemberIds = @MemberIds, @Status_ID=2

    raises the following error

    Msg 206, Level 16, State 2, Procedure udpMembersStatusSet, Line 0 [Batch Start Line 55]
    Operand type clash: IdList is incompatible with MemberId

    Tuesday, May 2, 2017 8:16 PM