CREATE VIEW dbo.PW001SRV18 AS
SELECT P01.PIN PIN,
P01.NAME NAME,
P01.FIRSTNAME FIRSTNAME,
P01.LASTNAME LASTNAME,
P01.NATIONALITY NATIONALITY,
C02.NAME RANK,
ISNULL(ISNULL(CA.DATEFROM,HA.DATEFROM),PA.DATEFROM) as 'ACTIVITY START',
ISNULL(ISNULL(CA.TODATEESTIMATED,HA.DATETO),PA.TODATEESTIMATED) as 'ACTIVITY END',
CASE
WHEN CA.SEQUENCENO IS NOT NULL THEN 'C'
WHEN HA.SEQUENCENO IS NOT NULL THEN 'H'
WHEN PA.SEQUENCENO IS NOT NULL THEN 'P'
ELSE 'N'
END as 'ACTIVITY',
POS.[NAME] POSITION,
DEP.[NAME] DEPARTMENT,
VES.[NAME] VESSEL,
CASE
WHEN ISNULL(ISNULL(CA.SEQUENCENO,HA.SEQUENCENO),PA.SEQUENCENO) IS NULL THEN 0.00
ELSE dbo.ad_AccruedLeave(P01.PIN,CONVERT(DATE,GETDATE()),'Y')
END as 'LEAVE BALANCE TODAY',
CASE
WHEN ISNULL(ISNULL(CA.SEQUENCENO,HA.SEQUENCENO),PA.SEQUENCENO) IS NULL THEN 0.00
ELSE dbo.ad_AccruedLeave(P01.PIN,ISNULL(ISNULL(CA.TODATEESTIMATED,HA.DATETO),PA.TODATEESTIMATED),'Y')
END as 'LEAVE BALANCE SIGN-OFF',
P01.CLIENT NUMORGID,
ORG.NAME ORGNAME,
P01.BIRTHDATE BIRTHDATE,
P01.PERSONALIDNO,
P01.TELEPHONE PHONE,
P01.EMPLOYMENTSTARTDATE,
P01.EMPLOYMENTENDDATE,
PC.COUNTRYNAME AS ADDRESSCOUNTRY,
P0T.TELENO EMAIL
FROM
PW001P01 P01
LEFT JOIN PW001P0P P0P ON ((P01.PIN=P0P.PIN) AND (P0P.PNUMBER='A'))
LEFT JOIN PW001C02 C02 ON P0P.POSITIONID=C02.CODE
LEFT JOIN PWORG ORG ON P01.CLIENT=ORG.NUMORGID
LEFT JOIN PW001C32 C32 ON P01.CONTRACTTYPE=C32.CODE
LEFT JOIN PWCOUNTRY PC ON P01.ADDRESS_COUNTRY = PC.COUNTRYCODE
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 PW001P03 CA
ON CA.CODE in (SELECT t.CODE FROM PW001C12 t WHERE t.OPTIONS LIKE '%S%')
AND CA.PIN = P01.PIN AND Isnull(CA.PLANNED,'N') != 'Y'
AND CA.DATETO IS NULL
LEFT JOIN PW001P03 HA
ON HA.CODE in (SELECT t.CODE FROM PW001C12 t WHERE t.OPTIONS LIKE '%S%')
AND HA.PIN = P01.PIN AND Isnull(HA.PLANNED,'N') != 'Y'
AND CA.SEQUENCENO IS NULL
AND HA.DATETO IS NOT NULL
AND HA.DATETO = (SELECT MAX(t.DATETO) FROM PW001P03 t
WHERE t.PIN = P01.PIN AND Isnull(t.PLANNED,'N') != 'Y'
AND t.CODE in (SELECT c.CODE FROM PW001C12 c WHERE c.OPTIONS LIKE '%S%')
AND t.DATETO IS NOT NULL)
LEFT JOIN PW001P03 PA
ON PA.CODE in (SELECT t.CODE FROM PW001C12 t WHERE t.OPTIONS LIKE '%S%')
AND PA.PIN = P01.PIN AND Isnull(PA.PLANNED,'N') = 'Y'
AND CA.SEQUENCENO IS NULL
AND HA.SEQUENCENO IS NULL
AND PA.DATEFROM = (SELECT MIN(t.DATEFROM) FROM PW001P03 t
WHERE t.PIN = P01.PIN AND Isnull(t.PLANNED,'N') = 'Y'
AND t.CODE in (SELECT c.CODE FROM PW001C12 c WHERE c.OPTIONS LIKE '%S%'))
LEFT JOIN PWORG POS
ON POS.ORGTYPE = 5
AND POS.NUMORGID = ISNULL(ISNULL(CA.NUMORGID,HA.NUMORGID),PA.NUMORGID)
LEFT JOIN PWORG DEP
ON DEP.ORGTYPE = 4
AND (DEP.NUMORGID = ISNULL(ISNULL(CA.NUMORGID,HA.NUMORGID),PA.NUMORGID)
OR DEP.NUMORGID = POS.NUMORGIDABOVE)
LEFT JOIN PWORG VES
ON VES.NUMORGID = dbo.ad_scanorgtree(ISNULL(ISNULL(CA.NUMORGID,HA.NUMORGID),PA.NUMORGID),3)
|