/
Changes made to Active Contracts
Changes made to Active Contracts
Arvin John Salandanan
Daria Chebotaieva
Owned by Arvin John Salandanan, created
1 Description | 2 View Sample | 3 Main Data Selection | 4 SQL statement | 5 Columns Specification
Summary | A view that will show changes to active contracts in the past 30 days |
|---|---|
Keywords | Contracts, Module, Crew List View |
Category | Crew List View |
Description
A view that will show changes to active contracts for contract ranks, contract type, contract start date, contract end date, and contract trial period ended in the past 30 days.
View Sample
Main Data Selection
All existing contracts that have been changed in the past 30 days.
SQL statement
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())
) sourceColumns Specification
Column | Description/ Location in APM |
|---|
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 |
, multiple selections available,
Related content
Changes made to Sick Activities
Changes made to Sick Activities
More like this
Main Contracts
Main Contracts
More like this
Sub Contracts
Sub Contracts
More like this
Historical: Crew on Sick
Historical: Crew on Sick
More like this
Crew Data Issues
Crew Data Issues
More like this
Expiry view
Expiry view
More like this