Versions Compared

Version Old Version 2 New Version Current
Changes made by

Arvin John Salandanan

Daria Chebotaieva

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

Page Properties

Summary

The view shows if there are any documents with missing scans for the embarked crew.

Keywords

Documents, Scan

Category

Crew List View

Description

The view shows if there are any documents with missing scans for the embarked crew/staff.

View Sample

...

Main Data Selection

All seafarers whos has current sea-service activity.

SQL statement

Expand
Code Block
languagesql
CREATE VIEW dbo.PW001SRV83
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,
       ves.[NAME]               AS 'Vessel Name',
       dep.[NAME]               AS 'Department',
       pos.[NAME]               AS 'Position',
       nat.NATIONALITY,
       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,
                  P01.CLIENT        AS NUMORGID,
                  P01.EMPLOYMENTSTARTDATE,
                  P01.EMPLOYMENTENDDATE
           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]
           
           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,
                  P01.CLIENT        AS NUMORGID,
                  P01.EMPLOYMENTSTARTDATE,
                  P01.EMPLOYMENTENDDATE
           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
           
           
           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,
                  P01.CLIENT        AS NUMORGID,
                  P01.EMPLOYMENTSTARTDATE,
                  P01.EMPLOYMENTENDDATE
           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
       )                        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 (
                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 PW001P01 p01
            ON  p01.PIN = p03.PIN
       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 PWCOUNTRY nat
            ON  nat.COUNTRYCODE = p01.NATIONALITY
       LEFT JOIN PW001C43 c43
            ON  c43.CODE = p01.COSTPLACE
WHERE  MISS_DOC.SCAN IS            NULL

Columns Specification

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 Details > Personal > Payroll > Cost Place

Additional Information

You can highlight the expiration date in the view and define the period within which it should be highlighted.

...