EXEC SQL.
    CONNECT TO :con_name.
ENDEXEC.

IF sy-subrc = 0.
    WRITE: / "Successfully connected".
ENDIF.

ALTER PROCEDURE [dbo].[R3PersonalList]

@StartDate varchar(20) = '',
@EndDate varchar(20) = '',
@_PERNR varchar(20) = ''


AS

-- Метка в саплагоне должна быть ******** !!!   ip ********
SET NOCOUNT ON


--Declare  vDate int
--select sDate = Convert(varchar(200),GetDate(),112)

  DECLARE @object int
  DECLARE @hr int
  DECLARE @src varchar(255), @desc varchar(255)
  EXEC @hr = sp_OACreate 'SAP.Functions', @object OUT

  IF @hr <> 0
  BEGIN
    EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
    SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
        GOTO SPEXIT
  END
  else
  begin
    declare @hprop int
    EXEC @hr = sp_OAGetProperty @object,'Connection', @hprop OUT
    EXEC @hr = sp_OASetProperty @hprop, 'Destination', '*****'
    EXEC @hr = sp_OASetProperty @hprop, 'Client', '010'
    EXEC @hr = sp_OASetProperty @hprop, 'Language', '8'
    EXEC @hr = sp_OASetProperty @hprop, 'User', '*****'
    EXEC @hr = sp_OASetProperty @hprop, 'Password', '*****'
 
    declare @ret int -- для проверки рез. Logon
    EXEC @hr = sp_OAMethod @hprop, 'Logon', @ret OUT , 1, True
    IF @hr <> 0
    BEGIN
      EXEC sp_OAGetErrorInfo @hprop
      GOTO SPEXIT
    END
   
    if @ret <> 1
     BEGIN
      Raiserror('Logon failed', 16, 1)
      EXEC @hr = sp_OADestroy @object
      IF @hr <> 0
      BEGIN
      EXEC sp_OAGetErrorInfo @object
      GOTO SPEXIT
     END
     END
 
    declare @Funct int
    EXEC @hr = sp_OAMethod @object, 'Add', @Funct OUT, 'Z_HR_LIST_WERKS'
 
 
    declare @Collection int
    EXEC @hr = sp_OAGetProperty @Funct, 'Exports',  @Collection OUT

    declare @Item int
    declare @RowCount int
    declare @Name varchar(100)
    declare @i int
    set @i = 1

    EXEC @hr = sp_OAGetProperty @Collection, 'Count', @RowCount OUT


  --declare @s varchar(20)
--  EXEC @hr = sp_OAGetProperty @Item, 'name', @S OUT
--  Select 's' = @s
   

    EXEC @hr = sp_OAGetProperty @Collection, 'Item', @Item OUT, 1
    EXEC @hr = sp_OASetProperty @Item, 'Value', @StartDate
    EXEC @hr = sp_OAGetProperty @Collection, 'Item', @Item OUT, 2
    EXEC @hr = sp_OASetProperty @Item, 'Value', @EndDate
    EXEC @hr = sp_OAGetProperty @Collection, 'Item', @Item OUT, 3
    EXEC @hr = sp_OASetProperty @Item, 'Value', @_PERNR
    EXEC @hr = sp_OAGetProperty @Collection, 'Item', @Item OUT, 4
    EXEC @hr = sp_OASetProperty @Item, 'Value', 'BL04'



    declare @Exports int
    EXEC @hr = sp_OAMethod @Funct, 'Call'

    declare @Tables int
    EXEC @hr = sp_OAGetProperty @Funct, 'Tables', @Tables OUT

    declare @Items int
    EXEC @hr = sp_OAGetProperty @Tables, 'Item', @Items OUT, 1

    EXEC @hr = sp_OAGetProperty @Items, 'RowCount', @RowCount OUT


--


    declare @PERNR int
    declare @ENAME varchar(100)
    declare @ORGEH int
    declare @ZSTEXT_O varchar(100)
    declare @PLANS int
    declare @ZSTEXT_S varchar(100)
    declare @ENDDA datetime
    declare @TRFST varchar(2)
    declare @STELL int
    declare @STELL_NAME varchar(50)

    create table  #tValues
     (
      ID int IDENTITY(1, 1) NOT NULL,
      PERNR int,
      ENAME varchar(100),
      ORGEH int,
      ZSTEXT_O varchar(100),
      PLANS int,
      ZSTEXT_S varchar(100),
      ENDDA datetime,
      TRFST varchar(10),
      STELL int,
      STELL_NAME varchar(100)
      )


    set @i = 1
    while (@i <= @RowCount)
    begin
      EXEC @hr = sp_OAGetProperty @Items, 'Cell', @PERNR OUT, @i, 'PERNR'
      EXEC @hr = sp_OAGetProperty @Items, 'Cell', @ENAME OUT, @i, 'ENAME'
      EXEC @hr = sp_OAGetProperty @Items, 'Cell', @ORGEH OUT, @i, 'ORGEH'
      EXEC @hr = sp_OAGetProperty @Items, 'Cell', @ZSTEXT_O OUT, @i, 'ZSTEXT_O'
      EXEC @hr = sp_OAGetProperty @Items, 'Cell', @PLANS OUT, @i, 'PLANS'
      EXEC @hr = sp_OAGetProperty @Items, 'Cell', @ZSTEXT_S OUT, @i, 'ZSTEXT_S'
      EXEC @hr = sp_OAGetProperty @Items, 'Cell', @ENDDA OUT, @i, 'ENDDA'

      EXEC @hr = sp_OAGetProperty @Items, 'Cell', @TRFST OUT, @i, 'TRFST'
      EXEC @hr = sp_OAGetProperty @Items, 'Cell', @STELL OUT, @i, 'STELL'
      EXEC @hr = sp_OAGetProperty @Items, 'Cell', @STELL_NAME OUT, @i, 'STELL_NAME'


if @PERNR <>0 
BEGIN

      insert into #tValues(PERNR, ENAME, ORGEH,ZSTEXT_O,PLANS,ZSTEXT_S,ENDDA,TRFST,STELL,STELL_NAME)
        --Values(@PERNR, @ENAME, @ORGEH,@ZSTEXT_O,@PLANS,@ZSTEXT_S,@ENDDA,@TRFST,@STELL,@STELL_NAME)
          Values(@PERNR, @ENAME, @ORGEH,@ZSTEXT_O,@STELL,@STELL_NAME,@ENDDA,@TRFST,@STELL,@STELL_NAME)
END
      set @i = @i + 1

    end;



--delete  @tValues where --@tValues.ENDDA < (Select Max(t2.ENDDA) from @tValues  t2 where t2.PERNR = t.PERNR)
--delete  from #tValues  where ENDDA < (Select Max(t2.ENDDA) from #tValues t2   where t2.PERNR =PERNR)

IF @_PERNR <>''
BEGIN
--Update #tValues Set ENDDA = NULL  where YEAR(ENDDA) = 9999
SELECT   *  FROM     #tValues  Order by ENDDA desc --desc --where ENDDA = NULL Order by desc
EXEC @hr = sp_OADestroy @object
  IF @hr <> 0
  BEGIN
    EXEC sp_OAGetErrorInfo @object
    GOTO SPEXIT
  END
GOTO SPEXIT
END

--==================================================================================================
--[Синхронизация с таблицей Personal]

--   Select * from @tValues where PERNR = 8818
-- Select count(*) from @tValues


Select @I = 0
Declare @iCount int
Select @iCount = 0
Declare @inPersonal int
select @inPersonal = 0

Select @iCount = (Select count(*) from #tValues)

WHILE @I<=@iCount
BEGIN

    Select @PERNR     =(Select isNull(PERNR,0) from #tValues where ID = @i )
    Select @ENAME     =(Select ENAME from #tValues where ID = @i )
    Select @ORGEH     =(Select ORGEH from #tValues where ID = @i )
    Select @ZSTEXT_O  =(Select ZSTEXT_O  from #tValues where ID = @i )
    Select @PLANS     =(Select PLANS from #tValues where ID = @i )
    Select @ZSTEXT_S  =(Select ZSTEXT_S from #tValues where ID = @i )
    Select @ENDDA     =(Select ENDDA from #tValues where ID = @i )

    Select @TRFST          =(Select TRFST from #tValues where ID = @i )
    Select @STELL          =(Select STELL from #tValues where ID = @i )
    Select @STELL_NAME     =(Select STELL_NAME from #tValues where ID = @i )


if Year(@ENDDA) = 9999 select @ENDDA = null

IF (Select Count(isNull(PERNR,0)) from Personal where PERNR = @PERNR) = 0
   BEGIN
      insert into Personal(PERNR, ENAME, ORGEH,ZSTEXT_O,PLANS,ZSTEXT_S,ENDDA,TRFST,STELL,STELL_NAME)
        Values(@PERNR, @ENAME, @ORGEH,@ZSTEXT_O,@PLANS,@ZSTEXT_S,@ENDDA,@TRFST,@STELL,@STELL_NAME)
   END
   ELSE
   BEGIN
        UPDATE Personal
        SET
           ENAME    =     @ENAME,
           ORGEH    =     @ORGEH,
           ZSTEXT_O =     @ZSTEXT_O,
           PLANS    =     @PLANS,
           ZSTEXT_S =     @ZSTEXT_S,
           ENDDA    =     @ENDDA,
      TRFST    =     @TRFST,
           STELL    =     @STELL,
           STELL_NAME =   @STELL_NAME
        WHERE PERNR = @PERNR
   END

Select @I = @I+1
END

--==================================================================================================



  EXEC @hr = sp_OADestroy @object
  IF @hr <> 0
  BEGIN
    EXEC sp_OAGetErrorInfo @object
   GOTO SPEXIT
  END
end

SPEXIT:
exec sp_OADestroy @hprop
exec sp_OADestroy @Funct
exec sp_OADestroy @Collection
exec sp_OADestroy @Items
exec sp_OADestroy @Funct
exec sp_OADestroy @Tables
exec sp_OADestroy @RowCount
exec sp_OADestroy @Item

exec sp_OADestroy @Src
exec sp_OADestroy @desc
exec sp_OADestroy @I
exec sp_OADestroy @Item
exec sp_OADestroy @object
exec sp_OADestroy @ret

delete from personal where PERNR is null or PERNR = 0

SET NOCOUNT OFF

SPEXIT:
exec sp_OADestroy @hprop
exec sp_OADestroy @Funct
exec sp_OADestroy @Collection
exec sp_OADestroy @Items
exec sp_OADestroy @Funct
exec sp_OADestroy @Tables
exec sp_OADestroy @RowCount
exec sp_OADestroy @Item

exec sp_OADestroy @Src
exec sp_OADestroy @desc
exec sp_OADestroy @I
exec sp_OADestroy @Item
exec sp_OADestroy @object
exec sp_OADestroy @ret