- Created by Arvin John Salandanan on Jan 06, 2025
-
0 link
You are viewing an old version of this content. View the current version.
Compare with Current View Version History
Version 1 Next »
Summary | A view that will show changes to contracts in the past 30 days |
|---|---|
Keywords | Contracts, Module, Crew List View |
Category | Crew List View |
Please remember to add labels to the article. Only the labels from the lit are allowed: /wiki/spaces/PnP/pages/4523197124
Description
A view that will show changes to contracts for contract ranks, contract type, contract start date, contract end date, and contract trial period end in the past 30 days.
View Sample
Main Data Selection
All existing contracts that have been changed in the past 30 days.
SQL statement
Click here to expand...
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
Columns Specification
Column | Description/ Location in APM |
|---|---|
PIN | Personal Details > Personal > PIN |
NAME | Personal Details > Personal > Full Name |
Contract Start Date | Datagroups > Contracts > Start Date |
Date Modified | Modified Date of Contract |
Old Data | Old value of changed field |
New Data | New value of changed field |
Current Ship | Datagroups > Activity > Current > Ship Name |
Current Department | Datagroups > Activity > Current > Department |
Current Position | Datagroups > Activity > Current > Position |
EMPLOYMENTSTARTDATE | Personal Details > Employment > Employment Start Date |
EMPLOYMENTENDDATE | Personal Details > Employment > Employment End Date |
Modified By | User name who modified the contract |
Estimated End Date | Datagroups > Contracts > End Date |
Contract Type | Datagroups > Contracts > Contract Type |
Type of Change | Which field has been changed.
|
Termination Date | Datagroups > Contracts > Termination Date |
- No labels