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

Summary	Shows all the crew activities with start date/ estimated end date equal to today’s date.
Keywords	Activities, To Confirm
Category	Crew List View

Description

The view shows all the crew activities with a start date/ estimated end date equal to today’s date.

View Sample

Main Data Selection

All crew who have an activity with a start date equal to today’s date with Planned status, and an estimated end date equal to today’s date with Current status.

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 (
                   CAST(CONVERT(CHAR(12), p03.datefrom) AS DATETIME) = CAST(CONVERT(CHAR(12), GETDATE()) AS DATETIME)
               )
       )
       OR  (
               p03.datefrom <= GETDATE()
               AND p03.dateto IS NULL
               AND (
                       CAST(CONVERT(CHAR(12), p03.todateestimated) AS DATETIME) = CAST(CONVERT(CHAR(12), GETDATE()) AS DATETIME)
                   )
           )

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

Reports, Views and Similar

To Confirm Activities Today

Description

View Sample

Main Data Selection

SQL statement

Columns Specification