How to set competence to expire during Sign-Off

Contents

Purpose

Some certificates are valid only during one onboard contract. They should not be valid if the crew leaves the vessel and then joins again.

For e.g. there is a certificate that consists of 2 parts: an online course and training onboard, thus the resulting certificate is gained when the seafarer is onboard.

The contract length is also different within even the same position for different nationalities, it makes it impossible to solve it via default Expiry Date set up.

Updating all the competencies manually so that the Expiry Date is equal to the Onboard activity end is too cumbersome and takes a lot of resources.

Based on the above we made it possible to automatically set the certificate as expired during the sign-off with the help of the stored database procedure.

It allows setting the Expiration Date of the competence record equal to Onboard Activity Date To. This update happens at the same moment when the person signs off (Off-signer is confirmed in Crew Change). The setup takes into account the Disembarkation reason, for example for the "Vessel Transfer" or for the "Work Break" this certificate needs to stay valid. Thus the setup allows selecting relevant disembarkation reasons for the certificate's automatic expiration.

Implementation

We created a SQL script to:

  1. Create a stored procedure dbo.sp_signoff_expiry_competence.

    The stored procedure dbo.sp_signoff_expiry_competence executes the update statement which sets the Expiry date for the competences with P05.EXPIRYDATE IS NULL AND P05.DATEFROM < P03.DATETO and P05.DATEFROM > P03. DATEFROM (P03 is Seaservice activity linked to SignOff crewchange and P03.Disembarkation has XREF), (P05 is competence record which has XREF).

  2. Create a job 'Competence to expire' which

    The job will be executed the dbo.sp_signoff_expiry_competence each 10 min by default, It is possible to change the default values by editing the script. The XREF interface must be added from GCT and proper Disembarkation and Competence codes should be marked by this interface.

Setup and Configuration

  1. Create the XREF interface with some ‘interface_code'.

     

  2. Create the disembarking reason, link XREF.

     

  3. Create the competence code and link XREF.

     

  4. Run the attached script (at the end of the article) to create a procedure. The XREF field name should be set (as in GCT).

Attachment

Workflow

  1. Find a person with onboard activity with a link to the Crew Change itinerary.

     

  2. Create a competence record created with the Date From crossing the activity dates.

     

  3. Sign Off a person. The disembarking reason should be selected.

  4. As a result the Expiry date is updated and equals to DateTo (Sign Off date) of an activity.