CREATE VIEW dbo.PW001SRV132 AS
SELECT p01.PIN,
       p01.client              NUMORGID,
       P01.EMPLOYMENTSTARTDATE,
       P01.EMPLOYMENTENDDATE,
       p01.NAME,
       p01.Lastname         AS LASTNAME,
       p01.Firstname        AS FIRSTNAME,
       p01.SEX                 GENDER,
       orgpos.NAME          AS POSITION,
       orgdep.NAME          AS DEPARTMENT,
       orgves.NAME          AS VESSEL,
       ORG.NAME             AS ORGANIZATION,
       p01.BIRTHDATE        AS 'DATE OF BIRTH',
       P01.PLACEOFBIRTH 'PLACE OF BIRTH',
       nat.NATIONALITY      AS 'NATIONALITY ',
       p01.NATIONALITY 'NATIONALITY CODE',
       nat.ISO3166_ALPHA3 'NATIONALOTY CODE ISO',
       P08.TDNUMBER 'PASS NUMBER',
       p08.datefrom 'PASS ISSUE',
       p08.dateto 'PASS EXPIRY',
       P081.TDNUMBER           SBOOK,
       P081.DATEFROM 'SB ISSUE DATE',
       p081.issuedby 'SB ISSUE BY',
       P081.DATETO 'SBOOK EXPIRY',
       p08v.TDNUMBER 'VISA',
       p08v.datefrom 'VISA ISSUE',
       p08v.dateto 'VISA EXPIRY',
       c79d.[TEXT] 'Disembarkation Reason',
       --c79e.[TEXT] 'Embarkation Reason',
       p03.DATEFROM 'EMBARKATION DATE',
       ISNULL(portOn.portcode, portEst.portcode) 'EMBARKATION PORT CODE',
       ISNULL(portOn.NAME, portEst.NAME) 'EMBARKATION PORT',
       p03.TODATEESTIMATED  AS 'DISEMBARKATION DATE',
       portOff.portcode     AS 'DISEMBARKATION PORT CODE',
       portOff.NAME         AS 'DISEMBARKATION PORT',
       fb.GDSLOCATOR        AS 'GDS Locator',
       STUFF(
           (
               SELECT ',' + CHAR(10) + al.AIRLINELOCATOR
               FROM   PWCCMAIRSEGMENTS al
               WHERE  al.seqno = fb.SEQNO
               ORDER BY
                      al.SEGNO
                      FOR XML PATH('')
           ),
           1,
           2,
           ''
       )                    AS 'Airline Locator',Stuff((Select ','+Char(10)
			+Case when len(rd.CARRIER+convert(varchar,rd.FLIGHTNUM))<7 then Space(7-len(rd.CARRIER+convert(varchar,rd.FLIGHTNUM)))+rd.CARRIER+convert(varchar,rd.FLIGHTNUM) else rd.CARRIER+convert(varchar,rd.FLIGHTNUM) end +Space(1)
			+case when left(convert(varchar,rd.DEPT_DATE,106),1) = '0'
				then Replace(Replace(Upper(Left(convert(varchar,rd.DEPT_DATE,106),6)),' ',''),'0',' ')
				else Replace(Upper(Left(convert(varchar,rd.DEPT_DATE,106),6)),' ','')
			end+Space(1)
			+Isnull((select Case when isnull(charindex('-',LTRIM(RTRIM(Replace(Replace(Replace(Replace([name],'Int`l',''),'Int''l',''),'Apt.',''),'Airport','')))),0)=0 then Replace(Replace(Replace(Replace([name],'Int`l',''),'Int''l',''),'Apt.',''),'Airport','')
						else RTRIM(Left(LTRIM(RTRIM(Replace(Replace(Replace(Replace([name],'Int`l',''),'Int''l',''),'Apt.',''),'Airport',''))),charindex('-',LTRIM(RTRIM(Replace(Replace(Replace(Replace([name],'Int`l',''),'Int''l',''),'Apt.',''),'Airport',''))))-1))
						End as airport from pw001c48 where code  = rd.DEPT_AIRPORT),'')+Char(9)+
			+Isnull((select Case when isnull(charindex('-',LTRIM(RTRIM(Replace(Replace(Replace(Replace([name],'Int`l',''),'Int''l',''),'Apt.',''),'Airport','')))),0)=0 then Replace(Replace(Replace(Replace([name],'Int`l',''),'Int''l',''),'Apt.',''),'Airport','')
						else RTRIM(Left(LTRIM(RTRIM(Replace(Replace(Replace(Replace([name],'Int`l',''),'Int''l',''),'Apt.',''),'Airport',''))),charindex('-',LTRIM(RTRIM(Replace(Replace(Replace(Replace([name],'Int`l',''),'Int''l',''),'Apt.',''),'Airport',''))))-1))
						End as airport from pw001c48 where code  = rd.ARRV_AIRPORT),'')+Char(9)+
			+Isnull(case when rd.DEPT_TIME<100 then '00' when rd.DEPT_TIME<1000 then '0' else '' end+Convert(varchar(4),rd.DEPT_TIME)+Space(1),'    '+Space(1))
			+Isnull(case when rd.ARRV_TIME<100 then '00' when rd.ARRV_TIME<1000 then '0' else '' end+Convert(varchar(4),rd.ARRV_TIME)+Space(1),'    '+Space(1))
	   From PWCCMAIRSEGMENTS rd where rd.seqno = fb.SEQNO
	   order by rd.SEGNO
	   for xml path('')),1,2,'') as 'Route Details',
       STUFF(
           (
               SELECT ',' + CHAR(10) + fn.CARRIER + SPACE(1) + CONVERT(VARCHAR, fn.FLIGHTNUM)
               FROM   PWCCMAIRSEGMENTS fn
               WHERE  fn.seqno = fb.SEQNO
               ORDER BY
                      fn.SEGNO
                      FOR XML PATH('')
           ),
           1,
           2,
           ''
       )                    AS 'Flight No',
       fb.DPTDATE           AS 'Departure Date',
       LEFT(fbat.ETD, 2) + ':' + RIGHT(fbat.ETD, 2) AS 'ETD',
       STUFF(
           (
               SELECT DISTINCT ',' + CHAR(10) + tn.TICKETCODE
               FROM   PWCCMAIRSEGMENTS tn
               WHERE  tn.seqno = fb.SEQNO
                      --order by tn.SEGNO
                      FOR XML PATH('')
           ),
           1,
           2,
           ''
       )                    AS 'Ticket Number',
       CASE 
            WHEN c20manning.NAME IS NULL THEN 'NOT ASSIGNED'
            ELSE C20MANNING.NAME
       END                  AS 'MANNING POOL'
FROM   PW001P01 p01
       LEFT JOIN PWORG ORG
            ON  P01.CLIENT = ORG.NUMORGID
       JOIN PW001P03 p03
            ON  p03.PIN = p01.PIN
            AND ISNULL(p03.DATETO, GETDATE()) >= GETDATE()
       JOIN PW001C12 c12
            ON  c12.CODE = p03.CODE
            AND c12.OPTIONS LIKE '%S%'
       LEFT JOIN PWORG orgves
            ON  orgves.NUMORGID = dbo.ad_scanorgtree(p03.NUMORGID, 3)
       LEFT JOIN PWORG orgdep
            ON  orgdep.NUMORGID = dbo.ad_scanorgtree(p03.NUMORGID, 4)
       LEFT JOIN PWORG orgpos
            ON  orgpos.NUMORGID = p03.NUMORGID
            AND orgpos.ORGTYPE = 5
       LEFT JOIN PWCOUNTRY nat
            ON  nat.COUNTRYCODE = p01.NATIONALITY
       LEFT JOIN PWROT_SHIFT_ACTIVITIES psa
            ON  psa.ACTIVITIES = p03.SEQUENCENO
       LEFT JOIN PWROT_ACTIVE_SHIFT pas
            ON  pas.SEQUENCENO = psa.SEQUENCENO
       LEFT JOIN PWCCMCHANGECREWLIST cclOff
            ON  cclOff.CURRENTACT = p03.SEQUENCENO
       LEFT JOIN PWORGVESACT ccOff
            ON  ccOff.SEQNO = cclOff.SEQNO
       LEFT JOIN PWPORT portOff
            ON  portOff.PORTCODE = ccOff.PLACEFROM
       LEFT JOIN PWCCMCHANGECREWLIST cclOn
            ON  cclOn.NEXTACT = p03.SEQUENCENO
       LEFT JOIN PWORGVESACT ccOn
            ON  ccOn.SEQNO = cclOn.SEQNO
       LEFT JOIN PWPORT portOn
            ON  portOn.PORTCODE = ccOn.PLACEFROM
       LEFT JOIN PWORGVESACT ccEst
            ON  ccEst.SEQNO = pas.SPD_CC_SIGNOFF
       LEFT JOIN PWPORT portEst
            ON  portEst.PORTCODE = ccEst.PLACEFROM
       LEFT JOIN PW001P08 P08
            ON  P08.PIN = P01.PIN
            AND P08.CODE = 'PP'
       LEFT JOIN PW001P08 P081
            ON  P081.PIN = P01.PIN
            AND P081.CODE = 'SBBAH'
       LEFT JOIN PW001P08 P08v
            ON  P08v.PIN = P01.PIN
            AND P08v.CODE = 'VISASH'
       LEFT JOIN PW001C20 c20manning
            ON  c20manning.CODE = MANAGENTCODE
       LEFT JOIN PWCCMFLIGHTARRANGEMENTS fb
            ON  fb.pin = p01.pin
            AND fb.SEQNOM = ISNULL(ccOff.SEQNO, ccEst.SEQNO)
            AND fb.SIGNON = 0
            AND fb.BOOKING_STATUS IN ('ISSUED', 'APPROVED')
       LEFT JOIN (
                SELECT SEQNOM,
                       PIN,
                       CASE 
                            WHEN SCHEDULED_DEPARTURE_TIME < 100 THEN '00'
                            WHEN SCHEDULED_DEPARTURE_TIME < 1000 THEN '0'
                            ELSE ''
                       END + CONVERT(VARCHAR(4), SCHEDULED_DEPARTURE_TIME) AS 'ETD',
                       SCHEDULED_DEPARTURE_TIME
                FROM   PWCCMFLIGHTARRANGEMENTS
                WHERE  signon = 0
                       AND BOOKING_STATUS IN ('ISSUED', 'APPROVED')
            ) fbat
            ON  fbat.pin = p01.pin
            AND fbat.SEQNOM = ISNULL(cclOn.SEQNO, ccEst.SEQNO)
       LEFT JOIN PW001C79 c79d
            ON  c79d.CODE = p03.DISEMBARKATION
       --LEFT JOIN PW001C79 c79e
            --ON  c79e.CODE = p03.EMBARKATION