Summary	The view shows if there are any competence documents with not verified scans: where the ‘Validity of Scan’ in set to 'N'
Keywords	Personal Details, Employment
Category	Crew List View

Description

The view shows if there are any competence documents with not valid scans: where the ‘Validity of Scan’ in set to 'N' for the employed crew.

View Sample

Mail data selection

All crew members employed under the current organization and units below that have at least one Competence document without valid scan.

SQL Statement

VIEWID should be substituted with the id of the crew list view that you are creating in your system e.g. PW001SRV13 etc.

CREATE VIEW dbo.PW001SRV13 AS

Select
	p.PIN,
	p.CLIENT NUMORGID,
	p.EMPLOYMENTSTARTDATE,
	p.EMPLOYMENTENDDATE,
	p.NAME,
	(SELECT NAME from PW001C02 WHERE CODE = p.RANK) 'RANK',
	c.CODE 'COMPETENCE CODE',
	(SELECT TEXT FROM PW001C06 WHERE CODE=c.CODE) 'COMPETENCE TEXT',
	c.CNUMBER 'COMPETENCE NUMBER',
	c.DATEFROM 'ISSUE DATE',
	Isnull(c.EXPIRYDATE,c.DATETO) 'EXPIRY DATE',
	c.CHANGETIME 'LASTUPDATED',
	Isnull((SELECT LOGINNAME FROM PWLOGINSESSION WHERE SESSIONID=c.SESSIONID),
			(SELECT LOGIN_NAME FROM PWSECURITY_USERS WHERE USERID=c.CHANGEDBY)) 'LASTUPDATEDBY',
	Case When SCANVALIDITY = 1 Then 'Y' else 'N' End 'VALIDITYOFSCAN'
From PW001P01 p
join PW001P05 c on p.PIN=c.PIN and c.scanneddocno is not null and c.SCANVALIDITY<>1

Field Specification

Column	Description/ Location in APM

Column	Description/ Location in APM
Pin	PIN
Employment Start Date	Select from Personal Details > Employment > Employment State > Start Format dd/mm/yyyy
Employment End Date	Select from Personal Details > Employment > Employment State > End Format dd/mm/yyyy
Name	Full name of the person in Personal Details. Format: L_F_M
Rank	Current Rank of the person in Personal Details.
Competence Code	The code of the competence document record linked to the selected crew.
Competence Text	The name of the competence document record linked to the selected crew.
Competence Number	The number of the competence document record linked to the selected crew.
Issued Date	The date issued from of the competence document record linked to the selected crew.
Expiry Date	The expiry date of the competence document record linked to the selected crew.
Last Updated	Date / time last update action was done to this competence document
Last Updated By	Login name who did last update action to this competence document
Validity of Scan	'N'

Start Page

N/A

Adonis HR Reports, Views and Similar

Competence: Scan not valid

Analytics