The view is based on the script of the standard Expiry view and shows the crew with expired Competence, Medical and Travel documents, ignores the expired documents if there is a document with the same code but not expired. Considers Competence replacement setup.
Keywords
Documents, expiry
Category
Crew List View
Description
The view is based on the script of the standard Expiry view and shows the crew with expired Competence, Medical and Travel documents, ignores the expired documents if there is a document with the same code but not expired. Considers Competence replacement setup and does not show the expired documents if there is a non-expired replacement. Columns Notified, Photo and Payroll Status SortNo have been removed as not relevant.
View Sample
Main Data Selection
The view shows the crew that have expired Competence, Medical and Travel documents, ignores the expired documents if there is a document with the same code but not expired. Considers Competence replacement setup and does not show the expired documents if there is a non-expired replacement.
SQL statement
-- New expiry
CREATE VIEW dbo.PW001SRV38
AS
SELECT
P01.PIN AS PIN,
P01.NAME AS NAME,
P01.TITLENAME TITLENAME,
P01.FIRSTNAME FIRSTNAME,
P01.MIDDLENAME MIDDLENAME,
P01.LASTNAME LASTNAME,
P01.MAIDENNAME MAIDENNAME,
P01.CALLINGNAME CALLINGNAME,
P01.SUFFIXNAME SUFFIXNAME,
ORG.NAME ORGANIZATION,
C02.NAME RANK,
C02.GROUPNO RANKSORT,
P01.CLIENT NUMORGID,
1 AS DTYPE,
CAST('Competence' AS CHAR(10)) DOC,
P05.CODE AS CODE,
C06.TEXT AS DOCNAME,
P05.DATEFROM AS ISSUED,
P05.EXPIRYDATE AS VALID,
P0T.TELENO,
P05.SEQUENCENO,
P01.EMPLOYMENTSTARTDATE,
P01.EMPLOYMENTENDDATE
FROM
(SELECT
p05.PIN,
p05.EXPIRYDATE,
p05.CODE,
p05.DATEFROM,
p05.EXPIRYNOTIFICATIONDATE,
p05.SEQUENCENO
FROM pw001p05 p05
/*WHERE EXISTS (
SELECT 1
FROM PW001P05 p05r
WHERE p05r.PIN = p05.PIN
AND p05.CODE = dbo.ad_ReturnReplacingCode(p05r.PIN, p05r.CODE, 0)
)*/
) p05
--pw001p05 p05
JOIN PW001P01 P01 ON P05.PIN = P01.PIN
LEFT JOIN PWORG ORG ON ORG.NUMORGID=P01.CLIENT
LEFT JOIN PW001C38 C38 ON P01.PAYROLLSTATUS=C38.CODE
LEFT JOIN PW001P0P P0P ON ((P01.PIN=P0P.PIN) AND (P0P.PNUMBER = 'A'))
LEFT JOIN PW001C02 C02 ON P0P.POSITIONID=C02.CODE
LEFT JOIN PW001C06 C06 ON P05.CODE = C06.CODE
LEFT JOIN PW001P01PICT PL ON P05.PIN = PL.PIN
LEFT JOIN PWUSERLASTVALUE COMPETENCEIGNOREEXPIRY ON (COMPETENCEIGNOREEXPIRY.USERID = '*' AND COMPETENCEIGNOREEXPIRY.MODULEID='EXPIRY CHECK' AND COMPETENCEIGNOREEXPIRY.FIELDID=14 )
LEFT JOIN PW001P0T P0T ON ((P01.PIN = P0T.PIN) AND (P0T.TELETYPE = 6) AND (NOT P0T.TELEPRIORITY IS NULL) AND NOT EXISTS
(SELECT SEQUENCENO
FROM PW001P0T P0T2
WHERE (P0T.PIN = P0T2.PIN) AND (P0T2.TELETYPE = 6) AND ((P0T2.TELEPRIORITY < P0T.TELEPRIORITY) OR
((P0T2.TELEPRIORITY = P0T.TELEPRIORITY) AND (P0T2.SEQUENCENO < P0T.SEQUENCENO)))))
WHERE
(NOT (P05.EXPIRYDATE IS NULL)) AND
(case
when FLOOR(COMPETENCEIGNOREEXPIRY.LASTINTEGER/365)+YEAR(P05.EXPIRYDATE) < 9999
then DATEADD (DAY, COMPETENCEIGNOREEXPIRY.LASTINTEGER, P05.EXPIRYDATE)
else '99991231'
end) > GETDATE()
AND NOT EXISTS (SELECT t.SEQUENCENO FROM PW001P05 t
WHERE t.PIN = P05.PIN and t.SEQUENCENO != p05.SEQUENCENO
and t.CODE = dbo.ad_ReturnReplacingCode(p05.PIN, p05.CODE, 0)
and t.DATEFROM<= Convert(date,Getdate())
and Isnull(t.EXPIRYDATE,dateadd(year,1,Getdate()))>Getdate())
and p05.EXPIRYDATE<=Convert(date,Getdate())
UNION ALL
SELECT
P01.PIN AS PIN,
P01.NAME AS NAME,
P01.TITLENAME TITLENAME,
P01.FIRSTNAME FIRSTNAME,
P01.MIDDLENAME MIDDLENAME,
P01.LASTNAME LASTNAME,
P01.MAIDENNAME MAIDENNAME,
P01.CALLINGNAME CALLINGNAME,
P01.SUFFIXNAME SUFFIXNAME,
ORG.NAME ORGANIZATION,
C02.NAME RANK,
C02.GROUPNO RANKSORT,
P01.CLIENT NUMORGID,
2 AS DTYPE,
CAST('Medical' AS CHAR(10)) DOC,
P07.CODE AS CODE,
C24.TEXT AS DOCNAME,
P07.DATEISSUED AS ISSUED,
P07.EXPIRYDATE AS VALID,
P0T.TELENO,
P07.SEQUENCENO,
P01.EMPLOYMENTSTARTDATE,
P01.EMPLOYMENTENDDATE
FROM
PW001P07 P07
LEFT JOIN PW001P01 P01 ON P07.PIN = P01.PIN
LEFT JOIN PWORG ORG ON ORG.NUMORGID=P01.CLIENT
LEFT JOIN PW001C38 C38 ON P01.PAYROLLSTATUS=C38.CODE
LEFT JOIN PW001P0P P0P ON ((P01.PIN=P0P.PIN) AND (P0P.PNUMBER = 'A'))
LEFT JOIN PW001C02 C02 ON P0P.POSITIONID=C02.CODE
LEFT JOIN PW001C24 C24 ON P07.CODE = C24.CODE
LEFT JOIN PW001P01PICT PL ON P07.PIN = PL.PIN
LEFT JOIN PWUSERLASTVALUE MEDICALIGNOREEXPIRY ON (MEDICALIGNOREEXPIRY.USERID = '*' AND MEDICALIGNOREEXPIRY.MODULEID='EXPIRY CHECK' AND MEDICALIGNOREEXPIRY.FIELDID=24 )
LEFT JOIN PW001P0T P0T ON ((P01.PIN = P0T.PIN) AND (P0T.TELETYPE = 6) AND (NOT P0T.TELEPRIORITY IS NULL) AND NOT EXISTS
(SELECT SEQUENCENO
FROM PW001P0T P0T2
WHERE (P0T.PIN = P0T2.PIN) AND (P0T2.TELETYPE = 6) AND ((P0T2.TELEPRIORITY < P0T.TELEPRIORITY) OR
((P0T2.TELEPRIORITY = P0T.TELEPRIORITY) AND (P0T2.SEQUENCENO < P0T.SEQUENCENO)))))
WHERE
(NOT (P07.EXPIRYDATE IS NULL)) AND
(case
when FLOOR(MEDICALIGNOREEXPIRY.LASTINTEGER/365)+YEAR(P07.EXPIRYDATE) < 9999
then DATEADD (DAY, MEDICALIGNOREEXPIRY.LASTINTEGER, P07.EXPIRYDATE)
else '99991231'
end) > GETDATE()
AND NOT EXISTS (SELECT t.SEQUENCENO FROM PW001P07 t
WHERE t.PIN = P07.PIN and t.SEQUENCENO != P07.SEQUENCENO
and t.CODE = P07.CODE
and t.DATEISSUED<= Convert(date,Getdate())
and Isnull(t.EXPIRYDATE,dateadd(year,1,Getdate()))>Getdate())
and P07.EXPIRYDATE<=Convert(date,Getdate())
UNION ALL
SELECT
P01.PIN AS PIN,
P01.NAME AS NAME,
P01.TITLENAME TITLENAME,
P01.FIRSTNAME FIRSTNAME,
P01.MIDDLENAME MIDDLENAME,
P01.LASTNAME LASTNAME,
P01.MAIDENNAME MAIDENNAME,
P01.CALLINGNAME CALLINGNAME,
P01.SUFFIXNAME SUFFIXNAME,
ORG.NAME ORGANIZATION,
C02.NAME RANK,
C02.GROUPNO RANKSORT,
P01.CLIENT NUMORGID,
3 AS DTYPE,
CAST('Passport' AS CHAR(10)) DOC,
P08.CODE AS CODE,
C23.VISATYPE AS DOCNAME,
P08.DATEFROM AS ISSUED,
P08.DATETO AS VALID,
P0T.TELENO,
P08.SEQUENCENO,
P01.EMPLOYMENTSTARTDATE,
P01.EMPLOYMENTENDDATE
FROM
PW001P08 P08
LEFT JOIN PW001P01 P01 ON P08.PIN = P01.PIN
LEFT JOIN PWORG ORG ON ORG.NUMORGID=P01.CLIENT
LEFT JOIN PW001C38 C38 ON P01.PAYROLLSTATUS=C38.CODE
LEFT JOIN PW001P0P P0P ON ((P01.PIN=P0P.PIN) AND (P0P.PNUMBER = 'A'))
LEFT JOIN PW001C02 C02 ON P0P.POSITIONID=C02.CODE
LEFT JOIN PW001C23 C23 ON P08.CODE = C23.CODE
LEFT JOIN PW001P01PICT PL ON P01.PIN = PL.PIN
LEFT JOIN PWUSERLASTVALUE TRAVELIGNOREEXPIRY ON (TRAVELIGNOREEXPIRY.USERID = '*' AND TRAVELIGNOREEXPIRY.MODULEID='EXPIRY CHECK' AND TRAVELIGNOREEXPIRY.FIELDID=34)
LEFT JOIN PW001P0T P0T ON ((P01.PIN = P0T.PIN) AND (P0T.TELETYPE = 6) AND (NOT P0T.TELEPRIORITY IS NULL) AND NOT EXISTS
(SELECT SEQUENCENO
FROM PW001P0T P0T2
WHERE (P0T.PIN = P0T2.PIN) AND (P0T2.TELETYPE = 6) AND ((P0T2.TELEPRIORITY < P0T.TELEPRIORITY) OR
((P0T2.TELEPRIORITY = P0T.TELEPRIORITY) AND (P0T2.SEQUENCENO < P0T.SEQUENCENO)))))
WHERE
(NOT (P08.DATETO IS NULL)) AND
(case
when FLOOR(TRAVELIGNOREEXPIRY.LASTINTEGER/365)+YEAR(P08.DATETO) < 9999
then DATEADD (DAY, TRAVELIGNOREEXPIRY.LASTINTEGER, P08.DATETO)
else '99991231'
end) > GETDATE()
AND (C23.OPTIONS LIKE '%T%')
AND NOT EXISTS (SELECT t.SEQUENCENO FROM PW001P08 t
WHERE t.PIN = P08.PIN and t.SEQUENCENO != P08.SEQUENCENO
and t.CODE = P08.CODE
and t.DATEFROM<= Convert(date,Getdate())
and Isnull(t.DATETO,dateadd(year,1,Getdate()))>Getdate())
and P08.DATETO<=Convert(date,Getdate())
UNION ALL
SELECT
P01.PIN AS PIN,
P01.NAME AS NAME,
P01.TITLENAME TITLENAME,
P01.FIRSTNAME FIRSTNAME,
P01.MIDDLENAME MIDDLENAME,
P01.LASTNAME LASTNAME,
P01.MAIDENNAME MAIDENNAME,
P01.CALLINGNAME CALLINGNAME,
P01.SUFFIXNAME SUFFIXNAME,
ORG.NAME ORGANIZATION,
C02.NAME RANK,
C02.GROUPNO RANKSORT,
P01.CLIENT NUMORGID,
4 AS DTYPE,
CAST('Travel' AS CHAR(10)) DOC,
P08.CODE AS CODE,
C23.VISATYPE AS DOCNAME,
P08.DATEFROM AS ISSUED,
P08.DATETO AS VALID,
P0T.TELENO,
P08.SEQUENCENO,
P01.EMPLOYMENTSTARTDATE,
P01.EMPLOYMENTENDDATE
FROM
PW001P08 P08
LEFT JOIN PW001P01 P01 ON P08.PIN = P01.PIN
LEFT JOIN PWORG ORG ON ORG.NUMORGID=P01.CLIENT
LEFT JOIN PW001C38 C38 ON P01.PAYROLLSTATUS=C38.CODE
LEFT JOIN PW001P0P P0P ON ((P01.PIN=P0P.PIN) AND (P0P.PNUMBER = 'A'))
LEFT JOIN PW001C02 C02 ON P0P.POSITIONID=C02.CODE
LEFT JOIN PW001C23 C23 ON P08.CODE = C23.CODE
LEFT JOIN PW001P01PICT PL ON P08.PIN = PL.PIN
LEFT JOIN PWUSERLASTVALUE TRAVELIGNOREEXPIRY ON (TRAVELIGNOREEXPIRY.USERID = '*' AND TRAVELIGNOREEXPIRY.MODULEID='EXPIRY CHECK' AND TRAVELIGNOREEXPIRY.FIELDID=44)
LEFT JOIN PW001P0T P0T ON ((P01.PIN = P0T.PIN) AND (P0T.TELETYPE = 6) AND (NOT P0T.TELEPRIORITY IS NULL) AND NOT EXISTS
(SELECT SEQUENCENO
FROM PW001P0T P0T2
WHERE (P0T.PIN = P0T2.PIN) AND (P0T2.TELETYPE = 6) AND ((P0T2.TELEPRIORITY < P0T.TELEPRIORITY) OR
((P0T2.TELEPRIORITY = P0T.TELEPRIORITY) AND (P0T2.SEQUENCENO < P0T.SEQUENCENO)))))
WHERE
(NOT (P08.DATETO IS NULL)) AND
(case
when FLOOR(TRAVELIGNOREEXPIRY.LASTINTEGER/365)+YEAR(P08.DATETO) < 9999
then DATEADD (DAY, TRAVELIGNOREEXPIRY.LASTINTEGER, P08.DATETO)
else '99991231'
end) > GETDATE()
AND NOT EXISTS (SELECT t.SEQUENCENO FROM PW001P08 t
WHERE t.PIN = P08.PIN and t.SEQUENCENO != P08.SEQUENCENO
and t.CODE = P08.CODE
and t.DATEFROM<= Convert(date,Getdate())
and Isnull(t.DATETO,dateadd(year,1,Getdate()))>Getdate())
and P08.DATETO<=Convert(date,Getdate())
AND ((NOT (C23.OPTIONS LIKE '%T%')) OR (C23.OPTIONS IS NULL))
Columns Specification
Column
Description/ Location in APM
Column
Description/ Location in APM
PIN
PIN
NAME
Name
TITLENAME
Title Name
FIRSTNAME
First Name
MIDDLENAME
Middle Name
LASTNAME
Last Name
MAIDENNAME
Maiden Name
CALLINGNAME
Calling Name
SUFFIXNAME
Suffix Name
ORGANIZATION
Name of the organization the crew is linked to in Personal Details
RANK
Current Rank
DOC
Expired Document type: Competence, Travel, Medical or Passport
