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+ ' ' + AS RANK,
pay.currencyforpayment AS 'Payment Currency P01.ADDRESS3)
ELSE (PAI.ADDRESS1+ ' ' + PAI.ADDRESS2+ ' ' + PAI.ADDRESS3)
END 'RECEIVER Address',
CASE WHEN pay.currencyrateforpaymentPAI.PAYNAME = 'Payment Currency Rate', and PAI.paycountry = '' THEN p01P01.clientADDRESS_COUNTRY
ELSE PAI.paycountry
END numorgid'RECEIVER Country',
CASE WHEN p01.employmentstartdate,
p01.employmentenddate
FROM pw001pay payPAI.PAYNAME = '' and PAI.PAYPOSTCODE = '' THEN P01.POSTCODE
ELSE LEFT JOIN pw001p01 p01PAI.PAYPOSTCODE
END 'RECEIVER Post Code',
CASE WHEN PAI.PAYNAME = ON'' and p01PAI.pinPAYPOSTPLACE = '' THEN payP01.pinPOSTPLACE
LEFT JOINELSE pworgPAI.PAYPOSTPLACE
ves END 'RECEIVER Post Place',
ON vesp01.numorgidclient = pay.accountdim2 LEFT JOIN pyety001 ec numorgid,
ON ecp01.entrycodeemploymentstartdate,
= pay.entrycode p01.employmentenddate,
LEFT JOIN pworgpay.accountdim2 persorg AS VesOrgID
ONFROM persorg.numorgid =pw001pay p01.clientpay
LEFT JOIN PW001C117pw001p01 CATp01
ON ecp01.CATEGORYpin = CAT.SEQUENCENO
AND pay.ENTRYCODE = ec.ENTRYCODEpay.pin
LEFT JOIN pworg ves
--left join pw001p03 p03ON on p03ves.SEQUENCENOnumorgid = pay.ACTIVITYSEQaccountdim2
LEFT JOIN pyety001 ec
--left join pw001c12 c12 on c12.CODE=p03.CODE ON ec.entrycode = pay.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 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.PAYSCALETABLEPAYSCALECODE
LEFT JOIN PWSECURITY_USERS US on US.USERID=PAY.CREATEDBY
LEFT JOIN PW001P0Y p0y
ON AND payscaleinfop0y.Payscale_CodePIN = pay.PAYSCALECODE
WHERE p01.CLIENT NOT IN (10012602)p01.PIN
LEFT JOIN PW001PAI PAI
ON PAI.SEQNO=PAY.SEQNO
LEFT JOIN PW001C26 BANK
ON PAI.RECEIVERBANK=BANK.CODE
|
