cancel
Showing results for 
Search instead for 
Did you mean: 

Attachments are Replaced in SAP Business One

ibrey
Explorer
0 Kudos

Hi,

In SAP Business One, attachments are getting replaced if the name is matching without user knowing.

Is there anyway to prevent this? or whats the best way to handle this situation.

If the attachment is getting user should be notified.

Regards

Ibbe

View Entire Topic
genuiforex-83
Member
0 Kudos

While this scenario can be addressed through version 10 upgrade as there is a settings there to make all attachment unique by SAP B1 automatically adding date_time stamp on the document as they are attached. For those can not upgrade this can be done through:

1. create a temp folder for all attachment and rename file with date_time stamp

2. once the are renamed, move the file to permanent folder

Create stored procedure: [UpdateAttachments]

CREATE PROCEDURE [dbo].[UpdateAttachments]

-- Add the parameters for the stored procedure here

@docentry int

,@actiontype char(1)

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

-- Insert statements for procedure here

declare @command varchar(1000) = '';

declare @datestamp varchar(28) = format(getdate(),'yyyyMMddHHmmss')

declare @sourcePathA varchar(250) = '\\svtalfs01\SAP_Attachments\attemp\';

declare @destinationPathB varchar(250) = '\\svtalfs01\SAP_Attachments\2024onwards\';

declare @fileName varchar(250)

declare @newFileName varchar(250)

declare @dbwk1 table(wabsentry int, wfilename varchar(250), wfileext varchar(28), wlinenum int, wfilepath varchar(250))

insert into @dbwk1 (wabsentry, wfilename, wfileext, wlinenum, wfilepath)

(select absentry, [filename], FileExt, Line, trgtPath

from atc1 where isnull(absentry,'') <> '' and AbsEntry = @docentry

)

begin

declare @wfilename varchar(250)

declare @wfileext varchar(250)

declare @wlinenum int

declare @wabsentry int

declare @wfilepath varchar(250)

set @filename = ''

set @wfileext = ''

set @wlinenum = ''

set @wabsentry = ''

set @wfilepath = ''

declare attCursor cursor for

select wfilename, wfileext, wlinenum, wabsentry, wfilepath

from @dbwk1

open attCursor

fetch next from attCursor into @wfilename, @wfileext, @wlinenum, @wabsentry, @wfilepath

while @@fetch_status = 0

begin

set @fileName = @wfilename + '.' + @wfileext

set @newFileName = @wfilename + '_' + @datestamp + '_sapatt' + '.' + @wfileext

set @command = 'move /Y "' + @sourcePathA + @fileName + '" "' + @destinationPathB + @newFileName + '"'

exec master..xp_cmdshell @command, no_output

if @actiontype in ('A')

begin

update atc1 set filename = @wfilename + '_' + @datestamp + '_sapatt' where absentry = @wabsentry and line = @wlinenum

update atc1 set trgtPath = '\\svtalfs01\SAP_Attachments\2024onwards' where AbsEntry = @wabsentry and line = @wlinenum

end

else if @actiontype in ('U')

begin

update atc1 set filename = @wfilename + '_' + @datestamp + '_sapatt' where absentry = @wabsentry and line = @wlinenum and @wfilename not like '%_sapatt%' and @wfilepath <> '\\svtalfs01\SAP_Attachments'

update atc1 set trgtPath = '\\svtalfs01\SAP_Attachments\2024onwards' where AbsEntry = @wabsentry and line = @wlinenum and @wfilename not like '%_sapatt%' and @wfilepath <> '\\svtalfs01\SAP_Attachments'

end

fetch next from attCursor into @wfilename, @wfileext, @wlinenum, @wabsentry, @wfilepath

end

close attCursor

deallocate attCursor

end

END

In SAP B1 sbo_sp_postTransactionNotice

add this:

IF (@object_type = '221' AND @transaction_type IN ('A','U'))

begin

exec [UpdateAttachments] @list_of_cols_val_tab_del, @transaction_type

end

NB: Try to test database first before applying to your production database.