CREATE VIEW dbo.PW001SRV136PW001SRV35
AS

SELECT p01.PIN,
       p01.[NAME]           AS 'Full Name',
       p01.firstname        AS 'First Name',
       p01.MIDDLENAME       AS 'Middle Name',
       p01.lastname         AS 'Last Name',
       org.[NAME]           AS 'Organization',
       r.[NAME]             AS 'Rank',
       p01.pantskirtsize    AS 'Pant/Skirt Size',
       p01.shirtblousesize  AS 'Shirt/Blouse Size',
       p01.shoesize         AS 'Shoe Size',
       p01.ppe_boilersuite  AS 'Boiler Suit',
       VSL.NAME             AS 'Vessel Name',
       VSL.numorgid as vessel_numorgid,
       pos.name             AS 'Position',
       p03.DATEFROM         AS 'Date From',
       p03.TODATEESTIMATED  AS 'Estimated End Date',
       dep.name             AS 'Department',
       P01.CLIENT              NUMORGID,
       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 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 org
            ON  org.NUMORGID = p01.CLIENT
       LEFT JOIN PW001C02 r
            ON  r.CODE = p01.[RANK]