- Created by Arvin John Salandanan , last modified 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
« Previous Version 2 Current »
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 |
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 active 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
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