Uploaded image for project: 'WORKTERRA'
  1. WORKTERRA
  2. WT-6020

Spouse enrolled in macro not working

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Critical
    • Resolution: Done
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: BenAdmin
    • Labels:
      None
    • Environment:
      Production
    • Module:
      BenAdmin - Enrollment
    • Reported by:
      Harbinger
    • Item State:
      Production QA
    • Issue Importance:
      Must Have

      Description

      If we use spouse enrolled in macro in additional criteria, while generating criteria query, it uses enrolled in macro. Pleas refer below queries from MBF for 'Spousal surcharge new rule 2017' rule on stage and on production.
      Stage:

      Select ID From( Select ActionType,ID,ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ActionType ASC,DateTImeStamp Desc, EffectiveDate DESC)AS rownum, Case WHEN UseEmployerAddress = 0 THEN PostalCode WHEN UseEmployerAddress = 1 THEN (Select PostalCode From CompanyInfo) END AS PostalCode,UseEmployerAddress, Class1ID,Class2ID,Class3ID,Class4ID,EmployeeStatusID,EffectiveDate,DateTimeStamp From ( Select null as ActionType,ID ,Case WHEN UseEmployerAddress = 0 THEN PostalCode WHEN UseEmployerAddress = 1 THEN (Select PostalCode From CompanyInfo) END AS PostalCode,UseEmployerAddress, Class1ID,Class2ID,Class3ID,Class4ID,EmployeeStatusID,EffectiveDate,DateTimeStamp from Employee WHERE ID IN (Select Items from dbo.Split(@EmployeeID,',')) AND DATEDIFF(DD,EffectiveDate,@EffectiveDate) >=0 UNION ALL Select ActionType,EmployeeID as ID,Case WHEN UseEmployerAddress = 0 THEN PostalCode WHEN UseEmployerAddress = 1 THEN (Select PostalCode From CompanyInfo) END AS PostalCode,UseEmployerAddress, Class1ID,Class2ID,Class3ID,Class4ID,EmployeeStatusID,EffectiveDate,DateTimeStamp from Employee_History WHERE EmployeeID IN (Select Items from dbo.Split(@EmployeeID,',')) AND actiontype = 'M' AND DATEDIFF(DD,EffectiveDate,@EffectiveDate) >=0 ) Employee WHERE ID IN (Select Items from dbo.Split(@EmployeeID,',')) AND DATEDIFF(DD,EffectiveDate,@EffectiveDate) >=0 ) AS E WHERE EmployeeStatusID IN (5,6,7,8,9,1,2,3,4,10) AND (( dbo.OES_UDF_SpouseEnrollIn(E.id,'=',@EffectiveDate,'1')=1 OR dbo.OES_UDF_SpouseEnrollIn(E.id,'=',@EffectiveDate,'2')=1 OR dbo.OES_UDF_SpouseEnrollIn(E.id,'=',@EffectiveDate,'3')=1 OR dbo.OES_UDF_SpouseEnrollIn(E.id,'=',@EffectiveDate,'4')=1 OR dbo.OES_UDF_SpouseEnrollIn(E.id,'=',@EffectiveDate,'5')=1 OR dbo.OES_UDF_SpouseEnrollIn(E.id,'=',@EffectiveDate,'6')=1 OR dbo.OES_UDF_SpouseEnrollIn(E.id,'=',@EffectiveDate,'7')=1 OR dbo.OES_UDF_SpouseEnrollIn(E.id,'=',@EffectiveDate,'8')=1 OR dbo.OES_UDF_SpouseEnrollIn(E.id,'=',@EffectiveDate,'9')=1 OR dbo.OES_UDF_SpouseEnrollIn(E.id,'=',@EffectiveDate,'10')=1 OR dbo.OES_UDF_SpouseEnrollIn(E.id,'=',@EffectiveDate,'11')=1 OR dbo.OES_UDF_SpouseEnrollIn(E.id,'=',@EffectiveDate,'46')=1 OR dbo.OES_UDF_SpouseEnrollIn(E.id,'=',@EffectiveDate,'47')=1 OR dbo.OES_UDF_SpouseEnrollIn(E.id,'=',@EffectiveDate,'48')=1 OR dbo.OES_UDF_SpouseEnrollIn(E.id,'=',@EffectiveDate,'78')=1 OR dbo.OES_UDF_SpouseEnrollIn(E.id,'=',@EffectiveDate,'82')=1 OR dbo.OES_UDF_SpouseEnrollIn(E.id,'=',@EffectiveDate,'80')=1 OR dbo.OES_UDF_SpouseEnrollIn(E.id,'=',@EffectiveDate,'103')=1 OR dbo.OES_UDF_SpouseEnrollIn(E.id,'=',@EffectiveDate,'88')=1 OR dbo.OES_UDF_SpouseEnrollIn(E.id,'=',@EffectiveDate,'89')=1 ) AND (Exists ( Select EmployeeID From ( Select ActionType,ROW_NUMBER() OVER (PARTITION BY EmployeeID,ParentID ORDER BY ActionType ASC , DateTimeStamp Desc, EffectiveDate DESC)AS rownum,EmployeeID,ParentID,EffectiveDate,DateTimeStamp,SpousalSurchargeResponse542131 From ( Select NULL as ActionType,EmployeeID ,ID as ParentID, EffectiveDate,DateTimeStamp,SpousalSurchargeResponse542131 From Employee_FormData Where EmployeeID = E.ID and PageID =131 UNION ALL Select ActionType,EmployeeID ,Employee_FormDataID AS ParentID, EffectiveDate, OriginalDateTimeStamp as DateTimeStamp,SpousalSurchargeResponse542131 From Employee_FormData_History Where EmployeeID = E.ID and ActionType = 'M' and PageID=131 ) AS Inner_Query WHERE DATEDIFF(DD,Inner_Query.EffectiveDate,@EffectiveDate) >=0 ) AS Employee_Other Where rownum = 1 AND Rtrim(Ltrim(replace(SpousalSurchargeResponse542131,' ',''))) = Rtrim(Ltrim(replace('Yes',' ','')))))) AND rownum = 1

      Prod:
      Select ID From( Select ActionType,ID,ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ActionType ASC,DateTImeStamp Desc, EffectiveDate DESC)AS rownum, Case WHEN UseEmployerAddress = 0 THEN PostalCode WHEN UseEmployerAddress = 1 THEN (Select PostalCode From CompanyInfo) END AS PostalCode,UseEmployerAddress, Class1ID,Class2ID,Class3ID,Class4ID,EmployeeStatusID,EffectiveDate,DateTimeStamp From ( Select null as ActionType,ID ,Case WHEN UseEmployerAddress = 0 THEN PostalCode WHEN UseEmployerAddress = 1 THEN (Select PostalCode From CompanyInfo) END AS PostalCode,UseEmployerAddress, Class1ID,Class2ID,Class3ID,Class4ID,EmployeeStatusID,EffectiveDate,DateTimeStamp from Employee WHERE ID IN (Select Items from dbo.Split(@EmployeeID,',')) AND DATEDIFF(DD,EffectiveDate,@EffectiveDate) >=0 UNION ALL Select ActionType,EmployeeID as ID,Case WHEN UseEmployerAddress = 0 THEN PostalCode WHEN UseEmployerAddress = 1 THEN (Select PostalCode From CompanyInfo) END AS PostalCode,UseEmployerAddress, Class1ID,Class2ID,Class3ID,Class4ID,EmployeeStatusID,EffectiveDate,DateTimeStamp from Employee_History WHERE EmployeeID IN (Select Items from dbo.Split(@EmployeeID,',')) AND actiontype = 'M' AND DATEDIFF(DD,EffectiveDate,@EffectiveDate) >=0 ) Employee WHERE ID IN (Select Items from dbo.Split(@EmployeeID,',')) AND DATEDIFF(DD,EffectiveDate,@EffectiveDate) >=0 ) AS E WHERE EmployeeStatusID IN (5,6,7,8,9,1,2,3,4,10) AND (( dbo.OES_UDF_EnrollIn(E.id,'=',@EffectiveDate,'1')=1 OR dbo.OES_UDF_EnrollIn(E.id,'=',@EffectiveDate,'2')=1 OR dbo.OES_UDF_EnrollIn(E.id,'=',@EffectiveDate,'3')=1 OR dbo.OES_UDF_EnrollIn(E.id,'=',@EffectiveDate,'4')=1 OR dbo.OES_UDF_EnrollIn(E.id,'=',@EffectiveDate,'5')=1 OR dbo.OES_UDF_EnrollIn(E.id,'=',@EffectiveDate,'6')=1 OR dbo.OES_UDF_EnrollIn(E.id,'=',@EffectiveDate,'7')=1 OR dbo.OES_UDF_EnrollIn(E.id,'=',@EffectiveDate,'8')=1 OR dbo.OES_UDF_EnrollIn(E.id,'=',@EffectiveDate,'9')=1 OR dbo.OES_UDF_EnrollIn(E.id,'=',@EffectiveDate,'10')=1 OR dbo.OES_UDF_EnrollIn(E.id,'=',@EffectiveDate,'11')=1 OR dbo.OES_UDF_EnrollIn(E.id,'=',@EffectiveDate,'46')=1 OR dbo.OES_UDF_EnrollIn(E.id,'=',@EffectiveDate,'47')=1 OR dbo.OES_UDF_EnrollIn(E.id,'=',@EffectiveDate,'48')=1 OR dbo.OES_UDF_EnrollIn(E.id,'=',@EffectiveDate,'78')=1 OR dbo.OES_UDF_EnrollIn(E.id,'=',@EffectiveDate,'82')=1 OR dbo.OES_UDF_EnrollIn(E.id,'=',@EffectiveDate,'80')=1 OR dbo.OES_UDF_EnrollIn(E.id,'=',@EffectiveDate,'103')=1 OR dbo.OES_UDF_EnrollIn(E.id,'=',@EffectiveDate,'88')=1 OR dbo.OES_UDF_EnrollIn(E.id,'=',@EffectiveDate,'89')=1 ) AND (Exists ( Select EmployeeID From ( Select ActionType,ROW_NUMBER() OVER (PARTITION BY EmployeeID,ParentID ORDER BY ActionType ASC , DateTimeStamp Desc, EffectiveDate DESC)AS rownum,EmployeeID,ParentID,EffectiveDate,DateTimeStamp,SpousalSurchargeResponse542131 From ( Select NULL as ActionType,EmployeeID ,ID as ParentID, EffectiveDate,DateTimeStamp,SpousalSurchargeResponse542131 From Employee_FormData Where EmployeeID = E.ID and PageID =131 UNION ALL Select ActionType,EmployeeID ,Employee_FormDataID AS ParentID, EffectiveDate, OriginalDateTimeStamp as DateTimeStamp,SpousalSurchargeResponse542131 From Employee_FormData_History Where EmployeeID = E.ID and ActionType = 'M' and PageID=131 ) AS Inner_Query WHERE DATEDIFF(DD,Inner_Query.EffectiveDate,@EffectiveDate) >=0 ) AS Employee_Other Where rownum = 1 AND Rtrim(Ltrim(replace(SpousalSurchargeResponse542131,' ',''))) = Rtrim(Ltrim(replace('Yes',' ','')))))) AND rownum = 1

        Attachments

        1. EnrolledinPlanID.png
          EnrolledinPlanID.png
          19 kB
        2. LB_SpouseEnrollIn_1.jpg
          LB_SpouseEnrollIn_1.jpg
          194 kB
        3. LB_SpouseEnrollIn_2.jpg
          LB_SpouseEnrollIn_2.jpg
          191 kB
        4. LB_SpouseEnrollIn_3.jpg
          LB_SpouseEnrollIn_3.jpg
          202 kB
        5. Prod_SpouseEnrolledIn_1.jpg
          Prod_SpouseEnrolledIn_1.jpg
          218 kB
        6. Prod_SpouseEnrolledIn_2.jpg
          Prod_SpouseEnrolledIn_2.jpg
          195 kB
        7. Prod_SpouseEnrolledIn_3.jpg
          Prod_SpouseEnrolledIn_3.jpg
          212 kB
        8. Stage_SpouseEnrolledIn_1.jpg
          Stage_SpouseEnrolledIn_1.jpg
          201 kB
        9. Stage_SpouseEnrolledIn_2.jpg
          Stage_SpouseEnrolledIn_2.jpg
          232 kB

          Issue Links

            Activity

            Transition Time In Source Status Execution Times
            Vinanti Yadav (Inactive) made transition -
            Open In Development
            13h 55m 1
            Rakesh Roy (Inactive) made transition -
            In Development In LB Testing
            1d 11h 2m 1
            Priya Dhamande (Inactive) made transition -
            In LB Testing Stage Testing
            19h 24m 1
            Priya Dhamande (Inactive) made transition -
            Stage Testing In Production Testing
            18h 51m 1
            Priya Dhamande (Inactive) made transition -
            In Production Testing Production Complete
            27m 40s 1
            Priya Dhamande (Inactive) made transition -
            Production Complete Closed
            7s 1

              People

              Assignee:
              priya.dhamande Priya Dhamande (Inactive)
              Reporter:
              jaideep.vinchurkar Jaideep Vinchurkar (Inactive)
              Developer:
              Vinanti Yadav (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:
                Dev Due Date:
                Code Review Date: