CREATE VIEW dbo.PW001SRV136
AS

SELECT p01.PIN,
       p01.lastname[NAME]           AS 'LastFull Name',
       p01.MIDDLENAMEfirstname        AS 'MiddleFirst Name',
       p01.firstnameMIDDLENAME        AS 'FirstMiddle Name',
       p01.[NAME] lastname          AS 'FullLast Name',
       p01.pantskirtsizeorg.[NAME]           AS 'Pant/Skirt SizeOrganization',
       p01.shirtblousesizer.[NAME]             AS 'Shirt/Blouse SizeRank',
       p01.shoesizepantskirtsize         AS 'ShoePant/Skirt Size',
       p01.ppe_boilersuiteshirtblousesize  AS 'BoilerShirt/Blouse SuitSize',
       p03p01.DATEFROMshoesize         AS 'DateShoe FromSize',
       ISNULL(p03.DATETO, p03.TODATEESTIMATED)p01.ppe_boilersuite  AS 'EstimatedBoiler End DateSuit',
       VSL.NAME             AS 'Vessel Name',
       pos.name             AS 'Position',
       r.[NAME]   p03.DATEFROM          AS 'RankDate From',
       depp03.nameTODATEESTIMATED  AS 'Estimated          AS 'Department'End Date',
       P01dep.CLIENTname             AS NUMORGID'Department',
       org.[NAME]P01.CLIENT             AS 'Organization'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]