CREATE VIEW dbo.PW001SRV49 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,
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,
P05.EXPIRYNOTIFICATIONDATE AS NOTIFIED,
P0T.TELENO,
P05.SEQUENCENO,
P01.EMPLOYMENTSTARTDATE,
P01.EMPLOYMENTENDDATE,
C38.GROUPNO PAYROLLSTATUSSORTNO,
PL.DOCUMENT PPICTURE
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
LEFT JOIN PW001P01 P01 ON P05.PIN = P01.PIN
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()
/**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,
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,
P07.EXPIRYNOTIFICATIONDATE AS NOTIFIED,
P0T.TELENO,
P07.SEQUENCENO,
P01.EMPLOYMENTSTARTDATE,
P01.EMPLOYMENTENDDATE,
C38.GROUPNO PAYROLLSTATUSSORTNO,
PL.DOCUMENT PPICTURE
FROM
PW001P07 P07
LEFT JOIN PW001P01 P01 ON P07.PIN = P01.PIN
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()**/
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,
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,
P08.EXPIRYNOTIFICATIONDATE AS NOTIFIED,
P0T.TELENO,
P08.SEQUENCENO,
P01.EMPLOYMENTSTARTDATE,
P01.EMPLOYMENTENDDATE,
C38.GROUPNO PAYROLLSTATUSSORTNO,
PL.DOCUMENT PPICTURE
FROM
PW001P08 P08
LEFT JOIN PW001P01 P01 ON P08.PIN = P01.PIN
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%')
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,
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,
P08.EXPIRYNOTIFICATIONDATE AS NOTIFIED,
P0T.TELENO,
P08.SEQUENCENO,
P01.EMPLOYMENTSTARTDATE,
P01.EMPLOYMENTENDDATE,
C38.GROUPNO PAYROLLSTATUSSORTNO,
PL.DOCUMENT PPICTURE
FROM
PW001P08 P08
LEFT JOIN PW001P01 P01 ON P08.PIN = P01.PIN
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 (C23.OPTIONS LIKE '%T%')) OR (C23.OPTIONS IS NULL))