Summary	The view shows Medical Documents of the onboard crew.
Keywords	Medical Documents
Category	Crew List View

Description

Displays all the medical records of the embarked crew.

View Sample

Main Data Selection

All seafarers who have current sea-service activity.

SQL statement

CREATE VIEW dbo.PW001SRV81
(
	PIN,
	"ALTERNATIVE PIN",
	"Full Name",
	"First Name",
	"Last  Name",
	"Middle Name",
	[Organization Name],
	RANK,
	"Document Code",
	"Document Name",
	"Document Status",
	"Issued Date",
	"Expiry Date",
	"Issued by",
	Comments,
	"Scan Status",
	EMPLOYMENTSTARTDATE,
	EMPLOYMENTENDDATE,
	NUMORGID,
	VESSEL_NUMORGID,
	[Vessel Name],
	[Department],
	[Position],
	[Nationality],
	[Department/Cost Place Code],
	[Department/Cost Place]
)
AS
SELECT P07.PIN,
       P01.ALTERNATIVEPIN  AS 'CAPE ID',
       P01.NAME,
       P01.FIRSTNAME,
       P01.LASTNAME,
       P01.MIDDLENAME,
       ORG.NAME            AS [Organization Name],
       C02.NAME,
       P07.CODE,
       C24.TEXT               MEDICAL,
       CASE 
            WHEN (P07.PLANNED = ('N')) AND (P07.EXPIRYDATE > GETDATE() OR P07.EXPIRYDATE IS NULL) THEN 'Current'
            WHEN (P07.PLANNED = ('N')) AND (P07.EXPIRYDATE <= GETDATE() OR P07.EXPIRYDATE IS NULL) THEN 'Historical'
            WHEN (P07.PLANNED = ('Y')) THEN 'Planned'
            ELSE 'N/A'
       END                 AS 'Document Status',
       p07.DATEISSUED,
       P07.EXPIRYDATE,
       P07.ISSUEDBY,
       P07.COMMENTS,
       CASE 
            WHEN (P07.scanneddocno IS NULL OR doc.[DOCUMENT] IS NULL) THEN NCHAR(9940) + ' Missing'
            WHEN P07.scanvalidity = 1 AND scanneddocno IS NOT NULL THEN NCHAR(10004) + ' Confirmed'
            WHEN (P07.scanvalidity = 0 OR P07.scanvalidity IS NULL) AND scanneddocno IS NOT NULL THEN NCHAR(10006) +
                 ' Not-Confirmed'
                 --ELSE 'N/A'
       END                 AS 'Scan Status',
       P01.EMPLOYMENTSTARTDATE,
       P01.EMPLOYMENTENDDATE,
       P01.CLIENT             NUMORGID,
       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   PW001P07 P07
       JOIN PW001P03 p03
            ON  p03.PIN = p07.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 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 PW001P01 P01
            ON  P01.PIN = P07.PIN
       LEFT JOIN PWORG ORG
            ON  ORG.NUMORGID = P01.CLIENT
       LEFT JOIN PWCOUNTRY nat
            ON  nat.COUNTRYCODE = p01.NATIONALITY
       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 PW001C43 c43
            ON  c43.CODE = p01.COSTPLACE

Columns Specification

Column	Description/ Location in APM

Column	Description/ Location in APM
PIN	PIN
Full Name	Full name of the person in Perosonal Details.
First Name	First name of the person in Perosonal Details.
Last Name	Last name of the person in Perosonal Details.
Middle Name	Middle name of the person in Perosonal Details.
Organizaton	Current employment organization of the person in Perosonal Details.
Rank	Current Rank of the person in Perosonal Details.
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.

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.

Reports, Views and Similar

Medical Documents - Onboard