Overview

You have set the status of old Compensation Plans to “Inactive”, but the individual details within those plans still say “Yes” and employees are getting duplicated payouts on the Comp Detail Plans. You are looking for instructions to get active details within inactive plans changed without having to edit each one individually.

Solution

To resolve this issue, please contact the Support Team to request all active details within inactive compensation plans to be modified in the R1 Database.

<supportagent>

Agents-Only: Escalate the ticket to L2 who will request access to ResourceOne production environment in order to complete the request.

Note: The query contains some customer-specific fields, such as the database name [wnb1092_db]. 

Query Used:

SELECT * --into Compensation_Detail_BK_20210412
  FROM [wnb1092_db].[dbo].[Compensation_Detail]
  where Comp_Plan_ID in (
  SELECT Comp_Plan_ID
  FROM [wnb1092_db].[dbo].[Compensation_Plans]
  where active = 0)
  and active = 1
  --count = 37213


  select * from Compensation_Detail_BK_20210412


  update Compensation_Detail
  set Active = 0
    where Comp_Plan_ID in (
  SELECT Comp_Plan_ID
  FROM [wnb1092_db].[dbo].[Compensation_Plans]
  where active = 0)
  and active = 1
  --count = 37213

  select count (CD.Comp_Detail_ID) QTY,
  --CP.Role_ID,
  R.Role_Type, 
  CP.Name,
   --U.Email1, 
   CP.Mod_Date, CP.Period from Compensation_Detail_BK_20210412 CD
  join Compensation_Plans CP on CD.Comp_Plan_ID = CP.Comp_Plan_ID
  join Roles R on R.Role_ID = CP.Role_ID
  --join Users U on U.User_ID = CP.User_ID
  where CP.Active = 0
  and CP.Role_ID is not null
  group by 
  --CP.Role_ID,
   CP.Name,
   --U.Email1, 
   R.Role_Type, 
   CP.Period, CP.Mod_Date
  order by
   R.Role_Type,
    CP.period, CP.Mod_Date desc

  select count (CD.Comp_Detail_ID) QTY,
   U.Email1, 
  CP.Name,
   CP.Mod_Date, CP.Period from Compensation_Detail_BK_20210412 CD
  join Compensation_Plans CP on CD.Comp_Plan_ID = CP.Comp_Plan_ID
  --join Roles R on R.Role_ID = CP.Role_ID
  join Users U on U.User_ID = CP.User_ID
  where CP.Active = 0
  and CP.User_ID is not null
  group by 
   CP.Name,
   U.Email1, 
   --R.Role_Type, 
   CP.Period, CP.Mod_Date
  order by
   --R.Role_Type,
    CP.period, CP.Mod_Date desc

  select count (CD.Comp_Detail_ID) QTY,
  B.Branch_Name, 
  CP.Name,
   CP.Mod_Date, CP.Period from Compensation_Detail_BK_20210412 CD
  join Compensation_Plans CP on CD.Comp_Plan_ID = CP.Comp_Plan_ID
  join Branches B on B.Branch_ID = CP.Branch_ID
  where CP.Active = 0
  and CP.Branch_ID is not null
  group by 
   CP.Name,
   B.Branch_Name, 
   CP.Period, CP.Mod_Date
  order by
   B.Branch_Name,
    CP.period, CP.Mod_Date desc

</supportagent>