--IMPLADC-583 06/25/2024
--CREATE VIEW dbo.PW001SRV103 --AS
SELECT pay.PIN,
p01P01.Name,NAME persorg.name AS 'Full Name',
AS Organization,P01.FIRSTNAME pay.accountdim2 AS 'First Name',
ASP01.MIDDLENAME VesOrgID, ISNULL(ves.name,AS 'N/A') Middle Name',
AS 'Vessel Name', P01.LASTNAME CASE AS 'Last Name',
WHEN paypersorg.ACCOUNTDIM3name IS NULL OR pay.ACCOUNTDIM3 = '' 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
OR pay.ENTRYCODE like '%995'
OR pay.ENTRYCODE like '%996'
OR pay.ENTRYCODE like '%998'
OR pay.ENTRYCODE like '%999' THEN 'Generated automatically'
ELSE 'Added manually'
END 'Entry Type',
WHEN pay.ENTRYFIELD2 = '' THEN ISNULL(CAT.NAME, '0Other') AS 'Category',
pay.CALCULATEDAMOUNT1 AS 'Amount',
CAST(
ELSE pay.ENTRYFIELD2 REPLACE(
END, CASE
',', WHEN pay.ENTRYFIELD2 = '.' THEN '0'
) AS FLOAT ) ELSE pay.ENTRYFIELD2
END,
AS Days, ',',
CASE '.'
WHEN pay.entryfield3 = '' THEN 0 ) AS FLOAT
ELSE ROUND( ) ( AS 'Days/Hours',
CASE
dbo.ad_StrToFloat( WHEN pay.entryfield3 = '' THEN 0
ELSE ROUND(
RIGHT( (
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 2
AS Period, CONVERT(VARCHAR, PAY.ENTRYCODE) + ' ' + EC.TEXT AS)
'Entry Code', US.USER_NAME 'Created By',END pay.CreateTime 'Create Time', pay.CALCULATEDBY 'Calculated By AS 'Daily/Hourly Rate',
pay.calculatetimecurrencyforpayment AS 'CalculationPayment Currency Time',
pay.currencyrateforpayment /************************************************************
CASE
WHEN PAY.ENTRYCODE BETWEEN 30 AND 39 THEN pay.CALCULATEDAMOUNT10
ELSE pay.CALCULATEDAMOUNT1
END 'Amount',
'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 '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 'Crew Memeber'
ELSE 'Alternative Receiver'
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',
pay.CALCULATEDAMOUNT1 AS 'Amount',CASE WHEN PAI.PAYNAME = '' and (PAI.ADDRESS1+ ' case' when+ payPAI.autoentryADDRESS2+ =' 'Y' OR+ payPAI.ENTRYCODEADDRESS3) like= '%994' ORTHEN pay.ENTRYCODE like(P01.ADDRESS1+ '%995 ' OR+ payP01.ENTRYCODEADDRESS2+ like' '%996' OR+ pay.ENTRYCODE like '%998' OR pay.ENTRYCODE like '%999' THEN 'Generated automatically'
ELSE 'Added manually'
END 'Entry Type',
pay.payscaletable AS 'PayScale Table',
pay.payscalecode AS 'PayScale Code',
payscaleinfo.Table_Name 'PayScale Table Name',
payscaleinfo.Payscale_Name 'PayScale Name',
pay.rank 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 ASPost RANKPlace',
payp01.currencyforpaymentclient AS 'Payment Currency ', pay.currencyrateforpayment 'Payment Currency Rate'numorgid,
p01.clientemploymentstartdate,
p01.employmentenddate,
numorgid,
pay.accountdim2 p01.employmentstartdate, AS p01.employmentenddateVesOrgID
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
PWPSC000 C0 ON C1.SEQNO = C0.SEQNO
LEFT JOIN PWPSC001) C1payscaleinfo
ON payscaleinfo.Table_Code = pay.PAYSCALETABLE
ON AND C1payscaleinfo.SEQNOPayscale_Code = C0pay.SEQNOPAYSCALECODE
LEFT JOIN PWSECURITY_USERS US on US.USERID=PAY.CREATEDBY
LEFT JOIN ) payscaleinfoPW001P0Y p0y
ON payscaleinfop0y.Table_CodePIN = payp01.PAYSCALETABLEPIN
LEFT JOIN PW001PAI PAI
AND payscaleinfo.Payscale_Code = pay.PAYSCALECODE ON PAI.SEQNO=PAY.SEQNO
LEFT JOIN PW001C26 PWSECURITY_USERSBANK US
on US.USERID=PAY.CREATEDBY ON PAI.RECEIVERBANK=BANK.CODE
|