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 (
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 TYPE,
JOIN 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 c23.CODE IS NOT NULL THEN 'Travel' ON prc.NUMORGID = p03.NUMORGID
WHEN c24.code IS NOT NULL THEN 'Medical' WHERE p03.DATETO >= CONVERT(VARCHAR, ENDGETDATE(), 112)
AS TYPE, CASE OR p03.DATETO IS NULL
WHEN p08.SEQUENCENO IS NOT NULL THEN 'Expiring' ) prc
WHEN p07.SEQUENCENO IS NOT NULL THEN 'Expiring' LEFT JOIN PW001P05 p05
WHEN p07.SEQUENCENO IS NULL THEN 'Missing' WHEN p08.SEQUENCENO IS NULL THENON 'Missing' p05.PIN = prc.PIN
END AS STATUS, AND prc.COMPETENCE = p05.CODE
rtd.DOCCODE AS Code,
CASE rtd.DOCTYPE UNION ALL
WHEN 8 THEN p08.SEQUENCENO SELECT p.PIN,
WHEN 7 THEN p07.SEQUENCENO END CASE
AS SEQUENCENO FROM PW001P01 p WHEN c23.CODE IS NOT NULL THEN 'Travel'
JOIN ( 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%' WHEN p07.SEQUENCENO IS NULL THEN 'Missing'
WHERE p03.DATETO >= CONVERT(VARCHAR, GETDATE(), 112) WHEN p08.SEQUENCENO IS NULL THEN 'Missing'
OR p03.DATETO IS NULL END AS STATUS,
) p03 ON p03.PIN = prtd.PINDOCCODE AS Code,
JOIN PWORGRTD rtd CASE rtd.DOCTYPE
ON rtd.NUMORGID = p03.companyID WHEN 8 THEN LEFTp08.SEQUENCENO
JOIN PW001C23 c23 ON c23.CODE = rtd.DOCCODE WHEN 7 THEN p07.SEQUENCENO
AND rtd.DOCTYPE = 8 END AS SEQUENCENO
LEFT JOIN PW001P08 p08 FROM PW001P01 p
ON p08.PIN = p.PIN ANDJOIN p08.CODE(
= c23.CODE LEFT JOIN PW001C24 c24 SELECT DISTINCT p03.PIN,
ON c24.CODE = rtd.DOCCODE AND rtd.DOCTYPE = 7 dbo.ad_scanorgtree(p03.NUMORGID, 2) companyID
LEFT JOIN PW001P07 p07 FROM PW001P03 p03
ON p07.PIN = p.PIN AND p07.CODE = c24.CODE JOIN PW001C12 c12
UNION ALL SELECT p01.PIN, ON c12.CODE = p03.CODE
CASE WHEN c23.CODE IS NOT NULL THEN 'Travel' AND c12.OPTIONS LIKE '%S%'
WHEN c24.code IS NOT NULL THEN 'Medical' WHERE p03.DATETO END AS TYPE,
>= CONVERT(VARCHAR, GETDATE(), 112)
CASE OR WHEN p08.SEQUENCENOp03.DATETO IS NOT NULL
THEN 'Expiring' 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' JOIN PWORGRTD rtd
END AS STATUS, reqDoc.[DOCUMENT] AS Code, ON rtd.NUMORGID = p03.companyID
CASE LEFT JOIN PW001C23 WHEN c23.CODE
IS NOT NULL THEN p08.SEQUENCENO 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 AND p08.CODE = c23.CODE
PW001P03 p03 LEFT JOIN PW001C12 c12 PW001C24 c24
ON c12c24.CODE = p03.CODErtd.DOCCODE
AND c12.OPTIONS LIKE '%S%'rtd.DOCTYPE = 7
LEFT JOIN PW001P01 PPW001P07 p07
ON pp07.PIN = p03p.PIN
WHERE p03.DATETO >= CONVERT(VARCHAR, GETDATE(), 112) AND p07.CODE = c24.CODE
OR p03.DATETO IS NULL
) p01 UNION ALL
CROSS APPLY ( SELECT p01.PIN,
SELECT 7 AS DOCTYPE, * CASE
FROM RP_NAT_REQDOC_MEDICAL WHEN c23.CODE IS NOT NULL UNIONTHEN ALL'Travel'
SELECT 8 AS DOCTYPE, WHEN c24.code IS NOT NULL THEN 'Medical'
* FROM RP_NAT_REQDOC_TRAVEL END AS TYPE,
) reqDoc LEFT JOIN PW001C23 c23 CASE
ON c23.CODE = reqDoc.DOCUMENT WHEN p08.SEQUENCENO IS NOT NULL THEN 'Expiring'
AND reqDoc.DOCTYPE = 8 LEFT JOIN PW001P08 p08WHEN p07.SEQUENCENO IS NOT NULL THEN 'Expiring'
ON p08.PIN = p01.PIN WHEN p07.SEQUENCENO IS NULL THEN 'Missing'
AND p08.CODE = c23.CODE LEFT JOIN PW001C24 c24WHEN p08.SEQUENCENO IS NULL THEN 'Missing'
ON c24.CODE = reqDoc.DOCUMENT END AS STATUS,
AND reqDoc.DOCTYPE = 7 reqDoc.[DOCUMENT] AS Code,
LEFT JOIN PW001P07 p07 CASE ON
p07.PIN = p01.PIN AND p07.CODE = reqDoc.[DOCUMENT] WHEN c23.CODE IS NOT NULL THEN p08.SEQUENCENO
LEFT JOIN PWCOUNTRY c WHEN c24.code IS NOT ONNULL THEN cp07.COUNTRYCODESEQUENCENO
= p01.NATIONALITY LEFT JOIN RP_NAT_EXCLUDED_MEDICAL excMed
END AS SEQUENCENO
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
)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 excMedc02.NATIONALITYCODE = cp0p.COUNTRYCODEPOSITIONID
LEFT JOIN PWCOUNTRY nat
AND excMed.[DOCUMENT] = reqDoc.[DOCUMENT] ON nat.COUNTRYCODE = p01.NATIONALITY
WHERE excMed.NATIONALITY IS NULL LEFT JOIN (
) doc LEFT JOIN PW001P01 p01 SELECT CODE,
ON p01.PIN = doc.PIN LEFT[TEXT]
JOIN PWORG org ON org.NUMORGID = p01.CLIENT FROM PW001C06
LEFT JOIN PW001P0P p0p WHERE OPTIONS NOT LIKE '%P%' --JMV Filter out passive ONdocument codes p0p.PINto =avoid p01.PINduplicates
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
|