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

Main Data Selection

All seafarers that have planned sea-service activity.

SQL statement

Click here to expand...

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
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.

Missing/Expiring Requirements (Competence) - Planned