1 Description | 2 View Sample | 3 Main Data Selection | 4 SQL statement | 5 Columns Specification

Summary	A view to show all existing “Crew Relations“
Keywords
Category	Crew List View

Description

The view shows all relations defined in APM under the Personal Details > Onboard > Relations along with the current activity details of the crew.

View Sample

Main Data Selection

All crew with defined “Relations“ under Personal Details > Onboard > Relations in APM.

SQL statement

CREATE VIEW dbo.PW001SRV260 AS
SELECT P01.PIN,
       P01.NAME,
       P0R.PIN2,
       P01P2.NAME              RELATION_WITH_NAME,
       CASE 
            WHEN P0R.RELATION = 1 THEN 'Excellent'
            WHEN P0R.RELATION = 2 THEN 'Good'
            WHEN P0R.RELATION = 3 THEN 'Average'
            WHEN P0R.RELATION = 4 THEN 'Poor'
            WHEN P0R.RELATION = 5 THEN 'Very Poor'
       END                     RELATION_MARK,
       C07.TEXT                RELATIONSHIP,
       C12.TEXT                ACTIVITY,
       P03.DATEFROM            FROMDATE,
       P03.TODATEESTIMATED     ESTIMATEEND,
       VSL.NAME                VESSEL,
       P01.CLIENT              NUMORGID,
       P01.EMPLOYMENTSTARTDATE,
       P01.EMPLOYMENTENDDATE
FROM   dbo.PW001P01 P01
       JOIN PW001P0R P0R
            ON  P01.PIN = P0R.PIN1
            AND P0R.RELATION IS NOT NULL
       JOIN PW001P01 P01P2
            ON  P01P2.PIN = P0R.PIN2
       LEFT JOIN PW001C07 C07
            ON  C07.CODE = P0R.RELATIONSHIP
       LEFT JOIN dbo.PW001P03 P03
            ON  P01.PIN = P03.PIN
            AND (P03.DATETO IS NULL OR P03.DATETO >= GETDATE())
            AND P03.DATEFROM <= GETDATE()
            AND P03.PLANNED <> 'Y'
       LEFT JOIN PWORG VSL
            ON  VSL.NUMORGID = dbo.ad_scanorgtree(P03.NUMORGID, 3)
       LEFT JOIN PW001C12 C12
            ON  C12.CODE = P03.CODE
WHERE  P0R.RELATION IS NOT     NULL
UNION
SELECT P01.PIN,
       P01.NAME,
       P0R.PIN1,
       P01P1.NAME              RELATION_WITH_NAME,
       CASE 
            WHEN P0R.RELATION = 1 THEN 'Excellent'
            WHEN P0R.RELATION = 2 THEN 'Good'
            WHEN P0R.RELATION = 3 THEN 'Average'
            WHEN P0R.RELATION = 4 THEN 'Poor'
            WHEN P0R.RELATION = 5 THEN 'Very Poor'
       END                     RELATION_MARK,
       C07.TEXT                RELATIONSHIP,
       C12.TEXT                ACTIVITY,
       P03.DATEFROM            FROMDATE,
       P03.TODATEESTIMATED     ESTIMATEEND,
       VSL.NAME                VESSEL,
       P01.CLIENT              NUMORGID,
       P01.EMPLOYMENTSTARTDATE,
       P01.EMPLOYMENTENDDATE
FROM   dbo.PW001P01 P01
       JOIN PW001P0R P0R
            ON  P01.PIN = P0R.PIN2
       JOIN PW001P01 P01P1
            ON  P01P1.PIN = P0R.PIN1
       LEFT JOIN PW001C07 C07
            ON  C07.CODE = P0R.RELATIONSHIP
       LEFT JOIN dbo.PW001P03 P03
            ON  P01.PIN = P03.PIN
            AND (P03.DATETO IS NULL OR P03.DATETO >= GETDATE())
            AND P03.DATEFROM <= GETDATE()
            AND P03.PLANNED <> 'Y'
       LEFT JOIN PWORG VSL
            ON  VSL.NUMORGID = dbo.ad_scanorgtree(P03.NUMORGID, 3)
       LEFT JOIN PW001C12 C12
            ON  C12.CODE = P03.CODE
WHERE  P0R.RELATION IS NOT     NULL

Columns Specification

Column	Description/ Location in APM

Column	Description/ Location in APM
PIN	Personal Details > Personal > PIN
NAME	Personal Details > Personal > Name
PIN2	Personal Details > Onboard > Relation With > PIN
RELATION_WITH_NAME	Personal Details > Onboard > Relations With Name
RELATION_MARK	`1 'Excellent' 2 'Good' 3 'Average' 4 'Poor' 5 'Very Poor'`
RELATIONSHIP	Personal Details > Onboard > Relationship
ACTIVITY	Datagroups > Activity > Current > Activity Name
FROMDATE	Datagroups > Activity > Current > Date From
ESTIMATEDEND	Datagroups > Activity > Current > Estimated End Date
VESSEL	Datagroups > Activity > Current > Vessel Name
NUMORGID	Personal Details > Employment > Organization’s NUMORGID
EMPLOYMENSTARTDATE	Personal Details > Employment > Employment Start Date
EMPLOYMENTENDDATE	Personal Details > Employment > Employment End Date

Adonis HR Reports, Views and Similar

Crew Relations

Analytics

Description

View Sample

Main Data Selection

SQL statement

Columns Specification

Related content