/*Updated 2020-05-04*/
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO
if object_id('ufsReadfileAsString') is not null drop function [dbo].[ufsReadfileAsString]
GO

CREATE FUNCTION [dbo].[ufsReadfileAsString]
(
@Path VARCHAR(255),
@Filename VARCHAR(100)
)
RETURNS 
 Varchar(max)
AS
BEGIN

DECLARE  @objFileSystem int
        ,@objTextStream int,
		@objErrorObject int,
		@strErrorMessage Varchar(1000),
	    @Command varchar(1000),
		@Chunk Varchar(8000),
		@String varchar(max),
	    @hr int,
		@YesOrNo int

Select @String=''
select @strErrorMessage='opening the File System Object'
EXECUTE @hr = sp_OACreate  'Scripting.FileSystemObject' , @objFileSystem OUT


if @HR=0 Select @objErrorObject=@objFileSystem, @strErrorMessage='Opening file "'+@path+'\'+@filename+'"',@command=@path+'\'+@filename

if @HR=0 execute @hr = sp_OAMethod   @objFileSystem  , 'OpenTextFile'
	, @objTextStream OUT, @command,1,false,0--for reading, FormatASCII

WHILE @hr=0
	BEGIN
	if @HR=0 Select @objErrorObject=@objTextStream, 
		@strErrorMessage='finding out if there is more to read in "'+@filename+'"'
	if @HR=0 execute @hr = sp_OAGetProperty @objTextStream, 'AtEndOfStream', @YesOrNo OUTPUT

	IF @YesOrNo<>0  break
	if @HR=0 Select @objErrorObject=@objTextStream, 
		@strErrorMessage='reading from the output file "'+@filename+'"'
	if @HR=0 execute @hr = sp_OAMethod  @objTextStream, 'Read', @chunk OUTPUT,4000
	SELECT @String=@string+@chunk
	end
if @HR=0 Select @objErrorObject=@objTextStream, 
	@strErrorMessage='closing the output file "'+@filename+'"'
if @HR=0 execute @hr = sp_OAMethod  @objTextStream, 'Close'


if @hr<>0
	begin
	Declare 
		@Source varchar(255),
		@Description Varchar(255),
		@Helpfile Varchar(255),
		@HelpID int
	
	EXECUTE sp_OAGetErrorInfo  @objErrorObject, 
		@source output,@Description output,@Helpfile output,@HelpID output
	Select @strErrorMessage='Error whilst '
			+coalesce(@strErrorMessage,'doing something')
			+', '+coalesce(@Description,'')
	select @String=@strErrorMessage
	end
EXECUTE  sp_OADestroy @objTextStream
	-- Fill the table variable with the rows for your result set
	
	RETURN @string
END
GO
set nocount on
print 'Drop BACKUP tables'
DECLARE
        @sql NVARCHAR(4000)
DECLARE cur7 CURSOR FOR 
	    SELECT 'DROP TABLE '+USER_NAME(o.uid)+'.'+o.name
                FROM sysobjects o
                WHERE o.xtype='U' AND 
					not (o.name like 'MRL%' or
						o.name like 'AAM%' or
						o.name like 'ACC_%' or
						o.name like 'AIS_%' or
						o.name like 'CALENDAR' or
						o.name like 'WEB%' or
						o.name like 'PY%' or
						o.name like 'PW%' or
						o.name like 'repl%' or
						o.name like 'AUDIT%' or
						o.name like 'VRP%' or
						o.name like 'AIS%' or
						o.name like 'WEB_CP%' or
						o.name like '%names' or
						o.name like 'PROJECT%' or
						o.name like 'D2D%' or
						o.name like 'DD%' or
						o.name like 'PRM%' or
						o.name like 'people%' or
						o.name like 'xapp%' or
						o.name like '%xPW%' or
						o.name like 'SPM%')
        OPEN cur7
        FETCH NEXT FROM cur7 INTO @sql
        WHILE @@FETCH_STATUS=0
        BEGIN
			exec sp_executesql @sql
			-- print @sql
            FETCH NEXT FROM cur7 INTO @sql
        END
        CLOSE cur7;
        DEALLOCATE cur7;
GO
	    
print 'Delete data from "AUDIT" tables'
declare @sql nvarchar(4000)
DECLARE cur4 CURSOR FOR 
        SELECT 'TRUNCATE TABLE '+o.name
                FROM sysobjects o
                WHERE o.xtype='U' AND o.name LIKE 'AUDIT_PWORG%';
        OPEN cur4;
        FETCH NEXT FROM cur4 INTO @sql;
        WHILE @@FETCH_STATUS=0
        BEGIN
                exec sp_executesql @sql;
                FETCH NEXT FROM cur4 INTO @sql;
        END
        CLOSE cur4;
        DEALLOCATE cur4;
GO
     
print 'Drop Utvalg tables'
DECLARE
        @sql NVARCHAR(4000)
DECLARE cur6 CURSOR FOR 
        SELECT 'DROP TABLE ADPW.'+o.name
                FROM sysobjects o
                WHERE o.xtype='U' AND  o.name LIKE 'WW%' ;
        OPEN cur6;
        FETCH NEXT FROM cur6 INTO @sql;
        WHILE @@FETCH_STATUS=0
        BEGIN
                exec sp_executesql @sql;
                FETCH NEXT FROM cur6 INTO @sql;
        END
        CLOSE cur6;
        DEALLOCATE cur6;
GO        

/* Load names.txt and family.txt into database */
if object_id('first_names') is not null drop table first_names
go
create table first_names(id int,name varchar(50))
go
if object_id('last_names') is not null drop table last_names
go
create table last_names(id int,name varchar(50))
go

declare 
    @crlf varchar(2) = CHAR(10),
	@name varchar(50),  
	@fnames varchar(max),
	@lnames varchar (max),
	@path varchar(255) = 'h:\backup\temp',
	@curPos int = 0,
	@prevPos int = 0
	
	set @fnames = dbo.ufsReadfileAsString(@path,'name.txt')
	if @fnames <> '' 
	begin
	  set @curPos = charindex(@crlf,@fnames)
	  while @curPos > 0 
	  begin
		set @name = substring(@fnames,@prevPos+1,@curPos-@prevPos)
		insert into first_names (id, name) values (@curPos, @name)
		set @prevPos = @curPos
  	    set @curPos = charindex(@crlf,@fnames,@prevPos+1)
	  end
	end
	
	select @curPos = 0, @prevPos = 0	
	
	set @fnames = dbo.ufsReadfileAsString(@path,'family.txt')
	if @fnames <> '' 
	begin
	  set @curPos = charindex(@crlf,@fnames)
	  while @curPos > 0 
	  begin
		set @name = substring(@fnames,@prevPos+1,@curPos-@prevPos-1)
		insert into last_names (id, name) values (@curPos, @name)
		set @prevPos = @curPos
  	    set @curPos = charindex(@crlf,@fnames,@prevPos+1)
	  end
	end
go

print 'Update PW001P01 First and Last names'
alter table pw001p01 disable trigger all
declare 
  curP01 cursor for select pin  from pw001p01

declare
  @iPin int,
  @sLastName varchar(40),  
  @sFirstName varchar(40)

 open curP01;
 fetch next from curP01 into @iPin;
 while (@@FETCH_STATUS <> -1)
 begin

	SELECT TOP 1 @sLastName = name 
    FROM last_names 
    ORDER BY newid()
    
	SELECT TOP 1 @sFirstName = name 
	FROM first_names 
    ORDER BY newid()

    update pw001p01 set name = @sLastName +' '+ @sFirstName, lastname = @sLastName, FirstName=@sFirstName where pin=@iPin
	fetch next from curP01 into @iPin;
 end
 close curP01
 deallocate curP01
alter table pw001p01 enable trigger all
update AUDIT_PW001P01
set
	MAIDENNAME='',
	FIRSTNAME = p.FIRSTNAME,
	LASTNAME = p.LASTNAME,
	[NAME] = p.[NAME]
from AUDIT_PW001P01 a
join pw001p01 p on a.pin = p.pin;

go  	

print 'Update PW001P06 First and Last names'
alter table pw001p06 disable trigger all
declare 
  curP06 cursor for select pin, sequenceno  from pw001p06
declare
  @iPin int,
  @sLastName varchar(40),  
  @sFirstName varchar(40),
  @sequenceno int

 open curP06;
 fetch next from curP06 into @iPin,@sequenceno;
 while (@@FETCH_STATUS <> -1)
 begin

	select @sLastname = lastname from pw001p01 where pin=@iPin
	
	SELECT TOP 1 @sFirstName = name 
	FROM first_names 
	ORDER BY newid()

	update pw001p06 set name = @sLastName +', '+ @sFirstName, lastname = @sLastName, FirstName=@sFirstName where sequenceno=@sequenceno
	fetch next from curP06 into @iPin,@sequenceno;
 end
 close curP06
 deallocate curP06
alter table pw001p06 enable trigger all
update AUDIT_PW001P06
set
	MAIDENNAME='',
	FIRSTNAME = p.FIRSTNAME,
	LASTNAME = p.LASTNAME,
	[NAME] = p.[NAME]
from AUDIT_PW001P06 a
join pw001p06 p on a.pin = p.pin and a.SEQUENCENO = p.SEQUENCENO;
GO

print 'Set all fields "NOTES", "NOTE" of database to "Not me, Chief!" the Seaman replied. "Once I get out of the Navy, I''m never going to stand in line again!"'
DECLARE
        @sql NVARCHAR(4000),
        @tablename VARCHAR(30),
        @fieldname VARCHAR(30),
        @notes NVARCHAR(4000);

DECLARE cur0 CURSOR FOR
        SELECT o.name, c.name 
        FROM sysobjects o,      syscolumns c
        WHERE o.xtype='U' AND o.id=c.id
        AND c.xtype IN (35, 99)
        AND c.name IN ('NOTES', 'NOTE')
		and (o.name like 'MRL%' or
		o.name like 'WEB%' or
		o.name like 'PY%' or
		o.name like 'PW%' or
		o.name like 'repl%' or
		o.name like 'VPR%')

        SET @notes = '{\rtf1\ansi\deff0{\fonttbl{\f0\fnil Arial;}{\f1\fswiss\fcharset204{\*\fname Arial;}Arial CYR;}}  {\colortbl ;\red0\green0\blue0;}  \viewkind4\uc1\pard\lang1049\b\f0\fs16\par  \par  [ 25-February-2010 14:52  - ADPW \b0 ] \par  \par  # \cf1\f1 "Well," snarled the tough old Navy Chief to the bewildered Seaman. "I suppose after you get discharged from the Navy, you''ll just be waiting for me to die so you can come and piss on my grave."\par  \b\par  \par  [ 25-February-2010 14:52  - ADPW \b0 ] \par  \cf0\f0\par  # \cf1\f1 "Not me, Chief!" the Seaman replied. "Once I get out of the Navy, I''m never going to stand in line again!"\par  \cf0\f0\par  }  '
        OPEN cur0;
        FETCH NEXT FROM cur0 INTO @tablename, @fieldname;
        WHILE @@FETCH_STATUS = 0
        BEGIN
				set @sql = 'ALTER TABLE '+@tablename+' DISABLE TRIGGER ALL'
				exec sp_executesql @sql
                SET @sql = 'UPDATE '+@tablename+' SET '+@fieldname+'=@notes WHERE '+@fieldname+' IS NOT NULL';
                exec sp_executesql @sql,
                        @Params = N'@notes varchar',
                        @notes = @notes; 
				set @sql = 'ALTER TABLE '+@tablename+' ENABLE TRIGGER ALL'
				exec sp_executesql @sql
                FETCH NEXT FROM cur0 INTO @tablename, @fieldname;
        END 
        CLOSE cur0;
        DEALLOCATE cur0;
GO       

PRINT 'Erase maiden names'
alter table PW001P01 disable trigger all;
Update PW001P01
Set MAIDENNAME = ''
alter table PW001P01 enable trigger all;
GO

PRINT 'Set all fields of database "COMMENTS", "COMMENT", "REMARK", "REMARKS" of database to "Remember pancake day"'
DECLARE
        @sql NVARCHAR(4000),
        @tablename VARCHAR(30),
        @fieldname VARCHAR(30),
        @notes NVARCHAR(4000);
DECLARE cur1 CURSOR FOR
        SELECT o.name, c.name 
        FROM sysobjects o,      syscolumns c
        WHERE o.xtype='U' AND o.id=c.id
        AND c.xtype IN (35, 99, 167, 231)
        AND c.name IN ('COMMENTS', 'COMMENT', 'REMARK', 'REMARKS')
		and (o.name like 'MRL%' or
		o.name like 'WEB%' or
		o.name like 'PY%' or
		o.name like 'PW%' or
		o.name like 'repl%' or
		o.name like 'VPR%')

        SET @notes = 'Remember pancake day'
        OPEN cur1;
        FETCH NEXT FROM cur1 INTO @tablename, @fieldname;
        WHILE @@FETCH_STATUS = 0
        BEGIN
				set @sql = 'ALTER TABLE '+@tablename+' DISABLE TRIGGER ALL'
				exec sp_executesql @sql
                SET @sql = 'UPDATE '+@tablename+' SET '+@fieldname+'=@notes WHERE '+@fieldname+' IS NOT NULL';
                exec sp_executesql @sql,
                        @Params = N'@notes varchar',
                        @notes = @notes; 
				set @sql = 'ALTER TABLE '+@tablename+' ENABLE TRIGGER ALL'
				exec sp_executesql @sql
                FETCH NEXT FROM cur1 INTO @tablename, @fieldname;
        END 
        CLOSE cur1;
        DEALLOCATE cur1;
GO       

print 'Set all fields of database "CreatedBY", "ChangedBY" to "ADEMO"'
DECLARE
        @sql NVARCHAR(4000),
        @tablename VARCHAR(30),
        @fieldname VARCHAR(30),
        @notes NVARCHAR(4000),
		@user varchar(18)
DECLARE cur5 CURSOR FOR
        SELECT o.name, c.name 
        FROM sysobjects o,      syscolumns c
        WHERE o.xtype='U' AND o.id=c.id
         AND c.xtype IN (35, 99, 167, 231)
        AND c.name IN ('CREATEDBY', 'CHANGEDBY') 
		and (o.name like 'MRL%' or
		o.name like 'WEB%' or
		o.name like 'PY%' or
		o.name like 'PW%' or
		o.name like 'repl%' or
		o.name like 'VPR%')
        SET @user = 'ADEMO'
        OPEN cur5;
        FETCH NEXT FROM cur5 INTO @tablename, @fieldname;
        WHILE @@FETCH_STATUS = 0
        BEGIN
				set @sql = 'ALTER TABLE '+@tablename+' DISABLE TRIGGER ALL'
				exec sp_executesql @sql
                SET @sql = 'UPDATE '+@tablename+' SET '+@fieldname+'=@user WHERE '+@fieldname+' IS NOT NULL';
                exec sp_executesql @sql,
                        @Params = N'@user varchar(18)',
                        @user = @user; 
				set @sql = 'ALTER TABLE '+@tablename+' ENABLE TRIGGER ALL'
				exec sp_executesql @sql
                FETCH NEXT FROM cur5 INTO @tablename, @fieldname;
        END 
        CLOSE cur5;
        DEALLOCATE cur5;
GO
/*
print 'Rename Company and Vessels to "CompanyX", "VesselX"'
DECLARE @otype int, @numorgid int, @count int
DECLARE cur8 CURSOR FOR 
        select numorgid, orgtype  FROM PWORG where orgtype in (2,3)
        set @count = 1
        OPEN cur8
        FETCH NEXT FROM cur8 INTO @numorgid, @otype;
        WHILE @@FETCH_STATUS = 0
        BEGIN
            IF @otype = 2
                UPDATE PWORG SET  NAME = 'Company '+LTRIM(STR(@count)) WHERE NUMORGID=@numorgid
            ELSE
                UPDATE PWORG SET  NAME = 'Vessel '+LTRIM(STR(@count)) WHERE NUMORGID=@numorgid
            
            set @count = @count+1
            FETCH NEXT FROM cur8 INTO @numorgid, @otype;
        END
        CLOSE cur8
        DEALLOCATE cur8
GO        
*/
print 'Scramble telecommunications of PW001P0T'
alter table pw001p0t disable trigger all

UPDATE PW001P0T SET TELENO = case
								when TELETYPE in (6,8) and teleno like '%@%' then replace(TELENO,left(teleno,charindex('@',teleno)-1),convert(varchar,pin)+'_email')
								when TELETYPE in (6,8) and teleno not like '%@%' then convert(varchar,pin)+'_email@corporate.net'
								when TELETYPE in (0,1,2,3,4,9) then SUBSTRING('+128' + Replace(replace(teleno,'+',''),' ',''),1,len(Replace(replace(teleno,'+',''),' ','')))
								else ''
							End;


alter table pw001p0t enable trigger all
GO

print 'Scramble telecommunications of PW001PT6'
alter table pw001pt6 disable trigger all

UPDATE pw001pt6 SET TELENO = case
								when TELETYPE in (6,8) and teleno like '%@%' then replace(TELENO,left(teleno,charindex('@',teleno)-1),'NOK_email')
								when TELETYPE in (6,8) and teleno not like '%@%' then 'NOK_email@corporate.net'
								when TELETYPE in (0,1,2,3,4,9) then SUBSTRING('+128' + Replace(replace(teleno,'+',''),' ',''),1,len(Replace(replace(teleno,'+',''),' ','')))
								else ''
							End;

alter table pw001pt6 enable trigger all
GO    

print 'Set "DESCRIPTION" field of PW001OLEDOCS to "Enclosed document"'
alter table PW001OLEDOCS disable trigger all
        update PW001OLEDOCS set DESCRIPTION='Enclosed document'
alter table PW001OLEDOCS enable trigger all
GO        

print 'Scramble telecommunications of PW001P01'
alter table pw001p01 disable trigger all
        UPDATE PW001P01 SET TELEPHONE = SUBSTRING('+128' + Replace(replace(TELEPHONE,'+',''),' ',''),1,len(Replace(replace(TELEPHONE,'+',''),' ',''))) WHERE TELEPHONE IS NOT NULL and LTRIM(RTRIM(TELEPHONE))!='';
alter table pw001p01 enable trigger all
GO        

print 'Scramble BIRTHDATE of PW001P01'
alter table pw001p01 disable trigger all
        UPDATE PW001P01 SET BIRTHDATE=BIRTHDATE + FLOOR(RAND(PIN)*1000) WHERE BIRTHDATE IS NOT NULL       ;
alter table pw001p01 enable trigger all
GO        
print 'Scramble PERSONALIDNO of PW001P01'
alter table pw001p01 disable trigger all
        UPDATE PW001P01 SET PERSONALIDNO = SUBSTRING('12345' + PERSONALIDNO,1,11) WHERE PERSONALIDNO IS NOT NULL
alter table pw001p01 enable trigger all
GO
print 'Scramble BANKACCOUNTNO of PW001P01'
alter table pw001p01 disable trigger all
        UPDATE PW001P01 SET BANKACCOUNTNO = left(BANKACCOUNTNO,1)+'X'+right(left(BANKACCOUNTNO,3),1)+'XX'+
											+case when len(BANKACCOUNTNO)>7 then right(left(BANKACCOUNTNO,6),1) else '0'end + 'XXX'+
											+case when len(BANKACCOUNTNO)>9 then right(BANKACCOUNTNO,len(BANKACCOUNTNO)-9) else '' end 
		WHERE BANKACCOUNTNO IS NOT NULL and BANKACCOUNTNO <> ''
alter table pw001p01 enable trigger all
update AUDIT_PW001P01
set
	TELEPHONE=p.TELEPHONE,
	BIRTHDATE = p.BIRTHDATE,
	PERSONALIDNO = p.PERSONALIDNO,
	BANKACCOUNTNO = p.BANKACCOUNTNO
from AUDIT_PW001P01 a
join pw001p01 p on a.pin = p.pin;
GO
print 'Scramble SSN of PW001P0Y'
alter table PW001P0Y disable trigger all
        UPDATE PW001P0Y SET SSN = left(SSN,1)+'X'+right(left(SSN,3),1)+'XX'+
									+case when len(SSN)>7 then right(left(SSN,6),1) else '0'end + 'XXX'+
									+case when len(SSN)>9 then right(SSN,len(SSN)-9) else '' end
		WHERE SSN IS NOT NULL and SSN <> ''
alter table PW001P0Y enable trigger all
GO
print 'Scramble SSN of PW001P0Y_HIST'
alter table PW001P0Y_HIST disable trigger all
		UPDATE PW001P0Y_HIST SET SSN = left(SSN,1)+'X'+right(left(SSN,3),1)+'XX'+
									+case when len(SSN)>7 then right(left(SSN,6),1) else '0'end + 'XXX'+
									+case when len(SSN)>9 then right(SSN,len(SSN)-9) else '' end
		WHERE SSN IS NOT NULL and SSN <> ''
alter table PW001P0Y_HIST enable trigger all
GO
print 'Scramble BANKACCOUNT of PW001P0Y_HIST'
alter table PW001P0Y_HIST disable trigger all
		UPDATE PW001P0Y_HIST SET BANKACCOUNT = left(BANKACCOUNT,1)+'X'+right(left(BANKACCOUNT,3),1)+'XX'+
									+case when len(BANKACCOUNT)>7 then right(left(BANKACCOUNT,6),1) else '0'end + 'XXX'+
									+case when len(BANKACCOUNT)>9 then right(BANKACCOUNT,len(BANKACCOUNT)-9) else '' end
		WHERE BANKACCOUNT IS NOT NULL and BANKACCOUNT <> ''
alter table PW001P0Y_HIST enable trigger all
GO
print 'Scramble ACCOUNTNO of PW001PAI'
alter table pw001pai disable trigger all
		UPDATE pw001pai SET ACCOUNTNO = left(ACCOUNTNO,1)+'X'+right(left(ACCOUNTNO,3),1)+'XX'+
											+case when len(ACCOUNTNO)>7 then right(left(ACCOUNTNO,6),1) else '0'end + 'XXX'+
											+case when len(ACCOUNTNO)>9 then right(ACCOUNTNO,len(ACCOUNTNO)-9) else '' end 
		WHERE ACCOUNTNO IS NOT NULL and ACCOUNTNO <> ''		
alter table pw001pai enable trigger all
go
print 'Erase TEXT of PW001PAI'
alter table pw001pai disable trigger all
		UPDATE pw001pai SET TEXT = ''		
alter table pw001pai enable trigger all
go
print 'Scramble travel document numbers'
alter table pw001p08 disable trigger all
		;with tally as (select top (100) N=row_number() over (order by @@spid) from sys.all_columns),
		data as (
		    select SEQUENCENO,PIN,CODE,(select VISATYPE from pw001c23 where code=p08.CODE) 'type',TDNUMBER DirtyCol, Col
		    from PW001P08 p08
		        cross apply (
		            select (select C + ''
		            from (select N, substring(TDNUMBER, N, 1) C from tally where N<=datalength(TDNUMBER)) [1]
		            where C between '0' and '9'
		            order by N
		            for xml path(''))
		        ) p (Col)
		    where p.Col is not NULL
		)
		UPDATE pw001p08 set TDNUMBER = scrmbl.NewNum 
		FROM pw001p08 p08
		left join(select SEQUENCENO,PIN,type,DirtyCol, Code+SUBSTRING(left(Col,1)+'X'+right(left(Col,3),1)+'XX'+
		+case when len(Col)>7 then right(left(Col,6),1) else '0'end + 'XXX'+
		+case when len(Col)>9 then right(Col,len(Col)-9) else '' end,1,10) as NewNum
		from data) scrmbl on p08.pin=scrmbl.PIN and p08.SEQUENCENO=scrmbl.SEQUENCENO
alter table pw001p08 enable trigger all
go
print 'Scramble competence numbers'
alter table pw001p05 disable trigger all
		;with tally as (select top (100) N=row_number() over (order by @@spid) from sys.all_columns),
		data as (
		    select SEQUENCENO,PIN,CODE,(select VISATYPE from pw001c23 where code=p05.CODE) 'type',CNUMBER DirtyCol, Col
		    from PW001P05 p05
		        cross apply (
		            select (select C + ''
		            from (select N, substring(CNUMBER, N, 1) C from tally where N<=datalength(CNUMBER)) [1]
		            where C between '0' and '9'
		            order by N
		            for xml path(''))
		        ) p (Col)
		    where p.Col is not NULL
		)
		UPDATE pw001p05 set CNUMBER = scrmbl.NewNum 
		FROM pw001p05 p05
		left join(select SEQUENCENO,PIN,type,DirtyCol, Code+SUBSTRING(left(Col,1)+'X'+right(left(Col,3),1)+'XX'+
		+case when len(Col)>7 then right(left(Col,6),1) else '0'end + 'XXX'+
		+case when len(Col)>9 then right(Col,len(Col)-9) else '' end,1,10) as NewNum
		from data) scrmbl on p05.pin=scrmbl.PIN and p05.SEQUENCENO=scrmbl.SEQUENCENO
alter table pw001p05 enable trigger all
go
print 'Set Enclosed document to NULL'
alter table PW001OLEDOCS disable trigger all
        UPDATE PW001OLEDOCS SET [DOCUMENT] = NULL WHERE DOCUMENT IS NOT NULL AND ISNULL(MERGETPL,'N') <> 'Y'
alter table PW001OLEDOCS enable trigger all
GO        

     
/*Change Concern name to Adonis Demo*/
/*
print 'Change Concern name to Adonis Demo'
UPDATE PWORG SET NAME='Adonis Demo' WHERE ORGTYPE=1
GO
*/
print 'Erase company phones'
delete from pworgtel
go

print 'Erase company addresses'
update pworgcmp set address1=null,address2=null,address3=null, country=null, postcode=null
go

print 'Erase vessel additional data'
update pworgves set callsignal=null, shortname=null,registryport=null,builtplace=null,officialshipno=null,imonumber=null
GO

print 'Erase notification task receivers'
UPDATE web_cp_notification_tasks set email_recipients = null 
GO

print 'Change users in login session log to DEMO'
update pwloginsession set loginname='DEMO'
GO

print 'Delete data from "AUDIT" tables after the scramble'
delete from AUDIT_PW001P05;
delete from AUDIT_PW001P08;
GO