2012 Business Rule Issue involving blanks/nulls

Question

I'm running into an issue with a business rule (See logic below).  The rule works as long as there is a value in the DBA.attribute.  However if the value is null/blank this does not overwrite a previous value.   

RuleConditionText

HRNumber is equal to DBA.HRNumber.Code

RuleActionText

HRJobCode equals DBA.HRNumber.JobCode

HRJobTitle equals DBA.HRNumber.JobDesc

HRCostCenter equals DBA.HRNumber.CostCenter

XNumber equals DBA.HRNumber.XNumber

TermDate equals DBA.HRNumber.TermDate

SSNumber equals DBA.HRNumber.SSNumber

PhoneNumber equals DBA.HRNumber.PhoneNumber

Email equals DBA.HRNumber.Email

City equals DBA.HRNumber.City

State equals DBA.HRNumber.State

Answer 1

In the business rule stored procedure I've pinpointed the followings as to what may be the issue.  It looks as if the ImportType = 0 thus omitting the NULLS.

The ImportType = @stagingMergeOverwrite param.

DECLARE @ProcessOptionDefault INT = 1;

DECLARE @ProcessOptionChangeValue INT = 2;

DECLARE @ProcessOptionAssignments INT = @ProcessOptionDefault | @ProcessOptionChangeValue;

DECLARE @ProcessOptionValidation INT = 4;

DECLARE @ProcessOptionUI INT = 8;

DECLARE @ProcessOptionExternalAction INT = 16;

DECLARE @ProcessOptionLogging INT = 128;

DECLARE @doAssignments BIT = 0;

DECLARE @doValidation BIT = 0;

DECLARE @doValidationLogging BIT = 0;

DECLARE @doExternalAction BIT = 0;

DECLARE @stagingMergeOverwrite INT = 0;

DECLARE @ValidationStatus_NewAwaitingValidation INT = 0;

DECLARE @ValidationStatus_AwaitingRevalidation INT = 4;

DECLARE @ValidationStatus_ValidationFailed INT = 2;

DECLARE @ValidationStatus_AwaitingDependentRevalidation INT = 5;I'm trying to locate the XML file that creates the BusinessRule procedure?

Answer 2

Spent some time looking into without much success.  I updated the @stagingMergeOverwrite to 2 and that sort of worked however it did cause an issue so I republished the rules and in doing so the variable reset to 0.  I was hoping that I could locate the, i'm guessing, either the stored procedure or XML script that creates the business rule stored procedure when you publish a rule.  I was thinking, if the @stagingMergeOverwrite = 2 then it would stage the null values?

--This is the variable that is used as the ImportType when the rule seeds the Staging table .
---------
DECLARE @stagingMergeOverwrite 
INT = 0;
---------
-- Seed staging table with Code values.
INSERT INTO stg.[OfficerCodeData_Leaf]
        (BatchTag, Batch_ID, ImportType, Code)
  
SELECT DISTINCT @BatchTag, @NewBatchID,  @stagingMergeOverwrite, brstg.MemberCode
         FROM #BRAssignmentStaging brstg
         WHERE brstg.IsChanged = 1

To explain the rule in the original post.  We have employee numbers assigned to codes.  When an employee leaves the Employee Number {00000} assigned to a code is reset and all of the attributes are updated.  Because '00000' is just a default, 99% of the attributes are NULL and this clears out the code until a new Employee is assigned to it.

Out of ideas on this one...except to load a space " " for the NULLS so the rule reads something?