1 Description | 2 Main Data Selection | 3 Setup and Configuration | 4 Rows/ Columns Specification | 5 Export to Excel

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.

SELECT 
	(select top 1 PIn from PW001P01) as [PIN],
	(select top 1 NUMORGID from PWORG) as [NUMORGID],
	'1' [EMPLOYMENTSTARTDATE],
	'1' [EMPLOYMENTENDDATE],
	C0.REGULATIVECODE [Table Code],
	C0.REGULATIVENAME [Table Name],
	C1.PAYSCALECODE [Payscale Code],
	C1.PAYSCALENAME [Payscale Name],
	CONVERT(VARCHAR, LN.Line) + ' ' + LN.NAME [Rate Line],
	--Cl.Col as [Seniority Column],
	Cl.Seniority [Seniority Value],
	C0.CURRENCYCODE [Currency],
	CASE WHEN C1R.Rate iS NULL or C1R.Rate='' then  ISNULL(TRY_CAST(REPLACE(C0R.Formula, ',', '.') AS DECIMAL(10,2)), '0.00')
		 WHEN (C1R.Rate iS NULL or C1R.Rate='') and (C0R.Formula is NULL or C0R.Formula='') THEN '0.00'
		ELSE  TRY_CAST(REPLACE(C1R.Rate, ',', '.') AS DECIMAL(10, 2)) 
	END [Rate]                    
FROM   PWPSC000 C0								--payscale table properties table
       LEFT JOIN PWPSC001 C1					--payscale code properties table
            ON  C1.SEQNO = C0.SEQNO
       LEFT JOIN PWPSC_RATES_LINES LN			--payscale rate lines list
            ON  LN.SEQNO = C0.SEQNO
       LEFT JOIN PWPSC_RATES_COLUMNS CL			--payscale columns list
            ON  CL.SEQNO = C0.SEQNO
       LEFT JOIN PWPSC000_RATES C0R				--payscale rate value table
            ON  C0R.SeqNo = C0.SEQNO
            AND LN.LineId = C0R.LineId
            AND CL.ColId = C0R.ColId
       LEFT JOIN PWPSC001_RATES C1R				--payscale rate value table
            ON  C1R.SeqNo = C1.SEQNO 
            AND LN.LineId = C1R.LineId
            AND CL.ColId = C1R.ColId
            AND C1.ID = C1R.ID
WHERE   C0.options not like '%O%' 
--and cl.Seniority=0
--and C0.REGULATIVECODE='FC-1OFF'  
--and C1.PAYSCALECODE='FIROFF'

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

Row/Column	Description/ Location in APM

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.

Export to Excel

You can export the view into excel.

Adonis HR Reports, Views and Similar

Payscale Rates Overview