Customer	Adora Cruises
Summary	Displays all emergency contact details from the "Family" datagroup.
Keywords	Emergency Contact
Category	Crew List View

Description

The view provides access to Emergency contact information drawn from the Family datagroup. This essential contact information will become visible within the view once the "In Case of Emergency" setup is complete on the NOK record.

For multiple contacts associated with a person, display individual lines for each contact.
In the absence of an emergency contact, indicate "No Emergency Contact Added." If present, state "Emergency Contact Added."

View Sample

Main Data Selection

All Crew members.

SQL statement

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

Column	Description/ Location in APM
PIN	The Pin number from Personal Details.
Emergency Contact Status	In the "In Case of Emergency" field provided by the family, if an emergency contact is tagged, it will display “Emergency Contact added.” If no emergency contact is specified, it will show “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 Personal 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 Relationship	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

Reports, Views and Similar

Emergency Contact View