Versions Compared

Version Old Version 3 New Version Current
Changes made by

Arvin John Salandanan

Daria Chebotaieva

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

Page Properties

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

Expand
titleSQL statement
Code Block
SELECT pyh.PIN,
       p01.Name
--CREATE VIEW dbo.PW001SRV103 AS

SELECT PYH.PIN,
       persorgP01.nameNAME               AS 'Full OrganizationName',
       pyhP01.accountdim2FIRSTNAME          AS  AS VesOrgID'First Name',
       ISNULL(ves.name, 'N/A')P01.MIDDLENAME         AS 'VesselMiddle Name',
       CASEP01.LASTNAME           AS 'Last Name',
  WHEN pyh.ACCOUNTDIM3 IS NULL OR pyhpersorg.ACCOUNTDIM3name = '' THEN 'N/A'           AS Organization,
	  ELSE pyhPYH.ACCOUNTDIM3periodeused        END 'Department',   AS Period,
	   ISNULLCONVERT(CAT.NAMEVARCHAR, 'Other'PYH.ENTRYCODE)  AS 'Category',
       CAST(+ ' ' + 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'
			WHEN PYH.SOURCE = 'I' and activityseq is NULL THEN 'Imported Travel Expenses'
			WHEN PYH.SOURCE = 'I' and activityseq is NOT NULL THEN 'Imported Timesheets'
			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 'Credit/Debit Card (ShipMoney)'
                       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'
                REPLACE(       WHEN PAI.BALANCEACTION = 'T' THEN 'Chase Insight'
  CASE                      WHEN pyhPAI.ENTRYFIELD2BALANCEACTION = 'U' THEN '0'Ocean Pay'
                       ELSEWHEN pyh.ENTRYFIELD2
 PAI.BALANCEACTION = 'V' THEN 'Banco de Oro'
             END,          WHEN PAI.BALANCEACTION = 'W' THEN  ',',
 'Bank of Philippine Island'
             '.'          WHEN PAI.BALANCEACTION )= AS'X' FLOATTHEN 'CitiDirect (Onboard)'
     )                  WHEN PAI.BALANCEACTION = 'Y' THEN 'Metrobank Direct (PH)'
AS Days,        CASE              WHEN pyhPAI.entryfield3BALANCEACTION = 'Z' THEN 0'Elektron'
            ELSE ROUND(          WHEN PAI.BALANCEACTION = 'A1' THEN 'E-Banking (Maramut)'
     (                  WHEN PAI.BALANCEACTION = 'A2' THEN 'RBS Direct   dbo.ad_StrToFloat(Access'
                       WHEN PAI.BALANCEACTION = 'A3' THEN 'Brazilian Banks'
           RIGHT(            WHEN PAI.BALANCEACTION = 'A4' THEN 'NETS'
                pyh.entryfield3,       WHEN PAI.BALANCEACTION = 'A5' THEN 'NONE'
                     LEN(pyh.entryfield3) - PATINDEX('%[0-9]%', pyh.entryfield3) + 1
   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'
    2                  ) WHEN PAI.BALANCEACTION = 'B1' THEN 'Rabobank'
 END                      WHEN PAI.BALANCEACTION AS= 'Daily RateB2', THEN 'Deutsche Bank'
					   WHEN pyhPAI.periodeusedBALANCEACTION = 'B3' THEN 'Berenberg Bank'
      AS Period,        CONVERT(VARCHAR, pyh.ENTRYCODE) + ' ' + EC.TEXT AS 'Entry Code',    WHEN PAI.BALANCEACTION = 'B4' CASETHEN 'ISO20022'
            WHEN pyh.ENTRYCODE BETWEEN 30 AND 39 THEN pyh.CALCULATEDAMOUNT10    WHEN PAI.BALANCEACTION = 'B5' THEN 'Brightwell'
					   ELSEelse pyhPAI.CALCULATEDAMOUNT1BALANCEACTION
       END 'Amount',        pyh.payscaletable  END        AS 'PayPAYMENT Scale TableMETHOD', 
	   CASE WHEN PAI.PAYNAME <> pyh.payscalecode'' then 'Alternative Receiver' 
			ELSE 'Crew Memeber'
	   ASEND 'Pay Scale Code',
       payscaleinfo.Table_Name 'PayScale Table Name',
       payscaleinfo.Payscale_Name 'PayScale Name',
       pyh.rank                   AS RANK,
       pyh.currencyforpayment     AS 'Currency for Payment',
       pyh.currencyrateforpayment 'Currency Rate for Payment',
       p01.client                    numorgid,
       p01.employmentstartdate,'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 p01PAI.employmentenddatepaycountry FROM
	  pw001pyh pyhEND 'RECEIVER Country',
	   CASE WHEN LEFTPAI.PAYNAME JOIN= pw001p01'' p01and PAI.PAYPOSTCODE = '' THEN P01.POSTCODE 
	       ON ELSE p01PAI.pinPAYPOSTCODE
=	 pyh.pin  END 'RECEIVER Post Code',
	  LEFT JOINCASE pworgWHEN vesPAI.PAYNAME = '' and PAI.PAYPOSTPLACE = '' THEN P01.POSTPLACE
	     ON  ves.numorgid =ELSE pyhPAI.accountdim2PAYPOSTPLACE
	   END 'RECEIVER Post Place',
LEFT JOIN pyety001 ec    p01.client         ON  ec.entrycode  = pyh.entrycode      numorgid,
 LEFT JOIN pworg persorg   p01.employmentstartdate,
       p01.employmentenddate,
	 ON  persorgPYH.numorgidaccountdim2 = p01.client        LEFT JOIN PW001C117AS CATVesOrgID
FROM   pw001pyh PYH
       ONLEFT JOIN ec.CATEGORYpw001p01 =p01
CAT.SEQUENCENO            ON AND pyhp01.ENTRYCODEpin = ec.ENTRYCODE PYH.pin
       LEFT JOIN pworg ves
         --left join pw001p03 p03ON on p03ves.SEQUENCENOnumorgid =pyh.ACTIVITYSEQ PYH.accountdim2
       LEFT JOIN pyety001 ec
        --left  join pw001c12 c12ON on c12ec.CODEentrycode =p03 PYH.CODEentrycode
       LEFT JOIN pworg persorg
      --LEFT  JOIN dbo.PWORG  DEP ON  DEPpersorg.NUMORGIDnumorgid = dbo.ad_scanorgtree(P03.NUMORGID, 4)p01.client
       LEFT JOIN PW001C117 CAT
        --LEFT JOIN dbo.PWORG POS ON  POSec.NUMORGIDCATEGORY = dbo.ad_scanorgtree(P03.NUMORGID, 5)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 ON  C1.SEQNOpayscaleinfo.Payscale_Code = C0PYH.SEQNOPAYSCALECODE 
		LEFT JOIN PWSECURITY_USERS US on US.USERID=PYH.CREATEDBY
		LEFT JOIN PW001P0Y   ) payscaleinfop0y
            ON  payscaleinfop0y.Table_CodePIN = pyhp01.PAYSCALETABLEPIN
		LEFT JOIN PW001PAI PAI 
			ON PAI.SEQNO=PYH.SEQNO
		LEFT JOIN PW001C26 BANK 
AND payscaleinfo.Payscale_Code = pyh.PAYSCALECODE			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.

Other