Summary	Shows crew with current or next planned activity with missing or expired required competencies. Based on the requirements profile.
Keywords	Competence, requirements profile

Description

Shows crew with current or next planned activity with missing or expired required competencies.

The view is based on the requirements profile.

View Sample

Main data selection

Selection of the view is based on crews having current or next planned activity with missing or expired required competencies.

SQL Statement

SELECT p01.PIN,
       p01.NAME AS NAME,
       --p03.CODE AS ACTIVITY,
       c12.[TEXT]  as ACTIVITY,
       p03.DATEFROM AS 'START DATE', 
       p03.TODATEESTIMATED AS 'ESTIMATED END',
      ORG.NAME ORGANIZATION,
       dep.name as DEPARTMENT,
       orgves.NAME AS VESSEL,
       prc.COMPETENCE AS 'DOC CODE',
       c06.TEXT AS 'DOC NAME',
       p05.DATEFROM AS 'ISSUED DATE',
       p05.EXPIRYDATE AS 'EXPIRY DATE',
       P01.CLIENT NUMORGID, 
       P01.EMPLOYMENTSTARTDATE, 
       P01.EMPLOYMENTENDDATE,
       OrgVes.numorgid as Vessel_Numorgid
FROM  PW001P01 p01
LEFT JOIN PWORG ORG ON P01.CLIENT=ORG.NUMORGID
      JOIN PW001P03 p03
           ON p03.PIN = p01.PIN 
              AND (
                     (p03.PLANNED <> 'Y'
                      AND p03.DATEFROM <= GETDATE()
                      AND (p03.DATETO >= GETDATE() OR p03.DATETO IS NULL)
                      AND NOT EXISTS (SELECT 1
                                      FROM PW001P03 t
                                      WHERE t.PIN = p03.pin
                                            AND t.PLANNED <> 'Y'
                                            AND t.DATEFROM <= GETDATE()
                                            AND (t.DATETO >= GETDATE() OR t.DATETO IS NULL)
                                            AND t.DATEFROM < p03.DateFrom)
                     ) 
                  OR 
                     (p03.PLANNED = 'Y'
                      AND NOT EXISTS (SELECT 1
                                      FROM PW001P03 t
                                      WHERE t.PIN = p03.pin
                                            AND (
                                                    (t.PLANNED = 'Y'
                                                     AND t.DATEFROM < p03.DateFrom
                                                    )
                                                 OR (t.PLANNED <> 'Y'
                                                     AND t.DATEFROM <= GETDATE()
                                                     AND (t.DATETO >= GETDATE() OR t.DATETO IS NULL)
                                                    )
                                                )
                                     )
                     )    
                  )                      
LEFT JOIN PW001C12 c12
            ON  c12.CODE = p03.CODE    
     JOIN PWORG orgpos
          ON orgpos.NUMORGID = p03.NUMORGID
          AND orgpos.ORGTYPE = 5
     JOIN PWORGPRC prc
          ON prc.NUMORGID = orgpos.NUMORGID
     LEFT JOIN PW001C06 c06
          ON c06.CODE = prc.COMPETENCE
     LEFT JOIN PW001P05 p05
          ON p05.pin = p01.PIN
          AND p05.CODE = dbo.ad_ReturnReplacingCode(p05.pin, prc.COMPETENCE, 0)
     LEFT JOIN PWORG orgves
          ON orgves.NUMORGID = dbo.ad_scanorgtree(p03.NUMORGID, 3)
LEFT JOIN dbo.PWORG DEP
ON DEP.NUMORGID = dbo.ad_scanorgtree(P03.NUMORGID, 4)
WHERE (p05.CODE IS NULL
        OR p05.EXPIRYDATE < ISNULL(p03.TODATEESTIMATED, p03.DATEFROM))

Fields/Columns Specification

Column	Description/ Location in APM

Column	Description/ Location in APM
PIN	Personal Details > Personal > Personal Identification Number
NAME	Personal Details > Personal > Full Name
ACTIVITY	Datagroups > Activity > Current/Planned Activity > Activity Name
START DATE	Datagroups > Activity > Current/Planned Activity > Start Date
ESTIMATED END	Datagroups > Activity > Current Activity > Estimated End Date
ORGANIZATION	Personal Details > Employment > Organization
DEPARTMENT	Datagroups > Activity > Current/Planned Activity > Department
VESSEL	Datagroups > Activity > Current/Planned Activity > Vessel Name
DOC CODE	Datagroups > Competence > Expired competence > Doc Code
DOC NAME	Datagroups > Competence > Expired competence > Doc Name
ISSUED DATE	Datagroups > Competence > Expired competence > Issued Date
EXPIRY DATE	Datagroups > Competence > Expired competence > Expiry Date
NUMORGID	Personal Details > Employment > Organizations NUMORGID
EMPLOYMENT START DATE	Personal Details > Employment > Employment Start Date
EMPLOYMENT END DATE	Personal Details > Employment > Employment End Date

Start Page

N/A

Reports, Views and Similar

Missing required competences (onboard and planned)