Missing/Expiring Requirements (Competence) - Planned

Summary

This view shows missing/expiring Requirements (Competence) for the crew whos currently planned to embark.

Keywords

Required Documents

Category

Crew List View

Description

This view shows missing/expiring Requirements (Competence) for the crew whos currently planned to embark based on the setup of the Requirements Profile and the Organization Structure > Crew Change for the Vessel Pool.

View Sample

image-20240222-091351.png

Main Data Selection

All seafarers that have planned sea-service activity.

SQL statement

CREATE VIEW dbo.PW001SRV58 AS SELECT final.PIN, final.ALTERNATIVEPIN, [Full Name], [Organization Name], [Current Rank], [Nationality], --[Document Type], [Document Code], [Document Name], [Document Status], [Issued Date], [Expiry Date], VESSEL_NUMORGID, [Embarkation Date], [Disembarkation Date], [Activity Vessel], [Activity Department], [Activity Position], [Scan Status], NUMORGID, EMPLOYMENTSTARTDATE, EMPLOYMENTENDDATE FROM ( SELECT p01.PIN, P01.ALTERNATIVEPIN, p01.CLIENT NUMORGID, P01.EMPLOYMENTSTARTDATE, P01.EMPLOYMENTENDDATE, p01.[NAME] AS 'Full Name', org.[NAME] AS 'Organization Name', c02.NAME AS 'Current Rank', nat.NATIONALITY AS 'Nationality', --doc.[TYPE] AS 'Document Type', doc.CODE AS 'Document Code', docName.[TEXT] AS 'Document Name', doc.[STATUS] AS 'Document Status', docDetails.DATEFROM AS 'Issued Date', docDetails.EXPIRYDATE AS 'Expiry Date', ves.NUMORGID AS VESSEL_NUMORGID, p03plan.DATEFROM AS 'Embarkation Date', ISNULL(p03plan.DATETO, p03plan.TODATEESTIMATED) AS 'Disembarkation Date', ves.[NAME] AS 'Activity Vessel', dep.[NAME] AS 'Activity Department', pos.[NAME] AS 'Activity Position', CASE WHEN ( docDetails.scanneddocno IS NULL OR oledoc.[DOCUMENT] IS NULL ) THEN NCHAR(9940) + ' Missing' WHEN docDetails.SCANVALIDITY = 1 AND docDetails.scanneddocno IS NOT NULL THEN NCHAR(10004) + ' Confirmed' WHEN( docDetails.SCANVALIDITY = 0 OR docDetails.SCANVALIDITY IS NULL ) AND scanneddocno IS NOT NULL THEN NCHAR(10006) + ' Not-Confirmed' --ELSE 'N/A' END AS 'Scan Status' FROM ( SELECT prc.PIN, prc.[TYPE], CASE WHEN p05.CODE IS NULL THEN 'Missing' WHEN p05.CODE IS NOT NULL THEN 'Expiring' END AS STATUS, prc.COMPETENCE AS CODE, p05.SEQUENCENO FROM ( SELECT DISTINCT p03.PIN, CASE WHEN c12.CODE IS NOT NULL THEN 'Competence/Certificate' END AS TYPE, prc.COMPETENCE FROM PW001P03 p03 JOIN PW001C12 c12 ON c12.CODE = p03.CODE AND c12.OPTIONS LIKE '%S%' JOIN PWORGPRC prc ON prc.NUMORGID = p03.NUMORGID WHERE p03.DATETO >= CONVERT(VARCHAR, GETDATE(), 112) OR p03.DATETO IS NULL ) prc LEFT JOIN PW001P05 p05 ON p05.PIN = prc.PIN AND prc.COMPETENCE = p05.CODE ) doc LEFT JOIN PW001P01 p01 ON p01.PIN = doc.PIN LEFT JOIN PWORG org ON org.NUMORGID = p01.CLIENT LEFT JOIN PW001P0P p0p ON p0p.PIN = p01.PIN LEFT JOIN PW001C02 c02 ON c02.CODE = p0p.POSITIONID LEFT JOIN PWCOUNTRY nat ON nat.COUNTRYCODE = p01.NATIONALITY LEFT JOIN ( SELECT CODE, [TEXT] FROM PW001C06 UNION ALL SELECT CODE, VISATYPE AS [TEXT] FROM PW001C23 UNION ALL SELECT CODE, [TEXT] FROM PW001C24 )docName ON docName.CODE = doc.CODE LEFT JOIN ( SELECT SEQUENCENO, DATEFROM, EXPIRYDATE, SCANNEDDOCNO, SCANVALIDITY FROM PW001P05 UNION ALL SELECT SEQUENCENO, DATEISSUED AS DATEFROM, EXPIRYDATE, SCANNEDDOCNO, SCANVALIDITY FROM PW001P07 UNION ALL SELECT SEQUENCENO, DATEFROM, DATETO AS EXPIRYDATE, SCANNEDDOCNO, SCANVALIDITY FROM PW001P08 ) docDetails ON docDetails.SEQUENCENO = doc.SEQUENCENO JOIN PW001P03 p03plan ON p03plan.PIN = p01.PIN AND p03plan.PLANNED = 'Y' AND p03plan.CODE IN (SELECT t.CODE FROM pw001c12 t WHERE t.OPTIONS LIKE '%S%') AND NOT EXISTS ( SELECT 1 FROM PW001P03 p03t WHERE p03t.PIN = p01.PIN AND p03t.PLANNED = 'Y' AND p03t.CODE IN (SELECT t.CODE FROM PW001C12 t WHERE t.OPTIONS LIKE '%S%') AND p03t.DATEFROM < p03plan.DateFrom ) LEFT JOIN ( SELECT NUMORGID, dbo.ad_scanorgtree(NUMORGID, 3) AS VesselID, dbo.ad_scanorgtree(NUMORGID, 4) AS DeptID FROM pworg ) ou ON ou.NUMORGID = p03plan.NUMORGID LEFT JOIN pworg ves ON ves.NUMORGID = ou.VesselID LEFT JOIN pworg dep ON dep.NUMORGID = ou.DeptID LEFT JOIN pworg pos ON pos.NUMORGID = p03plan.NUMORGID LEFT JOIN PW001OLEDOCS oledoc ON oledoc.DOCNO = docDetails.scanneddocno ) final WHERE final.[Expiry Date] <= DATEADD(YEAR, 1, CAST(GETDATE() AS DATE)) OR Final.[Expiry Date] IS NULL

Columns Specification

Column

Description/ Location in APM

Column

Description/ Location in APM

PIN

Personal Details > Personal > PIN

Alternative PIN

Personal Details > Personal > Alternative PIN

Full Name

Personal Details > Personal > Full Name

Nationality

Personal Details > Personal > Nationality

Organization Name

Personal Details > Employment > Organization’s Name

Current Rank

Personal Details > Employment > Current Rank

Numorgid

Personal Details > Employment > Organization’s No

Document Type

The type of the document: Competence, Medical, Travel.

Document Code

The code of the document record linked to the selected crew.

Document Name

The name of the document record linked to the selected crew.

Document No

The number of the document record linked to the selected crew.

Document Status

The status of the document record linked to the selected crew.

Issue Date

The date from of the document record linked to the selected crew.

Expiry Date

The expert date of the document record linked to the selected crew.

EmploymentStartDate

Personal Details > Employment > Employment Start Date

EmploymentEndDate

Personal Details > Employment > Employment End Date

NUMORGID

Perosnal Details > Client > NUMORGID

Embarkation Date

Shows the activity DateFrom of the Embarked activity. If none, then blank.

Disembarkation Date

Shows the activity DateTo/ToDateEstimated of the Embarked activity. If none, then blank.

Activty Vessel

Shows the vessel name of the Embarked activity. If none, then blank.

Activity Department

Shows the department name of the Embarked activity. If none, then blank.

Activity Position

Showsthe position name of the Embarked activity. If none, then blank.

Scan status

Shows missing, confirmed, and non confirmed document status.

VESSEL_NUMORGID

Shows the vessels numorgid of the Embarked activity. If none, then blank.

Additional Information

You can highlight the expiration date in the view and define the period within which it should be highlighted.

In our example:

  • Yellow will show all records that expire from today's date + 60 days forward.

  • Red will show all records that expired in the period from yesterday and 365 back.

  • Gery will show all records that expired 365 ago and longer.

image-20240222-091744.png