How to fix invalid Competence Codes with spaces


This articles explains how to fix invalid Competence 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 Competence Codes with spaces.

Workflow

  1. Find all the competence codes with spaces.

    select * from pw001c06
    where convert(varbinary(max),code) != convert(varbinary(max),upper(replace(ltrim(rtrim(code)),' ','')))

  2. Insert copies of the invalid competence codes, but without spaces (they would contain same information as the wrong codes).


    insert into pw001c06
    select
    upper(replace(ltrim(rtrim(code)),' ','')) as CODE,
    GROUPNO,
    CREATED,
    CREATEDBY,
    LASTCHANGED,
    CHANGEDBY,
    UPDATEFLAGS,
    SEQUENCENO,
    CODETYPE,
    TEXT,
    OPTIONS,
    ALTERNATIVE,
    STCWCODE,
    DOCTYPE,
    DEFEXPIRYDATEDAYS,
    DEFEDATETODAYS,
    MEASSUREMENT,
    repl_ModifiedBySite,
    repl_ModifiedDate,
    repl_GMTModifiEDDate,
    Q88_COC,
    Q88_STCW_PARA,
    Q88_IS_TANKER_CERT,
    Q88_RADIO_QUALIFICATION,
    Q88_CERTIFICATION,
    Q88_ADMINACCEPT,
    Q88_WATCHKEEPCERT,
    restriction,
    uOBLModuleID,
    vCD,
    EDOPTIONS
    from pw001c06
    where convert(varbinary(max),code) != convert(varbinary(max),upper(replace(ltrim(rtrim(code)),' ','')))

  3. Re-link competence records to the new/fixed competence codes.

    update PW001P05
    set code =upper(replace(ltrim(rtrim(code)),' ',''))
    where convert(varbinary(max),code) != convert(varbinary(max),upper(replace(ltrim(rtrim(code)),' ','')))

  4. Then set invalid codes to passive.

    update PW001C06
    set OPTIONS = 'P'+Isnull(OPTIONS,'')
    where convert(varbinary(max),code) != convert(varbinary(max),upper(replace(ltrim(rtrim(code)),' ','')))

  5. Please also note that it would be necessary to change the setup in the Requirements Profile Module for those codes.
    You would need to remove the invalid codes from each position and substitute them with the new/valid ones.
    Then 'Activate Profile' for the changes to take place.