The view shows if there are any documents with not verified scans: where the “Validity of Scan” in set to “N”.
Keywords
Documents, Scan
Description
The view shows if there are any documents with not valid scans: where the “Validity of Scan” in set to “N” for the employed crew/ staff.
View Sample
Mail data selection
All crew members employed under the current organization and units below that have at least one document (Travel, Medical, Competence) without invalid scan.
SQL Statement
VIEWID should be substituted with the id of the crew list view that you are creating in your system e.g. PW001SRV20 etc.
-- 9/6/2023 4:05:29 PM
CREATE VIEW dbo.PW001SRV54
AS
SELECT MISS_DOC.PIN AS PIN,
MISS_DOC.ALTERNATIVEPIN AS 'ALTERNATIVE PIN',
MISS_DOC.Full_Name AS 'Full Name',
MISS_DOC.First_Name AS 'First Name',
MISS_DOC.Last_Name AS 'Last Name',
MISS_DOC.Middle_Name 'Middle Name',
MISS_DOC.Organization AS 'Organization',
MISS_DOC.Rank AS 'Rank',
MISS_DOC.DOC_TYPE AS 'Document type',
MISS_DOC.Document_Code AS 'Document Code',
MISS_DOC.Document_Name AS 'Document Name',
MISS_DOC.Date_Issued AS 'Date Issued',
MISS_DOC.Expiry_Date AS 'Expiry Date',
MISS_DOC.COMMENTS,
MISS_DOC.NUMORGID AS NUMORGID,
MISS_DOC.EMPLOYMENTSTARTDATE,
MISS_DOC.EMPLOYMENTENDDATE,
MISS_DOC.[Department/Cost Place Code],
MISS_DOC.[Department/Cost Place]
FROM (
--Competence Documents
SELECT P01.PIN AS PIN,
P01.ALTERNATIVEPIN,
P01.NAME AS Full_Name,
P01.FIRSTNAME AS First_Name,
P01.LASTNAME AS Last_Name,
P01.MIDDLENAME Middle_Name,
ORG.NAME AS Organization,
C02.[NAME] AS RANK,
'Competence' AS DOC_TYPE,
C06.CODE AS Document_Code,
C06.[TEXT] AS Document_Name,
P05.DATEFROM AS Date_Issued,
P05.EXPIRYDATE AS EXPIRY_DATE,
P05.COMMENTS,
P05.scanneddocno AS SCAN,
P05.SCANVALIDITY AS SCAN_Validity,
P01.CLIENT AS NUMORGID,
P01.EMPLOYMENTSTARTDATE,
P01.EMPLOYMENTENDDATE,
doc.[DOCUMENT],
p01.COSTPLACE AS 'Department/Cost Place Code',
c43.[NAME] AS 'Department/Cost Place'
FROM PW001P01 P01
JOIN PW001P05 P05
ON P05.PIN = P01.PIN
LEFT JOIN PWORG ORG
ON ORG.NUMORGID = P01.CLIENT
LEFT JOIN PW001C06 C06
ON C06.CODE = P05.CODE
LEFT JOIN PW001C02 C02
ON C02.CODE = P01.[RANK]
LEFT JOIN PW001OLEDOCS doc
ON doc.DOCNO = p05.scanneddocno
LEFT JOIN PW001C43 c43
ON c43.CODE = p01.COSTPLACE
UNION ALL
--Medical Documents 08/08/2022
SELECT P01.PIN AS PIN,
P01.ALTERNATIVEPIN,
P01.NAME AS Full_Name,
P01.FIRSTNAME AS First_Name,
P01.LASTNAME AS Last_Name,
P01.MIDDLENAME Middle_Name,
ORG.NAME AS Organization,
C02.[NAME] AS RANK,
'Medical' AS DOC_TYPE,
C24.CODE AS Document_Code,
C24.[TEXT] AS Document_Name,
P07.DATEISSUED AS Date_Issued,
P07.EXPIRYDATE AS EXPIRY_DATE,
P07.COMMENTS,
P07.scanneddocno AS SCAN,
P07.SCANVALIDITY AS SCAN_Validity,
P01.CLIENT AS NUMORGID,
P01.EMPLOYMENTSTARTDATE,
P01.EMPLOYMENTENDDATE,
doc.[DOCUMENT],
p01.COSTPLACE AS 'Department/Cost Place Code',
c43.[NAME] AS 'Department/Cost Place'
FROM PW001P07 P07
LEFT JOIN PW001P01 P01
ON P01.PIN = P07.PIN
LEFT JOIN PWORG ORG
ON ORG.NUMORGID = P01.CLIENT
LEFT JOIN PW001C24 C24
ON C24.CODE = P07.CODE
LEFT JOIN PW001C02 C02
ON C02.CODE = P01.RANK
LEFT JOIN PW001OLEDOCS doc
ON doc.DOCNO = p07.scanneddocno
LEFT JOIN PW001C43 c43
ON c43.CODE = p01.COSTPLACE
UNION ALL
--Travel Documents
SELECT P01.PIN AS PIN,
P01.ALTERNATIVEPIN,
P01.NAME AS Full_Name,
P01.FIRSTNAME AS First_Name,
P01.LASTNAME AS Last_Name,
P01.MIDDLENAME Middle_Name,
ORG.NAME AS Organization,
C02.[NAME] AS RANK,
'Travel' AS DOC_TYPE,
C23.CODE AS Document_Code,
C23.VISATYPE AS Document_Name,
P08.DATEFROM AS Date_Issued,
P08.DATETO AS EXPIRY_DATE,
P08.COMMENTS,
P08.scanneddocno AS SCAN,
P08.SCANVALIDITY AS SCAN_Validity,
P01.CLIENT AS NUMORGID,
P01.EMPLOYMENTSTARTDATE,
P01.EMPLOYMENTENDDATE,
doc.[DOCUMENT],
p01.COSTPLACE AS 'Department/Cost Place Code',
c43.[NAME] AS 'Department/Cost Place'
FROM PW001P08 P08
LEFT JOIN PW001P01 P01
ON P01.PIN = P08.PIN
LEFT JOIN PWORG ORG
ON ORG.NUMORGID = P01.CLIENT
LEFT JOIN PW001C23 C23
ON C23.CODE = P08.CODE
LEFT JOIN PW001C02 C02
ON C02.CODE = P01.RANK
LEFT JOIN PW001OLEDOCS doc
ON doc.DOCNO = p08.scanneddocno
LEFT JOIN PW001C43 c43
ON c43.CODE = p01.COSTPLACE
) AS MISS_DOC
WHERE (MISS_DOC.SCAN_Validity = 0 AND SCAN IS NOT NULL)
OR (MISS_DOC.SCAN_Validity = 1 AND DOCUMENT IS NULL)
Field Specification
Column
Description/ Location in APM
Column
Description/ Location in APM
PIN
PIN
Full Name
Full name of the person in Perosonal Details.
First Name
First name of the person in Perosonal Details.
Last Name
Last name of the person in Perosonal Details.
Middle Name
Middle name of the person in Perosonal Details.
Organizaton
Current employment organization of the person in Perosonal Details.
Rank
Current Rank of the person in Perosonal Details.
Document Type
The type of document without scan linked to the selected crew.
Document Code
The code of the document record linked to the selected crew.
Document Name
The name of the document record linked to the selected crew.
Date Issued
The date from of the document record linked to the selected crew.
Expiry Date
The expert date of the document record linked to the selected crew.
Comments
The comments added to the document record linked to the selected crew.
