CREATE VIEW dbo.PW001SRV56
AS
SELECT m.PIN,
m.ALTERNATIVEPIN as 'ALTERNATIVE PIN',
m.[Full Name],
m.[Organization],
m.[Current Rank],
m.[Nationality],
m.[Document type],
m.[Document Code],
m.[Document Name],
m.[Document Status],
m.[Issued date],
m.[Expiry date],
m.EMPLOYMENTSTARTDATE,
m.EMPLOYMENTENDDATE,
m.NUMORGID
FROM (
SELECT DISTINCT P01.PIN,
P01.ALTERNATIVEPIN,
p01.NAME as 'Full Name',
ORG.Name as 'Organization',
c02.NAME AS 'Current Rank',
nat.NATIONALITY as 'Nationality',
doc.Type AS 'Document type',
doc.status as 'Document Status',
doc.Code 'Document Code',
doc.DocName AS 'Document Name',
doc.DATEFROM AS 'Issued date',
doc.EXPIRYDATE AS 'Expiry date',
P01.CLIENT NUMORGID,
P01.EMPLOYMENTSTARTDATE,
P01.EMPLOYMENTENDDATE
FROM PW001P01 p01
--Required document per position
JOIN (
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,
c06.TEXT AS DocName,
p05.DATEFROM,
p05.EXPIRYDATE
--link to sailing activity
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%'
LEFT JOIN PWORGPRC prc
ON prc.NUMORGID = p03.NUMORGID
WHERE p03.DATETO >= CONVERT(VARCHAR, GETDATE(), 112)
OR p03.DATETO IS NULL
) prc
LEFT JOIN PW001C06 c06
ON c06.CODE = prc.COMPETENCE
LEFT JOIN PW001P05 p05
ON p05.PIN = prc.PIN
AND prc.COMPETENCE = p05.CODE
--required medical/travel documents on org level
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.CODE IS NULL THEN 'Missing'
WHEN p07.CODE IS NULL THEN 'Missing'
WHEN p08.CODE IS NOT NULL THEN 'Expiring'
WHEN p07.CODE IS NOT NULL THEN 'Expiring'
END AS STATUS,
rtd.DOCCODE AS Code,
CASE rtd.DOCTYPE
WHEN 8 THEN c23.VISATYPE
WHEN 7 THEN c24.TEXT
END AS DocName,
CASE rtd.DOCTYPE
WHEN 8 THEN p08.DATEFROM
WHEN 7 THEN p07.DATEISSUED
END AS DateFrom,
CASE rtd.DOCTYPE
WHEN 8 THEN p08.DATETO
WHEN 7 THEN p07.EXPIRYDATE
END AS ExpiryDate
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
-- 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 p07.CODE IS NULL THEN 'Missing'
WHEN p07.CODE IS NOT NULL THEN 'Expiring'
END AS STATUS,
reqDoc.[DOCUMENT] AS Code,
CASE reqDoc.DOCTYPE
WHEN 8 THEN c23.VISATYPE
WHEN 7 THEN c24.TEXT
END AS DocName,
CASE reqDoc.DOCTYPE
WHEN 8 THEN p08.DATEFROM
WHEN 7 THEN p07.DATEISSUED
END AS DateFrom,
CASE reqDoc.DOCTYPE
WHEN 8 THEN p08.DATETO
WHEN 7 THEN p07.EXPIRYDATE
END AS ExpiryDate
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
ON doc.PIN = p01.PIN
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 PWORG ORG
ON ORG.NUMORGID=P01.CLIENT
--ORDER BY
-- 1
)m
