CREATE VIEW dbo.PW001SRV107 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',
	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)