Skip to end of banner
Go to start of banner

Crew Relations

Skip to end of metadata
Go to start of metadata

You are viewing an old version of this content. View the current version.

Compare with Current View Version History

« Previous Version 2 Current »

Summary

A view that will shows all existing “Relations“ in APM,

Keywords

Category

Crew List View

Please remember to add labels to the article. Only the labels from the lit are allowed: /wiki/spaces/PnP/pages/4523197124

Description

A view that will show all relations in APM defined and showing under Personal Details > Onboard > Relations and shows current activity details of the crew.

View Sample

image-20241220-161908.png

Main Data Selection

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

SQL statement

 Click here to expand...
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

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

  • No labels