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

            jaideep.vinchurkar Jaideep Vinchurkar (Inactive) created issue -
            jaideep.vinchurkar Jaideep Vinchurkar (Inactive) made changes -
            Field Original Value New Value
            Attachment EnrolledinPlanID.png [ 31376 ]
            jaideep.vinchurkar Jaideep Vinchurkar (Inactive) made changes -
            Assignee Jaideep Vinchurkar [ jaideep.vinchurkar ] Amruta Lohiya [ amruta.baheti ]
            amruta.lohiya Amruta Lohiya made changes -
            Assignee Amruta Lohiya [ amruta.baheti ] Vinanti Yadav [ vinanti.yadav ]
            vinanti.yadav Vinanti Yadav (Inactive) made changes -
            Status Open [ 1 ] In Development [ 10007 ]
            vinanti.yadav Vinanti Yadav (Inactive) made changes -
            Item State Parent values: Development(10200)Level 1 values: In Analysis(10204)
            vinanti.yadav Vinanti Yadav (Inactive) made changes -
            Developer Vinanti Yadav [ vinanti.yadav ]
            vinanti.yadav Vinanti Yadav (Inactive) made changes -
            Code Review Date 05/Nov/2016
            Code Reviewed By Amruta Lohiya [ 11906 ]
            vinanti.yadav Vinanti Yadav (Inactive) made changes -
            Item State Parent values: Development(10200)Level 1 values: In Analysis(10204) Parent values: Development(10200)Level 1 values: Ready for Local Testing(10209)
            vinanti.yadav Vinanti Yadav (Inactive) made changes -
            Assignee Vinanti Yadav [ vinanti.yadav ] Rakesh Roy [ rakeshr ]
            vinanti.yadav Vinanti Yadav (Inactive) made changes -
            Item State Parent values: Development(10200)Level 1 values: Ready for Local Testing(10209) Parent values: LB QA(10201)Level 1 values: LB Deployed(11600)
            rakeshr Rakesh Roy (Inactive) made changes -
            Status In Development [ 10007 ] Local Testing [ 10200 ]
            rakeshr Rakesh Roy (Inactive) made changes -
            Assignee Rakesh Roy [ rakeshr ] Priya Dhamande [ priya.dhamande ]
            priya.dhamande Priya Dhamande (Inactive) made changes -
            Item State Parent values: LB QA(10201)Level 1 values: LB Deployed(11600) Parent values: LB QA(10201)Level 1 values: In Testing(10210)
            priya.dhamande Priya Dhamande (Inactive) made changes -
            Attachment LB_SpouseEnrollIn_3.jpg [ 31453 ]
            Attachment LB_SpouseEnrollIn_2.jpg [ 31454 ]
            Attachment LB_SpouseEnrollIn_1.jpg [ 31455 ]
            priya.dhamande Priya Dhamande (Inactive) made changes -
            Item State Parent values: LB QA(10201)Level 1 values: In Testing(10210) Parent values: LB QA(10201)Level 1 values: Ready for Stage(10213)
            vinanti.yadav Vinanti Yadav (Inactive) made changes -
            Dev Due Date 07/Nov/2016
            vinanti.yadav Vinanti Yadav (Inactive) made changes -
            Dev Due Date 07/Nov/2016 06/Nov/2016
            vinanti.yadav Vinanti Yadav (Inactive) made changes -
            Dev Due Date 06/Nov/2016 05/Nov/2016
            khandu.kshirsagar Khandu Kshirsagar (Inactive) made changes -
            Item State Parent values: LB QA(10201)Level 1 values: Ready for Stage(10213) Parent values: Stage QA(10202)Level 1 values: Stage Deployed(11602)
            priya.dhamande Priya Dhamande (Inactive) made changes -
            Item State Parent values: Stage QA(10202)Level 1 values: Stage Deployed(11602) Parent values: Stage QA(10202)Level 1 values: In Testing(10214)
            priya.dhamande Priya Dhamande (Inactive) made changes -
            Attachment Stage_SpouseEnrolledIn_2.jpg [ 31504 ]
            Attachment Stage_SpouseEnrolledIn_1.jpg [ 31505 ]
            priya.dhamande Priya Dhamande (Inactive) made changes -
            Status Local Testing [ 10200 ] Stage Testing [ 10201 ]
            priya.dhamande Priya Dhamande (Inactive) made changes -
            Link This issue relates to WT-6064 [ WT-6064 ]
            priya.dhamande Priya Dhamande (Inactive) made changes -
            Item State Parent values: Stage QA(10202)Level 1 values: In Testing(10214) Parent values: Stage QA(10202)Level 1 values: Ready for Production(10217)
            ashwin.wankhede Ashwin Wankhede (Inactive) made changes -
            Item State Parent values: Stage QA(10202)Level 1 values: Ready for Production(10217) Parent values: Production QA(10203)Level 1 values: Production Deployed(10221)
            priya.dhamande Priya Dhamande (Inactive) made changes -
            Item State Parent values: Production QA(10203)Level 1 values: Production Deployed(10221) Parent values: Stage QA(10202)Level 1 values: In Testing(10214)
            priya.dhamande Priya Dhamande (Inactive) made changes -
            Item State Parent values: Stage QA(10202)Level 1 values: In Testing(10214) Parent values: Production QA(10203)
            priya.dhamande Priya Dhamande (Inactive) made changes -
            Item State Parent values: Production QA(10203) Parent values: Production QA(10203)Level 1 values: In Testing(10218)
            priya.dhamande Priya Dhamande (Inactive) made changes -
            Status Stage Testing [ 10201 ] Production Testing [ 10202 ]
            priya.dhamande Priya Dhamande (Inactive) made changes -
            Attachment Prod_SpouseEnrolledIn_3.jpg [ 31615 ]
            Attachment Prod_SpouseEnrolledIn_2.jpg [ 31616 ]
            Attachment Prod_SpouseEnrolledIn_1.jpg [ 31617 ]
            priya.dhamande Priya Dhamande (Inactive) made changes -
            Item State Parent values: Production QA(10203)Level 1 values: In Testing(10218) Parent values: Production QA(10203)
            priya.dhamande Priya Dhamande (Inactive) made changes -
            Resolution Fixed [ 1 ]
            Status Production Testing [ 10202 ] Production Complete [ 10028 ]
            priya.dhamande Priya Dhamande (Inactive) made changes -
            Status Production Complete [ 10028 ] Closed [ 6 ]
            satyap Satya made changes -
            Environment_New Production [ 18442 ]

              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: