CREATE VIEW dbo.PW001SRV86
AS
SELECT PIN,
ALTERNATIVEPIN,
[Full Name],
[Organization Name],
[Current Rank],
[Nationality],
[Document Type],
[Document Code],
[Document Name],
[Document Status],
[Issued Date],
[Expiry Date],
VESSEL_NUMORGID,
[Embarkation Date],
[Disembarkation Date],
[Activity Vessel],
[Activity Department],
[Activity Position],
[Scan Status],
NUMORGID,
EMPLOYMENTSTARTDATE,
EMPLOYMENTENDDATE
FROM (
SELECT p01.PIN,
P01.ALTERNATIVEPIN,
p01.CLIENT NUMORGID,
P01.EMPLOYMENTSTARTDATE,
P01.EMPLOYMENTENDDATE,
p01.[NAME] AS 'Full Name',
org.[NAME] AS 'Organization Name',
c02.NAME AS 'Current Rank',
nat.NATIONALITY AS 'Nationality',
doc.[TYPE] AS 'Document Type',
doc.CODE AS 'Document Code',
docName.[TEXT] AS 'Document Name',
doc.[STATUS] AS 'Document Status',
docDetails.DATEFROM AS 'Issued Date',
docDetails.EXPIRYDATE AS 'Expiry Date',
ves.NUMORGID AS VESSEL_NUMORGID,
p03cur.DATEFROM AS 'Embarkation Date',
ISNULL(p03cur.DATETO, p03cur.TODATEESTIMATED) AS 'Disembarkation Date',
ves.[NAME] AS 'Activity Vessel',
dep.[NAME] AS 'Activity Department',
pos.[NAME] AS 'Activity Position',
CASE
WHEN (
docDetails.scanneddocno IS NULL
OR oledoc.[DOCUMENT] IS NULL
) THEN NCHAR(9940) + ' Missing'
WHEN docDetails.SCANVALIDITY = 1
AND docDetails.scanneddocno IS NOT NULL THEN NCHAR(10004) + ' Confirmed'
WHEN( WHEN (
docDetails.SCANVALIDITY = 0 OR docDetails.SCANVALIDITY IS= NULL0
) AND scanneddocno IS NOT NULL THEN NCHAR(10006) +
OR docDetails.SCANVALIDITY IS NULL
' Not-Confirmed' )
--ELSE 'N/A' END AS 'Scan Status' AND scanneddocno IS NOT NULL THEN NCHAR(10006) +
FROM ( SELECT prc.PIN, ' Not-Confirmed'
prc.[TYPE], --ELSE 'N/A'
CASEEND AS 'Scan Status'
WHEN p05.CODEFROM IS NULL THEN(
'Missing' SELECT prc.PIN,
WHEN p05.CODE IS NOT NULL THEN 'Expiring' prc.[TYPE],
END AS STATUS, CASE
prc.COMPETENCE AS CODE, WHEN p05.CODE IS NULL THEN 'Missing'
p05.SEQUENCENO FROM (WHEN p05.CODE IS NOT NULL THEN 'Expiring'
SELECT DISTINCT p03.PIN, END AS STATUS,
prc.COMPETENCE AS CODE,
CASE p05.SEQUENCENO
WHEN c12.CODE IS NOT NULL THEN 'Competence/Certificate' FROM (
ENDSELECT ASDISTINCT TYPEp03.PIN,
prc.COMPETENCE CASE
FROM PW001P03 p03 WHEN c12.CODE IS NOT NULL THEN 'Competence/Certificate'
JOIN PW001C12 c12 END AS TYPE,
ON c12.CODE = p03.CODE prc.COMPETENCE
AND c12.OPTIONS LIKE '%S%' FROM PW001P03 p03
JOIN PWORGPRC prc JOIN PW001C12 c12
ON prc.NUMORGID = p03.NUMORGID ON c12.CODE = p03.CODE
WHERE p03.DATETO >= CONVERT(VARCHAR, GETDATE(), 112) AND c12.OPTIONS LIKE '%S%'
OR p03.DATETO IS NULL )JOIN PWORGPRC prc
LEFT JOIN PW001P05 p05 ON prc.NUMORGID = p03.NUMORGID
ON p05.PIN = prc.PIN WHERE p03.DATETO >= CONVERT(VARCHAR, GETDATE(), 112)
AND prc.COMPETENCE = p05.CODE UNION ALL OR p03.DATETO IS NULL
SELECT p.PIN, ) prc
CASE LEFT JOIN PW001P05 p05
WHEN c23.CODE IS NOT NULL THEN 'Travel' ON p05.PIN = prc.PIN
WHEN c24.code IS NOT NULL THEN 'Medical' AND prc.COMPETENCE = p05.CODE
END AS TYPE, UNION ALL
CASE SELECT p.PIN,
WHEN p08.SEQUENCENO ISCASE NOT
NULL THEN 'Expiring' WHEN p07c23.SEQUENCENOCODE IS NOT NULL THEN 'ExpiringTravel'
WHEN p07c24.SEQUENCENOcode IS NOT NULL THEN 'MissingMedical'
END WHEN p08.SEQUENCENO IS NULL THEN 'Missing' AS TYPE,
END CASE
AS STATUS, rtd.DOCCODE AS Code, WHEN p08.SEQUENCENO IS NOT NULL THEN 'Expiring'
CASE rtd.DOCTYPE WHEN p07.SEQUENCENO IS NOT NULL THEN 'Expiring'
WHEN 8 THEN p08.SEQUENCENO WHEN p07.SEQUENCENO IS NULL THEN 'Missing'
WHEN 7 THEN p07.SEQUENCENO WHEN ENDp08.SEQUENCENO IS NULL THEN 'Missing'
AS SEQUENCENO FROM PW001P01END p AS STATUS,
JOIN ( rtd.DOCCODE AS Code,
SELECT DISTINCT p03.PIN, CASE rtd.DOCTYPE
dbo.ad_scanorgtree(p03.NUMORGID, 2) companyID
WHEN 8 THEN p08.SEQUENCENO
FROMWHEN 7 THEN PW001P03p07.SEQUENCENO
p03 END AS SEQUENCENO
JOIN PW001C12 c12 FROM PW001P01 p
ON c12.CODE = p03.CODEJOIN (
SELECT DISTINCT p03.PIN,
AND c12.OPTIONS LIKE '%S%' WHERE p03.DATETO >= CONVERT(VARCHAR, GETDATE(), 112) dbo.ad_scanorgtree(p03.NUMORGID, 2) companyID
FROM PW001P03 ORp03
p03.DATETO IS NULL ) p03 JOIN PW001C12 c12
ON p03.PIN = p.PIN ON JOIN PWORGRTDc12.CODE rtd= p03.CODE
ON rtd.NUMORGID = p03.companyID AND c12.OPTIONS LIKE '%S%'
LEFT JOIN PW001C23 c23 WHERE p03.DATETO ON c23.CODE = rtd.DOCCODE>= CONVERT(VARCHAR, GETDATE(), 112)
AND rtd.DOCTYPE = 8 OR p03.DATETO IS NULL
LEFT JOIN PW001P08 p08 ) p03
ON p08.PIN = p.PIN ON p03.PIN = p.PIN
AND p08.CODE = c23.CODE JOIN PWORGRTD rtd
LEFT JOIN PW001C24 c24 ON rtd.NUMORGID = p03.companyID
ON c24.CODE = rtd.DOCCODE LEFT JOIN PW001C23 c23
AND rtd.DOCTYPE = 7 ON LEFT JOIN PW001P07 p07c23.CODE = rtd.DOCCODE
ON AND p07rtd.PINDOCTYPE = p.PIN8
LEFT JOIN ANDPW001P08 p07.CODEp08
= c24.CODE ON p08.PIN = p.PIN
UNION ALL SELECT p01.PIN, AND p08.CODE = c23.CODE
CASE LEFT JOIN PW001C24 c24
WHEN c23.CODE IS NOT NULL THEN 'Travel' ON c24.CODE = rtd.DOCCODE
WHEN c24.code IS NOT NULL THEN 'Medical' AND rtd.DOCTYPE = 7
END AS TYPE, LEFT JOIN PW001P07 p07
CASE ON p07.PIN WHEN= p08p.SEQUENCENOPIN
IS NOT NULL THEN 'Expiring' AND p07.CODE WHEN= p07c24.SEQUENCENOCODE
IS NOT NULL THEN 'Expiring'
WHEN p07.SEQUENCENO IS NULL THEN 'Missing' UNION ALL
SELECT p01.PIN,
WHEN p08.SEQUENCENO IS NULL THEN 'Missing' CASE
END AS STATUS, reqDoc.[DOCUMENT] AS Code,
WHEN c23.CODE IS NOT NULL THEN 'Travel'
CASE WHEN c24.code IS NOT NULL THEN 'Medical'
WHEN c23.CODE IS NOT NULL THEN p08.SEQUENCENO END AS TYPE,
WHEN c24.code IS NOT NULL THEN p07.SEQUENCENO CASE
END AS SEQUENCENO WHEN p08.SEQUENCENO IS NOT NULL THEN 'Expiring'
FROM ( WHEN p07.SEQUENCENO SELECTIS DISTINCT p03.PIN,
NOT NULL THEN 'Expiring'
WHEN dbo.ad_scanorgtree(p03.NUMORGID, 2) companyID,
p07.SEQUENCENO IS NULL THEN 'Missing'
WHEN pp08.NATIONALITYSEQUENCENO IS NULL THEN 'Missing'
FROM PW001P03END p03 AS STATUS,
reqDoc.[DOCUMENT] AS Code,
JOIN PW001C12 c12 CASE
ON c12.CODE = p03.CODE WHEN c23.CODE IS NOT NULL THEN p08.SEQUENCENO
AND c12.OPTIONS LIKE '%S%' WHEN c24.code IS NOT NULL THEN p07.SEQUENCENO
LEFT JOIN PW001P01 P END AS SEQUENCENO
FROM (
ON p.PIN = p03.PIN SELECT DISTINCT p03.PIN,
WHERE p03.DATETO >= CONVERT(VARCHAR, GETDATE(), 112) dbo.ad_scanorgtree(p03.NUMORGID, 2) companyID,
OR p03.DATETO IS NULL p.NATIONALITY
) p01 CROSS APPLY ( FROM PW001P03 p03
SELECT 7 AS DOCTYPE, JOIN PW001C12 c12
* FROM RP_NAT_REQDOC_MEDICAL ON c12.CODE = p03.CODE
UNION ALL SELECT 8 AS DOCTYPE, AND c12.OPTIONS LIKE '%S%'
* LEFT JOIN PW001P01 P
FROM RP_NAT_REQDOC_TRAVEL ) reqDoc ON p.PIN LEFT= JOINp03.PIN
PW001C23 c23 ONWHERE c23p03.CODEDATETO >= reqDoc.DOCUMENT
CONVERT(VARCHAR, GETDATE(), 112)
AND reqDoc.DOCTYPE = 8 OR p03.DATETO IS NULL
LEFT JOIN PW001P08 p08 ) p01
ON p08.PIN = p01.PIN CROSS APPLY (
SELECT 7 AS DOCTYPE,
AND p08.CODE = c23.CODE *
LEFT JOIN PW001C24 c24 FROM RP_NAT_REQDOC_MEDICAL
ON c24.CODE = reqDoc.DOCUMENT UNION ALL
AND reqDoc.DOCTYPE = 7 SELECT 8 AS DOCTYPE,
LEFT JOIN PW001P07 p07 *
ON p07.PIN = p01.PIN FROM RP_NAT_REQDOC_TRAVEL
AND p07.CODE =) reqDoc.[DOCUMENT]
LEFT JOIN PW001C23 c23
ON c23.CODE = reqDoc.DOCUMENT
AND reqDoc.DOCTYPE = 8
LEFT JOIN PW001P08 p08
ON p08.PIN = p01.PIN
AND p08.CODE = c23.CODE
LEFT JOIN PW001C24 c24
ON c24.CODE = reqDoc.DOCUMENT
AND reqDoc.DOCTYPE = 7
LEFT JOIN PW001P07 p07
ON p07.PIN = p01.PIN
AND p07.CODE = reqDoc.[DOCUMENT]
LEFT JOIN PWCOUNTRY c
ON c.COUNTRYCODE = p01.NATIONALITY
LEFT JOIN RP_NAT_EXCLUDED_MEDICAL excMed
ON excMed.NATIONALITY = c.COUNTRYCODE
AND excMed.[DOCUMENT] = reqDoc.[DOCUMENT]
WHERE excMed.NATIONALITY IS NULL
) doc
LEFT JOIN PW001P01 p01
ON p01.PIN = doc.PIN
LEFT JOIN PWORG org
ON org.NUMORGID = p01.CLIENT
LEFT JOIN PW001P0P p0p
ON p0p.PIN = p01.PIN
LEFT JOIN PW001C02 c02
ON c02.CODE = p0p.POSITIONID
LEFT JOIN PWCOUNTRY nat
ON nat.COUNTRYCODE = p01.NATIONALITY
LEFT JOIN (
LEFT JOIN PWCOUNTRY cSELECT CODE,
ON c.COUNTRYCODE = p01.NATIONALITY [TEXT]
LEFT JOIN RP_NAT_EXCLUDED_MEDICAL excMed
FROM PW001C06
UNION ALL
ON excMed.NATIONALITY = c.COUNTRYCODE SELECT CODE,
AND excMed.[DOCUMENT] = reqDoc.[DOCUMENT] WHERE excMed.NATIONALITY ISVISATYPE NULLAS [TEXT]
) doc FROM PW001C23
LEFT JOIN PW001P01 p01 ON p01.PIN = doc.PIN UNION ALL
LEFT JOIN PWORG org SELECT ONCODE,
org.NUMORGID = p01.CLIENT LEFT JOIN PW001P0P p0p [TEXT]
ON p0p.PIN = p01.PIN LEFT JOIN PW001C02 c02 FROM PW001C24
ON c02.CODE = p0p.POSITIONID )docName
LEFT JOIN PWCOUNTRY nat ON nat docName.COUNTRYCODECODE = p01.NATIONALITYdoc.CODE
LEFT JOIN (
SELECT CODEPIN,
[TEXT] SEQUENCENO,
FROM PW001C06 DATEFROM,
UNION ALL SELECT CODE, EXPIRYDATE,
VISATYPE AS [TEXT] SCANNEDDOCNO,
FROM PW001C23 UNION ALL SCANVALIDITY
SELECT CODE, FROM PW001P05
[TEXT] FROM UNION ALL PW001C24
)docName SELECT PIN,
ON docName.CODE = doc.CODE LEFT JOIN ( SEQUENCENO,
SELECT SEQUENCENO, DATEISSUED AS DATEFROM,
EXPIRYDATE, EXPIRYDATE,
SCANNEDDOCNO, SCANNEDDOCNO,
SCANVALIDITY FROMSCANVALIDITY
PW001P05 UNION ALL FROM PW001P07
SELECT SEQUENCENO, UNION ALL
DATEISSUED AS DATEFROM, SELECT PIN,
EXPIRYDATE, SEQUENCENO,
SCANNEDDOCNO, SCANVALIDITY DATEFROM,
FROM PW001P07 DATETO AS EXPIRYDATE,
UNION ALL SELECT SEQUENCENO, SCANNEDDOCNO,
DATEFROM, SCANVALIDITY
DATETO AS EXPIRYDATE, FROM PW001P08
SCANNEDDOCNO, ) docDetails
SCANVALIDITY ON docDetails.SEQUENCENO = doc.SEQUENCENO
FROM PW001P08 ) docDetails AND docDetails.PIN = p01.PIN
ON docDetails.SEQUENCENO = doc.SEQUENCENO JOIN dbo.PW001P03 P03cur
JOIN dbo.PW001P03 P03cur ON P01.PIN = P03cur.PIN
AND P03cur.CODE IN (SELECT c12.CODE
FROM PW001C12 c12
WHERE c12.OPTIONS LIKE '%S%')
AND (P03cur.DATETO IS NULL OR P03cur.DATETO >= GETDATE())
AND P03cur.DATEFROM <= GETDATE()
AND P03cur.PLANNED <> 'Y''
LEFT JOIN (
SELECT NUMORGID,
dbo.ad_scanorgtree(NUMORGID, 3) AS VesselID,
dbo.ad_scanorgtree(NUMORGID, 4) AS DeptID
FROM pworg
) ou
ON ou.NUMORGID = p03cur.NUMORGID
LEFT JOIN pworg ves
ON ves.NUMORGID = ou.VesselID
LEFT JOIN pworg dep
ON dep.NUMORGID = ou.DeptID
LEFT JOIN pworg pos
ON pos.NUMORGID = p03cur.NUMORGID
LEFT JOIN PW001OLEDOCS oledoc
ON oledoc.DOCNO = docDetails.scanneddocno
) final
WHERE final.[Expiry Date] <= DATEADD(YEAR, 1, CAST(GETDATE() AS DATE))
OR Final.[Expiry Date] IS NULL |