/
Income-based Pension Scheme
Income-based Pension Scheme
- Janette Ilunio
- Daria Chebotaieva
- Sigrid Kviteberg
Owned by Janette Ilunio, created
1 Description | 2 SQl Statement | 3 Other
Summary | The view shows persons born after 1970 without the tick for Incomebasedpension |
|---|---|
Keywords | Pension |
Description
The crew list view shows all the crew who was born after 1970 and incomebasedpensionscheme = 'Y'.
SQl Statement
SELECT
p01.PIN,
p01.NAME,
p01.LASTNAME,
p01.FIRSTNAME,
p01.MIDDLENAME,
p01.PERSONALIDNO,
p01.NATIONALITY,
p01.SEX AS GENDER,
p01.BIRTHDATE,
DATEDIFF(YEAR, p01.BIRTHDATE, GETDATE()) - CASE WHEN 100 * MONTH(GETDATE()) + DAY(GETDATE()) < 100 * MONTH(p01.birthdate) + DAY(p01.birthdate) THEN 1
ELSE 0
END AS AGE,
ORG.NAME AS ORGANIZATION,
p01.ADDRESS1,
p01.ADDRESS2,
p01.ADDRESS3,
telephone.TELENO AS 'HOME PHONE',
mobile.TELENO AS 'MOBILE',
email.TELENO AS EMAIL,
residencecountry.COUNTRYNAME AS 'ADDRESS COUNTRY',
c32.TEXT AS 'CONTRACT TYPE',
p01.CONTRACTSTARTDATE AS 'CONTRACT START',
p01.CONTRACTEXPIRYDATE AS 'CONTRACT END',
P03.DATEFROM AS 'ACTIVITY START',
ISNULL(P03.DATETO,P03.TODATEESTIMATED) AS 'ACTIVITY END',
DEP.NAME AS DEPARTMENT,
POS.NAME AS POSITION, pass.PASSPORTNO,
pass.PASSPORTEXPIRYDATE AS 'PASS EXPIRY',
P03.RANK, VSL.NAME AS VESSEL,
DEP.NUMORGID AS department_numorgid,
VSL.NUMORGID AS VESSEL_NUMORGID,
p01.CLIENT AS numorgid,
p01.EMPLOYMENTSTARTDATE,
p01.EMPLOYMENTENDDATE
FROM PW001P01 AS p01
LEFT OUTER JOIN PWORG AS ORG ON p01.CLIENT = ORG.NUMORGID
LEFT OUTER JOIN PW001C32 AS c32 ON c32.CODE = p01.CONTRACTTYPE
INNER JOIN PW001P03 AS P03 ON p01.PIN = P03.PIN AND (P03.DATETO IS NULL OR P03.DATETO >= GETDATE()) AND P03.DATEFROM <= GETDATE() AND P03.PLANNED <> 'Y'
INNER JOIN PW001C12 AS c12 ON c12.CODE = P03.CODE AND c12.OPTIONS LIKE '%S%'
LEFT OUTER JOIN PWROT_SHIFT_ACTIVITIES AS psa ON psa.ACTIVITIES = P03.SEQUENCENO
LEFT OUTER JOIN PWROT_ACTIVE_SHIFT AS pas ON pas.SEQUENCENO = psa.SEQUENCENO
INNER JOIN PWORG AS VSL ON VSL.NUMORGID = dbo.ad_scanorgtree(P03.NUMORGID, 3)
LEFT OUTER JOIN PWORG AS DEP ON DEP.NUMORGID = dbo.ad_scanorgtree(P03.NUMORGID, 4)
LEFT OUTER JOIN PWORG AS POS ON POS.NUMORGID = dbo.ad_scanorgtree(P03.NUMORGID, 5)
LEFT OUTER JOIN PWORGVESCAB AS PCAB ON PCAB.CABINID = P03.cabinid
LEFT OUTER JOIN PWCCMCHANGECREWLIST AS cclj ON cclj.NEXTACT = P03.SEQUENCENO
LEFT OUTER JOIN PWORGVESACT AS vaj ON vaj.SEQNO = cclj.SEQNO
LEFT OUTER JOIN PWPORT AS portj ON portj.PORTCODE = vaj.PLACEFROM
LEFT OUTER JOIN PWCCMCHANGECREWLIST AS ccll ON ccll.CURRENTACT = P03.SEQUENCENO
LEFT OUTER JOIN PWORGVESACT AS val ON val.SEQNO = ccll.SEQNO
LEFT OUTER JOIN PWPORT AS portl ON portl.PORTCODE = val.PLACEFROM
LEFT OUTER JOIN PW001P0T AS mobile ON mobile.PIN = p01.PIN AND
mobile.TELETYPE = 3 AND NOT EXISTS
(SELECT 1 AS Expr1 FROM PW001P0T AS t WHERE ((PIN = mobile.PIN) AND
(TELETYPE = mobile.TELETYPE) AND
(TELEPRIORITY < mobile.TELEPRIORITY)) OR
((PIN = mobile.PIN) AND
(TELETYPE = mobile.TELETYPE) AND
(TELEPRIORITY = mobile.TELEPRIORITY)) AND
(SEQUENCENO > mobile.SEQUENCENO))
LEFT OUTER JOIN PW001P0T AS telephone ON telephone.PIN = p01.PIN AND
telephone.TELETYPE = 2 AND NOT EXISTS
(SELECT 1 AS Expr1 FROM PW001P0T AS t WHERE ((PIN = telephone.PIN) AND
(TELETYPE = telephone.TELETYPE) AND
(TELEPRIORITY < telephone.TELEPRIORITY)) OR
((PIN = telephone.PIN) AND
(TELETYPE = telephone.TELETYPE) AND
(TELEPRIORITY = telephone.TELEPRIORITY)) AND
(SEQUENCENO > telephone.SEQUENCENO))
LEFT OUTER JOIN PW001P0T AS email ON email.PIN = p01.PIN AND
email.TELETYPE = 6 AND NOT EXISTS
(SELECT 1 AS Expr1 FROM PW001P0T AS t WHERE ((PIN = email.PIN) AND
(TELETYPE = email.TELETYPE) AND
(TELEPRIORITY < email.TELEPRIORITY)) OR
((PIN = email.PIN) AND
(TELETYPE = email.TELETYPE) AND
(TELEPRIORITY = email.TELEPRIORITY)) AND
(SEQUENCENO > email.SEQUENCENO))
LEFT OUTER JOIN PWCOUNTRY AS residencecountry ON residencecountry.COUNTRYCODE = p01.ADDRESS_COUNTRY
LEFT OUTER JOIN PW001P1P AS pass ON pass.PIN = p01.PIN AND pass.CODE = 'PP'
LEFT OUTER JOIN PW001C02 AS C02 ON C02.CODE = dbo.ad_orgPosC02Code(POS.ORGCODE)
WHERE (p01.PIN IN (SELECT PIN FROM PW001P0Y WHERE (IncomebasedPensionScheme = 'Y'))) AND
(p01.BIRTHDATE >= CONVERT(DATETIME, '1970-01-01 00:00:00', 102))Other
N/A