Versions Compared

Version Old Version 1 New Version 2
Changes made by

Anna Zelenskaya

Anna Zelenskaya

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Table of Contents
minLevel1
maxLevel1
typeflat
separatorpipe

...

This section is only visible for the Adonis employees.

Customer

The view was originally created for Eckero Group

Summary

The view lists the crew who uploaded documents themselves from APP > Employee Portal. It shows competence, travel and enclosed documents uploaded from the APP for the last two months.

Keywords

Competence / Travel Documents, Scans

Category

Crew List View

Description

The view lists the crew who uploaded competence, travel and enclosed documents themselves from APP for the last two months.

...

Code Block
languagesql
CREATE VIEW dbo.PW001SRV28 AS
SELECT 
	p.PIN,
	p.CLIENT NUMORGID,
	p.EMPLOYMENTSTARTDATE,
	p.EMPLOYMENTENDDATE,
	p.FIRSTNAME,
	p.LASTNAME,
	o.[NAME] Organization,
	d.[Type],
	d.Code,
	d.Document,
	d.Number,
	d.[Scan Attached],
	d.[Scan Valid],
	d.CREATETIME

From PW001P01 p
Join	(Select
			c.[PIN],
			'Competence' [Type],
			c.CODE [Code],
			(Select [TEXT] from PW001C06 where CODE=c.CODE) [Document],
			c.CNUMBER [Number],
			Case When c.scanneddocno is not null and Convert(int,c.scanneddocno) in (select docno from PW001OLEDOCS) Then NCHAR(10004) Else '' End [Scan Attached],
			Case When c.SCANVALIDITY=1 Then NCHAR(10004) Else 'Scan has not been approved' End [Scan Valid],
			c.[COMMENTS],
			c.CREATETIME
		From PW001P05 c
		where c.CREATEDBY = 'APP'
		Union All
		Select
			tr.[PIN],
			'Travel' [Type],
			tr.CODE [Code],
			(Select VISATYPE from PW001C23 where CODE=tr.CODE) [Document],
			tr.TDNUMBER [NUMBER],
			Case When tr.scanneddocno is not null and Convert(int,tr.scanneddocno) in (select docno from PW001OLEDOCS) Then NCHAR(10004) Else '' End [Scan Attached],
			Case When tr.SCANVALIDITY=1 Then NCHAR(10004) Else 'Scan has not been approved' End [Scan Valid],
			tr.[COMMENTS],
			tr.CREATETIME
		From PW001P08 tr
		where tr.CREATEDBY = 'APP'
		Union All
		Select
			ole.[PIN],
			'Enclosed' [Type],
			s.DOCTYPE [Code],
			(Select [NAME] from PW001C45 where CODE=s.DOCTYPE) [Document],
			'N/A' [NUMBER],
			NCHAR(10004) [Scan Attached],
			'N/A' [Scan Valid],
			s.[DESCRIPTION] [COMMENTS],
			s.CREATETIME
		From PW001P01OLE ole
		join PW001OLEDOCS s on ole.DOCNO=s.DOCNO
		where s.CREATEDBY = 'APP' and s.DOCTYPE!='MED') d on d.PIN = p.PIN and d.CREATETIME>=DATEADD(day,-60,getutcdate())
Join PWORG o on p.CLIENT = o.NUMORGID

Field Specification

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