CREATE VIEW dbo.PW001SRV107PW001SRV132 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 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 p03P08v.PIN = p01P01.PIN
AND ISNULL(p03.DATETO, GETDATE()) >= GETDATE()P08v.CODE = 'VISASH'
LEFT JOIN PW001C12PW001C20 c12c20manning
ON c12c20manning.CODE = p03.CODEMANAGENTCODE
LEFT JOIN PWCCMFLIGHTARRANGEMENTS fb
AND c12.OPTIONS LIKE '%S%' LEFTON JOIN PWORGfb.pin orgves= p01.pin
ON AND orgvesfb.NUMORGIDSEQNOM = dbo.ad_scanorgtree(p03.NUMORGID, 3)
LEFT JOIN PWORG orgdepISNULL(ccOff.SEQNO, ccEst.SEQNO)
ON orgdep.NUMORGID = dbo.ad_scanorgtree(p03.NUMORGID, 4)AND fb.SIGNON = 0
LEFT JOIN PWORG orgpos AND fb.BOOKING_STATUS IN ('ISSUED', 'APPROVED')
ON LEFT JOIN orgpos.NUMORGID(
= p03.NUMORGID AND orgpos.ORGTYPE =SELECT 5SEQNOM,
LEFT JOIN PWCOUNTRY nat ON nat.COUNTRYCODE = p01.NATIONALITY LEFT JOIN PWROT_SHIFT_ACTIVITIES psaPIN,
ON psa.ACTIVITIES = p03.SEQUENCENO CASE
LEFT JOIN PWROT_ACTIVE_SHIFT pas ON pas.SEQUENCENO = psa.SEQUENCENO LEFT JOIN PWCCMCHANGECREWLIST cclOff WHEN SCHEDULED_DEPARTURE_TIME < 100 THEN '00'
ON cclOff.CURRENTACT = p03.SEQUENCENO LEFT JOIN PWORGVESACT ccOff WHEN ON ccOff.SEQNO = cclOff.SEQNO
SCHEDULED_DEPARTURE_TIME < 1000 THEN '0'
LEFT JOIN PWPORT portOff ON portOff.PORTCODE = ccOff.PLACEFROM LEFT JOIN PWCCMCHANGECREWLISTELSE cclOn''
ON cclOn.NEXTACT = p03.SEQUENCENO LEFT JOIN PWORGVESACT ccOn END + CONVERT(VARCHAR(4), SCHEDULED_DEPARTURE_TIME) AS 'ETD',
ON ccOn.SEQNO = cclOn.SEQNO LEFT JOIN PWPORT portOn SCHEDULED_DEPARTURE_TIME
ON portOn.PORTCODE = ccOn.PLACEFROM LEFT JOIN PWORGVESACT ccEst FROM PWCCMFLIGHTARRANGEMENTS
ON ccEst.SEQNO = pas.SPD_CC_SIGNOFF LEFT JOIN PWPORT portEstWHERE signon = 0
ON portEst.PORTCODE = ccEst.PLACEFROM LEFT JOIN PW001P08 P08 ON P08.PIN=P01.PIN AND P08.CODE='PP' LEFT JOIN PW001P08 P081AND 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 fbBOOKING_STATUS IN ('ISSUED', 'APPROVED')
) fbat
ON fbat.pin = p01.pin
and fb
AND fbat.SEQNOM = IsnullISNULL(ccOffcclOn.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 |