Summary	The view provides an overview of payments for the current payroll period.
Keywords	Payroll, Payments
Category	Crew List View

Description

The view provides an overview of payments for the current payroll period.

View Sample

Main Data Selection

All the crew members have payments within the current payroll period. Only calculatedamount1 is considered by this view.

SQL statement

Click here to expand...

CREATE VIEW dbo.PW001SRV203
AS

SELECT pay.PIN,
       P01.NAME                   AS 'Full Name',
       P01.FIRSTNAME              AS 'First Name',
       P01.MIDDLENAME             AS 'Middle Name',
       P01.LASTNAME               AS 'Last Name',
       persorg.name               AS Organization,
       pay.periodeused            AS Period,
       CONVERT(VARCHAR, PAY.ENTRYCODE) + ' ' + EC.TEXT AS 'Entry Code',
       CASE 
            WHEN pay.autoentry = 'Y' 
                 OR pay.ENTRYCODE LIKE '%994' 
                 OR pay.ENTRYCODE LIKE '%995' 
                 OR pay.ENTRYCODE LIKE '%996' 
                 OR pay.ENTRYCODE LIKE '%998' 
                 OR pay.ENTRYCODE LIKE '%999' THEN 'Generated automatically'
            WHEN pay.SOURCE = 'I' AND activityseq IS NULL THEN 'Imported Travel Expenses'
            WHEN pay.SOURCE = 'I' AND activityseq IS NOT NULL THEN 'Imported Timesheets'
            ELSE                     'Added manually/ Imported Excel'
       END 'Entry Type',
       ISNULL(CAT.NAME, 'Other')  AS 'Category',
       pay.CALCULATEDAMOUNT1      AS 'Amount',
       pay.FromDate               AS 'Date From',
       pay.ToDate                 AS 'Date To',
       CAST(
           REPLACE(
               CASE 
                    WHEN pay.ENTRYFIELD2 = '' THEN '0'
                    ELSE pay.ENTRYFIELD2
               END,
               ',',
               '.'
           ) AS FLOAT
       )                          AS 'Days/Hours',
       CASE 
            WHEN pay.entryfield3 = '' THEN 0
            ELSE ROUND(
                     (
                         dbo.ad_StrToFloat(
                             RIGHT(
                                 pay.entryfield3,
                                 LEN(pay.entryfield3) - PATINDEX('%[0-9]%', pay.entryfield3) + 1
                             )
                         )
                     ),
                     2
                 )
       END                        AS 'Daily/Hourly Rate',
       pay.currencyforpayment     AS 'Payment Currency ',
       pay.currencyrateforpayment 'Payment Currency Rate',
       vsl.name                   AS Vessel,
       pay.accountdim3            AS Department,
       pay.ACCOUNTDIM4 'Rank/Position',
       pay.ACCOUNTDIM5 'Employer',
       pay.ACCOUNTDIM6 'Officer/Rating',
       US.USER_NAME 'Created By',
       pay.CreateTime 'Create Time',
       pay.CALCULATEDBY 'Calculated By',
       pay.calculatetime 'Calculation Time',
       /************************************************************
       CASE 
       WHEN PAY.ENTRYCODE BETWEEN 30 AND 39 THEN pay.CALCULATEDAMOUNT10
       ELSE pay.CALCULATEDAMOUNT1
       END 'Amount',
       ************************************************************/
       pay.payscaletable          AS 'PayScale Table',
       pay.payscalecode           AS 'PayScale Code',
       payscaleinfo.Table_Name 'PayScale Table Name',
       payscaleinfo.Payscale_Name 'PayScale Name',
       P0Y.PAYROLLPERIODLOCK      AS 'Person Locked (Y/N)',
       CASE 
            WHEN pay.PAID IS NULL OR PAY.PAID = '' THEN 'N'
            ELSE PAY.PAID
       END 'Entry Paid (Y/N)',
       PAY.[TEXT]                 AS Comments,
       CASE 
            WHEN PAI.BALANCEACTION = '0' THEN 'Carry Forward To Next Month'
            WHEN PAI.BALANCEACTION = '1' THEN 'Transfer To Bank Account'
            WHEN PAI.BALANCEACTION = '2' THEN 'Interpay'
            WHEN PAI.BALANCEACTION = '3' THEN 'Alpha Credit Bank'
            WHEN PAI.BALANCEACTION = '4' THEN 'Cash'
            WHEN PAI.BALANCEACTION = '5' THEN 'Citibank Direct Deposit'
            WHEN PAI.BALANCEACTION = '6' THEN 'Citibank'
            WHEN PAI.BALANCEACTION = '7' THEN 'Chase Manhattan'
            WHEN PAI.BALANCEACTION = '8' THEN 'Hellenic Bank'
            WHEN PAI.BALANCEACTION = '9' THEN 'Isabel Domestic'
            WHEN PAI.BALANCEACTION = 'A' THEN 'Isabel International'
            WHEN PAI.BALANCEACTION = 'B' THEN 'Bank Transfer'
            WHEN PAI.BALANCEACTION = 'C' THEN 'Swedish Domestic'
            WHEN PAI.BALANCEACTION = 'D' THEN 'Swedish International'
            WHEN PAI.BALANCEACTION = 'E' THEN 'Philippine Banking'
            WHEN PAI.BALANCEACTION = 'F' THEN 'Manual Bank'
            WHEN PAI.BALANCEACTION = 'G' THEN 'Zagrebancka'
            WHEN PAI.BALANCEACTION = 'H' THEN 'ING Bank'
            WHEN PAI.BALANCEACTION = 'I' THEN 'Direct Deposit E-Monee'
            WHEN PAI.BALANCEACTION = 'J' THEN 'Deutsche Bank'
            WHEN PAI.BALANCEACTION = 'K' THEN 'SACS'
            WHEN PAI.BALANCEACTION = 'L' THEN 'Hellenic Cyprus'
            WHEN PAI.BALANCEACTION = 'M' THEN 'MT 100'
            WHEN PAI.BALANCEACTION = 'N' THEN 'Citibank (PL)'
            WHEN PAI.BALANCEACTION = '0' THEN 'Agent .R'
            WHEN PAI.BALANCEACTION = 'P' THEN 'German Bank'
            WHEN PAI.BALANCEACTION = 'Q' THEN 'Trident Trust'
            WHEN PAI.BALANCEACTION = 'R' THEN 'Citibank (Asia)'
            WHEN PAI.BALANCEACTION = 'S' THEN 'PNC Bank'
            WHEN PAI.BALANCEACTION = 'T' THEN 'Chase Insight'
            WHEN PAI.BALANCEACTION = 'U' THEN 'Ocean Pay'
            WHEN PAI.BALANCEACTION = 'V' THEN 'Banco de Oro'
            WHEN PAI.BALANCEACTION = 'W' THEN 'Bank of Philippine Island'
            WHEN PAI.BALANCEACTION = 'X' THEN 'CitiDirect (Onboard)'
            WHEN PAI.BALANCEACTION = 'Y' THEN 'Metrobank Direct (PH)'
            WHEN PAI.BALANCEACTION = 'Z' THEN 'Elektron'
            WHEN PAI.BALANCEACTION = 'A1' THEN 'E-Banking (Maramut)'
            WHEN PAI.BALANCEACTION = 'A2' THEN 'RBS Direct Access'
            WHEN PAI.BALANCEACTION = 'A3' THEN 'Brazilian Banks'
            WHEN PAI.BALANCEACTION = 'A4' THEN 'NETS'
            WHEN PAI.BALANCEACTION = 'A5' THEN 'NONE'
            WHEN PAI.BALANCEACTION = 'A6' THEN 'JDP Morgan'
            WHEN PAI.BALANCEACTION = 'A7' THEN 'J. P. Morgan Access'
            WHEN PAI.BALANCEACTION = 'A8' THEN 'Spar Nord Domestic'
            WHEN PAI.BALANCEACTION = 'A9' THEN 'Spar Nord International'
            WHEN PAI.BALANCEACTION = 'B1' THEN 'Rabobank'
            WHEN PAI.BALANCEACTION = 'B2' THEN 'Deutsche Bank'
            WHEN PAI.BALANCEACTION = 'B3' THEN 'Berenberg Bank'
            WHEN PAI.BALANCEACTION = 'B4' THEN 'ISO20022'
            WHEN PAI.BALANCEACTION = 'B5' THEN 'Brightwell'
            WHEN PAI.BALANCEACTION = 'B9' THEN 'Kadmos'
            ELSE PAI.BALANCEACTION
       END                        AS 'PAYMENT METHOD',
       CASE 
            WHEN PAI.PAYNAME <> '' THEN 'Alternative Receiver'
            ELSE                     'Crew Memeber'
       END 'RECEIVER',
       PAI.ACCOUNTNO 'RECEIVER IBAN/ Bank Account No',
       BANK.NAME 'RECEIVER Bank',
       PAI.SWIFT 'RECEIVER SWIFT',
       CASE 
            WHEN PAI.PAYNAME = '' THEN p01.name
            ELSE PAI.PAYNAME
       END 'RECEIVER Name',
       CASE 
            WHEN PAI.PAYNAME = '' AND (PAI.ADDRESS1 + ' ' + PAI.ADDRESS2 + ' ' + PAI.ADDRESS3) = '' THEN (P01.ADDRESS1 + ' ' + P01.ADDRESS2 + ' ' + P01.ADDRESS3)
            ELSE (PAI.ADDRESS1 + ' ' + PAI.ADDRESS2 + ' ' + PAI.ADDRESS3)
       END 'RECEIVER Address',
       CASE 
            WHEN PAI.PAYNAME = '' AND PAI.paycountry = '' THEN P01.ADDRESS_COUNTRY
            ELSE PAI.paycountry
       END 'RECEIVER Country',
       CASE 
            WHEN PAI.PAYNAME = '' AND PAI.PAYPOSTCODE = '' THEN P01.POSTCODE
            ELSE PAI.PAYPOSTCODE
       END 'RECEIVER Post Code',
       CASE 
            WHEN PAI.PAYNAME = '' AND PAI.PAYPOSTPLACE = '' THEN P01.POSTPLACE
            ELSE PAI.PAYPOSTPLACE
       END 'RECEIVER Post Place',
       p01.client                    numorgid,
       p01.employmentstartdate,
       p01.employmentenddate,
       pay.accountdim2            AS VesOrgID
FROM   pw001pay pay
       LEFT JOIN pw001p01 p01
            ON  p01.pin = pay.pin
       LEFT JOIN pworg ves
            ON  ves.numorgid = pay.accountdim2
       LEFT JOIN pyety001 ec
            ON  ec.entrycode = pay.entrycode
       LEFT JOIN pworg persorg
            ON  persorg.numorgid = p01.client
       LEFT JOIN PW001C117 CAT
            ON  ec.CATEGORY = CAT.SEQUENCENO
            AND pay.ENTRYCODE = ec.ENTRYCODE
       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 = pay.PAYSCALETABLE
            AND payscaleinfo.Payscale_Code = pay.PAYSCALECODE
       LEFT JOIN PWSECURITY_USERS US
            ON  US.USERID = PAY.CREATEDBY
       LEFT JOIN PW001P0Y p0y
            ON  p0y.PIN = p01.PIN
       LEFT JOIN PW001PAI PAI
            ON  PAI.SEQNO = PAY.SEQNO
       LEFT JOIN PW001C26 BANK
            ON  PAI.RECEIVERBANK = BANK.CODE
       JOIN PWORG VSL
            ON  VSL.NUMORGID = dbo.ad_scanorgtree(pay.ACCOUNTDIM2, 3)

Columns Specification

Column	Description/ Location in APM
PIN	Personal Details > Personal > PIN
Full Name	Personal Details > Personal > Full Name
First Name	Personal Details > Personal > First Name
Middle Name	Personal Details > Personal > Middle name
Last Name	Personal Details > Personal > Last Name
Organization	Personal Details > Employment > Organization/Company
Period	Current payroll period
Entry Code	Payroll > Entries > Entry Code Number + Entry Code Name
Entry Type	Generated automaticallyatically pay.AUTOENTRY = 'Y' pay.ENTRYCODE IN (994, 995, 996, 998, 999) Imported Travel Expenses pay.SOURCE = 'I' AND pay.ACTIVITYSEQ IS NULL Imported Timesheets pay.SOURCE = 'I' AND pay.ACTIVITYSEQ IS NOT NULL ELSE Added manually/ Imported Excel pay.AUTOENTRY = 'Y' pay.ENTRYCODE IN (994, 995, 996, 998, 999) CASE WHEN pay.autoentry = 'Y' OR pay.ENTRYCODE LIKE '%994' OR pay.ENTRYCODE LIKE '%995' OR pay.ENTRYCODE LIKE '%996' OR pay.ENTRYCODE LIKE '%998' OR pay.ENTRYCODE LIKE '%999' THEN 'Generated automatically' WHEN pay.SOURCE = 'I' AND activityseq IS NULL THEN 'Imported Travel Expenses' WHEN pay.SOURCE = 'I' AND activityseq IS NOT NULL THEN 'Imported Timesheets' ELSE 'Added manually/ Imported Excel' END 'Entry Type'
Category	Payroll > Entries > Entry Code > Category
Amount	Payroll > Entries > Entry Code > Calculated Amount 1
Date From	Payroll > Entries > Entry Code > From Date
Date To	Payroll > Entries > Entry Code > To Date
Days/Hours	Payroll > Entries > Entry Code > Entry Field 2
Daily/Houry Rate	Payroll > Entries > Entry Code > Entry Field 3
Payment Currency	Payroll > Entries > Entry Code > Payment Currency
Payment Currency Rate	Payroll > Entries > Entry Code > Payment Currency Rate
Vessel	Payroll > Entries > Entry Code > Account Dim 2
Department	Payroll > Entries > Entry Code > Account Dim 3
Rank/Position	Payroll > Entries > Entry Code > Account Dim 4
Employer	Payroll > Entries > Entry Code > Account Dim 5
Officer/Rating	Payroll > Entries > Entry Code > Account Dim 6
Created by	Payroll > Entries > Entry Code > User who calculates/inserted payroll
Create Time	Payroll > Entries > Entry Code > User who calculates/inserted payroll
Calculation Time	Payroll > Entries > Entry Code > The time when user calculates payroll
Payscale Table	Payroll > Entries > Entry Code > Payscale table used
Payscale Code	Payroll > Entries > Entry Code > Payscale code used
Payscale Name	Payroll > Entries > Entry Code > Payscale Name
Person Locked (Y/N)	Payroll > Entries > Period Lock
Entry Paid (Y/N)	Payroll > Entries > Paid Status
Comments	Payroll > Entries > Comments
Payment Method	Payroll > Entries > Entry Code > Payment Method
Receiver	Payroll > Entries > Entry Code > Additional Info > Receiver (Alternate Receiver or Crew Member)
Receiver IBAN/Bank Account No	Payroll > Entries > Entry Code > Additional Info > Receiver Bank Account
Receiver Bank	Payroll > Entries > Entry Code > Additional Info > Receiver Bank Name
Receiver Swift	Payroll > Entries > Entry Code > Additional Info > Receiver Swift
Receiver Name	Payroll > Entries > Entry Code > Additional Info > Receiver Name
Receiver Address	Payroll > Entries > Entry Code > Additional Info > Receiver Address
Receiver Country	Payroll > Entries > Entry Code > Additional Info > Receiver Country
Receiver Post Code	Payroll > Entries > Entry Code > Additional Info > Receiver Post Code
Receiver Post Place	Payroll > Entries > Entry Code > Additional Info > Receiver Post Place
NUMORGID	Personal Details > Employment > Organization’s NUMORGID
EMPLOYMENTSTARTDATE	Personal Details > Employment > Employment Start Date
EMPLOYMENTENDDATE	Personal Details > Employment > Employment End Date
VesOrgID	Payroll > Entries > Entry Code > Account Dim 2

Crew List View: Payroll Summary (current period)

Description

View Sample

Main Data Selection

SQL statement

Columns Specification