CREATE VIEW dbo.PW001SRV56
AS
SELECT final.PIN,
final.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,
p03plan.DATEFROM AS 'Embarkation Date',
ISNULL(p03plan.DATETO, p03plan.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 (
docDetails.SCANVALIDITY = 0
OR docDetails.SCANVALIDITY IS NULL
)
AND scanneddocno IS NOT NULL THEN NCHAR(10006) +
' Not-Confirmed'
--ELSE 'N/A'
END AS 'Scan Status'
FROM (
SELECT prc.PIN,
prc.[TYPE],
CASE
WHEN p05.CODE IS NULL THEN 'Missing'
WHEN p05.CODE IS NOT NULL THEN 'Expiring'
END AS STATUS,
prc.COMPETENCE AS CODE,
p05.SEQUENCENO
FROM (
SELECT DISTINCT p03.PIN,
CASE
WHEN c12.CODE IS NOT NULL THEN 'Competence/Certificate'
END AS TYPE,
prc.COMPETENCE
FROM PW001P03 p03
JOIN PW001C12 c12
ON c12.CODE = p03.CODE
AND c12.OPTIONS LIKE '%S%'
JOIN PWORGPRC prc
ON prc.NUMORGID = p03.NUMORGID
WHERE p03.DATETO >= CONVERT(VARCHAR, GETDATE(), 112)
OR p03.DATETO IS NULL
) prc
LEFT JOIN PW001P05 p05
ON p05.PIN = prc.PIN
AND prc.COMPETENCE = p05.CODE
UNION ALL
SELECT p.PIN,
CASE
WHEN c23.CODE IS NOT NULL THEN 'Travel'
WHEN c24.code IS NOT NULL THEN 'Medical'
END AS TYPE,
CASE
WHEN p08.SEQUENCENO IS NOT NULL THEN 'Expiring'
WHEN p07.SEQUENCENO IS NOT NULL THEN 'Expiring'
WHEN p07.SEQUENCENO IS NULL THEN 'Missing'
WHEN p08.SEQUENCENO IS NULL THEN 'Missing'
END AS STATUS,
rtd.DOCCODE AS Code,
CASE rtd.DOCTYPE
WHEN 8 THEN p08.SEQUENCENO
WHEN 7 THEN p07.SEQUENCENO
END AS SEQUENCENO
FROM PW001P01 p
JOIN (
SELECT DISTINCT p03.PIN,
dbo.ad_scanorgtree(p03.NUMORGID, 2) companyID
FROM PW001P03 p03
JOIN PW001C12 c12
ON c12.CODE = p03.CODE
AND c12.OPTIONS LIKE '%S%'
WHERE p03.DATETO >= CONVERT(VARCHAR, GETDATE(), 112)
OR p03.DATETO IS NULL
) p03
ON p03.PIN = p.PIN
JOIN PWORGRTD rtd
ON rtd.NUMORGID = p03.companyID
LEFT JOIN PW001C23 c23
ON c23.CODE = rtd.DOCCODE
AND rtd.DOCTYPE = 8
LEFT JOIN PW001P08 p08
ON p08.PIN = p.PIN
AND p08.CODE = c23.CODE
LEFT JOIN PW001C24 c24
ON c24.CODE = rtd.DOCCODE
AND rtd.DOCTYPE = 7
LEFT JOIN PW001P07 p07
ON p07.PIN = p.PIN
AND p07.CODE = c24.CODE
UNION ALL
SELECT p01.PIN,
CASE
WHEN c23.CODE IS NOT NULL THEN 'Travel'
WHEN c24.code IS NOT NULL THEN 'Medical'
END AS TYPE,
CASE
WHEN p08.SEQUENCENO IS NOT NULL THEN 'Expiring'
WHEN p07.SEQUENCENO IS NOT NULL THEN 'Expiring'
WHEN p07.SEQUENCENO IS NULL THEN 'Missing'
WHEN p08.SEQUENCENO IS NULL THEN 'Missing'
END AS STATUS,
reqDoc.[DOCUMENT] AS Code,
CASE
WHEN c23.CODE IS NOT NULL THEN p08.SEQUENCENO
WHEN c24.code IS NOT NULL THEN p07.SEQUENCENO
END AS SEQUENCENO
FROM (
SELECT DISTINCT p03.PIN,
dbo.ad_scanorgtree(p03.NUMORGID, 2) companyID,
p.NATIONALITY
FROM PW001P03 p03
JOIN PW001C12 c12
ON c12.CODE = p03.CODE
AND c12.OPTIONS LIKE '%S%'
LEFT JOIN PW001P01 P
ON p.PIN = p03.PIN
WHERE p03.DATETO >= CONVERT(VARCHAR, GETDATE(), 112)
OR p03.DATETO IS NULL
) p01
CROSS APPLY (
SELECT 7 AS DOCTYPE,
*
FROM RP_NAT_REQDOC_MEDICAL
UNION ALL
SELECT 8 AS DOCTYPE,
*
FROM RP_NAT_REQDOC_TRAVEL
) reqDoc
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 (
SELECT CODE,
[TEXT]
FROM PW001C06
UNION ALL
SELECT CODE,
VISATYPE AS [TEXT]
FROM PW001C23
UNION ALL
SELECT CODE,
[TEXT]
FROM PW001C24
)docName
ON docName.CODE = doc.CODE
LEFT JOIN (
SELECT PIN,
SEQUENCENO,
DATEFROM,
EXPIRYDATE,
SCANNEDDOCNO,
SCANVALIDITY
FROM PW001P05
UNION ALL
SELECT PIN,
SEQUENCENO,
DATEISSUED AS DATEFROM,
EXPIRYDATE,
SCANNEDDOCNO,
SCANVALIDITY
FROM PW001P07
UNION ALL
SELECT PIN,
SEQUENCENO,
DATEFROM,
DATETO AS EXPIRYDATE,
SCANNEDDOCNO,
SCANVALIDITY
FROM PW001P08
) docDetails
ON docDetails.SEQUENCENO = doc.SEQUENCENO
AND docDetails.PIN = p01.PIN
JOIN PW001P03 p03plan
ON p03plan.PIN = p01.PIN
AND p03plan.PLANNED = 'Y'
AND p03plan.CODE IN (SELECT t.CODE
FROM pw001c12 t
WHERE t.OPTIONS LIKE '%S%')
AND NOT EXISTS (
SELECT 1
FROM PW001P03 p03t
WHERE p03t.PIN = p01.PIN
AND p03t.PLANNED = 'Y'
AND p03t.CODE IN (SELECT t.CODE
FROM PW001C12 t
WHERE t.OPTIONS LIKE '%S%')
AND p03t.DATEFROM < p03plan.DateFrom
)
LEFT JOIN (
SELECT NUMORGID,
dbo.ad_scanorgtree(NUMORGID, 3) AS VesselID,
dbo.ad_scanorgtree(NUMORGID, 4) AS DeptID
FROM pworg
) ou
ON ou.NUMORGID = p03plan.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 = p03plan.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
