Summary	This view shows missing/expiring Requirements (Competence, Travel, Medical) for the crew currently onboard or planned to embark.
Keywords	Required Documents
Category	Crew List View

Description

This view shows missing/expiring Requirements (Competence, Travel, Medical) for the crew who are 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 sea-service activity regardless if it's planned or current.

SQL statement

VIEWID should be substituted with the ID of the crew list view that you are creating in your system e.g. PW001SRV20 etc.

CREATE VIEW dbo.PW001SRV56 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 DISTINCT * --JMV Fetch distinct value as a quickfix
                          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 p05.CODE = dbo.ad_ReturnReplacingCode(p05.pin, prc.COMPETENCE, 0)
                                     
                                     UNION ALL 
                                     SELECT p.PIN,
                                            CASE 
                                                 WHEN c23.CODE IS NOT NULL THEN 'Travel'
                                                 WHEN c24.code IS NOT NULL THEN 'Medical'
                                            END AS TYPE,
                                            CASE 
                                                 WHEN p08.SEQUENCENO IS NOT NULL THEN 'Expiring'
                                                 WHEN p07.SEQUENCENO IS NOT NULL THEN 'Expiring'
                                                 WHEN p07.SEQUENCENO IS NULL THEN 'Missing'
                                                 WHEN p08.SEQUENCENO IS NULL THEN 'Missing'
                                            END AS STATUS,
                                            rtd.DOCCODE AS Code,
                                            CASE rtd.DOCTYPE
                                                 WHEN 8 THEN p08.SEQUENCENO
                                                 WHEN 7 THEN p07.SEQUENCENO
                                            END AS SEQUENCENO
                                     FROM   PW001P01 p
                                            JOIN (
                                                     SELECT DISTINCT p03.PIN,
                                                            dbo.ad_scanorgtree(p03.NUMORGID, 2) companyID
                                                     FROM   PW001P03 p03
                                                            JOIN PW001C12 c12
                                                                 ON  c12.CODE = p03.CODE
                                                                 AND c12.OPTIONS LIKE '%S%'
                                                     WHERE  p03.DATETO >= CONVERT(VARCHAR, GETDATE(), 112)
                                                            OR  p03.DATETO IS NULL
                                                 ) p03
                                                 ON  p03.PIN = p.PIN
                                            JOIN PWORGRTD rtd
                                                 ON  rtd.NUMORGID = p03.companyID
                                            LEFT JOIN PW001C23 c23
                                                 ON  c23.CODE = rtd.DOCCODE
                                                 AND rtd.DOCTYPE = 8
                                            LEFT JOIN PW001P08 p08
                                                 ON  p08.PIN = p.PIN
                                                 AND p08.CODE = c23.CODE
                                            LEFT JOIN PW001C24 c24
                                                 ON  c24.CODE = rtd.DOCCODE
                                                 AND rtd.DOCTYPE = 7
                                            LEFT JOIN PW001P07 p07
                                                 ON  p07.PIN = p.PIN
                                                 AND p07.CODE = c24.CODE
                                     
                                     UNION ALL
                                     SELECT p01.PIN,
                                            CASE 
                                                 WHEN c23.CODE IS NOT NULL THEN 'Travel'
                                                 WHEN c24.code IS NOT NULL THEN 'Medical'
                                            END AS TYPE,
                                            CASE 
                                                 WHEN p08.SEQUENCENO IS NOT NULL THEN 'Expiring'
                                                 WHEN p07.SEQUENCENO IS NOT NULL THEN 'Expiring'
                                                 WHEN p07.SEQUENCENO IS NULL THEN 'Missing'
                                                 WHEN p08.SEQUENCENO IS NULL THEN 'Missing'
                                            END AS STATUS,
                                            reqDoc.[DOCUMENT] AS Code,
                                            CASE 
                                                 WHEN c23.CODE IS NOT NULL THEN p08.SEQUENCENO
                                                 WHEN c24.code IS NOT NULL THEN p07.SEQUENCENO
                                            END AS SEQUENCENO
                                     FROM   (
                                                SELECT DISTINCT p03.PIN,
                                                       dbo.ad_scanorgtree(p03.NUMORGID, 2) companyID,
                                                       p.NATIONALITY
                                                FROM   PW001P03 p03
                                                       JOIN PW001C12 c12
                                                            ON  c12.CODE = p03.CODE
                                                            AND c12.OPTIONS LIKE '%S%'
                                                       LEFT JOIN PW001P01 P
                                                            ON  p.PIN = p03.PIN
                                                WHERE  p03.DATETO >= CONVERT(VARCHAR, GETDATE(), 112)
                                                       OR  p03.DATETO IS NULL
                                            ) p01
                                            CROSS APPLY (
                                         SELECT 7 AS DOCTYPE,
                                                *
                                         FROM   RP_NAT_REQDOC_MEDICAL
                                         UNION ALL 
                                         SELECT 8 AS DOCTYPE,
                                                *
                                         FROM   RP_NAT_REQDOC_TRAVEL
                                     ) reqDoc
                                     LEFT JOIN PW001C23 c23
                                                 ON  c23.CODE = reqDoc.DOCUMENT
                                                 AND reqDoc.DOCTYPE = 8
                                            LEFT JOIN PW001P08 p08
                                                 ON  p08.PIN = p01.PIN
                                                 AND p08.CODE = c23.CODE
                                            LEFT JOIN PW001C24 c24
                                                 ON  c24.CODE = reqDoc.DOCUMENT
                                                 AND reqDoc.DOCTYPE = 7
                                            LEFT JOIN PW001P07 p07
                                                 ON  p07.PIN = p01.PIN
                                                 AND p07.CODE = reqDoc.[DOCUMENT]
                                            LEFT JOIN PWCOUNTRY c
                                                 ON  c.COUNTRYCODE = p01.NATIONALITY
                                            LEFT JOIN RP_NAT_EXCLUDED_MEDICAL excMed
                                                 ON  excMed.NATIONALITY = c.COUNTRYCODE
                                                 AND excMed.[DOCUMENT] = reqDoc.[DOCUMENT]
                                     WHERE  excMed.NATIONALITY IS NULL
                                 )t
                      ) 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
                               WHERE  OPTIONS NOT LIKE '%P%' --JMV Filter out passive document codes to avoid duplicates
                               UNION ALL
                               SELECT CODE,
                                      VISATYPE AS [TEXT]
                               FROM   PW001C23
                               WHERE  OPTIONS NOT LIKE '%P%'--JMV Filter out passive document codes to avoid duplicates
                               UNION ALL
                               SELECT CODE,
                                      [TEXT]
                               FROM   PW001C24
                               WHERE  OPTIONS NOT LIKE '%P%'--JMV Filter out passive document codes to avoid duplicates
                           )docName
                           ON  docName.CODE = doc.CODE
                      LEFT JOIN (
                               SELECT PIN,
                                      SEQUENCENO,
                                      DATEFROM,
                                      EXPIRYDATE,
                                      SCANNEDDOCNO,
                                      SCANVALIDITY
                               FROM   PW001P05
                               UNION ALL 
                               SELECT PIN,
                                      SEQUENCENO,
                                      DATEISSUED AS DATEFROM,
                                      EXPIRYDATE,
                                      SCANNEDDOCNO,
                                      SCANVALIDITY
                               FROM   PW001P07
                               UNION ALL
                               SELECT PIN,
                                      SEQUENCENO,
                                      DATEFROM,
                                      DATETO AS EXPIRYDATE,
                                      SCANNEDDOCNO,
                                      SCANVALIDITY
                               FROM   PW001P08
                           ) docDetails
                           ON  docDetails.SEQUENCENO = doc.SEQUENCENO
                           AND docDetails.PIN = p01.PIN
                      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
       )
	   --ORDER BY PIN
       --AND final.pin = 800027

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.

Start Page

N/A

Adonis HR Reports, Views and Similar

Planned: Missing/Expiring Requirements (Competence, Travel, Medical)