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 currently onboard or 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 m.PIN,
       m.ALTERNATIVEPIN  as 'ALTERNATIVE PIN',
       m.[Full Name],
	   m.[Organization],
       m.[Current Rank],
       m.[Nationality],
       m.[Document type],
       m.[Document Code],
       m.[Document Name],
       m.[Document Status],
       m.[Issued date],
       m.[Expiry date],
       m.EMPLOYMENTSTARTDATE,
       m.EMPLOYMENTENDDATE,
       m.NUMORGID
FROM   (
           SELECT DISTINCT P01.PIN,
                  P01.ALTERNATIVEPIN,
                  p01.NAME as 'Full Name',
				  ORG.Name as 'Organization',
                  c02.NAME        AS 'Current Rank',
                  nat.NATIONALITY as 'Nationality',
                  doc.Type        AS 'Document type',
                  doc.status as 'Document Status',
                  doc.Code 'Document Code',
                  doc.DocName     AS 'Document Name',
                  doc.DATEFROM    AS 'Issued date',
                  doc.EXPIRYDATE  AS 'Expiry date',
                  P01.CLIENT         NUMORGID,
                  P01.EMPLOYMENTSTARTDATE,
                  P01.EMPLOYMENTENDDATE
           FROM   PW001P01 p01
                  --Required document per position
                  JOIN (
                           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,
                                  c06.TEXT AS DocName,
                                  p05.DATEFROM,
                                  p05.EXPIRYDATE
                                  --link to sailing activity
                           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%'
                                             LEFT JOIN PWORGPRC prc
                                                  ON  prc.NUMORGID = p03.NUMORGID
                                      WHERE  p03.DATETO >= CONVERT(VARCHAR, GETDATE(), 112)
                                             OR  p03.DATETO IS NULL
                                  ) prc
                                  LEFT JOIN PW001C06 c06
                                       ON  c06.CODE = prc.COMPETENCE
                                  LEFT JOIN PW001P05 p05
                                       ON  p05.PIN = prc.PIN
                                       AND prc.COMPETENCE = p05.CODE
                           --required medical/travel documents on org level
                           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.CODE IS NULL THEN 'Missing'
                                       WHEN p07.CODE IS NULL THEN 'Missing'
                                       WHEN p08.CODE IS NOT NULL THEN 'Expiring'
                                       WHEN p07.CODE IS NOT NULL THEN 'Expiring'
                                  END  AS STATUS,
                                  rtd.DOCCODE AS Code,
                                  CASE rtd.DOCTYPE
                                       WHEN 8 THEN c23.VISATYPE
                                       WHEN 7 THEN c24.TEXT
                                  END  AS DocName,
                                  CASE rtd.DOCTYPE
                                       WHEN 8 THEN p08.DATEFROM
                                       WHEN 7 THEN p07.DATEISSUED
                                  END  AS DateFrom,
                                  CASE rtd.DOCTYPE
                                       WHEN 8 THEN p08.DATETO
                                       WHEN 7 THEN p07.EXPIRYDATE
                                  END  AS ExpiryDate
                           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
                           -- required travel/docs per nationality
                           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 p07.CODE IS NULL THEN 'Missing'
                                       WHEN p07.CODE IS NOT NULL THEN 'Expiring'
                                  END  AS STATUS,
                                  reqDoc.[DOCUMENT] AS Code,
                                  CASE reqDoc.DOCTYPE
                                       WHEN 8 THEN c23.VISATYPE
                                       WHEN 7 THEN c24.TEXT
                                  END  AS DocName,
                                  CASE reqDoc.DOCTYPE
                                       WHEN 8 THEN p08.DATEFROM
                                       WHEN 7 THEN p07.DATEISSUED
                                  END  AS DateFrom,
                                  CASE reqDoc.DOCTYPE
                                       WHEN 8 THEN p08.DATETO
                                       WHEN 7 THEN p07.EXPIRYDATE
                                  END  AS ExpiryDate
                           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
                       ON  doc.PIN = p01.PIN
                  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 PWORG ORG
					   ON ORG.NUMORGID=P01.CLIENT

                           --ORDER BY
                           --       1
       )m

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	Personal Details > Employment > Organization’s Name
Current Rank	Personal Details > Employment > Current Rank
Numorgid	Personal Details > Employment > Organization’s No
Document Code	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

Start Page

N/A

Reports, Views and Similar

Missing/Expiring Requirements (Competence, Travel, Medical)