Versions Compared

Version Old Version 2 New Version 3
Changes made by

Anna Zelenskaya

Anna Zelenskaya

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

Page Properties

Summary

Overview of latest changes made to any activity of sick type done within the last 30 days

Keywords

Sick Activities, Audits

Category

Crew List View

Description

Overview of latest changes made to any activity of sick type done within the last 30 days

View Sample

...

Main Data Selection

Crew members employed under the current organization and units below.

...

This view is especially important to Sick Administrator to give heads-up if they need to prolong any compensation claims to insurance companies / authorities.

SQL statement

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

Expand
titlesql select statement
Code Block
CREATE VIEW dbo.PW001SRV47 AS
Select
p03.repl_ModifiedDate as DATE_MODIFIED,
psec.USER_NAME as MODIFIED_BY,
p01.PIN,
p01.NAME,
p03.CODE,
c12.TEXT as CODE_TEXT,
p03.DATEFROM as DATE_FROM,
p03.DATETO as DATE_TO,
p03.TODATEESTIMATED as ESTIMATED_END_DATE,
p03.DAYS as NO_OF_DAYS,
p03.VESSELNAME,
c02.NAME as RANK,
p01.Client AS NUMORGID,
p01.EMPLOYMENTSTARTDATE,
p01.EMPLOYMENTENDDATE
from pw001p01 p01
JOIN pw001p03 p03
	on p01.PIN = p03.PIN
	--and p03.HISTORICAL = 'T'
	and p03.CODE LIKE '%SICK%'
	and DATEDIFF(day,p03.repl_ModifiedDate,GETDATE()) < 30
	--and (P03.DATETO IS NULL AND PLANNED <> 'Y')
	--and(p03.DATETO IS NULL OR p03.DATETO >= GETDATE())
	--and p03.DATEFROM <= GETDATE()
	--and p03.TODATEESTIMATED >= GETDATE()
LEFT JOIN pw001c12 c12
	on p03.CODE = c12.CODE
LEFT JOIN pw001c02 c02
	on p01.RANK = c02.CODE
LEFT JOIN PWSECURITY_USERS psec
	on p03.CHANGEDBY = psec.USERID
	and psec.SITENR = 1

Columns Specification

Column

Description/ Location in APM

DATE MODIFIED

Modified Date/time

MODIFIED BY

Login Name

PIN

Select from Personal Details > Personal > Pin (whose sick activity was modified)

NAME

LastName FirstName (no comma, only space)

Select from Personal Details > Personal > Name (whose sick activity was modified)

CODE TEXT

Select from activity of sick type > Details > Code

TEXT

Select from activity of sick type > Details > Code Text

DATE FROM

Select from activity of sick type > Details > Date From

DATE TO

Select from activity of sick type > Details > Date To

ESTIMATED END DATE

Select from activity of sick type > Details > EED

NO OF DAYS

Number of days from activity in question

VESSEL NAME

Select from activity of sick type > Details > Vessel

name of the vessel activity is linked

RANK

Select from Personal Details > Employment, Rank, Current Rank; name of current rank

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

Start Page

N/A