Payroll view (24 payroll periods)
1 Description | 2 SQL Statement | 3 Field Specification
Summary | Payroll 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 24 payroll periods a year only. For 12 payroll periods, there is a different view.
SQL Statement
CREATE VIEW dbo.PW001SRV20 AS
SELECT
p01.PIN,
p01.NAME,
ORG.NAME AS 'ORG. NAME',
p03.CODE AS 'Activity Code',
c12.Text AS 'Activity Name',
p03.datefrom,
p03.todateestimated,
p03.dateto,
orgves.NAME AS Vessel,
orgdep.NAME AS Department,
orgpos.NAME AS Position,
p03.PAYSCALETABLE,
p03.PAYSCALE,
p01.sex,
p01.Nationality,
P0T.TELENO 'E-MAIL',
P0Y.PAYROLLPERIODLOCK,
p01.CLIENT NUMORGID,
p01.EMPLOYMENTSTARTDATE,
p01.EMPLOYMENTENDDATE,
Case
When P01.Transferbalance = '0' Then 'Carry Forward To Next Month'
When P01.Transferbalance = '1' Then 'Transfer To Bank Account'
When P01.Transferbalance = '2' Then 'Interpay'
When P01.Transferbalance = '3' Then 'Alpha Credit Bank'
When P01.Transferbalance = '4' Then 'Cash'
When P01.Transferbalance = '5' Then 'Citibank Direct Deposit'
When P01.Transferbalance = '6' Then 'Citibank'
When P01.Transferbalance = '7' Then 'Chase Manhattan'
When P01.Transferbalance = '8' Then 'Hellenic Bank'
When P01.Transferbalance = '9' Then 'Isabel Domestic'
When P01.Transferbalance = 'A' Then 'Isabel International'
When P01.Transferbalance = 'B' Then 'Bank Transfer'
When P01.Transferbalance = 'C' Then 'Swedish Domestic'
When P01.Transferbalance = 'D' Then 'Swedish International'
When P01.Transferbalance = 'E' Then 'Philippine Banking'
When P01.Transferbalance = 'F' Then 'Manual Bank'
When P01.Transferbalance = 'G' Then 'Zagrebancka'
When P01.Transferbalance = 'H' Then 'ING Bank'
When P01.Transferbalance = 'I' Then 'Direct Deposit E-Monee'
When P01.Transferbalance = 'J' Then 'Deutsche Bank'
When P01.Transferbalance = 'K' Then 'SACS'
When P01.Transferbalance = 'L' Then 'Hellenic Cyprus'
When P01.Transferbalance = 'M' Then 'MT 100'
When P01.Transferbalance = 'N' Then 'Citibank (PL)'
When P01.Transferbalance = '0' Then 'Agent .R'
When P01.Transferbalance = 'P' Then 'German Bank'
When P01.Transferbalance = 'Q' Then 'Trident Trust'
When P01.Transferbalance = 'R' Then 'Citibank (Asia)'
When P01.Transferbalance = 'S' Then 'PNC Bank'
When P01.Transferbalance = 'T' Then 'Chase Insight'
When P01.Transferbalance = 'U' Then 'Ocean Pay'
When P01.Transferbalance = 'V' Then 'Banco de Oro'
When P01.Transferbalance = 'W' Then 'Bank of Philippine Island'
When P01.Transferbalance = 'X' Then 'CitiDirect (Onboard)'
When P01.Transferbalance = 'Y' Then 'Metrobank Direct (PH)'
When P01.Transferbalance = 'Z' Then 'Elektron'
When P01.Transferbalance = 'A1' Then 'E-Banking (Maramut)'
When P01.Transferbalance = 'A2' Then 'RBS Direct Access'
When P01.Transferbalance = 'A3' Then 'Brazilian Banks'
When P01.Transferbalance = 'A4' Then 'NETS'
When P01.Transferbalance = 'A5' Then 'NONE'
When P01.Transferbalance = 'A6' Then 'JDP Morgan'
When P01.Transferbalance = 'A7' Then 'J. P01. Morgan Access'
When P01.Transferbalance = 'A8' Then 'Spar Nord Domestic'
When P01.Transferbalance = 'A9' Then 'Spar Nord International'
When P01.Transferbalance = 'B1' Then 'Rabobank'
When P01.Transferbalance = 'B2' Then 'Deutsche Bank'
When P01.Transferbalance = 'B4' Then 'ISO20022'
When P01.Transferbalance = 'B5' Then 'Brightwell'
End as 'BALANCE ACTION'
FROM dbo.PW001P01 p01
LEFT JOIN PWORG AS payorg
ON payorg.NUMORGID = dbo.ad_ScanOrgTreePayroll(p01.CLIENT)
LEFT JOIN PWORGCMP AS paycmp
ON paycmp.NUMORGID = dbo.ad_scanorgtree(payorg.NUMORGID, 2)
LEFT JOIN PWCMPPAY AS per
ON per.COMPANYID = paycmp.NUMORGID
AND per.[PERIOD] = paycmp.PERIODE
LEFT JOIN PW001P0Y P0Y ON P01.PIN=P0Y.PIN
JOIN PW001P03 AS p03
ON p03.PIN = P01.PIN
AND p03.DATEFROM <= per.COMPLETIONDATE
AND (ISNULL(p03.DATETO, p03.TODATEESTIMATED) >= per.PREPARATIONDATE
OR ISNULL(p03.DATETO, p03.TODATEESTIMATED) IS NULL
)
AND p03.CODE IN (SELECT t.CODE
FROM pw001c12 t
WHERE (t.OPTIONS LIKE '%S%' or t.OPTIONS LIKE '%K%')
)
AND NOT EXISTS (SELECT 1 FROM pw001p03 p03t
WHERE p03t.PIN = p03.PIN AND p03t.DATEFROM <= per.COMPLETIONDATE
AND (ISNULL(p03t.DATETO, p03t.TODATEESTIMATED) >= per.PREPARATIONDATE
OR ISNULL(p03t.DATETO, p03t.TODATEESTIMATED) IS NULL
)
AND dbo.ad_scanorgtree(p03t.NUMORGID, 3) = dbo.ad_scanorgtree(p03.NUMORGID, 3)
AND p03t.code IN (SELECT t.CODE
FROM pw001c12 t
WHERE (t.OPTIONS LIKE '%S%' or t.OPTIONS LIKE '%K%')
)
AND p03t.DATEFROM > p03.DateFrom)
JOIN PW001C12 AS c12
ON c12.CODE = p03.CODE
and c12.CODE not like 'LDC'
LEFT JOIN PWORG AS orgves
ON orgves.NUMORGID = dbo.ad_scanorgtree(p03.NUMORGID, 3)
LEFT JOIN PWORG AS orgdep
ON orgdep.NUMORGID = dbo.ad_scanorgtree(p03.NUMORGID, 4)
LEFT JOIN PWORG AS orgpos
ON orgpos.NUMORGID = p03.NUMORGID
AND orgpos.ORGTYPE = '5'
LEFT JOIN PW001P0T P0T ON ((P01.PIN = P0T.PIN) AND (P0T.TELETYPE = 6) AND (NOT P0T.TELEPRIORITY IS NULL) AND NOT EXISTS
(SELECT SEQUENCENO
FROM PW001P0T P0T2
WHERE (P0T.PIN = P0T2.PIN) AND (P0T2.TELETYPE = 6) AND ((P0T2.TELEPRIORITY < P0T.TELEPRIORITY) OR
((P0T2.TELEPRIORITY = P0T.TELEPRIORITY) AND (P0T2.SEQUENCENO < P0T.SEQUENCENO)))))
LEFT JOIN PWORG ORG ON P01.CLIENT=ORG.NUMORGID
where P03.DATEFROM = (select MAX(DATEFROM) from PW001P03 old
where old.PIN = P01.PIN
AND old.DATEFROM <= per.COMPLETIONDATE
AND (ISNULL(old.DATETO, old.TODATEESTIMATED) >= per.PREPARATIONDATE
OR ISNULL(old.DATETO, old.TODATEESTIMATED) IS NULL
)
AND old.CODE IN (SELECT t.CODE
FROM pw001c12 t
WHERE (t.OPTIONS LIKE '%S%' or t.OPTIONS LIKE '%K%')
)
AND NOT EXISTS (SELECT 1 FROM pw001p03 p03t
WHERE p03t.PIN = old.PIN AND p03t.DATEFROM <= per.COMPLETIONDATE
AND (ISNULL(p03t.DATETO, p03t.TODATEESTIMATED) >= per.PREPARATIONDATE
OR ISNULL(p03t.DATETO, p03t.TODATEESTIMATED) IS NULL
)
AND dbo.ad_scanorgtree(p03t.NUMORGID, 3) = dbo.ad_scanorgtree(old.NUMORGID, 3)
AND p03t.code IN (SELECT t.CODE
FROM pw001c12 t
WHERE (t.OPTIONS LIKE '%S%' or t.OPTIONS LIKE '%K%')
)
AND p03t.DATEFROM > old.DateFrom))
Field Specification
N/A