--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 'Automatic'
ELSE 'Manual'
END 'Entry Type',
pyh.payscaletable AS 'PayPayScale Scale Table',
pyh.payscalecode AS 'Pay ScalePayScale Code',
payscaleinfo.Table_Name 'PayScale Table Name',
payscaleinfo.Payscale_Name 'PayScale Name',
pyh.rank AS RANK,
pyh.currencyforpayment AS 'Payment Currency for Payment',
pyh.currencyrateforpayment 'Payment Currency Rate for Payment',
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 pw001p03 p03 on p03.SEQUENCENO=pyh.ACTIVITYSEQ
--left join pw001c12
c12 on c12.CODE=p03.CODE --LEFT JOIN dbo.PWORG DEP ON DEP.NUMORGID = dbo.ad_scanorgtree(P03.NUMORGID, 4)
--LEFT JOIN dbo.PWORG POS ON POS.NUMORGID = dbo.ad_scanorgtree(P03.NUMORGID, 5)
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
|