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',
CAST(+ ' ' + EC.TEXT AS 'Entry Code',
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'
WHEN PYH.SOURCE = 'I' and activityseq is NULL THEN 'Imported Travel Expenses'
WHEN PYH.SOURCE = 'I' and activityseq is NOT NULL THEN 'Imported Timesheets'
ELSE 'Added manually'
END 'Entry Type',
ISNULL(CAT.NAME, 'Other') AS 'Category',
PYH.CALCULATEDAMOUNT1 AS 'Amount',
CAST(
REPLACE(
CASE
WHEN PYH.ENTRYFIELD2 = '' THEN '0'
ELSE PYH.ENTRYFIELD2
END,
',',
'.'
) AS FLOAT
) AS 'Days/Hours',
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/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',
/************************************************************
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 'Credit/Debit Card (ShipMoney)'
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'
REPLACE( WHEN PAI.BALANCEACTION = 'U' THEN 'Ocean Pay'
CASE WHEN pyhPAI.ENTRYFIELD2BALANCEACTION = 'V' THEN '0'Banco de Oro'
ELSEWHEN pyh.ENTRYFIELD2
PAI.BALANCEACTION = 'W' THEN 'Bank of Philippine Island'
END, WHEN PAI.BALANCEACTION = 'X' THEN ',CitiDirect (Onboard)',
'.' WHEN PAI.BALANCEACTION = 'Y' )THEN AS'Metrobank FLOATDirect (PH)'
) WHEN PAI.BALANCEACTION = 'Z' AS Days,THEN 'Elektron'
CASE WHEN pyhPAI.entryfield3BALANCEACTION = 'A1' THEN 0
'E-Banking (Maramut)'
ELSE ROUND( WHEN PAI.BALANCEACTION = 'A2' THEN 'RBS Direct Access'
( WHEN PAI.BALANCEACTION = dbo.ad_StrToFloat(
'A3' THEN 'Brazilian Banks'
WHEN PAI.BALANCEACTION = 'A4' RIGHT(THEN 'NETS'
WHEN PAI.BALANCEACTION = 'A5' THEN 'NONE'
pyh.entryfield3, WHEN PAI.BALANCEACTION = 'A6' THEN 'JDP Morgan'
LEN(pyh.entryfield3) - PATINDEX('%[0-9]%', pyh.entryfield3) + 1 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'
2 WHEN PAI.BALANCEACTION = 'B1' THEN 'Rabobank'
) END WHEN PAI.BALANCEACTION = 'B2' THEN 'Deutsche Bank'
WHEN PAI.BALANCEACTION = 'B3' THEN 'Berenberg Bank'
AS 'Daily Rate', pyh.periodeused AS Period,WHEN PAI.BALANCEACTION = 'B4' THEN 'ISO20022'
CONVERT(VARCHAR, pyh.ENTRYCODE) + ' ' + EC.TEXT AS 'Entry Code', CASE WHEN PAI.BALANCEACTION = 'B5' THEN 'Brightwell'
else WHEN pyhPAI.ENTRYCODEBALANCEACTION
BETWEEN 30 AND 39 THEN pyh.CALCULATEDAMOUNT10 ELSEEND pyh.CALCULATEDAMOUNT1 AS END'PAYMENT 'AmountMETHOD',
CASE WHEN PAI.PAYNAME <> '' pyh.payscaletablethen 'Alternative Receiver'
ELSE 'Crew Memeber'
ASEND 'Pay Scale TableRECEIVER',
pyh.payscalecode AS 'Pay Scale Code',
payscaleinfo.Table_Name 'PayScale Table Name',
payscaleinfo.Payscale_Name 'PayScalePAI.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 pyhPAI.rankPAYNAME = '' and (PAI.ADDRESS1+ ' ' + PAI.ADDRESS2+ ' ' + PAI.ADDRESS3) = '' THEN (P01.ADDRESS1+ ' AS' RANK,
pyh.currencyforpayment AS 'Currency for Payment',
pyh.currencyrateforpayment 'Currency Rate for Payment',
p01.client + 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
numorgid,END 'RECEIVER Country',
CASE WHEN p01PAI.employmentstartdate,PAYNAME = '' and PAI.PAYPOSTCODE = '' THEN p01.employmentenddate
FROM P01.POSTCODE
pw001pyh pyh ELSE LEFTPAI.PAYPOSTCODE
JOIN pw001p01 p01 END 'RECEIVER Post Code',
CASE WHEN PAI.PAYNAME = '' ONand p01PAI.pinPAYPOSTPLACE = '' THEN pyhP01.pinPOSTPLACE
LEFT JOINELSE pworgPAI.PAYPOSTPLACE
ves END 'RECEIVER Post Place',
ON vesp01.numorgidclient = pyh.accountdim2 LEFT JOIN pyety001 ec numorgid,
ON ecp01.entrycodeemploymentstartdate,
= pyh.entrycode p01.employmentenddate,
LEFT JOIN pworgPYH.accountdim2 persorg AS VesOrgID
ONFROM persorg.numorgid =pw001pyh p01.clientPYH
LEFT JOIN PW001C117pw001p01 CATp01
ON ecp01.CATEGORYpin = CATPYH.SEQUENCENO
pin
LEFT JOIN pworg ves
AND pyh.ENTRYCODE = ec.ENTRYCODE ON ves.numorgid = PYH.accountdim2
--left join pw001p03 p03 on p03.SEQUENCENO=pyh.ACTIVITYSEQ
LEFT JOIN pyety001 ec
ON --left join pw001c12 c12 on c12.CODE=p03.CODEec.entrycode = PYH.entrycode
LEFT JOIN pworg persorg
--LEFT JOIN dbo.PWORG DEP ON DEPpersorg.NUMORGIDnumorgid = dbo.ad_scanorgtree(P03.NUMORGID, 4)p01.client
LEFT JOIN PW001C117 CAT
--LEFT JOIN dbo.PWORG POS ON POSec.NUMORGIDCATEGORY = dbo.ad_scanorgtree(P03.NUMORGID, 5)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'
AS 'Payscale_Name'
FROM PWPSC000 C0
LEFT JOIN PWPSC001 C1
FROM 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 ) payscaleinfoon US.USERID=PYH.CREATEDBY
LEFT JOIN PW001P0Y p0y
ON payscaleinfop0y.Table_CodePIN = pyhp01.PAYSCALETABLEPIN
LEFT JOIN PW001PAI PAI
ON PAI.SEQNO=PYH.SEQNO
LEFT JOIN PW001C26 BANK
AND payscaleinfo.Payscale_Code = pyh.PAYSCALECODE ON PAI.RECEIVERBANK=BANK.CODE
|
