CREATE VIEW dbo.PW001SRV106PW001SRV131 AS
SELECT p01.PIN,
p01.PIN,P01.client P01.client NUMORGID,
p01.EMPLOYMENTSTARTDATE,
p01.EMPLOYMENTENDDATE,
p01.NAME,
p01.FIRSTNAME,
p01.LASTNAME,
p01.SEX GENDER,
plan_pos.NAME POSITION,
PLAN_DEP.NAME DEPARTMENT,
plan_ves.NAME VESSEL,
plan_ves.NUMORGID vessel_numorgid,
PLAN_DEP.numorgid department_numorgid,
ORG.NAME ORGANIZATION,
p01.BIRTHDATE as AS 'DATE OF BIRTH',
P01.PLACEOFBIRTH 'PLACE OF BIRTH',
CASE WHEN
c20manning.NAME IS NULL THEN WHEN c20manning.NAME IS NULL THEN 'NOT ASSIGNED'
ELSE C20MANNING.NAME
END AS 'MANNING POOL',
nat.NATIONALITY AS 'NATIONALITY ',
p01.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 Reson',
p03plan.DATEFROM 'EMBARKATION DATE',
ISNULL(portOn.portcode, portEst.portcode) 'EMBARKATION PORT CODE',
ISNULL(portOn.NAME, portEst.NAME) 'EMBARKATION PORT',
p03plan.TODATEESTIMATED 'DISEMBARKATION DATE',
ISNULL(portOff.portcode, portEstOff.portcode) 'DISEMBARKATION PORT CODE',
ISNULL(portOff.NAME, portEstOff.NAME) 'DISEMBARKATION PORT',
fb.GDSLOCATOR as AS 'GDS Locator',
Stuff((Select ','+Char(10)+al.AIRLINELOCATOR
STUFF(
From PWCCMAIRSEGMENTS(
al where al.seqno = fb.SEQNO order by al.SEGNO for xmlSELECT 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)+ + 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.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.ARVLDATE as 'Arrival Date',
LEFT(fbat.ETA,2)+':'+RIGHT(fbat.ETA,2) as 'ETA',
Stuff((Select distinct ','+Char(10)+tn.TICKETCODE
From PWCCMAIRSEGMENTS tn where tn.seqno = fb.SEQNO
for xml path('')),1,2,'') as 'Ticket Number',
CASE WHEN P0N.NOTES IS NULL THEN 'N' ELSE 'Y' END AS NOTES
FROM PW001P01 p01
LEFT JOIN PWORG ORG ON P01.CLIENT=ORG.NUMORGID
JOIN PW001P03 p03plan))))-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.ARVLDATE AS 'Arrival Date',
LEFT(fbat.ETA, 2) + ':' + RIGHT(fbat.ETA, 2) AS 'ETA',
STUFF(
(
SELECT DISTINCT ',' + CHAR(10) + tn.TICKETCODE
FROM PWCCMAIRSEGMENTS tn
WHERE tn.seqno = fb.SEQNO
FOR XML PATH('')
),
1,
2,
''
) AS 'Ticket Number',
CASE
WHEN P0N.NOTES IS NULL THEN 'N'
ELSE 'Y'
END AS NOTES
FROM PW001P01 p01
LEFT JOIN PWORG ORG
ON P01.CLIENT = ORG.NUMORGID
JOIN PW001P03 p03plan
ON p03plan.PIN = p01.PIN
AND p03plan.PLANNED = 'Y'
AND NOT EXISTS (
SELECT 1
FROM PW001P03 t
JOIN PW001C12 c12t
ON c12t.CODE = t.CODE
AND c12t.OPTIONS LIKE '%S%'
WHERE t.PIN = p03plan.Pin
AND dbo.ad_scanorgtree(t.NUMORGID, 3) = dbo.ad_scanorgtree(p03plan.NUMORGID, 3)
AND t.Numorgid <> p03plan.Numorgid
AND t.TODATEESTIMATED = p03plan.DATEFROM - 1
)
JOIN PW001C12 c12plan
ON c12plan.CODE = p03plan.CODE
AND c12plan.OPTIONS LIKE '%S%'
LEFT JOIN PWROT_SHIFT_ACTIVITIES psa
ON psa.ACTIVITIES = p03plan.SEQUENCENO
LEFT JOIN PWROT_ACTIVE_SHIFT pas
ON pas.SEQUENCENO = psa.SEQUENCENO
LEFT JOIN PWORG plan_ves
ON plan_ves.NUMORGID = dbo.ad_scanorgtree(p03plan.NUMORGID, 3)
LEFT JOIN PWORG PLAN_DEP
ON PLAN_DEP.NUMORGID = dbo.ad_scanorgtree(P03PLAN.NUMORGID, 4)
LEFT JOIN PWORG plan_pos
ON plan_pos.NUMORGID = p03plan.NUMORGID
AND plan_pos.ORGTYPE = 5
LEFT JOIN PW001C02 C02
ON C02.code = dbo.ad_orgPosC02Code(PLAN_POS.ORGCODE)
LEFT JOIN PWCOUNTRY nat
ON p03plannat.PINCOUNTRYCODE = p01.PINNATIONALITY
LEFT JOIN PW001P08 P08
AND p03plan.PLANNED = 'Y' ON P08.PIN = P01.PIN
AND NOT EXISTS (SELECT 1 AND P08.CODE = 'PP'
LEFT JOIN PW001P08 P081
FROM PW001P03 t ON P081.PIN = P01.PIN
AND P081.CODE = 'SBBAH'
LEFT JOIN JOINPW001P08 PW001C12P08v
c12t ON P08v.PIN = P01.PIN
AND P08v.CODE = 'VISASH'
ONLEFT c12t.CODEJOIN =PWCCMCHANGECREWLIST t.CODEcclOn
ON cclOn.NEXTACT = p03plan.SEQUENCENO
LEFT JOIN PWORGVESACT ccOn
AND c12t.OPTIONS LIKEON '%S%' ccOn.SEQNO = cclOn.SEQNO
LEFT JOIN PWPORT portOn
ON WHERE tportOn.PINPORTCODE = p03planccOn.PinPLACEFROM
LEFT JOIN PWORGVESACT ccEst
ON ccEst.SEQNO = pas.SPD_CC_SIGNON
AND dbo.ad_scanorgtree(t.NUMORGID, 3) = dbo.ad_scanorgtree(p03plan.NUMORGID, 3)
LEFT JOIN PWPORT portEst
ON portEst.PORTCODE = ccEst.PLACEFROM
LEFT JOIN PWCCMCHANGECREWLIST cclOff
AND t.Numorgid <> p03plan.Numorgid ON cclOff.CURRENTACT = p03plan.SEQUENCENO
LEFT JOIN PWORGVESACT ccOff
ON AND tccOff.TODATEESTIMATEDSEQNO = p03plan.DATEFROM - 1cclOff.SEQNO
LEFT JOIN PWPORT portOff
) JOIN PW001C12 c12plan ON portOff.PORTCODE = ccOff.PLACEFROM
ONLEFT JOIN c12plan.CODEPWORGVESACT =ccEstOff
p03plan.CODE ON AND c12planccEstOff.OPTIONSSEQNO LIKE '%S%'= pas.SPD_CC_SIGNON
LEFT JOIN PWROT_SHIFT_ACTIVITIES psaPWPORT portEstOff
ON psaportEstOff.ACTIVITIESPORTCODE = p03planccEstOff.SEQUENCENOPLACEFROM
LEFT JOIN PWROT_ACTIVE_SHIFT pasPW001P0N P0N
ON pas P0N.SEQUENCENOPIN = psaP01.SEQUENCENOPIN
LEFT JOIN PWORG plan_ves
PW001C20 c20manning
ON plan_ves.NUMORGID = dbo.ad_scanorgtree(p03plan.NUMORGID, 3) LEFT JOIN PWORG PLAN_DEP ON PLAN_DEP.NUMORGID = dbo.ad_scanorgtree(P03PLAN.NUMORGID, 4) c20manning.CODE = MANAGENTCODE
LEFT JOIN PWORGPWCCMFLIGHTARRANGEMENTS plan_posfb
ON plan_posfb.NUMORGIDpin = p03planp01.NUMORGIDpin
AND plan_posfb.ORGTYPESEQNOM = 5
LEFT JOIN PW001C02 C02
ON C02.code = dbo.ad_orgPosC02Code(PLAN_POS.ORGCODE)
LEFT JOIN PWCOUNTRY nat ON nat.COUNTRYCODE = p01.NATIONALITY
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'ISNULL(cclOn.SEQNO, ccEst.SEQNO)
AND fb.SIGNON = 1
AND fb.BOOKING_STATUS IN ('ISSUED', 'APPROVED')
LEFT JOIN (
LEFT JOIN PWCCMCHANGECREWLIST cclOn SELECT SEQNOM,
ON cclOn.NEXTACT = p03plan.SEQUENCENO LEFT JOIN PWORGVESACT ccOn PIN,
ON ccOn.SEQNO = cclOn.SEQNO LEFT JOIN PWPORT portOn CASE ON
portOn.PORTCODE = ccOn.PLACEFROM LEFT JOIN PWORGVESACT ccEst ON ccEst.SEQNO =WHEN pas.SPDSCHEDULED_CC_SIGNONARRIVAL_TIME < 100 THEN '00'
LEFT JOIN PWPORT portEst ON portEst.PORTCODE = ccEst.PLACEFROM
LEFT JOIN PWCCMCHANGECREWLIST cclOff
WHEN SCHEDULED_ARRIVAL_TIME < 1000 THEN '0'
ON cclOff.CURRENTACT = p03plan.SEQUENCENO LEFT JOIN PWORGVESACT ccOff ELSE ''
ON ccOff.SEQNO = cclOff.SEQNO LEFT JOIN PWPORT portOff END ON portOff.PORTCODE = ccOff.PLACEFROM+ CONVERT(VARCHAR(4), SCHEDULED_ARRIVAL_TIME) AS 'ETA'
LEFT JOIN PWORGVESACT ccEstOff FROM PWCCMFLIGHTARRANGEMENTS
ON ccEstOff.SEQNO = pas.SPD_CC_SIGNON LEFT JOIN PWPORTWHERE portEstOff signon = 1
ON portEstOff.PORTCODE = ccEstOff.PLACEFROM LEFT JOIN PW001P0N P0N ON P0N.PIN = P01.PIN AND BOOKING_STATUS IN ('ISSUED', 'APPROVED')
LEFT JOIN PW001C20 c20manning ) fbat
ON c20manning.CODE = MANAGENTCODE left join PWCCMFLIGHTARRANGEMENTS fb ON on fbfbat.pin = p01.pin
and fb
AND fbat.SEQNOM = IsnullISNULL(cclOn.SEQNO, ccEst.SEQNO)
and fb.SIGNON = 1
and fb.BOOKING_STATUS in ('ISSUED','APPROVED')
Left Join (Select SEQNOM, PIN, case when SCHEDULED_ARRIVAL_TIME <100 then '00' when SCHEDULED_ARRIVAL_TIME<1000 then '0' else '' end+Convert(varchar(4),SCHEDULED_ARRIVAL_TIME) as 'ETA'
from PWCCMFLIGHTARRANGEMENTS
where signon = 1 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 = p03plan.DISEMBARKATION
LEFT JOIN PW001C79 c79e
ON c79e.CODE = p03plan.EMBARKATION
|