CREATE VIEW dbo.PW001SRV86PW001SRV56
AS
SELECT m.PIN,
m.ALTERNATIVEPIN as 'ALTERNATIVE PIN',
m.[Full Name],
m.[Organization Name],
m.[Current Rank],
m.[Nationality],
m.[Document Typetype],
m.[Document Code],
m.[Document Name],
m.[Document Status],
m.[Issued Datedate],
m.[Expiry Datedate],
VESSEL_NUMORGIDm.EMPLOYMENTSTARTDATE,
[Embarkation Date]m.EMPLOYMENTENDDATE,
[Disembarkation Date],m.NUMORGID
FROM (
[Activity Vessel], SELECT [Activity Department],DISTINCT P01.PIN,
[Activity Position], [Scan Status] P01.ALTERNATIVEPIN,
NUMORGID, EMPLOYMENTSTARTDATE, p01.NAME as 'Full Name',
EMPLOYMENTENDDATE FROMORG.Name (
as 'Organization',
SELECT p01.PIN, c02.NAME AS P01.ALTERNATIVEPIN'Current Rank',
p01nat.CLIENTNATIONALITY as 'Nationality',
NUMORGID, doc.Type AS 'Document type',
P01.EMPLOYMENTSTARTDATE, doc.status as P01.EMPLOYMENTENDDATE'Document Status',
p01doc.[NAME]Code 'Document Code',
AS 'Full Name', doc.DocName AS 'Document Name',
org.[NAME] doc.DATEFROM AS 'OrganizationIssued Namedate',
c02.NAMEdoc.EXPIRYDATE AS 'Expiry date',
AS 'Current Rank', P01.CLIENT NUMORGID,
nat.NATIONALITY AS 'Nationality' P01.EMPLOYMENTSTARTDATE,
docP01.[TYPE]EMPLOYMENTENDDATE
FROM AS 'Document Type',PW001P01 p01
doc.CODE --Required document per position
AS 'Document Code', JOIN (
docName.[TEXT] AS 'Document Name', SELECT prc.PIN,
doc.[STATUS] AS 'Document Status', prc.type,
docDetails.DATEFROM AS 'Issued Date', docDetails.EXPIRYDATECASE
AS 'Expiry Date', ves.NUMORGID AS VESSEL_NUMORGID, WHEN p05.CODE IS NULL THEN 'Missing'
P03.DATEFROM AS 'Embarkation Date', ISNULL(P03.DATETO, P03.TODATEESTIMATED) AS 'Disembarkation Date',
WHEN p05.CODE IS NOT NULL THEN 'Expiring'
ves.[NAME] AS 'Activity Vessel', END AS STATUS,
dep.[NAME] AS 'Activity Department', prc.COMPETENCE AS CODE,
pos.[NAME] AS 'Activity Position', c06.TEXT AS DocName,
CASE WHEN ( p05.DATEFROM,
docDetails.scanneddocno IS NULL p05.EXPIRYDATE
OR oledoc.[DOCUMENT] IS NULL --link to sailing activity
) THEN NCHAR(9940) + ' Missing' FROM (
WHEN docDetails.SCANVALIDITY = 1 SELECT DISTINCT p03.PIN,
AND docDetails.scanneddocno IS NOT NULL THEN NCHAR(10004) + ' Confirmed' CASE
WHEN ( docDetails.SCANVALIDITY = 0 WHEN c12.CODE IS NOT NULL THEN 'Competence/Certificate'
OR docDetails.SCANVALIDITY IS NULL END AS )TYPE,
AND scanneddocno IS NOT NULL THEN NCHAR(10006) + prc.COMPETENCE
' Not-Confirmed' FROM PW001P03 p03
--ELSE 'N/A' END ASJOIN 'ScanPW001C12 Status'c12
FROM ( SELECT prc.PIN, ON c12.CODE = p03.CODE
prc.[TYPE], CASE AND c12.OPTIONS LIKE '%S%'
WHEN p05.CODE IS NULL THEN 'Missing' LEFT JOIN PWORGPRC prc
WHEN p05.CODE IS NOT NULL THEN 'Expiring' ON ENDprc.NUMORGID AS STATUS,= p03.NUMORGID
prc.COMPETENCE AS CODE, WHERE p03.DATETO >= CONVERT(VARCHAR, GETDATE(), 112)
p05.SEQUENCENO FROM ( OR p03.DATETO IS NULL
SELECT DISTINCT p03.PIN, ) prc
CASE LEFT JOIN PW001C06 c06
ON WHEN c12c06.CODE IS NOT NULL THEN 'Competence/Certificate'= prc.COMPETENCE
LEFT JOIN PW001P05 p05
END AS TYPE, ON p05.PIN = prc.COMPETENCEPIN
FROM PW001P03 p03 AND prc.COMPETENCE = p05.CODE
--required medical/travel documents on org level
JOIN PW001C12 c12 UNION ALL
ON c12.CODE = p03.CODE SELECT p.PIN,
CASE
AND c12.OPTIONS LIKE '%S%' WHEN c23.CODE IS NOT NULL THEN 'Travel'
JOIN PWORGPRC prc WHEN c24.code IS NOT NULL THEN 'Medical'
ON prc.NUMORGID = p03.NUMORGID END AS TYPE,
WHERE p03.DATETO >= CONVERT(VARCHAR, GETDATE(), 112) CASE
OR p03.DATETO IS NULL WHEN p08.CODE IS NULL THEN 'Missing'
) prc LEFT JOIN PW001P05 p05WHEN p07.CODE IS NULL THEN 'Missing'
ON p05.PIN = prc.PIN WHEN p08.CODE IS NOT NULL THEN 'Expiring'
AND prc.COMPETENCE = p05.CODE WHEN p07.CODE IS NOT NULL THEN 'Expiring'
UNION ALL SELECT p.PIN, END AS STATUS,
CASE rtd.DOCCODE AS Code,
WHEN c23.CODE IS NOT NULL THEN 'Travel' CASE rtd.DOCTYPE
WHEN c24.code IS NOT NULL THEN 'Medical' WHEN 8 THEN c23.VISATYPE
END AS TYPE, WHEN 7 THEN c24.TEXT
CASE END AS DocName,
WHEN p08.SEQUENCENO IS NOT NULL THEN 'Expiring' CASE rtd.DOCTYPE
WHEN p07.SEQUENCENO IS NOT NULL THEN 'Expiring' WHEN 8 THEN p08.DATEFROM
WHEN p07.SEQUENCENO IS NULL THEN 'Missing' WHEN 7 THEN p07.DATEISSUED
WHEN p08.SEQUENCENO IS NULL THEN 'Missing' END AS DateFrom,
END AS STATUS, CASE rtd.DOCTYPE
rtd.DOCCODE AS Code, WHEN 8 THEN p08.DATETO
CASE rtd.DOCTYPE WHEN 87 THEN p08p07.SEQUENCENOEXPIRYDATE
WHEN 7END THEN p07.SEQUENCENOAS ExpiryDate
ENDFROM PW001P01 p
AS SEQUENCENO FROM PW001P01 p JOIN (
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 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
-- required travel/docs per nationality
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 p08p07.SEQUENCENOCODE IS NOT NULL THEN 'ExpiringMissing'
WHEN p07.SEQUENCENOCODE IS NOT NULL THEN 'Expiring'
WHENEND p07.SEQUENCENO ISAS NULLSTATUS,
THEN 'Missing' reqDoc.[DOCUMENT] AS WHENCode,
p08.SEQUENCENO IS NULL THEN 'Missing' CASE reqDoc.DOCTYPE
END AS STATUS, reqDoc.[DOCUMENT] AS Code, WHEN 8 THEN c23.VISATYPE
CASE WHEN 7 THEN c24.TEXT
WHEN c23.CODE IS NOT NULL THEN p08.SEQUENCENO END AS DocName,
WHEN c24.code IS NOT NULL THEN p07.SEQUENCENO CASE reqDoc.DOCTYPE
END AS SEQUENCENO WHEN 8 THEN p08.DATEFROM
FROM ( WHEN SELECT7 DISTINCTTHEN p03.PIN,p07.DATEISSUED
END AS DateFrom,
dbo.ad_scanorgtree(p03.NUMORGID, 2) companyID, CASE reqDoc.DOCTYPE
p.NATIONALITY WHEN 8 THEN p08.DATETO
FROM PW001P03 p03 WHEN 7 THEN p07.EXPIRYDATE
JOIN PW001C12 c12 END AS ExpiryDate
ON c12.CODE = p03.CODE FROM (
SELECT ANDDISTINCT c12p03.OPTIONSPIN,
LIKE '%S%' LEFT JOIN PW001P01 P
dbo.ad_scanorgtree(p03.NUMORGID, 2) companyID,
ON p.PINNATIONALITY
= p03.PIN WHERE p03.DATETO >= CONVERT(VARCHAR, GETDATE(), 112)
FROM PW001P03 p03
OR p03.DATETO IS NULL JOIN PW001C12 c12
) p01 ON c12.CODE = p03.CODE
CROSS APPLY ( SELECT 7 AS DOCTYPE, AND c12.OPTIONS LIKE '%S%'
* FROM RP_NAT_REQDOC_MEDICAL LEFT JOIN PW001P01 P
UNION ALL SELECTON 8 AS DOCTYPE,
p.PIN = p03.PIN
* WHERE p03.DATETO >= CONVERT(VARCHAR, GETDATE(), 112)
FROM RP_NAT_REQDOC_TRAVEL ) reqDoc OR p03.DATETO IS NULL
LEFT JOIN PW001C23 c23 )
ON c23.CODE = reqDoc.DOCUMENT p01
AND reqDoc.DOCTYPE = 8 CROSS APPLY (
LEFT JOIN PW001P08 p08 SELECT 7 AS DOCTYPE,
ON p08.PIN = p01.PIN *
AND p08.CODE = c23.CODE FROM RP_NAT_REQDOC_MEDICAL
LEFT JOIN PW001C24 c24 UNION ALL
ON c24.CODE = reqDoc.DOCUMENT SELECT 8 AS DOCTYPE,
AND reqDoc.DOCTYPE = 7 *
LEFT JOIN PW001P07 p07 FROM RP_NAT_REQDOC_TRAVEL
ON p07.PIN =) p01.PINreqDoc
LEFT JOIN PW001C23 c23
AND p07.CODE = reqDoc.[DOCUMENT] LEFT JOIN PWCOUNTRY cON c23.CODE = reqDoc.DOCUMENT
ON c.COUNTRYCODE = p01.NATIONALITY AND reqDoc.DOCTYPE = 8
LEFT JOIN RP_NAT_EXCLUDED_MEDICAL excMed LEFT JOIN PW001P08 p08
ON excMed.NATIONALITY = c.COUNTRYCODE ON p08.PIN = p01.PIN
AND excMed.[DOCUMENT] = reqDoc.[DOCUMENT] AND p08.CODE = c23.CODE
WHERE excMed.NATIONALITY IS NULL ) doc LEFT JOIN PW001C24 c24
LEFT JOIN PW001P01 p01 ON p01.PIN = doc.PIN ON c24.CODE = reqDoc.DOCUMENT
LEFT JOIN PWORG org ON AND orgreqDoc.NUMORGIDDOCTYPE = 7
p01.CLIENT LEFT JOIN PW001P0P p0p LEFT JOIN PW001P07 p07
ON p0p.PIN = p01.PIN LEFT JOIN PW001C02 c02 ON p07.PIN = p01.PIN
ON c02.CODE = p0p.POSITIONID LEFT JOIN PWCOUNTRYAND natp07.CODE = reqDoc.[DOCUMENT]
ON nat.COUNTRYCODE = p01.NATIONALITY LEFT JOIN PWCOUNTRY c
LEFT JOIN ( SELECT CODE, ON c.COUNTRYCODE = p01.NATIONALITY
[TEXT] LEFT JOIN RP_NAT_EXCLUDED_MEDICAL excMed
FROM PW001C06 ON excMed.NATIONALITY = c.COUNTRYCODE
UNION ALL SELECT CODE, AND excMed.[DOCUMENT] = reqDoc.[DOCUMENT]
VISATYPEWHERE AS [TEXT]
excMed.NATIONALITY IS NULL
) doc
FROM PW001C23 ON doc.PIN = p01.PIN
UNION ALL LEFT JOIN PW001P0P p0p
SELECT CODE, ON p0p.PIN = p01.PIN
[TEXT]LEFT JOIN PW001C02 c02
ON FROM c02.CODE = PW001C24p0p.POSITIONID
LEFT JOIN PWCOUNTRY )docNamenat
ON docNamenat.CODECOUNTRYCODE = docp01.CODENATIONALITY
LEFT JOIN PWORG ORG
ON ORG.NUMORGID=P01.CLIENT
LEFT JOIN ( --ORDER BY
SELECT SEQUENCENO, -- 1
)m
DATEFROM,
EXPIRYDATE,
SCANNEDDOCNO,
SCANVALIDITY
FROM PW001P05
UNION ALL
SELECT SEQUENCENO,
DATEISSUED AS DATEFROM,
EXPIRYDATE,
SCANNEDDOCNO,
SCANVALIDITY
FROM PW001P07
UNION ALL
SELECT SEQUENCENO,
DATEFROM,
DATETO AS EXPIRYDATE,
SCANNEDDOCNO,
SCANVALIDITY
FROM PW001P08
) docDetails
ON docDetails.SEQUENCENO = doc.SEQUENCENO
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 NULL OR P03.DATETO >= GETDATE())
AND P03.DATEFROM <= GETDATE()
LEFT JOIN (
SELECT NUMORGID,
dbo.ad_scanorgtree(NUMORGID, 3) AS VesselID,
dbo.ad_scanorgtree(NUMORGID, 4) AS DeptID
FROM pworg
) ou
ON ou.NUMORGID = P03.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 = P03.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 |