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 END AS 'Scan Status'
FROM ( FROM (
SELECT prc.PIN, SELECT DISTINCT * --JMV Fetch distinct value as a quickfix
prc.[TYPE], FROM (
CASE SELECT prc.PIN,
WHEN p05.CODE IS NULL THEN 'Missing' prc.[TYPE],
WHEN p05.CODE IS NOT NULL THEN 'Expiring' END AS STATUS, CASE
prc.COMPETENCE AS CODE, WHEN p05.CODE IS NULL THEN 'Missing'
p05.SEQUENCENO FROM ( WHEN p05.CODE IS NOT NULL THEN 'Expiring'
SELECT DISTINCT p03.PIN, END AS STATUS,
CASE prc.COMPETENCE AS CODE,
WHEN c12.CODE IS NOT NULL THEN 'Competence/Certificate' p05.SEQUENCENO
END AS TYPE, FROM (
prc.COMPETENCE FROM PW001P03 p03 SELECT DISTINCT p03.PIN,
JOIN PW001C12 c12 CASE
ON c12.CODE = p03.CODE WHEN c12.CODE IS NOT NULL THEN 'Competence/Certificate'
AND c12.OPTIONS LIKE '%S%' JOIN PWORGPRCEND prcAS TYPE,
ON prc.NUMORGID = p03.NUMORGID prc.COMPETENCE
WHERE p03.DATETO >= CONVERT(VARCHAR, GETDATE(), 112) FROM PW001P03 p03
OR p03.DATETO IS NULL ) prcJOIN PW001C12 c12
LEFT JOIN PW001P05 p05 ON p05c12.PINCODE = prcp03.PINCODE
AND prc.COMPETENCE = p05.CODE AND UNIONc12.OPTIONS ALLLIKE '%S%'
SELECT p.PIN, CASE JOIN PWORGPRC prc
WHEN c23.CODE IS NOT NULL THEN 'Travel' ON prc.NUMORGID = p03.NUMORGID
WHEN c24.code IS NOT NULL THEN 'Medical' END WHERE p03.DATETO >= AS TYPECONVERT(VARCHAR, GETDATE(), 112)
CASE OR WHEN p08.SEQUENCENOp03.DATETO IS NOT NULL
THEN 'Expiring' WHEN p07.SEQUENCENO IS NOT NULL THEN 'Expiring' ) prc
WHEN p07.SEQUENCENO IS NULL THEN 'Missing' LEFT JOIN PW001P05 p05
WHEN p08.SEQUENCENO IS NULL THEN 'Missing' ON p05.PIN = prc.PIN
END AS STATUS, rtd.DOCCODE AS Code, AND p05.CODE = dbo.ad_ReturnReplacingCode(p05.pin, prc.COMPETENCE, 0)
CASE rtd.DOCTYPE
WHEN 8 THEN p08.SEQUENCENO UNION ALL
WHEN 7 THEN p07.SEQUENCENO SELECT p.PIN,
END AS SEQUENCENO CASE
FROM PW001P01 p JOIN ( WHEN c23.CODE IS NOT NULL THEN 'Travel'
SELECT DISTINCT p03.PIN, WHEN c24.code IS NOT NULL THEN 'Medical'
dbo.ad_scanorgtree(p03.NUMORGID, 2) companyID END AS TYPE,
FROM PW001P03 p03 CASE
JOIN PW001C12 c12 WHEN p08.SEQUENCENO IS NOT NULL THEN 'Expiring'
ON c12.CODE = p03.CODE WHEN p07.SEQUENCENO IS NOT NULL THEN 'Expiring'
AND c12.OPTIONS LIKE '%S%' WHERE WHEN p03.DATETO >= CONVERT(VARCHAR, GETDATE(), 112)p07.SEQUENCENO IS NULL THEN 'Missing'
OR WHEN p03p08.DATETOSEQUENCENO IS NULL THEN 'Missing'
) p03 END AS STATUS,
ON p03.PIN = p.PIN rtd.DOCCODE AS Code,
JOIN PWORGRTD rtd ON CASE rtd.NUMORGIDDOCTYPE
= p03.companyID LEFT JOIN PW001C23 c23 WHEN 8 THEN p08.SEQUENCENO
ON c23.CODE = rtd.DOCCODE WHEN 7 THEN p07.SEQUENCENO
AND rtd.DOCTYPE = 8 LEFT JOIN PW001P08 p08 END AS SEQUENCENO
ON p08.PIN = p.PIN FROM PW001P01 p
AND p08.CODE = c23.CODE JOIN (
LEFT JOIN PW001C24 c24 ON c24.CODESELECT =DISTINCT rtdp03.DOCCODEPIN,
AND rtd.DOCTYPE = 7 dbo.ad_scanorgtree(p03.NUMORGID, 2) companyID
LEFT JOIN PW001P07 p07 ON p07.PIN = p.PIN FROM PW001P03 p03
AND p07.CODE = c24.CODE JOIN PW001C12 c12
UNION ALL SELECT p01.PIN, CASEON c12.CODE = p03.CODE
WHEN c23.CODE IS NOT NULL THEN 'Travel' AND c12.OPTIONS LIKE '%S%'
WHEN c24.code IS NOT NULL THEN 'Medical' END AS TYPE, WHERE p03.DATETO >= CONVERT(VARCHAR, GETDATE(), 112)
CASE WHEN p08.SEQUENCENO IS NOT NULL THEN 'Expiring' OR p03.DATETO IS NULL
WHEN p07.SEQUENCENO IS NOT NULL THEN 'Expiring' ) p03
WHEN p07.SEQUENCENO IS NULL THEN 'Missing' ON p03.PIN = p.PIN
WHEN p08.SEQUENCENO IS NULL THEN 'Missing' END AS STATUS, JOIN PWORGRTD rtd
reqDoc.[DOCUMENT] AS Code, ON rtd.NUMORGID = p03.companyID
CASE WHEN c23.CODE IS NOT NULL THENLEFT p08.SEQUENCENOJOIN PW001C23 c23
WHEN c24.code IS NOT NULL THEN p07.SEQUENCENO ON c23.CODE = rtd.DOCCODE
END AS SEQUENCENO FROM ( AND rtd.DOCTYPE = 8
SELECT DISTINCT p03.PIN, LEFT JOIN PW001P08 p08
dbo.ad_scanorgtree(p03.NUMORGID, 2) companyID, ON p08.PIN = p.PIN
p.NATIONALITY FROM PW001P03 p03 AND p08.CODE = c23.CODE
JOIN PW001C12 c12 LEFT JOIN PW001C24 c24
ON c12.CODE = p03.CODE ON c24.CODE = rtd.DOCCODE
AND c12.OPTIONS LIKE '%S%' AND rtd.DOCTYPE = 7
LEFT JOIN PW001P01 P LEFT JOIN PW001P07 p07
ON p.PIN = p03.PIN ON p07.PIN = p.PIN
WHERE p03.DATETO >= CONVERT(VARCHAR, GETDATE(), 112) AND p07.CODE = c24.CODE
OR p03.DATETO IS NULL ) p01
CROSS APPLY ( UNION ALL
SELECT 7 AS DOCTYPE, SELECT p01.PIN,
* FROM CASE RP_NAT_REQDOC_MEDICAL
UNION ALL WHEN c23.CODE IS NOT NULL THEN SELECT'Travel'
8 AS DOCTYPE, * WHEN c24.code IS NOT NULL THEN 'Medical'
FROM RP_NAT_REQDOC_TRAVEL ) reqDoc END AS TYPE,
LEFT JOIN PW001C23 c23 CASE
ON c23.CODE = reqDoc.DOCUMENT AND reqDoc.DOCTYPE = 8 WHEN p08.SEQUENCENO IS NOT NULL THEN 'Expiring'
LEFT JOIN PW001P08 p08 WHEN p07.SEQUENCENO IS NOT NULL THEN 'Expiring'
ON p08.PIN = p01.PIN AND p08.CODE = c23.CODE WHEN p07.SEQUENCENO IS NULL THEN 'Missing'
LEFT JOIN PW001C24 c24 WHEN p08.SEQUENCENO IS NULL THEN 'Missing'
ON c24.CODE = reqDoc.DOCUMENT AND reqDoc.DOCTYPE = 7END AS STATUS,
LEFT JOIN PW001P07 p07 reqDoc.[DOCUMENT] AS Code,
ON p07.PIN = p01.PIN CASE
AND p07.CODE = reqDoc.[DOCUMENT] LEFT JOIN PWCOUNTRY c WHEN c23.CODE IS NOT NULL THEN p08.SEQUENCENO
ON c.COUNTRYCODE = p01.NATIONALITY WHEN c24.code IS NOT NULL THEN p07.SEQUENCENO
LEFT JOIN RP_NAT_EXCLUDED_MEDICAL excMed ON excMed.NATIONALITYEND =AS c.COUNTRYCODESEQUENCENO
AND excMed.[DOCUMENT] = reqDoc.[DOCUMENT]FROM (
WHERE excMed.NATIONALITY IS NULL ) doc SELECT DISTINCT p03.PIN,
LEFT JOIN PW001P01 p01 ON p01.PIN = doc.PIN dbo.ad_scanorgtree(p03.NUMORGID, 2) companyID,
LEFT JOIN PWORG org ON org.NUMORGID = p01.CLIENTp.NATIONALITY
FROM PW001P03 p03
LEFT JOIN PW001P0P p0p ON p0p.PIN = p01.PIN 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
)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%' --JMV Filter out passive document codes to avoid duplicates
LEFT JOIN PW001C02 c02 UNION ALL
ON c02.CODE = p0p.POSITIONID SELECT CODE,
LEFT JOIN PWCOUNTRY nat ON nat.COUNTRYCODE = p01.NATIONALITY VISATYPE AS [TEXT]
LEFT JOIN ( FROM PW001C23
SELECT CODE, WHERE OPTIONS NOT LIKE '%P%'--JMV Filter out passive document codes [TEXT]to avoid duplicates
FROM PW001C06 UNION ALL
UNION ALL SELECT CODE,
SELECT CODE, [TEXT]
VISATYPE AS [TEXT] FROM PW001C24
FROM PW001C23 WHERE OPTIONS NOT LIKE '%P%'--JMV Filter out passive document codes UNIONto ALLavoid duplicates
SELECT CODE,)docName
ON docName.CODE = doc.CODE
[TEXT] LEFT JOIN (
FROM PW001C24 SELECT )docNamePIN,
ON docName.CODE = doc.CODE SEQUENCENO,
LEFT JOIN ( SELECT PINDATEFROM,
SEQUENCENO, EXPIRYDATE,
DATEFROM, SCANNEDDOCNO,
EXPIRYDATE, SCANVALIDITY
SCANNEDDOCNO, FROM PW001P05
SCANVALIDITY UNION ALL
FROM PW001P05 SELECT PIN,
UNION ALL SEQUENCENO,
SELECT PIN, DATEISSUED AS DATEFROM,
SEQUENCENO, DATEISSUED AS DATEFROMEXPIRYDATE,
EXPIRYDATE, SCANNEDDOCNO,
SCANNEDDOCNO, SCANVALIDITY
SCANVALIDITY FROM PW001P07
FROM PW001P07 UNION ALL
UNION ALL SELECT PIN,
SELECT PIN, SEQUENCENO,
SEQUENCENO, DATEFROM,
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, 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
)
--ORDER BY PIN
--AND final.pin = 800027
|
