CREATE VIEW dbo.PW001SRV300 AS
SELECT source.[Date Modified],
source.PIN,
source.[NAME],
source.[Contract Type],
source.[Contract Start Date],
source.[Estimated End Date],
source.[Termination Date],
source.[Type of Change],
source.[Old Data],
source.[New Data],
source.[Modified By],
source.[Current Ship],
source.[Current Department],
source.[Current Position],
source.EMPLOYMENTSTARTDATE,
source.EMPLOYMENTENDDATE,
source.NUMORGID
FROM (
SELECT DISTINCT
CONVERT(VARCHAR, main.repl_ModifiEDDate, 101) AS 'Date Modified',
p20.SEQUENCENO,
main.PIN,
p01.[NAME],
p20.DATESTART AS 'Contract Start Date',
conEndDate.ENDDATE AS 'Estimated End Date',
p20.DATEEND AS 'Termination Date',
c32.[TEXT] AS 'Contract Type',
main.Field_Changed AS 'Type of Change',
CASE
WHEN main.Field_Changed = 'Contract Type' THEN (
SELECT t.[TEXT]
FROM PW001C32 t
WHERE t.CODE = main.OldData
)
/*WHEN main.Field_Changed = 'Contract Scan Validity' THEN CASE
WHEN main.OldData = '0' THEN 'No'
ELSE 'Yes'
END*/
WHEN main.Field_Changed = 'Contract Rank' THEN (
SELECT t.[NAME]
FROM PW001C02 t
WHERE t.CODE = main.OldData
)
WHEN main.Field_Changed IN ('Contract Start Date', 'Contract End Date',
'Contract Trial Period End')
AND main.OldData = '0' THEN NULL
ELSE main.OldData
END AS 'Old Data',
CASE
WHEN main.Field_Changed = 'Contract Type' THEN (
SELECT t.[TEXT]
FROM PW001C32 t
WHERE t.CODE = main.NewData
)
/*WHEN main.Field_Changed = 'Contract Scan Validity' THEN CASE
WHEN main.NewData = '0' THEN
'No'
ELSE 'Yes'
END*/
WHEN main.Field_Changed = 'Contract Rank' THEN (
SELECT t.[NAME]
FROM PW001C02 t
WHERE t.CODE = main.NewData
)
WHEN main.Field_Changed IN ('Contract Start Date', 'Contract End Date',
'Contract Trial Period End')
AND main.NewData = '0' THEN NULL
ELSE main.NewData
END AS 'New Data',
ves.[NAME] AS 'Current Ship',
dep.[NAME] AS 'Current Department',
pos.[NAME] AS 'Current Position',
ISNULL(pu.[USER_NAME], 'ADPW') AS 'Modified By',
p01.EMPLOYMENTSTARTDATE,
p01.EMPLOYMENTENDDATE,
p01.CLIENT NUMORGID
FROM (
SELECT PIN,
CASE
WHEN ap20.RANK <> lead(ap20.RANK, 1, 0)OVER(
PARTITION BY [sequenceno] ORDER BY [sequenceno],
[AUDIT_LINENR] DESC,
[repl_modifieddate] DESC
) THEN lead(ap20.RANK, 1, 0)OVER(
PARTITION BY [sequenceno] ORDER BY [sequenceno],
[AUDIT_LINENR] DESC,
[repl_modifieddate] DESC
)
WHEN ap20.CONTRACTTYPE <> lead(ap20.CONTRACTTYPE, 1, 0)OVER(
PARTITION BY [sequenceno] ORDER BY [sequenceno],
[AUDIT_LINENR] DESC,
[repl_modifieddate] DESC
) THEN lead(ap20.CONTRACTTYPE, 1, 0)OVER(
PARTITION BY [sequenceno] ORDER BY [sequenceno],
[AUDIT_LINENR] DESC,
[repl_modifieddate] DESC
)
WHEN ap20.DATESTART <> lead(ap20.DATESTART, 1, 0)OVER(
PARTITION BY [sequenceno] ORDER BY [sequenceno],
[AUDIT_LINENR] DESC,
[repl_modifieddate] DESC
) THEN lead(CONVERT(VARCHAR, ap20.DATESTART, 101), 1, 0)OVER(
PARTITION BY [sequenceno] ORDER BY [sequenceno],
[AUDIT_LINENR] DESC,
[repl_modifieddate] DESC
)
WHEN ap20.DATEEND <> lead(ap20.DATEEND, 1, 0)OVER(
PARTITION BY [sequenceno] ORDER BY [sequenceno],
[AUDIT_LINENR] DESC,
[repl_modifieddate] DESC
) THEN lead(CONVERT(VARCHAR, ap20.DATEEND, 101), 1, 0)OVER(
PARTITION BY [sequenceno] ORDER BY [sequenceno],
[AUDIT_LINENR] DESC,
[repl_modifieddate] DESC
)
WHEN ap20.TRIALPERIODEND <> lead(ap20.TRIALPERIODEND, 1, 0)OVER(
PARTITION BY [sequenceno] ORDER BY [sequenceno],
[AUDIT_LINENR] DESC,
[repl_modifieddate] DESC
) THEN lead(CONVERT(VARCHAR, ap20.TRIALPERIODEND, 101), 1, 0)OVER(
PARTITION BY [sequenceno] ORDER BY [sequenceno],
[AUDIT_LINENR] DESC,
[repl_modifieddate] DESC
)
/*WHEN ap20.SCANVALIDITY <> lead(ap20.SCANVALIDITY, 1, 0)OVER(
PARTITION BY [sequenceno] ORDER BY [sequenceno],
[AUDIT_LINENR] DESC,
[repl_modifieddate] DESC
) THEN lead(CAST(ap20.SCANVALIDITY AS VARCHAR), 1, 0)OVER(
PARTITION BY [sequenceno] ORDER BY [sequenceno],
[AUDIT_LINENR] DESC,
[repl_modifieddate] DESC
)*/
END AS 'OldData',
CASE
WHEN ap20.RANK <> lead(ap20.RANK, 1, 0)OVER(
PARTITION BY [sequenceno] ORDER BY [sequenceno],
[AUDIT_LINENR] DESC,
[repl_modifieddate] DESC
) THEN ap20.RANK
WHEN ap20.CONTRACTTYPE <> lead(ap20.CONTRACTTYPE, 1, 0)OVER(
PARTITION BY [sequenceno] ORDER BY [sequenceno],
[AUDIT_LINENR] DESC,
[repl_modifieddate] DESC
) THEN ap20.CONTRACTTYPE
WHEN ap20.DATESTART <> lead(ap20.DATESTART, 1, 0)OVER(
PARTITION BY [sequenceno] ORDER BY [sequenceno],
[AUDIT_LINENR] DESC,
[repl_modifieddate] DESC
) THEN CONVERT(VARCHAR, ap20.DATESTART, 101)
WHEN ap20.DATEEND <> lead(ap20.DATEEND, 1, 0)OVER(
PARTITION BY [sequenceno] ORDER BY [sequenceno],
[AUDIT_LINENR] DESC,
[repl_modifieddate] DESC
) THEN CONVERT(VARCHAR, ap20.DATEEND, 101)
WHEN ap20.TRIALPERIODEND <> lead(ap20.TRIALPERIODEND, 1, 0)OVER(
PARTITION BY [sequenceno] ORDER BY [sequenceno],
[AUDIT_LINENR] DESC,
[repl_modifieddate] DESC
) THEN CONVERT(VARCHAR, ap20.TRIALPERIODEND, 101)
/*WHEN ap20.SCANVALIDITY <> lead(ap20.SCANVALIDITY, 1, 0)OVER(
PARTITION BY [sequenceno] ORDER BY [sequenceno],
[AUDIT_LINENR] DESC,
[repl_modifieddate] DESC
) THEN CAST(ap20.SCANVALIDITY AS VARCHAR)*/
END AS 'NewData',
CASE
WHEN ap20.RANK <> lead(ap20.RANK, 1, 0)OVER(
PARTITION BY [sequenceno] ORDER BY [sequenceno],
[AUDIT_LINENR] DESC,
[repl_modifieddate] DESC
) THEN 'Contract Rank'
WHEN ap20.CONTRACTTYPE <> lead(ap20.CONTRACTTYPE, 1, 0)OVER(
PARTITION BY [sequenceno] ORDER BY [sequenceno],
[AUDIT_LINENR] DESC,
[repl_modifieddate] DESC
) THEN 'Contract Type'
WHEN ap20.DATESTART <> lead(ap20.DATESTART, 1, 0)OVER(
PARTITION BY [sequenceno] ORDER BY [sequenceno],
[AUDIT_LINENR] DESC,
[repl_modifieddate] DESC
) THEN 'Contract Start Date'
WHEN ap20.DATEEND <> lead(ap20.DATEEND, 1, 0)OVER(
PARTITION BY [sequenceno] ORDER BY [sequenceno],
[AUDIT_LINENR] DESC,
[repl_modifieddate] DESC
) THEN 'Contract End Date'
WHEN ap20.TRIALPERIODEND <> lead(ap20.TRIALPERIODEND, 1, 0)OVER(
PARTITION BY [sequenceno] ORDER BY [sequenceno],
[AUDIT_LINENR] DESC,
[repl_modifieddate] DESC
) THEN 'Contract Trial Period End'
/*WHEN CAST(ap20.SCANVALIDITY AS VARCHAR) <> lead(CAST(ap20.SCANVALIDITY AS VARCHAR), 1, 0)
OVER(
PARTITION BY [sequenceno] ORDER BY [sequenceno],
[AUDIT_LINENR] DESC,
[repl_modifieddate] DESC
) THEN 'Contract Scan Validity'*/
END AS 'Field_Changed',
ap20.repl_ModifiEDDate,
ap20.DBACTION,
ap20.SEQUENCENO,
ap20.CHANGEDBY
FROM AUDIT_PW001P20 ap20
) main
LEFT JOIN dbo.PW001P03 P03cur
ON main.PIN = P03cur.PIN
AND P03cur.CODE IN (SELECT c12.CODE
FROM PW001C12 c12
WHERE c12.OPTIONS LIKE '%S%')
AND (P03cur.DATETO IS NULL OR P03cur.DATETO >= GETDATE())
AND P03cur.DATEFROM <= GETDATE()
AND P03cur.PLANNED <> 'Y'
LEFT JOIN (
SELECT NUMORGID,
dbo.ad_scanorgtree(NUMORGID, 3) AS VesselID,
dbo.ad_scanorgtree(NUMORGID, 4) AS DeptID
FROM pworg
) ou
ON ou.NUMORGID = p03cur.NUMORGID
LEFT JOIN pworg ves
ON ves.NUMORGID = ou.VesselID
LEFT JOIN pworg dep
ON dep.NUMORGID = ou.DeptID
LEFT JOIN pworg pos
ON pos.NUMORGID = p03cur.NUMORGID
LEFT JOIN PW001P01 p01
ON p01.PIN = main.PIN
LEFT JOIN PWSECURITY_USERS pu
ON pu.USERID = main.CHANGEDBY
AND pu.SITENR = 1
JOIN PW001P20 p20
ON p20.SEQUENCENO = main.SEQUENCENO
LEFT JOIN pw001c32 c32
ON c32.CODE = p20.CONTRACTTYPE
LEFT JOIN PW001P20U conEndDate
ON conEndDate.CONTRACT_SEQNO = p20.SEQUENCENO
WHERE main.DBACTION = 'update'
AND main.Field_Changed IS NOT NULL
AND main.repl_ModifiEDDate > DATEADD(MONTH, -1, GETDATE())
) source |