Summary	The view compares the actual number of crew onboard with the number of each Position defined in Organization > Position > Company Required Manning.
Keywords	Manning, Onboard Crew
Category	Analytical View

Description

The view compares the actual number of crew onboard with the number of each Position defined in Organization > Position > Company Required Manning.

Main Data Selection

The view checks all the crew that have Onboard (sea service) Activity on the date within the current year.

Setup and Configuration

SQL statement

sql select statement

SELECT P.PIN,
       P.NAME AS NAME,
       p03.DATEFROM as 'ACTIVITY DATE FROM',
       p03.DATETO as 'ACTIVITY DATE TO',
       p03.code AS ACTIVITY,
       orgpos.name AS POSITION,
       ISNULL(pos.mbr2, 0) AS 'COMPANY REQUIRED MANNING',
       CASE
           WHEN CHARINDEX ('.', dep.name ) > 0 THEN  SUBSTRING(dep.name, 5, LEN(dep.name) - 4)
           ELSE dep.name
       END AS DEPARTMENT,
       ves.name AS VESSEL,
       CONVERT(DATETIME, CONVERT(VARCHAR, CheckDate.Onboard, 112)) AS 'ONBOARD ON',
       1 AS 'CREW COUNT',
       P.CLIENT NUMORGID,
       P.EMPLOYMENTSTARTDATE,
       P.EMPLOYMENTENDDATE
FROM PW001P01 AS P
     JOIN PW001P03 AS P03
          ON P.PIN = P03.PIN
     JOIN PW001C12 AS C12
          ON P03.CODE = C12.CODE
         AND C12.OPTIONS LIKE '%S%'
     JOIN(
          SELECT DISTINCT
                 DATEADD(d, day, DATEADD(day, -60, CONVERT(VARCHAR, GETDATE(), 112))) AS Onboard
          FROM(
               SELECT DISTINCT
                      id % 365 + 1 AS day
               FROM sysobjects)AS a)AS CheckDate
          ON p03.datefrom <= CheckDate.Onboard
         AND ISNULL(p03.dateto, CheckDate.Onboard) >= CheckDate.Onboard
         AND CheckDate.Onboard <= DATEADD(day, 60, CONVERT(VARCHAR, GETDATE(), 112))
     LEFT JOIN PWORG AS orgpos
          ON dbo.ad_scanorgtree(p03.numorgid, 5) = orgpos.NUMORGID
     LEFT JOIN PWORGPOS AS pos
          ON pos.NUMORGID = orgpos.NUMORGID
     LEFT JOIN PW001C02 AS C02
          ON dbo.ad_orgPosC02Code(orgpos.orgcode) = c02.code
     LEFT JOIN PWORG AS DEP
          ON dbo.ad_scanorgtree(p03.numorgid, 4) = DEP.NUMORGID
     LEFT JOIN PWORG AS VES
          ON dbo.ad_scanorgtree(p03.numorgid, 3) = VES.NUMORGID

Fields Definition

View Configuration (Column/Raw/Data Fields)

Rows/ Columns Specification

Row/Column	Description/ Location in APM
PIN	PIN (should be hidden)
Name	The full name of the person.
NUMORGID	Vessel Organization ID (should be hidden)
EmploymentStartDate	Employment Start Date (can be hidden)
EmploymentEndDate	Employment End Date (can be hidden)
Vessel	Onboard Activity Vessel name
Department	Onboard Activity Department name
Position	Onboard Activity Position
Onboard On	The date when the crew are onboard.
Required Manning	The number of Position according to Company requirements: Organization > Position > Company Required Manning
Crew Count	Total number of the crew that have Onboard Activities on the specific date.

Crew Onboard vs Company Required Manning v.1