CREATE VIEW dbo.PW001SRV86PW001SRV56 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,
P03p03plan.DATEFROM AS 'Embarkation Date',
ISNULL(P03p03plan.DATETO, P03p03plan.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 (
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' CASE END
AS STATUS, 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 SELECT PW001P03DISTINCT 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%' END AS JOINTYPE,
PWORGPRC prc ON prc.NUMORGID = p03.NUMORGID prc.COMPETENCE
WHERE p03.DATETO >= CONVERT(VARCHAR, GETDATE(), 112) FROM PW001P03 p03
OR p03.DATETO IS NULL JOIN PW001C12 )c12
prc LEFT JOIN PW001P05 p05 ON c12.CODE = p03.CODE
ON p05.PIN = prc.PIN AND prc.COMPETENCE = p05.CODE AND c12.OPTIONS LIKE '%S%'
UNION ALL SELECT p.PIN, JOIN PWORGPRC prc
CASE WHEN 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 p05.CODE = dbo.ad_ReturnReplacingCode(p05.pin, prc.COMPETENCE, 0)
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.SEQUENCENO IS NOT NULL THEN 'Expiring'
WHEN p07.SEQUENCENO IS NOT NULL THEN 'Expiring'
WHEN p07.SEQUENCENO IS NULL THEN 'Missing'
WHEN p08.SEQUENCENO IS NULL THEN 'Missing'
END AS STATUS,
rtd.DOCCODE AS Code,
CASE rtd.DOCTYPE
WHEN 8 THEN p08.SEQUENCENO
WHEN 7 THEN p07.SEQUENCENO
END AS SEQUENCENO
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
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, END AS TYPE,
CASE CASE
WHEN p08.SEQUENCENO IS NOT NULL THEN 'Expiring' WHEN p07p08.SEQUENCENO IS NOT NULL THEN 'Expiring'
WHEN p07.SEQUENCENO IS NOT NULL THEN 'Missing'Expiring'
WHEN p08p07.SEQUENCENO IS NULL THEN 'Missing'
END AS STATUS, WHEN p08.SEQUENCENO IS NULL THEN 'Missing'
rtd.DOCCODE AS Code, END AS STATUS,
CASE rtd.DOCTYPE WHEN 8 THEN p08reqDoc.SEQUENCENO[DOCUMENT] AS Code,
WHEN 7 THEN p07.SEQUENCENO CASE
END AS SEQUENCENO WHEN c23.CODE IS NOT NULL FROMTHEN p08.SEQUENCENO
PW001P01 p JOIN ( WHEN c24.code IS NOT NULL THEN p07.SEQUENCENO
SELECT DISTINCT p03.PIN, END AS SEQUENCENO
dbo.ad_scanorgtree(p03.NUMORGID, 2) companyID FROM (
FROM PW001P03 p03 SELECT DISTINCT p03.PIN,
JOIN PW001C12 c12 dbo.ad_scanorgtree(p03.NUMORGID, 2) companyID,
ON c12.CODE = p03.CODE p.NATIONALITY
AND c12.OPTIONS LIKE '%S%' FROM PW001P03 WHEREp03
p03.DATETO >= CONVERT(VARCHAR, GETDATE(), 112) OR p03.DATETO IS NULL JOIN PW001C12 c12
) p03 ON c12.CODE = p03.CODE
ON p03.PIN = p.PIN JOIN PWORGRTD rtd AND c12.OPTIONS LIKE '%S%'
ON rtd.NUMORGID = p03.companyID LEFT JOIN PW001C23 c23 LEFT JOIN PW001P01 P
ON c23.CODE = rtd.DOCCODE ON AND rtdp.DOCTYPEPIN = 8p03.PIN
LEFT JOIN PW001P08 p08 WHERE p03.DATETO >= CONVERT(VARCHAR, GETDATE(), 112)
ON p08.PIN = p.PIN AND p08.CODE = c23.CODE OR p03.DATETO IS NULL
LEFT JOIN PW001C24 c24 ) p01
ON c24.CODE = rtd.DOCCODE CROSS APPLY (
AND rtd.DOCTYPE = 7 LEFTSELECT JOIN7 PW001P07AS p07DOCTYPE,
ON p07.PIN = p.PIN *
AND p07.CODE = c24.CODE FROM RP_NAT_REQDOC_MEDICAL
UNION ALL UNION ALL
SELECT p01.PIN, CASE SELECT 8 AS DOCTYPE,
WHEN c23.CODE IS NOT NULL THEN 'Travel' *
WHEN c24.code IS NOT NULL THEN 'Medical' FROM RP_NAT_REQDOC_TRAVEL
END AS TYPE, ) reqDoc
CASE LEFT JOIN PW001C23 c23
WHEN p08.SEQUENCENO IS NOT NULL THEN 'Expiring' WHEN p07.SEQUENCENO IS NOT NULL THEN 'Expiring'ON c23.CODE = reqDoc.DOCUMENT
WHEN p07.SEQUENCENO IS NULL THEN 'Missing' AND reqDoc.DOCTYPE = 8
WHEN p08.SEQUENCENO IS NULL THEN 'Missing' LEFT JOIN PW001P08 p08
END AS STATUS, reqDoc.[DOCUMENT] AS Code, ON p08.PIN = p01.PIN
CASE AND p08.CODE WHEN= c23.CODE
IS NOT NULL THEN p08.SEQUENCENO LEFT JOIN WHENPW001C24 c24.code
IS NOT NULL THEN p07.SEQUENCENO END AS SEQUENCENO ON c24.CODE = reqDoc.DOCUMENT
FROM ( SELECT DISTINCT p03.PIN, AND reqDoc.DOCTYPE = 7
dbo.ad_scanorgtree(p03.NUMORGID, 2) companyID, LEFT JOIN PW001P07 p07
p.NATIONALITY ON p07.PIN = p01.PIN
FROM PW001P03 p03 AND p07.CODE = reqDoc.[DOCUMENT]
JOIN PW001C12 c12 LEFT JOIN PWCOUNTRY c
ON c12.CODE = p03.CODE ON c.COUNTRYCODE AND= c12p01.OPTIONSNATIONALITY
LIKE '%S%' LEFT JOIN PW001P01 P RP_NAT_EXCLUDED_MEDICAL excMed
ON pexcMed.PINNATIONALITY = p03c.PINCOUNTRYCODE
WHERE p03.DATETO >= CONVERT(VARCHAR, GETDATE(), 112) AND excMed.[DOCUMENT] = reqDoc.[DOCUMENT]
OR p03.DATETO IS NULL WHERE excMed.NATIONALITY IS NULL
) p01 )t
CROSS APPLY ( ) doc
SELECT 7 AS DOCTYPE, LEFT JOIN PW001P01 p01
ON * p01.PIN = doc.PIN
LEFT FROMJOIN PWORG org
RP_NAT_REQDOC_MEDICAL UNIONON ALL org.NUMORGID = p01.CLIENT
LEFT JOIN SELECTPW001P0P 8p0p
AS DOCTYPE, ON p0p.PIN = p01.PIN
* LEFT JOIN PW001C02 c02
FROM RP_NAT_REQDOC_TRAVEL ON c02.CODE )= reqDocp0p.POSITIONID
LEFT JOIN PW001C23PWCOUNTRY nat
c23 ON nat.COUNTRYCODE = p01.NATIONALITY
ON c23.CODE = reqDoc.DOCUMENT LEFT JOIN (
AND reqDoc.DOCTYPE = 8 SELECT CODE,
LEFT JOIN PW001P08 p08 [TEXT]
ON p08.PIN = p01.PIN FROM PW001C06
AND p08.CODE = c23.CODE WHERE OPTIONS NOT LIKE '%P%' --JMV Filter out passive document codes to avoid duplicates
LEFT JOIN PW001C24 c24 UNION ALL
ON c24.CODE = reqDoc.DOCUMENT SELECT CODE,
AND reqDoc.DOCTYPE = 7 VISATYPE AS [TEXT]
LEFT JOIN PW001P07 p07 FROM PW001C23
ON p07.PIN = p01.PIN WHERE OPTIONS NOT LIKE '%P%'--JMV Filter out passive document codes to avoid duplicates
AND p07.CODE = reqDoc.[DOCUMENT] UNION ALL
LEFT JOIN PWCOUNTRY c SELECT CODE,
ON c.COUNTRYCODE = p01.NATIONALITY [TEXT]
LEFT JOIN RP_NAT_EXCLUDED_MEDICAL excMed FROM PW001C24
ON excMed.NATIONALITY = c.COUNTRYCODE WHERE OPTIONS NOT LIKE '%P%'--JMV Filter out passive document codes to avoid duplicates
AND excMed.[DOCUMENT] = reqDoc.[DOCUMENT] )docName
WHERE excMed.NATIONALITY IS NULL ) docON docName.CODE = doc.CODE
LEFT JOIN PW001P01 p01 LEFT JOIN (
ON p01.PIN = doc.PIN SELECT PIN,
LEFT JOIN PWORG org ON org.NUMORGID = p01.CLIENT SEQUENCENO,
LEFT JOIN PW001P0P p0p DATEFROM,
ON p0p.PIN = p01.PIN LEFT JOIN PW001C02 c02 EXPIRYDATE,
ON c02.CODE = p0p.POSITIONID SCANNEDDOCNO,
LEFT JOIN PWCOUNTRY nat ON nat.COUNTRYCODE = p01.NATIONALITY SCANVALIDITY
LEFT JOIN ( FROM PW001P05
SELECT CODE, UNION ALL
[TEXT] SELECT PIN,
FROM PW001C06 UNION ALL SEQUENCENO,
SELECT CODE, DATEISSUED AS DATEFROM,
VISATYPE AS [TEXT] EXPIRYDATE,
FROM PW001C23 SCANNEDDOCNO,
UNION ALL SELECT CODE, SCANVALIDITY
[TEXT] FROM PW001P07
FROM PW001C24 UNION ALL
)docName SELECT PIN,
ON docName.CODE = doc.CODE LEFT JOIN ( SEQUENCENO,
SELECT SEQUENCENO, DATEFROM,
DATEFROM, DATETO AS EXPIRYDATE,
EXPIRYDATE, SCANNEDDOCNO,
SCANNEDDOCNO, SCANVALIDITY
SCANVALIDITY FROM PW001P05PW001P08
) UNIONdocDetails
ALL ON SELECT SEQUENCENO,docDetails.SEQUENCENO = doc.SEQUENCENO
AND docDetails.PIN = p01.PIN
DATEISSUED AS DATEFROM, JOIN PW001P03 p03plan
EXPIRYDATE, ON p03plan.PIN = p01.PIN
SCANNEDDOCNO, AND p03plan.PLANNED = 'Y'
SCANVALIDITY AND p03plan.CODE IN (SELECT t.CODE
FROM PW001P07 UNIONFROM ALL pw001c12 t
SELECT SEQUENCENO, WHERE t.OPTIONS LIKE '%S%')
DATEFROM, AND NOT EXISTS (
DATETO AS EXPIRYDATE, SELECT 1
SCANNEDDOCNO, FROM PW001P03 p03t
SCANVALIDITY FROM WHERE PW001P08 p03t.PIN = p01.PIN
) docDetails AND p03t.PLANNED = ON'Y'
docDetails.SEQUENCENO = doc.SEQUENCENO JOIN dbo.PW001P03 P03 AND p03t.CODE IN (SELECT t.CODE
ON P01.PIN = P03.PIN AND P03.CODE IN (SELECT c12.CODE FROM PW001C12 t
FROM PW001C12 c12 WHERE c12t.OPTIONS LIKE '%S%')
AND (P03.DATETO IS NULL OR P03.DATETO >= GETDATE())p03t.DATEFROM < p03plan.DateFrom
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 = P03p03plan.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 = P03p03plan.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
|
