Summary	Overview of all 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 all changes made to any activity of sick type done within the last 30 days

View Sample

Main Data Selection

Select all changes made to any sick activities done within the last 30 days. Ordered by latest modification on top.
Scenario: Possibility to view any changes made to existing sick activities.

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.

sql select statement

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
PIN	Select from Personal Details > Personal > Pin
NAME	LastName FirstName (no comma, only space) Select from Personal Details > Personal > Name
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

Changed made to Sick Activities