Versions Compared

Version Old Version 1 New Version 2
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 all the Travel documents for all the crew.

Keywords

Documents, Travel Documents

Description

The view shows all the Travel documents for all the crew

...

.

View Sample

...

Mail data selection

All the crew crew members employed under the current organization and units below that have at least one record in PW001P08 (Travel Documents).

SQL statement

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

Code Block
--Travel Docuements 08/08/2022
Documents
CREATE VIEW dbo.PW001SRV26PW001SRVXXX 
(
	PIN,
	"Full Name",
	"First Name",
	"Last  Name",
	"Middle Name",
	Organization,
	"Rank",
	"Document Code",
	"Document Name",
	"Document No",
	"Document Status",
	"Date Issued",
	"Expiry Date",
	"Issued by",
	"Issued place",
	Comments,
	"Scan Status",
	NUMORGID,
	EMPLOYMENTSTARTDATE,
	EMPLOYMENTENDDATE

) AS
SELECT 
	P08.PIN,
	P01.NAME,
	P01.ALTERNATIVEPIN 'EMPLOYEE NR',
FIRSTNAME,
	P01.LASTNAME,
	P01.MIDDLENAME,
	ORG.NAME 'EMPLOYMENT ENTITYORG',
P01.NAME,
	C02.NAME RANK,
	P08.CODE 'DOCUMENT CODE',
	C23.VISATYPE TRAVEL,
P08.DATEFROM,
 'DOCUMENT Name',
	P08.TDNUMBER NUMBER,
	case when (P08.DATETO > GETDATE() or P08.DATETO is NULL) then 'Current'
		 when (P08.DATETO <= GETDATE() or P08.DATETO is NULL) then 'Historical'
		 else 'N/A'
	END Status,
	P08.DATEFROM ISSUED,
	P08.DATETO EXPIRY,
	P08.ISSUEDBY,
	p08.ISSUE_COUNTRY,
	P08.ISSUEDBY,
P08.TDNUMBER NUMBER,
P08.COMMENTS,
P01.EMPLOYMENTSTARTDATE,
P01.EMPLOYMENTENDDATE,
P01.CLIENT NUMORGID
COMMENTS,
	case when P08.scanneddocno is NULL then NCHAR(9940)+' Missing'
		 when P08.scanvalidity=1 and scanneddocno is not null then NCHAR(10004)+' Confirmed'
		 when P08.scanvalidity=0 and scanneddocno is not null then NCHAR(10006)+' Not-Confirmed'
		 else 'N/A'
	end as scan,
	P01.CLIENT 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

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 competence record linked to the selected crew.

Document Name

The name of the competence record linked to the selected crew.

Document No

The number of the competence record linked to the selected crew.

Document Status

The status of the competence record linked to the selected crew.

Date Issued

The date from of the competence record linked to the selected crew.

Expiry Date

The expert date of the competence record linked to the selected crew.

Issued by

The Issued By field of the competence record linked to the selected crew.

Issued place

The Issued Place field of the competence record linked to the selected crew.

Comments

The comments added to the competence record linked to the selected crew.

Scan Status

The status of the scanned document linked to the competence 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