CREATE VIEW [dbo].[PW001SRV58] 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 DISTINCT * 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 prcp05.COMPETENCECODE = dbo.ad_ReturnReplacingCode(p05.CODEpin, prc.COMPETENCE, 0)
)t
) 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
WHERE OPTIONS NOT LIKE '%P%'
UNION ALL
SELECT CODE,
VISATYPE AS [TEXT]
FROM PW001C23
WHERE OPTIONS NOT LIKE '%P%'
UNION ALL
SELECT CODE,
[TEXT]
FROM PW001C24
WHERE OPTIONS NOT LIKE '%P%'
)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)
|
