CREATE VIEW dbo.PW001SRV136
AS

SELECT p01.pinPIN,
       p01.lastname         AS 'Last Name',
       p01.MIDDLENAME       AS 'Middle Name',
       p01.firstname        AS 'First Name',
       p01.[NAME]           AS 'Full Name',
       p01.pantskirtsize    AS 'Pant/Skirt Size',
       p01.shirtblousesize  AS 'Shirt/Blouse Size',
       p01.shoesize         AS 'Shoe Size',
       p01.ppe_boilersuite  AS 'Boiler Suit',
       p03.DATEFROM         AS 'Date From',
       ISNULL(p03.DATETO, p03.TODATEESTIMATED) AS 'Estimated End Date',
       VSL.NAME             AS 'Vessel Name',
       pos.name             AS 'Position',
       r.[NAME]             AS 'Rank',
       dep.name             AS 'Department',
       p03plan.DateFROM     AS 'Next Sign On Date',P01.CLIENT             VSLplan.name         AS 'Future Vessel' NUMORGID,
       P01org.CLIENT[NAME]              NUMORGIDAS 'Organization',
       P01.EMPLOYMENTSTARTDATE,
       P01.EMPLOYMENTENDDATE
FROM   pw001p01 p01
       JOIN PW001P03 p03
            ON  p03.PIN = p01.PIN
            AND p03.DATEFROM <= CONVERT(VARCHAR, GETDATE(), 112)
            AND p03.DATETO IS NULL
            AND p03.PLANNED <> 'Y'
            AND p03.CODE IN (SELECT c12.Code
                             FROM   pw001c12 c12
                             WHERE  c12.OPTIONS LIKE '%S%')
       LEFT JOIN PW001P03 p03plan
            ON  p03plan.PIN = p01.PIN
            AND p03plan.PLANNED = 'Y'
            AND p03plan.code IN (SELECT c12.Code
                                 FROM   pw001c12 c12
                                 WHERE  c12.OPTIONS LIKE '%S%')
            AND NOT EXISTS (
                    SELECT 1
                    FROM   pw001p03 p03t
                    WHERE  p03t.PIN = p01.PIN
                           AND p03t.PLANNED = 'Y'
                           AND p03t.DATEFROM < p03plan.DateFrom
                           AND p03t.CODE IN (SELECT c12.Code
             
                               FROM   pw001c12 c12
                                             WHERE  c12.OPTIONS LIKE '%S%')
                )
       LEFT JOIN pworg pos
            ON  pos.NUMORGID = dbo.ad_scanorgtree(p03.NUMORGID, 5)
       LEFT JOIN pworg dep
            ON  dep.NUMORGID = dbo.ad_scanorgtree(p03.NUMORGID, 4)
       LEFT JOIN PWORG VSL
            ON  VSL.NUMORGID = dbo.ad_scanorgtree(P03.NUMORGID, 3)
       LEFT JOIN PWORG VSLPlanorg
            ON  VSLplanorg.NUMORGID = dbo.ad_scanorgtree(p03plan.NUMORGID, 3)p01.CLIENT
        LEFT JOIN PW001C02 r
            ON  r.CODE = p01.[RANK]