This articles explains how to fix invalid Rank Codes under the General Codes with spaces.

Introduction

Sometimes when codes are imported via sql they might contain a misprint, since sql insert doesn't apply any additional validation to the imported data that APM has.

The only way to avoid such problem is to check all the values before the import.

Here are the requirements applied to any Code under the General Codes:

• it should be max 10 symbols;

• capital letters only;

• spaces and some of the special signs are not allowed (e.g. !,?,*,%).
  However, there are still signs that are allowed (/,&). So you'd better test it by adding dummy code from the user interface;

In case you already imported Codes with invalid values the following error message will pop up if you try to open it in APM:

At this stage, the only way to fix this problem is to run another sql update.

Here is a short instruction on how to to fix invalid Rank Codes with spaces.

Workflow

1. Find all the rank codes with spaces.
   
   select * from PW001C02
   where convert(varbinary(max),code) != convert(varbinary(max),upper(replace(ltrim(rtrim(code)),' ','')))
   

2. Insert copies of the invalid rank codes, but without spaces (they would contain same information as the wrong codes).
   
   insert into PW001C02
   select
   upper(replace(ltrim(rtrim(code)),' ','')) as CODE,
   GROUPNO,
   CREATED,
   CREATEDBY,
   LASTCHANGED,
   CHANGEDBY,
   UPDATEFLAGS,
   SEQUENCENO,
   CODETYPE,
   NAME,
   PAYCLASS,
   LEAVEPAYFACTOR,
   OPTIONS,
   ALTERNATIVE,
   CODESPECIFICATION,
   PROMYEARS,
   PROMMONTH,
   CODECATEGORY,
   NEXTPROMRANKCODE,
   repl_ModifiedBySite,
   repl_ModifiedDate,
   repl_GMTModifiEDDate,
   COSTPLACE,
   ENOAD,
   PANAMA_CANAL_AUTH,
   Q88_OFFMATRIX,
   Q88_DEPARTMENT,
   Q88COMBINERANK,
   Q88ONWATCH,
   SDNRANK,
   SDNTRAININGTYPE,
   LB_SECGROUP,
   UDL_CODE,
   DMA_CODE,
   SeagulluRankId
   from PW001C02
   where convert(varbinary(max),code) != convert(varbinary(max),upper(replace(ltrim(rtrim(code)),' ','')))
   

3. Re-link rank records to the new/fixed rank codes.
   
   --Organization Properties
   update PWORG
   set ORGCODE =upper(replace(ltrim(rtrim(ORGCODE)),' ',''))
   where convert(varbinary(max),ORGCODE) != convert(varbinary(max),upper(replace(ltrim(rtrim(ORGCODE)),' ',''))) and ORGTYPE=5
   
   --Personal Details RANK
   update PW001P0P
   set positionid =upper(replace(ltrim(rtrim(positionid)),' ',''))
   where convert(varbinary(max),positionid) != convert(varbinary(max),upper(replace(ltrim(rtrim(positionid)),' ','')))
   
   update PW001p01
   set RANK =upper(replace(ltrim(rtrim(RANK)),' ',''))
   where convert(varbinary(max),RANK) != convert(varbinary(max),upper(replace(ltrim(rtrim(RANK)),' ','')))
   
   --Promotion history
   update PW001PROMHIST
   set CODERANKOLD =upper(replace(ltrim(rtrim(CODERANKOLD)),' ',''))
   where convert(varbinary(max),CODERANKOLD) != convert(varbinary(max),upper(replace(ltrim(rtrim(CODERANKOLD)),' ','')))
   
   --Activity Rank
   update PW001P03
   set RANK =upper(replace(ltrim(rtrim(RANK)),' ',''))
   where convert(varbinary(max),RANK) != convert(varbinary(max),upper(replace(ltrim(rtrim(RANK)),' ','')))
   
   --Rotation Shift
   update PWROT_ACTIVE_SHIFT
   set POSITION=upper(replace(ltrim(rtrim(POSITION)),' ',''))
   where convert(varbinary(max),POSITION) != convert(varbinary(max),upper(replace(ltrim(rtrim(POSITION)),' ','')))
   
   --Rotation Plan details
   update PWROT_DETAILS
   set POSITION=upper(replace(ltrim(rtrim(POSITION)),' ',''))
   where convert(varbinary(max),POSITION) != convert(varbinary(max),upper(replace(ltrim(rtrim(POSITION)),' ','')))
   
   --Ranks which are required onboard to man a project (rotation)
   update PWPRJ_POSITIONS
   set CODE=upper(replace(ltrim(rtrim(CODE)),' ',''))
   where convert(varbinary(max),CODE) != convert(varbinary(max),upper(replace(ltrim(rtrim(CODE)),' ','')))
   
   --Crew Change
   update PWCCMCHANGECREWLIST
   set PSNCODE=upper(replace(ltrim(rtrim(PSNCODE)),' ',''))
   where convert(varbinary(max),PSNCODE) != convert(varbinary(max),upper(replace(ltrim(rtrim(PSNCODE)),' ','')))
   
   update PWCCMCHANGEPOSPROMLIST
   set NRANK=upper(replace(ltrim(rtrim(NRANK)),' ',''))
   where convert(varbinary(max),NRANK) != convert(varbinary(max),upper(replace(ltrim(rtrim(NRANK)),' ','')))
   
   update PWCCMCHANGEPOSPROMLIST
   set CRANK=upper(replace(ltrim(rtrim(CRANK)),' ',''))
   where convert(varbinary(max),CRANK) != convert(varbinary(max),upper(replace(ltrim(rtrim(CRANK)),' ','')))
   
   --Competence Documents
   update PW001P05
   set RANK=upper(replace(ltrim(rtrim(RANK)),' ',''))
   where convert(varbinary(max),RANK) != convert(varbinary(max),upper(replace(ltrim(rtrim(RANK)),' ','')))
   
   --Required Competence List
   update VRP_REQ_EXPERIENCE
   set POSITION=upper(replace(ltrim(rtrim(POSITION)),' ',''))
   where convert(varbinary(max),POSITION) != convert(varbinary(max),upper(replace(ltrim(rtrim(POSITION)),' ','')))
   
   --Pay-scales
   update PWPSC001RANK
   set RANKCODE=upper(replace(ltrim(rtrim(RANKCODE)),' ',''))
   where convert(varbinary(max),RANKCODE) != convert(varbinary(max),upper(replace(ltrim(rtrim(RANKCODE)),' ','')))
   
   --Current payroll entries
   update PW001PAY
   set RANK =upper(replace(ltrim(rtrim(RANK)),' ',''))
   where convert(varbinary(max),RANK) != convert(varbinary(max),upper(replace(ltrim(rtrim(RANK)),' ','')))
   

   --Payroll history entries
   update PW001PYH
   set RANK =upper(replace(ltrim(rtrim(RANK)),' ',''))
   where convert(varbinary(max),RANK) != convert(varbinary(max),upper(replace(ltrim(rtrim(RANK)),' ','')))
   
   --Crew Station Bill Template
   update MRL_Template
   set RANK=upper(replace(ltrim(rtrim(RANK)),' ',''))
   where convert(varbinary(max),RANK) != convert(varbinary(max),upper(replace(ltrim(rtrim(RANK)),' ','')))
   

   --APP Crew Portal Organization
   update WEB_CP_ORGANIZATION_RANKS
   set RANK_CODE=upper(replace(ltrim(rtrim(RANK_CODE)),' ',''))
   where convert(varbinary(max),RANK_CODE) != convert(varbinary(max),upper(replace(ltrim(rtrim(RANK_CODE)),' ','')))
   
   --Budget Module
   update PWBUDGET_VSL_NAT_DETAILS
   set RANK=upper(replace(ltrim(rtrim(RANK)),' ',''))
   where convert(varbinary(max),RANK) != convert(varbinary(max),upper(replace(ltrim(rtrim(RANK)),' ','')))
   

4. Then set invalid codes to passive.
   
   update PW001C02
   set OPTIONS = 'P'+Isnull(OPTIONS,'')
   where convert(varbinary(max),code) != convert(varbinary(max),upper(replace(ltrim(rtrim(code)),' ','')))
   
   Or deleted them:
   delete from PW001C02 where convert(varbinary(max),code) != convert(varbinary(max),upper(replace(ltrim(rtrim(code)),' ','')))