select
--CREATE VIEW dbo.PW001SRV103 AS
SELECT pay.PIN,
p01.Name,
persorg.name as P01.NAME AS 'Full Name',
P01.FIRSTNAME AS 'First Name',
P01.MIDDLENAME AS 'Middle Name',
P01.LASTNAME AS 'Last Name',
persorg.name AS Organization,
pay.accountdim2 as VesOrgIDperiodeused AS Period,
isnull(ves.name, 'N/A') as 'Vessel Name CONVERT(VARCHAR, PAY.ENTRYCODE) + ' ' + EC.TEXT AS 'Entry Code',
case when pay.ACCOUNTDIM3 is NULL or pay.ACCOUNTDIM3='' then 'N/A'
else pay.ACCOUNTDIM3
end 'Department',
isnull 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 'Generated automatically'
WHEN PAY.SOURCE = 'I' and activityseq is NULL THEN 'Imported Travel Expenses'
WHEN PAY.SOURCE = 'I' and activityseq is NOT NULL THEN 'Imported Timesheets'
ELSE 'Added manually/ Imported Excel'
END 'Entry Type',
ISNULL(CAT.NAME, 'Other') as AS 'Category',
Cast(Replace(Case when pay.CALCULATEDAMOUNT1 AS 'Amount',
CAST(
REPLACE(
CASE
WHEN pay.ENTRYFIELD2 = '' ThenTHEN '0' Else
ELSE pay.ENTRYFIELD2 End,
END,
',',
'.') as float) as
) AS FLOAT
) AS 'Days/QuantityHours',
Case When
CASE
WHEN pay.entryfield3 = '' ThenTHEN 0
Else Round(( ELSE ROUND(
(
dbo.ad_StrToFloat(
RIGHT(
pay.entryfield3,
LEN(pay.entryfield3) - PATINDEX('%[0-9]%', pay.entryfield3) + 1
)
)
),
2
)
End as 'Rate',
pay.periodeused as Period,
CONVERT(VARCHAR,PAY.ENTRYCODE) + ' ' + EC.TEXT AS 'Entry Code',
pay.calculatedamount1 'Amount' ,
pay.payscaletable as 'PayScale Table',
pay.payscalecode as 'PayScale Code',
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 pw001p03 p03 on p03.SEQUENCENO=pay.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) END AS 'Daily/Hourly Rate',
pay.currencyforpayment AS 'Payment Currency ',
pay.currencyrateforpayment 'Payment Currency Rate',
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.payscaletable AS 'PayScale Table',
pay.payscalecode AS 'PayScale Code',
payscaleinfo.Table_Name 'PayScale Table Name',
payscaleinfo.Payscale_Name 'PayScale Name',
P0Y.PAYROLLPERIODLOCK AS 'Person Locked (Y/N)',
CASE
WHEN pay.PAID is null or PAY.PAID = '' THEN 'N'
ELSE PAY.PAID
END 'Entry Paid (Y/N)',
PAY.[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'
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,
pay.accountdim2 AS VesOrgID
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
LEFT JOIN PW001P0Y p0y
ON p0y.PIN = p01.PIN
LEFT JOIN PW001PAI PAI
ON PAI.SEQNO=PAY.SEQNO
LEFT JOIN PW001C26 BANK
ON PAI.RECEIVERBANK=BANK.CODE
|
