...
| Expand |
|---|
|
| Code Block |
|---|
SELECT
(select top '1'1 PIn from PW001P01) as [PIN],
(select top '1'1 NUMORGID from PWORG) as [NUMORGID],
'1' as [EMPLOYMENTSTARTDATE],
'1' as [EMPLOYMENTENDDATE ],
C0.REGULATIVECODE as 'Table'[Table Code],
C0.REGULATIVENAME as[Table Table_Name],
C1.PAYSCALECODE as[Payscale 'Payscale_Code'],
C1.PAYSCALENAME as[Payscale 'Payscale_Name'],
convert CONVERT(varcharVARCHAR, LN.Line) + ' ' + LN.NAME as[Rate 'Rate_Line'],
--Cl.Col as '[Seniority_Column' Column],
Cl.Seniority [Seniority Value],
C0.CURRENCYCODE as 'Currency',
Isnull(TRY_CONVERT(Decimal [Currency],
CASE WHEN C1R.Rate iS NULL or C1R.Rate='' then ISNULL(TRY_CAST(REPLACE(C0R.Formula, ',', '.') AS DECIMAL(10,2),C1R.Rate), '0.00')
asWHEN [Rate],
CASE when (C1R(C1R.Rate iS NULL or C1R.Rate='') and (C0R.Formula is NULL or C1RC0R.Formula = '') then convert (Decimal(10,2), THEN '0.00')
ELSE TRY_CONVERT(DecimalCAST(REPLACE(C1R.Rate, ',', '.') AS DECIMAL(10, 2),C1R.Rate)
END Formula
from [Rate]
FROM PWPSC000 C0 --payscale table properties table
leftLEFT joinJOIN PWPSC001 C1 on --payscale code properties table
ON C1.SEQNO = C0.SEQNO
left join
LEFT JOIN PWPSC_RATES_LINES LN on --payscale rate lines list
ON LN.SEQNO = C0.SEQNO
left join
LEFT JOIN PWPSC_RATES_COLUMNS CL on CL.SEQNO=C0.SEQNO
left join --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 on --payscale rate value table
ON C1R.SeqNo =C0 C1.SEQNO and
AND LN.LineId = C1R.LineId
and AND CL.ColId = C1R.ColId
and 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.
...
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.Export to Excel
You can export the view into excel.
...
