Versions Compared

Version Old Version 5 New Version Current
Changes made by

Daria Chebotaieva (Unlicensed)

Daria Chebotaieva (Unlicensed)

Saved on

Key

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

...

Page Properties

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.

Expand
titleSQL statement
Code Block
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.

Info

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.

Info

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

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.

...