Description
The script can be used to insert Rank Name to Rank Code Xref. The script is designed to be used for Crewlink Interface but can be modified for other interface codes.
Selection
The script takes the Rank name and inserts it in the Xref field of the Rank Codes where Interface Code=”CREWLINK”:
SQL statement
Click here to expand...
declare @Code varchar(10) = (select MIN(CODE) from PW001C02);
while @Code is not null
begin
print @Code;
if (select CODE from PW001C000_XREF
where INTERFACE_XREF='CREWLINK' and TABLE_REF=2 and CODE=@Code) is not null
begin
update PW001C000_XREF
set XREF_CODE = (select [NAME] from PW001C02 where CODE = @Code)
where CODE = @Code;
end
else
begin
insert into PW001C000_XREF (INTERFACE_XREF,TABLE_REF,CODE,XREF_CODE,COMMENT,repl_ModifiedBySite,repl_ModifiedDate,REPL_GMTMODIFIEDDATE)
values ('CREWLINK',2,@Code,(select [NAME] from PW001C02 where CODE = @Code),'',(select sitenr from repl_Site where isCurrentSite=1),getdate(),GETUTCDATE());
end
set @Code = (select MIN(CODE) from PW001C02 where CODE > @Code);
end;
go
Field Specification
N/A
