CREATE VIEW dbo.PW001SRV56PW001SRV86
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],
m.EMPLOYMENTSTARTDATE,VESSEL_NUMORGID,
m.EMPLOYMENTENDDATE[Embarkation Date],
m.NUMORGID
FROM [Disembarkation Date],
( [Activity Vessel],
SELECT DISTINCT P01.PIN[Activity Department],
[Activity Position],
[Scan P01.ALTERNATIVEPIN,Status],
NUMORGID,
EMPLOYMENTSTARTDATE,
p01.NAME asEMPLOYMENTENDDATE
'FullFROM Name', (
ORG.Name as 'Organization', SELECT p01.PIN,
c02.NAME AS 'Current Rank' P01.ALTERNATIVEPIN,
natp01.NATIONALITYCLIENT as 'Nationality', NUMORGID,
doc.Type AS 'Document type', P01.EMPLOYMENTSTARTDATE,
doc.status as 'Document Status' P01.EMPLOYMENTENDDATE,
doc.Code 'Document Code',p01.[NAME] AS 'Full Name',
doc.DocName AS 'Document Name', org.[NAME] doc.DATEFROM AS 'IssuedOrganization dateName',
docc02.EXPIRYDATENAME AS 'Expiry date', AS 'Current Rank',
P01.CLIENT NUMORGID, nat.NATIONALITY P01.EMPLOYMENTSTARTDATEAS 'Nationality',
P01doc.EMPLOYMENTENDDATE[TYPE] FROM AS 'Document PW001P01Type',
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',
CASEdocDetails.EXPIRYDATE 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 WHEN p05.CODE IS NOT NULL THEN 'Expiring''Disembarkation Date',
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
p05.DATEFROM,WHEN (
p05docDetails.EXPIRYDATEscanneddocno IS NULL
--link to sailing activityOR oledoc.[DOCUMENT] IS NULL
FROM ) THEN NCHAR(9940) + ' Missing'
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 JOINAS PW001C12'Scan c12Status'
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 prc.NUMORGIDEND = p03.NUMORGIDAS STATUS,
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 c06c12.CODE =IS prc.COMPETENCENOT NULL THEN 'Competence/Certificate'
LEFT JOIN PW001P05 p05 END AS TYPE,
ON p05.PIN = prc.PIN prc.COMPETENCE
FROM AND prc.COMPETENCE =PW001P03 p05.CODEp03
--required medical/travel documents on org level JOIN PW001C12 c12
UNION ALL ON SELECT p.PIN,
c12.CODE = p03.CODE
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
WHEN p07.CODE IS NULLLEFT THENJOIN 'Missing'PW001P05 p05
ON p05.PIN = WHEN p08prc.CODEPIN
IS NOT NULL THEN 'Expiring' AND prc.COMPETENCE = p05.CODE
WHEN p07.CODE IS NOT NULL THEN 'Expiring' UNION ALL
SELECT ENDp.PIN,
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 7 THEN p07.EXPIRYDATE WHEN 8 THEN p08.SEQUENCENO
WHEN 7 THEN p07.SEQUENCENO
END AS ExpiryDate END AS SEQUENCENO
FROM PW001P01 p 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 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
UNION ALL SELECT p01.PIN,
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 p07p08.CODESEQUENCENO IS NOT NULL THEN 'MissingExpiring'
WHEN p07.CODESEQUENCENO IS NOT NULL THEN 'Expiring'
ENDWHEN p07.SEQUENCENO ASIS STATUS,NULL THEN 'Missing'
reqDoc.[DOCUMENT] AS Code,WHEN p08.SEQUENCENO IS NULL THEN 'Missing'
CASEEND reqDoc.DOCTYPE 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.DATEFROMFROM (
SELECT WHEN 7 THEN p07.DATEISSUED
DISTINCT p03.PIN,
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 DISTINCT p03.PIN,
AND c12.OPTIONS LIKE '%S%'
LEFT JOIN dbo.ad_scanorgtree(p03.NUMORGID, 2) companyID,PW001P01 P
ON p.NATIONALITYPIN = p03.PIN
WHERE p03.DATETO FROM PW001P03 p03>= CONVERT(VARCHAR, GETDATE(), 112)
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
ONSELECT 8 p.PIN = p03.PINAS DOCTYPE,
*
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.PIN
reqDoc LEFT JOIN PW001C23 c23 AND p07.CODE = reqDoc.[DOCUMENT]
LEFT JOIN PWCOUNTRY c
ON c23.CODE = reqDoc.DOCUMENT ON AND reqDoc.DOCTYPEc.COUNTRYCODE = 8
p01.NATIONALITY
LEFT JOIN PW001P08 p08
RP_NAT_EXCLUDED_MEDICAL excMed
ON p08excMed.PINNATIONALITY = p01c.PINCOUNTRYCODE
AND p08.CODEAND excMed.[DOCUMENT] = c23reqDoc.CODE[DOCUMENT]
WHERE excMed.NATIONALITY IS NULL
LEFT JOIN PW001C24 c24 ) doc
LEFT JOIN PW001P01 p01
ON c24.CODE = reqDoc.DOCUMENT ON p01.PIN = doc.PIN
LEFT JOIN PWORG org
AND reqDoc.DOCTYPE = 7 ON org.NUMORGID = p01.CLIENT
LEFT JOIN PW001P07 p07 LEFT JOIN PW001P0P p0p
ON p07p0p.PIN = p01.PIN
LEFT JOIN PW001C02 c02
AND p07.CODE = reqDoc.[DOCUMENT] ON c02.CODE = p0p.POSITIONID
LEFT JOIN PWCOUNTRY nat
LEFT JOIN PWCOUNTRY c ON nat.COUNTRYCODE = p01.NATIONALITY
LEFT JOIN (
ON c.COUNTRYCODE = p01.NATIONALITY SELECT CODE,
LEFT JOIN RP_NAT_EXCLUDED_MEDICAL excMed [TEXT]
ON excMed.NATIONALITY = c.COUNTRYCODE FROM PW001C06
UNION ALL
AND excMed.[DOCUMENT] = reqDoc.[DOCUMENT] SELECT CODE,
WHERE excMed.NATIONALITY IS NULL ) doc VISATYPE AS [TEXT]
ON doc.PIN = p01.PIN FROM PW001C23
LEFT JOIN PW001P0P p0p UNION ALL
ON p0p.PIN = p01.PIN SELECT CODE,
LEFT JOIN PW001C02 c02 ON c02.CODE = p0p.POSITIONID [TEXT]
LEFT JOIN PWCOUNTRY nat FROM PW001C24
ON nat.COUNTRYCODE = p01.NATIONALITY LEFT JOIN)docName
PWORG ORG ON ORG.NUMORGID=P01.CLIENT ON docName.CODE = doc.CODE
--ORDER BY LEFT JOIN (
-- 1 SELECT SEQUENCENO,
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
)m
ON oledoc.DOCNO = docDetails.scanneddocno
) final
WHERE final.[Expiry Date] <= DATEADD(YEAR, 1, CAST(GETDATE() AS DATE))
OR Final.[Expiry Date] IS NULL |