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

            Hide
            jaideep.vinchurkar Jaideep Vinchurkar (Inactive) added a comment -

            As of now, have changed the criteria query manually on production. If you generate it again, it will show you enrolled in macro instead of spouse enrolled in macro

            Show
            jaideep.vinchurkar Jaideep Vinchurkar (Inactive) added a comment - As of now, have changed the criteria query manually on production. If you generate it again, it will show you enrolled in macro instead of spouse enrolled in macro
            Hide
            amruta.lohiya Amruta Lohiya added a comment -

            Jaideep Vinchurkar
            Which company?

            Show
            amruta.lohiya Amruta Lohiya added a comment - Jaideep Vinchurkar Which company?
            Hide
            vinanti.yadav Vinanti Yadav (Inactive) added a comment -

            Please Refer Following 3C's:

            Concern : Spouse enrolled in macro not working.

            Cause : If we use additional criteria with macro Spouse Enrolled In.
            Criteria query for that GenericEligibilityRule is not correct it uses OES_UDF_EnrollIn instead of OES_UDF_SpouseEnrollIn .

            Correction : Code fix is Done.

            Thanks & Regards,
            Vinanti R. Yadav

            Show
            vinanti.yadav Vinanti Yadav (Inactive) added a comment - Please Refer Following 3C's: Concern : Spouse enrolled in macro not working. Cause : If we use additional criteria with macro Spouse Enrolled In. Criteria query for that GenericEligibilityRule is not correct it uses OES_UDF_EnrollIn instead of OES_UDF_SpouseEnrollIn . Correction : Code fix is Done. Thanks & Regards, Vinanti R. Yadav
            Hide
            priya.dhamande Priya Dhamande (Inactive) added a comment - - edited

            Environment: LB
            Company: Asml

            The issue of displaying plan id instead on plan name for Spouse enrolled In field is verified on LB. This issue is resolved on LB. The issue is tested for below pages for new and existing rules both:
            1. Generic Eligibility Page
            2. Rates
            3. Add/Change Plan Design
            4. Form Builder
            5. Export > Template Management > Rule Engine

            Also, other macros like Enrolled In, Enrolled In Sub Plan are also checked for its working.
            For all the pages the plan name displayed as expected after saving. Few screen captures are attached with jira. So, moving jira on Stage.

            Show
            priya.dhamande Priya Dhamande (Inactive) added a comment - - edited Environment: LB Company: Asml The issue of displaying plan id instead on plan name for Spouse enrolled In field is verified on LB. This issue is resolved on LB. The issue is tested for below pages for new and existing rules both: 1. Generic Eligibility Page 2. Rates 3. Add/Change Plan Design 4. Form Builder 5. Export > Template Management > Rule Engine Also, other macros like Enrolled In, Enrolled In Sub Plan are also checked for its working. For all the pages the plan name displayed as expected after saving. Few screen captures are attached with jira. So, moving jira on Stage.
            Hide
            priya.dhamande Priya Dhamande (Inactive) added a comment - - edited

            Environment: Stage
            Company: MBF for Hspl
            The issue of displaying plan id instead on plan name for Spouse enrolled In field is verified on Stage. This issue is resolved on Stage. The issue is tested for below pages for new and existing rules both:
            1. Generic Eligibility Page
            2. Rates
            3. Add/Change Plan Design
            4. Qualifying Events
            5. Export > Template Management > Rule Engine
            6. Credit Pool

            Also, other macros like Enrolled In, Enrolled In Sub Plan are also checked for its working.
            For all the pages the plan name displayed as expected after saving. Few screen captures are attached with jira. So, moving jira on Production.

            Show
            priya.dhamande Priya Dhamande (Inactive) added a comment - - edited Environment: Stage Company: MBF for Hspl The issue of displaying plan id instead on plan name for Spouse enrolled In field is verified on Stage. This issue is resolved on Stage. The issue is tested for below pages for new and existing rules both: 1. Generic Eligibility Page 2. Rates 3. Add/Change Plan Design 4. Qualifying Events 5. Export > Template Management > Rule Engine 6. Credit Pool Also, other macros like Enrolled In, Enrolled In Sub Plan are also checked for its working. For all the pages the plan name displayed as expected after saving. Few screen captures are attached with jira. So, moving jira on Production.
            Hide
            priya.dhamande Priya Dhamande (Inactive) added a comment -

            Environment: Production
            Company: Michael Best Benefits For HSPL

            The issue of displaying plan id instead on plan name for Spouse enrolled In field is verified on Production. This is also checked for Enrolled In and Enrolled In Sub Plan macros. This issue is resolved on Production. The issue is tested for below pages for new and existing rules both:

            1. Generic Eligibility Page
            2. Rates
            3. Add/Change Plan Design
            4. Export > Template Management > Rule Engine

            For all the pages the plan name displayed as expected after saving. Few screen captures are attached with jira. So, moving jira on Production.

            Show
            priya.dhamande Priya Dhamande (Inactive) added a comment - Environment: Production Company: Michael Best Benefits For HSPL The issue of displaying plan id instead on plan name for Spouse enrolled In field is verified on Production. This is also checked for Enrolled In and Enrolled In Sub Plan macros. This issue is resolved on Production. The issue is tested for below pages for new and existing rules both: 1. Generic Eligibility Page 2. Rates 3. Add/Change Plan Design 4. Export > Template Management > Rule Engine For all the pages the plan name displayed as expected after saving. Few screen captures are attached with jira. So, moving jira on Production.

              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: