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

Spouse enrolled in macro not working

    XMLWordPrintable

    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
    • Code Reviewed By:
      Amruta Lohiya

      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. Stage_SpouseEnrolledIn_2.jpg
          232 kB
          Priya Dhamande
        2. Stage_SpouseEnrolledIn_1.jpg
          201 kB
          Priya Dhamande
        3. Prod_SpouseEnrolledIn_3.jpg
          212 kB
          Priya Dhamande
        4. Prod_SpouseEnrolledIn_2.jpg
          195 kB
          Priya Dhamande
        5. Prod_SpouseEnrolledIn_1.jpg
          218 kB
          Priya Dhamande
        6. LB_SpouseEnrollIn_3.jpg
          202 kB
          Priya Dhamande
        7. LB_SpouseEnrollIn_2.jpg
          191 kB
          Priya Dhamande
        8. LB_SpouseEnrollIn_1.jpg
          194 kB
          Priya Dhamande
        9. EnrolledinPlanID.png
          19 kB
          Jaideep Vinchurkar

          Issue Links

            Activity

              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: