CREATE VIEW [dbo].[PW001SRV58] 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( WHEN (
docDetails.SCANVALIDITY = 0 OR docDetails.SCANVALIDITY IS NULL= 0
) ANDOR scanneddocnodocDetails.SCANVALIDITY IS NOT NULL
THEN NCHAR(10006) + ' 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'
END CASE AS 'Scan Status'
FROM WHEN p05.CODE IS NULL THEN 'Missing'(SELECT DISTINCT * FROM (
SELECT prc.PIN,
WHEN p05.CODE IS NOT NULL THEN 'Expiring' prc.[TYPE],
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,
prc.COMPETENCE AS CODE,
CASE p05.SEQUENCENO
WHEN c12.CODE IS NOT NULL THEN 'Competence/Certificate' FROM (
ENDSELECT ASDISTINCT TYPEp03.PIN,
prc.COMPETENCE CASE
FROM PW001P03 p03 WHEN c12.CODE IS NOT NULL THEN 'Competence/Certificate'
JOIN 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 c12.CODE = p03.CODE
WHERE p03.DATETO >= CONVERT(VARCHAR, GETDATE(), 112) AND c12.OPTIONS LIKE '%S%'
OR p03.DATETO IS NULL )JOIN PWORGPRC prc
LEFT JOIN PW001P05 p05 ON prc.NUMORGID = p03.NUMORGID
ON p05.PIN = prc.PIN WHERE p03.DATETO >= CONVERT(VARCHAR, GETDATE(), 112)
AND prc.COMPETENCE = p05.CODE ) doc OR p03.DATETO IS NULL
LEFT JOIN PW001P01 p01 ON p01.PIN = doc.PIN ) prc
LEFT JOIN PWORG org ONLEFT org.NUMORGIDJOIN =PW001P05 p01.CLIENTp05
LEFT JOIN PW001P0P p0p ON p0p p05.PIN = p01prc.PIN
LEFT JOIN PW001C02 c02 AND p05.CODE = dbo.ad_ReturnReplacingCode(p05.pin, prc.COMPETENCE, 0)
)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%'
UNION ALL
SELECT CODE,
VISATYPE AS [TEXT]
FROM PW001C23
WHERE OPTIONS NOT LIKE '%P%'
UNION ALL
SELECT CODE,
[TEXT]
FROM PW001C24
WHERE OPTIONS NOT LIKE '%P%'
)docName
ON docName.CODE = doc.CODE
LEFT JOIN (
SELECT PIN,
ON c02.CODE = p0p.POSITIONID SEQUENCENO,
LEFT JOIN PWCOUNTRY nat ON nat.COUNTRYCODE = p01.NATIONALITY DATEFROM,
LEFT JOIN ( SELECT CODE, EXPIRYDATE,
[TEXT] SCANNEDDOCNO,
FROM PW001C06 UNION ALL SCANVALIDITY
SELECT CODE, FROM PW001P05
VISATYPE AS [TEXT] UNION ALL
FROM PW001C23 UNION ALL SELECT PIN,
SELECT CODE, SEQUENCENO,
[TEXT] FROM PW001C24 DATEISSUED AS DATEFROM,
)docName ON docName.CODE = doc.CODE EXPIRYDATE,
LEFT JOIN ( SELECT SEQUENCENOSCANNEDDOCNO,
DATEFROM, SCANVALIDITY
EXPIRYDATE, FROM PW001P07
SCANNEDDOCNO, UNION ALL
SCANVALIDITY SELECT PIN,
FROM PW001P05 UNION ALL SEQUENCENO,
SELECT SEQUENCENO, DATEFROM,
DATEISSUED AS DATEFROM, DATETO AS EXPIRYDATE,
SCANNEDDOCNO, SCANNEDDOCNO,
SCANVALIDITY SCANVALIDITY
FROM PW001P07 FROM UNION ALL PW001P08
SELECT SEQUENCENO, ) docDetails
DATEFROM, ON docDetails.SEQUENCENO = doc.SEQUENCENO
DATETO AS EXPIRYDATE, AND docDetails.PIN = p01.PIN
JOIN PW001P03 p03plan
SCANNEDDOCNO, ON p03plan.PIN = p01.PIN
SCANVALIDITY FROM PW001P08AND p03plan.PLANNED = 'Y'
) docDetails AND p03plan.CODE IN (SELECT t.CODE
ON docDetails.SEQUENCENO = doc.SEQUENCENO JOIN PW001P03 p03plan ON p03plan.PIN = p01.PIN FROM pw001c12 t
AND p03plan.PLANNED = 'Y' AND p03plan.CODE IN (SELECT t.CODE WHERE t.OPTIONS LIKE '%S%')
FROM pw001c12 t AND NOT EXISTS (
WHERE t.OPTIONS LIKE '%S%') SELECT AND1
NOT EXISTS ( SELECT 1 FROM PW001P03 p03t
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,
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)
|
