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
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
left join PW001PAY pay on pay.PIN=p01.PIN
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))
and p01.pin not in (select pin from pw001pay where entrycode in (994, 995, 999)) | 