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

Summary	The view gives an overview of all the payscale rates added to the system.
Keywords	Payroll, Payscales
Category	Analytical View

Description

The view gives an overview of all the payscale rates added to the system.

The view only works if you have static yearly rates that don’t change based on a person’s seniority.

Main Data Selection

All the payscale rates that added in column A.

Setup and Configuration

SQL statement

When copying the script make sure you replace the PIN, NUMORGID, EMPLOYMENTSTARTDATE, EMPLOYMENTENDDAT value with some existing value in your database. PIN, NUMORGID, EMPLOYMENTSTARTDATE, EMPLOYMENTENDDATE are mandatory fields for the Analytical Module, so we can’t skip it even if it doesn’t make sense for this view.

select 
	'80001' as PIN, 
	'80001' as NUMORGID, 
	'80001' as EMPLOYMENTSTARTDATE, 
	'80001' 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 '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
where cl.col=1 --and LN.Line='8'

Fields Definition

View Configuration (Column/Raw/Data Fields)

Rows/ Columns Specification

Row/Column	Description/ Location in APM

Row/Column	Description/ Location in APM
PIN	PIN is a mandatory field for the Analytical Module, so we can’t skip it even if it doesn’t make sense for this view.
NUMORGID	NUMORGID is a mandatory field for the Analytical Module, so we can’t skip it even if it doesn’t make sense for this view.
EMPLOYMENTSTARTDATE	EMPLOYMENTSTARTDATE is a mandatory field for the Analytical Module, so we can’t skip it even if it doesn’t make sense for this view.
EMPLOYMENTENDDATE	EMPLOYMENTENDDATE is a mandatory field for the Analytical Module, so we can’t skip it even if it doesn’t make sense for this view.
Table_Name	The name of the Payscale Table.
Payscale_Name	The name of the Payscale Code.
Rate_Line	The rate line + text.
Rate	The rate value (both manually inputted amount and calculated with formula).
Formula	The formula used for rate calculation.

Export to Excel

You can export the view into Excel.

Reports, Views and Similar

Payscale Rates Overview v.2