'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.ARVLDATE AS 'Flight Arrival 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
FOR XML PATH('')
),
1,
2,
''
) AS 'Flight Ticket Number',
CASE
WHEN P0N.NOTES IS NULL THEN 'N'
ELSE 'Y'
END AS NOTES,
mobile.TELENO AS MOBILE,
email.TELENO AS EMAIL,
p01.HOMEAIRPORT,
CASE
WHEN CONTRACTKIND = 0 THEN 'Main Contract'
WHEN CONTRACTKIND = 1 THEN 'Sub Contract'
WHEN CONTRACTKIND = 2 THEN 'Ammendment'
ELSE ''
END AS 'Contract Type',
C32CONT.Text 'Contract Name',
C02CONT.NAME 'Contract Rank',
p20.DATESTART 'Contract Start',
p20.DATEEND 'Contract End',
p20.DURATION 'Trial period duration',
p20.TRIALPERIODEND 'Trial period end',
p20.PAYSCALETABLE 'Contract Pasycale Table',
p20.PAYSCALECODE 'Contract Pasycale Code',
p0u.EMP_PORT 'Employee Portal Access From',
p20u.[LENGTH] + ' months' AS 'Duration Of Employment',
p01.[RANK] AS 'Current Rank Code',
cRANK.[NAME] AS 'Current Rank Name',
org.[NAME] AS 'Organization',
CASE
WHEN p20.scanneddocno IS NULL THEN NCHAR(9940) + ' Missing'
WHEN p20.scanvalidity in (1,0) AND p20.scanneddocno IS NOT NULL THEN NCHAR(10004) + ' Confirmed'
ELSE 'N/A'
END AS 'Contract Scan Status',
p01.COSTPLACE AS 'Department/Cost Place Code',
c43.[NAME] AS 'Department/Cost Place'
FROM PW001P01 p01
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 nat.COUNTRYCODE = p01.NATIONALITY
LEFT JOIN PW001P08 P08
ON P08.PIN = P01.PIN
AND P08.CODE = 'P'
LEFT JOIN PW001P08 P081
ON P081.PIN = P01.PIN
AND P081.CODE = 'SFR'
LEFT JOIN PW001P08 P08v
ON P08v.PIN = P01.PIN
AND P08v.CODE = 'VISASH'
LEFT JOIN PWCCMCHANGECREWLIST cclOn
ON cclOn.NEXTACT = p03plan.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_SIGNON
LEFT JOIN PWPORT portEst
ON portEst.PORTCODE = ccEst.PLACEFROM
LEFT JOIN PWCCMCHANGECREWLIST cclOff
ON cclOff.CURRENTACT = p03plan.SEQUENCENO
LEFT JOIN PWORGVESACT ccOff
ON ccOff.SEQNO = cclOff.SEQNO
LEFT JOIN PWPORT portOff
ON portOff.PORTCODE = ccOff.PLACEFROM
LEFT JOIN PWORGVESACT ccEstOff
ON ccEstOff.SEQNO = pas.SPD_CC_SIGNON
LEFT JOIN PWPORT portEstOff
ON portEstOff.PORTCODE = ccEstOff.PLACEFROM
LEFT JOIN PW001P0N P0N
ON P0N.PIN = P01.PIN and p0n.FIELDNO is not null and p0n.[NOTES] is not null
LEFT JOIN PW001C20 c20manning
ON c20manning.CODE = MANAGENTCODE
LEFT JOIN PWCCMFLIGHTARRANGEMENTS fb
ON fb.pin = p01.pin
AND fb.SEQNOM = ISNULL(cclOn.SEQNO, ccEst.SEQNO)
AND fb.SIGNON = 1
AND fb.BOOKING_STATUS IN ('ISSUED', 'APPROVED', 'REISSUED')
LEFT JOIN (
SELECT SEQNOM,
PIN,
SCHEDULED_ARRIVAL_TIME AS ETA,
SCHEDULED_DEPARTURE_TIME AS ETD
FROM PWCCMFLIGHTARRANGEMENTS
WHERE signon = 1
AND BOOKING_STATUS IN ('ISSUED', 'APPROVED', 'REISSUED')
) fbat
ON fbat.pin = p01.pin
AND fbat.SEQNOM = ISNULL(cclOn.SEQNO, ccEst.SEQNO)
LEFT JOIN PW001C79 c79e
ON c79e.CODE = p03plan.EMBARKATION
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
)
)
)
LEFT JOIN PW001P20 p20
ON p20.PIN = p01.PIN
AND p20.HISTORICAL = 'F'
AND NOT EXISTS (
SELECT 1
FROM PW001P20 t
WHERE t.PIN = p20.PIN
AND t.HISTORICAL = 'F'
AND t.DATESTART > p20.DATESTART
)
LEFT JOIN PW001OLEDOCS sdoc
ON sdoc.DOCNO = p20.SCANNEDDOCNO
LEFT JOIN PW001C02 C02CONT
ON p20.RANK = C02CONT.CODE
LEFT JOIN PW001C32 C32CONT
ON p20.CONTRACTTYPE = C32CONT.code
LEFT JOIN PW001P0U p0u
ON p0u.PIN = p01.PIN
LEFT JOIN PW001P20U p20u
ON p20u.CONTRACT_SEQNO = p20.SEQUENCENO
LEFT JOIN PW001C02 cRANK
ON cRank.CODE = p01.[RANK]
LEFT JOIN PWORG org
ON org.NUMORGID = p01.CLIENT
LEFT JOIN PW001C43 c43
ON c43.CODE = p01.COSTPLACE |
