Skip to end of banner
Go to start of banner

Changes made to Active Contracts

Skip to end of metadata
Go to start of metadata

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

image-20250106-145138.png

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.

  • Contract ranks

  • Contract type

  • Contract start date

  • Contract end date

  • Contract trial period end

Termination Date

Datagroups > Contracts > Termination Date

  • No labels