--IMPLADC-583 06/25/2024
--CREATE VIEW dbo.PW001SRV103
--AS
SELECT pyh.PIN,
p01.Name,
persorg.name AS Organization,
pyh.accountdim2 AS VesOrgID,
ISNULL(ves.name, 'N/A') AS 'Vessel Name',
CASE
WHEN pyh.ACCOUNTDIM3 IS NULL OR pyh.ACCOUNTDIM3 = '' THEN 'N/A'
ELSE pyh.ACCOUNTDIM3
END 'Department',
ISNULL(CAT.NAME, 'Other') AS 'Category',
CAST(
REPLACE(
CASE
WHEN pyh.ENTRYFIELD2 = '' THEN '0'
ELSE pyh.ENTRYFIELD2
END,
',',
'.'
) AS FLOAT
) AS Days,
CASE
WHEN pyh.entryfield3 = '' THEN 0
ELSE ROUND(
(
dbo.ad_StrToFloat(
RIGHT(
pyh.entryfield3,
LEN(pyh.entryfield3) - PATINDEX('%[0-9]%', pyh.entryfield3) + 1
)
)
),
2
)
END AS 'Daily Rate',
pyh.periodeused AS Period,
CONVERT(VARCHAR, pyh.ENTRYCODE) + ' ' + EC.TEXT AS 'Entry Code',
US.USER_NAME 'Created By',
pyh.CreateTime 'Create Time',
pyh.CALCULATEDBY 'Calculated By',
pyh.calculatetime 'Calculation Time',
/************************************************************
CASE
WHEN pyh.ENTRYCODE BETWEEN 30 AND 39 THEN pyh.CALCULATEDAMOUNT10
ELSE pyh.CALCULATEDAMOUNT1
END 'Amount',
************************************************************/
pyh.CALCULATEDAMOUNT1 AS 'Amount',
case when pyh.autoentry = 'Y'
OR pyh.ENTRYCODE like '%994'
OR pyh.ENTRYCODE like '%995'
OR pyh.ENTRYCODE like '%996'
OR pyh.ENTRYCODE like '%998'
OR pyh.ENTRYCODE like '%999' THEN 'AutomaticGenerated automatically'
ELSE 'ManualAdded manually'
END 'Entry Type',
pyh.payscaletable AS 'PayScale Table',
pyh.payscalecode AS 'PayScale Code',
payscaleinfo.Table_Name 'PayScale Table Name',
payscaleinfo.Payscale_Name 'PayScale Name',
pyh.rank AS RANK,
pyh.currencyforpayment AS 'Payment Currency ',
pyh.currencyrateforpayment 'Payment Currency Rate',
p01.client numorgid,
p01.employmentstartdate,
p01.employmentenddate
FROM pw001pyh pyh
LEFT JOIN pw001p01 p01
ON p01.pin = pyh.pin
LEFT JOIN pworg ves
ON ves.numorgid = pyh.accountdim2
LEFT JOIN pyety001 ec
ON ec.entrycode = pyh.entrycode
LEFT JOIN pworg persorg
ON persorg.numorgid = p01.client
LEFT JOIN PW001C117 CAT
ON ec.CATEGORY = CAT.SEQUENCENO
AND pyh.ENTRYCODE = ec.ENTRYCODE
LEFT JOIN (
SELECT C0.REGULATIVECODE AS 'Table_Code',
C0.REGULATIVENAME AS 'Table_Name',
C1.PAYSCALECODE AS 'Payscale_Code',
C1.PAYSCALENAME AS 'Payscale_Name'
FROM PWPSC000 C0
LEFT JOIN PWPSC001 C1
ON C1.SEQNO = C0.SEQNO
) payscaleinfo
ON payscaleinfo.Table_Code = pyh.PAYSCALETABLE
AND payscaleinfo.Payscale_Code = pyh.PAYSCALECODE
LEFT JOIN PWSECURITY_USERS US on US.USERID=pyh.CREATEDBY
|