-- Метка в саплагоне должна быть ******** !!! 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
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 [email protected] < (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
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum You cannot attach files in this forum You can download files in this forum
All product names are trademarks of their respective companies. SAPNET.RU websites are in no way affiliated with SAP AG. SAP, SAP R/3, R/3 software, mySAP, ABAP, BAPI, xApps, SAP NetWeaver and any other are registered trademarks of SAP AG. Every effort is made to ensure content integrity. Use information on this site at your own risk.
