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

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.

Click here to expand...

--Medical Documents 08/08/2022
CREATE VIEW dbo.PW001SRVXXX
(  
	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
)
 AS
SELECT 
	P07.PIN,
	P01.NAME,
	P01.FIRSTNAME,
	P01.LASTNAME,
	P01.MIDDLENAME,
	ORG.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 then NCHAR(9940)+' Missing'
		 when P07.scanvalidity=1 and scanneddocno is not null then NCHAR(10004)+' Confirmed'
		 when P07.scanvalidity=0 and scanneddocno is not null then NCHAR(10006)+' Not-Confirmed'
		 else 'N/A'
	end as 'Scan Status',
	P01.EMPLOYMENTSTARTDATE,
	P01.EMPLOYMENTENDDATE,
	P01.CLIENT NUMORGID
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

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