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)
                   )
           )