-
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
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
- relates to
-
WT-12609 Additional Eligibility | Able to Save blank data if Enrolled In sub Plan is not available
- Closed