CLIENT SUPPORT » HINO Bugs

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