Summary	The view shows if there are any documents with not verified scans: where the “Validity of Scan” in set to “N” for the embarked crew/ staff.
Keywords	Documents, Scan
Category	Crew List View

Description

The view shows if there are any documents with not valid scans: where the “Validity of Scan” in set to “N” for the embarked crew/ staff.

View Sample

Main Data Selection

All seafarers whos has current sea-service activity.

SQL statement

CREATE VIEW dbo.PW001SRV84
AS

SELECT MISS_DOC.PIN             AS PIN,
       MISS_DOC.ALTERNATIVEPIN  AS 'ALTERNATIVE PIN',
       MISS_DOC.Full_Name       AS 'Full Name',
       MISS_DOC.First_Name      AS 'First Name',
       MISS_DOC.Last_Name       AS 'Last Name',
       MISS_DOC.Middle_Name 'Middle Name',
       MISS_DOC.Organization    AS 'Organization Name',
       MISS_DOC.Rank            AS 'Rank',
       MISS_DOC.DOC_TYPE        AS 'Document type',
       MISS_DOC.Document_Code   AS 'Document Code',
       MISS_DOC.Document_Name   AS 'Document Name',
       MISS_DOC.Date_Issued     AS 'Date Issued',
       MISS_DOC.Expiry_Date     AS 'Expiry Date',
       MISS_DOC.COMMENTS,
       MISS_DOC.NUMORGID        AS NUMORGID,
       MISS_DOC.EMPLOYMENTSTARTDATE,
       MISS_DOC.EMPLOYMENTENDDATE,
       ou.VesselID              AS VESSEl_NUMORGID,
       MISS_DOC.NATIONALITY,
       ves.[NAME]               AS 'Vessel Name',
       dep.[NAME]               AS 'Department',
       pos.[NAME]               AS 'Position',
       p01.COSTPLACE            AS 'Department/Cost Place Code',
       c43.[NAME]               AS 'Department/Cost Place'
FROM   (
           --Competence Documents
           SELECT P01.PIN           AS PIN,
                  P01.ALTERNATIVEPIN,
                  P01.NAME          AS Full_Name,
                  P01.FIRSTNAME     AS First_Name,
                  P01.LASTNAME      AS Last_Name,
                  P01.MIDDLENAME       Middle_Name,
                  ORG.NAME          AS Organization,
                  C02.[NAME]        AS RANK,
                  'Competence'      AS DOC_TYPE,
                  C06.CODE          AS Document_Code,
                  C06.[TEXT]        AS Document_Name,
                  P05.DATEFROM      AS Date_Issued,
                  P05.EXPIRYDATE    AS EXPIRY_DATE,
                  P05.COMMENTS,
                  P05.scanneddocno  AS SCAN,
                  P05.SCANVALIDITY  AS SCAN_Validity,
                  P01.CLIENT        AS NUMORGID,
                  P01.EMPLOYMENTSTARTDATE,
                  P01.EMPLOYMENTENDDATE,
                  doc.[DOCUMENT],
                  nat.NATIONALITY
           FROM   PW001P01 P01
                  JOIN PW001P05 P05
                       ON  P05.PIN = P01.PIN
                  LEFT JOIN PWORG ORG
                       ON  ORG.NUMORGID = P01.CLIENT
                  LEFT JOIN PW001C06 C06
                       ON  C06.CODE = P05.CODE
                  LEFT JOIN PW001C02 C02
                       ON  C02.CODE = P01.[RANK]
                  LEFT JOIN PW001OLEDOCS doc
                       ON  doc.DOCNO = p05.scanneddocno
                  LEFT JOIN PWCOUNTRY nat
                       ON  nat.COUNTRYCODE = p01.NATIONALITY
           UNION ALL
           --Medical Documents 08/08/2022
           SELECT P01.PIN           AS PIN,
                  P01.ALTERNATIVEPIN,
                  P01.NAME          AS Full_Name,
                  P01.FIRSTNAME     AS First_Name,
                  P01.LASTNAME      AS Last_Name,
                  P01.MIDDLENAME       Middle_Name,
                  ORG.NAME          AS Organization,
                  C02.[NAME]        AS RANK,
                  'Medical'         AS DOC_TYPE,
                  C24.CODE          AS Document_Code,
                  C24.[TEXT]        AS Document_Name,
                  P07.DATEISSUED    AS Date_Issued,
                  P07.EXPIRYDATE    AS EXPIRY_DATE,
                  P07.COMMENTS,
                  P07.scanneddocno  AS SCAN,
                  P07.SCANVALIDITY  AS SCAN_Validity,
                  P01.CLIENT        AS NUMORGID,
                  P01.EMPLOYMENTSTARTDATE,
                  P01.EMPLOYMENTENDDATE,
                  doc.[DOCUMENT],
                  nat.NATIONALITY
           FROM   PW001P07 P07
                  LEFT JOIN PW001P01 P01
                       ON  P01.PIN = P07.PIN
                  LEFT JOIN PWORG ORG
                       ON  ORG.NUMORGID = P01.CLIENT
                  LEFT JOIN PW001C24 C24
                       ON  C24.CODE = P07.CODE
                  LEFT JOIN PW001C02 C02
                       ON  C02.CODE = P01.RANK
                  LEFT JOIN PW001OLEDOCS doc
                       ON  doc.DOCNO = p07.scanneddocno
                  LEFT JOIN PWCOUNTRY nat
                       ON  nat.COUNTRYCODE = p01.NATIONALITY
           UNION ALL
           --Travel Documents
           SELECT P01.PIN           AS PIN,
                  P01.ALTERNATIVEPIN,
                  P01.NAME          AS Full_Name,
                  P01.FIRSTNAME     AS First_Name,
                  P01.LASTNAME      AS Last_Name,
                  P01.MIDDLENAME       Middle_Name,
                  ORG.NAME          AS Organization,
                  C02.[NAME]        AS RANK,
                  'Travel'          AS DOC_TYPE,
                  C23.CODE          AS Document_Code,
                  C23.VISATYPE      AS Document_Name,
                  P08.DATEFROM      AS Date_Issued,
                  P08.DATETO        AS EXPIRY_DATE,
                  P08.COMMENTS,
                  P08.scanneddocno  AS SCAN,
                  P08.SCANVALIDITY  AS SCAN_Validity,
                  P01.CLIENT        AS NUMORGID,
                  P01.EMPLOYMENTSTARTDATE,
                  P01.EMPLOYMENTENDDATE,
                  doc.[DOCUMENT],
                  nat.COUNTRYNAME
           FROM   PW001P08 P08
                  LEFT JOIN PW001P01 P01
                       ON  P01.PIN = P08.PIN
                  LEFT JOIN PWORG ORG
                       ON  ORG.NUMORGID = P01.CLIENT
                  LEFT JOIN PW001C23 C23
                       ON  C23.CODE = P08.CODE
                  LEFT JOIN PW001C02 C02
                       ON  C02.CODE = P01.RANK
                  LEFT JOIN PW001OLEDOCS doc
                       ON  doc.DOCNO = p08.scanneddocno
                  LEFT JOIN PWCOUNTRY nat
                       ON  nat.COUNTRYCODE = p01.NATIONALITY
       )                        AS MISS_DOC
       JOIN PW001P03 p03
            ON  p03.PIN = MISS_DOC.PIN
            AND p03.DATETO IS NULL
            AND p03.PLANNED <> 'Y'
            AND p03.CODE IN (SELECT c12.Code
                             FROM   pw001c12 c12
                             WHERE  c12.OPTIONS LIKE '%S%')
       LEFT JOIN PW001P01 p01
            ON  p01.PIN = p03.PIN
       LEFT JOIN (
                SELECT NUMORGID,
                       dbo.ad_scanorgtree(NUMORGID, 3) AS VesselID,
                       dbo.ad_scanorgtree(NUMORGID, 4) AS DeptID
                FROM   pworg
            ) ou
            ON  ou.NUMORGID = p03.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 = p03.NUMORGID
       LEFT JOIN PW001C43 c43
            ON  c43.CODE = p01.COSTPLACE
WHERE  (MISS_DOC.SCAN_Validity = 0 AND SCAN IS NOT NULL)
       OR  (MISS_DOC.SCAN_Validity = 1 AND DOCUMENT 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
Department/Cost Place Code	Personal Details > Personal > Payroll > Cost Place Code
Department/Cost Place	Personal > Personal > Payroll > Cost Place Code

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: Scan not valid