CREATE VIEW dbo.PW001SRV132 AS
SELECT p01.PIN,
P01.ALTERNATIVEPIN as 'ALTERNATIVE PIN',
p01.client NUMORGID,
P01.EMPLOYMENTSTARTDATE,
P01.EMPLOYMENTENDDATE,
p01.NAME,
P08.NATIVENAME 'NATIVE NAME',
p01.Lastname AS LASTNAME,
p01.Firstname AS FIRSTNAME,
p01.MIDDLENAME,
p01.SEX GENDER,
P01.PERSONALIDNO 'Personal ID',
orgpos.NAME AS POSITION,
orgdep.NAME AS DEPARTMENT,
orgves.NAME AS VESSEL,
orgves.NUMORGID vessel_numorgid,
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]p03.DATEFROM 'EmbarkationEMBARKATION ReasonDATE',
p03.DATEFROMTODATEESTIMATED AS 'EMBARKATIONDISEMBARKATION DATE',
--IMPLADC-568 06/27/2024
/*
ISNULL(portOn.portcode, portEst.portcode) 'EMBARKATION PORT CODE',
ISNULL(portOn.NAME, portEst.NAME) 'EMBARKATION PORT',
*/
p03.TODATEESTIMATED AS 'DISEMBARKATION DATE portOn.portcode 'EMBARKATION PORT CODE',
portOn.NAME 'EMBARKATION PORT',
portOff.portcode AS 'DISEMBARKATION PORT CODE',
portOff.NAME AS 'DISEMBARKATION PORT',
fb.GDSLOCATOR AS 'Flight 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 'Flight Airline Locator',
CONVERT(
VARCHAR(200),
REPLACE(
REPLACE(
REPLACE(
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
),
''
) + ' ' +
+ 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
),
''
) + ', ' +
+ 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 'Flight Route Details',
CONVERT(
VARCHAR(200),
REPLACE(
REPLACE(
REPLACE(
STUFF(
(
SELECT ',' + ' ' + fn.CARRIER + ' ' + 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 'Flight Departure Date',
LEFT(fbat.ETA, 2) + ':' + RIGHT(fbat.ETA, 2) AS 'Flight ETA',
LEFT(fbat.ETD, 2) + ':' + RIGHT(fbat.ETD, 2) AS 'Flight 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 'Flight Ticket Number',
CASE
WHEN c20manning.NAME IS NULL THEN 'NOT ASSIGNED'
ELSE C20MANNING.NAME
END AS 'MANNING POOL',
mobile.TELENO AS MOBILE,
email.TELENO AS EMAIL,
p01.HOMEAIRPORT,
p01.COSTPLACE AS 'Department/Cost Place Code',
c43.[NAME] AS 'Department/Cost Place'
FROM PW001P01 p01
LEFT JOIN PWORG ORG
ON P01.CLIENT = ORG.NUMORGID
JOIN dbo.PW001P03 p03
ON p03P01.PIN = p01p03.PIN
AND ISNULL(p03.DATETO,CODE IN GETDATE()) >= GETDATE()SELECT c12.CODE
JOIN PW001C12 c12 ON c12.CODE = p03.CODE FROM PW001C12 c12
AND c12.OPTIONS LIKE '%S%' LEFT JOIN PWORG orgves ON orgves.NUMORGIDWHERE = dbo.ad_scanorgtree(p03.NUMORGID, 3c12.OPTIONS LIKE '%S%')
AND (p03.DATETO IS NULL OR p03.DATETO >= GETDATE())
LEFT JOIN PWORG orgdepAND p03.DATEFROM <= GETDATE()
ON orgdep.NUMORGID = dbo.ad_scanorgtree(AND p03.NUMORGID,PLANNED 4)<> 'Y'
LEFT JOIN PWORGPW001C12 orgposc12
ON orgposc12.NUMORGIDCODE = p03.NUMORGIDCODE
AND orgposc12.ORGTYPEOPTIONS =LIKE 5'%S%'
LEFT JOIN PWCOUNTRYPWORG natorgves
ON natorgves.COUNTRYCODENUMORGID = p01.NATIONALITYdbo.ad_scanorgtree(p03.NUMORGID, 3)
LEFT JOIN PWROT_SHIFT_ACTIVITIES psaPWORG orgdep
ON psaorgdep.ACTIVITIESNUMORGID = dbo.ad_scanorgtree(p03.SEQUENCENONUMORGID, 4)
LEFT JOIN PWROT_ACTIVE_SHIFT pasPWORG orgpos
ON pasorgpos.SEQUENCENONUMORGID = psa.SEQUENCENO p03.NUMORGID
AND orgpos.ORGTYPE = 5
LEFT JOIN PWCCMCHANGECREWLISTPWCOUNTRY cclOffnat
ON cclOffnat.CURRENTACTCOUNTRYCODE = p03p01.SEQUENCENONATIONALITY
LEFT JOIN PWORGVESACT ccOffPWROT_SHIFT_ACTIVITIES psa
ON ccOffpsa.SEQNOACTIVITIES = cclOffp03.SEQNOSEQUENCENO
LEFT JOIN PWPORT portOffPWROT_ACTIVE_SHIFT pas
ON portOffpas.PORTCODESEQUENCENO = ccOffpsa.PLACEFROMSEQUENCENO
LEFT JOIN PWCCMCHANGECREWLIST cclOncclOff
ON cclOncclOff.NEXTACTCURRENTACT = p03.SEQUENCENO
LEFT JOIN PWORGVESACT ccOnccOff
ON ccOnccOff.SEQNO = cclOncclOff.SEQNO
LEFT JOIN PWPORT portOnportOff
ON portOnportOff.PORTCODE = ccOnccOff.PLACEFROM
LEFT JOIN PWORGVESACTPWCCMCHANGECREWLIST ccEstcclOn
ON ccEstcclOn.SEQNONEXTACT = pas.SPD_CC_SIGNOFFp03.SEQUENCENO
LEFT JOIN PWPORTPWORGVESACT portEstccOn
ON portEstccOn.PORTCODESEQNO = ccEstcclOn.PLACEFROMSEQNO
LEFT JOIN PW001P08PWPORT P08portOn
ON P08portOn.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 = 'P'
AND NOT EXISTS(SELECT 1
FROM PW001P08 t
WHERE t.PIN = P01.Pin
AND t.CODE = p08.CODE
AND (
ISNULL(t.DATETO, 0) > ISNULL(p08.DATETO, 0)
OR ISNULL(t.DATETO, 0) = ISNULL(p08.DATETO, 0)
AND t.SEQUENCENO > p08.SEQUENCENO
)
)
LEFT JOIN PW001P08 P081
ON P081.PIN = P01.PIN
AND P081.CODE = 'SFR'
AND NOT EXISTS(SELECT 1
FROM PW001P08 t
WHERE t.PIN = P01.Pin
AND t.CODE = P081.CODE
AND (
ISNULL(t.DATETO, 0) > ISNULL(P081.DATETO, 0)
OR ISNULL(t.DATETO, 0) = ISNULL(P081.DATETO, 0)
AND t.SEQUENCENO > P081.SEQUENCENO
)
)
LEFT JOIN PW001P08 P08v
ON P08v.PIN = P01.PIN
AND P08P08v.CODE = 'PVISASH'
AND NOT EXISTS(SELECT 1
FROM LEFT JOIN PW001P08 P081t
WHERE t.PIN = P01.Pin
AND ON P081.PINt.CODE = P01.PINP08v.CODE
AND (
AND P081.CODE = 'SFR'
ISNULL(t.DATETO, 0) > ISNULL(P08v.DATETO, 0)
LEFT JOIN PW001P08 P08v OR ISNULL(t.DATETO, 0) = ISNULL(P08v.DATETO, 0)
ON P08v.PIN =AND P01t.PINSEQUENCENO > P08v.SEQUENCENO
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', 'REISSUED')
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,*/
SCHEDULED_ARRIVAL_TIME AS ETA,
SCHEDULED_DEPARTURE_TIME AS ETD
FROM PWCCMFLIGHTARRANGEMENTS
WHERE signon = 0
AND BOOKING_STATUS IN ('ISSUED', 'APPROVED', 'REISSUED')
) fbat
ON fbat.pin = p01.pin
AND fbat.SEQNOM = ISNULL(cclOff.SEQNO, ccEst.SEQNO)
LEFT JOIN PW001C79 c79d
ON c79d.CODE = p03.DISEMBARKATION
LEFT JOIN PW001C43 c43
ON c43.CODE = p01.COSTPLACE
LEFT JOIN PW001P0T mobile
ON mobile.PIN = p01.PIN
AND mobile.TELETYPE = 3
AND NOT EXISTS (
SELECT 1
FROM pw001p0t t
WHERE t.PIN = mobile.PIN
AND t.TELETYPE = mobile.TELETYPE
AND (
t.TELEPRIORITY < mobile.TELEPRIORITY
OR (
t.TELEPRIORITY = mobile.TELEPRIORITY
AND t.SEQUENCENO > mobile.SEQUENCENO
)
)
)
LEFT JOIN PW001P0T email
ON email.PIN = p01.PIN
AND email.TELETYPE = 6
AND NOT EXISTS (
SELECT 1
FROM pw001p0t t
WHERE t.PIN = email.PIN
AND t.TELETYPE = email.TELETYPE
AND (
t.TELEPRIORITY < email.TELEPRIORITY
OR (
t.TELEPRIORITY = email.TELEPRIORITY
AND t.SEQUENCENO > email.SEQUENCENO
)
)
)
|
