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( WHEN (
docDetails.SCANVALIDITY = 0 OR docDetails.SCANVALIDITY IS NULL = 0
) ANDOR scanneddocnodocDetails.SCANVALIDITY IS NOTNULL
NULL THEN NCHAR(10006) + ' Not-Confirmed' )
--ELSE 'N/A' AND scanneddocno IS NOT END AS 'Scan Status'NULL THEN NCHAR(10006) +
FROM ( ' Not-Confirmed'
SELECT prc.PIN, --ELSE 'N/A'
prc.[TYPE], END CASE AS 'Scan Status'
FROM (
WHEN p05.CODE IS NULL THEN 'Missing' 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)
WHEN p05.CODE IS NOT NULL THEN 'Expiring' OR p03.DATETO IS NULL
END AS STATUS, ) prc
prc.COMPETENCE AS CODE, LEFT JOIN PW001P05 p05
p05.SEQUENCENO ON p05.PIN = prc.PIN
FROM ( AND SELECTprc.COMPETENCE DISTINCT= p03p05.PIN,CODE
) doc
CASELEFT JOIN PW001P01 p01
ON p01.PIN = doc.PIN
WHEN c12.CODE IS NOT NULL THEN 'Competence/Certificate'LEFT JOIN PWORG org
ON org.NUMORGID = p01.CLIENT
END AS TYPE, LEFT JOIN PW001P0P p0p
ON prc p0p.COMPETENCEPIN = p01.PIN
LEFT JOIN PW001C02 c02
FROM PW001P03 p03 ON c02.CODE = p0p.POSITIONID
LEFT JOIN PWCOUNTRY JOINnat
PW001C12 c12 ON nat.COUNTRYCODE = p01.NATIONALITY
ON c12.CODELEFT =JOIN p03.CODE(
SELECT CODE,
AND c12.OPTIONS LIKE '%S%' [TEXT]
JOIN PWORGPRC prc FROM PW001C06
UNION ALL
ON prc.NUMORGID = p03.NUMORGID SELECT CODE,
WHERE p03.DATETO >= CONVERT(VARCHAR, GETDATE(), 112) VISATYPE AS [TEXT]
OR p03.DATETO IS NULL FROM PW001C23
) prc UNION ALL
LEFT JOIN PW001P05 p05 SELECT CODE,
ON p05.PIN = prc.PIN [TEXT]
AND prc.COMPETENCE = p05.CODE FROM PW001C24
) doc )docName
LEFT JOIN PW001P01 p01 ON p01 docName.PINCODE = doc.PINCODE
LEFT JOIN PWORG orgLEFT JOIN (
ON org.NUMORGID = p01.CLIENT SELECT PIN,
LEFT JOIN PW001P0P p0p ON p0p.PIN = p01.PIN SEQUENCENO,
LEFT JOIN PW001C02 c02 ON c02.CODE = p0p.POSITIONID DATEFROM,
LEFT JOIN PWCOUNTRY nat ON nat.COUNTRYCODE = p01.NATIONALITY EXPIRYDATE,
LEFT JOIN ( SELECT CODESCANNEDDOCNO,
[TEXT] SCANVALIDITY
FROM PW001C06 FROM PW001P05
UNION ALL SELECT CODE, UNION ALL
VISATYPE AS [TEXT] SELECT PIN,
FROM PW001C23 SEQUENCENO,
UNION ALL SELECT CODE, DATEISSUED AS DATEFROM,
[TEXT] EXPIRYDATE,
FROM PW001C24 )docName SCANNEDDOCNO,
ON docName.CODE = doc.CODE LEFT JOIN ( SCANVALIDITY
SELECT SEQUENCENO, FROM PW001P07
DATEFROM, UNION ALL
EXPIRYDATE, SELECT SCANNEDDOCNOPIN,
SCANVALIDITY SEQUENCENO,
FROM PW001P05 DATEFROM,
UNION ALL SELECT SEQUENCENO, DATETO AS EXPIRYDATE,
DATEISSUED AS DATEFROM, SCANNEDDOCNO,
EXPIRYDATE, SCANNEDDOCNO,SCANVALIDITY
SCANVALIDITY FROM PW001P08
FROM PW001P07 ) docDetails
UNION ALL ON docDetails.SEQUENCENO = doc.SEQUENCENO
SELECT SEQUENCENO, AND docDetails.PIN = p01.PIN
DATEFROM, JOIN PW001P03 p03plan
DATETO AS EXPIRYDATE, ON p03plan.PIN = p01.PIN
SCANNEDDOCNO, AND p03plan.PLANNED = 'Y'
SCANVALIDITY AND p03plan.CODE IN (SELECT t.CODE
FROM PW001P08 ) docDetails ON docDetails.SEQUENCENO =FROM doc.SEQUENCENO pw001c12 t
JOIN PW001P03 p03plan ON p03plan.PIN = p01.PIN WHERE AND p03plant.PLANNEDOPTIONS =LIKE 'Y%S%')
AND p03plan.CODE IN (SELECT t.CODE AND NOT EXISTS (
FROM pw001c12 t SELECT 1
WHERE t.OPTIONS LIKE '%S%') FROM PW001P03 p03t
AND NOT EXISTS ( SELECT 1 WHERE p03t.PIN = p01.PIN
FROM PW001P03 p03t WHERE AND p03t.PINPLANNED = p01.PIN 'Y'
AND p03t.PLANNED = 'Y' AND p03t.CODE IN (SELECT t.CODE
AND p03t.CODE IN (SELECT t.CODE FROM PW001C12 t
FROM PW001C12 t WHERE t.OPTIONS LIKE '%S%')
AND p03t.DATEFROM < p03plan.DateFrom
)
LEFT JOIN (
SELECT NUMORGID,
dbo.ad_scanorgtree(NUMORGID, 3) AS VesselID,) 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 |