Summary	Displays all the competences linked to a person.
Keywords	Documents, Competence

Description

The view displays all competence linked to a person.

View Sample

Mail data selection

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

SQL Statement

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

--Competence Documents
CREATE VIEW dbo.PW001SRVXXX
(
	PIN,
	"Full Name",
	"First Name",
	"Last  Name",
	"Middle Name",
	Organization,
	"Rank",
	"Document Code",
	"Document Name",
	"Document No",
	"Document Status",
	"Date From",
	"Date To",
	"Expiry Date",
	"Issued by",
	"Issued place",
	Comments,
	"Scan Status",
	NUMORGID,
	EMPLOYMENTSTARTDATE,
	EMPLOYMENTENDDATE,
	[Department/Cost Place Code],
	[Department/Cost Place]
)
AS
SELECT P01.PIN,
       P01.NAME,
       P01.FIRSTNAME,
       P01.LASTNAME,
       P01.MIDDLENAME,
       ORG.NAME,
       C02.[NAME]     AS RankName,
       C06.CODE       AS CompetenceCode,
       C06.[TEXT]     AS CompetenceName,
       P05.CNUMBER    AS No,
       CASE 
            WHEN (P05.PLANNED = ('N')) AND (P05.EXPIRYDATE > GETDATE() OR P05.EXPIRYDATE IS NULL) THEN 'Current'
            WHEN (P05.PLANNED = ('N')) AND (P05.EXPIRYDATE <= GETDATE() OR P05.EXPIRYDATE IS NULL) THEN 'Historical'
            WHEN (P05.PLANNED = ('Y')) THEN 'Planned'
            ELSE 'N/A'
       END               STATUS,
       P05.DATEFROM,
       P05.DATETO,
       P05.EXPIRYDATE,
       P05.ISSUEDBY,
       P05.ISSUEDPLACE,
       P05.COMMENTS,
       CASE 
            WHEN (P05.scanneddocno IS NULL OR doc.[DOCUMENT] IS NULL) THEN NCHAR(9940) + ' Missing'
            WHEN P05.scanvalidity = 1 AND scanneddocno IS NOT NULL THEN NCHAR(10004) + ' Confirmed'
            WHEN (P05.scanvalidity = 0 OR P05.scanvalidity IS NULL) AND scanneddocno IS NOT NULL THEN NCHAR(10006) + 
                 ' Not-Confirmed'
                 --ELSE 'N/A'
       END            AS scan,
       P01.CLIENT     AS NUMORGID,
       P01.EMPLOYMENTSTARTDATE,
       P01.EMPLOYMENTENDDATE,
       p01.COSTPLACE  AS 'Department/Cost Place Code',
       c43.[NAME]     AS 'Department/Cost Place'
FROM   PW001P01 P01
       JOIN PW001P05 P05
            ON  P05.PIN = P01.PIN
       LEFT JOIN PWORG ORG
            ON  ORG.NUMORGID = P01.CLIENT
       LEFT JOIN PW001C06 C06
            ON  C06.CODE = P05.CODE
       LEFT JOIN PW001C02 C02
            ON  C02.CODE = P01.[RANK]
       LEFT JOIN PW001OLEDOCS doc
            ON  doc.DOCNO = p05.scanneddocno
       LEFT JOIN PW001C43 c43
            ON  c43.CODE = p01.COSTPLACE

Field Specification

Column	Description/ Location in APM

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 From	The date from of the competence record linked to the selected crew.
Date To	The date to 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

Adonis HR Reports, Views and Similar

All: Competence Documents