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)) |