Summary	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

Please remember to add labels to the article. Only the labels from the lit are allowed: /wiki/spaces/PnP/pages/4523197124

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

Click here to expand...

-- 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
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
CODE	Expired Document Code
DOCUMENT	Expired Document Name
ISSUE DATE	Issue Date of the expired document
EXPIRY DATE	Expiry Date of the expired document
TELENO	Email
EMPLOYMENT START DATE	Employment Start Date
EMPLOYMENT END DATE	Employment End Date