- Created by Arvin John Salandanan on Dec 18, 2024
-
0 link
You are viewing an old version of this content. View the current version.
Compare with Current View Version History
Version 1 Current »
Summary | This view displays currently sailing persons assigned that are planned sign-off. |
|---|---|
Keywords | Crew Change, Sign-off, Disembarkation |
Category | Crew List View |
Description
This view displays historical sailing persons assigned that are signed-off.
It also shows passport, visa, and seaman’s book details.
View Sample
Main Data Selection
All crew who has existing sea-service activity
SQL statement
CREATE VIEW dbo.PW001SRV15 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,
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 'NATIONALITY 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',
--IMPLADC-568 06/27/2024
/*
ISNULL(portOn.portcode, portEst.portcode) 'EMBARKATION PORT CODE',
ISNULL(portOn.NAME, portEst.NAME) 'EMBARKATION PORT',
*/
portOn.portcode 'EMBARKATION PORT CODE',
portOn.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',
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 '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 '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',
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 PW001P03 p03
ON p03.PIN = p01.PIN
--AND ISNULL(p03.DATETO, GETDATE()) >= GETDATE()
AND p03.DATETO IS NOT NULL*/
JOIN dbo.PW001P03 p03
ON P01.PIN = p03.PIN
AND p03.CODE IN (SELECT c12.CODE
FROM PW001C12 c12
WHERE c12.OPTIONS LIKE '%S%')
AND p03.DATETO IS NOT NULL
--AND p03.DATEFROM <= GETDATE()
--AND p03.PLANNED <> 'Y'
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 = '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 P08v.CODE = 'VISASH'
AND NOT EXISTS(SELECT 1
FROM PW001P08 t
WHERE t.PIN = P01.Pin
AND t.CODE = P08v.CODE
AND (
ISNULL(t.DATETO, 0) > ISNULL(P08v.DATETO, 0)
OR ISNULL(t.DATETO, 0) = ISNULL(P08v.DATETO, 0)
AND t.SEQUENCENO > P08v.SEQUENCENO
)
)
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)
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
)
)
)
Columns Specification
Column | Description/ Location in APM |
|---|---|
PIN | Personal Details > Personal > PIN |
Alternative PIN | Personal Details > Personal > Alternative PIN |
NUMORGID | Personal Details > Employment > Organization’s NUMORGID |
Employment Start Date | Personal Details > Employment > Start Date |
Employment End Date | Personal Details > Employment > End Date |
Name | Personal Details > Personal > Full Name |
Native Name | Personal Details > Personal > Native Name |
Last Name | Personal Details > Personal > Last Name |
First Name | Personal Details > Personal > First Name |
Middle Name | Personal Details > Personal > Middle Name |
Gender | Personal Details > Personal > Gender |
Position | Datagroups > Acitivities > Position name |
Department | Datagroups > Acitivities > Department name |
Vessel | Datagroups > Acitivities > Vessel name |
Organization | Personal Details > Personal > Organization |
Date of Birth | Personal Details > Personal > Date of Birth |
Place of Birth | Personal Details > Personal > Place of Birth |
Nationality | Personal Details > Personal > Nationality |
Nationality Code | Personal Details > Personal > Nationality Code |
Naitonality Code ISO | Personal Details > Personal > Nationality Code ISO |
Passport Number | Datagroups > Travel Documents > Passport Number |
Passport Issued Date | Datagroups > Travel Documents > Passport Issued Date |
Passport Expiry | Datagroups > Travel Documents > Passport Expiry |
Seamans Book Number | Datagroups > Travel Documents > Seamans Book Number |
Seamans Book Issued Date | Datagroups > Travel Documents > Seamans Book Issued Date |
Seamans Book Issued By | Datagroups > Travel Documents > Seamans Book Issued by |
Seamans Book Expiry | Datagroups > Travel Documents > Seamans Book Expiry |
Visa Number | Datagroups > Travel Documents > Visa number |
Visa Issued Date | Datagroups > Travel Documents > Visa Issued Date |
Visa Expiry | Datagroups > Travel Documents > Visa Expiry |
Disembarkation Reason | Datagroups > Activity > Disembarkation Reason |
Embarkation Date | Datagroups > Activity > Embarkation Date |
Embarkation Port Code | Datagroups > Activity > Embarkation Port Code |
Embarkation Port Name | Datagroups > Activity > Embarkation Port Name |
Disembarkation Date | Datagroups > Activity > Disembarkation Date |
Disembarkation Port Code | Datagroups > Activity > Disembarkation Port Code |
Disembarkation Port Name | Datagroups > Activity > Disembarkation Port Name |
Manning Pool | Personal Details > Personal > Manning Pool |
Mobile | Personal Details > Personal > Telecommunication > Mobile |
Personal Details > Personal > Telecommunication > Email | |
Home Airport | Personal Details > Personal > Home Airport |
Department/Cost Place Code | Personal Details > Personal > Payroll > Cost Place Code |
Department Cost Place | Personal Details > Personal > Payroll > Cost Place |
- No labels