Payroll view (12 payroll periods a year)
1 Description | 2 SQL Statement | 3 Field Specification
Summary | The view to process crew payroll. |
|---|---|
Keywords | payroll calculation |
Description
The Payroll view will select all the crew with activities that participate in the current payroll period calculations. The view is considering 12 payroll periods a year only. For 24 payroll periods, there is a different view.
SQL Statement
CREATE VIEW dbo.PW001SRV11 AS
select
p.PIN,
periode.PERIODE,
p.EMPLOYMENTSTARTDATE,
p.EMPLOYMENTENDDATE,
p.CLIENT NUMORGID,
--c43.NAME AS 'Company',
--v.NUMORGID 'VESSEL_NUMORGID',
(Select Name from pworg where NUMORGID = a.VESSEL) 'ACT VESSEL',
p.NAME,
ORGAN.NAME 'ORGANIZATION',
--p.PERSONALIDNO 'PERSONAL ID',
-- p.SRCNO 'SRC NUMBER',
--C02.GROUPNO 'RANK SORT',
(select name from pworg where orgtype = 4 and numorgid = (select NUMORGIDABOVE from pworg where orgtype = 5 and numorgid = a.NUMORGID)) 'DEPARTMENT',
(select name from pworg where orgtype = 5 and numorgid = a.NUMORGID) 'POSITION',
c02.NAME 'RANK',
dbo.ad_orgPosC02Code((select ORGCODE from pworg where orgtype = 5 and numorgid = a.NUMORGID))'RANK CODE',
p.NATIONALITY 'NATIONALITY CODE',
company.PERIODE 'PERIOD',
periode.PStart 'PERIOD START',
periode.PEnd 'PERIOD END',
a.PAYSCALETABLE 'ACT PAYSCALE TABLE',
a.PAYSCALE 'ACT PAYSCALE',
p0p.PAYSCALETABLE 'PD PAYSCALE TABLE',
p0p.PAYSCALECODE 'PD PAYSCALE',
pay.CALCULATEDBY 'CALCULATED BY',
pay.CalculateTime 'CALCULATE TIME',
/*p.BANKACCOUNTNO 'BANK ACCOUNT NO',
case when p0y.PAYSWIFTCODE = '' then p0y.RTYPERECEIVER
else p0y.PAYSWIFTCODE
end 'SWIFT',
p0y.RTYPEINTERMEDIATE 'SWIFT INTERMEDIATE',*/
Case
When P.Transferbalance = '0' Then 'Carry Forward To Next Month'
When P.Transferbalance = '1' Then 'Transfer To Bank Account'
When P.Transferbalance = '2' Then 'Interpay'
When P.Transferbalance = '3' Then 'Alpha Credit Bank'
When P.Transferbalance = '4' Then 'Cash'
When P.Transferbalance = '5' Then 'Citibank Direct Deposit'
When P.Transferbalance = '6' Then 'Citibank'
When P.Transferbalance = '7' Then 'Chase Manhattan'
When P.Transferbalance = '8' Then 'Hellenic Bank'
When P.Transferbalance = '9' Then 'Isabel Domestic'
When P.Transferbalance = 'A' Then 'Isabel International'
When P.Transferbalance = 'B' Then 'Bank Transfer'
When P.Transferbalance = 'C' Then 'Swedish Domestic'
When P.Transferbalance = 'D' Then 'Swedish International'
When P.Transferbalance = 'E' Then 'Philippine Banking'
When P.Transferbalance = 'F' Then 'Manual Bank'
When P.Transferbalance = 'G' Then 'Zagrebancka'
When P.Transferbalance = 'H' Then 'ING Bank'
When P.Transferbalance = 'I' Then 'Direct Deposit E-Monee'
When P.Transferbalance = 'J' Then 'Deutsche Bank'
When P.Transferbalance = 'K' Then 'SACS'
When P.Transferbalance = 'L' Then 'Hellenic Cyprus'
When P.Transferbalance = 'M' Then 'MT 100'
When P.Transferbalance = 'N' Then 'Citibank (PL)'
When P.Transferbalance = '0' Then 'Agent .R'
When P.Transferbalance = 'P' Then 'German Bank'
When P.Transferbalance = 'Q' Then 'Trident Trust'
When P.Transferbalance = 'R' Then 'Citibank (Asia)'
When P.Transferbalance = 'S' Then 'PNC Bank'
When P.Transferbalance = 'T' Then 'Chase Insight'
When P.Transferbalance = 'U' Then 'Ocean Pay'
When P.Transferbalance = 'V' Then 'Banco de Oro'
When P.Transferbalance = 'W' Then 'Bank of Philippine Island'
When P.Transferbalance = 'X' Then 'CitiDirect (Onboard)'
When P.Transferbalance = 'Y' Then 'Metrobank Direct (PH)'
When P.Transferbalance = 'Z' Then 'Elektron'
When P.Transferbalance = 'A1' Then 'E-Banking (Maramut)'
When P.Transferbalance = 'A2' Then 'RBS Direct Access'
When P.Transferbalance = 'A3' Then 'Brazilian Banks'
When P.Transferbalance = 'A4' Then 'NETS'
When P.Transferbalance = 'A5' Then 'NONE'
When P.Transferbalance = 'A6' Then 'JDP Morgan'
When P.Transferbalance = 'A7' Then 'J. P. Morgan Access'
When P.Transferbalance = 'A8' Then 'Spar Nord Domestic'
When P.Transferbalance = 'A9' Then 'Spar Nord International'
When P.Transferbalance = 'B1' Then 'Rabobank'
When P.Transferbalance = 'B2' Then 'Deutsche Bank'
When P.Transferbalance = 'B4' Then 'ISO20022'
When P.Transferbalance = 'B5' Then 'Brightwell'
End as 'BALANCE ACTION',
p.EMAILPAYSLIP 'EMAIL PAYSLIP',
email.TELENO 'EMAIL',
--pay.CURRENCYCODECALC 'CALCULATION CURRENCY',
pay.CURRENCYFORPAYMENT 'PAYMENT CURRENCY',
--Case When EC994.PAID is not null Then 'Y' Else 'N' End 'FUND TRANSFERRED',
--'PAID '+ CONVERT(VARCHAR,ec994.date_of_payment,103) 'PAYMENT DATE',
--orgv.name 'EC 994 VESSEL',
(select text from pw001c12 where code = a.code) 'ACTIVITY',
a.DATEFROM 'DATE FROM',
a.DATETO 'DATE TO',
a.TODATEESTIMATED 'ESTIMATED END',
datediff(day,
(Case when (select min(DATEFROM) from pw001p03 pa where pa.PIN = p.PIN
and pa.datefrom < (DATEADD(MM,1,CAST(Case when Right(Cast(company.PERIODE as varchar),2) = '00'
then left(Cast(company.PERIODE as varchar),4)+'01'
when Convert(int,Right(Cast(company.PERIODE as varchar),2)) > 12
then left(Cast(company.PERIODE as varchar),4)+'12'
else Cast(company.PERIODE as varchar(6))
end+'01' AS DATETIME))-1)
and IsNull(pa.dateto,pa.TODATEESTIMATED) >= (CAST(Case when Right(Cast(company.PERIODE as varchar),2) = '00'
then left(Cast(company.PERIODE as varchar),4)+'01'
when Convert(int,Right(Cast(company.PERIODE as varchar),2)) > 12
then left(Cast(company.PERIODE as varchar),4)+'12'
else Cast(company.PERIODE as varchar(6))
end+'01' AS DATETIME)))
< (CAST(Case when Right(Cast(company.PERIODE as varchar),2) = '00'
then left(Cast(company.PERIODE as varchar),4)+'01'
when Convert(int,Right(Cast(company.PERIODE as varchar),2)) > 12
then left(Cast(company.PERIODE as varchar),4)+'12'
else Cast(company.PERIODE as varchar(6))
end+'01' AS DATETIME))
then CAST(Case when Right(Cast(company.PERIODE as varchar),2) = '00'
then left(Cast(company.PERIODE as varchar),4)+'01'
when Convert(int,Right(Cast(company.PERIODE as varchar),2)) > 12
then left(Cast(company.PERIODE as varchar),4)+'12'
else Cast(company.PERIODE as varchar(6))
end+'01' AS DATETIME)
else (select min(DATEFROM) from pw001p03 pa where pa.PIN = p.PIN
and pa.datefrom < (DATEADD(MM,1,CAST(Case when Right(Cast(company.PERIODE as varchar),2) = '00'
then left(Cast(company.PERIODE as varchar),4)+'01'
when Convert(int,Right(Cast(company.PERIODE as varchar),2)) > 12
then left(Cast(company.PERIODE as varchar),4)+'12'
else Cast(company.PERIODE as varchar(6))
end+'01' AS DATETIME))-1)
and IsNull(pa.dateto,pa.TODATEESTIMATED) >= (CAST(Case when Right(Cast(company.PERIODE as varchar),2) = '00'
then left(Cast(company.PERIODE as varchar),4)+'01'
when Convert(int,Right(Cast(company.PERIODE as varchar),2)) > 12
then left(Cast(company.PERIODE as varchar),4)+'12'
else Cast(company.PERIODE as varchar(6))
end+'01' AS DATETIME)))
end),
(case when (select max(IsNull(pa.DATETO,pa.TODATEESTIMATED)) from pw001p03 pa where pa.PIN = p.PIN
and pa.datefrom < (DATEADD(MM,1,CAST(Case when Right(Cast(company.PERIODE as varchar),2) = '00'
then left(Cast(company.PERIODE as varchar),4)+'01'
when Convert(int,Right(Cast(company.PERIODE as varchar),2)) > 12
then left(Cast(company.PERIODE as varchar),4)+'12'
else Cast(company.PERIODE as varchar(6))
end+'01' AS DATETIME))-1)
and IsNull(pa.dateto,pa.TODATEESTIMATED) >= (CAST(Case when Right(Cast(company.PERIODE as varchar),2) = '00'
then left(Cast(company.PERIODE as varchar),4)+'01'
when Convert(int,Right(Cast(company.PERIODE as varchar),2)) > 12
then left(Cast(company.PERIODE as varchar),4)+'12'
else Cast(company.PERIODE as varchar(6))
end+'01' AS DATETIME)))
> (DATEADD(MM,1,CAST(Case when Right(Cast(company.PERIODE as varchar),2) = '00'
then left(Cast(company.PERIODE as varchar),4)+'01'
when Convert(int,Right(Cast(company.PERIODE as varchar),2)) > 12
then left(Cast(company.PERIODE as varchar),4)+'12'
else Cast(company.PERIODE as varchar(6))
end+'01' AS DATETIME))-1)
then (DATEADD(MM,1,CAST(Case when Right(Cast(company.PERIODE as varchar),2) = '00'
then left(Cast(company.PERIODE as varchar),4)+'01'
when Convert(int,Right(Cast(company.PERIODE as varchar),2)) > 12
then left(Cast(company.PERIODE as varchar),4)+'12'
else Cast(company.PERIODE as varchar(6))
end+'01' AS DATETIME))-1)
else (select max(IsNull(pa.DATETO,pa.TODATEESTIMATED)) from pw001p03 pa where pa.PIN = p.PIN
and pa.datefrom < (DATEADD(MM,1,CAST(Case when Right(Cast(company.PERIODE as varchar),2) = '00'
then left(Cast(company.PERIODE as varchar),4)+'01'
when Convert(int,Right(Cast(company.PERIODE as varchar),2)) > 12
then left(Cast(company.PERIODE as varchar),4)+'12'
else Cast(company.PERIODE as varchar(6))
end+'01' AS DATETIME))-1)
and IsNull(pa.dateto,pa.TODATEESTIMATED) >= (CAST(Case when Right(Cast(company.PERIODE as varchar),2) = '00'
then left(Cast(company.PERIODE as varchar),4)+'01'
when Convert(int,Right(Cast(company.PERIODE as varchar),2)) > 12
then left(Cast(company.PERIODE as varchar),4)+'12'
else Cast(company.PERIODE as varchar(6))
end+'01' AS DATETIME)))
end)
)+1 'ACTIVITY DAYS'
From pw001p01 p
join (Select NUMORGID,PERIODE,
CAST(Case when Right(Cast(PERIODE as varchar),2) = '00'
then left(Cast(PERIODE as varchar),4)+'01'
when Convert(int,Right(Cast(PERIODE as varchar),2)) > 12
then left(Cast(PERIODE as varchar),4)+'12'
else Cast(PERIODE as varchar(6))
end+'01' AS DATETIME) as 'PStart',
DATEADD(MM,1,CAST(Case when Right(Cast(PERIODE as varchar),2) = '00'
then left(Cast(PERIODE as varchar),4)+'01'
when Convert(int,Right(Cast(PERIODE as varchar),2)) > 12
then left(Cast(PERIODE as varchar),4)+'12'
else Cast(PERIODE as varchar(6))
end+'01' AS DATETIME))-1 as 'PEnd'
FROM PWORGCMP where PAYROLLENABLED = 'Y'
Union All
Select NUMORGID,PERIODE,
CAST(Case when Right(Cast(PERIODE as varchar),2) = '00'
then left(Cast(PERIODE as varchar),4)+'01'
when Convert(int,Right(Cast(PERIODE as varchar),2)) > 12
then left(Cast(PERIODE as varchar),4)+'12'
else Cast(PERIODE as varchar(6))
end+'01' AS DATETIME) as 'PStart',
DATEADD(MM,1,CAST(Case when Right(Cast(PERIODE as varchar),2) = '00'
then left(Cast(PERIODE as varchar),4)+'01'
when Convert(int,Right(Cast(PERIODE as varchar),2)) > 12
then left(Cast(PERIODE as varchar),4)+'12'
else Cast(PERIODE as varchar(6))
end+'01' AS DATETIME))-1 as 'PEnd'
FROM PWORGVES where PAYROLLENABLED = 'Y') periode on p.CLIENT = periode.NUMORGID
JOIN PWORG ORGAN ON p.CLIENT = ORGAN.NUMORGID
Join PWORGCMP company on p.CLIENT = company.NUMORGID
left join PW001P0P p0p on p0p.PIN = p.PIN and p0p.PNUMBER = 'A' and p0p.PAYSCALECODE is not null and p0p.PAYSCALECODE <> ''
LEFT JOIN PW001PAY pay on pay.PIN = p.PIN and pay.CalculateTime IS NOT NULL and pay.PERIODEUSED = company.PERIODE
and NOT EXISTS(SELECT 1 FROM PW001PAY PAY2 WHERE PAY.PIN=PAY2.PIN and PAY2.PERIODEUSED = company.PERIODE
AND (PAY2.CALCULATETIME > PAY.CALCULATETIME
OR (PAY2.CALCULATETIME=PAY.CALCULATETIME AND PAY2.SEQNO>PAY.SEQNO)))
LEFT JOIN PW001P1R p1r5 ON p1r5.PIN = p.PIN AND p1r5.RATENO = 5
LEFT JOIN pw001c02 c02 on p.rank = c02.code
LEFT JOIN PW001P0Y P0Y ON p.PIN = P0Y.PIN
LEFT JOIN PW001P0T email ON email.PIN = p.PIN AND email.TELETYPE = 6
AND NOT EXISTS (SELECT 1 FROM pw001p0t t WHERE t.PIN=email.PIN
AND t.TELETYPE=email.TELETYPE
AND (t.TELEPRIORITY<email.TELEPRIORITY
OR (t.TELEPRIORITY=email.TELEPRIORITY AND t.SEQUENCENO>email.SEQUENCENO)))
--Activity matching payroll period
join PW001P03 a on a.PIN = p.PIN and a.CODE in (select CODE from PW001C12 where TRANSACTIONCODE <> '')
and a.datefrom <= periode.PEnd
and IsNull(a.dateto,a.TODATEESTIMATED) >= periode.PStart
--payscale table and code from activity
join PWPSC000 PT on a.PAYSCALETABLE = pt.REGULATIVECODE and (PT.INCLUDEPLANNEDACTIVITY = 'Y' or (PT.INCLUDEPLANNEDACTIVITY <> 'Y' and a.PLANNED<>'Y'))
join PWPSC001 PS on a.PAYSCALE = PS.PAYSCALECODE and PS.SEQNO = pt.SEQNO
left join PWORGVES v on a.VESSEL = v.NUMORGID
LEFT JOIN PW001C43 c43 on c43.CODE = P.COSTPLACE
--where isnull(pay.seqno,a.SEQUENCENO) is not null
--check if the activity code incleuded on pascale table or code into payroll
where ((/*pt.TRANSACTIONCODES <> '' and*/ (CHARINDEX(a.CODE,pt.ACTIVITYCODES,0)>0 or pt.ACTIVITYCODES is null))
or (/*ps.TRANSACTIONCODES <> '' and*/ (CHARINDEX(a.CODE,PS.ACTIVITYCODES,0)>0 or ps.ACTIVITYCODES is null)))Field Specification
N/A