Summary	Displays all the emergency contact details from the Family data group.
Keywords	Emergency Contact
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 displays Emergency contact details from the Family data group. Contact information will be listed in the view when “In Case of Emergency“ is set-up.

If there are several contacts. then shown several lines per person.
If no emergency contact is added the status will be No Emergency Contact Added otherwise Emergency Contact added.

View Sample

Main Data Selection

All Crew members.

SQL statement

Click here to expand...

CREATE VIEW dbo.PW001SRVXX  AS
SELECT P01.PIN  
                   PIN,
       CASE 
            WHEN p06.[EMERGENCY] = 1 THEN 'Emergency Contact added'
            ELSE 'No Emergency Contact added'
       END                      AS 'Emergency Contact Status',
       P01.NAME                    NAME,
       P01.FIRSTNAME               FIRSTNAME,
       P01.MIDDLENAME              MIDDLENAME,
       P01.LASTNAME                LASTNAME,
       C02.NAME                 AS 'CURRENT RANK',
       P01.CLIENT                  NUMORGID,
       P06.PRIORITY 'CONTACT PRIORITY',
       P06.NAME 'NOK NAME',
       C07.TEXT 'NOK RELATIONSHIP',
       P06.PHONE 'NOK PHONE',
       P06.BIRTHDATE 'NOK BIRTH DATE',
       dbo.ad_AddSeparator(
           P06.ADDRESS1,
           dbo.ad_AddSeparator(
               P06.ADDRESS2,
               dbo.ad_AddSeparator(
                   P06.ADDRESS3,
                   dbo.ad_AddSeparator(
                       P06.POSTPLACE,
                       dbo.ad_AddSeparator(P06.POSTCODE, PC.COUNTRYNAME)
                   )
               )
           )
       ) 'NOK ADDRESS',
       P06.COUNTRY 'NOK COUNTRY',
       P01.EMPLOYMENTSTARTDATE     EMPLOYMENTSTARTDATE,
       P01.EMPLOYMENTENDDATE       EMPLOYMENTENDDATE,
       C38.GROUPNO                 PAYROLLSTATUSSORTNO,
       PL.DOCUMENT                 PPICTURE,
       p01.COSTPLACE            AS 'Department/Cost Place Code',
       c43.[NAME]               AS 'Department/Cost Place'
FROM   PW001P01 P01
       LEFT JOIN PW001P06 P06
            ON  P01.PIN = P06.PIN
       LEFT JOIN PW001C07 C07
            ON  P06.RELATION = C07.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 PW001C38 C38
            ON  P01.PAYROLLSTATUS = C38.CODE
       LEFT JOIN PWCOUNTRY PC
            ON  P06.COUNTRY = PC.COUNTRYCODE
       LEFT JOIN PW001P01PICT PL
            ON  P01.PIN = PL.PIN
       LEFT JOIN PW001C43 c43
            ON  c43.CODE = p01.COSTPLACE
WHERE  P06.[EMERGENCY] = 1 
UNION ALL 
SELECT P01.PIN                     PIN,
       CASE 
            WHEN p06.[EMERGENCY] = 1 THEN 'Emergency Contact added'
            ELSE 'No Emergency Contact added'
       END                      AS 'Emergency Contact Status',
       P01.NAME                    NAME,
       P01.FIRSTNAME               FIRSTNAME,
       P01.MIDDLENAME              MIDDLENAME,
       P01.LASTNAME                LASTNAME,
       C02.NAME                 AS 'CURRENT RANK',
       P01.CLIENT                  NUMORGID,
       '' 'CONTACT PRIORITY',
       '' 'NOK NAME',
       '' 'NOK RELATIONSHIP',
       '' 'NOK PHONE',
       NULL 'NOK BIRTH DATE',
       '' 'NOK ADDRESS',
       '' 'NOK COUNTRY',
       P01.EMPLOYMENTSTARTDATE     EMPLOYMENTSTARTDATE,
       P01.EMPLOYMENTENDDATE       EMPLOYMENTENDDATE,
       C38.GROUPNO                 PAYROLLSTATUSSORTNO,
       PL.DOCUMENT                 PPICTURE,
       p01.COSTPLACE            AS 'Department/Cost Place Code',
       c43.[NAME]               AS 'Department/Cost Place'
FROM   PW001P01 P01
       LEFT JOIN PW001P06 P06
            ON  (
                    (P01.PIN = P06.PIN)
                    AND (NOT PRIORITY IS NULL)
                    AND NOT EXISTS
                        (
                            SELECT *
                            FROM   PW001P06 F
                            WHERE  (F.PIN = P06.PIN)
                                   AND (
                                           (P06.PRIORITY > F.PRIORITY)
                                           OR (
                                                  (P06.PRIORITY = F.PRIORITY)
                                                  AND (P06.SEQUENCENO < F.SEQUENCENO)
                                              )
                                       )
                        )
                )
       LEFT JOIN PW001C07 C07
            ON  P06.RELATION = C07.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 PW001C38 C38
            ON  P01.PAYROLLSTATUS = C38.CODE
       LEFT JOIN PWCOUNTRY PC
            ON  P06.COUNTRY = PC.COUNTRYCODE
       LEFT JOIN PW001P01PICT PL
            ON  P01.PIN = PL.PIN
       LEFT JOIN PW001C43 c43
            ON  c43.CODE = p01.COSTPLACE
WHERE  NOT EXISTS (
           SELECT 1
           FROM   PW001P06 t
           WHERE  t.PIN = p01.PIN
                  AND t.[EMERGENCY] = 1
       )

Columns Specification

Column	Description/ Location in APM
PIN	The Pin number from Personal Details.
Emergency Contact Status	In Case of Emergeny field from the Family. If Emergency is tag the value is “Emergency Contact added” otherwise “No Emergency Contact added“
Name	The Full Name from the Personal Details.
FirstName	The Firstname from the Personal Details.
MiddleName	The Middle Name from the Personal Details.
LastName	The Last Name from the Personal Details.
Current Rank	Current Rank of the person in Perosonal Details.
Numorgid	The client from the Personal Details.
CONTACT PRIORITY	Contact priority number from the Family telecommunication details.
NOK Name	Next of Kin Name, Name from the Family.
NOK Relatationship	Relationship from the Family.
NOK Phone	Contact information from the Family.
NOK Birth Date	Birthday information from the Family.
NOK Address	Next of Kin Address from the Family.
NOK Country	Next of Kin Country Address from the Family.
EmploymentStartDate	Employment Start Date from the Personal Details.
EmploymentEndDate	Employment End Date from the Personal Details.
PayrollStatusSortNo	Payroll status from the Pesonal Details.
PPicture	Picture of the crew.
Department/Cost Place Code	Cost place code from the Personal Details.
Department/Code Place	Cost place description.

Start Page

N/A

Other

N/A

Emergency Contact View