CREATE VIEW dbo.PW001SRV86
AS
SELECT PIN,
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,
p03cur.DATEFROM AS 'Embarkation Date',
ISNULL(p03cur.DATETO, p03cur.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( WHEN (
docDetails.SCANVALIDITY = 0 OR docDetails.SCANVALIDITY IS= NULL0
) AND scanneddocno IS NOT NULL THEN NCHAR(10006) +
OR docDetails.SCANVALIDITY IS NULL
' Not-Confirmed' )
--ELSE 'N/A' END AS 'Scan Status' AND scanneddocno IS NOT NULL THEN NCHAR(10006) +
FROM ( SELECT prc.PIN, ' Not-Confirmed'
prc.[TYPE], --ELSE 'N/A'
CASEEND AS 'Scan Status'
WHEN p05.CODE
IS NULL THEN 'Missing' FROM (
WHEN p05.CODE IS NOT NULL THEN 'Expiring'SELECT prc.PIN,
ENDprc.[TYPE],
AS STATUS, CASE
prc.COMPETENCE AS CODE, WHEN p05.SEQUENCENOCODE IS NULL THEN 'Missing'
FROM ( WHEN p05.CODE IS NOT NULL THEN 'Expiring'
SELECT DISTINCT p03.PIN, END AS STATUS,
CASE prc.COMPETENCE AS CODE,
WHEN c12p05.CODESEQUENCENO
IS NOT NULL THEN 'Competence/Certificate' FROM (
END AS TYPE, SELECT DISTINCT p03.PIN,
prc.COMPETENCE CASE
FROM PW001P03 p03 WHEN c12.CODE IS NOT NULL THEN JOIN'Competence/Certificate'
PW001C12 c12 END AS TYPE,
ON c12.CODE = p03.CODE prc.COMPETENCE
AND c12.OPTIONS LIKE '%S%' FROM PW001P03 p03
JOIN PWORGPRC prc JOIN PW001C12 c12
ON prc.NUMORGID = p03.NUMORGID ON WHEREc12.CODE = p03.DATETO >= CONVERT(VARCHAR, GETDATE(), 112)CODE
OR AND p03c12.DATETOOPTIONS ISLIKE NULL'%S%'
) prc JOIN PWORGPRC prc
LEFT JOIN PW001P05 p05 ON p05prc.PINNUMORGID = prcp03.PINNUMORGID
AND prc.COMPETENCEWHERE = p05p03.CODEDATETO >= CONVERT(VARCHAR, GETDATE(), 112)
UNION ALL SELECT p.PIN, OR p03.DATETO IS NULL
CASE ) prc
WHEN c23.CODE IS NOT NULL THEN 'Travel' LEFT JOIN PW001P05 p05
WHEN c24.code IS NOT NULL THEN 'Medical' ON p05.PIN = prc.PIN
END AS TYPE, AND p05.CODE = dbo.ad_ReturnReplacingCode(p05.pin, prc.COMPETENCE, 0)
CASE
WHEN p08.SEQUENCENO IS NOT NULL THENUNION 'Expiring'ALL
SELECT p.PIN,
WHEN p07.SEQUENCENO IS NOT NULL THEN 'Expiring' CASE
WHEN p07.SEQUENCENO IS NULL THEN 'Missing' WHEN c23.CODE IS NOT NULL THEN 'Travel'
WHEN p08.SEQUENCENO IS NULL THEN 'Missing' WHEN c24.code IS NOT NULL THEN END'Medical'
AS STATUS, END rtd.DOCCODE AS CodeTYPE,
CASE rtd.DOCTYPE
WHEN 8 THENWHEN p08.SEQUENCENO IS NOT NULL THEN 'Expiring'
WHEN 7 THEN p07.SEQUENCENO WHEN p07.SEQUENCENO IS NOT NULL THEN 'Expiring'
END AS SEQUENCENO WHEN p07.SEQUENCENO IS NULL THEN 'Missing'
FROM PW001P01 p WHEN p08.SEQUENCENO JOINIS (NULL THEN 'Missing'
END SELECT DISTINCT p03.PIN, AS STATUS,
rtd.DOCCODE AS Code,
dbo.ad_scanorgtree(p03.NUMORGID, 2) companyID CASE rtd.DOCTYPE
FROM PW001P03 p03 WHEN 8 THEN p08.SEQUENCENO
JOIN PW001C12 c12 WHEN 7 THEN p07.SEQUENCENO
END ON c12.CODE = p03.CODE AS SEQUENCENO
FROM PW001P01 p
AND c12.OPTIONS LIKE '%S%' JOIN (
WHERE p03.DATETO >= CONVERT(VARCHAR, GETDATE(), 112) SELECT DISTINCT p03.PIN,
OR p03.DATETO IS NULL dbo.ad_scanorgtree(p03.NUMORGID, 2) companyID
) p03 FROM PW001P03 p03
ON p03.PIN = p.PIN JOIN PWORGRTD rtd JOIN PW001C12 c12
ON rtd.NUMORGID = p03.companyID ON c12.CODE = p03.CODE
LEFT JOIN PW001C23 c23 ON c23.CODE = rtd.DOCCODE AND c12.OPTIONS LIKE '%S%'
AND rtd.DOCTYPE = 8 WHERE p03.DATETO >= CONVERT(VARCHAR, GETDATE(), 112)
LEFT JOIN PW001P08 p08 ON p08.PIN = p.PIN OR p03.DATETO IS NULL
AND p08.CODE = c23.CODE ) p03
LEFT JOIN PW001C24 c24 ON p03.PIN = p.PIN
ON c24.CODE = rtd.DOCCODE JOIN PWORGRTD rtd
AND rtd.DOCTYPE = 7 ON rtd.NUMORGID = p03.companyID
LEFT JOIN PW001P07 p07 LEFT JOIN PW001C23 c23
ON p07.PIN = p.PIN ON c23.CODE = rtd.DOCCODE
AND p07.CODE = c24.CODE AND rtd.DOCTYPE = 8
UNION ALL LEFT JOIN PW001P08 p08
SELECT p01.PIN, CASE ON p08.PIN = p.PIN
WHEN c23.CODE IS NOT NULL THEN 'Travel' AND p08.CODE = c23.CODE
WHEN c24.code IS NOT NULLLEFT THENJOIN 'Medical'PW001C24 c24
END AS TYPE, ON c24.CODE = rtd.DOCCODE
CASE AND rtd.DOCTYPE = 7
WHEN p08.SEQUENCENO IS NOT NULL THEN 'Expiring' LEFT JOIN PW001P07 p07
WHEN p07.SEQUENCENO IS NOT NULL THEN 'Expiring' ON p07.PIN = p.PIN
WHEN p07.SEQUENCENO IS NULL THEN 'Missing' AND p07.CODE = c24.CODE
WHEN p08.SEQUENCENO IS NULL THEN 'Missing'
UNION ENDALL
AS STATUS, SELECT p01.PIN,
reqDoc.[DOCUMENT] AS Code, CASE
CASE WHEN c23.CODE IS NOT NULL THEN p08.SEQUENCENO 'Travel'
WHEN c24.code IS NOT NULL THEN p07.SEQUENCENO 'Medical'
END AS SEQUENCENOTYPE,
FROM ( CASE
SELECT DISTINCT p03.PIN, WHEN p08.SEQUENCENO IS NOT NULL THEN 'Expiring'
dbo.ad_scanorgtree(p03.NUMORGID, 2) companyID, WHEN p07.SEQUENCENO IS NOT NULL THEN 'Expiring'
p.NATIONALITY WHEN p07.SEQUENCENO IS NULL THEN 'Missing'
FROM PW001P03 p03 WHEN p08.SEQUENCENO IS NULL THEN 'Missing'
JOIN PW001C12 c12 END AS STATUS,
ON c12.CODE = p03.CODE
reqDoc.[DOCUMENT] AS Code,
CASE
AND c12.OPTIONS LIKE '%S%' WHEN c23.CODE IS NOT NULL THEN p08.SEQUENCENO
LEFT JOIN PW001P01 P WHEN c24.code IS NOT NULL THEN p07.SEQUENCENO
ON p.PIN = p03.PIN END AS SEQUENCENO
WHERE p03.DATETO >= CONVERT(VARCHAR, GETDATE(), 112) FROM (
SELECT ORDISTINCT p03.DATETOPIN,
IS NULL ) p01 dbo.ad_scanorgtree(p03.NUMORGID, 2) companyID,
CROSS APPLY ( SELECT 7p.NATIONALITY
AS DOCTYPE, * FROM PW001P03 p03
FROM RP_NAT_REQDOC_MEDICAL JOIN PW001C12 c12
UNION ALL SELECT 8 AS DOCTYPE, ON c12.CODE = p03.CODE
* FROM RP_NAT_REQDOC_TRAVEL AND c12.OPTIONS LIKE '%S%'
) reqDoc LEFT JOIN PW001C23 c23 LEFT JOIN PW001P01 P
ON c23.CODE = reqDoc.DOCUMENT ON p.PIN = p03.PIN
AND reqDoc.DOCTYPE = 8 WHERE p03.DATETO >= LEFT JOIN PW001P08 p08CONVERT(VARCHAR, GETDATE(), 112)
ON p08.PIN = p01.PIN OR p03.DATETO IS NULL
) p01
AND p08.CODE = c23.CODE CROSS APPLY (
LEFT JOIN PW001C24 c24 SELECT 7 AS DOCTYPE,
ON c24.CODE = reqDoc.DOCUMENT *
AND reqDoc.DOCTYPE = 7 FROM RP_NAT_REQDOC_MEDICAL
LEFT JOIN PW001P07 p07 UNION ALL
ON p07.PIN = p01.PINSELECT 8 AS DOCTYPE,
AND p07.CODE = reqDoc.[DOCUMENT]
*
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
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 (
LEFT JOIN PWCOUNTRY cSELECT CODE,
ON c.COUNTRYCODE = p01.NATIONALITY [TEXT]
LEFT JOIN RP_NAT_EXCLUDED_MEDICAL excMed
FROM PW001C06
UNION ALL
ON excMed.NATIONALITY = c.COUNTRYCODE SELECT CODE,
AND excMed.[DOCUMENT] = reqDoc.[DOCUMENT] WHERE excMed.NATIONALITY ISVISATYPE NULLAS [TEXT]
) doc FROM PW001C23
LEFT JOIN PW001P01 p01 ON p01.PIN = doc.PIN UNION ALL
LEFT JOIN PWORG org SELECT ONCODE,
org.NUMORGID = p01.CLIENT LEFT JOIN PW001P0P p0p [TEXT]
ON p0p.PIN = p01.PIN LEFT JOIN PW001C02 c02 FROM PW001C24
ON c02.CODE = p0p.POSITIONID )docName
LEFT JOIN PWCOUNTRY nat ON natdocName.COUNTRYCODECODE = p01.NATIONALITYdoc.CODE
LEFT JOIN (
SELECT CODEPIN,
[TEXT] SEQUENCENO,
FROM PW001C06 DATEFROM,
UNION ALL SELECT CODE, EXPIRYDATE,
VISATYPE AS [TEXT] SCANNEDDOCNO,
FROM PW001C23 UNION ALL SCANVALIDITY
SELECT CODE, FROM PW001P05
[TEXT] FROM UNION ALL PW001C24
)docName SELECT PIN,
ON docName.CODE = doc.CODE LEFT JOIN ( SEQUENCENO,
SELECT SEQUENCENO, DATEISSUED AS DATEFROM,
EXPIRYDATE, EXPIRYDATE,
SCANNEDDOCNO, SCANNEDDOCNO,
SCANVALIDITY FROMSCANVALIDITY
PW001P05 UNION ALL FROM PW001P07
SELECT SEQUENCENO, UNION ALL
DATEISSUED AS DATEFROM, SELECT PIN,
EXPIRYDATE, SEQUENCENO,
SCANNEDDOCNO, SCANVALIDITY DATEFROM,
FROM PW001P07 DATETO AS EXPIRYDATE,
UNION ALL SELECT SEQUENCENO, SCANNEDDOCNO,
DATEFROM, SCANVALIDITY
DATETO AS EXPIRYDATE, FROM PW001P08
SCANNEDDOCNO, ) docDetails
SCANVALIDITY ON docDetails.SEQUENCENO = doc.SEQUENCENO
FROM PW001P08 ) docDetails AND docDetails.PIN = p01.PIN
ON docDetails.SEQUENCENO = doc.SEQUENCENO JOIN dbo.PW001P03 P03cur
JOIN dbo.PW001P03 P03cur ON P01.PIN = P03cur.PIN
AND P03cur.CODE IN (SELECT c12.CODE
FROM PW001C12 c12
WHERE c12.OPTIONS LIKE '%S%')
AND (P03cur.DATETO IS NULL OR P03cur.DATETO >= GETDATE())
AND P03cur.DATEFROM <= GETDATE()
AND P03cur.PLANNED <> 'Y''
LEFT JOIN (
SELECT NUMORGID,
dbo.ad_scanorgtree(NUMORGID, 3) AS VesselID,
dbo.ad_scanorgtree(NUMORGID, 4) AS DeptID
FROM pworg
) ou
ON ou.NUMORGID = p03cur.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 = p03cur.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
|
