1 Description | 2 View Sample | 3 Main Data Selection | 4 SQL statement | 5 Columns Specification

Summary	This is the generic payroll view. It shows all the crew members under the selected organization and units below it.
Keywords	Payroll
Category	Crew List View

Description

This is the generic payroll view. It shows all the crew members under the selected organization and units below it.

This view shows:

generic person's details such as passport details, personal details,
current contract details
payroll payment details
etc.

View Sample

Main Data Selection

All crew members.

SQL statement

CREATE VIEW dbo.PW001SRV501
AS

SELECT P01.PIN                               PIN,
       P01.ALTERNATIVEPIN                 AS 'ALTERNATIVE PIN',
       P01.NAME                           AS 'Full Name',
       P01.TITLENAME                      AS 'Title Name',
       P01.FIRSTNAME                      AS 'First Name',
       P01.MIDDLENAME                     AS 'Middle Name',
       P01.LASTNAME                       AS 'Last Name',
       P01.MAIDENNAME                     AS 'Maiden Name',
       CASE 
            WHEN p01.SEX = 'M' THEN 'Male'
            WHEN p01.SEX = 'F' THEN 'Female'
            ELSE 'Undefined'
       END                                AS Gender,
       p01Rank.[NAME]                     AS [Rank],
       ORG.NAME                           AS 'Organization',
       C02.NAME 'Current Rank',
       P01.CLIENT                            Numorgid,
       P01.EmploymentStartDate,
       P01.EmploymentEndDate,
       nat.NATIONALITY                    AS Nationality,
       P01.BIRTHDATE 'Birth date',
       P01.PERSONALIDNO                   AS 'Personal ID',
       PASS.TDNUMBER 'Passport No',
       PASS.DATETO                        AS 'Passport Expiry',
       passc.COUNTRYNAME                  AS 'Passport Country of Issue',
       P01.ADDRESS1 + ' ' + P01.ADDRESS2  AS 'Address',
       P01.POSTCODE 'Post Code',
       P01.POSTPLACE 'Post Place',
       PC.COUNTRYNAME                     AS 'Address Country',
       P01.HOMEAIRPORT 'Home Airport',
       email.TELENO                       AS 'E-MAIL',
       mob.TELENO                         AS 'Mobile Phone',
       PL.DOCUMENT                           PPICTURE,
       CASE 
            WHEN CONTRACTKIND = 0 THEN 'Main Contract'
            WHEN CONTRACTKIND = 1 THEN 'Sub Contract'
            WHEN CONTRACTKIND = 2 THEN 'Ammendment'
            ELSE ''
       END                                AS 'Contract Type',
       CASE 
            WHEN CONTRACTKIND = 2 THEN p20.DATESTART
            ELSE NULL
       END                                AS 'Amendment Date Start',
       CASE 
            WHEN CONTRACTKIND = 2 THEN p20.DATEEND
            ELSE NULL
       END                                AS 'Amendment Date End',
       C32CONT.Text 'Contract Name',
       C02CONT.NAME 'Contract Rank',
       p20.DATESTART 'Contract Start',
       p20.DATEEND 'Contract End',
       p20.DURATION 'Trial period duration',
       p20.TRIALPERIODEND 'Trial period end',
       p20.PAYSCALETABLE 'Contract Payscale Table',
       payscaleinfo.Table_Name 'Contract Pasyscale Table Name',
       p20.PAYSCALECODE 'Contract Payscale Code',
       p20.DATESTART,
       payscaleinfo.Payscale_Name 'Contract Payscale Name',
       CONVERT(VARCHAR, CAST(ROUND(p20r.RATEN, 2) AS DECIMAL(10, 2))) 'Merit by Individual',
       CASE 
            WHEN ISNULL(p0y.PAYROLLPERIODLOCK, 'N') = '' THEN 'N'
            ELSE ISNULL(p0y.PAYROLLPERIODLOCK, 'N')
       END                                AS 'Paroll Period Lock',
       CASE 
            WHEN p01.Transferbalance = '0' THEN 'Carry Forward To Next Month'
            WHEN p01.Transferbalance = '1' THEN 'Transfer To Bank Account'
            WHEN p01.Transferbalance = '2' THEN 'Interpay'
            WHEN p01.Transferbalance = '3' THEN 'Alpha Credit Bank'
            WHEN p01.Transferbalance = '4' THEN 'Cash'
            WHEN p01.Transferbalance = '5' THEN 'Credit/Debit Card'
            WHEN p01.Transferbalance = '6' THEN 'Citibank'
            WHEN p01.Transferbalance = '7' THEN 'Chase Manhattan'
            WHEN p01.Transferbalance = '8' THEN 'Hellenic Bank'
            WHEN p01.Transferbalance = '9' THEN 'Isabel Domestic'
            WHEN p01.Transferbalance = 'A' THEN 'Isabel International'
            WHEN p01.Transferbalance = 'B' THEN 'Bank Transfer'
            WHEN p01.Transferbalance = 'C' THEN 'Swedish Domestic'
            WHEN p01.Transferbalance = 'D' THEN 'Swedish International'
            WHEN p01.Transferbalance = 'E' THEN 'Philippine Banking'
            WHEN p01.Transferbalance = 'F' THEN 'Manual Bank'
            WHEN p01.Transferbalance = 'G' THEN 'Zagrebancka'
            WHEN p01.Transferbalance = 'H' THEN 'ING Bank'
            WHEN p01.Transferbalance = 'I' THEN 'Direct Deposit E-Monee'
            WHEN p01.Transferbalance = 'J' THEN 'Deutsche Bank'
            WHEN p01.Transferbalance = 'K' THEN 'SACS'
            WHEN p01.Transferbalance = 'L' THEN 'Hellenic Cyprus'
            WHEN p01.Transferbalance = 'M' THEN 'MT 100'
            WHEN p01.Transferbalance = 'N' THEN 'Citibank (PL)'
            WHEN p01.Transferbalance = '0' THEN 'Agent .R'
            WHEN p01.Transferbalance = 'P' THEN 'German Bank'
            WHEN p01.Transferbalance = 'Q' THEN 'Trident Trust'
            WHEN p01.Transferbalance = 'R' THEN 'Citibank (Asia)'
            WHEN p01.Transferbalance = 'S' THEN 'PNC Bank'
            WHEN p01.Transferbalance = 'T' THEN 'Chase Insight'
            WHEN p01.Transferbalance = 'U' THEN 'Ocean Pay'
            WHEN p01.Transferbalance = 'V' THEN 'Banco de Oro'
            WHEN p01.Transferbalance = 'W' THEN 'Bank of Philippine Island'
            WHEN p01.Transferbalance = 'X' THEN 'CitiDirect (Onboard)'
            WHEN p01.Transferbalance = 'Y' THEN 'Metrobank Direct (PH)'
            WHEN p01.Transferbalance = 'Z' THEN 'Elektron'
            WHEN p01.Transferbalance = 'A1' THEN 'E-Banking (Maramut)'
            WHEN p01.Transferbalance = 'A2' THEN 'RBS Direct Access'
            WHEN p01.Transferbalance = 'A3' THEN 'Brazilian Banks'
            WHEN p01.Transferbalance = 'A4' THEN 'NETS'
            WHEN p01.Transferbalance = 'A5' THEN 'NONE'
            WHEN p01.Transferbalance = 'A6' THEN 'JDP Morgan'
            WHEN p01.Transferbalance = 'A7' THEN 'J. P. Morgan Access'
            WHEN p01.Transferbalance = 'A8' THEN 'Spar Nord Domestic'
            WHEN p01.Transferbalance = 'A9' THEN 'Spar Nord International'
            WHEN p01.Transferbalance = 'B1' THEN 'Rabobank'
            WHEN p01.Transferbalance = 'B2' THEN 'Deutsche Bank'
            WHEN p01.Transferbalance = 'B4' THEN 'ISO20022'
            WHEN p01.Transferbalance = 'B5' THEN 'Brightwell'
       END                                AS 'Payment Method',
       CASE 
            WHEN GETDATE() BETWEEN p03cur.DATEFROM AND ISNULL(p03cur.DATETO, p03cur.TODATEESTIMATED) THEN C12.[TEXT]--p03cur.CODE
            ELSE 'Available'
       END                                AS 'Status',
       CASE 
            WHEN p03cur.PIN IS NOT NULL THEN 'Currently Onboard'
            WHEN p03plan.PIN IS NOT NULL THEN CONVERT(VARCHAR, p03plan.DATEFROM, 110)
            WHEN p03cur.PIN IS NULL AND p03plan.PIN IS NULL THEN 'Not planned'
       END 'Nearest Embarkation Date',
       (
           CONVERT(
               NUMERIC(6, 2),
               CONVERT(REAL, DATEDIFF(DAY, P01.EMPLOYMENTSTARTDATE, GETDATE())) / 365
           )
       ) 'Employment Seniority',
       ISNULL(
           (
               CONVERT(
                   NUMERIC(6, 2),
                   CONVERT(
                       REAL,
                       DATEDIFF(DAY, ISNULL(p03cur.DATEFROM, p03plan.DATEFROM), GETDATE())
                   ) / 365
               )
           ),
           0
       ) 'Activity Seniority',
       dbo.ad_PayrollSeniority(
           CMP.PERIODE,
           PRD.COMPLETIONDATE,
           P20.PAYROL_SENIORITY_START,
           P20.PAYROL_SENIORITY_END,
           REPLACE(P20.PAYROLL_SENIORITY_YEARS, ',', '.')
       ) 'Payroll Seniority',
       p01.COSTPLACE                      AS 'Department/Cost Place Code',
       c43.[NAME]                         AS 'Department/Cost Place'
FROM   PW001P01 P01
       LEFT JOIN PW001P08 pass
            ON  pass.PIN = p01.PIN
            AND pass.CODE = 'P'
            AND NOT EXISTS (
                    SELECT 1
                    FROM   PW001P08 t
                    WHERE  t.PIN = pass.PIN
                           AND t.CODE = pass.CODE
                           AND (
                                   t.DATETO < pass.DATETO
                                   OR (t.DATETO = pass.DATETO AND t.SEQUENCENO > pass.SEQUENCENO)
                               )
                )
       LEFT JOIN dbo.PW001P03 P03cur
            ON  P01.PIN = P03cur.PIN
                --AND (P03cur.CODE IN (SELECT c12.CODE
                --                    FROM   PW001C12 c12
                --                    WHERE  c12.OPTIONS LIKE '%S%') OR p03cur.CODE = 'SHORE')
            AND (P03cur.DATETO IS NULL OR P03cur.DATETO >= GETDATE())
            AND P03cur.DATEFROM <= GETDATE()
            AND P03cur.PLANNED <> 'Y'
       LEFT JOIN PW001P03 p03plan
            ON  p03plan.PIN = p01.PIN
            AND p03plan.PLANNED = 'Y'
            AND p03plan.CODE IN (SELECT t.CODE
                                 FROM   pw001c12 t
                                 WHERE  t.OPTIONS LIKE '%S%')
            AND NOT EXISTS (
                    SELECT 1
                    FROM   PW001P03 p03t
                    WHERE  p03t.PIN = p01.PIN
                           AND p03t.PLANNED = 'Y'
                           AND p03t.CODE IN (SELECT t.CODE
                                             FROM   PW001C12 t
                                             WHERE  t.OPTIONS LIKE '%S%')
                           AND p03t.DATEFROM < p03plan.DateFrom
                )
       LEFT JOIN (
                SELECT NUMORGID,
                       PERIODE
                FROM   PWORGCMP
                UNION ALL
                SELECT NUMORGID,
                       PERIODE
                FROM   PWORGVES
            ) CMP
            ON  (CMP.NUMORGID = dbo.ad_ScanOrgTreePayroll(p01.client))
       LEFT JOIN PWCMPPAY PRD
            ON  (COMPANYID = CMP.NUMORGID)
            AND ((PTYPE <> 'T') OR (PTYPE IS NULL))
            AND (PRD.PERIOD = CMP.PERIODE)
       LEFT JOIN PWCOUNTRY passc
            ON  passc.COUNTRYCODE = pass.ISSUE_COUNTRY
       LEFT JOIN PW001P0T email
            ON  email.PIN = p01.PIN
            AND email.TELETYPE = 6
            AND email.TELEPRIORITY IS NOT NULL
            AND NOT EXISTS (
                    SELECT 1
                    FROM   PW001P0T t
                    WHERE  t.PIN = email.PIN
                           AND t.TELETYPE = email.TELETYPE
                           AND t.TELEPRIORITY IS NOT NULL
                           AND (
                                   t.TELEPRIORITY < email.TELEPRIORITY
                                   OR t.TELEPRIORITY = email.TELEPRIORITY
                                   AND t.SEQUENCENO < email.SEQUENCENO
                               )
                )
       LEFT JOIN PW001P0T mob
            ON  mob.PIN = p01.PIN
            AND mob.TELETYPE = 3
            AND mob.TELEPRIORITY IS NOT NULL
            AND NOT EXISTS (
                    SELECT 1
                    FROM   PW001P0T t
                    WHERE  t.PIN = mob.PIN
                           AND t.TELETYPE = mob.TELETYPE
                           AND t.TELEPRIORITY IS NOT NULL
                           AND (
                                   t.TELEPRIORITY < mob.TELEPRIORITY
                                   OR t.TELEPRIORITY = mob.TELEPRIORITY
                                   AND t.SEQUENCENO < mob.SEQUENCENO
                               )
                )
       LEFT JOIN PWCOUNTRY nat
            ON  nat.COUNTRYCODE = p01.NATIONALITY
       LEFT JOIN PW001P0P P0P
            ON  ((P01.PIN = P0P.PIN) AND (P0P.PNUMBER = 'A'))
       LEFT JOIN PW001C02 p01Rank
            ON  p01Rank.CODE = p01.[RANK]
       LEFT JOIN PW001C02 C02
            ON  P0P.POSITIONID = C02.CODE
       LEFT JOIN PWORG ORG
            ON  P01.CLIENT = ORG.NUMORGID
       LEFT JOIN PWCOUNTRY PC
            ON  P01.ADDRESS_COUNTRY = PC.COUNTRYCODE
       LEFT JOIN PW001P01PICT PL
            ON  P01.PIN = PL.PIN
       LEFT JOIN PW001P20 p20
            ON  p20.PIN = p01.PIN
            AND p20.HISTORICAL = 'F'
            AND NOT EXISTS (
                    SELECT 1
                    FROM   PW001P20 t
                    WHERE  t.PIN = p20.PIN
                           AND t.HISTORICAL = 'F'
                           AND t.DATESTART > p20.DATESTART
                )
       LEFT JOIN PW001C02 C02CONT
            ON  p20.RANK = C02CONT.CODE
       LEFT JOIN PW001C32 C32CONT
            ON  p20.CONTRACTTYPE = C32CONT.code
       LEFT JOIN PW001P0Y p0y
            ON  p0y.PIN = p01.PIN
       LEFT JOIN (
                SELECT C0.REGULATIVECODE  AS 'Table_Code',
                       C0.REGULATIVENAME  AS 'Table_Name',
                       C1.PAYSCALECODE    AS 'Payscale_Code',
                       C1.PAYSCALENAME    AS 'Payscale_Name'
                FROM   PWPSC000 C0
                       LEFT JOIN PWPSC001 C1
                            ON  C1.SEQNO = C0.SEQNO
            ) payscaleinfo
            ON  payscaleinfo.Table_Code = p20.PAYSCALETABLE
            AND payscaleinfo.Payscale_Code = p20.PAYSCALECODE
       LEFT JOIN pw001c12 c12
            ON  c12.CODE = p03cur.CODE
       LEFT JOIN PW001C43 c43
            ON  c43.CODE = p01.COSTPLACE
       LEFT JOIN PW001P20R p20r
            ON  p20r.CONTRACT_SEQNO = p20.SEQUENCENO

Columns Specification

Column	Description/ Location in APM

Column	Description/ Location in APM
PIN	Personal Details > Personal > PIN
ALTERNATIVEPIN	Personal Details > Personal > Alternative PIN
Full Name	Personal Details > Personal > Full Name
Title Name	Personal Details > Personal > Title Name
First Name	Personal Details > Personal > First Name
Middle Name	Personal Details > Personal > Middle name
Last Name	Personal Details > Personal > Last Name
Maiden Name	Personal Details > Personal > Maiden Name
Gender	Personal Details > Personal > Gender in full text
Organization	Personal Details > Employment > Organization/Company
Current Rank	Personal Details > Employment > Current Rank
NUMORGID	Personal Details > Employment > Organization’s NUMORGID
EmploymentStartDate	Personal Details > Employment > Employment Start Date
EmploymentEnddate	Personal Details > Employment > Employment End Date
Nationality	Personal Details > Personal > Nationality
Birth Date	Personal Details > Personal > Birth Date
Personal ID	Personal Details > Personal > Personal ID
Passport No	Datagroups > Travel Document > Passport Number
Passport Expiry	Datagroups > Travel Document > Passport Expiry
Passport Country of Issue	Datagroups > Travel Document > Passport Country of Issue
Address	Personal Details > Personal > Address1 + Address2
Post Code	Personal Details > Personal > Post Code
Post Place	Personal Details > Personal > Post Place
Address Country	Personal Details > Personal > Address Country
Home Airport	Personal Details > Personal > Home Airport
Email	Personal Details > Personal > Telecommunications > Email
Mobile Phone	Personal Details > Personal > Telecommunications > Phone
PPicture	Personal Details > Personal > Picture
Contract Type	Datagroups > Contracts > Current Contract > Contract Type
Amendment Start Date	Datagroups > Contracts > Current Contract > Amendment Start Date
Amendment End Date	Datagroups > Contracts > Current Contract > Amendment End Date
Contract Name	Datagroups > Contracts > Current Contract > Details > Type
Contract Rank	Datagroups > Contracts > Current Contract > Details > Current Rank
Trial period duration	Datagroups > Contracts > Current Contract > Details > Trial period duration
Trial period end	Datagroups > Contracts > Current Contract > Details > Trial period end
Contract Payscale Table	Datagroups > Contracts > Current Contract > Details > Payscale Table
Contract Payscale Table Name	Datagroups > Contracts > Current Contract > Details > Payscale Table Name
Contract Payscale Code	Datagroups > Contracts > Current Contract > Details > Payscale Code
Contract Payscale Name	Datagroups > Contracts > Current Contract > Details > Payscale Name
Merit by Individual	Datagroups > Contracts > Current Contract > Personal Rates > RateNo 1
Payroll Period Lock	Payroll > Period Lock
Payment Method	The Balance Action of the person is set in Payroll > Payment Details.
Status	The Current activity code. Current activity crossing today’s date. If no activity, then the system should show Available as a status.
Nearest Embarkation Date	If the person is currently on board, then the column should state “Currently Onboard“ If the person is not on board, then the sign-on date of the nearest planned onboard activity should be shown. If no current activity or nearest planned activity, then it should state “Not planned“
Employment Seniority	Employment Seniority
Activity Seniority	Activity Seniority
Payroll Seniority	Payroll Seniority
Department/Cost Place Code	Personal Details > Personal > Payroll > Cost Place Code
Department Cost Place	Personal Details > Personal > Payroll > Cost Place

Reports, Views and Similar

Payroll General

Description

View Sample

Main Data Selection

SQL statement

Columns Specification