Skip to end of banner
Go to start of banner

Competence with Invalid Scan

Skip to end of metadata
Go to start of metadata

You are viewing an old version of this content. View the current version.

Compare with Current View Version History

Version 1 Next »

Summary

The view shows if there are any competence documents with not verified scans: where the “Validity of Scan” in set to “N”.

Keywords

Competence Documents, Scan

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/ staff.

View Sample

Mail data selection

All crew members employed under the current organization and units below that have at least one document (Travel, Medical, Competence) without invalid 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.

 Click here to expand...
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

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 Type

The type of document without scan linked to the selected crew.

Document Code

The code of the document record linked to the selected crew.

Document Name

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

Date Issued

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

Expiry Date

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

Comments

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

Additional Information

N/A

Start Page

N/A

  • No labels