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