(ShipMoney)'
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))) |
