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

Adonis HR Reports, Views and Similar

Insert Rank Name to Rank Code Xref (Crewlink Interface)

Description

Selection

SQL statement

Field Specification

Related content