Insert Rank Name to Rank Code Xref (Crewlink Interface)

Owned by Helen Ustych, created
Last updated: Aug 23, 2023 by Daria Chebotaieva
1 min read
1 Description | 2 Selection | 3 SQL statement | 4 Field Specification

Summary

The script can be used to insert Rank Name to Rank Code Xref

Keywords

Xref, Rank, Crewlink

Category

SQL Statement

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 with the Interface Code=”CREWLINK”:

To use the script for other interface codes, 'CREWLINK' in the script needs to be replaced with a different interface name.

SQL statement

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