Skip to end of banner
Go to start of banner

Payroll Summary (current period)

Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Version History

« Previous Version 8 Next »

Summary

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

Keywords

Payroll, Payments

Category

Analytical View

Description

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

Main Data Selection

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

Setup and Configuration

SQL statement

 SQL statement
--IMPLADC-583 06/25/2024
--CREATE VIEW dbo.PW001SRV103
--AS

SELECT pay.PIN,
       p01.Name,
       persorg.name               AS Organization,
       pay.accountdim2            AS VesOrgID,
       ISNULL(ves.name, 'N/A')    AS 'Vessel Name',
       CASE 
            WHEN pay.ACCOUNTDIM3 IS NULL OR pay.ACCOUNTDIM3 = '' THEN 'N/A'
            ELSE pay.ACCOUNTDIM3
       END 'Department',
       ISNULL(CAT.NAME, 'Other')  AS 'Category',
       CAST(
           REPLACE(
               CASE 
                    WHEN pay.ENTRYFIELD2 = '' THEN '0'
                    ELSE pay.ENTRYFIELD2
               END,
               ',',
               '.'
           ) AS FLOAT
       )                          AS Days,

       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 Rate',
       pay.periodeused            AS Period,
       CONVERT(VARCHAR, PAY.ENTRYCODE) + ' ' + EC.TEXT AS 'Entry Code',
	   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.CALCULATEDAMOUNT1 AS 'Amount',
       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'
			ELSE 'Added manually'
       END 'Entry Type',
       pay.payscaletable          AS 'PayScale Table',
       pay.payscalecode           AS 'PayScale Code',
       payscaleinfo.Table_Name 'PayScale Table Name',
       payscaleinfo.Payscale_Name 'PayScale Name',
       pay.rank                   AS RANK,
       pay.currencyforpayment     AS 'Payment Currency ',
       pay.currencyrateforpayment 'Payment Currency Rate',
       p01.client                    numorgid,
       p01.employmentstartdate,
       p01.employmentenddate
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

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.

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

  • No labels