Summary	This view shows missing/expiring Requirements (Competence, Travel, Medical) for the crew who have current sea-service activity.
Keywords	Required Documents
Category	Crew List View

Description

This view shows missing/expiring Requirements (Competence, Travel, Medical) for the crew who has current sea-service activity 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 whos has current sea-service activity.

SQL statement

CREATE VIEW dbo.PW001SRV86
AS

SELECT PIN,
       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,
                  p03cur.DATEFROM        AS 'Embarkation Date',
                  ISNULL(p03cur.DATETO, p03cur.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 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
                  ) 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 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 dbo.PW001P03 P03cur
                       ON  P01.PIN = P03cur.PIN
                       AND P03cur.CODE IN (SELECT c12.CODE
                                           FROM   PW001C12 c12
                                           WHERE  c12.OPTIONS LIKE '%S%')
                       AND (P03cur.DATETO IS NULL OR P03cur.DATETO >= GETDATE())
                       AND P03cur.DATEFROM <= GETDATE()
                       AND P03cur.PLANNED <> 'Y'
                  LEFT JOIN (
                           SELECT NUMORGID,
                                  dbo.ad_scanorgtree(NUMORGID, 3) AS VesselID,
                                  dbo.ad_scanorgtree(NUMORGID, 4) AS DeptID
                           FROM   pworg
                       ) ou
                       ON  ou.NUMORGID = p03cur.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 = p03cur.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	PIN
Alternative PIN	Alternative PIN
Full Name	Full name of the person in Personal Details.
First Name	First name of the person in Personal Details.
Last Name	Last name of the person in PersonalDetails.
Middle Name	Middle name of the person in Personal Details.
Organizaton	Current employment organization of the person in Personal Details.
Rank	Current Rank of the person in Personal Details.
Document Type	The type of the document linked to the selected crew (Competence,Travel,Medical)
Document Code	The code of the medical record linked to the selected crew.
Document Name	The name of the medical record linked to the selected crew.
Document Status	The status of the medical record linked to the selected crew.
Issue Date	The issue from of the medical record linked to the selected crew.
Expiry Date	The expert date of the medical record linked to the selected crew.
Issued by	The Issued By field of the medical record linked to the selected crew.
Comments	The comments added to the medical record linked to the selected crew.
Scan Status	The status of the scanned document linked to the medical record linked for the selected crew.
EMPLOYMENTSTARTDATE	Personal Details > Employment > Employment Start Date
EMPLOYMENTENDDATE	Personal Details > Employment > Employment End Date
NUMORGID	Personal Details > Client > NUMORGID
VESSEL_NUMORGID	Shows the vessels numorgid of the Embarked activity.
Vessel Name	Shows the vessel name of the Embarked activity.
Department	Shows the department name of the Embarked activity.
Position	Shows the positon name of the Embarked activity.
Nationality	Personal Details > Personal > Nationality

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.

Adonis HR Reports, Views and Similar

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