Versions Compared

Version Old Version 1 New Version Current
Changes made by

Helen Ustych

Daria Chebotaieva

Saved on

Key

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

...

Page Properties

Summary

The view shows Medical Documents for all the crew.

Keywords

Medical Documents

Description

The view shows Medical Documents for all the crew

View Sample

...

Main Data Selection

Shows all the crew All crew members employed under the current organization and units below that have at least one record in PW001P07 (Medical Documents).

SQL statement

Please remember to update the View ID in the view from dbo.PW001SRVXXX to the correct one.

Expand
Code Block
--Medical Documents
08
09/
08
06/
2022
2023
CREATE VIEW dbo.
PW001SRV27
PW001SRVXXX
(
	PIN,
	"ALTERNATIVE PIN",
	"Full Name",
	"First Name",
	"Last  Name",
	"Middle Name",
	Organization,
	RANK,
	"Document Code",
	"Document Name",
	"Document Status",
	"Issued Date",
	"Expiry Date",
	"Issued by",
	Comments,
	"Scan Status",
	EMPLOYMENTSTARTDATE,
	EMPLOYMENTENDDATE,
	NUMORGID,
	[Department/Cost Place Code],
	[Department/Cost Place]
)
AS
SELECT 
P07.PIN,
       P01.ALTERNATIVEPIN  AS 'CAPE ID',
       P01.NAME,
P01.ALTERNATIVEPIN 'EMPLOYEE NR',
     P01.FIRSTNAME,
       P01.LASTNAME,
       P01.MIDDLENAME,
       ORG.NAME
'EMPLOYMENT ENTITY',
,
       C02.NAME
RANK,
,
       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,
       p01.COSTPLACE       AS 'Department/Cost Place Code',
       c43.[NAME]          AS 'Department/Cost Place'
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 PW001C43 c43
            ON  c43.CODE = p01.COSTPLACE

Field Specification

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.

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.

...

Start Page

N/A