DECLARE @last_success AS varchar(14) = '20111001000000' 
SELECT 
  TOP (1) @last_success = dt.LastAt 
FROM 
  PCMEPSVCLOGHEADER AS dt WITH (NOLOCK) 
WHERE 
  dt.SVCType = 'EMP_OM_RECAP' 
  AND dt.Status = 'S' 
ORDER BY 
  dt.LastAt DESC DECLARE @emptable AS TABLE (
    empID varchar(20), 
    posID varchar(20), 
    orgID varchar(20), 
    empStat varchar(20)
  ) INSERT INTO @emptable 
SELECT 
  md2.EmployeeID, 
  md2.Position, 
  Organization, 
  md2.EmployeeStatus 
FROM 
  PHRPA0002 AS md2 WITH (NOLOCK) 
WHERE 
  md2.StartDate <= @now8 
  AND md2.EndDate >= @now8 
  AND md2.ChangeDate >= @last_success 
  AND (
    (
      @object_from <> '' 
      AND @object_to <> '' 
      AND md2.EmployeeID >= @object_from 
      AND md2.EmployeeID <= @object_to
    ) 
    OR (
      @object_from = '' 
      AND @object_to = ''
    )
  ) DECLARE @i1 AS int = 1 DECLARE @countEmp AS int = 0 
SELECT 
  @countEmp = COUNT(*) 
FROM 
  @emptable 
WHERE 
  empStat = '04' 
  AND (
    posID <> '' 
    OR posID IS NOT NULL
  ) DECLARE @empResign AS TABLE (
    id int IDENTITY, 
    empID varchar(20)
  ) INSERT INTO @empResign 
SELECT 
  empID 
FROM 
  @emptable 
WHERE 
  empStat = '04' 
  AND (
    posID <> '' 
    OR posID IS NOT NULL
  ) WHILE (@i1 <= @countEmp) BEGIN DECLARE @emp_nya AS varchar(8) 
SELECT 
  @emp_nya = empID 
FROM 
  @empResign 
WHERE 
  id = @i1 
DELETE FROM 
  @emptable 
WHERE 
  empID = @emp_nya INSERT INTO @emptable 
SELECT 
  TOP(1) md2.EmployeeID, 
  md2.Position, 
  Organization, 
  md2.EmployeeStatus 
FROM 
  PHRPA0002 AS md2 WITH (NOLOCK) 
WHERE 
  md2.EmployeeID = @emp_nya 
  AND md2.EmployeeStatus <> '04' 
ORDER BY 
  md2.EndDate DESC 
SET 
  @i1 = @i1 + 1 END DECLARE @tableOrgID AS TABLE (
    id int IDENTITY, 
    org varchar(20)
  ) INSERT INTO @tableOrgID 
SELECT 
  RelationshipObject 
FROM 
  PHROM0002 AS rel WITH (NOLOCK) 
  INNER JOIN PHROM0001 AS tipe WITH (NOLOCK) ON rel.ObjectID = tipe.ObjectID 
  AND tipe.PositionHead = '1' 
WHERE 
  rel.ObjectClass = 'P' 
  AND rel.RelationshipClass = 'O' 
  AND rel.RelationshipType = '001' 
  AND rel.StartDate <= @now8 
  AND rel.EndDate >= @now8 
  AND rel.ObjectID IN (
    SELECT 
      posID 
    FROM 
      @emptable
  ) DECLARE @i AS int = 1 DECLARE @countOrg AS int = 0 
SELECT 
  @countOrg = COUNT(*) 
FROM 
  @tableOrgID WHILE (@i <= @countOrg) BEGIN DECLARE @org_nya AS varchar(8) 
SELECT 
  @org_nya = org 
FROM 
  @tableOrgID 
WHERE 
  id = @i DECLARE @t TABLE (
    organization varchar(20)
  ) INSERT INTO @t 
SELECT 
  @org_nya INSERT INTO @t 
SELECT 
  rel_obj 
FROM 
  dbo.GetObjectChildAll(@org_nya, 'O', 'O', @now8) INSERT INTO @emptable (empID) 
SELECT 
  DISTINCT om2pe.RelationshipObject AS EmployeeID 
FROM 
  PHROM0002 AS om2 
  LEFT JOIN PHROM0002 AS om2pe ON om2pe.ObjectID = om2.RelationshipObject 
  AND om2pe.ObjectClass = 'P' 
  AND om2pe.RelationshipClass = 'E' 
  AND om2pe.StartDate <= @now8 
  AND om2pe.EndDate >= @now8 
  LEFT JOIN dbo.PHROM0001 AS om1 ON om1.ObjectID = om2.ObjectID 
  AND om1.ObjectClass = 'O' 
  AND om1.StartDate <= @now8 
  AND om1.EndDate >= @now8 
  LEFT JOIN PHROM0002 AS om2pj ON om2pj.ObjectID = om2.RelationshipObject 
  AND om2pj.ObjectID = om2.RelationshipObject 
  AND om2pj.ObjectClass = 'P' 
  AND om2pj.RelationshipClass = 'J' 
  AND om2pj.StartDate <= @now8 
  AND om2pj.EndDate >= @now8 
  LEFT JOIN dbo.PHRPA0001 AS pa1 ON pa1.EmployeeID = om2pe.RelationshipObject 
  AND pa1.StartDate <= @now8 
  AND pa1.EndDate >= @now8 
WHERE 
  om2.ObjectID IN (
    SELECT 
      DISTINCT * 
    FROM 
      @t
  ) 
  AND om2.ObjectClass = 'O' 
  AND om2.RelationshipType = '001' 
  AND om2.RelationshipClass = 'P' 
  AND om2.StartDate <= @now8 
  AND om2.EndDate >= @now8 
  AND om2pe.RelationshipObject <> '' 
SET 
  @i = @i + 1 END 
SELECT 
  DISTINCT empID 
FROM 
  @emptable