Skip to end of banner
Go to start of banner

Payscale Rates Overview

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 2 Next »

Summary

This analytical view gives an overview of the rates from all the Payscales.

Keywords

Payscale, Rates

Category

Analytical View

Description

This analytical view gives an overview of the rates from all the Payscales.

Main Data Selection

Payscale rates from all the payscales.

Setup and Configuration

SQL statement

When using the view remember to replace the '1' value in lines PIN, NUMORGID, EMPLOYMENTSTARTDATE, EMPLOYMENTENDDATE with any existing PIN in your system. This is a work-around since all the analytical views should be by default person-based and PIN, NUMORGID, EMPLOYMENTSTARTDATE, and EMPLOYMENTENDDATE are mandatory fields.

 SQL statement
select 
	'1' as PIN, 
	'1' as NUMORGID, 
	'1' as EMPLOYMENTSTARTDATE, 
	'1' as EMPLOYMENTENDDATE ,
	C0.REGULATIVECODE as 'Table',
	C0.REGULATIVENAME as Table_Name,
	C1.PAYSCALECODE as 'Payscale_Code',
	C1.PAYSCALENAME as 'Payscale_Name',
	convert (varchar, LN.Line) + ' ' + LN.NAME as 'Rate_Line',
	Cl.Col as 'Seniority_Column',
	C0.CURRENCYCODE as 'Currency',
	Isnull(TRY_CONVERT(Decimal(10,2),C1R.Rate),0.00) as [Rate],
	CASE when (C1R.Formula is NULL or C1R.Formula = '') then convert (Decimal(10,2), '0.00')
		ELSE TRY_CONVERT(Decimal(10,2),C1R.Rate)
	END Formula	
from PWPSC000 C0 
left join PWPSC001 C1 on C1.SEQNO=C0.SEQNO
left join PWPSC_RATES_LINES LN on  LN.SEQNO=C0.SEQNO
left join PWPSC_RATES_COLUMNS CL on CL.SEQNO=C0.SEQNO
left join PWPSC001_RATES C1R on C1R.SeqNo=C0.SEQNO and LN.LineId=C1R.LineId and CL.ColId=C1R.ColId and C1.ID=C1R.ID

Fields Definition

Fields Definition in General Codes > Datab Migning Code setup.

The Seniority_Column can be hidden if you don’t split any rates depending on seniority.

View Configuration (Column/Raw/Data Fields)

The screenshot of the configured view from the Analytical Module.

The Seniority_Column can be removed if you don’t split any rates depending on seniority.

Rows/ Columns Specification

	'1' as PIN, 
	'1' as NUMORGID, 
	'1' as EMPLOYMENTSTARTDATE, 
	'1' as EMPLOYMENTENDDATE ,
	C0.REGULATIVECODE as 'Table',
	C0.REGULATIVENAME as Table_Name,
	C1.PAYSCALECODE as 'Payscale_Code',
	C1.PAYSCALENAME as 'Payscale_Name',
	convert (varchar, LN.Line) + ' ' + LN.NAME as 'Rate_Line',
	Cl.Col as 'Seniority_Column',
	C0.CURRENCYCODE as 'Currency',
	Isnull(TRY_CONVERT(Decimal(10,2),C1R.Rate),0.00) as [Rate],
	CASE when (C1R.Formula is NULL or C1R.Formula = '') then convert (Decimal(10,2), '0.00')
		ELSE TRY_CONVERT(Decimal(10,2),C1R.Rate)
	END Formula

Row/Column

Description/ Location in APM

Table Code

The Code of the Payscale Table.

Table_Name

The Name of the Payscale Table.

Payscale_Code

The Code of the Payscale.

Payscale_Name

The Name of the Payscale.

Rate_Line

The rate line from the payscale.

Seniority_Column

The seniority column from the payscale.

Currency

The currency from the payscale.

Formula

The rate value from the payscale.

  • No labels