SELECT pyh.PIN,
p01.Name
--CREATE VIEW dbo.PW001SRV103 AS
SELECT PYH.PIN,
persorgP01.nameNAME AS 'Full OrganizationName',
pyhP01.accountdim2FIRSTNAME AS AS VesOrgID'First Name',
ISNULL(ves.name, 'N/A')P01.MIDDLENAME AS 'VesselMiddle Name',
CASEP01.LASTNAME AS 'Last Name',
WHEN pyh.ACCOUNTDIM3 IS NULL OR pyhpersorg.ACCOUNTDIM3name = '' THEN 'N/A' AS Organization,
ELSE pyhPYH.ACCOUNTDIM3periodeused END 'Department', AS Period,
ISNULLCONVERT(CAT.NAMEVARCHAR, 'Other'PYH.ENTRYCODE) + AS' 'Category', + EC.TEXT AS 'Entry Code',
CAST( case when PYH.autoentry = 'Y'
OR PYH.ENTRYCODE like '%994'
REPLACE(
CASE
OR PYH.ENTRYCODE like '%995'
OR PYH.ENTRYCODE like '%996'
OR PYH.ENTRYCODE like '%998'
OR PYH.ENTRYCODE like '%999' THEN 'Generated automatically'
ELSE 'Added manually'
END 'Entry Type',
WHEN pyh.ENTRYFIELD2 = '' THEN ISNULL(CAT.NAME, '0Other') AS 'Category',
PYH.CALCULATEDAMOUNT1 AS 'Amount',
CAST(
ELSE pyh.ENTRYFIELD2 REPLACE(
END, CASE
',', WHEN PYH.ENTRYFIELD2 = '.' THEN '0'
) AS FLOAT ) ELSE PYH.ENTRYFIELD2
END,
AS Days, ',',
CASE WHEN pyh'.entryfield3'
= '' THEN 0 ) AS FLOAT
ELSE ROUND( ) ( AS 'Days/Hours',
CASE
dbo.ad_StrToFloat( WHEN PYH.entryfield3 = '' THEN 0
ELSE ROUND(
RIGHT( (
pyh.entryfield3, dbo.ad_StrToFloat(
LENRIGHT(pyh.entryfield3)
- PATINDEX('%[0-9]%', pyh.entryfield3) + 1 PYH.entryfield3,
) ) LEN(PYH.entryfield3) - PATINDEX('%[0-9]%', PYH.entryfield3) + 1
), )
2 ) )
END ),
AS 'Daily Rate', pyh.periodeused 2
AS Period, CONVERT(VARCHAR, pyh.ENTRYCODE) + ' ')
+ EC.TEXT AS 'Entry Code', END 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',
AS 'Daily/Hourly Rate',
PYH.currencyforpayment AS 'Payment Currency ',
PYH.currencyrateforpayment 'Payment Currency Rate',
US.USER_NAME 'Created By',
PYH.CreateTime 'Create Time',
PYH.CALCULATEDBY 'Calculated By',
PYH.calculatetime 'Calculation Time',
/************************************************************/
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 'Generated automatically'
ELSE 'Added 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
CASE
WHEN PYH.ENTRYCODE BETWEEN 30 AND 39 THEN PYH.CALCULATEDAMOUNT10
ELSE PYH.CALCULATEDAMOUNT1
END 'Amount',
************************************************************/
PYH.payscaletable AS 'PayScale Table',
PYH.payscalecode AS 'PayScale Code',
payscaleinfo.Table_Name 'PayScale Table Name',
payscaleinfo.Payscale_Name 'PayScale Name',
P0Y.PAYROLLPERIODLOCK AS 'Person Locked (Y/N)',
CASE
WHEN PYH.PAID is null or PYH.PAID = '' THEN 'N'
ELSE PYH.PAID
END 'Entry Paid (Y/N)',
PYH.[TEXT] AS Comments,
CASE
WHEN PAI.BALANCEACTION = '0' THEN 'Carry Forward To Next Month'
WHEN PAI.BALANCEACTION = '1' THEN 'Transfer To Bank Account'
WHEN PAI.BALANCEACTION = '2' THEN 'Interpay'
WHEN PAI.BALANCEACTION = '3' THEN 'Alpha Credit Bank'
WHEN PAI.BALANCEACTION = '4' THEN 'Cash'
WHEN PAI.BALANCEACTION = '5' THEN 'Citibank Direct Deposit'
WHEN PAI.BALANCEACTION = '6' THEN 'Citibank'
WHEN PAI.BALANCEACTION = '7' THEN 'Chase Manhattan'
WHEN PAI.BALANCEACTION = '8' THEN 'Hellenic Bank'
WHEN PAI.BALANCEACTION = '9' THEN 'Isabel Domestic'
WHEN PAI.BALANCEACTION = 'A' THEN 'Isabel International'
WHEN PAI.BALANCEACTION = 'B' THEN 'Bank Transfer'
WHEN PAI.BALANCEACTION = 'C' THEN 'Swedish Domestic'
WHEN PAI.BALANCEACTION = 'D' THEN 'Swedish International'
WHEN PAI.BALANCEACTION = 'E' THEN 'Philippine Banking'
WHEN PAI.BALANCEACTION = 'F' THEN 'Manual Bank'
WHEN PAI.BALANCEACTION = 'G' THEN 'Zagrebancka'
WHEN PAI.BALANCEACTION = 'H' THEN 'ING Bank'
WHEN PAI.BALANCEACTION = 'I' THEN 'Direct Deposit E-Monee'
WHEN PAI.BALANCEACTION = 'J' THEN 'Deutsche Bank'
WHEN PAI.BALANCEACTION = 'K' THEN 'SACS'
WHEN PAI.BALANCEACTION = 'L' THEN 'Hellenic Cyprus'
WHEN PAI.BALANCEACTION = 'M' THEN 'MT 100'
WHEN PAI.BALANCEACTION = 'N' THEN 'Citibank (PL)'
WHEN PAI.BALANCEACTION = '0' THEN 'Agent .R'
WHEN PAI.BALANCEACTION = 'P' THEN 'German Bank'
WHEN PAI.BALANCEACTION = 'Q' THEN 'Trident Trust'
WHEN PAI.BALANCEACTION = 'R' THEN 'Citibank (Asia)'
WHEN PAI.BALANCEACTION = 'S' THEN 'PNC Bank'
WHEN PAI.BALANCEACTION = 'T' THEN 'Chase Insight'
WHEN PAI.BALANCEACTION = 'U' THEN 'Ocean Pay'
WHEN PAI.BALANCEACTION = 'V' THEN 'Banco de Oro'
WHEN PAI.BALANCEACTION = 'W' THEN 'Bank of Philippine Island'
WHEN PAI.BALANCEACTION = 'X' THEN 'CitiDirect (Onboard)'
WHEN PAI.BALANCEACTION = 'Y' THEN 'Metrobank Direct (PH)'
WHEN PAI.BALANCEACTION = 'Z' THEN 'Elektron'
WHEN PAI.BALANCEACTION = 'A1' THEN 'E-Banking (Maramut)'
WHEN PAI.BALANCEACTION = 'A2' THEN 'RBS Direct Access'
WHEN PAI.BALANCEACTION = 'A3' THEN 'Brazilian Banks'
WHEN PAI.BALANCEACTION = 'A4' THEN 'NETS'
WHEN PAI.BALANCEACTION = 'A5' THEN 'NONE'
WHEN PAI.BALANCEACTION = 'A6' THEN 'JDP Morgan'
WHEN PAI.BALANCEACTION = 'A7' THEN 'J. P. Morgan Access'
WHEN PAI.BALANCEACTION = 'A8' THEN 'Spar Nord Domestic'
WHEN PAI.BALANCEACTION = 'A9' THEN 'Spar Nord International'
WHEN PAI.BALANCEACTION = 'B1' THEN 'Rabobank'
WHEN PAI.BALANCEACTION = 'B2' THEN 'Deutsche Bank'
WHEN PAI.BALANCEACTION = 'B3' THEN 'Berenberg Bank'
WHEN PAI.BALANCEACTION = 'B4' THEN 'ISO20022'
WHEN PAI.BALANCEACTION = 'B5' THEN 'Brightwell'
else PAI.BALANCEACTION
END AS 'PAYMENT METHOD',
CASE WHEN PAI.PAYNAME = '' then 'Crew Memeber'
ELSE 'Alternative Receiver'
END 'RECEIVER',
PAI.ACCOUNTNO 'RECEIVER IBAN/ Bank Account No',
BANK.NAME 'RECEIVER Bank',
PAI.SWIFT,
CASE WHEN PAI.PAYNAME = '' then p01.name
ELSE PAI.PAYNAME
END 'RECEIVER Name',
CASE WHEN PAI.PAYNAME = '' and (PAI.ADDRESS1+ ' ' + PAI.ADDRESS2+ ' ' + PAI.ADDRESS3) = '' THEN (P01.ADDRESS1+ ' ' + P01.ADDRESS2+ ' ' + P01.ADDRESS3)
ELSE (PAI.ADDRESS1+ ' ' + PAI.ADDRESS2+ ' ' + PAI.ADDRESS3)
END 'RECEIVER Address',
CASE WHEN PAI.PAYNAME = '' and PAI.paycountry = '' THEN P01.ADDRESS_COUNTRY
ELSE PAI.paycountry
END 'RECEIVER Country',
CASE WHEN PAI.PAYNAME = '' and PAI.PAYPOSTCODE = '' THEN P01.POSTCODE
ELSE PAI.PAYPOSTCODE
END 'RECEIVER Post Code',
CASE WHEN PAI.PAYNAME = '' and PAI.PAYPOSTPLACE = '' THEN P01.POSTPLACE
ELSE PAI.PAYPOSTPLACE
END 'RECEIVER Post Place',
p01.client numorgid,
p01.employmentstartdate,
p01.employmentenddate,
PYH.accountdim2 AS VesOrgID
FROM pw001pyhpw001pay pyhPYH
LEFT JOIN pw001p01 p01
ON p01.pin = pyhPYH.pin
LEFT JOIN pworg ves
ON ves.numorgid = pyhPYH.accountdim2
LEFT JOIN pyety001 ec
ON ec.entrycode = pyhPYH.entrycode
LEFT JOIN pworg persorg
ON persorg.numorgid = p01.client
LEFT JOIN PW001C117 CAT
ON ec.CATEGORY = CAT.SEQUENCENO
AND pyhPYH.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
PWPSC000 C0 ON C1.SEQNO = C0.SEQNO
LEFT JOIN PWPSC001) C1payscaleinfo
ON payscaleinfo.Table_Code = PYH.PAYSCALETABLE
ON AND C1payscaleinfo.SEQNOPayscale_Code = C0PYH.SEQNOPAYSCALECODE
LEFT JOIN PWSECURITY_USERS US on US.USERID=PYH.CREATEDBY
LEFT JOIN ) payscaleinfoPW001P0Y p0y
ON payscaleinfop0y.Table_CodePIN = pyhp01.PAYSCALETABLEPIN
LEFT JOIN PW001PAI PAI
AND payscaleinfo.Payscale_Code = pyh.PAYSCALECODE ON PAI.SEQNO=PYH.SEQNO
LEFT JOIN PW001C26 PWSECURITY_USERSBANK US
on US.USERID=pyh.CREATEDBY ON PAI.RECEIVERBANK=BANK.CODE
|