--CREATE VIEW dbo.PW001SRV103 AS
SELECT PYH.PIN,
P01.NAME AS 'Full Name',
P01.FIRSTNAME AS 'First Name',
P01.MIDDLENAME AS 'Middle Name',
P01.LASTNAME AS 'Last Name',
persorg.name AS Organization,
PYH.periodeused AS Period,
CONVERT(VARCHAR, PYH.ENTRYCODE) + ' ' + 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 '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 'Alternative Receiver'
ELSE 'Crew Memeber'
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 pw001paypw001pyh 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
LEFT JOIN PW001P0Y p0y
ON p0y.PIN = p01.PIN
LEFT JOIN PW001PAI PAI
ON PAI.SEQNO=PYH.SEQNO
LEFT JOIN PW001C26 BANK
ON PAI.RECEIVERBANK=BANK.CODE
|