Summary	The view provides an overview of payments for the historical payroll periods.
Keywords	Payroll, Payments
Category	Analytical View

Description

The view provides an overview of payments for the historical payroll periods.

Main Data Selection

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

Setup and Configuration

SQL statement

--CREATE VIEW dbo.PW001SRV103 AS

SELECT PYH.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,
	   PYH.periodeused            AS Period,
	   CONVERT(VARCHAR, PYH.ENTRYCODE) + ' ' + EC.TEXT AS 'Entry Code',
	   case when PYH.autoentry = 'Y' 
				OR PYH.ENTRYCODE like '%994' 
				OR PYH.ENTRYCODE like '%995' 
				OR PYH.ENTRYCODE like '%996' 
				OR PYH.ENTRYCODE like '%998' 
				OR PYH.ENTRYCODE like '%999' THEN 'Generated automatically'
			ELSE 'Added manually'
       END 'Entry Type',
       ISNULL(CAT.NAME, 'Other')  AS 'Category',
	   PYH.CALCULATEDAMOUNT1 AS 'Amount',
       CAST(
           REPLACE(
               CASE 
                    WHEN PYH.ENTRYFIELD2 = '' THEN '0'
                    ELSE PYH.ENTRYFIELD2
               END,
               ',',
               '.'
           ) AS FLOAT
       )                          AS 'Days/Hours',

       CASE 
            WHEN PYH.entryfield3 = '' THEN 0
            ELSE ROUND(
                     (
                         dbo.ad_StrToFloat(
                             RIGHT(
                                 PYH.entryfield3,
                                 LEN(PYH.entryfield3) - PATINDEX('%[0-9]%', PYH.entryfield3) + 1
                             )
                         )
                     ),
                     2
                 )
       END                        AS 'Daily/Hourly Rate',
	   PYH.currencyforpayment     AS 'Payment Currency ',
       PYH.currencyrateforpayment 'Payment Currency Rate',

	   US.USER_NAME 'Created By',
	   PYH.CreateTime 'Create Time',
	   PYH.CALCULATEDBY 'Calculated By',
	   PYH.calculatetime 'Calculation Time',
 /************************************************************
       CASE 
            WHEN PYH.ENTRYCODE BETWEEN 30 AND 39 THEN PYH.CALCULATEDAMOUNT10
            ELSE PYH.CALCULATEDAMOUNT1
       END 'Amount',
 ************************************************************/
       PYH.payscaletable          AS 'PayScale Table',
       PYH.payscalecode           AS 'PayScale Code',
       payscaleinfo.Table_Name 'PayScale Table Name',
       payscaleinfo.Payscale_Name 'PayScale Name',
	          P0Y.PAYROLLPERIODLOCK  AS 'Person Locked (Y/N)',
       CASE 
				       WHEN PYH.PAID is null or PYH.PAID = '' THEN 'N'  
					   ELSE PYH.PAID
	   END 'Entry Paid (Y/N)',
       PYH.[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'
					   else PAI.BALANCEACTION
                  END        AS 'PAYMENT METHOD', 
	   CASE WHEN PAI.PAYNAME = '' then 'Crew Memeber'
			ELSE 'Alternative Receiver'
	   END 'RECEIVER',
	   PAI.ACCOUNTNO 'RECEIVER IBAN/ Bank Account No',
	   BANK.NAME 'RECEIVER Bank',
	   PAI.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,
	   PYH.accountdim2            AS VesOrgID
FROM   pw001pay PYH
       LEFT JOIN pw001p01 p01
            ON  p01.pin = PYH.pin
       LEFT JOIN pworg ves
            ON  ves.numorgid = PYH.accountdim2
       LEFT JOIN pyety001 ec
            ON  ec.entrycode = PYH.entrycode
       LEFT JOIN pworg persorg
            ON  persorg.numorgid = p01.client
       LEFT JOIN PW001C117 CAT
            ON  ec.CATEGORY = CAT.SEQUENCENO
            AND PYH.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 = PYH.PAYSCALETABLE
            AND payscaleinfo.Payscale_Code = PYH.PAYSCALECODE 
		LEFT JOIN PWSECURITY_USERS US on US.USERID=PYH.CREATEDBY
		LEFT JOIN PW001P0Y p0y
            ON  p0y.PIN = p01.PIN
		LEFT JOIN PW001PAI PAI 
			ON PAI.SEQNO=PYH.SEQNO
		LEFT JOIN PW001C26 BANK 
			ON PAI.RECEIVERBANK=BANK.CODE

Fields Definition

View Configuration (Column/Raw/Data Fields)

Rows/ Columns Specification

Row/Column	Description/ Location in APM
PIN	The PIN from the Personal Details.
Name	The Full Name from the Personal Details.
Organization	The Employment Organization from the Personal Details.
VesOrgID	The ID of the vessel linked to the payment entry in Account Dim 2.
Vessel Name	The Name of the vessel linked to the payment entry Account Dim 2.
Department	The Code of the department linked to the payment entry Account Dim 3.
Category	The category of the payment entry.
Days/Quantity	The days (Entry Field 2) of the payment entry.
Rate	The rate (Entry Field 3) of the payment entry.
Period	The period of the payment entry.
Entry Code	The Code of the payment entry.
Amount	The Amount (Entry Field 1) value of the payment entry.
Payscale Table	The payscale table of the payment entry.
Payscale Code	The payscale code of the payment entry.
Payscale Table Name	The payscale table name of the payment entry.
Payscale Code Name	The payscale code name of the payment entry.
Rank	The Name of the rank linked to the payment entry.
Payment Currency	The payment currency of the payment entry.
Payment Currency Rate	The exchange rate of the payment entry.
numorgid	The emplyment organization ID from the Personal Details.
employmentstratdate	The emplyment start date from the Personal Details.
employmentenddate	The emplyment end date from the Personal Details.

Payroll Summary (historical period)