SELECT pay.PIN,
p01.Name
--CREATE VIEW dbo.PW001SRV103 AS
SELECT pay.PIN,
persorgP01.nameNAME AS 'Full OrganizationName',
payP01.accountdim2FIRSTNAME AS AS VesOrgID'First Name',
ISNULL(ves.name, 'N/A')P01.MIDDLENAME AS 'VesselMiddle Name',
CASEP01.LASTNAME AS 'Last Name',
WHEN pay.ACCOUNTDIM3 IS NULL OR paypersorg.ACCOUNTDIM3name = '' THEN 'N/A' AS Organization,
ELSE pay.ACCOUNTDIM3periodeused END 'Department', AS Period,
ISNULLCONVERT(CAT.NAMEVARCHAR, 'Other'PAY.ENTRYCODE) + AS' 'Category', + EC.TEXT AS 'Entry Code',
CAST( case WHEN pay.autoentry = 'Y'
OR pay.ENTRYCODE like '%994'
REPLACE(
CASE
WHEN pay.ENTRYFIELD2 = '' THEN '0'
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',
ELSE pay.ENTRYFIELD2
ISNULL(CAT.NAME, 'Other') AS 'Category',
pay.CALCULATEDAMOUNT1 AS 'Amount',
END, CAST(
REPLACE(
',', CASE
'.' ) AS FLOAT WHEN pay.ENTRYFIELD2 = '' THEN '0'
) ELSE pay.ENTRYFIELD2
AS Days, CASE END,
WHEN pay.entryfield3 = ',',
THEN 0 ELSE'.'
ROUND( ) AS FLOAT
) ( dbo.ad_StrToFloat(AS 'Days/Hours',
CASE
WHEN pay.entryfield3 = '' THEN 0
RIGHT( ELSE ROUND(
pay.entryfield3,(
dbo.ad_StrToFloat(
LEN(pay.entryfield3) - PATINDEX('%[0-9]%', pay.entryfield3) + 1 RIGHT(
) pay.entryfield3,
) ), LEN(pay.entryfield3) - PATINDEX('%[0-9]%', pay.entryfield3) + 1
2 )
END )
AS 'Daily Rate', pay.periodeused ),
AS Period, CONVERT(VARCHAR, PAY.ENTRYCODE) + ' ' + EC.TEXT AS2
'Entry Code', CASE )
WHEN PAY.ENTRYCODE BETWEENEND 30 AND 39 THEN pay.CALCULATEDAMOUNT10 ELSE pay.CALCULATEDAMOUNT1 AS END 'Amount'Daily/Hourly Rate',
pay.payscaletablecurrencyforpayment AS 'PayScalePayment Currency Table',
pay.payscalecodecurrencyrateforpayment 'Payment Currency Rate',
ASUS.USER_NAME 'PayScaleCreated CodeBy',
payscaleinfo.Table_Namepay.CreateTime 'PayScaleCreate Table NameTime',
payscaleinfo.Payscale_Namepay.CALCULATEDBY 'PayScaleCalculated NameBy',
pay.rankcalculatetime '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
AS RANK, END 'RECEIVER Country',
CASE WHEN PAI.PAYNAME = '' and PAI.PAYPOSTCODE = '' THEN payP01.currencyforpaymentPOSTCODE
AS 'Payment Currency ', ELSE PAI.PAYPOSTCODE
pay.currencyrateforpaymentEND 'PaymentRECEIVER CurrencyPost RateCode',
CASE WHEN p01PAI.clientPAYNAME = '' and PAI.PAYPOSTPLACE = '' THEN P01.POSTPLACE
numorgid,
ELSE PAI.PAYPOSTPLACE
END 'RECEIVER Post p01.employmentstartdatePlace',
p01.employmentenddate
FROM client pw001pay pay LEFT JOIN pw001p01 p01 numorgid,
ON p01.pinemploymentstartdate,
= pay.pin p01.employmentenddate,
LEFT JOIN pworgpay.accountdim2 ves AS VesOrgID
ONFROM ves.numorgid =pw001pay pay.accountdim2
LEFT JOIN pyety001pw001p01 ecp01
ON ecp01.entrycodepin = pay.entrycodepin
LEFT JOIN pworg persorgves
ON persorgves.numorgid = p01pay.clientaccountdim2
LEFT JOIN PW001C117pyety001 CATec
ON ec.CATEGORYentrycode = CATpay.SEQUENCENOentrycode
LEFT JOIN pworg persorg
AND pay.ENTRYCODE = ec.ENTRYCODE ON persorg.numorgid = p01.client
--left join pw001p03 p03LEFT on p03.SEQUENCENO=pay.ACTIVITYSEQ
JOIN PW001C117 CAT
--left join pw001c12 c12ON on c12ec.CODECATEGORY =p03 CAT.CODESEQUENCENO
AND pay.ENTRYCODE --LEFT JOIN dbo.PWORG DEP ON DEP.NUMORGID = dbo.ad_scanorgtree(P03.NUMORGID, 4)
= ec.ENTRYCODE
LEFT JOIN (
--LEFT JOIN dbo.PWORG POS ON POS.NUMORGID = dbo.ad_scanorgtree(P03.NUMORGID, 5) SELECT C0.REGULATIVECODE AS 'Table_Code',
C0.REGULATIVENAME LEFT JOIN (AS 'Table_Name',
SELECT C0C1.REGULATIVECODEPAYSCALECODE AS 'TablePayscale_Code',
C0C1.REGULATIVENAMEPAYSCALENAME AS 'TablePayscale_Name',
FROM PWPSC000 C0
C1.PAYSCALECODE AS 'Payscale_Code', LEFT JOIN PWPSC001 C1
C1.PAYSCALENAME AS 'Payscale_Name' FROM PWPSC000ON C0 C1.SEQNO = C0.SEQNO
) payscaleinfo
LEFT JOIN PWPSC001 C1 ON payscaleinfo.Table_Code = pay.PAYSCALETABLE
AND payscaleinfo.Payscale_Code = pay.PAYSCALECODE
LEFT JOIN PWSECURITY_USERS ONUS on C1US.SEQNO USERID= C0PAY.SEQNOCREATEDBY
LEFT JOIN PW001P0Y p0y
) payscaleinfo ON p0y.PIN = p01.PIN
LEFT JOIN PW001PAI PAI
ON payscaleinfo.Table_Code = pay.PAYSCALETABLE
AND payscaleinfo.Payscale_Code = pay.PAYSCALECODEPAI.SEQNO=PAY.SEQNO
LEFT JOIN PW001C26 BANK
ON PAI.RECEIVERBANK=BANK.CODE
|
