1 Description | 2 Main Data Selection | 3 Setup and Configuration | 4 Rows/ Columns Specification

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

 -- Time: 9/14/2023 7:11:26 PM
 -- IMPLADC-222
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(DAY, Number, DATEFROM) AS Onboard
                FROM   PWROT_ACTIVE_SHIFT
                       CROSS APPLY (
                    SELECT TOP(DATEDIFF(DAY, DATEFROM, DATETO) + 1)
                           ROW_NUMBER() OVER(
                               ORDER BY(
                                   SELECT NULL
                               )
                           ) - 1        AS Number
                    FROM   sys.columns  AS c1
                           CROSS JOIN sys.columns AS c2
                ) AS a
                WHERE  DATEFROM <= DATETO
            )               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
where Year(CheckDate.Onboard) = Year(Getdate())

Fields Definition

View Configuration (Column/Raw/Data Fields)

Rows/ Columns Specification

Row/Column	Description/ Location in APM

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.

Reports, Views and Similar

Crew Onboard vs Company Required Manning v.1 (current year)