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

Summary	Shows all the crews’ unconfirmed activities
Keywords	Activities, Unconfirmed
Category	Crew List View

Description

The view shows all the crews’ unconfirmed activities.

View Sample

Main Data Selection

All crew with unconfirmed activities. An activity is considered unconfirmed if the status is current and the estimated end date is less than today’s date, or if the status is planned and the date from is less than today’s date.

SQL statement

CREATE VIEW dbo.VIEWID AS
SELECT P01.PIN             AS PIN,
       P01.ALTERNATIVEPIN  AS 'ALTERNATIVE PIN',
       P01.NAME            AS 'Full Name',
       P01.FIRSTNAME       AS 'First Name',
       P01.LASTNAME        AS 'Last Name',
       P01.MIDDLENAME 'Middle Name',
       ORG.NAME            AS 'Organization',
       C02.[NAME]          AS 'Rank',
       c12plan.TEXT 'Activity',
       CASE 
            WHEN ((p03.DAteto = '' OR Dateto IS NULL) AND p03.PLANNED = 'Y') THEN 'Planned'
            WHEN ((p03.DAteto = '' OR Dateto IS NULL) AND p03.PLANNED = 'N') THEN 'Current'
       END                 AS 'Activity status',
       CASE 
            WHEN ((P03.DATEFROM <= GETDATE()) AND p03.PLANNED = 'Y') THEN 'Unconfirmed Date From'
            WHEN (
                     (P03.todateestimated <= GETDATE())
                     AND (p03.DAteto = '' OR Dateto IS NULL)
                 ) THEN 'Unconfirmed Date To'
       END                 AS 'Issue',
       p03.DATEFROM 'Start Date',
       p03.TODATEESTIMATED 'Estimated End Date',
       plan_ves.NUMORGID 'Vessel_NUMORGID',
       plan_ves.NAME 'Vessel',
       plan_dep.NAME 'Department',
       plan_pos.NAME 'Position',
       P01.client             NUMORGID,
       p01.employmentstartdate,
       p01.employmentenddate,
       p01.COSTPLACE       AS 'Department/Cost Place Code',
       c43.[NAME]          AS 'Department/Cost Place'
FROM   PW001P01 p01
       JOIN PW001P03 p03
            ON  p03.PIN = p01.PIN
       LEFT JOIN PW001C02 C02
            ON  C02.CODE = P01.[RANK]
       LEFT JOIN PW001C12 c12plan
            ON  c12plan.CODE = p03.CODE
       LEFT JOIN PWORG plan_ves
            ON  plan_ves.NUMORGID = dbo.ad_scanorgtree(p03.NUMORGID, 3)
       LEFT JOIN PWORG plan_dep
            ON  plan_dep.NUMORGID = dbo.ad_scanorgtree(p03.NUMORGID, 4)
       LEFT JOIN PWORG plan_pos
            ON  plan_pos.NUMORGID = dbo.ad_scanorgtree(p03.NUMORGID, 5)
       LEFT JOIN PWORG ORG
            ON  ORG.NUMORGID = P01.CLIENT
       LEFT JOIN PW001C43 c43
            ON  c43.CODE = p01.COSTPLACE
WHERE  (p03.PLANNED = 'Y' AND p03.datefrom <= GETDATE())
       OR  (
               p03.datefrom <= GETDATE()
               AND p03.dateto IS NULL
               AND p03.todateestimated <= GETDATE()
           )

Columns Specification

Column	Description/ Location in APM

Column	Description/ Location in APM
PIN	Personal Details > Personal > PIN
Alternative PIN	Personal Details > Personal > Alternative PIN
Full Name	Personal Details > Personal > Full Name
Firstname	Personal Details > Personal > Firstname
Lastname	Personal Details > Personal > Lastname
Middlename	Personal Details > Personal > Middlename
Organization	Personal Details > Employment > Organization/Company
Rank	Personal Details > Employment > Rank
Activity	Activitiy name
Activity Status	Current/Planned
Issue	Unconfirmed Date From/ Unconfirmed Date To
Start Date	Datagroups > Activities > Date From
Estimated End Date	Datagroups > Activities > Estimated End Date
Vessel	Datagroups > Activities > Vessel name
Department	Datagroups > Activities > Department name
Position	Datagroups > Activities > Position name
Department/Cost Place Code	Personal Details > Employment > Payroll: Cost Place code
Department/Cost Place	Personal Details > Employment > Payroll: Cost Place name

Adonis HR Reports, Views and Similar

All: Unconfirmed Activities

Description

View Sample

Main Data Selection

SQL statement

Columns Specification

Related content